1 PACKAGE BODY OE_CONFIG_TSO_PVT AS
2 /* $Header: OEXVTSOB.pls 120.17.12020000.2 2012/07/03 10:33:08 amallik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CONFIG_TSO_PVT';
5
6
7 ------------------------------------------------------------------
8 -- Local Procedures and Function Declarations
9 ------------------------------------------------------------------
10
11 Procedure Create_header
12 (p_sold_to_org_id IN NUMBER
13 ,x_header_id OUT NOCOPY NUMBER
14 ,x_return_status OUT NOCOPY VARCHAR2
15 ,x_msg_count OUT NOCOPY NUMBER
16 ,x_msg_data OUT NOCOPY VARCHAR2);
17
18 PROCEDURE Create_TSO_Order_Lines
19 (p_header_id IN NUMBER
20 ,p_top_model_line_id IN NUMBER
21 ,p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,x_msg_count OUT NOCOPY NUMBER
24 ,x_return_status OUT NOCOPY VARCHAR2);
25
26 PROCEDURE Populate_MACD_action(
27 p_header_id IN NUMBER
28 ,p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
29 ,p_x_Line_Tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
30 ,p_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl
31 ,p_macd_action IN VARCHAR2
32 -- ,x_config_item_tbl OUT NOCOPY CZ_API_PUB.config_tbl_type;
33 -- ,x_config_ext_attr_tbl OUT NOCOPY config_ext_attr_tbl_type
34 ,x_msg_data OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_return_status OUT NOCOPY VARCHAR2);
37
38
39 Procedure IS_Container_Present
40 (p_header_id IN NUMBER
41 ,p_config_instance_hdr_id IN NUMBER
42 ,p_config_instance_rev_number IN NUMBER
43 ,x_top_model_line_id OUT NOCOPY NUMBER);
44
45 Procedure Validate_action
46 (p_top_model_line_id IN NUMBER
47 ,p_instance_item_id IN NUMBER
48 ,p_macd_action IN VARCHAR2
49 ,x_config_item_id OUT NOCOPY NUMBER
50 ,x_component_code OUT NOCOPY VARCHAR2
51 ,x_return_status OUT NOCOPY VARCHAR2);
52
53 Procedure Validate_line_action
54 (p_line_id IN NUMBER
55 ,P_config_header_id IN NUMBER
56 ,P_config_rev_nbr IN NUMBER
57 ,P_config_item_id IN NUMBER
58 ,P_macd_action IN NUMBER
59 ,x_return_status OUT NOCOPY VARCHAR2);
60
61 Procedure Populate_new_to_old
62 (p_Instance_Tbl IN csi_datastructures_pub.instance_cz_tbl
63 ,x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_cz_tbl)
64 IS
65
66 BEGIN
67
68 FOR I IN 1..p_Instance_Tbl.count LOOP
69
70 x_instance_tbl(I).ITEM_INSTANCE_ID := p_Instance_Tbl(I).ITEM_INSTANCE_ID;
71 x_instance_tbl(I).CONFIG_INSTANCE_HDR_ID := p_Instance_Tbl(I).CONFIG_INSTANCE_HDR_ID;
72 x_instance_tbl(I).CONFIG_INSTANCE_REV_NUMBER := p_Instance_Tbl(I).CONFIG_INSTANCE_REV_NUMBER;
73 x_instance_tbl(I).CONFIG_INSTANCE_ITEM_ID := p_Instance_Tbl(I).CONFIG_INSTANCE_ITEM_ID;
74 x_instance_tbl(I).BILL_TO_SITE_USE_ID := p_Instance_Tbl(I).BILL_TO_SITE_USE_ID;
75 x_instance_tbl(I).SHIP_TO_SITE_USE_ID := p_Instance_Tbl(I).SHIP_TO_SITE_USE_ID;
76 x_instance_tbl(I).INSTANCE_NAME := p_Instance_Tbl(I).INSTANCE_NAME;
77
78 END LOOP;
79
80 END Populate_new_to_old;
81 PROCEDURE Print_Time(p_msg IN VARCHAR2);
82
83
84 -----------------------------------------------------------------
85 -- Name : Print_Time
86 -- Parameters : IN p_msg
87 --
88 -- Description : This Procedure will print Current time along
89 -- with the Debug Message Passed as input.
90 -- This Procedure will be called from Main
91 -- Procedures to print Entering and Leaving Msg
92 -----------------------------------------------------------------
93
94 PROCEDURE Print_Time(p_msg IN VARCHAR2)
95 IS
96 l_time VARCHAR2(100);
97 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
98 BEGIN
99 l_time := to_char (new_time (sysdate, 'PST', 'EST'),
100 'DD-MON-YY HH24:MI:SS');
101 IF l_debug_level > 0 THEN
102 OE_DEBUG_PUB.Add(p_msg || ': '|| l_time, 1);
103 END IF;
104 END Print_Time;
105
106
107 /*-----------------------------------------------------------------
108 -- Name : Is_Part_of_Container_Model
109 -- Parameters : IN p_line_id
110 -- IN p_top_model_line_id
111 -- IN p_ato_line_id
112 -- IN p_inventory_item_id
113 -- OUT x_top_container_model
114 -- OUT x_part_of_container
115
116 -- Description : This API determines if an order line is
117 -- part of a container model configuration.
118 -- Can also be used to determine if input order
119 -- line itself is the top-level container model
120 --
121 How to use it:
122
123 For a top model line
124 you can pass inv item id -- if the operation is CREATE i.e.
125 the line is not in database yet, you should only use inv item id
126 OR
127 you can pass the line_id, top_model_line_id etc. if available, this is
128 better for performance.
129
130 For a child line
131 if operation is CREATE i.e.
132 the line is not in database yet, you should either pass inv item id
133 of the parent line or pass operation of CREATE and pass top_model_line_id.
134 you can also pass the parent line's inv item id directly if you have it
135 in case of operation of CREATE
136 OR
137 you can pass the line_id, top_model_line_id etc. if available, this is
138 better for performance.
139
140 --
141 -- Change Record :
142 Added p_operation as a parameter and consolidated some code in to
143 support it.
144 ------------------------------------------------------------------*/
145
146 PROCEDURE Is_Part_of_Container_Model
147 ( p_line_id IN NUMBER DEFAULT NULL
148 , p_top_model_line_id IN NUMBER DEFAULT NULL
149 , p_ato_line_id IN NUMBER DEFAULT NULL
150 , p_inventory_item_id IN NUMBER DEFAULT NULL
151 , p_operation IN VARCHAR2 DEFAULT NULL
152 , p_org_id IN NUMBER DEFAULT NULL --Bug 5524710
153 , x_top_container_model OUT NOCOPY VARCHAR2
154 , x_part_of_container OUT NOCOPY VARCHAR2
155 )
156 IS
157 l_ato_line_id NUMBER;
158 l_bom_item_type NUMBER;
159 l_config_model_type VARCHAR2(30);
160 l_replenish_to_order_flag VARCHAR2(1);
161 l_top_model_line_id NUMBER;
162 l_inventory_item_id NUMBER;
163 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
164 BEGIN
165
166 Print_Time('Entering OE_CONFIG_TSO_PVT.Is_Part_of_Container_Model..');
167
168 l_top_model_line_id := p_top_model_line_id;
169 l_ato_line_id := p_ato_line_id;
170 l_inventory_item_id := p_inventory_item_id;
171 x_part_of_container := 'N';
172 x_top_container_model := 'N';
173
174 IF l_debug_level > 0 THEN
175 OE_DEBUG_PUB.Add('Line Id : '|| p_line_id ,3);
176 OE_DEBUG_PUB.Add('Top Model Line id:'|| l_top_model_line_id,3);
177 OE_DEBUG_PUB.Add('ATO Line id: '|| l_ato_line_id,3);
178 OE_DEBUG_PUB.Add('operation : '|| p_operation,3);
179 OE_DEBUG_PUB.Add('inv item : '|| p_inventory_item_id,3);
180 END IF;
181
182 --operation is sent in only by OEXLLINB right now
183 IF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
184
185 IF l_debug_level > 0 THEN
186 OE_DEBUG_PUB.Add('Operation is CREATE',3);
187 OE_DEBUG_PUB.Add('Inv Item ID:'||p_inventory_item_id,3);
188 END IF;
189
190 IF p_top_model_line_id = p_line_id THEN
191 IF p_inventory_item_id is NULL THEN
192 RAISE FND_API.G_EXC_ERROR;
193 END IF;
194 ELSE
195
196 SELECT inventory_item_id
197 INTO l_inventory_item_id
198 FROM oe_order_lines
199 WHERE line_id = p_top_model_line_id;
200
201 IF l_debug_level > 0 THEN
202 OE_DEBUG_PUB.Add('Get Parent Item Id for:'||p_inventory_item_id,3);
203 OE_DEBUG_PUB.Add('Parent item id is:'||l_inventory_item_id,3);
204 END IF;
205
206 END IF;
207 ELSIF p_operation = 'UPDATE' OR
208 p_operation = 'DELETE' THEN
209
210 IF l_debug_level > 0 THEN
211 OE_DEBUG_PUB.Add('Operation is UPDATE/DELETE',3);
212 OE_DEBUG_PUB.Add('l_inventory_item_id set to NULL',3);
213 END IF;
214
215 l_inventory_item_id := null;
216
217 END IF;
218
219
220 IF l_inventory_item_id IS NOT NULL THEN
221
222 IF l_debug_level > 0 THEN
223 OE_DEBUG_PUB.Add('Inventory Item id:'||l_inventory_item_id,3);
224 END IF;
225
226 SELECT mtl_msi.bom_item_type
227 ,mtl_msi.replenish_to_order_flag
228 ,mtl_msi.config_model_type
229 INTO l_bom_item_type
230 ,l_replenish_to_order_flag
231 ,l_config_model_type
232 FROM mtl_system_items mtl_msi
233 WHERE mtl_msi.inventory_item_id = l_inventory_item_id
234 AND mtl_msi.organization_id=OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID',p_org_id); --Bug 5524710
235
236 IF l_debug_level > 0 THEN
237 OE_DEBUG_PUB.Add('BOM Item Type:'||l_bom_item_type,3);
238 OE_DEBUG_PUB.Add('Replenish to Order:'||l_replenish_to_order_flag,3);
239 OE_DEBUG_PUB.Add('Config Model Type:'||l_config_model_type,3);
240 END IF;
241
242 IF l_bom_item_type = 1 AND
243 l_replenish_to_order_flag = 'N' AND
244 l_config_model_type = 'N' THEN
245
246 IF p_inventory_item_id = l_inventory_item_id THEN
247 x_top_container_model := 'Y';
248 END IF;
249
250 x_part_of_container := 'Y';
251
252 END IF;
253
254 IF l_debug_level > 0 THEN
255 OE_DEBUG_PUB.Add('Top Container Model?:'||x_top_container_model,3);
256 OE_DEBUG_PUB.Add('Part of Container Model?:'||x_part_of_container,3);
257 END IF;
258
259 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model..');
260
261 RETURN;
262 END IF; -- inv item id is not null;
263
264 ------------------- if line_id is to be used -----------------
265 IF p_line_id IS NOT NULL THEN
266
267 IF l_debug_level > 0 THEN
268 OE_DEBUG_PUB.Add('Line Id:'||p_line_id ,3);
269 OE_DEBUG_PUB.Add('Top Model Line id:'||l_top_model_line_id,3);
270 OE_DEBUG_PUB.Add('ATO Line id:'||l_ato_line_id,3);
271 END IF;
272
273 IF l_top_model_line_id IS NULL OR
274 l_ato_line_id IS NULL THEN
275
276 SELECT top_model_line_id,ato_line_id
277 INTO l_top_model_line_id,l_ato_line_id
278 FROM oe_order_lines
279 WHERE line_id = p_line_id;
280
281 IF l_debug_level > 0 THEN
282 OE_DEBUG_PUB.Add('Top Model Line id:'||l_top_model_line_id,3);
283 OE_DEBUG_PUB.Add('ATO Line id:'||l_ato_line_id,3);
284 END IF;
285
286 END IF;
287
288 IF l_top_model_line_id IS NOT NULL AND
289 l_ato_line_id IS NULL THEN
290
291 SELECT mtl_msi.config_model_type
292 INTO l_config_model_type
293 FROM mtl_system_items mtl_msi, oe_order_lines oe_l
294 WHERE oe_l.line_id = l_top_model_line_id
295 AND oe_l.inventory_item_id = mtl_msi.inventory_item_id
296 AND mtl_msi.organization_id = OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID',p_org_id); --Bug 5524710
297
298 IF l_debug_level > 0 THEN
299 OE_DEBUG_PUB.Add('Model Type:'||l_config_model_type,2);
300 END IF;
301
302 IF l_config_model_type = 'N' THEN
303 x_part_of_container := 'Y';
304 IF l_top_model_line_id = p_line_id THEN
305 x_top_container_model := 'Y';
306 END IF;
307 ELSE
308 x_part_of_container := 'N';
309 END IF;
310
311 IF l_debug_level > 0 THEN
312 OE_DEBUG_PUB.Add('Top Container Model?:'||x_top_container_model,3);
313 OE_DEBUG_PUB.Add('Part of Container Model?:'||x_part_of_container,3);
314 END IF;
315 Print_Time('Exiting OE_CONFIG_TSO_PVT.Is_Part_of_Container_Model..');
316
317 RETURN;
318
319 END IF;
320
321 END IF; --p_line_id is NOT NULL
322
323 IF l_debug_level > 0 THEN
324 OE_DEBUG_PUB.Add('Top Container Model?:'||x_top_container_model,3);
325 OE_DEBUG_PUB.Add('Part of Container Model?:'||x_part_of_container,3);
326 END IF;
327 Print_Time('Exiting OE_CONFIG_TSO_PVT.Is_Part_of_Container_Model..');
328
329 EXCEPTION
330
331 WHEN NO_DATA_FOUND THEN
332 IF l_debug_level > 0 THEN
333 OE_DEBUG_PUB.Add('No data found in Is_Part_of_Container_Model:'
334 ||sqlerrm, 3);
335 END IF;
336 RAISE FND_API.G_EXC_ERROR;
337
338 WHEN TOO_MANY_ROWS THEN
339 IF l_debug_level > 0 THEN
340 OE_DEBUG_PUB.Add('Too Many Rows in Is_Part_of_Container_Model:'
341 ||sqlerrm, 3);
342 END IF;
343 RAISE FND_API.G_EXC_ERROR;
344
345 WHEN OTHERS THEN
346 IF l_debug_level > 0 THEN
347 OE_DEBUG_PUB.Add('Other error in Is_Part_Of_Container_Model:'
348 ||sqlerrm,1);
349 END IF;
350 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
351 OE_MSG_PUB.Add_Exc_Msg
352 ( G_PKG_NAME
353 ,'Is_Part_of_Container_Model' );
354 END IF;
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356
357
358 END Is_Part_of_Container_Model;
359
360
361
362 -----------------------------------------------------------------
363 -- Name : Validate_Container_Model
364 -- Parameters : IN p_x_line_rec
365 -- IN p_old_line_rec
366 -- OUT x_return_status
367 --
368 -- Description : This API is used to validate or restrict
369 -- certain restrictions for the MACD orders.
370 -- It is called from the OE_Validate_Line.Entity
371 -- during the entity level validation
372 --
373 -- Change Record :
374 ------------------------------------------------------------------
375 PROCEDURE Validate_Container_Model
376 ( p_line_rec IN OE_Order_Pub.Line_Rec_Type
377 , p_old_line_rec IN OE_Order_Pub.Line_Rec_Type
378 , x_return_status OUT NOCOPY VARCHAR2
379 )
380 IS
381 l_delta NUMBER;
382 l_part_of_container_model VARCHAR2(1);
383 l_return_status VARCHAR2(1);
384 l_top_container_model VARCHAR2(1);
385 l_description VARCHAR2(240);
386 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
387 l_config_mode NUMBER;
388 l_x_return_status VARCHAR2(1);
389
390 l_ib_trackable_flag VARCHAR2(1);
391 BEGIN
392
393 Print_Time('Entering OE_CONFIG_TSO_PVT.Validate_Container_model...');
394
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 IF l_debug_level > 0 THEN
398 OE_DEBUG_PUB.Add('Before Calling Is_Part_Of_Container_Model...',3);
399 END IF;
400
401 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
402 ( p_line_id => p_line_rec.line_id
403 ,p_top_model_line_id => p_line_rec.top_model_line_id
404 ,p_ato_line_id => p_line_rec.ato_line_id
405 ,p_inventory_item_id => p_line_rec.inventory_item_id
406 ,p_operation => p_line_rec.operation
407 ,x_top_container_model => l_top_container_model
408 ,x_part_of_container => l_part_of_container_model );
409
410 --Abghosh
411
412 IF l_part_of_container_model = 'N' THEN
413
414 IF p_line_rec.ib_owner='INSTALL_BASE' THEN
415 IF l_debug_level > 0 THEN
416 OE_DEBUG_PUB.Add('IB validation failed: IB_OWNER',3);
417 END IF;
418 x_return_status := FND_API.G_RET_STS_ERROR;
419 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
420 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_OWNER');
421 OE_MSG_PUB.Add;
422 END IF;
423
424 IF p_line_rec.ib_installed_at_location='INSTALL_BASE' THEN
425 IF l_debug_level > 0 THEN
426 OE_DEBUG_PUB.Add('IB validation failed: INSTALLED_AT_LOCATION',3);
427 END IF;
428 x_return_status := FND_API.G_RET_STS_ERROR;
429 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
430 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_INSTALLED_AT_LOCATION');
431 OE_MSG_PUB.Add;
432 END IF;
433
434 IF p_line_rec.ib_current_location='INSTALL_BASE' THEN
435 IF l_debug_level > 0 THEN
436 OE_DEBUG_PUB.Add('IB validation failed: IB_CURRENT_LOCATION',3);
437 END IF;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
440 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_CURRENT_LOCATION');
441 OE_MSG_PUB.Add;
442 END IF;
443
444 END IF;
445 -- Abghoshend contd
446
447 IF l_part_of_container_model = 'N' THEN
448 IF l_debug_level > 0 THEN
449 OE_DEBUG_PUB.Add('Not a part of container model, hence RETURNing',3);
450 END IF;
451 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Validate_Container_Model...');
452 RETURN;
453 END IF;
454
455 -- Abghosh
456
457 IF l_part_of_container_model='Y' AND
458 (p_line_rec.ib_owner='INSTALL_BASE' OR
459 p_line_rec.ib_installed_at_location='INSTALL_BASE' OR
460 p_line_rec.ib_current_location='INSTALL_BASE') THEN
461
462
463 OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode
464 ( p_line_rec => p_line_rec
465 ,p_check_ibreconfig => 'Y'
466 ,x_config_mode => l_config_mode
467 ,x_return_status => l_x_return_status );
468
469 IF l_debug_level >0 THEN
470 OE_DEBUG_PUB.ADD('l_config_mode='||l_config_mode);
471 END IF;
472
473 -- Bug 3677344
474 IF l_x_return_status= FND_API.G_RET_STS_ERROR THEN
475 Raise FND_API.G_EXC_ERROR ;
476 ELSIF l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
477 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
478 END IF;
479 -- Bug 3677344
480 END IF;
481
482 IF l_config_mode = 1 THEN
483
484 IF p_line_rec.ib_owner='INSTALL_BASE' THEN
485 IF l_debug_level > 0 THEN
486 OE_DEBUG_PUB.Add('IB validation failed: IB_OWNER');
487 END IF;
488 x_return_status := FND_API.G_RET_STS_ERROR;
489 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
490 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_OWNER'));
491 OE_MSG_PUB.Add;
492 END IF;
493
494 IF p_line_rec.ib_installed_at_location='INSTALL_BASE' THEN
495 IF l_debug_level > 0 THEN
496 OE_DEBUG_PUB.Add('IB validation failed: INSTALLED_AT_LOCATION',3);
497 END IF;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
500 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_INSTALLED_AT_LOCATION'));
501 OE_MSG_PUB.Add;
502 END IF;
503
504 IF p_line_rec.ib_current_location='INSTALL_BASE' THEN
505 IF l_debug_level > 0 THEN
506 OE_DEBUG_PUB.Add('IB validation failed: IB_CURRENT_LOCATION', 3);
507 END IF;
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
510 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_CURRENT_LOCATION'));
511 OE_MSG_PUB.Add;
512 END IF;
513
514 END IF;
515
516 --Abghosh
517
518 -- Reconfigure of MACD order after booking is not supported.
519 -- Reconfiguring can be only for a top model line.
520 IF l_debug_level > 0 THEN
521 OE_DEBUG_PUB.Add('Inv Item Id:'||p_line_rec.inventory_item_id,3);
522 OE_DEBUG_PUB.Add('Item:'||p_line_rec.ordered_item,3);
523 OE_DEBUG_PUB.Add('Ord Qty (New):'||p_line_rec.ordered_quantity,3);
524 OE_DEBUG_PUB.Add('Cancelled Flag:'||p_line_rec.cancelled_flag,3);
525 OE_DEBUG_PUB.Add('Cascade Changes:'
526 ||OE_CONFIG_UTIL.Cascade_Changes_Flag,3);
527 OE_DEBUG_PUB.Add('VAL_CT_MDL_CHK: No Reconfig of order after booking',3);
528 OE_DEBUG_PUB.Add('Booked flag:'||p_line_rec.booked_flag,3);
529 OE_DEBUG_PUB.Add('Operation:'||p_line_rec.operation,3);
530 OE_DEBUG_PUB.Add('Old Ord Qty:'||p_old_line_rec.ordered_quantity,3);
531 END IF;
532
533 IF NVL(p_line_rec.booked_flag,'N') = 'Y' AND
534 (p_line_rec.operation=OE_GLOBALS.G_OPR_CREATE OR
535 (p_line_rec.operation=OE_GLOBALS.G_OPR_UPDATE AND
536 NOT OE_Globals.Equal(p_line_rec.ordered_quantity,
537 p_old_line_rec.ordered_quantity)) OR
538 p_line_rec.operation=OE_GLOBALS.G_OPR_DELETE) THEN
539
540 IF ( p_line_rec.cancelled_flag = 'Y' AND p_line_rec.ordered_quantity = 0 AND (OE_CONFIG_UTIL.Cascade_Changes_Flag = 'Y' OR l_top_container_model = 'Y') )
541 OR
542 ( p_line_rec.ordered_quantity = 0 and nvl(p_line_rec.model_remnant_flag, 'N') = 'Y' ) --OR condition Added for fp bug 5662532
543 THEN
544
545 IF l_debug_level > 0 THEN
546 OE_DEBUG_PUB.Add('Note: Booked MACD Order Cancel Allowed',3);
547 END IF;
548 ELSE
549 IF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
550 Null;
551 ELSE
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 FND_MESSAGE.SET_NAME('ONT','ONT_TSO_BOOKED_ORDER');
554 OE_MSG_PUB.Add;
555 END IF;
556 IF l_debug_level > 0 THEN
557 OE_DEBUG_PUB.Add('ERRM: Reconfigure after booking is not supported',3);
558 END IF;
559 END IF;
560
561 END IF; --booked flag = y
562
563 --Qty of IB trackable item that is part of container model should not
564 --other than 1 unless it is cancellation, when qty = 0
565 IF l_debug_level > 0 THEN
566 OE_DEBUG_PUB.Add ('VAL_CT_MDL_CHK: Ib trackable component value <> 1',3);
567 END IF;
568 IF (p_line_rec.cancelled_flag = 'Y' AND p_line_rec.ordered_quantity = 0 AND ( OE_CONFIG_UTIL.Cascade_Changes_Flag = 'Y' OR l_top_container_model = 'Y'))
569 OR
570 ( p_line_rec.ordered_quantity = 0 and nvl(p_line_rec.model_remnant_flag, 'N') = 'Y' ) --OR condition Added for fp:bug 5662532
571
572 THEN
573 IF l_debug_level > 0 THEN
574 OE_DEBUG_PUB.Add ('Note:Cancellation allowed',1);
575 END IF;
576 ELSE
577 IF l_top_container_model = 'Y' AND p_line_rec.ordered_quantity <> 1 THEN -- 7217602
578
579 SELECT nvl(comms_nl_trackable_flag, 'N')
580 INTO l_ib_trackable_flag
581 FROM mtl_system_items
582 WHERE inventory_item_id = p_line_rec.inventory_item_id
583 AND organization_id = OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID');
584
585 IF l_ib_trackable_flag = 'Y' THEN
586 IF l_debug_level > 0 THEN
587 OE_DEBUG_PUB.Add ('Item is IB trackable',3);
588 OE_DEBUG_PUB.Add('ERRM: IB trackable item Ordered Quantity must be 1 Only',3);
589 END IF;
590 x_return_status := FND_API.G_RET_STS_ERROR;
591 FND_MESSAGE.SET_NAME ('ONT','ONT_TSO_ORD_QTY_NOT_ONE');
592 OE_MSG_PUB.Add;
593 ELSE
594 IF l_debug_level > 0 THEN
595 OE_DEBUG_PUB.Add('Note: Item not IB trackable',3);
596 OE_DEBUG_PUB.Add('This line qty not restricted to 1',3);
597 END IF;
598 END IF;
599
600 END IF; --ord qty > 1
601 END IF;
602
603 -- Line type change for components of a container model
604 --is not allowed
605 IF l_debug_level > 0 THEN
606 OE_DEBUG_PUB.Add('VAL_CT_MDL_CHK: line type change for components of container',3);
607 OE_DEBUG_PUB.Add('New line type:'||p_line_rec.line_type_id,3);
608 OE_DEBUG_PUB.Add('Old line type:'||p_old_line_rec.line_type_id,3);
609 OE_DEBUG_PUB.Add('New line operation:'||p_line_rec.operation,3);
610 OE_DEBUG_PUB.Add('OECFG_VALIDATE_CONFIG Flag:'
611 || OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG,3);
612 END IF;
613
614 IF (NOT OE_GLOBALS.EQUAL(p_line_rec.line_type_id
615 ,p_old_line_rec.line_type_id)) AND
616 p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
617 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG = 'Y' THEN
618
619 x_return_status := FND_API.G_RET_STS_ERROR;
620
621 FND_MESSAGE.SET_NAME ('ONT','ONT_TSO_NO_LINE_TYPE_CHANGE');
622 OE_MSG_PUB.Add;
623
624 IF l_debug_level > 0 THEN
625 OE_DEBUG_PUB.Add('ERRM: Line Type Change not allowed for MACD orders',3);
626 END IF;
627
628 END IF;
629
630 -- Line below top model of the config can be deleted only if
631 -- the line has not changed in CZ
632 IF l_debug_level > 0 THEN
633 OE_DEBUG_PUB.Add('VAL_CT_MDL_CHK: Del of opt items only if no change in CZ',3);
634 OE_DEBUG_PUB.Add('New line operation:'||p_line_rec.operation,3);
635 OE_DEBUG_PUB.Add('OECFG_VALIDATE_CONFIG Flag:'
636 || OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG,3);
637 END IF;
638
639 IF p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE AND
640 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG = 'Y' THEN
641
642 IF NVL(p_line_rec.booked_flag,'N')='N' THEN
643
644 BEGIN
645
646 SELECT 1
647 INTO l_delta
648 FROM cz_config_details_v
649 WHERE config_delta = 0
650 AND config_hdr_id = p_line_rec.config_header_id
651 AND config_rev_nbr = p_line_rec.config_rev_nbr
652 AND config_item_id = p_line_rec.configuration_id;
653
654 EXCEPTION
655
656 WHEN NO_DATA_FOUND THEN
657 IF l_debug_level > 0 THEN
658 OE_DEBUG_PUB.Add('ERRM: No Data Found when selecting config delta',3);
659
660 SELECT description
661 INTO l_description
662 FROM cz_config_details_v cz_czv, mtl_system_items mtl_msi
663 WHERE cz_czv.inventory_item_id = mtl_msi.inventory_item_id
664 AND cz_czv.config_delta <> 0 --implies change
665 AND cz_czv.config_hdr_id = p_line_rec.config_header_id
666 AND cz_czv.config_rev_nbr = p_line_rec.config_rev_nbr
667 AND cz_czv.config_item_id = p_line_rec.configuration_id;
668 END IF;
669 l_delta := 0;
670 END;
671
672 IF l_delta = 0 THEN
673 x_return_status:=FND_API.G_RET_STS_ERROR;
674 FND_MESSAGE.SET_NAME('ONT','ONT_TSO_DELETE_NOT_ALLOWED');
675 FND_MESSAGE.SET_TOKEN('ITEM_DESCRIPTION',l_description);
676 OE_MSG_PUB.Add;
677 IF l_debug_level > 0 THEN
678 OE_DEBUG_PUB.Add('ERRM: Line changed in CZ, delete not allowed',3);
679 END IF;
680 END IF;
681
682 END IF; --booked flag = n
683
684 END IF; --operation=delete
685
686
687 -- Field sold_to_org_id on the header cannot be updated
688 -- once an item instance has been selected on a line.
689 -- Also, sold_to_org_id on a line holding a reconfigured
690 -- Also item instance cannot be updated
691 IF l_debug_level > 0 THEN
692 OE_DEBUG_PUB.Add('VAL_CT_MDL_CHK: No change in sold_to_org_id allowed',3);
693 OE_DEBUG_PUB.Add('New line rec sold to:'||p_line_rec.sold_to_org_id,3);
694 OE_DEBUG_PUB.Add('Old line rec sold to:'||p_old_line_rec.sold_to_org_id,3);
695 OE_DEBUG_PUB.Add('New line operation:'||p_line_rec.operation,3);
696 END IF;
697 IF NOT OE_GLOBALS.EQUAL(p_line_rec.sold_to_org_id,
698 p_old_line_rec.sold_to_org_id) AND
699 p_line_rec.operation=OE_GLOBALS.G_OPR_UPDATE THEN
700
701 x_return_status := FND_API.G_RET_STS_ERROR;
702
703 FND_MESSAGE.SET_NAME('ONT','ONT_TSO_NO_CUSTOMER_CHANGE');
704 OE_MSG_PUB.Add;
705
706 IF l_debug_level > 0 THEN
707 OE_DEBUG_PUB.Add('ERRM: Customer Change not allowed for MACD orders',3);
708 END IF;
709
710 END IF;
711
712 IF l_debug_level > 0 THEN
713 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
714 OE_DEBUG_PUB.Add('All validations for container model passed',3);
715 ELSE
716 OE_DEBUG_PUB.Add('ERR:Validations violation for container model',3);
717 END IF;
718 END IF;
719
720 Print_Time('Exiting OE_CONFIG_TSO_PVT.Validate_Container_Model...');
721
722 EXCEPTION
723
724 WHEN FND_API.G_EXC_ERROR THEN
725 IF l_debug_level > 0 THEN
726 OE_DEBUG_PUB.Add('Expected Error in Validate_Container_Model:'
727 ||sqlerrm,3);
728 END IF;
729 x_return_status := FND_API.G_RET_STS_ERROR;
730
731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
732 IF l_debug_level > 0 THEN
733 OE_DEBUG_PUB.Add('Unexpected Error Validate_Container_Model:'
734 ||sqlerrm,3);
735 END IF;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737
738 WHEN OTHERS THEN
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 IF l_debug_level > 0 THEN
741 OE_DEBUG_PUB.Add('Other error in Validate_Container_Model:'
742 ||sqlerrm,1);
743 END IF;
744 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
745 OE_MSG_PUB.Add_Exc_Msg
746 ( G_PKG_NAME
747 ,'Validate_Container_Model' );
748 END IF;
749
750
751 END Validate_Container_Model;
752
753
754 /*----------------------------------------------------------------
755 Name : Get_MACD_Action_Mode
756 Parameters : IN p_top_model_line_id
757 IN p_line_id
758 OUT x_config_mode
759
760 Description : This procedure is used to detect a new
761 configuration vs a re-configuration.
762 By new configuration we mena when model order
763 line was already configured using Configurator
764 alone without first doing an IB instance search
765
766
767
768 Change Record: Change in specifications and logic as per HLD ver 4.9
769 added p_x_line_rec so that the api can be called during
770 operation create.
771
772 How to use this api:
773 1) call this api only if the line is part of a container model.
774
775 2) pass anyone of the 3 input parameters, we will use different
776 sqls to and will go in order of,
777 p_top_model_line_id
778 p_line_id
779 p_line_rec
780
781 pass p_line_rec if the line is yet not saved in oe_order_lines.
782
783 if you pass p_line_id or p_top_model_line_id, api assumes
784 that the lines are saved in oe_order_lines.
785
786 3) if p_check_ibreconfig is passed, pass p_line_rec as well.
787
788
789 4) x_config_mode will be,
790 1 - new config
791 2 - re-config as in change made in cz.
792 3 - re-config only query from ib, no change in cz for this line or
793 the configuration based on i/p parameter passed.
794 4 - there are one or more components changed in cz for the top model.
795 null - incorrect input parameters or products not installed or
796 exception or top model getting created from ui.
797
798 5) from copy_order - call this api with top_model_line_id of the
799 model that is getting copied and try to avoind calling if
800 called for one line in that model as it would get same
801 results for all lines in that model.
802
803 6) for ib fields validation send p_line_rec and p_check_ibrconfig
804 -----------------------------------------------------------------*/
805
806 PROCEDURE Get_MACD_Action_Mode
807 ( p_line_rec IN OE_Order_pub.Line_Rec_Type := null
808 , p_line_id IN NUMBER := null
809 , p_top_model_line_id IN NUMBER := null
810 , p_check_ibreconfig IN VARCHAR2 := null
811 , x_config_mode OUT NOCOPY NUMBER
812 , x_return_status OUT NOCOPY VARCHAR2
813 )
814 IS
815 l_baseline_rev_nbr NUMBER(9);
816 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
817 l_status VARCHAR2(1);
818 l_ind VARCHAR2(1);
819 l_schema VARCHAR2(30);
820
821 BEGIN
822 Print_Time('Entering OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode..');
823
824 x_config_mode := null;
825
826 BEGIN
827
828 IF p_top_model_line_id is NOT NULL THEN
829
830 IF l_debug_level > 0 THEN
831 OE_DEBUG_PUB.Add('using top_model_line_id ' || p_top_model_line_id, 1);
832 END IF;
833
834 SELECT cz_hdr.baseline_rev_nbr
835 INTO l_baseline_rev_nbr
836 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
837 cz_config_items czi
838 WHERE oe_line.top_model_line_id = p_top_model_line_id
839 AND czi.config_hdr_id = oe_line.config_header_id
840 AND czi.config_rev_nbr = oe_line.config_rev_nbr
841 AND czi.config_item_id = oe_line.configuration_id
842 AND cz_hdr.config_hdr_id = czi.instance_hdr_id
843 AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
844 AND cz_hdr.baseline_rev_nbr is not NULL
845 AND rownum = 1;
846
847 IF l_debug_level > 0 THEN
848 OE_DEBUG_PUB.Add('top model baseline rev exists', 1);
849 END IF;
850
851 x_config_mode := 3;
852
853 BEGIN
854 SELECT czi.config_delta
855 INTO l_baseline_rev_nbr
856 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
857 cz_config_items czi
858 WHERE oe_line.top_model_line_id = p_top_model_line_id
859 AND czi.config_hdr_id = oe_line.config_header_id
860 AND czi.config_rev_nbr = oe_line.config_rev_nbr
861 AND czi.config_item_id = oe_line.configuration_id
862 AND nvl(czi.config_delta, 0) > 0
863 AND cz_hdr.config_hdr_id = czi.instance_hdr_id
864 AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
865 AND cz_hdr.baseline_rev_nbr is not NULL
866 AND rownum = 1;
867
868 IF l_debug_level > 0 THEN
869 OE_DEBUG_PUB.Add('111 config delta > 0', 1);
870 END IF;
871
872 x_config_mode := 4;
873 EXCEPTION
874 WHEN no_data_found THEN
875 IF l_debug_level > 0 THEN
876 OE_DEBUG_PUB.Add('no data 111 - no config delta so ib reonfig', 1);
877 END IF;
878 END;
879
880 IF l_debug_level > 0 THEN
881 OE_DEBUG_PUB.Add('top model x_config_mode ' || x_config_mode, 1);
882 END IF;
883 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode');
884 RETURN;
885
886 ELSIF p_line_id is NOT NULL THEN
887
888 IF l_debug_level > 0 THEN
889 OE_DEBUG_PUB.Add('using line_id ' || p_line_id, 1);
890 END IF;
891
892 SELECT cz_hdr.baseline_rev_nbr
893 INTO l_baseline_rev_nbr
894 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
895 cz_config_items czi
896 WHERE oe_line.line_id = p_line_id
897 AND czi.config_hdr_id = oe_line.config_header_id
898 AND czi.config_rev_nbr = oe_line.config_rev_nbr
899 AND czi.config_item_id = oe_line.configuration_id
900 AND cz_hdr.config_hdr_id = czi.instance_hdr_id
901 AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
902 AND cz_hdr.baseline_rev_nbr is NOT NULL;
903
904 x_config_mode := 3;
905
906 BEGIN
907 SELECT czi.config_delta
908 INTO l_baseline_rev_nbr
909 FROM oe_order_lines oe_line,
910 cz_config_items czi
911 WHERE oe_line.line_id = p_line_id
912 AND czi.config_hdr_id = oe_line.config_header_id
913 AND czi.config_rev_nbr = oe_line.config_rev_nbr
914 AND czi.config_item_id = oe_line.configuration_id
915 AND nvl(czi.config_delta, 0) > 0;
916
917 x_config_mode := 2;
918
919 IF l_debug_level > 0 THEN
920 OE_DEBUG_PUB.Add('222 config delta > 0', 1);
921 END IF;
922 EXCEPTION
923 WHEN no_data_found THEN
924 IF l_debug_level > 0 THEN
925 OE_DEBUG_PUB.Add('no data 222 - no config delta so ib reonfig', 1);
926 END IF;
927 END;
928
929
930 IF l_debug_level > 0 THEN
931 OE_DEBUG_PUB.Add('p_line_id x_config_mode ' || x_config_mode, 1);
932 END IF;
933
934 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode');
935 RETURN;
936
937 ELSIF p_line_rec.line_id is NOT NULL THEN
938
939 IF l_debug_level > 0 THEN
940 OE_DEBUG_PUB.Add('using line_rec ' || p_line_rec.line_id, 1);
941 OE_DEBUG_PUB.Add('Config Hdr:'||p_line_rec.config_header_id,1);
942 OE_DEBUG_PUB.Add('Config Rev:'||p_line_rec.config_rev_nbr,1);
943 OE_DEBUG_PUB.Add('Config ID:'||p_line_rec.configuration_id,1);
944 OE_DEBUG_PUB.Add('OrdItem:'||p_line_rec.ordered_item,1);
945 END IF;
946
947 IF p_line_rec.top_model_line_id is NULL OR
948 p_line_rec.config_header_id is NULL OR
949 p_line_rec.config_rev_nbr is NULL OR
950 p_line_rec.configuration_id is NULL THEN
951
952 IF l_debug_level > 0 THEN
953 OE_DEBUG_PUB.Add('ERR: At least one Config keys not present', 1);
954 END IF;
955 x_return_status := FND_API.G_RET_STS_ERROR;
956 x_config_mode := null;
957 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode');
958 RETURN;
959 --RAISE FND_API.G_EXC_ERROR;
960 END IF;
961
962
963 SELECT cz_hdr.baseline_rev_nbr
964 INTO l_baseline_rev_nbr
965 FROM cz_config_hdrs cz_hdr, cz_config_items czi
966 WHERE czi.config_hdr_id = p_line_rec.config_header_id
967 AND czi.config_rev_nbr = p_line_rec.config_rev_nbr
968 AND czi.config_item_id = p_line_rec.configuration_id
969 AND cz_hdr.config_hdr_id = czi.instance_hdr_id
970 AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
971 AND cz_hdr.baseline_rev_nbr is NOT NULL;
972
973 IF l_debug_level > 0 THEN
974 OE_DEBUG_PUB.Add('Baseline 2:'||l_baseline_rev_nbr,2);
975 END IF;
976
977 x_config_mode := 3;
978
979 BEGIN
980 SELECT czi.config_delta
981 INTO l_baseline_rev_nbr
982 --bug3667985 fix
983 --FROM cz_config_hdrs cz_hdr, cz_config_items czi
984 FROM cz_config_items czi
985 WHERE czi.config_hdr_id = p_line_rec.config_header_id
986 AND czi.config_rev_nbr = p_line_rec.config_rev_nbr
987 AND czi.config_item_id = p_line_rec.configuration_id
988 AND nvl(czi.config_delta, 0) > 0;
989
990 x_config_mode := 2;
991
992 IF l_debug_level > 0 THEN
993 OE_DEBUG_PUB.Add('333 config delta > 0', 1);
994 END IF;
995 EXCEPTION
996 WHEN no_data_found THEN
997 IF l_debug_level > 0 THEN
998 OE_DEBUG_PUB.Add('no data 333 - no config delta so ib reonfig', 1);
999 END IF;
1000 x_config_mode := 3;
1001 END;
1002
1003 IF l_debug_level > 0 THEN
1004 OE_DEBUG_PUB.Add('p_line_rec x_config_mode ' || x_config_mode, 1);
1005 END IF;
1006
1007 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode');
1008 RETURN;
1009
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN NO_DATA_FOUND THEN
1014 IF l_debug_level > 0 THEN
1015 OE_DEBUG_PUB.Add('no data Baseline Rev', 2);
1016 END IF;
1017 l_baseline_rev_nbr := null;
1018 END;
1019
1020 IF l_baseline_rev_nbr IS NULL THEN
1021 IF l_debug_level > 0 THEN
1022 OE_DEBUG_PUB.Add('x_config_mode set to 1=new config using CZ',3);
1023 END IF;
1024 x_config_mode := 1;
1025 END IF;
1026
1027 Print_Time('Exiting OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode..');
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 IF l_debug_level > 0 THEN
1032 OE_DEBUG_PUB.Add('Other error in Get_MACD_Action_Mode:'||sqlerrm,1);
1033 END IF;
1034
1035 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1036 OE_MSG_PUB.Add_Exc_Msg
1037 ( G_PKG_NAME
1038 ,'Get_MACD_Action_Mode' );
1039 END IF;
1040
1041 x_config_mode := null;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 END Get_MACD_Action_Mode;
1044
1045
1046 -----------------------------------------------------------------------
1047 -- Name: Remove_Unchanged_Lines
1048 --
1049 -- Parameters: IN p_top_model_line_id
1050 -- IN p_line_id
1051 -- IN ato_line_id
1052 -- OUT x_msg_count
1053 -- OUT x_msg_data
1054 -- OUT x_return_status
1055 --
1056 -- Description: This API is called from Remove_Unchanged_Components
1057 -- and removes all (optional) unchanged lines in CZ
1058 -- for a given top model line
1059 --
1060 -- Change Record:
1061 ----------------------------------------------------------------------
1062 PROCEDURE Remove_Unchanged_Lines
1063 ( p_top_model_line_id IN NUMBER
1064 ,p_line_id IN NUMBER
1065 ,p_ato_line_id IN NUMBER
1066 ,x_msg_count OUT NOCOPY NUMBER
1067 ,x_msg_data OUT NOCOPY VARCHAR2
1068 ,x_return_status OUT NOCOPY VARCHAR2
1069 )
1070 IS
1071 CURSOR C_UNCHANGED_LINES IS
1072 SELECT oe_ol.line_id
1073 ,oe_ol.config_header_id
1074 ,oe_ol.config_rev_nbr
1075 ,oe_ol.configuration_id
1076 FROM oe_order_lines oe_ol
1077 ,cz_config_details_v cz_det
1078 WHERE oe_ol.top_model_line_id = p_top_model_line_id
1079 AND cz_det.config_delta = 0
1080 AND cz_det.config_hdr_id = oe_ol.config_header_id
1081 AND cz_det.config_rev_nbr = oe_ol.config_rev_nbr
1082 AND cz_det.config_item_id = oe_ol.configuration_id
1083 AND oe_ol.line_id <> oe_ol.top_model_line_id
1084 AND oe_ol.open_flag = 'Y'
1085 ORDER BY option_number desc;
1086
1087 l_line_id NUMBER;
1088 l_config_header_id NUMBER;
1089 l_config_rev_nbr NUMBER;
1090 l_configuration_id NUMBER;
1091 l_cursor_count NUMBER;
1092
1093 l_ato_line_id NUMBER;
1094 l_top_model_line_id NUMBER;
1095 l_change_flag VARCHAR2(1);
1096 l_part_of_container VARCHAR2(1);
1097 l_top_container_model VARCHAR2(1);
1098 l_top_config_header_id NUMBER;
1099 l_top_config_rev_nbr NUMBER;
1100 l_header_id NUMBER;
1101
1102 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1103 l_description VARCHAR2(240);
1104
1105
1106 BEGIN
1107
1108 Print_Time('Entering OE_CONFIG_TSO_PVT.Remove_Unchanged_Lines...');
1109
1110 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
1111 ( p_line_id => p_line_id
1112 ,p_top_model_line_id => p_top_model_line_id
1113 ,p_ato_line_id => p_ato_line_id
1114 ,p_inventory_item_id => NULL
1115 ,x_top_container_model => l_top_container_model
1116 ,x_part_of_container => l_part_of_container );
1117
1118 IF l_top_container_model='N' OR l_part_of_container='N' THEN
1119
1120 IF l_debug_level > 0 THEN
1121 OE_DEBUG_PUB.Add('Item not eligible for removal of unchanged lines',3);
1122 END IF;
1123
1124 x_return_status:=FND_API.G_RET_STS_ERROR;
1125 x_msg_count := 1;
1126
1127 SELECT description
1128 INTO l_description
1129 FROM oe_order_lines oe_oel, mtl_system_items mtl_msi
1130 WHERE oe_oel.line_id = p_line_id
1131 AND oe_oel.inventory_item_id = mtl_msi.inventory_item_id
1132 AND oe_oel.org_id = mtl_msi.organization_id;
1133
1134 FND_MESSAGE.SET_NAME('ONT','ONT_TSO_NOT_CONTAINER');
1135 FND_MESSAGE.SET_TOKEN('ITEM_DESCRIPTION',l_description);
1136 OE_MSG_PUB.Add;
1137
1138 Print_Time ('Exiting OE_CONFIG_TSO_PVT.Remove_Unchanged_Lines');
1139 RETURN;
1140 END IF;
1141
1142 --{ bug3611490 starts
1143 l_cursor_count := 0;
1144 OPEN C_UNCHANGED_LINES;
1145 LOOP
1146 --FOR c_lines IN C_UNCHANGED_LINES LOOP
1147 FETCH C_UNCHANGED_LINES INTO l_line_id, l_config_header_id
1148 ,l_config_rev_nbr, l_configuration_id;
1149 EXIT WHEN C_UNCHANGED_LINES%NOTFOUND;
1150
1151 l_cursor_count := l_cursor_count + 1;
1152 IF l_debug_level > 0 THEN
1153 OE_DEBUG_PUB.Add('Line Id:'|| l_line_id,5);
1154 OE_DEBUG_PUB.Add('Config Hdr:'||l_config_header_id,5);
1155 OE_DEBUG_PUB.Add('Config Rev Nbr:'||l_config_rev_nbr,5);
1156 OE_DEBUG_PUB.Add('Config ID:'||l_configuration_id,5);
1157 END IF;
1158
1159 Print_Time('Calling CZ_PUB.Ext_deactivate_item at: ');
1160
1161 CZ_NETWORK_API_PUB.Ext_Deactivate_Item
1162 ( p_api_version => 1.0
1163 ,p_config_hdr_id => l_config_header_id
1164 ,p_config_rev_nbr => l_config_rev_nbr
1165 ,p_config_item_id => l_configuration_id
1166 ,x_return_status => x_return_status
1167 ,x_msg_count => x_msg_count
1168 ,x_msg_data => x_msg_data );
1169
1170 Print_Time('Return from CZ_PUB.Ext_Deactivate_item at: ');
1171
1172 IF l_debug_level > 0 THEN
1173 OE_DEBUG_PUB.Add('After Calling CZ_NETWORK_API_PUB.'
1174 ||'Ext_Deactivate_Item:'||x_return_status,3);
1175 END IF;
1176
1177 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
1178 RAISE FND_API.G_EXC_ERROR;
1179 ELSIF x_return_status=FND_API.G_RET_STS_UNEXP_ERROR THEN
1180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1181 END IF;
1182
1183 END LOOP;
1184 CLOSE C_UNCHANGED_LINES;
1185
1186 IF l_cursor_count > 0 THEN
1187 SELECT config_header_id
1188 ,config_rev_nbr
1189 ,header_id
1190 INTO l_top_config_header_id
1191 ,l_top_config_rev_nbr
1192 ,l_header_id
1193 FROM oe_order_lines oe_l
1194 WHERE oe_l.line_id = p_top_model_line_id;
1195
1196 IF l_debug_level > 0 THEN
1197 OE_DEBUG_PUB.Add('Unchanged Lines Count:'||l_cursor_count,3);
1198 OE_DEBUG_PUB.Add('Header Id:'||l_header_id,3);
1199 OE_DEBUG_PUB.Add('Config Hdr:'||l_top_config_header_id,3);
1200 OE_DEBUG_PUB.Add('Config Rev Number:'||l_top_config_rev_nbr,3);
1201 END IF;
1202
1203 -- Delete/Cancel the Model lines which hold unchanged
1204 -- MACD components
1205 IF l_debug_level > 0 THEN
1206 OE_DEBUG_PUB.Add('Before Calling Process_Config..header_id:'
1207 ||l_header_id,3);
1208 END IF;
1209
1210 OE_CONFIG_PVT.Process_Config
1211 ( p_header_id => l_header_id
1212 ,p_config_hdr_id => l_top_config_header_id
1213 ,p_config_rev_nbr => l_top_config_rev_nbr
1214 ,p_top_model_line_id => p_top_model_line_id
1215 ,p_ui_flag => 'Y'
1216 ,x_change_flag => l_change_flag
1217 ,x_msg_count => x_msg_count
1218 ,x_msg_data => x_msg_data
1219 ,x_return_status => x_return_status );
1220
1221 IF l_debug_level > 0 THEN
1222 OE_DEBUG_PUB.Add('After Calling Process_Config..'
1223 ||x_return_status,3);
1224 END IF;
1225
1226 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1228 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231 ELSE
1232 IF l_debug_level > 0 THEN
1233 OE_DEBUG_PUB.Add ('Csr Count 0. Skip OE_CONFIG_PVT.Process_Config',1);
1234 END IF;
1235 END IF;
1236 Print_Time('Exiting OE_CONFIG_TSO_PVT.Remove_Unchanged_Lines...');
1237
1238 EXCEPTION
1239
1240 WHEN FND_API.G_EXC_ERROR THEN
1241 IF l_debug_level > 0 THEN
1242 OE_DEBUG_PUB.Add('Expected Error in Remove_Unchanged_Lines:'
1243 ||sqlerrm, 2);
1244 END IF;
1245 x_return_status := FND_API.G_RET_STS_ERROR;
1246
1247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1248 IF l_debug_level > 0 THEN
1249 OE_DEBUG_PUB.Add('Unexpected Error in Remove_unchanged_Lines:'
1250 ||sqlerrm, 1);
1251 END IF;
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1253
1254 WHEN OTHERS THEN
1255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1256 IF l_debug_level > 0 THEN
1257 OE_DEBUG_PUB.Add('Other error in Remove_Unchanged_Lines:'
1258 ||sqlerrm,1);
1259 END IF;
1260 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1261 OE_MSG_PUB.Add_Exc_Msg
1262 ( G_PKG_NAME
1263 ,'Remove_Unchanged_Lines'
1264 );
1265 END IF;
1266
1267 END Remove_Unchanged_Lines;
1268
1269
1270
1271
1272 -----------------------------------------------------------------
1273 -- Name : Remove_Unchanged_Components
1274 -- Parameters : IN p_line_id
1275 -- IN p_top_model_line_id
1276 -- IN p_ato_line_id
1277 -- OUT x_msg_data
1278 -- OUT x_msg_count
1279 -- OUT x_return_status
1280 --
1281 -- Description : This procedure enables to remove all lines
1282 -- below the top model of the configuration
1283 -- that have NOT been changed in configurator
1284 -- and are optional in the configuration
1285 --
1286 --
1287 -- Change Record :
1288 ------------------------------------------------------------------
1289 PROCEDURE Remove_Unchanged_Components
1290 ( p_header_id IN NUMBER
1291 , p_line_id IN NUMBER
1292 , p_top_model_line_id IN NUMBER
1293 , p_ato_line_id IN NUMBER
1294 , x_msg_data OUT NOCOPY VARCHAR2
1295 , x_msg_count OUT NOCOPY NUMBER
1296 , x_return_status OUT NOCOPY VARCHAR2
1297 )
1298 IS
1299
1300 CURSOR C_TOP_MODELS IS
1301 SELECT line_id
1302 ,ato_line_id
1303 ,top_model_line_id
1304 FROM oe_order_lines
1305 WHERE header_id = p_header_id
1306 AND top_model_line_id IS NOT NULL
1307 AND line_id = top_model_line_id;
1308
1309 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1310
1311 BEGIN
1312
1313 Print_Time('Entering OE_CONFIG_TSO_PVT.Remove_Unchanged_Components...');
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315
1316 --{ bug3611488 starts
1317 IF p_top_model_line_id IS NOT NULL THEN
1318
1319 IF l_debug_level > 0 THEN
1320 OE_DEBUG_PUB.Add('This call made from Lines Block',3);
1321 OE_DEBUG_PUB.Add('Calling Remove_Unchanged_Lines with...',3);
1322 OE_DEBUG_PUB.Add('HeaderID:'||p_header_id,3);
1323 OE_DEBUG_PUB.Add('LineID:'||p_line_id,3);
1324 OE_DEBUG_PUB.Add('TopModel:'||p_top_model_line_id,3);
1325 END IF;
1326
1327 OE_CONFIG_TSO_PVT.Remove_Unchanged_Lines
1328 ( p_top_model_line_id => p_top_model_line_id
1329 ,p_line_id => p_line_id
1330 ,p_ato_line_id => p_ato_line_id
1331 ,x_msg_count => x_msg_count
1332 ,x_msg_data => x_msg_data
1333 ,x_return_status => x_return_status );
1334
1335 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1336 IF l_debug_level > 0 THEN
1337 OE_DEBUG_PUB.Add ('Error in Remove_Unchanged_Lines!',3);
1338 END IF;
1339 END IF;
1340
1341 ELSE --bug3611488 ends }
1342
1343 IF l_debug_level > 0 THEN
1344 OE_DEBUG_PUB.Add('This call made from Header block',3);
1345 OE_DEBUG_PUB.Add('Header ID:'||p_header_id,3);
1346 END IF;
1347
1348 FOR c_top_model IN C_TOP_MODELS LOOP
1349
1350 IF l_debug_level > 0 THEN
1351 OE_DEBUG_PUB.Add('Calling Remove_Unchanged_lines with...',3);
1352 OE_DEBUG_PUB.Add('Line id:'||c_top_model.line_id,3);
1353 OE_DEBUG_PUB.Add('Top Model:'||c_top_model.top_model_line_id,3);
1354 END IF;
1355
1356 OE_CONFIG_TSO_PVT.Remove_Unchanged_Lines
1357 ( p_top_model_line_id => c_top_model.top_model_line_id
1358 ,p_line_id => c_top_model.line_id
1359 ,p_ato_line_id => c_top_model.ato_line_id
1360 ,x_msg_count => x_msg_count
1361 ,x_msg_data => x_msg_data
1362 ,x_return_status => x_return_status );
1363
1364 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1365 IF l_debug_level > 0 THEN
1366 OE_DEBUG_PUB.Add('ERROR during Remove_Unchanged_Lines!',3);
1367 END IF;
1368 END IF;
1369
1370 END LOOP;
1371
1372 END IF;
1373
1374 Print_Time('Exiting OE_CONFIG_TSO_PVT.Remove_Unchanged_Components...');
1375
1376 END Remove_Unchanged_Components;
1377
1378
1379
1380
1381 -----------------------------------------------------------------
1382 -- Name : populate_tso_order_lines
1383 -- Parameters : IN p_top_model_line_id
1384 -- IN p_instance_tbl
1385 -- IN p_mode
1386 -- OUT x_msg_data
1387 -- OUT x_msg_count
1388 -- OUT x_return_status
1389 --
1390 -- Description : This API is used during MACD re-configuration
1391 -- flows. OM will create order lines for the
1392 -- selected instances and some more instances
1393 -- (if returned by CZ) and container model.
1394 --
1395 --
1396 -- Change Record :
1397 ------------------------------------------------------------------
1398
1399 PROCEDURE populate_tso_order_lines
1400 ( p_header_id IN NUMBER
1401 , p_top_model_line_id IN NUMBER
1402 , p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
1403 , p_mode IN NUMBER
1404 , x_msg_data OUT NOCOPY VARCHAR2
1405 , x_msg_count OUT NOCOPY NUMBER
1406 , x_return_status OUT NOCOPY VARCHAR2
1407 )
1408 IS
1409 l_model_line_rec OE_ORDER_PUB.Line_Rec_Type;
1410 l_config_model_rec CZ_API_PUB.config_model_rec_type;
1411 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
1412 l_control_rec OE_GLOBALS.Control_Rec_Type;
1413
1414 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
1415 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
1416 l_config_tbl CZ_API_PUB.config_tbl_type;
1417 l_config_model_tbl CZ_API_PUB.config_model_tbl_type;
1418
1419 l_change_flag VARCHAR2(1);
1420 l_old_behavior VARCHAR2(1);
1421 l_frozen_model_bill VARCHAR2(1);
1422 l_top_container_model VARCHAR2(1);
1423 l_config_mode VARCHAR2(1);
1424 l_debug_level NUMBER; -- := oe_debug_pub.g_debug_level;
1425 l_header_id NUMBER;
1426 l_model_inv_item_id NUMBER;
1427 l_config_header_id NUMBER;
1428 l_config_rev_nbr NUMBER;
1429 l_inventory_item_id NUMBER;
1430
1431 l_config_creation_date DATE;
1432 l_config_model_lookup_date DATE;
1433 l_config_date DATE;
1434 l_config_effective_date DATE;
1435 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
1436 BEGIN
1437
1438 Print_Time ('Entering OE_CONFIG_TSO_PVT.populate_tso_order_lines...');
1439 l_debug_level := oe_debug_pub.g_debug_level;
1440
1441 IF l_debug_level > 0 THEN
1442 OE_DEBUG_PUB.Add('Top Model Line id:'||p_top_model_line_id,3);
1443 OE_DEBUG_PUB.Add('Instance Tbl Count:'||p_instance_tbl.count);
1444 END IF;
1445
1446
1447 IF l_debug_level > 0 THEN
1448 FOR I in p_instance_tbl.first..p_instance_tbl.last LOOP
1449 OE_DEBUG_PUB.Add
1450 ('inst hdr : '|| p_instance_tbl(I).config_instance_hdr_id,3);
1451 OE_DEBUG_PUB.Add
1452 ('inst rev : '|| p_instance_tbl(I).config_Instance_rev_number);
1453 OE_DEBUG_PUB.Add
1454 ('inst item: '|| p_instance_tbl(I).config_instance_item_id);
1455 OE_DEBUG_PUB.Add
1456 ('ship to : '|| p_instance_tbl(I).ship_to_site_use_id);
1457 OE_DEBUG_PUB.Add
1458 ('bill to : '|| p_instance_tbl(I).bill_to_site_use_id);
1459 END LOOP;
1460 oe_debug_pub.add('after the loop',2);
1461 END IF;
1462
1463 --IF the ship_to_site_use_id and bill_to_site_use_id has NULL value
1464 --for all records returned by IB, we remember this and pass the
1465 --G_CONFIG_INTSTANCE_TBL to Process_Config to improve performance
1466 --l_instance_tbl := p_instance_tbl;
1467 l_instance_tbl := OE_CONFIG_PVT.G_CONFIG_INSTANCE_TBL;
1468
1469 FOR I IN 1..p_instance_tbl.COUNT LOOP
1470
1471 IF p_instance_tbl(I).ship_to_site_use_id IS NOT NULL AND
1472 p_instance_tbl(I).bill_to_site_use_id IS NOT NULL THEN
1473
1474 l_instance_tbl := p_instance_tbl;
1475 IF l_debug_level > 0 THEN
1476 OE_DEBUG_PUB.Add('MACD: Instance table has values',3);
1477 OE_DEBUG_PUB.Add('Breaking out of loop with table copy',3);
1478 END IF;
1479 EXIT; --copied table so break out of loop
1480 END IF;
1481
1482 END LOOP;
1483
1484 oe_debug_pub.add('after the second loop',2);
1485
1486 IF p_top_model_line_id IS NOT NULL THEN
1487 BEGIN
1488 SELECT 'A'
1489 INTO l_config_mode
1490 FROM oe_order_lines
1491 WHERE line_id = p_top_model_line_id
1492 AND config_header_id IS NOT NULL;
1493 EXCEPTION
1494 WHEN NO_DATA_FOUND THEN
1495 IF p_top_model_line_id is NULL THEN
1496 l_config_mode := 'R';
1497 ELSE
1498 FND_Message.Set_Name('ONT', 'ONT_CONFIG_USE_CZ_NOT_IB');
1499 OE_Msg_Pub.add;
1500 RAISE FND_API.G_EXC_ERROR;
1501 END IF;
1502 END;
1503 ELSE
1504 l_config_mode := 'R';
1505 END IF;
1506
1507 IF l_debug_level > 0 THEN
1508 OE_DEBUG_PUB.Add('Config Mode is:'||l_config_mode,3);
1509 END IF;
1510
1511
1512 IF p_top_model_line_id IS NOT NULL THEN
1513
1514 BEGIN
1515 SELECT creation_date
1516 ,header_id
1517 ,inventory_item_id
1518 INTO l_config_creation_date
1519 ,l_header_id
1520 ,l_model_inv_item_id
1521 FROM oe_order_lines
1522 WHERE line_id = p_top_model_line_id;
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 oe_debug_pub.add('Other exception in select from oe-order_lines',3);
1526 END;
1527
1528 IF l_debug_level > 0 THEN
1529 oe_debug_pub.add('RMV: After select from order_lines...',3);
1530 oe_debug_pub.add('RMV: Creat date is '||l_config_creation_date,3);
1531 OE_DEBUG_PUB.Add('Before calling Get_Config_Effective_Date.',3);
1532 END IF;
1533
1534 OE_CONFIG_UTIL.Get_Config_Effective_Date
1535 ( p_model_line_id => p_top_model_line_id
1536 ,x_old_behavior => l_old_behavior
1537 ,x_config_effective_date => l_config_date
1538 ,x_frozen_model_bill => l_frozen_model_bill );
1539
1540 IF l_debug_level > 0 THEN
1541 OE_DEBUG_PUB.Add('Finished Get_Config_Effective_Date.',3);
1542 oe_debug_pub.add('Old behav:'||l_old_behavior,3);
1543 oe_debug_pub.add('Config eff date:'||l_config_date,3);
1544 oe_debug_pub.add('Frozen:'||l_frozen_model_bill,3);
1545 END IF;
1546
1547 IF l_old_behavior = 'N' THEN
1548 l_config_effective_date := l_config_date;
1549 l_config_model_lookup_date := l_config_effective_date;
1550 ELSE
1551 l_config_effective_date := NULL;
1552 l_config_model_lookup_date := NULL;
1553 END IF;
1554 ELSE
1555
1556 l_config_creation_date := sysdate;
1557
1558 l_config_effective_date := null;
1559 l_config_model_lookup_date := null;
1560 l_header_id := p_header_id;
1561 END IF; ------- top model null
1562
1563
1564 FOR I IN 1..p_instance_tbl.count LOOP
1565
1566
1567 l_config_tbl(I).config_hdr_id :=
1568 p_instance_tbl(I).config_instance_hdr_id;
1569 l_config_tbl(I).config_rev_nbr:=
1570 p_instance_tbl(I).config_instance_rev_number;
1571
1572 END LOOP;
1573
1574
1575 l_appl_param_rec.config_creation_date := l_config_creation_date;
1576 l_appl_param_rec.config_model_lookup_date
1577 := l_config_model_lookup_date;
1578 l_appl_param_rec.config_effective_date := l_config_effective_date;
1579 -- l_appl_param_rec.calling_application_id := fnd_profile.value('RESP_APPL_ID');
1580 l_appl_param_rec.calling_application_id := 660; ---added for bug 14161644
1581
1582
1583 IF l_debug_level > 0 THEN
1584 OE_DEBUG_PUB.Add('Config Create Date: '
1585 || l_appl_param_rec.config_creation_date,1);
1586 OE_DEBUG_PUB.Add('Config Effective Date: '
1587 || l_appl_param_rec.config_effective_date,1);
1588 OE_DEBUG_PUB.Add('Model Lookup Date: '
1589 || l_appl_param_rec.config_model_lookup_date,1);
1590 OE_DEBUG_PUB.Add('appl id: '
1591 || l_appl_param_rec.calling_application_id,1);
1592 END IF;
1593
1594 --IF p_mode = 1 THEN
1595 IF l_config_mode = 'R' THEN
1596
1597 Print_Time('Before Calling CZ_PUB.Generate_Config_trees');
1598
1599 CZ_NETWORK_API_PUB.Generate_Config_Trees
1600 ( p_api_version => 1.0
1601 ,p_config_tbl => l_config_tbl
1602 ,p_tree_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
1603 --,p_tree_copy_mode => CZ_API_PUB.G_NEW_REVISION_COPY_MODE
1604 ,p_appl_param_rec => l_appl_param_rec
1605 ,p_validation_context => CZ_API_PUB.G_INSTALLED
1606 ,x_config_model_tbl => l_config_model_tbl
1607 ,x_return_status => x_return_status
1608 ,x_msg_count => x_msg_count
1609 ,x_msg_data => x_msg_data
1610 );
1611 OE_MSG_PUB.Transfer_Msg_Stack;
1612 Print_Time('After CZ_PUB.Generate_Config_Trees:'||x_return_status);
1613
1614 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1615 IF l_debug_level >0 THEN
1616 OE_DEBUG_PUB.Add('Error in Generate_Config_Trees',2);
1617 END IF;
1618 RAISE FND_API.G_EXC_ERROR;
1619 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1620 IF l_debug_level > 0 THEN
1621 OE_DEBUG_PUB.Add('Unexpected Error in Gen_Config_trees',1);
1622 END IF;
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 END IF;
1625
1626 ---------now create model lines -----------------
1627
1628
1629 FOR I IN 1..l_config_model_tbl.count LOOP
1630 l_line_tbl(I) := OE_Order_PUB.G_MISS_LINE_REC;
1631
1632 l_line_tbl(I).header_id := l_header_id;
1633 l_line_tbl(I).inventory_item_id :=
1634 l_config_model_tbl(I).inventory_item_id;
1635 l_line_tbl(I).org_id :=
1636 l_config_model_tbl(I).organization_id;
1637 l_line_tbl(I).config_header_id :=
1638 l_config_model_tbl(I).config_hdr_id;
1639 l_line_tbl(I).config_rev_nbr :=
1640 l_config_model_tbl(I).config_rev_nbr;
1641 l_line_tbl(I).configuration_id :=
1642 l_config_model_tbl(I).config_item_id;
1643
1644 l_line_tbl(I).ordered_quantity := 1;
1645
1646 IF l_model_inv_item_id = l_line_tbl(I).inventory_item_id THEN
1647 l_line_tbl(I).operation := 'UPDATE';
1648 l_line_tbl(I).line_id := p_top_model_line_id;
1649 ELSE
1650 l_line_tbl(I).operation := 'CREATE';
1651 END IF;
1652 END LOOP;
1653
1654 IF l_debug_level > 0 THEN
1655 OE_DEBUG_PUB.Add('Before Calling OE_CONFIG_PVT.Call_Process_Order',3);
1656 oe_debug_pub.add('--------------------------------------');
1657 oe_debug_pub.add('Line_Tbl being pased to Call_Process_Order is...');
1658
1659 FOR I in 1..l_line_tbl.count LOOP
1660 oe_debug_pub.add('Row #'||I);
1661 oe_debug_pub.add('Hdr id:'||l_line_tbl(I).header_id);
1662 oe_debug_pub.add('Inv item id:'||l_line_tbl(I).inventory_item_id);
1663 oe_debug_pub.add('Org:'||l_line_tbl(I).org_id);
1664 oe_debug_pub.add('ConfigHdr id:'||l_line_tbl(I).config_header_id);
1665 oe_debug_pub.add('Config Rev:'||l_line_tbl(I).config_rev_nbr);
1666 oe_debug_pub.add('ConfID:'||l_line_tbl(I).configuration_id);
1667 oe_debug_pub.add('Ord qty:'||l_line_tbl(I).ordered_quantity);
1668 oe_debug_pub.add('LineID:'||l_line_tbl(I).line_id);
1669 oe_debug_pub.add('Oper:'||l_line_tbl(I).operation);
1670 END LOOP;
1671
1672 oe_debug_pub.add('-------------------------------------');
1673 END IF;
1674
1675 OE_CONFIG_PVT.Call_Process_Order
1676 ( p_line_tbl => l_line_tbl
1677 ,p_control_rec => l_control_rec
1678 ,p_ui_flag => 'Y'
1679 ,x_return_status => x_return_status );
1680
1681 IF l_debug_level > 0 THEN
1682 OE_DEBUG_PUB.Add('After Calling Process Order:'
1683 ||x_return_status,3);
1684 END IF;
1685
1686 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1687 IF l_debug_level > 0 THEN
1688 OE_DEBUG_PUB.Add('Error in Process Order.',2);
1689 END IF;
1690 RAISE FND_API.G_EXC_ERROR;
1691 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1692 IF l_debug_level > 0 THEN
1693 OE_DEBUG_PUB.Add('Unexpected Error in Process order.',1);
1694 END IF;
1695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1696 END IF;
1697
1698 ------------------now create child lines-------------------
1699 FOR I IN 1..l_line_tbl.count LOOP
1700
1701 IF l_debug_level > 0 THEN
1702 OE_DEBUG_PUB.Add('Before calling OE_CONFIG_PVT.Process_Config..',3);
1703 OE_DEBUG_PUB.Add('for cfg hdr ' || l_line_tbl(I).config_header_id,3);
1704 OE_DEBUG_PUB.Add('for cfg rev '|| l_line_tbl(I).config_rev_nbr,3);
1705 END IF;
1706
1707 SELECT line_id
1708 INTO l_line_tbl(I).line_id
1709 FROM oe_order_lines
1710 WHERE header_id = l_line_tbl(I).header_id
1711 AND config_header_id = l_line_tbl(I).config_header_id
1712 AND config_rev_nbr = l_line_tbl(I).config_rev_nbr;
1713
1714 MACD_SYSTEM_CALL := 'Y';
1715
1716 OE_CONFIG_PVT.Process_Config
1717 ( p_header_id => l_line_tbl(I).header_id
1718 ,p_config_hdr_id => l_line_tbl(I).config_header_id
1719 ,p_config_rev_nbr => l_line_tbl(I).config_rev_nbr
1720 ,p_top_model_line_id => l_line_tbl(I).line_id
1721 ,p_ui_flag => 'N'
1722 ,p_config_instance_tbl => l_instance_tbl
1723 ,x_change_flag => l_change_flag
1724 ,x_msg_count => x_msg_count
1725 ,x_msg_data => x_msg_data
1726 ,x_return_status => x_return_status );
1727
1728 MACD_SYSTEM_CALL := 'N';
1729
1730 IF l_debug_level > 0 THEN
1731 OE_DEBUG_PUB.Add('After Calling Process Config..'
1732 ||x_return_status,3);
1733 END IF;
1734
1735 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1736 IF l_debug_level >0 THEN
1737 OE_DEBUG_PUB.Add('Error in Process Config..',2);
1738 END IF;
1739 RAISE FND_API.G_EXC_ERROR;
1740 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1741 IF l_debug_level > 0 THEN
1742 OE_DEBUG_PUB.Add('Unexpected Error in Process Config..',1);
1743 END IF;
1744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1745 END IF;
1746
1747 END LOOP;
1748 Print_Time ('Exiting OE_CONFIG_TSO_PVT.populate_tso_order_lines..');
1749 RETURN;
1750
1751 END IF; --ending l_Config_mode = r
1752
1753 IF l_config_mode = 'A' THEN
1754
1755 IF l_debug_level > 0 THEN
1756 OE_DEBUG_PUB.Add('Start Add to container.config mode=A',3);
1757 END IF;
1758
1759 BEGIN
1760
1761 SELECT config_header_id
1762 ,config_rev_nbr
1763 ,inventory_item_id
1764 INTO l_config_header_id
1765 ,l_config_rev_nbr
1766 ,l_inventory_item_id
1767 FROM oe_order_lines
1768 WHERE line_id = p_top_model_line_id;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 IF l_debug_level > 0 THEN
1773 OE_DEBUG_PUB.Add('Error during select errmsg:'||sqlerrm,1);
1774 END IF;
1775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1776
1777 END;
1778
1779 Print_Time('Before Calling CZ_PUB.Add_To_Config_Tree:');
1780
1781 CZ_NETWORK_API_PUB.Add_To_Config_Tree
1782 ( p_api_version => 1.0
1783 ,p_inventory_item_id => l_inventory_item_id
1784 ,p_organization_id => OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
1785 ,p_config_hdr_id => l_config_header_id
1786 ,p_config_rev_nbr => l_config_rev_nbr
1787 ,p_instance_tbl => l_config_tbl
1788 --,p_tree_copy_mode => CZ_API_PUB.G_NEW_REVISION_COPY_MODE
1789 ,p_tree_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
1790 ,p_appl_param_rec => l_appl_param_rec
1791 ,p_validation_context => CZ_API_PUB.G_INSTALLED
1792 ,x_config_model_rec => l_config_model_rec
1793 ,x_return_status => x_return_status
1794 ,x_msg_count => x_msg_count
1795 ,x_msg_data => x_msg_data
1796 );
1797
1798 Print_Time('After CZ_PUB.Add_To_Config_Tree:'||x_return_status);
1799
1800 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1801 IF l_debug_level > 0 THEN
1802 OE_DEBUG_PUB.Add ('Error in Add_to_config_tree',2);
1803 END IF;
1804 RAISE FND_API.G_EXC_ERROR;
1805 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1806 IF l_debug_level > 0 THEN
1807 OE_DEBUG_PUB.Add ('Unexpected Error in Add_to_config_tree',1);
1808 END IF;
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 END IF;
1811
1812 IF l_debug_level > 0 THEN
1813 OE_DEBUG_PUB.Add('HeaderID:'||l_config_model_rec.config_hdr_id,3);
1814 OE_DEBUG_PUB.Add('Rev Number:'||l_config_model_rec.config_rev_nbr,3);
1815 OE_DEBUG_PUB.Add('Before Calling Process_Config',3);
1816 END IF;
1817
1818 MACD_SYSTEM_CALL := 'Y';
1819 OE_CONFIG_PVT.Process_Config
1820 ( p_header_id => l_header_id
1821 ,p_config_hdr_id => l_config_model_rec.config_hdr_id
1822 ,p_config_rev_nbr => l_config_model_rec.config_rev_nbr
1823 ,p_top_model_line_id => p_top_model_line_id
1824 ,p_ui_flag => 'Y'
1825 ,p_config_instance_tbl => l_instance_tbl
1826 ,x_change_flag => l_change_flag
1827 ,x_msg_count => x_msg_count
1828 ,x_msg_data => x_msg_data
1829 ,x_return_status => x_return_status
1830 );
1831 MACD_SYSTEM_CALL := 'N';
1832
1833 IF l_debug_level > 0 THEN
1834 OE_DEBUG_PUB.Add('After Process_Config:'||x_return_status,3);
1835 END IF;
1836
1837 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1838 IF l_debug_level > 0 THEN
1839 OE_DEBUG_PUB.Add('Error in Process_Config',2);
1840 END IF;
1841 RAISE FND_API.G_EXC_ERROR;
1842 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1843 IF l_debug_level > 0 THEN
1844 OE_DEBUG_PUB.Add('Unexpected Error in Process_Config',1);
1845 END IF;
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847 END IF;
1848
1849 END IF; --if l_config_mode = a
1850
1851 OE_MSG_PUB.Count_And_Get
1852 ( p_count => x_msg_count
1853 ,p_data => x_msg_data );
1854
1855 Print_Time ('Exiting OE_CONFIG_TSO_PVT.populate_tso_order_lines...');
1856
1857 EXCEPTION
1858
1859 WHEN FND_API.G_EXC_ERROR THEN
1860 IF l_debug_level > 0 THEN
1861 OE_DEBUG_PUB.Add('Expected Error in populate_tso_order_lines:'
1862 ||sqlerrm, 2);
1863 END IF;
1864
1865 oe_debug_pub.add('RMV: 2 Msg count:'||x_msg_count);
1866 OE_MSG_PUB.Count_And_Get
1867 ( p_count => x_msg_count
1868 ,p_data => x_msg_data );
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870
1871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1872 IF l_debug_level > 0 THEN
1873 OE_DEBUG_PUB.Add('Unexpected Error in populate_tso_order_lines'
1874 ||sqlerrm, 1);
1875 END IF;
1876
1877 x_msg_count := OE_MSG_PUB.COUNT_MSG;
1878 IF l_debug_level > 0 THEN
1879 oe_debug_pub.add('RMV: 3 Msg count:'||x_msg_count);
1880 END IF;
1881
1882 OE_MSG_PUB.Count_And_Get
1883 ( p_count => x_msg_count
1884 ,p_data => x_msg_data );
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886
1887 WHEN OTHERS THEN
1888
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 IF l_debug_level > 0 THEN
1891 oe_debug_pub.add('RMV: 4 Msg count: '|| sqlerrm );
1892 END IF;
1893
1894 x_msg_count := OE_MSG_PUB.COUNT_MSG;
1895 FOR I in 1..x_msg_count LOOP
1896 x_msg_data := OE_MSG_PUB.Get(I,'F');
1897 IF l_debug_level > 0 THEN
1898 oe_debug_pub.add('Messages from Configurator...');
1899 END IF;
1900 END LOOP;
1901
1902 OE_MSG_PUB.Count_And_Get
1903 ( p_count => x_msg_count
1904 ,p_data => x_msg_data );
1905
1906 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1907 OE_MSG_PUB.Add_Exc_Msg
1908 ( G_PKG_NAME
1909 ,'populate_tso_order_lines'
1910 );
1911 END IF;
1912
1913 END populate_tso_order_lines;
1914
1915
1916 Procedure Process_MACD_Order
1917 (p_api_version_number IN NUMBER,
1918 p_caller IN VARCHAR2,
1919 p_x_header_id IN OUT NOCOPY NUMBER,
1920 p_sold_to_org_id IN NUMBER,
1921 p_MACD_Action IN VARCHAR2,
1922 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
1923 p_Instance_Tbl IN csi_datastructures_pub.instance_cz_tbl,
1924 p_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl,
1925 x_container_line_id OUT NOCOPY NUMBER,
1926 x_number_of_containers OUT NOCOPY NUMBER,
1927 x_return_status OUT NOCOPY VARCHAR2,
1928 x_msg_count OUT NOCOPY VARCHAR2,
1929 x_msg_data OUT NOCOPY VARCHAR2)
1930 IS
1931
1932 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1933 l_perform_action BOOLEAN;
1934 l_header_id NUMBER;
1935 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
1936
1937 CURSOR Models is
1938 SELECT line_id
1939 FROM oe_order_lines_all
1940 WHERE header_id = p_x_header_id
1941 AND open_flag = 'Y'
1942 AND top_model_line_id = line_id
1943 AND ATO_LINE_ID IS NULL;
1944
1945 BEGIN
1946
1947 IF l_debug_level > 0 THEN
1948 oe_debug_pub.add('Entering oe_config_tso_pvt.Process_MACD_Order',1);
1949 oe_debug_pub.add('p_x_header_id : ' || p_x_header_id,2);
1950 oe_debug_pub.add('p_sold_to_org_id : ' || p_sold_to_org_id,2);
1951 oe_debug_pub.add('p_MACD_Action : ' || p_MACD_Action,2);
1952 oe_debug_pub.add('p_x_line_tbl : ' || p_x_line_tbl.count,2);
1953 oe_debug_pub.add('p_Instance_Tbl : ' || p_Instance_Tbl.count,2);
1954 oe_debug_pub.add('p_Extended_Attrib_Tbl : ' || p_Extended_Attrib_Tbl.count,2);
1955 END IF;
1956 -- All validations will be performed here.
1957
1958 x_return_status := FND_API.G_RET_STS_SUCCESS;
1959
1960 IF p_caller = 'P' THEN
1961
1962 -- p_header_id and p_sold_to_org_id should be mutually exclusive. (Not required for the group call)
1963
1964 IF (p_x_header_id is null
1965 AND p_sold_to_org_id is null)
1966 OR (p_x_header_id is not null
1967 AND p_sold_to_org_id is not null) THEN
1968 -- Set retun status and raise an error.
1969 -- Message should be seeded for this.
1970
1971 FND_MESSAGE.Set_Name('ONT','ONT_TSO_HEAD_CUST_MISSING');
1972 x_return_status := FND_API.G_RET_STS_ERROR;
1973 oe_debug_pub.add('Unable to process since both header and customer is null',1);
1974 END IF;
1975
1976 -- p_instance and p_x_line table should be exclusive. (not required for the group call)
1977
1978 IF (p_instance_tbl.count > 0
1979 AND p_x_line_tbl.count > 0)
1980 OR (p_instance_tbl.count = 0
1981 AND p_x_line_tbl.count = 0) THEN
1982
1983 --Set return status and raise an error
1984 --Message should be seeded for this.
1985 -- If no data is passed, raise an error and set the return status.
1986
1987 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INS_ORD_MISSING');
1988 oe_debug_pub.add('Unable to process since both line and instance table are populated',1);
1989 x_return_status := FND_API.G_RET_STS_ERROR;
1990 END IF;
1991
1992
1993 -- All selected instances should belong to same sold_to
1994 -- Item instance should be passed only once in the instance table (Not required this call CZ can handle this scenario)
1995 -- IB owner should be same on all the instances.
1996 -- Fail the call, when different action is passed in p_instance/line table than the p_macd_action.
1997
1998 If p_instance_tbl.count > 1 THEN
1999
2000 For I in 2..p_instance_tbl.count LOOP
2001
2002 IF p_instance_tbl(1).sold_to_org_id <> p_instance_tbl(I).sold_to_org_id
2003 OR p_instance_tbl(1).IB_OWNER <> p_instance_tbl(I).IB_OWNER
2004 OR nvl(p_instance_tbl(I).action,p_macd_action) <> nvl(p_macd_action, p_instance_tbl(I).action)
2005 THEN
2006
2007 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2008 x_return_status := FND_API.G_RET_STS_ERROR;
2009 oe_debug_pub.add('Invalid Datea 2',3);
2010 -- Invalid data is passed, do not proceed further. Raise error.
2011 ELSE
2012
2013 IF p_instance_tbl(I).action is not null then
2014 l_perform_action := TRUE;
2015 END IF; -- action
2016 END IF;
2017 END LOOP;
2018
2019
2020 If p_instance_tbl(1).action is not null then
2021 L_perform_action := TRUE;
2022 END IF; -- action
2023 IF nvl(p_instance_tbl(1).action,p_macd_action) <> nvl(p_macd_action, p_instance_tbl(1).action) THEN
2024
2025 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2026 x_return_status := FND_API.G_RET_STS_ERROR;
2027 oe_debug_pub.add('Invalid Datea 3',3);
2028 Raise FND_API.G_EXC_ERROR;
2029 END IF;
2030
2031 End IF; -- count
2032
2033 -- The following code is for validating line record data.
2034 IF p_x_line_tbl.count > 0 THEN
2035
2036 For I in 1..p_x_line_tbl.count LOOP
2037
2038 IF nvl(p_x_line_tbl(I).operation,p_macd_action) <> nvl(p_macd_action, p_x_line_tbl(I).operation)
2039 THEN
2040 -- Invalid data is passed, do not proceed further. Raise error.
2041 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2042 x_return_status := FND_API.G_RET_STS_ERROR;
2043 oe_debug_pub.add('Invalid Datea 4',3);
2044 Raise FND_API.G_EXC_ERROR;
2045 ELSE
2046
2047 IF p_x_line_tbl(I).operation is not null then
2048 L_perform_action := TRUE;
2049 END IF; -- action
2050
2051 END IF;
2052 END LOOP;
2053
2054 End IF; -- Table count
2055 END IF; -- Public Validation.
2056
2057
2058 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2059 oe_debug_pub.add('Unable to process due to error',2);
2060 Raise FND_API.G_EXC_ERROR;
2061 END IF;
2062 IF l_debug_level > 0 THEN
2063 oe_debug_pub.add('Main Logic Starts from here',2);
2064 END IF;
2065 -- If p_sold_to_org_id is passed first create an order using the sold to and add the instances passed to the order.
2066 -- Procedure create_header will be introduced to create header record.*/
2067
2068 IF p_sold_to_org_id is not null THEN
2069
2070 -- If the p_sold_to is passed we will call the below mentioned new API to create header record first and then call CZ API's
2071 -- to create container models and it's child lines.
2072
2073 IF l_debug_level > 0 THEN
2074 oe_debug_pub.add('Before calling Create_header',2);
2075 END IF;
2076 Create_header(p_sold_to_org_id => p_sold_to_org_id
2077 ,x_header_id => p_x_header_id
2078 ,x_return_status => x_return_status
2079 ,x_msg_count => x_msg_count
2080 ,x_msg_data => x_msg_data);
2081 IF l_debug_level > 0 THEN
2082 oe_debug_pub.add('After calling Create_header::' || p_x_header_id ||'::' || x_return_status,2);
2083 END IF;
2084
2085 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2086 IF l_debug_level > 0 THEN
2087 oe_debug_pub.add('Unexpected error in creating Order Header for sold to: ' || p_sold_to_org_id,2);
2088 END IF;
2089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2090 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2091 IF l_debug_level > 0 THEN
2092 oe_debug_pub.add('Expected error in creating Order Header for sold to: ' || p_sold_to_org_id,2);
2093 END IF;
2094 RAISE FND_API.G_EXC_ERROR;
2095 END IF;
2096
2097
2098 -- The procedure populate_tso_order_lines will take care of creating model lines and child lines with the given instance table.
2099 -- We can continue to call this API in this case as the order is getting created in this call.
2100
2101 -- The following API will call generate_config_tree procedure to generate the container models. OM creates the models first and then
2102 -- creates the child lines by looking at the cz_config_details_v.
2103
2104 -- populate_tso_order_lines procedure accepts the instance record declared in package oe_install_base_util. But we have mentioned in the FDD to declare a new instance
2105 -- record. We need evaluate the need for the new record. If the new record is mandatory then the data needs to be populated into local instance table which is based
2106 -- on the oe_install_base_util's record structure.
2107
2108 IF l_debug_level > 0 THEN
2109 oe_debug_pub.add('Before calling populate_tso_order_lines ',2);
2110 END IF;
2111
2112 IF p_Instance_Tbl.count > 0 THEN
2113
2114 oe_config_tso_pvt.populate_tso_order_lines(
2115 p_header_id => p_x_header_id,
2116 p_top_model_line_id => null,
2117 p_instance_tbl => p_instance_tbl,
2118 p_mode => 1,
2119 x_msg_count => x_msg_count,
2120 x_msg_data => x_msg_data,
2121 x_return_status => x_return_status);
2122
2123 IF l_debug_level > 0 THEN
2124 oe_debug_pub.add('After calling populate_tso_order_lines ' || x_return_status,2);
2125 END IF;
2126
2127 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2129 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2130 RAISE FND_API.G_EXC_ERROR;
2131 END IF;
2132 END IF;
2133
2134 ELSIF p_x_header_id is not null THEN
2135
2136 /* If the p_header_id is passed it means that the header already exists in the system and the newely passed data should be inserted into existing order.
2137
2138 We cannot use the procedure populate_tso_order_lines as that procedure creates container model for the newly passed instances,
2139 but actually the container may already exists in that order. We need to have the logic to identify the existance of the container
2140 model for the passed in instance, if the container exists then the instances should be added to the same or else create the new
2141 container. For example, container "A" was fulfilled and that created instances I1 to I10. First time if the user creates the
2142 order with instance I1, system should create the container "A" and added the I1 to the same. Later if user picks I4 from IB,
2143 system should add the I4 to the existing container model instead of creating a new vcontainer recored in that order.
2144
2145 We will create an API named create_TSO_order_lines procedure to add the lines into existing order.*/
2146
2147 IF l_debug_level > 0 THEN
2148 oe_debug_pub.add('Before calling oe_config_tso_pvt.create_tso_order_lines ',2);
2149 END IF;
2150
2151 oe_config_tso_pvt.create_tso_order_lines(
2152 p_header_id => p_x_header_id,
2153 p_top_model_line_id => null,
2154 p_instance_tbl => p_instance_tbl,
2155 x_msg_count => x_msg_count,
2156 x_msg_data => x_msg_data,
2157 x_return_status => x_return_status);
2158
2159 IF l_debug_level > 0 THEN
2160 oe_debug_pub.add('After calling oe_config_tso_pvt.create_tso_order_lines ' || x_return_status , 2);
2161 END IF;
2162
2163
2164 END IF; -- Sold to org.
2165
2166 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2169 RAISE FND_API.G_EXC_ERROR;
2170 END IF;
2171 -- We are done with TSO line creation logic. Processing logic follows.
2172 IF p_macd_action is not null
2173 OR l_perform_action THEN
2174
2175 /* If the caller passes an action we need to validate the action. If the lines are created in this call,
2176 the logic would be based on the p_instance_tbl or else the logic would be based on the lines_tbl passed by the user.
2177 Or the process will be based on the lines table.*/
2178
2179 IF l_debug_level > 0 THEN
2180 oe_debug_pub.add('Before calling populate_tso_order_lines ',2);
2181 END IF;
2182
2183 Populate_MACD_action
2184 ( p_header_id => p_x_header_id
2185 ,p_instance_tbl => p_instance_tbl
2186 ,p_x_line_tbl => p_x_line_tbl
2187 ,p_Extended_Attrib_Tbl => p_Extended_Attrib_Tbl
2188 ,p_macd_action => p_macd_action
2189 ,x_msg_data => x_msg_data
2190 ,x_msg_count => x_msg_count
2191 ,x_return_status => x_return_status);
2192
2193 IF l_debug_level > 0 THEN
2194 oe_debug_pub.add('After calling Populate_MACD_action ' || x_return_status,2);
2195
2196 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF;
2201 END IF; -- action
2202
2203 END IF;
2204 -- Populate out variables
2205
2206 FOR I IN Models LOOP
2207
2208 Oe_debug_pub.add('Model record ' || I.line_id,2);
2209 IF x_container_line_id IS NULL THEN
2210 x_container_line_id := I.line_id;
2211 END IF;
2212
2213 x_number_of_containers := nvl(x_number_of_containers,0) + 1;
2214
2215 END LOOP;
2216
2217 IF p_x_line_tbl.count = 0 THEN
2218
2219 oe_line_util.Query_Rows
2220 (p_header_id => p_x_header_id
2221 ,x_line_tbl => p_x_line_tbl);
2222
2223 END IF;
2224 -- Get message count and data
2225
2226 oe_msg_pub.count_and_get
2227 ( p_count => x_msg_count
2228 , p_data => x_msg_data);
2229
2230 IF l_debug_level > 0 THEN
2231 Oe_debug_pub.add(' Out Values from process macd',2);
2232 Oe_debug_pub.add('header_id : ' || p_x_header_id,2);
2233 Oe_debug_pub.add('Line count : ' || p_x_line_tbl.count,2);
2234 Oe_debug_pub.add('x_container_line_id : ' || x_container_line_id,2);
2235 Oe_debug_pub.add('x_number_of_container : ' || x_number_of_containers,2);
2236 Oe_debug_pub.add('Return Status : ' || x_return_status,2);
2237 Oe_debug_pub.add('Message count : ' || x_msg_count,2);
2238 Oe_debug_pub.add('Message Data : ' || x_msg_data,2);
2239
2240 oe_debug_pub.add('Exiting oe_config_tso_pvt.Process_MACD_Order ' || x_return_status,1);
2241 END IF;
2242 EXCEPTION
2243
2244 WHEN FND_API.G_EXC_ERROR THEN
2245 oe_msg_pub.count_and_get
2246 ( p_count => x_msg_count
2247 , p_data => x_msg_data);
2248
2249 IF l_debug_level > 0 THEN
2250 OE_DEBUG_PUB.Add('Expected Error in Process_MACD_Order:'
2251 ||sqlerrm,3);
2252 END IF;
2253 x_return_status := FND_API.G_RET_STS_ERROR;
2254
2255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2256 oe_msg_pub.count_and_get
2257 ( p_count => x_msg_count
2258 , p_data => x_msg_data);
2259
2260 IF l_debug_level > 0 THEN
2261 OE_DEBUG_PUB.Add('Unexpected Error Process_MACD_Order:'
2262 ||sqlerrm,3);
2263 END IF;
2264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2265
2266 WHEN OTHERS THEN
2267 oe_msg_pub.count_and_get
2268 ( p_count => x_msg_count
2269 , p_data => x_msg_data);
2270
2271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2272 IF l_debug_level > 0 THEN
2273 OE_DEBUG_PUB.Add('Other error in Process_MACD_Order:'
2274 ||sqlerrm,1);
2275 END IF;
2276 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2277 OE_MSG_PUB.Add_Exc_Msg
2278 ( G_PKG_NAME
2279 ,'Process_MACD_Order' );
2280 END IF;
2281
2282 END Process_MACD_Order;
2283
2284 /* The logic of the create_header is given below. This API will accept sold to as a in parameter and creates a header
2285 using the same by calling headers procedure.*/
2286
2287 Procedure Create_header
2288 (p_sold_to_org_id IN NUMBER
2289 ,x_header_id OUT NOCOPY NUMBER
2290 ,x_return_status OUT NOCOPY VARCHAR2
2291 ,x_msg_count OUT NOCOPY NUMBER
2292 ,x_msg_data OUT NOCOPY VARCHAR2)
2293 IS
2294 -- Declare following local variables which are in parameters to the header procedure.
2295 l_header_rec OE_Order_PUB.Header_Rec_Type;
2296 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
2297 l_control_rec OE_GLOBALS.Control_Rec_Type;
2298
2299 --
2300 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2301 --
2302 BEGIN
2303
2304 IF l_debug_level > 0 THEN
2305 oe_debug_pub.add( 'ENTERING OE_TSO_CONFIG_PVT.Create_Header' , 1 ) ;
2306 END IF;
2307 x_return_status := FND_API.G_RET_STS_SUCCESS;
2308
2309 -- Use the default control record to call header procedure.
2310 -- Populate the header record with the input data and an operation.
2311
2312 l_old_header_rec :=OE_ORDER_PUB.G_MISS_HEADER_REC;
2313 l_header_rec :=OE_ORDER_PUB.G_MISS_HEADER_REC;
2314 l_header_rec.sold_to_org_id := p_sold_to_org_id;
2315
2316 -- Set Operation to Create
2317
2318 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2319
2320 -- Call Oe_Order_Pvt.Header
2321 -- Add debug messages.
2322
2323 Oe_Order_Pvt.Header
2324 ( p_validation_level =>FND_API.G_VALID_LEVEL_NONE
2325 , p_init_msg_list => FND_API.G_TRUE
2326 , p_control_rec =>l_control_rec
2327 , p_x_header_rec =>l_header_rec
2328 , p_x_old_header_rec =>l_old_header_rec
2329 , x_return_status =>x_return_status );
2330
2331 -- Handle the return status.
2332
2333 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2335 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2336 RAISE FND_API.G_EXC_ERROR;
2337 END IF;
2338
2339 -- Load OUT parameters.
2340
2341 X_header_id := l_header_rec.header_id;
2342
2343 -- Get message count and data
2344
2345
2346 OE_MSG_PUB.Count_And_Get
2347 ( p_count => x_msg_count
2348 , p_data => x_msg_data);
2349
2350 EXCEPTION
2351
2352 WHEN FND_API.G_EXC_ERROR THEN
2353 IF l_debug_level > 0 THEN
2354 OE_DEBUG_PUB.Add('Expected Error in Create_Header:'
2355 ||sqlerrm,3);
2356 END IF;
2357 x_return_status := FND_API.G_RET_STS_ERROR;
2358
2359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2360 IF l_debug_level > 0 THEN
2361 OE_DEBUG_PUB.Add('Unexpected Error Create_Header:'
2362 ||sqlerrm,3);
2363 END IF;
2364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2365
2366 WHEN OTHERS THEN
2367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2368 IF l_debug_level > 0 THEN
2369 OE_DEBUG_PUB.Add('Other error in Create_Header:'
2370 ||sqlerrm,1);
2371 END IF;
2372 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2373 OE_MSG_PUB.Add_Exc_Msg
2374 ( G_PKG_NAME
2375 ,'Create_Header' );
2376 END IF;
2377
2378 END Create_Header;
2379
2380
2381
2382 --The logic of this API would be very similar to the populate_tso_order_lines.
2383 --The main difference here would be we will look for the existance of the container model
2384 --for each instance and if container exists then we will add the instance to the exisiting container
2385 --model rather creating new container model. We will check for the existance of the container model by
2386 --using the component code passed in the instance table.
2387
2388 PROCEDURE Create_TSO_Order_Lines
2389 ( p_header_id IN NUMBER
2390 , p_top_model_line_id IN NUMBER
2391 , p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
2392 , x_msg_data OUT NOCOPY VARCHAR2
2393 , x_msg_count OUT NOCOPY NUMBER
2394 , x_return_status OUT NOCOPY VARCHAR2
2395 )
2396 IS
2397
2398
2399 l_model_inv_item_id NUMBER;
2400 l_config_header_id NUMBER;
2401 l_config_rev_nbr NUMBER;
2402 l_top_model_line_id NUMBER;
2403 J NUMBER;
2404 I NUMBER;
2405
2406 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2407 l_parent_exists_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2408 l_no_parent_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2409 l_Temp_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2410
2411 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2412 Begin
2413
2414 --IF the ship_to_site_use_id and bill_to_site_use_id has NULL value
2415 --for all records returned by IB, we remember this and pass the
2416 --G_CONFIG_INTSTANCE_TBL to Process_Config to improve performance
2417
2418 oe_debug_pub.add('Entering Procedure Create_TSO_Order_Lines: ' || l_instance_tbl.count,1);
2419
2420
2421 l_instance_tbl := p_instance_tbl;
2422 I := l_instance_tbl.FIRST;
2423
2424 While I IS NOT NULL LOOP
2425 BEGIN
2426
2427 -- Code should be added to identify the existance of the container model record.
2428 -- Loop through the table, identify the container model item from cz tables and check the existance of the record in
2429 -- OM table from the given header_id.
2430
2431 IS_container_present(
2432 p_header_id => p_header_id
2433 ,p_config_instance_hdr_id => l_instance_tbl(I).config_instance_hdr_id
2434 ,p_config_instance_rev_number => l_instance_tbl(I).config_instance_rev_number
2435 ,x_top_model_line_id => l_top_model_line_id);
2436
2437 oe_debug_pub.add('After calling IS_container_present: ' || l_top_model_line_id,2);
2438
2439 -- If the model exists then transfer the instance record to l_parent_exists_instance_tbl and
2440 -- delete the instance record from l_instance_tbl. Also loop through the l_instance_tbl and transfer
2441 -- all the instance records that matchs the parent config_hdr and config_rev_nbr.
2442 -- Delete the records from l_instance_tbl after every transfer. Now we need to add these instances to the existing model.
2443
2444 IF l_top_model_line_id is not null THEN
2445
2446 oe_debug_pub.add('Top model is present',3);
2447 J := l_instance_tbl.NEXT(I);
2448
2449 L_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2450
2451 While J IS NOT NULL LOOP
2452 BEGIN
2453
2454 IF l_instance_tbl(J).config_instance_hdr_id = l_instance_tbl(I).config_instance_hdr_id
2455 AND l_instance_tbl(J).config_instance_rev_number = l_instance_tbl(I).config_instance_rev_number THEN
2456
2457 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) :=
2458 l_instance_tbl(J);
2459
2460 l_instance_tbl.DELETE(J);
2461
2462
2463 END IF;
2464 J := l_instance_tbl.NEXT(J);
2465 END;
2466 END LOOP;
2467 l_instance_tbl.DELETE(I);
2468
2469 oe_debug_pub.add('Before calling populate_tso_order_lines to append lines: ' ||
2470 l_parent_exists_instance_tbl.count,3);
2471 oe_config_tso_pvt.populate_tso_order_lines(
2472 p_header_id => p_header_id,
2473 p_top_model_line_id => l_top_model_line_id,
2474 p_instance_tbl => l_parent_exists_instance_tbl,
2475 p_mode => 1,
2476 x_msg_count => x_msg_count,
2477 x_msg_data => x_msg_data,
2478 x_return_status => x_return_status);
2479
2480 -- Handle the return status from the call. Raise an exception accordingly.
2481 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
2482 RAISE FND_API.G_EXC_ERROR;
2483 ELSIF x_return_status=FND_API.G_RET_STS_UNEXP_ERROR THEN
2484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2485 END IF;
2486
2487 -- Delete the parent table and also clear the top model so that we do not carry the value.
2488
2489 l_top_model_line_Id := null;
2490 l_parent_exists_instance_tbl.delete;
2491 Goto End_loop;
2492
2493 ELSE
2494 --If the model does not exists then transfer the record to l_no_paranet_instance_tbl and delete the record.
2495
2496 --commented for BUG#7376452
2497 --The table type being evaluated is l_no_parent_instance_tbl and the conter we use is of l_parent_exists_instance_tbl
2498 --so whenever we evaluate for any standard line only the last line is evaluated evrytime as the conter always remains at 0
2499
2500 --l_no_parent_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2501 l_no_parent_instance_tbl(l_no_parent_instance_tbl.count + 1) := l_instance_tbl(I); --added BUG#7376452
2502
2503 END IF; -- top model
2504
2505 <<End_Loop>>
2506
2507 I := L_instance_tbl.Next(I);
2508 END;
2509 END LOOP;
2510
2511 IF l_no_parent_instance_tbl.count >0 THEN
2512
2513 -- We will come here only if have instances without parent. Call populate_tso_order_lines to create containers and its chiled lines.
2514 oe_debug_pub.add('Before calling populate_tso_order_line to create lines: ' ||
2515 l_no_parent_instance_tbl.count,2);
2516 oe_config_tso_pvt.populate_tso_order_lines(
2517 p_header_id =>p_header_id,
2518 p_top_model_line_id => null,
2519 p_instance_tbl => l_no_parent_instance_tbl,
2520 p_mode => 1,
2521 x_msg_count =>x_msg_count,
2522 x_msg_data => x_msg_data,
2523 x_return_status => x_return_status);
2524 -- Handle the return status from the call. Raise an exception accordingly.
2525 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
2526 RAISE FND_API.G_EXC_ERROR;
2527 ELSIF x_return_status=FND_API.G_RET_STS_UNEXP_ERROR THEN
2528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2529 END IF;
2530
2531 l_no_parent_instance_tbl.delete;
2532
2533 END IF; -- L_no_parent_instance.
2534
2535 -- Get message count and data
2536 oe_debug_pub.add('Before exiting Create_TSO_Order_Lines' || x_return_status,2);
2537 OE_MSG_PUB.Count_And_Get
2538 ( p_count => x_msg_count
2539 , p_data => x_msg_data
2540 );
2541
2542 EXCEPTION
2543
2544 WHEN FND_API.G_EXC_ERROR THEN
2545 IF l_debug_level > 0 THEN
2546 OE_DEBUG_PUB.Add('Expected Error in Create_TSO_Order_Lines:'
2547 ||sqlerrm,3);
2548 END IF;
2549 x_return_status := FND_API.G_RET_STS_ERROR;
2550
2551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2552 IF l_debug_level > 0 THEN
2553 OE_DEBUG_PUB.Add('Unexpected Error Create_TSO_Order_Lines:'
2554 ||sqlerrm,3);
2555 END IF;
2556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2557
2558 WHEN OTHERS THEN
2559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2560 IF l_debug_level > 0 THEN
2561 OE_DEBUG_PUB.Add('Other error in Create_TSO_Order_Lines:'
2562 ||sqlerrm,1);
2563 END IF;
2564 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2565 OE_MSG_PUB.Add_Exc_Msg
2566 ( G_PKG_NAME
2567 ,'Create_TSO_Order_Lines' );
2568 END IF;
2569 END Create_TSO_Order_Lines;
2570
2571
2572
2573 /*Procedure Populate_MACD_action will prepare the config tables for batch validation.
2574 This API will be called when p_macd_action is passed so the action should be populated
2575 on the each line before calliing macd batch validate API. We will fetch the config details
2576 and populate the same in the config tables along with the action. We will validate the
2577 action and convert the actions to number to call CZ validate. Either instance table or
2578 line table will be passed to this table along with the extended table. We will also populate
2579 the data in cz extended table as well. */
2580
2581 PROCEDURE Populate_MACD_action(
2582 p_header_id IN NUMBER
2583 ,p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
2584 ,p_x_Line_Tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2585 ,p_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl
2586 ,p_macd_action IN VARCHAR2
2587 -- ,x_config_item_tbl OUT NOCOPY CZ_API_PUB.config_tbl_type;
2588 -- ,x_config_ext_attr_tbl OUT NOCOPY config_ext_attr_tbl_type
2589 ,x_msg_data OUT NOCOPY VARCHAR2
2590 ,x_msg_count OUT NOCOPY NUMBER
2591 ,x_return_status OUT NOCOPY VARCHAR2)
2592
2593 IS
2594
2595 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2596 l_parent_exists_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2597 l_no_parent_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2598 l_config_item_rec CZ_CF_API.config_item_rec_type;
2599 l_config_item_tbl CZ_CF_API.config_item_tbl_type;
2600 l_config_attr_rec CZ_CF_API.config_ext_attr_rec_type;
2601 l_config_attr_tbl CZ_CF_API.config_ext_attr_tbl_type;
2602 l_url VARCHAR2(100);
2603 l_init_msg VARCHAR2(2000);
2604 l_validation_type VARCHAR2(1) := CZ_API_PUB.VALIDATE_ORDER;
2605 l_config_xml_msg CZ_CF_API.CFG_OUTPUT_PIECES;
2606 l_control_rec OE_GLOBALS.Control_Rec_Type;
2607 J NUMBER;
2608 I NUMBER;
2609 l_component_code VARCHAR2(30);
2610 l_config_item_id NUMBER;
2611 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2612 L_TOP_MODEL_LINE_ID NUMBER;
2613 l_top_config_header_id NUMBER;
2614 l_top_config_rev_nbr NUMBER;
2615 l_header_id NUMBER;
2616 l_change_flag VARCHAR2(30);
2617 L_line_tbl OE_Order_Pub.Line_Tbl_Type;
2618 l_config_header_id NUMBER;
2619 l_config_rev_nbr NUMBER;
2620 l_valid_config VARCHAR2(10);
2621 l_complete_config VARCHAR2(10);
2622 l_xml_str LONG := NULL;
2623 Begin
2624
2625 --IF the ship_to_site_use_id and bill_to_site_use_id has NULL value
2626 --for all records returned by IB, we remember this and pass the
2627 --G_CONFIG_INTSTANCE_TBL to Process_Config to improve performance
2628
2629 oe_debug_pub.add('Entering Populate_macd_action procedure',1);
2630 x_return_status := FND_API.G_RET_STS_SUCCESS;
2631 IF p_instance_tbl.count > 0 THEN
2632
2633 l_instance_tbl := p_instance_tbl;
2634 I := l_instance_tbl.FIRST;
2635
2636 While I IS NOT NULL LOOP
2637 BEGIN
2638
2639 -- Code should be added to identify the existance of the container model record.
2640 -- Loop through the table, identify the container model item from cz tables
2641 -- and check the existance of the record in OM table from the given header_id.
2642
2643 IS_container_present(
2644 P_header_id => p_header_id
2645 ,p_config_instance_hdr_id => l_instance_tbl(I).config_instance_hdr_id
2646 ,p_config_instance_rev_number => l_instance_tbl(I).config_instance_rev_number
2647 ,x_top_model_line_id => l_top_model_line_id);
2648
2649 oe_debug_pub.add('After is container: ' || l_top_model_line_id,2);
2650 -- After identifying the container model, loop through the instance table and
2651 -- populate the config table for validation.
2652
2653 IF l_top_model_line_id is not null THEN
2654
2655 J := l_instance_tbl.NEXT(I);
2656
2657 L_parent_exists_instance_tbl (l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2658
2659 -- Instance record will be validated for the given action and see whether the action is
2660 -- applicable and also system finds the current config_header_id (session header id) so that
2661 -- can be passed to CZ for the batch validation.
2662
2663 oe_debug_pub.add('Before Validate Action' || x_return_status,2);
2664 Validate_action
2665 (p_top_model_line_id => l_top_model_line_id,
2666 p_instance_item_id => l_instance_tbl(I).config_instance_item_id,
2667 p_macd_action => nvl(p_macd_action,l_instance_tbl(I).action),
2668 x_config_item_id => l_config_item_id,
2669 x_component_code => l_component_code,
2670 x_return_status => x_return_status);
2671
2672 oe_debug_pub.add('After Validate Action' || x_return_status,2);
2673 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2675 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2676 RAISE FND_API.G_EXC_ERROR;
2677 END IF;
2678 l_config_item_rec.config_item_id := l_config_item_id;
2679 l_config_item_rec.component_code := l_component_code;
2680
2681 IF nvl(p_macd_action, l_instance_tbl(I).action) = 'UPDATE' THEN
2682 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
2683 Elsif nvl(p_macd_action, l_instance_tbl(I).action) = 'DELETE' THEN
2684 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2685 Elsif nvl(p_macd_action, l_instance_tbl(I).action) = 'DISCONTINUE' THEN
2686 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2687 End if;
2688
2689
2690 l_config_item_rec.instance_name := l_instance_tbl(I).instance_name;
2691 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
2692 oe_debug_pub.add('Before getting the data from P_Extended_Attrib_Tbl: ' ||
2693 P_Extended_Attrib_Tbl.count,2);
2694 For K in 1..P_Extended_Attrib_Tbl.count Loop
2695 oe_debug_pub.add('In the ext loop: ' || K,2);
2696 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = I then
2697
2698 l_config_attr_rec.config_item_id := l_config_item_id;
2699 l_config_attr_rec.component_code := l_component_code;
2700 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
2701 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
2702 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
2703 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
2704
2705 END IF; -- index xomparison.
2706
2707 END LOOP;
2708 oe_debug_pub.add('Before J loop: ' || J,2);
2709 While J IS NOT NULL
2710 LOOP
2711
2712 oe_debug_pub.add('Inside the J loop ' || J,2);
2713 IF l_instance_tbl(J).config_instance_hdr_id = l_instance_tbl(I).config_instance_hdr_id
2714 AND l_instance_tbl(J).config_instance_rev_number = l_instance_tbl(I).config_instance_rev_number THEN
2715
2716 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(J);
2717
2718 oe_debug_pub.add(' 1 Before Validate Action' || x_return_status,2);
2719 Validate_action
2720 (p_top_model_line_id => l_top_model_line_id,
2721 p_instance_item_id => l_instance_tbl(J).config_instance_item_id,
2722 p_macd_action => nvl(p_macd_action, l_instance_tbl(J).action),
2723 x_config_item_id => l_config_item_id,
2724 x_component_code => l_component_code,
2725 x_return_status => x_return_status);
2726
2727 oe_debug_pub.add(' 1 After Validate Action' || x_return_status,2);
2728 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2730 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2731 RAISE FND_API.G_EXC_ERROR;
2732 END IF;
2733 l_config_item_rec.config_item_id := l_config_item_id;
2734 l_config_item_rec.component_code := l_component_code;
2735
2736 IF nvl(p_macd_action, l_instance_tbl(j).action) = 'UPDATE' THEN
2737 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
2738 ELSIF nvl(p_macd_action, l_instance_tbl(j).action) = 'DELETE' THEN
2739 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2740 ELSIF nvl(p_macd_action, l_instance_tbl(j).action) = 'DISCONTINUE' THEN
2741 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2742 End if;
2743
2744 l_config_item_rec.instance_name := l_instance_tbl(J).instance_name;
2745 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
2746
2747
2748 -- Populate corresponding extended attributes into cz extented table.
2749 For K in 1..P_Extended_Attrib_Tbl.count Loop
2750
2751 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = J then
2752
2753 l_config_attr_rec.config_item_id := l_config_item_id;
2754 l_config_attr_rec.component_code := l_component_code;
2755 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
2756 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
2757 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
2758 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
2759
2760 END IF; -- index xomparison.
2761
2762 END Loop; -- K loop
2763
2764
2765 l_instance_tbl.DELETE(J);
2766 END IF; -- After Mai Loop
2767 J := l_instance_tbl.NEXT(J);
2768 oe_debug_pub.add('Processing next instance ' || J);
2769 END LOOP; -- J Loop
2770 l_instance_tbl.DELETE(I);
2771
2772 -- Call macd batch validate API to validate the actions for the given instances.
2773
2774 oe_debug_pub.add('Before calling the oe_config_util.Create_hdr_xml: ' || l_top_model_line_id,3);
2775
2776 oe_debug_pub.add('Before calling Create_hdr_xml',1);
2777 oe_config_util.Create_hdr_xml
2778 ( p_model_line_id => l_top_model_line_id ,
2779 x_xml_hdr => l_init_msg);
2780
2781
2782 oe_debug_pub.add('Before calling CZ_CF_API.VALIDATE',2);
2783 CZ_CF_API.VALIDATE
2784 (p_api_version => 1.0
2785 ,p_config_item_tbl => l_config_item_tbl
2786 ,p_config_ext_attr_tbl => l_config_attr_tbl
2787 ,p_url => l_url
2788 ,p_init_msg => l_init_msg
2789 ,p_validation_type => l_validation_type
2790 ,x_config_xml_msg => l_config_xml_msg
2791 ,x_return_status => x_return_status
2792 ,x_msg_count => x_msg_count
2793 ,x_msg_data => x_msg_data);
2794
2795 OE_MSG_PUB.Transfer_Msg_Stack;
2796 oe_debug_pub.add('After calling CZ_CF_API.VALIDATE: ' || x_return_status,2);
2797 oe_debug_pub.add('x_msg_data: ' || x_msg_data,2);
2798
2799 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2801 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2802 RAISE FND_API.G_EXC_ERROR;
2803 END IF;
2804 -- Handle the return status from the call. Raise an exception accordingly.
2805
2806 -- extract data from xml message.
2807
2808 IF l_debug_level > 0 THEN
2809 oe_debug_pub.add('CALLING PARSE_OUTPUT_XML: ' || l_config_xml_msg.COUNT, 2 );
2810 END IF;
2811
2812 IF (l_config_xml_msg.COUNT > 0) THEN
2813
2814 FOR xmlStr IN l_config_xml_msg.FIRST..l_config_xml_msg.LAST
2815 LOOP
2816 l_xml_str := l_xml_str||l_config_xml_msg(xmlStr);
2817 oe_debug_pub.add(' Row count ' || xmlStr,2);
2818 END LOOP;
2819
2820 l_xml_str := UPPER(l_xml_str);
2821
2822 oe_debug_pub.add(' Out Message '|| l_xml_str,2);
2823 oe_config_util.Parse_Output_xml
2824 ( p_xml => l_xml_str,
2825 p_line_id => l_top_model_line_id,
2826 x_valid_config => l_valid_config,
2827 x_complete_config => l_complete_config,
2828 x_config_header_id => l_top_config_header_id,
2829 x_config_rev_nbr => l_top_config_rev_nbr,
2830 x_return_status => x_return_status );
2831
2832 END IF;
2833 IF l_debug_level > 0 THEN
2834 oe_debug_pub.add('AFTER CALLING PARSE_XML: '||x_RETURN_STATUS , 2 );
2835 END IF;
2836
2837 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2839 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2840 RAISE FND_API.G_EXC_ERROR;
2841 END IF;
2842 -- Need to call the process_config api to synch up the order linesto onfiguration changes resulted due to the batch validation call.
2843 /*
2844 Begin
2845
2846 Select config_header_id, config_rev_nbr
2847 Into l_top_config_header_id, l_top_config_rev_nbr
2848 From oe_order_lines_all
2849 Where line_id = l_top_model_line_id;
2850
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 oe_debug_pub.add(' Line SELECT: '|| SQLERRM , 1 ) ;
2854 RAISE FND_API.G_EXC_ERROR;
2855 -- Exception handler
2856
2857 End;
2858 */
2859 oe_debug_pub.add('Before calling process config',2);
2860 OE_CONFIG_PVT.Process_Config
2861 (p_header_id => l_header_id
2862 ,p_config_hdr_id => l_top_config_header_id
2863 ,p_config_rev_nbr => l_top_config_rev_nbr
2864 ,p_top_model_line_id => l_top_model_line_id
2865 ,p_ui_flag => 'Y'
2866 ,x_change_flag => l_change_flag
2867 ,x_msg_count => x_msg_count
2868 ,x_msg_data => x_msg_data
2869 ,x_return_status => x_return_status);
2870
2871 oe_debug_pub.add('After calling Process Config: ' || x_return_status,2);
2872 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2874 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2875 RAISE FND_API.G_EXC_ERROR;
2876 END IF;
2877 -- Delete the parent table and also clear the top model so that we do not carry the value.
2878
2879 l_top_model_line_Id := null;
2880 l_parent_exists_instance_tbl.delete;
2881 Goto End_loop;
2882
2883 END IF; -- top model
2884
2885
2886 <<End_Loop>>
2887 I := L_instance_tbl.Next(I);
2888 END;
2889 END LOOP;
2890
2891
2892 ELSE -- Process the data passed through the lines table.
2893
2894 --Line table logic
2895
2896 L_line_tbl := p_x_line_tbl;
2897
2898 -- Add the logic to call process order so that all the updates are done before processing
2899 -- any action sent along with the lines table. We will loop through the lines table and adjust
2900 -- the operation so that process order can take the action on those. We will not pass the discontinue
2901 -- operation to process order as discontinue is not a valid operation for process order.
2902
2903 For M in 1..l_line_tbl.count LOOP
2904
2905
2906 IF nvl(p_macd_action, l_line_tbl(M).operation) in ('DELETE', 'DISCONTINUE') THEN
2907
2908 -- If the config details are passed on the line record copy the same to local variables.
2909 -- Or else query the config details for the lines table.
2910
2911 l_config_header_id := l_line_tbl(M).config_header_id;
2912 l_config_rev_nbr := l_line_tbl(M).config_rev_nbr;
2913 l_config_item_id := l_line_tbl(M).configuration_id;
2914
2915 IF l_config_header_id is null
2916 OR l_config_rev_nbr is null
2917 OR L_config_item_id is null THEN
2918
2919 Begin
2920
2921 Select config_header_id, config_rev_nbr, configuration_id
2922 Into l_config_header_id, l_config_rev_nbr, l_config_item_id
2923 From oe_order_lines_all
2924 Where line_id = l_line_tbl(M).line_id;
2925
2926 L_line_tbl(M).config_header_id := l_config_header_id;
2927 L_line_tbl(M).config_rev_nbr := l_config_rev_nbr;
2928 L_line_tbl(M).configuration_id := l_config_item_id;
2929
2930
2931 Exception
2932 WHEN OTHERS THEN
2933
2934 Null;
2935
2936 End;
2937
2938 END IF;
2939 Validate_line_action
2940 (p_line_id => l_line_tbl(M).line_id,
2941 p_config_header_id => l_config_header_id,
2942 p_config_rev_nbr => l_config_rev_nbr,
2943 P_config_item_id => l_config_item_id,
2944 P_macd_action => nvl(p_macd_action, l_line_tbl(M).operation),
2945 x_return_status => x_return_status);
2946
2947 -- Handle the return status and change the operation accordingly.
2948
2949 IF x_return_status = 'ERRROR' then
2950
2951 l_line_tbl(M).operation := 'NONE';
2952
2953 ELSIF L_line_tbl(M).operation = 'DISCONTINUE' Then
2954
2955 l_line_tbl(M).operation := 'NONE';
2956
2957 END IF;
2958
2959
2960 END IF; -- Delete;
2961 END LOOP;
2962
2963
2964 -- After the action validation we will call process order to update any changes passed in.
2965
2966 OE_CONFIG_PVT.Call_Process_Order
2967 (p_line_tbl => l_line_tbl
2968 ,p_control_rec => l_control_rec
2969 ,x_return_status => x_return_status);
2970
2971
2972 -- Handle the exception of the process order here. If there were no exception then process the macd action.
2973
2974 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2976 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2977 RAISE FND_API.G_EXC_ERROR;
2978 END IF;
2979
2980 I := l_line_tbl.FIRST;
2981
2982 While I IS NOT NULL
2983 LOOP
2984
2985 -- Code should be added to identify the existance of the container model record.
2986 -- Loop through the table, identify the container model item from cz tables
2987 -- and check the existance of the record in OM table from the given header_id.
2988
2989 IS_container_present(
2990 P_header_id => p_header_id
2991 ,p_config_instance_hdr_id => l_line_tbl(I).config_header_id
2992 ,p_config_instance_rev_number => l_line_tbl(I).config_rev_nbr
2993 ,x_top_model_line_id => l_top_model_line_id);
2994
2995 -- After identifying the container model, loop through the instance table
2996 -- and populate the config table for validation.
2997
2998 IF l_top_model_line_id is not null THEN
2999
3000 J := l_instance_tbl.NEXT(I);
3001
3002 l_parent_exists_instance_tbl (l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
3003
3004 -- base line validation should be performed for the lines.
3005
3006 Validate_line_action
3007 (p_line_id => l_line_tbl(I).line_id,
3008 P_config_header_id => l_line_tbl(I).config_header_id,
3009 P_config_rev_nbr => l_line_tbl(I).config_rev_nbr,
3010 P_config_item_id => l_line_tbl(I).configuration_id,
3011 P_macd_action => nvl(p_macd_action, l_line_tbl(I).operation),
3012 X_return_status => x_return_status);
3013
3014 oe_debug_pub.add('3 After calling Validate_line_action' || x_return_status,2);
3015 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3017 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3018 RAISE FND_API.G_EXC_ERROR;
3019 END IF;
3020 -- Handle exceptions.
3021
3022 l_config_item_rec.config_item_id := l_line_tbl(I).configuration_id;
3023 l_config_item_rec.component_code := l_line_tbl(I).component_code;
3024
3025 IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
3026 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
3027 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
3028 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3029 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DISCONTINUE' THEN
3030 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3031 END IF;
3032
3033 --l_config_item_rec.instance_name := l_line_tbl(I).instance_name;
3034 --l_config_item_rec.sequence_nbr := l_line_tbl(I).config_input_sequence;
3035 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
3036
3037 For K in 1..P_Extended_Attrib_Tbl.count Loop
3038
3039 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = I then
3040
3041 l_config_attr_rec.config_item_id := l_config_item_id;
3042 l_config_attr_rec.component_code := l_component_code;
3043 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
3044 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
3045 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
3046 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
3047
3048 END IF; --index xomparison.
3049
3050 END LOOP;
3051 While J IS NOT NULL
3052 LOOP
3053
3054 IF l_instance_tbl(J).config_instance_hdr_id = l_line_tbl(I).config_header_id
3055 AND l_line_tbl(J).config_rev_nbr = l_instance_tbl(I).config_instance_rev_number THEN
3056
3057 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) :=
3058 l_instance_tbl(J);
3059
3060 Validate_line_action
3061 (p_line_id => l_line_tbl(j).line_id,
3062 p_config_header_id => l_line_tbl(j).config_header_id,
3063 p_config_rev_nbr => l_line_tbl(j).config_rev_nbr,
3064 p_config_item_id => l_line_tbl(j).configuration_id,
3065 p_macd_action => nvl(p_macd_action,l_line_tbl(j).operation),
3066 x_return_status => x_return_status);
3067 oe_debug_pub.add('4 After calling Validate_line_action' || x_return_status,2);
3068 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3070 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3071 RAISE FND_API.G_EXC_ERROR;
3072 END IF;
3073
3074 l_config_item_rec.config_item_id := l_line_tbl(j).configuration_id;
3075 l_config_item_rec.component_code := L_line_tbl(J).component_code;
3076 IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
3077 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
3078 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
3079 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3080 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DISCONTINUE' THEN
3081 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3082 End if;
3083 l_config_item_rec.instance_name := l_instance_tbl(J).instance_name;
3084 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
3085
3086
3087 -- Populate corresponding extended attributes into cz extented table.
3088 For K in 1..P_Extended_Attrib_Tbl.count Loop
3089
3090 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = J then
3091
3092 l_config_attr_rec.config_item_id := l_config_item_id;
3093 l_config_attr_rec.component_code := l_component_code;
3094 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
3095 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
3096 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
3097 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
3098
3099 END IF; -- index xomparison.
3100
3101 END Loop;
3102
3103 l_instance_tbl.DELETE(J);
3104
3105 END IF;
3106 J := l_instance_tbl.NEXT(J);
3107 oe_debug_pub.add('1 Processing next instance ' || J);
3108 END LOOP;
3109 l_instance_tbl.DELETE(I);
3110
3111 oe_debug_pub.add('1 Before calling Create_hdr_xml',1);
3112 oe_config_util.Create_hdr_xml
3113 ( p_model_line_id => l_top_model_line_id ,
3114 x_xml_hdr => l_init_msg);
3115 -- Call macd batch validate API to validate the actions for the given instances.
3116
3117 CZ_CF_API.VALIDATE
3118 (p_api_version => 1.0
3119 ,p_config_item_tbl => l_config_item_tbl
3120 ,p_config_ext_attr_tbl => l_config_attr_tbl
3121 ,p_url => l_url
3122 ,p_init_msg => l_init_msg
3123 ,p_validation_type => l_validation_type
3124 ,x_config_xml_msg => l_config_xml_msg
3125 ,x_return_status => x_return_status
3126 ,x_msg_count => x_msg_count
3127 ,x_msg_data => x_msg_data);
3128
3129 oe_debug_pub.add('2 After calling CZ_CF_API.VALIDATE' || x_return_status,2);
3130 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3132 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3133 RAISE FND_API.G_EXC_ERROR;
3134 END IF;
3135 -- Handle the return status from the call. Raise an exception accordingly.
3136
3137 IF (l_config_xml_msg.COUNT > 0) THEN
3138 oe_debug_pub.add('2 Count is greater: ' || l_config_xml_msg.COUNT,2);
3139 FOR xmlStr IN l_config_xml_msg.FIRST..l_config_xml_msg.LAST
3140 LOOP
3141 l_xml_str := l_xml_str||l_config_xml_msg(xmlStr);
3142 END LOOP;
3143
3144 l_xml_str := Upper(l_xml_str);
3145 oe_config_util.Parse_Output_xml
3146 ( p_xml => l_xml_str,
3147 p_line_id => l_top_model_line_id,
3148 x_valid_config => l_valid_config,
3149 x_complete_config => l_complete_config,
3150 x_config_header_id => l_top_config_header_id,
3151 x_config_rev_nbr => l_top_config_rev_nbr,
3152 x_return_status => x_return_status );
3153
3154 END IF;
3155
3156 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3158 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3159 RAISE FND_API.G_EXC_ERROR;
3160 END IF;
3161 -- Need to call the process_config api to synch up the order lines to onfiguration changes
3162 -- resulted due to the batch validation call.
3163 /*
3164 Begin
3165
3166 Select config_header_id, config_rev_nbr
3167 Into l_top_config_header_id, l_top_config_rev_nbr
3168 From oe_order_lines_all
3169 Where line_id = l_top_model_line_id;
3170
3171 Exception
3172 When OTHERS THEN
3173 Null;
3174
3175 End;
3176 */
3177 OE_CONFIG_PVT.Process_Config
3178 (p_header_id => l_header_id
3179 ,p_config_hdr_id => l_top_config_header_id
3180 ,p_config_rev_nbr => l_top_config_rev_nbr
3181 ,p_top_model_line_id => l_top_model_line_id
3182 ,p_ui_flag => 'Y'
3183 ,x_change_flag => l_change_flag
3184 ,x_msg_count => x_msg_count
3185 ,x_msg_data => x_msg_data
3186 ,x_return_status => x_return_status );
3187
3188 oe_debug_pub.add('After calling Process Config ' || x_return_status,2);
3189
3190 -- Delete the parent table and also clear the top model so that we do not carry the value.
3191 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3193 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3194 RAISE FND_API.G_EXC_ERROR;
3195 END IF;
3196
3197 l_top_model_line_Id := null;
3198 l_parent_exists_instance_tbl.delete;
3199
3200 END IF;
3201
3202 END LOOP;
3203
3204 END IF; -- Main if of p_instance_tbl count.
3205
3206
3207 -- Get message count and data
3208
3209 OE_MSG_PUB.Count_And_Get
3210 ( p_count => x_msg_count
3211 , p_data => x_msg_data
3212 );
3213 Oe_debug_pub.add('Before exiting populate macd action : ' || x_return_status,1);
3214 Exception
3215 WHEN FND_API.G_EXC_ERROR THEN
3216 IF l_debug_level > 0 THEN
3217 OE_DEBUG_PUB.Add('Expected Error in Populate_macd_action:'
3218 ||sqlerrm,3);
3219 END IF;
3220 x_return_status := FND_API.G_RET_STS_ERROR;
3221
3222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3223 IF l_debug_level > 0 THEN
3224 OE_DEBUG_PUB.Add('Unexpected Error Populate_macd_action:'
3225 ||sqlerrm,3);
3226 END IF;
3227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3228
3229 WHEN OTHERS THEN
3230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3231 IF l_debug_level > 0 THEN
3232 OE_DEBUG_PUB.Add('Other error in Populate_macd_action:'
3233 ||sqlerrm,1);
3234 END IF;
3235 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3236 OE_MSG_PUB.Add_Exc_Msg
3237 ( G_PKG_NAME
3238 ,'Populate_macd_action' );
3239 END IF;
3240
3241
3242 END Populate_macd_action;
3243
3244 -- This procedure verifies the existance of the parent line for the given instance header.
3245 -- If the header is present, system returns the top_model or else top_model would be null.
3246 -- Frist the get the config_hdr and rev for the given instance details and then look for the
3247 -- top model in the given header for the fetched item.
3248
3249 Procedure IS_CONTAINER_PRESENT
3250 (P_header_id IN NUMBER
3251 ,p_config_instance_hdr_id IN NUMBER
3252 ,p_config_instance_rev_number IN NUMBER
3253 ,x_top_model_line_id OUT NOCOPY NUMBER)
3254
3255 IS
3256
3257 l_item_id Number;
3258 l_config_hdr_id Number;
3259 l_config_rev_nbr Number;
3260 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3261 BEGIN
3262 oe_debug_pub.add('Entering IS_CONTAINER_PRESENT: ' || p_config_instance_hdr_id,2);
3263 Begin
3264
3265 /*
3266 Select cz.config_hdr_id, cz.config_rev_nbr, substr(cz.component_code, 1,instr(cz.component_code,'-')-1),
3267 oe.top_model_line_id
3268 Into l_config_hdr_id, l_config_rev_nbr, L_item_id,x_top_model_line_id
3269 from cz_config_details_v cz, oe_order_lines_all oe
3270 where cz.instance_hdr_id = p_config_instance_hdr_id
3271 and oe.config_header_id = cz.config_hdr_id
3272 and oe.config_rev_nbr = cz.config_rev_nbr
3273 and oe.top_model_line_id = oe.line_id
3274 and oe.header_id = p_header_id
3275 and component_instance_type = 'I'
3276 and rownum = 1;
3277 */
3278
3279 Select config_hdr_id, config_rev_nbr, substr(component_code, 1,instr(component_code,'-')-1)
3280 Into l_config_hdr_id, l_config_rev_nbr, L_item_id
3281 from cz_config_details_v
3282 where instance_hdr_id = p_config_instance_hdr_id
3283 and instance_rev_nbr = p_config_instance_rev_number
3284 and component_instance_type = 'I';
3285
3286 Exception
3287 WHEN OTHERS THEN
3288 oe_debug_pub.add('In when Others of cz_config query',2);
3289 Return;
3290
3291 END;
3292 oe_debug_pub.add('Top model Present: ' || l_item_id,2);
3293 IF l_item_id is not null
3294 THEN
3295
3296 Begin
3297
3298 Select top_model_line_id
3299 Into x_top_model_line_id
3300 From oe_order_lines_all
3301 Where header_id = p_header_id
3302 -- And config_header_id = l_config_hdr_id
3303 -- And config_rev_nbr = l_config_rev_nbr
3304 AND open_flag = 'Y'
3305 AND inventory_item_id = l_item_id
3306 And top_model_line_id = line_id
3307 AND rownum = 1;
3308
3309 Exception
3310 WHEN OTHERS THEN
3311 oe_debug_pub.add('In when Others of oe_order_lines query',2);
3312 Null;
3313 --RETURN;
3314 End;
3315 END IF;
3316 oe_debug_pub.add('Exiting IS_Container_Present: ' || x_top_model_line_id,2);
3317 Exception
3318 WHEN FND_API.G_EXC_ERROR THEN
3319 IF l_debug_level > 0 THEN
3320 OE_DEBUG_PUB.Add('Expected Error in IS_Container_Present::'
3321 ||sqlerrm,3);
3322 END IF;
3323
3324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3325 IF l_debug_level > 0 THEN
3326 OE_DEBUG_PUB.Add('Unexpected Error IS_Container_Present::'
3327 ||sqlerrm,3);
3328 END IF;
3329
3330 WHEN OTHERS THEN
3331 IF l_debug_level > 0 THEN
3332 OE_DEBUG_PUB.Add('Other error in IS_Container_Present::'
3333 ||sqlerrm,1);
3334 END IF;
3335 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3336 OE_MSG_PUB.Add_Exc_Msg
3337 ( G_PKG_NAME
3338 ,'Validate Action' );
3339 END IF;
3340
3341 End IS_Container_Present;
3342
3343
3344 -- This procedure will validate the action passed by the caller against the line.
3345 -- We will fetch the line details corresponding to the instance information.
3346 -- If the validation does not go through then the call will be failed.
3347
3348 Procedure Validate_action
3349 (p_top_model_line_id IN NUMBER
3350 ,P_instance_item_id IN NUMBER
3351 ,P_macd_action IN VARCHAR2
3352 ,x_config_item_id OUT NOCOPY NUMBER
3353 ,x_component_code OUT NOCOPY VARCHAR2
3354 ,x_return_status OUT NOCOPY VARCHAR2)
3355
3356
3357 IS
3358 L_BASELINE_REV_NBR NUMBER(9);
3359 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3360 BEGIN
3361
3362 -- In case of DELETE operation on order line with a baseline rev number > 0 , OM will error out.
3363 -- This is because CZ will not support REVERT action in this phase.
3364
3365 oe_debug_pub.add('Entering validate action: ' || p_macd_action,2);
3366
3367 x_return_status := FND_API.G_RET_STS_SUCCESS;
3368
3369
3370 IF p_macd_action in ('DELETE', 'DISCONTINUE') then
3371
3372 Begin
3373
3374 SELECT cz_hdr.baseline_rev_nbr
3375 INTO l_baseline_rev_nbr
3376 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
3377 cz_config_details_v czv
3378 WHERE oe_line.top_model_line_id = p_top_model_line_id
3379 AND oe_line.configuration_id = P_instance_item_id
3380 AND czv.config_hdr_id = oe_line.config_header_id
3381 AND czv.config_rev_nbr = oe_line.config_rev_nbr
3382 AND czv.config_item_id = oe_line.configuration_id
3383 AND cz_hdr.config_hdr_id = czv.instance_hdr_id
3384 AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
3385 AND cz_hdr.baseline_rev_nbr IS NOT NULL
3386 AND rownum = 1;
3387
3388
3389 oe_debug_pub.add('Base line rev number: ' || l_baseline_rev_nbr,2);
3390
3391 IF l_baseline_rev_nbr > 0 AND
3392 p_macd_action = 'DELETE' THEN
3393 oe_debug_pub.add('Before raising error',2);
3394 RAISE FND_API.G_EXC_ERROR;
3395 END IF;
3396
3397 Exception
3398 WHEN NO_DATA_FOUND THEN
3399 IF p_macd_action = 'DISCONTINUE' THEN
3400 oe_debug_pub.add('Before raising error no data found',2);
3401 RAISE FND_API.G_EXC_ERROR;
3402 END IF;
3403 End;
3404
3405 End if;
3406 BEGIN
3407
3408 SELECT configuration_id, component_code
3409 INTO x_config_item_id, x_component_code
3410 FROM oe_order_lines_all oe_line
3411 WHERE oe_line.top_model_line_id = p_top_model_line_id
3412 AND oe_line.configuration_id = p_instance_item_id
3413 AND rownum = 1;
3414
3415 EXCEPTION
3416
3417 WHEN OTHERS THEN
3418 Null;
3419 END;
3420 oe_debug_pub.add('component_code ' || x_component_code,1);
3421 oe_debug_pub.add('Before exiting Validate action' || x_return_status,1);
3422 Exception
3423 WHEN FND_API.G_EXC_ERROR THEN
3424 IF l_debug_level > 0 THEN
3425 OE_DEBUG_PUB.Add('Expected Error in validate_action:'
3426 ||sqlerrm,3);
3427 END IF;
3428 x_return_status := FND_API.G_RET_STS_ERROR;
3429
3430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3431 IF l_debug_level > 0 THEN
3432 OE_DEBUG_PUB.Add('Unexpected Error validate_action:'
3433 ||sqlerrm,3);
3434 END IF;
3435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3436
3437 WHEN OTHERS THEN
3438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3439 IF l_debug_level > 0 THEN
3440 OE_DEBUG_PUB.Add('Other error in validate_action:'
3441 ||sqlerrm,1);
3442 END IF;
3443 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3444 OE_MSG_PUB.Add_Exc_Msg
3445 ( G_PKG_NAME
3446 ,'Validate Action' );
3447 END IF;
3448 END Validate_action;
3449
3450
3451 -- This procedure will validate the action passed by the caller against the line.
3452 -- We will fetch the line details corresponding to the instance information.
3453 -- If the validation does not go through then the call will be failed.
3454
3455 Procedure Validate_line_action
3456 (p_line_id IN NUMBER
3457 ,P_config_header_id IN NUMBER
3458 ,P_config_rev_nbr IN NUMBER
3459 ,P_config_item_id IN NUMBER
3460 ,P_macd_action IN NUMBER
3461 ,x_return_status OUT NOCOPY VARCHAR2)
3462
3463 IS
3464 l_baseline_rev_nbr NUMBER(9);
3465 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3466 BEGIN
3467
3468 -- This is very similar to validateaction. In case of DELETE operation on order
3469 -- line with a baseline rev number > 0 , OM will error out.
3470 -- This is because CZ will not support REVERT action in this phase.
3471
3472 oe_debug_pub.add('Enetering validate line action ' || p_macd_action,2);
3473 x_return_status := FND_API.G_RET_STS_SUCCESS;
3474
3475 If p_macd_action in ('DELETE', 'DISCONTINUE') then
3476
3477 Begin
3478 SELECT cz_hdr.baseline_rev_nbr
3479 INTO l_baseline_rev_nbr
3480 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
3481 cz_config_details_v czv
3482 WHERE oe_line.line_id = p_line_id
3483 AND czv.config_hdr_id = oe_line.config_header_id
3484 AND czv.config_rev_nbr = oe_line.config_rev_nbr
3485 AND czv.config_item_id = oe_line.configuration_id
3486 AND cz_hdr.config_hdr_id = czv.instance_hdr_id
3487 AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
3488 AND cz_hdr.baseline_rev_nbr IS NOT NULL
3489 AND rownum = 1;
3490
3491 oe_debug_pub.add('Base line rev number : ' || l_baseline_rev_nbr,2);
3492 IF l_baseline_rev_nbr > 0 AND
3493 p_macd_action = 'DELETE' THEN
3494 oe_debug_pub.add('Base line rev number greater than 0',2);
3495 RAISE FND_API.G_EXC_ERROR;
3496 END IF;
3497
3498 Exception
3499 WHEN NO_DATA_FOUND THEN
3500 Null;
3501 End;
3502
3503 End if;
3504 oe_debug_pub.add('Exiting validate_line_action',2);
3505 Exception
3506 WHEN FND_API.G_EXC_ERROR THEN
3507 IF l_debug_level > 0 THEN
3508 OE_DEBUG_PUB.Add('Expected Error in Validate_line_action:'
3509 ||sqlerrm,3);
3510 END IF;
3511 x_return_status := FND_API.G_RET_STS_ERROR;
3512
3513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3514 IF l_debug_level > 0 THEN
3515 OE_DEBUG_PUB.Add('Unexpected Error Validate_line_action:'
3516 ||sqlerrm,3);
3517 END IF;
3518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3519
3520 WHEN OTHERS THEN
3521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3522 IF l_debug_level > 0 THEN
3523 OE_DEBUG_PUB.Add('Other error in validate_action:'
3524 ||sqlerrm,1);
3525 END IF;
3526 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3527 OE_MSG_PUB.Add_Exc_Msg
3528 ( G_PKG_NAME
3529 ,'Validate_line_action' );
3530 END IF;
3531 END Validate_line_action;
3532
3533
3534 END OE_CONFIG_TSO_PVT;
[Home] [Help]