Query Absence in Oracle Fusion

 SELECT distinct * FROM(

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id

))

       department_name,

       paam.ass_attribute2 sector,

       paam.ass_attribute1

       unit,

       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,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type ='HR_REP'

       AND resp.status = 'Active'

       AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   --AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       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 ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

  

  UNION

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id))

       department_name,

       paam.ass_attribute2 sector,

       paam.ass_attribute1

       unit,

       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,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type IN ('XXXXXXXXXX')

       AND resp.status = 'Active'

       --AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       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 ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)  

)

   WHERE 1=1

    AND (LEAST(:p_sector) IS NULL

OR  trim(sector) IN trim( :p_sector ))

--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))

AND (LEAST(:p_dept) IS NULL

OR  department_name IN ( :p_dept ))

ORDER  BY department_name,order_by_date

Query For Absence Custom OTBI Query (Workforce Management - Absence Real Time) , OTBI Reporting Using Two Subject Areas

 SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT DISTINCT 

   Abs_name_type saw_0,

   Abs_entry_created_by saw_1,

   Abs_last_updated_by saw_2,

   Abs_End_Date saw_3,

   Abs_Start_Date saw_4,

   Abs_Entry_Identifier saw_5,

   Dep_Name saw_6,

   Grade_Name saw_7,

   sector_2 saw_8,

   Job_Name saw_9,

   Legal_employer_name saw_10,

   Postion_name saw_11,

   Emp_name saw_12,

   Hire_date saw_13,

   Emp_num saw_14,

   Abs_status saw_15,

   Approval_status saw_16,

   Abs_UOM saw_17,

   BU_Name saw_18,

   Leave_Duration saw_19

 FROM (select 

   "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name" Abs_name_type,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By" Abs_entry_created_by,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By" Abs_last_updated_by,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status" Abs_status,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status" Approval_status,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End" Abs_End_Date,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start" Abs_Start_Date,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier" Abs_Entry_Identifier,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM" Abs_UOM,

   "Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name" BU_Name,

   "Workforce Management - Absence Real Time"."Department"."Department Name" Dep_Name,

   "Workforce Management - Absence Real Time"."Grade"."Grade Name" Grade_Name,

   "Workforce Management - Absence Real Time"."Job"."Job Name" Job_Name,

   "Workforce Management - Absence Real Time"."Legal Employer"."Name" Legal_employer_name,

   "Workforce Management - Absence Real Time"."Position"."Position Name" Postion_name,

   "Workforce Management - Absence Real Time"."Worker"."Employee Name" Emp_name,

   "Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date" Hire_date,

   "Workforce Management - Absence Real Time"."Worker"."Person Number" Emp_num,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status") Abs_status_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status") Approval_status_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM") Abs_UOM_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name")BU_Name_des ,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name") Job_Name_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name") Postion_name_des,

   "Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration" Leave_Duration,

   REPORT_SUM("Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration" 

   BY "Workforce Management - Absence Real Time"."Worker"."Person Number",

   "Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date",

   "Workforce Management - Absence Real Time"."Worker"."Employee Name",

   "Workforce Management - Absence Real Time"."Department"."Department Name",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name"),

   "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM"),

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status"),

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status"),

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name"),"Workforce Management - Absence Real Time"."Grade"."Grade Name",DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name")) Duration

FROM "Workforce Management - Absence Real Time"

WHERE

((DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Worker"."HR Status") NOT IN ('*)nqgtn(*', 'INACTIVE')))) TABLE_A INNER JOIN (

SELECT

   "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,

   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2

FROM "Workforce Management - Worker Assignment Real Time"

WHERE

(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))

) TABLE_B ON TABLE_A.Emp_num = TABLE_B.person_number2

 WHERE 

(Emp_num IN ('XYZ'))

 ORDER BY saw_14, saw_4 DESC

Query For Payroll Run Results Custom OTBI Query

 SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT 

   EMP_NUMBER saw_0,

   ASSIGNMENT_NUMBER saw_1,

   EMP_NAME saw_2,

   HIRE_DATE saw_3,

   GRADE saw_4,

   JOB saw_5,

   POSITION_NAME saw_6,

   DEPT_NAME1 saw_7,

   sector_2 saw_8,

   BU_NAME1 saw_9,

   PAYROLL_PERIOD_NAME saw_10,

   PERIOD_NAME saw_11,

   DATE_EARNED saw_12,

   RUN_TYPE saw_13,

   ELEMENT_CLASSI_NAME saw_14,

   ELEMENT_NAME saw_15,

   AMOUNT saw_16,

   CASE WHEN ELEMENT_CLASSI_NAME in ('Involuntary Deductions','Social Insurance Deductions','Voluntary Deductions') THEN (AMOUNT*(-1)) ELSE AMOUNT END saw_17,

   PROCESS_FLOW_NAME saw_18

 FROM (SELECT

   "Payroll - Payroll Run Results Real Time"."Business Unit"."Business Unit Name" BU_NAME1,

   "Payroll - Payroll Run Results Real Time"."Department"."Department Name" DEPT_NAME1,

   "Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" ELEMENT_CLASSI_NAME,

   "Payroll - Payroll Run Results Real Time"."Element"."Element Name" ELEMENT_NAME,

   "Payroll - Payroll Run Results Real Time"."Grade"."Grade Name" GRADE,

   "Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Name" INPUT_VALUE_NAME,

   "Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure" INPUT_VALUE_UOM,

   "Payroll - Payroll Run Results Real Time"."Job"."Job Name" JOB,

   "Payroll - Payroll Run Results Real Time"."Worker"."Enterprise Hire Date" HIRE_DATE,

   "Payroll - Payroll Run Results Real Time"."Payroll Period"."Period Name" PERIOD_NAME,

   "Payroll - Payroll Run Results Real Time"."Payroll Run Result Details"."Date Earned" DATE_EARNED,

   "Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Run Result Value" RUN_RESULT_VALUE,

   "Payroll - Payroll Run Results Real Time"."Payroll Statutory Unit"."Payroll Statutory Unit Name" Payroll_Statutory_Unit_Name,

   "Payroll - Payroll Run Results Real Time"."Payroll"."Payroll Period Name" PAYROLL_PERIOD_NAME,

   "Payroll - Payroll Run Results Real Time"."Position"."Position Name" POSITION_NAME,

   "Payroll - Payroll Run Results Real Time"."Run Type"."Run Type Name" RUN_TYPE,

   "Payroll - Payroll Run Results Real Time"."Worker"."Assignment Number" ASSIGNMENT_NUMBER,

   "Payroll - Payroll Run Results Real Time"."Worker"."Assignment Status Code" ASSIGN_STATUS_CODE,

   "Payroll - Payroll Run Results Real Time"."Worker"."Employee Display Name" EMP_NAME,

   "Payroll - Payroll Run Results Real Time"."Worker"."Person Number" EMP_NUMBER,

    "Payroll - Payroll Run Results Real Time"."Payroll Flows"."Flow Instance Name" PROCESS_FLOW_NAME,

   "Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Amount" AMOUNT

FROM "Payroll - Payroll Run Results Real Time"

WHERE

((DESCRIPTOR_IDOF("Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure") = 'M') AND ("Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" in ('Involuntary Deductions','Social Insurance Deductions','Standard Earnings','Supplemental Earnings','Voluntary Deductions','Information') )AND ("Input Value"."Input Value Name" = 'Pay Value'))

) TABLE_A LEFT OUTER JOIN (

SELECT

   "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,

   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2

FROM "Workforce Management - Worker Assignment Real Time"

WHERE

(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))

) TABLE_B ON TABLE_A.EMP_NUMBER = TABLE_B.person_number2

 WHERE 

(EMP_NUMBER IN ('ABC')) AND (DATE_EARNED >= ANY 

 ( 

 SELECT saw_0 FROM (SELECT 

   "Payroll Period"."Default Pay Date" saw_0

 FROM "Payroll - Payroll Run Results Real Time"

 WHERE 

"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')

 ) nqw_1 

 )) AND (DATE_EARNED <= ANY 

 ( 

 SELECT saw_0 FROM (SELECT 

   "Payroll Period"."Default Pay Date" saw_0

 FROM "Payroll - Payroll Balances Real Time"

 WHERE 

"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')

 ) nqw_1 

 ))

 ORDER BY saw_12 DESC

Query For Payroll Run Results

 SELECT 

PERSON_NUMBER,

assignment_number,

Date_of_Joining,

grade,

Job,

Position,

Department,

BU_Name,

payroll_name,

PERIOD_NAME,

DEFAULT_PAYDATE,

Order_date,

Amount,

entitle_amount,

child_name_age,

child_school_type,

child_academic_year,

element_name,

base_element_name,

reporting_name

FROM (

SELECT

(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME = 'Pay Value'

AND pivf.uom = 'M'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)aMOUNT

,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)entitle_amount

,((select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)||'_'||SUBSTR(pett.element_name,20))child_name_age

,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)child_school_type

,(SELECT MEANING FROM HCM_LOOKUPS WHERE LOOKUP_TYPE = 'XXXXXXXXX' AND LOOKUP_CODE = (select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID))child_academic_year

,ptp.PERIOD_NAME

,TO_CHAR(ptp.DEFAULT_PAYDATE,'DD-Mon-YYYY') DEFAULT_PAYDATE

,Trim(DEFAULT_PAYDATE) Order_date

,petf.base_element_name

,petT.reporting_name

,to_char(PAPF.PERSON_NUMBER) PERSON_NUMBER

,to_char(ASG.assignment_number) assignment_number

,To_char(papf.start_date,'DD-Mon-YYYY') Date_of_Joining

,peTt.element_name

,pgft.name  grade

,pjft.name  Job

,hapft.name Position

,pd.name Department

,haou.name BU_Name

,pay.PAYROLL_NAME

FROM 

PAY_PAYROLL_REL_ACTIONS ppra

,PAY_PAYROLL_ACTIONS ppa

,PAY_PAY_RELATIONSHIPS_DN pprd

,PAY_REQUESTS pr

,PAY_FLOW_TASK_INSTANCES pfti

,PER_ALL_ASSIGNMENTS_M asg

,PAY_TIME_PERIODS ptp

,PAY_ELEMENT_TYPES_F petf

,PAY_ELEMENT_TYPES_TL pett

,PAY_ELE_CLASSIFICATIONS pec

,PAY_ELE_CLASSIFICATIONS pec1

,PAY_ELE_CLASSIFICATIONS_TL pect

,PAY_TIME_DEFINITIONS ptd

,PAY_TIME_DEFINITIONS ptd1

,PER_LEGISLATIVE_DATA_GROUPS pldg

,pay_all_payrolls_f pay

       ,per_all_people_f papf

       ,per_grades_f pgf

,per_grades_f_tl pgft

,per_jobs_f_tl pjft

,hr_all_positions_f_tl hapft

,per_departments PD

,hr_all_organization_units haou

,pay_element_entries_f        peef

WHERE  1=1

AND ppra.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID

AND ppra.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID

AND ppa.PAY_REQUEST_ID = pr.PAY_REQUEST_ID(+)

AND pr.FLOW_TASK_INSTANCE_ID = pfti.FLOW_TASK_INSTANCE_ID(+)

AND asg.PERSON_ID = pprd.PERSON_ID

AND asg.PRIMARY_FLAG = 'Y'

AND asg.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL

AND ppa.EFFECTIVE_DATE BETWEEN asg.effective_start_Date AND asg.effective_end_date

AND asg.assignment_status_type <> 'INACTIVE'

AND ppra.RETRO_COMPONENT_ID IS NULL

AND ppa.EARN_TIME_PERIOD_ID = ptp.TIME_PERIOD_ID(+)

AND petf.ELEMENT_TYPE_ID = pett.ELEMENT_TYPE_ID(+)

AND (USERENV('LANG')) = pett.LANGUAGE (+)

AND petf.CLASSIFICATION_ID = pec1.CLASSIFICATION_ID

AND pec1.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID

AND petf.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID

AND pec.CLASSIFICATION_ID = pect.CLASSIFICATION_ID(+)

AND (USERENV('LANG')) = pect.LANGUAGE (+)

AND petf.STARTING_TIME_DEF_ID = ptd.TIME_DEFINITION_ID(+)

AND petf.ENDING_TIME_DEF_ID = ptd1.TIME_DEFINITION_ID(+)

AND pec.LEGISLATION_CODE = NVL(petf.LEGISLATION_CODE, pldg.LEGISLATION_CODE)

AND petf.LEGISLATIVE_DATA_GROUP_ID = pldg.LEGISLATIVE_DATA_GROUP_ID(+)

AND sysdate BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE

AND pett.ELEMENT_NAME IN (

'XXXXXXXXXXX'

)

AND pay.payroll_id = ptp.payroll_id(+)

AND pay.payroll_id = ppa.payroll_id(+)

       AND ASG.person_id = papf.person_id

       --AND papf.person_id = ppnf.person_id

AND pgf.grade_id = ASG.grade_id

AND pgft.grade_id = pgf.grade_id

AND pgft.LANGUAGE = 'US'

AND pjft.job_id(+) = ASG.job_id

AND pjft.LANGUAGE = 'US'

AND hapft.position_id(+) = ASG.position_id

AND hapft.LANGUAGE = 'US'

AND ASG.organization_id = pd.organization_id(+)

AND haou.organization_id(+) = ASG.business_unit_id

AND sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between hapft.effective_start_date and hapft.effective_end_date

AND ASG.assignment_type IN ( 'E' )

AND papf.person_id = peef.PERSON_ID

AND peef.element_type_id = petf.element_type_id

AND peef.EFFECTIVE_START_DATE >= ptp.START_DATE

AND peef.EFFECTIVE_END_DATE <= ptp.END_DATE

AND (COALESCE(NULL, :P_Emp_No) IS null OR PAPF.PERSON_NUMBER IN (:P_Emp_No))

AND (COALESCE(NULL, :p_ass_no) IS null OR ASG.assignment_number IN (:p_ass_no))

AND (COALESCE(NULL, :p_payroll) IS null OR pay.payroll_name IN (:p_payroll))

AND (COALESCE(NULL, :P_Element_Name) IS null OR petT.element_name IN (:P_Element_Name))

AND ptp.DEFAULT_PAYDATE between (:P_From) and (:P_To)

)                               

WHERE 1=1

AND (COALESCE(NULL, :P_child_academic_year) IS null OR child_academic_year IN (:P_child_academic_year))

AND (Amount IS NOT NULL OR Amount >0)

--and entitle_amount is not null

ORDER BY PERSON_NUMBER,Order_date desc