SELECT
l1.meaning error_type,
'D' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
sl.line_number line_no,
sh.statement_date statement_date,
sl.trx_date trx_date,
ba.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
NULL message_name,
nvl(sl.amount,0) amount,
acr.currency_code C_CURRENCY_CODE,
l2.meaning trx_type,
acr.receipt_number trx_no,
'CE' application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(NULL) C_PRT_MESSAGE_NAME
FROM ce_lookups l1,
ce_lookups l2,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_statement_headers sh,
ce_statement_lines sl,
ar_cash_receipts_all acr,
ce_222_reconciled_v cr
WHERE l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l1.lookup_code = 'MISC_RECEIPT_CREATED'
AND l2.lookup_type = 'BANK_TRX_TYPE'
AND l2.lookup_code = sl.trx_type
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND sh.statement_header_id = sl.statement_header_id
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)
and acr.cash_receipt_id = cr.cash_receipt_id
and cr.statement_line_id = sl.statement_line_id
and acr.comments = 'Created by Auto Bank Rec'
UNION ALL
SELECT
l1.description error_type,
'C' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
sl.line_number line_no,
sh.statement_date statement_date,
sl.trx_date trx_date,
ba.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
nvl(sl.amount,0) amount,
nvl(sl.currency_code,ba.currency_code) C_CURRENCY_CODE,
l2.meaning trx_type,
sl.bank_trx_number trx_no,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM ce_lookups l1,
ce_lookups l2,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_statement_headers sh,
ce_statement_lines sl,
ce_reconciliation_errors e
WHERE l2.lookup_type = 'BANK_TRX_TYPE'
AND l2.lookup_code = sl.trx_type
AND l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l1.lookup_code='RECONCILIATION'
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification = 'INTERNAL'
AND sh.statement_header_id = sl.statement_header_id
AND sh.statement_header_id = e.statement_header_id
AND sl.statement_line_id = e.statement_line_id
AND e.statement_line_id is not null
and sl.status != 'EXTERNAL'
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)
UNION ALL
SELECT
l1.description error_type,
'C' break_group,
sh.statement_number statement_no,
sh.doc_sequence_value doc_sequence_value,
0 line_no,
sh.statement_date statement_date,
to_date(NULL) trx_date,
ba.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
to_number(NULL) amount,
ba.currency_code C_CURRENCY_CODE,
NULL trx_type,
NULL trx_no,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM ce_lookups l1,
ce_bank_accts_gt_v ba,
ce_bank_branches_v bb,
ce_statement_headers sh,
ce_reconciliation_errors e
WHERE l1.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l1.lookup_code='RECONCILIATION'
AND bb.branch_party_id = ba.bank_branch_id
AND bb.branch_party_id = :P_BANK_BRANCH_ID
AND ba.bank_account_id = sh.bank_account_id
AND ba.account_classification = 'INTERNAL'
AND sh.statement_header_id = e.statement_header_id
AND e.statement_line_id is null
AND ba.bank_account_id = DECODE(:P_BANK_ACCOUNT_ID, NULL, ba.bank_account_id, :P_BANK_ACCOUNT_ID)
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)
UNION ALL
(SELECT
l.description error_type,
'A' break_group,
e.statement_number statement_no,
to_number(NULL) doc_sequence_value,
1 line_no,
sh.statement_date statement_date,
sh.statement_date trx_date,
e.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
0 amount,
' ' C_CURRENCY_CODE,
' ' trx_type,
' ' trx_no,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM ce_lookups l,
ce_statement_headers_int sh,
ce_header_interface_errors e,
ce_bank_branches_v bb,
ce_bank_accts_gt_v ba
WHERE l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l.lookup_code='STATEMENT'
AND sh.bank_account_num = e.bank_account_num
AND sh.statement_number = e.statement_number
AND sh.bank_branch_name = bb.bank_branch_name
AND e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num)
AND ba.bank_branch_id = bb.branch_party_id
AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND ba.account_classification = 'INTERNAL'
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)
UNION
SELECT
l.description error_type,
'A' break_group,
e.statement_number statement_no,
to_number(NULL) doc_sequence_value,
1 line_no,
sh.statement_date statement_date,
sh.statement_date trx_date,
e.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
0 amount,
' ' C_CURRENCY_CODE,
' ' trx_type,
' ' trx_no,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM ce_lookups l,
ce_statement_headers sh,
ce_header_interface_errors e,
ce_bank_branches_v bb,
ce_bank_accts_gt_v ba
WHERE l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l.lookup_code='STATEMENT'
AND e.bank_account_num = ba.bank_account_num
AND e.statement_number = sh.statement_number
AND sh.bank_account_id = ba.bank_account_id
AND ba.bank_branch_id = bb.branch_party_id
AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND ba.account_classification = 'INTERNAL'
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number) )
UNION ALL
SELECT
l.description error_type,
'B' break_group,
e.statement_number statement_no,
to_number(NULL) doc_sequence_value,
e.line_number line_no,
sh.statement_date statement_date,
sl.trx_date trx_date,
e.bank_account_num bank_acc,
ba.bank_account_name bank_account_name,
ba.currency_code acc_currency,
e.message_name message_name,
nvl(sl.amount,0) amount,
NVL(curr.currency_code, ba.CURRENCY_CODE) C_CURRENCY_CODE,
' ' trx_type,
' ' trx_no,
NVL(e.application_short_name,'CE') application_short_name,
bb.branch_party_id bank_branch_id,
CE_CEXINERR_XMLP_PKG.c_prt_message_nameformula(e.message_name) C_PRT_MESSAGE_NAME
FROM fnd_currencies curr,
ce_lookups l,
ce_statement_headers_int sh,
ce_statement_lines_interface sl,
ce_line_interface_errors e,
ce_bank_branches_v bb,
ce_bank_accts_gt_v ba
WHERE curr.currency_code(+) = sl.currency_code
AND l.lookup_type = 'ABR_REPORT_EXCEPTIONS'
AND l.lookup_code='LINE'
AND sh.bank_account_num = sl.bank_account_num
AND sh.statement_number = sl.statement_number
AND sl.line_number = e.line_number
AND sl.statement_number = e.statement_number
AND sl.bank_account_num = e.bank_account_num
AND bb.bank_branch_name = sh.bank_branch_name
AND bb.branch_party_id = ba.bank_branch_id
AND e.bank_account_num = NVL(ba.bank_account_num, e.bank_account_num)
AND bb.bank_branch_name = DECODE(:C_BANK_BRANCH_NAME_DSP, :C_ALL_TRANSLATION, bb.bank_branch_name, :C_BANK_BRANCH_NAME_DSP)
AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id)
AND ba.account_classification = 'INTERNAL'
AND to_char(sh.statement_date,'YYYY/MM/DD') >= nvl(to_char(:P_STAT_DATE_FROM,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND to_char(sh.statement_date,'YYYY/MM/DD') <= nvl(to_char(:P_STAT_DATE_TO,'YYYY/MM/DD'), to_char(sh.statement_date,'YYYY/MM/DD') )
AND sh.statement_number >= nvl(:P_STAT_NUMBER_FROM,sh.statement_number)
AND sh.statement_number <= nvl(:P_STAT_NUMBER_TO,sh.statement_number)
ORDER BY 5 |
