XLA Import Initial Balance for Third Party Control Account

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