[Home] [Help]
PACKAGE BODY: APPS.ASO_SERVICE_CONTRACTS_INT
Source
1 PACKAGE BODY ASO_service_contracts_INT as
2 /* $Header: asoioksb.pls 120.2.12010000.2 2009/07/20 09:35:47 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_service_contracts_INT
5 -- Purpose :
6 -- History :
7 -- 10/18/2002 hyang - 2633507 performance fix
8 -- 10/21/2002 hyang - fix GSSC warning about default parameter values
9 -- NOTE :
10 -- End of Comments
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_service_contracts_INT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoioksb.pls';
14
15 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
16 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
17
18 Procedure Get_service_attributes
19 (
20 P_Api_Version_Number IN Number,
21 P_init_msg_list IN Varchar2 := FND_API.G_FALSE,
22 P_Qte_Line_Rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type,
23 P_Qte_Line_Dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_tbl_Type,
24 X_msg_Count OUT NOCOPY /* file.sql.39 change */ Number,
25 X_msg_Data OUT NOCOPY /* file.sql.39 change */ Varchar2,
26 X_Return_Status OUT NOCOPY /* file.sql.39 change */ Varchar2
27 )
28 IS
29
30 /* 2633507 - hyang: using cursor variable and base table */
31 CURSOR C_item1(inv1 NUMBER, lc_organization_id NUMBER) IS
32 select SERVICEABLE_PRODUCT_FLAG
33 from MTL_SYSTEM_ITEMS_B
34 where inventory_item_id = inv1
35 and organization_id = lc_organization_id;
36 CURSOR C_qln(c_qln_id NUMBER) IS
37 SELECT b.inventory_item_id,b.organization_id,a.cust_account_id
38 FROM aso_quote_headers_all a,aso_quote_lines_all b
39 WHERE b.quote_line_id = c_qln_id and
40 a.quote_header_id=b.quote_header_id;
41 CURSOR C_get_cust IS
42 SELECT cust_account_id
43 FROM aso_quote_headers_all
44 WHERE quote_header_id= P_Qte_Line_Rec.quote_header_id;
45 /*
46 CURSOR C_cs_item(cs_prd_id NUMBER) IS
47 SELECT a.inventory_item_id
48 FROM
49 CS_CUSTOMER_PRODUCTS_All a, mtl_system_items_kfv b
50 WHERE a.inventory_item_id = b.inventory_item_id
51 AND a.customer_product_id = cs_prd_id
52 AND b.organization_id = ( SELECT cs_std.get_item_valdn_orgzn_id FROM DUAL ) ;
53 */
54
55 CURSOR C_cs_item(p_instance_id NUMBER, cs_org_id NUMBER) IS
56 SELECT a.inventory_item_id
57 FROM
58 csi_item_instances a, mtl_system_items_b b
59 WHERE a.inventory_item_id = b.inventory_item_id
60 AND a.instance_id = p_instance_id
61 AND b.organization_id = cs_org_id ;
62
63 CURSOR C_ord_item(ord_line_id NUMBER) IS
64 SELECT inventory_item_id
65 FROM oe_order_lines_All
66 WHERE line_id=ord_line_id;
67
68 /* Commented for Sun ER 8647883
69 CURSOR C_cust_id (Quote_hd_id NUMBER)IS
70 SELECT cust_account_id
71 FROM aso_quote_headers_all
72 WHERE quote_header_id= Quote_hd_id;
73 */
74
75 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
76 CURSOR C_cust_id (Quote_hd_id NUMBER)IS
77 SELECT cust_account_id,
78 decode(nvl(fnd_profile.value('ASO_FILTER_SERVICE_RF_END_CUST'),'N'),'Y',nvl(END_CUSTOMER_CUST_ACCOUNT_ID,cust_account_id),cust_account_id)
79 hd_end_cust_account_id
80 FROM aso_quote_headers_all
81 WHERE quote_header_id= Quote_hd_id;
82
83
84 CURSOR C_get_cust_line(Quote_ln_id number) IS
85 SELECT END_CUSTOMER_CUST_ACCOUNT_ID cust_account_id
86 FROM aso_quote_lines_all
87 WHERE quote_line_id= Quote_ln_id;
88
89 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
90
91 l_api_version_number NUMBER := 1.0;
92 l_api_name VARCHAR2(50) := 'Get_service_attributes';
93 l_inventory_item_id NUMBER ;
94 l_organization_id NUMBER;
95 l_cust_account_id NUMBER;
96 l_serviceable_flag VARCHAR2(1);
97 l_check_service_rec ASO_SERVICE_CONTRACTS_INT.CHECK_SERVICE_REC_TYPE;
98 l_Available_YN VARCHAR2(1);
99 l_cs_org_id number;
100 ln_end_cust_account_id NUMBER; -- line level end customer Sun ER 8647883
101 lh_end_cust_account_id NUMBER; -- header level end customer Sun ER 8647883
102
103 BEGIN
104 -- Standard Start of API savepoint
105 SAVEPOINT GET_SERVICE_ATTRIBUTES_PVT;
106
107 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
108
109 -- Standard call to check for call compatibility.
110 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
111 p_api_version_number,
112 l_api_name,
113 G_PKG_NAME)
114 THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118
119 -- Initialize message list if p_init_msg_list is set to TRUE.
120 IF FND_API.to_Boolean( p_init_msg_list )
121 THEN
122 FND_MSG_PUB.initialize;
123 END IF;
124
125 -- Debug Message
126
127 -- Initialize API return status to SUCCESS
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 --
131 -- API body
132 --
133
134 -- ******************************************************************
135 -- Validate Environment
136 -- ******************************************************************
137 IF FND_GLOBAL.User_Id IS NULL
138 THEN
139 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
140 THEN
141 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
142 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
143 FND_MSG_PUB.ADD;
144 END IF;
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 OPEN C_cust_id(P_Qte_Line_rec.quote_header_id);
148 FETCH C_cust_id INTO l_cust_account_id,lh_end_cust_account_id; -- ER 8647883
149
150 IF aso_debug_pub.g_debug_flag = 'Y' THEN
151 aso_debug_pub.add('Get Service Attr: cust Acct id'||l_cust_account_id);
152 aso_debug_pub.add('Get Service Attr: header cust Acct id'||lh_end_cust_account_id);
153 END IF;
154
155 IF C_cust_id%NOTFOUND THEN
156 l_cust_account_id := NULL;
157 lh_end_cust_account_id := NULL;
158 END IF;
159 CLOSE C_cust_id;
160
161 FOR i in 1..P_Qte_Line_Dtl_tbl.count LOOP
162 IF P_Qte_Line_Dtl_tbl(i).SERVICE_REF_TYPE_CODE = 'QUOTE' THEN
163 OPEN C_qln( P_Qte_Line_Dtl_tbl(i).service_ref_line_id);
164 FETCH C_qln INTO l_inventory_item_id,l_organization_id,l_cust_account_id;
165
166 IF aso_debug_pub.g_debug_flag = 'Y' THEN
167
168 aso_debug_pub.add('Get Service Attr:ref code QUOTE inv id '||l_inventory_item_id, 1, 'Y');
169 aso_debug_pub.add('Get Service Attr:ref code QUOTE orgnization id '||l_organization_id , 1, 'Y');
170 aso_debug_pub.add('Get Service Attr:ref code QUOTE cust id '||l_cust_account_id, 1, 'Y');
171
172 END IF;
173
174 IF C_qln%NOTFOUND THEN
175 CLOSE C_qln;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
178 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
179 FND_MESSAGE.Set_Token('COLUMN','SERVICE_REF_LINE_ID', FALSE);
180 FND_MSG_PUB.Add;
181 END IF;
182 raise FND_API.G_EXC_ERROR;
183 ELSE
184 CLOSE C_qln;
185 OPEN C_item1( l_inventory_item_id,l_organization_id);
186 FETCH C_item1 INTO l_serviceable_flag;
187
188 IF aso_debug_pub.g_debug_flag = 'Y' THEN
189 aso_debug_pub.add('Get Service Attr:ref code QUOTE serviceable flag '||l_serviceable_flag, 1, 'Y');
190 END IF;
191
192 IF C_item1%NOTFOUND OR l_serviceable_flag <> 'Y' THEN
193 CLOSE C_item1;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
196 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO1');
197 --FND_MESSAGE.Set_Token('COLUMN','SERVICE_REF_LINE_ID', FALSE);
198 FND_MSG_PUB.Add;
199 END IF;
200 raise FND_API.G_EXC_ERROR;
201
202 END IF;
203 CLOSE C_item1;
204
205 IF aso_debug_pub.g_debug_flag = 'Y' THEN
206
207 aso_debug_pub.add('Get Service Attr:product_item_id '||l_inventory_item_id, 1, 'Y');
208 aso_debug_pub.add('Get Service Attr:service_item_id '||P_Qte_Line_rec.inventory_item_id, 1, 'Y');
209 aso_debug_pub.add('Get Service Attr:customer_id '||l_cust_account_id, 1, 'Y');
210
211 END IF;
212
213 l_check_service_rec.product_item_id := l_inventory_item_id;
214 l_check_service_rec.service_item_id := P_Qte_Line_rec.inventory_item_id;
215 l_check_service_rec.customer_id := l_cust_account_id;
216
217 IF aso_debug_pub.g_debug_flag = 'Y' THEN
218 aso_debug_pub.add('Get Service Attr:ref code QUOTE before Is Service '||l_serviceable_flag, 1, 'Y');
219 END IF;
220
221 ASO_SERVICE_CONTRACTS_INT.Is_Service_Available(
222 P_Api_Version_Number => P_Api_Version_Number ,
223 P_init_msg_list => p_init_msg_list,
224 X_msg_Count => X_msg_count ,
225 X_msg_Data => X_msg_data ,
226 X_Return_Status => X_return_status ,
227 p_check_service_rec => l_check_service_rec,
228 X_Available_YN => l_Available_YN
229 );
230
231 IF aso_debug_pub.g_debug_flag = 'Y' THEN
232 aso_debug_pub.add('Get Service Attr:ref code QUOTE after Is Service '||l_Available_YN);
233 END IF;
234
235 IF l_Available_YN = 'N' THEN
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
238 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO2');
239 --FND_MESSAGE.Set_Token('COLUMN','SERVICE_REF_LINE_ID', FALSE);
240 FND_MSG_PUB.Add;
241 END IF;
242 raise FND_API.G_EXC_ERROR;
243 END IF;
244 END IF;
245 ELSIF P_Qte_Line_Dtl_tbl(i).SERVICE_REF_TYPE_CODE = 'CUSTOMER_PRODUCT' THEN
246
247 IF aso_debug_pub.g_debug_flag = 'Y' THEN
248
249 aso_debug_pub.add('Get Service Attr:SERVICE_REF_TYPE_CODE CUSTOMER_PRODUCT');
250 aso_debug_pub.add('Get Service Attr: CUSTOMER_PRODUCT inv id'||P_Qte_Line_rec.inventory_item_id);
251 aso_debug_pub.add('Get Service Attr: CUSTOMER_PRODUCT organization id'||P_Qte_Line_rec.organization_id);
252 aso_debug_pub.add('Get Service Attr: CUSTOMER_PRODUCT service ref line id'||P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID, 1, 'Y');
253
254 END IF;
255
256 l_cs_org_id := cs_std.get_item_valdn_orgzn_id;
257
258 OPEN C_cs_item( P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID, l_cs_org_id);
259 FETCH C_cs_item INTO l_inventory_item_id;
260 IF C_cs_item%NOTFOUND THEN
261 CLOSE C_cs_item;
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
264 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO3');
265 --FND_MESSAGE.Set_Token('COLUMN','INSTALLBASE', FALSE);
266 FND_MSG_PUB.Add;
267 END IF;
268 raise FND_API.G_EXC_ERROR;
269 END IF;
270 CLOSE C_cs_item;
271
272 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
273 if fnd_profile.value('ASO_FILTER_SERVICE_RF_END_CUST') ='Y' then
274
275 OPEN C_get_cust_line (P_Qte_Line_rec.quote_line_id);
276 FETCH C_get_cust_line INTO ln_end_cust_account_id;
277
278 IF aso_debug_pub.g_debug_flag = 'Y' THEN
279 aso_debug_pub.add('Get Service Attr: line level cust Acct id'||ln_end_cust_account_id);
280 END IF;
281 IF C_get_cust_line%NOTFOUND THEN
282 ln_end_cust_account_id := NULL;
283 end if;
284 if ln_end_cust_account_id is not null then
285 l_cust_account_id := ln_end_cust_account_id;
286 else
287 l_cust_account_id := lh_end_cust_account_id;
288 END IF;
289 CLOSE C_get_cust_line;
290
291 END IF; -- profile check
292
293
294 IF aso_debug_pub.g_debug_flag = 'Y' THEN
295 aso_debug_pub.add('Get Service Attr: cust Acct id'||l_cust_account_id);
296 END IF;
297
298 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
299 l_check_service_rec.product_item_id := l_inventory_item_id;
300 l_check_service_rec.service_item_id := P_Qte_Line_rec.inventory_item_id;
301 l_check_service_rec.customer_id := l_cust_account_id;
302 ASO_SERVICE_CONTRACTS_INT.Is_Service_Available(
303 P_Api_Version_Number => 1.0 ,
304 P_init_msg_list => p_init_msg_list,
305 X_msg_Count => X_msg_count ,
306 X_msg_Data => X_msg_data ,
307 X_Return_Status => X_return_status ,
308 p_check_service_rec => l_check_service_rec,
309 X_Available_YN => l_Available_YN
310 );
311 IF l_Available_YN = 'N' THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
314 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO2');
315 --FND_MESSAGE.Set_Token('COLUMN','SERVICE_REF_LINE_ID', FALSE);
316 FND_MSG_PUB.Add;
317 END IF;
318 raise FND_API.G_EXC_ERROR;
319 END IF;
320 -- END IF;
321 ELSIF P_Qte_Line_Dtl_tbl(i).SERVICE_REF_TYPE_CODE = 'ORDER' THEN
322
323 IF aso_debug_pub.g_debug_flag = 'Y' THEN
324
325 aso_debug_pub.add('Get Service Attr:SERVICE_REF_TYPE_CODE ORDER', 1, 'Y');
326 aso_debug_pub.add('Get Service Attr: ORDER inv id'||P_Qte_Line_rec.inventory_item_id, 1, 'Y');
327 aso_debug_pub.add('Get Service Attr: ORDER organization id'||P_Qte_Line_rec.organization_id, 1, 'Y');
328 aso_debug_pub.add('Get Service Attr: ORDER service ref line id'||P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID, 1, 'Y');
329
330 END IF;
331
332
333 OPEN C_ord_item( P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID);
334 FETCH C_ord_item INTO l_inventory_item_id;
335 IF C_ord_item%NOTFOUND THEN
336 CLOSE C_ord_item;
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
339 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO3');
340 --FND_MESSAGE.Set_Token('COLUMN','ORDER_LINE_ID', FALSE);
341 FND_MSG_PUB.Add;
342 END IF;
343 raise FND_API.G_EXC_ERROR;
344 END IF;
345 CLOSE C_ord_item;
346 l_check_service_rec.product_item_id := l_inventory_item_id;
347 l_check_service_rec.service_item_id := P_Qte_Line_rec.inventory_item_id;
348 l_check_service_rec.customer_id := l_cust_account_id;
349 ASO_SERVICE_CONTRACTS_INT.Is_Service_Available(
350 P_Api_Version_Number => 1.0 ,
351 P_init_msg_list => p_init_msg_list,
352 X_msg_Count => X_msg_count ,
353 X_msg_Data => X_msg_data ,
354 X_Return_Status => X_return_status ,
355 p_check_service_rec => l_check_service_rec,
356 X_Available_YN => l_Available_YN
357 );
358 IF l_Available_YN = 'N' THEN
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
361 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO2');
362 --FND_MESSAGE.Set_Token('COLUMN','SERVICE_REF_LINE_ID', FALSE);
363 FND_MSG_PUB.Add;
364 END IF;
365 raise FND_API.G_EXC_ERROR;
366 END IF;
367 --END IF;
368 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
369 ELSIF P_Qte_Line_Dtl_tbl(i).SERVICE_REF_TYPE_CODE = 'PRODUCT_CATALOG' THEN
370
371 IF aso_debug_pub.g_debug_flag = 'Y' THEN
372
373 aso_debug_pub.add('SUN ER Get Service Attr: PRODUCT_CATALOG');
374 aso_debug_pub.add('SUN ER Get Service Attr: PRODUCT_CATALOG SERVICE_REF_LINE_ID inv id'||P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID);
375 aso_debug_pub.add('SUN ER Get Service Attr: PRODUCT_CATALOG inv id'||P_Qte_Line_rec.inventory_item_id);
376 aso_debug_pub.add('SUN ER Get Service Attr: PRODUCT_CATALOG organization id'||P_Qte_Line_rec.organization_id);
377 aso_debug_pub.add('SUN ER Get Service Attr: PRODUCT_CATALOG service ref line id'||P_Qte_Line_rec.quote_LINE_ID, 1, 'Y');
378
379 END IF;
380 -- Checking for end customer depending on profile 8647883
381 if fnd_profile.value('ASO_FILTER_SERVICE_RF_END_CUST') ='Y' then
382
383 OPEN C_get_cust_line (P_Qte_Line_rec.quote_line_id);
384 FETCH C_get_cust_line INTO ln_end_cust_account_id;
385
386 IF aso_debug_pub.g_debug_flag = 'Y' THEN
387 aso_debug_pub.add('Get Service Attr: line level cust Acct id'||ln_end_cust_account_id);
388 END IF;
389 IF C_get_cust_line%NOTFOUND THEN
390 ln_end_cust_account_id := NULL;
391 end if;
392 if ln_end_cust_account_id is not null then
393 l_cust_account_id := ln_end_cust_account_id;
394 else
395 l_cust_account_id := lh_end_cust_account_id;
396 END IF;
397 CLOSE C_get_cust_line;
398
399 END IF; -- profile check
400
401 l_inventory_item_id:= P_Qte_Line_Dtl_tbl(i).SERVICE_REF_LINE_ID;
402 l_check_service_rec.product_item_id := l_inventory_item_id;
403 l_check_service_rec.service_item_id := P_Qte_Line_rec.inventory_item_id;
404 l_check_service_rec.customer_id := l_cust_account_id;
405 ASO_SERVICE_CONTRACTS_INT.Is_Service_Available(
406 P_Api_Version_Number => 1.0 ,
407 P_init_msg_list => p_init_msg_list,
408 X_msg_Count => X_msg_count ,
409 X_msg_Data => X_msg_data ,
410 X_Return_Status => X_return_status ,
411 p_check_service_rec => l_check_service_rec,
412 X_Available_YN => l_Available_YN
413 );
414 IF l_Available_YN = 'N' THEN
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
417 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_SRV_INFO2');
418 FND_MSG_PUB.Add;
419 END IF;
420 raise FND_API.G_EXC_ERROR;
421 END IF;
422 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
423 END IF;-- If Service_ref_type_code
424
425 END LOOP;
426
427
428
429
430
431 -- Check return status from the above procedure call
432 IF x_return_status = FND_API.G_RET_STS_ERROR then
433 raise FND_API.G_EXC_ERROR;
434 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
435 raise FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437
438 --
439 -- End of API body.
440 --
441
442 /* -- Standard check for p_commit
443 IF FND_API.to_Boolean( p_commit )
444 THEN
445 COMMIT WORK;
446 END IF;
447 */
448
449
450
451 -- Standard call to get message count and if count is 1, get message info.
452 FND_MSG_PUB.Count_And_Get
453 ( p_count => x_msg_count,
454 p_data => x_msg_data
455 );
456
457 EXCEPTION
458 WHEN FND_API.G_EXC_ERROR THEN
459 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
460 P_API_NAME => L_API_NAME
461 ,P_PKG_NAME => G_PKG_NAME
462 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
463 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
464 ,P_SQLCODE => SQLCODE
465 ,P_SQLERRM => SQLERRM
466 ,X_MSG_COUNT => X_MSG_COUNT
467 ,X_MSG_DATA => X_MSG_DATA
468 ,X_RETURN_STATUS => X_RETURN_STATUS);
469
470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
471 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
472 P_API_NAME => L_API_NAME
473 ,P_PKG_NAME => G_PKG_NAME
474 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
475 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
476 ,P_SQLCODE => SQLCODE
477 ,P_SQLERRM => SQLERRM
478 ,X_MSG_COUNT => X_MSG_COUNT
479 ,X_MSG_DATA => X_MSG_DATA
480 ,X_RETURN_STATUS => X_RETURN_STATUS);
481
482 WHEN OTHERS THEN
483 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
484 P_API_NAME => L_API_NAME
485 ,P_PKG_NAME => G_PKG_NAME
486 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
487 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
488 ,P_SQLCODE => SQLCODE
489 ,P_SQLERRM => SQLERRM
490 ,X_MSG_COUNT => X_MSG_COUNT
491 ,X_MSG_DATA => X_MSG_DATA
492 ,X_RETURN_STATUS => X_RETURN_STATUS);
493 END Get_service_attributes;
494
495
496
497
498
499 Procedure Get_Duration
500 (
501 P_Api_Version_Number IN Number,
502 P_init_msg_list IN Varchar2 := FND_API.G_FALSE,
503 X_msg_Count OUT NOCOPY /* file.sql.39 change */ Number,
504 X_msg_Data OUT NOCOPY /* file.sql.39 change */ Varchar2,
505 X_Return_Status OUT NOCOPY /* file.sql.39 change */ Varchar2,
506 P_customer_id IN Number,
507 P_system_id IN Number,
508 P_Service_Duration IN Number,
509 P_service_period IN Varchar2,
510 P_coterm_checked_yn IN Varchar2 := FND_API.G_FALSE,
511 P_start_date IN Date,
512 P_end_date IN Date,
513 X_service_duration OUT NOCOPY /* file.sql.39 change */ Number,
514 X_service_period OUT NOCOPY /* file.sql.39 change */ Varchar2,
515 X_new_end_date OUT NOCOPY /* file.sql.39 change */ Date
516 )
517 IS
518 l_api_version_number NUMBER := 1.0;
519 l_api_name VARCHAR2(50) := 'Get_Duration';
520
521
522 BEGIN
523 -- Standard Start of API savepoint
524 SAVEPOINT GET_DURATION_PUB;
525
526 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
527
528 -- Standard call to check for call compatibility.
529 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
530 p_api_version_number,
531 l_api_name,
532 G_PKG_NAME)
533 THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536
537
538 -- Initialize message list if p_init_msg_list is set to TRUE.
539 IF FND_API.to_Boolean( p_init_msg_list )
540 THEN
541 FND_MSG_PUB.initialize;
542 END IF;
543
544
545 -- Initialize API return status to SUCCESS
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 --
549 -- API body
550 --
551
552 -- ******************************************************************
553 -- Validate Environment
554 -- ******************************************************************
555 IF FND_GLOBAL.User_Id IS NULL
556 THEN
557 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
558 THEN
559 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
560 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
561 FND_MSG_PUB.ADD;
562 END IF;
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565
566 IF aso_debug_pub.g_debug_flag = 'Y' THEN
567
568 aso_utility_pvt.print_login_info();
569 aso_debug_pub.add('ASO_service_contracts_INT.Get_Duration: Before call OKS_OMINT_PUB.Get Duration.',1,'Y');
570 aso_debug_pub.add('Get Duration: p_customer_id: '||p_customer_id, 1, 'Y');
571 aso_debug_pub.add('Get Duration: p_system_id: '||p_system_id, 1, 'Y');
572 aso_debug_pub.add('Get Duration: p_service_duration: '||p_service_duration, 1, 'Y');
573 aso_debug_pub.add('Get Duration: p_service_period: '||p_service_period, 1, 'Y');
574 aso_debug_pub.add('Get Duration: p_coterm_checked_yn: '||p_coterm_checked_yn, 1, 'Y');
575 aso_debug_pub.add('Get Duration: p_start_date: '||p_start_date, 1, 'Y');
576 aso_debug_pub.add('Get Duration: p_end_date: '||p_end_date, 1, 'Y');
577
578 END IF;
579
580
581 OKS_OMINT_PUB.Get_Duration(
582 P_Api_Version => 1.0 ,
583 P_init_msg_list => P_init_msg_list,
584 X_msg_Count => X_msg_count ,
585 X_msg_Data => X_msg_data ,
586 X_Return_Status => X_return_status ,
587 P_customer_id => p_customer_id ,
588 P_system_id => p_system_id ,
589 P_Service_Duration => p_service_duration ,
590 P_service_period => p_service_period ,
591 P_coterm_checked_yn => p_coterm_checked_yn,
592 P_start_date => p_start_date ,
593 P_end_date => p_end_date ,
594 X_service_duration => x_service_duration ,
595 X_service_period => x_service_period ,
596 X_new_end_date => x_new_end_date) ;
597
598
599 IF aso_debug_pub.g_debug_flag = 'Y' THEN
600
601 aso_debug_pub.add('Get Duration: After Call to OKS_OMINT_PUB.Get_Duration: X_return_status: '||X_return_status, 1, 'Y');
602 aso_debug_pub.add('Get Duration: x_service_duration: '||x_service_duration, 1, 'Y');
603 aso_debug_pub.add('Get Duration: x_service_period: '||x_service_period, 1, 'Y');
604 aso_debug_pub.add('Get Duration: x_new_end_date: '||x_new_end_date, 1, 'Y');
605 aso_utility_pvt.print_login_info();
606
607 END IF;
608
609
610 -- Check return status from the above procedure call
611 IF x_return_status = FND_API.G_RET_STS_ERROR then
612 raise FND_API.G_EXC_ERROR;
613 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
614 raise FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616
617 --
618 -- End of API body.
619 --
620
621 /* -- Standard check for p_commit
622 IF FND_API.to_Boolean( p_commit )
623 THEN
624 COMMIT WORK;
625 END IF;
626 */
627
628
629
630 -- Standard call to get message count and if count is 1, get message info.
631 FND_MSG_PUB.Count_And_Get
632 ( p_count => x_msg_count,
633 p_data => x_msg_data
634 );
635
636 EXCEPTION
637 WHEN FND_API.G_EXC_ERROR THEN
638 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
639 P_API_NAME => L_API_NAME
640 ,P_PKG_NAME => G_PKG_NAME
641 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
642 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
643 ,X_MSG_COUNT => X_MSG_COUNT
644 ,X_MSG_DATA => X_MSG_DATA
645 ,X_RETURN_STATUS => X_RETURN_STATUS);
646
647 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
648 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
649 P_API_NAME => L_API_NAME
650 ,P_PKG_NAME => G_PKG_NAME
651 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
652 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
653 ,X_MSG_COUNT => X_MSG_COUNT
654 ,X_MSG_DATA => X_MSG_DATA
655 ,X_RETURN_STATUS => X_RETURN_STATUS);
656
657 WHEN OTHERS THEN
658 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
659 P_API_NAME => L_API_NAME
660 ,P_PKG_NAME => G_PKG_NAME
661 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
662 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
663 ,X_MSG_COUNT => X_MSG_COUNT
664 ,X_MSG_DATA => X_MSG_DATA
665 ,X_RETURN_STATUS => X_RETURN_STATUS);
666 END Get_Duration;
667
668
669
670
671 Procedure Is_Service_Available
672 (
673 P_Api_Version_Number IN Number,
674 P_init_msg_list IN Varchar2 := FND_API.G_FALSE,
675 X_msg_Count OUT NOCOPY /* file.sql.39 change */ Number,
676 X_msg_Data OUT NOCOPY /* file.sql.39 change */ Varchar2,
677 X_Return_Status OUT NOCOPY /* file.sql.39 change */ Varchar2,
678 p_check_service_rec IN CHECK_SERVICE_REC_TYPE,
679 X_Available_YN OUT NOCOPY /* file.sql.39 change */ Varchar2
680 )
681 IS
682 l_api_version_number NUMBER := 1.0;
683 l_api_name VARCHAR2(50) := 'Is_Service_Available';
684
685 l_check_service_rec OKS_OMINT_PUB.CHECK_SERVICE_REC_TYPE;
686 BEGIN
687 -- Standard Start of API savepoint
688 SAVEPOINT IS_SERVICE_AVAILABLE_PUB;
689
690 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
691
692 -- Standard call to check for call compatibility.
693 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
694 p_api_version_number,
695 l_api_name,
696 G_PKG_NAME)
697 THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END IF;
700
701
702 -- Initialize message list if p_init_msg_list is set to TRUE.
703 IF FND_API.to_Boolean( p_init_msg_list )
704 THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707
708
709 -- Initialize API return status to SUCCESS
710 x_return_status := FND_API.G_RET_STS_SUCCESS;
711
712 --
713 -- API body
714 --
715
716 -- ******************************************************************
717 -- Validate Environment
718 -- ******************************************************************
719 IF FND_GLOBAL.User_Id IS NULL
720 THEN
721 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
722 THEN
723 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
724 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
725 FND_MSG_PUB.ADD;
726 END IF;
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 IF aso_debug_pub.g_debug_flag = 'Y' THEN
731
732 aso_debug_pub.add('ASO_SER_INT:IS_service_available: p_check_service_rec.product_item_id '|| p_check_service_rec.product_item_id, 1, 'N');
733 aso_debug_pub.add('ASO_SER_INT:IS_service_available: p_check_service_rec.customer_id '|| p_check_service_rec.customer_id, 1, 'N');
734 aso_debug_pub.add('ASO_SER_INT:IS_service_available: p_check_service_rec.request_date '|| p_check_service_rec.request_date, 1, 'N');
735 aso_debug_pub.add('ASO_SER_INT:IS_service_available: p_check_service_rec.service_item_id '|| p_check_service_rec.service_item_id, 1, 'N');
736 aso_debug_pub.add('ASO_SER_INT:IS_service_available: p_check_service_rec.product_revision '|| p_check_service_rec.product_revision, 1, 'N');
737
738 END IF;
739
740
741 -- map quote rec to oks record type
742 IF p_check_service_rec.product_item_id = FND_API.G_MISS_NUM THEN
743 l_check_service_rec.product_item_id := NULL;
744 ELSE
745 l_check_service_rec.product_item_id := p_check_service_rec.product_item_id;
746 END IF;
747 IF p_check_service_rec.customer_id = FND_API.G_MISS_NUM THEN
748 l_check_service_rec.customer_id := NULL;
749 ELSE
750 l_check_service_rec.customer_id := p_check_service_rec.customer_id;
751 END IF;
752 IF p_check_service_rec.service_item_id = FND_API.G_MISS_NUM THEN
753 l_check_service_rec.service_item_id := NULL;
754 ELSE
755 l_check_service_rec.service_item_id := p_check_service_rec.service_item_id;
756 END IF;
757 IF p_check_service_rec.product_revision = FND_API.G_MISS_CHAR THEN
758 l_check_service_rec.product_revision := NULL;
759 ELSE
760 l_check_service_rec.product_revision := p_check_service_rec.product_revision;
761 END IF;
762 IF p_check_service_rec.request_date = FND_API.G_MISS_DATE THEN
763 l_check_service_rec.request_date := NULL;
764 ELSE
765 l_check_service_rec.request_date := p_check_service_rec.request_date;
766 END IF;
767
768 /*
769 l_check_service_rec.product_item_id := p_check_service_rec.product_item_id;
770 l_check_service_rec.service_item_id := p_check_service_rec.service_item_id;
771 l_check_service_rec.customer_id := p_check_service_rec.customer_id;
772 l_check_service_rec.product_revision:=p_check_service_rec.product_revision;
773 l_check_service_rec.request_date := p_check_service_rec.request_date;
774 */
775
776 IF aso_debug_pub.g_debug_flag = 'Y' THEN
777 aso_debug_pub.add('Before Calling OKS_OMINT_PUB.Is_Service_Available ',1,'Y');
778 aso_utility_pvt.print_login_info();
779 END IF;
780
781 OKS_OMINT_PUB.Is_Service_Available(
782 P_Api_Version => 1.0 ,
783 P_init_msg_list => p_init_msg_list,
784 X_msg_Count => X_msg_count ,
785 X_msg_Data => X_msg_data ,
786 X_Return_Status => X_return_status ,
787 p_check_service_rec => l_check_service_rec,
788 X_Available_YN => X_Available_YN );
789
790 IF aso_debug_pub.g_debug_flag = 'Y' THEN
791 aso_debug_pub.add('After Calling OKS_OMINT_PUB.Is_Service_Available ',1,'Y');
792 aso_utility_pvt.print_login_info();
793 END IF;
794
795
796 -- Check return status from the above procedure call
797 IF x_return_status = FND_API.G_RET_STS_ERROR then
798 raise FND_API.G_EXC_ERROR;
799 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
800 raise FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 --
804 -- End of API body.
805 --
806
807 /* -- Standard check for p_commit
808 IF FND_API.to_Boolean( p_commit )
809 THEN
810 COMMIT WORK;
811 END IF;
812 */
813
814
815 -- Standard call to get message count and if count is 1, get message info.
816 FND_MSG_PUB.Count_And_Get
817 ( p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820
821 EXCEPTION
822 WHEN FND_API.G_EXC_ERROR THEN
823 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
824 P_API_NAME => L_API_NAME
825 ,P_PKG_NAME => G_PKG_NAME
826 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
827 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
828 ,X_MSG_COUNT => X_MSG_COUNT
829 ,X_MSG_DATA => X_MSG_DATA
830 ,X_RETURN_STATUS => X_RETURN_STATUS);
831
832 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
833 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
834 P_API_NAME => L_API_NAME
835 ,P_PKG_NAME => G_PKG_NAME
836 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
837 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
838 ,X_MSG_COUNT => X_MSG_COUNT
839 ,X_MSG_DATA => X_MSG_DATA
840 ,X_RETURN_STATUS => X_RETURN_STATUS);
841
842 WHEN OTHERS THEN
843 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
844 P_API_NAME => L_API_NAME
845 ,P_PKG_NAME => G_PKG_NAME
846 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
847 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
848 ,X_MSG_COUNT => X_MSG_COUNT
849 ,X_MSG_DATA => X_MSG_DATA
850 ,X_RETURN_STATUS => X_RETURN_STATUS);
851
852 END Is_Service_Available;
853
854
855 PROCEDURE Available_Services(
856 P_Api_Version_number IN Number,
857 P_init_msg_list IN Varchar2 := FND_API.G_FALSE,
858 X_msg_Count OUT NOCOPY /* file.sql.39 change */ Number,
859 X_msg_Data OUT NOCOPY /* file.sql.39 change */ Varchar2,
860 X_Return_Status OUT NOCOPY /* file.sql.39 change */ Varchar2,
861 p_avail_service_rec IN AVAIL_SERVICE_REC_TYPE,
862 X_Orderable_Service_tbl OUT NOCOPY /* file.sql.39 change */ order_service_tbl_type
863 )
864 IS
865 l_api_version_number NUMBER := 1.0;
866 l_api_name VARCHAR2(50) := 'Available_Services';
867
868 l_avail_service_rec OKS_OMINT_PUB.AVAIL_SERVICE_REC_TYPE;
869 l_Orderable_Service_tbl OKS_OMINT_PUB.order_service_tbl_type;
870
871
872 BEGIN
873 -- Standard Start of API savepoint
874 SAVEPOINT AVAILABLE_SERVICES_PUB;
875
876 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
877
878 -- Standard call to check for call compatibility.
879 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
880 p_api_version_number,
881 l_api_name,
882 G_PKG_NAME)
883 THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887
888 -- Initialize message list if p_init_msg_list is set to TRUE.
889 IF FND_API.to_Boolean( p_init_msg_list )
890 THEN
891 FND_MSG_PUB.initialize;
892 END IF;
893
894
895 -- Initialize API return status to SUCCESS
896 x_return_status := FND_API.G_RET_STS_SUCCESS;
897
898 --
899 -- API body
900 --
901
902 -- ******************************************************************
903 -- Validate Environment
904 -- ******************************************************************
905 IF FND_GLOBAL.User_Id IS NULL
906 THEN
907 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
908 THEN
909 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
910 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
911 FND_MSG_PUB.ADD;
912 END IF;
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915
916 -- map service rec type
917 IF aso_debug_pub.g_debug_flag = 'Y' THEN
918
919 aso_debug_pub.add('ASO_SER_INT:available_service: p_avail_service_rec.PRODUCT_ITEM_ID '|| p_avail_service_rec.PRODUCT_ITEM_ID, 1, 'N');
920 aso_debug_pub.add('ASO_SER_INT:available_service: p_avail_service_rec.CUSTOMER_ID '|| p_avail_service_rec.CUSTOMER_ID, 1, 'N');
921 aso_debug_pub.add('ASO_SER_INT:available_service: p_avail_service_rec.PRODUCT_REVISION '|| p_avail_service_rec.PRODUCT_REVISION, 1, 'N');
922 aso_debug_pub.add('ASO_SER_INT:available_service: p_avail_service_rec.REQUEST_DATE '|| p_avail_service_rec.REQUEST_DATE, 1, 'N');
923
924 END IF;
925
926 IF p_avail_service_rec.PRODUCT_ITEM_ID = FND_API.G_MISS_NUM THEN
927 l_avail_service_rec.PRODUCT_ITEM_ID := NULL;
928 ELSE
929 l_avail_service_rec.PRODUCT_ITEM_ID := p_avail_service_rec.PRODUCT_ITEM_ID;
930 END IF;
931 IF p_avail_service_rec.CUSTOMER_ID = FND_API.G_MISS_NUM THEN
932 l_avail_service_rec.CUSTOMER_ID := NULL;
933 ELSE
934 l_avail_service_rec.CUSTOMER_ID := p_avail_service_rec.CUSTOMER_ID;
935 END IF;
936 IF p_avail_service_rec.PRODUCT_REVISION = FND_API.G_MISS_CHAR THEN
937 l_avail_service_rec.PRODUCT_REVISION := NULL;
938 ELSE
939 l_avail_service_rec.PRODUCT_REVISION := p_avail_service_rec.PRODUCT_REVISION;
940 END IF;
941 IF p_avail_service_rec.request_date = FND_API.G_MISS_DATE THEN
942 l_avail_service_rec.request_date := NULL;
943 ELSE
944 l_avail_service_rec.request_date := p_avail_service_rec.request_date;
945 END IF;
946 /*
947 l_avail_service_rec.PRODUCT_ITEM_ID := p_avail_service_rec.PRODUCT_ITEM_ID;
948 l_avail_service_rec.CUSTOMER_ID := p_avail_service_rec.CUSTOMER_ID ;
949 l_avail_service_rec.PRODUCT_REVISION := p_avail_service_rec.PRODUCT_REVISION;
950 l_avail_service_rec.REQUEST_DATE := p_avail_service_rec.REQUEST_DATE;
951 */
952
953 IF aso_debug_pub.g_debug_flag = 'Y' THEN
954 aso_debug_pub.add('Before Calling OKS_OMINT_PUB.Available_Services ',1,'Y');
955 aso_utility_pvt.print_login_info();
956 END IF;
957
958 OKS_OMINT_PUB.Available_Services(
959 P_Api_Version => 1.0 ,
960 P_init_msg_list => p_init_msg_list,
961 X_msg_Count => X_msg_count ,
962 X_msg_Data => X_msg_data ,
963 X_Return_Status => X_return_status ,
964 p_avail_service_rec => l_avail_service_rec,
965 X_Orderable_Service_tbl => l_Orderable_Service_tbl
966 );
967
968 IF aso_debug_pub.g_debug_flag = 'Y' THEN
969 aso_debug_pub.add('ASO_SER_INT:After Call to OKS available_service: x_return_status '|| X_return_status, 1, 'Y');
970 aso_utility_pvt.print_login_info();
971 END IF;
972
973 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
974 FOR i in 1..l_Orderable_Service_tbl.count LOOP
975 x_orderable_service_tbl(i).service_item_id := l_Orderable_Service_tbl(i).service_item_id;
976 END LOOP;
977 END IF;
978
979
980 -- Check return status from the above procedure call
981 IF x_return_status = FND_API.G_RET_STS_ERROR then
982 raise FND_API.G_EXC_ERROR;
983 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
984 raise FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986
987 --
988 -- End of API body.
989 --
990
991 /* -- Standard check for p_commit
992 IF FND_API.to_Boolean( p_commit )
993 THEN
994 COMMIT WORK;
995 END IF;
996 */
997
998
999
1000 -- Standard call to get message count and if count is 1, get message info.
1001 FND_MSG_PUB.Count_And_Get
1002 ( p_count => x_msg_count,
1003 p_data => x_msg_data
1004 );
1005
1006 EXCEPTION
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1009 P_API_NAME => L_API_NAME
1010 ,P_PKG_NAME => G_PKG_NAME
1011 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1012 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1013 ,X_MSG_COUNT => X_MSG_COUNT
1014 ,X_MSG_DATA => X_MSG_DATA
1015 ,X_RETURN_STATUS => X_RETURN_STATUS);
1016
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1019 P_API_NAME => L_API_NAME
1020 ,P_PKG_NAME => G_PKG_NAME
1021 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1022 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1023 ,X_MSG_COUNT => X_MSG_COUNT
1024 ,X_MSG_DATA => X_MSG_DATA
1025 ,X_RETURN_STATUS => X_RETURN_STATUS);
1026
1027 WHEN OTHERS THEN
1028 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1029 P_API_NAME => L_API_NAME
1030 ,P_PKG_NAME => G_PKG_NAME
1031 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1032 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1033 ,X_MSG_COUNT => X_MSG_COUNT
1034 ,X_MSG_DATA => X_MSG_DATA
1035 ,X_RETURN_STATUS => X_RETURN_STATUS);
1036
1037 END Available_Services;
1038
1039
1040 Procedure Get_Warranty (
1041 P_Api_Version_Number IN Number,
1042 P_init_msg_list IN Varchar2 := FND_API.G_FALSE,
1043 X_msg_Count OUT NOCOPY /* file.sql.39 change */ Number,
1044 X_msg_Data OUT NOCOPY /* file.sql.39 change */ Varchar2,
1045 P_Org_id IN Number,
1046 P_Organization_id IN NUMBER := null,
1047 P_product_item_id IN Number,
1048 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2,
1049 X_Warranty_tbl OUT NOCOPY /* file.sql.39 change */ War_tbl_type )
1050 IS
1051
1052 CURSOR C_warranty(item_id NUMBER) IS
1053 SELECT description, concatenated_segments
1054 -- SELECT description, segment1
1055 -- FROM aso_i_items_v
1056 FROM mtl_system_items_vl
1057 WHERE inventory_item_id = item_id
1058 AND organization_id = p_organization_id;
1059 /* AND bom_item_type in (1,4)
1060 AND inventory_item_status_code = 'Active'
1061 AND customer_order_enabled_flag = 'Y'; */
1062
1063 l_api_version_number NUMBER := 1.0;
1064 l_api_name VARCHAR2(50) := 'Get_Warranty';
1065
1066 l_warranty_tbl OKS_EXTWAR_UTIL_PUB.War_tbl;
1067 l_description VARCHAR2(240);
1068 l_concatenated_segments VARCHAR2(2000);
1069
1070 BEGIN
1071 -- Standard Start of API savepoint
1072 SAVEPOINT GET_WARRANTY_PUB;
1073
1074 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1075
1076 -- Standard call to check for call compatibility.
1077 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1078 p_api_version_number,
1079 l_api_name,
1080 G_PKG_NAME)
1081 THEN
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 END IF;
1084
1085
1086 -- Initialize message list if p_init_msg_list is set to TRUE.
1087 IF FND_API.to_Boolean( p_init_msg_list )
1088 THEN
1089 FND_MSG_PUB.initialize;
1090 END IF;
1091
1092
1093 -- Initialize API return status to SUCCESS
1094 x_return_status := FND_API.G_RET_STS_SUCCESS;
1095
1096 --
1097 -- API body
1098 --
1099
1100 -- ******************************************************************
1101 -- Validate Environment
1102 -- ******************************************************************
1103 IF FND_GLOBAL.User_Id IS NULL
1104 THEN
1105 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1106 THEN
1107 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
1108 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
1109 FND_MSG_PUB.ADD;
1110 END IF;
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113
1114 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1115 aso_debug_pub.add('Before Call to OKS_EXTWAR_UTIL_PUB.Get_Warranty_info', 1, 'Y');
1116 aso_utility_pvt.print_login_info();
1117 END IF;
1118
1119 OKS_EXTWAR_UTIL_PUB.Get_Warranty_info(
1120 P_Api_Version => 1.0 ,
1121 P_init_msg_list => FND_API.G_FALSE,
1122 P_Org_id => P_Org_id ,
1123 P_prod_item_id => P_product_item_id ,
1124 X_Return_Status => X_return_status ,
1125 X_msg_Count => X_msg_count ,
1126 X_msg_Data => X_msg_data ,
1127 X_Warranty_tbl => l_Warranty_tbl );
1128 -- map the output
1129
1130 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1131 aso_debug_pub.add('After Call to OKS_EXTWAR_UTIL_PUB.Get_Warranty_info', 1, 'Y');
1132 aso_utility_pvt.print_login_info();
1133 END IF;
1134
1135
1136 For i in 1..l_warranty_tbl.count LOOP
1137 IF p_organization_id is not null THEN
1138 OPEN C_warranty(l_Warranty_tbl(i).Service_item_id);
1139 FETCH C_warranty INTO l_description,
1140 l_concatenated_segments;
1141 IF (C_warranty%FOUND) THEN
1142 CLOSE C_warranty;
1143 X_Warranty_tbl(i).Service_item_id := l_Warranty_tbl(i).Service_item_id;
1144 X_Warranty_tbl(i).Duration_Quantity:= l_Warranty_tbl(i).Duration_Quantity;
1145 X_Warranty_tbl(i).Duration_Period := l_Warranty_tbl(i).Duration_Period;
1146 X_Warranty_tbl(i).Coverage_Schedule_id
1147 := l_Warranty_tbl(i).Coverage_Schedule_id ;
1148 X_Warranty_tbl(i).Warranty_Start_Date
1149 := l_Warranty_tbl(i).Warranty_Start_Date;
1150 X_Warranty_tbl(i).Warranty_End_Date := l_Warranty_tbl(i).Warranty_End_Date;
1151 X_Warranty_tbl(i).service_description := l_description;
1152 X_Warranty_tbl(i).service_name := l_concatenated_segments;
1153 ELSE
1154 -- raise FND_API.G_EXC_ERROR;
1155 CLOSE C_warranty;
1156 END IF;
1157 END IF;
1158 END LOOP;
1159
1160
1161 -- Check return status from the above procedure call
1162 IF x_return_status = FND_API.G_RET_STS_ERROR then
1163 raise FND_API.G_EXC_ERROR;
1164 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1165 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1166 END IF;
1167
1168 --
1169 -- End of API body.
1170 --
1171
1172
1173 -- Standard call to get message count and if count is 1, get message info.
1174 FND_MSG_PUB.Count_And_Get
1175 ( p_count => x_msg_count,
1176 p_data => x_msg_data
1177 );
1178
1179 EXCEPTION
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1182 P_API_NAME => L_API_NAME
1183 ,P_PKG_NAME => G_PKG_NAME
1184 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1185 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1186 ,X_MSG_COUNT => X_MSG_COUNT
1187 ,X_MSG_DATA => X_MSG_DATA
1188 ,X_RETURN_STATUS => X_RETURN_STATUS);
1189
1190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1191 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1192 P_API_NAME => L_API_NAME
1193 ,P_PKG_NAME => G_PKG_NAME
1194 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1195 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1196 ,X_MSG_COUNT => X_MSG_COUNT
1197 ,X_MSG_DATA => X_MSG_DATA
1198 ,X_RETURN_STATUS => X_RETURN_STATUS);
1199
1200 WHEN OTHERS THEN
1201 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1202 P_API_NAME => L_API_NAME
1203 ,P_PKG_NAME => G_PKG_NAME
1204 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1205 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
1206 ,X_MSG_COUNT => X_MSG_COUNT
1207 ,X_MSG_DATA => X_MSG_DATA
1208 ,X_RETURN_STATUS => X_RETURN_STATUS);
1209
1210 END Get_Warranty;
1211
1212
1213 End ASO_service_contracts_INT;