SELECT fpob.profile_option_name, fpov.level_name, fpov.level_value, fpov.profile_option_value
FROM fnd_profile_options_b fpob
,fnd_profile_option_values fpov
WHERE fpob.profile_option_id = fpov.profile_option_id
AND fpob.profile_option_name = 'FND_FILE_UPLOAD_MAX_SIZE'Select papfemp.person_number
, ppnfemp.first_name person_first_name
, ppnfemp.last_name person_last_name
, paamemp.assignment_number person_assignment
, to_char(pasf.effective_start_date, 'YYYY-MM-DD') effective_start_date
, to_char(pasf.effective_end_date, 'YYYY-MM-DD') effective_end_date
, papf_mgr.person_number manager_number
, ppnf_mgr.first_name manager_first_name
, ppnf_mgr.last_name manager_last_name
, paam_mgr.assignment_number manager_assignment
, pasf.manager_type
From per_all_people_f papfemp
, per_person_names_f ppnfemp
, per_all_assignments_m paamemp
, per_all_people_f papf_mgr
, per_person_names_f ppnf_mgr
, per_all_assignments_m paam_mgr
, per_assignment_supervisors_f pasf
Where ppnfemp.person_id = papfemp.person_id
And ppnfemp.name_type = 'GLOBAL'
And paamemp.person_id = papfemp.person_id
And paamemp.assignment_type In ('E', 'C', 'N')
And paamemp.effective_latest_change = 'Y'
And paamemp.effective_start_date Between papfemp.effective_start_date And papfemp.effective_end_date
And paamemp.effective_start_date Between ppnfemp.effective_start_date And ppnfemp.effective_end_date
And TRUNC(SYSDATE) Between paamemp.effective_start_date And paamemp.effective_end_date
And paamemp.effective_start_date Between pasf.effective_start_date And pasf.effective_end_date
And ppnf_mgr.person_id = papf_mgr.person_id
And ppnf_mgr.name_type = 'GLOBAL'
And paam_mgr.person_id = papf_mgr.person_id
And paam_mgr.assignment_type In ('E', 'C', 'N')
And paam_mgr.effective_latest_change = 'Y'
And paam_mgr.effective_start_date Between papf_mgr.effective_start_date And papf_mgr.effective_end_date
And paam_mgr.effective_start_date Between ppnf_mgr.effective_start_date And ppnf_mgr.effective_end_date
And paamemp.effective_start_date Between paam_mgr.effective_start_date And paam_mgr.effective_end_date
And pasf.person_id = papfemp.person_id
And pasf.assignment_id = paamemp.assignment_id
And pasf.manager_id = papf_mgr.person_id
And pasf.manager_assignment_id = paam_mgr.assignment_idselect 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'Post P2T, there is always a requirement to mask the candidate email addresses in lower environments. Below SQL can be used to generate data masked email data in HDL format:
SELECT 'METADATA|CandidateEmail|EmailAddressId|PersonId|CandidateNumber|EmailAddress|DateFrom|DateTo|SourceSystemOwner|SourceSystemId' "Record"
FROM DUAL
UNION
SELECT DISTINCT 'MERGE' || '|'||
'CandidateEmail' || '|'||
ICEA.EMAIL_ADDRESS_ID || '|'||
ICEA.PERSON_ID || '|'||
ICEA.CANDIDATE_NUMBER || '|'||
replace(PEAD.EMAIL_ADDRESS,'.com','.invalidcomx') || '|'||
TO_CHAR(PEAD.DATE_FROM,'YYYY/MM/DD') || '|'||
TO_CHAR(PEAD.DATE_TO,'YYYY/MM/DD') || '|'||
HIKM.SOURCE_SYSTEM_OWNER || '|'||
HIKM.SOURCE_SYSTEM_ID "Record"
FROM IRC_CAND_EMAIL_ADDRESS_V ICEA,
PER_EMAIL_ADDRESSES PEAD,
HRC_INTEGRATION_KEY_MAP HIKM
WHERE ICEA.EMAIL_ADDRESS_ID = PEAD.EMAIL_ADDRESS_ID
AND HIKM.SURROGATE_ID = PEAD.EMAIL_ADDRESS_IDYou can update the email address as per your requirement.
Use the below SQL to extract assignment rules defined at course level:
SELECT wlift.NAME
,war.DEFAULT_INITIAL_REC_SUBSTA
,war.DEFAULT_INITIAL_ACT_SCORE
,war.DEFAULT_INITIAL_COMMENTS
,war.DEFAULT_INITIAL_COMPL_DATE
,war.DEFAULT_INITIAL_TOTACT_EFF
,war.DEFAULT_INITIAL_REASON_CODE
,war.INITIAL_DUE_DATE_OPTION
,war.INITIAL_DUE_DATE
,war.INITIAL_DUE_IN_DAYS
,war.VALIDITY_OPTION
,war.EXPIRY_OPTION
,war.EXPIRY_IN_DAYS
,war.EXPIRY_DATE
,war.EXPIRY_IN_NUM_YRS
,war.RENEWAL_OPTIONS
,war.RENEWAL_BEFORE_EXPIRY_DAYS
FROM WLF_ASSIGNMENT_RULES war
,WLF_LEARNING_ITEMS_F_TL wlift
,WLF_LEARNING_ITEMS_F wlif
WHERE war.ASSIGNMENT_RULE_ID = wlif.ASSIGNMENT_RULE_ID
AND wlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID
AND wlift.language = 'US'
AND TRUNC(sysdate) BETWEEN wlif.effective_start_date AND wlif.effective_end_date
AND TRUNC(sysdate) BETWEEN wlift.effective_start_date AND wlift.effective_end_dateBelow HDL file can be used to DELETE the schedule assignment data in a Fusion environment:
METADATA|ScheduleAssignment|ResourceType|ScheduleAssignmentId|SourceSystemId|SourceSystemOwner
DELETE|ScheduleAssignment|ASSIGN|300000117113937|SCHEDULE_E1000001_30-MAY-2017-31-DEC-2017_0-8-8-8-8-0-0|EBS
DELETE|ScheduleAssignment|ASSIGN|300000117309993|SCHEDULE_E1000002_04-JUN-2018-31-DEC-4712_0-8-8-8-8-0-0|EBSThis data can be extracted from Fusion HCM using a simple BIP query:
SELECT DATA_ROW
FROM (
SELECT 'METADATA|ScheduleAssignment|ResourceType|ScheduleAssignmentId|SourceSystemId|SourceSystemOwner' AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE|ScheduleAssignment|',
RESOURCE_TYPE,
SCHEDULE_ASSIGNMENT_ID,
source_system_id,
source_system_owner AS DATA_ROW
from fusion.per_schedule_assignments psa
,hrc_integration_key_map hikm
where psa.SCHEDULE_ASSIGNMENT_ID = hikm.surrogate_id
)Use below SQL query to extract email addresses data in HDL format to DELETE the data:
SELECT DATA_ROW
FROM (
SELECT 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|DateTo|EmailType|EmailAddress|SourceSystemId|SourceSystemOwner' AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
'PersonEmail' ||'|'||
pea.email_address_id||'|'||
pea.person_id ||'|'||
to_char(pea.date_from,'RRRR/MM/DD')||'|'||
to_char(pea.date_to,'RRRR/MM/DD') ||'|'||
pea.email_type ||'|'||
pea.email_address ||'|'||
(select email_hrc.source_system_id
from hrc_integration_key_map email_hrc
WHERE pea.email_Address_id = email_hrc.surrogate_id) ||'|'||
(select email_hrc.source_system_owner
from hrc_integration_key_map email_hrc
WHERE pea.email_Address_id = email_hrc.surrogate_id) AS DATA_ROW
from per_email_addresses pea
)If you only want to update the email addresses not delete them, then use refer below post:
HDL – Email Data Obfuscation in Test environment – Welcome to Fusion HCM Consulting
Query to extract Element entry details data in HDL format:
SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'ElementEntry'
|| CHR (124) || 'ElementEntryId'
|| CHR (124) || 'EffectiveStartDate'
|| CHR (124) || 'SourceSystemId'
|| CHR (124) || 'SourceSystemOwner' AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'
|| CHR (124) || 'ElementEntry'
|| CHR (124) || peef.element_entry_id
|| CHR (124) || to_char(peef.effective_start_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM PAY_ELEMENT_ENTRIES_F peef,
HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = peef.element_entry_id
)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(+)Calendar events are used in HCM to capture public holidays available to employees based on their location or department.
Below SQL queries can be used to extract the list of configured calendar events from backend table:
List of all calendar events configured:
SELECT pcetl.name
,pce.short_code
,pcetl.description
,pce.category
,pce.coverage_type
,pce.start_date_time
,pce.end_date_time
,pce.tree_code
,pce.tree_structure_code
FROM per_calendar_events pce,
per_calendar_events_tl pcetl
WHERE pce.calendar_event_id=pcetl.calendar_event_id
AND pcetl.LANGUAGE=userenv('LANG')List of calendar events by location:
SELECT hla.location_id
,hla.location_name
,hla.internal_location_code
,pcet.name
,pce.short_code
,pcet.description
,pce.category
,pce.coverage_type
,pce.start_date_time
,pce.end_date_time
,pce.tree_code
,pce.tree_structure_code
FROM hr_locations_all hla
,per_calendar_events pce
,per_calendar_events_tl pcet
,per_cal_event_coverage pcec
,per_geo_tree_node_rf pgtnr
WHERE hla.geo_hierarchy_node_value = pgtnr.ancestor_pk1_value
AND pgtnr.tree_structure_code = 'PER_GEO_TREE_STRUCTURE'
AND pgtnr.distance in (0)
AND pgtnr.ancestor_tree_node_id = pcec.tree_node_id
AND pcec.calendar_event_id = pcet.calendar_event_id
AND pcet.calendar_event_id = pce.calendar_event_id
AND pcet.language = userenv('lang')
AND pcec.coverage_flag in ('I')
AND trunc(sysdate) between hla.effective_start_date and hla.effective_end_dateOracle provides HCMGroup.dat which can be used to assign members against HCM Group. However, the business object has a major limitation (till release version 22A) that it can be used only for below two seeded Oracle HCM Groups:
Below is the sample HDL file to add members to Audit HCM Group:
METADATA|HCMGroup|GrpMemberId|EffectiveStartDate|GrpId|MemberId|GroupCode|PersonNumber|SourceSystemOwner|SourceSystemId|GUID|EffectiveEndDate
MERGE|HCMGroup||2022/01/04|||MANUALLY MAINTAINED AUDIT MEMBERS|1014|HRC_SQLLOADER|MANUALLY MAINTAINED AUDIT MEMBERS_1014||For any custom HCM Groups, this can’t be used. If you try to use any custom Group Code in the HDL file, you will receive below error:
| Valid values for attribute GROUP CODE: MANUALLY MAINTAINED AUDIT MEMBERS,MANUALLY MAINTAINED EXCLUDED MEMBERS. |
Query to extract members of an HCM Group:
SELECT hgt.group_name
,papf.person_id
,papf.person_number
,ppnf.full_name
,ppnf.last_name
,ppnf.first_name
,hgmf.effective_start_date
,hgmf.effective_end_date
FROM PER_ALL_PEOPLE_F papf,
PER_PERSON_NAMES_F ppnf,
HWM_GRP_MEMBERS_F hgmf,
HWM_GRPS_B hgb,
HWM_GRPS_TL hgt
WHERE hgb.grp_id = hgmf.grp_id
AND hgb.grp_id = hgt.grp_id
AND hgt.language = 'US'
AND papf.person_id = hgmf.member_id
AND papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND trunc(sysdate) BETWEEN hgmf.effective_start_date and hgmf.effective_end_date
AND trunc(sysdate) BETWEEN ppnf.effective_start_date and ppnf.effective_end_date
ORDER BY 1,3 Loading the profile data against a worker/job/position is little tricky one. One has to understand the templates thoroughly before uploading any data.
Below are some of the most commonly used queries to extract some of the required information for loading Talent profiles data.
Extract Person Profile Code:
SELECT distinct papf.person_id, papf.person_number,ProfilePEO.profile_code
FROM HRT_PROFILES_VL ProfilePEO, PER_ALL_PEOPLE_F papf
WHERE papf.person_id = ProfilePEO.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date and papf.effective_end_dateExtract Profile Type Id:
Select * from HRT_PROFILE_TYPES_TL
For many BIP reports, it is required to publish the environment url. One can easily hardcode it in SQL query, however, when P2T or T2T happens, the same url gets copied to target instance which is incorrect. The better way of doing it is to use below piece of code in custom SQL to extract environment url dynamically:
SELECT 'https://'||EXTERNAL_VIRTUAL_HOST INSTANCE_NAME
FROM FUSION.ASK_DEPLOYED_DOMAINS
WHERE DEPLOYED_DOMAIN_NAME = 'FADomain'SELECT
fvvs.value_set_code
,fvvv.value
,fvvv.description
,fvvv.enabled_flag
,to_char(fvvv.start_date_active,'YYYY/MM/DD') start_date_active
,to_char(fvvv.end_date_active,'YYYY/MM/DD') end_date_active
,fvvv.summary_flag
,fvvv.translated_value
,fvvv.independent_value
,fvvv.independent_value_number
,to_char(fvvv.independent_value_date,'YYYY/MM/DD') independent_value_date
,fvvv.independent_value_timestamp
,fvvv.value_number
,to_char(fvvv.value_date,'YYYY/MM/DD') value_date
,fvvv.value_timestamp
,fvvv.sort_order
FROM
fnd_vs_values_vl fvvv,
fnd_vs_value_sets fvvs
WHERE fvvv.value_set_id = fvvs.value_set_id
order by fvvs.value_set_code
SELECT csb.name
,csb.salary_basis_code
,csb.salary_annualization_factor
,pld.name LDG
,petft.element_name
,pivt.name input_value_name
,rate.name rate_name
FROM cmp_salary_bases csb,
per_legislative_data_groups_tl pld,
pay_element_types_tl petft,
pay_input_values_f pivf,
pay_input_values_tl pivt,
per_rates_f_tl rate
WHERE 1=1
and csb.legislative_data_group_id = pld.legislative_data_group_id
and pld.language = USERENV('LANG')
and csb.element_type_id = petft.element_type_id
-- and trunc(sysdate) between petft.effective_start_date and petft.effective_end_date
and petft.language = USERENV('LANG')
and csb.element_type_id = pivf.element_type_id
and csb.input_value_id = pivf.input_value_id
and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date
and pivf.input_value_id = pivt.input_value_id
and pivt.language = USERENV('LANG')
and csb.grade_rate_id = rate.rate_id(+)
and NVL(rate.language,USERENV('LANG')) = USERENV('LANG')
ORDER BY 1