[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aba.bank_account_id,
aba.ACCOUNT_OWNER_ORG_ID,
--bau.bank_acct_use_id,
/* aba.AP_AMOUNT_TOLERANCE,
aba.AP_PERCENT_TOLERANCE,
aba.AR_AMOUNT_TOLERANCE,
aba.AR_PERCENT_TOLERANCE,
aba.CE_AMOUNT_TOLERANCE,
aba.CE_PERCENT_TOLERANCE,
nvl(bau.AP_USE_ENABLE_FLAG,'N'),
nvl(bau.AR_USE_ENABLE_FLAG,'N'),
nvl(bau.XTR_USE_ENABLE_FLAG,'N'),
nvl(bau.PAY_USE_ENABLE_FLAG,'N'),
decode(bau.org_id, -1, null, bau.org_id),
bau.legal_entity_id,*/
-1 /* for JEC - replace cash account GL CCID here*/
FROM ce_bank_accounts aba
--ce_bank_acct_uses bau
WHERE aba.bank_branch_id = p_bank_branch_id
AND aba.bank_account_id = NVL(p_bank_account_id, aba.bank_account_id)
--AND aba.bank_account_id = bau.bank_account_id
--and sysdate <= nvl(bau.end_date,sysdate)
AND aba.account_classification = 'INTERNAL'
--and aba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id,aba.ACCOUNT_OWNER_ORG_ID)
and exists (select 1 from ce_bank_acct_uses_gt_v bau
where bau.bank_account_id = aba.bank_account_id
and sysdate <= nvl(bau.end_date,sysdate)
and (bau.org_id = nvl(p_org_id, bau.org_id) or
bau.legal_entity_id = nvl(p_legal_entity_id,bau.legal_entity_id)))
order by aba.bank_account_id;
--SELECT aba.bank_account_id
--FROM ap_bank_accounts aba
--WHERE aba.bank_branch_id = p_bank_branch_id
--AND aba.bank_account_id = NVL(p_bank_account_id, aba.bank_account_id)
--AND aba.account_type = get_security_account_type(aba.account_type);
SELECT csh.statement_header_id,
csh.statement_number,
csh.statement_date,
csh.check_digits,
csh.gl_date,
aba.currency_code,
aba.multi_currency_allowed_flag,
aba.check_digits,
csh.rowid,
NVL(csh.statement_complete_flag,'N')
FROM ce_bank_accts_gt_v aba, --ce_bank_accounts_v aba,
ce_statement_headers csh
WHERE aba.bank_account_id = NVL(p_bank_account_id,aba.bank_account_id)
AND aba.bank_account_id = csh.bank_account_id
AND csh.statement_number
BETWEEN NVL(p_statement_number_from,csh.statement_number)
AND NVL(p_statement_number_to,csh.statement_number)
AND to_char(csh.statement_date,'YYYY/MM/DD')
BETWEEN NVL(to_char(p_statement_date_from,'YYYY/MM/DD'),
to_char(csh.statement_date,'YYYY/MM/DD'))
AND NVL(to_char(p_statement_date_to,'YYYY/MM/DD'),
to_char(csh.statement_date,'YYYY/MM/DD'))
AND NVL(csh.statement_complete_flag,'N') = 'N'; -- Bug 2593830 added this condition
SELECT distinct sl.rowid,
sl.statement_line_id,
--cd.receivables_trx_id,
--cd.receipt_method_id,
--cd.create_misc_trx_flag,
--cd.matching_against,
--cd.correction_method,
--rm.name,
sl.exchange_rate_type,
sl.exchange_rate_date,
sl.exchange_rate,
sl.currency_code,
sl.trx_type,
--decode(cd.PAYROLL_PAYMENT_FORMAT_ID, null, NVL(cd.reconcile_flag,'X'),
-- decode(cd.reconcile_flag,'PAY', 'PAY_EFT', NVL(cd.reconcile_flag,'X'))),
'NONE',
NULL,
NULL,
sl.original_amount,
--ppt.payment_type_name,
sl.je_status_flag, --JEC
sl.accounting_date, --JEC
--sl.accounting_event_id, --JEC
sl.cashflow_id,
DECODE(sl.trx_type, 'NSF', 5, 'REJECTED', 5,
decode(nvl(matching_against,'MISC'), 'MISC', 3, 'MS', 2, 1)) order_stmt_lns1,
decode(nvl(matching_against,'MISC'), 'MISC', 0,
to_char(sl.trx_date, 'J')) order_stmt_lns2
FROM --pay_payment_types ppt,
--ar_receipt_methods rm,
ce_statement_headers sh,
ce_transaction_codes cd,
ce_statement_lines sl
WHERE --rm.receipt_method_id(+) = cd.receipt_method_id
--nvl(cd.RECONCILIATION_SEQUENCE (+) ,1) = 1
nvl(cd.RECONCILIATION_SEQUENCE ,1) =
(select nvl(min(tc.reconciliation_sequence),1)
from ce_transaction_codes tc
where tc.bank_account_id = cd.bank_account_id
and tc.trx_code = cd.trx_code)
AND cd.trx_code = sl.trx_code
--AND cd.payroll_payment_format_id = ppt.payment_type_id (+)
AND csh_statement_date
between nvl(cd.start_date, csh_statement_date)
and nvl(cd.end_date, csh_statement_date)
AND sl.status = 'UNRECONCILED'
AND sl.statement_header_id = csh_statement_header_id
and sh.statement_header_id = sl.statement_header_id
and sh.bank_account_id = cd.bank_account_id
ORDER BY order_stmt_lns1, order_stmt_lns2 desc;
SELECT cd.receivables_trx_id,
cd.receipt_method_id,
cd.create_misc_trx_flag,
cd.matching_against,
cd.correction_method,
rm.name,
decode(cd.PAYROLL_PAYMENT_FORMAT_ID, null, NVL(cd.reconcile_flag,'X'),
decode(cd.reconcile_flag,'PAY', 'PAY_EFT', NVL(cd.reconcile_flag,'X'))),
ppt.payment_type_name
FROM pay_payment_types ppt,
ar_receipt_methods rm,
ce_transaction_codes cd,
ce_statement_lines sl
WHERE rm.receipt_method_id(+) = cd.receipt_method_id
--AND cd.transaction_code_id(+) = sl.trx_code_id
AND cd.trx_code (+) = sl.trx_code
AND cd.payroll_payment_format_id = ppt.payment_type_id (+)
AND csh_statement_date
between nvl(cd.start_date, csh_statement_date)
and nvl(cd.end_date, csh_statement_date)
AND sl.status = 'UNRECONCILED'
AND sl.statement_line_id = csl_statement_line_id
AND cd.bank_account_id = csh_bank_account_id
ORDER BY cd.RECONCILIATION_SEQUENCE, DECODE(sl.trx_type, 'NSF', 5, 'REJECTED', 5,
decode(nvl(cd.matching_against,'MISC'), 'MISC', 3, 'MS', 2, 1)),
decode(nvl(cd.matching_against,'MISC'), 'MISC', 0,
to_char(sl.trx_date, 'J')) desc;
SELECT jel.je_header_id
FROM gl_je_lines jel,
ce_statement_reconcils_all rec
WHERE jel.rowid = trx_rowid
AND rec.reference_id(+) = jel.je_line_num
AND rec.je_header_id(+) = jel.je_header_id
AND rec.reference_type(+) = 'JE_LINE'
AND NVL(rec.status_flag,X_call_mode) = x_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF jel.je_header_id NOWAIT;
SELECT c.check_id
FROM ap_checks_all c,
ce_statement_reconcils_all rec
WHERE c.rowid = trx_rowid
AND rec.reference_id(+) = c.check_id
AND rec.reference_type(+) = 'PAYMENT'
AND NVL(rec.status_flag,x_call_mode) = X_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF c.check_id NOWAIT;
SELECT c.check_id, c.status_lookup_code
FROM ap_checks_all c
WHERE c.rowid = trx_rowid
FOR UPDATE OF c.check_id NOWAIT;
SELECT crh.cash_receipt_history_id,
cr.cash_receipt_id,
NVL(crh.current_record_flag,'N')
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ce_statement_reconcils_all rec
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.rowid = trx_rowid
AND rec.reference_id(+) = crh.cash_receipt_history_id
AND rec.reference_type(+) = decode(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y', 'DM REVERSAL', 'RECEIPT')
AND NVL(rec.status_flag,X_call_mode) = X_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF crh.cash_receipt_history_id,
cr.cash_receipt_id NOWAIT;
select xtr.settlement_summary_id
FROM xtr_settlement_summary xtr,
ce_statement_reconcils_all rec
WHERE xtr.rowid = trx_rowid
AND rec.reference_id(+) = xtr.settlement_summary_id
AND rec.reference_type(+) = 'XTR_LINE'
AND NVL(rec.status_flag,X_call_mode) = x_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF xtr.settlement_summary_id NOWAIT;
SELECT crh.cash_receipt_history_id,
cr.cash_receipt_id,
NVL(crh.current_record_flag,'N')
FROM ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh
WHERE cr.cash_receipt_id = crh.cash_receipt_id
AND crh.rowid = trx_rowid
FOR UPDATE OF crh.cash_receipt_history_id,
cr.cash_receipt_id NOWAIT;
SELECT cl.statement_line_id
FROM ce_statement_lines cl,
ce_statement_reconcils_all rec
WHERE cl.rowid = trx_rowid
AND rec.reference_id(+) = cl.statement_line_id
AND rec.reference_type(+) = 'STATEMENT'
AND NVL(rec.status_flag,x_call_mode) = X_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF cl.statement_line_id NOWAIT;
SELECT cl.statement_line_id
FROM ce_statement_lines cl
WHERE cl.rowid = trx_rowid
FOR UPDATE OF cl.statement_line_id NOWAIT;
SELECT cc.cashflow_id
FROM ce_cashflows cc,
ce_statement_reconcils_all rec
WHERE cc.rowid = trx_rowid
AND rec.reference_id(+) = cc.cashflow_id
AND rec.reference_type(+) = 'CASHFLOW'
AND NVL(rec.status_flag,x_call_mode) = X_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF cc.cashflow_id NOWAIT;
SELECT cc.cashflow_id
FROM ce_cashflows cc
WHERE cc.rowid = trx_rowid
FOR UPDATE OF cc.cashflow_id NOWAIT;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
ce_statement_reconcils_all rec
WHERE paa.rowid = trx_rowid
AND rec.reference_id(+) = paa.assignment_action_id
AND rec.reference_type(+) = 'PAY'
AND NVL(rec.status_flag,x_call_mode) = X_call_mode
AND NVL(rec.current_record_flag,'Y') = 'Y'
FOR UPDATE OF paa.assignment_action_id NOWAIT;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
WHERE paa.rowid = trx_rowid
FOR UPDATE OF paa.assignment_action_id NOWAIT;
SELECT crh.cash_receipt_history_id,
cr.cash_receipt_id,
b.batch_id batch_id
FROM AR_CASH_RECEIPTS_all CR,
AR_CASH_RECEIPT_HISTORY_all CRH,
AR_CASH_RECEIPT_HISTORY_all CRH2,
AR_BATCHES_all B
WHERE b.rowid = trx_rowid
AND crh.cash_receipt_history_id = decode(crh.batch_id,
null, crh2.reversal_cash_receipt_hist_id,
crh2.cash_receipt_history_id)
AND nvl(crh.status, 'REMITTED') <> 'REVERSED'
AND crh.cash_receipt_id = crh2.cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh2.batch_id = b.batch_id
FOR UPDATE OF crh.cash_receipt_history_id,
cr.cash_receipt_id,
b.batch_id NOWAIT;
SELECT SUM(a.bank_account_amount)
FROM ce_222_txn_for_batch_v a
WHERE a.batch_id = x_batch_id
AND nvl(a.status, 'REMITTED') <> 'REVERSED';
SELECT c.check_id,
b.PAYMENT_INSTRUCTION_ID
FROM AP_CHECKS_all C,
iby_pay_instructions_all B
WHERE c.PAYMENT_INSTRUCTION_ID = b.PAYMENT_INSTRUCTION_ID AND
b.rowid = trx_rowid
AND nvl(c.status_lookup_code, 'NEGOTIABLE') <> 'VOIDED'
FOR UPDATE OF c.check_id, b.PAYMENT_INSTRUCTION_ID NOWAIT;
SELECT c.check_id,
b.checkrun_id
FROM AP_CHECKS_all C,
AP_INVOICE_SELECTION_CRITERIA B
WHERE c.checkrun_id = b.checkrun_id AND
b.rowid = trx_rowid
AND nvl(c.status_lookup_code, 'NEGOTIABLE') <> 'VOIDED'
FOR UPDATE OF c.check_id, b.checkrun_id NOWAIT;*/
SELECT SUM(bank_account_amount)
FROM CE_200_TRANSACTIONS_V
--FROM ce_available_transactions_tmp
WHERE batch_id = X_batch_id
AND nvl(status, 'NEGOTIABLE') <> 'VOIDED'
AND application_id = 200;
SELECT ACA.check_id,
b.PAYMENT_INSTRUCTION_ID
FROM AP_CHECKS_all ACA,
iby_pay_instructions_all B,
iby_payments_all IPA
WHERE ACA.PAYMENT_INSTRUCTION_ID = b.PAYMENT_INSTRUCTION_ID AND
b.rowid = trx_rowid
AND nvl(ACA.status_lookup_code, 'NEGOTIABLE') <> 'VOIDED'
AND IPA.PAYMENT_INSTRUCTION_ID = b.PAYMENT_INSTRUCTION_ID
AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
AND IPA.LOGICAL_GROUP_REFERENCE = X_LOGICAL_GROUP_REFERENCE
FOR UPDATE OF ACA.check_id, b.PAYMENT_INSTRUCTION_ID NOWAIT;
SELECT SUM(bank_account_amount)
FROM CE_200_TRANSACTIONS_V catv
WHERE batch_id = X_batch_id
AND nvl(status, 'NEGOTIABLE') <> 'VOIDED'
AND application_id = 200
AND EXISTS ( SELECT 1
FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
WHERE ACA.CHECK_ID =catv.trx_id
AND ACA.PAYMENT_INSTRUCTION_ID = X_batch_id
AND IPA.PAYMENT_INSTRUCTION_ID = X_batch_id
AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
AND IPA.LOGICAL_GROUP_REFERENCE = X_LOGICAL_GROUP_REFERENCE);
select mo_global.GET_CURRENT_ORG_ID
into current_org_id
from dual;
select org_id
into X_ORG_ID
from AR_RECEIVABLES_TRX
where RECEIVABLES_TRX_ID = x_receivables_trx_id;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_MISSING_REC_ACT_ID');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_REC_ACT_ID');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.legal_entity_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_185_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, catv.legal_entity_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND to_char(catv.trx_date,'YYYY/MM/DD') =
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.currency_code = tx_curr
AND round(catv.amount, precision) = tx_match_amount
AND catv.application_id = 185
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.legal_entity_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_999_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
--AND catv.org_id = CE_AUTO_BANK_REC.G_org_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND to_char(catv.trx_date,'YYYY/MM/DD') =
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.currency_code = tx_curr
AND round(catv.amount, precision) = tx_match_amount
AND catv.application_id = 999
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
| If the user selects the exhange rate type of 'User', they must |
| also provide the exchange rate. If the type is anything other |
| than user, the exchange rate date must be provided. |
| |
| CALLED BY |
| trx_validation |
--------------------------------------------------------------------- */
FUNCTION validate_exchange_details RETURN BOOLEAN IS
error_found BOOLEAN;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_DIFFERENT_CURRENCY');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_RATE_TYPE');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_RATE_DATE');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_INCOMPLETE_USER_RATE');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_OTHER_ERROR_RATE');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_OTHER_NO_RATE');
SELECT min(line_number)
INTO min_statement_line_num
FROM ce_statement_lines
WHERE statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
SELECT statement_line_id
INTO min_statement_line
FROM ce_statement_lines
WHERE line_number = min_statement_line_num
AND statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
cep_standard.debug('Transaction Either Deleted OR Reconciled');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.reference_type,
catv.value_date,
catv.cleared_date,
catv.deposit_date,
catv.legal_entity_id,
catv.seq_id
--FROM ce_260_cf_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id , catv.legal_entity_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.check_number = CE_AUTO_BANK_MATCH.csl_statement_line_id
AND catv.application_id = 261
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.reference_type,
catv.value_date,
catv.cleared_date,
catv.deposit_date,
catv.legal_entity_id,
catv.seq_id
--FROM ce_260_cf_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, catv.legal_entity_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
and catv.check_number is null
AND to_char(catv.trx_date,'YYYY/MM/DD') =
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.application_id = 261
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.reference_type,
catv.value_date,
catv.cleared_date,
catv.deposit_date,
catv.legal_entity_id,
catv.seq_id
--FROM ce_260_cf_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.legal_entity_id = CE_AUTO_BANK_REC.G_legal_entity_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
--AND catv.customer_id = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.bank_account_text = CE_AUTO_BANK_MATCH.csl_bank_account_text
and catv.check_number is null
AND to_char(catv.trx_date,'YYYY/MM/DD') =
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.application_id = 261
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT
NVL(s.amount_tolerance_old,0),
NVL(s.percent_tolerance_old,0),
NVL(s.fx_difference_handling_old,'C'),
s.CE_DIFFERENCES_ACCOUNT_old,
s.CASHFLOW_EXCHANGE_RATE_TYPE,
s.AUTHORIZATION_BAT,
s.BSC_EXCHANGE_DATE_TYPE,
s.BAT_EXCHANGE_DATE_TYPE,
1
FROM CE_SYSTEM_PARAMETERS s
WHERE s.legal_entity_id = CE_AUTO_BANK_MATCH.trx_legal_entity_id;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_CE_TRX_MATCH');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_TRX_MATCH');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_CE_TRX_MATCH');
SELECT count(*),
sum(catv.bank_account_amount),
nvl(sum(catv.base_amount),0),
nvl(sum(catv.amount_cleared),0),
SUM(DECODE(catv.currency_code,
CE_AUTO_BANK_MATCH.trx_currency_code,0,1)),
sum(catv.amount),
'PAY_EFT',
1,
catv.batch_id,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
INTO CE_AUTO_BANK_MATCH.trx_count,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
no_of_currencies,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_group,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
--FROM ce_801_EFT_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE upper(catv.batch_name) =
upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
AND catv.trx_date = CE_AUTO_BANK_MATCH.csl_trx_date
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(catv.status, 'C') <> 'V'
AND catv.application_id = 802
AND NVL(catv.reconciled_status_flag, 'N') = 'N'
having sum(catv.bank_account_amount) = CE_AUTO_BANK_MATCH.csl_amount
group by catv.batch_id, catv.batch_name, catv.trx_date, catv.org_id, catv.ce_bank_acct_use_id; -- bug 7242853
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_BACS');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_NACHA');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_BACS');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_NACHA');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_BATCH_BACS');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_BATCH_NACHA');
SELECT DISTINCT(c.check_id),
to_number(NULL),
c.rowid,
DECODE(c.currency_code,
sob.currency_code,c.amount,
ba.currency_code,c.amount,
NVL(c.base_amount,c.amount)),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_base_amount,
c.cleared_amount),
c.status_lookup_code,
DECODE(c.currency_code,
sob.currency_code, DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_amount),
ba.currency_code, DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_amount),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',NVL(c.cleared_base_amount, c.cleared_amount))),
'PAYMENT',
c.currency_code,
DECODE(c.currency_code,
sob.currency_code, 'FUNCTIONAL',
ba.currency_code, 'BANK',
'FOREIGN'),
c.amount,
'PAYMENT',
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_rate,
c.exchange_rate),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_date,
c.exchange_date),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_rate_type,
c.exchange_rate_type),
aph.accounting_date,
c.cleared_date,
c.org_id,
c.CE_BANK_ACCT_USE_ID
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_gl_date,
CE_AUTO_BANK_MATCH.trx_cleared_date,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
FROM gl_sets_of_books sob,
ce_system_parameters sp,
ce_statement_reconcils_all rec,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts ba,
ce_bank_acct_uses_ou_v aba2,
ce_bank_accounts ba2,
ap_payment_history_all aph,
ap_checks_all c,
ap_invoice_payments_all pay,
ap_invoices_all inv,
po_vendors ven
WHERE sob.set_of_books_id = sp.set_of_books_id
AND NVL(rec.status_flag, 'U') = 'U'
AND NVL(rec.current_record_flag,'Y') = 'Y'
AND rec.reference_type(+) = 'PAYMENT'
AND rec.reference_id(+) = c.check_id
--AND aba.bank_account_id = c.bank_account_id
AND aba.bank_acct_use_id = c.CE_BANK_ACCT_USE_ID
AND aba.bank_account_id = ba.bank_account_id
AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id --bug5182963
AND BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
AND aba.org_id = c.org_id
AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, c.org_id)
--AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND aba.AP_USE_ENABLE_FLAG = 'Y'
AND c.status_lookup_code IN
('NEGOTIABLE', 'STOP INITIATED',
DECODE(CE_AUTO_BANK_MATCH.csl_trx_type,
'STOP', 'VOIDED',
'NEGOTIABLE'),
DECODE(sp.show_cleared_flag,
'N','NEGOTIABLE',
'CLEARED'),
DECODE(sp.show_cleared_flag,
'N','NEGOTIABLE',
'CLEARED BUT UNACCOUNTED'))
AND c.check_date >= sp.cashbook_begin_date
--and c.org_id = sp.org_id
and c.org_id = rec.org_id
AND c.check_id = pay.check_id
AND c.org_id = pay.org_id
AND pay.invoice_id = inv.invoice_id
AND pay.org_id = inv.org_id
AND ven.vendor_id = inv.vendor_id
AND inv.invoice_num = CE_AUTO_BANK_MATCH.csl_invoice_text
AND aba2.bank_account_id = ba2.bank_account_id
AND aba2.AP_USE_ENABLE_FLAG = 'Y'
AND aba2.bank_acct_use_id = c.external_bank_account_id --c.external_bank_acct_use_id
AND ba2.bank_account_num = CE_AUTO_BANK_MATCH.csl_bank_account_text
AND aph.check_id (+) = c.check_id
AND aph.org_id (+) = c.org_id
AND aph.transaction_type (+) = 'PAYMENT CLEARING'
AND not exists
(select null
from ap_payment_history_all aph2
where aph2.check_id = c.check_id
and aph2.org_id = c.org_id
and aph2.transaction_type = 'PAYMENT CLEARING'
and aph2.payment_history_id > aph.payment_history_id);
SELECT distinct(crh.cash_receipt_history_id),
crh.cash_receipt_id,
crh.rowid,
DECODE(cr.currency_code,
CE_AUTO_BANK_REC.G_functional_currency, crh.amount,
CE_AUTO_BANK_MATCH.aba_bank_currency, crh.amount,
NVL(crh.acctd_amount,crh.amount)),
crh.acctd_amount,
crh.status,
DECODE(crh.status,
'CLEARED', crh.amount,
'RISK_ELIMINATED', crh.amount,
0),
cr.type,
cr.currency_code,
DECODE(cr.currency_code,
sob.currency_code, 'FUNCTIONAL',
ba.currency_code, 'BANK',
'FOREIGN'),
crh.amount,
cr.type,
crh.exchange_rate,
crh.exchange_date,
crh.exchange_rate_type,
crh.org_id,
cr.remit_bank_acct_use_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
FROM gl_sets_of_books sob,
ce_system_parameters sp,
ce_statement_reconcils_all rec,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts ba,
ce_bank_acct_uses_ou_v aba2,
ce_bank_accounts ba2,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr,
ar_receivable_applications_all ra,
ar_payment_schedules_all ps
WHERE sob.set_of_books_id = sp.set_of_books_id
AND nvl(rec.status_flag, 'U') = 'U'
AND nvl(rec.current_record_flag,'Y') = 'Y'
AND nvl(rec.reference_type, 'RECEIPT') IN ('RECEIPT', 'DM REVERSAL')
AND rec.reference_id(+) = crh.cash_receipt_history_id
AND crh.status IN (
DECODE(CE_AUTO_BANK_MATCH.csl_trx_type,
'CREDIT', 'REMITTED',
'REVERSED'),
DECODE(sp.show_cleared_flag,
'N','REMITTED',
'CLEARED'),
'REMITTED',
'RISK_ELIMINATED')
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
and crh.org_id = cr.org_id
and crh.org_id = rec.org_id
--AND aba.bank_account_id = cr.REMIT_BANK_ACCT_USE_ID
--AND cr.remittance_bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND aba.bank_acct_use_id = cr.remittance_bank_account_id
--AND cr.remit_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND aba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND aba.org_id = cr.org_id
AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
--AND aba.org_id = sp.org_id
and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
AND aba.bank_account_id = ba.bank_account_id
AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND aba.AR_USE_ENABLE_FLAG = 'Y'
AND crh.trx_date >= sp.cashbook_begin_date
AND cr.cash_receipt_id = ra.cash_receipt_id
and cr.org_id = ra.org_id
AND ra.display = 'Y'
AND ra.status = 'APP'
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
and ra.org_id = ps.org_id
AND ps.trx_number = CE_AUTO_BANK_MATCH.csl_invoice_text
--AND aba2.bank_account_id = cr.customer_bank_account_id
AND aba2.bank_acct_use_id = cr.customer_bank_account_id --cr.customer_bank_acct_use_id
AND aba2.org_id = cr.org_id
AND aba2.bank_account_id = ba2.bank_account_id
AND ba2.bank_account_num = CE_AUTO_BANK_MATCH.csl_bank_account_text;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BAP');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BAR');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
SELECT DISTINCT(c.check_id),
to_number(NULL),
c.rowid,
DECODE(c.currency_code,
sob.currency_code,c.amount,
ba.currency_code,c.amount,
NVL(c.base_amount,c.amount)),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_base_amount,
c.cleared_amount),
c.status_lookup_code,
DECODE(c.currency_code,
sob.currency_code, DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_amount),
ba.currency_code, DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_amount),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',NVL(c.cleared_base_amount, c.cleared_amount))),
'PAYMENT',
c.currency_code,
DECODE(c.currency_code,
sob.currency_code, 'FUNCTIONAL',
ba.currency_code, 'BANK',
'FOREIGN'),
c.amount,
'PAYMENT',
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_rate,
c.exchange_rate),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_date,
c.exchange_date),
DECODE(DECODE(c.status_lookup_code,
'CLEARED BUT UNACCOUNTED','CLEARED',
c.status_lookup_code),
'CLEARED',c.cleared_exchange_rate_type,
c.exchange_rate_type),
aph.accounting_date,
c.cleared_date,
c.org_id,
c.CE_BANK_ACCT_USE_ID
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_gl_date,
CE_AUTO_BANK_MATCH.trx_cleared_date,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
FROM gl_sets_of_books sob,
ce_system_parameters sp,
ce_statement_reconcils_all rec,
ce_bank_accounts ba,
ce_bank_acct_uses_ou_v aba,
ap_payment_history_all aph,
ap_checks_all c,
ap_invoice_payments_all pay,
ap_invoices_all inv,
po_vendors ven
WHERE sob.set_of_books_id = sp.set_of_books_id
AND NVL(rec.status_flag, 'U') = 'U'
AND NVL(rec.current_record_flag,'Y') = 'Y'
AND rec.reference_type(+) = 'PAYMENT'
AND rec.reference_id(+) = c.check_id
--AND aba.bank_account_id = c.bank_account_id
AND aba.bank_acct_use_id = c.CE_BANK_ACCT_USE_ID
AND aba.bank_account_id = ba.bank_account_id
AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id --bug5182963
and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
AND aba.org_id = c.org_id
AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
--AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND c.status_lookup_code IN
('NEGOTIABLE', 'STOP INITIATED',
DECODE(CE_AUTO_BANK_MATCH.csl_trx_type,
'STOP', 'VOIDED',
'NEGOTIABLE'),
DECODE(sp.show_cleared_flag,
'N','NEGOTIABLE',
'CLEARED'),
DECODE(sp.show_cleared_flag,
'N','NEGOTIABLE',
'CLEARED BUT UNACCOUNTED'))
AND c.check_date >= sp.cashbook_begin_date
AND c.check_id = pay.check_id
AND c.org_id = pay.org_id
AND pay.invoice_id = inv.invoice_id
AND inv.invoice_num = CE_AUTO_BANK_MATCH.csl_invoice_text
AND inv.vendor_id = ven.vendor_id
AND ven.vendor_name = NVL(CE_AUTO_BANK_MATCH.csl_customer_text, ven.vendor_name)
AND aph.check_id (+) = c.check_id
AND aph.org_id (+) = c.org_id
AND aph.transaction_type (+) = 'PAYMENT CLEARING'
AND not exists
(select null
from ap_payment_history aph2
where aph2.check_id = c.check_id
and aph2.org_id = c.org_id
and aph2.transaction_type = 'PAYMENT CLEARING'
and aph2.payment_history_id > aph.payment_history_id);
SELECT distinct(crh.cash_receipt_history_id),
crh.cash_receipt_id,
crh.rowid,
DECODE(cr.currency_code,
CE_AUTO_BANK_REC.G_functional_currency, crh.amount,
CE_AUTO_BANK_MATCH.aba_bank_currency, crh.amount,
NVL(crh.acctd_amount,crh.amount)),
crh.acctd_amount,
crh.status,
DECODE( crh.status,
'CLEARED', crh.amount,
'RISK_ELIMINATED', crh.amount,
0),
cr.type,
cr.currency_code,
DECODE(cr.currency_code,
sob.currency_code, 'FUNCTIONAL',
ba.currency_code, 'BANK',
'FOREIGN'),
crh.amount,
cr.type,
crh.exchange_rate,
crh.exchange_date,
crh.exchange_rate_type,
crh.org_id,
cr.remit_bank_acct_use_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
FROM gl_sets_of_books sob,
ce_system_parameters sp,
ce_statement_reconcils_all rec,
ce_bank_accounts ba,
ce_bank_acct_uses_ou_v aba,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr,
ar_receivable_applications_all ra,
--ra_customers rc,
HZ_CUST_ACCOUNTS CU,
hz_parties hp,
ar_payment_schedules_all ps
WHERE sob.set_of_books_id = sp.set_of_books_id
AND nvl(rec.status_flag, 'U') = 'U'
AND nvl(rec.current_record_flag,'Y') = 'Y'
AND nvl(rec.reference_type, 'RECEIPT') IN ('RECEIPT', 'DM REVERSAL')
AND rec.reference_id(+) = crh.cash_receipt_history_id
AND crh.status IN ('REMITTED',
DECODE(sp.show_cleared_flag,
'N','REMITTED',
'CLEARED'),
decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'NSF', 'REVERSED',
'REJECTED', 'REVERSED',
'REMITTED'),
'RISK_ELIMINATED')
AND crh.current_record_flag = 'Y'
AND crh.cash_receipt_id = cr.cash_receipt_id
and crh.org_id = cr.org_id
and crh.org_id = rec.org_id
--AND aba.bank_account_id = cr.remittance_bank_account_id
--AND cr.remittance_bank_account_id =CE_AUTO_BANK_MATCH.csh_bank_account_id
AND aba.bank_acct_use_id = cr.remit_bank_acct_use_id
AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
--AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND aba.org_id = cr.org_id
--AND aba.org_id = sp.org_id
AND aba.bank_account_id = ba.bank_account_id
and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
AND crh.status = decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'NSF', decode(CE_AUTO_BANK_REC.G_nsf_handling,
'REVERSE',crh.status,
'REVERSED'),
'REJECTED', decode('REVERSE',
'REVERSE', crh.status,
'REVERSED'),
crh.status)
AND crh.trx_date >= sp.cashbook_begin_date
AND cr.cash_receipt_id = ra.cash_receipt_id
and cr.org_id = ra.org_id
-- AND ra.display = 'Y'
AND ra.status = 'APP'
AND ra.applied_payment_schedule_id = ps.payment_schedule_id
and ra.org_id = ps.org_id
--AND rc.customer_name = CE_AUTO_BANK_MATCH.csl_customer_text
--AND rc.customer_id = nvl(ps.customer_id,rc.customer_id)
AND CU.CUST_ACCOUNT_ID = CR.PAY_FROM_CUSTOMER
AND HP.PARTY_ID = CU.PARTY_ID
AND hp.party_name = CE_AUTO_BANK_MATCH.csl_customer_text
AND hp.party_id = nvl(ps.customer_id,hp.party_id)
AND ps.trx_number = CE_AUTO_BANK_MATCH.csl_invoice_text;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_INP');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_INR');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
SELECT
ab.batch_id,
ab.row_id,
1,
ab.trx_currency_type,
ab.currency_code,
'PBATCH',
ab.exchange_rate,
ab.exchange_rate_date,
ab.exchange_rate_type,
ab.org_id,
ab.legal_entity_id,
ab.CE_BANK_ACCT_USE_ID,
'PAYMENT' -- bug 7571492
INTO
CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_rowid,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type -- bug 7571492
FROM CE_200_BATCHES_V ab
WHERE
UPPER(ab.trx_number) = UPPER(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
--AND ab.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id --bug 4435028 ignore MO security for IBY batches
AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
OR ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
SELECT count(*),
NVL(sum(catv.bank_account_amount),0),
nvl(sum(catv.base_amount),0),
nvl(sum(catv.amount_cleared),0),
NVL(SUM(DECODE(catv.currency_code,
CE_AUTO_BANK_MATCH.trx_currency_code,0,1)),0),
NVL(sum(catv.amount),0),
'PBATCH'
INTO trx_count_ap,
trx_amount_ap,
trx_base_amount_ap,
trx_cleared_amount_ap,
no_of_currencies_ap,
trx_curr_amount_ap,
CE_AUTO_BANK_MATCH.csl_match_type
--FROM ce_200_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.batch_id = CE_AUTO_BANK_MATCH.trx_id
AND nvl(catv.status, 'NEGOTIABLE') <> 'VOIDED'
AND catv.application_id = 200
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT count(*),
NVL(sum(catv.bank_account_amount),0),
nvl(sum(catv.base_amount),0),
nvl(sum(catv.amount_cleared),0),
NVL(SUM(DECODE(catv.currency_code,
CE_AUTO_BANK_MATCH.trx_currency_code,0,1)),0),
NVL(sum(catv.amount),0),
'PBATCH'
INTO trx_count_ce,
trx_amount_ce,
trx_base_amount_ce,
trx_cleared_amount_ce,
no_of_currencies_ce,
trx_curr_amount_ce,
CE_AUTO_BANK_MATCH.csl_match_type
--FROM ce_260_cf_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.batch_id = CE_AUTO_BANK_MATCH.trx_id
AND nvl(catv.status, 'CANCELED') <> 'CANCELED'
AND catv.application_id = 261
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT ab.batch_id,
ab.row_id,
1,
ab.trx_currency_type,
ab.currency_code,
'RBATCH',
ab.exchange_rate,
ab.exchange_rate_date,
ab.exchange_rate_type,
ab.org_id,
ab.CE_BANK_ACCT_USE_ID
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_rowid,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
FROM CE_222_BATCHES_V ab
WHERE ab.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
--AND ab.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
SELECT count(*),
sum(catv.bank_account_amount),
sum(catv.base_amount),
nvl(sum(catv.amount_cleared),0),
SUM(DECODE(catv.currency_code,
CE_AUTO_BANK_MATCH.trx_currency_code,0,1)),
SUM(catv.amount),
'RBATCH'
INTO trx_count,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
no_of_currencies,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_match_type
FROM ce_222_txn_for_batch_v catv
WHERE catv.batch_id = CE_AUTO_BANK_MATCH.trx_id
AND nvl(catv.status, 'REMITTED') <> 'REVERSED';
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_P');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_R');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_P');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_R');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APB_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ARB_PARTIAL');
SELECT
ab.batch_id,
ab.row_id,
1,
ab.trx_currency_type,
ab.currency_code,
'PGROUP', -- 7571492 : Changed to PGROUP
ab.exchange_rate,
ab.exchange_rate_date,
ab.exchange_rate_type,
ab.org_id,
ab.legal_entity_id,
ab.CE_BANK_ACCT_USE_ID,
ab.logical_group_reference,
'PAYMENT' -- bug 7571492
INTO
CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_rowid,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.csl_match_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.logical_group_reference,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type -- bug 7571492
FROM CE_200_GROUPS_V ab
WHERE upper(ab.logical_group_reference) = upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
or
ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
SELECT
count(*),
sum(catv.bank_account_amount),
nvl(sum(catv.base_amount),0),
nvl(sum(catv.amount_cleared),0),
SUM(DECODE(catv.currency_code, CE_AUTO_BANK_MATCH.trx_currency_code,0,1)),
sum(catv.amount),
'PGROUP'
INTO
trx_count,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
no_of_currencies,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_match_type
FROM ce_available_transactions_tmp catv
WHERE catv.batch_id = CE_AUTO_BANK_MATCH.trx_id
AND nvl(catv.status, 'NEGOTIABLE') <> 'VOIDED'
AND catv.application_id = 200
AND NVL(catv.reconciled_status_flag, 'N') = 'N'
AND EXISTS ( SELECT 1
FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
WHERE ACA.CHECK_ID =catv.trx_id
AND ACA.PAYMENT_INSTRUCTION_ID = CE_AUTO_BANK_MATCH.trx_id
AND IPA.PAYMENT_INSTRUCTION_ID = CE_AUTO_BANK_MATCH.trx_id
AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
AND IPA.LOGICAL_GROUP_REFERENCE = NVL(CE_AUTO_BANK_MATCH.LOGICAL_GROUP_REFERENCE,IPA.LOGICAL_GROUP_REFERENCE));
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_FOREIGN_MISC');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_REQUIRED_EXCHANGE_FIELD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PAYMENT_METHOD');
select mo_global.GET_CURRENT_ORG_ID
into current_org_id
from dual;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PBATCH_CURRENCY');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_PMT_AMOUNT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_PMT_AMOUNT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_CHARGES_AMOUNT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_XCH_AMOUNT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PAYMENT_METHOD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_CANNOT_RECONCILE_FD_PAYMENT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_PMT_AMOUNT');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_INVALID_AP_PERIOD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_INVALID_AR_PERIOD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_TRX_RECONCILED');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_TRX_BUSY');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
'PAY_LINE',
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_801_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.bank_account_amount = CE_AUTO_BANK_MATCH.csl_amount
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.application_id = 801
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
'JE_LINE',
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_101_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.application_id = 101
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.legal_entity_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_185_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,CE_AUTO_BANK_REC.G_legal_entity_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.application_id = 185
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.legal_entity_id,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_999_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_type = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,CE_AUTO_BANK_REC.G_legal_entity_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.application_id = 999
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_REQUIRED_EXCHANGE_FIELD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_REQUIRED_EXCHANGE_FIELD');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.trx_type,
to_number(NULL),
to_date(NULL),
NULL,
catv.customer_id,
'N', -- reversed receipt flag
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
-1
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_customer_id,
CE_AUTO_BANK_MATCH.reversed_receipt_flag,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
FROM ce_222_reversal_v catv
WHERE DECODE(tx_type,'CASH',
DECODE(catv.trx_type,'MISC',
'CASH',
catv.trx_type),
catv.trx_type) = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.status = decode(CE_AUTO_BANK_REC.G_nsf_handling,
'REVERSE',catv.status,
'DM REVERSE',catv.status, 'REVERSED')
UNION
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.trx_type,
to_number(NULL),
to_date(NULL),
NULL,
catv.customer_id,
NVL(catv.reversed_receipt_flag, 'N'),
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
--FROM ce_222_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE DECODE(tx_type,'CASH',
DECODE(catv.trx_type,'MISC',
'CASH',
catv.trx_type),
catv.trx_type) = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.status = 'REVERSED'
AND catv.application_id = 222
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
NVL(catv.reversed_receipt_flag, 'N'),
catv.gl_date,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.reversed_receipt_flag,
CE_AUTO_BANK_MATCH.trx_gl_date ,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_222_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE DECODE(tx_type,'CASH',
DECODE(catv.trx_type,'MISC',
'CASH',
catv.trx_type),
catv.trx_type) = tx_type
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND catv.status <> 'REVERSED'
AND catv.application_id = 222
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
'PAYMENT', /* catv.trx_type, */
1,
catv.trx_currency_type,
catv.amount,
'PAYMENT', /* catv.clearing_trx_type, */
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.gl_date,
catv.cleared_date,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_gl_date,
CE_AUTO_BANK_MATCH.trx_cleared_date,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_200_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.check_number =
to_number(LTRIM(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '0'))
AND catv.application_id = 200
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
'PAYMENT', /* catv.trx_type, */
1,
catv.trx_currency_type,
catv.amount,
'PAYMENT', /* catv.clearing_trx_type, */
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.gl_date,
catv.cleared_date,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_gl_date,
CE_AUTO_BANK_MATCH.trx_cleared_date,
CE_AUTO_BANK_MATCH.trx_org_id,
CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_200_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
AND catv.trx_number =
LTRIM(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '0')
AND catv.application_id = 200
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_ARL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_NSF');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_STATEMENT_REVERSAL_NSF');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_ARL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_ARL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_NSF');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_PAYL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_JEL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_OIL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_APL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_JEL_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_OIL_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_PAYL_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.seq_id
--FROM ce_260_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.trx_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(catv.trx_number, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-99999')
AND (nvl(catv.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
AND (nvl(catv.bank_account_text,'-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(catv.customer_text,'-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND catv.trx_type in (
decode(csl_trx_type,
'MISC_DEBIT', 'CREDIT',
'DEBIT'),
decode(csl_trx_type,
'MISC_DEBIT', 'MISC_CREDIT',
'MISC_DEBIT'))
AND catv.bank_account_amount = CE_AUTO_BANK_MATCH.csl_amount
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.application_id = 260
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.trx_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.seq_id,
v.trx_id,
v.cash_receipt_id,
v.row_id,
v.trx_date,
v.currency_code,
v.bank_account_amount,
v.base_amount,
v.status,
nvl(v.amount_cleared,0),
v.trx_type,
v.trx_currency_type,
v.amount,
v.clearing_trx_type,
v.exchange_rate,
v.exchange_rate_date,
v.exchange_rate_type,
v.gl_date,
v.cleared_date,
v.org_id,
v.CE_BANK_ACCT_USE_ID,
v.seq_id
--FROM ce_222_transactions_v v, ce_260_transactions_v catv
FROM ce_available_transactions_tmp v, ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(catv.trx_number, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND (nvl(catv.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
and (nvl(catv.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')
or nvl(catv.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')))
AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.trx_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND catv.trx_type in ('MISC_DEBIT', 'MISC_CREDIT')
AND v.trx_type = 'MISC'
AND v.bank_account_id = catv.bank_account_id
--AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v.org_id)
AND v.trx_number = nvl(catv.trx_number,v.trx_number)
AND to_char(v.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
AND v.bank_account_amount
between (CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount)
- decode(catv.trx_currency_type,
'BANK', tolerance_amount_ar,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
and (CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount)
+ decode(catv.trx_currency_type,
'BANK', tolerance_amount_ar,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
AND v.bank_account_amount
between ((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount))
- abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
and ((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount))
+ abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,
'MISC_CREDIT', catv.amount,
- catv.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
AND v.application_id = 222
AND NVL(v.reconciled_status_flag, 'N') = 'N'
AND catv.application_id = 260
AND NVL(catv.reconciled_status_flag, 'N') = 'N'
UNION
SELECT catv.trx_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.seq_id,
v.trx_id,
v.cash_receipt_id,
v.row_id,
v.trx_date,
v.currency_code,
v.bank_account_amount,
v.base_amount,
v.status,
nvl(v.amount_cleared,0),
v.trx_type,
v.trx_currency_type,
v.amount,
v.clearing_trx_type,
v.exchange_rate,
v.exchange_rate_date,
v.exchange_rate_type,
v.gl_date,
v.cleared_date,
v.org_id,
v.CE_BANK_ACCT_USE_ID,
v.seq_id
--FROM ce_222_transactions_v v, ce_260_transactions_v catv
FROM ce_available_transactions_tmp v, ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(catv.trx_number, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND (nvl(catv.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
and (nvl(catv.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')
or nvl(catv.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')))
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.trx_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND catv.trx_type in ('DEBIT', 'CREDIT')
AND v.trx_type = 'CASH'
AND v.bank_account_id = catv.bank_account_id
--AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, v.org_id)
AND v.trx_number = nvl(catv.trx_number,v.trx_number)
AND to_char(v.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
AND CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type,'DEBIT',-catv.amount, catv.amount) > 0
AND v.bank_account_amount
between (CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount)
- decode(catv.trx_currency_type, 'BANK', tolerance_amount_ar,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
and (CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount)
+ decode(catv.trx_currency_type, 'BANK', tolerance_amount_ar,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
AND v.bank_account_amount
between ((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount))
- abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
and ((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount))
+ abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'CREDIT', catv.amount, - catv.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance/ 100))
AND v.application_id = 222
AND NVL(v.reconciled_status_flag, 'N') = 'N'
AND catv.application_id = 260
AND NVL(catv.reconciled_status_flag, 'N') = 'N'
UNION
SELECT catv.trx_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.seq_id,
v2.trx_id,
v2.cash_receipt_id,
v2.row_id,
v2.trx_date,
v2.currency_code,
v2.bank_account_amount,
v2.base_amount,
v2.status,
nvl(v2.amount_cleared,0),
'PAYMENT', /* v2.trx_type, */
v2.trx_currency_type,
v2.amount,
'PAYMENT', /* v2.clearing_trx_type, */
v2.exchange_rate,
v2.exchange_rate_date,
v2.exchange_rate_type,
v2.gl_date,
v2.cleared_date,
v2.org_id,
v2.CE_BANK_ACCT_USE_ID,
v2.seq_id
--FROM ce_200_transactions_v v2, ce_260_transactions_v catv
FROM ce_available_transactions_tmp v2, ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(catv.trx_number, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND (nvl(catv.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
and (nvl(catv.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')
or nvl(catv.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')))
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.trx_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND catv.trx_type in ('DEBIT', 'CREDIT')
AND v2.bank_account_id = catv.bank_account_id
AND v2.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, v2.org_id)
--AND v2.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND v2.trx_number = nvl(catv.trx_number,v2.trx_number)
AND to_char(v2.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', - catv.amount, catv.amount) < 0
AND v2.bank_account_amount
between (- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount)
- decode(catv.trx_currency_type, 'BANK', tolerance_amount_ap,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
and (- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount)
+ decode(catv.trx_currency_type, 'BANK', tolerance_amount_ap,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
AND v2.bank_account_amount
between ((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount))
- abs((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount))
* CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
and ((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount))
+ abs((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(catv.trx_type, 'DEBIT', catv.amount, - catv.amount))
* CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
AND v2.application_id = 200
AND NVL(v2.reconciled_status_flag, 'N') = 'N'
AND catv.application_id = 260
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
SELECT catv.statement_line_id,
catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
FROM ce_200_reconciled_v catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
AND nvl(catv.trx_number,'-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND (nvl(catv.invoice_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
and (nvl(catv.customer_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')
or
nvl(catv.bank_account_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')))
AND catv.bank_account_amount = CE_AUTO_BANK_MATCH.csl_amount
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.request_id = nvl(FND_GLOBAL.conc_request_id,-1)
AND NOT EXISTS
(select NULL
from ce_statement_reconcils_all r
where r.statement_line_id = catv.statement_line_id
and r.current_record_flag = 'Y'
and nvl(r.status_flag, 'U') <> 'U'
AND r.reference_type = 'STATEMENT');
SELECT catv.statement_line_id,
catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
FROM ce_222_reconciled_v catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
AND nvl(catv.trx_number,'-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND (nvl(catv.invoice_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
and (nvl(catv.customer_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')
or
nvl(catv.bank_account_text, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')))
AND catv.bank_account_amount = decode(csl_trx_type,
'MISC_CREDIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.request_id = nvl(FND_GLOBAL.conc_request_id,-1)
AND NOT EXISTS
(select NULL
from ce_statement_reconcils_all r
where r.statement_line_id = catv.statement_line_id
and r.current_record_flag = 'Y'
and nvl(r.status_flag, 'U') <> 'U'
AND r.reference_type = 'STATEMENT');
SELECT sl.statement_line_id,
'RECEIPT',
sl.trx_type,
sl.rowid,
sl.amount,
catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
FROM ce_222_reconciled_v catv, ce_statement_lines sl
WHERE nvl(sl.bank_trx_number,'-9999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-9999')
AND (nvl(sl.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text, '-99999')
and (nvl(sl.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(sl.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND sl.statement_line_id = catv.statement_line_id
AND sl.trx_type in ('MISC_DEBIT', 'MISC_CREDIT')
AND catv.trx_type = 'MISC'
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
AND nvl(catv.trx_number, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.request_id = nvl(FND_GLOBAL.conc_request_id,-1)
AND catv.bank_account_amount
between (decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount) +
decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)
- decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
and (decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount) +
decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)
+ decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
AND catv.bank_account_amount
between ((decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount) +
decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount))
- abs((decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount) +
decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
and ((decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount))
+ abs((decode(sl.trx_type,
'MISC_CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
AND NOT EXISTS
(select NULL
from ce_statement_reconcils_all r
where r.statement_line_id = catv.statement_line_id
and r.current_record_flag = 'Y'
and nvl(r.status_flag, 'U') <> 'U'
AND r.reference_type = 'STATEMENT');
SELECT sl.statement_line_id,
'RECEIPT',
sl.trx_type,
sl.rowid,
sl.amount,
catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
FROM ce_222_reconciled_v catv, ce_statement_lines sl
WHERE nvl(sl.bank_trx_number,'-9999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-9999')
AND (nvl(sl.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text, '-99999')
and (nvl(sl.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(sl.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND sl.statement_line_id = catv.statement_line_id
AND sl.trx_type in ('DEBIT', 'CREDIT')
AND catv.trx_type = 'CASH'
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
AND nvl(catv.trx_number, '-99999')
= nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-99999')
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.request_id = nvl(FND_GLOBAL.conc_request_id,-1)
AND catv.bank_account_amount
between (decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)
- decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
and (decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)
+ decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
AND catv.bank_account_amount
between ((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount))
- abs((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
and ((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount))
+ abs((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', - CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
AND NOT EXISTS
(select NULL
from ce_statement_reconcils_all r
where r.statement_line_id = catv.statement_line_id
and r.current_record_flag = 'Y'
and nvl(r.status_flag, 'U') <> 'U'
AND r.reference_type = 'STATEMENT');
SELECT catv.statement_line_id,
'PAYMENT',
sl.trx_type,
sl.rowid,
sl.amount,
catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.status,
nvl(catv.amount_cleared,0),
catv.trx_type,
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.org_id,
catv.CE_BANK_ACCT_USE_ID
FROM ce_200_reconciled_v catv, ce_statement_lines sl
WHERE nvl(sl.bank_trx_number,'-9999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-9999')
AND (nvl(sl.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text, '-99999')
and (nvl(sl.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(sl.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND sl.statement_line_id = catv.statement_line_id
AND sl.trx_type in ('DEBIT', 'CREDIT')
AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
AND nvl(catv.trx_number,'-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
AND to_char(catv.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND catv.request_id = nvl(FND_GLOBAL.conc_request_id,-1)
AND catv.bank_account_amount
between (decode(sl.trx_type,
'DEBIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount)
- decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
and (decode(sl.trx_type,
'DEBIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount)
+ decode(catv.trx_currency_type,
'BANK', tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
AND catv.bank_account_amount
between ((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount))
- abs((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
and ((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount))
+ abs((decode(sl.trx_type,
'CREDIT', sl.amount,
- sl.amount)
+ decode(CE_AUTO_BANK_MATCH.csl_trx_type,
'MISC_DEBIT', CE_AUTO_BANK_MATCH.csl_amount,
- CE_AUTO_BANK_MATCH.csl_amount)) *
CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
AND NOT EXISTS
(select NULL
from ce_statement_reconcils_all r
where r.statement_line_id = catv.statement_line_id
and r.current_record_flag = 'Y'
and nvl(r.status_flag, 'U') <> 'U'
AND r.reference_type = 'STATEMENT');
SELECT l.statement_line_id,
l.rowid,
l.trx_date,
l.currency_code,
decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
l.amount, CE_AUTO_BANK_MATCH.aba_bank_currency, l.amount,
nvl(l.original_amount, l.amount)),
l.original_amount,
l.status,
0,
l.trx_type,
1,
decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
'FUNCTIONAL', CE_AUTO_BANK_MATCH.aba_bank_currency, 'BANK',
'FOREIGN'),
l.amount,
l.trx_type,
l.exchange_rate,
l.exchange_rate_date,
glcc.user_conversion_type,
v.trx_id,
v.cash_receipt_id,
v.row_id,
v.trx_date,
v.currency_code,
v.bank_account_amount,
v.base_amount,
v.status,
nvl(v.amount_cleared,0),
v.trx_type,
v.trx_currency_type,
v.amount,
v.clearing_trx_type,
v.exchange_rate,
v.exchange_rate_date,
v.exchange_rate_type,
'RECEIPT',
r.reference_id,
ar.cash_receipt_id,
ar.trx_date,
v.org_id,
v.CE_BANK_ACCT_USE_ID,
v.seq_id
--FROM ce_222_transactions_v v, gl_daily_conversion_types glcc,
FROM ce_available_transactions_tmp v, gl_daily_conversion_types glcc,
ar_cash_receipt_history_all ar, ce_statement_headers h,
ce_statement_reconcils_all r, ce_statement_lines l
WHERE h.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(l.bank_trx_number,'-9999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-9999')
AND (nvl(l.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text, '-99999')
and (nvl(l.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(l.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND to_char(l.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND l.status = 'RECONCILED'
AND l.statement_line_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND l.trx_type in ('DEBIT', 'CREDIT')
AND l.statement_header_id = h.statement_header_id
AND r.statement_line_id = l.statement_line_id
AND r.org_id = v.org_id
AND nvl(r.current_record_flag, 'Y') = 'Y'
AND nvl(r.status_flag, 'U') <> 'U'
AND glcc.conversion_type = l.exchange_rate_type
AND ar.cash_receipt_history_id = r.reference_id
AND ar.org_id = r.org_id
AND v.trx_type = 'CASH'
AND v.bank_account_id = h.bank_account_id
--AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v.org_id)
AND v.trx_number = nvl(l.bank_trx_number,v.trx_number)
AND to_char(v.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
AND CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'DEBIT', - l.amount, l.amount) > 0
AND v.bank_account_amount
between (CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type,
'CREDIT', l.amount,
- l.amount)
- decode(l.currency_code,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
CE_AUTO_BANK_MATCH.aba_bank_currency,
tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
and (CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type,
'CREDIT', l.amount,
- l.amount)
+ decode(l.currency_code,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
CE_AUTO_BANK_MATCH.aba_bank_currency,
tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
AND v.bank_account_amount
between ((CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'CREDIT', l.amount, - l.amount))
- abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'CREDIT', l.amount, - l.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
and ((CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'CREDIT', l.amount, - l.amount))
+ abs((CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'CREDIT', l.amount, - l.amount))
* CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance / 100))
AND v.application_id = 222
AND NVL(v.reconciled_status_flag, 'N') = 'N';
SELECT l.statement_line_id,
l.rowid,
l.trx_date,
l.currency_code,
decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
l.amount, CE_AUTO_BANK_MATCH.aba_bank_currency, l.amount,
nvl(l.original_amount, l.amount)),
l.original_amount,
l.status,
0,
l.trx_type,
1,
decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
'FUNCTIONAL', CE_AUTO_BANK_MATCH.aba_bank_currency, 'BANK',
'FOREIGN'),
l.amount,
l.trx_type,
l.exchange_rate,
l.exchange_rate_date,
glcc.user_conversion_type,
v2.trx_id,
v2.cash_receipt_id,
v2.row_id,
v2.trx_date,
v2.currency_code,
v2.bank_account_amount,
v2.base_amount,
v2.status,
nvl(v2.amount_cleared,0),
'PAYMENT', /* v2.trx_type, */
v2.trx_currency_type,
v2.amount,
'PAYMENT', /* v2.clearing_trx_type, */
v2.exchange_rate,
v2.exchange_rate_date,
v2.exchange_rate_type,
'PAYMENT',
r.reference_id,
to_number(NULL),
to_date(NULL),
v2.org_id,
v2.CE_BANK_ACCT_USE_ID,
v2.seq_id
--FROM ce_200_transactions_v v2, gl_daily_conversion_types glcc,
FROM ce_available_transactions_tmp v2, gl_daily_conversion_types glcc,
ce_statement_headers h,
ce_statement_reconcils_all r, ce_statement_lines l
WHERE h.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND nvl(l.bank_trx_number,'-9999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-9999')
AND (nvl(l.invoice_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_invoice_text, '-99999')
and (nvl(l.bank_account_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_bank_account_text,'-99999')
or nvl(l.customer_text, '-99999') =
nvl(CE_AUTO_BANK_MATCH.csl_customer_text,'-99999')))
AND to_char(l.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND l.status = 'RECONCILED'
AND l.statement_line_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
AND l.trx_type in ('DEBIT', 'CREDIT')
AND l.statement_header_id = h.statement_header_id
AND r.statement_line_id = l.statement_line_id
AND r.org_id = v2.org_id
AND nvl(r.current_record_flag, 'Y') = 'Y'
AND nvl(r.status_flag, 'U') <> 'U'
AND glcc.conversion_type = l.exchange_rate_type
AND v2.bank_account_id = h.bank_account_id
--AND v2.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND v2.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v2.org_id)
AND v2.trx_number = nvl(l.bank_trx_number,v2.trx_number)
AND to_char(v2.trx_date,'YYYY/MM/DD') <=
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
AND CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'DEBIT', - l.amount, l.amount) < 0
AND v2.bank_account_amount
between (- CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'DEBIT', l.amount, - l.amount)
- decode(l.currency_code,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance,
CE_AUTO_BANK_MATCH.aba_bank_currency,
tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
and (- CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'DEBIT', l.amount, - l.amount)
+ decode(l.currency_code,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance,
CE_AUTO_BANK_MATCH.aba_bank_currency,
tolerance_amount,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
AND v2.bank_account_amount
between ((- CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'DEBIT', l.amount, - l.amount))
- abs((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'DEBIT', l.amount, - l.amount))
* CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
and ((- CE_AUTO_BANK_MATCH.calc_csl_amount
+ decode(l.trx_type, 'DEBIT', l.amount, - l.amount))
+ abs((- CE_AUTO_BANK_MATCH.calc_csl_amount +
decode(l.trx_type, 'DEBIT', l.amount, - l.amount))
* CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance / 100))
AND v2.application_id = 200
AND NVL(v2.reconciled_status_flag, 'N') = 'N';
X_LAST_UPDATED_BY => nvl(FND_GLOBAL.user_id, -1),
X_LAST_UPDATE_LOGIN => nvl(FND_GLOBAL.user_id, -1),
X_CREATED_BY => nvl(FND_GLOBAL.user_id, -1),
X_PROGRAM_APPLICATION_ID => NULL,
X_PROGRAM_ID => NULL,
X_REQUEST_ID => NULL);
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
X_CREATION_DATE => sysdate,
X_PROGRAM_UPDATE_DATE => to_date(NULL),
X_PROGRAM_APPLICATION_ID=> to_number(NULL),
X_PROGRAM_ID => to_number(NULL),
X_REQUEST_ID => to_number(NULL),
X_CALLING_SEQUENCE => 'CE_AUTO_BANK_MATCH.stmtline_match');
delete from ce_statement_reconcils_all
where statement_line_id = CE_AUTO_BANK_MATCH.trx_id2
and request_id = nvl(FND_GLOBAL.conc_request_id,-1);
X_LAST_UPDATED_BY => nvl(FND_GLOBAL.user_id, -1),
X_LAST_UPDATE_LOGIN => nvl(FND_GLOBAL.user_id, -1),
X_CREATED_BY => nvl(FND_GLOBAL.user_id, -1),
X_PROGRAM_APPLICATION_ID => NULL,
X_PROGRAM_ID => NULL,
X_REQUEST_ID => NULL);
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
X_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
X_CREATION_DATE => sysdate,
X_PROGRAM_UPDATE_DATE => to_date(NULL),
X_PROGRAM_APPLICATION_ID=> to_number(NULL),
X_PROGRAM_ID => to_number(NULL),
X_REQUEST_ID => to_number(NULL),
X_CALLING_SEQUENCE => 'CE_AUTO_BANK_MATCH.stmtline_match');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_STMTL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
select count(*)
from ap_invoices ap,
ap_invoice_payments aip,
ap_checks_all ac,
ce_bank_accounts ba,
ce_bank_acct_uses_ou_v bau
where ba.bank_account_num = CE_AUTO_BANK_MATCH.csl_bank_account_text
and ba.bank_account_id = bau.bank_account_id
and bau.AP_USE_ENABLE_FLAG ='Y'
--and bau.bank_account_id = ac.external_bank_account_id
--and bau.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND bau.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,bau.org_id)
and bau.bank_acct_use_id = ac.CE_BANK_ACCT_USE_ID
and ac.check_id = aip.check_id
and ac.org_id = aip.org_id
and aip.invoice_id = ap.invoice_id
and ap.invoice_num = CE_AUTO_BANK_MATCH.csl_invoice_text;
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_MULTI_MATCH_INVOICE');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_PMT_PARTIAL');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_FOREIGN_RECON');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_OPEN_INTERFACE_DISABLED');
--bug 7565001 : selective setting secondary_match
IF (primary_match = 'T') THEN
secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'B');
SELECT count(*)
INTO accounting_method_found
FROM ar_system_parameters s
where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id);
SELECT accounting_method
INTO CE_AUTO_BANK_MATCH.ar_accounting_method
FROM ar_system_parameters s
where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id);
select 'Y'
into misc_exists
--from ce_222_transactions_v
from ce_available_transactions_tmp
where trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number || '/NSF'
and trx_type = 'MISC'
and rownum = 1
and application_id = 222
and reconciled_status_flag = 'N';
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_REQUIRED_EXCHANGE_FIELD');
CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PAYMENT_METHOD');
select mo_global.GET_CURRENT_ORG_ID
into current_org_id
from dual;
--CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
--CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DR_NOT_FOUND');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_INFO_MISSING');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id, l_message_name,
l_app_short_name);
SELECT statement_header_id
INTO x_statement_header_id
FROM ce_statement_headers
WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid
FOR UPDATE OF statement_header_id NOWAIT;
SELECT statement_line_id,
trx_date,
trx_type,
trx_code_id,
bank_trx_number,
invoice_text,
bank_account_text,
amount,
NVL(charges_amount,0),
currency_code,
line_number,
customer_text,
effective_date,
original_amount
INTO CE_AUTO_BANK_MATCH.csl_statement_line_id,
CE_AUTO_BANK_MATCH.csl_trx_date,
CE_AUTO_BANK_MATCH.csl_trx_type,
CE_AUTO_BANK_MATCH.csl_trx_code_id,
CE_AUTO_BANK_MATCH.csl_bank_trx_number,
CE_AUTO_BANK_MATCH.csl_invoice_text,
CE_AUTO_BANK_MATCH.csl_bank_account_text,
CE_AUTO_BANK_MATCH.csl_amount,
CE_AUTO_BANK_MATCH.csl_charges_amount,
CE_AUTO_BANK_MATCH.csl_currency_code,
CE_AUTO_BANK_MATCH.csl_line_number,
CE_AUTO_BANK_MATCH.csl_customer_text,
CE_AUTO_BANK_MATCH.csl_effective_date,
CE_AUTO_BANK_MATCH.csl_original_amount
FROM ce_statement_lines
WHERE rowid = CE_AUTO_BANK_MATCH.csl_rowid
FOR UPDATE OF status NOWAIT;
| update_gl_date |
| |
| DESCRIPTION |
| Update the gl posting date on ce_statement_headers to the new |
| one for this run. |
| |
| CALLED BY |
| match_process |
--------------------------------------------------------------------- */
PROCEDURE update_gl_date IS
BEGIN
cep_standard.debug('>>CE_AUTO_BANK_MATCH.update_gl_date');
UPDATE ce_statement_headers
SET gl_date = CE_AUTO_BANK_REC.G_gl_date
WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
cep_standard.debug('<
cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.update_gl_date' );
END update_gl_date;
select ACCOUNT_OWNER_ORG_ID
into p_le_id
from ce_bank_accts_gt_v --ce_BANK_ACCOUNTS_v
where BANK_ACCOUNT_ID = x_bank_account_id;
select AMOUNT_TOLERANCE_OLD,
PERCENT_TOLERANCE_OLD,
OI_FLOAT_STATUS_OLD,
OI_CLEAR_STATUS_OLD,
FLOAT_HANDLING_FLAG_OLD,
SHOW_VOID_PAYMENT_FLAG,
OI_MATCHING_CODE_OLD
FROM CE_SYSTEM_PARAMETERS;
SELECT arm.name
INTO CE_AUTO_BANK_REC.G_payment_method_name
FROM ar_receipt_method_accounts arma,
ar_receipt_methods arm
WHERE arm.receipt_method_id = arma.receipt_method_id
--AND arma.REMIT_BANK_ACCT_USE_ID = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
AND arma.REMIT_BANK_ACCT_USE_ID = nvl(CE_AUTO_BANK_MATCH.trx_bank_acct_use_id, arma.REMIT_BANK_ACCT_USE_ID)
--AND arma.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
--AND arma.org_id = CE_AUTO_BANK_MATCH.bau_org_id
AND arma.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, nvl(CE_AUTO_BANK_REC.G_org_id, arma.org_id))
--AND arm.receipt_method_id = nvl(CE_AUTO_BANK_REC.G_payment_method_id,CE_AUTO_BANK_MATCH.csl_receipt_method_id)
AND arm.receipt_method_id = nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id, CE_AUTO_BANK_REC.G_payment_method_id)
AND CE_AUTO_BANK_MATCH.csl_trx_date between nvl(arm.start_date,CE_AUTO_BANK_MATCH.csl_trx_date)
AND nvl(arm.end_date,CE_AUTO_BANK_MATCH.csl_trx_date)
and exists (select 1 from ce_bank_acct_uses_gt_v bau
where bau.bank_acct_use_id = arma.REMIT_BANK_ACCT_USE_ID
and bau.bank_account_id =CE_AUTO_BANK_MATCH.csh_bank_account_id
and bau.AR_USE_ENABLE_FLAG = 'Y' );
SELECT ar.liability_tax_code, ar.asset_tax_code
INTO y_dr_vat_tax_code, y_cr_vat_tax_code
FROM ar_receivables_trx ar
WHERE ar.receivables_trx_id = CE_AUTO_BANK_MATCH.csl_receivables_trx_id
AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ; --CE_AUTO_BANK_MATCH.bau_org_id;
select LEGAL_ENTITY_ID
into l_le_id
from XLE_FP_OU_LEDGER_V
where OPERATING_UNIT_ID =nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
SELECT ar.vat_tax_id, ar.tax_rate
INTO l_vat_tax_id, X_tax_rate
FROM ce_misc_tax_code_v ar --ar_vat_tax ar
WHERE ar.tax_code = y_dr_vat_tax_code
AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, CE_AUTO_BANK_REC.G_org_id)
AND to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
between to_char(ar.start_date,'YYYY/MM/DD')
and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
SELECT ar.vat_tax_id, ar.tax_rate
INTO l_vat_tax_id, X_tax_rate
FROM ce_misc_tax_code_v ar --ar_vat_tax ar
WHERE tax_code = y_cr_vat_tax_code
AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id)
AND to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
between to_char(ar.start_date,'YYYY/MM/DD')
and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
Select count(*)
into x_ap_count
from ce_bank_acct_uses_all bau, CE_SECURITY_PROFILES_GT OU
where AP_USE_ENABLE_FLAG = 'Y'
and sysdate <= nvl(end_date,sysdate)
and BANK_ACCOUNT_ID = x_bank_account_id
and BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT';
Select count(*)
into x_ar_count
from ce_bank_acct_uses_all bau, CE_SECURITY_PROFILES_GT OU
where AR_USE_ENABLE_FLAG = 'Y'
and sysdate <= nvl(end_date,sysdate)
and BANK_ACCOUNT_ID = x_bank_account_id
and BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT';
Select count(*)
into x_xtr_count
from ce_bank_acct_uses_all bau, CE_SECURITY_PROFILES_GT OU
where XTR_USE_ENABLE_FLAG = 'Y'
and sysdate <= nvl(end_date,sysdate)
and BANK_ACCOUNT_ID = x_bank_account_id
and BAU.LEGAL_ENTITY_ID = OU.ORGANIZATION_ID --BUG 5122576
AND OU.ORGANIZATION_TYPE = 'LEGAL_ENTITY';
Select count(*)
into x_pay_count
from ce_bank_acct_uses_all bau, CE_SECURITY_PROFILES_GT OU
where PAY_USE_ENABLE_FLAG = 'Y'
and sysdate <= nvl(end_date,sysdate)
and BANK_ACCOUNT_ID = x_bank_account_id
and BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP';
SELECT
JEL.JE_HEADER_ID,
JEL.JE_LINE_NUM,
SOB.CURRENCY_CODE,
JEL.ENTERED_DR,
JEL.ENTERED_CR,
JEH.CURRENCY_CODE
INTO
l_je_header_id,
l_je_line_num,
l_sob_currency_code,
l_je_entered_dr,
l_je_entered_cr,
l_je_currency_code
FROM
GL_JE_LINES JEL,
GL_SETS_OF_BOOKS SOB,
CE_SYSTEM_PARAMETERS SYS,
GL_JE_HEADERS JEH,
GL_PERIOD_STATUSES GPS,
XLA_DISTRIBUTION_LINKS XLA,
XLA_AE_LINES XLL
WHERE
JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL.CODE_COMBINATION_ID = CE_AUTO_BANK_MATCH.aba_asset_code_combination_id
AND JEL.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
--JEC AND JEL.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND JEL.EFFECTIVE_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND JEH.JE_SOURCE ='Other'
AND JEL.STATUS = 'P'
AND JEH.ACTUAL_FLAG = 'A'
AND JEL.EFFECTIVE_DATE = CE_AUTO_BANK_MATCH.csl_accounting_date
AND JEH.CURRENCY_CODE = NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
CE_AUTO_BANK_MATCH.aba_bank_currency)
AND XLL.AE_LINE_NUM = XLA.AE_LINE_NUM
AND XLL.AE_HEADER_ID = XLA.AE_HEADER_ID
AND XLA.APPLICATION_ID = 260
AND XLA.EVENT_ID = CE_AUTO_BANK_MATCH.csl_event_id
AND JEL.GL_SL_LINK_ID = XLL.GL_SL_LINK_ID
AND JEL.GL_SL_LINK_TABLE = XLL.GL_SL_LINK_TABLE
AND NOT EXISTS
(SELECT NULL
FROM
CE_STATEMENT_RECONCILS_ALL CRE2,
CE_SYSTEM_PARAMETERS SYS2
WHERE JEL.JE_HEADER_ID = CRE2.JE_HEADER_ID
AND JEL.JE_LINE_NUM = CRE2.REFERENCE_ID
AND CRE2.STATUS_FLAG = 'M'
AND NVL(CRE2.CURRENT_RECORD_FLAG,'Y') = 'Y');
SELECT catv.trx_id,
catv.cash_receipt_id,
catv.row_id,
catv.trx_date,
catv.currency_code,
catv.bank_account_amount,
catv.base_amount,
catv.status,
nvl(catv.amount_cleared,0),
'CASHFLOW',
1,
catv.trx_currency_type,
catv.amount,
catv.clearing_trx_type,
catv.exchange_rate,
catv.exchange_rate_date,
catv.exchange_rate_type,
catv.legal_entity_id,
catv.seq_id
INTO CE_AUTO_BANK_MATCH.trx_id,
CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
CE_AUTO_BANK_MATCH.trx_rowid,
CE_AUTO_BANK_MATCH.trx_date,
CE_AUTO_BANK_MATCH.trx_currency_code,
CE_AUTO_BANK_MATCH.trx_amount,
CE_AUTO_BANK_MATCH.trx_base_amount,
CE_AUTO_BANK_MATCH.trx_status,
CE_AUTO_BANK_MATCH.trx_cleared_amount,
CE_AUTO_BANK_MATCH.csl_match_type,
no_of_matches,
CE_AUTO_BANK_MATCH.trx_currency_type,
CE_AUTO_BANK_MATCH.trx_curr_amount,
CE_AUTO_BANK_MATCH.csl_clearing_trx_type,
CE_AUTO_BANK_MATCH.trx_exchange_rate,
CE_AUTO_BANK_MATCH.trx_exchange_rate_date,
CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
CE_AUTO_BANK_MATCH.trx_legal_entity_id,
CE_AUTO_BANK_MATCH.gt_seq_id
--FROM ce_260_cf_transactions_v catv
FROM ce_available_transactions_tmp catv
WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
AND catv.TRX_ID = CE_AUTO_BANK_MATCH.csl_cashflow_id
AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,catv.legal_entity_id)
AND catv.application_id = 261
AND NVL(catv.reconciled_status_flag, 'N') = 'N';
CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
-- update the reconciled_status_flag of the GT table, ce_available_transactions_tmp,
-- to 'Y'
update_gt_reconciled_status (CE_AUTO_BANK_MATCH.gt_seq_id, 'Y');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_CE_TRX_AMT_OR_CUR_NOT_MATCH');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_NO_CE_TRX_MATCH');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_ABR_JEL_PARTIAL');
SELECT trx_type,
trx_code,
decode(PAYROLL_PAYMENT_FORMAT_ID, null, NVL(reconcile_flag,'X'),
decode(reconcile_flag,'PAY', 'PAY_EFT', NVL(reconcile_flag,'X'))),
matching_against
FROM ce_transaction_codes
WHERE bank_account_id = p_bank_account_id;
delete ce_available_transactions_tmp;
select count(1)
into l_cf
from ce_statement_lines sl,
ce_statement_headers sh
where sl.statement_header_id = sh.statement_header_id
and sh.bank_account_id = p_bank_account_id
and sl.cashflow_id is not null;
IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_260_cf_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
COUNTERPARTY,
TRXN_SUBTYPE,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
261, --APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
COUNTERPARTY,
TRXN_SUBTYPE,
CE_BANK_ACCT_USE_ID,
'N'
from ce_260_cf_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag := 'Y';
END IF; --CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N'
IF CE_AUTO_BANK_MATCH.av_200_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
cep_standard.debug('inserting data from ce_200_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_200_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_200_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
cep_standard.debug('inserting data from ce_222_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_222_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_222_inserted_flag := 'Y';
IF av_260_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_260_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
CE_BANK_ACCT_USE_ID,
'N'
from ce_260_transactions_v
where bank_account_id = p_bank_account_id;
av_260_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_260_cf_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
COUNTERPARTY,
TRXN_SUBTYPE,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
261, --APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
COUNTERPARTY,
TRXN_SUBTYPE,
CE_BANK_ACCT_USE_ID,
'N'
from ce_260_cf_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_101_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_101_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
DESCRIPTION,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
DESCRIPTION,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_101_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_101_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_999_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_999_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_999_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_999_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_801_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_801_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_801_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_801_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_801_eft_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
802, --APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_801_eft_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag := 'Y';
IF CE_AUTO_BANK_MATCH.av_260_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_260_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
INVOICE_TEXT,
BANK_ACCOUNT_TEXT,
CUSTOMER_TEXT,
CE_BANK_ACCT_USE_ID,
'N'
from ce_260_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_260_inserted_flag := 'Y';
END IF; -- CE_AUTO_BANK_MATCH.av_260_inserted_flag = 'N'
IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_222_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_222_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_222_inserted_flag := 'Y';
END IF; -- CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N'
IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
cep_standard.debug('inserting data from ce_222_transactions_v');
insert into ce_available_transactions_tmp
(seq_id,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
RECONCILED_STATUS_FLAG)
select ce_available_transactions_s.nextval,
ROW_ID,
MULTI_SELECT,
BANK_ACCOUNT_ID,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NUM,
BANK_NAME,
BANK_BRANCH_NAME,
TRX_ID,
TRX_TYPE,
TYPE_MEANING,
TRX_NUMBER,
CHECK_NUMBER,
CURRENCY_CODE,
AMOUNT,
BANK_ACCOUNT_AMOUNT,
AMOUNT_CLEARED,
GL_DATE,
STATUS_DSP,
STATUS,
TRX_DATE,
CLEARED_DATE,
MATURITY_DATE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE_TYPE,
USER_EXCHANGE_RATE_TYPE,
EXCHANGE_RATE,
BANK_CHARGES,
BANK_ERRORS,
BATCH_NAME,
BATCH_ID,
AGENT_NAME,
CUSTOMER_NAME,
PAYMENT_METHOD,
VENDOR_NAME,
CUSTOMER_ID,
SUPPLIER_ID,
REFERENCE_TYPE_DSP,
REFERENCE_TYPE,
REFERENCE_ID,
ACTUAL_AMOUNT_CLEARED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REMITTANCE_NUMBER,
CASH_RECEIPT_ID,
APPLICATION_ID,
COUNT_CLEARED,
BANK_CURRENCY_CODE,
TRX_CURRENCY_TYPE,
CODE_COMBINATION_ID,
PERIOD_NAME,
JOURNAL_ENTRY_NAME,
DOCUMENT_NUMBER,
JOURNAL_ENTRY_LINE_NUMBER,
CLEARING_TRX_TYPE,
JOURNAL_CATEGORY,
BASE_AMOUNT,
RECEIPT_CLASS_ID,
RECEIPT_METHOD_ID,
RECEIPT_CLASS_NAME,
DEPOSIT_DATE,
VALUE_DATE,
REVERSED_RECEIPT_FLAG,
LEGAL_ENTITY_ID,
ORG_ID,
CE_BANK_ACCT_USE_ID,
'N'
from ce_222_transactions_v
where bank_account_id = p_bank_account_id;
CE_AUTO_BANK_MATCH.av_222_inserted_flag := 'Y';
END IF; -- av_222_inserted_flag = 'N'
| update_gt_reconciled_status |
| |
| DESCRIPTION |
| update the reconciled_status_flag of table |
| ce_available_transactions_tmp |
| |
| CALLED BY |
| match_process |
| match_stmt_line_JE |
| CE_AUTO_BANK_CLEAR1.reconcile_pbatch |
| CE_AUTO_BANK_CLEAR1.reconcile_rbatch |
| CE_AUTO_BANK_CLEAR1.reconcile_pay_eft |
| |
| HISTORY |
| 11-MAY-2006 Xin Wang Created |
--------------------------------------------------------------------- */
PROCEDURE update_gt_reconciled_status(p_seq_id NUMBER,
p_status VARCHAR2) IS
BEGIN
update ce_available_transactions_tmp
set reconciled_status_flag = p_status
where seq_id = p_seq_id;
cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
END update_gt_reconciled_status;
| update_gt_reconciled_status |
| |
| DESCRIPTION |
| update the reconciled_status_flag of table |
| ce_available_transactions_tmp |
| mainly used to update the status to 'N' during unreconciliation |
| |
| CALLED BY |
| CE_AUTO_BANK_CLEAR1.unclear_process |
| |
| HISTORY |
| 11-MAY-2006 Xin Wang Created |
--------------------------------------------------------------------- */
PROCEDURE update_gt_reconciled_status(p_application_id NUMBER,
p_trx_id NUMBER,
p_reconciled_status VARCHAR2) IS
BEGIN
update ce_available_transactions_tmp
set reconciled_status_flag = p_reconciled_status
where application_id = p_application_id
and trx_id = p_trx_id;
cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
END update_gt_reconciled_status;
| update_gt_reconciled_status |
| |
| DESCRIPTION |
| update the reconciled_status_flag of table |
| ce_available_transactions_tmp |
| mainly used to update the status to 'N' during |
| auto unreconciliation |
| |
| CALLED BY |
| CE_AUTO_BANK_CLEAR1.unclear_process |
| |
| HISTORY |
| 11-MAY-2006 Xin Wang Created |
--------------------------------------------------------------------- */
PROCEDURE update_gt_reconciled_status(p_reconciled_status VARCHAR2) IS
BEGIN
update ce_available_transactions_tmp
set reconciled_status_flag = p_reconciled_status;
cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
END update_gt_reconciled_status;
| Update_GL_Date |
| CE_RECONCILIATION_ERRORS_PKG.delete_row |
| Lock_Statement_Line |
| Match_Statement_Line |
| CE_AUTO_BANK_CLEAR.reconcile_process |
| CE_RECONCILIATION_ERRORS_PKG.insert_row |
| Get_Min_Statement_Line_Id |
| |
| CALLED BY |
| statement |
--------------------------------------------------------------------- */
PROCEDURE match_process IS
error_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE;
SELECT count(*)
INTO accounting_method_found
FROM ar_system_parameters s
where s.org_id = CE_AUTO_BANK_REC.G_org_id;
SELECT accounting_method
INTO CE_AUTO_BANK_MATCH.ar_accounting_method
FROM ar_system_parameters s
where s.org_id = CE_AUTO_BANK_REC.G_org_id;
CE_AUTO_BANK_MATCH.av_101_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_200_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_222_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_260_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_801_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_999_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_185_inserted_flag := 'N';
select mo_global.GET_CURRENT_ORG_ID
into current_org_id
from dual;
SELECT count(*)
INTO accounting_method_found
FROM ar_system_parameters s
where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.bau_org_id);
SELECT accounting_method
INTO CE_AUTO_BANK_MATCH.ar_accounting_method
FROM ar_system_parameters s
where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.bau_org_id);
CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
CE_AUTO_BANK_REC.G_set_of_books_id,
CE_AUTO_BANK_REC.G_cashbook_begin_date,
CE_AUTO_BANK_REC.G_show_cleared_flag,
CE_AUTO_BANK_REC.G_show_void_payment_flag,
CE_AUTO_BANK_REC.G_line_autocreation_flag,
CE_AUTO_BANK_REC.G_interface_purge_flag,
CE_AUTO_BANK_REC.G_interface_archive_flag,
CE_AUTO_BANK_REC.G_lines_per_commit,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_REC.G_sob_short_name,
CE_AUTO_BANK_REC.G_account_period_type,
CE_AUTO_BANK_REC.G_user_exchange_rate_type,
CE_AUTO_BANK_REC.G_chart_of_accounts_id,
CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
CE_AUTO_BANK_MATCH.ba_owner_le_id
);
CE_SYSTEM_PARAMETERS1_PKG.ba_select_columns(CE_AUTO_BANK_MATCH.BA_ROWID,
CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance,
CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance,
CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance,
CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance,
CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance,
CE_AUTO_BANK_REC.G_float_handling_flag,
CE_AUTO_BANK_REC.G_ap_matching_order,
CE_AUTO_BANK_REC.G_ar_matching_order,
CE_AUTO_BANK_REC.G_exchange_rate_type,
CE_AUTO_BANK_REC.G_exchange_rate_date,
CE_AUTO_BANK_REC.G_open_interface_flag,
CE_AUTO_BANK_REC.G_open_interface_float_status,
CE_AUTO_BANK_REC.G_open_interface_clear_status,
CE_AUTO_BANK_REC.G_open_interface_matching_code,
CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE,
CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE,
ignore_trx_id,
ignore_trx_id2,
CE_AUTO_BANK_MATCH.BA_RECON_AP_FX_DIFF_HANDLING,
CE_AUTO_BANK_MATCH.BA_RECON_AR_FX_DIFF_HANDLING,
CE_AUTO_BANK_MATCH.BA_RECON_CE_FX_DIFF_HANDLING,
CE_AUTO_BANK_REC.G_differences_account,
CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
CE_AUTO_BANK_MATCH.ba_owner_le_id,
CE_AUTO_BANK_MATCH.csh_bank_account_id,
CE_AUTO_BANK_REC.G_ap_matching_order2 -- FOR SEPA ER 6700007
);
CE_AUTO_BANK_MATCH.av_101_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_200_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_222_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_260_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_801_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag := 'N';
CE_AUTO_BANK_MATCH.av_999_inserted_flag := 'N';
select count(1)
into row_count
from ce_statement_lines
where statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
to_number(NULL));
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
to_number(NULL), 'CE_NO_STMT_LINE');
/* Select count(*)
into x_pay_count
from ce_bank_acct_uses_all bau, CE_SECURITY_PROFILES_GT OU
where bau.PAY_USE_ENABLE_FLAG = 'Y'
and sysdate <= nvl(end_date,sysdate)
and BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.csh_bank_account_id
--and BAU.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
and BAU.ORG_ID = nvl(CE_AUTO_BANK_REC.G_org_id, BAU.ORG_ID)
and BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP';
/* SELECT aba.account_classification
INTO account_type
FROM ce_bank_accounts_v aba
WHERE aba.bank_account_id =
CE_AUTO_BANK_MATCH.csh_bank_account_id;
update_gl_date;
-- Read in all the lines on the statement for the selected bank
-- account.
--
cep_standard.debug('>>CE_AUTO_BANK_MATCH.Opening line_cursor');
select count(*)
into rec_status
--from ce_statement_reconciliations
from ce_statement_reconcils_all
where statement_line_id =
CE_AUTO_BANK_MATCH.csl_statement_line_id
and nvl(status_flag, 'U') = 'M'
and nvl(current_record_flag, 'Y') = 'Y';
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id);
-- update the ce_available_transactions_tmp.reconciled_status_flag
IF (CE_AUTO_BANK_MATCH.gt_seq_id is not null) AND
(CE_AUTO_BANK_MATCH.gt_seq_id <> -1) THEN
update_gt_reconciled_status (CE_AUTO_BANK_MATCH.gt_seq_id, 'Y');
--update ce_available_transactions_tmp
--set reconciled_status_flag = 'Y'
--where seq_id = CE_AUTO_BANK_MATCH.gt_seq_id;
update_gt_reconciled_status (CE_AUTO_BANK_MATCH.gt_seq_id2, 'Y');
-- CE_RECONCILIATION_ERRORS_PKG.insert_row(
-- CE_AUTO_BANK_MATCH.csh_statement_header_id,
-- CE_AUTO_BANK_MATCH.csl_statement_line_id,
-- 'CE_DR_NOT_FOUND');
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
CE_AUTO_BANK_MATCH.csl_statement_line_id,
'CE_LINE_LOCKED');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
error_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,
error_statement_line_id, 'CE_STATEMENT_COMPLETED');
CE_RECONCILIATION_ERRORS_PKG.delete_row(error_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
error_statement_line_id,'CE_LOCK_STATEMENT_HEADER_ERR');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id, to_number(NULL));
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,to_number(NULL),
'CE_LOCK_STATEMENT_HEADER_ERR');
CE_RECONCILIATION_ERRORS_PKG.delete_row(error_statement_line_id);
CE_RECONCILIATION_ERRORS_PKG.insert_row(
error_statement_line_id,'CE_CHECK_DIGITS');
CE_RECONCILIATION_ERRORS_PKG.delete_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id, to_number(NULL));
CE_RECONCILIATION_ERRORS_PKG.insert_row(
CE_AUTO_BANK_MATCH.csh_statement_header_id,to_number(NULL),
'CE_CHECK_DIGITS');