XLA Import Initial Balance for Third Party Control Account

➜ Back to Library
Description
Categories: BI Publisher
Application: Subledger Accounting
Source: Import Initial Balance for Third Party Control Account
Short Name: XLACAINBAL
DB package: XLA_CA_BALANCES_PKG
Run XLA Import Initial Balance for Third Party Control Account and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
                    APPLICATION_NAME
                   ,LEDGER_NAME
                   ,ACCOUNTING_CODE_COMBINATION
                   ,PERIOD_NAME
                   ,PARTY_TYPE
                   ,PARTY_NAME
                   ,PARTY_SITE_NAME
                   ,DECODE (SUBSTR(ERR_APPLICATION,length(ERR_APPLICATION),1),',',SUBSTR(ERR_APPLICATION,1,length(ERR_APPLICATION)-1),ERR_APPLICATION)  ERR_APPLICATION
                   ,DECODE (SUBSTR(ERR_LEDGER,length(ERR_LEDGER),1),',',SUBSTR(ERR_LEDGER,1,length(ERR_LEDGER)-1),ERR_LEDGER)                           ERR_LEDGER
                   ,DECODE (SUBSTR(ERR_ACCOUNT,length(ERR_ACCOUNT),1),',',SUBSTR(ERR_ACCOUNT,1,length(ERR_ACCOUNT)-1),ERR_ACCOUNT)                      ERR_ACCOUNT
                   ,DECODE (SUBSTR(ERR_PERIOD,length(ERR_PERIOD),1),',',SUBSTR(ERR_PERIOD,1,length(ERR_PERIOD)-1),ERR_PERIOD)                           ERR_PERIOD
                   ,DECODE (SUBSTR(ERR_OTHER,length(ERR_OTHER),1),',',SUBSTR(ERR_OTHER,1,length(ERR_OTHER)-1),ERR_OTHER)                                ERR_OTHER
                  FROM
                  (SELECT NVL(fap.application_name,xib.application_id)  APPLICATION_NAME
      ,NVL(gll.name,xib.ledger_id)                   LEDGER_NAME
      ,gcc.CONCATENATED_SEGMENTS ACCOUNTING_CODE_COMBINATION
      ,xib.period_name       PERIOD_NAME
      ,NVL(xlk.meaning,xib.party_type_code)          PARTY_TYPE
      ,NVL(CASE WHEN xib.party_type_code = 'S' THEN
            ( SELECT party_name
                FROM hz_parties
               WHERE party_id = xib.party_id)
            WHEN xib.party_type_code = 'C' THEN
            (SELECT hzp.party_name 
               FROM hz_cust_accounts hca
                   ,hz_parties   hzp
              WHERE hca.cust_account_id  = xib.party_id
                AND hzp.party_id         = hca.party_id)
           END , xib.party_id)                     PARTY_NAME
     ,NVL(CASE WHEN xib.party_type_code = 'S' THEN
           ( SELECT hps.party_site_name
             FROM  ap_supplier_sites_all  asp
                  ,hz_party_sites        hps
            WHERE  asp.vendor_site_id = 
                              xib.party_site_id
              AND  hps.party_site_id  = 
                              asp.party_site_id )
          WHEN xib.party_type_code = 'C' THEN
            (SELECT hps.party_site_name
             FROM  hz_cust_site_uses_all hcs
                  ,hz_cust_acct_sites_all hca
                  ,hz_party_sites  hps
             WHERE hcs.site_use_id = xib.party_site_id
              AND  hcs.cust_acct_site_id = 
                               hca.cust_acct_site_id
              AND  hca.party_site_id = 
                               hps.party_site_id)
            END , xib.party_site_id  )            PARTY_SITE_NAME  
    ,xib.init_balance_dr      INIT_BALANCE_DR
    ,xib.init_balance_cr      INIT_BALANCE_CR
    , DECODE(INSTR(message_codes,'IB001'),0,NULL,'IB001,') ||
      DECODE(INSTR(message_codes,'IB002'),0,null,'IB002')      ERR_APPLICATION
    , DECODE(INSTR(message_codes,'IB003'),0,NULL,'IB003,') ||
      DECODE(INSTR(message_codes,'IB004'),0,null,'IB004')      ERR_LEDGER
    , DECODE(INSTR(message_codes,'IB005'),0,NULL,'IB005,') ||
      DECODE(INSTR(message_codes,'IB006'),0,NULL,'IB006,') ||
      DECODE(INSTR(message_codes,'IB007'),0,null,'IB007')      ERR_ACCOUNT
    , DECODE(INSTR(message_codes,'IB011'),0,NULL,'IB011,') ||
      DECODE(INSTR(message_codes,'IB012'),0,null,'IB012,')  ||
      DECODE(INSTR(message_codes,'IB025'),0,NULL,'IB025,')  ||
      DECODE(INSTR(message_codes,'IB026'),0,NULL,'IB026')   ||
      DECODE(INSTR(message_codes,'IB027'),0,NULL,'IB027')      ERR_PERIOD
    , DECODE(INSTR(message_codes,'IB008'),0,NULL,'IB008,') ||
      DECODE(INSTR(message_codes,'IB009'),0,NULL,'IB009,') ||
      DECODE(INSTR(message_codes,'IB010'),0,NULL,'IB010,') ||
      DECODE(INSTR(message_codes,'IB013'),0,NULL,'IB013,') ||
      DECODE(INSTR(message_codes,'IB014'),0,NULL,'IB014,') ||
      DECODE(INSTR(message_codes,'IB015'),0,NULL,'IB015,') ||   -- Bug 12721423
      DECODE(INSTR(message_codes,'IB016'),0,NULL,'IB016,') ||
      DECODE(INSTR(message_codes,'IB017'),0,NULL,'IB017,') ||
      DECODE(INSTR(message_codes,'IB018'),0,NULL,'IB018,') ||
      DECODE(INSTR(message_codes,'IB019'),0,NULL,'IB019,') ||
      DECODE(INSTR(message_codes,'IB020'),0,NULL,'IB020,') ||
      DECODE(INSTR(message_codes,'IB021'),0,NULL,'IB021,') ||
      DECODE(INSTR(message_codes,'IB022'),0,NULL,'IB022,')  ||
      DECODE(INSTR(message_codes,'IB023'),0,NULL,'IB023,')  ||
      DECODE(INSTR(message_codes,'IB024'),0,NULL,'IB024')  ERR_OTHER
FROM xla_ctrl_balances_int xib
    ,fnd_application_tl       fap
    ,gl_ledgers               gll
    ,gl_code_combinations_kfv     gcc
    ,xla_lookups              xlk
WHERE  xib.application_id = fap.application_id(+)
  AND  USERENV('LANG') = fap.language(+)
  AND  xib.ledger_id = gll.ledger_id(+)
  AND  xib.code_combination_id = gcc.code_combination_id(+)  
  AND  'XLA_PARTY_TYPE' = xlk.lookup_type(+)
  AND  xib.party_type_code = xlk.lookup_code(+)
  AND  xib.message_codes IS NOT NULL
  AND  xib.status = 'ERROR'
  AND  xib.last_update_date = &p_run_date)
ORDER BY 
APPLICATION_NAME
,LEDGER_NAME
,ACCOUNTING_CODE_COMBINATION
,PERIOD_NAME
Parameter Name SQL text Validation
Purge Option 
 
 LOV Oracle
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis (Distributions) GL Account Analysis 11g GL Account Analysis (Distributions) 11g ECC Payables, AP Closing, SQL2 XXSJ Report VAT Register AR Miscellaneous Receipts AP Accounted Invoice Aging CAC Intercompany Relationship Setup
Blitz Report™

Blitz Report™ provides multiple benefits: