[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select LOOKUP_CODE
from ar_lookups
where LOOKUP_TYPE = 'ARLPLB_MATCHING_OPTION'
order by decode(LOOKUP_CODE, 'INVOICE', 1,
'SALES_ORDER', 2,
'PURCHASE_ORDER', 3,
'CONSOLIDATE_BILL', 4,
100);
| l_customer_id with l_prev_customer_id. If they are same update the
| record in ar_payments_interface for returned trx_number, trx_date and
| installment number.
| If find_cust_and_trx_num returns matched_flag = FALSE, then rollback
| the updates for current item number.
| If all the customers are same for given item number,
| return p_out_customer_identified = 1, p_out_customer_id = identfied customer id.
| Else return p_out_customer_identified = 0, p_out_customer_id = NULL.
|
| Modification History
| 28-Jul-97 K Trivedi Created.
| 24-Sep-97 K Trivedi Modified to populate match_resolved_using
| column in ar_payments_interface.
----------------------------------------------------------------------------*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
select
rowid,
receipt_date,
invoice1, matching1_date, invoice1_installment,
invoice2, matching2_date, invoice2_installment,
invoice3, matching3_date, invoice3_installment,
invoice4, matching4_date, invoice4_installment,
invoice5, matching5_date, invoice5_installment,
invoice6, matching6_date, invoice6_installment,
invoice7, matching7_date, invoice7_installment,
invoice8, matching8_date, invoice8_installment
from ar_payments_interface pi
where pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
and pi.customer_id is null
and pi.status in ('AR_PLB_CONTROLS_OK','AR_PLB_NO_CUST')
and pi.item_number = l_item_num
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
UPDATE ar_payments_interface
SET resolved_matching_number1 = l_matching_number1,
resolved_matching1_installment = l_matching1_installment,
resolved_matching1_date = l_matching1_date,
invoice1_status = l_match1_status,
resolved_matching_number2 = l_matching_number2,
resolved_matching2_installment = l_matching2_installment,
resolved_matching2_date = l_matching2_date,
invoice2_status = l_match2_status,
resolved_matching_number3 = l_matching_number3,
resolved_matching3_installment = l_matching3_installment,
resolved_matching3_date = l_matching3_date,
invoice3_status = l_match3_status,
resolved_matching_number4 = l_matching_number4,
resolved_matching4_installment = l_matching4_installment,
resolved_matching4_date = l_matching4_date,
invoice4_status = l_match4_status,
resolved_matching_number5 = l_matching_number5,
resolved_matching5_installment = l_matching5_installment,
resolved_matching5_date = l_matching5_date,
invoice5_status = l_match5_status,
resolved_matching_number6 = l_matching_number6,
resolved_matching6_installment = l_matching6_installment,
resolved_matching6_date = l_matching6_date,
invoice6_status = l_match6_status,
resolved_matching_number7 = l_matching_number7,
resolved_matching7_installment = l_matching7_installment,
resolved_matching7_date = l_matching7_date,
invoice7_status = l_match7_status,
resolved_matching_number8 = l_matching_number8,
resolved_matching8_installment = l_matching8_installment,
resolved_matching8_date = l_matching8_date,
invoice8_status = l_match8_status,
match_resolved_using = l_matching_option
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE SET tmp_amt_applied1=amount_applied1,
tmp_amt_applied2=amount_applied2,
tmp_amt_applied3=amount_applied3,
tmp_amt_applied4=amount_applied4,
tmp_amt_applied5=amount_applied5,
tmp_amt_applied6=amount_applied6,
tmp_amt_applied7=amount_applied7,
tmp_amt_applied8=amount_applied8,
amount_applied1 = null,
amount_applied2= null,
amount_applied3= null,
amount_applied4= null,
amount_applied5= null,
amount_applied6= null,
amount_applied7= null,
amount_applied8= null
WHERE rowid = l_rowid and match_resolved_using='CONSOLIDATE_BILL';
UPDATE ar_payments_interface pi
SET resolved_matching_number1 = SUBSTRB(invoice1, 1, 20),
resolved_matching1_installment = invoice1_installment,
resolved_matching1_date = matching1_date,
invoice1_status = decode(invoice1, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number2 = SUBSTRB(invoice2, 1, 20),
resolved_matching2_installment = invoice2_installment,
resolved_matching2_date = matching2_date,
invoice2_status = decode(invoice2, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number3 = SUBSTRB(invoice3, 1, 20),
resolved_matching3_installment = invoice3_installment,
resolved_matching3_date = matching3_date,
invoice3_status = decode(invoice3, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number4 = SUBSTRB(invoice4, 1, 20),
resolved_matching4_installment = invoice4_installment,
resolved_matching4_date = matching4_date,
invoice4_status = decode(invoice4, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number5 = SUBSTRB(invoice5, 1, 20),
resolved_matching5_installment = invoice5_installment,
resolved_matching5_date = matching5_date,
invoice5_status = decode(invoice5, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number6 = SUBSTRB(invoice6, 1, 20),
resolved_matching6_installment = invoice6_installment,
resolved_matching6_date = matching6_date,
invoice6_status = decode(invoice6, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number7 = SUBSTRB(invoice7, 1, 20),
resolved_matching7_installment = invoice7_installment,
resolved_matching7_date = matching7_date,
invoice7_status = decode(invoice7, null, null, 'AR_PLB_INVALID_MATCH'),
resolved_matching_number8 = SUBSTRB(invoice8, 1, 20),
resolved_matching8_installment = invoice8_installment,
resolved_matching8_date = matching8_date,
invoice8_status = decode(invoice8, null, null, 'AR_PLB_INVALID_MATCH'),
match_resolved_using = null
where pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
and pi.customer_id is null
and pi.status = 'AR_PLB_CONTROLS_OK'
and pi.item_number = l_item_num
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
/* Bug 2106408. Added the following UPDATE */
<>
/* Bug2980051. Replaced l_only_one_lb with l_no_batch_or_lb */
UPDATE ar_payments_interface pi
SET resolved_matching_number1 = SUBSTRB(invoice1, 1, 20),
resolved_matching1_installment = invoice1_installment,
resolved_matching1_date = matching1_date,
invoice1_status = decode(invoice1, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number2 = SUBSTRB(invoice2, 1, 20),
resolved_matching2_installment = invoice2_installment,
resolved_matching2_date = matching2_date,
invoice2_status = decode(invoice2, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number3 = SUBSTRB(invoice3, 1, 20),
resolved_matching3_installment = invoice3_installment,
resolved_matching3_date = matching3_date,
invoice3_status = decode(invoice3, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number4 = SUBSTRB(invoice4, 1, 20),
resolved_matching4_installment = invoice4_installment,
resolved_matching4_date = matching4_date,
invoice4_status = decode(invoice4, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number5 = SUBSTRB(invoice5, 1, 20),
resolved_matching5_installment = invoice5_installment,
resolved_matching5_date = matching5_date,
invoice5_status = decode(invoice5, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number6 = SUBSTRB(invoice6, 1, 20),
resolved_matching6_installment = invoice6_installment,
resolved_matching6_date = matching6_date,
invoice6_status = decode(invoice6, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number7 = SUBSTRB(invoice7, 1, 20),
resolved_matching7_installment = invoice7_installment,
resolved_matching7_date = matching7_date,
invoice7_status = decode(invoice7, null, null, 'AR_PLB_DUP_INV'),
resolved_matching_number8 = SUBSTRB(invoice8, 1, 20),
resolved_matching8_installment = invoice8_installment,
resolved_matching8_date = matching8_date,
invoice8_status = decode(invoice8, null, null, 'AR_PLB_DUP_INV'),
match_resolved_using = null
where pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
and pi.customer_id is null
and pi.status = 'AR_PLB_CONTROLS_OK'
and pi.item_number = l_item_num
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
| Update record in ar_payments_interface for returned trx_number, trx_date and
| installment number.
|
| Modification History
| 30-Jul-97 K Trivedi Created. Rel 11 related changes.
| 24-Sep-97 K Trivedi Modified to populate match_resolved_using
| column in ar_payments_interface.
|
----------------------------------------------------------------------------*/
PROCEDURE populate_resolved_columns(
p_transmission_id IN VARCHAR2,
p_payment_rec_type IN VARCHAR2,
p_overflow_rec_type IN VARCHAR2,
p_item_num IN ar_payments_interface.item_number%type,
p_batch_name IN ar_payments_interface.batch_name%type,
p_lockbox_number IN ar_payments_interface.lockbox_number%type,
p_batches IN VARCHAR2,
p_only_one_lb IN VARCHAR2,
p_use_matching_date IN ar_lockboxes.use_matching_date%type,
p_lockbox_matching_option IN ar_lockboxes.lockbox_matching_option%type,
p_pay_unrelated_invoices IN VARCHAR2
) IS
--
l_transmission_id VARCHAR2(50);
select
rowid, customer_id,
receipt_date,
invoice1, matching1_date, invoice1_installment,
invoice2, matching2_date, invoice2_installment,
invoice3, matching3_date, invoice3_installment,
invoice4, matching4_date, invoice4_installment,
invoice5, matching5_date, invoice5_installment,
invoice6, matching6_date, invoice6_installment,
invoice7, matching7_date, invoice7_installment,
invoice8, matching8_date, invoice8_installment
from ar_payments_interface pi
where pi.transmission_id = l_transmission_id
and pi.record_type||'' in ( l_payment_rec_type, l_overflow_rec_type )
and pi.customer_id is NOT null
and pi.status in ('AR_PLB_CUST_OK', 'AR_PLB_MICR_OK')
and pi.item_number = l_item_num
and ( pi.batch_name = l_batch_name
or
( pi.lockbox_number = l_lockbox_number
and
l_batches = 'N'
)
or
l_no_batch_or_lb = 'Y'
);
UPDATE ar_payments_interface
SET resolved_matching_number1 = SUBSTRB(l_matching_number1, 1, 20),
resolved_matching1_installment = l_matching1_installment,
resolved_matching1_date = l_matching1_date,
invoice1_status = l_match1_status,
resolved_matching_number2 = SUBSTRB(l_matching_number2, 1, 20),
resolved_matching2_installment = l_matching2_installment,
resolved_matching2_date = l_matching2_date,
invoice2_status = l_match2_status,
resolved_matching_number3 = SUBSTRB(l_matching_number3, 1, 20),
resolved_matching3_installment = l_matching3_installment,
resolved_matching3_date = l_matching3_date,
invoice3_status = l_match3_status,
resolved_matching_number4 = SUBSTRB(l_matching_number4, 1, 20),
resolved_matching4_installment = l_matching4_installment,
resolved_matching4_date = l_matching4_date,
invoice4_status = l_match4_status,
resolved_matching_number5 = SUBSTRB(l_matching_number5, 1, 20),
resolved_matching5_installment = l_matching5_installment,
resolved_matching5_date = l_matching5_date,
invoice5_status = l_match5_status,
resolved_matching_number6 = SUBSTRB(l_matching_number6, 1, 20),
resolved_matching6_installment = l_matching6_installment,
resolved_matching6_date = l_matching6_date,
invoice6_status = l_match6_status,
resolved_matching_number7 = SUBSTRB(l_matching_number7, 1, 20),
resolved_matching7_installment = l_matching7_installment,
resolved_matching7_date = l_matching7_date,
invoice7_status = l_match7_status,
resolved_matching_number8 = SUBSTRB(l_matching_number8, 1, 20),
resolved_matching8_installment = l_matching8_installment,
resolved_matching8_date = l_matching8_date,
invoice8_status = l_match8_status,
match_resolved_using = l_matching_option
WHERE rowid = l_rowid;
UPDATE AR_PAYMENTS_INTERFACE SET tmp_amt_applied1=amount_applied1,
tmp_amt_applied2=amount_applied2,
tmp_amt_applied3=amount_applied3,
tmp_amt_applied4=amount_applied4,
tmp_amt_applied5=amount_applied5,
tmp_amt_applied6=amount_applied6,
tmp_amt_applied7=amount_applied7,
tmp_amt_applied8=amount_applied8,
amount_applied1 = null,
amount_applied2= null,
amount_applied3= null,
amount_applied4= null,
amount_applied5= null,
amount_applied6= null,
amount_applied7= null,
amount_applied8= null
WHERE rowid = l_rowid and match_resolved_using='CONSOLIDATE_BILL';
select LOOKUP_CODE
from ar_lookups
where LOOKUP_TYPE = 'ARLPLB_MATCHING_OPTION'
and LOOKUP_CODE = decode(p_matching_option, 'ALL', LOOKUP_CODE, p_matching_option)
order by decode(LOOKUP_CODE, 'INVOICE', 1,
'SALES_ORDER', 2,
'PURCHASE_ORDER', 3,
'CONSOLIDATE_BILL', 4,
100);
Invoice/PO/SO matching, trx_number is selected like max(trx_number).
Hence, Need to check l_current_customer_id to check the auto
association result.
*/
/* bug3252655
IF ((r_invoice_number1 IS NULL) AND
(p_matching_number1 IS NOT NULL))
*/
/*5052049 added -6666 along with -9999 in the if structure
for all l_current_customer_id 1 to 8*/
-- If the resolved matching number is -1111, it means that the invoice
-- is closed. So flag the invoice as 'Invalid Match'. Bug 7431540.
IF ((l_current_customer_id1 IN (-9999,-6666) OR r_invoice_number1 = '-1111') AND
(p_matching_number1 IS NOT NULL))
THEN
debug1('Invalid Match for 1st match num');
SELECT DISTINCT customer_trx_id
FROM ar_pmts_interface_header_gt
WHERE transmission_request_id = p_trans_request_id;
SELECT distinct apply_to
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_request_id
AND customer_trx_id = p_customer_trx_id;
SELECT distinct transmission_record_id
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_request_id
AND status = 'AR_PLB_NEW_RECORD';
SELECT distinct customer_trx_id
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_request_id
AND transmission_record_id = p_transmission_record_id;
SELECT apply_to,
line_amount,
tax,
amount_applied
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_req_id
AND transmission_record_id = p_transmission_record_id
AND customer_trx_id = p_customer_trx_id
AND apply_to NOT IN ('FREIGHT','CHARGES')
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_NEW_RECORD'
WHERE transmission_request_id = p_trans_req_id;
UPDATE ar_pmts_interface_line_details
SET amount_applied = decode(apply_to,'FREIGHT',freight,charges)
WHERE transmission_request_id = p_trans_req_id
AND apply_to IN ('FREIGHT', 'CHARGES')
AND amount_applied IS NULL
AND allocated_receipt_amount IS NULL;
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_INVALID_REC_ID'
WHERE transmission_record_id in ( SELECT transmission_record_id
FROM ar_payments_interface interface
WHERE interface.transmission_request_id = p_trans_req_id
AND interface.transmission_record_id = line_details.transmission_record_id
GROUP BY transmission_record_id
HAVING count(transmission_record_id) <> 1
)
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_INVALID_RECORD'
WHERE transmission_record_id in ( SELECT transmission_record_id
FROM ar_payments_interface interface
WHERE interface.transmission_request_id = p_trans_req_id
AND interface.transmission_record_id = line_details.transmission_record_id
AND status <> 'AR_PLB_APP_OK')
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_NEW_RECORD';
SELECT transmission_record_id
INTO l_trans_record_id
FROM ar_payments_interface
WHERE transmission_request_id = p_trans_req_id
AND rownum = 1;
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice1,
decode(format_amount_app1, 'Y',
round(amount_applied_from1/power(10,fc1.precision),fc1.precision),
amount_applied_from1),
decode(format_amount1, 'Y',
round(amount_applied1/power(10,fc.precision),fc.precision),
amount_applied1),
nvl(trans_to_receipt_rate1,1),
invoice_currency_code1,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice1 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code1,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice1);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice2,
decode(format_amount_app2, 'Y',
round(amount_applied_from2/power(10,fc1.precision),fc1.precision),
amount_applied_from2),
decode(format_amount2, 'Y',
round(amount_applied2/power(10,fc.precision),fc.precision),
amount_applied2),
nvl(trans_to_receipt_rate2,1),
invoice_currency_code2,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice2 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code2,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice2);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice3,
decode(format_amount_app3, 'Y',
round(amount_applied_from3/power(10,fc1.precision),fc1.precision),
amount_applied_from3),
decode(format_amount3, 'Y',
round(amount_applied3/power(10,fc.precision),fc.precision),
amount_applied3),
nvl(trans_to_receipt_rate3,1),
invoice_currency_code3,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice3 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code3,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice3);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice4,
decode(format_amount_app4, 'Y',
round(amount_applied_from4/power(10,fc1.precision),fc1.precision),
amount_applied_from4),
decode(format_amount4, 'Y',
round(amount_applied4/power(10,fc.precision),fc.precision),
amount_applied4),
nvl(trans_to_receipt_rate4,1),
invoice_currency_code4,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice4 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code4,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice4);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice5,
decode(format_amount_app5, 'Y',
round(amount_applied_from5/power(10,fc1.precision),fc1.precision),
amount_applied_from5),
decode(format_amount5, 'Y',
round(amount_applied5/power(10,fc.precision),fc.precision),
amount_applied5),
nvl(trans_to_receipt_rate5,1),
invoice_currency_code5,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice5 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code5,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice5);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice6,
decode(format_amount_app6, 'Y',
round(amount_applied_from6/power(10,fc1.precision),fc1.precision),
amount_applied_from6),
decode(format_amount6, 'Y',
round(amount_applied6/power(10,fc.precision),fc.precision),
amount_applied6),
nvl(trans_to_receipt_rate6,1),
invoice_currency_code6,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice6 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code6,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice6);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice7,
decode(format_amount_app7, 'Y',
round(amount_applied_from7/power(10,fc1.precision),fc1.precision),
amount_applied_from7),
decode(format_amount7, 'Y',
round(amount_applied7/power(10,fc.precision),fc.precision),
amount_applied7),
nvl(trans_to_receipt_rate7,1),
invoice_currency_code7,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice7 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code7,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice7);
INSERT INTO ar_pmts_interface_header_gt
(transmission_request_id,
transmission_record_id,
currency_code,
invoice_number,
amount_applied_from,
amount_applied,
trans_to_receipt_rate,
invoice_currency_code,
record_status )
SELECT transmission_request_id,
transmission_record_id,
interface.currency_code,
invoice8,
decode(format_amount_app8, 'Y',
round(amount_applied_from8/power(10,fc1.precision),fc1.precision),
amount_applied_from8),
decode(format_amount8, 'Y',
round(amount_applied8/power(10,fc.precision),fc.precision),
amount_applied8),
nvl(trans_to_receipt_rate8,1),
invoice_currency_code8,
status
FROM ar_payments_interface interface,
fnd_currencies fc,
fnd_currencies fc1
WHERE invoice8 is NOT NULL
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_APP_OK'
AND fc.currency_code = interface.currency_code
AND fc1.currency_code = nvl(interface.invoice_currency_code8,
interface.currency_code)
AND EXISTS ( SELECT 'X'
FROM ar_pmts_interface_line_details line_details
WHERE line_details.transmission_record_id = interface.transmission_record_id
AND line_details.invoice_number = interface.invoice8);
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_DUP_INVOICE'
WHERE invoice_number IN ( SELECT invoice_number
FROM ar_pmts_interface_header_gt
WHERE transmission_record_id = line_details.transmission_record_id
GROUP BY invoice_number
HAVING count(invoice_number) > 1 )
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_DUP_FRGT_CHRG'
WHERE (transmission_record_id, invoice_number, apply_to)
IN (SELECT transmission_record_id, invoice_number, apply_to
FROM ar_pmts_interface_line_details ld
WHERE ld.transmission_record_id = line_details.transmission_record_id
AND ld.invoice_number = line_details.invoice_number
AND ld.apply_to = line_details.apply_to
AND ld.transmission_request_id = p_trans_req_id
GROUP BY transmission_record_id, invoice_number, apply_to
HAVING count(*) > 1)
AND transmission_request_id = p_trans_req_id
AND apply_to IN ('FREIGHT', 'CHARGES')
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_NO_APP_INFO'
WHERE transmission_request_id = p_trans_req_id
AND amount_applied IS NULL
AND line_amount IS NULL
AND allocated_receipt_amount IS NULL
AND status = 'AR_PLB_NEW_RECORD';
/*UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_LINE_TAX_TOT_MISMATCH'
WHERE transmission_request_id = p_trans_req_id
AND amount_applied IS NOT NULL
AND line_amount IS NOT NULL
AND tax_amount IS NOT NULL
AND amount_applied <> line_amount + tax_amount
AND status = 'AR_PLB_NEW_RECORD'; */
UPDATE ar_pmts_interface_header_gt gt
SET (customer_trx_id) = ( SELECT customer_trx_id
FROM ra_customer_trx
WHERE trx_number = gt.invoice_number ) ;
UPDATE ar_pmts_interface_line_details ld
SET customer_trx_id = (SELECT customer_trx_id
FROM ar_pmts_interface_header_gt
WHERE invoice_number = ld.invoice_number
AND transmission_record_id = ld.transmission_record_id
)
WHERE transmission_request_id = p_trans_req_id;
/*UPDATE ar_pmts_interface_header_gt gt
SET precision = get_currency_precision(gt.currency_code),
inv_precision = get_currency_precision(NVL(gt.inv_currency_code, gt.currency_code));*/
UPDATE ar_pmts_interface_line_details line_details
SET amount_applied = allocated_receipt_amount
WHERE amount_applied IS NULL
AND allocated_receipt_amount IS NOT NULL
AND invoice_number IN ( SELECT invoice_number
FROM ar_pmts_interface_header_gt header
WHERE header.transmission_record_id = line_details.transmission_record_id
AND header.invoice_number = line_details.invoice_number
AND header.currency_code = header.invoice_currency_code
AND transmission_request_id = p_trans_req_id)
AND transmission_request_id = p_trans_req_id
AND status = 'AR_PLB_NEW_RECORD';
SELECT amount_due_original
INTO line_amt_due_original
FROM ra_customer_trx_lines TL,ra_customer_trx T
WHERE T.customer_trx_id = TL.customer_trx_id
AND T.customer_trx_id = cur_var.customer_trx_id
AND TL.line_type = 'LINE'
AND rownum = 1;
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_BAL_STAMP_FAILED'
WHERE transmission_request_id = p_trans_req_id
AND invoice_number = ( SELECT invoice_number
FROM ar_pmts_interface_header_gt
WHERE transmission_request_id = p_trans_req_id
AND customer_trx_id = cur_var.customer_trx_id);
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_INVALID_LINE_NUM'
WHERE transmission_request_id = p_trans_req_id
AND apply_to NOT in ('FREIGHT','CHARGES')
AND NOT EXISTS ( select 'x'
FROM ra_customer_trx trx,
ra_customer_trx_lines lines,
ar_pmts_interface_header_gt header
WHERE trx.customer_trx_id = lines.customer_trx_id
AND header.invoice_number = line_details.invoice_number
AND header.transmission_record_id = line_details.transmission_record_id
AND trx.customer_trx_id = header.customer_trx_id
AND lines.line_type = 'LINE'
AND lines.line_number = line_details.apply_to );
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_INVALID_LINE_NUM'
WHERE transmission_request_id = p_trans_req_id
AND apply_to IN ('FREIGHT', 'CHARGES')
AND NOT EXISTS ( select 'x'
FROM ra_customer_trx trx,
ra_customer_trx_lines lines,
ar_pmts_interface_header_gt header
WHERE trx.customer_trx_id = lines.customer_trx_id
AND header.invoice_number = line_details.invoice_number
AND header.transmission_record_id = line_details.transmission_record_id
AND trx.customer_trx_id = header.customer_trx_id
AND lines.line_type = line_details.apply_to );
SELECT invoice_number,
trans_to_receipt_rate,
invoice_currency_code,
currency_code
INTO l_invoice_number,
l_trans_to_receipt_rate,
l_inv_currency_code,
l_currency_code
FROM ar_pmts_interface_header_gt
WHERE transmission_request_id = p_trans_req_id
AND customer_trx_id = cur_var.customer_trx_id
AND rownum = 1;
INSERT INTO ar_pmts_interface_lines_gt(
transmission_request_id,
customer_trx_id,
currency_code,
invoice_number,
invoice_currency_code,
trans_to_receipt_rate,
apply_to
)VALUES(
p_trans_req_id,
cur_var.customer_trx_id,
l_currency_code,
l_invoice_number,
l_inv_currency_code,
l_trans_to_receipt_rate,
cur_var1.apply_to
);
UPDATE ar_pmts_interface_lines_gt lines
SET line_amt_remaining
= (select sum(nvl(TL.amount_due_remaining,0))
from ra_customer_trx_lines TL
where TL.customer_trx_id = lines.customer_trx_id
and TL.line_number = lines.apply_to
and TL.line_type = 'LINE'
)
WHERE lines.apply_to NOT IN ('FREIGHT', 'CHARGES');
UPDATE ar_pmts_interface_lines_gt lines
SET tax_remaining
= (select sum(nvl(TL.amount_due_remaining,0))
from ra_customer_trx_lines TL
where TL.link_to_cust_trx_line_id = (
select l.customer_trx_line_id
from ra_customer_trx_lines l
where l.customer_trx_id = lines.customer_trx_id
and l.line_type = 'LINE'
and l.line_number = lines.apply_to)
and TL.line_type = 'TAX'
)
WHERE lines.apply_to NOT IN ('FREIGHT', 'CHARGES');
UPDATE ar_pmts_interface_lines_gt lines
SET freight_remaining
= ( SELECT sum(nvl(TL.amount_due_remaining,0))
FROM ra_customer_trx_lines TL
WHERE TL.customer_trx_id = lines.customer_trx_id
AND TL.line_type = lines.apply_to)
WHERE lines.apply_to = 'FREIGHT';
UPDATE ar_pmts_interface_lines_gt lines
SET charges_remaining
= ( SELECT sum(nvl(TL.amount_due_remaining,0))
FROM ra_customer_trx_lines TL
WHERE TL.customer_trx_id = lines.customer_trx_id
AND TL.line_type = lines.apply_to)
WHERE lines.apply_to = 'CHARGES';
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_INVALID_APP'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
(SELECT customer_trx_id, apply_to
FROM ar_pmts_interface_lines_gt
WHERE nvl(line_amt_remaining, 0) = 0
AND nvl(tax_remaining, 0) = 0
AND apply_to NOT IN ('FREIGHT', 'CHARGES'));
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_INVALID_APP'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
(SELECT customer_trx_id, apply_to
FROM ar_pmts_interface_lines_gt
WHERE freight_remaining = 0
AND apply_to = 'FREIGHT');
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_INVALID_APP'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
(SELECT customer_trx_id, apply_to
FROM ar_pmts_interface_lines_gt
WHERE charges_remaining = 0
AND apply_to = 'CHARGES' );
UPDATE ar_pmts_interface_header_gt header
SET default_by = 'LINE_AMT'
WHERE transmission_request_id = p_trans_req_id
AND invoice_number IN (SELECT invoice_number
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_req_id
AND transmission_record_id = header.transmission_record_id
AND allocated_receipt_amount IS NULL
AND line_amount IS NOT NULL);
SELECT invoice_currency_code,
trans_to_receipt_rate,
currency_code,
invoice_number
INTO l_inv_currency_code,
l_trans_to_receipt_rate,
l_currency_code,
l_invoice_number
FROM ar_pmts_interface_header_gt
WHERE customer_trx_id = cur_var1.customer_trx_id
AND transmission_request_id = p_trans_req_id
AND rownum = 1;
SELECT amount_applied, amount_applied_from
INTO hdr_amt_app, hdr_amt_app_frm
FROM ar_pmts_interface_header_gt
WHERE transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND transmission_request_id = p_trans_req_id;
SELECT SUM(DECODE(apply_to,'FREIGHT',0, 'CHARGES',0,1)) line_app_count,
count(*) tot_app_count
INTO line_app_count, tot_app_count
FROM ar_pmts_interface_line_details
WHERE transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND transmission_request_id = p_trans_req_id ;
UPDATE ar_pmts_interface_line_details
SET amount_applied = ARPCURR.CurrRound( allocated_receipt_amount/l_trans_to_receipt_rate,
l_currency_code),
line_amount = NULL,
tax = NULL
WHERE allocated_receipt_amount IS NOT NULL
AND amount_applied IS NULL
AND transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number;
UPDATE ar_pmts_interface_header_gt
SET default_by = 'AMT_APP_FRM'
WHERE transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number;
SELECT line_amt_remaining,
tax_remaining
INTO l_line_amount_remaining,
l_line_tax_remaining
FROM ar_pmts_interface_lines_gt
WHERE customer_trx_id = cur_var1.customer_trx_id
AND transmission_request_id = p_trans_req_id
AND apply_to = cur_var2.apply_to;
UPDATE ar_pmts_interface_line_details
SET amount_applied = l_calc_tot_amount_app,
line_amount = l_calc_line_amount,
tax = l_calc_tax_amount
WHERE transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND apply_to = cur_var2.apply_to
AND apply_to NOT IN ('FREIGHT','CHARGES');
UPDATE ar_pmts_interface_line_details line_details
SET allocated_receipt_amount
= ARPCURR.CurrRound( amount_applied * l_trans_to_receipt_rate,
l_inv_currency_code)
WHERE amount_applied IS NOT NULL
AND allocated_receipt_amount IS NULL
AND transmission_request_id = p_trans_req_id
AND transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number;
UPDATE ar_pmts_interface_header_gt
SET default_by = 'AMT_APP'
WHERE transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND default_by <> 'LINE_AMT';
SELECT SUM(amount_applied), SUM(allocated_receipt_amount)
INTO l_tot_amt_app, l_tot_amt_app_from
FROM ar_pmts_interface_line_details
WHERE transmission_request_id = p_trans_req_id
AND transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number;
UPDATE ar_pmts_interface_line_details
SET amount_applied = amount_applied + (hdr_amt_app - l_tot_amt_app )
WHERE transmission_request_id = p_trans_req_id
AND transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND rownum = 1;
UPDATE ar_pmts_interface_line_details
SET allocated_receipt_amount = allocated_receipt_amount + (hdr_amt_app_frm - l_tot_amt_app_from )
WHERE transmission_request_id = p_trans_req_id
AND transmission_record_id = cur_var.transmission_record_id
AND invoice_number = l_invoice_number
AND rownum = 1;
UPDATE ar_pmts_interface_line_details ld
SET status = 'AR_PLB_EXCEED_LINE_AMT'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
( select lines_gt.customer_trx_id, lines_gt.apply_to
from ar_pmts_interface_lines_gt ld1,
( select customer_trx_id,
apply_to,
sum(lines.line_amount) tot_amt
from ar_pmts_interface_line_details lines
where lines.transmission_request_id = p_trans_req_id
group by customer_trx_id, apply_to ) lines_gt
where lines_gt.customer_trx_id = ld1.customer_trx_id
and lines_gt.apply_to = ld1.apply_to
and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
and ld1.line_amt_remaining < lines_gt.tot_amt)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details ld
SET status = 'AR_PLB_EXCEED_TAX_AMT'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
( select lines_gt.customer_trx_id, lines_gt.apply_to
from ar_pmts_interface_lines_gt ld1,
( select customer_trx_id,
apply_to,
sum(lines.tax) tot_amt
from ar_pmts_interface_line_details lines
where lines.transmission_request_id = p_trans_req_id
group by customer_trx_id, apply_to ) lines_gt
where lines_gt.customer_trx_id = ld1.customer_trx_id
and lines_gt.apply_to = ld1.apply_to
and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
and ld1.tax_remaining < lines_gt.tot_amt)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details ld
SET status = 'AR_PLB_EXCEED_FRGT_AMT'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
( select ld1.customer_trx_id, ld1.apply_to
from ar_pmts_interface_lines_gt ld1,
ar_pmts_interface_line_details ld2
where ld1.customer_trx_id = ld2.customer_trx_id
and ld1.apply_to = ld2.apply_to
and ld2.apply_to = 'FREIGHT'
and ld2.transmission_request_id = p_trans_req_id
and ld1.freight_remaining < ld2.amount_applied)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details ld
SET status = 'AR_PLB_EXCEED_CHRG_AMT'
WHERE transmission_request_id = p_trans_req_id
AND (customer_trx_id, apply_to) IN
( select ld1.customer_trx_id, ld1.apply_to
from ar_pmts_interface_lines_gt ld1,
ar_pmts_interface_line_details ld2
where ld1.customer_trx_id = ld2.customer_trx_id
and ld1.apply_to = ld2.apply_to
and ld2.transmission_request_id = p_trans_req_id
and ld2.apply_to = 'CHARGES'
and ld1.charges_remaining < ld2.amount_applied)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_AMT_APP_INVALID'
WHERE transmission_request_id = p_trans_req_id
AND (transmission_record_id, customer_trx_id) IN
(select header.transmission_record_id, header.customer_trx_id
from ar_pmts_interface_header_gt header,
(select transmission_record_id,
customer_trx_id,
sum(amount_applied) aa
from ar_pmts_interface_line_details ld
where ld.transmission_request_id = p_trans_req_id
group by transmission_record_id, customer_trx_id) line
where header.transmission_record_id = line.transmission_record_id
and header.customer_trx_id = line.customer_trx_id
and header.transmission_request_id = p_trans_req_id
and header.amount_applied <> line.aa)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details line_details
SET status = 'AR_PLB_AMT_APP_FRM_INVALID'
WHERE transmission_request_id = p_trans_req_id
AND (transmission_record_id, customer_trx_id) IN
(select header.transmission_record_id, header.customer_trx_id
from ar_pmts_interface_header_gt header,
(select transmission_record_id,
customer_trx_id,
sum(allocated_receipt_amount) aa
from ar_pmts_interface_line_details ld
where ld.transmission_request_id = p_trans_req_id
group by transmission_record_id, customer_trx_id) line
where header.transmission_record_id = line.transmission_record_id
and header.customer_trx_id = line.customer_trx_id
and header.transmission_request_id = p_trans_req_id
and header.amount_applied_from <> line.aa)
AND status = 'AR_PLB_NEW_RECORD';
UPDATE ar_pmts_interface_line_details
SET status = 'AR_PLB_LINE_OK'
WHERE status = 'AR_PLB_NEW_RECORD'
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number1 = NULL
WHERE (transmission_record_id, resolved_matching_number1)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number2 = NULL
WHERE (transmission_record_id, resolved_matching_number2)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number3 = NULL
WHERE (transmission_record_id, resolved_matching_number3)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number4 = NULL
WHERE (transmission_record_id, resolved_matching_number4)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number5 = NULL
WHERE (transmission_record_id, resolved_matching_number5)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number6 = NULL
WHERE (transmission_record_id, resolved_matching_number6)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number7 = NULL
WHERE (transmission_record_id, resolved_matching_number7)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET resolved_matching_number8 = NULL
WHERE (transmission_record_id, resolved_matching_number8)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice1_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number1)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice2_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number2)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice3_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number3)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice4_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number4)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice5_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number5)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice6_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number6)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice7_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number7)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET invoice8_status = 'AR_PLB_INVALID_LINE_DET'
WHERE (transmission_record_id, resolved_matching_number8)
IN (SELECT transmission_record_id, invoice_number
FROM ar_pmts_interface_line_details line_details
WHERE transmission_request_id = p_trans_req_id
AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
AND transmission_request_id = p_trans_req_id;
UPDATE ar_payments_interface interface
SET status = 'AR_PLB_INVALID_RECEIPT'
WHERE (item_number, nvl(batch_name, -1))
IN (SELECT item_number, nvl(batch_name, -1)
FROM ar_payments_interface interface1
WHERE interface1.transmission_request_id = p_trans_req_id
AND ( interface1.invoice1_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice2_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice3_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice4_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice5_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice6_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice7_status = 'AR_PLB_INVALID_LINE_DET'
OR interface1.invoice8_status = 'AR_PLB_INVALID_LINE_DET'))
AND transmission_request_id = p_trans_req_id;
select amount_format_lookup_code
into l_format_yn
from
AR_TRANS_FIELD_FORMATS FF,
AR_TRANS_RECORD_FORMATS RF,
AR_PAYMENTS_INTERFACE_all PI,
AR_TRANSMISSIONS_all TR
where TR.transmission_request_id = p_trans_req_id
and PI.transmission_record_id = p_trans_rec_id
and RF.transmission_format_id = TR.requested_trans_format_id
and RF.record_identifier = PI.record_type
and FF.transmission_format_id = RF.transmission_format_id
and FF.record_format_id = RF.record_format_id
and field_type_lookup_code= p_column_type;
SELECT precision
INTO l_precision
FROM fnd_currencies
WHERE currency_code = p_currency_code ;
| insert_interim_line_details |
| |
| DESCRIPTION |
| insert records into AR_INTERIM_CASH_LINE_DETAILS |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
| |
| ARGUMENTS : IN: |
| |
| MODIFICATION HISTORY : |
| |
| 28-Jul-07 vpusulur Created. |
+===========================================================================*/
PROCEDURE insert_interim_line_details(
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%type,
p_cash_receipt_line_id IN NUMBER,
p_trans_req_id IN ar_payments_interface.transmission_request_id%type,
p_batch_name IN ar_payments_interface.batch_name%type,
p_item_num IN ar_payments_interface.item_number%type,
p_return_status OUT NOCOPY varchar2
)
IS
status_count number := 0;
select status,
invoice_number,
apply_to,
allocated_receipt_amount,
amount_applied,
line_amount,
tax,
freight,
charges,
line_discount,
tax_discount,
freight_discount,
comments,
transmission_record_id
from ar_pmts_interface_line_details
where transmission_request_id = req_id
and invoice_number = inv_num
and transmission_record_id in (
select overflow.transmission_record_id
from ar_payments_interface payment, ar_payments_interface overflow,
ar_trans_record_formats format, ar_transmissions transmissions,
ar_interim_cash_receipts cash
where payment.check_number = cash.receipt_number
and payment.transmission_request_id = overflow.transmission_request_id
and payment.item_number = overflow.item_number
and nvl(payment.batch_name, -1) = nvl(overflow.batch_name, -1)
and overflow.record_type = format.record_identifier
and transmissions.requested_trans_format_id = format.transmission_format_id
AND transmissions.transmission_request_id = payment.transmission_request_id
AND format.record_type_lookup_code = 'OVRFLW PAYMENT'
and cash.cash_receipt_id = receipt_id
and payment.transmission_request_id = req_id
and payment.item_number = l_item_number
and nvl(payment.batch_name, -1) = nvl(l_batch_name, -1) ) ;
select customer_trx_line_id,
amount_due_remaining
from ra_customer_trx_lines
where customer_trx_id = ct_id
and line_type = app_to;
debug1('insert_interim_line_details()+ ' ||p_cash_receipt_line_id );
select trx_number
into inv_number
from ra_customer_trx
where customer_trx_id = p_customer_trx_id;
select currency_code
into l_currency_code
from ar_interim_cash_receipts
where cash_receipt_id = p_cash_receipt_id;
select count(distinct status)
into status_count
from ar_pmts_interface_line_details
where transmission_request_id = p_trans_req_id
and invoice_number = inv_number;
select count(distinct status)
into status_count
from ar_pmts_interface_line_details
where transmission_request_id = p_trans_req_id
and invoice_number = inv_number;
Select ar_activity_details_s.nextval
INTO l_line_id
from dual;
insert into AR_INTERIM_CASH_LINE_DETAILS(
cash_receipt_id,
customer_trx_line_id,
source_id,
source_table,
allocated_receipt_amount,
amount,
tax,
freight,
charges,
last_update_date,
last_updated_by,
line_discount,
tax_discount,
freight_discount,
line_balance,
tax_balance,
creation_date,
created_by,
last_update_login,
comments,
apply_to,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
reference1,
reference2,
reference3,
reference4,
reference5,
group_id,
object_version_number,
created_by_module,
line_id)
select
p_cash_receipt_id,
l.customer_trx_line_id,
null,
null,
nvl(det_line.allocated_receipt_amount,0),
nvl(det_line.line_amount,0),
nvl(det_line.tax,0),
nvl(det_line.freight,0),
nvl(det_line.charges,0),
sysdate,
null,
nvl(det_line.line_discount,0),
nvl(det_line.tax_discount,0),
nvl(det_line.freight_discount,0),
null,
null,
sysdate,
null,
null,
det_line.comments,
det_line.apply_to,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
1,
'ARLPLB',
l_line_id
from ra_customer_trx_lines l
where l.customer_trx_id = p_customer_trx_id
and l.line_type = 'LINE'
and l.line_number = det_line.apply_to;
Select sum(nvl(amount_due_remaining,0))
into tot_frgt_chrg_amt
from ra_customer_trx_lines
where customer_trx_id = p_customer_trx_id
and line_type = det_line.apply_to;
SELECT invoice_currency_code,
trans_to_receipt_rate
INTO l_inv_currency_code,
l_trans_to_receipt_rate
FROM ar_pmts_interface_header_gt
WHERE customer_trx_id = p_customer_trx_id
AND transmission_request_id = p_trans_req_id
AND rownum = 1;
Select ar_activity_details_s.nextval
INTO l_line_id
from dual;
insert into AR_INTERIM_CASH_LINE_DETAILS(
cash_receipt_id,
customer_trx_line_id,
source_id,
source_table,
allocated_receipt_amount,
amount,
tax,
freight,
charges,
last_update_date,
last_updated_by,
line_discount,
tax_discount,
freight_discount,
line_balance,
tax_balance,
creation_date,
created_by,
last_update_login,
comments,
apply_to,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
reference1,
reference2,
reference3,
reference4,
reference5,
group_id,
object_version_number,
created_by_module,
line_id)
values(
p_cash_receipt_id,
line.customer_trx_line_id,
null,
null,
prorated_frgt_chrg_amt_frm,
nvl(det_line.line_amount,0),
nvl(det_line.tax,0),
decode(det_line.apply_to,'FREIGHT',prorated_frgt_chrg_amt,0),
decode(det_line.apply_to,'CHARGES',prorated_frgt_chrg_amt,0),
sysdate,
null,
nvl(det_line.line_discount,0),
nvl(det_line.tax_discount,0),
decode(det_line.apply_to,'FREIGHT',
nvl(det_line.freight_discount,0)*(nvl(line.amount_due_remaining,0)/
tot_frgt_chrg_amt),0),
null,
null,
sysdate,
null,
null,
det_line.comments,
det_line.apply_to,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
1,
'ARLPLB',
l_line_id);
delete from ar_pmts_interface_line_details
where transmission_request_id = p_trans_req_id
and invoice_number = inv_number
and transmission_record_id = det_line.transmission_record_id
and status = 'AR_PLB_LINE_OK';
debug1('insert_interim_line_details()-');
debug1('update_statuses : p_return_status ' || p_return_status);
END insert_interim_line_details;
/* Constructing the SELECT clause */
g_cursor_string := 'select ';
' and EXISTS ( select ''Exists dual'' from dual ' ||
' where decode(:b_pay_unrelated_customers, ''Y'', ps.customer_id, ' ||
' nvl(:b_customer_id, ps.customer_id)) = ps.customer_id ' ||
' UNION ' ||
' select ''Exists ha_cust_acct_relate'' ' ||
' from hz_cust_acct_relate rel ' ||
' where rel.cust_account_id = :b_customer_id ' ||
' and rel.bill_to_flag = ''Y'' ' ||
' and rel.status = ''A'' ' ||
' UNION ' ||
' select ''Exists ar_paying_relationships_v'' ' ||
' from ar_paying_relationships_v rel,' ||
' hz_cust_accounts acc ' ||
' where acc.cust_account_id = :b_customer_id ' ||
' and acc.party_id = rel.party_id ' ||
' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
' AND effective_end_date ) ';
' (select decode( min( decode(ps.status, ''CL'', 99999999999999999999, ps.payment_schedule_id)), '||
' 99999999999999999999, min(ps.payment_schedule_id), '||
' min( decode(ps.status, ''CL'', 99999999999999999999, ps.payment_schedule_id))) ' ||
' from ar_payment_schedules ps '
/* ' ra_cust_trx_types tt ' || */;
' (select min(ps.payment_schedule_id) ' ||
' from ar_payment_schedules ps, ' ||
' ra_cust_trx_types tt ';
' and EXISTS ( select ''Exists dual'' from dual ' ||
' where decode(:b_pay_unrelated_customers, ''Y'', ps.customer_id, ' ||
' nvl(:b_customer_id, ps.customer_id)) = ps.customer_id ' ||
' UNION ' ||
' select ''Exists ha_cust_acct_relate'' ' ||
' from hz_cust_acct_relate rel ' ||
' where rel.cust_account_id = :b_customer_id ' ||
' and rel.bill_to_flag = ''Y'' ' ||
' and rel.status = ''A'' ' ||
' UNION ' ||
' select ''Exists ar_paying_relationships_v'' ' ||
' from ar_paying_relationships_v rel,' ||
' hz_cust_accounts acc ' ||
' where acc.cust_account_id = :b_customer_id ' ||
' and acc.party_id = rel.party_id ' ||
' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
' AND effective_end_date ) ';
/* Constructing SELECT CLAUSE */
g_cursor_string := 'select ';
' and EXISTS ( select ''Exists dual'' from dual ' ||
' where decode(:b_pay_unrelated_customers, ''Y'', rct.bill_to_customer_id, ' ||
' nvl(:b_customer_id, rct.bill_to_customer_id)) = rct.bill_to_customer_id ' ||
' UNION ' ||
' select ''Exists ha_cust_acct_relate'' ' ||
' from hz_cust_acct_relate rel ' ||
' where rel.cust_account_id = :b_customer_id ' ||
' and rel.bill_to_flag = ''Y'' ' ||
' and rel.status = ''A'' ' ||
' UNION ' ||
' select ''Exists ar_paying_relationships_v'' ' ||
' from ar_paying_relationships_v rel,' ||
' hz_cust_accounts acc ' ||
' where acc.cust_account_id = :b_customer_id ' ||
' and acc.party_id = rel.party_id ' ||
' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
' AND effective_end_date ) ' ||
' and rctt.accounting_affect_flag = ''Y'' ' ||
' and rctl.customer_trx_id = rct.customer_trx_id ' ||
' and rct.cust_trx_type_id = rctt.cust_trx_type_id ' ||
' and rctt.type not in (''PMT'', ''GUAR'') ' ||
' and rct.complete_flag = ''Y'' ' ||
' and rct.bill_to_customer_id = cpc.cust_account_id ' ||
' and cpc.site_use_id is NULL ' ||
' and rct.bill_to_site_use_id = cps.site_use_id (+) ' ||
' and rct.customer_trx_id = ps.customer_trx_id ' ;
g_cursor_string := 'select ';
' and EXISTS ( select ''Exists dual'' from dual ' ||
' where decode(:b_pay_unrelated_customers, ''Y'', rct.bill_to_customer_id, ' ||
' nvl(:b_customer_id, rct.bill_to_customer_id)) = rct.bill_to_customer_id ' ||
' UNION ' ||
' select ''Exists ha_cust_acct_relate'' ' ||
' from hz_cust_acct_relate rel ' ||
' where rel.cust_account_id = :b_customer_id ' ||
' and rel.bill_to_flag = ''Y'' ' ||
' and rel.status = ''A'' ' ||
' UNION ' ||
' select ''Exists ar_paying_relationships_v'' ' ||
' from ar_paying_relationships_v rel,' ||
' hz_cust_accounts acc ' ||
' where acc.cust_account_id = :b_customer_id ' ||
' and acc.party_id = rel.party_id ' ||
' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
' AND effective_end_date ) ' ||
' and rctt.accounting_affect_flag = ''Y'' ' ||
' and rct.cust_trx_type_id = rctt.cust_trx_type_id ' ||
' and rctt.type not in (''PMT'', ''GUAR'') ' ||
' and rct.complete_flag = ''Y'' ' ||
' and rct.bill_to_customer_id = cpc.cust_account_id ' ||
' and cpc.site_use_id is NULL ' ||
' and rct.bill_to_site_use_id = cps.site_use_id (+) ' ||
' and rct.customer_trx_id = ps.customer_trx_id ';
g_cursor_string := 'select ';
' and EXISTS ( select ''Exists dual'' from dual ' ||
' where decode(:b_pay_unrelated_customers, ''Y'', ci.customer_id, ' ||
' nvl(:b_customer_id, ci.customer_id)) = ci.customer_id ' ||
' UNION ' ||
' select ''Exists ha_cust_acct_relate'' ' ||
' from hz_cust_acct_relate rel ' ||
' where rel.cust_account_id = :b_customer_id ' ||
' and rel.bill_to_flag = ''Y'' ' ||
' and rel.status = ''A'' ' ||
' UNION ' ||
' select ''Exists ar_paying_relationships_v'' ' ||
' from ar_paying_relationships_v rel,' ||
' hz_cust_accounts acc ' ||
' where acc.cust_account_id = :b_customer_id ' ||
' and acc.party_id = rel.party_id ' ||
' and to_date(:b_receipt_date,''YYYYMMDD'') BETWEEN effective_start_date ' ||
' AND effective_end_date ) ' ||
' and ci.customer_id = cpc.cust_account_id ' ||
' and cpc.site_use_id is NULL ' ||
' and ci.site_use_id = cps.site_use_id (+) ' ||
' and nvl(cps.lockbox_matching_option, ' ||
' nvl(cpc.lockbox_matching_option, :b_lockbox_matching_option)) = ''CONSOLIDATE_BILL'' ' ||
' and trunc(ci.issue_date) ' ||
' = decode(:b_use_matching_date, ''ALWAYS'', :b_current_matching_date, trunc(ci.issue_date)) ';
debug1('Inserting values into PL/SQL Table for index ' || to_char(g_total_maching_options));