[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.19.12000000.4 2007/09/17 16:36:31 sasukuma ship $ */
3
4 /*****************************************************************************/
5 /***** Variable Declaration For All Processes ****/
6 /*****************************************************************************/
7 g_module_name VARCHAR2(100) ;
8 statement_no varchar2(100);
9
10 /***** Variable Declaration for Accrue Finance Charges Process **************/
11
12
13 ORACLE_ERROR exception;
14 start_time date;
15 run_today date;
16 sob_run_today date;
17 last_accrual_date date;
18 set_of_books_id ra_customer_trx.set_of_books_id%type;
19 customer_trx_id ra_customer_trx.customer_trx_id%type;
20 trx_number ra_customer_trx.trx_number%type;
21 due_date date;
22 rate_amount number;
23 accrual_type varchar2(1);
24 frequency varchar2(1);
25 rate_flag varchar2(1);
26 charge_id fv_finance_charge_controls.charge_id%type;
27 description fv_finance_charge_controls.description%type;
28 charge_type fv_finance_charge_controls.charge_type%type;
29 trx_type_id fv_finance_charge_controls.trx_type_id%type;
30 first fv_finance_charge_controls.first_accrual%type;
31 interval fv_finance_charge_controls.accrual_interval%type;
32 grace fv_finance_charge_controls.grace_period%type;
33 suffix fv_finance_charge_controls.invoice_suffix%type;
34 -- TC Obsoletion
35 -- ussgl_transaction_code fv_finance_charge_controls.ussgl_transaction_code%type;
36 fin_chrg number;
37 dm_flag boolean;
38 first_late_day date;
39 found boolean;
40 current_pd date;
41 last_pd date;
42 fixed_amount number;
43 num_periods number :=0;
44 error_message varchar2(500);
45 term_id number;
46 sold_to_customer_id number;
47 batch_source_id number;
48 sold_to_site_use_id number;
49 bill_to_customer_id number;
50 bill_to_site_use_id number;
51 ship_to_customer_id number;
52 ship_to_site_use_id number;
53 remit_to_address_id number;
54 bill_to_address_id number;
55 ship_to_address_id number;
56 customer_site_use_id number;
57 trx_s number;
58 trx_lines_s number;
59 trx_line_gl_dist_rec_s number;
60 trx_line_gl_dist_rev_s number;
61 payment_schedules_s number;
62 rev_ccid number;
63 rec_ccid number;
64 amount_due_original ar_payment_schedules.amount_due_original%type;
65 amount_due_remaining ar_payment_schedules.amount_due_remaining%type;
66 schedule_id number;
67 rec_trx_id number;
68 adjust_ccid number;
69 existing_trx_id number;
70 invoice_id number;
71 v_mfar_options varchar2(1);
72 v_adjustment_id number;
73 v_adjustment_dist_id number;
74 v_mfar_rec_ccid number;
75 v_write_off_ccid number;
76 v_payment_schedule_dist_id number;
77 p_error_code number;
78 p_error_mesg varchar2(200);
79 v_adjustment_ccid number;
80 -- Added New Variable Invoice_Date March 30, 1999 BG
81 --v_invoice_date Date;
82 adjust_amount number ; -- Added a new variable to store the adjust amount
83 v_conc_req_id number; -- to store req id for Bug# 2157100
84 v_old_sch_id NUMBER;
85 v_invoice_level varchar2(1);
86 v_insert_flag varchar2(1):='N';
87 v_old_ctrx_id ra_customer_trx.customer_trx_id%type;
88 v_int_zero NUMBER;
89
90
91 v_inv_date_type VARCHAR2(10);
92
93 v_inv_due_date VARCHAR2(3);
94
95 v_gl_date DATE ;
96 v_trx_date DATE ;
97
98
99 /*
100 -- declare array for v_auto_id
101 TYPE autoidarray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
102 v_auto_id autoidarray;
103 */
104
105 fc_dm_rec RA_INTERFACE_LINES_ALL%ROWTYPE;
106 fc_dm_dist_rec RA_INTERFACE_DISTRIBUTIONS%ROWTYPE;
107 fin_charge_batch_source_id number;
108 invoice_currency_code RA_CUSTOMER_TRX.INVOICE_CURRENCY_CODE%TYPE;
109 bill_to_contact_id RA_CUSTOMER_TRX.BILL_TO_CONTACT_ID%TYPE;
110 v_exchange_date RA_CUSTOMER_TRX.EXCHANGE_DATE%TYPE;
111 v_exchange_rate RA_CUSTOMER_TRX.EXCHANGE_RATE%TYPE;
112 v_exchange_rate_type RA_CUSTOMER_TRX.EXCHANGE_RATE_TYPE%TYPE;
113 v_interface_record varchar2(1);
114 v_user_id number;
115 v_login_id number;
116 v_conc_program_id number;
117 v_conc_info boolean;
118 v_request_id number;
119 v_auto_id number;
120 process_status boolean;
121 v_split_term boolean;
122 v_line_count number;
123
124 cursor main_select(cv_sob_id ra_customer_trx.set_of_books_id%TYPE) is
125 select aps.customer_trx_id,
126 aps.amount_due_original,
127 aps.amount_due_remaining,
128 aps.payment_schedule_id,
129 aps.customer_site_use_id,
130 rct.set_of_books_id,
131 fcc.charge_id,
132 fcc.charge_type,
133 fcc.description,
134 fcc.batch_source_id,
135 aps.trx_number,
136 aps.due_date,
137 nvl(amount, nvl(rate,0)/100),
138 substr(fch.accrual_type,1,1),
139 substr(fch.frequency,1,1),
140 decode(amount, NULL, 'Y', 'N'),
141 nvl(fcc.accrue_at_invoice,'N'),
142 fcc.trx_type_id ,
143 fcc.first_accrual ,
144 fcc.accrual_interval ,
145 fcc.grace_period ,
146 fcc.invoice_suffix,
147 -- TC Obsoletion
148 -- fcc.ussgl_transaction_code,
149 fcc.receivables_trx_id, --Added as part of Enahncement #1957297
150 rct.sold_to_customer_id,
151 rct.sold_to_site_use_id,
152 rct.bill_to_customer_id,
153 rct.bill_to_site_use_id,
154 rct.ship_to_customer_id,
155 rct.ship_to_site_use_id,
156 rct.remit_to_address_id,
157 /* ----- changed
158 rct.bill_to_address_id,
159 */
160 rct.bill_to_site_use_id,
161 rct.ship_to_address_id,
162 rct.bill_to_contact_id,
163 rct.invoice_currency_code,
164 rct.exchange_date,
165 rct.exchange_rate,
166 rct.exchange_rate_type ,
167 aps.trx_date --Added for Bug 3018578
168 -- Added extra column to get the Trx_date(invoice_data) March 30, 1999 BG
169 -- rct.trx_date
170 -- bug 992498 commented out rct.trx_date
171 from ar_payment_schedules aps,
172 fv_invoice_finance_chrgs fifc,
173 fv_finance_charge_controls fcc,
174 fv_finance_charge_history fch,
175 ra_customer_trx rct,
176 hz_cust_accounts hzca
177 where trunc(sysdate) > (due_date + first_accrual + grace_period + 1)
178 and waive_flag = 'N'
179 and aps.amount_due_remaining > 0
180 and aps.status <> 'CL'
181 and aps.customer_trx_id = rct.customer_trx_id
182 and nvl(rct.interface_header_attribute3,'XX') not in
183 (select charge_type from fv_finance_charge_controls)
184 and rct.set_of_books_id = cv_sob_id
185 and aps.customer_trx_id = fifc.customer_trx_id
186 and fifc.charge_id = fcc.charge_id
187 and fcc.charge_id = fch.charge_id
188 and aps.trx_date between start_date and nvl(end_date,
189 to_date('31-12-4712','DD-MM-YYYY'))
190 and fcc.enabled_flag = 'Y'
191 and hzca.cust_account_id = rct.bill_to_customer_id
192 and exists (select 'x' from fv_finance_chrg_cust_classes
193 where customer_class = hzca.customer_class_code
194 and enabled_flag = 'Y'
195 and charge_id = fcc.charge_id
196 and set_of_books_id = cv_sob_id)
197 order by aps.payment_schedule_id;
198
199
200 cursor c_last_run is
201 select distinct last_accrual_date
202 from fv_ar_controls
203 where last_accrual_date = trunc(sysdate);
204
205 cursor c_last_run_sob(cv_sob_id fv_ar_controls.payment_schedule_id%TYPE) is
206 select fac.last_accrual_date
207 from ar_payment_schedules aps,
208 fv_ar_controls fac,
209 ra_customer_trx rct
210 where aps.payment_schedule_id = fac.payment_schedule_id
211 and rct.customer_trx_id = aps.customer_trx_id
212 and rct.set_of_books_id = cv_sob_id
213 and fac.last_accrual_date = trunc(sysdate);
214
215
216 -- cursor selects batch source names for submitting autoinvoice for
217 -- multiple batch sources
218 cursor c_batch_source_id is
219 select batch_source_id, name
220 from ra_batch_sources
221 where name in (select distinct batch_source_name
222 from ra_interface_lines
223 where interface_line_context = 'Accrue Finance Charges'
224 and interface_line_id not in (select interface_line_id
225 from ra_interface_errors));
226
227 /*****************************************************************************/
228 /* Variable Declarations For Assign Finance Charges */
229 /*****************************************************************************/
230
231 sob_id number; -- set of books id
232 v_org_id number; -- organization id
233 assign_date date; -- This will map to the creation_date field.
234 -- Used for output report.
235
236 /****************************************************************************/
237
238
239 /* This function calculates the finance charge to be added to the invoice -
240 Called if the finance charge is a rate */
241
242 function accrue_charges (last_date date) return number is
243 l_module_name VARCHAR2(200) ;
244 accrued_amount number;
245 amt_due number;
246 ct_id number;
247 begin
248 l_module_name := g_module_name || 'accrue_charges';
249 if interval = 0 then
250 -- sum the total amount_due_remaining for the invoice. We want to
251 -- apply the charge on the total amount of the invoice. This is really for
252 -- invoices with split payment terms. Because main query only is looking
253 -- at the porition of the invoice that is late. But when calculating
254 -- for one time charges we want the total amount of the invoice not just
255 -- the amount for the schedule that is late.
256
257 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
258 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
259 'cust_trx_id ='||customer_trx_id);
260 END IF;
261
262 ct_id := customer_trx_id;
263
264 select sum(amount_due_remaining)
265 into amt_due
266 from ar_payment_schedules
267 where customer_trx_id = ct_id
268 and class = 'INV';
269
270 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
272 'amt_due_remain='||amt_due);
273 END IF;
274
275 else
276 amt_due := amount_due_remaining;
277 end if;
278
279 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
281 'amt_due_remain='||amt_due);
282 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
283 'last_date ='||last_date);
284 END IF;
285
286 accrued_amount := round(amt_due * rate_amount *
287 ((trunc(sysdate) - trunc(last_date))/360),2);
288
289 return accrued_amount;
290 exception
291 when others then
292 error_message := SQLERRM;
293 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
294 l_module_name||'.final_exception',error_message);
295 RAISE;
296
297 end accrue_charges;
298
299 /* This function determines the last_accrual_date for the payment_schedule_id
300 and charge type being processed. */
301
302 function check_last_accrual return date
303 is
304 l_module_name VARCHAR2(200) ;
305 accrual_date date;
306 begin
307 l_module_name := g_module_name || 'check_last_accrual';
308 select distinct trunc(last_accrual_date)
309 into accrual_date
310 from fv_ar_controls
311 where payment_schedule_id = schedule_id
312 and created_from = charge_type;
313
314 return accrual_date;
315 exception
316 when no_data_found then
317 return null;
318 when others then
319 error_message := SQLERRM;
320 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
321 l_module_name||'.final_exception',error_message);
322 RAISE;
323 end check_last_accrual;
324
325 /* This function checks to see if a finance charge debit memo
326 already exists for this finance charge and invoice */
327
328 function check_dm return boolean is
329 l_module_name VARCHAR2(200) ;
330 begin
331 l_module_name := g_module_name || 'check_dm';
332 v_line_count := 0;
333 v_split_term := FALSE;
334
335 invoice_id := customer_trx_id;
336
337 select customer_trx_id
338 into existing_trx_id
339 from ra_customer_trx
340 where related_customer_trx_id = invoice_id
341 -- and created_from = charge_type; CHANGED
342 and interface_header_attribute3 = charge_type;
343
344 return TRUE;
345 exception
346 when no_data_found then
347 -- check interface table, this is specifically for inv w/split terms.
348 select count(*)
349 into v_line_count
350 from ra_interface_lines_all
351 where interface_line_attribute3 = charge_type
352 and related_customer_trx_id = invoice_id;
353
354 IF v_line_count = 0 THEN
355 -- no debit memo is being created for this invoice and charge_type
356 v_split_term:=FALSE;
357 return FALSE;
358 ELSE
359 -- a debit memo is being created for the invoice and charge type
360 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
362 'This is a possible Split Term');
363 END IF;
364 v_split_term := TRUE;
365 return TRUE;
366 END IF;
367
368 when others then
369 error_message := 'check_dm'||'-'||sqlerrm;
370 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
371 l_module_name||'.final_exception',error_message);
372 raise ORACLE_ERROR;
373 end;
374
375 /* This procedure selects data to be used in later SQL statements */
376
377 procedure get_global_info is
378 l_module_name VARCHAR2(200);
379 l_ledger_name VARCHAR2(30); --PSKI LA Changes
380 begin
381 l_module_name := g_module_name || 'get_global_info';
382 statement_no := 'A';
383
384 select sysdate
385 into start_time
386 from dual;
387
388 statement_no := 'B';
389
390 select term_id
391 into term_id
392 from ra_terms
393 where name = 'IMMEDIATE';
394
395 statement_no := 'C';
396 --sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
397
398 -- v_org_id := to_number(fnd_profile.value('ORG_ID'));
399 v_org_id := MO_GLOBAL.get_current_org_id; -- PSKI MOAC Changes
400 MO_UTILS.get_ledger_info(v_org_id,sob_id,l_ledger_name); --PSKI LA Changes
401
402 /****************** Use System Option ****************/
403 --Commented as part of Enhancement #1957297
404 /*
405 select receivables_trx_id
406 into rec_trx_id
407 from fv_operating_units
408 where set_of_books_id = sob_id;
409 */
410 /*************************************************/
411
412
413
414 exception
415 when no_data_found then
416 if statement_no ='C'
417 then error_message := 'Receivables Activity Not Found In Setup Options.'||
418 ' Please Add and Run This Process Again';
419 elsif statement_no = 'B'
420 then error_message := 'IMMEDIATE Terms Not Found. Please Add and Run '||
421 'This Process Again';
422 else
423 error_message := 'get_global_info'||'-'||statement_no||sqlerrm;
424 end if;
425 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
426 l_module_name||'.no_data_found',error_message);
427 raise ORACLE_ERROR;
428 when others then
429 error_message := 'get_global_info'||'-'||statement_no||'-'||sqlerrm;
430 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
431 l_module_name||'.final_exception',error_message);
432 raise ORACLE_ERROR;
433 end;
434
435
436 /* Select Revenue and Receivables Code Combination
437 ID's for use in Debit Memo Creation */
438
439 procedure get_additional_invoice_info is
440 l_module_name VARCHAR2(200) ;
441 begin
442 l_module_name := g_module_name || 'get_additional_invoice_info';
443
444 statement_no := 'A';
445
446 select gl_id_rev, gl_id_rec
447 into rev_ccid, rec_ccid
448 from ra_cust_trx_types
449 where cust_trx_type_id = trx_type_id;
450
451 exception
452
453 when others then
454 error_message := 'get_additional_invoice_info'||'-'||statement_no||
455 '-'||sqlerrm;
456 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
457 l_module_name||'.final_exception',error_message);
458 raise ORACLE_ERROR;
459 end;
460
461 FUNCTION fnd_ar_billto_adr_id(p_billto_site_use_id NUMBER)
462 RETURN NUMBER IS
463 l_module_name VARCHAR2(200) ;
464 v_billto_adr_id NUMBER;
465
466 BEGIN
467 l_module_name := g_module_name || 'fnd_ar_billto_adr_id';
468 SELECT cust_acct_site_id
469 INTO v_billto_adr_id
470 FROM hz_cust_site_uses_all
471 WHERE site_use_id = p_billto_site_use_id;
472
473 RETURN v_billto_adr_id;
474
475 exception
476 when others then
477 error_message := SQLERRM;
478 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
479 l_module_name||'.final_exception',error_message);
480 raise ORACLE_ERROR;
481 END fnd_ar_billto_adr_id;
482
483 /* ************************************************************* */
484 /* This procedure creates records in temp table used for
485 Detail report */
486
487 PROCEDURE create_record_temp (create_adjust varchar2)
488 IS
489 l_module_name VARCHAR2(200);
490 BEGIN
491 l_module_name := g_module_name || 'create_record_temp';
492 IF create_adjust='C' THEN
493 INSERT INTO fv_finchrg_debitmemo_temp
494 (Trx_number,
495 Charge_type ,
496 amount,
497 line_number,
498 create_adjust_flag,
499 set_of_books_id,
500 org_id)
501 VALUES
502 (fc_dm_rec.TRX_NUMBER,
503 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE3,
504 fc_dm_rec.AMOUNT,
505 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE2,
506 create_adjust,
507 fc_dm_rec.SET_OF_BOOKS_ID,
508 v_org_id);
509
510 ELSIF create_adjust='A' THEN
511
512 INSERT INTO fv_finchrg_debitmemo_temp
513 (trx_number ,
514 charge_type ,
515 amount ,
516 create_adjust_flag ,
517 set_of_books_id,
518 org_id )
519 VALUES
520 (substr(trx_number,1,17)||suffix,
521 charge_type ,
522 adjust_amount ,
523 create_adjust ,
524 set_of_books_id,
525 v_org_id) ;
526 END IF;
527 exception
528 when others then
529 error_message := SQLERRM;
530 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
531 l_module_name||'.final_exception',error_message);
532 raise ORACLE_ERROR;
533 END create_record_temp ;
534 /* ************************************************************* */
535
536 PROCEDURE map_debit_memo_line(dollars number)
537 IS
538
539 l_module_name VARCHAR2(200) ;
540 dm_trx_number varchar2(20);
541 v_batch_source_name ra_batch_sources.name%type;
542 v_autoinv_seq_nbr number;
543 v_line_num number;
544 v_split_trx_date date;
545 v_customer_trx_id number;
546 v_pay_schedule_cnt number;
547
548 BEGIN
549
550 l_module_name := g_module_name || 'map_debit_memo_line';
551 dm_trx_number := substr(trx_number,1,17)||suffix;
552
553 statement_no := 'H';
554 select name
555 into v_batch_source_name
556 from ra_batch_sources
557 where batch_source_id = fin_charge_batch_source_id;
558
559 IF v_split_term THEN
560
561 -- because this could be a split term inv for which the dm is being
562 -- created, v_line_num (attribute2) is being calulated. If a split
563 -- term inv has both payment schedules accruing for the first time
564 -- then we won't to add a new line to the debit memo for the amount of
565 -- the accrual (bug 2113826)
566
567 v_line_num := v_line_count + 1;
568
569 statement_no := 'I';
570 -- find the attribute1 value for the original debit memo
571 -- bug 3058213, capturing the trx_date
572 select interface_line_attribute1, trx_date
573 into v_autoinv_seq_nbr, v_split_trx_date
574 from ra_interface_lines_all
575 where related_customer_trx_id = invoice_id
576 and interface_line_attribute3 = charge_type
577 and interface_line_attribute2 = '1';
578
579 ELSE -- normal debit memo
580
581 statement_no := 'J';
582 select fv_dc_autoinv_header_s.nextval
583 into v_autoinv_seq_nbr
584 from dual;
585
586 v_line_num := 1;
587 END IF;
588
589 fc_dm_rec.INTERFACE_LINE_ID := NULL;
590 fc_dm_rec.INTERFACE_LINE_CONTEXT := 'Accrue Finance Charges';
591 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE1 := to_char(v_autoinv_seq_nbr);
592 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE2 := to_char(v_line_num);
593 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE3 := charge_type; -- finance charge type
594 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE4 := schedule_id; -- payment schedule id of original overdue invoice
595 fc_dm_rec.HEADER_ATTRIBUTE_CATEGORY := 'Accrue Finance Charge';
596 fc_dm_rec.HEADER_ATTRIBUTE1 := v_autoinv_seq_nbr;
597 fc_dm_rec.HEADER_ATTRIBUTE3 := charge_type; -- finance charge type
598 fc_dm_rec.BATCH_SOURCE_NAME := v_batch_source_name;
599 fc_dm_rec.SET_OF_BOOKS_ID := set_of_books_id;
600 fc_dm_rec.LINE_TYPE := 'LINE';
601 fc_dm_rec.DESCRIPTION := 'Accrue Federal Finance Charges';
602 fc_dm_rec.CURRENCY_CODE := invoice_currency_code;
603 fc_dm_rec.AMOUNT := dollars;
604 fc_dm_rec.CUST_TRX_TYPE_NAME := NULL;
605 fc_dm_rec.CUST_TRX_TYPE_ID := trx_type_id;
606 fc_dm_rec.TERM_NAME := NULL;
607
608 IF v_inv_date_type='ORI' THEN
609 -- if the the parameter is Original than the term_id of the dm should
610 -- be the same as the original invoice. bug 3018578
611
612 v_customer_trx_id := customer_trx_id;
613
614 select count(*)
615 into v_pay_schedule_cnt
616 from ar_payment_schedules
617 where customer_trx_id = v_customer_trx_id;
618
619 IF v_pay_schedule_cnt > 1 THEN
620 -- this is a split term
621 fc_dm_rec.TERM_ID := 5;
622
623 ELSE
624
625 select term_id
626 into fc_dm_rec.term_id
627 from ra_customer_trx
628 where customer_trx_id = v_customer_trx_id;
629 END IF;
630
631 ELSE
632
633 fc_dm_rec.TERM_ID := 5;
634 END If;
635
636 fc_dm_rec.ORIG_SYSTEM_BATCH_NAME := NULL;
637 fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_REF := NULL;
638 fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID := bill_to_customer_id;
639 fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_REF := NULL;
640 /* -------------------------------- changed
641 fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_ID := bill_to_address_id;
642 */
643 fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_ID := fnd_ar_billto_adr_id(bill_to_site_use_id);
644 fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_REF := NULL;
645 fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_ID := bill_to_contact_id;
646 fc_dm_rec.CONVERSION_TYPE := NVL(v_exchange_rate_type,'User');
647 fc_dm_rec.CONVERSION_RATE := NVL(v_exchange_rate,1);
648 fc_dm_rec.CONVERSION_DATE := v_exchange_date;
649 fc_dm_rec.CUSTOMER_TRX_ID := NULL;
650
651 IF v_inv_date_type='DUE' THEN
652 -- added this bec of when have split term invoice, every payment schedule
653 -- has it's own due date. Therefore every line of the dm was having a
654 -- a different due date and causing Autoinvoice to error due to group
655 -- violations. When we are adding additional lines
656 -- to the dm for split terms we will ust the original payment schedule's
657 -- due date. bug 3058213
658
659 IF v_split_term then
660 fc_dm_rec.TRX_DATE := trunc(v_split_trx_date);
661 ELSE
662 fc_dm_rec.TRX_DATE := trunc(due_date);
663 END If;
664
665 ELSIF v_inv_date_type='ORI' THEN
666 fc_dm_rec.TRX_DATE := trunc(v_trx_date);
667
668 ELSE
669 fc_dm_rec.TRX_DATE := trunc(sysdate);
670 END IF;
671
672 fc_dm_rec.GL_DATE := trunc(v_gl_date);
673 fc_dm_rec.DOCUMENT_NUMBER := v_autoinv_seq_nbr;
674 fc_dm_rec.TRX_NUMBER := dm_trx_number;
675 fc_dm_rec.RELATED_CUSTOMER_TRX_ID := customer_trx_id;
676 fc_dm_rec.LINE_NUMBER := NULL;
677 fc_dm_rec.QUANTITY := 1;
678 fc_dm_rec.QUANTITY_ORDERED := NULL;
679 fc_dm_rec.UNIT_SELLING_PRICE := dollars;
680 fc_dm_rec.UNIT_STANDARD_PRICE := NULL;
681 fc_dm_rec.CREDIT_METHOD_FOR_ACCT_RULE := NULL;
682 fc_dm_rec.CREDIT_METHOD_FOR_INSTALLMENTS := NULL;
683 fc_dm_rec.REASON_CODE := NULL;
684 fc_dm_rec.COMMENTS := NULL;
685 fc_dm_rec.USSGL_TRANSACTION_CODE_CONTEXT := NULL;
686 -- fc_dm_rec.USSGL_TRANSACTION_CODE := ussgl_transaction_code;
687 fc_dm_rec.USSGL_TRANSACTION_CODE := NULL;
688 fc_dm_rec.UOM_CODE := NULL;
689 fc_dm_rec.UOM_NAME := 'EACH';
690 fc_dm_rec.CREATED_BY := FND_GLOBAL.USER_ID;
691 fc_dm_rec.CREATION_DATE := trunc(sysdate);
692 fc_dm_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
693 fc_dm_rec.LAST_UPDATE_DATE := trunc(sysdate);
694 -- fc_dm_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
695 fc_dm_rec.LAST_UPDATE_LOGIN := NULL;
696 fc_dm_rec.ORG_ID := v_org_id;
697 fc_dm_rec.AMOUNT_INCLUDES_TAX_FLAG := 'N';
698
699 exception
700 when others then
701 error_message := SQLERRM;
702 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
703 l_module_name||'.final_exception',error_message);
704 raise ORACLE_ERROR;
705 END;
706
707
708 PROCEDURE insert_debit_memo_line
709 IS
710 l_module_name VARCHAR2(200) ;
711 BEGIN
712 l_module_name := g_module_name || 'insert_debit_memo_line';
713 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
715 'Creating DM '||fc_dm_rec.TRX_NUMBER);
716 END IF;
717
718
719 INSERT INTO ra_interface_lines_all(
720 INTERFACE_LINE_ID ,
721 INTERFACE_LINE_CONTEXT,
722 INTERFACE_LINE_ATTRIBUTE1,
723 INTERFACE_LINE_ATTRIBUTE2 ,
724 INTERFACE_LINE_ATTRIBUTE3,
725 INTERFACE_LINE_ATTRIBUTE4,
726 HEADER_ATTRIBUTE_CATEGORY,
727 HEADER_ATTRIBUTE1,
728 HEADER_ATTRIBUTE3,
729 BATCH_SOURCE_NAME,
730 SET_OF_BOOKS_ID,
731 LINE_TYPE,
732 DESCRIPTION ,
733 CURRENCY_CODE,
734 AMOUNT,
735 -- CUST_TRX_TYPE_NAME,
736 CUST_TRX_TYPE_ID,
737 -- TERM_NAME ,
738 TERM_ID ,
739 ORIG_SYSTEM_BATCH_NAME,
740 ORIG_SYSTEM_BILL_CUSTOMER_REF,
741 ORIG_SYSTEM_BILL_CUSTOMER_ID,
742 ORIG_SYSTEM_BILL_ADDRESS_REF,
743 ORIG_SYSTEM_BILL_ADDRESS_ID,
744 -- ORIG_SYSTEM_BILL_CONTACT_REF,
745 -- ORIG_SYSTEM_BILL_CONTACT_ID,
746 CONVERSION_TYPE,
747 CONVERSION_DATE,
748 CONVERSION_RATE,
749 CUSTOMER_TRX_ID ,
750 TRX_DATE,
751 GL_DATE,
752 DOCUMENT_NUMBER ,
753 TRX_NUMBER ,
754 RELATED_CUSTOMER_TRX_ID,
755 LINE_NUMBER,
756 QUANTITY,
757 QUANTITY_ORDERED,
758 UNIT_SELLING_PRICE ,
759 UNIT_STANDARD_PRICE ,
760 CREDIT_METHOD_FOR_ACCT_RULE,
761 CREDIT_METHOD_FOR_INSTALLMENTS,
762 REASON_CODE,
763 REFERENCE_LINE_ID ,
764 REFERENCE_LINE_CONTEXT ,
765 REFERENCE_LINE_ATTRIBUTE1,
766 REFERENCE_LINE_ATTRIBUTE2 ,
767 COMMENTS,
768 -- TC Obsoletion
769 -- USSGL_TRANSACTION_CODE_CONTEXT,
770 -- USSGL_TRANSACTION_CODE,
771 UOM_CODE,
772 UOM_NAME,
773 CREATED_BY ,
774 CREATION_DATE,
775 LAST_UPDATED_BY,
776 LAST_UPDATE_DATE,
777 LAST_UPDATE_LOGIN ,
778 ORG_ID ,
779 AMOUNT_INCLUDES_TAX_FLAG)
780 VALUES(
781 fc_dm_rec.INTERFACE_LINE_ID,
782 fc_dm_rec.INTERFACE_LINE_CONTEXT,
783 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE1,
784 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE2,
785 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE3,
786 fc_dm_rec.INTERFACE_LINE_ATTRIBUTE4,
787 fc_dm_rec.HEADER_ATTRIBUTE_CATEGORY,
788 fc_dm_rec.HEADER_ATTRIBUTE1,
789 fc_dm_rec.HEADER_ATTRIBUTE3,
790 fc_dm_rec.BATCH_SOURCE_NAME,
791 fc_dm_rec.SET_OF_BOOKS_ID,
792 fc_dm_rec.LINE_TYPE,
793 fc_dm_rec.DESCRIPTION,
794 fc_dm_rec.CURRENCY_CODE,
795 fc_dm_rec.AMOUNT,
796 -- fc_dm_rec.CUST_TRX_TYPE_NAME,
797 fc_dm_rec.CUST_TRX_TYPE_ID,
798 -- fc_dm_rec.TERM_NAME,
799 fc_dm_rec.TERM_ID,
800 fc_dm_rec.ORIG_SYSTEM_BATCH_NAME,
801 fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_REF,
802 fc_dm_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID,
803 fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_REF,
804 fc_dm_rec.ORIG_SYSTEM_BILL_ADDRESS_ID,
805 -- fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_REF,
806 -- fc_dm_rec.ORIG_SYSTEM_BILL_CONTACT_ID,
807 fc_dm_rec.CONVERSION_TYPE,
808 fc_dm_rec.CONVERSION_DATE,
809 fc_dm_rec.CONVERSION_RATE,
810 fc_dm_rec.CUSTOMER_TRX_ID,
811 fc_dm_rec.TRX_DATE,
812 fc_dm_rec.GL_DATE,
813 fc_dm_rec.DOCUMENT_NUMBER,
814 fc_dm_rec.TRX_NUMBER,
815 fc_dm_rec.RELATED_CUSTOMER_TRX_ID,
816 fc_dm_rec.LINE_NUMBER,
817 fc_dm_rec.QUANTITY,
818 fc_dm_rec.QUANTITY_ORDERED,
819 fc_dm_rec.UNIT_SELLING_PRICE,
820 fc_dm_rec.UNIT_STANDARD_PRICE,
821 fc_dm_rec.CREDIT_METHOD_FOR_ACCT_RULE,
822 fc_dm_rec.CREDIT_METHOD_FOR_INSTALLMENTS,
823 fc_dm_rec.REASON_CODE,
824 fc_dm_rec.REFERENCE_LINE_ID,
825 fc_dm_rec.REFERENCE_LINE_CONTEXT,
826 fc_dm_rec.REFERENCE_LINE_ATTRIBUTE1,
827 fc_dm_rec.REFERENCE_LINE_ATTRIBUTE2,
828 fc_dm_rec.COMMENTS,
829 -- TC Obsoletion
830 -- fc_dm_rec.USSGL_TRANSACTION_CODE_CONTEXT,
831 -- fc_dm_rec.USSGL_TRANSACTION_CODE,
832 fc_dm_rec.UOM_CODE,
833 fc_dm_rec.UOM_NAME,
834 fc_dm_rec.CREATED_BY,
835 fc_dm_rec.CREATION_DATE,
836 fc_dm_rec.LAST_UPDATED_BY,
837 fc_dm_rec.LAST_UPDATE_DATE,
838 fc_dm_rec.LAST_UPDATE_LOGIN,
839 fc_dm_rec.ORG_ID,
840 fc_dm_rec.AMOUNT_INCLUDES_TAX_FLAG);
841
842 create_record_temp('C') ; -- Creates a record in temp table for Detail report
843
844 exception
845 when others then
846 error_message := SQLERRM;
847 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
848 l_module_name||'.final_exception',error_message);
849 raise ORACLE_ERROR;
850 END insert_debit_memo_line;
851
852
853
854 PROCEDURE map_debit_memo_dist(v_account_class IN varchar2, dollars IN number)
855 IS
856 l_module_name VARCHAR2(200);
857 BEGIN
858
859 l_module_name := g_module_name || 'map_debit_memo_dist';
860 fc_dm_dist_rec.interface_line_context := fc_dm_rec.interface_line_context;
861 fc_dm_dist_rec.interface_line_attribute1 := fc_dm_rec.interface_line_attribute1;
862 fc_dm_dist_rec.interface_line_attribute2 := fc_dm_rec.interface_line_attribute2;
863 fc_dm_dist_rec.interface_line_attribute3 := fc_dm_rec.interface_line_attribute3;
864 fc_dm_dist_rec.interface_line_attribute4 := fc_dm_rec.interface_line_attribute4;
865
866 fc_dm_dist_rec.account_class := v_account_class;
867 fc_dm_dist_rec.percent := 100;
868 fc_dm_dist_rec.amount := dollars;
869
870 /*
871 -- fc_dm_dist_rec.code_combination_id :=
872 -- decode(v_account_class,'REC',rec_ccid,'REV',rev_ccid);
873 */
874
875 select (decode(v_account_class,'REC',rec_ccid,'REV',rev_ccid))
876 into
877 fc_dm_dist_rec.code_combination_id
878 from dual;
879
880
881 fc_dm_dist_rec.org_id := v_org_id;
882 fc_dm_dist_rec.created_by := FND_GLOBAL.USER_ID;
883 fc_dm_dist_rec.creation_date := trunc(sysdate);
884 fc_dm_dist_rec.last_updated_by := FND_GLOBAL.USER_ID;
885 fc_dm_dist_rec.last_update_date := trunc(sysdate);
886
887 exception
888 when others then
889 error_message := SQLERRM;
890 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
891 l_module_name||'.final_exception',error_message);
892 raise ORACLE_ERROR;
893 END map_debit_memo_dist;
894
895 -- Procedure Added For Bug 4655482
896 PROCEDURE update_debit_memo_dist
897 IS
898 l_module_name VARCHAR2(200);
899 BEGIN
900 l_module_name := g_module_name || 'update_debit_memo_dist';
901 UPDATE ra_interface_distributions_all rd
902 set rd.amount = rd.amount + fc_dm_dist_rec.amount,
903 rd.last_updated_by = fc_dm_dist_rec.last_updated_by,
904 rd.last_update_date = fc_dm_dist_rec.last_update_date
905 WHERE
906 rd.INTERFACE_LINE_CONTEXT = fc_dm_dist_rec.INTERFACE_LINE_CONTEXT
907 AND rd.INTERFACE_LINE_ATTRIBUTE1 = fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE1
908 AND rd.INTERFACE_LINE_ATTRIBUTE2 = '1'
909 AND rd.INTERFACE_LINE_ATTRIBUTE3 = fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE3
910 AND rd.ACCOUNT_CLASS = fc_dm_dist_rec.ACCOUNT_CLASS
911 AND rd.org_id = fc_dm_dist_rec.ORG_ID;
912
913 exception
914 when others then
915 error_message := SQLERRM;
916 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
917 l_module_name||'.final_exception',error_message);
918 raise ORACLE_ERROR;
919 END update_debit_memo_dist;
920
921
922 PROCEDURE insert_debit_memo_dist
923 IS
924 l_module_name VARCHAR2(200) ;
925 BEGIN
926 l_module_name := g_module_name || 'insert_debit_memo_dist';
927 INSERT into ra_interface_distributions_all(
928 INTERFACE_LINE_CONTEXT,
929 INTERFACE_LINE_ATTRIBUTE1,
930 INTERFACE_LINE_ATTRIBUTE2,
931 INTERFACE_LINE_ATTRIBUTE3,
932 INTERFACE_LINE_ATTRIBUTE4,
933 ACCOUNT_CLASS,
934 PERCENT,
935 AMOUNT,
936 CODE_COMBINATION_ID,
937 ORG_ID,
938 CREATED_BY,
939 CREATION_DATE,
940 LAST_UPDATED_BY,
941 LAST_UPDATE_DATE)
942 VALUES(
943 fc_dm_dist_rec.INTERFACE_LINE_CONTEXT,
944 fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE1,
945 fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE2,
946 fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE3,
947 fc_dm_dist_rec.INTERFACE_LINE_ATTRIBUTE4,
948 fc_dm_dist_rec.ACCOUNT_CLASS,
949 fc_dm_dist_rec.PERCENT,
950 fc_dm_dist_rec.AMOUNT,
951 fc_dm_dist_rec.CODE_COMBINATION_ID,
952 fc_dm_dist_rec.ORG_ID,
953 fc_dm_dist_rec.CREATED_BY,
954 fc_dm_dist_rec.CREATION_DATE,
955 fc_dm_dist_rec.LAST_UPDATED_BY,
956 fc_dm_dist_rec.LAST_UPDATE_DATE);
957 exception
958 when others then
959 error_message := SQLERRM;
960 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
961 l_module_name||'.final_exception',error_message);
962 raise ORACLE_ERROR;
963 END insert_debit_memo_dist;
964
965 /* ************************************************************* */
966
967 /* This procedure creates a new debit memo */
968
969 procedure create_new_dm(dollars number) is
970 l_module_name VARCHAR2(200);
971 date_exists fv_ar_controls.last_accrual_date%TYPE;
972 begin
973
974 l_module_name := g_module_name || 'create_new_dm';
975 -- call procedure to setup ra_interface_lines column values
976 map_debit_memo_line(dollars);
977
978 -- insert values in ra_interface_lines
979 insert_debit_memo_line;
980
981 -- setup ra_interface_distributions column values for the Receivable type line
982 map_debit_memo_dist('REC', dollars);
983
984 -- insert values in ra_interface_distributions;
985 IF v_split_term THEN
986 /**
987 This is done split payment schedules. If v_split_term is
988 TRUE that means line for first payment schedule already exist in
989 interface which also means we have REC distribution available
990 which we update
991 */
992 update_debit_memo_dist;
993 ELSE
994 insert_debit_memo_dist;
995 END IF;
996
997 -- setup ra_interface_distributions column values for the Revenue type line.
998 map_debit_memo_dist('REV', dollars);
999
1000 -- insert values in ra_interface_distributions;
1001 insert_debit_memo_dist;
1002
1003 -- variable used to check if AutoInvoice should be submitted.
1004 -- If the interface table contains no records then
1005 -- AutoInvoice should not be submitted.
1006 v_interface_record := 'Y';
1007
1008 begin
1009 select distinct last_accrual_date
1010 into date_exists
1011 from fv_ar_controls
1012 where payment_schedule_id = schedule_id
1013 and created_from = charge_type;
1014
1015 exception
1016 when no_data_found then
1017 statement_no := 'G';
1018
1019 insert into fv_ar_controls
1020 (payment_schedule_id, last_accrual_date, created_from, org_id)
1021 values(schedule_id, trunc(sysdate), charge_type, v_org_id);
1022 end;
1023
1024
1025 exception
1026 when others then
1027 error_message := 'create_new_dm'||'-'||statement_no||'-'||sqlerrm;
1028 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1029 l_module_name||'.final_exception',error_message);
1030 raise ORACLE_ERROR;
1031 end;
1032
1033 /* ************************************************************* */
1034
1035 /* This procedure creates an adjustment to an existing debit memo */
1036
1037 procedure adjust_existing_dm(dollars in number) is
1038 l_module_name VARCHAR2(200) ;
1039
1040 existing_pay_schedule_id ar_payment_schedules.payment_schedule_id%TYPE;
1041 dt_exists varchar2(1);
1042 v_adjustment_number_o number;
1043 v_adjustment_id_o number;
1044 v_adjustment_number number;
1045 v_phase varchar2(80);
1046 v_status varchar2(80);
1047 v_dev_phase varchar2(80);
1048 v_dev_status varchar2(80);
1049 v_message varchar2(80);
1050
1051 begin
1052 l_module_name := g_module_name || 'adjust_existing_dm';
1053 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1055 l_module_name,'adjust_existing_dm');
1056 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1057 'existing dm trx_id= '||existing_trx_id);
1058 END IF;
1059
1060 statement_no := 'A';
1061 adjust_amount:=dollars ; --Store the dollars value to insert in temp table
1062
1063 /* bug 1788860, this is not the correct ccid to put in the adjustment
1064 instead the ccid should come from ccid defined for the activity name
1065
1066 select code_combination_id
1067 into adjust_ccid
1068 from ra_cust_trx_line_gl_dist
1069 where account_class='REC'
1070 and customer_trx_id = existing_trx_id;
1071
1072 below is the correct code
1073 */
1074
1075 BEGIN
1076 select code_combination_id
1077 into adjust_ccid
1078 from ar_receivables_trx
1079 where receivables_trx_id = rec_trx_id
1080 and set_of_books_id = sob_id;
1081
1082 EXCEPTION
1083 when others then
1084 error_message := 'A'||sqlerrm;
1085 raise ORACLE_ERROR;
1086 END;
1087 -- existing_trx_id is the debt memo customer_trx_id for the charge type
1088
1089 statement_no := 'A.A';
1090
1091 select payment_schedule_id
1092 into existing_pay_schedule_id
1093 from ar_payment_schedules
1094 where customer_trx_id = existing_trx_id
1095 and class = 'DM';
1096 -- finding the payment_schedule_id for the existing dm.
1097
1098 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1099 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1100 'existing pay_sch_id = '||existing_pay_schedule_id);
1101 END IF;
1102
1103
1104
1105 /* Insert into ar_adjustments to capture the accrual of penalty for
1106 the existing debit memo. */
1107
1108 statement_no := 'B';
1109
1110 BEGIN
1111 select ar_adjustments_s.nextval
1112 into v_adjustment_id
1113 from dual;
1114 EXCEPTION
1115 when others then
1116 error_message := 'B.1 '||sqlerrm;
1117 raise ORACLE_ERROR;
1118 END;
1119
1120 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1121 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1122 'v_adjustment_id = '||v_adjustment_id);
1123 END IF;
1124
1125 v_user_id := to_number(fnd_profile.value('USER_ID'));
1126 v_login_id := to_number(fnd_profile.value('LOGIN_ID'));
1127
1128
1129 BEGIN
1130 select concurrent_program_id
1131 into v_conc_program_id
1132 from fnd_concurrent_programs
1133 where concurrent_program_name = 'FVXDCACC'
1134 and application_id = 8901;
1135
1136 EXCEPTION
1137 when others then
1138 error_message := 'B.2 '||sqlerrm;
1139 raise ORACLE_ERROR;
1140 END;
1141
1142 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1143 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1144 'v_conc_program_id = '||v_conc_program_id);
1145 END IF;
1146
1147 /*
1148 -- get concurrent request id
1149 v_conc_info := fnd_concurrent.get_request_status(v_request_id,
1150 'FV', 'FVXDCACC', v_phase,
1151 v_status, v_dev_phase, v_dev_status, v_message);
1152 */
1153
1154 -- Call the server package ARP_INSERT_ADJ_COVER.INSERT_ADJUST_COVER which
1155 -- calls various internal packages and procedures for adjustment
1156
1157 select ar_adjustment_number_s.nextval
1158 into v_adjustment_number
1159 from dual;
1160
1161 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1162 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1163 'Calling INSERT_ADJUST_COVER to adjust'||
1164 ' payment_schedule_id'||existing_pay_schedule_id);
1165 END IF;
1166
1167
1168 ARP_INSERT_ADJ_COVER.INSERT_ADJUST_COVER
1169 ( p_form_name => 'FVXDCACC',
1170 p_form_version => 0,
1171 p_acctd_amount => dollars,
1172 p_adjustment_id => v_adjustment_id,
1173 -- p_adjustment_number => ar_adjustment_number_s.nextval,
1174 p_adjustment_number => v_adjustment_number,
1175 p_adjustment_type => 'M',
1176 -- Adjustment type, defaulted to M in the core form
1177 p_amount => dollars,
1178 p_apply_date => trunc(SYSDATE),
1179 p_approved_by => v_user_id,
1180 p_associated_application_id => null,
1181 p_associated_cash_receipt_id => null,
1182 p_attribute1 => null,
1183 p_attribute10 => null,
1184 p_attribute11 => null,
1185 p_attribute12 => null,
1186 p_attribute13 => null,
1187 p_attribute14 => null,
1188 p_attribute15 => null,
1189 p_attribute2 => null,
1190 p_attribute3 => null,
1191 p_attribute4 => null,
1192 p_attribute5 => null,
1193 p_attribute6 => null,
1194 p_attribute7 => null,
1195 p_attribute8 => null,
1196 p_attribute9 => null,
1197 p_attribute_category => null,
1198 p_automatically_generated =>null,
1199 p_batch_id => null,
1200 p_chargeback_customer_trx_id => null,
1201 p_code_combination_id => adjust_ccid,
1202 p_comments => null, -- Comments
1203 p_created_by => v_user_id,
1204 p_created_from =>'FVXDCACC',
1205 p_creation_date => SYSDATE,
1206 p_customer_trx_id => existing_trx_id,
1207 p_customer_trx_line_id => null,
1208 p_distribution_set_id => null,
1209 p_doc_sequence_id => null,
1210 p_doc_sequence_value => null,
1211 p_freight_adjusted => null,
1212 p_gl_date => trunc(sysdate),
1213 p_gl_posted_date => null,
1214 p_last_updated_by => v_user_id,
1215 p_last_update_date => SYSDATE,
1216 p_last_update_login => v_login_id,
1217 p_line_adjusted => null,
1218 p_org_id => v_org_id,
1219 p_payment_schedule_id => existing_pay_schedule_id,
1220 p_postable => 'Y',
1221 p_posting_control_id => -3, -- This value is been defaulted
1222 -- to -3 in the core form
1223 p_program_application_id => -1,
1224 p_program_id => v_conc_program_id,
1225 p_program_update_date => sysdate,
1226 p_reason_code => null,
1227 p_receivables_charges_adjusted => dollars,
1228 p_receivables_trx_id => rec_trx_id,
1229 p_request_id => v_request_id,
1230 p_set_of_books_id => set_of_books_id,
1231 p_status => 'A',
1232 p_subsequent_trx_id => null,
1233 p_tax_adjusted => null,
1234 p_type => 'CHARGES',
1235 -- p_ussgl_transaction_code => ussgl_transaction_code,
1236 p_ussgl_transaction_code => null,
1237 p_ussgl_transaction_code_conte => null,
1238 p_adjustment_number_o => v_adjustment_number_o,-- Out variable
1239 p_adjustment_id_o => v_adjustment_id_o -- Out variable
1240 );
1241 create_record_temp('A') ; -- Creates a record in temp table for Detail report
1242 begin
1243 update fv_ar_controls
1244 set last_accrual_date = trunc(sysdate)
1245 where payment_schedule_id = schedule_id
1246 and created_from = charge_type;
1247
1248 exception
1249 when no_data_found then
1250 statement_no := 'F';
1251 insert into fv_ar_controls(last_accrual_date, payment_schedule_id,
1252 created_from, org_id)
1253 values(trunc(sysdate), schedule_id, charge_type, v_org_id);
1254 end;
1255
1256 exception
1257 when others then
1258 error_message := 'adjust_existing_dm'||'-'||statement_no||'-'||sqlerrm;
1259 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1260 l_module_name||'.final_exception',error_message);
1261 raise ORACLE_ERROR;
1262
1263 end adjust_existing_dm;
1264
1265
1266 procedure cleanup(var_batch_source_name varchar2)
1267 is
1268 l_module_name VARCHAR2(200) ;
1269 -- this procedure is called when AutoInvoice fails .
1270 -- Records from interface tables, control record from fv_ar_controls
1271 -- and also record for invoice from fv_ar_controls have to be deleted
1272
1273
1274 cursor error_records_c(x_batch_source_name varchar2) is
1275 select interface_line_attribute4, header_attribute3, interface_line_id
1276 from ra_interface_lines
1277 where interface_line_context = 'Accrue Finance Charges'
1278 and batch_source_name = x_batch_source_name -- added line
1279 and interface_line_id not in (select interface_line_id
1280 from ra_interface_errors);
1281
1282 begin
1283 l_module_name := g_module_name || 'cleanup';
1284
1285 for error_records_rec IN error_records_c(var_batch_source_name) loop
1286 delete from fv_ar_controls
1287 where payment_schedule_id = error_records_rec.interface_line_attribute4
1288 and created_from = error_records_rec.header_attribute3;
1289
1290 delete from ra_interface_distributions
1291 where interface_line_id = error_records_rec.interface_line_id;
1292
1293 end loop;
1294
1295 delete from ra_interface_lines
1296 where interface_line_context = 'Accrue Finance Charges'
1297 and batch_source_name = var_batch_source_name -- added line
1298 and interface_line_id not in (select interface_line_id
1299 from ra_interface_errors);
1300
1301 DELETE FROM fv_finchrg_debitmemo_temp -- Purge from temp table 2157100
1302 WHERE set_of_books_id=SOB_ID
1303 AND NVL(org_id,-99)=NVL(v_org_id,-99) ;
1304 exception
1305 when others then
1306 error_message := SQLERRM;
1307 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1308 l_module_name||'.final_exception',error_message);
1309 raise ORACLE_ERROR;
1310 end cleanup;
1311
1312 /*
1313 If the finance charge is a fixed amount, this procedure will
1314 determine how much to add to the invoice.
1315 If more than one period has passed since the last finance charge accrual,
1316 this must be taken into consideration and the fixed amount
1317 should be applied multiple times.
1318 */
1319
1320 function calculate_fixed_amount(nper number) return number is
1321 l_module_name VARCHAR2(200) ;
1322 f_amount number := 0;
1323 done boolean := FALSE;
1324 curr date;
1325 last date;
1326 ctr number := 0;
1327
1328 begin
1329 l_module_name := g_module_name || 'calculate_fixed_amount';
1330 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1332 'beginning calculate_fixed_amount');
1333 END IF;
1334
1335 curr := trunc(first_late_day);
1336 last := trunc(first_late_day);
1337 while (not done)
1338 loop
1339 curr := curr + interval;
1340
1341 ctr := ctr + 1;
1342 if last_accrual_date between last and curr then
1343 done := TRUE;
1344 else
1345 last := curr;
1346 end if;
1347 end loop;
1348 f_amount := rate_amount * (nper - ctr);
1349
1350 return f_amount;
1351 exception
1352 when others then
1353 error_message := SQLERRM;
1354 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1355 '.final_exception',error_message);
1356 raise ORACLE_ERROR;
1357 end calculate_fixed_amount;
1358
1359 /* Function determines the number of interval periods that have passed since
1360 the invoice was first late */
1361
1362 function missed_intervals return number is
1363 l_module_name VARCHAR2(200) ;
1364 begin
1365 l_module_name := g_module_name || 'missed_intervals';
1366 found := FALSE;
1367 num_periods := 1; -- starting at one takes in to consideration we must
1368 -- charge an amount for just being late.
1369 current_pd :=trunc(first_late_day) + interval;
1370 last_pd :=trunc(first_late_day) + interval;
1371
1372 if interval <> 0 then
1373
1374 while found = FALSE loop
1375 current_pd :=current_pd + interval;
1376 num_periods := num_periods + 1;
1377 if trunc(sysdate) between last_pd and current_pd then
1378 found := TRUE;
1379 else
1380 last_pd := current_pd;
1381 end if;
1382 end loop;
1383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1384 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1385 'number of periods = '||num_periods);
1386 END IF;
1387
1388
1389 return num_periods;
1390
1391 else -- interval = 0
1392 num_periods := 1;
1393 return num_periods;
1394 end if;
1395 exception
1396 when others then
1397 error_message := SQLERRM;
1398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1399 l_module_name||'.final_exception',error_message);
1400 raise ORACLE_ERROR;
1401 end missed_intervals;
1402
1403 /*****************************************************************************/
1404 /* Begin Accrue Finance Charge Process */
1405 /*****************************************************************************/
1406
1407
1408 procedure accrue_finance_charge(errbuf OUT NOCOPY varchar2,
1409 retcode out NOCOPY number,
1410 p_invoice_date VARCHAR2,
1411 p_inv_due_date VARCHAR2,
1412 p_gl_date VARCHAR2
1413 ) is
1414
1415 l_module_name VARCHAR2(200);
1416 CURSOR RA_INTERFACE_ERR_CUR(p_sob number, p_org NUMBER) IS
1417 SELECT 1 FROM ra_interface_errors_all
1418 WHERE INTERFACE_LINE_ID IN
1419 (SELECT INTERFACE_LINE_ID
1420 FROM ra_interface_lines_all
1421 WHERE INTERFACE_LINE_CONTEXT='Accrue Finance Charges' AND
1422 set_of_books_id=p_sob
1423 AND org_id = p_org);
1424
1425 CURSOR fv_ar_open_periods(p_date date) IS
1426 SELECT 1
1427 FROM gl_period_statuses WHERE closing_status ='O'
1428 AND set_of_books_id = sob_id
1429 AND application_id = 222
1430 AND p_date between start_date and end_date;
1431
1432
1433 v_interface_err_count NUMBER ;
1434 v_ar_period_count NUMBER;
1435 cnt NUMBER:=0;
1436 rphase varchar2(80);
1437 rstatus varchar2(80);
1438 dphase varchar2(80);
1439 dstatus varchar2(80);
1440 message varchar2(80);
1441 new_due_date DATE;
1442 l_error_cnt number;
1443 -- v_auto_id_indx number;
1444
1445 begin
1446 l_module_name := g_module_name || 'accrue_finance_charge';
1447 v_gl_date:= TO_DATE(p_gl_date,'YYYY/MM/DD HH24:MI:SS');
1448 v_inv_date_type:=p_invoice_date ;
1449
1450 v_inv_due_date := p_inv_due_date;
1451
1452 get_global_info;
1453
1454 OPEN RA_INTERFACE_ERR_CUR(sob_id, v_org_id) ;
1455 FETCH RA_INTERFACE_ERR_CUR INTO v_interface_err_count ;
1456 IF RA_INTERFACE_ERR_CUR %FOUND THEN
1457 CLOSE RA_INTERFACE_ERR_CUR;
1458 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1459 'Debit Memos created by the previous submission ');
1460 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1461 'of this process, exists in Receivables AutoInvoice ') ;
1462 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1463 'Interface tables because of validation failure. Please ');
1464 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1465 'correct these transactions and run AutoInvoice before ');
1466 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1467 'performing the Accrue Finance Charge process. ' );
1468 retcode:= 2;
1469 RETURN ;
1470 END IF;
1471 CLOSE RA_INTERFACE_ERR_CUR ;
1472
1473 --
1474 OPEN fv_ar_open_periods (v_gl_date) ;
1475 FETCH fv_ar_open_periods INTO v_ar_period_count ;
1476 IF fv_ar_open_periods%NOTFOUND THEN
1477 CLOSE fv_ar_open_periods;
1478 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1479 'GL Date should be in an open period. ');
1480 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1481 'Please Enter a GL Date which is in an open period ') ;
1482 retcode:= 2;
1483 RETURN ;
1484 END IF;
1485 CLOSE fv_ar_open_periods;
1486 --
1487
1488 DELETE FROM fv_finchrg_debitmemo_temp -- Purge from temp table
1489 WHERE set_of_books_id=SOB_ID
1490 AND NVL(org_id,-99)=NVL(v_org_id,-99) ;
1491
1492 v_interface_record := 'N';
1493
1494 open main_select(sob_id);
1495
1496
1497 loop
1498
1499 fetch main_select into
1500 customer_trx_id,
1501 amount_due_original,
1502 amount_due_remaining,
1503 schedule_id,
1504 customer_site_use_id,
1505 set_of_books_id,
1506 charge_id,
1507 charge_type,
1508 description,
1509 fin_charge_batch_source_id,
1510 trx_number,
1511 due_date,
1512 rate_amount,
1513 accrual_type ,
1514 frequency ,
1515 rate_flag ,
1516 v_invoice_level,
1517 trx_type_id,
1518 first ,
1519 interval ,
1520 grace ,
1521 suffix,
1522 -- TC Obsoletion
1523 -- ussgl_transaction_code,
1524 rec_trx_id, --Added as part of Enhancement #1957297
1525 sold_to_customer_id,
1526 sold_to_site_use_id,
1527 bill_to_customer_id,
1528 bill_to_site_use_id,
1529 ship_to_customer_id,
1530 ship_to_site_use_id,
1531 remit_to_address_id,
1532 bill_to_address_id,
1533 ship_to_address_id,
1534 --Added to fetch Invoice date, March 30, 99 BG
1535 --bug change 992498, commented OUT NOCOPY v_invoice_date
1536 --v_invoice_date;
1537 bill_to_contact_id,
1538 invoice_currency_code,
1539 v_exchange_date,
1540 v_exchange_rate,
1541 v_exchange_rate_type,
1542 v_trx_date;
1543
1544 IF NVL(v_old_ctrx_id,customer_trx_id) <> customer_trx_id THEN
1545 cnt:=0;
1546 END IF;
1547
1548 IF NVL(v_old_sch_id,schedule_id)=schedule_id AND NVL(v_old_ctrx_id,customer_trx_id) = customer_trx_id THEN
1549 v_insert_flag:='N';
1550 ELSIF NVL(v_old_sch_id,schedule_id)<>schedule_id AND NVL(v_old_ctrx_id,customer_trx_id) = customer_trx_id THEN
1551 v_insert_flag:='Y';
1552 ELSIF NVL(v_old_ctrx_id,customer_trx_id) <> customer_trx_id THEN
1553 v_insert_flag:='N';
1554 END IF;
1555
1556 exit when main_select%notfound;
1557
1558 if fin_charge_batch_source_id is null then
1559 error_message := ('Please define Batch Source in Define Finance Charges '||
1560 'for this finance charge '||charge_type||'.');
1561 raise oracle_error;
1562 end if;
1563
1564 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1565 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1566 'Transaction Number = '||trx_number);
1567 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1568 'customer_trx_id = '||customer_trx_id);
1569 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1570 'charge_type = '||charge_type);
1571 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1572 'schedule_id = '||schedule_id);
1573 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1574 'bill_to_customer_id = '||bill_to_customer_id);
1575 END IF;
1576
1577
1578 first_late_day := trunc(due_date) + first + grace + 1;
1579
1580 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1581 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1582 'first_late_day = '||first_late_day);
1583 END IF;
1584
1585 if rate_flag = 'Y' then -- rate
1586
1587 dm_flag := check_dm; -- checking for existing debit memo for invoice + charge
1588
1589 IF v_inv_due_date = 'INV' THEN
1590 new_due_date :=v_trx_date;
1591 ELSIF v_inv_due_date = 'DDI' THEN
1592 new_due_date := due_date;
1593 END IF;
1594
1595 -- bug fix 2113826
1596 if (dm_flag = TRUE) and (interval = 0) and (v_split_term = TRUE) THEN
1597 -- this is for split term (2113826) a dm is in interface
1598 -- but haven't accrued for this payment schedule.
1599 -- so add new line to dm in interface table (bug 2113826)
1600
1601 --bug 2515917 changed from first_late_day to due_date
1602 fin_chrg := accrue_charges(trunc(new_due_date));
1603
1604 IF fin_chrg <> 0 THEN
1605 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1606 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1607 'Adding New Line to DM for amount '||to_char(fin_chrg));
1608 END IF;
1609 END IF;
1610 get_additional_invoice_info;
1611
1612 IF rate_amount <> 0 THEN
1613 create_new_dm(rate_amount);
1614 END IF;
1615
1616 elsif (dm_flag = TRUE) and (interval > 0) then
1617 -- Debit Memo Already Exists and not a one-time charge
1618
1619 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1620 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1621 'debit memo exists/not one time charge');
1622 END IF;
1623
1624 -- get last_accrual_date for the payment_schedule_id being processed
1625 last_accrual_date := check_last_accrual;
1626
1627 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1628 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1629 'last_accrual_date = '||last_accrual_date);
1630 END IF;
1631
1632 if last_accrual_date is null then
1633 -- an accrual of payment_schedule has not been made
1634
1635 -- bug 2515917 commented out, moving within branch below
1636 -- fin_chrg:=accrue_charges(first_late_day);
1637 -- if fin_chrg <> 0 then
1638 IF v_split_term THEN
1639
1640 -- bug 2515917 changed from first_late_day to due_date
1641 fin_chrg:=accrue_charges(trunc(new_due_date));
1642
1643 -- this is a split term, add additional line to new DM.
1644 -- bug 2113826
1645 if fin_chrg <> 0 THEN
1646 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1647 THEN
1648 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1649 'Adding New Line to DM for amount '||to_char(fin_chrg));
1650 END IF;
1651
1652
1653 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1654 THEN
1655 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1656 l_module_name,'fin_chrg = '||fin_chrg);
1657 END IF;
1658 get_additional_invoice_info;
1659 create_new_dm(fin_chrg);
1660 END If;
1661 ELSE
1662 first_late_day := trunc(new_due_date) + first + grace + 1;
1663 fin_chrg:=accrue_charges(trunc(first_late_day));
1664 if fin_chrg <> 0 THEN
1665 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1666 THEN
1667 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1668 'fin_chrg = '||fin_chrg);
1669 END IF;
1670 adjust_existing_dm(fin_chrg); -- Adjust Existing Debit Memo
1671 end if;
1672 END IF;
1673
1674 --end if;
1675
1676 --Modified Logic to Use the Interval
1677 --to calculate the finance charges
1678 --modified the else statement to look like
1679 --elsif last_accrual_date + Interval < trunc(sysdate)
1680 --Modified by Babu Ganesan on March 30, 1999.
1681 elsif last_accrual_date + interval < trunc(sysdate) Then
1682 -- an accrual already exists for payment_schedule
1683 fin_chrg:=accrue_charges (last_accrual_date);
1684
1685 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1686 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1687 'fin_chrg = '||fin_chrg);
1688 END IF;
1689
1690
1691 if fin_chrg <> 0 then
1692 adjust_existing_dm(fin_chrg); -- Adjust Existing Debit Memo
1693 end if;
1694 end if;
1695
1696 elsif dm_flag = FALSE then -- Create New Debit Memo
1697 -- bug 2515917 using due_date instead of first_late_day
1698 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1699 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1700 'due_date = '||due_date);
1701 END IF;
1702 fin_chrg:=accrue_charges(trunc(new_due_date));
1703
1704 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1705 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1706 'dm_flag = FALSE');
1707 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1708 'fin_chrg = '||fin_chrg);
1709 END IF;
1710
1711 IF fin_chrg <> 0 THEN
1712 get_additional_invoice_info;
1713 create_new_dm(fin_chrg);
1714 END IF;
1715 end if;
1716
1717 else -- fixed amount (rate_flag = 'N')
1718
1719 dm_flag := check_dm; -- verify dm exists for charge and invoice
1720
1721 IF dm_flag = TRUE and interval = 0 and v_split_term = TRUE THEN
1722 -- this is for split term (2113826) a dm is in interface
1723 -- but haven't accrued for this payment schedule.
1724 -- so add new line to dm in interface table (bug 2113826)
1725 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1726 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1727 'Adding New Line to DM for amount'||to_char(rate_amount));
1728 END IF;
1729
1730 IF rate_amount <> 0 AND v_insert_flag='N' AND v_invoice_level='Y' THEN
1731 get_additional_invoice_info;
1732 create_new_dm(rate_amount);
1733 ELSIF rate_amount <> 0 AND v_invoice_level='N' THEN
1734 get_additional_invoice_info;
1735 create_new_dm(rate_amount);
1736 END IF;
1737
1738 ELSIF dm_flag = TRUE and interval > 0 then
1739
1740 -- dm already exists and not a one time charge
1741
1742 -- get last accrual_date for payment_schedule_id and charge type
1743 last_accrual_date := check_last_accrual;
1744
1745 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1746 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1747 'last_accrual_date = '||last_accrual_date);
1748 END IF;
1749
1750 if last_accrual_date is null then
1751 -- an accrual has not been made for payment_schedule_id + charge type
1752 -- but dm exists for this invoice + charge type.
1753 -- an invoice with split terms would fall to this branch.
1754 if trunc(sysdate) between first_late_day and
1755 first_late_day + interval then
1756
1757 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1758 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1759 'rate_amount = '||rate_amount);
1760 END IF;
1761 IF rate_amount <> 0 THEN
1762 IF v_split_term = TRUE THEN
1763 -- add new line to dm in interface table (bug 2113826)
1764
1765 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1766 THEN
1767 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1768 'Adding New Line to DM for amount '||to_char(rate_amount));
1769 END IF;
1770 get_additional_invoice_info;
1771 create_new_dm(rate_amount);
1772 ELSE
1773 -- adjust existing dm
1774 adjust_existing_dm(rate_amount);
1775 END IF;
1776 END IF; -- rate_amount
1777 else
1778 num_periods := missed_intervals; -- function to determine missed int
1779 fixed_amount := rate_amount * num_periods;
1780
1781 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1782 THEN
1783 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1784 'fixed_amt = '||fixed_amount);
1785 END IF;
1786
1787 if fixed_amount <> 0 then
1788 IF v_split_term = TRUE THEN
1789 -- add new line to dm in interface table (bug 2113826)
1790 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1791 THEN
1792 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1793 'Adding New Line to DM for amount '||to_char(fixed_amount));
1794 END IF;
1795 IF v_insert_flag='N' AND v_invoice_level='Y' THEN
1796 get_additional_invoice_info;
1797 create_new_dm(fixed_amount);
1798 ELSIF v_invoice_level='N' THEN
1799 get_additional_invoice_info;
1800 create_new_dm(fixed_amount);
1801 END IF;
1802
1803 ELSE
1804 IF v_insert_flag='N' AND v_invoice_level='Y' THEN
1805 -- adjust existing dm
1806 adjust_existing_dm(fixed_amount);
1807 ELSIF v_invoice_level='N' THEN
1808 adjust_existing_dm(fixed_amount);
1809 END IF;
1810
1811 END IF;
1812 end if;
1813
1814 end if;
1815 elsif first_late_day + interval < trunc(sysdate) then
1816 -- accrual already exists for payment_schedule and charge type
1817 -- determine new accrual
1818 num_periods := missed_intervals; -- function to determine missed int
1819 fixed_amount := calculate_fixed_amount(num_periods);
1820 if fixed_amount <> 0 then
1821 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1822 THEN
1823 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1824 'fixed_amt = '||fixed_amount);
1825 END IF;
1826
1827 IF v_insert_flag='N' AND v_invoice_level='Y' THEN
1828 adjust_existing_dm(fixed_amount);
1829 ELSIF v_invoice_level='N' THEN
1830 adjust_existing_dm(fixed_amount);
1831 END IF;
1832
1833 end if;
1834 end if;
1835
1836 elsif dm_flag = FALSE then
1837
1838 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1839 THEN
1840 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1841 l_module_name,'dm_flag=FALSE');
1842 END IF;
1843
1844 -- create new dm for interval = 0 or > 0
1845
1846 if trunc(sysdate) between first_late_day and first_late_day + interval
1847 then
1848
1849 IF rate_amount <> 0 THEN
1850 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1851 THEN
1852 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1853 'rate_amount = '||rate_amount);
1854 END IF;
1855
1856 get_additional_invoice_info;
1857 create_new_dm(rate_amount); -- create new debit memo
1858 END IF;
1859 else
1860 num_periods := missed_intervals; -- function to determine missed int
1861 fixed_amount := rate_amount * num_periods;
1862
1863 IF fixed_amount <> 0 THEN
1864 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1865 THEN
1866 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1867 'fixed_amount = '||fixed_amount);
1868 END IF;
1869
1870 get_additional_invoice_info;
1871 create_new_dm(fixed_amount); -- create new debit memo
1872 END IF ;
1873 end if;
1874 end if;
1875 end if;
1876 v_old_ctrx_id:=customer_trx_id;
1877 IF cnt=0 THEN
1878 v_old_sch_id:=schedule_id;
1879 END IF;
1880 cnt:=cnt+1;
1881 end loop;
1882
1883 if v_interface_record = 'Y' then
1884 -- record has been inserted in interface tables, so call Auto Invoice
1885
1886 FOR v_finchrg_batch_src_id IN c_batch_source_id
1887
1888
1889 LOOP
1890
1891 -- bug 2323254 changed null to '', 1 to '1', 'Y' to 'YES' and added extra ''
1892 FND_REQUEST.set_org_id(v_org_id); -- PSKI MOAC Changes
1893 v_auto_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'RAXMTR','','', FALSE,
1894 '1',v_org_id, v_finchrg_batch_src_id.batch_source_id,v_finchrg_batch_src_id.name, trunc(sysdate),
1895 '','','','','','','','','','',
1896 '','','','','','','','','','', 'YES','');
1897
1898 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1899 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'!!!!! '||
1900 'SUBMITTING REQUEST '||v_auto_id);
1901 END IF;
1902
1903 if v_auto_id <> 0 then
1904
1905 LOOP
1906 commit ; -- Added for Bug 2157100
1907
1908 process_status := FND_CONCURRENT.WAIT_FOR_REQUEST(v_auto_id, 20, 0,
1909 rphase,rstatus, dphase, dstatus, message);
1910 exit when process_status;
1911
1912 END LOOP;
1913
1914 if (dphase = 'COMPLETE' and dstatus <> 'NORMAL') then
1915 -- end with warning to user, but process ends successfully
1916 error_message := 'Please review AutoInvoice Interface Lines form '||
1917 'for Batch Source ' ||
1918 v_finchrg_batch_src_id.batch_source_id||
1919 '. Correct errors and resubmit'||
1920 ' AutoInvoice for this Batch Source';
1921 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1922 'ERROR:- '||error_message);
1923 end if;
1924
1925 --Bug 6348669
1926 IF (dphase = 'COMPLETE' and dstatus = 'NORMAL') THEN
1927 l_error_cnt := 0;
1928 select count(*) into l_error_cnt
1929 from fnd_concurrent_requests
1930 where parent_request_id = v_auto_id and status_code = 'E';
1931
1932 IF (l_error_cnt <> 0) THEN
1933 cleanup(v_finchrg_batch_src_id.name);
1934 commit;
1935 retcode := 2;
1936 error_message := 'Errors in Submitted Request for Autoinvoice with '||
1937 'Batch Source - '||
1938 v_finchrg_batch_src_id.batch_source_id||'-'||
1939 FND_MESSAGE.GET;
1940 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1941 'ERROR:- '||error_message);
1942 RAISE ORACLE_ERROR;
1943 END IF;
1944 END IF;
1945
1946 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1948 '!!!!! Completed request '||v_auto_id);
1949 END IF;
1950
1951 else
1952
1953 cleanup(v_finchrg_batch_src_id.name);
1954 -- retcode := 2;
1955 error_message := 'Failed to Submit Request for Autoinvoice with '||
1956 'Batch Source - '
1957 ||v_finchrg_batch_src_id.batch_source_id||'-'||
1958 FND_MESSAGE.GET;
1959 -- RAISE ORACLE_ERROR;
1960 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1961 'ERROR:- '||error_message);
1962
1963 end if;
1964
1965 END LOOP;
1966
1967
1968 /*
1969 LOOP
1970
1971 process_status := FND_CONCURRENT.GET_REQUEST_STATUS(v_auto_id, NULL, NULL,
1972 rphase, rstatus, dphase, dstatus, message);
1973
1974 exit when ((process_status and dphase = 'COMPLETE') or NOT(process_status));
1975 dbms_lock.sleep(5);
1976
1977 END LOOP;
1978
1979
1980 if (dphase <> 'COMPLETE' and NOT process_status) then
1981 -- end with warning to user, but process ends successfully
1982 error_message := 'Please review AutoInvoice Interface Lines form for '||
1983 ' Batch Source '||
1984 ||fin_charge_batch_source_id||
1985 '. Correct errors and resubmit '||
1986 'AutoInvoice for this Batch Source.';
1987 end if;
1988 */
1989
1990
1991 else
1992 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,
1993 'Not submitting AutoInvoice since no new debit memos being created. '||
1994 'Process Complete.');
1995 end if; -- end v_interface_record = 'Y'
1996
1997
1998 if main_select%rowcount = 0 then
1999 null;
2000 end if;
2001 close main_select;
2002 -- The following will submit Accrue finance charge detail report
2003 FND_REQUEST.set_org_id(v_org_id); -- PSKI MOAC Changes
2004 v_conc_req_id := FND_REQUEST.SUBMIT_REQUEST
2005 ('FV','FVDCACCD','','',FALSE, set_of_books_id) ;
2006 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2007 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_EVENT, l_module_name,
2008 'Submitting Detail report '||v_conc_req_id) ;
2009 END IF;
2010
2011 if v_conc_req_id = 0 then
2012 error_message := 'Error in Submitting Accrue Finance '||
2013 'charge Detail Report ' ;
2014 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
2015 error_message) ;
2016 end if ;
2017 retcode := 0;
2018 errbuf := 'Accrual Process Successfully Completed';
2019
2020 commit;
2021
2022 exception
2023 when ORACLE_ERROR then
2024 errbuf := error_message;
2025 retcode := 2;
2026 rollback;
2027 if main_select%ISOPEN then
2028 close main_select;
2029 end if;
2030 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception1',
2031 errbuf);
2032
2033 when others then
2034
2035 if main_select%ISOPEN then
2036 close main_select;
2037 end if;
2038
2039 error_message := statement_no||'-'||sqlerrm;
2040 errbuf := error_message;
2041 retcode := 2;
2042 rollback;
2043 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception2',
2044 errbuf);
2045 end accrue_finance_charge;
2046
2047
2048
2049 /*****************************************************************************/
2050 /* Begin Assign Finance Charges Process */
2051 /*****************************************************************************/
2052 procedure assign_finance_charge(errbuf OUT NOCOPY varchar2,
2053 retcode out NOCOPY number) is
2054 l_module_name VARCHAR2(200) ;
2055 v_set_bks_id gl_ledgers.ledger_id%TYPE; --PSKI LA Changes
2056 v_set_bks_name gl_ledgers.name%TYPE;
2057 v_currency_code gl_ledgers.currency_code%TYPE;
2058 v_req_id number;
2059
2060 cursor c1 is
2061 (select distinct hzca.cust_Account_id customer_id
2062 from hz_cust_accounts hzca, fv_finance_charge_controls fcc
2063 where fcc.enabled_flag = 'Y'
2064 and fcc.set_of_books_id = sob_id
2065 and hzca.status = 'A'
2066 and exists (select 'x' from fv_cust_finance_chrgs
2067 where hzca.cust_account_id = customer_id
2068 and fcc.charge_id = charge_id
2069 and set_of_books_id = sob_id)
2070 and not exists (select 'x' from fv_finance_chrg_cust_classes
2071 where customer_class = hzca.customer_class_code
2072 and enabled_flag = 'Y'
2073 and charge_id = fcc.charge_id
2074 and set_of_books_id = sob_id));
2075 begin
2076 l_module_name := g_module_name || 'assign_finance_charge';
2077
2078 retcode := 0;
2079 errbuf := null;
2080
2081 --sob_id := to_number(fnd_profile.value('GL_SET_OF_BKS_ID'));
2082 --v_org_id := to_number(fnd_profile.value('ORG_ID'));
2083 v_org_id := MO_GLOBAL.get_current_org_id; -- PSKI MOAC Changes
2084 MO_UTILS.get_ledger_info(v_org_id,sob_id,v_set_bks_name); -- PSKI LA Changes
2085 v_set_bks_id := sob_id; -- PSKI LA Changes
2086 assign_date := trunc(sysdate);
2087
2088 statement_no := 'Customer';
2089
2090
2091
2092 -- Added the following to fix bug# 1383838
2093 -- update waive_flag = 'Y' when there is a change in the customer class
2094 -- for a customer who had a finance charge for the previous class
2095 begin
2096
2097 for customer in c1
2098 LOOP
2099
2100 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2101 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2102 'Updating waive flag for: '||customer.customer_id);
2103 END IF;
2104
2105 update fv_cust_finance_chrgs
2106 set waive_flag = 'Y'
2107 where customer_id = customer.customer_id
2108 and charge_id not in
2109 (select charge_id from fv_finance_chrg_cust_classes, hz_cust_Accounts hzca
2110 where hzca.cust_Account_id = customer.customer_id
2111 and customer_class = hzca.customer_class_code
2112 and enabled_flag = 'Y'
2113 and set_of_books_id = sob_id);
2114
2115 END LOOP;
2116
2117 end;
2118
2119 -- End addition
2120 insert into fv_cust_finance_chrgs (customer_id, charge_id, waive_flag,
2121 created_by, creation_date, last_updated_by, last_update_date,
2122 set_of_books_id,org_id)
2123 select hzca.cust_Account_id, fcc.charge_id, 'N', 0, assign_date, 0, sysdate, sob_id,
2124 fcc.org_id
2125 from hz_cust_accounts hzca, fv_finance_charge_controls fcc
2126 where fcc.enabled_flag = 'Y'
2127 and fcc.set_of_books_id = sob_id
2128 and hzca.status = 'A'
2129 and not exists (select 'x' from fv_cust_finance_chrgs
2130 where hzca.cust_account_id = customer_id
2131 and fcc.charge_id = charge_id
2132 and set_of_books_id = sob_id)
2133 and exists (select 'x' from fv_finance_chrg_cust_classes
2134 where customer_class = hzca.customer_class_code
2135 and enabled_flag = 'Y'
2136 and charge_id = fcc.charge_id
2137 and set_of_books_id = sob_id);
2138
2139 statement_no := 'Invoice';
2140
2141 insert into fv_invoice_finance_chrgs
2142 (customer_id, customer_trx_id, charge_id,waive_flag, created_by,
2143 creation_date,last_updated_by, last_update_date, set_of_books_id,org_id)
2144 select ract.bill_to_customer_id, ract.customer_trx_id, fcfc.charge_id,
2145 fcfc.waive_flag, 0 , assign_date, 0 , sysdate, sob_id, v_org_id
2146 from ra_customer_trx ract,
2147 fv_cust_finance_chrgs fcfc,
2148 fv_finance_charge_controls fcc,
2149 ra_cust_trx_types rctt
2150 where ract.bill_to_customer_id = fcfc.customer_id
2151 and fcfc.set_of_books_id = sob_id
2152 and ract.cust_trx_type_id = rctt.cust_trx_type_id
2153 and ract.complete_flag = 'Y'
2154 and fcc.enabled_flag = 'Y'
2155 and fcc.charge_id = fcfc.charge_id
2156 and rctt.type in ('DM','INV')
2157 and nvl(ract.interface_header_attribute3,'XX') not in (select charge_type
2158 from fv_finance_charge_controls
2159 where set_of_books_id = sob_id)
2160 and not exists (select 'x'
2161 from fv_invoice_finance_chrgs
2162 where ract.customer_trx_id = customer_trx_id
2163 and ract.bill_to_customer_id=customer_id
2164 and fcfc.charge_id = charge_id
2165 and set_of_books_id = sob_id);
2166
2167
2168 /*
2169 Removing this per discussions 1/23 with K. Conquest/H.Gensler
2170 and ract.trx_date >= (select nvl(last_assignment_date,trunc(sysdate))
2171 from fv_ar_controls)
2172 */
2173
2174 statement_no := 'Control';
2175
2176 commit;
2177
2178 --v_set_bks_name := fnd_profile.value('GL_SET_OF_BKS_NAME'); --PSKI LA Changes
2179
2180 /* select currency_code
2181 into v_currency_code
2182 from gl_sets_of_books
2183 where set_of_books_id = sob_id; */
2184
2185
2186 IF v_set_bks_id is NOT NULL THEN --PSKI LA changes
2187 SELECT currency_code
2188 INTO v_currency_code
2189 FROM gl_ledgers_public_v
2190 WHERE ledger_id = sob_id;
2191 END IF;
2192
2193
2194 FND_REQUEST.set_org_id(v_org_id); -- PSKI MOAC Changes
2195
2196 v_req_id := fnd_request.submit_request('FV',
2197 'FVDCAFCO',
2198 '','',
2199 FALSE,
2200 v_set_bks_name,
2201 v_currency_code,
2202 chr(0),'','','','','','','',
2203 '','','','','','','','','','',
2204 '','','','','','','','','','',
2205 '','','','','','','','','','',
2206 '','','','','','','','','','',
2207 '','','','','','','','','','',
2208 '','','','','','','','','','',
2209 '','','','','','','','','','',
2210 '','','','','','','','','','',
2211 '','','','','','','','','','');
2212
2213 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2214 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2215 'v_req_id = '||v_req_id);
2216 END IF;
2217
2218
2219 IF v_req_id = 0 THEN
2220 error_message := 'Submitting the concurrent process, FVDCAFCO, '||
2221 'failed contact System Admin.';
2222 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, error_message);
2223 RAISE ORACLE_ERROR;
2224 END IF;
2225
2226
2227
2228 exception
2229 when ORACLE_ERROR then
2230 errbuf := error_message;
2231 retcode := 2;
2232 rollback;
2233 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception1',
2234 errbuf);
2235
2236 when others then
2237 error_message := statement_no||'-'||sqlerrm;
2238 errbuf := error_message;
2239 retcode := 2;
2240 rollback;
2241 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception2',
2242 errbuf);
2243
2244 FND_FILE.CLOSE;
2245
2246 end assign_finance_charge;
2247 BEGIN
2248 g_module_name := 'fv.plsql.fv_dc_assign_finance_chrg.';
2249
2250
2251 END fv_dc_assign_finance_chrg;