[Home] [Help]
PACKAGE BODY: APPS.ARP_DEDUCTION
Source
1 PACKAGE BODY ARP_DEDUCTION AS
2 /* $Header: ARXLDEDB.pls 120.15.12010000.2 2008/09/15 12:11:05 mgaleti 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 --------------------------------------------------------
1072 -- Claim Amount passed to TM is the amount_due_remaining
1073 --------------------------------------------------------
1074 l_claim_amount := l_amount_due_remaining;
1075
1076 IF PG_DEBUG in ('Y','C') THEN
1077 arp_standard.debug('Claim Amount => '||to_char(l_claim_amount));
1078 arp_standard.debug('p_customer_trx_id => '||to_char(l_customer_trx_id));
1079 arp_standard.debug('p_invoice_ps_id => '||to_char(claim_rec.payment_schedule_id));
1080 arp_standard.debug('p_trx_number => '||l_trx_number);
1081 arp_standard.debug('p_customer_ref_number => '||claim_rec.customer_reference);
1082 arp_standard.debug('p_cash_receipt_id => '||to_char(l_cash_receipt_id));
1083 arp_standard.debug('p_receipt_number => '||l_receipt_number);
1084 arp_standard.debug('p_reason_id => '||claim_rec.application_ref_reason);
1085 END IF;
1086
1087 ------------------------------------------------------------
1088 -- Retreive application info - Amount_Applied, Applied_Date
1089 ------------------------------------------------------------
1090 IF PG_DEBUG in ('Y','C') THEN
1091 arp_standard.debug('Fetch application info for subsequent receipt application');
1092 END IF;
1093 OPEN get_trx_app_info(ra_tbl.applied_payment_schedule_id(i));
1094 FETCH get_trx_app_info INTO l_amount_applied, l_applied_date;
1095 CLOSE get_trx_app_info;
1096
1097 ---------------------------------------------------------------------
1098 -- Check for claim existence in TM
1099 -- Based on this result, call to create_claim API (only short pay) or
1100 -- subsequent receipt API is initiated
1101 ---------------------------------------------------------------------
1102 IF (ozf_claim_grp.check_open_claims(l_customer_trx_id,l_cash_receipt_id)) THEN
1103
1104 l_trx_claim_exist := 'Y'; -- OPEN Claim Exist in TM. Subsequent Receipt App in TM
1105 IF PG_DEBUG in ('Y','C') THEN
1106 arp_standard.debug('Call subsequent receipt API. Open claim exist in TM');
1107 END IF;
1108
1109 ELSE -- Create TRX based Claim
1110 l_trx_claim_exist := 'N'; -- Claim Does Not Exist in TM. Claim Creation in TM
1111 IF PG_DEBUG in ('Y','C') THEN
1112 arp_standard.debug('Initiate Create Claim API. Open claim Does Not exist in TM');
1113 END IF;
1114
1115 END IF; --IF (ozf_claim_grp.check_open_claims(l_customer_trx_id,l_cash_receipt_id))
1116
1117 --------------------------------------------------------------------
1118 -- Determine if short pay OR Over pay. This determination will aid
1119 -- in chosing to perform task specific to Over pay Or Short pay.
1120 -- IF Over Pay then the claim amount is initialized with 0 amount for
1121 -- subsequent receipt application. No claim creation for Over Pay.
1122 --------------------------------------------------------------------
1123 IF (SIGN(l_claim_amount) = SIGN(l_amount_due_original)) THEN
1124 l_trx_claim_type := 'S'; --Short Pay Claim
1125
1126 ELSE -- Over Pay Claim
1127 l_trx_claim_type := 'O'; --Over Pay Claim
1128 l_claim_amount := 0; --Claim Amount for subsequent receipt Application
1129
1130 END IF; --IF (SIGN(l_claim_amount) = SIGN(l_amount_due_original))
1131
1132 IF l_trx_claim_exist = 'Y' THEN
1133 --------------------------------------------------
1134 -- Claim Status is set appropriately for
1135 -- updating AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM_FLAG
1136 --------------------------------------------------
1137 IF (l_claim_amount = 0) THEN -- Claim Cancel
1138 l_claim_status := 'C';
1139 ELSE -- Claim Open
1140 l_claim_status := 'Y';
1141 END IF;
1142
1143 ---------------------------------------------------------
1144 -- Subsequent Receipt Applcation for Open claims in TM
1145 -- Call to this routine handles
1146 -- 1. Claim updates in TM
1147 -- 2. Insert TRX Notes in AR
1148 -- 3. Update amount_in_dispute in AR
1149 ---------------------------------------------------------
1150 IF PG_DEBUG in ('Y','C') THEN
1151 arp_standard.debug('Call Subsequent Receipt API. Open Claim exist in TM');
1152 END IF;
1153 arp_deduction.update_claim
1154 (p_claim_id => claim_rec.secondary_application_ref_id
1155 ,p_claim_number => claim_rec.application_ref_num
1156 ,p_amount => l_claim_amount
1157 ,p_currency_code => l_currency_code
1158 ,p_exchange_rate_type => l_exchange_rate_type
1159 ,p_exchange_rate_date => l_exchange_date
1160 ,p_exchange_rate => l_exchange_rate
1161 ,p_customer_trx_id => l_customer_trx_id
1162 ,p_invoice_ps_id => claim_rec.applied_payment_schedule_id
1163 ,p_cust_trx_type_id => l_cust_trx_type_id
1164 ,p_trx_number => l_trx_number
1165 ,p_cust_account_id => l_customer_id
1166 ,p_bill_to_site_id => l_bill_to_site_use_id
1167 ,p_ship_to_site_id => l_ship_to_site_use_id
1168 ,p_salesrep_id => l_salesrep_id
1169 ,p_customer_ref_date => NULL
1170 ,p_customer_ref_number => claim_rec.customer_reference
1171 ,p_cash_receipt_id => l_cash_receipt_id
1172 ,p_receipt_number => l_receipt_number
1173 ,p_reason_id => to_number(claim_rec.application_ref_reason)
1174 ,p_comments => claim_rec.comments
1175 ,p_attribute_category => claim_rec.attribute_category
1176 ,p_attribute1 => claim_rec.attribute1
1177 ,p_attribute2 => claim_rec.attribute2
1178 ,p_attribute3 => claim_rec.attribute3
1179 ,p_attribute4 => claim_rec.attribute4
1180 ,p_attribute5 => claim_rec.attribute5
1181 ,p_attribute6 => claim_rec.attribute6
1182 ,p_attribute7 => claim_rec.attribute7
1183 ,p_attribute8 => claim_rec.attribute8
1184 ,p_attribute9 => claim_rec.attribute9
1185 ,p_attribute10 => claim_rec.attribute10
1186 ,p_attribute11 => claim_rec.attribute11
1187 ,p_attribute12 => claim_rec.attribute12
1188 ,p_attribute13 => claim_rec.attribute13
1189 ,p_attribute14 => claim_rec.attribute14
1190 ,p_attribute15 => claim_rec.attribute15
1191 ,p_applied_date => l_applied_date
1192 ,p_applied_action_type => l_applied_action_type
1193 ,p_amount_applied => l_amount_applied
1194 ,p_applied_receipt_id => l_applied_receipt_id
1195 ,p_applied_receipt_number=> l_applied_receipt_number
1196 ,x_return_status => claim_rec.return_status
1197 ,x_msg_count => claim_rec.msg_count
1198 ,x_msg_data => claim_rec.msg_data
1199 ,x_object_version_number => l_object_version_number
1200 ,x_claim_reason_code_id => l_claim_reason_code_id
1201 ,x_claim_reason_name => l_claim_reason_name
1202 ,x_claim_id => claim_rec.secondary_application_ref_id
1203 ,x_claim_number => claim_rec.application_ref_num
1204 );
1205
1206 IF PG_DEBUG in ('Y','C') THEN
1207 arp_standard.debug('Object Version Number => '||to_char(l_object_version_number));
1208 END IF;
1209
1210 ELSE -- Open Claim does not exist in TM
1211
1212 IF l_trx_claim_type = 'S' THEN --Short Pay Claim Creation
1213 l_claim_status := 'Y';
1214
1215 ---------------------------------------------
1216 -- Call create_claim for short pay deduction
1217 -- Call to this routine handles
1218 -- 1. Claim creation in TM
1219 -- 2. Insert into AR TRX Notes
1220 -- 3. Update amount_in_dispute in AR
1221 ---------------------------------------------
1222 IF PG_DEBUG in ('Y','C') THEN
1223 arp_standard.debug('Call Create Claim API - Short Pay. No Open Claim exist');
1224 END IF;
1225 arp_process_application.create_claim
1226 (p_amount => l_claim_amount
1227 ,p_amount_applied => l_amount_applied
1228 ,p_currency_code => l_currency_code
1229 ,p_exchange_rate_type => l_exchange_rate_type
1230 ,p_exchange_rate_date => l_exchange_date
1231 ,p_exchange_rate => l_exchange_rate
1232 ,p_customer_trx_id => l_customer_trx_id
1233 ,p_invoice_ps_id => claim_rec.applied_payment_schedule_id
1234 ,p_cust_trx_type_id => l_cust_trx_type_id
1235 ,p_trx_number => l_trx_number
1236 ,p_cust_account_id => l_customer_id
1237 ,p_bill_to_site_id => l_bill_to_site_use_id
1238 ,p_ship_to_site_id => l_ship_to_site_use_id
1239 ,p_salesrep_id => l_salesrep_id
1240 ,p_customer_ref_date => NULL
1241 ,p_customer_ref_number => claim_rec.customer_reference
1242 ,p_cash_receipt_id => l_cash_receipt_id
1243 ,p_receipt_number => l_receipt_number
1244 ,p_reason_id => to_number(claim_rec.application_ref_reason)
1245 ,p_customer_reason => claim_rec.customer_reason
1246 ,p_comments => claim_rec.comments
1247 ,p_apply_date => l_applied_date --Bug 5495310
1248 ,p_attribute_category => claim_rec.attribute_category
1249 ,p_attribute1 => claim_rec.attribute1
1250 ,p_attribute2 => claim_rec.attribute2
1251 ,p_attribute3 => claim_rec.attribute3
1252 ,p_attribute4 => claim_rec.attribute4
1253 ,p_attribute5 => claim_rec.attribute5
1254 ,p_attribute6 => claim_rec.attribute6
1255 ,p_attribute7 => claim_rec.attribute7
1256 ,p_attribute8 => claim_rec.attribute8
1257 ,p_attribute9 => claim_rec.attribute9
1258 ,p_attribute10 => claim_rec.attribute10
1259 ,p_attribute11 => claim_rec.attribute11
1260 ,p_attribute12 => claim_rec.attribute12
1261 ,p_attribute13 => claim_rec.attribute13
1262 ,p_attribute14 => claim_rec.attribute14
1263 ,p_attribute15 => claim_rec.attribute15
1264 ,x_return_status => claim_rec.return_status
1265 ,x_msg_count => claim_rec.msg_count
1266 ,x_msg_data => claim_rec.msg_data
1267 ,x_claim_id => claim_rec.secondary_application_ref_id
1268 ,x_claim_number => claim_rec.application_ref_num
1269 ,x_claim_reason_name => claim_rec.claim_reason_name
1270 ,p_legal_entity_id => l_legal_entity_id
1271 );
1272
1273 IF PG_DEBUG in ('Y','C') THEN
1274 arp_standard.debug('Short Pay DED # => '||claim_rec.application_ref_num);
1275 END IF;
1276
1277 ELSE -- Over Pay Claim
1278 IF PG_DEBUG in ('Y','C') THEN
1279 arp_standard.debug('Over Pay condition - Claim will not be created.');
1280 END IF;
1281 RAISE skip_overpay_create;
1282
1283 END IF; --IF l_trx_claim_type = 'S' THEN
1284
1285 END IF; --IF l_trx_claim_exist = 'Y' THEN
1286
1287 ------------------------------------
1288 -- Check TM API Call return status
1289 ------------------------------------
1290 IF claim_rec.return_status = 'S' THEN --TM API Return Status = Success
1291
1292 --------------------------------------------------------------------------
1293 -- Update Payment Schedules with apporpriate claim status in TM
1294 -- AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM = 'C' for subsequent receipt
1295 -- application of Over Pay Claim.
1296 -- AR_PAYMENT_SCHEDULES.ACTIVE_CLAIM = 'Y' for Short Pay Claim Creation
1297 --------------------------------------------------------------------------
1298 IF ( ((l_trx_claim_type = 'S') AND (l_trx_claim_exist = 'N'))
1299 OR ((l_trx_claim_exist = 'Y') AND (l_claim_amount = 0))
1300 ) THEN
1301
1302 IF PG_DEBUG in ('Y','C') THEN
1303 arp_standard.debug('Update PS with active_claim_flag = C');
1304 END IF;
1305 arp_deduction.update_claim_create_status
1306 (p_ps_id => claim_rec.applied_payment_schedule_id
1307 ,p_claim_status => l_claim_status
1308 );
1309
1310 END IF; --IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1311
1312
1313 ---------------------------------------------------------------------------
1314 --Update Receivable Application with Claim No, ID, Rec TRX id, Appl Ref Type
1315 ---------------------------------------------------------------------------
1316 IF ( ((l_trx_claim_type = 'S') AND (l_trx_claim_exist = 'N'))
1317 OR (l_trx_claim_exist = 'Y')
1318 ) THEN
1319
1320 IF PG_DEBUG in ('Y','C') THEN
1321 arp_standard.debug('Update RA with Ded No,Ded ID,Type,receivables_trx_id');
1322 END IF;
1323 UPDATE ar_receivable_applications
1324 SET secondary_application_ref_id = claim_rec.secondary_application_ref_id
1325 ,application_ref_num = claim_rec.application_ref_num
1326 ,application_ref_type = 'CLAIM'
1327 ,receivables_trx_id = ARP_DEDUCTION.GET_RECEIVABLES_TRX_ID(l_cash_receipt_id)
1328 WHERE applied_payment_schedule_id = claim_rec.applied_payment_schedule_id
1329 AND applied_customer_trx_id = claim_rec.applied_customer_trx_id;
1330
1331 END IF; --IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1332
1333 ELSE -- TM API Return_Status = ERROR
1334
1335 ----------------------------------------------------------
1336 -- Assign the TM API call return status to x_return_status
1337 -- For PostBatch (ARCABP) to take appropraite action
1338 -- Assign only when the x_return_status is not 'E' or 'U'
1339 -- Note : x_return_status is initialized with 'S' before
1340 -- processing any claims
1341 ----------------------------------------------------------
1342 IF (x_return_status = 'S') THEN
1343 x_return_status := claim_rec.return_status;
1344 END IF;
1345
1346 l_claim_status := 'N';
1347 IF PG_DEBUG in ('Y','C') THEN
1348 arp_standard.debug('EXCEPTION - TM API : OZF_Claim_GRP ');
1349 END IF;
1350
1351 --------------------------------------------------
1352 -- Write Failure Message to Concurrent Request Log
1353 --------------------------------------------------
1354 IF l_trx_claim_type = 'S' AND l_trx_claim_exist = 'N' THEN
1355 ---------------------------------------
1356 -- Specific to Short Pay Claim Creation
1357 ---------------------------------------
1358 arp_deduction.conc_req_log_msg
1359 ('Short Pay Claim Creation Failure - '
1360 ||' Receipt Number = '||l_receipt_number
1361 ||' Applied Payment Schedule ID = '
1362 ||to_char(claim_rec.applied_payment_schedule_id)
1363 ||' TRX Number = '||l_trx_number
1364 ||' Claim Amount = '||to_char(l_claim_amount)
1365 );
1366
1367 ELSIF l_trx_claim_exist = 'Y' THEN
1368 ---------------------------------------------
1369 -- Specific to Subsequent Receipt Application
1370 ---------------------------------------------
1371 arp_deduction.conc_req_log_msg
1372 ('Subsequent Receipt Application Failure - '
1373 ||' Receipt Number = '||l_receipt_number
1374 ||' Receipt ID = '||l_cash_receipt_id
1375 ||' Claim ID = '||to_char(claim_rec.secondary_application_ref_id)
1376 ||' Claim Number = '||claim_rec.application_ref_num
1377 ||' Claim Amount = '||to_char(l_claim_amount)
1378 ||' TRX Number = '||l_trx_number
1379 ||' Cust TRX ID = '||to_char(l_customer_trx_id)
1380 ||' Cust TRX Type ID = '||to_char(l_cust_trx_type_id)
1381 ||' Invoice PS ID = '
1382 ||to_char(claim_rec.applied_payment_schedule_id)
1383 );
1384
1385 END IF; --l_trx_claim_type = 'S' AND l_trx_claim_exist = 'Y' THEN
1386
1387 END IF; --IF claim_rec.return_status = 'S' THEN
1388
1389 END IF; --IF l_amount_due_remaining <> 0 And Short Pay Claim And Class qualified
1390
1391 EXCEPTION
1392 WHEN skip_overpay_create THEN
1393
1394 IF PG_DEBUG in ('Y', 'C') THEN
1395 arp_standard.debug('EXCEPTION - Skip_overpay_create : Trx Related - '||
1396 'Over Pay claim doesnot exist in TM.');
1397 END IF;
1398
1399 WHEN skip_pmt_record THEN
1400
1401 IF PG_DEBUG in ('Y', 'C') THEN
1402 arp_standard.debug('EXCEPTION - Skip_PMT_Record : Non Trx Related - '||
1403 'Payment Schedule CLASS=PMT. Skip processing this record.');
1404 END IF;
1405
1406 END;
1407 END LOOP; -- Loop thru ra_tbl.applied_payment_schedule_id
1408
1409 --If Recent Fetch is Last then exit loop
1410 IF l_last_fetch THEN
1411
1412 IF PG_DEBUG in ('Y', 'C') THEN
1413 arp_standard.debug('l_last_fetch=TRUE. Exit Loop for processing Non Trx claims');
1414 END IF;
1415 EXIT;
1416
1417 END IF; --IF l_last_fetch
1418
1419 END LOOP; -- Loop thru cursor get_ra_rec
1420 CLOSE get_ra_rec;
1421
1422 END IF; --IF p_matched_claim_creation_flag = 'Y' THEN
1423
1424 IF PG_DEBUG in ('Y','C') THEN
1425 arp_standard.debug('ARP_DEDUCTION.claim_creation()-');
1426 END IF;
1427
1428 EXCEPTION
1429 WHEN invalid_param THEN
1430
1431 IF PG_DEBUG in ('Y', 'C') THEN
1432 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.CLAIM_CREATION');
1433 END IF;
1434 RAISE;
1435
1436 WHEN OTHERS THEN
1437
1438 IF PG_DEBUG in ('Y', 'C') THEN
1439 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CLAIM_CREATION');
1440 END IF;
1441 RAISE;
1442
1443 END claim_creation;
1444
1445
1446 /*===========================================================================+
1447 | PROCEDURE |
1448 | update_claim |
1449 | |
1450 | DESCRIPTION |
1451 | Calls iClaim group API to update a deduction claim. |
1452 | |
1453 | SCOPE - PUBLIC |
1454 | |
1455 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
1456 | OZF_Claim_GRP.Update_Deduction - Group API to update a claim from AR |
1457 | |
1458 | ARGUMENTS : IN: |
1459 | OUT: X_RETURN_STATUS ('S' for success, 'E' or 'U' for Error |
1460 | |
1461 | RETURNS : NONE |
1462 | |
1463 | |
1464 | MODIFICATION HISTORY |
1465 | CTHANGAI 03-FEB-2003 Created |
1466 | CTHANGAI 27-FEB-2003 Added OUT parameters x_claim_id,x_claim_number |
1467 +===========================================================================*/
1468 PROCEDURE update_claim
1469 ( p_claim_id IN NUMBER
1470 , p_claim_number IN VARCHAR2
1471 , p_amount IN NUMBER
1472 , p_currency_code IN VARCHAR2
1473 , p_exchange_rate_type IN VARCHAR2
1474 , p_exchange_rate_date IN DATE
1475 , p_exchange_rate IN NUMBER
1476 , p_customer_trx_id IN NUMBER
1477 , p_invoice_ps_id IN NUMBER
1478 , p_cust_trx_type_id IN NUMBER
1479 , p_trx_number IN VARCHAR2
1480 , p_cust_account_id IN NUMBER
1481 , p_bill_to_site_id IN NUMBER
1482 , p_ship_to_site_id IN NUMBER
1483 , p_salesrep_id IN NUMBER
1484 , p_customer_ref_date IN DATE
1485 , p_customer_ref_number IN VARCHAR2
1486 , p_cash_receipt_id IN NUMBER
1487 , p_receipt_number IN VARCHAR2
1488 , p_reason_id IN NUMBER
1489 , p_comments IN VARCHAR2
1490 , p_attribute_category IN VARCHAR2
1491 , p_attribute1 IN VARCHAR2
1492 , p_attribute2 IN VARCHAR2
1493 , p_attribute3 IN VARCHAR2
1494 , p_attribute4 IN VARCHAR2
1495 , p_attribute5 IN VARCHAR2
1496 , p_attribute6 IN VARCHAR2
1497 , p_attribute7 IN VARCHAR2
1498 , p_attribute8 IN VARCHAR2
1499 , p_attribute9 IN VARCHAR2
1500 , p_attribute10 IN VARCHAR2
1501 , p_attribute11 IN VARCHAR2
1502 , p_attribute12 IN VARCHAR2
1503 , p_attribute13 IN VARCHAR2
1504 , p_attribute14 IN VARCHAR2
1505 , p_attribute15 IN VARCHAR2
1506 , p_applied_date IN DATE
1507 , p_applied_action_type IN VARCHAR2
1508 , p_amount_applied IN NUMBER
1509 , p_applied_receipt_id IN NUMBER
1510 , p_applied_receipt_number IN VARCHAR2
1511 , x_return_status OUT NOCOPY VARCHAR2
1512 , x_msg_count OUT NOCOPY NUMBER
1513 , x_msg_data OUT NOCOPY VARCHAR2
1514 , x_object_version_number OUT NOCOPY NUMBER
1515 , x_claim_reason_code_id OUT NOCOPY NUMBER
1516 , x_claim_reason_name OUT NOCOPY VARCHAR2
1517 , x_claim_id OUT NOCOPY NUMBER
1518 , x_claim_number OUT NOCOPY VARCHAR2
1519 ) IS
1520
1521 l_claim_rec OZF_Claim_GRP.Deduction_Rec_Type;
1522 l_return_status VARCHAR2(1);
1523 l_text VARCHAR2(2000);
1524 l_user_id NUMBER;
1525 l_last_update_login NUMBER;
1526 l_sysdate DATE;
1527 l_note_id NUMBER;
1528
1529 BEGIN
1530
1531 IF PG_DEBUG in ('Y', 'C') THEN
1532 arp_standard.debug('update_action: ' || 'arp_deduction.update_claim()+' );
1533 END IF;
1534
1535 x_return_status := 'S';
1536 l_claim_rec.claim_id := p_claim_id;
1537 l_claim_rec.claim_number := p_claim_number;
1538 l_claim_rec.claim_type_id := NULL;
1539 l_claim_rec.claim_date := NULL;
1540 l_claim_rec.due_date := NULL;
1541 l_claim_rec.amount := p_amount;
1542 l_claim_rec.currency_code := p_currency_code;
1543 l_claim_rec.exchange_rate_type := p_exchange_rate_type;
1544 l_claim_rec.exchange_rate_date := p_exchange_rate_date;
1545 l_claim_rec.exchange_rate := p_exchange_rate;
1546 l_claim_rec.set_of_books_id := arp_global.set_of_books_id;
1547 l_claim_rec.source_object_id := p_customer_trx_id;
1548 l_claim_rec.source_object_type_id := p_cust_trx_type_id;
1549 l_claim_rec.source_object_class := 'INVOICE';
1550 l_claim_rec.source_object_number := p_trx_number;
1551 l_claim_rec.cust_account_id := p_cust_account_id;
1552 l_claim_rec.cust_billto_acct_site_id := p_bill_to_site_id;
1553 l_claim_rec.cust_shipto_acct_site_id := p_ship_to_site_id;
1554 l_claim_rec.sales_rep_id := p_salesrep_id;
1555 l_claim_rec.reason_code_id := p_reason_id;
1556 l_claim_rec.customer_ref_date := p_customer_ref_date;
1557 l_claim_rec.customer_ref_number := p_customer_ref_number;
1558 l_claim_rec.receipt_id := p_cash_receipt_id;
1559 l_claim_rec.receipt_number := p_receipt_number;
1560 l_claim_rec.comments := p_comments;
1561 l_claim_rec.deduction_attribute_category := p_attribute_category;
1562 l_claim_rec.deduction_attribute1 := p_attribute1;
1563 l_claim_rec.deduction_attribute2 := p_attribute2;
1564 l_claim_rec.deduction_attribute3 := p_attribute3;
1565 l_claim_rec.deduction_attribute4 := p_attribute4;
1566 l_claim_rec.deduction_attribute5 := p_attribute5;
1567 l_claim_rec.deduction_attribute6 := p_attribute6;
1568 l_claim_rec.deduction_attribute7 := p_attribute7;
1569 l_claim_rec.deduction_attribute8 := p_attribute8;
1570 l_claim_rec.deduction_attribute9 := p_attribute9;
1571 l_claim_rec.deduction_attribute10 := p_attribute10;
1572 l_claim_rec.deduction_attribute11 := p_attribute11;
1573 l_claim_rec.deduction_attribute12 := p_attribute12;
1574 l_claim_rec.deduction_attribute13 := p_attribute13;
1575 l_claim_rec.deduction_attribute14 := p_attribute14;
1576 l_claim_rec.deduction_attribute15 := p_attribute15;
1577 l_claim_rec.applied_date := p_applied_date;
1578 l_claim_rec.applied_action_type := p_applied_action_type;
1579 l_claim_rec.amount_applied := p_amount_applied;
1580 l_claim_rec.applied_receipt_id := p_applied_receipt_id;
1581 l_claim_rec.applied_receipt_number := p_applied_receipt_number;
1582
1583 -------------------------------------------------
1584 -- Call TM API for Subsequent Receipt Application
1585 -------------------------------------------------
1586 OZF_Claim_GRP.Update_Deduction
1587 (p_api_version_number => 1.0
1588 ,p_init_msg_list => FND_API.G_TRUE
1589 ,p_commit => FND_API.G_FALSE
1590 ,x_return_status => l_return_status
1591 ,x_msg_count => x_msg_count
1592 ,x_msg_data => x_msg_data
1593 ,p_deduction => l_claim_rec
1594 ,x_object_version_number => x_object_version_number
1595 ,x_claim_reason_code_id => x_claim_reason_code_id
1596 ,x_claim_reason_name => x_claim_reason_name
1597 ,x_claim_id => x_claim_id
1598 ,x_claim_number => x_claim_number
1599 );
1600
1601 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1602
1603 x_return_status := 'S';
1604 IF (p_customer_trx_id IS NOT NULL) AND (p_customer_trx_id > 0) THEN
1605
1606 --------------------
1607 -- Insert Trx Notes
1608 --------------------
1609 IF PG_DEBUG in ('Y', 'C') THEN
1610 arp_standard.debug('insert_trx_notes: arp_deduction.update_claim');
1611 END IF;
1612
1613 l_text := 'RECEIPT_NUM : '||p_receipt_number||' CLAIM_NUM : '
1614 ||p_claim_number||' TRX_NUM : '||p_trx_number;
1615
1616 l_user_id := arp_standard.profile.user_id;
1617 l_last_update_login := arp_standard.profile.last_update_login;
1618 l_sysdate := SYSDATE;
1619
1620 arp_notes_pkg.insert_cover(
1621 p_note_type => 'MAINTAIN',
1622 p_text => l_text,
1623 p_customer_call_id => NULL,
1624 p_customer_call_topic_id => NULL,
1625 p_call_action_id => NULL,
1626 p_customer_trx_id => p_customer_trx_id,
1627 p_note_id => l_note_id,
1628 p_last_updated_by => l_user_id,
1629 p_last_update_date => l_sysdate,
1630 p_last_update_login => l_last_update_login,
1631 p_created_by => l_user_id,
1632 p_creation_date => l_sysdate);
1633
1634 IF PG_DEBUG in ('Y', 'C') THEN
1635 arp_standard.debug('insert_trx_notes: Note ID = '||to_char(l_note_id));
1636 END IF;
1637
1638 --
1639 -- Update TRX Amount in Dispute
1640 --
1641 IF PG_DEBUG in ('Y', 'C') THEN
1642 arp_standard.debug('put_trx_in_dispute: arp_deduction.update_claim');
1643 END IF;
1644 arp_process_application.update_dispute_on_trx
1645 (p_invoice_ps_id
1646 ,'Y' --p_active_claim
1647 ,p_amount_applied
1648 );
1649
1650 END IF; -- IF (p_customer_trx_id IS NOT NULL) AND (p_customer_trx_id > 0)
1651
1652 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1653 x_return_status := 'E';
1654
1655 ELSE --Unidentified Error
1656 x_return_status := 'U';
1657
1658 END IF; --IF l_return_status = FND_API.G_RET_STS_SUCCESS
1659
1660 IF PG_DEBUG in ('Y', 'C') THEN
1661 arp_standard.debug('update_action: arp_deduction.update_claim()-');
1662 END IF;
1663
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 IF PG_DEBUG in ('Y', 'C') THEN
1667 arp_standard.debug('update_action: EXCEPTION: arp_deduction.update_claim');
1668 END IF;
1669 RAISE;
1670
1671 END update_claim;
1672
1673
1674 /*===========================================================================+
1675 | PUBLIC PROCEDURE |
1676 | create_claims_rapp_dist |
1677 | |
1678 | DESCRIPTION |
1679 | Procedure for creating claims related RA and associated Distributions |
1680 | Insert 2 RA rows - One as -ve UNAPP row and the second as 'OTHER ACC' |
1681 | The on-account ACC row is similar to the 'OTHER ACC'. |
1682 | This new procedure is introduced for creating special applications like|
1683 | claim.The RA records for 'ACC' or 'OTHER ACC' are created first along |
1684 | with thier corresponding distributions records. After which the |
1685 | negative UNAPP record is created along with its PAIRED distribution |
1686 | record. |
1687 | |
1688 | SCOPE - PUBLIC |
1689 | |
1690 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
1691 | arp_app_pkg.insert_p - Insert a row into RA table |
1692 | arp_acct_main.Create_Acct_Entry - Insert a row into Distributions table |
1693 | |
1694 | ARGUMENTS : IN: |
1695 | |
1696 | OUT: x_return_status (S=Success; E=Error/Failure |
1697 | |
1698 | RETURNS : NONE |
1699 | |
1700 | NOTES - |
1701 | |
1702 | MODIFICATION HISTORY |
1703 | 13-JAN-03 CTHANGAI Created |
1704 | 21-JAN-03 CTHANGAI Added parameter receivables_trx_id |
1705 | 05-FEB-03 CTHANGAI Removed paramter application_ref_reason |
1706 | 10-FEB-03 CTHANGAI Initialize customer_reference with NULL |
1707 | before creating UNAPP records in |
1708 | ar_receivable_applications |
1709 | 13-FEB-03 CTHANGAI Removed paramter applied_payment_schedule_id |
1710 | 14-FEB-03 CTHANGAI Initialize receivable_trx_id with NULL for ACC |
1711 | 18-FEB-03 CTHANGAI On Exception write message to concurrent log |
1712 | 19-FEB-03 CTHANGAI Defualt program_application_id = 222 |
1713 | 24-FEB-03 CTHANGAI Defualt postable='Y' for Claim Investigation |
1714 | 24-FEB-03 CTHANGAI On_Account_Customer populated only for ACC |
1715 +===========================================================================*/
1716 PROCEDURE create_claims_rapp_dist
1717 (p_cash_receipt_id IN ar_receivable_applications.cash_receipt_id%TYPE
1718 ,p_unapp_ccid IN ar_receivable_applications.code_combination_id%TYPE
1719 ,p_other_acc_ccid IN ar_receivable_applications.code_combination_id%TYPE
1720 ,p_acc_ccid IN ar_receivable_applications.code_combination_id%TYPE
1721 ,p_gl_date IN ar_receivable_applications.gl_date%TYPE
1722 ,p_status IN ar_receivable_applications.status%TYPE
1723 ,p_amount_applied IN ar_receivable_applications.amount_applied%TYPE
1724 ,p_created_by IN ar_receivable_applications.created_by%TYPE
1725 ,p_creation_date IN ar_receivable_applications.creation_date%TYPE
1726 ,p_last_updated_by IN ar_receivable_applications.last_updated_by%TYPE
1727 ,p_program_application_id IN ar_receivable_applications.program_application_id%TYPE
1728 ,p_program_id IN ar_receivable_applications.program_id%TYPE
1729 ,p_request_id IN ar_receivable_applications.request_id%TYPE
1730 ,p_sob_id IN ar_receivable_applications.set_of_books_id%TYPE
1731 ,p_apply_date IN ar_receivable_applications.apply_date%TYPE
1732 ,p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE
1733 ,p_receipt_ps_id IN ar_receivable_applications.payment_schedule_id%TYPE
1734 ,p_unapp_application_rule IN ar_receivable_applications.application_rule%TYPE
1735 ,p_other_application_rule IN ar_receivable_applications.application_rule%TYPE
1736 ,p_acc_application_rule IN ar_receivable_applications.application_rule%TYPE
1737 ,p_on_account_customer IN ar_receivable_applications.on_account_customer%TYPE
1738 ,p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE
1739 ,p_customer_reference IN ar_receivable_applications.customer_reference%TYPE
1740 ,p_customer_reason IN ar_receivable_applications.customer_reason%TYPE
1741 ,p_attribute_category IN ar_receivable_applications.attribute_category%TYPE
1742 ,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
1743 ,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
1744 ,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
1745 ,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
1746 ,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
1747 ,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
1748 ,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
1749 ,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
1750 ,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
1751 ,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
1752 ,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
1753 ,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
1754 ,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
1755 ,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
1756 ,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
1757 ,x_return_status OUT NOCOPY VARCHAR2
1758 ) IS
1759
1760 l_ra_rec ar_receivable_applications%ROWTYPE;
1761 l_prev_app_id ar_receivable_applications.receivable_application_id%TYPE;
1762 l_ae_doc_rec ARP_ACCT_MAIN.ae_doc_rec_type;
1763 l_xla_ev_rec arp_xla_events.xla_events_type;
1764 l_msg_data VARCHAR2(2000);
1765
1766 BEGIN
1767
1768 IF PG_DEBUG in ('Y', 'C') THEN
1769 arp_standard.debug('arp_deduction.create_claims_rapp_dist()+' );
1770 END IF;
1771 x_return_status := FND_API.G_RET_STS_SUCCESS;
1772
1773 IF PG_DEBUG in ('Y', 'C') THEN
1774 arp_standard.debug('p_receipt_ps_id = '||TO_CHAR(p_receipt_ps_id));
1775 arp_standard.debug('p_amount_applied = '||TO_CHAR( p_amount_applied ) );
1776 --arp_standard.debug('p_gl_date = '|| TO_CHAR( p_gl_date ) );
1777 --arp_standard.debug('p_apply_date = '|| TO_CHAR( p_apply_date ) );
1778 END IF;
1779
1780 -- ---------------------------------------------------------------------
1781 -- Prepare for 'ACC' and 'OTHER ACC' record insertion with +ve amount applied
1782 -- applied_customer_trx_id = -1 and display = 'Y'
1783 -- ---------------------------------------------------------------------
1784 l_msg_data := 'Initialize local reccord type with parameter.';
1785 l_ra_rec.cash_receipt_id := p_cash_receipt_id;
1786 l_ra_rec.receivable_application_id := NULL;
1787 l_ra_rec.gl_date := p_gl_date;
1788 l_ra_rec.status := p_status;
1789 l_ra_rec.amount_applied := p_amount_applied;
1790 l_ra_rec.acctd_amount_applied_from := p_amount_applied;
1791 l_ra_rec.created_by := p_created_by;
1792 l_ra_rec.creation_date := p_creation_date;
1793 l_ra_rec.last_updated_by := p_last_updated_by;
1794 l_ra_rec.last_update_date := SYSDATE;
1795 l_ra_rec.program_application_id := 222; --p_program_application_id;
1796 l_ra_rec.program_id := p_program_id;
1797 l_ra_rec.program_update_date := SYSDATE;
1798 l_ra_rec.request_id := p_request_id;
1799 l_ra_rec.display := 'Y';
1800 l_ra_rec.set_of_books_id := p_sob_id;
1801 l_ra_rec.apply_date := p_apply_date;
1802 l_ra_rec.application_type := 'CASH';
1803 l_ra_rec.posting_control_id := -3;
1804 l_ra_rec.ussgl_transaction_code := p_ussgl_transaction_code;
1805 l_ra_rec.payment_schedule_id := p_receipt_ps_id;
1806 l_ra_rec.application_rule := p_unapp_application_rule;
1807 l_ra_rec.applied_customer_trx_id := -1;
1808 l_ra_rec.attribute_category := p_attribute_category;
1809 l_ra_rec.attribute1 := p_attribute1;
1810 l_ra_rec.attribute2 := p_attribute2;
1811 l_ra_rec.attribute3 := p_attribute3;
1812 l_ra_rec.attribute4 := p_attribute4;
1813 l_ra_rec.attribute5 := p_attribute5;
1814 l_ra_rec.attribute6 := p_attribute6;
1815 l_ra_rec.attribute7 := p_attribute7;
1816 l_ra_rec.attribute8 := p_attribute8;
1817 l_ra_rec.attribute9 := p_attribute9;
1818 l_ra_rec.attribute10 := p_attribute10;
1819 l_ra_rec.attribute11 := p_attribute11;
1820 l_ra_rec.attribute12 := p_attribute12;
1821 l_ra_rec.attribute13 := p_attribute13;
1822 l_ra_rec.attribute14 := p_attribute14;
1823 l_ra_rec.attribute15 := p_attribute15;
1824 l_ra_rec.customer_reference := p_customer_reference;
1825 l_ra_rec.customer_reason := p_customer_reason;
1826
1827 ----------------------------------------------------
1828 -- Assign values appropriate to 'ACC' or 'OTHER ACC'
1829 ----------------------------------------------------
1830 IF p_status = 'OTHER ACC' THEN
1831 l_ra_rec.applied_payment_schedule_id := -4;
1832 l_ra_rec.code_combination_id := p_other_acc_ccid;
1833 l_ra_rec.application_ref_type := 'CLAIM';
1834 l_ra_rec.application_rule := p_other_application_rule;
1835 l_ra_rec.receivables_trx_id := p_receivables_trx_id;
1836 l_ra_rec.postable := 'Y';
1837 l_ra_rec.on_account_customer := NULL;
1838 ELSIF p_status = 'ACC' THEN
1839 l_ra_rec.applied_payment_schedule_id := -1;
1840 l_ra_rec.code_combination_id := p_acc_ccid;
1841 l_ra_rec.application_ref_type := NULL;
1842 l_ra_rec.application_rule := p_acc_application_rule;
1843 l_ra_rec.receivables_trx_id := NULL;
1844 l_ra_rec.postable := NULL;
1845 l_ra_rec.on_account_customer := p_on_account_customer;
1846 END IF;
1847
1848 -- ---------------------------------------------------------------------
1849 -- Insert 'OTHER ACC' OR 'ACC' record into AR_RECEIVABLE_APPLICATIONS
1850 -- ---------------------------------------------------------------------
1851 arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
1852
1853 IF l_ra_rec.receivable_application_id IS NOT NULL THEN
1854
1855 arp_standard.debug('Before calling ARP_XLA_EVENTS.create_events....');
1856
1857 l_xla_ev_rec.xla_from_doc_id := l_ra_rec.receivable_application_id;
1858 l_xla_ev_rec.xla_to_doc_id := l_ra_rec.receivable_application_id;
1859
1860 l_xla_ev_rec.xla_mode := 'O';
1861 l_xla_ev_rec.xla_call := 'B';
1862 l_xla_ev_rec.xla_doc_table := 'APP';
1863 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1864
1865 arp_standard.debug('Processessed following RA row :receivable_application_id : '|| l_ra_rec.receivable_application_id);
1866
1867 END IF;
1868
1869 ------------------------------------------------------------------------
1870 -- Replicate MRC data if necessary
1871 ------------------------------------------------------------------------
1872 ar_mrc_engine3.insert_ra_rec_quickcash(
1873 p_rec_app_id => l_ra_rec.receivable_application_id);
1874
1875 -- ---------------------------------------------------------------------
1876 -- Store APP id for PAIRING
1877 -- ---------------------------------------------------------------------
1878 l_prev_app_id := l_ra_rec.receivable_application_id;
1879
1880 -- ---------------------------------------------------------------------
1881 -- Create 'OTHER ACC' OR 'ACC' record accounting in ar_distributions
1882 -- ---------------------------------------------------------------------
1883 l_ae_doc_rec.document_type := 'RECEIPT';
1884 l_ae_doc_rec.document_id := l_ra_rec.cash_receipt_id;
1885 l_ae_doc_rec.accounting_entity_level := 'ONE';
1886 l_ae_doc_rec.source_table := 'RA';
1887 l_ae_doc_rec.source_id := l_ra_rec.receivable_application_id;
1888 l_ae_doc_rec.source_id_old := '';
1889 l_ae_doc_rec.other_flag := '';
1890 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1891
1892 -- ---------------------------------------------------------------------
1893 -- Prepare for 'UNAPP' record insertion with -ve amount applied
1894 -- applied_customer_trx_id and applied_payment_schedule_id is NULL
1895 -- display = 'N'
1896 -- ---------------------------------------------------------------------
1897 l_ra_rec.receivable_application_id := NULL; --Sequence generated while insert
1898 l_ra_rec.applied_customer_trx_id := NULL;
1899 l_ra_rec.display := 'N';
1900 l_ra_rec.on_account_customer := NULL;
1901 l_ra_rec.customer_reference := NULL;
1902 l_ra_rec.customer_reason := NULL;
1903 l_ra_rec.receivables_trx_id := NULL;
1904 l_ra_rec.amount_applied := -p_amount_applied;
1905 l_ra_rec.acctd_amount_applied_from := -p_amount_applied;
1906 l_ra_rec.applied_payment_schedule_id := NULL;
1907 l_ra_rec.code_combination_id := p_unapp_ccid;
1908 l_ra_rec.status := 'UNAPP';
1909 l_ra_rec.application_ref_type := NULL;
1910 l_ra_rec.application_rule := p_unapp_application_rule;
1911
1912 -- ---------------------------------------------------------------------
1913 -- Insert Negative UNAPP record
1914 -- ---------------------------------------------------------------------
1915 arp_app_pkg.insert_p( l_ra_rec, l_ra_rec.receivable_application_id );
1916
1917 IF l_ra_rec.receivable_application_id IS NOT NULL THEN
1918
1919 arp_standard.debug('Before calling ARP_XLA_EVENTS.create_events....');
1920
1921 l_xla_ev_rec.xla_from_doc_id := l_ra_rec.receivable_application_id;
1922 l_xla_ev_rec.xla_to_doc_id := l_ra_rec.receivable_application_id;
1923
1924 l_xla_ev_rec.xla_mode := 'O';
1925 l_xla_ev_rec.xla_call := 'B';
1926 l_xla_ev_rec.xla_doc_table := 'APP';
1927 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1928
1929 arp_standard.debug('Processessed following RA row :receivable_application_id : '|| l_ra_rec.receivable_application_id);
1930
1931 END IF;
1932
1933 ------------------------------------------------------------------------
1934 -- Replicate MRC data if necessary
1935 ------------------------------------------------------------------------
1936 ar_mrc_engine3.create_matching_unapp_records(
1937 p_rec_app_id => l_prev_app_id,
1938 p_rec_unapp_id => l_ra_rec.receivable_application_id);
1939 --
1940
1941 -- ---------------------------------------------------------------------
1942 -- Create paired UNAPP record accounting in ar_distributions
1943 -- ---------------------------------------------------------------------
1944 l_ae_doc_rec.source_id_old := l_prev_app_id;
1945 l_ae_doc_rec.source_id := l_ra_rec.receivable_application_id;
1946 l_ae_doc_rec.other_flag := 'PAIR';
1947 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1948
1949 IF PG_DEBUG in ('Y', 'C') THEN
1950 arp_standard.debug('arp_deduction.create_claims_rapp_dist()-' );
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955
1956 IF PG_DEBUG in ('Y', 'C') THEN
1957 arp_standard.debug('EXCEPTION: arp_deduction.create_claims_rapp_dist()-');
1958 END IF;
1959
1960 x_return_status := FND_API.G_RET_STS_ERROR;
1961 arp_deduction.conc_req_log_msg
1962 ('EXCEPTION: arp_deduction.create_claims_rapp_dist'||
1963 ' STATUS='||p_status||
1964 ' CCID='||to_char(l_ra_rec.code_combination_id)||
1965 ' Cash_Receipt_ID='||to_char(l_ra_rec.cash_receipt_id)||
1966 ' Payment Schedule_ID='||to_char(l_ra_rec.payment_schedule_id)
1967 );
1968 arp_deduction.conc_req_log_msg('SQLERRM='||substr(SQLERRM,1,255));
1969
1970 RAISE;
1971
1972 END create_claims_rapp_dist;
1973
1974
1975 /*========================================================================
1976 | PUBLIC PROCEDURE claim_create_fail_recover
1977 |
1978 | DESCRIPTION
1979 | Procedure to recover from claim creation failure.
1980 | The receivable application records and thier corresponding distribution
1981 | records for the claim are deleted. The payment schedule amounts are
1982 | updated appropriately.
1983 |
1984 | CALLED FROM PROCEDURES/FUNCTIONS
1985 | claim_creation
1986 |
1987 | CALLS PROCEDURES/FUNCTIONS
1988 | arp_app_pkg.delete_p
1989 | arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1990 |
1991 |
1992 | PARAMETERS
1993 | IN :
1994 | p_rec_app_id
1995 | p_receipt_id
1996 |
1997 | OUT :
1998 |
1999 | KNOWN ISSUES
2000 |
2001 | NOTES
2002 |
2003 | MODIFICATION HISTORY
2004 | Date Author Description of Changes
2005 | 17-JAN-2003 cthangai Created
2006 | 05-MAR-2003 cthangai Removed Payment Schedule ID Parameter.
2007 | 01-Apr-2003 Debbie Jancis Added calls to replicate mrc data
2008 *=======================================================================*/
2009 PROCEDURE claim_create_fail_recover
2010 (p_rapp_id IN ar_receivable_applications.receivable_application_id%TYPE
2011 ,p_cr_id IN ar_receivable_applications.cash_receipt_id%TYPE
2012 ) IS
2013
2014 --
2015 -- Cursor to fetch the ('APP' OR 'OTHER ACC') AND 'UNAPP' applications
2016 -- for delete
2017 --
2018 CURSOR rapp_rec IS
2019 select ra.* --'APP' OR 'OTHER ACC'
2020 from ar_receivable_applications ra
2021 where ra.receivable_application_id = p_rapp_id
2022 and nvl(ra.confirmed_flag,'Y') = 'Y'
2023 and exists (select 'x'
2024 from ar_distributions_all ard
2025 where ard.source_table = 'RA'
2026 and ard.source_id = ra.receivable_application_id)
2027 UNION
2028 select ra.* --'UNAPP'
2029 from ar_receivable_applications ra
2030 , ar_distributions ard
2031 where ra.receivable_application_id = ard.SOURCE_ID
2032 and nvl(ra.confirmed_flag,'Y') = 'Y'
2033 and ard.source_table = 'RA'
2034 and ard.source_id_secondary =
2035 ( select ra1.receivable_application_id
2036 from ar_receivable_applications ra1
2037 where ra1.receivable_application_id = p_rapp_id );
2038
2039 l_ae_doc_rec ARP_ACCT_MAIN.ae_doc_rec_type;
2040 ln_unapp_ra_id ar_receivable_applications.receivable_application_id%TYPE;
2041 ln_ra_id ar_receivable_applications.receivable_application_id%TYPE;
2042
2043 BEGIN
2044
2045 IF PG_DEBUG in ('Y', 'C') THEN
2046 arp_standard.debug('arp_deduction.claim_create_fail_recover()+' );
2047 END IF;
2048
2049 IF PG_DEBUG in ('Y', 'C') THEN
2050 arp_standard.debug('p_rec_app_id = '||TO_CHAR(p_rapp_id));
2051 arp_standard.debug('p_receipt_id = '||TO_CHAR(p_cr_id));
2052 END IF;
2053
2054 ------------------------------------------------------------------
2055 -- Loop through the RA ('APP' OR 'OTHER ACC') and UNAPP For Delete
2056 ------------------------------------------------------------------
2057 FOR l_rapp_rec in rapp_rec
2058 LOOP
2059
2060 IF l_rapp_rec.status IN ('APP','OTHER ACC') THEN
2061 ln_ra_id := l_rapp_rec.receivable_application_id;
2062
2063 ELSIF l_rapp_rec.status = 'UNAPP' THEN
2064 ln_unapp_ra_id := l_rapp_rec.receivable_application_id;
2065
2066 END IF;
2067
2068 -------------------------------------------------
2069 --Delete child accounting records associated with
2070 --parent applications for APP
2071 -------------------------------------------------
2072 l_ae_doc_rec.document_type := 'RECEIPT';
2073 l_ae_doc_rec.document_id := l_rapp_rec.cash_receipt_id;
2074 l_ae_doc_rec.accounting_entity_level := 'ONE';
2075 l_ae_doc_rec.source_table := 'RA';
2076 l_ae_doc_rec.source_id := l_rapp_rec.receivable_application_id;
2077 l_ae_doc_rec.source_id_old := '';
2078 l_ae_doc_rec.other_flag := '';
2079 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2080
2081 END LOOP; -- FOR l_rapp_rec in rapp_rec
2082
2083 -------------------------------------------------------------
2084 -- Delete 'APP' OR 'OTHER ACC' Receivable Application record.
2085 -------------------------------------------------------------
2086 arp_app_pkg.delete_p(ln_ra_id);
2087
2088 /*----------------------------------+
2089 | Calling central MRC library |
2090 | for MRC Integration |
2091 +---------------------------------*/
2092
2093 ar_mrc_engine.maintain_mrc_data(
2094 p_event_mode => 'DELETE',
2095 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
2096 p_mode => 'SINGLE',
2097 p_key_value => ln_ra_id);
2098
2099 ------------------------------------------------
2100 -- Delete 'UNAPP' Receivable Application record.
2101 ------------------------------------------------
2102 arp_app_pkg.delete_p(ln_unapp_ra_id);
2103
2104 /*----------------------------------+
2105 | Calling central MRC library |
2106 | for MRC Integration |
2107 +---------------------------------*/
2108
2109 ar_mrc_engine.maintain_mrc_data(
2110 p_event_mode => 'DELETE',
2111 p_table_name => 'AR_RECEIVABLE_APPLICATIONS',
2112 p_mode => 'SINGLE',
2113 p_key_value => ln_ra_id);
2114
2115 IF PG_DEBUG in ('Y', 'C') THEN
2116 arp_standard.debug('arp_deduction.claim_create_fail_recover()-' );
2117 END IF;
2118
2119 EXCEPTION
2120
2121 WHEN OTHERS THEN
2122 IF PG_DEBUG IN ('Y', 'C') THEN
2123 arp_standard.debug('EXCEPTION: arp_deduction.claim_create_fail_recover' );
2124 END IF;
2125 RAISE;
2126
2127 END claim_create_fail_recover;
2128
2129
2130 /*========================================================================
2131 | PUBLIC FUNCTION GET_FUNCTIONAL_CURRENCY
2132 |
2133 | DESCRIPTION
2134 | This function is called in the view associated with the LOV in the
2135 | multiple Quickcash screen for the receipt to receipt feature to
2136 | derive the functional currency code
2137 |
2138 | SCOPE - PUBLIC
2139 |
2140 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2141 |
2142 | ARGUMENTS :
2143 | IN : NONE
2144 |
2145 | OUT: NONE
2146 |
2147 | RETURNS : VARCHAR (Functional Currency Code by Set Of Books)
2148 |
2149 |
2150 | NOTES : This should be eventually rellocated into the arp_util package
2151 |
2152 | MODIFICATION HISTORY
2153 | Date Author Description of Changes
2154 | 21-JAN-2003 cthangai Created
2155 | DD-MON-YYYY Name Bug #####, modified amount ..
2156 |
2157 *=======================================================================*/
2158 FUNCTION GET_FUNCTIONAL_CURRENCY RETURN VARCHAR2 IS
2159
2160 l_currency_code gl_sets_of_books.currency_code%type;
2161
2162 BEGIN
2163
2164 SELECT gl.currency_code
2165 INTO l_currency_code
2166 FROM gl_sets_of_books gl
2167 ,ar_system_parameters ar
2168 WHERE gl.set_of_books_id = ar.set_of_books_id;
2169
2170 RETURN l_currency_code;
2171
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174
2175 IF PG_DEBUG in ('Y', 'C') THEN
2176 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_FUNCTIONAL_CURRENCY');
2177 END IF;
2178 RETURN NULL;
2179
2180 END GET_FUNCTIONAL_CURRENCY;
2181
2182
2183 /*========================================================================
2184 | PUBLIC FUNCTION GET_RECEIVABLES_TRX_ID
2185 |
2186 | DESCRIPTION
2187 | This function is called to retreive the receivables_trx_id
2188 |
2189 | SCOPE - PUBLIC
2190 |
2191 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2192 |
2193 | ARGUMENTS :
2194 | IN : p_cash_receipt_id
2195 |
2196 | OUT: NONE
2197 |
2198 | RETURNS : NUMBER (receivable_trx_id associated with the cash_receipt_id)
2199 |
2200 |
2201 | NOTES :
2202 |
2203 | MODIFICATION HISTORY
2204 | Date Author Description of Changes
2205 | 23-JAN-2003 cthangai Created
2206 | DD-MON-YYYY Name Bug #####, modified amount ..
2207 |
2208 *=======================================================================*/
2209 FUNCTION GET_RECEIVABLES_TRX_ID
2210 (p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE) RETURN NUMBER IS
2211
2212 l_receivables_trx_id ar_receivables_trx.receivables_trx_id%TYPE;
2213
2214 BEGIN
2215
2216 IF PG_DEBUG in ('Y', 'C') THEN
2217 arp_standard.debug('arp_deduction.GET_RECEIVABLES_TRX_ID()+' );
2218 END IF;
2219
2220 SELECT rt.receivables_trx_id
2221 INTO l_receivables_trx_id
2222 FROM ar_receivables_trx rt
2223 WHERE rt.receivables_trx_id = (
2224 SELECT rma.claim_receivables_trx_id
2225 FROM ar_receipt_method_accounts rma, ar_cash_receipts cr
2226 WHERE rma.receipt_method_id = cr.receipt_method_id
2227 AND cr.cash_receipt_id = p_cash_receipt_id
2228 AND rma.primary_flag = 'Y' );
2229
2230 IF PG_DEBUG in ('Y', 'C') THEN
2231 arp_standard.debug('arp_deduction.GET_RECEIVABLES_TRX_ID()-' );
2232 END IF;
2233
2234 RETURN l_receivables_trx_id;
2235
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 IF PG_DEBUG in ('Y', 'C') THEN
2239 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_RECEIVABLES_TRX_ID');
2240 END IF;
2241 RETURN NULL;
2242
2243 END GET_RECEIVABLES_TRX_ID;
2244
2245
2246 /*========================================================================
2247 | PUBLIC PROCEDURE UPDATE_CLAIM_CREATE_STATUS
2248 |
2249 | DESCRIPTION
2250 | This function is called to update ar_payment_schedules,
2251 | active_claim_flag column with the appropriate claim status returned
2252 | from TM
2253 |
2254 | SCOPE - PUBLIC
2255 |
2256 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2257 |
2258 | ARGUMENTS :
2259 | IN : p_ps_id --payment_schedule_id
2260 | p_claim_status --claim status
2261 |
2262 | OUT: NONE
2263 |
2264 | NOTES :
2265 |
2266 | MODIFICATION HISTORY
2267 | Date Author Description of Changes
2268 | 21-JAN-2003 cthangai Update ar_payment_schdeules.active_claim_flag
2269 | based on the claim status in TM
2270 | DD-MON-YYYY Name Bug #####, modified amount ..
2271 |
2272 *=======================================================================*/
2273 PROCEDURE UPDATE_CLAIM_CREATE_STATUS
2274 (p_ps_id IN ar_payment_schedules.payment_schedule_id%type
2275 ,p_claim_status IN ar_payment_schedules.active_claim_flag%type
2276 ) IS
2277
2278 BEGIN
2279
2280 -------------------------
2281 -- Update claim status
2282 -------------------------
2283 UPDATE ar_payment_schedules
2284 SET active_claim_flag = p_claim_status
2285 WHERE payment_schedule_id = p_ps_id;
2286
2287 EXCEPTION
2288 WHEN OTHERS THEN
2289 IF PG_DEBUG in ('Y', 'C') THEN
2290 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.UPDATE_CLAIM_CREATE_STATUS');
2291 END IF;
2292 RAISE;
2293
2294 END UPDATE_CLAIM_CREATE_STATUS;
2295
2296
2297 /*========================================================================
2298 | PUBLIC FUNCTION OVERAPPLICATION_INDICATOR
2299 |
2300 | DESCRIPTION
2301 | Function to determine whether amount applied will cause an
2302 | overapplication given the current amount due remaining and
2303 | amount due original.
2304 | A=sign(amount_due_remaining - amount_applied)
2305 | B=sign(amount_due_original)
2306 | If A=-1 and B=+1 or A=+1 and B=-1 then overapplication
2307 | Returns Y else Return N
2308 | CALLED FROM PROCEDURES/FUNCTIONS
2309 | Called from AR_INTERIM_CASH_RECEIPTS_V ,AR_INTERIM_CR_LINES_V
2310 | and Quick Cash Form (ARXRWQRC.fmb)
2311 | CALLS PROCEDURES/FUNCTIONS
2312 |
2313 |
2314 |
2315 | PARAMETERS
2316 | IN:
2317 | P_AMOUNT_DUE_ORIGINAL IN NUMBER
2318 | P_AMOUNT_DUE_REMAININIG IN NUMBER
2319 | P_AMOUNT_APPLIED IN NUMBER
2320 | OUT:
2321 | RETURN Y/N
2322 | KNOWN ISSUES
2323 |
2324 | NOTES
2325 |
2326 | MODIFICATION HISTORY
2327 | Date Author Description of Changes
2328 | 21-JAN-2003 KDhaliwal Created
2329 |
2330 *=======================================================================*/
2331 FUNCTION OVERAPPLICATION_INDICATOR
2332 (P_AMOUNT_DUE_ORIGINAL IN NUMBER
2333 ,P_AMOUNT_DUE_REMAINING IN NUMBER
2334 ,P_AMOUNT_APPLIED IN NUMBER
2335 ) RETURN VARCHAR2 IS
2336
2337 l_return VARCHAR2(1);
2338 ln_balance_sign NUMBER;
2339 ln_amount_due_original_sign NUMBER;
2340
2341 BEGIN
2342
2343 ln_balance_sign := SIGN(P_AMOUNT_DUE_REMAINING - P_AMOUNT_APPLIED);
2344 ln_amount_due_original_sign :=SIGN(P_AMOUNT_DUE_ORIGINAL);
2345
2346 IF (ln_balance_sign=-1 AND ln_amount_due_original_sign=1) THEN
2347 l_return :='Y';
2348
2349 ELSIF (ln_balance_sign=1 AND ln_amount_due_original_sign=-1) THEN
2350 l_return :='Y';
2351
2352 ELSE
2353 l_return :='N';
2354
2355 END IF;
2356
2357 RETURN(l_return);
2358
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 RAISE;
2362
2363 END OVERAPPLICATION_INDICATOR;
2364
2365
2366 /*========================================================================
2367 | PUBLIC FUNCTION CHECK_APP_VIOLATE
2368 |
2369 | DESCRIPTION
2370 | Function to determine whether amount entered in the screen
2371 | is violating Natural Applicatioon OR violating Over Application OR
2372 | is a valid Natural Application
2373 |
2374 | CALLED FROM PROCEDURES/FUNCTIONS
2375 | Called from WHEN-VALIDATE-ITEM trigger of the AMOUNT field in the
2376 | Multiple QuickCash window as well as the Receipt Application window
2377 |
2378 | CALLS PROCEDURES/FUNCTIONS
2379 |
2380 | PARAMETERS
2381 | IN:
2382 | P_AMOUNT IN NUMBER (amount passed from the form)
2383 | P_RAPP_ID IN NUMBER
2384 | P_CR_ID IN NUMBER
2385 | OUT:
2386 | RETURN ('NATURAL','OVER','NO')
2387 | NATURAL -> Natural Application Violation
2388 | OVER -> Over Application Violation
2389 | NO -> No Violation
2390 |
2391 | KNOWN ISSUES
2392 |
2393 | NOTES
2394 |
2395 | MODIFICATION HISTORY
2396 | Date Author Description of Changes
2397 | 23-JAN-2003 CTHANGAI Created
2398 | 17-FEB-2003 CTHANGAI natural application of the receipt is based on
2399 | to the payment schedule of the receipt
2400 *=======================================================================*/
2401 FUNCTION CHECK_APP_VIOLATE
2402 (p_amount IN ar_receivable_applications.amount_applied%TYPE
2403 ,p_rapp_id IN ar_receivable_applications.receivable_application_id%TYPE
2404 ,p_cr_id IN ar_receivable_applications.cash_receipt_id%TYPE
2405 ) RETURN VARCHAR2 IS
2406
2407 l_return VARCHAR2(10);
2408 ln_amount_applied ar_receivable_applications.amount_applied%TYPE;
2409
2410 invalid_param EXCEPTION;
2411
2412 BEGIN
2413
2414 IF PG_DEBUG in ('Y', 'C') THEN
2415 arp_standard.debug('arp_deduction.CHECK_APP_VIOLATE()+' );
2416 END IF;
2417
2418 IF (p_rapp_id IS NOT NULL) AND (p_cr_id IS NOT NULL) THEN --'On Account'
2419
2420 SELECT amount_applied
2421 INTO ln_amount_applied
2422 FROM ar_receivable_applications
2423 WHERE receivable_application_id = p_rapp_id;
2424
2425 ELSIF (p_rapp_id IS NULL) AND (p_cr_id IS NOT NULL) THEN --'Unapplied'
2426
2427 SELECT sum(amount_applied)
2428 INTO ln_amount_applied
2429 FROM ar_receivable_applications
2430 WHERE cash_receipt_id = p_cr_id
2431 AND status = 'UNAPP';
2432
2433 ELSE --'Raise Invalid Arguments'
2434
2435 APP_EXCEPTION.INVALID_ARGUMENT
2436 ('ARP_DEDUCTION.CHECK_APP_VIOLATE'
2437 ,'p_rapp_id'
2438 ,'NULL'
2439 );
2440
2441 IF PG_DEBUG in ('Y','C') THEN
2442 arp_standard.debug('Invalid Argument - p_rapp_id is Null');
2443 END IF;
2444
2445 l_return := NULL;
2446 RAISE invalid_param;
2447
2448 END IF; -- IF (p_rapp_id IS NOT NULL) AND (p_cr_id IS NOT NULL)
2449
2450 IF ( SIGN(ln_amount_applied * -1) = SIGN(p_amount) ) THEN
2451
2452 IF ( ABS(ln_amount_applied) < ABS(p_amount) ) THEN -- Over Application Violation
2453 l_return := 'OVER';
2454
2455 ELSE -- Natural Application
2456 l_return := 'NO';
2457
2458 END IF;
2459
2460 ELSE --Natural Application Violation
2461 l_return := 'NATURAL';
2462
2463 END IF; -- IF ( SIGN(ln_amount_applied * -1) = SIGN(p_amount) )
2464
2465 IF PG_DEBUG in ('Y', 'C') THEN
2466 arp_standard.debug('arp_deduction.CHECK_APP_VIOLATE()-' );
2467 END IF;
2468
2469 RETURN (l_return);
2470
2471 EXCEPTION
2472 WHEN invalid_param THEN
2473
2474 IF PG_DEBUG in ('Y', 'C') THEN
2475 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.CHECK_APP_VIOLATE');
2476 END IF;
2477 RETURN(l_return);
2478
2479 WHEN OTHERS THEN
2480
2481 IF PG_DEBUG in ('Y', 'C') THEN
2482 arp_standard.debug('EXCEPTION - OTHERS : ARP_DEDUCTION.CHECK_APP_VIOLATE');
2483 END IF;
2484 RETURN(l_return);
2485
2486 END CHECK_APP_VIOLATE;
2487
2488
2489 /*========================================================================
2490 | PUBLIC FUNCTION GET_TM_ORACLE_REASON
2491 |
2492 | DESCRIPTION
2493 | This function is called to retrieve the Oracle reason description
2494 | from TM
2495 |
2496 | SCOPE - PUBLIC
2497 |
2498 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2499 |
2500 | ARGUMENTS :
2501 | IN : TM Claim ID
2502 |
2503 | OUT: NONE
2504 |
2505 | RETURNS : VARCHAR (Oracle Reason From TM)
2506 |
2507 |
2508 | NOTES :
2509 |
2510 | MODIFICATION HISTORY
2511 | Date Author Description of Changes
2512 | 12-FEB-2003 cthangai Created
2513 | DD-MON-YYYY Name Bug #####, modified amount ..
2514 |
2515 *=======================================================================*/
2516 FUNCTION GET_TM_ORACLE_REASON
2517 (p_claim_id IN ar_receivable_applications.secondary_application_ref_id%TYPE
2518 ) RETURN VARCHAR2 IS
2519
2520 l_query_string VARCHAR2(2000);
2521 l_tm_oracle_reason ar_receivable_applications.customer_reason%type;
2522
2523 BEGIN
2524
2525 l_query_string :=
2526 ' SELECT rc.name FROM ozf_reason_codes_vl rc, ozf_claims c '||
2527 ' WHERE c.reason_code_id = rc.reason_code_id '||
2528 ' AND c.claim_id = :claim_id ';
2529
2530 BEGIN
2531 EXECUTE IMMEDIATE l_query_string
2532 INTO l_tm_oracle_reason
2533 USING p_claim_id;
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 FND_MESSAGE.set_name('AR','AR_RW_INVALID_CLAIM_ID');
2537 FND_MESSAGE.set_token('CLAIM_ID',p_claim_id);
2538 APP_EXCEPTION.raise_exception;
2539 END;
2540
2541 RETURN l_tm_oracle_reason;
2542
2543 EXCEPTION
2544 WHEN OTHERS THEN
2545
2546 IF PG_DEBUG in ('Y', 'C') THEN
2547 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_TM_ORACLE_REASON');
2548 END IF;
2549 RETURN NULL;
2550
2551 END GET_TM_ORACLE_REASON;
2552
2553
2554 /*========================================================================
2555 | PUBLIC PROCEDURE conc_req_log_msg
2556 |
2557 | DESCRIPTION
2558 | This procedure writes messages to the concurrent request log file
2559 |
2560 | SCOPE - PUBLIC
2561 |
2562 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2563 |
2564 | ARGUMENTS :
2565 | IN : message
2566 |
2567 | OUT: NONE
2568 |
2569 |
2570 | NOTES :
2571 |
2572 | MODIFICATION HISTORY
2573 | Date Author Description of Changes
2574 | 13-FEB-2003 cthangai Created
2575 | DD-MON-YYYY Name Bug #####
2576 |
2577 *=======================================================================*/
2578 PROCEDURE conc_req_log_msg (p_message IN VARCHAR2) IS
2579 BEGIN
2580
2581 fnd_file.put_line(FND_FILE.LOG, p_message);
2582
2583 END conc_req_log_msg;
2584
2585
2586 /*========================================================================
2587 | PUBLIC PROCEDURE conc_req_out_msg
2588 |
2589 | DESCRIPTION
2590 | This procedure writes messages to the concurrent request output file
2591 |
2592 | SCOPE - PUBLIC
2593 |
2594 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2595 |
2596 | ARGUMENTS :
2597 | IN : message
2598 |
2599 | OUT: NONE
2600 |
2601 |
2602 | NOTES :
2603 |
2604 | MODIFICATION HISTORY
2605 | Date Author Description of Changes
2606 | 13-FEB-2003 cthangai Created
2607 | DD-MON-YYYY Name Bug #####
2608 |
2609 *=======================================================================*/
2610 PROCEDURE conc_req_out_msg (p_message IN VARCHAR2) IS
2611 BEGIN
2612
2613 fnd_file.put_line(FND_FILE.OUTPUT, p_message);
2614
2615 END conc_req_out_msg;
2616
2617
2618 /*========================================================================
2619 | PUBLIC PROCEDURE apply_open_receipt_cover
2620 |
2621 | DESCRIPTION
2622 | This Procedure will be called from Postbatch to create receipt
2623 | to receipt applications. It will actually call the Receipt
2624 | Api which will created the rec apps records, distribution records
2625 | updates to payment schedules and the synch up with Trade management
2626 | if necessary.
2627 | This function will write messages to the concurrent request output file
2628 |
2629 | SCOPE - PUBLIC
2630 |
2631 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED
2632 |
2633 | AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT
2634 |
2635 | ARGUMENTS :
2636 | IN : p_cash_receipt_id
2637 | p_applied_payment_schedule_id
2638 | p_open_rec_app_id
2639 | p_amount_applied
2640 | p_attribute_category
2641 | p_attribute1
2642 | p_attribute2
2643 | p_attribute3
2644 | p_attribute4
2645 | p_attribute5
2646 | p_attribute6
2647 | p_attribute7
2648 | p_attribute8
2649 | p_attribute9
2650 | p_attribute10
2651 | p_attribute11
2652 | p_attribute12
2653 | p_attribute13
2654 | p_attribute14
2655 | p_attribute15
2656 |
2657 | OUT: X_RETURN_STAUS (S=success, E=Error, U=Unidentified Error)
2658 | x_receipt_number
2659 | X_APPLY_TYPE (F=Full, P=Partial)
2660 |
2661 | NOTES
2662 | This routine currently does not support receipt to receipt applications
2663 | which are in a currency other then functional currency.
2664 |
2665 | MODIFICATION HISTORY
2666 | Date Author Description of Changes
2667 | 19-FEB-2003 cthangai Created
2668 | Modify get_open_cr_id cursor to include receipt
2669 | number by joining to ar_cash_receipts
2670 | Modify cursor c1_validate to get amount_applied
2671 | Modify cursor c2_validate to get
2672 | sum(amount_applied)
2673 | Amount_applied retreived is used in determining
2674 | Full or Partial payment
2675 | Add OUT parameters x_return_status, x_apply_type
2676 | 03-MAR-2003 Debbie Jancis Added Comments and formatting. Fixed how
2677 | x_apply_type was figuring out whether
2678 | application was full or partial. For a
2679 | receipt to receipt application, the sign
2680 | of the the p_amount_applied variable will
2681 | always be the opposite sign of the
2682 | l_amount_applied value retrieved from the
2683 | receivable apps record of the applied receipt.
2684 | 07-MAR-2003 cthangai Added OUT parameter x_application_ref_num
2685 | for the TM API call to apply_open_receipt
2686 | local variable l_application_ref_num
2687 |
2688 *=======================================================================*/
2689 PROCEDURE apply_open_receipt_cover
2690 (p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
2691 p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE
2692 ,p_open_rec_app_id IN
2693 ar_receivable_applications.receivable_application_id%TYPE
2694 ,p_amount_applied IN
2695 ar_receivable_applications.amount_applied%TYPE
2696 ,p_attribute_category IN
2697 ar_receivable_applications.attribute_category%TYPE
2698 ,p_attribute1 IN ar_receivable_applications.attribute1%TYPE
2699 ,p_attribute2 IN ar_receivable_applications.attribute2%TYPE
2700 ,p_attribute3 IN ar_receivable_applications.attribute3%TYPE
2701 ,p_attribute4 IN ar_receivable_applications.attribute4%TYPE
2702 ,p_attribute5 IN ar_receivable_applications.attribute5%TYPE
2703 ,p_attribute6 IN ar_receivable_applications.attribute6%TYPE
2704 ,p_attribute7 IN ar_receivable_applications.attribute7%TYPE
2705 ,p_attribute8 IN ar_receivable_applications.attribute8%TYPE
2706 ,p_attribute9 IN ar_receivable_applications.attribute9%TYPE
2707 ,p_attribute10 IN ar_receivable_applications.attribute10%TYPE
2708 ,p_attribute11 IN ar_receivable_applications.attribute11%TYPE
2709 ,p_attribute12 IN ar_receivable_applications.attribute12%TYPE
2710 ,p_attribute13 IN ar_receivable_applications.attribute13%TYPE
2711 ,p_attribute14 IN ar_receivable_applications.attribute14%TYPE
2712 ,p_attribute15 IN ar_receivable_applications.attribute15%TYPE
2713 ,x_return_status OUT NOCOPY VARCHAR2
2714 ,x_receipt_number OUT NOCOPY ar_cash_receipts.receipt_number%TYPE
2715 ,x_apply_type OUT NOCOPY VARCHAR2
2716 ) IS
2717
2718 --Fetch open_cash_receipt_id
2719 CURSOR get_open_cr_id
2720 (p_applied_ps_id ar_payment_schedules.payment_schedule_id%TYPE) IS
2721 SELECT ps.cash_receipt_id, cr.receipt_number
2722 FROM ar_payment_schedules ps
2723 ,ar_cash_receipts cr
2724 WHERE ps.payment_schedule_id = p_applied_ps_id
2725 AND ps.cash_receipt_id = cr.cash_receipt_id;
2726
2727 --Fetch from receivable application if application is valid
2728 --for receipt to receipt application. Application is 'ACC' or 'OTHER ACC'
2729 CURSOR c1_validate
2730 (p_rapp_id ar_receivable_applications.receivable_application_id%TYPE) IS
2731 SELECT amount_applied --'Y'
2732 FROM ar_receivable_applications
2733 WHERE receivable_application_id = p_rapp_id
2734 AND display = 'Y';
2735
2736 --Fetch from receivable application if application is valid
2737 --for receipt to receipt application. Application is 'UNAPP'
2738 CURSOR c2_validate
2739 (p_cr_id ar_receivable_applications.cash_receipt_id%TYPE) IS
2740 SELECT sum(amount_applied)
2741 FROM ar_receivable_applications
2742 WHERE cash_receipt_id = p_cr_id
2743 AND status = 'UNAPP'
2744 HAVING sum(amount_applied) >= p_amount_applied;
2745
2746 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
2747 l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type;
2748 l_global_attribute_rec_null ar_receipt_api_pub.global_attribute_rec_type;
2749 l_return_status VARCHAR2(1); --'E','U' = ERROR ;; 'S'=Success;;
2750 l_msg_count NUMBER;
2751 l_msg_data VARCHAR2(2000);
2752 l_called_from VARCHAR2(255) := 'ARCABP'; --PostBatch Pro*C
2753 l_process VARCHAR2(1); --'Y'=Process 'N'=Stop Process
2754 l_amount_applied ar_receivable_applications.amount_applied%TYPE;
2755 l_open_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2756 l_open_receipt_number ar_cash_receipts.receipt_number%type;
2757 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
2758 l_receivable_application_id ar_receivable_applications.application_ref_num%TYPE;
2759 l_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
2760 l_acctd_amount_applied_from NUMBER;
2761 l_acctd_amount_applied_to NUMBER;
2762
2763 invalid_param EXCEPTION;
2764 amount_applied_null EXCEPTION;
2765
2766 BEGIN
2767
2768 IF PG_DEBUG in ('Y', 'C') THEN
2769 arp_standard.debug('update_action: arp_deduction.apply_open_receipt_cover()+');
2770 END IF;
2771
2772 ---------------------------------------------------------------
2773 -- Fetch open_cash_receipt_id using applied_payment_schedule_id
2774 ---------------------------------------------------------------
2775 IF (p_applied_payment_schedule_id IS NOT NULL) THEN
2776 OPEN get_open_cr_id (p_applied_payment_schedule_id);
2777 FETCH get_open_cr_id into l_open_cash_receipt_id ,l_open_receipt_number;
2778
2779 IF get_open_cr_id%NOTFOUND THEN
2780 IF PG_DEBUG IN ('Y','C') THEN
2781 arp_standard.debug('No Data Found : Invalid p_applied_payment_schedule_id');
2782 END IF;
2783 CLOSE get_open_cr_id;
2784 RAISE invalid_param;
2785
2786 ELSE
2787 x_receipt_number := l_open_receipt_number;
2788
2789 END IF;
2790
2791 CLOSE get_open_cr_id;
2792
2793 ELSE
2794 APP_EXCEPTION.INVALID_ARGUMENT
2795 ('ARP_DEDUCTION.APPLY_OPEN_RECEIPT_COVER'
2796 ,'P_APPLIED_PAYMENT_SCHEDULE_ID'
2797 ,'NULL'
2798 );
2799
2800 IF PG_DEBUG IN ('Y','C') THEN
2801 arp_standard.debug('Invalid Argument - Applied Payment Schedule ID IS NULL');
2802 END IF;
2803 RAISE invalid_param;
2804
2805 END IF; --IF (p_applied_payment_schedule_id IS NOT NULL)
2806
2807 -------------------------------------
2808 -- Initialize process flag. Y=Process
2809 -------------------------------------
2810 l_process := 'Y';
2811
2812 ------------------------------------------------------------------------------
2813 -- Determine if the receipt api should be called and set l_process='Y' on
2814 -- success. For 'ACC' or 'OTHER ACC', the receivable application must have
2815 -- display='Y'.
2816 -- For 'UNAPP' record, the sum(amount_applied) for the cash receipt must
2817 -- be greater than or equal to the amount being applied to the open receipt
2818 ------------------------------------------------------------------------------
2819 IF (p_open_rec_app_id > 0) AND (l_open_cash_receipt_id IS NOT NULL) THEN
2820
2821 -- 'ACC' OR 'OTHER ACC'
2822 OPEN c1_validate (p_open_rec_app_id);
2823 FETCH c1_validate INTO l_amount_applied;
2824
2825 IF c1_validate%NOTFOUND THEN
2826
2827 x_return_status := FND_API.G_RET_STS_ERROR;
2828 l_process := 'N';
2829
2830 arp_deduction.conc_req_log_msg
2831 ('Failed Validation : display<>Y for the application. No Call will be '
2832 ||' initiated for receipt API - apply_open_receipt'
2833 ||' Receivable Application id = '||to_char(p_open_rec_app_id)
2834 ||' Cash Receipt id = '||to_char(l_open_cash_receipt_id));
2835
2836 END IF;-- IF c1_validate%NOTFOUND
2837
2838 CLOSE c1_validate;
2839
2840 ELSIF (p_open_rec_app_id <= 0) AND (l_open_cash_receipt_id IS NOT NULL) THEN
2841
2842 -- 'UNAPP'
2843 OPEN c2_validate (l_open_cash_receipt_id);
2844 FETCH c2_validate INTO l_amount_applied;
2845
2846 IF c2_validate%NOTFOUND THEN
2847
2848 x_return_status := FND_API.G_RET_STS_ERROR;
2849 l_process := 'N';
2850
2851 arp_deduction.conc_req_log_msg
2852 ('Failed Validation : sum(amount_applied) is less than apply_amount for '
2853 ||' UNAPP. No call will be initiated for receipt API - apply_open_receipt'
2854 ||' Cash Receipt ID = '||to_char(l_open_cash_receipt_id));
2855
2856 END IF; --IF c2_validate%NOTFOUND
2857
2858 CLOSE c2_validate;
2859
2860 END IF; --IF (p_open_rec_app_id IS NOT NULL) AND (l_open_cash_receipt_id IS NOT NULL)
2861
2862 -------------------------------------------------------
2863 -- Determine if l_process='Y' to process call to TM API
2864 -------------------------------------------------------
2865 IF l_process = 'Y' THEN
2866
2867 -----------------------------------------------
2868 -- Determine if apply amount is Partial or Full
2869 -- 'P' for Partial and 'F' for full
2870 -----------------------------------------------
2871 IF l_amount_applied <> -p_amount_applied THEN
2872 x_apply_type := 'P';
2873
2874 ELSIF l_amount_applied = -p_amount_applied THEN
2875 x_apply_type := 'F';
2876
2877 END IF;
2878
2879 -------------------------------------------------------------
2880 -- Initialize the record type variables with the correspoding
2881 -- parameter values
2882 -------------------------------------------------------------
2883 l_attribute_rec.attribute_category := p_attribute_category;
2884 l_attribute_rec.attribute1 := p_attribute1;
2885 l_attribute_rec.attribute2 := p_attribute2;
2886 l_attribute_rec.attribute3 := p_attribute3;
2887 l_attribute_rec.attribute4 := p_attribute4;
2888 l_attribute_rec.attribute5 := p_attribute5;
2889 l_attribute_rec.attribute6 := p_attribute6;
2890 l_attribute_rec.attribute7 := p_attribute7;
2891 l_attribute_rec.attribute8 := p_attribute8;
2892 l_attribute_rec.attribute9 := p_attribute9;
2893 l_attribute_rec.attribute10 := p_attribute10;
2894 l_attribute_rec.attribute11 := p_attribute11;
2895 l_attribute_rec.attribute12 := p_attribute12;
2896 l_attribute_rec.attribute13 := p_attribute13;
2897 l_attribute_rec.attribute14 := p_attribute14;
2898 l_attribute_rec.attribute15 := p_attribute15;
2899 l_global_attribute_rec := l_global_attribute_rec_null;
2900
2901 --------------------------------------------
2902 -- Call to receipt API to Apply Open Receipt
2903 --------------------------------------------
2904 ar_receipt_api_pub.apply_open_receipt
2905 (p_api_version => 1.0
2906 ,p_init_msg_list => FND_API.G_TRUE
2907 ,p_commit => FND_API.G_FALSE
2908 ,x_return_status => l_return_status
2909 ,x_msg_count => l_msg_count
2910 ,x_msg_data => l_msg_data
2911 ,p_cash_receipt_id => p_cash_receipt_id
2912 ,p_applied_payment_schedule_id => p_applied_payment_schedule_id
2913 ,p_open_cash_receipt_id => l_open_cash_receipt_id
2914 ,p_open_receipt_number => l_open_receipt_number
2915 ,p_open_rec_app_id => p_open_rec_app_id
2916 ,p_amount_applied => p_amount_applied
2917 ,p_called_from => l_called_from
2918 ,p_attribute_rec => l_attribute_rec
2919 ,p_global_attribute_rec => l_global_attribute_rec
2920 ,x_application_ref_num => l_application_ref_num
2921 ,x_receivable_application_id => l_receivable_application_id
2922 ,x_applied_rec_app_id => l_applied_rec_app_id
2923 ,x_acctd_amount_applied_from => l_acctd_amount_applied_from
2924 ,x_acctd_amount_applied_to => l_acctd_amount_applied_to
2925 );
2926
2927 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2928 x_return_status := 'S';
2929
2930 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2931 x_return_status := 'E';
2932 arp_deduction.conc_req_log_msg
2933 ('FAILURE - ERROR : Return status = E from API call apply_open_receipt');
2934
2935 ELSE
2936 x_return_status := 'U';
2937 arp_deduction.conc_req_log_msg
2938 ('FAILURE - UNIDENTIFIED : Return Status = U from API call apply_open_receipt');
2939
2940 END IF; --IF l_return_status = FND_API.G_RET_STS_SUCCESS
2941
2942 END IF; --IF l_process = 'Y' THEN
2943
2944 IF PG_DEBUG in ('Y','C') THEN
2945 arp_standard.debug('update_action: ARP_DEDUCTION.apply_open_receipt_cover()-');
2946 END IF;
2947
2948 EXCEPTION
2949 WHEN invalid_param THEN
2950
2951 x_return_status := FND_API.G_RET_STS_ERROR;
2952
2953 IF PG_DEBUG in ('Y','C') THEN
2954 arp_standard.debug('EXCEPTION - invalid_param : ARP_DEDUCTION.apply_open_receipt_cover()-');
2955 END IF;
2956
2957 RAISE;
2958
2959 WHEN OTHERS THEN
2960
2961 x_return_status := FND_API.G_RET_STS_ERROR;
2962
2963 arp_deduction.conc_req_log_msg
2964 ('EXCEPTION - WHEN OTHERS : ARP_DEDUCTION.apply_open_receipt_cover()-');
2965 arp_deduction.conc_req_log_msg('SQLERRM='||substr(SQLERRM,1,255));
2966
2967 RAISE;
2968
2969 END apply_open_receipt_cover;
2970
2971 /*========================================================================
2972 | PUBLIC FUNCTION GET_ACTIVE_CLAIM_FLAG
2973 |
2974 | DESCRIPTION
2975 | This function returns the value of the active claim flag which is
2976 | stored on the Payment Schedule of a transaction. This flag indicates
2977 | whether or not an active claim exists in trade management.
2978 |
2979 | SCOPE - PUBLIC
2980 |
2981 | EXTERNAL PROCEDURE/FUNCTIONS ACCESSED - NONE
2982 |
2983 | ARGUMENTS :
2984 | IN : payment_schedule_id
2985 | IN : payment_schedule_id
2986 |
2987 | RETURNS: ACTIVE_CLAIM_FLAG (for that payment schedule id)
2988 |
2989 | KNOWN ISSUES
2990 |
2991 | NOTES :
2992 |
2993 | MODIFICATION HISTORY
2994 | Date Author Description of Changes
2995 | 03-JUN-2003 Debbie Jancis Created
2996 |
2997 *=======================================================================*/
2998 FUNCTION GET_ACTIVE_CLAIM_FLAG(
2999 p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE)
3000 RETURN VARCHAR2 IS
3001
3002 l_active_claim_flag ar_payment_schedules.active_claim_flag%type;
3003
3004 BEGIN
3005
3006 SELECT nvl(active_claim_flag,'N')
3007 INTO l_active_claim_flag
3008 FROM ar_payment_schedules
3009 WHERE payment_schedule_id = p_payment_schedule_id;
3010
3011 RETURN l_active_claim_flag;
3012
3013 EXCEPTION
3014 WHEN OTHERS THEN
3015
3016 IF PG_DEBUG in ('Y', 'C') THEN
3017 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.GET_ACTIVE_CLAIM_FLAG');
3018 END IF;
3019 RETURN NULL;
3020
3021 END GET_ACTIVE_CLAIM_FLAG;
3022
3023
3024
3025
3026
3027 /*========================================================================
3028 | INITIALIZATION SECTION
3029 |
3030 | DESCRIPTION
3031 | Enter a brief description of what this section does.
3032 | ----------------------------------------
3033 | This does the following ......
3034 |
3035 | KNOWN ISSUES
3036 | Enter business functionality which was de-scoped as part of the
3037 | implementation. Ideally this should never be used.
3038 |
3039 | NOTES
3040 | Any interesting aspect of the code in this section
3041 |
3042 | MODIFICATION HISTORY
3043 | Date Author Description of Changes
3044 | DD-MON-YYYY Name Created
3045 | DD-MON-YYYY Name Bug #####, modified amount ..
3046 |
3047 *=======================================================================*/
3048 BEGIN
3049
3050 null;
3051
3052 EXCEPTION
3053 WHEN NO_DATA_FOUND THEN
3054
3055 IF PG_DEBUG in ('Y', 'C') THEN
3056 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.INITIALIZE');
3057 END IF;
3058 RAISE;
3059
3060 WHEN OTHERS THEN
3061
3062 IF PG_DEBUG in ('Y', 'C') THEN
3063 arp_standard.debug('EXCEPTION: ARP_DEDUCTION.INITIALIZE');
3064 END IF;
3065 RAISE;
3066
3067 END ARP_DEDUCTION;