Search for:
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
Jan 5 2021
Configuration – How to disable Position Incumbent Validation?

Sometimes while loading the worker assignments or assigning an employee to a new position, you get “The Selected Position Has No Open headcount. Select A Different Position To Continue” error, one resolution to this is to disable the Position Incumbent Validation at enterprise level.

Follow the below steps to disable the position validation:

  1. Navigate to Setup and Maintenance -> Search -> Manage Enterprise HCM Information:

2. Search for Position Incumbent Validation on Enterprise screen. If not found, then follow the steps 3 – 8, else move directly to step 9.

3. If Position Incumbent Validation is not available on enterprise screen, search for ‘Manage Extensible Flexfields’ and search for ‘PER_ORGANIZATION_INFORMATION_EFF’:

4. Click on edit icon:

5. Under Category, expand Category and choose Enterprise and scroll down:

6. Click on Pages under ‘Enterprise Details’:

7. Click on add icon under ‘Enterprise Details: Associated Context Details’ and select & add ‘Position Incumbent Validation’ context:

8. A new context will be added:

Click on Save and Close and Deploy the flexfield.

9. On successful deployment of flexfield, go back to ‘Manage Enterprise HCM Information’ task and search for ‘Position Incumbent Validation’:

10. Click on Edit button in Correction mode and check the checkbox and save the record:

Mandeep Gupta 0 Comments
Jan 2 2021
HRHD – Generate list of published changes

In HR Helpdesk module, generally a lot of customizations are done i.e. adding a new field on standard screen, new triggers etc. Once the solution is finalized, a report can be generated which will list down all the customizations. This is an Out of the box functionality provided by Fusion applications.

Navigate to Home -> Configuration -> Application Composer -> Metadata Manager

Click on generate button:

Choose the objects and Metadata types to be included in report:

Click on generate button and wait while the configuration report is getting generated.

Once the report is generated, click on Export to generate the report in excel or HTML format.

Mandeep Gupta 0 Comments
Jan 2 2021
HDL – Sample HDL to load GL information at Department level

Below is the sample HDL to load the GL Costing information at department level using Organization.dat file. It is always advisable to get the latest METADATA attributes. Check the below link to see the process of Refreshing the HDL objects:

HDL – Refresh Business Objects

Once the latest .dat file is available, check the DFF attributes from attribute window in View business objects.

Sample HDL:

METADATA|OrgInformation|FLEX:PER_ORGANIZATION_INFORMATION_EFF|EFF_CATEGORY_CODE|recordIdentifier(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO)|_COMPANY_VALUESET(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO)|OrganizationId(SourceSystemId)|_COMPANY(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO)|EffectiveStartDate|EffectiveEndDate|ClassificationCode|SourceSystemOwner|SourceSystemId
MERGE|OrgInformation|PER_GL_COST_CENTER_INFO|DEPARTMENT|XX1001|980007|Org_6115|139|1901/01/01|4712/12/31|DEPARTMENT|HRC_SQLLOADER|6115_OrgInformation_PER_GL_COST_CENTER_INFO

Save this as Organization.dat, zip the file and upload it.

Please Note:-

  1. The GL Accounting should have been setup by financials in Fusion.
  2. recordIdentifier(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : This is a unique value. It can be anything.
  3. _COMPANY_VALUESET(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : Pass the value set Id attached to the segment.
  4. _COMPANY(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : Pass the actual value under this attribute.

Check the below post to extract the loaded data from HCM Cloud:

https://fusionhcmconsulting.com/2021/10/bip-query-to-extract-gl-cost-details-from-department/

You can run below query to find value set ID:

select *
  from FND_VS_VALUE_SETS
 where value_set_code = 'XXX_COMPANY'
Mandeep Gupta 1 Comments
Jan 2 2021
HDL – Refresh Business Objects

Refresh Business objects is a process which is used to create the HDL template for a business object with the latest supported attributes.

When to run ‘Refresh Business Objects’ process:

It is advised to run this process:

  1. After every upgrade
  2. After any new DFF/EFF attributes are configured for any HDL supported business objects.

How to run ‘Refresh Business Objects’ process:

To run ‘Refresh Business Objects’ process, navigate to My Client Groups -> Data Exchange -> View Business Objects

On the View Business Objects UI, you will see 2 buttons:

a. Refresh Object – This is used when you want to refresh only one object at a time. This is usually run when any new DFF attributes are setup for a particular object. Once you click on ‘Refresh Object’, you will see the below screen:

Click on Submit button to submit the process.

You can then click on ‘View Process Results’ button to check the progress:

Once the process completes, you can see the last refreshed date:

b. Refresh All Objects – This is used when you want to refresh all the business objects. This is usually run post release upgrade.

Mandeep Gupta 0 Comments
Jan 1 2021
Reports (BIP) – Implementing Digital Signature in PDF BIP reports

Oracle Fusion reports and analytics provides the capability of implementing digital signatures with in BIP. However there are some limitations:

  • Only one digital signature can be used across all reports.
  • For a report with multiple templates, the same signature will be applicable for all the templates.
  • Digital signature can be tested only using the BIP scheduler.

Follow the below steps to implement digital signatures:

  1. Get your pfx file (digital signature file). Fusion currently supports pfx and p12 extensions only.
  2. Login to reports and analytics using below url:

https://xxyxxmz.ea2.oraclecloud.com/xmlpserver

3. Navigate to Administration -> System Maintenance -> Upload Center

4. Choose the signature file under Upload File and choose the File Type as ‘Digital Signature’ and click on ‘Upload’ button:

5. Click on Return and Navigate to Security Center -> Digital Signature

Choose the *Digital ID File and provide the value * Password and choose the roles required and click on Apply button:

6. Go to the report properties -> Formatting and choose the value ‘True’ for Enable Digital Signature attribute. Choose the other values as required:

7. Click ‘Ok’ and save the changes.

8. Schedule the report to test the digital signature:

9. Open the received output and verify the digital signature:

Mandeep Gupta 0 Comments
Jan 1 2021
Security – Role to view all scheduled processes

As per the standard Fusion security, a user can view only the scheduled processes which has been scheduled from his/her user login. However, there is a common requirement where the admin should be able to view all the scheduled processes (processes scheduled by all the users). This can be achieved by creating a custom role. Follow the below mentioned steps to create the Role:

  1. Navigate to Home -> Tools -> Security Console -> Create Role

2. Choose the category as ‘Abstract Data Role’

3. Click next and stop on ‘Role Hierarchy’ train stop. Click on Add button and search for ‘ESS Monitor Role’ and click on ‘Add Role Membership’:

4. Click Next and in ‘User’ train stop add the user to whom you want to assign this role. This is an optional step.

5. Click Next and Click on Save and Close.

6. Search for the newly created role under ‘Roles’ tab:

7. Assign the role to required users.

8. Run the ESS process ‘Import User and Role Application Security Data’. Once the process is complete, login and logout and verify that the user is able to see all the ESS jobs under ‘Scheduled Processes’.

Mandeep Gupta 0 Comments
Jan 1 2021
HDL – Email Data Obfuscation in Test environment

Post P2T refresh, it is mandatory to mask the actual email Ids of the users in cloned test environment. This is required to avoid any unwanted emails getting triggered to the end users.

The approach is already discussed in below post: https://fusionhcmconsulting.com/2020/12/hdl-query-to-delete-phones-data/

Use the below SQL to extract the data in HDL format:

select 'MERGE' "METADATA",
       'PersonEmail' "PersonEmail",
       pea.email_address_id "EmailAddressId",
       pea.person_id "PersonId",
       to_char(pea.date_from,'RRRR/MM/DD') "DateFrom",
       to_char(pea.date_to,'RRRR/MM/DD') "DateTo",
       pea.email_type "EmailType",
       -->pea.email_address "1EmailAddress",
       replace(replace(pea.email_address,'@','@invalid'),'.co','.xco') "EmailAddress", --> Change the format as per your requirement
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemId",
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemOwner"
  from per_email_addresses pea
 where 1=1
   and upper(email_address) like '%XXCOMPANY%' --> Change the format as per your requirement

Query with Person Number:

select 'MERGE' "METADATA",
       'PersonEmail' "PersonEmail",
       pea.email_address_id "EmailAddressId",
       pea.person_id "PersonId",
       papf.person_number "PersonNumber",
       to_char(pea.date_from,'RRRR/MM/DD') "DateFrom",
       to_char(pea.date_to,'RRRR/MM/DD') "DateTo",
       pea.email_type "EmailType",
       -->pea.email_address "1EmailAddress",
       replace(replace(pea.email_address,'@','@invalid'),'.co','.xco') "EmailAddress", --> Change the format as per your requirement
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemId",
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) "SourceSystemOwner"
  from per_email_addresses pea, per_all_people_f papf
 where 1=1
   and pea.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and upper(email_address) like '%XXCOMPANY%' --> Change the format as per your requirement

Once the data is generated, create the HDL file and load the Worker.dat.

Post data load steps:

  1. Run “Send Personal Data for Multiple Users to LDAP” process. This process will sync up the latest email address from worker email to user.
  2. Login to Security Console and verify for couple of users from UI or you can verify from backend.
  3. Disable the notifications (optional) – Navigate to Security Console -> User Categories -> Notification Preferences -> Uncheck the Enable Notifications:

4. Or you can redirect all the notification to single email address. Search for ‘Manage Approval Groups’ task in ‘Setup and Maintenance”:

  • Clicking on above task will take you to the BPM Worklist page.
  • Click on “Administration” button and search for Test Notification Email Address and provide the value where you want to redirect all your notifications:

If you want to completely purge the email data then use below post:

https://fusionhcmconsulting.com/2022/10/hdl-delete-person-email-addresses/

Mandeep Gupta 1 Comments
Dec 31 2020
Reports (BIP) – Customizing a standard BIP report

Many a times, consultants are required to modify the standard BIP reports. This is very much applicable in case of workflow notifications which are based on BIP reports. Both data model as well report can be customized to include/exclude any additional fields.

Follow the below steps:

  1. Login to your pod using below url:

https://xxzzaa.fa.bb.oraclecloud.com/xmlpserver/

(note the xmlpserver in the end).

2. Then navigate to /Shared Folders/Human Capital Management/Folder

3. Click on More and you will see Customize option. Click on Customize:

This will create a new report under:

/Shared Folders/Custom/Human Capital Management/Folder (notice the change in path).

You can upload your customized templates here.

Click on edit and choose the data model from standard path.

Similar approach will be followed for data model customizations as well.

In order to display the changes asap, please configure the below profile option for global scope:

BIP_CLIENT_REFRESH_TIME – Oracle Middleware Extensions for Applications – Application Core

Provide a value of 15 min.

Check below document for more details:

https://docs.oracle.com/en/cloud/saas/applications-common/20b/facia/approval-management.html#FACIA3819777

Mandeep Gupta 0 Comments
Dec 31 2020
Reports (BIP) – Tips/ Shortcuts
  • To display the date in DD-Mon-RRRR format, please use below in your SQL query:

to_char(date_of_birth,’DD-Mon-RRRR’, ‘nls_date_language=American’)

  • Use below condition to get current user id:

fnd_global.USER_GUID

AND PP.PERSON_ID = PU.PERSON_ID
AND PU.USER_GUID = FND_GLOBAL.USER_GUID

— Use below function to convert the amount in Arabic:

<?xdoxslt:toCheckNumber(‘ar-SA’, TOTAL, ‘AED’,’CASE_UPPER’)?>

— Using LISTAGG to show multiple rows into a single cell:

select listagg(papf.person_number,';') within group (order by person_number) from per_all_people_f papf
where rownum <=5

— Getting Person Id of logged in Person:

HRC_SESSION_UTIL.GET_USER_PERSONID

— Get UDT value in BIP:

ff_user_tables_pkg.get_table_value
Mandeep Gupta 0 Comments
Dec 31 2020
HDL – Query to delete phones data

Sometimes it is required to delete the uploaded phones data from Fusion HCM. Below query can be used to create a BIP report which in turn can be used to generate HDL file to delete phones data.

The query can be modified to delete phones data for contacts as well as for other person types.

Supported Version :- 20D

Query:

SELECT 'METADATA|PersonPhone|PhoneId|PhoneType|PersonId|DateFrom|SourceSystemId|SourceSystemOwner' D_ROW
FROM DUAL
union
SELECT 'DELETE|PersonPhone|'||
       pp.phone_id||'|'||
 
       pp.phone_type||'|'||
       pp.person_id||'|'||
       to_char(pp.date_from,'RRRR/MM/DD')||'|'||
       hikm.source_system_id||'|'||
       hikm.source_system_owner
  from per_phones pp,
       hrc_integration_key_map hikm
 where pp.phone_id= hikm.surrogate_id
   and exists (select 1 from per_periods_of_service where person_id=pp.person_id)
Mandeep Gupta 1 Comments
Dec 31 2020
HRHD – Employee Resource Query

In HR Helpdesk (HRHD), workers are assigned as resources. Below SQL query can be used to get the details of workers who are assigned as resources:

select hp.PERSON_FIRST_NAME "FirstName"
,hp.PERSON_LAST_NAME "LastName"
,hp.EMAIL_ADDRESS "ResourceEmail"
,hp.PARTY_NUMBER "ResourcePartyNumber"
,papf.person_number
from HZ_PARTIES hp,
per_all_people_f papf
where papf.person_id = hp.ORIG_SYSTEM_REFERENCE
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
--and papf.person_number IN ('964091')
Mandeep Gupta 0 Comments
Dec 30 2020
HCM Extract – Query to get last extract run date

While creating an HCM extract based on a BIP report, there is a common requirement where the BIP should retrieve “Changed Only” data. This can be achieved using a BIP report data model which is based on globalReportsDataModel (/shared/Human Capital Management/Payroll/Data Model).

Use the below query in the data set to extract last run date of HCM extract and based on this date create a filter on last_update_date of the standard table from which we are extracting the data.

WITH pLastRunDate AS
(SELECT MAX(pfi.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM pay_flows pf
,pay_flow_instances pfi
WHERE 1=1
AND pf.base_flow_id = pfi.base_flow_id
AND pfi.LAST_UPDATE_DATE < sysdate
AND pf.base_flow_name ='XX Custom Integration Extract' -- - give the name of your HCM extract
AND pfi.status = 'COMPLETED'
)
Mandeep Gupta 0 Comments