Oracle Payroll Standard Query

Oracle Payroll Standard Query (After Payroll Run)

SELECT PTP.PERIOD_NAME,

          PTP.START_DATE,

          PTP.END_DATE,

          PTP.PAYROLL_ID,

          PPA.DATE_EARNED,

          PPA.EFFECTIVE_DATE,

          PPA.ACTION_TYPE,

          PPA.PAYROLL_ACTION_ID,

          PAA.ASSIGNMENT_ACTION_ID,

          PAA.ASSIGNMENT_ID,

          PET.ELEMENT_NAME,

          PET.ATTRIBUTE2 ELEMENT_GROUP,

          PRRV.RESULT_VALUE,

          DECODE (

             PET.ELEMENT_NAME,

             'Basic Salary', 'Earnings',

             DECODE (

                PEC.CLASSIFICATION_NAME,

                'Involuntary Deductions', 'Deductions',

                'Voluntary Deductions', 'Deductions',

                'Statutory Deductions', 'Deductions',

                DECODE (PET.ELEMENT_NAME,

                        'Employer GOSI Annuities', 'GOSI',

                        'Employer GOSI Hazards', 'GOSI',

                        PEC.CLASSIFICATION_NAME)))

             CLASSIFICATION_NAME,

          PEC.COSTING_DEBIT_OR_CREDIT,

          PPA.BUSINESS_GROUP_ID,

          PAAF.ORGANIZATION_ID

     FROM HR.PER_TIME_PERIODS PTP,

          HR.PAY_PAYROLL_ACTIONS PPA,

          HR.PAY_ASSIGNMENT_ACTIONS PAA,

          HR.PAY_RUN_RESULTS PRR,

          HR.PAY_RUN_RESULT_VALUES PRRV,

          HR.PAY_ELEMENT_TYPES_F PET,

          HR.PAY_INPUT_VALUES_F PIV,

          HR.PAY_ELEMENT_CLASSIFICATIONS PEC,

          PER_ALL_ASSIGNMENTS_F PAAF

    --       pay_element_sets pes,

    --       pay_element_type_rules petr,

    WHERE PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID

      AND PPA.ACTION_TYPE IN ('Q', 'R')

      AND PPA.ACTION_STATUS = 'C'

      AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID

      AND PAA.ACTION_STATUS = 'C'

      AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID

      AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID

      AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID

      AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE

                                 AND PET.EFFECTIVE_END_DATE

      AND (PET.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID

        OR  PET.BUSINESS_GROUP_ID IS NULL)

      AND PIV.NAME = 'Pay Value'

      AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE

                                 AND PIV.EFFECTIVE_END_DATE

      AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID

      --      AND paa.assignment_id = :p_assignment_id

      AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID

      AND PEC.CLASSIFICATION_NAME NOT IN ('Information')

      AND PEC.CLASSIFICATION_NAME IN

             ('Earnings',

              'Involuntary Deductions',

              'Voluntary Deductions',

              'Statutory Deductions',

              'Employer Charges')

      AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID

      AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE

                                 AND PAAF.EFFECTIVE_END_DATE

      AND PAAF.PRIMARY_FLAG = 'Y';