[Home] [Help]
PACKAGE BODY: APPS.ARP_CORRECT_CC_ERRORS
Source
1 PACKAGE BODY ARP_CORRECT_CC_ERRORS AS
2 /*$Header: ARCCCORB.pls 120.7 2009/03/23 06:13:36 dgaurab ship $*/
3
4 /* =======================================================================
5 | Global Data Types
6 * ======================================================================*/
7
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9 pg_user_id number;
10 pg_conc_login_id number;
11 pg_login_id number;
12 pg_user_name VARCHAR2(100);
13
14 --
15 -- Private procedures used by the package
16 --
17
18 PROCEDURE get_action_code(p_cc_trx_id IN NUMBER,p_cc_trx_category IN VARCHAR2,
19 p_receipt_method_id IN NUMBER,
20 p_payment_trxn_extension_id IN NUMBER,
21 p_cc_error_code IN VARCHAR2,x_cc_error_desc OUT NOCOPY VARCHAR2,
22 x_first_record_flag OUT NOCOPY VARCHAR2,
23 x_cc_action_code OUT NOCOPY VARCHAR2,x_cc_action_type OUT NOCOPY VARCHAR2,
24 x_error_notes OUT NOCOPY VARCHAR2);
25
26 PROCEDURE insert_p(p_cc_error_hist IN ar_cc_error_history%ROWTYPE);
27
28 PROCEDURE clear_invoice_pay_info(p_customer_trx_id IN NUMBER,
29 p_cc_trx_category IN VARCHAR2,
30 p_source_receipt_id IN NUMBER DEFAULT NULL,
31 p_source_receipt_number IN VARCHAR2 DEFAULT NULL,
32 p_error_notes IN VARCHAR2);
33
34 FUNCTION default_reversal_gl_date(p_cash_receipt_id IN NUMBER) RETURN DATE;
35
36 FUNCTION default_reversal_date(p_cash_receipt_id IN NUMBER) RETURN DATE;
37
38 PROCEDURE reverse_receipt(p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
39 x_reversal_comments IN VARCHAR2 DEFAULT NULL);
40
41 /*Bug 8341117, Removed the parameter 'expiration_date' as part of the changes
42 for PA - DSS Project.*/
43 PROCEDURE Raise_Collection_Event(
44 p_cc_trx_id IN NUMBER,
45 p_cc_error_code IN VARCHAR2,
46 p_cc_error_desc IN VARCHAR2,
47 p_cc_trx_category_dsp IN VARCHAR2,
48 p_cc_trx_number IN VARCHAR2,
49 p_cc_trx_currency IN VARCHAR2,
50 p_cc_trx_amount IN NUMBER,
51 p_cc_trx_date IN DATE,
52 p_customer_name IN VARCHAR2,
53 p_customer_number IN VARCHAR2,
54 p_customer_location IN VARCHAR2,
55 p_cc_number IN VARCHAR2,
56 p_payment_trxn_extension_id IN NUMBER,
57 p_approval_code IN VARCHAR2,
58 p_collector IN VARCHAR2,
59 p_payment_method_name IN VARCHAR2,
60 p_billto_contact IN VARCHAR2 DEFAULT NULL,
61 p_salesrep_name IN VARCHAR2 DEFAULT NULL,
62 p_source_receipt_id IN NUMBER DEFAULT NULL,
63 p_source_receipt_num IN VARCHAR2 DEFAULT NULL,
64 p_error_notes IN VARCHAR2);
65
66 PROCEDURE Raise_RefundReverse_Event(p_misc_cash_receipt_id IN NUMBER,
67 p_cc_error_code IN VARCHAR2,
68 p_cc_error_desc IN VARCHAR2,
69 p_cc_trx_number IN VARCHAR2,
70 p_cc_trx_currency IN VARCHAR2,
71 p_cc_trx_amount IN NUMBER,
72 p_cc_trx_date IN DATE,
73 p_customer_name IN VARCHAR2,
74 p_customer_number IN VARCHAR2,
75 p_customer_location IN VARCHAR2,
76 p_cc_number IN VARCHAR2,
77 p_payment_trxn_extension_id IN NUMBER,
78 p_approval_code IN VARCHAR2,
79 p_collector IN VARCHAR2,
80 p_payment_method_name IN VARCHAR2,
81 p_source_receipt_id IN NUMBER,
82 p_source_receipt_num IN VARCHAR2,
83 p_error_notes IN VARCHAR2
84 );
85
86 PROCEDURE AddParamEnvToList( x_list IN OUT NOCOPY WF_PARAMETER_LIST_T);
87
88 FUNCTION item_key(p_event_name IN VARCHAR2,
89 p_unique_identifier NUMBER) RETURN VARCHAR2;
90
91 FUNCTION event(p_event_name IN VARCHAR2) RETURN VARCHAR2;
92
93 PROCEDURE raise_event
94 (p_event_name IN VARCHAR2,
95 p_event_key IN VARCHAR2,
96 p_data IN CLOB DEFAULT NULL,
97 p_parameters IN wf_parameter_list_t DEFAULT NULL);
98
99 PROCEDURE attach_notes(p_customer_trx_id IN NUMBER,
100 p_text IN VARCHAR2);
101
102 FUNCTION cc_error_occurred(p_mode VARCHAR2,p_request_id NUMBER) RETURN VARCHAR2;
103
104 PROCEDURE correct_remittance_errors(p_request_id IN NUMBER);
105
106 PROCEDURE correct_creation_errors(p_request_id IN NUMBER);
107
108 /*===========================================================================+
109 | FUNCTION
110 | get_collector_name
111 |
112 | DESCRIPTION
113 | gets the collector name
114 |
115 |
116 | SCOPE - PUBLIC
117 |
118 |
119 | ARGUMENTS : IN:
120 | p_customer_id - Ideally cust_account_id
121 | p_customer_site_use_id
122 | OUT:
123 |
124 | RETURNS : l_collector_name if it finds a collector,
125 | otherwise null
126 |
127 | NOTES
128 |
129 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
130 |
131 +===========================================================================*/
132 FUNCTION get_collector_name (
133 p_customer_id IN NUMBER,
134 p_customer_site_use_id IN NUMBER)
135 RETURN VARCHAR2 IS
136 l_collector_name ar_collectors.name%TYPE;
137 l_collector_id NUMBER;
138 BEGIN
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.get_collector_name()+' );
141 END IF;
142 SELECT prof.collector_id INTO l_collector_id
143 FROM hz_customer_profiles prof
144 WHERE prof.cust_account_id = p_customer_id AND
145 prof.site_use_id = p_customer_site_use_id;
146
147 SELECT name
148 INTO l_collector_name
149 FROM ar_collectors
150 WHERE collector_id = l_collector_id;
151
152 IF PG_DEBUG in ('Y', 'C') THEN
153 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.get_collector_name()-' );
154 END IF;
155 RETURN l_collector_name;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 SELECT prof.collector_id INTO l_collector_id
159 FROM hz_customer_profiles prof
160 WHERE prof.cust_account_id = p_customer_id AND
161 prof.site_use_id is NULL;
162
163 SELECT name
164 INTO l_collector_name
165 FROM ar_collectors
166 WHERE collector_id = l_collector_id;
167 IF PG_DEBUG in ('Y', 'C') THEN
168 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.get_collector_name()-' );
169 END IF;
170 RETURN l_collector_name;
171 WHEN OTHERS THEN
172 RETURN null;
173 END get_collector_name;
174
175 /*===========================================================================+
176 | FUNCTION
177 | cc_mapping_exist
178 |
179 | DESCRIPTION
180 | This function is used in report query for autorec execution report
181 | This function will return 'Y' if cc error mapping found otherwise 'N'
182 |
183 |
184 | SCOPE - PUBLIC
185 |
186 |
187 | ARGUMENTS : IN:
188 | p_cc_error_code - Error Code
189 | p_cc_trx_category - Transaction category like 'REC'/'MISC'/'INV'
190 | p_receipt_method_id - payment method identifier
191 | OUT:
192 |
193 | RETURNS : 'Y' if mapping found otherwise 'N'
194 |
195 |
196 | NOTES
197 |
198 | MODIFICATION HISTORY - Created by Srinivasa Kini - 30-Oct-2004
199 |
200 +===========================================================================*/
201 FUNCTION cc_mapping_exist (
202 p_cc_error_code IN ar_cc_error_mappings.cc_error_code%TYPE,
203 p_cc_trx_category IN ar_cc_error_mappings.cc_trx_category%TYPE,
204 p_receipt_method_id IN ar_cc_error_mappings.receipt_method_id%TYPE)
205 RETURN VARCHAR2 IS
206 l_return VARCHAR2(1);
207 BEGIN
208 IF PG_DEBUG in ('Y', 'C') THEN
209 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.cc_mapping_exist()+' );
210 END IF;
211 SELECT 'Y'
212 INTO l_return
213 FROM dual
214 WHERE EXISTS (SELECT 1
215 FROM ar_cc_error_mappings
216 WHERE cc_error_code = p_cc_error_code
217 AND cc_trx_category = p_cc_trx_category
218 AND receipt_method_id = p_receipt_method_id);
219 IF PG_DEBUG in ('Y', 'C') THEN
220 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.cc_mapping_exist()-' );
221 END IF;
222 RETURN l_return;
223 EXCEPTION
224 WHEN OTHERS THEN
225 RETURN 'N';
226 END;
227
228 /*===========================================================================+
229 | PROCEDURE
230 | get_action_code
231 |
232 | DESCRIPTION
233 | Gets the cc_action_code from ar_cc_error_mappings if one exists
234 | otherwise passes 'Not avaialable'
235 |
236 |
237 | SCOPE - PRIVATE
238 |
239 |
240 | ARGUMENTS : IN:
241 | p_cc_trx_id - Cash_receipt_id/Customer_trx_id
242 | p_cc_trx_category - CASH/MISC/INV
243 | p_receipt_method_id -
244 | p_payment_trxn_extension_id -
245 | p_cc_error_code - Error that was occured during CC processing
246 | OUT:
247 | x_cc_error_desc - From ar_cc_error_mappings
248 | x_first_record_flag - Y Indicates this is the first correction
249 | on the given transaction with the present
250 | error code and credit card number
251 | x_cc_action_code - RET/REV REC/CLR PAY INFO/REAUT REC
252 | x_cc_action_type - A/S, 'A' for Action and 'S' for subsequent action
253 | x_error_notes - Error note, picked from ar_cc_error_mappings
254 |
255 |
256 | RETURNS : None
257 |
258 | NOTES
259 |
260 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
261 |
262 +===========================================================================*/
263 PROCEDURE get_action_code(p_cc_trx_id IN NUMBER,
264 p_cc_trx_category IN VARCHAR2,
265 p_receipt_method_id IN NUMBER,
266 p_payment_trxn_extension_id IN NUMBER,
267 p_cc_error_code IN VARCHAR2,
268 x_cc_error_desc OUT NOCOPY VARCHAR2,
269 x_first_record_flag OUT NOCOPY VARCHAR2,
270 x_cc_action_code OUT NOCOPY VARCHAR2,
271 x_cc_action_type OUT NOCOPY VARCHAR2,
272 x_error_notes OUT NOCOPY VARCHAR2) IS
273 l_no_days NUMBER;
274 l_first_cc_error_date DATE;
275 l_dummy_number NUMBER;
276 l_cc_action_code VARCHAR2(30);
277 l_subsequent_action_code VARCHAR2(30);
278 l_instr_assignment_id NUMBER;
279
280 BEGIN
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.get_action_code()+' );
283 END IF;
284
285 IF PG_DEBUG in ('Y', 'C') THEN
286 ---Print input parameters
287 arp_standard.debug( '--- Input Parameters : ---');
288 arp_standard.debug( 'p_cc_trx_id :'|| p_cc_trx_id);
289 arp_standard.debug( 'p_cc_trx_category :'|| p_cc_trx_category);
290 arp_standard.debug( 'p_payment_trxn_extension_id: ' ||
291 p_payment_trxn_extension_id);
292 arp_standard.debug( 'p_cc_error_code: ' || p_cc_error_code);
293 arp_standard.debug( 'p_receipt_method_id: ' || p_receipt_method_id);
294
295 END IF;
296
297
298 -- Check if there is action code in ar_cc_error_mappings
299 BEGIN
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_standard.debug( 'get_action_code()+' );
302 END IF;
303 SELECT cc_action_code,
304 cc_error_text,
305 no_days,
306 subsequent_action_code,
307 error_notes
308 INTO l_cc_action_code,
309 x_cc_error_desc,
310 l_no_days,
311 l_subsequent_action_code,
312 x_error_notes
313 FROM ar_cc_error_mappings
314 WHERE cc_error_code = p_cc_error_code
315 AND cc_trx_category = p_cc_trx_category
316 AND receipt_method_id = p_receipt_method_id;
317
318
319 IF PG_DEBUG in ('Y', 'C') THEN
320 arp_standard.debug( 'cc_action_code: '|| l_cc_action_code );
321 arp_standard.debug( 'no_days: '|| l_no_days );
322 arp_standard.debug( 'subsequent_action_code: '|| l_subsequent_action_code );
323
324 END IF;
325
326
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 x_cc_action_code := 'Not available';
330 x_cc_action_type := NULL;
331 x_first_record_flag := NULL;
332 x_error_notes := NULL;
333 RETURN;
334 WHEN OTHERS THEN
335 IF PG_DEBUG in ('Y', 'C') THEN
336 arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.get_action_code()' );
337 END IF;
338 raise;
339 END;
340
341
342 -- Check if subsequent action code already performed on this entity
343 BEGIN
344 arp_standard.debug( 'Checking for subsequent action code');
345
346 SELECT INSTR_ASSIGNMENT_ID INTO l_instr_assignment_id
347 FROM IBY_FNDCPT_TX_EXTENSIONS
348 WHERE TRXN_EXTENSION_ID = p_payment_trxn_extension_id;
349
350 SELECT 1
351 INTO l_dummy_number
352 FROM dual
353 WHERE NOT EXISTS ( SELECT 1
354 FROM ar_cc_error_history cc, iby_fndcpt_tx_extensions b
355 WHERE cc.cc_trx_id = p_cc_trx_id
356 AND cc.cc_trx_category = p_cc_trx_category
357 AND cc.payment_trxn_extension_id = b.trxn_extension_id
358 AND b.instr_assignment_id = l_instr_assignment_id
359 AND cc.cc_error_code = p_cc_error_code
360 AND cc.cc_action_type_flag = 'S' );
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 arp_standard.debug( 'No Data Found for subsequent action code');
364 x_cc_action_code := 'Not available';
365 x_cc_action_type := NULL;
366 x_first_record_flag := NULL;
367 x_error_notes := NULL;
368 RETURN;
369 WHEN OTHERS THEN
370 IF PG_DEBUG in ('Y', 'C') THEN
371 arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.get_action_code()' );
372 END IF;
373 raise;
374 END;
375
376 -- Check if this a first time error
377 BEGIN
378 arp_standard.debug( 'Checking if it is first time error');
379 SELECT cc.cc_error_date
380 INTO l_first_cc_error_date
381 FROM ar_cc_error_history cc, iby_fndcpt_tx_extensions b
382 WHERE cc.cc_trx_id = p_cc_trx_id
383 AND cc.cc_trx_category = p_cc_trx_category
384 AND cc.payment_trxn_extension_id = b.trxn_extension_id
385 AND b.instr_assignment_id = l_instr_assignment_id
386 AND cc.cc_error_code = p_cc_error_code
387 AND cc.first_record_flag = 'Y';
388
389 IF PG_DEBUG in ('Y', 'C') THEN
390 arp_standard.debug( 'First CC error Date; '|| l_first_cc_error_date );
391 END IF;
392
393 EXCEPTION
394 WHEN NO_DATA_FOUND THEN
395 arp_standard.debug( 'No Data Found for first time error check');
396 x_cc_action_code := l_cc_action_code;
397 x_cc_action_type := 'A';
398 x_first_record_flag := 'Y';
399 RETURN;
400 WHEN OTHERS THEN
401 IF PG_DEBUG in ('Y', 'C') THEN
402 arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.get_action_code()' );
403 END IF;
404 raise;
405 END;
406
407 -- Determine action to perform or subsequent action.
408 --Bug 4192014 change the sign from < to > for action/subsequent action logic
409 arp_standard.debug( 'Setting action for subsequent action');
410 IF trunc(NVL(l_first_cc_error_date,to_date('01/01/1951','dd/mm/yyyy')))+l_no_days
411 > trunc(sysdate)
412 THEN /* Action */
413 x_cc_action_code := l_cc_action_code;
414 x_cc_action_type := 'A';
415 x_first_record_flag := 'N';
416
417 ELSE /* Subsequent Action */
418 x_cc_action_code := NVL(l_subsequent_action_code,'Not available');
419 x_cc_action_type := 'S';
420 x_first_record_flag := 'N';
421 END IF;
422
423 IF PG_DEBUG in ('Y', 'C') THEN
424 arp_standard.debug( ' Action or Subsequent Action x_cc_action_type : ' ||
425 x_cc_action_type );
426 END IF;
427
428 IF PG_DEBUG in ('Y', 'C') THEN
429 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.get_action_code()-' );
430 END IF;
431 END get_action_code;
432
433 /*===========================================================================+
434 | PROCEDURE
435 | insert_p
436 |
437 | DESCRIPTION
438 | Inserts the record into ar_cc_error_history
439 |
440 |
441 | SCOPE - PRIVATE
442 |
443 |
444 | ARGUMENTS : IN:
445 | p_cc_error_hist - record variable containing data to be inserted
446 | OUT: NONE
447 |
448 |
449 | RETURNS : None
450 |
451 | NOTES
452 |
453 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
454 |
455 +===========================================================================*/
456 PROCEDURE insert_p(p_cc_error_hist IN ar_cc_error_history%ROWTYPE) IS
457 BEGIN
458 IF PG_DEBUG in ('Y', 'C') THEN
459 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.insert_p()+' );
460 END IF;
461 INSERT INTO ar_cc_error_history
462 (
463 request_id,
464 cc_trx_category,
465 cc_trx_id,
466 cc_error_code,
467 cc_error_date,
468 cc_vendor_error_desc,
469 cc_error_text,
470 cc_action_code,
471 cc_action_type_flag,
472 payment_trxn_extension_id,
473 first_record_flag,
474 current_record_flag,
475 last_update_date,
476 creation_date,
477 created_by,
478 last_update_login,
479 last_updated_by
480 )
481 VALUES
482 (
483 p_cc_error_hist.request_id,
484 p_cc_error_hist.cc_trx_category ,
485 p_cc_error_hist.cc_trx_id,
486 p_cc_error_hist.cc_error_code,
487 sysdate,
488 p_cc_error_hist.cc_vendor_error_desc,
489 p_cc_error_hist.cc_error_text,
490 p_cc_error_hist.cc_action_code,
491 p_cc_error_hist.cc_action_type_flag,
492 p_cc_error_hist.payment_trxn_extension_id,
493 p_cc_error_hist.first_record_flag,
494 p_cc_error_hist.current_record_flag,
495 sysdate,
496 sysdate,
497 pg_user_id,
498 NVL(pg_login_id,pg_conc_login_id),
499 pg_user_id
500 );
501 IF PG_DEBUG in ('Y', 'C') THEN
502 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.insert_p()-' );
503 END IF;
504 EXCEPTION
505 WHEN OTHERS THEN
506 arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.insert_p()' );
507 raise;
508 END insert_p;
509
510 /*===========================================================================+
511 | PROCEDURE
512 | clear_invoice_pay_info
513 |
514 | DESCRIPTION
515 | 1)Clears the payment information on a invoice
516 | 2)Attach an note to the invoice
517 | 3)Raise an business event for this action
518 |
519 |
520 | SCOPE - PRIVATE
521 |
522 |
523 | ARGUMENTS : IN:
524 | p_customer_trx_id - customer_trx_id of the invoice, for which
525 | payment information needs to be cleared
526 | p_cc_trx_category - INV/CM etc.
527 | p_source_receipt_id - This will be passed to this if this
528 | is being called because of reverse receipt action
529 | p_source_receipt_number - receipt number of above
530 | p_error_notes - note that has to be attached to the invoice
531 | OUT: NONE
532 |
533 |
534 | RETURNS : None
535 |
536 | NOTES
537 |
538 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
539 | payment uptake
540 | i) removed ap_bank_account
541 | ii) added payment_trxn_extension_id
542 |
543 | 23-MAR-07 MRAYMOND 5589984 Modified routine to handle transactions
544 | with split payment terms.
545 +===========================================================================*/
546 PROCEDURE clear_invoice_pay_info(p_customer_trx_id IN NUMBER,
547 p_cc_trx_category IN VARCHAR2,
548 p_source_receipt_id IN NUMBER DEFAULT NULL,
549 p_source_receipt_number IN VARCHAR2 DEFAULT NULL,
550 p_error_notes IN VARCHAR2) IS
551 l_cc_error_code ra_customer_trx.cc_error_code%TYPE;
552 l_cc_error_desc ra_customer_trx.cc_error_text%TYPE;
553 l_cc_trx_category_dsp VARCHAR2(240);
554 l_cc_trx_number ra_customer_trx.trx_number%TYPE;
555 l_cc_trx_currency ra_customer_trx.invoice_currency_code%TYPE;
556 l_cc_trx_amount NUMBER;
557 l_cc_trx_date DATE;
558 l_customer_name hz_parties.party_name%TYPE;
559 l_customer_number hz_cust_accounts.account_number%TYPE;
560 l_customer_location hz_cust_site_uses.location%TYPE;
561 l_cc_number IBY_FNDCPT_PAYER_ASSGN_INSTR_V.CARD_NUMBER%TYPE;
562 l_payment_trxn_extension_id ra_customer_trx.payment_trxn_extension_id%TYPE;
563 l_approval_code ra_customer_trx.approval_code%TYPE;
564 l_collector ar_collectors.name%TYPE;
565 l_payment_method_name ar_receipt_methods.name%TYPE;
566 l_days_late NUMBER;
567 l_billto_contact varchar2(150);
568 l_salesrep_name ra_salesreps.name%TYPE;
569 BEGIN
570 IF PG_DEBUG in ('Y', 'C') THEN
571 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.clear_invoice_pay_info()+' );
572 END IF;
573 SELECT NVL(trx.cc_error_code,'Unknown'),
574 NVL(trx.cc_error_text,'Unknown Error'),
575 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',p_cc_trx_category),
576 trx.trx_number,
577 trx.invoice_currency_code,
578 trx.trx_date,
579 party.party_name,
580 cust.account_number,
581 site_uses.location,
582 iby.card_number,
583 trx.payment_trxn_extension_id,
584 trx.approval_code,
585 ARP_CORRECT_CC_ERRORS.get_collector_name(trx.paying_customer_id,site_uses.site_use_id),
586 rm.name
587 INTO l_cc_error_code,
588 l_cc_error_desc,
589 l_cc_trx_category_dsp,
590 l_cc_trx_number,
591 l_cc_trx_currency,
592 l_cc_trx_date,
593 l_customer_name,
594 l_customer_number,
595 l_customer_location,
596 l_cc_number,
597 l_payment_trxn_extension_id,
598 l_approval_code,
599 l_collector,
600 l_payment_method_name
601 FROM ra_customer_trx trx,
602 ar_receipt_methods rm,
603 hz_parties party,
604 hz_cust_accounts cust,
605 hz_cust_site_uses site_uses,
606 iby_trxn_extensions_v iby,
607 iby_fndcpt_pmt_chnnls_b pc
608 WHERE trx.receipt_method_id = rm.receipt_method_id
609 AND rm.payment_channel_code = pc.payment_channel_code
610 AND pc.instrument_type = 'CREDITCARD'
611 AND trx.paying_customer_id = cust.cust_account_id (+)
612 AND cust.party_id = party.party_id (+)
613 AND trx.paying_site_use_id = site_uses.site_use_id (+)
614 AND iby.trxn_extension_id = trx.payment_trxn_extension_id
615 AND trx.customer_trx_id = p_customer_trx_id;
616
617 /* 5589984 - hack to insure that adr and due date are set. Note
618 that this probably should select amount_due_original instead. Also,
619 I kept the other where conditions to insure that this code would
620 still raise an unhandled exception if there was no eligible PS.
621 My main concern was the status=OP part.. snort! we are reversing
622 a receipt on a closed transaction! */
623 SELECT sum(amount_due_remaining), trunc(sysdate) - trunc(max(due_date))
624 INTO l_cc_trx_amount, l_days_late
625 FROM ar_payment_schedules
626 WHERE customer_trx_id = p_customer_trx_id
627 AND selected_for_receipt_batch_id IS NULL
628 AND reserved_type IS NULL
629 AND reserved_value IS NULL;
630
631 /* Splitted the query because if we have a single query, it might give rise
632 * to performace issue
633 */
634 SELECT substrb(RACO_BILL_PARTY.person_last_name,1,50) ||' ' ||
635 substrb(RACO_BILL_PARTY.person_first_name,1,50),
636 substrb(RA_SALES.NAME,1,50)
637 INTO l_billto_contact,
638 l_salesrep_name
639 FROM ra_customer_trx CT,
640 hz_cust_account_roles RACO_BILL,
641 hz_parties RACO_BILL_PARTY,
642 hz_relationships RACO_BILL_REL,
643 ra_salesreps RA_SALES
644 WHERE ct.bill_to_contact_id = raco_bill.cust_account_role_id(+)
645 and raco_bill.party_id = raco_bill_rel.party_id(+)
646 and raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
647 and raco_bill_rel.object_table_name(+) = 'HZ_PARTIES'
648 and raco_bill_rel.directional_flag(+) = 'F'
649 and raco_bill.role_type(+) = 'CONTACT'
650 and raco_bill_rel.subject_id = raco_bill_party.party_id(+)
651 and raco_bill_rel.status(+) = 'A'
652 and ct.primary_salesrep_id = ra_sales.salesrep_id (+)
653 and ct.customer_trx_id = p_customer_trx_id;
654
655 UPDATE ra_customer_trx
656 SET payment_trxn_extension_id = NULL,
657 receipt_method_id = NULL,
658 cc_error_flag = NULL,
659 cc_error_code = NULL,
660 cc_error_text = NULL,
661 last_update_date = sysdate,
662 last_updated_by = pg_user_id,
663 last_update_login = NVL(pg_login_id,pg_conc_login_id)
664 WHERE customer_trx_id = p_customer_trx_id;
665
666 /* Attach note */
667 attach_notes(p_customer_trx_id => p_customer_trx_id,
668 p_text => p_error_notes);
669
670 Raise_Collection_Event(p_cc_trx_id => p_customer_trx_id,
671 p_cc_error_code=> l_cc_error_code,
672 p_cc_error_desc => l_cc_error_desc,
673 p_cc_trx_category_dsp=>l_cc_trx_category_dsp,
674 p_cc_trx_number=>l_cc_trx_number,
675 p_cc_trx_currency=>l_cc_trx_currency,
676 p_cc_trx_amount=>l_cc_trx_amount,
677 p_cc_trx_date=>l_cc_trx_date,
678 p_customer_name=>l_customer_name,
679 p_customer_number=>l_customer_number,
680 p_customer_location=>l_customer_location,
681 p_cc_number=>l_cc_number,
682 p_payment_trxn_extension_id=>l_payment_trxn_extension_id,
683 p_approval_code=>l_approval_code,
684 p_collector=>l_collector,
685 p_payment_method_name=>l_payment_method_name,
686 p_billto_contact => l_billto_contact,
687 p_salesrep_name => l_salesrep_name,
688 p_source_receipt_id=>p_source_receipt_id,
689 p_source_receipt_num=>p_source_receipt_number,
690 p_error_notes=>p_error_notes);
691
692
693
694 IF PG_DEBUG in ('Y', 'C') THEN
695 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.clear_invoice_pay_info()-' );
696 END IF;
697 EXCEPTION
698 WHEN OTHERS THEN
699 IF PG_DEBUG in ('Y', 'C') THEN
700 arp_standard.debug( 'Exception ARP_CORRECT_CC_ERRORS.clear_invoice_pay_info()' );
701 END IF;
702 raise;
703 END clear_invoice_pay_info;
704
705 /*===========================================================================+
706 | FUNCTION
707 | default_reversal_gl_date
708 |
709 | DESCRIPTION
710 | 1)Return reversal gl date for a receipt
711 |
712 |
713 | SCOPE - PRIVATE
714 |
715 |
716 | ARGUMENTS : IN:
717 | p_cash_receipt_id
718 | OUT: NONE
719 |
720 |
721 | RETURNS : Reversal gl date
722 |
723 | NOTES
724 |
725 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
726 |
727 +===========================================================================*/
728 FUNCTION default_reversal_gl_date(p_cash_receipt_id IN NUMBER) RETURN DATE IS
729 l_sob_id NUMBER;
730 l_error_message VARCHAR2(128);
731 l_defaulting_rule_used VARCHAR2(50);
732 l_default_gl_date DATE;
733 l_gl_date DATE;
734 BEGIN
735 IF PG_DEBUG in ('Y', 'C') THEN
736 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.default_reversal_gl_date()+' );
737 END IF;
738 SELECT max(gl_date)
739 INTO l_gl_date
740 FROM ar_cash_receipt_history
741 WHERE cash_receipt_id = p_cash_receipt_id;
742
743 SELECT set_of_books_id
744 INTO l_sob_id
745 FROM ar_cash_receipts
746 WHERE cash_receipt_id = p_cash_receipt_id;
747
748 IF (arp_util.validate_and_default_gl_date(
749 l_gl_date,
750 NULL,
751 l_gl_date,
752 NULL,
753 NULL,
754 l_gl_date,
755 NULL,
756 NULL,
757 'N',
758 NULL,
759 l_sob_id,
760 222,
761 l_default_gl_date,
762 l_defaulting_rule_used,
763 l_error_message) = TRUE) THEN
764 IF trunc(l_default_gl_date) > trunc(sysdate) THEN
765 RETURN l_default_gl_date;
766 ELSE
767 RETURN trunc(sysdate);
768 END IF;
769 ELSE
770 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
771 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
772 FND_MSG_PUB.Add;
773 app_exception.raise_exception;
774 END IF;
775 IF PG_DEBUG in ('Y', 'C') THEN
776 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.default_reversal_gl_date()-' );
777 END IF;
778 EXCEPTION
779 WHEN OTHERS THEN
780 IF PG_DEBUG in ('Y', 'C') THEN
781 arp_standard.debug( 'EXCEPTION ARP_CORRECT_CC_ERRORS.default_reversal_gl_date()' );
782 END IF;
783 raise;
784 END default_reversal_gl_date;
785
786 /*===========================================================================+
787 | FUNCTION
788 | default_reversal_date
789 |
790 | DESCRIPTION
791 | 1)Return reversal date for a receipt
792 |
793 |
794 | SCOPE - PRIVATE
795 |
796 |
797 | ARGUMENTS : IN:
798 | p_cash_receipt_id
799 | OUT: NONE
800 |
801 |
802 | RETURNS : Reversal date
803 |
804 | NOTES
805 |
806 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
807 |
808 +===========================================================================*/
809 FUNCTION default_reversal_date(p_cash_receipt_id IN NUMBER) RETURN DATE IS
810 l_receipt_date DATE;
811 BEGIN
812 IF PG_DEBUG in ('Y', 'C') THEN
813 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.default_reversal_date()+' );
814 END IF;
815 SELECT receipt_date
816 INTO l_receipt_date
817 FROM ar_cash_receipts
818 WHERE cash_receipt_id = p_cash_receipt_id;
819
820 IF trunc(l_receipt_date) < trunc(sysdate) THEN
821 RETURN trunc(sysdate);
822 ELSE
823 RETURN l_receipt_date;
824 END IF;
825 IF PG_DEBUG in ('Y', 'C') THEN
826 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.default_reversal_date()+' );
827 END IF;
828 END default_reversal_date;
829
830 /*===========================================================================+
831 | PROCEDURE
832 | reverse_receipt
833 |
834 | DESCRIPTION
835 | reverses the receipt using receipt API AR_RECEIPT_API_PUB.Reverse
836 |
837 |
838 | SCOPE - PRIVATE
839 |
840 |
841 | ARGUMENTS : IN:
842 | p_cash_receipt_id
843 | x_reversal_comments - reversal comments
844 | OUT: NONE
845 |
846 |
847 | RETURNS : NONE
848 |
849 | NOTES
850 |
851 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
852 |
853 +===========================================================================*/
854 PROCEDURE reverse_receipt(p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
855 x_reversal_comments IN VARCHAR2 DEFAULT NULL) IS
856 l_reversal_gl_date DATE;
857 l_reversal_date DATE;
858 l_called_from VARCHAR2(30) DEFAULT NULL;
859 l_return_status VARCHAR2(1);
860 l_msg_count NUMBER;
861 l_msg_data VARCHAR2(2000);
862 l_msg_index NUMBER;
863 API_exception EXCEPTION;
864 BEGIN
865 IF PG_DEBUG in ('Y', 'C') THEN
866 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.reverse_receipt()+' );
867 END IF;
868 --
869 -- Populate the arguments to pass to AR_RECEIPT_API_PUB.Reverse
870 --
871 l_reversal_gl_date := default_reversal_gl_date(p_cash_receipt_id);
872 l_reversal_date := default_reversal_date(p_cash_receipt_id);
873
874 AR_RECEIPT_API_PUB.Reverse(
875 p_api_version => 1.0,
876 p_init_msg_list => FND_API.G_TRUE,
877 x_return_status => l_return_status,
878 x_msg_count => l_msg_count,
879 x_msg_data => l_msg_data,
880 p_cash_receipt_id => p_cash_receipt_id,
881 p_reversal_reason_code =>'CC ERROR CORRECTION ACTION',
882 p_reversal_comments => x_reversal_comments,
883 p_reversal_category_code =>'REV',
884 p_reversal_gl_date => l_reversal_gl_date,
885 p_reversal_date => l_reversal_date,
886 p_called_from => 'CC ERROR HANDLING');
887
888 /*------------------------------------------------+
889 | Write API output to the concurrent program log |
890 +------------------------------------------------*/
891 IF PG_DEBUG in ('Y', 'C') THEN
892 arp_util.debug('reverse_action: ' || 'API error count '||to_char(NVL(l_msg_count,0)));
893 END IF;
894
895 IF NVL(l_msg_count,0) > 0 Then
896
897 IF l_msg_count = 1 Then
898
899 /*------------------------------------------------+
900 | There is one message returned by the API, so it|
901 | has been sent out NOCOPY in the parameter x_msg_data |
902 +------------------------------------------------*/
903 IF PG_DEBUG in ('Y', 'C') THEN
904 arp_util.debug('reverse_action: ' || l_msg_data);
905 END IF;
906
907 ELSIF l_msg_count > 1 Then
908
909 /*-------------------------------------------------------+
910 | There are more than one messages returned by the API, |
911 | so call them in a loop and print the messages |
912 +-------------------------------------------------------*/
913
914 FOR l_count IN 1..l_msg_count LOOP
915
916 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
917 FND_API.G_FALSE);
918 IF PG_DEBUG in ('Y', 'C') THEN
919 arp_util.debug('reverse_action: ' || to_char(l_count)||' : '||l_msg_data);
920 END IF;
921
922 END LOOP;
923
924 END IF; -- l_msg_count
925
926 END IF; -- NVL(l_msg_count,0)
927
928 /*-----------------------------------------------------+
929 | If API return status is not SUCCESS raise exception |
930 +-----------------------------------------------------*/
931 IF l_return_status = FND_API.G_RET_STS_SUCCESS Then
932
933 /*-----------------------------------------------------+
934 | Success do nothing, else branch introduced to make |
935 | sure that NULL case will also raise exception |
936 +-----------------------------------------------------*/
937 NULL;
938
939 ELSE
940 /*---------------------------+
941 | Error, raise an exception |
942 +---------------------------*/
943 RAISE API_exception;
944
945 END IF; -- l_return_status
946
947 IF PG_DEBUG in ('Y', 'C') THEN
948 arp_standard.debug( 'ARP_CORRECT_CC_ERRORS.reverse_receipt()-' );
949 END IF;
950 EXCEPTION
951 WHEN API_exception THEN
952 IF PG_DEBUG in ('Y', 'C') THEN
953 arp_util.debug('ARP_CORRECT_CC_ERRORS.reverse_action: ' || 'API EXCEPTION: ' ||
954 'ARP_CORRECT_CC_ERRORS.reverse_receipt'
955 ||SQLERRM);
956 END IF;
957 RAISE;
958 WHEN OTHERS THEN
959 IF PG_DEBUG in ('Y', 'C') THEN
960 arp_util.debug('EXCEPTION: ARP_CORRECT_CC_ERRORS.reverse_receipt '
961 ||SQLERRM);
962 END IF;
963 RAISE;
964 END reverse_receipt;
965
966 /*===========================================================================+
967 | PROCEDURE
968 | Raise_RefundReverse_Event
969 |
970 | DESCRIPTION
971 | raises event 'oracle.apps.ar.ccerrorhandling.RefunReverse'
972 | which to be caught by payables
973 |
974 |
975 | SCOPE - PRIVATE
976 |
977 |
978 | ARGUMENTS : IN:
979 | p_misc_cash_receipt_id
980 | p_cc_error_code
981 | p_cc_error_desc
982 | p_cc_trx_number
983 | p_cc_trx_currency
984 | p_cc_trx_amount
985 | p_cc_trx_date
986 | p_customer_name
987 | p_customer_number
988 | p_customer_location
989 | p_cc_number
990 | p_payment_trxn_extension_id
991 | p_approval_code
992 | p_collector
993 | p_payment_method_name
994 | p_source_receipt_id
995 | p_source_receipt_num
996 | p_error_notes
997 | OUT: NONE
998 |
999 |
1000 | RETURNS : NONE
1001 |
1002 | NOTES
1003 |
1004 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1005 |
1006 +===========================================================================*/
1007 PROCEDURE Raise_RefundReverse_Event(p_misc_cash_receipt_id IN NUMBER,
1008 p_cc_error_code IN VARCHAR2,
1009 p_cc_error_desc IN VARCHAR2,
1010 p_cc_trx_number IN VARCHAR2,
1011 p_cc_trx_currency IN VARCHAR2,
1012 p_cc_trx_amount IN NUMBER,
1013 p_cc_trx_date IN DATE,
1014 p_customer_name IN VARCHAR2,
1015 p_customer_number IN VARCHAR2,
1016 p_customer_location IN VARCHAR2,
1017 p_cc_number IN VARCHAR2,
1018 p_payment_trxn_extension_id IN NUMBER,
1019 p_approval_code IN VARCHAR2,
1020 p_collector IN VARCHAR2,
1021 p_payment_method_name IN VARCHAR2,
1022 p_source_receipt_id IN NUMBER,
1023 p_source_receipt_num IN VARCHAR2,
1024 p_error_notes IN VARCHAR2
1025 ) IS
1026 l_list WF_PARAMETER_LIST_T;
1027 l_param WF_PARAMETER_T;
1028 l_key VARCHAR2(240);
1029 l_event_name VARCHAR2(150);
1030 BEGIN
1031 IF PG_DEBUG in ('Y', 'C') THEN
1032 arp_util.debug('ARP_CORRECT_CC_ERRORS.Raise_RefundReverse_Event (+)');
1033 END IF;
1034 -- Assign the business event
1035 l_event_name := 'oracle.apps.ar.ccerrorhandling.RefunReverse';
1036 --Get the item key
1037 l_key := item_key( l_event_name ,
1038 p_misc_cash_receipt_id);
1039 -- initialization of object variables
1040 l_list := WF_PARAMETER_LIST_T();
1041
1042 -- Add Context values to the list
1043 AddParamEnvToList(l_list);
1044
1045 -- add more parameters to the parameters list
1046 wf_event.AddParameterToList(p_name => 'REFUND MISC CASH RECEIPT ID',
1047 p_value => p_misc_cash_receipt_id,
1048 p_parameterlist => l_list);
1049 wf_event.AddParameterToList(p_name => 'CREDIT CARD ERROR CODE',
1050 p_value => p_cc_error_code,
1051 p_parameterlist => l_list);
1052 wf_event.AddParameterToList(p_name => 'CREDIT CARD ERROR DESCRIPTION',
1053 p_value => p_cc_error_desc,
1054 p_parameterlist => l_list);
1055 wf_event.AddParameterToList(p_name => 'REFUD MISC RECEIPT NUMBER',
1056 p_value => p_cc_trx_number,
1057 p_parameterlist => l_list);
1058 wf_event.AddParameterToList(p_name => 'CURRENCY CODE',
1059 p_value => p_cc_trx_currency,
1060 p_parameterlist => l_list);
1061 wf_event.AddParameterToList(p_name => 'AMOUNT',
1062 p_value => p_cc_trx_amount,
1063 p_parameterlist => l_list);
1064 wf_event.AddParameterToList(p_name => 'REFUND DATE',
1065 p_value => p_cc_trx_date,
1066 p_parameterlist => l_list);
1067 wf_event.AddParameterToList(p_name => 'CUSTOMER NAME',
1068 p_value => p_customer_name,
1069 p_parameterlist => l_list);
1070 wf_event.AddParameterToList(p_name => 'CUSTOMER NUMBER',
1071 p_value => p_customer_number,
1072 p_parameterlist => l_list);
1073 wf_event.AddParameterToList(p_name => 'CUSTOMER LOCATION',
1074 p_value => p_customer_location,
1075 p_parameterlist => l_list);
1076 wf_event.AddParameterToList(p_name => 'CREDIT CARD NUMBER',
1077 p_value => p_cc_number,
1078 p_parameterlist => l_list);
1079 wf_event.AddParameterToList(p_name => 'APPROVAL CODE',
1080 p_value => p_approval_code,
1081 p_parameterlist => l_list);
1082 wf_event.AddParameterToList(p_name => 'PAYMENT SERVER ID',
1083 p_value => p_payment_trxn_extension_id,
1084 p_parameterlist => l_list);
1085 wf_event.AddParameterToList(p_name => 'COLLECTOR',
1086 p_value => p_collector,
1087 p_parameterlist => l_list);
1088 wf_event.AddParameterToList(p_name => 'PAYMENT METHOD NAME',
1089 p_value => p_payment_method_name,
1090 p_parameterlist => l_list);
1091 wf_event.AddParameterToList(p_name => 'SOURCE CASH RECEIPT ID',
1092 p_value => p_source_receipt_id,
1093 p_parameterlist => l_list);
1094 wf_event.AddParameterToList(p_name => 'SOURCE RECEIPT NUMBER',
1095 p_value => p_source_receipt_num,
1096 p_parameterlist => l_list);
1097 wf_event.AddParameterToList(p_name => 'CREDIT CARD CORRECTION NOTES',
1098 p_value => p_error_notes,
1099 p_parameterlist => l_list);
1100 -- Raise Event
1101 raise_event(
1102 p_event_name => l_event_name,
1103 p_event_key => l_key,
1104 p_parameters => l_list );
1105 l_list.DELETE;
1106 IF PG_DEBUG in ('Y', 'C') THEN
1107 arp_util.debug('ARP_CORRECT_CC_ERRORS.Raise_RefundReverse_Event (-)');
1108 END IF;
1109 EXCEPTION
1110 WHEN others THEN
1111 IF PG_DEBUG in ('Y', 'C') THEN
1112 arp_util.debug('ERR RAISING EVENT in ARP_CORRECT_CC_ERRORS.Raise_RefundReverse_Event: '||l_event_name);
1113 END IF;
1114 raise;
1115 END Raise_RefundReverse_Event;
1116
1117 /*===========================================================================+
1118 | PROCEDURE
1119 | Raise_RefundReverse_Event
1120 |
1121 | DESCRIPTION
1122 | raises event 'oracle.apps.ar.ccerrorhandling.ClrInvPayInfoORReverseReceipt'
1123 | which to be caught by collections
1124 | This event typically followed by reversal of a receipt or clearing
1125 | of payment information triggered by cc corrective action
1126 |
1127 |
1128 | SCOPE - PRIVATE
1129 |
1130 |
1131 | ARGUMENTS : IN:
1132 | p_cc_trx_id
1133 | p_cc_error_code
1134 | p_cc_error_desc
1135 | p_cc_trx_category_dsp
1136 | p_cc_trx_number
1137 | p_cc_trx_currency
1138 | p_cc_trx_amount
1139 | p_cc_trx_date
1140 | p_customer_name
1141 | p_customer_number
1142 | p_customer_location
1143 | p_cc_number
1144 | p_approval_code
1145 | p_collector
1146 | p_payment_method_name
1147 | p_billto_contact
1148 | p_salesrep_name
1149 | p_source_receipt_id - cash_receipt_id of the receipt which triggered the
1150 | clearance of payment information from the invoice
1151 | p_source_receipt_num - receipt no. of above
1152 | p_source_receipt_id
1153 | p_source_receipt_num
1154 | p_error_notes
1155 | OUT: NONE
1156 |
1157 |
1158 | RETURNS : NONE
1159 |
1160 | NOTES
1161 |
1162 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1163 |
1164 +===========================================================================*/
1165 PROCEDURE Raise_Collection_Event(
1166 p_cc_trx_id IN NUMBER,
1167 p_cc_error_code IN VARCHAR2,
1168 p_cc_error_desc IN VARCHAR2,
1169 p_cc_trx_category_dsp IN VARCHAR2,
1170 p_cc_trx_number IN VARCHAR2,
1171 p_cc_trx_currency IN VARCHAR2,
1172 p_cc_trx_amount IN NUMBER,
1173 p_cc_trx_date IN DATE,
1174 p_customer_name IN VARCHAR2,
1175 p_customer_number IN VARCHAR2,
1176 p_customer_location IN VARCHAR2,
1177 p_cc_number IN VARCHAR2,
1178 p_payment_trxn_extension_id IN NUMBER,
1179 p_approval_code IN VARCHAR2,
1180 p_collector IN VARCHAR2,
1181 p_payment_method_name IN VARCHAR2,
1182 p_billto_contact IN VARCHAR2 DEFAULT NULL,
1183 p_salesrep_name IN VARCHAR2 DEFAULT NULL,
1184 p_source_receipt_id IN NUMBER DEFAULT NULL,
1185 p_source_receipt_num IN VARCHAR2 DEFAULT NULL,
1186 p_error_notes IN VARCHAR2) IS
1187 l_list WF_PARAMETER_LIST_T;
1188 l_param WF_PARAMETER_T;
1189 l_key VARCHAR2(240);
1190 l_event_name VARCHAR2(150);
1191 BEGIN
1192 IF PG_DEBUG in ('Y', 'C') THEN
1193 arp_util.debug('ARP_CORRECT_CC_ERRORS.Raise_Collection_Event (+)');
1194 END IF;
1195 --Assign the business event name
1196 l_event_name := 'oracle.apps.ar.ccerrorhandling.ClrInvPayInfoORReverseReceipt';
1197 --Get the item key
1198 l_key := item_key( l_event_name ,
1199 p_cc_trx_id);
1200 -- initialization of object variables
1201 l_list := WF_PARAMETER_LIST_T();
1202
1203 -- Add Context values to the list
1204 AddParamEnvToList(l_list);
1205
1206 -- add more parameters to the parameters list
1207 wf_event.AddParameterToList(p_name => 'CREDIT CARD TRANSACTION ID',
1208 p_value => p_cc_trx_id,
1209 p_parameterlist => l_list);
1210 wf_event.AddParameterToList(p_name => 'CREDIT CARD ERROR CODE',
1211 p_value => p_cc_error_code,
1212 p_parameterlist => l_list);
1213 wf_event.AddParameterToList(p_name => 'CREDIT CARD ERROR DESCRIPTION',
1214 p_value => p_cc_error_desc,
1215 p_parameterlist => l_list);
1216 wf_event.AddParameterToList(p_name => 'TRANSACTION CATEGORY',
1217 p_value => p_cc_trx_category_dsp,
1218 p_parameterlist => l_list);
1219 wf_event.AddParameterToList(p_name => 'REFUD MISC RECEIPT NUMBER',
1220 p_value => p_cc_trx_number,
1221 p_parameterlist => l_list);
1222 wf_event.AddParameterToList(p_name => 'CURRENCY CODE',
1223 p_value => p_cc_trx_currency,
1224 p_parameterlist => l_list);
1225 wf_event.AddParameterToList(p_name => 'AMOUNT',
1226 p_value => p_cc_trx_amount,
1227 p_parameterlist => l_list);
1228 wf_event.AddParameterToList(p_name => 'REFUND DATE',
1229 p_value => p_cc_trx_date,
1230 p_parameterlist => l_list);
1231 wf_event.AddParameterToList(p_name => 'CUSTOMER NAME',
1232 p_value => p_customer_name,
1233 p_parameterlist => l_list);
1234 wf_event.AddParameterToList(p_name => 'CUSTOMER NUMBER',
1235 p_value => p_customer_number,
1236 p_parameterlist => l_list);
1237 wf_event.AddParameterToList(p_name => 'CUSTOMER LOCATION',
1238 p_value => p_customer_location,
1239 p_parameterlist => l_list);
1240 wf_event.AddParameterToList(p_name => 'CREDIT CARD NUMBER',
1241 p_value => p_cc_number,
1242 p_parameterlist => l_list);
1243 wf_event.AddParameterToList(p_name => 'APPROVAL CODE',
1244 p_value => p_approval_code,
1245 p_parameterlist => l_list);
1246 wf_event.AddParameterToList(p_name => 'PAYMENT SERVER ID',
1247 p_value => p_payment_trxn_extension_id,
1248 p_parameterlist => l_list);
1249 wf_event.AddParameterToList(p_name => 'COLLECTOR',
1250 p_value => p_collector,
1251 p_parameterlist => l_list);
1252 wf_event.AddParameterToList(p_name => 'PAYMENT METHOD NAME',
1253 p_value => p_payment_method_name,
1254 p_parameterlist => l_list);
1255 wf_event.AddParameterToList(p_name => 'CREDIT CARD CORRECTION NOTES',
1256 p_value => p_error_notes,
1257 p_parameterlist => l_list);
1258 IF p_source_receipt_id IS NOT NULL THEN
1259 wf_event.AddParameterToList(p_name => 'SOURCE CASH RECEIPT ID',
1260 p_value => p_source_receipt_id,
1261 p_parameterlist => l_list);
1262 END IF;
1263 IF p_source_receipt_num IS NOT NULL THEN
1264 wf_event.AddParameterToList(p_name => 'SOURCE RECEIPT NUMBER',
1265 p_value => p_source_receipt_num,
1266 p_parameterlist => l_list);
1267 END IF;
1268 IF p_billto_contact IS NOT NULL THEN
1269 wf_event.AddParameterToList(p_name => 'BILL TO CONTACT',
1270 p_value => p_billto_contact,
1271 p_parameterlist => l_list);
1272 END IF;
1273 IF p_salesrep_name IS NOT NULL THEN
1274 wf_event.AddParameterToList(p_name => 'SALES REP NAME',
1275 p_value => p_salesrep_name,
1276 p_parameterlist => l_list);
1277 END IF;
1278
1279
1280 -- Raise Event
1281 raise_event(
1282 p_event_name => l_event_name,
1283 p_event_key => l_key,
1284 p_parameters => l_list );
1285 l_list.DELETE;
1286 IF PG_DEBUG in ('Y', 'C') THEN
1287 arp_util.debug('ARP_CORRECT_CC_ERRORS.Raise_Collection_Event (-)');
1288 END IF;
1289 EXCEPTION
1290 WHEN others THEN
1291 IF PG_DEBUG in ('Y', 'C') THEN
1292 arp_util.debug('ERR RAISING EVENT from ARP_CORRECT_CC_ERRORS.Raise_Collection_Event: '||l_event_name);
1293 raise;
1294 END IF;
1295 END Raise_Collection_Event;
1296
1297 /*===========================================================================+
1298 | PROCEDURE
1299 | AddParamEnvToList
1300 |
1301 | DESCRIPTION
1302 | Adds user name and org name to business event parameter list
1303 |
1304 |
1305 | SCOPE - PRIVATE
1306 |
1307 |
1308 | ARGUMENTS : IN/OUT:
1309 | x_list - Business event parameter list
1310 |
1311 |
1312 | RETURNS : NONE
1313 |
1314 | NOTES
1315 |
1316 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1317 |
1318 +===========================================================================*/
1319 PROCEDURE AddParamEnvToList( x_list IN OUT NOCOPY WF_PARAMETER_LIST_T) IS
1320 l_param WF_PARAMETER_T;
1321 l_rang NUMBER;
1322 l_org_name VARCHAR2(240);
1323 BEGIN
1324 IF PG_DEBUG in ('Y', 'C') THEN
1325 arp_util.debug('ARP_CORRECT_CC_ERRORS.AddParamEnvToList ()+');
1326 END IF;
1327 l_rang := 0;
1328
1329 /* Get the org_name to pass while raising the business event */
1330 BEGIN
1331 SELECT name INTO l_org_name
1332 FROM hr_all_organization_units
1333 WHERE organization_id = fnd_profile.value( 'ORG_ID');
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 l_org_name := NULL;
1337 END;
1338
1339 l_param := WF_PARAMETER_T( NULL, NULL );
1340 -- fill the parameters list
1341 x_list.extend;
1342 l_param.SetName( 'USER NAME' );
1343 l_param.SetValue( pg_user_name);
1344 l_rang := l_rang + 1;
1345 x_list(l_rang) := l_param;
1346
1347 l_param := WF_PARAMETER_T( NULL, NULL );
1348 -- fill the parameters list
1349 x_list.extend;
1350 l_param.SetName( 'ORG NAME' );
1351 l_param.SetValue( l_org_name );
1352 l_rang := l_rang + 1;
1353 x_list(l_rang) := l_param;
1354 IF PG_DEBUG in ('Y', 'C') THEN
1355 arp_util.debug('ARP_CORRECT_CC_ERRORS.AddParamEnvToList ()-');
1356 END IF;
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 IF PG_DEBUG in ('Y', 'C') THEN
1360 arp_util.debug('EXCEPTION ARP_CORRECT_CC_ERRORS.AddParamEnvToList ()');
1361 END IF;
1362 raise;
1363 END AddParamEnvToList;
1364
1365 FUNCTION item_key(p_event_name IN VARCHAR2,
1366 p_unique_identifier NUMBER) RETURN VARCHAR2
1367 IS
1368 RetKey VARCHAR2(240);
1369 BEGIN
1370 RetKey := p_event_name||'_'||to_char(p_unique_identifier)||'_'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');
1371 Return RetKey;
1372 END item_key;
1373
1374 /*===========================================================================+
1375 | FUNCTION
1376 | event
1377 |
1378 | DESCRIPTION
1379 | Checks if the event exist
1380 |
1381 |
1382 | SCOPE - PRIVATE
1383 |
1384 |
1385 | ARGUMENTS : IN:
1386 | p_event_name - Business event name
1387 | OUT: NONE
1388 |
1389 | RETURNS : NONE
1390 |
1391 | NOTES
1392 |
1393 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1394 |
1395 +===========================================================================*/
1396 FUNCTION event(p_event_name IN VARCHAR2) RETURN VARCHAR2
1397 -----------------------------------------------
1398 -- Return event name if the entered event exist
1399 -- Otherwise return NOTFOUND
1400 -----------------------------------------------
1401 IS
1402 RetEvent VARCHAR2(240);
1403 CURSOR get_event IS
1404 SELECT name
1405 FROM wf_events
1406 WHERE name = p_event_name;
1407 BEGIN
1408 OPEN get_event;
1409
1410 FETCH get_event INTO RetEvent;
1411 IF get_event%NOTFOUND THEN
1412 RetEvent := 'NOTFOUND';
1413 END IF;
1414 CLOSE get_event;
1415
1416 RETURN RetEvent;
1417 END event;
1418
1419 PROCEDURE raise_event
1420 (p_event_name IN VARCHAR2,
1421 p_event_key IN VARCHAR2,
1422 p_data IN CLOB DEFAULT NULL,
1423 p_parameters IN wf_parameter_list_t DEFAULT NULL)
1424 IS
1425 l_item_key VARCHAR2(240);
1426 l_event VARCHAR2(240);
1427 EventNotFound EXCEPTION;
1428 EventNotARCC EXCEPTION;
1429 BEGIN
1430 IF PG_DEBUG in ('Y', 'C') THEN
1431 arp_util.debug('ARP_CORRECT_CC_ERRORS.raise_event ()+');
1432 END IF;
1433
1434 SAVEPOINT ar_cccorrection_raise_event;
1435
1436 l_event := event(p_event_name);
1437
1438 IF l_event = 'NOTFOUND' THEN
1439 RAISE EventNotFound;
1440 END IF;
1441
1442 IF SUBSTRB(l_event,1,31) <> 'oracle.apps.ar.ccerrorhandling.' THEN
1443 RAISE EventNotARCC;
1444 END IF;
1445
1446 Wf_Event.Raise
1447 ( p_event_name => l_event,
1448 p_event_key => p_event_key,
1449 p_parameters => p_parameters,
1450 p_event_data => p_data);
1451
1452 IF PG_DEBUG in ('Y', 'C') THEN
1453 arp_util.debug('ARP_CORRECT_CC_ERRORS.raise_event ()-');
1454 END IF;
1455
1456 EXCEPTION
1457 WHEN EventNotFound THEN
1458
1459 FND_MESSAGE.SET_NAME( 'AR', 'AR_EVENTNOTFOUND');
1460 FND_MESSAGE.SET_TOKEN( 'EVENT' ,p_event_name );
1461 FND_MSG_PUB.Add;
1462 app_exception.raise_exception;
1463
1464 WHEN EventNotARCC THEN
1465 FND_MESSAGE.SET_NAME( 'AR', 'AR_EVENTNOTAR');
1466 FND_MESSAGE.SET_TOKEN( 'EVENT' ,p_event_name );
1467 FND_MSG_PUB.Add;
1468 app_exception.raise_exception;
1469
1470 WHEN NO_DATA_FOUND THEN
1471 ROLLBACK TO ar_cccorrection_raise_event;
1472 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
1473 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1474 FND_MSG_PUB.Add;
1475 app_exception.raise_exception;
1476
1477 WHEN OTHERS THEN
1478 ROLLBACK TO ar_cccorrection_raise_event;
1479 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
1480 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
1481 FND_MSG_PUB.Add;
1482 app_exception.raise_exception;
1483 END raise_event;
1484
1485 /*===========================================================================+
1486 | PROCEDURE
1487 | attach_notes
1488 |
1489 | DESCRIPTION
1490 | Attaches the note to the invoice
1491 |
1492 |
1493 | SCOPE - PRIVATE
1494 |
1495 |
1496 | ARGUMENTS : IN:
1497 | p_customer_trx_id - customer_trx_id of the invoice for which note
1498 | to be attached
1499 | p_text - note text
1500 | OUT: NONE
1501 |
1502 | RETURNS : NONE
1503 |
1504 | NOTES
1505 |
1506 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1507 |
1508 +===========================================================================*/
1509 PROCEDURE attach_notes(p_customer_trx_id IN NUMBER,
1510 p_text IN VARCHAR2) IS
1511 l_note_id ar_notes.note_id%type;
1512 BEGIN
1513 IF PG_DEBUG in ('Y', 'C') THEN
1514 arp_util.debug('ARP_CORRECT_CC_ERRORS.attach_notes()+');
1515 END IF;
1516
1517 INSERT INTO ar_notes
1518 (
1519 note_id,
1520 note_type,
1521 text,
1522 customer_trx_id,
1523 customer_call_id,
1524 customer_call_topic_id,
1525 call_action_id,
1526 last_updated_by,
1527 last_update_date,
1528 last_update_login,
1529 created_by,
1530 creation_date
1531 )
1532 VALUES
1533 (
1534 ar_notes_s.nextval,
1535 'MAINTAIN',
1536 p_text,
1537 p_customer_trx_id,
1538 NULL,
1539 NULL,
1540 NULL,
1541 pg_user_id,
1542 sysdate,
1543 NVL(pg_conc_login_id, pg_login_id),
1544 pg_user_id,
1545 sysdate
1546 );
1547 IF PG_DEBUG in ('Y', 'C') THEN
1548 arp_util.debug('ARP_CORRECT_CC_ERRORS.attach_notes()-');
1549 END IF;
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 IF PG_DEBUG in ('Y', 'C') THEN
1553 arp_util.debug('EXCEPTION: ARP_CORRECT_CC_ERRORS.attach_notes()');
1554 END IF;
1555 raise;
1556 END attach_notes;
1557
1558 /*===========================================================================+
1559 | PROCEDURE
1560 | lock_table_nowait
1561 |
1562 | DESCRIPTION
1563 | Locks the table, this would be mainly used by OA frame work UI
1564 |
1565 |
1566 | SCOPE - PUBLIC
1567 |
1568 |
1569 | ARGUMENTS : IN:
1570 | p_key - primary key of the record to be locked
1571 | p_object_version_number - Applicable for receipts only
1572 | p_table_name - RA_CUSTOMER_TRX/AR_CASH_RECEIPTS/CASH/MISC
1573 | p_trx_number - receipt no./trx no.(USed to show in messages)
1574 | OUT:
1575 |
1576 | RETURNS : NONE
1577 |
1578 | NOTES
1579 |
1580 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1581 |
1582 +===========================================================================*/
1583 PROCEDURE lock_table_nowait(p_key IN NUMBER,
1584 p_object_version_number IN NUMBER DEFAULT NULL,
1585 p_table_name IN VARCHAR2,
1586 p_trx_number IN VARCHAR2) IS
1587 l_dummy_number NUMBER;
1588 BEGIN
1589 IF PG_DEBUG in ('Y', 'C') THEN
1590 arp_util.debug('ARP_CORRECT_CC_ERRORS.lock_table_nowait()+');
1591 END IF;
1592 IF p_table_name in ('AR_CASH_RECEIPTS','CASH','MISC') THEN
1593 arp_cash_receipts_pkg.nowaitlock_version_p(p_key,p_object_version_number);
1594 ELSE
1595 -- Here we need to consider calling some procedure
1596 -- which does locking
1597 ARP_CT_PKG.lock_p(p_key);
1598 END IF;
1599 IF PG_DEBUG in ('Y', 'C') THEN
1600 arp_util.debug('ARP_CORRECT_CC_ERRORS.lock_table_nowait()-');
1601 END IF;
1602 EXCEPTION
1603 WHEN NO_DATA_FOUND THEN
1604 IF p_table_name in ('AR_CASH_RECEIPTS','CASH','MISC') THEN
1605 FND_MESSAGE.SET_NAME('AR','AR_RECEIPT_RECORD_CHANGED');
1606 FND_MESSAGE.SET_TOKEN('PARAMETER',p_trx_number);
1607 FND_MSG_PUB.Add;
1608 ELSE
1609 FND_MESSAGE.SET_NAME('AR','AR_TRANSACTION_RECORD_CHANGED');
1610 FND_MESSAGE.SET_TOKEN('PARAMETER',p_trx_number);
1611 FND_MSG_PUB.Add;
1612 END IF;
1613 IF PG_DEBUG in ('Y', 'C') THEN
1614 arp_util.debug('EXCEPTION: ARP_CORRECT_CC_ERRORS.lock_table_nowait()');
1615 END IF;
1616 app_exception.raise_exception;
1617 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1618 IF p_table_name in ('AR_CASH_RECEIPTS','CASH','MISC') THEN
1619 FND_MESSAGE.SET_NAME('AR','AR_RECEIPT_RECORD_LOCKED');
1620 FND_MESSAGE.SET_TOKEN('PARAMETER',p_trx_number);
1621 FND_MSG_PUB.Add;
1622 ELSE
1623 FND_MESSAGE.SET_NAME('AR','AR_TRANSACTION_RECORD_LOCKED');
1624 FND_MESSAGE.SET_TOKEN('PARAMETER',p_trx_number);
1625 FND_MSG_PUB.Add;
1626 END IF;
1627 IF PG_DEBUG in ('Y', 'C') THEN
1628 arp_util.debug('EXCEPTION: ARP_CORRECT_CC_ERRORS.lock_table_nowait()');
1629 END IF;
1630 app_exception.raise_exception;
1631 WHEN OTHERS THEN
1632 raise;
1633 END lock_table_nowait;
1634
1635 /*===========================================================================+
1636 | FUNCTION
1637 | cc_error_occurred
1638 |
1639 | DESCRIPTION
1640 | Checks if CC error has been occured in a autoreceipt/remittance batch
1641 |
1642 |
1643 | SCOPE - PRIVATE
1644 |
1645 |
1646 | ARGUMENTS : IN:
1647 | p_mode : CREATION/REMITTANCE
1648 | p_request_id : Request id of autoreceipt/remittance batch
1649 | OUT: NONE
1650 |
1651 | RETURNS : Y if atleast one transaction has been CC errored
1652 | and has error code attached with it. 'N' otherwise
1653 |
1654 | NOTES
1655 |
1656 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1657 |
1658 +===========================================================================*/
1659 FUNCTION cc_error_occurred(p_mode VARCHAR2,p_request_id NUMBER) RETURN VARCHAR2 IS
1660 l_return_status VARCHAR2(1);
1661 BEGIN
1662 IF PG_DEBUG in ('Y', 'C') THEN
1663 arp_util.debug('ARP_CORRECT_CC_ERRORS.cc_error_occurred()+');
1664 END IF;
1665 l_return_status := 'Y';
1666 IF p_mode = 'REMITTANCE' THEN
1667 BEGIN
1668 SELECT 'Y'
1669 INTO l_return_status
1670 FROM dual
1671 WHERE EXISTS (SELECT 1
1672 FROM ar_cash_receipts
1673 WHERE request_id IN ( select request_id
1674 from fnd_concurrent_requests
1675 where request_id = p_request_id
1676 or parent_request_id = p_request_id )
1677 AND cc_error_flag = 'Y');
1678 EXCEPTION
1679 WHEN NO_DATA_FOUND THEN
1680 l_return_status := 'N';
1681 WHEN OTHERS THEN
1682 IF PG_DEBUG in ('Y', 'C') THEN
1683 arp_util.debug('Exception : ARP_CORRECT_CC_ERRORS.cc_error_occurred');
1684 END IF;
1685 raise;
1686 END;
1687 ELSE
1688 BEGIN
1689 SELECT 'Y'
1690 INTO l_return_status
1691 FROM dual
1692 WHERE EXISTS (SELECT 1
1693 FROM ra_customer_trx
1694 WHERE request_id IN ( select request_id
1695 from fnd_concurrent_requests
1696 where request_id = p_request_id
1697 or parent_request_id = p_request_id )
1698 AND cc_error_flag = 'Y');
1699 EXCEPTION
1700 WHEN NO_DATA_FOUND THEN
1701 l_return_status := 'N';
1702 WHEN OTHERS THEN
1703 IF PG_DEBUG in ('Y', 'C') THEN
1704 arp_util.debug('Exception : ARP_CORRECT_CC_ERRORS.cc_error_occured');
1705 END IF;
1706 raise;
1707 END;
1708 END IF;
1709 IF PG_DEBUG in ('Y', 'C') THEN
1710 arp_util.debug('ARP_CORRECT_CC_ERRORS.cc_error_occurred()-');
1711 END IF;
1712 RETURN l_return_status;
1713 END cc_error_occurred;
1714
1715 /*===========================================================================+
1716 | PROCEDURE
1717 | correct_remittance_errors
1718 |
1719 | DESCRIPTION
1720 | Entry routine to automatically correct CC errors occured
1721 | during automatic remittance
1722 |
1723 |
1724 | SCOPE - PRIVATE
1725 |
1726 |
1727 | ARGUMENTS : IN:
1728 | p_request_id : Request id of autoreceipt/remittance batch
1729 | OUT: NONE
1730 |
1731 | RETURNS : NONE
1732 |
1733 | NOTES
1734 |
1735 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1736 |
1737 +===========================================================================*/
1738 PROCEDURE correct_remittance_errors(p_request_id IN NUMBER) IS
1739 l_cc_error_hist ar_cc_error_history%ROWTYPE;
1740 l_cc_trx_category ar_cc_error_history.cc_trx_category%TYPE;
1741 l_error_notes VARCHAR2(240);
1742 l_cc_error_desc VARCHAR2(240);
1743 l_first_record_flag VARCHAR2(1);
1744 l_cc_action_type VARCHAR2(1);
1745 l_cc_action_code ar_cc_error_mappings.cc_action_code%TYPE;
1746 CURSOR cr IS
1747 Select cash_receipt_id,
1748 type,
1749 receipt_method_id,
1750 payment_trxn_extension_id,
1751 cc_error_code,
1752 cc_error_text,
1753 request_id
1754 FROM ar_cash_receipts
1755 WHERE request_id IN ( select request_id
1756 from fnd_concurrent_requests
1757 where request_id = p_request_id
1758 or parent_request_id = p_request_id )
1759 AND cc_error_flag = 'Y'
1760 AND cc_error_code IS NOT NULL;
1761 BEGIN
1762 IF PG_DEBUG in ('Y', 'C') THEN
1763 arp_util.debug('arp_correct_cc_errors.correct_remittance_errors()+');
1764 END IF;
1765 FOR cr_rec IN cr LOOP
1766
1767 -- Call get_action_code
1768 get_action_code( p_cc_trx_id => cr_rec.cash_receipt_id,
1769 p_cc_trx_category => cr_rec.type,
1770 p_receipt_method_id => cr_rec.receipt_method_id,
1771 p_payment_trxn_extension_id => cr_rec.payment_trxn_extension_id,
1772 p_cc_error_code => cr_rec.cc_error_code,
1773 x_cc_error_desc => l_cc_error_desc, /* One which was stored in ar_cc_error_mappings*/
1774 x_first_record_flag => l_first_record_flag,
1775 x_cc_action_code => l_cc_action_code,
1776 x_cc_action_type => l_cc_action_type,
1777 x_error_notes => l_error_notes);
1778
1779 arp_util.debug('l_cc_action_code '|| l_cc_action_code);
1780 arp_util.debug('l_cc_action_type '|| l_cc_action_type);
1781 arp_util.debug('l_cc_error_desc '|| l_cc_error_desc);
1782 arp_util.debug('l_error_notes '|| l_error_notes);
1783
1784 IF l_cc_action_code = 'RET' THEN
1785 -- Unmark the errors so that receipt can be picked next time
1786 retry(p_cc_trx_id => cr_rec.cash_receipt_id,
1787 p_cc_trx_category => cr_rec.type,
1788 p_payment_trxn_extension_id => cr_rec.payment_trxn_extension_id,
1789 p_error_notes => l_error_notes);
1790 ELSIF l_cc_action_code = 'REAUT REC' THEN
1791 --null out the PSON info and Unmark the errors so that receipt is picked next time and reauthorized
1792 reauthorize(p_cc_trx_id => cr_rec.cash_receipt_id,
1793 p_cc_trx_category => cr_rec.type,
1794 p_payment_trxn_extension_id => cr_rec.payment_trxn_extension_id,
1795 p_error_notes => l_error_notes);
1796 ELSIF l_cc_action_code = 'REV REC' THEN
1797 obtain_alternate_payment(p_cc_trx_id => cr_rec.cash_receipt_id,
1798 p_cc_trx_category => cr_rec.type,
1799 p_error_notes => l_error_notes);
1800 END If;
1801
1802 -- Insert records into ar_cc_error_history
1803 IF l_cc_action_code in ('RET','REAUT REC','REV REC') THEN
1804 -- Update current_record_flag in ar_cc_error_history
1805 IF NVL(l_first_record_flag,'N') <> 'Y' THEN
1806 UPDATE ar_cc_error_history
1807 SET current_record_flag = 'N',
1808 last_update_date = sysdate,
1809 last_updated_by = pg_user_id,
1810 last_update_login = NVL(pg_login_id,pg_conc_login_id)
1811 WHERE cc_trx_id = cr_rec.cash_receipt_id
1812 AND cc_trx_category = cr_rec.type
1813 AND payment_trxn_extension_id = cr_rec.payment_trxn_extension_id
1814 AND cc_error_code = cr_rec.cc_error_code;
1815 END IF;
1816 l_cc_error_hist.request_id := cr_rec.request_id;
1817 l_cc_error_hist.cc_trx_category := cr_rec.type;
1818 l_cc_error_hist.cc_trx_id := cr_rec.cash_receipt_id;
1819 l_cc_error_hist.cc_error_code := cr_rec.cc_error_code;
1820 l_cc_error_hist.cc_vendor_error_desc := cr_rec.cc_error_text;
1821 l_cc_error_hist.cc_error_text := l_error_notes;
1822 l_cc_error_hist.cc_action_code := l_cc_action_code;
1823 l_cc_error_hist.cc_action_type_flag := l_cc_action_type;
1824 l_cc_error_hist.payment_trxn_extension_id := cr_rec.payment_trxn_extension_id;
1825 l_cc_error_hist.first_record_flag := l_first_record_flag;
1826 l_cc_error_hist.current_record_flag := 'Y';
1827 insert_p(l_cc_error_hist);
1828 END IF;
1829 END LOOP;
1830 IF PG_DEBUG in ('Y', 'C') THEN
1831 arp_util.debug('arp_correct_cc_errors.correct_remittance_errors()-');
1832 END IF;
1833 EXCEPTION
1834 WHEN OTHERS THEN
1835 IF PG_DEBUG in ('Y', 'C') THEN
1836 arp_util.debug('Exception in arp_correct_cc_errors.correct_remittance_errors()');
1837 END IF;
1838 raise;
1839 END correct_remittance_errors;
1840
1841 /*===========================================================================+
1842 | PROCEDURE
1843 | correct_creation_errors
1844 |
1845 | DESCRIPTION
1846 | Entry routine to automatically correct CC errors occured during
1847 | automatic creation program
1848 |
1849 |
1850 | SCOPE - PRIVATE
1851 |
1852 |
1853 | ARGUMENTS : IN:
1854 | p_request_id : Request id of autoreceipt/remittance batch
1855 | OUT: NONE
1856 |
1857 | RETURNS : NONE
1858 |
1859 | NOTES
1860 |
1861 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1862 |
1863 +===========================================================================*/
1864 PROCEDURE correct_creation_errors(p_request_id IN NUMBER) IS
1865 l_cc_error_hist ar_cc_error_history%ROWTYPE;
1866 l_cc_trx_category ar_cc_error_history.cc_trx_category%TYPE;
1867 l_error_notes VARCHAR2(240);
1868 l_cc_error_desc VARCHAR2(240);
1869 l_first_record_flag VARCHAR2(1);
1870 l_cc_action_type VARCHAR2(1);
1871 l_cc_action_code ar_cc_error_mappings.cc_action_code%TYPE;
1872 CURSOR inv IS
1873 Select trx.customer_trx_id,
1874 trx_type.type,
1875 trx.receipt_method_id,
1876 trx.payment_trxn_extension_id,
1877 trx.cc_error_code,
1878 trx.cc_error_text,
1879 trx.request_id
1880 FROM ra_customer_trx trx,
1881 ra_cust_trx_types trx_type
1882 WHERE trx.cust_trx_type_id = trx_type.cust_trx_type_id
1883 AND trx.request_id IN ( select request_id
1884 from fnd_concurrent_requests
1885 where request_id = p_request_id
1886 or parent_request_id = p_request_id )
1887 AND trx.cc_error_flag = 'Y'
1888 AND cc_error_code IS NOT NULL;
1889 BEGIN
1890 IF PG_DEBUG in ('Y', 'C') THEN
1891 arp_util.debug('ARP_CORRECT_CC_ERRORS.correct_creation_errors()+');
1892 END IF;
1893 FOR inv_rec IN inv LOOP
1894 get_action_code( p_cc_trx_id => inv_rec.customer_trx_id,
1895 p_cc_trx_category => inv_rec.type,
1896 p_receipt_method_id => inv_rec.receipt_method_id,
1897 p_payment_trxn_extension_id => inv_rec.payment_trxn_extension_id,
1898 p_cc_error_code => inv_rec.cc_error_code,
1899 x_cc_error_desc => l_cc_error_desc, /* One which was stored in ar_cc_error_mappings*/
1900 x_first_record_flag => l_first_record_flag,
1901 x_cc_action_code => l_cc_action_code,
1902 x_cc_action_type => l_cc_action_type,
1903 x_error_notes => l_error_notes);
1904
1905 arp_util.debug('l_cc_action_code '|| l_cc_action_code);
1906 arp_util.debug('l_cc_action_type '|| l_cc_action_type);
1907 arp_util.debug('l_cc_error_desc '|| l_cc_error_desc);
1908 arp_util.debug('l_error_notes '|| l_error_notes);
1909
1910 IF l_cc_action_code = 'RET' THEN
1911 -- Unmark the errors so that invoice is picked next time
1912 retry(p_cc_trx_id => inv_rec.customer_trx_id,
1913 p_cc_trx_category => inv_rec.type,
1914 p_payment_trxn_extension_id => inv_rec.payment_trxn_extension_id,
1915 p_error_notes => l_error_notes);
1916 ELSIF l_cc_action_code = 'CLR PAY INFO' THEN
1917 obtain_alternate_payment(p_cc_trx_id => inv_rec.customer_trx_id,
1918 p_cc_trx_category => inv_rec.type,
1919 p_error_notes => l_error_notes);
1920 END IF;
1921
1922 -- Insert records into ar_cc_error_history
1923 IF l_cc_action_code in ('RET','CLR PAY INFO') THEN
1924 -- Update current_record_flag in ar_cc_error_history
1925 IF NVL(l_first_record_flag,'N') <> 'Y' THEN
1926 UPDATE ar_cc_error_history
1927 SET current_record_flag = 'N',
1928 last_update_date = sysdate,
1929 last_updated_by = pg_user_id,
1930 last_update_login = NVL(pg_login_id,pg_conc_login_id)
1931 WHERE cc_trx_id = inv_rec.customer_trx_id
1932 AND cc_trx_category = inv_rec.type
1933 AND payment_trxn_extension_id = inv_rec.payment_trxn_extension_id
1934 AND cc_error_code = inv_rec.cc_error_code;
1935 arp_util.debug('Update current_record_flag. Sql%rowcount '|| sql%rowcount);
1936
1937 END IF;
1938 l_cc_error_hist.request_id := inv_rec.request_id;
1939 l_cc_error_hist.cc_trx_category := inv_rec.type;
1940 l_cc_error_hist.cc_trx_id := inv_rec.customer_trx_id;
1941 l_cc_error_hist.cc_error_code := inv_rec.cc_error_code;
1942 l_cc_error_hist.cc_vendor_error_desc := inv_rec.cc_error_text;
1943 l_cc_error_hist.cc_error_text := l_error_notes;
1944 l_cc_error_hist.cc_action_code := l_cc_action_code;
1945 l_cc_error_hist.cc_action_type_flag := l_cc_action_type;
1946 l_cc_error_hist.payment_trxn_extension_id := inv_rec.payment_trxn_extension_id;
1947 l_cc_error_hist.first_record_flag := l_first_record_flag;
1948 l_cc_error_hist.current_record_flag := 'Y';
1949
1950 arp_util.debug('Calling Insert into ar_cc_error_history');
1951 insert_p(l_cc_error_hist);
1952 END IF;
1953 END LOOP;
1954 IF PG_DEBUG in ('Y', 'C') THEN
1955 arp_util.debug('ARP_CORRECT_CC_ERRORS.correct_creation_errors()-');
1956 END IF;
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959 IF PG_DEBUG in ('Y', 'C') THEN
1960 arp_util.debug('EXCEPTION ARP_CORRECT_CC_ERRORS.correct_creation_errors()');
1961 END IF;
1962 RAISE;
1963 END correct_creation_errors;
1964
1965 /*===========================================================================+
1966 | PROCEDURE
1967 | retry
1968 |
1969 | DESCRIPTION
1970 | Make credit card errored transaction available
1971 | for next run of automatic receipt creation/remittance by marking it as
1972 | non-errored
1973 |
1974 |
1975 | SCOPE - PUBLIC
1976 |
1977 |
1978 | ARGUMENTS : IN:
1979 | p_cc_trx_id - Cash_receipt_id/Customer_trx_id
1980 | p_cc_trx_category - RECEIPT/INVOICE/REFUND
1981 | p_payment_trxn_extension_id - This is the id correpsonding to
1982 | errored credit card in iby_trxn_extensions_v
1983 | p_error_notes - Error notes to be attached
1984 | OUT:
1985 |
1986 | RETURNS : NONE
1987 |
1988 | NOTES
1989 |
1990 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
1991 |
1992 +===========================================================================*/
1993 PROCEDURE retry(p_cc_trx_id IN NUMBER,
1994 p_cc_trx_category IN VARCHAR2,
1995 p_payment_trxn_extension_id IN NUMBER,
1996 p_error_notes IN VARCHAR2) IS
1997 l_error_notes VARCHAR2(240);
1998 BEGIN
1999 IF PG_DEBUG in ('Y', 'C') THEN
2000 arp_util.debug('ARP_CORRECT_CC_ERRORS.retry()+');
2001 arp_util.debug('p_payment_trxn_extension_id ' || p_payment_trxn_extension_id );
2002 arp_util.debug('p_cc_trx_category ' || p_cc_trx_category);
2003 arp_util.debug('p_error_notes ' || p_error_notes);
2004 END IF;
2005 IF (p_error_notes IS NULL) OR(p_error_notes = ' ') THEN
2006 l_error_notes := NULL;
2007 ELSE
2008 l_error_notes := p_error_notes;
2009 END IF;
2010 IF p_cc_trx_category IN ('CASH','MISC') THEN
2011 arp_util.debug('Updating ar_cash_receipts record');
2012 arp_util.debug('cash_receipt_id '|| p_cc_trx_id);
2013
2014 UPDATE ar_cash_receipts
2015 SET cc_error_flag = NULL,
2016 cc_error_code = NULL,
2017 cc_error_text = NULL,
2018 comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
2019 rec_version_number = rec_version_number+1
2020 WHERE cash_receipt_id = p_cc_trx_id;
2021
2022 arp_util.debug ('Sql rows updated ' || sql%rowcount);
2023
2024 ELSE
2025 arp_util.debug('Updating ra_customer_trx records');
2026 arp_util.debug('customer_trx_id '|| p_cc_trx_id);
2027
2028 UPDATE ra_customer_trx
2029 SET cc_error_flag =NULL,
2030 cc_error_code = NULL,
2031 cc_error_text = NULL
2032 WHERE customer_trx_id = p_cc_trx_id;
2033
2034 arp_util.debug ('Sql rows updated ' || sql%rowcount);
2035
2036 IF l_error_notes IS NOT NULL THEN
2037 attach_notes(p_customer_trx_id => p_cc_trx_id,
2038 p_text => l_error_notes);
2039 END IF;
2040 END IF;
2041
2042 IF PG_DEBUG in ('Y', 'C') THEN
2043 arp_util.debug('ARP_CORRECT_CC_ERRORS.retry()-');
2044 END IF;
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 arp_util.debug('Exception in ARP_CORRECT_CC_ERRORS.retry()');
2048 raise;
2049 END retry;
2050
2051 /*===========================================================================+
2052 | PROCEDURE
2053 | reauthorize
2054 |
2055 | DESCRIPTION
2056 | Clears payment server order num and approval code from a receipt so that
2057 | the receipt will be re-authorized in the next run of autoremittance
2058 |
2059 |
2060 | SCOPE - PUBLIC
2061 |
2062 |
2063 | ARGUMENTS : IN:
2064 | p_cc_trx_id - Cash_receipt_id/Customer_trx_id
2065 | p_cc_trx_category - RECEIPT/INVOICE/REFUND
2066 | p_payment_trxn_extension_id - This is the id correpsonding to
2067 | errored credit card in iby_trxn_extensions_v
2068 | p_error_notes - Error notes to be attached
2069 | OUT:
2070 |
2071 | RETURNS : NONE
2072 |
2073 | NOTES
2074 |
2075 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
2076 |
2077 +===========================================================================*/
2078 PROCEDURE reauthorize(p_cc_trx_id IN NUMBER,
2079 p_cc_trx_category IN VARCHAR2,
2080 p_payment_trxn_extension_id IN NUMBER,
2081 p_error_notes IN VARCHAR2) IS
2082 l_error_notes VARCHAR2(240);
2083
2084 l_return_status VARCHAR2(2000);
2085 l_msg_count NUMBER;
2086 l_msg_data VARCHAR2(2000);
2087 l_instrument_assignment_id NUMBER;
2088 l_response_rec IBY_FNDCPT_COMMON_PUB.RESULT_REC_TYPE;
2089 l_payment_trxn_extension_id NUMBER;
2090 l_payer_rec IBY_FNDCPT_COMMON_PUB.PAYERCONTEXT_REC_TYPE;
2091 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TRXNEXTENSION_REC_TYPE;
2092 l_cust_account_id NUMBER;
2093 l_cust_site_use_id NUMBER;
2094 l_party_id NUMBER;
2095 l_org_id NUMBER;
2096 l_org_type VARCHAR2(30);
2097 l_payment_function VARCHAR2(30);
2098 l_payment_channel VARCHAR2(30);
2099 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
2100
2101 BEGIN
2102 IF PG_DEBUG in ('Y', 'C') THEN
2103 arp_util.debug('ARP_CORRECT_CC_ERRORS.reauthorize()+');
2104 END IF;
2105 IF (p_error_notes IS NULL) OR(p_error_notes = ' ') THEN
2106 l_error_notes := NULL;
2107 ELSE
2108 l_error_notes := p_error_notes;
2109 END IF;
2110 -- Ideally this check is not necessary
2111 -- as there won't be reauthorization failure for invoice
2112 IF p_cc_trx_category = 'CASH' THEN
2113 UPDATE ar_cash_receipts
2114 SET approval_code = NULL,
2115 cc_error_flag = NULL,
2116 cc_error_code = NULL,
2117 cc_error_text = NULL,
2118 comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,240))
2119 WHERE cash_receipt_id = p_cc_trx_id;
2120
2121 IF p_payment_trxn_extension_id IS NOT NULL THEN
2122
2123 SELECT
2124 P.PARTY_ID,
2125 P.CUST_ACCOUNT_ID,
2126 P.ACCT_SITE_USE_ID,
2127 P.ORG_ID,
2128 P.ORG_TYPE,
2129 U.PAYMENT_FUNCTION,
2130 B.INSTR_ASSIGNMENT_ID,
2131 B.PAYMENT_CHANNEL_CODE,
2132 CR.RECEIPT_NUMBER
2133 INTO
2134 l_party_id,
2135 l_cust_account_id,
2136 l_cust_site_use_id,
2137 l_org_id,
2138 l_org_type,
2139 l_payment_function,
2140 l_instrument_assignment_id,
2141 l_payment_channel,
2142 l_receipt_number
2143 FROM
2144 IBY_FNDCPT_TX_EXTENSIONS B,
2145 IBY_EXTERNAL_PAYERS_ALL P,
2146 IBY_PMT_INSTR_USES_ALL U,
2147 AR_CASH_RECEIPTS CR
2148 WHERE B.TRXN_EXTENSION_ID = p_payment_trxn_extension_id
2149 AND B.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID
2150 AND U.EXT_PMT_PARTY_ID = P.EXT_PAYER_ID
2151 AND CR.CASH_RECEIPT_ID = p_cc_trx_id
2152 AND B.TRXN_EXTENSION_ID = CR.PAYMENT_TRXN_EXTENSION_ID;
2153
2154 /* Set Payer Details in Payer Rec. */
2155 l_payer_rec.payment_function := l_payment_function;
2156 l_payer_rec.party_id := l_party_id;
2157 l_payer_rec.cust_account_id := l_cust_account_id;
2158 l_payer_rec.account_site_id := l_cust_site_use_id;
2159 l_payer_rec.org_id := l_org_id;
2160 l_payer_rec.org_type := l_org_type;
2161
2162 /* Set Transction Attributes in Transaction Atrribs Rec. */
2163 l_trxn_attribs_rec.originating_application_id := arp_standard.application_id;
2164 l_trxn_attribs_rec.order_id := l_receipt_number;
2165 l_trxn_attribs_rec.trxn_ref_number1 := 'RECEIPT';
2166 l_trxn_attribs_rec.trxn_ref_number2 := p_cc_trx_id;
2167
2168 IBY_FNDCPT_TRXN_PUB.CREATE_TRANSACTION_EXTENSION(
2169 p_api_version => 1.0,
2170 p_init_msg_list => FND_API.G_TRUE,
2171 p_commit => FND_API.G_FALSE,
2172 x_return_status => l_return_status,
2173 x_msg_count => l_msg_count,
2174 x_msg_data => l_msg_data,
2175 p_payer => l_payer_rec,
2176 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
2177 p_pmt_channel => l_payment_channel,
2178 p_instr_assignment => l_instrument_assignment_id,
2179 p_trxn_attribs => l_trxn_attribs_rec,
2180 x_entity_id => l_payment_trxn_extension_id,
2181 x_response => l_response_rec);
2182
2183 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2184
2185 update ar_cash_receipts set
2186 payment_trxn_extension_id = l_payment_trxn_extension_id
2187 where cash_receipt_id = p_cc_trx_id;
2188
2189 ELSE
2190 arp_standard.debug('Processing cash_receipt_id :- ' || p_cc_trx_id);
2191 arp_standard.debug('result_code :- ' || l_response_rec.result_code);
2192 arp_standard.debug('result_category :- ' || l_response_rec.result_category);
2193 arp_standard.debug('result_message :- ' || l_response_rec.result_message);
2194 arp_standard.debug('l_return_status :- ' || l_return_status);
2195 arp_standard.debug('l_msg_count :- ' || l_msg_count);
2196 arp_standard.debug('l_msg_data :- ' || l_msg_data);
2197 END IF;
2198 END IF;
2199
2200 END IF;
2201 IF PG_DEBUG in ('Y', 'C') THEN
2202 arp_util.debug('ARP_CORRECT_CC_ERRORS.authorize()-');
2203 END IF;
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 IF PG_DEBUG in ('Y', 'C') THEN
2207 arp_util.debug('Exception in ARP_CORRECT_CC_ERRORS.reauthorize()');
2208 END IF;
2209 raise;
2210 END reauthorize;
2211
2212 /*===========================================================================+
2213 | PROCEDURE
2214 | obtain_alternate_payment
2215 |
2216 | DESCRIPTION
2217 | Perform the cc correction actions for clear payment info and reverse receipt
2218 |
2219 |
2220 | SCOPE - PUBLIC
2221 |
2222 |
2223 | ARGUMENTS : IN:
2224 | p_cc_trx_id - Cash_receipt_id/Customer_trx_id
2225 | p_cc_trx_category - RECEIPT/INVOICE/REFUND
2226 | p_error_notes - Error notes to be attached
2227 | OUT:
2228 |
2229 | RETURNS : NONE
2230 |
2231 | NOTES
2232 |
2233 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
2234 +===========================================================================*/
2235 PROCEDURE obtain_alternate_payment(p_cc_trx_id IN NUMBER,
2236 p_cc_trx_category IN VARCHAR2,
2237 p_error_notes IN VARCHAR2) IS
2238 CURSOR inv IS
2239 SELECT trx.customer_trx_id,
2240 trx.trx_number,
2241 trx_types.type,
2242 cr.receipt_number
2243 FROM ra_customer_trx trx,
2244 ar_receivable_applications ra,
2245 ar_cash_receipts cr,
2246 ra_cust_trx_types trx_types
2247 WHERE trx.customer_trx_id = ra.applied_customer_trx_id
2248 AND ra.cash_receipt_id = cr.cash_receipt_id
2249 AND cr.payment_trxn_extension_id = trx.payment_trxn_extension_id
2250 AND cr.receipt_method_id = trx.receipt_method_id
2251 AND ra.display = 'Y'
2252 AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
2253 AND cr.cash_receipt_id = p_cc_trx_id;
2254
2255 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2256 l_rec_number ar_cash_receipts.receipt_number%TYPE;
2257 l_error_notes VARCHAR2(240);
2258 l_dummy_number NUMBER;
2259 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
2260 l_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
2261 l_trx_number ra_customer_trx.trx_number%TYPE;
2262 l_object_version_number ar_cash_receipts.rec_version_number%TYPE;
2263 l_cc_error_code ar_cash_receipts.cc_error_code%TYPE;
2264 l_cc_error_desc ar_cash_receipts.cc_error_text%TYPE;
2265 l_cc_trx_category_dsp VARCHAR2(240);
2266 l_cc_trx_number ar_cash_receipts.receipt_number%TYPE;
2267 l_cc_trx_currency ar_cash_receipts.currency_code%TYPE;
2268 l_cc_trx_amount NUMBER;
2269 l_cc_trx_date DATE;
2270 l_customer_name hz_parties.party_name%TYPE;
2271 l_customer_number hz_cust_accounts.account_number%TYPE;
2272 l_customer_location hz_cust_site_uses.location%TYPE;
2273 l_cc_number IBY_FNDCPT_PAYER_ASSGN_INSTR_V.CARD_NUMBER%TYPE;
2274 l_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2275 l_approval_code ra_customer_trx.approval_code%TYPE;
2276 l_collector ar_collectors.name%TYPE;
2277 l_payment_method_name ar_receipt_methods.name%TYPE;
2278 PAY_exception EXCEPTION;
2279
2280 BEGIN
2281 IF PG_DEBUG in ('Y', 'C') THEN
2282 arp_util.debug('ARP_CORRECT_CC_ERRORS.obtain_alternate_payment()+');
2283 END IF;
2284 IF (p_error_notes IS NULL) OR (p_error_notes = ' ') THEN
2285
2286 ---Bug 4192368 get notes from description field of lookup
2287 ---This is because CC mapping UI also selects from description
2288 BEGIN
2289
2290 select description
2291 into l_error_notes
2292 from ar_lookups
2293 where lookup_type = 'AR_CC_ERROR_NOTES'
2294 and lookup_code = 'E1';
2295
2296 EXCEPTION
2297 WHEN no_data_found then
2298 null;
2299 END;
2300
2301 ELSE
2302 l_error_notes := p_error_notes;
2303 END IF;
2304
2305 IF p_cc_trx_category IN ('CASH','MISC') THEN
2306 /*--------------------------------------------------------------+
2307 |This is the case of reverse receipt action |
2308 |1)Clear the credit card information and payment method |
2309 | from the invoice. This should be only done if the invoice |
2310 | has already not been unapplied for all the cash receipts, |
2311 | and attach an error note to the invoice. |
2312 |2)Reverse the receipt |
2313 |3)Attach an comment to the receipt |
2314 |4)Raise business event for this |
2315 +--------------------------------------------------------------*/
2316 arp_util.debug('p_cc_trx_category '|| p_cc_trx_category);
2317 /* Loop through invoices */
2318 FOR inv_cur IN inv LOOP
2319 -- Lock the invoice that needs to be cleared
2320 -- Locking should be nowait when the package is being called
2321 -- other than conc program
2322 IF ARP_GLOBAL.request_id IS NOT NULL then
2323 BEGIN
2324 SELECT 1
2325 INTO l_dummy_number
2326 FROM ra_customer_trx
2327 WHERE customer_trx_id = inv_cur.customer_trx_id
2328 FOR UPDATE OF customer_trx_id;
2329 EXCEPTION
2330 WHEN OTHERS THEN
2331 raise;
2332 END;
2333 ELSE
2334 lock_table_nowait(p_key=>inv_cur.customer_trx_id,
2335 p_table_name=>'RA_CUSTOMER_TRX',
2336 p_trx_number=>inv_cur.trx_number);
2337 END IF;
2338 -- Clear the credit card information and payment method from invoice
2339 clear_invoice_pay_info(p_customer_trx_id=>inv_cur.customer_trx_id,
2340 p_cc_trx_category =>inv_cur.type,
2341 p_source_receipt_id => p_cc_trx_id,
2342 p_source_receipt_number => inv_cur.receipt_number,
2343 p_error_notes=>l_error_notes);
2344 END LOOP;
2345 /*Collect the info to pass to events */
2346 Select NVL(cr.cc_error_code,'Unknown'),
2347 NVL(cr.cc_error_text,'Unknown Error'),
2348 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CC_TRX_CATEGORY',cr.type),
2349 cr.receipt_number,
2350 cr.currency_code,
2351 cr.amount,
2352 cr.receipt_date,
2353 party.party_name,
2354 cust.account_number,
2355 site_uses.location,
2356 decode(iby.INSTRUMENT_TYPE,'CREDITCARD',iby.CARD_NUMBER,iby.ACCOUNT_NUMBER),
2357 cr.payment_trxn_extension_id,
2358 cr.approval_code approval_code,
2359 ARP_CORRECT_CC_ERRORS.get_collector_name(cr.pay_from_customer,cr.customer_site_use_id),
2360 rm.name
2361 INTO l_cc_error_code,
2362 l_cc_error_desc,
2363 l_cc_trx_category_dsp,
2364 l_cc_trx_number,
2365 l_cc_trx_currency,
2366 l_cc_trx_amount,
2367 l_cc_trx_date,
2368 l_customer_name,
2369 l_customer_number,
2370 l_customer_location,
2371 l_cc_number,
2372 l_payment_trxn_extension_id,
2373 l_approval_code,
2374 l_collector,
2375 l_payment_method_name
2376 FROM ar_cash_receipts cr,
2377 ar_receipt_methods rm,
2378 hz_parties party,
2379 hz_cust_accounts cust,
2380 hz_cust_site_uses site_uses,
2381 iby_trxn_extensions_v iby
2382 WHERE rm.receipt_method_id = cr.receipt_method_id
2383 AND cr.pay_from_customer = cust.cust_account_id (+)
2384 AND cust.party_id = party.party_id (+)
2385 AND cr.customer_site_use_id = site_uses.site_use_id (+)
2386 AND cr.cc_error_flag = 'Y'
2387 AND cr.selected_remittance_batch_id IS NULL
2388 AND iby.trxn_extension_id = cr.payment_trxn_extension_id
2389 AND cr.cash_receipt_id = p_cc_trx_id;
2390 -- reverse the receipt
2391 reverse_receipt(p_cc_trx_id,l_error_notes);
2392 -- Attach comment to the invoice
2393 UPDATE ar_cash_receipts
2394 SET comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000))
2395 WHERE cash_receipt_id = p_cc_trx_id;
2396
2397 IF p_cc_trx_category = 'CASH' THEN
2398 Raise_Collection_Event(p_cc_trx_id => p_cc_trx_id,
2399 p_cc_error_code=> l_cc_error_code,
2400 p_cc_error_desc => l_cc_error_desc,
2401 p_cc_trx_category_dsp=>l_cc_trx_category_dsp,
2402 p_cc_trx_number=>l_cc_trx_number,
2403 p_cc_trx_currency=>l_cc_trx_currency,
2404 p_cc_trx_amount=>l_cc_trx_amount,
2405 p_cc_trx_date=>l_cc_trx_date,
2406 p_customer_name=>l_customer_name,
2407 p_customer_number=>l_customer_number,
2408 p_customer_location=>l_customer_location,
2409 p_cc_number=>l_cc_number,
2410 p_payment_trxn_extension_id=>l_payment_trxn_extension_id,
2411 p_approval_code=>l_approval_code,
2412 p_collector=>l_collector,
2413 p_payment_method_name=>l_payment_method_name,
2414 p_error_notes=>l_error_notes);
2415 ELSE
2416 /*Get the source cash receipt */
2417 SELECT cr.cash_receipt_id,
2418 cr.receipt_number
2419 INTO l_cash_receipt_id,
2420 l_rec_number
2421 FROM ar_cash_receipts cr
2422 WHERE cash_receipt_id in (SELECT ra.cash_receipt_id
2423 FROM ar_receivable_applications ra
2424 WHERE ra.application_ref_id = p_cc_trx_id
2425 AND ra.applied_payment_schedule_id = -6);
2426
2427 Raise_RefundReverse_Event(p_misc_cash_receipt_id => p_cc_trx_id,
2428 p_cc_error_code=> l_cc_error_code,
2429 p_cc_error_desc => l_cc_error_desc,
2430 p_cc_trx_number=>l_cc_trx_number,
2431 p_cc_trx_currency=>l_cc_trx_currency,
2432 p_cc_trx_amount=>l_cc_trx_amount,
2433 p_cc_trx_date=>l_cc_trx_date,
2434 p_customer_name=>l_customer_name,
2435 p_customer_number=>l_customer_number,
2436 p_customer_location=>l_customer_location,
2437 p_cc_number=>l_cc_number,
2438 p_payment_trxn_extension_id=>l_payment_trxn_extension_id,
2439 p_approval_code=>l_approval_code,
2440 p_collector=>l_collector,
2441 p_payment_method_name=>l_payment_method_name,
2442 p_source_receipt_id=>l_cash_receipt_id,
2443 p_source_receipt_num=>l_rec_number,
2444 p_error_notes=>l_error_notes);
2445 END IF;
2446 ELSE
2447 /*------------------------------------------------------------+
2448 |1)Clear the credit card information and payment method |
2449 | from invoice and Raise business event for this |
2450 |2)Attach an error note to the invoice |
2451 +------------------------------------------------------------*/
2452 -- send an error message
2453 arp_util.debug('p_cc_trx_category '|| p_cc_trx_category);
2454
2455 -- Clear the credit card information and payment method from invoice
2456 clear_invoice_pay_info(p_customer_trx_id => p_cc_trx_id,
2457 p_cc_trx_category => p_cc_trx_category,
2458 p_error_notes => p_error_notes);
2459
2460 END IF;
2461 IF PG_DEBUG in ('Y', 'C') THEN
2462 arp_util.debug('ARP_CORRECT_CC_ERRORS.obtain_alternate_payment()-');
2463 END IF;
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 IF PG_DEBUG in ('Y', 'C') THEN
2467 arp_util.debug('Exception in ARP_CORRECT_CC_ERRORS.obtain_alternate_payment()');
2468 END IF;
2469 raise;
2470 END obtain_alternate_payment;
2471
2472 /*===========================================================================+
2473 | PROCEDURE
2474 | cc_auto_correct
2475 |
2476 | DESCRIPTION
2477 | Spawns concurrent program for Credit Card Correction
2478 |
2479 | SCOPE - PUBLIC
2480 |
2481 |
2482 | ARGUMENTS : IN:
2483 | errbuf - Error buf for concurrent program
2484 | retcode - Error code for concurrent program
2485 | p_request_id - autoreceipt/autoremittance request id
2486 | p_mode - Creation/Remittance
2487 | OUT:
2488 |
2489 | RETURNS : NONE
2490 |
2491 | NOTES
2492 |
2493 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
2494 |
2495 +===========================================================================*/
2496 PROCEDURE cc_auto_correct(
2497 errbuf IN OUT NOCOPY VARCHAR2,
2498 retcode IN OUT NOCOPY VARCHAR2,
2499 p_request_id IN NUMBER,
2500 p_mode IN VARCHAR2) IS
2501 CURSOR lock_cr IS
2502 SELECT 'lock'
2503 FROM ar_cash_receipts
2504 WHERE request_id IN ( select request_id
2505 from fnd_concurrent_requests
2506 where request_id = p_request_id
2507 or parent_request_id = p_request_id )
2508 AND cc_error_flag = 'Y'
2509 AND cc_error_code IS NOT NULL
2510 FOR UPDATE OF cash_receipt_id;
2511 CURSOR lock_trx IS
2512 SELECT 'lock'
2513 FROM ra_customer_trx
2514 WHERE request_id IN ( select request_id
2515 from fnd_concurrent_requests
2516 where request_id = p_request_id
2517 or parent_request_id = p_request_id )
2518 AND cc_error_flag = 'Y'
2519 AND cc_error_code IS NOT NULL
2520 FOR UPDATE OF customer_trx_id;
2521 BEGIN
2522 IF PG_DEBUG in ('Y', 'C') THEN
2523 arp_util.debug('Begin : ' ||to_char(sysdate,'DD-MON-YYYY hh24:mi:ss'));
2524 arp_util.debug('arp_correct_cc_errors.cc_auto_correct()+');
2525 arp_util.debug('--------------- Input parameters --------------------');
2526 arp_util.debug('p_request_id :'||to_char(p_request_id));
2527 arp_util.debug('Called from : '||p_mode);
2528 END IF;
2529 IF (p_mode = 'REMITTANCE') THEN
2530 IF PG_DEBUG in ('Y', 'C') THEN
2531 arp_util.debug('Locking ar_cash_receipts records for processing..');
2532 END IF;
2533 OPEN lock_cr;
2534 CLOSE lock_cr;
2535 correct_remittance_errors(p_request_id);
2536 ELSE
2537 IF PG_DEBUG in ('Y', 'C') THEN
2538 arp_util.debug('Locking ra_customer_trx records for processing..');
2539 END IF;
2540 OPEN lock_trx;
2541 CLOSE lock_trx;
2542 correct_creation_errors(p_request_id);
2543 END IF;
2544 retcode := 0;
2545 errbuf := 'Sucess!';
2546 IF PG_DEBUG in ('Y', 'C') THEN
2547 arp_util.debug('arp_correct_cc_errors.cc_auto_correct()-');
2548 arp_util.debug('End : ' ||to_char(sysdate,'DD-MON-YYYY hh24:mi:ss'));
2549 END IF;
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 IF PG_DEBUG in ('Y', 'C') THEN
2553 arp_util.debug('Exception in arp_correct_cc_errors.cc_auto_correct');
2554 END IF;
2555 IF lock_trx%ISOPEN THEN
2556 CLOSE lock_trx;
2557 END IF;
2558 errbuf := fnd_message.get||' X '||SQLERRM;
2559 retcode := 2;
2560 END cc_auto_correct;
2561
2562
2563 /*===========================================================================+
2564 | PROCEDURE
2565 | correct_funds_error
2566 |
2567 | DESCRIPTION
2568 | Correct the errors. from funds transfer.
2569 |
2570 | SCOPE - PUBLIC
2571 |
2572 |
2573 | ARGUMENTS : IN:
2574 | p_cc_trx_id - Cash_receipt_id/Customer_trx_id
2575 | p_cc_trx_category - RECEIPT/INVOICE/REFUND
2576 | p_corrective_action -- WHAT is the action.
2577 | p_instrument_number -- the instrument_id
2578 | p_expiration_date -- expiry date.
2579 | p_error_notes - Error notes to be attached
2580 | OUT:
2581 |
2582 | RETURNS : NONE
2583 |
2584 | NOTES
2585 |
2586 | MODIFICATION HISTORY - Created by bichatte - 03-OCT-2005
2587 |
2588 +===========================================================================*/
2589 PROCEDURE correct_funds_error(p_cc_trx_id IN NUMBER,
2590 p_cc_trx_category IN VARCHAR2,
2591 p_corrective_action In VARCHAR2,
2592 p_instrument_number IN VARCHAR2,
2593 p_expiration_date IN VARCHAR2,
2594 p_error_notes IN VARCHAR2) IS
2595 l_error_notes VARCHAR2(240);
2596 BEGIN
2597 IF PG_DEBUG in ('Y', 'C') THEN
2598 arp_util.debug('ARP_CORRECT_CC_ERRORS.correct_funds_error()+');
2599
2600 arp_util.debug('value of p_cc_trx_id '||'<'||p_cc_trx_id||'>');
2601 arp_util.debug('value of p_cc_trx_category '||'<'||p_cc_trx_category||'>');
2602 arp_util.debug('value of p_corrective_action'||'<'||p_corrective_action||'>');
2603 arp_util.debug('value of p_instrument_number'||'<'||p_instrument_number||'>');
2604 arp_util.debug('value of p_expiration_date '||'<'||p_expiration_date||'>');
2605 arp_util.debug('value of p_error_notes '||'<'||p_error_notes||'>');
2606 END IF;
2607 IF (p_error_notes IS NULL) OR(p_error_notes = ' ') THEN
2608 l_error_notes := NULL;
2609 ELSE
2610 l_error_notes := p_error_notes;
2611 END IF;
2612
2613 IF p_corrective_action = 'Retry' THEN
2614
2615 IF p_cc_trx_category IN ('CASH','MISC') THEN
2616 UPDATE ar_cash_receipts
2617 SET cc_error_flag = NULL,
2618 cc_error_code = NULL,
2619 cc_error_text = NULL,
2620 comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
2621 rec_version_number = rec_version_number+1
2622 WHERE cash_receipt_id = p_cc_trx_id;
2623 ELSE
2624 UPDATE ra_customer_trx
2625 SET cc_error_flag =NULL,
2626 cc_error_code = NULL,
2627 cc_error_text = NULL
2628 WHERE customer_trx_id = p_cc_trx_id;
2629
2630 END IF;
2631
2632 END IF; /* end Retry */
2633
2634 IF p_corrective_action = 'Change Instrument' THEN
2635 IF p_cc_trx_category IN ('CASH','MISC') THEN
2636 UPDATE ar_cash_receipts
2637 SET cc_error_flag = NULL,
2638 cc_error_code = NULL,
2639 cc_error_text = NULL,
2640 comments = DECODE(p_error_notes,NULL,comments,substrb('<'||l_error_notes||'>'||comments,1,2000)),
2641 rec_version_number = rec_version_number+1
2642 WHERE cash_receipt_id = p_cc_trx_id;
2643 ELSE
2644 UPDATE ra_customer_trx
2645 SET cc_error_flag =NULL,
2646 cc_error_code = NULL,
2647 cc_error_text = NULL
2648 WHERE customer_trx_id = p_cc_trx_id;
2649
2650 END IF;
2651
2652
2653 END IF; /* end change instrument */
2654
2655 IF p_corrective_action = 'Reverse Receipt' THEN
2656
2657 /* i) reverse the rec
2658 ii) update receipt_method and payment info of Corresponding inv to null */
2659 obtain_alternate_payment(p_cc_trx_id => p_cc_trx_id,
2660 p_cc_trx_category => p_cc_trx_category,
2661 p_error_notes => l_error_notes);
2662 END IF; /* end reverse receipt */
2663
2664 IF p_corrective_action = 'Clear Payment Information' THEN
2665 IF p_cc_trx_category IN ('CASH','MISC') THEN
2666 /* i) update receipt_method and payment info of RECEIPT to null */
2667 UPDATE ar_cash_receipts_all
2668 SET payment_trxn_extension_id = NULL,
2669 receipt_method_id = NULL,
2670 cc_error_flag = NULL,
2671 last_update_date = sysdate,
2672 last_updated_by = pg_user_id,
2673 last_update_login = NVL(pg_login_id,pg_conc_login_id)
2674 WHERE cash_receipt_id = p_cc_trx_id;
2675
2676
2677 ELSE
2678 /* i) update receipt_method and payment info of inv to null */
2679 UPDATE ra_customer_trx_all
2680 SET payment_trxn_extension_id = NULL,
2681 receipt_method_id = NULL,
2682 cc_error_flag = NULL,
2683 last_update_date = sysdate,
2684 last_updated_by = pg_user_id,
2685 last_update_login = NVL(pg_login_id,pg_conc_login_id)
2686 WHERE customer_trx_id = p_cc_trx_id;
2687
2688
2689 END IF;
2690
2691 END IF; /* end Clear Payment Information */
2692
2693
2694 IF PG_DEBUG in ('Y', 'C') THEN
2695 arp_util.debug('ARP_CORRECT_CC_ERRORS.correct_funds_error()-');
2696 END IF;
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 arp_util.debug('Exception in ARP_CORRECT_CC_ERRORS.correct_funds_error()'|| SQLERRM );
2700 raise;
2701 END correct_funds_error;
2702
2703
2704
2705
2706
2707
2708 /*===========================================================================+
2709 | PROCEDURE
2710 | cc_auto_correct_cover
2711 |
2712 | DESCRIPTION
2713 | Calls cc_auto_correct based on if any CC Error has occured
2714 |
2715 | SCOPE - PUBLIC
2716 |
2717 |
2718 | ARGUMENTS : IN:
2719 | p_request_id - autoreceipt/autoremittance request id
2720 | p_mode - Creation/Remittance
2721 | OUT:
2722 |
2723 | RETURNS : NONE
2724 |
2725 | NOTES
2726 |
2727 | MODIFICATION HISTORY - Created by Srinivasa Kini - 25-Aug-2004
2728 |
2729 +===========================================================================*/
2730 PROCEDURE cc_auto_correct_cover(p_request_id IN NUMBER,
2731 p_mode IN VARCHAR2) IS
2732 l_request_id NUMBER;
2733 l_org_id NUMBER;
2734
2735 BEGIN
2736 IF PG_DEBUG in ('Y', 'C') THEN
2737 arp_util.debug('ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover()+');
2738 arp_util.debug('Calling Program request id passed : ' || p_request_id);
2739 END IF;
2740 IF cc_error_occurred(p_mode,p_request_id) = 'Y' THEN
2741 IF PG_DEBUG in ('Y', 'C') THEN
2742 arp_util.debug('cc error has occurred while automatic receipt processing..');
2743 arp_util.debug('So calling the concurrent program to correct any automatically correctable issues');
2744 END IF;
2745
2746 l_org_id := mo_global.get_current_org_id;
2747
2748 IF PG_DEBUG in ('Y', 'C') THEN
2749 arp_util.debug('Setting the org_id context to child request '|| l_org_id);
2750 END IF;
2751
2752 fnd_request.set_org_id(l_org_id);
2753
2754 l_request_id := fnd_request.submit_request(
2755 application=>'AR',
2756 program=>'ARCCAUTOCON',
2757 argument1=>p_request_id,
2758 argument2=>p_mode);
2759 IF PG_DEBUG in ('Y', 'C') THEN
2760 arp_util.debug('Submitted request no : ' || l_request_id);
2761 END IF;
2762 commit;
2763 ELSE
2764 IF PG_DEBUG in ('Y', 'C') THEN
2765 arp_util.debug('No cc error has occurred while automatic receipt processing..');
2766 END IF;
2767 END IF;
2768 IF PG_DEBUG in ('Y', 'C') THEN
2769 arp_util.debug('ARP_CORRECT_CC_ERRORS.cc_auto_correct_cover()-');
2770 END IF;
2771 END cc_auto_correct_cover;
2772 /*---------------------------------------------+
2773 | Package initialization section. |
2774 | Sets WHO column variables for later use. |
2775 +---------------------------------------------*/
2776 BEGIN
2777 pg_user_id := fnd_global.user_id;
2778 pg_conc_login_id := fnd_global.conc_login_id;
2779 pg_login_id := fnd_global.login_id;
2780 /*Get the user_name */
2781 SELECT user_name
2782 INTO pg_user_name
2783 FROM fnd_user
2784 WHERE user_id = pg_user_id;
2785 END ARP_CORRECT_CC_ERRORS;