[Home] [Help]
PACKAGE BODY: APPS.FV_DC_ASSIGN_FINANCE_CHRG
Source
1 package body fv_dc_assign_finance_chrg as
2 /* $Header: FVDCAAFB.pls 120.47.12020000.3 2013/02/13 14:25:23 snama ship $ */
3
4 /*****************************************************************************/
5 /***** Variable Declaration For All Processes ****/
6 /*****************************************************************************/
7 g_module_name VARCHAR2(100) ;
8 g_FAILURE NUMBER;
9 g_SUCCESS NUMBER;
10 g_WARNING NUMBER;
11 g_enter VARCHAR2(10);
12 g_exit VARCHAR2(10);
13 g_request_id NUMBER;
14 g_user_id NUMBER;
15 g_login_id NUMBER;
16 g_org_id NUMBER;
17 g_set_of_books_id NUMBER;
18 g_ledger_name VARCHAR2(30);
19 g_CURRENT_LOG_LEVEL NUMBER;
20 g_hi_date DATE;
21 g_lo_date DATE;
22
23 TYPE out_rec IS RECORD
24 (
25 invoice_id ra_customer_trx_all.customer_trx_id%TYPE,
26 payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE,
27 charge_type fv_finance_charge_controls.charge_type%TYPE,
28 invoice_number ra_customer_trx_all.trx_number%TYPE,
29 amount_due_remaining ar_payment_schedules.amount_due_remaining%TYPE,
30 amount_due_original ar_payment_schedules.amount_due_original%TYPE,
31 due_date ar_payment_schedules.due_date%TYPE,
32 amount_based VARCHAR2(1),
33 amount_or_rate NUMBER,
34 first_accrual NUMBER,
35 accrual_interval NUMBER,
36 grace_period NUMBER,
37 last_accrual_date DATE,
38 base_date_type VARCHAR2(30),
39 comments VARCHAR2(1024),
40 accrual_date DATE,
41 first_late_date DATE,
42 finance_charges NUMBER,
43 number_of_periods NUMBER
44 );
45
46 TYPE out_rec_tab IS TABLE OF out_rec INDEX BY BINARY_INTEGER;
47
48 g_out_rec_tab out_rec_tab;
49 g_out_rec_count NUMBER;
50
51 /****************************************************************************/
52
53
54 PROCEDURE log
55 (
56 p_location IN VARCHAR2,
57 p_message IN VARCHAR2
58 ) IS
59 BEGIN
60 fnd_file.put_line (fnd_file.log, p_location||':'||p_message);
61 END;
62
63 PROCEDURE output
64 (
65 p_message IN VARCHAR2
66 ) IS
67 BEGIN
68 fnd_file.put_line (fnd_file.output,p_message);
69 END;
70
71 PROCEDURE debug
72 (
73 p_module IN VARCHAR2,
74 p_message IN VARCHAR2
75 ) IS
76 BEGIN
77 fv_utility.debug_mesg(fnd_log.level_statement, p_module,p_message);
78 END;
79
80 PROCEDURE initialize_global_variables
81 IS
82 BEGIN
83 g_module_name := 'fv.plsql.fv_dc_assign_finance_chrg.';
84 g_FAILURE := -1;
85 g_SUCCESS := 0;
86 g_WARNING := -2;
87 g_request_id := fnd_global.conc_request_id;
88 g_user_id := fnd_global.user_id;
89 g_login_id := fnd_global.login_id;
90 g_org_id := mo_global.get_current_org_id;
91 mo_utils.get_ledger_info(g_org_id, g_set_of_books_id, g_ledger_name);
92 g_enter := 'ENTER';
93 g_exit := 'EXIT';
94 g_CURRENT_LOG_LEVEL := fnd_log.g_current_runtime_level;
95 g_out_rec_count := 0;
96 g_lo_date := TO_DATE('1-1-1900', 'DD-MM-YYYY');
97 g_hi_date := TO_DATE('31-12-4000', 'DD-MM-YYYY');
98 END;
99
100 PROCEDURE initialize_program_variables
101 (
102 p_gl_date IN DATE,
103 p_term_id OUT NOCOPY NUMBER,
104 p_errbuf OUT NOCOPY VARCHAR2,
105 p_retcode OUT NOCOPY NUMBER
106 )
107 IS
108 l_module_name VARCHAR2(200);
109 l_location VARCHAR2(200);
110 l_ar_period_count NUMBER;
111 BEGIN
112 l_module_name := g_module_name || 'initialize_program_variables';
113 p_retcode := g_SUCCESS;
114 p_errbuf := NULL;
115
116 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
117 debug(l_module_name,g_enter);
118 END IF;
119
120 BEGIN
121 SELECT term_id
122 INTO p_term_id
123 FROM ra_terms a
124 WHERE a.name = 'IMMEDIATE';
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 p_retcode := g_FAILURE;
128 p_errbuf := SQLERRM;
129 l_location := l_module_name||'.select_ra_terms';
130 log(l_location, 'IMMEDIATE terms not defined');
131 log(l_location,l_location) ;
132 log(l_location,p_errbuf) ;
133 WHEN OTHERS THEN
134 p_retcode := g_FAILURE;
135 p_errbuf := SQLERRM;
136 l_location := l_module_name||'.select_ra_terms';
137 log(l_location,l_location) ;
138 log(l_location,p_errbuf) ;
139 END;
140
141 IF (p_retcode = g_SUCCESS) THEN
142 BEGIN
143 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
144 debug(l_module_name,'SELECT gl_period_statuses');
145 END IF;
146 SELECT 1
147 INTO l_ar_period_count
148 FROM gl_period_statuses
149 WHERE closing_status ='O'
150 AND set_of_books_id = g_set_of_books_id
151 AND application_id = 222
152 AND p_gl_date between start_date and end_date;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 l_location := l_module_name||'.select_gl_period_statuses1';
156 log (l_location, 'GL Date should be in an open period. ');
157 log (l_location, 'Please Enter a GL Date which is in an open period ') ;
158 p_retcode := g_FAILURE;
159 WHEN OTHERS THEN
160 p_retcode := g_FAILURE;
161 p_errbuf := SQLERRM;
162 l_location := l_module_name||'.select_gl_period_statuses2';
163 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
164 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
165 END;
166 END IF;
167
168
169 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
170 debug(l_module_name,g_exit);
171 END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 p_retcode := g_FAILURE;
176 p_errbuf := SQLERRM;
177 l_location := l_module_name||'.final_exception';
178 log(l_location,l_location) ;
179 log(l_location, p_errbuf);
180 END;
181
182 PROCEDURE write_output
183 IS
184 l_module_name VARCHAR2(200);
185 l_location VARCHAR2(200);
186 l_HTML_SPACE VARCHAR2(100) := '&'||'nbsp;';
187
188 PROCEDURE th
189 (
190 p_data IN VARCHAR2
191 )
192 IS
193 BEGIN
194 output ('<TH class=''OraTableColumnHeader'' style=''border-left:1px solid #c9cbd3''>');
195 output (p_data);
196 output ('</TH>');
197 END;
198
199 PROCEDURE td
200 (
201 p_data IN VARCHAR2
202 )
203 IS
204 BEGIN
205 output ('<TD class=''OraTableCellText'' style=''border:1px solid #c9cbd3''>');
206 output (NVL(p_data, l_HTML_SPACE));
207 output ('</TD>');
208 END;
209
210
211 BEGIN
212
213 l_module_name := g_module_name || 'write_output';
214 output ('<HTML>');
215 output ('<STYLE TYPE="text/css">');
216 output ('<!--');
217 output ('.OraTable {background-color:#999966}');
218 output ('.OraTableColumnHeader {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-size:9pt;font-weight:bold;text-align:left;background-color:#cfe0f1;color:#3c3c3c;vertical-align:bottom}');
219 output ('.OraTableCellText {font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;font-size:9pt;background-color:#f2f2f5;color:#3c3c3c;vertical-align:baseline}');
220 output ('-->');
221 output ('</STYLE>');
222 output ('<TABLE class=''OraTable'' style=''border-collapse:collapse'' width=''100%'' cellpadding=1 cellspacing=0 border=0 >');
223 output ('<TR>');
224 th ('Invoice<BR>Id');
225 th ('Invoice<BR> Schedule Id');
226 th ('Invoice<BR>Number');
227 th ('Charge<BR>Type');
228 th ('Amount Due<BR>Remaining');
229 th ('Amount Due<BR>Original');
230 th ('Due Date');
231 th ('Amount Based');
232 th ('Amount/Rate');
233 th ('First<BR>Accrual');
234 th ('Accrual<BR>Interval');
235 th ('Grace<BR>Period');
236 th ('Base Date<BR>Type');
237 th ('Last Accrual Date');
238 th ('Accrual Date');
239 th ('Finance Charges');
240 th ('Number of Periods');
241 th ('Comments');
242 output ('</TH>');
243 output ('</TR>');
244 FOR i IN 1..g_out_rec_tab.COUNT LOOP
245 output ('<TR>');
246 td (TO_CHAR(g_out_rec_tab(i).invoice_id));
247 td (TO_CHAR(g_out_rec_tab(i).payment_schedule_id));
248 td (g_out_rec_tab(i).invoice_number);
249 td (g_out_rec_tab(i).charge_type);
250 td (TO_CHAR(g_out_rec_tab(i).amount_due_remaining));
251 td (TO_CHAR(g_out_rec_tab(i).amount_due_original));
252 td (TO_CHAR(g_out_rec_tab(i).due_date));
253 td (g_out_rec_tab(i).amount_based);
254 td (TO_CHAR(g_out_rec_tab(i).amount_or_rate));
255 td (TO_CHAR(g_out_rec_tab(i).first_accrual));
256 td (TO_CHAR(g_out_rec_tab(i).accrual_interval));
257 td (TO_CHAR(g_out_rec_tab(i).grace_period));
258 td (g_out_rec_tab(i).base_date_type);
259 td (TO_CHAR(g_out_rec_tab(i).last_accrual_date));
260 td (TO_CHAR(g_out_rec_tab(i).accrual_date));
261 td (TO_CHAR(g_out_rec_tab(i).finance_charges));
262 td (TO_CHAR(g_out_rec_tab(i).number_of_periods));
263 td (g_out_rec_tab(i).comments);
264 output ('</TR>');
265 END LOOP;
266 output ('</TABLE>');
267 output ('</HTML>');
268 EXCEPTION
269 WHEN OTHERS THEN
270 log(l_location,l_location) ;
271 l_location := l_module_name||'.final_exception';
272 log(l_location,SQLERRM) ;
273 log(l_location,l_location) ;
274 END;
275
276 PROCEDURE accrue_charges
277 (
278 p_accrue_as_of_date IN DATE,
279 p_last_date IN DATE,
280 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
281 p_amount_due_remaining IN OUT NOCOPY ar_payment_schedules.amount_due_remaining%TYPE,
282 p_rate_amount IN NUMBER,
283 p_interval IN fv_finance_charge_controls.accrual_interval%TYPE,
284 p_charges OUT NOCOPY NUMBER,
285 p_errbuf OUT NOCOPY VARCHAR2,
286 p_retcode OUT NOCOPY NUMBER
287 )
288 IS
289 l_module_name VARCHAR2(200);
290 l_location VARCHAR2(200);
291 l_accrued_amount NUMBER;
292 l_amt_due NUMBER;
293 l_ct_id NUMBER;
294 BEGIN
295 l_module_name := g_module_name || 'accrue_charges';
296 p_retcode := g_SUCCESS;
297 p_errbuf := NULL;
298
299 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
300 debug(l_module_name,g_enter);
301 END IF;
302
303 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
304 debug(l_module_name,'p_last_date='||p_last_date);
305 debug(l_module_name,'p_customer_trx_id='||p_customer_trx_id);
306 debug(l_module_name,'p_rate_amount='||p_rate_amount);
307 debug(l_module_name,'p_interval='||p_interval);
308 debug(l_module_name,'p_amount_due_remaining='||p_amount_due_remaining);
309 END IF;
310
311 IF (p_interval = 0) THEN
312 -- sum the total amount_due_remaining for the invoice. We want to
313 -- apply the charge on the total amount of the invoice. This is really for
314 -- invoices with split payment terms. Because main query only is looking
315 -- at the porition of the invoice that is late. But when calculating
316 -- for one time charges we want the total amount of the invoice not just
317 -- the amount for the schedule that is late.
318
319 l_ct_id := p_customer_trx_id;
320 BEGIN
321 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
322 debug(l_module_name,'l_ct_id='||l_ct_id);
323 END IF;
324
325 SELECT SUM(amount_due_remaining)
326 INTO l_amt_due
327 FROM ar_payment_schedules
328 WHERE customer_trx_id = l_ct_id
329 AND class = 'INV';
330
331 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
332 debug(l_module_name,'l_amt_due='||l_amt_due);
333 END IF;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 p_retcode := g_FAILURE;
338 p_errbuf := SQLERRM;
339 l_location := l_module_name||'.select_ar_payment_schedules';
340 log(l_location,l_location) ;
341 log(l_location,p_errbuf) ;
342 END;
343
344 ELSE
345 l_amt_due := p_amount_due_remaining;
346 END IF;
347
348 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
349 debug(l_module_name,'l_amt_due(1)='||l_amt_due);
350 END IF;
351
352 IF (p_retcode = g_SUCCESS) THEN
353 l_accrued_amount := round(l_amt_due * p_rate_amount *
354 ((trunc(p_accrue_as_of_date) - trunc(p_last_date))/360),2);
355 g_out_rec_tab(g_out_rec_count).comments := 'Accrued Amount = '||l_amt_due||'*'||p_rate_amount||'*('||(trunc(p_accrue_as_of_date) - trunc(p_last_date))||'/360)'||'='||l_accrued_amount;
356 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
357 debug(l_module_name,'l_accrued_amount='||l_accrued_amount);
358 END IF;
359 END IF;
360
361 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
362 debug(l_module_name,g_exit);
363 END IF;
364 p_charges := l_accrued_amount;
365 EXCEPTION
366 WHEN OTHERS THEN
367 p_retcode := g_FAILURE;
368 p_errbuf := SQLERRM;
369 l_location := l_module_name||'.final_exception';
370 log(l_location,l_location) ;
371 log(l_location, p_errbuf);
372 end accrue_charges;
373
374 ------------------------------------------------------------------------------
375
376 PROCEDURE missed_intervals
377 (
378 p_accrue_as_of_date IN DATE,
379 p_interval IN fv_finance_charge_controls.accrual_interval%type,
380 p_last_accrual_date IN DATE,
381 p_first_late_day IN OUT NOCOPY DATE,
382 p_number_of_periods OUT NOCOPY NUMBER,
383 p_errbuf OUT NOCOPY VARCHAR2,
384 p_retcode OUT NOCOPY NUMBER
385 )
386 IS
387 l_module_name VARCHAR2(200);
388 l_location VARCHAR2(200);
389 BEGIN
390 l_module_name := g_module_name || 'missed_intervals';
391 p_retcode := g_SUCCESS;
392 p_errbuf := NULL;
393 p_number_of_periods := 1; -- starting at one takes in to consideration we must
394
395 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
396 debug(l_module_name,'Inside '||l_module_name);
397 debug(l_module_name,'p_interval='||p_interval);
398 debug(l_module_name,'p_last_accrual_date='||p_last_accrual_date);
399 debug(l_module_name,'p_first_late_day='||p_first_late_day);
400 END IF;
401
402 IF (p_last_accrual_date IS NOT NULL) THEN
403 p_first_late_day := p_last_accrual_date;
404 END IF;
405
406 IF p_interval <> 0 THEN
407 IF TRUNC(p_accrue_as_of_date) >= (trunc(p_first_late_day) + p_interval) THEN
408 p_number_of_periods := trunc((trunc(p_accrue_as_of_date) - trunc(p_first_late_day))/p_interval);
409 END IF;
410 END IF;
411
412 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
413 l_location := '6';
414 debug(l_module_name,'p_number_of_periods='||p_number_of_periods);
415 END IF;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 p_retcode := g_FAILURE;
420 p_errbuf := SQLERRM;
421 debug(l_module_name,'l_location='||l_location);
422 l_location := l_module_name||'.final_exception';
423 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
424 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
425 END missed_intervals;
426
427 PROCEDURE submit_report
428 (
429 p_errbuf OUT NOCOPY VARCHAR2,
430 p_retcode OUT NOCOPY NUMBER
431 )
432 IS
433 l_module_name VARCHAR2(200);
434 l_location VARCHAR2(200);
435 l_conc_req_id NUMBER;
436 BEGIN
437 l_module_name := g_module_name || 'submit_report';
438 p_retcode := g_SUCCESS;
439 p_errbuf := null;
440 --Added for bug 9701855
441 fnd_request.set_org_id(g_org_id); -- PSKI MOAC Changes
442 l_conc_req_id := fnd_request.submit_request
443 (
444 application => 'FV',
445 program => 'FVDCACCD',
446 description => NULL,
447 start_time => NULL,
448 sub_request => FALSE,
449 argument1 => g_request_id
450 );
451 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
452 debug (l_module_name,'Submitting Detail report '||l_conc_req_id);
453 END IF;
454
455 IF (l_conc_req_id = 0) THEN
456 p_retcode := g_FAILURE;
457 p_errbuf := 'Error in Submitting Accrue Finance charge Detail Report ';
458 log (l_location,p_errbuf);
459 END IF;
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 p_retcode := g_FAILURE;
464 p_errbuf := SQLERRM;
465 l_location := l_module_name||'.final_exception';
466 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
467 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
468 END submit_report;
469
470 PROCEDURE interpret_dm_error
471 (
472 p_trx_number IN VARCHAR2,
473 p_return_status IN VARCHAR2,
474 p_message_count IN NUMBER,
475 p_message_data IN VARCHAR2,
476 p_errbuf OUT NOCOPY VARCHAR2,
477 p_retcode OUT NOCOPY NUMBER
478 )
479 IS
480 l_module_name VARCHAR2(200);
481 l_location VARCHAR2(200);
482 l_header_printed BOOLEAN := FALSE;
483 l_message_data VARCHAR2(1024);
484 BEGIN
485 l_module_name := g_module_name || 'interpret_dm_error';
486 p_retcode := g_SUCCESS;
487 p_errbuf := null;
488
489 IF (NVL(p_return_status, 'S') <> 'S') THEN
490 IF NOT l_header_printed THEN
491 log ('', '*********** ERRORS FOR TRX NUMBER '||p_trx_number||' ***************');
492 l_header_printed := TRUE;
493 END IF;
494 p_retcode := g_FAILURE;
495
496 IF (p_message_count = 1) THEN
497 ----------------------------------------------------------------------
498 -- Message Count is 1, hence the error message is in x_msg_data --
499 ----------------------------------------------------------------------
500 p_errbuf := p_message_data;
501 log ('',p_message_data);
502 ELSIF (p_message_count > 1) THEN
503 ----------------------------------------------------------------------
504 -- Message Count is > 1, hence loop for x_msg_count times and call --
505 -- fnd_msg_pub.get to get the error messages --
506 ----------------------------------------------------------------------
507 FOR l_counter IN 1..p_message_count LOOP
508 l_message_data := fnd_msg_pub.get (p_encoded => 'F');
509 log ('',l_message_data);
510 END LOOP;
511 p_errbuf := 'Too many errors.';
512 END IF;
513 END IF;
514
515 FOR error_rec IN (SELECT *
516 FROM ar_trx_errors_gt) LOOP
517 IF NOT l_header_printed THEN
518 log ('', '*********** ERRORS FOR TRX NUMBER '||p_trx_number||' ***************');
519 l_header_printed := TRUE;
520 END IF;
521
522 log ('', error_rec.error_message||':'||error_rec.invalid_value);
523 p_retcode := g_FAILURE;
524 p_errbuf := 'Too many errors.';
525 END LOOP;
526 IF l_header_printed THEN
527 log ('', '*******************************************************');
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 p_retcode := g_FAILURE;
533 p_errbuf := SQLERRM;
534 l_location := l_module_name||'.final_exception';
535 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
536 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
537 END interpret_dm_error;
538
539 PROCEDURE check_and_correct_rounding
540 (
541 p_amount IN NUMBER,
542 p_trx_header_tbl IN OUT NOCOPY ar_invoice_api_pub.trx_header_tbl_type,
543 p_trx_lines_tbl IN OUT NOCOPY ar_invoice_api_pub.trx_line_tbl_type,
544 p_trx_dist_tbl IN OUT NOCOPY ar_invoice_api_pub.trx_dist_tbl_type,
545 p_errbuf OUT NOCOPY VARCHAR2,
546 p_retcode OUT NOCOPY NUMBER
547 )
548 IS
549 l_module_name VARCHAR2(200);
550 l_location VARCHAR2(200);
551 l_line_id NUMBER;
552 l_dist_id NUMBER;
553 l_last_dist_id NUMBER;
554 l_total_line_amount NUMBER := 0;
555 l_total_dist_amount NUMBER := 0;
556 l_total_percent NUMBER := 0;
557 BEGIN
558 l_module_name := g_module_name || 'check_and_correct_rounding';
559 p_retcode := g_SUCCESS;
560 p_errbuf := null;
561
562 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
563 debug(l_module_name,g_enter);
564 END IF;
565
566 /* First correct the line amount variations if any */
567 l_total_line_amount := 0;
568 l_line_id := 0;
569 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
570 debug (l_module_name,'Checking if total of line changes are correct');
571 END IF;
572 FOR line IN 1..p_trx_lines_tbl.COUNT LOOP
573 l_line_id := l_line_id + 1;
574 l_total_line_amount := l_total_line_amount + p_trx_lines_tbl(l_line_id).amount;
575 END LOOP;
576
577 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
578 debug (l_module_name,'l_total_line_amount='||l_total_line_amount);
579 debug (l_module_name,'p_amount='||p_amount);
580 debug (l_module_name,'l_line_id='||l_line_id);
581 END IF;
582
583 IF (l_total_line_amount <> p_amount) THEN
584 p_trx_lines_tbl(l_line_id).amount := p_trx_lines_tbl(l_line_id).amount + (p_amount-l_total_line_amount);
585 p_trx_lines_tbl(l_line_id).unit_selling_price := p_trx_lines_tbl(l_line_id).amount;
586 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
587 debug (l_module_name,'Changed the line amount');
588 debug (l_module_name,'amount='||p_trx_lines_tbl(l_line_id).amount);
589 debug (l_module_name,'unit_selling_price='||p_trx_lines_tbl(l_line_id).unit_selling_price);
590 END IF;
591 END IF;
592
593 /* Now correct the dist amount variations if any */
594 l_line_id := 0;
595 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
596 debug (l_module_name,'Checking if total of dist changes are correct');
597 END IF;
598 FOR line IN 1..p_trx_lines_tbl.COUNT LOOP
599 l_line_id := l_line_id + 1;
600 l_dist_id := 0;
601 l_total_dist_amount := 0;
602 l_last_dist_id := 0;
603 l_total_percent := 0;
604 FOR dist IN 1..p_trx_dist_tbl.COUNT LOOP
605 l_dist_id := l_dist_id + 1;
606 IF (p_trx_dist_tbl(l_dist_id).trx_line_id = l_line_id) THEN
607 l_total_dist_amount := l_total_dist_amount + p_trx_dist_tbl(l_dist_id).amount;
608 p_trx_dist_tbl(l_dist_id).percent := ROUND((p_trx_dist_tbl(l_dist_id).amount/p_trx_lines_tbl(l_line_id).amount)*100, 4);
609 l_total_percent := l_total_percent + p_trx_dist_tbl(l_dist_id).percent;
610 l_last_dist_id := l_dist_id;
611 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
612 debug (l_module_name,'Checking for dist amounts');
613 debug (l_module_name,'percent='||p_trx_dist_tbl(l_dist_id).percent);
614 debug (l_module_name,'Running l_total_dist_amount='||l_total_dist_amount);
615 debug (l_module_name,'Running l_total_percent='||l_total_percent);
616 END IF;
617 END IF;
618 END LOOP;
619
620 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
621 debug (l_module_name,'l_total_dist_amount='||l_total_dist_amount);
622 debug (l_module_name,'l_total_percent(1)='||l_total_percent);
623 debug (l_module_name,'p_trx_lines_tbl(l_line_id).amount='||p_trx_lines_tbl(l_line_id).amount);
624 debug (l_module_name,'l_last_dist_id='||l_last_dist_id);
625 END IF;
626
627 IF (l_total_dist_amount <> p_trx_lines_tbl(l_line_id).amount) THEN
628 l_total_percent := l_total_percent - p_trx_dist_tbl(l_last_dist_id).percent;
629 p_trx_dist_tbl(l_last_dist_id).amount := p_trx_dist_tbl(l_last_dist_id).amount + (p_trx_lines_tbl(l_line_id).amount - l_total_dist_amount);
630 p_trx_dist_tbl(l_last_dist_id).percent := ROUND((p_trx_dist_tbl(l_last_dist_id).amount/p_trx_lines_tbl(l_line_id).amount)*100, 4);
631 l_total_percent := l_total_percent + p_trx_dist_tbl(l_last_dist_id).percent;
632 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
633 debug (l_module_name,'Changed the dist amount');
634 debug (l_module_name,'amount='||p_trx_dist_tbl(l_last_dist_id).amount);
635 debug (l_module_name,'percent='||p_trx_dist_tbl(l_last_dist_id).percent);
636 END IF;
637 END IF;
638
639 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
640 debug (l_module_name,'l_total_percent(2)='||l_total_percent);
641 debug (l_module_name,'l_last_dist_id='||l_last_dist_id);
642 END IF;
643
644 IF (l_total_percent <> 100) THEN
645 p_trx_dist_tbl(l_last_dist_id).percent := p_trx_dist_tbl(l_last_dist_id).percent + (100-l_total_percent);
646 END IF;
647 END LOOP;
648 EXCEPTION
649 WHEN OTHERS THEN
650 p_retcode := g_FAILURE;
651 p_errbuf := SQLERRM;
652 l_location := l_module_name||'.final_exception';
653 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
654 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
655 END;
656
657 PROCEDURE create_dm
658 (
659 p_accrue_as_of_date IN DATE,
660 p_parent_invoice_id IN NUMBER,
661 p_trx_number IN VARCHAR2,
662 p_prorate_charge IN VARCHAR2,
663 p_invoice_date_type IN VARCHAR,
664 p_trx_date IN DATE,
665 p_due_date IN DATE,
666 p_trx_currency IN VARCHAR2,
667 p_trx_type_id IN NUMBER,
668 p_gl_date IN DATE,
669 p_bill_to_customer_id IN NUMBER,
670 p_bill_to_contact_id IN NUMBER,
671 p_bill_to_address_id IN NUMBER,
672 p_term_id IN NUMBER,
673 p_exchange_date IN ra_customer_trx.exchange_date%TYPE,
674 p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE,
675 p_exchange_rate_type IN ra_customer_trx.exchange_rate_type%TYPE,
676 p_root_invoice_id IN NUMBER,
677 p_org_id IN NUMBER,
678 p_rec_ccid IN NUMBER,
679 p_rev_ccid IN NUMBER,
680 p_legal_entity_id IN NUMBER,
681 p_amount IN NUMBER,
682 p_batch_source_id IN NUMBER,
683 p_charge_type IN VARCHAR2,
684 p_invoice_suffix IN VARCHAR2,
685 p_dm_invoice_id OUT NOCOPY NUMBER,
686 p_dm_trx_number OUT NOCOPY VARCHAR2,
687 p_dm_trx_date OUT NOCOPY DATE,
688 p_errbuf OUT NOCOPY VARCHAR2,
689 p_retcode OUT NOCOPY NUMBER
690 )
691 IS
692 l_module_name VARCHAR2(200);
693 l_location VARCHAR2(200);
694 l_api_version CONSTANT NUMBER := 1.0;
695 l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
696 l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
697 l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
698 l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
699 l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
700 l_return_status VARCHAR2(10);
701 l_message_count NUMBER;
702 l_message_data VARCHAR2(1024);
703 l_dm_trx_number ra_customer_trx_all.trx_number%TYPE;
704 l_line_counter NUMBER;
705 l_dist_counter NUMBER;
706 l_prorated_line_amount NUMBER;
707 l_line_amount_due NUMBER;
708 l_total_line_amount NUMBER;
709
710 BEGIN
711 l_module_name := g_module_name || 'create_dm';
712 p_retcode := g_SUCCESS;
713 p_errbuf := null;
714
715 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
716 debug(l_module_name,g_enter);
717 END IF;
718
719 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
720 debug (l_module_name,'p_invoice_date_type='||p_invoice_date_type);
721 debug (l_module_name,'p_trx_date='||p_trx_date);
722 debug (l_module_name,'p_due_date='||p_due_date);
723 debug (l_module_name,'p_trx_currency='||p_trx_currency);
724 debug (l_module_name,'p_gl_date='||p_gl_date);
725 debug (l_module_name,'p_bill_to_customer_id='||p_bill_to_customer_id);
726 debug (l_module_name,'p_bill_to_contact_id='||p_bill_to_contact_id);
727 debug (l_module_name,'p_bill_to_address_id='||p_bill_to_address_id);
728 debug (l_module_name,'p_term_id='||p_term_id);
729 debug (l_module_name,'p_exchange_date='||p_exchange_date);
730 debug (l_module_name,'p_exchange_rate='||p_exchange_rate);
731 debug (l_module_name,'p_exchange_rate_type='||p_exchange_rate_type);
732 debug (l_module_name,'p_org_id='||p_org_id);
733 debug (l_module_name,'p_rev_ccid='||p_rev_ccid);
734 debug (l_module_name,'p_rec_ccid='||p_rec_ccid);
735 debug (l_module_name,'p_batch_source_id='||p_batch_source_id);
736 debug (l_module_name,'p_legal_entity_id='||p_legal_entity_id);
737 debug (l_module_name,'p_invoice_suffix='||p_invoice_suffix);
738 debug (l_module_name,'p_amount='||p_amount);
739 debug (l_module_name,'p_root_invoice_id='||p_root_invoice_id);
740 debug (l_module_name,'p_parent_invoice_id='||p_parent_invoice_id);
741 END IF;
742
743 IF (p_invoice_date_type='DUE') THEN
744 p_dm_trx_date := trunc(p_due_date);
745 ELSIF (p_invoice_date_type='ORI') THEN
746 p_dm_trx_date := trunc(p_trx_date);
747 ELSE
748 p_dm_trx_date := trunc(p_accrue_as_of_date);
749 END IF;
750
751 IF (p_retcode = g_SUCCESS) THEN
752 l_batch_source_rec.batch_source_id := p_batch_source_id;
753 END IF;
754
755 IF (p_retcode = g_SUCCESS) THEN
756 BEGIN
757 SELECT fv_ra_customer_trx_s.nextval
758 INTO l_dm_trx_number
759 FROM dual;
760 EXCEPTION
761 WHEN OTHERS THEN
762 p_retcode := g_FAILURE;
763 p_errbuf := SQLERRM;
764 l_location := l_module_name||'.select_fv_ra_customer_trx_s';
765 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
766 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
767 END;
768 END IF;
769
770 IF (p_retcode = g_SUCCESS) THEN
771 BEGIN
772 SELECT SUM(NVL(rctl.amount_due_remaining, rctl.quantity_invoiced*rctl.unit_selling_price))
773 INTO l_total_line_amount
774 FROM ra_customer_trx_lines rctl
775 WHERE rctl.customer_trx_id = p_parent_invoice_id;
776 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
777 debug (l_module_name,'l_total_line_amount='||l_total_line_amount);
778 END IF;
779 EXCEPTION
780 WHEN OTHERS THEN
781 p_retcode := g_FAILURE;
782 p_errbuf := SQLERRM;
783 l_location := l_module_name||'.select_ra_customer_trx_lines';
784 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
785 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
786 END;
787 END IF;
788
789 IF (p_retcode = g_SUCCESS) THEN
790 l_trx_header_tbl(1).trx_header_id := 1;
791 IF (p_invoice_suffix IS NOT NULL) THEN
792 --Bug8922069
793 --Debit Memo trx number should be suffixed by the invoice suffix
794 --and not be prefixed.
795 --l_trx_header_tbl(1).trx_number := p_invoice_suffix||'-';
796 l_trx_header_tbl(1).trx_number := '-'||p_invoice_suffix;
797 END IF;
798 --l_trx_header_tbl(1).trx_number := l_trx_header_tbl(1).trx_number||l_dm_trx_number;
799 l_trx_header_tbl(1).trx_number := l_dm_trx_number||l_trx_header_tbl(1).trx_number;
800 p_dm_trx_number := l_trx_header_tbl(1).trx_number;
801 l_trx_header_tbl(1).trx_date := p_dm_trx_date;
802 l_trx_header_tbl(1).trx_currency := p_trx_currency;
803 l_trx_header_tbl(1).trx_class := 'DM';
804 l_trx_header_tbl(1).cust_trx_type_id := p_trx_type_id;
805 l_trx_header_tbl(1).gl_date := p_gl_date;
806 l_trx_header_tbl(1).bill_to_customer_id := p_bill_to_customer_id;
807 l_trx_header_tbl(1).bill_to_contact_id := p_bill_to_contact_id;
808 l_trx_header_tbl(1).bill_to_address_id := p_bill_to_address_id;
809 l_trx_header_tbl(1).term_id := p_term_id;
810 l_trx_header_tbl(1).exchange_rate_type := p_exchange_rate_type;
811 l_trx_header_tbl(1).exchange_date := p_exchange_date;
812 l_trx_header_tbl(1).exchange_rate := p_exchange_rate;
813 --l_trx_header_tbl(1).related_customer_trx_id := p_root_invoice_id;
814 l_trx_header_tbl(1).org_id := p_org_id;
815 l_trx_header_tbl(1).legal_entity_id := p_legal_entity_id;
816 END IF;
817
818 IF (p_prorate_charge IN ('L', 'D')) THEN
819 l_line_counter := 0;
820 l_dist_counter := 0;
821
822 FOR inv_dist_rec IN (SELECT *
823 FROM ra_cust_trx_line_gl_dist rctl
824 WHERE rctl.customer_trx_id = p_parent_invoice_id
825 AND rctl.customer_trx_line_id IS NULL) LOOP
826 l_dist_counter := l_dist_counter + 1;
827 l_trx_dist_tbl(l_dist_counter).trx_header_id := 1;
828 l_trx_dist_tbl(l_dist_counter).trx_line_id := NULL;
829 l_trx_dist_tbl(l_dist_counter).trx_dist_id := l_dist_counter;
830 l_trx_dist_tbl(l_dist_counter).account_class := 'REC';
831 l_trx_dist_tbl(l_dist_counter).amount := p_amount;
832 --Currently AR allows only one Receivable line so putting the percent
833 --at 100.
834 l_trx_dist_tbl(l_dist_counter).percent := 100;
835 l_trx_dist_tbl(l_dist_counter).code_combination_id := inv_dist_rec.code_combination_id;
836 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
837 debug (l_module_name,'** DIST BEGIN **');
838 debug (l_module_name,'trx_header_id='||l_trx_dist_tbl(l_dist_counter).trx_header_id);
839 debug (l_module_name,'trx_line_id='||l_trx_dist_tbl(l_dist_counter).trx_line_id);
840 debug (l_module_name,'trx_dist_id='||l_trx_dist_tbl(l_dist_counter).trx_dist_id);
841 debug (l_module_name,'account_class='||l_trx_dist_tbl(l_dist_counter).account_class);
842 debug (l_module_name,'amount='||l_trx_dist_tbl(l_dist_counter).amount);
843 debug (l_module_name,'percent='||l_trx_dist_tbl(l_dist_counter).percent);
844 debug (l_module_name,'code_combination_id='||l_trx_dist_tbl(l_dist_counter).code_combination_id);
845 debug (l_module_name,'** DIST ENDS **');
846 END IF;
847 END LOOP;
848
849 IF (p_prorate_charge = 'L') THEN
850 FOR inv_lines_rec IN (SELECT *
851 FROM ra_customer_trx_lines rctl
852 WHERE rctl.customer_trx_id = p_parent_invoice_id) LOOP
853 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
854 debug (l_module_name,'inv_lines_rec.amount_due_remaining='||inv_lines_rec.amount_due_remaining);
855 debug (l_module_name,'inv_lines_rec.quantity_invoiced='||inv_lines_rec.quantity_invoiced);
856 debug (l_module_name,'inv_lines_rec.unit_selling_price='||inv_lines_rec.unit_selling_price);
857 END IF;
858 l_line_amount_due := NVL(inv_lines_rec.amount_due_remaining, (inv_lines_rec.quantity_invoiced*inv_lines_rec.unit_selling_price));
859 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
860 debug (l_module_name,'l_line_amount_due='||l_line_amount_due);
861 debug (l_module_name,'l_total_line_amount='||l_total_line_amount);
862 debug (l_module_name,'p_amount='||p_amount);
863 END IF;
864 l_prorated_line_amount := ROUND((l_line_amount_due/l_total_line_amount), 2)*p_amount;
865 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
866 debug (l_module_name,'l_prorated_line_amount='||l_prorated_line_amount);
867 END IF;
868 l_line_counter := l_line_counter + 1;
869 l_trx_lines_tbl(l_line_counter).trx_header_id := 1;
870 l_trx_lines_tbl(l_line_counter).trx_line_id := l_line_counter;
871 l_trx_lines_tbl(l_line_counter).line_number := l_line_counter;
872 l_trx_lines_tbl(l_line_counter).description := 'Accrue Federal Finance Charges';
873 l_trx_lines_tbl(l_line_counter).line_type := 'LINE';
874 l_trx_lines_tbl(l_line_counter).uom_code := 'EA';
875 l_trx_lines_tbl(l_line_counter).quantity_invoiced := 1;
876 l_trx_lines_tbl(l_line_counter).amount := l_prorated_line_amount;
877 l_trx_lines_tbl(l_line_counter).unit_selling_price := l_prorated_line_amount;
878
879 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
880 debug (l_module_name,'** LINES BEGIN **');
881 debug (l_module_name,'trx_header_id='||l_trx_lines_tbl(l_line_counter).trx_header_id);
882 debug (l_module_name,'trx_line_id='||l_trx_lines_tbl(l_line_counter).trx_line_id);
883 debug (l_module_name,'line_number='||l_trx_lines_tbl(l_line_counter).line_number);
884 debug (l_module_name,'description='||l_trx_lines_tbl(l_line_counter).description);
885 debug (l_module_name,'line_type='||l_trx_lines_tbl(l_line_counter).line_type);
886 debug (l_module_name,'uom_code='||l_trx_lines_tbl(l_line_counter).uom_code);
887 debug (l_module_name,'quantity_invoiced='||l_trx_lines_tbl(l_line_counter).quantity_invoiced);
888 debug (l_module_name,'amount='||l_trx_lines_tbl(l_line_counter).amount);
889 debug (l_module_name,'unit_selling_price='||l_trx_lines_tbl(l_line_counter).unit_selling_price);
890 debug (l_module_name,'** LINES END **');
891 END IF;
892
893 FOR inv_dist_rec IN (SELECT *
894 FROM ra_cust_trx_line_gl_dist rctl
895 WHERE rctl.customer_trx_id = p_parent_invoice_id
896 AND rctl.customer_trx_line_id = inv_lines_rec.customer_trx_line_id) LOOP
897 l_dist_counter := l_dist_counter + 1;
898 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
899 debug (l_module_name,'REC');
900 debug (l_module_name,'inv_dist_rec.percent='||inv_dist_rec.percent);
901 debug (l_module_name,'l_prorated_line_amount='||l_prorated_line_amount);
902 debug (l_module_name,'amount='||(l_prorated_line_amount*inv_dist_rec.percent)/100);
903 END IF;
904 l_trx_dist_tbl(l_dist_counter).trx_dist_id := l_dist_counter;
905 l_trx_dist_tbl(l_dist_counter).trx_header_id := 1;
906 l_trx_dist_tbl(l_dist_counter).trx_line_id := l_line_counter;
907 l_trx_dist_tbl(l_dist_counter).account_class := 'REV';
908 l_trx_dist_tbl(l_dist_counter).amount:= ROUND((l_prorated_line_amount*inv_dist_rec.percent)/100,2);
909
910 --l_trx_dist_tbl(l_dist_counter).percent := NULL;
911 l_trx_dist_tbl(l_dist_counter).percent := inv_dist_rec.percent;
912
913 l_trx_dist_tbl(l_dist_counter).code_combination_id := inv_dist_rec.code_combination_id;
914 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
915 debug (l_module_name,'** DIST BEGIN **');
916 debug (l_module_name,'trx_header_id='||l_trx_dist_tbl(l_dist_counter).trx_header_id);
917 debug (l_module_name,'trx_line_id='||l_trx_dist_tbl(l_dist_counter).trx_line_id);
918 debug (l_module_name,'trx_dist_id='||l_trx_dist_tbl(l_dist_counter).trx_dist_id);
919 debug (l_module_name,'account_class='||l_trx_dist_tbl(l_dist_counter).account_class);
920 debug (l_module_name,'amount='||l_trx_dist_tbl(l_dist_counter).amount);
921 debug (l_module_name,'percent='||l_trx_dist_tbl(l_dist_counter).percent);
922 debug (l_module_name,'code_combination_id='||l_trx_dist_tbl(l_dist_counter).code_combination_id);
923 debug (l_module_name,'** DIST ENDS **');
924 END IF;
925 END LOOP;
926 END LOOP;
927 ELSE -- It is D
928 l_line_counter := l_line_counter + 1;
929 l_trx_lines_tbl(l_line_counter).trx_header_id := 1;
930 l_trx_lines_tbl(l_line_counter).trx_line_id := l_line_counter;
931 l_trx_lines_tbl(l_line_counter).line_number := l_line_counter;
932 l_trx_lines_tbl(l_line_counter).description := 'Accrue Federal Finance Charges';
933 l_trx_lines_tbl(l_line_counter).line_type := 'LINE';
934 l_trx_lines_tbl(l_line_counter).uom_code := 'EA';
935 l_trx_lines_tbl(l_line_counter).quantity_invoiced := 1;
936 l_trx_lines_tbl(l_line_counter).amount := p_amount;
937 l_trx_lines_tbl(l_line_counter).unit_selling_price := p_amount;
938
939 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
940 debug (l_module_name,'** LINES BEGIN **');
941 debug (l_module_name,'trx_header_id='||l_trx_lines_tbl(l_line_counter).trx_header_id);
942 debug (l_module_name,'trx_line_id='||l_trx_lines_tbl(l_line_counter).trx_line_id);
943 debug (l_module_name,'line_number='||l_trx_lines_tbl(l_line_counter).line_number);
944 debug (l_module_name,'description='||l_trx_lines_tbl(l_line_counter).description);
945 debug (l_module_name,'line_type='||l_trx_lines_tbl(l_line_counter).line_type);
946 debug (l_module_name,'uom_code='||l_trx_lines_tbl(l_line_counter).uom_code);
947 debug (l_module_name,'quantity_invoiced='||l_trx_lines_tbl(l_line_counter).quantity_invoiced);
948 debug (l_module_name,'amount='||l_trx_lines_tbl(l_line_counter).amount);
949 debug (l_module_name,'unit_selling_price='||l_trx_lines_tbl(l_line_counter).unit_selling_price);
950 debug (l_module_name,'** LINES END **');
951 END IF;
952
953 SELECT SUM(amount)
954 INTO l_total_line_amount
955 FROM ra_cust_trx_line_gl_dist rctl
956 WHERE rctl.customer_trx_id = p_parent_invoice_id
957 AND rctl.customer_trx_line_id IS NOT NULL;
958
959
960 FOR inv_dist_rec IN (SELECT code_combination_id,
961 sum (amount) amount,
962 ROUND(sum (amount)/l_total_line_amount, 4) percent
963 FROM ra_cust_trx_line_gl_dist rctl
964 WHERE rctl.customer_trx_id = p_parent_invoice_id
965 AND rctl.customer_trx_line_id IS NOT NULL
966 GROUP BY code_combination_id
967 ORDER BY code_combination_id) LOOP
968
969 l_dist_counter := l_dist_counter + 1;
970 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
971 debug (l_module_name,'REC');
972 debug (l_module_name,'inv_dist_rec.percent='||inv_dist_rec.percent);
973 debug (l_module_name,'amount='||inv_dist_rec.amount);
974 END IF;
975 l_trx_dist_tbl(l_dist_counter).trx_dist_id := l_dist_counter;
976 l_trx_dist_tbl(l_dist_counter).trx_header_id := 1;
977 l_trx_dist_tbl(l_dist_counter).trx_line_id := l_line_counter;
978 l_trx_dist_tbl(l_dist_counter).account_class := 'REV';
979 l_trx_dist_tbl(l_dist_counter).amount:= p_amount*inv_dist_rec.percent;
980
981 --l_trx_dist_tbl(l_dist_counter).percent := NULL;
982 l_trx_dist_tbl(l_dist_counter).percent := inv_dist_rec.percent*100;
983
984 l_trx_dist_tbl(l_dist_counter).code_combination_id := inv_dist_rec.code_combination_id;
985 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
986 debug (l_module_name,'** DIST BEGIN **');
987 debug (l_module_name,'trx_header_id='||l_trx_dist_tbl(l_dist_counter).trx_header_id);
988 debug (l_module_name,'trx_line_id='||l_trx_dist_tbl(l_dist_counter).trx_line_id);
989 debug (l_module_name,'trx_dist_id='||l_trx_dist_tbl(l_dist_counter).trx_dist_id);
990 debug (l_module_name,'account_class='||l_trx_dist_tbl(l_dist_counter).account_class);
991 debug (l_module_name,'amount='||l_trx_dist_tbl(l_dist_counter).amount);
992 debug (l_module_name,'percent='||l_trx_dist_tbl(l_dist_counter).percent);
993 debug (l_module_name,'code_combination_id='||l_trx_dist_tbl(l_dist_counter).code_combination_id);
994 debug (l_module_name,'** DIST ENDS **');
995 END IF;
996 END LOOP;
997 END IF;
998 IF (p_retcode = g_SUCCESS) THEN
999 check_and_correct_rounding
1000 (
1001 p_amount => p_amount,
1002 p_trx_header_tbl => l_trx_header_tbl,
1003 p_trx_lines_tbl => l_trx_lines_tbl,
1004 p_trx_dist_tbl => l_trx_dist_tbl,
1005 p_errbuf => p_errbuf,
1006 p_retcode => p_retcode
1007 );
1008 END IF;
1009 ELSE
1010 l_trx_lines_tbl(1).trx_header_id := 1;
1011 l_trx_lines_tbl(1).trx_line_id := 1;
1012 l_trx_lines_tbl(1).line_number := 1;
1013 l_trx_lines_tbl(1).description := 'Accrue Federal Finance Charges';
1014 l_trx_lines_tbl(1).line_type := 'LINE';
1015 l_trx_lines_tbl(1).uom_code := 'EA';
1016 l_trx_lines_tbl(1).quantity_invoiced := 1;
1017 l_trx_lines_tbl(1).amount := p_amount;
1018 l_trx_lines_tbl(1).unit_selling_price := p_amount;
1019
1020 l_trx_dist_tbl(1).trx_dist_id := 1;
1021 l_trx_dist_tbl(1).trx_header_id := 1;
1022 l_trx_dist_tbl(1).trx_line_id := NULL;
1023 l_trx_dist_tbl(1).account_class := 'REC';
1024 l_trx_dist_tbl(1).amount:= p_amount;
1025 l_trx_dist_tbl(1).percent := 100;
1026 l_trx_dist_tbl(1).code_combination_id := p_rec_ccid;
1027
1028 l_trx_dist_tbl(2).trx_dist_id := 2;
1029 l_trx_dist_tbl(2).trx_header_id := 1;
1030 l_trx_dist_tbl(2).trx_line_id := 1;
1031 l_trx_dist_tbl(2).account_class := 'REV';
1032 l_trx_dist_tbl(2).amount := p_amount;
1033 l_trx_dist_tbl(2).percent := NULL;
1034 l_trx_dist_tbl(2).code_combination_id := p_rev_ccid;
1035 END IF;
1036
1037
1038 IF (p_retcode = g_SUCCESS) THEN
1039 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1040 debug (l_module_name,'Calling ar_invoice_api_pub.create_single_invoice');
1041 END IF;
1042
1043 ar_invoice_api_pub.create_single_invoice
1044 (
1045 p_api_version => l_api_version,
1046 p_init_msg_list => FND_API.G_TRUE,
1047 p_commit => FND_API.G_FALSE,
1048 p_batch_source_rec => l_batch_source_rec,
1049 p_trx_header_tbl => l_trx_header_tbl,
1050 p_trx_lines_tbl => l_trx_lines_tbl,
1051 p_trx_dist_tbl => l_trx_dist_tbl,
1052 p_trx_salescredits_tbl => l_trx_salescredits_tbl,
1053 x_customer_trx_id => p_dm_invoice_id,
1054 x_return_status => l_return_status,
1055 x_msg_count => l_message_count,
1056 x_msg_data => l_message_data
1057 );
1058
1059 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1060 debug (l_module_name,'l_return_status='||l_return_status);
1061 debug (l_module_name,'l_message_count='||l_message_count);
1062 debug (l_module_name,'l_message_data='||l_message_data);
1063 debug (l_module_name,'p_dm_invoice_id='||p_dm_invoice_id);
1064 END IF;
1065
1066 interpret_dm_error
1067 (
1068 p_trx_number => p_trx_number,
1069 p_return_status => l_return_status,
1070 p_message_count => l_message_count,
1071 p_message_data => l_message_data,
1072 p_errbuf => p_errbuf,
1073 p_retcode => p_retcode
1074 );
1075 END IF;
1076
1077
1078 IF (p_retcode = g_SUCCESS) THEN
1079 -- There is no way to put p_related_invoice_id in the API
1080 -- as it is giving an error. For the time being as a
1081 -- workaround updating the filed directly.
1082 BEGIN
1083 UPDATE ra_customer_trx rct
1084 SET rct.related_customer_trx_id = p_root_invoice_id,
1085 rct.interface_header_attribute3 = p_charge_type
1086 WHERE rct.customer_trx_id = p_dm_invoice_id;
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 p_retcode := g_FAILURE;
1090 p_errbuf := SQLERRM;
1091 l_location := l_module_name||'.update_ra_customer_trx';
1092 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1093 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1094 END;
1095 END IF;
1096
1097
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 p_retcode := g_FAILURE;
1101 p_errbuf := SQLERRM;
1102 l_location := l_module_name||'.final_exception';
1103 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1104 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
1105 END create_dm;
1106
1107 PROCEDURE process_and_create_dm
1108 (
1109 p_org_id IN NUMBER,
1110 p_set_of_books_id IN NUMBER,
1111 p_accrue_as_of_date IN DATE,
1112 p_finance_charges IN NUMBER,
1113 p_invoice_id IN NUMBER,
1114 p_customer_id IN NUMBER,
1115 p_trx_number IN VARCHAR2,
1116 p_trx_date IN DATE,
1117 p_charge_id IN NUMBER,
1118 p_finance_charge_group_hdr_id IN NUMBER,
1119 p_finance_charge_group_dtl_id IN NUMBER,
1120 p_invoice_currency_code IN VARCHAR2,
1121 p_trx_type_id IN NUMBER,
1122 p_gl_date IN DATE,
1123 p_bill_to_customer_id IN NUMBER,
1124 p_bill_to_contact_id IN NUMBER,
1125 p_bill_to_address_id IN NUMBER,
1126 p_invoice_due_date IN DATE,
1127 p_exchange_date IN ra_customer_trx.exchange_date%TYPE,
1128 p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE,
1129 p_exchange_rate_type IN ra_customer_trx.exchange_rate_type%TYPE,
1130 p_root_invoice_id IN NUMBER,
1131 p_invoice_date_type IN VARCHAR2,
1132 p_immediate_term_id IN NUMBER,
1133 p_invoice_term_id IN NUMBER,
1134 p_gl_id_rec IN NUMBER,
1135 p_gl_id_rev IN NUMBER,
1136 p_prorate_charge IN VARCHAR2,
1137 p_batch_source_id IN NUMBER,
1138 p_inv_amount_due_remaining IN NUMBER,
1139 p_inv_amount_due_original IN NUMBER,
1140 p_charge_type IN VARCHAR2,
1141 p_invoice_suffix IN VARCHAR2,
1142 p_errbuf OUT NOCOPY VARCHAR2,
1143 p_retcode OUT NOCOPY NUMBER
1144 )
1145 IS
1146 l_module_name VARCHAR2(200);
1147 l_location VARCHAR2(200);
1148 l_term_id NUMBER;
1149 l_dm_invoice_id NUMBER;
1150 l_dm_trx_number ra_customer_trx_all.trx_number%TYPE;
1151 l_dm_trx_date ra_customer_trx_all.trx_date%TYPE;
1152 l_root_invoice_id NUMBER;
1153 BEGIN
1154 l_module_name := g_module_name || 'process_and_create_dm';
1155 p_retcode := g_SUCCESS;
1156 p_errbuf := null;
1157
1158 -- There are finance charges. Hence a DM has to be created.
1159 IF (NVL(p_root_invoice_id, 0) = 0) THEN
1160 l_root_invoice_id := p_invoice_id;
1161 ELSE
1162 l_root_invoice_id := p_root_invoice_id;
1163 END IF;
1164
1165 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1166 debug(l_module_name,'Calling create_dm');
1167 END IF;
1168
1169 l_term_id := p_immediate_term_id;
1170 IF (p_invoice_date_type='ORI') THEN
1171 l_term_id := p_invoice_term_id;
1172 END IF;
1173
1174 create_dm
1175 (
1176 p_accrue_as_of_date => p_accrue_as_of_date,
1177 p_parent_invoice_id => p_invoice_id,
1178 p_trx_number => p_trx_number,
1179 p_prorate_charge => p_prorate_charge,
1180 p_invoice_date_type => p_invoice_date_type,
1181 p_trx_date => p_trx_date,
1182 p_due_date => p_invoice_due_date,
1183 p_trx_currency => p_invoice_currency_code,
1184 p_trx_type_id => p_trx_type_id,
1185 p_gl_date => p_gl_date,
1186 p_bill_to_customer_id => p_bill_to_customer_id,
1187 p_bill_to_contact_id => p_bill_to_contact_id,
1188 p_bill_to_address_id => p_bill_to_address_id,
1189 p_term_id => l_term_id,
1190 p_exchange_date => p_exchange_date,
1191 p_exchange_rate => p_exchange_rate,
1192 p_exchange_rate_type => p_exchange_rate_type,
1193 p_root_invoice_id => l_root_invoice_id,
1194 p_org_id => p_org_id,
1195 p_rec_ccid => p_gl_id_rec,
1196 p_rev_ccid => p_gl_id_rev,
1197 p_legal_entity_id => NULL,
1198 p_amount => p_finance_charges,
1199 p_batch_source_id => p_batch_source_id,
1200 p_charge_type => p_charge_type,
1201 p_invoice_suffix => p_invoice_suffix,
1202 p_dm_invoice_id => l_dm_invoice_id,
1203 p_dm_trx_number => l_dm_trx_number,
1204 p_dm_trx_date => l_dm_trx_date,
1205 p_errbuf => p_errbuf,
1206 p_retcode => p_retcode
1207 );
1208
1209 IF (p_retcode = g_SUCCESS) THEN
1210 BEGIN
1211 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1212 debug(l_module_name,'Insert fv_ar_fin_chrg_invoices');
1213 END IF;
1214 INSERT INTO fv_ar_fin_chrg_invoices
1215 (
1216 org_id,
1217 customer_id,
1218 invoice_id,
1219 invoice_number,
1220 invoice_date,
1221 invoice_amount,
1222 finance_charges,
1223 waive_flag,
1224 enabled_flag,
1225 parent_invoice_id,
1226 root_invoice_id,
1227 last_update_date,
1228 last_updated_by,
1229 created_by,
1230 creation_date,
1231 last_update_login,
1232 request_id,
1233 finance_charge_group_hdr_id,
1234 finance_charge_group_dtl_id,
1235 charge_id
1236 )
1237 VALUES
1238 (
1239 p_org_id,
1240 p_customer_id,
1241 l_dm_invoice_id,
1242 l_dm_trx_number,
1243 l_dm_trx_date,
1244 p_finance_charges,
1245 0,
1246 'N',
1247 'Y',
1248 p_invoice_id,
1249 l_root_invoice_id,
1250 SYSDATE,
1251 g_user_id,
1252 g_user_id,
1253 SYSDATE,
1254 g_login_id,
1255 g_request_id,
1256 p_finance_charge_group_hdr_id,
1257 p_finance_charge_group_dtl_id,
1258 p_charge_id
1259 );
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 p_retcode := g_FAILURE;
1263 p_errbuf := SQLERRM;
1264 l_location := l_module_name||'.insert_fv_ar_fin_chrg_invoices';
1265 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1266 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1267 END;
1268 END IF;
1269
1270 IF (p_retcode = g_SUCCESS) THEN
1271 BEGIN
1272 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1273 debug(l_module_name,'Insert fv_ar_fin_chrg_inv_lines');
1274 END IF;
1275 INSERT INTO fv_ar_fin_chrg_inv_lines
1276 (
1277 org_id,
1278 customer_id,
1279 invoice_id,
1280 line_number,
1281 gl_date,
1282 line_amount,
1283 last_update_date,
1284 last_updated_by,
1285 created_by,
1286 creation_date,
1287 last_update_login,
1288 request_id,
1289 finance_charge_group_hdr_id,
1290 finance_charge_group_dtl_id,
1291 charge_id
1292 )
1293 SELECT rctl.org_id,
1294 p_customer_id,
1295 rctl.customer_trx_id,
1296 rctl.line_number,
1297 p_gl_date,
1298 rctl.quantity_invoiced*rctl.unit_selling_price,
1299 SYSDATE,
1300 g_user_id,
1301 g_user_id,
1302 SYSDATE,
1303 g_login_id,
1304 g_request_id,
1305 p_finance_charge_group_hdr_id,
1306 p_finance_charge_group_dtl_id,
1307 p_charge_id
1308 FROM ra_customer_trx_lines rctl
1309 WHERE rctl.customer_trx_id = l_dm_invoice_id;
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 p_retcode := g_FAILURE;
1313 p_errbuf := SQLERRM;
1314 l_location := l_module_name||'.insert_fv_ar_fin_chrg_inv_lines';
1315 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1316 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1317 END;
1318 END IF;
1319
1320 IF (p_retcode = g_SUCCESS) THEN
1321 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1322 debug(l_module_name,'Updating fv_ar_fin_chrg_invoices(1)');
1323 END IF;
1324
1325 BEGIN
1326 UPDATE fv_ar_fin_chrg_invoices
1327 SET request_id=g_request_id,
1328 last_updated_by = g_user_id,
1329 last_update_date = SYSDATE,
1330 last_accrual_date = p_accrue_as_of_date,
1331 finance_charges = NVL(finance_charges, 0) + p_finance_charges,
1332 current_child_invoice_id = l_dm_invoice_id, --can be used in future for adjusting
1333 last_line_number = 1 --can be used in future for adjusting
1334 WHERE invoice_id = p_invoice_id;
1335
1336 /* Bug 8515997: If p_invoice_id=l_root_invoice_id, then finance charges get doubled. Added condition to check it */
1337 IF (l_root_invoice_id <> p_invoice_id) THEN
1338 UPDATE fv_ar_fin_chrg_invoices
1339 SET last_updated_by = g_user_id,
1340 last_update_date = SYSDATE,
1341 finance_charges = NVL(finance_charges, 0) + p_finance_charges
1342 WHERE invoice_id = l_root_invoice_id;
1343 END IF;
1344
1345 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1346 debug(l_module_name,'Updated '||SQL%ROWCOUNT||' rows.');
1347 END IF;
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 p_retcode := g_FAILURE;
1351 p_errbuf := SQLERRM;
1352 l_location := l_module_name||'.update_fv_ar_fin_chrg_invoices (1)';
1353 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1354 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1355 END;
1356 END IF;
1357
1358 IF (p_retcode = g_SUCCESS) THEN
1359 FOR dm_rec IN (SELECT fcgd.base_charge_id,
1360 fcgd.assessed_charge_id
1361 FROM fv_finance_charge_grp_dtls fcgd,
1362 fv_finance_charge_grp_hdrs fcgh
1363 WHERE fcgh.finance_charge_group_hdr_id = p_finance_charge_group_hdr_id
1364 AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
1365 AND fcgd.base_charge_id = p_charge_id
1366 AND fcgd.start_date <= sysdate
1367 AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
1368 ) LOOP
1369 BEGIN
1370 INSERT INTO fv_invoice_finance_chrgs_all
1371 (
1372 customer_trx_id,
1373 customer_id,
1374 charge_id,
1375 set_of_books_id,
1376 last_update_date,
1377 last_updated_by,
1378 created_by,
1379 creation_date,
1380 last_update_login,
1381 waive_flag,
1382 org_id,
1383 finance_charge_group_hdr_id,
1384 finance_charge_group_dtl_id,
1385 enabled_flag,
1386 base_charge_id,
1387 request_id
1388 )
1389 VALUES
1390 (
1391 l_dm_invoice_id,
1392 p_customer_id,
1393 dm_rec.assessed_charge_id,
1394 p_set_of_books_id,
1395 SYSDATE,
1396 g_user_id,
1397 g_user_id,
1398 SYSDATE,
1399 g_login_id,
1400 'N',
1401 p_org_id,
1402 p_finance_charge_group_hdr_id,
1403 p_finance_charge_group_dtl_id,
1404 'Y',
1405 dm_rec.base_charge_id,
1406 g_request_id
1407 );
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 p_retcode := g_FAILURE;
1411 p_errbuf := SQLERRM;
1412 l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';
1413 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1414 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1415 END;
1416 END LOOP;
1417 END IF;
1418
1419 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
1420 debug(l_module_name,g_enter);
1421 END IF;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 p_retcode := g_FAILURE;
1425 p_errbuf := SQLERRM;
1426 l_location := l_module_name||'.final_exception';
1427 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1428 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
1429 END process_and_create_dm;
1430
1431 PROCEDURE get_cvf_rate
1432 (
1433 p_accrue_as_of_date IN DATE,
1434 p_cvf_rate OUT NOCOPY NUMBER,
1435 p_errbuf OUT NOCOPY VARCHAR2,
1436 p_retcode OUT NOCOPY NUMBER
1437 )
1438 IS
1439 l_module_name VARCHAR2(200);
1440 l_location VARCHAR2(200);
1441 BEGIN
1442 l_module_name := g_module_name || 'get_cvf_rate';
1443 p_retcode := g_SUCCESS;
1444 p_errbuf := NULL;
1445 p_cvf_rate := NULL;
1446
1447 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1448 debug(l_module_name,'Inside '||l_module_name);
1449 debug(l_module_name,'p_accrue_as_of_date '||p_accrue_as_of_date);
1450 END IF;
1451 BEGIN
1452 SELECT a.curr_value_of_funds_percent
1453 INTO p_cvf_rate
1454 FROM fv_value_of_fund_periods a
1455 WHERE p_accrue_as_of_date BETWEEN a.effective_start_date AND NVL(a.effective_end_date, g_hi_date);
1456 EXCEPTION
1457 WHEN NO_DATA_FOUND THEN
1458 p_cvf_rate := NULL;
1459 END;
1460
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 p_retcode := g_FAILURE;
1464 p_errbuf := SQLERRM;
1465 debug(l_module_name,'l_location='||l_location);
1466 l_location := l_module_name||'.final_exception';
1467 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1468 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
1469 END get_cvf_rate;
1470
1471 PROCEDURE get_last_accrual_date
1472 (
1473 p_payment_schedule_id IN NUMBER,
1474 p_charge_type IN VARCHAR2,
1475 p_last_accrual_date OUT NOCOPY DATE,
1476 p_errbuf OUT NOCOPY VARCHAR2,
1477 p_retcode OUT NOCOPY NUMBER
1478 )
1479 IS
1480 l_module_name VARCHAR2(200);
1481 l_location VARCHAR2(200);
1482 BEGIN
1483 l_module_name := g_module_name || 'get_last_accrual_date';
1484 p_retcode := g_SUCCESS;
1485 p_errbuf := NULL;
1486 p_last_accrual_date := NULL;
1487
1488 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1489 debug(l_module_name,'Inside '||l_module_name);
1490 debug(l_module_name,'p_payment_schedule_id '||p_payment_schedule_id);
1491 debug(l_module_name,'p_charge_type '||p_charge_type);
1492 END IF;
1493 BEGIN
1494 SELECT a.last_accrual_date
1495 INTO p_last_accrual_date
1496 FROM fv_ar_controls a
1497 WHERE a.payment_schedule_id = p_payment_schedule_id
1498 AND a.created_from = p_charge_type;
1499 EXCEPTION
1500 WHEN NO_DATA_FOUND THEN
1501 p_last_accrual_date := NULL;
1502 END;
1503
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 p_retcode := g_FAILURE;
1507 p_errbuf := SQLERRM;
1508 debug(l_module_name,'l_location='||l_location);
1509 l_location := l_module_name||'.final_exception';
1510 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1511 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
1512 END get_last_accrual_date;
1513
1514 PROCEDURE set_last_accrual_date
1515 (
1516 p_org_id IN NUMBER,
1517 p_payment_schedule_id IN NUMBER,
1518 p_charge_type IN VARCHAR2,
1519 p_last_accrual_date IN DATE,
1520 p_errbuf OUT NOCOPY VARCHAR2,
1521 p_retcode OUT NOCOPY NUMBER
1522 )
1523 IS
1524 l_module_name VARCHAR2(200);
1525 l_location VARCHAR2(200);
1526 BEGIN
1527 l_module_name := g_module_name || 'set_last_accrual_date';
1528 p_retcode := g_SUCCESS;
1529 p_errbuf := NULL;
1530
1531 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1532 debug(l_module_name,'Inside '||l_module_name);
1533 debug(l_module_name,'p_org_id '||p_org_id);
1534 debug(l_module_name,'p_payment_schedule_id '||p_payment_schedule_id);
1535 debug(l_module_name,'p_charge_type '||p_charge_type);
1536 debug(l_module_name,'p_last_accrual_date '||p_last_accrual_date);
1537 END IF;
1538
1539 UPDATE fv_ar_controls a
1540 SET last_accrual_date = p_last_accrual_date
1541 WHERE a.payment_schedule_id = p_payment_schedule_id
1542 AND a.created_from = p_charge_type;
1543
1544 IF (SQL%ROWCOUNT = 0) THEN
1545 INSERT INTO fv_ar_controls
1546 (
1547 payment_schedule_id,
1548 created_from,
1549 last_accrual_date,
1550 org_id
1551 )
1552 VALUES
1553 (
1554 p_payment_schedule_id,
1555 p_charge_type,
1556 p_last_accrual_date,
1557 p_org_id
1558 );
1559 END IF;
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 p_retcode := g_FAILURE;
1563 p_errbuf := SQLERRM;
1564 debug(l_module_name,'l_location='||l_location);
1565 l_location := l_module_name||'.final_exception';
1566 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1567 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
1568 END set_last_accrual_date;
1569
1570 ----------------------------------------------------------------------------------------------
1571
1572 /*****************************************************************************/
1573 /* Begin Accrue Finance Charge Process */
1574 /*****************************************************************************/
1575 PROCEDURE accrue_finance_charge
1576 (
1577 p_errbuf OUT NOCOPY VARCHAR2,
1578 p_retcode OUT NOCOPY NUMBER,
1579 p_invoice_date_type VARCHAR2,
1580 p_gl_date VARCHAR2
1581 ) IS
1582 l_module_name VARCHAR2(200);
1583 l_location VARCHAR2(200);
1584 l_message VARCHAR2(1024);
1585 l_gl_date DATE;
1586 l_old_charge_id fv_finance_charge_controls.charge_id%TYPE;
1587 l_old_invoice_id ra_customer_trx.customer_trx_id%TYPE;
1588 l_accrue_as_of_date DATE;
1589 l_accrual_date DATE;
1590 l_finance_charges NUMBER;
1591 l_immediate_term_id NUMBER;
1592 l_number_of_periods NUMBER;
1593 l_first_late_day DATE;
1594 l_inv_amount_due_original NUMBER;
1595 l_inv_amount_due_remaining NUMBER;
1596 l_cvf_rate NUMBER;
1597
1598 BEGIN
1599 l_module_name := g_module_name || 'assign_finance_charge';
1600 p_retcode := g_SUCCESS;
1601 p_errbuf := NULL;
1602 l_accrue_as_of_date := TRUNC(SYSDATE);
1603 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
1604 debug(l_module_name,g_enter);
1605 END IF;
1606
1607 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1608 debug(l_module_name,'p_invoice_date_type='||p_invoice_date_type);
1609 debug(l_module_name,'p_gl_date='||p_gl_date);
1610 END IF;
1611
1612
1613 l_gl_date:= TO_DATE(p_gl_date,'YYYY/MM/DD HH24:MI:SS');
1614
1615 initialize_program_variables
1616 (
1617 p_gl_date => l_gl_date,
1618 p_term_id => l_immediate_term_id,
1619 p_errbuf => p_errbuf,
1620 p_retcode => p_retcode
1621 );
1622
1623 IF (p_retcode = g_SUCCESS) THEN
1624 IF (p_retcode = g_SUCCESS) THEN
1625 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1626 debug(l_module_name,'Calling get_cvf_rate');
1627 END IF;
1628 /*
1629 Get the Current Value of Fund rate as this is not going to change
1630 per Invoice.
1631 */
1632 get_cvf_rate
1633 (
1634 p_accrue_as_of_date => l_accrue_as_of_date,
1635 p_cvf_rate => l_cvf_rate,
1636 p_errbuf => p_errbuf,
1637 p_retcode => p_retcode
1638 );
1639 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1640 debug(l_module_name,'p_cvf_rate ='||l_cvf_rate);
1641 END IF;
1642 END IF;
1643 END IF;
1644
1645 IF (p_retcode = g_SUCCESS) THEN
1646 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1647 debug(l_module_name,'main_rec');
1648 END IF;
1649 FOR main_rec IN (SELECT aps.customer_trx_id invoice_id,
1650 aps.amount_due_remaining,
1651 aps.amount_due_original,
1652 aps.payment_schedule_id,
1653 fcc.charge_id,
1654 fcc.charge_type,
1655 fcc.batch_source_id,
1656 aps.trx_number,
1657 aps.due_date,
1658 nvl(fch.amount, nvl(fch.rate,0)/100) rate_amount,
1659 decode(fch.amount, NULL, 'Y', 'N') rate_flag,
1660 fch.rate_base,
1661 fch.rate_type,
1662 nvl(fcc.accrue_at_invoice,'N') accrue_at_invoice,
1663 fcc.trx_type_id,
1664 fcc.first_accrual,
1665 fcc.accrual_interval,
1666 fcc.grace_period,
1667 fcc.receivables_trx_id,
1668 rct.bill_to_customer_id,
1669 rct.bill_to_contact_id,
1670 rct.invoice_currency_code,
1671 rct.exchange_date,
1672 rct.exchange_rate,
1673 rct.exchange_rate_type,
1674 aps.trx_date,
1675 rctt.gl_id_rev,
1676 rctt.gl_id_rec,
1677 rsua.cust_acct_site_id bill_to_address_id,
1678 fai.last_accrual_date,
1679 fcc.base_date_type,
1680 fai.root_invoice_id,
1681 rct.org_id,
1682 rct.term_id,
1683 fifc.customer_id,
1684 fifc.set_of_books_id,
1685 fifc.finance_charge_group_hdr_id,
1686 fifc.finance_charge_group_dtl_id,
1687 NVL(fcc.prorate_charge, 'N') prorate_charge,
1688 fcc.invoice_suffix
1689 FROM ar_payment_schedules aps,
1690 fv_invoice_finance_chrgs fifc,
1691 fv_finance_charge_controls fcc,
1692 fv_finance_charge_history fch,
1693 ra_customer_trx rct,
1694 ra_cust_trx_types rctt,
1695 hz_cust_site_uses_all rsua,
1696 fv_ar_fin_chrg_invoices fai
1697 WHERE (due_date + first_accrual + grace_period) <= l_accrue_as_of_date
1698 AND fifc.waive_flag = 'N'
1699 AND aps.amount_due_remaining > 0
1700 AND aps.status <> 'CL'
1701 AND aps.customer_trx_id = rct.customer_trx_id
1702
1703 /* AND nvl(rct.interface_header_attribute3,'XX') NOT IN (SELECT charge_type
1704 FROM fv_finance_charge_controls)*/
1705
1706 AND rct.set_of_books_id = g_set_of_books_id
1707 --bug 9716140
1708 AND (rct.related_customer_trx_id IS NULL OR
1709 EXISTS(SELECT 'x'
1710 FROM fv_finance_chrg_cust_classes fccc,
1711 fv_finance_charge_grp_dtls fcgd,
1712 hz_cust_accounts hzca
1713 WHERE fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
1714 AND fccc.enabled_flag = 'Y'
1715 AND fcgd.assessed_charge_id = fcc.charge_id
1716 AND fcgd.base_charge_id <> 0
1717 AND fccc.customer_class = hzca.customer_class_code
1718 AND set_of_books_id = g_set_of_books_id))
1719 -- end of bug 9716140
1720 AND aps.customer_trx_id = fifc.customer_trx_id
1721 AND fifc.charge_id = fcc.charge_id
1722 AND fcc.charge_id = fch.charge_id
1723 AND fcc.enabled_flag = 'Y'
1724 AND aps.due_date
1725 BETWEEN fch.start_date AND
1726 nvl(fch.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1727 AND rctt.cust_trx_type_id = fcc.trx_type_id
1728 AND rsua.site_use_id = rct.bill_to_site_use_id
1729 AND fai.invoice_id = fifc.customer_trx_id
1730 AND EXISTS ( SELECT 'x'
1731 FROM fv_finance_chrg_cust_classes fccc,
1732 fv_finance_charge_grp_dtls fcgd,
1733 hz_cust_accounts hzca
1734 WHERE fccc.customer_class = hzca.customer_class_code
1735 AND fccc.enabled_flag = 'Y'
1736 AND fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
1737 AND fcgd.assessed_charge_id = fcc.charge_id
1738 AND set_of_books_id = g_set_of_books_id)
1739 ORDER BY rct.customer_trx_id,
1740 fcc.charge_id,
1741 aps.payment_schedule_id)
1742 LOOP
1743 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1744 debug(l_module_name,'************************************************************');
1745 debug(l_module_name,'main_rec.invoice_id='||main_rec.invoice_id);
1746 debug(l_module_name,'main_rec.amount_due_remaining='||main_rec.amount_due_remaining);
1747 debug(l_module_name,'main_rec.payment_schedule_id='||main_rec.payment_schedule_id);
1748 debug(l_module_name,'main_rec.charge_id='||main_rec.charge_id);
1749 debug(l_module_name,'main_rec.charge_type='||main_rec.charge_type);
1750 debug(l_module_name,'main_rec.batch_source_id='||main_rec.batch_source_id);
1751 debug(l_module_name,'main_rec.trx_number='||main_rec.trx_number);
1752 debug(l_module_name,'main_rec.due_date='||main_rec.due_date);
1753 debug(l_module_name,'main_rec.amount='||main_rec.rate_amount);
1754 debug(l_module_name,'main_rec.rate_flag='||main_rec.rate_flag);
1755 debug(l_module_name,'main_rec.rate_base='||main_rec.rate_base);
1756 debug(l_module_name,'main_rec.rate_type='||main_rec.rate_type);
1757 debug(l_module_name,'main_rec.accrue_at_invoice='||main_rec.accrue_at_invoice);
1758 debug(l_module_name,'main_rec.trx_type_id='||main_rec.trx_type_id);
1759 debug(l_module_name,'main_rec.first_accrual='||main_rec.first_accrual);
1760 debug(l_module_name,'main_rec.accrual_interval='||main_rec.accrual_interval);
1761 debug(l_module_name,'main_rec.grace_period='||main_rec.grace_period);
1762 debug(l_module_name,'main_rec.receivables_trx_id='||main_rec.receivables_trx_id);
1763 debug(l_module_name,'main_rec.bill_to_customer_id='||main_rec.bill_to_customer_id);
1764 debug(l_module_name,'main_rec.bill_to_contact_id='||main_rec.bill_to_contact_id);
1765 debug(l_module_name,'main_rec.invoice_currency_code='||main_rec.invoice_currency_code);
1766 debug(l_module_name,'main_rec.exchange_date='||main_rec.exchange_date);
1767 debug(l_module_name,'main_rec.exchange_rate='||main_rec.exchange_rate);
1768 debug(l_module_name,'main_rec.exchange_rate_type='||main_rec.exchange_rate_type);
1769 debug(l_module_name,'main_rec.trx_date='||main_rec.trx_date);
1770 debug(l_module_name,'main_rec.gl_id_rev='||main_rec.gl_id_rev);
1771 debug(l_module_name,'main_rec.gl_id_rec='||main_rec.gl_id_rec);
1772 debug(l_module_name,'main_rec.bill_to_address_id='||main_rec.bill_to_address_id);
1773 debug(l_module_name,'main_rec.last_accrual_date='||main_rec.last_accrual_date);
1774 debug(l_module_name,'main_rec.base_date_type='||main_rec.base_date_type);
1775 debug(l_module_name,'main_rec.root_invoice_id='||main_rec.root_invoice_id);
1776 debug(l_module_name,'main_rec.org_id='||main_rec.org_id);
1777 debug(l_module_name,'main_rec.term_id='||main_rec.term_id);
1778 debug(l_module_name,'main_rec.customer_id='||main_rec.customer_id);
1779 debug(l_module_name,'main_rec.set_of_books_id='||main_rec.set_of_books_id);
1780 debug(l_module_name,'main_rec.finance_charge_group_hdr_id='||main_rec.finance_charge_group_hdr_id);
1781 debug(l_module_name,'main_rec.finance_charge_group_dtl_id='||main_rec.finance_charge_group_dtl_id);
1782 debug(l_module_name,'main_rec.prorate_charge='||main_rec.prorate_charge);
1783 debug(l_module_name,'main_rec.invoice_suffix='||main_rec.invoice_suffix);
1784 debug(l_module_name,'************************************************************');
1785 END IF;
1786
1787 IF (main_rec.batch_source_id IS NULL) THEN
1788 p_retcode := g_FAILURE;
1789 p_errbuf := 'Please define Batch Source in Define Finance Charges for this finance charge '||main_rec.charge_type||'.';
1790 l_location := l_module_name||'.main_rec.batch_source_id';
1791 log (l_location,l_location);
1792 log (l_location,p_errbuf);
1793 END IF;
1794
1795 IF (main_rec.rate_type = 'CVFR' AND l_cvf_rate IS NULL) THEN
1796 p_retcode := g_FAILURE;
1797 p_errbuf := 'You have setup rate type as CVFR for charge type '||main_rec.charge_type||' but no rates are defined in Define Funds Rates.';
1798 l_location := l_module_name||'.main_rec.rate_type';
1799 log (l_location,l_location);
1800 log (l_location,p_errbuf);
1801 END IF;
1802
1803 IF (p_retcode = g_SUCCESS) THEN
1804 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1805 debug(l_module_name,'Calling get_last_accrual_date');
1806 END IF;
1807 get_last_accrual_date
1808 (
1809 p_payment_schedule_id => main_rec.payment_schedule_id,
1810 p_charge_type => main_rec.charge_type,
1811 p_last_accrual_date => main_rec.last_accrual_date,
1812 p_errbuf => p_errbuf,
1813 p_retcode => p_retcode
1814 );
1815 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1816 debug(l_module_name,'main_rec.last_accrual_date='||main_rec.last_accrual_date);
1817 END IF;
1818 END IF;
1819
1820 IF (p_retcode = g_SUCCESS) THEN
1821 g_out_rec_count := g_out_rec_count + 1;
1822 g_out_rec_tab(g_out_rec_count).invoice_id := main_rec.invoice_id;
1823 g_out_rec_tab(g_out_rec_count).invoice_number := main_rec.trx_number;
1824 g_out_rec_tab(g_out_rec_count).amount_due_remaining := main_rec.amount_due_remaining;
1825 g_out_rec_tab(g_out_rec_count).amount_due_original := main_rec.amount_due_original;
1826 g_out_rec_tab(g_out_rec_count).due_date := main_rec.due_date;
1827
1828 -- Bug 8947425
1829 IF ( main_rec.rate_flag IS NOT NULL) then
1830 IF (main_rec.rate_flag = 'Y') THEN
1831 g_out_rec_tab(g_out_rec_count).amount_based := 'N';
1832 ELSE
1833 g_out_rec_tab(g_out_rec_count).amount_based := 'Y';
1834 END IF;
1835 END IF;
1836
1837 g_out_rec_tab(g_out_rec_count).amount_or_rate := main_rec.rate_amount;
1838 g_out_rec_tab(g_out_rec_count).first_accrual := main_rec.first_accrual;
1839 g_out_rec_tab(g_out_rec_count).accrual_interval := main_rec.accrual_interval;
1840 g_out_rec_tab(g_out_rec_count).grace_period := main_rec.grace_period;
1841 g_out_rec_tab(g_out_rec_count).last_accrual_date := main_rec.last_accrual_date;
1842 g_out_rec_tab(g_out_rec_count).base_date_type := main_rec.base_date_type;
1843 g_out_rec_tab(g_out_rec_count).payment_schedule_id := main_rec.payment_schedule_id;
1844 g_out_rec_tab(g_out_rec_count).charge_type := main_rec.charge_type;
1845
1846 IF (main_rec.rate_type = 'CVFR' AND l_cvf_rate IS NOT NULL) THEN
1847 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1848 debug(l_module_name,'Changing the rates to CVFR');
1849 END IF;
1850 main_rec.rate_flag := 'Y';
1851 main_rec.rate_amount := l_cvf_rate/100;
1852 END IF;
1853
1854 LOOP
1855 l_finance_charges := 0;
1856 IF (NVL(l_old_invoice_id, -999) <> main_rec.invoice_id) THEN
1857 BEGIN
1858 SELECT SUM(aps.amount_due_original),
1859 SUM(aps.amount_due_remaining)
1860 INTO l_inv_amount_due_original,
1861 l_inv_amount_due_remaining
1862 FROM ar_payment_schedules aps
1863 WHERE aps.customer_trx_id = main_rec.invoice_id;
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 p_retcode := g_FAILURE;
1867 p_errbuf := SQLERRM;
1868 l_location := l_module_name||'.select_ar_payment_schedules';
1869 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1870 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1871 END;
1872 END IF;
1873
1874 IF (NVL(l_old_invoice_id, -999) = main_rec.invoice_id AND
1875 NVL(l_old_charge_id , -999) = main_rec.charge_id AND
1876 main_rec.accrue_at_invoice = 'Y') THEN
1877 l_location := '';
1878 l_message := 'Finance charges to be accrued at invoice level ,so skipping this payment schedule';
1879 g_out_rec_tab(g_out_rec_count).comments := l_message;
1880 log (l_location, l_message);
1881 EXIT;
1882 ELSIF (main_rec.accrual_interval = 0 AND main_rec.last_accrual_date IS NOT NULL) THEN
1883 l_location := '';
1884 l_message := 'Finance charges already accrued, so skipping this payment schedule';
1885 g_out_rec_tab(g_out_rec_count).comments := l_message;
1886 log (l_location, l_message);
1887 EXIT;
1888
1889 -- ELSIF (main_rec.last_accrual_date + main_rec.accrual_interval >= l_accrue_as_of_date) THEN
1890 ELSIF (main_rec.last_accrual_date + main_rec.accrual_interval > l_accrue_as_of_date) THEN
1891
1892 l_location := '';
1893 l_message := 'Accrual process already ran for today, so skipping';
1894 g_out_rec_tab(g_out_rec_count).comments := l_message;
1895 log (l_location, l_message);
1896 EXIT;
1897 ELSE
1898 /*
1899 Rate Type is valid only for Rate% and not for Amount
1900 But when Rate Type is FLAT, a flat charge is calculated based on the
1901 percentage. So it is equivalent to Amount based with
1902 rate * amount due. We have to convert this to rate_flag as N and put
1903 an amount to calculate for every missing period.
1904 */
1905 IF (main_rec.rate_flag = 'Y' AND main_rec.rate_base = 'FLAT') THEN
1906 main_rec.rate_flag := 'N';
1907 main_rec.rate_amount := main_rec.rate_amount * main_rec.amount_due_remaining;
1908 END IF;
1909
1910 IF (main_rec.rate_flag = 'Y') THEN
1911 IF (main_rec.last_accrual_date IS NULL) THEN
1912 IF (main_rec.base_date_type = 'INVOICE') THEN
1913 l_accrual_date := main_rec.trx_date;
1914 ELSE
1915 l_accrual_date := main_rec.due_date;
1916 END IF;
1917 ELSE
1918 l_accrual_date := main_rec.last_accrual_date;
1919 END IF;
1920
1921 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1922 debug(l_module_name,'Calling accrue_charges');
1923 END IF;
1924 -- l_first_late_day := trunc(main_rec.due_date) + main_rec.first_accrual + main_rec.grace_period + 1;
1925 g_out_rec_tab(g_out_rec_count).accrual_date := l_accrual_date;
1926 accrue_charges
1927 (
1928 p_accrue_as_of_date => l_accrue_as_of_date,
1929 p_last_date => l_accrual_date,
1930 p_customer_trx_id => main_rec.invoice_id,
1931 p_amount_due_remaining => main_rec.amount_due_remaining,
1932 p_rate_amount => main_rec.rate_amount,
1933 p_interval => main_rec.accrual_interval,
1934 p_charges => l_finance_charges,
1935 p_errbuf => p_errbuf,
1936 p_retcode => p_retcode
1937 );
1938 g_out_rec_tab(g_out_rec_count).finance_charges := l_finance_charges;
1939
1940 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1941 debug(l_module_name,'accrue_charges return code = '||p_retcode);
1942 debug(l_module_name,'accrue_charges return buf = '||p_errbuf);
1943 END IF;
1944
1945 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
1946 debug(l_module_name,'l_finance_charges='||l_finance_charges);
1947 END IF;
1948
1949 ELSE
1950
1951 --l_first_late_day := trunc(main_rec.due_date) + main_rec.first_accrual + main_rec.grace_period + 1;
1952 l_first_late_day := trunc(main_rec.due_date) + main_rec.first_accrual + main_rec.grace_period;
1953
1954 g_out_rec_tab(g_out_rec_count).first_late_date := l_first_late_day;
1955 missed_intervals
1956 (
1957 p_accrue_as_of_date => l_accrue_as_of_date,
1958 p_interval => main_rec.accrual_interval,
1959 p_last_accrual_date => main_rec.last_accrual_date,
1960 p_first_late_day => l_first_late_day,
1961 p_number_of_periods => l_number_of_periods,
1962 p_errbuf => p_errbuf,
1963 p_retcode => p_retcode
1964 );
1965 g_out_rec_tab(g_out_rec_count).number_of_periods := l_number_of_periods;
1966 IF (p_retcode = g_SUCCESS) THEN
1967 l_finance_charges := main_rec.rate_amount * l_number_of_periods;
1968 g_out_rec_tab(g_out_rec_count).comments := 'Finance Charges = '||main_rec.rate_amount||'*'||l_number_of_periods||'='||l_finance_charges;
1969 END IF;
1970 g_out_rec_tab(g_out_rec_count).finance_charges := l_finance_charges;
1971
1972 END IF;
1973
1974
1975 IF (p_retcode = g_SUCCESS) THEN
1976 IF (l_finance_charges <> 0) THEN
1977 process_and_create_dm
1978 (
1979 p_org_id => main_rec.org_id,
1980 p_set_of_books_id => main_rec.set_of_books_id,
1981 p_accrue_as_of_date => l_accrue_as_of_date,
1982 p_finance_charges => l_finance_charges,
1983 p_invoice_id => main_rec.invoice_id,
1984 p_customer_id => main_rec.customer_id,
1985 p_trx_number => main_rec.trx_number,
1986 p_trx_date => main_rec.trx_date,
1987 p_charge_id => main_rec.charge_id,
1988 p_finance_charge_group_hdr_id => main_rec.finance_charge_group_hdr_id,
1989 p_finance_charge_group_dtl_id => main_rec.finance_charge_group_dtl_id,
1990 p_invoice_currency_code => main_rec.invoice_currency_code,
1991 p_trx_type_id => main_rec.trx_type_id,
1992 p_gl_date => l_gl_date,
1993 p_bill_to_customer_id => main_rec.bill_to_customer_id,
1994 p_bill_to_contact_id => main_rec.bill_to_contact_id,
1995 p_bill_to_address_id => main_rec.bill_to_address_id,
1996 p_invoice_due_date => main_rec.due_date,
1997 p_exchange_date => main_rec.exchange_date,
1998 p_exchange_rate => main_rec.exchange_rate,
1999 p_exchange_rate_type => main_rec.exchange_rate_type,
2000 p_root_invoice_id => main_rec.root_invoice_id,
2001 p_invoice_date_type => p_invoice_date_type,
2002 p_immediate_term_id => l_immediate_term_id,
2003 p_invoice_term_id => main_rec.term_id,
2004 p_gl_id_rec => main_rec.gl_id_rec,
2005 p_gl_id_rev => main_rec.gl_id_rev,
2006 p_prorate_charge => main_rec.prorate_charge,
2007 p_batch_source_id => main_rec.batch_source_id,
2008 p_inv_amount_due_remaining => l_inv_amount_due_remaining,
2009 p_inv_amount_due_original => l_inv_amount_due_original,
2010 p_charge_type =>main_rec.charge_type,
2011 p_invoice_suffix => main_rec.invoice_suffix,
2012 p_errbuf => p_errbuf,
2013 p_retcode => p_retcode
2014 );
2015
2016 IF (p_retcode = g_SUCCESS) THEN
2017 set_last_accrual_date
2018 (
2019 p_org_id => main_rec.org_id,
2020 p_payment_schedule_id => main_rec.payment_schedule_id,
2021 p_charge_type => main_rec.charge_type,
2022 p_last_accrual_date => l_accrue_as_of_date,
2023 p_errbuf => p_errbuf,
2024 p_retcode => p_retcode
2025 );
2026 END IF;
2027
2028 END IF;
2029 END IF;
2030
2031 END IF;
2032
2033 l_old_invoice_id := main_rec.invoice_id;
2034 l_old_charge_id := main_rec.charge_id;
2035 EXIT;
2036 END LOOP;
2037 END IF;
2038 IF (p_retcode <> g_SUCCESS) THEN
2039 EXIT;
2040 END IF;
2041 END LOOP;
2042
2043 write_output;
2044
2045 IF (p_retcode = g_SUCCESS) THEN
2046 COMMIT;
2047 submit_report
2048 (
2049 p_errbuf => p_errbuf,
2050 p_retcode => p_retcode
2051 );
2052 ELSE
2053 ROLLBACK;
2054 END IF;
2055
2056
2057 END IF;
2058
2059 EXCEPTION
2060 WHEN OTHERS THEN
2061 p_retcode := g_FAILURE;
2062 p_errbuf := SQLERRM;
2063 l_location := l_module_name||'.final_exception';
2064 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2065 fv_utility.log_mesg(fnd_log.level_unexpected, l_location, p_errbuf);
2066 ROLLBACK;
2067 END;
2068
2069
2070 /*****************************************************************************/
2071 /* Begin Assign Finance Charges Process */
2072 /*****************************************************************************/
2073 PROCEDURE assign_finance_charge
2074 (
2075 p_errbuf OUT NOCOPY VARCHAR2,
2076 p_retcode OUT NOCOPY NUMBER
2077 ) IS
2078 l_module_name VARCHAR2(200);
2079 l_location VARCHAR2(200);
2080 l_currency_code gl_ledgers.currency_code%TYPE;
2081 l_req_id NUMBER;
2082
2083 CURSOR c1 (c_ledger_id NUMBER) IS
2084 SELECT DISTINCT hzca.cust_Account_id customer_id,
2085 hzca.customer_class_code cust_class_code
2086 FROM hz_cust_accounts hzca,
2087 fv_finance_charge_controls fcc
2088 WHERE fcc.enabled_flag = 'Y'
2089 AND fcc.set_of_books_id = c_ledger_id
2090 AND hzca.status = 'A'
2091 AND EXISTS (SELECT 'x'
2092 FROM fv_cust_finance_chrgs
2093 WHERE hzca.cust_account_id = customer_id
2094 AND fcc.charge_id = charge_id
2095 AND set_of_books_id = c_ledger_id)
2096 AND NOT EXISTS (SELECT 'x'
2097 FROM fv_finance_chrg_cust_classes fccc,
2098 fv_finance_charge_grp_dtls fcgd
2099 WHERE fccc.customer_class = hzca.customer_class_code
2100 AND fccc.enabled_flag = 'Y'
2101 AND fccc.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
2102 AND SYSDATE between NVL(fcgd.start_date, g_lo_date) and NVL(fcgd.end_date, g_hi_date)
2103 AND fcgd.assessed_charge_id = fcc.charge_id
2104 AND set_of_books_id = c_ledger_id);
2105 BEGIN
2106 l_module_name := g_module_name || 'assign_finance_charge';
2107 p_retcode := g_SUCCESS;
2108 p_errbuf := null;
2109
2110 IF (fnd_log.level_procedure >= g_CURRENT_LOG_LEVEL) THEN
2111 debug(l_module_name,g_enter);
2112 END IF;
2113
2114
2115 BEGIN
2116 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
2117 debug(l_module_name,'g_set_of_books_id='||g_set_of_books_id);
2118 END IF;
2119
2120 SELECT currency_code
2121 INTO l_currency_code
2122 FROM gl_ledgers_public_v
2123 WHERE ledger_id = g_set_of_books_id;
2124
2125 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
2126 debug(l_module_name,'l_currency_code='||l_currency_code);
2127 END IF;
2128
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 p_retcode := g_FAILURE;
2132 p_errbuf := SQLERRM;
2133 l_location := l_module_name||'.select_gl_ledgers_public_v';
2134 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2135 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2136 END;
2137
2138 IF (p_retcode = g_SUCCESS) THEN
2139 FOR customer in c1 (g_set_of_books_id)
2140 LOOP
2141 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
2142 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating waive flag for: '||customer.customer_id);
2143 END IF;
2144
2145 BEGIN
2146
2147
2148 -- commented the below UPDATE statement and added 3new UPDATE statements
2149
2150
2151 /* UPDATE fv_cust_finance_chrgs
2152 SET waive_flag = 'Y'
2153 WHERE customer_id = customer.customer_id
2154 AND charge_id NOT IN (SELECT charge_id
2155 FROM fv_finance_chrg_cust_classes,
2156 hz_cust_accounts hzca
2157 WHERE hzca.cust_account_id = customer.customer_id
2158 AND customer_class = hzca.customer_class_code
2159 AND enabled_flag = 'Y'
2160 AND set_of_books_id = g_set_of_books_id);*/
2161
2162 /* Commented for bug 9089029
2163 UPDATE fv_cust_finance_chrgs a
2164 SET CUSTOMER_CLASS_CODE = customer.cust_class_code,
2165 finance_charge_group_hdr_id = (SELECT finance_charge_group_hdr_id
2166 FROM fv_finance_chrg_cust_classes b
2167 WHERE customer_class= customer.cust_class_code
2168 AND a.set_of_books_id = b.set_of_books_id
2169 AND rownum =1)
2170 WHERE EXISTS(SELECT 'A'
2171 FROM fv_finance_chrg_cust_classes c
2172 WHERE customer_class= customer.cust_class_code
2173 AND a.set_of_books_id = c.set_of_books_id)
2174 AND customer_id = customer.customer_id;
2175 */
2176
2177 UPDATE fv_cust_finance_chrgs
2178 SET waive_flag = 'Y'
2179 WHERE customer_id = customer.customer_id
2180 AND charge_id NOT IN (SELECT fcf.charge_id
2181 FROM fv_cust_finance_chrgs fcf,
2182 fv_finance_chrg_cust_classes fcfc,
2183 hz_cust_accounts hzca,
2184 fv_finance_charge_grp_hdrs fcgh,
2185 fv_finance_charge_grp_dtls fcgd
2186 WHERE hzca.cust_account_id = customer.customer_id
2187 AND fcf.customer_id = hzca.cust_account_id
2188 AND fcf.customer_class_code = fcfc.customer_class
2189 AND fcf.customer_class_code = hzca.customer_class_code
2190 AND fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
2191 AND fcgh.finance_charge_group_hdr_id = fcf.finance_charge_group_hdr_id
2192 AND fcgh.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
2193 AND fcf.charge_id = fcgd.assessed_charge_id
2194 AND fcfc.enabled_flag = 'Y'
2195 AND fcf.set_of_books_id =g_set_of_books_id
2196 AND fcgd.start_date <= sysdate
2197 AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
2198 AND fcgd.base_charge_id = 0);
2199
2200 UPDATE fv_cust_finance_chrgs
2201 SET waive_flag = 'N'
2202 WHERE customer_id = customer.customer_id
2203 AND charge_id IN (SELECT fcf.charge_id
2204 FROM fv_cust_finance_chrgs fcf,
2205 fv_finance_chrg_cust_classes fcfc,
2206 hz_cust_accounts hzca,
2207 fv_finance_charge_grp_hdrs fcgh,
2208 fv_finance_charge_grp_dtls fcgd
2209 WHERE hzca.cust_account_id = customer.customer_id
2210 AND fcf.customer_id = hzca.cust_account_id
2211 AND fcf.customer_class_code = fcfc.customer_class
2212 AND fcf.customer_class_code = hzca.customer_class_code
2213 AND fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
2214 AND fcgh.finance_charge_group_hdr_id = fcf.finance_charge_group_hdr_id
2215 AND fcgh.finance_charge_group_hdr_id = fcgd.finance_charge_group_hdr_id
2216 AND fcf.charge_id = fcgd.assessed_charge_id
2217 AND fcfc.enabled_flag = 'Y'
2218 AND fcf.set_of_books_id =g_set_of_books_id
2219 AND fcgd.start_date <= sysdate
2220 AND decode(fcgd.end_date,null,sysdate,fcgd.end_date) >= sysdate
2221 AND fcgd.base_charge_id = 0);
2222
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225 p_retcode := g_FAILURE;
2226 p_errbuf := SQLERRM;
2227 l_location := l_module_name||'.update_fv_cust_finance_chrgs';
2228 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2229 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2230 END;
2231 END LOOP;
2232 END IF;
2233
2234 IF (p_retcode = g_SUCCESS) THEN
2235
2236 -- Bug 13042490: ORA-00001: unique constraint (FV.FV_CUST_FINANANCE_CHRGS_U1)
2237 -- violated occurs when the customer class code is changed and both new
2238 -- and old customer classes are assigned to the same finance charge group.
2239 -- To avoid this error, delete the previous customer finance charge
2240 -- assignment.
2241 BEGIN
2242 DELETE FROM fv_cust_finance_chrgs WHERE
2243 customer_id IN
2244 (SELECT hzca.cust_account_id
2245 FROM hz_cust_accounts hzca,
2246 fv_finance_charge_controls fcc,
2247 fv_finance_chrg_cust_classes fccc,
2248 fv_finance_charge_grp_dtls fcgd,
2249 fv_finance_charge_grp_hdrs fcgh
2250 WHERE fcc.enabled_flag = 'Y'
2251 AND fcc.set_of_books_id = g_set_of_books_id
2252 AND hzca.status = 'A'
2253 AND fccc.customer_class = hzca.customer_class_code
2254 AND fccc.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
2255 AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
2256 AND fcgh.enabled_flag = 'Y'
2257 AND fcgd.enabled_flag = 'Y'
2258 AND fcgd.assessed_charge_id = fcc.charge_id
2259 AND fccc.set_of_books_id = g_set_of_books_id
2260 AND fcgh.ledger_id = g_set_of_books_id
2261 AND fccc.enabled_flag = 'Y'
2262 AND fcgd.base_charge_id = 0
2263 AND EXISTS (SELECT 'x'
2264 FROM fv_cust_finance_chrgs_all fcfc
2265 WHERE hzca.cust_account_id = fcfc.customer_id
2266 AND fcc.charge_id = fcfc.charge_id
2267 AND fcgd.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
2268 AND fcfc.customer_class_code <> fccc.customer_class
2269 AND fcfc.set_of_books_id = g_set_of_books_id));
2270 EXCEPTION
2271 WHEN OTHERS THEN
2272 p_retcode := g_FAILURE;
2273 p_errbuf := SQLERRM;
2274 l_location := l_module_name||'.delete_fv_cust_finance_chrgs';
2275 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2276 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2277 END;
2278
2279 BEGIN
2280 INSERT INTO fv_cust_finance_chrgs
2281 (
2282 customer_id,
2283 charge_id,
2284 waive_flag,
2285 created_by,
2286 creation_date,
2287 last_updated_by,
2288 last_update_date,
2289 set_of_books_id,
2290 org_id,
2291 enabled_flag,
2292 customer_class_code,
2293 finance_charge_group_hdr_id,
2294 base_charge_id,
2295 request_id
2296 )
2297 SELECT hzca.cust_account_id,
2298 fcgd.assessed_charge_id,
2299 'N',
2300 g_user_id,
2301 SYSDATE,
2302 g_user_id,
2303 SYSDATE,
2304 g_set_of_books_id,
2305 fcc.org_id,
2306 'Y',
2307 fccc.customer_class,
2308 fcgh.finance_charge_group_hdr_id,
2309 fcgd.base_charge_id,
2310 g_request_id
2311 FROM hz_cust_accounts hzca,
2312 fv_finance_charge_controls fcc,
2313 fv_finance_chrg_cust_classes fccc,
2314 fv_finance_charge_grp_dtls fcgd,
2315 fv_finance_charge_grp_hdrs fcgh
2316 WHERE fcc.enabled_flag = 'Y'
2317 AND fcc.set_of_books_id = g_set_of_books_id
2318 AND hzca.status = 'A'
2319 AND fccc.customer_class = hzca.customer_class_code
2320 AND fccc.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
2321 AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
2322 AND fcgh.enabled_flag = 'Y'
2323 AND fcgd.enabled_flag = 'Y'
2324 AND fcgd.assessed_charge_id = fcc.charge_id
2325 AND fccc.set_of_books_id = g_set_of_books_id
2326 AND fcgh.ledger_id = g_set_of_books_id
2327 AND fccc.enabled_flag = 'Y'
2328 AND fcgd.base_charge_id = 0
2329 AND NOT EXISTS (SELECT 'x'
2330 FROM fv_cust_finance_chrgs fcfc
2331 WHERE hzca.cust_account_id = fcfc.customer_id
2332 AND fcc.charge_id = fcfc.charge_id
2333 AND fcgd.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
2334 and fcfc.customer_class_code = fccc.customer_class
2335 AND fcfc.set_of_books_id = g_set_of_books_id);
2336 EXCEPTION
2337 WHEN OTHERS THEN
2338 p_retcode := g_FAILURE;
2339 p_errbuf := SQLERRM;
2340 l_location := l_module_name||'.insert_fv_cust_finance_chrgs';
2341 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2342 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2343 END;
2344 END IF;
2345
2346 IF (p_retcode = g_SUCCESS) THEN
2347 BEGIN
2348 /* Bug 13035171. Replaced clause AND rctt.type IN ('DM','INV') with
2349 AND( rctt.type ='INV' OR (rctt.type ='DM' and not exists
2350 (SELECT 'X' FROM ra_customer_trx_lines_all ral
2351 WHERE description = 'Accrue Federal Finance Charges'
2352 AND ral.customer_trx_id = ract.customer_trx_id )))
2353 in both insert statements below. Assign Finance Charges report should display
2354 finance charges assigned to invoices and debit memos not created by the Accrue
2355 Finance charge process(AcFC) only. It should not display the charges assigned
2356 to debit memos created using AcFC process */
2357
2358 INSERT INTO fv_ar_fin_chrg_invoices
2359 (
2360 org_id,
2361 customer_id,
2362 invoice_id,
2363 invoice_number,
2364 invoice_date,
2365 invoice_amount,
2366 finance_charges,
2367 waive_flag,
2368 enabled_flag,
2369 parent_invoice_id,
2370 root_invoice_id,
2371 last_accrual_date,
2372 last_update_date,
2373 last_updated_by,
2374 created_by,
2375 creation_date,
2376 last_update_login,
2377 request_id
2378 )
2379 SELECT ract.org_id,
2380 ract.bill_to_customer_id,
2381 ract.customer_trx_id,
2382 ract.trx_number,
2383 ract.trx_date,
2384 0, --invoice amount
2385 0, --finance charges
2386 'N',
2387 'Y',
2388 0,
2389 0,
2390 NULL,
2391 SYSDATE,
2392 g_user_id,
2393 g_user_id,
2394 SYSDATE,
2395 g_login_id,
2396 g_request_id
2397 FROM ra_customer_trx ract,
2398 ra_cust_trx_types rctt
2399 WHERE ract.cust_trx_type_id = rctt.cust_trx_type_id
2400 AND ract.complete_flag = 'Y'
2401 --AND rctt.type IN ('DM','INV')
2402 AND( rctt.type ='INV' OR (rctt.type ='DM' AND NOT EXISTS
2403 ( SELECT 'X' FROM ra_customer_trx_lines_all ral
2404 WHERE description = 'Accrue Federal Finance Charges'
2405 AND ral.customer_trx_id = ract.customer_trx_id )))
2406
2407 /* AND NVL(ract.interface_header_attribute3,'XX') NOT IN (SELECT charge_type
2408 FROM fv_finance_charge_controls
2409 WHERE set_of_books_id = g_set_of_books_id)*/
2410
2411 AND EXISTS (SELECT 'x'
2412 FROM fv_cust_finance_chrgs fcfc
2413 WHERE ract.bill_to_customer_id = fcfc.customer_id
2414 AND fcfc.enabled_flag = 'Y'
2415 AND fcfc.waive_flag = 'N')
2416 AND NOT EXISTS (SELECT 'x'
2417 FROM fv_ar_fin_chrg_invoices fai
2418 WHERE ract.customer_trx_id = fai.invoice_id
2419 AND ract.bill_to_customer_id=fai.customer_id);
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 p_retcode := g_FAILURE;
2423 p_errbuf := SQLERRM;
2424 l_location := l_module_name||'.insert_fv_cust_finance_chrgs';
2425 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2426 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2427 END;
2428 END IF;
2429 IF (p_retcode = g_SUCCESS) THEN
2430 BEGIN
2431 INSERT INTO fv_invoice_finance_chrgs
2432 (
2433 customer_id,
2434 customer_trx_id,
2435 charge_id,
2436 waive_flag,
2437 created_by,
2438 creation_date,
2439 last_updated_by,
2440 last_update_date,
2441 set_of_books_id,
2442 org_id,
2443 request_id,
2444 base_charge_id,
2445 finance_charge_group_hdr_id,
2446 finance_charge_group_dtl_id,
2447 enabled_flag
2448 )
2449 SELECT ract.bill_to_customer_id,
2450 ract.customer_trx_id,
2451 fcgd.assessed_charge_id,
2452 fcfc.waive_flag,
2453 g_user_id,
2454 SYSDATE,
2455 g_user_id,
2456 SYSDATE,
2457 g_set_of_books_id,
2458 fcfc.org_id,
2459 g_request_id,
2460 fcgd.base_charge_id,
2461 fcgh.finance_charge_group_hdr_id,
2462 fcgd.finance_charge_group_dtl_id,
2463 'Y'
2464 FROM ra_customer_trx ract,
2465 fv_cust_finance_chrgs fcfc,
2466 fv_finance_charge_controls fcc,
2467 ra_cust_trx_types rctt,
2468 fv_finance_charge_grp_dtls fcgd,
2469 fv_finance_charge_grp_hdrs fcgh
2470 WHERE ract.bill_to_customer_id = fcfc.customer_id
2471 AND fcfc.set_of_books_id = g_set_of_books_id
2472 AND ract.cust_trx_type_id = rctt.cust_trx_type_id
2473 AND ract.complete_flag = 'Y'
2474 AND fcc.enabled_flag = 'Y'
2475 AND fcc.charge_id = fcfc.charge_id
2476 AND fcgh.finance_charge_group_hdr_id = fcfc.finance_charge_group_hdr_id
2477 AND fcfc.enabled_flag = 'Y'
2478 AND fcfc.waive_flag = 'N'
2479 --AND rctt.type IN ('DM','INV')
2480 AND( rctt.type ='INV' OR (rctt.type ='DM' AND NOT EXISTS
2481 ( SELECT 'X' FROM ra_customer_trx_lines_all ral
2482 WHERE description = 'Accrue Federal Finance Charges'
2483 AND ral.customer_trx_id = ract.customer_trx_id )))
2484 AND fcgd.finance_charge_group_hdr_id = fcgh.finance_charge_group_hdr_id
2485 AND fcgh.enabled_flag = 'Y'
2486 AND fcgd.enabled_flag = 'Y'
2487 AND fcgd.assessed_charge_id = fcc.charge_id
2488 AND fcgd.base_charge_id = 0
2489 AND fcgh.ledger_id = g_set_of_books_id
2490
2491 /* AND NVL(ract.interface_header_attribute3,'XX') NOT IN (SELECT charge_type
2492 FROM fv_finance_charge_controls
2493 WHERE set_of_books_id = g_set_of_books_id)*/
2494
2495 AND NOT EXISTS (SELECT 'x'
2496 FROM fv_invoice_finance_chrgs fifc
2497 WHERE ract.customer_trx_id = fifc.customer_trx_id
2498 AND ract.bill_to_customer_id=fifc.customer_id
2499 AND fcgd.assessed_charge_id = fifc.charge_id
2500 AND set_of_books_id = g_set_of_books_id);
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 p_retcode := g_FAILURE;
2504 p_errbuf := SQLERRM;
2505 l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';
2506 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2507 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
2508 END;
2509 END IF;
2510
2511 IF (p_retcode = g_SUCCESS) THEN
2512 fnd_request.set_org_id(g_org_id); -- PSKI MOAC Changes
2513
2514 l_req_id := fnd_request.submit_request
2515 (
2516 application => 'FV',
2517 program => 'FVDCAFCO',
2518 description => '',
2519 start_time => '',
2520 sub_request => FALSE,
2521 argument1 => g_ledger_name,
2522 argument2 => l_currency_code,
2523 argument3 => g_request_id
2524 );
2525
2526 IF (fnd_log.level_statement >= g_CURRENT_LOG_LEVEL) THEN
2527 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_req_id = '||l_req_id);
2528 END IF;
2529
2530
2531 IF l_req_id = 0 THEN
2532 p_retcode := g_FAILURE;
2533 p_errbuf := 'Submitting the concurrent process, FVDCAFCO, failed contact System Admin.';
2534 fv_utility.log_mesg(fnd_log.level_error, l_module_name, p_errbuf);
2535 END IF;
2536 END IF;
2537
2538 COMMIT;
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 p_retcode := g_FAILURE;
2542 p_errbuf := SQLERRM;
2543 l_location := l_module_name||'.insert_fv_invoice_finance_chrgs';
2544 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
2545 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_errbuf);
2546 ROLLBACK;
2547 END assign_finance_charge;
2548 BEGIN
2549 initialize_global_variables;
2550 END fv_dc_assign_finance_chrg;