[Home] [Help]
PACKAGE BODY: APPS.OKS_OMINT_PUB
Source
1 Package Body OKS_OMINT_PUB AS
2 /* $Header: OKSPOMIB.pls 120.25 2007/12/24 10:17:56 rriyer ship $ */
3
4
5
6 TYPE War_item_rec_type IS RECORD (War_item_id Number);
7
8 TYPE War_item_id_tbl_type IS TABLE OF War_item_rec_type INDEX BY BINARY_INTEGER;
9
10 l_war_item_Id NUMBER;
11
12
13
14 Procedure Get_Duration
15 (
16 P_Api_Version IN Number,
17 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
18 X_msg_Count OUT NOCOPY Number,
19 X_msg_Data OUT NOCOPY Varchar2,
20 X_Return_Status OUT NOCOPY Varchar2,
21 P_customer_id IN Number,
22 P_system_id IN Number,
23 P_Service_Duration IN Number,
24 P_service_period IN Varchar2,
25 P_coterm_checked_yn IN Varchar2 Default OKC_API.G_FALSE,
26 P_start_date IN Date,
27 P_end_date IN Date,
28 X_service_duration OUT NOCOPY Number,
29 X_service_period OUT NOCOPY Varchar2,
30 X_new_end_date OUT NOCOPY Date
31 )
32 Is
33 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
34 l_api_version CONSTANT NUMBER := 11.5;
35 l_row_count NUMBER;
36 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
37
38 Cursor l_Customer_Coterm_Csr is
39 Select Coterminate_Day_Month
40 From HZ_CUST_ACCOUNTS --OKX_CUSTOMER_ACCOUNTS_V
41 Where CUST_ACCOUNT_ID = p_customer_id;
42
43 Cursor l_System_Coterm_Csr is
44 Select Coterminate_Day_Month
45 From CS_SYSTEMS_ALL_B --OKX_SYSTEMS_V
46 Where SYSTEM_ID = P_system_id;
47
48 Cursor l_UOM_Csr Is
49 Select UOM_Code, Unit_Of_Measure
50 From MTL_UNITS_OF_MEASURE_TL --OKX_UNITS_OF_MEASURE_V
51 Where UOM_Code = p_Service_Period;
52
53
54 Cursor l_tce_csr(p_code varchar2, p_qty Number) Is
55 Select uom_code
56 from OKC_TIME_CODE_UNITS_V
57 Where tce_code = p_code
58 And quantity = p_qty;
59
60
61 l_sys_coterm Varchar2(6);
62 l_cus_coterm Varchar2(6);
63 l_min_duration Number;
64 l_min_period Varchar2(10);
65 l_coterm_day Varchar2(6);
66
67 l_time_duration Number;
68 l_time_unit Varchar2(20);
69 l_uom_rec l_UOM_Csr%ROWTYPE;
70 l_unit_of_measure Varchar2(20);
71 l_tce_rec l_tce_csr%rowtype;
72 BEGIN
73
74 x_return_status := OKC_API.G_RET_STS_SUCCESS;
75
76 -- Profile Check
77 l_min_duration := fnd_profile.value('OKS_MINIMUM_SVC_DURATION');
78 l_min_period := fnd_profile.value('OKS_MINIMUM_SVC_PERIOD');
79 l_min_duration := Round(l_min_duration,0);
80 If l_min_duration Is Null Or l_min_period Is Null Then
81 -- l_min_duration := 60;
82 -- l_min_period := 'DAY';
83
84 l_return_status := OKC_API.G_RET_STS_ERROR;
85 OKC_API.set_message(G_APP_NAME,'OKS_PROFILE_NOT_SET');
86 Raise G_EXCEPTION_HALT_VALIDATION;
87
88 End If;
89
90 -- Parameter Check
91
92 If p_start_date Is Null Then
93 l_return_status := OKC_API.G_RET_STS_ERROR;
94 OKC_API.set_message(G_APP_NAME,'OKS_START_DATE_REQUIRED');
95 Raise G_EXCEPTION_HALT_VALIDATION;
96 End If;
97
98 If p_end_date Is Null And (p_service_duration Is Null Or p_service_period Is Null) Then
99 l_return_status := OKC_API.G_RET_STS_ERROR;
100 OKC_API.set_message(G_APP_NAME,'OKS_END_DT_DUR_REQUIRED');
101 Raise G_EXCEPTION_HALT_VALIDATION;
102 End If;
103
104 If p_Service_Duration Is Not Null Then
105 Open l_UOM_Csr;
106 Fetch l_UOM_Csr Into l_UOM_Rec;
107 If l_UOM_Csr%NotFound Then
108 l_return_status := OKC_API.G_RET_STS_ERROR;
109 OKC_API.set_message(G_APP_NAME,'OKS_INVD_PERIOD');
110 Raise G_EXCEPTION_HALT_VALIDATION;
111 End if;
112 l_unit_of_measure := l_uom_rec.uom_code;
113 End If;
114
115 Open l_system_coterm_csr;
116 Fetch l_system_coterm_csr Into l_sys_coterm;
117 Close l_system_coterm_csr;
118
119 Open l_customer_coterm_csr;
120 Fetch l_customer_coterm_csr into l_cus_coterm;
121 Close l_customer_coterm_csr;
122
123 If l_sys_coterm Is Not NULL Then
124 l_coterm_day := l_sys_coterm;
125 Elsif l_cus_coterm Is Not NULL Then
126 l_coterm_day := l_cus_coterm;
127 End if;
128
129 If l_coterm_day is not null And Upper(p_coterm_checked_yn) = 'Y' Then
130 x_new_end_date := TO_DATE(l_coterm_day || to_char(p_start_date,'YYYY'),'MM/DD/YYYY HH24:MI:SS');
131 If Upper(l_min_period) = 'DAY' Then
132 Loop
133 If (x_new_end_date - p_start_Date) < l_min_duration Then
134 x_new_end_date := add_months(x_new_end_date,12);
135 Else
136 exit;
137 End if;
138 End Loop;
139 Elsif Upper(l_min_period) = 'MONTHS' then
140 Loop
141 if months_between(x_new_end_date,p_start_date) < l_min_duration then
142 x_new_end_date := add_months(x_new_end_date,12);
143 else
144 exit;
145 End if;
146 End Loop;
147 Elsif Upper(l_min_period) = 'YEAR' then
148 Loop
149 If months_between(x_new_end_date,p_start_date) < (l_min_duration * 12) then
150 x_new_end_date := add_months(x_new_end_date,12);
151 Else
152 exit;
153 End if;
154 End Loop;
155 End if;
156
157 OKC_TIME_UTIL_PUB.get_duration (
158 p_start_date => p_start_date,
159 p_end_date => x_new_end_date,
160 x_duration => x_service_duration,
161 x_timeunit => x_service_period,
162 x_return_status => l_return_status
163 );
164
165 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
166 Raise G_EXCEPTION_HALT_VALIDATION;
167 End if;
168 Else
169
170 If p_end_date is null then
171 x_new_end_date := OKC_TIME_UTIL_PUB.GET_ENDDATE
172 (
173 p_start_date => p_start_date,
174 p_timeunit => l_unit_of_measure,
175 p_duration => p_service_duration
176 );
177 Else
178 x_new_end_date := p_end_date;
179 End If;
180
181
182 If Upper(l_min_period) = 'MONTHS' then
183 if months_between(x_new_end_date,p_start_date) < l_min_duration then
184 Open l_tce_csr('MONTH',1);
185 Fetch l_tce_csr into l_tce_rec;
186 Close l_tce_csr;
187 x_new_end_date := OKC_TIME_UTIL_PUB.get_enddate
188 (
189 p_start_date => p_start_date,
190 p_duration => l_min_duration,
191 p_timeunit => l_tce_rec.uom_code
192 );
193 End if;
194 Elsif Upper(l_min_period) = 'DAY' then
195 If (x_new_end_date - p_start_Date) < l_min_duration then
196 Open l_tce_csr('DAY',1);
197 Fetch l_tce_csr into l_tce_rec;
198 Close l_tce_csr;
199 x_new_end_date := OKC_TIME_UTIL_PUB.get_enddate
200 (
201 p_start_date => p_start_date,
202 p_duration => l_min_duration,
203 p_timeunit => l_tce_rec.uom_code
204 );
205 End if;
206 Elsif Upper(l_min_period) = 'YEAR' then
207 If months_between(x_new_end_date,p_start_date) < (l_min_duration * 12) then
208 Open l_tce_csr('YEAR',1);
209 Fetch l_tce_csr into l_tce_rec;
210 Close l_tce_csr;
211 x_new_end_date := OKC_TIME_UTIL_PUB.get_enddate
212 (
213 p_start_date => p_start_date,
214 p_duration => l_min_duration,
215 p_timeunit => l_tce_rec.uom_code
216 );
217 End if;
218 End if;
219
220 OKC_TIME_UTIL_PUB.get_duration (
221 p_start_date => p_start_date,
222 p_end_date => x_new_end_date,
223 x_duration => x_service_duration,
224 x_timeunit => x_service_period,
225 x_return_status => l_return_status
226 );
227
228
229 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
230 Raise G_EXCEPTION_HALT_VALIDATION;
231 End If;
232
233 End if;
234
235 Exception
236
237 When G_EXCEPTION_HALT_VALIDATION Then
238 x_return_status := l_return_status;
239 When Others Then
240 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
241 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
242
243 END;
244
245
246 Procedure Is_service_available
247 (
248 P_Api_Version IN Number,
249 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
250 X_msg_Count OUT NOCOPY Number,
251 X_msg_Data OUT NOCOPY Varchar2,
252 X_Return_Status OUT NOCOPY Varchar2,
253 p_check_service_rec IN CHECK_SERVICE_REC_TYPE,
254 X_Available_YN OUT NOCOPY Varchar2,
255 --NPALEPU added on 29-sep-2005 for bug # 4608694
256 P_ORG_ID IN NUMBER Default NULL
257 --END NPALEPU
258 )
259 Is
260 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
261 l_api_version CONSTANT NUMBER := 11.5;
262 l_row_count NUMBER;
263 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
264
265 --Local
266 l_first Boolean := TRUE;
267 l_pstat Boolean;
268 l_cstat Boolean;
269
270 Cursor l_service_csr (Obj_type Varchar2) Is
271 Select Id
272 ,General_YN
273 ,Except_Object_Type
274 ,Start_Date_Active
275 ,End_Date_Active
276 From OKS_SERV_AVAILS_V
277 Where object1_id1 = p_check_service_rec.service_item_id
278 And Except_Object_type = Nvl(obj_type,Except_Object_type)
279 Order By Except_Object_type;
280
281 Cursor l_party_csr (l_custid Number) Is
282 Select Party_Id From OKX_CUSTOMER_ACCOUNTS_V
283 Where Id1 = l_custid;
284
285 Cursor l_cp_status_csr Is
286 Select distinct SERVICE_ORDER_ALLOWED_FLAG
287 From CS_CUSTOMER_PRODUCT_STATUSES
288 Where customer_product_status_id In
289 (
290 select customer_product_status_id From okx_customer_products_v
291 where id1 = p_check_service_rec.customer_product_id
292 );
293 /**** Added cursor to check servicable flag for product ***/
294
295 --npalepu 29-sep-2005 added for bug # 4608694
296 /* Cursor l_servicable_prod_csr (p_item_id Number) Is
297 Select serviceable_product_flag
298 FROM mtl_system_items_b
299 WHERE inventory_item_id = p_item_id
300 AND rownum < 2; */
301
302 Cursor l_servicable_prod_csr (p_item_id Number, l_organization_id Number) Is
303 Select serviceable_product_flag
304 From OKX_SYSTEM_ITEMS_V
305 Where id1 = p_item_id
306 And id2 = l_organization_id;
307 --end npalepu
308
309 /* start fixes 4605912
310 Cursor l_servicable_prod_csr (p_item_id Number, l_organization_id Number) Is
311 Select serviceable_product_flag
312 From OKX_SYSTEM_ITEMS_V
313 Where id1 = p_item_id
314 And id2 = l_organization_id;
315 end fixes 4605912 */
316
317
318
319 /**** End of cursor 04/30/2001 ***/
320
321 --- Added cursor to get inventory item id when customer product id is passed but
322 -- inventory item id is not passed by user for Bug # 2252026
323 Cursor l_product_item_id_csr(cust_prdId NUMBER) IS
324 select INVENTORY_ITEM_ID
325 from cs_customer_products_all
326 where CUSTOMER_PRODUCT_ID = cust_prdId;
327
328 /* This cursor added to check whether the item is model */
329
330 /*Cursor l_model_item_csr (p_inv_item_id NUMBER) IS
331 select 'Y'
332 from mtl_system_items
333 where inventory_item_id = p_inv_item_id
334 and ((bom_item_type = 1) OR
335 (bom_item_type = 4 AND
336 (pick_components_flag = 'Y' OR replenish_to_order_flag = 'Y')));
337 */
338
339 l_party_id Number;
340 l_service_rec l_service_csr%ROWTYPE;
341 End_Exc Exception;
342 l_servicable_flag_yn Varchar2(1);
343 l_cp_status Varchar2(3);
344 l_organization_id Number;
345 l_product_item_id NUMBER := p_check_service_rec.product_item_id;
346 l_model_flag Varchar2(1) := 'N';
347
348 --NPALEPU 29-sep-2005 for bug # 4608694
349 l_original_org_id NUMBER;
350 l_original_access_mode Varchar2(1);
351 --npalepu added on 23-dec-2005 for bug # 4897884
352 l_org_id NUMBER;
353 l_default_org_id hr_operating_units.organization_id%TYPE;
354 l_default_ou_name hr_operating_units.name%TYPE;
355 l_ou_count NUMBER;
356 --end 4897884
357 --END NPALEPU
358
359 Procedure Product (Pid IN Number, Stat OUT NOCOPY Boolean)
360 Is
361 Cursor l_product_csr (p_mast_id Number, p_prod_id Number) Is
362 Select Object1_Id1,
363 Start_Date_Active,
364 End_Date_Active
365 From OKS_SERV_AVAIL_EXCEPTS_V
366 Where SAV_Id = p_mast_id And
367 Object1_Id1 = p_prod_id;
368
369
370 l_product_rec l_product_csr%ROWTYPE;
371
372
373 BEGIN
374
375 Open l_service_csr ('P');
376 Fetch l_service_csr Into l_service_rec;
377 If l_service_csr%NOTFOUND Then
378 -- Close l_service_csr;
379 Stat := TRUE;
380 Raise End_Exc;
381 Else
382 If l_service_rec.general_yn = 'Y' Then
383 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
384 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
385 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
386 Then
387 For l_product_rec In l_product_csr(l_service_rec.Id, Pid)
388 Loop
389 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
390 Between TRUNC(NVL(l_product_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
391 And TRUNC(NVL(l_product_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
392 Then
393
394 Stat := FALSE;
395 Raise End_Exc;
396 End If;
397 End Loop;
398 Stat := TRUE;
399 Raise End_Exc;
400 Else
401 Stat := FALSE;
402 Raise End_Exc;
403 End If; -- If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
404 Else -- If l_service_rec.general_yn = 'Y' Then
405 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
406 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
407 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
408 Then
409 For l_product_rec In l_product_csr(l_service_rec.Id, Pid)
410 Loop
411 If TRUNC(NVL(p_check_service_rec.request_date,sysdate))
412 Between TRUNC(NVL(l_product_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
413 And TRUNC(NVL(l_product_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
414 Then
415 Stat := TRUE;
416 Raise End_Exc;
417 End If;
418 End Loop;
419 Stat := FALSE;
420 Raise End_Exc;
421 Else
422 Stat := TRUE;
423 Raise End_Exc;
424 End If;
425
426 End If; -- If l_service_rec.general_yn = 'Y' Then
427
428 End If; -- If l_service_csr%NOTFOUND Then
429 Close l_service_csr;
430
431 Exception
432
433 When End_Exc Then
434 Close l_service_csr;
435 Null;
436 When Others Then
437 Stat := False;
438
439 END;
440
441
442 Procedure Customer (p_Cid Number, Stat OUT NOCOPY Boolean)
443 Is
444 Cursor l_customer_csr (p_mast_id Number, p_cust_id Number) Is
445 Select Object1_Id1,
446 Start_Date_Active,
447 End_Date_Active
448 From OKS_SERV_AVAIL_EXCEPTS_V
449 Where SAV_Id = p_mast_id
450 And Object1_Id1 = p_cust_id;
451
452 BEGIN
453 Open l_service_csr ('C');
454 Fetch l_service_csr Into l_service_rec;
455 If l_service_csr%NOTFOUND Then
456 -- Close l_service_csr;
457 Stat := TRUE;
458 Raise End_Exc;
459 Else
460 If l_service_rec.general_yn = 'Y' Then
461 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate) )
462 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
463 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
464 Then
465 For l_customer_rec In l_customer_csr(l_service_rec.Id, p_Cid)
466 Loop
467 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
468 Between TRUNC(NVL(l_customer_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
469 And TRUNC(NVL(l_customer_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
470 Then
471 Stat := FALSE;
472 Raise End_Exc;
473 End If;
474 End Loop;
475 Stat := TRUE;
476 Raise End_Exc;
477 Else
478 Stat := FALSE;
479 Raise End_Exc;
480 End If;
481 Else
482 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
483 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
484 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
485 Then
486 For l_customer_rec In l_customer_csr(l_service_rec.Id, p_cid)
487 Loop
488 If TRUNC(NVL(p_check_service_rec.request_date ,sysdate))
489 Between TRUNC(NVL(l_customer_rec.Start_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
490 And TRUNC(NVL(l_customer_rec.End_Date_Active, NVL(p_check_service_rec.request_date ,sysdate)))
491 Then
492 Stat := TRUE;
493 Raise End_Exc;
494 End If;
495 End Loop;
496 Stat := FALSE;
497 Raise End_Exc;
498 Else
499 Stat := TRUE;
500 Raise End_Exc;
501 End If;
502
503 End If;
504 End If;
505 Close l_service_csr;
506
507
508 Exception
509
510 When End_Exc Then
511 Close l_service_csr;
512 Null;
513 When Others Then
514 Stat := False;
515
516 END;
517
518
519 BEGIN
520 If (l_product_item_id is null
521 AND p_check_service_rec.customer_product_id is not null) Then
522 Open l_product_item_id_csr(p_check_service_rec.customer_product_id);
523 Fetch l_product_item_id_csr into l_product_item_id;
524 Close l_product_item_id_csr;
525 End If;
526 /* start fixes 4605912
527 --set org_id if it is null
528 if okc_context.get_okc_org_id IS NULL
529 then
530 okc_context.set_okc_org_context;
531 end if;
532
533 l_organization_id := okc_context.get_okc_organization_id;
534 end fixes 4605912*/
535
536 --NPALEPU 21-sep-2005 for bug # 4608694
537
538 --capturing the original context
539 l_original_org_id := mo_global.get_current_org_id;
540 l_original_access_mode := mo_global.get_access_mode();
541
542 IF p_org_id IS NOT NULL THEN
543 --npalepu added on 23-dec-2005 for bug # 4897884
544 /* okc_context.set_okc_org_context(p_org_id =>P_ORG_ID); */
545 l_org_id := p_org_id;
546 --end 4897884
547 ELSIF l_original_org_id IS NOT NULL THEN
548 --npalepu added on 23-dec-2005 for bug # 4897884
549 /* okc_context.set_okc_org_context(p_org_id =>l_original_org_id); */
550 l_org_id := l_original_org_id;
551 --end 4897884
552 ELSE
553 --npalepu added on 23-dec-2005 for bug # 4897884
554 /* okc_context.set_okc_org_context; */
555 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
556 l_org_id := l_default_org_id;
557 --end 4897884
558 END IF;
559 --END NPALEPU
560
561 --npalepu added on 23-dec-2005 for bug # 4897884
562 IF l_org_id IS NOT NULL AND l_org_id <> -99 THEN
563 l_organization_id := OE_PROFILE.VALUE('OE_ORGANIZATION_ID',l_org_id);
564 END IF;
565 --end 4897884
566
567 If p_check_service_rec.Service_Item_id Is Null
568 Or (p_check_service_rec.Customer_id Is Null and l_product_item_id Is Null) Then
569 l_return_status := OKC_API.G_RET_STS_ERROR;
570 x_available_yn := 'N';
571 OKC_API.set_message(G_APP_NAME, 'OKS_MISSING_REQUIRED_PARAMETERS');
572 Raise G_EXCEPTION_HALT_VALIDATION;
573 End If;
574 /****** Added to check whether Item is servicable or not ****/
575
576 If l_product_item_id Is Not Null Then
577
578 /* Open l_model_item_csr (l_product_item_id);
579 Fetch l_model_item_csr Into l_model_flag;
580 Close l_model_item_csr;
581
582 If l_model_flag = 'Y' then
583 l_return_status := OKC_API.G_RET_STS_SUCCESS;
584 x_available_yn := 'Y';
585 Raise G_EXCEPTION_HALT_VALIDATION;
586 End If;
587 */
588 /* start fixes 4605912
589 Open l_servicable_prod_csr(l_product_item_id,l_organization_id);
590 end fixes 4605912*/
591 --NPALEPU 29-sep-2005 for bug # 4608694
592 /* Open l_servicable_prod_csr(l_product_item_id); */
593 Open l_servicable_prod_csr(l_product_item_id,l_organization_id);
594 --end npalepu
595 Fetch l_servicable_prod_csr Into l_servicable_flag_yn;
596 Close l_servicable_prod_csr;
597
598 IF NVL(l_servicable_flag_yn,'N') = 'N' THEN
599 l_return_status := OKC_API.G_RET_STS_ERROR;
600 x_available_yn := 'N';
601 OKC_API.set_message(G_APP_NAME, 'OKS_PRODUCT_NOT_SERVICABLE');
602 Raise G_EXCEPTION_HALT_VALIDATION;
603 END IF;
604 End If;
605
606 /*** End of check ***/
607
608 Open l_service_csr (Null);
609 Fetch l_service_csr Into l_service_rec;
610
611 If l_service_csr%NOTFOUND Then
612
613 If p_check_service_rec.customer_product_id Is Not Null Then
614 l_cp_status := Null;
615
616 Open l_cp_status_csr;
617 Fetch l_cp_status_csr Into l_cp_status;
618 Close l_cp_status_csr;
619
620 If Nvl(l_cp_status,'Y') = 'N' Then
621 x_available_yn := 'N';
622 End If;
623 End If;
624
625 x_available_yn := 'Y';
626 Close l_service_csr;
627 Raise G_EXCEPTION_HALT_VALIDATION;
628 End If;
629
630 Close l_service_csr;
631
632 l_cstat := TRUE;
633 l_pstat := TRUE;
634
635 If p_check_service_rec.customer_id Is Not Null Then
636
637 l_party_id := Null;
638 Open l_party_csr (p_check_service_rec.customer_id);
639 Fetch l_party_csr Into l_party_id;
640 Close l_party_csr;
641
642 If l_party_id IS Null Then
643 l_return_status := 'E';
644 x_available_yn := 'N';
645 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',to_char(p_check_service_rec.customer_id));
646 Raise G_EXCEPTION_HALT_VALIDATION;
647 End If;
648
649 Customer(l_party_id, l_cstat);
650
651 End If;
652
653 If l_cstat Then
654 If l_product_item_id Is Not Null Then
655 Product (l_product_item_id, l_pstat);
656 End If;
657 End If;
658
659 If l_cstat And l_pstat Then
660 x_available_yn := 'Y';
661 Else
662 x_available_yn := 'N';
663 End If;
664
665 If p_check_service_rec.customer_product_id Is Not Null Then
666
667 l_cp_status := Null;
668
669 Open l_cp_status_csr;
670 Fetch l_cp_status_csr Into l_cp_status;
671 Close l_cp_status_csr;
672
673 If Nvl(l_cp_status,'Y') = 'N' Then
674 x_available_yn := 'N';
675 End If;
676
677 End If;
678
679 x_return_status := l_return_status;
680
681 --npalepu 23-dec-2005 removed the code for bug # 4897884
682 /* --NPALEPU 29-sep-2005 for bug # 4608694
683 --Resetting to original context
684 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
685 --END NPALEPU */
686 --end 4897884
687
688 Exception
689
690 When G_EXCEPTION_HALT_VALIDATION Then
691
692 x_return_status := l_return_status;
693 Null;
694 --npalepu 23-dec-2005 removed the code for bug # 4897884
695 /* --NPALEPU 29-sep-2005 for bug # 4608694
696 --Resetting to original context
697 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
698 --END NPALEPU */
699 --end 4897884
700 When Others Then
701 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
702 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
703 --npalepu 23-dec-2005 removed the code for bug # 4897884
704 /* --NPALEPU 29-sep-2005 for bug # 4608694
705 --Resetting th eoriginal context
706 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
707 --END NPALEPU */
708 --end 4897884
709
710 END;
711
712
713 Procedure Available_Services
714 (
715 P_Api_Version IN Number,
716 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
717 X_msg_Count OUT NOCOPY Number,
718 X_msg_Data OUT NOCOPY Varchar2,
719 X_Return_Status OUT NOCOPY Varchar2,
720 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
721 X_Orderable_Service_tbl OUT NOCOPY order_service_tbl_type,
722 --NPALEPU added on 21-sep-2005 for bug # 4608694
723 P_ORG_ID IN NUMBER Default NULL
724 --END NPALEPU
725 )
726 Is
727
728 -- Added two more condition for bug # 2721044
729 --- Could not replace the view because we need the TL table for order by
730 --npalepu modified on 23-dec-2005 for bug # 4897884
731 /* Cursor l_srv_csr Is */
732 Cursor l_srv_csr(v_organization_id IN NUMBER) Is
733 --end 4897884
734 Select INVENTORY_ITEM_ID id1
735 From MTL_SYSTEM_ITEMS_B_KFV
736 Where VENDOR_WARRANTY_FLAG = 'N'
737 And SERVICE_ITEM_FLAG = 'Y'
738 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
739 AND DECODE(ENABLED_FLAG,'Y','A','I') = 'A'
740 --npalepu modified on 23-dec-2005 for bug # 4897884
741 /* And ORGANIZATION_ID = okc_context.get_okc_organization_id */
742 And ORGANIZATION_ID = v_organization_id
743 --end 4897884
744 order by CONCATENATED_SEGMENTS;
745
746
747
748 Cursor l_party_csr (l_custid Number) Is
749 Select Party_Id From OKX_CUSTOMER_ACCOUNTS_V
750 Where Id1 = l_custid;
751
752
753 l_party_id Number;
754
755
756 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
757 l_api_version CONSTANT NUMBER := 11.5;
758 l_row_count NUMBER;
759 l_msg_data VARCHAR2(2000);
760 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
761 l_ptr Binary_Integer := 0 ;
762 l_avail Varchar2(1);
763 l_isrec Check_service_rec_type;
764 l_Srv_tbl order_service_tbl_type;
765 l_found BOOLEAN;
766
767 --NPALEPU 21-sep-2005 for bug # 4608694
768 l_original_org_id NUMBER;
769 l_original_access_mode Varchar2(1);
770 --npalepu added on 23-dec-2005 for bug # 4897884
771 l_org_id NUMBER;
772 l_default_org_id hr_operating_units.organization_id%TYPE;
773 l_default_ou_name hr_operating_units.name%TYPE;
774 l_ou_count NUMBER;
775 l_organization_id NUMBER;
776 --end 4897884
777 --END NPALEPU
778
779 BEGIN
780
781 --NPALEPU 21-sep-2005 for bug # 4608694
782
783 /* --set org_id if it is null
784 if okc_context.get_okc_org_id IS NULL then
785 okc_context.set_okc_org_context;
786 end if; */
787
788 --capturing the original context
789 l_original_org_id := mo_global.get_current_org_id;
790 l_original_access_mode := mo_global.get_access_mode();
791
792 IF p_org_id IS NOT NULL THEN
793 --npalepu added on 23-dec-2005 for bug # 4897884
794 /* okc_context.set_okc_org_context(p_org_id =>P_ORG_ID); */
795 l_org_id := p_org_id;
796 --end 4897884
797 ELSIF l_original_org_id IS NOT NULL THEN
798 --npalepu added on 23-dec-2005 for bug # 4897884
799 /* okc_context.set_okc_org_context(p_org_id =>l_original_org_id); */
800 l_org_id := l_original_org_id;
801 --end 4897884
802 ELSE
803 --npalepu added on 23-dec-2005 for bug # 4897884
804 /* okc_context.set_okc_org_context; */
805 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
806 l_org_id := l_default_org_id;
807 --end 4897884
808 END IF;
809 --END NPALEPU
810
811 --npalepu added on 23-dec-2005 for bug # 4897884
812 IF l_org_id IS NOT NULL AND l_org_id <> -99 THEN
813 l_organization_id := OE_PROFILE.VALUE('OE_ORGANIZATION_ID',l_org_id);
814 END IF;
815 --end 4897884
816
817 l_party_id := Null;
818
819 If p_avail_service_rec.customer_id Is Not Null Then
820
821 Open l_party_csr (p_avail_service_rec.customer_id);
822 Fetch l_party_csr Into l_party_id;
823 Close l_party_csr;
824
825 If l_party_id IS Null Then
826 l_return_status := 'E';
827 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',to_char(p_avail_service_rec.customer_id));
828 Raise G_EXCEPTION_HALT_VALIDATION;
829 End If;
830
831 End If;
832
833 --npalepu modified on 23-dec-2005 for bug # 4897884
834 /* For l_srv_rec In l_srv_csr */
835 For l_srv_rec In l_srv_csr(l_organization_id)
836 --end 4897884
837 Loop
838
839 l_avail := 'N';
840
841 l_isrec.service_item_id := l_srv_rec.id1;
842 l_isrec.product_item_id := p_avail_service_rec.product_item_id;
843 l_isrec.customer_id := p_avail_service_rec.customer_id;
844 l_isrec.request_date := p_avail_service_rec.request_date;
845
846
847 --npalepu modified on 20-nov-2005
848 /* Is_Service_Available
849 (
850 1.0,
851 OKC_API.G_FALSE,
852 l_row_count,
853 l_msg_Data,
854 l_Return_Status,
855 l_isrec,
856 l_Avail
857 ); */
858 Is_Service_Available
859 (
860 P_Api_Version => 1.0,
861 P_init_msg_list => OKC_API.G_FALSE,
862 X_msg_Count => l_row_count,
863 X_msg_Data => l_msg_Data,
864 X_Return_Status => l_Return_Status,
865 p_check_service_rec => l_isrec,
866 X_Available_YN => l_Avail
867 );
868 --end npalepu
869
870 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
871 Raise G_EXCEPTION_HALT_VALIDATION;
872 End If;
873
874
875 If l_Avail = 'Y' Then
876 l_ptr := l_ptr + 1;
877 X_Orderable_Service_tbl(l_ptr).Service_Item_id := l_srv_rec.id1;
878 End If;
879
880 End Loop;
881
882 x_return_status := OKC_API.G_RET_STS_SUCCESS;
883
884 --npalepu removed the following code for bug # 4897884
885 /* --NPALEPU 21-sep-2005 for bug # 4608694
886 --Resetting to original context
887 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
888 --END NPALEPU */
889 --end 4897884
890
891
892 Exception
893 When G_EXCEPTION_HALT_VALIDATION Then
894 x_return_status := l_return_status;
895 Null;
896 --npalepu removed the following code for bug # 4897884
897 /* --NPALEPU 21-sep-2005 for bug # 4608694
898 --Resetting to original context
899 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
900 --END NPALEPU */
901 --end 4897884
902 When Others Then
903 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
904 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
905 --npalepu removed the following code for bug # 4897884
906 /* --NPALEPU 21-sep-2005 for bug # 4608694
907 --Resetting to original context
908 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
909 --END NPALEPU */
910 --end 4897884
911
912 END;
913
914 Procedure OKS_Available_Services
915 (
916 P_Api_Version IN Number,
917 P_init_msg_list IN Varchar2 Default OKC_API.G_FALSE,
918 X_msg_Count OUT NOCOPY Number,
919 X_msg_Data OUT NOCOPY Varchar2,
920 X_Return_Status OUT NOCOPY Varchar2,
921 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
922 --ADDED FOR OKS REQ
923 X_Orderable_Service_tbl OUT NOCOPY OKS_order_service_tbl_type,
924 --NPALEPU added on 21-sep-2005 for bug # 4608694
925 P_ORG_ID IN NUMBER Default NULL
926 --END NPALEPU
927 )
928 Is
929
930
931 --npalepu modified on 23-dec-2005 for bug # 4897884
932 /* Cursor l_srv_csr Is */
933 Cursor l_srv_csr(v_organization_id IN NUMBER) Is
934 --end 4897884
935 --ADDED FOR OKS REQ
936 Select B.INVENTORY_ITEM_ID Id1,
937 T.DESCRIPTION Name,
938 B.CONCATENATED_SEGMENTS Description,
939 B.COVERAGE_SCHEDULE_ID COVERAGE_TEMPLATE_ID
940 From MTL_SYSTEM_ITEMS_B_KFV B,MTL_SYSTEM_ITEMS_TL T --OKX_SYSTEM_ITEMS_V
941 Where B.SERVICE_ITEM_FLAG='Y'
942 And B.VENDOR_WARRANTY_FLAG = 'N'
943 And B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
944 AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
945 AND T.LANGUAGE = userenv('LANG')
946 --npalepu modified on 23-dec-2005 for bug # 4897884
947 /* And B.ORGANIZATION_ID = okc_context.get_okc_organization_id; */
948 AND B.ORGANIZATION_ID = v_organization_id;
949 --end 4897884
950
951 Cursor l_party_csr (l_custid Number) Is
952 Select Party_Id From HZ_CUST_ACCOUNTS -- OKX_CUSTOMER_ACCOUNTS_V
953 Where CUST_ACCOUNT_ID = l_custid;
954
955
956 l_party_id Number;
957
958
959 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
960 l_api_version CONSTANT NUMBER := 11.5;
961 l_row_count NUMBER;
962 l_msg_data VARCHAR2(2000);
963 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
964 l_ptr Binary_Integer := 0 ;
965 l_avail Varchar2(1);
966 l_isrec Check_service_rec_type;
967 l_Srv_tbl order_service_tbl_type;
968 l_found BOOLEAN;
969
970 --NPALEPU 21-sep-2005 for bug # 4608694
971 l_original_org_id NUMBER;
972 l_original_access_mode Varchar2(1);
973 --npalepu added on 23-dec-2005 for bug # 4897884
974 l_org_id NUMBER;
975 l_default_org_id hr_operating_units.organization_id%TYPE;
976 l_default_ou_name hr_operating_units.name%TYPE;
977 l_ou_count NUMBER;
978 l_organization_id NUMBER;
979 --end 4897884
980 --END NPALEPU
981
982 BEGIN
983
984 /* l_party_id := Null;
985
986 If p_avail_service_rec.customer_id Is Not Null Then
987
988 Open l_party_csr (p_avail_service_rec.customer_id);
989 Fetch l_party_csr Into l_party_id;
990 Close l_party_csr;
991
992 If l_party_id IS Null Then
993 l_return_status := 'E';
994 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',to_char(p_check_service_rec.customer_id));
995 Raise G_EXCEPTION_HALT_VALIDATION;
996 End If;
997
998 End If;
999 */
1000
1001 --NPALEPU 21-sep-2005 for bug # 4608694
1002
1003 /* --set org_id if it is null
1004 if okc_context.get_okc_org_id IS NULL then
1005 okc_context.set_okc_org_context;
1006 end if; */
1007
1008 --capturing the original context
1009 l_original_org_id := mo_global.get_current_org_id;
1010 l_original_access_mode := mo_global.get_access_mode();
1011
1012 IF p_org_id IS NOT NULL THEN
1013 --npalepu added on 23-dec-2005 for bug # 4897884
1014 /* okc_context.set_okc_org_context(p_org_id =>P_ORG_ID); */
1015 l_org_id := p_org_id;
1016 --end 4897884
1017 ELSIF l_original_org_id IS NOT NULL THEN
1018 --npalepu added on 23-dec-2005 for bug # 4897884
1019 /* okc_context.set_okc_org_context(p_org_id =>l_original_org_id); */
1020 l_org_id := l_original_org_id;
1021 --end 4897884
1022 ELSE
1023 --npalepu added on 23-dec-2005 for bug # 4897884
1024 /* okc_context.set_okc_org_context; */
1025 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
1026 l_org_id := l_default_org_id;
1027 --end 4897884
1028 END IF;
1029 --END NPALEPU
1030
1031 --npalepu added on 23-dec-2005 for bug # 4897884
1032 IF l_org_id IS NOT NULL AND l_org_id <> -99 THEN
1033 l_organization_id := OE_PROFILE.VALUE('OE_ORGANIZATION_ID',l_org_id);
1034 END IF;
1035 --end 4897884
1036
1037 --npalepu modified on 23-dec-2005 for bug # 4897884
1038 /* For l_srv_rec In l_srv_csr */
1039 For l_srv_rec In l_srv_csr(l_organization_id)
1040 --end 4897884
1041 Loop
1042
1043 l_avail := 'N';
1044
1045 l_isrec.service_item_id := l_srv_rec.id1;
1046 l_isrec.product_item_id := p_avail_service_rec.product_item_id;
1047 l_isrec.customer_id := p_avail_service_rec.customer_id;
1048 l_isrec.request_date := p_avail_service_rec.request_date;
1049
1050
1051 --npalepu modified on 20-nov-2005
1052 /* Is_Service_Available
1053 (
1054 1.0,
1055 OKC_API.G_FALSE,
1056 l_row_count,
1057 l_msg_Data,
1058 l_Return_Status,
1059 l_isrec,
1060 l_Avail
1061 ); */
1062 Is_Service_Available
1063 (
1064 P_Api_Version => 1.0,
1065 P_init_msg_list => OKC_API.G_FALSE,
1066 X_msg_Count => l_row_count,
1067 X_msg_Data => l_msg_Data,
1068 X_Return_Status => l_Return_Status,
1069 p_check_service_rec => l_isrec,
1070 X_Available_YN => l_Avail
1071 );
1072 --end npalepu
1073
1074 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1075 Raise G_EXCEPTION_HALT_VALIDATION;
1076 End If;
1077
1078
1079 If l_Avail = 'Y' Then
1080 l_ptr := l_ptr + 1;
1081 X_Orderable_Service_tbl(l_ptr).Service_Item_id := l_srv_rec.id1;
1082 --ADDED FOR OKS REQ
1083 X_Orderable_Service_tbl(l_ptr).Name := l_srv_rec.Name;
1084 X_Orderable_Service_tbl(l_ptr).Description := l_srv_rec.Description;
1085 X_Orderable_Service_tbl(l_ptr).Coverage_template_id := l_srv_rec.Coverage_template_id;
1086 End If;
1087
1088 End Loop;
1089
1090 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1091
1092 --npalepu removed the following code for bug # 4897884
1093 /* --NPALEPU 21-sep-2005 for bug # 4608694
1094 --Resetting to original context
1095 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
1096 --END NPALEPU */
1097 --end 4897884
1098
1099 Exception
1100 When G_EXCEPTION_HALT_VALIDATION Then
1101 x_return_status := l_return_status;
1102 Null;
1103 --npalepu removed the following code for bug # 4897884
1104 /* --NPALEPU 21-sep-2005 for bug # 4608694
1105 --Resetting to original context
1106 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
1107 --END NPALEPU */
1108 --end 4897884
1109
1110 When Others Then
1111 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1112 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1113 --npalepu removed the following code for bug # 4897884
1114 /* --NPALEPU 21-sep-2005 for bug # 4608694
1115 --Resetting to original context
1116 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
1117 --END NPALEPU */
1118 --end 4897884
1119 END;
1120
1121
1122 Procedure Is_service_available
1123 (p_api_version IN Number
1124 ,p_party_id IN Number
1125 ,p_service_id IN Number
1126 ,p_request_date IN Date Default sysdate
1127 ,p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE
1128 ,x_available_yn OUT NOCOPY Varchar2
1129 ,x_msg_Count OUT NOCOPY Number
1130 ,x_msg_Data OUT NOCOPY Varchar2
1131 ,x_return_status OUT NOCOPY Varchar2)
1132 Is
1133
1134 End_Exc Exception;
1135 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
1136 l_api_version CONSTANT NUMBER := 11.5;
1137
1138 Cursor l_service_csr (Obj_type Varchar2) Is
1139 Select Id
1140 ,General_YN
1141 ,Except_Object_Type
1142 ,Start_Date_Active
1143 ,End_Date_Active
1144 From OKS_SERV_AVAILS_V
1145 Where object1_id1 = p_service_id
1146 And Except_Object_type = Nvl(obj_type,Except_Object_type)
1147 Order By Except_Object_type;
1148
1149 l_service_rec l_service_csr%ROWTYPE;
1150
1151 Cursor l_customer_csr (p_mast_id Number, p_party_id Number) Is
1152 Select Object1_Id1,
1153 Start_Date_Active,
1154 End_Date_Active
1155 From OKS_SERV_AVAIL_EXCEPTS_V
1156 Where SAV_Id = p_mast_id
1157 And Object1_Id1 = p_party_id;
1158
1159
1160 BEGIN
1161 -- initialize return status
1162 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1163 If p_Service_id Is Null Or p_party_id Is Null Then
1164 x_return_status := OKC_API.G_RET_STS_ERROR;
1165 OKC_API.set_message(G_APP_NAME, 'OKS_MISSING_REQUIRED_PARAMETERS');
1166 Raise G_EXCEPTION_HALT_VALIDATION;
1167 End If;
1168
1169 Open l_service_csr ('C');
1170 Fetch l_service_csr Into l_service_rec;
1171 If l_service_csr%NOTFOUND Then
1172 -- Close l_service_csr;
1173 x_available_yn := 'Y';
1174 --Stat := TRUE;
1175 Raise End_Exc;
1176 Else
1177 If l_service_rec.general_yn = 'Y' Then
1178 If TRUNC(NVL(p_request_date ,sysdate))
1179 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_request_date ,sysdate)))
1180 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_request_date ,sysdate)))
1181 Then
1182 For l_customer_rec In l_customer_csr(l_service_rec.Id, p_party_id)
1183 Loop
1184 If TRUNC(NVL(p_request_date ,sysdate))
1185 Between TRUNC(NVL(l_customer_rec.Start_Date_Active, NVL(p_request_date ,sysdate)))
1186 And TRUNC(NVL(l_customer_rec.End_Date_Active, NVL(p_request_date ,sysdate)))
1187 Then
1188 -- Stat := FALSE;
1189 x_available_yn := 'N';
1190 Raise End_Exc;
1191 End If;
1192 End Loop; -- For l_customer_rec In l_customer_csr(l_service_rec.Id, p_party_id)
1193
1194 --Stat := TRUE;
1195 x_available_yn := 'Y';
1196 Raise End_Exc;
1197 Else
1198 -- Stat := FALSE;
1199 x_available_yn := 'N';
1200 Raise End_Exc;
1201 End If;
1202 Else
1203 If TRUNC(NVL(p_request_date ,sysdate))
1204 Between TRUNC(NVL(l_service_rec.Start_Date_Active, NVL(p_request_date ,sysdate)))
1205 And TRUNC(NVL(l_service_rec.End_Date_Active, NVL(p_request_date ,sysdate)))
1206 Then
1207 For l_customer_rec In l_customer_csr(l_service_rec.Id, p_party_id)
1208 Loop
1209 If TRUNC(NVL(p_request_date ,sysdate))
1210 Between TRUNC(NVL(l_customer_rec.Start_Date_Active, NVL(p_request_date ,sysdate)))
1211 And TRUNC(NVL(l_customer_rec.End_Date_Active, NVL(p_request_date ,sysdate)))
1212 Then
1213 -- Stat := TRUE;
1214 x_available_yn := 'Y';
1215 Raise End_Exc;
1216 End If;
1217 End Loop;
1218 --Stat := FALSE;
1219 x_available_yn := 'N';
1220 Raise End_Exc;
1221 Else
1222 --Stat := TRUE;
1223 x_available_yn := 'Y';
1224 Raise End_Exc;
1225 End If;
1226
1227 End If;
1228 End If;
1229 Close l_service_csr;
1230 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1231
1232
1233 Exception
1234
1235 When End_Exc Then
1236 Close l_service_csr;
1237 Null;
1238 When Others Then
1239 x_available_yn := 'N';
1240 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1241 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1242
1243 END Is_service_available;
1244
1245
1246 Procedure Delete_Contract_details
1247 ( p_api_version IN Number
1248 ,p_init_msg_list IN Varchar2 Default OKC_API.G_FALSE
1249 ,p_order_line_id IN Number
1250 ,x_msg_Count OUT NOCOPY Number
1251 ,x_msg_Data OUT NOCOPY Varchar2
1252 ,x_return_status OUT NOCOPY Varchar2)
1253 Is
1254
1255 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
1256 l_api_version CONSTANT NUMBER := 11.5;
1257 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1258 l_msg_count Number;
1259 l_msg_data Varchar(2000);
1260 l_line_id Number := Null;
1261 l_coc_tbl_in Oks_coc_pvt.cocv_tbl_type;
1262 l_coc_tbl_out Oks_coc_pvt.cocv_tbl_type;
1263 l_cod_tbl_in Oks_cod_pvt.codv_tbl_type;
1264 l_cod_tbl_out Oks_cod_pvt.codv_tbl_type;
1265
1266 Cursor l_k_csr(l_order_line_id Number) Is
1267 Select Id
1268 From Oks_k_order_details
1269 Where Order_line_id1 = to_char(l_order_line_id);
1270
1271
1272 Cursor l_kdetails_csr(l_line_id Number) Is
1273 Select Id
1274 From Oks_k_order_details
1275 Where Link_ord_line_id1 = to_char(l_line_id);
1276
1277
1278 Cursor l_contact_csr(l_id Number) Is
1279 Select Id
1280 From Oks_k_order_contacts
1281 Where Cod_id = l_id;
1282
1283
1284
1285 BEGIN
1286
1287
1288 x_return_status := l_return_status;
1289
1290 Open l_k_csr(p_order_line_id);
1291 Fetch l_k_csr into l_line_id;
1292 Close l_k_csr;
1293
1294 If l_line_id is Not Null Then
1295 For l_rec in l_kdetails_csr(p_Order_line_id)
1296 Loop
1297
1298 For l_contact_rec in l_contact_csr(l_rec.id)
1299 Loop
1300
1301
1302 l_coc_tbl_in(1).id := l_contact_rec.id;
1303
1304 Oks_Order_Contacts_Pub.Delete_Order_Contact
1305 (
1306 P_api_version => 1.0
1307 ,P_init_msg_list => 'T'
1308 ,X_return_status => l_return_status
1309 ,X_msg_count => l_msg_count
1310 ,X_msg_data => l_msg_data
1311 ,P_cocv_tbl => l_coc_tbl_in
1312
1313 );
1314
1315 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1316 Raise G_EXCEPTION_HALT_VALIDATION;
1317 End If;
1318 End Loop;
1319
1320 l_cod_tbl_in(1).Id := l_rec.id;
1321 Oks_Order_details_pub.Delete_order_detail
1322 (
1323 P_api_version => 1.0
1324 ,P_init_msg_list => 'T'
1325 ,X_return_status => l_return_status
1326 ,X_msg_count => l_msg_count
1327 ,X_msg_data => l_msg_data
1328 ,P_codv_tbl => l_cod_tbl_in
1329 );
1330
1331 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1332 Raise G_EXCEPTION_HALT_VALIDATION;
1333 End If;
1334 End Loop;
1335
1336 For l_contact_rec in l_contact_csr(l_line_id)
1337 Loop
1338 l_coc_tbl_in(1).id := l_contact_rec.id;
1339
1340 Oks_Order_Contacts_Pub.Delete_Order_Contact
1341 (
1342 P_api_version => 1.0
1343 ,P_init_msg_list => 'T'
1344 ,X_return_status => l_return_status
1345 ,X_msg_count => l_msg_count
1346 ,X_msg_data => l_msg_data
1347 ,P_cocv_tbl => l_coc_tbl_in
1348
1349 );
1350
1351 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1352 Raise G_EXCEPTION_HALT_VALIDATION;
1353 End If;
1354 End Loop;
1355
1356 l_cod_tbl_in(1).Id := l_line_id;
1357 Oks_Order_details_pub.Delete_order_detail
1358 (
1359 P_api_version => 1.0
1360 ,P_init_msg_list => 'T'
1361 ,X_return_status => l_return_status
1362 ,X_msg_count => l_msg_count
1363 ,X_msg_data => l_msg_data
1364 ,P_codv_tbl => l_cod_tbl_in
1365 );
1366
1367 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
1368 Raise G_EXCEPTION_HALT_VALIDATION;
1369 End If;
1370
1371
1372
1373 End If;
1374
1375
1376
1377
1378
1379
1380 Exception
1381 When G_EXCEPTION_HALT_VALIDATION Then
1382 x_return_status := l_return_status;
1383 When Others Then
1384 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1385 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1386
1387 END Delete_Contract_details;
1388
1389
1390
1391 Procedure GET_SVC_SDATE
1392 (
1393 P_api_version IN Number,
1394 P_init_msg_list IN Varchar2,
1395 P_order_line_id IN Number, -- (Service Order line Id)
1396 X_msg_count OUT NOCOPY Number,
1397 X_msg_data OUT NOCOPY Varchar2,
1398 X_return_status OUT NOCOPY Varchar2,
1399 X_start_date OUT NOCOPY Date,
1400 X_end_date OUT NOCOPY Date
1401 )
1402 Is
1403
1404 Cursor l_Oline_csr Is
1405 Select service_reference_type_code
1406 ,service_reference_line_id
1407 ,service_duration
1408 ,service_period
1409 ,fulfillment_date
1410 From oe_order_lines_all
1411 Where line_id = p_order_line_id;
1412
1413 Cursor l_get_warr_dates_csr(p_cp_id Number) IS
1414 Select max(ol.end_date)
1415 From okc_k_items ot, okc_k_lines_b ol
1416 Where ot.object1_id1 = to_char(p_cp_id) -- Bug Fix #5011519
1417 And ol.id = ot.cle_id
1418 And ol.lse_id = 18 ;
1419
1420 Cursor l_cp_csr(p_line_id Number) Is
1421 Select csi.instance_id
1422 ,csi.install_date
1423 ,ol.Actual_shipment_date
1424 ,ol.schedule_ship_date
1425 From csi_item_instances csi
1426 ,oe_order_lines_all ol
1427 Where ol.line_id = csi.last_oe_order_line_id
1428 And ol.inventory_item_id = csi.inventory_item_id
1429 And ol.line_id = p_line_id;
1430
1431 Cursor l_product_csr(p_cp_id Number) Is
1432 Select csi.install_date
1433 From csi_item_instances csi
1434 Where csi.instance_id = p_cp_id;
1435
1436
1437 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1438 l_cp_rec l_cp_csr%rowtype;
1439 l_line_rec l_oline_csr%rowtype;
1440 l_install_date Date;
1441 l_ship_date Date;
1442 l_warend_date Date;
1443
1444
1445
1446 Begin
1447
1448 l_line_rec := Null;
1449 l_cp_rec := Null;
1450
1451 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1452 x_return_status := l_return_status;
1453 Open l_oline_csr;
1454 Fetch l_oline_csr into l_line_rec;
1455 Close l_oline_csr;
1456
1457 If l_line_rec.Service_reference_type_code = 'ORDER' Then
1458 Open l_cp_csr(l_line_rec.service_reference_line_id);
1459 Fetch l_cp_csr into l_cp_rec;
1460 Close l_cp_csr;
1461
1462 If l_cp_rec.instance_id Is Null Then
1463 l_return_status := 'E';
1464 OKC_API.set_message(G_APP_NAME,'OKS_NULL_SDT','LINE_ID',p_order_line_id);
1465 Raise G_EXCEPTION_HALT_VALIDATION;
1466 End If;
1467 l_warend_date := Null;
1468 Open l_get_warr_dates_csr(l_cp_rec.instance_id);
1469 fetch l_get_warr_dates_csr into l_warend_date;
1470 Close l_get_warr_dates_csr;
1471
1472 If l_warend_date is Not null Then
1473 X_start_date := trunc(l_warend_date) + 1;
1474
1475 Else
1476 l_ship_date := nvl(l_cp_rec.actual_shipment_date,l_cp_rec.schedule_ship_date);
1477 X_start_date := Trunc(Nvl(l_cp_rec.install_date, l_ship_date));
1478 End If;
1479
1480
1481 ElsIf l_line_rec.Service_reference_type_code = 'CUSTOMER_PRODUCT' Then
1482 l_warend_date := Null;
1483 Open l_get_warr_dates_csr(l_line_rec.service_reference_line_id);
1484 Fetch l_get_warr_dates_csr into l_warend_date;
1485 Close l_get_warr_dates_csr;
1486
1487 If l_warend_date is Not null Then
1488 X_start_date := trunc(l_warend_date) + 1;
1489
1490 Else
1491 Open l_product_csr(l_line_rec.service_reference_line_id);
1492 Fetch l_product_csr into l_install_date;
1493 Close l_product_csr;
1494
1495
1496 X_start_date := Trunc(NVL(l_install_date,l_line_rec.fulfillment_date));
1497 End If;
1498
1499 End If;
1500 If X_start_date Is Null Then
1501 l_return_status := 'E';
1502 OKC_API.set_message(G_APP_NAME,'OKS_NULL_SDT','LINE_ID',p_order_line_id);
1503 Raise G_EXCEPTION_HALT_VALIDATION;
1504 End If;
1505 X_end_date := okc_time_util_pub.get_enddate
1506 ( X_Start_Date
1507 ,l_line_rec.service_period
1508 ,l_line_rec.service_duration
1509 );
1510
1511 x_return_status := l_return_status;
1512
1513 Exception
1514 When G_EXCEPTION_HALT_VALIDATION Then
1515 x_return_status := l_return_status;
1516 When Others Then
1517 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1518 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1519
1520
1521
1522 End;
1523
1524 -- Added for ASO Queue Replacement
1525
1526 PROCEDURE Interface_Service_Order_Lines
1527 (p_Service_Order_Lines IN Service_Order_Lines_TblType
1528 ,x_Return_Status OUT NOCOPY VARCHAR2
1529 ,x_Error_Message OUT NOCOPY VARCHAR2)
1530 IS
1531
1532 TYPE Num_TblType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1533
1534 l_Order_Header_ID_Tbl Num_TblType;
1535 l_Order_Line_ID_Tbl Num_TblType;
1536 l_Order_Number_Tbl Num_TblType;
1537
1538 l_Tbl_Idx NUMBER;
1539 l_SrvOrdLine_Idx NUMBER;
1540 l_SrvOrdLine_Idx_FIRST NUMBER;
1541 l_SrvOrdLine_Idx_LAST NUMBER;
1542
1543 BEGIN
1544
1545 l_Tbl_Idx := 0;
1546 l_SrvOrdLine_Idx := p_Service_Order_Lines.FIRST;
1547
1548 WHILE l_SrvOrdLine_Idx IS NOT NULL LOOP
1549
1550 l_Tbl_Idx := l_Tbl_Idx + 1;
1551
1552 l_Order_Header_ID_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Header_ID;
1553 l_Order_Line_ID_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Line_ID;
1554 l_Order_Number_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Number;
1555
1556 l_SrvOrdLine_Idx := p_Service_Order_Lines.NEXT(l_SrvOrdLine_Idx);
1557
1558 END LOOP;
1559
1560 --
1561
1562 l_SrvOrdLine_Idx_FIRST := l_Order_Line_ID_Tbl.FIRST;
1563 l_SrvOrdLine_Idx_LAST := l_Order_Line_ID_Tbl.LAST;
1564
1565 --
1566
1567 FORALL f_SrvOrdLine_Idx IN l_SrvOrdLine_Idx_FIRST .. l_SrvOrdLine_Idx_LAST
1568 INSERT INTO OKS_REPROCESSING
1569 (ID
1570 ,ORDER_ID
1571 ,ORDER_LINE_ID
1572 ,CONTRACT_ID
1573 ,CONTRACT_LINE_ID
1574 ,SUBLINE_ID
1575 ,ERROR_TEXT
1576 ,SUCCESS_FLAG
1577 ,SOURCE_FLAG
1578 ,CONC_REQUEST_ID
1579 ,CREATED_BY
1580 ,CREATION_DATE
1581 ,LAST_UPDATED_BY
1582 ,LAST_UPDATE_DATE
1583 ,LAST_UPDATE_LOGIN
1584 ,OBJECT_VERSION_NUMBER
1585 ,SECURITY_GROUP_ID
1586 ,REPROCESS_YN
1587 ,ORDER_NUMBER )
1588 VALUES
1589 (TO_NUMBER(RAWTOHEX(SYS_GUID()),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
1590 ,l_Order_Header_ID_Tbl(f_SrvOrdLine_Idx)
1591 ,l_Order_Line_ID_Tbl(f_SrvOrdLine_Idx)
1592 ,NULL
1593 ,NULL
1594 ,NULL
1595 ,NULL
1596 ,'N'
1597 ,'ASO'
1598 ,NULL
1599 ,FND_GLOBAL.USER_ID
1600 ,SYSDATE
1601 ,FND_GLOBAL.USER_ID
1602 ,SYSDATE
1603 ,FND_GLOBAL.LOGIN_ID
1604 ,1
1605 ,NULL
1606 ,'Y'
1607 ,l_Order_Number_Tbl(f_SrvOrdLine_idx));
1608
1609 x_Return_Status := 'S';
1610
1611 EXCEPTION
1612
1613 WHEN OTHERS THEN
1614 x_Return_Status := 'U';
1615 x_Error_Message := 'Unexpected Error :'||' ('||SQLCODE||') '||SUBSTR(SQLERRM,1,170);
1616
1617 END Interface_Service_Order_Lines;
1618
1619 /*
1620 Important Note regarding bug#5330614 , Dated 14-JUN-2006
1621 This function OKS_OMINT_PUB.Get_quantity is called by Pricing engine to prorate the price breaks if
1622 the prorate option is set to 'ALL' for the usage. This function should not be changed and should remain
1623 as is as it is needed for proration of price breaks.
1624 OKS_OMINT_PUB.Get_target_duration function is called by pricing engine to determine the
1625 duration between pair of passed service dates and in case of usage, this function will return NULL.
1626 */
1627
1628 FUNCTION get_quantity(p_start_date IN DATE,
1629 p_end_date IN DATE,
1630 p_source_uom IN VARCHAR2 DEFAULT NULL,
1631 p_org_id IN VARCHAR2 DEFAULT NULL)
1632 return number
1633 as
1634
1635 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
1636 is
1637 SELECT 1
1638 FROM MTL_UNITS_OF_MEASURE_TL
1639 WHERE uom_code = p_uom_code
1640 AND LANGUAGE = USERENV('LANG');
1641 cr_validate_uom cs_validate_uom%ROWTYPE;
1642
1643
1644 --Added for bug# 5623790 by sjanakir
1645 CURSOR l_lse_id_csr(p_id IN NUMBER) IS
1646 SELECT lse_id
1647 FROM OKC_K_LINES_B
1648 WHERE id = p_id;
1649 --Addition Ends
1650 l_target_qty NUMBER;
1651 l_source_uom varchar2(30);
1652 l_price_uom varchar2(30);
1653 l_period_type varchar2(30);
1654 l_period_start varchar2(30);
1655 l_chr_id NUMBER;
1656 l_status varchar2(80);
1657 --Added for bug# 5623790 by sjanakir
1658 l_cle_id NUMBER;
1659 l_lse_id NUMBER;
1660 --Addition Ends
1661
1662 invalid_date_exception EXCEPTION;
1663 invalid_uom_exception EXCEPTION;
1664
1665 BEGIN
1666 IF p_source_uom Is Null Then
1667 l_source_uom := OKS_TIME_MEASURES_PUB.get_uom_code('MONTH',1);
1668 Else
1669 open cs_validate_uom(p_source_uom);
1670 fetch cs_validate_uom into cr_validate_uom;
1671
1672 IF cs_validate_uom%NOTFOUND
1673 THEN
1674 RAISE INVALID_UOM_EXCEPTION;
1675 END IF;
1676
1677 l_source_uom := p_source_uom;
1678 close cs_validate_uom;
1679 END IF;
1680
1681 IF (p_start_date IS NULL)OR(p_end_date IS NULL)OR(p_start_date > p_end_date)
1682 THEN
1683 RAISE INVALID_DATE_EXCEPTION;
1684 END IF;
1685
1686 --OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID is set by QP_PKG before calling pricing engine
1687
1688 l_chr_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID;
1689
1690 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1691 p_org_id => p_org_id,
1692 x_period_type => l_period_type,
1693 x_period_start => l_period_start,
1694 x_price_uom => l_price_uom,
1695 x_return_status => l_status);
1696 --Added for bug# 5623790 by sjanakir
1697 l_cle_id := OKS_QPATTRIB_PVT.G_CONTRACT_LINREC.TOP_LINE_ID;
1698
1699 Open l_lse_id_csr(l_cle_id);
1700 Fetch l_lse_id_csr into l_lse_id;
1701 Close l_lse_id_csr;
1702
1703 IF l_lse_id = 12 THEN
1704 l_period_start := 'SERVICE';
1705 END IF;
1706 --Addition Ends
1707
1708
1709 IF l_status = OKC_API.G_RET_STS_ERROR
1710 THEN
1711 return 0;
1712 END IF;
1713
1714 IF l_chr_id IS NULL
1715 THEN
1716 l_period_start := 'SERVICE'; --one time billing for OM/ASO case
1717 END IF;
1718
1719 l_target_qty := OKS_TIME_MEASURES_PUB.get_quantity(p_start_date => p_start_date,
1720 p_end_date => p_end_date,
1721 p_source_uom => p_source_uom,
1722 p_period_type => l_period_type,
1723 p_period_start => l_period_start);
1724
1725 return(l_target_qty);
1726
1727 EXCEPTION
1728 WHEN
1729 INVALID_UOM_EXCEPTION
1730 THEN
1731 OKC_API.set_message('OKS','OKS_INVD_UOM_CODE');
1732 close cs_validate_uom;
1733 return 0;
1734 WHEN
1735 INVALID_DATE_EXCEPTION
1736 THEN
1737 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
1738 return 0;
1739 WHEN OTHERS THEN
1740 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1741 return 0;
1742
1743 END get_quantity;
1744
1745 --21-DEC-2005 mchoudha
1746 /* NEW Function added for R12 enhancement partialperiods. This function will replace
1747 the use of OKS_TIME_MEASURES_PUB.get_quantity by Pricing, and will also replace the use
1748 of inventory uom conversion API by Order Management and Quoting/Sales Online.
1749 */
1750
1751 /*
1752 Important Note regarding bug#5330614 dated 14-JUN-2006 :
1753 OKS_OMINT_PUB.Get_target_duration function is called by pricing engine to determine the
1754 duration between pair of passed service dates and in case of usage this function will return NULL.
1755 The function OKS_OMINT_PUB.Get_quantity is called by Pricing engine to prorate the price breaks if
1756 the prorate option is set to 'ALL' for the usage.
1757 */
1758 FUNCTION get_target_duration ( p_start_date IN DATE DEFAULT NULL,
1759 p_end_date IN DATE DEFAULT NULL,
1760 p_source_uom IN VARCHAR2 DEFAULT NULL,
1761 p_source_duration IN NUMBER DEFAULT NULL,
1762 p_target_uom IN VARCHAR2 DEFAULT NULL,/*Default Month*/
1763 p_org_id IN NUMBER DEFAULT NULL)
1764 return number
1765 AS
1766
1767 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
1768 is
1769 SELECT 1
1770 FROM MTL_UNITS_OF_MEASURE_TL
1771 WHERE uom_code = p_uom_code
1772 AND LANGUAGE = USERENV('LANG');
1773 cr_validate_uom cs_validate_uom%ROWTYPE;
1774
1775 --Added for bug#5330614
1776 Cursor l_lse_id_csr(p_id IN NUMBER) IS
1777 SELECT lse_id
1778 FROM OKC_K_LINES_B
1779 WHERE id = p_id;
1780
1781 l_target_qty NUMBER;
1782 l_target_uom varchar2(30);
1783 l_price_uom varchar2(30);
1784 l_period_type varchar2(30);
1785 l_period_start varchar2(30);
1786 l_chr_id NUMBER;
1787 l_cle_id NUMBER;
1788 l_top_line_id NUMBER;
1789 l_source_uom_quantity NUMBER;
1790 l_source_tce_code VARCHAR2(30);
1791 l_target_uom_quantity NUMBER;
1792 l_target_tce_code VARCHAR2(30);
1793 l_return_status VARCHAR2(1);
1794 l_lse_id NUMBER;
1795 invalid_date_exception EXCEPTION;
1796 invalid_uom_exception EXCEPTION;
1797 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
1798
1799 /* CURSOR om_period_csr(p_id IN NUMBER) IS
1800 select service_period,service_duration
1801 from okc_k_lines_b subline,
1802 okc_k_rel_objs rel,
1803 oe_order_lines_all oel
1804 where subline.cle_id = p_id
1805 and rel.cle_id = subline.id
1806 and oel.line_id = rel.object1_id1;*/
1807
1808 BEGIN
1809
1810 --check for target uom passed if null then default to month
1811 IF p_target_uom Is Null Then
1812 l_target_uom := OKS_TIME_MEASURES_PUB.get_uom_code('MONTH',1);
1813 Else
1814 --validate the target uom passed
1815 open cs_validate_uom(p_target_uom);
1816 fetch cs_validate_uom into cr_validate_uom;
1817
1818 IF cs_validate_uom%NOTFOUND
1819 THEN
1820 RAISE INVALID_UOM_EXCEPTION;
1821 END IF;
1822
1823 l_target_uom := p_target_uom;
1824 close cs_validate_uom;
1825 END IF;
1826
1827 --fetching the contract id
1828 l_chr_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID;
1829
1830 --Added for bug#5330614
1831 l_cle_id := OKS_QPATTRIB_PVT.G_CONTRACT_LINREC.TOP_LINE_ID;
1832
1833 Open l_lse_id_csr(l_cle_id);
1834 Fetch l_lse_id_csr into l_lse_id;
1835 Close l_lse_id_csr;
1836
1837 IF l_lse_id = 12 THEN
1838 return NULL;
1839 END IF;
1840 --bug#5330614
1841
1842 --l_top_line_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.TOP_LINE_ID;
1843 --l_service_period := NULL;
1844 --Call from OKS to Price Engine
1845 /*IF l_top_line_id IS NOT NULL THEN
1846 Open om_period_csr(l_top_line_id);
1847 Fetch om_period_csr into l_service_period,l_service_duration;
1848 Close om_period_csr;
1849 END IF; */
1850
1851 --22-MAR-2006 mchoudha Changes for Partial periods CR3
1852 IF l_chr_id IS NOT NULL OR --price engine call from OKS
1853 (l_chr_id IS NULL AND (p_start_date IS NOT NULL) AND (p_end_date IS NOT NULL)) --OM call with start date present
1854 THEN
1855
1856 IF (p_start_date IS NOT NULL) AND (p_end_date IS NOT NULL)
1857 THEN
1858
1859 IF (p_start_date > p_end_date) THEN
1860 RAISE INVALID_DATE_EXCEPTION;
1861 END IF;
1862
1863
1864 --OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID is set by QP_PKG before calling pricing engine
1865
1866 --get the partial period attributes stamped on the contract
1867 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1868 p_org_id => p_org_id,
1869 x_period_type => l_period_type,
1870 x_period_start => l_period_start,
1871 x_price_uom => l_price_uom,
1872 x_return_status => l_return_status);
1873 IF l_return_status = OKC_API.G_RET_STS_ERROR
1874 THEN
1875 return 0;
1876 END IF;
1877
1878 --mchoudha added l_chr_id is NULL condition for bug#5182587
1879 --so that the assignment within IF condition gets executed only for OM contracts
1880 IF l_period_start IS NOT NULL AND l_chr_id IS NULL THEN
1881 l_period_start := 'SERVICE';
1882 END IF;
1883
1884 l_target_qty := OKS_TIME_MEASURES_PUB.get_quantity(p_start_date => p_start_date,
1885 p_end_date => p_end_date,
1886 p_source_uom => l_target_uom,
1887 p_period_type => l_period_type,
1888 p_period_start => l_period_start);
1889 ELSE
1890 return 0;
1891 END IF;
1892 /*ELSIF l_chr_id IS NOT NULL AND l_service_period IS NOT NULL --OKS price engine call for OM/ASO/Istore case
1893 NULL;*/
1894
1895 ELSE --OM/ASO/Istore case
1896
1897 l_period_start := 'SERVICE';
1898 l_period_type := 'FIXED';
1899
1900 IF (p_source_uom is NOT NULL) AND (p_source_duration is NOT NULL) THEN
1901
1902 --Get the seeded timeunit for the source uom
1903 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
1904 (p_timeunit => p_source_uom,
1905 x_return_status => l_return_status,
1906 x_quantity => l_source_uom_quantity ,
1907 x_timeunit => l_source_tce_code);
1908 IF l_return_status <> 'S' THEN
1909 RAISE G_EXCEPTION_HALT_VALIDATION;
1910 END IF;
1911 --Get the seeded timeunit for the target uom
1912 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
1913 (p_timeunit => l_target_uom,
1914 x_return_status => l_return_status,
1915 x_quantity => l_target_uom_quantity ,
1916 x_timeunit => l_target_tce_code);
1917 IF l_return_status <> 'S' THEN
1918 RAISE G_EXCEPTION_HALT_VALIDATION;
1919 END IF;
1920
1921 /*Conversion logic using fixed 30 days in a month*/
1922
1923 --When source uom is seeded in terms of DAY
1924 IF l_source_tce_code ='DAY' THEN
1925 IF l_target_tce_code ='YEAR' THEN
1926 l_target_qty:= p_source_duration*(l_source_uom_quantity/(360*l_target_uom_quantity));
1927 ELSIF l_target_tce_code ='MONTH' THEN
1928 l_target_qty:= p_source_duration*(l_source_uom_quantity/(30*l_target_uom_quantity));
1929 ELSIF l_target_tce_code ='DAY' THEN
1930 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1931 END IF;
1932 --When source uom is seeded in terms of MONTH
1933 ELSIF l_source_tce_code ='MONTH' THEN
1934 IF l_target_tce_code ='YEAR' THEN
1935 l_target_qty:= p_source_duration*(l_source_uom_quantity/(12*l_target_uom_quantity));
1936 ELSIF l_target_tce_code ='MONTH' THEN
1937 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1938 ELSIF l_target_tce_code ='DAY' THEN
1939 l_target_qty:= p_source_duration*((l_source_uom_quantity*30)/l_target_uom_quantity);
1940 END IF;
1941 --When source uom is seeded in terms of YEAR
1942 ELSIF l_source_tce_code ='YEAR' THEN
1943 IF l_target_tce_code ='YEAR' THEN
1944 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1945 ELSIF l_target_tce_code ='MONTH' THEN
1946 l_target_qty:= p_source_duration*((l_source_uom_quantity*12)/l_target_uom_quantity);
1947 ELSIF l_target_tce_code ='DAY' THEN
1948 l_target_qty:= p_source_duration*((l_source_uom_quantity*360)/l_target_uom_quantity);
1949 END IF;
1950
1951 END IF;
1952 ELSE --none of two sets of parameters are passed so set the error message and return 0
1953 OKC_API.set_message('OKS','OKS_INVD_DURATION');
1954 return 0;
1955 END IF;
1956 END IF;
1957 return(l_target_qty);
1958
1959 EXCEPTION
1960 WHEN
1961 INVALID_UOM_EXCEPTION
1962 THEN
1963 OKC_API.set_message('OKS','OKS_INVD_UOM_CODE');
1964 close cs_validate_uom;
1965 return 0;
1966 WHEN
1967 G_EXCEPTION_HALT_VALIDATION
1968 THEN
1969 return 0;
1970 WHEN
1971 INVALID_DATE_EXCEPTION
1972 THEN
1973 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
1974 return 0;
1975 WHEN OTHERS THEN
1976 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1977 return 0;
1978
1979 END get_target_duration;
1980
1981 --NPALEPU
1982 --23-JUN-2005
1983 --SERVICE AVAILABILITY API ENHANCEMENT(ER 3680488)
1984 --ADDED NEW OVERLOADED API "Available_Services"
1985
1986 PROCEDURE Available_Services
1987 (
1988 P_Api_Version IN NUMBER,
1989 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
1990 P_search_input IN VARCHAR2 Default OKC_API.G_MISS_CHAR,
1991 P_MAX_ROWS_RETURNED IN NUMBER Default 200,
1992 X_msg_Count OUT NOCOPY NUMBER,
1993 X_msg_Data OUT NOCOPY VARCHAR2,
1994 X_Return_Status OUT NOCOPY VARCHAR2,
1995 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
1996 X_Orderable_Service_tbl OUT NOCOPY NEW_ORDER_SERVICE_TBL_TYPE,
1997 --NPALEPU added on 21-sep-2005 for bug # 4608694
1998 P_ORG_ID IN NUMBER Default NULL
1999 --END NPALEPU
2000 )
2001 IS
2002
2003 --cursor to fetch the services when P_search_input value is not null
2004 --npalepu 23-dec-2005 for bug # 4897884 removed MTL_UNITS_OF_MEASURE_VL uom,
2005 -- MTL_UNITS_OF_MEASURE_VL srvuom,FND_LOOKUP_VALUES fnd,FND_LOOKUP_VALUES fnd1 tables
2006 Cursor l_service_csr(v_organization_id IN NUMBER) Is
2007 SELECT
2008 kfv.organization_id Organization_id,
2009 kfv.inventory_item_id id1,
2010 kfv.concatenated_segments Concatenated_segments,
2011 kfv.description Description,
2012 kfv.primary_uom_code Primary_uom_code,
2013 uom.description Primary_uom_description,
2014 kfv.Serviceable_product_flag Serviceable_product_flag,
2015 kfv.service_item_flag Service_item_flag,
2016 kfv.bom_item_type Bom_item_type,
2017 fnd.meaning Bom_item_type_meaning,
2018 kfv.item_type Item_type,
2019 fnd1.meaning Item_type_meaning,
2020 kfv.service_duration Service_duration,
2021 kfv.service_duration_period_code Service_duration_period_code,
2022 srvuom.description Service_duration_period_mean,
2023 kfv.shippable_item_flag shippable_item_flag,
2024 kfv.returnable_flag Returnable_flag
2025 FROM MTL_SYSTEM_ITEMS_B_KFV kfv,
2026 MTL_UNITS_OF_MEASURE_VL uom,
2027 MTL_UNITS_OF_MEASURE_VL srvuom,
2028 FND_LOOKUP_VALUES fnd,
2029 FND_LOOKUP_VALUES fnd1
2030 WHERE kfv.vendor_warranty_flag = 'N'
2031 AND kfv.service_item_flag = 'Y'
2032 AND kfv.customer_order_enabled_flag = 'Y'
2033 AND kfv.enabled_flag = 'Y'
2034 --npalepu modified on 23-dec-2005 for bug # 4897884
2035 /* AND kfv.organization_id = okc_context.get_okc_organization_id */
2036 AND kfv.organization_id = v_organization_id
2037 --end npalepu
2038 --NPALEPU,11-AUG-2005
2039 --Used UPPER Function as Requested by Quoting Team.
2040 --AND (kfv.concatenated_segments LIKE P_search_input OR kfv.description LIKE P_search_input )
2041 AND (UPPER(kfv.concatenated_segments) LIKE UPPER(P_search_input) OR UPPER(kfv.description) LIKE UPPER(P_search_input) )
2042 --END NPALEPU
2043 AND uom.uom_code = primary_uom_code
2044 AND srvuom.uom_code = service_duration_period_code
2045 AND fnd.lookup_type = 'BOM_ITEM_TYPE'
2046 AND fnd.lookup_code = bom_item_type
2047 AND fnd.language = USERENV('LANG')
2048 AND fnd1.lookup_type = 'ITEM_TYPE'
2049 AND fnd1.lookup_code = item_type
2050 AND fnd1.language = USERENV('LANG')
2051 ORDER BY CONCATENATED_SEGMENTS;
2052
2053 --cursor to fetch the services when P_search_input value is null
2054 --npalepu 23-dec-2005 for bug # 4897884 removed MTL_UNITS_OF_MEASURE_VL uom,
2055 -- MTL_UNITS_OF_MEASURE_VL srvuom,FND_LOOKUP_VALUES fnd,FND_LOOKUP_VALUES fnd1 tables
2056 Cursor l_service_csr1(v_organization_id IN NUMBER) Is
2057 SELECT
2058 kfv.organization_id Organization_id,
2059 kfv.inventory_item_id id1,
2060 kfv.concatenated_segments Concatenated_segments,
2061 kfv.description Description,
2062 kfv.primary_uom_code Primary_uom_code,
2063 uom.description Primary_uom_description,
2064 kfv.Serviceable_product_flag Serviceable_product_flag,
2065 kfv.service_item_flag Service_item_flag,
2066 kfv.bom_item_type Bom_item_type,
2067 fnd.meaning Bom_item_type_meaning,
2068 kfv.item_type Item_type,
2069 fnd1.meaning Item_type_meaning,
2070 kfv.service_duration Service_duration,
2071 kfv.service_duration_period_code Service_duration_period_code,
2072 srvuom.description Service_duration_period_mean,
2073 kfv.shippable_item_flag shippable_item_flag,
2074 kfv.returnable_flag Returnable_flag
2075 FROM MTL_SYSTEM_ITEMS_B_KFV kfv,
2076 MTL_UNITS_OF_MEASURE_VL uom,
2077 MTL_UNITS_OF_MEASURE_VL srvuom,
2078 FND_LOOKUP_VALUES fnd,
2079 FND_LOOKUP_VALUES fnd1
2080 WHERE kfv.vendor_warranty_flag = 'N'
2081 AND kfv.service_item_flag = 'Y'
2082 AND kfv.customer_order_enabled_flag = 'Y'
2083 AND kfv.enabled_flag = 'Y'
2084 --npalepu modified on 23-dec-2005 for bug # 4897884
2085 /* AND kfv.organization_id = okc_context.get_okc_organization_id */
2086 AND kfv.organization_id = v_organization_id
2087 --end npalepu
2088 AND uom.uom_code = primary_uom_code
2089 AND srvuom.uom_code = service_duration_period_code
2090 AND fnd.lookup_type = 'BOM_ITEM_TYPE'
2091 AND fnd.lookup_code = bom_item_type
2092 AND fnd.language = USERENV('LANG')
2093 AND fnd1.lookup_type = 'ITEM_TYPE'
2094 AND fnd1.lookup_code = item_type
2095 AND fnd1.language = USERENV('LANG')
2096 ORDER BY CONCATENATED_SEGMENTS;
2097
2098 Cursor l_party_csr (l_custid Number) Is
2099 Select Party_Id From OKX_CUSTOMER_ACCOUNTS_V
2100 Where Id1 = l_custid;
2101
2102 l_party_id Number;
2103
2104 TYPE Srv_tbl_type IS TABLE OF l_service_csr%ROWTYPE INDEX BY BINARY_INTEGER;
2105 l_Srv_tbl Srv_tbl_type;
2106
2107 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
2108 l_api_version CONSTANT NUMBER := 11.5;
2109 l_row_count NUMBER;
2110 l_msg_data VARCHAR2(2000);
2111 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2112 l_ptr Binary_Integer := 0 ;
2113 l_avail Varchar2(1);
2114 l_isrec Check_service_rec_type;
2115 l_found BOOLEAN;
2116 l_index Binary_Integer := 0 ;
2117 l_outer_loop_exit Varchar2(1) := 'N';
2118
2119 --NPALEPU 21-sep-2005 for bug # 4608694
2120 l_original_org_id NUMBER;
2121 l_original_access_mode Varchar2(1);
2122 --npalepu added on 23-dec-2005 for bug # 4897884
2123 l_org_id NUMBER;
2124 l_default_org_id hr_operating_units.organization_id%TYPE;
2125 l_default_ou_name hr_operating_units.name%TYPE;
2126 l_ou_count NUMBER;
2127 l_organization_id NUMBER;
2128 --end 4897884
2129 --END NPALEPU
2130
2131
2132 BEGIN
2133
2134 --NPALEPU 21-sep-2005 for bug # 4608694
2135
2136 /* --set org_id if it is null
2137 if okc_context.get_okc_org_id IS NULL then
2138 okc_context.set_okc_org_context;
2139 end if; */
2140
2141 --capturing the original context
2142 l_original_org_id := mo_global.get_current_org_id;
2143 l_original_access_mode := mo_global.get_access_mode();
2144
2145 IF p_org_id IS NOT NULL THEN
2146 --npalepu added on 23-dec-2005 for bug # 4897884
2147 /* okc_context.set_okc_org_context(p_org_id =>P_ORG_ID); */
2148 l_org_id := p_org_id;
2149 --end 4897884
2150 ELSIF l_original_org_id IS NOT NULL THEN
2151 --npalepu added on 23-dec-2005 for bug # 4897884
2152 /* okc_context.set_okc_org_context(p_org_id =>l_original_org_id); */
2153 l_org_id := l_original_org_id;
2154 --end 4897884
2155 ELSE
2156 --npalepu added on 23-dec-2005 for bug # 4897884
2157 /* okc_context.set_okc_org_context; */
2158 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
2159 l_org_id := l_default_org_id;
2160 --end 4897884
2161 END IF;
2162 --END NPALEPU
2163
2164 --npalepu added on 23-dec-2005 for bug # 4897884
2165 IF l_org_id IS NOT NULL AND l_org_id <> -99 THEN
2166 l_organization_id := OE_PROFILE.VALUE('OE_ORGANIZATION_ID',l_org_id);
2167 END IF;
2168 --end 4897884
2169
2170 l_party_id := Null;
2171
2172 If p_avail_service_rec.customer_id Is Not Null Then
2173
2174 Open l_party_csr (p_avail_service_rec.customer_id);
2175 Fetch l_party_csr Into l_party_id;
2176 Close l_party_csr;
2177
2178 If l_party_id IS Null Then
2179 l_return_status := 'E';
2180 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',to_char(p_avail_service_rec.customer_id));
2181 Raise G_EXCEPTION_HALT_VALIDATION;
2182 End If; /*IF <l_party_id IS Null> */
2183
2184 End If; /*IF <p_avail_service_rec.customer_id Is Not Null> */
2185
2186 IF ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) THEN
2187 --npalepu modified on 23-dec-2005 for bug # 4897884
2188 /* OPEN l_service_csr1; */
2189 OPEN l_service_csr1(l_organization_id);
2190 --end 4897884
2191 ELSE
2192 --npalepu modified on 23-dec-2005 for bug # 4897884
2193 /* OPEN l_service_csr; */
2194 OPEN l_service_csr(l_organization_id);
2195 --end 4897884
2196 End IF;/*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2197
2198 LOOP
2199 IF ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) THEN
2200 FETCH l_service_csr1 BULK COLLECT INTO l_Srv_tbl LIMIT 1000;
2201 ELSE
2202 FETCH l_service_csr BULK COLLECT INTO l_Srv_tbl LIMIT 1000;
2203 END IF; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2204
2205 IF l_Srv_tbl.count > 0 THEN
2206 l_index := l_Srv_tbl.FIRST;
2207
2208 while l_index is not null
2209 Loop
2210
2211 l_avail := 'N';
2212 l_isrec.service_item_id := l_Srv_tbl(l_index).id1;
2213 l_isrec.product_item_id := p_avail_service_rec.product_item_id;
2214 l_isrec.customer_id := p_avail_service_rec.customer_id;
2215 l_isrec.request_date := p_avail_service_rec.request_date;
2216
2217 --npalepu modified on 20-nov-2005
2218 /* Is_Service_Available
2219 (
2220 1.0,
2221 OKC_API.G_FALSE,
2222 l_row_count,
2223 l_msg_Data,
2224 l_Return_Status,
2225 l_isrec,
2226 l_Avail
2227 ); */
2228 Is_Service_Available
2229 (
2230 P_Api_Version => 1.0,
2231 P_init_msg_list => OKC_API.G_FALSE,
2232 X_msg_Count => l_row_count,
2233 X_msg_Data => l_msg_Data,
2234 X_Return_Status => l_Return_Status,
2235 p_check_service_rec => l_isrec,
2236 X_Available_YN => l_Avail
2237 );
2238 --end npalepu
2239
2240 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2241 Raise G_EXCEPTION_HALT_VALIDATION;
2242 End If;
2243
2244 IF (l_ptr >= P_MAX_ROWS_RETURNED) THEN
2245 l_outer_loop_exit := 'Y';
2246 Exit;
2247 END IF; /* IF <(l_ptr = P_MAX_ROWS_RETURNED)> */
2248
2249 If l_Avail = 'Y' Then
2250 l_ptr := l_ptr + 1;
2251 X_Orderable_Service_tbl(l_ptr).Inventory_organization_id := l_Srv_tbl(l_index).Organization_id;
2252 X_Orderable_Service_tbl(l_ptr).Service_Item_id := l_Srv_tbl(l_index).id1;
2253 X_Orderable_Service_tbl(l_ptr).Concatenated_segments := l_Srv_tbl(l_index).concatenated_segments;
2254 X_Orderable_Service_tbl(l_ptr).Description := l_Srv_tbl(l_index).Description;
2255 X_Orderable_Service_tbl(l_ptr).Primary_uom_code := l_Srv_tbl(l_index).Primary_uom_code;
2256 X_Orderable_Service_tbl(l_ptr).Serviceable_product_flag := l_Srv_tbl(l_index).Serviceable_product_flag;
2257 X_Orderable_Service_tbl(l_ptr).Service_item_flag := l_Srv_tbl(l_index).Service_item_flag;
2258 X_Orderable_Service_tbl(l_ptr).Bom_item_type := l_Srv_tbl(l_index).Bom_item_type;
2259 X_Orderable_Service_tbl(l_ptr).Item_type := l_Srv_tbl(l_index).Item_type;
2260 X_Orderable_Service_tbl(l_ptr).Service_duration := l_Srv_tbl(l_index).Service_duration;
2261 X_Orderable_Service_tbl(l_ptr).Service_duration_period_code := l_Srv_tbl(l_index).Service_duration_period_code;
2262 X_Orderable_Service_tbl(l_ptr).Shippable_item_flag := l_Srv_tbl(l_index).Shippable_item_flag;
2263 X_Orderable_Service_tbl(l_ptr).Returnable_flag := l_Srv_tbl(l_index).Returnable_flag;
2264 End If;/*IF <l_Avail = 'Y'> */
2265
2266 l_index := l_Srv_tbl.next(l_index);
2267
2268 End Loop;/* End of While Loop */
2269
2270 END IF; /*IF <l_Srv_tbl.count > 0> */
2271
2272 IF (l_outer_loop_exit = 'Y') THEN
2273 l_outer_loop_exit := 'N';
2274 Exit;
2275 END IF; /*IF <(l_outer_loop_exit = 'Y')> */
2276
2277 If ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) then
2278 Exit When l_service_csr1%NOTFound;
2279 ELSE
2280 Exit When l_service_csr%NOTFound;
2281 End If; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2282
2283 END LOOP;/*End of Outer Loop */
2284
2285 If ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) then
2286 CLOSE l_service_csr1;
2287 ELSE
2288 CLOSE l_service_csr;
2289 End If; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2290
2291 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2292
2293 --npalepu removed the code for bug # 4897884
2294 /* --NPALEPU 21-sep-2005 for bug # 4608694
2295 --Resetting to original context
2296 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2297 --END NPALEPU */
2298 --end 4897884
2299
2300 Exception
2301 When G_EXCEPTION_HALT_VALIDATION Then
2302 x_return_status := l_return_status;
2303 IF (l_service_csr1%ISOPEN) THEN
2304 CLOSE l_service_csr1;
2305 ELSIF (l_service_csr%ISOPEN) THEN
2306 CLOSE l_service_csr;
2307 END IF;
2308 --npalepu removed the code for bug # 4897884
2309 /*--NPALEPU 21-sep-2005 for bug # 4608694
2310 --Resetting to original context
2311 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2312 --END NPALEPU */
2313 --end 4897884
2314 When Others Then
2315 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2316 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2317 IF (l_service_csr1%ISOPEN) THEN
2318 CLOSE l_service_csr1;
2319 ELSIF (l_service_csr%ISOPEN) THEN
2320 CLOSE l_service_csr;
2321 END IF;
2322 --npalepu removed the code for bug # 4897884
2323 /* --NPALEPU 21-sep-2005 for bug # 4608694
2324 --Resetting to original context
2325 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2326 --END NPALEPU */
2327 --end 4897884
2328 END Available_Services;
2329 --END NPALEPU
2330
2331 END OKS_OMINT_PUB;