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