[Home] [Help]
PACKAGE BODY: APPS.OKS_OMINT_PUB
Source
1 Package Body OKS_OMINT_PUB AS
2 /* $Header: OKSPOMIB.pls 120.25.12020000.2 2013/02/01 08:46:30 mchandak 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 /* Modified cursor for Bug#15885433 */
1405 Cursor l_Oline_csr Is
1406 Select service_reference_type_code
1407 ,service_reference_line_id
1408 ,service_duration
1409 ,service_period
1410 ,fulfillment_date
1411 ,org_id
1412 ,ship_from_org_id
1413 ,sold_from_org_id
1414 From oe_order_lines_all
1415 Where line_id = p_order_line_id;
1416
1417 Cursor l_get_warr_dates_csr(p_cp_id Number) IS
1418 Select max(ol.end_date)
1419 From okc_k_items ot, okc_k_lines_b ol
1420 Where ot.object1_id1 = to_char(p_cp_id) -- Bug Fix #5011519
1421 And ol.id = ot.cle_id
1422 And ol.lse_id = 18 ;
1423
1424 /* Modified cursor for Bug#15885433 */
1425 Cursor l_cp_csr(p_line_id Number) Is
1426 /*Select csi.instance_id
1427 ,csi.install_date
1428 ,ol.Actual_shipment_date
1429 ,ol.schedule_ship_date
1430 From csi_item_instances csi
1431 ,oe_order_lines_all ol
1432 Where ol.line_id = csi.last_oe_order_line_id
1433 And ol.inventory_item_id = csi.inventory_item_id
1434 And ol.line_id = p_line_id; */
1435 SELECT csi.instance_id,
1436 csi.install_date,
1437 ol.actual_shipment_date,
1438 ol.schedule_ship_date,
1439 mtl.service_starting_delay
1440 FROM csi_item_instances csi,
1441 oe_order_lines_all ol,
1442 okx_system_items_v mtl
1443 WHERE csi.inventory_item_id = mtl.id1
1444 AND ol.inventory_item_id = csi.inventory_item_id
1445 AND mtl.id2 = OKC_CONTEXT.GET_OKC_ORGANIZATION_ID
1446 AND csi.last_oe_order_line_id = ol.line_id
1447 AND ol.line_id = p_line_id;
1448
1449 Cursor l_product_csr(p_cp_id Number) Is
1450 Select csi.install_date
1451 From csi_item_instances csi
1452 Where csi.instance_id = p_cp_id;
1453
1454 /* Added cursor for Bug#15885433*/
1455 CURSOR l_organization_csr(p_org_id Number) Is
1456 SELECT master_organization_id
1457 FROM oe_system_parameters_all
1458 WHERE org_id = p_org_id;
1459
1460 l_organization_id NUMBER;
1461 /* End of code added for Bug#15885433*/
1462
1463 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1464 l_cp_rec l_cp_csr%rowtype;
1465 l_line_rec l_oline_csr%rowtype;
1466 l_install_date Date;
1467 l_ship_date Date;
1468 l_warend_date Date;
1469
1470
1471
1472 Begin
1473
1474 l_line_rec := Null;
1475 l_cp_rec := Null;
1476
1477 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1478 x_return_status := l_return_status;
1479 Open l_oline_csr;
1480 Fetch l_oline_csr into l_line_rec;
1481 Close l_oline_csr;
1482
1483 /* Added for Bug#15885433*/
1484 If Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'IB' OR Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') Is NULL Then
1485 Okc_context.set_okc_org_context (l_line_rec.org_id, l_line_rec.ship_from_org_id);
1486 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'MO' Then
1487 Okc_context.set_okc_org_context (l_line_rec.org_id, NULL );
1488 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SO' Then
1489 l_organization_id := Null;
1490 If l_line_rec.sold_from_org_id Is Not Null Then
1491 Open l_organization_csr(l_line_rec.sold_from_org_id);
1492 Fetch l_organization_csr into l_organization_id;
1493 Close l_organization_csr;
1494 Else
1495 l_organization_id := Null;
1496 End If;
1497 Okc_context.set_okc_org_context (l_line_rec.org_id, l_organization_id);
1498 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SH' Then
1499 Okc_context.set_okc_org_context (l_line_rec.org_id, l_line_rec.ship_from_org_id);
1500 End If;
1501 /* End of Code Added for Bug#15885433*/
1502
1503 If l_line_rec.Service_reference_type_code = 'ORDER' Then
1504 Open l_cp_csr(l_line_rec.service_reference_line_id);
1505 Fetch l_cp_csr into l_cp_rec;
1506 Close l_cp_csr;
1507
1508 If l_cp_rec.instance_id Is Null Then
1509 l_return_status := 'E';
1510 OKC_API.set_message(G_APP_NAME,'OKS_NULL_SDT','LINE_ID',p_order_line_id);
1511 Raise G_EXCEPTION_HALT_VALIDATION;
1512 End If;
1513 l_warend_date := Null;
1514 Open l_get_warr_dates_csr(l_cp_rec.instance_id);
1515 fetch l_get_warr_dates_csr into l_warend_date;
1516 Close l_get_warr_dates_csr;
1517
1518 If l_warend_date is Not null Then
1519 X_start_date := trunc(l_warend_date) + 1;
1520
1521 Else
1522 l_ship_date := nvl(l_cp_rec.actual_shipment_date,l_cp_rec.schedule_ship_date);
1523 /* Modified below code line for Bug#15885433 */
1524 X_start_date := Trunc(Nvl(l_cp_rec.install_date, l_ship_date + Nvl(l_cp_rec.service_starting_delay,0)));
1525 End If;
1526
1527
1528 ElsIf l_line_rec.Service_reference_type_code = 'CUSTOMER_PRODUCT' Then
1529 l_warend_date := Null;
1530 Open l_get_warr_dates_csr(l_line_rec.service_reference_line_id);
1531 Fetch l_get_warr_dates_csr into l_warend_date;
1532 Close l_get_warr_dates_csr;
1533
1534 If l_warend_date is Not null Then
1535 X_start_date := trunc(l_warend_date) + 1;
1536
1537 Else
1538 Open l_product_csr(l_line_rec.service_reference_line_id);
1539 Fetch l_product_csr into l_install_date;
1540 Close l_product_csr;
1541
1542
1543 X_start_date := Trunc(NVL(l_install_date,l_line_rec.fulfillment_date));
1544 End If;
1545
1546 End If;
1547 If X_start_date Is Null Then
1548 l_return_status := 'E';
1549 OKC_API.set_message(G_APP_NAME,'OKS_NULL_SDT','LINE_ID',p_order_line_id);
1550 Raise G_EXCEPTION_HALT_VALIDATION;
1551 End If;
1552 X_end_date := okc_time_util_pub.get_enddate
1553 ( X_Start_Date
1554 ,l_line_rec.service_period
1555 ,l_line_rec.service_duration
1556 );
1557
1558 x_return_status := l_return_status;
1559
1560 Exception
1561 When G_EXCEPTION_HALT_VALIDATION Then
1562 x_return_status := l_return_status;
1563 When Others Then
1564 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1565 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1566
1567
1568
1569 End;
1570
1571 -- Added for ASO Queue Replacement
1572
1573 PROCEDURE Interface_Service_Order_Lines
1574 (p_Service_Order_Lines IN Service_Order_Lines_TblType
1575 ,x_Return_Status OUT NOCOPY VARCHAR2
1576 ,x_Error_Message OUT NOCOPY VARCHAR2)
1577 IS
1578
1579 TYPE Num_TblType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1580
1581 l_Order_Header_ID_Tbl Num_TblType;
1582 l_Order_Line_ID_Tbl Num_TblType;
1583 l_Order_Number_Tbl Num_TblType;
1584
1585 l_Tbl_Idx NUMBER;
1586 l_SrvOrdLine_Idx NUMBER;
1587 l_SrvOrdLine_Idx_FIRST NUMBER;
1588 l_SrvOrdLine_Idx_LAST NUMBER;
1589
1590 BEGIN
1591
1592 l_Tbl_Idx := 0;
1593 l_SrvOrdLine_Idx := p_Service_Order_Lines.FIRST;
1594
1595 WHILE l_SrvOrdLine_Idx IS NOT NULL LOOP
1596
1597 l_Tbl_Idx := l_Tbl_Idx + 1;
1598
1599 l_Order_Header_ID_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Header_ID;
1600 l_Order_Line_ID_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Line_ID;
1601 l_Order_Number_Tbl(l_Tbl_Idx) := p_Service_Order_Lines(l_SrvOrdLine_Idx).Order_Number;
1602
1603 l_SrvOrdLine_Idx := p_Service_Order_Lines.NEXT(l_SrvOrdLine_Idx);
1604
1605 END LOOP;
1606
1607 --
1608
1609 l_SrvOrdLine_Idx_FIRST := l_Order_Line_ID_Tbl.FIRST;
1610 l_SrvOrdLine_Idx_LAST := l_Order_Line_ID_Tbl.LAST;
1611
1612 --
1613
1614 FORALL f_SrvOrdLine_Idx IN l_SrvOrdLine_Idx_FIRST .. l_SrvOrdLine_Idx_LAST
1615 INSERT INTO OKS_REPROCESSING
1616 (ID
1617 ,ORDER_ID
1618 ,ORDER_LINE_ID
1619 ,CONTRACT_ID
1620 ,CONTRACT_LINE_ID
1621 ,SUBLINE_ID
1622 ,ERROR_TEXT
1623 ,SUCCESS_FLAG
1624 ,SOURCE_FLAG
1625 ,CONC_REQUEST_ID
1626 ,CREATED_BY
1627 ,CREATION_DATE
1628 ,LAST_UPDATED_BY
1629 ,LAST_UPDATE_DATE
1630 ,LAST_UPDATE_LOGIN
1631 ,OBJECT_VERSION_NUMBER
1632 ,SECURITY_GROUP_ID
1633 ,REPROCESS_YN
1634 ,ORDER_NUMBER )
1635 VALUES
1636 (TO_NUMBER(RAWTOHEX(SYS_GUID()),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
1637 ,l_Order_Header_ID_Tbl(f_SrvOrdLine_Idx)
1638 ,l_Order_Line_ID_Tbl(f_SrvOrdLine_Idx)
1639 ,NULL
1640 ,NULL
1641 ,NULL
1642 ,NULL
1643 ,'N'
1644 ,'ASO'
1645 ,NULL
1646 ,FND_GLOBAL.USER_ID
1647 ,SYSDATE
1648 ,FND_GLOBAL.USER_ID
1649 ,SYSDATE
1650 ,FND_GLOBAL.LOGIN_ID
1651 ,1
1652 ,NULL
1653 ,'Y'
1654 ,l_Order_Number_Tbl(f_SrvOrdLine_idx));
1655
1656 x_Return_Status := 'S';
1657
1658 EXCEPTION
1659
1660 WHEN OTHERS THEN
1661 x_Return_Status := 'U';
1662 x_Error_Message := 'Unexpected Error :'||' ('||SQLCODE||') '||SUBSTR(SQLERRM,1,170);
1663
1664 END Interface_Service_Order_Lines;
1665
1666 /*
1667 Important Note regarding bug#5330614 , Dated 14-JUN-2006
1668 This function OKS_OMINT_PUB.Get_quantity is called by Pricing engine to prorate the price breaks if
1669 the prorate option is set to 'ALL' for the usage. This function should not be changed and should remain
1670 as is as it is needed for proration of price breaks.
1671 OKS_OMINT_PUB.Get_target_duration function is called by pricing engine to determine the
1672 duration between pair of passed service dates and in case of usage, this function will return NULL.
1673 */
1674
1675 FUNCTION get_quantity(p_start_date IN DATE,
1676 p_end_date IN DATE,
1677 p_source_uom IN VARCHAR2 DEFAULT NULL,
1678 p_org_id IN VARCHAR2 DEFAULT NULL)
1679 return number
1680 as
1681
1682 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
1683 is
1684 SELECT 1
1685 FROM MTL_UNITS_OF_MEASURE_TL
1686 WHERE uom_code = p_uom_code
1687 AND LANGUAGE = USERENV('LANG');
1688 cr_validate_uom cs_validate_uom%ROWTYPE;
1689
1690
1691 --Added for bug# 5623790 by sjanakir
1692 CURSOR l_lse_id_csr(p_id IN NUMBER) IS
1693 SELECT lse_id
1694 FROM OKC_K_LINES_B
1695 WHERE id = p_id;
1696 --Addition Ends
1697 l_target_qty NUMBER;
1698 l_source_uom varchar2(30);
1699 l_price_uom varchar2(30);
1700 l_period_type varchar2(30);
1701 l_period_start varchar2(30);
1702 l_chr_id NUMBER;
1703 l_status varchar2(80);
1704 --Added for bug# 5623790 by sjanakir
1705 l_cle_id NUMBER;
1706 l_lse_id NUMBER;
1707 --Addition Ends
1708
1709 invalid_date_exception EXCEPTION;
1710 invalid_uom_exception EXCEPTION;
1711
1712 BEGIN
1713 IF p_source_uom Is Null Then
1714 l_source_uom := OKS_TIME_MEASURES_PUB.get_uom_code('MONTH',1);
1715 Else
1716 open cs_validate_uom(p_source_uom);
1717 fetch cs_validate_uom into cr_validate_uom;
1718
1719 IF cs_validate_uom%NOTFOUND
1720 THEN
1721 RAISE INVALID_UOM_EXCEPTION;
1722 END IF;
1723
1724 l_source_uom := p_source_uom;
1725 close cs_validate_uom;
1726 END IF;
1727
1728 IF (p_start_date IS NULL)OR(p_end_date IS NULL)OR(p_start_date > p_end_date)
1729 THEN
1730 RAISE INVALID_DATE_EXCEPTION;
1731 END IF;
1732
1733 --OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID is set by QP_PKG before calling pricing engine
1734
1735 l_chr_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID;
1736
1737 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1738 p_org_id => p_org_id,
1739 x_period_type => l_period_type,
1740 x_period_start => l_period_start,
1741 x_price_uom => l_price_uom,
1742 x_return_status => l_status);
1743 --Added for bug# 5623790 by sjanakir
1744 l_cle_id := OKS_QPATTRIB_PVT.G_CONTRACT_LINREC.TOP_LINE_ID;
1745
1746 Open l_lse_id_csr(l_cle_id);
1747 Fetch l_lse_id_csr into l_lse_id;
1748 Close l_lse_id_csr;
1749
1750 IF l_lse_id = 12 THEN
1751 l_period_start := 'SERVICE';
1752 END IF;
1753 --Addition Ends
1754
1755
1756 IF l_status = OKC_API.G_RET_STS_ERROR
1757 THEN
1758 return 0;
1759 END IF;
1760
1761 IF l_chr_id IS NULL
1762 THEN
1763 l_period_start := 'SERVICE'; --one time billing for OM/ASO case
1764 END IF;
1765
1766 l_target_qty := OKS_TIME_MEASURES_PUB.get_quantity(p_start_date => p_start_date,
1767 p_end_date => p_end_date,
1768 p_source_uom => p_source_uom,
1769 p_period_type => l_period_type,
1770 p_period_start => l_period_start);
1771
1772 return(l_target_qty);
1773
1774 EXCEPTION
1775 WHEN
1776 INVALID_UOM_EXCEPTION
1777 THEN
1778 OKC_API.set_message('OKS','OKS_INVD_UOM_CODE');
1779 close cs_validate_uom;
1780 return 0;
1781 WHEN
1782 INVALID_DATE_EXCEPTION
1783 THEN
1784 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
1785 return 0;
1786 WHEN OTHERS THEN
1787 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1788 return 0;
1789
1790 END get_quantity;
1791
1792 --21-DEC-2005 mchoudha
1793 /* NEW Function added for R12 enhancement partialperiods. This function will replace
1794 the use of OKS_TIME_MEASURES_PUB.get_quantity by Pricing, and will also replace the use
1795 of inventory uom conversion API by Order Management and Quoting/Sales Online.
1796 */
1797
1798 /*
1799 Important Note regarding bug#5330614 dated 14-JUN-2006 :
1800 OKS_OMINT_PUB.Get_target_duration function is called by pricing engine to determine the
1801 duration between pair of passed service dates and in case of usage this function will return NULL.
1802 The function OKS_OMINT_PUB.Get_quantity is called by Pricing engine to prorate the price breaks if
1803 the prorate option is set to 'ALL' for the usage.
1804 */
1805 FUNCTION get_target_duration ( p_start_date IN DATE DEFAULT NULL,
1806 p_end_date IN DATE DEFAULT NULL,
1807 p_source_uom IN VARCHAR2 DEFAULT NULL,
1808 p_source_duration IN NUMBER DEFAULT NULL,
1809 p_target_uom IN VARCHAR2 DEFAULT NULL,/*Default Month*/
1810 p_org_id IN NUMBER DEFAULT NULL)
1811 return number
1812 AS
1813
1814 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
1815 is
1816 SELECT 1
1817 FROM MTL_UNITS_OF_MEASURE_TL
1818 WHERE uom_code = p_uom_code
1819 AND LANGUAGE = USERENV('LANG');
1820 cr_validate_uom cs_validate_uom%ROWTYPE;
1821
1822 --Added for bug#5330614
1823 Cursor l_lse_id_csr(p_id IN NUMBER) IS
1824 SELECT lse_id
1825 FROM OKC_K_LINES_B
1826 WHERE id = p_id;
1827
1828 l_target_qty NUMBER;
1829 l_target_uom varchar2(30);
1830 l_price_uom varchar2(30);
1831 l_period_type varchar2(30);
1832 l_period_start varchar2(30);
1833 l_chr_id NUMBER;
1834 l_cle_id NUMBER;
1835 l_top_line_id NUMBER;
1836 l_source_uom_quantity NUMBER;
1837 l_source_tce_code VARCHAR2(30);
1838 l_target_uom_quantity NUMBER;
1839 l_target_tce_code VARCHAR2(30);
1840 l_return_status VARCHAR2(1);
1841 l_lse_id NUMBER;
1842 invalid_date_exception EXCEPTION;
1843 invalid_uom_exception EXCEPTION;
1844 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
1845
1846 /* CURSOR om_period_csr(p_id IN NUMBER) IS
1847 select service_period,service_duration
1848 from okc_k_lines_b subline,
1849 okc_k_rel_objs rel,
1850 oe_order_lines_all oel
1851 where subline.cle_id = p_id
1852 and rel.cle_id = subline.id
1853 and oel.line_id = rel.object1_id1;*/
1854
1855 BEGIN
1856
1857 --check for target uom passed if null then default to month
1858 IF p_target_uom Is Null Then
1859 l_target_uom := OKS_TIME_MEASURES_PUB.get_uom_code('MONTH',1);
1860 Else
1861 --validate the target uom passed
1862 open cs_validate_uom(p_target_uom);
1863 fetch cs_validate_uom into cr_validate_uom;
1864
1865 IF cs_validate_uom%NOTFOUND
1866 THEN
1867 RAISE INVALID_UOM_EXCEPTION;
1868 END IF;
1869
1870 l_target_uom := p_target_uom;
1871 close cs_validate_uom;
1872 END IF;
1873
1874 --fetching the contract id
1875 l_chr_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID;
1876
1877 --Added for bug#5330614
1878 l_cle_id := OKS_QPATTRIB_PVT.G_CONTRACT_LINREC.TOP_LINE_ID;
1879
1880 Open l_lse_id_csr(l_cle_id);
1881 Fetch l_lse_id_csr into l_lse_id;
1882 Close l_lse_id_csr;
1883
1884 IF l_lse_id = 12 THEN
1885 return NULL;
1886 END IF;
1887 --bug#5330614
1888
1889 --l_top_line_id := OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.TOP_LINE_ID;
1890 --l_service_period := NULL;
1891 --Call from OKS to Price Engine
1892 /*IF l_top_line_id IS NOT NULL THEN
1893 Open om_period_csr(l_top_line_id);
1894 Fetch om_period_csr into l_service_period,l_service_duration;
1895 Close om_period_csr;
1896 END IF; */
1897
1898 --22-MAR-2006 mchoudha Changes for Partial periods CR3
1899 IF l_chr_id IS NOT NULL OR --price engine call from OKS
1900 (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
1901 THEN
1902
1903 IF (p_start_date IS NOT NULL) AND (p_end_date IS NOT NULL)
1904 THEN
1905
1906 IF (p_start_date > p_end_date) THEN
1907 RAISE INVALID_DATE_EXCEPTION;
1908 END IF;
1909
1910
1911 --OKS_QPATTRIB_PVT.G_CONTRACT_HDRREC.CHR_ID is set by QP_PKG before calling pricing engine
1912
1913 --get the partial period attributes stamped on the contract
1914 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1915 p_org_id => p_org_id,
1916 x_period_type => l_period_type,
1917 x_period_start => l_period_start,
1918 x_price_uom => l_price_uom,
1919 x_return_status => l_return_status);
1920 IF l_return_status = OKC_API.G_RET_STS_ERROR
1921 THEN
1922 return 0;
1923 END IF;
1924
1925 --mchoudha added l_chr_id is NULL condition for bug#5182587
1926 --so that the assignment within IF condition gets executed only for OM contracts
1927 IF l_period_start IS NOT NULL AND l_chr_id IS NULL THEN
1928 l_period_start := 'SERVICE';
1929 END IF;
1930
1931 l_target_qty := OKS_TIME_MEASURES_PUB.get_quantity(p_start_date => p_start_date,
1932 p_end_date => p_end_date,
1933 p_source_uom => l_target_uom,
1934 p_period_type => l_period_type,
1935 p_period_start => l_period_start);
1936 ELSE
1937 return 0;
1938 END IF;
1939 /*ELSIF l_chr_id IS NOT NULL AND l_service_period IS NOT NULL --OKS price engine call for OM/ASO/Istore case
1940 NULL;*/
1941
1942 ELSE --OM/ASO/Istore case
1943
1944 l_period_start := 'SERVICE';
1945 l_period_type := 'FIXED';
1946
1947 IF (p_source_uom is NOT NULL) AND (p_source_duration is NOT NULL) THEN
1948
1949 --Get the seeded timeunit for the source uom
1950 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
1951 (p_timeunit => p_source_uom,
1952 x_return_status => l_return_status,
1953 x_quantity => l_source_uom_quantity ,
1954 x_timeunit => l_source_tce_code);
1955 IF l_return_status <> 'S' THEN
1956 RAISE G_EXCEPTION_HALT_VALIDATION;
1957 END IF;
1958 --Get the seeded timeunit for the target uom
1959 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
1960 (p_timeunit => l_target_uom,
1961 x_return_status => l_return_status,
1962 x_quantity => l_target_uom_quantity ,
1963 x_timeunit => l_target_tce_code);
1964 IF l_return_status <> 'S' THEN
1965 RAISE G_EXCEPTION_HALT_VALIDATION;
1966 END IF;
1967
1968 /*Conversion logic using fixed 30 days in a month*/
1969
1970 --When source uom is seeded in terms of DAY
1971 IF l_source_tce_code ='DAY' THEN
1972 IF l_target_tce_code ='YEAR' THEN
1973 l_target_qty:= p_source_duration*(l_source_uom_quantity/(360*l_target_uom_quantity));
1974 ELSIF l_target_tce_code ='MONTH' THEN
1975 l_target_qty:= p_source_duration*(l_source_uom_quantity/(30*l_target_uom_quantity));
1976 ELSIF l_target_tce_code ='DAY' THEN
1977 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1978 END IF;
1979 --When source uom is seeded in terms of MONTH
1980 ELSIF l_source_tce_code ='MONTH' THEN
1981 IF l_target_tce_code ='YEAR' THEN
1982 l_target_qty:= p_source_duration*(l_source_uom_quantity/(12*l_target_uom_quantity));
1983 ELSIF l_target_tce_code ='MONTH' THEN
1984 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1985 ELSIF l_target_tce_code ='DAY' THEN
1986 l_target_qty:= p_source_duration*((l_source_uom_quantity*30)/l_target_uom_quantity);
1987 END IF;
1988 --When source uom is seeded in terms of YEAR
1989 ELSIF l_source_tce_code ='YEAR' THEN
1990 IF l_target_tce_code ='YEAR' THEN
1991 l_target_qty:= p_source_duration*(l_source_uom_quantity/l_target_uom_quantity);
1992 ELSIF l_target_tce_code ='MONTH' THEN
1993 l_target_qty:= p_source_duration*((l_source_uom_quantity*12)/l_target_uom_quantity);
1994 ELSIF l_target_tce_code ='DAY' THEN
1995 l_target_qty:= p_source_duration*((l_source_uom_quantity*360)/l_target_uom_quantity);
1996 END IF;
1997
1998 END IF;
1999 ELSE --none of two sets of parameters are passed so set the error message and return 0
2000 OKC_API.set_message('OKS','OKS_INVD_DURATION');
2001 return 0;
2002 END IF;
2003 END IF;
2004 return(l_target_qty);
2005
2006 EXCEPTION
2007 WHEN
2008 INVALID_UOM_EXCEPTION
2009 THEN
2010 OKC_API.set_message('OKS','OKS_INVD_UOM_CODE');
2011 close cs_validate_uom;
2012 return 0;
2013 WHEN
2014 G_EXCEPTION_HALT_VALIDATION
2015 THEN
2016 return 0;
2017 WHEN
2018 INVALID_DATE_EXCEPTION
2019 THEN
2020 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
2021 return 0;
2022 WHEN OTHERS THEN
2023 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2024 return 0;
2025
2026 END get_target_duration;
2027
2028 --NPALEPU
2029 --23-JUN-2005
2030 --SERVICE AVAILABILITY API ENHANCEMENT(ER 3680488)
2031 --ADDED NEW OVERLOADED API "Available_Services"
2032
2033 PROCEDURE Available_Services
2034 (
2035 P_Api_Version IN NUMBER,
2036 P_init_msg_list IN VARCHAR2 Default OKC_API.G_FALSE,
2037 P_search_input IN VARCHAR2 Default OKC_API.G_MISS_CHAR,
2038 P_MAX_ROWS_RETURNED IN NUMBER Default 200,
2039 X_msg_Count OUT NOCOPY NUMBER,
2040 X_msg_Data OUT NOCOPY VARCHAR2,
2041 X_Return_Status OUT NOCOPY VARCHAR2,
2042 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
2043 X_Orderable_Service_tbl OUT NOCOPY NEW_ORDER_SERVICE_TBL_TYPE,
2044 --NPALEPU added on 21-sep-2005 for bug # 4608694
2045 P_ORG_ID IN NUMBER Default NULL
2046 --END NPALEPU
2047 )
2048 IS
2049
2050 --cursor to fetch the services when P_search_input value is not null
2051 --npalepu 23-dec-2005 for bug # 4897884 removed MTL_UNITS_OF_MEASURE_VL uom,
2052 -- MTL_UNITS_OF_MEASURE_VL srvuom,FND_LOOKUP_VALUES fnd,FND_LOOKUP_VALUES fnd1 tables
2053 Cursor l_service_csr(v_organization_id IN NUMBER) Is
2054 SELECT
2055 kfv.organization_id Organization_id,
2056 kfv.inventory_item_id id1,
2057 kfv.concatenated_segments Concatenated_segments,
2058 kfv.description Description,
2059 kfv.primary_uom_code Primary_uom_code,
2060 uom.description Primary_uom_description,
2061 kfv.Serviceable_product_flag Serviceable_product_flag,
2062 kfv.service_item_flag Service_item_flag,
2063 kfv.bom_item_type Bom_item_type,
2064 fnd.meaning Bom_item_type_meaning,
2065 kfv.item_type Item_type,
2066 fnd1.meaning Item_type_meaning,
2067 kfv.service_duration Service_duration,
2068 kfv.service_duration_period_code Service_duration_period_code,
2069 srvuom.description Service_duration_period_mean,
2070 kfv.shippable_item_flag shippable_item_flag,
2071 kfv.returnable_flag Returnable_flag
2072 FROM MTL_SYSTEM_ITEMS_B_KFV kfv,
2073 MTL_UNITS_OF_MEASURE_VL uom,
2074 MTL_UNITS_OF_MEASURE_VL srvuom,
2075 FND_LOOKUP_VALUES fnd,
2076 FND_LOOKUP_VALUES fnd1
2077 WHERE kfv.vendor_warranty_flag = 'N'
2078 AND kfv.service_item_flag = 'Y'
2079 AND kfv.customer_order_enabled_flag = 'Y'
2080 AND kfv.enabled_flag = 'Y'
2081 --npalepu modified on 23-dec-2005 for bug # 4897884
2082 /* AND kfv.organization_id = okc_context.get_okc_organization_id */
2083 AND kfv.organization_id = v_organization_id
2084 --end npalepu
2085 --NPALEPU,11-AUG-2005
2086 --Used UPPER Function as Requested by Quoting Team.
2087 --AND (kfv.concatenated_segments LIKE P_search_input OR kfv.description LIKE P_search_input )
2088 AND (UPPER(kfv.concatenated_segments) LIKE UPPER(P_search_input) OR UPPER(kfv.description) LIKE UPPER(P_search_input) )
2089 --END NPALEPU
2090 AND uom.uom_code = primary_uom_code
2091 AND srvuom.uom_code = service_duration_period_code
2092 AND fnd.lookup_type = 'BOM_ITEM_TYPE'
2093 AND fnd.lookup_code = bom_item_type
2094 AND fnd.language = USERENV('LANG')
2095 AND fnd1.lookup_type = 'ITEM_TYPE'
2096 AND fnd1.lookup_code = item_type
2097 AND fnd1.language = USERENV('LANG')
2098 ORDER BY CONCATENATED_SEGMENTS;
2099
2100 --cursor to fetch the services when P_search_input value is null
2101 --npalepu 23-dec-2005 for bug # 4897884 removed MTL_UNITS_OF_MEASURE_VL uom,
2102 -- MTL_UNITS_OF_MEASURE_VL srvuom,FND_LOOKUP_VALUES fnd,FND_LOOKUP_VALUES fnd1 tables
2103 Cursor l_service_csr1(v_organization_id IN NUMBER) Is
2104 SELECT
2105 kfv.organization_id Organization_id,
2106 kfv.inventory_item_id id1,
2107 kfv.concatenated_segments Concatenated_segments,
2108 kfv.description Description,
2109 kfv.primary_uom_code Primary_uom_code,
2110 uom.description Primary_uom_description,
2111 kfv.Serviceable_product_flag Serviceable_product_flag,
2112 kfv.service_item_flag Service_item_flag,
2113 kfv.bom_item_type Bom_item_type,
2114 fnd.meaning Bom_item_type_meaning,
2115 kfv.item_type Item_type,
2116 fnd1.meaning Item_type_meaning,
2117 kfv.service_duration Service_duration,
2118 kfv.service_duration_period_code Service_duration_period_code,
2119 srvuom.description Service_duration_period_mean,
2120 kfv.shippable_item_flag shippable_item_flag,
2121 kfv.returnable_flag Returnable_flag
2122 FROM MTL_SYSTEM_ITEMS_B_KFV kfv,
2123 MTL_UNITS_OF_MEASURE_VL uom,
2124 MTL_UNITS_OF_MEASURE_VL srvuom,
2125 FND_LOOKUP_VALUES fnd,
2126 FND_LOOKUP_VALUES fnd1
2127 WHERE kfv.vendor_warranty_flag = 'N'
2128 AND kfv.service_item_flag = 'Y'
2129 AND kfv.customer_order_enabled_flag = 'Y'
2130 AND kfv.enabled_flag = 'Y'
2131 --npalepu modified on 23-dec-2005 for bug # 4897884
2132 /* AND kfv.organization_id = okc_context.get_okc_organization_id */
2133 AND kfv.organization_id = v_organization_id
2134 --end npalepu
2135 AND uom.uom_code = primary_uom_code
2136 AND srvuom.uom_code = service_duration_period_code
2137 AND fnd.lookup_type = 'BOM_ITEM_TYPE'
2138 AND fnd.lookup_code = bom_item_type
2139 AND fnd.language = USERENV('LANG')
2140 AND fnd1.lookup_type = 'ITEM_TYPE'
2141 AND fnd1.lookup_code = item_type
2142 AND fnd1.language = USERENV('LANG')
2143 ORDER BY CONCATENATED_SEGMENTS;
2144
2145 Cursor l_party_csr (l_custid Number) Is
2146 Select Party_Id From OKX_CUSTOMER_ACCOUNTS_V
2147 Where Id1 = l_custid;
2148
2149 l_party_id Number;
2150
2151 TYPE Srv_tbl_type IS TABLE OF l_service_csr%ROWTYPE INDEX BY BINARY_INTEGER;
2152 l_Srv_tbl Srv_tbl_type;
2153
2154 l_api_name CONSTANT VARCHAR2(30) := 'GET_DURATION';
2155 l_api_version CONSTANT NUMBER := 11.5;
2156 l_row_count NUMBER;
2157 l_msg_data VARCHAR2(2000);
2158 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2159 l_ptr Binary_Integer := 0 ;
2160 l_avail Varchar2(1);
2161 l_isrec Check_service_rec_type;
2162 l_found BOOLEAN;
2163 l_index Binary_Integer := 0 ;
2164 l_outer_loop_exit Varchar2(1) := 'N';
2165
2166 --NPALEPU 21-sep-2005 for bug # 4608694
2167 l_original_org_id NUMBER;
2168 l_original_access_mode Varchar2(1);
2169 --npalepu added on 23-dec-2005 for bug # 4897884
2170 l_org_id NUMBER;
2171 l_default_org_id hr_operating_units.organization_id%TYPE;
2172 l_default_ou_name hr_operating_units.name%TYPE;
2173 l_ou_count NUMBER;
2174 l_organization_id NUMBER;
2175 --end 4897884
2176 --END NPALEPU
2177
2178
2179 BEGIN
2180
2181 --NPALEPU 21-sep-2005 for bug # 4608694
2182
2183 /* --set org_id if it is null
2184 if okc_context.get_okc_org_id IS NULL then
2185 okc_context.set_okc_org_context;
2186 end if; */
2187
2188 --capturing the original context
2189 l_original_org_id := mo_global.get_current_org_id;
2190 l_original_access_mode := mo_global.get_access_mode();
2191
2192 IF p_org_id IS NOT NULL THEN
2193 --npalepu added on 23-dec-2005 for bug # 4897884
2194 /* okc_context.set_okc_org_context(p_org_id =>P_ORG_ID); */
2195 l_org_id := p_org_id;
2196 --end 4897884
2197 ELSIF l_original_org_id IS NOT NULL THEN
2198 --npalepu added on 23-dec-2005 for bug # 4897884
2199 /* okc_context.set_okc_org_context(p_org_id =>l_original_org_id); */
2200 l_org_id := l_original_org_id;
2201 --end 4897884
2202 ELSE
2203 --npalepu added on 23-dec-2005 for bug # 4897884
2204 /* okc_context.set_okc_org_context; */
2205 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
2206 l_org_id := l_default_org_id;
2207 --end 4897884
2208 END IF;
2209 --END NPALEPU
2210
2211 --npalepu added on 23-dec-2005 for bug # 4897884
2212 IF l_org_id IS NOT NULL AND l_org_id <> -99 THEN
2213 l_organization_id := OE_PROFILE.VALUE('OE_ORGANIZATION_ID',l_org_id);
2214 END IF;
2215 --end 4897884
2216
2217 l_party_id := Null;
2218
2219 If p_avail_service_rec.customer_id Is Not Null Then
2220
2221 Open l_party_csr (p_avail_service_rec.customer_id);
2222 Fetch l_party_csr Into l_party_id;
2223 Close l_party_csr;
2224
2225 If l_party_id IS Null Then
2226 l_return_status := 'E';
2227 OKC_API.set_message(G_APP_NAME,'OKS_PARTY_ID_NOT_FOUND','CUSTOMER_ID',to_char(p_avail_service_rec.customer_id));
2228 Raise G_EXCEPTION_HALT_VALIDATION;
2229 End If; /*IF <l_party_id IS Null> */
2230
2231 End If; /*IF <p_avail_service_rec.customer_id Is Not Null> */
2232
2233 IF ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) THEN
2234 --npalepu modified on 23-dec-2005 for bug # 4897884
2235 /* OPEN l_service_csr1; */
2236 OPEN l_service_csr1(l_organization_id);
2237 --end 4897884
2238 ELSE
2239 --npalepu modified on 23-dec-2005 for bug # 4897884
2240 /* OPEN l_service_csr; */
2241 OPEN l_service_csr(l_organization_id);
2242 --end 4897884
2243 End IF;/*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2244
2245 LOOP
2246 IF ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) THEN
2247 FETCH l_service_csr1 BULK COLLECT INTO l_Srv_tbl LIMIT 1000;
2248 ELSE
2249 FETCH l_service_csr BULK COLLECT INTO l_Srv_tbl LIMIT 1000;
2250 END IF; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2251
2252 IF l_Srv_tbl.count > 0 THEN
2253 l_index := l_Srv_tbl.FIRST;
2254
2255 while l_index is not null
2256 Loop
2257
2258 l_avail := 'N';
2259 l_isrec.service_item_id := l_Srv_tbl(l_index).id1;
2260 l_isrec.product_item_id := p_avail_service_rec.product_item_id;
2261 l_isrec.customer_id := p_avail_service_rec.customer_id;
2262 l_isrec.request_date := p_avail_service_rec.request_date;
2263
2264 --npalepu modified on 20-nov-2005
2265 /* Is_Service_Available
2266 (
2267 1.0,
2268 OKC_API.G_FALSE,
2269 l_row_count,
2270 l_msg_Data,
2271 l_Return_Status,
2272 l_isrec,
2273 l_Avail
2274 ); */
2275 Is_Service_Available
2276 (
2277 P_Api_Version => 1.0,
2278 P_init_msg_list => OKC_API.G_FALSE,
2279 X_msg_Count => l_row_count,
2280 X_msg_Data => l_msg_Data,
2281 X_Return_Status => l_Return_Status,
2282 p_check_service_rec => l_isrec,
2283 X_Available_YN => l_Avail
2284 );
2285 --end npalepu
2286
2287 If not l_return_status = OKC_API.G_RET_STS_SUCCESS Then
2288 Raise G_EXCEPTION_HALT_VALIDATION;
2289 End If;
2290
2291 IF (l_ptr >= P_MAX_ROWS_RETURNED) THEN
2292 l_outer_loop_exit := 'Y';
2293 Exit;
2294 END IF; /* IF <(l_ptr = P_MAX_ROWS_RETURNED)> */
2295
2296 If l_Avail = 'Y' Then
2297 l_ptr := l_ptr + 1;
2298 X_Orderable_Service_tbl(l_ptr).Inventory_organization_id := l_Srv_tbl(l_index).Organization_id;
2299 X_Orderable_Service_tbl(l_ptr).Service_Item_id := l_Srv_tbl(l_index).id1;
2300 X_Orderable_Service_tbl(l_ptr).Concatenated_segments := l_Srv_tbl(l_index).concatenated_segments;
2301 X_Orderable_Service_tbl(l_ptr).Description := l_Srv_tbl(l_index).Description;
2302 X_Orderable_Service_tbl(l_ptr).Primary_uom_code := l_Srv_tbl(l_index).Primary_uom_code;
2303 X_Orderable_Service_tbl(l_ptr).Serviceable_product_flag := l_Srv_tbl(l_index).Serviceable_product_flag;
2304 X_Orderable_Service_tbl(l_ptr).Service_item_flag := l_Srv_tbl(l_index).Service_item_flag;
2305 X_Orderable_Service_tbl(l_ptr).Bom_item_type := l_Srv_tbl(l_index).Bom_item_type;
2306 X_Orderable_Service_tbl(l_ptr).Item_type := l_Srv_tbl(l_index).Item_type;
2307 X_Orderable_Service_tbl(l_ptr).Service_duration := l_Srv_tbl(l_index).Service_duration;
2308 X_Orderable_Service_tbl(l_ptr).Service_duration_period_code := l_Srv_tbl(l_index).Service_duration_period_code;
2309 X_Orderable_Service_tbl(l_ptr).Shippable_item_flag := l_Srv_tbl(l_index).Shippable_item_flag;
2310 X_Orderable_Service_tbl(l_ptr).Returnable_flag := l_Srv_tbl(l_index).Returnable_flag;
2311 End If;/*IF <l_Avail = 'Y'> */
2312
2313 l_index := l_Srv_tbl.next(l_index);
2314
2315 End Loop;/* End of While Loop */
2316
2317 END IF; /*IF <l_Srv_tbl.count > 0> */
2318
2319 IF (l_outer_loop_exit = 'Y') THEN
2320 l_outer_loop_exit := 'N';
2321 Exit;
2322 END IF; /*IF <(l_outer_loop_exit = 'Y')> */
2323
2324 If ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) then
2325 Exit When l_service_csr1%NOTFound;
2326 ELSE
2327 Exit When l_service_csr%NOTFound;
2328 End If; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2329
2330 END LOOP;/*End of Outer Loop */
2331
2332 If ((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL)) then
2333 CLOSE l_service_csr1;
2334 ELSE
2335 CLOSE l_service_csr;
2336 End If; /*IF <((P_search_input = OKC_API.G_MISS_CHAR) OR (P_search_input IS NULL))> */
2337
2338 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2339
2340 --npalepu removed the code for bug # 4897884
2341 /* --NPALEPU 21-sep-2005 for bug # 4608694
2342 --Resetting to original context
2343 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2344 --END NPALEPU */
2345 --end 4897884
2346
2347 Exception
2348 When G_EXCEPTION_HALT_VALIDATION Then
2349 x_return_status := l_return_status;
2350 IF (l_service_csr1%ISOPEN) THEN
2351 CLOSE l_service_csr1;
2352 ELSIF (l_service_csr%ISOPEN) THEN
2353 CLOSE l_service_csr;
2354 END IF;
2355 --npalepu removed the code for bug # 4897884
2356 /*--NPALEPU 21-sep-2005 for bug # 4608694
2357 --Resetting to original context
2358 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2359 --END NPALEPU */
2360 --end 4897884
2361 When Others Then
2362 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2363 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2364 IF (l_service_csr1%ISOPEN) THEN
2365 CLOSE l_service_csr1;
2366 ELSIF (l_service_csr%ISOPEN) THEN
2367 CLOSE l_service_csr;
2368 END IF;
2369 --npalepu removed the code for bug # 4897884
2370 /* --NPALEPU 21-sep-2005 for bug # 4608694
2371 --Resetting to original context
2372 mo_global.set_policy_context(l_original_access_mode,l_original_org_id);
2373 --END NPALEPU */
2374 --end 4897884
2375 END Available_Services;
2376 --END NPALEPU
2377
2378 END OKS_OMINT_PUB;