Payroll Reconciliation

Payroll Reconciliation, Costing Query, Costing of Payment

Costing Query

SELECT EMPLOYEE_NUMBER,

            ASSIGNMENT_NUMBER,

            SUM (CREDIT_AMOUNT) CREDIT_AMT,

            SUM (DEBIT_AMOUNT) DEBIT_AMT,

            SUM (CREDIT_AMOUNT) - SUM (DEBIT_AMOUNT) C_AMT_CRDIT

       FROM APPS.PAY_COSTING_DETAILS_V

      WHERE     EFFECTIVE_DATE BETWEEN TO_DATE ('23-DEC-2014')

                                   AND TO_DATE ('20-JAN-2015')

            AND PAYROLL_NAME = 'Payroll Hijra'

            AND ELEMENT_NAME NOT IN ('Employer GOSI Annuities')

            AND CONCATENATED_SEGMENTS = 'control_acc_combination'

   --            AND EMPLOYEE_NUMBER = '01272'

   GROUP BY EMPLOYEE_NUMBER, ASSIGNMENT_NUMBER;

Costing of Payment

SELECT ASSIGNMENT_NUMBER, SUM (COSTED_VALUE) C_DEBIT_AMT

       FROM APPS.PAY_PAYMENT_COSTS_V PPC, APPS.PER_ALL_ASSIGNMENTS_F PAPF

      WHERE     PAPF.ASSIGNMENT_ID = PPC.ASSIGNMENT_ID

            AND PAPF.PAYROLL_ID =44

            AND COST_TYPE = 'Payroll Control Account'

            AND ACCOUNTING_DATE BETWEEN PAPF.EFFECTIVE_START_DATE

                                    AND PAPF.EFFECTIVE_END_DATE

            AND ACCOUNTING_DATE BETWEEN TO_DATE ('23-DEC-2014')

                                    AND TO_DATE ('20-JAN-2015')

   GROUP BY ASSIGNMENT_NUMBER;

Employee is Paid or Not Paid

SELECT ORG_PAYMENT_METHOD_NAME,

       PCOS.ACCOUNTING_DATE ACCOUNTING_DATE,

       apps.HR_GENERAL.DECODE_LOOKUP ('PAY_COST_ACCOUNT_TYPE',

                                      PCOS.ACCOUNT_TYPE)

          COST_TYPE,

       apps.HR_GENERAL.DECODE_LOOKUP ('DEBIT_CREDIT', PCOS.DEBIT_OR_CREDIT)

          DEBIT_OR_CREDIT,

       apps.HR_CHKFMT.CHANGEFORMAT (PCOS.VALUE, 'M', PCOS.CURRENCY_CODE)

          COSTED_VALUE,

       PCOS.SOURCE_TYPE SOURCE_TYPE,

       PCOS.SOURCE_ACTION_ID SOURCE_ACTION_ID,

       PCOS.ACCOUNT_ID GL_ACCOUNT_CCID,

       GCC.CHART_OF_ACCOUNTS_ID FLEX_NUM,

       PCOS.ASSIGNMENT_ID,

       PCOS.ASSIGNMENT_ACTION_ID,

       PCOS.PRE_PAYMENT_ID,

       PCOS.PAYMENT_COST_ID,

       ppp1.status,

       paa.assignment_id

  FROM PAY_PRE_PAYMENTS_V ppp1,

       PAY_PAYMENT_COSTS PCOS,

       PAY_PRE_PAYMENTS PPP,

       PAY_ASSIGNMENT_ACTIONS PAA,

       PAY_PAYROLL_ACTIONS PAC,

       apps.GL_CODE_COMBINATIONS GCC

 WHERE     PCOS.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID

       AND ppp1.PRE_PAYMENT_ID = ppp.PRE_PAYMENT_ID

       AND PAA.ASSIGNMENT_ACTION_ID = PCOS.ASSIGNMENT_ACTION_ID

       AND PAA.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID

       AND GCC.CODE_COMBINATION_ID = PCOS.ACCOUNT_ID

       --and 'Cheque KKESH' <>OPM.ORG_PAYMENT_METHOD_NAME

       AND ACCOUNTING_DATE BETWEEN '01-FEB-2014' AND '01-MAR-2014';