[Home] [Help]
PACKAGE BODY: APPS.AR_ACTIVITY_DETAILS_PKG
Source
1 PACKAGE BODY AR_ACTIVITY_DETAILS_PKG AS
2 /*$Header: ARRWLLTB.pls 120.4.12010000.8 2008/10/06 08:38:10 mpsingh ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE Insert_Row (
7 X_ROWID IN OUT NOCOPY VARCHAR2,
8 X_APPLY_TO IN VARCHAR2,
9 X_TAX_BALANCE IN NUMBER,
10 X_CUSTOMER_TRX_LINE_ID IN NUMBER,
11 X_COMMENTS IN VARCHAR2,
12 X_TAX IN NUMBER,
13 X_CASH_RECEIPT_ID IN NUMBER,
14 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
15 X_ALLOCATED_RECEIPT_AMOUNT IN NUMBER,
16 X_GROUP_ID IN NUMBER,
17 X_TAX_DISCOUNT IN NUMBER,
18 X_REFERENCE5 IN VARCHAR2,
19 X_REFERENCE4 IN VARCHAR2,
20 X_REFERENCE3 IN VARCHAR2,
21 X_AMOUNT IN NUMBER,
22 X_LINE_DISCOUNT IN NUMBER,
23 X_REFERENCE2 IN VARCHAR2,
24 X_REFERENCE1 IN VARCHAR2,
25 X_ATTRIBUTE9 IN VARCHAR2,
26 X_ATTRIBUTE8 IN VARCHAR2,
27 X_ATTRIBUTE7 IN VARCHAR2,
28 X_ATTRIBUTE6 IN VARCHAR2,
29 X_ATTRIBUTE5 IN VARCHAR2,
30 X_ATTRIBUTE4 IN VARCHAR2,
31 X_ATTRIBUTE3 IN VARCHAR2,
32 X_ATTRIBUTE2 IN VARCHAR2,
33 X_ATTRIBUTE1 IN VARCHAR2,
34 X_LINE_BALANCE IN NUMBER,
35 X_ATTRIBUTE15 IN VARCHAR2,
36 X_ATTRIBUTE14 IN VARCHAR2,
37 X_ATTRIBUTE13 IN VARCHAR2,
38 X_ATTRIBUTE12 IN VARCHAR2,
39 X_ATTRIBUTE11 IN VARCHAR2,
40 X_ATTRIBUTE10 IN VARCHAR2,
41 X_OBJECT_VERSION_NUMBER IN NUMBER,
42 X_CREATED_BY_MODULE IN VARCHAR2
43 ) IS
44
45 l_line_id NUMBER;
46
47 BEGIN
48
49 Select ar_activity_details_s.nextval
50 INTO l_line_id
51 from dual;
52
53 INSERT INTO AR_ACTIVITY_DETAILS (
54 LINE_ID,
55 APPLY_TO,
56 TAX_BALANCE,
57 CUSTOMER_TRX_LINE_ID,
58 COMMENTS,
59 TAX,
60 CASH_RECEIPT_ID,
61 ATTRIBUTE_CATEGORY,
62 ALLOCATED_RECEIPT_AMOUNT,
63 GROUP_ID,
64 TAX_DISCOUNT,
65 REFERENCE5,
66 REFERENCE4,
67 REFERENCE3,
68 AMOUNT,
69 LINE_DISCOUNT,
70 FREIGHT,
71 FREIGHT_DISCOUNT,
72 CHARGES,
73 REFERENCE2,
74 REFERENCE1,
75 ATTRIBUTE9,
76 ATTRIBUTE8,
77 ATTRIBUTE7,
78 ATTRIBUTE6,
79 ATTRIBUTE5,
80 ATTRIBUTE4,
81 ATTRIBUTE3,
82 ATTRIBUTE2,
83 ATTRIBUTE1,
84 LINE_BALANCE,
85 ATTRIBUTE15,
86 ATTRIBUTE14,
87 ATTRIBUTE13,
88 ATTRIBUTE12,
89 ATTRIBUTE11,
90 ATTRIBUTE10,
91 CREATED_BY,
92 CREATION_DATE,
93 LAST_UPDATE_LOGIN,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 OBJECT_VERSION_NUMBER,
97 CREATED_BY_MODULE,
98 SOURCE_TABLE,
99 CURRENT_ACTIVITY_FLAG
100 )
101 VALUES (
102 l_line_id,
103 DECODE(X_APPLY_TO, FND_API.G_MISS_CHAR, NULL , X_APPLY_TO),
104 DECODE(X_TAX_BALANCE, FND_API.G_MISS_NUM, NULL , X_TAX_BALANCE),
105 DECODE(X_CUSTOMER_TRX_LINE_ID, FND_API.G_MISS_NUM, NULL , X_CUSTOMER_TRX_LINE_ID),
106 DECODE(X_COMMENTS, FND_API.G_MISS_CHAR, NULL , X_COMMENTS),
107 DECODE(X_TAX, FND_API.G_MISS_NUM, NULL , X_TAX),
108 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
109 DECODE(X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE_CATEGORY),
110 DECODE(X_ALLOCATED_RECEIPT_AMOUNT, NULL,
111 nvl(X_AMOUNT,0) + nvl(X_TAX,0)
112 -- - nvl(X_LINE_DISCOUNT,0) - nvl(X_TAX_DISCOUNT,0) - nvl(X_FREIGHT_DISCOUNT,0)
113 , X_ALLOCATED_RECEIPT_AMOUNT),
114 DECODE(X_GROUP_ID, FND_API.G_MISS_NUM, NULL , X_GROUP_ID),
115 DECODE(X_TAX_DISCOUNT, FND_API.G_MISS_NUM, NULL , X_TAX_DISCOUNT),
116 DECODE(X_REFERENCE5, FND_API.G_MISS_CHAR, NULL , X_REFERENCE5),
117 DECODE(X_REFERENCE4, FND_API.G_MISS_CHAR, NULL , X_REFERENCE4),
118 DECODE(X_REFERENCE3, FND_API.G_MISS_CHAR, NULL , X_REFERENCE3),
119 DECODE(X_APPLY_TO, 'FREIGHT', 0, 'CHARGES', 0, X_AMOUNT ),
120 DECODE(X_APPLY_TO, 'FREIGHT', 0, 'CHARGES', 0, X_LINE_DISCOUNT),
121 DECODE(X_APPLY_TO, 'FREIGHT', X_AMOUNT, 0),
122 DECODE(X_APPLY_TO, 'FREIGHT', X_LINE_DISCOUNT, 0),
123 DECODE(X_APPLY_TO, 'CHARGES', X_AMOUNT, 0),
124 DECODE(X_REFERENCE2, FND_API.G_MISS_CHAR, NULL , X_REFERENCE2),
125 DECODE(X_REFERENCE1, FND_API.G_MISS_CHAR, NULL , X_REFERENCE1),
126 DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE9),
127 DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE8),
128 DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE7),
129 DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE6),
130 DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE5),
131 DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE4),
132 DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE3),
133 DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE2),
134 DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE1),
135 DECODE(X_LINE_BALANCE, FND_API.G_MISS_NUM, NULL , X_LINE_BALANCE),
136 DECODE(X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE15),
137 DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE14),
138 DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE13),
139 DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE12),
140 DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE11),
141 DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL , X_ATTRIBUTE10),
142 NVL(FND_GLOBAL.user_id,-1),
143 SYSDATE,
144 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
145 SYSDATE,
146 NVL(FND_GLOBAL.user_id,-1),
147 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
148 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
149 'RA',
150 'Y'
151 )RETURNING ROWID INTO X_ROWID;
152
153
154 END Insert_Row;
155
156 -- Bug 7241111
157 /*===========================================================================+
158 | PROCEDURE |
159 | offset_row |
160 | |
161 | DESCRIPTION |
162 | Insert the Offset row for updated/unapplied line |
163 | at apply_in_detail screen for a specific line. |
164 | |
165 | SCOPE - PUBLIC |
166 | |
167 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
168 | |
169 | ARGUMENTS : IN: |
170 | X_CUSTOMER_TRX_LINE_ID |
171 | X_CASH_RECEIPT_ID |
172 | |
173 | RETURNS : NONE |
174 | |
175 | NOTES |
176 | |
177 | MODIFICATION HISTORY - Created by mpsingh - 19-SEP-2008 |
178 +===========================================================================*/
179
180 PROCEDURE offset_row (
181 X_CUSTOMER_TRX_LINE_ID IN NUMBER,
182 X_CASH_RECEIPT_ID IN NUMBER
183 )
184 IS
185 l_line_id NUMBER;
186 BEGIN
187
188 IF PG_DEBUG in ('Y', 'C') THEN
189 arp_standard.debug('AR_ACTIVITY_DETAILS_PKG.offset_Row()+');
190 END IF;
191
192 Select ar_activity_details_s.nextval
193 INTO l_line_id
194 from dual;
195
196 INSERT INTO AR_ACTIVITY_DETAILS(
197 CASH_RECEIPT_ID,
198 CUSTOMER_TRX_LINE_ID,
199 ALLOCATED_RECEIPT_AMOUNT,
200 AMOUNT,
201 TAX,
202 FREIGHT,
203 CHARGES,
204 LAST_UPDATE_DATE,
205 LAST_UPDATED_BY,
206 LINE_DISCOUNT,
207 TAX_DISCOUNT,
208 FREIGHT_DISCOUNT,
209 LINE_BALANCE,
210 TAX_BALANCE,
211 CREATION_DATE,
212 CREATED_BY,
213 LAST_UPDATE_LOGIN,
214 COMMENTS,
215 APPLY_TO,
216 ATTRIBUTE1,
217 ATTRIBUTE2,
218 ATTRIBUTE3,
219 ATTRIBUTE4,
220 ATTRIBUTE5,
221 ATTRIBUTE6,
222 ATTRIBUTE7,
223 ATTRIBUTE8,
224 ATTRIBUTE9,
225 ATTRIBUTE10,
226 ATTRIBUTE11,
227 ATTRIBUTE12,
228 ATTRIBUTE13,
229 ATTRIBUTE14,
230 ATTRIBUTE15,
231 ATTRIBUTE_CATEGORY,
232 GROUP_ID,
233 REFERENCE1,
234 REFERENCE2,
235 REFERENCE3,
236 REFERENCE4,
237 REFERENCE5,
238 OBJECT_VERSION_NUMBER,
239 CREATED_BY_MODULE,
240 SOURCE_ID,
241 SOURCE_TABLE,
242 LINE_ID,
243 CURRENT_ACTIVITY_FLAG)
244 SELECT
245 LLD.CASH_RECEIPT_ID,
246 LLD.CUSTOMER_TRX_LINE_ID,
247 LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
248 LLD.AMOUNT*-1,
249 LLD.TAX*-1,
250 LLD.FREIGHT*-1,
251 LLD.CHARGES*-1,
252 LLD.LAST_UPDATE_DATE,
253 LLD.LAST_UPDATED_BY,
254 LLD.LINE_DISCOUNT,
255 LLD.TAX_DISCOUNT,
256 LLD.FREIGHT_DISCOUNT,
257 LLD.LINE_BALANCE,
258 LLD.TAX_BALANCE,
259 LLD.CREATION_DATE,
260 LLD.CREATED_BY,
261 LLD.LAST_UPDATE_LOGIN,
262 LLD.COMMENTS,
263 LLD.APPLY_TO,
264 LLD.ATTRIBUTE1,
265 LLD.ATTRIBUTE2,
266 LLD.ATTRIBUTE3,
267 LLD.ATTRIBUTE4,
268 LLD.ATTRIBUTE5,
269 LLD.ATTRIBUTE6,
270 LLD.ATTRIBUTE7,
271 LLD.ATTRIBUTE8,
272 LLD.ATTRIBUTE9,
273 LLD.ATTRIBUTE10,
274 LLD.ATTRIBUTE11,
275 LLD.ATTRIBUTE12,
276 LLD.ATTRIBUTE13,
277 LLD.ATTRIBUTE14,
278 LLD.ATTRIBUTE15,
279 LLD.ATTRIBUTE_CATEGORY,
280 LLD.GROUP_ID,
281 LLD.REFERENCE1,
282 LLD.REFERENCE2,
283 LLD.REFERENCE3,
284 LLD.REFERENCE4,
285 LLD.REFERENCE5,
286 LLD.OBJECT_VERSION_NUMBER,
287 LLD.CREATED_BY_MODULE,
288 LLD.SOURCE_ID,
289 LLD.SOURCE_TABLE,
290 l_line_id,
291 'R'
292 FROM ar_Activity_details LLD
293 WHERE 1 = 1 AND CUSTOMER_TRX_LINE_ID = X_CUSTOMER_TRX_LINE_ID
294 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
295 AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID;
296
297
298 UPDATE ar_Activity_details
299 set CURRENT_ACTIVITY_FLAG = 'N'
300 WHERE 1 = 1 AND CUSTOMER_TRX_LINE_ID = X_CUSTOMER_TRX_LINE_ID
301 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
302 AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID;
303
304 IF PG_DEBUG in ('Y', 'C') THEN
305 arp_standard.debug('AR_ACTIVITY_DETAILS_PKG.offset_Row()-');
306 END IF;
307
308 END;
309
310
311 /*===========================================================================+
312 | PROCEDURE |
313 | Update_Row |
314 | |
315 | DESCRIPTION |
316 | This routine changed with the bug 7241111, which deals with sync of |
317 | line level details under ar_activity_details with the Application |
318 | (APP rows ) of the AR_receivable_applications. |
319 | |
320 | This will enable user to get each line level application/unapplication |
321 | details corresponding to the RA ID under AR_receivable_applications |
322 | if the line level application is performed. |
323 | |
324 | SCOPE - PUBLIC |
325 | |
326 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
327 | |
328 | ARGUMENTS : IN: |
329 | |
330 | |
331 | RETURNS : NONE |
332 | |
333 | NOTES |
334 | |
335 | MODIFICATION HISTORY - Created by mpsingh - 19-SEP-2008 |
336 +===========================================================================*/
337
338
339 /*===========================================================================+
340 CODE LOGIC
341 1. Instead of updating the row, now we insert the offset
342 row under ar_activity_details with negative amount of the original row
343 ( routine used 'offset_row') with current_activity_flag as 'R' and source_id
344 is of original row. And Update the current_activity_flag as 'N' for the original row.
345
346 2. With the new updated amount we insert the new row under ar_activity_details,
347 having current_activity_flag as 'Y' and source_id as NULL.
348
349 3. Also from routine "reversal_insert_oppos_ra_recs " (ARCEAPPB.pls), we call routine
350 'Chk_offset_Row' to enter the offset rows and new rows for the lines which are
351 not updated/unapplied.
352
353 4. Now under routine "reversal_insert_oppos_ra_recs " (ARCEAPPB.pls), we update
354 the source_id for offset record inserted at point 1 and 3 with reversal APP record RA ID
355 along with current_activity_flag as 'N'.
356
357 5. The source_id of record point 2 and 3 will get updated with new APP record RA ID.
358
359
360
361 EXAMPLE :
362
363 1. Perform Line Level application for two lines for amount 100 and 200 to the receipt of amount
364 500.
365
366
367 Rows Under RA table
368 ~~~~~~~~~~~~~~~~~~~
369
370 RA ID AMOUNT STATUS
371 1 500 UNAPP
372 2 -300 UNAPP
373 3 300 APP
374
375 ROWS UNDER ACTIVITY table
376 ~~~~~~~~~~~~~~~~~~~~~~~~~
377 Source_id Amount Current_activity_flag
378 3 100 Y
379 3 200 Y
380
381
382
383 -- Now user updated the Line amount from 200 to 50.
384
385 Rows Under RA table
386 ~~~~~~~~~~~~~~~~~~~
387
388 RA ID AMOUNT STATUS
389 1 500 UNAPP
390 2 -300 UNAPP
391 3 300 APP
392 4 -300 APP
393 5 300 UNAPP
394 6 150 APP
395 7 -150 UNAPP
396
397
398 -- Now rows under ACTIVITY table
399
400 Here I am taking the intermediate data record also to understand the code flow.
401
402 AFTER POINT 1 :
403
404 ROWS UNDER ACTIVITY table
405 ~~~~~~~~~~~~~~~~~~~~~~~~~
406 Source_id Amount Current_activity_flag
407 3 100 Y
408 3 200 N
409 3 -200 R
410
411
412 AFTER POINT 2 :
413
414 ROWS UNDER ACTIVITY table
415 ~~~~~~~~~~~~~~~~~~~~~~~~~
416 Source_id Amount Current_activity_flag
420 - 50 Y
417 3 100 Y
418 3 200 N
419 3 -200 R
421
422 AFTER POINT 3 :
423
424 ROWS UNDER ACTIVITY table
425 ~~~~~~~~~~~~~~~~~~~~~~~~~
426 Source_id Amount Current_activity_flag
427 3 100 N
428 3 200 N
429 3 -200 R
430 - 50 Y
431 3 -100 R
432 - 100 Y
433
434
435 AFTER POINT 4 :
436
437 ROWS UNDER ACTIVITY table
438 ~~~~~~~~~~~~~~~~~~~~~~~~~
439 Source_id Amount Current_activity_flag
440 3 100 N
441 3 200 N
442 4 -200 N
443 - 50 Y
444 4 -100 N
445 - 100 Y
446
447 AFTER POINT 5 (Final look of Activity table):
448
449 ROWS UNDER ACTIVITY table
450 ~~~~~~~~~~~~~~~~~~~~~~~~~
451 Source_id Amount Current_activity_flag
452 3 100 N
453 3 200 N
454 4 -200 N
455 6 50 Y
456 4 -100 N
457 6 100 Y
458
459 +===========================================================================*/
460
461
462 PROCEDURE Update_Row (
463 X_APPLY_TO IN VARCHAR2,
464 X_TAX_BALANCE IN NUMBER,
465 X_CUSTOMER_TRX_LINE_ID IN NUMBER,
466 X_COMMENTS IN VARCHAR2,
467 X_TAX IN NUMBER,
468 X_CASH_RECEIPT_ID IN NUMBER,
469 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
470 X_ALLOCATED_RECEIPT_AMOUNT IN NUMBER,
471 X_GROUP_ID IN NUMBER,
472 X_TAX_DISCOUNT IN NUMBER,
473 X_REFERENCE5 IN VARCHAR2,
474 X_REFERENCE4 IN VARCHAR2,
475 X_REFERENCE3 IN VARCHAR2,
476 X_AMOUNT IN NUMBER,
477 X_LINE_DISCOUNT IN NUMBER,
478 X_REFERENCE2 IN VARCHAR2,
479 X_REFERENCE1 IN VARCHAR2,
480 X_ATTRIBUTE9 IN VARCHAR2,
481 X_ATTRIBUTE8 IN VARCHAR2,
482 X_ATTRIBUTE7 IN VARCHAR2,
483 X_ATTRIBUTE6 IN VARCHAR2,
484 X_ATTRIBUTE5 IN VARCHAR2,
485 X_ATTRIBUTE4 IN VARCHAR2,
486 X_ATTRIBUTE3 IN VARCHAR2,
487 X_ATTRIBUTE2 IN VARCHAR2,
488 X_ATTRIBUTE1 IN VARCHAR2,
489 X_LINE_BALANCE IN NUMBER,
490 X_ATTRIBUTE15 IN VARCHAR2,
491 X_ATTRIBUTE14 IN VARCHAR2,
492 X_ATTRIBUTE13 IN VARCHAR2,
493 X_ATTRIBUTE12 IN VARCHAR2,
494 X_ATTRIBUTE11 IN VARCHAR2,
495 X_ATTRIBUTE10 IN VARCHAR2,
496 X_OBJECT_VERSION_NUMBER IN NUMBER,
497 X_CREATED_BY_MODULE IN VARCHAR2
498 ) IS
499
500 p_rowid rowid;
501
502 BEGIN
503
504
505 -- Bug 7241111
506
507 offset_row(X_CUSTOMER_TRX_LINE_ID,
508 X_CASH_RECEIPT_ID);
509
510 -- Instead of update now inserting new rows with latest amounts.
511
512
513 insert_row(
514 x_rowid => p_rowid ,
515 x_cash_receipt_id => x_cash_receipt_id ,
516 x_customer_trx_line_id => x_customer_trx_line_id ,
517 x_attribute2 => x_attribute2 ,
518 x_attribute3 => x_attribute3 ,
519 x_attribute4 => x_attribute4 ,
520 x_attribute5 => x_attribute5 ,
521 x_attribute6 => x_attribute6 ,
522 x_attribute7 => x_attribute7 ,
523 x_attribute8 => x_attribute8 ,
524 x_attribute9 => x_attribute9 ,
525 x_attribute_category => x_attribute_category ,
526 x_allocated_receipt_amount => x_allocated_receipt_amount ,
527 x_amount => x_amount ,
528 x_tax => x_tax ,
529 x_line_discount => x_line_discount ,
530 x_tax_discount => x_tax_discount ,
531 x_line_balance => x_line_balance ,
532 x_tax_balance => x_tax_balance ,
533 x_comments => x_comments ,
534 x_apply_to => x_apply_to ,
535 x_attribute1 => x_attribute1 ,
536 x_attribute10 => x_attribute10 ,
537 x_attribute11 => x_attribute11 ,
538 x_attribute12 => x_attribute12 ,
539 x_attribute13 => x_attribute13 ,
540 x_attribute14 => x_attribute14 ,
541 x_attribute15 => x_attribute15 ,
542 x_group_id => x_group_id ,
543 x_object_version_number => x_object_version_number ,
544 x_created_by_module => x_created_by_module ,
545 x_reference1 => x_reference1 ,
549 x_reference5 => x_reference5
546 x_reference2 => x_reference2 ,
547 x_reference3 => x_reference3 ,
548 x_reference4 => x_reference4 ,
550 );
551
552 END Update_Row;
553
554
555
556 PROCEDURE Delete_Row (
557 X_CUSTOMER_TRX_LINE_ID IN NUMBER,
558 X_CASH_RECEIPT_ID IN NUMBER
559 ) IS
560
561
562 BEGIN
563
564 DELETE AR_ACTIVITY_DETAILS
565 WHERE 1 = 1 AND CUSTOMER_TRX_LINE_ID = X_CUSTOMER_TRX_LINE_ID
566 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- BUG 7241111
567 AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
568 ;
569
570
571 IF ( SQL%NOTFOUND ) THEN
572 -- 18 Oct 05 Need not raise error if there is no data
573 /*RAISE NO_DATA_FOUND;
574 */ null;
575 END IF;
576
577
578 END Delete_Row;
579
580 procedure select_summary (x_customer_Trx_id in number,
581 x_cash_receipt_id in number,
582 x_total in out NOCOPY number,
583 x_total_rtot_db in out NOCOPY number) IS
584 begin
585 select sum(nvl(line_discount,0)+nvl(tax_discount,0))
586 into x_total
587 from ar_ll_lines_groups_v
588 where customer_Trx_id = x_customer_Trx_id
589 and cash_receipt_id = x_cash_receipt_id;
590
591 x_total_rtot_db := x_total;
592
593 end select_summary;
594
595 /*===========================================================================+
596 | PROCEDURE |
597 | Chk_offset_Row |
598 | |
599 | DESCRIPTION |
600 | Inserts the remaining Offset rows incase of update/unapply |
601 | performs by user at apply_in_detail screen. |
602 | |
603 | SCOPE - PUBLIC |
604 | |
605 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
606 | |
607 | ARGUMENTS : IN: |
608 | X_RECEIVABLE_APPLICATION_ID |
609 | X_CASH_RECEIPT_ID |
610 | |
611 | RETURNS : NONE |
612 | |
613 | NOTES |
614 | |
615 | MODIFICATION HISTORY - Created by mpsingh - 19-SEP-2008 |
616 +===========================================================================*/
617
618 PROCEDURE Chk_offset_Row (
619 X_RECEIVABLE_APPLICATION_ID IN NUMBER,
620 X_OLD_RECEIVABLE_APP_ID IN NUMBER,
621 X_CASH_RECEIPT_ID IN NUMBER
622 ) is
623 l_activity_amt number;
624 l_ra_amt number;
625
626 BEGIN
627
628
629 IF PG_DEBUG in ('Y', 'C') THEN
630 arp_standard.debug('AR_ACTIVITY_DETAILS_PKG.Chk_offset_Row()+');
631 END IF;
632
633 begin
634
635 select sum( nvl(amount,0)+ nvl(tax,0)+ nvl(freight,0)+ nvl(charges,0) )
636 into l_activity_amt
637 from ar_Activity_details
638 where cash_receipt_id = X_CASH_RECEIPT_ID
639 and NVL(source_id,-1) = X_OLD_RECEIVABLE_APP_ID
640 and source_table = 'RA'
641 and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'R';
642
643
644 Select amount_applied into l_ra_amt
645 from ar_receivable_applications
646 where receivable_application_id = X_RECEIVABLE_APPLICATION_ID;
647
648
649 exception
650 when others then
651 IF PG_DEBUG in ('Y', 'C') THEN
652 arp_standard.debug('ERROR : Chk_offset_Row ' || ' UNABLE TO GET AMT TOTALS FOR ACTIVITY_DETAILS AND RA TABLES' );
653 END IF;
654 null;
655 end;
656
657 -- If the RA amount is different then the Activity table amount,we need to insert offset rows.
658
659 IF NVL(l_activity_amt,0) <> NVL(l_ra_amt,0) THEN
660
661 INSERT INTO AR_ACTIVITY_DETAILS(
662 CASH_RECEIPT_ID,
663 CUSTOMER_TRX_LINE_ID,
664 ALLOCATED_RECEIPT_AMOUNT,
665 AMOUNT,
666 TAX,
667 FREIGHT,
668 CHARGES,
669 LAST_UPDATE_DATE,
670 LAST_UPDATED_BY,
671 LINE_DISCOUNT,
672 TAX_DISCOUNT,
673 FREIGHT_DISCOUNT,
674 LINE_BALANCE,
675 TAX_BALANCE,
676 CREATION_DATE,
677 CREATED_BY,
678 LAST_UPDATE_LOGIN,
679 COMMENTS,
680 APPLY_TO,
681 ATTRIBUTE1,
685 ATTRIBUTE5,
682 ATTRIBUTE2,
683 ATTRIBUTE3,
684 ATTRIBUTE4,
686 ATTRIBUTE6,
687 ATTRIBUTE7,
688 ATTRIBUTE8,
689 ATTRIBUTE9,
690 ATTRIBUTE10,
691 ATTRIBUTE11,
692 ATTRIBUTE12,
693 ATTRIBUTE13,
694 ATTRIBUTE14,
695 ATTRIBUTE15,
696 ATTRIBUTE_CATEGORY,
697 GROUP_ID,
698 REFERENCE1,
699 REFERENCE2,
700 REFERENCE3,
701 REFERENCE4,
702 REFERENCE5,
703 OBJECT_VERSION_NUMBER,
704 CREATED_BY_MODULE,
705 SOURCE_ID,
706 SOURCE_TABLE,
707 LINE_ID,
708 CURRENT_ACTIVITY_FLAG,
709 OFFSET_REC_FLAG)
710 SELECT
711 LLD.CASH_RECEIPT_ID,
712 LLD.CUSTOMER_TRX_LINE_ID,
713 LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
714 LLD.AMOUNT*-1,
715 LLD.TAX*-1,
716 LLD.FREIGHT*-1,
717 LLD.CHARGES*-1,
718 LLD.LAST_UPDATE_DATE,
719 LLD.LAST_UPDATED_BY,
720 LLD.LINE_DISCOUNT,
721 LLD.TAX_DISCOUNT,
722 LLD.FREIGHT_DISCOUNT,
723 LLD.LINE_BALANCE,
724 LLD.TAX_BALANCE,
725 LLD.CREATION_DATE,
726 LLD.CREATED_BY,
727 LLD.LAST_UPDATE_LOGIN,
728 LLD.COMMENTS,
729 LLD.APPLY_TO,
730 LLD.ATTRIBUTE1,
731 LLD.ATTRIBUTE2,
732 LLD.ATTRIBUTE3,
733 LLD.ATTRIBUTE4,
734 LLD.ATTRIBUTE5,
735 LLD.ATTRIBUTE6,
736 LLD.ATTRIBUTE7,
737 LLD.ATTRIBUTE8,
738 LLD.ATTRIBUTE9,
739 LLD.ATTRIBUTE10,
740 LLD.ATTRIBUTE11,
741 LLD.ATTRIBUTE12,
742 LLD.ATTRIBUTE13,
743 LLD.ATTRIBUTE14,
744 LLD.ATTRIBUTE15,
745 LLD.ATTRIBUTE_CATEGORY,
746 LLD.GROUP_ID,
747 LLD.REFERENCE1,
748 LLD.REFERENCE2,
749 LLD.REFERENCE3,
750 LLD.REFERENCE4,
751 LLD.REFERENCE5,
752 LLD.OBJECT_VERSION_NUMBER,
753 LLD.CREATED_BY_MODULE,
754 LLD.SOURCE_ID,
755 LLD.SOURCE_TABLE,
756 ar_activity_details_s.nextval,
757 'R',
758 'Y'
759 FROM ar_Activity_details LLD
760 WHERE cash_receipt_id = X_CASH_RECEIPT_ID
761 and NVl(source_id,-1) = X_OLD_RECEIVABLE_APP_ID
762 and source_table = 'RA'
763 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
764
765 INSERT INTO AR_ACTIVITY_DETAILS(
766 CASH_RECEIPT_ID,
767 CUSTOMER_TRX_LINE_ID,
768 ALLOCATED_RECEIPT_AMOUNT,
769 AMOUNT,
770 TAX,
771 FREIGHT,
772 CHARGES,
773 LAST_UPDATE_DATE,
774 LAST_UPDATED_BY,
775 LINE_DISCOUNT,
776 TAX_DISCOUNT,
777 FREIGHT_DISCOUNT,
778 LINE_BALANCE,
779 TAX_BALANCE,
780 CREATION_DATE,
781 CREATED_BY,
782 LAST_UPDATE_LOGIN,
783 COMMENTS,
784 APPLY_TO,
785 ATTRIBUTE1,
786 ATTRIBUTE2,
787 ATTRIBUTE3,
788 ATTRIBUTE4,
789 ATTRIBUTE5,
790 ATTRIBUTE6,
791 ATTRIBUTE7,
792 ATTRIBUTE8,
793 ATTRIBUTE9,
794 ATTRIBUTE10,
795 ATTRIBUTE11,
796 ATTRIBUTE12,
797 ATTRIBUTE13,
798 ATTRIBUTE14,
799 ATTRIBUTE15,
800 ATTRIBUTE_CATEGORY,
804 REFERENCE3,
801 GROUP_ID,
802 REFERENCE1,
803 REFERENCE2,
805 REFERENCE4,
806 REFERENCE5,
807 OBJECT_VERSION_NUMBER,
808 CREATED_BY_MODULE,
809 SOURCE_ID,
810 SOURCE_TABLE,
811 LINE_ID,
812 CURRENT_ACTIVITY_FLAG,
813 OFFSET_REC_FLAG)
814 SELECT
815 LLD.CASH_RECEIPT_ID,
816 LLD.CUSTOMER_TRX_LINE_ID,
817 LLD.ALLOCATED_RECEIPT_AMOUNT,
818 LLD.AMOUNT,
819 LLD.TAX,
820 LLD.FREIGHT,
821 LLD.CHARGES,
822 sysdate,
823 NVL(FND_GLOBAL.user_id,-1),
824 LLD.LINE_DISCOUNT,
825 LLD.TAX_DISCOUNT,
826 LLD.FREIGHT_DISCOUNT,
827 LLD.LINE_BALANCE,
828 LLD.TAX_BALANCE,
829 sysdate,
830 NVL(FND_GLOBAL.user_id,-1),
831 NVL(arp_standard.profile.last_update_login,lld.last_update_login),
832 LLD.COMMENTS,
833 LLD.APPLY_TO,
834 LLD.ATTRIBUTE1,
835 LLD.ATTRIBUTE2,
836 LLD.ATTRIBUTE3,
837 LLD.ATTRIBUTE4,
838 LLD.ATTRIBUTE5,
839 LLD.ATTRIBUTE6,
840 LLD.ATTRIBUTE7,
841 LLD.ATTRIBUTE8,
842 LLD.ATTRIBUTE9,
843 LLD.ATTRIBUTE10,
844 LLD.ATTRIBUTE11,
845 LLD.ATTRIBUTE12,
846 LLD.ATTRIBUTE13,
847 LLD.ATTRIBUTE14,
848 LLD.ATTRIBUTE15,
849 LLD.ATTRIBUTE_CATEGORY,
850 LLD.GROUP_ID,
851 LLD.REFERENCE1,
852 LLD.REFERENCE2,
853 LLD.REFERENCE3,
854 LLD.REFERENCE4,
855 LLD.REFERENCE5,
856 LLD.OBJECT_VERSION_NUMBER,
857 LLD.CREATED_BY_MODULE,
858 NULL,
859 LLD.SOURCE_TABLE,
860 ar_activity_details_s.nextval,
861 'Y',
862 'Y'
863 FROM ar_Activity_details LLD
864 WHERE cash_receipt_id = X_CASH_RECEIPT_ID
865 and NVL(source_id,-1) = X_OLD_RECEIVABLE_APP_ID
866 and source_table = 'RA'
867 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
868
869
870 UPDATE ar_Activity_details
871 set CURRENT_ACTIVITY_FLAG = 'N'
872 WHERE cash_receipt_id = X_CASH_RECEIPT_ID
873 and NVL(source_id,-1) = X_OLD_RECEIVABLE_APP_ID
874 and source_table = 'RA'
875 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
876
877
878 END IF;
879
880
881 IF PG_DEBUG in ('Y', 'C') THEN
882 arp_standard.debug('Amount total under AR_ACTIVITY_DETAILS : '|| l_activity_amt );
883 arp_standard.debug('Amount total under RECEIVABLE_APPLICATIONS : '|| l_ra_amt );
884 END IF;
885
886
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_standard.debug('AR_ACTIVITY_DETAILS_PKG.Chk_offset_Row()-');
889 END IF;
890
891 END Chk_offset_Row;
892
893
894 END AR_ACTIVITY_DETAILS_PKG;