1 package body ar_bfb_utils_pvt as
2 /* $Header: ARBFBUTB.pls 120.34.12020000.9 2013/01/10 20:02:10 hvaladip ship $ */
3
4 /* Global table definitions */
5 TYPE l_trx_id_type IS TABLE OF ra_customer_trx_all.customer_trx_id%type
6 INDEX BY BINARY_INTEGER;
10 INDEX BY BINARY_INTEGER;
7 TYPE l_term_id_type IS TABLE OF ra_customer_trx_all.term_id%type
8 INDEX BY BINARY_INTEGER;
9 TYPE hz_customer_prof_cach is table of hz_customer_profiles.cons_bill_level%TYPE
11
12 temp_bill_level_table hz_customer_prof_cach;
13
14 TYPE billing_cycle_id_cach is table of ra_terms.billing_cycle_id%type
15 INDEX BY BINARY_INTEGER;
16 temp_billing_cycle_id_tab billing_cycle_id_cach;
17
18 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
19
20 FUNCTION ar_day_of_week( the_date in date ) return number is
21 m number ;
22 y number ;
23 d number;
24 l_dow NUMBER;
25
26 begin
27
28 select to_char(the_date, 'MM' ) - 3 into m from dual;
29 select to_char(the_date, 'YYYY' ) into y from dual;
30 select to_char(the_date, 'DD' ) into d from dual;
31
32 if ( m < 0 ) then
33 m := m + 12;
34 y := y - 1;
35 end if;
36
37 l_dow := 1 +
38 ( d + floor( ( 19 + 31 * m ) / 12 ) +
39 y + floor(y/4) - floor(y/100) + floor(y/400) ) mod 7 ;
40
41 return l_dow;
42 END ar_day_of_week;
43
44
45 function get_billing_date (p_billing_cycle_id IN NUMBER,
46 p_billing_date IN DATE
47 ) RETURN DATE IS
48
49 l_billing_date DATE;
50
51 BEGIN
52
53 -- passed in p_billing_date is a valid date, use it
54 --Modified the logic to pick start_date when billing_date passed is less than start_date
55 select min(trunc(billable_date))
56 into l_billing_date
57 from ar_cons_bill_cycles_b cy,
58 ar_cons_bill_cycle_dates cyd
59 where cy.billing_cycle_id = p_billing_cycle_id
60 and cy.billing_cycle_id = cyd.billing_cycle_id
61 and cyd.billable_date
62 between trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate)))
63 and (trunc(greatest(nvl(p_billing_date,sysdate), nvl(cy.START_DATE, sysdate))) + decode(cy.cycle_frequency,
64 'DAILY', 1*nvl(cy.REPEAT_DAILY,0),
65 'WEEKLY', 7*nvl(cy.REPEAT_WEEKLY,0),
66 'MONTHLY', 31*nvl(cy.REPEAT_MONTHLY,0),
67 0));
68 return l_billing_date;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72
73 l_billing_date := null;
74
75 return l_billing_date;
76
77 END get_billing_date;
78
79 function get_bill_process_date (p_billing_cycle_id IN NUMBER,
80 p_billing_date IN DATE,
81 p_last_bill_date IN DATE DEFAULT sysdate
82 ) RETURN DATE IS
83
84 l_billing_date DATE;
85
86 BEGIN
87
88 if p_billing_date > nvl(p_last_bill_date, sysdate) then
89 select max(billable_date)
90 into l_billing_date
91 from ar_cons_bill_cycle_dates
92 where billing_cycle_id = p_billing_cycle_id
93 and billable_date between trunc(p_last_bill_date) and trunc(p_billing_date);
94 else
95 select max(billable_date)
96 into l_billing_date
97 from ar_cons_bill_cycle_dates
98 where billing_cycle_id = p_billing_cycle_id
99 and billable_date between trunc(p_billing_date) and trunc(p_last_bill_date);
100 end if;
101
102 return l_billing_date;
103 END;
104
105 -- checks if entered date is a valid billing date
106 function is_valid_billing_date(p_billing_cycle_id IN NUMBER,
107 p_entered_date IN DATE) RETURN VARCHAR2 IS
108 BEGIN
109
110 return('Y');
111
112
113 END;
114
115 function get_due_date ( p_billing_date in DATE,
116 p_payment_term_id in NUMBER) RETURN DATE IS
117
118 dued NUMBER;
119 duedom NUMBER;
120 duemf NUMBER;
121 due_date DATE;
122 l_char_date varchar2(30);
123 l_duedom NUMBER;
124 begin
125
126 select due_days,
127 due_day_of_month,
128 due_months_forward
129 into dued,
130 duedom,
131 duemf
132 from ra_terms_lines
133 where term_id = p_payment_term_id;
134
135 l_duedom := duedom ;
136 if dued is not null then
137 due_date := p_billing_date + dued;
138 elsif duedom is not null then
139 -- if duedom is greater than last day of that month, change it to last day.
140 if duedom > to_number(substr(to_char(last_day(p_billing_date),'DD/MM/YYYY'),1,2)) then
141 duedom := to_number(substr(to_char(last_day(p_billing_date),'DD/MM/YYYY'),1,2));
142 end if;
143
144 l_char_date := substr(to_char(p_billing_date,'MM/DD/YYYY'),1,3) ||
145 duedom || substr(to_char(p_billing_date,'MM/DD/YYYY'),6,5);
146 due_date := to_date(l_char_date,'MM/DD/YYYY');
147 due_date := add_months(due_date, duemf);
148
149 /*Bug 12344350 : Logic to get exact date as due_day_of_month*/
150 if l_duedom < to_number(substr(to_char(due_date,'DD/MM/YYYY'),1,2)) then
151 l_char_date := (substr(to_char(due_date,'MM/DD/YYYY'),1,3) ||
152 l_duedom || substr(to_char(due_date,'MM/DD/YYYY'),6,5));
153 due_date := to_date(l_char_date,'MM/DD/YYYY');
154 end if;
155
156 end if;
157
158 return due_date;
159 end;
160
161 function is_payment_term_bfb( p_payment_term_id IN NUMBER) RETURN VARCHAR2 IS
162
166
163 bill_cycle_id NUMBER;
164
165 BEGIN
167 select billing_cycle_id
168 into bill_cycle_id
169 from ra_terms
170 where term_id = p_payment_term_id;
171
172 if bill_cycle_id is not null then
173 RETURN 'Y';
174 else
175 RETURN 'N';
176 end if;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 RETURN 'N';
181 END is_payment_term_bfb;
182
183 /* Overloaded function, parameter passed in is NAME instead of ID */
184 function is_payment_term_bfb( p_payment_term_name IN VARCHAR2) RETURN VARCHAR2 IS
185
186 bill_cycle_id NUMBER;
187
188 BEGIN
189
190 select billing_cycle_id
191 into bill_cycle_id
192 from ra_terms
193 where name = p_payment_term_name;
194
195 if bill_cycle_id is not null then
196 RETURN 'Y';
197 else
198 RETURN 'N';
199 end if;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 RETURN 'N';
204 END is_payment_term_bfb;
205
206 function get_bill_level( p_cust_account_id IN NUMBER,
207 p_site_use_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
208
209 bfb_level VARCHAR2(1);
210
211 BEGIN
212
213 IF PG_DEBUG in ('Y', 'C') THEN
214 arp_standard.debug('ar_bfb_utils_pvt.get_bill_level(+)');
215 END IF;
216
217 IF ( p_cust_account_id < -2147483647 or p_cust_account_id > 2147483647 ) then
221 select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
218 arp_standard.debug('Customer Account Id is outside the sequence range.');
219
220 begin
222 into bfb_level
223 from hz_customer_profiles cp
224 where cp.cust_account_id = p_cust_account_id
225 and cp.site_use_id IS NULL;
226 exception
227 when no_data_found then
228 bfb_level := 'N';
229 end;
230
231 return bfb_level;
232 END IF;
233
234 IF (temp_bill_level_table.EXISTS(p_cust_account_id) = FALSE) then
235 -- the cons_bill_level is driven by the value at the account level profile
236 select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
237 into bfb_level
238 from hz_customer_profiles cp
239 where cp.cust_account_id = p_cust_account_id
240 and cp.site_use_id IS NULL;
241
242 temp_bill_level_table(p_cust_account_id) := bfb_level;
243 END IF;
244 IF PG_DEBUG in ('Y', 'C') THEN
245 arp_standard.debug('ar_bfb_utils_pvt.get_bill_level(-)');
246 END IF;
247 return temp_bill_level_table(p_cust_account_id);
248
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251 /*bug 12371053 was changed by shaoclbj as below*/
252 IF p_cust_account_id is not null THEN
253 temp_bill_level_table(p_cust_account_id) := 'N';
254 return temp_bill_level_table(p_cust_account_id);
255 ELSE
256 return 'N';
257 END IF;
258 WHEN OTHERS THEN
259 arp_standard.debug('EXCEPTION OTHERS in ar_bfb_utils_pvt.get_bill_level :'||SQLERRM);
260 RAISE;
261 END get_bill_level;
262
263 function get_billing_cycle (p_payment_term_id in NUMBER) RETURN NUMBER IS
264
265 bill_cycle_id NUMBER;
266 BEGIN
267 IF PG_DEBUG in ('Y', 'C') THEN
268 arp_standard.debug('get_billing_cycle(+)');
269 END IF;
270 IF (temp_billing_cycle_id_tab.EXISTS(p_payment_term_id) = FALSE) THEN
271 select billing_cycle_id
272 into bill_cycle_id
273 from ra_terms
274 where term_id = p_payment_term_id;
275
276 temp_billing_cycle_id_tab(p_payment_term_id) := bill_cycle_id;
277 END IF;
278
279 IF PG_DEBUG in ('Y', 'C') THEN
280 arp_standard.debug('get_billing_cycle(-)');
281 END IF;
282 return temp_billing_cycle_id_tab(p_payment_term_id);
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 arp_standard.debug('NO_DATA_FOUND EXCEPTION in get_billing_cycle');
286 return 0;
287 WHEN OTHERS THEN
288 arp_standard.debug('EXCEPTION OTHERS in get_billing_cycle');
289 return 0;
290 END get_billing_cycle;
291
292 function get_cycle_type (p_bill_cycle_id IN NUMBER) RETURN VARCHAR2 IS
293
294 cycle_type VARCHAR2(30);
295
296 BEGIN
297
298 select bill_cycle_type
299 into cycle_type
300 from ar_cons_bill_cycles_b
301 where billing_cycle_id = p_bill_cycle_id;
302
303 return cycle_type;
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 return null;
307 END get_cycle_type;
308
309 function get_open_rec(p_cust_trx_type_id IN NUMBER) RETURN VARCHAR2 IS
310
311 open_rec VARCHAR2(1);
312 BEGIN
313 select nvl(accounting_affect_flag,'Y')
314 into open_rec
315 from ra_cust_trx_types
316 where cust_trx_type_id = p_cust_trx_type_id;
317
318 return open_rec;
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 return 'Y';
322 END get_open_rec;
323
324
325 function get_default_term( p_trx_type_id IN NUMBER,
326 p_trx_date IN DATE,
327 p_org_id IN NUMBER,
328 p_bill_to_site IN NUMBER,
329 p_bill_to_customer IN NUMBER) RETURN NUMBER IS
330
331 l_default_term NUMBER;
332 BEGIN
333
334 select nvl(su.payment_term_id,
335 decode(spt.billing_cycle_id,
336 -- if cycle is NULL
337 NULL, nvl(sp.standard_terms,
338 decode(apt.billing_cycle_id,
339 -- if cycle is NULL
340 NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94) ,
341 -- if cycle is NOT NULL
342 -92)),
343 -- if cycle is NOT NULL
344 decode(ap.cons_bill_level,
345 -- if bill level = Account
346 'ACCOUNT', nvl(ap.standard_terms, -93),
347 -- if bill level = Site
348 'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
349 -- if bill level is not set
350 , -91)))
351 into l_default_term
352 from ra_cust_trx_types tt,
353 hz_customer_profiles ap,
354 hz_cust_site_uses su,
355 ra_terms_b apt,
356 ra_terms_b spt,
357 ( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
358 su2.site_use_id profile_bill_to_site_use_id
359 from hz_customer_profiles cp,
360 hz_cust_site_uses su1,
361 hz_cust_site_uses su2
362 where cp.site_use_id = su1.site_use_id
363 and cp.status ='A'
364 and su1.cust_acct_site_id =su2.cust_acct_site_id
365 --bug11698651 start--
366 --add a condition to make sure the return result is only one row
370 ) sp
367 and su1.site_use_id = su2.site_use_id
368 --bug11698651 end--
369 and su2.site_use_code = 'BILL_TO'
371 where p_trx_type_id = tt.cust_trx_type_id
372 and p_org_id = tt.org_id
373 and p_bill_to_site = su.site_use_id
374 and p_bill_to_customer = ap.cust_account_id
375 and ap.site_use_id is null
376 and p_bill_to_customer = sp.cust_account_id (+)
377 and su.site_use_id = sp.profile_bill_to_site_use_id (+)
378 and ap.standard_terms = apt.term_id (+)
379 and sysdate between nvl(apt.start_date_active, sysdate) and
380 nvl(apt.end_date_active, sysdate)
381 and sp.standard_terms = spt.term_id (+)
382 and sysdate between nvl(spt.start_date_active, sysdate) and
383 nvl(spt.end_date_active, sysdate);
384
385 return l_default_term;
386
387 END get_default_term;
388
389 /* Procedure that bulk updates the term_ids on imported transactions
390 using the predefined algorithm for BFB/ECBI. Takes a request
391 ID in and processes all invoices in that request batch. Also
392 inserts errors into ra_interface_errors for those situations
393 where the term is in conflict with the setups */
394
395 PROCEDURE validate_and_default_term( p_request_id IN NUMBER,
396 p_error_count IN OUT NOCOPY NUMBER)
397 IS
398
399 CURSOR c_terms(p_request_id NUMBER) IS
400 select
401 decode(invt.billing_cycle_id,
402 NULL, decode(decode(ap.cons_bill_level,'SITE',sp.override_terms,ap.override_terms),
403 'Y', trx.term_id,
404 nvl(su.payment_term_id,
405 decode(spt.billing_cycle_id,
406 NULL, nvl(sp.standard_terms,
407 decode(apt.billing_cycle_id,
408 NULL, nvl(nvl(ap.standard_terms, tt.default_term) , -94),
409 -92)),
410 decode(ap.cons_bill_level,
411 'ACCOUNT', nvl(ap.standard_terms, -93),
412 'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95),
413 -91)))),
414 nvl(su.payment_term_id,
415 decode(spt.billing_cycle_id,
416 NULL, DECODE(APT.BILLING_CYCLE_ID,
417 NULL, nvl(sp.standard_terms, nvl(nvl(ap.standard_terms, tt.default_term), -94)),
418 DECODE(AP.CONS_BILL_LEVEL,
419 'ACCOUNT', NVL(AP.STANDARD_TERMS, -93),
420 'SITE', NVL(AP.STANDARD_TERMS, -93),
421 -91)),
422 decode(ap.cons_bill_level,
423 'ACCOUNT', nvl(ap.standard_terms, -93),
424 'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
425 , -91)))) new_term_id, trx.customer_trx_id
426 from ra_customer_trx trx,
427 ra_cust_trx_types tt,
428 hz_customer_profiles ap,
429 hz_cust_site_uses su,
430 ra_terms_b sut,
431 ra_terms_b apt,
432 ra_terms_b spt,
433 ra_terms_b ttt,
434 ra_terms_b invt,
435 ( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
436 su2.site_use_id profile_bill_to_site_use_id
437 from hz_customer_profiles cp,
438 hz_cust_site_uses su1,
439 hz_cust_site_uses su2
440 where cp.site_use_id = su1.site_use_id
441 and cp.status ='A'
442 and su1.cust_acct_site_id =su2.cust_acct_site_id
443 and su2.site_use_code = 'BILL_TO'
444 ) sp
445 where trx.request_id = p_request_id
446 and trx.previous_customer_trx_id IS NULL -- invoices only
447 and trx.term_id = invt.term_id
448 and trx.cust_trx_type_id = tt.cust_trx_type_id
449 and trx.org_id = tt.org_id
450 and tt.default_term = ttt.term_id (+)
451 and trx.trx_date between nvl(ttt.start_date_active, trx.trx_date) and
452 nvl(ttt.end_date_active, trx.trx_date)
453 and trx.bill_to_site_use_id = su.site_use_id
454 and su.payment_term_id = sut.term_id (+)
455 and trx.trx_date between nvl(sut.start_date_active, trx.trx_date) and
456 nvl(sut.end_date_active, trx.trx_date)
457 and trx.bill_to_customer_id = ap.cust_account_id
458 and ap.site_use_id is null
459 and NVL(ap.cons_inv_flag, 'N') = 'Y' -- 7575555
460 and trx.bill_to_customer_id = sp.cust_account_id (+)
461 and trx.bill_to_site_use_id = sp.profile_bill_to_site_use_id (+)
462 and ap.standard_terms = apt.term_id (+)
463 and trx.trx_date between nvl(apt.start_date_active, trx.trx_date) and
464 nvl(apt.end_date_active, trx.trx_date)
465 and sp.standard_terms = spt.term_id (+)
466 and trx.trx_date between nvl(spt.start_date_active, trx.trx_date) and
467 nvl(spt.end_date_active, trx.trx_date);
468
469 t_trx_id l_trx_id_type;
470 t_term_id l_term_id_type;
471
472 l_rows_selected number;
473 l_rows_processed number;
474 l_rows_rejected number := 0;
475 l_rows_reject number := 0;
476 l_rows_updated number;
477 l_msg_91 fnd_new_messages.message_text%type;
478 l_msg_92 fnd_new_messages.message_text%type;
479 l_msg_93 fnd_new_messages.message_text%type;
480 l_msg_94 fnd_new_messages.message_text%type;
481 l_msg_95 fnd_new_messages.message_text%type;
482 l_msg_96 fnd_new_messages.message_text%type;
483 BEGIN
484 IF PG_DEBUG in ('Y', 'C') THEN
485 arp_standard.debug('ar_bfb_utils_pvt.validate_and_default_term()+');
489 /* Fetch rows (transactions) for processing */
486 arp_standard.debug(' request_id = ' || p_request_id);
487 END IF;
488
490 /* 7575555 - we now only fetch records where the account-level cons_inv_flag = Y.
491 others are skipped (not defaulted at all). In general, a transaction must
492 have a term assigned to it long before this code executes. */
493 OPEN c_terms(p_request_id);
494 FETCH c_terms BULK COLLECT INTO
495 t_term_id,
496 t_trx_id;
497
498 l_rows_selected := c_terms%ROWCOUNT;
499
500 CLOSE c_terms;
501
502 IF l_rows_selected > 0
503 THEN
504 /* Process what we've got */
505 IF PG_DEBUG in ('Y', 'C') THEN
506 arp_standard.debug(' rows selected = ' || l_rows_selected);
507 END IF;
508
509 /* Bulk update of transaction terms
510 NOTE: This excludes those in error or null */
511 FORALL i IN t_trx_id.FIRST..t_trx_id.LAST
512 UPDATE ra_customer_trx trx
513 SET term_id = t_term_id(i)
514 WHERE trx.customer_trx_id = t_trx_id(i)
515 AND NVL(t_term_id(i), -99) > 0;
516
517 l_rows_processed := SQL%ROWCOUNT;
518
519 /* Now, for those that we didn't process because they
520 were in error, we need to insert the correct error
521 message into RA_INTERFACE_ERRORS */
522 IF NVL(l_rows_processed,0) < l_rows_selected
523 THEN
524 /* Get error messages for bulk insert */
525 fnd_message.set_name('AR', 'AR_BFB_TERM_BILL_LEVEL_NULL');
526 l_msg_91 := fnd_message.get;
527 fnd_message.set_name('AR', 'AR_BFB_TERM_BILL_LEVEL_WRONG');
528 l_msg_92 := fnd_message.get;
529 fnd_message.set_name('AR', 'AR_BFB_TERM_MISSING_AT_ACCT');
530 l_msg_93 := fnd_message.get;
531 fnd_message.set_name('AR', 'AR_BFB_TERM_NO_DEFAULT');
532 l_msg_94 := fnd_message.get;
533 fnd_message.set_name('AR', 'AR_BFB_TERM_NO_BFB_DEFAULT');
534 l_msg_95 := fnd_message.get;
535
536 /* process the errors */
537 FORALL err IN t_trx_id.FIRST..t_trx_id.LAST
538 INSERT into RA_INTERFACE_ERRORS
539 (interface_line_id,
540 message_text,
541 org_id)
542 SELECT line.customer_trx_line_id,
543 DECODE(t_term_id(err),
544 -91,l_msg_91,
545 -92,l_msg_92,
546 -93,l_msg_93,
547 -94,l_msg_94,
548 -95,l_msg_95),
549 line.org_id
550 FROM RA_CUSTOMER_TRX_LINES line
551 WHERE line.customer_trx_id = t_trx_id(err)
552 AND t_term_id(err) < 0;
553
554 l_rows_rejected := SQL%ROWCOUNT;
555 END IF;
556 ELSE
557 /* Nothing to process */
558 IF PG_DEBUG in ('Y', 'C') THEN
559 arp_standard.debug(' NO ROWS TO PROCESS');
560 END IF;
561 END IF;
562
563 p_error_count := l_rows_rejected;
564
565 IF PG_DEBUG in ('Y', 'C') THEN
566 arp_standard.debug(' rows processed = ' || l_rows_updated);
567 arp_standard.debug(' rows rejected = ' || p_error_count);
568 arp_standard.debug('ar_bfb_utils_pvt.validate_and_default_term()-');
569 END IF;
570
571
572 END validate_and_default_term;
573
574 PROCEDURE POPULATE(p_billing_cycle_id IN NUMBER) IS
575
576 /* ----Number of Years Billing dates will be generated for ---*/
577 l_years_to_process NUMBER := 10;
578
579 l_billing_month_year varchar2(50);
580 trxday NUMBER;
581 trxmo NUMBER;
582 billday NUMBER;
583 bill_date DATE;
584 i number;
585 k number;
586 l_current_date date;
587 l_billing_week number;
588 l_start_date date;
589 l_cycle_frequency varchar2(30);
590 l_next_billing_date date;
591 l_last_day varchar2(1);
592 l_month_days varchar2(10);
593 l_repeat_frequency number;
594 l_skip_weekends varchar2(1);
595 l_last_billed_date date;
596 l_billing_month varchar2(20);
597 l_billing_year varchar2(20);
598
599 l_day_type varchar2(1);
600 l_char_date1 varchar2(30);
601 l_mon_day_of_week varchar2(30);
602 l_last_day_month_insert_flag varchar2(1); -- Added for Bug 7476810
603 TYPE day_tab_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
604 daytab day_tab_type;
605
606 TYPE week_tab_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
607 week_tab week_tab_type;
608
609 BEGIN
610
611 ------------------------------------------------
612 ---Look at billing cycle ID and find the type
613 ------------------------------------------------
614 select cycle_frequency ,
615 decode(cycle_frequency, 'MONTHLY', repeat_monthly ,
616 'WEEKLY', repeat_weekly,
617 'DAILY', repeat_daily ) repeat_frequency,
618 skip_weekends,
619 day_type,
620 trunc(start_date)
621 into l_cycle_frequency ,
622 l_repeat_frequency , l_skip_weekends, l_day_type,l_start_date
623 from ar_cons_bill_cycles_b
624 where billing_cycle_id = p_billing_cycle_id;
625
626
627 ------------------------------------------------
628 ---DAILY
629 ------------------------------------------------
630 IF l_cycle_frequency = 'DAILY' THEN
631
632 l_next_billing_date := l_start_date;
633
634 loop
635
636 ------------------------------------------------
637 ---Skip weekends flag is Y
638 ------------------------------------------------
642 l_next_billing_date := l_next_billing_date + 2;
639 if l_skip_weekends = 'Y' THEN
640
641 if ar_day_of_week(l_next_billing_date) = 6 then
643 elsif
644 ar_day_of_week(l_next_billing_date) = 7 THEN
645 l_next_billing_date := l_next_billing_date + 1;
646 end if;
647
648 end if;
649
650 --------------------------------------------------------
651 --Exit if required years processed
652 --------------------------------------------------------
653
654 if l_next_billing_date >= trunc( l_start_date + 365*l_years_to_process) then
655 exit;
656 end if;
657
658 --------------------------------------------------------
659 --Insert the Bill Date in the table
660 --------------------------------------------------------
661 INSERT INTO AR_CONS_BILL_CYCLE_DATES (
662 BILLING_CYCLE_ID ,
663 BILLABLE_DATE ,
664 CREATED_BY ,
665 CREATION_DATE ,
666 LAST_UPDATE_LOGIN,
667 LAST_UPDATE_DATE ,
668 LAST_UPDATED_BY )
669 values
670 ( p_billing_cycle_id,
671 l_next_billing_date,
672 fnd_global.user_id,
673 sysdate,
674 fnd_global.user_id,
675 sysdate,
676 fnd_global.user_id);
677
678 l_next_billing_date := l_next_billing_date + l_repeat_frequency ;
679 end loop;
680
681
682
683 --------------------------------------------------------
684 --WEEKLY
685 --------------------------------------------------------
686 ELSIF l_cycle_frequency = 'WEEKLY' THEN
687
688 /* Logic for populating the Billing Date for a Weekly Billing Cycle has been modified as per the Bug-7139182 to extend the validation for 'saturday' and 'sunday' */
689
690 l_current_date := l_start_date;
691
692 select day_monday, day_tuesday, day_wednesday, day_thursday, day_friday, day_saturday, day_sunday
693 into week_tab(2), week_tab(3), week_tab(4), week_tab(5), week_tab(6),week_tab(7),week_tab(1)
694 from AR_CONS_BILL_CYCLES_B
695 where billing_cycle_id = p_billing_cycle_id;
696
697 k := to_char(l_current_date,'D'); -- This variable is used to index for the start date of the Billing Cycle
698 -- k=1 indicates 'Sunday' and k=7 indicates 'Saturday'
699 i := 1; -- This variable is used to check if seven consecutive days have been passed
700
701 loop
702
703 if week_tab(k) = 'Y' then
704 l_next_billing_date := l_current_date;
705
706 --------------------------------------------------------
707 --Exit if required years processed
708 --------------------------------------------------------
709 /****
710 Bug# 16088947 -- Not able to print Balance forward documnet, so
711 extended to 30 yrs
712 ****/
713 l_years_to_process := 30;
714 if (l_next_billing_date > (l_start_date + 365*l_years_to_process)) then
715 exit;
716 end if;
717
718 --------------------------------------------------------
719 --Insert the Bill Date in the table
720 --------------------------------------------------------
721 INSERT INTO AR_CONS_BILL_CYCLE_DATES (
722 BILLING_CYCLE_ID ,
723 BILLABLE_DATE ,
724 CREATED_BY ,
725 CREATION_DATE ,
726 LAST_UPDATE_LOGIN,
727 LAST_UPDATE_DATE ,
728 LAST_UPDATED_BY )
729 values
730 ( p_billing_cycle_id,
731 l_next_billing_date,
732 fnd_global.user_id,
733 sysdate,
734 fnd_global.user_id,
735 sysdate,
736 fnd_global.user_id);
737 end if;
738
739 k :=k+1;
740 i :=i+1;
741
742 if k = 8 then
743 k := 1;
744 end if;
745
746 ----------------------------------------------------------------------------------------------------
747 -- Moving the date forward depending upon the 'Repeat Frequency' selected for the Billing Cycle.
748 ----------------------------------------------------------------------------------------------------
749
750 if i = 8 then
751 l_current_date := l_current_date + (l_repeat_frequency-1)*7;
752 i := 1;
753 end if;
754
755 l_current_date := l_current_date + 1;
756
757 end loop;
758
759 --------------------------------------------------------
760 --MONTHLY
761 --------------------------------------------------------
762 ELSIF l_cycle_frequency = 'MONTHLY' THEN
763
764 i := 0;
765 loop
766 i := i + 1;
767 if i = 1 then
768 l_billing_month_year := to_char(l_start_date, 'MM/RRRR' );
769 l_billing_month := to_char(l_start_date, 'MM' );
770 l_billing_year := to_char(l_start_date, 'RRRR' );
771 else
772 l_char_date1 := '01/'||l_billing_month_year;
773
774 l_billing_month_year :=
775 to_char(add_months(to_date(l_char_date1,
776 'DD/MM/RRRR'),l_repeat_frequency), 'MM/RRRR');
777 l_billing_month :=
778 to_char(to_date(l_char_date1, 'DD/MM/RRRR'),'MM');
779 l_billing_year :=
780 to_char(to_date(l_char_date1,'DD/MM/RRRR'),'RRRR');
781
782 end if;
783
787 l_last_day_month_insert_flag := 'N';
784 l_month_days := to_char(LAST_DAY(to_date('01/'||l_billing_month_year,'DD/MM/RRRR')),'DD');
785 /* Bug 7476810. Initializing the variable l_last_day_month_insert_flag each
786 time to 'N' at the start of the new month */
788 --------------------------------------------------------
789 --Exit if required years processed
790 --------------------------------------------------------
791 /****
792 Bug# 16088947 -- Not able to print Balance forward documnet, so
793 extended to 30 yrs
794 ****/
795 l_years_to_process := 30;
796 if (l_start_date + 365*l_years_to_process)
797 < to_date('01/'||l_billing_month_year,'DD/MM/RRRR') then
798 exit;
799 end if;
800
801 --------------------------------------------------------
802 --Get the Billing Day
803 --------------------------------------------------------
804 select day_1, day_2, day_3, day_4, day_5,
805 day_6, day_7, day_8, day_9, day_10,
806 day_11, day_12, day_13, day_14, day_15,
807 day_16, day_17, day_18, day_19, day_20,
808 day_21, day_22, day_23, day_24, day_25,
809 day_26, day_27, day_28, day_29, day_30, day_31, last_day
810 into daytab(1), daytab(2), daytab(3), daytab(4), daytab(5),
811 daytab(6), daytab(7), daytab(8), daytab(9), daytab(10),
812 daytab(11), daytab(12), daytab(13), daytab(14), daytab(15),
813 daytab(16), daytab(17), daytab(18), daytab(19), daytab(20),
814 daytab(21), daytab(22), daytab(23), daytab(24), daytab(25),
815 daytab(26), daytab(27), daytab(28), daytab(29), daytab(30), daytab(31),
816 l_last_day
817 from ar_cons_bill_cycles_b
818 where billing_cycle_id = p_billing_cycle_id;
819
820 if l_last_day = 'Y' THEN
821 daytab(31) := 'Y';
822 end if;
823
824 for i in 1 .. 31 loop
825 /* Bug 7476810. If the last day of the month is inserted in the table as a
826 billling date, then dont insert the last date again. */
827 if daytab(i) = 'Y' and l_last_day_month_insert_flag = 'N' then
828
829 if i >= l_month_days then
830 l_last_day_month_insert_flag := 'Y';
831 l_char_date1 := l_month_days||'/'||substr(l_billing_month_year,1,7);
832 l_next_billing_date :=
833 to_date(l_char_date1,'DD/MM/RRRR');
834 else
835 l_char_date1 := i||'/'||substr(l_billing_month_year,1,7);
836
837 l_next_billing_date :=
838 to_date(l_char_date1,'DD/MM/RRRR');
839 end if;
840 /* Changes for Bug 7365237: - Start
841 Fix for the Bug 7365237 will ensure that duplicate values (combination of billing_cycle_id and billable_date)
842 would not be inserted in the table AR_CONS_BILL_CYCLE_DATES */
843 if l_day_type = 'W' and ar_day_of_week(l_next_billing_date) in (6,7) then
844 if ar_day_of_week(l_next_billing_date) = 6 then
845 -- If the billable day is 'Saturday', make the coming monday as billable day and skip Saturday.
846 daytab(i+2) := 'Y';
847 elsif ar_day_of_week(l_next_billing_date) = 7 THEN
848 -- If the billable day is 'Sunday', make the coming monday as billable day and skip Saturday.
849 daytab(i+1) := 'Y';
850 end if;
851 else -- if the day_type is not 'W' or if the day is not a Weekend (Saturday or Sunday)
852 --------------------------------------------------------
853 --Insert the Bill Date in the table
854 --------------------------------------------------------
855 INSERT INTO AR_CONS_BILL_CYCLE_DATES (
856 BILLING_CYCLE_ID ,
857 BILLABLE_DATE ,
858 CREATED_BY ,
859 CREATION_DATE ,
860 LAST_UPDATE_LOGIN,
861 LAST_UPDATE_DATE ,
862 LAST_UPDATED_BY )
863 values
864 ( p_billing_cycle_id,
865 l_next_billing_date,
866 fnd_global.user_id,
867 sysdate,
868 fnd_global.user_id,
869 sysdate,
870 fnd_global.user_id);
871 end if;
872 -- Changes for Bug 7365237: - End
873 end if;
874 end loop;
875 end loop;
876
877 END IF ; ---Monthly
878
879
880
881 END POPULATE;
882
883 end ar_bfb_utils_pvt;
[Home] [Help]