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