Supervisor Query

SELECT    papf.person_number
         ,TO_CHAR (pps.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_termination_date
         ,TO_CHAR (paaf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_start_date
         ,TO_CHAR (paaf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_end_date
         ,TO_CHAR (pasf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_start_date
         ,TO_CHAR (pasf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_end_date
         ,pasf.manager_type
         ,pasf.primary_flag
         ,paaf.assignment_number
         ,papfs.person_number manager_number
         ,TO_CHAR (ppss.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_hire_date
         ,TO_CHAR (ppss.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_termination_date
FROM      per_periods_of_service ppss
         ,per_all_people_f papfs
         ,per_assignment_supervisors_f pasf
         ,per_all_people_f papf
         ,per_all_assignments_m paaf
         ,per_periods_of_service pps
WHERE         ppss.person_id(+) = papfs.person_id
          AND pasf.effective_start_date BETWEEN papfs.effective_start_date(+) AND papfs.effective_end_date(+)
          AND papfs.person_id(+) = pasf.manager_id
          AND paaf.effective_start_date BETWEEN pasf.effective_start_date(+) AND pasf.effective_end_date(+)
          AND pasf.assignment_id(+) = paaf.assignment_id
          AND papf.person_number = NVL (:p_person_number, papf.person_number)
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = paaf.person_id
          AND paaf.effective_latest_change = 'Y'
          AND paaf.primary_assignment_flag = 'Y'
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.period_of_service_id = pps.period_of_service_id
ORDER BY  papf.person_number
         ,paaf.effective_start_date

Element Links

SELECT    pldgv.name legislative_data_group
         ,pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,TO_CHAR (pelf.effective_start_date
                  ,'YYYY/MM/DD')
             effective_start_date
         ,TO_CHAR (pelf.effective_end_date
                  ,'YYYY/MM/DD')
             effective_end_date
         ,haou.name legal_entity
         ,ppg.segment1 people_group_segment1
         ,ppg.segment2 people_group_segment2
         ,ppg.segment3 people_group_segment3
         ,pcac.source_sub_type
         ,pcac.segment1 cost_segment1
         ,pcac.segment2 cost_segment2
         ,pcac.segment3 cost_segment3
         ,pcac.segment4 cost_segment4
         ,pcac.segment5 cost_segment5
         ,pcac.segment6 cost_segment6
         ,pcac.segment7 cost_segment7
         ,pcac.segment8 cost_segment8
         ,pcif.costable_type costing_type
         ,pcif.transfer_to_gl_flag
FROM      pay_cost_alloc_accounts pcac
         ,pay_cost_allocations_f pcaf
         ,pay_cost_info_f pcif
         ,per_people_groups ppg
         ,hr_all_organization_units haou
         ,pay_element_criteria pec
         ,pay_element_links_f pelf
         ,pay_ele_classifications_tl pect
         ,per_legislative_data_groups_vl pldgv
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
WHERE         pcac.cost_allocation_record_id(+) = pcaf.cost_allocation_record_id
          AND pelf.effective_start_date BETWEEN pcaf.effective_start_date(+) AND pcaf.effective_end_date(+)
          AND pcaf.source_type(+) = 'EL'
          AND pcaf.source_id(+) = pelf.element_link_id
          AND pelf.effective_start_date BETWEEN pcif.effective_start_date(+) AND pcif.effective_end_date(+)
          AND pcif.source_type(+) = 'EL'
          AND pcif.source_id(+) = pelf.element_link_id
          AND ppg.people_group_id(+) = pec.people_group_id
          AND haou.organization_id(+) = pec.legal_employer_id
          AND pec.element_criteria_id(+) = pelf.element_criteria_id
          AND pelf.element_type_id(+) = pett.element_type_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pldgv.legislative_data_group_id = petf.legislative_data_group_id
          AND petf.effective_start_date = (SELECT  MAX (petfi.effective_start_date)
                                           FROM    pay_element_types_f petfi
                                           WHERE   petfi.element_type_id = petf.element_type_id)
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date




SELECT    pect.classification_name link_classification_name
         ,pett.element_name link_element_name
         ,pelf.element_link_name link_element_link_name
         ,TO_CHAR (plivf.effective_start_date
                  ,'YYYY/MM/DD')
             link_effective_start_date
         ,TO_CHAR (plivf.effective_end_date
                  ,'YYYY/MM/DD')
             link_effective_end_date
         ,pivt.name link_input_name
         ,pcif.costable_type link_costable_type
         ,pcif.costed_flag link_costed_flag
         ,pcif.transfer_to_gl_flag link_transfer_to_gl_flag
FROM      pay_ele_classifications_tl pect
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
         ,pay_element_links_f pelf
         ,pay_input_values_tl pivt
         ,pay_link_input_values_f plivf
         ,pay_cost_info_f pcif
WHERE         pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pcif.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
          AND pett.element_type_id = pelf.element_type_id
          AND pcif.effective_start_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
          AND pelf.element_link_id = plivf.element_link_id
          AND pivt.language = 'US'
          AND pivt.input_value_id = plivf.input_value_id
          AND pcif.effective_start_date BETWEEN plivf.effective_start_date AND plivf.effective_end_date
          AND plivf.link_input_value_id = pcif.source_id
          AND pcif.source_type = 'LIV'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date



SELECT    pldg.name ele_leg_data_group_name
         ,pect.classification_name ele_classification_name
         ,pett.element_name ele_element_name
         ,pivf.display_sequence ele_display_sequence
         ,pivt.name ele_input_name
         ,pivf.user_enterable_flag ele_user_enterable_flag
         ,pivf.mandatory_flag ele_mandatory_flag
         ,pivf.hot_default_flag ele_hot_default_flag
         ,hr_general.decode_lookup ('UOM'
                                   ,pivf.uom)
             ele_uom
         ,pivf.user_display_flag ele_user_display_flag
         ,pivf.DEFAULT_VALUE ele_default_value
         ,pivf.lookup_type ele_lookup_type
         ,pivf.min_value ele_min_value
         ,pivf.max_value ele_max_value
         ,pivf.value_set_code ele_value_set_code
FROM      pay_input_values_tl pivt
         ,pay_input_values_f pivf
         ,per_legislative_data_groups_vl pldg
         ,pay_ele_classifications_tl pect
         ,pay_element_types_tl pett
         ,pay_element_types_f petf
WHERE         pivt.language = 'US'
          AND pivt.input_value_id = pivf.input_value_id
          AND petf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
          AND pldg.legislative_data_group_id = petf.legislative_data_group_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pett.language = 'US'
          AND pett.element_type_id = petf.element_type_id
and 1=2
ORDER BY  pect.classification_name
         ,pett.element_name
         ,petf.effective_start_date
         ,pivf.display_sequence

Bank Details Query

SELECT   papf.person_number
,       ppnf.full_name
,           pp.attribute1 initials
,           bank_dtls.bank_account_name
,           bank_dtls.bank_name
,           bank_dtls.bank_branch_name
,          bank_dtls.bank_account_num
,           bank_dtls.iban_number
,           bank_dtls.currency_code
,           bank_dtls.base_org_pay_method_name
FROM    per_all_people_f papf
,    per_person_names_f ppnf
,    per_all_assignments_f paaf
,    pay_rel_groups_dn payrel
,           per_persons pp
,    (SELECT pba.bank_account_name
       ,       pba.bank_name
       ,       pba.bank_branch_name
       ,      pba.bank_account_num
       ,       pba.iban_number
       ,       popf.currency_code
       ,       popf.base_org_pay_method_name
     ,       pppmf.payroll_relationship_id
     FROM  pay_bank_accounts pba
     ,   pay_person_pay_methods_f pppmf
     ,   pay_org_pay_methods_f popf
     WHERE pppmf.bank_account_id = pba.bank_account_id
     AND     pppmf.org_payment_method_id = popf.org_payment_method_id
     AND TRUNC(SYSDATE) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN popf.effective_start_date AND popf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN pba.start_date AND pba.end_date) bank_dtls
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND payrel.payroll_relationship_id = bank_dtls.payroll_relationship_id(+)
AND payrel.assignment_id = paaf.assignment_id
AND papf.person_id = paaf.person_id
AND   paaf.assignment_status_type = 'ACTIVE'
--AND papf.person_number = '44'
-- AND paaf.primary_flag = 'Y'
AND   papf.person_id = pp.person_id
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
--AN D TRUNC(SYSDATE) BETWEen epbad.account_start_date and epbad.account_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN payrel.start_date AND payrel.end_date

Assigned Payrolls

SELECT    papf.person_number
         ,paam.assignment_number
         ,TO_CHAR (pps.date_start
                  ,'YYYY/MM/DD')
             hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'YYYY/MM/DD')
             actual_termination_date
         ,papfp.payroll_name
         ,TO_CHAR (papd.start_date
                  ,'YYYY/MM/DD')
             start_date
         ,TO_CHAR (papd.lsed
                  ,'YYYY/MM/DD')
             close_date
         ,TO_CHAR (papd.lspd
                  ,'YYYY/MM/DD')
             last_standard_process_date
         ,TO_CHAR (papd.fsed
                  ,'YYYY/MM/DD')
             final_process_date
FROM      pay_all_payrolls_f papfp
         ,pay_assigned_payrolls_dn papd
         ,pay_payroll_assignments ppay
         ,per_all_assignments_m paam
         ,per_all_people_f papf
         ,per_periods_of_service pps
WHERE         1 = 1
          AND papd.start_date BETWEEN papfp.effective_start_date(+) AND papfp.effective_end_date(+)
          AND papfp.payroll_id(+) = papd.payroll_id
          AND papd.payroll_term_id(+) = ppay.payroll_term_id
          AND ppay.hr_assignment_id(+) = paam.assignment_id
          AND LEAST (NVL (pps.actual_termination_date
                         ,TO_DATE ('31-12-4712'
                                  ,'DD-MM-YYYY'))
                    ,TO_DATE ('31-12-4712'
                             ,'DD-MM-YYYY')) BETWEEN paam.effective_start_date
                                                 AND paam.effective_end_date
          AND paam.effective_latest_change = 'Y'
          AND paam.assignment_type = 'E'
          AND paam.primary_assignment_flag = 'Y'
          AND paam.primary_flag = 'Y'
          AND paam.period_of_service_id = pps.period_of_service_id
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = pps.person_id
ORDER BY  papf.person_number
         ,papd.start_date

Area of Responsibility AOR Query

SELECT (SELECT DISTINCT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID) PERSON_NUMBER,
    (SELECT DISTINCT FULL_NAME FROM PER_PERSON_NAMES_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID AND NAME_TYPE = 'GLOBAL') PERSON_NAME,
    AOR.RESPONSIBILITY_NAME,
    TO_CHAR(AOR.START_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') START_DATE,
    TO_CHAR(AOR.END_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') END_DATE,
    AOR.RESPONSIBILITY_TYPE,
    AOR.STATUS,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.BUSINESS_UNIT_ID) BUSINESS_UNIT,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.LEGAL_ENTITY_ID) LEGAL_ENTITY,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.ORGANIZATION_ID) DEPARTMENT,
    (SELECT LOCATION_NAME FROM HR_LOCATIONS HL WHERE HL.LOCATION_ID = AOR.LOCATION_ID) LOCATION,
    (SELECT DISTINCT NAME FROM HR_ALL_POSITIONS_F_VL PP WHERE PP.POSITION_ID = AOR.POSITION_ID) POSITION,
    (SELECT DISTINCT NAME FROM PER_JOBS_F_TL PJ WHERE PJ.JOB_ID = AOR.JOB_ID) JOB,
    (SELECT DISTINCT NAME FROM PER_GRADES_F_TL  PG WHERE PG.GRADE_ID = AOR.GRADE_ID) GRADE,
    AOR.ASSIGNMENT_CATEGORY,
    (SELECT PAY.PAYROLL_NAME
            FROM PAY_ALL_PAYROLLS_F    PAY
        WHERE PAY.PAYROLL_ID = AOR.PAYROLL_ID
             AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE  AND PAY.EFFECTIVE_END_DATE ) PAYROLL,
   (SELECT NAME
     FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLD
      WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = AOR.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP
FROM PER_ASG_RESPONSIBILITIES AOR

Absences Leave Entries

select  per.person_number,
            abs.name absence_type,
            to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  start_date,
            to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  end_date,
            duration
from ANC_PER_ABS_ENTRIES apae,
         PER_PERIODS_OF_SERVICE pps,
         PER_ALL_PEOPLE_F per,
   ANC_ABSENCE_TYPES_VL abs
where apae.period_of_service_id = pps.period_of_Service_id
    and apae.absence_type_id = abs.absence_type_id
    and pps.person_id = per.person_id
 and trunc(sysdate) between abs.effective_start_date and abs.effective_end_Date
 and trunc(sysdate) between per.effective_start_date and per.effective_end_Date
 order by per.person_number

Work Schedules Query

SELECT to_char(psa.start_date,'YYYY/MM/DD') START_DATE,to_char(psa.end_date,'YYYY/MM/DD') END_DATE,papf.person_number,zsst.schedule_name,zsst.SCHEDULE_ID,psa.SCHEDULE_ASSIGNMENT_ID
FROM   zmm_sr_schedules_tl zsst
      ,per_schedule_assignments psa
      ,per_all_assignments_m paam1
      ,per_all_people_f papf
WHERE      1 = 1
       AND zsst.language = 'US'
       AND zsst.schedule_id = psa.schedule_id
       AND TRUNC (SYSDATE) BETWEEN psa.start_date AND psa.end_date
       AND psa.resource_id = paam1.assignment_id
       AND psa.resource_type = 'ASSIGN'
       AND TRUNC (SYSDATE) BETWEEN paam1.effective_start_date
                               AND paam1.effective_end_date
       AND paam1.primary_flag = 'Y'
         AND paam1.effective_latest_change = 'Y'
       AND paam1.assignment_type = 'E'
       AND papf.person_id = paam1.person_id
       AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                               AND papf.effective_end_date
--and person_number = '17'
ORDER BY LPAD(PAPF.PERSON_NUMBER,20,'0')

User Roles Query

select papf.person_number,
--pu.username,
prd.role_common_name
 from
 per_users pu,
 per_user_roles pr,
 per_roles_dn prd ,
 per_all_people_f papf
 where 1=1
 and papf.person_id = pu.person_id
 and pu.user_id = pr.user_id
 and pr.role_id = prd.role_id
--and papf.person_number = '119802'
 --and prd.role_common_name ='PAYROLL_SPECIALIST_DATA'

Salary Query

SELECT   papf.person_number
        ,TO_CHAR(cs.date_to,'YYYY/MM/DD') date_to
        ,to_char(cs.date_from,'YYYY/MM/DD') date_from
        ,cs.salary_amount
        ,csb.name
FROM     cmp_salary cs
        ,cmp_salary_bases csb
        ,per_all_assignments_m paam
        ,per_all_people_f papf
WHERE        cs.salary_basis_id = csb.salary_basis_id
         AND paam.assignment_id = cs.assignment_id
         AND paam.effective_latest_change = 'Y'
         AND paam.primary_assignment_flag = 'Y'
         AND TRUNC (SYSDATE) BETWEEN paam.effective_start_date
                                 AND paam.effective_end_date
         AND papf.person_id = paam.person_id
         AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                 AND papf.effective_end_date
ORDER BY LPAD (papf.person_number
              ,20
              ,'0')

Position Query

SELECT    esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card
         ,MIN (valid_grade_code1) valid_grade_code1
         ,MIN (valid_grade_code2) valid_grade_code2
         ,MIN (valid_grade_code3) valid_grade_code3
FROM      (SELECT  TO_CHAR (hap.effective_start_date
                           ,'dd-mm-yyyy')
                      esd
                  ,hap.position_code position_code
                  ,haou.name department
                  ,haoub.name business_unit
                  ,hapt.name position_name
                  ,hap.full_part_time
                  ,hap.permanent_temporary_flag
                  ,hap.position_type position_type
                  ,hap.fte fte
                  ,hap.max_persons head_count
                  ,hap.probation_period probation_period
                  ,hap.probation_period_unit_cd probation_period_unit_cd
                  ,hap.hiring_status hiring_status
                  ,pj.job_code job_code
                  ,hap.attribute1 health_card
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 1
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code1
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 2
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code2
                  ,CASE
                      WHEN RANK ()
                              OVER (PARTITION BY hap.position_id
                                    ORDER BY LPAD (pg.grade_code
                                                  ,10
                                                  ,'0')) = 3
                      THEN
                         pg.grade_code
                      ELSE
                         ''
                   END
                      valid_grade_code3
           FROM    hr_all_positions_f hap
                  ,hr_all_positions_f_tl hapt
                  ,hr_all_organization_units haoub
                  ,hr_all_organization_units haou
                  ,per_jobs_f pj
                  ,per_valid_grades_f pvg
                  ,per_grades_f pg
                  ,per_grades_f_tl pgft
           WHERE       1 = 1
                   AND TRUNC (SYSDATE) BETWEEN hap.effective_start_date AND hap.effective_end_date
                   AND hapt.position_id = hap.position_id
                   AND hapt.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN hapt.effective_start_date AND hapt.effective_end_date
                   AND haoub.organization_id = hap.business_unit_id
                   AND haou.organization_id = hap.organization_id
                   AND pj.job_id = hap.job_id
                   AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date AND pj.effective_end_date
                   AND pvg.position_id = hap.position_id
                   AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date AND pvg.effective_end_date
                   AND pg.grade_id = pvg.grade_id
                   AND TRUNC (SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date
                   AND pgft.grade_id = pg.grade_id
                   AND pgft.language = 'US'
                   AND TRUNC (SYSDATE) BETWEEN pgft.effective_start_date AND pgft.effective_end_date)
GROUP BY  esd
         ,position_code
         ,department
         ,business_unit
         ,position_name
         ,full_part_time
         ,permanent_temporary_flag
         ,position_type
         ,fte
         ,head_count
         ,probation_period
         ,probation_period_unit_cd
         ,hiring_status
         ,job_code
         ,health_card

Job Family

SELECT pjf.job_family_name
      ,pj.job_family_code CODE
      ,pj.active_status
      ,TO_CHAR (pj.effective_start_date
               ,'DD-Mon-YYYY'
               ,'NLS_DATE_LANGUAGE=AMERICAN')
          effective_start_date
FROM   per_job_family_f_tl pjf
      ,per_job_family_f pj
WHERE  pjf.job_family_id = pj.job_family_id
       AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                               AND pj.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                               AND pjf.effective_end_date

Leave Initial Balance

select PER.PERSON_NUMBER,
            PPN.FULL_NAME,
            TO_CHAR(ACC.PROCD_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') PROCD_DATE,
            (select  NAME
   from ANC_ABSENCE_PLANS_VL   
   WHERE ABSENCE_PLAN_ID = ACC.PL_ID) PLAN_NAME,
            ACC.VALUE BALANCE
   from ANC_PER_ACRL_ENTRY_DTLS ACC,
            PER_ALL_PEOPLE_F PER,
            PER_PERSON_NAMES_F PPN
WHERE ACC.PERSON_ID = PER.PERSON_ID
  AND PER.PERSON_ID = PPN.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE AND PPN.EFFECTIVE_END_DATE
  AND PPN.NAME_TYPE = 'GLOBAL'
AND ACC.TYPE = 'INIT'
ORDER BY PER.PERSON_NUMBER

Organization Department Query

SELECT haou.name
      ,fnd.set_name
      ,haou.attribute1 classification
      ,haou.attribute2 business_function
      ,haou.attribute3 operating_unit
      ,TO_CHAR (haou.effective_start_date
               ,'dd-mm-yyyy')
          Effective_Start_Date
      ,hl.iNTERNAL_LOCATION_CODE LOCATION_CODE
FROM   hr_org_unit_classifications_f houc
      ,hr_all_organization_units haou
      ,fnd_setid_sets fnd
      ,hr_locations hl
WHERE  1 = 1
AND    hl.location_id(+) = haou.location_id
AND    haou.organization_id = houc.organization_id
AND    houc.classification_code = 'DEPARTMENT'
AND    fnd.set_id = houc.set_id

Location Query

/* Formatted on 9/25/2017 4:12:35 PM (QP5 v5.163.1008.3004) */
SELECT LOCATION_NAME,
       iNTERNAL_LOCATION_CODE,
       TO_CHAR (EFFECTIVE_START_DATE,
                'DD-Mon-YYYY',
                'NLS_DATE_LANGUAGE=AMERICAN')
          "Effective Start Date",
       POSTAL_CODE,
       COUNTRY, ADDRESS_LINE_1,
       ADDRESS_LINE_2
  FROM hr_locations
 --WHERE iNTERNAL_LOCATION_CODE in ('225','203')

Job Query

SELECT pjf.job_family_name
      ,pj.job_family_code CODE
      ,pj.active_status
      ,TO_CHAR (pj.effective_start_date
               ,'DD-Mon-YYYY'
               ,'NLS_DATE_LANGUAGE=AMERICAN')
          effective_start_date
FROM   per_job_family_f_tl pjf
      ,per_job_family_f pj
WHERE  pjf.job_family_id = pj.job_family_id
       AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                               AND pj.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                               AND pjf.effective_end_date

Job Query

SELECT   effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
        ,MIN (valid_grade_code1) valid_grade_code1
        ,MIN (valid_grade_code2) valid_grade_code2
        ,MIN (valid_grade_code3) valid_grade_code3
FROM     (SELECT TO_CHAR (pj.effective_start_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_start_date
                ,TO_CHAR (pj.effective_end_date
                         ,'DD-Mon-YYYY'
                         ,'NLS_DATE_LANGUAGE=AMERICAN')
                    effective_end_date
                ,pj.job_code job_code
                ,pjj.name job_name
                ,RANK ()
                    OVER (PARTITION BY pj.job_id
                          ORDER BY
                             LPAD (pg.grade_code
                                  ,10
                                  ,'0'))
                    seq_no
                ,pg.grade_code grade_code
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 1
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code1
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 2
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code2
                ,CASE
                    WHEN RANK ()
                            OVER (PARTITION BY pj.job_id
                                  ORDER BY
                                     LPAD (pg.grade_code
                                          ,10
                                          ,'0')) = 3
                    THEN
                       pg.grade_code
                    ELSE
                       ''
                 END
                    valid_grade_code3
                ,pjf.job_family_name job_family_name
                ,pj.attribute2 arabic_name
                ,pj.attribute1 deployment_indicator
                , (SELECT set_name
                   FROM   fnd_setid_sets_vl
                   WHERE  set_id = pj.set_id)
                    set_name
          FROM   per_job_family_f_tl pjf
                ,per_grades pg
                ,per_valid_grades_f pvg
                ,per_jobs_f pj
                ,per_jobs pjj
          WHERE  pjf.job_family_id = pj.job_family_id
                 AND pjf.language = 'US'
                 AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                                         AND pjf.effective_end_date
                 AND pg.grade_id = pvg.grade_id
                 AND TRUNC (SYSDATE) BETWEEN pvg.effective_start_date
                                         AND pvg.effective_end_date
                 AND pvg.job_id = pj.job_id
                 AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                                         AND pj.effective_end_date
                 AND pj.job_id = pjj.job_id)
GROUP BY effective_start_date
        ,effective_end_date
        ,job_code
        ,job_name
        ,job_family_name
        ,arabic_name
        ,deployment_indicator
        ,set_name
ORDER BY job_name

Fusion HDL Error Analysis

Fusion Error Analysis Summary:

SELECT '1' KEY, ucm_content_id, data_set_name, imported_status,
       transfer_status,
       TO_CHAR (ds.last_update_date,
                'dd-mon-rrrr hh24:mi:ss'
               ) batch_submission_date,
       import_lines_total_count, import_error_count, loaded_count,
       error_count
  FROM hrc_dl_data_sets ds
 WHERE ucm_content_id =  'UCMFA00009281'
/* (SELECT MAX (ucm_content_id)
                           FROM hrc_dl_data_sets hdd
                          WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
        AND hdd.LOADED_STATUS = 'ERROR')*/

Fusion Error Analysis Details :
SELECT  DISTINCT ds.ucm_content_id ucm_content_id1
                ,fr.key_source_owner data_source1
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date1
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_logical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_business_objects hdbo
       ,hrc_dl_file_headers fh
WHERE       ucm_content_id = 'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                                   FROM hrc_dl_data_sets hdd
                                  WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                                  AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_LOGICAL_LINES'
        AND ml.message_source_line_id = ll.logical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id
-- AND ds.transfer_status = 'ERROR'
UNION
SELECT  DISTINCT ds.ucm_content_id
                ,fr.key_source_owner data_source
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_physical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_file_headers fh
       ,hrc_dl_business_objects hdbo
WHERE       ucm_content_id =  'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                             FROM hrc_dl_data_sets hdd
                            WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                            AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'
        AND ml.message_source_line_id = ll.physical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id

 

Documents of Records DOR Query

SELECT papf.person_number
      , (SELECT documents_of_record_id
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          documents_of_record_id
           , (SELECT DEI_ATTRIBUTE4
         FROM   hr_documents_of_record hrd
         WHERE  hrd.person_id = papf.person_id
                AND ROWNUM = 1)
          self_dependant
      , (SELECT hdr.dei_attribute1 uid_number
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE  ROWNUM = 1
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id)
                AND hdr.document_type_id = hdt.document_type_id
                AND hdr.person_id = papf.person_id
                AND hdt.language = 'US'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.dei_attribute_category = 'Update National Identifier')
          uid_number
      , (SELECT hdr.dei_attribute2
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   eid_expiry
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          eid_expiry
      , (SELECT hdr.dei_attribute3 mol_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update National Identifier'
                AND hdt.document_type = 'Update National Identifier'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          mol_expiry
      , (SELECT hdr.dei_attribute3 visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_num
      , (SELECT TO_CHAR (hdr.dei_attribute_date1
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
                   visa_num
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          visa_expiry_date
      , (SELECT hdr.dei_attribute6 sponsor_name
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category = 'Update Visa Details'
                AND hdt.document_type = 'Update Visa Details'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          sponsor_name
      , (SELECT hdr.dei_attribute7
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_number
      , (SELECT TO_CHAR (hdr.dei_attribute_date2
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_issue_date
      , (SELECT TO_CHAR (hdr.dei_attribute_date3
                        ,'DD-Mon-YYYY'
                        ,'NLS_DATE_LANGUAGE=AMERICAN')
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passport_expiry_date
      , (SELECT hdr.dei_attribute6
         FROM   hr_documents_of_record hdr
               ,hr_document_types_tl hdt
         WHERE      ROWNUM = 1
                AND hdt.document_type_id = hdr.document_type_id
                AND hdt.language = 'US'
                AND hdr.dei_attribute_category =
                       'Passport Information Update'
                AND hdt.document_type = 'Passport Information Update'
                AND hdr.person_id = papf.person_id
                AND hdr.last_update_date =
                       (SELECT MAX (hdr1.last_update_date)
                        FROM   hr_documents_of_record hdr1
                        WHERE  hdr1.person_id = hdr.person_id
                               AND hdr1.document_type_id =
                                      hdr.document_type_id))
          passprot_country
FROM   per_all_people_f papf
WHERE  TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date