PA Process Payroll Actuals Exception

Description
Categories: BI Publisher, Financials
Application: Projects
Source: PRC: Process Payroll Actuals Exception Report
Short Name: PAPAYEXC
DB package:
        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
Parameter Name SQL text Validation
Employee
 
LOV Oracle
Expenditure Organization
 
LOV Oracle
Costed Payroll Set ID
 
LOV Oracle