[Home] [Help]
PACKAGE BODY: APPS.CS_GET_CONTRACTS_PUB
Source
1 PACKAGE BODY CS_GET_CONTRACTS_PUB AS
2 /* $Header: csctglcb.pls 115.12 99/07/16 08:52:46 porting ship $ */
3
4 -- Start of comments
5 -- API name : Get_List_of_Contracts
6 -- Type : Public
7 -- Pre-reqs : None.
8 -- Function : This procedure gets a list of contracts for the
9 -- specified customer/customer product/site/system
10 --
11 -- Parameters :
12 -- IN :
13 -- p_api_version NUMBER Required
14 -- p_init_msg_list VARCHAR2 Required
15 -- p_commit VARCHAR2 Required
16 -- p_Customer_Product_id NUMBER
17 -- p_Business_Process_Id NUMBER Required
18 -- p_Charge_Date_Time DATE Required
19 -- p_Time_Zone_Id NUMBER Required
20 -- p_Exception_coverage_flag VARCHAR2 Required
21 -- OUT :
22 --
23 -- VARCHAR2
24 -- x_Overlap_flag VARCHAR2
25 -- x_return_status VARCHAR2
26 -- x_msg_count NUMBER
27 -- x_msg_data VARCHAR2
28 --End of comments
29
30 PROCEDURE Get_List_Of_Contracts (
31 p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2 ,
33 p_commit IN VARCHAR2 ,
34 p_Customer_Product_Id IN OUT NUMBER,
35 p_Business_Process_Id IN NUMBER,
36 p_charge_date_time IN DATE,
37 p_time_zone_id IN NUMBER,
38 p_exception_coverage_flag IN VARCHAR2,
39 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
40 x_Contract_Id OUT NUMBER,
41 x_Contract_Number OUT NUMBER,
42 x_Contract_Status OUT VARCHAR2,
43 x_Contract_Type OUT VARCHAR2,
44 x_Contract_Group OUT VARCHAR2,
45 x_Contract_Duration OUT NUMBER,
46 x_Contract_Period OUT VARCHAR2,
47 x_Contract_Start_Date OUT DATE,
48 x_Contract_End_Date OUT DATE,
49 x_Contract_Agreement OUT VARCHAR2,
50 x_Contract_Price_List OUT VARCHAR2,
51 x_Currency_Code OUT VARCHAR2,
52 x_Invoicing_Rule OUT VARCHAR2,
53 x_Accounting_Rule OUT VARCHAR2,
54 x_Billing_Frequency_Period OUT VARCHAR2,
55 x_Bill_On OUT NUMBER,
56 x_First_Bill_Date OUT DATE,
57 x_Next_Bill_Date OUT DATE,
58 x_Workflow_Process_Id OUT NUMBER,
59 x_Renewal_Rule OUT VARCHAR2,
60 x_Termination_Rule OUT VARCHAR2,
61 x_Contract_Amount OUT NUMBER,
62 x_discount OUT VARCHAR2,
63 x_Service_Id OUT NUMBER,
64 x_Service OUT VARCHAR2,
65 x_Service_Start_Date OUT DATE,
66 x_Service_End_Date OUT DATE,
67 x_Coverage_Id OUT NUMBER,
68 x_coverage OUT VARCHAR2,
69 p_rec_count IN OUT NUMBER,
70 x_return_status OUT VARCHAR2,
71 x_msg_count OUT NUMBER,
72 x_msg_data OUT VARCHAR2 )IS
73
74 /** Cursor for getting list of contracts when customer
75 product is specified **/
76 CURSOR Get_Contracts IS
77 SELECT CONT.Contract_Id,
78 CONT.Contract_Number,
79 STAT.Name Status,
80 CONT.Start_Date_Active START_DATE_ACTIVE,
81 CONT.End_Date_Active END_DATE_ACTIVE,
82 CONT.Duration DURATION,
83 CONT.Currency_Code CURRENCY_CODE,
84 CONT.Bill_On BILL_ON,
85 CONT.First_Bill_Date FIRST_BILL_DATE,
86 CONT.Next_Bill_Date NEXT_BILL_DATE,
87 CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
88 CONT.Renewal_Rule RENEWAL_RULE,
89 CONT.Termination_Rule TERMINATION_RULE,
90 CONT.Contract_Amount CONTRACT_AMOUNT,
91 SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
92 MTL.Concatenated_Segments SERVICE,
93 SERV.Start_Date_Active SERVICE_START_DATE,
94 SERV.End_Date_Active SERVICE_END_DATE,
95 COV2.Coverage_Id COVERAGE_ID,
96 COV2.Name COVERAGE
97 FROM CS_Contracts CONT,
98 Cs_Coverages COV,
99 Cs_Coverages COV2,
100 Cs_Coverage_Txn_Groups CTG,
101 CS_Contract_Statuses STAT,
102 Cs_Covered_Products COVPROD,
103 Cs_Contract_Cov_Levels CL,
104 Mtl_System_Items_Kfv MTL,
105 CS_Contract_Statuses STAT2,
106 Cs_Cp_Services SERV
107 WHERE COVPROD.Customer_Product_Id = P_Customer_Product_Id
108 AND COVPROD.Coverage_Level_Id = CL.Coverage_Level_Id
109 AND CL.Cp_Service_Id = SERV.Cp_Service_Id
110 AND SERV.Contract_Id = CONT.Contract_Id
111 AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
112 AND CTG.Business_Process_Id = P_Business_Process_Id
113 AND (( COV.Coverage_Id = COV2.Coverage_Id
114 AND COV.Coverage_Id = CTG.Coverage_Id
115 AND P_Exception_Coverage_Flag = 'N')
116 OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
117 AND COV.Exception_Coverage_Id = CTG.Coverage_Id
118 AND P_Exception_Coverage_Flag = 'Y'))
119 AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
120 AND STAT2.Eligible_For_Entitlements = 'Y'
121 AND trunc(to_date(P_Charge_Date_Time))
122 BETWEEN SERV.Start_Date_Active AND
123 SERV.End_Date_Active
124 AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
125 AND MTL.Organization_Id =
126 FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
127 AND CONT.Contract_Status_Id = STAT.Contract_Status_Id
128 AND STAT.Eligible_for_Entitlements = 'Y';
129
130
131 l_covered_Yes_No VARCHAR2(1);
132 l_coverage_id NUMBER;
133 l_api_name VARCHAR2(30) := G_PKG_NAME;
134 l_api_version NUMBER := G_API_VERSION;
135 l_contract_rec_type CONTRACTS_REC_TYPE;
136
137 BEGIN
138
139 OPEN Get_Contracts;
140
141 LOOP
142
143 --DBMS_Output.Put_Line('Parameters: Business Process='||
144 -- to_char(p_business_process_id));
145 --DBMS_Output.Put_Line('Charge Date='|| to_char(p_charge_date_time));
146 --DBMS_Output.Put_Line('CP Id='|| to_char(p_customer_product_Id));
147 --DBMS_Output.Put_Line('Exception='|| p_exception_coverage_flag);
148
149 FETCH Get_Contracts
150 INTO x_Contract_Rec_Type;
151
152 --DBMS_Output.Put_Line('Fetched Record');
153
154 EXIT WHEN Get_Contracts%NOTFOUND;
155
156 l_coverage_id := x_contract_rec_type.V_Coverage_Id;
157
158 --DBMS_Output.Put_Line('Coverage id='|| to_char(l_coverage_id));
159 --DBMS_Output.PUt_Line('Time zone id='|| to_char(p_time_zone_id));
160
161 CS_Coverage_Service_PUB.Validate_Coverage_Times(
162 p_api_version,
163 p_init_msg_list,
164 p_commit,
165 l_Coverage_Id,
166 P_Business_Process_Id,
167 P_Charge_Date_Time,
168 P_Time_Zone_Id,
169 P_Exception_Coverage_Flag,
170 l_Covered_Yes_No,
171 x_return_status,
172 x_msg_count,
173 x_msg_data);
174
175 -- DBMS_Output.Put_Line('Validate Coverage covered='|| l_covered_yes_no);
176
177 l_covered_yes_no := 'Y';
178
179 IF (l_covered_yes_no = 'Y') THEN
180 migrate_to_table(
181 p_api_version,
182 p_init_msg_list,
183 p_commit,
184 x_contract_rec_type,
185 p_rec_count,
186 x_return_status,
187 x_msg_count,
188 x_msg_data );
189
190 IF (p_rec_count = 1) THEN
191 Migrate_to_Out_Variables(
192 p_api_version ,
193 p_init_msg_list ,
194 p_commit ,
195 x_contract_rec_type ,
196 x_Contract_Id ,
197 x_Contract_Number ,
198 x_Contract_Status ,
199 x_Contract_Type ,
200 x_Contract_Group ,
201 x_Contract_Duration ,
202 x_Contract_Period ,
203 x_Contract_Start_Date ,
204 x_Contract_End_Date ,
205 x_Contract_Agreement ,
206 x_Contract_Price_List ,
207 x_Currency_Code ,
208 x_Invoicing_Rule ,
209 x_Accounting_Rule ,
210 x_Billing_Frequency_Period ,
211 x_Bill_On ,
212 x_First_Bill_Date ,
213 x_Next_Bill_Date ,
214 x_Workflow_Process_Id ,
215 x_Renewal_Rule ,
216 x_Termination_Rule ,
217 x_Contract_Amount ,
218 x_discount ,
219 x_Service_Id ,
220 x_Service ,
221 x_Service_Start_Date ,
222 x_Service_End_Date ,
223 x_Coverage_Id ,
224 x_coverage ,
225 x_return_status ,
226 x_msg_count ,
227 x_msg_data );
228 END IF;
229
230 p_rec_count := p_rec_count + 1;
231 END IF;
232 END LOOP;
233
234 CLOSE Get_Contracts;
235
236 p_rec_count := p_rec_count -1;
237
238 TAPI_DEV_KIT.END_ACTIVITY( p_commit,
239 x_msg_count,
240 x_msg_data);
241
242 EXCEPTION
243 WHEN FND_API.G_EXC_ERROR THEN
244 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
245 (
246 l_api_name,
247 G_PKG_NAME,
248 'FND_API.G_RET_STS_ERROR',
249 x_msg_count,
250 x_msg_data,
251 '_Pvt'
252 );
253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
254 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
255 (
256 l_api_name,
257 G_PKG_NAME,
258 'FND_API.G_RET_STS_UNEXP_ERROR',
259 x_msg_count,
260 x_msg_data,
261 '_Pvt'
262 );
263 WHEN OTHERS THEN
264 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
265 (
266 l_api_name,
267 G_PKG_NAME,
268 'OTHERS',
269 x_msg_count,
270 x_msg_data,
271 '_Pvt',
272 SQLERRM
273 );
274
275 END Get_List_Of_Contracts;
276
277
278 PROCEDURE Get_List_Of_Contracts (
279 p_api_version IN NUMBER,
280 p_init_msg_list IN VARCHAR2 ,
281 p_commit IN VARCHAR2 ,
282 p_Coverage_Level_Value IN NUMBER,
283 p_coverage_level_code IN VARCHAR2,
284 p_Business_Process_Id IN NUMBER,
285 p_charge_date_time IN DATE,
286 p_time_zone_id IN NUMBER,
287 p_exception_coverage_flag IN VARCHAR2,
288 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
289 x_Contract_Id OUT NUMBER,
290 x_Contract_Number OUT NUMBER,
291 x_Contract_Status OUT VARCHAR2,
292 x_Contract_Type OUT VARCHAR2,
293 x_Contract_Group OUT VARCHAR2,
294 x_Contract_Duration OUT NUMBER,
295 x_Contract_Period OUT VARCHAR2,
296 x_Contract_Start_Date OUT DATE,
297 x_Contract_End_Date OUT DATE,
298 x_Contract_Agreement OUT VARCHAR2,
299 x_Contract_Price_List OUT VARCHAR2,
300 x_Currency_Code OUT VARCHAR2,
301 x_Invoicing_Rule OUT VARCHAR2,
302 x_Accounting_Rule OUT VARCHAR2,
303 x_Billing_Frequency_Period OUT VARCHAR2,
304 x_Bill_On OUT NUMBER,
305 x_First_Bill_Date OUT DATE,
306 x_Next_Bill_Date OUT DATE,
307 x_Workflow_Process_Id OUT NUMBER,
308 x_Renewal_Rule OUT VARCHAR2,
309 x_Termination_Rule OUT VARCHAR2,
310 x_Contract_Amount OUT NUMBER,
311 x_discount OUT VARCHAR2,
312 x_Service_Id OUT NUMBER,
313 x_Service OUT VARCHAR2,
314 x_Service_Start_Date OUT DATE,
315 x_Service_End_Date OUT DATE,
316 x_Coverage_Id OUT NUMBER,
317 x_coverage OUT VARCHAR2,
318 p_rec_count IN OUT NUMBER,
319 x_return_status OUT VARCHAR2,
320 x_msg_count OUT NUMBER,
321 x_msg_data OUT VARCHAR2 )IS
322
323 /** Cursor for getting list of contracts when customer
324 product is specified **/
325 CURSOR Get_Contracts IS
326 SELECT CONT.Contract_Id,
327 CONT.Contract_Number,
328 STAT.Name Status,
329 CONT.Start_Date_Active START_DATE_ACTIVE,
330 CONT.End_Date_Active END_DATE_ACTIVE,
331 CONT.Duration DURATION,
332 CONT.Currency_Code CURRENCY_CODE,
333 CONT.Bill_On BILL_ON,
334 CONT.First_Bill_Date FIRST_BILL_DATE,
335 CONT.Next_Bill_Date NEXT_BILL_DATE,
336 CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
337 CONT.Renewal_Rule RENEWAL_RULE,
338 CONT.Termination_Rule TERMINATION_RULE,
339 CONT.Contract_Amount CONTRACT_AMOUNT,
340 SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
341 MTL.Concatenated_Segments SERVICE,
342 SERV.Start_Date_Active SERVICE_START_DATE,
343 SERV.End_Date_Active SERVICE_END_DATE,
344 COV2.Coverage_Id COVERAGE_ID,
345 COV2.Name COVERAGE
346 FROM CS_Contracts CONT,
347 Cs_Coverages COV,
348 Cs_Coverages COV2,
349 Cs_Coverage_Txn_Groups CTG,
350 CS_Contract_Statuses STAT,
351 Cs_Contract_Cov_Levels CL,
352 Mtl_System_Items_Kfv MTL,
353 CS_Contract_Statuses STAT2,
354 Cs_Cp_Services SERV
355 WHERE CL.Coverage_Level_Value = P_Coverage_level_value
356 AND CL.Cp_Service_Id = SERV.Cp_Service_Id
357 AND SERV.Contract_Id = CONT.Contract_Id
358 AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
359 AND CTG.Business_Process_Id = P_Business_Process_ID
360 AND (( COV.Coverage_Id = COV2.Coverage_Id
361 AND COV.Coverage_Id = CTG.Coverage_Id
362 AND P_Exception_Coverage_Flag = 'N')
363 OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
364 AND P_Exception_Coverage_Flag = 'Y'
365 AND COV.Exception_Coverage_Id = CTG.Coverage_Id))
366 AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
367 AND STAT2.Eligible_For_Entitlements = 'Y'
368 AND trunc(p_charge_date_time)
369 BETWEEN SERV.Start_Date_Active AND
370 SERV.End_Date_Active
371 AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
372 AND MTL.Organization_Id =
373 FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
374 AND CONT.COntract_Status_Id = STAT.Contract_Status_Id
375 AND STAT.Eligible_for_Entitlements = 'Y';
376
377 l_covered_Yes_No VARCHAR2(1);
378 l_coverage_id NUMBER;
379 l_api_name VARCHAR2(30) := G_PKG_NAME;
380 l_api_version NUMBER := G_API_VERSION;
381
382 BEGIN
383
384 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
385 G_PKG_NAME,
386 l_api_version,
387 p_api_version,
388 p_init_msg_list,
389 '_Pvt',
390 x_return_status);
391 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
394 RAISE FND_API.G_EXC_ERROR;
395 END IF;
396
397 OPEN Get_Contracts;
398
399 LOOP
400
401 FETCH Get_Contracts
402 INTO x_contract_rec_type;
403
404 --DBMS_OUtput.Put_Line('After fetch');
405
406 EXIT WHEN Get_Contracts%NOTFOUND;
407
408 --DBMS_OUtput.Put_Line('not exited');
409
410 l_coverage_id := x_Contract_Rec_Type.V_coverage_id;
411
412 CS_Coverage_Service_PUB.Validate_Coverage_Times(
413 p_api_version,
414 p_init_msg_list,
415 p_commit,
416 l_Coverage_Id,
417 P_Business_Process_Id,
418 P_Charge_Date_Time,
419 P_Time_Zone_Id,
420 P_Exception_Coverage_Flag,
421 l_Covered_Yes_No,
422 x_return_status,
423 x_msg_count,
424 x_msg_data);
425
426 -- DBMS_OUtput.Put_LIne('Covered='|| l_covered_yes_no);
427
428 -- l_covered_yes_no := 'Y';
429 IF (l_Covered_Yes_No = 'Y') THEN
430
431 migrate_to_table(
432 p_api_version,
433 p_init_msg_list,
434 p_commit,
435 x_contract_rec_type,
436 p_rec_count,
437 x_return_status,
438 x_msg_count,
439 x_msg_data );
440
441 IF (p_rec_count = 1) THEN
442 Migrate_to_Out_Variables(
443 p_api_version ,
444 p_init_msg_list ,
445 p_commit ,
446 x_contract_rec_Type ,
447 x_Contract_Id ,
448 x_Contract_Number ,
449 x_Contract_Status ,
450 x_Contract_Type ,
451 x_Contract_Group ,
452 x_Contract_Duration ,
453 x_Contract_Period ,
454 x_Contract_Start_Date ,
455 x_Contract_End_Date ,
456 x_Contract_Agreement ,
457 x_Contract_Price_List ,
458 x_Currency_Code ,
459 x_Invoicing_Rule ,
460 x_Accounting_Rule ,
461 x_Billing_Frequency_Period ,
462 x_Bill_On ,
463 x_First_Bill_Date ,
464 x_Next_Bill_Date ,
465 x_Workflow_Process_Id ,
466 x_Renewal_Rule ,
467 x_Termination_Rule ,
468 x_Contract_Amount ,
469 x_discount ,
470 x_Service_Id ,
471 x_Service ,
472 x_Service_Start_Date ,
473 x_Service_End_Date ,
474 x_Coverage_Id ,
475 x_coverage ,
476 x_return_status ,
477 x_msg_count ,
478 x_msg_data );
479 END IF;
480
481 p_rec_count := p_rec_count + 1;
482 END IF;
483
484 END LOOP;
485
486 CLOSE Get_Contracts;
487
488 p_rec_count := p_rec_count -1;
489
490 --DBMS_Output.Put_Line('Rec Count='|| to_char(p_rec_count));
491
492 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
493
494 EXCEPTION
495 WHEN FND_API.G_EXC_ERROR THEN
496 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
497 (
498 l_api_name,
499 G_PKG_NAME,
500 'FND_API.G_RET_STS_ERROR',
501 x_msg_count,
502 x_msg_data,
503 '_Pvt'
504 );
505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
506 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
507 (
508 l_api_name,
509 G_PKG_NAME,
510 'FND_API.G_RET_STS_UNEXP_ERROR',
511 x_msg_count,
512 x_msg_data,
513 '_Pvt'
514 );
515 WHEN OTHERS THEN
516 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
517 (
518 l_api_name,
519 G_PKG_NAME,
520 'OTHERS',
521 x_msg_count,
522 x_msg_data,
523 '_Pvt',
524 SQLERRM
525 );
526
527 END Get_List_Of_Contracts;
528
529 PROCEDURE Get_List_Of_Contracts (
530 p_api_version IN NUMBER,
531 p_init_msg_list IN VARCHAR2 ,
532 p_commit IN VARCHAR2 ,
533 p_System_Id IN NUMBER,
534 p_site_use_Id IN NUMBER,
535 p_Business_Process_Id IN NUMBER,
536 p_charge_date_time IN DATE,
537 p_time_zone_id IN NUMBER,
538 p_exception_coverage_flag IN VARCHAR2,
539 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
540 x_Contract_Id OUT NUMBER,
541 x_Contract_Number OUT NUMBER,
542 x_Contract_Status OUT VARCHAR2,
543 x_Contract_Type OUT VARCHAR2,
544 x_Contract_Group OUT VARCHAR2,
545 x_Contract_Duration OUT NUMBER,
546 x_Contract_Period OUT VARCHAR2,
547 x_Contract_Start_Date OUT DATE,
548 x_Contract_End_Date OUT DATE,
549 x_Contract_Agreement OUT VARCHAR2,
550 x_Contract_Price_List OUT VARCHAR2,
551 x_Currency_Code OUT VARCHAR2,
552 x_Invoicing_Rule OUT VARCHAR2,
553 x_Accounting_Rule OUT VARCHAR2,
554 x_Billing_Frequency_Period OUT VARCHAR2,
555 x_Bill_On OUT NUMBER,
556 x_First_Bill_Date OUT DATE,
557 x_Next_Bill_Date OUT DATE,
558 x_Workflow_Process_Id OUT NUMBER,
559 x_Renewal_Rule OUT VARCHAR2,
560 x_Termination_Rule OUT VARCHAR2,
561 x_Contract_Amount OUT NUMBER,
562 x_discount OUT VARCHAR2,
563 x_Service_Id OUT NUMBER,
564 x_Service OUT VARCHAR2,
565 x_Service_Start_Date OUT DATE,
566 x_Service_End_Date OUT DATE,
567 x_Coverage_Id OUT NUMBER,
568 x_coverage OUT VARCHAR2,
569 p_rec_count IN OUT NUMBER,
570 x_return_status OUT VARCHAR2,
571 x_msg_count OUT NUMBER,
572 x_msg_data OUT VARCHAR2 )IS
573
574 /** Cursor for getting list of contracts when customer
575 product is specified **/
576 CURSOR Get_Contracts IS
577 SELECT CONT.Contract_Id,
578 CONT.Contract_Number,
579 STAT.Name Status,
580 CONT.Start_Date_Active START_DATE_ACTIVE,
581 CONT.End_Date_Active END_DATE_ACTIVE,
582 CONT.Duration DURATION,
583 CONT.Currency_Code CURRENCY_CODE,
584 CONT.Bill_On BILL_ON,
585 CONT.First_Bill_Date FIRST_BILL_DATE,
586 CONT.Next_Bill_Date NEXT_BILL_DATE,
587 CONT.Workflow_Process_Id WORKFLOW_PROCESS_ID,
588 CONT.Renewal_Rule RENEWAL_RULE,
589 CONT.Termination_Rule TERMINATION_RULE,
590 CONT.Contract_Amount CONTRACT_AMOUNT,
591 SERV.Service_Inventory_Item_Id SERVICE_INVENTORY_ITEM_ID,
592 MTL.Concatenated_Segments SERVICE,
593 SERV.Start_Date_Active SERVICE_START_DATE,
594 SERV.End_Date_Active SERVICE_END_DATE,
595 COV2.Coverage_Id COVERAGE_ID,
596 COV2.Name COVERAGE
597 FROM CS_Contracts CONT,
598 CS_Contract_Statuses STAT,
599 Cs_Coverages COV,
600 Cs_Coverages COV2,
601 Cs_Coverage_Txn_Groups CTG,
602 Cs_Contract_Cov_Levels CL,
603 Mtl_System_Items_Kfv MTL,
604 CS_Contract_Statuses STAT2,
605 Cs_Cp_Services SERV
606 WHERE CL.Coverage_Level_Value IN (P_System_Id, P_Site_Use_Id)
607 AND CL.Cp_Service_Id = SERV.Cp_Service_Id
608 AND SERV.Contract_Id = CONT.Contract_Id
609 AND SERV.Coverage_Schedule_Id = COV.Coverage_Id
610 AND CTG.Business_Process_Id = P_Business_Process_Id
611 AND (( COV.Coverage_Id = COV2.Coverage_Id
612 AND COV.Coverage_Id = CTG.Coverage_Id
613 AND P_Exception_Coverage_Flag = 'N')
614 OR ( COV.Exception_Coverage_Id = COV2.Coverage_Id
615 AND COV.Exception_Coverage_Id = CTG.Coverage_Id
616 AND P_Exception_Coverage_Flag = 'Y'))
617 AND SERV.Contract_Line_Status_Id = STAT2.Contract_Status_Id
618 AND STAT2.Eligible_For_Entitlements = 'Y'
619 AND trunc(p_charge_date_time)
620 BETWEEN SERV.Start_Date_Active AND
621 SERV.End_Date_Active
622 AND SERV.Service_Inventory_Item_Id = MTL.Inventory_Item_Id
623 AND MTL.Organization_Id =
624 FND_Profile.Value_Specific('SO_ORGANIZATION_ID')
625 AND CONT.Contract_Status_Id = STAT.Contract_Status_Id
626 AND STAT.Eligible_for_Entitlements = 'Y';
627
628 l_covered_Yes_No VARCHAR2(1);
629 l_coverage_id NUMBER;
630 l_api_name VARCHAR2(30) := G_PKG_NAME;
631 l_api_version NUMBER := G_API_VERSION;
632
633 BEGIN
634
635 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
636 G_PKG_NAME,
637 l_api_version,
638 p_api_version,
639 p_init_msg_list,
640 '_Pvt',
641 x_return_status);
642 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
645 RAISE FND_API.G_EXC_ERROR;
646 END IF;
647
648 --DBMS_Output.Put_Line('Procedure 4');
649
650 OPEN Get_Contracts;
651
652 LOOP
653
654 FETCH Get_Contracts
655 INTO x_contract_rec_type;
656
657 EXIT WHEN Get_Contracts%NOTFOUND;
658
659
660 l_coverage_id := x_Contract_Rec_Type.V_coverage_id;
661 --DBMS_Output.Put_Line('Procedure 4 fetched record');
662
663 CS_Coverage_Service_PUB.Validate_Coverage_Times(
664 p_api_version,
665 p_init_msg_list,
666 p_commit,
667 l_Coverage_Id,
668 P_Business_Process_Id,
669 P_Charge_Date_Time,
670 P_Time_Zone_Id,
671 P_Exception_Coverage_Flag,
672 l_Covered_Yes_No,
673 x_return_status,
674 x_msg_count,
675 x_msg_data);
676
677 --DBMS_Output.Put_Line('Covered=' || l_covered_yes_no);
678
679 -- l_covered_yes_no := 'Y';
680 IF (l_Covered_Yes_No = 'Y') THEN
681 migrate_to_table(
682 p_api_version,
683 p_init_msg_list,
684 p_commit,
685 x_contract_rec_type,
686 p_rec_count,
687 x_return_status,
688 x_msg_count,
689 x_msg_data );
690
691 Migrate_to_Out_Variables(
692 p_api_version ,
693 p_init_msg_list ,
694 p_commit ,
695 x_contract_rec_Type ,
696 x_Contract_Id ,
697 x_Contract_Number ,
698 x_Contract_Status ,
699 x_Contract_Type ,
700 x_Contract_Group ,
701 x_Contract_Duration ,
702 x_Contract_Period ,
703 x_Contract_Start_Date ,
704 x_Contract_End_Date ,
705 x_Contract_Agreement ,
706 x_Contract_Price_List ,
707 x_Currency_Code ,
708 x_Invoicing_Rule ,
709 x_Accounting_Rule ,
710 x_Billing_Frequency_Period ,
711 x_Bill_On ,
712 x_First_Bill_Date ,
713 x_Next_Bill_Date ,
714 x_Workflow_Process_Id ,
715 x_Renewal_Rule ,
716 x_Termination_Rule ,
717 x_Contract_Amount ,
718 x_discount ,
719 x_Service_Id ,
720 x_Service ,
721 x_Service_Start_Date ,
722 x_Service_End_Date ,
723 x_Coverage_Id ,
724 x_coverage ,
725 x_return_status ,
726 x_msg_count ,
727 x_msg_data );
728
729 p_rec_count := p_rec_count + 1;
730
731 END IF;
732 END LOOP;
733
734 CLOSE Get_Contracts;
735
736 p_rec_count := p_rec_count -1;
737
738 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
739
740 EXCEPTION
741 WHEN FND_API.G_EXC_ERROR THEN
742 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
743 (
744 l_api_name,
745 G_PKG_NAME,
746 'FND_API.G_RET_STS_ERROR',
747 x_msg_count,
748 x_msg_data,
749 '_Pvt'
750 );
751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
752 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
753 (
754 l_api_name,
755 G_PKG_NAME,
756 'FND_API.G_RET_STS_UNEXP_ERROR',
757 x_msg_count,
758 x_msg_data,
759 '_Pvt'
760 );
761 WHEN OTHERS THEN
762 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
763 (
764 l_api_name,
765 G_PKG_NAME,
766 'OTHERS',
767 x_msg_count,
768 x_msg_data,
769 '_Pvt',
770 SQLERRM
771 );
772
773 END Get_List_Of_Contracts;
774
775 PROCEDURE Get_List_Of_Contracts (
776 p_api_version IN NUMBER,
777 p_init_msg_list IN VARCHAR2 ,
778 p_commit IN VARCHAR2 ,
779 p_Customer_Product_Id IN OUT NUMBER,
780 p_Customer_Id IN OUT NUMBER,
781 p_site_use_Id IN OUT NUMBER,
782 p_system_Id IN OUT NUMBER,
783 p_inventory_Item_Id IN OUT NUMBER,
784 p_Business_Process_Id IN NUMBER,
785 p_charge_date_time IN DATE,
786 p_time_zone_id IN NUMBER,
787 p_exception_coverage_flag IN VARCHAR2,
788 x_Contract_Id OUT NUMBER,
789 x_Contract_Number OUT NUMBER,
790 x_Contract_Status OUT VARCHAR2,
791 x_Contract_Type OUT VARCHAR2,
792 x_Contract_Group OUT VARCHAR2,
793 x_Contract_Duration OUT NUMBER,
794 x_Contract_Period OUT VARCHAR2,
795 x_Contract_Start_Date OUT DATE,
796 x_Contract_End_Date OUT DATE,
797 x_Contract_Agreement OUT VARCHAR2,
798 x_Contract_Price_List OUT VARCHAR2,
799 x_Currency_Code OUT VARCHAR2,
800 x_Invoicing_Rule OUT VARCHAR2,
801 x_Accounting_Rule OUT VARCHAR2,
802 x_Billing_Frequency_Period OUT VARCHAR2,
803 x_Bill_On OUT NUMBER,
804 x_First_Bill_Date OUT DATE,
805 x_Next_Bill_Date OUT DATE,
806 x_Workflow_Process_Id OUT NUMBER,
807 x_Renewal_Rule OUT VARCHAR2,
808 x_Termination_Rule OUT VARCHAR2,
809 x_Contract_Amount OUT NUMBER,
810 x_discount OUT VARCHAR2,
811 x_Service_Id OUT NUMBER,
812 x_Service OUT VARCHAR2,
813 x_Service_Start_Date OUT DATE,
814 x_Service_End_Date OUT DATE,
815 x_Coverage_Id OUT NUMBER,
816 x_coverage OUT VARCHAR2,
817 p_rec_count IN OUT NUMBER,
818 x_return_status OUT VARCHAR2,
819 x_msg_count OUT NUMBER,
820 x_msg_data OUT VARCHAR2 )IS
821
822 l_api_name VARCHAR2(30) := G_PKG_NAME;
823 l_api_version NUMBER := G_API_VERSION;
824 l_contract_rec_type CONTRACTS_REC_TYPE;
825 BEGIN
826
827 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
828 G_PKG_NAME,
829 l_api_version,
830 p_api_version,
831 p_init_msg_list,
832 '_Pvt',
833 x_return_status);
834
835 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
838 RAISE FND_API.G_EXC_ERROR;
839 END IF;
840
841
842 IF (P_Customer_Product_Id IS NOT NULL) THEN
843 --DBMS_Output.Put_line('Cust. prod id is not null');
844 Get_List_Of_Contracts (
845 p_api_version ,
846 p_init_msg_list ,
847 p_commit ,
848 p_customer_product_id ,
849 p_business_process_id ,
850 p_charge_date_time ,
851 p_time_zone_id ,
852 p_exception_coverage_flag ,
853 l_contract_rec_type ,
854 x_Contract_Id ,
855 x_Contract_Number ,
856 x_Contract_Status ,
857 x_Contract_Type ,
858 x_Contract_Group ,
859 x_Contract_Duration ,
860 x_Contract_Period ,
861 x_Contract_Start_Date ,
862 x_Contract_End_Date ,
863 x_Contract_Agreement ,
864 x_Contract_Price_List ,
865 x_Currency_Code ,
866 x_Invoicing_Rule ,
867 x_Accounting_Rule ,
868 x_Billing_Frequency_Period ,
869 x_Bill_On ,
870 x_First_Bill_Date ,
871 x_Next_Bill_Date ,
872 x_Workflow_Process_Id ,
873 x_Renewal_Rule ,
874 x_Termination_Rule ,
875 x_Contract_Amount ,
876 x_discount ,
877 x_Service_Id ,
878 x_Service ,
879 x_Service_Start_Date ,
880 x_Service_End_Date ,
881 x_Coverage_Id ,
882 x_coverage ,
883 p_rec_count ,
884 x_return_status ,
885 x_msg_count ,
886 x_msg_data );
887
888 END IF;
889
890
891 IF (P_Site_Use_Id IS NOT NULL ) THEN
892 --DBMS_Output.Put_line('Site use id is not null');
893 Get_List_Of_Contracts (
894 p_api_version ,
895 p_init_msg_list ,
896 p_commit ,
897 p_system_id ,
898 p_site_use_id ,
899 p_Business_Process_Id ,
900 p_charge_date_time ,
901 p_time_zone_id ,
902 p_exception_coverage_flag ,
903 l_contract_rec_type ,
904 x_Contract_Id ,
905 x_Contract_Number ,
906 x_Contract_Status ,
907 x_Contract_Type ,
908 x_Contract_Group ,
909 x_Contract_Duration ,
910 x_Contract_Period ,
911 x_Contract_Start_Date ,
912 x_Contract_End_Date ,
913 x_Contract_Agreement ,
914 x_Contract_Price_List ,
915 x_Currency_Code ,
916 x_Invoicing_Rule ,
917 x_Accounting_Rule ,
918 x_Billing_Frequency_Period ,
919 x_Bill_On ,
920 x_First_Bill_Date ,
921 x_Next_Bill_Date ,
922 x_Workflow_Process_Id ,
923 x_Renewal_Rule ,
924 x_Termination_Rule ,
925 x_Contract_Amount ,
926 x_discount ,
927 x_Service_Id ,
928 x_Service ,
929 x_Service_Start_Date ,
930 x_Service_End_Date ,
931 x_Coverage_Id ,
932 x_coverage ,
933 p_rec_count ,
934 x_return_status ,
935 x_msg_count ,
936 x_msg_data );
937 END IF;
938
939 IF (P_System_ID IS NOT NULL ) THEN
940 --DBMS_Output.Put_line('System id is not null');
941 Get_List_Of_Contracts (
942 p_api_version ,
943 p_init_msg_list ,
944 p_commit ,
945 p_system_id ,
946 p_site_use_id ,
947 p_Business_Process_Id ,
948 p_charge_date_time ,
949 p_time_zone_id ,
950 p_exception_coverage_flag ,
951 l_contract_rec_type ,
952 x_Contract_Id ,
953 x_Contract_Number ,
954 x_Contract_Status ,
955 x_Contract_Type ,
956 x_Contract_Group ,
957 x_Contract_Duration ,
958 x_Contract_Period ,
959 x_Contract_Start_Date ,
960 x_Contract_End_Date ,
961 x_Contract_Agreement ,
962 x_Contract_Price_List ,
963 x_Currency_Code ,
964 x_Invoicing_Rule ,
965 x_Accounting_Rule ,
966 x_Billing_Frequency_Period ,
967 x_Bill_On ,
968 x_First_Bill_Date ,
969 x_Next_Bill_Date ,
970 x_Workflow_Process_Id ,
971 x_Renewal_Rule ,
972 x_Termination_Rule ,
973 x_Contract_Amount ,
974 x_discount ,
975 x_Service_Id ,
976 x_Service ,
977 x_Service_Start_Date ,
978 x_Service_End_Date ,
979 x_Coverage_Id ,
980 x_coverage ,
981 p_rec_count ,
982 x_return_status ,
983 x_msg_count ,
984 x_msg_data );
985 END IF;
986
987 IF (P_Inventory_Item_Id IS NOT NULL ) THEN
988 --DBMS_Output.Put_line('Item id is not null');
989 Get_List_Of_Contracts (
990 p_api_version ,
991 p_init_msg_list ,
992 p_commit ,
993 p_inventory_item_id ,
994 'ITEM',
995 p_Business_Process_Id ,
996 p_charge_date_time ,
997 p_time_zone_id ,
998 p_exception_coverage_flag ,
999 l_contract_rec_type ,
1000 x_Contract_Id ,
1001 x_Contract_Number ,
1002 x_Contract_Status ,
1003 x_Contract_Type ,
1004 x_Contract_Group ,
1005 x_Contract_Duration ,
1006 x_Contract_Period ,
1007 x_Contract_Start_Date ,
1008 x_Contract_End_Date ,
1009 x_Contract_Agreement ,
1010 x_Contract_Price_List ,
1011 x_Currency_Code ,
1012 x_Invoicing_Rule ,
1013 x_Accounting_Rule ,
1014 x_Billing_Frequency_Period ,
1015 x_Bill_On ,
1016 x_First_Bill_Date ,
1017 x_Next_Bill_Date ,
1018 x_Workflow_Process_Id ,
1019 x_Renewal_Rule ,
1020 x_Termination_Rule ,
1021 x_Contract_Amount ,
1022 x_discount ,
1023 x_Service_Id ,
1024 x_Service ,
1025 x_Service_Start_Date ,
1026 x_Service_End_Date ,
1027 x_Coverage_Id ,
1028 x_coverage ,
1029 p_rec_count ,
1030 x_return_status ,
1031 x_msg_count ,
1032 x_msg_data );
1033 END IF;
1034
1035 IF (P_Customer_Id IS NOT NULL ) THEN
1036 --DBMS_Output.Put_line('Cust. id is not null');
1037 Get_List_Of_Contracts (
1038 p_api_version ,
1039 p_init_msg_list ,
1040 p_commit ,
1041 p_customer_Id ,
1042 'CUSTOMER',
1043 p_Business_Process_Id ,
1044 p_charge_date_time ,
1045 p_time_zone_id ,
1046 p_exception_coverage_flag ,
1047 l_contract_rec_type ,
1048 x_Contract_Id ,
1049 x_Contract_Number ,
1050 x_Contract_Status ,
1051 x_Contract_Type ,
1052 x_Contract_Group ,
1053 x_Contract_Duration ,
1054 x_Contract_Period ,
1055 x_Contract_Start_Date ,
1056 x_Contract_End_Date ,
1057 x_Contract_Agreement ,
1058 x_Contract_Price_List ,
1059 x_Currency_Code ,
1060 x_Invoicing_Rule ,
1061 x_Accounting_Rule ,
1062 x_Billing_Frequency_Period ,
1063 x_Bill_On ,
1064 x_First_Bill_Date ,
1065 x_Next_Bill_Date ,
1066 x_Workflow_Process_Id ,
1067 x_Renewal_Rule ,
1068 x_Termination_Rule ,
1069 x_Contract_Amount ,
1070 x_discount ,
1071 x_Service_Id ,
1072 x_Service ,
1073 x_Service_Start_Date ,
1074 x_Service_End_Date ,
1075 x_Coverage_Id ,
1076 x_coverage ,
1077 p_rec_count ,
1078 x_return_status ,
1079 x_msg_count ,
1080 x_msg_data );
1081 END IF;
1082
1083 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1084
1085 EXCEPTION
1086 WHEN FND_API.G_EXC_ERROR THEN
1087 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1088 (
1089 l_api_name,
1090 G_PKG_NAME,
1091 'FND_API.G_RET_STS_ERROR',
1092 x_msg_count,
1093 x_msg_data,
1094 '_Pvt'
1095 );
1096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1097 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1098 (
1099 l_api_name,
1100 G_PKG_NAME,
1101 'FND_API.G_RET_STS_UNEXP_ERROR',
1102 x_msg_count,
1103 x_msg_data,
1104 '_Pvt'
1105 );
1106 WHEN OTHERS THEN
1107 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1108 (
1109 l_api_name,
1110 G_PKG_NAME,
1111 'OTHERS',
1112 x_msg_count,
1113 x_msg_data,
1114 '_Pvt',
1115 SQLERRM
1116 );
1117
1118 END Get_List_Of_Contracts;
1119
1120 Procedure migrate_to_table(
1121 p_api_version IN NUMBER,
1122 p_init_msg_list IN VARCHAR2 ,
1123 p_commit IN VARCHAR2 ,
1124 p_contract_rec_type IN CONTRACTS_REC_TYPE,
1125 p_rec_index IN NUMBER,
1126 x_return_status OUT VARCHAR2,
1127 x_msg_count OUT NUMBER,
1128 x_msg_data OUT VARCHAR2 ) IS
1129
1130 l_api_name VARCHAR2(30) := G_PKG_NAME;
1131 l_api_version NUMBER := G_API_VERSION;
1132 Begin
1133
1134 --DBMS_Output.Put_Line('In Migrate to table');
1135
1136 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
1137 G_PKG_NAME,
1138 l_api_version,
1139 p_api_version,
1140 p_init_msg_list,
1141 '_Pvt',
1142 x_return_status);
1143 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1145 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1146 RAISE FND_API.G_EXC_ERROR;
1147 END IF;
1148
1149 --DBMS_Output.Put_Line('After start');
1150 G_Contracts_Tab_Type(p_rec_index).V_Contract_Id
1151 := p_contract_rec_type.V_Contract_Id;
1152 --DBMS_OUTPUT.Put_Line('Global tab contract id='||
1153 -- to_char(G_Contracts_Tab_Type(p_rec_index).V_CONTRACT_ID));
1154 G_Contracts_Tab_Type(p_rec_index).V_Contract_Number
1155 := p_contract_rec_type.V_Contract_Number;
1156 G_Contracts_Tab_Type(p_rec_index).V_Contract_Status
1157 := p_contract_rec_type.V_Contract_Status;
1158 G_Contracts_Tab_Type(p_rec_index).V_contract_Duration
1159 := p_contract_rec_type.V_contract_duration;
1160 G_Contracts_Tab_Type(p_rec_index).V_Contract_Start_Date
1161 := p_contract_rec_type.V_Contract_Start_Date;
1162 G_Contracts_Tab_Type(p_rec_index).V_Contract_End_Date
1163 := p_contract_rec_type.V_contract_end_Date;
1164 G_Contracts_Tab_Type(p_rec_index).V_Currency_Code
1165 := p_contract_rec_type.V_Currency_Code;
1166 G_Contracts_Tab_Type(p_rec_index).V_Bill_On
1167 := p_contract_rec_type.V_Bill_ON;
1168 G_Contracts_Tab_Type(p_rec_index).V_First_Bill_Date
1169 := p_contract_rec_type.V_First_Bill_Date;
1170 G_Contracts_Tab_Type(p_rec_index).V_Next_Bill_Date
1171 := p_contract_rec_type.V_Next_Bill_Date;
1172 G_Contracts_Tab_Type(p_rec_index).V_Workflow_Process_Id
1173 := p_contract_rec_type.V_Workflow_Process_Id;
1174 G_Contracts_Tab_Type(p_rec_index).V_Renewal_Rule
1175 := p_contract_rec_type.V_Renewal_Rule;
1176 G_Contracts_Tab_Type(p_rec_index).V_Termination_Rule
1177 := p_contract_rec_type.V_Termination_Rule;
1178 G_Contracts_Tab_Type(p_rec_index).V_Contract_Amount
1179 := p_contract_rec_type.V_Contract_Amount;
1180 G_Contracts_Tab_Type(p_rec_index).V_Service_Id
1181 := p_contract_rec_type.V_Service_Id;
1182 G_Contracts_Tab_Type(p_rec_index).V_Service
1183 := p_contract_rec_type.V_Service;
1184 G_Contracts_Tab_Type(p_rec_index).V_Service_Start_Date
1185 := p_contract_rec_type.V_Service_Start_Date;
1186 G_Contracts_Tab_Type(p_rec_index).V_Service_End_Date
1187 := p_contract_rec_type.V_Service_End_Date;
1188 G_Contracts_Tab_Type(p_rec_index).V_Coverage_Id
1189 := p_contract_rec_type.V_Coverage_Id;
1190 G_Contracts_Tab_Type(p_rec_index).V_Coverage
1191 := p_contract_rec_type.V_Coverage;
1192
1193 --DBMS_Output.Put_Line('Before end activity');
1194
1195 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1196
1197 --DBMS_Output.Put_Line('After end activity');
1198
1199 EXCEPTION
1200 WHEN FND_API.G_EXC_ERROR THEN
1201 --DBMS_Output.Put_Line('Expected Error');
1202 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1203 (
1204 l_api_name,
1205 G_PKG_NAME,
1206 'FND_API.G_RET_STS_ERROR',
1207 x_msg_count,
1208 x_msg_data,
1209 '_Pvt'
1210 );
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212 --DBMS_Output.Put_Line('UnExpected Error');
1213 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1214 (
1215 l_api_name,
1216 G_PKG_NAME,
1217 'FND_API.G_RET_STS_UNEXP_ERROR',
1218 x_msg_count,
1219 x_msg_data,
1220 '_Pvt'
1221 );
1222 WHEN OTHERS THEN
1223 --DBMS_Output.Put_Line('Others');
1224 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1225 (
1226 l_api_name,
1227 G_PKG_NAME,
1228 'OTHERS',
1229 x_msg_count,
1230 x_msg_data,
1231 '_Pvt',
1232 SQLERRM
1233 );
1234
1235 END migrate_to_table;
1236
1237 Procedure count_and_get(
1238 p_api_version IN NUMBER,
1239 p_init_msg_list IN VARCHAR2 ,
1240 p_commit IN VARCHAR2 ,
1241 p_rec_index IN NUMBER,
1242 x_Contract_Id OUT NUMBER,
1243 x_Contract_Number OUT NUMBER,
1244 x_Contract_Status OUT VARCHAR2,
1245 x_Contract_Type OUT VARCHAR2,
1246 x_Contract_Group OUT VARCHAR2,
1247 x_Contract_Duration OUT NUMBER,
1248 x_Contract_Period OUT VARCHAR2,
1249 x_Contract_Start_Date OUT DATE,
1250 x_Contract_End_Date OUT DATE,
1251 x_Contract_Agreement OUT VARCHAR2,
1252 x_Contract_Price_List OUT VARCHAR2,
1253 x_Currency_Code OUT VARCHAR2,
1254 x_Invoicing_Rule OUT VARCHAR2,
1255 x_Accounting_Rule OUT VARCHAR2,
1256 x_Billing_Frequency_Period OUT VARCHAR2,
1257 x_Bill_On OUT NUMBER ,
1258 x_First_Bill_Date OUT DATE,
1259 x_Next_Bill_Date OUT DATE,
1260 x_Workflow_Process_Id OUT NUMBER,
1261 x_Renewal_Rule OUT VARCHAR2,
1262 x_Termination_Rule OUT VARCHAR2,
1263 x_Contract_Amount OUT NUMBER,
1264 x_discount OUT VARCHAR2,
1265 x_Service_Id OUT NUMBER,
1266 x_Service OUT VARCHAR2,
1267 x_Service_Start_Date OUT DATE,
1268 x_Service_End_Date OUT DATE,
1269 x_Coverage_Id OUT NUMBER,
1270 x_coverage OUT VARCHAR2,
1271 p_rec_count IN OUT NUMBER,
1272 x_return_status OUT VARCHAR2,
1273 x_msg_count OUT NUMBER,
1274 x_msg_data OUT VARCHAR2 ) IS
1275
1276 l_api_name VARCHAR2(30) := G_PKG_NAME;
1277 l_api_version NUMBER := G_API_VERSION;
1278 l_contract_rec_type CONTRACTS_REC_TYPE;
1279 Begin
1280
1281 --DBMS_Output.Put_Line('In count and get');
1282
1283 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
1284 G_PKG_NAME,
1285 l_api_version,
1286 p_api_version,
1287 p_init_msg_list,
1288 '_Pvt',
1289 x_return_status);
1290 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1291
1292 --DBMS_Output.Put_Line('Count and get unexpected error');
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1295
1296 --DBMS_Output.Put_Line('Count and get error');
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299
1300 --DBMS_Output.Put_Line('After start');
1301
1302 x_contract_id := G_Contracts_Tab_Type(p_rec_index).V_Contract_Id;
1303
1304 --DBMS_Output.Put_Line('Contract Id='|| to_char(x_contract_id));
1305
1306 l_contract_rec_type.v_contract_id := G_Contracts_Tab_Type(p_rec_index).V_Contract_Id;
1307
1308 x_contract_number :=
1309 G_Contracts_Tab_Type(p_rec_index).V_Contract_Number ;
1310 x_contract_status :=
1311 G_Contracts_Tab_Type(p_rec_index).V_Contract_Status;
1312 x_contract_duration :=
1313 G_Contracts_Tab_Type(p_rec_index).V_contract_Duration ;
1314 x_contract_start_date :=
1315 G_Contracts_Tab_Type(p_rec_index).V_Contract_Start_Date;
1316 x_contract_end_date :=
1317 G_Contracts_Tab_Type(p_rec_index).V_Contract_End_Date;
1318 x_currency_code :=
1319 G_Contracts_Tab_Type(p_rec_index).V_Currency_Code;
1320 x_bill_on := G_Contracts_Tab_Type(p_rec_index).V_Bill_On;
1321 x_first_bill_date :=
1322 G_Contracts_Tab_Type(p_rec_index).V_First_Bill_Date;
1323 x_next_bill_date :=
1324 G_Contracts_Tab_Type(p_rec_index).V_Next_Bill_Date ;
1325 x_workflow_process_id :=
1326 G_Contracts_Tab_Type(p_rec_index).V_Workflow_Process_Id;
1327 x_renewal_rule :=
1328 G_Contracts_Tab_Type(p_rec_index).V_Renewal_Rule ;
1329 x_termination_rule :=
1330 G_Contracts_Tab_Type(p_rec_index).V_Termination_Rule;
1331 x_contract_amount :=
1332 G_Contracts_Tab_Type(p_rec_index).V_Contract_Amount;
1333 x_service_id :=
1334 G_Contracts_Tab_Type(p_rec_index).V_Service_Id;
1335 x_service := G_Contracts_Tab_Type(p_rec_index).V_Service ;
1336 x_service_start_date :=
1337 G_Contracts_Tab_Type(p_rec_index).V_Service_Start_Date;
1338 x_service_end_date :=
1339 G_Contracts_Tab_Type(p_rec_index).V_Service_End_Date;
1340 x_coverage_id :=
1341 G_Contracts_Tab_Type(p_rec_index).V_Coverage_Id;
1342 x_coverage :=
1343 G_Contracts_Tab_Type(p_rec_index).V_Coverage;
1344
1345 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1346
1347 EXCEPTION
1348 WHEN FND_API.G_EXC_ERROR THEN
1349 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1350 (
1351 l_api_name,
1352 G_PKG_NAME,
1353 'FND_API.G_RET_STS_ERROR',
1354 x_msg_count,
1355 x_msg_data,
1356 '_Pvt'
1357 );
1358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1359 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1360 (
1361 l_api_name,
1362 G_PKG_NAME,
1363 'FND_API.G_RET_STS_UNEXP_ERROR',
1364 x_msg_count,
1365 x_msg_data,
1366 '_Pvt'
1367 );
1368 WHEN OTHERS THEN
1369 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1370 (
1371 l_api_name,
1372 G_PKG_NAME,
1373 'OTHERS',
1374 x_msg_count,
1375 x_msg_data,
1376 '_Pvt',
1377 SQLERRM
1378 );
1379
1380 END count_and_get;
1381
1382 Procedure Migrate_to_Out_Variables(
1383 p_api_version IN NUMBER,
1384 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1385 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1386 x_contract_rec_Type IN CONTRACTS_REC_TYPE,
1387 x_Contract_Id OUT NUMBER,
1388 x_Contract_Number OUT NUMBER,
1389 x_Contract_Status OUT VARCHAR2,
1390 x_Contract_Type OUT VARCHAR2,
1391 x_Contract_Group OUT VARCHAR2,
1392 x_Contract_Duration OUT NUMBER,
1393 x_Contract_Period OUT VARCHAR2,
1394 x_Contract_Start_Date OUT DATE,
1395 x_Contract_End_Date OUT DATE,
1396 x_Contract_Agreement OUT VARCHAR2,
1397 x_Contract_Price_List OUT VARCHAR2,
1398 x_Currency_Code OUT VARCHAR2,
1399 x_Invoicing_Rule OUT VARCHAR2,
1400 x_Accounting_Rule OUT VARCHAR2,
1401 x_Billing_Frequency_Period OUT VARCHAR2,
1402 x_Bill_On OUT NUMBER,
1403 x_First_Bill_Date OUT DATE,
1404 x_Next_Bill_Date OUT DATE,
1405 x_Workflow_Process_Id OUT NUMBER,
1406 x_Renewal_Rule OUT VARCHAR2,
1407 x_Termination_Rule OUT VARCHAR2,
1408 x_Contract_Amount OUT NUMBER,
1409 x_discount OUT VARCHAR2,
1410 x_Service_Id OUT NUMBER,
1411 x_Service OUT VARCHAR2,
1412 x_Service_Start_Date OUT DATE,
1413 x_Service_End_Date OUT DATE,
1414 x_Coverage_Id OUT NUMBER,
1415 x_coverage OUT VARCHAR2,
1416 x_return_status OUT VARCHAR2,
1417 x_msg_count OUT NUMBER,
1418 x_msg_data OUT VARCHAR2 ) IS
1419
1420 l_api_name VARCHAR2(30) := G_PKG_NAME;
1421 l_api_version NUMBER := G_API_VERSION;
1422 BEGIN
1423 x_return_status := TAPI_DEV_KIT.START_ACTIVITY(
1424 l_api_name,
1425 G_PKG_NAME,
1426 l_api_version,
1427 p_api_version,
1428 p_init_msg_list,
1429 '_Pvt',
1430 x_return_status);
1431 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1434 RAISE FND_API.G_EXC_ERROR;
1435 END IF;
1436
1437 x_Contract_Id := x_contract_rec_type.V_Contract_Id;
1438 x_Contract_Number := x_contract_rec_type.V_Contract_Number;
1439 x_Contract_Status := x_contract_rec_type.V_COntract_Status;
1440 x_Contract_Duration := x_contract_rec_type.v_Contract_Duration;
1441 x_Contract_Start_Date := x_contract_rec_type.V_Contract_Start_Date;
1442 x_Contract_End_Date := x_contract_rec_type.V_Contract_End_Date;
1443 x_Currency_Code := x_contract_rec_type.V_Currency_code;
1444 x_Bill_On := x_contract_rec_type.V_Bill_On;
1445 x_First_Bill_Date := x_contract_rec_type.V_First_Bill_Date;
1446 x_Next_Bill_Date := x_contract_rec_type.V_Next_Bill_Date;
1447 x_Workflow_Process_Id := x_contract_rec_type.V_Workflow_Process_Id;
1448 x_Renewal_Rule := x_contract_rec_type.V_Renewal_Rule;
1449 x_Termination_Rule := x_contract_rec_type.V_Termination_Rule;
1450 x_Contract_Amount := x_contract_rec_type.V_Contract_Amount;
1451 x_Service_Id := x_contract_rec_type.V_Service_id;
1452 x_Service := x_contract_rec_type.V_Service;
1453 x_Service_Start_Date := x_contract_rec_type.V_Service_start_date;
1454 x_Service_End_Date := x_contract_rec_type.V_Service_End_Date;
1455 x_Coverage_Id := x_contract_rec_type.V_Coverage_Id;
1456 x_coverage := x_contract_rec_type.V_Coverage;
1457
1458 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1459
1460 EXCEPTION
1461 WHEN FND_API.G_EXC_ERROR THEN
1462 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1463 (
1464 l_api_name,
1465 G_PKG_NAME,
1466 'FND_API.G_RET_STS_ERROR',
1467 x_msg_count,
1468 x_msg_data,
1469 '_Pvt'
1470 );
1471 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1472 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1473 (
1474 l_api_name,
1475 G_PKG_NAME,
1476 'FND_API.G_RET_STS_UNEXP_ERROR',
1477 x_msg_count,
1478 x_msg_data,
1479 '_Pvt'
1480 );
1481 WHEN OTHERS THEN
1482 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1483 (
1484 l_api_name,
1485 G_PKG_NAME,
1486 'OTHERS',
1487 x_msg_count,
1488 x_msg_data,
1489 '_Pvt',
1490 SQLERRM
1491 );
1492
1493 End Migrate_to_Out_Variables;
1494
1495 END CS_GET_CONTRACTS_PUB;