PA Sweep Transaction Accounting Events

SELECT XE.EVENT_ID		 EVEN_ID,
       PRJ.SEGMENT1		 PROJECT_NUMBER,
       PRJ.NAME			 PROJECT_NAME ,
       CDL.EXPENDITURE_ITEM_ID   TRANSACTION_NUMBER,
       CDL.LINE_NUM		 LINE_NUMBER,
       JC.USER_JE_CATEGORY_NAME  JOURNAL_CATEGORY,
       EI.EXPENDITURE_ITEM_DATE  TRANSACTION_DATE,
       XE.TRANSACTION_DATE       GL_DATE,
       DECODE ( JC.EVENT_CLASS_CODE ,
			'BURDEN_COST' , CDL.ACCT_BURDENED_COST ,
					CDL.ACCT_RAW_COST
	      )		 AMOUNT,
       LKP_EXC.MEANING		 EXCEPTION_REASON,
       XER.ENCODED_MSG		 ERROR_MSG,
       DECODE ( LINE_TYPE, 'R', NVL(CDL.PJI_SUMMARIZED_FLAG, 'Y'), Null) PJI_SUMM_FLG,
       XE.PROCESS_STATUS_CODE    EVENT_STATUS
FROM PA_PROJECTS_ALL PRJ,
       PA_COST_DISTRIBUTION_LINES CDL,
       XLA_EVENTS XE,
       XLA_EVENT_CLASS_ATTRS_FVL JC,
       PA_LOOKUPS LKP_EXC,
       XLA_ACCOUNTING_ERRORS XER,
       PA_EXPENDITURE_ITEMS_ALL EI,
       XLA_EVENT_TYPES_B XET
 WHERE XE.APPLICATION_ID = 275 
   AND XE.PROCESS_STATUS_CODE <> 'P'
   AND XER.EVENT_ID(+) = XE.EVENT_ID
   AND CDL.ACCT_EVENT_ID = XE.EVENT_ID
   AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
   AND PRJ.PROJECT_ID    = CDL.PROJECT_ID
   AND LKP_EXC.LOOKUP_TYPE = 'PROJECT EVENT ERRORS'
   AND NVL(:P_TRAN_TYPE, 'EXPENDITURES') = 'EXPENDITURES'
   AND XET.APPLICATION_ID = 275
   AND XET.EVENT_TYPE_CODE  = XE.EVENT_TYPE_CODE 
   AND JC.APPLICATION_ID = 275
   AND JC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE
   AND (
		( :P_MODE = 'S' 
		 AND CDL.REQUEST_ID = :P_CONC_REQUEST_ID
		)
		OR
		(
		 :P_MODE = 'R'
		 AND XE.EVENT_DATE BETWEEN :P_FIRST_DATE AND :P_LAST_DATE
		)
       )
   AND LKP_EXC.LOOKUP_CODE =  DECODE(XE.PROCESS_STATUS_CODE, 
						'U','UNPROCESSED'  ,
			                        'D' ,'DRAFT' ,
						'ERROR'
				     )
UNION ALL
SELECT XE.EVENT_ID		EVEN_ID,
       PRJ.SEGMENT1		PROJECT_NUMBER,
       PRJ.NAME			PROJECT_NAME ,
       CCDL.EXPENDITURE_ITEM_ID TRANSACTION_NUMBER,
       CCDL.LINE_NUM		LINE_NUMBER,
       JC.USER_JE_CATEGORY_NAME JOURNAL_CATEGORY,
       EI.EXPENDITURE_ITEM_DATE TRANSACTION_DATE,
       XE.TRANSACTION_DATE      GL_DATE,
       CCDL.AMOUNT		AMOUNT,
       LKP_EXC.MEANING		EXCEPTION_REASON,
       XER.ENCODED_MSG		ERROR_MSG,
       NULL			PJI_SUMM_FLG,
       XE.PROCESS_STATUS_CODE   EVENT_STATUS
FROM PA_PROJECTS_ALL PRJ,
       PA_CC_DIST_LINES CCDL,
       XLA_EVENTS XE,
       XLA_EVENT_CLASS_ATTRS_FVL JC,
       PA_LOOKUPS LKP_EXC,
       XLA_ACCOUNTING_ERRORS XER,
       PA_EXPENDITURE_ITEMS_ALL  EI,
       XLA_EVENT_TYPES_B XET
 WHERE XE.APPLICATION_ID = 275 
   AND XE.PROCESS_STATUS_CODE <> 'P'
   AND XER.EVENT_ID(+) = XE.EVENT_ID
   AND CCDL.ACCT_EVENT_ID = XE.EVENT_ID
   AND EI.EXPENDITURE_ITEM_ID = CCDL.EXPENDITURE_ITEM_ID
   AND PRJ.PROJECT_ID    = CCDL.PROJECT_ID
   AND JC.APPLICATION_ID = 275
   AND JC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE 
   AND XET.APPLICATION_ID = 275
   AND XET.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE
   AND LKP_EXC.LOOKUP_TYPE = 'PROJECT EVENT ERRORS'
   AND NVL(:P_TRAN_TYPE,'CROSSCHARGE') = 'CROSSCHARGE'
   AND (
		( :P_MODE = 'S' 
		 AND CCDL.REQUEST_ID = :P_CONC_REQUEST_ID
		)
		OR
		(
		 :P_MODE = 'R'
		 AND XE.EVENT_DATE BETWEEN :P_FIRST_DATE AND :P_LAST_DATE
		)
       )
   AND LKP_EXC.LOOKUP_CODE =  DECODE(XE.PROCESS_STATUS_CODE, 
						'U','UNPROCESSED'  ,
			                        'D' ,'DRAFT' ,
						'ERROR'
				     )
UNION ALL
SELECT XE.EVENT_ID		EVEN_ID,
       PRJ.SEGMENT1		PROJECT_NUMBER,
       PRJ.NAME			PROJECT_NAME ,
       RDL.DRAFT_REVENUE_NUM    TRANSACTION_NUMBER,
       NULL			LINE_NUMBER,
       JC.USER_JE_CATEGORY_NAME JOURNAL_CATEGORY,
       RDL.ACCRUE_THROUGH_DATE  TRANSACTION_DATE,
       XE.TRANSACTION_DATE      GL_DATE,
       SUM(RI.AMOUNT)		AMOUNT,
       LKP_EXC.MEANING		EXCEPTION_REASON,
       XER.ENCODED_MSG		ERROR_MSG,
       NULL			PJI_SUMM_FLG,
       XE.PROCESS_STATUS_CODE   EVENT_STATUS
FROM PA_PROJECTS_ALL PRJ,
       PA_DRAFT_REVENUES RDL,
       PA_DRAFT_REVENUE_ITEMS RI ,
       XLA_EVENTS XE,
       XLA_EVENT_CLASS_ATTRS_FVL JC,
       PA_LOOKUPS LKP_EXC,
       XLA_ACCOUNTING_ERRORS XER,
       XLA_EVENT_TYPES_B XET
 WHERE XE.PROCESS_STATUS_CODE <> 'P'
   AND XER.EVENT_ID(+) = XE.EVENT_ID
   AND RDL.EVENT_ID = XE.EVENT_ID
   AND PRJ.PROJECT_ID = RDL.PROJECT_ID
   AND RI.PROJECT_ID = RDL.PROJECT_ID 
   AND RI.DRAFT_REVENUE_NUM = RDL.DRAFT_REVENUE_NUM 
   AND JC.APPLICATION_ID = 275
   AND JC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE
   AND XET.APPLICATION_ID = 275
   AND XET.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE
   AND LKP_EXC.LOOKUP_TYPE = 'PROJECT EVENT ERRORS'
   AND NVL(:P_TRAN_TYPE,'REVENUE') = 'REVENUE'
   AND (
		( :P_MODE = 'S' 
		 AND RDL.REQUEST_ID = :P_CONC_REQUEST_ID
		)
		OR
		(
		 :P_MODE = 'R'
		 AND XE.EVENT_DATE BETWEEN :P_FIRST_DATE AND :P_LAST_DATE
		)
       )
   AND LKP_EXC.LOOKUP_CODE =  DECODE(XE.PROCESS_STATUS_CODE, 
						'U','UNPROCESSED'  ,
			                        'D' ,'DRAFT' ,
						'ERROR'
				     )
GROUP BY XE.EVENT_ID		,
       PRJ.SEGMENT1		, 
       PRJ.NAME			,
       RDL.DRAFT_REVENUE_NUM    ,
       NULL			,
       JC.USER_JE_CATEGORY_NAME ,
       RDL.ACCRUE_THROUGH_DATE  ,
       XE.TRANSACTION_DATE      ,
       LKP_EXC.MEANING		,
       XER.ENCODED_MSG		,
       NULL			,
       XE.PROCESS_STATUS_CODE
ORDER BY JOURNAL_CATEGORY, TRANSACTION_NUMBER, LINE_NUMBER