[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTOAPPLY_API
Source
1 PACKAGE BODY ARP_AUTOAPPLY_API AS
2 /*$Header: ARATAPPB.pls 120.0.12010000.11 2009/05/12 07:46:11 aghoraka noship $*/
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4 G_PROGRAM_ID NUMBER := 111;
5 G_PROGRAM_APPLICATION_ID NUMBER := 222;
6 G_CREATED_BY NUMBER := -222;
7 G_LAST_UPDATED_BY NUMBER := -222;
8 G_LAST_UPDATE_LOGIN NUMBER := -222;
9 g_next_reco_id NUMBER;
10 g_prev_reco_num NUMBER;
11 G_MAX_ARRAY_SIZE NUMBER := 1000;
12
13 TYPE l_processed_rules_tab IS TABLE OF AR_CASH_AUTOMATCHES.AUTOMATCH_ID%TYPE
14 INDEX BY BINARY_INTEGER;
15 l_processed_rules l_processed_rules_tab;
16 g_reco_index NUMBER := 0;
17 reco_id_arr reco_id_tab;
18 remit_ref_id_arr remit_ref_id_tab;
19 customer_id_arr customer_id_tab;
20 customer_site_use_id_arr customer_site_use_id_tab;
21 resolved_matching_number_arr resolved_matching_number_tab;
22 resolved_matching_date_arr resolved_matching_date_tab;
23 resolved_matching_class_arr resolved_matching_class_tab;
24 resolved_match_currency_arr resolved_match_currency_tab;
25 match_resolved_using_arr match_resolved_using_tab;
26 cons_inv_id_arr cons_inv_id_tab;
27 match_score_value_arr match_score_value_tab;
28 match_reason_code_arr match_reason_code_tab;
29 org_id_arr org_id_tab;
30 automatch_id_arr automatch_id_tab;
31 priority_arr priority_tab;
32 reco_num_arr reco_num_tab;
33 customer_trx_id_arr customer_trx_id_tab;
34 payment_schedule_id_arr payment_schedule_id_tab;
35 amount_applied_arr amount_applied_tab;
36 amount_applied_from_arr amount_applied_from_tab;
37 trans_to_receipt_rate_arr trans_to_receipt_rate_tab;
38 receipt_currency_code_arr receipt_currency_code_tab;
39 receipt_date_arr receipt_date_tab;
40 recommendation_reason_arr recommendation_reason_tab;
41 discount_taken_earned_arr discount_taken_earned_tab;
42 discount_taken_unearned_arr discount_taken_unearned_tab;
43
44 PROCEDURE gen_str_transformations(p_rule_id IN NUMBER
45 , x_trans_format_str OUT NOCOPY VARCHAR2
46 , x_rem_format_str OUT NOCOPY VARCHAR2
47 , x_trans_float_str OUT NOCOPY VARCHAR2
48 , x_rem_float_str OUT NOCOPY VARCHAR2);
49
50 PROCEDURE insert_invoice_recos (p_automatch_id IN NUMBER
51 , p_use_matching_date IN VARCHAR2
52 , p_trans_format_str IN VARCHAR2
53 , p_rem_format_str IN VARCHAR2
54 , p_trans_float_str IN VARCHAR2
55 , p_rem_float_str IN VARCHAR2
56 , p_worker_number IN NUMBER
57 , p_request_id IN NUMBER);
58
59 PROCEDURE insert_po_recos (p_automatch_id IN NUMBER
60 , p_use_matching_date IN VARCHAR2
61 , p_trans_format_str IN VARCHAR2
62 , p_rem_format_str IN VARCHAR2
63 , p_trans_float_str IN VARCHAR2
64 , p_rem_float_str IN VARCHAR2
65 , p_worker_number IN NUMBER
66 , p_request_id IN NUMBER);
67
68 PROCEDURE insert_so_recos (p_automatch_id IN NUMBER
69 , p_use_matching_date IN VARCHAR2
70 , p_trans_format_str IN VARCHAR2
71 , p_rem_format_str IN VARCHAR2
72 , p_trans_float_str IN VARCHAR2
73 , p_rem_float_str IN VARCHAR2
74 , p_worker_number IN NUMBER
75 , p_request_id IN NUMBER);
76
77 PROCEDURE insert_contract_recos (p_automatch_id IN NUMBER
78 , p_use_matching_date IN VARCHAR2
79 , p_trans_format_str IN VARCHAR2
80 , p_rem_format_str IN VARCHAR2
81 , p_trans_float_str IN VARCHAR2
82 , p_rem_float_str IN VARCHAR2
83 , p_worker_number IN NUMBER
84 , p_request_id IN NUMBER);
85
86 PROCEDURE insert_attribute_recos (p_automatch_id IN NUMBER
87 , p_use_matching_date IN VARCHAR2
88 , p_trans_format_str IN VARCHAR2
89 , p_rem_format_str IN VARCHAR2
90 , p_trans_float_str IN VARCHAR2
91 , p_rem_float_str IN VARCHAR2
92 , p_worker_number IN NUMBER
93 , p_attribute_number IN VARCHAR2
94 , p_request_id IN NUMBER);
95
96 PROCEDURE insert_waybill_recos (p_automatch_id IN NUMBER
97 , p_use_matching_date IN VARCHAR2
98 , p_trans_format_str IN VARCHAR2
99 , p_rem_format_str IN VARCHAR2
100 , p_trans_float_str IN VARCHAR2
101 , p_rem_float_str IN VARCHAR2
102 , p_worker_number IN NUMBER
103 , p_request_id IN NUMBER);
104
105 PROCEDURE insert_bfb_recos (p_automatch_id IN NUMBER
106 , p_use_matching_date IN VARCHAR2
107 , p_trans_format_str IN VARCHAR2
108 , p_rem_format_str IN VARCHAR2
109 , p_trans_float_str IN VARCHAR2
110 , p_rem_float_str IN VARCHAR2
111 , p_worker_number IN NUMBER
112 , p_request_id IN NUMBER);
113
114 PROCEDURE insert_reference_recos (p_automatch_id IN NUMBER
115 , p_use_matching_date IN VARCHAR2
116 , p_trans_format_str IN VARCHAR2
117 , p_rem_format_str IN VARCHAR2
118 , p_trans_float_str IN VARCHAR2
119 , p_rem_float_str IN VARCHAR2
120 , p_worker_number IN NUMBER
121 , p_request_id IN NUMBER);
122
123 PROCEDURE validate_trx_recos( p_req_id IN NUMBER
124 , p_worker_number IN NUMBER);
125
126 PROCEDURE apply_trx_recos(p_req_id IN NUMBER
127 , p_worker_number IN NUMBER);
128
129 PROCEDURE copy_current_record( p_current_reco IN OUT NOCOPY selected_recos_table
130 , p_selected_recos IN selected_recos_table
131 , p_index IN NUMBER);
132
133 PROCEDURE process_single_reco(p_current_reco IN OUT NOCOPY selected_recos_table
134 , p_match_resolved_using IN VARCHAR2);
135
136 PROCEDURE clear_reco_lines_struct;
137
138 PROCEDURE populate_reco_line_struct(p_current_reco IN selected_recos_table
139 , p_match_resolved_using IN VARCHAR2
140 , p_recommendation_id IN NUMBER
141 , p_recommendation_reason IN VARCHAR2);
142
143 PROCEDURE insert_recos(p_request_id IN NUMBER);
144
145 PROCEDURE calc_amount_app_and_disc(
146 p_customer_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE
147 , p_bill_to_site_use_id IN AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE
148 , p_invoice_currency_code IN AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE
149 , p_ps_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE
150 , p_term_id IN AR_PAYMENT_SCHEDULES.term_id%TYPE
151 , p_terms_sequence_number IN AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE
152 , p_trx_date IN AR_PAYMENT_SCHEDULES.trx_date%TYPE
153 , p_allow_overapp_flag IN RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE
154 , p_partial_discount_flag IN RA_TERMS.partial_discount_flag%TYPE
155 , p_input_amount IN AR_CASH_REMIT_REFS.amount_applied%TYPE
156 , p_amount_due_original IN AR_PAYMENT_SCHEDULES.amount_due_original%TYPE
157 , p_amount_due_remaining IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE
158 , p_discount_taken_earned IN AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE
159 , p_discount_taken_unearned IN AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE
160 , p_cash_receipt_id IN AR_CASH_RECEIPTS.cash_receipt_id%TYPE
161 , x_out_amount_to_apply OUT NOCOPY NUMBER
162 , x_out_discount_to_take OUT NOCOPY NUMBER);
163
164 PROCEDURE calc_amt_applied_from(
165 p_currency_code IN VARCHAR2,
166 p_amount_applied IN ar_payments_interface.amount_applied1%type,
167 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
168 amount_applied_from OUT NOCOPY ar_payments_interface.amount_applied_from1%type);
169
170 PROCEDURE calc_amt_applied(
171 p_invoice_currency_code IN VARCHAR2,
172 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
173 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
174 amount_applied OUT NOCOPY ar_payments_interface.amount_applied1%type);
175
176 /*===========================================================================+
177 * PROCEDURE *
178 * LOG() *
179 * DESCRIPTION *
180 * Writes the message to debug log. *
181 * SCOPE - LOCAL *
182 * ARGUMENTS *
183 * IN : p_msg - Message *
184 * OUT : NONE *
185 * RETURNS NONE *
186 * ALGORITHM *
187 * *
188 * NOTES - *
189 * *
190 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
191 * *
192 +===========================================================================*/
193
194 PROCEDURE log(p_msg VARCHAR2) IS
195 BEGIN
196 arp_standard.debug('AutoApply: ' || p_msg || ' : ' || TO_CHAR(SYSDATE,'DD/MM/YY hh:mi:ss'));
197 END;
198
199 /*===========================================================================+
200 * FUNCTION *
201 * GET_NEXT_RECO_ID() *
202 * DESCRIPTION *
203 * Generates the recommendation id from sequence ar_cash_recos_s *
204 * SCOPE - LOCAL *
205 * ARGUMENTS *
206 * IN : p_reco_num - Recommendation Number *
207 * RETURNS NUMBER *
208 * ALGORITHM *
209 * Generate a new sequence if p_reco_num passed is equal to 1. *
210 * NOTES - *
211 * The function will be called for every insert execution but we need to *
212 * generate a new sequence only for a new recommendation (not for each line)*
213 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
214 * *
215 +===========================================================================*/
216
217 FUNCTION get_next_reco_id( p_reco_num IN NUMBER)
218 RETURN NUMBER IS
219 l_reco_id NUMBER;
220 BEGIN
221 IF p_reco_num = 1 AND NVL(g_prev_reco_num, -1) <> 1 THEN
222 SELECT ar_cash_recos_s.nextval
223 INTO l_reco_id
224 FROM DUAL;
225 g_next_reco_id := l_reco_id;
226 g_prev_reco_num := p_reco_num;
227 ELSIF p_reco_num = 1 AND g_prev_reco_num = 1 THEN
228 g_prev_reco_num := -1;
229 ELSE
230 g_prev_reco_num := p_reco_num;
231 END IF;
232
233 RETURN g_next_reco_id;
234 END get_next_reco_id;
235
236 /*===========================================================================+
237 * FUNCTION *
238 * IS_RULE_PROCESSED() *
239 * DESCRIPTION *
240 * Checks if an Automatch Rule is already processed in the current run. *
241 * SCOPE - LOCAL *
242 * ARGUMENTS *
243 * IN : p_rule_id - Automatch Rule ID *
244 * OUT : NONE *
245 * RETURNS BOOLEAN *
246 * ALGORITHM *
247 * Table l_processed_rules is used to store the rules already processed in *
248 * the current run. The function first checks if a rule is present in the *
249 * table l_processed_rules via linear search. If present retuns TRUE. Other *
250 * wise add the rule to the table and retun FALSE. *
251 * NOTES - *
252 * *
253 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
254 * *
255 +===========================================================================*/
256
257 FUNCTION is_rule_processed ( p_rule_id IN NUMBER)
258 RETURN BOOLEAN IS
259 l_table_size NUMBER;
260 i NUMBER;
261 BEGIN
262 IF (PG_DEBUG IN ('Y', 'C')) THEN
263 log('arp_autoapply_api.is_rule_processed(+)');
264 log('Rule Id: '||p_rule_id);
265 END IF;
266 l_table_size := NVL(l_processed_rules.last, 0);
267
268 FOR i IN 1..l_table_size LOOP
269 IF l_processed_rules(i) = p_rule_id THEN
270 IF (PG_DEBUG IN ('Y', 'C')) THEN
271 log('Rule already processed.');
272 log('arp_autoapply_api.is_rule_processed(-)');
273 END IF;
274 RETURN TRUE;
275 END IF;
276 END LOOP;
277
278 l_processed_rules(l_table_size + 1) := p_rule_id;
279 IF (PG_DEBUG IN ('Y', 'C')) THEN
280 log('New Rule.');
281 log('arp_autoapply_api.is_rule_processed(-)');
282 END IF;
283 RETURN FALSE;
284 END is_rule_processed;
285
286 /*===========================================================================+
287 * PROCEDURE *
288 * GEN_STR_TRANSFORMATIONS() *
289 * DESCRIPTION *
290 * Generate regular expressions for remittance and document reference *
291 * string transformations using the setup at 'AutoMatch Rule'. *
292 * SCOPE - LOCAL *
293 * ARGUMENTS *
294 * IN : p_rule_id Automatch Rule Identifier. *
295 * OUT : x_trans_format_str Document Number transformation String
296 * x_rem_format_str Remittance Number transformation String
297 * *
298 * RETURNS NONE *
299 * ALGORITHM *
300 * 1. Fetch string type, location and padding values for the current auto *
301 * identifier. *
302 * 2. Build regular expressions based on the above values. *
303 * 3. If more than one transformation rows are added for any reference, *
304 * are combined using 'AND' operator. *
305 * NOTES - *
306 * Example : *
307 * String_Type_Code : Document *
308 * String_Location_Code : Front *
309 * Padding_Value_Code : ZERO *
310 * Number_Of_Positions : 3 *
311 * Padding_Value_Code : Space *
312 * Number_Of_Positions : 2 *
313 * Padding_Value_Code : Back *
314 * Padding_Value_Code : ANY *
315 * Number_Of_Positions : 2 *
316 * Transformation String : ^([0]{3}[ ]{2})(.*)(.{2})$ *
317 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
318 * *
319 +===========================================================================*/
320
321 PROCEDURE gen_str_transformations(p_rule_id IN NUMBER
322 , x_trans_format_str OUT NOCOPY VARCHAR2
323 , x_rem_format_str OUT NOCOPY VARCHAR2
324 , x_trans_float_str OUT NOCOPY VARCHAR2
325 , x_rem_float_str OUT NOCOPY VARCHAR2)IS
326 CURSOR rule_dtls (p_rule_id NUMBER) IS
327 SELECT string_type_code type,
328 string_location_code location,
329 DECODE(padding_value_code, 'ZERO', '[0]',
330 'SPACE', '[ ]',
331 'ANY', '.',
332 padding_value_code) value,
333 DECODE(padding_value_code, 'ANY', NVL(TO_CHAR(number_of_positions),'9999'),
334 'ZERO', NVL(TO_CHAR(number_of_positions),'1,'),
335 'SPACE', NVL(TO_CHAR(number_of_positions),'1,')) position
336 /* When no of positions is not mentioned replace all the occurences */
337 FROM ar_cash_automatch_dtls
338 WHERE automatch_id = p_rule_id
339 ORDER BY string_type_code ASC, string_location_code ASC, padding_sequence ASC;
340
341 l_trx_ft_exp VARCHAR2(1000) := '^(';
342 l_rmt_ft_exp VARCHAR2(1000) := '^(';
343 l_trx_fl_exp VARCHAR2(1000);
344 l_trx_bk_exp VARCHAR2(1000) := ')$';
345 l_rmt_bk_exp VARCHAR2(1000) := ')$';
346 l_rmt_fl_exp VARCHAR2(1000);
347
348 BEGIN
349 IF (PG_DEBUG IN ('Y', 'C')) THEN
350 log('arp_autoapply_api.gen_regexp(+)');
351 log('Rule Id: '||p_rule_id);
352 END IF;
353 FOR r_rule_dtls IN rule_dtls(p_rule_id) LOOP
354 IF (r_rule_dtls.type = 'DOCUMENT') THEN /* Document Number */
355 IF (r_rule_dtls.location = 'FRONT') THEN
356 l_trx_ft_exp := l_trx_ft_exp || r_rule_dtls.value || '{' || r_rule_dtls.position || '}';
357 ELSIF (r_rule_dtls.location = 'BACK') THEN
358 l_trx_bk_exp := r_rule_dtls.value || '{' || r_rule_dtls.position || '}' || l_trx_bk_exp;
359 ELSIF (r_rule_dtls.location = 'FLOAT') THEN
360 l_trx_fl_exp := l_trx_fl_exp || '|' || r_rule_dtls.value;
361 END IF;
362 ELSIF (r_rule_dtls.type = 'REMITTANCE') THEN /* Remittance Number */
363 IF (r_rule_dtls.location = 'FRONT') THEN
364 l_rmt_ft_exp := l_rmt_ft_exp || r_rule_dtls.value || '{' || r_rule_dtls.position || '}';
365 ELSIF (r_rule_dtls.location = 'BACK') THEN
366 l_rmt_bk_exp := r_rule_dtls.value || '{' || r_rule_dtls.position || '}' || l_rmt_bk_exp;
367 ELSIF (r_rule_dtls.location = 'FLOAT') THEN
368 l_rmt_fl_exp := l_rmt_fl_exp || '|' || r_rule_dtls.value;
369 END IF;
370 END IF;
371 END LOOP;
372
373 x_trans_format_str := l_trx_ft_exp || ')' || '(.*)' || '(' || l_trx_bk_exp;
374 x_trans_float_str := LTRIM(l_trx_fl_exp, '|');
375 x_rem_format_str := l_rmt_ft_exp || ')' || '(.*)' || '(' || l_rmt_bk_exp;
376 x_rem_float_str := LTRIM(l_rmt_fl_exp, '|');
377
378 IF (PG_DEBUG IN ('Y', 'C')) THEN
379 log('Transaction Expression : '|| l_trx_ft_exp || ')' || '(.*)' || '(' || l_trx_bk_exp);
380 log('Transaction Float Expression : ' || x_trans_float_str);
381 log('Remittance Expression : '|| l_rmt_ft_exp || ')' || '(.*)' || '(' || l_rmt_bk_exp);
382 log('Remittance Float Expression : ' || x_rem_float_str);
383 log('ar_automatch_pkg.gen_regexp(-)');
384 END IF;
385 EXCEPTION
386 WHEN OTHERS THEN
387 log('Exception from ar_automatch_pkg.gen_regexp');
388 log(SQLERRM);
389 RAISE;
390 END gen_str_transformations;
391
392 /*===========================================================================+
393 * PROCEDURE *
394 * AUTO_APPLY_MASTER() *
395 * DESCRIPTION *
396 * Automatic Cash Application Master Program *
397 * SCOPE - PUBLIC *
398 * ARGUMENTS *
399 * IN : p_org_id Operating Unit identifier *
400 * p_receipt_no_l Receipt Number Low *
401 * p_receipt_no_h Receipt Number High *
402 * p_batch_name_l Batch Name Low *
403 * p_batch_name_h Batch Name High *
404 * p_min_unapp_amt Minimun Unapplied Amount on the Receipt*
405 * p_receipt_date_l Receipt Date Low *
406 * p_receipt_date_h Receipt Date High *
407 * p_receipt_method_l Receipt Method Low *
408 * p_receipt_method_h Receipt Method High *
409 * p_customer_name_l Customer Name Low *
410 * p_customer_name_h Customer Name High *
411 * p_customer_no_l Customer Number Low *
412 * p_customer_no_h Customer Number High *
413 * p_batch_id Batch Identifier *
414 * p_transmission_id Transmission Identifier *
415 * p_called_from Calling Program Name *
416 * p_total_workers No of Instances *
417 * OUT : P_ERRBUF Error Message Buffer *
418 * P_RETCODE Return Code *
419 * *
420 * RETURNS NONE *
421 * ALGORITHM *
422 * 1. Delete data from ar_cash_remit_refs_interim, if any data is present. *
423 * The table is truncated at the end of each run. However if any data *
424 * exists inside the interim event necause of any unhandled exception in*
425 * the previous run, just a precautionary measure to retrunc the table *
426 * 2. Populate ar_cash_Remit_refs_interim with data from ar_cash_remit_refs*
427 * based on the parameters provided to the concurrent program. *
428 * 3. Update the references with status 'AR_AA_RULE_SET_INACTIVE' which *
429 * are associated to a rule set that is inactive. *
430 * 4. Spawn the child process or directly call auto_apply_child() process *
431 * based on the 'No of Instances' parameter. *
432 * 5. Update the references with status 'AR_AA_SUGG_FOUND'/'AR_AA_NO_MATCH'*
433 * based on the number of receommendations generated for the remittances*
434 * that are not automatically applied. *
435 * 6. Update the receipt's WORK_ITEM_EXCEPTION_REASON for the receipts that*
436 * have unapplied remittance lines at the end of the program. *
437 * NOTES - *
438 * This program is the starting point for 'AR_AUTOAPPLY_API'. This is *
439 * called from XML report *
440 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
441 * *
442 +===========================================================================*/
443
444 PROCEDURE auto_apply_master ( P_ERRBUF OUT NOCOPY VARCHAR2
445 , P_RETCODE OUT NOCOPY NUMBER
446 , p_org_id IN NUMBER
447 , p_receipt_no_l IN VARCHAR2
448 , p_receipt_no_h IN VARCHAR2
449 , p_batch_name_l IN VARCHAR2
450 , p_batch_name_h IN VARCHAR2
451 , p_min_unapp_amt IN NUMBER
452 , p_receipt_date_l IN VARCHAR2
453 , p_receipt_date_h IN VARCHAR2
454 , p_receipt_method_l IN VARCHAR2
455 , p_receipt_method_h IN VARCHAR2
456 , p_customer_name_l IN VARCHAR2
457 , p_customer_name_h IN VARCHAR2
458 , p_customer_no_l IN VARCHAR2
459 , p_customer_no_h IN VARCHAR2
460 , p_batch_id IN NUMBER
461 , p_transmission_id IN NUMBER
462 , p_called_from IN VARCHAR2
463 , p_total_workers IN NUMBER) IS
464
465 l_insert_stmt VARCHAR2(30000) := NULL;
466 l_from_clause VARCHAR2(1000) := NULL;
467 l_where_clause VARCHAR2(10000) := NULL;
468 l_use_cr VARCHAR2(1) := 'N';
469 l_use_rm VARCHAR2(1) := 'N';
470 l_use_bat VARCHAR2(1) := 'N';
471 l_use_cust VARCHAR2(1) := 'N';
472 l_use_party VARCHAR2(1) := 'N';
473 l_worker_number NUMBER;
474 l_complete BOOLEAN := FALSE;
475 l_receipt_date_low AR_CASH_RECEIPTS.receipt_date%TYPE;
476 l_receipt_date_high AR_CASH_RECEIPTS.receipt_date%TYPE;
477 l_errbuf VARCHAR2(1000);
478 l_retcode NUMBER;
479
480 insert_gt INTEGER;
481 l_rows_inserted INTEGER;
482
483 TYPE req_status_typ IS RECORD (
484 request_id NUMBER(15),
485 dev_phase VARCHAR2(255),
486 dev_status VARCHAR2(255),
487 message VARCHAR2(2000),
488 phase VARCHAR2(255),
489 status VARCHAR2(255));
490
491 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
492
493 l_req_status_tab req_status_tab_typ;
494
495 /*=======================================================================+
496 * PROCEDURE *
497 * SUBMIT_SUBREQUEST() - *
498 * DESCRIPTION *
499 * This procedure launches the child programs for AutoApply Process *
500 * *
501 * SCOPE - LOCAL *
502 * *
503 * ARGUMENTS : IN :p_worker_number - Worker Number *
504 * p_org_id - Operating Unit Identifier *
505 * *
506 * OUT : None *
507 * RETURNS : NONE *
508 * *
509 * NOTES - *
510 * *
511 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
512 +=======================================================================*/
513 PROCEDURE submit_subrequest ( p_worker_number IN NUMBER,
514 p_org_id IN NUMBER) IS
515 l_request_id NUMBER(15);
516 BEGIN
517 IF (PG_DEBUG IN ('Y', 'C')) THEN
518 log('arp_autoapply_api.submit_subrequest(+)');
519 log('Worker No : '|| p_worker_number);
520 END IF;
521
522 FND_REQUEST.SET_ORG_ID(p_org_id);
523
524 l_request_id := FND_REQUEST.submit_request( 'AR', 'ARATAPPC',
525 'Auto Cash Application Child Program',
526 SYSDATE,
527 FALSE,
528 p_worker_number);
529
530 IF (l_request_id = 0) THEN
531 log('Can not start for worker_id: ' ||p_worker_number );
532 P_ERRBUF := fnd_Message.get;
533 P_RETCODE := 2;
534 return;
535 ELSE
536 commit;
537 log('child request id: ' ||l_request_id || ' started for worker_id: ' ||p_worker_number );
538 END IF;
539
540 l_req_status_tab(p_worker_number).request_id := l_request_id;
541 IF (PG_DEBUG IN ('Y', 'C')) THEN
542 log('arp_autoapply_api.submit_subrequest(-)');
543 END IF;
544 EXCEPTION
545 WHEN OTHERS THEN
546 log('Exception from arp_autoapply_api.submit_subrequest');
547 log(SQLERRM);
548 RAISE;
549 END submit_subrequest;
550
551 BEGIN
552 IF (PG_DEBUG IN ('Y', 'C')) THEN
553 log('arp_autoapply_api.auto_apply_master(+)');
554 log('Org Id : ' || p_org_id);
555 log('Receipt Number From ' || p_receipt_no_l || ' To ' || p_receipt_no_h);
556 log('Batch Name From ' || p_batch_name_l || ' To ' || p_batch_name_h);
557 log('Minimun Unapplied Amount : ' || p_min_unapp_amt);
558 log('Receipt Date From ' || p_receipt_date_l || ' To ' || p_receipt_date_h);
559 log('Customer Name From ' || p_customer_name_l || ' To ' || p_customer_name_h);
560 log('Customer Number From ' || p_customer_no_l || ' To ' || p_customer_no_h);
561 log('Batch ID : ' || p_batch_id);
562 log('Transmission ID : ' || p_transmission_id);
563 log('Called From : ' || p_called_from);
564 log('Total Workers : ' || p_total_workers);
565 END IF;
566 delete_interim_records; /* Call to delete records from interface table */
567 G_PROGRAM_ID := arp_standard.profile.program_id;
568 G_PROGRAM_APPLICATION_ID := arp_standard.application_id;
569 G_CREATED_BY := arp_standard.profile.user_id;
570 G_LAST_UPDATED_BY := arp_standard.profile.user_id;
571 G_LAST_UPDATE_LOGIN := arp_standard.profile.last_update_login;
572 /* Fetch the data from ar_cash_remit_refs table for the current run
573 based on the parameters */
574 l_insert_stmt := ' INSERT INTO AR_CASH_REMIT_REFS_INTERIM
575 ( REMIT_REFERENCE_ID,
576 RECEIPT_REFERENCE_STATUS,
577 AUTOMATCH_SET_ID,
578 CASH_RECEIPT_ID,
579 REFERENCE_SOURCE,
580 CUSTOMER_ID,
581 CUSTOMER_NUMBER,
582 BANK_ACCOUNT_NUMBER,
583 TRANSIT_ROUTING_NUMBER,
584 INVOICE_REFERENCE,
585 MATCHING_REFERENCE_DATE,
586 INSTALLMENT_REFERENCE,
587 INVOICE_CURRENCY_CODE,
588 AMOUNT_APPLIED,
589 AMOUNT_APPLIED_FROM,
590 TRANS_TO_RECEIPT_RATE,
591 TRANSMISSION_ID,
592 BATCH_ID,
593 WORKER_NUMBER)
594 SELECT ref.REMIT_REFERENCE_ID,
595 ''AR_AM_NEW'',
596 cr.AUTOMATCH_SET_ID,
597 ref.CASH_RECEIPT_ID,
598 ref.REFERENCE_SOURCE,
599 cr.PAY_FROM_CUSTOMER,
600 ref.CUSTOMER_NUMBER,
601 ref.BANK_ACCOUNT_NUMBER,
602 ref.TRANSIT_ROUTING_NUMBER,
603 ref.INVOICE_REFERENCE,
604 ref.MATCHING_REFERENCE_DATE,
605 ref.INSTALLMENT_NUMBER,
606 ref.INVOICE_CURRENCY_CODE,
607 ref.AMOUNT_APPLIED,
608 ref.AMOUNT_APPLIED_FROM,
609 ref.TRANS_TO_RECEIPT_RATE,
610 ref.TRANSMISSION_ID,
611 ref.BATCH_ID,
612 MOD( ref.CASH_RECEIPT_ID, :b_total_workers) + 1';
613
614 l_from_clause := ' FROM AR_CASH_REMIT_REFS ref
615 , AR_CASH_RECEIPTS cr ';
616
617 l_where_clause := ' WHERE ref.auto_applied = ''N''
618 AND ref.manually_applied = ''N''
619 AND ref.resolved_matching_number = ''NULL''
620 AND ref.invoice_reference IS NOT NULL
621 AND cr.cash_receipt_id = ref.cash_receipt_id ';
622
623 IF p_called_from = 'ARCABP' and NVL(p_transmission_id, -1) > 0 THEN
624 /* Called From Lockbox */
625 l_from_clause := l_from_clause || ', ar_batches bat ';
626 l_where_clause := l_where_clause ||
627 ' AND ref.batch_id = bat.batch_id
628 AND bat.transmission_id = :b_transmission_id ';
629
630 ELSIF p_called_from = 'ARCABP' and NVL(p_transmission_id, -1) <= 0 THEN
631 /* Called From QuickCash */
632 l_where_clause := l_where_clause ||
633 ' AND ref.batch_id = :b_batch_id ';
634 ELSE /* Called From Concurrent Program */
635 IF p_receipt_no_l IS NOT NULL THEN
636 l_where_clause := l_where_clause || ' AND cr.receipt_number >= :b_receipt_no_l ';
637 END IF;
638 IF p_receipt_no_h IS NOT NULL THEN
639 l_where_clause := l_where_clause || ' AND cr.receipt_number <= :b_receipt_no_h ';
640 END IF;
641 IF p_batch_name_l IS NOT NULL THEN
642 l_where_clause := l_where_clause || ' AND bat.name >= :b_batch_name_l ';
643 l_use_bat := 'Y';
644 END IF;
645 IF p_batch_name_h IS NOT NULL THEN
646 l_where_clause := l_where_clause || ' AND bat.name <= :b_batch_name_h ';
647 l_use_bat := 'Y';
648 END IF;
649 IF p_min_unapp_amt IS NOT NULL THEN
650 l_from_clause := l_from_clause || ' , AR_PAYMENT_SCHEDULES ps ';
651 l_where_clause := l_where_clause || ' AND ps.amount_due_remaining * -1 >= :b_min_unapp_amt
652 AND ps.cash_receipt_id = ref.cash_receipt_id ';
653 END IF;
654 IF p_receipt_date_l IS NOT NULL THEN
655 l_receipt_date_low := fnd_date.canonical_to_date(p_receipt_date_l);
656 l_where_clause := l_where_clause || ' AND cr.receipt_date >= :b_receipt_date_l ';
657 END IF;
658 IF p_receipt_date_h IS NOT NULL THEN
659 l_receipt_date_high := fnd_date.canonical_to_date(p_receipt_date_h);
660 l_where_clause := l_where_clause || ' AND cr.receipt_date <= :b_receipt_date_h ';
661 END IF;
662 IF p_receipt_method_l IS NOT NULL THEN
663 l_where_clause := l_where_clause || ' AND rm.name >= :b_receipt_method_l ';
664 l_use_rm := 'Y';
665 END IF;
666 IF p_receipt_method_h IS NOT NULL THEN
667 l_where_clause := l_where_clause || ' AND rm.name <= :b_receipt_method_h ';
668 l_use_rm := 'Y';
669 END IF;
670 IF p_customer_name_l IS NOT NULL THEN
671 l_where_clause := l_where_clause || ' AND party.party_name >= :b_customer_name_l ';
672 l_use_party := 'Y';
673 END IF;
674 IF p_customer_name_h IS NOT NULL THEN
675 l_where_clause := l_where_clause || ' AND party.party_name <= :b_customer_name_h ';
676 l_use_party := 'Y';
677 END IF;
678 IF p_customer_no_l IS NOT NULL THEN
679 l_where_clause := l_where_clause || ' AND cust.account_number >= :b_customer_no_l ';
680 l_use_cust := 'Y';
681 END IF;
682 IF p_customer_no_h IS NOT NULL THEN
683 l_where_clause := l_where_clause || ' AND cust.account_number <= :b_customer_no_h ';
684 l_use_cust := 'Y';
685 END IF;
686 IF l_use_rm = 'Y' THEN
687 l_from_clause := l_from_clause || ' , AR_RECEIPT_METHODS rm ';
688 l_where_clause := l_where_clause || ' AND cr.receipt_method_id = rm.receipt_method_id ';
689 END IF;
690 IF l_use_bat = 'Y' THEN
691 l_from_clause := l_from_clause || ' , AR_BATCHES bat
692 , AR_CASH_RECEIPT_HISTORY crh ';
693 l_where_clause := l_where_clause || ' AND bat.batch_id = crh.batch_id
694 AND crh.cash_receipt_id = ref.cash_receipt_id ';
695 END IF;
696 IF l_use_cust = 'Y' THEN
697 l_from_clause := l_from_clause || ' , HZ_CUST_ACCOUNTS cust ';
698 l_where_clause := l_where_clause || ' AND cust.cust_account_id = NVL(ref.customer_id, cr.pay_from_customer)';
699 END IF;
700 IF l_use_party = 'Y' THEN
701 IF l_use_cust = 'Y' THEN
702 l_from_clause := l_from_clause || ' , HZ_PARTIES party ';
703 l_where_clause := l_where_clause ||
704 'AND party.party_id = cust.party_id ';
705 ELSE
706 l_from_clause := l_from_clause || ' , HZ_CUST_ACCOUNTS cust
707 , HZ_PARTIES party ';
708 l_where_clause := l_where_clause ||
709 ' AND party.party_id = cust.party_id
710 AND cust.cust_account_id = NVL(ref.customer_id, cr.pay_from_customer)';
711 END IF;
712 END IF;
713 END IF;
714
715 l_insert_stmt := l_insert_stmt || l_from_clause || l_where_clause;
716 log('Insert Statement : ' || l_insert_stmt);
717 insert_gt := dbms_sql.open_cursor;
718 dbms_sql.parse (insert_gt,l_insert_stmt,dbms_sql.v7);
719
720 dbms_sql.bind_variable ( insert_gt, ':b_total_workers', p_total_workers);
721 IF p_called_from = 'ARCABP' and NVL(p_transmission_id, -1) > 0 THEN
722 dbms_sql.bind_variable ( insert_gt, ':b_transmission_id', p_transmission_id);
723 ELSIF p_called_from = 'ARCABP' and NVL(p_transmission_id, -1) <= 0 THEN
724 dbms_sql.bind_variable ( insert_gt, ':b_batch_id', p_batch_id);
725 ELSE
726 IF p_receipt_no_l IS NOT NULL THEN
727 dbms_sql.bind_variable ( insert_gt, ':b_receipt_no_l', p_receipt_no_l);
728 END IF;
729 IF p_receipt_no_h IS NOT NULL THEN
730 dbms_sql.bind_variable ( insert_gt, ':b_receipt_no_h', p_receipt_no_h);
731 END IF;
732 IF p_batch_name_l IS NOT NULL THEN
733 dbms_sql.bind_variable ( insert_gt, ':b_batch_name_l', p_batch_name_l);
734 END IF;
735 IF p_batch_name_h IS NOT NULL THEN
736 dbms_sql.bind_variable ( insert_gt, ':b_batch_name_h', p_batch_name_h);
737 END IF;
738 IF p_min_unapp_amt IS NOT NULL THEN
739 dbms_sql.bind_variable ( insert_gt, ':b_min_unapp_amt', p_min_unapp_amt);
740 END IF;
741 IF p_receipt_date_l IS NOT NULL THEN
742 dbms_sql.bind_variable ( insert_gt, ':b_receipt_date_l', l_receipt_date_low);
743 END IF;
744 IF p_receipt_date_h IS NOT NULL THEN
745 dbms_sql.bind_variable ( insert_gt, ':b_receipt_date_h', l_receipt_date_high);
746 END IF;
747 IF p_receipt_method_l IS NOT NULL THEN
748 dbms_sql.bind_variable ( insert_gt, ':b_receipt_method_l', p_receipt_method_l);
749 END IF;
750 IF p_receipt_method_h IS NOT NULL THEN
751 dbms_sql.bind_variable ( insert_gt, ':b_receipt_method_h', p_receipt_method_h);
752 END IF;
753 IF p_customer_name_l IS NOT NULL THEN
754 dbms_sql.bind_variable ( insert_gt, ':b_customer_name_l', p_customer_name_l);
755 END IF;
756 IF p_customer_name_h IS NOT NULL THEN
757 dbms_sql.bind_variable ( insert_gt, ':b_customer_name_h', p_customer_name_h);
758 END IF;
759 IF p_customer_no_l IS NOT NULL THEN
760 dbms_sql.bind_variable ( insert_gt, ':b_customer_no_l', p_customer_no_l);
761 END IF;
762 IF p_customer_no_h IS NOT NULL THEN
763 dbms_sql.bind_variable ( insert_gt, ':b_customer_no_h', p_customer_no_h);
764 END IF;
765 END IF;
766
767 l_rows_inserted := dbms_sql.execute( insert_gt);
768
769 /* * Mark the rows with status 'AR_AA_RULE_SET_INACTIVE' if the *
770 * rule set provided in the receipt is not active as per the *
771 * receipt date. These references will not be processed further *
772 * by the Automatic Cash Application Program * */
773
774 UPDATE ar_cash_remit_refs_interim cri
775 SET cri.receipt_reference_status = 'AR_AA_RULE_SET_INACTIVE'
776 WHERE cri.cash_receipt_id IN (
777 SELECT distinct cr.cash_receipt_id
778 FROM ar_cash_remit_refs_interim cri1,
779 ar_cash_auto_rule_sets aca,
780 ar_cash_receipts cr
781 WHERE cr.cash_receipt_id = cri1.cash_receipt_id
782 AND cr.automatch_set_id = aca.automatch_set_id
783 AND (cr.receipt_date < NVL(aca.start_date, cr.receipt_date)
784 OR cr.receipt_date > NVL(aca.end_date, to_date('31/12/4712','DD/MM/YYYY'))
785 OR NVL(aca.active_flag, 'N') = 'N')
786 )
787 AND cri.receipt_reference_status = 'AR_AM_NEW';
788
789 /* * Mark references with status 'AR_AA_AUTOAPPLY_NOT_SET' if auto *
790 * match set id is not present in both receipt and reference info * */
791 UPDATE ar_cash_remit_refs_interim
792 SET receipt_reference_status = 'AR_AA_RULE_SET_NOT_PASSED'
793 WHERE automatch_set_id IS NULL
794 AND receipt_reference_status = 'AR_AM_NEW';
795
796 UPDATE ar_cash_remit_refs_interim
797 SET receipt_reference_status = 'AR_AA_AMT_NOT_PASSED'
798 WHERE amount_applied IS NULL
799 AND amount_applied_from IS NULL
800 AND receipt_reference_status = 'AR_AM_NEW';
801
802 /* * Delete Suggestions for the references that will be processed in *
803 * the current run. This is to avoid duplicate recommendations *
804 * getting generated and to handle the cases where a refernce no is*
805 * changed after the previous run. Refer bug 8396831 * */
806
807 DELETE FROM ar_cash_reco_lines lines
808 WHERE EXISTS (
809 SELECT 'Suggestion Exists'
810 FROM ar_cash_recos rec, ar_cash_remit_refs_interim ref
811 WHERE rec.recommendation_id = lines.recommendation_id
812 AND rec.remit_reference_id = ref.remit_reference_id
813 AND ref.receipt_reference_status = 'AR_AM_NEW'
814 );
815
816 DELETE FROM ar_cash_recos rec
817 WHERE EXISTS(
818 SELECT 'Suggestion Exists'
819 FROM ar_cash_remit_refs_interim ref
820 WHERE rec.remit_reference_id = ref.remit_reference_id
821 AND ref.receipt_reference_status = 'AR_AM_NEW'
822 );
823
824 commit;
825
826 IF p_total_workers > 1 THEN
827 FOR l_worker_number IN 1..p_total_workers LOOP
828 log('worker # : ' || l_worker_number );
829 submit_subrequest (l_worker_number,p_org_id);
830 END LOOP;
831
832 IF PG_DEBUG in ('Y', 'C') THEN
833 log ( 'The Master program waits for child processes');
834 END IF;
835
836 -- Wait for the completion of the submitted requests
837 FOR i in 1..p_total_workers LOOP
838
839 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
840 request_id => l_req_status_tab(i).request_id,
841 interval => 30,
842 max_wait => 144000,
843 phase => l_req_status_tab(i).phase,
844 status => l_req_status_tab(i).status,
845 dev_phase => l_req_status_tab(i).dev_phase,
846 dev_status => l_req_status_tab(i).dev_status,
847 message => l_req_status_tab(i).message);
848
849 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
850 P_RETCODE := 2;
851 log('Worker # '|| i||' has a phase '||l_req_status_tab(i).dev_phase);
852 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
853 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
854 P_RETCODE := 2;
855 log('Worker # '|| i||' completed with status '||l_req_status_tab(i).dev_status);
856 ELSE
857 log('Worker # '|| i||' completed successfully');
858 END IF;
859
860 END LOOP;
861
862 log('Return Code : ' || p_retcode);
863
864 IF NVL( p_retcode, -1) = 2 THEN
865 log(' - Child program failed.' );
866 ELSE
867 log(' - Child programs completed successfully' );
868 END IF;
869
870 ELSE
871 auto_apply_child(l_errbuf, l_retcode, p_total_workers);
872 END IF;
873 /* * AutoCash Application Process Completed. Now update the receipt_ *
874 * reference_status for the unapplied references with either No Match*
875 * Found or Suggestions found based on recommendations generated * */
876 UPDATE ar_cash_remit_refs_interim cri
877 SET cri.receipt_reference_status = DECODE(
878 ( SELECT 'MATCH_FOUND'
879 FROM ar_cash_recos
880 WHERE remit_reference_id = cri.remit_reference_id
881 AND rownum = 1 ),'MATCH_FOUND','AR_AA_SUGG_FOUND','AR_AA_NO_MATCH')
882 WHERE cri.receipt_reference_status = 'AR_AM_NEW';
883
884 UPDATE ar_cash_remit_refs crr
885 SET crr.receipt_reference_status = (SELECT cri.receipt_reference_status
886 FROM ar_cash_remit_refs_interim cri
887 WHERE crr.remit_reference_id = cri.remit_reference_id
888 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'))
889 WHERE crr.remit_reference_id IN (SELECT cri.remit_reference_id
890 FROM ar_cash_remit_refs_interim cri
891 WHERE crr.remit_reference_id = cri.remit_reference_id
892 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'))
893 AND crr.receipt_reference_status <> 'AR_AA_INV_APPLIED';
894
895 /* * If a receipt has any unapplied remittance line, update the *
896 * receipt work_item_exception_reason with the exception reason *
897 * defined at the AutoMatchRule Setup * */
898 UPDATE ar_cash_receipts_all cr
899 SET WORK_ITEM_EXCEPTION_REASON =
900 (SELECT exception_reason
901 FROM ar_cash_auto_rule_sets
902 WHERE automatch_set_id = cr.automatch_set_id)
903 WHERE cash_receipt_id IN
904 (SELECT distinct cash_receipt_id
905 FROM ar_cash_remit_refs_interim cri
906 WHERE receipt_reference_status IN ('AR_AA_NO_MATCH','AR_AA_SUGG_FOUND')
907 );
908
909 COMMIT;
910
911 IF (PG_DEBUG IN ('Y', 'C')) THEN
912 log('arp_autoapply_api.auto_apply_master(-)');
913 END IF;
914 EXCEPTION
915 WHEN OTHERS THEN
916 log('Exception from arp_autoapply_api.auto_apply_master');
917 log(SQLERRM);
918 RAISE;
919
920 END auto_apply_master;
921
922 /*===========================================================================+
923 * PROCEDURE *
924 * AUTO_APPLY_CHILD() *
925 * DESCRIPTION *
926 * Automatic Cash Application Child program *
927 * SCOPE - PUBLIC *
928 * ARGUMENTS *
929 * IN : p_worker_number Worker Number *
930 * OUT : NONE *
931 * *
932 * RETURNS NONE *
933 * ALGORITHM *
934 * 1. Fetch distinct Automatch Set Identifiers associated to the receipts *
935 * allocated for the current worker. *
936 * 2. Fetch active Automatch Rules associated with each Automatch Sets. *
937 * 3. For each Automatch Set *
938 * For each active Automatch Rule inside a rule set *
939 * Check if the rule is already processed (refer Notes below) *
940 * If processed Skip. Proceed with next rule. *
941 * Else *
942 * Generate String Transformations. *
943 * Create recommendations based on Match_By option. *
944 * 4. Validate the recommendations generated. *
945 * 5. Apply the valid recommendations. *
946 * NOTES - *
947 * 1. The check if a rule is active wrt receipt date is made while inserting
948 * recommendations. *
949 * 2. Recommendations are inserted once per each automatch rule. Meaning if*
950 * a rule R1 is part of two sets S1, S2 and suppose we are processing S1*
951 * first, then for all the references that have either S1 or S2 as rule *
952 * sets recommendations for the rule R1 are generated while processing S1
953 * itself. So there is no need to insert recommendations again while *
954 * processing S2. Hence whenever a rule is fetched for a rule set, first*
955 * check is made to see if the rule is already processed as part of any *
956 * other rule set. *
957 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
958 * *
959 +===========================================================================*/
960
961 PROCEDURE auto_apply_child( P_ERRBUF OUT NOCOPY VARCHAR2
962 , P_RETCODE OUT NOCOPY NUMBER
963 , p_worker_number IN NUMBER) IS
964 CURSOR auto_rule_set_cur(p_worker_number IN NUMBER) IS
965 SELECT distinct automatch_set_id
966 FROM AR_CASH_REMIT_REFS_INTERIM
967 WHERE worker_number = p_worker_number
968 AND receipt_reference_status = 'AR_AM_NEW';
969
970 CURSOR auto_rule_cursor(p_automatch_set_id IN NUMBER) IS
971 SELECT aca.automatch_id automatch_id,
972 aca.matching_option matching_option,
973 NVL(aca.use_matching_date, 'N') use_matching_date
974 FROM AR_CASH_AUTOMATCHES aca,
975 AR_CASH_AUTOMATCH_RULE_MAP acm
976 WHERE acm.automatch_set_id = p_automatch_set_id
977 AND aca.automatch_id = acm.automatch_id
978 AND NVL(aca.active_flag, 'N') = 'Y'
979 ORDER BY acm.priority;
980
981 l_worker_number NUMBER;
982 l_automatch_set_id NUMBER;
983 l_automatch_id NUMBER;
984 l_matching_option VARCHAR2(30);
985 l_use_matching_date VARCHAR2(10);
986 l_trans_format_str VARCHAR2(1000);
987 l_trans_float_str VARCHAR2(1000);
988 l_rem_format_str VARCHAR2(1000);
989 l_rem_float_str VARCHAR2(1000);
990 p_request_id NUMBER := -1;
991 BEGIN
992 IF (PG_DEBUG IN ('Y', 'C')) THEN
993 log('arp_autoapply_api.auto_apply_child(+)');
994 log('Worker Number : ' || p_worker_number);
995 END IF;
996 l_worker_number := p_worker_number;
997 p_request_id := arp_standard.profile.request_id;
998
999 FOR auto_rule_set_var in auto_rule_set_cur(l_worker_number)
1000 LOOP
1001 l_automatch_set_id := auto_rule_set_var.automatch_set_id;
1002 FOR auto_rule_var in auto_rule_cursor(l_automatch_set_id)
1003 LOOP
1004 l_automatch_id := auto_rule_var.automatch_id;
1005 l_matching_option := auto_rule_var.matching_option;
1006 l_use_matching_date := auto_rule_var.use_matching_date;
1007
1008 IF NOT is_rule_processed (l_automatch_id) THEN
1009 gen_str_transformations(p_rule_id => l_automatch_id
1010 , x_trans_format_str => l_trans_format_str
1011 , x_rem_format_str => l_rem_format_str
1012 , x_trans_float_str => l_trans_float_str
1013 , x_rem_float_str => l_rem_float_str );
1014 IF l_matching_option = 'INVOICE' THEN
1015 insert_invoice_recos(l_automatch_id,
1016 l_use_matching_date,
1017 l_trans_format_str,
1018 l_rem_format_str,
1019 l_trans_float_str,
1020 l_rem_float_str,
1021 l_worker_number,
1022 p_request_id);
1023 ELSIF l_matching_option = 'SALES_ORDER' THEN
1024 insert_so_recos(l_automatch_id,
1025 l_use_matching_date,
1026 l_trans_format_str,
1027 l_rem_format_str,
1028 l_trans_float_str,
1029 l_rem_float_str,
1030 l_worker_number,
1031 p_request_id);
1032 ELSIF l_matching_option = 'PURCHASE_ORDER' THEN
1033 insert_po_recos(l_automatch_id,
1034 l_use_matching_date,
1035 l_trans_format_str,
1036 l_rem_format_str,
1037 l_trans_float_str,
1038 l_rem_float_str,
1039 l_worker_number,
1040 p_request_id);
1041 ELSIF l_matching_option = 'CONSOLIDATE_BILL' THEN
1042 insert_bfb_recos(l_automatch_id,
1043 l_use_matching_date,
1044 l_trans_format_str,
1045 l_rem_format_str,
1046 l_trans_float_str,
1047 l_rem_float_str,
1048 l_worker_number,
1049 p_request_id);
1050 ELSIF l_matching_option = 'WAY_BILL' THEN
1051 insert_waybill_recos(l_automatch_id,
1052 l_use_matching_date,
1053 l_trans_format_str,
1054 l_rem_format_str,
1055 l_trans_float_str,
1056 l_rem_float_str,
1057 l_worker_number,
1058 p_request_id);
1059 ELSIF substr(l_matching_option, 1, 11) = 'INT_HDR_ATT' THEN
1060 insert_attribute_recos(l_automatch_id,
1061 l_use_matching_date,
1062 l_trans_format_str,
1063 l_rem_format_str,
1064 l_trans_float_str,
1065 l_rem_float_str,
1066 l_worker_number,
1067 substr(l_matching_option, 12),
1068 p_request_id);
1069 ELSIF l_matching_option = 'SERVICE_CONTRACT' THEN
1070 insert_contract_recos(l_automatch_id,
1071 l_use_matching_date,
1072 l_trans_format_str,
1073 l_rem_format_str,
1074 l_trans_float_str,
1075 l_rem_float_str,
1076 l_worker_number,
1077 p_request_id);
1078 ELSIF l_matching_option = 'REFERENCE_NUMBER' THEN
1079 insert_reference_recos(l_automatch_id,
1080 l_use_matching_date,
1081 l_trans_format_str,
1082 l_rem_format_str,
1083 l_trans_float_str,
1084 l_rem_float_str,
1085 l_worker_number,
1086 p_request_id);
1087 END IF;
1088 END IF;
1089 END LOOP;
1090 END LOOP;
1091 validate_trx_recos(p_request_id, p_worker_number);
1092
1093 apply_trx_recos(p_request_id, p_worker_number);
1094
1095 IF (PG_DEBUG IN ('Y', 'C')) THEN
1096 log('arp_autoapply_api.auto_apply_child(-)');
1097 END IF;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 log('Exception from arp_autoapply_api.auto_apply_main');
1101 log(SQLERRM);
1102 RAISE;
1103 END auto_apply_child;
1104
1105 /*===========================================================================+
1106 * PROCEDURE *
1107 * INSERT_INVOICE_RECOS() *
1108 * DESCRIPTION *
1109 * Inserts recommendations for transaction numbers *
1110 * SCOPE - LOCAL *
1111 * ARGUMENTS *
1112 * IN : p_automatch_id Automatch Rule Identifier *
1113 * p_use_matching_date Use Matching Date [ALWAYS/For *
1114 * Duplicates/NULL] *
1115 * p_trans_format_str Transaction Number Format String *
1116 * p_rem_format_str Reference Number Format String *
1117 * p_worker_number Current Worker Number *
1118 * p_request_id Request ID *
1119 * OUT : None *
1120 * *
1121 * RETURNS NONE *
1122 * ALGORITHM *
1123 * 1. For all open transactions satisfying all the setup conditions calculate
1124 * the matching score of transaction number with the reference number *
1125 * given in the remittance lines (ar_cash_remit_refs_all) *
1126 * 2. If match_score > suggested threshold value specified at the AutoMatch*
1127 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1128 * -tion. *
1129 * NOTES - *
1130 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1131 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1132 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1133 * unidentified then transactions for all the customers are considered. *
1134 * Otherwise only the transactions related to the paying customer of the*
1135 * receipt are considered. *
1136 * 3. An invoice can have multiple installments; which means there is a *
1137 * possibility that the receipt is applied against multiple payment *
1138 * schedules for the same transaction. ar_cash_recos contains header *
1139 * level information like resolved number(trx number), trx date etc., *
1140 * where as ar_cash_reco_lines contains the sepecific ps information for*
1141 * the resolved transaction. *
1142 * *
1143 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1144 * *
1145 +===========================================================================*/
1146
1147 PROCEDURE insert_invoice_recos (p_automatch_id IN NUMBER
1148 , p_use_matching_date IN VARCHAR2
1149 , p_trans_format_str IN VARCHAR2
1150 , p_rem_format_str IN VARCHAR2
1151 , p_trans_float_str IN VARCHAR2
1152 , p_rem_float_str IN VARCHAR2
1153 , p_worker_number IN NUMBER
1154 , p_request_id IN NUMBER) IS
1155 CURSOR select_recos IS
1156 SELECT ref.remit_reference_id remit_reference_id,
1157 ref.amount_applied ref_amount_applied,
1158 ref.amount_applied_from ref_amount_applied_from,
1159 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
1160 ref.cash_receipt_id cash_receipt_id,
1161 cr.pay_from_customer pay_from_customer,
1162 cr.customer_site_use_id cr_customer_site_use_id,
1163 ps.customer_trx_id customer_trx_id,
1164 ps.customer_id customer_id,
1165 ps.customer_site_use_id customer_site_use_id,
1166 ps.trx_number resolved_matching_number,
1167 ps.terms_sequence_number terms_sequence_number,
1168 decode(am.match_date_by,
1169 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
1170 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
1171 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
1172 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
1173 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
1174 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
1175 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
1176 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
1177 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
1178 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
1179 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
1180 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
1181 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
1182 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
1183 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
1184 'PURCH_ORDER_DATE', trx.purchase_order_date,
1185 'TRANS_DATE', trx.trx_date,
1186 NULL) resolved_matching_date,
1187 ps.trx_date trx_date,
1188 ps.class resolved_matching_class,
1189 ps.invoice_currency_code resolved_match_currency,
1190 ps.amount_due_original amount_due_original,
1191 ps.amount_due_remaining amount_due_remaining,
1192 ps.discount_taken_earned discount_taken_earned,
1193 ps.discount_taken_unearned discount_taken_unearned,
1194 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1195 ROUND(NVL(ref.trans_to_receipt_rate,
1196 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1197 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1198 ref.amount_applied,
1199 ref.amount_applied_from,
1200 ps.invoice_currency_code,
1201 cr.currency_code
1202 )
1203 , GL_CURRENCY_API.GET_RATE_SQL(
1204 ps.invoice_currency_code,
1205 cr.currency_code,
1206 cr.receipt_date,
1207 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
1208 )
1209 )
1210 ),38) trans_to_receipt_rate,
1211 NULL amount_applied_from, -- will be calculated later for xcurr app.
1212 ps.payment_schedule_id payment_schedule_id,
1213 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1214 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
1215 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1216 ps.org_id org_id,
1217 ps.term_id term_id,
1218 am.automatch_id automatch_id,
1219 am.use_matching_date use_matching_date,
1220 am.use_matching_amount use_matching_amount,
1221 am.auto_match_threshold auto_match_threshold,
1222 amp.priority priority,
1223 cr.currency_code receipt_currency_code,
1224 cr.receipt_date receipt_date,
1225 ctt.allow_overapplication_flag allow_overapplication_flag,
1226 tr.partial_discount_flag partial_discount_flag,
1227 RANK() OVER (PARTITION BY ps.trx_number, ps.customer_site_use_id,
1228 ref.remit_reference_id, ps.customer_trx_id
1229 ORDER BY ps.payment_schedule_id) AS reco_num
1230 FROM ar_cash_automatches am,
1231 ar_cash_automatch_rule_map amp,
1232 ar_cash_remit_refs_interim ref,
1233 ar_cash_receipts cr,
1234 ar_payment_schedules ps,
1235 ra_customer_trx trx,
1236 ra_cust_trx_types ctt,
1237 ra_terms tr
1238 WHERE am.automatch_id = p_automatch_id
1239 AND amp.automatch_id = am.automatch_id
1240 AND amp.automatch_set_id = ref.automatch_set_id
1241 AND ref.worker_number = p_worker_number
1242 AND ref.receipt_reference_status = 'AR_AM_NEW'
1243 AND cr.cash_receipt_id = ref.cash_receipt_id
1244 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1245 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1246 AND ps.trx_number IS NOT NULL
1247 AND ps.selected_for_receipt_batch_id IS NULL
1248 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ps.trx_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
1249 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1250 AND ps.class NOT IN ('PMT', 'GUAR')
1251 AND ps.payment_schedule_id > 0
1252 AND ps.status = 'OP'
1253 AND ps.terms_sequence_number = NVL(ref.installment_reference,
1254 ps.terms_sequence_number)
1255 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
1256 NVL(cr.pay_from_customer, ps.customer_id))
1257 FROM DUAL
1258 UNION ALL
1259 SELECT related_cust_account_id
1260 FROM hz_cust_acct_relate_all rel
1261 WHERE rel.cust_account_id = cr.pay_from_customer
1262 AND rel.bill_to_flag = 'Y'
1263 AND rel.status = 'A'
1264 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
1265 UNION ALL
1266 SELECT rel.related_cust_account_id
1267 FROM ar_paying_relationships_v rel,
1268 hz_cust_accounts acc
1269 WHERE acc.cust_account_id = cr.pay_from_customer
1270 AND acc.party_id = rel.party_id
1271 AND cr.receipt_date >= effective_start_date
1272 AND cr.receipt_date <= effective_end_date
1273 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
1274 AND trx.customer_trx_id = ps.customer_trx_id
1275 AND tr.term_id(+) = ps.term_id
1276 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
1277 l_selected_recos selected_recos_table;
1278 l_current_reco selected_recos_table;
1279 l_current_fetch_count NUMBER;
1280 l_outer_index NUMBER;
1281 l_current_reco_line NUMBER;
1282 got_current_block BOOLEAN;
1283 BEGIN
1284 IF (PG_DEBUG IN ('Y', 'C')) THEN
1285 log('arp_autoapply_api.insert_invoice_recos(+)');
1286 log('Auto Match ID :'||p_automatch_id);
1287 log('Worker Number :'||p_worker_number);
1288 END IF;
1289 OPEN select_recos;
1290 LOOP
1291 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
1292 log('Count : '||l_selected_recos.COUNT);
1293 IF l_selected_recos.COUNT = 0 THEN
1294 IF l_current_reco.count > 0 THEN
1295 process_single_reco(l_current_reco
1296 , 'INVOICE');
1297 l_current_reco.DELETE;
1298 insert_recos(p_request_id);
1299 clear_reco_lines_struct;
1300 END IF;
1301 EXIT;
1302 END IF;
1303 l_current_fetch_count := l_selected_recos.COUNT;
1304 l_outer_index := 1;
1305 LOOP
1306 IF l_outer_index > l_current_fetch_count THEN
1307 insert_recos(p_request_id);
1308 clear_reco_lines_struct;
1309 EXIT;
1310 END IF;
1311 got_current_block := FALSE;
1312 LOOP
1313 l_current_reco_line := l_current_reco.COUNT;
1314 IF l_current_reco_line = 0 THEN
1315 log('If Statement');
1316 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1317 l_outer_index := l_outer_index + 1;
1318 ELSE
1319 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
1320 log('Else-If');
1321 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1322 l_outer_index := l_outer_index + 1;
1323 ELSE
1324 log('Else-Else');
1325 got_current_block := TRUE;
1326 END IF;
1327 END IF;
1328 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
1329 EXIT;
1330 END IF;
1331 END LOOP;
1332 IF l_outer_index > l_current_fetch_count THEN
1333 insert_recos(p_request_id);
1334 clear_reco_lines_struct;
1335 EXIT;
1336 END IF;
1337 process_single_reco(l_current_reco
1338 , 'INVOICE');
1339 l_current_reco.DELETE;
1340 END LOOP;
1341 END LOOP;
1342
1343 IF (PG_DEBUG IN ('Y', 'C')) THEN
1344 log('arp_autoapply_api.insert_invoice_recos(-)');
1345 END IF;
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 log('Exception from arp_autoapply_api.insert_invoice_recos');
1349 log(SQLERRM);
1350 RAISE;
1351 END insert_invoice_recos;
1352
1353 /*===========================================================================+
1354 * PROCEDURE *
1355 * INSERT_PO_RECOS() *
1356 * DESCRIPTION *
1357 * Inserts recommendations for Purchase Orders *
1358 * SCOPE - LOCAL *
1359 * ARGUMENTS *
1360 * IN : p_automatch_id Automatch Rule Identifier *
1361 * p_use_matching_date Use Matching Date [ALWAYS/For *
1362 * Duplicates/NULL] *
1363 * p_trans_format_str Transaction Number Format String *
1364 * p_rem_format_str Reference Number Format String *
1365 * p_worker_number Current Worker Number *
1366 * p_request_id Request ID *
1367 * OUT : None *
1368 * *
1369 * RETURNS NONE *
1370 * ALGORITHM *
1371 * 1. For all open POs satisfying all the setup conditions calculate *
1372 * the matching score of purchase order number with the reference number*
1373 * given in the remittance lines (ar_cash_remit_refs_all) *
1374 * 2. If match_score > suggested threshold value specified at the AutoMatch*
1375 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1376 * -tion. *
1377 * NOTES - *
1378 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1379 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1380 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1381 * unidentified then transactions for all the customers are considered. *
1382 * Otherwise only the transactions related to the paying customer of the*
1383 * receipt are considered. *
1384 * 3. A PO can have multiple invoices; which means there is a possibility *
1385 * that the receipt is applied against multiple payment schedules for *
1386 * the same transaction. ar_cash_recos contains header level information*
1387 * level information like resolved number(purchase order)etc., *
1388 * where as ar_cash_reco_lines contains the sepecific ps information for*
1389 * the resolved transaction. *
1390 * *
1391 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1392 * *
1393 +===========================================================================*/
1394
1395 PROCEDURE insert_po_recos (p_automatch_id IN NUMBER
1396 , p_use_matching_date IN VARCHAR2
1397 , p_trans_format_str IN VARCHAR2
1398 , p_rem_format_str IN VARCHAR2
1399 , p_trans_float_str IN VARCHAR2
1400 , p_rem_float_str IN VARCHAR2
1401 , p_worker_number IN NUMBER
1402 , p_request_id IN NUMBER) IS
1403 CURSOR select_recos IS
1404 SELECT ref.remit_reference_id remit_reference_id,
1405 ref.amount_applied ref_amount_applied,
1406 ref.amount_applied_from ref_amount_applied_from,
1407 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
1408 ref.cash_receipt_id cash_receipt_id,
1409 cr.pay_from_customer pay_from_customer,
1410 cr.customer_site_use_id cr_customer_site_use_id,
1411 ps.customer_trx_id customer_trx_id,
1412 ps.customer_id,
1413 ps.customer_site_use_id customer_site_use_id,
1414 trx.purchase_order resolved_matching_number,
1415 ps.terms_sequence_number terms_sequence_number,
1416 decode(am.match_date_by,
1417 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
1418 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
1419 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
1420 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
1421 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
1422 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
1423 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
1424 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
1425 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
1426 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
1427 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
1428 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
1429 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
1430 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
1431 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
1432 'PURCH_ORDER_DATE', trx.purchase_order_date,
1433 'TRANS_DATE', trx.trx_date, NULL) resolved_matching_date,
1434 ps.trx_date trx_date,
1435 ps.class resolved_matching_class,
1436 trx.invoice_currency_code resolved_match_currency,
1437 ps.amount_due_original amount_due_original,
1438 ps.amount_due_remaining amount_due_remaining,
1439 ps.discount_taken_earned discount_taken_earned,
1440 ps.discount_taken_unearned discount_taken_unearned,
1441 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1442 ROUND(NVL(ref.trans_to_receipt_rate,
1443 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1444 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1445 ref.amount_applied,
1446 ref.amount_applied_from,
1447 ps.invoice_currency_code,
1448 cr.currency_code
1449 )
1450 , GL_CURRENCY_API.GET_RATE_SQL(
1451 ps.invoice_currency_code,
1452 cr.currency_code,
1453 cr.receipt_date,
1454 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
1455 )
1456 )
1457 ),38) trans_to_receipt_rate,
1458 NULL amount_applied_from, -- will be calculated later for xcurr app.
1459 ps.payment_schedule_id,
1460 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1461 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1462 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1463 ps.org_id,
1464 ps.term_id term_id,
1465 am.automatch_id,
1466 am.use_matching_date use_matching_date,
1467 am.use_matching_amount use_matching_amount,
1468 am.auto_match_threshold auto_match_threshold,
1469 amp.priority priority,
1470 cr.currency_code receipt_currency_code,
1471 cr.receipt_date,
1472 ctt.allow_overapplication_flag allow_overapplication_flag,
1473 tr.partial_discount_flag partial_discount_flag,
1474 RANK() OVER (PARTITION BY trx.purchase_order, ps.customer_site_use_id,
1475 ref.remit_reference_id, ps.customer_trx_id
1476 ORDER BY ps.payment_schedule_id) AS reco_num
1477 FROM ar_cash_automatches am,
1478 ar_cash_automatch_rule_map amp,
1479 ar_cash_remit_refs_interim ref,
1480 ar_cash_receipts cr,
1481 ra_customer_trx trx,
1482 ar_payment_schedules ps,
1483 ra_cust_trx_types ctt,
1484 ra_terms tr
1485 WHERE am.automatch_id = p_automatch_id
1486 AND amp.automatch_id = am.automatch_id
1487 AND amp.automatch_set_id = ref.automatch_set_id
1488 AND ref.worker_number = p_worker_number
1489 AND ref.receipt_reference_status = 'AR_AM_NEW'
1490 AND cr.cash_receipt_id = ref.cash_receipt_id
1491 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1492 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1493 AND trx.purchase_order IS NOT NULL
1494 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.purchase_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1495 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1496 AND ps.customer_trx_id = trx.customer_trx_id
1497 AND ps.selected_for_receipt_batch_id IS NULL
1498 AND ps.class NOT IN ('PMT', 'GUAR')
1499 AND ps.payment_schedule_id > 0
1500 AND ps.status = 'OP'
1501 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
1502 NVL(cr.pay_from_customer, ps.customer_id))
1503 FROM DUAL
1504 UNION ALL
1505 SELECT related_cust_account_id
1506 FROM hz_cust_acct_relate_all rel
1507 WHERE rel.cust_account_id = cr.pay_from_customer
1508 AND rel.bill_to_flag = 'Y'
1509 AND rel.status = 'A'
1510 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
1511 UNION ALL
1512 SELECT rel.related_cust_account_id
1513 FROM ar_paying_relationships_v rel,
1514 hz_cust_accounts acc
1515 WHERE acc.cust_account_id = cr.pay_from_customer
1516 AND acc.party_id = rel.party_id
1517 AND cr.receipt_date >= effective_start_date
1518 AND cr.receipt_date <= effective_end_date
1519 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
1520 AND tr.term_id(+) = ps.term_id
1521 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
1522 l_selected_recos selected_recos_table;
1523 l_current_reco selected_recos_table;
1524 l_current_fetch_count NUMBER;
1525 l_outer_index NUMBER;
1526 l_current_reco_line NUMBER;
1527 got_current_block BOOLEAN;
1528 BEGIN
1529 IF (PG_DEBUG IN ('Y', 'C')) THEN
1530 log('arp_autoapply_api.insert_po_recos(+)');
1531 END IF;
1532 OPEN select_recos;
1533 LOOP
1534 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
1535 log('Count : '||l_selected_recos.COUNT);
1536 IF l_selected_recos.COUNT = 0 THEN
1537 IF l_current_reco.count > 0 THEN
1538 process_single_reco(l_current_reco
1539 , 'PURCHASE ORDER');
1540 l_current_reco.DELETE;
1541 insert_recos(p_request_id);
1542 clear_reco_lines_struct;
1543 END IF;
1544 EXIT;
1545 END IF;
1546 l_current_fetch_count := l_selected_recos.COUNT;
1547 l_outer_index := 1;
1548 LOOP
1549 IF l_outer_index > l_current_fetch_count THEN
1550 insert_recos(p_request_id);
1551 clear_reco_lines_struct;
1552 EXIT;
1553 END IF;
1554 got_current_block := FALSE;
1555 LOOP
1556 l_current_reco_line := l_current_reco.COUNT;
1557 IF l_current_reco_line = 0 THEN
1558 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1559 l_outer_index := l_outer_index + 1;
1560 ELSE
1561 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
1562 log('Else-If');
1563 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1564 l_outer_index := l_outer_index + 1;
1565 ELSE
1566 got_current_block := TRUE;
1567 END IF;
1568 END IF;
1569 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
1570 EXIT;
1571 END IF;
1572 END LOOP;
1573 IF l_outer_index > l_current_fetch_count THEN
1574 insert_recos(p_request_id);
1575 clear_reco_lines_struct;
1576 EXIT;
1577 END IF;
1578 process_single_reco(l_current_reco
1579 , 'PURCHASE ORDER');
1580 l_current_reco.DELETE;
1581 END LOOP;
1582 END LOOP;
1583 IF (PG_DEBUG IN ('Y', 'C')) THEN
1584 log('arp_autoapply_api.insert_po_recos(-)');
1585 END IF;
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 log('Exception from arp_autoapply_api.insert_po_recos');
1589 log(SQLERRM);
1590 RAISE;
1591 END insert_po_recos;
1592
1593 /*===========================================================================+
1594 * PROCEDURE *
1595 * INSERT_SO_RECOS() *
1596 * DESCRIPTION *
1597 * Inserts recommendations for Sales Orders *
1598 * SCOPE - LOCAL *
1599 * ARGUMENTS *
1600 * IN : p_automatch_id Automatch Rule Identifier *
1601 * p_use_matching_date Use Matching Date [ALWAYS/For *
1602 * Duplicates/NULL] *
1603 * p_trans_format_str Transaction Number Format String *
1604 * p_rem_format_str Reference Number Format String *
1605 * p_worker_number Current Worker Number *
1606 * p_request_id Request ID *
1607 * OUT : None *
1608 * *
1609 * RETURNS NONE *
1610 * ALGORITHM *
1611 * 1. For all open SOs satisfying all the setup conditions calculate *
1612 * the matching score of sales order number with the reference number *
1613 * given in the remittance lines (ar_cash_remit_refs_all) *
1614 * 2. If match_score > suggested threshold value specified at the AutoMatch*
1615 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1616 * -tion. *
1617 * NOTES - *
1618 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1619 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1620 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1621 * unidentified then transactions for all the customers are considered. *
1622 * Otherwise only the transactions related to the paying customer of the*
1623 * receipt are considered. *
1624 * 3. A SO can have multiple invoices; which means there is a possibility *
1625 * that the receipt is applied against multiple payment schedules for *
1626 * the same transaction. ar_cash_recos contains header level information*
1627 * level information like resolved number(sales order number)etc., *
1628 * where as ar_cash_reco_lines contains the sepecific ps information for*
1629 * the resolved transaction. *
1630 * *
1631 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1632 * *
1633 +===========================================================================*/
1634
1635 PROCEDURE insert_so_recos (p_automatch_id IN NUMBER
1636 , p_use_matching_date IN VARCHAR2
1637 , p_trans_format_str IN VARCHAR2
1638 , p_rem_format_str IN VARCHAR2
1639 , p_trans_float_str IN VARCHAR2
1640 , p_rem_float_str IN VARCHAR2
1641 , p_worker_number IN NUMBER
1642 , p_request_id IN NUMBER) IS
1643 CURSOR select_recos IS
1644 SELECT ref.remit_reference_id remit_reference_id,
1645 ref.amount_applied ref_amount_applied,
1646 ref.amount_applied_from ref_amount_applied_from,
1647 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
1648 ref.cash_receipt_id cash_receipt_id,
1649 cr.pay_from_customer pay_from_customer,
1650 cr.customer_site_use_id cr_customer_site_use_id,
1651 ps.customer_trx_id customer_trx_id,
1652 ps.customer_id,
1653 ps.customer_site_use_id customer_site_use_id,
1654 lin.sales_order resolved_matching_number,
1655 ps.terms_sequence_number terms_sequence_number,
1656 decode(am.match_date_by,
1657 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
1658 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
1659 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
1660 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
1661 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
1662 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
1663 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
1664 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
1665 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
1666 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
1667 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
1668 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
1669 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
1670 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
1671 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
1672 'PURCH_ORDER_DATE', trx.purchase_order_date,
1673 'TRANS_DATE', trx.trx_date,
1674 NULL) resolved_matching_date,
1675 ps.trx_date trx_date,
1676 ps.class resolved_matching_class,
1677 ps.invoice_currency_code resolved_match_currency,
1678 ps.amount_due_original amount_due_original,
1679 ps.amount_due_remaining amount_due_remaining,
1680 ps.discount_taken_earned discount_taken_earned,
1681 ps.discount_taken_unearned discount_taken_unearned,
1682 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1683 ROUND(NVL(ref.trans_to_receipt_rate,
1684 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1685 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1686 ref.amount_applied,
1687 ref.amount_applied_from,
1688 ps.invoice_currency_code,
1689 cr.currency_code
1690 )
1691 , GL_CURRENCY_API.GET_RATE_SQL(
1692 ps.invoice_currency_code,
1693 cr.currency_code,
1694 cr.receipt_date,
1695 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
1696 )
1697 )
1698 ),38) trans_to_receipt_rate,
1699 NULL amount_applied_from, -- will be calculated later for xcurr app.
1700 ps.payment_schedule_id payment_schedule_id,
1701 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1702 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1703 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1704 ps.org_id,
1705 ps.term_id term_id,
1706 am.automatch_id automatch_id,
1707 am.use_matching_date use_matching_date,
1708 am.use_matching_amount use_matching_amount,
1709 am.auto_match_threshold auto_match_threshold,
1710 amp.priority priority,
1711 cr.currency_code receipt_currency_code,
1712 cr.receipt_date receipt_date,
1713 ctt.allow_overapplication_flag allow_overapplication_flag,
1714 tr.partial_discount_flag partial_discount_flag,
1715 RANK() OVER (PARTITION BY lin.sales_order, ps.customer_site_use_id,
1716 ref.remit_reference_id, ps.customer_trx_id
1717 ORDER BY ps.payment_schedule_id) AS reco_num
1718 FROM ar_cash_automatches am,
1719 ar_cash_automatch_rule_map amp,
1720 ar_cash_remit_refs_interim ref,
1721 ar_cash_receipts cr,
1722 ra_customer_trx_lines lin,
1723 ar_payment_schedules ps,
1724 ra_customer_trx trx,
1725 ra_cust_trx_types ctt,
1726 ra_terms tr
1727 WHERE am.automatch_id = p_automatch_id
1728 AND amp.automatch_id = am.automatch_id
1729 AND amp.automatch_set_id = ref.automatch_set_id
1730 AND ref.worker_number = p_worker_number
1731 AND ref.receipt_reference_status = 'AR_AM_NEW'
1732 AND cr.cash_receipt_id = ref.cash_receipt_id
1733 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1734 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1735 AND lin.interface_line_context <> 'OKS CONTRACTS'
1736 AND lin.sales_order IS NOT NULL
1737 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1738 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1739 AND ps.customer_trx_id = lin.customer_trx_id
1740 AND trx.customer_trx_id = ps.customer_trx_id
1741 /* Added to fetch values from Header Attributes */
1742 AND ps.selected_for_receipt_batch_id IS NULL
1743 AND ps.class NOT IN ('PMT', 'GUAR')
1744 AND ps.payment_schedule_id > 0
1745 AND ps.status = 'OP'
1746 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
1747 NVL(cr.pay_from_customer, ps.customer_id))
1748 FROM DUAL
1749 UNION ALL
1750 SELECT related_cust_account_id
1751 FROM hz_cust_acct_relate_all rel
1752 WHERE rel.cust_account_id = cr.pay_from_customer
1753 AND rel.bill_to_flag = 'Y'
1754 AND rel.status = 'A'
1755 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
1756 UNION ALL
1757 SELECT rel.related_cust_account_id
1758 FROM ar_paying_relationships_v rel,
1759 hz_cust_accounts acc
1760 WHERE acc.cust_account_id = cr.pay_from_customer
1761 AND acc.party_id = rel.party_id
1762 AND cr.receipt_date >= effective_start_date
1763 AND cr.receipt_date <= effective_end_date
1764 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
1765 AND tr.term_id(+) = ps.term_id
1766 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
1767 l_selected_recos selected_recos_table;
1768 l_current_reco selected_recos_table;
1769 l_current_fetch_count NUMBER;
1770 l_outer_index NUMBER;
1771 l_current_reco_line NUMBER;
1772 got_current_block BOOLEAN;
1773 BEGIN
1774 IF (PG_DEBUG IN ('Y', 'C')) THEN
1775 log('arp_autoapply_api.insert_so_recos(+)');
1776 END IF;
1777 OPEN select_recos;
1778 LOOP
1779 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
1780 log('Count : '||l_selected_recos.COUNT);
1781 IF l_selected_recos.COUNT = 0 THEN
1782 IF l_current_reco.count > 0 THEN
1783 process_single_reco(l_current_reco
1784 , 'SALES ORDER');
1785 l_current_reco.DELETE;
1786 insert_recos(p_request_id);
1787 clear_reco_lines_struct;
1788 END IF;
1789 EXIT;
1790 END IF;
1791 l_current_fetch_count := l_selected_recos.COUNT;
1792 l_outer_index := 1;
1793 LOOP
1794 IF l_outer_index > l_current_fetch_count THEN
1795 insert_recos(p_request_id);
1796 clear_reco_lines_struct;
1797 EXIT;
1798 END IF;
1799 got_current_block := FALSE;
1800 LOOP
1801 l_current_reco_line := l_current_reco.COUNT;
1802 IF l_current_reco_line = 0 THEN
1803 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1804 l_outer_index := l_outer_index + 1;
1805 ELSE
1806 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
1807 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
1808 l_outer_index := l_outer_index + 1;
1809 ELSE
1810 got_current_block := TRUE;
1811 END IF;
1812 END IF;
1813 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
1814 EXIT;
1815 END IF;
1816 END LOOP;
1817 IF l_outer_index > l_current_fetch_count THEN
1818 insert_recos(p_request_id);
1819 clear_reco_lines_struct;
1820 EXIT;
1821 END IF;
1822 process_single_reco(l_current_reco
1823 , 'SALES ORDER');
1824 l_current_reco.DELETE;
1825 END LOOP;
1826 END LOOP;
1827 IF (PG_DEBUG IN ('Y', 'C')) THEN
1828 log('arp_autoapply_api.insert_so_recos(-)');
1829 END IF;
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 log('Exception from arp_autoapply_api.insert_so_recos');
1833 log(SQLERRM);
1834 RAISE;
1835 END insert_so_recos;
1836
1837 /*===========================================================================+
1838 * PROCEDURE *
1839 * INSERT_CONTRACT_RECOS() *
1840 * DESCRIPTION *
1841 * Inserts recommendations for Sales Contracts *
1842 * SCOPE - LOCAL *
1843 * ARGUMENTS *
1844 * IN : p_automatch_id Automatch Rule Identifier *
1845 * p_use_matching_date Use Matching Date [ALWAYS/For *
1846 * Duplicates/NULL] *
1847 * p_trans_format_str Transaction Number Format String *
1848 * p_rem_format_str Reference Number Format String *
1849 * p_worker_number Current Worker Number *
1850 * p_request_id Request ID *
1851 * OUT : None *
1852 * *
1853 * RETURNS NONE *
1854 * ALGORITHM *
1855 * 1. For all open contracts satisfying all the setup conditions calculate *
1856 * the matching score of contract number with the reference number *
1857 * given in the remittance lines (ar_cash_remit_refs_all) *
1858 * 2. If match_score > suggested threshold value specified at the AutoMatch*
1859 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
1860 * -tion. *
1861 * NOTES - *
1862 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
1863 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
1864 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
1865 * unidentified then transactions for all the customers are considered. *
1866 * Otherwise only the transactions related to the paying customer of the*
1867 * receipt are considered. *
1868 * 3. A Contract can have multiple invoices;which means there is a possibility
1869 * that the receipt is applied against multiple payment schedules for *
1870 * the same transaction. ar_cash_recos contains header level information*
1871 * level information like resolved number(contract number)etc., *
1872 * where as ar_cash_reco_lines contains the sepecific ps information for*
1873 * the resolved transaction. *
1874 * *
1875 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
1876 * *
1877 +===========================================================================*/
1878
1879 PROCEDURE insert_contract_recos (p_automatch_id IN NUMBER
1880 , p_use_matching_date IN VARCHAR2
1881 , p_trans_format_str IN VARCHAR2
1882 , p_rem_format_str IN VARCHAR2
1883 , p_trans_float_str IN VARCHAR2
1884 , p_rem_float_str IN VARCHAR2
1885 , p_worker_number IN NUMBER
1886 , p_request_id IN NUMBER) IS
1887 CURSOR select_recos IS
1888 SELECT ref.remit_reference_id remit_reference_id,
1889 ref.amount_applied ref_amount_applied,
1890 ref.amount_applied_from ref_amount_applied_from,
1891 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
1892 ref.cash_receipt_id cash_receipt_id,
1893 cr.pay_from_customer pay_from_customer,
1894 cr.customer_site_use_id cr_customer_site_use_id,
1895 ps.customer_trx_id customer_trx_id,
1896 ps.customer_id,
1897 ps.customer_site_use_id customer_site_use_id,
1898 lin.sales_order resolved_matching_number,
1899 ps.terms_sequence_number terms_sequence_number,
1900 decode(am.match_date_by,
1901 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
1902 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
1903 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
1904 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
1905 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
1906 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
1907 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
1908 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
1909 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
1910 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
1911 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
1912 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
1913 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
1914 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
1915 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
1916 'PURCH_ORDER_DATE', trx.purchase_order_date,
1917 'TRANS_DATE', trx.trx_date,
1918 NULL) resolved_matching_date,
1919 ps.trx_date trx_date,
1920 ps.class resolved_matching_class,
1921 ps.invoice_currency_code resolved_match_currency,
1922 ps.amount_due_original amount_due_original,
1923 ps.amount_due_remaining amount_due_remaining,
1924 ps.discount_taken_earned discount_taken_earned,
1925 ps.discount_taken_unearned discount_taken_unearned,
1926 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
1927 ROUND(NVL(ref.trans_to_receipt_rate,
1928 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
1929 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
1930 ref.amount_applied,
1931 ref.amount_applied_from,
1932 ps.invoice_currency_code,
1933 cr.currency_code
1934 )
1935 , GL_CURRENCY_API.GET_RATE_SQL(
1936 ps.invoice_currency_code,
1937 cr.currency_code,
1938 cr.receipt_date,
1939 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
1940 )
1941 )
1942 ),38) trans_to_receipt_rate,
1943 NULL amount_applied_from, -- will be calculated later for xcurr app.
1944 ps.payment_schedule_id payment_schedule_id,
1945 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
1946 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1947 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
1948 ps.org_id,
1949 ps.term_id term_id,
1950 am.automatch_id automatch_id,
1951 am.use_matching_date use_matching_date,
1952 am.use_matching_amount use_matching_amount,
1953 am.auto_match_threshold auto_match_threshold,
1954 amp.priority priority,
1955 cr.currency_code receipt_currency_code,
1956 cr.receipt_date receipt_date,
1957 ctt.allow_overapplication_flag allow_overapplication_flag,
1958 tr.partial_discount_flag partial_discount_flag,
1959 RANK() OVER (PARTITION BY lin.sales_order, ps.customer_site_use_id,
1960 ref.remit_reference_id, ps.customer_trx_id
1961 ORDER BY ps.payment_schedule_id) AS reco_num
1962 FROM ar_cash_automatches am,
1963 ar_cash_automatch_rule_map amp,
1964 ar_cash_remit_refs_interim ref,
1965 ar_cash_receipts cr,
1966 ra_customer_trx_lines lin,
1967 ar_payment_schedules ps,
1968 ra_customer_trx trx,
1969 ra_cust_trx_types ctt,
1970 ra_terms tr
1971 WHERE am.automatch_id = p_automatch_id
1972 AND amp.automatch_id = am.automatch_id
1973 AND amp.automatch_set_id = ref.automatch_set_id
1974 AND ref.worker_number = p_worker_number
1975 AND ref.receipt_reference_status = 'AR_AM_NEW'
1976 AND cr.cash_receipt_id = ref.cash_receipt_id
1977 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
1978 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
1979 AND lin.interface_line_context = 'OKS CONTRACTS'
1980 AND lin.sales_order IS NOT NULL
1981 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(lin.sales_order, p_trans_format_str, '\2'), p_trans_float_str, ''),
1982 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
1983 AND ps.customer_trx_id = lin.customer_trx_id
1984 AND trx.customer_trx_id = ps.customer_trx_id
1985 /* Added to fetch the date from Header Attribute Columns */
1986 AND ps.selected_for_receipt_batch_id IS NULL
1987 AND ps.class NOT IN ('PMT', 'GUAR')
1988 AND ps.payment_schedule_id > 0
1989 AND ps.status = 'OP'
1990 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
1991 NVL(cr.pay_from_customer, ps.customer_id))
1992 FROM DUAL
1993 UNION ALL
1994 SELECT related_cust_account_id
1995 FROM hz_cust_acct_relate_all rel
1996 WHERE rel.cust_account_id = cr.pay_from_customer
1997 AND rel.bill_to_flag = 'Y'
1998 AND rel.status = 'A'
1999 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
2000 UNION ALL
2001 SELECT rel.related_cust_account_id
2002 FROM ar_paying_relationships_v rel,
2003 hz_cust_accounts acc
2004 WHERE acc.cust_account_id = cr.pay_from_customer
2005 AND acc.party_id = rel.party_id
2006 AND cr.receipt_date >= effective_start_date
2007 AND cr.receipt_date <= effective_end_date
2008 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
2009 AND tr.term_id(+) = ps.term_id
2010 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
2011 l_selected_recos selected_recos_table;
2012 l_current_reco selected_recos_table;
2013 l_current_fetch_count NUMBER;
2014 l_outer_index NUMBER;
2015 l_current_reco_line NUMBER;
2016 got_current_block BOOLEAN;
2017 BEGIN
2018 IF (PG_DEBUG IN ('Y', 'C')) THEN
2019 log('arp_autoapply_api.insert_contract_recos(+)');
2020 END IF;
2021 OPEN select_recos;
2022 LOOP
2023 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2024 log('Count : '||l_selected_recos.COUNT);
2025 IF l_selected_recos.COUNT = 0 THEN
2026 IF l_current_reco.count > 0 THEN
2027 process_single_reco(l_current_reco
2028 , 'SERVICE CONTRACT');
2029 l_current_reco.DELETE;
2030 insert_recos(p_request_id);
2031 clear_reco_lines_struct;
2032 END IF;
2033 EXIT;
2034 END IF;
2035 l_current_fetch_count := l_selected_recos.COUNT;
2036 l_outer_index := 1;
2037 LOOP
2038 IF l_outer_index > l_current_fetch_count THEN
2039 insert_recos(p_request_id);
2040 clear_reco_lines_struct;
2041 EXIT;
2042 END IF;
2043 got_current_block := FALSE;
2044 LOOP
2045 l_current_reco_line := l_current_reco.COUNT;
2046 IF l_current_reco_line = 0 THEN
2047 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2048 l_outer_index := l_outer_index + 1;
2049 ELSE
2050 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
2051 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2052 l_outer_index := l_outer_index + 1;
2053 ELSE
2054 got_current_block := TRUE;
2055 END IF;
2056 END IF;
2057 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
2058 EXIT;
2059 END IF;
2060 END LOOP;
2061 IF l_outer_index > l_current_fetch_count THEN
2062 insert_recos(p_request_id);
2063 clear_reco_lines_struct;
2064 EXIT;
2065 END IF;
2066 process_single_reco(l_current_reco
2067 , 'SERVICE CONTRACT');
2068 l_current_reco.DELETE;
2069 END LOOP;
2070 END LOOP;
2071 IF (PG_DEBUG IN ('Y', 'C')) THEN
2072 log('arp_autoapply_api.insert_contract_recos(-)');
2073 END IF;
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 log('Exception from arp_autoapply_api.insert_contract_recos');
2077 log(SQLERRM);
2078 RAISE;
2079 END insert_contract_recos;
2080
2081 /*===========================================================================+
2082 * PROCEDURE *
2083 * INSERT_ATTRIBUTE_RECOS() *
2084 * DESCRIPTION *
2085 * Inserts recommendations for transaction numbers (Matched with interface *
2086 * header attribute) *
2087 * SCOPE - LOCAL *
2088 * ARGUMENTS *
2089 * IN : p_automatch_id Automatch Rule Identifier *
2090 * p_use_matching_date Use Matching Date [ALWAYS/For *
2091 * Duplicates/NULL] *
2092 * p_trans_format_str Transaction Number Format String *
2093 * p_rem_format_str Reference Number Format String *
2094 * p_worker_number Current Worker Number *
2095 * p_attribute_number Header Attribute Number that has to *
2096 * be matches with (1-16) *
2097 * p_request_id Request ID *
2098 * OUT : None *
2099 * *
2100 * RETURNS NONE *
2101 * ALGORITHM *
2102 * 1. For all open transactions satisfying all the setup conditions calculate
2103 * the matching score of header attribute value with the reference number
2104 * given in the remittance lines (ar_cash_remit_refs_all) *
2105 * 2. If match_score > suggested threshold value specified at the AutoMatch*
2106 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2107 * -tion. *
2108 * NOTES - *
2109 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2110 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2111 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2112 * unidentified then transactions for all the customers are considered. *
2113 * Otherwise only the transactions related to the paying customer of the*
2114 * receipt are considered. *
2115 * 3. An invoice can have multiple installments; which means there is a *
2116 * possibility that the receipt is applied against multiple payment *
2117 * schedules for the same transaction. ar_cash_recos contains header *
2118 * level information like resolved number(trx number), trx date etc., *
2119 * where as ar_cash_reco_lines contains the sepecific ps information for*
2120 * the resolved transaction. *
2121 * *
2122 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2123 * *
2124 +===========================================================================*/
2125
2126 PROCEDURE insert_attribute_recos (p_automatch_id IN NUMBER
2127 , p_use_matching_date IN VARCHAR2
2128 , p_trans_format_str IN VARCHAR2
2129 , p_rem_format_str IN VARCHAR2
2130 , p_trans_float_str IN VARCHAR2
2131 , p_rem_float_str IN VARCHAR2
2132 , p_worker_number IN NUMBER
2133 , p_attribute_number IN VARCHAR2
2134 , p_request_id IN NUMBER) IS
2135 l_sel_stmt VARCHAR2(12000) := 'SELECT ref.remit_reference_id remit_reference_id,
2136 ref.amount_applied ref_amount_applied,
2137 ref.amount_applied_from ref_amount_applied_from,
2138 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
2139 ref.cash_receipt_id cash_receipt_id,
2140 cr.pay_from_customer pay_from_customer,
2141 cr.customer_site_use_id cr_customer_site_use_id,
2142 ps.customer_trx_id customer_trx_id,
2143 ps.customer_id,
2144 ps.customer_site_use_id customer_site_use_id,
2145 trx.trx_number resolved_matching_number,
2146 ps.terms_sequence_number terms_sequence_number,
2147 decode(am.match_date_by,
2148 ''INT_HDR_ATT1'', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
2149 ''INT_HDR_ATT10'', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
2150 ''INT_HDR_ATT11'', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
2151 ''INT_HDR_ATT12'', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
2152 ''INT_HDR_ATT13'', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
2153 ''INT_HDR_ATT14'', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
2154 ''INT_HDR_ATT15'', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
2155 ''INT_HDR_ATT2'', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
2156 ''INT_HDR_ATT3'', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
2157 ''INT_HDR_ATT4'', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
2158 ''INT_HDR_ATT5'', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
2159 ''INT_HDR_ATT6'', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
2160 ''INT_HDR_ATT7'', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
2161 ''INT_HDR_ATT8'', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
2162 ''INT_HDR_ATT9'', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
2163 ''PURCH_ORDER_DATE'', trx.purchase_order_date,
2164 ''TRANS_DATE'', trx.trx_date,
2165 NULL) resolved_matching_date,
2166 ps.trx_date trx_date,
2167 ps.class resolved_matching_class,
2168 ps.invoice_currency_code resolved_match_currency,
2169 ps.amount_due_original amount_due_original,
2170 ps.amount_due_remaining amount_due_remaining,
2171 ps.discount_taken_earned discount_taken_earned,
2172 ps.discount_taken_unearned discount_taken_unearned,
2173 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2174 ROUND(NVL(ref.trans_to_receipt_rate,
2175 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2176 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2177 ref.amount_applied,
2178 ref.amount_applied_from,
2179 ps.invoice_currency_code,
2180 cr.currency_code
2181 )
2182 , GL_CURRENCY_API.GET_RATE_SQL(
2183 ps.invoice_currency_code,
2184 cr.currency_code,
2185 cr.receipt_date,
2186 ar_setup.value(''AR_CROSS_CURRENCY_RATE_TYPE'',null) )
2187 )
2188 )
2189 ),38) trans_to_receipt_rate,
2190 NULL amount_applied_from, -- will be calculated later for xcurr app.
2191 ps.payment_schedule_id payment_schedule_id,
2192 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2193 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, ''''),
2194 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) match_score_value,
2195 ps.org_id org_id,
2196 ps.term_id term_id,
2197 am.automatch_id automatch_id,
2198 am.use_matching_date use_matching_date,
2199 am.use_matching_amount use_matching_amount,
2200 am.auto_match_threshold auto_match_threshold,
2201 amp.priority priority,
2202 cr.currency_code receipt_currency_code,
2203 cr.receipt_date receipt_date,
2204 ctt.allow_overapplication_flag allow_overapplication_flag,
2205 tr.partial_discount_flag partial_discount_flag,
2206 RANK() OVER (PARTITION BY trx.interface_header_attribute' || p_attribute_number ||', ps.customer_site_use_id,
2207 ref.remit_reference_id, ps.customer_trx_id
2208 ORDER BY ps.payment_schedule_id) AS reco_num
2209 FROM ar_cash_automatches am,
2210 ar_cash_automatch_rule_map amp,
2211 ar_cash_remit_refs_interim ref,
2212 ar_cash_receipts cr,
2213 ra_customer_trx trx,
2214 ar_payment_schedules ps,
2215 ra_cust_trx_types ctt,
2216 ra_terms tr
2217 WHERE am.automatch_id = :b_automatch_id
2218 AND amp.automatch_id = am.automatch_id
2219 AND amp.automatch_set_id = ref.automatch_set_id
2220 AND ref.worker_number = :b_worker_number
2221 AND ref.receipt_reference_status = ''AR_AM_NEW''
2222 AND cr.cash_receipt_id = ref.cash_receipt_id
2223 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2224 AND NVL(am.end_date, to_date(''31/12/4712'',''DD/MM/YYYY''))
2225 AND trx.interface_header_attribute'|| p_attribute_number || ' IS NOT NULL
2226 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, ''''),
2227 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, :b_rem_format_str, ''\2''), :b_rem_float_str, '''')) >= am.sugg_match_threshold
2228 AND ps.customer_trx_id = trx.customer_trx_id
2229 AND ps.selected_for_receipt_batch_id IS NULL
2230 AND ps.class NOT IN (''PMT'', ''GUAR'')
2231 AND ps.status = ''OP''
2232 AND ps.terms_sequence_number = NVL(ref.installment_reference,
2233 ps.terms_sequence_number)
2234 AND ps.payment_schedule_id > 0
2235 AND ps.customer_id IN (SELECT DECODE(:b_pay_unrelated_invoices_flag,''Y'', ps.customer_id,
2236 NVL(cr.pay_from_customer, ps.customer_id))
2237 FROM DUAL
2238 UNION ALL
2239 SELECT related_cust_account_id
2240 FROM hz_cust_acct_relate_all rel
2241 WHERE rel.cust_account_id = cr.pay_from_customer
2242 AND rel.bill_to_flag = ''Y''
2243 AND rel.status = ''A''
2244 AND :b_pay_unrelated_invoices_flag <> ''Y''
2245 UNION ALL
2246 SELECT rel.related_cust_account_id
2247 FROM ar_paying_relationships_v rel,
2248 hz_cust_accounts acc
2249 WHERE acc.cust_account_id = cr.pay_from_customer
2250 AND acc.party_id = rel.party_id
2251 AND cr.receipt_date >= effective_start_date
2252 AND cr.receipt_date <= effective_end_date
2253 AND :b_pay_unrelated_invoices_flag <> ''Y'' )
2254 AND trx.customer_trx_id = ps.customer_trx_id
2255 AND tr.term_id(+) = ps.term_id
2256 AND ps.cust_trx_type_id = ctt.cust_trx_type_id';
2257
2258 TYPE SelectRecoType IS REF CURSOR;
2259 select_recos SelectRecoType;
2260 l_selected_recos selected_recos_table;
2261 l_current_reco selected_recos_table;
2262 l_current_fetch_count NUMBER;
2263 l_outer_index NUMBER;
2264 l_current_reco_line NUMBER;
2265 got_current_block BOOLEAN;
2266 BEGIN
2267 IF (PG_DEBUG IN ('Y', 'C')) THEN
2268 log('arp_autoapply_api.insert_attribute_recos(+)');
2269 log('SQL : '||l_sel_stmt);
2270 END IF;
2271
2272 OPEN select_recos FOR l_sel_stmt USING p_trans_format_str,
2273 p_trans_float_str,
2274 p_rem_format_str,
2275 p_rem_float_str,
2276 p_automatch_id,
2277 p_worker_number,
2278 p_trans_format_str,
2279 p_trans_float_str,
2280 p_rem_format_str,
2281 p_rem_float_str,
2282 ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,
2283 ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,
2284 ARP_STANDARD.sysparm.pay_unrelated_invoices_flag;
2285 LOOP
2286 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2287 log('Count : '||l_selected_recos.COUNT);
2288 IF l_selected_recos.COUNT = 0 THEN
2289 IF l_current_reco.count > 0 THEN
2290 process_single_reco(l_current_reco
2291 , 'INTERFACE HEADER ATTRIBUTE'||p_attribute_number);
2292 l_current_reco.DELETE;
2293 insert_recos(p_request_id);
2294 clear_reco_lines_struct;
2295 END IF;
2296 EXIT;
2297 END IF;
2298 l_current_fetch_count := l_selected_recos.COUNT;
2299 l_outer_index := 1;
2300 LOOP
2301 IF l_outer_index > l_current_fetch_count THEN
2302 insert_recos(p_request_id);
2303 clear_reco_lines_struct;
2304 EXIT;
2305 END IF;
2306 got_current_block := FALSE;
2307 LOOP
2308 l_current_reco_line := l_current_reco.COUNT;
2309 IF l_current_reco_line = 0 THEN
2310 log('If Statement');
2311 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2312 l_outer_index := l_outer_index + 1;
2313 ELSE
2314 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
2315 log('Else-If');
2316 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2317 l_outer_index := l_outer_index + 1;
2318 ELSE
2319 log('Else-Else');
2320 got_current_block := TRUE;
2321 END IF;
2322 END IF;
2323 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
2324 EXIT;
2325 END IF;
2326 END LOOP;
2327 IF l_outer_index > l_current_fetch_count THEN
2328 insert_recos(p_request_id);
2329 clear_reco_lines_struct;
2330 EXIT;
2331 END IF;
2332 process_single_reco(l_current_reco
2333 , 'INTERFACE HEADER ATTRIBUTE'||p_attribute_number);
2334 l_current_reco.DELETE;
2335 END LOOP;
2336 END LOOP;
2337
2338 IF (PG_DEBUG IN ('Y', 'C')) THEN
2339 log('arp_autoapply_api.insert_attribute_recos(-)');
2340 END IF;
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 log('Exception from arp_autoapply_api.insert_attribute_recos');
2344 log(SQLERRM);
2345 RAISE;
2346 END insert_attribute_recos;
2347
2348 /*===========================================================================+
2349 * PROCEDURE *
2350 * INSERT_WAYBILL_RECOS() *
2351 * DESCRIPTION *
2352 * Inserts recommendations for Waybill Numbers *
2353 * SCOPE - LOCAL *
2354 * ARGUMENTS *
2355 * IN : p_automatch_id Automatch Rule Identifier *
2356 * p_use_matching_date Use Matching Date [ALWAYS/For *
2357 * Duplicates/NULL] *
2358 * p_trans_format_str Transaction Number Format String *
2359 * p_rem_format_str Reference Number Format String *
2360 * p_worker_number Current Worker Number *
2361 * p_request_id Request ID *
2362 * OUT : None *
2363 * *
2364 * RETURNS NONE *
2365 * ALGORITHM *
2366 * 1. For all open way bills satisfying all the setup conditions calculate *
2367 * the matching score of way bill number with the reference number *
2368 * given in the remittance lines (ar_cash_remit_refs_all) *
2369 * 2. If match_score > suggested threshold value specified at the AutoMatch*
2370 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2371 * -tion. *
2372 * NOTES - *
2373 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2374 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2375 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2376 * unidentified then transactions for all the customers are considered. *
2377 * Otherwise only the transactions related to the paying customer of the*
2378 * receipt are considered. *
2379 * 3. ar_cash_recos contains header level information like resolved *
2380 * number(way bill number)etc., where as ar_cash_reco_lines contains the*
2381 * sepecific ps information for the resolved transaction. *
2382 * *
2383 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2384 * *
2385 +===========================================================================*/
2386
2387 PROCEDURE insert_waybill_recos (p_automatch_id IN NUMBER
2388 , p_use_matching_date IN VARCHAR2
2389 , p_trans_format_str IN VARCHAR2
2390 , p_rem_format_str IN VARCHAR2
2391 , p_trans_float_str IN VARCHAR2
2392 , p_rem_float_str IN VARCHAR2
2393 , p_worker_number IN NUMBER
2394 , p_request_id IN NUMBER) IS
2395 CURSOR select_recos IS
2396 SELECT ref.remit_reference_id remit_reference_id,
2397 ref.amount_applied ref_amount_applied,
2398 ref.amount_applied_from ref_amount_applied_from,
2399 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
2400 ref.cash_receipt_id cash_receipt_id,
2401 cr.pay_from_customer pay_from_customer,
2402 cr.customer_site_use_id cr_customer_site_use_id,
2403 ps.customer_trx_id customer_trx_id,
2404 ps.customer_id,
2405 ps.customer_site_use_id customer_site_use_id,
2406 trx.waybill_number resolved_matching_number,
2407 ps.terms_sequence_number terms_sequence_number,
2408 decode(am.match_date_by,
2409 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
2410 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
2411 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
2412 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
2413 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
2414 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
2415 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
2416 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
2417 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
2418 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
2419 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
2420 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
2421 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
2422 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
2423 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
2424 'PURCH_ORDER_DATE', trx.purchase_order_date,
2425 'TRANS_DATE', trx.trx_date,
2426 NULL) resolved_matching_date,
2427 ps.trx_date trx_date,
2428 ps.class resolved_matching_class,
2429 ps.invoice_currency_code resolved_match_currency,
2430 ps.amount_due_original amount_due_original,
2431 ps.amount_due_remaining amount_due_remaining,
2432 ps.discount_taken_earned discount_taken_earned,
2433 ps.discount_taken_unearned discount_taken_unearned,
2434 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2435 ROUND(NVL(ref.trans_to_receipt_rate,
2436 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2437 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2438 ref.amount_applied,
2439 ref.amount_applied_from,
2440 ps.invoice_currency_code,
2441 cr.currency_code
2442 )
2443 , GL_CURRENCY_API.GET_RATE_SQL(
2444 ps.invoice_currency_code,
2445 cr.currency_code,
2446 cr.receipt_date,
2447 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
2448 )
2449 )
2450 ),38) trans_to_receipt_rate,
2451 NULL amount_applied_from, -- will be calculated later for xcurr app.
2452 ps.payment_schedule_id payment_schedule_id,
2453 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2454 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2455 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2456 ps.org_id,
2457 ps.term_id term_id,
2458 am.automatch_id,
2459 am.use_matching_date use_matching_date,
2460 am.use_matching_amount use_matching_amount,
2461 am.auto_match_threshold auto_match_threshold,
2462 amp.priority priority,
2463 cr.currency_code receipt_currency_code,
2464 cr.receipt_date,
2465 ctt.allow_overapplication_flag allow_overapplication_flag,
2466 tr.partial_discount_flag partial_discount_flag,
2467 RANK() OVER (PARTITION BY trx.waybill_number, ps.customer_site_use_id,
2468 ref.remit_reference_id, ps.customer_trx_id
2469 ORDER BY ps.payment_schedule_id) AS reco_num
2470 FROM ar_cash_automatches am,
2471 ar_cash_automatch_rule_map amp,
2472 ar_cash_remit_refs_interim ref,
2473 ar_cash_receipts cr,
2474 ra_customer_trx trx,
2475 ar_payment_schedules ps,
2476 ra_cust_trx_types ctt,
2477 ra_terms tr
2478 WHERE am.automatch_id = p_automatch_id
2479 AND amp.automatch_id = am.automatch_id
2480 AND amp.automatch_set_id = ref.automatch_set_id
2481 AND ref.worker_number = p_worker_number
2482 AND ref.receipt_reference_status = 'AR_AM_NEW'
2483 AND cr.cash_receipt_id = ref.cash_receipt_id
2484 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2485 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2486 AND trx.waybill_number IS NOT NULL
2487 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.waybill_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2488 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2489 AND ps.customer_trx_id = trx.customer_trx_id
2490 AND ps.selected_for_receipt_batch_id IS NULL
2491 AND ps.class NOT IN ('PMT', 'GUAR')
2492 AND ps.payment_schedule_id > 0
2493 AND ps.status = 'OP'
2494 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
2495 NVL(cr.pay_from_customer, ps.customer_id))
2496 FROM DUAL
2497 UNION ALL
2498 SELECT related_cust_account_id
2499 FROM hz_cust_acct_relate_all rel
2500 WHERE rel.cust_account_id = cr.pay_from_customer
2501 AND rel.bill_to_flag = 'Y'
2502 AND rel.status = 'A'
2503 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
2504 UNION ALL
2505 SELECT rel.related_cust_account_id
2506 FROM ar_paying_relationships_v rel,
2507 hz_cust_accounts acc
2508 WHERE acc.cust_account_id = cr.pay_from_customer
2509 AND acc.party_id = rel.party_id
2510 AND cr.receipt_date >= effective_start_date
2511 AND cr.receipt_date <= effective_end_date
2512 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
2513 AND tr.term_id(+) = ps.term_id
2514 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
2515 l_selected_recos selected_recos_table;
2516 l_current_reco selected_recos_table;
2517 l_current_fetch_count NUMBER;
2518 l_outer_index NUMBER;
2519 l_current_reco_line NUMBER;
2520 got_current_block BOOLEAN;
2521 BEGIN
2522 IF (PG_DEBUG IN ('Y', 'C')) THEN
2523 log('arp_autoapply_api.insert_waybill_recos(+)');
2524 END IF;
2525 OPEN select_recos;
2526 LOOP
2527 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2528 log('Count : '||l_selected_recos.COUNT);
2529 IF l_selected_recos.COUNT = 0 THEN
2530 IF l_current_reco.count > 0 THEN
2531 process_single_reco(l_current_reco
2532 , 'WAYBILL NUMBER');
2533 l_current_reco.DELETE;
2534 insert_recos(p_request_id);
2535 clear_reco_lines_struct;
2536 END IF;
2537 EXIT;
2538 END IF;
2539 l_current_fetch_count := l_selected_recos.COUNT;
2540 l_outer_index := 1;
2541 LOOP
2542 IF l_outer_index > l_current_fetch_count THEN
2543 insert_recos(p_request_id);
2544 clear_reco_lines_struct;
2545 EXIT;
2546 END IF;
2547 got_current_block := FALSE;
2548 LOOP
2549 l_current_reco_line := l_current_reco.COUNT;
2550 IF l_current_reco_line = 0 THEN
2551 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2552 l_outer_index := l_outer_index + 1;
2553 ELSE
2554 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
2555 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2556 l_outer_index := l_outer_index + 1;
2557 ELSE
2558 got_current_block := TRUE;
2559 END IF;
2560 END IF;
2561 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
2562 EXIT;
2563 END IF;
2564 END LOOP;
2565 IF l_outer_index > l_current_fetch_count THEN
2566 insert_recos(p_request_id);
2567 clear_reco_lines_struct;
2568 EXIT;
2569 END IF;
2570 process_single_reco(l_current_reco
2571 , 'WAYBILL NUMBER');
2572 l_current_reco.DELETE;
2573 END LOOP;
2574 END LOOP;
2575 IF (PG_DEBUG IN ('Y', 'C')) THEN
2576 log('arp_autoapply_api.insert_waybill_recos(-)');
2577 END IF;
2578 EXCEPTION
2579 WHEN OTHERS THEN
2580 log('Exception from arp_autoapply_api.insert_waybill_recos');
2581 log(SQLERRM);
2582 RAISE;
2583 END insert_waybill_recos;
2584
2585 /*===========================================================================+
2586 * PROCEDURE *
2587 * INSERT_BFB_RECOS() *
2588 * DESCRIPTION *
2589 * Inserts recommendations for Balance Forward Bills *
2590 * SCOPE - LOCAL *
2591 * ARGUMENTS *
2592 * IN : p_automatch_id Automatch Rule Identifier *
2593 * p_use_matching_date Use Matching Date [ALWAYS/For *
2594 * Duplicates/NULL] *
2595 * p_trans_format_str Transaction Number Format String *
2596 * p_rem_format_str Reference Number Format String *
2597 * p_worker_number Current Worker Number *
2598 * p_request_id Request ID *
2599 * OUT : None *
2600 * *
2601 * RETURNS NONE *
2602 * ALGORITHM *
2603 * 1. For all open bfbs satisfying all the setup conditions calculate *
2604 * the matching score of bfb number with the reference number *
2605 * given in the remittance lines (ar_cash_remit_refs_all) *
2606 * 2. If match_score > suggested threshold value specified at the AutoMatch*
2607 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2608 * -tion. *
2609 * NOTES - *
2610 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2611 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2612 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2613 * unidentified then transactions for all the customers are considered. *
2614 * Otherwise only the transactions related to the paying customer of the*
2615 * receipt are considered. *
2616 * 3. A bfb can have multiple invoices; which means there is a possibility *
2617 * that the receipt is applied against multiple payment schedules for *
2618 * the same transaction. ar_cash_recos contains header level information*
2619 * level information like resolved number(bfb number)etc., *
2620 * where as ar_cash_reco_lines contains the sepecific ps information for*
2621 * the resolved transaction. *
2622 * *
2623 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2624 * *
2625 +===========================================================================*/
2626
2627 PROCEDURE insert_bfb_recos (p_automatch_id IN NUMBER
2628 , p_use_matching_date IN VARCHAR2
2629 , p_trans_format_str IN VARCHAR2
2630 , p_rem_format_str IN VARCHAR2
2631 , p_trans_float_str IN VARCHAR2
2632 , p_rem_float_str IN VARCHAR2
2633 , p_worker_number IN NUMBER
2634 , p_request_id IN NUMBER) IS
2635 CURSOR select_recos IS
2636 SELECT ref.remit_reference_id remit_reference_id,
2637 ref.amount_applied ref_amount_applied,
2638 ref.amount_applied_from ref_amount_applied_from,
2639 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
2640 ref.cash_receipt_id cash_receipt_id,
2641 cr.pay_from_customer pay_from_customer,
2642 cr.customer_site_use_id cr_customer_site_use_id,
2643 ps.customer_trx_id customer_trx_id,
2644 ci.customer_id,
2645 ci.site_use_id customer_site_use_id,
2646 ci.cons_billing_number resolved_matching_number,
2647 ps.terms_sequence_number terms_sequence_number,
2648 decode(am.match_date_by, 'BAL_FWD_BILL_DATE', trunc(ci.billing_date), NULL) resolved_matching_date,
2649 ps.trx_date trx_date,
2650 ps.class resolved_matching_class,
2651 ci.currency_code resolved_match_currency,
2652 ps.amount_due_original amount_due_original,
2653 ps.amount_due_remaining amount_due_remaining,
2654 ps.discount_taken_earned discount_taken_earned,
2655 ps.discount_taken_unearned discount_taken_unearned,
2656 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2657 ROUND(NVL(ref.trans_to_receipt_rate,
2658 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2659 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2660 ref.amount_applied,
2661 ref.amount_applied_from,
2662 ps.invoice_currency_code,
2663 cr.currency_code
2664 )
2665 , GL_CURRENCY_API.GET_RATE_SQL(
2666 ps.invoice_currency_code,
2667 cr.currency_code,
2668 cr.receipt_date,
2669 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
2670 )
2671 )
2672 ),38) trans_to_receipt_rate,
2673 NULL amount_applied_from, -- will be calculated later for xcurr app.
2674 ps.payment_schedule_id payment_schedule_id,
2675 ci.cons_inv_id cons_inv_id,
2676 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2677 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2678 ci.org_id,
2679 ps.term_id term_id,
2680 am.automatch_id automatch_id,
2681 am.use_matching_date use_matching_date,
2682 am.use_matching_amount use_matching_amount,
2683 am.auto_match_threshold auto_match_threshold,
2684 amp.priority priority,
2685 cr.currency_code receipt_currency_code,
2686 cr.receipt_date receipt_date,
2687 ctt.allow_overapplication_flag allow_overapplication_flag,
2688 tr.partial_discount_flag partial_discount_flag,
2689 RANK() OVER (PARTITION BY ci.cons_billing_number, ci.site_use_id, ref.remit_reference_id
2690 ORDER BY ps.due_date, ps.payment_schedule_id) AS reco_num
2691 FROM ar_cash_automatches am,
2692 ar_cash_automatch_rule_map amp,
2693 ar_cash_remit_refs_interim ref,
2694 ar_cash_receipts cr,
2695 ar_cons_inv ci,
2696 ar_payment_schedules ps,
2697 ra_customer_trx trx,
2698 ra_cust_trx_types ctt,
2699 ra_terms tr
2700 WHERE am.automatch_id = p_automatch_id
2701 AND amp.automatch_id = am.automatch_id
2702 AND amp.automatch_set_id = ref.automatch_set_id
2703 AND ref.worker_number = p_worker_number
2704 AND ref.receipt_reference_status = 'AR_AM_NEW'
2705 AND cr.cash_receipt_id = ref.cash_receipt_id
2706 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2707 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2708 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(ci.cons_billing_number, p_trans_format_str, '\2'), p_trans_float_str, ''),
2709 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2710 AND ps.cons_inv_id = ci.cons_inv_id
2711 AND ps.selected_for_receipt_batch_id IS NULL
2712 AND ps.class NOT IN ('PMT', 'GUAR')
2713 AND ps.payment_schedule_id > 0
2714 AND ps.status = 'OP'
2715 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
2716 NVL(cr.pay_from_customer, ps.customer_id))
2717 FROM DUAL
2718 UNION ALL
2719 SELECT related_cust_account_id
2720 FROM hz_cust_acct_relate_all rel
2721 WHERE rel.cust_account_id = cr.pay_from_customer
2722 AND rel.bill_to_flag = 'Y'
2723 AND rel.status = 'A'
2724 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
2725 UNION ALL
2726 SELECT rel.related_cust_account_id
2727 FROM ar_paying_relationships_v rel,
2728 hz_cust_accounts acc
2729 WHERE acc.cust_account_id = cr.pay_from_customer
2730 AND acc.party_id = rel.party_id
2731 AND cr.receipt_date >= effective_start_date
2732 AND cr.receipt_date <= effective_end_date
2733 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
2734 AND trx.customer_trx_id = ps.customer_trx_id
2735 AND tr.term_id(+) = ps.term_id
2736 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
2737 l_selected_recos selected_recos_table;
2738 l_current_reco selected_recos_table;
2739 l_current_fetch_count NUMBER;
2740 l_outer_index NUMBER;
2741 l_current_reco_line NUMBER;
2742 got_current_block BOOLEAN;
2743 BEGIN
2744 IF (PG_DEBUG IN ('Y', 'C')) THEN
2745 log('arp_autoapply_api.insert_bfb_recos(+)');
2746 END IF;
2747
2748 OPEN select_recos;
2749 LOOP
2750 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2751 log('Count : '||l_selected_recos.COUNT);
2752 IF l_selected_recos.COUNT = 0 THEN
2753 IF l_current_reco.count > 0 THEN
2754 process_single_reco(l_current_reco
2755 , 'BALANCE FORWARD BILL');
2756 l_current_reco.DELETE;
2757 insert_recos(p_request_id);
2758 clear_reco_lines_struct;
2759 END IF;
2760 EXIT;
2761 END IF;
2762 l_current_fetch_count := l_selected_recos.COUNT;
2763 l_outer_index := 1;
2764 LOOP
2765 IF l_outer_index > l_current_fetch_count THEN
2766 insert_recos(p_request_id);
2767 clear_reco_lines_struct;
2768 EXIT;
2769 END IF;
2770 got_current_block := FALSE;
2771 LOOP
2772 l_current_reco_line := l_current_reco.COUNT;
2773 IF l_current_reco_line = 0 THEN
2774 log('If Statement');
2775 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2776 l_outer_index := l_outer_index + 1;
2777 ELSE
2778 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
2779 log('Else-If');
2780 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
2781 l_outer_index := l_outer_index + 1;
2782 ELSE
2783 log('Else-Else');
2784 got_current_block := TRUE;
2785 END IF;
2786 END IF;
2787 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
2788 EXIT;
2789 END IF;
2790 END LOOP;
2791 IF l_outer_index > l_current_fetch_count THEN
2792 insert_recos(p_request_id);
2793 clear_reco_lines_struct;
2794 EXIT;
2795 END IF;
2796 process_single_reco(l_current_reco
2797 , 'BALANCE FORWARD BILL');
2798 l_current_reco.DELETE;
2799 END LOOP;
2800 END LOOP;
2801
2802 IF (PG_DEBUG IN ('Y', 'C')) THEN
2803 log('arp_autoapply_api.insert_bfb_recos(-)');
2804 END IF;
2805 EXCEPTION
2806 WHEN OTHERS THEN
2807 log('Exception from arp_autoapply_api.insert_bfb_recos');
2808 log(SQLERRM);
2809 RAISE;
2810 END insert_bfb_recos;
2811 /*===========================================================================+
2812 * PROCEDURE *
2813 * INSERT_REFERENCE_RECOS() *
2814 * DESCRIPTION *
2815 * Inserts recommendations for transaction numbers (Matched with reference *
2816 * number ra_customer_trx.ct_reference) *
2817 * SCOPE - LOCAL *
2818 * ARGUMENTS *
2819 * IN : p_automatch_id Automatch Rule Identifier *
2820 * p_use_matching_date Use Matching Date [ALWAYS/For *
2821 * Duplicates/NULL] *
2822 * p_trans_format_str Transaction Number Format String *
2823 * p_rem_format_str Reference Number Format String *
2824 * p_worker_number Current Worker Number *
2825 * p_request_id Request ID *
2826 * OUT : None *
2827 * *
2828 * RETURNS NONE *
2829 * ALGORITHM *
2830 * 1. For all open transactions satisfying all the setup conditions calculate
2831 * the matching score of trx reference number with the reference number *
2832 * given in the remittance lines (ar_cash_remit_refs_all) *
2833 * 2. If match_score > suggested threshold value specified at the AutoMatch*
2834 * setup, insert into ar_cash_recos, ar_cash_reco_lines as a recommenda *
2835 * -tion. *
2836 * NOTES - *
2837 * 1. Tables with _ALL is used in INSERT statement as multi-table insert is*
2838 * not possible on secured synonyms (ar_cash_recos and ar_cash_reco_lines)
2839 * 2. If pay_unrelated_customer is set to 'Yes' or the reference/receipt is*
2840 * unidentified then transactions for all the customers are considered. *
2841 * Otherwise only the transactions related to the paying customer of the*
2842 * receipt are considered. *
2843 * 3. An invoice can have multiple installments; which means there is a *
2844 * possibility that the receipt is applied against multiple payment *
2845 * schedules for the same transaction. ar_cash_recos contains header *
2846 * level information like resolved number(trx number), trx date etc., *
2847 * where as ar_cash_reco_lines contains the sepecific ps information for*
2848 * the resolved transaction. *
2849 * *
2850 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
2851 * *
2852 +===========================================================================*/
2853
2854 PROCEDURE insert_reference_recos (p_automatch_id IN NUMBER
2855 , p_use_matching_date IN VARCHAR2
2856 , p_trans_format_str IN VARCHAR2
2857 , p_rem_format_str IN VARCHAR2
2858 , p_trans_float_str IN VARCHAR2
2859 , p_rem_float_str IN VARCHAR2
2860 , p_worker_number IN NUMBER
2861 , p_request_id IN NUMBER) IS
2862 CURSOR select_recos IS
2863 SELECT ref.remit_reference_id remit_reference_id,
2864 ref.amount_applied ref_amount_applied,
2865 ref.amount_applied_from ref_amount_applied_from,
2866 ref.trans_to_receipt_rate ref_trans_to_receipt_rate,
2867 ref.cash_receipt_id cash_receipt_id,
2868 cr.pay_from_customer pay_from_customer,
2869 cr.customer_site_use_id cr_customer_site_use_id,
2870 ps.customer_trx_id customer_trx_id,
2871 ps.customer_id,
2872 ps.customer_site_use_id customer_site_use_id,
2873 ps.trx_number resolved_matching_number,
2874 ps.terms_sequence_number terms_sequence_number,
2875 decode(am.match_date_by,
2876 'INT_HDR_ATT1', fnd_conc_date.string_to_date(trx.interface_header_attribute1),
2877 'INT_HDR_ATT10', fnd_conc_date.string_to_date(trx.interface_header_attribute10),
2878 'INT_HDR_ATT11', fnd_conc_date.string_to_date(trx.interface_header_attribute11),
2879 'INT_HDR_ATT12', fnd_conc_date.string_to_date(trx.interface_header_attribute12),
2880 'INT_HDR_ATT13', fnd_conc_date.string_to_date(trx.interface_header_attribute13),
2881 'INT_HDR_ATT14', fnd_conc_date.string_to_date(trx.interface_header_attribute14),
2882 'INT_HDR_ATT15', fnd_conc_date.string_to_date(trx.interface_header_attribute15),
2883 'INT_HDR_ATT2', fnd_conc_date.string_to_date(trx.interface_header_attribute2),
2884 'INT_HDR_ATT3', fnd_conc_date.string_to_date(trx.interface_header_attribute3),
2885 'INT_HDR_ATT4', fnd_conc_date.string_to_date(trx.interface_header_attribute4),
2886 'INT_HDR_ATT5', fnd_conc_date.string_to_date(trx.interface_header_attribute5),
2887 'INT_HDR_ATT6', fnd_conc_date.string_to_date(trx.interface_header_attribute6),
2888 'INT_HDR_ATT7', fnd_conc_date.string_to_date(trx.interface_header_attribute7),
2889 'INT_HDR_ATT8', fnd_conc_date.string_to_date(trx.interface_header_attribute8),
2890 'INT_HDR_ATT9', fnd_conc_date.string_to_date(trx.interface_header_attribute9),
2891 'PURCH_ORDER_DATE', trx.purchase_order_date,
2892 'TRANS_DATE', trx.trx_date,
2893 NULL) resolved_matching_date,
2894 ps.trx_date trx_date,
2895 ps.class resolved_matching_class,
2896 ps.invoice_currency_code resolved_match_currency,
2897 ps.amount_due_original amount_due_original,
2898 ps.amount_due_remaining amount_due_remaining,
2899 ps.discount_taken_earned discount_taken_earned,
2900 ps.discount_taken_unearned discount_taken_unearned,
2901 ARPCURR.CURRROUND(ps.amount_due_remaining, ps.invoice_currency_code ) amount_applied,
2902 ROUND(NVL(ref.trans_to_receipt_rate,
2903 DECODE(ps.invoice_currency_code, cr.currency_code, NULL,
2904 NVL( ARP_AUTOAPPLY_API.get_cross_curr_rate(
2905 ref.amount_applied,
2906 ref.amount_applied_from,
2907 ps.invoice_currency_code,
2908 cr.currency_code
2909 )
2910 , GL_CURRENCY_API.GET_RATE_SQL(
2911 ps.invoice_currency_code,
2912 cr.currency_code,
2913 cr.receipt_date,
2914 arp_standard.sysparm.CROSS_CURRENCY_RATE_TYPE )
2915 )
2916 )
2917 ),38) trans_to_receipt_rate,
2918 NULL amount_applied_from, -- will be calculated later for xcurr app.
2919 ps.payment_schedule_id payment_schedule_id,
2920 NULL cons_inv_id, -- Not used here. Useful for BFBs. So null value selected.
2921 UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
2922 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) match_score_value,
2923 ps.org_id,
2924 ps.term_id term_id,
2925 am.automatch_id automatch_id,
2926 am.use_matching_date use_matching_date,
2927 am.use_matching_amount use_matching_amount,
2928 am.auto_match_threshold auto_match_threshold,
2929 amp.priority priority,
2930 cr.currency_code receipt_currency_code,
2931 cr.receipt_date receipt_date,
2932 ctt.allow_overapplication_flag allow_overapplication_flag,
2933 tr.partial_discount_flag partial_discount_flag,
2934 RANK() OVER (PARTITION BY trx.ct_reference, ps.customer_site_use_id,
2935 ref.remit_reference_id, ps.customer_trx_id
2936 ORDER BY ps.payment_schedule_id) AS reco_num
2937 FROM ar_cash_automatches am,
2938 ar_cash_automatch_rule_map amp,
2939 ar_cash_remit_refs_interim ref,
2940 ar_cash_receipts cr,
2941 ar_payment_schedules ps,
2942 ra_customer_trx trx,
2943 ra_cust_trx_types ctt,
2944 ra_terms tr
2945 WHERE am.automatch_id = p_automatch_id
2946 AND amp.automatch_id = am.automatch_id
2947 AND amp.automatch_set_id = ref.automatch_set_id
2948 AND ref.worker_number = p_worker_number
2949 AND ref.receipt_reference_status = 'AR_AM_NEW'
2950 AND cr.cash_receipt_id = ref.cash_receipt_id
2951 AND cr.receipt_date BETWEEN NVL(am.start_date, cr.receipt_date)
2952 AND NVL(am.end_date, to_date('31/12/4712','DD/MM/YYYY'))
2953 AND trx.ct_reference IS NOT NULL
2954 AND UTL_MATCH.edit_distance_similarity(REGEXP_REPLACE(REGEXP_REPLACE(trx.ct_reference, p_trans_format_str, '\2'), p_trans_float_str, ''),
2955 REGEXP_REPLACE(REGEXP_REPLACE(ref.invoice_reference, p_rem_format_str, '\2'), p_rem_float_str, '')) >= am.sugg_match_threshold
2956 AND ps.customer_trx_id = trx.customer_trx_id
2957 AND ps.selected_for_receipt_batch_id IS NULL
2958 AND ps.class NOT IN ('PMT', 'GUAR')
2959 AND ps.payment_schedule_id > 0
2960 AND ps.status = 'OP'
2961 AND ps.terms_sequence_number = NVL(ref.installment_reference,
2962 ps.terms_sequence_number)
2963 AND ps.customer_id IN (SELECT DECODE(ARP_STANDARD.sysparm.pay_unrelated_invoices_flag,'Y', ps.customer_id,
2964 NVL(cr.pay_from_customer, ps.customer_id))
2965 FROM DUAL
2966 UNION ALL
2967 SELECT related_cust_account_id
2968 FROM hz_cust_acct_relate_all rel
2969 WHERE rel.cust_account_id = cr.pay_from_customer
2970 AND rel.bill_to_flag = 'Y'
2971 AND rel.status = 'A'
2972 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y'
2973 UNION ALL
2974 SELECT rel.related_cust_account_id
2975 FROM ar_paying_relationships_v rel,
2976 hz_cust_accounts acc
2977 WHERE acc.cust_account_id = cr.pay_from_customer
2978 AND acc.party_id = rel.party_id
2979 AND cr.receipt_date >= effective_start_date
2980 AND cr.receipt_date <= effective_end_date
2981 AND ARP_STANDARD.sysparm.pay_unrelated_invoices_flag <> 'Y' )
2982 AND tr.term_id(+) = ps.term_id
2983 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
2984 l_selected_recos selected_recos_table;
2985 l_current_reco selected_recos_table;
2986 l_current_fetch_count NUMBER;
2987 l_outer_index NUMBER;
2988 l_current_reco_line NUMBER;
2989 got_current_block BOOLEAN;
2990 BEGIN
2991 IF (PG_DEBUG IN ('Y', 'C')) THEN
2992 log('arp_autoapply_api.insert_reference_recos(+)');
2993 END IF;
2994 OPEN select_recos;
2995 LOOP
2996 FETCH select_recos BULK COLLECT INTO l_selected_recos LIMIT G_MAX_ARRAY_SIZE;
2997 log('Count : '||l_selected_recos.COUNT);
2998 IF l_selected_recos.COUNT = 0 THEN
2999 IF l_current_reco.count > 0 THEN
3000 process_single_reco(l_current_reco
3001 , 'REFERENCE NUMBER');
3002 l_current_reco.DELETE;
3003 insert_recos(p_request_id);
3004 clear_reco_lines_struct;
3005 END IF;
3006 EXIT;
3007 END IF;
3008 l_current_fetch_count := l_selected_recos.COUNT;
3009 l_outer_index := 1;
3010 LOOP
3011 IF l_outer_index > l_current_fetch_count THEN
3012 insert_recos(p_request_id);
3013 clear_reco_lines_struct;
3014 EXIT;
3015 END IF;
3016 got_current_block := FALSE;
3017 LOOP
3018 l_current_reco_line := l_current_reco.COUNT;
3019 IF l_current_reco_line = 0 THEN
3020 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
3021 l_outer_index := l_outer_index + 1;
3022 ELSE
3023 IF l_current_reco(l_current_reco_line).reco_num < l_selected_recos(l_outer_index).reco_num THEN
3024 copy_current_record(l_current_reco, l_selected_recos, l_outer_index);
3025 l_outer_index := l_outer_index + 1;
3026 ELSE
3027 got_current_block := TRUE;
3028 END IF;
3029 END IF;
3030 IF got_current_block = TRUE OR l_outer_index > l_current_fetch_count THEN
3031 EXIT;
3032 END IF;
3033 END LOOP;
3034 IF l_outer_index > l_current_fetch_count THEN
3035 insert_recos(p_request_id);
3036 clear_reco_lines_struct;
3037 EXIT;
3038 END IF;
3039 process_single_reco(l_current_reco
3040 , 'REFERENCE NUMBER');
3041 l_current_reco.DELETE;
3042 END LOOP;
3043 END LOOP;
3044 IF (PG_DEBUG IN ('Y', 'C')) THEN
3045 log('arp_autoapply_api.insert_reference_recos(-)');
3046 END IF;
3047 EXCEPTION
3048 WHEN OTHERS THEN
3049 log('Exception from arp_autoapply_api.insert_reference_recos');
3050 log(SQLERRM);
3051 RAISE;
3052 END insert_reference_recos;
3053
3054 /*===========================================================================+
3055 * PROCEDURE *
3056 * VALIDATE_TRX_RECOS() *
3057 * DESCRIPTION *
3058 * Validates the recommendations generated for each reference. At the end *
3059 * of validation, only one valid recommendation should exist for application
3060 * for each reference. *
3061 * SCOPE - LOCAL *
3062 * ARGUMENTS *
3063 * IN : p_worker_number Current Worker Number *
3064 * p_req_id Request ID *
3065 * OUT : None *
3066 * *
3067 * RETURNS NONE *
3068 * ALGORITHM *
3069 * A recommendation will be unvalid for one of the following reasons : *
3070 * 1. AR_AA_BELOW_TRX_TSLD : Header Level Validation *
3071 * Match Score is less than Automatic Threshold value set at the *
3072 * automatch rule setup. *
3073 * 2. AR_AA_DATE_MISMATCH : Header Level Validation *
3074 * If Use_Matching_Date = 'ALWAYS' and if the matching_date provided at *
3075 * the reference is not equal to the recommendation date. *
3076 * 3. AR_AA_AMOUNT_MISMATCH : Line Level Validation *
3077 * If Use_Matching_Amount = 'ALWAYS' and if the amount_applied provided *
3078 * at the reference is not equal to the open balance of transaction. *
3079 * 4. AR_AA_CURR_NO_MATCH : Line Level Validation *
3080 * If reference currency code, if provided, is not equal to *
3081 * recommendation currency code. *
3082 * 5. AR_AA_NO_XCURR_RATE : Line Level Validation *
3083 * If exchange rate is not provided in case of a cross currency app. *
3084 * 6. AR_AA_INVALID_RATE : Line Level Validation *
3085 * If invalid rate is provided for fixed rate currencies. *
3086 * 7. AR_AA_NAT_APP_VIO : Line Level Validation *
3087 * Natural Application Violation *
3088 * 8. AR_AA_OVER_APPLN : Line Level Validation *
3089 * Over Application *
3090 * 9. AR_AA_MUL_APP_TRX : Line Level Validation *
3091 * If the same PS is already applied by the same receipt. *
3092 * 10. AR_AA_MUL_RECO_TRX : Line Level Validtion *
3093 * If the same transaction is selected for different references. *
3094 * 11. AR_AA_DUPLICATE_RECOS : Header Level Validation *
3095 * If two recommendations with same number is selected for a reference. *
3096 * 12. AR_AA_MULT_RECOS : Header Level Validation *
3097 * If more than one recommendations are valid for a reference. *
3098 * 13. AR_AA_CUST_NOT_UNIQUE : Header Level Validation *
3099 * If all the recommendations does not belong to a same customer in case*
3100 * if the receipt is unidentified. *
3101 * Finally at the end of validation the valid payment schedules selected *
3102 * for application are locked. This is done to counter the possibility of a *
3103 * deadlock if the process is run with multiple workers. In such a case a PS*
3104 * may be selected for different references for different workers. *
3105 * Final valid status at the end of validation : AR_AA_INV_LOCKED *
3106 * NOTES - *
3107 * 1. Validate Recos is called once per each worker. *
3108 *
3109 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
3110 * *
3111 +===========================================================================*/
3112 PROCEDURE validate_trx_recos( p_req_id IN NUMBER
3113 , p_worker_number IN NUMBER) IS
3114 TYPE psid_tab IS TABLE OF ar_payment_schedules.payment_schedule_id%TYPE INDEX BY PLS_INTEGER;
3115 locked_ps_records psid_tab;
3116 BEGIN
3117 IF (PG_DEBUG IN ('Y', 'C')) THEN
3118 log('arp_autoapply_api.validate_trx_recos(+)');
3119 END IF;
3120
3121 /* * If Use_Matching_Date is set to 'ALWAYS' in Automatch Rule Setup *
3122 * resolved_matching_date of the recommendations must equal the *
3123 * reference matching date provided by the user. If the date is not*
3124 * provided in 'Remittance Lines', then it will be treated as a mis*
3125 * match. * */
3126
3127 UPDATE ar_cash_recos rec
3128 SET rec.match_reason_code = 'AR_AA_DATE_MISMATCH'
3129 WHERE rec.request_id = p_req_id
3130 AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3131 AND EXISTS ( SELECT 'Date Not Matching'
3132 FROM ar_cash_automatches am,
3133 ar_cash_remit_refs_interim ref
3134 WHERE am.automatch_id = rec.automatch_id
3135 AND ref.remit_reference_id = rec.remit_reference_id
3136 AND am.use_matching_date = 'ALWAYS'
3137 AND trunc(rec.resolved_matching_date) <> NVL(ref.matching_reference_date, to_date('31/12/4712','DD/MM/YYYY'))
3138 );
3139 IF (PG_DEBUG IN ('Y', 'C')) THEN
3140 log('No. of recos updated to Date Mismatch: ' || SQL%ROWCOUNT );
3141 END IF;
3142
3143 UPDATE ar_cash_reco_lines l
3144 SET recommendation_reason = 'AR_AA_DATE_MISMATCH'
3145 WHERE recommendation_id IN (SELECT recommendation_id
3146 FROM ar_cash_recos r
3147 WHERE match_reason_code = 'AR_AA_DATE_MISMATCH'
3148 AND request_id = l.request_id)
3149 AND request_id = p_req_id;
3150
3151 IF (PG_DEBUG IN ('Y', 'C')) THEN
3152 log('No. of reco lines updated to Date Mismatch: ' || SQL%ROWCOUNT );
3153 END IF;
3154
3155 /* * If Use_Matching_Amount is set to 'Yes', then the transaction *
3156 * balance must equal the amount applied of the remittance line * */
3157 /*UPDATE ar_cash_recos rec
3158 SET rec.match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
3159 WHERE rec.request_id = p_req_id
3160 AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3161 AND EXISTS ( SELECT ref.remit_reference_id
3162 FROM ar_cash_reco_lines lin,
3163 ar_cash_automatches am,
3164 ar_cash_remit_refs_interim ref
3165 WHERE lin.request_id = p_req_id
3166 AND am.automatch_id = rec.automatch_id
3167 AND am.use_matching_amount = 'ALWAYS'
3168 AND rec.recommendation_id = lin.recommendation_id
3169 AND ref.remit_reference_id = rec.remit_reference_id
3170 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))
3171 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))
3172 );
3173
3174 IF (PG_DEBUG IN ('Y', 'C')) THEN
3175 log('No. of recos updated to Amount Mismatch: ' || SQL%ROWCOUNT );
3176 END IF;
3177
3178 UPDATE ar_cash_reco_lines l
3179 SET recommendation_reason = 'AR_AA_AMOUNT_MISMATCH'
3180 WHERE recommendation_id IN (SELECT recommendation_id
3181 FROM ar_cash_recos r
3182 WHERE match_reason_code = 'AR_AA_AMOUNT_MISMATCH'
3183 AND request_id = l.request_id)
3184 AND request_id = p_req_id;
3185
3186 IF (PG_DEBUG IN ('Y', 'C')) THEN
3187 log('No. of reco lines updated to Amount Mismatch: ' || SQL%ROWCOUNT );
3188 END IF;*/
3189
3190 --Do not apply the transaction if the currency provided is different to that of the transaction
3191 UPDATE ar_cash_reco_lines l
3192 SET recommendation_reason = 'AR_AA_CURR_NO_MATCH'
3193 WHERE EXISTS (SELECT 'Inconsistent Currency'
3194 FROM ar_cash_recos rec,
3195 ar_cash_remit_refs_interim ref,
3196 ar_payment_schedules ps
3197 WHERE rec.recommendation_id = l.recommendation_id
3198 AND ref.remit_reference_id = rec.remit_reference_id
3199 AND ref.worker_number = p_worker_number
3200 AND ps.payment_schedule_id = l.payment_schedule_id
3201 AND ps.invoice_currency_code<> NVL(ref.invoice_currency_code,
3202 ps.invoice_currency_code))
3203 AND request_id = p_req_id
3204 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3205
3206 IF (PG_DEBUG IN ('Y', 'C')) THEN
3207 log('No. of recos updated to No Currency Match: ' || SQL%ROWCOUNT );
3208 END IF;
3209
3210 --Do not autoapply a PS if any 2 of amount applied, amount applied from and trans to receipt rate
3211 --is not provided in the reference
3212 UPDATE ar_cash_reco_lines l
3213 SET recommendation_reason = 'AR_AA_NO_XCURR_RATE'
3214 WHERE EXISTS (SELECT 'No X Rate Info'
3215 FROM ar_payment_schedules ps
3216 WHERE l.payment_schedule_id = ps.payment_schedule_id
3217 AND l.receipt_currency_code <> ps.invoice_currency_code
3218 AND (l.trans_to_receipt_rate IS NULL
3219 OR l.trans_to_receipt_rate = -1))
3220 AND request_id = p_req_id
3221 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3222
3223 IF (PG_DEBUG IN ('Y', 'C')) THEN
3224 log('No. of recos updated to No Exchange Rate: ' || SQL%ROWCOUNT );
3225 END IF;
3226
3227 --Invalid rate provided for fixed rate currencies
3228 UPDATE ar_cash_reco_lines l
3229 SET recommendation_reason = 'AR_AA_INVALID_RATE'
3230 WHERE (EXISTS (SELECT 'Same Currency'
3231 FROM ar_payment_schedules ps
3232 WHERE l.payment_schedule_id = ps.payment_schedule_id
3233 AND l.receipt_currency_code = ps.invoice_currency_code
3234 AND l.trans_to_receipt_rate IS NOT NULL)
3235 OR EXISTS (SELECT 'Wrong rate for fixed rate currency'
3236 FROM ar_payment_schedules ps
3237 WHERE l.payment_schedule_id = ps.payment_schedule_id
3238 AND GL_CURRENCY_API.IS_FIXED_RATE(ps.invoice_currency_code,
3239 l.receipt_currency_code,
3240 l.receipt_date) = 'Y'
3241 AND l.trans_to_receipt_rate <> ROUND(GL_CURRENCY_API.GET_RATE_SQL(
3242 ps.invoice_currency_code,
3243 l.receipt_currency_code,
3244 l.receipt_date,
3245 null), 38)))
3246 AND request_id = p_req_id
3247 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3248
3249 IF (PG_DEBUG IN ('Y', 'C')) THEN
3250 log('No. of recos updated to Invalid Rate: ' || SQL%ROWCOUNT );
3251 END IF;
3252
3253 --Update the status if the application is not a natural application or an over application
3254 UPDATE ar_cash_reco_lines l
3255 SET recommendation_reason = (SELECT CASE WHEN SIGN(l.amount_applied*ps.amount_due_remaining) = -1
3256 THEN 'AR_AA_NAT_APP_VIO'
3257 ELSE recommendation_reason
3258 END
3259 FROM ar_payment_schedules ps
3260 WHERE ps.payment_schedule_id = l.payment_schedule_id)
3261 WHERE request_id = p_req_id
3262 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3263
3264 IF (PG_DEBUG IN ('Y', 'C')) THEN
3265 log('No. of recos updated to Nat Appn Vio/Over Appn: ' || SQL%ROWCOUNT );
3266 END IF;
3267
3268 --Prevent same PS applied twice to the same receipt
3269 UPDATE ar_cash_reco_lines l
3270 SET recommendation_reason = 'AR_AA_MUL_APP_TRX'
3271 WHERE ( EXISTS (SELECT 'PS already Applied'
3272 FROM ar_cash_recos rec,
3273 ar_cash_remit_refs_interim ref,
3274 ar_receivable_applications ra
3275 WHERE rec.recommendation_id = l.recommendation_id
3276 AND ref.remit_reference_id = rec.remit_reference_id
3277 AND ra.cash_receipt_id = ref.cash_receipt_id
3278 AND ref.worker_number = p_worker_number
3279 AND l.payment_schedule_id = ra.applied_payment_schedule_id
3280 AND ra.display = 'Y')
3281 )
3282 AND request_id = p_req_id
3283 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3284
3285 IF (PG_DEBUG IN ('Y', 'C')) THEN
3286 log('No. of recos updated to Multiple Application on same receipt: ' || SQL%ROWCOUNT );
3287 END IF;
3288
3289 /* Prevent application if the same PS is selected for any other reco
3290 Since we are validating per worker, at the end of validation there is
3291 a chance that same PS is selected by two receipts from diff workers */
3292 /* UPDATE ar_cash_reco_lines l
3293 SET recommendation_reason = 'AR_AA_MUL_RECO_TRX'
3294 WHERE EXISTS (SELECT 'PS eligible for more than one reference'
3295 FROM ar_cash_reco_lines l1,
3296 ar_cash_recos rec,
3297 ar_cash_recos rec1
3298 WHERE l.payment_schedule_id = l1.payment_schedule_id
3299 AND l.recommendation_id <> l1.recommendation_id
3300 AND rec.recommendation_id = l.recommendation_id
3301 AND rec1.recommendation_id = l1.recommendation_id
3302 AND rec.remit_reference_id <> rec1.remit_reference_id
3303 AND l1.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3304 AND l1.request_id = p_req_id)
3305 AND request_id = p_req_id
3306 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3307
3308 IF (PG_DEBUG IN ('Y', 'C')) THEN
3309 log('No. of recos updated to Same Trx for multiple Recos: ' || SQL%ROWCOUNT );
3310 END IF; */
3311 /*
3312 UPDATE ar_cash_recos rec
3313 SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3314 WHERE rec.recommendation_id IN
3315 (SELECT recommendation_id
3316 FROM ar_cash_recos
3317 WHERE request_id = p_req_id
3318 AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3319 (
3320 SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3321 FROM ar_cash_recos rec
3322 WHERE rec.request_id = p_req_id
3323 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3324 GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
3325 HAVING COUNT(*) > 1
3326 )
3327 MINUS
3328 SELECT recommendation_id
3329 FROM ar_cash_recos rec
3330 WHERE request_id = p_req_id
3331 AND (resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date) IN
3332 (
3333 SELECT resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
3334 FROM ar_cash_recos rec1
3335 WHERE request_id = p_req_id
3336 AND (resolved_matching_number, match_resolved_using, remit_reference_id) IN
3337 (
3338 SELECT resolved_matching_number, match_resolved_using, remit_reference_id
3339 FROM ar_cash_recos rec
3340 WHERE rec.request_id = p_req_id
3341 GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id
3342 HAVING COUNT(*) > 1
3343 )
3344 AND trunc(rec1.resolved_matching_date) = (SELECT decode(am.use_matching_date,
3345 'DUPLICATE', nvl(ref.matching_reference_date, rec1.resolved_matching_date),
3346 rec1.resolved_matching_date)
3347 FROM ar_cash_remit_refs_interim ref,
3348 ar_cash_automatches am
3349 WHERE ref.worker_number = p_worker_number
3350 AND ref.remit_reference_id = rec1.remit_reference_id
3351 AND am.automatch_id = rec1.automatch_id)
3352 AND EXISTS ( SELECT ref.remit_reference_id
3353 FROM ar_cash_reco_lines lin,
3354 ar_cash_automatches am,
3355 ar_cash_remit_refs_interim ref
3356 WHERE lin.request_id = p_req_id
3357 AND am.automatch_id = rec.automatch_id
3358 AND am.use_matching_amount = 'DUPLICATE'
3359 AND rec.recommendation_id = lin.recommendation_id
3360 AND ref.remit_reference_id = rec1.remit_reference_id
3361 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))
3362 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))
3363 )
3364 GROUP BY resolved_matching_number, match_resolved_using, remit_reference_id, resolved_matching_date
3365 HAVING count(*) = 1
3366 )
3367 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3368 )
3369 AND rec.request_id = p_req_id
3370 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD';
3371
3372 IF (PG_DEBUG IN ('Y', 'C')) THEN
3373 log('No. of recos updated to Duplicate Recos: ' || SQL%ROWCOUNT );
3374 END IF; */
3375
3376 UPDATE ar_cash_recos rec
3377 SET rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3378 WHERE request_id = p_req_id
3379 AND (resolved_matching_number, match_resolved_using, remit_reference_id )
3380 IN ( SELECT resolved_matching_number,
3381 match_resolved_using ,
3382 remit_reference_id
3383 FROM ar_cash_recos rec
3384 WHERE rec.request_id = p_req_id
3385 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3386 GROUP BY resolved_matching_number,
3387 match_resolved_using ,
3388 remit_reference_id
3389 HAVING COUNT(*) > 1);
3390
3391 IF (PG_DEBUG IN ('Y', 'C')) THEN
3392 log('No. of recos updated to Duplicate Recos: ' || SQL%ROWCOUNT );
3393 END IF;
3394
3395 DECLARE
3396 CURSOR dup_recos_cur IS
3397 SELECT rec.recommendation_id,
3398 rec.remit_reference_id,
3399 rec.resolved_matching_date,
3400 ref.matching_reference_date,
3401 sum(NVL(lin.amount_applied, 0)) amount_applied,
3402 sum(NVL(lin.discount_taken_earned, 0)) discount_taken_earned,
3403 ps.amount_due_remaining,
3404 lin.customer_trx_id,
3405 lin.receipt_date,
3406 am.use_matching_date,
3407 am.use_matching_amount
3408 FROM ar_cash_recos rec,
3409 ar_cash_reco_lines lin,
3410 ar_cash_remit_refs_interim ref,
3411 ar_cash_automatches am,
3412 ar_payment_schedules ps
3413 WHERE rec.request_id = p_req_id
3414 AND rec.match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3415 AND ref.remit_reference_id = rec.remit_reference_id
3416 AND ref.worker_number = p_worker_number
3417 AND lin.recommendation_id = rec.recommendation_id
3418 AND am.automatch_id = rec.automatch_id
3419 AND ps.customer_trx_id = lin.customer_trx_id
3420 GROUP BY rec.recommendation_id,
3421 rec.remit_reference_id,
3422 rec.resolved_matching_date,
3423 ref.matching_reference_date,
3424 ps.amount_due_remaining,
3425 lin.customer_trx_id,
3426 lin.receipt_date,
3427 am.use_matching_date,
3428 am.use_matching_amount
3429 ORDER BY rec.remit_reference_id, rec.recommendation_id;
3430
3431 l_rm_frm_dup_count NUMBER;
3432 l_old_remit_reference_id NUMBER := -1;
3433 l_rec_count NUMBER;
3434 l_passed_amount BOOLEAN;
3435 l_passed_date BOOLEAN;
3436 TYPE l_rm_frm_dup_rec_tbl IS TABLE OF ar_cash_recos.recommendation_id%TYPE INDEX BY BINARY_INTEGER;
3437 l_rm_frm_dup_rec l_rm_frm_dup_rec_tbl;
3438 i NUMBER;
3439 l_discount NUMBER;
3440 BEGIN
3441 l_rm_frm_dup_count := 1;
3442 FOR rec in dup_recos_cur LOOP
3443 IF rec.remit_reference_id <> l_old_remit_reference_id THEN
3444 l_old_remit_reference_id := rec.remit_reference_id;
3445 l_rec_count := 0;
3446 END IF;
3447 l_passed_amount := FALSE;
3448 l_passed_date := FALSE;
3449 IF rec.use_matching_date = 'DUPLICATE' THEN
3450 IF trunc(rec.resolved_matching_date) = rec.matching_reference_date THEN
3451 l_passed_date := TRUE;
3452 END IF;
3453 ELSE
3454 l_passed_date := TRUE;
3455 END IF;
3456 IF rec.use_matching_amount = 'DUPLICATE' THEN
3457 IF rec.amount_applied + rec.discount_taken_earned = rec.amount_due_remaining THEN
3458 l_passed_amount := TRUE;
3459 END IF;
3460 ELSE
3461 l_passed_amount := TRUE;
3462 END IF;
3463 IF l_passed_amount AND l_passed_date THEN
3464 l_rec_count := l_rec_count + 1;
3465 IF l_rec_count > 1 THEN
3466 l_rm_frm_dup_rec.DELETE(l_rm_frm_dup_count-1);
3467 l_rm_frm_dup_count := l_rm_frm_dup_count - 1;
3468 EXIT;
3469 END IF;
3470 l_rm_frm_dup_rec(l_rm_frm_dup_count) := rec.recommendation_id;
3471 l_rm_frm_dup_count := l_rm_frm_dup_count + 1;
3472 END IF;
3473 END LOOP;
3474
3475 FORALL i IN 1..NVL(l_rm_frm_dup_rec.LAST, 0)
3476 UPDATE ar_cash_recos
3477 SET match_reason_code = 'AR_AM_INV_THRESHOLD'
3478 WHERE request_id = p_req_id
3479 AND match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3480 AND recommendation_id = l_rm_frm_dup_rec(i);
3481
3482 IF (PG_DEBUG IN ('Y', 'C')) THEN
3483 log('No. of recos Corrected: ' || SQL%ROWCOUNT );
3484 END IF;
3485 END;
3486
3487 UPDATE ar_cash_reco_lines l
3488 SET recommendation_reason = 'AR_AA_DUPLICATE_RECOS'
3489 WHERE recommendation_id IN (SELECT recommendation_id
3490 FROM ar_cash_recos r
3491 WHERE match_reason_code = 'AR_AA_DUPLICATE_RECOS'
3492 AND request_id = l.request_id)
3493 AND request_id = p_req_id;
3494
3495 IF (PG_DEBUG IN ('Y', 'C')) THEN
3496 log('No. of reco lines updated to Duplicate Recos: ' || SQL%ROWCOUNT );
3497 END IF;
3498
3499 --Check if customer can be uniquely identified if not yet identified
3500 UPDATE ar_cash_recos rec
3501 SET match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
3502 WHERE remit_reference_id IN (SELECT remit_reference_id
3503 FROM ar_cash_remit_refs_interim ref1
3504 WHERE cash_receipt_id IN (
3505 SELECT cr.cash_receipt_id
3506 FROM ar_cash_receipts cr,
3507 ar_cash_remit_refs_interim ref,
3508 ar_cash_recos rec,
3509 ar_cash_reco_lines recl
3510 WHERE cr.autoapply_flag = 'Y'
3511 AND cr.pay_from_customer IS NULL
3512 AND cr.cash_receipt_id = ref.cash_receipt_id
3513 AND ref.remit_reference_id = rec.remit_reference_id
3514 AND ref.worker_number = p_worker_number
3515 AND recl.recommendation_id = rec.recommendation_id
3516 AND recl.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3517 AND EXISTS (SELECT 'Reco of Different Customer'
3518 FROM ar_cash_remit_refs_interim ref2,
3519 ar_cash_recos rec1,
3520 ar_cash_reco_lines recl1
3521 WHERE ref2.cash_receipt_id = ref.cash_receipt_id
3522 AND rec1.remit_reference_id = ref2.remit_reference_id
3523 AND recl1.recommendation_id = rec1.recommendation_id
3524 AND recl1.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3525 AND rec.recommendation_id <> rec1.recommendation_id
3526 AND rec.pay_from_customer <> rec1.pay_from_customer
3527 AND rec1.request_id = p_req_id
3528 AND ref2.worker_number = p_worker_number))
3529 AND ref1.worker_number = p_worker_number)
3530 AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3531 AND request_id = p_req_id;
3532 IF (PG_DEBUG IN ('Y', 'C')) THEN
3533 log('No. of recos updated to Non Unique Customer: ' || SQL%ROWCOUNT );
3534 END IF;
3535
3536 UPDATE ar_cash_reco_lines l
3537 SET recommendation_reason = 'AR_AA_CUST_NOT_UNIQUE'
3538 WHERE recommendation_id IN (SELECT recommendation_id
3539 FROM ar_cash_recos
3540 WHERE match_reason_code = 'AR_AA_CUST_NOT_UNIQUE'
3541 AND request_id = p_req_id)
3542 AND request_id = p_req_id
3543 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3544
3545 --Multiple recos for same reference
3546 UPDATE ar_cash_recos rec
3547 SET match_reason_code = 'AR_AA_MULT_RECOS'
3548 WHERE EXISTS (SELECT 'Multiple Recos'
3549 FROM ar_cash_recos rec1,
3550 ar_cash_reco_lines lin
3551 WHERE rec1.remit_reference_id = rec.remit_reference_id
3552 AND rec1.recommendation_id <> rec.recommendation_id
3553 AND lin.recommendation_id = rec1.recommendation_id
3554 AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3555 AND (CASE
3556 WHEN rec1.match_score_value > rec.match_score_value THEN 'T'
3557 WHEN rec1.match_score_value = rec.match_score_value THEN
3558 CASE WHEN rec1.priority >= rec.priority THEN 'T'
3559 END
3560 END) = 'T'
3561 AND lin.request_id = p_req_id)
3562 AND EXISTS (SELECT 'Applicable Reco Exist'
3563 FROM ar_cash_reco_lines lin
3564 WHERE lin.recommendation_id = rec.recommendation_id
3565 AND lin.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3566 AND lin.request_id = p_req_id)
3567 AND request_id = p_req_id
3568 AND match_reason_code = 'AR_AM_INV_THRESHOLD';
3569
3570 IF (PG_DEBUG IN ('Y', 'C')) THEN
3571 log('No. of recos updated to Multiple Recos: ' || SQL%ROWCOUNT );
3572 END IF;
3573
3574 UPDATE ar_cash_reco_lines l
3575 SET recommendation_reason = 'AR_AA_MULT_RECOS'
3576 WHERE EXISTS (SELECT 'Many Types of Recos'
3577 FROM ar_cash_recos rec
3578 WHERE l.recommendation_id = rec.recommendation_id
3579 AND rec.match_reason_code = 'AR_AA_MULT_RECOS'
3580 AND rec.request_id = p_req_id)
3581 AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
3582 AND request_id = p_req_id;
3583 IF (PG_DEBUG IN ('Y', 'C')) THEN
3584 log('No. of reco lines updated to Multiple Recos: ' || SQL%ROWCOUNT );
3585 END IF;
3586
3587 --Check if the allocated receipt amounts of all the valid recos exceed the receipt amount/balance
3588 UPDATE ar_cash_recos rec
3589 SET match_reason_code = 'AR_AA_REMIT_EXCEEDED'
3590 WHERE remit_reference_id IN
3591 (SELECT remit_reference_id
3592 FROM ar_cash_remit_refs_interim
3593 WHERE cash_receipt_id IN (
3594 SELECT ps.cash_receipt_id
3595 FROM ar_payment_schedules ps,
3596 ar_cash_receipts cr,
3597 ar_cash_remit_refs_interim ref,
3598 ar_cash_recos rec,
3599 ar_cash_reco_lines recl
3600 WHERE ps.cash_receipt_id = cr.cash_receipt_id
3601 AND ps.cash_receipt_id = ref.cash_receipt_id
3602 AND ref.remit_reference_id = rec.remit_reference_id
3603 AND ref.worker_number = p_worker_number
3604 AND recl.recommendation_id = rec.recommendation_id
3605 AND recl.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3606 AND recl.request_id = p_req_id
3607 GROUP BY ps.cash_receipt_id, ps.amount_due_remaining
3608 HAVING ps.amount_due_remaining*-1 < SUM(NVL(recl.amount_applied_from,
3609 recl.amount_applied))))
3610 AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3611 AND request_id = p_req_id;
3612 IF (PG_DEBUG IN ('Y', 'C')) THEN
3613 log('No. of recos updated to Remittance amount exceeded: ' || SQL%ROWCOUNT );
3614 END IF;
3615
3616 UPDATE ar_cash_reco_lines l
3617 SET recommendation_reason = 'AR_AA_REMIT_EXCEEDED'
3618 WHERE recommendation_id IN (SELECT recommendation_id
3619 FROM ar_cash_recos
3620 WHERE match_reason_code = 'AR_AA_REMIT_EXCEEDED'
3621 AND request_id = p_req_id)
3622 AND request_id = p_req_id
3623 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3624 IF (PG_DEBUG IN ('Y', 'C')) THEN
3625 log('No. of reco lines updated to Remittance amount exceeded: ' || SQL%ROWCOUNT );
3626 END IF;
3627
3628 /* * Update the unidentified cash receipts with the customer number of *
3629 * the valid recommendations. * */
3630
3631 DECLARE
3632 CURSOR unid_receipts IS
3633 SELECT distinct cash_receipt_id
3634 FROM ar_cash_remit_refs_interim
3635 WHERE worker_number = p_worker_number
3636 AND customer_id IS NULL;
3637
3638 l_cash_receipt_id AR_CASH_REMIT_REFS_INTERIM.cash_receipt_id%TYPE;
3639 l_customer_id AR_CASH_RECOS.pay_from_customer%TYPE;
3640 v_msg_count NUMBER(4);
3641 v_msg_data VARCHAR2(1000);
3642 v_return_status VARCHAR2(5);
3643 v_status VARCHAR2(100);
3644 loop_index NUMBER;
3645 BEGIN
3646 FOR unid_receipts_rec in unid_receipts LOOP
3647 l_cash_receipt_id := unid_receipts_rec.cash_receipt_id;
3648 SELECT decode(count(distinct rec.pay_from_customer),
3649 1, max(rec.pay_from_customer),
3650 NULL)
3651 INTO l_customer_id
3652 FROM ar_cash_recos rec,
3653 ar_cash_remit_refs_interim ref
3654 WHERE rec.request_id = p_req_id
3655 AND ref.cash_receipt_id = l_cash_receipt_id
3656 AND rec.remit_reference_id = ref.remit_reference_id
3657 AND ref.worker_number = p_worker_number
3658 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD';
3659 IF l_customer_id IS NOT NULL THEN
3660 log('Calling unid_to_unapp');
3661 log('Cash receipt Id : ' || l_cash_receipt_id);
3662 log('Customer Id : '|| l_customer_id);
3663 AR_RECEIPT_UPDATE_API_PUB.update_receipt_unid_to_unapp(
3664 p_api_version => 1.0,
3665 x_return_status => v_return_status,
3666 x_msg_count => v_msg_count,
3667 x_msg_data => v_msg_data,
3668 --p_commit => FND_API.G_TRUE,
3669 p_cash_receipt_id => l_cash_receipt_id,
3670 p_pay_from_customer => l_customer_id,
3671 x_status => v_status
3672 );
3673 log('Return Status '||v_return_status);
3674
3675 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3676 UPDATE ar_cash_recos
3677 SET match_reason_code = 'AR_AA_CUST_UNID'
3678 WHERE remit_reference_id IN (SELECT ref1.remit_reference_id
3679 FROM ar_cash_remit_refs_interim ref1
3680 WHERE ref1.cash_receipt_id = l_cash_receipt_id)
3681 AND match_reason_code = 'AR_AM_INV_THRESHOLD'
3682 AND request_id = p_req_id;
3683 END IF;
3684 END IF;
3685 END LOOP;
3686 UPDATE ar_cash_reco_lines l
3687 SET recommendation_reason = 'AR_AA_CUST_UNID'
3688 WHERE recommendation_id IN (SELECT recommendation_id
3689 FROM ar_cash_recos
3690 WHERE match_reason_code = 'AR_AA_CUST_UNID'
3691 AND request_id = p_req_id)
3692 AND request_id = p_req_id
3693 AND recommendation_reason = 'AR_AM_INV_THRESHOLD';
3694 END;
3695
3696 SELECT ps.payment_schedule_id
3697 BULK COLLECT INTO locked_ps_records
3698 FROM ar_payment_schedules ps,
3699 ar_cash_reco_lines lines
3700 WHERE lines.request_id = p_req_id
3701 AND lines.recommendation_reason = 'AR_AM_INV_THRESHOLD'
3702 AND ps.payment_schedule_id = lines.payment_schedule_id
3703 FOR UPDATE OF ps.amount_due_remaining SKIP LOCKED;
3704
3705 FORALL i IN 1..NVL(locked_ps_records.LAST, 0)
3706 UPDATE ar_cash_reco_lines
3707 SET recommendation_reason = 'AR_AA_INV_LOCKED'
3708 WHERE request_id = p_req_id
3709 AND recommendation_reason = 'AR_AM_INV_THRESHOLD'
3710 AND payment_schedule_id = locked_ps_records(i);
3711
3712
3713 IF (PG_DEBUG IN ('Y', 'C')) THEN
3714 log('arp_autoapply_api.validate_trx_recos(-)');
3715 END IF;
3716 EXCEPTION
3717 WHEN OTHERS THEN
3718 log('Exception from arp_autoapply_api.validate_trx_recos');
3719 log(SQLERRM);
3720 RAISE;
3721 END validate_trx_recos;
3722
3723 /*===========================================================================+
3724 * PROCEDURE *
3725 * APPLY_TRX_RECOS() *
3726 * DESCRIPTION *
3727 * Apply all valid recommendations and update the reference with resolved *
3728 * matching numbers. *
3729 * SCOPE - LOCAL *
3730 * ARGUMENTS *
3731 * IN : p_worker_number Current Worker Number *
3732 * p_req_id Request ID *
3733 * OUT : None *
3734 * *
3735 * RETURNS NONE *
3736 * ALGORITHM *
3737 * 1. Select Valid Recommendation lines ( with status 'AR_AA_INV_LOCKED') *
3738 * 2. For all recommendation lines *
3739 * 3. Select Next Recommendation Line *
3740 * 4. Apply the transaction *
3741 * 5. Compute the remaining balace for the reference *
3742 * 6. If balance > 0 go to Step 3. *
3743 * 7. Update the referene with Resolved matching number, currency etc . *
3744 * 8. Delete the recommendations for the references that were (automatically)
3745 * applied. *
3746 * NOTES - *
3747 * 1. APPLY_TRX_RECOS is called once per each worker. *
3748 * *
3749 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
3750 * *
3751 +===========================================================================*/
3752
3753 PROCEDURE apply_trx_recos(p_req_id IN NUMBER
3754 , p_worker_number IN NUMBER) IS
3755
3756 l_return_status VARCHAR2(10);
3757 l_msg_count NUMBER;
3758 l_msg_data VARCHAR2(3000);
3759 l_amount_rem NUMBER;
3760 l_line_num NUMBER;
3761 l_remit_reference_id NUMBER;
3762 l_old_remit_reference_id NUMBER := -1;
3763 l_ref_amt_applied NUMBER;
3764 loop_index NUMBER;
3765 l_ref_amt_applied_from NUMBER;
3766 l_rec_currency_code ar_cash_receipts.currency_code%TYPE;
3767 l_amount_applied BOOLEAN;
3768 l_amount_applied_from BOOLEAN;
3769
3770 CURSOR app_reco_cur IS
3771 SELECT distinct rec.remit_reference_id
3772 FROM ar_cash_recos rec
3773 WHERE rec.request_id = p_req_id
3774 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3775 AND rec.match_resolved_using <> 'BALANCE FORWARD BILL';
3776
3777 CURSOR app_reco_line_cur(p_remit_reference_id NUMBER) IS
3778 SELECT ref.cash_receipt_id,
3779 rec.remit_reference_id,
3780 NVL(ref.amount_applied, ARPCURR.CURRROUND((ref.amount_applied_from / NVL(lin.trans_to_receipt_rate, 1)), lin.receipt_currency_code)) ref_amount_applied,
3781 lin.amount_applied,
3782 lin.payment_schedule_id,
3783 lin.amount_applied_from,
3784 lin.trans_to_receipt_rate,
3785 lin.recommendation_id,
3786 lin.line_number,
3787 lin.receipt_currency_code,
3788 rec.resolved_match_currency
3789 FROM ar_cash_remit_refs_interim ref,
3790 ar_cash_recos rec,
3791 ar_cash_reco_lines lin
3792 WHERE rec.remit_reference_id = p_remit_reference_id
3793 AND ref.remit_reference_id = rec.remit_reference_id
3794 AND rec.recommendation_id = lin.recommendation_id
3795 AND ref.worker_number = p_worker_number
3796 AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
3797 AND rec.match_resolved_using <> 'BALANCE FORWARD BILL'
3798 AND lin.request_id = p_req_id
3799 ORDER BY lin.recommendation_id, lin.line_number;
3800
3801 CURSOR bfb_recos_cur(p_worker_number NUMBER) IS
3802 SELECT rec.remit_reference_id,
3803 rec.cons_inv_id,
3804 rec.recommendation_id,
3805 ref.amount_applied,
3806 ref.amount_applied_from,
3807 ref.cash_receipt_id,
3808 cr.currency_code
3809 FROM ar_cash_recos rec,
3810 ar_cash_remit_refs_interim ref,
3811 ar_cash_receipts cr
3812 WHERE rec.request_id = p_req_id
3813 AND rec.match_reason_code = 'AR_AM_INV_THRESHOLD'
3814 AND rec.match_resolved_using = 'BALANCE FORWARD BILL'
3815 AND ref.remit_reference_id = rec.remit_reference_id
3816 AND ref.worker_number = p_worker_number
3817 AND cr.cash_receipt_id = ref.cash_receipt_id;
3818
3819 CURSOR bfb_lines_cur(p_reco_id NUMBER) IS
3820 SELECT lin.recommendation_id,
3821 lin.line_number,
3822 lin.payment_schedule_id,
3823 lin.customer_trx_id,
3824 lin.amount_applied,
3825 lin.amount_applied_from,
3826 lin.trans_to_receipt_rate,
3827 ps.invoice_currency_code
3828 FROM ar_cash_reco_lines lin,
3829 ar_payment_schedules ps
3830 WHERE lin.recommendation_id = p_reco_id
3831 AND lin.request_id = p_req_id
3832 AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
3833 AND ps.payment_schedule_id = lin.payment_schedule_id
3834 ORDER BY lin.recommendation_id, lin.line_number;
3835
3836 BEGIN
3837 IF (PG_DEBUG IN ('Y', 'C')) THEN
3838 log('arp_autoapply_api.apply_trx_recos(+)');
3839 log('p_req_id: ' || p_req_id);
3840 log('p_worker_number: ' || p_worker_number);
3841 END IF;
3842 FOR app_reco IN app_reco_cur LOOP
3843 l_remit_reference_id := app_reco.remit_reference_id;
3844 FOR app_line IN app_reco_line_cur(l_remit_reference_id) LOOP
3845 IF l_old_remit_reference_id <> l_remit_reference_id THEN
3846 l_ref_amt_applied := app_line.ref_amount_applied;
3847 l_old_remit_reference_id := l_remit_reference_id;
3848 END IF;
3849 app_line.amount_applied := LEAST(app_line.amount_applied, l_ref_amt_applied);
3850 IF app_line.receipt_currency_code <> app_line.resolved_match_currency THEN
3851 calc_amt_applied_from(
3852 p_currency_code => app_line.receipt_currency_code,
3853 p_amount_applied => app_line.amount_applied,
3854 p_trans_to_receipt_rate => app_line.trans_to_receipt_rate,
3855 amount_applied_from => app_line.amount_applied_from);
3856 ELSE
3857 app_line.amount_applied_from := NULL;
3858 END IF;
3859
3860 IF (PG_DEBUG IN ('Y', 'C')) THEN
3861 log('Calling Apply API with parameters:');
3862 log('p_cash_receipt_id: ' || app_line.cash_receipt_id);
3863 log('p_applied_payment_schedule_id:' || app_line.payment_schedule_id);
3864 log('p_amount_applied: ' || app_line.amount_applied);
3865 END IF;
3866
3867 AR_RECEIPT_API_PUB.APPLY(
3868 p_api_version => 1.0,
3869 x_return_status => l_return_status,
3870 x_msg_count => l_msg_count,
3871 x_msg_data => l_msg_data,
3872 p_cash_receipt_id => app_line.cash_receipt_id,
3873 p_applied_payment_schedule_id => app_line.payment_schedule_id,
3874 p_amount_applied => app_line.amount_applied,
3875 p_amount_applied_from => app_line.amount_applied_from,
3876 p_trans_to_receipt_rate => app_line.trans_to_receipt_rate,
3877 p_org_id => ARP_STANDARD.sysparm.org_id);
3878
3879 IF (PG_DEBUG IN ('Y', 'C')) THEN
3880 log('Apply API Status :'|| l_return_status);
3881 END IF;
3882
3883 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3884 UPDATE ar_cash_reco_lines
3885 SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
3886 WHERE recommendation_id = app_line.recommendation_id
3887 AND line_number = app_line.line_number;
3888
3889 FOR loop_index in 1..l_msg_count LOOP
3890 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3891 IF l_msg_data IS NULL THEN
3892 EXIT;
3893 END IF;
3894 log('Error From Receipt API :' || loop_index ||' ---'||l_msg_data);
3895 END LOOP;
3896 ELSE
3897 l_ref_amt_applied := l_ref_amt_applied - app_line.amount_applied;
3898 EXIT WHEN l_ref_amt_applied = 0;
3899 END IF;
3900 END LOOP;
3901 END LOOP;
3902 FOR bfb_reco IN bfb_recos_cur(p_worker_number) LOOP
3903 l_remit_reference_id := bfb_reco.remit_reference_id;
3904 l_ref_amt_applied := bfb_reco.amount_applied;
3905 l_ref_amt_applied_from := bfb_reco.amount_applied_from;
3906 l_rec_currency_code := bfb_reco.currency_code;
3907 IF l_ref_amt_applied IS NOT NULL THEN
3908 l_amount_applied := TRUE;
3909 ELSIF l_ref_amt_applied_from IS NOT NULL THEN
3910 l_amount_applied_from := TRUE;
3911 END IF;
3912 FOR bfb_line in bfb_lines_cur(bfb_reco.recommendation_id) LOOP
3913 IF l_amount_applied THEN
3914 bfb_line.amount_applied := LEAST(bfb_line.amount_applied, l_ref_amt_applied);
3915 IF l_rec_currency_code <> bfb_line.invoice_currency_code THEN
3916 calc_amt_applied_from(
3917 p_currency_code => l_rec_currency_code,
3918 p_amount_applied => bfb_line.amount_applied,
3919 p_trans_to_receipt_rate => bfb_line.trans_to_receipt_rate,
3920 amount_applied_from => bfb_line.amount_applied_from);
3921 ELSE
3922 bfb_line.amount_applied_from := NULL;
3923 END IF;
3924 ELSIF l_amount_applied_from THEN
3925 bfb_line.amount_applied_from := least(bfb_line.amount_applied_from, nvl(l_ref_amt_applied_from, l_ref_amt_applied));
3926 IF l_rec_currency_code <> bfb_line.invoice_currency_code THEN
3927 calc_amt_applied(
3928 p_invoice_currency_code => bfb_line.invoice_currency_code,
3929 p_amount_applied_from => bfb_line.amount_applied_from,
3930 p_trans_to_receipt_rate => bfb_line.trans_to_receipt_rate,
3931 amount_applied => bfb_line.amount_applied);
3932 ELSE
3933 bfb_line.amount_applied := bfb_line.amount_applied_from;
3934 END IF;
3935 END IF;
3936
3937 IF (PG_DEBUG IN ('Y', 'C')) THEN
3938 log('Calling Apply API with parameters:');
3939 log('p_cash_receipt_id: ' || bfb_reco.cash_receipt_id);
3940 log('p_applied_payment_schedule_id:' || bfb_line.payment_schedule_id);
3941 log('p_amount_applied: ' || bfb_line.amount_applied);
3942 END IF;
3943
3944 AR_RECEIPT_API_PUB.APPLY(
3945 p_api_version => 1.0,
3946 x_return_status => l_return_status,
3947 x_msg_count => l_msg_count,
3948 x_msg_data => l_msg_data,
3949 p_cash_receipt_id => bfb_reco.cash_receipt_id,
3950 p_applied_payment_schedule_id => bfb_line.payment_schedule_id,
3951 p_amount_applied => bfb_line.amount_applied,
3952 p_amount_applied_from => bfb_line.amount_applied_from,
3953 p_trans_to_receipt_rate => bfb_line.trans_to_receipt_rate,
3954 p_org_id => ARP_STANDARD.SYSPARM.org_id);
3955
3956 IF (PG_DEBUG IN ('Y', 'C')) THEN
3957 log('Apply API Status :'|| l_return_status);
3958 END IF;
3959
3960 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3961 UPDATE ar_cash_reco_lines
3962 SET recommendation_reason = 'AR_AA_REC_APP_IN_ERROR'
3963 WHERE recommendation_id = bfb_line.recommendation_id
3964 AND line_number = bfb_line.line_number;
3965 FOR loop_index in 1..l_msg_count LOOP
3966 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3967 IF l_msg_data IS NULL THEN
3968 EXIT;
3969 END IF;
3970 log('Error From Receipt API :' || loop_index ||' ---'||l_msg_data);
3971 END LOOP;
3972 ELSE
3973 IF l_amount_applied THEN
3974 l_ref_amt_applied := l_ref_amt_applied - bfb_line.amount_applied;
3975 EXIT WHEN l_ref_amt_applied = 0;
3976 ELSIF l_amount_applied_from THEN
3977 l_ref_amt_applied_from := l_ref_amt_applied_from - bfb_line.amount_applied_from;
3978 EXIT WHEN l_ref_amt_applied_from = 0;
3979 END IF;
3980 END IF;
3981 END LOOP;
3982 END LOOP;
3983
3984 UPDATE ar_cash_remit_refs ref
3985 SET (receipt_reference_status,
3986 resolved_matching_number,
3987 auto_applied,
3988 match_score_value,
3989 resolved_matching_date,
3990 invoice_currency_code,
3991 match_resolved_using) =(SELECT 'AR_AA_INV_APPLIED',
3992 rec.resolved_matching_number,
3993 'Y',
3994 rec.match_score_value,
3995 rec.resolved_matching_date,
3996 rec.resolved_match_currency,
3997 rec.automatch_id
3998 FROM ar_cash_recos rec,
3999 ar_cash_reco_lines lin
4000 WHERE ref.remit_reference_id = rec.remit_reference_id
4001 AND lin.recommendation_id = rec.recommendation_id
4002 AND rec.request_id = p_req_id
4003 AND recommendation_type = 'TRX'
4004 AND lin.recommendation_reason = 'AR_AA_INV_LOCKED'
4005 AND rownum =1)
4006 WHERE EXISTS (SELECT 'Found Match'
4007 FROM ar_cash_recos rec,
4008 ar_cash_reco_lines lin
4009 WHERE ref.remit_reference_id = rec.remit_reference_id
4010 AND lin.recommendation_id = rec.recommendation_id
4011 AND lin.request_id = p_req_id
4012 AND recommendation_type = 'TRX'
4013 AND lin.recommendation_reason = 'AR_AA_INV_LOCKED');
4014 IF (PG_DEBUG IN ('Y', 'C')) THEN
4015 log('No. of References updated with Resolved Matching Number: ' || SQL%ROWCOUNT );
4016 END IF;
4017
4018 DELETE FROM ar_cash_reco_lines lin
4019 WHERE EXISTS (SELECT 'Delete Recos'
4020 FROM ar_cash_remit_refs ref,
4021 ar_cash_recos rec
4022 WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
4023 AND lin.recommendation_id = rec.recommendation_id
4024 AND rec.remit_reference_id = ref.remit_reference_id)
4025 AND request_id = p_req_id;
4026
4027 DELETE FROM ar_cash_recos rec
4028 WHERE EXISTS (SELECT 'Delete Recos'
4029 FROM ar_cash_remit_refs ref
4030 WHERE ref.receipt_reference_status = 'AR_AA_INV_APPLIED'
4031 AND rec.remit_reference_id = ref.remit_reference_id)
4032 AND request_id = p_req_id
4033 AND recommendation_type = 'TRX';
4034
4035 IF (PG_DEBUG IN ('Y', 'C')) THEN
4036 log('No. of Recos deleted: ' || SQL%ROWCOUNT );
4037 END IF;
4038 IF (PG_DEBUG IN ('Y', 'C')) THEN
4039 log('arp_autoapply_api.apply_trx_recos(-)' );
4040 END IF;
4041 EXCEPTION
4042 WHEN OTHERS THEN
4043 log('Exception from arp_autoapply_api.apply_trx_recos');
4044 log(SQLERRM);
4045 RAISE;
4046 END apply_trx_recos;
4047
4048 PROCEDURE copy_current_record( p_current_reco IN OUT NOCOPY selected_recos_table
4049 , p_selected_recos IN selected_recos_table
4050 , p_index IN NUMBER) IS
4051 i NUMBER;
4052 BEGIN
4053 IF (PG_DEBUG IN ('Y', 'C')) THEN
4054 log('copy_current_record()+');
4055 log('p_index : '|| p_index);
4056 END IF;
4057 i := p_current_reco.COUNT + 1;
4058 p_current_reco(i).remit_reference_id := p_selected_recos(p_index).remit_reference_id;
4059 p_current_reco(i).ref_amount_applied := p_selected_recos(p_index).ref_amount_applied;
4060 p_current_reco(i).ref_amount_applied_from := p_selected_recos(p_index).ref_amount_applied_from;
4061 p_current_reco(i).ref_trans_to_receipt_rate := p_selected_recos(p_index).ref_trans_to_receipt_rate;
4062 p_current_reco(i).payment_schedule_id := p_selected_recos(p_index).payment_schedule_id;
4063 p_current_reco(i).amount_applied := p_selected_recos(p_index).amount_applied;
4064 p_current_reco(i).amount_applied_from := p_selected_recos(p_index).amount_applied_from;
4065 p_current_reco(i).cash_receipt_id := p_selected_recos(p_index).cash_receipt_id;
4066 p_current_reco(i).pay_from_customer := p_selected_recos(p_index).pay_from_customer;
4067 p_current_reco(i).cr_customer_site_use_id := p_selected_recos(p_index).cr_customer_site_use_id;
4068 p_current_reco(i).amount_due_original := p_selected_recos(p_index).amount_due_original;
4069 p_current_reco(i).amount_due_remaining := p_selected_recos(p_index).amount_due_remaining;
4070 p_current_reco(i).discount_taken_earned := p_selected_recos(p_index).discount_taken_earned;
4071 p_current_reco(i).discount_taken_unearned := p_selected_recos(p_index).discount_taken_unearned;
4072 p_current_reco(i).customer_trx_id := p_selected_recos(p_index).customer_trx_id;
4073 p_current_reco(i).customer_id := p_selected_recos(p_index).customer_id;
4074 p_current_reco(i).customer_site_use_id := p_selected_recos(p_index).customer_site_use_id;
4075 p_current_reco(i).resolved_matching_number := p_selected_recos(p_index).resolved_matching_number;
4076 p_current_reco(i).terms_sequence_number := p_selected_recos(p_index).terms_sequence_number;
4077 p_current_reco(i).resolved_matching_date := p_selected_recos(p_index).resolved_matching_date;
4078 p_current_reco(i).trx_date := p_selected_recos(p_index).trx_date;
4079 p_current_reco(i).resolved_matching_class := p_selected_recos(p_index).resolved_matching_class;
4080 p_current_reco(i).resolved_match_currency := p_selected_recos(p_index).resolved_match_currency;
4081 p_current_reco(i).amount_applied := p_selected_recos(p_index).amount_applied;
4082 p_current_reco(i).amount_applied_from := p_selected_recos(p_index).amount_applied_from;
4083 p_current_reco(i).trans_to_receipt_rate := p_selected_recos(p_index).trans_to_receipt_rate;
4084 p_current_reco(i).payment_schedule_id := p_selected_recos(p_index).payment_schedule_id;
4085 p_current_reco(i).match_score_value := p_selected_recos(p_index).match_score_value;
4086 p_current_reco(i).org_id := p_selected_recos(p_index).org_id;
4087 p_current_reco(i).term_id := p_selected_recos(p_index).term_id;
4088 p_current_reco(i).automatch_id := p_selected_recos(p_index).automatch_id;
4089 p_current_reco(i).use_matching_date := p_selected_recos(p_index).use_matching_date;
4090 p_current_reco(i).use_matching_amount := p_selected_recos(p_index).use_matching_amount;
4091 p_current_reco(i).auto_match_threshold := p_selected_recos(p_index).auto_match_threshold;
4092 p_current_reco(i).priority := p_selected_recos(p_index).priority;
4093 p_current_reco(i).receipt_currency_code := p_selected_recos(p_index).receipt_currency_code;
4094 p_current_reco(i).receipt_date := p_selected_recos(p_index).receipt_date;
4095 p_current_reco(i).allow_overapplication_flag := p_selected_recos(p_index).allow_overapplication_flag;
4096 p_current_reco(i).partial_discount_flag := p_selected_recos(p_index).partial_discount_flag;
4097 p_current_reco(i).reco_num := p_selected_recos(p_index).reco_num;
4098 IF (PG_DEBUG IN ('Y', 'C')) THEN
4099 log('copy_current_record()-');
4100 END IF;
4101 END copy_current_record;
4102
4103 PROCEDURE process_single_reco(p_current_reco IN OUT NOCOPY selected_recos_table
4104 , p_match_resolved_using IN VARCHAR2) IS
4105 l_block_index NUMBER;
4106 l_recommendation_id NUMBER;
4107 l_recommendation_reason VARCHAR2(30);
4108 l_use_matching_date AR_CASH_AUTOMATCHES.use_matching_date%TYPE;
4109 l_use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE;
4110 l_ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4111 l_ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE;
4112 l_ref_orig_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4113 l_ref_rem_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4114 l_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE;
4115 l_res_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4116 l_match_score_value AR_CASH_RECOS.match_score_value%TYPE;
4117 l_receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE;
4118 l_resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4119 l_trx_amt_due_rem AR_CASH_REMIT_REFS.amount_applied%TYPE := 0;
4120 l_discount_taken AR_CASH_RECO_LINES.discount_taken_earned%TYPE := 0;
4121 l_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE;
4122 l_out_amount_to_apply AR_CASH_REMIT_REFS.amount_applied%TYPE;
4123 l_out_discount_to_take AR_CASH_REMIT_REFS.amount_applied%TYPE;
4124 l_valid VARCHAR2(1);
4125 BEGIN
4126 IF (PG_DEBUG IN ('Y', 'C')) THEN
4127 log('process_single_reco()+');
4128 END IF;
4129 SELECT ar_cash_recos_s.nextval
4130 INTO l_recommendation_id
4131 FROM dual;
4132 l_recommendation_reason := 'AR_AM_INV_THRESHOLD';
4133 l_use_matching_date := p_current_reco(1).use_matching_date;
4134 l_use_matching_amount := p_current_reco(1).use_matching_amount;
4135 l_ref_amount_applied := p_current_reco(1).ref_amount_applied;
4136 l_ref_amount_applied_from := p_current_reco(1).ref_amount_applied_from;
4137 l_trans_to_receipt_rate := p_current_reco(1).trans_to_receipt_rate;
4138 l_res_matching_date := p_current_reco(1).resolved_matching_date;
4139 l_match_score_value := p_current_reco(1).match_score_value;
4140 l_receipt_currency_code := p_current_reco(1).receipt_currency_code;
4141 l_resolved_match_currency := p_current_reco(1).resolved_match_currency;
4142
4143 IF l_receipt_currency_code = l_resolved_match_currency THEN
4144 IF l_ref_amount_applied IS NULL THEN
4145 l_ref_amount_applied := l_ref_amount_applied_from;
4146 END IF;
4147 ELSE
4148 /* In case of cross currency transaction, calculate the missing values */
4149 IF l_ref_amount_applied IS NULL THEN
4150 calc_amt_applied(
4151 p_invoice_currency_code => l_resolved_match_currency,
4152 p_amount_applied_from => l_ref_amount_applied_from,
4153 p_trans_to_receipt_rate => l_trans_to_receipt_rate,
4154 amount_applied => l_ref_amount_applied);
4155 ELSIF l_ref_amount_applied_from IS NULL THEN
4156 calc_amt_applied_from(
4157 p_currency_code => l_receipt_currency_code,
4158 p_amount_applied => l_ref_amount_applied,
4159 p_trans_to_receipt_rate => l_trans_to_receipt_rate,
4160 amount_applied_from => l_ref_amount_applied_from);
4161 END IF;
4162 /* At this point we have all values related to a xcurr application.
4163 Validate the values */
4164 AR_CC_LOCKBOX.are_values_valid(
4165 p_invoice_currency_code => l_resolved_match_currency,
4166 p_amount_applied_from => l_ref_amount_applied_from,
4167 p_trans_to_receipt_rate => l_trans_to_receipt_rate,
4168 p_amount_applied => l_ref_amount_applied,
4169 p_currency_code => l_receipt_currency_code,
4170 valid => l_valid
4171 );
4172 IF l_valid <> 'Y' THEN
4173 l_recommendation_reason := 'AR_AA_INV_XCURR_APP';
4174 END IF;
4175 END IF;
4176 l_ref_orig_amount := l_ref_amount_applied;
4177 l_ref_rem_amount := l_ref_amount_applied;
4178 FOR l_index in 1..p_current_reco.LAST LOOP
4179 l_trx_amt_due_rem := l_trx_amt_due_rem + p_current_reco(l_index).amount_applied;
4180 IF l_ref_rem_amount > 0 THEN
4181 l_amount_applied := LEAST(l_ref_rem_amount, p_current_reco(l_index).amount_applied);
4182 log('l_amount_applied :'||l_amount_applied);
4183 calc_amount_app_and_disc(
4184 p_customer_id => NVL(p_current_reco(l_index).pay_from_customer,
4185 p_current_reco(l_index).customer_id)
4186 , p_bill_to_site_use_id => NVL(p_current_reco(l_index).cr_customer_site_use_id,
4187 p_current_reco(l_index).customer_site_use_id)
4188 , p_invoice_currency_code => l_resolved_match_currency
4189 , p_ps_id => p_current_reco(l_index).payment_schedule_id
4190 , p_term_id => p_current_reco(l_index).term_id
4191 , p_terms_sequence_number => p_current_reco(l_index).terms_sequence_number
4192 , p_trx_date => p_current_reco(l_index).trx_date
4193 , p_allow_overapp_flag => p_current_reco(l_index).allow_overapplication_flag
4194 , p_partial_discount_flag => p_current_reco(l_index).partial_discount_flag
4195 , p_input_amount => l_amount_applied
4196 , p_amount_due_original => p_current_reco(l_index).amount_due_original
4197 , p_amount_due_remaining => p_current_reco(l_index).amount_due_remaining
4198 , p_discount_taken_earned => p_current_reco(l_index).discount_taken_earned
4199 , p_discount_taken_unearned => p_current_reco(l_index).discount_taken_unearned
4200 , p_cash_receipt_id => p_current_reco(l_index).cash_receipt_id
4201 , x_out_amount_to_apply => l_out_amount_to_apply
4202 , x_out_discount_to_take => l_out_discount_to_take);
4203 p_current_reco(l_index).discount_taken_earned := NVL(l_out_discount_to_take, 0);
4204 l_discount_taken := l_discount_taken + NVL(l_out_discount_to_take, 0);
4205 log('l_out_amount_to_apply : '||l_out_amount_to_apply);
4206 IF l_amount_applied <> l_out_amount_to_apply THEN
4207 log('If');
4208 p_current_reco(l_index).amount_applied := l_out_amount_to_apply;
4209 ELSE
4210 log('Else');
4211 p_current_reco(l_index).amount_applied := l_amount_applied;
4212 END IF;
4213 log('Amount Applied :'||p_current_reco(l_index).amount_applied);
4214 IF NVL(l_trans_to_receipt_rate, -1) <> -1 THEN
4215 calc_amt_applied_from(
4216 p_currency_code => l_receipt_currency_code,
4217 p_amount_applied => p_current_reco(l_index).amount_applied,
4218 p_trans_to_receipt_rate => l_trans_to_receipt_rate,
4219 amount_applied_from => p_current_reco(l_index).amount_applied_from);
4220 END IF;
4221 l_ref_rem_amount := l_ref_rem_amount - p_current_reco(l_index).amount_applied;
4222 ELSE
4223 p_current_reco(l_index).amount_applied := 0;
4224 END IF;
4225 END LOOP;
4226 IF l_recommendation_reason = 'AR_AM_INV_THRESHOLD' THEN
4227 IF p_current_reco(1).match_score_value < p_current_reco(1).auto_match_threshold THEN
4228 l_recommendation_reason := 'AR_AA_BELOW_TRX_TSLD';
4229 ELSIF p_current_reco(1).use_matching_amount = 'ALWAYS' THEN
4230 IF l_ref_amount_applied <> l_trx_amt_due_rem - NVL(l_discount_taken, 0) THEN
4231 log('Ref Amt :' || l_ref_amount_applied);
4232 log('Trx Amt :' || l_trx_amt_due_rem);
4233 l_recommendation_reason := 'AR_AA_AMOUNT_MISMATCH';
4234 END IF;
4235 ELSIF l_ref_rem_amount <> 0 THEN
4236 IF p_current_reco(1).allow_overapplication_flag = 'Y'
4237 AND p_match_resolved_using <> 'BALANCE FORWARD BILL' THEN
4238 p_current_reco(p_current_reco.LAST).amount_applied := p_current_reco(p_current_reco.LAST).amount_applied +
4239 l_ref_rem_amount;
4240 l_ref_rem_amount := 0;
4241 ELSE
4242 IF l_ref_orig_amount > l_trx_amt_due_rem THEN
4243 l_recommendation_reason := 'AR_AA_OVER_APPLN';
4244 END IF;
4245 END IF;
4246 END IF;
4247 END IF;
4248 populate_reco_line_struct(p_current_reco
4249 , p_match_resolved_using
4250 , l_recommendation_id
4251 , l_recommendation_reason);
4252 IF (PG_DEBUG IN ('Y', 'C')) THEN
4253 log('process_single_reco()-');
4254 END IF;
4255 EXCEPTION
4256 WHEN OTHERS THEN
4257 log('Exception from arp_autoapply_api.process_single_reco');
4258 log(SQLERRM);
4259 RAISE;
4260 END process_single_reco;
4261
4262 PROCEDURE populate_reco_line_struct(p_current_reco IN selected_recos_table
4263 , p_match_resolved_using IN VARCHAR2
4264 , p_recommendation_id IN NUMBER
4265 , p_recommendation_reason IN VARCHAR2) IS
4266 l_index NUMBER;
4267 BEGIN
4268 IF (PG_DEBUG IN ('Y', 'C')) THEN
4269 log('populate_reco_line_struct()+');
4270 log('Recommendation ID : '||p_recommendation_id);
4271 log('Recommendation Reason : '||p_recommendation_reason);
4272 log('Match Resolved Using : '||p_match_resolved_using);
4273 END IF;
4274 g_reco_index := reco_id_arr.COUNT;
4275 FOR l_index IN 1 .. NVL(p_current_reco.LAST, 0) LOOP
4276 IF p_current_reco(l_index).amount_applied <> 0 THEN
4277 g_reco_index := g_reco_index + 1;
4278
4279 reco_id_arr(g_reco_index) := p_recommendation_id;
4280 remit_ref_id_arr(g_reco_index) := p_current_reco(l_index).remit_reference_id;
4281 customer_id_arr(g_reco_index) := p_current_reco(l_index).customer_id;
4282 customer_site_use_id_arr(g_reco_index) := p_current_reco(l_index).customer_site_use_id;
4283 resolved_matching_number_arr(g_reco_index) := p_current_reco(l_index).resolved_matching_number;
4284 resolved_matching_date_arr(g_reco_index) := p_current_reco(l_index).resolved_matching_date;
4285 resolved_matching_class_arr(g_reco_index) := p_current_reco(l_index).resolved_matching_class;
4286 resolved_match_currency_arr(g_reco_index) := p_current_reco(l_index).resolved_match_currency;
4287 match_resolved_using_arr(g_reco_index) := p_match_resolved_using;
4288 cons_inv_id_arr(g_reco_index) := p_current_reco(l_index).cons_inv_id;
4289 match_score_value_arr(g_reco_index) := p_current_reco(l_index).match_score_value;
4290 match_reason_code_arr(g_reco_index) := p_recommendation_reason;
4291 org_id_arr(g_reco_index) := p_current_reco(l_index).org_id;
4292 automatch_id_arr(g_reco_index) := p_current_reco(l_index).automatch_id;
4293 priority_arr(g_reco_index) := p_current_reco(l_index).priority;
4294 reco_num_arr(g_reco_index) := p_current_reco(l_index).reco_num;
4295 customer_trx_id_arr(g_reco_index) := p_current_reco(l_index).customer_trx_id;
4296 payment_schedule_id_arr(g_reco_index) := p_current_reco(l_index).payment_schedule_id;
4297 amount_applied_arr(g_reco_index) := p_current_reco(l_index).amount_applied;
4298 amount_applied_from_arr(g_reco_index) := p_current_reco(l_index).amount_applied_from;
4299 trans_to_receipt_rate_arr(g_reco_index) := p_current_reco(l_index).trans_to_receipt_rate;
4300 receipt_currency_code_arr(g_reco_index) := p_current_reco(l_index).receipt_currency_code;
4301 receipt_date_arr(g_reco_index) := p_current_reco(l_index).receipt_date;
4302 discount_taken_earned_arr(g_reco_index) := p_current_reco(l_index).discount_taken_earned;
4303 discount_taken_unearned_arr(g_reco_index) := p_current_reco(l_index).discount_taken_unearned;
4304 END IF;
4305 END LOOP;
4306 IF (PG_DEBUG IN ('Y', 'C')) THEN
4307 log('populate_reco_line_struct()-');
4308 END IF;
4309 EXCEPTION
4310 WHEN OTHERS THEN
4311 log('Exception from arp_autoapply_api.populate_reco_line_struct');
4312 log(SQLERRM);
4313 RAISE;
4314 END populate_reco_line_struct;
4315
4316 PROCEDURE clear_Reco_lines_struct IS
4317 BEGIN
4318 IF (PG_DEBUG IN ('Y', 'C')) THEN
4319 log('clear_Reco_lines_struct()+');
4320 END IF;
4321 g_reco_index := 0;
4322 reco_id_arr.DELETE;
4323 remit_ref_id_arr.DELETE;
4324 customer_id_arr.DELETE;
4325 customer_site_use_id_arr.DELETE;
4326 resolved_matching_number_arr.DELETE;
4327 resolved_matching_date_arr.DELETE;
4328 resolved_matching_class_arr.DELETE;
4329 resolved_match_currency_arr.DELETE;
4330 match_resolved_using_arr.DELETE;
4331 cons_inv_id_arr.DELETE;
4332 match_score_value_arr.DELETE;
4333 match_reason_code_arr.DELETE;
4334 org_id_arr.DELETE;
4335 priority_arr.DELETE;
4336 reco_num_arr.DELETE;
4337 customer_trx_id_arr.DELETE;
4338 payment_schedule_id_arr.DELETE;
4339 amount_applied_arr.DELETE;
4340 amount_applied_from_arr.DELETE;
4341 trans_to_receipt_rate_arr.DELETE;
4342 receipt_currency_code_arr.DELETE;
4343 receipt_date_arr.DELETE;
4344 recommendation_reason_arr.DELETE;
4345 discount_taken_earned_arr.DELETE;
4346 discount_taken_unearned_arr.DELETE;
4347 IF (PG_DEBUG IN ('Y', 'C')) THEN
4348 log('clear_Reco_lines_struct()-');
4349 END IF;
4350 EXCEPTION
4351 WHEN OTHERS THEN
4352 log('Exception from arp_autoapply_api.clear_Reco_lines_struct');
4353 log(SQLERRM);
4354 RAISE;
4355 END clear_Reco_lines_struct;
4356
4357 PROCEDURE insert_recos(p_request_id IN NUMBER) IS
4358 l_reco_index NUMBER;
4359 l_reco_line_index NUMBER;
4360 TYPE rec_rows_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4361 rec_rows_arr rec_rows_tab;
4362 l_index NUMBER;
4363 BEGIN
4364 IF (PG_DEBUG IN ('Y', 'C')) THEN
4365 log('insert_recos()+');
4366 END IF;
4367
4368 FOR l_reco_index IN 1 .. NVL(reco_num_arr.LAST, 0) LOOP
4369 IF reco_num_arr(l_reco_index) = 1 THEN
4370 INSERT
4371 INTO ar_cash_recos_all (
4372 recommendation_id,
4373 recommendation_type,
4374 recommendation_source,
4375 remit_reference_id,
4376 pay_from_customer,
4377 customer_site_use_id,
4378 resolved_matching_number,
4379 resolved_matching_date,
4380 resolved_matching_class,
4381 resolved_match_currency,
4382 cons_inv_id,
4383 match_resolved_using,
4384 match_score_value,
4385 match_reason_code,
4386 recommendation_status,
4387 autoapply_status,
4388 org_id,
4389 created_by,
4390 creation_date,
4391 last_updated_by,
4392 last_update_date,
4393 last_update_login,
4394 program_application_id,
4395 program_id,
4396 program_update_date,
4397 request_id,
4398 automatch_id,
4399 priority)
4400 VALUES (reco_id_arr(l_reco_index),
4401 'TRX',
4402 'AUTOMATCH',
4403 remit_ref_id_arr(l_reco_index),
4404 customer_id_arr(l_reco_index),
4405 customer_site_use_id_arr(l_reco_index),
4406 resolved_matching_number_arr(l_reco_index),
4407 resolved_matching_date_arr(l_reco_index),
4408 resolved_matching_class_arr(l_reco_index),
4409 resolved_match_currency_arr(l_reco_index),
4410 cons_inv_id_arr(l_reco_index),
4411 match_resolved_using_arr(l_reco_index),
4412 match_score_value_arr(l_reco_index),
4413 match_reason_code_arr(l_reco_index),
4414 'CREATED',
4415 'NONE',
4416 org_id_arr(l_reco_index),
4417 g_created_by,
4418 SYSDATE,
4419 g_last_updated_by,
4420 SYSDATE,
4421 g_last_update_login,
4422 g_program_application_id,
4423 g_program_id,
4424 SYSDATE,
4425 p_request_id,
4426 automatch_id_arr(l_reco_index),
4427 priority_arr(l_reco_index));
4428 END IF;
4429 END LOOP;
4430
4431 FORALL l_reco_line_index IN 1 .. NVL(reco_id_arr.LAST, 0)
4432 INSERT INTO ar_cash_reco_lines_all (
4433 recommendation_id,
4434 line_number,
4435 customer_trx_id,
4436 payment_schedule_id,
4437 amount_applied,
4438 amount_applied_from,
4439 trans_to_receipt_rate,
4440 receipt_currency_code,
4441 receipt_date,
4442 org_id,
4443 created_by,
4444 creation_date,
4445 last_updated_by,
4446 last_update_date,
4447 last_update_login,
4448 program_application_id,
4449 program_id,
4450 program_update_date,
4451 request_id,
4452 recommendation_reason,
4453 discount_taken_earned)
4454 SELECT reco_id_arr(l_reco_line_index),
4455 reco_num_arr(l_reco_line_index),
4456 customer_trx_id_arr(l_reco_line_index),
4457 payment_schedule_id_arr(l_reco_line_index),
4458 amount_applied_arr(l_reco_line_index),
4459 amount_applied_from_arr(l_reco_line_index),
4460 trans_to_receipt_rate_arr(l_reco_line_index),
4461 receipt_currency_code_arr(l_reco_line_index),
4462 receipt_date_arr(l_reco_line_index),
4463 org_id_arr(l_reco_line_index),
4464 g_created_by,
4465 SYSDATE,
4466 g_last_updated_by,
4467 SYSDATE,
4468 g_last_update_login,
4469 g_program_application_id,
4470 g_program_id,
4471 SYSDATE,
4472 p_request_id,
4473 match_reason_code_arr(l_reco_line_index),
4474 discount_taken_earned_arr(l_reco_line_index)
4475 FROM DUAL;
4476 IF (PG_DEBUG IN ('Y', 'C')) THEN
4477 log('insert_recos()+');
4478 END IF;
4479 EXCEPTION
4480 WHEN OTHERS THEN
4481 log('Exception from arp_autoapply_api.insert_recos');
4482 log(SQLERRM);
4483 RAISE;
4484 END insert_recos;
4485
4486 FUNCTION get_cross_curr_rate(p_amount_applied IN ar_cash_remit_refs.amount_applied%TYPE
4487 , p_amount_applied_from IN ar_cash_remit_refs.amount_applied_from%TYPE
4488 , p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%TYPE
4489 , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
4490 RETURN NUMBER IS
4491 l_cross_curr_rate NUMBER;
4492 l_amount_applied ar_cash_remit_refs.amount_applied%TYPE;
4493 l_amount_applied_from ar_cash_remit_refs.amount_applied_from%TYPE;
4494 l_inv_curr_code ar_payment_schedules.invoice_currency_code%TYPE;
4495 l_rec_curr_code ar_cash_receipts.currency_code%TYPE;
4496 BEGIN
4497 l_amount_applied := NVL(p_amount_applied, 0);
4498 l_amount_applied_from := NVL(p_amount_applied_from, 0);
4499 l_inv_curr_code := p_inv_curr_code;
4500 l_rec_curr_code := p_rec_curr_code;
4501
4502 AR_CC_LOCKBOX.calc_cross_rate (
4503 p_amount_applied => l_amount_applied,
4504 p_amount_applied_from => l_amount_applied_from,
4505 p_inv_curr_code => l_inv_curr_code,
4506 p_rec_curr_code => l_rec_curr_code,
4507 p_cross_rate => l_cross_curr_rate);
4508
4509 RETURN l_cross_curr_rate;
4510 END get_cross_curr_rate;
4511
4512 PROCEDURE calc_amount_app_and_disc(
4513 p_customer_id IN AR_PAYMENT_SCHEDULES.customer_id%TYPE
4514 , p_bill_to_site_use_id IN AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE
4515 , p_invoice_currency_code IN AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE
4516 , p_ps_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE
4517 , p_term_id IN AR_PAYMENT_SCHEDULES.term_id%TYPE
4518 , p_terms_sequence_number IN AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE
4519 , p_trx_date IN AR_PAYMENT_SCHEDULES.trx_date%TYPE
4520 , p_allow_overapp_flag IN RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE
4521 , p_partial_discount_flag IN RA_TERMS.partial_discount_flag%TYPE
4522 , p_input_amount IN AR_CASH_REMIT_REFS.amount_applied%TYPE
4523 , p_amount_due_original IN AR_PAYMENT_SCHEDULES.amount_due_original%TYPE
4524 , p_amount_due_remaining IN AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE
4525 , p_discount_taken_earned IN AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE
4526 , p_discount_taken_unearned IN AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE
4527 , p_cash_receipt_id IN AR_CASH_RECEIPTS.cash_receipt_id%TYPE
4528 , x_out_amount_to_apply OUT NOCOPY NUMBER
4529 , x_out_discount_to_take OUT NOCOPY NUMBER) IS
4530 l_customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE;
4531 l_bill_to_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
4532 l_invoice_currency_code AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE;
4533 l_ps_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
4534 l_term_id AR_PAYMENT_SCHEDULES.term_id%TYPE;
4535 l_terms_sequence_number AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE;
4536 l_trx_date AR_PAYMENT_SCHEDULES.trx_date%TYPE;
4537 l_allow_overapp_flag RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE;
4538 l_partial_discount_flag RA_TERMS.partial_discount_flag%TYPE;
4539 l_input_amount AR_CASH_REMIT_REFS.amount_applied%TYPE;
4540 l_amount_due_original AR_PAYMENT_SCHEDULES.amount_due_original%TYPE;
4541 l_amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
4542 l_discount_taken_earned AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE;
4543 l_discount_taken_unearned AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE;
4544 l_cash_receipt_id AR_CASH_RECEIPTS.cash_receipt_id%TYPE;
4545
4546 ln_earned_disc_pct NUMBER;
4547 ln_best_disc_pct NUMBER;
4548 ln_out_discount_date DATE;
4549 ln_out_earned_discount NUMBER;
4550 ln_out_unearned_discount NUMBER;
4551
4552 l_allow_discount VARCHAR2(1);
4553 l_grace_days NUMBER;
4554 BEGIN
4555 IF (PG_DEBUG IN ('Y', 'C')) THEN
4556 log('arp_autoapply_api.calc_amount_app_and_disc(+)');
4557 END IF;
4558 l_customer_id := p_customer_id;
4559 l_bill_to_site_use_id := p_bill_to_site_use_id;
4560 l_invoice_currency_code := p_invoice_currency_code;
4561 l_ps_id := p_ps_id;
4562 l_term_id := p_term_id;
4563 l_terms_sequence_number := p_terms_sequence_number;
4564 l_trx_date := p_trx_date;
4565 l_allow_overapp_flag := p_allow_overapp_flag;
4566 l_partial_discount_flag := p_partial_discount_flag;
4567 l_input_amount := p_input_amount;
4568 l_amount_due_original := p_amount_due_original;
4569 l_amount_due_remaining := p_amount_due_remaining;
4570 l_discount_taken_earned := p_discount_taken_earned;
4571 l_discount_taken_unearned := p_discount_taken_unearned;
4572
4573 SELECT NVL(NVL(site.discount_terms, cust.discount_terms),'Y')
4574 INTO l_allow_discount
4575 FROM
4576 hz_customer_profiles cust
4577 , hz_customer_profiles site
4578 WHERE
4579 cust.cust_account_id = l_customer_id
4580 AND cust.site_use_id IS NULL
4581 AND site.cust_account_id (+) = cust.cust_account_id
4582 AND site.site_use_id (+) = l_bill_to_site_use_id;
4583
4584 SELECT NVL(NVL(site.discount_grace_days, cust.discount_grace_days),0)
4585 INTO l_grace_days
4586 FROM
4587 hz_customer_profiles cust
4588 , hz_customer_profiles site
4589 , hz_cust_accounts cust_acct
4590 WHERE
4591 cust_acct.cust_account_id = l_customer_id
4592 AND cust.cust_account_id = cust_acct.cust_account_id
4593 AND cust.site_use_id IS NULL
4594 AND site.cust_account_id (+) = cust_acct.cust_account_id
4595 AND site.site_use_id (+) = NVL(l_BILL_TO_SITE_USE_ID, -4444);
4596
4597 arp_calculate_discount.discounts_cover(
4598 --*** IN
4599 p_mode => 3 /* Default */
4600 , p_invoice_currency_code => l_invoice_currency_code
4601 , p_ps_id => l_ps_id
4602 , p_term_id => l_term_id
4603 , p_terms_sequence_number => l_terms_sequence_number
4604 , p_trx_date => l_trx_date
4605 , p_apply_date => trunc(sysdate)
4606 , p_grace_days => l_grace_days
4607 , p_default_amt_apply_flag => 'PMT'
4608 , p_partial_discount_flag => l_partial_discount_flag
4609 , p_calc_discount_on_lines_flag=>NULL
4610 , p_allow_overapp_flag => l_allow_overapp_flag
4611 , p_close_invoice_flag => 'N'
4612 , p_input_amount => l_input_amount
4613 , p_amount_due_original => l_amount_due_original
4614 , p_amount_due_remaining => l_amount_due_remaining
4615 , p_discount_taken_earned => l_discount_taken_earned
4616 , p_discount_taken_unearned => l_discount_taken_unearned
4617 , p_amount_line_items_original=> NULL
4618 , p_module_name => 'ARATAPPM'
4619 , p_module_version => '1.0'
4620 --*** OUT
4621 , p_earned_disc_pct => ln_earned_disc_pct
4622 , p_best_disc_pct => ln_best_disc_pct
4623 , p_out_discount_date => ln_out_discount_date
4624 , p_out_earned_discount => ln_out_earned_discount
4625 , p_out_unearned_discount => ln_out_unearned_discount
4626 , p_out_amount_to_apply => x_out_amount_to_apply
4627 , p_out_discount_to_take => x_out_discount_to_take
4628 , p_cash_receipt_id => l_cash_receipt_id
4629 , p_allow_discount => l_allow_discount
4630 );
4631 IF (PG_DEBUG IN ('Y', 'C')) THEN
4632 log('arp_autoapply_api.calc_amount_app_and_disc(-)');
4633 END IF;
4634 EXCEPTION
4635 WHEN OTHERS THEN
4636 log('Exception from arp_autoapply_api.calc_amount_app_and_disc()');
4637 log(SQLERRM);
4638 RAISE;
4639 END;
4640
4641 PROCEDURE calc_amt_applied_from(
4642 p_currency_code IN VARCHAR2,
4643 p_amount_applied IN ar_payments_interface.amount_applied1%type,
4644 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
4645 amount_applied_from OUT NOCOPY ar_payments_interface.amount_applied_from1%type
4646 ) IS
4647 --
4648 l_mau NUMBER;
4649 l_precision NUMBER(1);
4650 l_extended_precision NUMBER;
4651 --
4652
4653 BEGIN
4654 --
4655 log( 'calc_amt_applied_from() +' );
4656 log('p_amount_applied = ' || to_char(p_amount_applied));
4657 log('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
4658 log('p curr code = ' || p_currency_code);
4659
4660 fnd_currency.Get_Info(
4661 p_currency_code,
4662 l_precision,
4663 l_extended_precision,
4664 l_mau);
4665 IF (l_mau IS NOT NULL) THEN
4666 amount_applied_from :=
4667 ROUND((p_amount_applied *
4668 p_trans_to_receipt_rate) /
4669 l_mau) * l_mau;
4670 ELSE
4671 amount_applied_from :=
4672 ROUND((p_amount_applied *
4673 p_trans_to_receipt_rate),
4674 l_precision);
4675 END IF; /* l_mau is not null */
4676
4677 /* after amount_applied_from is calculated, we need to remove
4678 the decimal place since the value stored in the interim
4679 table and then transfered to the interface tables is stored
4680 with an implied decimal */
4681
4682 log('p_amount_applied_from = ' || to_char(amount_applied_from));
4683 log( 'calc_amt_applied_from() -' );
4684
4685 END calc_amt_applied_from;
4686
4687 PROCEDURE calc_amt_applied(
4688 p_invoice_currency_code IN VARCHAR2,
4689 p_amount_applied_from IN ar_payments_interface.amount_applied_from1%type,
4690 p_trans_to_receipt_rate IN ar_payments_interface.trans_to_receipt_rate1%type,
4691 amount_applied OUT NOCOPY ar_payments_interface.amount_applied1%type
4692 ) IS
4693
4694 --
4695 l_mau NUMBER;
4696 l_precision NUMBER(1);
4697 l_extended_precision NUMBER;
4698 --
4699
4700 BEGIN
4701 log( 'calc_amt_applied() +' );
4702 log('p_amount_applied_from = ' || to_char(p_amount_applied_from));
4703 log('p_trans_to_receipt_rate = ' || to_char(p_trans_to_receipt_rate));
4704 log('p inv curr code = ' || p_invoice_currency_code);
4705
4706 fnd_currency.Get_Info(
4707 p_invoice_currency_code,
4708 l_precision,
4709 l_extended_precision,
4710 l_mau);
4711 IF (l_mau IS NOT NULL) THEN
4712 amount_applied :=
4713 ROUND((p_amount_applied_from /
4714 p_trans_to_receipt_rate) /
4715 l_mau) * l_mau;
4716 ELSE
4717 amount_applied:=
4718 ROUND((p_amount_applied_from /
4719 p_trans_to_receipt_rate),
4720 l_precision);
4721 END IF; /* l_mau is not null */
4722
4723 log('p_amount_applied = ' || to_char(amount_applied));
4724 log( 'calc_amt_applied() -' );
4725
4726 END calc_amt_applied;
4727
4728
4729 /*===========================================================================+
4730 * PROCEDURE *
4731 * DELETE_INTERIM_RECORDS() *
4732 * DESCRIPTION *
4733 * Delete records from ar_cash_remit_refs_interim. *
4734 * SCOPE - LOCAL *
4735 * ARGUMENTS *
4736 * IN : None *
4737 * OUT : None *
4738 * *
4739 * RETURNS NONE *
4740 * ALGORITHM *
4741 * 1. Delete records from ar_cash_remit_refs_interim. *
4742 * NOTES - *
4743 * 1. This is called from the XML report *
4744 * *
4745 * MODIFICATION HISTORY - 09/03/2009 - Created by AGHORAKA *
4746 * *
4747 +===========================================================================*/
4748 PROCEDURE delete_interim_records IS
4749 BEGIN
4750 IF (PG_DEBUG IN ('Y', 'C')) THEN
4751 log('arp_autoapply_api.delete_interim_records()+' );
4752 END IF;
4753 DELETE FROM ar_cash_remit_refs_interim;
4754 IF (PG_DEBUG IN ('Y', 'C')) THEN
4755 log('No. of records deleted: ' || SQL%ROWCOUNT );
4756 END IF;
4757 IF (PG_DEBUG IN ('Y', 'C')) THEN
4758 log('arp_autoapply_api.delete_interim_records(-)' );
4759 END IF;
4760 EXCEPTION
4761 WHEN OTHERS THEN
4762 log('Exception from arp_autoapply_api.delete_interim_records');
4763 log(SQLERRM);
4764 RAISE;
4765 END delete_interim_records;
4766
4767 END ARP_AUTOAPPLY_API;