SELECT
INTERFACE_RUN_ID_PAY ,
BATCH_ID ,
PAYROLL_NAME ,
PAYROLL_ID ,
ORGANIZATION_NAME ,
ORGANIZATION_ID ,
ELEMENT_TYPE_ID ,
ELEMENT_NAME ,
REPORTING_NAME ,
OUTPUT_CURRENCY_CODE ,
TIME_PERIOD_ID ,
TIME_PERIOD_START_DATE,
TIME_PERIOD_END_DATE ,
SOURCE_START_DATE,
SOURCE_END_DATE,
CLASSIFICATION ,
AMOUNT
FROM
(
SELECT
PPA.INTERFACE_RUN_ID INTERFACE_RUN_ID_PAY ,
PCV.PAYROLL_ACTION_ID BATCH_ID ,
PCV.PAYROLL_NAME ,
PCV.PAYROLL_ID ,
PCV.ORGANIZATION_NAME ,
PCV.ORGANIZATION_ID ,
PCV.ELEMENT_TYPE_ID ,
PCV.ELEMENT_NAME ,
PCV.REPORTING_NAME ,
PCV.OUTPUT_CURRENCY_CODE ,
PCV.TIME_PERIOD_ID ,
PCV.TIME_PERIOD_START_DATE ,
PCV.TIME_PERIOD_END_DATE ,
NVL(PCV.SOURCE_START_DATE,PCV.TIME_PERIOD_START_DATE) SOURCE_START_DATE,
NVL(PCV.SOURCE_END_DATE,PCV.TIME_PERIOD_END_DATE) SOURCE_END_DATE,
CLASSIFICATION_NAME CLASSIFICATION ,
SUM(PCV.DEBIT_AMOUNT- PCV.CREDIT_AMOUNT) AMOUNT
FROM
PAY_COSTING_PROJECTS_V PCV,
PA_PAY_AUDIT PPA
WHERE
BALANCE_OR_COST = 'C' AND
PPA.INTERFACE_RUN_ID = NVL(:P_INTERFACE_RUN_ID ,PPA.INTERFACE_RUN_ID) AND
PCV.ORGANIZATION_ID = NVL(:P_EMP_ORG,PCV.ORGANIZATION_ID ) AND
PCV.PERSON_ID = NVL(:P_PERSON_ID , PCV.PERSON_ID ) AND
PCV.PAYROLL_ACTION_ID = PPA.BATCH_ID AND
PPA.INT_EXT_INDICATOR = 'INT' AND
PPA.PAYROLL_ID = PCV.PAYROLL_ID AND
TRUNC(NVL(PPA.SOURCE_START_DATE,PPA.PAY_PERIOD_START_DATE)) = TRUNC(NVL(PCV.SOURCE_START_DATE,PCV.TIME_PERIOD_START_DATE)) AND
TRUNC(NVL(PPA.SOURCE_END_DATE,PPA.PAY_PERIOD_END_DATE)) = TRUNC(NVL(PCV.SOURCE_END_DATE,PCV.TIME_PERIOD_END_DATE))
GROUP BY
PPA.INTERFACE_RUN_ID ,
PCV.PAYROLL_ACTION_ID ,
PCV.PAYROLL_NAME ,
PCV.PAYROLL_ID ,
ORGANIZATION_NAME ,
ORGANIZATION_ID ,
ELEMENT_TYPE_ID ,
ELEMENT_NAME ,
REPORTING_NAME ,
OUTPUT_CURRENCY_CODE ,
PCV.TIME_PERIOD_ID ,
PCV.TIME_PERIOD_START_DATE,
PCV.TIME_PERIOD_END_DATE ,
NVL(PCV.SOURCE_START_DATE,PCV.TIME_PERIOD_START_DATE) ,
NVL(PCV.SOURCE_END_DATE,PCV.TIME_PERIOD_END_DATE) ,
CLASSIFICATION_NAME
UNION
SELECT
PPA.INTERFACE_RUN_ID INTERFACE_RUN_ID_PAY ,
PCV.PAYROLL_BATCH_ID BATCH_ID ,
PPE.PAYROLL_NAME ,
PCV.PAYROLL_ID ,
PCV.EMP_ORGANIZATION_NAME ORGANIZATION_NAME ,
PCV.EMP_ORGANIZATION_ID ORGANIZATION_ID ,
NULL AS ELEMENT_TYPE_ID ,
PCV.PAY_ELEMENT_TYPE_CODE ELEMENT_NAME ,
PL.MEANING REPORTING_NAME ,
PCV.PAY_CURRENCY_CODE ,
PCV.TIME_PERIOD_ID ,
PCV.TIME_PERIOD_START_DATE ,
PCV.TIME_PERIOD_END_DATE ,
NULL,
NULL,
(CASE
WHEN PREDEFINED_FLAG = 'E' THEN 'Earnings'
WHEN PREDEFINED_FLAG = 'I' THEN 'Informational'
WHEN PREDEFINED_FLAG = 'S' THEN 'Supplemental Earnings'
WHEN PREDEFINED_FLAG = 'T' THEN 'Employer Taxes'
WHEN PREDEFINED_FLAG = 'L' THEN 'Employer Liabilities'
WHEN PREDEFINED_FLAG = 'O' THEN 'Other'
WHEN PREDEFINED_FLAG IS NULL THEN 'Other'
END) CLASSIFICATION ,
SUM(PAY_AMOUNT) AMOUNT
FROM
PA_PAY_EXT_INTERAFACE_V PCV,
PA_PAY_AUDIT PPA ,
PA_PAY_EXTERNAL_PAYROLL PPE,
PA_LOOKUPS PL
WHERE
PPA.INTERFACE_RUN_ID = NVL(:P_INTERFACE_RUN_ID, PPA.INTERFACE_RUN_ID) AND
PCV.EMP_ORGANIZATION_ID = NVL(:P_EMP_ORG, PCV.EMP_ORGANIZATION_ID ) AND
PCV.PERSON_ID = NVL(:P_PERSON_ID , PCV.PERSON_ID ) AND
PL.lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS' AND
NVL(PREDEFINED_FLAG,'E') IN ('E','I') AND
PCV.PAY_ELEMENT_TYPE_CODE = PL.LOOKUP_CODE AND
PCV.PAYROLL_BATCH_ID = PPA.BATCH_ID AND
PPA.INT_EXT_INDICATOR = 'EXT' AND
PPA.PAYROLL_ID = PCV.PAYROLL_ID AND
TRUNC(PPA.PAY_PERIOD_START_DATE) = TRUNC( PCV.TIME_PERIOD_START_DATE) AND
TRUNC(PPA.PAY_PERIOD_END_DATE) = TRUNC(PCV.TIME_PERIOD_END_DATE) AND
PCV.PAYROLL_ID = PPE.PAYROLL_ID
GROUP BY
PPA.INTERFACE_RUN_ID ,
PCV.PAYROLL_BATCH_ID ,
PPE.PAYROLL_NAME ,
PCV.PAYROLL_ID ,
PCV.EMP_ORGANIZATION_NAME ,
PCV.EMP_ORGANIZATION_ID ,
PCV.PAY_ELEMENT_TYPE_CODE ,
PL.MEANING ,
PAY_CURRENCY_CODE ,
PCV.TIME_PERIOD_ID ,
PCV.TIME_PERIOD_START_DATE,
PCV.TIME_PERIOD_END_DATE ,
(
CASE
WHEN PREDEFINED_FLAG = 'E' THEN 'Earnings'
WHEN PREDEFINED_FLAG = 'I' THEN 'Informational'
WHEN PREDEFINED_FLAG = 'S' THEN 'Supplemental Earnings'
WHEN PREDEFINED_FLAG = 'T' THEN 'Employer Taxes'
WHEN PREDEFINED_FLAG = 'L' THEN 'Employer Liabilities'
WHEN PREDEFINED_FLAG = 'O' THEN 'Other'
WHEN PREDEFINED_FLAG IS NULL THEN 'Other'
END)
)
ORDER BY
BATCH_ID ,
PAYROLL_ID ,
TIME_PERIOD_ID ,
OUTPUT_CURRENCY_CODE,
CLASSIFICATION |
