Usage of PVOs GLOBALPERSONPVOVIEWALL and PERSONDETAILSPVOVIEWALL

SR_CC

We are building a BICC extract to extract person and their assignment data, wanted to understand when should we use GLOBALPERSONPVOVIEWALL and PERSONDETAILSPVOVIEWAL

Nathan CCC

Hi,

The best way to make a decision is to review the sql in each view object to decide if that is how you want your data to be extracted or not. For example, does it inner join a parent and child table so parents with no children are excluded etc. Obviously you can compare the data in each but looking at the sql is easier.

So go to page /analytics/saw.dll?IssueRawSQL.

Run the query, view log, review physical sql(s), make a decision which you want to use.

select_physical t.* from 
"HcmTopModelAnalyticsGlobalAM.PersonAM.GlobalPersonPVOViewAll" 
t
fetch first 1 rows only

and

select_physical * from 
"HcmTopModelAnalyticsGlobalAM.PersonAM.PersonDetailsPVOViewAll"
fetch first 1 rows only

and in case the VO is not actually used in a subject areas so the above does not work

select all t.* 
from EXTERNAL(
'ADF', 
'"oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal"."Connection Pool"'
).'HcmTopModelAnalyticsGlobalAM.PersonAM.GlobalPersonPVOViewAll' 
t
fetch first 7 rows only	

Nathan CCC

Using this method you can see that PERSONDETAILSPVOVIEWALL is just a basic select from one table query but only getting the current date effective date row. For some reason no query in the other log.

... FROM PER_ALL_PEOPLE_F V195095386 WHERE ( DATE'2024-02-13' BETWEEN V195095386.EFFECTIVE_START_DATE AND V195095386.EFFECTIVE_END_DATE)) T2540548 )