Search for:
Feb 6 2021
HCM Infolets – Enable HCM Infolets on Home screen (Part – 1)

HCM Infolets are hidden out of the box. These can enabled using configuration. Follow the below steps to enable HCM Infolets:

  • Navigate to Configuration -> Structure:
  • Click on “Go to the Manage Sandboxes Page” and click on Create Sandbox button. Give a name and choose ‘Structure’ and ‘Page Composer’ from available tools:

And click on Create and Enter the sandox.

  • From ‘Tools’ choose ‘Structure’:
  • Click on Home Icon and then on HCM Infolets and choose the value of ‘Visible’ to “Yes:
  • Click on Save, once you have made the changes:
  • Click on Application Home icon and scroll down to make sure that HCM Infolets are visible at the end of the page:
  • After verifying the changes, publish the sandbox.

Mandeep Gupta 0 Comments
Feb 5 2021
HDL – Load Goal Library Data

Goal Library is loaded as Content items using ContentItem.dat. The various attributes and their mapping can be found using GoalLibraryAttributes.xlsx which can be found on metalink using below note id:

Query To Get Goal Library Goals (Doc ID 2228128.1)

How to Load Library Goals (Doc ID 2201757.1)

Please note that ItemText2 (Priority Code) and ItemText12 (External ID) is a required attribute. Although these are not marked as mandatory and HDL load will complete successfully with a NULL value for these, however, the loaded Goals will not be visible in UI.

ItemText12 – Can be any random numeric value.

Sample HDL File:

METADATA|ContentItem|ContentItemId|ContentTypeId|ContentItemCode|Name|ItemDescrlong|ItemTextTl11|DateFrom|DateTo|ItemText1|ItemText2|ItemDate1|ItemDate2|ItemDate3|ItemText3|ItemTextTl1|ItemText5|ItemTextTl2|ItemNumber1|ItemText6|ItemText7|RatingModelId|ItemText9|ItemText10|ItemText12|SourceSystemId|SourceSystemOwner
MERGE|ContentItem||124||Test Goal Libarary 123|Goal Loaded into libarary using content item HDL|Schedule regular headcount and budget review meetings. Facilitate regular reporting distributions to senior management on a biweekly basis|2000/01/01||A|HIGH||||STRETCH||QUANTITATIVE||20|MAX|PERCENT||PERFORMANCE|ALL|1000090000|VIS09090909|VIS
Mandeep Gupta 0 Comments
Feb 5 2021
BIP Reports – Query to get pending absence transactions
select distinct module_identifier "ProcessName"
, p.person_number
, n.full_name
, subject
, STATUS
, h.INITIATOR_USER_ID "SubmittedBy" 
, d.submitted_date
, ppnf_sup.full_name "assignees"
, ps.person_number "ApproverPersonNumber"
, ppnf_sup.full_name "PendingApproverName", h.transaction_id
, module_identifier
, object
, object_id
from
hrc_txn_header h, 
hrc_txn_data d,
anc_per_abs_entries abs,
PER_PERSON_SECURED_LIST_V p, 
per_person_names_f n,
PER_ASSIGNMENT_SECURED_LIST_V a,
per_assignment_supervisors_f AssignmentSupervisor,
per_person_names_f_v ppnf_sup,
per_all_people_f ps
where 
h.transaction_id = d.transaction_id  
and object_id= abs.per_absence_entry_id
and abs.person_id = p.person_id
and n.person_id = p.person_id
and a.person_id = p.person_id
and abs.approval_status_cd = 'AWAITING'
and a.assignment_type ='E'
and n.name_type = 'GLOBAL'
and ppnf_sup.name_type = 'GLOBAL'
and a.assignment_status_type = 'ACTIVE'
and ps.person_id = ppnf_sup.person_id
and AssignmentSupervisor.assignment_id = a.assignment_id
and AssignmentSupervisor.manager_id = ppnf_sup.person_id
and sysdate between p.effective_start_date and p.effective_end_date
and sysdate between n.effective_start_date and n.effective_end_date
and sysdate between a.effective_start_date and a.effective_end_date
and sysdate between ps.effective_start_date and ps.effective_end_date
and sysdate between AssignmentSupervisor.effective_start_date and AssignmentSupervisor.effective_end_date
and sysdate between ppnf_sup.effective_start_date and ppnf_sup.effective_end_date
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of courses
*FROM Clausewlf_learning_items_f wlif, wlf_learning_items_f_tl wlift
Value Attributes Table Alias 
*Value Column NameSUBSTR(wlift.name,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(wlif.LEARNING_ITEM_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausewlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID AND wlift.language = ‘US’ AND wlif.LEARNING_ITEM_TYPE = ‘ORA_COURSE’ AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date and wlif.effective_end_date AND wlif.effective_start_date BETWEEN wlift.effective_start_date and wlift.effective_end_date
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of departments

Use below setup to create a table based value set which will show the list of all departments:

*FROM ClauseHR_ORG_UNIT_CLASSIFICATIONS_F HOCF, HR_ORGANIZATION_UNITS_F_TL HOUT
Value Attributes Table Alias 
*Value Column NameSUBSTR(HOUT.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(HOUT.ORGANIZATION_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE ClauseHOCF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID AND TRUNC(SYSDATE) BETWEEN HOCF.EFFECTIVE_START_DATE AND HOCF.EFFECTIVE_END_DATE AND HOCF.EFFECTIVE_START_DATE BETWEEN HOUT.EFFECTIVE_START_DATE AND HOUT.EFFECTIVE_END_DATE AND HOUT.LANGUAGE = USERENV(‘LANG’) AND HOCF.CATEGORY_CODE = ‘DEPARTMENT’
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
HRHD – Query to get Service Request Details

Use below query to get service request details from Fusion BIP:

SELECT ssr.sr_id,
ssr.title,
ssr.problem_desc,
to_char(ssr.open_date,’RRRR/MM/DD’) sr_opening_date,
ssr.primary_contact_party_id,
ssr.sr_number,
ssr.status_cd,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.status_cd
AND flv.lookup_type = ‘ORA_SVC_SR_STATUS_CD’
AND flv.LANGUAGE = ‘US’) sr_status_meaning,
ssr.severity_cd,
(SELECT meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_code = ssr.severity_cd
AND flv.lookup_type = ‘ORA_SVC_SR_SEVERITY_CD’
AND flv.LANGUAGE = ‘US’) sr_severity_meaning,
sc.category_name category,
sq.queue_name queue,
to_char(ssr.last_resource_assign_date,’RRRR/MM/DD’) sr_last_resource_assigned_on
FROM svc_service_requests ssr,
svc_queues sq,
svc_categories sc
WHERE ssr.queue_id = sq.queue_id
AND ssr.category_id = sc.category_id
–AND ssr.status_cd in (‘ORA_SVC_NEW’,’ORA_SVC_INPROGRESS’)

Mandeep Gupta 0 Comments
Feb 5 2021
Reports (BIP) – Query to extract Person EIT Data
select papf.person_number ,
ppeif.PEI_INFORMATION1,
ppeif.PEI_INFORMATION2,
ppeif.PEI_INFORMATION3,
to_char(ppeif.PEI_INFORMATION_DATE1,'yyyy/mm/dd')date_from,
to_char(ppeif.PEI_INFORMATION_DATE2,'yyyy/mm/dd')date_to
from per_all_people_f papf,
PER_PEOPLE_EXTRA_INFO_F_V ppeif
where 1=1
and papf.person_id = ppeif.person_id
AND ppeif.INFORMATION_TYPE = 'XYZ Test Results'
AND ppeif.PEI_INFORMATION_CATEGORY = 'XYZ Test Results'
Mandeep Gupta 0 Comments
Feb 4 2021
Reports (BIP) – Query to get Adjustments in Absences

select papf.person_number,
ppnf.full_name,
absence_plan.name ,
apaed1.value,
apaed1.type,
to_char(apaed1.procd_date,’DD-MON-YYYY’) processed_date,
apaed1.created_by
from anc_per_acrl_entry_dtls apaed1,
ANC_PER_PLAN_ENROLLMENT apaed,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT aapf.absence_plan_id, aapft.NAME
FROM anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf
WHERE aapft.absence_plan_id = aapf.absence_plan_id
AND aapf.plan_status = ‘A’ — added to pick only Active Absence Plans
AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
AND aapft.language = ‘US’
) absence_plan
where apaed.plan_id = absence_plan.absence_plan_id
and apaed.plan_id = apaed1.pl_id
AND ppnf.name_type = ‘GLOBAL’
AND ppnf.person_id = apaed.person_id
AND ppnf.person_id = apaed1.person_id
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
AND apaed.person_id = papf.person_id
AND apaed1.person_id = papf.person_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date and papf.effective_end_date
–AND UPPER(absence_plan.name) NOT LIkE ‘%SICK%’
AND apaed1.type = ‘ADJOTH’
–and apaed1.created_by not like ‘FUSION_APPS_HCM_ESS_LOADER_APPID’
ORDER BY papf.person_number,absence_plan.name

Mandeep Gupta 0 Comments
Feb 3 2021
Reports (BIP) – Query to get Legislative Data group associated with Legal Entity

select ple.name legal_employer
, pldgt.name legislative_data_group
from per_legislative_data_groups pldg
,per_legislative_data_groups_tl pldgt
,Hr_Organization_Information_F Psu_Inf
,per_legal_employers ple
where 1=1
and pldg.legislative_data_group_id = pldgt.legislative_data_group_id
and pldgt.language = ‘US’
and psu_inf.Org_Information_Context = ‘PER_PSU_PAYROLL_INFO’
and psu_inf.Org_Information1 = pldgt.legislative_data_group_id
and Psu_Inf.Organization_Id = ple.Organization_Id
and TRUNC(sysdate) BETWEEN ple.effective_start_date AND ple.effective_end_date

Mandeep Gupta 0 Comments
Jan 29 2021
Reports (BIP) – Organization classifications in Fusion

Fusion supports multiple organization types like Legal Employer, Business Units, Division, Departments etc. Below are the main classification codes used for organizations in Fusion:

Division – HCM_DIVISION

Department – DEPARTMENT

Legal Employer – HCM_LEMP

Business Unit – FUN_BUSINESS_UNIT

All the available classifications can be found in below table:

select classification_code
,classification_name
,description
from hr_org_classifications_tl hoct
where language = 'US'

Example :- Query to get divisions list:

select haou.name,
houcf.classification_code,
houcf.status
from hr_all_organization_units haou,
hr_org_unit_classifications_f houcf
where haou.ORGANIZATION_ID = houcf.ORGANIZATION_ID
--and houcf.STATUS ='A'
and houcf.CLASSIFICATION_CODE ='HCM_DIVISION'
and trunc(sysdate)between houcf.effective_start_date and houcf.effective_end_date
order by Name
Mandeep Gupta 0 Comments
Jan 14 2021
REST API – Sample Payloads (Part – 1)

Sample payload for adding DFF attribute value for absence entries:

  1. Sample Rest API Payload for adding dff value

url will be same :

https://abc_test.oraclecloud.com/hcmRestApi/resources/11.13.18.02/absences/

{

    “personNumber”: “999”,

    “employer”: “ABC Test Employer“,

    “absenceType”: “Sick Leave”,

    “startDateDuration”: “1”,

    “startDate”: “2019-03-28”,

    “startTime”: “08:00”,

    “endDate”: “2019-03-28”,

    “endTime”: “17:00”,

    “absenceStatusCd”: “SUBMITTED”,

    “diseaseCode”:”https://google.com”,

    “absenceRecordingDFF”: [

        {

            “__FLEX_Context”: null,

            “preapprovaltaken”: “N”

        }

    ]

}

In above example dff preapprovaltaken is enabled at global level and not at any context.

Below is another example where context is enabled

{

                “personNumber”: “999”,

                “employer”: ” ABC Test Employer “,

                “absenceType”: “Maternity Leave”,

                “startDateDuration”:”1″,

                “startDate”: “2019-10-08”,

                “startTime”: “08:00”,

                “endDate”: “2019-10-08”,

                “endTime”: “17:00”,

                “absenceStatusCd”: “SUBMITTED”,

                “absenceReason”:”ML – For Adoption”,

                “absenceRecordingDFF”: [{

                                                “administratorComments” : null,

                                               “__FLEX_Context” : “300000089749943”,

                                              “dateOfAdoption” : “2019-10-22”

                                               }

                                         ]

}

This context value can be retrieved from below query:

SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
FROM fnd_descr_flex_contexts_vl
WHERE UPPER(DESCRIPTIVE_FLEXFIELD_NAME) LIKE ‘ANC_PER_ABS_ENTRIES_DFF’

Mandeep Gupta 0 Comments
Jan 7 2021
Reports (BIP) – Query to get UCM Content ID of an absence attachment

Use below query to get absence attachment id of an absence from UCM:

SELECT papf.person_number
,type.name absence_type
,fdt.dm_document_id ucm_content_id
,fdt.file_name
,to_char(apae.start_date,'DD-Mon-RRRR', 'nls_date_language=American') absence_start_date
,to_char(apae.end_date,'DD-Mon-RRRR', 'nls_date_language=American') absence_end_date
,apae.source
FROM fnd_attached_documents fad
,fnd_documents_tl fdt
,anc_per_abs_entries apae
,anc_absence_types_vl type
,per_all_people_f papf
WHERE 1 = 1
AND fad.entity_name ='ANC_ATTACHMENT'
AND fad.pk1_value = apae.per_absence_entry_id
AND fdt.document_id = fad.document_id
AND fdt.language = userenv('LANG')
AND apae.absence_type_id = type.absence_type_id
--AND type.name = 'Sick Leave'
AND papf.person_id = apae.person_id
-- AND papf.person_number in (:PERSON_NUMBER)
AND trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
Mandeep Gupta 0 Comments
Jan 7 2021
Configuration – How to get list of supported parameters for a DFF?

There is a common requirement to default the value of DFF attributes on a UI page. For example, we want to default a defined attribute of ‘PER_PERSONS_DFF’ DFF based on person_id. For this we will need to pass the parameter as :{parameter.parameter_name} in table validated value set. To know the list of all parameters supported for ‘PER_PERSONS_DFF’, we can utilize either of below mentioned methods:

  1. From UI:

Navigate to Setup and Maintenance -> Search -> Manage Descriptive Flexfields-> PER_PERSONS_DFF -> Search

Click on edit icon and search for Derivation value field under ‘Context Segment’:

You don’t see any derivation value here which means ‘PER_PERSONS_DFF’ doesn’t support any parameters.

You can check the same for any other DFF and check the list of parameters. Below is a screen shot from ‘PER_POSITIONS_DFF’ dff which shows the supported parameter list.

2. From Backend:

Run the below SQL from reports and analytics and verify the results:

select * from FND_DF_PARAMETERS
where DESCRIPTIVE_FLEXFIELD_CODE = ‘PER_POSITIONS_DFF’

Mandeep Gupta 0 Comments
Jan 6 2021
SSO – SSO Configuration and Integration with Fusion HCM

There is a common requirement to implement Single Sign On (SSO) with fusion HCM.

First, let’s try to understand some basic concepts of SSO:

  • IDP – Identity provider : The identity provider verifies the user credentials and sends the authorization and authentication response back to the service provider. For example, Microsoft AD.
  • SP – Service Provide : Oracle Application Cloud (Fusion HCM) which is setup as a service provider, sends a verification request to the user’s identity provider.

Users will be registered with identity providers i.e. Microsoft AD in this case who store and manage identity and credentials. In Security Console, we can add those identity providers so that we can verify those users .

  • Oracle Applications Cloud, which is set up as a service provider, sends a verification request to the user’s identity provider who’s already added to the Security Console.
  • The identity provider verifies the user credentials and sends the authorization and authentication response back to the service provider.
  • After successful authentication, users are granted access to the required application or web page.

Configuration Steps with Microsoft AD:

To configure Oracle Applications Cloud as the service provider, you must do the following in Oracle Cloud and some steps in Microsoft AD:

1) Microsoft AD – In Azure AD, download the Azure AD SAML metadata document.

2) Oracle Applications – Add an identity provider , upload the metadata document provided by IDP is Step 1

3) Oracle Applications – Download the federation metadata document.

4) Microsoft AD – Upload the federation metadata document provided by oracle in Step 3 .

References:

Oracle Cloud Steps – https://docs.oracle.com/en/cloud/saas/human-resources/20d/ochus/single-sign-on.html#OCHUS3432974

Microsoft AD –

https://docs.cloud.oracle.com/en-us/iaas/Content/Identity/Tasks/federatingADFSazure.htm

Please check the below link for SSO related FAQ’s:

Single Sign-On (Chapter 9) 20D (oracle.com)

Mandeep Gupta 0 Comments
Jan 6 2021
HDL – Updating an attribute on Assignment

Technical consultants encounter many scenario’s where they have to update one or two attributes on the assignment data post initial data load. This can happen during any phase of the new implementation or post go-live. For example, there is a need to update the assignment category for a set of workers. In such scenario’s, the approach is to extract the already uploaded assignment data with minimum required attributes and append the attributes for assignment category and upload it back to Fusion using HDL.

The key thing here is to extract the minimum required data for Assignment and WorkTerms. The mandatory fields required for an update using HDL can be checked from:

My Client Groups -> Data Exchange -> View Business Objects -> Worker -> WorkTerms -> Assignment

Validated Version :- 20D

Sample Query to extract the minimum required attributes for Assignments and Workterms in order to update assignment category (AssignmentCategory):

SELECT HDL_DATA
FROM (
SELECT 'METADATA'
|| CHR (124)
|| 'WorkTerms'
|| CHR (124)
|| 'AssignmentId'
|| CHR (124)
|| 'PeriodOfServiceId'
|| CHR (124)
|| 'EffectiveLatestChange'
|| CHR (124)
|| 'EffectiveSequence'
|| CHR (124)
|| 'EffectiveStartDate'
|| CHR (124)
|| 'EffectiveEndDate'
|| CHR (124)
|| 'SourceSystemId'
|| CHR (124)
|| 'SourceSystemOwner'
|| CHR (124)
|| 'ActionCode'
HDL_DATA
, 1 SEQUENCE
FROM DUAL
UNION ALL
select 'MERGE'
|| CHR (124)
|| 'WorkTerms'
|| CHR (124)
|| paam.assignment_id
|| CHR (124)
|| paam.period_of_service_id
|| CHR (124)
|| paam.effective_latest_change
|| CHR (124)
|| paam.effective_sequence
|| CHR (124)
|| to_char(paam.effective_start_date, 'YYYY/MM/DD')
|| CHR (124)
|| to_char(paam.effective_end_date, 'YYYY/MM/DD')
|| CHR (124)
|| hikm.source_system_id
|| CHR (124)
|| hikm.source_system_owner
|| CHR (124)
|| paam.action_code HDL_DATA,
2 SEQUENCE
FROM per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE 1 = 1
AND paam.assignment_type IN ('ET','CT','PT')
AND hikm.surrogate_id = paam.assignment_id
UNION ALL
SELECT 'METADATA'
|| CHR (124)
|| 'Assignment'
|| CHR (124)
|| 'AssignmentId'
|| CHR (124)
|| 'WorkTermsAssignmentId'
|| CHR (124)
|| 'EffectiveLatestChange'
|| CHR (124)
|| 'EffectiveSequence'
|| CHR (124)
|| 'EffectiveStartDate'
|| CHR (124)
|| 'EffectiveEndDate'
|| CHR (124)
|| 'SourceSystemId'
|| CHR (124)
|| 'SourceSystemOwner'
|| CHR (124)
|| 'ActionCode'
|| CHR (124)
|| 'AssignmentCategory' HDL_DATA
, 3 SEQUENCE
FROM DUAL
UNION ALL
SELECT 'MERGE'
|| CHR (124)
|| 'Assignment'
|| CHR (124)
|| paam.assignment_id
|| CHR (124)
|| paam.work_terms_assignment_id
|| CHR (124)
|| paam.effective_latest_change
|| CHR (124)
|| paam.effective_sequence
|| CHR (124)
|| to_char(paam.effective_start_date, 'YYYY/MM/DD')
|| CHR (124)
|| to_char(paam.effective_end_date, 'YYYY/MM/DD')
|| CHR (124)
|| hikm.source_system_id
|| CHR (124)
|| hikm.source_system_owner
|| CHR (124)
|| paam.action_code
|| CHR (124)
|| 'FR' HDL_DATA
,4 SEQUENCE
FROM per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE 1 = 1
AND paam.assignment_type IN ('E','C','P')
AND hikm.surrogate_id = paam.assignment_id
)
ORDER BY SEQUENCE

You can modify the above query to include any attributes which you want to update. For example, sample file to update Notice Period will look like:

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2002/06/16|4712/12/31|300000066966135|FUSION|HIRE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|NoticePeriod|NoticePeriodUOM
MERGE|Assignment|300000066966140|300000066966135|Y|1|2002/06/16|4712/12/31|300000066966140|FUSION|HIRE|3|M

Sample RTF File:

ASG_UPD_HDL-2Download
Mandeep Gupta 0 Comments
Jan 6 2021
Reports (BIP) – Query to extract Worker disability details

Below query can be used to extract disability details of a worker:

SELECT papf.person_id
,papf.person_number
,ppnf.full_name
,to_char(pdf.effective_start_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_st_dt
,to_char(pdf.effective_end_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_eff_end_dt
,pdf.legislation_code
,pdf.disability_code
,(select pou.name from per_organization_units pou
where pou.organization_id = pdf.organization_id) disability_org_name
,to_char(pdf.registration_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_date
,to_char(pdf.registration_exp_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_registration_exp_date
,to_char(pdf.assessment_due_date,'DD-Mon-RRRR', 'nls_date_language=American') dis_assessment_due_date
,pdf.category
,pdf.description
,pdf.degree
,pdf.quota_fte
,pdf.reason
FROM PER_DISABILITIES_F pdf
,PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
WHERE papf.person_id = pdf.person_id
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
Mandeep Gupta 0 Comments
Jan 6 2021
Reports (BIP) – Query to get assigned payroll details

Use the below query to extract assigned payroll details from Fusion HCM:

SELECT papf.person_id
	  ,papf.person_number
	  ,ppnf.full_name
	  ,papd.payroll_id
	  ,papf_pay.payroll_name
	  ,prrd.payroll_relationship_number
	  ,to_char(prrd.start_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_start_date
	  ,to_char(prrd.end_date,'DD-Mon-RRRR', 'nls_date_language=American') payroll_rel_end_date
	  ,to_char(papd.FSED,'DD-Mon-RRRR', 'nls_date_language=American') fsed
	  ,to_char(papd.FINC,'DD-Mon-RRRR', 'nls_date_language=American') finc
	  ,to_char(papd.LSPD,'DD-Mon-RRRR', 'nls_date_language=American') lspd
	  ,to_char(papd.LSED,'DD-Mon-RRRR', 'nls_date_language=American') lsed
	  ,paam.assignment_number
	  ,prgf.time_card_req
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND papf.person_number = '11111'
 ORDER BY 2,3
Mandeep Gupta 0 Comments