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 1Below 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 1Oracle 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 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 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,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
)There are multiple primary flags for a worker in assignment table. Namely – Primary Assignment Flag, Primary Work Terms flag and Primary flag.
Below query can be used to check these flags:
--> Primary Flags Query
SELECT papf.person_number
,ppnf.first_name
,ppnf.last_name
,to_char(ppos.date_start,'RRRR/MM/DD') start_date
,paam.assignment_number
,to_char(paam.effective_start_date,'RRRR/MM/DD') asg_eff_start_date
,to_char(paam.effective_end_date,'RRRR/MM/DD') asg_eff_end_date
,paam.action_code
,pastt.user_status
,paam.assignment_status_type
,paam.primary_flag
,paam.primary_assignment_flag
,paam.primary_work_relation_flag
,ppos.primary_flag "WR Table Primary Flag"
FROM PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
,PER_ALL_ASSIGNMENTS_M paam
,PER_PERIODS_OF_SERVICE ppos
,PER_ASSIGNMENT_STATUS_TYPES_TL pastt
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = paam.person_id
AND paam.period_of_service_id = ppos.period_of_service_id
AND paam.assignment_status_type_id = pastt.assignment_status_type_id
AND paam.effective_sequence = 1
AND paam.assignment_type NOT LIKE '%T'
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 pastt.language = 'US'
AND papf.person_number IN ('500035','500036')
ORDER BY 1, 6 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
)While performing SIT/UAT, sometimes it becomes mandatory to disable (bypass) all configured approval rules except for a few. One has to go in manually in Transaction console and check the status for each task.
An easy way to run the following SQL query and check the tasks for which approvals are yet not Bypassed:
SELECT HRPB.TASK_FILE_NAME TaskName
,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
,haao.last_update_date
,haao.last_updated_by
FROM FUSION.HRC_ARM_PROCESS_B HRPB
,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
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 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
)