[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATED_BY NUMBER := -222;
G_LAST_UPDATE_LOGIN NUMBER := -222;
PROCEDURE insert_invoice_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_po_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_so_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_contract_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_attribute_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_attribute_number IN VARCHAR2
, p_request_id IN NUMBER);
PROCEDURE insert_waybill_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_bfb_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE insert_reference_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER);
PROCEDURE copy_current_record( p_current_reco IN OUT NOCOPY selected_recos_table
, p_selected_recos IN selected_recos_table
, p_index IN NUMBER);
PROCEDURE process_single_reco(p_current_reco IN OUT NOCOPY selected_recos_table
, p_match_resolved_using IN VARCHAR2);
PROCEDURE populate_reco_line_struct(p_current_reco IN selected_recos_table
, p_match_resolved_using IN VARCHAR2
, p_recommendation_id IN NUMBER
, p_recommendation_reason IN VARCHAR2);
PROCEDURE insert_recos(p_request_id IN NUMBER);
* The function will be called for every insert execution but we need to *
* generate a new sequence only for a new recommendation (not for each line)*
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
FUNCTION get_next_reco_id( p_reco_num IN NUMBER)
RETURN NUMBER IS
l_reco_id NUMBER;
SELECT ar_cash_recos_s.nextval
INTO l_reco_id
FROM DUAL;
SELECT string_type_code type,
string_location_code location,
DECODE(padding_value_code, 'ZERO', '[0]',
'SPACE', '[ ]',
'ANY', '.',
padding_value_code) value,
DECODE(padding_value_code, 'ANY', NVL(TO_CHAR(number_of_positions),'9999'),
'ZERO', NVL(TO_CHAR(number_of_positions),'1,'),
'SPACE', NVL(TO_CHAR(number_of_positions),'1,')) position
/* When no of positions is not mentioned replace all the occurences */
FROM ar_cash_automatch_dtls
WHERE automatch_id = p_rule_id
ORDER BY string_type_code ASC, string_location_code ASC, padding_sequence ASC;
* 1. Delete data from ar_cash_remit_refs_interim, if any data is present. *
* The table is truncated at the end of each run. However if any data *
* exists inside the interim event necause of any unhandled exception in*
* the previous run, just a precautionary measure to retrunc the table *
* 2. Populate ar_cash_Remit_refs_interim with data from ar_cash_remit_refs*
* based on the parameters provided to the concurrent program. *
* 3. Update the references with status 'AR_AA_RULE_SET_INACTIVE' which *
* are associated to a rule set that is inactive. *
* 4. Spawn the child process or directly call auto_apply_child() process *
* based on the 'No of Instances' parameter. *
* 5. Update the references with status 'AR_AA_SUGG_FOUND'/'AR_AA_NO_MATCH'*
* based on the number of receommendations generated for the remittances*
* that are not automatically applied. *
* 6. Update the receipt's WORK_ITEM_EXCEPTION_REASON for the receipts that*
* have unapplied remittance lines at the end of the program. *
* NOTES - *
* This program is the starting point for 'AR_AUTOAPPLY_API'. This is *
* called from XML report *
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE auto_apply_master ( P_ERRBUF OUT NOCOPY VARCHAR2
, P_RETCODE OUT NOCOPY NUMBER
, p_org_id IN NUMBER
, p_receipt_no_l IN VARCHAR2
, p_receipt_no_h IN VARCHAR2
, p_batch_name_l IN VARCHAR2
, p_batch_name_h IN VARCHAR2
, p_min_unapp_amt IN NUMBER
, p_receipt_date_l IN VARCHAR2
, p_receipt_date_h IN VARCHAR2
, p_receipt_method_l IN VARCHAR2
, p_receipt_method_h IN VARCHAR2
, p_customer_name_l IN VARCHAR2
, p_customer_name_h IN VARCHAR2
, p_customer_no_l IN VARCHAR2
, p_customer_no_h IN VARCHAR2
, p_batch_id IN NUMBER
, p_transmission_id IN NUMBER
, p_called_from IN VARCHAR2
, p_total_workers IN NUMBER) IS
l_insert_stmt VARCHAR2(30000) := NULL;
insert_gt INTEGER;
l_rows_inserted INTEGER;
delete_interim_records; /* Call to delete records from interface table */
G_LAST_UPDATED_BY := arp_standard.profile.user_id;
G_LAST_UPDATE_LOGIN := arp_standard.profile.last_update_login;
l_insert_stmt := ' INSERT INTO AR_CASH_REMIT_REFS_INTERIM
( REMIT_REFERENCE_ID,
RECEIPT_REFERENCE_STATUS,
AUTOMATCH_SET_ID,
CASH_RECEIPT_ID,
REFERENCE_SOURCE,
CUSTOMER_ID,
CUSTOMER_NUMBER,
BANK_ACCOUNT_NUMBER,
TRANSIT_ROUTING_NUMBER,
INVOICE_REFERENCE,
MATCHING_REFERENCE_DATE,
INSTALLMENT_REFERENCE,
INVOICE_CURRENCY_CODE,
AMOUNT_APPLIED,
AMOUNT_APPLIED_FROM,
TRANS_TO_RECEIPT_RATE,
TRANSMISSION_ID,
BATCH_ID,
WORKER_NUMBER)
SELECT ref.REMIT_REFERENCE_ID,
''AR_AM_NEW'',
cr.AUTOMATCH_SET_ID,
ref.CASH_RECEIPT_ID,
ref.REFERENCE_SOURCE,
cr.PAY_FROM_CUSTOMER,
ref.CUSTOMER_NUMBER,
ref.BANK_ACCOUNT_NUMBER,
ref.TRANSIT_ROUTING_NUMBER,
ref.INVOICE_REFERENCE,
ref.MATCHING_REFERENCE_DATE,
ref.INSTALLMENT_NUMBER,
ref.INVOICE_CURRENCY_CODE,
ref.AMOUNT_APPLIED,
ref.AMOUNT_APPLIED_FROM,
ref.TRANS_TO_RECEIPT_RATE,
ref.TRANSMISSION_ID,
ref.BATCH_ID,
MOD( ref.CASH_RECEIPT_ID, :b_total_workers) + 1';
l_insert_stmt := l_insert_stmt || l_from_clause || l_where_clause;
log('Insert Statement : ' || l_insert_stmt);
insert_gt := dbms_sql.open_cursor;
dbms_sql.parse (insert_gt,l_insert_stmt,dbms_sql.v7);
dbms_sql.bind_variable ( insert_gt, ':b_total_workers', p_total_workers);
dbms_sql.bind_variable ( insert_gt, ':b_transmission_id', p_transmission_id);
dbms_sql.bind_variable ( insert_gt, ':b_batch_id', p_batch_id);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_no_l', p_receipt_no_l);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_no_h', p_receipt_no_h);
dbms_sql.bind_variable ( insert_gt, ':b_batch_name_l', p_batch_name_l);
dbms_sql.bind_variable ( insert_gt, ':b_batch_name_h', p_batch_name_h);
dbms_sql.bind_variable ( insert_gt, ':b_min_unapp_amt', p_min_unapp_amt);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_date_l', l_receipt_date_low);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_date_h', l_receipt_date_high);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_method_l', p_receipt_method_l);
dbms_sql.bind_variable ( insert_gt, ':b_receipt_method_h', p_receipt_method_h);
dbms_sql.bind_variable ( insert_gt, ':b_customer_name_l', p_customer_name_l);
dbms_sql.bind_variable ( insert_gt, ':b_customer_name_h', p_customer_name_h);
dbms_sql.bind_variable ( insert_gt, ':b_customer_no_l', p_customer_no_l);
dbms_sql.bind_variable ( insert_gt, ':b_customer_no_h', p_customer_no_h);
l_rows_inserted := dbms_sql.execute( insert_gt);
UPDATE ar_cash_remit_refs_interim cri
SET cri.receipt_reference_status = 'AR_AA_RULE_SET_INACTIVE'
WHERE cri.cash_receipt_id IN (
SELECT distinct cr.cash_receipt_id
FROM ar_cash_remit_refs_interim cri1,
ar_cash_auto_rule_sets aca,
ar_cash_receipts cr
WHERE cr.cash_receipt_id = cri1.cash_receipt_id
AND cr.automatch_set_id = aca.automatch_set_id
AND (cr.receipt_date < NVL(aca.start_date, cr.receipt_date)
OR cr.receipt_date > NVL(aca.end_date, to_date('31/12/4712','DD/MM/YYYY'))
OR NVL(aca.active_flag, 'N') = 'N')
)
AND cri.receipt_reference_status = 'AR_AM_NEW';
UPDATE ar_cash_remit_refs_interim
SET receipt_reference_status = 'AR_AA_RULE_SET_NOT_PASSED'
WHERE automatch_set_id IS NULL
AND receipt_reference_status = 'AR_AM_NEW';
UPDATE ar_cash_remit_refs_interim
SET receipt_reference_status = 'AR_AA_AMT_NOT_PASSED'
WHERE amount_applied IS NULL
AND amount_applied_from IS NULL
AND receipt_reference_status = 'AR_AM_NEW';
/* * Delete Suggestions for the references that will be processed in *
* the current run. This is to avoid duplicate recommendations *
* getting generated and to handle the cases where a refernce no is*
* changed after the previous run. Refer bug 8396831 * */
DELETE FROM ar_cash_reco_lines lines
WHERE EXISTS (
SELECT 'Suggestion Exists'
FROM ar_cash_recos rec, ar_cash_remit_refs_interim ref
WHERE rec.recommendation_id = lines.recommendation_id
AND rec.remit_reference_id = ref.remit_reference_id
AND ref.receipt_reference_status = 'AR_AM_NEW'
);
DELETE FROM ar_cash_recos rec
WHERE EXISTS(
SELECT 'Suggestion Exists'
FROM ar_cash_remit_refs_interim ref
WHERE rec.remit_reference_id = ref.remit_reference_id
AND ref.receipt_reference_status = 'AR_AM_NEW'
);
/* * AutoCash Application Process Completed. Now update the receipt_ *
* reference_status for the unapplied references with either No Match*
* Found or Suggestions found based on recommendations generated * */
UPDATE ar_cash_remit_refs_interim cri
SET cri.receipt_reference_status = DECODE(
( SELECT 'MATCH_FOUND'
FROM ar_cash_recos
WHERE remit_reference_id = cri.remit_reference_id
AND rownum = 1 ),'MATCH_FOUND','AR_AA_SUGG_FOUND','AR_AA_NO_MATCH')
WHERE cri.receipt_reference_status = 'AR_AM_NEW';
UPDATE ar_cash_remit_refs crr
SET crr.receipt_reference_status = (SELECT cri.receipt_reference_status
FROM ar_cash_remit_refs_interim cri
WHERE crr.remit_reference_id = cri.remit_reference_id
AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
WHERE crr.remit_reference_id IN (SELECT cri.remit_reference_id
FROM ar_cash_remit_refs_interim cri
WHERE crr.remit_reference_id = cri.remit_reference_id
AND cri.receipt_reference_status IN ('AR_AA_SUGG_FOUND', 'AR_AA_NO_MATCH', 'AR_AA_RULE_SET_INACTIVE', 'AR_AA_RULE_SET_NOT_PASSED', 'AR_AA_AMT_NOT_PASSED'))
AND crr.receipt_reference_status <> 'AR_AA_INV_APPLIED';
/* * If a receipt has any unapplied remittance line, update the *
* receipt work_item_exception_reason with the exception reason *
* defined at the AutoMatchRule Setup * */
UPDATE ar_cash_receipts_all cr
SET WORK_ITEM_EXCEPTION_REASON =
(SELECT exception_reason
FROM ar_cash_auto_rule_sets
WHERE automatch_set_id = cr.automatch_set_id)
WHERE cash_receipt_id IN
(SELECT distinct cash_receipt_id
FROM ar_cash_remit_refs_interim cri
WHERE receipt_reference_status IN ('AR_AA_NO_MATCH','AR_AA_SUGG_FOUND')
);
* 1. The check if a rule is active wrt receipt date is made while inserting
* recommendations. *
* 2. Recommendations are inserted once per each automatch rule. Meaning if*
* a rule R1 is part of two sets S1, S2 and suppose we are processing S1*
* first, then for all the references that have either S1 or S2 as rule *
* sets recommendations for the rule R1 are generated while processing S1
* itself. So there is no need to insert recommendations again while *
* processing S2. Hence whenever a rule is fetched for a rule set, first*
* check is made to see if the rule is already processed as part of any *
* other rule set. *
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE auto_apply_child( P_ERRBUF OUT NOCOPY VARCHAR2
, P_RETCODE OUT NOCOPY NUMBER
, p_worker_number IN NUMBER) IS
CURSOR auto_rule_set_cur(p_worker_number IN NUMBER) IS
SELECT distinct automatch_set_id
FROM AR_CASH_REMIT_REFS_INTERIM
WHERE worker_number = p_worker_number
AND receipt_reference_status = 'AR_AM_NEW';
SELECT aca.automatch_id automatch_id,
aca.matching_option matching_option,
NVL(aca.use_matching_date, 'N') use_matching_date
FROM AR_CASH_AUTOMATCHES aca,
AR_CASH_AUTOMATCH_RULE_MAP acm
WHERE acm.automatch_set_id = p_automatch_set_id
AND aca.automatch_id = acm.automatch_id
AND NVL(aca.active_flag, 'N') = 'Y'
ORDER BY acm.priority;
insert_invoice_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_so_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_po_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_bfb_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_waybill_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_attribute_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
substr(l_matching_option, 12),
p_request_id);
insert_contract_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
insert_reference_recos(l_automatch_id,
l_use_matching_date,
l_trans_format_str,
l_rem_format_str,
l_trans_float_str,
l_rem_float_str,
l_worker_number,
p_request_id);
* INSERT_INVOICE_RECOS() *
* DESCRIPTION *
* Inserts recommendations for transaction numbers *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open transactions satisfying all the setup conditions calculate
* the matching score of transaction number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. An invoice can have multiple installments; which means there is a *
PROCEDURE insert_invoice_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id customer_id,
ps.customer_site_use_id customer_site_use_id,
ps.trx_number resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY ps.trx_number, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND ps.trx_number IS NOT NULL
AND ps.selected_for_receipt_batch_id IS NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.terms_sequence_number = NVL(ref.installment_reference,
ps.terms_sequence_number)
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND trx.customer_trx_id = ps.customer_trx_id
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_invoice_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'INVOICE');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
log('Else-If');
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_invoice_recos(-)');
log('Exception from arp_autoapply_api.insert_invoice_recos');
END insert_invoice_recos;
* INSERT_PO_RECOS() *
* DESCRIPTION *
* Inserts recommendations for Purchase Orders *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open POs satisfying all the setup conditions calculate *
* the matching score of purchase order number with the reference number*
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. A PO can have multiple invoices; which means there is a possibility *
PROCEDURE insert_po_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
trx.purchase_order resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date, NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
trx.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id,
ps.term_id term_id,
am.automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY trx.purchase_order, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ra_customer_trx trx,
ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND trx.purchase_order IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.customer_trx_id = trx.customer_trx_id
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_po_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'PURCHASE ORDER');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
log('Else-If');
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_po_recos(-)');
log('Exception from arp_autoapply_api.insert_po_recos');
END insert_po_recos;
* INSERT_SO_RECOS() *
* DESCRIPTION *
* Inserts recommendations for Sales Orders *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open SOs satisfying all the setup conditions calculate *
* the matching score of sales order number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. A SO can have multiple invoices; which means there is a possibility *
PROCEDURE insert_so_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
lin.sales_order resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY lin.sales_order, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ra_customer_trx_lines lin,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND lin.interface_line_context <> 'OKS CONTRACTS'
AND lin.sales_order IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.customer_trx_id = lin.customer_trx_id
AND trx.customer_trx_id = ps.customer_trx_id
/* Added to fetch values from Header Attributes */
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_so_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'SALES ORDER');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_so_recos(-)');
log('Exception from arp_autoapply_api.insert_so_recos');
END insert_so_recos;
* INSERT_CONTRACT_RECOS() *
* DESCRIPTION *
* Inserts recommendations for Sales Contracts *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open contracts satisfying all the setup conditions calculate *
* the matching score of contract number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. A Contract can have multiple invoices;which means there is a possibility
PROCEDURE insert_contract_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
lin.sales_order resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY lin.sales_order, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ra_customer_trx_lines lin,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND lin.interface_line_context = 'OKS CONTRACTS'
AND lin.sales_order IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.customer_trx_id = lin.customer_trx_id
AND trx.customer_trx_id = ps.customer_trx_id
/* Added to fetch the date from Header Attribute Columns */
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_contract_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'SERVICE CONTRACT');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_contract_recos(-)');
log('Exception from arp_autoapply_api.insert_contract_recos');
END insert_contract_recos;
* INSERT_ATTRIBUTE_RECOS() *
* DESCRIPTION *
* Inserts recommendations for transaction numbers (Matched with interface *
* header attribute) *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_attribute_number Header Attribute Number that has to *
* be matches with (1-16) *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open transactions satisfying all the setup conditions calculate
* the matching score of header attribute value with the reference number
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. An invoice can have multiple installments; which means there is a *
PROCEDURE insert_attribute_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_attribute_number IN VARCHAR2
, p_request_id IN NUMBER) IS
l_sel_stmt VARCHAR2(12000) := 'SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
trx.trx_number resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
''INT_HDR_ATT1'', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
''INT_HDR_ATT10'', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
''INT_HDR_ATT11'', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
''INT_HDR_ATT12'', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
''INT_HDR_ATT13'', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
''INT_HDR_ATT14'', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
''INT_HDR_ATT15'', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
''INT_HDR_ATT2'', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
''INT_HDR_ATT3'', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
''INT_HDR_ATT4'', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
''INT_HDR_ATT5'', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
''INT_HDR_ATT6'', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
''INT_HDR_ATT7'', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
''INT_HDR_ATT8'', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
''INT_HDR_ATT9'', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
''PURCH_ORDER_DATE'', trx.purchase_order_date,
''TRANS_DATE'', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
ar_setup.value(''AR_CROSS_CURRENCY_RATE_TYPE'',null) )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.interface_header_attribute'|| p_attribute_number ||', :b_trans_format_str, ''\2''), :b_trans_float_str, ''''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) match_score_value,
ps.org_id org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY trx.interface_header_attribute' || p_attribute_number ||', ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ra_customer_trx trx,
ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = :b_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = :b_worker_number
AND ref.receipt_reference_status = ''AR_AM_NEW''
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date(''31/12/4712'',''DD/MM/YYYY''))
AND trx.interface_header_attribute'|| p_attribute_number || ' IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.interface_header_attribute'|| p_attribute_number ||', :b_trans_format_str, ''\2''), :b_trans_float_str, ''''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) >= am.sugg_match_threshold
AND ps.customer_trx_id = trx.customer_trx_id
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN (''PMT'', ''GUAR'')
AND ps.status = ''OP''
AND ps.terms_sequence_number = NVL(ref.installment_reference,
ps.terms_sequence_number)
AND ps.payment_schedule_id > 0
AND ps.customer_id IN (SELECT DECODE(:b_pay_unrelated_invoices_flag,''Y'', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = ''Y''
AND rel.status = ''A''
AND :b_pay_unrelated_invoices_flag <> ''Y''
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND :b_pay_unrelated_invoices_flag <> ''Y'' )
AND trx.customer_trx_id = ps.customer_trx_id
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id';
TYPE SelectRecoType IS REF CURSOR;
select_recos SelectRecoType;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_attribute_recos(+)');
OPEN select_recos FOR l_sel_stmt USING p_trans_format_str,
p_trans_float_str,
p_rem_format_str,
p_rem_float_str,
p_automatch_id,
p_worker_number,
p_trans_format_str,
p_trans_float_str,
p_rem_format_str,
p_rem_float_str,
ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,
ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,
ARP_STANDARD.sysparm.pay_unrelated_invoices_flag;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'INTERFACE HEADER ATTRIBUTE'||p_attribute_number);
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
log('Else-If');
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_attribute_recos(-)');
log('Exception from arp_autoapply_api.insert_attribute_recos');
END insert_attribute_recos;
* INSERT_WAYBILL_RECOS() *
* DESCRIPTION *
* Inserts recommendations for Waybill Numbers *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open way bills satisfying all the setup conditions calculate *
* the matching score of way bill number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. ar_cash_recos contains header level information like resolved *
* number(way bill number)etc., where as ar_cash_reco_lines contains the*
* sepecific ps information for the resolved transaction. *
* *
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE insert_waybill_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
trx.waybill_number resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id,
ps.term_id term_id,
am.automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY trx.waybill_number, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ra_customer_trx trx,
ar_payment_schedules ps,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND trx.waybill_number IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.customer_trx_id = trx.customer_trx_id
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_waybill_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'WAYBILL NUMBER');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_waybill_recos(-)');
log('Exception from arp_autoapply_api.insert_waybill_recos');
END insert_waybill_recos;
* INSERT_BFB_RECOS() *
* DESCRIPTION *
* Inserts recommendations for Balance Forward Bills *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open bfbs satisfying all the setup conditions calculate *
* the matching score of bfb number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. A bfb can have multiple invoices; which means there is a possibility *
PROCEDURE insert_bfb_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ci.customer_id,
ci.site_use_id customer_site_use_id,
ci.cons_billing_number resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by, 'BAL_FWD_BILL_DATE', trunc(ci.billing_date), NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ci.currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
ci.cons_inv_id cons_inv_id,
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ci.org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY ci.cons_billing_number, ci.site_use_id, ref.remit_reference_id
ORDER BY ps.due_date, ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ar_cons_inv ci,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.cons_inv_id = ci.cons_inv_id
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND trx.customer_trx_id = ps.customer_trx_id
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_bfb_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'BALANCE FORWARD BILL');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
log('Else-If');
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_bfb_recos(-)');
log('Exception from arp_autoapply_api.insert_bfb_recos');
END insert_bfb_recos;
* INSERT_REFERENCE_RECOS() *
* DESCRIPTION *
* Inserts recommendations for transaction numbers (Matched with reference *
* number ra_customer_trx.ct_reference) *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_automatch_id Automatch Rule Identifier *
* p_use_matching_date Use Matching Date [ALWAYS/For *
* Duplicates/NULL] *
* p_trans_format_str Transaction Number Format String *
* p_rem_format_str Reference Number Format String *
* p_worker_number Current Worker Number *
* p_request_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. For all open transactions satisfying all the setup conditions calculate
* the matching score of trx reference number with the reference number *
* given in the remittance lines (ar_cash_remit_refs_all) *
* 2. If match_score > suggested threshold value specified at the AutoMatch*
* setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
* -tion. *
* NOTES - *
* 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
* not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
* 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
* unidentified then transactions for all the customers are considered. *
* Otherwise only the transactions related to the paying customer of the*
* receipt are considered. *
* 3. An invoice can have multiple installments; which means there is a *
PROCEDURE insert_reference_recos (p_automatch_id IN NUMBER
, p_use_matching_date IN VARCHAR2
, p_trans_format_str IN VARCHAR2
, p_rem_format_str IN VARCHAR2
, p_trans_float_str IN VARCHAR2
, p_rem_float_str IN VARCHAR2
, p_worker_number IN NUMBER
, p_request_id IN NUMBER) IS
CURSOR select_recos IS
SELECT ref.remit_reference_id remit_reference_id,
ref.amount_applied ref_amount_applied,
ref.amount_applied_from ref_amount_applied_from,
ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
ref.cash_receipt_id cash_receipt_id,
cr.pay_from_customer pay_from_customer,
cr.customer_site_use_id cr_customer_site_use_id,
ps.customer_trx_id customer_trx_id,
ps.customer_id,
ps.customer_site_use_id customer_site_use_id,
ps.trx_number resolved_matching_number,
ps.terms_sequence_number terms_sequence_number,
decode(am.match_date_by,
'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
'PURCH_ORDER_DATE', trx.purchase_order_date,
'TRANS_DATE', trx.trx_date,
NULL) resolved_matching_date,
ps.trx_date trx_date,
ps.class resolved_matching_class,
ps.invoice_currency_code resolved_match_currency,
ps.amount_due_original amount_due_original,
ps.amount_due_remaining amount_due_remaining,
ps.discount_taken_earned discount_taken_earned,
ps.discount_taken_unearned discount_taken_unearned,
ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
ROUND(NVL(ref.trans_to_receipt_rate,
DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
ref.amount_applied,
ref.amount_applied_from,
ps.invoice_currency_code,
cr.currency_code
)
, GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
cr.currency_code,
cr.receipt_date,
arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
)
)
),38) trans_to_receipt_rate,
NULL amount_applied_from, -- will be calculated later for xcurr app.
ps.payment_schedule_id payment_schedule_id,
NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
ps.org_id,
ps.term_id term_id,
am.automatch_id automatch_id,
am.use_matching_date use_matching_date,
am.use_matching_amount use_matching_amount,
am.auto_match_threshold auto_match_threshold,
amp.priority priority,
cr.currency_code receipt_currency_code,
cr.receipt_date receipt_date,
ctt.allow_overapplication_flag allow_overapplication_flag,
tr.partial_discount_flag partial_discount_flag,
RANK() OVER (PARTITION BY trx.ct_reference, ps.customer_site_use_id,
ref.remit_reference_id, ps.customer_trx_id
ORDER BY ps.payment_schedule_id) AS reco_num
FROM ar_cash_automatches am,
ar_cash_automatch_rule_map amp,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr,
ar_payment_schedules ps,
ra_customer_trx trx,
ra_cust_trx_types ctt,
ra_terms tr
WHERE am.automatch_id = p_automatch_id
AND amp.automatch_id = am.automatch_id
AND amp.automatch_set_id = ref.automatch_set_id
AND ref.worker_number = p_worker_number
AND ref.receipt_reference_status = 'AR_AM_NEW'
AND cr.cash_receipt_id = ref.cash_receipt_id
AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
AND trx.ct_reference IS NOT NULL
AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
AND ps.customer_trx_id = trx.customer_trx_id
AND ps.selected_for_receipt_batch_id IS NULL
AND ps.class NOT IN ('PMT', 'GUAR')
AND ps.payment_schedule_id > 0
AND ps.status = 'OP'
AND ps.terms_sequence_number = NVL(ref.installment_reference,
ps.terms_sequence_number)
AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
NVL(cr.pay_from_customer, ps.customer_id))
FROM DUAL
UNION ALL
SELECT related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = cr.pay_from_customer
AND rel.bill_to_flag = 'Y'
AND rel.status = 'A'
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
UNION ALL
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE acc.cust_account_id = cr.pay_from_customer
AND acc.party_id = rel.party_id
AND cr.receipt_date >= effective_start_date
AND cr.receipt_date <= effective_end_date
AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
AND tr.term_id(+) = ps.term_id
AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
l_selected_recos selected_recos_table;
l_current_reco selected_recos_table;
log('arp_autoapply_api.insert_reference_recos(+)');
OPEN select_recos;
FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
log('Count : '||l_selected_recos.COUNT);
IF l_selected_recos.COUNT = 0 THEN
IF l_current_reco.count > 0 THEN
process_single_reco(l_current_reco
, 'REFERENCE NUMBER');
l_current_reco.DELETE;
insert_recos(p_request_id);
l_current_fetch_count := l_selected_recos.COUNT;
insert_recos(p_request_id);
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
insert_recos(p_request_id);
l_current_reco.DELETE;
log('arp_autoapply_api.insert_reference_recos(-)');
log('Exception from arp_autoapply_api.insert_reference_recos');
END insert_reference_recos;
* If the same transaction is selected for different references. *
* 11. AR_AA_DUPLICATE_RECOS : Header Level Validation *
* If two recommendations with same number is selected for a reference. *
* 12. AR_AA_MULT_RECOS : Header Level Validation *
* If more than one recommendations are valid for a reference. *
* 13. AR_AA_CUST_NOT_UNIQUE : Header Level Validation *
* If all the recommendations does not belong to a same customer in case*
* if the receipt is unidentified. *
* Finally at the end of validation the valid payment schedules selected *
* for application are locked. This is done to counter the possibility of a *
* deadlock if the process is run with multiple workers. In such a case a PS*
* may be selected for different references for different workers. *
* Final valid status at the end of validation : AR_AA_INV_LOCKED *
* NOTES - *
* 1. Validate Recos is called once per each worker. *
*
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE validate_trx_recos( p_req_id IN NUMBER
, p_worker_number IN NUMBER) IS
TYPE psid_tab IS TABLE OF ar_payment_schedules.payment_schedule_id%TYPE INDEX BY PLS_INTEGER;
UPDATE ar_cash_recos rec
SET rec.match_reason_code = 'AR_AA_DATE_MISMATCH'
WHERE rec.request_id = p_req_id
AND match_reason_code = 'AR_AM_INV_THRESHOLD'
AND EXISTS ( SELECT 'Date Not Matching'
FROM ar_cash_automatches am,
ar_cash_remit_refs_interim ref
WHERE am.automatch_id = rec.automatch_id
AND ref.remit_reference_id = rec.remit_reference_id
AND am.use_matching_date = 'ALWAYS'
AND trunc(rec.resolved_matching_date) <> NVL(ref.matching_reference_date, to_date('31/12/4712','DD/MM/YYYY'))
);
log('No. of recos updated to Date Mismatch: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_DATE_MISMATCH'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos r
WHERE match_reason_code = 'AR_AA_DATE_MISMATCH'
AND request_id = l.request_id)
AND request_id = p_req_id;
log('No. of reco lines updated to Date Mismatch: ' || SQL%ROWCOUNT );
/*UPDATE ar_cash_recos rec
SET rec.match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
WHERE rec.request_id = p_req_id
AND match_reason_code = 'AR_AM_INV_THRESHOLD'
AND EXISTS ( SELECT ref.remit_reference_id
FROM ar_cash_reco_lines lin,
ar_cash_automatches am,
ar_cash_remit_refs_interim ref
WHERE lin.request_id = p_req_id
AND am.automatch_id = rec.automatch_id
AND am.use_matching_amount = 'ALWAYS'
AND rec.recommendation_id = lin.recommendation_id
AND ref.remit_reference_id = rec.remit_reference_id
GROUP BY ref.remit_reference_id, NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code))
HAVING SUM(lin.amount_applied) <> NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code))
);
log('No. of recos updated to Amount Mismatch: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_AMOUNT_MISMATCH'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos r
WHERE match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
AND request_id = l.request_id)
AND request_id = p_req_id;
log('No. of reco lines updated to Amount Mismatch: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_CURR_NO_MATCH'
WHERE EXISTS (SELECT 'Inconsistent Currency'
FROM ar_cash_recos rec,
ar_cash_remit_refs_interim ref,
ar_payment_schedules ps
WHERE rec.recommendation_id = l.recommendation_id
AND ref.remit_reference_id = rec.remit_reference_id
AND ref.worker_number = p_worker_number
AND ps.payment_schedule_id = l.payment_schedule_id
AND ps.invoice_currency_code<> NVL(ref.invoice_currency_code,
ps.invoice_currency_code))
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to No Currency Match: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_NO_XCURR_RATE'
WHERE EXISTS (SELECT 'No X Rate Info'
FROM ar_payment_schedules ps
WHERE l.payment_schedule_id = ps.payment_schedule_id
AND l.receipt_currency_code <> ps.invoice_currency_code
AND (l.trans_to_receipt_rate IS NULL
OR l.trans_to_receipt_rate = -1))
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to No Exchange Rate: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_INVALID_RATE'
WHERE (EXISTS (SELECT 'Same Currency'
FROM ar_payment_schedules ps
WHERE l.payment_schedule_id = ps.payment_schedule_id
AND l.receipt_currency_code = ps.invoice_currency_code
AND l.trans_to_receipt_rate IS NOT NULL)
OR EXISTS (SELECT 'Wrong rate for fixed rate currency'
FROM ar_payment_schedules ps
WHERE l.payment_schedule_id = ps.payment_schedule_id
AND GL_CURRENCY_API.IS_FIXED_RATE(ps.invoice_currency_code,
l.receipt_currency_code,
l.receipt_date) = 'Y'
AND l.trans_to_receipt_rate <> ROUND(GL_CURRENCY_API.GET_RATE_SQL(
ps.invoice_currency_code,
l.receipt_currency_code,
l.receipt_date,
null), 38)))
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Invalid Rate: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = (SELECT CASE WHEN SIGN(l.amount_applied*ps.amount_due_remaining) = -1
THEN 'AR_AA_NAT_APP_VIO'
ELSE recommendation_reason
END
FROM ar_payment_schedules ps
WHERE ps.payment_schedule_id = l.payment_schedule_id)
WHERE request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Nat Appn Vio/Over Appn: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_MUL_APP_TRX'
WHERE ( EXISTS (SELECT 'PS already Applied'
FROM ar_cash_recos rec,
ar_cash_remit_refs_interim ref,
ar_receivable_applications ra
WHERE rec.recommendation_id = l.recommendation_id
AND ref.remit_reference_id = rec.remit_reference_id
AND ra.cash_receipt_id = ref.cash_receipt_id
AND ref.worker_number = p_worker_number
AND l.payment_schedule_id = ra.applied_payment_schedule_id
AND ra.display = 'Y')
)
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Multiple Application on same receipt: ' || SQL%ROWCOUNT );
/* Prevent application if the same PS is selected for any other reco
Since we are validating per worker, at the end of validation there is
a chance that same PS is selected by two receipts from diff workers */
/* UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_MUL_RECO_TRX'
WHERE EXISTS (SELECT 'PS eligible for more than one reference'
FROM ar_cash_reco_lines l1,
ar_cash_recos rec,
ar_cash_recos rec1
WHERE l.payment_schedule_id = l1.payment_schedule_id
AND l.recommendation_id <> l1.recommendation_id
AND rec.recommendation_id = l.recommendation_id
AND rec1.recommendation_id = l1.recommendation_id
AND rec.remit_reference_id <> rec1.remit_reference_id
AND l1.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND l1.request_id = p_req_id)
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Same Trx for multiple Recos: ' || SQL%ROWCOUNT );
UPDATE ar_cash_recos rec
SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
WHERE rec.recommendation_id IN
(SELECT recommendation_id
FROM ar_cash_recos
WHERE request_id = p_req_id
AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
(
SELECT resolved_matching_number, match_resolved_using, remit_reference_id
FROM ar_cash_recos rec
WHERE rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
HAVING COUNT(*) > 1
)
MINUS
SELECT recommendation_id
FROM ar_cash_recos rec
WHERE request_id = p_req_id
AND (resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date) IN
(
SELECT resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
FROM ar_cash_recos rec1
WHERE request_id = p_req_id
AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
(
SELECT resolved_matching_number, match_resolved_using, remit_reference_id
FROM ar_cash_recos rec
WHERE rec.request_id = p_req_id
GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
HAVING COUNT(*) > 1
)
AND trunc(rec1.resolved_matching_date) = (SELECT decode(am.use_matching_date,
'DUPLICATE', nvl(ref.matching_reference_date, rec1.resolved_matching_date),
rec1.resolved_matching_date)
FROM ar_cash_remit_refs_interim ref,
ar_cash_automatches am
WHERE ref.worker_number = p_worker_number
AND ref.remit_reference_id = rec1.remit_reference_id
AND am.automatch_id = rec1.automatch_id)
AND EXISTS ( SELECT ref.remit_reference_id
FROM ar_cash_reco_lines lin,
ar_cash_automatches am,
ar_cash_remit_refs_interim ref
WHERE lin.request_id = p_req_id
AND am.automatch_id = rec.automatch_id
AND am.use_matching_amount = 'DUPLICATE'
AND rec.recommendation_id = lin.recommendation_id
AND ref.remit_reference_id = rec1.remit_reference_id
GROUP BY ref.remit_reference_id, NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code))
HAVING SUM(lin.amount_applied) = NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code))
)
GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
HAVING count(*) = 1
)
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
)
AND rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Duplicate Recos: ' || SQL%ROWCOUNT );
UPDATE ar_cash_recos rec
SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
WHERE request_id = p_req_id
AND (resolved_matching_number, match_resolved_using, remit_reference_id )
IN ( SELECT resolved_matching_number,
match_resolved_using ,
remit_reference_id
FROM ar_cash_recos rec
WHERE rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
GROUP BY resolved_matching_number,
match_resolved_using ,
remit_reference_id
HAVING COUNT(*) > 1);
log('No. of recos updated to Duplicate Recos: ' || SQL%ROWCOUNT );
SELECT rec.recommendation_id,
rec.remit_reference_id,
rec.resolved_matching_date,
ref.matching_reference_date,
sum(NVL(lin.amount_applied, 0)) amount_applied,
sum(NVL(lin.discount_taken_earned, 0)) discount_taken_earned,
ps.amount_due_remaining,
lin.customer_trx_id,
lin.receipt_date,
am.use_matching_date,
am.use_matching_amount
FROM ar_cash_recos rec,
ar_cash_reco_lines lin,
ar_cash_remit_refs_interim ref,
ar_cash_automatches am,
ar_payment_schedules ps
WHERE rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
AND ref.remit_reference_id = rec.remit_reference_id
AND ref.worker_number = p_worker_number
AND lin.recommendation_id = rec.recommendation_id
AND am.automatch_id = rec.automatch_id
AND ps.customer_trx_id = lin.customer_trx_id
GROUP BY rec.recommendation_id,
rec.remit_reference_id,
rec.resolved_matching_date,
ref.matching_reference_date,
ps.amount_due_remaining,
lin.customer_trx_id,
lin.receipt_date,
am.use_matching_date,
am.use_matching_amount
ORDER BY rec.remit_reference_id, rec.recommendation_id;
l_rm_frm_dup_rec.DELETE(l_rm_frm_dup_count-1);
UPDATE ar_cash_recos
SET match_reason_code = 'AR_AM_INV_THRESHOLD'
WHERE request_id = p_req_id
AND match_reason_code = 'AR_AA_DUPLICATE_RECOS'
AND recommendation_id = l_rm_frm_dup_rec(i);
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_DUPLICATE_RECOS'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos r
WHERE match_reason_code = 'AR_AA_DUPLICATE_RECOS'
AND request_id = l.request_id)
AND request_id = p_req_id;
log('No. of reco lines updated to Duplicate Recos: ' || SQL%ROWCOUNT );
UPDATE ar_cash_recos rec
SET match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
WHERE remit_reference_id IN (SELECT remit_reference_id
FROM ar_cash_remit_refs_interim ref1
WHERE cash_receipt_id IN (
SELECT cr.cash_receipt_id
FROM ar_cash_receipts cr,
ar_cash_remit_refs_interim ref,
ar_cash_recos rec,
ar_cash_reco_lines recl
WHERE cr.autoapply_flag = 'Y'
AND cr.pay_from_customer IS NULL
AND cr.cash_receipt_id = ref.cash_receipt_id
AND ref.remit_reference_id = rec.remit_reference_id
AND ref.worker_number = p_worker_number
AND recl.recommendation_id = rec.recommendation_id
AND recl.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND EXISTS (SELECT 'Reco of Different Customer'
FROM ar_cash_remit_refs_interim ref2,
ar_cash_recos rec1,
ar_cash_reco_lines recl1
WHERE ref2.cash_receipt_id = ref.cash_receipt_id
AND rec1.remit_reference_id = ref2.remit_reference_id
AND recl1.recommendation_id = rec1.recommendation_id
AND recl1.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND rec.recommendation_id <> rec1.recommendation_id
AND rec.pay_from_customer <> rec1.pay_from_customer
AND rec1.request_id = p_req_id
AND ref2.worker_number = p_worker_number))
AND ref1.worker_number = p_worker_number)
AND match_reason_code = 'AR_AM_INV_THRESHOLD'
AND request_id = p_req_id;
log('No. of recos updated to Non Unique Customer: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_CUST_NOT_UNIQUE'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos
WHERE match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
AND request_id = p_req_id)
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
UPDATE ar_cash_recos rec
SET match_reason_code = 'AR_AA_MULT_RECOS'
WHERE EXISTS (SELECT 'Multiple Recos'
FROM ar_cash_recos rec1,
ar_cash_reco_lines lin
WHERE rec1.remit_reference_id = rec.remit_reference_id
AND rec1.recommendation_id <> rec.recommendation_id
AND lin.recommendation_id = rec1.recommendation_id
AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND (CASE
WHEN rec1.match_score_value > rec.match_score_value THEN 'T'
WHEN rec1.match_score_value = rec.match_score_value THEN
CASE WHEN rec1.priority >= rec.priority THEN 'T'
END
END) = 'T'
AND lin.request_id = p_req_id)
AND EXISTS (SELECT 'Applicable Reco Exist'
FROM ar_cash_reco_lines lin
WHERE lin.recommendation_id = rec.recommendation_id
AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND lin.request_id = p_req_id)
AND request_id = p_req_id
AND match_reason_code = 'AR_AM_INV_THRESHOLD';
log('No. of recos updated to Multiple Recos: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_MULT_RECOS'
WHERE EXISTS (SELECT 'Many Types of Recos'
FROM ar_cash_recos rec
WHERE l.recommendation_id = rec.recommendation_id
AND rec.match_reason_code = 'AR_AA_MULT_RECOS'
AND rec.request_id = p_req_id)
AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND request_id = p_req_id;
log('No. of reco lines updated to Multiple Recos: ' || SQL%ROWCOUNT );
UPDATE ar_cash_recos rec
SET match_reason_code = 'AR_AA_REMIT_EXCEEDED'
WHERE remit_reference_id IN
(SELECT remit_reference_id
FROM ar_cash_remit_refs_interim
WHERE cash_receipt_id IN (
SELECT ps.cash_receipt_id
FROM ar_payment_schedules ps,
ar_cash_receipts cr,
ar_cash_remit_refs_interim ref,
ar_cash_recos rec,
ar_cash_reco_lines recl
WHERE ps.cash_receipt_id = cr.cash_receipt_id
AND ps.cash_receipt_id = ref.cash_receipt_id
AND ref.remit_reference_id = rec.remit_reference_id
AND ref.worker_number = p_worker_number
AND recl.recommendation_id = rec.recommendation_id
AND recl.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND recl.request_id = p_req_id
GROUP BY ps.cash_receipt_id, ps.amount_due_remaining
HAVING ps.amount_due_remaining*-1 < SUM(NVL(recl.amount_applied_from,
recl.amount_applied))))
AND match_reason_code = 'AR_AM_INV_THRESHOLD'
AND request_id = p_req_id;
log('No. of recos updated to Remittance amount exceeded: ' || SQL%ROWCOUNT );
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_REMIT_EXCEEDED'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos
WHERE match_reason_code = 'AR_AA_REMIT_EXCEEDED'
AND request_id = p_req_id)
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
log('No. of reco lines updated to Remittance amount exceeded: ' || SQL%ROWCOUNT );
/* * Update the unidentified cash receipts with the customer number of *
* the valid recommendations. * */
DECLARE
CURSOR unid_receipts IS
SELECT distinct cash_receipt_id
FROM ar_cash_remit_refs_interim
WHERE worker_number = p_worker_number
AND customer_id IS NULL;
SELECT decode(count(distinct rec.pay_from_customer),
1, max(rec.pay_from_customer),
NULL)
INTO l_customer_id
FROM ar_cash_recos rec,
ar_cash_remit_refs_interim ref
WHERE rec.request_id = p_req_id
AND ref.cash_receipt_id = l_cash_receipt_id
AND rec.remit_reference_id = ref.remit_reference_id
AND ref.worker_number = p_worker_number
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD';
AR_RECEIPT_UPDATE_API_PUB.update_receipt_unid_to_unapp(
p_api_version => 1.0,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
--p_commit => FND_API.G_TRUE,
p_cash_receipt_id => l_cash_receipt_id,
p_pay_from_customer => l_customer_id,
x_status => v_status
);
UPDATE ar_cash_recos
SET match_reason_code = 'AR_AA_CUST_UNID'
WHERE remit_reference_id IN (SELECT ref1.remit_reference_id
FROM ar_cash_remit_refs_interim ref1
WHERE ref1.cash_receipt_id = l_cash_receipt_id)
AND match_reason_code = 'AR_AM_INV_THRESHOLD'
AND request_id = p_req_id;
UPDATE ar_cash_reco_lines l
SET recommendation_reason = 'AR_AA_CUST_UNID'
WHERE recommendation_id IN (SELECT recommendation_id
FROM ar_cash_recos
WHERE match_reason_code = 'AR_AA_CUST_UNID'
AND request_id = p_req_id)
AND request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
SELECT ps.payment_schedule_id
BULK COLLECT INTO locked_ps_records
FROM ar_payment_schedules ps,
ar_cash_reco_lines lines
WHERE lines.request_id = p_req_id
AND lines.recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND ps.payment_schedule_id = lines.payment_schedule_id
FOR UPDATE OF ps.amount_due_remaining SKIP LOCKED;
UPDATE ar_cash_reco_lines
SET recommendation_reason = 'AR_AA_INV_LOCKED'
WHERE request_id = p_req_id
AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
AND payment_schedule_id = locked_ps_records(i);
* Apply all valid recommendations and update the reference with resolved *
* matching numbers. *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : p_worker_number Current Worker Number *
* p_req_id Request ID *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. Select Valid Recommendation lines ( with status 'AR_AA_INV_LOCKED') *
* 2. For all recommendation lines *
* 3. Select Next Recommendation Line *
* 4. Apply the transaction *
* 5. Compute the remaining balace for the reference *
* 6. If balance > 0 go to Step 3. *
* 7. Update the referene with Resolved matching number, currency etc . *
* 8. Delete the recommendations for the references that were (automatically)
* applied. *
* NOTES - *
* 1. APPLY_TRX_RECOS is called once per each worker. *
* *
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE apply_trx_recos(p_req_id IN NUMBER
, p_worker_number IN NUMBER) IS
l_return_status VARCHAR2(10);
SELECT distinct rec.remit_reference_id
FROM ar_cash_recos rec
WHERE rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
AND rec.match_resolved_using <> 'BALANCE FORWARD BILL';
SELECT ref.cash_receipt_id,
rec.remit_reference_id,
NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code)) ref_amount_applied,
lin.amount_applied,
lin.payment_schedule_id,
lin.amount_applied_from,
lin.trans_to_receipt_rate,
lin.recommendation_id,
lin.line_number,
lin.receipt_currency_code,
rec.resolved_match_currency
FROM ar_cash_remit_refs_interim ref,
ar_cash_recos rec,
ar_cash_reco_lines lin
WHERE rec.remit_reference_id = p_remit_reference_id
AND ref.remit_reference_id = rec.remit_reference_id
AND rec.recommendation_id = lin.recommendation_id
AND ref.worker_number = p_worker_number
AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
AND rec.match_resolved_using <> 'BALANCE FORWARD BILL'
AND lin.request_id = p_req_id
ORDER BY lin.recommendation_id, lin.line_number;
SELECT rec.remit_reference_id,
rec.cons_inv_id,
rec.recommendation_id,
ref.amount_applied,
ref.amount_applied_from,
ref.cash_receipt_id,
cr.currency_code
FROM ar_cash_recos rec,
ar_cash_remit_refs_interim ref,
ar_cash_receipts cr
WHERE rec.request_id = p_req_id
AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
AND rec.match_resolved_using = 'BALANCE FORWARD BILL'
AND ref.remit_reference_id = rec.remit_reference_id
AND ref.worker_number = p_worker_number
AND cr.cash_receipt_id = ref.cash_receipt_id;
SELECT lin.recommendation_id,
lin.line_number,
lin.payment_schedule_id,
lin.customer_trx_id,
lin.amount_applied,
lin.amount_applied_from,
lin.trans_to_receipt_rate,
ps.invoice_currency_code
FROM ar_cash_reco_lines lin,
ar_payment_schedules ps
WHERE lin.recommendation_id = p_reco_id
AND lin.request_id = p_req_id
AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
AND ps.payment_schedule_id = lin.payment_schedule_id
ORDER BY lin.recommendation_id, lin.line_number;
UPDATE ar_cash_reco_lines
SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
WHERE recommendation_id = app_line.recommendation_id
AND line_number = app_line.line_number;
UPDATE ar_cash_reco_lines
SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
WHERE recommendation_id = bfb_line.recommendation_id
AND line_number = bfb_line.line_number;
UPDATE ar_cash_remit_refs ref
SET (receipt_reference_status,
resolved_matching_number,
auto_applied,
match_score_value,
resolved_matching_date,
invoice_currency_code,
match_resolved_using) =(SELECT 'AR_AA_INV_APPLIED',
rec.resolved_matching_number,
'Y',
rec.match_score_value,
rec.resolved_matching_date,
rec.resolved_match_currency,
rec.automatch_id
FROM ar_cash_recos rec,
ar_cash_reco_lines lin
WHERE ref.remit_reference_id = rec.remit_reference_id
AND lin.recommendation_id = rec.recommendation_id
AND rec.request_id = p_req_id
AND recommendation_type = 'TRX'
AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
AND rownum =1)
WHERE EXISTS (SELECT 'Found Match'
FROM ar_cash_recos rec,
ar_cash_reco_lines lin
WHERE ref.remit_reference_id = rec.remit_reference_id
AND lin.recommendation_id = rec.recommendation_id
AND lin.request_id = p_req_id
AND recommendation_type = 'TRX'
AND lin.recommendation_reason = 'AR_AA_INV_LOCKED');
log('No. of References updated with Resolved Matching Number: ' || SQL%ROWCOUNT );
DELETE FROM ar_cash_reco_lines lin
WHERE EXISTS (SELECT 'Delete Recos'
FROM ar_cash_remit_refs ref,
ar_cash_recos rec
WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
AND lin.recommendation_id = rec.recommendation_id
AND rec.remit_reference_id = ref.remit_reference_id)
AND request_id = p_req_id;
DELETE FROM ar_cash_recos rec
WHERE EXISTS (SELECT 'Delete Recos'
FROM ar_cash_remit_refs ref
WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
AND rec.remit_reference_id = ref.remit_reference_id)
AND request_id = p_req_id
AND recommendation_type = 'TRX';
log('No. of Recos deleted: ' || SQL%ROWCOUNT );
PROCEDURE copy_current_record( p_current_reco IN OUT NOCOPY selected_recos_table
, p_selected_recos IN selected_recos_table
, p_index IN NUMBER) IS
i NUMBER;
p_current_reco(i).remit_reference_id := p_selected_recos(p_index).remit_reference_id;
p_current_reco(i).ref_amount_applied := p_selected_recos(p_index).ref_amount_applied;
p_current_reco(i).ref_amount_applied_from := p_selected_recos(p_index).ref_amount_applied_from;
p_current_reco(i).ref_trans_to_receipt_rate := p_selected_recos(p_index).ref_trans_to_receipt_rate;
p_current_reco(i).payment_schedule_id := p_selected_recos(p_index).payment_schedule_id;
p_current_reco(i).amount_applied := p_selected_recos(p_index).amount_applied;
p_current_reco(i).amount_applied_from := p_selected_recos(p_index).amount_applied_from;
p_current_reco(i).cash_receipt_id := p_selected_recos(p_index).cash_receipt_id;
p_current_reco(i).pay_from_customer := p_selected_recos(p_index).pay_from_customer;
p_current_reco(i).cr_customer_site_use_id := p_selected_recos(p_index).cr_customer_site_use_id;
p_current_reco(i).amount_due_original := p_selected_recos(p_index).amount_due_original;
p_current_reco(i).amount_due_remaining := p_selected_recos(p_index).amount_due_remaining;
p_current_reco(i).discount_taken_earned := p_selected_recos(p_index).discount_taken_earned;
p_current_reco(i).discount_taken_unearned := p_selected_recos(p_index).discount_taken_unearned;
p_current_reco(i).customer_trx_id := p_selected_recos(p_index).customer_trx_id;
p_current_reco(i).customer_id := p_selected_recos(p_index).customer_id;
p_current_reco(i).customer_site_use_id := p_selected_recos(p_index).customer_site_use_id;
p_current_reco(i).resolved_matching_number := p_selected_recos(p_index).resolved_matching_number;
p_current_reco(i).terms_sequence_number := p_selected_recos(p_index).terms_sequence_number;
p_current_reco(i).resolved_matching_date := p_selected_recos(p_index).resolved_matching_date;
p_current_reco(i).trx_date := p_selected_recos(p_index).trx_date;
p_current_reco(i).resolved_matching_class := p_selected_recos(p_index).resolved_matching_class;
p_current_reco(i).resolved_match_currency := p_selected_recos(p_index).resolved_match_currency;
p_current_reco(i).amount_applied := p_selected_recos(p_index).amount_applied;
p_current_reco(i).amount_applied_from := p_selected_recos(p_index).amount_applied_from;
p_current_reco(i).trans_to_receipt_rate := p_selected_recos(p_index).trans_to_receipt_rate;
p_current_reco(i).payment_schedule_id := p_selected_recos(p_index).payment_schedule_id;
p_current_reco(i).match_score_value := p_selected_recos(p_index).match_score_value;
p_current_reco(i).org_id := p_selected_recos(p_index).org_id;
p_current_reco(i).term_id := p_selected_recos(p_index).term_id;
p_current_reco(i).automatch_id := p_selected_recos(p_index).automatch_id;
p_current_reco(i).use_matching_date := p_selected_recos(p_index).use_matching_date;
p_current_reco(i).use_matching_amount := p_selected_recos(p_index).use_matching_amount;
p_current_reco(i).auto_match_threshold := p_selected_recos(p_index).auto_match_threshold;
p_current_reco(i).priority := p_selected_recos(p_index).priority;
p_current_reco(i).receipt_currency_code := p_selected_recos(p_index).receipt_currency_code;
p_current_reco(i).receipt_date := p_selected_recos(p_index).receipt_date;
p_current_reco(i).allow_overapplication_flag := p_selected_recos(p_index).allow_overapplication_flag;
p_current_reco(i).partial_discount_flag := p_selected_recos(p_index).partial_discount_flag;
p_current_reco(i).reco_num := p_selected_recos(p_index).reco_num;
PROCEDURE process_single_reco(p_current_reco IN OUT NOCOPY selected_recos_table
, p_match_resolved_using IN VARCHAR2) IS
l_block_index NUMBER;
SELECT ar_cash_recos_s.nextval
INTO l_recommendation_id
FROM dual;
PROCEDURE populate_reco_line_struct(p_current_reco IN selected_recos_table
, p_match_resolved_using IN VARCHAR2
, p_recommendation_id IN NUMBER
, p_recommendation_reason IN VARCHAR2) IS
l_index NUMBER;
reco_id_arr.DELETE;
remit_ref_id_arr.DELETE;
customer_id_arr.DELETE;
customer_site_use_id_arr.DELETE;
resolved_matching_number_arr.DELETE;
resolved_matching_date_arr.DELETE;
resolved_matching_class_arr.DELETE;
resolved_match_currency_arr.DELETE;
match_resolved_using_arr.DELETE;
cons_inv_id_arr.DELETE;
match_score_value_arr.DELETE;
match_reason_code_arr.DELETE;
org_id_arr.DELETE;
priority_arr.DELETE;
reco_num_arr.DELETE;
customer_trx_id_arr.DELETE;
payment_schedule_id_arr.DELETE;
amount_applied_arr.DELETE;
amount_applied_from_arr.DELETE;
trans_to_receipt_rate_arr.DELETE;
receipt_currency_code_arr.DELETE;
receipt_date_arr.DELETE;
recommendation_reason_arr.DELETE;
discount_taken_earned_arr.DELETE;
discount_taken_unearned_arr.DELETE;
PROCEDURE insert_recos(p_request_id IN NUMBER) IS
l_reco_index NUMBER;
log('insert_recos()+');
INSERT
INTO ar_cash_recos_all (
recommendation_id,
recommendation_type,
recommendation_source,
remit_reference_id,
pay_from_customer,
customer_site_use_id,
resolved_matching_number,
resolved_matching_date,
resolved_matching_class,
resolved_match_currency,
cons_inv_id,
match_resolved_using,
match_score_value,
match_reason_code,
recommendation_status,
autoapply_status,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
automatch_id,
priority)
VALUES (reco_id_arr(l_reco_index),
'TRX',
'AUTOMATCH',
remit_ref_id_arr(l_reco_index),
customer_id_arr(l_reco_index),
customer_site_use_id_arr(l_reco_index),
resolved_matching_number_arr(l_reco_index),
resolved_matching_date_arr(l_reco_index),
resolved_matching_class_arr(l_reco_index),
resolved_match_currency_arr(l_reco_index),
cons_inv_id_arr(l_reco_index),
match_resolved_using_arr(l_reco_index),
match_score_value_arr(l_reco_index),
match_reason_code_arr(l_reco_index),
'CREATED',
'NONE',
org_id_arr(l_reco_index),
g_created_by,
SYSDATE,
g_last_updated_by,
SYSDATE,
g_last_update_login,
g_program_application_id,
g_program_id,
SYSDATE,
p_request_id,
automatch_id_arr(l_reco_index),
priority_arr(l_reco_index));
INSERT INTO ar_cash_reco_lines_all (
recommendation_id,
line_number,
customer_trx_id,
payment_schedule_id,
amount_applied,
amount_applied_from,
trans_to_receipt_rate,
receipt_currency_code,
receipt_date,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
recommendation_reason,
discount_taken_earned)
SELECT reco_id_arr(l_reco_line_index),
reco_num_arr(l_reco_line_index),
customer_trx_id_arr(l_reco_line_index),
payment_schedule_id_arr(l_reco_line_index),
amount_applied_arr(l_reco_line_index),
amount_applied_from_arr(l_reco_line_index),
trans_to_receipt_rate_arr(l_reco_line_index),
receipt_currency_code_arr(l_reco_line_index),
receipt_date_arr(l_reco_line_index),
org_id_arr(l_reco_line_index),
g_created_by,
SYSDATE,
g_last_updated_by,
SYSDATE,
g_last_update_login,
g_program_application_id,
g_program_id,
SYSDATE,
p_request_id,
match_reason_code_arr(l_reco_line_index),
discount_taken_earned_arr(l_reco_line_index)
FROM DUAL;
log('insert_recos()+');
log('Exception from arp_autoapply_api.insert_recos');
END insert_recos;
SELECT NVL(NVL(site.discount_terms, cust.discount_terms),'Y')
INTO l_allow_discount
FROM
hz_customer_profiles cust
, hz_customer_profiles site
WHERE
cust.cust_account_id = l_customer_id
AND cust.site_use_id IS NULL
AND site.cust_account_id (+) = cust.cust_account_id
AND site.site_use_id (+) = l_bill_to_site_use_id;
SELECT NVL(NVL(site.discount_grace_days, cust.discount_grace_days),0)
INTO l_grace_days
FROM
hz_customer_profiles cust
, hz_customer_profiles site
, hz_cust_accounts cust_acct
WHERE
cust_acct.cust_account_id = l_customer_id
AND cust.cust_account_id = cust_acct.cust_account_id
AND cust.site_use_id IS NULL
AND site.cust_account_id (+) = cust_acct.cust_account_id
AND site.site_use_id (+) = NVL(l_BILL_TO_SITE_USE_ID, -4444);
* DELETE_INTERIM_RECORDS() *
* DESCRIPTION *
* Delete records from ar_cash_remit_refs_interim. *
* SCOPE - LOCAL *
* ARGUMENTS *
* IN : None *
* OUT : None *
* *
* RETURNS NONE *
* ALGORITHM *
* 1. Delete records from ar_cash_remit_refs_interim. *
* NOTES - *
* 1. This is called from the XML report *
* *
* MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
* *
+===========================================================================*/
PROCEDURE delete_interim_records IS
BEGIN
IF (PG_DEBUG IN ('Y', 'C')) THEN
log('arp_autoapply_api.delete_interim_records()+' );
DELETE FROM ar_cash_remit_refs_interim;
log('No. of records deleted: ' || SQL%ROWCOUNT );
log('arp_autoapply_api.delete_interim_records(-)' );
log('Exception from arp_autoapply_api.delete_interim_records');
END delete_interim_records;