[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_DET_PKG
Source
1 PACKAGE BODY arp_process_det_pkg AS
2 /* $Header: ARDLAPPB.pls 120.30 2011/08/15 09:27:48 chuansha ship $*/
3 g_gt_id NUMBER := 0;
4 g_payschedule_trx ar_payment_schedules%ROWTYPE;
5 g_payschedule_rec ar_payment_schedules%ROWTYPE;
6 g_payschedule_clr ar_payment_schedules%ROWTYPE;
7 g_app_ra_id NUMBER;
8 g_bulk_fetch_rows NUMBER := 10000;
9 g_customer_trx ra_customer_trx%ROWTYPE;
10 g_ae_sys_rec arp_acct_main.ae_sys_rec_type;
11 g_cash_receipt ar_cash_receipts%ROWTYPE;
12
13 g_unapplied_ccid NUMBER;
14 g_ed_ccid NUMBER;
15 g_uned_ccid NUMBER;
16 g_unidentified_ccid NUMBER;
17 g_clearing_ccid NUMBER;
18 g_remittance_ccid NUMBER;
19 g_cash_ccid NUMBER;
20 g_on_account_ccid NUMBER;
21 g_factor_ccid NUMBER;
22 g_inv_rec_ccid NUMBER;
23
24
25 ------------------------Local procedures -----
26 /*-----------------------------------------------------------------------------+
27 | Procedure get_inv_ps |
28 +-----------------------------------------------------------------------------+
29 | Parameter : p_customer_trx_id invoice ID |
30 | Action : Copy the invoice payment schedule into g_payschedule_trx global |
31 +-----------------------------------------------------------------------------*/
32 PROCEDURE get_inv_ps
33 (x_return_status IN OUT NOCOPY VARCHAR2);
34
35 /*-----------------------------------------------------------------------------+
36 | Procedure get_rec_ps |
37 +-----------------------------------------------------------------------------+
38 | Parameter : p_cr_id CR ID |
39 | Action : Copy the receipt payment schedule into g_payschedule_rec global |
40 +-----------------------------------------------------------------------------*/
41 PROCEDURE get_rec_ps
42 (p_cr_id IN NUMBER,
43 x_return_status IN OUT NOCOPY VARCHAR2);
44
45 /*-----------------------------------------------------------------------------+
46 | Procedure upd_inv_ps |
47 +-----------------------------------------------------------------------------+
48 | Parameter : |
49 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
50 | p_group_id Group_id req when Application level is GROUP |
51 | p_ctl_id customer_trx_line_id required when the application level |
52 | is LINE |
53 | p_line_applied Line amount applied |
54 | p_tax_applied Tax amount applied |
55 | p_freight_applied Freight amount applied |
56 | p_charges_applied Charge amount applied |
57 | -- |
58 | p_line_ediscounted Earned Discount on Revenue |
59 | p_tax_ediscounted Earned Discount on Tax |
60 | p_freight_ediscounted Earned Discount on Freight |
61 | p_charges_ediscounted Earned Discount on charge |
62 | -- |
63 | p_line_uediscounted Unearned Discount on Revenue |
64 | p_tax_uediscounted Unearned Discount on Tax |
65 | p_freight_uediscounted Unearned Discount on Freight |
66 | p_charges_uediscounted Unearned Discount on charge |
67 | p_ps_rec Payment schedule invoice |
68 | p_ae_sys_rec Receivable system parameters |
69 | -- |
70 | x_apps_rec Out variable containing the ar_receivable_apps_gt rec|
71 +-----------------------------------------------------------------------------+
72 | Action : Compute payment schedule effect based on amount arguments |
73 | then update the global variable g_payschedule_rec |
74 | Return a record ar_receivable_apps_gt type with the amount info |
75 +-----------------------------------------------------------------------------*/
76 PROCEDURE upd_inv_ps(
77 p_app_level IN VARCHAR2,
78 --
79 p_source_data_key1 IN VARCHAR2,
80 p_source_data_key2 IN VARCHAR2,
81 p_source_data_key3 IN VARCHAR2,
82 p_source_data_key4 IN VARCHAR2,
83 p_source_data_key5 IN VARCHAR2,
84 --
85 p_ctl_id IN NUMBER,
86 --
87 p_line_applied IN NUMBER,
88 p_tax_applied IN NUMBER,
89 p_freight_applied IN NUMBER,
90 p_charges_applied IN NUMBER,
91 --
92 p_line_ediscounted IN NUMBER,
93 p_tax_ediscounted IN NUMBER,
94 p_freight_ediscounted IN NUMBER,
95 p_charges_ediscounted IN NUMBER,
96 --
97 p_line_uediscounted IN NUMBER,
98 p_tax_uediscounted IN NUMBER,
99 p_freight_uediscounted IN NUMBER,
100 p_charges_uediscounted IN NUMBER,
101 p_ps_rec IN ar_payment_schedules%ROWTYPE,
102 --
103 x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE,
104 x_return_status IN OUT NOCOPY VARCHAR2);
105
106 /*-----------------------------------------------------------------------------+
107 | Procedure insert_rapps_p |
108 +-----------------------------------------------------------------------------+
109 | Parameter : |
110 | p_app_rec variable of type ar_receivable_apps_gt |
111 +-----------------------------------------------------------------------------+
112 | Action : insert p_rec_apps in ar_receivable_apps_gt |
113 +-----------------------------------------------------------------------------*/
114 PROCEDURE insert_rapps_p
115 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
116 x_return_status IN OUT NOCOPY VARCHAR2);
117
118 /*-----------------------------------------------------------------------------+
119 | Procedure res_ctl_rem_amt_for_app |
120 +-----------------------------------------------------------------------------+
121 | Parameter : |
122 | p_app_rec variable of type ar_receivable_apps_gt |
123 +-----------------------------------------------------------------------------+
124 | Action : restore the amounts in the ra_customer_trx_lines_gt |
125 +-----------------------------------------------------------------------------*/
126 PROCEDURE res_ctl_rem_amt_for_app
127 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
128 x_return_status IN OUT NOCOPY VARCHAR2);
129
130 /*-----------------------------------------------------------------------------+
131 | Procedure res_inv_ps |
132 +-----------------------------------------------------------------------------+
133 | Parameter : |
134 | p_app_rec variable of type ar_receivable_apps_gt |
135 +-----------------------------------------------------------------------------+
136 | Action : restore the amounts in the g_payschedule_trx based on the input|
137 +-----------------------------------------------------------------------------*/
138 PROCEDURE res_inv_ps
139 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
140 x_return_status IN OUT NOCOPY VARCHAR2);
141
142 /*-----------------------------------------------------------------------------+
143 | Procedure delete_application |
144 +-----------------------------------------------------------------------------+
145 | Parameter : |
146 | p_app_rec variable of type ar_receivable_apps_gt |
147 +-----------------------------------------------------------------------------+
148 | Action : |
149 | 1) Call res_inv_ps to restore payment schedule |
150 | 2) Call res_ctl_rem_amt_for_app to restore the ra_customer_trx_lines_gt |
151 | amounts |
152 | 3) Delete the record from ar_receivable_apps_gt |
153 +-----------------------------------------------------------------------------*/
154 PROCEDURE delete_application
155 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
156 x_return_status IN OUT NOCOPY VARCHAR2);
157
158 /*-----------------------------------------------------------------------------+
159 | Procedure do_apply |
160 +-----------------------------------------------------------------------------+
161 | Parameter : |
162 | p_app_rec variable of type ar_receivable_apps_gt |
163 | p_customer_trx invoice record |
164 | p_ae_sys_rec receivable system parameter |
165 | p_gt_id global ID |
166 +-----------------------------------------------------------------------------+
167 | Action : Call arp_det_dist_pkg to do the application |
168 +-----------------------------------------------------------------------------*/
169 PROCEDURE do_apply
170 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
171 p_gt_id IN VARCHAR2,
172 x_return_status IN OUT NOCOPY VARCHAR2);
173
174 /*-----------------------------------------------------------------------------+
175 | Procedure apply |
176 +-----------------------------------------------------------------------------+
177 | Parameter : |
178 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
179 | p_group_id Group_id req when Application level is GROUP |
180 | p_ctl_id customer_trx_line_id required when the application level |
181 | is LINE |
182 | p_line_applied Line amount applied |
183 | p_tax_applied Tax amount applied |
184 | p_freight_applied Freight amount applied |
185 | p_charges_applied Charge amount applied |
186 | -- |
187 | p_line_ediscounted Earned Discount on Revenue |
188 | p_tax_ediscounted Earned Discount on Tax |
189 | p_freight_ediscounted Earned Discount on Freight |
190 | p_charges_ediscounted Earned Discount on charge |
191 | -- |
192 | p_line_uediscounted Unearned Discount on Revenue |
193 | p_tax_uediscounted Unearned Discount on Tax |
194 | p_freight_uediscounted Unearned Discount on Freight |
195 | p_charges_uediscounted Unearned Discount on charge |
196 | p_customer_trx Invoice record |
197 | p_ae_sys_rec Receivable system parameters |
198 +-----------------------------------------------------------------------------+
199 | Action : |
200 | 1) Call upd_inv_ps |
201 | 2) Call do_apply |
202 +-----------------------------------------------------------------------------*/
203 PROCEDURE apply
204 ( p_app_level IN VARCHAR2,
205 --
206 p_source_data_key1 IN VARCHAR2,
207 p_source_data_key2 IN VARCHAR2,
208 p_source_data_key3 IN VARCHAR2,
209 p_source_data_key4 IN VARCHAR2,
210 p_source_data_key5 IN VARCHAR2,
211 --
212 p_ctl_id IN NUMBER,
213 --
214 p_line_applied IN NUMBER,
215 p_tax_applied IN NUMBER,
216 p_freight_applied IN NUMBER,
217 p_charges_applied IN NUMBER,
218 --
219 p_line_ediscounted IN NUMBER,
220 p_tax_ediscounted IN NUMBER,
221 p_freight_ediscounted IN NUMBER,
222 p_charges_ediscounted IN NUMBER,
223 --
224 p_line_uediscounted IN NUMBER,
225 p_tax_uediscounted IN NUMBER,
226 p_freight_uediscounted IN NUMBER,
227 p_charges_uediscounted IN NUMBER,
228 --
229 x_return_status IN OUT NOCOPY VARCHAR2);
230
231 /*-----------------------------------------------------------------------------+
232 | FUNCTION cur_app_gt_id |
233 +-----------------------------------------------------------------------------+
234 | Parameter : |
235 | p_app_level Application level TRANSACTION/GROUP/LINE |
236 | p_group_id Group_id required if level is GROUP |
237 | p_ctl_id customer_trx_line_id required if level is LINE |
238 | Out variable |
239 | x_app_rec return the current ar_receivable_apps_gt record matching|
240 | the search criteria in ar_receivable_apps_gt |
241 | Return : |
242 | Gt_id of that record matching the search criteria |
243 | If no row found the n returns NO_GT_ID |
244 +-----------------------------------------------------------------------------+
245 | Action : |
246 | Search for the current ar_receivable_apps_gt record that match the criteria|
247 +-----------------------------------------------------------------------------*/
248 FUNCTION cur_app_gt_id
249 ( p_app_level IN VARCHAR2,
250 --
251 p_source_data_key1 IN VARCHAR2,
252 p_source_data_key2 IN VARCHAR2,
253 p_source_data_key3 IN VARCHAR2,
254 p_source_data_key4 IN VARCHAR2,
255 p_source_data_key5 IN VARCHAR2,
256 --
257 p_ctl_id IN NUMBER,
258 x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE)
259 RETURN VARCHAR2;
260
261
262 PROCEDURE dump_payschedule(p_ps_rec IN ar_payment_schedules%ROWTYPE);
263
264
265
266 --
267 PROCEDURE get_trx_db_app
268 ( x_line_app OUT NOCOPY NUMBER,
269 x_tax_app OUT NOCOPY NUMBER,
270 x_frt_app OUT NOCOPY NUMBER,
271 x_chrg_app OUT NOCOPY NUMBER,
272 x_line_ed OUT NOCOPY NUMBER,
273 x_tax_ed OUT NOCOPY NUMBER,
274 x_frt_ed OUT NOCOPY NUMBER,
275 x_chrg_ed OUT NOCOPY NUMBER,
276 x_line_uned OUT NOCOPY NUMBER,
277 x_tax_uned OUT NOCOPY NUMBER,
278 x_frt_uned OUT NOCOPY NUMBER,
279 x_chrg_uned OUT NOCOPY NUMBER)
280 IS
281 CURSOR c_trx_db IS
282 SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)),
283 SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)),
284 SUM( DECODE (activity_bucket,'APP_FRT' ,amt,0)),
285 SUM( DECODE (activity_bucket,'APP_CHRG',amt,0)),
286 SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)),
287 SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)),
288 SUM( DECODE (activity_bucket,'ED_FRT' ,amt,0)),
289 SUM( DECODE (activity_bucket,'ED_CHRG' ,amt,0)),
290 SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)),
291 SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0)),
292 SUM( DECODE (activity_bucket,'UNED_FRT' ,amt,0)),
293 SUM( DECODE (activity_bucket,'UNED_CHRG' ,amt,0))
294 FROM (SELECT ctl.line_type,
295 ctl.customer_trx_line_id,
296 ctl.link_to_cust_trx_line_id,
297 NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id),
298 amt_tab.amt,
299 amt_tab.activity_bucket,
300 amt_tab.ref_account_class
301 FROM ra_customer_trx_lines_all ctl,
302 (select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
303 ard.activity_bucket activity_bucket,
304 ard.ref_account_class ref_account_class,
305 ard.ref_customer_trx_line_id ref_customer_trx_line_id
306 from ar_distributions_all ard
307 WHERE ard.source_table = 'RA'
308 AND ard.source_id IN
309 (select receivable_application_id
310 from ar_receivable_applications_all
311 where applied_customer_trx_id = g_customer_trx.customer_trx_id)
312 GROUP BY ard.activity_bucket,
313 ard.ref_account_class,
314 ard.ref_customer_trx_line_id) amt_tab
315 WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id);
316 BEGIN
317 arp_standard.debug(' get_trx_db_app +');
318 OPEN c_trx_db;
319 FETCH c_trx_db INTO x_line_app ,
320 x_tax_app ,
321 x_frt_app ,
322 x_chrg_app ,
323 x_line_ed ,
324 x_tax_ed ,
325 x_frt_ed ,
326 x_chrg_ed ,
327 x_line_uned,
328 x_tax_uned ,
329 x_frt_uned ,
330 x_chrg_uned;
331 IF c_trx_db%NOTFOUND THEN
332 x_line_app := 0;
333 x_tax_app := 0;
334 x_frt_app := 0;
335 x_chrg_app := 0;
336 x_line_ed := 0;
337 x_tax_ed := 0;
338 x_frt_ed := 0;
339 x_chrg_ed := 0;
340 x_line_uned:= 0;
341 x_tax_uned := 0;
342 x_frt_uned := 0;
343 x_chrg_uned:= 0;
344 END IF;
345 CLOSE c_trx_db;
346 arp_standard.debug(' x_line_app '||x_line_app);
347 arp_standard.debug(' x_tax_app '||x_tax_app);
348 arp_standard.debug(' x_frt_app '||x_frt_app);
349 arp_standard.debug(' x_chrg_app '||x_chrg_app);
350 arp_standard.debug(' x_line_ed '||x_line_ed);
351 arp_standard.debug(' x_tax_ed '||x_tax_ed);
352 arp_standard.debug(' x_frt_ed '||x_frt_ed);
353 arp_standard.debug(' x_chrg_ed '||x_chrg_ed);
354 arp_standard.debug(' x_line_uned '||x_line_uned);
355 arp_standard.debug(' x_tax_uned '||x_tax_uned);
356 arp_standard.debug(' x_frt_uned '||x_frt_uned);
357 arp_standard.debug(' x_chrg_uned '||x_chrg_uned);
358 arp_standard.debug(' get_trx_db_app -');
359 END;
360
361 PROCEDURE get_group_db_app
362 (p_source_data_key1 IN VARCHAR2,
363 p_source_data_key2 IN VARCHAR2,
364 p_source_data_key3 IN VARCHAR2,
365 p_source_data_key4 IN VARCHAR2,
366 p_source_data_key5 IN VARCHAR2,
367 --
368 x_line_app OUT NOCOPY NUMBER,
369 x_tax_app OUT NOCOPY NUMBER,
370 x_line_ed OUT NOCOPY NUMBER,
371 x_tax_ed OUT NOCOPY NUMBER,
372 x_line_uned OUT NOCOPY NUMBER,
373 x_tax_uned OUT NOCOPY NUMBER)
374 IS
375 CURSOR c_group_db
376 (p_source_data_key1 IN VARCHAR2,
377 p_source_data_key2 IN VARCHAR2,
378 p_source_data_key3 IN VARCHAR2,
379 p_source_data_key4 IN VARCHAR2,
380 p_source_data_key5 IN VARCHAR2)
381 IS
382 SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)),
383 SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)),
384 SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)),
385 SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)),
386 SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)),
387 SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0))
388 FROM (
389 SELECT ctl.line_type,
390 ctl.customer_trx_line_id,
391 ctl.link_to_cust_trx_line_id,
392 NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id),
393 amt_tab.amt,
394 amt_tab.activity_bucket,
395 amt_tab.ref_account_class,
396 ctl.source_data_key1,
397 ctl.source_data_key2,
398 ctl.source_data_key3,
399 ctl.source_data_key4,
400 ctl.source_data_key5
401 FROM ra_customer_trx_lines_gt ctl,
402 (select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
403 ard.activity_bucket activity_bucket,
404 ard.ref_account_class ref_account_class,
405 ard.ref_customer_trx_line_id ref_customer_trx_line_id
406 from ar_distributions_all ard
407 WHERE ard.source_table = 'RA'
408 AND ard.source_id IN
409 (select receivable_application_id
410 from ar_receivable_applications_all
411 where applied_customer_trx_id = g_customer_trx.customer_trx_id)
412 GROUP BY ard.activity_bucket,
413 ard.ref_account_class,
414 ard.ref_customer_trx_line_id) amt_tab
415 WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id
416 AND ctl.source_data_key1 = NVL(p_source_data_key1,'00')
417 AND ctl.source_data_key2 = NVL(p_source_data_key2,'00')
418 AND ctl.source_data_key3 = NVL(p_source_data_key3,'00')
419 AND ctl.source_data_key4 = NVL(p_source_data_key4,'00')
420 AND ctl.source_data_key5 = NVL(p_source_data_key5,'00'));
421 BEGIN
422 arp_standard.debug(' get_group_db_app +');
423 arp_standard.debug(' p_source_data_key1 '||p_source_data_key1);
424 arp_standard.debug(' p_source_data_key2 '||p_source_data_key2);
425 arp_standard.debug(' p_source_data_key3 '||p_source_data_key3);
426 arp_standard.debug(' p_source_data_key4 '||p_source_data_key4);
427 arp_standard.debug(' p_source_data_key5 '||p_source_data_key5);
428 OPEN c_group_db
429 (p_source_data_key1 => p_source_data_key1,
430 p_source_data_key2 => p_source_data_key2,
431 p_source_data_key3 => p_source_data_key3,
432 p_source_data_key4 => p_source_data_key4,
433 p_source_data_key5 => p_source_data_key5);
434 FETCH c_group_db INTO x_line_app ,
435 x_tax_app ,
436 x_line_ed ,
437 x_tax_ed ,
438 x_line_uned,
439 x_tax_uned ;
440 IF c_group_db%NOTFOUND THEN
441 x_line_app := 0;
442 x_tax_app := 0;
443 x_line_ed := 0;
444 x_tax_ed := 0;
445 x_line_uned := 0;
446 x_tax_uned := 0;
447 END IF;
448 CLOSE c_group_db;
449 arp_standard.debug(' x_line_app '||x_line_app);
450 arp_standard.debug(' x_tax_app '||x_tax_app);
451 arp_standard.debug(' x_line_ed '||x_line_ed);
452 arp_standard.debug(' x_tax_ed '||x_tax_ed);
453 arp_standard.debug(' x_line_uned '||x_line_uned);
454 arp_standard.debug(' x_tax_uned '||x_tax_uned);
455 arp_standard.debug(' get_group_db_app -');
456 END;
457
458 PROCEDURE get_log_line_db_app
459 (p_log_line_id IN NUMBER,
460 --
461 x_line_app OUT NOCOPY NUMBER,
462 x_tax_app OUT NOCOPY NUMBER,
463 x_line_ed OUT NOCOPY NUMBER,
464 x_tax_ed OUT NOCOPY NUMBER,
465 x_line_uned OUT NOCOPY NUMBER,
466 x_tax_uned OUT NOCOPY NUMBER)
467 IS
468 CURSOR c_log_line(p_log_line_id IN NUMBER) IS
469 SELECT app_line,
470 app_tax,
471 ed_line,
472 ed_tax,
473 uned_line,
474 uned_tax
475 FROM(
476 (SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)) app_line,
477 SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)) app_tax,
478 SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)) ed_line,
479 SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)) ed_tax,
480 SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)) uned_line,
481 SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0)) uned_tax,
482 log_line_id log_line_id
483 FROM (SELECT ctl.line_type,
484 ctl.customer_trx_line_id,
485 ctl.link_to_cust_trx_line_id,
486 NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id) log_line_id,
487 amt_tab.amt,
488 amt_tab.activity_bucket,
489 amt_tab.ref_account_class,
490 ctl.source_data_key1,
491 ctl.source_data_key2,
492 ctl.source_data_key3,
493 ctl.source_data_key4,
494 ctl.source_data_key5
495 FROM ra_customer_trx_lines_gt ctl,
496 (select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
497 ard.activity_bucket activity_bucket,
498 ard.ref_account_class ref_account_class,
499 ard.ref_customer_trx_line_id ref_customer_trx_line_id
500 from ar_distributions_all ard
501 WHERE ard.source_table = 'RA'
502 AND ard.source_id IN
503 (select receivable_application_id
504 from ar_receivable_applications_all
505 where applied_customer_trx_id = g_customer_trx.customer_trx_id)
506 GROUP BY ard.activity_bucket,
507 ard.ref_account_class,
508 ard.ref_customer_trx_line_id) amt_tab
509 WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id )
510 GROUP BY log_line_id)) log_line_tab
511 WHERE log_line_tab.log_line_id = p_log_line_id;
512 BEGIN
513 arp_standard.debug(' get_log_line_db_app +');
514 arp_standard.debug(' p_log_line_id '||p_log_line_id);
515 OPEN c_log_line(p_log_line_id => p_log_line_id);
516 FETCH c_log_line INTO x_line_app ,
517 x_tax_app ,
518 x_line_ed ,
519 x_tax_ed ,
520 x_line_uned,
521 x_tax_uned ;
522 IF c_log_line%NOTFOUND THEN
523 x_line_app := 0;
524 x_tax_app := 0;
525 x_line_ed := 0;
526 x_tax_ed := 0;
527 x_line_uned := 0;
528 x_tax_uned := 0;
529 END IF;
530 CLOSE c_log_line;
531 arp_standard.debug(' x_line_app '||x_line_app);
532 arp_standard.debug(' x_tax_app '||x_tax_app);
533 arp_standard.debug(' x_line_ed '||x_line_ed);
534 arp_standard.debug(' x_tax_ed '||x_tax_ed);
535 arp_standard.debug(' x_line_uned '||x_line_uned);
536 arp_standard.debug(' x_tax_uned '||x_tax_uned);
537 arp_standard.debug(' get_log_line_db_app -');
538 END;
539
540
541 -- procedures and functions Body
542
543 PROCEDURE dump_payschedule(p_ps_rec IN ar_payment_schedules%ROWTYPE)
544 IS
545 BEGIN
546 arp_standard.debug('p_ps_rec.amount_applied :'||p_ps_rec.amount_applied);
547 arp_standard.debug('p_ps_rec.discount_taken_earned :'||p_ps_rec.discount_taken_earned);
548 arp_standard.debug('p_ps_rec.discount_taken_unearned :'||p_ps_rec.discount_taken_unearned);
549 arp_standard.debug('p_ps_rec.discount_remaining :'||p_ps_rec.discount_remaining);
550 arp_standard.debug('p_ps_rec.amount_line_items_remaining :'||p_ps_rec.amount_line_items_remaining);
551 arp_standard.debug('p_ps_rec.receivables_charges_remaining:'||p_ps_rec.receivables_charges_remaining);
552 arp_standard.debug('p_ps_rec.tax_remaining :'||p_ps_rec.tax_remaining);
553 arp_standard.debug('p_ps_rec.freight_remaining :'||p_ps_rec.freight_remaining);
554 END dump_payschedule;
555
556 PROCEDURE dump_sys_param
557 IS
558 BEGIN
559 arp_standard.debug('g_ae_sys_rec.set_of_books_id :'||g_ae_sys_rec.set_of_books_id);
560 arp_standard.debug('g_ae_sys_rec.coa_id :'||g_ae_sys_rec.coa_id);
561 arp_standard.debug('g_ae_sys_rec.base_currency :'||g_ae_sys_rec.base_currency);
562 arp_standard.debug('g_ae_sys_rec.base_precision :'||g_ae_sys_rec.base_precision);
563 arp_standard.debug('g_ae_sys_rec.base_min_acc_unit:'||g_ae_sys_rec.base_min_acc_unit);
564 arp_standard.debug('g_ae_sys_rec.gain_cc_id :'||g_ae_sys_rec.gain_cc_id);
565 arp_standard.debug('g_ae_sys_rec.loss_cc_id :'||g_ae_sys_rec.loss_cc_id);
566 arp_standard.debug('g_ae_sys_rec.round_cc_id :'||g_ae_sys_rec.round_cc_id);
567 arp_standard.debug('g_ae_sys_rec.SOB_TYPE :'||g_ae_sys_rec.SOB_TYPE);
568 END dump_sys_param;
569
570 /*-----------------------------------------------------------------------------+
571 | Procedure get_inv_ps |
572 +-----------------------------------------------------------------------------+
573 | Parameter : p_customer_trx_id invoice ID |
574 | Action : Copy the invoice payment schedule into g_payschedule_trx global |
575 +-----------------------------------------------------------------------------*/
576 PROCEDURE get_inv_ps
577 (x_return_status IN OUT NOCOPY VARCHAR2)
578 IS
579 CURSOR c_ps IS
580 SELECT *
581 FROM ar_payment_schedules
582 WHERE class in ('INV','DM') /* Bug 5189370 */
583 AND customer_trx_id = g_customer_trx.customer_trx_id
584 AND status = 'OP';
585 l_cpt NUMBER := 0;
586 l_inv_ps ar_payment_schedules%ROWTYPE;
587 no_installed_inv_allowed EXCEPTION;
588 no_op_trx_pay_schedule EXCEPTION;
589 no_customer_trx_cache EXCEPTION;
590 BEGIN
591 arp_standard.debug('get_inv_ps +');
592 arp_standard.debug(' g_customer_trx.customer_trx_id :'||g_customer_trx.customer_trx_id);
593 IF g_customer_trx.customer_trx_id IS NULL THEN
594 RAISE no_customer_trx_cache;
595 END IF;
596 OPEN c_ps;
597 LOOP
598 IF l_cpt > 1 THEN
599 CLOSE c_ps;
600 RAISE no_installed_inv_allowed;
601 END IF;
602 FETCH c_ps INTO l_inv_ps;
603 EXIT WHEN c_ps%NOTFOUND;
604 l_cpt := l_cpt + 1;
605 END LOOP;
606 CLOSE c_ps;
607 IF l_cpt = 0 THEN
608 RAISE no_op_trx_pay_schedule;
609 ELSE
610 g_payschedule_trx := l_inv_ps;
611 END IF;
612 -- dump_payschedule(g_payschedule_trx);
613 arp_standard.debug('get_inv_ps -');
614 EXCEPTION
615 WHEN no_customer_trx_cache THEN
616 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
617 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_inv_ps-no_customer_trx_cache
618 Please verify if initialization has been successfully' );
619 FND_MSG_PUB.ADD;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 arp_standard.debug
622 ('EXCEPTION get_inv_ps no_installed_inv_allowed customer_trx_id '||g_customer_trx.customer_trx_id);
623 WHEN no_installed_inv_allowed THEN
624 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
625 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_inv_ps-no_installed_inv_allowed customer_trx_id:'
626 ||g_customer_trx.customer_trx_id);
627 FND_MSG_PUB.ADD;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 arp_standard.debug
630 ('EXCEPTION get_inv_ps no_installed_inv_allowed customer_trx_id '||g_customer_trx.customer_trx_id);
631 WHEN no_op_trx_pay_schedule THEN
632 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
633 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_inv_ps-no_open_trx_pay_schedule customer_trx_id:'
634 ||g_customer_trx.customer_trx_id);
635 FND_MSG_PUB.ADD;
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 arp_standard.debug
638 ('EXCEPTION get_inv_ps no_op_trx_pay_schedule customer_trx_id '||g_customer_trx.customer_trx_id);
639 WHEN OTHERS THEN
640 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
641 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_inv_ps-'||SQLERRM);
642 FND_MSG_PUB.ADD;
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 arp_standard.debug('EXCEPTION get_inv_ps OTHERS '||SQLERRM);
645 END get_inv_ps;
646
647
648 /*-----------------------------------------------------------------------------+
649 | Procedure get_rec_ps |
650 +-----------------------------------------------------------------------------+
651 | Parameter : p_cr_id CR ID |
652 | Action : Copy the receipt payment schedule into g_payschedule_rec global |
653 +-----------------------------------------------------------------------------*/
654 PROCEDURE get_rec_ps
655 (p_cr_id IN NUMBER,
656 x_return_status IN OUT NOCOPY VARCHAR2)
657 IS
658 CURSOR c_ps IS
659 SELECT *
660 FROM ar_payment_schedules
661 WHERE class = 'PMT'
662 AND cash_receipt_id = p_cr_id
663 AND status = 'OP';
664 l_cpt NUMBER := 0;
665 l_rec_ps ar_payment_schedules%ROWTYPE;
666 no_op_rec_pay_schedule EXCEPTION;
667 BEGIN
668 arp_standard.debug('get_rec_ps +');
669 arp_standard.debug(' p_cr_id :'||p_cr_id);
670 OPEN c_ps;
671 FETCH c_ps INTO l_rec_ps;
672 IF c_ps%NOTFOUND THEN
673 RAISE no_op_rec_pay_schedule;
674 ELSE
675 g_payschedule_rec := l_rec_ps;
676 END IF;
677 CLOSE c_ps;
678 arp_standard.debug('get_rec_ps -');
679 EXCEPTION
680 WHEN no_op_rec_pay_schedule THEN
681 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
682 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_rec_ps no_open_rec_pay_schedule' );
683 FND_MSG_PUB.ADD;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 arp_standard.debug
686 ('EXCEPTION get_rec_ps no_op_rec_pay_schedule p_cr_id '||p_cr_id);
687 WHEN OTHERS THEN
688 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
689 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_rec_ps:'||SQLERRM );
690 FND_MSG_PUB.ADD;
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 arp_standard.debug
693 ('EXCEPTION get_rec_ps OTHERS '||SQLERRM);
694 END get_rec_ps;
695
696
697 /*-----------------------------------------------------------------------------+
698 | Procedure upd_inv_ps |
699 +-----------------------------------------------------------------------------+
700 | Parameter : |
701 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
702 | p_group_id Group_id req when Application level is GROUP |
703 | p_ctl_id customer_trx_line_id required when the application level |
704 | is LINE |
705 | p_line_applied Line amount applied |
706 | p_tax_applied Tax amount applied |
707 | p_freight_applied Freight amount applied |
708 | p_charges_applied Charge amount applied |
709 | -- |
710 | p_line_ediscounted Earned Discount on Revenue |
711 | p_tax_ediscounted Earned Discount on Tax |
712 | p_freight_ediscounted Earned Discount on Freight |
713 | p_charges_ediscounted Earned Discount on charge |
714 | -- |
715 | p_line_uediscounted Unearned Discount on Revenue |
716 | p_tax_uediscounted Unearned Discount on Tax |
717 | p_freight_uediscounted Unearned Discount on Freight |
718 | p_charges_uediscounted Unearned Discount on charge |
719 | p_ps_rec Payment schedule invoice |
720 | p_ae_sys_rec Receivable system parameters |
721 | -- |
722 | x_apps_rec Out variable containing the ar_receivable_apps_gt rec|
723 +-----------------------------------------------------------------------------+
724 | Action : Compute payment schedule effect based on amount arguments |
725 | then update the global variable g_payschedule_rec |
726 | Return a record ar_receivable_apps_gt type with the amount info |
727 +-----------------------------------------------------------------------------*/
728 PROCEDURE upd_inv_ps(
729 p_app_level IN VARCHAR2,
730 --
731 p_source_data_key1 IN VARCHAR2,
732 p_source_data_key2 IN VARCHAR2,
733 p_source_data_key3 IN VARCHAR2,
734 p_source_data_key4 IN VARCHAR2,
735 p_source_data_key5 IN VARCHAR2,
736 --
737 p_ctl_id IN NUMBER,
738 --
739 p_line_applied IN NUMBER,
740 p_tax_applied IN NUMBER,
741 p_freight_applied IN NUMBER,
742 p_charges_applied IN NUMBER,
743 --
744 p_line_ediscounted IN NUMBER,
745 p_tax_ediscounted IN NUMBER,
746 p_freight_ediscounted IN NUMBER,
747 p_charges_ediscounted IN NUMBER,
748 --
749 p_line_uediscounted IN NUMBER,
750 p_tax_uediscounted IN NUMBER,
751 p_freight_uediscounted IN NUMBER,
752 p_charges_uediscounted IN NUMBER,
753 p_ps_rec IN ar_payment_schedules%ROWTYPE,
754 --
755 x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE,
756 x_return_status IN OUT NOCOPY VARCHAR2)
757 IS
758 l_amount_applied NUMBER := 0;
759 l_discount_taken_total NUMBER:=0;
760 l_tax_discounted NUMBER:=0;
761 l_freight_discounted NUMBER:=0;
762 l_line_discounted NUMBER:=0;
763 l_charges_discounted NUMBER:=0;
764 l_line_remaining NUMBER:=0;
765 l_tax_remaining NUMBER:=0;
766 l_rec_charges_remaining NUMBER:=0;
767 l_freight_remaining NUMBER:=0;
768 l_tax_applied NUMBER:=0;
769 l_freight_applied NUMBER:=0;
770 l_line_applied NUMBER:=0;
771 l_charges_applied NUMBER:=0;
772 l_tax_ediscounted NUMBER:=0;
773 l_freight_ediscounted NUMBER:=0;
774 l_line_ediscounted NUMBER:=0;
775 l_charges_ediscounted NUMBER:=0;
776 l_tax_uediscounted NUMBER:=0;
777 l_freight_uediscounted NUMBER:=0;
778 l_line_uediscounted NUMBER:=0;
779 l_charges_uediscounted NUMBER:=0;
780 l_acctd_amount_applied NUMBER:=0;
781 l_acctd_earned_discount_taken NUMBER:=0;
782 l_acctd_unearned_disc_taken NUMBER:=0;
783 l_nocopy_amt_due_remain NUMBER;
784 l_nocopy_acctd_amt_due_remain NUMBER;
785 l_applied_concern VARCHAR2(50);
786 l_earned_concern VARCHAR2(50);
787 l_uearned_concern VARCHAR2(50);
788 l_apps_rec ar_receivable_apps_gt%ROWTYPE;
789 l_gt_id VARCHAR2(30);
790 l_ps_rec ar_payment_schedules%ROWTYPE;
791 l_discount_taken_earned NUMBER := 0;
792 l_discount_taken_unearned NUMBER := 0;
793 neg_app_amt EXCEPTION;
794 neg_earned_amt EXCEPTION;
795 neg_unearned_amt EXCEPTION;
796
797 FUNCTION is_bucket_concern
798 (p_line_applied IN NUMBER,
799 p_tax_applied IN NUMBER,
800 p_freight_applied IN NUMBER,
801 p_charges_applied IN NUMBER,
802 x_amount_applied OUT NOCOPY NUMBER,
803 p_bc_ps_rec IN ar_payment_schedules%ROWTYPE)
804 RETURN VARCHAR2
805 IS
806 l_line_applied NUMBER;
807 l_tax_applied NUMBER;
808 l_freight_applied NUMBER;
809 l_charges_applied NUMBER;
810 l_amount_due_original NUMBER;
811 l_acctd_amount_due_original NUMBER;
812
813 BEGIN
814 l_line_applied := NVL(p_line_applied ,0);
815 l_tax_applied := NVL(p_tax_applied ,0);
816 l_freight_applied := NVL(p_freight_applied,0);
817 l_charges_applied := NVL(p_charges_applied ,0);
818 --
819 -- Non negative amount allowed
820 --
821 arp_standard.debug(' is_bucket_concern(+)' );
822 arp_standard.debug(' amount_line_items_original : '||p_bc_ps_rec.amount_line_items_original);
823 arp_standard.debug(' tax_original : '||p_bc_ps_rec.tax_original);
824 arp_standard.debug(' freight_applied : '||p_bc_ps_rec.freight_original);
825 arp_standard.debug(' charges_applied : '||p_bc_ps_rec.RECEIVABLES_CHARGES_CHARGED);
826
827 If p_ctl_id IS NOT NULL
828 Then
829 /* Due to rounding issues with the proration some of the lines may end up with
830 due_remaining as negative,to let the LLCA go threw for these lines we will
831 make use of AMOUNT_DUE_REMAINING sign.Ref bug 7307197 */
832 SELECT nvl(AMOUNT_DUE_REMAINING,AMOUNT_DUE_ORIGINAL)
833 INTO l_amount_due_original
834 FROM ra_customer_trx_lines
835 WHERE customer_trx_line_id = p_ctl_id;
836
837 IF (sign(l_line_applied) <> sign(l_amount_due_original)
838 AND l_line_applied <> 0)
839 THEN RETURN 'PBLINENEG';
840 ELSIF (sign(l_tax_applied) <> sign(l_amount_due_original)
841 and l_tax_applied <> 0 )
842 THEN RETURN 'PBTAXNEG';
843 ELSIF (sign(l_freight_applied) <> sign(l_amount_due_original)
844 AND l_freight_applied <> 0 )
845 THEN RETURN 'PBFRTNEG';
846 ELSIF (sign(l_charges_applied) <> sign(l_amount_due_original)
847 AND l_charges_applied <> 0 )
848 THEN RETURN 'PBCHRGNEG';
849 END IF;
850
851 Else
852
853 IF (sign(l_line_applied) <> sign(p_bc_ps_rec.amount_line_items_original)
854 AND l_line_applied <> 0)
855 THEN RETURN 'PBLINENEG';
856 ELSIF (sign(l_tax_applied) <> sign(p_bc_ps_rec.tax_original)
857 and l_tax_applied <> 0 )
858 THEN RETURN 'PBTAXNEG';
859 ELSIF ((sign(l_freight_applied) <> sign(p_bc_ps_rec.freight_original)
860 AND p_bc_ps_rec.freight_original <> 0)
861 AND l_freight_applied <> 0 )
862 THEN RETURN 'PBFRTNEG';
863 ELSIF ((sign(l_charges_applied) <> sign(p_bc_ps_rec.RECEIVABLES_CHARGES_CHARGED)
864 AND p_bc_ps_rec.RECEIVABLES_CHARGES_CHARGED <> 0)
865 AND l_charges_applied <> 0 )
866 THEN RETURN 'PBCHRGNEG';
867 END IF;
868 End If;
869 --
870 -- If all bucket 0 then not concern
871 --
872 IF l_line_applied = 0 AND
873 l_tax_applied = 0 AND
874 l_freight_applied = 0 AND
875 l_charges_applied = 0
876 THEN
877 x_amount_applied := 0;
878 RETURN 'N';
879 ELSE
880 x_amount_applied := l_line_applied + l_tax_applied +
881 l_freight_applied + l_charges_applied;
882 RETURN 'Y';
883 END IF;
884 END is_bucket_concern;
885 BEGIN
886 arp_standard.debug('arp_process_det_pkg.upd_inv_ps+' );
887 arp_standard.debug(' p_line_applied :'||p_line_applied);
888 arp_standard.debug(' p_tax_applied :'||p_tax_applied);
889 arp_standard.debug(' p_freight_applied :'||p_freight_applied);
890 arp_standard.debug(' p_charges_applied :'||p_charges_applied);
891 --
892 arp_standard.debug(' p_line_ediscounted :'||p_line_ediscounted);
893 arp_standard.debug(' p_tax_ediscounted :'||p_tax_ediscounted);
894 arp_standard.debug(' p_freight_ediscounted :'||p_freight_ediscounted);
895 arp_standard.debug(' p_charges_ediscounted :'||p_charges_ediscounted);
896 --
897 arp_standard.debug(' p_line_uediscounted :'||p_line_uediscounted);
898 arp_standard.debug(' p_tax_uediscounted :'||p_tax_uediscounted);
899 arp_standard.debug(' p_freight_uediscounted :'||p_freight_uediscounted);
900 arp_standard.debug(' p_charges_uediscounted :'||p_charges_uediscounted);
901 --
902 arp_standard.debug(' payment_schedule_id : '||g_payschedule_trx.payment_schedule_id );
903 l_ps_rec := g_payschedule_trx;
904 l_applied_concern := is_bucket_concern(p_line_applied => p_line_applied,
905 p_tax_applied => p_tax_applied,
906 p_freight_applied => p_freight_applied,
907 p_charges_applied => p_charges_applied,
908 x_amount_applied => l_amount_applied,
909 p_bc_ps_rec => p_ps_rec );
910
911 IF l_applied_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
912 RAISE neg_app_amt;
913 END IF;
914 l_earned_concern := is_bucket_concern(p_line_applied => p_line_ediscounted,
915 p_tax_applied => p_tax_ediscounted,
916 p_freight_applied => p_freight_ediscounted,
917 p_charges_applied => p_charges_ediscounted,
918 x_amount_applied => l_discount_taken_earned,
919 p_bc_ps_rec => p_ps_rec );
920 IF l_earned_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
921 RAISE neg_earned_amt;
922 END IF;
923 l_uearned_concern := is_bucket_concern(p_line_applied => p_line_uediscounted,
924 p_tax_applied => p_tax_uediscounted,
925 p_freight_applied => p_freight_uediscounted,
926 p_charges_applied => p_charges_uediscounted,
927 x_amount_applied => l_discount_taken_unearned,
928 p_bc_ps_rec => p_ps_rec );
929 IF l_earned_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
930 RAISE neg_unearned_amt;
931 END IF;
932 l_line_discounted := NVL(p_line_uediscounted,0) + NVL(p_line_ediscounted,0);
933 l_tax_discounted := NVL(p_tax_uediscounted,0) + NVL(p_tax_ediscounted,0);
934 l_freight_discounted := NVL(p_freight_uediscounted,0) + NVL(p_freight_ediscounted,0);
935 l_charges_discounted := NVL(p_charges_uediscounted,0) + NVL(p_charges_ediscounted,0);
936 l_discount_taken_total := l_line_discounted + l_tax_discounted
937 + l_freight_discounted + l_charges_discounted;
938
939 IF l_earned_concern = 'Y' THEN
940 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
941 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
942 arp_util.calc_acctd_amount
943 (p_currency => NULL,
944 p_precision => NULL,
945 p_mau => NULL,
946 p_rate => l_ps_rec.exchange_rate,
947 p_type => '-', /** ADR must be reduced by amount_applied */
948 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
949 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
950 p_detail => l_discount_taken_earned, /* Earned discount */
951 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
952 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
953 p_acctd_detail => l_acctd_earned_discount_taken ); /* Acct. amount_applied */
954 END IF;
955 IF l_uearned_concern = 'Y' THEN
956 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
957 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
958 arp_util.calc_acctd_amount
959 (p_currency => NULL,
960 p_precision => NULL,
961 p_mau => NULL,
962 p_rate => l_ps_rec.exchange_rate,
963 p_type => '-', /** ADR must be reduced by amount_applied */
964 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
965 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
966 p_detail => l_discount_taken_unearned, /* Unearned discount */
967 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
968 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
969 p_acctd_detail => l_acctd_unearned_disc_taken ); /* Acct. amount_applied */
970 END IF;
971 IF l_applied_concern = 'Y' THEN
972 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
973 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
974 arp_util.calc_acctd_amount
975 (p_currency => NULL,
976 p_precision => NULL,
977 p_mau => NULL,
978 p_rate => l_ps_rec.exchange_rate,
979 p_type => '-', /** ADR must be reduced by amount_applied */
980 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
981 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
982 p_detail => l_amount_applied, /* Receipt Amount */
983 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
984 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
985 p_acctd_detail => l_acctd_amount_applied ); /* Acct. amount_applied */
986 END IF;
987 l_ps_rec.amount_applied := NVL(l_ps_rec.amount_applied,0)
988 + l_amount_applied;
989 l_ps_rec.discount_taken_earned := NVL(l_ps_rec.discount_taken_earned,0)
990 + l_discount_taken_earned;
991 l_ps_rec.discount_taken_unearned := NVL(l_ps_rec.discount_taken_unearned,0)
992 + l_discount_taken_unearned;
993 l_ps_rec.discount_remaining := NVL(l_ps_rec.discount_remaining,0)
994 - l_discount_taken_total;
995 l_ps_rec.amount_line_items_remaining :=
996 NVL(l_ps_rec.amount_line_items_remaining,0) -
997 ( NVL( p_line_applied, 0 ) +
998 NVL( l_line_discounted, 0 ) );
999 l_ps_rec.receivables_charges_remaining :=
1000 NVL (l_ps_rec.receivables_charges_remaining, 0 ) -
1001 ( NVL( p_charges_applied, 0 ) +
1002 NVL( l_charges_discounted , 0 ) );
1003 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) -
1004 ( NVL( p_tax_applied, 0 ) +
1005 NVL( l_tax_discounted, 0 ) );
1006 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) -
1007 ( NVL( p_freight_applied, 0 ) +
1008 NVL( l_freight_discounted, 0 ) );
1009 g_payschedule_trx := l_ps_rec;
1010 -- dump_payschedule(g_payschedule_trx);
1011 --
1012 g_gt_id := g_gt_id + 1;
1013 l_gt_id := userenv('SESSIONID')||'_'||g_gt_id;
1014 --
1015 l_apps_rec.GT_ID := l_gt_id;
1016 l_apps_rec.app_level := p_app_level;
1017 l_apps_rec.source_data_key1 := p_source_data_key1;
1018 l_apps_rec.source_data_key2 := p_source_data_key2;
1019 l_apps_rec.source_data_key3 := p_source_data_key3;
1020 l_apps_rec.source_data_key4 := p_source_data_key4;
1021 l_apps_rec.source_data_key5 := p_source_data_key5;
1022 l_apps_rec.ctl_id := p_ctl_id;
1023 --
1024 l_apps_rec.RECEIVABLE_APPLICATION_ID := g_app_ra_id;
1025 l_apps_rec.AMOUNT_APPLIED := l_amount_applied;
1026 l_apps_rec.CODE_COMBINATION_ID := g_inv_rec_ccid;
1027 l_apps_rec.SET_OF_BOOKS_ID := g_ae_sys_rec.set_of_books_id;
1028 l_apps_rec.APPLICATION_TYPE := 'CASH';
1029 l_apps_rec.PAYMENT_SCHEDULE_ID := p_ps_rec.payment_schedule_id;
1030 l_apps_rec.APPLIED_CUSTOMER_TRX_ID := p_ps_rec.customer_trx_id;
1031 l_apps_rec.LINE_APPLIED := p_line_applied;
1032 l_apps_rec.TAX_APPLIED := p_tax_applied;
1033 l_apps_rec.FREIGHT_APPLIED := p_freight_applied;
1034 l_apps_rec.RECEIVABLES_CHARGES_APPLIED:= p_charges_applied;
1035 l_apps_rec.EARNED_DISCOUNT_TAKEN := l_discount_taken_earned;
1036 l_apps_rec.UNEARNED_DISCOUNT_TAKEN := l_discount_taken_unearned;
1037 -- l_apps_rec.ACCTD_AMOUNT_APPLIED_FROM := p_unapp_rec_apps.ACCTD_AMOUNT_APPLIED_FROM;
1038 l_apps_rec.ACCTD_AMOUNT_APPLIED_TO := l_acctd_amount_applied;
1039 l_apps_rec.ACCTD_EARNED_DISCOUNT_TAKEN:= l_acctd_earned_discount_taken;
1040 l_apps_rec.EARNED_DISCOUNT_CCID := g_ed_ccid;
1041 l_apps_rec.UNEARNED_DISCOUNT_CCID := g_uned_ccid;
1042 l_apps_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN := l_acctd_unearned_disc_taken;
1043 -- l_apps_rec.AMOUNT_APPLIED_FROM := p_unapp_rec_apps.AMOUNT_APPLIED_FROM;
1044 l_apps_rec.LINE_EDISCOUNTED := p_line_ediscounted;
1045 l_apps_rec.TAX_EDISCOUNTED := p_tax_ediscounted;
1046 l_apps_rec.FREIGHT_EDISCOUNTED := p_freight_ediscounted;
1047 l_apps_rec.CHARGES_EDISCOUNTED := p_charges_ediscounted;
1048 l_apps_rec.LINE_UEDISCOUNTED := p_line_uediscounted;
1049 l_apps_rec.TAX_UEDISCOUNTED := p_tax_uediscounted;
1050 l_apps_rec.FREIGHT_UEDISCOUNTED := p_freight_uediscounted;
1051 l_apps_rec.CHARGES_UEDISCOUNTED := p_charges_uediscounted;
1052 l_apps_rec.STATUS := 'APP';
1053 insert_rapps_p(p_app_rec => l_apps_rec,
1054 x_return_status => x_return_status);
1055 x_app_rec := l_apps_rec;
1056 arp_standard.debug( 'arp_process_det_pkg.upd_inv_ps-' );
1057 EXCEPTION
1058 WHEN neg_app_amt THEN
1059 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1060 IF l_earned_concern = 'PBLINENEG' THEN
1061 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line applied amt is negative');
1062 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1063 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax applied amt is negative');
1064 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1065 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight applied amt is negative');
1066 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1067 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge applied amt is negative');
1068 END IF;
1069 FND_MSG_PUB.ADD;
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 WHEN neg_earned_amt THEN
1072 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1073 IF l_earned_concern = 'PBLINENEG' THEN
1074 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line earned amt is negative');
1075 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1076 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax earned amt is negative');
1077 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1078 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight earned amt is negative');
1079 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1080 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge earned amt is negative');
1081 END IF;
1082 FND_MSG_PUB.ADD;
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 WHEN neg_unearned_amt THEN
1085 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1086 IF l_earned_concern = 'PBLINENEG' THEN
1087 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line unearned amt is negative');
1088 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1089 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax unearned amt is negative');
1090 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1091 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight unearned amt is negative');
1092 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1093 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge unearned amt is negative');
1094 END IF;
1095 FND_MSG_PUB.ADD;
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097 WHEN OTHERS THEN
1098 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1099 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-'||SQLERRM );
1100 FND_MSG_PUB.ADD;
1101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1102 arp_util.debug('EXCEPTION OTHERS arp_process_det_pkg.upd_inv_ps:'||SQLERRM );
1103 END upd_inv_ps;
1104
1105
1106
1107 PROCEDURE disp_app_rec
1108 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE)
1109 IS
1110 BEGIN
1111 arp_standard.debug('p_app_rec.GT_ID :'||p_app_rec.GT_ID);
1112 arp_standard.debug('p_app_rec.app_level :'||p_app_rec.app_level);
1113 arp_standard.debug('p_app_rec.group_id :'||p_app_rec.group_id);
1114 arp_standard.debug('p_app_rec.ctl_id :'||p_app_rec.ctl_id);
1115 arp_standard.debug('p_app_rec.RECEIVABLE_APPLICATION_ID :'||p_app_rec.RECEIVABLE_APPLICATION_ID);
1116 arp_standard.debug('p_app_rec.AMOUNT_APPLIED :'||p_app_rec.AMOUNT_APPLIED);
1117 arp_standard.debug('p_app_rec.CODE_COMBINATION_ID :'||p_app_rec.CODE_COMBINATION_ID);
1118 arp_standard.debug('p_app_rec.SET_OF_BOOKS_ID :'||p_app_rec.SET_OF_BOOKS_ID);
1119 arp_standard.debug('p_app_rec.APPLICATION_TYPE :'||p_app_rec.APPLICATION_TYPE);
1120 arp_standard.debug('p_app_rec.PAYMENT_SCHEDULE_ID :'||p_app_rec.PAYMENT_SCHEDULE_ID);
1121 arp_standard.debug('p_app_rec.CASH_RECEIPT_ID :'||p_app_rec.CASH_RECEIPT_ID);
1122 arp_standard.debug('p_app_rec.APPLIED_CUSTOMER_TRX_ID :'||p_app_rec.APPLIED_CUSTOMER_TRX_ID);
1123 arp_standard.debug('p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID :'||p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID);
1124 arp_standard.debug('p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID :'||p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID);
1125 arp_standard.debug('p_app_rec.CUSTOMER_TRX_ID :'||p_app_rec.CUSTOMER_TRX_ID);
1126 arp_standard.debug('p_app_rec.LINE_APPLIED :'||p_app_rec.LINE_APPLIED);
1127 arp_standard.debug('p_app_rec.TAX_APPLIED :'||p_app_rec.TAX_APPLIED);
1128 arp_standard.debug('p_app_rec.FREIGHT_APPLIED :'||p_app_rec.freight_APPLIED);
1129 arp_standard.debug('p_app_rec.RECEIVABLES_CHARGES_APPLIED :'||p_app_rec.RECEIVABLES_CHARGES_APPLIED);
1130 arp_standard.debug('p_app_rec.EARNED_DISCOUNT_TAKEN :'||p_app_rec.EARNED_DISCOUNT_TAKEN);
1131 arp_standard.debug('p_app_rec.UNEARNED_DISCOUNT_TAKEN :'||p_app_rec.UNEARNED_DISCOUNT_TAKEN);
1132 arp_standard.debug('p_app_rec.APPLICATION_RULE :'||p_app_rec.APPLICATION_RULE);
1133 arp_standard.debug('p_app_rec.ACCTD_AMOUNT_APPLIED_FROM :'||p_app_rec.ACCTD_AMOUNT_APPLIED_FROM);
1134 arp_standard.debug('p_app_rec.ACCTD_AMOUNT_APPLIED_TO :'||p_app_rec.ACCTD_AMOUNT_APPLIED_TO);
1135 arp_standard.debug('p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN :'||p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN);
1136 arp_standard.debug('p_app_rec.EARNED_DISCOUNT_CCID :'||p_app_rec.EARNED_DISCOUNT_CCID);
1137 arp_standard.debug('p_app_rec.UNEARNED_DISCOUNT_CCID :'||p_app_rec.UNEARNED_DISCOUNT_CCID);
1138 arp_standard.debug('p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN :'||p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN);
1139 arp_standard.debug('p_app_rec.ORG_ID :'||p_app_rec.ORG_ID);
1140 arp_standard.debug('p_app_rec.AMOUNT_APPLIED_FROM :'||p_app_rec.AMOUNT_APPLIED_FROM );
1141 arp_standard.debug('p_app_rec.RULE_SET_ID :'||p_app_rec.RULE_SET_ID);
1142 arp_standard.debug('p_app_rec.LINE_EDISCOUNTED :'||p_app_rec.LINE_EDISCOUNTED);
1143 arp_standard.debug('p_app_rec.TAX_EDISCOUNTED :'||p_app_rec.TAX_EDISCOUNTED);
1144 arp_standard.debug('p_app_rec.FREIGHT_EDISCOUNTED :'||p_app_rec.FREIGHT_EDISCOUNTED);
1145 arp_standard.debug('p_app_rec.CHARGES_EDISCOUNTED :'||p_app_rec.CHARGES_EDISCOUNTED);
1146 arp_standard.debug('p_app_rec.LINE_UEDISCOUNTED :'||p_app_rec.LINE_UEDISCOUNTED);
1147 arp_standard.debug('p_app_rec.TAX_UEDISCOUNTED :'||p_app_rec.TAX_UEDISCOUNTED);
1148 arp_standard.debug('p_app_rec.FREIGHT_UEDISCOUNTED :'||p_app_rec.FREIGHT_UEDISCOUNTED);
1149 arp_standard.debug('p_app_rec.CHARGES_UEDISCOUNTED :'||p_app_rec.CHARGES_UEDISCOUNTED);
1150 END disp_app_rec;
1151
1152
1153 /*-----------------------------------------------------------------------------+
1154 | Procedure insert_rapps_p |
1155 +-----------------------------------------------------------------------------+
1156 | Parameter : |
1157 | p_app_rec variable of type ar_receivable_apps_gt |
1158 +-----------------------------------------------------------------------------+
1159 | Action : insert p_rec_apps in ar_receivable_apps_gt |
1160 +-----------------------------------------------------------------------------*/
1161 PROCEDURE insert_rapps_p
1162 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1163 x_return_status IN OUT NOCOPY VARCHAR2)
1164 IS
1165 BEGIN
1166 arp_standard.debug('insert_rapps_p +');
1167 disp_app_rec(p_app_rec);
1168 INSERT INTO ar_receivable_apps_gt
1169 (GT_ID
1170 ,app_level
1171 ,source_data_key1
1172 ,source_data_key2
1173 ,source_data_key3
1174 ,source_data_key4
1175 ,source_data_key5
1176 ,ctl_id
1177 ,RECEIVABLE_APPLICATION_ID
1178 ,AMOUNT_APPLIED
1179 ,CODE_COMBINATION_ID
1180 ,SET_OF_BOOKS_ID
1181 ,APPLICATION_TYPE
1182 ,PAYMENT_SCHEDULE_ID
1183 ,CASH_RECEIPT_ID
1184 ,APPLIED_CUSTOMER_TRX_ID
1185 ,APPLIED_CUSTOMER_TRX_LINE_ID
1186 ,APPLIED_PAYMENT_SCHEDULE_ID
1187 ,CUSTOMER_TRX_ID
1188 ,LINE_APPLIED
1189 ,TAX_APPLIED
1190 ,FREIGHT_APPLIED
1191 ,RECEIVABLES_CHARGES_APPLIED
1192 ,EARNED_DISCOUNT_TAKEN
1193 ,UNEARNED_DISCOUNT_TAKEN
1194 ,APPLICATION_RULE
1195 ,ACCTD_AMOUNT_APPLIED_FROM
1196 ,ACCTD_AMOUNT_APPLIED_TO
1197 ,ACCTD_EARNED_DISCOUNT_TAKEN
1198 ,EARNED_DISCOUNT_CCID
1199 ,UNEARNED_DISCOUNT_CCID
1200 ,ACCTD_UNEARNED_DISCOUNT_TAKEN
1201 ,ORG_ID
1202 ,AMOUNT_APPLIED_FROM
1203 ,RULE_SET_ID
1204 ,LINE_EDISCOUNTED
1205 ,TAX_EDISCOUNTED
1206 ,FREIGHT_EDISCOUNTED
1207 ,CHARGES_EDISCOUNTED
1208 ,LINE_UEDISCOUNTED
1209 ,TAX_UEDISCOUNTED
1210 ,FREIGHT_UEDISCOUNTED
1211 ,CHARGES_UEDISCOUNTED) VALUES
1212 (p_app_rec.GT_ID
1213 ,p_app_rec.app_level
1214 ,p_app_rec.source_data_key1
1215 ,p_app_rec.source_data_key2
1216 ,p_app_rec.source_data_key3
1217 ,p_app_rec.source_data_key4
1218 ,p_app_rec.source_data_key5
1219 ,p_app_rec.ctl_id
1220 ,p_app_rec.RECEIVABLE_APPLICATION_ID
1221 ,p_app_rec.AMOUNT_APPLIED
1222 ,p_app_rec.CODE_COMBINATION_ID
1223 ,p_app_rec.SET_OF_BOOKS_ID
1224 ,p_app_rec.APPLICATION_TYPE
1225 ,p_app_rec.PAYMENT_SCHEDULE_ID
1226 ,p_app_rec.CASH_RECEIPT_ID
1227 ,p_app_rec.APPLIED_CUSTOMER_TRX_ID
1228 ,p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID
1229 ,p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID
1230 ,p_app_rec.CUSTOMER_TRX_ID
1231 ,p_app_rec.LINE_APPLIED
1232 ,p_app_rec.TAX_APPLIED
1233 ,p_app_rec.FREIGHT_APPLIED
1234 ,p_app_rec.RECEIVABLES_CHARGES_APPLIED
1235 ,p_app_rec.EARNED_DISCOUNT_TAKEN
1236 ,p_app_rec.UNEARNED_DISCOUNT_TAKEN
1237 ,p_app_rec.APPLICATION_RULE
1238 ,p_app_rec.ACCTD_AMOUNT_APPLIED_FROM
1239 ,p_app_rec.ACCTD_AMOUNT_APPLIED_TO
1240 ,p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN
1241 ,p_app_rec.EARNED_DISCOUNT_CCID
1242 ,p_app_rec.UNEARNED_DISCOUNT_CCID
1243 ,p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN
1244 ,p_app_rec.ORG_ID
1245 ,p_app_rec.AMOUNT_APPLIED_FROM
1246 ,p_app_rec.RULE_SET_ID
1247 ,p_app_rec.LINE_EDISCOUNTED
1248 ,p_app_rec.TAX_EDISCOUNTED
1249 ,p_app_rec.FREIGHT_EDISCOUNTED
1250 ,p_app_rec.CHARGES_EDISCOUNTED
1251 ,p_app_rec.LINE_UEDISCOUNTED
1252 ,p_app_rec.TAX_UEDISCOUNTED
1253 ,p_app_rec.FREIGHT_UEDISCOUNTED
1254 ,p_app_rec.CHARGES_UEDISCOUNTED);
1255 arp_standard.debug('insert_rapps_p -');
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 arp_standard.debug('EXCEPTION insert_rapps_p OTHERS:'||SQLERRM);
1259 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1260 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS insert_rapps_p:'||SQLERRM );
1261 FND_MSG_PUB.ADD;
1262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1263 END insert_rapps_p;
1264
1265
1266 /*-----------------------------------------------------------------------------+
1267 | Procedure res_ctl_rem_amt_for_app |
1268 +-----------------------------------------------------------------------------+
1269 | Parameter : |
1270 | p_app_rec variable of type ar_receivable_apps_gt |
1271 +-----------------------------------------------------------------------------+
1272 | Action : restore the amounts in the ra_customer_trx_lines_gt |
1273 +-----------------------------------------------------------------------------*/
1274 PROCEDURE res_ctl_rem_amt_for_app
1275 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1276 x_return_status IN OUT NOCOPY VARCHAR2)
1277 IS
1278 CURSOR c_app(p_app_rec IN ar_receivable_apps_gt%ROWTYPE)
1279 IS
1280 SELECT SUM(DECODE(b.ref_account_class,
1281 'REV',
1282 DECODE(b.REF_DET_ID,NULL,b.AMOUNT,0),
1283 0))
1284 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE AMOUNT_DUE_REMAINING
1285 SUM(DECODE(b.ref_account_class,
1286 'REV',
1287 DECODE(b.REF_DET_ID,NULL,b.ACCTD_AMOUNT,0),
1288 0))
1289 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE ACCTD_AMOUNT_DUE_REMAINING
1290 SUM(DECODE(b.ref_account_class,
1291 'REV',
1292 DECODE(b.REF_DET_ID,NULL,0,
1293 DECODE(b.SOURCE_TYPE,'FREIGHT',b.AMOUNT,0)),
1294 0))
1295 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_REMAINING
1296 SUM(DECODE(b.ref_account_class,
1297 'REV',
1298 DECODE(b.REF_DET_ID,NULL,0,
1299 DECODE(b.SOURCE_TYPE,'FREIGHT',b.ACCTD_AMOUNT,0)),
1300 0))
1301 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_ACCTD_REMAINING
1302 SUM(DECODE(b.ref_account_class,
1303 'REV',
1304 DECODE(b.REF_DET_ID,NULL,0,
1305 DECODE(b.SOURCE_TYPE,'CHARGES',b.AMOUNT,0)),
1306 0))
1307 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_REMAINING
1308 SUM(DECODE(b.ref_account_class,
1309 'REV',
1310 DECODE(b.REF_DET_ID,NULL,0,
1311 DECODE(b.SOURCE_TYPE,'CHARGES',b.ACCTD_AMOUNT,0)),
1312 0))
1313 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_ACCTD_REMAINING
1314 SUM(DECODE(b.ref_account_class,
1315 'TAX',
1316 b.AMOUNT,
1317 0))
1318 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR TAX
1319 SUM(DECODE(b.ref_account_class,
1320 'TAX',
1321 b.ACCTD_AMOUNT,
1322 0))
1323 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD TAX
1324 SUM(DECODE(b.ref_account_class,
1325 'FREIGHT',
1326 b.AMOUNT,
1327 0))
1328 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR FREIGHT
1329 SUM(DECODE(b.ref_account_class,
1330 'FREIGHT',
1331 b.ACCTD_AMOUNT,
1332 0))
1333 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD FREIGHT
1334 b.REF_CUSTOMER_TRX_LINE_ID,
1335 c.line_type
1336 FROM AR_LINE_APP_DETAIL_GT b,
1337 ra_customer_trx_lines_gt c
1338 WHERE b.gt_id = p_app_rec.gt_id
1339 AND b.app_level = p_app_rec.app_level
1340 AND b.REF_CUSTOMER_TRX_LINE_ID = c.customer_trx_line_id;
1341
1342 l_rev_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1343 l_rev_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1344 l_frt_adj_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1345 l_frt_adj_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1346 l_chrg_adj_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1347 l_chrg_adj_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1348 l_tax_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1349 l_tax_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1350 l_frt_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1351 l_frt_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1352 l_ctl_id_tab DBMS_SQL.NUMBER_TABLE;
1353 l_line_type_tab DBMS_SQL.VARCHAR2_TABLE;
1354 l_last_fetch BOOLEAN := FALSE;
1355 BEGIN
1356 arp_standard.debug('res_ctl_rem_amt_for_app +');
1357 disp_app_rec(p_app_rec);
1358 OPEN c_app(p_app_rec);
1359 LOOP
1360 FETCH c_app BULK COLLECT INTO l_rev_amt_rem_tab,
1361 l_rev_acctd_amt_rem_tab,
1362 l_frt_adj_amt_rem_tab,
1363 l_frt_adj_acctd_amt_rem_tab,
1364 l_chrg_adj_amt_rem_tab,
1365 l_chrg_adj_acctd_amt_rem_tab,
1366 l_tax_amt_rem_tab,
1367 l_tax_acctd_amt_rem_tab,
1368 l_frt_amt_rem_tab,
1369 l_frt_acctd_amt_rem_tab,
1370 l_ctl_id_tab,
1371 l_line_type_tab
1372 LIMIT g_bulk_fetch_rows;
1373
1374 IF c_app%NOTFOUND THEN
1375 l_last_fetch := TRUE;
1376 END IF;
1377
1378 IF (l_ctl_id_tab.COUNT = 0) AND (l_last_fetch) THEN
1379 arp_standard.debug('COUNT = 0 and LAST FETCH ');
1380 EXIT;
1381 END IF;
1382
1383 FORALL i IN l_ctl_id_tab.FIRST .. l_ctl_id_tab.LAST
1384 UPDATE ra_customer_trx_lines_gt
1385 SET AMOUNT_DUE_REMAINING =
1386 DECODE(l_line_type_tab(i),
1387 'LINE', AMOUNT_DUE_REMAINING + l_rev_amt_rem_tab(i),
1388 'FREIGHT',AMOUNT_DUE_REMAINING + l_frt_amt_rem_tab(i),
1389 'TAX', AMOUNT_DUE_REMAINING + l_tax_amt_rem_tab(i),
1390 AMOUNT_DUE_REMAINING),
1391 ACCTD_AMOUNT_DUE_REMAINING =
1392 DECODE(l_line_type_tab(i),
1393 'LINE', ACCTD_AMOUNT_DUE_REMAINING + l_rev_acctd_amt_rem_tab(i),
1394 'FREIGHT',ACCTD_AMOUNT_DUE_REMAINING + l_frt_acctd_amt_rem_tab(i),
1395 'TAX', ACCTD_AMOUNT_DUE_REMAINING + l_tax_acctd_amt_rem_tab(i),
1396 ACCTD_AMOUNT_DUE_REMAINING),
1397 FRT_ADJ_REMAINING =
1398 FRT_ADJ_REMAINING + l_frt_adj_amt_rem_tab(i),
1399 FRT_ADJ_ACCTD_REMAINING =
1400 FRT_ADJ_ACCTD_REMAINING + l_frt_adj_acctd_amt_rem_tab(i),
1401 CHRG_AMOUNT_REMAINING =
1402 CHRG_AMOUNT_REMAINING + l_chrg_adj_amt_rem_tab(i),
1403 CHRG_ACCTD_AMOUNT_REMAINING =
1404 CHRG_ACCTD_AMOUNT_REMAINING + l_chrg_adj_acctd_amt_rem_tab(i)
1405 WHERE customer_trx_line_id = l_ctl_id_tab(i);
1406 END LOOP;
1407 CLOSE c_app;
1408 arp_standard.debug('res_ctl_rem_amt_for_app -');
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 arp_standard.debug('EXCEPTION res_ctl_rem_amt_for_app OTHERS:'||SQLERRM);
1412 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1413 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS res_ctl_rem_amt_for_app:'||SQLERRM );
1414 FND_MSG_PUB.ADD;
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1416 END res_ctl_rem_amt_for_app;
1417
1418
1419 /*-----------------------------------------------------------------------------+
1420 | Procedure res_inv_ps |
1421 +-----------------------------------------------------------------------------+
1422 | Parameter : |
1423 | p_app_rec variable of type ar_receivable_apps_gt |
1424 +-----------------------------------------------------------------------------+
1425 | Action : restore the amounts in the g_payschedule_trx based on the input|
1426 +-----------------------------------------------------------------------------*/
1427 PROCEDURE res_inv_ps
1428 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1429 x_return_status IN OUT NOCOPY VARCHAR2)
1430 IS
1431 l_ps_rec ar_payment_schedules%ROWTYPE;
1432 l_line_discounted NUMBER;
1433 l_tax_discounted NUMBER;
1434 l_charges_discounted NUMBER;
1435 l_discount_taken_total NUMBER;
1436 l_freight_discounted NUMBER;
1437 BEGIN
1438 arp_standard.debug('res_inv_ps +');
1439 disp_app_rec(p_app_rec);
1440 l_ps_rec := g_payschedule_trx;
1441
1442 l_ps_rec.amount_applied := NVL(l_ps_rec.amount_applied,0)
1443 - p_app_rec.AMOUNT_APPLIED;
1444 l_ps_rec.discount_taken_earned := NVL(l_ps_rec.discount_taken_earned,0)
1445 - p_app_rec.EARNED_DISCOUNT_TAKEN;
1446 l_ps_rec.discount_taken_unearned := NVL(l_ps_rec.discount_taken_unearned,0)
1447 - p_app_rec.UNEARNED_DISCOUNT_TAKEN;
1448
1449 l_line_discounted := NVL(p_app_rec.LINE_UEDISCOUNTED,0) + NVL(p_app_rec.LINE_EDISCOUNTED,0);
1450 l_tax_discounted := NVL(p_app_rec.TAX_UEDISCOUNTED,0) + NVL(p_app_rec.TAX_EDISCOUNTED,0);
1451 l_freight_discounted := NVL(p_app_rec.FREIGHT_UEDISCOUNTED,0) + NVL(p_app_rec.FREIGHT_EDISCOUNTED,0);
1452 l_charges_discounted := NVL(p_app_rec.CHARGES_UEDISCOUNTED,0) + NVL(p_app_rec.CHARGES_EDISCOUNTED,0);
1453
1454 l_discount_taken_total := l_line_discounted + l_tax_discounted
1455 + l_freight_discounted + l_charges_discounted;
1456
1457 l_ps_rec.discount_remaining := NVL(l_ps_rec.discount_remaining,0)
1458 + l_discount_taken_total;
1459
1460 l_ps_rec.amount_line_items_remaining :=
1461 NVL(l_ps_rec.amount_line_items_remaining,0) +
1462 ( NVL( p_app_rec.LINE_APPLIED, 0 ) +
1463 NVL( l_line_discounted, 0 ) );
1464 l_ps_rec.receivables_charges_remaining :=
1465 NVL (l_ps_rec.receivables_charges_remaining, 0 ) +
1466 ( NVL( p_app_rec.RECEIVABLES_CHARGES_APPLIED, 0 ) +
1467 NVL( l_charges_discounted , 0 ) );
1468 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) +
1469 ( NVL( p_app_rec.TAX_APPLIED, 0 ) +
1470 NVL( l_tax_discounted, 0 ) );
1471 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) +
1472 ( NVL( p_app_rec.FREIGHT_APPLIED, 0 ) +
1473 NVL( l_freight_discounted, 0 ) );
1474 g_payschedule_trx := l_ps_rec;
1475 arp_standard.debug('res_inv_ps -');
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 arp_standard.debug('EXCEPTION res_inv_ps OTHERS:'||SQLERRM);
1479 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1480 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS res_inv_ps:'||SQLERRM );
1481 FND_MSG_PUB.ADD;
1482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1483 END res_inv_ps;
1484
1485
1486 /*-----------------------------------------------------------------------------+
1487 | Procedure delete_application |
1488 +-----------------------------------------------------------------------------+
1489 | Parameter : |
1490 | p_app_rec variable of type ar_receivable_apps_gt |
1491 +-----------------------------------------------------------------------------+
1492 | Action : |
1493 | 1) Call res_inv_ps to restore payment schedule |
1494 | 2) Call res_ctl_rem_amt_for_app to restore the ra_customer_trx_lines_gt |
1495 | amounts |
1496 | 3) Delete the record from ar_receivable_apps_gt |
1497 +-----------------------------------------------------------------------------*/
1498 PROCEDURE delete_application
1499 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1500 x_return_status IN OUT NOCOPY VARCHAR2)
1501 IS
1502 BEGIN
1503 arp_standard.debug('delete_application +');
1504 -- 1 restore ps inv
1505 res_inv_ps(p_app_rec => p_app_rec,
1506 x_return_status => x_return_status);
1507
1508 -- 2 restore inv rem amt
1509 res_ctl_rem_amt_for_app(p_app_rec => p_app_rec,
1510 x_return_status => x_return_status);
1511
1512 -- 3 delete the application from ar_receivable_apps_gt
1513 DELETE FROM ar_receivable_apps_gt
1514 WHERE gt_id = p_app_rec.gt_id
1515 AND app_level = p_app_rec.app_level;
1516
1517 -- 4 delete the distributions created by the application
1518 DELETE FROM AR_LINE_APP_DETAIL_GT
1519 WHERE gt_id = p_app_rec.gt_id
1520 AND app_level = p_app_rec.app_level;
1521
1522 arp_standard.debug('delete_application -');
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 arp_standard.debug('EXCEPTION delete_application OTHERS:'||SQLERRM);
1526 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1527 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS delete_application:'||SQLERRM );
1528 FND_MSG_PUB.ADD;
1529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530 END delete_application;
1531
1532
1533 PROCEDURE copy_app_rec
1534 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1535 x_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE,
1536 x_return_status IN OUT NOCOPY VARCHAR2)
1537 IS
1538 BEGIN
1539 arp_standard.debug('copy_app_rec +');
1540 disp_app_rec(p_app_rec);
1541 x_ra_rec.RECEIVABLE_APPLICATION_ID := p_app_rec.RECEIVABLE_APPLICATION_ID;
1542 x_ra_rec.AMOUNT_APPLIED := p_app_rec.AMOUNT_APPLIED;
1543 x_ra_rec.CODE_COMBINATION_ID := p_app_rec.CODE_COMBINATION_ID;
1544 x_ra_rec.SET_OF_BOOKS_ID := p_app_rec.SET_OF_BOOKS_ID;
1545 x_ra_rec.APPLICATION_TYPE := p_app_rec.APPLICATION_TYPE;
1546 x_ra_rec.PAYMENT_SCHEDULE_ID := p_app_rec.PAYMENT_SCHEDULE_ID;
1547 x_ra_rec.CASH_RECEIPT_ID := p_app_rec.CASH_RECEIPT_ID;
1548 x_ra_rec.APPLIED_CUSTOMER_TRX_ID := p_app_rec.APPLIED_CUSTOMER_TRX_ID;
1549 x_ra_rec.APPLIED_CUSTOMER_TRX_LINE_ID := p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID;
1550 x_ra_rec.APPLIED_PAYMENT_SCHEDULE_ID := p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID;
1551 x_ra_rec.CUSTOMER_TRX_ID := p_app_rec.CUSTOMER_TRX_ID;
1552 x_ra_rec.LINE_APPLIED := p_app_rec.LINE_APPLIED;
1553 x_ra_rec.TAX_APPLIED := p_app_rec.TAX_APPLIED;
1554 x_ra_rec.FREIGHT_APPLIED := p_app_rec.FREIGHT_APPLIED;
1555 x_ra_rec.RECEIVABLES_CHARGES_APPLIED := p_app_rec.RECEIVABLES_CHARGES_APPLIED;
1556 x_ra_rec.EARNED_DISCOUNT_TAKEN := p_app_rec.EARNED_DISCOUNT_TAKEN;
1557 x_ra_rec.UNEARNED_DISCOUNT_TAKEN := p_app_rec.UNEARNED_DISCOUNT_TAKEN;
1558 x_ra_rec.APPLICATION_RULE := p_app_rec.APPLICATION_RULE;
1559 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM := p_app_rec.ACCTD_AMOUNT_APPLIED_FROM;
1560 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO := p_app_rec.ACCTD_AMOUNT_APPLIED_TO;
1561 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN := p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN;
1562 x_ra_rec.EARNED_DISCOUNT_CCID := p_app_rec.EARNED_DISCOUNT_CCID;
1563 x_ra_rec.UNEARNED_DISCOUNT_CCID := p_app_rec.UNEARNED_DISCOUNT_CCID;
1564 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN := p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN;
1565 x_ra_rec.ORG_ID := p_app_rec.ORG_ID;
1566 x_ra_rec.AMOUNT_APPLIED_FROM := p_app_rec.AMOUNT_APPLIED_FROM;
1567 x_ra_rec.RULE_SET_ID := p_app_rec.RULE_SET_ID;
1568 x_ra_rec.LINE_EDISCOUNTED := p_app_rec.LINE_EDISCOUNTED;
1569 x_ra_rec.TAX_EDISCOUNTED := p_app_rec.TAX_EDISCOUNTED;
1570 x_ra_rec.FREIGHT_EDISCOUNTED := p_app_rec.FREIGHT_EDISCOUNTED;
1571 x_ra_rec.CHARGES_EDISCOUNTED := p_app_rec.CHARGES_EDISCOUNTED;
1572 x_ra_rec.LINE_UEDISCOUNTED := p_app_rec.LINE_UEDISCOUNTED;
1573 x_ra_rec.TAX_UEDISCOUNTED := p_app_rec.TAX_UEDISCOUNTED;
1574 x_ra_rec.FREIGHT_UEDISCOUNTED := p_app_rec.FREIGHT_UEDISCOUNTED;
1575 x_ra_rec.CHARGES_UEDISCOUNTED := p_app_rec.CHARGES_UEDISCOUNTED;
1576 x_ra_rec.STATUS := p_app_rec.STATUS;
1577
1578 arp_standard.debug(' x_ra_rec.LINE_APPLIED:'||x_ra_rec.LINE_APPLIED);
1579 arp_standard.debug(' x_ra_rec.TAX_APPLIED:'||x_ra_rec.TAX_APPLIED);
1580
1581 arp_standard.debug('copy_app_rec -');
1582 EXCEPTION
1583 WHEN OTHERS THEN
1584 arp_standard.debug('EXCEPTION copy_app_rec OTHERS:'||SQLERRM);
1585 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1586 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS copy_app_rec:'||SQLERRM );
1587 FND_MSG_PUB.ADD;
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 END copy_app_rec;
1590
1591
1592 /*-----------------------------------------------------------------------------+
1593 | Procedure do_apply |
1594 +-----------------------------------------------------------------------------+
1595 | Parameter : |
1596 | p_app_rec variable of type ar_receivable_apps_gt |
1597 | p_customer_trx invoice record |
1598 | p_ae_sys_rec receivable system parameter |
1599 | p_gt_id global ID |
1600 +-----------------------------------------------------------------------------+
1601 | Action : Call arp_det_dist_pkg to do the application |
1602 +-----------------------------------------------------------------------------*/
1603 PROCEDURE do_apply
1604 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1605 p_gt_id IN VARCHAR2,
1606 x_return_status IN OUT NOCOPY VARCHAR2)
1607 IS
1608 l_ra_rec ar_receivable_applications%ROWTYPE;
1609 BEGIN
1610 arp_standard.debug('do_apply +');
1611 copy_app_rec(p_app_rec => p_app_rec,
1612 x_ra_rec => l_ra_rec,
1613 x_return_status => x_return_status);
1614
1615 dump_sys_param;
1616
1617 IF p_app_rec.app_level = 'TRANSACTION' THEN
1618
1619 ARP_DET_DIST_PKG.Trx_level_cash_apply
1620 (p_customer_trx => g_customer_trx,
1621 p_app_rec => l_ra_rec,
1622 p_ae_sys_rec => g_ae_sys_rec,
1623 p_gt_id => p_gt_id);
1624
1625 ELSIF p_app_rec.app_level = 'GROUP' THEN
1626
1627 ARP_DET_DIST_PKG.Trx_gp_level_cash_apply
1628 (p_customer_trx => g_customer_trx,
1629 --
1630 p_source_data_key1 => p_app_rec.source_data_key1,
1631 p_source_data_key2 => p_app_rec.source_data_key2,
1632 p_source_data_key3 => p_app_rec.source_data_key3,
1633 p_source_data_key4 => p_app_rec.source_data_key4,
1634 p_source_data_key5 => p_app_rec.source_data_key5,
1635 --
1636 p_app_rec => l_ra_rec,
1637 p_ae_sys_rec => g_ae_sys_rec,
1638 p_gt_id => p_gt_id);
1639
1640 ELSIF p_app_rec.app_level = 'LINE' THEN
1641
1642 arp_standard.debug(' HYU l_ra_rec.LINE_APPLIED:'||l_ra_rec.LINE_APPLIED);
1643 arp_standard.debug(' HYU l_ra_rec.TAX_APPLIED:'||l_ra_rec.TAX_APPLIED);
1644
1645 ARP_DET_DIST_PKG.Trx_line_level_cash_apply
1646 (p_customer_trx => g_customer_trx,
1647 p_customer_trx_line_id => p_app_rec.ctl_id,
1648 p_log_inv_line => 'Y',
1649 p_app_rec => l_ra_rec,
1650 p_ae_sys_rec => g_ae_sys_rec,
1651 p_gt_id => p_gt_id);
1652
1653 END IF;
1654 arp_standard.debug('do_apply -');
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 arp_standard.debug('EXCEPTION do_apply OTHERS:'||SQLERRM);
1658 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1659 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS do_apply:'||SQLERRM );
1660 FND_MSG_PUB.ADD;
1661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1662 END do_apply;
1663
1664
1665 /*-----------------------------------------------------------------------------+
1666 | Procedure apply |
1667 +-----------------------------------------------------------------------------+
1668 | Parameter : |
1669 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
1670 | p_group_id Group_id req when Application level is GROUP |
1671 | p_ctl_id customer_trx_line_id required when the application level |
1672 | is LINE |
1673 | p_line_applied Line amount applied |
1674 | p_tax_applied Tax amount applied |
1675 | p_freight_applied Freight amount applied |
1676 | p_charges_applied Charge amount applied |
1677 | -- |
1678 | p_line_ediscounted Earned Discount on Revenue |
1679 | p_tax_ediscounted Earned Discount on Tax |
1680 | p_freight_ediscounted Earned Discount on Freight |
1681 | p_charges_ediscounted Earned Discount on charge |
1682 | -- |
1683 | p_line_uediscounted Unearned Discount on Revenue |
1684 | p_tax_uediscounted Unearned Discount on Tax |
1685 | p_freight_uediscounted Unearned Discount on Freight |
1686 | p_charges_uediscounted Unearned Discount on charge |
1687 | p_customer_trx Invoice record |
1688 | p_ae_sys_rec Receivable system parameters |
1689 +-----------------------------------------------------------------------------+
1690 | Action : |
1691 | 1) Call upd_inv_ps |
1692 | 2) Call do_apply |
1693 +-----------------------------------------------------------------------------*/
1694 PROCEDURE apply
1695 ( p_app_level IN VARCHAR2,
1696 --
1697 p_source_data_key1 IN VARCHAR2,
1698 p_source_data_key2 IN VARCHAR2,
1699 p_source_data_key3 IN VARCHAR2,
1700 p_source_data_key4 IN VARCHAR2,
1701 p_source_data_key5 IN VARCHAR2,
1702 --
1703 p_ctl_id IN NUMBER,
1704 --
1705 p_line_applied IN NUMBER,
1706 p_tax_applied IN NUMBER,
1707 p_freight_applied IN NUMBER,
1708 p_charges_applied IN NUMBER,
1709 --
1710 p_line_ediscounted IN NUMBER,
1711 p_tax_ediscounted IN NUMBER,
1712 p_freight_ediscounted IN NUMBER,
1713 p_charges_ediscounted IN NUMBER,
1714 --
1715 p_line_uediscounted IN NUMBER,
1716 p_tax_uediscounted IN NUMBER,
1717 p_freight_uediscounted IN NUMBER,
1718 p_charges_uediscounted IN NUMBER,
1719 --
1720 x_return_status IN OUT NOCOPY VARCHAR2)
1721 IS
1722 l_app_rec ar_receivable_apps_gt%ROWTYPE;
1723 BEGIN
1724 arp_standard.debug('apply +');
1725
1726 arp_standard.debug(' Calling upd_inv_ps +');
1727
1728 upd_inv_ps(
1729 p_app_level => p_app_level,
1730 --
1731 p_source_data_key1 => p_source_data_key1,
1732 p_source_data_key2 => p_source_data_key2,
1733 p_source_data_key3 => p_source_data_key3,
1734 p_source_data_key4 => p_source_data_key4,
1735 p_source_data_key5 => p_source_data_key5,
1736 --
1737 p_ctl_id => p_ctl_id,
1738 --
1739 p_line_applied => p_line_applied,
1740 p_tax_applied => p_tax_applied,
1741 p_freight_applied => p_freight_applied,
1742 p_charges_applied => p_charges_applied,
1743 --
1744 p_line_ediscounted => p_line_ediscounted,
1745 p_tax_ediscounted => p_tax_ediscounted,
1746 p_freight_ediscounted => p_freight_ediscounted,
1747 p_charges_ediscounted => p_charges_ediscounted,
1748 --
1749 p_line_uediscounted => p_line_uediscounted,
1750 p_tax_uediscounted => p_tax_uediscounted,
1751 p_freight_uediscounted => p_freight_uediscounted,
1752 p_charges_uediscounted => p_charges_uediscounted,
1753 p_ps_rec => g_payschedule_trx,
1754 --
1755 x_app_rec => l_app_rec,
1756 x_return_status => x_return_status);
1757
1758 arp_standard.debug(' x_return_status :'|| x_return_status);
1759
1760 arp_standard.debug(' Calling upd_inv_ps -');
1761
1762 do_apply
1763 (p_app_rec => l_app_rec,
1764 p_gt_id => l_app_rec.gt_id,
1765 x_return_status=> x_return_status);
1766 arp_standard.debug('apply -');
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 arp_standard.debug('EXCEPTION apply OTHERS:'||SQLERRM);
1770 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1771 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS apply:'||SQLERRM );
1772 FND_MSG_PUB.ADD;
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774 END apply;
1775
1776 PROCEDURE dump_ccid
1777 IS
1778 BEGIN
1779 arp_standard.debug('g_unapplied_ccid :'||g_unapplied_ccid);
1780 arp_standard.debug('g_ed_ccid :'||g_ed_ccid);
1781 arp_standard.debug('g_uned_ccid :'||g_uned_ccid);
1782 arp_standard.debug('g_unidentified_ccid :'||g_unidentified_ccid);
1783 arp_standard.debug('g_clearing_ccid :'||g_clearing_ccid);
1784 arp_standard.debug('g_remittance_ccid :'||g_remittance_ccid);
1785 arp_standard.debug('g_cash_ccid :'||g_cash_ccid);
1786 arp_standard.debug('g_on_account_ccid :'||g_on_account_ccid);
1787 arp_standard.debug('g_factor_ccid :'||g_factor_ccid);
1788 arp_standard.debug('g_inv_rec_ccid :'||g_inv_rec_ccid);
1789 END dump_ccid;
1790
1791 /*-----------------------------------------------------------------------------+
1792 | Procedure initialization |
1793 +-----------------------------------------------------------------------------+
1794 | Parameter : |
1795 | p_customer_trx_id invoice ID |
1796 | p_cash_receipt_id receipt ID |
1797 +-----------------------------------------------------------------------------+
1798 | Action : |
1799 | 1) Call arp_det_dist_pkg to copy trx line into ra_customer_trx_lines_gt |
1800 | 2) Call get_inv_ps to cache the invoice payment schedule |
1801 +-----------------------------------------------------------------------------*/
1802 PROCEDURE initialization
1803 (p_customer_trx_id IN NUMBER,
1804 p_cash_receipt_id IN NUMBER,
1805 x_return_status OUT NOCOPY VARCHAR2,
1806 x_msg_data OUT NOCOPY VARCHAR2,
1807 x_msg_count OUT NOCOPY NUMBER)
1808 IS
1809 CURSOR c IS
1810 SELECT *
1811 FROM ra_customer_trx
1812 WHERE customer_trx_id = p_customer_trx_id;
1813 CURSOR c_cr IS
1814 SELECT *
1815 FROM ar_cash_receipts
1816 WHERE cash_receipt_id = p_cash_receipt_id;
1817 CURSOR c_sys IS
1818 SELECT sob.set_of_books_id,
1819 sob.chart_of_accounts_id,
1820 sob.currency_code,
1821 c.precision,
1822 c.minimum_accountable_unit,
1823 sysp.code_combination_id_gain,
1824 sysp.code_combination_id_loss,
1825 sysp.code_combination_id_round
1826 FROM ar_system_parameters sysp,
1827 gl_sets_of_books sob,
1828 fnd_currencies c
1829 WHERE sob.set_of_books_id = sysp.set_of_books_id
1830 AND sob.currency_code = c.currency_code;
1831 CURSOR c_acct IS
1832 SELECT rma.unapplied_ccid
1833 , ed.code_combination_id
1834 , uned.code_combination_id
1835 , rma.unidentified_ccid
1836 , rma.receipt_clearing_ccid
1837 , rma.remittance_ccid
1838 , rma.cash_ccid
1839 , rma.on_account_ccid
1840 , rma.factor_ccid
1841 , ctlgd.code_combination_id
1842 FROM ar_cash_receipts cr
1843 , ar_cash_receipt_history crh
1844 , ar_receipt_methods rm
1845 , ce_bank_acct_uses aba
1846 , ce_bank_branches_v bp
1847 , ce_bank_accounts cba
1848 , ar_receipt_method_accounts rma
1849 , ar_receivables_trx ed
1850 , ar_receivables_trx uned
1851 , ra_cust_trx_line_gl_dist ctlgd
1852 WHERE cr.cash_receipt_id = p_cash_receipt_id
1853 AND cr.cash_receipt_id = crh.cash_receipt_id
1854 AND crh.current_record_flag = 'Y'
1855 AND rm.receipt_method_id = cr.receipt_method_id
1856 AND cr.remit_bank_acct_use_id = aba.bank_acct_use_id
1857 AND aba.bank_account_id = cba.bank_account_id
1858 AND bp.branch_party_id = cba.bank_branch_id
1859 AND rma.remit_bank_acct_use_id = aba.bank_acct_use_id
1860 AND rma.receipt_method_id = rm.receipt_method_id
1861 AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
1862 AND rma.unedisc_receivables_trx_id= uned.receivables_trx_id (+)
1863 AND ctlgd.customer_trx_id = p_customer_trx_id
1864 AND ctlgd.account_class = 'REC';
1865
1866 CURSOR c1 IS
1867 SELECT ctl.customer_trx_id
1868 FROM ra_customer_trx_lines ctl
1869 WHERE ctl.customer_trx_id = p_customer_trx_id
1870 AND ctl.autorule_complete_flag||'' = 'N'
1871 GROUP BY ctl.customer_trx_id;
1872
1873 CURSOR c_trx_number(p_customer_trx_id IN NUMBER) IS
1874 SELECT ct.trx_number
1875 FROM ra_customer_trx ct
1876 WHERE ct.customer_trx_id = p_customer_trx_id;
1877
1878 l_dummy NUMBER;
1879 l_rev_rec_req BOOLEAN;
1880 l_sum_dist NUMBER;
1881 l_trx_number VARCHAR2(20);
1882
1883 not_valid_trx EXCEPTION;
1884 no_sys_param EXCEPTION;
1885 not_valid_cr EXCEPTION;
1886 rev_rec_error EXCEPTION;
1887 BEGIN
1888 arp_standard.debug('initialization +');
1889 SAVEPOINT initialization;
1890 x_return_status := fnd_api.g_ret_sts_success;
1891 OPEN c;
1892 FETCH c INTO g_customer_trx;
1893 IF c%NOTFOUND THEN
1894 CLOSE c;
1895 RAISE not_valid_trx;
1896 END IF;
1897 CLOSE c;
1898 OPEN c_cr;
1899 FETCH c_cr INTO g_cash_receipt;
1900 IF c_cr%NOTFOUND THEN
1901 CLOSE c_cr;
1902 RAISE not_valid_cr;
1903 END IF;
1904 CLOSE c_cr;
1905 OPEN c_sys;
1906 FETCH c_sys INTO
1907 g_ae_sys_rec.set_of_books_id,
1908 g_ae_sys_rec.coa_id,
1909 g_ae_sys_rec.base_currency,
1910 g_ae_sys_rec.base_precision,
1911 g_ae_sys_rec.base_min_acc_unit,
1912 g_ae_sys_rec.gain_cc_id,
1913 g_ae_sys_rec.loss_cc_id,
1914 g_ae_sys_rec.round_cc_id;
1915 IF c_sys%NOTFOUND THEN
1916 CLOSE c_sys;
1917 RAISE no_sys_param;
1918 ELSE
1919 g_ae_sys_rec.SOB_TYPE := 'P';
1920 dump_sys_param;
1921 END IF;
1922 CLOSE c_sys;
1923 OPEN c_acct;
1924 FETCH c_acct INTO g_unapplied_ccid
1925 ,g_ed_ccid
1926 ,g_uned_ccid
1927 ,g_unidentified_ccid
1928 ,g_clearing_ccid
1929 ,g_remittance_ccid
1930 ,g_cash_ccid
1931 ,g_on_account_ccid
1932 ,g_factor_ccid
1933 ,g_inv_rec_ccid;
1934 CLOSE c_acct;
1935 dump_ccid;
1936
1937 arp_standard.debug(' Check whether Rev Recognition is to be Run');
1938 OPEN c1;
1939 FETCH c1 INTO l_dummy;
1940 IF c1%NOTFOUND THEN
1941 arp_standard.debug(' No need to run rev rec for trx_id :' || p_customer_trx_id);
1942 l_rev_rec_req := FALSE;
1943 ELSE
1944 arp_standard.debug(' Need to run rev rec for trx_id :' || p_customer_trx_id);
1945 l_rev_rec_req := TRUE;
1946 END IF;
1947 CLOSE c1;
1948
1949 IF l_rev_rec_req THEN
1950 arp_standard.debug(' Executing Rev Rec - calling ARP_AUTO_RULE.create_distributions');
1951 l_sum_dist := ARP_AUTO_RULE.create_distributions
1952 ( p_commit => 'N',
1953 p_debug => 'N',
1954 p_trx_id => p_customer_trx_id);
1955
1956 IF l_sum_dist < 0 THEN
1957 RAISE rev_rec_error;
1958 END IF;
1959 arp_standard.debug(' Completed running revenue recognition for Transaction');
1960 END IF;
1961
1962
1963 ARP_DET_DIST_PKG.set_original_rem_amt
1964 (p_customer_trx => g_customer_trx,
1965 p_from_llca => 'Y');
1966
1967 ARP_DET_DIST_PKG.copy_trx_lines (p_customer_trx_id => p_customer_trx_id,
1968 p_ae_sys_rec => g_ae_sys_rec);
1969
1970 get_inv_ps(x_return_status => x_return_status);
1971
1972 get_rec_ps(p_cr_id => g_cash_receipt.cash_receipt_id,
1973 x_return_status => x_return_status);
1974
1975 SELECT ar_receivable_applications_s.nextval
1976 INTO g_app_ra_id
1977 FROM dual;
1978
1979 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1980 RAISE FND_API.G_EXC_ERROR;
1981 END IF;
1982
1983 arp_standard.debug('initialization -');
1984 EXCEPTION
1985 WHEN no_sys_param THEN
1986 ROLLBACK TO initialization;
1987 arp_standard.debug('EXCEPTION initialization no_sys_param');
1988 FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_ROW_IN_SYSTEM_PARAMETERS' );
1989 FND_MSG_PUB.ADD;
1990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1991 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1992 p_count => x_msg_count,
1993 p_data => x_msg_data);
1994 WHEN not_valid_trx THEN
1995 ROLLBACK TO initialization;
1996 arp_standard.debug('EXCEPTION initialization not_valid_trx');
1997 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1998 FND_MESSAGE.SET_TOKEN( 'TEXT', 'not_valid_trx initialization p_customer_trx_id:'||
1999 p_customer_trx_id );
2000 FND_MSG_PUB.ADD;
2001 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2002 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2003 p_count => x_msg_count,
2004 p_data => x_msg_data);
2005 WHEN not_valid_cr THEN
2006 ROLLBACK TO initialization;
2007 arp_standard.debug('EXCEPTION initialization not_valid_cr');
2008 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2009 FND_MESSAGE.SET_TOKEN( 'TEXT', 'not_valid_cr initialization p_cash_receipt_id:'||
2010 p_cash_receipt_id );
2011 FND_MSG_PUB.ADD;
2012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2013 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2014 p_count => x_msg_count,
2015 p_data => x_msg_data);
2016 WHEN FND_API.G_EXC_ERROR THEN
2017 ROLLBACK TO initialization;
2018 x_return_status := FND_API.G_RET_STS_ERROR;
2019 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2020 p_count => x_msg_count,
2021 p_data => x_msg_data);
2022 WHEN rev_rec_error THEN
2023 ROLLBACK TO initialization;
2024 OPEN c_trx_number(p_customer_trx_id);
2025 FETCH c_trx_number INTO l_trx_number;
2026 CLOSE c_trx_number;
2027 arp_standard.debug('Error in Rev Rec - ARP_AUTO_RULE.create_distributions for trx_id :'||p_customer_trx_id);
2028 FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTORULE_ERROR' );
2029 FND_MESSAGE.SET_TOKEN( 'TRX_NUMBER', l_trx_number );
2030 FND_MSG_PUB.ADD;
2031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2032 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2033 p_count => x_msg_count,
2034 p_data => x_msg_data);
2035
2036 WHEN OTHERS THEN
2037 ROLLBACK TO initialization;
2038 arp_standard.debug('EXCEPTION initialization OTHERS:'||SQLERRM);
2039 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2040 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS initialization:'||SQLERRM );
2041 FND_MSG_PUB.ADD;
2042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2043 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2044 p_count => x_msg_count,
2045 p_data => x_msg_data);
2046 END initialization;
2047
2048
2049 /*-----------------------------------------------------------------------------+
2050 | FUNCTION cur_app_gt_id |
2051 +-----------------------------------------------------------------------------+
2052 | Parameter : |
2053 | p_app_level Application level TRANSACTION/GROUP/LINE |
2054 | p_group_id Group_id required if level is GROUP |
2055 | p_ctl_id customer_trx_line_id required if level is LINE |
2056 | Out variable |
2057 | x_app_rec return the current ar_receivable_apps_gt record matching|
2058 | the search criteria in ar_receivable_apps_gt |
2059 | Return : |
2060 | Gt_id of that record matching the search criteria |
2061 | If no row found the n returns NO_GT_ID |
2062 +-----------------------------------------------------------------------------+
2063 | Action : |
2064 | Search for the current ar_receivable_apps_gt record that match the criteria|
2065 +-----------------------------------------------------------------------------*/
2066 FUNCTION cur_app_gt_id
2067 ( p_app_level IN VARCHAR2,
2068 p_source_data_key1 IN VARCHAR2,
2069 p_source_data_key2 IN VARCHAR2,
2070 p_source_data_key3 IN VARCHAR2,
2071 p_source_data_key4 IN VARCHAR2,
2072 p_source_data_key5 IN VARCHAR2,
2073 p_ctl_id IN NUMBER,
2074 x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE)
2075 RETURN VARCHAR2
2076 IS
2077 CURSOR c_trx IS
2078 SELECT *
2079 FROM ar_receivable_apps_gt
2080 WHERE app_level = 'TRANSACTION'
2081 AND 1=2;
2082
2083 CURSOR c_grp IS
2084 SELECT *
2085 FROM ar_receivable_apps_gt
2086 WHERE app_level = 'GROUP'
2087 AND source_data_key1 = p_source_data_key1
2088 AND source_data_key2 = p_source_data_key2
2089 AND source_data_key3 = p_source_data_key3
2090 AND source_data_key4 = p_source_data_key4
2091 AND source_data_key5 = p_source_data_key5;
2092
2093 CURSOR c_ctl IS
2094 SELECT *
2095 FROM ar_receivable_apps_gt
2096 WHERE app_level = 'LINE'
2097 AND ctl_id = p_ctl_id;
2098 l_res VARCHAR2(30);
2099 BEGIN
2100 arp_standard.debug('cur_app_gt_id +');
2101 arp_standard.debug(' p_app_level :'||p_app_level);
2102 arp_standard.debug(' p_source_data_key1 :'||p_source_data_key1);
2103 arp_standard.debug(' p_source_data_key2 :'||p_source_data_key2);
2104 arp_standard.debug(' p_source_data_key3 :'||p_source_data_key3);
2105 arp_standard.debug(' p_source_data_key4 :'||p_source_data_key4);
2106 arp_standard.debug(' p_source_data_key5 :'||p_source_data_key5);
2107 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2108 IF p_app_level = 'TRANSACTION' THEN
2109 OPEN c_trx;
2110 FETCH c_trx INTO x_app_rec;
2111 IF c_trx%NOTFOUND THEN
2112 l_res := 'NO_GT_ID';
2113 ELSE
2114 l_res := x_app_rec.gt_id;
2115 END IF;
2116 CLOSE c_trx;
2117 ELSIF p_app_level = 'GROUP' THEN
2118 OPEN c_grp;
2119 FETCH c_grp INTO x_app_rec;
2120 IF c_grp%NOTFOUND THEN
2121 l_res := 'NO_GT_ID';
2122 ELSE
2123 l_res := x_app_rec.gt_id;
2124 END IF;
2125 CLOSE c_grp;
2126 ELSIF p_app_level = 'LINE' THEN
2127 OPEN c_ctl;
2128 FETCH c_ctl INTO x_app_rec;
2129 IF c_ctl%NOTFOUND THEN
2130 l_res := 'NO_GT_ID';
2131 ELSE
2132 l_res := x_app_rec.gt_id;
2133 END IF;
2134 CLOSE c_ctl;
2135 ELSE
2136 l_res := 'X';
2137 END IF;
2138 arp_standard.debug(' l_res :'||l_res);
2139 arp_standard.debug('cur_app_gt_id -');
2140 RETURN l_res;
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 arp_standard.debug('EXCEPTION cur_app_gt_id OTHERS:'||SQLERRM);
2144 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2145 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS cur_app_gt_id:'||SQLERRM );
2146 FND_MSG_PUB.ADD;
2147 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
2148 END cur_app_gt_id;
2149
2150
2151 /*-----------------------------------------------------------------------------+
2152 | Procedure application_execute |
2153 +-----------------------------------------------------------------------------+
2154 | Parameter : |
2155 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2156 | p_group_id Group_id req when Application level is GROUP |
2157 | p_ctl_id customer_trx_line_id required when the application level |
2158 | is LINE |
2159 | p_line_applied Line amount applied |
2160 | p_tax_applied Tax amount applied |
2161 | p_freight_applied Freight amount applied |
2162 | p_charges_applied Charge amount applied |
2163 | -- |
2164 | p_line_ediscounted Earned Discount on Revenue |
2165 | p_tax_ediscounted Earned Discount on Tax |
2166 | p_freight_ediscounted Earned Discount on Freight |
2167 | p_charges_ediscounted Earned Discount on charge |
2168 | -- |
2169 | p_line_uediscounted Unearned Discount on Revenue |
2170 | p_tax_uediscounted Unearned Discount on Tax |
2171 | p_freight_uediscounted Unearned Discount on Freight |
2172 | p_charges_uediscounted Unearned Discount on charge |
2173 | p_customer_trx Invoice record |
2174 | p_ae_sys_rec Receivable system parameters |
2175 +-----------------------------------------------------------------------------+
2176 | Action : |
2177 | 1) Call cur_app_gt_id to looking for current apps |
2178 | 2) If found then call delete_application |
2179 | 3) Call apply to do the application |
2180 +-----------------------------------------------------------------------------*/
2181 PROCEDURE application_execute
2182 ( p_app_level IN VARCHAR2,
2183 p_source_data_key1 IN VARCHAR2,
2184 p_source_data_key2 IN VARCHAR2,
2185 p_source_data_key3 IN VARCHAR2,
2186 p_source_data_key4 IN VARCHAR2,
2187 p_source_data_key5 IN VARCHAR2,
2188 p_ctl_id IN NUMBER,
2189 --
2190 p_line_applied IN NUMBER,
2191 p_tax_applied IN NUMBER,
2192 p_freight_applied IN NUMBER,
2193 p_charges_applied IN NUMBER,
2194 --
2195 p_line_ediscounted IN NUMBER,
2196 p_tax_ediscounted IN NUMBER,
2197 p_freight_ediscounted IN NUMBER,
2198 p_charges_ediscounted IN NUMBER,
2199 --
2200 p_line_uediscounted IN NUMBER,
2201 p_tax_uediscounted IN NUMBER,
2202 p_freight_uediscounted IN NUMBER,
2203 p_charges_uediscounted IN NUMBER,
2204 --
2205 x_return_status OUT NOCOPY VARCHAR2,
2206 x_msg_count OUT NOCOPY NUMBER,
2207 x_msg_data OUT NOCOPY VARCHAR2)
2208 IS
2209 cur_gt_id VARCHAR2(30);
2210 l_app_rec ar_receivable_apps_gt%ROWTYPE;
2211 unexpected_error EXCEPTION;
2212 BEGIN
2213 arp_standard.debug('application_execute +');
2214 SAVEPOINT first_delete_then_apply;
2215
2216 x_return_status := fnd_api.g_ret_sts_success;
2217
2218 cur_gt_id := cur_app_gt_id( p_app_level ,
2219 p_source_data_key1 ,
2220 p_source_data_key2 ,
2221 p_source_data_key3 ,
2222 p_source_data_key4 ,
2223 p_source_data_key5 ,
2224 p_ctl_id ,
2225 l_app_rec);
2226
2227 IF cur_gt_id = 'X' THEN
2228 RAISE unexpected_error;
2229 ELSIF cur_gt_id <> 'NO_GT_ID' THEN
2230 -- First delete
2231 delete_application
2232 (p_app_rec => l_app_rec,
2233 x_return_status => x_return_status);
2234
2235 ELSIF cur_gt_id = FND_API.G_RET_STS_UNEXP_ERROR THEN
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239 -- Apply
2240 apply(p_app_level => p_app_level,
2241 --
2242 p_source_data_key1 => p_source_data_key1,
2243 p_source_data_key2 => p_source_data_key2,
2244 p_source_data_key3 => p_source_data_key3,
2245 p_source_data_key4 => p_source_data_key4,
2246 p_source_data_key5 => p_source_data_key5,
2247 --
2248 p_ctl_id => p_ctl_id,
2249 --
2250 p_line_applied => p_line_applied,
2251 p_tax_applied => p_tax_applied,
2252 p_freight_applied => p_freight_applied,
2253 p_charges_applied => p_charges_applied,
2254 --
2255 p_line_ediscounted => p_line_ediscounted,
2256 p_tax_ediscounted => p_tax_ediscounted,
2257 p_freight_ediscounted => p_freight_ediscounted,
2258 p_charges_ediscounted => p_charges_ediscounted,
2259 --
2260 p_line_uediscounted => p_line_uediscounted,
2261 p_tax_uediscounted => p_tax_uediscounted,
2262 p_freight_uediscounted => p_freight_uediscounted,
2263 p_charges_uediscounted => p_charges_uediscounted,
2264 --
2265 x_return_status => x_return_status);
2266
2267 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2268 RAISE FND_API.G_EXC_ERROR;
2269 END IF;
2270
2271 arp_standard.debug('application_execute -');
2272 EXCEPTION
2273 WHEN FND_API.G_EXC_ERROR THEN
2274 ROLLBACK TO first_delete_then_apply;
2275 x_return_status := FND_API.G_RET_STS_ERROR;
2276 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2277 p_count => x_msg_count,
2278 p_data => x_msg_data);
2279 WHEN unexpected_error THEN
2280 ROLLBACK TO first_delete_then_apply;
2281 arp_standard.debug('EXCEPTION first_delete_then_apply unexpected_error - p_app_level:'
2282 ||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
2283 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2284 FND_MESSAGE.SET_TOKEN( 'TEXT', 'Unexpected first_delete_then_apply - p_app_level:'
2285 ||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
2286 FND_MSG_PUB.ADD;
2287 x_return_status := FND_API.G_RET_STS_ERROR;
2288 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2289 p_count => x_msg_count,
2290 p_data => x_msg_data);
2291 WHEN OTHERS THEN
2292 ROLLBACK TO first_delete_then_apply;
2293 arp_standard.debug('EXCEPTION first_delete_then_apply OTHERS:'||SQLERRM);
2294 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2295 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS first_delete_then_apply:'||SQLERRM );
2296 FND_MSG_PUB.ADD;
2297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2298 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2299 p_count => x_msg_count,
2300 p_data => x_msg_data);
2301 END application_execute;
2302
2303 PROCEDURE get_app_ra_amounts
2304 (p_gt_id IN NUMBER,
2305 x_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE)
2306 IS
2307 CURSOR c IS
2308 SELECT SUM(AMOUNT_APPLIED),
2309 SUM(AMOUNT_APPLIED_FROM),
2310 SUM(EARNED_DISCOUNT_TAKEN),
2311 SUM(UNEARNED_DISCOUNT_TAKEN),
2312 SUM(LINE_APPLIED),
2313 SUM(TAX_APPLIED),
2314 SUM(FREIGHT_APPLIED),
2315 SUM(RECEIVABLES_CHARGES_APPLIED),
2316 SUM(EARNED_DISCOUNT_TAKEN),
2317 SUM(UNEARNED_DISCOUNT_TAKEN),
2318 MAX(ACCTD_AMOUNT_APPLIED_FROM),
2319 SUM(ACCTD_AMOUNT_APPLIED_TO),
2320 SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
2321 SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
2322 MAX(AMOUNT_APPLIED_FROM),
2323 SUM(LINE_EDISCOUNTED),
2324 SUM(TAX_EDISCOUNTED),
2325 SUM(FREIGHT_EDISCOUNTED),
2326 SUM(CHARGES_EDISCOUNTED),
2327 SUM(LINE_UEDISCOUNTED),
2328 SUM(TAX_UEDISCOUNTED),
2329 SUM(FREIGHT_UEDISCOUNTED),
2330 SUM(CHARGES_UEDISCOUNTED),
2331 MAX(receivable_application_id)
2332 FROM ar_receivable_apps_gt
2333 WHERE gt_id = p_gt_id;
2334 BEGIN
2335 OPEN c;
2336 FETCH c INTO
2337 x_ra_rec.amount_applied ,
2338 x_ra_rec.AMOUNT_APPLIED_FROM ,
2339 x_ra_rec.EARNED_DISCOUNT_TAKEN ,
2340 x_ra_rec.UNEARNED_DISCOUNT_TAKEN ,
2341 x_ra_rec.LINE_APPLIED ,
2342 x_ra_rec.TAX_APPLIED ,
2343 x_ra_rec.FREIGHT_APPLIED ,
2344 x_ra_rec.RECEIVABLES_CHARGES_APPLIED,
2345 x_ra_rec.EARNED_DISCOUNT_TAKEN ,
2346 x_ra_rec.UNEARNED_DISCOUNT_TAKEN ,
2347 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM,
2348 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO ,
2349 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN,
2350 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN,
2351 x_ra_rec.AMOUNT_APPLIED_FROM ,
2352 x_ra_rec.LINE_EDISCOUNTED ,
2353 x_ra_rec.TAX_EDISCOUNTED ,
2354 x_ra_rec.FREIGHT_EDISCOUNTED ,
2355 x_ra_rec.CHARGES_EDISCOUNTED ,
2356 x_ra_rec.LINE_UEDISCOUNTED ,
2357 x_ra_rec.TAX_UEDISCOUNTED ,
2358 x_ra_rec.FREIGHT_UEDISCOUNTED ,
2359 x_ra_rec.CHARGES_UEDISCOUNTED ,
2360 x_ra_rec.receivable_application_id;
2361 CLOSE c;
2362 END get_app_ra_amounts;
2363
2364
2365
2366
2367 PROCEDURE final_commit
2368 (p_gl_date IN DATE,
2369 p_apply_date IN DATE,
2370 p_attribute_category IN VARCHAR2 DEFAULT NULL,
2371 p_attribute1 IN VARCHAR2 DEFAULT NULL,
2372 p_attribute2 IN VARCHAR2 DEFAULT NULL,
2373 p_attribute3 IN VARCHAR2 DEFAULT NULL,
2374 p_attribute4 IN VARCHAR2 DEFAULT NULL,
2375 p_attribute5 IN VARCHAR2 DEFAULT NULL,
2376 p_attribute6 IN VARCHAR2 DEFAULT NULL,
2377 p_attribute7 IN VARCHAR2 DEFAULT NULL,
2378 p_attribute8 IN VARCHAR2 DEFAULT NULL,
2379 p_attribute9 IN VARCHAR2 DEFAULT NULL,
2380 p_attribute10 IN VARCHAR2 DEFAULT NULL,
2381 p_attribute11 IN VARCHAR2 DEFAULT NULL,
2382 p_attribute12 IN VARCHAR2 DEFAULT NULL,
2383 p_attribute13 IN VARCHAR2 DEFAULT NULL,
2384 p_attribute14 IN VARCHAR2 DEFAULT NULL,
2385 p_attribute15 IN VARCHAR2 DEFAULT NULL,
2386 p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
2387 p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
2388 p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
2389 p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
2390 p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
2391 p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
2392 p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
2393 p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
2394 p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
2395 p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
2396 p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
2397 p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
2398 p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
2399 p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
2400 p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
2401 p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
2402 p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
2403 p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
2404 p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
2405 p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
2406 p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
2407 p_comments IN VARCHAR2 DEFAULT NULL,
2408 p_customer_reference IN VARCHAR2 DEFAULT NULL,--bug12854129
2409 --{Cross Currency
2410 p_amount_applied_from IN NUMBER DEFAULT NULL,
2411 p_trans_to_receipt_rate IN NUMBER DEFAULT NULL,
2412 --}
2413 x_ra_rec OUT NOCOPY ar_receivable_applications%ROWTYPE,
2414 x_return_status OUT NOCOPY VARCHAR2,
2415 x_msg_count OUT NOCOPY NUMBER,
2416 x_msg_data OUT NOCOPY VARCHAR2)
2417 IS
2418 CURSOR c IS
2419 SELECT SUM(AMOUNT_APPLIED),
2420 SUM(AMOUNT_APPLIED_FROM),
2421 SUM(EARNED_DISCOUNT_TAKEN),
2422 SUM(UNEARNED_DISCOUNT_TAKEN),
2423 SUM(LINE_APPLIED),
2424 SUM(TAX_APPLIED),
2425 SUM(FREIGHT_APPLIED),
2426 SUM(RECEIVABLES_CHARGES_APPLIED),
2427 SUM(EARNED_DISCOUNT_TAKEN),
2428 SUM(UNEARNED_DISCOUNT_TAKEN),
2429 MAX(ACCTD_AMOUNT_APPLIED_FROM),
2430 SUM(ACCTD_AMOUNT_APPLIED_TO),
2431 SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
2432 SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
2433 MAX(AMOUNT_APPLIED_FROM),
2434 SUM(LINE_EDISCOUNTED),
2435 SUM(TAX_EDISCOUNTED),
2436 SUM(FREIGHT_EDISCOUNTED),
2437 SUM(CHARGES_EDISCOUNTED),
2438 SUM(LINE_UEDISCOUNTED),
2439 SUM(TAX_UEDISCOUNTED),
2440 SUM(FREIGHT_UEDISCOUNTED),
2441 SUM(CHARGES_UEDISCOUNTED),
2442 MAX(receivable_application_id)
2443 FROM ar_receivable_apps_gt
2444 WHERE applied_customer_trx_id = g_customer_trx.customer_trx_id;
2445 l_adj_rec ar_adjustments%ROWTYPE;
2446
2447 x_application_ref_id NUMBER;
2448 x_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
2449 x_receivable_application_id NUMBER;
2450 x_acctd_amount_applied_from NUMBER;
2451 x_acctd_amount_applied_to NUMBER;
2452 x_claim_reason_name VARCHAR2(30);
2453
2454 l_app_from NUMBER;
2455 l_tx_rate NUMBER;
2456
2457 BEGIN
2458 arp_standard.debug('final_commit +');
2459 arp_standard.debug(' customer_trx_id :'||g_customer_trx.customer_trx_id);
2460
2461 savepoint final_commit;
2462
2463 x_return_status := fnd_api.g_ret_sts_success;
2464 OPEN c;
2465 FETCH c INTO x_ra_rec.AMOUNT_APPLIED,
2466 x_ra_rec.AMOUNT_APPLIED_FROM,
2467 x_ra_rec.EARNED_DISCOUNT_TAKEN,
2468 x_ra_rec.UNEARNED_DISCOUNT_TAKEN,
2469 x_ra_rec.LINE_APPLIED,
2470 x_ra_rec.TAX_APPLIED,
2471 x_ra_rec.FREIGHT_APPLIED,
2472 x_ra_rec.RECEIVABLES_CHARGES_APPLIED,
2473 x_ra_rec.EARNED_DISCOUNT_TAKEN,
2474 x_ra_rec.UNEARNED_DISCOUNT_TAKEN,
2475 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM,
2476 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO,
2477 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN,
2478 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN,
2479 x_ra_rec.AMOUNT_APPLIED_FROM,
2480 x_ra_rec.LINE_EDISCOUNTED,
2481 x_ra_rec.TAX_EDISCOUNTED,
2482 x_ra_rec.FREIGHT_EDISCOUNTED,
2483 x_ra_rec.CHARGES_EDISCOUNTED,
2484 x_ra_rec.LINE_UEDISCOUNTED,
2485 x_ra_rec.TAX_UEDISCOUNTED,
2486 x_ra_rec.FREIGHT_UEDISCOUNTED,
2487 x_ra_rec.CHARGES_UEDISCOUNTED,
2488 x_ra_rec.receivable_application_id;
2489 IF c%FOUND THEN
2490 --
2491 UPDATE ar_line_app_detail_gt
2492 SET gt_id = USERENV('SESSIONID')
2493 WHERE gt_id LIKE USERENV('SESSIONID')||'%';
2494 --
2495 UPDATE ar_receivable_apps_gt
2496 SET gt_id = USERENV('SESSIONID')
2497 WHERE gt_id LIKE USERENV('SESSIONID')||'%';
2498
2499 /*
2500 arp_det_dist_pkg.final_update_inv_ctl_rem_orig
2501 (p_customer_trx => g_customer_trx);
2502
2503 arp_det_dist_pkg.create_final_split
2504 (p_customer_trx => g_customer_trx,
2505 p_app_rec => x_ra_rec,
2506 p_adj_rec => l_adj_rec,
2507 p_ae_sys_rec => g_ae_sys_rec);
2508 */
2509
2510 IF NVL(p_amount_applied_from,0) <> 0 THEN
2511
2512 l_app_from := p_amount_applied_from;
2513
2514 ELSE
2515
2516 IF (x_ra_rec.amount_applied_from IS NOT NULL
2517 AND x_ra_rec.amount_applied_from <> 0
2518 AND x_ra_rec.amount_applied_from <> x_ra_rec.amount_applied)
2519 THEN
2520 l_app_from := x_ra_rec.amount_applied_from;
2521 ELSE
2522 l_app_from := x_ra_rec.AMOUNT_APPLIED;
2523 END IF;
2524
2525 END IF;
2526
2527 arp_standard.debug(' x_ra_rec.amount_applied:'||x_ra_rec.amount_applied);
2528 arp_standard.debug(' p_amount_applied_from :'||p_amount_applied_from);
2529 arp_standard.debug(' x_ra_rec.amount_applied_from:'||x_ra_rec.amount_applied_from);
2530 arp_standard.debug(' l_app_from :'||l_app_from);
2531
2532
2533 IF NVL(p_trans_to_receipt_rate,0) <> 0 THEN
2534 l_tx_rate := p_trans_to_receipt_rate;
2535 ELSE
2536 l_tx_rate := x_ra_rec.trans_to_receipt_rate;
2537 END IF;
2538
2539
2540
2541 -- call arp_process_application
2542 arp_process_application.receipt_application(
2543 p_receipt_ps_id => g_payschedule_rec.payment_schedule_id,
2544 p_invoice_ps_id => g_payschedule_trx.payment_schedule_id,
2545 p_amount_applied => x_ra_rec.amount_applied,
2546 p_amount_applied_from => l_app_from,
2547 p_trans_to_receipt_rate => l_tx_rate,
2548 p_invoice_currency_code => g_customer_trx.invoice_currency_code,
2549 p_receipt_currency_code => g_cash_receipt.currency_code,
2550 p_earned_discount_taken => x_ra_rec.earned_discount_taken,
2551 p_unearned_discount_taken =>x_ra_rec.unearned_discount_taken,
2552 p_apply_date => p_apply_date,
2553 p_gl_date => p_gl_date,
2554 p_ussgl_transaction_code => NULL,
2555 p_customer_trx_line_id => NULL,
2556 p_application_ref_type => NULL,
2557 p_application_ref_id => NULL,
2558 p_application_ref_num => NULL,
2559 p_secondary_application_ref_id => NULL,
2560 p_attribute_category => p_attribute_category,
2561 p_attribute1 => p_attribute1,
2562 p_attribute2 => p_attribute2,
2563 p_attribute3 => p_attribute3,
2564 p_attribute4 => p_attribute4,
2565 p_attribute5 => p_attribute5,
2566 p_attribute6 => p_attribute6,
2567 p_attribute7 => p_attribute7,
2568 p_attribute8 => p_attribute8,
2569 p_attribute9 => p_attribute9,
2570 p_attribute10 => p_attribute10,
2571 p_attribute11 => p_attribute11,
2572 p_attribute12 => p_attribute12,
2573 p_attribute13 => p_attribute13,
2574 p_attribute14 => p_attribute14,
2575 p_attribute15 => p_attribute15,
2576 p_global_attribute_category => p_global_attribute_category,
2577 p_global_attribute1 => p_global_attribute1,
2578 p_global_attribute2 => p_global_attribute2,
2579 p_global_attribute3 => p_global_attribute3,
2580 p_global_attribute4 => p_global_attribute4,
2581 p_global_attribute5 => p_global_attribute5,
2582 p_global_attribute6 => p_global_attribute6,
2583 p_global_attribute7 => p_global_attribute7,
2584 p_global_attribute8 => p_global_attribute8,
2585 p_global_attribute9 => p_global_attribute9,
2586 p_global_attribute10 => p_global_attribute10,
2587 p_global_attribute11 => p_global_attribute11,
2588 p_global_attribute12 => p_global_attribute11,
2589 p_global_attribute13 => p_global_attribute13,
2590 p_global_attribute14 => p_global_attribute14,
2591 p_global_attribute15 => p_global_attribute15,
2592 p_global_attribute16 => p_global_attribute16,
2593 p_global_attribute17 => p_global_attribute17,
2594 p_global_attribute18 => p_global_attribute18,
2595 p_global_attribute19 => p_global_attribute19,
2596 p_global_attribute20 => p_global_attribute20,
2597 p_comments => p_comments,
2598 p_module_name => 'LLCAFINALCOMMIT',
2599 p_module_version => '1.0',
2600 -- OUT NOCOPY
2601 x_application_ref_id => x_application_ref_id,
2602 x_application_ref_num => x_application_ref_num,
2603 x_return_status => x_return_status,
2604 x_msg_count => x_msg_count,
2605 x_msg_data => x_msg_data,
2606 p_out_rec_application_id => x_receivable_application_id,
2607 p_acctd_amount_applied_from => x_acctd_amount_applied_from,
2608 p_acctd_amount_applied_to => x_acctd_amount_applied_to,
2609 x_claim_reason_name => x_claim_reason_name,
2610 p_called_from => NULL,
2611 p_move_deferred_tax => NULL,
2612 p_link_to_trx_hist_id => NULL,
2613 p_amount_due_remaining => NULL,
2614 p_payment_set_id => NULL,
2615 p_application_ref_reason => NULL,
2616 --p_customer_reference => NULL,
2617 p_customer_reference => p_customer_reference,--bug12854129
2618 p_customer_reason => NULL,
2619 from_llca_call => 'Y',
2620 p_gt_id => USERENV('SESSIONID'));
2621
2622 END IF;
2623 CLOSE c;
2624
2625 IF x_return_status = fnd_api.g_ret_sts_success THEN
2626 arp_ps_util.populate_closed_dates( p_gl_date,
2627 p_apply_date,
2628 g_payschedule_trx.class,
2629 g_payschedule_trx );
2630 -- update inv ps
2631 arp_ps_pkg.update_p( g_payschedule_trx);
2632
2633 arp_det_dist_pkg.final_update_inv_ctl_rem_orig(p_customer_trx =>g_customer_trx);
2634
2635 x_ra_rec.application_ref_id := x_application_ref_id;
2636 x_ra_rec.application_ref_num := x_application_ref_num;
2637 x_ra_rec.receivable_application_id := x_receivable_application_id;
2638 --{Cross Currency
2639 x_ra_rec.amount_applied_from := p_amount_applied_from;
2640 x_ra_rec.trans_to_receipt_rate := p_trans_to_receipt_rate;
2641 x_ra_rec.acctd_amount_applied_from := x_acctd_amount_applied_from;
2642 x_ra_rec.acctd_amount_applied_to := x_acctd_amount_applied_to; /* Bug 5189370 */
2643 --}
2644 DELETE FROM ra_customer_trx_lines_gt WHERE customer_trx_id = g_customer_trx.customer_trx_id;
2645 g_payschedule_trx := g_payschedule_clr;
2646 g_payschedule_rec := g_payschedule_clr;
2647
2648 DELETE FROM ra_ar_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
2649 DELETE FROM ar_line_app_detail_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
2650 DELETE FROM ar_receivable_apps_gt where gt_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
2651 DELETE FROM ar_ae_alloc_rec_gt where ae_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
2652
2653
2654
2655 END IF;
2656
2657 arp_standard.debug('final_commit -');
2658 EXCEPTION
2659 WHEN OTHERS THEN
2660 ROLLBACK TO final_commit;
2661 arp_standard.debug('EXCEPTION OTHERS final_commit:'||SQLERRM);
2662 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2663 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.final_commit-'||SQLERRM );
2664 FND_MSG_PUB.ADD;
2665 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2666 p_count => x_msg_count,
2667 p_data => x_msg_data);
2668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2669 END final_commit;
2670
2671
2672 /*-----------------------------------------------------------------------------+
2673 | Procedure get_latest_amount_remaining |
2674 +-----------------------------------------------------------------------------+
2675 | Parameter : |
2676 | p_customer_trx_id The invoice ID |
2677 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2678 | p_group_id Group_id req when Application level is GROUP |
2679 | p_ctl_id customer_trx_line_id required when the application level |
2680 | is LINE |
2681 | OUT |
2682 | x_line_rem The remaining revenue amount for the level |
2683 | x_tax_rem The remaining tax amount for the level |
2684 | x_freight_rem The remaining freight amount for the level TRANSACTION only|
2685 | x_charges_rem The remaining charges amount for the level TRANSACTION only|
2686 +-----------------------------------------------------------------------------+
2687 | Action : |
2688 | Read the remaining amount on ra_customer_trx_lines_gt |
2689 +-----------------------------------------------------------------------------*/
2690 PROCEDURE get_latest_amount_remaining
2691 (p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
2692 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
2693 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
2694 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
2695 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
2696 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
2697 p_ctl_id IN NUMBER DEFAULT NULL,
2698 x_line_rem OUT NOCOPY NUMBER,
2699 x_tax_rem OUT NOCOPY NUMBER,
2700 x_freight_rem OUT NOCOPY NUMBER,
2701 x_charges_rem OUT NOCOPY NUMBER,
2702 x_return_status OUT NOCOPY VARCHAR2,
2703 x_msg_data OUT NOCOPY VARCHAR2,
2704 x_msg_count OUT NOCOPY NUMBER)
2705 IS
2706 CURSOR c_trx IS
2707 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2708 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2709 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2710 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2711 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2712 FROM ra_customer_trx_lines_gt
2713 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id;
2714
2715 CURSOR c_line IS
2716 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2717 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2718 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2719 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2720 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2721 FROM ra_customer_trx_lines_gt
2722 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
2723 AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
2724
2725 CURSOR c_gp IS
2726 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2727 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2728 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2729 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2730 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2731 FROM ra_customer_trx_lines_gt
2732 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
2733 AND source_data_key1 = NVL(p_source_data_key1,'00')
2734 AND source_data_key2 = NVL(p_source_data_key2,'00')
2735 AND source_data_key3 = NVL(p_source_data_key3,'00')
2736 AND source_data_key4 = NVL(p_source_data_key4,'00')
2737 AND source_data_key5 = NVL(p_source_data_key5,'00');
2738 BEGIN
2739 arp_standard.debug('get_latest_amount_remaining +');
2740 arp_standard.debug(' customer_trx_id :'||g_customer_trx.customer_trx_id);
2741 arp_standard.debug(' p_app_level :'||p_app_level);
2742 arp_standard.debug(' p_source_data_key1:'||p_source_data_key1);
2743 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2744 IF p_app_level = 'TRANSACTION' THEN
2745 OPEN c_trx;
2746 FETCH c_trx INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2747 CLOSE c_trx;
2748 ELSIF p_app_level = 'GROUP' THEN
2749 OPEN c_gp;
2750 FETCH c_gp INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2751 CLOSE c_gp;
2752 ELSIF p_app_level = 'LINE' THEN
2753 OPEN c_line;
2754 FETCH c_line INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2755 CLOSE c_line;
2756 END IF;
2757 arp_standard.debug(' x_line_rem :'||x_line_rem);
2758 arp_standard.debug(' x_tax_rem :'||x_tax_rem);
2759 arp_standard.debug(' x_freight_rem :'||x_freight_rem);
2760 arp_standard.debug(' x_charges_rem :'||x_charges_rem);
2761 arp_standard.debug('get_latest_amount_remaining -');
2762 EXCEPTION
2763 WHEN NO_DATA_FOUND THEN
2764 arp_standard.debug('EXCEPTION NO_DATA_FOUND get_latest_amount_remaining:'||SQLERRM);
2765 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2766 FND_MESSAGE.SET_TOKEN( 'TEXT', 'get_latest_amount_remaining NO_DATA_FOUND
2767 customer_trx_id :'||g_customer_trx.customer_trx_id||'
2768 p_app_level :'||p_app_level||'
2769 p_ctl_id :'||p_ctl_id);
2770 FND_MSG_PUB.ADD;
2771 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2772 p_count => x_msg_count,
2773 p_data => x_msg_data);
2774 x_return_status := FND_API.G_RET_STS_SUCCESS;
2775 WHEN OTHERS THEN
2776 arp_standard.debug('EXCEPTION OTHERS get_latest_amount_remaining:'||SQLERRM);
2777 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2778 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_latest_amount_remaining-'||SQLERRM );
2779 FND_MSG_PUB.ADD;
2780 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2781 p_count => x_msg_count,
2782 p_data => x_msg_data);
2783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2784
2785 END get_latest_amount_remaining;
2786
2787 /*-----------------------------------------------------------------------------+
2788 | Procedure get_latest_amount_applied |
2789 +-----------------------------------------------------------------------------+
2790 | Parameter : |
2791 | p_customer_trx_id The invoice ID |
2792 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2793 | p_group_id Group_id req when Application level is GROUP |
2794 | p_ctl_id customer_trx_line_id required when the application level |
2795 | is LINE |
2796 | p_log_inv_line 'N'/'Y' if 'N' then only return the amount applied on |
2797 | a trx line. If 'Y' then should the ctl_id be a line |
2798 | type LINE and the TAX and FREIGHT line linked to the LINE|
2799 | line will be part of the result |
2800 | OUT |
2801 | x_line_app The applied revenue amount for the level |
2802 | x_tax_app The applied tax amount for the level |
2803 | x_freight_app The applied freight amount for the level TRANSACTION only |
2804 | x_charges_app The applied charges amount for the level TRANSACTION only |
2805 +-----------------------------------------------------------------------------+
2806 | Action : |
2807 | Read the applied amount on ar_line_aplication_detail_gt |
2808 +-----------------------------------------------------------------------------*/
2809 PROCEDURE get_latest_amount_applied
2810 (p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
2811 --
2812 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
2813 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
2814 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
2815 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
2816 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
2817 p_ctl_id IN NUMBER DEFAULT NULL,
2818 p_log_inv_line IN VARCHAR2 DEFAULT 'Y',
2819 --
2820 x_line_app OUT NOCOPY NUMBER,
2821 x_tax_app OUT NOCOPY NUMBER,
2822 x_freight_app OUT NOCOPY NUMBER,
2823 x_charges_app OUT NOCOPY NUMBER,
2824 --
2825 x_line_ed OUT NOCOPY NUMBER,
2826 x_tax_ed OUT NOCOPY NUMBER,
2827 x_freight_ed OUT NOCOPY NUMBER,
2828 x_charges_ed OUT NOCOPY NUMBER,
2829 --
2830 x_line_uned OUT NOCOPY NUMBER,
2831 x_tax_uned OUT NOCOPY NUMBER,
2832 x_freight_uned OUT NOCOPY NUMBER,
2833 x_charges_uned OUT NOCOPY NUMBER,
2834 --
2835 x_return_status OUT NOCOPY VARCHAR2,
2836 x_msg_data OUT NOCOPY VARCHAR2,
2837 x_msg_count OUT NOCOPY NUMBER)
2838 IS
2839 CURSOR c_trx_local IS
2840 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2841 SUM(NVL(TAX_APPLIED,0)) app_tax,
2842 SUM(NVL(FREIGHT_APPLIED,0)) app_frt,
2843 SUM(NVL(RECEIVABLES_CHARGES_APPLIED,0)) app_chrg,
2844 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2845 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2846 SUM(NVL(FREIGHT_EDISCOUNTED,0)) ed_frt,
2847 SUM(NVL(CHARGES_EDISCOUNTED,0)) ed_chrg,
2848 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2849 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax,
2850 SUM(NVL(FREIGHT_UEDISCOUNTED,0)) uned_frt,
2851 SUM(NVL(CHARGES_UEDISCOUNTED,0)) uned_chrg
2852 FROM ar_receivable_apps_gt;
2853
2854 CURSOR c_gp_local IS
2855 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2856 SUM(NVL(TAX_APPLIED,0)) app_tax,
2857 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2858 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2859 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2860 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
2861 FROM ar_receivable_apps_gt
2862 WHERE SOURCE_DATA_KEY1 = NVL(p_source_data_key1,'00')
2863 AND SOURCE_DATA_KEY2 = NVL(p_source_data_key2,'00')
2864 AND SOURCE_DATA_KEY3 = NVL(p_source_data_key3,'00')
2865 AND SOURCE_DATA_KEY4 = NVL(p_source_data_key4,'00')
2866 AND SOURCE_DATA_KEY5 = NVL(p_source_data_key5,'00');
2867
2868 CURSOR c_log_inv_line IS
2869 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2870 SUM(NVL(TAX_APPLIED,0)) app_tax,
2871 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2872 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2873 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2874 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
2875 FROM ar_receivable_apps_gt
2876 WHERE CTL_ID = p_ctl_id;
2877
2878 CURSOR cu_line IS
2879 SELECT DECODE(line_type,'LINE','OK',line_type)
2880 FROM ra_customer_trx_lines_gt
2881 WHERE customer_trx_line_id = p_ctl_id;
2882
2883 l_line_app NUMBER := 0;
2884 l_tax_app NUMBER := 0;
2885 l_frt_app NUMBER := 0;
2886 l_chrg_app NUMBER := 0;
2887 l_line_ed NUMBER := 0;
2888 l_tax_ed NUMBER := 0;
2889 l_frt_ed NUMBER := 0;
2890 l_chrg_ed NUMBER := 0;
2891 l_line_uned NUMBER := 0;
2892 l_tax_uned NUMBER := 0;
2893 l_frt_uned NUMBER := 0;
2894 l_chrg_uned NUMBER := 0;
2895
2896
2897 l_db_line_app NUMBER := 0;
2898 l_db_tax_app NUMBER := 0;
2899 l_db_frt_app NUMBER := 0;
2900 l_db_chrg_app NUMBER := 0;
2901 l_db_line_ed NUMBER := 0;
2902 l_db_tax_ed NUMBER := 0;
2903 l_db_frt_ed NUMBER := 0;
2904 l_db_chrg_ed NUMBER := 0;
2905 l_db_line_uned NUMBER := 0;
2906 l_db_tax_uned NUMBER := 0;
2907 l_db_frt_uned NUMBER := 0;
2908 l_db_chrg_uned NUMBER := 0;
2909
2910 l_res VARCHAR2(30);
2911 not_a_valid_inv_line EXCEPTION;
2912 not_a_line_type_inv_line EXCEPTION;
2913 BEGIN
2914 arp_standard.debug('get_latest_amount_applied +');
2915 arp_standard.debug(' p_customer_trx_id :'||g_customer_trx.customer_trx_id);
2916 arp_standard.debug(' p_app_level :'||p_app_level);
2917 arp_standard.debug(' p_source_data_key1:'||p_source_data_key1);
2918 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2919
2920 x_line_app := l_line_app;
2921 x_tax_app := l_tax_app;
2922 x_freight_app := l_frt_app;
2923 x_charges_app := l_chrg_app;
2924 x_line_ed := l_line_ed;
2925 x_tax_ed := l_tax_ed;
2926 x_freight_ed := l_frt_ed;
2927 x_charges_ed := l_chrg_ed;
2928 x_line_uned := l_line_uned;
2929 x_tax_uned := l_tax_uned;
2930 x_freight_uned := l_frt_uned;
2931 x_charges_uned := l_chrg_uned;
2932
2933 IF p_app_level = 'TRANSACTION' THEN
2934 get_trx_db_app(x_line_app => l_db_line_app,
2935 x_tax_app => l_db_tax_app,
2936 x_frt_app => l_db_frt_app,
2937 x_chrg_app => l_db_chrg_app,
2938 x_line_ed => l_db_line_ed,
2939 x_tax_ed => l_db_tax_ed,
2940 x_frt_ed => l_db_frt_ed,
2941 x_chrg_ed => l_db_chrg_ed,
2942 x_line_uned=> l_db_line_uned,
2943 x_tax_uned => l_db_tax_uned,
2944 x_frt_uned => l_db_frt_uned,
2945 x_chrg_uned=> l_db_chrg_uned);
2946
2947 IF l_db_line_app IS NULL THEN
2948 l_db_line_app := 0;
2949 l_db_tax_app := 0;
2950 l_db_frt_app := 0;
2951 l_db_chrg_app := 0;
2952 l_db_line_ed := 0;
2953 l_db_tax_ed := 0;
2954 l_db_frt_ed := 0;
2955 l_db_chrg_ed := 0;
2956 l_db_line_uned := 0;
2957 l_db_tax_uned := 0;
2958 l_db_frt_uned := 0;
2959 l_db_chrg_uned := 0;
2960 END IF;
2961
2962 OPEN c_trx_local;
2963 FETCH c_trx_local INTO l_line_app,
2964 l_tax_app,
2965 l_frt_app,
2966 l_chrg_app,
2967 l_line_ed,
2968 l_tax_ed,
2969 l_frt_ed,
2970 l_chrg_ed,
2971 l_line_uned,
2972 l_tax_uned,
2973 l_frt_uned,
2974 l_chrg_uned;
2975
2976 IF (c_trx_local%FOUND) THEN
2977 x_line_app := l_db_line_app + NVL(l_line_app,0);
2978 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
2979 x_freight_app := l_db_frt_app + NVL(l_frt_app,0);
2980 x_charges_app := l_db_chrg_app + NVL(l_chrg_app,0);
2981 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
2982 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
2983 x_freight_ed := l_db_frt_ed + NVL(l_frt_ed,0);
2984 x_charges_ed := l_db_chrg_ed + NVL(l_chrg_ed,0);
2985 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
2986 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
2987 x_freight_uned := l_db_frt_uned + NVL(l_frt_uned,0);
2988 x_charges_uned := l_db_chrg_uned + NVL(l_chrg_uned,0);
2989 ELSE
2990 x_line_app := l_db_line_app;
2991 x_tax_app := l_db_tax_app;
2992 x_freight_app := l_db_frt_app;
2993 x_charges_app := l_db_chrg_app;
2994 x_line_ed := l_db_line_ed;
2995 x_tax_ed := l_db_tax_ed;
2996 x_freight_ed := l_db_frt_ed;
2997 x_charges_ed := l_db_chrg_ed;
2998 x_line_uned := l_db_line_uned;
2999 x_tax_uned := l_db_tax_uned;
3000 x_freight_uned := l_db_frt_uned;
3001 x_charges_uned := l_db_chrg_uned;
3002 END IF;
3003 CLOSE c_trx_local;
3004
3005
3006 ELSIF p_app_level = 'GROUP' THEN
3007
3008 get_group_db_app
3009 (p_source_data_key1 => p_source_data_key1,
3010 p_source_data_key2 => p_source_data_key2,
3011 p_source_data_key3 => p_source_data_key3,
3012 p_source_data_key4 => p_source_data_key4,
3013 p_source_data_key5 => p_source_data_key5,
3014 x_line_app => l_db_line_app,
3015 x_tax_app => l_db_tax_app,
3016 x_line_ed => l_db_line_ed,
3017 x_tax_ed => l_db_tax_ed,
3018 x_line_uned => l_db_line_uned,
3019 x_tax_uned => l_db_tax_uned);
3020
3021 IF l_db_line_app IS NULL THEN
3022 l_db_line_app := 0;
3023 l_db_tax_app := 0;
3024 l_db_line_ed := 0;
3025 l_db_tax_ed := 0;
3026 l_db_line_uned := 0;
3027 l_db_tax_uned := 0;
3028 END IF;
3029
3030 OPEN c_gp_local;
3031 FETCH c_gp_local INTO l_line_app,
3032 l_tax_app,
3033 l_line_ed,
3034 l_tax_ed,
3035 l_line_uned,
3036 l_tax_uned;
3037 IF c_gp_local%FOUND THEN
3038 x_line_app := l_db_line_app + NVL(l_line_app,0);
3039 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
3040 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
3041 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
3042 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
3043 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
3044 ELSE
3045 x_line_app := l_db_line_app;
3046 x_tax_app := l_db_tax_app;
3047 x_line_ed := l_db_line_ed;
3048 x_tax_ed := l_db_tax_ed;
3049 x_line_uned := l_db_line_uned;
3050 x_tax_uned := l_db_tax_uned;
3051 END IF;
3052 CLOSE c_gp_local;
3053
3054 ELSIF p_app_level = 'LINE' THEN
3055
3056 get_log_line_db_app
3057 (p_log_line_id => p_ctl_id,
3058 x_line_app => l_db_line_app,
3059 x_tax_app => l_db_tax_app,
3060 x_line_ed => l_db_line_ed,
3061 x_tax_ed => l_db_tax_ed,
3062 x_line_uned => l_db_line_uned,
3063 x_tax_uned => l_db_tax_uned);
3064
3065 IF l_db_line_app IS NULL THEN
3066 l_db_line_app := 0;
3067 l_db_tax_app := 0;
3068 l_db_line_ed := 0;
3069 l_db_tax_ed := 0;
3070 l_db_line_uned := 0;
3071 l_db_tax_uned := 0;
3072 END IF;
3073
3074 OPEN c_log_inv_line;
3075 FETCH c_log_inv_line INTO l_line_app,
3076 l_tax_app,
3077 l_line_ed,
3078 l_tax_ed,
3079 l_line_uned,
3080 l_tax_uned;
3081 IF c_log_inv_line%FOUND THEN
3082 x_line_app := l_db_line_app + NVL(l_line_app,0);
3083 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
3084 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
3085 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
3086 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
3087 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
3088 ELSE
3089 x_line_app := l_db_line_app;
3090 x_tax_app := l_db_tax_app;
3091 x_line_ed := l_db_line_ed;
3092 x_tax_ed := l_db_tax_ed;
3093 x_line_uned := l_db_line_uned;
3094 x_tax_uned := l_db_tax_uned;
3095 END IF;
3096 CLOSE c_log_inv_line;
3097
3098 END IF;
3099 arp_standard.debug(' x_line_app :'||x_line_app);
3100 arp_standard.debug(' x_tax_app :'||x_tax_app);
3101 arp_standard.debug(' x_freight_app :'||x_freight_app);
3102 arp_standard.debug(' x_charges_app :'||x_charges_app);
3103 arp_standard.debug(' x_line_ed :'||x_line_ed);
3104 arp_standard.debug(' x_tax_ed :'||x_tax_ed);
3105 arp_standard.debug(' x_freight_ed :'||x_freight_ed);
3106 arp_standard.debug(' x_charges_ed :'||x_charges_ed);
3107 arp_standard.debug(' x_line_uned :'||x_line_uned);
3108 arp_standard.debug(' x_tax_uned :'||x_tax_uned);
3109 arp_standard.debug(' x_freight_uned :'||x_freight_uned);
3110 arp_standard.debug(' x_charges_uned :'||x_charges_uned);
3111 arp_standard.debug('get_latest_amount_applied -');
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 arp_standard.debug('EXCEPTION OTHERS get_latest_amount_applied:'||SQLERRM);
3115 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
3116 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_latest_amount_applied-'||SQLERRM );
3117 FND_MSG_PUB.ADD;
3118 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3119 p_count => x_msg_count,
3120 p_data => x_msg_data);
3121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3122 END get_latest_amount_applied;
3123
3124
3125
3126 FUNCTION base_for_proration
3127 (p_customer_trx_id IN NUMBER,
3128 p_gt_id IN NUMBER,
3129 p_line_type IN VARCHAR2,
3130 p_activity IN VARCHAR2)
3131 RETURN NUMBER
3132 IS
3133 CURSOR cu_base IS
3134 SELECT SUM(DECODE(p_activity,
3135 'APP' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
3136 'TAX' , NVL(tax_amount,0) ,0),
3137 'ADJ' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
3138 'TAX' , NVL(tax_amount,0) ,0),
3139 'ED' ,DECODE(p_line_type,'LINE', NVL(ed_line_amount,0),
3140 'TAX' , NVL(ed_tax_amount,0) ,0),
3141 'UNED' ,DECODE(p_line_type,'LINE', NVL(uned_line_amount,0),
3142 'TAX' , NVL(uned_tax_amount,0) ,0),0))
3143 FROM ar_line_dist_interface_gt
3144 WHERE customer_trx_id = p_customer_trx_id
3145 AND gt_id = p_gt_id;
3146 l_res NUMBER;
3147 BEGIN
3148 OPEN cu_base;
3149 FETCH cu_base INTO l_res;
3150 IF cu_base%NOTFOUND THEN
3151 l_res := 0;
3152 END IF;
3153 CLOSE cu_base;
3154 RETURN l_res;
3155 END;
3156
3157 FUNCTION element_for_proration
3158 (p_customer_trx_id IN NUMBER,
3159 p_customer_trx_line_id IN NUMBER,
3160 p_gt_id IN NUMBER,
3161 p_line_type IN VARCHAR2,
3162 p_activity IN VARCHAR2)
3163 RETURN NUMBER
3164 IS
3165 CURSOR cu_element IS
3166 SELECT DECODE(p_activity,'APP' ,DECODE(line_type,'LINE',line_amount , 'TAX',tax_amount,NULL),
3167 'ED' ,DECODE(line_type,'LINE',ed_line_amount , 'TAX',ed_tax_amount,NULL),
3168 'UNED',DECODE(line_type,'LINE',uned_line_amount, 'TAX',uned_tax_amount,NULL),NULL)
3169 FROM ar_line_dist_interface_gt
3170 WHERE customer_trx_id = p_customer_trx_id
3171 AND customer_trx_line_id = p_customer_trx_line_id
3172 AND gt_id = p_gt_id
3173 AND line_type = p_line_type;
3174 l_res NUMBER;
3175 BEGIN
3176 OPEN cu_element;
3177 FETCH cu_element INTO l_res;
3178 IF cu_element%NOTFOUND THEN
3179 l_res := 0;
3180 END IF;
3181 CLOSE cu_element;
3182 RETURN l_res;
3183 END;
3184
3185
3186 PROCEDURE verif_int_adj_line_tax
3187 (p_customer_trx IN ra_customer_trx%ROWTYPE,
3188 p_adj_rec IN ar_adjustments%ROWTYPE,
3189 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
3190 p_gt_id IN NUMBER,
3191 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3192 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3193 x_return_status IN OUT NOCOPY VARCHAR2)
3194 IS
3195 CURSOR verif_amt IS
3196 SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
3197 CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
3198 ,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
3199 FROM ar_line_dist_interface_gt
3200 WHERE gt_id = p_gt_id
3201 AND customer_trx_id = p_customer_trx.customer_trx_id
3202 AND source_table = 'ADJ';
3203
3204 l_sum_line NUMBER;
3205 l_sum_tax NUMBER;
3206 l_sum_ed_line NUMBER;
3207 l_sum_ed_tax NUMBER;
3208 l_sum_uned_line NUMBER;
3209 l_sum_uned_tax NUMBER;
3210 i NUMBER := 0;
3211 BEGIN
3212 arp_standard.debug('verif_int_adj_line_tax +');
3213 arp_standard.debug(' adjustment_id :'||p_adj_rec.adjustment_id);
3214 arp_standard.debug(' p_line_flag :'||p_line_flag);
3215 arp_standard.debug(' p_tax_flag :'||p_tax_flag);
3216
3217 IF p_gt_id IS NULL THEN
3218 x_return_status := fnd_api.g_ret_sts_error;
3219 arp_standard.debug(' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3220 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3221 FND_MESSAGE.SET_TOKEN('TEXT',' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3222 FND_MSG_PUB.ADD;
3223 ELSE
3224
3225 IF ((p_adj_rec.amount IS NULL) AND (p_adj_rec.acctd_amount IS NULL))
3226 OR
3227 ((p_adj_rec.amount = 0 ) AND (p_adj_rec.acctd_amount = 0 ))
3228 THEN
3229 x_return_status := fnd_api.g_ret_sts_error;
3230 arp_standard.debug(' Adjustment record amount and accounted amount causes no need to execute');
3231 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3232 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment record amount and accounted amount causes no need to execute');
3233 FND_MSG_PUB.ADD;
3234 ELSIF ((p_adj_rec.amount IS NULL) AND (p_adj_rec.acctd_amount IS NOT NULL))
3235 OR
3236 ((p_adj_rec.amount = 0 ) AND (p_adj_rec.acctd_amount <> 0 ))
3237 OR
3238 ((p_adj_rec.amount <> p_adj_rec.acctd_amount) AND
3239 (p_customer_trx.invoice_currency_code = p_ae_sys_rec.base_currency))
3240 THEN
3241 x_return_status := fnd_api.g_ret_sts_error;
3242 arp_standard.debug(' Adjustment record combination causes an invalid combination');
3243 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3244 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment record combination causes an invalid combination');
3245 FND_MSG_PUB.ADD;
3246 END IF;
3247
3248 OPEN verif_amt;
3249 FETCH verif_amt INTO l_sum_line ,
3250 l_sum_tax ;
3251 CLOSE verif_amt;
3252
3253 arp_standard.debug(' sum line from ar_line_dist_interface_gt, l_sum_line :'||l_sum_line);
3254 arp_standard.debug(' sum tax from ar_line_dist_interface_gt, l_sum_tax :'||l_sum_tax);
3255
3256 IF (l_sum_line = NULL) AND (l_sum_tax = NULL )
3257 THEN
3258 x_return_status := fnd_api.g_ret_sts_error;
3259 arp_standard.debug(' There is no line amount and tax amount in the interface table for this adjustment');
3260 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3261 FND_MESSAGE.SET_TOKEN('TEXT',' There is no line amount and tax amount in the interface table for adjustment -'||
3262 ' adjustment_id : ' || p_adj_rec.adjustment_id);
3263 END IF;
3264
3265
3266 IF (p_line_flag = 'Y') AND (l_sum_line <> p_adj_rec.line_adjusted) THEN
3267 x_return_status := fnd_api.g_ret_sts_error;
3268 arp_standard.debug(' Adjustment line_adjusted <> l_sum_line from ar_line_dist_interface_gt');
3269 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3270 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment line_adjusted <> l_sum_line from ar_line_dist_interface_gt');
3271 FND_MSG_PUB.ADD;
3272 END IF;
3273
3274 IF (p_tax_flag = 'Y') AND (l_sum_tax <> p_adj_rec.tax_adjusted) THEN
3275 x_return_status := fnd_api.g_ret_sts_error;
3276 arp_standard.debug(' Adjustment tax_adjusted <> l_sum_tax from ar_line_dist_interface_gt');
3277 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3278 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment tax_adjusted <> l_sum_tax from ar_line_dist_interface_gt');
3279 FND_MSG_PUB.ADD;
3280 END IF;
3281
3282 END IF;
3283 arp_standard.debug('verif_int_adj_line_tax -');
3284 END;
3285
3286
3287 PROCEDURE verif_int_app_line_tax
3288 (p_customer_trx IN ra_customer_trx%ROWTYPE,
3289 p_app_rec IN ar_receivable_applications%ROWTYPE,
3290 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
3291 p_gt_id IN NUMBER,
3292 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3293 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3294 p_ed_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
3295 p_ed_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
3296 p_uned_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
3297 p_uned_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
3298 x_return_status IN OUT NOCOPY VARCHAR2)
3299 IS
3300 CURSOR verif_amt IS
3301 SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
3302 CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
3303 ,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
3304 ,CASE WHEN p_ed_line_flag = 'INTERFACE' THEN SUM(NVL(ed_line_amount,0)) ELSE NULL END
3305 ,CASE WHEN p_ed_tax_flag = 'INTERFACE' THEN SUM(NVL(ed_tax_amount,0)) ELSE NULL END
3306 ,CASE WHEN p_uned_line_flag = 'INTERFACE' THEN SUM(NVL(uned_line_amount,0)) ELSE NULL END
3307 ,CASE WHEN p_uned_tax_flag = 'INTERFACE' THEN SUM(NVL(uned_tax_amount,0)) ELSE NULL END
3308 FROM ar_line_dist_interface_gt
3309 WHERE gt_id = p_gt_id
3310 AND customer_trx_id = p_customer_trx.customer_trx_id
3311 AND source_table = 'RA';
3312
3313 l_sum_line NUMBER;
3314 l_sum_tax NUMBER;
3315 l_sum_ed_line NUMBER;
3316 l_sum_ed_tax NUMBER;
3317 l_sum_uned_line NUMBER;
3318 l_sum_uned_tax NUMBER;
3319 i NUMBER := 0;
3320 BEGIN
3321 arp_standard.debug('verif_int_app_line_tax +');
3322 arp_standard.debug(' receivable_application_id :'||p_app_rec.receivable_application_id);
3323 arp_standard.debug(' p_app_rec.amount_applied :'||p_app_rec.amount_applied);
3324 arp_standard.debug(' p_app_rec.acctd_amount_applied_to :'||p_app_rec.acctd_amount_applied_to);
3325 arp_standard.debug(' p_customer_trx.invoice_currency_code:'||p_customer_trx.invoice_currency_code);
3326 arp_standard.debug(' p_ae_sys_rec.base_currency :'||p_ae_sys_rec.base_currency);
3327 arp_standard.debug(' p_line_flag :'||p_line_flag);
3328 arp_standard.debug(' p_tax_flag :'||p_tax_flag);
3329 arp_standard.debug(' p_ed_line_flag :'||p_ed_line_flag);
3330 arp_standard.debug(' p_ed_tax_flag :'||p_ed_tax_flag);
3331 arp_standard.debug(' p_uned_line_flag :'||p_uned_line_flag);
3332 arp_standard.debug(' p_uned_tax_flag :'||p_uned_tax_flag);
3333
3334 IF p_gt_id IS NULL THEN
3335 x_return_status := fnd_api.g_ret_sts_error;
3336 arp_standard.debug(' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3337 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3338 FND_MESSAGE.SET_TOKEN('TEXT',' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3339 FND_MSG_PUB.ADD;
3340 ELSE
3341
3342 IF ((p_app_rec.amount_applied IS NULL) AND (p_app_rec.acctd_amount_applied_to IS NULL))
3343 OR
3344 ((p_app_rec.amount_applied = 0 ) AND (p_app_rec.acctd_amount_applied_to = 0 ))
3345 THEN
3346 x_return_status := fnd_api.g_ret_sts_error;
3347 arp_standard.debug(' Application record amount and accounted amount to causes no need to execute'||
3348 ' as no amount in amount_applied and acctd_amount_applied_to bucket');
3349 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3350 FND_MESSAGE.SET_TOKEN('TEXT',' Application record amount and accounted amount to causes no need to execute'||
3351 ' as no amount in amount_applied and acctd_amount_applied_to bucket');
3352 FND_MSG_PUB.ADD;
3353 ELSIF ((p_app_rec.amount_applied IS NULL) AND (p_app_rec.acctd_amount_applied_to IS NOT NULL))
3354 OR
3355 ((p_app_rec.amount_applied = 0 ) AND (p_app_rec.acctd_amount_applied_to <> 0 ))
3356 OR
3357 ((p_app_rec.amount_applied <> p_app_rec.acctd_amount_applied_to) AND
3358 (p_customer_trx.invoice_currency_code = p_ae_sys_rec.base_currency))
3359 THEN
3360 x_return_status := fnd_api.g_ret_sts_error;
3361 arp_standard.debug(' Application record combination causes an invalid combination on amount bucket');
3362 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3363 FND_MESSAGE.SET_TOKEN('TEXT',' Application record combination causes an invalid combination');
3364 FND_MSG_PUB.ADD;
3365 END IF;
3366
3367 OPEN verif_amt;
3368 FETCH verif_amt INTO l_sum_line ,
3369 l_sum_tax ,
3370 l_sum_ed_line ,
3371 l_sum_ed_tax ,
3372 l_sum_uned_line ,
3373 l_sum_uned_tax ;
3374 CLOSE verif_amt;
3375
3376 arp_standard.debug('TABLE ar_line_dist_interface_gt ');
3377 arp_standard.debug(' l_sum_line :'||l_sum_line);
3378 arp_standard.debug(' l_sum_tax :'||l_sum_tax);
3379 arp_standard.debug(' l_sum_ed_line :'||l_sum_ed_line);
3380 arp_standard.debug(' l_sum_ed_tax :'||l_sum_ed_tax);
3381 arp_standard.debug(' l_sum_uned_line :'||l_sum_uned_line);
3382 arp_standard.debug(' l_sum_uned_tax :'||l_sum_uned_tax);
3383
3384 arp_standard.debug('RECORD p_app_rec ');
3385 arp_standard.debug(' p_app_rec.line_applied :'||p_app_rec.line_applied);
3386 arp_standard.debug(' p_app_rec.tax_applied :'||p_app_rec.tax_applied);
3387 arp_standard.debug(' p_app_rec.LINE_EDISCOUNTED :'||p_app_rec.LINE_EDISCOUNTED);
3388 arp_standard.debug(' p_app_rec.TAX_EDISCOUNTED :'||p_app_rec.TAX_EDISCOUNTED);
3389 arp_standard.debug(' p_app_rec.LINE_UEDISCOUNTED :'||p_app_rec.LINE_UEDISCOUNTED);
3390 arp_standard.debug(' p_app_rec.TAX_UEDISCOUNTED :'||p_app_rec.TAX_UEDISCOUNTED);
3391
3392
3393 IF (l_sum_line = NULL) AND (l_sum_tax = NULL ) AND
3394 (l_sum_ed_line = NULL) AND (l_sum_ed_tax = NULL ) AND
3395 (l_sum_uned_line = NULL) AND (l_sum_uned_tax = NULL )
3396 THEN
3397 x_return_status := fnd_api.g_ret_sts_error;
3398 arp_standard.debug(' There is no line amount, tax amount for app, edisc and unedisc in the interface table for this application');
3399 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3400 FND_MESSAGE.SET_TOKEN('TEXT',' There is no line amount, tax amount for app, edisc and unedisc in the interface table for this application'||
3401 ' receivable_application_id : ' || p_app_rec.receivable_application_id);
3402 END IF;
3403
3404
3405 IF (p_line_flag = 'Y') AND (l_sum_line <> p_app_rec.line_applied) THEN
3406 x_return_status := fnd_api.g_ret_sts_error;
3407 arp_standard.debug(' Application line_applied ('||p_app_rec.line_applied||')<> l_sum_line('||l_sum_line||') from ar_line_dist_interface_gt');
3408 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3409 FND_MESSAGE.SET_TOKEN('TEXT',' Application line_applied ('||p_app_rec.line_applied||')<> l_sum_line('||l_sum_line||') from ar_line_dist_interface_gt');
3410 FND_MSG_PUB.ADD;
3411 END IF;
3412
3413 IF (p_tax_flag = 'Y') AND (l_sum_tax <> p_app_rec.tax_applied) THEN
3414 x_return_status := fnd_api.g_ret_sts_error;
3415 arp_standard.debug(' Adjustment tax_adjusted ('||p_app_rec.tax_applied||')<> l_sum_tax('||l_sum_tax||') from ar_line_dist_interface_gt');
3416 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3417 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment tax_adjusted ('||p_app_rec.tax_applied||')<> l_sum_tax('||l_sum_tax||') from ar_line_dist_interface_gt');
3418 FND_MSG_PUB.ADD;
3419 END IF;
3420
3421 IF (p_ed_line_flag = 'Y') AND (l_sum_ed_line <> p_app_rec.LINE_EDISCOUNTED) THEN
3422 x_return_status := fnd_api.g_ret_sts_error;
3423 arp_standard.debug(' Application line_ediscounted ('||p_app_rec.LINE_EDISCOUNTED||')<> l_sum_ed_line('||l_sum_ed_line||') from ar_line_dist_interface_gt');
3424 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3425 FND_MESSAGE.SET_TOKEN('TEXT',' Application line_ediscounted ('||p_app_rec.LINE_EDISCOUNTED||')<> l_sum_ed_line('||l_sum_ed_line||') from ar_line_dist_interface_gt');
3426 FND_MSG_PUB.ADD;
3427 END IF;
3428
3429 IF (p_ed_tax_flag = 'Y') AND (l_sum_ed_tax <> p_app_rec.TAX_EDISCOUNTED) THEN
3430 x_return_status := fnd_api.g_ret_sts_error;
3431 arp_standard.debug(' Application tax_ediscounted ('||p_app_rec.tax_EDISCOUNTED||')<> l_sum_ed_tax('||l_sum_ed_tax||') from ar_line_dist_interface_gt');
3432 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3433 FND_MESSAGE.SET_TOKEN('TEXT',' Application tax_ediscounted ('||p_app_rec.tax_EDISCOUNTED||')<> l_sum_ed_tax('||l_sum_ed_tax||') from ar_line_dist_interface_gt');
3434 FND_MSG_PUB.ADD;
3435 END IF;
3436
3437 IF (p_uned_line_flag = 'Y') AND (l_sum_uned_line <> p_app_rec.LINE_uEDISCOUNTED) THEN
3438 x_return_status := fnd_api.g_ret_sts_error;
3439 arp_standard.debug(' Application line_uediscounted ('||p_app_rec.LINE_uEDISCOUNTED||')<> l_sum_uned_line('||l_sum_uned_line||') from ar_line_dist_interface_gt');
3440 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3441 FND_MESSAGE.SET_TOKEN('TEXT',' Application line_uediscounted ('||p_app_rec.LINE_uEDISCOUNTED||')<> l_sum_uned_line('||l_sum_uned_line||') from ar_line_dist_interface_gt');
3442 FND_MSG_PUB.ADD;
3443 END IF;
3444
3445 IF (p_uned_tax_flag = 'Y') AND (l_sum_uned_tax <> p_app_rec.TAX_uEDISCOUNTED) THEN
3446 x_return_status := fnd_api.g_ret_sts_error;
3447 arp_standard.debug(' Application tax_uediscounted ('||p_app_rec.tax_uEDISCOUNTED||')<> l_sum_uned_tax('||l_sum_uned_tax||') from ar_line_dist_interface_gt');
3448 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3449 FND_MESSAGE.SET_TOKEN('TEXT',' Application tax_uediscounted ('||p_app_rec.tax_uEDISCOUNTED||')<> l_sum_uned_tax('||l_sum_uned_tax||') from ar_line_dist_interface_gt');
3450 FND_MSG_PUB.ADD;
3451 END IF;
3452
3453 END IF;
3454 arp_standard.debug('verif_int_app_line_tax -');
3455 END;
3456
3457
3458
3459 PROCEDURE breakup_discounts (
3460 /*--------------------------------------------------------------------------+
3461 private procedure used for LLCA bucket-wise breakup RM Jul 19, 2005
3462 +---------------------------------------------------------------------------*/
3463 --in variables
3464 lin_discount_in in NUMBER,
3465 tax_discount_in in NUMBER,
3466 frt_discount_in in NUMBER,
3467 tot_earned_discount_in in NUMBER,
3468 tot_unearned_discount_in in NUMBER,
3469 --out variables
3470 ed_lin_out out nocopy number,
3471 ued_lin_out out nocopy number,
3472 ed_tax_out out nocopy number,
3473 ued_tax_out out nocopy number,
3474 ed_frt_out out nocopy number,
3475 ued_frt_out out nocopy number
3476 )
3477 IS
3478 l_denom number;
3479 begin
3480
3481 arp_standard.debug ('llc brk dsc lin in= ' || lin_discount_in);
3482 arp_standard.debug ('llc brk dsc tax in= ' || tax_discount_in);
3483 arp_standard.debug ('llc brk dsc frt in= ' || frt_discount_in);
3484 arp_standard.debug ('llc brk dsc ed in= ' || tot_earned_discount_in);
3485 arp_standard.debug ('llc brk dsc ued in= ' || tot_unearned_discount_in);
3486
3487 l_denom := tot_earned_discount_in + tot_unearned_discount_in;
3488
3489 if l_denom <> 0 then
3490 ed_lin_out := (lin_discount_in / l_denom) * tot_earned_discount_in;
3491 ued_lin_out := lin_discount_in - ed_lin_out;
3492
3493 ed_tax_out := (tax_discount_in / l_denom) * tot_earned_discount_in;
3494 ued_tax_out := tax_discount_in - ed_tax_out;
3495
3496 ed_frt_out := (frt_discount_in / l_denom) * tot_earned_discount_in;
3497 ued_frt_out := frt_discount_in - ed_frt_out;
3498
3499 end if;
3500 arp_standard.debug ('llc ed_lin_out '||ed_lin_out );
3501 arp_standard.debug ('llc ued_lin_out '||ued_lin_out );
3502 arp_standard.debug ('llc ed_tax_out '||ed_tax_out );
3503 arp_standard.debug ('llc ued_tax_out '||ued_tax_out );
3504 arp_standard.debug ('llc ed_frt_out '||ed_frt_out );
3505 arp_standard.debug ('llc ued_frt_out '||ued_frt_out );
3506
3507 END breakup_discounts;
3508
3509
3510
3511
3512 END arp_process_det_pkg;