PA Process Payroll Actuals

       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,
to_char(TIME_PERIOD_START_DATE,'DD-MM-YYYY') TIME_PERIOD_START_DATE,  
to_char(TIME_PERIOD_END_DATE ,'DD-MM-YYYY')  TIME_PERIOD_END_DATE,
to_char(SOURCE_START_DATE,'DD-MM-YYYY') SOURCE_START_DATE,
to_char(SOURCE_END_DATE,'DD-MM-YYYY') 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.REQUEST_ID                   = :P_REQUEST_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  PAYROLL_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  ,
		 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) 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    PL.lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
AND      Nvl(PREDEFINED_FLAG,'O') IN  ('E','I','S','T','L','O')
AND      PCV.PAY_ELEMENT_TYPE_CODE = PL.LOOKUP_CODE
AND      PPA.REQUEST_ID                   = :P_REQUEST_ID 
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 INTERFACE_RUN_ID_PAY ,
         PAYROLL_ID        ,
         TIME_PERIOD_ID ,
		 OUTPUT_CURRENCY_CODE,
		 CLASSIFICATION