[Home] [Help]
PACKAGE BODY: APPS.ARP_DEDUCTION
Source
1 PACKAGE BODY ARP_DEDUCTION AS
2 /* $Header: ARXLDEDB.pls 120.19.12020000.2 2012/07/26 07:44:30 ashlkuma ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8 /*========================================================================
9 | Prototype Declarations Procedures
10 *=======================================================================*/
11
12 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 /*========================================================================
15 | Prototype Declarations Functions
16 *=======================================================================*/
17
18 /*========================================================================
19 | PUBLIC FUNCTION CHECK_TM
20 |
21 | DESCRIPTION
22 | This function returns true if Trade Management is installed
23 | otherwise it returns false.
24 |
25 | CALLED FROM PROCEDURES/FUNCTIONS
26 | arlplb.opc (lockbox main program)
27 | arccbp.lpc (postbatch)
28 |
29 | CALLED PROCEDURES/FUNCTIONS
30 | OZF_CLAIM_INSTALL.CHECK_INSTALLED
31 |
32 | PARAMETERS
33 | none
34 | KNOWN ISSUES
35 |
36 | NOTES
37 |
38 | MODIFICATION HISTORY
39 | Date Author Description of Changes
40 | 27-NOV-2002 AMateen Created
41 | 20-NOV-2003 jbeckett Bug 3251839: Checks cached arp_global
42 | instead of TM function.
43 |
44 *=======================================================================*/
45 FUNCTION CHECK_TM RETURN BOOLEAN IS
46
47 BEGIN
48 IF arp_global.tm_installed_flag = 'Y' THEN
49 RETURN TRUE;
50 ELSE
51 RETURN FALSE;
52 END IF;
53
54 EXCEPTION
55
56 WHEN OTHERS THEN
57
58 IF PG_DEBUG in ('Y','C') THEN
59 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CHECK_TM');
60 END IF;
61 RETURN FALSE;
62
63 END CHECK_TM;
64
65
66 /*========================================================================
67 | PUBLIC FUNCTION CHECK_TM_DEFAULT_SETUP
68 |
69 | DESCRIPTION
70 | This function returns true if Trade Management Default setup is
71 | available otherwise it returns false.
72 |
73 | CALLED FROM PROCEDURES/FUNCTIONS
74 | arlplb.opc (Lockbox)
75 | arccbp.lpc (Postbatch)
76 |
77 |
78 | CALLS PROCEDURES/FUNCTIONS
79 | OZF_CLAIM_INSTALL.CHECK_DEFAULT_SETUP
80 |
81 | PARAMETERS
82 |
83 | KNOWN ISSUES
84 |
85 | NOTES
86 | This will check to see if Trade Management is setup properly to
87 | handle Claims. If TM is not set up to handle claims and the user
88 | has setup to create claims, we want postbatch and lockbox to
89 | error and force the setup of TM before continuing.
90 |
91 | MODIFICATION HISTORY
92 | Date Author Description of Changes
93 | 10-FEB-2003 cthangai Created
94 | 20-NOV-2003 jbeckett Bug 3251839: Replaced ozf function
95 | call with check on cached
96 | arp_global.tm_default_setup_flag.
97 *=======================================================================*/
98 FUNCTION CHECK_TM_DEFAULT_SETUP RETURN BOOLEAN IS
99
100 BEGIN
101
102 IF arp_global.tm_default_setup_flag = 'Y' THEN
103 RETURN TRUE;
104 ELSE
105 RETURN FALSE;
106 END IF;
107
108 EXCEPTION
109
110 WHEN OTHERS THEN
111
112 IF PG_DEBUG in ('Y','C') THEN
113 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CHECK_TM_DEFAULT_SETUP');
114 END IF;
115 RETURN FALSE;
116
117 END CHECK_TM_DEFAULT_SETUP;
118
119
120 /*========================================================================
121 | PUBLIC PROCEDURE claim_creation
122 |
123 | DESCRIPTION
124 | Procedure to create a deduction claim in Trade Management and update
125 | receivable applications, appropriately with Deduction ID, deduction
126 | number and customer reason code. Handles Non Trx realted claims
127 | and On Transaction related claims (short pays).
128 | This procedure is initiated by the Post Batch process.
129 |
130 | CALLED FROM PROCEDURES/FUNCTIONS
131 | arccbp.lpc (Postbatch)
132 |
133 | CALLS PROCEDURES/FUNCTIONS
134 | arp_process_application.create_claim
135 | app_exception.invalid_argument
136 |
137 | PARAMETERS
138 | IN : p_request_id
139 | p_matched_claim_creation_flag
140 | p_matched_claim_excl_cm_flag
141 | OUT : x_return_status
142 |
143 | KNOWN ISSUES
144 | This procedure will not create Cross Currency Claims.
145 |
146 | NOTES
147 | This procedure should only do claim creation for those records
148 | which have been created by postbatch during this request id,
149 | Since the receipt api is called during the postbatch process, it
150 | is possible that the receipt api will create 'OTHER ACC' (Claim
151 | Investigation Applications), however we do not want to create
152 | a claim for those records. To deal with this, we are explicitly
153 | looking for claims created with an application rule = 90 (ie.
154 | OTHER ACC records which have come from postbatch)
155 |
156 | MODIFICATION HISTORY
157 | Date Author Description of Changes
158 | 19-DEC-2002 cthangai Created
159 | 07-FEB-2003 cthangai Added parameters customer_reason,
160 | x_claim_reason_name to the routine call
161 | arp_process_application.create_claim
162 | Also modified the cursors to fetch RA info
163 | and associated composite data structures
164 | 17-FEB-2003 cthangai Added qualifier to exclude claim processing
165 | for class = 'PMT'
166 | 24-FEB-2003 cthangai Qualified cursor get_ra_info by request_id
167 | 26-FEB-2003 cthangai Pass amount_applied to TM for claim creation
168 | 27-FEB-2003 cthangai Added OUT parameter x_return_status
169 | 03-MAR-2003 cthangai Qualified the update that stamps TRX based claims
170 | with claim info by applied_payment_schedule_id
171 | and applied_customer_trx_id
172 | Qaulified the get_trx_app_info cursor by request_id
173 | 05-MAR-2003 cthangai Fail Recover - Full recover when the initial UNAPP
174 | amount_applied is less than the sum of
175 | amount applied for the paired UNAPP records
176 | else partial recover.
177 | 14-OCT-2005 jbeckett Bug 4565758 - AR/TM legal entity uptake
178 *=======================================================================*/
179 PROCEDURE claim_creation
180 (p_request_id IN ar_receivable_applications.request_id%TYPE DEFAULT NULL
181 ,p_matched_claim_creation_flag IN ar_system_parameters.matched_claim_creation_flag%TYPE
182 ,p_matched_claim_excl_cm_flag IN ar_system_parameters.matched_claim_excl_cm_flag%TYPE
183 ,x_return_status OUT NOCOPY VARCHAR2
184 )IS
185
186 --Primary cursor for processing Non TRX based claims
187 CURSOR get_claim_rec IS
188 SELECT
189 ra.rowid
190 ,ra.receivable_application_id
191 ,ra.amount_applied
192 ,ra.payment_schedule_id
193 ,ra.applied_payment_schedule_id
194 ,ra.applied_customer_trx_id
195 ,ra.comments
196 ,ra.attribute_category
197 ,ra.attribute1
198 ,ra.attribute2
199 ,ra.attribute3
200 ,ra.attribute4
201 ,ra.attribute5
202 ,ra.attribute6
203 ,ra.attribute7
204 ,ra.attribute8
205 ,ra.attribute9
206 ,ra.attribute10
207 ,ra.attribute11
208 ,ra.attribute12
209 ,ra.attribute13
210 ,ra.attribute14
211 ,ra.attribute15
212 ,ra.application_ref_num
213 ,ra.secondary_application_ref_id
214 ,ra.application_ref_reason
215 ,ra.customer_reason
216 ,ra.customer_reference
217 ,NULL -- x_return_status
218 ,NULL -- x_msg_count
219 ,NULL -- x_msg_data
220 ,NULL -- x_claim_reason_name
221 ,apply_date -- bug 5495310
222 FROM ar_receivable_applications ra
223 WHERE ra.application_ref_type = 'CLAIM'
224 AND ra.status = 'OTHER ACC'
225 AND ra.application_ref_num IS NULL
226 AND ra.application_rule = '90'
227 AND ra.request_id = p_request_id;
228
229
230 --Cursor to fetch RA info based on the applied PS id retreived by the
231 --primary cursor to process TRX based claims
232 CURSOR get_ra_info (
233 p_trx_ps_id ar_receivable_applications.applied_payment_schedule_id%type
234 ) IS
235 SELECT
236 ra.receivable_application_id
237 ,ra.amount_applied
238 ,ra.payment_schedule_id
239 ,ra.applied_payment_schedule_id
240 ,ra.applied_customer_trx_id
241 ,ra.comments
242 ,ra.attribute_category
243 ,ra.attribute1
244 ,ra.attribute2
245 ,ra.attribute3
246 ,ra.attribute4
247 ,ra.attribute5
248 ,ra.attribute6
249 ,ra.attribute7
250 ,ra.attribute8
251 ,ra.attribute9
252 ,ra.attribute10
253 ,ra.attribute11
254 ,ra.attribute12
255 ,ra.attribute13
256 ,ra.attribute14
257 ,ra.attribute15
258 ,ra.application_ref_num
259 ,ra.secondary_application_ref_id
260 ,ra.application_ref_reason
261 ,ra.customer_reason
262 ,ra.customer_reference
263 ,NULL -- x_return_status
264 ,NULL -- x_msg_count
265 ,NULL -- x_msg_data
266 ,NULL -- x_claim_reason_name
267 FROM ar_receivable_applications ra
268 WHERE ra.applied_payment_schedule_id = p_trx_ps_id
269 AND ra.request_id = p_request_id
270 AND ra.display = 'Y'
271 AND ra.receivable_application_id = (
272 select max(ra1.receivable_application_id)
273 from ar_receivable_applications ra1
274 where ra1.applied_payment_schedule_id
275 = ra.applied_payment_schedule_id);
276
277 --Primary cursor for processing TRX based claims
278 CURSOR get_ra_rec IS
279 SELECT distinct ra.applied_payment_schedule_id
280 FROM ar_receivable_applications ra
281 WHERE ra.applied_payment_schedule_id NOT IN (-4,-1)
282 AND ra.status = 'APP'
283 AND ra.display = 'Y'
284 AND ra.request_id = p_request_id;
285
286 --Get TRX based info from ra_customer_trx and ar_payment_schedules
287 --Exclude CLASS=PMT
288 CURSOR get_ps_trx_info
289 (p_trx_ps_id ar_receivable_applications.applied_payment_schedule_id%type
290 ) IS
291 SELECT ct.customer_trx_id --customer_trx_id
292 ,ct.trx_number --trx_number
293 ,ct.cust_trx_type_id --trx_type_id
294 ,ct.invoice_currency_code --currency_code
295 ,ct.exchange_rate_type --exchange_rate_type
296 ,ct.exchange_date --exchange_date
297 ,ct.exchange_rate --exchange_rate
298 ,ct.bill_to_customer_id --customer_id
299 ,ct.bill_to_site_use_id --bill_to_site_use_id
300 ,ct.ship_to_site_use_id --ship_to_site_use_id
301 ,ct.primary_salesrep_id --salesrep_id
302 ,ps.amount_due_remaining --amount_due_remaining
303 ,ps.amount_due_original --amount_due_original
304 ,ps.class --class
305 ,ps.active_claim_flag --active_claim_flag
306 ,ct.legal_entity_id
307 FROM ra_customer_trx ct
308 ,ar_payment_schedules ps
309 WHERE ct.customer_trx_id = ps.customer_trx_id
310 AND ps.payment_schedule_id = p_trx_ps_id
311 AND ps.class <> 'PMT';
312
313 --Fetch receipt info using payment schedule id of the receipt
314 CURSOR get_receipt_info (
315 p_receipt_ps_id ar_receivable_applications.payment_schedule_id%type
316 ) IS
317 SELECT ps.cash_receipt_id --cash_receipt_id
318 ,cr.receipt_number --receipt_number
319 ,cr.currency_code --currency_code
320 ,cr.exchange_rate_type --exchange_rate_type
321 ,cr.exchange_date --exchange_date
322 ,cr.exchange_rate --exchange_rate
323 ,cr.pay_from_customer --customer_id
324 ,cr.customer_site_use_id --bill_to_site_use_id
325 ,NULL --ship_to_site_use_id
326 ,cr.legal_entity_id
327 FROM ar_payment_schedules ps
328 ,ar_cash_receipts cr
329 ,ar_cash_receipt_history crh
330 WHERE ps.payment_schedule_id = p_receipt_ps_id
331 AND cr.cash_receipt_id = ps.cash_receipt_id
332 AND crh.cash_receipt_id = cr.cash_receipt_id
333 AND crh.current_record_flag = 'Y';
334
335 --Fetch receipt info using applied payment schedule id of the trx
336 CURSOR get_receipt_num (
337 p_receipt_ps_id ar_receivable_applications.applied_payment_schedule_id%type
338 ) IS
339 SELECT ps.cash_receipt_id --cash_receipt_id
340 ,cr.receipt_number --receipt_number
341 FROM ar_payment_schedules ps
342 ,ar_cash_receipts cr
343 WHERE ps.payment_schedule_id = p_receipt_ps_id
344 AND cr.cash_receipt_id = ps.cash_receipt_id;
345
346 --Fetch Receivable application info required for subsequent receipt application
347 CURSOR get_trx_app_info
348 ( p_trx_ps_id ar_receivable_applications.applied_payment_schedule_id%type
349 ) IS
350 SELECT SUM(ra.amount_applied), MAX(ra.apply_date)
351 FROM ar_receivable_applications ra
352 WHERE ra.applied_payment_schedule_id = p_trx_ps_id
353 AND ra.request_id = p_request_id
354 AND ra.status = 'APP'
355 AND ra.display = 'Y';
356
357 -- Fetch the amount_applied from the initial UNAPP record for the cash receipt
358 CURSOR unapp_amt_rec
359 ( p_cr_id ar_receivable_applications.cash_receipt_id%type
360 ) IS
361 select ra.amount_applied
362 from ar_receivable_applications ra
363 ,ar_distributions ard
364 where ra.cash_receipt_id = p_cr_id
365 and ra.status = 'UNAPP'
366 and nvl(ra.confirmed_flag,'Y') = 'Y'
367 and ra.receivable_application_id = ard.source_id
368 and ard.source_table = 'RA'
369 and ard.source_id_secondary IS NULL;
370
371 -- Fetch the amount_applied from the Paired UNAPP records for the cash receipt
372 CURSOR pair_unapp_amt_rec
373 ( p_cr_id ar_receivable_applications.cash_receipt_id%type
374 ) IS
375 select sum(ra.amount_applied)
376 from ar_receivable_applications ra
377 ,ar_distributions ard
378 where ra.cash_receipt_id = p_cr_id
379 and ra.status = 'UNAPP'
380 and nvl(ra.confirmed_flag,'Y') = 'Y'
381 and ra.receivable_application_id = ard.source_id
382 and ard.source_table = 'RA'
383 and ard.source_id_secondary IS NOT NULL;
384
385 -- Fetch rapp_id for all application for a full claim creation failure recovery
386 CURSOR fail_rec_rapp_id
387 ( p_cr_id ar_receivable_applications.cash_receipt_id%type
388 ) IS
389 select ra.receivable_application_id
390 from ar_receivable_applications ra
391 where ra.cash_receipt_id = p_cr_id
392 and ra.status <> 'UNAPP'
393 and nvl(ra.confirmed_flag,'Y') = 'Y';
394
395 TYPE ra_rec_type IS RECORD (
396 l_rowid DBMS_SQL.VARCHAR2_TABLE
397 ,receivable_application_id DBMS_SQL.NUMBER_TABLE
398 ,amount_applied DBMS_SQL.NUMBER_TABLE
399 ,payment_schedule_id DBMS_SQL.NUMBER_TABLE
400 ,applied_payment_schedule_id DBMS_SQL.NUMBER_TABLE
401 ,applied_customer_trx_id DBMS_SQL.NUMBER_TABLE
402 ,comments DBMS_SQL.VARCHAR2_TABLE
403 ,attribute_category DBMS_SQL.VARCHAR2_TABLE
404 ,attribute1 DBMS_SQL.VARCHAR2_TABLE
405 ,attribute2 DBMS_SQL.VARCHAR2_TABLE
406 ,attribute3 DBMS_SQL.VARCHAR2_TABLE
407 ,attribute4 DBMS_SQL.VARCHAR2_TABLE
408 ,attribute5 DBMS_SQL.VARCHAR2_TABLE
409 ,attribute6 DBMS_SQL.VARCHAR2_TABLE
410 ,attribute7 DBMS_SQL.VARCHAR2_TABLE
411 ,attribute8 DBMS_SQL.VARCHAR2_TABLE
412 ,attribute9 DBMS_SQL.VARCHAR2_TABLE
413 ,attribute10 DBMS_SQL.VARCHAR2_TABLE
414 ,attribute11 DBMS_SQL.VARCHAR2_TABLE
415 ,attribute12 DBMS_SQL.VARCHAR2_TABLE
416 ,attribute13 DBMS_SQL.VARCHAR2_TABLE
417 ,attribute14 DBMS_SQL.VARCHAR2_TABLE
418 ,attribute15 DBMS_SQL.VARCHAR2_TABLE
419 ,application_ref_num DBMS_SQL.VARCHAR2_TABLE
420 ,secondary_application_ref_id DBMS_SQL.NUMBER_TABLE
421 ,application_ref_reason DBMS_SQL.VARCHAR2_TABLE
422 ,customer_reason DBMS_SQL.VARCHAR2_TABLE
423 ,customer_reference DBMS_SQL.VARCHAR2_TABLE
424 ,return_status DBMS_SQL.VARCHAR2_TABLE
425 ,msg_count DBMS_SQL.NUMBER_TABLE
426 ,msg_data DBMS_SQL.VARCHAR2_TABLE
427 ,claim_reason_name DBMS_SQL.VARCHAR2_TABLE
428 ,apply_date DBMS_SQL.DATE_TABLE --bug 5495310
429 );
430 claim_tbl ra_rec_type;
431 claim_tbl_null ra_rec_type;
432
433 TYPE ra_trx_rec_type IS RECORD (
434 applied_payment_schedule_id DBMS_SQL.NUMBER_TABLE
435 );
436 ra_tbl ra_trx_rec_type;
437 ra_tbl_null ra_trx_rec_type;
438
439
440 TYPE ra_claim_rec_type IS RECORD (
441 receivable_application_id ar_receivable_applications.receivable_application_id%TYPE
442 ,amount_applied ar_receivable_applications.amount_applied%TYPE
443 ,payment_schedule_id ar_receivable_applications.payment_schedule_id%TYPE
444 ,applied_payment_schedule_id ar_receivable_applications.applied_payment_schedule_id%TYPE
445 ,applied_customer_trx_id ar_receivable_applications.applied_customer_trx_id%TYPE
446 ,comments ar_receivable_applications.comments%TYPE
447 ,attribute_category ar_receivable_applications.attribute_category%TYPE
448 ,attribute1 ar_receivable_applications.attribute1%TYPE
449 ,attribute2 ar_receivable_applications.attribute2%TYPE
450 ,attribute3 ar_receivable_applications.attribute3%TYPE
451 ,attribute4 ar_receivable_applications.attribute4%TYPE
452 ,attribute5 ar_receivable_applications.attribute5%TYPE
453 ,attribute6 ar_receivable_applications.attribute6%TYPE
454 ,attribute7 ar_receivable_applications.attribute7%TYPE
455 ,attribute8 ar_receivable_applications.attribute8%TYPE
456 ,attribute9 ar_receivable_applications.attribute9%TYPE
457 ,attribute10 ar_receivable_applications.attribute10%TYPE
458 ,attribute11 ar_receivable_applications.attribute11%TYPE
459 ,attribute12 ar_receivable_applications.attribute12%TYPE
460 ,attribute13 ar_receivable_applications.attribute13%TYPE
461 ,attribute14 ar_receivable_applications.attribute14%TYPE
462 ,attribute15 ar_receivable_applications.attribute15%TYPE
463 ,application_ref_num ar_receivable_applications.application_ref_num%TYPE
464 ,secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE
465 ,application_ref_reason ar_receivable_applications.application_ref_reason%TYPE
466 ,customer_reason ar_receivable_applications.customer_reason%TYPE
467 ,customer_reference ar_receivable_applications.customer_reference%TYPE
468 ,return_status VARCHAR2(1)
469 ,msg_count NUMBER
470 ,msg_data VARCHAR2(2000)
471 ,claim_reason_name ar_receivable_applications.application_ref_reason%TYPE
472 );
473 claim_rec ra_claim_rec_type;
474 claim_rec_null ra_claim_rec_type;
475
476 l_currency_code ar_cash_receipts.currency_code%type;
477 l_exchange_rate_type ar_cash_receipts.exchange_rate_type%type;
478 l_exchange_rate ar_cash_receipts.exchange_rate%type;
479 l_exchange_date ar_cash_receipts.exchange_date%type;
480 l_customer_id ar_cash_receipts.pay_from_customer%type;
481 l_bill_to_site_use_id ar_cash_receipts.customer_site_use_id%type;
482 l_ship_to_site_use_id ar_cash_receipts.customer_site_use_id%type;
483 l_receipt_number ar_cash_receipts.receipt_number%type;
484 l_cash_receipt_id ar_cash_receipts.cash_receipt_id%type;
485 l_amount_due_remaining ar_payment_schedules_all.amount_due_remaining%type;
486 l_amount_due_original ar_payment_schedules_all.amount_due_original%type;
487 l_class ar_payment_schedules_all.class%type;
488 l_claim_amount NUMBER;
489 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
490 l_cust_trx_type_id ra_cust_trx_types.cust_trx_type_id%type;
491 l_trx_number ra_customer_trx.trx_number%type;
492 l_salesrep_id ra_customer_trx.primary_salesrep_id%type;
493 l_index NUMBER;
494 l_bulk_fetch_rows NUMBER := 400;
495 l_last_fetch BOOLEAN := FALSE;
496 l_claim_status ar_payment_schedules.active_claim_flag%type;
497 l_return_status VARCHAR2(1); --'E','U' = ERROR ;; 'S'=Success;;
498 l_msg_count NUMBER;
499 l_msg_data VARCHAR2(2000);
500 l_trx_claim_exist VARCHAR2(1); --'Y'=Open Claim in TM;; 'N'=No Claim in TM
501 l_trx_claim_type VARCHAR2(1); --'O'=Over Pay Claim;; 'S'=Short Pay Claim
502 l_applied_date ar_receivable_applications.apply_date%TYPE;
503 l_applied_action_type VARCHAR2(1);
504 l_amount_applied ar_receivable_applications.amount_applied%TYPE;
505 l_applied_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
506 l_applied_receipt_number ar_cash_receipts.receipt_number%TYPE;
507 l_object_version_number NUMBER;
508 l_claim_reason_code_id NUMBER;
509 l_claim_reason_name VARCHAR2(255);
510 l_active_claim_flag ar_payment_schedules.active_claim_flag%TYPE;
511 l_unapp_amt_appl ar_receivable_applications.amount_applied%TYPE;
512 l_sum_pair_unapp_amt_appl ar_receivable_applications.amount_applied%TYPE;
513 l_legal_entity_id NUMBER;
514
515 invalid_param EXCEPTION;
516 skip_pmt_record EXCEPTION;
517 skip_overpay_create EXCEPTION;
518
519 jg_return_status VARCHAR2(1); --'E','U' = ERROR ;; 'S'=Success;;
520
521 BEGIN
522
523 IF PG_DEBUG in ('Y','C') THEN
524 arp_standard.debug('ARP_DEDUCTION.claim_creation()+');
525 END IF;
526
527 IF (p_request_id IS NULL) THEN
528
529 APP_EXCEPTION.INVALID_ARGUMENT
530 ('ARP_DEDUCTION.CLAIM_CREATION'
531 ,'P_REQUEST_ID'
532 ,'NULL'
533 );
534
535 IF PG_DEBUG in ('Y','C') THEN
536 arp_standard.debug('Invalid Argument - Request ID is Null');
537 END IF;
538 RAISE invalid_param;
539
540 END IF;
541
542 ------------------------------------------
543 -- x_return_status initialized with 'S'
544 -- Will be reset with 'E' or 'U' when
545 -- TM API Call return status in 'E' or 'U'
546 ------------------------------------------
547 x_return_status := 'S';
548
549 ------------------------------------------
550 -- Begin Non TRX related claims processing
551 -- 1. Create Claim Investigation
552 ------------------------------------------
553 IF PG_DEBUG in ('Y','C') THEN
554 arp_standard.debug('Open Cursor - get_claim_rec');
555 END IF;
556
557 OPEN get_claim_rec;
558 LOOP -- Loop thru get_claim_rec
559
560 FETCH get_claim_rec BULK COLLECT INTO
561 claim_tbl.l_rowid
562 ,claim_tbl.receivable_application_id
563 ,claim_tbl.amount_applied
564 ,claim_tbl.payment_schedule_id
565 ,claim_tbl.applied_payment_schedule_id
566 ,claim_tbl.applied_customer_trx_id
567 ,claim_tbl.comments
568 ,claim_tbl.attribute_category
569 ,claim_tbl.attribute1
570 ,claim_tbl.attribute2
571 ,claim_tbl.attribute3
572 ,claim_tbl.attribute4
573 ,claim_tbl.attribute5
574 ,claim_tbl.attribute6
575 ,claim_tbl.attribute7
576 ,claim_tbl.attribute8
577 ,claim_tbl.attribute9
578 ,claim_tbl.attribute10
579 ,claim_tbl.attribute11
580 ,claim_tbl.attribute12
581 ,claim_tbl.attribute13
582 ,claim_tbl.attribute14
583 ,claim_tbl.attribute15
584 ,claim_tbl.application_ref_num
585 ,claim_tbl.secondary_application_ref_id
586 ,claim_tbl.application_ref_reason
587 ,claim_tbl.customer_reason
588 ,claim_tbl.customer_reference
589 ,claim_tbl.return_status
590 ,claim_tbl.msg_count
591 ,claim_tbl.msg_data
592 ,claim_tbl.claim_reason_name
593 ,claim_tbl.apply_date
594 LIMIT l_bulk_fetch_rows;
595
596 IF PG_DEBUG in ('Y','C') THEN
597 arp_standard.debug('get_claim_rec cursor RowCount = '||to_char(get_claim_rec%rowcount));
598 END IF;
599
600 IF get_claim_rec%NOTFOUND THEN
601 l_last_fetch := TRUE;
602 END IF;
603
604 IF (claim_tbl.l_rowid.COUNT = 0) AND (l_last_fetch) THEN
605
606 IF PG_DEBUG in ('Y','C') THEN
607 arp_standard.debug('Claim_Tbl.Rowid.Count = 0 and Last Fetch is TRUE');
608 END IF;
609 EXIT;
610
611 END IF;
612
613 FOR i IN claim_tbl.l_rowid.FIRST..claim_tbl.l_rowid.LAST
614 LOOP --Loop thru claim_tbl
615
616 -----------------------------
617 --Initialize Local Variables
618 -----------------------------
619 l_currency_code := NULL;
620 l_exchange_rate_type := NULL;
621 l_exchange_rate := NULL;
622 l_exchange_date := NULL;
623 l_customer_id := NULL;
624 l_bill_to_site_use_id := NULL;
625 l_ship_to_site_use_id := NULL;
626 l_receipt_number := NULL;
627 l_cash_receipt_id := NULL;
628 l_customer_trx_id := NULL;
629 l_cust_trx_type_id := NULL;
630 l_trx_number := NULL;
631 l_salesrep_id := NULL;
632 l_claim_status := NULL;
633 l_unapp_amt_appl := NULL;
634 l_sum_pair_unapp_amt_appl := NULL;
635
636 ---------------------------------------------
637 -- Fetch Receipt Info for Claim Investigation
638 ---------------------------------------------
639 IF PG_DEBUG in ('Y','C') THEN
640 arp_standard.debug('Open get_receipt_info');
641 END IF;
642 OPEN get_receipt_info(claim_tbl.payment_schedule_id(i));
643 FETCH get_receipt_info INTO
644 l_cash_receipt_id
645 ,l_receipt_number
646 ,l_currency_code
647 ,l_exchange_rate_type
648 ,l_exchange_date
649 ,l_exchange_rate
650 ,l_customer_id
651 ,l_bill_to_site_use_id
652 ,l_ship_to_site_use_id
653 ,l_legal_entity_id;
654 CLOSE get_receipt_info;
655
656 IF PG_DEBUG in ('Y','C') THEN
657 arp_standard.debug('p_amount => '||to_char(claim_tbl.amount_applied(i)));
658 arp_standard.debug('p_currency_code => '||l_currency_Code);
659 arp_standard.debug('p_invoice_ps_id => '||to_char(claim_tbl.payment_schedule_id(i)));
660 arp_standard.debug('p_trx_number => '||l_trx_number);
661 arp_standard.debug('p_receipt_number => '||l_receipt_number);
662 END IF;
663
664 ----------------------------------------------
665 -- Call create_claim for Non Trx related claim
666 ----------------------------------------------
667 arp_process_application.create_claim(
668 p_amount => claim_tbl.amount_applied(i)
669 ,p_amount_applied => claim_tbl.amount_applied(i)
670 ,p_currency_code => l_currency_code
671 ,p_exchange_rate_type => l_exchange_rate_type
672 ,p_exchange_rate_date => l_exchange_date
673 ,p_exchange_rate => l_exchange_rate
674 ,p_customer_trx_id => l_customer_trx_id
675 ,p_invoice_ps_id => claim_tbl.payment_schedule_id(i) --Non Trx Based
676 ,p_cust_trx_type_id => l_cust_trx_type_id
677 ,p_trx_number => l_trx_number
678 ,p_cust_account_id => l_customer_id
679 ,p_bill_to_site_id => l_bill_to_site_use_id
680 ,p_ship_to_site_id => l_ship_to_site_use_id
681 ,p_salesrep_id => l_salesrep_id
682 ,p_customer_ref_date => NULL
683 ,p_customer_ref_number => claim_tbl.customer_reference(i)
684 ,p_cash_receipt_id => l_cash_receipt_id
685 ,p_receipt_number => l_receipt_number
686 ,p_reason_id => to_number(claim_tbl.application_ref_reason(i))
687 ,p_customer_reason => claim_tbl.customer_reason(i)
688 ,p_comments => claim_tbl.comments(i)
689 ,p_apply_date => claim_tbl.apply_date(i) --bug 5495310
690 ,p_attribute_category => claim_tbl.attribute_category(i)
691 ,p_attribute1 => claim_tbl.attribute1(i)
692 ,p_attribute2 => claim_tbl.attribute2(i)
693 ,p_attribute3 => claim_tbl.attribute3(i)
694 ,p_attribute4 => claim_tbl.attribute4(i)
695 ,p_attribute5 => claim_tbl.attribute5(i)
696 ,p_attribute6 => claim_tbl.attribute6(i)
697 ,p_attribute7 => claim_tbl.attribute7(i)
698 ,p_attribute8 => claim_tbl.attribute8(i)
699 ,p_attribute9 => claim_tbl.attribute9(i)
700 ,p_attribute10 => claim_tbl.attribute10(i)
701 ,p_attribute11 => claim_tbl.attribute11(i)
702 ,p_attribute12 => claim_tbl.attribute12(i)
703 ,p_attribute13 => claim_tbl.attribute13(i)
704 ,p_attribute14 => claim_tbl.attribute14(i)
705 ,p_attribute15 => claim_tbl.attribute15(i)
706 ,x_return_status => claim_tbl.return_status(i)
707 ,x_msg_count => claim_tbl.msg_count(i)
708 ,x_msg_data => claim_tbl.msg_data(i)
709 ,x_claim_id => claim_tbl.secondary_application_ref_id(i)
710 ,x_claim_number => claim_tbl.application_ref_num(i)
711 ,x_claim_reason_name => claim_tbl.claim_reason_name(i)
712 ,p_legal_entity_id => l_legal_entity_id
713 );
714 IF PG_DEBUG in ('Y','C') THEN
715 arp_standard.debug('Short Pay DED # => '||claim_tbl.application_ref_num(i));
716 END IF;
717
718 ---------------------------------------------------------------------------
719 -- Check TM API Call return status. If E or U then initiate Failure recover
720 -- and initiate concurrent log message
721 ---------------------------------------------------------------------------
722 IF claim_tbl.return_status(i) IN ('E','U') THEN
723
724 ----------------------------------------------------------
725 -- Assign the TM API call return status to x_return_status
726 -- For PostBatch (ARCABP) to take appropraite action
727 -- Assign only when the x_return_status is not 'E' or 'U'
728 -- Note : x_return_status is initialized with 'S' before
729 -- processing any claims
730 ----------------------------------------------------------
731 IF (x_return_status = 'S') THEN
732 x_return_status := claim_tbl.return_status(i);
733 END IF;
734
735 l_claim_status := 'N';
736
737 ------------------------------------------------------------------
738 -- Amount applied from initial UNAPP record for the receipt
739 -- Used in the determination of full or partial recovery
740 ------------------------------------------------------------------
741 IF PG_DEBUG in ('Y','C') THEN
742 arp_standard.debug('arp_deduction.claim_creation: Fetch amount_aplied from '
743 ||'first UNAPP record for the receipt - Fail Recover');
744 END IF;
745 OPEN unapp_amt_rec(l_cash_receipt_id);
746 FETCH unapp_amt_rec into l_unapp_amt_appl;
747 CLOSE unapp_amt_rec;
748
749 ------------------------------------------------------------------
750 -- SUM(Amount applied) from the Paired UNAPP records for the receipt
751 -- Used in the determination of full or partial recovery
752 ------------------------------------------------------------------
753 IF PG_DEBUG in ('Y','C') THEN
754 arp_standard.debug('arp_deduction.claim_creation: Fetch Sum amount_aplied '
755 ||'from the paired UNAPP records for the receipt - Fail Recover');
756 END IF;
757 OPEN pair_unapp_amt_rec(l_cash_receipt_id);
758 FETCH pair_unapp_amt_rec into l_sum_pair_unapp_amt_appl;
759 CLOSE pair_unapp_amt_rec;
760
761 --------------------------------------------------------------------
762 -- Recover on Failure - Full or Partial
763 -- if the sum(amount_applied) for the paired UNAPP records is less
764 -- than the amount_applied from the initial UNAPP record then do a
765 -- partial recover else do a full recover
766 --------------------------------------------------------------------
767 IF l_unapp_amt_appl > l_sum_pair_unapp_amt_appl THEN
768
769 -------------------------------------------------------------
770 -- Claim creation in TM failed. Initiate Partial recovery process
771 -------------------------------------------------------------
772 IF PG_DEBUG in ('Y','C') THEN
773 arp_standard.debug('arp_deduction.claim_creation: Fail Recover in Partial');
774 END IF;
775 arp_deduction.claim_create_fail_recover
776 (p_rapp_id => claim_tbl.receivable_application_id(i)
777 ,p_cr_id => l_cash_receipt_id
778 );
779
780 ELSE -- Full Reversal
781
782 IF PG_DEBUG in ('Y','C') THEN
783 arp_standard.debug('arp_deduction.claim_creation: Fail Recover in FULL');
784 END IF;
785 FOR l_fail_rec in fail_rec_rapp_id(l_cash_receipt_id)
786 LOOP
787
788 -------------------------------------------------------------
789 -- Claim creation in TM failed. Initiate full recovery process
790 -------------------------------------------------------------
791 arp_deduction.claim_create_fail_recover
792 (p_rapp_id => l_fail_rec.receivable_application_id
793 ,p_cr_id => l_cash_receipt_id
794 );
795
796 END LOOP; -- FOR l_fail_rec in fail_rec_appi_id(l_cash_receipt_id)
797
798 /*-----------------------------------------+
799 | Initialize return status to SUCCESS |
800 +-----------------------------------------*/
801 jg_return_status := FND_API.G_RET_STS_SUCCESS;
802
803 /* in the case of a full reversal, we have to also
804 if the localization is installed rollback the
805 interest adjustments. */
806
807 jg_ar_receivable_applications.delete_interest_adjustment(
808 p_cash_receipt_id => l_cash_receipt_id,
809 x_return_status => jg_return_status);
810
811 IF jg_return_status <> FND_API.G_RET_STS_SUCCESS then
812 /* print an error message */
813 arp_standard.debug('Error from jg_ar_receivable_applications.delete_interst_adjustment');
814 END IF;
815
816 END IF; -- IF l_unapp_amt_appl > l_sum_pair_unapp_amt_appl THEN
817
818 --------------------------------------------------
819 -- Write Failure Message to Concurrent Request Log
820 --------------------------------------------------
821 arp_deduction.conc_req_log_msg
822 ('Claim Investigation - Creation Failure '
823 ||' RAPP ID = '||claim_tbl.receivable_application_id(i)
824 ||' Receipt Number = '||l_receipt_number
825 ||' PS ID = '||to_char(claim_tbl.payment_schedule_id(i))
826 ||' Claim Amount = '||to_char(claim_tbl.amount_applied(i))
827 );
828
829 ELSE -- Claim Creation was successful
830
831 l_claim_status := 'Y';
832 ---------------------------------------------------------
833 -- Update AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM appropriately
834 ---------------------------------------------------------
835 arp_deduction.update_claim_create_status
836 (p_ps_id => claim_tbl.payment_schedule_id(i)
837 ,p_claim_status => l_claim_status
838 );
839
840 END IF; --IF claim_tbl.return_status(i) IN ('E','U')
841
842 END LOOP; --Loop thru claim_tbl
843
844 -----------------------------------------------------------------------
845 -- Update Receivable Application with claim number and claim id from TM
846 -----------------------------------------------------------------------
847 IF PG_DEBUG in ('Y','C') THEN
848 arp_standard.debug('Bulk Update RA with DED No, ID and translated oracle reason');
849 END IF;
850 FORALL i IN claim_tbl.l_rowid.FIRST .. claim_tbl.l_rowid.LAST
851 UPDATE ar_receivable_applications
852 SET secondary_application_ref_id = claim_tbl.secondary_application_ref_id(i)
853 ,application_ref_num = claim_tbl.application_ref_num(i)
854 WHERE rowid = claim_tbl.l_rowid(i);
855
856 IF l_last_fetch THEN
857
858 IF PG_DEBUG in ('Y','C') THEN
859 arp_standard.debug('Exit Loop for Processing Non Trx Related claims. Last Fetch');
860 END IF;
861 EXIT;
862
863 END IF; --IF l_last_fetch
864
865 END LOOP; -- Loop thru get_claim_rec
866 CLOSE get_claim_rec; --End of Non Trx related claims processing
867
868 ------------------------------------------------------------------------
869 -- Begin TRX related claims processing
870 -- 1. Short pay claim creation
871 -- 2. Short pay and Over pay, subsequent receipt application
872 -- Note: Subsequent receipt application for Over Pay, is for amount zero
873 -- which results in claim cancel. No Over Pay claim creation
874 ------------------------------------------------------------------------
875 IF PG_DEBUG in ('Y','C') THEN
876 arp_standard.debug('Determine to process Trx Related Claims');
877 END IF;
878
879 IF p_matched_claim_creation_flag = 'Y' THEN
880
881 l_last_fetch := FALSE;
882
883 OPEN get_ra_rec;
884 LOOP -- Loop thru cursor get_ra_rec
885
886 IF PG_DEBUG in ('Y','C') THEN
887 arp_standard.debug('Bulk fetch cursor get_ra_rec into ra_tbl');
888 END IF;
889
890 FETCH get_ra_rec BULK COLLECT INTO
891 ra_tbl.applied_payment_schedule_id
892 LIMIT l_bulk_fetch_rows;
893
894 IF get_ra_rec%NOTFOUND THEN
895
896 IF PG_DEBUG in ('Y','C') THEN
897 arp_standard.debug('LAST Bulk fetch. Set l_last_fetch=TRUE');
898 END IF;
899
900 l_last_fetch := TRUE;
901
902 END IF; --IF get_ra_rec%NOTFOUND
903
904 IF (ra_tbl.applied_payment_schedule_id.COUNT = 0) AND (l_last_fetch) THEN
905
906 IF PG_DEBUG in ('Y','C') THEN
907 arp_standard.debug('Get_RA_Rec: Count = 0 and Last Bulk Fetch');
908 END IF;
909 EXIT;
910
911 END IF; --IF (ra_tbl.applied_payment_schedule_id.COUNT = 0) AND (l_last_fetch)
912
913 FOR i IN ra_tbl.applied_payment_schedule_id.FIRST..ra_tbl.applied_payment_schedule_id.LAST
914 LOOP -- Loop thru ra_tbl.applied_payment_schedule_id
915 BEGIN
916
917 ----------------------------
918 --Initialize Local Variables
919 ----------------------------
920 IF PG_DEBUG in ('Y','C') THEN
921 arp_standard.debug('Processing Trx Related Claim Record = '||to_char(i)||'. Initialize');
922 END IF;
923 l_customer_trx_id := NULL;
924 l_trx_number := NULL;
925 l_cust_trx_type_id := NULL;
926 l_currency_code := NULL;
927 l_exchange_rate_type := NULL;
928 l_exchange_date := NULL;
929 l_exchange_rate := NULL;
930 l_customer_id := NULL;
931 l_bill_to_site_use_id := NULL;
932 l_ship_to_site_use_id := NULL;
933 l_salesrep_id := NULL;
934 l_amount_due_remaining := NULL;
935 l_amount_due_original := NULL;
936 l_class := NULL;
937 l_cash_receipt_id := NULL;
938 l_receipt_number := NULL;
939 l_claim_amount := NULL;
940 claim_rec := claim_rec_null;
941 l_claim_status := NULL;
942 l_return_status := NULL;
943 l_msg_count := NULL;
944 l_msg_data := NULL;
945 l_trx_claim_exist := NULL;
946 l_trx_claim_type := NULL;
947 l_applied_date := NULL;
948 l_applied_action_type := 'A'; --Apply
949 l_amount_applied := NULL;
950 l_applied_receipt_id := NULL;
951 l_applied_receipt_number:= NULL;
952 l_object_version_number := NULL;
953 l_claim_reason_code_id := NULL;
954 l_claim_reason_name := NULL;
955
956 --------------------------------------------------------------------
957 --Fetch PS and Trx info required to determine a short pay/over pay
958 --Continue claims processing only if the payment schedule CLASS<>PMT
959 --------------------------------------------------------------------
960 IF PG_DEBUG in ('Y','C') THEN
961 arp_standard.debug('Open cursor get_ps_rtx_info and fetch');
962 END IF;
963
964 OPEN get_ps_trx_info(ra_tbl.applied_payment_schedule_id(i));
965 FETCH get_ps_trx_info INTO
966 l_customer_trx_id
967 ,l_trx_number
968 ,l_cust_trx_type_id
969 ,l_currency_code
970 ,l_exchange_rate_type
971 ,l_exchange_date
972 ,l_exchange_rate
973 ,l_customer_id
974 ,l_bill_to_site_use_id
975 ,l_ship_to_site_use_id
976 ,l_salesrep_id
977 ,l_amount_due_remaining
978 ,l_amount_due_original
979 ,l_class
980 ,l_active_claim_flag
981 ,l_legal_entity_id;
982
983 -----------------------------------------------------
984 -- If Payment Schedule CLASS=PMT then skip processing
985 -----------------------------------------------------
986 IF get_ps_trx_info%NOTFOUND THEN
987 RAISE skip_pmt_record;
988 END IF;
989
990 CLOSE get_ps_trx_info;
991
992 IF PG_DEBUG in ('Y','C') THEN
993 arp_standard.debug('Amount_due_remaining = '||to_char(l_amount_due_remaining));
994 arp_standard.debug('Amount_due_original = '||to_char(l_amount_due_original));
995 arp_standard.debug('Class = '||l_class);
996 arp_standard.debug('Matched_claim_excl_cm = '||p_matched_claim_excl_cm_flag);
997 END IF;
998
999 ---------------------------------------------------------------
1000 --Determine if amount due remaining for the payment schedule id
1001 --and if claim to be created is short pay
1002 --and class <> 'CM' OR class='CM' and matched_claim_excl_cm_flag='N'
1003 ---------------------------------------------------------------
1004 IF ( ( (l_amount_due_remaining <> 0)
1005 OR
1006 ((l_amount_due_remaining = 0) AND (l_active_claim_flag = 'Y')) )
1007 AND
1008 ( (l_class <> 'CM')
1009 OR
1010 ((l_class = 'CM') AND (p_matched_claim_excl_cm_flag = 'N')) )
1011 ) THEN
1012
1013 --------------------------------------------------------
1014 --Get RA Info Required for Trx Related Claims processing
1015 --------------------------------------------------------
1016 IF PG_DEBUG in ('Y','C') THEN
1017 arp_standard.debug('Open cursor Get_Ra_info for '
1018 ||'PS ID = '||to_char(claim_rec.payment_schedule_id));
1019 END IF;
1020 OPEN get_ra_info(ra_tbl.applied_payment_schedule_id(i));
1021 FETCH get_ra_info INTO
1022 claim_rec.receivable_application_id
1023 ,claim_rec.amount_applied
1024 ,claim_rec.payment_schedule_id
1025 ,claim_rec.applied_payment_schedule_id
1026 ,claim_rec.applied_customer_trx_id
1027 ,claim_rec.comments
1028 ,claim_rec.attribute_category
1029 ,claim_rec.attribute1
1030 ,claim_rec.attribute2
1031 ,claim_rec.attribute3
1032 ,claim_rec.attribute4
1033 ,claim_rec.attribute5
1034 ,claim_rec.attribute6
1035 ,claim_rec.attribute7
1036 ,claim_rec.attribute8
1037 ,claim_rec.attribute9
1038 ,claim_rec.attribute10
1039 ,claim_rec.attribute11
1040 ,claim_rec.attribute12
1041 ,claim_rec.attribute13
1042 ,claim_rec.attribute14
1043 ,claim_rec.attribute15
1044 ,claim_rec.application_ref_num
1045 ,claim_rec.secondary_application_ref_id
1046 ,claim_rec.application_ref_reason
1047 ,claim_rec.customer_reason
1048 ,claim_rec.customer_reference
1049 ,claim_rec.return_status
1050 ,claim_rec.msg_count
1051 ,claim_rec.msg_data
1052 ,claim_rec.claim_reason_name;
1053 CLOSE get_ra_info;
1054
1055 ---------------------------------------------
1056 --Fetch Receipt Info - On TRX Related Claim.
1057 ---------------------------------------------
1058 IF PG_DEBUG in ('Y','C') THEN
1059 arp_standard.debug('Open cursor Get_receipt_num and fetch'
1060 ||'PS ID = '||to_char(claim_rec.payment_schedule_id));
1061 END IF;
1062 OPEN get_receipt_num(claim_rec.payment_schedule_id);
1063 FETCH get_receipt_num INTO
1064 l_cash_receipt_id
1065 ,l_receipt_number;
1066 CLOSE get_receipt_num;
1067 IF PG_DEBUG in ('Y','C') THEN
1068 arp_standard.debug('Close cursor Get_receipt_num and fetch');
1069 END IF;
1070
1071 /*Bug 8584950: OZF procedure verifies l_applied_receipt_id value*/
1072 l_applied_receipt_id := l_cash_receipt_id ;
1073 l_applied_receipt_number := l_receipt_number;
1074
1075 --------------------------------------------------------
1076 -- Claim Amount passed to TM is the amount_due_remaining
1077 --------------------------------------------------------
1078 l_claim_amount := l_amount_due_remaining;
1079
1080 IF PG_DEBUG in ('Y','C') THEN
1081 arp_standard.debug('Claim Amount => '||to_char(l_claim_amount));
1082 arp_standard.debug('p_customer_trx_id => '||to_char(l_customer_trx_id));
1083 arp_standard.debug('p_invoice_ps_id => '||to_char(claim_rec.payment_schedule_id));
1084 arp_standard.debug('p_trx_number => '||l_trx_number);
1085 arp_standard.debug('p_customer_ref_number => '||claim_rec.customer_reference);
1086 arp_standard.debug('p_cash_receipt_id => '||to_char(l_cash_receipt_id));
1087 arp_standard.debug('p_receipt_number => '||l_receipt_number);
1088 arp_standard.debug('p_reason_id => '||claim_rec.application_ref_reason);
1089 END IF;
1090
1091 ------------------------------------------------------------
1092 -- Retreive application info - Amount_Applied, Applied_Date
1093 ------------------------------------------------------------
1094 IF PG_DEBUG in ('Y','C') THEN
1095 arp_standard.debug('Fetch application info for subsequent receipt application');
1096 END IF;
1097 OPEN get_trx_app_info(ra_tbl.applied_payment_schedule_id(i));
1098 FETCH get_trx_app_info INTO l_amount_applied, l_applied_date;
1099 CLOSE get_trx_app_info;
1100
1101 ---------------------------------------------------------------------
1102 -- Check for claim existence in TM
1103 -- Based on this result, call to create_claim API (only short pay) or
1104 -- subsequent receipt API is initiated
1105 ---------------------------------------------------------------------
1106 IF (ozf_claim_grp.check_open_claims(l_customer_trx_id,l_cash_receipt_id)) THEN
1107
1108 l_trx_claim_exist := 'Y'; -- OPEN Claim Exist in TM. Subsequent Receipt App in TM
1109 IF PG_DEBUG in ('Y','C') THEN
1110 arp_standard.debug('Call subsequent receipt API. Open claim exist in TM');
1111 END IF;
1112
1113 ELSE -- Create TRX based Claim
1114 l_trx_claim_exist := 'N'; -- Claim Does Not Exist in TM. Claim Creation in TM
1115 IF PG_DEBUG in ('Y','C') THEN
1116 arp_standard.debug('Initiate Create Claim API. Open claim Does Not exist in TM');
1117 END IF;
1118
1119 END IF; --IF (ozf_claim_grp.check_open_claims(l_customer_trx_id,l_cash_receipt_id))
1120
1121 --------------------------------------------------------------------
1122 -- Determine if short pay OR Over pay. This determination will aid
1123 -- in chosing to perform task specific to Over pay Or Short pay.
1124 -- IF Over Pay then the claim amount is initialized with 0 amount for
1125 -- subsequent receipt application. No claim creation for Over Pay.
1126 --------------------------------------------------------------------
1127 IF (SIGN(l_claim_amount) = SIGN(l_amount_due_original)) THEN
1128 l_trx_claim_type := 'S'; --Short Pay Claim
1129
1130 ELSE -- Over Pay Claim
1131 l_trx_claim_type := 'O'; --Over Pay Claim
1132 l_claim_amount := 0; --Claim Amount for subsequent receipt Application
1133
1134 END IF; --IF (SIGN(l_claim_amount) = SIGN(l_amount_due_original))
1135
1136 IF l_trx_claim_exist = 'Y' THEN
1137 --------------------------------------------------
1138 -- Claim Status is set appropriately for
1139 -- updating AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM_FLAG
1140 --------------------------------------------------
1141 IF (l_claim_amount = 0) THEN -- Claim Cancel
1142 l_claim_status := 'C';
1143 ELSE -- Claim Open
1144 l_claim_status := 'Y';
1145 END IF;
1146
1147 ---------------------------------------------------------
1148 -- Subsequent Receipt Applcation for Open claims in TM
1149 -- Call to this routine handles
1150 -- 1. Claim updates in TM
1151 -- 2. Insert TRX Notes in AR
1152 -- 3. Update amount_in_dispute in AR
1153 ---------------------------------------------------------
1154 IF PG_DEBUG in ('Y','C') THEN
1155 arp_standard.debug('Call Subsequent Receipt API. Open Claim exist in TM');
1156 END IF;
1157 arp_deduction.update_claim
1158 (p_claim_id => claim_rec.secondary_application_ref_id
1159 ,p_claim_number => claim_rec.application_ref_num
1160 ,p_amount => l_claim_amount
1161 ,p_currency_code => l_currency_code
1162 ,p_exchange_rate_type => l_exchange_rate_type
1163 ,p_exchange_rate_date => l_exchange_date
1164 ,p_exchange_rate => l_exchange_rate
1165 ,p_customer_trx_id => l_customer_trx_id
1166 ,p_invoice_ps_id => claim_rec.applied_payment_schedule_id
1167 ,p_cust_trx_type_id => l_cust_trx_type_id
1168 ,p_trx_number => l_trx_number
1169 ,p_cust_account_id => l_customer_id
1170 ,p_bill_to_site_id => l_bill_to_site_use_id
1171 ,p_ship_to_site_id => l_ship_to_site_use_id
1172 ,p_salesrep_id => l_salesrep_id
1173 ,p_customer_ref_date => NULL
1174 ,p_customer_ref_number => claim_rec.customer_reference
1175 ,p_cash_receipt_id => l_cash_receipt_id
1176 ,p_receipt_number => l_receipt_number
1177 ,p_reason_id => to_number(claim_rec.application_ref_reason)
1178 ,p_comments => claim_rec.comments
1179 ,p_attribute_category => claim_rec.attribute_category
1180 ,p_attribute1 => claim_rec.attribute1
1181 ,p_attribute2 => claim_rec.attribute2
1182 ,p_attribute3 => claim_rec.attribute3
1183 ,p_attribute4 => claim_rec.attribute4
1184 ,p_attribute5 => claim_rec.attribute5
1185 ,p_attribute6 => claim_rec.attribute6
1186 ,p_attribute7 => claim_rec.attribute7
1187 ,p_attribute8 => claim_rec.attribute8
1188 ,p_attribute9 => claim_rec.attribute9
1189 ,p_attribute10 => claim_rec.attribute10
1190 ,p_attribute11 => claim_rec.attribute11
1191 ,p_attribute12 => claim_rec.attribute12
1192 ,p_attribute13 => claim_rec.attribute13
1193 ,p_attribute14 => claim_rec.attribute14
1194 ,p_attribute15 => claim_rec.attribute15
1195 ,p_applied_date => l_applied_date
1196 ,p_applied_action_type => l_applied_action_type
1197 ,p_amount_applied => l_amount_applied
1198 ,p_applied_receipt_id => l_applied_receipt_id
1199 ,p_applied_receipt_number=> l_applied_receipt_number
1200 ,x_return_status => claim_rec.return_status
1201 ,x_msg_count => claim_rec.msg_count
1202 ,x_msg_data => claim_rec.msg_data
1203 ,x_object_version_number => l_object_version_number
1204 ,x_claim_reason_code_id => l_claim_reason_code_id
1205 ,x_claim_reason_name => l_claim_reason_name
1206 ,x_claim_id => claim_rec.secondary_application_ref_id
1207 ,x_claim_number => claim_rec.application_ref_num
1208 );
1209
1210 IF PG_DEBUG in ('Y','C') THEN
1211 arp_standard.debug('Object Version Number => '||to_char(l_object_version_number));
1212 END IF;
1213
1214 ELSE -- Open Claim does not exist in TM
1215
1216 IF l_trx_claim_type = 'S' THEN --Short Pay Claim Creation
1217 l_claim_status := 'Y';
1218
1219 ---------------------------------------------
1220 -- Call create_claim for short pay deduction
1221 -- Call to this routine handles
1222 -- 1. Claim creation in TM
1223 -- 2. Insert into AR TRX Notes
1224 -- 3. Update amount_in_dispute in AR
1225 ---------------------------------------------
1226 IF PG_DEBUG in ('Y','C') THEN
1227 arp_standard.debug('Call Create Claim API - Short Pay. No Open Claim exist');
1228 END IF;
1229 arp_process_application.create_claim
1230 (p_amount => l_claim_amount
1231 ,p_amount_applied => l_amount_applied
1232 ,p_currency_code => l_currency_code
1233 ,p_exchange_rate_type => l_exchange_rate_type
1234 ,p_exchange_rate_date => l_exchange_date
1235 ,p_exchange_rate => l_exchange_rate
1236 ,p_customer_trx_id => l_customer_trx_id
1237 ,p_invoice_ps_id => claim_rec.applied_payment_schedule_id
1238 ,p_cust_trx_type_id => l_cust_trx_type_id
1239 ,p_trx_number => l_trx_number
1240 ,p_cust_account_id => l_customer_id
1241 ,p_bill_to_site_id => l_bill_to_site_use_id
1242 ,p_ship_to_site_id => l_ship_to_site_use_id
1243 ,p_salesrep_id => l_salesrep_id
1244 ,p_customer_ref_date => NULL
1245 ,p_customer_ref_number => claim_rec.customer_reference
1246 ,p_cash_receipt_id => l_cash_receipt_id
1247 ,p_receipt_number => l_receipt_number
1248 ,p_reason_id => to_number(claim_rec.application_ref_reason)
1249 ,p_customer_reason => claim_rec.customer_reason
1250 ,p_comments => claim_rec.comments
1251 ,p_apply_date => l_applied_date --Bug 5495310
1252 ,p_attribute_category => claim_rec.attribute_category
1253 ,p_attribute1 => claim_rec.attribute1
1254 ,p_attribute2 => claim_rec.attribute2
1255 ,p_attribute3 => claim_rec.attribute3
1256 ,p_attribute4 => claim_rec.attribute4
1257 ,p_attribute5 => claim_rec.attribute5
1258 ,p_attribute6 => claim_rec.attribute6
1259 ,p_attribute7 => claim_rec.attribute7
1260 ,p_attribute8 => claim_rec.attribute8
1261 ,p_attribute9 => claim_rec.attribute9
1262 ,p_attribute10 => claim_rec.attribute10
1263 ,p_attribute11 => claim_rec.attribute11
1264 ,p_attribute12 => claim_rec.attribute12
1265 ,p_attribute13 => claim_rec.attribute13
1266 ,p_attribute14 => claim_rec.attribute14
1267 ,p_attribute15 => claim_rec.attribute15
1268 ,x_return_status => claim_rec.return_status
1269 ,x_msg_count => claim_rec.msg_count
1270 ,x_msg_data => claim_rec.msg_data
1271 ,x_claim_id => claim_rec.secondary_application_ref_id
1272 ,x_claim_number => claim_rec.application_ref_num
1273 ,x_claim_reason_name => claim_rec.claim_reason_name
1274 ,p_legal_entity_id => l_legal_entity_id
1275 );
1276
1277 IF PG_DEBUG in ('Y','C') THEN
1278 arp_standard.debug('Short Pay DED # => '||claim_rec.application_ref_num);
1279 END IF;
1280
1281 ELSE -- Over Pay Claim
1282 IF PG_DEBUG in ('Y','C') THEN
1283 arp_standard.debug('Over Pay condition - Claim will not be created.');
1284 END IF;
1285 RAISE skip_overpay_create;
1286
1287 END IF; --IF l_trx_claim_type = 'S' THEN
1288
1289 END IF; --IF l_trx_claim_exist = 'Y' THEN
1290
1291 ------------------------------------
1292 -- Check TM API Call return status
1293 ------------------------------------
1294 IF claim_rec.return_status = 'S' THEN --TM API Return Status = Success
1295
1296 --------------------------------------------------------------------------
1297 -- Update Payment Schedules with apporpriate claim status in TM
1298 -- AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM = 'C' for subsequent receipt
1299 -- application of Over Pay Claim.
1300 -- AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM = 'Y' for Short Pay Claim Creation
1301 --------------------------------------------------------------------------
1302 IF ( ((l_trx_claim_type = 'S') AND (l_trx_claim_exist = 'N'))
1303 OR ((l_trx_claim_exist = 'Y') AND (l_claim_amount = 0))
1304 ) THEN
1305
1306 IF PG_DEBUG in ('Y','C') THEN
1307 arp_standard.debug('Update PS with active_claim_flag = C');
1308 END IF;
1309 arp_deduction.update_claim_create_status
1310 (p_ps_id => claim_rec.applied_payment_schedule_id
1311 ,p_claim_status => l_claim_status
1312 );
1313
1314 END IF; --IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1315
1316
1317 ---------------------------------------------------------------------------
1318 --Update Receivable Application with Claim No, ID, Rec TRX id, Appl Ref Type
1319 ---------------------------------------------------------------------------
1320 IF ( ((l_trx_claim_type = 'S') AND (l_trx_claim_exist = 'N'))
1321 OR (l_trx_claim_exist = 'Y')
1322 ) THEN
1323
1324 IF PG_DEBUG in ('Y','C') THEN
1325 arp_standard.debug('Update RA with Ded No,Ded ID,Type,receivables_trx_id');
1326 END IF;
1327 UPDATE ar_receivable_applications
1328 SET secondary_application_ref_id = claim_rec.secondary_application_ref_id
1329 ,application_ref_num = claim_rec.application_ref_num
1330 ,application_ref_type = 'CLAIM'
1331 ,receivables_trx_id = ARP_DEDUCTION.GET_RECEIVABLES_TRX_ID(l_cash_receipt_id)
1332 WHERE applied_payment_schedule_id = claim_rec.applied_payment_schedule_id
1333 AND applied_customer_trx_id = claim_rec.applied_customer_trx_id;
1334
1335 END IF; --IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1336
1337 ELSE -- TM API Return_Status = ERROR
1338
1339 ----------------------------------------------------------
1340 -- Assign the TM API call return status to x_return_status
1341 -- For PostBatch (ARCABP) to take appropraite action
1342 -- Assign only when the x_return_status is not 'E' or 'U'
1343 -- Note : x_return_status is initialized with 'S' before
1344 -- processing any claims
1345 ----------------------------------------------------------
1346 IF (x_return_status = 'S') THEN
1347 x_return_status := claim_rec.return_status;
1348 END IF;
1349
1350 l_claim_status := 'N';
1351 IF PG_DEBUG in ('Y','C') THEN
1352 arp_standard.debug('EXCEPTION - TM API : OZF_Claim_GRP ');
1353 END IF;
1354
1355 --------------------------------------------------
1356 -- Write Failure Message to Concurrent Request Log
1357 --------------------------------------------------
1358 IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1359 ---------------------------------------
1360 -- Specific to Short Pay Claim Creation
1361 ---------------------------------------
1362 arp_deduction.conc_req_log_msg
1363 ('Short Pay Claim Creation Failure - '
1364 ||' Receipt Number = '||l_receipt_number
1365 ||' Applied Payment Schedule ID = '
1366 ||to_char(claim_rec.applied_payment_schedule_id)
1367 ||' TRX Number = '||l_trx_number
1368 ||' Claim Amount = '||to_char(l_claim_amount)
1369 );
1370
1371 ELSIF l_trx_claim_exist = 'Y' THEN
1372 ---------------------------------------------
1373 -- Specific to Subsequent Receipt Application
1374 ---------------------------------------------
1375 arp_deduction.conc_req_log_msg
1376 ('Subsequent Receipt Application Failure - '
1377 ||' Receipt Number = '||l_receipt_number
1378 ||' Receipt ID = '||l_cash_receipt_id
1379 ||' Claim ID = '||to_char(claim_rec.secondary_application_ref_id)
1380 ||' Claim Number = '||claim_rec.application_ref_num
1381 ||' Claim Amount = '||to_char(l_claim_amount)
1382 ||' TRX Number = '||l_trx_number
1383 ||' Cust TRX ID = '||to_char(l_customer_trx_id)
1384 ||' Cust TRX Type ID = '||to_char(l_cust_trx_type_id)
1385 ||' Invoice PS ID = '
1386 ||to_char(claim_rec.applied_payment_schedule_id)
1387 );
1388
1389 END IF; --l_trx_claim_type = 'S' AND l_trx_claim_exist = 'Y' THEN
1390
1391 END IF; --IF claim_rec.return_status = 'S' THEN
1392
1393 END IF; --IF l_amount_due_remaining <> 0 And Short Pay Claim And Class qualified
1394
1395 EXCEPTION
1396 WHEN skip_overpay_create THEN
1397
1398 IF PG_DEBUG in ('Y', 'C') THEN
1399 arp_standard.debug('EXCEPTION - Skip_overpay_create : Trx Related - '||
1400 'Over Pay claim doesnot exist in TM.');
1401 END IF;
1402
1403 WHEN skip_pmt_record THEN
1404
1405 IF PG_DEBUG in ('Y', 'C') THEN
1406 arp_standard.debug('EXCEPTION - Skip_PMT_Record : Non Trx Related - '||
1407 'Payment Schedule CLASS=PMT. Skip processing this record.');
1408 END IF;
1409
1410 END;
1411 END LOOP; -- Loop thru ra_tbl.applied_payment_schedule_id
1412
1413 --If Recent Fetch is Last then exit loop
1414 IF l_last_fetch THEN
1415
1416 IF PG_DEBUG in ('Y', 'C') THEN
1417 arp_standard.debug('l_last_fetch=TRUE. Exit Loop for processing Non Trx claims');
1418 END IF;
1419 EXIT;
1420
1421 END IF; --IF l_last_fetch
1422
1423 END LOOP; -- Loop thru cursor get_ra_rec
1424 CLOSE get_ra_rec;
1425
1426 END IF; --IF p_matched_claim_creation_flag = 'Y' THEN
1427
1428 IF PG_DEBUG in ('Y','C') THEN
1429 arp_standard.debug('ARP_DEDUCTION.claim_creation()-');
1430 END IF;
1431
1432 EXCEPTION
1433 WHEN invalid_param THEN
1434
1435 IF PG_DEBUG in ('Y', 'C') THEN
1436 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.CLAIM_CREATION');
1437 END IF;
1438 RAISE;
1439
1440 WHEN OTHERS THEN
1441
1442 IF PG_DEBUG in ('Y', 'C') THEN
1443 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CLAIM_CREATION');
1444 END IF;
1445 RAISE;
1446
1447 END claim_creation;
1448
1449
1450 /*===========================================================================+
1451 | PROCEDURE |
1452 | update_claim |
1453 | |
1454 | DESCRIPTION |
1455 | Calls iClaim group API to update a deduction claim. |
1456 | |
1457 | SCOPE - PUBLIC |
1458 | |
1459 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
1460 | OZF_Claim_GRP.Update_Deduction - Group API to update a claim from AR |
1461 | |
1462 | ARGUMENTS : IN: |
1463 | OUT: X_RETURN_STATUS ('S' for success, 'E' or 'U' for Error |
1464 | |
1465 | RETURNS : NONE |
1466 | |
1467 | |
1468 | MODIFICATION HISTORY |
1469 | CTHANGAI 03-FEB-2003 Created |
1470 | CTHANGAI 27-FEB-2003 Added OUT parameters x_claim_id,x_claim_number |
1471 +===========================================================================*/
1472 PROCEDURE update_claim
1473 ( p_claim_id IN NUMBER
1474 , p_claim_number IN VARCHAR2
1475 , p_amount IN NUMBER
1476 , p_currency_code IN VARCHAR2
1477 , p_exchange_rate_type IN VARCHAR2
1478 , p_exchange_rate_date IN DATE
1479 , p_exchange_rate IN NUMBER
1480 , p_customer_trx_id IN NUMBER
1481 , p_invoice_ps_id IN NUMBER
1482 , p_cust_trx_type_id IN NUMBER
1483 , p_trx_number IN VARCHAR2
1484 , p_cust_account_id IN NUMBER
1485 , p_bill_to_site_id IN NUMBER
1486 , p_ship_to_site_id IN NUMBER
1487 , p_salesrep_id IN NUMBER
1488 , p_customer_ref_date IN DATE
1489 , p_customer_ref_number IN VARCHAR2
1490 , p_cash_receipt_id IN NUMBER
1491 , p_receipt_number IN VARCHAR2
1492 , p_reason_id IN NUMBER
1493 , p_comments IN VARCHAR2
1494 , p_attribute_category IN VARCHAR2
1495 , p_attribute1 IN VARCHAR2
1496 , p_attribute2 IN VARCHAR2
1497 , p_attribute3 IN VARCHAR2
1498 , p_attribute4 IN VARCHAR2
1499 , p_attribute5 IN VARCHAR2
1500 , p_attribute6 IN VARCHAR2
1501 , p_attribute7 IN VARCHAR2
1502 , p_attribute8 IN VARCHAR2
1503 , p_attribute9 IN VARCHAR2
1504 , p_attribute10 IN VARCHAR2
1505 , p_attribute11 IN VARCHAR2
1506 , p_attribute12 IN VARCHAR2
1507 , p_attribute13 IN VARCHAR2
1508 , p_attribute14 IN VARCHAR2
1509 , p_attribute15 IN VARCHAR2
1510 , p_applied_date IN DATE
1511 , p_applied_action_type IN VARCHAR2
1512 , p_amount_applied IN NUMBER
1513 , p_applied_receipt_id IN NUMBER
1514 , p_applied_receipt_number IN VARCHAR2
1515 , x_return_status OUT NOCOPY VARCHAR2
1516 , x_msg_count OUT NOCOPY NUMBER
1517 , x_msg_data OUT NOCOPY VARCHAR2
1518 , x_object_version_number OUT NOCOPY NUMBER
1519 , x_claim_reason_code_id OUT NOCOPY NUMBER
1520 , x_claim_reason_name OUT NOCOPY VARCHAR2
1521 , x_claim_id OUT NOCOPY NUMBER
1522 , x_claim_number OUT NOCOPY VARCHAR2
1523 ) IS
1524
1525 l_claim_rec OZF_Claim_GRP.Deduction_Rec_Type;
1526 l_return_status VARCHAR2(1);
1527 l_text VARCHAR2(2000);
1528 l_user_id NUMBER;
1529 l_last_update_login NUMBER;
1530 l_sysdate DATE;
1531 l_note_id NUMBER;
1532
1533 BEGIN
1534
1535 IF PG_DEBUG in ('Y', 'C') THEN
1536 arp_standard.debug('update_action: ' || 'arp_deduction.update_claim()+' );
1537 END IF;
1538
1539 x_return_status := 'S';
1540 l_claim_rec.claim_id := p_claim_id;
1541 l_claim_rec.claim_number := p_claim_number;
1542 l_claim_rec.claim_type_id := NULL;
1543 l_claim_rec.claim_date := NULL;
1544 l_claim_rec.due_date := NULL;
1545 l_claim_rec.amount := p_amount;
1546 l_claim_rec.currency_code := p_currency_code;
1547 l_claim_rec.exchange_rate_type := p_exchange_rate_type;
1548 l_claim_rec.exchange_rate_date := p_exchange_rate_date;
1549 l_claim_rec.exchange_rate := p_exchange_rate;
1550 l_claim_rec.set_of_books_id := arp_global.set_of_books_id;
1551 l_claim_rec.source_object_id := p_customer_trx_id;
1552 l_claim_rec.source_object_type_id := p_cust_trx_type_id;
1553 l_claim_rec.source_object_class := 'INVOICE';
1554 l_claim_rec.source_object_number := p_trx_number;
1555 l_claim_rec.cust_account_id := p_cust_account_id;
1556 l_claim_rec.cust_billto_acct_site_id := p_bill_to_site_id;
1557 l_claim_rec.cust_shipto_acct_site_id := p_ship_to_site_id;
1558 l_claim_rec.sales_rep_id := p_salesrep_id;
1559 l_claim_rec.reason_code_id := p_reason_id;
1560 l_claim_rec.customer_ref_date := p_customer_ref_date;
1561 l_claim_rec.customer_ref_number := p_customer_ref_number;
1562 l_claim_rec.receipt_id := p_cash_receipt_id;
1563 l_claim_rec.receipt_number := p_receipt_number;
1564 l_claim_rec.comments := p_comments;
1565 l_claim_rec.deduction_attribute_category := p_attribute_category;
1566 l_claim_rec.deduction_attribute1 := p_attribute1;
1567 l_claim_rec.deduction_attribute2 := p_attribute2;
1568 l_claim_rec.deduction_attribute3 := p_attribute3;
1569 l_claim_rec.deduction_attribute4 := p_attribute4;
1570 l_claim_rec.deduction_attribute5 := p_attribute5;
1571 l_claim_rec.deduction_attribute6 := p_attribute6;
1572 l_claim_rec.deduction_attribute7 := p_attribute7;
1573 l_claim_rec.deduction_attribute8 := p_attribute8;
1574 l_claim_rec.deduction_attribute9 := p_attribute9;
1575 l_claim_rec.deduction_attribute10 := p_attribute10;
1576 l_claim_rec.deduction_attribute11 := p_attribute11;
1577 l_claim_rec.deduction_attribute12 := p_attribute12;
1578 l_claim_rec.deduction_attribute13 := p_attribute13;
1579 l_claim_rec.deduction_attribute14 := p_attribute14;
1580 l_claim_rec.deduction_attribute15 := p_attribute15;
1581 l_claim_rec.applied_date := p_applied_date;
1582 l_claim_rec.applied_action_type := p_applied_action_type;
1583 l_claim_rec.amount_applied := p_amount_applied;
1584 l_claim_rec.applied_receipt_id := p_applied_receipt_id;
1585 l_claim_rec.applied_receipt_number := p_applied_receipt_number;
1586
1587 -------------------------------------------------
1588 -- Call TM API for Subsequent Receipt Application
1589 -------------------------------------------------
1590 OZF_Claim_GRP.Update_Deduction
1591 (p_api_version_number => 1.0
1592 ,p_init_msg_list => FND_API.G_TRUE
1593 ,p_commit => FND_API.G_FALSE
1594 ,x_return_status => l_return_status
1595 ,x_msg_count => x_msg_count
1596 ,x_msg_data => x_msg_data
1597 ,p_deduction => l_claim_rec
1598 ,x_object_version_number => x_object_version_number
1599 ,x_claim_reason_code_id => x_claim_reason_code_id
1600 ,x_claim_reason_name => x_claim_reason_name
1601 ,x_claim_id => x_claim_id
1602 ,x_claim_number => x_claim_number
1603 );
1604
1605 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1606
1607 x_return_status := 'S';
1608 IF (p_customer_trx_id IS NOT NULL) AND (p_customer_trx_id > 0) THEN
1609
1610 --------------------
1611 -- Insert Trx Notes
1612 --------------------
1613 IF PG_DEBUG in ('Y', 'C') THEN
1614 arp_standard.debug('insert_trx_notes: arp_deduction.update_claim');
1615 END IF;
1616
1617 l_text := 'RECEIPT_NUM : '||p_receipt_number||' CLAIM_NUM : '
1618 ||p_claim_number||' TRX_NUM : '||p_trx_number;
1619
1620 l_user_id := arp_standard.profile.user_id;
1621 l_last_update_login := arp_standard.profile.last_update_login;
1622 l_sysdate := SYSDATE;
1623
1624 arp_notes_pkg.insert_cover(
1625 p_note_type => 'MAINTAIN',
1626 p_text => l_text,
1627 p_customer_call_id => NULL,
1628 p_customer_call_topic_id => NULL,
1629 p_call_action_id => NULL,
1630 p_customer_trx_id => p_customer_trx_id,
1631 p_note_id => l_note_id,
1632 p_last_updated_by => l_user_id,
1633 p_last_update_date => l_sysdate,
1634 p_last_update_login => l_last_update_login,
1635 p_created_by => l_user_id,
1636 p_creation_date => l_sysdate);
1637
1638 IF PG_DEBUG in ('Y', 'C') THEN
1639 arp_standard.debug('insert_trx_notes: Note ID = '||to_char(l_note_id));
1640 END IF;
1641
1642 --
1643 -- Update TRX Amount in Dispute
1644 --
1645 IF PG_DEBUG in ('Y', 'C') THEN
1646 arp_standard.debug('put_trx_in_dispute: arp_deduction.update_claim');
1647 END IF;
1648 arp_process_application.update_dispute_on_trx
1649 (p_invoice_ps_id
1650 ,'Y' --p_active_claim
1651 ,p_amount_applied
1652 );
1653
1654 END IF; -- IF (p_customer_trx_id IS NOT NULL) AND (p_customer_trx_id > 0)
1655
1656 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1657 x_return_status := 'E';
1658
1659 ELSE --Unidentified Error
1660 x_return_status := 'U';
1661
1662 END IF; --IF l_return_status = FND_API.G_RET_STS_SUCCESS
1663
1664 IF PG_DEBUG in ('Y', 'C') THEN
1665 arp_standard.debug('update_action: arp_deduction.update_claim()-');
1666 END IF;
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 IF PG_DEBUG in ('Y', 'C') THEN
1671 arp_standard.debug('update_action: EXCEPTION: arp_deduction.update_claim');
1672 END IF;
1673 RAISE;
1674
1675 END update_claim;
1676
1677
1678 /*===========================================================================+
1679 | PUBLIC PROCEDURE |
1680 | create_claims_rapp_dist |
1681 | |
1682 | DESCRIPTION |
1683 | Procedure for creating claims related RA and associated Distributions |
1684 | Insert 2 RA rows - One as -ve UNAPP row and the second as 'OTHER ACC' |
1685 | The on-account ACC row is similar to the 'OTHER ACC'. |
1686 | This new procedure is introduced for creating special applications like|
1687 | claim.The RA records for 'ACC' or 'OTHER ACC' are created first along |
1688 | with thier corresponding distributions records. After which the |
1689 | negative UNAPP record is created along with its PAIRED distribution |
1690 | record. |
1691 | |
1692 | SCOPE - PUBLIC |
1693 | |
1694 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
1695 | arp_app_pkg.insert_p - Insert a row into RA table |
1696 | arp_acct_main.Create_Acct_Entry - Insert a row into Distributions table |
1697 | |
1698 | ARGUMENTS : IN: |
1699 | |
1700 | OUT: x_return_status (S=Success; E=Error/Failure |
1701 | |
1702 | RETURNS : NONE |
1703 | |
1704 | NOTES - |
1705 | |
1706 | MODIFICATION HISTORY |
1707 | 13-JAN-03 CTHANGAI Created |
1708 | 21-JAN-03 CTHANGAI Added parameter receivables_trx_id |
1709 | 05-FEB-03 CTHANGAI Removed paramter application_ref_reason |
1710 | 10-FEB-03 CTHANGAI Initialize customer_reference with NULL |
1711 | before creating UNAPP records in |
1712 | ar_receivable_applications |
1713 | 13-FEB-03 CTHANGAI Removed paramter applied_payment_schedule_id |
1714 | 14-FEB-03 CTHANGAI Initialize receivable_trx_id with NULL for ACC |
1715 | 18-FEB-03 CTHANGAI On Exception write message to concurrent log |
1716 | 19-FEB-03 CTHANGAI Defualt program_application_id = 222 |
1717 | 24-FEB-03 CTHANGAI Defualt postable='Y' for Claim Investigation |
1718 | 24-FEB-03 CTHANGAI On_Account_Customer populated only for ACC |
1719 +===========================================================================*/
1720 PROCEDURE create_claims_rapp_dist
1721 (p_cash_receipt_id IN ar_receivable_applications.cash_receipt_id%TYPE
1722 ,p_unapp_ccid IN ar_receivable_applications.code_combination_id%TYPE
1723 ,p_other_acc_ccid IN ar_receivable_applications.code_combination_id%TYPE
1724 ,p_acc_ccid IN ar_receivable_applications.code_combination_id%TYPE
1725 ,p_gl_date IN ar_receivable_applications.gl_date%TYPE
1726 ,p_status IN ar_receivable_applications.status%TYPE
1727 ,p_amount_applied IN ar_receivable_applications.amount_applied%TYPE
1728 ,p_created_by IN ar_receivable_applications.created_by%TYPE
1729 ,p_creation_date IN ar_receivable_applications.creation_date%TYPE
1730 ,p_last_updated_by IN ar_receivable_applications.last_updated_by%TYPE
1731 ,p_program_application_id IN ar_receivable_applications.program_application_id%TYPE
1732 ,p_program_id IN ar_receivable_applications.program_id%TYPE
1733 ,p_request_id IN ar_receivable_applications.request_id%TYPE
1734 ,p_sob_id IN ar_receivable_applications.set_of_books_id%TYPE
1735 ,p_apply_date IN ar_receivable_applications.apply_date%TYPE
1736 ,p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE
1737 ,p_receipt_ps_id IN ar_receivable_applications.payment_schedule_id%TYPE
1738 ,p_unapp_application_rule IN ar_receivable_applications.application_rule%TYPE
1739 ,p_other_application_rule IN ar_receivable_applications.application_rule%TYPE
1740 ,p_acc_application_rule IN ar_receivable_applications.application_rule%TYPE
1741 ,p_on_account_customer IN ar_receivable_applications.on_account_customer%TYPE
1742 ,p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE
1743 ,p_customer_reference IN ar_receivable_applications.customer_reference%TYPE
1744 ,p_customer_reason IN ar_receivable_applications.customer_reason%TYPE
1745 ,p_attribute_category IN ar_receivable_applications.attribute_category%TYPE
1746 ,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
1747 ,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
1748 ,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
1749 ,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
1750 ,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
1751 ,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
1752 ,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
1753 ,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
1754 ,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
1755 ,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
1756 ,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
1757 ,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
1758 ,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
1759 ,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
1760 ,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
1761 ,x_return_status OUT NOCOPY VARCHAR2
1762 ) IS
1763
1764 l_ra_rec ar_receivable_applications%ROWTYPE;
1765 l_prev_app_id ar_receivable_applications.receivable_application_id%TYPE;
1766 l_ae_doc_rec ARP_ACCT_MAIN.ae_doc_rec_type;
1767 l_xla_ev_rec arp_xla_events.xla_events_type;
1768 l_msg_data VARCHAR2(2000);
1769
1770 BEGIN
1771
1772 IF PG_DEBUG in ('Y', 'C') THEN
1773 arp_standard.debug('arp_deduction.create_claims_rapp_dist()+' );
1774 END IF;
1775 x_return_status := FND_API.G_RET_STS_SUCCESS;
1776
1777 IF PG_DEBUG in ('Y', 'C') THEN
1778 arp_standard.debug('p_receipt_ps_id = '||TO_CHAR(p_receipt_ps_id));
1779 arp_standard.debug('p_amount_applied = '||TO_CHAR( p_amount_applied ) );
1780 --arp_standard.debug('p_gl_date = '|| TO_CHAR( p_gl_date ) );
1781 --arp_standard.debug('p_apply_date = '|| TO_CHAR( p_apply_date ) );
1782 END IF;
1783
1784 -- ---------------------------------------------------------------------
1785 -- Prepare for 'ACC' and 'OTHER ACC' record insertion with +ve amount applied
1786 -- applied_customer_trx_id = -1 and display = 'Y'
1787 -- ---------------------------------------------------------------------
1788 l_msg_data := 'Initialize local reccord type with parameter.';
1789 l_ra_rec.cash_receipt_id := p_cash_receipt_id;
1790 l_ra_rec.receivable_application_id := NULL;
1791 l_ra_rec.gl_date := p_gl_date;
1792 l_ra_rec.status := p_status;
1793 l_ra_rec.amount_applied := p_amount_applied;
1794 l_ra_rec.acctd_amount_applied_from := p_amount_applied;
1795 l_ra_rec.created_by := p_created_by;
1796 l_ra_rec.creation_date := p_creation_date;
1797 l_ra_rec.last_updated_by := p_last_updated_by;
1798 l_ra_rec.last_update_date := SYSDATE;
1799 l_ra_rec.program_application_id := 222; --p_program_application_id;
1800 l_ra_rec.program_id := p_program_id;
1801 l_ra_rec.program_update_date := SYSDATE;
1802 l_ra_rec.request_id := p_request_id;
1803 l_ra_rec.display := 'Y';
1804 l_ra_rec.set_of_books_id := p_sob_id;
1805 l_ra_rec.apply_date := p_apply_date;
1806 l_ra_rec.application_type := 'CASH';
1807 l_ra_rec.posting_control_id := -3;
1808 l_ra_rec.ussgl_transaction_code := p_ussgl_transaction_code;
1809 l_ra_rec.payment_schedule_id := p_receipt_ps_id;
1810 l_ra_rec.application_rule := p_unapp_application_rule;
1811 l_ra_rec.applied_customer_trx_id := -1;
1812 l_ra_rec.attribute_category := p_attribute_category;
1813 l_ra_rec.attribute1 := p_attribute1;
1814 l_ra_rec.attribute2 := p_attribute2;
1815 l_ra_rec.attribute3 := p_attribute3;
1816 l_ra_rec.attribute4 := p_attribute4;
1817 l_ra_rec.attribute5 := p_attribute5;
1818 l_ra_rec.attribute6 := p_attribute6;
1819 l_ra_rec.attribute7 := p_attribute7;
1820 l_ra_rec.attribute8 := p_attribute8;
1821 l_ra_rec.attribute9 := p_attribute9;
1822 l_ra_rec.attribute10 := p_attribute10;
1823 l_ra_rec.attribute11 := p_attribute11;
1824 l_ra_rec.attribute12 := p_attribute12;
1825 l_ra_rec.attribute13 := p_attribute13;
1826 l_ra_rec.attribute14 := p_attribute14;
1827 l_ra_rec.attribute15 := p_attribute15;
1828 l_ra_rec.customer_reference := p_customer_reference;
1829 l_ra_rec.customer_reason := p_customer_reason;
1830
1831 ----------------------------------------------------
1832 -- Assign values appropriate to 'ACC' or 'OTHER ACC'
1833 ----------------------------------------------------
1834 IF p_status = 'OTHER ACC' THEN
1835 l_ra_rec.applied_payment_schedule_id := -4;
1836 l_ra_rec.code_combination_id := p_other_acc_ccid;
1837 l_ra_rec.application_ref_type := 'CLAIM';
1838 l_ra_rec.application_rule := p_other_application_rule;
1839 l_ra_rec.receivables_trx_id := p_receivables_trx_id;
1840 l_ra_rec.postable := 'Y';
1841 l_ra_rec.on_account_customer := NULL;
1842 ELSIF p_status = 'ACC' THEN
1843 l_ra_rec.applied_payment_schedule_id := -1;
1844 l_ra_rec.code_combination_id := p_acc_ccid;
1845 l_ra_rec.application_ref_type := NULL;
1846 l_ra_rec.application_rule := p_acc_application_rule;
1847 l_ra_rec.receivables_trx_id := NULL;
1848 l_ra_rec.postable := NULL;
1849 l_ra_rec.on_account_customer := p_on_account_customer;
1850 END IF;
1851
1852 -- ---------------------------------------------------------------------
1853 -- Insert 'OTHER ACC' OR 'ACC' record into AR_RECEIVABLE_APPLICATIONS
1854 -- ---------------------------------------------------------------------
1855 arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
1856
1857 IF l_ra_rec.receivable_application_id IS NOT NULL THEN
1858
1859 arp_standard.debug('Before calling ARP_XLA_EVENTS.create_events....');
1860
1861 l_xla_ev_rec.xla_from_doc_id := l_ra_rec.receivable_application_id;
1862 l_xla_ev_rec.xla_to_doc_id := l_ra_rec.receivable_application_id;
1863
1864 l_xla_ev_rec.xla_mode := 'O';
1865 l_xla_ev_rec.xla_call := 'B';
1866 l_xla_ev_rec.xla_doc_table := 'APP';
1867 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1868
1869 arp_standard.debug('Processessed following RA row :receivable_application_id : '|| l_ra_rec.receivable_application_id);
1870
1871 END IF;
1872
1873 ------------------------------------------------------------------------
1874 -- Replicate MRC data if necessary
1875 ------------------------------------------------------------------------
1876 ar_mrc_engine3.insert_ra_rec_quickcash(
1877 p_rec_app_id => l_ra_rec.receivable_application_id);
1878
1879 -- ---------------------------------------------------------------------
1880 -- Store APP id for PAIRING
1881 -- ---------------------------------------------------------------------
1882 l_prev_app_id := l_ra_rec.receivable_application_id;
1883
1884 -- ---------------------------------------------------------------------
1885 -- Create 'OTHER ACC' OR 'ACC' record accounting in ar_distributions
1886 -- ---------------------------------------------------------------------
1887 l_ae_doc_rec.document_type := 'RECEIPT';
1888 l_ae_doc_rec.document_id := l_ra_rec.cash_receipt_id;
1889 l_ae_doc_rec.accounting_entity_level := 'ONE';
1890 l_ae_doc_rec.source_table := 'RA';
1891 l_ae_doc_rec.source_id := l_ra_rec.receivable_application_id;
1892 l_ae_doc_rec.source_id_old := '';
1893 l_ae_doc_rec.other_flag := '';
1894 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1895
1896 -- ---------------------------------------------------------------------
1897 -- Prepare for 'UNAPP' record insertion with -ve amount applied
1898 -- applied_customer_trx_id and applied_payment_schedule_id is NULL
1899 -- display = 'N'
1900 -- ---------------------------------------------------------------------
1901 l_ra_rec.receivable_application_id := NULL; --Sequence generated while insert
1902 l_ra_rec.applied_customer_trx_id := NULL;
1903 l_ra_rec.display := 'N';
1904 l_ra_rec.on_account_customer := NULL;
1905 l_ra_rec.customer_reference := NULL;
1906 l_ra_rec.customer_reason := NULL;
1907 l_ra_rec.receivables_trx_id := NULL;
1908 l_ra_rec.amount_applied := -p_amount_applied;
1909 l_ra_rec.acctd_amount_applied_from := -p_amount_applied;
1910 l_ra_rec.applied_payment_schedule_id := NULL;
1911 l_ra_rec.code_combination_id := p_unapp_ccid;
1912 l_ra_rec.status := 'UNAPP';
1913 l_ra_rec.application_ref_type := NULL;
1914 l_ra_rec.application_rule := p_unapp_application_rule;
1915
1916 -- ---------------------------------------------------------------------
1917 -- Insert Negative UNAPP record
1918 -- ---------------------------------------------------------------------
1919 arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
1920
1921 IF l_ra_rec.receivable_application_id IS NOT NULL THEN
1922
1923 arp_standard.debug('Before calling ARP_XLA_EVENTS.create_events....');
1924
1925 l_xla_ev_rec.xla_from_doc_id := l_ra_rec.receivable_application_id;
1926 l_xla_ev_rec.xla_to_doc_id := l_ra_rec.receivable_application_id;
1927
1928 l_xla_ev_rec.xla_mode := 'O';
1929 l_xla_ev_rec.xla_call := 'B';
1930 l_xla_ev_rec.xla_doc_table := 'APP';
1931 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1932
1933 arp_standard.debug('Processessed following RA row :receivable_application_id : '|| l_ra_rec.receivable_application_id);
1934
1935 END IF;
1936
1937 ------------------------------------------------------------------------
1938 -- Replicate MRC data if necessary
1939 ------------------------------------------------------------------------
1940 ar_mrc_engine3.create_matching_unapp_records(
1941 p_rec_app_id => l_prev_app_id,
1942 p_rec_unapp_id => l_ra_rec.receivable_application_id);
1943 --
1944
1945 -- ---------------------------------------------------------------------
1946 -- Create paired UNAPP record accounting in ar_distributions
1947 -- ---------------------------------------------------------------------
1948 l_ae_doc_rec.source_id_old := l_prev_app_id;
1949 l_ae_doc_rec.source_id := l_ra_rec.receivable_application_id;
1950 l_ae_doc_rec.other_flag := 'PAIR';
1951 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1952
1953 IF PG_DEBUG in ('Y', 'C') THEN
1954 arp_standard.debug('arp_deduction.create_claims_rapp_dist()-' );
1955 END IF;
1956
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959
1960 IF PG_DEBUG in ('Y', 'C') THEN
1961 arp_standard.debug('EXCEPTION: arp_deduction.create_claims_rapp_dist()-');
1962 END IF;
1963
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 arp_deduction.conc_req_log_msg
1966 ('EXCEPTION: arp_deduction.create_claims_rapp_dist'||
1967 ' STATUS='||p_status||
1968 ' CCID='||to_char(l_ra_rec.code_combination_id)||
1969 ' Cash_Receipt_ID='||to_char(l_ra_rec.cash_receipt_id)||
1970 ' Payment Schedule_ID='||to_char(l_ra_rec.payment_schedule_id)
1971 );
1972 arp_deduction.conc_req_log_msg('SQLERRM='||substr(SQLERRM,1,255));
1973
1974 RAISE;
1975
1976 END create_claims_rapp_dist;
1977
1978
1979 /*========================================================================
1980 | PUBLIC PROCEDURE claim_create_fail_recover
1981 |
1982 | DESCRIPTION
1983 | Procedure to recover from claim creation failure.
1984 | The receivable application records and thier corresponding distribution
1985 | records for the claim are deleted. The payment schedule amounts are
1986 | updated appropriately.
1987 |
1988 | CALLED FROM PROCEDURES/FUNCTIONS
1989 | claim_creation
1990 |
1991 | CALLS PROCEDURES/FUNCTIONS
1992 | arp_app_pkg.delete_p
1993 | arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1994 |
1995 |
1996 | PARAMETERS
1997 | IN :
1998 | p_rec_app_id
1999 | p_receipt_id
2000 |
2001 | OUT :
2002 |
2003 | KNOWN ISSUES
2004 |
2005 | NOTES
2006 |
2007 | MODIFICATION HISTORY
2008 | Date Author Description of Changes
2009 | 17-JAN-2003 cthangai Created
2010 | 05-MAR-2003 cthangai Removed Payment Schedule ID Parameter.
2011 | 01-Apr-2003 Debbie Jancis Added calls to replicate mrc data
2012 *=======================================================================*/
2013 PROCEDURE claim_create_fail_recover
2014 (p_rapp_id IN ar_receivable_applications.receivable_application_id%TYPE
2015 ,p_cr_id IN ar_receivable_applications.cash_receipt_id%TYPE
2016 ) IS
2017
2018 --
2019 -- Cursor to fetch the ('APP' OR 'OTHER ACC') AND 'UNAPP' applications
2020 -- for delete
2021 --
2022 CURSOR rapp_rec IS
2023 select ra.* --'APP' OR 'OTHER ACC'
2024 from ar_receivable_applications ra
2025 where ra.receivable_application_id = p_rapp_id
2026 and nvl(ra.confirmed_flag,'Y') = 'Y'
2027 and exists (select 'x'
2028 from ar_distributions_all ard
2029 where ard.source_table = 'RA'
2030 and ard.source_id = ra.receivable_application_id)
2031 UNION
2032 select ra.* --'UNAPP'
2033 from ar_receivable_applications ra
2034 , ar_distributions ard
2035 where ra.receivable_application_id = ard.SOURCE_ID
2036 and nvl(ra.confirmed_flag,'Y') = 'Y'
2037 and ard.source_table = 'RA'
2038 and ard.source_id_secondary =
2039 ( select ra1.receivable_application_id
2040 from ar_receivable_applications ra1
2041 where ra1.receivable_application_id = p_rapp_id );
2042
2043 l_ae_doc_rec ARP_ACCT_MAIN.ae_doc_rec_type;
2044 ln_unapp_ra_id ar_receivable_applications.receivable_application_id%TYPE;
2045 ln_ra_id ar_receivable_applications.receivable_application_id%TYPE;
2046
2047 BEGIN
2048
2049 IF PG_DEBUG in ('Y', 'C') THEN
2050 arp_standard.debug('arp_deduction.claim_create_fail_recover()+' );
2051 END IF;
2052
2053 IF PG_DEBUG in ('Y', 'C') THEN
2054 arp_standard.debug('p_rec_app_id = '||TO_CHAR(p_rapp_id));
2055 arp_standard.debug('p_receipt_id = '||TO_CHAR(p_cr_id));
2056 END IF;
2057
2058 ------------------------------------------------------------------
2059 -- Loop through the RA ('APP' OR 'OTHER ACC') and UNAPP For Delete
2060 ------------------------------------------------------------------
2061 FOR l_rapp_rec in rapp_rec
2062 LOOP
2063
2064 IF l_rapp_rec.status IN ('APP','OTHER ACC') THEN
2065 ln_ra_id := l_rapp_rec.receivable_application_id;
2066
2067 ELSIF l_rapp_rec.status = 'UNAPP' THEN
2068 ln_unapp_ra_id := l_rapp_rec.receivable_application_id;
2069
2070 END IF;
2071
2072 -------------------------------------------------
2073 --Delete child accounting records associated with
2074 --parent applications for APP
2075 -------------------------------------------------
2076 l_ae_doc_rec.document_type := 'RECEIPT';
2077 l_ae_doc_rec.document_id := l_rapp_rec.cash_receipt_id;
2078 l_ae_doc_rec.accounting_entity_level := 'ONE';
2079 l_ae_doc_rec.source_table := 'RA';
2080 l_ae_doc_rec.source_id := l_rapp_rec.receivable_application_id;
2081 l_ae_doc_rec.source_id_old := '';
2082 l_ae_doc_rec.other_flag := '';
2083 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2084
2085 END LOOP; -- FOR l_rapp_rec in rapp_rec
2086
2087 -------------------------------------------------------------
2088 -- Delete 'APP' OR 'OTHER ACC' Receivable Application record.
2089 -------------------------------------------------------------
2090 arp_app_pkg.delete_p(ln_ra_id);
2091
2092 /*----------------------------------+
2093 | Calling central MRC library |
2094 | for MRC Integration |
2095 +---------------------------------*/
2096
2097 ar_mrc_engine.maintain_mrc_data(
2098 p_event_mode => 'DELETE',
2099 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
2100 p_mode => 'SINGLE',
2101 p_key_value => ln_ra_id);
2102
2103 ------------------------------------------------
2104 -- Delete 'UNAPP' Receivable Application record.
2105 ------------------------------------------------
2106 arp_app_pkg.delete_p(ln_unapp_ra_id);
2107
2108 /*----------------------------------+
2109 | Calling central MRC library |
2110 | for MRC Integration |
2111 +---------------------------------*/
2112
2113 ar_mrc_engine.maintain_mrc_data(
2114 p_event_mode => 'DELETE',
2115 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
2116 p_mode => 'SINGLE',
2117 p_key_value => ln_ra_id);
2118
2119 IF PG_DEBUG in ('Y', 'C') THEN
2120 arp_standard.debug('arp_deduction.claim_create_fail_recover()-' );
2121 END IF;
2122
2123 EXCEPTION
2124
2125 WHEN OTHERS THEN
2126 IF PG_DEBUG IN ('Y', 'C') THEN
2127 arp_standard.debug('EXCEPTION: arp_deduction.claim_create_fail_recover' );
2128 END IF;
2129 RAISE;
2130
2131 END claim_create_fail_recover;
2132
2133
2134 /*========================================================================
2135 | PUBLIC FUNCTION GET_FUNCTIONAL_CURRENCY
2136 |
2137 | DESCRIPTION
2138 | This function is called in the view associated with the LOV in the
2139 | multiple Quickcash screen for the receipt to receipt feature to
2140 | derive the functional currency code
2141 |
2142 | SCOPE - PUBLIC
2143 |
2144 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2145 |
2146 | ARGUMENTS :
2147 | IN : NONE
2148 |
2149 | OUT: NONE
2150 |
2151 | RETURNS : VARCHAR (Functional Currency Code by Set Of Books)
2152 |
2153 |
2154 | NOTES : This should be eventually rellocated into the arp_util package
2155 |
2156 | MODIFICATION HISTORY
2157 | Date Author Description of Changes
2158 | 21-JAN-2003 cthangai Created
2159 | DD-MON-YYYY Name Bug #####, modified amount ..
2160 |
2161 *=======================================================================*/
2162 FUNCTION GET_FUNCTIONAL_CURRENCY RETURN VARCHAR2 IS
2163
2164 l_currency_code gl_sets_of_books.currency_code%type;
2165
2166 BEGIN
2167
2168 SELECT gl.currency_code
2169 INTO l_currency_code
2170 FROM gl_sets_of_books gl
2171 ,ar_system_parameters ar
2172 WHERE gl.set_of_books_id = ar.set_of_books_id;
2173
2174 RETURN l_currency_code;
2175
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178
2179 IF PG_DEBUG in ('Y', 'C') THEN
2180 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_FUNCTIONAL_CURRENCY');
2181 END IF;
2182 RETURN NULL;
2183
2184 END GET_FUNCTIONAL_CURRENCY;
2185
2186
2187 /*========================================================================
2188 | PUBLIC FUNCTION GET_RECEIVABLES_TRX_ID
2189 |
2190 | DESCRIPTION
2191 | This function is called to retreive the receivables_trx_id
2192 |
2193 | SCOPE - PUBLIC
2194 |
2195 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2196 |
2197 | ARGUMENTS :
2198 | IN : p_cash_receipt_id
2199 |
2200 | OUT: NONE
2201 |
2202 | RETURNS : NUMBER (receivable_trx_id associated with the cash_receipt_id)
2203 |
2204 |
2205 | NOTES :
2206 |
2207 | MODIFICATION HISTORY
2208 | Date Author Description of Changes
2209 | 23-JAN-2003 cthangai Created
2210 | DD-MON-YYYY Name Bug #####, modified amount ..
2211 |
2212 *=======================================================================*/
2213 FUNCTION GET_RECEIVABLES_TRX_ID
2214 (p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE) RETURN NUMBER IS
2215
2216 l_receivables_trx_id ar_receivables_trx.receivables_trx_id%TYPE;
2217
2218 BEGIN
2219
2220 IF PG_DEBUG in ('Y', 'C') THEN
2221 arp_standard.debug('arp_deduction.GET_RECEIVABLES_TRX_ID()+' );
2222 END IF;
2223
2224 SELECT rt.receivables_trx_id
2225 INTO l_receivables_trx_id
2226 FROM ar_receivables_trx rt
2227 WHERE rt.receivables_trx_id = (
2228 SELECT rma.claim_receivables_trx_id
2229 FROM ar_receipt_method_accounts rma, ar_cash_receipts cr
2230 WHERE rma.receipt_method_id = cr.receipt_method_id
2231 AND cr.cash_receipt_id = p_cash_receipt_id
2232 AND rma.primary_flag = 'Y' );
2233
2234 IF PG_DEBUG in ('Y', 'C') THEN
2235 arp_standard.debug('arp_deduction.GET_RECEIVABLES_TRX_ID()-' );
2236 END IF;
2237
2238 RETURN l_receivables_trx_id;
2239
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242 IF PG_DEBUG in ('Y', 'C') THEN
2243 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_RECEIVABLES_TRX_ID');
2244 END IF;
2245 RETURN NULL;
2246
2247 END GET_RECEIVABLES_TRX_ID;
2248
2249
2250 /*========================================================================
2251 | PUBLIC PROCEDURE UPDATE_CLAIM_CREATE_STATUS
2252 |
2253 | DESCRIPTION
2254 | This function is called to update ar_payment_schedules,
2255 | active_claim_flag column with the appropriate claim status returned
2256 | from TM
2257 |
2258 | SCOPE - PUBLIC
2259 |
2260 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2261 |
2262 | ARGUMENTS :
2263 | IN : p_ps_id --payment_schedule_id
2264 | p_claim_status --claim status
2265 |
2266 | OUT: NONE
2267 |
2268 | NOTES :
2269 |
2270 | MODIFICATION HISTORY
2271 | Date Author Description of Changes
2272 | 21-JAN-2003 cthangai Update ar_payment_schdeules.active_claim_flag
2273 | based on the claim status in TM
2274 | DD-MON-YYYY Name Bug #####, modified amount ..
2275 |
2276 *=======================================================================*/
2277 PROCEDURE UPDATE_CLAIM_CREATE_STATUS
2278 (p_ps_id IN ar_payment_schedules.payment_schedule_id%type
2279 ,p_claim_status IN ar_payment_schedules.active_claim_flag%type
2280 ) IS
2281
2282 BEGIN
2283
2284 -------------------------
2285 -- Update claim status
2286 -------------------------
2287 UPDATE ar_payment_schedules
2288 SET active_claim_flag = p_claim_status
2289 WHERE payment_schedule_id = p_ps_id;
2290
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 IF PG_DEBUG in ('Y', 'C') THEN
2294 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.UPDATE_CLAIM_CREATE_STATUS');
2295 END IF;
2296 RAISE;
2297
2298 END UPDATE_CLAIM_CREATE_STATUS;
2299
2300
2301 /*========================================================================
2302 | PUBLIC FUNCTION OVERAPPLICATION_INDICATOR
2303 |
2304 | DESCRIPTION
2305 | Function to determine whether amount applied will cause an
2306 | overapplication given the current amount due remaining and
2307 | amount due original.
2308 | A=sign(amount_due_remaining - amount_applied)
2309 | B=sign(amount_due_original)
2310 | If A=-1 and B=+1 or A=+1 and B=-1 then overapplication
2311 | Returns Y else Return N
2312 | CALLED FROM PROCEDURES/FUNCTIONS
2313 | Called from AR_INTERIM_CASH_RECEIPTS_V ,AR_INTERIM_CR_LINES_V
2314 | and Quick Cash Form (ARXRWQRC.fmb)
2315 | CALLS PROCEDURES/FUNCTIONS
2316 |
2317 |
2318 |
2319 | PARAMETERS
2320 | IN:
2321 | P_AMOUNT_DUE_ORIGINAL IN NUMBER
2322 | P_AMOUNT_DUE_REMAININIG IN NUMBER
2323 | P_AMOUNT_APPLIED IN NUMBER
2324 | OUT:
2325 | RETURN Y/N
2326 | KNOWN ISSUES
2327 |
2328 | NOTES
2329 |
2330 | MODIFICATION HISTORY
2331 | Date Author Description of Changes
2332 | 21-JAN-2003 KDhaliwal Created
2333 |
2334 *=======================================================================*/
2335 FUNCTION OVERAPPLICATION_INDICATOR
2336 (P_AMOUNT_DUE_ORIGINAL IN NUMBER
2337 ,P_AMOUNT_DUE_REMAINING IN NUMBER
2338 ,P_AMOUNT_APPLIED IN NUMBER
2339 ) RETURN VARCHAR2 IS
2340
2341 l_return VARCHAR2(1);
2342 ln_balance_sign NUMBER;
2343 ln_amount_due_original_sign NUMBER;
2344
2345 BEGIN
2346
2347 ln_balance_sign := SIGN(P_AMOUNT_DUE_REMAINING - P_AMOUNT_APPLIED);
2348 ln_amount_due_original_sign :=SIGN(P_AMOUNT_DUE_ORIGINAL);
2349
2350 IF (ln_balance_sign=-1 AND ln_amount_due_original_sign=1) THEN
2351 l_return :='Y';
2352
2353 ELSIF (ln_balance_sign=1 AND ln_amount_due_original_sign=-1) THEN
2354 l_return :='Y';
2355
2356 ELSE
2357 l_return :='N';
2358
2359 END IF;
2360
2361 RETURN(l_return);
2362
2363 EXCEPTION
2364 WHEN OTHERS THEN
2365 RAISE;
2366
2367 END OVERAPPLICATION_INDICATOR;
2368
2369
2370 /*========================================================================
2371 | PUBLIC FUNCTION CHECK_APP_VIOLATE
2372 |
2373 | DESCRIPTION
2374 | Function to determine whether amount entered in the screen
2375 | is violating Natural Applicatioon OR violating Over Application OR
2376 | is a valid Natural Application
2377 |
2378 | CALLED FROM PROCEDURES/FUNCTIONS
2379 | Called from WHEN-VALIDATE-ITEM trigger of the AMOUNT field in the
2380 | Multiple QuickCash window as well as the Receipt Application window
2381 |
2382 | CALLS PROCEDURES/FUNCTIONS
2383 |
2384 | PARAMETERS
2385 | IN:
2386 | P_AMOUNT IN NUMBER (amount passed from the form)
2387 | P_RAPP_ID IN NUMBER
2388 | P_CR_ID IN NUMBER
2389 | OUT:
2390 | RETURN ('NATURAL','OVER','NO')
2391 | NATURAL -> Natural Application Violation
2392 | OVER -> Over Application Violation
2393 | NO -> No Violation
2394 |
2395 | KNOWN ISSUES
2396 |
2397 | NOTES
2398 |
2399 | MODIFICATION HISTORY
2400 | Date Author Description of Changes
2401 | 23-JAN-2003 CTHANGAI Created
2402 | 17-FEB-2003 CTHANGAI natural application of the receipt is based on
2403 | to the payment schedule of the receipt
2404 *=======================================================================*/
2405 FUNCTION CHECK_APP_VIOLATE
2406 (p_amount IN ar_receivable_applications.amount_applied%TYPE
2407 ,p_rapp_id IN ar_receivable_applications.receivable_application_id%TYPE
2408 ,p_cr_id IN ar_receivable_applications.cash_receipt_id%TYPE
2409 ) RETURN VARCHAR2 IS
2410
2411 l_return VARCHAR2(10);
2412 ln_amount_applied ar_receivable_applications.amount_applied%TYPE;
2413
2414 invalid_param EXCEPTION;
2415
2416 BEGIN
2417
2418 IF PG_DEBUG in ('Y', 'C') THEN
2419 arp_standard.debug('arp_deduction.CHECK_APP_VIOLATE()+' );
2420 END IF;
2421
2422 IF (p_rapp_id IS NOT NULL) AND (p_cr_id IS NOT NULL) THEN --'On Account'
2423
2424 SELECT amount_applied
2425 INTO ln_amount_applied
2426 FROM ar_receivable_applications
2427 WHERE receivable_application_id = p_rapp_id;
2428
2429 ELSIF (p_rapp_id IS NULL) AND (p_cr_id IS NOT NULL) THEN --'Unapplied'
2430
2431 SELECT sum(amount_applied)
2432 INTO ln_amount_applied
2433 FROM ar_receivable_applications
2434 WHERE cash_receipt_id = p_cr_id
2435 AND status = 'UNAPP';
2436
2437 ELSE --'Raise Invalid Arguments'
2438
2439 APP_EXCEPTION.INVALID_ARGUMENT
2440 ('ARP_DEDUCTION.CHECK_APP_VIOLATE'
2441 ,'p_rapp_id'
2442 ,'NULL'
2443 );
2444
2445 IF PG_DEBUG in ('Y','C') THEN
2446 arp_standard.debug('Invalid Argument - p_rapp_id is Null');
2447 END IF;
2448
2449 l_return := NULL;
2450 RAISE invalid_param;
2451
2452 END IF; -- IF (p_rapp_id IS NOT NULL) AND (p_cr_id IS NOT NULL)
2453
2454 IF ( SIGN(ln_amount_applied * -1) = SIGN(p_amount) ) THEN
2455
2456 IF ( ABS(ln_amount_applied) < ABS(p_amount) ) THEN -- Over Application Violation
2457 l_return := 'OVER';
2458
2459 ELSE -- Natural Application
2460 l_return := 'NO';
2461
2462 END IF;
2463
2464 ELSE --Natural Application Violation
2465 l_return := 'NATURAL';
2466
2467 END IF; -- IF ( SIGN(ln_amount_applied * -1) = SIGN(p_amount) )
2468
2469 IF PG_DEBUG in ('Y', 'C') THEN
2470 arp_standard.debug('arp_deduction.CHECK_APP_VIOLATE()-' );
2471 END IF;
2472
2473 RETURN (l_return);
2474
2475 EXCEPTION
2476 WHEN invalid_param THEN
2477
2478 IF PG_DEBUG in ('Y', 'C') THEN
2479 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.CHECK_APP_VIOLATE');
2480 END IF;
2481 RETURN(l_return);
2482
2483 WHEN OTHERS THEN
2484
2485 IF PG_DEBUG in ('Y', 'C') THEN
2486 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CHECK_APP_VIOLATE');
2487 END IF;
2488 RETURN(l_return);
2489
2490 END CHECK_APP_VIOLATE;
2491
2492
2493 /*========================================================================
2494 | PUBLIC FUNCTION GET_TM_ORACLE_REASON
2495 |
2496 | DESCRIPTION
2497 | This function is called to retrieve the Oracle reason description
2498 | from TM
2499 |
2500 | SCOPE - PUBLIC
2501 |
2502 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2503 |
2504 | ARGUMENTS :
2505 | IN : TM Claim ID
2506 |
2507 | OUT: NONE
2508 |
2509 | RETURNS : VARCHAR (Oracle Reason From TM)
2510 |
2511 |
2512 | NOTES :
2513 |
2514 | MODIFICATION HISTORY
2515 | Date Author Description of Changes
2516 | 12-FEB-2003 cthangai Created
2517 | DD-MON-YYYY Name Bug #####, modified amount ..
2518 |
2519 *=======================================================================*/
2520 FUNCTION GET_TM_ORACLE_REASON
2521 (p_claim_id IN ar_receivable_applications.secondary_application_ref_id%TYPE
2522 ) RETURN VARCHAR2 IS
2523
2524 l_query_string VARCHAR2(2000);
2525 l_tm_oracle_reason VARCHAR2(80);
2526 /* Changed the size of l_tm_oracle_reason for bug 13943561 */
2527
2528 BEGIN
2529
2530 l_query_string :=
2531 ' SELECT rc.name FROM ozf_reason_codes_vl rc, ozf_claims c '||
2532 ' WHERE c.reason_code_id = rc.reason_code_id '||
2533 ' AND c.claim_id = :claim_id ';
2534
2535 BEGIN
2536 EXECUTE IMMEDIATE l_query_string
2537 INTO l_tm_oracle_reason
2538 USING p_claim_id;
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 FND_MESSAGE.set_name('AR','AR_RW_INVALID_CLAIM_ID');
2542 FND_MESSAGE.set_token('CLAIM_ID',p_claim_id);
2543 APP_EXCEPTION.raise_exception;
2544 END;
2545
2546 RETURN l_tm_oracle_reason;
2547
2548 EXCEPTION
2549 WHEN OTHERS THEN
2550
2551 IF PG_DEBUG in ('Y', 'C') THEN
2552 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_TM_ORACLE_REASON');
2553 END IF;
2554 RETURN NULL;
2555
2556 END GET_TM_ORACLE_REASON;
2557
2558
2559 /*========================================================================
2560 | PUBLIC PROCEDURE conc_req_log_msg
2561 |
2562 | DESCRIPTION
2563 | This procedure writes messages to the concurrent request log file
2564 |
2565 | SCOPE - PUBLIC
2566 |
2567 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2568 |
2569 | ARGUMENTS :
2570 | IN : message
2571 |
2572 | OUT: NONE
2573 |
2574 |
2575 | NOTES :
2576 |
2577 | MODIFICATION HISTORY
2578 | Date Author Description of Changes
2579 | 13-FEB-2003 cthangai Created
2580 | DD-MON-YYYY Name Bug #####
2581 |
2582 *=======================================================================*/
2583 PROCEDURE conc_req_log_msg (p_message IN VARCHAR2) IS
2584 BEGIN
2585
2586 fnd_file.put_line(FND_FILE.LOG, p_message);
2587
2588 END conc_req_log_msg;
2589
2590
2591 /*========================================================================
2592 | PUBLIC PROCEDURE conc_req_out_msg
2593 |
2594 | DESCRIPTION
2595 | This procedure writes messages to the concurrent request output file
2596 |
2597 | SCOPE - PUBLIC
2598 |
2599 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2600 |
2601 | ARGUMENTS :
2602 | IN : message
2603 |
2604 | OUT: NONE
2605 |
2606 |
2607 | NOTES :
2608 |
2609 | MODIFICATION HISTORY
2610 | Date Author Description of Changes
2611 | 13-FEB-2003 cthangai Created
2612 | DD-MON-YYYY Name Bug #####
2613 |
2614 *=======================================================================*/
2615 PROCEDURE conc_req_out_msg (p_message IN VARCHAR2) IS
2616 BEGIN
2617
2618 fnd_file.put_line(FND_FILE.OUTPUT, p_message);
2619
2620 END conc_req_out_msg;
2621
2622
2623 /*========================================================================
2624 | PUBLIC PROCEDURE apply_open_receipt_cover
2625 |
2626 | DESCRIPTION
2627 | This Procedure will be called from Postbatch to create receipt
2628 | to receipt applications. It will actually call the Receipt
2629 | Api which will created the rec apps records, distribution records
2630 | updates to payment schedules and the synch up with Trade management
2631 | if necessary.
2632 | This function will write messages to the concurrent request output file
2633 |
2634 | SCOPE - PUBLIC
2635 |
2636 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED
2637 |
2638 | AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT
2639 |
2640 | ARGUMENTS :
2641 | IN : p_cash_receipt_id
2642 | p_applied_payment_schedule_id
2643 | p_open_rec_app_id
2644 | p_amount_applied
2645 | p_attribute_category
2646 | p_attribute1
2647 | p_attribute2
2648 | p_attribute3
2649 | p_attribute4
2650 | p_attribute5
2651 | p_attribute6
2652 | p_attribute7
2653 | p_attribute8
2654 | p_attribute9
2655 | p_attribute10
2656 | p_attribute11
2657 | p_attribute12
2658 | p_attribute13
2659 | p_attribute14
2660 | p_attribute15
2661 |
2662 | OUT: X_RETURN_STAUS (S=success, E=Error, U=Unidentified Error)
2663 | x_receipt_number
2664 | X_APPLY_TYPE (F=Full, P=Partial)
2665 |
2666 | NOTES
2667 | This routine currently does not support receipt to receipt applications
2668 | which are in a currency other then functional currency.
2669 |
2670 | MODIFICATION HISTORY
2671 | Date Author Description of Changes
2672 | 19-FEB-2003 cthangai Created
2673 | Modify get_open_cr_id cursor to include receipt
2674 | number by joining to ar_cash_receipts
2675 | Modify cursor c1_validate to get amount_applied
2676 | Modify cursor c2_validate to get
2677 | sum(amount_applied)
2678 | Amount_applied retreived is used in determining
2679 | Full or Partial payment
2680 | Add OUT parameters x_return_status, x_apply_type
2681 | 03-MAR-2003 Debbie Jancis Added Comments and formatting. Fixed how
2682 | x_apply_type was figuring out whether
2683 | application was full or partial. For a
2684 | receipt to receipt application, the sign
2685 | of the the p_amount_applied variable will
2686 | always be the opposite sign of the
2687 | l_amount_applied value retrieved from the
2688 | receivable apps record of the applied receipt.
2689 | 07-MAR-2003 cthangai Added OUT parameter x_application_ref_num
2690 | for the TM API call to apply_open_receipt
2691 | local variable l_application_ref_num
2692 |
2693 *=======================================================================*/
2694 PROCEDURE apply_open_receipt_cover
2695 (p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2696 p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE
2697 ,p_open_rec_app_id IN
2698 ar_receivable_applications.receivable_application_id%TYPE
2699 ,p_amount_applied IN
2700 ar_receivable_applications.amount_applied%TYPE
2701 ,p_attribute_category IN
2702 ar_receivable_applications.attribute_category%TYPE
2703 ,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
2704 ,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
2705 ,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
2706 ,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
2707 ,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
2708 ,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
2709 ,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
2710 ,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
2711 ,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
2712 ,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
2713 ,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
2714 ,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
2715 ,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
2716 ,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
2717 ,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
2718 ,x_return_status OUT NOCOPY VARCHAR2
2719 ,x_receipt_number OUT NOCOPY ar_cash_receipts.receipt_number%TYPE
2720 ,x_apply_type OUT NOCOPY VARCHAR2
2721 ) IS
2722
2723 --Fetch open_cash_receipt_id
2724 CURSOR get_open_cr_id
2725 (p_applied_ps_id ar_payment_schedules.payment_schedule_id%TYPE) IS
2726 SELECT ps.cash_receipt_id, cr.receipt_number
2727 FROM ar_payment_schedules ps
2728 ,ar_cash_receipts cr
2729 WHERE ps.payment_schedule_id = p_applied_ps_id
2730 AND ps.cash_receipt_id = cr.cash_receipt_id;
2731
2732 --Fetch from receivable application if application is valid
2733 --for receipt to receipt application. Application is 'ACC' or 'OTHER ACC'
2734 CURSOR c1_validate
2735 (p_rapp_id ar_receivable_applications.receivable_application_id%TYPE) IS
2736 SELECT amount_applied --'Y'
2737 FROM ar_receivable_applications
2738 WHERE receivable_application_id = p_rapp_id
2739 AND display = 'Y';
2740
2741 --Fetch from receivable application if application is valid
2742 --for receipt to receipt application. Application is 'UNAPP'
2743 CURSOR c2_validate
2744 (p_cr_id ar_receivable_applications.cash_receipt_id%TYPE) IS
2745 SELECT sum(amount_applied)
2746 FROM ar_receivable_applications
2747 WHERE cash_receipt_id = p_cr_id
2748 AND status = 'UNAPP'
2749 HAVING sum(amount_applied) >= p_amount_applied;
2750
2751 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
2752 l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type;
2753 l_global_attribute_rec_null ar_receipt_api_pub.global_attribute_rec_type;
2754 l_return_status VARCHAR2(1); --'E','U' = ERROR ;; 'S'=Success;;
2755 l_msg_count NUMBER;
2756 l_msg_data VARCHAR2(2000);
2757 l_called_from VARCHAR2(255) := 'ARCABP'; --PostBatch Pro*C
2758 l_process VARCHAR2(1); --'Y'=Process 'N'=Stop Process
2759 l_amount_applied ar_receivable_applications.amount_applied%TYPE;
2760 l_open_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2761 l_open_receipt_number ar_cash_receipts.receipt_number%type;
2762 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
2763 l_receivable_application_id ar_receivable_applications.application_ref_num%TYPE;
2764 l_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
2765 l_acctd_amount_applied_from NUMBER;
2766 l_acctd_amount_applied_to NUMBER;
2767
2768 invalid_param EXCEPTION;
2769 amount_applied_null EXCEPTION;
2770
2771 BEGIN
2772
2773 IF PG_DEBUG in ('Y', 'C') THEN
2774 arp_standard.debug('update_action: arp_deduction.apply_open_receipt_cover()+');
2775 END IF;
2776
2777 ---------------------------------------------------------------
2778 -- Fetch open_cash_receipt_id using applied_payment_schedule_id
2779 ---------------------------------------------------------------
2780 IF (p_applied_payment_schedule_id IS NOT NULL) THEN
2781 OPEN get_open_cr_id (p_applied_payment_schedule_id);
2782 FETCH get_open_cr_id into l_open_cash_receipt_id ,l_open_receipt_number;
2783
2784 IF get_open_cr_id%NOTFOUND THEN
2785 IF PG_DEBUG IN ('Y','C') THEN
2786 arp_standard.debug('No Data Found : Invalid p_applied_payment_schedule_id');
2787 END IF;
2788 CLOSE get_open_cr_id;
2789 RAISE invalid_param;
2790
2791 ELSE
2792 x_receipt_number := l_open_receipt_number;
2793
2794 END IF;
2795
2796 CLOSE get_open_cr_id;
2797
2798 ELSE
2799 APP_EXCEPTION.INVALID_ARGUMENT
2800 ('ARP_DEDUCTION.APPLY_OPEN_RECEIPT_COVER'
2801 ,'P_APPLIED_PAYMENT_SCHEDULE_ID'
2802 ,'NULL'
2803 );
2804
2805 IF PG_DEBUG IN ('Y','C') THEN
2806 arp_standard.debug('Invalid Argument - Applied Payment Schedule ID IS NULL');
2807 END IF;
2808 RAISE invalid_param;
2809
2810 END IF; --IF (p_applied_payment_schedule_id IS NOT NULL)
2811
2812 -------------------------------------
2813 -- Initialize process flag. Y=Process
2814 -------------------------------------
2815 l_process := 'Y';
2816
2817 ------------------------------------------------------------------------------
2818 -- Determine if the receipt api should be called and set l_process='Y' on
2819 -- success. For 'ACC' or 'OTHER ACC', the receivable application must have
2820 -- display='Y'.
2821 -- For 'UNAPP' record, the sum(amount_applied) for the cash receipt must
2822 -- be greater than or equal to the amount being applied to the open receipt
2823 ------------------------------------------------------------------------------
2824 IF (p_open_rec_app_id > 0) AND (l_open_cash_receipt_id IS NOT NULL) THEN
2825
2826 -- 'ACC' OR 'OTHER ACC'
2827 OPEN c1_validate (p_open_rec_app_id);
2828 FETCH c1_validate INTO l_amount_applied;
2829
2830 IF c1_validate%NOTFOUND THEN
2831
2832 x_return_status := FND_API.G_RET_STS_ERROR;
2833 l_process := 'N';
2834
2835 arp_deduction.conc_req_log_msg
2836 ('Failed Validation : display<>Y for the application. No Call will be '
2837 ||' initiated for receipt API - apply_open_receipt'
2838 ||' Receivable Application id = '||to_char(p_open_rec_app_id)
2839 ||' Cash Receipt id = '||to_char(l_open_cash_receipt_id));
2840
2841 END IF;-- IF c1_validate%NOTFOUND
2842
2843 CLOSE c1_validate;
2844
2845 ELSIF (p_open_rec_app_id <= 0) AND (l_open_cash_receipt_id IS NOT NULL) THEN
2846
2847 -- 'UNAPP'
2848 OPEN c2_validate (l_open_cash_receipt_id);
2849 FETCH c2_validate INTO l_amount_applied;
2850
2851 IF c2_validate%NOTFOUND THEN
2852
2853 x_return_status := FND_API.G_RET_STS_ERROR;
2854 l_process := 'N';
2855
2856 arp_deduction.conc_req_log_msg
2857 ('Failed Validation : sum(amount_applied) is less than apply_amount for '
2858 ||' UNAPP. No call will be initiated for receipt API - apply_open_receipt'
2859 ||' Cash Receipt ID = '||to_char(l_open_cash_receipt_id));
2860
2861 END IF; --IF c2_validate%NOTFOUND
2862
2863 CLOSE c2_validate;
2864
2865 END IF; --IF (p_open_rec_app_id IS NOT NULL) AND (l_open_cash_receipt_id IS NOT NULL)
2866
2867 -------------------------------------------------------
2868 -- Determine if l_process='Y' to process call to TM API
2869 -------------------------------------------------------
2870 IF l_process = 'Y' THEN
2871
2872 -----------------------------------------------
2873 -- Determine if apply amount is Partial or Full
2874 -- 'P' for Partial and 'F' for full
2875 -----------------------------------------------
2876 IF l_amount_applied <> -p_amount_applied THEN
2877 x_apply_type := 'P';
2878
2879 ELSIF l_amount_applied = -p_amount_applied THEN
2880 x_apply_type := 'F';
2881
2882 END IF;
2883
2884 -------------------------------------------------------------
2885 -- Initialize the record type variables with the correspoding
2886 -- parameter values
2887 -------------------------------------------------------------
2888 l_attribute_rec.attribute_category := p_attribute_category;
2889 l_attribute_rec.attribute1 := p_attribute1;
2890 l_attribute_rec.attribute2 := p_attribute2;
2891 l_attribute_rec.attribute3 := p_attribute3;
2892 l_attribute_rec.attribute4 := p_attribute4;
2893 l_attribute_rec.attribute5 := p_attribute5;
2894 l_attribute_rec.attribute6 := p_attribute6;
2895 l_attribute_rec.attribute7 := p_attribute7;
2896 l_attribute_rec.attribute8 := p_attribute8;
2897 l_attribute_rec.attribute9 := p_attribute9;
2898 l_attribute_rec.attribute10 := p_attribute10;
2899 l_attribute_rec.attribute11 := p_attribute11;
2900 l_attribute_rec.attribute12 := p_attribute12;
2901 l_attribute_rec.attribute13 := p_attribute13;
2902 l_attribute_rec.attribute14 := p_attribute14;
2903 l_attribute_rec.attribute15 := p_attribute15;
2904 l_global_attribute_rec := l_global_attribute_rec_null;
2905
2906 --------------------------------------------
2907 -- Call to receipt API to Apply Open Receipt
2908 --------------------------------------------
2909 ar_receipt_api_pub.apply_open_receipt
2910 (p_api_version => 1.0
2911 ,p_init_msg_list => FND_API.G_TRUE
2912 ,p_commit => FND_API.G_FALSE
2913 ,x_return_status => l_return_status
2914 ,x_msg_count => l_msg_count
2915 ,x_msg_data => l_msg_data
2916 ,p_cash_receipt_id => p_cash_receipt_id
2917 ,p_applied_payment_schedule_id => p_applied_payment_schedule_id
2918 ,p_open_cash_receipt_id => l_open_cash_receipt_id
2919 ,p_open_receipt_number => l_open_receipt_number
2920 ,p_open_rec_app_id => p_open_rec_app_id
2921 ,p_amount_applied => p_amount_applied
2922 ,p_called_from => l_called_from
2923 ,p_attribute_rec => l_attribute_rec
2924 ,p_global_attribute_rec => l_global_attribute_rec
2925 ,x_application_ref_num => l_application_ref_num
2926 ,x_receivable_application_id => l_receivable_application_id
2927 ,x_applied_rec_app_id => l_applied_rec_app_id
2928 ,x_acctd_amount_applied_from => l_acctd_amount_applied_from
2929 ,x_acctd_amount_applied_to => l_acctd_amount_applied_to
2930 );
2931
2932 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2933 x_return_status := 'S';
2934
2935 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2936 x_return_status := 'E';
2937 arp_deduction.conc_req_log_msg
2938 ('FAILURE - ERROR : Return status = E from API call apply_open_receipt');
2939
2940 ELSE
2941 x_return_status := 'U';
2942 arp_deduction.conc_req_log_msg
2943 ('FAILURE - UNIDENTIFIED : Return Status = U from API call apply_open_receipt');
2944
2945 END IF; --IF l_return_status = FND_API.G_RET_STS_SUCCESS
2946
2947 END IF; --IF l_process = 'Y' THEN
2948
2949 IF PG_DEBUG in ('Y','C') THEN
2950 arp_standard.debug('update_action: ARP_DEDUCTION.apply_open_receipt_cover()-');
2951 END IF;
2952
2953 EXCEPTION
2954 WHEN invalid_param THEN
2955
2956 x_return_status := FND_API.G_RET_STS_ERROR;
2957
2958 IF PG_DEBUG in ('Y','C') THEN
2959 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.apply_open_receipt_cover()-');
2960 END IF;
2961
2962 RAISE;
2963
2964 WHEN OTHERS THEN
2965
2966 x_return_status := FND_API.G_RET_STS_ERROR;
2967
2968 arp_deduction.conc_req_log_msg
2969 ('EXCEPTION - WHEN OTHERS : ARP_DEDUCTION.apply_open_receipt_cover()-');
2970 arp_deduction.conc_req_log_msg('SQLERRM='||substr(SQLERRM,1,255));
2971
2972 RAISE;
2973
2974 END apply_open_receipt_cover;
2975
2976 /*========================================================================
2977 | PUBLIC FUNCTION GET_ACTIVE_CLAIM_FLAG
2978 |
2979 | DESCRIPTION
2980 | This function returns the value of the active claim flag which is
2981 | stored on the Payment Schedule of a transaction. This flag indicates
2982 | whether or not an active claim exists in trade management.
2983 |
2984 | SCOPE - PUBLIC
2985 |
2986 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2987 |
2988 | ARGUMENTS :
2989 | IN : payment_schedule_id
2990 | IN : payment_schedule_id
2991 |
2992 | RETURNS: ACTIVE_CLAIM_FLAG (for that payment schedule id)
2993 |
2994 | KNOWN ISSUES
2995 |
2996 | NOTES :
2997 |
2998 | MODIFICATION HISTORY
2999 | Date Author Description of Changes
3000 | 03-JUN-2003 Debbie Jancis Created
3001 |
3002 *=======================================================================*/
3003 FUNCTION GET_ACTIVE_CLAIM_FLAG(
3004 p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE)
3005 RETURN VARCHAR2 IS
3006
3007 l_active_claim_flag ar_payment_schedules.active_claim_flag%type;
3008
3009 BEGIN
3010
3011 SELECT nvl(active_claim_flag,'N')
3012 INTO l_active_claim_flag
3013 FROM ar_payment_schedules
3014 WHERE payment_schedule_id = p_payment_schedule_id;
3015
3016 RETURN l_active_claim_flag;
3017
3018 EXCEPTION
3019 WHEN OTHERS THEN
3020
3021 IF PG_DEBUG in ('Y', 'C') THEN
3022 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_ACTIVE_CLAIM_FLAG');
3023 END IF;
3024 RETURN NULL;
3025
3026 END GET_ACTIVE_CLAIM_FLAG;
3027
3028
3029
3030
3031
3032 /*========================================================================
3033 | INITIALIZATION SECTION
3034 |
3035 | DESCRIPTION
3036 | Enter a brief description of what this section does.
3037 | ----------------------------------------
3038 | This does the following ......
3039 |
3040 | KNOWN ISSUES
3041 | Enter business functionality which was de-scoped as part of the
3042 | implementation. Ideally this should never be used.
3043 |
3044 | NOTES
3045 | Any interesting aspect of the code in this section
3046 |
3047 | MODIFICATION HISTORY
3048 | Date Author Description of Changes
3049 | DD-MON-YYYY Name Created
3050 | DD-MON-YYYY Name Bug #####, modified amount ..
3051 |
3052 *=======================================================================*/
3053 BEGIN
3054
3055 null;
3056
3057 EXCEPTION
3058 WHEN NO_DATA_FOUND THEN
3059
3060 IF PG_DEBUG in ('Y', 'C') THEN
3061 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.INITIALIZE');
3062 END IF;
3063 RAISE;
3064
3065 WHEN OTHERS THEN
3066
3067 IF PG_DEBUG in ('Y', 'C') THEN
3068 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.INITIALIZE');
3069 END IF;
3070 RAISE;
3071
3072 END ARP_DEDUCTION;