[Home] [Help]
PACKAGE BODY: APPS.CS_CONTRACT_BILLING
Source
1 PACKAGE BODY CS_CONTRACT_BILLING as
2 /* $Header: csctbilb.pls 115.8 99/07/16 08:48:52 porting ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_CONTRACT_BILLING';
6
7 -- Global var holding the Current Error code for the error encountered
8 Current_Error_Code Varchar2(20) := NULL;
9
10 -- Global var holding the User Id
11 user_id NUMBER;
12
13 -- Global var to hold the ERROR value.
14 ERROR NUMBER := 1;
15
16 -- Global var to hold the SUCCESS value.
17 SUCCESS NUMBER := 0;
18
19 -- Global var to hold the commit size.
20 COMMIT_SIZE NUMBER := 10;
21
22 -- Global var to hold the month unit of measure.
23 month_unit VARCHAR2(15);
24
25 -- Global var to hold the day unit of measure.
26 day_unit VARCHAR2(15);
27
28 -- Global var to hold the Concurrent Process return value
29 conc_ret_code NUMBER := SUCCESS;
30
31 PROCEDURE Generate_Billing_Lines
32 (
33 ERRBUF OUT VARCHAR2,
34 RETCODE OUT NUMBER,
35 P_DEFAULT_DATE IN DATE,
36 P_WINDOW IN NUMBER,
37 P_CONTRACT_NUMBER IN NUMBER
38
39 )IS
40
41 CONC_STATUS BOOLEAN;
42 v_retcode NUMBER := SUCCESS;
43
44 input_date_range DATE;
45 BEGIN
46
47
48 -- Only for testing purpose --
49
50 --FND_FILE.PUT_NAMES('bill.log','bill.out','/sqlcom/log');
51 --FND_GLOBAL.APPS_INITIALIZE (
52 -- 1001,170,20638);
53
54 -- Only for testing purpose --
55
56
57 user_id := FND_GLOBAL.USER_ID;
58 FND_FILE.PUT_LINE(FND_FILE.LOG, 'User_Id ='||
59 to_char(user_id));
60
61 input_date_range := p_default_date + p_window;
62 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Default Date ='||
63 to_char(input_date_range));
64
65
66 month_unit :=
67 FND_PROFILE.VALUE('MONTH_UNIT_OF_MEASURE');
68 FND_FILE.PUT_LINE (FND_FILE.LOG,'MONTH UNIT = ' || month_unit);
69
70 day_unit :=
71 FND_PROFILE.VALUE('DAY_UNIT_OF_MEASURE');
72 FND_FILE.PUT_LINE (FND_FILE.LOG,'DAY UNIT = ' || day_unit);
73
74 v_retcode := get_billing_Lines( input_date_range,
75 p_contract_number);
76
77 IF v_retcode = SUCCESS THEN
78
79 FND_FILE.PUT_LINE( FND_FILE.LOG,
80 'get Billing lines successfully completed' );
81
82 v_retcode := CS_AR_FEEDER_PROGRAM.Main_Procedure;
83 END IF;
84
85
86
87 FND_FILE.PUT_LINE (FND_FILE.LOG,'RETCODE = ' || to_char(v_retcode));
88
89 COMMIT;
90
91 IF conc_ret_code = SUCCESS THEN
92 CONC_STATUS :=
93 FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
94 Current_Error_Code);
95 ELSE
96 CONC_STATUS :=
97 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
98 END IF;
99
100 --FND_FILE.CLOSE;
101
102 /*********************
103 EXCEPTION
104 WHEN UTL_FILE.INVALID_PATH THEN
105 --DBMS_OUTPUT.PUT_LINE ('FILE LOCATION OR NAME WAS INVALID');
106
107 WHEN UTL_FILE.INVALID_MODE THEN
108 --DBMS_OUTPUT.PUT_LINE ('FILE OPEN MODE STRING WAS INVALID');
109
110 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
111 --DBMS_OUTPUT.PUT_LINE ('FILE HANDLE WAS INVALID');
112
113 WHEN UTL_FILE.INVALID_OPERATION THEN
114 --DBMS_OUTPUT.PUT_LINE ('FILE IS NOT OPEN FOR WRITTING');
115
116 WHEN UTL_FILE.WRITE_ERROR THEN
117 --DBMS_OUTPUT.PUT_LINE ('OS ERROR OCCURRED DURING WRITE OPERATION');
118 NULL;
119
120 *****************/
121 END Generate_Billing_Lines;
122
123
124
125
126
127 FUNCTION Get_Billing_Lines
128 (
129 P_DATE_RANGE IN DATE,
130 P_CONTRACT_NUMBER IN NUMBER
131 )
132 RETURN NUMBER IS
133
134 CURSOR lines IS
135 SELECT
136 CON.CONTRACT_ID CONTRACT_ID,
137 CON.CONTRACT_NUMBER CONTRACT_NUMBER,
138 SRV.CP_SERVICE_ID CP_SERVICE_ID,
139 SRV.EXTENDED_PRICE EXTENDED_PRICE,
140 SRV.DURATION_QUANTITY DURATION,
141 SRV.UNIT_OF_MEASURE_CODE PERIOD,
142 SRV.BILLING_FREQUENCY_PERIOD BILLING_PERIOD,
143 SRV.NEXT_BILL_DATE NEXT_BILL_DATE,
144 SRV.FIRST_BILL_DATE FIRST_BILL_DATE,
145 SRV.START_DATE_ACTIVE START_DATE_ACTIVE,
146 SRV.END_DATE_ACTIVE END_DATE_ACTIVE,
147 SRV.BILL_ON BILL_ON,
148 SRV.SERVICE_INVENTORY_ITEM_ID INVENTORY_ITEM_ID
149 FROM
150 CS_CONTRACTS CON,
151 CS_CP_SERVICES SRV,
152 CS_CONTRACT_STATUSES STS
153 WHERE
154 CON.CONTRACT_ID = SRV.CONTRACT_ID AND
155 CON.CONTRACT_NUMBER =
156 NVL(p_contract_number,CON.CONTRACT_NUMBER) AND
157 STS.ELIGIBLE_FOR_INVOICING = 'Y' AND
158 (
159 ( SRV.FIRST_BILL_DATE <= p_date_range AND
160 SRV.NEXT_BILL_DATE IS NULL
161 ) OR
162
163 ( SRV.NEXT_BILL_DATE <= p_date_range AND
164 SRV.NEXT_BILL_DATE <= SRV.END_DATE_ACTIVE
165 )
166 ) AND
167 SRV.CONTRACT_LINE_STATUS_ID = STS.CONTRACT_STATUS_ID ;
168 --FOR UPDATE OF CON.CONTRACT_ID NOWAIT;
169 /*
170 CURSOR lines IS
171 SELECT
172 CON.CONTRACT_ID CONTRACT_ID,
173 CON.CONTRACT_NUMBER CONTRACT_NUMBER,
174 SRV.CP_SERVICE_ID CP_SERVICE_ID,
175 SRV.EXTENDED_PRICE EXTENDED_PRICE,
176 SRV.DURATION_QUANTITY DURATION,
177 SRV.UNIT_OF_MEASURE_CODE PERIOD,
178 SRV.BILLING_FREQUENCY_PERIOD BILLING_PERIOD,
179 SRV.NEXT_BILL_DATE NEXT_BILL_DATE,
180 SRV.FIRST_BILL_DATE FIRST_BILL_DATE,
181 SRV.START_DATE_ACTIVE START_DATE_ACTIVE,
182 SRV.END_DATE_ACTIVE END_DATE_ACTIVE,
183 SRV.BILL_ON BILL_ON,
184 SRV.SERVICE_INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
185 PROD.QUANTITY QUANTITY
186 FROM
187 CS_CONTRACTS CON,
188 CS_CP_SERVICES SRV,
189 CS_CUSTOMER_PRODUCTS PROD,
190 CS_CONTRACT_COVERAGE_LEVELS COVL,
191 CS_COVERED_PRODUCTS COVP,
192 CS_CONTRACT_STATUSES STS
193 WHERE
194 CON.CONTRACT_ID = SRV.CONTRACT_ID AND
195 STS.ELIGIBLE_FOR_INVOICING = 'Y' AND
196 (
197 ( SRV.FIRST_BILL_DATE <= p_date_range AND
198 SRV.NEXT_BILL_DATE IS NULL
199 ) OR
200
201 ( SRV.NEXT_BILL_DATE <= p_date_range AND
202 SRV.NEXT_BILL_DATE <= SRV.END_DATE_ACTIVE
203 )
204 ) AND
205 SRV.CONTRACT_LINE_STATUS_ID = STS.CONTRACT_STATUS_ID AND
206 SRV.CP_SERVICE_ID = COVL.CP_SERVICE_ID AND
207 COVL.COVERAGE_LEVEL_ID = COVP.COVERAGE_LEVEL_ID AND
208 PROD.CUSTOMER_PRODUCT_ID = COVP.CUSTOMER_PRODUCT_ID
209 FOR UPDATE OF CON.CONTRACT_ID;
210 */
211 eligible_line lines%ROWTYPE;
212
213
214 invoiced_service_amount NUMBER;
215 invoice_amount NUMBER;
216 v_retcode NUMBER := SUCCESS;
217 v_success NUMBER := SUCCESS;
218 billed_until_date DATE;
219 next_bill_date DATE;
220
221
222 BEGIN
223 /* Process all fetched lines where the service amount is greater than */
224 /* the total invoiced amount for the service. */
225
226 FOR eligible_line IN lines
227 LOOP
228 BEGIN
229 /*
230 get the total invoiced amount and the maximun
231 billed_until_date for a specific sevice line
232 in a contract.If the Service line is billed for
233 the first time then the invoiced_service_amount
234 will be 0 and billed_until_date = NULL.
235
236 */
237
238 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Contract line : ');
239 FND_FILE.PUT_LINE(FND_FILE.LOG,'CONTRACT NUMBER : '||
240 to_char(eligible_line.contract_number) );
241 FND_FILE.PUT_LINE(FND_FILE.LOG,'SERVICE ID : '||
242 to_char(eligible_line.CP_service_id) );
243
244 v_retcode := Get_Invoiced_Amount(
245 invoiced_service_amount,
246 billed_until_date,
247 eligible_line.contract_id,
248 eligible_line.cp_service_id);
249
250
251
252
253 IF (billed_until_date IS NULL ) THEN
254 NULL;
255 ELSE
256 billed_until_date := billed_until_date + 1;
257 END IF;
258
259 IF v_retcode = SUCCESS THEN
260
261 FND_FILE.PUT_LINE( FND_FILE.LOG,
262 'Invvoiced amount =' || to_char(invoiced_service_amount));
263 FND_FILE.PUT_LINE( FND_FILE.LOG,
264 'Billed Until Date =' || to_char(billed_until_date));
265
266
267
268 /* Process the service line only if the service amount
269 is greater than the total invoiced amount for the service.
270 This implies that the service line has to be further
271 invoiced
272 */
273 --IF (eligible_line.extended_price>invoiced_service_amount) THEN
274
275
276 /* Process all lines which have :
277 1. Value in the field First Bill Date
278 2. Null value in the field Next Bill Date and
279 3. Null value in Billed Until Date (which means the
280 contract line is billed for the first time ) or
281 4. all lines with a Not Null Next Bill Date and First_Bill_Date.
282 i.e.All eligible contract line which are not fully invoiced. */
283
284 IF (
285 (
286 (
287 eligible_line.next_bill_date IS NULL AND
288 eligible_line.first_bill_date IS NOT NULL AND
289 billed_until_date IS NULL
290 ) OR
291 (
292 eligible_line.next_bill_date IS NOT NULL AND
293 eligible_line.first_bill_date IS NOT NULL
294 )
295 ) AND
296 eligible_line.extended_price > 0
297 ) THEN
298
299 /* Calculate the remaining amount to be invoiced */
300
301 invoice_amount := eligible_line.extended_price
302 - invoiced_service_amount;
303
304 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invoice_Amount='||
305 to_char(Invoice_Amount));
306
307 Process_And_Insert_Records
308 (
309 Billed_Until_Date ,
310 eligible_line.Next_Bill_date ,
311 eligible_line.bill_on,
312 eligible_line.First_Bill_date ,
313 eligible_line.Start_Date_active ,
314 eligible_line.End_Date_active ,
315 eligible_line.Contract_Id ,
316 eligible_line.CP_Service_Id ,
317 invoice_amount,
318 eligible_line.Extended_Price ,
319 eligible_line.duration ,
320 eligible_line.inventory_Item_Id ,
321 eligible_line.period ,
322 eligible_line.Billing_Period
323 );
324 ELSE
325 FND_FILE.PUT_LINE(FND_FILE.LOG,
326 'The contract line has been fully invoiced Or
327 the Contract line has bad data.');
328
329 FND_FILE.PUT_LINE(FND_FILE.LOG,'CONTRACT NUMBER : '||
330 to_char(eligible_line.contract_number) ||
331 ' SERVICE ID : ' || to_char(eligible_line.CP_Service_Id));
332
333 END IF; /* After Total invoice check */
334 END IF ; /* Fetch Invoice Amount */
335
336 EXCEPTION
337 WHEN OTHERS THEN
338 v_retcode := ERROR;
339 Current_error_Code := to_Char(SQLCODE);
340 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Contract Line not Processed' );
341 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
342 return (v_retcode);
343 END;
344 END LOOP;
345
346 COMMIT;
347 --CLOSE lines;
348
349 Current_error_Code := to_Char(SQLCODE);
350 return (v_success);
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 v_success := ERROR;
355 Current_error_Code := to_Char(SQLCODE);
356 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Cursor Processing' );
357 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
358 return (v_retcode);
359
360 END Get_Billing_Lines;
361
362
363
364
365 FUNCTION Get_Invoiced_Amount
366 (
367 P_INVOICED_SERVICE_AMOUNT OUT NUMBER,
368 P_BILLED_UNTIL_DATE OUT DATE,
369 P_CONTRACT_ID IN NUMBER,
370 P_CP_SERVICE_ID IN NUMBER
371 )
372 RETURN NUMBER IS
373 v_retcode NUMBER := SUCCESS;
374 BEGIN
375
376 /* Select the sum of the invoice ammount for the service line and the
377 maximum billed_until_date from CS_CONTRACTS_BILLING */
378
379 --FND_FILE.PUT_LINE(FND_FILE.LOG,'CONTRACT_ID ='||
380 -- to_char(p_contract_id));
381 --FND_FILE.PUT_LINE(FND_FILE.LOG,'CP_SERVICE_ID ='||
382 -- to_char(p_cp_service_id));
383
384 SELECT SUM(TRX_PRE_TAX_AMOUNT), MAX(BILLED_UNTIL_DATE)
385 INTO p_invoiced_service_amount, p_billed_until_date
386 FROM CS_CONTRACTS_BILLING
387 WHERE CONTRACT_ID = p_contract_Id
388 AND CP_SERVICE_ID = p_cp_service_id;
389
390 /* When no record exist in CS_CONTRACTS_BILLING for the specific
391 Contract_Id and cs_cp_service_id , the total invoiced amount is ZERO
392 and the Billed_Until_Date is NULL */
393
394 IF (p_invoiced_service_amount IS NULL ) THEN
395 p_invoiced_service_amount := 0;
396 END IF;
397
398 Current_error_Code := to_Char(SQLCODE);
399 v_retcode := SUCCESS;
400 return (v_retcode);
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 v_retcode := ERROR;
405 Current_error_Code := to_Char(SQLCODE);
406 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error getting Invoice Amount ' );
407 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLCODE );
408 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
409 return (v_retcode);
410
411 END Get_Invoiced_Amount;
412
413 PROCEDURE Process_And_Insert_Records
414 (
415 P_BILLED_UNTIL_DATE IN OUT DATE,
416 P_NEXT_BILL_DATE IN OUT DATE,
417 P_BILL_ON IN OUT NUMBER,
418 P_FIRST_BILL_DATE IN DATE,
419 P_START_DATE_ACTIVE IN DATE,
420 P_END_DATE_ACTIVE IN DATE,
421 P_CONTRACT_ID IN NUMBER,
422 P_CP_SERVICE_ID IN NUMBER,
423 P_INVOICE_AMOUNT IN NUMBER,
424 P_EXTENDED_PRICE IN NUMBER,
425 P_DURATION_QUANTITY IN NUMBER,
426 P_SERVICE_INVENTORY_ITEM_ID IN NUMBER,
427 P_UNIT_OF_MEASURE_CODE IN VARCHAR2,
428 P_BILLING_FREQUENCY_PERIOD IN VARCHAR2
429 ) IS
430 v_retcode NUMBER ;
431 billed_from_date DATE;
432 transaction_date DATE;
433 billing_amount NUMBER;
434 commit_count NUMBER := 1;
435
436 BEGIN
437
438 /* set the transaction date which should be passed to the
439 interface table CS_CONT_BILL_IFACE */
440
441 IF (p_next_bill_date is NULL ) then
442 transaction_date := p_first_bill_date;
443 ELSE
444 transaction_date := p_Next_bill_date;
445 END IF;
446
447 FND_FILE.PUT_lINE(FND_FILE.LOG,'Transaction Date ='||
448 to_char(Transaction_Date));
449
450 v_retcode := Process_Billing_records
451 (
452 billing_Amount ,
453 Billed_From_Date ,
454 p_Billed_Until_Date ,
455 p_Next_Bill_date ,
456 p_bill_on,
457 p_First_Bill_date ,
458 p_Start_Date_active ,
459 p_End_Date_active ,
460 p_invoice_amount,
461 p_Extended_Price ,
462 p_duration_quantity ,
463 p_Service_inventory_Item_Id ,
464 p_unit_of_measure_code ,
465 p_Billing_Frequency_Period
466 );
467
468
469 IF v_retcode = SUCCESS THEN
470
471 /* Insert a record in the interface table */
472 /*
473 v_retcode := INSERT_CS_CONT_BILL_IFACE
474 (
475 billing_Amount ,
476 Billed_From_Date ,
477 p_Billed_Until_Date ,
478 Transaction_Date,
479 eligible_line.contract_id,
480 eligible_line.cp_service_id,
481 eligible_line.quantity);
482 */
483
484 v_retcode := INSERT_CS_CONT_BILL_IFACE
485 (
486 billing_Amount ,
487 Billed_From_Date ,
488 p_Billed_Until_Date ,
489 Transaction_Date,
490 p_contract_id,
491 p_cp_service_id,
492 NULL);
493
494 IF v_retcode = SUCCESS THEN
495
496 /* update CS_CP_SERVICES set the transaction_availability_code*/
497 /* To 'RESERVED' and update the column Next_bill_date with the*/
498 /* new Next_Bill_date */
499
500
501 v_retcode := UPDATE_CS_CP_SERVICES
502 (
503 p_contract_id,
504 p_cp_service_id,
505 p_Next_Bill_date
506 );
507
508
509 IF v_retcode = SUCCESS THEN
510 IF (commit_Count = COMMIT_SIZE) THEN
511 COMMIT;
512 commit_count := 1;
513 ELSE
514 commit_count := commit_count + 1;
515 END IF;
516 END IF; /* After Commit */
517 END IF; /* After Update */
518 END IF; /* After Insert */
519
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 Current_error_Code := to_Char(SQLCODE);
524 FND_FILE.PUT_LINE( FND_FILE.LOG,
525 'Error in Process and Insert record' );
526 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
527
528 END Process_And_Insert_Records ;
529
530
531 FUNCTION Process_Billing_Records
532 (
533 p_BILLING_AMOUNT OUT NUMBER,
534 P_BILLED_FROM_DATE OUT DATE,
535 P_BILLED_UNTIL_DATE IN OUT DATE,
536 P_NEXT_BILL_DATE IN OUT DATE,
537 P_BILL_ON IN OUT NUMBER,
538 P_FIRST_BILL_DATE IN DATE,
539 P_START_DATE_ACTIVE IN DATE,
540 P_END_DATE_ACTIVE IN DATE,
541 P_INVOICE_AMOUNT IN NUMBER,
542 P_EXTENDED_PRICE IN NUMBER,
543 P_DURATION_QUANTITY IN NUMBER,
544 P_SERVICE_INVENTORY_ITEM_ID IN NUMBER,
545 P_UNIT_OF_MEASURE_CODE IN VARCHAR2,
546 P_BILLING_FREQUENCY_PERIOD IN VARCHAR2
547 )
548 RETURN NUMBER IS
549 v_retcode NUMBER ;
550 BEGIN
551 IF ( P_Next_Bill_Date IS NULL ) THEN
552 /* Record has been picked up by the billing program */
553 /* for the first time. */
554
555 v_retcode := Process_First_Bill_date
556 (
557 P_BILLING_AMOUNT ,
558 P_BILLED_FROM_DATE ,
559 P_BILLED_UNTIL_DATE ,
560 P_NEXT_BILL_DATE ,
561 P_BILL_ON,
562 P_FIRST_BILL_DATE ,
563 P_START_DATE_ACTIVE ,
564 P_END_DATE_ACTIVE ,
565 P_EXTENDED_PRICE ,
566 P_DURATION_QUANTITY ,
567 P_SERVICE_INVENTORY_ITEM_ID ,
568 P_UNIT_OF_MEASURE_CODE ,
569 P_BILLING_FREQUENCY_PERIOD
570 );
571
572 FND_FILE.PUT_LINE(FND_FILE.LOG,'AFTER PROCESS FIRST BILL DATE ');
573 ELSE
574 v_retcode := Process_Next_Bill_date
575 (
576 P_BILLING_AMOUNT ,
577 P_BILLED_FROM_DATE ,
578 P_BILLED_UNTIL_DATE ,
579 P_NEXT_BILL_DATE ,
580 P_BILL_ON ,
581 P_FIRST_BILL_DATE ,
582 P_START_DATE_ACTIVE ,
583 P_END_DATE_ACTIVE ,
584 P_INVOICE_AMOUNT ,
585 P_EXTENDED_PRICE ,
586 P_DURATION_QUANTITY ,
587 P_SERVICE_INVENTORY_ITEM_ID ,
588 P_UNIT_OF_MEASURE_CODE ,
589 P_BILLING_FREQUENCY_PERIOD
590 );
591 FND_FILE.PUT_LINE(FND_FILE.LOG,'AFTER PROCESS NEXT BILL DATE');
592 END IF;
593
594 return(v_retcode);
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 v_retcode := ERROR;
599 Current_error_Code := to_Char(SQLCODE);
600 FND_FILE.PUT_LINE( FND_FILE.LOG,
601 'Error in Process Billing record' );
602 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
603 return (v_retcode);
604
605 END Process_Billing_Records;
606
607 FUNCTION Process_First_Bill_Date
608 (
609 P_BILLING_AMOUNT OUT NUMBER,
610 P_BILLED_FROM_DATE OUT DATE,
611 P_BILLED_UNTIL_DATE IN OUT DATE,
612 P_NEXT_BILL_DATE IN OUT DATE,
613 P_BILL_ON IN OUT NUMBER,
614 P_FIRST_BILL_DATE IN DATE,
615 P_START_DATE_ACTIVE IN DATE,
616 P_END_DATE_ACTIVE IN DATE,
617 P_EXTENDED_PRICE IN NUMBER,
618 P_DURATION_QUANTITY IN NUMBER,
619 P_SERVICE_INVENTORY_ITEM_ID IN NUMBER,
620 P_UNIT_OF_MEASURE_CODE IN VARCHAR2,
621 P_BILLING_FREQUENCY_PERIOD IN VARCHAR2
622 )
623 RETURN NUMBER IS
624
625 v_retcode NUMBER := SUCCESS;
626
627 BEGIN
628
629 FND_FILE.PUT_LINE(FND_FILE.LOG,'FBD =' || to_char(p_first_bill_date));
630 FND_FILE.PUT_LINE(FND_FILE.LOG,'SDA =' || to_char(p_start_date_active));
631 IF (P_First_Bill_Date >= P_End_Date_Active ) THEN
632
633 /* Bill for the entire amount */
634
635 P_Billing_Amount := P_extended_price;
636 --P_Next_Bill_date := P_end_date_active;
637 P_Next_Bill_date := Null;
638 P_Billed_From_Date := P_Start_date_active;
639 P_Billed_until_Date := P_end_date_active;
640
641 ELSIF (P_First_Bill_Date = p_Start_Date_Active) THEN
642
643
644 /* Bill in advance for the billing period between */
645 /* Service start Date and Next Bill Date. */
646 /* Next Bill Date is calculated based on the billing */
647 /* frequency and bill on */
648
649 --FND_FILE.Put_Line(FND_FILE.LOG,'PROCESS FIRST BILL IN ADVANCE');
650 p_next_bill_date :=CS_CONTRACT_BILLING.Calculate_next_bill_date(
651 p_start_Date_active,
652 p_End_Date_active,
653 p_bill_on,
654 p_Service_inventory_item_id,
655 p_billing_frequency_period );
656
657 FND_FILE.PUT_LINE(FND_FILE.LOG,
658 ' Next Bill Date =' || to_char(p_next_bill_date));
659
660 IF (p_next_bill_date IS NULL ) THEN
661 v_retcode := ERROR;
662 ELSE
663 /*
664 FND_FILE.PUT_LINE(FND_FILE.LOG,'STDA =' || to_char(p_start_date_active));
665 FND_FILE.PUT_LINE(FND_FILE.LOG,'NBD =' || to_char(p_next_bill_date));
666 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMNT =' || to_char(p_extended_price));
667 FND_FILE.PUT_LINE(FND_FILE.LOG,'DURN =' || to_char(p_duration_quantity));
668 FND_FILE.PUT_LINE(FND_FILE.LOG,'ITEM =' ||
669 to_char(p_service_inventory_item_id));
670 FND_FILE.PUT_LINE(FND_FILE.LOG,'PERD =' || (p_unit_of_measure_code));
671 FND_FILE.PUT_LINE(FND_FILE.LOG,'BILL =' || (p_billing_frequency_period));
672 */
673 P_Billing_amount := Calculate_Txn_Amount (
674 'Y',
675 p_Start_Date_Active,
676 p_next_Bill_Date,
677 p_extended_price,
678 p_duration_quantity,
679 p_service_inventory_item_id,
680 p_Unit_of_Measure_code,
681 p_billing_frequency_period);
682
683 IF (P_Billing_Amount > 0) THEN
684 p_billed_From_date := P_Start_Date_Active;
685 p_billed_until_date := P_Next_Bill_Date -1;
686 ELSE
687 v_retcode := ERROR;
688 END IF;
689 FND_FILE.PUT_LINE(FND_FILE.LOG,
690 'BILLING AMOUNT =' || to_char(p_billing_amount));
691
692 IF (P_Next_Bill_Date >= P_End_Date_Active ) THEN
693 P_Next_Bill_date := Null;
694 END IF;
695 END IF;
696 ELSE
697
698 /* Bill in arrears for the billing period between */
699 /* Start Date Active and First Bill Date. */
700 /*
701 FND_FILE.PUT_LINE(FND_FILE.LOG,'STDA =' || to_char(p_start_date_active));
702 FND_FILE.PUT_LINE(FND_FILE.LOG,'FBD =' || to_char(p_FIRST_bill_date));
703 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMNT =' || to_char(p_extended_price));
704 FND_FILE.PUT_LINE(FND_FILE.LOG,'DURN =' || to_char(p_duration_quantity));
705 FND_FILE.PUT_LINE(FND_FILE.LOG,'ITEM =' ||
706 to_char(p_service_inventory_item_id));
707 FND_FILE.PUT_LINE(FND_FILE.LOG,'PERD =' || (p_unit_of_measure_code));
708 FND_FILE.PUT_LINE(FND_FILE.LOG,'FREQ =' || (p_billing_frequency_period));
709 */
710
711 P_Billing_amount := Calculate_Txn_Amount (
712 'Y',
713 p_Start_Date_Active,
714 P_First_Bill_Date,
715 p_extended_price,
716 p_duration_quantity,
717 p_service_inventory_item_id,
718 p_Unit_of_Measure_code,
719 p_billing_frequency_period);
720
721 IF (P_Billing_Amount > 0) THEN
722 IF (P_Next_Bill_Date = P_End_Date_Active ) THEN
723 P_Next_Bill_date := Null;
724 ELSE
725 p_Next_Bill_Date :=Calculate_next_bill_date(
726 --p_start_Date_active,
727 p_first_bill_date,
728 p_End_Date_active,
729 p_bill_on,
730 p_Service_inventory_item_id,
731 p_billing_frequency_period );
732 p_billed_From_date := P_Start_Date_Active;
733 p_billed_until_date := P_First_Bill_Date - 1;
734 END IF;
735 ELSE
736 v_retcode := ERROR;
737 END IF;
738 END IF;
739
740 return (v_retcode);
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 v_retcode := ERROR;
745 Current_error_Code := to_Char(SQLCODE);
746 FND_FILE.PUT_LINE( FND_FILE.LOG,
747 'Error in Process First_Bill Date' );
748 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
749 return (v_retcode);
750
751 END Process_First_Bill_date;
752
753
754
755
756 FUNCTION Process_Next_Bill_Date
757 (
758 P_BILLING_AMOUNT OUT NUMBER,
759 P_BILLED_FROM_DATE OUT DATE,
760 P_BILLED_UNTIL_DATE IN OUT DATE,
761 P_NEXT_BILL_DATE IN OUT DATE,
762 P_BILL_ON IN OUT NUMBER,
763 P_FIRST_BILL_DATE IN DATE,
764 P_START_DATE_ACTIVE IN DATE,
765 P_END_DATE_ACTIVE IN DATE,
766 P_INVOICE_AMOUNT IN NUMBER,
767 P_EXTENDED_PRICE IN NUMBER,
768 P_DURATION_QUANTITY IN NUMBER,
769 P_SERVICE_INVENTORY_ITEM_ID IN NUMBER,
770 P_UNIT_OF_MEASURE_CODE IN VARCHAR2,
771 P_BILLING_FREQUENCY_PERIOD IN VARCHAR2
772 )
773 RETURN NUMBER IS
774 v_retcode NUMBER := SUCCESS;
775
776 BEGIN
777
778 IF (P_Next_Bill_Date = p_End_Date_Active) THEN
779 /* Bill for the remaining amount */
780
781 P_Billing_Amount := P_invoice_amount;
782 P_Next_Bill_date := NULL;
783 --P_Next_Bill_date := P_end_date_active;
784 P_Billed_From_Date := P_Billed_Until_Date;
785 P_Billed_until_Date := P_end_date_active;
786
787 ELSIF (P_Next_Bill_Date = p_Billed_Until_date) THEN
788 /* Bill in advance for the billing period between */
789 /* Billed until date and the newly calculated */
790 /* Next Bill Date. */
791
792 p_next_bill_date :=CS_CONTRACT_BILLING.Calculate_next_bill_date(
793 p_billed_Until_date ,
794 p_End_Date_active,
795 p_bill_on,
796 p_Service_inventory_item_id,
797 p_billing_frequency_period );
798
799
800 IF (p_next_bill_date IS NULL ) THEN
801 v_retcode := ERROR;
802 ELSE
803
804 IF (P_Next_Bill_Date = p_End_Date_Active) THEN
805 /* Bill for the remaining amount */
806 P_Billed_From_Date := P_Billed_Until_Date;
807 P_Billed_until_Date := P_End_Date_Active;
808
809 P_Billing_Amount := P_invoice_amount;
810 P_Next_Bill_date := NULL;
811 --P_Next_Bill_date := P_end_date_active;
812
813 ELSE
814 P_Billed_From_Date := P_Billed_Until_Date;
815 P_Billed_until_Date := P_Next_bill_Date - 1;
816 P_Billing_amount := Calculate_Txn_Amount (
817 'Y',
818 p_billed_from_date,
819 p_next_Bill_Date,
820 p_extended_price,
821 p_duration_quantity,
822 p_service_inventory_item_id,
823 p_Unit_of_Measure_code,
824 p_billing_frequency_period);
825 END IF;
826
827 IF (P_Billing_Amount > 0) THEN
828 NULL;
829 ELSE
830 v_retcode := ERROR;
831 END IF;
832 END IF;
833
834 ELSE
835 /* Bill in arrears for the billing period between */
836 /* Billed until date and Next Bill Date. */
837
838 P_Billing_amount := Calculate_Txn_Amount (
839 'Y',
840 p_Billed_Until_Date,
841 p_Next_Bill_Date,
842 p_extended_price,
843 p_duration_quantity,
844 p_service_inventory_item_id,
845 p_Unit_of_Measure_code,
846 p_billing_frequency_period);
847 FND_FILE.PUT_LINE(FND_FILE.LOG,
848 'BILLING AMOUNT='||to_char(p_billing_amount));
849
850 IF (P_Billing_Amount > 0) THEN
851 P_Billed_From_Date := P_Billed_Until_Date;
852 P_Billed_until_Date := P_Next_Bill_Date - 1;
853
854 IF (P_Next_Bill_Date = P_End_Date_Active ) THEN
855 P_Next_Bill_date := Null;
856 ELSE
857 p_Next_Bill_Date :=Calculate_next_bill_date(
858 p_Billed_Until_Date + 1,
859 p_End_Date_active,
860 p_bill_on,
861 p_Service_inventory_item_id,
862 p_billing_frequency_period );
863 END IF;
864 FND_FILE.PUT_LINE(FND_FILE.LOG,
865 'NBD ='||to_char(p_next_bill_date));
866
867 FND_FILE.PUT_LINE(FND_FILE.LOG,
868 'BILLED FROM='|| to_char(p_billed_from_date));
869 FND_FILE.PUT_LINE(FND_FILE.LOG,
870 'BILLED TILL='||to_char(p_billed_until_date));
871 ELSE
872 v_retcode := ERROR;
873 END IF;
874 END IF;
875
876 return (v_retcode);
877 EXCEPTION
878 WHEN OTHERS THEN
879 v_retcode := ERROR;
880 Current_error_Code := to_Char(SQLCODE);
881 FND_FILE.PUT_LINE( FND_FILE.LOG,
882 'Error in Process Next Date' );
883 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
884 return (v_retcode);
885
886 END Process_Next_Bill_date;
887
888 FUNCTION Insert_cs_cont_bill_iface
889 (
890 P_BILLING_AMOUNT IN NUMBER,
891 P_billed_from_date IN DATE,
892 P_billed_until_date IN DATE,
893 P_transaction_date IN DATE,
894 P1_CONTRACT_ID IN NUMBER,
895 P1_CP_SERVICE_ID IN NUMBER,
896 P_quantity IN NUMBER
897 )RETURN NUMBER IS
898 txn_id NUMBER;
899 v_return_status VARCHAR2(1);
900 v_msg_count NUMBER;
901 v_msg_data VARCHAR2(2000);
902
903 v_retcode NUMBER := SUCCESS;
904 contracts_interface_id NUMBER;
905 object_version_number NUMBER;
906
907 BEGIN
908 DBMS_TRANSACTION.SAVEPOINT('Insert_Interface');
909
910
911 SELECT MAX(CP_SERVICE_TRANSACTION_ID)
912 INTO txn_id
913 FROM CS_CP_SERVICE_TRANSACTIONS
914 WHERE CP_SERVICE_ID = p1_cp_service_id
915 AND TRANSACTION_TYPE_CODE NOT IN ('TERMINATE');
916
917 FND_FILE.PUT_LINE( FND_FILE.LOG, 'p1_cp_service_id ' || p1_cp_service_id);
918 FND_FILE.PUT_LINE( FND_FILE.LOG, 'txn_id ' || txn_id);
919 CS_CONTINTF_PVT.Insert_Row
920 (
921 p_api_version => 1.0,
922 p_init_msg_list => 'T',
923 p_validation_level => 100,
924 p_commit => 'F',
925 x_return_status => v_return_status,
926 x_msg_count => v_msg_count,
927 x_msg_data => v_msg_data,
928 p_cp_service_transaction_id => txn_id,
929 p_cp_service_id => p1_cp_service_id,
930 p_contract_id => p1_contract_id,
931 p_ar_trx_type => 'INV',
932 p_trx_start_date => p_billed_from_date,
933 p_trx_end_date => p_billed_until_date,
934 p_trx_date => p_transaction_date,
935 p_trx_amount => round(p_billing_amount,2),
936 p_reason_code => 'CONTRACTS',
937 p_reason_comments => NULL,
938 p_cp_quantity => NULL,
939 p_concurrent_process_id => NULL,
940 p_created_by => user_id,
941 p_creation_date => sysdate,
942 x_contracts_interface_id => contracts_interface_id,
943 x_object_version_number => object_version_number);
944
945 FND_FILE.PUT_LINE( FND_FILE.LOG, 'v_return_status ' ||
946 v_return_status);
947 IF (v_return_status <> 'S' and v_msg_count >= 1) THEN
948 v_retcode := ERROR;
949 Current_error_Code := to_Char(SQLCODE);
950 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Inserting in Interface tbl' );
951 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLCODE );
952 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
953 return (v_retcode);
954 ELSE
955 v_retcode := SUCCESS;
956 Current_error_Code := to_Char(SQLCODE);
957 FND_FILE.PUT_LINE( FND_FILE.LOG, 'records Inserted in Interface tbl');
958 return(v_retcode);
959 END IF;
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 v_retcode := ERROR;
964 Current_error_Code := to_Char(SQLCODE);
965 FND_FILE.PUT_LINE( FND_FILE.LOG,
966 'Error in Insert_CS_cont_bill_iface ' );
967 v_msg_data := FND_MESSAGE.Get;
968 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Err in UPdate CS_CP_SERVICES :'
969 || v_msg_data);
970 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLCODE );
971 return (v_retcode);
972
973 END;
974
975
976 FUNCTION Update_CS_CP_Services
977 (
978 P_CONTRACT_ID IN NUMBER,
979 P_CP_SERVICE_ID IN NUMBER,
980 P_Next_Bill_Date IN DATE
981 ) RETURN NUMBER IS
982
983 v_retcode NUMBER := SUCCESS;
984 dummy VARCHAR2(02) ;
985 BEGIN
986 FND_FILE.PUT_LINE( FND_FILE.LOG, 'CS_CP_SERVICES Before' );
987
988
989 -- This select is required only to lock the record.
990 SELECT 'X'
991 INTO dummy
992 FROM CS_CP_SERVICES
993 WHERE contract_id = p_contract_id AND
994 cp_service_id = p_cp_service_id
995 FOR UPDATE OF cp_service_id;
996
997
998 UPDATE CS_CP_SERVICES
999 -- SET SERVICE_TXN_AVAILABILITY_CODE = 'RESERVED' ,
1000 SET NEXT_BILL_DATE = p_Next_Bill_Date
1001 WHERE CONTRACT_ID = p_Contract_Id AND
1002 CP_SERVICE_ID = p_cp_service_id;
1003
1004 Current_Error_Code := to_char(SQLCODE);
1005 FND_FILE.PUT_LINE( FND_FILE.LOG, 'CS_CP_SERVICES Updated' );
1006 return (v_retcode);
1007
1008 EXCEPTION
1009 WHEN others THEN
1010 IF (SQLCODE = -54) THEN
1011 FND_FILE.PUT_LINE(FND_FILE.LOG,
1012 'The record has been locked: Contract_id : '
1013 || p_contract_id);
1014 FND_FILE.PUT_LINE(FND_FILE.LOG,
1015 ' CP_SERVICE_id : '
1016 || p_cp_service_id);
1017 ELSE
1018 RAISE;
1019 END IF;
1020 v_retcode := ERROR;
1021 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('Insert_Interface');
1022 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Transaction Rollbacked ' );
1023 return (v_retcode);
1024 END;
1025
1026
1027
1028
1029 FUNCTION Calculate_Next_Bill_Date
1030 (
1031 P_LAST_TXN_DATE IN DATE,
1032 P_END_DATE_ACTIVE IN DATE,
1033 P_BILL_ON IN OUT NUMBER,
1034 P_INVENTORY_ITEM_ID IN NUMBER,
1035 P_FROM_UNIT IN VARCHAR2
1036 )RETURN DATE IS
1037
1038 temp_to_unit VARCHAR2(15);
1039 temp_from_unit VARCHAR2(15);
1040
1041 Day_Of_week VARCHAR2(15);
1042 next_bill_date DATE;
1043 Converted_Duration NUMBER;
1044 BEGIN
1045
1046 month_unit :=
1047 FND_PROFILE.VALUE('MONTH_UNIT_OF_MEASURE');
1048 temp_to_unit :=
1049 FND_PROFILE.VALUE('DAY_UNIT_OF_MEASURE');
1050
1051 FND_FILE.PUT_LINE( FND_FILE.LOG,'p_From_unit = ' ||
1052 (p_From_Unit));
1053
1054
1055 IF (p_from_unit = month_unit) THEN
1056 Next_Bill_date := ADD_MONTHS(p_last_txn_Date , 1);
1057 ELSE
1058
1059 Converted_Duration := Convert_Duration(
1060 'Y',
1061 1,
1062 p_inventory_item_id,
1063 p_from_unit,
1064 temp_to_unit);
1065
1066
1067 IF (Converted_Duration > 0 )THEN
1068 Next_Bill_Date := p_Last_Txn_Date + Converted_Duration;
1069 ELSE
1070 Next_Bill_Date := NULL ;
1071 END IF;
1072 END IF;
1073
1074 IF (Next_Bill_Date is not null) THEN
1075 Calc_Actual_Next_Bill_Date(
1076 Next_Bill_Date ,
1077 P_Bill_On
1078 );
1079
1080 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Next Bill Date22 = ' ||
1081 to_char(Next_Bill_Date));
1082 FND_FILE.PUT_LINE(FND_FILE.LOG, 'end_date_Active22 = ' ||
1083 to_char(p_end_date_active));
1084
1085 IF (Next_Bill_Date > p_end_date_active) THEN
1086 Next_Bill_date := p_end_date_active;
1087 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Actual Next Bill Date22 = ' ||
1088 to_char(Next_Bill_Date));
1089 END IF;
1090 END IF;
1091
1092 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Actual Next Bill Date = ' ||
1093 to_char(Next_Bill_Date));
1094 RETURN(Next_Bill_Date);
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 Current_error_Code := to_Char(SQLCODE);
1099 Next_Bill_Date := NULL;
1100 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Next Bill Date' );
1101 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1102 RETURN(Next_Bill_Date);
1103 END Calculate_Next_Bill_date;
1104
1105
1106
1107
1108 PROCEDURE Calc_Actual_Next_Bill_date(
1109 P_NEXT_BILL_DATE IN OUT DATE,
1110 P_BILL_ON IN OUT NUMBER
1111 )IS
1112 Next_Day NUMBER;
1113 Next_month NUMBER;
1114 Next_year NUMBER;
1115 temp_date varchar2(20);
1116
1117 BEGIN
1118 Next_Day := TO_NUMBER(TO_CHAR(P_Next_Bill_Date, 'DD'));
1119 Next_Month := TO_NUMBER(TO_CHAR(P_Next_Bill_Date, 'MM'));
1120 Next_Year := TO_NUMBER(TO_CHAR(P_Next_Bill_Date,'YYYY'));
1121
1122
1123 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BILL ON = ' ||
1124 to_char(P_bill_On));
1125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEXT DAY = ' ||
1126 to_char(Next_day));
1127 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEXT MONTH = ' ||
1128 to_char(Next_Month));
1129 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEXT YEAR = ' ||
1130 to_char(Next_Year));
1131
1132 IF ((p_bill_on IS NULL )OR (p_bill_on = 0)) THEN
1133 NULL ;
1134 ELSIF (p_bill_on = 31) THEN
1135 Next_Month := Next_Month + 1;
1136 IF (Next_Month = 11 ) THEN
1137 Next_Month := 01;
1138 Next_Year := Next_Year + 1;
1139 END IF;
1140 temp_date := '01' ||'-' || Next_Month ||'-' || Next_year;
1141 P_Next_Bill_Date := TO_DATE( temp_date, 'DD-MM-YYYY') - 1;
1142 ELSIF (Next_Day <= p_bill_on) THEN
1143
1144 IF (p_bill_on = 29 and Next_Month = 02 ) THEN
1145 p_bill_on := 01;
1146 Next_Month := 03;
1147 END IF;
1148
1149 temp_date := p_bill_on ||'-' || Next_Month ||'-' || Next_year;
1150
1151 P_Next_Bill_Date := TO_DATE( temp_date, 'DD-MM-YYYY');
1152 ELSE
1153 IF (p_bill_on = 29 and Next_Month = 01 ) THEN
1154 p_bill_on := 01;
1155 Next_Month := 02;
1156 ELSIF (Next_Month = 12 ) THEN
1157 Next_Month := 01;
1158 Next_Year := Next_Year + 1;
1159 END IF;
1160 temp_date := p_bill_on ||'-' || (Next_Month + 1) ||'-' || Next_year;
1161 P_Next_Bill_Date := TO_DATE( temp_date, 'DD-MM-YYYY');
1162 END IF;
1163 /*
1164 FND_FILE.PUT_LINE( FND_FILE.LOG,
1165 'Next Bill Date IS = ' || to_char(P_Next_Bill_Date));
1166 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1167 */
1168
1169 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NEXT BILL_DATE = ' ||
1170 to_char(p_Next_Bill_Date));
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 Current_error_Code := to_Char(SQLCODE);
1174 p_Next_Bill_Date := NULL;
1175 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Actual Next Bill Date' );
1176 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1177 END Calc_Actual_Next_Bill_date;
1178
1179
1180
1181
1182
1183
1184
1185 /* This procedure is only used by the Terminate Contract form */
1186 /* to calculate the adjusted service amount, when a service */
1187 /* line is terminated on a date which is in a billing period `*/
1188 /* which has not been invoiced .*/
1189
1190 FUNCTION Get_Final_Adjustment (
1191 P_CONC_PROGRAM IN VARCHAR2,
1192 P_TXN_START_DATE IN DATE,
1193 P_TXN_END_DATE IN DATE,
1194 P_SERVICE_AMOUNT IN NUMBER,
1195 P_SERVICE_DURATION IN NUMBER,
1196 P_INVENTORY_ITEM_ID IN NUMBER,
1197 P_SERVICE_PERIOD IN VARCHAR2,
1198 P_BILL_FREQUENCY IN VARCHAR2
1199 )RETURN NUMBER IS
1200 Average_Amount NUMBER;
1201 Transaction_Amount NUMBER;
1202 Adjusted_Amount NUMBER;
1203 BEGIN
1204 /* The procedure Calculate_Txn_Amount is used to calculate */
1205 /* the invoice amount for a specific period*/
1206 Transaction_amount := Calculate_Txn_Amount (
1207 p_conc_program,
1208 p_txn_start_date,
1209 p_txn_end_date,
1210 p_Service_Amount,
1211 p_service_duration,
1212 p_inventory_item_id,
1213 p_service_period,
1214 p_bill_frequency);
1215
1216 IF (Transaction_Amount > 0) THEN
1217 /* The Final adjusted amount is the diference between */
1218 /* the total service amount and the transaction amount */
1219
1220 Adjusted_Amount := p_service_amount - Transaction_Amount;
1221 ELSE
1222 Adjusted_Amount := -99;
1223 END IF;
1224
1225 RETURN(Adjusted_Amount);
1226
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 Current_error_Code := to_Char(SQLCODE);
1230 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Get Final Adjust' );
1231 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1232 RETURN(Adjusted_Amount);
1233
1234 END Get_Final_Adjustment;
1235
1236 FUNCTION Calculate_Txn_Amount (
1237 P_CONC_PROGRAM IN VARCHAR2,
1238 P_TXN_START_DATE IN DATE,
1239 P_TXN_END_DATE IN DATE,
1240 P_SERVICE_AMOUNT IN NUMBER,
1241 P_SERVICE_DURATION IN NUMBER,
1242 P_INVENTORY_ITEM_ID IN NUMBER,
1243 P_SERVICE_PERIOD IN VARCHAR2,
1244 P_BILL_FREQUENCY IN VARCHAR2
1245 )RETURN NUMBER IS
1246 Average_Amount NUMBER;
1247 v_retcode NUMBER := SUCCESS;
1248 Transaction_Amount NUMBER;
1249 Txn_Duration_Days NUMBER;
1250 Converted_Duration NUMBER;
1251 temp_end_date DATE;
1252 p_temp_from_unit VARCHAR2(5);
1253 day_unit varchar2(5);
1254 BEGIN
1255 /*
1256
1257 FND_FILE.PUT_LINE(FND_FILE.LOG,
1258 'Service Amount =' || to_char(p_service_amount));
1259 FND_FILE.PUT_LINE(FND_FILE.LOG,
1260 'Service Duration=' || to_char(p_service_duration));
1261 FND_FILE.PUT_LINE(FND_FILE.LOG,
1262 'inventory_Item_Id =' || to_char(p_inventory_item_id));
1263 FND_FILE.PUT_LINE(FND_FILE.LOG,
1264 'Service Period=' || p_service_period);
1265 FND_FILE.PUT_LINE(FND_FILE.LOG,
1266 'p_bill_frequency =' || p_bill_frequency);
1267
1268 */
1269 Average_amount := Calculate_Average_Amount (
1270 p_conc_program,
1271 p_Service_Amount,
1272 p_service_duration,
1273 p_inventory_item_id,
1274 p_service_period,
1275 p_bill_frequency);
1276
1277
1278 IF (Average_Amount > 0) THEN
1279
1280 IF (p_txn_start_date >= p_txn_end_date ) THEN
1281 Transaction_Amount := -99 ;
1282 v_retcode := 1;
1283 ELSE
1284 p_temp_from_unit :=
1285 FND_PROFILE.VALUE('DAY_UNIT_OF_MEASURE');
1286 month_unit :=
1287 FND_PROFILE.VALUE('MONTH_UNIT_OF_MEASURE');
1288
1289 Txn_Duration_Days := p_txn_end_date - p_txn_start_date;
1290 --Converted_Duration := Txn_Duration_Days + 1;
1291
1292 /******** 11/16/98 (average amount per day) ***/
1293
1294 temp_end_date := p_txn_end_date + 1;
1295 /*
1296 DBMS_OUTPUT.PUT_LINE('TEMP_END_DATE :' ||
1297 to_char(temp_end_date));
1298 DBMS_OUTPUT.PUT_LINE('TXN_START_DATE :' ||
1299 to_char(p_txn_start_date));
1300 DBMS_OUTPUT.PUT_LINE('MONTH_UNIT :' || (month_unit));
1301 */
1302
1303 IF (p_bill_frequency = month_unit) THEN
1304 Converted_duration :=
1305 MONTHS_BETWEEN(temp_end_date ,p_txn_start_date);
1306 ELSE
1307 Converted_Duration := Convert_Duration(
1308 p_conc_program,
1309 Txn_duration_Days,
1310 p_inventory_item_id,
1311 p_temp_from_unit,
1312 p_bill_frequency);
1313
1314 END IF;
1315 /******* 11/16/98 *********/
1316
1317 --DBMS_OUTPUT.PUT_LINE(
1318 -- 'Converted DurationIN='|| to_char(Converted_Duration));
1319
1320 IF (Converted_Duration > 0 )THEN
1321 Transaction_Amount := Average_amount *
1322 Converted_Duration;
1323 v_retcode := 0;
1324 ELSE
1325 Transaction_Amount := -99 ;
1326 v_retcode := 2;
1327 END IF;
1328 END IF;
1329 ELSE
1330 Transaction_Amount := -99 ;
1331 v_retcode := 3;
1332 END IF;
1333
1334
1335
1336 IF p_conc_program = 'Y'THEN
1337 if v_retcode = 0 THEN
1338 FND_FILE.PUT_LINE( FND_FILE.LOG,
1339 'Transaction Amount calculated ');
1340 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1341 elsif v_retcode = 1 THEN
1342 FND_FILE.PUT_LINE( FND_FILE.LOG,
1343 'Txn End Date must be greater than tx start date ');
1344 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1345 elsif v_retcode = 2 THEN
1346 FND_FILE.PUT_LINE( FND_FILE.LOG,
1347 'Negative converted duration ');
1348 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1349 elsif v_retcode = 3 THEN
1350 FND_FILE.PUT_LINE( FND_FILE.LOG,
1351 'Negative average amount ');
1352 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1353 End if;
1354 END IF;
1355
1356 RETURN (Transaction_Amount);
1357
1358
1359
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 Current_error_Code := to_Char(SQLCODE);
1363 IF p_conc_program = 'Y'THEN
1364 FND_FILE.PUT_LINE( FND_FILE.LOG,
1365 'Error in Calculate Transaction Amount' );
1366 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1367 END IF;
1368 RETURN (Transaction_Amount);
1369
1370
1371 END Calculate_Txn_Amount;
1372
1373
1374
1375 FUNCTION Calculate_Average_Amount(
1376 P_CONC_PROGRAM IN VARCHAR2,
1377 P_SERVICE_AMOUNT IN NUMBER,
1378 P_SERVICE_DURATION IN NUMBER,
1379 P_INVENTORY_ITEM_ID IN NUMBER,
1380 P_FROM_UNIT IN VARCHAR2,
1381 P_TO_UNIT IN VARCHAR2
1382 )
1383 RETURN Number IS
1384 Average_Amount Number;
1385 Converted_Duration Number;
1386 day_unit Varchar2(05);
1387 BEGIN
1388 /*** Calculate the average amount per day ***/
1389 /*
1390 day_unit :=
1391 FND_PROFILE.VALUE('DAY_UNIT_OF_MEASURE');
1392 Converted_Duration := Convert_Duration(
1393 p_Conc_program,
1394 p_service_duration,
1395 p_inventory_item_id,
1396 p_from_unit,
1397 day_unit);
1398
1399 IF (Converted_Duration > 0 )THEN
1400 Average_Amount := p_service_amount/ Converted_Duration;
1401 ELSE
1402 Average_amount := -99;
1403 END IF;
1404
1405
1406
1407 */
1408
1409 IF (p_from_unit = p_to_unit) THEN
1410 Average_Amount := p_service_amount / p_service_duration;
1411 ELSE
1412 Converted_Duration := Convert_Duration(
1413 p_Conc_program,
1414 p_service_duration,
1415 p_inventory_item_id,
1416 p_from_unit,
1417 p_to_unit);
1418
1419
1420 IF (Converted_Duration > 0 )THEN
1421 Average_Amount := p_service_amount/ Converted_Duration;
1422 ELSE
1423 Average_amount := -99;
1424 END IF;
1425 END IF;
1426
1427
1428
1429 --DBMS_OUTPUT.PUT_LINE('AVERAGE_AMOUNT : ' || to_char(average_amount));
1430 IF (P_Conc_Program = 'Y')THEN
1431 FND_FILE.PUT_LINE( FND_FILE.LOG,
1432 'Average Amount = ' || to_char(average_amount));
1433 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1434 END IF;
1435
1436
1437
1438
1439 RETURN (Average_Amount);
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 Current_error_Code := to_Char(SQLCODE);
1444 IF (P_Conc_Program = 'Y')THEN
1445 FND_FILE.PUT_LINE( FND_FILE.LOG,
1446 'Error in Calculate Average Amount' );
1447 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1448 END IF;
1449 RETURN (Average_Amount);
1450
1451 END Calculate_Average_Amount;
1452
1453
1454
1455
1456
1457
1458 FUNCTION Convert_Duration(
1459 P_CONC_PROGRAM IN VARCHAR2,
1460 P_SERVICE_DURATION IN NUMBER,
1461 P_INVENTORY_ITEM_ID IN NUMBER,
1462 P_FROM_UNIT IN VARCHAR2,
1463 P_TO_UNIT IN VARCHAR2
1464 ) RETURN NUMBER IS
1465 Converted_Duration Number;
1466 BEGIN
1467 /*
1468 FND_FILE.Put_Line (FND_FILE.LOG,'Service Duration = '||
1469 to_char(p_service_duration));
1470 FND_FILE.Put_Line (FND_FILE.LOG,'From_Unit = '||p_from_unit);
1471 FND_FILE.Put_Line (FND_FILE.LOG,'To_Unit = '||p_to_unit);
1472 */
1473 IF (p_from_unit = p_to_unit) then
1474 Converted_Duration := p_service_duration ;
1475 ELSE
1476 Converted_duration := INV_CONVERT.inv_um_convert
1477 ( item_id => p_inventory_item_id,
1478 precision => 5,
1479 From_quantity => p_service_duration,
1480 From_Unit => p_from_unit,
1481 To_Unit => p_to_unit,
1482 From_Name => NULL,
1483 To_Name => NULL);
1484
1485 END IF;
1486
1487
1488 IF (P_Conc_Program = 'Y')THEN
1489 FND_FILE.PUT_LINE( FND_FILE.LOG,'Converted Duration is ='||
1490 to_char(Converted_Duration));
1491 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
1492 END IF;
1493
1494
1495 RETURN ( Converted_Duration);
1496
1497
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 Current_error_Code := to_Char(SQLCODE);
1501 IF (P_Conc_Program = 'Y')THEN
1502 FND_FILE.PUT_LINE( FND_FILE.LOG,
1503 'Error in Convert Duration' );
1504 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1505 END IF;
1506 RETURN (Converted_Duration);
1507
1508 END Convert_Duration;
1509
1510
1511
1512 Procedure Print_Error IS
1513 l_count number;
1514 l_msg varchar2(2000);
1515 begin
1516 Fnd_msg_pub.Count_And_get(p_count => l_count,
1517 p_data => l_msg,
1518 p_encoded => 'F');
1519 if l_count = 0
1520 then
1521 null;
1522 elsif l_count = 1
1523 then
1524 FND_FILE.PUT_LINE( FND_FILE.LOG, l_msg);
1525 else
1526 For I in 1..l_count
1527 loop
1528 l_msg := fnd_msg_pub.get(I,'F');
1529 FND_FILE.PUT_LINE( FND_FILE.LOG, l_msg);
1530 end loop;
1531 end if;
1532 FND_MSG_PUB.initialize;
1533 Exception
1534 when others then
1535 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
1536 End Print_Error;
1537 END CS_CONTRACT_BILLING;
1538