[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT (*)
INTO trans_count
FROM ar_payments_interface_all
WHERE transmission_request_id = req_id;
SELECT COUNT (*)
INTO inv_count
FROM ra_customer_trx_all
WHERE trx_number = ar_inv_ref
AND org_id = p_org_id
AND interface_header_context <> 'OKL_CONTRACTS';
SELECT NVL (fld.amount_format_lookup_code, 'N')
FROM ar_transmissions_all trans,
ar_trans_record_formats rec,
ar_trans_field_formats fld
WHERE trans.transmission_request_id = cp_trans_req_id
AND trans.requested_trans_format_id = rec.transmission_format_id
AND rec.record_type_lookup_code = cp_record_type
AND rec.record_format_id = fld.record_format_id
AND fld.field_type_lookup_code = cp_column_type;
SELECT NVL (PRECISION, 0)
FROM fnd_currencies_vl cur
WHERE cur.currency_code = cp_currency_code;
SELECT INVOICE_CURRENCY_CODE
FROM okl_rcpt_arinv_balances_uv
WHERE invoice_number = cp_invoice_number
--commented for bug 5391874
-- AND customer_account_number = cp_customer_num
AND org_id = cp_org_id
and INVOICE_CURRENCY_CODE = nvl(cp_currency_code,INVOICE_CURRENCY_CODE)
AND status = 'OP';
SELECT CURRENCY_CODE
FROM okl_rcpt_consinv_balances_uv
WHERE consolidated_invoice_number = cp_cons_invoice_number
--commented for bug 5391874
-- AND customer_account_number = cp_customer_num
AND org_id = cp_org_id
and CURRENCY_CODE = nvl(cp_currency_code,CURRENCY_CODE)
AND status = 'OP';
SELECT CURRENCY_CODE
-- FROM okl_rcpt_consinv_balances_uv -- incorrect view .. bug 7018894
FROM okl_rcpt_cust_cont_balances_uv
WHERE contract_number = cp_contract_number
--commented for bug 5391874
-- AND customer_account_number = cp_customer_num
AND org_id = cp_org_id
and CURRENCY_CODE = nvl(cp_currency_code,CURRENCY_CODE)
AND status = 'OP';
SELECT SUM (amount_due_remaining) due
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_inv_id
AND org_id = p_org_id
AND line_type = 'LINE';
SELECT arpt_sql_func_util.get_activity_flag
(ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.TYPE,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
) activity
FROM ra_cust_trx_types ctt, ra_customer_trx ct
WHERE ct.customer_trx_id = p_inv_id
AND ct.org_id = p_org_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id;
SELECT hca.account_number
FROM iby_ext_bank_accounts_v a,
iby_ext_bank_accounts b,
iby_pmt_instr_uses_all ipiua,
iby_external_payers_all iepa,
hz_cust_accounts hca
WHERE a.ext_bank_account_id = b.ext_bank_account_id
AND ipiua.instrument_id = a.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payer_id
AND ipiua.payment_flow = 'FUNDS_CAPTURE'
AND iepa.cust_account_id = hca.cust_account_id
AND a.bank_number = cp_transit_routing_number
AND b.bank_account_num = cp_account;
SELECT b.record_identifier,
b.record_type_lookup_code,
b.transmission_format_id
FROM ar_transmissions_all a, ar_trans_record_formats b
WHERE a.requested_trans_format_id = b.transmission_format_id
AND a.transmission_request_id = cp_trans_req_id
ORDER BY b.record_identifier;
SELECT overflow_rec_indicator
FROM ar_trans_field_formats
WHERE transmission_format_id = cp_trans_frmt_id
AND overflow_rec_indicator IS NOT NULL;
SELECT transmission_record_id,
transmission_id,
lockbox_number,
batch_name,
remittance_amount,
NVL (receipt_date, SYSDATE),
item_number,
currency_code,
customer_number,
check_number,
transit_routing_number,
ACCOUNT,
TRIM (invoice1),
TRIM (invoice2),
TRIM (invoice3),
TRIM (invoice4),
TRIM (invoice5),
TRIM (invoice6),
TRIM (invoice7),
TRIM (invoice8),
tmp_amt_applied1,
tmp_amt_applied2,
tmp_amt_applied3,
tmp_amt_applied4,
tmp_amt_applied5,
tmp_amt_applied6,
tmp_amt_applied7,
tmp_amt_applied8,
AMOUNT_APPLIED_FROM1,
AMOUNT_APPLIED_FROM2,
AMOUNT_APPLIED_FROM3,
AMOUNT_APPLIED_FROM4,
AMOUNT_APPLIED_FROM5,
AMOUNT_APPLIED_FROM6,
AMOUNT_APPLIED_FROM7,
AMOUNT_APPLIED_FROM8,
TRANS_TO_RECEIPT_RATE1,
TRANS_TO_RECEIPT_RATE2,
TRANS_TO_RECEIPT_RATE3,
TRANS_TO_RECEIPT_RATE4,
TRANS_TO_RECEIPT_RATE5,
TRANS_TO_RECEIPT_RATE6,
TRANS_TO_RECEIPT_RATE7,
TRANS_TO_RECEIPT_RATE8,
INVOICE_CURRENCY_CODE1,
INVOICE_CURRENCY_CODE2,
INVOICE_CURRENCY_CODE3,
INVOICE_CURRENCY_CODE4,
INVOICE_CURRENCY_CODE5,
INVOICE_CURRENCY_CODE6,
INVOICE_CURRENCY_CODE7,
INVOICE_CURRENCY_CODE8,
/* tmp_amt_applied_from1,
tmp_amt_applied_from2,
tmp_amt_applied_from3,
tmp_amt_applied_from4,
tmp_amt_applied_from5,
tmp_amt_applied_from6,
tmp_amt_applied_from7,
tmp_amt_applied_from8,
TMP_TRANS_TO_RCPT_RATE1,
TMP_TRANS_TO_RCPT_RATE2,
TMP_TRANS_TO_RCPT_RATE3,
TMP_TRANS_TO_RCPT_RATE4,
TMP_TRANS_TO_RCPT_RATE5,
TMP_TRANS_TO_RCPT_RATE6,
TMP_TRANS_TO_RCPT_RATE7,
TMP_TRANS_TO_RCPT_RATE8,
TMP_INV_CURRENCY_CODE1,
TMP_INV_CURRENCY_CODE2,
TMP_INV_CURRENCY_CODE3,
TMP_INV_CURRENCY_CODE4,
TMP_INV_CURRENCY_CODE5,
TMP_INV_CURRENCY_CODE6,
TMP_INV_CURRENCY_CODE7,
TMP_INV_CURRENCY_CODE8, */
org_id
FROM ar_payments_interface_all
WHERE transmission_request_id = cp_trans_req_id
AND record_type = cp_rec_type
ORDER BY item_number;
SELECT transmission_record_id,
-- currency_code, we will fetch currecny code from payment record only
TRIM (invoice1),
TRIM (invoice2),
TRIM (invoice3),
TRIM (invoice4),
TRIM (invoice5),
TRIM (invoice6),
TRIM (invoice7),
TRIM (invoice8),
tmp_amt_applied1,
tmp_amt_applied2,
tmp_amt_applied3,
tmp_amt_applied4,
tmp_amt_applied5,
tmp_amt_applied6,
tmp_amt_applied7,
tmp_amt_applied8,
AMOUNT_APPLIED_FROM1,
AMOUNT_APPLIED_FROM2,
AMOUNT_APPLIED_FROM3,
AMOUNT_APPLIED_FROM4,
AMOUNT_APPLIED_FROM5,
AMOUNT_APPLIED_FROM6,
AMOUNT_APPLIED_FROM7,
AMOUNT_APPLIED_FROM8,
TRANS_TO_RECEIPT_RATE1,
TRANS_TO_RECEIPT_RATE2,
TRANS_TO_RECEIPT_RATE3,
TRANS_TO_RECEIPT_RATE4,
TRANS_TO_RECEIPT_RATE5,
TRANS_TO_RECEIPT_RATE6,
TRANS_TO_RECEIPT_RATE7,
TRANS_TO_RECEIPT_RATE8,
INVOICE_CURRENCY_CODE1,
INVOICE_CURRENCY_CODE2,
INVOICE_CURRENCY_CODE3,
INVOICE_CURRENCY_CODE4,
INVOICE_CURRENCY_CODE5,
INVOICE_CURRENCY_CODE6,
INVOICE_CURRENCY_CODE7,
INVOICE_CURRENCY_CODE8
/* tmp_amt_applied_from1,
tmp_amt_applied_from2,
tmp_amt_applied_from3,
tmp_amt_applied_from4,
tmp_amt_applied_from5,
tmp_amt_applied_from6,
tmp_amt_applied_from7,
tmp_amt_applied_from8,
TMP_TRANS_TO_RCPT_RATE1,
TMP_TRANS_TO_RCPT_RATE2,
TMP_TRANS_TO_RCPT_RATE3,
TMP_TRANS_TO_RCPT_RATE4,
TMP_TRANS_TO_RCPT_RATE5,
TMP_TRANS_TO_RCPT_RATE6,
TMP_TRANS_TO_RCPT_RATE7,
TMP_TRANS_TO_RCPT_RATE8,
TMP_INV_CURRENCY_CODE1,
TMP_INV_CURRENCY_CODE2,
TMP_INV_CURRENCY_CODE3,
TMP_INV_CURRENCY_CODE4,
TMP_INV_CURRENCY_CODE5,
TMP_INV_CURRENCY_CODE6,
TMP_INV_CURRENCY_CODE7,
TMP_INV_CURRENCY_CODE8*/
FROM ar_payments_interface_all
WHERE transmission_request_id = cp_trans_req_id
AND record_type = cp_rec_type
AND item_number = cp_item_number
AND overflow_sequence IS NOT NULL
AND (batch_name IS NULL OR batch_name = cp_batch_name
) --Fixed bug 6033325
ORDER BY overflow_sequence;
SELECT transmission_record_id
FROM ar_payments_interface_all
WHERE transmission_request_id = cp_trans_req_id
AND record_type = cp_rec_type
AND item_number = cp_item_number
AND overflow_sequence IS NULL
AND (batch_name IS NULL OR batch_name = cp_batch_name
) --Fixed bug 6033325
ORDER BY transmission_record_id;
SELECT COUNT (*)
FROM ar_payments_interface_all
WHERE transmission_request_id = cp_trans_req_id;
SELECT days_past_quote_valid_toleranc,
amount_tolerance_percent
FROM okl_cash_allctn_rls
WHERE default_rule = 'YES' AND org_id = cp_org_id;
SELECT 'Y'
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = cp_transmission_request_id
AND transmission_record_id = cp_transmission_record_id
AND invoice_number = cp_invoice_number;
SELECT tq.id quote_id,
tq.date_effective_to,
SUM(NVL(tl.amount,0)) quote_amount
FROM okc_k_party_roles_v pr,
okl_quote_parties_v qp,
okl_trx_quotes_v tq,
okl_txl_quote_lines_v tl
WHERE pr.jtot_object1_code = 'OKX_PARTY'
AND pr.object1_id2 = '#'
AND pr.object1_id1 = cp_cust_id
AND qp.cpl_id = pr.id
AND qp.qpt_code = 'RECIPIENT'
AND tq.id = qp.qte_id
AND TRUNC(cp_rcpt_date) BETWEEN TRUNC (tq.date_effective_from)
AND TRUNC(NVL((tq.date_effective_to + cp_date_tol), SYSDATE))
AND tl.qte_id = tq.id
AND NVL(tq.accepted_yn,'N') = 'N' and tq.qtp_code like 'TER%'
AND NVL(tq.payment_received_yn,'N') = 'N'
AND NVL(tq.preproceeds_yn,'N') = 'N'
GROUP BY pr.object1_id1, tq.id, tq.date_effective_to;
SELECT tq.ID quote_id,
tq.date_effective_to,
tq.quote_number,
SUM (NVL (tl.amount, 0)) quote_amount
FROM okc_k_party_roles_v pr,
okl_quote_parties_v qp,
okl_trx_quotes_v tq,
okl_txl_quote_lines_v tl
WHERE pr.jtot_object1_code = 'OKX_PARTY'
AND pr.object1_id2 = '#'
AND pr.object1_id1 = cp_cust_id
AND qp.cpl_id = pr.ID
AND qp.qpt_code = 'RECIPIENT'
AND tq.ID = qp.qte_id
AND TRUNC (cp_rcpt_date) BETWEEN TRUNC (tq.date_effective_from)
AND TRUNC
(NVL
(( tq.date_effective_to
+ cp_date_tol
),
SYSDATE
)
)
AND tl.qte_id = tq.ID
AND NVL (tq.accepted_yn, 'N') = 'N'
AND tq.qtp_code LIKE 'TER%'
AND NVL (tq.payment_received_yn, 'N') = 'N'
AND NVL (tq.preproceeds_yn, 'N') = 'N'
AND TO_CHAR (tq.quote_number) = cp_quote_number
AND tq.qst_code IN ('APPROVED')
AND tl.qlt_code NOT IN ('AMCFIA')
AND qtp_code NOT IN
('TER_ROLL_PURCHASE', 'TER_ROLL_WO_PURCHASE')
-- Added Bug 3953303
GROUP BY pr.object1_id1, tq.ID, tq.date_effective_to,
tq.quote_number;
SELECT party_id
FROM okx_customer_accounts_v
WHERE description = TO_CHAR (cp_cust_num);
select decode(i,1,l_amount_applied1,2,l_amount_applied2,
3, l_amount_applied3,4,l_amount_applied4,
5,l_amount_applied5,6,l_amount_applied6,
7,l_amount_applied7,8,l_amount_applied8),
decode(i,1,l_amount_app_from1,2,l_amount_app_from2,
3, l_amount_app_from3,4,l_amount_app_from4,
5,l_amount_app_from5,6,l_amount_app_from6,
7,l_amount_app_from7,8,l_amount_app_from8),
decode(i,1,l_currency_code1,2,l_currency_code2,
3,l_currency_code3,4,l_currency_code4,
5,l_currency_code5,6,l_currency_code6,
7,l_currency_code7,8,l_currency_code8)
into l_tmp_amount_applied, l_tmp_amount_app_from, l_tmp_currency_code
from dual;
SELECT ar_payments_interface_s.NEXTVAL
INTO l_transmission_rec_id_of
FROM DUAL;
('Inserting overflow record with application information for the current payment record'
);
INSERT INTO ar_payments_interface_all
(transmission_record_id,
item_number,
record_type,
status,
transmission_id,
transmission_request_id,
lockbox_number,
batch_name,
invoice1,
amount_applied1,
amount_applied_from1,
invoice_currency_code1,
trans_to_receipt_rate1,
invoice2,
amount_applied2,
amount_applied_from2,
invoice_currency_code2,
trans_to_receipt_rate2,
invoice3,
amount_applied3,
amount_applied_from3,
invoice_currency_code3,
trans_to_receipt_rate3,
invoice4,
amount_applied4,
amount_applied_from4,
invoice_currency_code4,
trans_to_receipt_rate4,
invoice5,
amount_applied5,
amount_applied_from5,
invoice_currency_code5,
trans_to_receipt_rate5,
invoice6,
amount_applied6,
amount_applied_from6,
invoice_currency_code6,
trans_to_receipt_rate6,
invoice7,
amount_applied7,
amount_applied_from7,
invoice_currency_code7,
trans_to_receipt_rate7,
invoice8,
amount_applied8,
amount_applied_from8,
invoice_currency_code8,
trans_to_receipt_rate8,
org_id,
creation_date,
last_update_date
)
VALUES (l_transmission_rec_id_of,
l_item_number,
l_overflow,
l_status,
l_transmission_id,
l_trans_req_id,
l_lockbox_number,
l_batch_name,
l_new_invoice1,
l_new_amount_applied1,
l_new_amount_applied_from1,
l_new_currency_code1,
l_new_trans_to_rct_rate1,
l_new_invoice2,
l_new_amount_applied2,
l_new_amount_applied_from2,
l_new_currency_code2,
l_new_trans_to_rct_rate2,
l_new_invoice3,
l_new_amount_applied3,
l_new_amount_applied_from3,
l_new_currency_code3,
l_new_trans_to_rct_rate3,
l_new_invoice4,
l_new_amount_applied4,
l_new_amount_applied_from4,
l_new_currency_code4,
l_new_trans_to_rct_rate4,
l_new_invoice5,
l_new_amount_applied5,
l_new_amount_applied_from5,
l_new_currency_code5,
l_new_trans_to_rct_rate5,
l_new_invoice6,
l_new_amount_applied6,
l_new_amount_applied_from6,
l_new_currency_code6,
l_new_trans_to_rct_rate6,
l_new_invoice7,
l_new_amount_applied7,
l_new_amount_applied_from7,
l_new_currency_code7,
l_new_trans_to_rct_rate7,
l_new_invoice8,
l_new_amount_applied8,
l_new_amount_applied_from8,
l_new_currency_code8,
l_new_trans_to_rct_rate8,
l_org_id,
SYSDATE,
TRUNC (SYSDATE)
);
insert line level details in AR table here
*************************************************************************************/
log_debug
('Processing line level application data for currently inserted overflow record'
);
/* -- first, delete any existing line level application data
DELETE AR_PMTS_INTERFACE_LINE_DETAILS
WHERE transmission_req_id = l_trans_req_id
AND transmission_record_id = l_transmission_record_id
AND invoice_number = l_okl_rcpt_tbl(l_inv_indx).inv_hdr_rec.invoice_number;*/-- now create line level application data
( 'Inserting line level application data for Invoice Number = '
|| l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number
);
INSERT INTO ar_pmts_interface_line_details
(status,
transmission_request_id,
transmission_record_id,
invoice_number,
apply_to,
allocated_receipt_amount,
amount_applied
)
VALUES ('AR_PLB_NEW_RECORD',
l_trans_req_id,
l_transmission_rec_id_of,
l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number,
l_okl_rcpt_tbl (l_inv_indx).inv_lines_tbl
(l_line_indx).invoice_line_number,
l_amount_in_inv_curr,
l_amount_in_rct_curr
);
UPDATE ar_payments_interface_all
SET invoice1 = l_no_mtch_rcpt_tbl (j).invoice_number,
amount_applied1 = l_no_mtch_rcpt_tbl (j).amount_applied,
resolved_matching_number1 =
l_no_mtch_rcpt_tbl (j).invoice_number,
amount_applied_from1 = l_no_mtch_rcpt_tbl(j).amount_applied_from,
trans_to_receipt_rate1 = l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate,
invoice2 = l_no_mtch_rcpt_tbl (j + 1).invoice_number,
amount_applied2 = l_no_mtch_rcpt_tbl (j + 1).amount_applied,
resolved_matching_number2 =
l_no_mtch_rcpt_tbl (j + 1).invoice_number,
amount_applied_from2 = l_no_mtch_rcpt_tbl(j + 1).amount_applied_from,
trans_to_receipt_rate2 = l_no_mtch_rcpt_tbl(j + 1).trans_to_receipt_rate,
invoice3 = l_no_mtch_rcpt_tbl (j + 2).invoice_number,
amount_applied3 = l_no_mtch_rcpt_tbl (j + 2).amount_applied,
resolved_matching_number3 =
l_no_mtch_rcpt_tbl (j + 2).invoice_number,
amount_applied_from3 = l_no_mtch_rcpt_tbl(j + 2).amount_applied_from,
trans_to_receipt_rate3 = l_no_mtch_rcpt_tbl(j + 2).trans_to_receipt_rate,
invoice4 = l_no_mtch_rcpt_tbl (j + 3).invoice_number,
amount_applied4 = l_no_mtch_rcpt_tbl (j + 3).amount_applied,
resolved_matching_number4 =
l_no_mtch_rcpt_tbl (j + 3).invoice_number,
amount_applied_from4 = l_no_mtch_rcpt_tbl(j + 3).amount_applied_from,
trans_to_receipt_rate4 = l_no_mtch_rcpt_tbl(j + 3).trans_to_receipt_rate,
invoice5 = l_no_mtch_rcpt_tbl (j + 4).invoice_number,
amount_applied5 = l_no_mtch_rcpt_tbl (j + 4).amount_applied,
resolved_matching_number5 =
l_no_mtch_rcpt_tbl (j + 4).invoice_number,
amount_applied_from5 = l_no_mtch_rcpt_tbl(j + 4).amount_applied_from,
trans_to_receipt_rate5 = l_no_mtch_rcpt_tbl(j + 4).trans_to_receipt_rate,
invoice6 = l_no_mtch_rcpt_tbl (j + 5).invoice_number,
amount_applied6 = l_no_mtch_rcpt_tbl (j + 5).amount_applied,
resolved_matching_number6 =
l_no_mtch_rcpt_tbl (j + 5).invoice_number,
amount_applied_from6 = l_no_mtch_rcpt_tbl(j + 5).amount_applied_from,
trans_to_receipt_rate6 = l_no_mtch_rcpt_tbl(j + 5).trans_to_receipt_rate,
invoice7 = l_no_mtch_rcpt_tbl (j + 6).invoice_number,
amount_applied7 = l_no_mtch_rcpt_tbl (j + 6).amount_applied,
resolved_matching_number7 =
l_no_mtch_rcpt_tbl (j + 6).invoice_number,
amount_applied_from7 = l_no_mtch_rcpt_tbl(j + 6).amount_applied_from,
trans_to_receipt_rate7 = l_no_mtch_rcpt_tbl(j + 6).trans_to_receipt_rate,
invoice8 = l_no_mtch_rcpt_tbl (j + 7).invoice_number,
amount_applied8 = l_no_mtch_rcpt_tbl (j + 7).amount_applied,
resolved_matching_number8 =
l_no_mtch_rcpt_tbl (j + 7).invoice_number,
amount_applied_from8 = l_no_mtch_rcpt_tbl(j + 7).amount_applied_from,
trans_to_receipt_rate8 = l_no_mtch_rcpt_tbl(j + 7).trans_to_receipt_rate
WHERE transmission_record_id = l_transmission_record_id;
select decode(i,1,l_amount_applied1,2,l_amount_applied2,
3, l_amount_applied3,4,l_amount_applied4,
5,l_amount_applied5,6,l_amount_applied6,
7,l_amount_applied7,8,l_amount_applied8),
decode(i,1,l_amount_app_from1,2,l_amount_app_from2,
3, l_amount_app_from3,4,l_amount_app_from4,
5,l_amount_app_from5,6,l_amount_app_from6,
7,l_amount_app_from7,8,l_amount_app_from8),
decode(i,1,l_currency_code1,2,l_currency_code2,
3,l_currency_code3,4,l_currency_code4,
5,l_currency_code5,6,l_currency_code6,
7,l_currency_code7,8,l_currency_code8)
into l_tmp_amount_applied, l_tmp_amount_app_from, l_tmp_currency_code
from dual;
SELECT ar_payments_interface_s.NEXTVAL
INTO l_transmission_rec_id_of
FROM DUAL;
('Inserting overflow record with application information for the current payment record'
);
INSERT INTO ar_payments_interface_all
(transmission_record_id,
item_number,
record_type,
status,
transmission_id,
transmission_request_id,
lockbox_number,
batch_name,
invoice1,
amount_applied1,
amount_applied_from1,
invoice_currency_code1,
trans_to_receipt_rate1,
invoice2,
amount_applied2,
amount_applied_from2,
invoice_currency_code2,
trans_to_receipt_rate2,
invoice3,
amount_applied3,
amount_applied_from3,
invoice_currency_code3,
trans_to_receipt_rate3,
invoice4,
amount_applied4,
amount_applied_from4,
invoice_currency_code4,
trans_to_receipt_rate4,
invoice5,
amount_applied5,
amount_applied_from5,
invoice_currency_code5,
trans_to_receipt_rate5,
invoice6,
amount_applied6,
amount_applied_from6,
invoice_currency_code6,
trans_to_receipt_rate6,
invoice7,
amount_applied7,
amount_applied_from7,
invoice_currency_code7,
trans_to_receipt_rate7,
invoice8,
amount_applied8,
amount_applied_from8,
invoice_currency_code8,
trans_to_receipt_rate8,
org_id,
creation_date,
last_update_date
)
VALUES (l_transmission_rec_id_of,
l_item_number,
l_overflow,
l_status,
l_transmission_id,
l_trans_req_id,
l_lockbox_number,
l_batch_name,
l_new_invoice1,
l_new_amount_applied1,
l_new_amount_applied_from1,
l_new_currency_code1,
l_new_trans_to_rct_rate1,
l_new_invoice2,
l_new_amount_applied2,
l_new_amount_applied_from2,
l_new_currency_code2,
l_new_trans_to_rct_rate2,
l_new_invoice3,
l_new_amount_applied3,
l_new_amount_applied_from3,
l_new_currency_code3,
l_new_trans_to_rct_rate3,
l_new_invoice4,
l_new_amount_applied4,
l_new_amount_applied_from4,
l_new_currency_code4,
l_new_trans_to_rct_rate4,
l_new_invoice5,
l_new_amount_applied5,
l_new_amount_applied_from5,
l_new_currency_code5,
l_new_trans_to_rct_rate5,
l_new_invoice6,
l_new_amount_applied6,
l_new_amount_applied_from6,
l_new_currency_code6,
l_new_trans_to_rct_rate6,
l_new_invoice7,
l_new_amount_applied7,
l_new_amount_applied_from7,
l_new_currency_code7,
l_new_trans_to_rct_rate7,
l_new_invoice8,
l_new_amount_applied8,
l_new_amount_applied_from8,
l_new_currency_code8,
l_new_trans_to_rct_rate8,
l_org_id,
SYSDATE,
TRUNC (SYSDATE)
);
insert line level details in AR table here
*************************************************************************************/
log_debug
('Processing line level application data for currently inserted overflow record'
);
/* -- first, delete any existing line level application data
DELETE AR_PMTS_INTERFACE_LINE_DETAILS
WHERE transmission_req_id = l_trans_req_id
AND transmission_record_id = l_transmission_record_id
AND invoice_number = l_okl_rcpt_tbl(l_inv_indx).inv_hdr_rec.invoice_number;*/-- now create line level application data
( 'Inserting line level application data for Invoice Number = '
|| l_okl_rcpt_tbl (l_inv_indx).inv_hdr_rec.invoice_number
);
INSERT INTO ar_pmts_interface_line_details
(status,
transmission_request_id,
transmission_record_id,
invoice_number,
apply_to,
allocated_receipt_amount,
amount_applied
)
VALUES ('AR_PLB_NEW_RECORD',
l_trans_req_id,
l_transmission_rec_id_of,
l_okl_rcpt_tbl
(l_inv_indx).inv_hdr_rec.invoice_number,
l_okl_rcpt_tbl
(l_inv_indx).inv_lines_tbl
(l_line_indx).invoice_line_number,
l_amount_in_inv_curr,
l_amount_in_rct_curr
);
DELETE FROM ar_payments_interface_all
-- no need to keep old overflow rec
WHERE transmission_record_id = l_transmission_record_id;
UPDATE ar_payments_interface_all
SET overflow_sequence =
NULL
-- we'll take care of this later.see below
,
overflow_indicator =
NULL
-- we'll take care of this later.see below
,
invoice1 = l_no_mtch_rcpt_tbl (j).invoice_number,
amount_applied1 = l_no_mtch_rcpt_tbl (j).amount_applied,
resolved_matching_number1 =
l_no_mtch_rcpt_tbl (j).invoice_number,
amount_applied_from1 = l_no_mtch_rcpt_tbl(j).amount_applied_from,
trans_to_receipt_rate1 = l_no_mtch_rcpt_tbl(j).trans_to_receipt_rate,
invoice2 = l_no_mtch_rcpt_tbl (j + 1).invoice_number,
amount_applied2 =
l_no_mtch_rcpt_tbl (j + 1).amount_applied,
resolved_matching_number2 =
l_no_mtch_rcpt_tbl (j + 1).invoice_number,
amount_applied_from2 = l_no_mtch_rcpt_tbl(j+1).amount_applied_from,
trans_to_receipt_rate2 = l_no_mtch_rcpt_tbl(j+1).trans_to_receipt_rate,
invoice3 = l_no_mtch_rcpt_tbl (j + 2).invoice_number,
amount_applied3 =
l_no_mtch_rcpt_tbl (j + 2).amount_applied,
resolved_matching_number3 =
l_no_mtch_rcpt_tbl (j + 2).invoice_number,
amount_applied_from3 = l_no_mtch_rcpt_tbl(j + 2).amount_applied_from,
trans_to_receipt_rate3 = l_no_mtch_rcpt_tbl(j + 2).trans_to_receipt_rate,
invoice4 = l_no_mtch_rcpt_tbl (j + 3).invoice_number,
amount_applied4 =
l_no_mtch_rcpt_tbl (j + 3).amount_applied,
resolved_matching_number4 =
l_no_mtch_rcpt_tbl (j + 3).invoice_number,
amount_applied_from4 = l_no_mtch_rcpt_tbl(j + 3).amount_applied_from,
trans_to_receipt_rate4 = l_no_mtch_rcpt_tbl(j + 3).trans_to_receipt_rate,
invoice5 = l_no_mtch_rcpt_tbl (j + 4).invoice_number,
amount_applied5 =
l_no_mtch_rcpt_tbl (j + 4).amount_applied,
resolved_matching_number5 =
l_no_mtch_rcpt_tbl (j + 4).invoice_number,
amount_applied_from5 = l_no_mtch_rcpt_tbl(j + 4).amount_applied_from,
trans_to_receipt_rate5 = l_no_mtch_rcpt_tbl(j + 4).trans_to_receipt_rate,
invoice6 = l_no_mtch_rcpt_tbl (j + 5).invoice_number,
amount_applied6 =
l_no_mtch_rcpt_tbl (j + 5).amount_applied,
resolved_matching_number6 =
l_no_mtch_rcpt_tbl (j + 5).invoice_number,
amount_applied_from6 = l_no_mtch_rcpt_tbl(j + 5).amount_applied_from,
trans_to_receipt_rate6 = l_no_mtch_rcpt_tbl(j + 5).trans_to_receipt_rate,
invoice7 = l_no_mtch_rcpt_tbl (j + 6).invoice_number,
amount_applied7 =
l_no_mtch_rcpt_tbl (j + 6).amount_applied,
resolved_matching_number7 =
l_no_mtch_rcpt_tbl (j + 6).invoice_number,
amount_applied_from7 = l_no_mtch_rcpt_tbl(j + 6).amount_applied_from,
trans_to_receipt_rate7 = l_no_mtch_rcpt_tbl(j + 6).trans_to_receipt_rate,
invoice8 = l_no_mtch_rcpt_tbl (j + 7).invoice_number,
amount_applied8 =
l_no_mtch_rcpt_tbl (j + 7).amount_applied,
resolved_matching_number8 =
l_no_mtch_rcpt_tbl (j + 7).invoice_number,
amount_applied_from8 = l_no_mtch_rcpt_tbl(j + 7).amount_applied_from,
trans_to_receipt_rate8 = l_no_mtch_rcpt_tbl(j + 7).trans_to_receipt_rate
WHERE transmission_record_id = l_transmission_record_id;
UPDATE ar_payments_interface_all
SET overflow_sequence = seq_num,
overflow_indicator = l_overflow_rec_indicator
WHERE transmission_record_id = l_transmission_record_id;
UPDATE ar_payments_interface_all
SET overflow_indicator = l_ovf_ind
WHERE transmission_record_id = l_last_transmission_record_id;
UPDATE ar_payments_interface_all
SET transmission_record_count = l_trans_rec_count
WHERE transmission_request_id = l_trans_req_id
AND record_type IN (l_transmission_hdr, l_transmission_trl);