[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_DET_PKG
Source
1 PACKAGE BODY arp_process_det_pkg AS
2 /* $Header: ARDLAPPB.pls 120.20.12010000.3 2008/11/18 13:28:11 dgaurab 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 l_freight_applied <> 0 )
861 THEN RETURN 'PBFRTNEG';
862 ELSIF (sign(l_charges_applied) <> sign(p_bc_ps_rec.RECEIVABLES_CHARGES_CHARGED)
863 AND l_charges_applied <> 0 )
864 THEN RETURN 'PBCHRGNEG';
865 END IF;
866 End If;
867 --
868 -- If all bucket 0 then not concern
869 --
870 IF l_line_applied = 0 AND
871 l_tax_applied = 0 AND
872 l_freight_applied = 0 AND
873 l_charges_applied = 0
874 THEN
875 x_amount_applied := 0;
876 RETURN 'N';
877 ELSE
878 x_amount_applied := l_line_applied + l_tax_applied +
879 l_freight_applied + l_charges_applied;
880 RETURN 'Y';
881 END IF;
882 END is_bucket_concern;
883 BEGIN
884 arp_standard.debug('arp_process_det_pkg.upd_inv_ps+' );
885 arp_standard.debug(' p_line_applied :'||p_line_applied);
886 arp_standard.debug(' p_tax_applied :'||p_tax_applied);
887 arp_standard.debug(' p_freight_applied :'||p_freight_applied);
888 arp_standard.debug(' p_charges_applied :'||p_charges_applied);
889 --
890 arp_standard.debug(' p_line_ediscounted :'||p_line_ediscounted);
891 arp_standard.debug(' p_tax_ediscounted :'||p_tax_ediscounted);
892 arp_standard.debug(' p_freight_ediscounted :'||p_freight_ediscounted);
893 arp_standard.debug(' p_charges_ediscounted :'||p_charges_ediscounted);
894 --
895 arp_standard.debug(' p_line_uediscounted :'||p_line_uediscounted);
896 arp_standard.debug(' p_tax_uediscounted :'||p_tax_uediscounted);
897 arp_standard.debug(' p_freight_uediscounted :'||p_freight_uediscounted);
898 arp_standard.debug(' p_charges_uediscounted :'||p_charges_uediscounted);
899 --
900 arp_standard.debug(' payment_schedule_id : '||g_payschedule_trx.payment_schedule_id );
901 l_ps_rec := g_payschedule_trx;
902 l_applied_concern := is_bucket_concern(p_line_applied => p_line_applied,
903 p_tax_applied => p_tax_applied,
904 p_freight_applied => p_freight_applied,
905 p_charges_applied => p_charges_applied,
906 x_amount_applied => l_amount_applied,
907 p_bc_ps_rec => p_ps_rec );
908
909 IF l_applied_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
910 RAISE neg_app_amt;
911 END IF;
912 l_earned_concern := is_bucket_concern(p_line_applied => p_line_ediscounted,
913 p_tax_applied => p_tax_ediscounted,
914 p_freight_applied => p_freight_ediscounted,
915 p_charges_applied => p_charges_ediscounted,
916 x_amount_applied => l_discount_taken_earned,
917 p_bc_ps_rec => p_ps_rec );
918 IF l_earned_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
919 RAISE neg_earned_amt;
920 END IF;
921 l_uearned_concern := is_bucket_concern(p_line_applied => p_line_uediscounted,
922 p_tax_applied => p_tax_uediscounted,
923 p_freight_applied => p_freight_uediscounted,
924 p_charges_applied => p_charges_uediscounted,
925 x_amount_applied => l_discount_taken_unearned,
926 p_bc_ps_rec => p_ps_rec );
927 IF l_earned_concern IN ('PBLINENEG', 'PBTAXNEG','PBFRTNEG','PBCHRGNEG') THEN
928 RAISE neg_unearned_amt;
929 END IF;
930 l_line_discounted := NVL(p_line_uediscounted,0) + NVL(p_line_ediscounted,0);
931 l_tax_discounted := NVL(p_tax_uediscounted,0) + NVL(p_tax_ediscounted,0);
932 l_freight_discounted := NVL(p_freight_uediscounted,0) + NVL(p_freight_ediscounted,0);
933 l_charges_discounted := NVL(p_charges_uediscounted,0) + NVL(p_charges_ediscounted,0);
934 l_discount_taken_total := l_line_discounted + l_tax_discounted
935 + l_freight_discounted + l_charges_discounted;
936
937 IF l_earned_concern = 'Y' THEN
938 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
939 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
940 arp_util.calc_acctd_amount
941 (p_currency => NULL,
942 p_precision => NULL,
943 p_mau => NULL,
944 p_rate => l_ps_rec.exchange_rate,
945 p_type => '-', /** ADR must be reduced by amount_applied */
946 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
947 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
948 p_detail => l_discount_taken_earned, /* Earned discount */
949 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
950 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
951 p_acctd_detail => l_acctd_earned_discount_taken ); /* Acct. amount_applied */
952 END IF;
953 IF l_uearned_concern = 'Y' THEN
954 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
955 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
956 arp_util.calc_acctd_amount
957 (p_currency => NULL,
958 p_precision => NULL,
959 p_mau => NULL,
960 p_rate => l_ps_rec.exchange_rate,
961 p_type => '-', /** ADR must be reduced by amount_applied */
962 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
963 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
964 p_detail => l_discount_taken_unearned, /* Unearned discount */
965 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
966 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
967 p_acctd_detail => l_acctd_unearned_disc_taken ); /* Acct. amount_applied */
968 END IF;
969 IF l_applied_concern = 'Y' THEN
970 l_nocopy_amt_due_remain := l_ps_rec.amount_due_remaining;
971 l_nocopy_acctd_amt_due_remain := l_ps_rec.acctd_amount_due_remaining;
972 arp_util.calc_acctd_amount
973 (p_currency => NULL,
974 p_precision => NULL,
975 p_mau => NULL,
976 p_rate => l_ps_rec.exchange_rate,
977 p_type => '-', /** ADR must be reduced by amount_applied */
978 p_master_from => l_nocopy_amt_due_remain, /* Current ADR */
979 p_acctd_master_from => l_nocopy_acctd_amt_due_remain, /* Current Acctd. ADR */
980 p_detail => l_amount_applied, /* Receipt Amount */
981 p_master_to => l_ps_rec.amount_due_remaining, /* New ADR */
982 p_acctd_master_to => l_ps_rec.acctd_amount_due_remaining, /* New Acctd. ADR */
983 p_acctd_detail => l_acctd_amount_applied ); /* Acct. amount_applied */
984 END IF;
985 l_ps_rec.amount_applied := NVL(l_ps_rec.amount_applied,0)
986 + l_amount_applied;
987 l_ps_rec.discount_taken_earned := NVL(l_ps_rec.discount_taken_earned,0)
988 + l_discount_taken_earned;
989 l_ps_rec.discount_taken_unearned := NVL(l_ps_rec.discount_taken_unearned,0)
990 + l_discount_taken_unearned;
991 l_ps_rec.discount_remaining := NVL(l_ps_rec.discount_remaining,0)
992 - l_discount_taken_total;
993 l_ps_rec.amount_line_items_remaining :=
994 NVL(l_ps_rec.amount_line_items_remaining,0) -
995 ( NVL( p_line_applied, 0 ) +
996 NVL( l_line_discounted, 0 ) );
997 l_ps_rec.receivables_charges_remaining :=
998 NVL (l_ps_rec.receivables_charges_remaining, 0 ) -
999 ( NVL( p_charges_applied, 0 ) +
1000 NVL( l_charges_discounted , 0 ) );
1001 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) -
1002 ( NVL( p_tax_applied, 0 ) +
1003 NVL( l_tax_discounted, 0 ) );
1004 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) -
1005 ( NVL( p_freight_applied, 0 ) +
1006 NVL( l_freight_discounted, 0 ) );
1007 g_payschedule_trx := l_ps_rec;
1008 -- dump_payschedule(g_payschedule_trx);
1009 --
1010 g_gt_id := g_gt_id + 1;
1011 l_gt_id := userenv('SESSIONID')||'_'||g_gt_id;
1012 --
1013 l_apps_rec.GT_ID := l_gt_id;
1014 l_apps_rec.app_level := p_app_level;
1015 l_apps_rec.source_data_key1 := p_source_data_key1;
1016 l_apps_rec.source_data_key2 := p_source_data_key2;
1017 l_apps_rec.source_data_key3 := p_source_data_key3;
1018 l_apps_rec.source_data_key4 := p_source_data_key4;
1019 l_apps_rec.source_data_key5 := p_source_data_key5;
1020 l_apps_rec.ctl_id := p_ctl_id;
1021 --
1022 l_apps_rec.RECEIVABLE_APPLICATION_ID := g_app_ra_id;
1023 l_apps_rec.AMOUNT_APPLIED := l_amount_applied;
1024 l_apps_rec.CODE_COMBINATION_ID := g_inv_rec_ccid;
1025 l_apps_rec.SET_OF_BOOKS_ID := g_ae_sys_rec.set_of_books_id;
1026 l_apps_rec.APPLICATION_TYPE := 'CASH';
1027 l_apps_rec.PAYMENT_SCHEDULE_ID := p_ps_rec.payment_schedule_id;
1028 l_apps_rec.APPLIED_CUSTOMER_TRX_ID := p_ps_rec.customer_trx_id;
1029 l_apps_rec.LINE_APPLIED := p_line_applied;
1030 l_apps_rec.TAX_APPLIED := p_tax_applied;
1031 l_apps_rec.FREIGHT_APPLIED := p_freight_applied;
1032 l_apps_rec.RECEIVABLES_CHARGES_APPLIED:= p_charges_applied;
1033 l_apps_rec.EARNED_DISCOUNT_TAKEN := l_discount_taken_earned;
1034 l_apps_rec.UNEARNED_DISCOUNT_TAKEN := l_discount_taken_unearned;
1035 -- l_apps_rec.ACCTD_AMOUNT_APPLIED_FROM := p_unapp_rec_apps.ACCTD_AMOUNT_APPLIED_FROM;
1036 l_apps_rec.ACCTD_AMOUNT_APPLIED_TO := l_acctd_amount_applied;
1037 l_apps_rec.ACCTD_EARNED_DISCOUNT_TAKEN:= l_acctd_earned_discount_taken;
1038 l_apps_rec.EARNED_DISCOUNT_CCID := g_ed_ccid;
1039 l_apps_rec.UNEARNED_DISCOUNT_CCID := g_uned_ccid;
1040 l_apps_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN := l_acctd_unearned_disc_taken;
1041 -- l_apps_rec.AMOUNT_APPLIED_FROM := p_unapp_rec_apps.AMOUNT_APPLIED_FROM;
1042 l_apps_rec.LINE_EDISCOUNTED := p_line_ediscounted;
1043 l_apps_rec.TAX_EDISCOUNTED := p_tax_ediscounted;
1044 l_apps_rec.FREIGHT_EDISCOUNTED := p_freight_ediscounted;
1045 l_apps_rec.CHARGES_EDISCOUNTED := p_charges_ediscounted;
1046 l_apps_rec.LINE_UEDISCOUNTED := p_line_uediscounted;
1047 l_apps_rec.TAX_UEDISCOUNTED := p_tax_uediscounted;
1048 l_apps_rec.FREIGHT_UEDISCOUNTED := p_freight_uediscounted;
1049 l_apps_rec.CHARGES_UEDISCOUNTED := p_charges_uediscounted;
1050 l_apps_rec.STATUS := 'APP';
1051 insert_rapps_p(p_app_rec => l_apps_rec,
1052 x_return_status => x_return_status);
1053 x_app_rec := l_apps_rec;
1054 arp_standard.debug( 'arp_process_det_pkg.upd_inv_ps-' );
1055 EXCEPTION
1056 WHEN neg_app_amt THEN
1057 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1058 IF l_earned_concern = 'PBLINENEG' THEN
1059 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line applied amt is negative');
1060 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1061 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax applied amt is negative');
1062 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1063 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight applied amt is negative');
1064 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1065 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge applied amt is negative');
1066 END IF;
1067 FND_MSG_PUB.ADD;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 WHEN neg_earned_amt THEN
1070 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1071 IF l_earned_concern = 'PBLINENEG' THEN
1072 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line earned amt is negative');
1073 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1074 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax earned amt is negative');
1075 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1076 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight earned amt is negative');
1077 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1078 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge earned amt is negative');
1079 END IF;
1080 FND_MSG_PUB.ADD;
1081 x_return_status := FND_API.G_RET_STS_ERROR;
1082 WHEN neg_unearned_amt THEN
1083 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1084 IF l_earned_concern = 'PBLINENEG' THEN
1085 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-line unearned amt is negative');
1086 ELSIF l_earned_concern = 'PBTAXNEG' THEN
1087 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-tax unearned amt is negative');
1088 ELSIF l_earned_concern = 'PBFRTNEG' THEN
1089 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-freight unearned amt is negative');
1090 ELSIF l_earned_concern = 'PBCHRGNEG' THEN
1091 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-charge unearned amt is negative');
1092 END IF;
1093 FND_MSG_PUB.ADD;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 WHEN OTHERS THEN
1096 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1097 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.upd_inv_ps-'||SQLERRM );
1098 FND_MSG_PUB.ADD;
1099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1100 arp_util.debug('EXCEPTION OTHERS arp_process_det_pkg.upd_inv_ps:'||SQLERRM );
1101 END upd_inv_ps;
1102
1103
1104
1105 PROCEDURE disp_app_rec
1106 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE)
1107 IS
1108 BEGIN
1109 arp_standard.debug('p_app_rec.GT_ID :'||p_app_rec.GT_ID);
1110 arp_standard.debug('p_app_rec.app_level :'||p_app_rec.app_level);
1111 arp_standard.debug('p_app_rec.group_id :'||p_app_rec.group_id);
1112 arp_standard.debug('p_app_rec.ctl_id :'||p_app_rec.ctl_id);
1113 arp_standard.debug('p_app_rec.RECEIVABLE_APPLICATION_ID :'||p_app_rec.RECEIVABLE_APPLICATION_ID);
1114 arp_standard.debug('p_app_rec.AMOUNT_APPLIED :'||p_app_rec.AMOUNT_APPLIED);
1115 arp_standard.debug('p_app_rec.CODE_COMBINATION_ID :'||p_app_rec.CODE_COMBINATION_ID);
1116 arp_standard.debug('p_app_rec.SET_OF_BOOKS_ID :'||p_app_rec.SET_OF_BOOKS_ID);
1117 arp_standard.debug('p_app_rec.APPLICATION_TYPE :'||p_app_rec.APPLICATION_TYPE);
1118 arp_standard.debug('p_app_rec.PAYMENT_SCHEDULE_ID :'||p_app_rec.PAYMENT_SCHEDULE_ID);
1119 arp_standard.debug('p_app_rec.CASH_RECEIPT_ID :'||p_app_rec.CASH_RECEIPT_ID);
1120 arp_standard.debug('p_app_rec.APPLIED_CUSTOMER_TRX_ID :'||p_app_rec.APPLIED_CUSTOMER_TRX_ID);
1121 arp_standard.debug('p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID :'||p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID);
1122 arp_standard.debug('p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID :'||p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID);
1123 arp_standard.debug('p_app_rec.CUSTOMER_TRX_ID :'||p_app_rec.CUSTOMER_TRX_ID);
1124 arp_standard.debug('p_app_rec.LINE_APPLIED :'||p_app_rec.LINE_APPLIED);
1125 arp_standard.debug('p_app_rec.TAX_APPLIED :'||p_app_rec.TAX_APPLIED);
1126 arp_standard.debug('p_app_rec.FREIGHT_APPLIED :'||p_app_rec.freight_APPLIED);
1127 arp_standard.debug('p_app_rec.RECEIVABLES_CHARGES_APPLIED :'||p_app_rec.RECEIVABLES_CHARGES_APPLIED);
1128 arp_standard.debug('p_app_rec.EARNED_DISCOUNT_TAKEN :'||p_app_rec.EARNED_DISCOUNT_TAKEN);
1129 arp_standard.debug('p_app_rec.UNEARNED_DISCOUNT_TAKEN :'||p_app_rec.UNEARNED_DISCOUNT_TAKEN);
1130 arp_standard.debug('p_app_rec.APPLICATION_RULE :'||p_app_rec.APPLICATION_RULE);
1131 arp_standard.debug('p_app_rec.ACCTD_AMOUNT_APPLIED_FROM :'||p_app_rec.ACCTD_AMOUNT_APPLIED_FROM);
1132 arp_standard.debug('p_app_rec.ACCTD_AMOUNT_APPLIED_TO :'||p_app_rec.ACCTD_AMOUNT_APPLIED_TO);
1133 arp_standard.debug('p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN :'||p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN);
1134 arp_standard.debug('p_app_rec.EARNED_DISCOUNT_CCID :'||p_app_rec.EARNED_DISCOUNT_CCID);
1135 arp_standard.debug('p_app_rec.UNEARNED_DISCOUNT_CCID :'||p_app_rec.UNEARNED_DISCOUNT_CCID);
1136 arp_standard.debug('p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN :'||p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN);
1137 arp_standard.debug('p_app_rec.ORG_ID :'||p_app_rec.ORG_ID);
1138 arp_standard.debug('p_app_rec.AMOUNT_APPLIED_FROM :'||p_app_rec.AMOUNT_APPLIED_FROM );
1139 arp_standard.debug('p_app_rec.RULE_SET_ID :'||p_app_rec.RULE_SET_ID);
1140 arp_standard.debug('p_app_rec.LINE_EDISCOUNTED :'||p_app_rec.LINE_EDISCOUNTED);
1141 arp_standard.debug('p_app_rec.TAX_EDISCOUNTED :'||p_app_rec.TAX_EDISCOUNTED);
1142 arp_standard.debug('p_app_rec.FREIGHT_EDISCOUNTED :'||p_app_rec.FREIGHT_EDISCOUNTED);
1143 arp_standard.debug('p_app_rec.CHARGES_EDISCOUNTED :'||p_app_rec.CHARGES_EDISCOUNTED);
1144 arp_standard.debug('p_app_rec.LINE_UEDISCOUNTED :'||p_app_rec.LINE_UEDISCOUNTED);
1145 arp_standard.debug('p_app_rec.TAX_UEDISCOUNTED :'||p_app_rec.TAX_UEDISCOUNTED);
1146 arp_standard.debug('p_app_rec.FREIGHT_UEDISCOUNTED :'||p_app_rec.FREIGHT_UEDISCOUNTED);
1147 arp_standard.debug('p_app_rec.CHARGES_UEDISCOUNTED :'||p_app_rec.CHARGES_UEDISCOUNTED);
1148 END disp_app_rec;
1149
1150
1151 /*-----------------------------------------------------------------------------+
1152 | Procedure insert_rapps_p |
1153 +-----------------------------------------------------------------------------+
1154 | Parameter : |
1155 | p_app_rec variable of type ar_receivable_apps_gt |
1156 +-----------------------------------------------------------------------------+
1157 | Action : insert p_rec_apps in ar_receivable_apps_gt |
1158 +-----------------------------------------------------------------------------*/
1159 PROCEDURE insert_rapps_p
1160 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1161 x_return_status IN OUT NOCOPY VARCHAR2)
1162 IS
1163 BEGIN
1164 arp_standard.debug('insert_rapps_p +');
1165 disp_app_rec(p_app_rec);
1166 INSERT INTO ar_receivable_apps_gt
1167 (GT_ID
1168 ,app_level
1169 ,source_data_key1
1170 ,source_data_key2
1171 ,source_data_key3
1172 ,source_data_key4
1173 ,source_data_key5
1174 ,ctl_id
1175 ,RECEIVABLE_APPLICATION_ID
1176 ,AMOUNT_APPLIED
1177 ,CODE_COMBINATION_ID
1178 ,SET_OF_BOOKS_ID
1179 ,APPLICATION_TYPE
1180 ,PAYMENT_SCHEDULE_ID
1181 ,CASH_RECEIPT_ID
1182 ,APPLIED_CUSTOMER_TRX_ID
1183 ,APPLIED_CUSTOMER_TRX_LINE_ID
1184 ,APPLIED_PAYMENT_SCHEDULE_ID
1185 ,CUSTOMER_TRX_ID
1186 ,LINE_APPLIED
1187 ,TAX_APPLIED
1188 ,FREIGHT_APPLIED
1189 ,RECEIVABLES_CHARGES_APPLIED
1190 ,EARNED_DISCOUNT_TAKEN
1191 ,UNEARNED_DISCOUNT_TAKEN
1192 ,APPLICATION_RULE
1193 ,ACCTD_AMOUNT_APPLIED_FROM
1194 ,ACCTD_AMOUNT_APPLIED_TO
1195 ,ACCTD_EARNED_DISCOUNT_TAKEN
1196 ,EARNED_DISCOUNT_CCID
1197 ,UNEARNED_DISCOUNT_CCID
1198 ,ACCTD_UNEARNED_DISCOUNT_TAKEN
1199 ,ORG_ID
1200 ,AMOUNT_APPLIED_FROM
1201 ,RULE_SET_ID
1202 ,LINE_EDISCOUNTED
1203 ,TAX_EDISCOUNTED
1204 ,FREIGHT_EDISCOUNTED
1205 ,CHARGES_EDISCOUNTED
1206 ,LINE_UEDISCOUNTED
1207 ,TAX_UEDISCOUNTED
1208 ,FREIGHT_UEDISCOUNTED
1209 ,CHARGES_UEDISCOUNTED) VALUES
1210 (p_app_rec.GT_ID
1211 ,p_app_rec.app_level
1212 ,p_app_rec.source_data_key1
1213 ,p_app_rec.source_data_key2
1214 ,p_app_rec.source_data_key3
1215 ,p_app_rec.source_data_key4
1216 ,p_app_rec.source_data_key5
1217 ,p_app_rec.ctl_id
1218 ,p_app_rec.RECEIVABLE_APPLICATION_ID
1219 ,p_app_rec.AMOUNT_APPLIED
1220 ,p_app_rec.CODE_COMBINATION_ID
1221 ,p_app_rec.SET_OF_BOOKS_ID
1222 ,p_app_rec.APPLICATION_TYPE
1223 ,p_app_rec.PAYMENT_SCHEDULE_ID
1224 ,p_app_rec.CASH_RECEIPT_ID
1225 ,p_app_rec.APPLIED_CUSTOMER_TRX_ID
1226 ,p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID
1227 ,p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID
1228 ,p_app_rec.CUSTOMER_TRX_ID
1229 ,p_app_rec.LINE_APPLIED
1230 ,p_app_rec.TAX_APPLIED
1231 ,p_app_rec.FREIGHT_APPLIED
1232 ,p_app_rec.RECEIVABLES_CHARGES_APPLIED
1233 ,p_app_rec.EARNED_DISCOUNT_TAKEN
1234 ,p_app_rec.UNEARNED_DISCOUNT_TAKEN
1235 ,p_app_rec.APPLICATION_RULE
1236 ,p_app_rec.ACCTD_AMOUNT_APPLIED_FROM
1237 ,p_app_rec.ACCTD_AMOUNT_APPLIED_TO
1238 ,p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN
1239 ,p_app_rec.EARNED_DISCOUNT_CCID
1240 ,p_app_rec.UNEARNED_DISCOUNT_CCID
1241 ,p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN
1242 ,p_app_rec.ORG_ID
1243 ,p_app_rec.AMOUNT_APPLIED_FROM
1244 ,p_app_rec.RULE_SET_ID
1245 ,p_app_rec.LINE_EDISCOUNTED
1246 ,p_app_rec.TAX_EDISCOUNTED
1247 ,p_app_rec.FREIGHT_EDISCOUNTED
1248 ,p_app_rec.CHARGES_EDISCOUNTED
1249 ,p_app_rec.LINE_UEDISCOUNTED
1250 ,p_app_rec.TAX_UEDISCOUNTED
1251 ,p_app_rec.FREIGHT_UEDISCOUNTED
1252 ,p_app_rec.CHARGES_UEDISCOUNTED);
1253 arp_standard.debug('insert_rapps_p -');
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 arp_standard.debug('EXCEPTION insert_rapps_p OTHERS:'||SQLERRM);
1257 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1258 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS insert_rapps_p:'||SQLERRM );
1259 FND_MSG_PUB.ADD;
1260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1261 END insert_rapps_p;
1262
1263
1264 /*-----------------------------------------------------------------------------+
1265 | Procedure res_ctl_rem_amt_for_app |
1266 +-----------------------------------------------------------------------------+
1267 | Parameter : |
1268 | p_app_rec variable of type ar_receivable_apps_gt |
1269 +-----------------------------------------------------------------------------+
1270 | Action : restore the amounts in the ra_customer_trx_lines_gt |
1271 +-----------------------------------------------------------------------------*/
1272 PROCEDURE res_ctl_rem_amt_for_app
1273 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1274 x_return_status IN OUT NOCOPY VARCHAR2)
1275 IS
1276 CURSOR c_app(p_app_rec IN ar_receivable_apps_gt%ROWTYPE)
1277 IS
1278 SELECT SUM(DECODE(b.ref_account_class,
1279 'REV',
1280 DECODE(b.REF_DET_ID,NULL,b.AMOUNT,0),
1281 0))
1282 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE AMOUNT_DUE_REMAINING
1283 SUM(DECODE(b.ref_account_class,
1284 'REV',
1285 DECODE(b.REF_DET_ID,NULL,b.ACCTD_AMOUNT,0),
1286 0))
1287 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE ACCTD_AMOUNT_DUE_REMAINING
1288 SUM(DECODE(b.ref_account_class,
1289 'REV',
1290 DECODE(b.REF_DET_ID,NULL,0,
1291 DECODE(b.SOURCE_TYPE,'FREIGHT',b.AMOUNT,0)),
1292 0))
1293 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_REMAINING
1294 SUM(DECODE(b.ref_account_class,
1295 'REV',
1296 DECODE(b.REF_DET_ID,NULL,0,
1297 DECODE(b.SOURCE_TYPE,'FREIGHT',b.ACCTD_AMOUNT,0)),
1298 0))
1299 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_ACCTD_REMAINING
1300 SUM(DECODE(b.ref_account_class,
1301 'REV',
1302 DECODE(b.REF_DET_ID,NULL,0,
1303 DECODE(b.SOURCE_TYPE,'CHARGES',b.AMOUNT,0)),
1304 0))
1305 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_REMAINING
1306 SUM(DECODE(b.ref_account_class,
1307 'REV',
1308 DECODE(b.REF_DET_ID,NULL,0,
1309 DECODE(b.SOURCE_TYPE,'CHARGES',b.ACCTD_AMOUNT,0)),
1310 0))
1311 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_ACCTD_REMAINING
1312 SUM(DECODE(b.ref_account_class,
1313 'TAX',
1314 b.AMOUNT,
1315 0))
1316 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR TAX
1317 SUM(DECODE(b.ref_account_class,
1318 'TAX',
1319 b.ACCTD_AMOUNT,
1320 0))
1321 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD TAX
1322 SUM(DECODE(b.ref_account_class,
1323 'FREIGHT',
1324 b.AMOUNT,
1325 0))
1326 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR FREIGHT
1327 SUM(DECODE(b.ref_account_class,
1328 'FREIGHT',
1329 b.ACCTD_AMOUNT,
1330 0))
1331 OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD FREIGHT
1332 b.REF_CUSTOMER_TRX_LINE_ID,
1333 c.line_type
1334 FROM AR_LINE_APP_DETAIL_GT b,
1335 ra_customer_trx_lines_gt c
1336 WHERE b.gt_id = p_app_rec.gt_id
1337 AND b.app_level = p_app_rec.app_level
1338 AND b.REF_CUSTOMER_TRX_LINE_ID = c.customer_trx_line_id;
1339
1340 l_rev_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1341 l_rev_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1342 l_frt_adj_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1343 l_frt_adj_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1344 l_chrg_adj_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1345 l_chrg_adj_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1346 l_tax_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1347 l_tax_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1348 l_frt_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1349 l_frt_acctd_amt_rem_tab DBMS_SQL.NUMBER_TABLE;
1350 l_ctl_id_tab DBMS_SQL.NUMBER_TABLE;
1351 l_line_type_tab DBMS_SQL.VARCHAR2_TABLE;
1352 l_last_fetch BOOLEAN := FALSE;
1353 BEGIN
1354 arp_standard.debug('res_ctl_rem_amt_for_app +');
1355 disp_app_rec(p_app_rec);
1356 OPEN c_app(p_app_rec);
1357 LOOP
1358 FETCH c_app BULK COLLECT INTO l_rev_amt_rem_tab,
1359 l_rev_acctd_amt_rem_tab,
1360 l_frt_adj_amt_rem_tab,
1361 l_frt_adj_acctd_amt_rem_tab,
1362 l_chrg_adj_amt_rem_tab,
1363 l_chrg_adj_acctd_amt_rem_tab,
1364 l_tax_amt_rem_tab,
1365 l_tax_acctd_amt_rem_tab,
1366 l_frt_amt_rem_tab,
1367 l_frt_acctd_amt_rem_tab,
1368 l_ctl_id_tab,
1369 l_line_type_tab
1370 LIMIT g_bulk_fetch_rows;
1371
1372 IF c_app%NOTFOUND THEN
1373 l_last_fetch := TRUE;
1374 END IF;
1375
1376 IF (l_ctl_id_tab.COUNT = 0) AND (l_last_fetch) THEN
1377 arp_standard.debug('COUNT = 0 and LAST FETCH ');
1378 EXIT;
1379 END IF;
1380
1381 FORALL i IN l_ctl_id_tab.FIRST .. l_ctl_id_tab.LAST
1382 UPDATE ra_customer_trx_lines_gt
1383 SET AMOUNT_DUE_REMAINING =
1384 DECODE(l_line_type_tab(i),
1385 'LINE', AMOUNT_DUE_REMAINING + l_rev_amt_rem_tab(i),
1386 'FREIGHT',AMOUNT_DUE_REMAINING + l_frt_amt_rem_tab(i),
1387 'TAX', AMOUNT_DUE_REMAINING + l_tax_amt_rem_tab(i),
1388 AMOUNT_DUE_REMAINING),
1389 ACCTD_AMOUNT_DUE_REMAINING =
1390 DECODE(l_line_type_tab(i),
1391 'LINE', ACCTD_AMOUNT_DUE_REMAINING + l_rev_acctd_amt_rem_tab(i),
1392 'FREIGHT',ACCTD_AMOUNT_DUE_REMAINING + l_frt_acctd_amt_rem_tab(i),
1393 'TAX', ACCTD_AMOUNT_DUE_REMAINING + l_tax_acctd_amt_rem_tab(i),
1394 ACCTD_AMOUNT_DUE_REMAINING),
1395 FRT_ADJ_REMAINING =
1396 FRT_ADJ_REMAINING + l_frt_adj_amt_rem_tab(i),
1397 FRT_ADJ_ACCTD_REMAINING =
1398 FRT_ADJ_ACCTD_REMAINING + l_frt_adj_acctd_amt_rem_tab(i),
1399 CHRG_AMOUNT_REMAINING =
1400 CHRG_AMOUNT_REMAINING + l_chrg_adj_amt_rem_tab(i),
1401 CHRG_ACCTD_AMOUNT_REMAINING =
1402 CHRG_ACCTD_AMOUNT_REMAINING + l_chrg_adj_acctd_amt_rem_tab(i)
1403 WHERE customer_trx_line_id = l_ctl_id_tab(i);
1404 END LOOP;
1405 CLOSE c_app;
1406 arp_standard.debug('res_ctl_rem_amt_for_app -');
1407 EXCEPTION
1408 WHEN OTHERS THEN
1409 arp_standard.debug('EXCEPTION res_ctl_rem_amt_for_app OTHERS:'||SQLERRM);
1410 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1411 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS res_ctl_rem_amt_for_app:'||SQLERRM );
1412 FND_MSG_PUB.ADD;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 END res_ctl_rem_amt_for_app;
1415
1416
1417 /*-----------------------------------------------------------------------------+
1418 | Procedure res_inv_ps |
1419 +-----------------------------------------------------------------------------+
1420 | Parameter : |
1421 | p_app_rec variable of type ar_receivable_apps_gt |
1422 +-----------------------------------------------------------------------------+
1423 | Action : restore the amounts in the g_payschedule_trx based on the input|
1424 +-----------------------------------------------------------------------------*/
1425 PROCEDURE res_inv_ps
1426 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1427 x_return_status IN OUT NOCOPY VARCHAR2)
1428 IS
1429 l_ps_rec ar_payment_schedules%ROWTYPE;
1430 l_line_discounted NUMBER;
1431 l_tax_discounted NUMBER;
1432 l_charges_discounted NUMBER;
1433 l_discount_taken_total NUMBER;
1434 l_freight_discounted NUMBER;
1435 BEGIN
1436 arp_standard.debug('res_inv_ps +');
1437 disp_app_rec(p_app_rec);
1438 l_ps_rec := g_payschedule_trx;
1439
1440 l_ps_rec.amount_applied := NVL(l_ps_rec.amount_applied,0)
1441 - p_app_rec.AMOUNT_APPLIED;
1442 l_ps_rec.discount_taken_earned := NVL(l_ps_rec.discount_taken_earned,0)
1443 - p_app_rec.EARNED_DISCOUNT_TAKEN;
1444 l_ps_rec.discount_taken_unearned := NVL(l_ps_rec.discount_taken_unearned,0)
1445 - p_app_rec.UNEARNED_DISCOUNT_TAKEN;
1446
1447 l_line_discounted := NVL(p_app_rec.LINE_UEDISCOUNTED,0) + NVL(p_app_rec.LINE_EDISCOUNTED,0);
1448 l_tax_discounted := NVL(p_app_rec.TAX_UEDISCOUNTED,0) + NVL(p_app_rec.TAX_EDISCOUNTED,0);
1449 l_freight_discounted := NVL(p_app_rec.FREIGHT_UEDISCOUNTED,0) + NVL(p_app_rec.FREIGHT_EDISCOUNTED,0);
1450 l_charges_discounted := NVL(p_app_rec.CHARGES_UEDISCOUNTED,0) + NVL(p_app_rec.CHARGES_EDISCOUNTED,0);
1451
1452 l_discount_taken_total := l_line_discounted + l_tax_discounted
1453 + l_freight_discounted + l_charges_discounted;
1454
1455 l_ps_rec.discount_remaining := NVL(l_ps_rec.discount_remaining,0)
1456 + l_discount_taken_total;
1457
1458 l_ps_rec.amount_line_items_remaining :=
1459 NVL(l_ps_rec.amount_line_items_remaining,0) +
1460 ( NVL( p_app_rec.LINE_APPLIED, 0 ) +
1461 NVL( l_line_discounted, 0 ) );
1462 l_ps_rec.receivables_charges_remaining :=
1463 NVL (l_ps_rec.receivables_charges_remaining, 0 ) +
1464 ( NVL( p_app_rec.RECEIVABLES_CHARGES_APPLIED, 0 ) +
1465 NVL( l_charges_discounted , 0 ) );
1466 l_ps_rec.tax_remaining := NVL( l_ps_rec.tax_remaining, 0 ) +
1467 ( NVL( p_app_rec.TAX_APPLIED, 0 ) +
1468 NVL( l_tax_discounted, 0 ) );
1469 l_ps_rec.freight_remaining := NVL( l_ps_rec.freight_remaining, 0 ) +
1470 ( NVL( p_app_rec.FREIGHT_APPLIED, 0 ) +
1471 NVL( l_freight_discounted, 0 ) );
1472 g_payschedule_trx := l_ps_rec;
1473 arp_standard.debug('res_inv_ps -');
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 arp_standard.debug('EXCEPTION res_inv_ps OTHERS:'||SQLERRM);
1477 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1478 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS res_inv_ps:'||SQLERRM );
1479 FND_MSG_PUB.ADD;
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 END res_inv_ps;
1482
1483
1484 /*-----------------------------------------------------------------------------+
1485 | Procedure delete_application |
1486 +-----------------------------------------------------------------------------+
1487 | Parameter : |
1488 | p_app_rec variable of type ar_receivable_apps_gt |
1489 +-----------------------------------------------------------------------------+
1490 | Action : |
1491 | 1) Call res_inv_ps to restore payment schedule |
1492 | 2) Call res_ctl_rem_amt_for_app to restore the ra_customer_trx_lines_gt |
1493 | amounts |
1494 | 3) Delete the record from ar_receivable_apps_gt |
1495 +-----------------------------------------------------------------------------*/
1496 PROCEDURE delete_application
1497 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1498 x_return_status IN OUT NOCOPY VARCHAR2)
1499 IS
1500 BEGIN
1501 arp_standard.debug('delete_application +');
1502 -- 1 restore ps inv
1503 res_inv_ps(p_app_rec => p_app_rec,
1504 x_return_status => x_return_status);
1505
1506 -- 2 restore inv rem amt
1507 res_ctl_rem_amt_for_app(p_app_rec => p_app_rec,
1508 x_return_status => x_return_status);
1509
1510 -- 3 delete the application from ar_receivable_apps_gt
1511 DELETE FROM ar_receivable_apps_gt
1512 WHERE gt_id = p_app_rec.gt_id
1513 AND app_level = p_app_rec.app_level;
1514
1515 -- 4 delete the distributions created by the application
1516 DELETE FROM AR_LINE_APP_DETAIL_GT
1517 WHERE gt_id = p_app_rec.gt_id
1518 AND app_level = p_app_rec.app_level;
1519
1520 arp_standard.debug('delete_application -');
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 arp_standard.debug('EXCEPTION delete_application OTHERS:'||SQLERRM);
1524 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1525 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS delete_application:'||SQLERRM );
1526 FND_MSG_PUB.ADD;
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 END delete_application;
1529
1530
1531 PROCEDURE copy_app_rec
1532 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1533 x_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE,
1534 x_return_status IN OUT NOCOPY VARCHAR2)
1535 IS
1536 BEGIN
1537 arp_standard.debug('copy_app_rec +');
1538 disp_app_rec(p_app_rec);
1539 x_ra_rec.RECEIVABLE_APPLICATION_ID := p_app_rec.RECEIVABLE_APPLICATION_ID;
1540 x_ra_rec.AMOUNT_APPLIED := p_app_rec.AMOUNT_APPLIED;
1541 x_ra_rec.CODE_COMBINATION_ID := p_app_rec.CODE_COMBINATION_ID;
1542 x_ra_rec.SET_OF_BOOKS_ID := p_app_rec.SET_OF_BOOKS_ID;
1543 x_ra_rec.APPLICATION_TYPE := p_app_rec.APPLICATION_TYPE;
1544 x_ra_rec.PAYMENT_SCHEDULE_ID := p_app_rec.PAYMENT_SCHEDULE_ID;
1545 x_ra_rec.CASH_RECEIPT_ID := p_app_rec.CASH_RECEIPT_ID;
1546 x_ra_rec.APPLIED_CUSTOMER_TRX_ID := p_app_rec.APPLIED_CUSTOMER_TRX_ID;
1547 x_ra_rec.APPLIED_CUSTOMER_TRX_LINE_ID := p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID;
1548 x_ra_rec.APPLIED_PAYMENT_SCHEDULE_ID := p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID;
1549 x_ra_rec.CUSTOMER_TRX_ID := p_app_rec.CUSTOMER_TRX_ID;
1550 x_ra_rec.LINE_APPLIED := p_app_rec.LINE_APPLIED;
1551 x_ra_rec.TAX_APPLIED := p_app_rec.TAX_APPLIED;
1552 x_ra_rec.FREIGHT_APPLIED := p_app_rec.FREIGHT_APPLIED;
1553 x_ra_rec.RECEIVABLES_CHARGES_APPLIED := p_app_rec.RECEIVABLES_CHARGES_APPLIED;
1554 x_ra_rec.EARNED_DISCOUNT_TAKEN := p_app_rec.EARNED_DISCOUNT_TAKEN;
1555 x_ra_rec.UNEARNED_DISCOUNT_TAKEN := p_app_rec.UNEARNED_DISCOUNT_TAKEN;
1556 x_ra_rec.APPLICATION_RULE := p_app_rec.APPLICATION_RULE;
1557 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM := p_app_rec.ACCTD_AMOUNT_APPLIED_FROM;
1558 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO := p_app_rec.ACCTD_AMOUNT_APPLIED_TO;
1559 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN := p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN;
1560 x_ra_rec.EARNED_DISCOUNT_CCID := p_app_rec.EARNED_DISCOUNT_CCID;
1561 x_ra_rec.UNEARNED_DISCOUNT_CCID := p_app_rec.UNEARNED_DISCOUNT_CCID;
1562 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN := p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN;
1563 x_ra_rec.ORG_ID := p_app_rec.ORG_ID;
1564 x_ra_rec.AMOUNT_APPLIED_FROM := p_app_rec.AMOUNT_APPLIED_FROM;
1565 x_ra_rec.RULE_SET_ID := p_app_rec.RULE_SET_ID;
1566 x_ra_rec.LINE_EDISCOUNTED := p_app_rec.LINE_EDISCOUNTED;
1567 x_ra_rec.TAX_EDISCOUNTED := p_app_rec.TAX_EDISCOUNTED;
1568 x_ra_rec.FREIGHT_EDISCOUNTED := p_app_rec.FREIGHT_EDISCOUNTED;
1569 x_ra_rec.CHARGES_EDISCOUNTED := p_app_rec.CHARGES_EDISCOUNTED;
1570 x_ra_rec.LINE_UEDISCOUNTED := p_app_rec.LINE_UEDISCOUNTED;
1571 x_ra_rec.TAX_UEDISCOUNTED := p_app_rec.TAX_UEDISCOUNTED;
1572 x_ra_rec.FREIGHT_UEDISCOUNTED := p_app_rec.FREIGHT_UEDISCOUNTED;
1573 x_ra_rec.CHARGES_UEDISCOUNTED := p_app_rec.CHARGES_UEDISCOUNTED;
1574 x_ra_rec.STATUS := p_app_rec.STATUS;
1575
1576 arp_standard.debug(' x_ra_rec.LINE_APPLIED:'||x_ra_rec.LINE_APPLIED);
1577 arp_standard.debug(' x_ra_rec.TAX_APPLIED:'||x_ra_rec.TAX_APPLIED);
1578
1579 arp_standard.debug('copy_app_rec -');
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 arp_standard.debug('EXCEPTION copy_app_rec OTHERS:'||SQLERRM);
1583 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1584 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS copy_app_rec:'||SQLERRM );
1585 FND_MSG_PUB.ADD;
1586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1587 END copy_app_rec;
1588
1589
1590 /*-----------------------------------------------------------------------------+
1591 | Procedure do_apply |
1592 +-----------------------------------------------------------------------------+
1593 | Parameter : |
1594 | p_app_rec variable of type ar_receivable_apps_gt |
1595 | p_customer_trx invoice record |
1596 | p_ae_sys_rec receivable system parameter |
1597 | p_gt_id global ID |
1598 +-----------------------------------------------------------------------------+
1599 | Action : Call arp_det_dist_pkg to do the application |
1600 +-----------------------------------------------------------------------------*/
1601 PROCEDURE do_apply
1602 (p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
1603 p_gt_id IN VARCHAR2,
1604 x_return_status IN OUT NOCOPY VARCHAR2)
1605 IS
1606 l_ra_rec ar_receivable_applications%ROWTYPE;
1607 BEGIN
1608 arp_standard.debug('do_apply +');
1609 copy_app_rec(p_app_rec => p_app_rec,
1610 x_ra_rec => l_ra_rec,
1611 x_return_status => x_return_status);
1612
1613 dump_sys_param;
1614
1615 IF p_app_rec.app_level = 'TRANSACTION' THEN
1616
1617 ARP_DET_DIST_PKG.Trx_level_cash_apply
1618 (p_customer_trx => g_customer_trx,
1619 p_app_rec => l_ra_rec,
1620 p_ae_sys_rec => g_ae_sys_rec,
1621 p_gt_id => p_gt_id);
1622
1623 ELSIF p_app_rec.app_level = 'GROUP' THEN
1624
1625 ARP_DET_DIST_PKG.Trx_gp_level_cash_apply
1626 (p_customer_trx => g_customer_trx,
1627 --
1628 p_source_data_key1 => p_app_rec.source_data_key1,
1629 p_source_data_key2 => p_app_rec.source_data_key2,
1630 p_source_data_key3 => p_app_rec.source_data_key3,
1631 p_source_data_key4 => p_app_rec.source_data_key4,
1632 p_source_data_key5 => p_app_rec.source_data_key5,
1633 --
1634 p_app_rec => l_ra_rec,
1635 p_ae_sys_rec => g_ae_sys_rec,
1636 p_gt_id => p_gt_id);
1637
1638 ELSIF p_app_rec.app_level = 'LINE' THEN
1639
1640 arp_standard.debug(' HYU l_ra_rec.LINE_APPLIED:'||l_ra_rec.LINE_APPLIED);
1641 arp_standard.debug(' HYU l_ra_rec.TAX_APPLIED:'||l_ra_rec.TAX_APPLIED);
1642
1643 ARP_DET_DIST_PKG.Trx_line_level_cash_apply
1644 (p_customer_trx => g_customer_trx,
1645 p_customer_trx_line_id => p_app_rec.ctl_id,
1646 p_log_inv_line => 'Y',
1647 p_app_rec => l_ra_rec,
1648 p_ae_sys_rec => g_ae_sys_rec,
1649 p_gt_id => p_gt_id);
1650
1651 END IF;
1652 arp_standard.debug('do_apply -');
1653 EXCEPTION
1654 WHEN OTHERS THEN
1655 arp_standard.debug('EXCEPTION do_apply OTHERS:'||SQLERRM);
1656 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1657 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS do_apply:'||SQLERRM );
1658 FND_MSG_PUB.ADD;
1659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1660 END do_apply;
1661
1662
1663 /*-----------------------------------------------------------------------------+
1664 | Procedure apply |
1665 +-----------------------------------------------------------------------------+
1666 | Parameter : |
1667 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
1668 | p_group_id Group_id req when Application level is GROUP |
1669 | p_ctl_id customer_trx_line_id required when the application level |
1670 | is LINE |
1671 | p_line_applied Line amount applied |
1672 | p_tax_applied Tax amount applied |
1673 | p_freight_applied Freight amount applied |
1674 | p_charges_applied Charge amount applied |
1675 | -- |
1676 | p_line_ediscounted Earned Discount on Revenue |
1677 | p_tax_ediscounted Earned Discount on Tax |
1678 | p_freight_ediscounted Earned Discount on Freight |
1679 | p_charges_ediscounted Earned Discount on charge |
1680 | -- |
1681 | p_line_uediscounted Unearned Discount on Revenue |
1682 | p_tax_uediscounted Unearned Discount on Tax |
1683 | p_freight_uediscounted Unearned Discount on Freight |
1684 | p_charges_uediscounted Unearned Discount on charge |
1685 | p_customer_trx Invoice record |
1686 | p_ae_sys_rec Receivable system parameters |
1687 +-----------------------------------------------------------------------------+
1688 | Action : |
1689 | 1) Call upd_inv_ps |
1690 | 2) Call do_apply |
1691 +-----------------------------------------------------------------------------*/
1692 PROCEDURE apply
1693 ( p_app_level IN VARCHAR2,
1694 --
1695 p_source_data_key1 IN VARCHAR2,
1696 p_source_data_key2 IN VARCHAR2,
1697 p_source_data_key3 IN VARCHAR2,
1698 p_source_data_key4 IN VARCHAR2,
1699 p_source_data_key5 IN VARCHAR2,
1700 --
1701 p_ctl_id IN NUMBER,
1702 --
1703 p_line_applied IN NUMBER,
1704 p_tax_applied IN NUMBER,
1705 p_freight_applied IN NUMBER,
1706 p_charges_applied IN NUMBER,
1707 --
1708 p_line_ediscounted IN NUMBER,
1709 p_tax_ediscounted IN NUMBER,
1710 p_freight_ediscounted IN NUMBER,
1711 p_charges_ediscounted IN NUMBER,
1712 --
1713 p_line_uediscounted IN NUMBER,
1714 p_tax_uediscounted IN NUMBER,
1715 p_freight_uediscounted IN NUMBER,
1716 p_charges_uediscounted IN NUMBER,
1717 --
1718 x_return_status IN OUT NOCOPY VARCHAR2)
1719 IS
1720 l_app_rec ar_receivable_apps_gt%ROWTYPE;
1721 BEGIN
1722 arp_standard.debug('apply +');
1723
1724 arp_standard.debug(' Calling upd_inv_ps +');
1725
1726 upd_inv_ps(
1727 p_app_level => p_app_level,
1728 --
1729 p_source_data_key1 => p_source_data_key1,
1730 p_source_data_key2 => p_source_data_key2,
1731 p_source_data_key3 => p_source_data_key3,
1732 p_source_data_key4 => p_source_data_key4,
1733 p_source_data_key5 => p_source_data_key5,
1734 --
1735 p_ctl_id => p_ctl_id,
1736 --
1737 p_line_applied => p_line_applied,
1738 p_tax_applied => p_tax_applied,
1739 p_freight_applied => p_freight_applied,
1740 p_charges_applied => p_charges_applied,
1741 --
1742 p_line_ediscounted => p_line_ediscounted,
1743 p_tax_ediscounted => p_tax_ediscounted,
1744 p_freight_ediscounted => p_freight_ediscounted,
1745 p_charges_ediscounted => p_charges_ediscounted,
1746 --
1747 p_line_uediscounted => p_line_uediscounted,
1748 p_tax_uediscounted => p_tax_uediscounted,
1749 p_freight_uediscounted => p_freight_uediscounted,
1750 p_charges_uediscounted => p_charges_uediscounted,
1751 p_ps_rec => g_payschedule_trx,
1752 --
1753 x_app_rec => l_app_rec,
1754 x_return_status => x_return_status);
1755
1756 arp_standard.debug(' x_return_status :'|| x_return_status);
1757
1758 arp_standard.debug(' Calling upd_inv_ps -');
1759
1760 do_apply
1761 (p_app_rec => l_app_rec,
1762 p_gt_id => l_app_rec.gt_id,
1763 x_return_status=> x_return_status);
1764 arp_standard.debug('apply -');
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 arp_standard.debug('EXCEPTION apply OTHERS:'||SQLERRM);
1768 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1769 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS apply:'||SQLERRM );
1770 FND_MSG_PUB.ADD;
1771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1772 END apply;
1773
1774 PROCEDURE dump_ccid
1775 IS
1776 BEGIN
1777 arp_standard.debug('g_unapplied_ccid :'||g_unapplied_ccid);
1778 arp_standard.debug('g_ed_ccid :'||g_ed_ccid);
1779 arp_standard.debug('g_uned_ccid :'||g_uned_ccid);
1780 arp_standard.debug('g_unidentified_ccid :'||g_unidentified_ccid);
1781 arp_standard.debug('g_clearing_ccid :'||g_clearing_ccid);
1782 arp_standard.debug('g_remittance_ccid :'||g_remittance_ccid);
1783 arp_standard.debug('g_cash_ccid :'||g_cash_ccid);
1784 arp_standard.debug('g_on_account_ccid :'||g_on_account_ccid);
1785 arp_standard.debug('g_factor_ccid :'||g_factor_ccid);
1786 arp_standard.debug('g_inv_rec_ccid :'||g_inv_rec_ccid);
1787 END dump_ccid;
1788
1789 /*-----------------------------------------------------------------------------+
1790 | Procedure initialization |
1791 +-----------------------------------------------------------------------------+
1792 | Parameter : |
1793 | p_customer_trx_id invoice ID |
1794 | p_cash_receipt_id receipt ID |
1795 +-----------------------------------------------------------------------------+
1796 | Action : |
1797 | 1) Call arp_det_dist_pkg to copy trx line into ra_customer_trx_lines_gt |
1798 | 2) Call get_inv_ps to cache the invoice payment schedule |
1799 +-----------------------------------------------------------------------------*/
1800 PROCEDURE initialization
1801 (p_customer_trx_id IN NUMBER,
1802 p_cash_receipt_id IN NUMBER,
1803 x_return_status OUT NOCOPY VARCHAR2,
1804 x_msg_data OUT NOCOPY VARCHAR2,
1805 x_msg_count OUT NOCOPY NUMBER)
1806 IS
1807 CURSOR c IS
1808 SELECT *
1809 FROM ra_customer_trx
1810 WHERE customer_trx_id = p_customer_trx_id;
1811 CURSOR c_cr IS
1812 SELECT *
1813 FROM ar_cash_receipts
1814 WHERE cash_receipt_id = p_cash_receipt_id;
1815 CURSOR c_sys IS
1816 SELECT sob.set_of_books_id,
1817 sob.chart_of_accounts_id,
1818 sob.currency_code,
1819 c.precision,
1820 c.minimum_accountable_unit,
1821 sysp.code_combination_id_gain,
1822 sysp.code_combination_id_loss,
1823 sysp.code_combination_id_round
1824 FROM ar_system_parameters sysp,
1825 gl_sets_of_books sob,
1826 fnd_currencies c
1827 WHERE sob.set_of_books_id = sysp.set_of_books_id
1828 AND sob.currency_code = c.currency_code;
1829 CURSOR c_acct IS
1830 SELECT rma.unapplied_ccid
1831 , ed.code_combination_id
1832 , uned.code_combination_id
1833 , rma.unidentified_ccid
1834 , rma.receipt_clearing_ccid
1835 , rma.remittance_ccid
1836 , rma.cash_ccid
1837 , rma.on_account_ccid
1838 , rma.factor_ccid
1839 , ctlgd.code_combination_id
1840 FROM ar_cash_receipts cr
1841 , ar_cash_receipt_history crh
1842 , ar_receipt_methods rm
1843 , ce_bank_acct_uses aba
1844 , ce_bank_branches_v bp
1845 , ce_bank_accounts cba
1846 , ar_receipt_method_accounts rma
1847 , ar_receivables_trx ed
1848 , ar_receivables_trx uned
1849 , ra_cust_trx_line_gl_dist ctlgd
1850 WHERE cr.cash_receipt_id = p_cash_receipt_id
1851 AND cr.cash_receipt_id = crh.cash_receipt_id
1852 AND crh.current_record_flag = 'Y'
1853 AND rm.receipt_method_id = cr.receipt_method_id
1854 AND cr.remit_bank_acct_use_id = aba.bank_acct_use_id
1855 AND aba.bank_account_id = cba.bank_account_id
1856 AND bp.branch_party_id = cba.bank_branch_id
1857 AND rma.remit_bank_acct_use_id = aba.bank_acct_use_id
1858 AND rma.receipt_method_id = rm.receipt_method_id
1859 AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
1860 AND rma.unedisc_receivables_trx_id= uned.receivables_trx_id (+)
1861 AND ctlgd.customer_trx_id = p_customer_trx_id
1862 AND ctlgd.account_class = 'REC';
1863
1864
1865 not_valid_trx EXCEPTION;
1866 no_sys_param EXCEPTION;
1867 not_valid_cr EXCEPTION;
1868 BEGIN
1869 arp_standard.debug('initialization +');
1870 SAVEPOINT initialization;
1871 x_return_status := fnd_api.g_ret_sts_success;
1872 OPEN c;
1873 FETCH c INTO g_customer_trx;
1874 IF c%NOTFOUND THEN
1875 CLOSE c;
1876 RAISE not_valid_trx;
1877 END IF;
1878 CLOSE c;
1879 OPEN c_cr;
1880 FETCH c_cr INTO g_cash_receipt;
1881 IF c_cr%NOTFOUND THEN
1882 CLOSE c_cr;
1883 RAISE not_valid_cr;
1884 END IF;
1885 CLOSE c_cr;
1886 OPEN c_sys;
1887 FETCH c_sys INTO
1888 g_ae_sys_rec.set_of_books_id,
1889 g_ae_sys_rec.coa_id,
1890 g_ae_sys_rec.base_currency,
1891 g_ae_sys_rec.base_precision,
1892 g_ae_sys_rec.base_min_acc_unit,
1893 g_ae_sys_rec.gain_cc_id,
1894 g_ae_sys_rec.loss_cc_id,
1895 g_ae_sys_rec.round_cc_id;
1896 IF c_sys%NOTFOUND THEN
1897 CLOSE c_sys;
1898 RAISE no_sys_param;
1899 ELSE
1900 g_ae_sys_rec.SOB_TYPE := 'P';
1901 dump_sys_param;
1902 END IF;
1903 CLOSE c_sys;
1904 OPEN c_acct;
1905 FETCH c_acct INTO g_unapplied_ccid
1906 ,g_ed_ccid
1907 ,g_uned_ccid
1908 ,g_unidentified_ccid
1909 ,g_clearing_ccid
1910 ,g_remittance_ccid
1911 ,g_cash_ccid
1912 ,g_on_account_ccid
1913 ,g_factor_ccid
1914 ,g_inv_rec_ccid;
1915 CLOSE c_acct;
1916 dump_ccid;
1917
1918 ARP_DET_DIST_PKG.set_original_rem_amt
1919 (p_customer_trx => g_customer_trx,
1920 p_from_llca => 'Y');
1921
1922 ARP_DET_DIST_PKG.copy_trx_lines (p_customer_trx_id => p_customer_trx_id,
1923 p_ae_sys_rec => g_ae_sys_rec);
1924
1925 get_inv_ps(x_return_status => x_return_status);
1926
1927 get_rec_ps(p_cr_id => g_cash_receipt.cash_receipt_id,
1928 x_return_status => x_return_status);
1929
1930 SELECT ar_receivable_applications_s.nextval
1931 INTO g_app_ra_id
1932 FROM dual;
1933
1934 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1935 RAISE FND_API.G_EXC_ERROR;
1936 END IF;
1937
1938 arp_standard.debug('initialization -');
1939 EXCEPTION
1940 WHEN no_sys_param THEN
1941 ROLLBACK TO initialization;
1942 arp_standard.debug('EXCEPTION initialization no_sys_param');
1943 FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_ROW_IN_SYSTEM_PARAMETERS' );
1944 FND_MSG_PUB.ADD;
1945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1946 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1947 p_count => x_msg_count,
1948 p_data => x_msg_data);
1949 WHEN not_valid_trx THEN
1950 ROLLBACK TO initialization;
1951 arp_standard.debug('EXCEPTION initialization not_valid_trx');
1952 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1953 FND_MESSAGE.SET_TOKEN( 'TEXT', 'not_valid_trx initialization p_customer_trx_id:'||
1954 p_customer_trx_id );
1955 FND_MSG_PUB.ADD;
1956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1957 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1958 p_count => x_msg_count,
1959 p_data => x_msg_data);
1960 WHEN not_valid_cr THEN
1961 ROLLBACK TO initialization;
1962 arp_standard.debug('EXCEPTION initialization not_valid_cr');
1963 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1964 FND_MESSAGE.SET_TOKEN( 'TEXT', 'not_valid_cr initialization p_cash_receipt_id:'||
1965 p_cash_receipt_id );
1966 FND_MSG_PUB.ADD;
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data);
1971 WHEN FND_API.G_EXC_ERROR THEN
1972 ROLLBACK TO initialization;
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1975 p_count => x_msg_count,
1976 p_data => x_msg_data);
1977 WHEN OTHERS THEN
1978 ROLLBACK TO initialization;
1979 arp_standard.debug('EXCEPTION initialization OTHERS:'||SQLERRM);
1980 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
1981 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS initialization:'||SQLERRM );
1982 FND_MSG_PUB.ADD;
1983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1984 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1985 p_count => x_msg_count,
1986 p_data => x_msg_data);
1987 END initialization;
1988
1989
1990 /*-----------------------------------------------------------------------------+
1991 | FUNCTION cur_app_gt_id |
1992 +-----------------------------------------------------------------------------+
1993 | Parameter : |
1994 | p_app_level Application level TRANSACTION/GROUP/LINE |
1995 | p_group_id Group_id required if level is GROUP |
1996 | p_ctl_id customer_trx_line_id required if level is LINE |
1997 | Out variable |
1998 | x_app_rec return the current ar_receivable_apps_gt record matching|
1999 | the search criteria in ar_receivable_apps_gt |
2000 | Return : |
2001 | Gt_id of that record matching the search criteria |
2002 | If no row found the n returns NO_GT_ID |
2003 +-----------------------------------------------------------------------------+
2004 | Action : |
2005 | Search for the current ar_receivable_apps_gt record that match the criteria|
2006 +-----------------------------------------------------------------------------*/
2007 FUNCTION cur_app_gt_id
2008 ( p_app_level IN VARCHAR2,
2009 p_source_data_key1 IN VARCHAR2,
2010 p_source_data_key2 IN VARCHAR2,
2011 p_source_data_key3 IN VARCHAR2,
2012 p_source_data_key4 IN VARCHAR2,
2013 p_source_data_key5 IN VARCHAR2,
2014 p_ctl_id IN NUMBER,
2015 x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE)
2016 RETURN VARCHAR2
2017 IS
2018 CURSOR c_trx IS
2019 SELECT *
2020 FROM ar_receivable_apps_gt
2021 WHERE app_level = 'TRANSACTION';
2022
2023 CURSOR c_grp IS
2024 SELECT *
2025 FROM ar_receivable_apps_gt
2026 WHERE app_level = 'GROUP'
2027 AND source_data_key1 = p_source_data_key1
2028 AND source_data_key2 = p_source_data_key2
2029 AND source_data_key3 = p_source_data_key3
2030 AND source_data_key4 = p_source_data_key4
2031 AND source_data_key5 = p_source_data_key5;
2032
2033 CURSOR c_ctl IS
2034 SELECT *
2035 FROM ar_receivable_apps_gt
2036 WHERE app_level = 'LINE'
2037 AND ctl_id = p_ctl_id;
2038 l_res VARCHAR2(30);
2039 BEGIN
2040 arp_standard.debug('cur_app_gt_id +');
2041 arp_standard.debug(' p_app_level :'||p_app_level);
2042 arp_standard.debug(' p_source_data_key1 :'||p_source_data_key1);
2043 arp_standard.debug(' p_source_data_key2 :'||p_source_data_key2);
2044 arp_standard.debug(' p_source_data_key3 :'||p_source_data_key3);
2045 arp_standard.debug(' p_source_data_key4 :'||p_source_data_key4);
2046 arp_standard.debug(' p_source_data_key5 :'||p_source_data_key5);
2047 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2048 IF p_app_level = 'TRANSACTION' THEN
2049 OPEN c_trx;
2050 FETCH c_trx INTO x_app_rec;
2051 IF c_trx%NOTFOUND THEN
2052 l_res := 'NO_GT_ID';
2053 ELSE
2054 l_res := x_app_rec.gt_id;
2055 END IF;
2056 CLOSE c_trx;
2057 ELSIF p_app_level = 'GROUP' THEN
2058 OPEN c_grp;
2059 FETCH c_grp INTO x_app_rec;
2060 IF c_grp%NOTFOUND THEN
2061 l_res := 'NO_GT_ID';
2062 ELSE
2063 l_res := x_app_rec.gt_id;
2064 END IF;
2065 CLOSE c_grp;
2066 ELSIF p_app_level = 'LINE' THEN
2067 OPEN c_ctl;
2068 FETCH c_ctl INTO x_app_rec;
2069 IF c_ctl%NOTFOUND THEN
2070 l_res := 'NO_GT_ID';
2071 ELSE
2072 l_res := x_app_rec.gt_id;
2073 END IF;
2074 CLOSE c_ctl;
2075 ELSE
2076 l_res := 'X';
2077 END IF;
2078 arp_standard.debug(' l_res :'||l_res);
2079 arp_standard.debug('cur_app_gt_id -');
2080 RETURN l_res;
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 arp_standard.debug('EXCEPTION cur_app_gt_id OTHERS:'||SQLERRM);
2084 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2085 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS cur_app_gt_id:'||SQLERRM );
2086 FND_MSG_PUB.ADD;
2087 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
2088 END cur_app_gt_id;
2089
2090
2091 /*-----------------------------------------------------------------------------+
2092 | Procedure application_execute |
2093 +-----------------------------------------------------------------------------+
2094 | Parameter : |
2095 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2096 | p_group_id Group_id req when Application level is GROUP |
2097 | p_ctl_id customer_trx_line_id required when the application level |
2098 | is LINE |
2099 | p_line_applied Line amount applied |
2100 | p_tax_applied Tax amount applied |
2101 | p_freight_applied Freight amount applied |
2102 | p_charges_applied Charge amount applied |
2103 | -- |
2104 | p_line_ediscounted Earned Discount on Revenue |
2105 | p_tax_ediscounted Earned Discount on Tax |
2106 | p_freight_ediscounted Earned Discount on Freight |
2107 | p_charges_ediscounted Earned Discount on charge |
2108 | -- |
2109 | p_line_uediscounted Unearned Discount on Revenue |
2110 | p_tax_uediscounted Unearned Discount on Tax |
2111 | p_freight_uediscounted Unearned Discount on Freight |
2112 | p_charges_uediscounted Unearned Discount on charge |
2113 | p_customer_trx Invoice record |
2114 | p_ae_sys_rec Receivable system parameters |
2115 +-----------------------------------------------------------------------------+
2116 | Action : |
2117 | 1) Call cur_app_gt_id to looking for current apps |
2118 | 2) If found then call delete_application |
2119 | 3) Call apply to do the application |
2120 +-----------------------------------------------------------------------------*/
2121 PROCEDURE application_execute
2122 ( p_app_level IN VARCHAR2,
2123 p_source_data_key1 IN VARCHAR2,
2124 p_source_data_key2 IN VARCHAR2,
2125 p_source_data_key3 IN VARCHAR2,
2126 p_source_data_key4 IN VARCHAR2,
2127 p_source_data_key5 IN VARCHAR2,
2128 p_ctl_id IN NUMBER,
2129 --
2130 p_line_applied IN NUMBER,
2131 p_tax_applied IN NUMBER,
2132 p_freight_applied IN NUMBER,
2133 p_charges_applied IN NUMBER,
2134 --
2135 p_line_ediscounted IN NUMBER,
2136 p_tax_ediscounted IN NUMBER,
2137 p_freight_ediscounted IN NUMBER,
2138 p_charges_ediscounted IN NUMBER,
2139 --
2140 p_line_uediscounted IN NUMBER,
2141 p_tax_uediscounted IN NUMBER,
2142 p_freight_uediscounted IN NUMBER,
2143 p_charges_uediscounted IN NUMBER,
2144 --
2145 x_return_status OUT NOCOPY VARCHAR2,
2146 x_msg_count OUT NOCOPY NUMBER,
2147 x_msg_data OUT NOCOPY VARCHAR2)
2148 IS
2149 cur_gt_id VARCHAR2(30);
2150 l_app_rec ar_receivable_apps_gt%ROWTYPE;
2151 unexpected_error EXCEPTION;
2152 BEGIN
2153 arp_standard.debug('application_execute +');
2154 SAVEPOINT first_delete_then_apply;
2155
2156 x_return_status := fnd_api.g_ret_sts_success;
2157
2158 cur_gt_id := cur_app_gt_id( p_app_level ,
2159 p_source_data_key1 ,
2160 p_source_data_key2 ,
2161 p_source_data_key3 ,
2162 p_source_data_key4 ,
2163 p_source_data_key5 ,
2164 p_ctl_id ,
2165 l_app_rec);
2166
2167 IF cur_gt_id = 'X' THEN
2168 RAISE unexpected_error;
2169 ELSIF cur_gt_id <> 'NO_GT_ID' THEN
2170 -- First delete
2171 delete_application
2172 (p_app_rec => l_app_rec,
2173 x_return_status => x_return_status);
2174
2175 ELSIF cur_gt_id = FND_API.G_RET_STS_UNEXP_ERROR THEN
2176 RAISE FND_API.G_EXC_ERROR;
2177 END IF;
2178
2179 -- Apply
2180 apply(p_app_level => p_app_level,
2181 --
2182 p_source_data_key1 => p_source_data_key1,
2183 p_source_data_key2 => p_source_data_key2,
2184 p_source_data_key3 => p_source_data_key3,
2185 p_source_data_key4 => p_source_data_key4,
2186 p_source_data_key5 => p_source_data_key5,
2187 --
2188 p_ctl_id => p_ctl_id,
2189 --
2190 p_line_applied => p_line_applied,
2191 p_tax_applied => p_tax_applied,
2192 p_freight_applied => p_freight_applied,
2193 p_charges_applied => p_charges_applied,
2194 --
2195 p_line_ediscounted => p_line_ediscounted,
2196 p_tax_ediscounted => p_tax_ediscounted,
2197 p_freight_ediscounted => p_freight_ediscounted,
2198 p_charges_ediscounted => p_charges_ediscounted,
2199 --
2200 p_line_uediscounted => p_line_uediscounted,
2201 p_tax_uediscounted => p_tax_uediscounted,
2202 p_freight_uediscounted => p_freight_uediscounted,
2203 p_charges_uediscounted => p_charges_uediscounted,
2204 --
2205 x_return_status => x_return_status);
2206
2207 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2208 RAISE FND_API.G_EXC_ERROR;
2209 END IF;
2210
2211 arp_standard.debug('application_execute -');
2212 EXCEPTION
2213 WHEN FND_API.G_EXC_ERROR THEN
2214 ROLLBACK TO first_delete_then_apply;
2215 x_return_status := FND_API.G_RET_STS_ERROR;
2216 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data);
2219 WHEN unexpected_error THEN
2220 ROLLBACK TO first_delete_then_apply;
2221 arp_standard.debug('EXCEPTION first_delete_then_apply unexpected_error - p_app_level:'
2222 ||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
2223 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2224 FND_MESSAGE.SET_TOKEN( 'TEXT', 'Unexpected first_delete_then_apply - p_app_level:'
2225 ||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
2226 FND_MSG_PUB.ADD;
2227 x_return_status := FND_API.G_RET_STS_ERROR;
2228 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2229 p_count => x_msg_count,
2230 p_data => x_msg_data);
2231 WHEN OTHERS THEN
2232 ROLLBACK TO first_delete_then_apply;
2233 arp_standard.debug('EXCEPTION first_delete_then_apply OTHERS:'||SQLERRM);
2234 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2235 FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS first_delete_then_apply:'||SQLERRM );
2236 FND_MSG_PUB.ADD;
2237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2239 p_count => x_msg_count,
2240 p_data => x_msg_data);
2241 END application_execute;
2242
2243 PROCEDURE get_app_ra_amounts
2244 (p_gt_id IN NUMBER,
2245 x_ra_rec IN OUT NOCOPY ar_receivable_applications%ROWTYPE)
2246 IS
2247 CURSOR c IS
2248 SELECT SUM(AMOUNT_APPLIED),
2249 SUM(AMOUNT_APPLIED_FROM),
2250 SUM(EARNED_DISCOUNT_TAKEN),
2251 SUM(UNEARNED_DISCOUNT_TAKEN),
2252 SUM(LINE_APPLIED),
2253 SUM(TAX_APPLIED),
2254 SUM(FREIGHT_APPLIED),
2255 SUM(RECEIVABLES_CHARGES_APPLIED),
2256 SUM(EARNED_DISCOUNT_TAKEN),
2257 SUM(UNEARNED_DISCOUNT_TAKEN),
2258 MAX(ACCTD_AMOUNT_APPLIED_FROM),
2259 SUM(ACCTD_AMOUNT_APPLIED_TO),
2260 SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
2261 SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
2262 MAX(AMOUNT_APPLIED_FROM),
2263 SUM(LINE_EDISCOUNTED),
2264 SUM(TAX_EDISCOUNTED),
2265 SUM(FREIGHT_EDISCOUNTED),
2266 SUM(CHARGES_EDISCOUNTED),
2267 SUM(LINE_UEDISCOUNTED),
2268 SUM(TAX_UEDISCOUNTED),
2269 SUM(FREIGHT_UEDISCOUNTED),
2270 SUM(CHARGES_UEDISCOUNTED),
2271 MAX(receivable_application_id)
2272 FROM ar_receivable_apps_gt
2273 WHERE gt_id = p_gt_id;
2274 BEGIN
2275 OPEN c;
2276 FETCH c INTO
2277 x_ra_rec.amount_applied ,
2278 x_ra_rec.AMOUNT_APPLIED_FROM ,
2279 x_ra_rec.EARNED_DISCOUNT_TAKEN ,
2280 x_ra_rec.UNEARNED_DISCOUNT_TAKEN ,
2281 x_ra_rec.LINE_APPLIED ,
2282 x_ra_rec.TAX_APPLIED ,
2283 x_ra_rec.FREIGHT_APPLIED ,
2284 x_ra_rec.RECEIVABLES_CHARGES_APPLIED,
2285 x_ra_rec.EARNED_DISCOUNT_TAKEN ,
2286 x_ra_rec.UNEARNED_DISCOUNT_TAKEN ,
2287 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM,
2288 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO ,
2289 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN,
2290 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN,
2291 x_ra_rec.AMOUNT_APPLIED_FROM ,
2292 x_ra_rec.LINE_EDISCOUNTED ,
2293 x_ra_rec.TAX_EDISCOUNTED ,
2294 x_ra_rec.FREIGHT_EDISCOUNTED ,
2295 x_ra_rec.CHARGES_EDISCOUNTED ,
2296 x_ra_rec.LINE_UEDISCOUNTED ,
2297 x_ra_rec.TAX_UEDISCOUNTED ,
2298 x_ra_rec.FREIGHT_UEDISCOUNTED ,
2299 x_ra_rec.CHARGES_UEDISCOUNTED ,
2300 x_ra_rec.receivable_application_id;
2301 CLOSE c;
2302 END get_app_ra_amounts;
2303
2304
2305
2306
2307 PROCEDURE final_commit
2308 (p_gl_date IN DATE,
2309 p_apply_date IN DATE,
2310 p_attribute_category IN VARCHAR2 DEFAULT NULL,
2311 p_attribute1 IN VARCHAR2 DEFAULT NULL,
2312 p_attribute2 IN VARCHAR2 DEFAULT NULL,
2313 p_attribute3 IN VARCHAR2 DEFAULT NULL,
2314 p_attribute4 IN VARCHAR2 DEFAULT NULL,
2315 p_attribute5 IN VARCHAR2 DEFAULT NULL,
2316 p_attribute6 IN VARCHAR2 DEFAULT NULL,
2317 p_attribute7 IN VARCHAR2 DEFAULT NULL,
2318 p_attribute8 IN VARCHAR2 DEFAULT NULL,
2319 p_attribute9 IN VARCHAR2 DEFAULT NULL,
2320 p_attribute10 IN VARCHAR2 DEFAULT NULL,
2321 p_attribute11 IN VARCHAR2 DEFAULT NULL,
2322 p_attribute12 IN VARCHAR2 DEFAULT NULL,
2323 p_attribute13 IN VARCHAR2 DEFAULT NULL,
2324 p_attribute14 IN VARCHAR2 DEFAULT NULL,
2325 p_attribute15 IN VARCHAR2 DEFAULT NULL,
2326 p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
2327 p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
2328 p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
2329 p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
2330 p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
2331 p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
2332 p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
2333 p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
2334 p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
2335 p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
2336 p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
2337 p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
2338 p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
2339 p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
2340 p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
2341 p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
2342 p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
2343 p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
2344 p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
2345 p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
2346 p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
2347 p_comments IN VARCHAR2 DEFAULT NULL,
2348 --{Cross Currency
2349 p_amount_applied_from IN NUMBER DEFAULT NULL,
2350 p_trans_to_receipt_rate IN NUMBER DEFAULT NULL,
2351 --}
2352 x_ra_rec OUT NOCOPY ar_receivable_applications%ROWTYPE,
2353 x_return_status OUT NOCOPY VARCHAR2,
2354 x_msg_count OUT NOCOPY NUMBER,
2355 x_msg_data OUT NOCOPY VARCHAR2)
2356 IS
2357 CURSOR c IS
2358 SELECT SUM(AMOUNT_APPLIED),
2359 SUM(AMOUNT_APPLIED_FROM),
2360 SUM(EARNED_DISCOUNT_TAKEN),
2361 SUM(UNEARNED_DISCOUNT_TAKEN),
2362 SUM(LINE_APPLIED),
2363 SUM(TAX_APPLIED),
2364 SUM(FREIGHT_APPLIED),
2365 SUM(RECEIVABLES_CHARGES_APPLIED),
2366 SUM(EARNED_DISCOUNT_TAKEN),
2367 SUM(UNEARNED_DISCOUNT_TAKEN),
2368 MAX(ACCTD_AMOUNT_APPLIED_FROM),
2369 SUM(ACCTD_AMOUNT_APPLIED_TO),
2370 SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
2371 SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
2372 MAX(AMOUNT_APPLIED_FROM),
2373 SUM(LINE_EDISCOUNTED),
2374 SUM(TAX_EDISCOUNTED),
2375 SUM(FREIGHT_EDISCOUNTED),
2376 SUM(CHARGES_EDISCOUNTED),
2377 SUM(LINE_UEDISCOUNTED),
2378 SUM(TAX_UEDISCOUNTED),
2379 SUM(FREIGHT_UEDISCOUNTED),
2380 SUM(CHARGES_UEDISCOUNTED),
2381 MAX(receivable_application_id)
2382 FROM ar_receivable_apps_gt
2383 WHERE applied_customer_trx_id = g_customer_trx.customer_trx_id;
2384 l_adj_rec ar_adjustments%ROWTYPE;
2385
2386 x_application_ref_id NUMBER;
2387 x_application_ref_num VARCHAR2(30);
2388 x_receivable_application_id NUMBER;
2389 x_acctd_amount_applied_from NUMBER;
2390 x_acctd_amount_applied_to NUMBER;
2391 x_claim_reason_name VARCHAR2(30);
2392
2393 l_app_from NUMBER;
2394 l_tx_rate NUMBER;
2395
2396 BEGIN
2397 arp_standard.debug('final_commit +');
2398 arp_standard.debug(' customer_trx_id :'||g_customer_trx.customer_trx_id);
2399
2400 savepoint final_commit;
2401
2402 x_return_status := fnd_api.g_ret_sts_success;
2403 OPEN c;
2404 FETCH c INTO x_ra_rec.AMOUNT_APPLIED,
2405 x_ra_rec.AMOUNT_APPLIED_FROM,
2406 x_ra_rec.EARNED_DISCOUNT_TAKEN,
2407 x_ra_rec.UNEARNED_DISCOUNT_TAKEN,
2408 x_ra_rec.LINE_APPLIED,
2409 x_ra_rec.TAX_APPLIED,
2410 x_ra_rec.FREIGHT_APPLIED,
2411 x_ra_rec.RECEIVABLES_CHARGES_APPLIED,
2412 x_ra_rec.EARNED_DISCOUNT_TAKEN,
2413 x_ra_rec.UNEARNED_DISCOUNT_TAKEN,
2414 x_ra_rec.ACCTD_AMOUNT_APPLIED_FROM,
2415 x_ra_rec.ACCTD_AMOUNT_APPLIED_TO,
2416 x_ra_rec.ACCTD_EARNED_DISCOUNT_TAKEN,
2417 x_ra_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN,
2418 x_ra_rec.AMOUNT_APPLIED_FROM,
2419 x_ra_rec.LINE_EDISCOUNTED,
2420 x_ra_rec.TAX_EDISCOUNTED,
2421 x_ra_rec.FREIGHT_EDISCOUNTED,
2422 x_ra_rec.CHARGES_EDISCOUNTED,
2423 x_ra_rec.LINE_UEDISCOUNTED,
2424 x_ra_rec.TAX_UEDISCOUNTED,
2425 x_ra_rec.FREIGHT_UEDISCOUNTED,
2426 x_ra_rec.CHARGES_UEDISCOUNTED,
2427 x_ra_rec.receivable_application_id;
2428 IF c%FOUND THEN
2429 --
2430 UPDATE ar_line_app_detail_gt
2431 SET gt_id = USERENV('SESSIONID')
2432 WHERE gt_id LIKE USERENV('SESSIONID')||'%';
2433 --
2434 UPDATE ar_receivable_apps_gt
2435 SET gt_id = USERENV('SESSIONID')
2436 WHERE gt_id LIKE USERENV('SESSIONID')||'%';
2437
2438 /*
2439 arp_det_dist_pkg.final_update_inv_ctl_rem_orig
2440 (p_customer_trx => g_customer_trx);
2441
2442 arp_det_dist_pkg.create_final_split
2443 (p_customer_trx => g_customer_trx,
2444 p_app_rec => x_ra_rec,
2445 p_adj_rec => l_adj_rec,
2446 p_ae_sys_rec => g_ae_sys_rec);
2447 */
2448
2449 IF NVL(p_amount_applied_from,0) <> 0 THEN
2450
2451 l_app_from := p_amount_applied_from;
2452
2453 ELSE
2454
2455 IF (x_ra_rec.amount_applied_from IS NOT NULL
2456 AND x_ra_rec.amount_applied_from <> 0
2457 AND x_ra_rec.amount_applied_from <> x_ra_rec.amount_applied)
2458 THEN
2459 l_app_from := x_ra_rec.amount_applied_from;
2460 ELSE
2461 l_app_from := x_ra_rec.AMOUNT_APPLIED;
2462 END IF;
2463
2464 END IF;
2465
2466 arp_standard.debug(' x_ra_rec.amount_applied:'||x_ra_rec.amount_applied);
2467 arp_standard.debug(' p_amount_applied_from :'||p_amount_applied_from);
2468 arp_standard.debug(' x_ra_rec.amount_applied_from:'||x_ra_rec.amount_applied_from);
2469 arp_standard.debug(' l_app_from :'||l_app_from);
2470
2471
2472 IF NVL(p_trans_to_receipt_rate,0) <> 0 THEN
2473 l_tx_rate := p_trans_to_receipt_rate;
2474 ELSE
2475 l_tx_rate := x_ra_rec.trans_to_receipt_rate;
2476 END IF;
2477
2478
2479
2480 -- call arp_process_application
2481 arp_process_application.receipt_application(
2482 p_receipt_ps_id => g_payschedule_rec.payment_schedule_id,
2483 p_invoice_ps_id => g_payschedule_trx.payment_schedule_id,
2484 p_amount_applied => x_ra_rec.amount_applied,
2485 p_amount_applied_from => l_app_from,
2486 p_trans_to_receipt_rate => l_tx_rate,
2487 p_invoice_currency_code => g_customer_trx.invoice_currency_code,
2488 p_receipt_currency_code => g_cash_receipt.currency_code,
2489 p_earned_discount_taken => x_ra_rec.earned_discount_taken,
2490 p_unearned_discount_taken =>x_ra_rec.unearned_discount_taken,
2491 p_apply_date => p_apply_date,
2492 p_gl_date => p_gl_date,
2493 p_ussgl_transaction_code => NULL,
2494 p_customer_trx_line_id => NULL,
2495 p_application_ref_type => NULL,
2496 p_application_ref_id => NULL,
2497 p_application_ref_num => NULL,
2498 p_secondary_application_ref_id => NULL,
2499 p_attribute_category => p_attribute_category,
2500 p_attribute1 => p_attribute1,
2501 p_attribute2 => p_attribute2,
2502 p_attribute3 => p_attribute3,
2503 p_attribute4 => p_attribute4,
2504 p_attribute5 => p_attribute5,
2505 p_attribute6 => p_attribute6,
2506 p_attribute7 => p_attribute7,
2507 p_attribute8 => p_attribute8,
2508 p_attribute9 => p_attribute9,
2509 p_attribute10 => p_attribute10,
2510 p_attribute11 => p_attribute11,
2511 p_attribute12 => p_attribute12,
2512 p_attribute13 => p_attribute13,
2513 p_attribute14 => p_attribute14,
2514 p_attribute15 => p_attribute15,
2515 p_global_attribute_category => p_global_attribute_category,
2516 p_global_attribute1 => p_global_attribute1,
2517 p_global_attribute2 => p_global_attribute2,
2518 p_global_attribute3 => p_global_attribute3,
2519 p_global_attribute4 => p_global_attribute4,
2520 p_global_attribute5 => p_global_attribute5,
2521 p_global_attribute6 => p_global_attribute6,
2522 p_global_attribute7 => p_global_attribute7,
2523 p_global_attribute8 => p_global_attribute8,
2524 p_global_attribute9 => p_global_attribute9,
2525 p_global_attribute10 => p_global_attribute10,
2526 p_global_attribute11 => p_global_attribute11,
2527 p_global_attribute12 => p_global_attribute11,
2528 p_global_attribute13 => p_global_attribute13,
2529 p_global_attribute14 => p_global_attribute14,
2530 p_global_attribute15 => p_global_attribute15,
2531 p_global_attribute16 => p_global_attribute16,
2532 p_global_attribute17 => p_global_attribute17,
2533 p_global_attribute18 => p_global_attribute18,
2534 p_global_attribute19 => p_global_attribute19,
2535 p_global_attribute20 => p_global_attribute20,
2536 p_comments => p_comments,
2537 p_module_name => 'LLCAFINALCOMMIT',
2538 p_module_version => '1.0',
2539 -- OUT NOCOPY
2540 x_application_ref_id => x_application_ref_id,
2541 x_application_ref_num => x_application_ref_num,
2542 x_return_status => x_return_status,
2543 x_msg_count => x_msg_count,
2544 x_msg_data => x_msg_data,
2545 p_out_rec_application_id => x_receivable_application_id,
2546 p_acctd_amount_applied_from => x_acctd_amount_applied_from,
2547 p_acctd_amount_applied_to => x_acctd_amount_applied_to,
2548 x_claim_reason_name => x_claim_reason_name,
2549 p_called_from => NULL,
2550 p_move_deferred_tax => NULL,
2551 p_link_to_trx_hist_id => NULL,
2552 p_amount_due_remaining => NULL,
2553 p_payment_set_id => NULL,
2554 p_application_ref_reason => NULL,
2555 p_customer_reference => NULL,
2556 p_customer_reason => NULL,
2557 from_llca_call => 'Y',
2558 p_gt_id => USERENV('SESSIONID'));
2559
2560 END IF;
2561 CLOSE c;
2562
2563 IF x_return_status = fnd_api.g_ret_sts_success THEN
2564 arp_ps_util.populate_closed_dates( p_gl_date,
2565 p_apply_date,
2566 g_payschedule_trx.class,
2567 g_payschedule_trx );
2568 -- update inv ps
2569 arp_ps_pkg.update_p( g_payschedule_trx);
2570
2571 arp_det_dist_pkg.final_update_inv_ctl_rem_orig(p_customer_trx =>g_customer_trx);
2572
2573 x_ra_rec.application_ref_id := x_application_ref_id;
2574 x_ra_rec.application_ref_num := x_application_ref_num;
2575 x_ra_rec.receivable_application_id := x_receivable_application_id;
2576 --{Cross Currency
2577 x_ra_rec.amount_applied_from := p_amount_applied_from;
2578 x_ra_rec.trans_to_receipt_rate := p_trans_to_receipt_rate;
2579 x_ra_rec.acctd_amount_applied_from := x_acctd_amount_applied_from;
2580 x_ra_rec.acctd_amount_applied_to := x_acctd_amount_applied_to; /* Bug 5189370 */
2581 --}
2582 DELETE FROM ra_customer_trx_lines_gt WHERE customer_trx_id = g_customer_trx.customer_trx_id;
2583 g_payschedule_trx := g_payschedule_clr;
2584 g_payschedule_rec := g_payschedule_clr;
2585
2586 DELETE FROM ra_ar_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
2587 DELETE FROM ar_line_app_detail_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
2588 DELETE FROM ar_receivable_apps_gt where gt_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
2589 DELETE FROM ar_ae_alloc_rec_gt where ae_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
2590
2591
2592
2593 END IF;
2594
2595 arp_standard.debug('final_commit -');
2596 EXCEPTION
2597 WHEN OTHERS THEN
2598 ROLLBACK TO final_commit;
2599 arp_standard.debug('EXCEPTION OTHERS final_commit:'||SQLERRM);
2600 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2601 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.final_commit-'||SQLERRM );
2602 FND_MSG_PUB.ADD;
2603 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2604 p_count => x_msg_count,
2605 p_data => x_msg_data);
2606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2607 END final_commit;
2608
2609
2610 /*-----------------------------------------------------------------------------+
2611 | Procedure get_latest_amount_remaining |
2612 +-----------------------------------------------------------------------------+
2613 | Parameter : |
2614 | p_customer_trx_id The invoice ID |
2615 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2616 | p_group_id Group_id req when Application level is GROUP |
2617 | p_ctl_id customer_trx_line_id required when the application level |
2618 | is LINE |
2619 | OUT |
2620 | x_line_rem The remaining revenue amount for the level |
2621 | x_tax_rem The remaining tax amount for the level |
2622 | x_freight_rem The remaining freight amount for the level TRANSACTION only|
2623 | x_charges_rem The remaining charges amount for the level TRANSACTION only|
2624 +-----------------------------------------------------------------------------+
2625 | Action : |
2626 | Read the remaining amount on ra_customer_trx_lines_gt |
2627 +-----------------------------------------------------------------------------*/
2628 PROCEDURE get_latest_amount_remaining
2629 (p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
2630 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
2631 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
2632 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
2633 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
2634 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
2635 p_ctl_id IN NUMBER DEFAULT NULL,
2636 x_line_rem OUT NOCOPY NUMBER,
2637 x_tax_rem OUT NOCOPY NUMBER,
2638 x_freight_rem OUT NOCOPY NUMBER,
2639 x_charges_rem OUT NOCOPY NUMBER,
2640 x_return_status OUT NOCOPY VARCHAR2,
2641 x_msg_data OUT NOCOPY VARCHAR2,
2642 x_msg_count OUT NOCOPY NUMBER)
2643 IS
2644 CURSOR c_trx IS
2645 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2646 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2647 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2648 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2649 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2650 FROM ra_customer_trx_lines_gt
2651 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id;
2652
2653 CURSOR c_line IS
2654 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2655 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2656 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2657 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2658 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2659 FROM ra_customer_trx_lines_gt
2660 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
2661 AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
2662
2663 CURSOR c_gp IS
2664 SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
2665 SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
2666 SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
2667 SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
2668 SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
2669 FROM ra_customer_trx_lines_gt
2670 WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
2671 AND source_data_key1 = NVL(p_source_data_key1,'00')
2672 AND source_data_key2 = NVL(p_source_data_key2,'00')
2673 AND source_data_key3 = NVL(p_source_data_key3,'00')
2674 AND source_data_key4 = NVL(p_source_data_key4,'00')
2675 AND source_data_key5 = NVL(p_source_data_key5,'00');
2676 BEGIN
2677 arp_standard.debug('get_latest_amount_remaining +');
2678 arp_standard.debug(' customer_trx_id :'||g_customer_trx.customer_trx_id);
2679 arp_standard.debug(' p_app_level :'||p_app_level);
2680 arp_standard.debug(' p_source_data_key1:'||p_source_data_key1);
2681 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2682 IF p_app_level = 'TRANSACTION' THEN
2683 OPEN c_trx;
2684 FETCH c_trx INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2685 CLOSE c_trx;
2686 ELSIF p_app_level = 'GROUP' THEN
2687 OPEN c_gp;
2688 FETCH c_gp INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2689 CLOSE c_gp;
2690 ELSIF p_app_level = 'LINE' THEN
2691 OPEN c_line;
2692 FETCH c_line INTO x_line_rem, x_tax_rem, x_charges_rem, x_freight_rem;
2693 CLOSE c_line;
2694 END IF;
2695 arp_standard.debug(' x_line_rem :'||x_line_rem);
2696 arp_standard.debug(' x_tax_rem :'||x_tax_rem);
2697 arp_standard.debug(' x_freight_rem :'||x_freight_rem);
2698 arp_standard.debug(' x_charges_rem :'||x_charges_rem);
2699 arp_standard.debug('get_latest_amount_remaining -');
2700 EXCEPTION
2701 WHEN NO_DATA_FOUND THEN
2702 arp_standard.debug('EXCEPTION NO_DATA_FOUND get_latest_amount_remaining:'||SQLERRM);
2703 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2704 FND_MESSAGE.SET_TOKEN( 'TEXT', 'get_latest_amount_remaining NO_DATA_FOUND
2705 customer_trx_id :'||g_customer_trx.customer_trx_id||'
2706 p_app_level :'||p_app_level||'
2707 p_ctl_id :'||p_ctl_id);
2708 FND_MSG_PUB.ADD;
2709 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2710 p_count => x_msg_count,
2711 p_data => x_msg_data);
2712 x_return_status := FND_API.G_RET_STS_SUCCESS;
2713 WHEN OTHERS THEN
2714 arp_standard.debug('EXCEPTION OTHERS get_latest_amount_remaining:'||SQLERRM);
2715 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
2716 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_latest_amount_remaining-'||SQLERRM );
2717 FND_MSG_PUB.ADD;
2718 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2719 p_count => x_msg_count,
2720 p_data => x_msg_data);
2721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2722
2723 END get_latest_amount_remaining;
2724
2725 /*-----------------------------------------------------------------------------+
2726 | Procedure get_latest_amount_applied |
2727 +-----------------------------------------------------------------------------+
2728 | Parameter : |
2729 | p_customer_trx_id The invoice ID |
2730 | p_app_level Application Level (TRANSACTION/GROUP/LINE) |
2731 | p_group_id Group_id req when Application level is GROUP |
2732 | p_ctl_id customer_trx_line_id required when the application level |
2733 | is LINE |
2734 | p_log_inv_line 'N'/'Y' if 'N' then only return the amount applied on |
2735 | a trx line. If 'Y' then should the ctl_id be a line |
2736 | type LINE and the TAX and FREIGHT line linked to the LINE|
2737 | line will be part of the result |
2738 | OUT |
2739 | x_line_app The applied revenue amount for the level |
2740 | x_tax_app The applied tax amount for the level |
2741 | x_freight_app The applied freight amount for the level TRANSACTION only |
2742 | x_charges_app The applied charges amount for the level TRANSACTION only |
2743 +-----------------------------------------------------------------------------+
2744 | Action : |
2745 | Read the applied amount on ar_line_aplication_detail_gt |
2746 +-----------------------------------------------------------------------------*/
2747 PROCEDURE get_latest_amount_applied
2748 (p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
2749 --
2750 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
2751 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
2752 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
2753 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
2754 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
2755 p_ctl_id IN NUMBER DEFAULT NULL,
2756 p_log_inv_line IN VARCHAR2 DEFAULT 'Y',
2757 --
2758 x_line_app OUT NOCOPY NUMBER,
2759 x_tax_app OUT NOCOPY NUMBER,
2760 x_freight_app OUT NOCOPY NUMBER,
2761 x_charges_app OUT NOCOPY NUMBER,
2762 --
2763 x_line_ed OUT NOCOPY NUMBER,
2764 x_tax_ed OUT NOCOPY NUMBER,
2765 x_freight_ed OUT NOCOPY NUMBER,
2766 x_charges_ed OUT NOCOPY NUMBER,
2767 --
2768 x_line_uned OUT NOCOPY NUMBER,
2769 x_tax_uned OUT NOCOPY NUMBER,
2770 x_freight_uned OUT NOCOPY NUMBER,
2771 x_charges_uned OUT NOCOPY NUMBER,
2772 --
2773 x_return_status OUT NOCOPY VARCHAR2,
2774 x_msg_data OUT NOCOPY VARCHAR2,
2775 x_msg_count OUT NOCOPY NUMBER)
2776 IS
2777 CURSOR c_trx_local IS
2778 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2779 SUM(NVL(TAX_APPLIED,0)) app_tax,
2780 SUM(NVL(FREIGHT_APPLIED,0)) app_frt,
2781 SUM(NVL(RECEIVABLES_CHARGES_APPLIED,0)) app_chrg,
2782 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2783 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2784 SUM(NVL(FREIGHT_EDISCOUNTED,0)) ed_frt,
2785 SUM(NVL(CHARGES_EDISCOUNTED,0)) ed_chrg,
2786 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2787 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax,
2788 SUM(NVL(FREIGHT_UEDISCOUNTED,0)) uned_frt,
2789 SUM(NVL(CHARGES_UEDISCOUNTED,0)) uned_chrg
2790 FROM ar_receivable_apps_gt;
2791
2792 CURSOR c_gp_local IS
2793 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2794 SUM(NVL(TAX_APPLIED,0)) app_tax,
2795 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2796 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2797 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2798 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
2799 FROM ar_receivable_apps_gt
2800 WHERE SOURCE_DATA_KEY1 = NVL(p_source_data_key1,'00')
2801 AND SOURCE_DATA_KEY2 = NVL(p_source_data_key2,'00')
2802 AND SOURCE_DATA_KEY3 = NVL(p_source_data_key3,'00')
2803 AND SOURCE_DATA_KEY4 = NVL(p_source_data_key4,'00')
2804 AND SOURCE_DATA_KEY5 = NVL(p_source_data_key5,'00');
2805
2806 CURSOR c_log_inv_line IS
2807 SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
2808 SUM(NVL(TAX_APPLIED,0)) app_tax,
2809 SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
2810 SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
2811 SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
2812 SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
2813 FROM ar_receivable_apps_gt
2814 WHERE CTL_ID = p_ctl_id;
2815
2816 CURSOR cu_line IS
2817 SELECT DECODE(line_type,'LINE','OK',line_type)
2818 FROM ra_customer_trx_lines_gt
2819 WHERE customer_trx_line_id = p_ctl_id;
2820
2821 l_line_app NUMBER := 0;
2822 l_tax_app NUMBER := 0;
2823 l_frt_app NUMBER := 0;
2824 l_chrg_app NUMBER := 0;
2825 l_line_ed NUMBER := 0;
2826 l_tax_ed NUMBER := 0;
2827 l_frt_ed NUMBER := 0;
2828 l_chrg_ed NUMBER := 0;
2829 l_line_uned NUMBER := 0;
2830 l_tax_uned NUMBER := 0;
2831 l_frt_uned NUMBER := 0;
2832 l_chrg_uned NUMBER := 0;
2833
2834
2835 l_db_line_app NUMBER := 0;
2836 l_db_tax_app NUMBER := 0;
2837 l_db_frt_app NUMBER := 0;
2838 l_db_chrg_app NUMBER := 0;
2839 l_db_line_ed NUMBER := 0;
2840 l_db_tax_ed NUMBER := 0;
2841 l_db_frt_ed NUMBER := 0;
2842 l_db_chrg_ed NUMBER := 0;
2843 l_db_line_uned NUMBER := 0;
2844 l_db_tax_uned NUMBER := 0;
2845 l_db_frt_uned NUMBER := 0;
2846 l_db_chrg_uned NUMBER := 0;
2847
2848 l_res VARCHAR2(30);
2849 not_a_valid_inv_line EXCEPTION;
2850 not_a_line_type_inv_line EXCEPTION;
2851 BEGIN
2852 arp_standard.debug('get_latest_amount_applied +');
2853 arp_standard.debug(' p_customer_trx_id :'||g_customer_trx.customer_trx_id);
2854 arp_standard.debug(' p_app_level :'||p_app_level);
2855 arp_standard.debug(' p_source_data_key1:'||p_source_data_key1);
2856 arp_standard.debug(' p_ctl_id :'||p_ctl_id);
2857
2858 x_line_app := l_line_app;
2859 x_tax_app := l_tax_app;
2860 x_freight_app := l_frt_app;
2861 x_charges_app := l_chrg_app;
2862 x_line_ed := l_line_ed;
2863 x_tax_ed := l_tax_ed;
2864 x_freight_ed := l_frt_ed;
2865 x_charges_ed := l_chrg_ed;
2866 x_line_uned := l_line_uned;
2867 x_tax_uned := l_tax_uned;
2868 x_freight_uned := l_frt_uned;
2869 x_charges_uned := l_chrg_uned;
2870
2871 IF p_app_level = 'TRANSACTION' THEN
2872 get_trx_db_app(x_line_app => l_db_line_app,
2873 x_tax_app => l_db_tax_app,
2874 x_frt_app => l_db_frt_app,
2875 x_chrg_app => l_db_chrg_app,
2876 x_line_ed => l_db_line_ed,
2877 x_tax_ed => l_db_tax_ed,
2878 x_frt_ed => l_db_frt_ed,
2879 x_chrg_ed => l_db_chrg_ed,
2880 x_line_uned=> l_db_line_uned,
2881 x_tax_uned => l_db_tax_uned,
2882 x_frt_uned => l_db_frt_uned,
2883 x_chrg_uned=> l_db_chrg_uned);
2884
2885 IF l_db_line_app IS NULL THEN
2886 l_db_line_app := 0;
2887 l_db_tax_app := 0;
2888 l_db_frt_app := 0;
2889 l_db_chrg_app := 0;
2890 l_db_line_ed := 0;
2891 l_db_tax_ed := 0;
2892 l_db_frt_ed := 0;
2893 l_db_chrg_ed := 0;
2894 l_db_line_uned := 0;
2895 l_db_tax_uned := 0;
2896 l_db_frt_uned := 0;
2897 l_db_chrg_uned := 0;
2898 END IF;
2899
2900 OPEN c_trx_local;
2901 FETCH c_trx_local INTO l_line_app,
2902 l_tax_app,
2903 l_frt_app,
2904 l_chrg_app,
2905 l_line_ed,
2906 l_tax_ed,
2907 l_frt_ed,
2908 l_chrg_ed,
2909 l_line_uned,
2910 l_tax_uned,
2911 l_frt_uned,
2912 l_chrg_uned;
2913
2914 IF (c_trx_local%FOUND) THEN
2915 x_line_app := l_db_line_app + NVL(l_line_app,0);
2916 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
2917 x_freight_app := l_db_frt_app + NVL(l_frt_app,0);
2918 x_charges_app := l_db_chrg_app + NVL(l_chrg_app,0);
2919 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
2920 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
2921 x_freight_ed := l_db_frt_ed + NVL(l_frt_ed,0);
2922 x_charges_ed := l_db_chrg_ed + NVL(l_chrg_ed,0);
2923 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
2924 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
2925 x_freight_uned := l_db_frt_uned + NVL(l_frt_uned,0);
2926 x_charges_uned := l_db_chrg_uned + NVL(l_chrg_uned,0);
2927 ELSE
2928 x_line_app := l_db_line_app;
2929 x_tax_app := l_db_tax_app;
2930 x_freight_app := l_db_frt_app;
2931 x_charges_app := l_db_chrg_app;
2932 x_line_ed := l_db_line_ed;
2933 x_tax_ed := l_db_tax_ed;
2934 x_freight_ed := l_db_frt_ed;
2935 x_charges_ed := l_db_chrg_ed;
2936 x_line_uned := l_db_line_uned;
2937 x_tax_uned := l_db_tax_uned;
2938 x_freight_uned := l_db_frt_uned;
2939 x_charges_uned := l_db_chrg_uned;
2940 END IF;
2941 CLOSE c_trx_local;
2942
2943
2944 ELSIF p_app_level = 'GROUP' THEN
2945
2946 get_group_db_app
2947 (p_source_data_key1 => p_source_data_key1,
2948 p_source_data_key2 => p_source_data_key2,
2949 p_source_data_key3 => p_source_data_key3,
2950 p_source_data_key4 => p_source_data_key4,
2951 p_source_data_key5 => p_source_data_key5,
2952 x_line_app => l_db_line_app,
2953 x_tax_app => l_db_tax_app,
2954 x_line_ed => l_db_line_ed,
2955 x_tax_ed => l_db_tax_ed,
2956 x_line_uned => l_db_line_uned,
2957 x_tax_uned => l_db_tax_uned);
2958
2959 IF l_db_line_app IS NULL THEN
2960 l_db_line_app := 0;
2961 l_db_tax_app := 0;
2962 l_db_line_ed := 0;
2963 l_db_tax_ed := 0;
2964 l_db_line_uned := 0;
2965 l_db_tax_uned := 0;
2966 END IF;
2967
2968 OPEN c_gp_local;
2969 FETCH c_gp_local INTO l_line_app,
2970 l_tax_app,
2971 l_line_ed,
2972 l_tax_ed,
2973 l_line_uned,
2974 l_tax_uned;
2975 IF c_gp_local%FOUND THEN
2976 x_line_app := l_db_line_app + NVL(l_line_app,0);
2977 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
2978 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
2979 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
2980 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
2981 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
2982 ELSE
2983 x_line_app := l_db_line_app;
2984 x_tax_app := l_db_tax_app;
2985 x_line_ed := l_db_line_ed;
2986 x_tax_ed := l_db_tax_ed;
2987 x_line_uned := l_db_line_uned;
2988 x_tax_uned := l_db_tax_uned;
2989 END IF;
2990 CLOSE c_gp_local;
2991
2992 ELSIF p_app_level = 'LINE' THEN
2993
2994 get_log_line_db_app
2995 (p_log_line_id => p_ctl_id,
2996 x_line_app => l_db_line_app,
2997 x_tax_app => l_db_tax_app,
2998 x_line_ed => l_db_line_ed,
2999 x_tax_ed => l_db_tax_ed,
3000 x_line_uned => l_db_line_uned,
3001 x_tax_uned => l_db_tax_uned);
3002
3003 IF l_db_line_app IS NULL THEN
3004 l_db_line_app := 0;
3005 l_db_tax_app := 0;
3006 l_db_line_ed := 0;
3007 l_db_tax_ed := 0;
3008 l_db_line_uned := 0;
3009 l_db_tax_uned := 0;
3010 END IF;
3011
3012 OPEN c_log_inv_line;
3013 FETCH c_log_inv_line INTO l_line_app,
3014 l_tax_app,
3015 l_line_ed,
3016 l_tax_ed,
3017 l_line_uned,
3018 l_tax_uned;
3019 IF c_log_inv_line%FOUND THEN
3020 x_line_app := l_db_line_app + NVL(l_line_app,0);
3021 x_tax_app := l_db_tax_app + NVL(l_tax_app,0);
3022 x_line_ed := l_db_line_ed + NVL(l_line_ed,0);
3023 x_tax_ed := l_db_tax_ed + NVL(l_tax_ed,0);
3024 x_line_uned := l_db_line_uned + NVL(l_line_uned,0);
3025 x_tax_uned := l_db_tax_uned + NVL(l_tax_uned,0);
3026 ELSE
3027 x_line_app := l_db_line_app;
3028 x_tax_app := l_db_tax_app;
3029 x_line_ed := l_db_line_ed;
3030 x_tax_ed := l_db_tax_ed;
3031 x_line_uned := l_db_line_uned;
3032 x_tax_uned := l_db_tax_uned;
3033 END IF;
3034 CLOSE c_log_inv_line;
3035
3036 END IF;
3037 arp_standard.debug(' x_line_app :'||x_line_app);
3038 arp_standard.debug(' x_tax_app :'||x_tax_app);
3039 arp_standard.debug(' x_freight_app :'||x_freight_app);
3040 arp_standard.debug(' x_charges_app :'||x_charges_app);
3041 arp_standard.debug(' x_line_ed :'||x_line_ed);
3042 arp_standard.debug(' x_tax_ed :'||x_tax_ed);
3043 arp_standard.debug(' x_freight_ed :'||x_freight_ed);
3044 arp_standard.debug(' x_charges_ed :'||x_charges_ed);
3045 arp_standard.debug(' x_line_uned :'||x_line_uned);
3046 arp_standard.debug(' x_tax_uned :'||x_tax_uned);
3047 arp_standard.debug(' x_freight_uned :'||x_freight_uned);
3048 arp_standard.debug(' x_charges_uned :'||x_charges_uned);
3049 arp_standard.debug('get_latest_amount_applied -');
3050 EXCEPTION
3051 WHEN OTHERS THEN
3052 arp_standard.debug('EXCEPTION OTHERS get_latest_amount_applied:'||SQLERRM);
3053 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
3054 FND_MESSAGE.SET_TOKEN( 'TEXT', 'arp_process_det_pkg.get_latest_amount_applied-'||SQLERRM );
3055 FND_MSG_PUB.ADD;
3056 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3057 p_count => x_msg_count,
3058 p_data => x_msg_data);
3059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060 END get_latest_amount_applied;
3061
3062
3063
3064 FUNCTION base_for_proration
3065 (p_customer_trx_id IN NUMBER,
3066 p_gt_id IN NUMBER,
3067 p_line_type IN VARCHAR2,
3068 p_activity IN VARCHAR2)
3069 RETURN NUMBER
3070 IS
3071 CURSOR cu_base IS
3072 SELECT SUM(DECODE(p_activity,
3073 'APP' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
3074 'TAX' , NVL(tax_amount,0) ,0),
3075 'ADJ' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
3076 'TAX' , NVL(tax_amount,0) ,0),
3077 'ED' ,DECODE(p_line_type,'LINE', NVL(ed_line_amount,0),
3078 'TAX' , NVL(ed_tax_amount,0) ,0),
3079 'UNED' ,DECODE(p_line_type,'LINE', NVL(uned_line_amount,0),
3080 'TAX' , NVL(uned_tax_amount,0) ,0),0))
3081 FROM ar_line_dist_interface_gt
3082 WHERE customer_trx_id = p_customer_trx_id
3083 AND gt_id = p_gt_id;
3084 l_res NUMBER;
3085 BEGIN
3086 OPEN cu_base;
3087 FETCH cu_base INTO l_res;
3088 IF cu_base%NOTFOUND THEN
3089 l_res := 0;
3090 END IF;
3091 CLOSE cu_base;
3092 RETURN l_res;
3093 END;
3094
3095 FUNCTION element_for_proration
3096 (p_customer_trx_id IN NUMBER,
3097 p_customer_trx_line_id IN NUMBER,
3098 p_gt_id IN NUMBER,
3099 p_line_type IN VARCHAR2,
3100 p_activity IN VARCHAR2)
3101 RETURN NUMBER
3102 IS
3103 CURSOR cu_element IS
3104 SELECT DECODE(p_activity,'APP' ,DECODE(line_type,'LINE',line_amount , 'TAX',tax_amount,NULL),
3105 'ED' ,DECODE(line_type,'LINE',ed_line_amount , 'TAX',ed_tax_amount,NULL),
3106 'UNED',DECODE(line_type,'LINE',uned_line_amount, 'TAX',uned_tax_amount,NULL),NULL)
3107 FROM ar_line_dist_interface_gt
3108 WHERE customer_trx_id = p_customer_trx_id
3109 AND customer_trx_line_id = p_customer_trx_line_id
3110 AND gt_id = p_gt_id
3111 AND line_type = p_line_type;
3112 l_res NUMBER;
3113 BEGIN
3114 OPEN cu_element;
3115 FETCH cu_element INTO l_res;
3116 IF cu_element%NOTFOUND THEN
3117 l_res := 0;
3118 END IF;
3119 CLOSE cu_element;
3120 RETURN l_res;
3121 END;
3122
3123
3124 PROCEDURE verif_int_adj_line_tax
3125 (p_customer_trx IN ra_customer_trx%ROWTYPE,
3126 p_adj_rec IN ar_adjustments%ROWTYPE,
3127 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
3128 p_gt_id IN NUMBER,
3129 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3130 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3131 x_return_status IN OUT NOCOPY VARCHAR2)
3132 IS
3133 CURSOR verif_amt IS
3134 SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
3135 CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
3136 ,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
3137 FROM ar_line_dist_interface_gt
3138 WHERE gt_id = p_gt_id
3139 AND customer_trx_id = p_customer_trx.customer_trx_id
3140 AND source_table = 'ADJ';
3141
3142 l_sum_line NUMBER;
3143 l_sum_tax NUMBER;
3144 l_sum_ed_line NUMBER;
3145 l_sum_ed_tax NUMBER;
3146 l_sum_uned_line NUMBER;
3147 l_sum_uned_tax NUMBER;
3148 i NUMBER := 0;
3149 BEGIN
3150 arp_standard.debug('verif_int_adj_line_tax +');
3151 arp_standard.debug(' adjustment_id :'||p_adj_rec.adjustment_id);
3152 arp_standard.debug(' p_line_flag :'||p_line_flag);
3153 arp_standard.debug(' p_tax_flag :'||p_tax_flag);
3154
3155 IF p_gt_id IS NULL THEN
3156 x_return_status := fnd_api.g_ret_sts_error;
3157 arp_standard.debug(' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3158 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3159 FND_MESSAGE.SET_TOKEN('TEXT',' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3160 FND_MSG_PUB.ADD;
3161 ELSE
3162
3163 IF ((p_adj_rec.amount IS NULL) AND (p_adj_rec.acctd_amount IS NULL))
3164 OR
3165 ((p_adj_rec.amount = 0 ) AND (p_adj_rec.acctd_amount = 0 ))
3166 THEN
3167 x_return_status := fnd_api.g_ret_sts_error;
3168 arp_standard.debug(' Adjustment record amount and accounted amount causes no need to execute');
3169 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3170 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment record amount and accounted amount causes no need to execute');
3171 FND_MSG_PUB.ADD;
3172 ELSIF ((p_adj_rec.amount IS NULL) AND (p_adj_rec.acctd_amount IS NOT NULL))
3173 OR
3174 ((p_adj_rec.amount = 0 ) AND (p_adj_rec.acctd_amount <> 0 ))
3175 OR
3176 ((p_adj_rec.amount <> p_adj_rec.acctd_amount) AND
3177 (p_customer_trx.invoice_currency_code = p_ae_sys_rec.base_currency))
3178 THEN
3179 x_return_status := fnd_api.g_ret_sts_error;
3180 arp_standard.debug(' Adjustment record combination causes an invalid combination');
3181 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3182 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment record combination causes an invalid combination');
3183 FND_MSG_PUB.ADD;
3184 END IF;
3185
3186 OPEN verif_amt;
3187 FETCH verif_amt INTO l_sum_line ,
3188 l_sum_tax ;
3189 CLOSE verif_amt;
3190
3191 arp_standard.debug(' sum line from ar_line_dist_interface_gt, l_sum_line :'||l_sum_line);
3192 arp_standard.debug(' sum tax from ar_line_dist_interface_gt, l_sum_tax :'||l_sum_tax);
3193
3194 IF (l_sum_line = NULL) AND (l_sum_tax = NULL )
3195 THEN
3196 x_return_status := fnd_api.g_ret_sts_error;
3197 arp_standard.debug(' There is no line amount and tax amount in the interface table for this adjustment');
3198 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3199 FND_MESSAGE.SET_TOKEN('TEXT',' There is no line amount and tax amount in the interface table for adjustment -'||
3200 ' adjustment_id : ' || p_adj_rec.adjustment_id);
3201 END IF;
3202
3203
3204 IF (p_line_flag = 'Y') AND (l_sum_line <> p_adj_rec.line_adjusted) THEN
3205 x_return_status := fnd_api.g_ret_sts_error;
3206 arp_standard.debug(' Adjustment line_adjusted <> l_sum_line from ar_line_dist_interface_gt');
3207 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3208 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment line_adjusted <> l_sum_line from ar_line_dist_interface_gt');
3209 FND_MSG_PUB.ADD;
3210 END IF;
3211
3212 IF (p_tax_flag = 'Y') AND (l_sum_tax <> p_adj_rec.tax_adjusted) THEN
3213 x_return_status := fnd_api.g_ret_sts_error;
3214 arp_standard.debug(' Adjustment tax_adjusted <> l_sum_tax from ar_line_dist_interface_gt');
3215 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3216 FND_MESSAGE.SET_TOKEN('TEXT',' Adjustment tax_adjusted <> l_sum_tax from ar_line_dist_interface_gt');
3217 FND_MSG_PUB.ADD;
3218 END IF;
3219
3220 END IF;
3221 arp_standard.debug('verif_int_adj_line_tax -');
3222 END;
3223
3224
3225 PROCEDURE verif_int_app_line_tax
3226 (p_customer_trx IN ra_customer_trx%ROWTYPE,
3227 p_app_rec IN ar_receivable_applications%ROWTYPE,
3228 p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
3229 p_gt_id IN NUMBER,
3230 p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3231 p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
3232 p_ed_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
3233 p_ed_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
3234 p_uned_line_flag IN VARCHAR2 DEFAULT 'NORMAL',
3235 p_uned_tax_flag IN VARCHAR2 DEFAULT 'NORMAL',
3236 x_return_status IN OUT NOCOPY VARCHAR2)
3237 IS
3238 CURSOR verif_amt IS
3239 SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
3240 CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
3241 ,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
3242 ,CASE WHEN p_ed_line_flag = 'INTERFACE' THEN SUM(NVL(ed_line_amount,0)) ELSE NULL END
3243 ,CASE WHEN p_ed_tax_flag = 'INTERFACE' THEN SUM(NVL(ed_tax_amount,0)) ELSE NULL END
3244 ,CASE WHEN p_uned_line_flag = 'INTERFACE' THEN SUM(NVL(uned_line_amount,0)) ELSE NULL END
3245 ,CASE WHEN p_uned_tax_flag = 'INTERFACE' THEN SUM(NVL(uned_tax_amount,0)) ELSE NULL END
3246 FROM ar_line_dist_interface_gt
3247 WHERE gt_id = p_gt_id
3248 AND customer_trx_id = p_customer_trx.customer_trx_id
3249 AND source_table = 'RA';
3250
3251 l_sum_line NUMBER;
3252 l_sum_tax NUMBER;
3253 l_sum_ed_line NUMBER;
3254 l_sum_ed_tax NUMBER;
3255 l_sum_uned_line NUMBER;
3256 l_sum_uned_tax NUMBER;
3257 i NUMBER := 0;
3258 BEGIN
3259 arp_standard.debug('verif_int_app_line_tax +');
3260 arp_standard.debug(' receivable_application_id :'||p_app_rec.receivable_application_id);
3261 arp_standard.debug(' p_app_rec.amount_applied :'||p_app_rec.amount_applied);
3262 arp_standard.debug(' p_app_rec.acctd_amount_applied_to :'||p_app_rec.acctd_amount_applied_to);
3263 arp_standard.debug(' p_customer_trx.invoice_currency_code:'||p_customer_trx.invoice_currency_code);
3264 arp_standard.debug(' p_ae_sys_rec.base_currency :'||p_ae_sys_rec.base_currency);
3265 arp_standard.debug(' p_line_flag :'||p_line_flag);
3266 arp_standard.debug(' p_tax_flag :'||p_tax_flag);
3267 arp_standard.debug(' p_ed_line_flag :'||p_ed_line_flag);
3268 arp_standard.debug(' p_ed_tax_flag :'||p_ed_tax_flag);
3269 arp_standard.debug(' p_uned_line_flag :'||p_uned_line_flag);
3270 arp_standard.debug(' p_uned_tax_flag :'||p_uned_tax_flag);
3271
3272 IF p_gt_id IS NULL THEN
3273 x_return_status := fnd_api.g_ret_sts_error;
3274 arp_standard.debug(' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3275 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3276 FND_MESSAGE.SET_TOKEN('TEXT',' p_gt_id IS NULL, please excecute arp_det_dist_pkg.get_gt_sequence');
3277 FND_MSG_PUB.ADD;
3278 ELSE
3279
3280 IF ((p_app_rec.amount_applied IS NULL) AND (p_app_rec.acctd_amount_applied_to IS NULL))
3281 OR
3282 ((p_app_rec.amount_applied = 0 ) AND (p_app_rec.acctd_amount_applied_to = 0 ))
3283 THEN
3284 x_return_status := fnd_api.g_ret_sts_error;
3285 arp_standard.debug(' Application record amount and accounted amount to causes no need to execute'||
3286 ' as no amount in amount_applied and acctd_amount_applied_to bucket');
3287 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3288 FND_MESSAGE.SET_TOKEN('TEXT',' Application record amount and accounted amount to causes no need to execute'||
3289 ' as no amount in amount_applied and acctd_amount_applied_to bucket');
3290 FND_MSG_PUB.ADD;
3291 ELSIF ((p_app_rec.amount_applied IS NULL) AND (p_app_rec.acctd_amount_applied_to IS NOT NULL))
3292 OR
3293 ((p_app_rec.amount_applied = 0 ) AND (p_app_rec.acctd_amount_applied_to <> 0 ))
3294 OR
3295 ((p_app_rec.amount_applied <> p_app_rec.acctd_amount_applied_to) AND
3296 (p_customer_trx.invoice_currency_code = p_ae_sys_rec.base_currency))
3297 THEN
3298 x_return_status := fnd_api.g_ret_sts_error;
3299 arp_standard.debug(' Application record combination causes an invalid combination on amount bucket');
3300 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3301 FND_MESSAGE.SET_TOKEN('TEXT',' Application record combination causes an invalid combination');
3302 FND_MSG_PUB.ADD;
3303 END IF;
3304
3305 OPEN verif_amt;
3306 FETCH verif_amt INTO l_sum_line ,
3307 l_sum_tax ,
3308 l_sum_ed_line ,
3309 l_sum_ed_tax ,
3310 l_sum_uned_line ,
3311 l_sum_uned_tax ;
3312 CLOSE verif_amt;
3313
3314 arp_standard.debug('TABLE ar_line_dist_interface_gt ');
3315 arp_standard.debug(' l_sum_line :'||l_sum_line);
3316 arp_standard.debug(' l_sum_tax :'||l_sum_tax);
3317 arp_standard.debug(' l_sum_ed_line :'||l_sum_ed_line);
3318 arp_standard.debug(' l_sum_ed_tax :'||l_sum_ed_tax);
3319 arp_standard.debug(' l_sum_uned_line :'||l_sum_uned_line);
3320 arp_standard.debug(' l_sum_uned_tax :'||l_sum_uned_tax);
3321
3322 arp_standard.debug('RECORD p_app_rec ');
3323 arp_standard.debug(' p_app_rec.line_applied :'||p_app_rec.line_applied);
3324 arp_standard.debug(' p_app_rec.tax_applied :'||p_app_rec.tax_applied);
3325 arp_standard.debug(' p_app_rec.LINE_EDISCOUNTED :'||p_app_rec.LINE_EDISCOUNTED);
3326 arp_standard.debug(' p_app_rec.TAX_EDISCOUNTED :'||p_app_rec.TAX_EDISCOUNTED);
3327 arp_standard.debug(' p_app_rec.LINE_UEDISCOUNTED :'||p_app_rec.LINE_UEDISCOUNTED);
3328 arp_standard.debug(' p_app_rec.TAX_UEDISCOUNTED :'||p_app_rec.TAX_UEDISCOUNTED);
3329
3330
3331 IF (l_sum_line = NULL) AND (l_sum_tax = NULL ) AND
3332 (l_sum_ed_line = NULL) AND (l_sum_ed_tax = NULL ) AND
3333 (l_sum_uned_line = NULL) AND (l_sum_uned_tax = NULL )
3334 THEN
3335 x_return_status := fnd_api.g_ret_sts_error;
3336 arp_standard.debug(' There is no line amount, tax amount for app, edisc and unedisc in the interface table for this application');
3337 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3338 FND_MESSAGE.SET_TOKEN('TEXT',' There is no line amount, tax amount for app, edisc and unedisc in the interface table for this application'||
3339 ' receivable_application_id : ' || p_app_rec.receivable_application_id);
3340 END IF;
3341
3342
3343 IF (p_line_flag = 'Y') AND (l_sum_line <> p_app_rec.line_applied) THEN
3344 x_return_status := fnd_api.g_ret_sts_error;
3345 arp_standard.debug(' Application line_applied ('||p_app_rec.line_applied||')<> l_sum_line('||l_sum_line||') from ar_line_dist_interface_gt');
3346 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3347 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');
3348 FND_MSG_PUB.ADD;
3349 END IF;
3350
3351 IF (p_tax_flag = 'Y') AND (l_sum_tax <> p_app_rec.tax_applied) THEN
3352 x_return_status := fnd_api.g_ret_sts_error;
3353 arp_standard.debug(' Adjustment tax_adjusted ('||p_app_rec.tax_applied||')<> l_sum_tax('||l_sum_tax||') from ar_line_dist_interface_gt');
3354 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3355 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');
3356 FND_MSG_PUB.ADD;
3357 END IF;
3358
3359 IF (p_ed_line_flag = 'Y') AND (l_sum_ed_line <> p_app_rec.LINE_EDISCOUNTED) THEN
3360 x_return_status := fnd_api.g_ret_sts_error;
3361 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');
3362 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3363 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');
3364 FND_MSG_PUB.ADD;
3365 END IF;
3366
3367 IF (p_ed_tax_flag = 'Y') AND (l_sum_ed_tax <> p_app_rec.TAX_EDISCOUNTED) THEN
3368 x_return_status := fnd_api.g_ret_sts_error;
3369 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');
3370 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3371 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');
3372 FND_MSG_PUB.ADD;
3373 END IF;
3374
3375 IF (p_uned_line_flag = 'Y') AND (l_sum_uned_line <> p_app_rec.LINE_uEDISCOUNTED) THEN
3376 x_return_status := fnd_api.g_ret_sts_error;
3377 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');
3378 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3379 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');
3380 FND_MSG_PUB.ADD;
3381 END IF;
3382
3383 IF (p_uned_tax_flag = 'Y') AND (l_sum_uned_tax <> p_app_rec.TAX_uEDISCOUNTED) THEN
3384 x_return_status := fnd_api.g_ret_sts_error;
3385 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');
3386 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR');
3387 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');
3388 FND_MSG_PUB.ADD;
3389 END IF;
3390
3391 END IF;
3392 arp_standard.debug('verif_int_app_line_tax -');
3393 END;
3394
3395
3396
3397 PROCEDURE breakup_discounts (
3398 /*--------------------------------------------------------------------------+
3399 private procedure used for LLCA bucket-wise breakup RM Jul 19, 2005
3400 +---------------------------------------------------------------------------*/
3401 --in variables
3402 lin_discount_in in NUMBER,
3403 tax_discount_in in NUMBER,
3404 frt_discount_in in NUMBER,
3405 tot_earned_discount_in in NUMBER,
3406 tot_unearned_discount_in in NUMBER,
3407 --out variables
3408 ed_lin_out out nocopy number,
3409 ued_lin_out out nocopy number,
3410 ed_tax_out out nocopy number,
3411 ued_tax_out out nocopy number,
3412 ed_frt_out out nocopy number,
3413 ued_frt_out out nocopy number
3414 )
3415 IS
3416 l_denom number;
3417 begin
3418
3419 arp_standard.debug ('llc brk dsc lin in= ' || lin_discount_in);
3420 arp_standard.debug ('llc brk dsc tax in= ' || tax_discount_in);
3421 arp_standard.debug ('llc brk dsc frt in= ' || frt_discount_in);
3422 arp_standard.debug ('llc brk dsc ed in= ' || tot_earned_discount_in);
3423 arp_standard.debug ('llc brk dsc ued in= ' || tot_unearned_discount_in);
3424
3425 l_denom := tot_earned_discount_in + tot_unearned_discount_in;
3426
3427 if l_denom <> 0 then
3428 ed_lin_out := (lin_discount_in / l_denom) * tot_earned_discount_in;
3429 ued_lin_out := lin_discount_in - ed_lin_out;
3430
3431 ed_tax_out := (tax_discount_in / l_denom) * tot_earned_discount_in;
3432 ued_tax_out := tax_discount_in - ed_tax_out;
3433
3434 ed_frt_out := (frt_discount_in / l_denom) * tot_earned_discount_in;
3435 ued_frt_out := frt_discount_in - ed_frt_out;
3436
3437 end if;
3438 arp_standard.debug ('llc ed_lin_out '||ed_lin_out );
3439 arp_standard.debug ('llc ued_lin_out '||ued_lin_out );
3440 arp_standard.debug ('llc ed_tax_out '||ed_tax_out );
3441 arp_standard.debug ('llc ued_tax_out '||ued_tax_out );
3442 arp_standard.debug ('llc ed_frt_out '||ed_frt_out );
3443 arp_standard.debug ('llc ued_frt_out '||ued_frt_out );
3444
3445 END breakup_discounts;
3446
3447
3448
3449
3450 END arp_process_det_pkg;