BIP – Extract list of counties for GB
Below SQL can be used to extract list counties configured for GB in HCM:
SELECT *
FROM fusion.hz_geographies hg
WHERE hg.geography_type = 'COUNTY'
AND country_code= 'GB'
ORDER BY geography_name
Below SQL can be used to extract list counties configured for GB in HCM:
SELECT *
FROM fusion.hz_geographies hg
WHERE hg.geography_type = 'COUNTY'
AND country_code= 'GB'
ORDER BY geography_name
There are different requirements where one wants to get number of active/inactive work relationships in a legal entity.
Below SQL query can be run to get these counts:
select ple.name, count(ppos.period_of_Service_id) number_of_wrs
from per_legal_employers ple
,per_periods_of_service ppos
where ppos.legal_entity_id = ple.organization_id
group by ple.name
order by 1
Many a times, even after loading Person EFF information successfully, using HCM Data loader, the information is not available in UI.
In such cases, please make sure that following fields are passed correctly:
InformationType – Name of extra information type e.g. ‘XYZ Medical History’
EFF_CATEGORY_CODE – EFF Category Context. For example, for person EIT, the value will be PER_EIT
CategoryCode – EFF Category Context. For example, for person EIT, the value will be PER_EIT
PeiInformationCategory – Name of extra information type e.g. ‘XYZ Medical History’
Sample HDL file to load Worker EFF information:
METADATA|WorkerExtraInfo|PersonNumber|PersonId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|FLEX:PER_PERSON_EIT_EFF|PeiInformationCategory|CategoryCode|InformationType|medicalStatus(PER_PERSON_EIT_EFF=XYZ Medical Informaton))|EFF_CATEGORY_CODE
MERGE|WorkerExtraInfo|998812||2022/04/01|4712/12/31|FUSION_998812_1|HRC_SQLLOADER|XYZ Medical Informaton|XYZ Medical Informaton|PER_EIT|XYZ Medical Informaton|Normal|PER_EIT
Below query can be used to extract the document record from content server.
SELECT papf.person_number "Person Number",
ppnf.first_name "First Name",
ppnf.last_name "Last Name",
fdt.file_name "Attached File Name",
fdt.dm_version_number "Document Id",
fdt.dm_document_id "UCM Content Id",
(SELECT 'https://'||external_virtual_host
FROM fusion.ask_deployed_domains
WHERE deployed_domain_name = 'FADomain')
||'/cs/idcplg?IdcService=GET_FILE'
|| chr(38)
|| 'dID='
|| fdt.dm_version_number
|| '&dDocName='
|| fdt.dm_document_id
|| '&allowInterrupt=1' "UCM File Link"
FROM per_all_people_f papf,
per_person_names_f ppnf,
hr_documents_of_record hdor,
fnd_attached_documents fad,
fnd_documents_tl fdt
WHERE 1=1
AND hdor.person_id = papf.person_id
AND papf.person_id = ppnf.person_id
AND hdor.documents_of_record_id = fad.pk1_value
AND fad.document_id = fdt.document_id
AND fdt.language = 'US'
AND fad.entity_name = 'HR_DOCUMENTS_OF_RECORD'
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
ORDER BY 1
Oracle HCM allows special characters to be used in fields like Person Names/ Department Names/ Jobs etc. But in reporting, there may be a requirement to change the special characters (like ý, ě, ž, ů etc) to normal English characters.
CONVERT function can be used for such requirements.
select CONVERT('Politických vězňů ižní Předměstí Plzeň', 'US7ASCII') normal_english from dual
Output:
Settings and Actions under user name provides a lot of options. Sometimes, the users request to remove unwanted options from this list:
These options can be removed from this list by activating a sandbox and adding tool – “Page Template Composer”:
Once you activate the Sandbox, click on Edit Global Page Template option under “Settings and Actions” and click on Global Page Template:
Click on the option you want to hide (Print Me) in this case:
Click on Settings and uncheck the visible checkbox:
Once the changes are done and you are happy with the changes, publish the sandbox.
Below is a sample file to create Pending Worker record using HDL:
METADATA|Worker|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CategoryCode|ActionCode|SourceSystemOwner|SourceSystemId
MERGE|Worker|2023/07/01|4712/12/31||2023/07/01|1990/05/12||ADD_PEN_WKR|HDL|TestEmp_123
METADATA|PersonName|EffectiveStartDate|EffectiveEndDate|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)
MERGE|PersonName|2023/07/01|4712/12/31||GB|GLOBAL|TestEmpFN|U|TestEmp|HDL|TestEmpName_123|TestEmp_123
METADATA|PersonLegislativeData|EffectiveStartDate|EffectiveEndDate|PersonNumber|LegislationCode|Sex|MaritalStatus|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)
MERGE|PersonLegislativeData|2023/07/01|4712/12/31||GB|M|M|HDL|TestEmpLegData_123|TestEmp_123
METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|ActionCode|LegalEmployerName|LegalEmployerSeniorityDate|EnterpriseSeniorityDate|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|ReadyToConvert
MERGE|WorkRelationship||2023/07/01|P|ADD_PEN_WKR|GB Legal Employer|||HDL|TestEmpWR_123|TestEmp_123|Y
METADATA|WorkTerms|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|PersonNumber|EffectiveLatestChange|EffectiveSequence|LegalEmployerName|WorkerType|DateStart|AssignmentStatusTypeCode|BusinessUnitShortCode|ActionCode|PrimaryWorkTermsFlag|ProposedUserPersonType|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)
MERGE|WorkTerms||2023/07/01|4712/12/31||Y|1|GB Legal Employer|P|2023/07/01|ACTIVE_PROCESS|GB Business Unit|ADD_PEN_WKR|Y|Member|HDL|TestEmpWT_123|TestEmp_123|TestEmpWR_123
METADATA|Assignment|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsNumber|AssignmentNumber|AssignmentStatusTypeCode|BusinessUnitShortCode|PersonNumber|WorkerType|DateStart|LegalEmployerName|PrimaryAssignmentFlag|ProposedUserPersonType|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|WorkTermsAssignmentId(SourceSystemId)|ProjectedStartDate
MERGE|Assignment|ADD_PEN_WKR|2023/07/01|4712/12/31|1|Y|||ACTIVE_PROCESS|GB Business Unit||P|2023/07/01|GB Legal Employer|Y|Member|HDL|TestEmpASG_123|TestEmp_123|TestEmpWT_123|2023/07/10
ReadyToConvert Flag on Work relationship is used to convert the Pending worker record to Employee record. If this flag is set to Y, it will appear in NewPerson Dashboard with Automatic conversion marked as Yes.
Run Convert Pending Workers Automatically Process will pick the pending worker and convert it into Worker.
Many a times while trying to use Post method using REST API, we encounter – ” The specified operation is not supported for the invoked HTTP method. Please check the URL and the headers.”
Cause of this error is incorrect header parameters/ missing header parameters while making a call to REST API.
To fix the issue:
3. Set below values:
Key – Content-type
Value – application/vnd.oracle.adf.action+json
Once the header values are set, the error will go away:
METADATA|RoleMapping|RoleMappingId|MappingName|DateFrom|DateTo|LegalEmployerName|SystemPersonType|UserPersonType|AssignmentType|AssignmentStatus|SourceSystemId|SourceSystemOwner
MERGE|RoleMapping||Test HDL|1951/01/01|4712/12/31|Test Legal Employer|Employee|Employee|E|ACTIVE|RoleMapping_123|HRC_SQLLOADER
METADATA|Role|RoleMappingRoleId|RoleMappingId(SourceSystemId)|MappingName|RoleId|RequestableFlag|SelfRequestableFlag|UseForAutoProvisioningFlag|RoleCommonName|SourceSystemId|SourceSystemOwner
MERGE|Role||RoleMapping_123|Test HDL||N|N|Y|TEST_EMP_DATA|Role_123|HRC_SQLLOADER
The rules can be verified from UI, once the HDL load is successful:
Below query can be used to pull actions and configured actions for these reasons along with action type:
SELECT pav.action_type_code
,pav.action_code
,pav.action_name
,parv.action_reason_code
,parv.action_reason
,to_char(aru.start_date, 'yyyy/mm/dd') start_date
,to_char(aru.end_date, 'yyyy/mm/dd') end_date
FROM PER_ACTION_REASON_USAGES aru,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE 1=1
AND aru.action_id = pav.action_id
AND aru.action_reason_id = parv.action_reason_id
AND pav.action_code = 'CHANGE_SALARY'
ORDER BY 1,3,5
METADATA|Location|SourceSystemOwner|SourceSystemId|LocationCode|LocationName|EffectiveStartDate|EffectiveEndDate|SetCode|ActiveStatus|AddressLine1|Country|Description|AddressLine2|AddressLine3|AddressLine4|Building|FloorNumber|PostalCode|Region1|Region2|Region3|TimezoneCode|TownOrCity|FLEX:PER_LOCATIONS_DF|dffType(PER_LOCATIONS_DF=Global Data Elements)
MERGE|Location|HRC_SQLLOADER|TEST_LOCATION_1|TEST_LOCATION|Test Location|1951/01/01|4712/12/31|COMMON|A|1 Churchill Place|GB|1 Churchill Place||||||||||Europe/London|London|Global Data Elements|Sample DFF value
Below are some important my oracle support notes related to diagnostic framework:
Self-Service Data Integrity Framework for Employment Flows – Part 2 (Doc ID 2597759.1)
Self-Service Data Integrity Framework for Employment Flows – Part 1 (Doc ID 2548287.1)
Self-Service Data Integrity Framework for Person Flows (Doc ID 2548789.1)
Self-Service Data Integrity Framework for Workforce Structure Flows (Doc ID 2548827.1)
Fusion Global HR: Corruption Type in Person Diagnostic Auto Correct Report (Doc ID 2619978.1)
Check below link to see the privilege’s required for run Diagnostics:
PersonDeliveryMethod child business object of Worker can be used to upload delivery preferences for a worker.
Below is sample HDL file:
METADATA|PersonDeliveryMethod|DeliveryMethodId|DateStart|DateEnd|PersonId|PersonNumber|PreferredOrder|CommDlvryAddress|CommDlvryMethod|CommDlvryFkId|AddressType|AddressLine1|PhoneType|PhoneNumber|EmailType|EmailAddress|SourceSystemOwner|SourceSystemId
MERGE|PersonDeliveryMethod||2023/01/01|4712/12/31||1234|1||NORMAL||HOME|Address Line 1|||||HRC_SQLLOADER|1234_HOME_Address Line 1
Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId|SourceSystemId|SourceSystemOwner|ActionCode|ActionReasonCode' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
SALARY_AMOUNT||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id||'|'||
hikm.source_system_id||'|'||
source_system_owner||'|'||
pav.action_code||'|'||
'XX_ANNL_REVIEW' data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam,
hrc_integration_key_map hikm,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type not like '%T'
AND cs.salary_id = hikm.surrogate_id
and cs.action_id = pav.action_id
and cs.action_reason_id = parv.action_reason_id
and parv.action_reason_code = 'CMP_ANNV'
ORDER BY data_flow_order
Sometimes accessing an HCM environment causes in “Max HTTP Headers, Blocked by WAF” error as shown below:
Resolution:
This issue occurs due to browser cache issue. Clearing the cache or trying to access the environment in incognito mode or another browser should resolve the issue.
Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:
Select pjft.name
,pjf.job_code
,TO_CHAR(pjf.effective_start_date,'YYYY/MM/DD') start_date
,TO_CHAR(pjf.effective_end_date,'YYYY/MM/DD') end_Date
,(select add_set.set_code
from fnd_setid_sets add_set
where add_set.set_id = pjf.set_id
and add_set.language = USERENV('LANG')) set_code
,pjf.active_status
from per_jobs_f pjf,
per_jobs_f_tl pjft
where 1=1
and pjf.job_id = pjft.job_id
and pjft.language = USERENV('LANG')
and trunc(SYSDATE) between pjft.effective_start_date and pjft.effective_end_date
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
ORDER BY 2,4
There are requirements when the existing legal entity name should be updated to a new name. One should note that the LE name in UI is displayed for a view – HR_ALL_ORGANIZATION_UNITS_F_VL.
Sometimes, even after updating the name in UI, correct name is not reflected on some pages like Manage Employment or Manage Legal Entity Information for HCM.
In such cases, one should verify the value in HR_ALL_ORGANIZATION_UNITS_F_VL view to make sure the updated value is showing here.
If updated value is not available in this view, please follow below steps:
This should force the name update to happen in HR_ALL_ORGANIZATION_UNITS_F_VL view.
Below query can be used to extract checklist configuration details:
select PER_CHECKLISTS_TL.NAME
,PER_CHECKLISTS_TL.DESCRIPTION checklist_desc
,PER_TASKS_IN_CHECKLIST_B.CHECKLIST_TASK_NAME tin_ctn
,PER_TASKS_IN_CHECKLIST_B.DESCRIPTION task_desc
,PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_ID
,PER_TASKS_IN_CHECKLIST_B.TASK_CONFIGURATION
,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID
,PER_CHECKLIST_TASKS_B.TASK_LEVEL_CODE
,PER_CHECKLIST_TASKS_B.TASK_LEVEL_VALUE
,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_CODE
,PER_CHECKLIST_TASKS_B.TASK_CATEGORY
,PER_CHECKLIST_TASKS_B.ACTION_TYPE
,PER_CHECKLIST_TASKS_B.TASK_ACTION_ID ctb_action_id
,PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
,PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_NAME ctc_ctn
,PER_CHECKLIST_TASKS_TL.DESCRIPTION
,PER_CHECKLIST_TASKS_TL.ACTION_URL
,PER_CHECKLIST_TASKS_TL.USER_DISPLAY_NAME
FROM PER_CHECKLISTS_TL PER_CHECKLISTS_TL
,PER_TASKS_IN_CHECKLIST_VL PER_TASKS_IN_CHECKLIST_B
,PER_CHECKLIST_TASKS_B PER_CHECKLIST_TASKS_B
,PER_CHECKLIST_TASKS_TL PER_CHECKLIST_TASKS_TL
WHERE PER_CHECKLIST_TASKS_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
AND PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID = PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_ID
AND PER_CHECKLIST_TASKS_TL.LANGUAGE = 'US'
AND PER_CHECKLISTS_TL.LANGUAGE = 'US'
AND PER_TASKS_IN_CHECKLIST_B.CHECKLIST_ID = PER_CHECKLISTS_TL.CHECKLIST_ID
AND PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_CODE = PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
AND PER_TASKS_IN_CHECKLIST_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
HDL provides an option to upload Potential life events for a person in benefits. Please note that ‘Open Enrolment’ life event can’t be loaded using the potential life events file.
Provide the data in below format and save it as PotentialLifeEvents.dat
METADATA|PotentialLifeEvents|PersonNumber|LegalEmployer|BenefitRelationName|LifeEventName|LifeEventStatusCode|LifeEventOccuredDate|UnprocessedDate|NotificationDate|DetectedStatusDate|ManualStatusDate|ManualOverrideStatusDate|ProcessedDate|VoidedStatusDate|PtnlLerForPerSrcCd|SourceSystemId|SourceSystemOwner|LerId|PtnlLerForPerId|PersonId|LegalEntityId|BenefitRelationId|LifeEventTypeCode|ProdCd
MERGE|PotentialLifeEvents|123718|XX Test|DFLT|New Hire|UNPROCD|2023/02/01|2023/02/01||||||||123718_2023/02/01|HRC_SQLLOADER|||||||