[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_CONFIG_UTIL
Source
1 PACKAGE BODY OE_BULK_CONFIG_UTIL AS
2 /* $Header: OEBUCFGB.pls 120.0.12010000.4 2008/11/26 01:55:09 smusanna noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_CONFIG_UTIL';
5
6
7 /* -----------------------------------------------------------
8 --
9 -- Local procedures
10 --
11 --------------------------------------------------------------*/
12
13
14 PROCEDURE Extend_Line_Rec
15 (p_count IN NUMBER
16 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
17 ,p_config_rec IN OUT NOCOPY OE_BULK_CONFIG_UTIL.CONFIG_REC_TYPE
18 )
19 IS
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 BEGIN
24
25 IF l_debug_level > 0 THEN
26 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Extend_Line_Rec ') ;
27 END IF;
28
29 p_line_rec.ATO_LINE_ID.extend(p_count - p_line_rec.ATO_LINE_ID.count);
30 p_line_rec.BOOKED_FLAG.extend(p_count - p_line_rec.BOOKED_FLAG.count);
31 p_line_rec.COMPONENT_CODE.extend(p_count - p_line_rec.COMPONENT_CODE.count );
32 p_line_rec.COMPONENT_NUMBER.extend(p_count - p_line_rec.COMPONENT_NUMBER.count );
33 p_line_rec.COMPONENT_SEQUENCE_ID.extend(p_count - p_line_rec.COMPONENT_SEQUENCE_ID.count );
34 p_line_rec.CONFIG_HEADER_ID.extend(p_count - p_line_rec.CONFIG_HEADER_ID.count );
35 p_line_rec.CONFIG_REV_NBR.extend(p_count - p_line_rec.CONFIG_REV_NBR.count );
36 p_line_rec.CONFIG_DISPLAY_SEQUENCE.extend(p_count - p_line_rec.CONFIG_DISPLAY_SEQUENCE.count );
37 p_line_rec.CONFIGURATION_ID.extend(p_count - p_line_rec.CONFIGURATION_ID.count );
38 p_line_rec.EXPLOSION_DATE.extend(p_count - p_line_rec.EXPLOSION_DATE.count );
39 p_line_rec.HEADER_ID.extend(p_count - p_line_rec.HEADER_ID.count );
40 p_line_rec.INVENTORY_ITEM_ID.extend(p_count - p_line_rec.INVENTORY_ITEM_ID.count );
41 p_line_rec.ORDERED_ITEM_ID.extend(p_count - p_line_rec.ORDERED_ITEM_ID.count );
42 p_line_rec.ITEM_IDENTIFIER_TYPE.extend(p_count - p_line_rec.ITEM_IDENTIFIER_TYPE.count );
43 p_line_rec.ORDERED_ITEM.extend(p_count - p_line_rec.ORDERED_ITEM.count );
44 p_line_rec.ITEM_REVISION.extend(p_count - p_line_rec.ITEM_REVISION.count );
45 p_line_rec.ITEM_TYPE_CODE.extend(p_count - p_line_rec.ITEM_TYPE_CODE.count);
46 p_line_rec.LINE_CATEGORY_CODE.extend(p_count - p_line_rec.LINE_CATEGORY_CODE.count );
47 p_line_rec.LINE_ID.extend(p_count - p_line_rec.LINE_ID.count );
48 p_line_rec.LINE_NUMBER.extend(p_count - p_line_rec.LINE_NUMBER.count );
49 p_line_rec.LINE_TYPE_ID.extend(p_count - p_line_rec.LINE_TYPE_ID.count );
50 p_line_rec.LINK_TO_LINE_ID.extend(p_count - p_line_rec.LINK_TO_LINE_ID.count );
51 p_line_rec.OPTION_FLAG.extend(p_count - p_line_rec.OPTION_FLAG.count );
52 p_line_rec.OPTION_NUMBER.extend(p_count - p_line_rec.OPTION_NUMBER.count );
53 p_line_rec.ORDERED_QUANTITY.extend(p_count - p_line_rec.ORDERED_QUANTITY.count );
54 p_line_rec.ORDER_QUANTITY_UOM.extend(p_count - p_line_rec.ORDER_QUANTITY_UOM.count );
55 p_line_rec.ORIG_SYS_DOCUMENT_REF.extend(p_count - p_line_rec.ORIG_SYS_DOCUMENT_REF.count );
56 p_line_rec.ORIG_SYS_LINE_REF.extend(p_count - p_line_rec.ORIG_SYS_LINE_REF.count );
57 p_line_rec.ORIG_SYS_SHIPMENT_REF.extend(p_count - p_line_rec.ORIG_SYS_SHIPMENT_REF.count );
58 p_line_rec.SORT_ORDER.extend(p_count -p_line_rec.SORT_ORDER.count );
59 p_line_rec.TOP_MODEL_LINE_ID.extend(p_count - p_line_rec.TOP_MODEL_LINE_ID.count );
60 p_line_rec.TOP_MODEL_LINE_REF.extend(p_count - p_line_rec.TOP_MODEL_LINE_REF.count );
61 p_line_rec.ORDER_SOURCE_ID.extend(p_count - p_line_rec.ORDER_SOURCE_ID.count );
62 p_line_rec.LOCK_CONTROL.extend(p_count - p_line_rec.LOCK_CONTROL.count );
63 p_line_rec.line_index.extend(p_count - p_line_rec.line_index.count );
64 p_line_rec.header_index.extend(p_count - p_line_rec.header_index.count );
65 p_line_rec.Top_Bill_Sequence_Id.extend(p_count - p_line_rec.Top_Bill_Sequence_Id.count );
66 p_line_rec.cz_qty_match_flag.extend(p_count - p_line_rec.cz_qty_match_flag.count );
67
68 p_config_rec.high_quantity.extend(p_count - p_config_rec.high_quantity.count);
69 p_config_rec.low_quantity.extend(p_count - p_config_rec.low_quantity.count );
70 p_config_rec.mutually_exclusive_options.extend(p_count - p_config_rec.mutually_exclusive_options.count );
71 p_config_rec.bom_item_type.extend(p_count - p_config_rec.bom_item_type.count );
72 p_config_rec.replenish_to_order_flag.extend(p_count - p_config_rec.replenish_to_order_flag.count );
73
74 IF l_debug_level > 0 THEN
75 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Extend_Line_Rec ') ;
76 END IF;
77
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 IF l_debug_level > 0 THEN
82 oe_debug_pub.add( 'OTHERS ERROR , EXTEND_LINE_REC' ) ;
83 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
84 END IF;
85 OE_BULK_MSG_PUB.Add_Exc_Msg
86 ( G_PKG_NAME
87 , 'Extend_Line_Rec'
88 );
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END Extend_Line_Rec;
91
92
93 PROCEDURE Print_Line_Rec
94 ( p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
95 ,p_config_rec IN OE_BULK_CONFIG_UTIL.CONFIG_REC_TYPE
96 )
97 IS
98 --
99 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
100 --
101 BEGIN
102
103 IF l_debug_level > 0 THEN
104 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Print_Line_Rec ') ;
105 END IF;
106
107
108 IF l_debug_level > 0 THEN
109 FOR I IN 1..p_line_rec.line_id.count LOOP
110
111 oe_debug_pub.add( ' ===== PRINT LINE_REC LOOP INDEX : I = '|| I ) ;
112 oe_debug_pub.add( ' ATO_LINE_ID = ' || p_line_rec.ATO_LINE_ID(I));
113 oe_debug_pub.add( ' BOOKED_FLAG = ' || p_line_rec.BOOKED_FLAG(I));
114 oe_debug_pub.add( ' COMPONENT_CODE = ' || p_line_rec.COMPONENT_CODE(I) );
115 oe_debug_pub.add( ' COMPONENT_NUMBER = ' || p_line_rec.COMPONENT_NUMBER(I) );
116 oe_debug_pub.add( ' COMPONENT_SEQUENCE_ID = ' || p_line_rec.COMPONENT_SEQUENCE_ID(I) );
117 oe_debug_pub.add( ' CONFIG_HEADER_ID = ' || p_line_rec.CONFIG_HEADER_ID(I) );
118 oe_debug_pub.add( ' CONFIG_REV_NBR = ' || p_line_rec.CONFIG_REV_NBR(I) );
119 oe_debug_pub.add( ' CONFIG_DISPLAY_SEQUENCE = ' || p_line_rec.CONFIG_DISPLAY_SEQUENCE(I) );
120 oe_debug_pub.add( ' CONFIGURATION_ID = ' || p_line_rec.CONFIGURATION_ID(I) );
121 oe_debug_pub.add( ' EXPLOSION_DATE = ' || p_line_rec.EXPLOSION_DATE(I) );
122 oe_debug_pub.add( ' HEADER_ID = ' || p_line_rec.HEADER_ID(I) );
123 oe_debug_pub.add( ' INVENTORY_ITEM_ID = ' || p_line_rec.INVENTORY_ITEM_ID(I) );
124 oe_debug_pub.add( ' ORDERED_ITEM_ID = ' || p_line_rec.ORDERED_ITEM_ID(I) );
125 oe_debug_pub.add( ' ITEM_IDENTIFIER_TYPE = ' || p_line_rec.ITEM_IDENTIFIER_TYPE(I) );
126 oe_debug_pub.add( ' ORDERED_ITEM = ' || p_line_rec.ORDERED_ITEM(I) );
127 oe_debug_pub.add( ' ITEM_REVISION = ' || p_line_rec.ITEM_REVISION(I) );
128 oe_debug_pub.add( ' ITEM_TYPE_CODE = ' || p_line_rec.ITEM_TYPE_CODE(I));
129 oe_debug_pub.add( ' LINE_CATEGORY_CODE = ' || p_line_rec.LINE_CATEGORY_CODE(I) );
130 oe_debug_pub.add( ' LINE_ID = ' || p_line_rec.LINE_ID(I) );
131 oe_debug_pub.add( ' LINE_NUMBER = ' || p_line_rec.LINE_NUMBER(I) );
132 oe_debug_pub.add( ' LINE_TYPE_ID = ' || p_line_rec.LINE_TYPE_ID(I) );
133 oe_debug_pub.add( ' LINK_TO_LINE_ID = ' || p_line_rec.LINK_TO_LINE_ID(I) );
134 oe_debug_pub.add( ' OPTION_FLAG = ' || p_line_rec.OPTION_FLAG(I) );
135 oe_debug_pub.add( ' OPTION_NUMBER = ' || p_line_rec.OPTION_NUMBER(I) );
136 oe_debug_pub.add( ' ORDERED_QUANTITY = ' || p_line_rec.ORDERED_QUANTITY(I) );
137 oe_debug_pub.add( ' ORDER_QUANTITY_UOM = ' || p_line_rec.ORDER_QUANTITY_UOM(I) );
138 oe_debug_pub.add( ' ORIG_SYS_DOCUMENT_REF = ' || p_line_rec.ORIG_SYS_DOCUMENT_REF(I) );
139 oe_debug_pub.add( ' ORIG_SYS_LINE_REF = ' || p_line_rec.ORIG_SYS_LINE_REF(I) );
140 oe_debug_pub.add( ' ORIG_SYS_SHIPMENT_REF = ' || p_line_rec.ORIG_SYS_SHIPMENT_REF(I) );
141 oe_debug_pub.add( ' SORT_ORDER = ' || p_line_rec.SORT_ORDER(I) );
142 oe_debug_pub.add( ' TOP_MODEL_LINE_ID = ' || p_line_rec.TOP_MODEL_LINE_ID(I) );
143 oe_debug_pub.add( ' TOP_MODEL_LINE_REF = ' || p_line_rec.TOP_MODEL_LINE_REF(I) );
144 oe_debug_pub.add( ' ORDER_SOURCE_ID = ' || p_line_rec.ORDER_SOURCE_ID(I) );
145 oe_debug_pub.add( ' LOCK_CONTROL = ' || p_line_rec.LOCK_CONTROL(I) );
146 oe_debug_pub.add( ' line_index = ' || p_line_rec.line_index(I) );
147 oe_debug_pub.add( ' header_index = ' || p_line_rec.header_index(I) );
148 oe_debug_pub.add( ' Top_Bill_Sequence_Id = ' || p_line_rec.Top_Bill_Sequence_Id(I) );
149 oe_debug_pub.add( ' cz_qty_match_flag = ' || p_line_rec.cz_qty_match_flag(I) );
150
151 IF (I <= p_config_rec.high_quantity.count) THEN
152 oe_debug_pub.add( ' high_quantity = ' || p_config_rec.high_quantity(I));
153 oe_debug_pub.add( ' low_quantity = ' || p_config_rec.low_quantity(I) );
154 oe_debug_pub.add( ' mutually_exclusive_options = ' || p_config_rec.mutually_exclusive_options(I) );
155 oe_debug_pub.add( ' bom_item_type = ' || p_config_rec.bom_item_type(I) );
156 oe_debug_pub.add( ' replenish_to_order_flag = ' || p_config_rec.replenish_to_order_flag(I) );
157 END IF;
158
159 END LOOP;
160 END IF;
161
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Print_Line_Rec ') ;
164 END IF;
165
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 IF l_debug_level > 0 THEN
170 oe_debug_pub.add( 'OTHERS ERROR , PRINT_LINE_REC' ) ;
171 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
172 END IF;
173 OE_BULK_MSG_PUB.Add_Exc_Msg
174 ( G_PKG_NAME
175 , 'Print_Line_Rec'
176 );
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END Print_Line_Rec;
179
180 PROCEDURE Print_Line_Rec
181 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE )
182 IS
183
184 l_config_rec OE_BULK_CONFIG_UTIL.CONFIG_REC_TYPE;
185
186 BEGIN
187
188 Extend_Line_Rec( p_count => p_line_rec.line_id.count,
189 p_line_rec => p_line_rec,
190 p_config_rec => l_config_rec);
191
192 Print_Line_Rec( p_line_rec => p_line_rec,
193 p_config_rec => l_config_rec);
194
195 END Print_Line_Rec;
196
197
198
199 PROCEDURE Message_From_Cz
200 (
201 p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
202 p_line_index IN NUMBER,
203 p_valid_config IN VARCHAR2,
204 p_complete_config IN VARCHAR2,
205 p_config_header_id IN NUMBER,
206 p_config_rev_nbr IN NUMBER,
207 x_return_status OUT NOCOPY VARCHAR2 )
208 IS
209
210 l_config_header_id NUMBER := p_config_header_id;
211 l_config_rev_nbr NUMBER := p_config_rev_nbr;
212 l_message_text VARCHAR2(2000);
213 l_msg VARCHAR2(2000);
214 l_constraint VARCHAR2(16);
215
216 CURSOR messages(p_config_hdr_id NUMBER, p_config_rev_nbr NUMBER) is
217 SELECT constraint_type , message
218 FROM cz_config_messages
219 WHERE config_hdr_id = p_config_hdr_id
220 AND config_rev_nbr = p_config_rev_nbr;
221
222 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
223
224 BEGIN
225
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Message_From_Cz ') ;
228 END IF;
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 OPEN messages(l_config_header_id, l_config_rev_nbr);
232
233 LOOP
234 FETCH messages into l_constraint,l_msg;
235 EXIT when messages%notfound;
236
237 OE_BULK_Msg_Pub.Add_Text(l_msg);
238 oe_debug_pub.add('msg from spc: '||messages%rowcount , 2);
239 oe_debug_pub.add('msg from spc: '|| substr(l_msg, 1, 250) , 3);
240
241 END LOOP;
242
243 CLOSE messages;
244
245 IF nvl(p_valid_config, 'FALSE') = 'FALSE' THEN
246
247 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_VALIDATION_FAILURE');
248 OE_BULK_MSG_PUB.Add;
249 END IF;
250
251 IF nvl(p_complete_config, 'FALSE') = 'FALSE' THEN
252
253 l_message_text := nvl(p_line_rec.ordered_item(p_line_index),
254 p_line_rec.inventory_item_id(p_line_index));
255
256 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_INCOMPLETE_MODEL');
257 FND_MESSAGE.SET_TOKEN('MODEL',l_message_text);
258 OE_BULK_MSG_PUB.Add;
259
260 END IF;
261
262 IF l_debug_level > 0 THEN
263 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Message_From_Cz ') ;
264 END IF;
265
266
267 EXCEPTION
268
269 WHEN FND_API.G_EXC_ERROR THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271
272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
273 IF messages%ISOPEN THEN
274 CLOSE messages;
275 END IF;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277
278 IF l_debug_level > 0 THEN
279 OE_Debug_Pub.Add('UNEXPECTED_ERROR IN Message_From_Cz: '|| sqlerrm, 1);
280 END IF;
281
282 WHEN OTHERS THEN
283 IF messages%ISOPEN THEN
284 CLOSE messages;
285 END IF;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287
288 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
289 THEN
290 OE_MSG_PUB.Add_Exc_Msg
291 ( G_PKG_NAME
292 , 'Message_From_Cz'
293 );
294 END IF;
295
296 END Message_From_Cz;
297
298
299 PROCEDURE Get_Config_Effective_Date
300 ( p_book_flag IN VARCHAR2
301 ,x_old_behavior OUT NOCOPY VARCHAR2
302 ,x_config_effective_date OUT NOCOPY DATE
303 ,x_return_status OUT NOCOPY VARCHAR2 )
304 IS
305
306 l_profile VARCHAR2(10);
307 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
308 BEGIN
309
310 IF l_debug_level > 0 THEN
311 OE_Debug_Pub.Add ('entering Get_Config_Effective_Date ', 3);
312 END IF;
313
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 l_profile := OE_BULK_ORDER_PVT.G_CONFIG_EFFECT_DATE;
317
318 IF l_profile = '1' OR l_profile IS NULL THEN
319 x_old_behavior := 'Y';
320 x_config_effective_date := sysdate;
321 ELSIF l_profile = '2' THEN
322 x_old_behavior := 'N';
323 x_config_effective_date := sysdate;
324 ELSIF l_profile = '3' THEN
325 x_old_behavior := 'N';
326 if nvl(p_book_flag, 'N') = 'Y' then
327 x_config_effective_date := sysdate;
328 else
329 x_config_effective_date := null;
330 end if;
331 ELSE
332 IF l_debug_level > 0 THEN
333 OE_Debug_Pub.Add('System parameter ONT_CONFIG_EFFECTIVITY_DATE has wrong value', 3);
334 END IF;
335 END IF;
336
337 IF l_debug_level > 0 THEN
338 OE_Debug_Pub.Add
339 ('exiting Get_Config_Effective_Date '||
340 to_char(x_config_effective_date, 'DD-MON-YY HH24:MI:SS'), 3);
341 OE_Debug_Pub.Add('sysdate '||to_char(sysdate, 'DD-MON-YY HH24:MI:SS'),3);
342 END IF;
343
344
345 EXCEPTION
346
347 WHEN FND_API.G_EXC_ERROR THEN
348 x_return_status := FND_API.G_RET_STS_ERROR;
349
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 IF l_debug_level > 0 THEN
353 OE_Debug_Pub.Add('UNEXPECTED_ERROR IN Get_Config_Effective_Date: '|| sqlerrm, 1);
354 END IF;
355
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358
359 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
360 THEN
361 OE_MSG_PUB.Add_Exc_Msg
362 ( G_PKG_NAME
363 , 'Get_Config_Effective_Date'
364 );
365 END IF;
366
367 END Get_Config_Effective_Date;
368
369
370 PROCEDURE Create_hdr_xml
371 ( p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
372 p_line_index IN NUMBER,
373 x_xml_hdr OUT NOCOPY VARCHAR2,
374 x_return_status OUT NOCOPY VARCHAR2 )
375 IS
376
377 TYPE param_name_type IS TABLE OF VARCHAR2(30)
378 INDEX BY BINARY_INTEGER;
379
380 TYPE param_value_type IS TABLE OF VARCHAR2(200)
381 INDEX BY BINARY_INTEGER;
382
383 param_name param_name_type;
384 param_value param_value_type;
385
386 l_rec_index BINARY_INTEGER;
387
388 -- SPC specific params
389 l_database_id VARCHAR2(100);
390 l_save_config_behavior VARCHAR2(30):= 'new_revision';
391 l_ui_type VARCHAR2(30):= null;
392 l_msg_behavior VARCHAR2(30):= 'brief';
393
394 --ont parameters
395 l_context_org_id VARCHAR2(80);
396 l_inventory_item_id VARCHAR2(80);
397 l_config_header_id VARCHAR2(80);
398 l_config_rev_nbr VARCHAR2(80);
399 l_model_quantity VARCHAR2(80);
400 l_pricing_package_name VARCHAR2(100)
401 := 'OE_Config_Price_Util';
402 l_price_items_proc VARCHAR2(100)
403 := 'OE_Config_Price_Items';
404 l_configurator_session_key VARCHAR2(100):= NULL;
405 l_session_id VARCHAR2(80)
406 := FND_PROFILE.Value('DB_SESSION_ID');
407 l_count NUMBER;
408 -- message related
409 l_xml_hdr VARCHAR2(2000):=
410 '<initialize>';
411 l_dummy VARCHAR2(500) := NULL;
412 l_return_status VARCHAR2(1)
413 := FND_API.G_RET_STS_SUCCESS;
414
415 l_config_effective_date DATE;
416 l_old_behavior VARCHAR2(1);
417 l_frozen_model_bill VARCHAR2(1);
418
419 --
420 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
421 --
422 BEGIN
423
424 IF l_debug_level > 0 THEN
425 oe_debug_pub.add('ENTERING OE_BULK_CONFIG_UTIL.CREATE_HDR_XML' , 1);
426 END IF;
427
428 -- now set the values from model_rec and org_id
429 l_context_org_id := OE_BULK_ORDER_PVT.G_ITEM_ORG;
430 l_inventory_item_id := to_char(p_line_rec.inventory_item_id(p_line_index));
431 l_config_header_id := to_char(p_line_rec.config_header_id(p_line_index));
432 l_config_rev_nbr := to_char(p_line_rec.config_rev_nbr(p_line_index));
433
434
435 l_model_quantity := to_char(p_line_rec.ordered_quantity(p_line_index));
436
437 IF l_debug_level > 0 THEN
438 oe_debug_pub.add( ' QTY: ' || L_MODEL_QUANTITY ||
439 ' CONFIG-HDR: ' || L_CONFIG_HEADER_ID ||
440 ' CONFIG-REV: ' || L_CONFIG_REV_NBR ||
441 ' ORG-ID: ' || L_CONTEXT_ORG_ID ||
442 ' ITEM-ID: ' || L_INVENTORY_ITEM_ID , 2 );
443 END IF;
444
445 -- profiles and env. variables.
446 l_database_id := fnd_web_config.database_id;
447 IF l_debug_level > 0 THEN
448 oe_debug_pub.add('DATABASE_ID: '|| L_DATABASE_ID , 2 );
449 END IF;
450
451 -- set param_names
452
453 param_name(1) := 'database_id';
454 param_name(2) := 'context_org_id';
455 param_name(3) := 'config_creation_date';
456 param_name(4) := 'calling_application_id';
457 param_name(5) := 'responsibility_id';
458 param_name(6) := 'model_id';
459 param_name(7) := 'config_header_id';
460 param_name(8) := 'config_rev_nbr';
461 param_name(9) := 'read_only';
462 param_name(10) := 'save_config_behavior';
463 param_name(11) := 'ui_type';
464 param_name(12) := 'validation_org_id';
465 param_name(13) := 'terminate_msg_behavior';
466 param_name(14) := 'model_quantity';
467 param_name(15) := 'icx_session_ticket';
468 param_name(16) := 'client_header';
469 param_name(17) := 'client_line';
470 param_name(18) := 'sbm_flag';
471 param_name(19) := 'config_effective_date';
472 param_name(20) := 'config_model_lookup_date';
473 l_count := 20;
474
475 -- set param values
476
477 param_value(1) := l_database_id;
478 param_value(2) := l_context_org_id;
479 param_value(3) := to_char(sysdate, 'MM-DD-YYYY-HH24-MI-SS');
480 param_value(4) := OE_BULK_ORDER_PVT.G_RESP_APPL_ID;
481 param_value(5) := OE_BULK_ORDER_PVT.G_RESP_ID;
482 param_value(6) := l_inventory_item_id;
483 param_value(7) := l_config_header_id;
484 param_value(8) := l_config_rev_nbr;
485 param_value(9) := null;
486 param_value(10) := l_save_config_behavior;
487 param_value(11) := l_ui_type;
488 param_value(12) := null;
489 param_value(13) := l_msg_behavior;
490 param_value(14) := l_model_quantity;
491 param_value(15) := cz_cf_api.icx_session_ticket;
492 param_value(16) := to_char(p_line_rec.header_id(p_line_index));
493 param_value(17) := to_char(p_line_rec.line_id(p_line_index));
494
495
496 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' THEN
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add('UCFGB MI , PACK H NEW LOGIC' , 1);
499 END IF;
500 param_value(18) := 'TRUE';
501 ELSE
502 param_value(18) := 'FALSE';
503 END IF;
504
505 OE_Bulk_Config_Util.Get_Config_Effective_Date
506 ( p_book_flag => p_line_rec.booked_flag(p_line_index)
507 ,x_old_behavior => l_old_behavior
508 ,x_config_effective_date => l_config_effective_date
509 ,x_return_status => l_return_status);
510
511 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
512 RAISE FND_API.G_EXC_ERROR;
513 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 IF l_old_behavior = 'N' THEN
518 param_value(19) := to_char(l_config_effective_date,
519 'MM-DD-YYYY-HH24-MI-SS');
520 param_value(20) := param_value(19);
521 ELSE
522 param_value(19) := null;
523 param_value(20) := null;
524
525 IF l_debug_level > 0 THEN
526 oe_debug_pub.add('old behavior no dates', 2 );
527 END IF;
528 END IF;
529
530 IF l_debug_level > 0 THEN
531 oe_debug_pub.add('INSIDE CREATE_HDR_XML , PARAMETERS ARE SET' , 2 );
532 END IF;
533
534
535 l_rec_index := 1;
536
537 LOOP
538 -- ex : <param name="config_header_id">1890</param>
539
540 IF (param_value(l_rec_index) IS NOT NULL) THEN
541
542 l_dummy := '<param name=' ||
543 '"' || param_name(l_rec_index) || '"'
544 ||'>'|| param_value(l_rec_index) ||
545 '</param>';
546
547 l_xml_hdr := l_xml_hdr || l_dummy;
548
549 END IF;
550
551 l_dummy := NULL;
552
553 l_rec_index := l_rec_index + 1;
554 EXIT WHEN l_rec_index > l_count;
555
556 END LOOP;
557
558
559 -- add termination tags
560
561 l_xml_hdr := l_xml_hdr || '</initialize>';
562 l_xml_hdr := REPLACE(l_xml_hdr, ' ' , '+');
563
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add('1ST PART OF CREATE_HDR_XML IS : '
566 ||SUBSTR ( L_XML_HDR , 1 , 200 ) , 3 );
567 oe_debug_pub.add('2ND PART OF CREATE_HDR_XML IS : '
568 ||SUBSTR ( L_XML_HDR , 201 , 200 ) , 3 );
569 oe_debug_pub.add('3RD PART OF CREATE_HDR_XML IS : '
570 ||SUBSTR ( L_XML_HDR , 401 , 200 ) , 3 );
571 oe_debug_pub.add('4TH PART OF CREATE_HDR_XML IS : '
572 ||SUBSTR ( L_XML_HDR , 601 , 200 ) , 3 );
573 END IF;
574
575 x_xml_hdr := l_xml_hdr;
576
577 x_return_status := l_return_status;
578 IF l_debug_level > 0 THEN
579 oe_debug_pub.add('LENGTH OF INI MSG:' || LENGTH ( L_XML_HDR ) , 3 );
580 oe_debug_pub.add('EXITING CREATE_HDR_XML' , 3 );
581 END IF;
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 x_return_status := FND_API.G_RET_STS_ERROR;
585
586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF l_debug_level > 0 THEN
589 oe_debug_pub.add('UNEXPECTED EXCEPTION IN CREATE_HDR_XML '|| SQLERRM , 1 );
590 END IF;
591
592 WHEN OTHERS THEN
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594
595 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
596 THEN
597 OE_MSG_PUB.Add_Exc_Msg
598 ( G_PKG_NAME
599 , 'Create_hdr_xml'
600 );
601 END IF;
602
603 END Create_hdr_xml;
604
605
606
607 PROCEDURE Send_input_xml
608 ( p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
609 p_line_index IN NUMBER,
610 p_new_config IN VARCHAR2,
611 p_xml_hdr IN VARCHAR2,
612 x_out_xml_msg OUT NOCOPY LONG ,
613 x_return_status OUT NOCOPY VARCHAR2,
614 p_batch_validate_time IN OUT NOCOPY NUMBER)
615 IS
616 l_html_pieces CZ_BATCH_VALIDATE.CFG_OUTPUT_PIECES;
617 l_option CZ_BATCH_VALIDATE.INPUT_SELECTION;
618 l_batch_val_tbl CZ_BATCH_VALIDATE.CFG_INPUT_LIST;
619 l_db_options_tbl OE_Process_Options_Pvt.SELECTED_OPTIONS_TBL_TYPE;
620 -- update / delete options
621 l_req_rec OE_Order_Pub.Request_Rec_Type;
622 l_flag VARCHAR2(30) := '0';
623
624 --variable to fetch from cursor Get_Options
625 l_validation_status NUMBER;
626 l_sequence NUMBER := 0;
627 l_url VARCHAR2(500):=
628 FND_PROFILE.Value('CZ_UIMGR_URL');
629 l_rec_index BINARY_INTEGER;
630 l_xml_hdr VARCHAR2(2000);
631 l_long_xml LONG := NULL;
632
633 I NUMBER;
634 l_top_model_line_ref VARCHAR2(50);
635 l_top_model_line_id NUMBER;
636 --
637 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
638
639 l_start_time NUMBER;
640 l_end_time NUMBER;
641
642
643 --
644 BEGIN
645
646 IF l_debug_level > 0 THEN
647 oe_debug_pub.add('ENTERING OE_BULK_CONFIG_UTIL.SEND_INPUT_XML' , 1);
648 oe_debug_pub.add('UIMANAGER URL: ' || L_URL , 2 );
649 END IF;
650 x_return_status := FND_API.G_RET_STS_SUCCESS;
651
652 l_xml_hdr := p_xml_hdr;
653 IF l_debug_level > 0 THEN
654 oe_debug_pub.add('LENGTH OF INI MSG: ' || LENGTH ( L_XML_HDR ) , 2 );
655 END IF;
656
657 -- If Model line cz_qty_match_flag is Y Then No need to send model
658 -- record on l_batch_val_tbl Else Send Model record on l_batch_val_tbl.
659 -- (For new configuration, configuration_id will be NULL)
660
661 IF (nvl(p_line_rec.cz_qty_match_flag(p_line_index), 'N') = 'N') THEN
662
663 l_sequence := l_sequence + 1;
664 l_option.component_code := p_line_rec.component_code(p_line_index);
665 l_option.quantity := p_line_rec.ordered_quantity(p_line_index);
666 l_option.config_item_id := p_line_rec.configuration_id(p_line_index);
667 l_option.input_seq := l_sequence;
668
669 l_batch_val_tbl(l_sequence) := l_option;
670
671 END IF;
672
673 -- If p_new_config IS 'N' Then No need to populate l_batch_val_tbl
674 -- Else Loop over (p_line_index +1) to p_end_index on p_line_rec
675
676 IF p_new_config = 'Y' THEN
677 I := 1;
678 -- l_top_model_line_ref := p_line_rec.top_model_line_ref(p_line_index);
679
680 WHILE p_line_index + I <= p_line_rec.line_id.count AND
681 p_line_rec.top_model_line_ref(p_line_index + I) =
682 p_line_rec.top_model_line_ref(p_line_index) AND
683 p_line_rec.header_id(p_line_index + I) = p_line_rec.header_id(p_line_index) LOOP
684 IF l_debug_level > 0 THEN
685 oe_debug_pub.add('GET_OPTION : '
686 || p_line_rec.component_code(p_line_index + I) , 2 );
687 END IF;
688
689 l_sequence := l_sequence + 1;
690 l_option.component_code := p_line_rec.component_code(p_line_index + I);
691 l_option.quantity := p_line_rec.ordered_quantity(p_line_index + I);
692 l_option.config_item_id := p_line_rec.configuration_id(p_line_index + I);
693 l_option.input_seq := l_sequence;
694 l_batch_val_tbl(l_sequence) := l_option;
695 I := I + 1;
696 END LOOP;
697
698 IF l_debug_level > 0 THEN
699 oe_debug_pub.add('OUT OF NEWLY INSERTED OPTIONS LOOP' , 2 );
700 END IF;
701 END IF;
702
703 -- delete previous data.
704 IF (l_html_pieces.COUNT <> 0) THEN
705 l_html_pieces.DELETE;
706 END IF;
707
708 -- Call CZ batch validate API
709
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add('BEFORE CALL CZ_BATCH_VALIDATE.Validate', 1);
712 END IF;
713
714 SELECT hsecs INTO l_start_time from v$timer;
715
716 CZ_BATCH_VALIDATE.Validate
717 ( config_input_list => l_batch_val_tbl ,
718 init_message => l_xml_hdr ,
719 config_messages => l_html_pieces ,
720 validation_status => l_validation_status ,
721 URL => l_url );
722
723 SELECT hsecs INTO l_end_time from v$timer;
724 p_batch_validate_time := p_batch_validate_time + (l_end_time-l_start_time)/100;
725
726 IF l_debug_level > 0 THEN
727 oe_debug_pub.add('AFTER CALL CZ_BATCH_VALIDATE.Validate , STATUS : '
728 ||L_VALIDATION_STATUS , 1);
729 END IF;
730
731 IF (l_html_pieces.COUNT <= 0) THEN
732 IF l_debug_level > 0 THEN
733 oe_debug_pub.add('HTML_PIECES COUNT IS <= 0' , 2 );
734 END IF;
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738
739 l_rec_index := l_html_pieces.FIRST;
740 LOOP
741 IF l_debug_level > 0 THEN
742 oe_debug_pub.add(L_REC_INDEX ||': PART OF OUTPUT_MESSAGE: '
743 || SUBSTR ( L_HTML_PIECES ( L_REC_INDEX ) , 1 , 100 ) , 2 );
744 END IF;
745
746 l_long_xml := l_long_xml || l_html_pieces(l_rec_index);
747
748 EXIT WHEN l_rec_index = l_html_pieces.LAST;
749 l_rec_index := l_html_pieces.NEXT(l_rec_index);
750
751 END LOOP;
752
753 -- if everything ok, set out values
754 x_out_xml_msg := l_long_xml;
755
756 IF l_debug_level > 0 THEN
757 oe_debug_pub.add('EXITING OE_BULK_CONFIG_UTIL.SEND_INPUT_XML' , 1);
758 END IF;
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762 x_return_status := FND_API.G_RET_STS_ERROR;
763
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766
767 IF l_debug_level > 0 THEN
768 oe_debug_pub.add('AN UNEXP ERROR RAISED IN SEND_INPUT_XML: '
769 || SUBSTR ( SQLERRM , 1 , 100 ) , 1);
770 END IF;
771
772 WHEN OTHERS THEN
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774
775 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
776 THEN
777 OE_MSG_PUB.Add_Exc_Msg
778 ( G_PKG_NAME
779 , 'Send_input_xml'
780 );
781 END IF;
782
783 END Send_input_xml;
784
785
786 PROCEDURE Parse_output_xml
787 ( p_xml IN LONG,
788 p_line_index IN NUMBER,
789 p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
790 x_valid_config OUT NOCOPY VARCHAR2,
791 x_complete_config OUT NOCOPY VARCHAR2,
792 x_config_header_id OUT NOCOPY NUMBER,
793 x_config_rev_nbr OUT NOCOPY NUMBER,
794 x_return_status OUT NOCOPY VARCHAR2 )
795 IS
796
797 l_exit_start_tag VARCHAR2(20) := '<EXIT>';
798 l_exit_end_tag VARCHAR2(20) := '</EXIT>';
799 l_exit_start_pos NUMBER;
800 l_exit_end_pos NUMBER;
801
802 l_valid_config_start_tag VARCHAR2(30) := '<VALID_CONFIGURATION>';
803 l_valid_config_end_tag VARCHAR2(30) := '</VALID_CONFIGURATION>';
804 l_valid_config_start_pos NUMBER;
805 l_valid_config_end_pos NUMBER;
806
807 l_complete_config_start_tag VARCHAR2(30) := '<COMPLETE_CONFIGURATION>';
808 l_complete_config_end_tag VARCHAR2(30) := '</COMPLETE_CONFIGURATION>';
809 l_complete_config_start_pos NUMBER;
810 l_complete_config_end_pos NUMBER;
811
812 l_config_header_id_start_tag VARCHAR2(20) := '<CONFIG_HEADER_ID>';
813 l_config_header_id_end_tag VARCHAR2(20) := '</CONFIG_HEADER_ID>';
814 l_config_header_id_start_pos NUMBER;
815 l_config_header_id_end_pos NUMBER;
816
817 l_config_rev_nbr_start_tag VARCHAR2(20) := '<CONFIG_REV_NBR>';
818 l_config_rev_nbr_end_tag VARCHAR2(20) := '</CONFIG_REV_NBR>';
819 l_config_rev_nbr_start_pos NUMBER;
820 l_config_rev_nbr_end_pos NUMBER;
821
822 l_message_text_start_tag VARCHAR2(20) := '<MESSAGE_TEXT>';
823 l_message_text_end_tag VARCHAR2(20) := '</MESSAGE_TEXT>';
824 l_message_text_start_pos NUMBER;
825 l_message_text_end_pos NUMBER;
826
827 l_message_type_start_tag VARCHAR2(20) := '<MESSAGE_TYPE>';
828 l_message_type_end_tag VARCHAR2(20) := '</MESSAGE_TYPE>';
829 l_message_type_start_pos NUMBER;
830 l_message_type_end_pos NUMBER;
831
832 l_exit VARCHAR(20);
833 l_config_header_id NUMBER;
834 l_config_rev_nbr NUMBER;
835 l_message_text VARCHAR2(2000);
836 l_message_type VARCHAR2(200);
837 l_list_price NUMBER;
838 l_selection_line_id NUMBER;
839 l_valid_config VARCHAR2(10);
840 l_complete_config VARCHAR2(10);
841 l_header_id NUMBER;
842 l_return_status VARCHAR2(1) :=
843 FND_API.G_RET_STS_SUCCESS;
844 l_return_status_del VARCHAR2(1);
845 l_msg VARCHAR2(2000);
846 l_constraint VARCHAR2(16);
847 l_flag VARCHAR2(1) := 'N';
848
849 --
850 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
851 --
852 BEGIN
853
854 IF l_debug_level > 0 THEN
855 oe_debug_pub.add('ENTERING OE_CONGIG_UTIL.PARSE_OUTPUT_XML' , 1);
856 END IF;
857
858
859 l_exit_start_pos := INSTR(p_xml, l_exit_start_tag,1, 1) +
860 length(l_exit_start_tag);
861
862 l_exit_end_pos := INSTR(p_xml, l_exit_end_tag,1, 1) - 1;
863
864 l_exit := SUBSTR (p_xml, l_exit_start_pos,
865 l_exit_end_pos - l_exit_start_pos + 1);
866
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add('L_EXIT: ' || L_EXIT , 3 );
869 END IF;
870
871 -- if error go to msg etc.
872 IF nvl(l_exit,'ERROR') <> 'ERROR' THEN
873
874 l_valid_config_start_pos :=
875 INSTR(p_xml, l_valid_config_start_tag,1, 1) +
876 length(l_valid_config_start_tag);
877
878 l_valid_config_end_pos :=
879 INSTR(p_xml, l_valid_config_end_tag,1, 1) - 1;
880
881 l_valid_config := SUBSTR( p_xml, l_valid_config_start_pos,
882 l_valid_config_end_pos -
883 l_valid_config_start_pos + 1);
884
885 IF l_debug_level > 0 THEN
886 oe_debug_pub.add('GG1: '|| L_VALID_CONFIG , 3 );
887 END IF;
888
889 -- ex :- <VALID_CONFIGURATION>abc</VALID_CONFIGURATION>
890 -- 1st instr : posin of a(22), 2nd instr gives posn of c(24)
891 -- substr gives string starting from
892 -- posn a to posn c - posn a + 1(3)
893
894 l_complete_config_start_pos :=
895 INSTR(p_xml, l_complete_config_start_tag,1, 1) +
896 length(l_complete_config_start_tag);
897 l_complete_config_end_pos :=
898 INSTR(p_xml, l_complete_config_end_tag,1, 1) - 1;
899
900 l_complete_config := SUBSTR( p_xml, l_complete_config_start_pos,
901 l_complete_config_end_pos -
902 l_complete_config_start_pos + 1);
903
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add('GG2: '|| L_COMPLETE_CONFIG , 3 );
906 END IF;
907
908
909 IF (nvl(l_valid_config, 'N') <> 'TRUE') THEN
910 IF l_debug_level > 0 THEN
911 oe_debug_pub.add('SPC RETURNED VALID_FLAG AS NULL/FALSE' , 2 );
912 END IF;
913 l_flag := 'Y';
914 END IF ;
915
916
917 IF (nvl(l_complete_config, 'N') <> 'TRUE' ) THEN
918 IF l_debug_level > 0 THEN
919 oe_debug_pub.add('COMPLETE_FLAG AS NULL/FALSE' , 2 );
920 END IF;
921 l_flag := 'Y';
922 END IF;
923
924 IF l_debug_level > 0 THEN
925 oe_debug_pub.add('SPC VALID_CONFIG FLAG: ' || L_VALID_CONFIG , 2 );
926 oe_debug_pub.add('COMPLETE_CONFIG FLAG: ' || L_COMPLETE_CONFIG , 2 );
927 END IF;
928
929 END IF;
930
931
932 -- parsing message_text and type is not req. I use it for debugging.
933
934 l_message_text_start_pos :=
935 INSTR(p_xml, l_message_text_start_tag,1, 1) +
936 length(l_message_text_start_tag);
937 l_message_text_end_pos :=
938 INSTR(p_xml, l_message_text_end_tag,1, 1) - 1;
939
940 l_message_text := SUBSTR( p_xml, l_message_text_start_pos,
941 l_message_text_end_pos -
942 l_message_text_start_pos + 1);
943
944 IF l_debug_level > 0 THEN
945 oe_debug_pub.add('GG3: '|| L_MESSAGE_TEXT , 3 );
946 END IF;
947
948 l_message_type_start_pos :=
949 INSTR(p_xml, l_message_type_start_tag,1, 1) +
950 length(l_message_type_start_tag);
951 l_message_type_end_pos :=
952 INSTR(p_xml, l_message_type_end_tag,1, 1) - 1;
953
954 l_message_type := SUBSTR( p_xml, l_message_type_start_pos,
955 l_message_type_end_pos -
956 l_message_type_start_pos + 1);
957
958
959 -- get the latest config_header_id, and rev_nbr to get
960 -- messages if any.
961
962 IF l_debug_level > 0 THEN
963 oe_debug_pub.add('SPC RETURNED MESSAGE_TEXT: '|| L_MESSAGE_TEXT , 2 );
964 oe_debug_pub.add('SPC RETURNED MESSAGE_TYPE: '|| L_MESSAGE_TYPE , 2 );
965 END IF;
966
967
968 l_config_header_id_start_pos :=
969 INSTR(p_xml, l_config_header_id_start_tag, 1, 1)+
970 length(l_config_header_id_start_tag);
971
972 l_config_header_id_end_pos :=
973 INSTR(p_xml, l_config_header_id_end_tag, 1, 1) - 1;
974
975 l_config_header_id :=
976 to_number(SUBSTR( p_xml,l_config_header_id_start_pos,
977 l_config_header_id_end_pos -
978 l_config_header_id_start_pos + 1));
979
980
981 l_config_rev_nbr_start_pos :=
982 INSTR(p_xml, l_config_rev_nbr_start_tag, 1, 1)+
983 length(l_config_rev_nbr_start_tag);
984
985 l_config_rev_nbr_end_pos :=
986 INSTR(p_xml, l_config_rev_nbr_end_tag, 1, 1) - 1;
987
988 l_config_rev_nbr :=
989 to_number(SUBSTR( p_xml,l_config_rev_nbr_start_pos,
990 l_config_rev_nbr_end_pos -
991 l_config_rev_nbr_start_pos + 1));
992
993 IF l_debug_level > 0 THEN
994 oe_debug_pub.add('CONFIG_HEADER_ID AS:' || L_CONFIG_HEADER_ID , 2 );
995 oe_debug_pub.add('CONFIG_REV_NBR AS:' || L_CONFIG_REV_NBR , 2 );
996 END IF;
997
998
999 IF (l_flag = 'Y' ) OR
1000 l_exit is NULL OR
1001 l_exit = 'ERROR' THEN
1002
1003 IF l_debug_level > 0 THEN
1004 oe_debug_pub.add('GETTING MESSAGES FROM CZ_CONFIG_MESSAGES' , 2 );
1005 END IF;
1006
1007 Message_From_Cz
1008 ( p_line_index => p_line_index,
1009 p_line_rec => p_line_rec,
1010 p_valid_config => l_valid_config,
1011 p_complete_config => l_complete_config,
1012 p_config_header_id => l_config_header_id,
1013 p_config_rev_nbr => l_config_rev_nbr,
1014 x_return_status => l_return_status);
1015
1016 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1017 RAISE FND_API.G_EXC_ERROR;
1018 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020 END IF;
1021
1022 END IF;
1023
1024 IF l_exit is NULL OR
1025 l_exit = 'ERROR' THEN
1026
1027 IF l_debug_level > 0 THEN
1028 oe_debug_pub.add('SPC RETURNED ERROR , FAIL TRANSACTION' , 2 );
1029 END IF;
1030
1031 -- delete the SPC configuration in error
1032 OE_Config_Pvt.Delete_Config
1033 ( p_config_hdr_id => l_config_header_id
1034 ,p_config_rev_nbr => l_config_rev_nbr
1035 ,x_return_status => l_return_status_del);
1036
1037 IF l_return_status_del = FND_API.G_RET_STS_UNEXP_ERROR THEN
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 ELSE
1040 RAISE FND_API.G_EXC_ERROR;
1041 END IF;
1042
1043 END IF;
1044
1045
1046 -- if everything ok, set return values
1047 x_return_status := l_return_status;
1048 x_config_header_id := l_config_header_id;
1049 x_config_rev_nbr := l_config_rev_nbr;
1050 x_complete_config := nvl(l_complete_config, 'FALSE');
1051 x_valid_config := nvl(l_valid_config, 'FALSE');
1052
1053
1054 IF l_debug_level > 0 THEN
1055 oe_debug_pub.add('EXITING OE_BULK_CONFIG_UTIL.PARSE_OUTPUT_XML' , 1);
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN FND_API.G_EXC_ERROR THEN
1060 x_return_status := FND_API.G_RET_STS_ERROR;
1061 IF l_debug_level > 0 THEN
1062 oe_debug_pub.add('SPC EXIT TAG IS ERROR' , 1);
1063 END IF;
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066
1067 IF l_debug_level > 0 THEN
1068 oe_debug_pub.add('AN UNEXP ERROR RAISED IN Parse_output_xml: '
1069 || SUBSTR ( SQLERRM , 1 , 100 ) , 1);
1070 END IF;
1071
1072 WHEN OTHERS THEN
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074
1075 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1076 THEN
1077 OE_MSG_PUB.Add_Exc_Msg
1078 ( G_PKG_NAME
1079 , 'Parse_output_xml'
1080 );
1081 END IF;
1082
1083 END Parse_output_xml;
1084
1085
1086
1087 ---------------------------------------------------------------------
1088 --
1089 -- PROCEDURE Validate_Config_Attributes
1090 --
1091 ---------------------------------------------------------------------
1092
1093 PROCEDURE Validate_Config_Attributes
1094 ( p_use_Configurator IN VARCHAR2,
1095 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type,
1096 p_line_index IN NUMBER
1097 )
1098 IS
1099 l_msg_text VARCHAR2(2000);
1100 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1101
1102 l_attribute VARCHAR2(30);
1103
1104 BEGIN
1105 IF l_debug_level > 0 THEN
1106 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Validate_Config_Attributes ') ;
1107 END IF;
1108
1109 -- Validate Required Attributes
1110 IF nvl(p_use_configurator, 'N') = 'Y' THEN
1111 IF l_debug_level > 0 THEN
1112 oe_debug_pub.add( 'Using configurator ') ;
1113 END IF;
1114
1115 IF p_line_rec.config_header_id(p_line_index) IS NULL AND
1116 p_line_rec.config_rev_nbr(p_line_index) IS NOT NULL
1117 THEN
1118 l_attribute := 'CONFIG_HEADER_ID';
1119 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1120 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1121 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1122 OE_BULK_MSG_PUB.Add('Y','ERROR');
1123 RAISE FND_API.G_EXC_ERROR;
1124 ELSIF p_line_rec.config_header_id(p_line_index) IS NOT NULL AND
1125 p_line_rec.config_rev_nbr(p_line_index) IS NULL
1126 THEN
1127 l_attribute := 'CONFIG_REV_NBR';
1128 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1129 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1130 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1131 OE_BULK_MSG_PUB.Add('Y','ERROR');
1132 RAISE FND_API.G_EXC_ERROR;
1133
1134 ELSIF p_line_rec.config_header_id(p_line_index) IS NOT NULL AND
1135 p_line_rec.config_rev_nbr(p_line_index) IS NOT NULL AND
1136 p_line_rec.configuration_id(p_line_index) IS NULL AND
1137 p_line_rec.component_code(p_line_index) IS NULL
1138 THEN
1139 l_attribute := 'CONFIGURATION_ID';
1140 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1141 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1142 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1143 OE_BULK_MSG_PUB.Add('Y','ERROR');
1144 RAISE FND_API.G_EXC_ERROR;
1145 END IF;
1146
1147 IF p_line_rec.config_header_id(p_line_index) IS NOT NULL AND
1148 p_line_rec.config_rev_nbr(p_line_index) IS NOT NULL AND
1149 p_line_rec.component_code(p_line_index) IS NULL AND
1150 p_line_rec.configuration_id(p_line_index) IS NULL
1151 THEN
1152 l_attribute := 'COMPONENT_CODE';
1153 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1154 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1155 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1156 OE_BULK_MSG_PUB.Add('Y','ERROR');
1157 RAISE FND_API.G_EXC_ERROR;
1158
1159 END IF;
1160
1161 ELSE -- Configurator is not used
1162
1163 IF l_debug_level > 0 THEN
1164 oe_debug_pub.add( 'Not using configurator ') ;
1165 END IF;
1166
1167 IF p_line_rec.config_header_id(p_line_index) IS NOT NULL
1168 THEN
1169 l_attribute := 'CONFIG_HEADER_ID';
1170 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1171 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1172 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1173 OE_BULK_MSG_PUB.Add('Y','ERROR');
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176
1177 IF p_line_rec.config_rev_nbr(p_line_index) IS NOT NULL
1178 THEN
1179 l_attribute := 'CONFIG_REV_NBR';
1180 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1181 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1182 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1183 OE_BULK_MSG_PUB.Add('Y','ERROR');
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186
1187 IF p_line_rec.configuration_id(p_line_index) IS NOT NULL
1188 THEN
1189 l_attribute := 'CONFIGURATION_ID';
1190 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1191 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1192 OE_Order_UTIL.Get_Attribute_Name(l_attribute));
1193 OE_BULK_MSG_PUB.Add('Y','ERROR');
1194 RAISE FND_API.G_EXC_ERROR;
1195 END IF;
1196
1197 END IF; -- Use configurator
1198
1199 IF l_debug_level > 0 THEN
1200 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Validate_Config_Attributes ') ;
1201 END IF;
1202
1203 EXCEPTION
1204 WHEN FND_API.G_EXC_ERROR THEN
1205 p_line_rec.lock_control(p_line_index) := -99;
1206 IF l_debug_level > 0 THEN
1207 oe_debug_pub.add('AN EXC ERROR RAISED IN OE_BULK_CONFIG_UTIL.Validate_Config_Attributes', 1);
1208 END IF;
1209
1210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1211 IF l_debug_level > 0 THEN
1212 oe_debug_pub.add('AN UNEXP ERROR RAISED IN Validate_Config_Attributes: '
1213 || SUBSTR ( SQLERRM , 1 , 100 ) , 1);
1214 END IF;
1215
1216 WHEN OTHERS THEN
1217 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1218 THEN
1219 OE_MSG_PUB.Add_Exc_Msg
1220 ( G_PKG_NAME
1221 , 'Validate_Config_Attributes'
1222 );
1223 END IF;
1224
1225 END Validate_Config_Attributes;
1226
1227
1228 ---------------------------------------------------------------------
1229 --
1230 -- PROCEDURE Load_Item_Attributes
1231 --
1232 ---------------------------------------------------------------------
1233
1234 PROCEDURE Load_Item_Attributes
1235 (
1236 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type,
1237 p_index IN NUMBER
1238 )
1239 IS
1240
1241 l_item_index NUMBER;
1242 l_bom_item_type NUMBER;
1243 l_replenish_to_order_flag VARCHAR2(1);
1244
1245 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1246
1247 BEGIN
1248 IF l_debug_level > 0 THEN
1249 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Load_Item_Attributes ') ;
1250 END IF;
1251
1252 -- Load item cache and Get the index
1253 -- l_index := OE_Bulk_Cache.Load_Item(l_line_rec.inventory_item_id(p_index),
1254 -- l_line_rec.ship_from_org_id(p_index)); Use l_index to lookup the item attributes
1255 -- as -> OE_Bulk_Cache.G_Line_Tbl(l_index).$attribute
1256
1257 l_item_index := OE_BULK_CACHE.Load_item(
1258 p_key1 => p_line_rec.inventory_item_id(p_index),
1259 p_key2 => p_line_rec.ship_from_org_id(p_index) );
1260
1261 l_bom_item_type := OE_BULK_CACHE.G_Item_Tbl(l_item_index).bom_item_type;
1262 l_replenish_to_order_flag := OE_BULK_CACHE.G_Item_Tbl(l_item_index).replenish_to_order_flag;
1263
1264 -- Check If Top Model Line
1265 IF ( l_bom_item_type = 1 AND
1266 p_line_rec.top_model_line_id(p_index) = p_line_rec.line_id(p_index) ) OR
1267 ( p_line_rec.item_type_code(p_index) = 'MODEL') THEN
1268
1269 -- For ATO Model set ato_line_id
1270 IF nvl(l_replenish_to_order_flag, 'N') = 'Y' THEN
1271 p_line_rec.ato_line_id(p_index) := p_line_rec.line_id(p_index);
1272 END IF;
1273
1274
1275 -- Check if ATO model under PTO model
1276 ELSIF l_bom_item_type = 1 AND
1277 p_line_rec.top_model_line_id(p_index) <> p_line_rec.line_id(p_index) THEN
1278
1279 IF nvl(l_replenish_to_order_flag, 'N') = 'Y' THEN
1280 p_line_rec.item_type_code(p_index) := 'CLASS';
1281 END IF;
1282
1283 -- Check if it is a CLASS
1284 ELSIF l_bom_item_type = 2 THEN
1285 p_line_rec.item_type_code(p_index) := 'CLASS';
1286
1287 -- Check if it is an OPTION
1288 ELSIF l_bom_item_type = 4 THEN
1289 p_line_rec.item_type_code(p_index) := 'OPTION';
1290
1291 END IF;
1292
1293 IF p_line_rec.ordered_item(p_index) is null THEN
1294 -- Set it from the Item Cache.
1295 p_line_rec.ordered_item(p_index)
1296 := OE_BULK_CACHE.G_Item_Tbl(l_item_index).ordered_item;
1297
1298 END IF;
1299
1300
1301 IF l_debug_level > 0 THEN
1302 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Load_Item_Attributes ') ;
1303 END IF;
1304
1305 EXCEPTION
1306
1307 WHEN FND_API.G_EXC_ERROR THEN
1308 p_line_rec.lock_control(p_index) := -99;
1309 IF l_debug_level > 0 THEN
1310 oe_debug_pub.add('AN EXC ERROR RAISED IN Load_Item_Attributes', 1);
1311 END IF;
1312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1313 IF l_debug_level > 0 THEN
1314 oe_debug_pub.add('AN UNEXP ERROR RAISED IN Load_Item_Attributes: '
1315 || SUBSTR ( SQLERRM , 1 , 100 ) , 1);
1316 END IF;
1317
1318 WHEN OTHERS THEN
1319
1320 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1321 THEN
1322 OE_MSG_PUB.Add_Exc_Msg
1323 ( G_PKG_NAME
1324 , 'Load_Item_Attributes'
1325 );
1326 END IF;
1327
1328 END Load_Item_Attributes;
1329
1330
1331
1332 ---------------------------------------------------------------------
1333 --
1334 -- PROCEDURE Validate_Configuration
1335 --
1336 ---------------------------------------------------------------------
1337
1338 PROCEDURE Validate_Configuration(
1339 p_new_config IN VARCHAR2
1340 , p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
1341 , p_line_index IN NUMBER
1342 , p_batch_validate_time IN OUT NOCOPY NUMBER
1343 )
1344 IS
1345
1346 l_book_flag VARCHAR2(1);
1347 l_qty_match_flag VARCHAR2(1);
1348 l_config_header_id NUMBER := p_line_rec.config_header_id(p_line_index) ;
1349 l_config_rev_nbr NUMBER := p_line_rec.config_rev_nbr(p_line_index) ;
1350 l_valid_config VARCHAR2(10):= 'true';
1351 l_complete_config VARCHAR2(10):= 'true';
1352 l_exists_flag VARCHAR2(1) := FND_API.G_TRUE;
1353 l_complete_flag VARCHAR2(1) := FND_API.G_TRUE;
1354 l_valid_flag VARCHAR2(1) := FND_API.G_TRUE;
1355 l_msg_count NUMBER;
1356 l_msg_data VARCHAR2(2000);
1357 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1358
1359 l_xml_message LONG := NULL;
1360 l_xml_hdr VARCHAR2(2000);
1361
1362 I NUMBER;
1363 l_top_model_line_ref VARCHAR2(50);
1364 l_top_model_line_id NUMBER;
1365
1366 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1367
1368
1369 BEGIN
1370
1371 IF l_debug_level > 0 THEN
1372 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Validate_Configuration ') ;
1373 END IF;
1374
1375 l_book_flag := p_line_rec.booked_flag(p_line_index);
1376 l_qty_match_flag := p_line_rec.cz_qty_match_flag(p_line_index);
1377
1378 IF l_debug_level > 0 THEN
1379 oe_debug_pub.add( 'p_new_config = ' || p_new_config ) ;
1380 END IF;
1381
1382
1383 -- Set the message context to the top model line.
1384 oe_bulk_msg_pub.set_msg_context
1385 ( p_entity_code => 'LINE'
1386 ,p_entity_id => p_line_rec.line_id(p_line_index)
1387 ,p_header_id => p_line_rec.header_id(p_line_index)
1388 ,p_line_id => p_line_rec.line_id(p_line_index)
1389 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref(p_line_index)
1390 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref(p_line_index)
1391 ,p_source_document_id => NULL
1392 ,p_source_document_line_id => NULL
1393 ,p_order_source_id => p_line_rec.order_source_id(p_line_index)
1394 ,p_source_document_type_id => NULL );
1395
1396
1397 IF p_new_config = 'N' AND
1398 l_book_flag = 'N' AND
1399 l_qty_match_flag = 'Y'
1400 THEN
1401 -- We do not need to call Batch Validation API.
1402 -- Instead call CZ_CONFIG_API_PUB.verify_configuration
1403
1404 IF l_debug_level > 0 THEN
1405 OE_Debug_Pub.Add('Calling CZ_CONFIG_API_PUB.verify_configuration');
1406 END IF;
1407
1408 CZ_CONFIG_API_PUB.verify_configuration
1409 ( p_api_version => 1.0,
1410 p_config_hdr_id => l_config_header_id,
1411 p_config_rev_nbr => l_config_rev_nbr,
1412 x_exists_flag => l_exists_flag,
1413 x_valid_flag => l_valid_flag,
1414 x_complete_flag => l_complete_flag,
1415 x_return_status => l_return_status,
1416 x_msg_count => l_msg_count,
1417 x_msg_data => l_msg_data );
1418
1419
1420 IF l_debug_level > 0 THEN
1421 oe_debug_pub.add (' Exists Flag :' ||l_exists_flag,2);
1422 oe_debug_pub.add (' Valid Flag :'|| l_valid_flag,2);
1423 oe_debug_pub.add (' Complete Flag :'|| l_complete_flag,2);
1424 oe_debug_pub.add (' Return Status :'|| l_return_status,2);
1425 oe_debug_pub.add (' Message Count :'|| l_msg_count,2);
1426 oe_debug_pub.add (' Message Data :'|| l_msg_data,2);
1427 END IF;
1428
1429 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1432 RAISE FND_API.G_EXC_ERROR;
1433 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1434 IF l_exists_flag = FND_API.G_FALSE THEN
1435 IF l_debug_level > 0 THEN
1436 oe_debug_pub.add('Configuration Does not Exist '|| l_msg_data,2);
1437 END IF;
1438 RAISE FND_API.G_EXC_ERROR;
1439 ELSE
1440 IF l_debug_level > 0 THEN
1441 oe_debug_pub.add (' Configuration Exists ',2);
1442 END IF;
1443
1444 IF l_valid_flag = FND_API.G_FALSE THEN
1445 l_valid_config := 'FALSE';
1446 ELSE
1447 l_valid_config := 'TRUE';
1448 END IF;
1449
1450 IF l_complete_flag = FND_API.G_FALSE THEN
1451 l_complete_config := 'FALSE';
1452 ELSE
1453 IF l_debug_level > 0 THEN
1454 oe_debug_pub.add('Configuration Exists, valid and Complete ',2);
1455 END IF;
1456 l_complete_config := 'TRUE';
1457 END IF;
1458 END IF; -- if exist flag = false
1459 END IF; -- if success
1460
1461 IF l_debug_level > 0 THEN
1462 oe_debug_pub.add('CALLING Message_From_CZ' , 2 );
1463 END IF;
1464 Message_From_CZ(
1465 p_line_rec => p_line_rec,
1466 p_line_index => p_line_index,
1467 p_valid_config => l_valid_config,
1468 p_complete_config => l_complete_config,
1469 p_config_header_id => l_config_header_id,
1470 p_config_rev_nbr => l_config_rev_nbr,
1471 x_return_status => l_return_status);
1472
1473 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1474 RAISE FND_API.G_EXC_ERROR;
1475 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 END IF;
1478
1479
1480 ELSE -- call batch val
1481
1482 IF l_debug_level > 0 THEN
1483 oe_debug_pub.add('CALLING CREATE_HDR_XML' , 2 );
1484 END IF;
1485 Create_hdr_xml( p_line_rec => p_line_rec,
1486 p_line_index => p_line_index,
1487 x_xml_hdr => l_xml_hdr,
1488 x_return_status => l_return_status);
1489
1490 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1491 RAISE FND_API.G_EXC_ERROR;
1492 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1494 END IF;
1495
1496 IF l_debug_level > 0 THEN
1497 oe_debug_pub.add('CALLING SEND_INPUT_XML' , 2 );
1498 END IF;
1499
1500 Send_input_xml
1501 ( p_line_rec => p_line_rec,
1502 p_line_index => p_line_index,
1503 p_new_config => p_new_config,
1504 p_xml_hdr => l_xml_hdr,
1505 x_out_xml_msg => l_xml_message,
1506 x_return_status => l_return_status,
1507 p_batch_validate_time => p_batch_validate_time);
1508
1509 IF l_debug_level > 0 THEN
1510 oe_debug_pub.add
1511 ('AFTER CALLING SEND_INPUT_XML: '||L_RETURN_STATUS , 2 );
1512 END IF;
1513
1514 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1516 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1517 RAISE FND_API.G_EXC_ERROR;
1518 END IF;
1519
1520 l_xml_message := UPPER(l_xml_message);
1521
1522 -- extract data from xml message.
1523 IF l_debug_level > 0 THEN
1524 oe_debug_pub.add('CALLING PARSE_OUTPUT_XML' , 2 );
1525 END IF;
1526
1527 Parse_Output_xml
1528 ( p_xml => l_xml_message,
1529 p_line_index => p_line_index,
1530 p_line_rec => p_line_rec,
1531 x_valid_config => l_valid_config,
1532 x_complete_config => l_complete_config,
1533 x_config_header_id => l_config_header_id,
1534 x_config_rev_nbr => l_config_rev_nbr,
1535 x_return_status => l_return_status );
1536
1537 IF l_debug_level > 0 THEN
1538 oe_debug_pub.add('AFTER CALLING PARSE_OUTPUT_XML: '||L_RETURN_STATUS , 2 );
1539 END IF;
1540
1541 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546
1547 IF (l_valid_config = 'FALSE' OR l_complete_config = 'FALSE') THEN
1548 IF l_debug_level > 0 THEN
1549 oe_debug_pub.add('ERROR CONFIG' , 2 );
1550 END IF;
1551 RAISE FND_API.G_EXC_ERROR;
1552
1553 END IF;
1554
1555 -- As a part of the Batch Validation CRN can change for a configuration.
1556 -- Update CHI, CRN on all lines in that model.
1557
1558 I := 0;
1559 -- l_top_model_line_id := p_line_rec.top_model_line_id(p_line_index);
1560
1561 WHILE p_line_index + I <= p_line_rec.line_id.count AND
1562 p_line_rec.top_model_line_ref(p_line_index + I) =
1563 p_line_rec.top_model_line_ref(p_line_index) AND
1564 p_line_rec.header_id(p_line_index + I) = p_line_rec.header_id(p_line_index) LOOP
1565
1566 p_line_rec.config_header_id(p_line_index + I ) := l_config_header_id;
1567 p_line_rec.config_rev_nbr(p_line_index + I ) := l_config_rev_nbr;
1568 I := I + 1;
1569 END LOOP;
1570
1571 END IF; -- if skip batch val
1572
1573
1574 IF l_debug_level > 0 THEN
1575 oe_debug_pub.add('EXITING OE_BULK_CONFIG_UTIL.CONFIGURATOR_VALIDATION' , 1);
1576 END IF;
1577
1578
1579 EXCEPTION
1580
1581 WHEN FND_API.G_EXC_ERROR THEN
1582 IF l_debug_level > 0 THEN
1583 oe_debug_pub.add('AN EXC ERROR RAISED IN Validate_Configuration', 1);
1584 END IF;
1585 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_VALIDATION_FAILURE');
1586 OE_Bulk_Msg_Pub.Add;
1587 p_line_rec.lock_control(p_line_index) := -99;
1588
1589 WHEN OTHERS THEN
1590 IF l_debug_level > 0 THEN
1591 oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Validate_Configuration');
1592 oe_debug_pub.add(substr(sqlerrm,1,240));
1593 END IF;
1594
1595 OE_BULK_MSG_PUB.Add_Exc_Msg
1596 ( G_PKG_NAME,
1597 'Validate_Configuration'
1598 );
1599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1600
1601 END Validate_Configuration;
1602
1603
1604
1605 ---------------------------------------------------------------------
1606 --
1607 -- PROCEDURE Pre_Process_Configurator
1608 --
1609 ---------------------------------------------------------------------
1610
1611 PROCEDURE Pre_Process_Configurator(
1612 p_batch_id IN NUMBER
1613 ,p_validate_only IN VARCHAR2
1614 ,p_use_configurator IN VARCHAR2
1615 ,p_validate_configurations IN VARCHAR2
1616 )
1617 IS
1618
1619 -- Load the model records from the order import interface tables. For importing
1620 -- pre-created configurations, users will specify CRN(config_rev_nbr),
1621 -- CHI(config_header_id) and either CI(configuration_id) or CC (component_code)
1622 -- If p_validate_configuration = Y Then use cursor c_line_config1 that has extra
1623 -- joins to CZ_CONFIG_DETAILS_V Z, OE_ACTIONS_INTERFACE a. These are needed
1624 -- to derive extra attributes that are needed for CZ batch validation API.
1625 -- Else then use cursor c_line_config2
1626 --
1627 CURSOR c_line_config1 IS
1628 SELECT l.line_id,
1629 l.top_model_line_ref,
1630 l.config_header_id,
1631 l.config_rev_nbr,
1632 nvl(l.configuration_id, z.CONFIG_ITEM_ID) configuration_id,
1633 l.orig_sys_document_ref,
1634 l.orig_sys_line_ref,
1635 l.orig_sys_shipment_ref,
1636 l.order_source_id,
1637 l.ordered_quantity,
1638 l.inventory_item_id,
1639 h.header_id,
1640 nvl(h.booked_flag,decode(a.order_source_id,NULL,'N','Y')) booked_flag,
1641 decode(l.ordered_quantity, nvl(z.quantity, l.ordered_quantity),'Y','N')
1642 cz_qty_match_flag,
1643 nvl(l.component_code, z.component_code) component_code
1644 FROM OE_HEADERS_IFACE_ALL H,
1645 OE_LINES_IFACE_ALL L ,
1646 CZ_CONFIG_DETAILS_V Z,
1647 OE_ACTIONS_INTERFACE a
1648 WHERE h.batch_id = p_batch_id
1649 AND h.order_source_id = l.order_source_id
1650 AND h.orig_sys_document_ref = l.orig_sys_document_ref
1651 AND nvl(h.error_flag,'N') = 'N'
1652 AND nvl(l.error_flag,'N') = 'N'
1653 AND nvl(l.rejected_flag,'N') = 'N'
1654 AND l.item_type_code = 'MODEL'
1655 AND l.top_model_line_ref = l.orig_sys_line_ref
1656 AND nvl(l.config_header_id,-1) = z.config_hdr_id (+)
1657 AND nvl(l.config_rev_nbr, -1) = z.config_rev_nbr(+)
1658 -- AND NVL(l.configuration_id,-1) = z.config_item_id(+)
1659 AND NVL(l.component_code, '-1') = z.component_code(+)
1660 AND a.order_source_id(+) = h.order_source_id
1661 AND a.orig_sys_document_ref(+) = h.orig_sys_document_ref
1662 AND a.operation_code(+) = 'BOOK_ORDER';
1663
1664
1665 CURSOR c_line_config2 IS
1666 SELECT l.line_id,
1667 h.header_id,
1668 l.top_model_line_ref,
1669 l.config_header_id,
1670 l.config_rev_nbr,
1671 l.configuration_id,
1672 l.orig_sys_document_ref,
1673 l.orig_sys_line_ref,
1674 l.orig_sys_shipment_ref,
1675 l.order_source_id,
1676 l.ordered_quantity
1677 FROM OE_HEADERS_IFACE_ALL H,
1678 OE_LINES_IFACE_ALL L
1679 WHERE h.batch_id = p_batch_id
1680 AND h.order_source_id = l.order_source_id
1681 AND h.orig_sys_document_ref = l.orig_sys_document_ref
1682 AND nvl(h.error_flag,'N') = 'N'
1683 AND nvl(l.error_flag,'N') = 'N'
1684 AND nvl(l.rejected_flag,'N') = 'N'
1685 AND l.item_type_code = 'MODEL'
1686 AND l.top_model_line_ref = l.orig_sys_line_ref;
1687
1688
1689 l_line_rec OE_WSH_BULK_GRP.LINE_REC_TYPE;
1690 l_config_rec OE_BULK_CONFIG_UTIL.CONFIG_REC_TYPE;
1691 I NUMBER;
1692
1693 l_Need_Bom_Explosion VARCHAR2(1) := 'N';
1694
1695 l_order_source_id NUMBER := -99;
1696 l_orig_sys_document_ref VARCHAR2(50) := '-99';
1697
1698 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1699
1700 l_start_time NUMBER;
1701 l_end_time NUMBER;
1702 l_config_validate_time NUMBER := 0;
1703 l_batch_validate_time NUMBER := 0;
1704
1705
1706 BEGIN
1707 IF l_debug_level > 0 THEN
1708 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Pre_Process_Configurator ') ;
1709 END IF;
1710
1711 -- Load the cursor into l_line_rec
1712 IF nvl(p_validate_configurations, 'N') = 'Y' THEN
1713 IF l_debug_level > 0 THEN
1714 oe_debug_pub.add( 'before OPEN c_line_config1 ') ;
1715 END IF;
1716
1717 OPEN c_line_config1;
1718
1719 IF l_debug_level > 0 THEN
1720 oe_debug_pub.add( 'after OPEN c_line_config1 ') ;
1721 END IF;
1722
1723 FETCH c_line_config1 BULK COLLECT INTO
1724 l_line_rec.line_id,
1725 l_line_rec.top_model_line_ref,
1726 l_line_rec.config_header_id,
1727 l_line_rec.config_rev_nbr,
1728 l_line_rec.configuration_id,
1729 l_line_rec.orig_sys_document_ref,
1730 l_line_rec.orig_sys_line_ref,
1731 l_line_rec.orig_sys_shipment_ref,
1732 l_line_rec.order_source_id,
1733 l_line_rec.ordered_quantity,
1734 l_line_rec.inventory_item_id,
1735 l_line_rec.header_id,
1736 l_line_rec.booked_flag,
1737 l_line_rec.cz_qty_match_flag,
1738 l_line_rec.component_code;
1739
1740 CLOSE c_line_config1;
1741 ELSE
1742 IF l_debug_level > 0 THEN
1743 oe_debug_pub.add( 'before OPEN c_line_config2 ') ;
1744 END IF;
1745
1746 OPEN c_line_config2;
1747
1748 IF l_debug_level > 0 THEN
1749 oe_debug_pub.add( 'after OPEN c_line_config2 ') ;
1750 END IF;
1751
1752 FETCH c_line_config2 BULK COLLECT INTO
1753 l_line_rec.line_id,
1754 l_line_rec.header_id,
1755 l_line_rec.top_model_line_ref,
1756 l_line_rec.config_header_id,
1757 l_line_rec.config_rev_nbr,
1758 l_line_rec.configuration_id,
1759 l_line_rec.orig_sys_document_ref,
1760 l_line_rec.orig_sys_line_ref,
1761 l_line_rec.orig_sys_shipment_ref,
1762 l_line_rec.order_source_id,
1763 l_line_rec.ordered_quantity;
1764
1765 CLOSE c_line_config2;
1766
1767 END IF; -- p_validate_configurations = 'Y'
1768
1769 -- Exit procedure if no model line fetched
1770 IF l_line_rec.config_header_id.count < 1 THEN
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.add( 'no model line exists in batch: '|| p_batch_id) ;
1773 END IF;
1774 RETURN;
1775 END IF;
1776
1777
1778 Extend_Line_Rec
1779 (p_count => l_line_rec.line_id.count
1780 ,p_line_rec => l_line_rec
1781 ,p_config_rec => l_config_rec
1782 );
1783
1784 IF l_debug_level > 0 THEN
1785 Print_Line_Rec( p_line_rec => l_line_rec,
1786 p_config_rec => l_config_rec);
1787 END IF;
1788
1789
1790 -- Looping over the model lines
1791 FOR I IN 1..l_line_rec.line_id.count LOOP
1792
1793 IF l_debug_level > 0 THEN
1794 oe_debug_pub.add( 'IN PRE_PROCESS_CONFIGURATOR LOOP INDEX : I = '|| I ) ;
1795 END IF;
1796
1797
1798 -- skip line if order has config error
1799 IF p_validate_only = 'N' AND
1800 l_order_source_id = l_line_rec.order_source_id(I) AND
1801 l_orig_sys_document_ref = l_line_rec.orig_sys_document_ref(I) THEN
1802
1803 IF l_debug_level > 0 Then
1804 oe_debug_pub.add('Skip line in Pre_Process_Configurator(): '||l_line_rec.line_id(I));
1805 END IF;
1806
1807 GOTO SKIP_THE_LINE;
1808
1809 END IF;
1810
1811 -- Set the message context for errors.
1812 oe_bulk_msg_pub.set_msg_context
1813 ( p_entity_code => 'LINE'
1814 ,p_entity_id => l_line_rec.line_id(I)
1815 ,p_header_id => l_line_rec.header_id(I)
1816 ,p_line_id => l_line_rec.line_id(I)
1817 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref(I)
1818 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref(I)
1819 ,p_source_document_id => NULL
1820 ,p_source_document_line_id => NULL
1821 ,p_order_source_id => l_line_rec.order_source_id(I)
1822 ,p_source_document_type_id => NULL );
1823
1824
1825 Validate_Config_Attributes(
1826 p_use_Configurator => 'Y',
1827 p_line_rec => l_line_rec,
1828 p_line_index => I
1829 );
1830
1831
1832 -- If configuration is pre-created that is the CHI, CNR and CI/CC are not NULL
1833 IF l_line_rec.config_header_id(I) IS NOT NULL AND
1834 l_line_rec.config_rev_nbr(I) IS NOT NULL AND
1835 ( l_line_rec.configuration_id(I) IS NOT NULL OR
1836 l_line_rec.component_code(I) IS NOT NULL)
1837 THEN
1838 IF l_debug_level > 0 THEN
1839 oe_debug_pub.add( 'Pre-created configuration' ) ;
1840 END IF;
1841
1842 IF nvl(p_validate_configurations, 'N') = 'Y' THEN
1843
1844 SELECT hsecs INTO l_start_time from v$timer;
1845
1846 Validate_Configuration(
1847 p_new_config => 'N',
1848 p_line_rec => l_line_rec,
1849 p_line_index => I,
1850 p_batch_validate_time => l_batch_validate_time
1851 );
1852
1853 SELECT hsecs INTO l_end_time from v$timer;
1854 l_config_validate_time := l_config_validate_time + (l_end_time-l_start_time)/100;
1855
1856 END IF;
1857
1858 -- For Top Model record use following cursor to load Model + all child
1859 -- lines into temp table. This will include missing child lines on
1860 -- iface(Case when only model line is specified and partial/none child
1861 -- lines are specified)
1862
1863 -- insert missing child lines
1864 INSERT INTO oe_config_details_tmp
1865 (
1866 LINE_ID ,
1867 TOP_MODEL_LINE_ID,
1868 ATO_LINE_ID,
1869 LINK_TO_LINE_ID,
1870 ORDER_SOURCE_ID,
1871 ORIG_SYS_DOCUMENT_REF,
1872 ORIG_SYS_LINE_REF ,
1873 ORIG_SYS_SHIPMENT_REF ,
1874 TOP_MODEL_LINE_REF ,
1875 INVENTORY_ITEM_ID,
1876 --ORDERED_ITEM,
1877 UOM_CODE,
1878 ORDERED_QUANTITY,
1879 COMPONENT_CODE,
1880 COMPONENT_SEQUENCE_ID,
1881 SORT_ORDER ,
1882 CONFIG_HEADER_ID,
1883 CONFIG_REV_NBR ,
1884 CONFIGURATION_ID,
1885 TOP_BILL_SEQUENCE_ID,
1886 ITEM_TYPE_CODE,
1887 LINE_TYPE,
1888 CZ_QTY_MATCH_FLAG,
1889 HIGH_QUANTITY,
1890 LOW_QUANTITY,
1891 MUTUALLY_EXCLUSIVE_OPTIONS,
1892 BOM_ITEM_TYPE,
1893 LOCK_CONTROL,
1894 REPLENISH_TO_ORDER_FLAG )
1895 SELECT
1896 oe_order_lines_s.nextval Line_id,
1897 l_line_rec.line_id(I) top_model_line_id,
1898 decode(z.config_item_id, z.ato_config_item_id, oe_order_lines_s.currval,NULL)
1899 ato_line_id,
1900 decode(z.config_item_id, z.ato_config_item_id,l_line_rec.line_id(I),NULL)
1901 link_to_line_id,
1902 l_line_rec.order_source_id(I) order_source_id,
1903 l_line_rec.orig_sys_document_ref(I) orig_sys_document_ref,
1904 'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
1905 null orig_sys_shipment_ref,
1906 l_line_rec.orig_sys_line_ref(I) top_model_line_ref,
1907 z.inventory_item_id,
1908 -- NULL ordered_item,
1909 z.uom_code,
1910 z.quantity ordered_quantity,
1911 z.Component_code,
1912 z.Component_sequence_id,
1913 z.Bom_Sort_order,
1914 z.config_hdr_id,
1915 z.Config_rev_nbr,
1916 z.config_item_id Configuration_id,
1917 null top_bill_sequence_id,
1918 null item_type_code,
1919 z.line_type,
1920 'Y' cz_qty_match_flag,
1921 null HIGH_QUANTITY,
1922 null LOW_QUANTITY,
1923 null MUTUALLY_EXCLUSIVE_OPTIONS,
1924 null BOM_ITEM_TYPE,
1925 null LOCK_CONTROL,
1926 null REPLENISH_TO_ORDER_FLAG
1927 FROM cz_config_details_v z
1928 WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
1929 AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
1930 AND NOT EXISTS (
1931 Select l.line_id
1932 from oe_lines_iface_all l
1933 WHERE NVL(l.configuration_id, z.config_item_id) = z.config_item_id
1934 AND NVL(l.component_code, z.component_code) = z.component_code
1935 AND l.top_model_line_ref = l_line_rec.ORIG_SYS_LINE_REF(I)
1936 AND l.orig_sys_document_ref = l_line_rec.ORIG_SYS_DOCUMENT_REF(I)
1937 AND l.order_source_id = l_line_rec.ORDER_SOURCE_ID(I));
1938
1939
1940 IF l_debug_level > 0 THEN
1941 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' MISSING CHILD TO TMP'||I , 3 ) ;
1942 END IF;
1943
1944 -- insert lines from interface table
1945 INSERT INTO oe_config_details_tmp
1946 (
1947 LINE_ID ,
1948 TOP_MODEL_LINE_ID,
1949 ATO_LINE_ID,
1950 LINK_TO_LINE_ID,
1951 ORDER_SOURCE_ID,
1952 ORIG_SYS_DOCUMENT_REF,
1953 ORIG_SYS_LINE_REF ,
1954 ORIG_SYS_SHIPMENT_REF ,
1955 TOP_MODEL_LINE_REF ,
1956 INVENTORY_ITEM_ID,
1957 -- ORDERED_ITEM,
1958 UOM_CODE,
1959 ORDERED_QUANTITY,
1960 COMPONENT_CODE,
1961 COMPONENT_SEQUENCE_ID,
1962 SORT_ORDER ,
1963 CONFIG_HEADER_ID,
1964 CONFIG_REV_NBR ,
1965 CONFIGURATION_ID,
1966 TOP_BILL_SEQUENCE_ID,
1967 ITEM_TYPE_CODE,
1968 LINE_TYPE,
1969 CZ_QTY_MATCH_FLAG,
1970 HIGH_QUANTITY,
1971 LOW_QUANTITY,
1972 MUTUALLY_EXCLUSIVE_OPTIONS,
1973 BOM_ITEM_TYPE,
1974 LOCK_CONTROL,
1975 REPLENISH_TO_ORDER_FLAG )
1976 SELECT
1977 l.Line_id,
1978 l_line_rec.line_id(I) top_model_line_id,
1979 decode(z.config_item_id, z.ato_config_item_id,l.line_id,NULL)
1980 ato_line_id,
1981 decode(z.config_item_id, z.ato_config_item_id,l_line_rec.line_id(I),NULL)
1982 link_to_line_id,
1983 l_line_rec.order_source_id(I) order_source_id,
1984 l_line_rec.orig_sys_document_ref(I) orig_sys_document_ref,
1985 l_line_rec.orig_sys_line_ref(I) orig_sys_line_ref,
1986 null orig_sys_shipment_ref,
1987 l.top_model_line_ref,
1988 z.inventory_item_id,
1989 -- l_line_rec.ordered_item(I) ordered_item,
1990 z.uom_code,
1991 z.quantity ordered_quantity,
1992 z.Component_code,
1993 z.Component_sequence_id,
1994 z.Bom_Sort_order,
1995 z.config_hdr_id,
1996 z.Config_rev_nbr,
1997 z.config_item_id Configuration_id,
1998 null top_bill_sequence_id,
1999 l.item_type_code,
2000 z.line_type,
2001 decode(l.ordered_quantity, z.quantity,'Y','N')
2002 cz_qty_match_flag,
2003 null HIGH_QUANTITY,
2004 null LOW_QUANTITY,
2005 null MUTUALLY_EXCLUSIVE_OPTIONS,
2006 null BOM_ITEM_TYPE,
2007 l_line_rec.lock_control(I) LOCK_CONTROL,
2008 null REPLENISH_TO_ORDER_FLAG
2009 FROM cz_config_details_v z, oe_lines_iface_all l
2010 WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
2011 AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
2012 AND NVL(l.configuration_id,z.config_item_id) = z.config_item_id
2013 AND NVL(l.component_code, z.component_code) = z.component_code
2014 AND l.orig_sys_document_ref = l_line_rec.ORIG_SYS_DOCUMENT_REF(I)
2015 AND l.order_source_id = l_line_rec.ORDER_SOURCE_ID(I)
2016 AND l.top_model_line_ref = l_line_rec.ORIG_SYS_LINE_REF(I) ;
2017
2018 IF l_debug_level > 0 THEN
2019 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' LINES TO TMP FROM IFACE '||I , 3 ) ;
2020 END IF;
2021
2022
2023 ELSE -- new configuration
2024
2025 -- User is trying to import a new configuration. We will set a flag
2026 -- to indicate that we will need to call BOM_Explosion for these lines
2027 -- to get component_code and other attributes from BOM.
2028
2029 l_Need_Bom_Explosion := 'Y';
2030
2031 END IF;
2032
2033
2034 <<SKIP_THE_LINE>>
2035
2036 IF p_validate_only = 'N' AND nvl(l_line_rec.lock_control(I), 0) = -99 THEN
2037 l_order_source_id := l_line_rec.order_source_id(I);
2038 l_orig_sys_document_ref := l_line_rec.orig_sys_document_ref(I);
2039
2040 IF l_debug_level > 0 THEN
2041 oe_debug_pub.add( 'SKIP order_source_id: '||l_order_source_id, 3 ) ;
2042 oe_debug_pub.add( 'SKIP orig_sys_document_ref: '||l_orig_sys_document_ref, 3 ) ;
2043 END IF;
2044
2045 END IF;
2046
2047 END LOOP; -- loop over the model lines
2048
2049
2050 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in config_validate is (sec) '
2051 || l_config_validate_time );
2052
2053 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in batch_validate is (sec) '
2054 || l_batch_validate_time );
2055
2056
2057 -- Need to call Pre_Process_Bom for new configuration
2058 IF l_Need_Bom_Explosion = 'Y' THEN
2059 IF l_debug_level > 0 THEN
2060 oe_debug_pub.add( 'Need to call Pre_Process_Bom for new configuration ') ;
2061 END IF;
2062
2063 Pre_Process_Bom (
2064 p_batch_id => p_batch_id,
2065 p_validate_only => p_validate_only,
2066 p_use_configurator => p_use_configurator,
2067 p_validate_configurations => p_validate_configurations
2068 );
2069
2070 END IF;
2071
2072 IF l_debug_level > 0 THEN
2073 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Pre_Process_Configurator ') ;
2074 END IF;
2075
2076 EXCEPTION
2077 WHEN NO_DATA_FOUND THEN
2078 IF l_debug_level > 0 THEN
2079 oe_debug_pub.add('NO DATA FOUND in OE_BULK_CONFIG_UTIL.Pre_Process_Configurator ' , 1);
2080 END IF;
2081
2082 WHEN OTHERS THEN
2083
2084 IF c_line_config1%ISOPEN THEN
2085 CLOSE c_line_config1;
2086 ELSIF c_line_config2%ISOPEN THEN
2087 CLOSE c_line_config2;
2088 END IF;
2089
2090 IF l_debug_level > 0 THEN
2091 oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Pre_Process_Configurator');
2092 oe_debug_pub.add(substr(sqlerrm,1,240));
2093 END IF;
2094
2095 OE_BULK_MSG_PUB.Add_Exc_Msg
2096 ( G_PKG_NAME,
2097 'Pre_Process_Configurator'
2098 );
2099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2100
2101 END Pre_Process_Configurator;
2102
2103
2104
2105 ---------------------------------------------------------------------
2106 --
2107 -- PROCEDURE Pre_Process_Bom
2108 --
2109 ---------------------------------------------------------------------
2110
2111 PROCEDURE Pre_Process_Bom(
2112 p_batch_id IN NUMBER
2113 ,p_validate_only IN VARCHAR2
2114 ,p_use_configurator IN VARCHAR2
2115 ,p_validate_configurations IN VARCHAR2
2116 )
2117 IS
2118
2119
2120 -- Load the line records from the order import interface tables.
2121 -- If p_use_configurator = Y Then this procedure is called from
2122 -- pre_process_Configurator(), load the lines that with new configurations
2123 -- where the CHI, CNR and CI have NULL value, so to use cursor c_line_bom1
2124 -- Else use cursor c_line_bom2
2125
2126 CURSOR c_line_bom1 IS
2127 Select l.line_id,
2128 l.Top_model_line_REF,
2129 null Top_model_line_id,
2130 l.Component_code,
2131 l.Component_sequence_id,
2132 l.sort_order,
2133 l.config_header_id,
2134 l.config_rev_nbr,
2135 l.configuration_id,
2136 l.item_type_code,
2137 l.order_source_id,
2138 l.orig_sys_document_ref,
2139 l.orig_sys_line_ref,
2140 l.orig_sys_shipment_ref,
2141 l.ordered_quantity,
2142 -- l.top_bill_sequence_id, -- Donot need this column
2143 l.inventory_item_id,
2144 l.customer_item_id_type,
2145 l.customer_item_id,
2146 l.customer_item_name,
2147 l.sold_to_org_id,
2148 l.ship_from_org_id,
2149 h.header_id,
2150 nvl(h.booked_flag,decode(a.order_source_id,NULL,'N','Y')) booked_flag,
2151 'N' cz_qty_match_flag
2152 FROM OE_HEADERS_IFACE_ALL H,
2153 OE_LINES_IFACE_ALL L,
2154 OE_ACTIONS_INTERFACE a
2155 WHERE h.batch_id = p_batch_id
2156 AND h.order_source_id = l.order_source_id
2157 AND h.orig_sys_document_ref = l.orig_sys_document_ref
2158 AND nvl(h.error_flag,'N') = 'N'
2159 AND nvl(l.error_flag,'N') = 'N'
2160 AND nvl(l.rejected_flag,'N') = 'N'
2161 AND l.top_model_line_ref IS NOT NULL
2162 AND l.config_header_id IS NULL
2163 AND l.config_rev_nbr IS NULL
2164 AND l.configuration_id IS NULL
2165 AND a.order_source_id(+) = h.order_source_id
2166 AND a.orig_sys_document_ref(+) = h.orig_sys_document_ref
2167 AND a.operation_code(+) = 'BOOK_ORDER'
2168 ORDER BY h.header_id,
2169 l.top_model_line_ref,
2170 decode(item_type_code,'MODEL',item_type_code,'XXX'),
2171 l.component_code;
2172
2173 CURSOR c_line_bom2 IS
2174 Select l.line_id,
2175 h.header_id,
2176 l.Top_model_line_REF,
2177 null Top_model_line_id,
2178 l.Component_code,
2179 l.Component_sequence_id,
2180 l.sort_order,
2181 l.config_header_id,
2182 l.config_rev_nbr,
2183 l.configuration_id,
2184 l.item_type_code,
2185 l.order_source_id,
2186 l.orig_sys_document_ref,
2187 l.orig_sys_line_ref,
2188 l.orig_sys_shipment_ref,
2189 l.ordered_quantity,
2190 -- l.top_bill_sequence_id,
2191 l.inventory_item_id,
2192 l.customer_item_id_type,
2193 l.customer_item_id,
2194 l.customer_item_name,
2195 l.sold_to_org_id,
2196 l.ship_from_org_id
2197 FROM OE_HEADERS_IFACE_ALL H,
2198 OE_LINES_IFACE_ALL L
2199 WHERE h.batch_id = p_batch_id
2200 AND h.order_source_id = l.order_source_id
2201 AND h.orig_sys_document_ref = l.orig_sys_document_ref
2202 AND nvl(h.error_flag,'N') = 'N'
2203 AND nvl(l.error_flag,'N') = 'N'
2204 AND nvl(l.rejected_flag,'N') = 'N'
2205 AND top_model_line_ref IS NOT NULL
2206 ORDER BY h.header_id,
2207 l.top_model_line_ref,
2208 decode(item_type_code,'MODEL',item_type_code,'XXX'),
2209 l.component_code;
2210
2211 l_line_rec OE_WSH_BULK_GRP.LINE_REC_TYPE;
2212 l_config_rec OE_BULK_CONFIG_UTIL.CONFIG_REC_TYPE;
2213 I NUMBER;
2214 l_curr_model_index NUMBER;
2215 l_curr_ato_index NUMBER;
2216
2217 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2218
2219 l_msg_data VARCHAR2(2000);
2220 l_error_code VARCHAR2(2000);
2221 l_return_status VARCHAR2(30);
2222
2223 l_order_source_id NUMBER := -99;
2224 l_orig_sys_document_ref VARCHAR2(50) := '-99';
2225
2226 l_start_time NUMBER;
2227 l_end_time NUMBER;
2228 l_config_validate_time NUMBER := 0;
2229 l_batch_validate_time NUMBER := 0;
2230
2231
2232 BEGIN
2233 IF l_debug_level > 0 THEN
2234 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Pre_Process_Bom ', 1) ;
2235 END IF;
2236
2237 -----------------------------------------------------------------
2238 -- Load this cursor into Line Rec (OE_WSH_BULK_GRP.line_rec_type)
2239 -----------------------------------------------------------------
2240
2241
2242 IF nvl(p_use_configurator, 'N') = 'Y' THEN
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add( 'before OPEN c_line_bom1 ') ;
2245 END IF;
2246
2247 OPEN c_line_bom1;
2248
2249 IF l_debug_level > 0 THEN
2250 oe_debug_pub.add( 'after OPEN c_line_bom1 ') ;
2251 END IF;
2252
2253 FETCH c_line_bom1 BULK COLLECT INTO
2254 l_line_rec.line_id,
2255 l_line_rec.Top_model_line_REF,
2256 l_line_rec.Top_model_line_id,
2257 l_line_rec.Component_code,
2258 l_line_rec.Component_sequence_id,
2259 l_line_rec.sort_order,
2260 l_line_rec.config_header_id,
2261 l_line_rec.config_rev_nbr,
2262 l_line_rec.configuration_id,
2263 l_line_rec.item_type_code,
2264 l_line_rec.order_source_id,
2265 l_line_rec.orig_sys_document_ref,
2266 l_line_rec.orig_sys_line_ref,
2267 l_line_rec.orig_sys_shipment_ref,
2268 l_line_rec.ordered_quantity,
2269 l_line_rec.inventory_item_id,
2270 l_line_rec.item_identifier_type,
2271 l_line_rec.ordered_item_id,
2272 l_line_rec.ordered_item,
2273 l_line_rec.sold_to_org_id,
2274 l_line_rec.ship_from_org_id,
2275 l_line_rec.header_id,
2276 l_line_rec.booked_flag,
2277 l_line_rec.cz_qty_match_flag;
2278
2279 CLOSE c_line_bom1;
2280 ELSE
2281 IF l_debug_level > 0 THEN
2282 oe_debug_pub.add( 'before OPEN c_line_bom2 ', 3) ;
2283 END IF;
2284
2285 OPEN c_line_bom2;
2286
2287 IF l_debug_level > 0 THEN
2288 oe_debug_pub.add( 'after OPEN c_line_bom2 ') ;
2289 END IF;
2290
2291 FETCH c_line_bom2 BULK COLLECT INTO
2292 l_line_rec.line_id,
2293 l_line_rec.header_id,
2294 l_line_rec.Top_model_line_REF,
2295 l_line_rec.Top_model_line_id,
2296 l_line_rec.Component_code,
2297 l_line_rec.Component_sequence_id,
2298 l_line_rec.sort_order,
2299 l_line_rec.config_header_id,
2300 l_line_rec.config_rev_nbr,
2301 l_line_rec.configuration_id,
2302 l_line_rec.item_type_code,
2303 l_line_rec.order_source_id,
2304 l_line_rec.orig_sys_document_ref,
2305 l_line_rec.orig_sys_line_ref,
2306 l_line_rec.orig_sys_shipment_ref,
2307 l_line_rec.ordered_quantity,
2308 l_line_rec.inventory_item_id,
2309 l_line_rec.item_identifier_type,
2310 l_line_rec.ordered_item_id,
2311 l_line_rec.ordered_item,
2312 l_line_rec.sold_to_org_id,
2313 l_line_rec.ship_from_org_id;
2314
2315 CLOSE c_line_bom2;
2316
2317 END IF; -- p_use_configurator = 'Y'
2318
2319 Extend_Line_Rec
2320 (p_count => l_line_rec.line_id.count
2321 ,p_line_rec => l_line_rec
2322 ,p_config_rec => l_config_rec
2323 );
2324
2325 ------------------------------------------
2326 -- Start Looping over l_line_rec tables:
2327 ------------------------------------------
2328 FOR I IN 1..l_line_rec.line_id.count LOOP
2329
2330 IF l_debug_level > 0 THEN
2331 oe_debug_pub.add( ' IN PRE_PROCESS_BOM LOOP INDEX : I = '|| I ) ;
2332 END IF;
2333
2334
2335 IF l_debug_level > 0 THEN
2336 oe_debug_pub.add( 'order_source_id = '|| l_line_rec.order_source_id(I) ) ;
2337 oe_debug_pub.add( 'orig_sys_document_ref = '||l_line_rec.orig_sys_document_ref(I) ) ;
2338
2339 END IF;
2340
2341
2342 IF p_validate_only = 'N' AND
2343 l_order_source_id = l_line_rec.order_source_id(I) AND
2344 l_orig_sys_document_ref = l_line_rec.orig_sys_document_ref(I) THEN
2345
2346 IF l_debug_level > 0 Then
2347 oe_debug_pub.add('Skip line in Pre_Process_Bom(): '||l_line_rec.line_id(I));
2348 END IF;
2349
2350 GOTO SKIP_THE_LINE;
2351
2352 END IF;
2353
2354 -- Set the message context for errors.
2355 oe_bulk_msg_pub.set_msg_context
2356 ( p_entity_code => 'LINE'
2357 ,p_entity_id => l_line_rec.line_id(I)
2358 ,p_header_id => l_line_rec.header_id(I)
2359 ,p_line_id => l_line_rec.line_id(I)
2360 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref(I)
2361 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref(I)
2362 ,p_source_document_id => NULL
2363 ,p_source_document_line_id => NULL
2364 ,p_order_source_id => l_line_rec.order_source_id(I)
2365 ,p_source_document_type_id => NULL );
2366
2367 -- To check for mandatory attributes call Validate_config_attributes
2368 Validate_Config_Attributes(
2369 p_use_Configurator => p_use_Configurator,
2370 p_line_rec => l_line_rec,
2371 p_line_index => I
2372 );
2373
2374 -- Get inventory_item_id if NULL on l_line_rec.
2375 IF l_line_rec.inventory_item_id(I) IS NULL THEN
2376 OE_BULK_PROCESS_LINE.Get_Item_Info(
2377 p_index => I,
2378 p_line_rec => l_line_rec
2379 );
2380 END IF;
2381
2382 -- IF Model Line then Set top_model_line_id(I) = line_id(I);
2383 -- Load BOM into bom_explosions table if not there already.
2384 IF l_line_rec.item_type_code(I) = 'MODEL' AND
2385 l_line_rec.top_model_line_ref(I) = l_line_rec.orig_sys_line_ref(I) THEN
2386
2387 IF l_debug_level > 0 THEN
2388 oe_debug_pub.add( 'MODEL LINE ID = ' || l_line_rec.line_id(I) ) ;
2389 END IF;
2390
2391 l_line_rec.top_model_line_id(I) := l_line_rec.line_id(I);
2392
2393 OE_Config_UTIL.Explode
2394 (
2395 p_validation_org => OE_BULK_ORDER_PVT.G_ITEM_ORG,
2396 p_group_id => NULL,
2397 p_session_id => 0, -- ( null ?)
2398 p_levels => 60, -- (6 ?)
2399 p_stdcompflag => OE_Config_Util.OE_BMX_OPTION_COMPS,
2400 p_exp_quantity => l_line_rec.ordered_quantity(I),
2401 p_top_item_id => l_line_rec.inventory_item_id(I),
2402 p_revdate => sysdate,
2403 p_component_code => l_line_rec.component_code(I),
2404 x_msg_data => l_msg_data,
2405 x_error_code => l_error_code,
2406 x_return_status => l_return_status
2407 );
2408
2409
2410 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2411 IF l_debug_level > 0 THEN
2412 oe_debug_pub.add( 'ERROR in OE_Config_UTIL.Explode' ) ;
2413 END IF;
2414 l_line_rec.lock_control(I) := -99;
2415 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2416 IF l_debug_level > 0 THEN
2417 oe_debug_pub.add( 'UNEXPECTED ERROR in OE_Config_UTIL.Explode' ) ;
2418 END IF;
2419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2420 END IF;
2421
2422 -- Get the bill_sequence_id for model line.
2423 SELECT bill_sequence_id,
2424 component_code,
2425 component_sequence_id,
2426 sort_order,
2427 primary_uom_code,
2428 high_quantity,
2429 low_quantity,
2430 mutually_exclusive_options,
2431 bom_item_type,
2432 replenish_to_order_flag
2433 INTO l_line_rec.Top_Bill_Sequence_Id(I),
2434 l_line_rec.component_code(I),
2435 l_line_rec.component_sequence_id(I),
2436 l_line_rec.sort_order(I),
2437 l_line_rec.order_quantity_uom(I),
2438 l_config_rec.high_quantity(I),
2439 l_config_rec.low_quantity(I),
2440 l_config_rec.mutually_exclusive_options(I),
2441 l_config_rec.bom_item_type(I),
2442 l_config_rec.replenish_to_order_flag(I)
2443 FROM bom_explosions
2444 WHERE COMPONENT_ITEM_ID = l_line_rec.inventory_item_id(I)
2445 AND ORGANIZATION_ID = OE_BULK_ORDER_PVT.G_ITEM_ORG
2446 AND PLAN_LEVEL = 0
2447 AND nvl(effectivity_date, sysdate) <= sysdate
2448 AND nvl(disable_date, sysdate+1) > sysdate
2449 AND explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS;
2450
2451
2452 -- Set curr_model_index = cureent_index
2453 l_curr_model_index := I;
2454
2455 -- If Top Model is an ATO model (replenish_to_order_flag(I) = Y ) Then
2456 -- Set curr_ato_line_index = current_index;
2457 -- Set l_line_rec.ato_line_id(I) = line_id;
2458
2459 l_curr_ato_index := null;
2460 IF nvl(l_config_rec.replenish_to_order_flag(I), 'N') = 'Y' THEN
2461 l_curr_ato_index := I;
2462 l_line_rec.ato_line_id(I) := l_line_rec.line_id(I);
2463 END IF;
2464
2465
2466 ELSE -- child line
2467
2468 -- Use curr_model_index to
2469 -- Set l_line_rec.top_bill_sequence_id(I) on child lines.
2470 -- Set l_line_rec.top_model_line_id(I) on child lines.
2471 -- Use curr_ato_line_index to
2472 -- Set l_line_rec.ato_line_id(I) on child lines
2473
2474 IF l_debug_level > 0 THEN
2475 oe_debug_pub.add( 'CHILD LINE ID = ' || l_line_rec.line_id(I) ) ;
2476 END IF;
2477
2478
2479 l_line_rec.top_bill_sequence_id(I) :=
2480 l_line_rec.top_bill_sequence_id(l_curr_model_index);
2481 l_line_rec.top_model_line_id(I) :=
2482 l_line_rec.top_model_line_id(l_curr_model_index);
2483
2484 IF l_curr_ato_index IS NOT NULL THEN
2485 l_line_rec.ato_line_id(I) :=
2486 l_line_rec.line_id(l_curr_ato_index);
2487 END IF;
2488
2489 -- For Child Lines, use following cursor to derive the component_code.
2490 -- Also get other BOM attributes from BOM_EXPLOSIONS as
2491 BEGIN
2492
2493 SELECT component_code,
2494 component_sequence_id,
2495 sort_order,
2496 primary_uom_code,
2497 high_quantity,
2498 low_quantity,
2499 mutually_exclusive_options,
2500 bom_item_type,
2501 replenish_to_order_flag
2502 INTO l_line_rec.component_code(I),
2503 l_line_rec.component_sequence_id(I),
2504 l_line_rec.sort_order(I),
2505 l_line_rec.order_quantity_uom(I),
2506 l_config_rec.high_quantity(I),
2507 l_config_rec.low_quantity(I),
2508 l_config_rec.mutually_exclusive_options(I),
2509 l_config_rec.bom_item_type(I),
2510 l_config_rec.replenish_to_order_flag(I)
2511 FROM bom_explosions
2512 WHERE component_item_id = l_line_rec.inventory_item_id(I)
2513 AND explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
2514 AND top_bill_sequence_id = l_line_rec.top_bill_sequence_id(I)
2515 AND plan_level > 0
2516 AND nvl(effectivity_date, sysdate) <= sysdate
2517 AND nvl(disable_date, sysdate+1) > sysdate
2518 AND organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG
2519 AND component_code = NVL(l_line_rec.component_code(I), component_code);
2520
2521 EXCEPTION
2522 WHEN NO_DATA_FOUND THEN
2523 IF l_debug_level > 0 THEN
2524 oe_debug_pub.add('SELECT COMP_CODE FAILED , NO DATA FOUND ' , 1);
2525 oe_debug_pub.add('ITEM: '|| L_LINE_REC.INVENTORY_ITEM_ID(I) , 1);
2526 END IF;
2527 l_line_rec.lock_control(I) := -99;
2528 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_ITEM_NOT_IN_BILL');
2529 FND_MESSAGE.Set_Token('COMPONENT', l_line_rec.inventory_item_id(I));
2530 FND_MESSAGE.Set_Token('MODEL', l_line_rec.inventory_item_id(l_curr_model_index));
2531 oe_bulk_msg_pub.add;
2532
2533 WHEN TOO_MANY_ROWS THEN
2534 IF l_debug_level > 0 THEN
2535 oe_debug_pub.add('SELECT COMP_CODE FAILED , TOO_MANY ROWS ' , 1);
2536 oe_debug_pub.add('ITEM: '|| L_LINE_REC.INVENTORY_ITEM_ID(I) , 1);
2537 END IF;
2538 l_line_rec.lock_control(I) := -99;
2539 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_AMBIGUITY');
2540 FND_MESSAGE.Set_Token('COMPONENT', l_line_rec.inventory_item_id(I));
2541 FND_MESSAGE.Set_Token('MODEL', l_line_rec.inventory_item_id(l_curr_model_index));
2542 oe_bulk_msg_pub.add;
2543
2544 WHEN OTHERS THEN
2545 IF l_debug_level > 0 THEN
2546 oe_debug_pub.add('SELECT COMP_CODE FAILED , OTHERS ' , 1);
2547 oe_debug_pub.add('ITEM: '|| L_LINE_REC.INVENTORY_ITEM_ID(I) , 1);
2548 END IF;
2549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2550 END;
2551
2552 END IF; -- IF Model Line
2553
2554 -- Call Load_Item_Attributes() to load item_type_code and ato_line_id
2555 Load_Item_Attributes
2556 (
2557 p_line_rec => l_line_rec,
2558 p_index => I
2559 );
2560
2561
2562 -- IF Configurator is used and Current line is a last line in the model
2563 -- Then We will need to call Batch Validation API to create an instance in CZ.
2564
2565 IF nvl(p_use_configurator, 'N') = 'Y' AND
2566 (I < l_line_rec.line_id.count AND
2567 (l_line_rec.top_model_line_ref(I) <> l_line_rec.top_model_line_ref(I+1) OR
2568 l_line_rec.header_id(I) <> l_line_rec.header_id(I+1)) OR
2569 I = l_line_rec.line_id.count) THEN
2570
2571
2572 SELECT hsecs INTO l_start_time from v$timer;
2573
2574 Validate_Configuration(
2575 p_new_config => 'Y',
2576 p_line_rec => l_line_rec,
2577 p_line_index => l_curr_model_index,
2578 p_batch_validate_time => l_batch_validate_time
2579 );
2580
2581 SELECT hsecs INTO l_end_time from v$timer;
2582 l_config_validate_time := l_config_validate_time + (l_end_time-l_start_time)/100;
2583
2584 END IF;
2585
2586 <<SKIP_THE_LINE>>
2587
2588 IF p_validate_only = 'N' AND nvl(l_line_rec.lock_control(I), 0) = -99 THEN
2589 l_order_source_id := l_line_rec.order_source_id(I);
2590 l_orig_sys_document_ref := l_line_rec.orig_sys_document_ref(I);
2591
2592 IF l_debug_level > 0 THEN
2593 oe_debug_pub.add( 'SKIP order_source_id: '||l_order_source_id, 3 ) ;
2594 oe_debug_pub.add( 'SKIP orig_sys_document_ref: '||l_orig_sys_document_ref, 3 ) ;
2595 END IF;
2596
2597 END IF;
2598
2599 END LOOP;
2600
2601 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in config_validate is (sec) '
2602 || l_config_validate_time );
2603
2604 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in batch_validate is (sec) '
2605 || l_batch_validate_time );
2606
2607 IF l_debug_level > 0 THEN
2608 Print_Line_Rec( p_line_rec => l_line_rec,
2609 p_config_rec => l_config_rec);
2610 END IF;
2611
2612 -----------------------------------------------------------------
2613 -- Do BULK INSERT of all loaded lines into oe_config_details_tmp.
2614 -----------------------------------------------------------------
2615
2616 IF nvl(p_use_configurator, 'N') = 'N' THEN
2617
2618 FORALL I in 1..l_line_rec.line_id.COUNT
2619 INSERT INTO oe_config_details_tmp
2620 (
2621 LINE_ID ,
2622 TOP_MODEL_LINE_ID,
2623 ATO_LINE_ID,
2624 LINK_TO_LINE_ID,
2625 ORDER_SOURCE_ID,
2626 ORIG_SYS_DOCUMENT_REF,
2627 ORIG_SYS_LINE_REF ,
2628 ORIG_SYS_SHIPMENT_REF ,
2629 TOP_MODEL_LINE_REF ,
2630 INVENTORY_ITEM_ID,
2631 ORDERED_ITEM,
2632 UOM_CODE,
2633 ORDERED_QUANTITY,
2634 COMPONENT_CODE,
2635 COMPONENT_SEQUENCE_ID,
2636 SORT_ORDER ,
2637 CONFIG_HEADER_ID,
2638 CONFIG_REV_NBR ,
2639 CONFIGURATION_ID,
2640 TOP_BILL_SEQUENCE_ID,
2641 ITEM_TYPE_CODE,
2642 --LINE_TYPE,
2643 --CZ_QTY_MATCH_FLAG,
2644 HIGH_QUANTITY,
2645 LOW_QUANTITY,
2646 MUTUALLY_EXCLUSIVE_OPTIONS,
2647 BOM_ITEM_TYPE,
2648 LOCK_CONTROL,
2649 REPLENISH_TO_ORDER_FLAG
2650 )
2651 VALUES
2652 (
2653 l_line_rec.line_id(I),
2654 l_line_rec.top_model_line_id(I),
2655 l_line_rec.ato_line_id(I),
2656 l_line_rec.link_to_line_id(I),
2657 l_line_rec.order_source_id(I),
2658 l_line_rec.orig_sys_document_ref(I),
2659 l_line_rec.orig_sys_line_ref(I),
2660 l_line_rec.orig_sys_shipment_ref(I),
2661 l_line_rec.Top_model_line_REF(I),
2662 l_line_rec.inventory_item_id(I),
2663 l_line_rec.ordered_item(I),
2664 l_line_rec.order_quantity_uom(I),
2665 l_line_rec.ordered_quantity(I),
2666 l_line_rec.Component_code(I),
2667 l_line_rec.Component_sequence_id(I),
2668 l_line_rec.sort_order(I),
2669 l_line_rec.config_header_id(I),
2670 l_line_rec.config_rev_nbr(I),
2671 l_line_rec.configuration_id(I),
2672 l_line_rec.top_bill_sequence_id(I),
2673 l_line_rec.item_type_code(I),
2674 l_config_rec.high_quantity(I),
2675 l_config_rec.low_quantity(I),
2676 l_config_rec.mutually_exclusive_options(I),
2677 l_config_rec.bom_item_type(I),
2678 l_line_rec.lock_control(I),
2679 l_config_rec.replenish_to_order_flag(I)
2680 );
2681
2682 IF l_debug_level > 0 THEN
2683 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM LINE_REC' , 3 ) ;
2684 END IF;
2685
2686
2687 -- Insert Missing Classes
2688 INSERT INTO oe_config_details_tmp
2689 (
2690 LINE_ID ,
2691 TOP_MODEL_LINE_ID,
2692 ATO_LINE_ID,
2693 LINK_TO_LINE_ID,
2694 ORDER_SOURCE_ID,
2695 ORIG_SYS_DOCUMENT_REF,
2696 ORIG_SYS_LINE_REF ,
2697 --ORIG_SYS_SHIPMENT_REF ,
2698 TOP_MODEL_LINE_REF ,
2699 INVENTORY_ITEM_ID,
2700 ORDERED_ITEM,
2701 UOM_CODE,
2702 ORDERED_QUANTITY,
2703 COMPONENT_CODE,
2704 COMPONENT_SEQUENCE_ID,
2705 SORT_ORDER ,
2706 --CONFIG_HEADER_ID,
2707 --CONFIG_REV_NBR ,
2708 --CONFIGURATION_ID,
2709 TOP_BILL_SEQUENCE_ID,
2710 ITEM_TYPE_CODE,
2711 --LINE_TYPE,
2712 --CZ_QTY_MATCH_FLAG,
2713 HIGH_QUANTITY,
2714 LOW_QUANTITY,
2715 MUTUALLY_EXCLUSIVE_OPTIONS,
2716 BOM_ITEM_TYPE,
2717 LOCK_CONTROL,
2718 REPLENISH_TO_ORDER_FLAG
2719 )
2720 SELECT
2721 oe_order_lines_s.nextval Line_id,
2722 L.top_model_line_id top_model_line_id,
2723 L.ato_line_id ato_line_id,
2724 NULL link_to_line_id,
2725 l.order_source_id,
2726 l.orig_sys_document_ref orig_sys_document_ref,
2727 'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
2728 l.orig_sys_line_ref top_model_line_ref,
2729 b.component_item_id,
2730 NULL ordered_item,
2731 b.primary_uom_code,
2732 b.EXTENDED_QUANTITY * l.ordered_quantity,
2733 b.Component_code,
2734 b.Component_sequence_id,
2735 b.Sort_order,
2736 l.top_bill_sequence_id,
2737 'CLASS' item_type_code,
2738 b.high_quantity,
2739 b.low_quantity,
2740 b.mutually_exclusive_options,
2741 b.bom_item_type,
2742 null LOCK_CONTROL,
2743 b.replenish_to_order_flag
2744 FROM BOM_EXPLOSIONS b,
2745 oe_config_details_tmp L
2746 WHERE b.top_bill_sequence_id = L.top_bill_sequence_id
2747 AND L.item_type_code = 'MODEL'
2748 AND L.line_id = L.top_model_line_id
2749 AND nvl(L.lock_control, 0) <> -99
2750 AND b.explosion_type = OE_Config_Util.OE_BMX_OPTION_COMPS
2751 AND b.plan_level > 0
2752 AND nvl(b.effectivity_date, sysdate) <= sysdate
2753 AND nvl(b.disable_date, sysdate + 1) > sysdate
2754 AND b.component_sequence_id <> b.top_bill_sequence_id -- Exclude Model Lines
2755 AND b.component_code NOT IN (
2756 SELECT l2.component_code
2757 FROM oe_config_details_tmp l2
2758 WHERE l2.top_model_line_id = L.top_model_line_id )
2759 AND EXISTS (
2760 SELECT l3.line_id
2761 FROM oe_config_details_tmp l3
2762 WHERE
2763 instr(l3.component_code, b.component_code, 1) = 1
2764 AND l3.top_model_line_id = L.top_model_line_id
2765 AND l3.item_type_code <> 'MODEL'
2766 AND l3.bom_item_type = 4);
2767
2768 IF l_debug_level > 0 THEN
2769 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' MISSING CLASSES TO TMP' , 3 ) ;
2770 END IF;
2771
2772 ELSE -- Else If configurator is used Then
2773
2774 -- We will need to first insert all records from l_line_rec and pull
2775 -- missing attributes (quantity, line_type, configuration_id ) from
2776 -- cz_config_details_v for the new instance that just got created in
2777 -- Batch Validation call.
2778
2779 FORALL I in 1..l_line_rec.line_id.COUNT
2780 INSERT INTO oe_config_details_tmp
2781 (
2782 LINE_ID ,
2783 TOP_MODEL_LINE_ID,
2784 ATO_LINE_ID,
2785 LINK_TO_LINE_ID,
2786 ORDER_SOURCE_ID,
2787 ORIG_SYS_DOCUMENT_REF,
2788 ORIG_SYS_LINE_REF ,
2789 ORIG_SYS_SHIPMENT_REF ,
2790 TOP_MODEL_LINE_REF ,
2791 INVENTORY_ITEM_ID,
2792 UOM_CODE,
2793 ORDERED_QUANTITY,
2794 COMPONENT_CODE,
2795 COMPONENT_SEQUENCE_ID,
2796 SORT_ORDER ,
2797 CONFIG_HEADER_ID,
2798 CONFIG_REV_NBR ,
2799 CONFIGURATION_ID,
2800 --TOP_BILL_SEQUENCE_ID,
2801 ITEM_TYPE_CODE,
2802 LINE_TYPE,
2803 --CZ_QTY_MATCH_FLAG,
2804 --HIGH_QUANTITY,
2805 --LOW_QUANTITY,
2806 --MUTUALLY_EXCLUSIVE_OPTIONS,
2807 --BOM_ITEM_TYPE,
2808 LOCK_CONTROL
2809 --REPLENISH_TO_ORDER_FLAG
2810 )
2811 SELECT
2812 l_line_rec.line_id(I),
2813 l_line_rec.top_model_line_id(I),
2814 NVL(l_line_rec.ato_line_id(I),
2815 decode(z.config_item_id, z.ato_config_item_id,
2816 l_line_rec.line_id(I),NULL)) ato_line_id,
2817 NULL link_to_line_id,
2818 l_line_rec.order_source_id(I),
2819 l_line_rec.orig_sys_document_ref(I),
2820 l_line_rec.orig_sys_line_ref(I),
2821 l_line_rec.orig_sys_shipment_ref(I),
2822 l_line_rec.Top_model_line_REF(I),
2823 z.inventory_item_id,
2824 z.uom_code,
2825 z.quantity,
2826 l_line_rec.Component_code(I),
2827 l_line_rec.Component_sequence_id(I),
2828 z.bom_sort_order, -- l_line_rec.sort_order(I),
2829 z.config_hdr_id,
2830 z.config_rev_nbr,
2831 z.config_item_id,
2832 l_line_rec.item_type_code(I),
2833 z.line_type,
2834 --high_quantity(I)
2835 --low_quantity(I)
2836 --mutually_exclusive_options(I)
2837 --bom_item_type(I)
2838 l_line_rec.lock_control(I)
2839 FROM CZ_CONFIG_DETAILS_V z
2840 WHERE z.config_hdr_id = l_line_rec.config_header_id(I)
2841 AND z.config_rev_nbr = l_line_rec.config_rev_nbr(I)
2842 AND z.component_code = l_line_rec.component_code(I);
2843
2844 IF l_debug_level > 0 THEN
2845 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM LINE_REC' , 3 ) ;
2846 END IF;
2847
2848
2849 -- Insert any new components that are present in CZ but
2850 -- missing in oe_config_details_tmp.
2851 INSERT INTO oe_config_details_tmp
2852 (
2853 LINE_ID ,
2854 TOP_MODEL_LINE_ID,
2855 ATO_LINE_ID,
2856 LINK_TO_LINE_ID,
2857 ORDER_SOURCE_ID,
2858 ORIG_SYS_DOCUMENT_REF,
2859 ORIG_SYS_LINE_REF ,
2860 -- ORIG_SYS_SHIPMENT_REF ,
2861 TOP_MODEL_LINE_REF ,
2862 INVENTORY_ITEM_ID,
2863 UOM_CODE,
2864 ORDERED_QUANTITY,
2865 COMPONENT_CODE,
2866 COMPONENT_SEQUENCE_ID,
2867 SORT_ORDER ,
2868 CONFIG_HEADER_ID,
2869 CONFIG_REV_NBR ,
2870 CONFIGURATION_ID,
2871 TOP_BILL_SEQUENCE_ID,
2872 ITEM_TYPE_CODE,
2873 --LINE_TYPE,
2874 --CZ_QTY_MATCH_FLAG,
2875 --HIGH_QUANTITY,
2876 --LOW_QUANTITY,
2877 --MUTUALLY_EXCLUSIVE_OPTIONS,
2878 BOM_ITEM_TYPE,
2879 LOCK_CONTROL
2880 --REPLENISH_TO_ORDER_FLAG
2881 )
2882 SELECT
2883 oe_order_lines_s.nextval Line_id,
2884 L.top_model_line_id top_model_line_id,
2885 decode(z.config_item_id, z.ato_config_item_id,
2886 oe_order_lines_s.currval, NULL) ato_line_id ,
2887 NULL link_to_line_id,
2888 L.order_source_id order_source_id,
2889 L.orig_sys_document_ref orig_sys_document_ref,
2890 'OE_ORDER_LINES_ALL'||oe_order_lines_s.currval orig_sys_line_ref,
2891 L.orig_sys_line_ref top_model_line_ref,
2892 z.inventory_item_id,
2893 z.uom_code,
2894 z.quantity,
2895 z.Component_code,
2896 z.Component_sequence_id,
2897 z.bom_sort_order,
2898 z.config_hdr_id,
2899 z.config_rev_nbr,
2900 z.config_item_id,
2901 L.top_bill_sequence_id,
2902 'CLASS', -- In new configuration, only CLASS can be missing
2903 z.bom_item_type,
2904 null LOCK_CONTROL
2905 FROM CZ_CONFIG_DETAILS_V z,
2906 oe_config_details_tmp L
2907 WHERE L.item_type_code = 'MODEL'
2908 AND L.line_id = L.top_model_line_id
2909 AND nvl(L.lock_control, 0) <> -99
2910 AND L.config_header_id = z.config_hdr_id
2911 AND L.config_rev_nbr = z.config_rev_nbr
2912 AND z.config_item_id NOT IN
2913 (Select configuration_id
2914 FROM oe_config_details_tmp L2
2915 WHERE L2.top_model_line_id = L.line_id);
2916
2917 IF l_debug_level > 0 THEN
2918 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' TO TMP FROM CZ' , 3 ) ;
2919 END IF;
2920
2921 END IF; -- Use Configutor
2922
2923
2924 IF l_debug_level > 0 THEN
2925 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Pre_Process_Bom ', 1) ;
2926 END IF;
2927
2928 EXCEPTION
2929
2930 WHEN NO_DATA_FOUND THEN
2931 IF l_debug_level > 0 THEN
2932 oe_debug_pub.add('NO DATA FOUND in OE_BULK_CONFIG_UTIL.Pre_Process_Bom ' , 1);
2933 END IF;
2934
2935 WHEN OTHERS THEN
2936
2937 IF c_line_bom1%ISOPEN THEN
2938 CLOSE c_line_bom1;
2939 ELSIF c_line_bom2%ISOPEN THEN
2940 CLOSE c_line_bom2;
2941 END IF;
2942
2943 IF l_debug_level > 0 THEN
2944 oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Pre_Process_Bom');
2945 oe_debug_pub.add(substr(sqlerrm,1,240));
2946 END IF;
2947
2948 OE_BULK_MSG_PUB.Add_Exc_Msg
2949 ( G_PKG_NAME,
2950 'Pre_Process_Bom'
2951 );
2952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953
2954 END Pre_Process_Bom;
2955
2956
2957
2958 ---------------------------------------------------------------------
2959 --
2960 -- PROCEDURE Pre_Process
2961 --
2962 ---------------------------------------------------------------------
2963
2964 PROCEDURE Pre_Process(
2965 p_batch_id IN NUMBER
2966 ,p_validate_only IN VARCHAR2
2967 ,p_use_configurator IN VARCHAR2
2968 ,p_validate_configurations IN VARCHAR2
2969 )
2970 IS
2971
2972 l_start_time NUMBER;
2973 l_end_time NUMBER;
2974
2975 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2976
2977 BEGIN
2978 IF l_debug_level > 0 THEN
2979 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Pre_Process ') ;
2980 oe_debug_pub.add( ' Use Configurator :'|| p_use_configurator );
2981 END IF;
2982
2983 IF nvl(p_use_configurator, 'N') = 'Y' THEN
2984
2985 Pre_Process_Configurator(
2986 p_batch_id => p_batch_id,
2987 p_validate_only => p_validate_only,
2988 p_use_configurator => p_use_configurator,
2989 p_validate_configurations => p_validate_configurations );
2990
2991 -- Update ato_line_id on the temp table so that we do not need to loop over the
2992 -- lines later in process_lines. This UPDATE assumes that link_to_line_id and
2993 -- ato_line_id are already populated on ATO Models or ATOs under PTO cases.
2994 -- This happens in preprocessing. It will not set ATO_LINE_ID on ATO items under
2995 -- PTO models. That will be taken care of in Process_Lines.
2996 IF l_debug_level > 0 THEN
2997 oe_debug_pub.add( 'BEFORE update TMP for Config ') ;
2998 END IF;
2999
3000 update oe_config_details_tmp L
3001 set ato_line_id =
3002 ( select ato_line_id
3003 from oe_config_details_tmp L1
3004 where L1.ato_line_id IS NOT NULL
3005 AND L1.top_model_line_id = L.top_model_line_id
3006 AND INSTR(L1.component_code,'-',1,2) = 0
3007 AND L1.component_code = decode( L1.item_type_code, 'MODEL',
3008 substr(L.component_code,1, instr(L.component_code,'-',1,1)-1),
3009 substr(L.component_code,1, instr(L.component_code,'-',1,2)-1))
3010 AND ROWNUM = 1 )
3011 Where line_id <> top_model_line_id
3012 And ato_line_id is NULL;
3013
3014 IF l_debug_level > 0 THEN
3015 oe_debug_pub.add( 'AFTER update TMP for Config ') ;
3016 END IF;
3017
3018 ELSE
3019
3020 Pre_Process_Bom(
3021 p_batch_id => p_batch_id,
3022 p_validate_only => p_validate_only,
3023 p_use_configurator => p_use_configurator,
3024 p_validate_configurations => p_validate_configurations );
3025
3026
3027
3028 -- Update ato_line_id on the temp table so that we do not need to loop over
3029 -- the lines later in process_lines. In this case ATO_LINE_ID will be preset
3030 -- on all child lines of an ATO_MODEL. We need to take care of the case of
3031 -- ATO under PTO. We can assume that bom_item_type and replenish_to_order_flag
3032 -- are available for all of these lines
3033 IF l_debug_level > 0 THEN
3034 oe_debug_pub.add( 'BEFORE update TMP for Bom ') ;
3035 END IF;
3036
3037 UPDATE oe_config_details_tmp L
3038 SET ato_line_id=
3039 ( SELECT L1.line_id
3040 FROM oe_config_details_tmp L1
3041 WHERE L.top_model_line_id = L1.top_model_line_id
3042 AND L1.top_model_line_id <> L1.line_id
3043 AND L1.bom_item_type = 1
3044 AND L1.replenish_to_order_flag = 'Y'
3045 AND L1.component_code = SUBSTR( L.component_code, 1, LENGTH( L1.component_code ))
3046 AND L1.component_code =
3047 ( SELECT MIN( L2.component_code )
3048 FROM oe_config_details_tmp L2
3049 WHERE L2.top_model_line_id = L.top_model_line_id
3050 AND L2.component_code = SUBSTR( L.component_code, 1,
3051 LENGTH( L2.component_code ))
3052 AND L2.bom_item_type = 1
3053 AND L2.replenish_to_order_flag = 'Y')
3054 AND ((SUBSTR(L.component_code, LENGTH(L1.component_code) + 1, 1) = '-' OR
3055 SUBSTR(L.component_code, LENGTH(L1.component_code) + 1, 1) is NULL)))
3056 WHERE L.top_model_line_id <> line_id
3057 AND NOT (item_type_code = 'OPTION' AND
3058 ato_line_id = line_id AND
3059 ato_line_id is not null)
3060 AND L.ato_line_id IS NULL;
3061
3062 IF l_debug_level > 0 THEN
3063 oe_debug_pub.add( 'AFTER update TMP for Bom ') ;
3064 END IF;
3065
3066 END IF;
3067
3068 -- Update link_to_line_id on all lines except top model.
3069
3070 IF l_debug_level > 0 THEN
3071 oe_debug_pub.add( 'BEFORE update link_to_line_id ') ;
3072 END IF;
3073
3074 update oe_config_details_tmp L
3075 SET link_to_line_id = (select line_id
3076 from oe_config_details_tmp L2
3077 where L2.component_code = substr(L.component_code,
3078 1,instr(L.component_code,'-',-1,1)-1)
3079 and L2.top_model_line_id = L.top_model_line_id)
3080 where line_id <> top_model_line_id
3081 and link_to_line_id is NULL;
3082
3083 IF l_debug_level > 0 THEN
3084 oe_debug_pub.add( 'AFTER update link_to_line_id ') ;
3085 END IF;
3086
3087 -- Need to do BOM Validations upfront on TEMP table
3088
3089 If nvl(p_use_configurator, 'N') = 'N' AND
3090 nvl(p_validate_configurations, 'N') = 'Y' THEN
3091
3092 IF l_debug_level > 0 THEN
3093 oe_debug_pub.add( 'Calling OE_BULK_VALIDATE.Validate_BOM ') ;
3094 END IF;
3095
3096 SELECT hsecs INTO l_start_time from v$timer;
3097
3098 OE_BULK_VALIDATE.Validate_BOM;
3099
3100 SELECT hsecs INTO l_end_time from v$timer;
3101 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Validate_BOM is (sec) '
3102 ||((l_end_time-l_start_time)/100));
3103
3104 END IF;
3105
3106 IF l_debug_level > 0 THEN
3107 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Pre_Process ') ;
3108 END IF;
3109
3110
3111
3112 EXCEPTION
3113 WHEN NO_DATA_FOUND THEN
3114 IF l_debug_level > 0 THEN
3115 oe_debug_pub.add('NO DATA FOUND in OE_BULK_CONFIG_UTIL.Pre_Process ' , 1);
3116 END IF;
3117
3118 WHEN OTHERS THEN
3119 IF l_debug_level > 0 THEN
3120 oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Pre_Process');
3121 oe_debug_pub.add(substr(sqlerrm,1,240));
3122 END IF;
3123
3124 OE_BULK_MSG_PUB.Add_Exc_Msg
3125 ( G_PKG_NAME,
3126 'Pre_Process'
3127 );
3128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3129
3130 END Pre_Process;
3131
3132
3133
3134 PROCEDURE Delete_Configurations
3135 ( p_error_rec IN OE_BULK_ORDER_PVT.INVALID_HDR_REC_TYPE
3136 ,x_return_status OUT NOCOPY VARCHAR2
3137 )
3138 IS
3139
3140 CURSOR c_configs( p_orig_sys_document_ref VARCHAR2, p_order_source_id NUMBER)
3141 IS
3142 select config_header_id,
3143 config_rev_nbr,
3144 orig_sys_document_ref,
3145 order_source_id
3146 from OE_CONFIG_DETAILS_TMP
3147 where orig_sys_document_ref = p_orig_sys_document_ref
3148 and order_source_id = p_order_source_id
3149 and item_type_code = 'MODEL';
3150
3151 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3152
3153 l_usage_exists number;
3154 l_return_value number := 1;
3155 l_error_message varchar2(100);
3156
3157
3158 BEGIN
3159 IF l_debug_level > 0 THEN
3160 oe_debug_pub.add( 'ENTERING OE_BULK_CONFIG_UTIL.Delete_Configurations ') ;
3161 END IF;
3162
3163 -- initialize return status to success
3164 x_return_status := fnd_api.g_ret_sts_success;
3165
3166 FOR I IN 1..P_ERROR_REC.header_id.COUNT LOOP
3167
3168 FOR l_delete_rec IN c_configs(P_ERROR_REC.orig_sys_document_ref(I),
3169 P_ERROR_REC.order_source_id(I)) LOOP
3170
3171 IF l_debug_level > 0 THEN
3172 oe_debug_pub.add( 'DOC_REF: ' || l_delete_rec.orig_sys_document_ref ) ;
3173 oe_debug_pub.add( 'SOURCE: ' || l_delete_rec.order_source_id ) ;
3174 oe_debug_pub.add( 'CHI: ' || l_delete_rec.config_header_id ) ;
3175 oe_debug_pub.add( 'CRN: ' || l_delete_rec.config_rev_nbr ) ;
3176 END IF;
3177
3178 /*
3179 OE_Config_Pvt.Delete_Config
3180 ( p_config_hdr_id => l_delete_rec.config_header_id
3181 ,p_config_rev_nbr => l_delete_rec.config_rev_nbr
3182 ,x_return_status => x_return_status);
3183
3184 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3186 ELSE
3187 RAISE FND_API.G_EXC_ERROR;
3188 END IF;
3189
3190 IF l_debug_level > 0 THEN
3191 oe_debug_pub.add( 'CONFIG DELETED WITH SUCCESS' ) ;
3192 END IF;
3193 */
3194
3195 IF l_delete_rec.config_header_id is not null AND
3196 l_delete_rec.config_rev_nbr is not null THEN
3197
3198 CZ_CF_API.Delete_Configuration
3199 ( config_hdr_id => l_delete_rec.config_header_id
3200 ,config_rev_nbr => l_delete_rec.config_rev_nbr
3201 ,usage_exists => l_usage_exists
3202 ,error_message => l_error_message
3203 ,return_value => l_return_value );
3204
3205 IF l_return_value <> 1 THEN
3206 OE_BULK_Msg_Pub.Add_text(l_error_message);
3207 IF l_debug_level > 0 THEN
3208 oe_debug_pub.add('Error from CZ delete: ' ||l_error_message ) ;
3209 END IF;
3210 x_return_status := FND_API.G_RET_STS_ERROR;
3211 ELSE
3212 x_return_status := FND_API.G_RET_STS_SUCCESS;
3213 IF l_debug_level > 0 THEN
3214 oe_debug_pub.add( 'CONFIG DELETED WITH SUCCESS' ) ;
3215 END IF;
3216
3217 END IF;
3218 ELSE
3219 IF l_debug_level > 0 THEN
3220 oe_debug_pub.add('NOTE : NULL CONFIG_HEADER_ID/CONFIG_REV_NBR PASSED');
3221 END IF;
3222 END IF;
3223
3224 END LOOP;
3225 END LOOP;
3226
3227 IF l_debug_level > 0 THEN
3228 oe_debug_pub.add( 'EXITING OE_BULK_CONFIG_UTIL.Delete_Configurations ') ;
3229 END IF;
3230
3231
3232 EXCEPTION
3233
3234 WHEN OTHERS THEN
3235 IF l_debug_level > 0 THEN
3236 oe_debug_pub.add('Others Error, OE_BULK_CONFIG_UTIL.Delete_Configurations');
3237 oe_debug_pub.add(substr(sqlerrm,1,240));
3238 END IF;
3239
3240 OE_BULK_MSG_PUB.Add_Exc_Msg
3241 ( G_PKG_NAME,
3242 'Delete_Configurations'
3243 );
3244
3245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3246
3247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3248
3249 END Delete_Configurations;
3250
3251
3252
3253 END OE_BULK_CONFIG_UTIL;