AHCS Create Accounting Errors Report

WITH SECURITY_TBL AS
(SELECT DISTINCT GL.LEDGER_ID
FROM 
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU

WHERE 
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
	AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
	AND UPPER(FURDA.role_name) in ('XXC_AHCS_MANAGER_JOB', 'XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND  UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
	AND PU.USER_GUID = FURDA.USER_GUID
	
)

SELECT  /*+parallel(24) */ 
 GLL.NAME LEDGER_NAME
, XAH.PERIOD_NAME AS ACCOUNTING_PERIOD
,XAH.ACCOUNTING_DATE
, XEC.NAME AS EVENT_CLASS
, XET.NAME AS EVENT_TYPE
,XE.EVENT_DATE
, GJST.USER_JE_SOURCE_NAME AS JOURNAL_SOURCE 
, GJCT.USER_JE_CATEGORY_NAME AS JOURNAL_CATEGORY
, XTE.TRANSACTION_NUMBER
, XAL.AE_LINE_NUM LINE_NUMBER
,XAL.CURRENCY_CODE ENTERED_CURRENCY
,XAL.ENTERED_DR ENTERED_DR
,XAL.ENTERED_CR ENTERED_CR
,GLL.CURRENCY_CODE LEDGER_CURRENCY
,XAL.ACCOUNTED_DR ACCOUNTED_DR
,XAL.ACCOUNTED_CR ACCOUNTED_CR
, CASE WHEN NVL(GCC.SEGMENT4,'-1')!='-1' THEN GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5
 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 END AS ACCOUNT_CODE_COMBINATION
 , FLV3.MEANING AS ACCOUNTING_CLASS
,FLV.MEANING ACCOUNTING_ENTRY_STATUS
, 'Unposted' POSTING_STATUS
, XAH.CREATION_DATE JOURNAL_CREATION_DATE
,XAH.DESCRIPTION HEADER_DESCRIPTION
,XAL.DESCRIPTION LINE_DESCRIPTION
,XAE.encoded_msg Error_Message
,XTH.REQUEST_ID
,XAE.REQUEST_ID ERROR_REQUEST_ID
FROM
XLA_TRANSACTION_ENTITIES XTE,
XLA_TRANSACTION_HEADERS XTH,
XLA_AE_HEADERS XAH,
GL_LEDGERS GLL,
XLA_SUBLEDGERS XSL,
XLA_AE_LINES XAL,
GL_CODE_COMBINATIONS GCC,
XLA_SUBLEDGERS_TL XST
,XLA_EVENT_TYPES_TL XET
, GL_JE_SOURCES_TL GJST
, GL_JE_CATEGORIES_TL GJCT
--,gl_je_batches GJB
--, FND_LOOKUP_VALUES_VL FLV2
, FND_LOOKUP_VALUES_VL FLV
,FND_LOOKUP_VALUES_VL FLV3
--,GL_IMPORT_REFERENCES GIR

,XLA_EVENT_CLASSES_TL XEC
,XLA_EVENTS XE

,XLA_ACCOUNTING_ERRORS XAE
 --,XLA_FSN_EVENTS_INT EVTINT 
,SECURITY_TBL ST

--,XLA_AE_LINES XAL1

WHERE 1=1
AND ST.LEDGER_ID=GLL.LEDGER_ID
--AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID=XTH.APPLICATION_ID(+)
AND XAH.EVENT_ID=XTH.EVENT_ID(+)
--AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAH.ENTITY_ID = XTE.ENTITY_ID 
AND XAH.APPLICATION_ID = XTE.APPLICATION_ID  
AND XAH.APPLICATION_ID = XSL.APPLICATION_ID 
AND XSL.APPLICATION_ID=XST.APPLICATION_ID
AND xsT.LANGUAGE  = userenv('LANG')
and xah.application_id = xet.application_id
and xah.event_type_code = xet.event_type_code
AND xET.LANGUAGE  = userenv('LANG')
AND XSL.IMPLEMENTATION_MODE_CODE = 'SIMPLIFIED'
AND XAH.LEDGER_ID = GLL.LEDGER_ID
--AND XST.APPLICATION_NAME ='DOM INVENTORY'
--AND NVL(XAL.SR31,'-1')!='-1'
AND XAL.CODE_COMBINATION_ID  = GCC.CODE_COMBINATION_ID(+)
AND GJST.JE_SOURCE_NAME=XSL.JE_SOURCE_NAME
AND GJCT.JE_CATEGORY_NAME(+)=XAH.JE_CATEGORY_NAME
AND GJCT.LANGUAGE (+) = userenv('LANG')
AND GJST.LANGUAGE  = userenv('LANG')
AND FLV3.LOOKUP_TYPE(+)='XLA_ACCOUNTING_CLASS'
AND FLV3.LOOKUP_CODE(+)= XAL.ACCOUNTING_CLASS_CODE
AND FLV.LOOKUP_TYPE(+)='XLA_ACCOUNTING_ENTRY_STATUS'
AND FLV.LOOKUP_CODE(+)=XAH.ACCOUNTING_ENTRY_STATUS_CODE
/*AND GJB.GROUP_ID(+)=XAH.GROUP_ID
   AND FLV2.LOOKUP_TYPE(+)='MJE_BATCH_STATUS'
   AND FLV2.LOOKUP_CODE(+)=GJB.STATUS
   AND GIR.GL_SL_LINK_ID(+)=XAL.GL_SL_LINK_ID	
   AND GIR.GL_SL_LINK_TABLE(+)	=XAL.GL_SL_LINK_TABLE	
   AND GIR.JE_BATCH_ID=GJB.JE_BATCH_ID(+)
   AND XAL.ACCOUNTING_CLASS_CODE =(SELECT  XAL2.ACCOUNTING_CLASS_CODE
                                   FROM XLA_AE_LINES XAL2
								   WHERE XAL2.AE_HEADER_ID=XAL.AE_HEADER_ID
								   AND XAL2.AE_LINE_NUM=XAL.AE_LINE_NUM
								   AND XAL2.APPLICATION_ID=XAL.APPLICATION_ID
								   AND (XAL2.ACCOUNTING_CLASS_CODE!='EXCHANGE_GAIN_LOSS'
								   OR (XAL2.ACCOUNTED_DR!=0 OR XAL2.ACCOUNTED_CR!=0))
								   )*/
								   
								   
	AND XEC.APPLICATION_ID=XET.APPLICATION_ID
	AND XEC.ENTITY_CODE=XET.ENTITY_CODE
	AND XEC.EVENT_CLASS_CODE=XET.EVENT_CLASS_CODE
	AND XEC.LANGUAGE=userenv('LANG')
	AND XAH.APPLICATION_ID=XEC.APPLICATION_ID
	AND XAH.APPLICATION_ID=XE.APPLICATION_ID
	AND XAH.EVENT_ID=XE.EVENT_ID
	AND XAH.ENTITY_ID=XE.ENTITY_ID
	
	AND XAH.APPLICATION_ID(+)=XAE.APPLICATION_ID
	AND XAH.LEDGER_ID(+)=XAE.LEDGER_ID
	AND XAH.EVENT_ID(+)=XAE.EVENT_ID
	AND XAH.ENTITY_ID(+)=XAE.ENTITY_ID
	AND XAH.AE_HEADER_ID(+)=XAE.AE_HEADER_ID
	AND XAL.APPLICATION_ID(+)=XAE.APPLICATION_ID
	and XAL.AE_HEADER_ID(+)=XAE.AE_HEADER_ID
	AND XAL.ae_line_num(+)=XAE.ae_line_num
	and XAE.EVENT_ID=XE.EVENT_ID
	
	 /*AND  XAE.EVENT_ID 	= EVTINT.EVENT_ID
	 AND XE.EVENT_ID                 = EVTINT.EVENT_ID
	 AND  XET.EVENT_TYPE_CODE         = EVTINT.EVENT_TYPE_CODE
	 AND  XEC.APPLICATION_ID          = EVTINT.APPLICATION_ID
       AND  XEC.EVENT_CLASS_CODE        = EVTINT.EVENT_CLASS_CODE
	  -- AND  EVTINT.REPORT_REQUEST_ID    = XAE.REQUEST_ID */
	   AND  (XE.PROCESS_STATUS_CODE    IN ('I')
            OR NVL(XE.HAS_WARNINGS_FLAG,'N')    = 'Y')
	
	AND UPPER(FLV.MEANING) NOT IN ('FINAL')

	
--Parameters
AND (XST.APPLICATION_NAME IN (:P_SUBLEDGER_APPLICATION) OR 'All' IN ('All'||:P_SUBLEDGER_APPLICATION))
AND XAH.ACCOUNTING_DATE BETWEEN :P_FROM_ACCOUNTING_DATE AND :P_TO_ACCOUNTING_DATE
AND (XAH.PERIOD_NAME IN (:P_ACCOUNTING_PERIOD) OR 'All' IN ('All'||:P_ACCOUNTING_PERIOD))
AND (GLL.NAME IN (:P_Ledger) OR 'All' IN ('All'||:P_Ledger))
AND (XTH.REQUEST_ID  IN (:P_REQUEST_ID) OR 'All' IN ('All'||:P_REQUEST_ID))
AND (XET.NAME  IN (:P_EVENT_TYPE) OR 'All' IN ('All'||:P_EVENT_TYPE))

ORDER BY GLL.NAME, XAH.PERIOD_NAME, XTE.TRANSACTION_NUMBER, XAL.AE_LINE_NUM, XET.NAME/*,  GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5
 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9*/