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 |