[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
If out_insert_records is returned as 'Y', the first validation will
insert the records into ar_interim_cash_receipt and receipt_line.
In non-custom mode, this parameter returns 'Y', because we do not call
validation second time. However, if you are planning to call the second
validation, for customising lockbox, assign this variable as 'N'.
----------------------------------------------------------------------------*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
out_insert_records OUT NOCOPY VARCHAR2) IS
--
l_okl_flag varchar2(1) := 'N';
pvt_insert_records varchar2(1);
pub_insert_records varchar2(1);
select nvl(a.LINE_LEVEL_CASH_APP_RULE,'N') into l_line_level_cash_app_rule
from ar_lockboxes_all a, ar_transmissions_all b
where b.transmission_request_id = in_trans_req_id
and a.lockbox_id = b.requested_lockbox_id;
UPDATE ar_payments_interface pi
SET pi.lockbox_number =
(SELECT decode(ff.justification_lookup_code,
'LEFT', RTRIM(pi.lockbox_number, decode(ff.fill_character_lookup_code, 'ZERO', '0', 'BLANK', ' ')),
'RIGHT', LTRIM(pi.lockbox_number,decode(ff.fill_character_lookup_code, 'ZERO', '0', 'BLANK', ' ')))
FROM ar_transmissions tr,
ar_trans_field_formats ff,
ar_trans_record_formats rf
WHERE tr.transmission_id = pi.transmission_id
AND ff.transmission_format_id = tr.requested_trans_format_id
AND rf.record_format_id = ff.record_format_id
AND rf.record_identifier = pi.record_type
AND ff.field_type_lookup_code IN('LB NUM'))
WHERE pi.transmission_request_id = in_trans_req_id
AND pi.lockbox_number IS NOT NULL; */
select distinct( nvl(a.line_level_cash_app_rule, 'N'))
into l_line_level_cash_app_rule
from ar_lockboxes a
where a.lockbox_number in ( select distinct(lockbox_number)
from ar_payments_interface
where transmission_request_id = in_trans_req_id
and lockbox_number is not null );
pvt_insert_records := 'N';
pvt_insert_records := 'N';
pvt_insert_records := 'Y';
IF pvt_insert_records = 'N' THEN
g_second_validation_pvt := TRUE;
arp_lockbox_hook.proc_before_validation(pub_errorbuf,pub_errorcode,in_trans_req_id,pub_insert_records);
IF pub_insert_records = 'N' THEN
g_second_validation_pub := TRUE;
IF pvt_insert_records = 'N' THEN
out_insert_records := 'N';
out_insert_records := pub_insert_records;
If out_insert_records is returned as 'Y', the second validation will
insert the records into ar_interim_cash_receipt and receipt_line.
In non-custom mode, this parameter returns 'N', because we do not call
validation second time. However, if you are planning to call the second
validation and you have returned out_insert_records as 'N' in the
proc_before_validation, you should return 'Y' here. This parameter is
considered only if the out_errorcode was returned as 0.
----------------------------------------------------------------------------*/
PROCEDURE proc_after_validation(out_errorbuf OUT NOCOPY VARCHAR2,
out_errorcode OUT NOCOPY VARCHAR2,
in_trans_req_id IN VARCHAR2,
out_insert_records OUT NOCOPY VARCHAR2) IS
--
l_okl_block varchar2(1000);
pvt_insert_records varchar2(1);
pub_insert_records varchar2(1);
pvt_insert_records := 'Y';
pvt_insert_records := 'Y';
pvt_insert_records := 'N';
arp_lockbox_hook.proc_after_validation(pub_errorbuf,pub_errorcode,in_trans_req_id,pub_insert_records);
out_insert_records := pub_insert_records;
out_insert_records := pvt_insert_records;
out_insert_records := pvt_insert_records;
out_insert_records := pub_insert_records;
line level cash application is selected to be custom in lockbox setup.
This procedure calls the custom package which gives the line level application
details, which will be processed in this proc and will be inserted in lockbox
interface tables.
----------------------------------------------------------------------------*/
PROCEDURE proc_for_custom_llca(in_trans_req_id IN NUMBER) IS
l_invoice_array invoice_array;
select distinct item_number
from ar_payments_interface_all
where transmission_request_id = req_id;
select transmission_record_id
from ar_payments_interface_all
where transmission_request_id = request_id
and item_number = itm_num
and record_type = rec_type
order by transmission_record_id;
SELECT transmission_record_id,
trim(item_number) item_number,
trim(record_type) record_type,
trim(invoice1) invoice1,
trim(invoice2) invoice2,
trim(invoice3) invoice3,
trim(invoice4) invoice4,
trim(invoice5) invoice5,
trim(invoice6) invoice6,
trim(invoice7) invoice7,
trim(invoice8) invoice8,
amount_applied1,
amount_applied2,
amount_applied3,
amount_applied4,
amount_applied5,
amount_applied6,
amount_applied7,
amount_applied8,
batch_name
FROM ar_payments_interface_all
WHERE transmission_request_id = req_id
AND record_type in ( select a.record_identifier from ar_trans_record_formats a, ar_transmissions_all b
where b.transmission_request_id = req_id
and b.requested_trans_format_id = a.transmission_format_id
and a.record_type_lookup_code in ('PAYMENT','OVRFLW PAYMENT') );
SELECT distinct 'Y'
INTO l_batches
FROM ar_trans_field_formats
WHERE transmission_format_id = (SELECT transmission_format_id
FROM ar_transmission_formats a,
ar_transmissions_all b
WHERE a.transmission_format_id = b.requested_trans_format_id
AND b.transmission_request_id = in_trans_req_id )
AND field_type_lookup_code = 'BATCH NAME';
SELECT nvl(pay_unrelated_invoices_flag, 'N')
INTO l_pay_unrelated_invoices
FROM ar_system_parameters;
select max(fc.precision)
into l_precision
from fnd_currencies fc
where fc.currency_code =
(select max(pi.currency_code)
from ar_payments_interface pi,
ar_payments_interface pi1
where pi.item_number = pi1.item_number
and pi1.transmission_request_id = in_trans_req_id
and pi1.transmission_record_id = app_rec.transmission_record_id);
SELECT overflow_rec_indicator
INTO l_overflow_indicator
FROM ar_trans_field_formats a, ar_transmissions_all b
WHERE b.requested_trans_format_id = a.transmission_format_id
AND b.transmission_request_id = in_trans_req_id
AND a.FIELD_TYPE_LOOKUP_CODE = 'OVRFLW IND';
SELECT b.record_identifier,
b.transmission_format_id,
a.transmission_id
INTO l_overflow_rec, l_trans_format_id, l_transmission_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 = in_trans_req_id
AND b.record_type_lookup_code = 'OVRFLW PAYMENT';
* currency application, then a record is inserted into ar_payments_interface *
* for each resolved number along with their line level details in ar_pmts_ *
* interface_line_details, if any. If a record failed in validation then a record is *
* inserted into ar_payments_interface for the matching number, which would eventually fail*
* in validation.
*/
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('Number of Invoices inside custom code :' || l_last_invoice_index);
SELECT a
INTO l_resolved_number
FROM
(SELECT decode(l_unres_inv_array(i).matching_number,
invoice1, 1,
invoice2, 2,
invoice3, 3,
invoice4, 4,
invoice5, 5,
invoice6, 6,
invoice7, 7,
invoice8, 8) a
FROM ar_payments_interface_all
WHERE transmission_request_id = in_trans_req_id
AND item_number = l_unres_inv_array(i).item_number
AND record_type = l_unres_inv_array(i).record_type
AND NVL(batch_name, -1)= NVL(l_unres_inv_array(i).batch_name, -1))
where a IS NOT NULL;
l_sql_stmt := 'SELECT amount_applied_from'||l_resolved_number||', trans_to_receipt_rate'
||l_resolved_number||', invoice_currency_code'||l_resolved_number||', customer_id'
||', amount_applied'||l_resolved_number||', matching'||l_resolved_number||'_date'
||' FROM ar_payments_interface_all WHERE transmission_request_id = :1'
||' AND item_number = :2'
||' AND record_type = :3'
||' AND invoice'||l_resolved_number ||'= :4'
||' AND NVL(batch_name, -1) = :5';
SELECT distinct(invoice_currency_code)
INTO l_currency_code1
FROM ar_payment_schedules ps,
ra_cust_trx_types tt
WHERE ps.trx_number = l_resolved_array(j).invoice_number
AND ps.trx_date = nvl(l_matching_date, ps.trx_date)
AND ps.status = decode(tt.allow_overapplication_flag,
'N', 'OP',
ps.status)
AND ps.class NOT IN ('PMT','GUAR')
AND (ps.customer_id IN
(
select l_customer_id from dual
union
select related_cust_account_id
from hz_cust_acct_relate rel
where rel.cust_account_id = l_customer_id
and rel.status = 'A'
and rel.bill_to_flag = 'Y'
union
select rel.related_cust_account_id
from ar_paying_relationships_v rel,
hz_cust_accounts acc
where rel.party_id = acc.party_id
and acc.cust_account_id = l_customer_id
)
or
l_pay_unrelated_invoices = 'Y'
)
AND ps.cust_trx_type_id = tt.cust_trx_type_id;
SELECT decode(format_amount_app, 'Y', d.precision, 0)
INTO l_inv_precision
FROM fnd_currencies d
WHERE d.currency_code = trim(l_invoice_currency_code);
SELECT ar_payments_interface_s.nextval
INTO l_transmission_rec_id_of
FROM dual;
with the details provided at the resolved invoice level. So insert the Custom
number into the interface tables instead of resolved numbers as an invalid
application. */
l_upd_stmt := 'UPDATE ar_payments_interface_all'
||' SET invoice'||l_resolved_number||'status = ''AR_PLB_INVALID_MATCH'''
||' WHERE transmission_request_id = :1 AND item_number = :2'
||' AND record_type = :3'
||' AND invoice'||l_resolved_number ||'= :4'
||' AND NVL(batch_name, -1) = :5';
l_upd_stmt := 'UPDATE ar_payments_interface_all'
||' SET invoice'||l_resolved_number||' = NULL'
||', amount_applied'||l_resolved_number||' = NULL'
||' WHERE transmission_request_id = :1 AND item_number = :2'
||' AND record_type = :3'
||' AND invoice'||l_resolved_number ||'= :4'
||' AND NVL(batch_name, -1) = :5';
SELECT a.org_id,
a.lockbox_number,
a.batch_name,
a.currency_code,
decode(format_amount1,'Y',d.precision,0)
INTO l_org_id, l_lockbox_number, l_batch_name, l_currency_code, l_precision
FROM ar_payments_interface_all a,
ar_transmissions_all b,
ar_trans_record_formats c,
fnd_currencies d
WHERE a.transmission_request_id = b.transmission_request_id
AND b.requested_trans_format_id = c.transmission_format_id
AND c.record_identifier = a.record_type
AND d.currency_code = a.currency_code
AND a.transmission_request_id = in_trans_req_id
AND c.record_type_lookup_code = 'PAYMENT'
AND a.item_number = l_unres_inv_array(i).item_number
AND NVL(a.batch_name, -1) = NVL(l_unres_inv_array(i).batch_name, -1);
SELECT ar_payments_interface_s.nextval
INTO l_transmission_rec_id_of
FROM dual;
/* Insert a new overflow record for the new invoice number resolved. */
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,
trans_to_receipt_rate1,
invoice_currency_code1,
org_id,
creation_date,
last_update_date)
VALUES(
l_transmission_rec_id_of,
l_resolved_array(j).item_number,
l_overflow_rec,
'AR_PLB_NEW_RECORD',
l_transmission_id,
in_trans_req_id,
l_lockbox_number,
l_batch_name,
l_resolved_array(j).invoice_number,
l_resolved_array(j).amount_applied * power(10,l_inv_precision),
l_resolved_array(j).amount_applied_from * power(10,l_precision),
nvl(l_resolved_array(j).trans_to_receipt_rate,
l_trans_to_receipt_rate),
trim(nvl(l_resolved_array(j).invoice_currency_code,
l_invoice_currency_code)),
l_org_id,
sysdate,
trunc(sysdate));
arp_util.debug('Inserting lines for '|| l_resolved_array(j).invoice_number);
INSERT INTO AR_PMTS_INTERFACE_LINE_DETAILS (
status,
transmission_request_id,
transmission_record_id,
invoice_number,
apply_to,
amount_applied,
allocated_receipt_amount,
line_amount,
tax,
freight,
charges )
VALUES (
'AR_PLB_NEW_RECORD',
in_trans_req_id,
l_transmission_rec_id_of,
l_line_array(k).invoice_number,
l_line_array(k).apply_to,
l_line_array(k).amount_applied,
l_line_array(k).allocated_receipt_amount,
l_line_array(k).line_amount,
l_line_array(k).tax_amount,
l_line_array(k).freight,
l_line_array(k).charges
);
END IF; /* Insert line Records */
END LOOP; /* Insert Resolved Records */
END IF; /* Insert Overflow records */
/* Delete the old overflow records for all the receipts, where all the matching numbers in
the overflow record are resolved in custom code i.e, no use in having overflow
records with all invoice1 to invoice8 columns null. */
delete from ar_payments_interface_all
where transmission_request_id = in_trans_req_id
and invoice1 is null
and invoice2 is null
and invoice3 is null
and invoice4 is null
and invoice5 is null
and invoice6 is null
and invoice7 is null
and invoice8 is null
and record_type = l_overflow_rec;
/* Update the interface table overflow records for correct overflow sequence and
indicators value. */
FOR item_num IN distinct_item_num( in_trans_req_id ) LOOP
l_overflow_seq := 1;
update ar_payments_interface_all
set overflow_sequence = l_overflow_seq,
overflow_indicator = l_overflow_indicator
where transmission_record_id = record_id.transmission_record_id;
update ar_payments_interface_all
set overflow_indicator = l_final_rec_overflow_ind
where transmission_record_id = (
select max(transmission_record_id)
from ar_payments_interface_all
where transmission_request_id = in_trans_req_id
and item_number = item_num.item_number
and record_type = l_overflow_rec );
/* Update the transmission record count with correct value if there are
transmission header or trailer records in the transmission. */
update ar_payments_interface_all
set transmission_record_count = (
select count(*) from ar_payments_interface_all
where transmission_request_id = in_trans_req_id )
where transmission_request_id = in_trans_req_id
and record_type in ( select a.record_identifier
from ar_trans_record_formats a, ar_transmissions_all b
where b.transmission_request_id = in_trans_req_id
and b.requested_trans_format_id = a.transmission_format_id
and a.record_type_lookup_code in ('TRANS HDR','TRANS TRL') );