[Home] [Help]
PACKAGE BODY: APPS.AR_LL_RCV_SUMMARY_PKG
Source
4
1 PACKAGE BODY AR_LL_RCV_SUMMARY_PKG AS
2 /*$Header: ARRWSLTB.pls 120.9.12010000.8 2010/04/30 06:48:57 nemani ship $ */
3
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
119 ,p_attribute2 => x_attribute2
116 ,x_inv_to_rct_rate,x_rct_curr_code
117 ,p_attribute_category => x_attribute_category
118 ,p_attribute1 => x_attribute1
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
266 line_2b_applied := nvl(x_lin,0) - line_run_tot;
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);
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,
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),
392 SYSDATE,
389 arp_util.currRound(nvl(cross_currency_2b_applied,0), x_rct_curr_code),
390 arpcurr.currRound(nvl(tax_2b_applied ,0),x_inv_curr_code),
391 NVL(FND_GLOBAL.user_id,-1),
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 decode(line_type, 'FREIGHT', line.amount_due_remaining,
470 'LINE', frt_adj_remaining, 0) frt_rem,
471 decode(line_type, 'FREIGHT', line.amount_due_original,
472 'LINE', frt_adj_remaining, 0) frt_orig,
473 NULL group_id
474 from ra_customer_trx_lines line
475 where (line.line_type = 'FREIGHT' OR
476 (line.line_type = 'LINE'
477 and nvl(line.frt_adj_remaining,0) <> 0))
478 and line.customer_trx_id = x_customer_trx_id;
479
480 frt_row c_frt%rowtype;
481
482 line_count number;
483 iterator number := 1;
484
485 all_frtrem_tot number;
486 all_frtorig_tot number;
487 frt_run_tot number := 0;
488 frt_2b_applied number;
489
490 -- 2 lines Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
491 frtdsc_run_tot number := 0;
492 frtdsc_2b_applied number;
493
494 cross_currency_2b_applied number;
495 l_line_id NUMBER;
496 BEGIN
497 begin
498 select count(*) row_count,
499 sum(decode(line_type, 'FREIGHT', line.amount_due_remaining,
500 'LINE', frt_adj_remaining, 0)) all_frtrem_tot,
501 sum(decode(line_type, 'FREIGHT', line.amount_due_original,
502 'LINE', frt_adj_remaining, 0)) all_frtorig_tot
503 into line_count, all_frtrem_tot, all_frtorig_tot
504 from ra_customer_trx_lines line
505 where line.customer_trx_id = x_customer_trx_id
506 and (line.line_type = 'FREIGHT' OR
507 (line.line_type = 'LINE'
508 and nvl(line.frt_adj_remaining,0) <> 0))
509 ;
510 exception
511 when others then
512 arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
513 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
514 raise ;
515 end;
516 for frt_row in c_frt loop
517 if iterator = line_count then
518 frt_2b_applied := x_frt - frt_run_tot;
519 else
520 if all_frtrem_tot > 0 then
521 frt_2b_applied := arpcurr.currRound(frt_row.frt_rem * x_frt / all_frtrem_tot);
522 else -- Overappl (all_frtrem_tot < 0) should be done at the UI level,
523 -- so this means all_frtrem_tot = 0
524 if all_frtorig_tot <> 0 then
525 frt_2b_applied := arpcurr.currRound(frt_row.frt_orig * x_frt / all_frtorig_tot);
526 else
527 frt_2b_applied := 0;
528 end if;
529 end if;
530
531 end if;
532 -- Proate in the same ratio as that of the lin2bapplied / all_lin2bapplied_tot
533 if nvl(x_frt,0) <> 0 then
537 end if;
534 frtdsc_2b_applied := arpcurr.currRound(x_frt_dsc * nvl(frt_2b_applied,0) / nvl(x_frt,0) );
535 else
536 frtdsc_2b_applied := 0;
538 arp_standard.debug ('i='||to_char(iterator)||'.'||
539 'frt_amount='||to_char(frt_2b_applied)||'.'||
540 'frt_discount='||to_char(frtdsc_2b_applied)||'.'
541 , 'plsql',
542 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
543
544 Select ar_Activity_details_s.nextval
545 INTO l_line_id
546 FROM DUAL;
547
548 -- Calculate the Allocated Receipt Amount for the line
549 cross_currency_2b_applied := arp_util.currRound((frt_2b_applied) * nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
550 arp_standard.debug ('i='||to_char(iterator)||'.'||
551 'frt_amount='||to_char(frt_2b_applied)||'.'||
552 'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
553 , 'plsql',
554 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
555
556 /*Bug 7311231,Modified the code to insert Flexfield info in AR_ACTIVITY_DETAILS.*/
557 INSERT INTO AR_ACTIVITY_DETAILS (
558 LINE_ID,
559 APPLY_TO,
560 customer_trx_line_id,
561 CASH_RECEIPT_ID,
562 GROUP_ID,
563 AMOUNT,
564 COMMENTS,
565 CREATED_BY,
566 CREATION_DATE,
567 LAST_UPDATE_LOGIN,
568 LAST_UPDATE_DATE,
569 LAST_UPDATED_BY,
570 OBJECT_VERSION_NUMBER,
571 CREATED_BY_MODULE,
572 SOURCE_TABLE
573 -- 1 line added below Oct 26
574 , allocated_receipt_amount
575 -- 2 lines added below Dec 12
576 , freight
577 , freight_discount,
578 attribute_category,
579 attribute1,
580 attribute2,
581 attribute3,
582 attribute4,
583 attribute5,
584 attribute6,
585 attribute7,
586 attribute8,
587 attribute9,
588 attribute10,
589 attribute11,
590 attribute12,
591 attribute13,
592 attribute14,
593 attribute15,
594 CURRENT_ACTIVITY_FLAG
595 )
596
597 VALUES (
598 l_line_id,
599 frt_row.apply_to,
600 frt_row.line_id,
601 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
602 frt_row.GROUP_ID,
603 0, -- Bug 5189370 arpcurr.currRound(nvl(Frt_2b_applied ,0)),
604 X_COMMENTS,
605 NVL(FND_GLOBAL.user_id,-1),
606 SYSDATE,
607 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
608 FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
609 SYSDATE,
610 NVL(FND_GLOBAL.user_id,-1),
611 0, -- Object Version Number is zero when the insert is at the group/summary level,
612 x_created_by_module,
613 'RA'
614 -- 1 line added below added Oct 26
615 , cross_currency_2b_applied
616 -- 2 lines added below Dec 12
617 , arpcurr.currRound(nvl(Frt_2b_applied ,0),x_inv_curr_code)
618 , arpcurr.currRound(nvl(FrtDsc_2b_applied ,0),x_inv_curr_Code),
619 p_attribute_category,
620 p_attribute1,
621 p_attribute2,
622 p_attribute3,
623 p_attribute4,
624 p_attribute5,
625 p_attribute6,
626 p_attribute7,
627 p_attribute8,
628 p_attribute9,
629 p_attribute10,
630 p_attribute11,
631 p_attribute12,
632 p_attribute13,
633 p_attribute14,
634 p_attribute15,
635 'Y'
636 );
637
638 frt_run_tot := frt_run_tot + frt_2b_applied;
639 -- 1 line Added Dec 7, 2005 - Bug 4775656. Discounts are not getting saved from Summary
640 frtdsc_run_tot := frtdsc_run_tot + frtdsc_2b_applied;
641 iterator := iterator + 1;
642 end loop;
643 END Insert_frt_Rows;
644
645
646 PROCEDURE Insert_chg_Rows (
647 X_CASH_RECEIPT_ID IN NUMBER,
648 X_CUSTOMER_TRX_ID IN NUMBER,
649 X_chg in number,
650 x_CREATED_BY_MODULE in varchar2
651 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
652 ,x_inv_to_rct_rate in number default 1
653 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
654 ,p_attribute_category IN varchar2 DEFAULT NULL
655 ,p_attribute1 IN varchar2 DEFAULT NULL
656 ,p_attribute2 IN varchar2 DEFAULT NULL
657 ,p_attribute3 IN varchar2 DEFAULT NULL
658 ,p_attribute4 IN varchar2 DEFAULT NULL
659 ,p_attribute5 IN varchar2 DEFAULT NULL
660 ,p_attribute6 IN varchar2 DEFAULT NULL
661 ,p_attribute7 IN varchar2 DEFAULT NULL
662 ,p_attribute8 IN varchar2 DEFAULT NULL
663 ,p_attribute9 IN varchar2 DEFAULT NULL
664 ,p_attribute10 IN varchar2 DEFAULT NULL
665 ,p_attribute11 IN varchar2 DEFAULT NULL
666 ,p_attribute12 IN varchar2 DEFAULT NULL
667 ,p_attribute13 IN varchar2 DEFAULT NULL
668 ,p_attribute14 IN varchar2 DEFAULT NULL
669 ,p_attribute15 IN varchar2 DEFAULT NULL
670 ) IS
671
672 cursor c_chg
673 is
674 select
675 'CHARGES' apply_to,
676 line.customer_trx_line_id LINE_ID,
677 -- No nvl needed in the foll amounts since arp_process_det_pkg.initialization
678 -- would have updated the values to not-nulls
679 decode(line_type, 'CHARGES', line.amount_due_remaining,
680 'LINE', line.chrg_amount_remaining, 0) chg_rem,
684 from ra_customer_trx_lines line
681 decode(line_type, 'CHARGES', line.amount_due_original,
682 'LINE', line.chrg_amount_remaining, 0) chg_orig,
683 NULL group_id
685 where (line.line_type = 'CHARGES' OR
686 (line.line_type = 'LINE'
687 and nvl(line.chrg_amount_remaining,0) <> 0))
688 and line.customer_trx_id = x_customer_trx_id;
689
690 chg_row c_chg%rowtype;
691
692 line_count number;
693 iterator number := 1;
694
695 all_chgrem_tot number;
696 all_chgorig_tot number;
697 chg_run_tot number := 0;
698 chg_2b_applied number;
699
700 cross_currency_2b_applied number;
701 l_line_id NUMBER;
702 BEGIN
703 begin
704 select count(*) row_count,
705 sum(nvl(decode(line_type, 'CHARGES', line.amount_due_remaining,
706 'LINE', chrg_amount_remaining, 0), 0)),
707 sum(nvl(decode(line_type, 'CHARGES', line.amount_due_original,
708 'LINE', chrg_amount_remaining, 0), 0))
709 into line_count, all_chgrem_tot,
710 all_chgorig_tot
711 from ra_customer_trx_lines line
712 where line.customer_trx_id = x_customer_trx_id
713 and (line.line_type = 'CHARGES' OR
714 (line.line_type = 'LINE'
715 and nvl(line.chrg_amount_remaining,0) <> 0))
716 ;
717 exception
718 when others then
719 arp_standard.debug ('Error in calcuating the total of all rows', 'plsql',
720 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
721 raise ;
722 end;
723 for chg_row in c_chg loop
724 if iterator = line_count then
725 chg_2b_applied := x_chg - chg_run_tot;
726 else
727 if all_chgrem_tot > 0 then
728 chg_2b_applied := arpcurr.currRound(chg_row.chg_rem * x_chg / all_chgrem_tot);
729 else -- Overappl (all_chgrem_tot < 0) should be done at the UI level,
730 -- so this means all_chgrem_tot = 0
731 if all_chgorig_tot <> 0 then
732 chg_2b_applied := arpcurr.currRound(chg_row.chg_orig * x_chg / all_chgorig_tot);
733 else
734 chg_2b_applied := 0;
735 end if;
736 end if;
737 end if;
738 arp_standard.debug ('i='||to_char(iterator)||'.'||
739 'chg_amount='||to_char(chg_2b_applied)||'.'
740 , 'plsql',
741 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
742
743 Select ar_Activity_details_s.nextval
744 INTO l_line_id
745 FROM DUAL;
746
747
748 -- Calculate the Allocated Receipt Amount for the line
749 cross_currency_2b_applied := arp_util.currRound((chg_2b_applied) *
750 nvl(x_inv_to_rct_rate,1), x_rct_curr_code);
751 arp_standard.debug ('i='||to_char(iterator)||'.'||
752 'chg_amount='||to_char(chg_2b_applied)||'.'||
753 'alloc_rct_amt='||to_char(cross_currency_2b_applied)||'.'
754 , 'plsql',
755 'AR_LL_RCV_SUMMARY_PKG.INSERT_ROW', 1);
756
757 /*Bug 7311231,Modified the code to insert Flexfield info in AR_ACTIVITY_DETAILS.*/
758 INSERT INTO AR_ACTIVITY_DETAILS (
759 LINE_ID,
760 APPLY_TO,
761 customer_trx_line_id,
762 CASH_RECEIPT_ID,
763 GROUP_ID,
764 AMOUNT,
765 CREATED_BY,
766 CREATION_DATE,
767 LAST_UPDATE_LOGIN,
768 LAST_UPDATE_DATE,
769 LAST_UPDATED_BY,
770 OBJECT_VERSION_NUMBER,
771 CREATED_BY_MODULE,
772 SOURCE_TABLE
773 -- 1 line added below Oct 26
774 , allocated_receipt_amount
775 -- 1 line added below Dec 12
776 , charges,
777 attribute_category,
778 attribute1,
779 attribute2,
780 attribute3,
781 attribute4,
782 attribute5,
783 attribute6,
784 attribute7,
785 attribute8,
786 attribute9,
787 attribute10,
788 attribute11,
789 attribute12,
790 attribute13,
791 attribute14,
792 attribute15,
793 CURRENT_ACTIVITY_FLAG
794 )
795
796 VALUES (
797 l_line_id,
798 chg_row.apply_to,
799 chg_row.line_id,
800 DECODE(X_CASH_RECEIPT_ID, FND_API.G_MISS_NUM, NULL , X_CASH_RECEIPT_ID),
801 chg_row.GROUP_ID,
802 0, -- Bug 5189370 arpcurr.currRound(nvl(chg_2b_applied ,0)),
803 NVL(FND_GLOBAL.user_id,-1),
804 SYSDATE,
805 decode(FND_GLOBAL.conc_login_id,null,FND_GLOBAL.login_id,-1,
806 FND_GLOBAL.login_id,FND_GLOBAL.conc_login_id),
807 SYSDATE,
808 NVL(FND_GLOBAL.user_id,-1),
809 0, -- Object Version Number is zero when the insert is at the group/summary level,
810 x_created_by_module,
811 'RA'
812 -- 1 line added below Oct 26
813 , cross_currency_2b_applied
814 -- 1 line added below Dec 12
815 , arpcurr.currRound(nvl(chg_2b_applied ,0),x_inv_curr_code),
816 p_attribute_category,
817 p_attribute1,
818 p_attribute2,
819 p_attribute3,
820 p_attribute4,
821 p_attribute5,
822 p_attribute6,
823 p_attribute7,
824 p_attribute8,
825 p_attribute9,
826 p_attribute10,
827 p_attribute11,
828 p_attribute12,
829 p_attribute13,
830 p_attribute14,
831 p_attribute15,
832 'Y'
833 );
834
835 chg_run_tot := chg_run_tot + chg_2b_applied;
836 iterator := iterator + 1;
837 end loop;
838 END insert_chg_rows;
839
840
844 X_CASH_RECEIPT_ID IN NUMBER
841 -- Bug 7241111
842 PROCEDURE offset_row (
843 X_CUSTOMER_TRX_ID IN NUMBER,
845 )
846 IS
847 BEGIN
848
849 INSERT INTO AR_ACTIVITY_DETAILS(
850 CASH_RECEIPT_ID,
851 CUSTOMER_TRX_LINE_ID,
852 ALLOCATED_RECEIPT_AMOUNT,
853 AMOUNT,
854 TAX,
855 FREIGHT,
856 CHARGES,
857 LAST_UPDATE_DATE,
858 LAST_UPDATED_BY,
859 LINE_DISCOUNT,
860 TAX_DISCOUNT,
861 FREIGHT_DISCOUNT,
862 LINE_BALANCE,
863 TAX_BALANCE,
864 CREATION_DATE,
865 CREATED_BY,
866 LAST_UPDATE_LOGIN,
867 COMMENTS,
868 APPLY_TO,
869 ATTRIBUTE1,
870 ATTRIBUTE2,
871 ATTRIBUTE3,
872 ATTRIBUTE4,
873 ATTRIBUTE5,
874 ATTRIBUTE6,
875 ATTRIBUTE7,
876 ATTRIBUTE8,
877 ATTRIBUTE9,
878 ATTRIBUTE10,
879 ATTRIBUTE11,
880 ATTRIBUTE12,
881 ATTRIBUTE13,
882 ATTRIBUTE14,
883 ATTRIBUTE15,
884 ATTRIBUTE_CATEGORY,
885 GROUP_ID,
886 REFERENCE1,
887 REFERENCE2,
888 REFERENCE3,
889 REFERENCE4,
890 REFERENCE5,
891 OBJECT_VERSION_NUMBER,
892 CREATED_BY_MODULE,
893 SOURCE_ID,
894 SOURCE_TABLE,
895 LINE_ID,
896 CURRENT_ACTIVITY_FLAG)
897 SELECT
898 LLD.CASH_RECEIPT_ID,
899 LLD.CUSTOMER_TRX_LINE_ID,
900 LLD.ALLOCATED_RECEIPT_AMOUNT*-1,
901 LLD.AMOUNT*-1,
902 LLD.TAX*-1,
903 LLD.FREIGHT*-1,
904 LLD.CHARGES*-1,
905 LLD.LAST_UPDATE_DATE,
906 LLD.LAST_UPDATED_BY,
907 LLD.LINE_DISCOUNT,
908 LLD.TAX_DISCOUNT,
909 LLD.FREIGHT_DISCOUNT,
910 LLD.LINE_BALANCE,
911 LLD.TAX_BALANCE,
912 LLD.CREATION_DATE,
913 LLD.CREATED_BY,
914 LLD.LAST_UPDATE_LOGIN,
915 LLD.COMMENTS,
916 LLD.APPLY_TO,
917 LLD.ATTRIBUTE1,
918 LLD.ATTRIBUTE2,
919 LLD.ATTRIBUTE3,
920 LLD.ATTRIBUTE4,
921 LLD.ATTRIBUTE5,
922 LLD.ATTRIBUTE6,
923 LLD.ATTRIBUTE7,
924 LLD.ATTRIBUTE8,
925 LLD.ATTRIBUTE9,
926 LLD.ATTRIBUTE10,
927 LLD.ATTRIBUTE11,
928 LLD.ATTRIBUTE12,
929 LLD.ATTRIBUTE13,
930 LLD.ATTRIBUTE14,
931 LLD.ATTRIBUTE15,
932 LLD.ATTRIBUTE_CATEGORY,
933 LLD.GROUP_ID,
934 LLD.REFERENCE1,
935 LLD.REFERENCE2,
936 LLD.REFERENCE3,
937 LLD.REFERENCE4,
938 LLD.REFERENCE5,
939 LLD.OBJECT_VERSION_NUMBER,
940 LLD.CREATED_BY_MODULE,
941 LLD.SOURCE_ID,
942 LLD.SOURCE_TABLE,
943 ar_activity_details_s.nextval,
944 'R'
945 FROM ar_Activity_details LLD,
946 ra_customer_trx_lines rctl
947 WHERE rctl.CUSTOMER_TRX_ID = X_CUSTOMER_TRX_ID
948 AND LLD.CUSTOMER_TRX_LINE_ID = rctl.CUSTOMER_TRX_LINE_ID
949 AND LLD.CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
950 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y';
951
952
953 UPDATE ar_Activity_details
954 set CURRENT_ACTIVITY_FLAG = 'N'
955 WHERE CASH_RECEIPT_ID = X_CASH_RECEIPT_ID
956 AND NVL(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'
957 AND CUSTOMER_TRX_LINE_ID IN
958 ( select CUSTOMER_TRX_LINE_ID
959 from ra_customer_trx_lines
960 where CUSTOMER_TRX_ID = X_CUSTOMER_TRX_ID
961 );
962
963
964 END;
965
966
967 PROCEDURE Update_Row (
968 X_CASH_RECEIPT_ID IN NUMBER,
972 X_frt in number,
969 X_CUSTOMER_TRX_ID IN NUMBER,
970 X_lin in number,
971 x_tax in number ,
973 x_chg in number ,
974 X_lin_dsc in number,
975 x_tax_dsc in number ,
976 X_frt_dsc in number,
977 x_CREATED_BY_MODULE in varchar2
978 ,x_inv_curr_code in varchar2 default arpcurr.FunctionalCurrency
979 ,x_inv_to_rct_rate in number default 1
980 ,x_rct_curr_code in varchar2 default arpcurr.FunctionalCurrency
981 ) IS
982
983 BEGIN
984 -- Bug 7241111 instead of deleting now inserting offset rows
985
986 offset_row(X_CUSTOMER_TRX_ID,
987 X_CASH_RECEIPT_ID
988 );
989
990 insert_row( X_CASH_RECEIPT_ID=>X_CASH_RECEIPT_ID,
991 X_CUSTOMER_TRX_ID=>X_CUSTOMER_TRX_ID,
992 X_lin=>X_lin,
993 x_tax=>X_tax,
994 X_frt=>X_frt,
995 x_chg=>x_chg,
996 X_lin_dsc=>X_lin_dsc,
997 x_tax_dsc=>x_tax_dsc,
998 X_frt_dsc=>X_frt_dsc,
999 x_CREATED_BY_MODULE=>x_CREATED_BY_MODULE
1000 ,x_inv_curr_code =>x_inv_curr_code
1001 ,x_inv_to_rct_rate =>x_inv_to_rct_rate
1002 ,x_rct_curr_code =>x_rct_curr_code
1003 );
1004
1005 END Update_Row;
1006
1007 END AR_LL_RCV_SUMMARY_PKG;