[Home] [Help]
PACKAGE BODY: APPS.OE_SERVICE_UTIL
Source
1 Package body OE_SERVICE_UTIL As
2 /* $Header: OEXUSVCB.pls 120.9.12010000.2 2008/11/14 11:28:28 smanian ship $ */
3
4 G_ASO_STATUS VARCHAR2(1) := FND_API.G_MISS_CHAR;
5 G_OKC_STATUS VARCHAR2(1) := FND_API.G_MISS_CHAR;
6
7 g_customer_id number := NULL; -- 2225343
8
9 Function Get_Product_Status(p_application_id NUMBER)
10 RETURN VARCHAR2 IS
11 l_ret_val BOOLEAN;
12 l_status VARCHAR2(1);
13 l_industry VARCHAR2(1);
14 --
15 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
16 --
17 BEGIN
18
19 if (p_application_id = 697
20 AND G_ASO_STATUS = FND_API.G_MISS_CHAR)
21 or ( p_application_id = 515
22 AND G_OKC_STATUS = FND_API.G_MISS_CHAR)
23 then
24 IF l_debug_level > 0 THEN
25 oe_debug_pub.add( 'GET PROD. STATUS' ) ;
26 END IF;
27
28 -- Make a call to fnd_installation.get function to check for the
29 -- installation status of the CRM products and return the status.
30
31 l_ret_val := fnd_installation.get(p_application_id,p_application_id
32 ,l_status,l_industry);
33 if p_application_id = 697 then
34 G_ASO_STATUS := l_status;
35 elsif p_application_id = 515 then
36 G_OKC_STATUS := l_status;
37 end if;
38
39 end if;
40
41 if p_application_id = 697 then
42 IF l_debug_level > 0 THEN
43 oe_debug_pub.add( 'RET PROD. STATUS :'||G_ASO_STATUS ) ;
44 END IF;
45 return (G_ASO_STATUS);
46 elsif p_application_id = 515 then
47 return (G_OKC_STATUS);
48 end if;
49
50 END Get_Product_Status;
51
52 Procedure Notify_OC
53 ( p_api_version_number IN NUMBER
54 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
55 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
56 , p_control_rec IN OE_GLOBALS.Control_Rec_Type :=
57 OE_GLOBALS.G_MISS_CONTROL_REC
58 , x_return_status OUT NOCOPY VARCHAR2
59
60 , x_msg_count OUT NOCOPY NUMBER
61
62 , x_msg_data OUT NOCOPY VARCHAR2
63
64 , p_header_rec IN OE_Order_PUB.Header_Rec_Type :=
65 OE_Order_PUB.G_MISS_HEADER_REC
66 , p_old_header_rec IN OE_Order_PUB.Header_Rec_Type :=
67 OE_Order_PUB.G_MISS_HEADER_REC
68 , p_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
69 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL
70 , p_old_Header_Adj_tbl IN OE_Order_PUB.Header_Adj_Tbl_Type :=
71 OE_Order_PUB.G_MISS_HEADER_ADJ_TBL
72 , p_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
73 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL
74 , p_old_Header_Price_Att_tbl IN OE_Order_PUB.Header_Price_Att_Tbl_Type :=
75 OE_Order_PUB.G_MISS_HEADER_PRICE_ATT_TBL
76 , p_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
77 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL
78 , p_old_Header_Adj_Att_tbl IN OE_Order_PUB.Header_Adj_Att_Tbl_Type :=
79 OE_Order_PUB.G_MISS_HEADER_ADJ_ATT_TBL
80 , p_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
81 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
82 , p_old_Header_Adj_Assoc_tbl IN OE_Order_PUB.Header_Adj_Assoc_Tbl_Type :=
83 OE_Order_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL
84 , p_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
85 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL
86 , p_old_Header_Scredit_tbl IN OE_Order_PUB.Header_Scredit_Tbl_Type :=
87 OE_Order_PUB.G_MISS_HEADER_SCREDIT_TBL
88 , p_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
89 OE_Order_PUB.G_MISS_LINE_TBL
90 , p_old_line_tbl IN OE_Order_PUB.Line_Tbl_Type :=
91 OE_Order_PUB.G_MISS_LINE_TBL
92 , p_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
93 OE_Order_PUB.G_MISS_LINE_ADJ_TBL
94 , p_old_Line_Adj_tbl IN OE_Order_PUB.Line_Adj_Tbl_Type :=
95 OE_Order_PUB.G_MISS_LINE_ADJ_TBL
96 , p_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
97 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL
98 , p_old_Line_Price_Att_tbl IN OE_Order_PUB.Line_Price_Att_Tbl_Type :=
99 OE_Order_PUB.G_MISS_LINE_PRICE_ATT_TBL
100 , p_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
101 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL
102 , p_old_Line_Adj_Att_tbl IN OE_Order_PUB.Line_Adj_Att_Tbl_Type :=
103 OE_Order_PUB.G_MISS_LINE_ADJ_ATT_TBL
104 , p_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
105 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
106 , p_old_Line_Adj_Assoc_tbl IN OE_Order_PUB.Line_Adj_Assoc_Tbl_Type :=
107 OE_Order_PUB.G_MISS_LINE_ADJ_ASSOC_TBL
108 , p_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
109 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL
110 , p_old_Line_Scredit_tbl IN OE_Order_PUB.Line_Scredit_Tbl_Type :=
111 OE_Order_PUB.G_MISS_LINE_SCREDIT_TBL
112 , p_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
113 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL
114 , p_old_Lot_Serial_tbl IN OE_Order_PUB.Lot_Serial_Tbl_Type :=
115 OE_Order_PUB.G_MISS_LOT_SERIAL_TBL
116 , p_Lot_Serial_val_tbl IN OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
117 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL
118 , p_old_Lot_Serial_val_tbl IN OE_Order_PUB.Lot_Serial_Val_Tbl_Type :=
119 OE_Order_PUB.G_MISS_LOT_SERIAL_VAL_TBL
120 , p_action_request_tbl IN OE_Order_PUB.request_tbl_type :=
121 OE_Order_PUB.g_miss_request_tbl
122 )
123 IS
124
125 l_number NUMBER := 0;
126 l_api_name CONSTANT VARCHAR(30) := 'NOTIFY_OC';
127 l_return_status VARCHAR2(1);
128 l_control_rec OE_GLOBALS.Control_Rec_Type;
129 l_sql_stat VARCHAR2(3000);
130 l_init_msg_list VARCHAR2(240);
131 l_commit VARCHAR2(1);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(2000);
134 l_bypass_notify_oc VARCHAR2(30) := nvl(FND_PROFILE.VALUE('ONT_BYPASS_NOTIFY_OC'),'N');
135 l_buffer VARCHAR2(2000);
136
137 --
138 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
139 --
140 BEGIN
141
142 IF l_debug_level > 0 THEN
143 oe_debug_pub.add( 'ENTERING NOTIFY_OC API' ) ;
144 END IF;
145
146 -- Call Describe_Proc to check for the existance of the CRM's
147 -- Update_Notice API. If exists Then Call it else No Problem.
148
149 -- Commenting out the call to check proc for performance improvement
150 /*
151 OE_SERVICE_UTIL.CHECK_PROC('ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE', l_return_status);
152 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
153 */
154
155 /* The application id for Order Capture is 697 */
156
157 -- IF Get_Product_Status(697) IN ('I','S') THEN
158
159 -- lkxu, for bug 1701377
160 IF OE_GLOBALS.G_ASO_INSTALLED IS NULL THEN
161 OE_GLOBALS.G_ASO_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(697);
162 END IF;
163
164 IF OE_GLOBALS.G_ASO_INSTALLED = 'Y' THEN
165
166
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add( 'BYPASS VALUE:' || L_BYPASS_NOTIFY_OC ) ;
169 END IF;
170 IF l_bypass_notify_oc = 'Y' then
171 IF l_debug_level > 0 THEN
172 oe_debug_pub.add( 'BYPASSING THE CALL TO NOTIFY OC' , 1 ) ;
173 END IF;
174 ELSE
175
176 -- Call Update_Notice API Using Native Dynamic SQL
177
178 /*
179 * csheu Fixed bug #1677930 replaced the following call to static call
180 *
181 l_sql_stat := '
182 Begin
183 ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE(
184 1.0
185 , :p_init_msg_list
186 , :p_commit
187 , :x_return_status
188 , :x_msg_count
189 , :x_msg_data
190 , OE_SERVICE_UTIL.g_header_rec
191 , OE_SERVICE_UTIL.g_old_header_rec
192 , OE_SERVICE_UTIL.g_header_adj_tbl
193 , OE_SERVICE_UTIL.g_old_header_adj_tbl
194 , OE_SERVICE_UTIL.g_header_price_att_tbl
195 , OE_SERVICE_UTIL.g_old_header_price_att_tbl
196 , OE_SERVICE_UTIL.g_header_adj_att_tbl
197 , OE_SERVICE_UTIL.g_old_header_adj_att_tbl
198 , OE_SERVICE_UTIL.g_header_adj_assoc_tbl
199 , OE_SERVICE_UTIL.g_old_header_adj_assoc_tbl
200 , OE_SERVICE_UTIL.g_header_scredit_tbl
201 , OE_SERVICE_UTIL.g_old_header_scredit_tbl
202 , OE_SERVICE_UTIL.g_line_tbl
203 , OE_SERVICE_UTIL.g_old_line_tbl
204 , OE_SERVICE_UTIL.g_line_adj_tbl
205 , OE_SERVICE_UTIL.g_old_line_adj_tbl
206 , OE_SERVICE_UTIL.g_line_price_att_tbl
207 , OE_SERVICE_UTIL.g_old_line_price_att_tbl
208 , OE_SERVICE_UTIL.g_line_adj_att_tbl
209 , OE_SERVICE_UTIL.g_old_line_adj_att_tbl
210 , OE_SERVICE_UTIL.g_line_adj_assoc_tbl
211 , OE_SERVICE_UTIL.g_old_line_adj_assoc_tbl
212 , OE_SERVICE_UTIL.g_line_scredit_tbl
213 , OE_SERVICE_UTIL.g_old_line_scredit_tbl
214 , OE_SERVICE_UTIL.g_lot_serial_tbl
215 , OE_SERVICE_UTIL.g_old_lot_serial_tbl
216 , OE_SERVICE_UTIL.g_action_request_tbl);
217 END;';
218
219
220 EXECUTE IMMEDIATE l_sql_stat
221 USING IN l_init_msg_list
222 , IN l_commit
223 , OUT NOCOPY l_return_status
224
225 , OUT NOCOPY x_msg_count
226
227 , OUT NOCOPY x_msg_data;
228
229 */
230 -- Bug 5603656
231 -- Moved the code to the else part
232 -- Assign the value of the passed parameters to the Global variable
233 OE_SERVICE_UTIL.g_Header_Rec := p_Header_Rec;
234 OE_SERVICE_UTIL.g_old_header_rec := p_old_header_rec ;
235 OE_SERVICE_UTIL.g_Header_Adj_tbl := p_Header_Adj_tbl;
236 OE_SERVICE_UTIL.g_old_Header_Adj_tbl := p_old_Header_Adj_tbl;
237
238 /* Notification Project changes */
239 /* Comment out nocopy the calls to entities that are not used by the subscriber */
240
241
242 -- OE_SERVICE_UTIL.g_Header_Price_Att_tbl := p_Header_Price_Att_tbl;
243 -- OE_SERVICE_UTIL.g_old_Header_Price_Att_tbl := p_old_Header_Price_Att_tbl;
244 -- OE_SERVICE_UTIL.g_Header_Adj_Att_tbl := p_Header_Adj_Att_tbl;
245 -- OE_SERVICE_UTIL.g_old_Header_Adj_Att_tbl := p_old_Header_Adj_Att_tbl;
246 -- OE_SERVICE_UTIL.g_Header_Adj_Assoc_tbl := p_Header_Adj_Assoc_tbl;
247 -- OE_SERVICE_UTIL.g_old_Header_Adj_Assoc_tbl := p_old_Header_Adj_Assoc_tbl;
248 OE_SERVICE_UTIL.g_Header_Scredit_tbl := p_Header_Scredit_tbl;
249 OE_SERVICE_UTIL.g_old_Header_Scredit_tbl := p_old_Header_Scredit_tbl;
250 OE_SERVICE_UTIL.g_line_tbl := p_line_tbl;
251 OE_SERVICE_UTIL.g_old_line_tbl := p_old_line_tbl;
252 OE_SERVICE_UTIL.g_Line_Adj_tbl := p_Line_Adj_tbl;
253 OE_SERVICE_UTIL.g_old_Line_Adj_tbl := p_old_Line_Adj_tbl;
254 -- OE_SERVICE_UTIL.g_Line_Price_Att_tbl := p_Line_Price_Att_tbl;
255 -- OE_SERVICE_UTIL.g_old_Line_Price_Att_tbl := p_old_Line_Price_Att_tbl;
256 -- OE_SERVICE_UTIL.g_Line_Adj_Att_tbl := p_Line_Adj_Att_tbl;
257 -- OE_SERVICE_UTIL.g_old_Line_Adj_Att_tbl := p_old_Line_Adj_Att_tbl;
258 -- OE_SERVICE_UTIL.g_Line_Adj_Assoc_tbl := p_Line_Adj_Assoc_tbl;
259 -- OE_SERVICE_UTIL.g_old_Line_Adj_Assoc_tbl := p_old_Line_Adj_Assoc_tbl;
260 OE_SERVICE_UTIL.g_Line_Scredit_tbl := p_Line_Scredit_tbl;
261 OE_SERVICE_UTIL.g_old_Line_Scredit_tbl := p_old_Line_Scredit_tbl;
262 OE_SERVICE_UTIL.g_Lot_Serial_tbl := p_Lot_Serial_tbl;
263 OE_SERVICE_UTIL.g_old_Lot_Serial_tbl := p_old_Lot_Serial_tbl;
264 -- OE_SERVICE_UTIL.g_Lot_Serial_val_tbl := p_Lot_Serial_val_tbl;
265 -- OE_SERVICE_UTIL.g_old_Lot_Serial_val_tbl := p_old_Lot_Serial_val_tbl;
266 OE_SERVICE_UTIL.g_action_request_tbl := p_action_request_tbl;
267
268
269 IF l_debug_level > 0 THEN
270 oe_debug_pub.add( 'CSS: BEFORE CALLS UPDATE_NOTICE ' ) ;
271 END IF;
272
273 ASO_ORDER_FEEDBACK_PUB.UPDATE_NOTICE(
274 p_api_version => 1.0
275 , p_init_msg_list => l_init_msg_list
276 , p_commit => l_commit
277 , x_return_status => l_return_status
278 , x_msg_count => l_msg_count
279 , x_msg_data => l_msg_data
280 , p_header_rec => OE_SERVICE_UTIL.g_header_rec
281 , p_old_header_rec => OE_SERVICE_UTIL.g_old_header_rec
282 , p_Header_Adj_tbl => OE_SERVICE_UTIL.g_header_adj_tbl
283 , p_old_Header_Adj_tbl => OE_SERVICE_UTIL.g_old_header_adj_tbl
284 , p_Header_price_Att_tbl => OE_SERVICE_UTIL.g_header_price_att_tbl
285 , p_old_Header_Price_Att_tbl => OE_SERVICE_UTIL.g_old_header_price_att_tbl
286 , p_Header_Adj_Att_tbl => OE_SERVICE_UTIL.g_header_adj_att_tbl
287 , p_old_Header_Adj_Att_tbl => OE_SERVICE_UTIL.g_old_header_adj_att_tbl
288 , p_Header_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_header_adj_assoc_tbl
289 , p_old_Header_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_old_header_adj_assoc_tbl
290 , p_Header_Scredit_tbl => OE_SERVICE_UTIL.g_header_scredit_tbl
291 , p_old_Header_Scredit_tbl => OE_SERVICE_UTIL.g_old_header_scredit_tbl
292 , p_line_tbl => OE_SERVICE_UTIL.g_line_tbl
293 , p_old_line_tbl => OE_SERVICE_UTIL.g_old_line_tbl
294 , p_Line_Adj_tbl => OE_SERVICE_UTIL.g_line_adj_tbl
295 , p_old_Line_Adj_tbl => OE_SERVICE_UTIL.g_old_line_adj_tbl
296 , p_Line_Price_Att_tbl => OE_SERVICE_UTIL.g_line_price_att_tbl
297 , p_old_Line_Price_Att_tbl => OE_SERVICE_UTIL.g_old_line_price_att_tbl
298 , p_Line_Adj_Att_tbl => OE_SERVICE_UTIL.g_line_adj_att_tbl
299 , p_old_Line_Adj_Att_tbl => OE_SERVICE_UTIL.g_old_line_adj_att_tbl
300 , p_Line_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_line_adj_assoc_tbl
301 , p_old_Line_Adj_Assoc_tbl => OE_SERVICE_UTIL.g_old_line_adj_assoc_tbl
302 , p_Line_Scredit_tbl => OE_SERVICE_UTIL.g_line_scredit_tbl
303 , p_old_Line_Scredit_tbl => OE_SERVICE_UTIL.g_old_line_scredit_tbl
304 , p_Lot_Serial_tbl => OE_SERVICE_UTIL.g_lot_serial_tbl
305 , p_old_Lot_Serial_tbl => OE_SERVICE_UTIL.g_old_lot_serial_tbl
306 , p_action_request_tbl => OE_SERVICE_UTIL.g_action_request_tbl);
307
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'JPN: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
310 END IF;
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add( 'CSS: OC RETURN STATUS IS: ' || L_RETURN_STATUS ) ;
313 END IF;
314
315 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add( 'NOTIFY_OC API - UNEXPECTED ERROR' ) ;
318 END IF;
319 IF l_debug_level > 0 THEN
320 oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
321 END IF;
322 Retrieve_OC_Messages;
323 /* OE_DEBUG_PUB.ADD('Notify OC error msg is: ' || substr(x_msg_data, 1,200)); */
324 -- For bug 3574480. Modified unepected error to expected error to
325 -- support orders of this kind to be rebooked.
326 RAISE FND_API.G_EXC_ERROR;
327 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add( 'NOTIFY_OC API - ERROR' ) ;
330 END IF;
331 IF l_debug_level > 0 THEN
332 oe_debug_pub.add( 'EXITING NOTIFY_OC API' ) ;
333 END IF;
334 IF l_msg_data is not null THEN
335 /* fnd_message.set_encoded(l_msg_data);
336 l_buffer := fnd_message.get;
337 oe_msg_pub.add_text(p_message_text => l_buffer);
338 IF l_debug_level > 0 THEN
339 oe_debug_pub.add( L_BUFFER , 1 ) ;
340 END IF;*/
341 Retrieve_OC_Messages;
342 --RAISE FND_API.G_EXC_ERROR;
343 END IF;
344
345 END IF;
346
347 END IF; -- Bypass Notify_OC call
348 END IF; -- API exists
349
350 EXCEPTION
351
352
353 WHEN FND_API.G_EXC_ERROR THEN
354
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 -- For bug 3574480. Modified unexpected errors also to return normal error
357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
358
359 x_return_status := FND_API.G_RET_STS_ERROR;
360
361 WHEN OTHERS THEN
362
363 x_return_status := FND_API.G_RET_STS_ERROR;
364
365 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
366 THEN
367 OE_MSG_PUB.Add_Exc_Msg
368 ( G_PKG_NAME
369 , 'NOTIFY_OC'
370 );
371 END IF;
372 RAISE FND_API.G_EXC_ERROR;
373
374 END NOTIFY_OC;
375
376 -- Procedure to Check for the availability of the CRM APIS
377
378 Procedure Check_Proc
379 (
380 p_procedure_name IN varchar2,
381 x_return_status OUT NOCOPY varchar2
382
383 )
384 is
385
386 l_overload dbms_describe.number_table;
387 l_position dbms_describe.number_table;
388 l_level dbms_describe.number_table;
389 l_argumentname dbms_describe.varchar2_table;
390 l_datatype dbms_describe.number_table;
391 l_defaultvalue dbms_describe.number_table;
392 l_inout dbms_describe.number_table;
393 l_length dbms_describe.number_table;
394 l_precision dbms_describe.number_table;
395 l_scale dbms_describe.number_table;
396 l_radix dbms_describe.number_table;
397 l_spare dbms_describe.number_table;
398
399 --
400 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
401 --
402 BEGIN
403
404 IF l_debug_level > 0 THEN
405 oe_debug_pub.add( 'ENTERING CHECK_PROC API' ) ;
406 END IF;
407 BEGIN
408 dbms_describe.describe_procedure
409 (
410 p_procedure_name,
411 null,
412 null,
413 l_overload,
414 l_position,
415 l_level,
416 l_argumentname,
417 l_datatype,
418 l_defaultvalue,
419 l_inout,
420 l_length,
421 l_precision,
422 l_scale,
423 l_radix,
424 l_spare
425 );
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427 EXCEPTION
428 WHEN OTHERS THEN
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 END;
431 IF l_debug_level > 0 THEN
432 oe_debug_pub.add( 'EXITING CHECK_PROC API' ) ;
433 END IF;
434 END Check_Proc;
435
436 PROCEDURE Get_Service_Start -- added for bug 2897505
437 ( p_line_id IN NUMBER
438 , x_start_date OUT NOCOPY DATE
439 , x_return_status OUT NOCOPY VARCHAR2
440 )
441 IS
442 l_return_status VARCHAR2(1);
443 l_init_msg_list VARCHAR2(1);
444 l_api_version NUMBER := 1.0;
445 l_sql_stat VARCHAR2(3000);
446 l_msg_count NUMBER;
447 l_msg_data VARCHAR2(2000);
448 l_end_date DATE;
449 l_start_date DATE;
450 --
451 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
452 --
453 BEGIN
454
455 IF l_debug_level > 0 THEN
456 oe_debug_pub.add( 'ENTERING GET_SERVICE_START' ) ;
457 END IF;
458
459 IF l_debug_level > 0 THEN
460 oe_debug_pub.add( 'LINE ID PASSED IS ' || to_char(p_line_id) ) ;
461 END IF;
462
463 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
464 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
465 END IF;
466
467 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
468
469 l_sql_stat := '
470 Begin
471 OKS_OMINT_PUB.GET_SVC_SDATE(
472 :p_api_version
473 , :p_init_msg_list
474 , :p_order_line_id
475 , :x_msg_count
476 , :x_msg_data
477 , :x_return_status
478 , :x_start_date
479 , :x_end_date);
480 END;';
481
482 BEGIN -- to recover from any unexpected errors, such as OKS_OMINT_PUB.GET_SVC_SDATE not defined
483 EXECUTE IMMEDIATE l_sql_stat
484 USING IN l_api_version
485 , IN l_init_msg_list
486 , IN p_line_id
487 , OUT l_msg_count
488 , OUT l_msg_data
489 , OUT l_return_status
490 , OUT l_start_date
491 , OUT l_end_date;
492 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
493 IF l_debug_level > 0 THEN
494 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_SVC_SDATE SUCCESSFUL' ) ;
495 END IF;
496 IF l_debug_level > 0 THEN
497 oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
498 END IF;
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add( 'START_DATE ==> ' || TO_CHAR ( L_START_DATE ) ) ;
501 END IF;
502 x_start_date := l_start_date;
503 x_return_status := l_return_status;
504 IF l_debug_level > 0 THEN
505 oe_debug_pub.add( 'EXITING GET_SERVICE_START' ) ;
506 END IF;
507 RETURN;
508 END IF;
509
510 x_return_status := l_return_status;
511 IF l_debug_level > 0 THEN
512 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_SVC_SDATE RETURNED STATUS:'||
513 x_return_status ) ;
514 END IF;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 IF l_debug_level > 0 THEN
519 oe_debug_pub.add('Unexpected error calling OKS_OMINT_PUB.GET_SVC_SDATE:');
520 oe_debug_pub.add(sqlerrm);
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 RETURN;
523 END IF;
524 END; -- to recover from any unexpected errors
525
526 ELSE
527 IF l_debug_level > 0 THEN
528 oe_debug_pub.add( 'SERVICE CONTRACTS NOT INSTALLED' ) ;
529 END IF;
530 END IF;
531
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'EXITING GET_SERVICE_START' ) ;
534 END IF;
535 END Get_Service_Start;
536
537 -- Procedure : Get_Service_Duration
538 --
539
540 PROCEDURE Get_Service_Duration
541 ( x_return_status OUT NOCOPY VARCHAR2
542
543 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
544 )
545 IS
546 l_return_status VARCHAR2(1);
547 l_init_msg_list VARCHAR2(1);
548 l_api_version NUMBER := 1.0;
549 l_sql_stat VARCHAR2(3000);
550 l_msg_count NUMBER;
551 l_msg_data VARCHAR2(2000);
552 l_new_end_date DATE;
553 l_system_id NUMBER;
554 l_service_duration NUMBER;
555 l_service_period VARCHAR2(3);
556 --
557 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
558 --
559 BEGIN
560 -- oe_debug_pub.Debug_On;
561
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
564 END IF;
565
566 /* The following IF added for 2897505 */
567 IF p_x_line_rec.service_reference_type_code = 'GET_SVC_START' THEN
568 Get_Service_Start(p_line_id => p_x_line_rec.line_id,
569 x_start_date => p_x_line_rec.service_start_date,
570 x_return_status => x_return_status);
571 RETURN;
572 END IF;
573
574 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.GET_DURATION', l_return_status); */
575
576
577 IF l_debug_level > 0 THEN
578 oe_debug_pub.add( 'JPN: SERVICE DURATION PASSED IS ' || P_X_LINE_REC.SERVICE_DURATION ) ;
579 END IF;
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add( 'JPN: SERVICE DATE PASSED IS ' || P_X_LINE_REC.SERVICE_START_DATE ) ;
582 END IF;
583 IF l_debug_level > 0 THEN
584 oe_debug_pub.add( 'JPN: SERVICE END DATE PASSED IS ' || P_X_LINE_REC.SERVICE_END_DATE ) ;
585 END IF;
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add( 'JPN: SERVICE PERIOD PASSED IS ' || P_X_LINE_REC.SERVICE_PERIOD ) ;
588 END IF;
589 /* IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
590
591 -- IF Get_Product_Status(515) IN ('I','S') THEN
592
593 -- lkxu, for bug 1701377
594 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
595 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
596 END IF;
597
598 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
599
600 l_sql_stat := '
601 Begin
602 OKS_OMINT_PUB.GET_DURATION(
603 :p_api_version
604 , :p_init_msg_list
605 , :x_msg_count
606 , :x_msg_data
607 , :x_return_status
608 , :p_customer_id
609 , :p_system_id
610 , :p_service_duration
611 , :p_service_period
612 , :p_coterm_checked_yn
613 , :p_start_date
614 , :p_end_date
615 , :x_service_duration
616 , :x_service_period
617 , :x_new_end_date);
618 END;';
619
620 EXECUTE IMMEDIATE l_sql_stat
621 USING IN l_api_version
622 , IN l_init_msg_list
623 , OUT l_msg_count
624
625 , OUT l_msg_data
626
627 , OUT l_return_status
628
629 , IN p_x_line_rec.sold_to_org_id
630 , IN l_system_id
631 , IN p_x_line_rec.service_duration
632 , IN p_x_line_rec.service_period
633 , IN p_x_line_rec.service_coterminate_flag
634 , IN trunc(p_x_line_rec.service_start_date)
635 , IN trunc(p_x_line_rec.service_end_date)
636 , OUT l_service_duration
637
638 , OUT l_service_period
639
640 , OUT l_new_end_date;
641
642
643 IF l_debug_level > 0 THEN
644 oe_debug_pub.add( 'GET_DURATION RETURN WITH FOLLOWING VALUES OF ==>' ) ;
645 END IF;
646 IF l_debug_level > 0 THEN
647 oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
648 END IF;
649 IF l_debug_level > 0 THEN
650 oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
651 END IF;
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
654 END IF;
655 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
656 IF l_debug_level > 0 THEN
657 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION RETURN SUCCESS' ) ;
658 END IF;
659 IF l_debug_level > 0 THEN
660 oe_debug_pub.add( 'WITH VALUE OF ==>' ) ;
661 END IF;
662 IF l_debug_level > 0 THEN
663 oe_debug_pub.add( 'END_DATE ==> ' || TO_CHAR ( L_NEW_END_DATE ) ) ;
664 END IF;
665 IF l_debug_level > 0 THEN
666 oe_debug_pub.add( 'SERVICE_DURATION ==> ' || TO_CHAR ( L_SERVICE_DURATION ) ) ;
667 END IF;
668 IF l_debug_level > 0 THEN
669 oe_debug_pub.add( 'SERVICE_PERIOD ==> ' || L_SERVICE_PERIOD ) ;
670 END IF;
671 p_x_line_rec.service_end_date := l_new_end_date;
672 p_x_line_rec.service_duration := l_service_duration;
673 p_x_line_rec.service_period := l_service_period;
674 END IF;
675 -- x_return_status := l_return_status;
676 IF l_return_status is NULL THEN
677 IF l_debug_level > 0 THEN
678 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT RETURNED VALUE' ) ;
679 END IF;
680 --x_return_status := FND_API.G_RET_STS_ERROR;
681 END IF;
682 IF l_debug_level > 0 THEN
683 oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
684 END IF;
685 ELSE
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
688 END IF;
689 --x_return_status := FND_API.G_RET_STS_ERROR;
690 END IF;
691 END Get_Service_Duration;
692
693
694 -- Procedure : Get_Service_Attribute
695
696 PROCEDURE Get_Service_Attribute
697 ( x_return_status OUT NOCOPY VARCHAR2
698
699 , p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
700 )
701 IS
702 l_header_id NUMBER;
703 l_line_id NUMBER;
704 l_inventory_item_id NUMBER;
705 l_line_number NUMBER;
706 l_shipment_number NUMBER;
707 l_option_number NUMBER;
708 l_component_number NUMBER;
709 l_service_number NUMBER;
710 l_service_qty NUMBER;
711 l_service_uom VARCHAR2(3);
712 l_return_status VARCHAR2(1);
713 l_available_yn VARCHAR2(1);
714 l_init_msg_list VARCHAR2(1);
715 l_api_version NUMBER := 1.0;
716 l_sql_stat VARCHAR2(3000);
717 l_msg_count NUMBER;
718 l_msg_data VARCHAR2(2000);
719 l_top_model_id NUMBER; -- 2331301
720 l_serviceable VARCHAR2(1); -- 2331301
721 l_query_type VARCHAR2(1); --for ER 5926405,6346045
722
723 -- Start of BSA related changes for pack-J by Srini.
724 -- Added two more variable to dervie the BSA number based on Cust PO.
725 l_blanket_number NUMBER;
726 l_blanket_version_number Number;
727 l_blanket_line_number Number;
728 l_cust_po_number varchar2(50);
729 l_request_date date;
730 l_fulfilled_quantity number := null; --5699215
731
732 -- End of the Blanket related changes.
733
734 --
735 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
736 --
737 BEGIN
738
739 -- oe_debug_pub.Debug_On;
740
741 IF l_debug_level > 0 THEN
742 oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
743 oe_debug_pub.add( 'ENTERING For Blanket '||P_X_LINE_REC.BLANKET_NUMBER) ;
744 END IF;
745 IF l_debug_level > 0 THEN
746 oe_debug_pub.add( 'SERVICE REF TYPE CODE IS:' || P_X_LINE_REC.SERVICE_REFERENCE_TYPE_CODE ) ;
747 END IF;
748 IF l_debug_level > 0 THEN
749 oe_debug_pub.add( 'SERVICE REF LINE ID:' || P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ;
750 END IF;
751
752
753 IF l_debug_level > 0 THEN
754 oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE BEFORE IF' ) ;
755 END IF;
756 IF (p_x_line_rec.service_reference_type_code is NULL)
757 AND (p_x_line_rec.service_reference_line_id is NOT NULL) THEN
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE REF TYPE NULL' ) ;
760 END IF;
761 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
762 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SERVICE_REFERENCE_TYPE_CODE');
763 fnd_message.set_name('ONT','OE_INVALID_SERVICE_REFERENCE_TYPE_CODE');
764 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
765 OE_Order_Util.Get_Attribute_Name('service_reference_type_code'));
766 OE_MSG_PUB.Add;
767 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
768 END IF;
769 -- x_return_status := FND_API.G_RET_STS_ERROR;
770 ELSIF (p_x_line_rec.service_reference_type_code = 'ORDER') THEN
771
772 -- put code for the checking of valid service item
773 -- first use IS_SERVICE_AVAILABLE API to confirm that the enter
774 -- service item is valid for the reference item
775
776 IF l_debug_level > 0 THEN
777 oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE WITH REF AS ORDER' ) ;
778 END IF;
779 IF l_debug_level > 0 THEN
780 oe_debug_pub.add( 'REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
781 END IF;
782 IF l_debug_level > 0 THEN
783 oe_debug_pub.add( 'REF LINE# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_LINE_NUMBER ) ) ;
784 END IF;
785 IF l_debug_level > 0 THEN
786 oe_debug_pub.add( 'ORDER# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REF_ORDER_NUMBER ) ) ;
787 END IF;
788 BEGIN
789 IF p_x_line_rec.service_reference_line_id is null then
790
791 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
792
793 SELECT /* MOAC_SQL_CHANGE */ l.line_number
794 , l.shipment_number
795 , l.option_number
796 , l.component_number
797 , l.service_number
798 , l.ordered_quantity
799 , l.order_quantity_uom
800 , l.header_id
801 , l.line_id
802 , l.inventory_item_id
803 , l.blanket_number
804 , l.cust_po_number
805 , l.fulfilled_quantity --5699215
806 INTO l_line_number
807 , l_shipment_number
808 , l_option_number
809 , l_component_number
810 , l_service_number
811 , l_service_qty
812 , l_service_uom
813 , l_header_id
814 , l_line_id
815 , l_inventory_item_id
816 , l_blanket_number
817 , l_cust_po_number
818 , l_fulfilled_quantity --5699215
819 FROM oe_order_lines_all l, oe_order_headers h
820 WHERE h.order_number = p_x_line_rec.service_ref_order_number
821 AND l.line_number = p_x_line_rec.service_ref_line_number
822 AND l.shipment_number = p_x_line_rec.service_ref_shipment_number
823 AND NVL(l.option_number,0) = NVL(p_x_line_rec.service_ref_option_number, 0)
824 AND l.header_id = h.header_id
825 AND rownum < 2;
826 ELSE
827 SELECT /* MOAC_SQL_CHANGE */ l.line_number
828 , l.shipment_number
829 , l.option_number
830 , l.component_number
831 , l.service_number
832 , l.ordered_quantity
833 , l.order_quantity_uom
834 , l.header_id
835 , l.line_id
836 , l.inventory_item_id
837 , l.fulfilled_quantity --5699215
838 INTO l_line_number
839 , l_shipment_number
840 , l_option_number
841 , l_component_number
842 , l_service_number
843 , l_service_qty
844 , l_service_uom
845 , l_header_id
846 , l_line_id
847 , l_inventory_item_id
848 , l_fulfilled_quantity --5699215
849 FROM oe_order_lines_all l, oe_order_headers h
850 WHERE h.order_number = p_x_line_rec.service_ref_order_number
851 AND l.line_number = p_x_line_rec.service_ref_line_number
852 AND l.shipment_number = p_x_line_rec.service_ref_shipment_number
853 AND NVL(l.option_number,0) = NVL(p_x_line_rec.service_ref_option_number, 0)
854 AND l.header_id = h.header_id
855 AND rownum < 2;
856 END IF;
857
858 ELSE
859
860 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
861 IF l_debug_level > 0 THEN
862 oe_debug_pub.add( 'IN SELECT USING REF LINEID# WITH BLANKET'
863 || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
864 END IF;
865 SELECT l.line_number
866 , l.shipment_number
867 , l.option_number
868 , l.component_number
869 , l.service_number
870 , l.ordered_quantity
871 , l.order_quantity_uom
872 , l.header_id
873 , l.line_id
874 , l.inventory_item_id
875 , l.top_model_line_id -- 2331301
876 , NVL(m.serviceable_product_flag, 'N') -- 2331301
877 , l.blanket_number
878 , l.cust_po_number
879 , l.fulfilled_quantity --5699215
880 INTO l_line_number
881 , l_shipment_number
882 , l_option_number
883 , l_component_number
884 , l_service_number
885 , l_service_qty
886 , l_service_uom
887 , l_header_id
888 , l_line_id
889 , l_inventory_item_id
890 , l_top_model_id -- 2331301
891 , l_serviceable -- 2331301
892 , l_blanket_number
893 , l_cust_po_number
894 , l_fulfilled_quantity --5699215
895 FROM oe_order_lines l,
896 mtl_system_items m
897 WHERE l.line_id = p_x_line_rec.service_reference_line_id
898 AND l.inventory_item_id = m.inventory_item_id
899 AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
900
901 --Added for Bug # 4770432 Try to get blanket No from Top Model
902 --if unable to get from currently referenced option item
903 IF l_blanket_number is NULL AND l_top_model_id is NOT NULL THEN
904 BEGIN
905 SELECT blanket_number into l_blanket_number
906 FROM oe_order_lines where line_id = l_top_model_id;
907 EXCEPTION
908 WHEN OTHERS THEN
909 l_blanket_number := null;
910 END;
911 END IF;
912 --End of Changes
913
914 ELSE
915
916
917 IF l_debug_level > 0 THEN
918 oe_debug_pub.add( 'IN SELECT USING REF LINEID# ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) ) ;
919 END IF;
920 SELECT l.line_number
921 , l.shipment_number
922 , l.option_number
923 , l.component_number
924 , l.service_number
925 , l.ordered_quantity
926 , l.order_quantity_uom
927 , l.header_id
928 , l.line_id
929 , l.inventory_item_id
930 , l.top_model_line_id -- 2331301
931 , NVL(m.serviceable_product_flag, 'N') -- 2331301
932 , l.fulfilled_quantity --5699215
933 INTO l_line_number
934 , l_shipment_number
935 , l_option_number
936 , l_component_number
937 , l_service_number
938 , l_service_qty
939 , l_service_uom
940 , l_header_id
941 , l_line_id
942 , l_inventory_item_id
943 , l_top_model_id -- 2331301
944 , l_serviceable -- 2331301
945 , l_fulfilled_quantity --5699215
946 FROM oe_order_lines l,
947 mtl_system_items m
948 WHERE l.line_id = p_x_line_rec.service_reference_line_id
949 AND l.inventory_item_id = m.inventory_item_id
950 AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
951 END IF;
952 END IF;
953
954 IF l_debug_level > 0 THEN
955 oe_debug_pub.add( 'AFTER SELECT OF ORDER REF' ) ;
956 END IF;
957
958 -- IF OKS_OMINT_PUB.IS_SERVICE_AVAILABLE exists in DB
959 -- Then Call it Else Not
960 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.IS_SERVICE_AVAILABLE', l_return_status);
961 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
962
963 -- IF Get_Product_Status(515) IN ('I','S') THEN
964
965 -- lkxu, for bug 1701377
966 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
967 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
968 END IF;
969
970 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' AND -- AND added for 2331301
971 (NOT(l_top_model_id = l_line_id) OR l_serviceable = 'Y') THEN
972
973 IF l_debug_level > 0 THEN
974 oe_debug_pub.add( 'IS SERVICE AVAILABLE IS AVAILABLE - RUN DYNAMIC' ) ;
975 END IF;
976 -- Check
977 -- Sets customer_id and request date too to fix 1720185
978
979 -- for bug 2170348
980 -- have modified the dynamic sql and the EXECUTE IMMEDIATE stmt
981 -- beneath
982
983 l_sql_stat :=
984 'DECLARE l_check_service_rec OKS_OMINT_PUB.Check_service_rec_type;
985 Begin
986 l_check_service_rec.product_item_id := :inventory_item_id;
987 l_check_service_rec.service_item_id := :service_item_id;
988 l_check_service_rec.customer_id := :sold_to_org_id;
989 l_check_service_rec.request_date := nvl(:request_date, sysdate);
990
991 OKS_OMINT_PUB.IS_SERVICE_AVAILABLE(
992 :p_api_version
993 , :p_init_msg_list
994 , :x_msg_count
995 , :x_msg_data
996 , :x_return_status
997 , l_check_service_rec
998 , :x_available_yn);
999 END;';
1000
1001 IF l_debug_level > 0 THEN
1002 oe_debug_pub.add( L_SQL_STAT ) ;
1003 END IF;
1004
1005 EXECUTE IMMEDIATE l_sql_stat -- added request date to fix 1720185
1006 USING IN l_inventory_item_id
1007 , IN p_x_line_rec.inventory_item_id
1008 , IN p_x_line_rec.sold_to_org_id
1009 , IN p_x_line_rec.request_date
1010 , IN l_api_version
1011 , IN l_init_msg_list
1012 , OUT l_msg_count
1013
1014 , OUT l_msg_data
1015
1016 , OUT l_return_status
1017
1018 , OUT l_available_yn;
1019
1020
1021 -- debug messages added as part of 2170348
1022
1023 IF l_debug_level > 0 THEN
1024 oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1025 END IF;
1026 IF l_debug_level > 0 THEN
1027 oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1028 END IF;
1029 IF l_debug_level > 0 THEN
1030 oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1031 END IF;
1032 IF l_debug_level > 0 THEN
1033 oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1034 END IF;
1035 IF l_debug_level > 0 THEN
1036 oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1037 END IF;
1038 IF l_debug_level > 0 THEN
1039 oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1040 END IF;
1041
1042 IF l_debug_level > 0 THEN
1043 oe_debug_pub.add( 'AFTER CALL TO SERVICE AVAILABLE API' ) ;
1044 END IF;
1045
1046 /* OR added for 2282076 */
1047 IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1048 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1049 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
1050 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM');
1051 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1052 OE_Order_Util.Get_Attribute_Name('ordered_item'));
1053 OE_MSG_PUB.Add;
1054 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1055 END IF;
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057 return; /* comment on the return statement removed as part of 2271749, though not required to fix that bug */
1058 END IF; -- Should be a valid service item
1059
1060 END IF;
1061
1062 IF l_debug_level > 0 THEN
1063 oe_debug_pub.add( 'JPN: SERVICE QTY IS' || L_SERVICE_QTY ) ;
1064 END IF;
1065 IF l_debug_level > 0 THEN
1066 oe_debug_pub.add( 'JPN: PRODUCT QTY IS' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1067 END IF;
1068
1069 IF p_x_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty)THEN --5699215
1070 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1071 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM_QTY');
1072 OE_MSG_PUB.Add;
1073 /* Fix for the bug 2431953 / 2749740
1074 ELSIF OE_LINE_UTIL.G_ORDERED_QTY_CHANGE = TRUE THEN
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 Fix ends */
1077 END IF;
1078 --x_return_status := FND_API.G_RET_STS_ERROR;
1079 -- Make the service quantity same as product quantity
1080 p_x_line_rec.ordered_quantity := Nvl (l_fulfilled_quantity,l_service_qty); --5699215
1081 /* for bug 2068001 */
1082 -- return;
1083 /* end 2068001 */
1084 END IF; -- Invalid Service Quantity => Should be equal to Order QTY
1085
1086 IF l_header_id = p_x_line_rec.header_id THEN
1087 p_x_line_rec.service_reference_line_id := l_line_id;
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add( 'AKS: LINE NUMBER IN GET SA ' || TO_CHAR ( L_LINE_NUMBER ) ) ;
1090 END IF;
1091 p_x_line_rec.line_number := l_line_number;
1092 p_x_line_rec.shipment_number := l_shipment_number;
1093 p_x_line_rec.option_number := l_option_number;
1094 p_x_line_rec.component_number := l_component_number;
1095 p_x_line_rec.ordered_quantity := l_service_qty;
1096
1097 /* Added for Blankets for Pack J */
1098 if P_X_LINE_REC.BLANKET_NUMBER is null and
1099 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' then
1100
1101 /* Call Blanket Procedure for the Service Item */
1102 if l_blanket_number is not null and l_cust_po_number is not null
1103 then
1104 oe_default_line.default_blanket_values (
1105 p_blanket_number => l_blanket_number,
1106 p_cust_po_number => l_cust_po_number,
1107 p_ordered_item_id => P_X_LINE_REC.ordered_item_id, --bug6826787
1108 p_ordered_item => P_X_LINE_REC.ordered_item,
1109 p_inventory_item_id => P_X_LINE_REC.INVENTORY_ITEM_ID,
1110 p_item_identifier_type => P_X_LINE_REC.item_identifier_type,
1111 p_request_date => P_X_LINE_REC.request_date,
1112 p_sold_to_org_id => P_X_LINE_REC.sold_to_org_id,
1113 x_blanket_number => l_blanket_number,
1114 x_blanket_line_number => l_blanket_line_number,
1115 x_blanket_version_number => l_blanket_version_number,
1116 x_blanket_request_date => l_request_date);
1117 elsif l_blanket_number is not null then
1118 /*disabled this call, since we want to default the BSA, from BSA num as well from Cust PO
1119 when a valid BSA is not found in first case.
1120 Added by Srini */
1121 OE_Default_Line.get_blanket_number_svc_config(
1122 p_blanket_number => l_blanket_number,
1123 p_inventory_item_id => P_X_LINE_REC.INVENTORY_ITEM_ID,
1124 x_blanket_line_number => l_blanket_line_number,
1125 x_blanket_version_number => l_blanket_version_number);
1126 end if;
1127
1128 P_X_LINE_REC.BLANKET_NUMBER := l_blanket_number;
1129 P_X_LINE_REC.blanket_line_number := l_blanket_line_number;
1130 P_X_LINE_REC.blanket_version_number := l_blanket_version_number;
1131 end if;
1132
1133 select nvl(max(service_number)+1,1)
1134 into l_service_number
1135 from oe_order_lines
1136 where header_id = l_header_id
1137 and line_number = l_line_number
1138 and shipment_number = l_shipment_number
1139 and nvl(option_number,0) = nvl(l_option_number,0)
1140 and nvl(component_number,0) = nvl(l_component_number,0)
1141 and item_type_code = 'SERVICE';
1142
1143 p_x_line_rec.service_number := l_service_number;
1144
1145 ELSE
1146
1147 p_x_line_rec.service_reference_line_id := l_line_id;
1148 p_x_line_rec.service_number := 1;
1149 -- p_x_line_rec.ordered_quantity := l_service_qty;
1150 p_x_line_rec.ordered_quantity := Nvl(l_fulfilled_quantity,l_service_qty); --5699215
1151
1152 /* Added for Blankets for Pack J */
1153 if P_X_LINE_REC.BLANKET_NUMBER is null and
1154 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
1155 then
1156
1157 if l_blanket_number is not null and l_cust_po_number is not null
1158 then
1159 oe_default_line.default_blanket_values (
1160 p_blanket_number => l_blanket_number,
1161 p_cust_po_number => l_cust_po_number,
1162 p_ordered_item_id => P_X_LINE_REC.ordered_item_id, --bug6826787
1163 p_ordered_item => P_X_LINE_REC.ordered_item,
1164 p_inventory_item_id => P_X_LINE_REC.INVENTORY_ITEM_ID,
1165 p_item_identifier_type => P_X_LINE_REC.item_identifier_type,
1166 p_request_date => P_X_LINE_REC.request_date,
1167 p_sold_to_org_id => P_X_LINE_REC.sold_to_org_id,
1168 x_blanket_number => l_blanket_number,
1169 x_blanket_line_number => l_blanket_line_number,
1170 x_blanket_version_number => l_blanket_version_number,
1171 x_blanket_request_date => l_request_date);
1172 elsif l_blanket_number is not null
1173 then
1174 /*disabled this call, since we want to default the BSA, from BSA num as well from Cust PO
1175 when a valid BSA is not found in first case.
1176 Added by Srini*/
1177
1178 OE_Default_Line.get_blanket_number_svc_config(
1179 p_blanket_number => l_blanket_number,
1180 p_inventory_item_id => P_X_LINE_REC.INVENTORY_ITEM_ID,
1181 x_blanket_line_number => l_blanket_line_number,
1182 x_blanket_version_number => l_blanket_version_number);
1183 end if;
1184
1185 P_X_LINE_REC.BLANKET_NUMBER := l_blanket_number;
1186 P_X_LINE_REC.blanket_line_number := l_blanket_line_number;
1187 P_X_LINE_REC.blanket_version_number := l_blanket_version_number;
1188 end if;
1189
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( P_X_LINE_REC.SERVICE_NUMBER ) ) ;
1192 END IF;
1193 IF l_debug_level > 0 THEN
1194 oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( P_X_LINE_REC.LINE_NUMBER ) ) ;
1195 END IF;
1196 IF l_debug_level > 0 THEN
1197 oe_debug_pub.add( 'REF LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1198 END IF;
1199
1200
1201 END IF;
1202 IF l_debug_level > 0 THEN
1203 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1204 END IF;
1205
1206 EXCEPTION
1207
1208 WHEN NO_DATA_FOUND THEN
1209 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1210 OE_MSG_PUB.Add_Exc_Msg
1211 ( 'G_PKG_NAME' ,
1212 'Get_Service_Attribute'
1213 );
1214 END IF;
1215
1216 WHEN OTHERS THEN
1217 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1218 OE_MSG_PUB.Add_Exc_Msg
1219 ( 'G_PKG_NAME' ,
1220 'Get_Service_Attribute'
1221 );
1222 END IF;
1223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224
1225 END;
1226
1227 ELSIF (p_x_line_rec.service_reference_type_code = 'CUSTOMER_PRODUCT') THEN
1228 -- put code for the checking of valid service item
1229
1230 /* Enhancement changes for 1799820. The code references to CS objects */
1231 /* are being replaced by the new CSI product apis. */
1232
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add( 'GET SERVICE ATTRIBUTE - TYPE IS CUSTOMER_PRODUCT' ) ;
1235 END IF;
1236 --for ER 5926405,6346045 changes start here
1237 /* IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
1238 IF NOT (IB_ACTIVE()) THEN
1239 --3549675 Altered the sql to take bind variables instead of literals
1240 l_sql_stat := '
1241 SELECT quantity, unit_of_measure_code
1242 FROM cs_customer_products_rg_v
1243 WHERE customer_product_id = :b1 AND account_id = :b2 AND rownum < 2 ';
1244 ELSE
1245 l_sql_stat := '
1246 SELECT quantity, unit_of_measure_code
1247 FROM csi_instance_accts_rg_v
1248 WHERE customer_product_id = :b1 AND account_id = :b2 AND rownum < 2 ';
1249 */
1250 begin
1251 l_query_type := nvl(OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG_SVC'),'N'); --7120799
1252 exception
1253 when others then
1254 l_query_type := 'N';
1255 end;
1256 l_sql_stat := '
1257 SELECT quantity, unit_of_measure_code
1258 FROM csi_instance_accts_rg_v
1259 WHERE customer_product_id = :b1 ';
1260 if (l_query_type = 'N') THEN
1261 l_sql_stat := l_sql_stat || ' AND account_id = :b2';
1262 end if;
1263 IF (l_query_type = 'Y') THEN
1264 l_sql_stat := l_sql_stat || ' AND ( account_id in (';
1265 l_sql_stat := l_sql_stat ||' SELECT cust_account_id';
1266 l_sql_stat := l_sql_stat ||' FROM hz_cust_acct_relate';
1267 l_sql_stat := l_sql_stat ||' WHERE related_cust_account_id = :b2 ) OR account_id = :b3 )';
1268 end if;
1269 l_sql_stat := l_sql_stat || ' AND rownum < 2 ';
1270
1271 -- END IF;
1272 IF l_debug_level > 0 THEN
1273 oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST ' ) ;
1274 END IF;
1275 IF l_debug_level > 0 THEN
1276 oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1277 END IF;
1278 --3549675
1279 /*
1280 EXECUTE IMMEDIATE l_sql_stat
1281 INTO l_service_qty, l_service_uom
1282 USING to_char(p_x_line_rec.service_reference_line_id),
1283 to_char(p_x_line_rec.sold_to_org_id) ;
1284 */
1285 if (l_query_type = 'N' ) then
1286 EXECUTE IMMEDIATE l_sql_stat
1287 INTO l_service_qty, l_service_uom
1288 USING to_char(p_x_line_rec.service_reference_line_id),
1289 to_char(p_x_line_rec.sold_to_org_id) ;
1290 end if;
1291 if (l_query_type = 'Y' ) then
1292 EXECUTE IMMEDIATE l_sql_stat
1293 INTO l_service_qty, l_service_uom
1294 USING to_char(p_x_line_rec.service_reference_line_id),
1295 to_char(p_x_line_rec.sold_to_org_id),
1296 to_char(p_x_line_rec.sold_to_org_id);
1297 end if;
1298 if (l_query_type = 'A' ) then
1299 EXECUTE IMMEDIATE l_sql_stat
1300 INTO l_service_qty, l_service_uom
1301 USING to_char(p_x_line_rec.service_reference_line_id);
1302 end if;
1303 --for ER 5926405,6346045 changes end here
1304
1305 IF l_debug_level > 0 THEN
1306 oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE FOR CUST ' ) ;
1307 END IF;
1308
1309
1310
1311 -- IF OKS_OMINT_PUB.IS_SERVICE_AVAILABLE exists in DB
1312 -- Then Call it Else Not
1313 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.IS_SERVICE_AVAILABLE', l_return_status);
1314 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
1315
1316 -- IF Get_Product_Status(515) IN ('I','S') THEN
1317
1318 -- lkxu, for bug 1701377
1319 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
1320 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
1321 END IF;
1322
1323 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
1324
1325 -- Check
1326 /* Removed product_item_id and added customer_id, customer_product_id
1327 and request_date to fix 1720185 */
1328
1329 -- for bug 2170348
1330
1331 l_sql_stat :=
1332 'DECLARE l_check_service_rec OKS_OMINT_PUB.Check_service_rec_type;
1333 Begin
1334 l_check_service_rec.service_item_id := :service_item_id;
1335 l_check_service_rec.customer_id := :sold_to_org_id;
1336 l_check_service_rec.customer_product_id := :service_reference_line_id;
1337 l_check_service_rec.request_date := nvl(:request_date, sysdate);
1338
1339 OKS_OMINT_PUB.IS_SERVICE_AVAILABLE(
1340 :p_api_version
1341 , :p_init_msg_list
1342 , :x_msg_count
1343 , :x_msg_data
1344 , :x_return_status
1345 , l_check_service_rec
1346 , :x_available_yn);
1347 END;';
1348
1349 IF l_debug_level > 0 THEN
1350 oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1351 END IF;
1352 IF l_debug_level > 0 THEN
1353 oe_debug_pub.add( 'SQL_STAT ' ||L_SQL_STAT ) ;
1354 END IF;
1355 EXECUTE IMMEDIATE l_sql_stat -- added request_date to fix 1720185
1356 USING IN p_x_line_rec.inventory_item_id
1357 , IN p_x_line_rec.sold_to_org_id
1358 , IN p_x_line_rec.service_reference_line_id
1359 , IN p_x_line_rec.request_date
1360 , IN l_api_version
1361 , IN l_init_msg_list
1362 , OUT l_msg_count
1363
1364 , OUT l_msg_data
1365
1366 , OUT l_return_status
1367
1368 , OUT l_available_yn;
1369
1370
1371 -- debug mesasges added added as part of 2170348
1372 IF l_debug_level > 0 THEN
1373 oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1374 END IF;
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_X_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1377 END IF;
1378 IF l_debug_level > 0 THEN
1379 oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_X_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1380 END IF;
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1383 END IF;
1384 IF l_debug_level > 0 THEN
1385 oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_X_LINE_REC.REQUEST_DATE ) , 5 ) ;
1386 END IF;
1387 IF l_debug_level > 0 THEN
1388 oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1389 END IF;
1390
1391 IF l_debug_level > 0 THEN
1392 oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE FOR CUST-IS_SERVICE_AVAIL' ) ;
1393 END IF;
1394
1395 /* OR added for 2282076 */
1396 IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1397 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1398 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
1399 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM');
1400 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1401 OE_Order_Util.Get_Attribute_Name('ordered_item'));
1402 OE_MSG_PUB.Add;
1403 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1404 END IF;
1405 x_return_status := FND_API.G_RET_STS_ERROR; /* status set to ERROR for 2271749 */
1406 return;
1407 END IF; -- Should be a valid service item
1408
1409 END IF;
1410
1411 IF l_debug_level > 0 THEN
1412 oe_debug_pub.add( 'JPN: CUST PROD SERVICE QTY IS ' || L_SERVICE_QTY ) ;
1413 END IF;
1414 IF l_debug_level > 0 THEN
1415 oe_debug_pub.add( 'JPN: CUST PROD ORDERED QTY IS ' || P_X_LINE_REC.ORDERED_QUANTITY ) ;
1416 END IF;
1417 --IF l_service_qty <> p_x_line_rec.ordered_quantity THEN
1418 IF p_x_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty)THEN --5699215
1419 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1420 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
1421 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM_QTY');
1422 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1423 OE_Order_Util.Get_Attribute_Name('ordered_quantity'));
1424 OE_MSG_PUB.Add;
1425 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1426 END IF;
1427 -- x_return_status := FND_API.G_RET_STS_ERROR;
1428 --p_x_line_rec.ordered_quantity := l_service_qty; -- moved in front of RETURN with, but unrelated to, 1720185
1429 p_x_line_rec.ordered_quantity := Nvl (l_fulfilled_quantity,l_service_qty); --5699215
1430 return;
1431 END IF; -- Invalid Service Quantity => Should be equal to Order QTY
1432
1433 p_x_line_rec.service_number := 1;
1434 p_x_line_rec.ordered_quantity := Nvl (l_fulfilled_quantity,l_service_qty); --5699215
1435 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1438 END IF;
1439 /* ELSIF (p_x_line_rec.service_reference_type_code is NULL) OR
1440 (p_x_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1441 OE_DEBUG_PUB.ADD('IN Get_Service_Attribute - where code is NULL');
1442 NULL; */
1443 ELSE
1444 OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1445 'Get_Service_Attribute - Invalid Context');
1446 -- x_return_status := FND_API.G_RET_STS_ERROR;
1447 IF l_debug_level > 0 THEN
1448 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1449 END IF;
1450 END IF;
1451
1452 END Get_Service_Attribute;
1453
1454
1455 -- Procedure : Get_Service_Duration Overloaded for Form
1456 --
1457
1458 PROCEDURE Get_Service_Duration
1459 ( x_return_status OUT NOCOPY VARCHAR2
1460
1461 , p_line_rec IN OE_OE_FORM_LINE.Line_Rec_Type
1462 , x_line_rec OUT NOCOPY OE_OE_FORM_LINE.Line_Rec_Type
1463
1464 )
1465 IS
1466 l_return_status VARCHAR2(1);
1467 l_init_msg_list VARCHAR2(1);
1468 l_api_version NUMBER := 1.0;
1469 l_sql_stat VARCHAR2(3000);
1470 l_msg_count NUMBER;
1471 l_msg_data VARCHAR2(2000);
1472 l_new_end_date DATE;
1473 l_system_id NUMBER;
1474 l_service_duration NUMBER;
1475 l_service_period VARCHAR2(3);
1476
1477 --
1478 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1479 --
1480 BEGIN
1481 -- oe_debug_pub.Debug_On;
1482
1483 IF l_debug_level > 0 THEN
1484 oe_debug_pub.add( 'ENTERING GET_SERVICE_DURATION' ) ;
1485 END IF;
1486
1487 x_line_rec := p_line_rec;
1488 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.GET_DURATION', l_return_status);
1489 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
1490
1491 -- IF Get_Product_Status(515) IN ('I','S') THEN
1492
1493 -- lkxu, for bug 1701377
1494 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
1495 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
1496 END IF;
1497
1498 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
1499
1500 l_sql_stat := '
1501 Begin
1502 OKS_OMINT_PUB.GET_DURATION(
1503 :p_api_version
1504 , :p_init_msg_list
1505 , :x_msg_count
1506 , :x_msg_data
1507 , :x_return_status
1508 , :p_customer_id
1509 , :p_system_id
1510 , :p_service_duration
1511 , :p_service_period
1512 , :p_coterm_checked_yn
1513 , :p_start_date
1514 , :p_end_date
1515 , :x_service_duration
1516 , :x_service_period
1517 , :x_new_end_date);
1518 END;';
1519
1520 EXECUTE IMMEDIATE l_sql_stat
1521 USING IN l_api_version
1522 , IN l_init_msg_list
1523 , OUT l_msg_count
1524
1525 , OUT l_msg_data
1526
1527 , OUT l_return_status
1528
1529 , IN p_line_rec.sold_to_org_id
1530 , IN l_system_id
1531 , IN p_line_rec.service_duration
1532 , IN p_line_rec.service_period
1533 , IN p_line_rec.service_coterminate_flag
1534 , IN trunc(p_line_rec.service_start_date)
1535 , IN trunc(p_line_rec.service_end_date)
1536 , OUT l_service_duration
1537
1538 , OUT l_service_period
1539
1540 , OUT l_new_end_date;
1541
1542
1543 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1544 x_line_rec.service_end_date := l_new_end_date;
1545 x_line_rec.service_duration := l_service_duration;
1546 x_line_rec.service_period := l_service_period;
1547 END IF;
1548 x_return_status := l_return_status;
1549 IF l_return_status is NULL THEN
1550 IF l_debug_level > 0 THEN
1551 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT RETURNED VALUE' ) ;
1552 END IF;
1553 --x_return_status := FND_API.G_RET_STS_ERROR;
1554 END IF;
1555 IF l_debug_level > 0 THEN
1556 oe_debug_pub.add( 'EXITING GET_SERVICE_DURATION' ) ;
1557 END IF;
1558 ELSE
1559 IF l_debug_level > 0 THEN
1560 oe_debug_pub.add( 'OKS_OMINT_PUB.GET_DURATION NOT EXISTS' ) ;
1561 END IF;
1562 --x_return_status := FND_API.G_RET_STS_ERROR;
1563 END IF;
1564 END Get_Service_Duration;
1565
1566
1567 -- Procedure : Get_Service_Attribute overloaded for Form
1568
1569 --
1570
1571 PROCEDURE Get_Service_Attribute
1572 ( x_return_status OUT NOCOPY VARCHAR2
1573
1574 , p_line_rec IN OE_OE_FORM_LINE.Line_Rec_Type
1575 , x_line_rec OUT NOCOPY OE_OE_FORM_LINE.Line_Rec_Type
1576
1577 )
1578 IS
1579 l_header_id NUMBER;
1580 l_line_id NUMBER;
1581 l_inventory_item_id NUMBER;
1582 l_line_number NUMBER;
1583 l_shipment_number NUMBER;
1584 l_option_number NUMBER;
1585 l_service_number NUMBER;
1586 l_service_qty NUMBER;
1587 l_service_uom VARCHAR2(3);
1588 l_return_status VARCHAR2(1);
1589 l_available_yn VARCHAR2(1);
1590 l_init_msg_list VARCHAR2(1);
1591 l_api_version NUMBER := 1.0;
1592 l_sql_stat VARCHAR2(3000);
1593 l_msg_count NUMBER;
1594 l_msg_data VARCHAR2(2000);
1595 l_top_model_id NUMBER; -- 2331301
1596 l_serviceable VARCHAR2(1); -- 2331301
1597 l_fulfilled_quantity number := null; --5699215
1598 --
1599 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1600 --
1601 BEGIN
1602
1603 -- oe_debug_pub.Debug_On;
1604
1605 IF l_debug_level > 0 THEN
1606 oe_debug_pub.add( 'ENTERING GET_SERVICE_ATTRIBUTE' ) ;
1607 END IF;
1608
1609 x_line_rec := p_line_rec;
1610 IF (p_line_rec.service_reference_type_code is NULL) THEN
1611 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1612 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SERVICE_REFERENCE_TYPE_CODE');
1613 fnd_message.set_name('ONT','OE_INVALID_SERVICE_REFERENCE_TYPE_CODE');
1614 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1615 OE_Order_Util.Get_Attribute_Name('service_reference_type_code'));
1616 OE_MSG_PUB.Add;
1617 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1618 END IF;
1619 x_return_status := FND_API.G_RET_STS_ERROR;
1620 ELSIF (p_line_rec.service_reference_type_code = 'ORDER') THEN
1621
1622 -- put code for the checking of valid service item
1623 -- first use IS_SERVICE_AVAILABLE API to confirm that the enter
1624 -- service item is valid for the reference item
1625
1626 BEGIN
1627
1628 SELECT l.line_number
1629 , l.shipment_number
1630 , l.option_number
1631 , l.service_number
1632 , l.ordered_quantity
1633 , l.order_quantity_uom
1634 , l.header_id
1635 , l.line_id
1636 , l.inventory_item_id
1637 , l.top_model_line_id -- 2331301
1638 , NVL(m.serviceable_product_flag, 'N')
1639 , l.fulfilled_quantity --5699215
1640 INTO l_line_number
1641 , l_shipment_number
1642 , l_option_number
1643 , l_service_number
1644 , l_service_qty
1645 , l_service_uom
1646 , l_header_id
1647 , l_line_id
1648 , l_inventory_item_id
1649 , l_top_model_id -- 2331301
1650 , l_serviceable
1651 , l_fulfilled_quantity --5699215
1652 FROM oe_order_lines l,
1653 mtl_system_items m
1654 WHERE l.line_id = p_line_rec.service_reference_line_id
1655 AND l.inventory_item_id = m.inventory_item_id
1656 AND m.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
1657
1658 -- IF OKS_OMINT_PUB.IS_SERVICE_AVAILABLE exists in DB
1659 -- Then Call it Else Not
1660 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.IS_SERVICE_AVAILABLE', l_return_status);
1661 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
1662
1663 -- IF Get_Product_Status(515) IN ('I','S') THEN
1664
1665 -- lkxu, for bug 1701377
1666 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
1667 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
1668 END IF;
1669
1670 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' AND -- AND added for 2331301
1671 (NOT(l_top_model_id = l_line_id) OR l_serviceable = 'Y') THEN
1672
1673
1674 -- Check
1675 -- Sets customer_id and request date too to fix 1720185
1676
1677 -- for bug 2170348
1678 l_sql_stat :=
1679 'DECLARE l_check_service_rec OKS_OMINT_PUB.Check_service_rec_type;
1680 Begin
1681 l_check_service_rec.product_item_id := :inventory_item_id;
1682 l_check_service_rec.service_item_id := :service_item_id;
1683 l_check_service_rec.customer_id := :sold_to_org_id;
1684 l_check_service_rec.request_date := nvl(:request_date, sysdate);
1685
1686 OKS_OMINT_PUB.IS_SERVICE_AVAILABLE(
1687 :p_api_version
1688 , :p_init_msg_list
1689 , :x_msg_count
1690 , :x_msg_data
1691 , :x_return_status
1692 , l_check_service_rec
1693 , :x_available_yn);
1694 END;';
1695
1696 EXECUTE IMMEDIATE l_sql_stat -- added request date to fix 1720185
1697 USING IN l_inventory_item_id
1698 , IN p_line_rec.inventory_item_id
1699 , IN p_line_rec.sold_to_org_id
1700 , IN p_line_rec.request_date
1701 , IN l_api_version
1702 , IN l_init_msg_list
1703 , OUT l_msg_count
1704
1705 , OUT l_msg_data
1706
1707 , OUT l_return_status
1708
1709 , OUT l_available_yn;
1710
1711
1712 -- debug messages added as part of 2170348
1713
1714 IF l_debug_level > 0 THEN
1715 oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1716 END IF;
1717 IF l_debug_level > 0 THEN
1718 oe_debug_pub.add( ' INVENTORY ITEM ID : ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 5 ) ;
1719 END IF;
1720 IF l_debug_level > 0 THEN
1721 oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1722 END IF;
1723 IF l_debug_level > 0 THEN
1724 oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1725 END IF;
1726 IF l_debug_level > 0 THEN
1727 oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1728 END IF;
1729
1730 IF l_debug_level > 0 THEN
1731 oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1732 END IF;
1733
1734 /* OR added for 2282076 */
1735 IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1736 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1737 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
1738 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM');
1739 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1740 OE_Order_Util.Get_Attribute_Name('ordered_item'));
1741 OE_MSG_PUB.Add;
1742 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1743 END IF;
1744 x_return_status := FND_API.G_RET_STS_ERROR;
1745 return;
1746 END IF; -- Should be a valid service item
1747
1748 END IF;
1749
1750 -- IF l_service_qty <> p_line_rec.ordered_quantity THEN
1751 IF p_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty) THEN --5699215
1752 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1753 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
1754 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM_QTY');
1755 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1756 OE_Order_Util.Get_Attribute_Name('ordered_quantity'));
1757 OE_MSG_PUB.Add;
1758 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1759 END IF;
1760 x_return_status := FND_API.G_RET_STS_ERROR;
1761 return;
1762 END IF; -- Invalid Service Quantity => Should be equal to Order QTY
1763
1764 IF l_header_id = p_line_rec.header_id THEN
1765 x_line_rec.service_reference_line_id := l_line_id;
1766
1767 x_line_rec.line_number := l_line_number;
1768 x_line_rec.shipment_number := l_shipment_number;
1769 x_line_rec.option_number := l_option_number;
1770 x_line_rec.ordered_quantity := l_service_qty;
1771
1772 select nvl(max(service_number)+1,1)
1773 into l_service_number
1774 from oe_order_lines
1775 where header_id = l_header_id
1776 and line_number = l_line_number
1777 and shipment_number = l_shipment_number
1778 and option_number = l_option_number
1779 and item_type_code = 'SERVICE';
1780
1781 x_line_rec.service_number := l_service_number;
1782
1783 ELSE
1784
1785 BEGIN
1786 -- aksingh this has to be changed during testing -- see above comment also
1787 SELECT NVL(MAX(l.line_number)+1,1)
1788 INTO l_line_number
1789 FROM oe_order_lines l
1790 WHERE l.header_id = p_line_rec.header_id;
1791 END;
1792
1793 x_line_rec.service_reference_line_id := l_line_id;
1794 x_line_rec.line_number := l_line_number;
1795 x_line_rec.service_number := 1;
1796 x_line_rec.ordered_quantity := Nvl(l_fulfilled_quantity,l_service_qty); --5699215
1797
1798 IF l_debug_level > 0 THEN
1799 oe_debug_pub.add( 'SERVICE NUMBER IS :' || TO_CHAR ( X_LINE_REC.SERVICE_NUMBER ) ) ;
1800 END IF;
1801 IF l_debug_level > 0 THEN
1802 oe_debug_pub.add( 'LINE NUMBER IS :'|| TO_CHAR ( X_LINE_REC.LINE_NUMBER ) ) ;
1803 END IF;
1804 IF l_debug_level > 0 THEN
1805 oe_debug_pub.add( 'LINE ID IS :'|| TO_CHAR ( L_LINE_ID ) ) ;
1806 END IF;
1807
1808
1809 END IF;
1810 IF l_debug_level > 0 THEN
1811 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1812 END IF;
1813
1814 EXCEPTION
1815
1816 WHEN OTHERS THEN
1817 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1818 OE_MSG_PUB.Add_Exc_Msg
1819 ( 'G_PKG_NAME' ,
1820 'Get_Service_Attribute'
1821 );
1822 END IF;
1823
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825
1826 END;
1827
1828 ELSIF (p_line_rec.service_reference_type_code = 'CUSTOMER_PRODUCT') THEN
1829 -- put code for the checking of valid service item
1830
1831 -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
1832 IF NOT (IB_ACTIVE()) THEN
1833 l_sql_stat := '
1834 SELECT quantity
1835 , unit_of_measure_code
1836 FROM cs_customer_products_rg_v
1837 WHERE customer_product_id = p_line_rec.service_reference_line_id
1838 AND account_id = p_line_rec.sold_to_org_id
1839 AND rownum < 2';
1840 ELSE
1841
1842 l_sql_stat := '
1843 SELECT quantity
1844 , unit_of_measure_code
1845 FROM csi_instance_accts_rg_v
1846 WHERE customer_product_id = p_line_rec.service_reference_line_id
1847 AND account_id = p_line_rec.sold_to_org_id
1848 AND rownum < 2';
1849 END IF;
1850 EXECUTE IMMEDIATE l_sql_stat INTO l_service_qty, l_service_uom;
1851
1852
1853 -- IF OKS_OMINT_PUB.IS_SERVICE_AVAILABLE exists in DB
1854 -- Then Call it Else Not
1855 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.IS_SERVICE_AVAILABLE', l_return_status);
1856 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
1857
1858 -- IF Get_Product_Status(515) IN ('I','S') THEN
1859
1860 -- lkxu, for bug 1701377
1861 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
1862 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
1863 END IF;
1864
1865 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
1866
1867 -- Check
1868 /* Removed product_item_id and added customer_id, customer_product_id
1869 and request_date to fix 1720185 */
1870
1871 -- for bug 2170348
1872
1873 l_sql_stat :=
1874 'DECLARE l_check_service_rec OKS_OMINT_PUB.Check_service_rec_type;
1875 Begin
1876 l_check_service_rec.service_item_id := :service_item_id;
1877 l_check_service_rec.customer_id := :sold_to_org_id;
1878 l_check_service_rec.customer_product_id := :service_reference_line_id;
1879 l_check_service_rec.request_date := nvl(:request_date, sysdate);
1880
1881 OKS_OMINT_PUB.IS_SERVICE_AVAILABLE(
1882 :p_api_version
1883 , :p_init_msg_list
1884 , :x_msg_count
1885 , :x_msg_data
1886 , :x_return_status
1887 , l_check_service_rec
1888 , :x_available_yn);
1889 END;';
1890
1891 EXECUTE IMMEDIATE l_sql_stat -- added request_date to fix 1720185
1892 USING IN p_line_rec.inventory_item_id
1893 , IN p_line_rec.sold_to_org_id
1894 , IN p_line_rec.service_reference_line_id
1895 , IN p_line_rec.request_date
1896 , IN l_api_version
1897 , IN l_init_msg_list
1898 , OUT l_msg_count
1899
1900 , OUT l_msg_data
1901
1902 , OUT l_return_status
1903
1904 , OUT l_available_yn;
1905
1906
1907 -- debug mesasges added added as part of 2170348
1908 IF l_debug_level > 0 THEN
1909 oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.IS_SERVICE_AVAILLABLE :' , 5 ) ;
1910 END IF;
1911 IF l_debug_level > 0 THEN
1912 oe_debug_pub.add( ' SERVICE ITEM ID : ' || TO_CHAR ( P_LINE_REC.INVENTORY_ITEM_ID ) , 5 ) ;
1913 END IF;
1914 IF l_debug_level > 0 THEN
1915 oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_LINE_REC.SOLD_TO_ORG_ID ) , 5 ) ;
1916 END IF;
1917 IF l_debug_level > 0 THEN
1918 oe_debug_pub.add( ' CUST PROD LINE ID : ' || TO_CHAR ( P_LINE_REC.SERVICE_REFERENCE_LINE_ID ) , 5 ) ;
1919 END IF;
1920 IF l_debug_level > 0 THEN
1921 oe_debug_pub.add( ' REQUEST DATE : ' || TO_CHAR ( P_LINE_REC.REQUEST_DATE ) , 5 ) ;
1922 END IF;
1923 IF l_debug_level > 0 THEN
1924 oe_debug_pub.add( 'AVAILABLE/RET.STATUS:' || L_AVAILABLE_YN||'/'||L_RETURN_STATUS , 5 ) ;
1925 END IF;
1926
1927 /* OR added for 2282076 */
1928 IF l_available_yn = 'N' OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1929 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1930 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_ITEM');
1931 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM');
1932 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1933 OE_Order_Util.Get_Attribute_Name('ordered_item'));
1934 OE_MSG_PUB.Add;
1935 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1936 END IF;
1937 x_return_status := FND_API.G_RET_STS_ERROR;
1938 return;
1939 END IF; -- Should be a valid service item
1940
1941 END IF;
1942
1943 -- IF l_service_qty <> p_line_rec.ordered_quantity THEN
1944 IF p_line_rec.ordered_quantity <> Nvl (l_fulfilled_quantity,l_service_qty) THEN--5699215
1945 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
1946 OE_MSG_PUB.Update_Msg_Context(p_attribute_code =>'ORDERED_QUANTITY');
1947 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SERV_ITEM_QTY');
1948 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1949 OE_Order_Util.Get_Attribute_Name('ordered_quantity'));
1950 OE_MSG_PUB.Add;
1951 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1952 END IF;
1953 x_return_status := FND_API.G_RET_STS_ERROR;
1954 return;
1955 END IF; -- Invalid Service Quantity => Should be equal to Order QTY
1956
1957 x_line_rec.service_number := 1;
1958 -- x_line_rec.ordered_quantity := l_service_qty;
1959 x_line_rec.ordered_quantity := Nvl(l_fulfilled_quantity,l_service_qty);--5699215
1960 x_return_status := FND_API.G_RET_STS_SUCCESS;
1961 IF l_debug_level > 0 THEN
1962 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1963 END IF;
1964 ELSIF (p_line_rec.service_reference_type_code is NULL) OR
1965 (p_line_rec.service_reference_type_code = FND_API.G_MISS_CHAR) THEN
1966 IF l_debug_level > 0 THEN
1967 oe_debug_pub.add( 'IN GET_SERVICE_ATTRIBUTE - WHERE CODE IS NULL' ) ;
1968 END IF;
1969 NULL;
1970 ELSE
1971 OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
1972 'Get_Service_Attribute - Invalid Context');
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974 IF l_debug_level > 0 THEN
1975 oe_debug_pub.add( 'EXITING GET_SERVICE_ATTRIBUTE' ) ;
1976 END IF;
1977 END IF;
1978
1979 END Get_Service_Attribute;
1980
1981
1982 PROCEDURE Get_Service_Ref_Line_Id
1983 ( x_return_status OUT NOCOPY VARCHAR2
1984
1985 , p_order_number IN NUMBER
1986 , p_line_number IN NUMBER
1987 , p_shipment_number IN NUMBER
1988 , p_option_number IN NUMBER
1989 , x_reference_line_id OUT NOCOPY NUMBER
1990
1991 )
1992 IS
1993 --
1994 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1995 --
1996 BEGIN
1997 IF l_debug_level > 0 THEN
1998 oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_LINE_ID' ) ;
1999 END IF;
2000
2001 If p_order_number is not null and
2002 p_line_number is not null and
2003 p_shipment_number is not null Then
2004
2005 SELECT /* MOAC_SQL_CHANGE */ l.line_id
2006 INTO x_reference_line_id
2007 FROM oe_order_lines_all l, oe_order_headers h
2008 WHERE h.order_number = p_order_number
2009 AND h.header_id = l.header_id
2010 AND l.line_number = p_line_number
2011 AND l.shipment_number = p_shipment_number
2012 AND nvl(l.option_number, 0) = nvl(p_option_number, 0)
2013 AND l.item_type_code <> 'SERVICE';
2014 End If;
2015 x_return_status := FND_API.G_RET_STS_SUCCESS;
2016 IF l_debug_level > 0 THEN
2017 oe_debug_pub.add( 'EXITING GET_SERVICE_REF_LINE_ID' ) ;
2018 END IF;
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2022 'Get_Service_Ref_Line_Id -Invalid Order/Line No.');
2023 /* uncomment later
2024 x_return_status := FND_API.G_RET_STS_ERROR;
2025 OE_DEBUG_PUB.ADD('Exiting Get_Service_Ref_Line_Id');
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027 */
2028 END Get_Service_Ref_Line_Id;
2029
2030
2031 PROCEDURE Get_Service_Ref_System_Id
2032 ( x_return_status OUT NOCOPY VARCHAR2
2033
2034 , p_system_number IN VARCHAR2
2035 , p_customer_id IN NUMBER
2036 , x_reference_system_id OUT NOCOPY NUMBER
2037
2038 )
2039 IS
2040 l_sql_stat VARCHAR2(250);
2041 l_exists VARCHAR2(1);
2042 l_reference_system_id NUMBER;
2043
2044 --
2045 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2046 --
2047 BEGIN
2048 IF l_debug_level > 0 THEN
2049 oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_ID' ) ;
2050 END IF;
2051
2052 -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2053 IF NOT (IB_ACTIVE()) THEN
2054 BEGIN
2055 SELECT 'Y'
2056 INTO l_exists
2057 FROM user_views
2058 WHERE view_name = 'CS_SYSTEMS_RG_V'
2059 AND ROWNUM < 2;
2060 END;
2061 ELSE
2062 BEGIN
2063 SELECT 'Y'
2064 INTO l_exists
2065 FROM user_views
2066 WHERE view_name = 'CSI_SYSTEMS_RG_V'
2067 AND ROWNUM < 2;
2068 END;
2069 END IF;
2070
2071 IF l_exists = 'Y' THEN
2072 -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2073 IF NOT(IB_ACTIVE()) THEN
2074 l_sql_stat := '
2075 SELECT system_id
2076 FROM cs_systems_rg_v
2077 WHERE customer_id = :l_customer_id
2078 AND system = :l_system_number';
2079 ELSE
2080
2081 l_sql_stat := '
2082 SELECT system_id
2083 FROM csi_systems_rg_v
2084 WHERE customer_id = :l_customer_id
2085 AND system = :l_system_number';
2086 END IF;
2087
2088 EXECUTE IMMEDIATE l_sql_stat INTO l_reference_system_id
2089 USING p_customer_id, p_system_number;
2090
2091 x_reference_system_id := l_reference_system_id;
2092 END IF;
2093
2094 x_return_status := FND_API.G_RET_STS_SUCCESS;
2095 IF l_debug_level > 0 THEN
2096 oe_debug_pub.add( 'EXITING GET_SERVICE_REF_SYSTEM_ID' ) ;
2097 END IF;
2098 EXCEPTION
2099 WHEN OTHERS THEN
2100 OE_MSG_PUB.Add_Exc_Msg( 'G_PKG_NAME',
2101 'Get_Service_Ref_System_Id - Invalid System No.');
2102 x_return_status := FND_API.G_RET_STS_ERROR;
2103 IF l_debug_level > 0 THEN
2104 oe_debug_pub.add( 'EXITING GET_SERVICE_REF_SYSTEM_ID' ) ;
2105 END IF;
2106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2107 END Get_Service_Ref_System_Id;
2108
2109 PROCEDURE Get_Service_Ref_System_Name
2110 ( x_return_status OUT NOCOPY VARCHAR2
2111
2112 , p_reference_system_id IN NUMBER
2113 , p_customer_id IN NUMBER
2114 , x_system_name OUT NOCOPY VARCHAR2
2115
2116 )
2117 IS
2118 l_sql_stat VARCHAR2(250);
2119 l_system_name VARCHAR2(50);
2120 l_exists VARCHAR2(1);
2121 --
2122 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2123 --
2124 BEGIN
2125 IF l_debug_level > 0 THEN
2126 oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_SYSTEM_NAME' ) ;
2127 END IF;
2128
2129 -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2130 IF NOT (IB_ACTIVE()) THEN
2131 BEGIN
2132 SELECT 'Y'
2133 INTO l_exists
2134 FROM user_views
2135 WHERE view_name = 'CS_SYSTEMS_RG_V'
2136 AND ROWNUM < 2;
2137 END;
2138 ELSE
2139 BEGIN
2140 SELECT 'Y'
2141 INTO l_exists
2142 FROM user_views
2143 WHERE view_name = 'CSI_SYSTEMS_RG_V'
2144 AND ROWNUM < 2;
2145 END;
2146 END IF;
2147
2148 IF l_exists = 'Y' THEN
2149 -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2150 IF NOT (IB_ACTIVE()) THEN
2151 l_sql_stat := '
2152 SELECT NAME
2153 FROM cs_systems
2154 WHERE system_id = :l_system_id'; /*commented for 4731582
2155 AND customer_id = :l_customer_id';*/
2156 ELSE
2157 l_sql_stat := '
2158 SELECT NAME
2159 FROM csi_systems_vl
2160 WHERE system_id = :l_system_id';/*commented for 4731582
2161 AND customer_id = :l_customer_id';*/
2162 END IF;
2163 Execute Immediate l_sql_stat Into l_system_name
2164 Using p_reference_system_id; /* commented for 4731582, p_customer_id;*/
2165 x_system_name := l_system_name;
2166 END IF;
2167
2168 x_return_status := FND_API.G_RET_STS_SUCCESS;
2169 IF l_debug_level > 0 THEN
2170 oe_debug_pub.add( 'EXITING SUCCESS GET_SERVICE_REF_SYSTEM_NAME' ) ;
2171 END IF;
2172
2173 Exception
2174 When others then
2175 x_return_status := FND_API.G_RET_STS_SUCCESS;
2176 IF l_debug_level > 0 THEN
2177 oe_debug_pub.add( 'EXITING ERROR GET_SERVICE_REF_SYSTEM_NAME' ) ;
2178 END IF;
2179 END Get_Service_Ref_System_Name;
2180
2181 PROCEDURE Get_Service_Ref_Cust_Product
2182 ( x_return_status OUT NOCOPY VARCHAR2
2183
2184 , p_reference_line_id IN NUMBER
2185 , p_customer_id IN NUMBER
2186 , x_cust_product OUT NOCOPY VARCHAR2
2187
2188 )
2189 IS
2190 l_sql_stat VARCHAR2(250);
2191 l_exists VARCHAR2(1);
2192 l_cust_product VARCHAR2(50);
2193
2194 --
2195 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2196 --
2197 BEGIN
2198 IF l_debug_level > 0 THEN
2199 oe_debug_pub.add( 'ENTERING GET_SERVICE_REF_CUST_PRODUCT' ) ;
2200 END IF;
2201 IF l_debug_level > 0 THEN
2202 oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2203 END IF;
2204 IF l_debug_level > 0 THEN
2205 oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2206 END IF;
2207
2208 -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2209 IF NOT(IB_ACTIVE()) THEN
2210 BEGIN
2211 SELECT 'Y'
2212 INTO l_exists
2213 FROM user_views
2214 WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
2215 AND ROWNUM < 2;
2216 END;
2217 ELSE
2218 BEGIN
2219 SELECT 'Y'
2220 INTO l_exists
2221 FROM user_views
2222 WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
2223 AND ROWNUM < 2;
2224 END;
2225 END IF;
2226
2227 IF l_debug_level > 0 THEN
2228 oe_debug_pub.add( 'AFTER EXISTS' ) ;
2229 END IF;
2230 IF l_exists = 'Y' THEN
2231 -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2232 IF NOT(IB_ACTIVE()) THEN
2233 l_sql_stat := '
2234 SELECT PRODUCT
2235 FROM cs_customer_products_rg_v
2236 WHERE customer_product_id = :l_customer_product_id';
2237 -- AND account_id = :l_customer_id'; --3572516
2238 ELSE
2239 l_sql_stat := '
2240 SELECT PRODUCT
2241 FROM csi_instance_accts_rg_v
2242 WHERE customer_product_id = :l_customer_product_id';
2243 -- AND account_id = :l_customer_id'; --3572516
2244 END IF;
2245 IF l_debug_level > 0 THEN
2246 oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2247 END IF;
2248 IF l_debug_level > 0 THEN
2249 oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2250 END IF;
2251 --3572516 using clause is altered accordingly
2252 Execute Immediate l_sql_stat Into l_cust_product
2253 Using p_reference_line_id;
2254 -- Using p_reference_line_id, p_customer_id;
2255 x_cust_product := l_cust_product;
2256 IF l_debug_level > 0 THEN
2257 oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE' ) ;
2258 END IF;
2259 END IF;
2260
2261 x_return_status := FND_API.G_RET_STS_SUCCESS;
2262 IF l_debug_level > 0 THEN
2263 oe_debug_pub.add( 'EXITING SUCCESS GET_SERVICE_REF_CUST_PRODUCT' ) ;
2264 END IF;
2265
2266 Exception
2267 When others then
2268 x_return_status := FND_API.G_RET_STS_SUCCESS;
2269 IF l_debug_level > 0 THEN
2270 oe_debug_pub.add( 'EXITING ERROR GET_SERVICE_REF_CUST_PRODUCT' ) ;
2271 END IF;
2272 END Get_Service_Ref_Cust_Product;
2273
2274
2275
2276 PROCEDURE Get_Cust_Product_Line_ID
2277 ( x_return_status OUT NOCOPY VARCHAR2
2278
2279 , p_reference_line_id IN NUMBER
2280 , p_customer_id IN NUMBER
2281 , x_cust_product_line_id OUT NOCOPY NUMBER
2282
2283 )
2284 IS
2285 l_sql_stat VARCHAR2(250);
2286 l_exists VARCHAR2(1);
2287 l_cust_product VARCHAR2(50);
2288 l_order_line_id NUMBER;
2289 l_query_type VARCHAR2(1);--for ER 5926405,6346045
2290
2291 --
2292 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2293 --
2294 BEGIN
2295 IF l_debug_level > 0 THEN
2296 oe_debug_pub.add( 'ENTERING GET_CUST_PRODUCT_LINE_ID' ) ;
2297 END IF;
2298 IF l_debug_level > 0 THEN
2299 oe_debug_pub.add( 'CUSTOMER ID IS ' || P_CUSTOMER_ID ) ;
2300 END IF;
2301 IF l_debug_level > 0 THEN
2302 oe_debug_pub.add( 'LINE ID IS ' || P_REFERENCE_LINE_ID ) ;
2303 END IF;
2304
2305 -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2306 --for ER 5926405,6346045
2307 /* IF NOT(IB_ACTIVE()) THEN
2308 BEGIN
2309 SELECT 'Y'
2310 INTO l_exists
2311 FROM user_views
2312 WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
2313 AND ROWNUM < 2;
2314 END;
2315 ELSE
2316 BEGIN
2317 SELECT 'Y'
2318 INTO l_exists
2319 FROM user_views
2320 WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
2321 AND ROWNUM < 2;
2322 END;
2323 END IF;
2324 */
2325
2326 IF l_debug_level > 0 THEN
2327 oe_debug_pub.add( 'AFTER EXISTS' ) ;
2328 END IF;
2329 /* IF l_exists = 'Y' THEN
2330 -- IF NOT(CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2331 IF NOT(IB_ACTIVE()) THEN
2332 l_sql_stat := '
2333 SELECT ORIGINAL_ORDER_LINE_ID
2334 FROM cs_customer_products_rg_v
2335 WHERE customer_product_id = :l_customer_product_id
2336 AND account_id = :l_customer_id';
2337 ELSE
2338 */
2339 l_sql_stat := '
2340 SELECT ORIGINAL_ORDER_LINE_ID
2341 FROM csi_instance_accts_rg_v
2342 WHERE customer_product_id = :l_customer_product_id
2343 AND account_id = :l_customer_id';
2344 -- END IF;
2345
2346 IF l_debug_level > 0 THEN
2347 oe_debug_pub.add( 'BEFORE EXECUTE IMMEDIATE' ) ;
2348 END IF;
2349 IF l_debug_level > 0 THEN
2350 oe_debug_pub.add( 'SQL STATEMENT BEING EXECUTED'|| L_SQL_STAT ) ;
2351 END IF;
2352
2353 /*Execute Immediate l_sql_stat Into l_order_line_id
2354 Using p_reference_line_id, p_customer_id;
2355 x_cust_product_line_id := l_order_line_id;
2356 */
2357 begin
2358 l_query_type := nvl(OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG_SVC'),'N'); --7120799
2359 exception
2360 when others then
2361 l_query_type := 'N';
2362 end;
2363 if (l_query_type = 'N') then
2364 Execute Immediate l_sql_stat Into l_order_line_id
2365 Using p_reference_line_id, p_customer_id;
2366 else
2367 l_order_line_id := NULL;
2368 end if;
2369 --for ER 5926405,6346045 changes end heer
2370
2371 x_cust_product_line_id := l_order_line_id;
2372
2373 IF l_debug_level > 0 THEN
2374 oe_debug_pub.add( 'AFTER EXECUTE IMMEDIATE' ) ;
2375 END IF;
2376 -- END IF;
2377
2378 x_return_status := FND_API.G_RET_STS_SUCCESS;
2379 IF l_debug_level > 0 THEN
2380 oe_debug_pub.add( 'EXITING SUCCESS GET_CUST_PRODUCT_LINE_ID' ) ;
2381 END IF;
2382
2383 Exception
2384 WHEN NO_DATA_FOUND THEN
2385 -- Added for Bug 6889117 Start
2386 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2387 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Customer or Customer Product');
2388 OE_MSG_PUB.Add;
2389 -- Added for Bug 6889117 End
2390
2391 IF l_debug_level > 0 THEN
2392 oe_debug_pub.add( 'GET_CUST_PRODUCT_LINE_ID: NO DATA FOUND' ) ;
2393 END IF;
2394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2395
2396 WHEN FND_API.G_EXC_ERROR THEN
2397 RAISE FND_API.G_EXC_ERROR;
2398
2399 WHEN OTHERS THEN
2400 x_return_status := FND_API.G_RET_STS_ERROR;
2401 IF l_debug_level > 0 THEN
2402 oe_debug_pub.add( 'EXITING ERROR GET_CUST_PRODUCT_LINE_ID' ) ;
2403 END IF;
2404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405 END Get_Cust_Product_Line_Id;
2406
2407
2408 PROCEDURE Get_Cust_Prod_RG
2409 ( x_return_status OUT NOCOPY VARCHAR2
2410
2411 , p_customer_id IN NUMBER
2412 , x_srv_cust_prod_tbl OUT NOCOPY OE_SERVICE_UTIL.SRV_CUST_PROD_TBL
2413
2414 )
2415 IS
2416 Type CustProdCurTyp IS REF CURSOR;
2417 cust_cv CustProdCurTyp;
2418 l_cust_prod_id NUMBER;
2419 l_cust_prod VARCHAR2(40);
2420 l_cust_prod_desc VARCHAR2(240);
2421 l_sql_stat VARCHAR2(250);
2422 l_exists VARCHAR2(1);
2423 I NUMBER;
2424 --
2425 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2426 --
2427 BEGIN
2428 IF l_debug_level > 0 THEN
2429 oe_debug_pub.add( 'ENTERING GET_CUST_PROD_RG' ) ;
2430 END IF;
2431
2432 -- IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2433 IF NOT (IB_ACTIVE()) THEN
2434 BEGIN
2435 SELECT 'Y'
2436 INTO l_exists
2437 FROM user_views
2438 WHERE view_name = 'CS_CUSTOMER_PRODUCTS_RG_V'
2439 AND ROWNUM < 2;
2440 END;
2441 ELSE
2442 BEGIN
2443 SELECT 'Y'
2444 INTO l_exists
2445 FROM user_views
2446 WHERE view_name = 'CSI_INSTANCE_ACCTS_RG_V'
2447 AND ROWNUM < 2;
2448 END;
2449 END IF;
2450
2451 -- lchen add REFERENCE_NUMBER, CURRENT_SERIAL_NUMBER to fix bug 1529961 4/5/01
2452
2453 IF l_exists = 'Y' AND -- other conditions added for 2225343
2454 (p_customer_id <> g_customer_id OR
2455 g_customer_id IS NULL) THEN
2456
2457 OE_SERVICE_UTIL.l_srv_cust_prod_tbl.DELETE; -- 2225343 end
2458
2459 --IF NOT (CSI_UTILITY_GRP.IB_ACTIVE()) THEN
2460 IF NOT (IB_ACTIVE()) THEN
2461 OPEN cust_cv FOR
2462 'SELECT CUSTOMER_PRODUCT_ID, PRODUCT, PRODUCT_DESCRIPTION, REFERENCE_NUMBER, CURRENT_SERIAL_NUMBER
2463 FROM cs_customer_products_rg_v
2464 WHERE account_id = :l_customer_id' USING p_customer_id;
2465 ELSE
2466
2467 OPEN cust_cv FOR
2468 'SELECT CUSTOMER_PRODUCT_ID, PRODUCT, PRODUCT_DESCRIPTION, REFERENCE_NUMBER, CURRENT_SERIAL_NUMBER
2469 FROM csi_instance_accts_rg_v
2470 WHERE account_id = :l_customer_id' USING p_customer_id;
2471 END IF;
2472
2473 I := 1;
2474
2475 LOOP
2476 FETCH cust_cv INTO
2477 OE_SERVICE_UTIL.l_srv_cust_prod_tbl(I).customer_product_id,
2478 OE_SERVICE_UTIL.l_srv_cust_prod_tbl(I).product,
2479 OE_SERVICE_UTIL.l_srv_cust_prod_tbl(I).product_description,
2480 OE_SERVICE_UTIL.l_srv_cust_prod_tbl(I).reference_number,
2481 OE_SERVICE_UTIL.l_srv_cust_prod_tbl(I).current_serial_number;
2482 I := I + 1;
2483 EXIT WHEN cust_cv%NOTFOUND;
2484 END LOOP;
2485 END IF;
2486 x_srv_cust_prod_tbl := OE_SERVICE_UTIL.l_srv_cust_prod_tbl;
2487 x_return_status := FND_API.G_RET_STS_SUCCESS;
2488 IF l_debug_level > 0 THEN
2489 oe_debug_pub.add( 'EXITING SUCCESS GET_CUST_PROD_RG' ) ;
2490 END IF;
2491
2492 Exception
2493 When others then
2494 x_return_status := FND_API.G_RET_STS_SUCCESS;
2495 IF l_debug_level > 0 THEN
2496 oe_debug_pub.add( 'EXITING ERROR GET_CUST_PROD_RG' ) ;
2497 END IF;
2498
2499 END Get_Cust_Prod_RG;
2500
2501 PROCEDURE Get_Avail_Service_RG
2502 ( x_return_status OUT NOCOPY VARCHAR2
2503
2504 , p_service_rec IN OE_SERVICE_UTIL.T_SERVICE_REC
2505 , x_srv_cust_prod_tbl OUT NOCOPY OE_SERVICE_UTIL.SRV_ITEM_ID_TBL
2506
2507 )
2508 IS
2509 l_sql_stat VARCHAR2(500);
2510 l_return_status VARCHAR2(1);
2511 l_msg_count NUMBER;
2512 l_msg_data VARCHAR2(500);
2513 --
2514 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2515 --
2516 BEGIN
2517 IF l_debug_level > 0 THEN
2518 oe_debug_pub.add( 'ENTERING GET_AVAIL_SERVICE_RG' ) ;
2519 END IF;
2520
2521 -- Call Describe_Proc to check for the existance of the CRM's
2522 -- AVAILABLE_SERVICES API. If exists Then Call it else No Problem.
2523
2524 /* OE_SERVICE_UTIL.CHECK_PROC('OKS_OMINT_PUB.OKS_AVAILABLE_SERVICES', l_return_status);
2525 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN */
2526
2527 -- IF Get_Product_Status(515) IN ('I','S') THEN
2528
2529 -- lkxu, for bug 1701377
2530 IF OE_GLOBALS.G_OKS_INSTALLED IS NULL THEN
2531 OE_GLOBALS.G_OKS_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(515);
2532 END IF;
2533
2534 IF OE_GLOBALS.G_OKS_INSTALLED = 'Y' THEN
2535
2536 -- for bug 2170348
2537
2538 l_sql_stat := 'Declare
2539 l_service_rec OKS_OMINT_PUB.AVAIL_SERVICE_REC_TYPE;
2540 l_service_tbl OKS_OMINT_PUB.ORDER_SERVICE_TBL_TYPE;
2541 Begin
2542 l_service_rec.product_item_id := :product_item_id;
2543 l_service_rec.customer_id := :customer_id;
2544
2545 OKS_OMINT_PUB.OKS_AVAILABLE_SERVICES(
2546 1.0
2547 , NULL
2548 , :x_msg_count
2549 , :x_msg_data
2550 , :x_return_status
2551 , l_service_rec
2552 , l_service_tbl);
2553
2554 OE_SERVICE_UTIL.l_srv_tbl := l_service_tbl; ';
2555
2556 IF l_debug_level > 0 THEN
2557 oe_debug_pub.add( L_SQL_STAT ) ;
2558 END IF;
2559
2560 Execute Immediate l_sql_stat
2561 Using p_service_rec.product_item_id, p_service_rec.customer_id,
2562 l_msg_count, l_msg_data, l_return_status;
2563
2564 -- debug messages added for bug 2170348
2565
2566 IF l_debug_level > 0 THEN
2567 oe_debug_pub.add( 'PARAMETERS PASSED TO OKS_OMINT_PUB.OKS_AVAILABLE_SERVICES : ' ) ;
2568 END IF;
2569 IF l_debug_level > 0 THEN
2570 oe_debug_pub.add( ' PRODUCT ITEM ID : ' || TO_CHAR ( P_SERVICE_REC.PRODUCT_ITEM_ID ) ) ;
2571 END IF;
2572 IF l_debug_level > 0 THEN
2573 oe_debug_pub.add( ' CUSTOMER ID : ' || TO_CHAR ( P_SERVICE_REC.CUSTOMER_ID ) ) ;
2574 END IF;
2575
2576 x_return_status := FND_API.G_RET_STS_SUCCESS;
2577 IF l_debug_level > 0 THEN
2578 oe_debug_pub.add( 'EXITING ERROR GET_AVAIL_SERVICE_RG' ) ;
2579 END IF;
2580 END IF;
2581
2582 Exception
2583 When others then
2584 x_return_status := FND_API.G_RET_STS_SUCCESS;
2585 IF l_debug_level > 0 THEN
2586 oe_debug_pub.add( 'EXITING ERROR GET_AVAIL_SERVICE_RG' ) ;
2587 END IF;
2588
2589 END Get_Avail_Service_RG;
2590
2591 PROCEDURE Retrieve_OC_Messages IS
2592 l_msg_count NUMBER;
2593 l_msg_data VARCHAR2(2000);
2594 x_msg_data VARCHAR2(2000);
2595 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2596 BEGIN
2597
2598 IF l_debug_level > 0 THEN
2599 oe_debug_pub.add( 'ENTERING RETRIEVE OC MESSAGES' ) ;
2600 END IF;
2601 fnd_msg_pub.Count_And_Get
2602 (p_count => l_msg_count,
2603 p_data => l_msg_data
2604 );
2605
2606 IF l_debug_level > 0 THEN
2607 oe_debug_pub.add( 'NO. OF OC MESSAGES :' || L_MSG_COUNT ) ;
2608 END IF;
2609 for k in 1 ..l_msg_count loop
2610 x_msg_data := fnd_msg_pub.get( p_msg_index => k,
2611 p_encoded => 'F'
2612 );
2613 -- For bug 3574480. To show the error reason to the user.
2614 oe_msg_pub.add_text(p_message_text =>X_MSG_DATA);
2615 IF l_debug_level > 0 THEN
2616 oe_debug_pub.add( SUBSTR ( X_MSG_DATA , 1 , 255 ) ) ;
2617 END IF;
2618 end loop;
2619 END;
2620
2621 PROCEDURE Val_Item_Change( p_application_id IN NUMBER,
2622 p_entity_short_name in VARCHAR2,
2623 p_validation_entity_short_name in VARCHAR2,
2624 p_validation_tmplt_short_name in VARCHAR2,
2625 p_record_set_tmplt_short_name in VARCHAR2,
2626 p_scope in VARCHAR2,
2627 p_result OUT NOCOPY NUMBER ) is
2628
2629
2630
2631 l_exists VARCHAR2(30);
2632 l_line_id NUMBER := oe_line_security.g_record.line_id;
2633 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2634 BEGIN
2635 Select 'EXISTS' into
2636 l_exists
2637 from oe_order_lines_all
2638 where service_reference_line_id = l_line_id;
2639 p_result := 1;
2640
2641 EXCEPTION
2642
2643 when no_data_found then
2644 p_result := 0;
2645
2646 End Val_Item_Change;
2647
2648
2649 PROCEDURE Update_Service_Lines
2650 (p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
2651 x_return_status OUT NOCOPY VARCHAR2
2652
2653 )
2654 IS
2655
2656 l_line_rec OE_Order_PUB.Line_Rec_Type;
2657 l_referenced_line_rec OE_Order_PUB.Line_Rec_Type;
2658 i pls_integer;
2659 j pls_integer;
2660 k pls_integer;
2661 l_service_number NUMBER;
2662
2663 l_new_line_rec OE_ORDER_PUB.line_rec_type;
2664 l_old_line_rec OE_ORDER_PUB.line_rec_type;
2665 l_line_id NUMBER;
2666 line_not_found BOOLEAN;
2667 l_cascade_request_flag BOOLEAN := FALSE; -- for bug 2366503
2668
2669 CURSOR order_lines (p_service_reference_line_id NUMBER) IS
2670 SELECT line_id
2671 FROM oe_order_lines
2672 WHERE p_service_reference_line_id = service_reference_line_id
2673 ORDER BY line_id;
2674 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2675 BEGIN
2676
2677 IF l_debug_level > 0 THEN
2678 oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2679 END IF;
2680
2681 --lchen fix bug 2027650
2682 I := p_x_line_tbl.FIRST;
2683 While I is not null loop
2684
2685 -- l_line_rec := p_x_line_tbl(i); /*3676393*/
2686
2687 IF l_debug_level > 0 THEN
2688 oe_debug_pub.add( 'I = ' || I , 1 ) ;
2689 END IF;
2690 IF l_debug_level > 0 THEN
2691 oe_debug_pub.add( 'OPERATION:' || p_x_line_tbl(i).OPERATION , 1 ) ;
2692 END IF;
2693 IF l_debug_level > 0 THEN
2694 oe_debug_pub.add( 'SERVICE_REFERENCE_LINE_ID:' || p_x_line_tbl(i).SERVICE_REFERENCE_LINE_ID , 1 ) ;
2695 END IF;
2696 /*3676393-Altered the l_line_rec to p_x_line_tbl in the below conditions*/
2697 IF p_x_line_tbl(i).item_type_code = 'SERVICE' AND
2698 p_x_line_tbl(i).service_reference_type_code = 'ORDER' AND
2699 p_x_line_tbl(i).service_reference_line_id is NOT NULL AND
2700 --lchen fix for bug 2017271
2701 p_x_line_tbl(i).service_reference_line_id <> FND_API.G_MISS_NUM AND
2702 ( p_x_line_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE or
2703 p_x_line_tbl(i).operation= OE_GLOBALS.G_OPR_UPDATE) THEN
2704
2705 IF l_debug_level > 0 THEN
2706 oe_debug_pub.add( 'IN OE_SERVICE_UTIL.UPDATE_SERVICE_LINES.ENTERING OE_SERVICE_UTIL.NEW_SERVICE_LINES' , 1 ) ;
2707 END IF;
2708
2709 -- oe_debug_pub.ADD('headerId:' || l_line_rec.header_id , 1);
2710 -- oe_debug_pub.ADD('line_id:' || l_line_rec.line_id , 1);
2711 -- oe_debug_pub.ADD('service_reference_line_id:' || l_line_rec.service_reference_line_id , 1);
2712
2713 l_line_rec := p_x_line_tbl(i); /*3676393*/
2714
2715 OE_Line_Util.query_row(
2716 p_line_id => l_line_rec.service_reference_line_id,
2717 x_line_rec => l_referenced_line_rec);
2718
2719 IF l_referenced_line_rec.header_id = l_line_rec.header_id THEN
2720
2721 OE_Line_Util.query_row(
2722 p_line_id => l_line_rec.line_id,
2723 x_line_rec => l_old_line_rec);
2724
2725 l_new_line_rec := l_old_line_rec;
2726
2727 -- assigning line, shipment and option number
2728 l_new_line_rec.line_number := l_referenced_line_rec.line_number;
2729 l_new_line_rec.shipment_number := l_referenced_line_rec.shipment_number;
2730 l_new_line_rec.option_number := l_referenced_line_rec.option_number;
2731
2732 -- assigning service number
2733 IF l_line_rec.service_number IS NULL THEN
2734
2735 l_service_number := 1;
2736
2737 OPEN order_lines(l_line_rec.service_reference_line_id);
2738 LOOP
2739 FETCH order_lines INTO l_line_id;
2740 K := p_x_Line_Tbl.First;
2741 line_not_found := TRUE;
2742 While (K is not null) AND (line_not_found) loop
2743 IF (p_x_line_tbl(K).line_id = l_line_id) AND
2744 (p_x_line_tbl(K).service_number is NULL) THEN
2745 line_not_found := FALSE;
2746 p_x_line_tbl(K).service_number := l_service_number;
2747 l_service_number := l_service_number + 1;
2748 END IF;
2749 K := p_x_line_tbl.Next(K);
2750 End Loop;
2751 EXIT WHEN order_lines%NOTFOUND;
2752 END LOOP;
2753 CLOSE order_lines;
2754
2755 END IF;
2756 l_new_line_rec.service_number := p_x_line_tbl(i).service_number;
2757
2758 OE_LINE_UTIL.Update_Row(p_line_rec => l_new_line_rec);
2759 l_cascade_request_flag := TRUE; -- for bug 2366503
2760
2761 END IF; /* if header id */
2762
2763 END IF; /* if service line */
2764 I := p_x_line_tbl.Next(I);
2765 END LOOP;
2766
2767 -- for bug 2366503
2768 IF ( l_cascade_request_flag ) THEN
2769 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
2770 END IF;
2771
2772 IF l_debug_level > 0 THEN
2773 oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.UPDATE_SERVICE_LINES' , 1 ) ;
2774 END IF;
2775
2776
2777 EXCEPTION
2778
2779 WHEN FND_API.G_EXC_ERROR THEN
2780
2781 x_return_status := FND_API.G_RET_STS_ERROR;
2782
2783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2784
2785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2786
2787 WHEN OTHERS THEN
2788
2789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2790 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2791 THEN
2792 OE_MSG_PUB.Add_Exc_Msg
2793 ( G_PKG_NAME
2794 , 'Update_Service_Lines'
2795 );
2796 END IF;
2797
2798 END Update_Service_Lines;
2799
2800 /* csheu -- added for bug #1533658 */
2801
2802 Procedure CASCADE_CHANGES
2803 ( p_parent_line_id IN NUMBER,
2804 p_request_rec IN OE_Order_Pub.Request_Rec_Type,
2805 x_return_status OUT NOCOPY VARCHAR2
2806
2807 )
2808
2809 IS
2810
2811 -- process_order in variables
2812 l_control_rec OE_GLOBALS.Control_Rec_Type;
2813 l_header_rec OE_Order_PUB.Header_Rec_Type;
2814 l_line_rec OE_ORDER_PUB.Line_Rec_Type
2815 := OE_ORDER_PUB.G_MISS_LINE_REC;
2816 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type
2817 := OE_ORDER_PUB.G_MISS_LINE_TBL;
2818 l_line_tbl OE_Order_PUB.Line_Tbl_Type
2819 := OE_ORDER_PUB.G_MISS_LINE_TBL;
2820 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2821 l_line_count NUMBER;
2822
2823
2824 l_inventory_item_id NUMBER;
2825 l_service_start_date DATE ;
2826 l_service_end_date DATE ;
2827 l_service_duration NUMBER;
2828 l_service_period VARCHAR2(10);
2829
2830
2831 l_service_reference_line_id NUMBER;
2832 l_parent_line_rec OE_ORDER_PUB.Line_Rec_Type;
2833 child_line_id NUMBER;
2834 l_line_id NUMBER;
2835
2836 -- to get the line_id for all childern for the model
2837
2838 CURSOR model_children IS
2839 SELECT l.line_id
2840 FROM oe_order_lines l
2841 WHERE l.top_model_line_id = l_service_reference_line_id
2842 AND l.item_type_code in ('INCLUDED','CLASS','OPTION')
2843 AND exists (select null from mtl_system_items mtl where
2844 mtl.inventory_item_id = l.inventory_item_id and
2845 mtl.serviceable_product_flag = 'Y');
2846
2847 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2848 BEGIN
2849
2850 IF l_debug_level > 0 THEN
2851 oe_debug_pub.add( 'ENTER OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2852 END IF;
2853
2854 IF fnd_profile.value('ONT_CASCADE_SERVICE') = 'N' THEN
2855 /* 3128684 */
2856 IF l_debug_level > 0 THEN
2857 oe_debug_pub.add( 'DO NOT CASCADE SERVICES' , 2 ) ;
2858 END IF;
2859 x_return_status := FND_API.G_RET_STS_SUCCESS;
2860 RETURN;
2861 END IF;
2862 IF l_debug_level > 0 THEN
2863 oe_debug_pub.add( 'DO CASCADE SERVICES' , 2 ) ;
2864 END IF;
2865
2866 OE_Line_Util.Lock_Row( p_line_id => p_parent_line_id
2867 ,p_x_line_rec => l_parent_line_rec
2868 ,x_return_status => l_return_status);
2869
2870 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2872 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2873 RAISE FND_API.G_EXC_ERROR;
2874 END IF;
2875
2876 l_service_reference_line_id := l_parent_line_rec.service_reference_line_id;
2877 l_inventory_item_id := l_parent_line_rec.inventory_item_id;
2878 l_service_start_date := l_parent_line_rec.service_start_date;
2879 l_service_end_date := l_parent_line_rec.service_end_date;
2880 l_service_duration := l_parent_line_rec.service_duration;
2881 l_service_period := l_parent_line_rec.service_period;
2882
2883 IF l_debug_level > 0 THEN
2884 oe_debug_pub.add( 'CSH L_INV_ITEM_ID = ' || TO_CHAR ( L_INVENTORY_ITEM_ID ) , 1 ) ;
2885 END IF;
2886 IF l_debug_level > 0 THEN
2887 oe_debug_pub.add( 'CSH L_SERVICE_START_DATE = ' || TO_CHAR ( L_SERVICE_START_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2888 END IF;
2889 IF l_debug_level > 0 THEN
2890 oe_debug_pub.add( 'CSH L_SERVICE_END_DATE = ' || TO_CHAR ( L_SERVICE_END_DATE , 'DD-MON-YYYY HH24:MI:SS' ) , 1 ) ;
2891 END IF;
2892 IF l_debug_level > 0 THEN
2893 oe_debug_pub.add( 'CSH L_SERVICE_DURATION = ' || TO_CHAR ( L_SERVICE_DURATION ) , 1 ) ;
2894 END IF;
2895 IF l_debug_level > 0 THEN
2896 oe_debug_pub.add( 'CSH L_SERVICE_PERIOD = ' || L_SERVICE_PERIOD , 1 ) ;
2897 END IF;
2898
2899
2900 l_line_count := 0;
2901 OPEN model_children ;
2902 LOOP
2903 FETCH model_children into child_line_id;
2904 IF l_debug_level > 0 THEN
2905 oe_debug_pub.add( 'CSH CHILD_LINE_ID = ' || TO_CHAR ( CHILD_LINE_ID ) , 1 ) ;
2906 END IF;
2907 EXIT when model_children%NOTFOUND;
2908 BEGIN
2909 SELECT LINE_ID
2910 INTO l_line_id
2911 FROM OE_ORDER_LINES
2912 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
2913 AND service_reference_line_id = child_line_id
2914 AND item_type_code = 'SERVICE'
2915 -- AND ordered_item IS NULL This AND commented for 2556516
2916 AND service_reference_type_code = 'ORDER'
2917 FOR UPDATE NOWAIT;
2918 /* moved bellow with 3128684
2919 EXCEPTION
2920 WHEN OTHERS THEN
2921 l_line_id := 0;
2922 END;
2923 */
2924
2925 IF l_debug_level > 0 THEN
2926 oe_debug_pub.add( 'CSH L_LINE_ID = ' || TO_CHAR ( L_LINE_ID ) , 1 ) ;
2927 END IF;
2928 l_line_count := l_line_count + 1;
2929 l_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
2930 l_line_rec.line_id := l_line_id;
2931 l_line_tbl(l_line_count) := l_line_rec;
2932
2933 EXCEPTION -- moved from above for 3128684
2934 WHEN OTHERS THEN
2935 NULL; -- replacing l_line_id := 0;
2936 END;
2937
2938 END LOOP;
2939
2940 IF l_line_count = 0 THEN
2941 IF l_debug_level > 0 THEN
2942 oe_debug_pub.add( 'EXITING OE_SERVICE_UTIL.CASCADE_CHANGES' , 1 ) ;
2943 END IF;
2944 IF l_debug_level > 0 THEN
2945 oe_debug_pub.add( 'NO ROWS TO CASCADE' , 2 ) ;
2946 END IF;
2947 RETURN;
2948 END IF;
2949
2950 IF l_debug_level > 0 THEN
2951 oe_debug_pub.add( 'NO OF LINES TO CASCADE: ' || TO_CHAR ( L_LINE_TBL.COUNT ) , 1 ) ;
2952 END IF;
2953
2954 FOR I IN 1..l_line_tbl.count LOOP
2955
2956 IF l_service_duration is not NULL THEN
2957 l_line_tbl(I).service_duration := l_service_duration;
2958 END IF;
2959 IF l_service_period is not NULL THEN
2960 l_line_tbl(I).service_period := l_service_period;
2961 END IF;
2962 IF l_service_start_date is not NULL THEN
2963 l_line_tbl(I).service_start_date := l_service_start_date;
2964 END IF;
2965 IF l_service_end_date is not NULL THEN
2966 l_line_tbl(I).service_end_date := l_service_end_date;
2967 END IF;
2968
2969 l_line_tbl(I).OPERATION := OE_GLOBALS.G_OPR_UPDATE;
2970
2971 END LOOP;
2972
2973 -- Call Process Order to update the record.
2974
2975 -- Set control flags.
2976 l_control_rec.controlled_operation := TRUE;
2977 l_control_rec.change_attributes := TRUE;
2978 l_control_rec.default_attributes := TRUE;
2979 l_control_rec.validate_entity := TRUE;
2980 l_control_rec.write_to_DB := TRUE;
2981 l_control_rec.process := FALSE;
2982 l_control_rec.clear_dependents := TRUE;
2983
2984 -- Instruct API to retain its caches
2985
2986 l_header_rec.operation := OE_GLOBALS.G_OPR_NONE;
2987
2988 l_control_rec.clear_api_cache := FALSE;
2989 l_control_rec.clear_api_requests := FALSE;
2990
2991
2992 IF l_debug_level > 0 THEN
2993 oe_debug_pub.add( 'CALLING PROCESS ORDER' , 1 ) ;
2994 END IF;
2995
2996 -- Call OE_Order_PVT.Process_order
2997
2998 OE_ORDER_PVT.Lines
2999 (P_validation_level => FND_API.G_VALID_LEVEL_NONE
3000 ,p_control_rec => l_control_rec
3001 ,p_x_line_tbl => l_line_tbl
3002 ,p_x_old_line_tbl => l_old_line_tbl
3003 ,x_return_status => l_return_status);
3004
3005 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3006 IF l_debug_level > 0 THEN
3007 oe_debug_pub.add( 'INSIDE UNEXPECTED ERROR ' , 2 ) ;
3008 END IF;
3009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3010 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3011 IF l_debug_level > 0 THEN
3012 oe_debug_pub.add( 'INSIDE ERROR ' , 2 ) ;
3013 END IF;
3014 RAISE FND_API.G_EXC_ERROR;
3015 END IF;
3016
3017 /* jolin start: comment out nocopy for notification project
3018
3019 OE_ORDER_PVT.Process_Requests_And_notify
3020 ( p_process_requests => FALSE
3021 ,p_notify => TRUE
3022 ,x_return_status => l_return_status
3023 ,p_line_tbl => l_line_tbl
3024 ,p_old_line_tbl => l_old_line_tbl);
3025
3026 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3028 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3029 RAISE FND_API.G_EXC_ERROR;
3030 END IF;
3031 jolin end */
3032
3033 -- Clear Table
3034 l_line_tbl.DELETE;
3035
3036 IF l_debug_level > 0 THEN
3037 oe_debug_pub.add( 'EXITING UPDATE_SERVICE_FOR_OPTIONS' , 1 ) ;
3038 END IF;
3039
3040 EXCEPTION
3041
3042 WHEN FND_API.G_EXC_ERROR THEN
3043 IF l_debug_level > 0 THEN
3044 oe_debug_pub.add( 'INSIDE EXCEPTION EXE ERROR ' , 1 ) ;
3045 END IF;
3046 x_return_status := FND_API.G_RET_STS_ERROR;
3047
3048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3049 IF l_debug_level > 0 THEN
3050 oe_debug_pub.add( 'INSIDE EXCEPTION UNEXP ERROR ' , 1 ) ;
3051 END IF;
3052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3053
3054 WHEN OTHERS THEN
3055
3056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3057 IF l_debug_level > 0 THEN
3058 oe_debug_pub.add( 'INSIDE EXCEPTION OTHER ERROR ' , 1 ) ;
3059 END IF;
3060
3061 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3062 THEN
3063 OE_MSG_PUB.Add_Exc_Msg
3064 ( G_PKG_NAME
3065 , 'UPDATE_SERVICE_FOR_OPTIONS'
3066 );
3067 END IF;
3068
3069 END CASCADE_CHANGES;
3070
3071
3072 FUNCTION IB_ACTIVE RETURN BOOLEAN
3073 IS
3074 l_exists VARCHAR2(1);
3075 l_sql_stat VARCHAR2(250);
3076 l_freeze_flag VARCHAR2(1) := 'N';
3077 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3078 BEGIN
3079
3080 -- oe_debug_pub.Debug_On;
3081
3082 IF l_debug_level > 0 THEN
3083 oe_debug_pub.add( 'ENTERING IB_ACTIVE' ) ;
3084 END IF;
3085
3086 /* Following sql is commented for Performance problem
3087 reported in Bug-2159103 */
3088 /*
3089 SELECT 'Y'
3090 INTO l_exists
3091 FROM all_tables
3092 where table_name='CSI_INSTALL_PARAMETERS';
3093
3094 OE_DEBUG_PUB.ADD('l_exists= ' || l_exists ,1);
3095
3096
3097 IF l_exists = 'Y' THEN */
3098
3099 l_sql_stat := '
3100 SELECT freeze_flag
3101 FROM csi_install_parameters
3102 WHERE rownum = 1';
3103
3104 EXECUTE IMMEDIATE l_sql_stat INTO l_freeze_flag;
3105
3106
3107 IF l_debug_level > 0 THEN
3108 oe_debug_pub.add( 'L_FREEZE_FLAG= ' || L_FREEZE_FLAG , 1 ) ;
3109 END IF;
3110
3111 IF l_freeze_flag = 'Y' THEN
3112 return TRUE;
3113 ELSIF l_freeze_flag is NULL or l_freeze_flag = 'N' THEN
3114 return FALSE;
3115 END IF;
3116
3117 /* Commented for Bug-2159103 */
3118 /*
3119 ELSE
3120
3121 OE_DEBUG_PUB.ADD('CSI_UTILITY_GRP.IB_ACTIVE does not exists');
3122
3123 END IF;
3124 */
3125
3126 EXCEPTION
3127 WHEN NO_DATA_FOUND THEN
3128 IF l_debug_level > 0 THEN
3129 oe_debug_pub.add( 'NO DATA FOUND IN CSI_INSTALL_PARAMETERS' ) ;
3130 END IF;
3131 return FALSE;
3132
3133 WHEN OTHERS THEN
3134
3135 /* Added for Bug-2159103 */
3136
3137 if sqlcode = -942 then
3138 IF l_debug_level > 0 THEN
3139 oe_debug_pub.add( SQLERRM ) ;
3140 END IF;
3141 IF l_debug_level > 0 THEN
3142 oe_debug_pub.add( 'CSI_INSTALL_PARAMETERS DOES NOT EXIST' ) ;
3143 END IF;
3144 end if;
3145
3146 return FALSE;
3147
3148 END IB_ACTIVE;
3149
3150 -- Procedure added for bug 2247331 to update the option
3151 -- numbers for service lines. This procedure would be called
3152 -- from OE_CONFIG_PVT package after a call is made to
3153 -- Change_Columns proc while calling the Process Order API.
3154
3155 PROCEDURE Update_Service_Option_Numbers
3156 ( p_top_model_line_id IN NUMBER )
3157 IS
3158 CURSOR option_lines IS
3159 SELECT line_id, option_number
3160 FROM oe_order_lines
3161 WHERE top_model_line_id = p_top_model_line_id;
3162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3163
3164 TYPE num_tbl IS TABLE OF NUMBER;
3165 l_line_ids num_tbl;
3166 l_option_numbers num_tbl;
3167
3168 l_ref_type_code CONSTANT VARCHAR2(5) := 'ORDER';
3169 BEGIN
3170
3171 IF l_debug_level > 0 THEN
3172 oe_debug_pub.add( 'ENTERING OE_SERVICE_UTIL.UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;
3173 END IF;
3174
3175 OPEN option_lines;
3176 FETCH option_lines BULK COLLECT INTO l_line_ids, l_option_numbers;
3177 CLOSE option_lines;
3178
3179 FORALL i IN 1..l_line_ids.count
3180 UPDATE oe_order_lines_all
3181 SET option_number = l_option_numbers(i)
3182 WHERE service_reference_line_id = l_line_ids(i)
3183 AND service_reference_type_code = l_ref_type_code; -- For Bug 3087370
3184
3185 IF l_debug_level > 0 THEN
3186 oe_debug_pub.add( 'LEAVING UPDATE_SERVICE_OPTION_NUMBERS' , 2 ) ;
3187 END IF;
3188
3189 END;
3190
3191 END OE_SERVICE_UTIL;