SELECT
TABLE1.APPLICATION_ID APPLICATION_ID
,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE
,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME
,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME
,TABLE1.EVENT_ID EVENT_ID
,TABLE1.EVENT_STATUS_CODE EVENT_STATUS_CODE
,TABLE1.EVENT_STATUS EVENT_STATUS
,TABLE1.EVENT_NUMBER EVENT_NUMBER
,TABLE1.EVENT_DATE EVENT_DATE
,TABLE1.ENTITY_ID ENTITY_ID
,TABLE1.LEDGER_ID LEDGER_ID
,TABLE1.LEDGER_NAME LEDGER_NAME
,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
,TABLE1.COA_ID COA_ID
,TABLE1.PAD_NAME PAD_NAME
,TABLE1.PAD_VERSION PAD_VERSION
,TABLE1.AE_HEADER_ID AE_HEADER_ID
,TABLE1.BALANCE_TYPE BALANCE_TYPE
,TABLE1.BALANCE_TYPE_MEANING BALANCE_TYPE_MEANING
,TABLE1.GL_DATE GL_DATE
,TABLE1.AE_DESCRIPTION AE_DESCRIPTION
,TABLE1.AE_STATUS_CODE AE_STATUS_CODE
,TABLE1.AE_STATUS_MEANING AE_STATUS_MEANING
,TABLE1.DISPLAYED_LINE_NUM DISPLAYED_LINE_NUM
,TABLE1.AE_LINE_NUM AE_LINE_NUM
,TABLE1.ACCOUNT_CCID ACCOUNT_CCID
,TABLE1.ENTERED_CURRENCY ENTERED_CURRENCY
,TABLE1.ENTERED_DR ENTERED_DR
,TABLE1.ENTERED_CR ENTERED_CR
,TABLE1.ACCOUNTED_DR ACCOUNTED_DR
,TABLE1.ACCOUNTED_CR ACCOUNTED_CR
,TABLE1.ACCOUNTING_CLASS ACCOUNTING_CLASS
,TABLE1.ERROR_AE_LINE_NUM ERROR_AE_LINE_NUM
,TABLE1.MESSAGE_NUMBER MESSAGE_NUMBER
,TABLE1.ENCODED_MSG ENCODED_MSG
,TABLE1.ACCOUNT_SEGMENTS ACCOUNT_SEGMENTS
,substr(USERIDS,1,instr(USERIDS,'|',1,1)-1) USER_TRX_IDENTIFIER_NAME_1
,substr(USERIDS,instr(USERIDS,'|',1,1)+1,(instr(USERIDS,'|',1,2)-1-instr(USERIDS,'|',1,1))) USER_TRX_IDENTIFIER_VALUE_1
,substr(USERIDS,instr(USERIDS,'|',1,2)+1,(instr(USERIDS,'|',1,3)-1-instr(USERIDS,'|',1,2))) USER_TRX_IDENTIFIER_NAME_2
,substr(USERIDS,instr(USERIDS,'|',1,3)+1,(instr(USERIDS,'|',1,4)-1-instr(USERIDS,'|',1,3))) USER_TRX_IDENTIFIER_VALUE_2
,substr(USERIDS,instr(USERIDS,'|',1,4)+1,(instr(USERIDS,'|',1,5)-1-instr(USERIDS,'|',1,4))) USER_TRX_IDENTIFIER_NAME_3
,substr(USERIDS,instr(USERIDS,'|',1,5)+1,(instr(USERIDS,'|',1,6)-1-instr(USERIDS,'|',1,5))) USER_TRX_IDENTIFIER_VALUE_3
,substr(USERIDS,instr(USERIDS,'|',1,6)+1,(instr(USERIDS,'|',1,7)-1-instr(USERIDS,'|',1,6))) USER_TRX_IDENTIFIER_NAME_4
,substr(USERIDS,instr(USERIDS,'|',1,7)+1,(instr(USERIDS,'|',1,8)-1-instr(USERIDS,'|',1,7))) USER_TRX_IDENTIFIER_VALUE_4
,substr(USERIDS,instr(USERIDS,'|',1,8)+1,(instr(USERIDS,'|',1,9)-1-instr(USERIDS,'|',1,8))) USER_TRX_IDENTIFIER_NAME_5
,substr(USERIDS,instr(USERIDS,'|',1,9)+1,(instr(USERIDS,'|',1,10)-1-instr(USERIDS,'|',1,9))) USER_TRX_IDENTIFIER_VALUE_5
,substr(USERIDS,instr(USERIDS,'|',1,10)+1,(instr(USERIDS,'|',1,11)-1-instr(USERIDS,'|',1,10))) USER_TRX_IDENTIFIER_NAME_6
,substr(USERIDS,instr(USERIDS,'|',1,11)+1,(instr(USERIDS,'|',1,12)-1-instr(USERIDS,'|',1,11))) USER_TRX_IDENTIFIER_VALUE_6
,substr(USERIDS,instr(USERIDS,'|',1,12)+1,(instr(USERIDS,'|',1,13)-1-instr(USERIDS,'|',1,12))) USER_TRX_IDENTIFIER_NAME_7
,substr(USERIDS,instr(USERIDS,'|',1,13)+1,(instr(USERIDS,'|',1,14)-1-instr(USERIDS,'|',1,13))) USER_TRX_IDENTIFIER_VALUE_7
,substr(USERIDS,instr(USERIDS,'|',1,14)+1,(instr(USERIDS,'|',1,15)-1-instr(USERIDS,'|',1,14))) USER_TRX_IDENTIFIER_NAME_8
,substr(USERIDS,instr(USERIDS,'|',1,15)+1,(instr(USERIDS,'|',1,16)-1-instr(USERIDS,'|',1,15))) USER_TRX_IDENTIFIER_VALUE_8
,substr(USERIDS,instr(USERIDS,'|',1,16)+1,(instr(USERIDS,'|',1,17)-1-instr(USERIDS,'|',1,16))) USER_TRX_IDENTIFIER_NAME_9
,substr(USERIDS,instr(USERIDS,'|',1,17)+1,(instr(USERIDS,'|',1,18)-1-instr(USERIDS,'|',1,17))) USER_TRX_IDENTIFIER_VALUE_9
,substr(USERIDS,instr(USERIDS,'|',1,18)+1,(instr(USERIDS,'|',1,19)-1-instr(USERIDS,'|',1,18))) USER_TRX_IDENTIFIER_NAME_10
,substr(USERIDS,instr(USERIDS,'|',1,19)+1,(length(USERIDS)-instr(USERIDS,'|',1,19))) USER_TRX_IDENTIFIER_VALUE_10
FROM
(
SELECT
EVT.APPLICATION_ID APPLICATION_ID
,XET.EVENT_CLASS_CODE EVENT_CLASS_CODE
,XEC.NAME EVENT_CLASS_NAME
,XET.NAME EVENT_TYPE_NAME
,EVT.EVENT_ID EVENT_ID
,EVT.PROCESS_STATUS_CODE EVENT_STATUS_CODE
,XL3.MEANING EVENT_STATUS
,EVT.EVENT_NUMBER EVENT_NUMBER
,TO_CHAR(EVT.EVENT_DATE,'YYYY-MM-DD') EVENT_DATE
,EVT.ENTITY_ID ENTITY_ID
,XGL.LEDGER_ID LEDGER_ID
,XGL.NAME LEDGER_NAME
,XGL.CURRENCY_CODE LEDGER_CURRENCY
,XGL.CHART_OF_ACCOUNTS_ID COA_ID
,XPR.NAME PAD_NAME
,XPR.PRODUCT_RULE_VERSION PAD_VERSION
,AEH.AE_HEADER_ID AE_HEADER_ID
,AEH.BALANCE_TYPE_CODE BALANCE_TYPE
,XLK.MEANING BALANCE_TYPE_MEANING
,TO_CHAR(AEH.ACCOUNTING_DATE
,'YYYY-MM-DD') GL_DATE
,AEH.DESCRIPTION AE_DESCRIPTION
,AEH.ACCOUNTING_ENTRY_STATUS_CODE AE_STATUS_CODE
,XL1.MEANING AE_STATUS_MEANING
,AEL.DISPLAYED_LINE_NUMBER DISPLAYED_LINE_NUM
,AEL.AE_LINE_NUM AE_LINE_NUM
,AEL.CODE_COMBINATION_ID ACCOUNT_CCID
,AEL.CURRENCY_CODE ENTERED_CURRENCY
,AEL.ENTERED_DR ENTERED_DR
,AEL.ENTERED_CR ENTERED_CR
,AEL.ACCOUNTED_DR ACCOUNTED_DR
,AEL.ACCOUNTED_CR ACCOUNTED_CR
,XL2.MEANING ACCOUNTING_CLASS
,NULL ERROR_AE_LINE_NUM
,NULL MESSAGE_NUMBER
,NULL ENCODED_MSG
,FND_FLEX_EXT.GET_SEGS
('SQLGL'
,'GL#'
,XGL.CHART_OF_ACCOUNTS_ID
,AEL.CODE_COMBINATION_ID) ACCOUNT_SEGMENTS
&P_TRX_IDENTIFIERS_1
&P_TRX_IDENTIFIERS_2
&P_TRX_IDENTIFIERS_3
&P_TRX_IDENTIFIERS_4
&P_TRX_IDENTIFIERS_5
FROM
XLA_EVENTS EVT
,XLA_TRANSACTION_ENTITIES ENT
,XLA_EVENT_TYPES_VL XET
,XLA_EVENT_CLASSES_TL XEC
,XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,XLA_GL_LEDGERS_V XGL
,XLA_PRODUCT_RULES_VL XPR
,XLA_LOOKUPS XLK
,XLA_LOOKUPS XL1
,XLA_LOOKUPS XL2
,XLA_LOOKUPS XL3
WHERE
EVT.APPLICATION_ID = :APPLICATION_ID
AND EVT.REQUEST_ID = :REQUEST_ID
AND ENT.APPLICATION_ID = EVT.APPLICATION_ID
AND ENT.ENTITY_ID = EVT.ENTITY_ID
AND XET.APPLICATION_ID = ENT.APPLICATION_ID
AND XET.ENTITY_CODE = ENT.ENTITY_CODE
AND XET.EVENT_TYPE_CODE = EVT.EVENT_TYPE_CODE
AND XEC.APPLICATION_ID = XET.APPLICATION_ID
AND XEC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE
AND XEC.LANGUAGE = USERENV('LANG')
AND AEH.APPLICATION_ID = EVT.APPLICATION_ID
AND AEH.EVENT_ID = EVT.EVENT_ID
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'
AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XGL.LEDGER_ID = AEH.LEDGER_ID
AND XPR.APPLICATION_ID = AEH.APPLICATION_ID
AND XPR.PRODUCT_RULE_TYPE_CODE = AEH.PRODUCT_RULE_TYPE_CODE
AND XPR.PRODUCT_RULE_CODE = AEH.PRODUCT_RULE_CODE
AND XPR.AMB_CONTEXT_CODE = AEH.AMB_CONTEXT_CODE
AND XLK.LOOKUP_TYPE = 'XLA_BALANCE_TYPE'
AND XLK.LOOKUP_CODE = AEH.BALANCE_TYPE_CODE
AND XL1.LOOKUP_TYPE = 'XLA_ACCOUNTING_ENTRY_STATUS'
AND XL1.LOOKUP_CODE = AEH.ACCOUNTING_ENTRY_STATUS_CODE
AND XL2.LOOKUP_TYPE = 'XLA_ACCOUNTING_CLASS'
AND XL2.LOOKUP_CODE = AEL.ACCOUNTING_CLASS_CODE
AND XL3.LOOKUP_TYPE = 'XLA_EVENT_PROCESS_STATUS'
AND XL3.LOOKUP_CODE = EVT.PROCESS_STATUS_CODE
&P_EVENT_FILTER
&P_ZERO_AMT_FILTER
AND :P_REPORT_STYLE <> 'N'
UNION
SELECT
EVT.APPLICATION_ID APPLICATION_ID
,XET.EVENT_CLASS_CODE EVENT_CLASS_CODE
,XEC.NAME EVENT_CLASS_NAME
,XET.NAME EVENT_TYPE_NAME
,EVT.EVENT_ID EVENT_ID
,EVT.PROCESS_STATUS_CODE EVENT_STATUS_CODE
,XLK.MEANING EVENT_STATUS
,EVT.EVENT_NUMBER EVENT_NUMBER
,TO_CHAR(EVT.EVENT_DATE,'YYYY-MM-DD') EVENT_DATE
,EVT.ENTITY_ID ENTITY_ID
,XGL.LEDGER_ID LEDGER_ID
,XGL.NAME LEDGER_NAME
,XGL.CURRENCY_CODE LEDGER_CURRENCY
,XGL.CHART_OF_ACCOUNTS_ID COA_ID
,NULL PAD_NAME
,NULL PAD_VERSION
,ERR.AE_HEADER_ID AE_HEADER_ID
,NULL BALANCE_TYPE
,NULL BALANCE_TYPE_MEANING
,NULL GL_DATE
,NULL AE_DESCRIPTION
,NULL AE_STATUS_CODE
,NULL AE_STATUS_MEANING
,ERR.AE_LINE_NUM DISPLAYED_LINE_NUM
,ERR.AE_LINE_NUM AE_LINE_NUM
,NULL ACCOUNT_CCID
,NULL ENTERED_CURRENCY
,NULL ENTERED_DR
,NULL ENTERED_CR
,NULL ACCOUNTED_DR
,NULL ACCOUNTED_CR
,NULL ACCOUNTING_CLASS
,ERR.AE_LINE_NUM ERROR_AE_LINE_NUM
,ERR.MESSAGE_NUMBER MESSAGE_NUMBER
,ERR.ENCODED_MSG ENCODED_MSG
,NULL ACCOUNT_SEGMENTS
&P_TRX_IDENTIFIERS_1
&P_TRX_IDENTIFIERS_2
&P_TRX_IDENTIFIERS_3
&P_TRX_IDENTIFIERS_4
&P_TRX_IDENTIFIERS_5
FROM
XLA_EVENTS EVT
,XLA_TRANSACTION_ENTITIES ENT
,XLA_EVENT_TYPES_VL XET
,XLA_EVENT_CLASSES_TL XEC
,XLA_ACCOUNTING_ERRORS ERR
,XLA_GL_LEDGERS_V XGL
,XLA_LOOKUPS XLK
WHERE
EVT.APPLICATION_ID = :APPLICATION_ID
AND EVT.REQUEST_ID = :REQUEST_ID
AND EVT.PROCESS_STATUS_CODE IN ('I','E','R')
AND XLK.LOOKUP_TYPE = 'XLA_EVENT_PROCESS_STATUS'
AND XLK.LOOKUP_CODE = EVT.PROCESS_STATUS_CODE
AND ENT.APPLICATION_ID = EVT.APPLICATION_ID
AND ENT.ENTITY_ID = EVT.ENTITY_ID
AND XET.APPLICATION_ID = ENT.APPLICATION_ID
AND XET.ENTITY_CODE = ENT.ENTITY_CODE
AND XET.EVENT_TYPE_CODE = EVT.EVENT_TYPE_CODE
AND XEC.APPLICATION_ID = XET.APPLICATION_ID
AND XEC.EVENT_CLASS_CODE = XET.EVENT_CLASS_CODE
AND XEC.LANGUAGE = USERENV('LANG')
AND ERR.EVENT_ID = EVT.EVENT_ID
AND XGL.LEDGER_ID(+) = ERR.LEDGER_ID
AND :P_REPORT_STYLE <> 'N'
)TABLE1 |