[Home] [Help]
PACKAGE BODY: APPS.ASO_SERVICE_CONTRACTS_INT_W
Source
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
1 package body aso_service_contracts_int_w as
2 /* $Header: asovqwsb.pls 120.6 2005/10/27 23:10:15 gsachdev ship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
5
6 function rosetta_g_miss_num_map(n number) return number as
7 a number := fnd_api.g_miss_num;
8 b number := 0-1962.0724;
9 begin
10 if n=a then return b; end if;
11 if n=b then return a; end if;
12 return n;
13 end;
14
15 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
16 function rosetta_g_miss_date_in_map(d date) return date as
17 begin
18 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
19 return d;
20 end;
21
22 procedure rosetta_table_copy_in_p2(t OUT NOCOPY aso_service_contracts_int.order_service_tbl_type, a0 JTF_NUMBER_TABLE
23 ) as
24 ddindx binary_integer; indx binary_integer;
25 begin
26 if a0 is not null and a0.count > 0 then
27 if a0.count > 0 then
28 indx := a0.first;
29 ddindx := 1;
30 while true loop
31 t(ddindx).service_item_id := rosetta_g_miss_num_map(a0(indx));
32 ddindx := ddindx+1;
33 if a0.last =indx
34 then exit;
35 end if;
36 indx := a0.next(indx);
37 end loop;
38 end if;
39 end if;
40 end rosetta_table_copy_in_p2;
41 procedure rosetta_table_copy_out_p2(t aso_service_contracts_int.order_service_tbl_type, a0 OUT NOCOPY JTF_NUMBER_TABLE
42 ) as
43 ddindx binary_integer; indx binary_integer;
44 begin
45 if t is null or t.count = 0 then
46 a0 := JTF_NUMBER_TABLE();
47 else
48 a0 := JTF_NUMBER_TABLE();
49 if t.count > 0 then
50 a0.extend(t.count);
51 ddindx := t.first;
52 indx := 1;
53 while true loop
54 a0(indx) := rosetta_g_miss_num_map(t(ddindx).service_item_id);
55 indx := indx+1;
56 if t.last =ddindx
57 then exit;
58 end if;
59 ddindx := t.next(ddindx);
60 end loop;
61 end if;
62 end if;
63 end rosetta_table_copy_out_p2;
64
65 procedure rosetta_table_copy_in_p4(t OUT NOCOPY aso_service_contracts_int.war_tbl_type, a0 JTF_NUMBER_TABLE
66 , a1 JTF_VARCHAR2_TABLE_2000
67 , a2 JTF_VARCHAR2_TABLE_300
68 , a3 JTF_NUMBER_TABLE
69 , a4 JTF_VARCHAR2_TABLE_100
70 , a5 JTF_NUMBER_TABLE
71 , a6 JTF_DATE_TABLE
72 , a7 JTF_DATE_TABLE
73 ) as
74 ddindx binary_integer; indx binary_integer;
75 begin
76 if a0 is not null and a0.count > 0 then
77 if a0.count > 0 then
78 indx := a0.first;
79 ddindx := 1;
80 while true loop
81 t(ddindx).service_item_id := rosetta_g_miss_num_map(a0(indx));
82 t(ddindx).service_name := a1(indx);
83 t(ddindx).service_description := a2(indx);
84 t(ddindx).duration_quantity := rosetta_g_miss_num_map(a3(indx));
85 t(ddindx).duration_period := a4(indx);
86 t(ddindx).coverage_schedule_id := rosetta_g_miss_num_map(a5(indx));
87 t(ddindx).warranty_start_date := rosetta_g_miss_date_in_map(a6(indx));
88 t(ddindx).warranty_end_date := rosetta_g_miss_date_in_map(a7(indx));
89 ddindx := ddindx+1;
90 if a0.last =indx
91 then exit;
92 end if;
93 indx := a0.next(indx);
94 end loop;
95 end if;
96 end if;
97 end rosetta_table_copy_in_p4;
98 procedure rosetta_table_copy_out_p4(t aso_service_contracts_int.war_tbl_type, a0 OUT NOCOPY JTF_NUMBER_TABLE
99 , a1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
100 , a2 OUT NOCOPY JTF_VARCHAR2_TABLE_300
101 , a3 OUT NOCOPY JTF_NUMBER_TABLE
102 , a4 OUT NOCOPY JTF_VARCHAR2_TABLE_100
103 , a5 OUT NOCOPY JTF_NUMBER_TABLE
104 , a6 OUT NOCOPY JTF_DATE_TABLE
105 , a7 OUT NOCOPY JTF_DATE_TABLE
106 ) as
107 ddindx binary_integer; indx binary_integer;
108 begin
109 if t is null or t.count = 0 then
110 a0 := JTF_NUMBER_TABLE();
111 a1 := JTF_VARCHAR2_TABLE_2000();
112 a2 := JTF_VARCHAR2_TABLE_300();
113 a3 := JTF_NUMBER_TABLE();
114 a4 := JTF_VARCHAR2_TABLE_100();
115 a5 := JTF_NUMBER_TABLE();
116 a6 := JTF_DATE_TABLE();
117 a7 := JTF_DATE_TABLE();
118 else
119 a0 := JTF_NUMBER_TABLE();
120 a1 := JTF_VARCHAR2_TABLE_2000();
121 a2 := JTF_VARCHAR2_TABLE_300();
122 a3 := JTF_NUMBER_TABLE();
123 a4 := JTF_VARCHAR2_TABLE_100();
124 a5 := JTF_NUMBER_TABLE();
125 a6 := JTF_DATE_TABLE();
126 a7 := JTF_DATE_TABLE();
127 if t.count > 0 then
128 a0.extend(t.count);
129 a1.extend(t.count);
130 a2.extend(t.count);
131 a3.extend(t.count);
132 a4.extend(t.count);
133 a5.extend(t.count);
134 a6.extend(t.count);
135 a7.extend(t.count);
136 ddindx := t.first;
137 indx := 1;
138 while true loop
139 a0(indx) := rosetta_g_miss_num_map(t(ddindx).service_item_id);
140 a1(indx) := t(ddindx).service_name;
141 a2(indx) := t(ddindx).service_description;
142 a3(indx) := rosetta_g_miss_num_map(t(ddindx).duration_quantity);
143 a4(indx) := t(ddindx).duration_period;
144 a5(indx) := rosetta_g_miss_num_map(t(ddindx).coverage_schedule_id);
145 a6(indx) := t(ddindx).warranty_start_date;
149 then exit;
146 a7(indx) := t(ddindx).warranty_end_date;
147 indx := indx+1;
148 if t.last =ddindx
150 end if;
151 ddindx := t.next(ddindx);
152 end loop;
153 end if;
154 end if;
155 end rosetta_table_copy_out_p4;
156
157 procedure available_services(p_api_version_number NUMBER
158 , p_init_msg_list VARCHAR2
159 , x_msg_count OUT NOCOPY NUMBER
160 , x_msg_data OUT NOCOPY VARCHAR2
161 , x_return_status OUT NOCOPY VARCHAR2
162 , p6_a0 OUT NOCOPY JTF_NUMBER_TABLE
163 , p5_a0 NUMBER := 0-1962.0724
164 , p5_a1 NUMBER := 0-1962.0724
165 , p5_a2 VARCHAR2 := fnd_api.g_miss_char
166 , p5_a3 DATE := fnd_api.g_miss_date
167 )
168 as
169 ddp_avail_service_rec aso_service_contracts_int.avail_service_rec_type;
170 ddx_orderable_service_tbl aso_service_contracts_int.order_service_tbl_type;
171 ddindx binary_integer; indx binary_integer;
172 begin
173
174 ASO_Quote_Util_Pvt.Enable_Debug_Pvt;
175 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.available_services BEGIN....');
176 -- copy data to the local IN or IN-OUT args, if any
177
178 ddp_avail_service_rec.product_item_id := rosetta_g_miss_num_map(p5_a0);
179 ddp_avail_service_rec.customer_id := rosetta_g_miss_num_map(p5_a1);
180 ddp_avail_service_rec.product_revision := p5_a2;
181 ddp_avail_service_rec.request_date := rosetta_g_miss_date_in_map(p5_a3);
182
183 -- here's the delegated call to the old PL/SQL routine
184 ASO_QUOTE_UTIL_PVT.Debug('Calling aso_service_contracts_int.available_services');
185 aso_service_contracts_int.available_services(p_api_version_number,
186 p_init_msg_list,
187 x_msg_count,
188 x_msg_data,
189 x_return_status,
190 ddp_avail_service_rec,
191 ddx_orderable_service_tbl);
192 ASO_QUOTE_UTIL_PVT.Debug('Ending aso_service_contracts_int.available_services');
193
194 -- copy data back from the local OUT NOCOPY /* file.sql.39 change */ or IN-OUT args, if any
195
196 aso_service_contracts_int_w.rosetta_table_copy_out_p2(ddx_orderable_service_tbl, p6_a0
197 );
198 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.available_services END....');
199 ASO_Quote_Util_Pvt.Disable_Debug_Pvt;
200 end;
201
202 procedure get_warranty(p_api_version_number NUMBER
203 , p_init_msg_list VARCHAR2
204 , x_msg_count OUT NOCOPY NUMBER
205 , x_msg_data OUT NOCOPY VARCHAR2
206 , p_org_id NUMBER
207 , p_organization_id NUMBER
208 , p_product_item_id NUMBER
209 , x_return_status OUT NOCOPY VARCHAR2
210 , p8_a0 OUT NOCOPY JTF_NUMBER_TABLE
211 , p8_a1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
212 , p8_a2 OUT NOCOPY JTF_VARCHAR2_TABLE_300
213 , p8_a3 OUT NOCOPY JTF_NUMBER_TABLE
214 , p8_a4 OUT NOCOPY JTF_VARCHAR2_TABLE_100
215 , p8_a5 OUT NOCOPY JTF_NUMBER_TABLE
216 , p8_a6 OUT NOCOPY JTF_DATE_TABLE
217 , p8_a7 OUT NOCOPY JTF_DATE_TABLE
218 )
219 as
220 ddx_warranty_tbl aso_service_contracts_int.war_tbl_type;
221 ddindx binary_integer; indx binary_integer;
222 begin
223
224 ASO_Quote_Util_Pvt.Enable_Debug_Pvt;
225 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.get_warranty BEGIN....');
226 -- here's the delegated call to the old PL/SQL routine
227
228 ASO_QUOTE_UTIL_PVT.Debug('Calling aso_service_contracts_int.get_warranty');
229 aso_service_contracts_int.get_warranty(p_api_version_number,
230 p_init_msg_list,
231 x_msg_count,
232 x_msg_data,
233 p_org_id,
234 p_organization_id,
235 p_product_item_id,
236 x_return_status,
237 ddx_warranty_tbl);
238 ASO_QUOTE_UTIL_PVT.Debug('Ending aso_service_contracts_int.get_warranty');
239
240 -- copy data back from the local OUT NOCOPY /* file.sql.39 change */ or IN-OUT args, if any
241
242 aso_service_contracts_int_w.rosetta_table_copy_out_p4(ddx_warranty_tbl, p8_a0
243 , p8_a1
244 , p8_a2
245 , p8_a3
246 , p8_a4
247 , p8_a5
248 , p8_a6
249 , p8_a7
250 );
251 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.get_warranty END..');
252 ASO_Quote_Util_Pvt.Disable_Debug_Pvt;
253 end;
254
255
256 PROCEDURE GET_SERVICES (
257 x_item_number_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
258 x_item_desc_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_2000,
259 x_start_date_tbl OUT NOCOPY JTF_DATE_TABLE,
260 x_duration_tbl OUT NOCOPY JTF_NUMBER_TABLE,
261 x_period_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
262 x_warranty_flag_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
263 p_source IN VARCHAR2,
264 p_source_id IN NUMBER,
265 p_api_version_number IN NUMBER,
266 p_init_msg_list IN VARCHAR2,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2) AS
270
271 -- for the install base
272 CURSOR c_getInvOrgId_ib IS
273 SELECT inventory_item_id, inv_master_organization_id
274 FROM csi_item_instances
275 WHERE instance_id=p_source_id;
276
277 -- for the Quote
278 CURSOR c_getInvOrgId_qe IS
279 SELECT inventory_item_id, organization_id
280 FROM aso_quote_lines_all
281 WHERE quote_line_id=p_source_id;
282
283 -- for the Order
284 --Query to get inventory item id:
285 CURSOR c_getInventoryId IS
286 SELECT inventory_item_id
287 FROM oe_order_lines_all
288 WHERE line_id =p_source_id;
292 SELECT master_organization_id
289
290 --Query to get inventory organization id:
291 CURSOR c_getOrgId IS
293 FROM oe_system_parameters;
294
295
296 -- Cursor to retrieve the existing services for the org id
297 -- and service ref line id for the quote
298
299 CURSOR c_getExistService_qe(p_orgId NUMBER) IS
300 SELECT msiv.concatenated_segments, msiv.description,
301 aql.start_date_active, aqld.service_ref_line_id,
302 aqld.service_duration, aqld.service_period
303 FROM aso_quote_lines_all aql,
304 aso_quote_line_details aqld, mtl_system_items_vl msiv
305 WHERE aqld.quote_line_id = aql.quote_line_id
306 AND aql.inventory_item_id = msiv.inventory_item_id
307 AND aql.organization_id = msiv.organization_id
308 AND msiv.organization_id=p_orgId
309 AND aqld.service_ref_line_id =p_source_Id;
310
311 -- Cursor to get existing services for order
312 CURSOR c_getExistService_or (p_orgId NUMBER) IS
313 SELECT msiv.concatenated_segments, msiv.description,
314 ol.service_start_date, ol.service_duration, ol.service_period
315 FROM oe_order_lines_all ol, mtl_system_items_vl msiv
316 WHERE ol.service_reference_type_code = 'ORDER'
317 AND ol.inventory_item_id = msiv.inventory_item_id
318 AND msiv.organization_id = p_orgId
319 AND ol.service_reference_line_id=p_source_Id ;
320
321 l_contracts_tbl oks_entitlements_pub.output_tbl_ib;
322 l_inp_rec oks_entitlements_pub.input_rec_ib;
323 l_item_id NUMBER;
324 l_inv_org_id NUMBER;
325 l_warranty_tbl aso_service_contracts_int.war_tbl_type;
326 l_return_status VARCHAR2(5);
327 l_item_number JTF_VARCHAR2_TABLE_2000;
328 l_count NUMBER :=0;
329 l_index NUMBER :=0;
330 l_api_version NUMBER := 1.0;
331 l_api_name VARCHAR2(50) := 'GET_SERVICES';
332 G_PKG_NAME VARCHAR2(50):= 'ASO_SERVICE_CONTRACTS_INT_W';
333 l_current_org_id NUMBER ;
334
335 BEGIN
339
336
337 -- Enable debug message
338 Aso_Quote_Util_Pvt.Enable_Debug_Pvt;
340 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.getServices BEGIN....');
341 -- Standard Start of API savepoint
342 SAVEPOINT GET_SERVICES_PUB;
343
344 aso_debug_pub.g_debug_flag := Aso_Quote_Util_Pvt.is_debug_enabled;
345 -- Standard call to check for call compatibility.
346 IF NOT FND_API.Compatible_API_Call ( l_api_version,
347 p_api_version_number,
348 l_api_name,
349 G_PKG_NAME)
350 THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354
355 -- Initialize message list if p_init_msg_list is set to TRUE.
356 IF FND_API.to_Boolean( p_init_msg_list ) THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359
360 -- Debug Message
361 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
362 'Private API: ' || l_api_name || 'start');
363
364 -- Initialize API return status to SUCCESS
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 --
368 -- API body
369 --
370
371 -- ******************************************************************
372 -- Validate Environment
373 -- ******************************************************************
374 IF FND_GLOBAL.User_Id IS NULL THEN
378 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
375 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)THEN
376 FND_MESSAGE.Set_Name(' + appShortName +',
377 'UT_CANNOT_GET_PROFILE_VALUE');
379 FND_MSG_PUB.ADD;
380 END IF;
381 RAISE FND_API.G_EXC_ERROR;
382 END IF;
383
384
385 x_item_number_tbl := JTF_VARCHAR2_TABLE_2000();
386 x_item_desc_tbl := JTF_VARCHAR2_TABLE_2000();
387 x_start_date_tbl := JTF_DATE_TABLE();
388 x_duration_tbl := JTF_NUMBER_TABLE();
389 x_period_code_tbl := JTF_VARCHAR2_TABLE_100();
390 x_warranty_flag_tbl := JTF_VARCHAR2_TABLE_100();
391
392 IF (p_source ='INSTALL_BASE') THEN
393
394 IF aso_debug_pub.g_debug_flag = 'Y' THEN
395 aso_debug_pub.add('Aso_Service_Contracts_int_w:Get_Services p_source = INSTALL_BASE ', 1, 'N');
396 END IF;
397
398 l_inp_rec.validate_flag :='Y'; -- show all contracts
399 l_inp_rec.product_id :=p_source_Id;
400 l_inp_rec.calc_resptime_flag :='N';
401
402 ASO_QUOTE_UTIL_PVT.Debug('Calling oks_entitlements_pub.get_contracts InstallBase');
403 aso_utility_pvt.print_login_info();
404
405 oks_entitlements_pub.get_contracts(
406 p_api_version =>l_api_version,
407 p_init_msg_list => FND_API.G_TRUE,
408 p_inp_rec => l_inp_rec,
409 x_ent_contracts => l_contracts_tbl,
410 x_return_status => x_return_status,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data);
413 ASO_QUOTE_UTIL_PVT.Debug('Ending oks_entitlements_pub.get_contracts InstallBase');
414
415 IF aso_debug_pub.g_debug_flag = 'Y' THEN
416 aso_debug_pub.add('ASO_Service_contracts_int_w: After Call to OKS entitlements get contracts: x_return_status '|| x_return_status, 1, 'N');
417 END IF;
418
419 -- Check return status from the above procedure call
420 IF x_return_status = FND_API.G_RET_STS_ERROR then
421 raise FND_API.G_EXC_ERROR;
422 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
423 raise FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
427 IF l_contracts_tbl IS NOT NULL THEN
428 IF l_contracts_tbl.count >0 THEN
429 x_item_number_tbl.extend(l_contracts_tbl.count);
430 x_item_desc_tbl.extend(l_contracts_tbl.count);
431 x_start_date_tbl.extend(l_contracts_tbl.count);
432 x_duration_tbl.extend(l_contracts_tbl.count);
433 x_period_code_tbl.extend(l_contracts_tbl.count);
434 x_warranty_flag_tbl.extend(l_contracts_tbl.count);
435 FOR i in l_contracts_tbl.FIRST..l_contracts_tbl.LAST LOOP
436 x_item_number_tbl(i) := l_contracts_tbl(i).service_name;
437 x_item_desc_tbl(i) := l_contracts_tbl(i).service_description;
438 x_start_date_tbl(i) := l_contracts_tbl(i).service_start_date;
439 x_warranty_flag_tbl(i) := l_contracts_tbl(i).warranty_flag;
440 ASO_QUOTE_UTIL_PVT.Debug('Calling okc_time_util_pub.get_duration InstallBase');
441 okc_time_util_pub.get_duration(
442 p_start_date => l_contracts_tbl(i).service_start_date,
443 p_end_date => l_contracts_tbl(i).service_end_date,
444 x_duration => x_duration_tbl(i),
445 x_timeunit => x_period_code_tbl(i),
446 x_return_status => l_return_status);
447 ASO_QUOTE_UTIL_PVT.Debug('Calling okc_time_util_pub.get_duration InstallBase');
448 IF l_return_status = FND_API.G_RET_STS_ERROR then
449 RAISE FND_API.G_EXC_ERROR;
450 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
452 END IF;
453 END LOOP;
454 END IF;
455 END IF;
456 END IF;
457 ELSIF (p_source='QUOTE') THEN
458
459 IF aso_debug_pub.g_debug_flag = 'Y' THEN
460 aso_debug_pub.add('Aso_Service_Contracts_int_w:Get_Services p_source:= QUOTE', 1, 'N');
461 END IF;
462
463 FOR c_InvOrg_or IN c_getInvOrgId_qe LOOP
464 l_item_id := c_invOrg_or.inventory_item_id;
465 l_inv_org_id :=c_invOrg_or.organization_id;
466 END LOOP;
467
468 IF aso_debug_pub.g_debug_flag = 'Y' THEN
469 aso_debug_pub.add('Aso_Service_Contracts_int_w:Get_Services l_item_id= '|| l_item_id ||'l_inv_org_id=' || l_inv_org_id , 1, 'N');
470 END IF;
471
472 l_index :=0;
473 FOR c_quote IN c_getExistService_qe(l_inv_org_id) LOOP
474 l_index := l_index + 1;
475 x_item_number_tbl.extend(1);
476 x_item_desc_tbl.extend(1);
477 x_start_date_tbl.extend(1);
478 x_duration_tbl.extend(1);
479 x_period_code_tbl.extend(1);
480 x_warranty_flag_tbl.extend(1);
481
482 x_item_number_tbl(l_index) :=c_quote.concatenated_segments;
483 x_item_desc_tbl(l_index) := c_quote.description;
484 x_start_date_tbl(l_index) := c_quote.start_date_active;
485 x_duration_tbl(l_index) := c_quote.service_duration;
486 x_period_code_tbl(l_index) := c_quote.service_period;
487 x_warranty_flag_tbl(l_index) := 'N';
488 END LOOP;
489
490 ELSIF (p_source='ORDER') THEN
491 IF aso_debug_pub.g_debug_flag = 'Y' THEN
492 aso_debug_pub.add('Aso_Service_Contracts_int_w:Get_Services p_source = ORDER ', 1, 'N');
493 END IF;
494
495 FOR c_getItemId IN c_getInventoryId LOOP
496 l_item_id :=c_getItemId.inventory_item_id;
497 END LOOP;
498
499 FOR c_getMasterOrg IN c_getOrgId LOOP
500 l_inv_org_id :=c_getMasterOrg.master_organization_id;
501 END LOOP;
502
503 IF aso_debug_pub.g_debug_flag = 'Y' THEN
507 l_index :=0;
504 aso_debug_pub.add('Aso_Service_Contracts_int_w:Get_Services l_item_id= '|| l_item_id ||'l_inv_org_id=' || l_inv_org_id , 1, 'N');
505 END IF;
506
508 FOR c_order IN c_getExistService_or(l_inv_org_id) LOOP
509 l_index := l_index + 1;
510 x_item_number_tbl.extend(1);
511 x_item_desc_tbl.extend(1);
512 x_start_date_tbl.extend(1);
513 x_duration_tbl.extend(1);
514 x_period_code_tbl.extend(1);
515 x_warranty_flag_tbl.extend(1);
516 x_item_number_tbl(l_index) :=c_order.concatenated_segments;
517 x_item_desc_tbl(l_index) := c_order.description;
518 x_start_date_tbl(l_index) := c_order.service_start_date;
519 x_duration_tbl(l_index) := c_order.service_duration;
520 x_period_code_tbl(l_index) := c_order.service_period;
521 x_warranty_flag_tbl(l_index) := 'N';
522 END LOOP;
523 END IF;
524
525 IF (P_SOURCE <> 'INSTALL_BASE') THEN
526 ASO_QUOTE_UTIL_PVT.Debug('Calling aso_service_contracts_int.get_warranty <> InstallBase');
527 l_current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID ;
528 ASO_QUOTE_UTIL_PVT.Debug('Current Org ID :' || l_current_org_id);
529
530 Aso_Service_Contracts_Int.Get_Warranty(
531 p_api_version_number => l_api_version,
532 p_init_msg_list => FND_API.G_FALSE,
533 x_return_status => x_return_status,
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 --p_org_id => Fnd_Profile.value('ORG_ID'),
537 --Modified to get the value from context
538 p_org_id => l_current_org_id,
539 p_organization_id => l_inv_org_id,
540 p_product_item_id => l_item_id,
541 x_warranty_tbl => l_warranty_tbl);
542 ASO_QUOTE_UTIL_PVT.Debug('Ending aso_service_contracts_int.get_warranty <> InstallBase');
543
544 IF aso_debug_pub.g_debug_flag = 'Y' THEN
545 aso_debug_pub.add('ASO_Service_contracts_int_w: After Call to get_warranty: x_return_status '|| x_return_status, 1, 'N');
546 END IF;
547
548 -- Check return status from the above procedure call
549 IF x_return_status = FND_API.G_RET_STS_ERROR then
550 raise FND_API.G_EXC_ERROR;
551 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
552 raise FND_API.G_EXC_UNEXPECTED_ERROR;
553 END IF;
554
555 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
556 IF l_warranty_tbl IS NOT NULL THEN
557 IF l_warranty_tbl.count >0 THEN
558 x_item_number_tbl.extend(l_warranty_tbl.count);
559 x_item_desc_tbl.extend(l_warranty_tbl.count);
560 x_start_date_tbl.extend(l_warranty_tbl.count);
561 x_duration_tbl.extend(l_warranty_tbl.count);
562 x_period_code_tbl.extend(l_warranty_tbl.count);
563 x_warranty_flag_tbl.extend(l_warranty_tbl.count);
564 FOR i in l_warranty_tbl.FIRST..l_warranty_tbl.LAST LOOP
565 l_index := l_index + 1;
566 x_item_number_tbl(l_index) := l_warranty_tbl(i).service_name;
567 x_item_desc_tbl(l_index) := l_warranty_tbl(i).service_description;
568 x_start_date_tbl(l_index) := l_warranty_tbl(i).warranty_start_date;
569 x_duration_tbl(l_index) := l_warranty_tbl(i).duration_quantity;
570 x_period_code_tbl(l_index) := l_warranty_tbl(i).duration_period;
571 x_warranty_flag_tbl(l_index) := 'Y';
572 END LOOP;
573 END IF;
574 END IF;
575 END IF;
576 END IF;
577
578 -- Standard call to get message count and if count is 1, get message info.
579 FND_MSG_PUB.Count_And_Get
580 ( p_count => x_msg_count,
581 p_data => x_msg_data
582 );
583
584 --disable the debug message
585 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.getServices END....');
586 ASO_Quote_Util_Pvt.disable_debug_pvt;
587 EXCEPTION
588 WHEN FND_API.G_EXC_ERROR THEN
589 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
590 P_API_NAME => L_API_NAME
591 ,P_PKG_NAME => G_PKG_NAME
592 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
593 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
594 ,X_MSG_COUNT => X_MSG_COUNT
595 ,X_MSG_DATA => X_MSG_DATA
596 ,X_RETURN_STATUS => X_RETURN_STATUS);
597 ASO_Quote_Util_Pvt.disable_debug_pvt;
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
600 P_API_NAME => L_API_NAME
601 ,P_PKG_NAME => G_PKG_NAME
602 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
603 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
604 ,X_MSG_COUNT => X_MSG_COUNT
605 ,X_MSG_DATA => X_MSG_DATA
606 ,X_RETURN_STATUS => X_RETURN_STATUS);
607 ASO_Quote_Util_Pvt.disable_debug_pvt;
608 WHEN OTHERS THEN
609 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
610 P_API_NAME => L_API_NAME
611 ,P_PKG_NAME => G_PKG_NAME
612 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
613 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
614 ,X_MSG_COUNT => X_MSG_COUNT
615 ,X_MSG_DATA => X_MSG_DATA
616 ,X_RETURN_STATUS => X_RETURN_STATUS);
617 ASO_Quote_Util_Pvt.disable_debug_pvt;
618 END GET_SERVICES;
619
620 procedure is_service_available (
621 p_api_version_number IN NUMBER
622 , p_init_msg_list IN VARCHAR2
623 , x_return_status OUT NOCOPY VARCHAR2
624 , x_msg_count OUT NOCOPY NUMBER
625 , x_msg_data OUT NOCOPY VARCHAR2
626 , p_product_item_id IN Number
627 , p_service_item_id IN Number
631 , X_Available_YN OUT NOCOPY /* file.sql.39 change */ varchar2) IS
628 , p_customer_id IN Number
629 , p_product_revision IN Varchar2
630 , p_request_date IN Date
632
633 l_check_service_rec ASO_service_contracts_INT.CHECK_SERVICE_REC_TYPE;
634 l_api_name varchar2(50) := 'is_service_available';
638 BEGIN
635 l_api_version NUMBER := 1.0;
636 G_PKG_NAME VARCHAR2(50):= 'ASO_SERVICE_CONTRACTS_INT_W';
637
639
640
641 -- Enable debug message
642 Aso_Quote_Util_Pvt.Enable_Debug_Pvt;
643
644 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.is_service_available BEGIN....');
645
646 aso_debug_pub.g_debug_flag := Aso_Quote_Util_Pvt.is_debug_enabled;
647 -- Standard call to check for call compatibility.
648 IF NOT FND_API.Compatible_API_Call ( l_api_version,
649 p_api_version_number,
650 l_api_name,
651 G_PKG_NAME)
652 THEN
653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654 END IF;
655
656
657 -- Initialize message list if p_init_msg_list is set to TRUE.
658 IF FND_API.to_Boolean( p_init_msg_list ) THEN
659 FND_MSG_PUB.initialize;
660 END IF;
661
662 -- Initialize API return status to SUCCESS
663 x_return_status := FND_API.G_RET_STS_SUCCESS;
664
665 --
666 -- API body
667 --
668 l_check_service_rec.product_item_id := p_product_item_id;
669 l_check_service_rec.service_item_id := p_service_item_id;
670 l_check_service_rec.customer_id := p_customer_id;
671 l_check_service_rec.product_revision := p_product_revision;
672 l_check_service_rec.request_date := p_request_date;
673
674 ASO_service_contracts_INT.Is_Service_Available
675 (
676 P_Api_Version_Number,
677 P_init_msg_list,
678 X_msg_Count,
679 X_msg_Data,
680 X_Return_Status,
681 l_check_service_rec,
682 X_Available_YN);
683
684 -- Standard call to get message count and if count is 1, get message info.
685 FND_MSG_PUB.Count_And_Get
686 ( p_count => x_msg_count,
687 p_data => x_msg_data
688 );
689
690 --disable the debug message
691 ASO_QUOTE_UTIL_PVT.Debug('aso_service_contracts_int_w.is_service_available END....');
692 ASO_Quote_Util_Pvt.disable_debug_pvt;
693
694
695 EXCEPTION
696 WHEN FND_API.G_EXC_ERROR THEN
697 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
698 P_API_NAME => L_API_NAME
699 ,P_PKG_NAME => G_PKG_NAME
700 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
701 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
702 ,X_MSG_COUNT => X_MSG_COUNT
703 ,X_MSG_DATA => X_MSG_DATA
704 ,X_RETURN_STATUS => X_RETURN_STATUS);
705 ASO_Quote_Util_Pvt.disable_debug_pvt;
706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
708 P_API_NAME => L_API_NAME
709 ,P_PKG_NAME => G_PKG_NAME
710 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
711 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
712 ,X_MSG_COUNT => X_MSG_COUNT
713 ,X_MSG_DATA => X_MSG_DATA
714 ,X_RETURN_STATUS => X_RETURN_STATUS);
715 ASO_Quote_Util_Pvt.disable_debug_pvt;
716 WHEN OTHERS THEN
717 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
718 P_API_NAME => L_API_NAME
719 ,P_PKG_NAME => G_PKG_NAME
720 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
721 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
722 ,X_MSG_COUNT => X_MSG_COUNT
723 ,X_MSG_DATA => X_MSG_DATA
724 ,X_RETURN_STATUS => X_RETURN_STATUS);
725 ASO_Quote_Util_Pvt.disable_debug_pvt;
726
727 END is_service_available;
728
729 procedure available_services(
730 x_Inventory_organization_id OUT NOCOPY JTF_NUMBER_TABLE
731 , x_Service_item_id OUT NOCOPY JTF_NUMBER_TABLE
732 , x_Concatenated_segments OUT NOCOPY JTF_VARCHAR2_TABLE_1000
733 , x_Description OUT NOCOPY JTF_VARCHAR2_TABLE_1000
734 , x_Primary_uom_code OUT NOCOPY JTF_VARCHAR2_TABLE_300
735 , x_Serviceable_product_flag OUT NOCOPY JTF_VARCHAR2_TABLE_100
736 , x_Service_item_flag OUT NOCOPY JTF_VARCHAR2_TABLE_100
737 , x_Bom_item_type OUT NOCOPY JTF_NUMBER_TABLE
738 , x_Item_type OUT NOCOPY JTF_VARCHAR2_TABLE_1000
739 , x_Service_duration OUT NOCOPY JTF_NUMBER_TABLE
740 , x_Service_duration_period_code OUT NOCOPY JTF_VARCHAR2_TABLE_1000
744 , p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR
741 , x_Shippable_item_flag OUT NOCOPY JTF_VARCHAR2_TABLE_100
742 , x_Returnable_flag OUT NOCOPY JTF_VARCHAR2_TABLE_100
743 , p_api_version_number IN NUMBER
745 , p_commit IN VARCHAR2:= FND_API.g_false
746 , p_search_input IN VARCHAR2 := FND_API.G_MISS_CHAR
747 , p_product_item_id IN Number := FND_API.G_MISS_NUM
748 , p_customer_id IN Number := FND_API.G_MISS_NUM
749 , p_product_revision IN Varchar2 := FND_API.G_MISS_CHAR
750 , p_request_date IN Date := FND_API.G_MISS_DATE
751 , x_return_status OUT NOCOPY VARCHAR2
752 , x_msg_count OUT NOCOPY NUMBER
753 , x_msg_data OUT NOCOPY VARCHAR2)
754
755 is
756 l_api_name varchar2(50) := 'available_services';
757 l_api_version NUMBER := 1.0;
758 G_PKG_NAME VARCHAR2(50):= 'ASO_SERVICE_CONTRACTS_INT_W';
759 l_avail_service_rec OKS_OMINT_PUB.Avail_Service_Rec_Type;
760 l_orderable_service_tbl OKS_OMINT_PUB.new_order_service_tbl_type;
761 l_count NUMBER :=0;
762 l_index NUMBER :=0;
763 begin
764
765
766 -- Enable debug message
767 Aso_Quote_Util_Pvt.Enable_Debug_Pvt;
768
769
770 IF aso_debug_pub.g_debug_flag = 'Y' THEN
771 aso_debug_pub.ADD ( 'Overloaded aso_service_contracts_int_w.is_service_available BEGIN.... ' , 1 , 'Y' );
772 END IF;
773
774
775 aso_debug_pub.g_debug_flag := Aso_Quote_Util_Pvt.is_debug_enabled;
776 -- Standard call to check for call compatibility.
777 IF NOT FND_API.Compatible_API_Call ( l_api_version,
778 p_api_version_number,
779 l_api_name,
780 G_PKG_NAME)
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784
785
786 -- Initialize message list if p_init_msg_list is set to TRUE.
787 IF FND_API.to_Boolean( p_init_msg_list ) THEN
788 FND_MSG_PUB.initialize;
789 END IF;
790
791 -- Initialize API return status to SUCCESS
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 SAVEPOINT AVAILABLE_SERVICES_PUB;
795
796
797 --
798 -- API body
799
800
801 ASO_QUOTE_UTIL_PVT.Debug('Constructing the input record');
802
803 l_avail_service_rec.product_item_id := p_product_item_id;
804 l_avail_service_rec.customer_id := p_customer_id;
805 l_avail_service_rec.product_revision := p_product_revision;
806 l_avail_service_rec.request_date := p_request_date;
807
808 IF aso_debug_pub.g_debug_flag = 'Y' THEN
809 aso_debug_pub.ADD ( 'p_product_item_id'||p_product_item_id , 1 , 'Y' );
810 aso_debug_pub.ADD ( 'p_customer_id ' ||p_customer_id , 1 , 'Y' );
811 aso_debug_pub.ADD ( 'p_product_revision ' ||p_product_revision , 1 , 'Y' );
812 aso_debug_pub.ADD ( 'p_request_date ' ||p_request_date, 1 , 'Y' );
813 aso_debug_pub.ADD ( 'P_search_input ' ||P_search_input , 1 , 'Y' );
814 aso_debug_pub.ADD ( 'Before calling the OKC API' , 1 , 'Y' );
815 aso_utility_pvt.print_login_info();
816 END IF;
817
818
819
820 OKS_OMINT_PUB.Available_Services(
821 P_Api_Version => P_Api_Version_number,
822 P_init_msg_list => P_init_msg_list,
823 P_search_input => P_search_input,
824 X_msg_Count => X_msg_Count,
825 X_msg_Data => X_msg_Data,
826 X_Return_Status => X_Return_Status,
827 p_avail_service_rec => l_avail_service_rec,
828 x_orderable_service_tbl => l_orderable_service_tbl
829 );
830
831 IF aso_debug_pub.g_debug_flag = 'Y' THEN
832 aso_debug_pub.ADD ( 'After calling the OKC API' , 1 , 'Y' );
833 aso_debug_pub.ADD ( 'Return status from OKC API : '|| X_Return_Status , 1 , 'Y' );
834 aso_utility_pvt.print_login_info();
835 END IF;
836
837
838 -- Check return status from the above procedure call
839 IF x_return_status = FND_API.G_RET_STS_ERROR then
840 raise FND_API.G_EXC_ERROR;
841 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
842 raise FND_API.G_EXC_UNEXPECTED_ERROR;
843 END IF;
844
845 -- initialize the record structures
846 x_Inventory_organization_id := JTF_NUMBER_TABLE();
847 x_Service_item_id := JTF_NUMBER_TABLE();
848 x_Concatenated_segments := JTF_VARCHAR2_TABLE_1000();
849 x_Description := JTF_VARCHAR2_TABLE_1000();
850 x_Primary_uom_code := JTF_VARCHAR2_TABLE_300();
851 x_Serviceable_product_flag := JTF_VARCHAR2_TABLE_100();
852 x_Service_item_flag := JTF_VARCHAR2_TABLE_100();
853 x_Bom_item_type := JTF_NUMBER_TABLE();
854 x_Item_type := JTF_VARCHAR2_TABLE_1000();
855 x_Service_duration := JTF_NUMBER_TABLE();
856 x_Service_duration_period_code := JTF_VARCHAR2_TABLE_1000();
857 x_Shippable_item_flag := JTF_VARCHAR2_TABLE_100();
858 x_Returnable_flag := JTF_VARCHAR2_TABLE_100();
859
860 IF aso_debug_pub.g_debug_flag = 'Y' THEN
861 aso_debug_pub.ADD ( 'Number of Records Returned from OKC API : '|| to_char(l_orderable_service_tbl.count) , 1 , 'Y' );
862 END IF;
863
864
865 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
866 IF l_orderable_service_tbl IS NOT NULL THEN
867 IF l_orderable_service_tbl.count >0 THEN
871 x_Inventory_organization_id.extend(l_orderable_service_tbl.count);
868
869 ASO_QUOTE_UTIL_PVT.Debug('Extending the jtf tables ');
870
872 x_Service_item_id.extend(l_orderable_service_tbl.count);
873 x_Concatenated_segments.extend(l_orderable_service_tbl.count);
874 x_Description.extend(l_orderable_service_tbl.count);
875 x_Primary_uom_code.extend(l_orderable_service_tbl.count);
876 x_Serviceable_product_flag.extend(l_orderable_service_tbl.count);
877 x_Service_item_flag.extend(l_orderable_service_tbl.count);
878 x_Bom_item_type.extend(l_orderable_service_tbl.count);
879 x_Item_type.extend(l_orderable_service_tbl.count);
880 x_Service_duration.extend(l_orderable_service_tbl.count);
881 x_Service_duration_period_code.extend(l_orderable_service_tbl.count);
882 x_Shippable_item_flag.extend(l_orderable_service_tbl.count);
883 x_Returnable_flag.extend(l_orderable_service_tbl.count);
884
885 FOR i in l_orderable_service_tbl.FIRST..l_orderable_service_tbl.LAST LOOP
886 l_index := l_index + 1;
887
888 ASO_QUOTE_UTIL_PVT.Debug('Assigning the values ');
889
890 x_Inventory_organization_id(l_index) := l_orderable_service_tbl(i).Inventory_organization_id;
891 x_Service_item_id(l_index) := l_orderable_service_tbl(i).Service_item_id;
892 x_Concatenated_segments(l_index) := l_orderable_service_tbl(i).Concatenated_segments;
893 x_Description(l_index) := l_orderable_service_tbl(i).Description;
894 x_Primary_uom_code(l_index) := l_orderable_service_tbl(i).Primary_uom_code;
895 x_Serviceable_product_flag(l_index) := l_orderable_service_tbl(i).Serviceable_product_flag;
896 x_Service_item_flag(l_index) := l_orderable_service_tbl(i).Service_item_flag;
897 x_Bom_item_type(l_index) := l_orderable_service_tbl(i).Bom_item_type;
898 x_Item_type(l_index) := l_orderable_service_tbl(i).Item_type;
899 x_Service_duration(l_index) := l_orderable_service_tbl(i).Service_duration;
900 x_Service_duration_period_code(l_index) := l_orderable_service_tbl(i).Service_duration_period_code;
901 x_Shippable_item_flag(l_index) := l_orderable_service_tbl(i).Shippable_item_flag;
902 x_Returnable_flag(l_index) := l_orderable_service_tbl(i).Returnable_flag;
903
904 END LOOP;
905 END IF;
906 END IF;
907 END IF;
908
909 ASO_QUOTE_UTIL_PVT.Debug('After Assigning the values ');
910 -- Standard call to get message count and if count is 1, get message info.
911 FND_MSG_PUB.Count_And_Get
912 ( p_count => x_msg_count,
913 p_data => x_msg_data
914 );
915
916 --disable the debug message
917 IF aso_debug_pub.g_debug_flag = 'Y' THEN
918 aso_debug_pub.ADD ( 'Overloaded aso_service_contracts_int_w.is_service_available END.... ' , 1 , 'Y' );
919 END IF;
920
921 ASO_Quote_Util_Pvt.disable_debug_pvt;
922
923
924 EXCEPTION
925 WHEN FND_API.G_EXC_ERROR THEN
926 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
927 P_API_NAME => L_API_NAME
928 ,P_PKG_NAME => G_PKG_NAME
932 ,X_MSG_DATA => X_MSG_DATA
929 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
930 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
931 ,X_MSG_COUNT => X_MSG_COUNT
933 ,X_RETURN_STATUS => X_RETURN_STATUS);
934 ASO_Quote_Util_Pvt.disable_debug_pvt;
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
937 P_API_NAME => L_API_NAME
938 ,P_PKG_NAME => G_PKG_NAME
939 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
940 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
941 ,X_MSG_COUNT => X_MSG_COUNT
942 ,X_MSG_DATA => X_MSG_DATA
943 ,X_RETURN_STATUS => X_RETURN_STATUS);
944 ASO_Quote_Util_Pvt.disable_debug_pvt;
945 WHEN OTHERS THEN
946 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
947 P_API_NAME => L_API_NAME
948 ,P_PKG_NAME => G_PKG_NAME
949 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
950 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
951 ,X_MSG_COUNT => X_MSG_COUNT
952 ,X_MSG_DATA => X_MSG_DATA
953 ,X_RETURN_STATUS => X_RETURN_STATUS);
954 ASO_Quote_Util_Pvt.disable_debug_pvt;
955 end available_services;
956
957
958 end aso_service_contracts_int_w;