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