Search for:
Aug 25 2022
BIP – Query to extract Disability Org Details

Use below query to extract disability org details from Fusion HCM:

SELECT houft.name disability_org_name
      ,TO_CHAR(haouf.effective_start_date,'RRRR/MM/DD') disability_org_start_date
      ,TO_CHAR(haouf.effective_end_date,'RRRR/MM/DD') disability_org_end_date
      ,houcf.classification_code
      ,haouf.internal_address_line
      ,houcf.status
  FROM hr_org_unit_classifications_f houcf
     , hr_all_organization_units_f haouf
     , hr_organization_units_f_tl houft
 WHERE houcf.organization_id = haouf.organization_id
   AND houft.organization_id = haouf.organization_id
   AND TRUNC(SYSDATE) BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
   AND haouf.effective_start_date BETWEEN houft.effective_start_date AND houft.effective_end_date
   AND houft.language = USERENV('LANG')
   AND houcf.category_code = 'HCM_DISABILITY_ORGANIZATION'
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