Search This Blog

Saturday, November 25, 2017

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')