Reports2017-11-18T12:27:27+00:00

JA Create Accounting for Asia/Pacific FA

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: Create Accounting for Asia/Pacific FA
Short Name: JAFAACCP
DB package: XLA_CREATE_ACCT_RPT_PVT
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
Parameter Name SQL text Validation
P_INCLUDE_USER_TRX_IDENTIFIERS
 
Include Zero Amount Lines
 
Post in GL
 
Transfer to GL
 
Report Level
 
Errors Only
 
Accounting Mode Name
 
Create Accounting
 
Process Category Name
 
Ledger Name
 
Application Name
 
Source Application Name
 
Mixed Currency Precision
 
Number
XLA_SRS_NO_VALIDATION
 
XLA_SRS_ACCOUNTING_MODE_DUMMY
 
XLA_SRS_NO_VALIDATION
 
Create Accounting
 
LOV Oracle
XLA_SRS_SUBLEDGER_LEDGERS_JL
 
LOV Oracle
XLA_SRS_NO_VALIDATION
 
Application
 
LOV Oracle
Source Application
 
LOV Oracle
P_INCLUDE_USER_TRX_ID_FLAG
 
LOV Oracle
Include Zero Amt lines Flag
 
LOV Oracle
General Ledger Batch Name
 
Post in General Ledger
 
LOV Oracle
Transfer to General Ledger
 
LOV Oracle
Report
 
LOV Oracle
Errors Only
 
LOV Oracle
Accounting Mode
 
LOV Oracle
End Date
 
Date
Process Category
 
LOV Oracle
Book Type Code
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept