[Home] [Help]
PACKAGE BODY: APPS.AR_LL_RCV_SUMMARY_PKG
Source
1 PACKAGE BODY AR_LL_RCV_SUMMARY_PKG AS
2 /*$Header: ARRWSLTB.pls 120.9.12010000.6 2008/11/19 05:30:23 dgaurab ship $ */
3
4
5 PROCEDURE Delete_Row (
6 X_CUSTOMER_TRX_ID IN NUMBER,
7 X_CASH_RECEIPT_ID IN NUMBER
8 ) IS
9 BEGIN
10
11 DELETE AR_ACTIVITY_DETAILS
12 WHERE 1 = 1
13 AND CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
14 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- BUG 7241111
15 AND CUSTOMER_TRX_LINE_ID in (select customer_trx_line_id
16 from ra_customer_trx_lines
17 where customer_trx_id = X_CUSTOMER_TRX_ID);
18 IF ( SQL%NOTFOUND ) THEN
19 -- 17 Jan 2006, don't need to raise error, when there are no rows
20 /*RAISE NO_DATA_FOUND;
21 */ null;
22 END IF;
23 END Delete_Row;
24
25
26
27 PROCEDURE Lock_Row (
28 X_CUSTOMER_TRX_ID IN NUMBER,
29 X_CASH_RECEIPT_ID IN NUMBER,
30 x_object_Version_number in number
31 ) IS
32 BEGIN
33 null;
34 END Lock_Row;
35
36
37 PROCEDURE Insert_Row (
38 X_CASH_RECEIPT_ID IN NUMBER,
39 X_CUSTOMER_TRX_ID IN NUMBER,
40 X_lin in number,
41 x_tax in number ,
42 X_frt in number,
43 x_chg in number ,
44 X_lin_dsc in number,
45 x_tax_dsc in number ,
46 X_frt_dsc in number,
47 x_CREATED_BY_MODULE in varchar2
48 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency /* Bug 5189370 */
49 ,x_inv_to_rct_rate in number default 1
50 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
51 ,x_attribute_category IN varchar2 DEFAULT NULL
52 ,x_attribute1 IN varchar2 DEFAULT NULL
53 ,x_attribute2 IN varchar2 DEFAULT NULL
54 ,x_attribute3 IN varchar2 DEFAULT NULL
55 ,x_attribute4 IN varchar2 DEFAULT NULL
56 ,x_attribute5 IN varchar2 DEFAULT NULL
57 ,x_attribute6 IN varchar2 DEFAULT NULL
58 ,x_attribute7 IN varchar2 DEFAULT NULL
59 ,x_attribute8 IN varchar2 DEFAULT NULL
60 ,x_attribute9 IN varchar2 DEFAULT NULL
61 ,x_attribute10 IN varchar2 DEFAULT NULL
62 ,x_attribute11 IN varchar2 DEFAULT NULL
63 ,x_attribute12 IN varchar2 DEFAULT NULL
64 ,x_attribute13 IN varchar2 DEFAULT NULL
65 ,x_attribute14 IN varchar2 DEFAULT NULL
66 ,x_attribute15 IN varchar2 DEFAULT NULL
67 ) IS
68 begin
69 /*Bug 7311231, Added parameter p_attribute_rec and passed it to procedures
70 Insert_lintax_Rows, Insert_frt_Rows and Insert_chg_Rows */
71 insert_lintax_rows ( x_cash_receipt_id, x_customer_Trx_id, x_lin, x_tax,
72 x_lin_dsc, x_tax_dsc, x_created_by_module
73 ,x_inv_curr_code
74 ,x_inv_to_rct_rate,x_rct_curr_code
75 ,p_attribute_category => x_attribute_category
76 ,p_attribute1 => x_attribute1
77 ,p_attribute2 => x_attribute2
78 ,p_attribute3 => x_attribute3
79 ,p_attribute4 => x_attribute4
80 ,p_attribute5 => x_attribute5
81 ,p_attribute6 => x_attribute6
82 ,p_attribute7 => x_attribute7
83 ,p_attribute8 => x_attribute8
84 ,p_attribute9 => x_attribute9
85 ,p_attribute10 => x_attribute10
86 ,p_attribute11 => x_attribute11
87 ,p_attribute12 => x_attribute12
88 ,p_attribute13 => x_attribute13
89 ,p_attribute14 => x_attribute14
90 ,p_attribute15 => x_attribute15
91 );
92 insert_frt_rows (x_cash_receipt_id, x_customer_Trx_id, x_frt, x_frt_dsc,
93 x_created_by_module
94 ,x_inv_curr_code
95 ,x_inv_to_rct_rate,x_rct_curr_code
96 ,p_attribute_category => x_attribute_category
97 ,p_attribute1 => x_attribute1
98 ,p_attribute2 => x_attribute2
99 ,p_attribute3 => x_attribute3
100 ,p_attribute4 => x_attribute4
101 ,p_attribute5 => x_attribute5
102 ,p_attribute6 => x_attribute6
103 ,p_attribute7 => x_attribute7
104 ,p_attribute8 => x_attribute8
105 ,p_attribute9 => x_attribute9
106 ,p_attribute10 => x_attribute10
107 ,p_attribute11 => x_attribute11
108 ,p_attribute12 => x_attribute12
109 ,p_attribute13 => x_attribute13
110 ,p_attribute14 => x_attribute14
111 ,p_attribute15 => x_attribute15
112 );
113 insert_chg_rows (x_cash_receipt_id, x_customer_Trx_id, x_chg,
114 x_created_by_module
115 ,x_inv_curr_code
116 ,x_inv_to_rct_rate,x_rct_curr_code
117 ,p_attribute_category => x_attribute_category
118 ,p_attribute1 => x_attribute1
119 ,p_attribute2 => x_attribute2
120 ,p_attribute3 => x_attribute3
121 ,p_attribute4 => x_attribute4
122 ,p_attribute5 => x_attribute5
123 ,p_attribute6 => x_attribute6
124 ,p_attribute7 => x_attribute7
125 ,p_attribute8 => x_attribute8
126 ,p_attribute9 => x_attribute9
127 ,p_attribute10 => x_attribute10
128 ,p_attribute11 => x_attribute11
129 ,p_attribute12 => x_attribute12
130 ,p_attribute13 => x_attribute13
131 ,p_attribute14 => x_attribute14
132 ,p_attribute15 => x_attribute15
133 );
134 end;
135
136 PROCEDURE Insert_lintax_Rows (
137 X_CASH_RECEIPT_ID IN NUMBER,
138 X_CUSTOMER_TRX_ID IN NUMBER,
139 X_lin in number,
140 x_tax in number ,
141 X_lin_dsc in number,
142 x_tax_dsc in number ,
143 x_CREATED_BY_MODULE in varchar2
144 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
145 ,x_inv_to_rct_rate in number default 1
146 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
147 ,p_attribute_category IN varchar2 DEFAULT NULL
148 ,p_attribute1 IN varchar2 DEFAULT NULL
149 ,p_attribute2 IN varchar2 DEFAULT NULL
150 ,p_attribute3 IN varchar2 DEFAULT NULL
151 ,p_attribute4 IN varchar2 DEFAULT NULL
152 ,p_attribute5 IN varchar2 DEFAULT NULL
153 ,p_attribute6 IN varchar2 DEFAULT NULL
154 ,p_attribute7 IN varchar2 DEFAULT NULL
155 ,p_attribute8 IN varchar2 DEFAULT NULL
156 ,p_attribute9 IN varchar2 DEFAULT NULL
157 ,p_attribute10 IN varchar2 DEFAULT NULL
158 ,p_attribute11 IN varchar2 DEFAULT NULL
159 ,p_attribute12 IN varchar2 DEFAULT NULL
160 ,p_attribute13 IN varchar2 DEFAULT NULL
161 ,p_attribute14 IN varchar2 DEFAULT NULL
162 ,p_attribute15 IN varchar2 DEFAULT NULL
163 ) IS
164
165 cursor c_lintax
166 is
167 select
168 line.line_number apply_to,
169 line.customer_trx_line_id LINE_ID,
170 -- No nvl needed in the foll amounts since arp_process_det_pkg.initialization
171 -- would have updated the values to not-nulls
172 line.amount_due_remaining line_rem,
173 line.amount_due_original line_orig,
174 tax.amount_due_remaining tax_rem,
175 tax.amount_due_original tax_orig,
176 line.source_data_key4 group_id
177 from ra_customer_trx_lines line,
178 (select link_to_cust_trx_line_id,
179 line_type,
180 sum(nvl(amount_due_original,0)) amount_due_original,
181 sum(nvl(amount_due_remaining,0)) amount_due_remaining
182 from ra_customer_trx_lines
183 where nvl(line_type,'TAX') = 'TAX'
184 and customer_trx_id = x_customer_trx_id
185 group by link_to_cust_trx_line_id,
186 line_type
187 ) tax
188 where line.line_type = 'LINE'
189 and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
190 and line.customer_trx_id = x_customer_trx_id;
191
192 lintax_row c_lintax%rowtype;
193
194 line_count number;
195 iterator number := 1;
196
197 all_linrem_tot number;
198 all_linorig_tot number;
199 line_run_tot number := 0;
200 line_2b_applied number;
201
202 all_taxrem_tot number;
203 all_taxorig_tot number;
204 tax_run_tot number := 0;
205 tax_2b_applied number;
206
207 -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
208 lindsc_run_tot number := 0;
209 lindsc_2b_applied number;
210
211 taxdsc_run_tot number := 0;
212 taxdsc_2b_applied number;
213 -- End of additions for bug 4775656
214
215 --Used in proration (cumulation logic - bug 7307197)
216 x_run_line_amt number := 0;
217 x_run_tax_amt number := 0;
218 x_run_line_disc_amt number := 0;
219 x_run_tax_disc_amt number := 0;
220
221 --no need for the total for all lines, for amt_app_from
222 --since we are not pro-rating amt_app_from. To get amt_app_from
223 --we are just multiplying the inv_to_rct_rate into prorated amt
224 /*all_amt_app_from number;
225 amt_app_from_run_tot number := 0;*/
226 cross_currency_2b_applied number;
227
228 l_line_id NUMBER;
229
230 BEGIN
231 begin
232 select count(*) ,
233 sum(nvl(line.amount_due_remaining,0)),
234 sum(nvl(tax.amount_due_remaining,0)),
235 sum(nvl(line.amount_due_original,0)),
236 sum(nvl(tax.amount_due_original,0))
237 into line_count,
238 all_linrem_tot, all_taxrem_tot,
239 all_linorig_tot, all_taxorig_tot
240 from ra_customer_trx_lines line,
241 (select link_to_cust_trx_line_id,
242 line_type,
243 sum(nvl(amount_due_original,0)) amount_due_original,
244 sum(nvl(amount_due_remaining,0)) amount_due_remaining
245 from ra_customer_trx_lines
246 where nvl(line_type,'TAX') = 'TAX'
247 and customer_trx_id = x_customer_trx_id
248 group by link_to_cust_trx_line_id,
249 line_type
250 ) tax
251 where line.customer_trx_id = x_customer_trx_id
252 and line.line_type = 'LINE'
253 and line.customer_trx_line_id = tax.link_to_cust_trx_line_id (+)
254 ;
255 exception
256 when others then
257 arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
258 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
259 raise ;
260 end;
261 for lintax_row in c_lintax loop
262
263 -- Prorate the Line Amount
264 if iterator = line_count then
265 arp_standard.debug ('i='||to_char(iterator)||'.'|| 'THIS IS THE LAST. line_run_tot=' || line_run_tot);
266 line_2b_applied := nvl(x_lin,0) - line_run_tot;
267 tax_2b_applied := nvl(x_tax,0) - tax_run_tot;
268
269 -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
270 lindsc_2b_applied := nvl(x_lin_dsc,0) - lindsc_run_tot;
271 taxdsc_2b_applied := nvl(x_tax_dsc,0) - taxdsc_run_tot;
272 -- End of additions for bug 4775656
273 else -- If the adr on the invoice is zero, then
274 if all_linrem_tot > 0 then
275 arp_standard.debug ('i='||to_char(iterator)||'.'||
276 'NOT LAST, all_linrem_tot<>0. line_run_tot=' || line_run_tot
277 || '. all_linorig_tot=' || all_linorig_tot);
278 --line_2b_applied := arpcurr.currRound(lintax_row.line_rem * nvl(x_lin,0) / all_linrem_tot);
279 x_run_line_amt := x_run_line_amt + lintax_row.line_rem;
280 line_2b_applied := arpcurr.currRound(x_run_line_amt * nvl(x_lin,0) / all_linrem_tot) - line_run_tot;
281 else -- Overappl (all_linrem_tot < 0) should be done at the UI level,
282 -- so this means all_linrem_tot = 0
283 arp_standard.debug ('i='||to_char(iterator)||'.'||
284 'NOT LAST, all_linrem_tot=0. line_run_tot=' || line_run_tot
285 || '. all_linorig_tot=' || all_linorig_tot);
286 if all_linorig_tot <> 0 then
287 --line_2b_applied := arpcurr.currRound(lintax_row.line_orig * nvl(x_lin,0) / all_linorig_tot);
288 x_run_line_amt := x_run_line_amt + lintax_row.line_orig;
289 line_2b_applied := arpcurr.currRound(x_run_line_amt * nvl(x_lin,0) / all_linorig_tot) - line_run_tot;
290 else
291 line_2b_applied := 0;
292 end if;
293 end if;
294
295 --Prorate the Tax Amount
296
297 if all_taxrem_tot > 0 then
298 --tax_2b_applied := arpcurr.currRound(lintax_row.tax_rem * nvl(x_tax,0) / all_taxrem_tot);
299 x_run_tax_amt := x_run_tax_amt + lintax_row.tax_rem;
300 tax_2b_applied := arpcurr.currRound(x_run_tax_amt * nvl(x_tax,0) / all_taxrem_tot) - tax_run_tot;
301 else-- Overappl (all_taxrem_tot < 0) should be done at the UI level,
302 -- so this means all_taxrem_tot = 0
303 if all_taxorig_tot <> 0 then
304 --tax_2b_applied := arpcurr.currRound(lintax_row.tax_orig * nvl(x_tax,0) / all_taxorig_tot);
305 x_run_tax_amt := x_run_tax_amt + lintax_row.tax_orig;
306 tax_2b_applied := arpcurr.currRound(x_run_tax_amt * nvl(x_tax,0) / all_taxorig_tot) - tax_run_tot;
307 else
308 tax_2b_applied := 0;
309 end if;
310 end if ;
311
312 -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
313 -- Proate in the same ratio as that of the lin2bapplied / all_lin2bapplied_tot
314 if nvl(x_lin,0) <> 0 then
315 --lindsc_2b_applied := arpcurr.currRound(x_lin_dsc * nvl(line_2b_applied,0) / nvl(x_lin,0) );
316 lindsc_2b_applied := arpcurr.currRound(x_lin_dsc * nvl(line_run_tot,0) / nvl(x_lin,0) ) - lindsc_run_tot;
317 else
318 lindsc_2b_applied := 0;
319 end if;
320 -- Proate in the same ratio as that of the tax2bapplied / all_tax2bapplied_tot
321 if nvl(x_tax,0) <> 0 then
322 --taxdsc_2b_applied := arpcurr.currRound(x_tax_dsc * nvl(tax_2b_applied,0) / nvl(x_tax,0) );
323 taxdsc_2b_applied := arpcurr.currRound(x_tax_dsc * nvl(tax_run_tot,0) / nvl(x_tax,0) )- taxdsc_run_tot;
324 else
325 taxdsc_2b_applied := 0;
326 end if;
327 -- End of additions for bug 4775656
328 end if;
329
330 -- Calculate the Allocated Receipt Amount for the line
331 cross_currency_2b_applied := arp_util.currRound((line_2b_applied+tax_2b_applied) * nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
332 arp_standard.debug ('i='||to_char(iterator)||'.'||
333 'line_amount='||to_char(line_2b_applied)||'.'||
334 'tax_amount='||to_char(tax_2b_applied)||'.'||
335 'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
336 , 'plsql',
337 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
338
339 Select ar_Activity_details_s.nextval
340 INTO l_line_id
341 FROM DUAL;
342
343 /*Bug 7311231,Modified the code to insert Flexfield info in AR_ACTIVITY_DETAILS.*/
344 INSERT INTO AR_ACTIVITY_DETAILS (
345 LINE_ID,
346 APPLY_TO,
347 customer_trx_line_id,
348 CASH_RECEIPT_ID,
349 GROUP_ID,
350 AMOUNT,
351 allocated_receipt_amount,
352 TAX,
353 CREATED_BY,
354 CREATION_DATE,
355 LAST_UPDATE_LOGIN,
356 LAST_UPDATE_DATE,
357 LAST_UPDATED_BY,
358 OBJECT_VERSION_NUMBER,
359 CREATED_BY_MODULE,
360 SOURCE_TABLE,
361 line_discount,
362 tax_discount,
363 attribute_category,
364 attribute1,
365 attribute2,
366 attribute3,
367 attribute4,
368 attribute5,
369 attribute6,
370 attribute7,
371 attribute8,
372 attribute9,
373 attribute10,
374 attribute11,
375 attribute12,
376 attribute13,
377 attribute14,
378 attribute15,
379 CURRENT_ACTIVITY_FLAG
380 )
381
382 VALUES (
383 l_line_id,
384 lintax_row.apply_to,
385 lintax_row.line_id,
389 arp_util.currRound(nvl(cross_currency_2b_applied,0), x_rct_curr_code),
386 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
387 lintax_row.group_id,
388 arpcurr.currRound(nvl(line_2b_applied ,0),x_inv_curr_code),
390 arpcurr.currRound(nvl(tax_2b_applied ,0),x_inv_curr_code),
391 NVL(FND_GLOBAL.user_id,-1),
392 SYSDATE,
393 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
394 FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
395 SYSDATE,
396 NVL(FND_GLOBAL.user_id,-1),
397 0, -- Object Version Number is zero when the insert is at the group/summary level,
398 x_created_by_module,
399 'RA',
400 lindsc_2b_applied,
401 taxdsc_2b_applied,
402 p_attribute_category,
403 p_attribute1,
404 p_attribute2,
405 p_attribute3,
406 p_attribute4,
407 p_attribute5,
408 p_attribute6,
409 p_attribute7,
410 p_attribute8,
411 p_attribute9,
412 p_attribute10,
413 p_attribute11,
414 p_attribute12,
415 p_attribute13,
416 p_attribute14,
417 p_attribute15,
418 'Y'
419 );
420
421 line_run_tot := line_run_tot + line_2b_applied;
422 tax_run_tot := tax_run_tot + tax_2b_applied;
423 -- Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
424 lindsc_run_tot := lindsc_run_tot + lindsc_2b_applied;
425 taxdsc_run_tot := taxdsc_run_tot + taxdsc_2b_applied;
426 -- End of additions for bug 4775656
427 iterator := iterator + 1;
428 end loop;
429 END Insert_lintax_Rows;
430
431
432
433 PROCEDURE Insert_frt_Rows (
434 X_CASH_RECEIPT_ID IN NUMBER,
435 X_CUSTOMER_TRX_ID IN NUMBER,
436 X_frt in number,
437 X_frt_dsc in number,
438 x_CREATED_BY_MODULE in varchar2
439 -- Oct 04 added two param below
440 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
441 ,x_inv_to_rct_rate in number default 1
442 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
443 ,x_comments in varchar2 default null /* Bug 5453663 */
444 ,p_attribute_category IN varchar2 DEFAULT NULL
445 ,p_attribute1 IN varchar2 DEFAULT NULL
446 ,p_attribute2 IN varchar2 DEFAULT NULL
447 ,p_attribute3 IN varchar2 DEFAULT NULL
448 ,p_attribute4 IN varchar2 DEFAULT NULL
449 ,p_attribute5 IN varchar2 DEFAULT NULL
450 ,p_attribute6 IN varchar2 DEFAULT NULL
451 ,p_attribute7 IN varchar2 DEFAULT NULL
452 ,p_attribute8 IN varchar2 DEFAULT NULL
453 ,p_attribute9 IN varchar2 DEFAULT NULL
454 ,p_attribute10 IN varchar2 DEFAULT NULL
455 ,p_attribute11 IN varchar2 DEFAULT NULL
456 ,p_attribute12 IN varchar2 DEFAULT NULL
457 ,p_attribute13 IN varchar2 DEFAULT NULL
458 ,p_attribute14 IN varchar2 DEFAULT NULL
459 ,p_attribute15 IN varchar2 DEFAULT NULL
460 ) IS
461
462 cursor c_frt
463 is
464 select
465 'FREIGHT' apply_to,
466 line.customer_trx_line_id LINE_ID,
467 -- No nvl needed in the foll amounts since arp_process_det_pkg.initialization
468 -- would have updated the values to not-nulls
469 line.amount_due_remaining frt_rem,
470 line.amount_due_original frt_orig,
471 NULL group_id
472 from ra_customer_trx_lines line
473 where line.line_type = 'FREIGHT'
474 and line.customer_trx_id = x_customer_trx_id;
475
476 frt_row c_frt%rowtype;
477
478 line_count number;
479 iterator number := 1;
480
481 all_frtrem_tot number;
482 all_frtorig_tot number;
483 frt_run_tot number := 0;
484 frt_2b_applied number;
485
486 -- 2 lines Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
487 frtdsc_run_tot number := 0;
488 frtdsc_2b_applied number;
489
490 cross_currency_2b_applied number;
491 l_line_id NUMBER;
492 BEGIN
493 begin
494 select count(*) row_count,
495 sum(line.amount_due_remaining) all_frtrem_tot,
496 sum(line.amount_due_original) all_frtorig_tot
497 into line_count, all_frtrem_tot, all_frtorig_tot
498 from ra_customer_trx_lines line
499 where line.customer_trx_id = x_customer_trx_id
500 and line.line_type = 'FREIGHT'
501 ;
502 exception
503 when others then
504 arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
505 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
506 raise ;
507 end;
508 for frt_row in c_frt loop
509 if iterator = line_count then
510 frt_2b_applied := x_frt - frt_run_tot;
511 else
512 if all_frtrem_tot > 0 then
513 frt_2b_applied := arpcurr.currRound(frt_row.frt_rem * x_frt / all_frtrem_tot);
514 else -- Overappl (all_frtrem_tot < 0) should be done at the UI level,
515 -- so this means all_frtrem_tot = 0
516 if all_frtorig_tot <> 0 then
517 frt_2b_applied := arpcurr.currRound(frt_row.frt_orig * x_frt / all_frtorig_tot);
518 else
519 frt_2b_applied := 0;
520 end if;
521 end if;
522
523 end if;
524 -- Proate in the same ratio as that of the lin2bapplied / all_lin2bapplied_tot
525 if nvl(x_frt,0) <> 0 then
526 frtdsc_2b_applied := arpcurr.currRound(x_frt_dsc * nvl(frt_2b_applied,0) / nvl(x_frt,0) );
527 else
528 frtdsc_2b_applied := 0;
529 end if;
530 arp_standard.debug ('i='||to_char(iterator)||'.'||
531 'frt_amount='||to_char(frt_2b_applied)||'.'||
532 'frt_discount='||to_char(frtdsc_2b_applied)||'.'
533 , 'plsql',
534 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
535
536 Select ar_Activity_details_s.nextval
537 INTO l_line_id
538 FROM DUAL;
539
540 -- Calculate the Allocated Receipt Amount for the line
541 cross_currency_2b_applied := arp_util.currRound((frt_2b_applied) * nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
542 arp_standard.debug ('i='||to_char(iterator)||'.'||
543 'frt_amount='||to_char(frt_2b_applied)||'.'||
544 'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
545 , 'plsql',
546 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
547
548 /*Bug 7311231,Modified the code to insert Flexfield info in AR_ACTIVITY_DETAILS.*/
549 INSERT INTO AR_ACTIVITY_DETAILS (
550 LINE_ID,
551 APPLY_TO,
552 customer_trx_line_id,
553 CASH_RECEIPT_ID,
554 GROUP_ID,
555 AMOUNT,
556 COMMENTS,
557 CREATED_BY,
561 LAST_UPDATED_BY,
558 CREATION_DATE,
559 LAST_UPDATE_LOGIN,
560 LAST_UPDATE_DATE,
562 OBJECT_VERSION_NUMBER,
563 CREATED_BY_MODULE,
564 SOURCE_TABLE
565 -- 1 line added below Oct 26
566 , allocated_receipt_amount
567 -- 2 lines added below Dec 12
568 , freight
569 , freight_discount,
570 attribute_category,
571 attribute1,
572 attribute2,
573 attribute3,
574 attribute4,
575 attribute5,
576 attribute6,
577 attribute7,
578 attribute8,
579 attribute9,
580 attribute10,
581 attribute11,
582 attribute12,
583 attribute13,
584 attribute14,
585 attribute15,
586 CURRENT_ACTIVITY_FLAG
587 )
588
589 VALUES (
590 l_line_id,
591 frt_row.apply_to,
592 frt_row.line_id,
593 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
594 frt_row.GROUP_ID,
595 0, -- Bug 5189370 arpcurr.currRound(nvl(Frt_2b_applied ,0)),
596 X_COMMENTS,
597 NVL(FND_GLOBAL.user_id,-1),
598 SYSDATE,
599 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
600 FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
601 SYSDATE,
602 NVL(FND_GLOBAL.user_id,-1),
603 0, -- Object Version Number is zero when the insert is at the group/summary level,
604 x_created_by_module,
605 'RA'
606 -- 1 line added below added Oct 26
607 , cross_currency_2b_applied
608 -- 2 lines added below Dec 12
609 , arpcurr.currRound(nvl(Frt_2b_applied ,0),x_inv_curr_code)
610 , arpcurr.currRound(nvl(FrtDsc_2b_applied ,0),x_inv_curr_Code),
611 p_attribute_category,
612 p_attribute1,
613 p_attribute2,
614 p_attribute3,
615 p_attribute4,
616 p_attribute5,
617 p_attribute6,
618 p_attribute7,
619 p_attribute8,
620 p_attribute9,
621 p_attribute10,
622 p_attribute11,
623 p_attribute12,
624 p_attribute13,
625 p_attribute14,
626 p_attribute15,
627 'Y'
628 );
629
630 frt_run_tot := frt_run_tot + frt_2b_applied;
631 -- 1 line Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
632 frtdsc_run_tot := frtdsc_run_tot + frtdsc_2b_applied;
633 iterator := iterator + 1;
634 end loop;
635 END Insert_frt_Rows;
636
637
638 PROCEDURE Insert_chg_Rows (
639 X_CASH_RECEIPT_ID IN NUMBER,
640 X_CUSTOMER_TRX_ID IN NUMBER,
641 X_chg in number,
642 x_CREATED_BY_MODULE in varchar2
643 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
644 ,x_inv_to_rct_rate in number default 1
645 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
646 ,p_attribute_category IN varchar2 DEFAULT NULL
647 ,p_attribute1 IN varchar2 DEFAULT NULL
648 ,p_attribute2 IN varchar2 DEFAULT NULL
649 ,p_attribute3 IN varchar2 DEFAULT NULL
650 ,p_attribute4 IN varchar2 DEFAULT NULL
651 ,p_attribute5 IN varchar2 DEFAULT NULL
652 ,p_attribute6 IN varchar2 DEFAULT NULL
653 ,p_attribute7 IN varchar2 DEFAULT NULL
654 ,p_attribute8 IN varchar2 DEFAULT NULL
655 ,p_attribute9 IN varchar2 DEFAULT NULL
656 ,p_attribute10 IN varchar2 DEFAULT NULL
657 ,p_attribute11 IN varchar2 DEFAULT NULL
658 ,p_attribute12 IN varchar2 DEFAULT NULL
659 ,p_attribute13 IN varchar2 DEFAULT NULL
660 ,p_attribute14 IN varchar2 DEFAULT NULL
661 ,p_attribute15 IN varchar2 DEFAULT NULL
662 ) IS
663
664 cursor c_chg
665 is
666 select
667 'CHARGES' apply_to,
668 line.customer_trx_line_id LINE_ID,
669 -- No nvl needed in the foll amounts since arp_process_det_pkg.initialization
670 -- would have updated the values to not-nulls
671 line.amount_due_remaining chg_rem,
672 line.amount_due_original chg_orig,
673 NULL group_id
674 from ra_customer_trx_lines line
675 where line.line_type = 'CHARGES'
676 and line.customer_trx_id = x_customer_trx_id;
677
678 chg_row c_chg%rowtype;
679
680 line_count number;
681 iterator number := 1;
682
683 all_chgrem_tot number;
684 all_chgorig_tot number;
685 chg_run_tot number := 0;
686 chg_2b_applied number;
687
688 cross_currency_2b_applied number;
689 l_line_id NUMBER;
690 BEGIN
691 begin
692 select count(*) row_count,
693 sum(nvl(line.amount_due_remaining,0)),
694 sum(nvl(line.amount_due_remaining,0))
695 into line_count, all_chgrem_tot,
696 all_chgorig_tot
697 from ra_customer_trx_lines line
698 where line.customer_trx_id = x_customer_trx_id
699 and line.line_type = 'CHARGES'
700 ;
701 exception
702 when others then
703 arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
704 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
705 raise ;
706 end;
707 for chg_row in c_chg loop
708 if iterator = line_count then
709 chg_2b_applied := x_chg - chg_run_tot;
710 else
711 if all_chgrem_tot > 0 then
712 chg_2b_applied := arpcurr.currRound(chg_row.chg_rem * x_chg / all_chgrem_tot);
713 else -- Overappl (all_chgrem_tot < 0) should be done at the UI level,
714 -- so this means all_chgrem_tot = 0
715 if all_chgorig_tot <> 0 then
716 chg_2b_applied := arpcurr.currRound(chg_row.chg_orig * x_chg / all_chgorig_tot);
717 else
718 chg_2b_applied := 0;
719 end if;
723 'chg_amount='||to_char(chg_2b_applied)||'.'
720 end if;
721 end if;
722 arp_standard.debug ('i='||to_char(iterator)||'.'||
724 , 'plsql',
725 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
726
727 Select ar_Activity_details_s.nextval
728 INTO l_line_id
729 FROM DUAL;
730
731
732 -- Calculate the Allocated Receipt Amount for the line
733 cross_currency_2b_applied := arp_util.currRound((chg_2b_applied) *
734 nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
735 arp_standard.debug ('i='||to_char(iterator)||'.'||
736 'chg_amount='||to_char(chg_2b_applied)||'.'||
737 'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
738 , 'plsql',
739 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
740
741 /*Bug 7311231,Modified the code to insert Flexfield info in AR_ACTIVITY_DETAILS.*/
742 INSERT INTO AR_ACTIVITY_DETAILS (
743 LINE_ID,
744 APPLY_TO,
745 customer_trx_line_id,
746 CASH_RECEIPT_ID,
747 GROUP_ID,
748 AMOUNT,
749 CREATED_BY,
750 CREATION_DATE,
751 LAST_UPDATE_LOGIN,
752 LAST_UPDATE_DATE,
753 LAST_UPDATED_BY,
754 OBJECT_VERSION_NUMBER,
755 CREATED_BY_MODULE,
756 SOURCE_TABLE
757 -- 1 line added below Oct 26
758 , allocated_receipt_amount
759 -- 1 line added below Dec 12
760 , charges,
761 attribute_category,
762 attribute1,
763 attribute2,
764 attribute3,
765 attribute4,
766 attribute5,
767 attribute6,
768 attribute7,
769 attribute8,
770 attribute9,
771 attribute10,
772 attribute11,
773 attribute12,
774 attribute13,
775 attribute14,
776 attribute15,
777 CURRENT_ACTIVITY_FLAG
778 )
779
780 VALUES (
781 l_line_id,
782 chg_row.apply_to,
783 chg_row.line_id,
784 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
785 chg_row.GROUP_ID,
786 0, -- Bug 5189370 arpcurr.currRound(nvl(chg_2b_applied ,0)),
787 NVL(FND_GLOBAL.user_id,-1),
788 SYSDATE,
789 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
790 FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
791 SYSDATE,
792 NVL(FND_GLOBAL.user_id,-1),
793 0, -- Object Version Number is zero when the insert is at the group/summary level,
794 x_created_by_module,
795 'RA'
796 -- 1 line added below Oct 26
797 , cross_currency_2b_applied
798 -- 1 line added below Dec 12
799 , arpcurr.currRound(nvl(chg_2b_applied ,0),x_inv_curr_code),
800 p_attribute_category,
801 p_attribute1,
802 p_attribute2,
803 p_attribute3,
804 p_attribute4,
805 p_attribute5,
806 p_attribute6,
807 p_attribute7,
808 p_attribute8,
809 p_attribute9,
810 p_attribute10,
811 p_attribute11,
812 p_attribute12,
813 p_attribute13,
814 p_attribute14,
815 p_attribute15,
816 'Y'
817 );
818
819 chg_run_tot := chg_run_tot + chg_2b_applied;
820 iterator := iterator + 1;
821 end loop;
822 END insert_chg_rows;
823
824
825 -- Bug 7241111
826 PROCEDURE offset_row (
827 X_CUSTOMER_TRX_ID IN NUMBER,
828 X_CASH_RECEIPT_ID IN NUMBER
829 )
830 IS
831 BEGIN
832
833 INSERT INTO AR_ACTIVITY_DETAILS(
834 CASH_RECEIPT_ID,
835 CUSTOMER_TRX_LINE_ID,
836 ALLOCATED_RECEIPT_AMOUNT,
837 AMOUNT,
838 TAX,
839 FREIGHT,
840 CHARGES,
841 LAST_UPDATE_DATE,
842 LAST_UPDATED_BY,
843 LINE_DISCOUNT,
844 TAX_DISCOUNT,
845 FREIGHT_DISCOUNT,
846 LINE_BALANCE,
847 TAX_BALANCE,
848 CREATION_DATE,
849 CREATED_BY,
850 LAST_UPDATE_LOGIN,
851 COMMENTS,
852 APPLY_TO,
853 ATTRIBUTE1,
854 ATTRIBUTE2,
855 ATTRIBUTE3,
856 ATTRIBUTE4,
857 ATTRIBUTE5,
858 ATTRIBUTE6,
859 ATTRIBUTE7,
860 ATTRIBUTE8,
861 ATTRIBUTE9,
862 ATTRIBUTE10,
863 ATTRIBUTE11,
864 ATTRIBUTE12,
865 ATTRIBUTE13,
866 ATTRIBUTE14,
867 ATTRIBUTE15,
868 ATTRIBUTE_CATEGORY,
869 GROUP_ID,
870 REFERENCE1,
871 REFERENCE2,
872 REFERENCE3,
873 REFERENCE4,
874 REFERENCE5,
878 SOURCE_TABLE,
875 OBJECT_VERSION_NUMBER,
876 CREATED_BY_MODULE,
877 SOURCE_ID,
879 LINE_ID,
880 CURRENT_ACTIVITY_FLAG)
881 SELECT
882 LLD.CASH_RECEIPT_ID,
883 LLD.CUSTOMER_TRX_LINE_ID,
884 LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
885 LLD.AMOUNT*-1,
886 LLD.TAX*-1,
887 LLD.FREIGHT*-1,
888 LLD.CHARGES*-1,
889 LLD.LAST_UPDATE_DATE,
890 LLD.LAST_UPDATED_BY,
891 LLD.LINE_DISCOUNT,
892 LLD.TAX_DISCOUNT,
893 LLD.FREIGHT_DISCOUNT,
894 LLD.LINE_BALANCE,
895 LLD.TAX_BALANCE,
896 LLD.CREATION_DATE,
897 LLD.CREATED_BY,
898 LLD.LAST_UPDATE_LOGIN,
899 LLD.COMMENTS,
900 LLD.APPLY_TO,
901 LLD.ATTRIBUTE1,
902 LLD.ATTRIBUTE2,
903 LLD.ATTRIBUTE3,
904 LLD.ATTRIBUTE4,
905 LLD.ATTRIBUTE5,
906 LLD.ATTRIBUTE6,
907 LLD.ATTRIBUTE7,
908 LLD.ATTRIBUTE8,
909 LLD.ATTRIBUTE9,
910 LLD.ATTRIBUTE10,
911 LLD.ATTRIBUTE11,
912 LLD.ATTRIBUTE12,
913 LLD.ATTRIBUTE13,
914 LLD.ATTRIBUTE14,
915 LLD.ATTRIBUTE15,
916 LLD.ATTRIBUTE_CATEGORY,
917 LLD.GROUP_ID,
918 LLD.REFERENCE1,
919 LLD.REFERENCE2,
920 LLD.REFERENCE3,
921 LLD.REFERENCE4,
922 LLD.REFERENCE5,
923 LLD.OBJECT_VERSION_NUMBER,
924 LLD.CREATED_BY_MODULE,
925 LLD.SOURCE_ID,
926 LLD.SOURCE_TABLE,
927 ar_activity_details_s.nextval,
928 'R'
929 FROM ar_Activity_details LLD,
930 ra_customer_trx_lines rctl
931 WHERE rctl.CUSTOMER_TRX_ID = X_CUSTOMER_TRX_ID
932 AND LLD.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
933 AND LLD.CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
934 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
935
936
937 UPDATE ar_Activity_details
938 set CURRENT_ACTIVITY_FLAG = 'N'
939 WHERE CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
940 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
941 AND CUSTOMER_TRX_LINE_ID IN
942 ( select CUSTOMER_TRX_LINE_ID
943 from ra_customer_trx_lines
944 where CUSTOMER_TRX_ID = X_CUSTOMER_TRX_ID
945 );
946
947
948 END;
949
950
951 PROCEDURE Update_Row (
952 X_CASH_RECEIPT_ID IN NUMBER,
953 X_CUSTOMER_TRX_ID IN NUMBER,
954 X_lin in number,
955 x_tax in number ,
956 X_frt in number,
957 x_chg in number ,
958 X_lin_dsc in number,
959 x_tax_dsc in number ,
960 X_frt_dsc in number,
961 x_CREATED_BY_MODULE in varchar2
962 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
963 ,x_inv_to_rct_rate in number default 1
964 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
965 ) IS
966
967 BEGIN
968 -- Bug 7241111 instead of deleting now inserting offset rows
969
970 offset_row(X_CUSTOMER_TRX_ID,
971 X_CASH_RECEIPT_ID
972 );
973
974 insert_row( X_CASH_RECEIPT_ID=>X_CASH_RECEIPT_ID,
975 X_CUSTOMER_TRX_ID=>X_CUSTOMER_TRX_ID,
976 X_lin=>X_lin,
977 x_tax=>X_tax,
978 X_frt=>X_frt,
979 x_chg=>x_chg,
980 X_lin_dsc=>X_lin_dsc,
981 x_tax_dsc=>x_tax_dsc,
982 X_frt_dsc=>X_frt_dsc,
983 x_CREATED_BY_MODULE=>x_CREATED_BY_MODULE
984 ,x_inv_curr_code =>x_inv_curr_code
985 ,x_inv_to_rct_rate =>x_inv_to_rct_rate
986 ,x_rct_curr_code =>x_rct_curr_code
987 );
988
989 END Update_Row;
990
991 END AR_LL_RCV_SUMMARY_PKG;