Assignments Query

SELECT distinct per.person_number,
            (SELECT DISTINCT NAME FROM HR_ALL_ORGANIZATION_UNITS_F_VL HOU
            WHERE HOU.ORGANIZATION_ID = ASG.LEGAL_ENTITY_ID
            AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE AND HOU.EFFECTIVE_END_DATE) LEGAL_EMPLOYER,
                ASG.ASSIGNMENT_NUMBER,
                PPN.FULL_NAME,
                ASG.EFFECTIVE_START_DATE,
                ASG.EFFECTIVE_END_DATE,               
                (SELECT DISTINCT NAME
                FROM HR_ALL_ORGANIZATION_UNITS_F_VL  HOU
                WHERE HOU.ORGANIZATION_ID = ASG.BUSINESS_UNIT_ID
                AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE
                AND HOU.EFFECTIVE_END_DATE) BUSINESS_UNIT,
                (select distinct group_name from PER_PEOPLE_GROUPS  where PEOPLE_GROUP_ID = ASG.PEOPLE_GROUP_ID) PEOPLE_GROUP_NAME,
                INITCAP (TO_CHAR (TO_DATE (ASG.NOTICE_PERIOD, 'jsp'), 'jsp')) "NOTICE_PERIOD",
                (select distinct meaning
                from fnd_lookup_values
                where lookup_type = 'ANC_DURATION_UOM'
                and lookup_code = ASG.NOTICE_PERIOD_UOM) "NOTICE_PERIOD_UOM",
                (select DISTINCT NAME FROM PER_GRADES PG
                WHERE PG.GRADE_ID= ASG.GRADE_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN NVL(PG.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PG.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) GRADE,
                (select distinct meaning from fnd_lookup_values where lookup_type = 'EMP_CAT'
                and lookup_code=ASG.EMPLOYMENT_CATEGORY) ASSIGNMENT_CATEGORY,
                (SELECT DISTINCT PJ.NAME FROM PER_JOBS_F_VL PJ
                WHERE PJ.JOB_ID = ASG.JOB_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN NVL(PJ.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PJ.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) JOB,
                (SELECT DISTINCT LOCATION_NAME FROM HR_LOCATIONS_ALL HL
                WHERE HL.LOCATION_ID=ASG.LOCATION_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE) LOCATION,
                (SELECT DISTINCT NAME FROM HR_ALL_ORGANIZATION_UNITS_F_VL HOU
                WHERE HOU.ORGANIZATION_ID = ASG.ORGANIZATION_ID
                AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE AND HOU.EFFECTIVE_END_DATE) DEPARTMENT,
                ASG.PROBATION_PERIOD,
                (select distinct meaning from fnd_lookup_values where lookup_type = 'QUALIFYING_UNITS' and lookup_code = ASG.PROBATION_UNIT) PROBATION_UNIT,
                ASG.Manager_Flag,
                (SELECT DISTINCT PP.NAME FROM HR_ALL_POSITIONS_F_TL PP
                WHERE PP.POSITION_ID = ASG.POSITION_ID AND TRUNC(ASG.EFFECTIVE_START_DATE)
                BETWEEN NVL(PP.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PP.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) POSITION,
                'LINE_MANAGER' MANAGER_TYPE,
                (select distinct ASSIGNMENT_NUMBER from per_all_assignments_f
                where trunc(sysdate) between effective_start_date and effective_end_date
                and ASSIGNMENT_TYPE = 'E'
                and assignment_id = (select  distinct MANAGER_ASSIGNMENT_ID 
                                     from PER_ASSIGNMENT_SUPERVISORS_F
                                    where trunc(sysdate) between effective_start_date and effective_end_date
                                    AND MANAGER_TYPE = 'LINE_MANAGER'
                                    and person_id = PER.person_id)) MANAGER_ASSIGNMENT_NUMBER,
                (SELECT DISTINCT  PERSON_NUMBER 
                  FROM PER_ALL_PEOPLE_F PPL,
                              PER_ASSIGNMENT_SUPERVISORS_F ASGSUP
                 WHERE PPL.PERSON_ID=ASGSUP.MANAGER_ID
                      AND ASGSUP.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ppl.effective_start_date and ppl.effective_end_date
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ASGSUP.effective_start_date and ASGSUP.effective_end_date
                      AND MANAGER_TYPE = 'LINE_MANAGER'
                      and rownum = 1) MANAGER_NO,
                    (SELECT DISTINCT  FULL_NAME
                  FROM PER_PERSON_NAMES_F PPL,
                              PER_ASSIGNMENT_SUPERVISORS_F ASGSUP
                 WHERE PPL.PERSON_ID=ASGSUP.MANAGER_ID
                      AND ASGSUP.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ppl.effective_start_date and ppl.effective_end_date
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ASGSUP.effective_start_date and ASGSUP.effective_end_date
                      AND MANAGER_TYPE = 'LINE_MANAGER'
                      and rownum = 1) MANAGER_NAME,
                (select distinct person_number from per_person_names_f ppnf
        where trunc(sysdate) between effective_start_date and effective_end_date
        AND  PPN.NAME_TYPE = 'GLOBAL'
        and person_id =
        (select  distinct MANAGER_ID 
                from PER_ASSIGNMENT_SUPERVISORS_F
                where trunc(sysdate) between effective_start_date and effective_end_date
                AND Manager_type = 'DEPT_MGR'
                and person_id = per.person_id)) DEP_MANAGER_NUM,
                (SELECT distinct salary_amount FROM CMP_SALARY WHERE person_id =per.person_id
                 AND trunc(sysdate) BETWEEN date_from AND date_to
                    )   "BASIC_SALARY",
                        ASG.ASS_ATTRIBUTE3 AIR_TICKET_DESTINATION
    FROM PER_PEOPLE_F PER,
                PER_PERSON_NAMES_F PPN,
                PER_ALL_ASSIGNMENTS_F ASG,
                PER_PERIODS_OF_SERVICE PPS
 WHERE PER.PERSON_ID = PPN.PERSON_ID
      AND  PER.PERSON_ID = ASG.PERSON_ID
      AND  PER.PERSON_ID = PPS.PERSON_ID
      AND  ASG.ASSIGNMENT_TYPE = 'E'
      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'
      ORDER BY PER.PERSON_NUMBER

Contact i.e. Dependents Query

SELECT papf.person_number
      ,papf.person_id
       /* ,hdor.documents_of_record_id
       ,hdor.dei_attribute4 self_or_dependent
       ,hdor.dei_attribute8 confirm_colleague_id*/
       --,hdor.dei_attribute6 dependent_number
       --,hdor.dei_attribute1 uid_number
      -- ,hdor.dei_attribute2 eid_number
      -- ,hdor.dei_attribute3 mol_number
   ,pcrf.CONT_ATTRIBUTE5  authorised_dependent
   ,pcrf.CONT_ATTRIBUTE2  eligible_for_idcard
   ,pcrf.CONT_ATTRIBUTE1  eligible_for_medicalcard
   ,papfc.person_number dependent_number
   ,pcrf.EMERGENCY_CONTACT_FLAG
,      ppnfc.first_name
      ,ppnfc.last_name
       ,hr_general.decode_lookup ('CONTACT'
                             ,pcrf.contact_type) relationship
   ,TO_CHAR (pcrf.effective_start_date
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') effective_start_date
   ,pc.legislation_code nationality
      ,ppnfc.title
      ,pplf.sex gender
      ,TO_CHAR (pp.date_of_birth
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') date_of_birth
      /* ,TO_CHAR (hdor.dei_attribute_date2
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') mol_expiry_date
       ,TO_CHAR (hdor.dei_attribute_date1
               ,'YYYY/MM/DD'
               ,'nls_date_language=AMERICAN') eid_expiry_date
      ,NULL visa_number
      ,NULL visa_expiry_date
      ,NULL passport_number
      ,NULL passport_expiry_date
     
     
      ,NULL Passport_issue_date
      ,NULL passport_issue_country
      ,NULL Visa_issue_date
      ,NULL Visa_type
      ,NULL sponser_type
      ,NULL Sponser_name
      ,NULL Visa_permit_status */
/*-----------------------------------National Identifier Details----------------------*/
,(SELECT TO_CHAR (hdor.dei_attribute_date1
                       ,'YYYY/MM/DD'
                       ,'nls_date_language=AMERICAN')
        FROM   hr_documents_of_record hdor
              ,hr_document_types_tl hdtt
        WHERE  papf.person_id = hdor.person_id
        AND    hdor.document_type_id = hdtt.document_type_id
        AND    hdtt.LANGUAGE = 'US'
        AND    hdtt.document_type = 'Update National Identifier'
        AND    TRIM (hdor.dei_attribute4) = 'Dependent'
        AND    papfc.person_number = hdor.dei_attribute6) eid_expiry_date
      , (SELECT TO_CHAR (hdor.dei_attribute_date2
                        ,'YYYY/MM/DD'
                        ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) mol_expiry_date
      , (SELECT hdor.dei_attribute2
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) eid_number
      , (SELECT hdor.dei_attribute3
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update National Identifier'
         AND    TRIM (hdor.dei_attribute4) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute6) mol_number
      /*------------------- Visa Details ---------------------*/
  ,      (SELECT hdor.dei_attribute3
        FROM   hr_documents_of_record hdor
              ,hr_document_types_tl hdtt
        WHERE  papf.person_id = hdor.person_id
        AND    hdor.document_type_id = hdtt.document_type_id
        AND    hdtt.LANGUAGE = 'US'
        AND    hdtt.document_type LIKE 'Update Visa De%'
        AND    TRIM (hdor.dei_attribute1) = 'Dependent'
        AND    papfc.person_number = hdor.dei_attribute2) visa_number
      , (SELECT TO_CHAR (hdor.dei_attribute_date2
                        ,'YYYY/MM/DD'
                        ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_expiry_date
       , (SELECT hdor.dei_attribute4
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_type
      , (SELECT hdor.dei_attribute5
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) sponser_type
      , (SELECT hdor.dei_attribute6
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) sponser_name
      , (SELECT hdor.dei_attribute7
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND    hdtt.document_type = 'Update Visa Details'
         AND    TRIM (hdor.dei_attribute1) = 'Dependent'
         AND    papfc.person_number = hdor.dei_attribute2) visa_permit_status
  
/*----------------------------------------Passport Details-----------------------*/
,(SELECT hdor.dei_attribute7
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_number
,(SELECT TO_CHAR (hdor.dei_attribute_date3
                 ,'YYYY/MM/DD'
                 ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_expiry_date
,(SELECT TO_CHAR (hdor.DEI_ATTRIBUTE_DATE2
                 ,'YYYY/MM/DD'
                 ,'nls_date_language=AMERICAN')
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2)  passport_issue_date
,(SELECT hdor.DEI_ATTRIBUTE6
         FROM   hr_documents_of_record hdor
               ,hr_document_types_tl hdtt
         WHERE  papf.person_id = hdor.person_id
         AND    hdor.document_type_id = hdtt.document_type_id
         AND    hdtt.LANGUAGE = 'US'
         AND      hdtt.document_type LIKE 'Passport Information%'
         AND      TRIM (hdor.dei_attribute1) = 'Dependent'
         AND      papfc.person_number = hdor.dei_attribute2) passport_issue_country
        
/*---------------------------------END of sub queries---------------------------*/        
        
        
FROM   per_person_names_f ppnf
      ,per_all_people_f papf
      ,per_person_names_f ppnfc
      ,per_all_people_f papfc
      ,per_contact_relships_f pcrf
      ,per_citizenships pc
      ,per_people_legislative_f pplf
      ,per_persons pp
WHERE  pp.person_id(+) = papfc.person_id
AND    pplf.person_id = papfc.person_id
AND    pc.person_id = papfc.person_id
AND    TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date
                           AND ppnf.effective_end_date
AND    ppnf.name_type = 'GLOBAL'
AND    ppnf.person_id = papf.person_id
AND    pcrf.contact_person_id = papfc.person_id
AND    pcrf.person_id = papf.person_id
AND    TRUNC (SYSDATE) BETWEEN pcrf.effective_start_date
                           AND pcrf.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN ppnfc.effective_start_date
                           AND ppnfc.effective_end_date
AND    ppnfc.name_type = 'GLOBAL'
AND    ppnfc.person_id = papfc.person_id
AND    TRUNC (SYSDATE) BETWEEN papfc.effective_start_date
                           AND papfc.effective_end_date
AND    TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                           AND papf.effective_end_date
ORDER BY papfc.person_number

Fusion Person Address Query

select papf.person_number,pa.ADDRESS_LINE_1, pa.ADDRESS_LINE_2, pa.ADDRESS_LINE_3,pa.COUNTRY,pa.POSTAL_CODE from  per_addresses_F pa, PER_PERSON_ADDR_USAGES_F ppa,per_all_people_f papf
where ppa.address_id = pa.address_id
and papf.person_id = ppa.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

Grade Query

SELECT  pg.grade_code "Grade name" ,
      pg.grade_code "Grade code",
       (SELECT set_name
          FROM fnd_setid_sets_vl
         WHERE set_id = pg.set_id)
          set_name,
          TO_CHAR (pg.EFFECTIVE_START_DATE,
                'DD-Mon-YYYY',
                'NLS_DATE_LANGUAGE=AMERICAN')
          "Effective Start Date",
          pg.ACTIVE_STATUS
  FROM per_grades_f pg

Absence Entry Query in Fusion HCM

select  per.person_number,PER_ABSENCE_ENTRY_ID ,abs.absence_type_id,
            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
and per.person_number = '117889'
 order by per.person_number

HDL to Update existing Absence Leave Entry in Fusion

METADATA|PersonAbsenceEntry|StartDate|StartTime|EndDate|EndTime|ApprovalStatus|AbsenceStatus|advanceRequired(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DDF|advanceRequired_Display(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DFF|SourceSystemId|SourceSystemOwner|AbsenceTypeId|Duration
MERGE|PersonAbsenceEntry|2017/09/05|08:00|2017/09/05|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985134|FUSION|300000003079093|1
MERGE|PersonAbsenceEntry|2017/09/26|08:00|2017/09/26|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985262|FUSION|300000003079093|1
MERGE|PersonAbsenceEntry|2017/09/28|08:00|2017/09/28|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985398|FUSION|300000003079093|1


METADATA|PersonAbsenceEntry|Employer|PersonNumber|AbsenceType|StartDate|StartTime|EndDate|EndTime|ApprovalStatus|AbsenceStatus|advanceRequired(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DDF|advanceRequired_Display(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DFF
MERGE|PersonAbsenceEntry|XXX|1558|Annual Leave|2017/10/03|8:00|2017/10/03|6:30|APPROVED|SUBMITTED||||Annual Leave
MERGE|PersonAbsenceEntry|XXXX|4522|Annual Leave|2017/10/22|8:00|2017/10/22|6:30|APPROVED|SUBMITTED||||Annual Leave
MERGE|PersonAbsenceEntry|XXXXLLC|5830|Annual Leave|2017/10/02|8:00|2017/10/02|5:00|APPROVED|SUBMITTED||||Annual Leave

What are the Tables that Store the Calculations from the Calculate Payroll Process

There will be not any direct table to get the Net Amount and Gross Amount.
Please use the RUN RESULTS tables to see the all the run results for the employee.  Also you can use Balance functions to get the Balance value for particular person on given date.

For Employee information
PER_ALL_PEOPLE_F
PAY_PAY_RELATIONSHIPS_DN

For run results
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES

For Payment Method
PAY_PERSON_PAY_METHODS_F

For all HCM table information - please use below URL
http://docs.oracle.com/cloud/latest/globalcs_gs/OEDMH/toc.htm
To join these tables, the query will fetch requested information

select papf.person_number person_number
  ,pppmf.name payment_method_name
  ,PPA.PAYROLL_ACTION_ID payroll_action_id
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Net Payment',PRRV.RESULT_VALUE),0)) NET_PAYMENT
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Gross Pay',PRRV.RESULT_VALUE),0)) GROSS_PAY

from
PER_ALL_ASSIGNMENTS_m paam
,per_all_people_f papf
,per_periods_of_service ppos
,PAY_REL_GROUPS_DN prgd


,PAY_RUN_RESULTS PRR
,PAY_RUN_RESULT_VALUES PRRV
,PAY_BALANCE_FEEDS_F PBFF
,PAY_BALANCE_TYPES_TL PBTT
,PAY_PAYROLL_REL_ACTIONS PPRA
,PAY_PAYROLL_ACTIONS PPA
,pay_person_pay_methods_f pppmf


where paam.period_of_service_id = ppos.period_of_service_id
and paam.assignment_type='E'
and papf.person_id=paam.person_id
and prgd.GROUP_TYPE = 'A'
and prgd.payroll_relationship_id =PPRA.payroll_relationship_id
and prgd.assignment_id =paam.assignment_id
and ppos.date_start=(select max(pos1.date_start)
  from per_periods_of_service pos1
,per_all_assignments_f asg1
,pay_payroll_assignments ppa1
where PPA.date_earned >= pos1.date_start
  and pos1.person_id = asg1.person_id
  and asg1.assignment_id = ppa1.hr_assignment_id
  and asg1.period_of_service_id = pos1.period_of_service_id
  and ppa1.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID
and pos1.person_id = paam.person_id
and pos1.period_type = 'E')
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE

and papf.person_number=<PERSON_NUMBER>

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
AND PPRA.SOURCE_ID IS NULL
AND PPRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBFF.INPUT_VALUE_ID
AND PBFF.BALANCE_TYPE_ID = PBTT.BALANCE_TYPE_ID
AND PBTT.LANGUAGE = 'US'
and PBTT.BALANCE_NAME in ('Net Payment','Gross Pay')
and pppmf.payroll_relationship_id = prgd.payroll_relationship_id
GROUP BY
  papf.person_number
,pppmf.name
,PPA.PAYROLL_ACTION_ID