XLA Create Accounting
Description
- Categories
- BI Publisher
- Application
- Subledger Accounting
- Source
- Create Accounting
- Short Name
- XLAACCPB
- DB package
- XLA_CREATE_ACCT_RPT_PVT
Imported from BI Publisher
Report for the Accounting Program
Report for the Accounting Program
SQL 220 lines
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 |
Parameters 10
| Parameter | Type | List of values SQL |
|---|---|---|
| Ledger | LOV Oracle | |
| Process Category | LOV Oracle | |
| End Date | Date | |
| Mode | LOV Oracle | |
| Errors Only | LOV Oracle | |
| Report | LOV Oracle | |
| Transfer to General Ledger | LOV Oracle | |
| Post in General Ledger | LOV Oracle | |
| General Ledger Batch Name | Char | |
| Include User Transaction Identifiers | LOV Oracle |