1 PACKAGE BODY PA_Billing_Cycles_Pkg AS
2 -- $Header: PAXIBCLB.pls 120.1 2005/10/10 22:34:22 appldev ship $
3 /**------
4 function Get_Billing_Date (
5 X_Project_ID IN Number,
6 X_Project_Start_Date IN Date,
7 X_Billing_Cycle_ID IN Number,
8 X_Bill_Thru_Date IN Date,
9 X_Last_Bill_Thru_Date IN Date
10 ) RETURN Date;
11 ------**/
12
13
14 /*---------------------------------------------------------------
15 Get_Next_Billing_Date ()
16
17 The Function returns the Next Bill Date.
18 If the Bill Date computed is less than the Last_Bill_Thru_Date
19 then compute the Bill Date which is greater than the Last_Bill_Thru_Date.
20 -----------------------------------------------------------------*/
21
22 function Get_Next_Billing_Date (
23 X_Project_ID IN Number,
24 X_Project_Start_Date IN Date default NULL,
25 X_Billing_Cycle_ID IN Number default NULL,
26 X_Billing_Offset_Days IN Number default NULL,
27 X_Bill_Thru_Date IN Date default NULL,
28 X_Last_Bill_Thru_Date IN Date default NULL
29 ) RETURN Date
30 IS
31
32 Proj_start_date Date := X_Project_Start_date;
33 Proj_id Number := X_Project_ID;
34 Bill_cycle_id Number := X_Billing_Cycle_ID;
35 Bill_offset Number := X_Billing_Offset_Days;
36 Last_Bill_thru_date Date := X_Last_Bill_thru_date;
37 Bill_thru_date Date := X_Bill_Thru_Date;
38 Bill_date Date;
39
40 BEGIN
41
42 /** If project start date, billing cycle id, billing offset IS NULL
43 then get the proj info **/
44
45 IF (X_Project_Start_Date IS NULL) OR (X_Billing_Cycle_ID IS NULL)
46 OR (X_Billing_offset_days IS NULL) then
47 Begin
48 Select NVL(Start_date, Creation_date), billing_cycle_id,
49 NVL(billing_offset,0)
50 INTO proj_start_date, bill_cycle_id, bill_offset
51 FROM PA_Projects_All
52 WHERE project_id = Proj_ID;
53 Exception When NO_DATA_FOUND THEN
54 NULL;
55 End;
56 END IF;
57
58 /** If Last_Bill_thru_date IS NULL then determine the Last bill thru
59 date **/
60
61 IF Last_Bill_thru_date IS NULL then
62 Last_Bill_thru_date := Get_Last_Bill_Thru_Date( Proj_id);
63 END IF;
64
65 /** If Last Bill thru date NULL means No invs found.
66 Determine the date using offset days **/
67 /* Condition modified for Bug#1347747 */
68
69 IF Last_Bill_thru_date IS NULL then
70 IF NVL(Bill_offset,0) >= 0 then
71 /** Add offset and return **/
72 RETURN ( TRUNC(Proj_start_date + NVL(bill_offset,0)) );
73 END IF;
74 END IF;
75
76 /** Get the Next billing date using bill cycle **/
77
78 Bill_date := Get_Billing_Date( Proj_id,
79 Proj_start_date,
80 Bill_cycle_id,
81 Bill_thru_date,
82 Last_Bill_thru_date);
83
84 RETURN (Bill_date);
85
86 EXCEPTION
87 When OTHERS then
88 RAISE;
89 END Get_Next_Billing_Date;
90
91 /*------------------------------------------------------------------------
92 Get_Billing_Date ()
93
94 Function to compute the Billing Cycle Date based on the
95 Last Bill thru Date.
96 The Bill Date is computed based on the month of Last Bill Thru date.
97 The Bill Date > Last Bill Thru date.
98 Calls Billing Cycle extn for User Defined Billing Cycles Types.
99 The Client Extension should ensure the Bill Date > Last Bill thru date.
100 ---------------------------------------------------------------------------*/
101 function Get_Billing_Date (
102 X_Project_ID IN Number,
103 X_Project_Start_Date IN Date,
104 X_Billing_Cycle_ID IN Number,
105 X_Bill_Thru_Date IN Date,
106 X_Last_Bill_Thru_Date IN Date
107 ) RETURN Date
108 IS
109
110 TYPE BillValueTab IS TABLE OF Number
111 INDEX BY BINARY_INTEGER;
112
113 BillValue BillValueTab;
114 i BINARY_INTEGER := 5;
115
116 j Number := 0;
117 k Number := 0;
118 tmp Number := 0;
119 Bill_date Date;
120 Bill_thru_Date Date := X_Bill_Thru_Date;
121 Last_Bill_Thru_Date Date := X_Last_Bill_Thru_Date;
122 Last_Date Date := NVL(X_Last_Bill_thru_date, X_Project_Start_Date);
123 Temp_Date Date;
124 Temp2_Date Date;
125 Bill_Month Varchar2(10);
126 Bill_Day Varchar2(100); --Changed the length for bug 4630032
127 MaxVal Number := 9999;
128 Bill_Date_15 DATE;
129 Bill_Date_Last_Day DATE;
130
131 Cursor Bill_Cur IS
132 Select Billing_Cycle_Type, Billing_Value1,
133 Billing_Value2, Billing_Value3,
134 Billing_Value4, Billing_Value5
135 From PA_Billing_Cycles
136 Where Billing_Cycle_ID = X_Billing_Cycle_ID;
137
138 Bill_rec Bill_Cur%RowType;
139
140 BEGIN
141
142 /** Get the Billing Cycle Record **/
143
144 Open Bill_Cur;
145 Fetch Bill_Cur into Bill_rec;
146 If Bill_Cur%NOTFOUND then
147 Close Bill_Cur;
148 RAISE NO_DATA_FOUND;
149 RETURN NULL ;
150 End if;
151
152 Close Bill_cur;
153
154
155 -- Added for Patchset L Enhancement Bug 1584948
156 IF Bill_Rec.Billing_Cycle_Type = '15TH AND MONTH END'
157 THEN
158 Bill_Month := to_char( Last_Date, 'mm/yyyy' );
159 Bill_Date_15 := to_date('15/'|| Bill_Month,'dd/mm/yyyy' );
160 Bill_Date_Last_Day := last_day(to_date('01/'|| Bill_Month,'dd/mm/yyyy'));
161
162 -- Following if clause is modified for bug fix 3011314
163 -- If the last Bill thru date is before 15th of its month,
164 -- then return its 15th date of the month
165 IF Last_Date < Bill_Date_15 THEN
166 RETURN Bill_Date_15;
167 END IF;
168
169 -- If the last Bill trhu date is on before last day of the month
170 -- then return the last day of the month
171 -- Else return 15th of the next month.
172 IF Last_Date >= Bill_Date_15 AND Last_Date <> Bill_Date_Last_Day
173 THEN
174 RETURN Bill_Date_Last_Day ;
175 ELSE
176 Temp_Date := to_date( '15/'|| Bill_Month,'dd/mm/yyyy');
177 Temp_Date := add_months( Temp_Date, 1 );
178 Return Temp_Date;
179 END IF;
180
181 END IF;
182 -- Added for Patchset L END
183
184
185 /** User defined code called **/
186
187 IF Bill_Rec.Billing_Cycle_Type = 'USER DEFINED' Then
188 Return PA_Client_Extn_Bill_Cycle.Get_Next_Billing_Date(
189 X_Project_id,
190 X_Project_Start_Date,
191 X_Billing_Cycle_ID,
192 X_Bill_Thru_Date,
193 X_Last_Bill_Thru_Date);
194 End if;
195
196 /** Calculate the Bill Date for Billing Cycle Days **/
197
198 If Bill_Rec.Billing_Cycle_Type = 'BILLING CYCLE DAYS' Then
199 Bill_Date := Last_Date + (NVL(to_number(Bill_Rec.Billing_Value1),0));
200 Return Bill_Date;
201 End if;
202
203 Bill_Month := to_char( Last_Date, 'mm/yyyy' );
204
205 /** Set the billValue table with not null values **/
206
207 FOR i in 1..5 LOOP
208 BillValue(i) := NULL;
209 END LOOP;
210
211 BillValue(1) := nvl(to_number(Bill_Rec.Billing_Value1),MaxVal);
212 BillValue(2) := nvl(to_number(Bill_Rec.Billing_Value2),MaxVal);
213 BillValue(3) := nvl(to_number(Bill_Rec.Billing_Value3),MaxVal);
214 BillValue(4) := nvl(to_number(Bill_Rec.Billing_Value4),MaxVal);
215 BillValue(5) := nvl(to_number(Bill_Rec.Billing_Value5),MaxVal);
216
217 /** Sort the Values with null values to the end as 9999 **/
218
219 i := 1;
220 LOOP
221 i := i+1;
222 tmp := BillValue(i);
223 FOR j in 1..(i-1) LOOP
224 IF tmp < BillValue(j) then
225 k := i;
226 LOOP
227 BillValue(k) := BillValue(k-1);
228 k := k-1;
229 IF k = j then
230 BillValue(j) := tmp;
231 EXIT; -- exit to outer loop
232 END IF;
233 END LOOP;
234 EXIT; -- exit to main loop
235 END IF;
236 END LOOP;
237 IF i = 5 then
238 EXIT; -- exit out
239 END IF;
240 END LOOP;
241
242 /** Derive the date **/
243
244 LOOP
245
246 IF Bill_Rec.Billing_Cycle_Type = 'DATE OF MONTH' Then
247 /** Date of Month may have ore than one values **/
248 i := 0;
249 LOOP
250 i := i+1;
251 IF BillValue(i) = MaxVal then
252 EXIT; -- out of this loop
253 END IF;
254 BEGIN
255 Bill_Date := to_date( lpad(to_char(BillValue(i)),2,'0')
256 || '/' || Bill_Month,'dd/mm/yyyy' );
257 EXCEPTION
258 When OTHERS then
259 Bill_Date := last_day( to_date('01/'|| Bill_Month,'dd/mm/yyyy' ));
260 END;
261 IF Bill_Date > Last_date then
262 EXIT; -- out of this loop
263 END IF;
264 IF i = 5 then
265 EXIT; -- out of this loop
266 END IF;
267 END LOOP;
268
269 ELSIF Bill_Rec.Billing_Cycle_Type = 'LAST DAY OF MONTH' Then
270 Bill_Date := last_day( to_date('01/'|| Bill_Month,'dd/mm/yyyy' ));
271
272 ELSIF Bill_Rec.Billing_Cycle_Type = 'FIRST DAY OF MONTH' Then
273 Bill_Date := to_date( '01/'|| Bill_Month,'dd/mm/yyyy' );
274
275 ELSIF Bill_Rec.Billing_Cycle_Type = 'FIRST WEEKDAY OF MONTH' Then
276 Temp_Date := to_date( '01/'|| Bill_Month,'dd/mm/yyyy' ); -- First of curr mth
277 Temp2_Date := last_day( Temp_Date ); -- Last of curr mth
278 Temp_Date := add_months( Temp2_date, -1 ); -- Last of prev mth
279
280 -- decode number 1-7 into weekdays
281
282 if BillValue(1)=1 then
283 BillValue(1):=8;
284 end if;
285 select to_char(to_date('01-01-1950','DD-MM-YYYY') + BillValue(1)-1,'Day')
286 Into Bill_Day
287 From Dual;
288
289 -- Select meaning
290 -- Into Bill_Day
291 -- From PA_Lookups
292 -- Where Lookup_Type = 'EXPENDITURE CYCLE START DAY'
293 -- AND Lookup_Code = to_char(BillValue(1));
294
295 Bill_Date := next_day( Temp_Date, Bill_Day ); -- date for first wkday
296
297 ELSIF Bill_Rec.Billing_Cycle_Type = 'LAST WEEKDAY OF MONTH' Then
298 Temp_Date := to_date( '01/'|| Bill_Month,'dd/mm/yyyy' ); -- First of curr mth
299 Temp2_Date := last_day( Temp_Date ); -- Last of curr mth
300
301 -- decode number 1-7 into weekdays
302
303 if BillValue(1)=1 then
304 BillValue(1):=8;
305 end if;
306
307 select to_char(to_date('01-01-1950','DD-MM-YYYY') +BillValue(1)-1,'Day')
308 Into Bill_Day
309 From Dual;
310
311 -- Select meaning
312 -- Into Bill_Day
313 -- From PA_Lookups
314 -- Where Lookup_Type = 'EXPENDITURE CYCLE START DAY'
315 -- AND Lookup_Code = to_char(BillValue(1));
316
317 Temp_Date := next_day( Temp2_Date, Bill_Day ); -- date for first wkday
318 -- of next mth
319 Bill_Date := Temp_Date - 7; -- Last wkday of curr mth
320
321 ELSIF Bill_Rec.Billing_Cycle_Type = 'PROJECT COMPLETION' then
322 /** Get the completion date for the project **/
323 Select Completion_date
324 Into Bill_Date
325 From PA_Projects_All
326 Where Project_ID = X_Project_ID;
327
328 IF (Bill_date IS NULL) AND (X_Bill_Thru_Date IS NOT NULL) then
329 /** Project not yet complete.
330 If inv gen process ( Bill thru date not null)
331 then return bill thru date+1, so as not to consider the project.
332 else, return NULL **/
333 Bill_date := X_Bill_Thru_Date+1;
334 END IF;
335
336 RETURN Bill_Date;
337
338 ELSIF Bill_Rec.Billing_Cycle_Type = 'WEEKDAY EACH WEEK' then
339 -- decode number 1-7 into weekdays
340
341 if BillValue(1)=1 then
342 BillValue(1):=8;
343 end if;
344
345 select to_char(to_date('01-01-1950','DD-MM-YYYY') +BillValue(1)-1,'Day')
346 Into Bill_Day
347 From Dual;
348
349 -- Select meaning
350 -- Into Bill_Day
351 -- From PA_Lookups
352 -- Where Lookup_Type = 'EXPENDITURE CYCLE START DAY'
353 -- AND Lookup_Code = to_char(BillValue(1));
354
355 Bill_Date := next_day( Last_Date, Bill_Day ); -- date for next wkday
356
357 RETURN Bill_Date;
358
359 END IF;
360
361 If Bill_Date <= Last_Date then
362 Temp_Date := to_date( '01/'|| Bill_Month,'dd/mm/yyyy');
363 Temp_Date := add_months( Temp_Date, 1 );
364 Bill_Month := to_char( Temp_Date, 'mm/yyyy');
365 Else
366 Return Bill_Date;
367 End if;
368
369 END LOOP;
370
371 EXCEPTION
372 WHEN OTHERS then
373 RAISE;
374
375 END Get_Billing_Date;
376
377 /*-------------------------------------------------------------------
378 Get_Last_Bill_Thru_Date ()
379 Function to get the last bill thru date for a project.
380 If no invoices found, then null returned.
381 ---------------------------------------------------------------------*/
382
383 function Get_Last_Bill_Thru_Date (
384 X_Project_ID IN Number
385 ) RETURN Date
386 IS
387
388 Last_Bill_Thru_Date Date;
389
390 BEGIN
391
392 /** get the last bill thru date **/
393 /* Added retention_invoice_flag condition for Bug2385594 */
394
395 SELECT MAX(NVL(Bill_through_date, Creation_date))
396 INTO Last_Bill_thru_date
397 FROM PA_Draft_Invoices_All
398 WHERE Project_id = X_project_id
399 AND nvl(retention_invoice_flag, 'N') = 'N'
400 AND Draft_Invoice_Num_Credited IS NULL
401 AND Released_By_Person_ID IS NOT NULL
402 AND NVL(Canceled_Flag,'N') = 'N';
403
404 RETURN Last_Bill_Thru_Date;
405
406 EXCEPTION
407
408 WHEN OTHERS then
409 Raise;
410
411 END Get_Last_Bill_Thru_Date;
412
413
414 /*--------------------------------------------------------------------
415 Get_Last_Released_Invoice_Num ()
416
417 Function to determine the last invoice number for a project.
418 Returns the uncrediting, released draft invoice, if found,
419 else NULL.
420 ----------------------------------------------------------------------*/
421
422 function Get_Last_Released_Invoice_Num (
423 X_Project_ID IN Number
424 ) RETURN Number
425 IS
426
427 Invoice_Num Number;
428
429 BEGIN
430
431 /** Get invoice num **/
432
433 SELECT MAX(DI.Draft_Invoice_Num)
434 INTO Invoice_Num
435 FROM PA_Draft_Invoices DI
436 WHERE DI.Project_ID = X_Project_id
437 AND DI.Draft_Invoice_Num_Credited IS NULL
438 AND DI.Released_By_Person_ID IS NOT NULL
439 AND NVL(DI.Canceled_Flag,'N') = 'N'
440 AND NVL(DI.Bill_through_date, DI.Creation_date) = (
441 SELECT MAX(NVL(PDI.Bill_through_date, PDI.Creation_date))
442 FROM PA_Draft_Invoices PDI
443 WHERE PDI.Project_ID = DI.Project_ID
444 AND PDI.Draft_Invoice_Num_Credited IS NULL
445 AND PDI.Released_By_Person_ID IS NOT NULL
446 AND NVL(PDI.Canceled_Flag,'N') = 'N'
447 );
448
449 RETURN Invoice_Num;
450
451 EXCEPTION
452 WHEN OTHERS then
453 RAISE;
454
455 END Get_Last_Released_Invoice_Num;
456
457 ------------------------------------------------------------------------
458 END PA_Billing_Cycles_Pkg;
459
[Home] [Help]