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 dualOutput:
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:
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 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_orderOnce 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,4Below 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'SELECT poga.object_id
,petft.element_name
,poga.include_or_exclude
FROM pay_object_groups pog
,pay_object_group_amends poga
,pay_element_types_tl petft
WHERE poga.object_group_id = pog.object_group_id
AND pog.base_object_group_name='Test Element Set'
AND petft.element_type_id = poga.object_id
AND petft.language = 'US'There is no easy way in OTBI to find list of jobs which don’t have a model profile associated with them. This can be achieved easily using BIP query.
Sample Query:
SELECT * FROM PER_JOBS_F pjf
WHERE trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
AND NOT EXISTS ( SELECT 1
FROM HRT_PROFILE_ITEMS hpi
,HRT_PROFILES_B hpb
,HRT_PROFILE_RELATIONS hpr
WHERE hpi.profile_id = hpb.profile_id
AND hpb.profile_usage_code = 'M'
AND hpi.profile_id = hpr.profile_id
AND pjf.job_id = hpr.object_id
)SELECT pett.element_name, count(peef.element_type_id)
FROM pay_element_types_tl pett
,pay_element_entries_f peef
WHERE pett.element_type_id = peef.element_type_id
AND pett.language = 'US'
AND to_date('01-04-2022','DD/MM/RRRR') BETWEEN peef.effective_start_date AND peef.effective_end_date
GROUP BY pett.element_nameSELECT papf.person_number
FROM per_all_people_f papf
WHERE 1=1
AND EXISTS (SELECT 1 FROM per_periods_of_service ppos
WHERE ppos.person_id = papf.person_id
AND (ppos.actual_termination_date IS NULL or ppos.actual_termination_date>= TRUNC(SYSDATE))
GROUP BY ppos.person_id
HAVING COUNT (ppos.period_of_service_id) >1
)SELECT papf.person_number
FROM PER_ALL_PEOPLE_F papf
,PER_PERIODS_OF_SERVICE ppos
WHERE 1=1
AND papf.person_id = ppos.person_id
AND (ppos.actual_termination_date IS NULL OR ppos.actual_termination_date >= TRUNC(SYSDATE))
AND NOT EXISTS (SELECT 'Found' FROM PAY_PAY_RELATIONSHIPS_DN pprd
,PAY_PERSON_PAY_METHODS_F pppmf
WHERE pprd.payroll_relationship_id = pppmf.payroll_relationship_id
AND pprd.person_id = papf.person_id)In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:
SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner' AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
'Position' ||'|'||
hapf.Position_Id||'|'||
to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
(select email_hrc.source_system_id
from hrc_integration_key_map email_hrc
WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
(select email_hrc.source_system_owner
from hrc_integration_key_map email_hrc
WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
from hr_all_positions_f hapf
)select papf.person_number
,hdor.document_code
,hdor.document_name
,hdor.document_number
,fdt.file_name
,fdt.dm_version_number document_id
,fdt.dm_document_id UCM_file
from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
,per_all_people_f papf
,hr_document_types_tl hdtt
where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
and hdor.documents_of_record_id = fad.PK1_VALUE
and fad.document_id = fdt.document_id
and fdt.language = 'US'
and hdor.person_id = papf.person_id
and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
and hdor.document_type_id = hdtt.document_type_id
and hdtt.language = 'US'
and hdtt.DOCUMENT_TYPE = 'Test Doc'Oracle has released a new subject area for Areas of Responsibility.
Subject Area Name – Workforce Management – Areas of Responsibility
This has simplified the process of extracting AoR data using OTBI.
To get any data in this subject area, you should run “Refresh Representative Data” ESS process. The process should be run post any changes done to AoR’s.
To get data in this SA below duties should be assigned:
FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY
ORA_FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY_HCMPlease note that these duties should be added to Job Role not the Data Role.
Query for bank details:
select * from ce_banks_vQuery for bank branch details:
select * from ce_bank_branches_v
select cbbv.bank_name
,cbbv.bank_branch_name
,cbbv.bank_home_country
,cbbv.branch_number
,TO_CHAR(cbbv.start_date,'YYYY/MM/DD') branch_start_date
,TO_CHAR(cbbv.end_date,'YYYY/MM/DD') branch_end_date
from ce_bank_branches_v cbbvQuery to extract Internal bank accounts:
select * from ce_bank_accounts
Query to extract External bank accounts:
select * from iby_ext_bank_accountsSometime for data migration, it is required to get a dump of all the absence reasons configured in Fusion HCM in order to validate the data being loaded.
Below query can be used to extract the setup data for absence reasons:
SELECT aarft.name absence_reason_name
,to_char(aarf.effective_start_date,'YYYY/MM/DD') reason_start_date
,to_char(aarf.effective_end_date,'YYYY/MM/DD') reason_end_date
,aarf.legislation_code legislation_code
,aarf.status reason_status
,aarft.description reason_description
,aarf.base_name reason_base_name
FROM ANC_ABSENCE_REASONS_F aarf
,ANC_ABSENCE_REASONS_F_TL aarft
WHERE aarf.absence_reason_id=aarft.absence_reason_id
AND aarf.effective_start_date BETWEEN aarft.effective_start_date AND aarft.effective_end_date
AND aarft.language=userenv('LANG')select * from (
SELECT DISTINCT
papf.person_number,
paam.assignment_number,
pgf.name grade_name,
pgsf.name grade_step_name,
prv.value rate_value
FROM
PER_GRADES_F_tl PGF,
PER_GRADES_F PG,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_ASSIGN_GRADE_STEPS_F pagsf,
PER_GRADE_STEPS_F_TL PGSF ,
PER_RATE_VALUES_F prv
WHERE
1=1
AND PG.grade_id = PGF.grade_id
AND PAAM.grade_id = PGF.grade_id
AND paam.person_id = papf.person_id
and paam.assignment_type = 'E'
and PGF.language = 'US'
AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
AND TRUNC(SYSDATE) BETWEEN PAAM.effective_start_date AND PAAM.effective_end_date
AND TRUNC(SYSDATE) BETWEEN PGF.effective_start_date AND PGF.effective_end_date
AND TRUNC(SYSDATE) BETWEEN PG.effective_start_date AND PG.effective_end_date
AND pagsf.assignment_id (+) = paam.assignment_id
and pagsf.grade_step_id = PGSF.grade_step_id (+)
and paam.effective_start_date between pagsf.effective_start_date (+) and pagsf.effective_end_date (+)
and paam.effective_start_date between PGSF.effective_start_date (+) and PGSF.effective_end_date (+)
and PGSF.language (+) = 'US'
and PGSF.grade_step_id = PRV.rate_object_id (+)
) a
where grade_step_name is not nullSuccession Plans data is stored in HRM_PLANS table. A succession plan can a PUBLIC or PRIVATE plan. By default if any user accesses HRM_PLANS table, user will be able to see all plans data irrespective of plan type (public or private). The requirement here is that a person who is querying data should be able to see all PUBLIC succession plans and only those PRIVATE plans which are defined by the user.
This can be achieved using below SQL:
select * from HRM_PLANS hp
where hp.ACCESS_TYPE_CODE = 'PUBLIC'
UNION
select * from HRM_PLANS hp
where hp.ACCESS_TYPE_CODE = 'PRIVATE'
and exists (select 1 from HRM_PLAN_OWNERS hpo
where person_id = hrc_session_util.get_user_personid
and hpo.plan_id = hp.plan_id)SELECT pcafv.collective_agreement_name
,TO_CHAR(pcafv.effective_start_date,'YYYY/MM/DD') Start_Date
,pcafv.legislation_code
,pcafv.status
,pcafv.identification_code
,pcafv.description
,pcafv.comments
,houf_union.name Union_Name
,pcafv.bargaining_unit_code
,ple.name Legal_Employer
,pcafv.employee_org_name
,pcafv.employee_org_contact
,pcafv.employer_org_name
,pcafv.employer_org_contact
FROM per_col_agreements_f_vl pcafv
,hr_all_organization_units_f_vl houf_union
,per_legal_employers ple
WHERE pcafv.legal_entity_id = ple.organization_id(+)
AND pcafv.union_id = houf_union.organization_id(+)
AND trunc(SYSDATE) BETWEEN pcafv.effective_start_date AND pcafv.effective_end_date
AND trunc(SYSDATE) BETWEEN houf_union.effective_start_date(+) AND houf_union.effective_end_date(+)
AND trunc(SYSDATE) BETWEEN ple.effective_start_date(+) AND ple.effective_end_date(+)