CST Create Accounting - Cost Management

Description

Categories: BI Publisher
Application: Bills of Material
Source: Create Accounting - Cost Management
Short Name: CSTCRACC
DB package: XLA_CREATE_ACCT_RPT_PVT

Imported from BI Publisher
Create and view accounting for Inventory, WIP, and Receiving

SQL 221 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

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 12

Parameter	Type	List of values SQL
Operating Unit	LOV
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
Inventory Organization	LOV Oracle
Include User Transaction Identifiers	LOV Oracle

Description

SQL 221 lines

Parameters 12

Find out more

Features

Download

FAQ