1 PACKAGE BODY OE_CONFIG_TSO_PVT AS
2 /* $Header: OEXVTSOB.pls 120.15.12010000.4 2008/11/14 18:53:55 smusanna 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
418 x_return_status := FND_API.G_RET_STS_ERROR;
415 IF l_debug_level > 0 THEN
416 OE_DEBUG_PUB.Add('IB validation failed: IB_OWNER',3);
417 END IF;
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
545 IF l_debug_level > 0 THEN
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
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;
1092
1089 l_config_rev_nbr NUMBER;
1090 l_configuration_id NUMBER;
1091 l_cursor_count NUMBER;
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
1369
1366 OE_DEBUG_PUB.Add('ERROR during Remove_Unchanged_Lines!',3);
1367 END IF;
1368 END IF;
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');
1502 END;
1499 OE_Msg_Pub.add;
1500 RAISE FND_API.G_EXC_ERROR;
1501 END IF;
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
1580 := fnd_profile.value('RESP_APPL_ID');
1581
1582 IF l_debug_level > 0 THEN
1583 OE_DEBUG_PUB.Add('Config Create Date: '
1584 || l_appl_param_rec.config_creation_date,1);
1585 OE_DEBUG_PUB.Add('Config Effective Date: '
1586 || l_appl_param_rec.config_effective_date,1);
1587 OE_DEBUG_PUB.Add('Model Lookup Date: '
1588 || l_appl_param_rec.config_model_lookup_date,1);
1589 OE_DEBUG_PUB.Add('appl id: '
1590 || l_appl_param_rec.calling_application_id,1);
1591 END IF;
1592
1593 --IF p_mode = 1 THEN
1594 IF l_config_mode = 'R' THEN
1595
1596 Print_Time('Before Calling CZ_PUB.Generate_Config_trees');
1597
1598 CZ_NETWORK_API_PUB.Generate_Config_Trees
1599 ( p_api_version => 1.0
1600 ,p_config_tbl => l_config_tbl
1601 ,p_tree_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
1602 --,p_tree_copy_mode => CZ_API_PUB.G_NEW_REVISION_COPY_MODE
1603 ,p_appl_param_rec => l_appl_param_rec
1604 ,p_validation_context => CZ_API_PUB.G_INSTALLED
1605 ,x_config_model_tbl => l_config_model_tbl
1606 ,x_return_status => x_return_status
1607 ,x_msg_count => x_msg_count
1608 ,x_msg_data => x_msg_data
1609 );
1610 OE_MSG_PUB.Transfer_Msg_Stack;
1611 Print_Time('After CZ_PUB.Generate_Config_Trees:'||x_return_status);
1612
1613 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1614 IF l_debug_level >0 THEN
1615 OE_DEBUG_PUB.Add('Error in Generate_Config_Trees',2);
1616 END IF;
1617 RAISE FND_API.G_EXC_ERROR;
1618 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1619 IF l_debug_level > 0 THEN
1620 OE_DEBUG_PUB.Add('Unexpected Error in Gen_Config_trees',1);
1621 END IF;
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 END IF;
1624
1625 ---------now create model lines -----------------
1626
1627
1628 FOR I IN 1..l_config_model_tbl.count LOOP
1629 l_line_tbl(I) := OE_Order_PUB.G_MISS_LINE_REC;
1630
1631 l_line_tbl(I).header_id := l_header_id;
1632 l_line_tbl(I).inventory_item_id :=
1633 l_config_model_tbl(I).inventory_item_id;
1634 l_line_tbl(I).org_id :=
1635 l_config_model_tbl(I).organization_id;
1636 l_line_tbl(I).config_header_id :=
1637 l_config_model_tbl(I).config_hdr_id;
1638 l_line_tbl(I).config_rev_nbr :=
1639 l_config_model_tbl(I).config_rev_nbr;
1640 l_line_tbl(I).configuration_id :=
1641 l_config_model_tbl(I).config_item_id;
1642
1643 l_line_tbl(I).ordered_quantity := 1;
1644
1645 IF l_model_inv_item_id = l_line_tbl(I).inventory_item_id THEN
1646 l_line_tbl(I).operation := 'UPDATE';
1647 l_line_tbl(I).line_id := p_top_model_line_id;
1648 ELSE
1649 l_line_tbl(I).operation := 'CREATE';
1650 END IF;
1651 END LOOP;
1652
1653 IF l_debug_level > 0 THEN
1654 OE_DEBUG_PUB.Add('Before Calling OE_CONFIG_PVT.Call_Process_Order',3);
1655 oe_debug_pub.add('--------------------------------------');
1656 oe_debug_pub.add('Line_Tbl being pased to Call_Process_Order is...');
1657
1658 FOR I in 1..l_line_tbl.count LOOP
1659 oe_debug_pub.add('Row #'||I);
1660 oe_debug_pub.add('Hdr id:'||l_line_tbl(I).header_id);
1661 oe_debug_pub.add('Inv item id:'||l_line_tbl(I).inventory_item_id);
1662 oe_debug_pub.add('Org:'||l_line_tbl(I).org_id);
1663 oe_debug_pub.add('ConfigHdr id:'||l_line_tbl(I).config_header_id);
1664 oe_debug_pub.add('Config Rev:'||l_line_tbl(I).config_rev_nbr);
1665 oe_debug_pub.add('ConfID:'||l_line_tbl(I).configuration_id);
1666 oe_debug_pub.add('Ord qty:'||l_line_tbl(I).ordered_quantity);
1667 oe_debug_pub.add('LineID:'||l_line_tbl(I).line_id);
1668 oe_debug_pub.add('Oper:'||l_line_tbl(I).operation);
1669 END LOOP;
1670
1671 oe_debug_pub.add('-------------------------------------');
1672 END IF;
1673
1674 OE_CONFIG_PVT.Call_Process_Order
1675 ( p_line_tbl => l_line_tbl
1676 ,p_control_rec => l_control_rec
1677 ,p_ui_flag => 'Y'
1678 ,x_return_status => x_return_status );
1679
1680 IF l_debug_level > 0 THEN
1681 OE_DEBUG_PUB.Add('After Calling Process Order:'
1682 ||x_return_status,3);
1683 END IF;
1684
1685 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1686 IF l_debug_level > 0 THEN
1687 OE_DEBUG_PUB.Add('Error in Process Order.',2);
1688 END IF;
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1691 IF l_debug_level > 0 THEN
1692 OE_DEBUG_PUB.Add('Unexpected Error in Process order.',1);
1693 END IF;
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696
1697 ------------------now create child lines-------------------
1698 FOR I IN 1..l_line_tbl.count LOOP
1699
1700 IF l_debug_level > 0 THEN
1701 OE_DEBUG_PUB.Add('Before calling OE_CONFIG_PVT.Process_Config..',3);
1702 OE_DEBUG_PUB.Add('for cfg hdr ' || l_line_tbl(I).config_header_id,3);
1703 OE_DEBUG_PUB.Add('for cfg rev '|| l_line_tbl(I).config_rev_nbr,3);
1704 END IF;
1705
1706 SELECT line_id
1707 INTO l_line_tbl(I).line_id
1708 FROM oe_order_lines
1709 WHERE header_id = l_line_tbl(I).header_id
1710 AND config_header_id = l_line_tbl(I).config_header_id
1711 AND config_rev_nbr = l_line_tbl(I).config_rev_nbr;
1712
1713 MACD_SYSTEM_CALL := 'Y';
1714
1715 OE_CONFIG_PVT.Process_Config
1716 ( p_header_id => l_line_tbl(I).header_id
1717 ,p_config_hdr_id => l_line_tbl(I).config_header_id
1718 ,p_config_rev_nbr => l_line_tbl(I).config_rev_nbr
1719 ,p_top_model_line_id => l_line_tbl(I).line_id
1720 ,p_ui_flag => 'N'
1721 ,p_config_instance_tbl => l_instance_tbl
1722 ,x_change_flag => l_change_flag
1723 ,x_msg_count => x_msg_count
1724 ,x_msg_data => x_msg_data
1725 ,x_return_status => x_return_status );
1726
1727 MACD_SYSTEM_CALL := 'N';
1728
1729 IF l_debug_level > 0 THEN
1730 OE_DEBUG_PUB.Add('After Calling Process Config..'
1731 ||x_return_status,3);
1732 END IF;
1733
1734 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1735 IF l_debug_level >0 THEN
1736 OE_DEBUG_PUB.Add('Error in Process Config..',2);
1737 END IF;
1738 RAISE FND_API.G_EXC_ERROR;
1739 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1740 IF l_debug_level > 0 THEN
1741 OE_DEBUG_PUB.Add('Unexpected Error in Process Config..',1);
1742 END IF;
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END IF;
1745
1746 END LOOP;
1747 Print_Time ('Exiting OE_CONFIG_TSO_PVT.populate_tso_order_lines..');
1748 RETURN;
1749
1750 END IF; --ending l_Config_mode = r
1751
1752 IF l_config_mode = 'A' THEN
1753
1754 IF l_debug_level > 0 THEN
1755 OE_DEBUG_PUB.Add('Start Add to container.config mode=A',3);
1756 END IF;
1757
1758 BEGIN
1759
1760 SELECT config_header_id
1761 ,config_rev_nbr
1762 ,inventory_item_id
1763 INTO l_config_header_id
1764 ,l_config_rev_nbr
1765 ,l_inventory_item_id
1766 FROM oe_order_lines
1767 WHERE line_id = p_top_model_line_id;
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 IF l_debug_level > 0 THEN
1772 OE_DEBUG_PUB.Add('Error during select errmsg:'||sqlerrm,1);
1773 END IF;
1774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1775
1776 END;
1777
1778 Print_Time('Before Calling CZ_PUB.Add_To_Config_Tree:');
1779
1780 CZ_NETWORK_API_PUB.Add_To_Config_Tree
1781 ( p_api_version => 1.0
1782 ,p_inventory_item_id => l_inventory_item_id
1783 ,p_organization_id => OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
1784 ,p_config_hdr_id => l_config_header_id
1785 ,p_config_rev_nbr => l_config_rev_nbr
1786 ,p_instance_tbl => l_config_tbl
1787 --,p_tree_copy_mode => CZ_API_PUB.G_NEW_REVISION_COPY_MODE
1788 ,p_tree_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
1789 ,p_appl_param_rec => l_appl_param_rec
1790 ,p_validation_context => CZ_API_PUB.G_INSTALLED
1791 ,x_config_model_rec => l_config_model_rec
1792 ,x_return_status => x_return_status
1793 ,x_msg_count => x_msg_count
1794 ,x_msg_data => x_msg_data
1795 );
1796
1797 Print_Time('After CZ_PUB.Add_To_Config_Tree:'||x_return_status);
1798
1799 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1800 IF l_debug_level > 0 THEN
1801 OE_DEBUG_PUB.Add ('Error in Add_to_config_tree',2);
1802 END IF;
1803 RAISE FND_API.G_EXC_ERROR;
1804 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1805 IF l_debug_level > 0 THEN
1806 OE_DEBUG_PUB.Add ('Unexpected Error in Add_to_config_tree',1);
1807 END IF;
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 END IF;
1810
1811 IF l_debug_level > 0 THEN
1812 OE_DEBUG_PUB.Add('HeaderID:'||l_config_model_rec.config_hdr_id,3);
1813 OE_DEBUG_PUB.Add('Rev Number:'||l_config_model_rec.config_rev_nbr,3);
1814 OE_DEBUG_PUB.Add('Before Calling Process_Config',3);
1815 END IF;
1816
1817 MACD_SYSTEM_CALL := 'Y';
1818 OE_CONFIG_PVT.Process_Config
1819 ( p_header_id => l_header_id
1820 ,p_config_hdr_id => l_config_model_rec.config_hdr_id
1821 ,p_config_rev_nbr => l_config_model_rec.config_rev_nbr
1822 ,p_top_model_line_id => p_top_model_line_id
1823 ,p_ui_flag => 'Y'
1824 ,p_config_instance_tbl => l_instance_tbl
1825 ,x_change_flag => l_change_flag
1826 ,x_msg_count => x_msg_count
1827 ,x_msg_data => x_msg_data
1828 ,x_return_status => x_return_status
1829 );
1830 MACD_SYSTEM_CALL := 'N';
1831
1832 IF l_debug_level > 0 THEN
1833 OE_DEBUG_PUB.Add('After Process_Config:'||x_return_status,3);
1834 END IF;
1835
1836 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1837 IF l_debug_level > 0 THEN
1838 OE_DEBUG_PUB.Add('Error in Process_Config',2);
1839 END IF;
1840 RAISE FND_API.G_EXC_ERROR;
1841 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1842 IF l_debug_level > 0 THEN
1843 OE_DEBUG_PUB.Add('Unexpected Error in Process_Config',1);
1844 END IF;
1845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1846 END IF;
1847
1848 END IF; --if l_config_mode = a
1849
1850 OE_MSG_PUB.Count_And_Get
1851 ( p_count => x_msg_count
1852 ,p_data => x_msg_data );
1853
1854 Print_Time ('Exiting OE_CONFIG_TSO_PVT.populate_tso_order_lines...');
1855
1856 EXCEPTION
1857
1858 WHEN FND_API.G_EXC_ERROR THEN
1859 IF l_debug_level > 0 THEN
1860 OE_DEBUG_PUB.Add('Expected Error in populate_tso_order_lines:'
1861 ||sqlerrm, 2);
1862 END IF;
1863
1864 oe_debug_pub.add('RMV: 2 Msg count:'||x_msg_count);
1865 OE_MSG_PUB.Count_And_Get
1866 ( p_count => x_msg_count
1867 ,p_data => x_msg_data );
1868 x_return_status := FND_API.G_RET_STS_ERROR;
1869
1870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1871 IF l_debug_level > 0 THEN
1872 OE_DEBUG_PUB.Add('Unexpected Error in populate_tso_order_lines'
1873 ||sqlerrm, 1);
1874 END IF;
1875
1876 x_msg_count := OE_MSG_PUB.COUNT_MSG;
1877 IF l_debug_level > 0 THEN
1878 oe_debug_pub.add('RMV: 3 Msg count:'||x_msg_count);
1879 END IF;
1880
1881 OE_MSG_PUB.Count_And_Get
1882 ( p_count => x_msg_count
1883 ,p_data => x_msg_data );
1884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1885
1886 WHEN OTHERS THEN
1887
1888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1889 IF l_debug_level > 0 THEN
1890 oe_debug_pub.add('RMV: 4 Msg count: '|| sqlerrm );
1891 END IF;
1892
1893 x_msg_count := OE_MSG_PUB.COUNT_MSG;
1894 FOR I in 1..x_msg_count LOOP
1895 x_msg_data := OE_MSG_PUB.Get(I,'F');
1896 IF l_debug_level > 0 THEN
1897 oe_debug_pub.add('Messages from Configurator...');
1898 END IF;
1899 END LOOP;
1900
1901 OE_MSG_PUB.Count_And_Get
1902 ( p_count => x_msg_count
1903 ,p_data => x_msg_data );
1904
1905 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1906 OE_MSG_PUB.Add_Exc_Msg
1910 END IF;
1907 ( G_PKG_NAME
1908 ,'populate_tso_order_lines'
1909 );
1911
1912 END populate_tso_order_lines;
1913
1914
1915 Procedure Process_MACD_Order
1916 (p_api_version_number IN NUMBER,
1917 p_caller IN VARCHAR2,
1918 p_x_header_id IN OUT NOCOPY NUMBER,
1919 p_sold_to_org_id IN NUMBER,
1920 p_MACD_Action IN VARCHAR2,
1921 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
1922 p_Instance_Tbl IN csi_datastructures_pub.instance_cz_tbl,
1923 p_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl,
1924 x_container_line_id OUT NOCOPY NUMBER,
1925 x_number_of_containers OUT NOCOPY NUMBER,
1926 x_return_status OUT NOCOPY VARCHAR2,
1927 x_msg_count OUT NOCOPY VARCHAR2,
1928 x_msg_data OUT NOCOPY VARCHAR2)
1929 IS
1930
1931 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1932 l_perform_action BOOLEAN;
1933 l_header_id NUMBER;
1934 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
1935
1936 CURSOR Models is
1937 SELECT line_id
1938 FROM oe_order_lines_all
1939 WHERE header_id = p_x_header_id
1940 AND open_flag = 'Y'
1941 AND top_model_line_id = line_id
1942 AND ATO_LINE_ID IS NULL;
1943
1944 BEGIN
1945
1946 IF l_debug_level > 0 THEN
1947 oe_debug_pub.add('Entering oe_config_tso_pvt.Process_MACD_Order',1);
1948 oe_debug_pub.add('p_x_header_id : ' || p_x_header_id,2);
1949 oe_debug_pub.add('p_sold_to_org_id : ' || p_sold_to_org_id,2);
1950 oe_debug_pub.add('p_MACD_Action : ' || p_MACD_Action,2);
1951 oe_debug_pub.add('p_x_line_tbl : ' || p_x_line_tbl.count,2);
1952 oe_debug_pub.add('p_Instance_Tbl : ' || p_Instance_Tbl.count,2);
1953 oe_debug_pub.add('p_Extended_Attrib_Tbl : ' || p_Extended_Attrib_Tbl.count,2);
1954 END IF;
1955 -- All validations will be performed here.
1956
1957 x_return_status := FND_API.G_RET_STS_SUCCESS;
1958
1959 IF p_caller = 'P' THEN
1960
1961 -- p_header_id and p_sold_to_org_id should be mutually exclusive. (Not required for the group call)
1962
1963 IF (p_x_header_id is null
1964 AND p_sold_to_org_id is null)
1965 OR (p_x_header_id is not null
1966 AND p_sold_to_org_id is not null) THEN
1967 -- Set retun status and raise an error.
1968 -- Message should be seeded for this.
1969
1970 FND_MESSAGE.Set_Name('ONT','ONT_TSO_HEAD_CUST_MISSING');
1971 x_return_status := FND_API.G_RET_STS_ERROR;
1972 oe_debug_pub.add('Unable to process since both header and customer is null',1);
1973 END IF;
1974
1975 -- p_instance and p_x_line table should be exclusive. (not required for the group call)
1976
1977 IF (p_instance_tbl.count > 0
1978 AND p_x_line_tbl.count > 0)
1979 OR (p_instance_tbl.count = 0
1980 AND p_x_line_tbl.count = 0) THEN
1981
1982 --Set return status and raise an error
1983 --Message should be seeded for this.
1984 -- If no data is passed, raise an error and set the return status.
1985
1986 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INS_ORD_MISSING');
1987 oe_debug_pub.add('Unable to process since both line and instance table are populated',1);
1988 x_return_status := FND_API.G_RET_STS_ERROR;
1989 END IF;
1990
1991
1992 -- All selected instances should belong to same sold_to
1993 -- Item instance should be passed only once in the instance table (Not required this call CZ can handle this scenario)
1994 -- IB owner should be same on all the instances.
1995 -- Fail the call, when different action is passed in p_instance/line table than the p_macd_action.
1996
1997 If p_instance_tbl.count > 1 THEN
1998
1999 For I in 2..p_instance_tbl.count LOOP
2000
2001 IF p_instance_tbl(1).sold_to_org_id <> p_instance_tbl(I).sold_to_org_id
2002 OR p_instance_tbl(1).IB_OWNER <> p_instance_tbl(I).IB_OWNER
2003 OR nvl(p_instance_tbl(I).action,p_macd_action) <> nvl(p_macd_action, p_instance_tbl(I).action)
2004 THEN
2005
2006 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008 oe_debug_pub.add('Invalid Datea 2',3);
2009 -- Invalid data is passed, do not proceed further. Raise error.
2010 ELSE
2011
2012 IF p_instance_tbl(I).action is not null then
2013 l_perform_action := TRUE;
2014 END IF; -- action
2015 END IF;
2016 END LOOP;
2017
2018
2019 If p_instance_tbl(1).action is not null then
2020 L_perform_action := TRUE;
2021 END IF; -- action
2022 IF nvl(p_instance_tbl(1).action,p_macd_action) <> nvl(p_macd_action, p_instance_tbl(1).action) THEN
2023
2024 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2025 x_return_status := FND_API.G_RET_STS_ERROR;
2026 oe_debug_pub.add('Invalid Datea 3',3);
2027 Raise FND_API.G_EXC_ERROR;
2028 END IF;
2029
2030 End IF; -- count
2031
2032 -- The following code is for validating line record data.
2033 IF p_x_line_tbl.count > 0 THEN
2034
2035 For I in 1..p_x_line_tbl.count LOOP
2036
2037 IF nvl(p_x_line_tbl(I).operation,p_macd_action) <> nvl(p_macd_action, p_x_line_tbl(I).operation)
2038 THEN
2039 -- Invalid data is passed, do not proceed further. Raise error.
2040 FND_MESSAGE.Set_Name('ONT','ONT_TSO_INVALID_DATA_API_CALL');
2041 x_return_status := FND_API.G_RET_STS_ERROR;
2042 oe_debug_pub.add('Invalid Datea 4',3);
2043 Raise FND_API.G_EXC_ERROR;
2044 ELSE
2045
2046 IF p_x_line_tbl(I).operation is not null then
2047 L_perform_action := TRUE;
2048 END IF; -- action
2049
2050 END IF;
2051 END LOOP;
2052
2053 End IF; -- Table count
2054 END IF; -- Public Validation.
2055
2056
2057 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2058 oe_debug_pub.add('Unable to process due to error',2);
2059 Raise FND_API.G_EXC_ERROR;
2060 END IF;
2061 IF l_debug_level > 0 THEN
2062 oe_debug_pub.add('Main Logic Starts from here',2);
2063 END IF;
2064 -- If p_sold_to_org_id is passed first create an order using the sold to and add the instances passed to the order.
2065 -- Procedure create_header will be introduced to create header record.*/
2066
2067 IF p_sold_to_org_id is not null THEN
2068
2069 -- 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
2070 -- to create container models and it's child lines.
2071
2072 IF l_debug_level > 0 THEN
2073 oe_debug_pub.add('Before calling Create_header',2);
2074 END IF;
2075 Create_header(p_sold_to_org_id => p_sold_to_org_id
2076 ,x_header_id => p_x_header_id
2077 ,x_return_status => x_return_status
2078 ,x_msg_count => x_msg_count
2079 ,x_msg_data => x_msg_data);
2080 IF l_debug_level > 0 THEN
2081 oe_debug_pub.add('After calling Create_header::' || p_x_header_id ||'::' || x_return_status,2);
2082 END IF;
2083
2084 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2085 IF l_debug_level > 0 THEN
2086 oe_debug_pub.add('Unexpected error in creating Order Header for sold to: ' || p_sold_to_org_id,2);
2087 END IF;
2088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2089 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2090 IF l_debug_level > 0 THEN
2091 oe_debug_pub.add('Expected error in creating Order Header for sold to: ' || p_sold_to_org_id,2);
2092 END IF;
2093 RAISE FND_API.G_EXC_ERROR;
2094 END IF;
2095
2096
2097 -- The procedure populate_tso_order_lines will take care of creating model lines and child lines with the given instance table.
2098 -- We can continue to call this API in this case as the order is getting created in this call.
2099
2100 -- The following API will call generate_config_tree procedure to generate the container models. OM creates the models first and then
2101 -- creates the child lines by looking at the cz_config_details_v.
2102
2103 -- 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
2104 -- 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
2105 -- on the oe_install_base_util's record structure.
2106
2107 IF l_debug_level > 0 THEN
2108 oe_debug_pub.add('Before calling populate_tso_order_lines ',2);
2109 END IF;
2110
2111 IF p_Instance_Tbl.count > 0 THEN
2112
2113 oe_config_tso_pvt.populate_tso_order_lines(
2114 p_header_id => p_x_header_id,
2115 p_top_model_line_id => null,
2116 p_instance_tbl => p_instance_tbl,
2117 p_mode => 1,
2118 x_msg_count => x_msg_count,
2119 x_msg_data => x_msg_data,
2120 x_return_status => x_return_status);
2121
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add('After calling populate_tso_order_lines ' || x_return_status,2);
2124 END IF;
2125
2126 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2128 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2129 RAISE FND_API.G_EXC_ERROR;
2130 END IF;
2131 END IF;
2132
2133 ELSIF p_x_header_id is not null THEN
2134
2135 /* 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.
2136
2137 We cannot use the procedure populate_tso_order_lines as that procedure creates container model for the newly passed instances,
2138 but actually the container may already exists in that order. We need to have the logic to identify the existance of the container
2139 model for the passed in instance, if the container exists then the instances should be added to the same or else create the new
2140 container. For example, container "A" was fulfilled and that created instances I1 to I10. First time if the user creates the
2141 order with instance I1, system should create the container "A" and added the I1 to the same. Later if user picks I4 from IB,
2142 system should add the I4 to the existing container model instead of creating a new vcontainer recored in that order.
2143
2144 We will create an API named create_TSO_order_lines procedure to add the lines into existing order.*/
2145
2146 IF l_debug_level > 0 THEN
2147 oe_debug_pub.add('Before calling oe_config_tso_pvt.create_tso_order_lines ',2);
2148 END IF;
2149
2150 oe_config_tso_pvt.create_tso_order_lines(
2151 p_header_id => p_x_header_id,
2152 p_top_model_line_id => null,
2153 p_instance_tbl => p_instance_tbl,
2154 x_msg_count => x_msg_count,
2155 x_msg_data => x_msg_data,
2156 x_return_status => x_return_status);
2157
2158 IF l_debug_level > 0 THEN
2159 oe_debug_pub.add('After calling oe_config_tso_pvt.create_tso_order_lines ' || x_return_status , 2);
2160 END IF;
2161
2162
2163 END IF; -- Sold to org.
2164
2165 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2167 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2168 RAISE FND_API.G_EXC_ERROR;
2169 END IF;
2170 -- We are done with TSO line creation logic. Processing logic follows.
2171 IF p_macd_action is not null
2172 OR l_perform_action THEN
2173
2174 /* If the caller passes an action we need to validate the action. If the lines are created in this call,
2175 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.
2176 Or the process will be based on the lines table.*/
2177
2178 IF l_debug_level > 0 THEN
2179 oe_debug_pub.add('Before calling populate_tso_order_lines ',2);
2180 END IF;
2181
2182 Populate_MACD_action
2183 ( p_header_id => p_x_header_id
2184 ,p_instance_tbl => p_instance_tbl
2185 ,p_x_line_tbl => p_x_line_tbl
2186 ,p_Extended_Attrib_Tbl => p_Extended_Attrib_Tbl
2187 ,p_macd_action => p_macd_action
2188 ,x_msg_data => x_msg_data
2189 ,x_msg_count => x_msg_count
2190 ,x_return_status => x_return_status);
2191
2192 IF l_debug_level > 0 THEN
2193 oe_debug_pub.add('After calling Populate_MACD_action ' || x_return_status,2);
2194
2195 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2198 RAISE FND_API.G_EXC_ERROR;
2199 END IF;
2200 END IF; -- action
2201
2202 END IF;
2203 -- Populate out variables
2204
2205 FOR I IN Models LOOP
2206
2207 Oe_debug_pub.add('Model record ' || I.line_id,2);
2208 IF x_container_line_id IS NULL THEN
2209 x_container_line_id := I.line_id;
2210 END IF;
2211
2212 x_number_of_containers := nvl(x_number_of_containers,0) + 1;
2213
2214 END LOOP;
2215
2216 IF p_x_line_tbl.count = 0 THEN
2217
2218 oe_line_util.Query_Rows
2219 (p_header_id => p_x_header_id
2220 ,x_line_tbl => p_x_line_tbl);
2221
2222 END IF;
2223 -- Get message count and data
2224
2225 oe_msg_pub.count_and_get
2226 ( p_count => x_msg_count
2227 , p_data => x_msg_data);
2228
2229 IF l_debug_level > 0 THEN
2230 Oe_debug_pub.add(' Out Values from process macd',2);
2231 Oe_debug_pub.add('header_id : ' || p_x_header_id,2);
2232 Oe_debug_pub.add('Line count : ' || p_x_line_tbl.count,2);
2233 Oe_debug_pub.add('x_container_line_id : ' || x_container_line_id,2);
2234 Oe_debug_pub.add('x_number_of_container : ' || x_number_of_containers,2);
2235 Oe_debug_pub.add('Return Status : ' || x_return_status,2);
2236 Oe_debug_pub.add('Message count : ' || x_msg_count,2);
2237 Oe_debug_pub.add('Message Data : ' || x_msg_data,2);
2238
2239 oe_debug_pub.add('Exiting oe_config_tso_pvt.Process_MACD_Order ' || x_return_status,1);
2240 END IF;
2241 EXCEPTION
2242
2243 WHEN FND_API.G_EXC_ERROR THEN
2244 oe_msg_pub.count_and_get
2245 ( p_count => x_msg_count
2246 , p_data => x_msg_data);
2247
2248 IF l_debug_level > 0 THEN
2249 OE_DEBUG_PUB.Add('Expected Error in Process_MACD_Order:'
2250 ||sqlerrm,3);
2251 END IF;
2252 x_return_status := FND_API.G_RET_STS_ERROR;
2253
2254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2255 oe_msg_pub.count_and_get
2256 ( p_count => x_msg_count
2257 , p_data => x_msg_data);
2258
2259 IF l_debug_level > 0 THEN
2260 OE_DEBUG_PUB.Add('Unexpected Error Process_MACD_Order:'
2261 ||sqlerrm,3);
2262 END IF;
2263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2264
2265 WHEN OTHERS THEN
2266 oe_msg_pub.count_and_get
2267 ( p_count => x_msg_count
2268 , p_data => x_msg_data);
2269
2270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2271 IF l_debug_level > 0 THEN
2272 OE_DEBUG_PUB.Add('Other error in Process_MACD_Order:'
2273 ||sqlerrm,1);
2274 END IF;
2275 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2276 OE_MSG_PUB.Add_Exc_Msg
2277 ( G_PKG_NAME
2278 ,'Process_MACD_Order' );
2279 END IF;
2280
2281 END Process_MACD_Order;
2282
2283 /* The logic of the create_header is given below. This API will accept sold to as a in parameter and creates a header
2284 using the same by calling headers procedure.*/
2285
2286 Procedure Create_header
2287 (p_sold_to_org_id IN NUMBER
2288 ,x_header_id OUT NOCOPY NUMBER
2289 ,x_return_status OUT NOCOPY VARCHAR2
2290 ,x_msg_count OUT NOCOPY NUMBER
2291 ,x_msg_data OUT NOCOPY VARCHAR2)
2292 IS
2293 -- Declare following local variables which are in parameters to the header procedure.
2294 l_header_rec OE_Order_PUB.Header_Rec_Type;
2295 l_old_header_rec OE_Order_PUB.Header_Rec_Type;
2296 l_control_rec OE_GLOBALS.Control_Rec_Type;
2297
2298 --
2299 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2300 --
2301 BEGIN
2302
2303 IF l_debug_level > 0 THEN
2304 oe_debug_pub.add( 'ENTERING OE_TSO_CONFIG_PVT.Create_Header' , 1 ) ;
2305 END IF;
2306 x_return_status := FND_API.G_RET_STS_SUCCESS;
2307
2308 -- Use the default control record to call header procedure.
2309 -- Populate the header record with the input data and an operation.
2310
2311 l_old_header_rec :=OE_ORDER_PUB.G_MISS_HEADER_REC;
2312 l_header_rec :=OE_ORDER_PUB.G_MISS_HEADER_REC;
2313 l_header_rec.sold_to_org_id := p_sold_to_org_id;
2314
2315 -- Set Operation to Create
2316
2317 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2318
2319 -- Call Oe_Order_Pvt.Header
2320 -- Add debug messages.
2321
2322 Oe_Order_Pvt.Header
2323 ( p_validation_level =>FND_API.G_VALID_LEVEL_NONE
2324 , p_init_msg_list => FND_API.G_TRUE
2325 , p_control_rec =>l_control_rec
2326 , p_x_header_rec =>l_header_rec
2327 , p_x_old_header_rec =>l_old_header_rec
2328 , x_return_status =>x_return_status );
2329
2330 -- Handle the return status.
2331
2332 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2334 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2335 RAISE FND_API.G_EXC_ERROR;
2336 END IF;
2337
2338 -- Load OUT parameters.
2339
2340 X_header_id := l_header_rec.header_id;
2341
2342 -- Get message count and data
2343
2344
2345 OE_MSG_PUB.Count_And_Get
2346 ( p_count => x_msg_count
2347 , p_data => x_msg_data);
2348
2349 EXCEPTION
2350
2351 WHEN FND_API.G_EXC_ERROR THEN
2352 IF l_debug_level > 0 THEN
2353 OE_DEBUG_PUB.Add('Expected Error in Create_Header:'
2354 ||sqlerrm,3);
2355 END IF;
2356 x_return_status := FND_API.G_RET_STS_ERROR;
2357
2358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2359 IF l_debug_level > 0 THEN
2360 OE_DEBUG_PUB.Add('Unexpected Error Create_Header:'
2361 ||sqlerrm,3);
2362 END IF;
2363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2364
2365 WHEN OTHERS THEN
2366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2367 IF l_debug_level > 0 THEN
2368 OE_DEBUG_PUB.Add('Other error in Create_Header:'
2369 ||sqlerrm,1);
2370 END IF;
2371 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2372 OE_MSG_PUB.Add_Exc_Msg
2373 ( G_PKG_NAME
2374 ,'Create_Header' );
2375 END IF;
2376
2377 END Create_Header;
2378
2379
2380
2381 --The logic of this API would be very similar to the populate_tso_order_lines.
2382 --The main difference here would be we will look for the existance of the container model
2383 --for each instance and if container exists then we will add the instance to the exisiting container
2384 --model rather creating new container model. We will check for the existance of the container model by
2385 --using the component code passed in the instance table.
2386
2387 PROCEDURE Create_TSO_Order_Lines
2388 ( p_header_id IN NUMBER
2389 , p_top_model_line_id IN NUMBER
2390 , p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
2391 , x_msg_data OUT NOCOPY VARCHAR2
2392 , x_msg_count OUT NOCOPY NUMBER
2393 , x_return_status OUT NOCOPY VARCHAR2
2394 )
2395 IS
2396
2397
2398 l_model_inv_item_id NUMBER;
2399 l_config_header_id NUMBER;
2400 l_config_rev_nbr NUMBER;
2401 l_top_model_line_id NUMBER;
2402 J NUMBER;
2403 I NUMBER;
2404
2405 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2406 l_parent_exists_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2410 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2407 l_no_parent_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2408 l_Temp_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2409
2411 Begin
2412
2413 --IF the ship_to_site_use_id and bill_to_site_use_id has NULL value
2414 --for all records returned by IB, we remember this and pass the
2415 --G_CONFIG_INTSTANCE_TBL to Process_Config to improve performance
2416
2417 oe_debug_pub.add('Entering Procedure Create_TSO_Order_Lines: ' || l_instance_tbl.count,1);
2418
2419
2420 l_instance_tbl := p_instance_tbl;
2421 I := l_instance_tbl.FIRST;
2422
2423 While I IS NOT NULL LOOP
2424 BEGIN
2425
2426 -- Code should be added to identify the existance of the container model record.
2427 -- Loop through the table, identify the container model item from cz tables and check the existance of the record in
2428 -- OM table from the given header_id.
2429
2430 IS_container_present(
2431 p_header_id => p_header_id
2432 ,p_config_instance_hdr_id => l_instance_tbl(I).config_instance_hdr_id
2433 ,p_config_instance_rev_number => l_instance_tbl(I).config_instance_rev_number
2434 ,x_top_model_line_id => l_top_model_line_id);
2435
2436 oe_debug_pub.add('After calling IS_container_present: ' || l_top_model_line_id,2);
2437
2438 -- If the model exists then transfer the instance record to l_parent_exists_instance_tbl and
2439 -- delete the instance record from l_instance_tbl. Also loop through the l_instance_tbl and transfer
2440 -- all the instance records that matchs the parent config_hdr and config_rev_nbr.
2441 -- Delete the records from l_instance_tbl after every transfer. Now we need to add these instances to the existing model.
2442
2443 IF l_top_model_line_id is not null THEN
2444
2445 oe_debug_pub.add('Top model is present',3);
2446 J := l_instance_tbl.NEXT(I);
2447
2448 L_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2449
2450 While J IS NOT NULL LOOP
2451 BEGIN
2452
2453 IF l_instance_tbl(J).config_instance_hdr_id = l_instance_tbl(I).config_instance_hdr_id
2454 AND l_instance_tbl(J).config_instance_rev_number = l_instance_tbl(I).config_instance_rev_number THEN
2455
2456 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) :=
2457 l_instance_tbl(J);
2458
2459 l_instance_tbl.DELETE(J);
2460
2461
2462 END IF;
2463 J := l_instance_tbl.NEXT(J);
2464 END;
2465 END LOOP;
2466 l_instance_tbl.DELETE(I);
2467
2468 oe_debug_pub.add('Before calling populate_tso_order_lines to append lines: ' ||
2469 l_parent_exists_instance_tbl.count,3);
2470 oe_config_tso_pvt.populate_tso_order_lines(
2471 p_header_id => p_header_id,
2472 p_top_model_line_id => l_top_model_line_id,
2473 p_instance_tbl => l_parent_exists_instance_tbl,
2474 p_mode => 1,
2475 x_msg_count => x_msg_count,
2476 x_msg_data => x_msg_data,
2477 x_return_status => x_return_status);
2478
2479 -- Handle the return status from the call. Raise an exception accordingly.
2480 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
2481 RAISE FND_API.G_EXC_ERROR;
2482 ELSIF x_return_status=FND_API.G_RET_STS_UNEXP_ERROR THEN
2483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2484 END IF;
2485
2486 -- Delete the parent table and also clear the top model so that we do not carry the value.
2487
2488 l_top_model_line_Id := null;
2489 l_parent_exists_instance_tbl.delete;
2490 Goto End_loop;
2491
2492 ELSE
2493 --If the model does not exists then transfer the record to l_no_paranet_instance_tbl and delete the record.
2494
2495 --commented for BUG#7376452
2496 --The table type being evaluated is l_no_parent_instance_tbl and the conter we use is of l_parent_exists_instance_tbl
2497 --so whenever we evaluate for any standard line only the last line is evaluated evrytime as the conter always remains at 0
2498
2499 --l_no_parent_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2500 l_no_parent_instance_tbl(l_no_parent_instance_tbl.count + 1) := l_instance_tbl(I); --added BUG#7376452
2501
2502 END IF; -- top model
2503
2504 <<End_Loop>>
2505
2506 I := L_instance_tbl.Next(I);
2507 END;
2508 END LOOP;
2509
2510 IF l_no_parent_instance_tbl.count >0 THEN
2511
2512 -- We will come here only if have instances without parent. Call populate_tso_order_lines to create containers and its chiled lines.
2513 oe_debug_pub.add('Before calling populate_tso_order_line to create lines: ' ||
2514 l_no_parent_instance_tbl.count,2);
2515 oe_config_tso_pvt.populate_tso_order_lines(
2516 p_header_id =>p_header_id,
2517 p_top_model_line_id => null,
2518 p_instance_tbl => l_no_parent_instance_tbl,
2519 p_mode => 1,
2520 x_msg_count =>x_msg_count,
2521 x_msg_data => x_msg_data,
2522 x_return_status => x_return_status);
2523 -- Handle the return status from the call. Raise an exception accordingly.
2524 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
2528 END IF;
2525 RAISE FND_API.G_EXC_ERROR;
2526 ELSIF x_return_status=FND_API.G_RET_STS_UNEXP_ERROR THEN
2527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2529
2530 l_no_parent_instance_tbl.delete;
2531
2532 END IF; -- L_no_parent_instance.
2533
2534 -- Get message count and data
2535 oe_debug_pub.add('Before exiting Create_TSO_Order_Lines' || x_return_status,2);
2536 OE_MSG_PUB.Count_And_Get
2537 ( p_count => x_msg_count
2538 , p_data => x_msg_data
2539 );
2540
2541 EXCEPTION
2542
2543 WHEN FND_API.G_EXC_ERROR THEN
2544 IF l_debug_level > 0 THEN
2545 OE_DEBUG_PUB.Add('Expected Error in Create_TSO_Order_Lines:'
2546 ||sqlerrm,3);
2547 END IF;
2548 x_return_status := FND_API.G_RET_STS_ERROR;
2549
2550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2551 IF l_debug_level > 0 THEN
2552 OE_DEBUG_PUB.Add('Unexpected Error Create_TSO_Order_Lines:'
2553 ||sqlerrm,3);
2554 END IF;
2555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2556
2557 WHEN OTHERS THEN
2558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559 IF l_debug_level > 0 THEN
2560 OE_DEBUG_PUB.Add('Other error in Create_TSO_Order_Lines:'
2561 ||sqlerrm,1);
2562 END IF;
2563 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2564 OE_MSG_PUB.Add_Exc_Msg
2565 ( G_PKG_NAME
2566 ,'Create_TSO_Order_Lines' );
2567 END IF;
2568 END Create_TSO_Order_Lines;
2569
2570
2571
2572 /*Procedure Populate_MACD_action will prepare the config tables for batch validation.
2573 This API will be called when p_macd_action is passed so the action should be populated
2574 on the each line before calliing macd batch validate API. We will fetch the config details
2575 and populate the same in the config tables along with the action. We will validate the
2576 action and convert the actions to number to call CZ validate. Either instance table or
2577 line table will be passed to this table along with the extended table. We will also populate
2578 the data in cz extended table as well. */
2579
2580 PROCEDURE Populate_MACD_action(
2581 p_header_id IN NUMBER
2582 ,p_instance_tbl IN csi_datastructures_pub.instance_cz_tbl
2583 ,p_x_Line_Tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type
2584 ,p_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl
2585 ,p_macd_action IN VARCHAR2
2586 -- ,x_config_item_tbl OUT NOCOPY CZ_API_PUB.config_tbl_type;
2587 -- ,x_config_ext_attr_tbl OUT NOCOPY config_ext_attr_tbl_type
2588 ,x_msg_data OUT NOCOPY VARCHAR2
2589 ,x_msg_count OUT NOCOPY NUMBER
2590 ,x_return_status OUT NOCOPY VARCHAR2)
2591
2592 IS
2593
2594 l_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2595 l_parent_exists_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2596 l_no_parent_instance_tbl csi_datastructures_pub.instance_cz_tbl;
2597 l_config_item_rec CZ_CF_API.config_item_rec_type;
2598 l_config_item_tbl CZ_CF_API.config_item_tbl_type;
2599 l_config_attr_rec CZ_CF_API.config_ext_attr_rec_type;
2600 l_config_attr_tbl CZ_CF_API.config_ext_attr_tbl_type;
2601 l_url VARCHAR2(100);
2602 l_init_msg VARCHAR2(2000);
2603 l_validation_type VARCHAR2(1) := CZ_API_PUB.VALIDATE_ORDER;
2604 l_config_xml_msg CZ_CF_API.CFG_OUTPUT_PIECES;
2605 l_control_rec OE_GLOBALS.Control_Rec_Type;
2606 J NUMBER;
2607 I NUMBER;
2608 l_component_code VARCHAR2(30);
2609 l_config_item_id NUMBER;
2610 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2611 L_TOP_MODEL_LINE_ID NUMBER;
2612 l_top_config_header_id NUMBER;
2613 l_top_config_rev_nbr NUMBER;
2614 l_header_id NUMBER;
2615 l_change_flag VARCHAR2(30);
2616 L_line_tbl OE_Order_Pub.Line_Tbl_Type;
2617 l_config_header_id NUMBER;
2618 l_config_rev_nbr NUMBER;
2619 l_valid_config VARCHAR2(10);
2620 l_complete_config VARCHAR2(10);
2621 l_xml_str LONG := NULL;
2622 Begin
2623
2624 --IF the ship_to_site_use_id and bill_to_site_use_id has NULL value
2625 --for all records returned by IB, we remember this and pass the
2626 --G_CONFIG_INTSTANCE_TBL to Process_Config to improve performance
2627
2628 oe_debug_pub.add('Entering Populate_macd_action procedure',1);
2629 x_return_status := FND_API.G_RET_STS_SUCCESS;
2630 IF p_instance_tbl.count > 0 THEN
2631
2632 l_instance_tbl := p_instance_tbl;
2633 I := l_instance_tbl.FIRST;
2634
2635 While I IS NOT NULL LOOP
2636 BEGIN
2637
2638 -- Code should be added to identify the existance of the container model record.
2639 -- Loop through the table, identify the container model item from cz tables
2640 -- and check the existance of the record in OM table from the given header_id.
2641
2642 IS_container_present(
2643 P_header_id => p_header_id
2647
2644 ,p_config_instance_hdr_id => l_instance_tbl(I).config_instance_hdr_id
2645 ,p_config_instance_rev_number => l_instance_tbl(I).config_instance_rev_number
2646 ,x_top_model_line_id => l_top_model_line_id);
2648 oe_debug_pub.add('After is container: ' || l_top_model_line_id,2);
2649 -- After identifying the container model, loop through the instance table and
2650 -- populate the config table for validation.
2651
2652 IF l_top_model_line_id is not null THEN
2653
2654 J := l_instance_tbl.NEXT(I);
2655
2656 L_parent_exists_instance_tbl (l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
2657
2658 -- Instance record will be validated for the given action and see whether the action is
2659 -- applicable and also system finds the current config_header_id (session header id) so that
2660 -- can be passed to CZ for the batch validation.
2661
2662 oe_debug_pub.add('Before Validate Action' || x_return_status,2);
2663 Validate_action
2664 (p_top_model_line_id => l_top_model_line_id,
2665 p_instance_item_id => l_instance_tbl(I).config_instance_item_id,
2666 p_macd_action => nvl(p_macd_action,l_instance_tbl(I).action),
2667 x_config_item_id => l_config_item_id,
2668 x_component_code => l_component_code,
2669 x_return_status => x_return_status);
2670
2671 oe_debug_pub.add('After Validate Action' || x_return_status,2);
2672 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2674 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2675 RAISE FND_API.G_EXC_ERROR;
2676 END IF;
2677 l_config_item_rec.config_item_id := l_config_item_id;
2678 l_config_item_rec.component_code := l_component_code;
2679
2680 IF nvl(p_macd_action, l_instance_tbl(I).action) = 'UPDATE' THEN
2681 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
2682 Elsif nvl(p_macd_action, l_instance_tbl(I).action) = 'DELETE' THEN
2683 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2684 Elsif nvl(p_macd_action, l_instance_tbl(I).action) = 'DISCONTINUE' THEN
2685 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2686 End if;
2687
2688
2689 l_config_item_rec.instance_name := l_instance_tbl(I).instance_name;
2690 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
2691 oe_debug_pub.add('Before getting the data from P_Extended_Attrib_Tbl: ' ||
2692 P_Extended_Attrib_Tbl.count,2);
2693 For K in 1..P_Extended_Attrib_Tbl.count Loop
2694 oe_debug_pub.add('In the ext loop: ' || K,2);
2695 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = I then
2696
2697 l_config_attr_rec.config_item_id := l_config_item_id;
2698 l_config_attr_rec.component_code := l_component_code;
2699 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
2700 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
2701 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
2702 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
2703
2704 END IF; -- index xomparison.
2705
2706 END LOOP;
2707 oe_debug_pub.add('Before J loop: ' || J,2);
2708 While J IS NOT NULL
2709 LOOP
2710
2711 oe_debug_pub.add('Inside the J loop ' || J,2);
2712 IF l_instance_tbl(J).config_instance_hdr_id = l_instance_tbl(I).config_instance_hdr_id
2713 AND l_instance_tbl(J).config_instance_rev_number = l_instance_tbl(I).config_instance_rev_number THEN
2714
2715 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(J);
2716
2717 oe_debug_pub.add(' 1 Before Validate Action' || x_return_status,2);
2718 Validate_action
2719 (p_top_model_line_id => l_top_model_line_id,
2720 p_instance_item_id => l_instance_tbl(J).config_instance_item_id,
2721 p_macd_action => nvl(p_macd_action, l_instance_tbl(J).action),
2722 x_config_item_id => l_config_item_id,
2723 x_component_code => l_component_code,
2724 x_return_status => x_return_status);
2725
2726 oe_debug_pub.add(' 1 After Validate Action' || x_return_status,2);
2727 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2729 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2730 RAISE FND_API.G_EXC_ERROR;
2731 END IF;
2732 l_config_item_rec.config_item_id := l_config_item_id;
2733 l_config_item_rec.component_code := l_component_code;
2734
2735 IF nvl(p_macd_action, l_instance_tbl(j).action) = 'UPDATE' THEN
2736 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
2737 ELSIF nvl(p_macd_action, l_instance_tbl(j).action) = 'DELETE' THEN
2738 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2739 ELSIF nvl(p_macd_action, l_instance_tbl(j).action) = 'DISCONTINUE' THEN
2740 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
2741 End if;
2742
2743 l_config_item_rec.instance_name := l_instance_tbl(J).instance_name;
2744 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
2745
2746
2747 -- Populate corresponding extended attributes into cz extented table.
2748 For K in 1..P_Extended_Attrib_Tbl.count Loop
2749
2750 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = J then
2751
2752 l_config_attr_rec.config_item_id := l_config_item_id;
2753 l_config_attr_rec.component_code := l_component_code;
2754 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
2755 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
2756 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
2757 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
2758
2759 END IF; -- index xomparison.
2760
2761 END Loop; -- K loop
2762
2763
2764 l_instance_tbl.DELETE(J);
2765 END IF; -- After Mai Loop
2766 J := l_instance_tbl.NEXT(J);
2767 oe_debug_pub.add('Processing next instance ' || J);
2768 END LOOP; -- J Loop
2769 l_instance_tbl.DELETE(I);
2770
2771 -- Call macd batch validate API to validate the actions for the given instances.
2772
2773 oe_debug_pub.add('Before calling the oe_config_util.Create_hdr_xml: ' || l_top_model_line_id,3);
2774
2775 oe_debug_pub.add('Before calling Create_hdr_xml',1);
2776 oe_config_util.Create_hdr_xml
2777 ( p_model_line_id => l_top_model_line_id ,
2778 x_xml_hdr => l_init_msg);
2779
2780
2781 oe_debug_pub.add('Before calling CZ_CF_API.VALIDATE',2);
2782 CZ_CF_API.VALIDATE
2783 (p_api_version => 1.0
2784 ,p_config_item_tbl => l_config_item_tbl
2785 ,p_config_ext_attr_tbl => l_config_attr_tbl
2786 ,p_url => l_url
2787 ,p_init_msg => l_init_msg
2788 ,p_validation_type => l_validation_type
2789 ,x_config_xml_msg => l_config_xml_msg
2790 ,x_return_status => x_return_status
2791 ,x_msg_count => x_msg_count
2792 ,x_msg_data => x_msg_data);
2793
2794 OE_MSG_PUB.Transfer_Msg_Stack;
2795 oe_debug_pub.add('After calling CZ_CF_API.VALIDATE: ' || x_return_status,2);
2796 oe_debug_pub.add('x_msg_data: ' || x_msg_data,2);
2797
2798 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2800 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2801 RAISE FND_API.G_EXC_ERROR;
2802 END IF;
2803 -- Handle the return status from the call. Raise an exception accordingly.
2804
2805 -- extract data from xml message.
2806
2807 IF l_debug_level > 0 THEN
2808 oe_debug_pub.add('CALLING PARSE_OUTPUT_XML: ' || l_config_xml_msg.COUNT, 2 );
2809 END IF;
2810
2811 IF (l_config_xml_msg.COUNT > 0) THEN
2812
2813 FOR xmlStr IN l_config_xml_msg.FIRST..l_config_xml_msg.LAST
2814 LOOP
2815 l_xml_str := l_xml_str||l_config_xml_msg(xmlStr);
2816 oe_debug_pub.add(' Row count ' || xmlStr,2);
2817 END LOOP;
2818
2819 l_xml_str := UPPER(l_xml_str);
2820
2821 oe_debug_pub.add(' Out Message '|| l_xml_str,2);
2822 oe_config_util.Parse_Output_xml
2823 ( p_xml => l_xml_str,
2824 p_line_id => l_top_model_line_id,
2825 x_valid_config => l_valid_config,
2826 x_complete_config => l_complete_config,
2827 x_config_header_id => l_top_config_header_id,
2828 x_config_rev_nbr => l_top_config_rev_nbr,
2829 x_return_status => x_return_status );
2830
2831 END IF;
2832 IF l_debug_level > 0 THEN
2833 oe_debug_pub.add('AFTER CALLING PARSE_XML: '||x_RETURN_STATUS , 2 );
2834 END IF;
2835
2836 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2838 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2839 RAISE FND_API.G_EXC_ERROR;
2840 END IF;
2841 -- Need to call the process_config api to synch up the order linesto onfiguration changes resulted due to the batch validation call.
2842 /*
2843 Begin
2844
2845 Select config_header_id, config_rev_nbr
2846 Into l_top_config_header_id, l_top_config_rev_nbr
2847 From oe_order_lines_all
2848 Where line_id = l_top_model_line_id;
2849
2850 EXCEPTION
2851 WHEN OTHERS THEN
2852 oe_debug_pub.add(' Line SELECT: '|| SQLERRM , 1 ) ;
2853 RAISE FND_API.G_EXC_ERROR;
2854 -- Exception handler
2855
2856 End;
2857 */
2858 oe_debug_pub.add('Before calling process config',2);
2859 OE_CONFIG_PVT.Process_Config
2860 (p_header_id => l_header_id
2861 ,p_config_hdr_id => l_top_config_header_id
2862 ,p_config_rev_nbr => l_top_config_rev_nbr
2863 ,p_top_model_line_id => l_top_model_line_id
2864 ,p_ui_flag => 'Y'
2865 ,x_change_flag => l_change_flag
2866 ,x_msg_count => x_msg_count
2867 ,x_msg_data => x_msg_data
2868 ,x_return_status => x_return_status);
2869
2870 oe_debug_pub.add('After calling Process Config: ' || x_return_status,2);
2871 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2873 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2874 RAISE FND_API.G_EXC_ERROR;
2875 END IF;
2879 l_parent_exists_instance_tbl.delete;
2876 -- Delete the parent table and also clear the top model so that we do not carry the value.
2877
2878 l_top_model_line_Id := null;
2880 Goto End_loop;
2881
2882 END IF; -- top model
2883
2884
2885 <<End_Loop>>
2886 I := L_instance_tbl.Next(I);
2887 END;
2888 END LOOP;
2889
2890
2891 ELSE -- Process the data passed through the lines table.
2892
2893 --Line table logic
2894
2895 L_line_tbl := p_x_line_tbl;
2896
2897 -- Add the logic to call process order so that all the updates are done before processing
2898 -- any action sent along with the lines table. We will loop through the lines table and adjust
2899 -- the operation so that process order can take the action on those. We will not pass the discontinue
2900 -- operation to process order as discontinue is not a valid operation for process order.
2901
2902 For M in 1..l_line_tbl.count LOOP
2903
2904
2905 IF nvl(p_macd_action, l_line_tbl(M).operation) in ('DELETE', 'DISCONTINUE') THEN
2906
2907 -- If the config details are passed on the line record copy the same to local variables.
2908 -- Or else query the config details for the lines table.
2909
2910 l_config_header_id := l_line_tbl(M).config_header_id;
2911 l_config_rev_nbr := l_line_tbl(M).config_rev_nbr;
2912 l_config_item_id := l_line_tbl(M).configuration_id;
2913
2914 IF l_config_header_id is null
2915 OR l_config_rev_nbr is null
2916 OR L_config_item_id is null THEN
2917
2918 Begin
2919
2920 Select config_header_id, config_rev_nbr, configuration_id
2921 Into l_config_header_id, l_config_rev_nbr, l_config_item_id
2922 From oe_order_lines_all
2923 Where line_id = l_line_tbl(M).line_id;
2924
2925 L_line_tbl(M).config_header_id := l_config_header_id;
2926 L_line_tbl(M).config_rev_nbr := l_config_rev_nbr;
2927 L_line_tbl(M).configuration_id := l_config_item_id;
2928
2929
2930 Exception
2931 WHEN OTHERS THEN
2932
2933 Null;
2934
2935 End;
2936
2937 END IF;
2938 Validate_line_action
2939 (p_line_id => l_line_tbl(M).line_id,
2940 p_config_header_id => l_config_header_id,
2941 p_config_rev_nbr => l_config_rev_nbr,
2942 P_config_item_id => l_config_item_id,
2943 P_macd_action => nvl(p_macd_action, l_line_tbl(M).operation),
2944 x_return_status => x_return_status);
2945
2946 -- Handle the return status and change the operation accordingly.
2947
2948 IF x_return_status = 'ERRROR' then
2949
2950 l_line_tbl(M).operation := 'NONE';
2951
2952 ELSIF L_line_tbl(M).operation = 'DISCONTINUE' Then
2953
2954 l_line_tbl(M).operation := 'NONE';
2955
2956 END IF;
2957
2958
2959 END IF; -- Delete;
2960 END LOOP;
2961
2962
2963 -- After the action validation we will call process order to update any changes passed in.
2964
2965 OE_CONFIG_PVT.Call_Process_Order
2966 (p_line_tbl => l_line_tbl
2967 ,p_control_rec => l_control_rec
2968 ,x_return_status => x_return_status);
2969
2970
2971 -- Handle the exception of the process order here. If there were no exception then process the macd action.
2972
2973 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2975 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2976 RAISE FND_API.G_EXC_ERROR;
2977 END IF;
2978
2979 I := l_line_tbl.FIRST;
2980
2981 While I IS NOT NULL
2982 LOOP
2983
2984 -- Code should be added to identify the existance of the container model record.
2985 -- Loop through the table, identify the container model item from cz tables
2986 -- and check the existance of the record in OM table from the given header_id.
2987
2988 IS_container_present(
2989 P_header_id => p_header_id
2990 ,p_config_instance_hdr_id => l_line_tbl(I).config_header_id
2991 ,p_config_instance_rev_number => l_line_tbl(I).config_rev_nbr
2992 ,x_top_model_line_id => l_top_model_line_id);
2993
2994 -- After identifying the container model, loop through the instance table
2995 -- and populate the config table for validation.
2996
2997 IF l_top_model_line_id is not null THEN
2998
2999 J := l_instance_tbl.NEXT(I);
3000
3001 l_parent_exists_instance_tbl (l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
3002
3003 -- base line validation should be performed for the lines.
3004
3005 Validate_line_action
3006 (p_line_id => l_line_tbl(I).line_id,
3007 P_config_header_id => l_line_tbl(I).config_header_id,
3008 P_config_rev_nbr => l_line_tbl(I).config_rev_nbr,
3009 P_config_item_id => l_line_tbl(I).configuration_id,
3010 P_macd_action => nvl(p_macd_action, l_line_tbl(I).operation),
3011 X_return_status => x_return_status);
3012
3013 oe_debug_pub.add('3 After calling Validate_line_action' || x_return_status,2);
3014 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3017 RAISE FND_API.G_EXC_ERROR;
3018 END IF;
3019 -- Handle exceptions.
3020
3021 l_config_item_rec.config_item_id := l_line_tbl(I).configuration_id;
3022 l_config_item_rec.component_code := l_line_tbl(I).component_code;
3023
3024 IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
3025 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
3026 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
3027 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3028 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DISCONTINUE' THEN
3029 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3030 END IF;
3031
3032 --l_config_item_rec.instance_name := l_line_tbl(I).instance_name;
3033 --l_config_item_rec.sequence_nbr := l_line_tbl(I).config_input_sequence;
3034 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
3035
3036 For K in 1..P_Extended_Attrib_Tbl.count Loop
3037
3038 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = I then
3039
3040 l_config_attr_rec.config_item_id := l_config_item_id;
3041 l_config_attr_rec.component_code := l_component_code;
3042 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
3043 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
3044 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
3045 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
3046
3047 END IF; --index xomparison.
3048
3049 END LOOP;
3050 While J IS NOT NULL
3051 LOOP
3052
3053 IF l_instance_tbl(J).config_instance_hdr_id = l_line_tbl(I).config_header_id
3054 AND l_line_tbl(J).config_rev_nbr = l_instance_tbl(I).config_instance_rev_number THEN
3055
3056 l_parent_exists_instance_tbl(l_parent_exists_instance_tbl.count + 1) :=
3057 l_instance_tbl(J);
3058
3059 Validate_line_action
3060 (p_line_id => l_line_tbl(j).line_id,
3061 p_config_header_id => l_line_tbl(j).config_header_id,
3062 p_config_rev_nbr => l_line_tbl(j).config_rev_nbr,
3063 p_config_item_id => l_line_tbl(j).configuration_id,
3064 p_macd_action => nvl(p_macd_action,l_line_tbl(j).operation),
3065 x_return_status => x_return_status);
3066 oe_debug_pub.add('4 After calling Validate_line_action' || x_return_status,2);
3067 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3069 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3070 RAISE FND_API.G_EXC_ERROR;
3071 END IF;
3072
3073 l_config_item_rec.config_item_id := l_line_tbl(j).configuration_id;
3074 l_config_item_rec.component_code := L_line_tbl(J).component_code;
3075 IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
3076 l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
3077 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
3078 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3079 ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DISCONTINUE' THEN
3080 l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
3081 End if;
3082 l_config_item_rec.instance_name := l_instance_tbl(J).instance_name;
3083 l_config_item_tbl(l_config_item_tbl.count+1) := l_config_item_rec;
3084
3085
3086 -- Populate corresponding extended attributes into cz extented table.
3087 For K in 1..P_Extended_Attrib_Tbl.count Loop
3088
3089 IF P_Extended_Attrib_Tbl(K).parent_tbl_index = J then
3090
3091 l_config_attr_rec.config_item_id := l_config_item_id;
3092 l_config_attr_rec.component_code := l_component_code;
3093 l_config_attr_rec.sequence_nbr := P_Extended_Attrib_Tbl(k).attribute_sequence;
3094 l_config_attr_rec.attribute_name := P_Extended_Attrib_Tbl(k).attribute_code;
3095 l_config_attr_rec.attribute_value := P_Extended_Attrib_Tbl(k).attribute_value;
3096 l_config_attr_tbl(l_config_attr_tbl.count+1) := l_config_attr_rec;
3097
3098 END IF; -- index xomparison.
3099
3100 END Loop;
3101
3102 l_instance_tbl.DELETE(J);
3103
3104 END IF;
3105 J := l_instance_tbl.NEXT(J);
3106 oe_debug_pub.add('1 Processing next instance ' || J);
3107 END LOOP;
3108 l_instance_tbl.DELETE(I);
3109
3110 oe_debug_pub.add('1 Before calling Create_hdr_xml',1);
3111 oe_config_util.Create_hdr_xml
3112 ( p_model_line_id => l_top_model_line_id ,
3113 x_xml_hdr => l_init_msg);
3114 -- Call macd batch validate API to validate the actions for the given instances.
3115
3116 CZ_CF_API.VALIDATE
3117 (p_api_version => 1.0
3118 ,p_config_item_tbl => l_config_item_tbl
3122 ,p_validation_type => l_validation_type
3119 ,p_config_ext_attr_tbl => l_config_attr_tbl
3120 ,p_url => l_url
3121 ,p_init_msg => l_init_msg
3123 ,x_config_xml_msg => l_config_xml_msg
3124 ,x_return_status => x_return_status
3125 ,x_msg_count => x_msg_count
3126 ,x_msg_data => x_msg_data);
3127
3128 oe_debug_pub.add('2 After calling CZ_CF_API.VALIDATE' || x_return_status,2);
3129 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3131 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3132 RAISE FND_API.G_EXC_ERROR;
3133 END IF;
3134 -- Handle the return status from the call. Raise an exception accordingly.
3135
3136 IF (l_config_xml_msg.COUNT > 0) THEN
3137 oe_debug_pub.add('2 Count is greater: ' || l_config_xml_msg.COUNT,2);
3138 FOR xmlStr IN l_config_xml_msg.FIRST..l_config_xml_msg.LAST
3139 LOOP
3140 l_xml_str := l_xml_str||l_config_xml_msg(xmlStr);
3141 END LOOP;
3142
3143 l_xml_str := Upper(l_xml_str);
3144 oe_config_util.Parse_Output_xml
3145 ( p_xml => l_xml_str,
3146 p_line_id => l_top_model_line_id,
3147 x_valid_config => l_valid_config,
3148 x_complete_config => l_complete_config,
3149 x_config_header_id => l_top_config_header_id,
3150 x_config_rev_nbr => l_top_config_rev_nbr,
3151 x_return_status => x_return_status );
3152
3153 END IF;
3154
3155 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3157 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3158 RAISE FND_API.G_EXC_ERROR;
3159 END IF;
3160 -- Need to call the process_config api to synch up the order lines to onfiguration changes
3161 -- resulted due to the batch validation call.
3162 /*
3163 Begin
3164
3165 Select config_header_id, config_rev_nbr
3166 Into l_top_config_header_id, l_top_config_rev_nbr
3167 From oe_order_lines_all
3168 Where line_id = l_top_model_line_id;
3169
3170 Exception
3171 When OTHERS THEN
3172 Null;
3173
3174 End;
3175 */
3176 OE_CONFIG_PVT.Process_Config
3177 (p_header_id => l_header_id
3178 ,p_config_hdr_id => l_top_config_header_id
3179 ,p_config_rev_nbr => l_top_config_rev_nbr
3180 ,p_top_model_line_id => l_top_model_line_id
3181 ,p_ui_flag => 'Y'
3182 ,x_change_flag => l_change_flag
3183 ,x_msg_count => x_msg_count
3184 ,x_msg_data => x_msg_data
3185 ,x_return_status => x_return_status );
3186
3187 oe_debug_pub.add('After calling Process Config ' || x_return_status,2);
3188
3189 -- Delete the parent table and also clear the top model so that we do not carry the value.
3190 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3192 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3193 RAISE FND_API.G_EXC_ERROR;
3194 END IF;
3195
3196 l_top_model_line_Id := null;
3197 l_parent_exists_instance_tbl.delete;
3198
3199 END IF;
3200
3201 END LOOP;
3202
3203 END IF; -- Main if of p_instance_tbl count.
3204
3205
3206 -- Get message count and data
3207
3208 OE_MSG_PUB.Count_And_Get
3209 ( p_count => x_msg_count
3210 , p_data => x_msg_data
3211 );
3212 Oe_debug_pub.add('Before exiting populate macd action : ' || x_return_status,1);
3213 Exception
3214 WHEN FND_API.G_EXC_ERROR THEN
3215 IF l_debug_level > 0 THEN
3216 OE_DEBUG_PUB.Add('Expected Error in Populate_macd_action:'
3217 ||sqlerrm,3);
3218 END IF;
3219 x_return_status := FND_API.G_RET_STS_ERROR;
3220
3221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3222 IF l_debug_level > 0 THEN
3223 OE_DEBUG_PUB.Add('Unexpected Error Populate_macd_action:'
3224 ||sqlerrm,3);
3225 END IF;
3226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3227
3228 WHEN OTHERS THEN
3229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3230 IF l_debug_level > 0 THEN
3231 OE_DEBUG_PUB.Add('Other error in Populate_macd_action:'
3232 ||sqlerrm,1);
3233 END IF;
3234 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3235 OE_MSG_PUB.Add_Exc_Msg
3236 ( G_PKG_NAME
3237 ,'Populate_macd_action' );
3238 END IF;
3239
3240
3241 END Populate_macd_action;
3242
3243 -- This procedure verifies the existance of the parent line for the given instance header.
3244 -- If the header is present, system returns the top_model or else top_model would be null.
3245 -- Frist the get the config_hdr and rev for the given instance details and then look for the
3246 -- top model in the given header for the fetched item.
3247
3248 Procedure IS_CONTAINER_PRESENT
3249 (P_header_id IN NUMBER
3250 ,p_config_instance_hdr_id IN NUMBER
3251 ,p_config_instance_rev_number IN NUMBER
3252 ,x_top_model_line_id OUT NOCOPY NUMBER)
3253
3254 IS
3255
3256 l_item_id Number;
3257 l_config_hdr_id Number;
3258 l_config_rev_nbr Number;
3259 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3260 BEGIN
3261 oe_debug_pub.add('Entering IS_CONTAINER_PRESENT: ' || p_config_instance_hdr_id,2);
3262 Begin
3263
3264 /*
3265 Select cz.config_hdr_id, cz.config_rev_nbr, substr(cz.component_code, 1,instr(cz.component_code,'-')-1),
3266 oe.top_model_line_id
3267 Into l_config_hdr_id, l_config_rev_nbr, L_item_id,x_top_model_line_id
3268 from cz_config_details_v cz, oe_order_lines_all oe
3269 where cz.instance_hdr_id = p_config_instance_hdr_id
3270 and oe.config_header_id = cz.config_hdr_id
3271 and oe.config_rev_nbr = cz.config_rev_nbr
3272 and oe.top_model_line_id = oe.line_id
3273 and oe.header_id = p_header_id
3274 and component_instance_type = 'I'
3275 and rownum = 1;
3276 */
3277
3278 Select config_hdr_id, config_rev_nbr, substr(component_code, 1,instr(component_code,'-')-1)
3279 Into l_config_hdr_id, l_config_rev_nbr, L_item_id
3280 from cz_config_details_v
3281 where instance_hdr_id = p_config_instance_hdr_id
3282 and instance_rev_nbr = p_config_instance_rev_number
3283 and component_instance_type = 'I';
3284
3285 Exception
3286 WHEN OTHERS THEN
3287 oe_debug_pub.add('In when Others of cz_config query',2);
3288 Return;
3289
3290 END;
3291 oe_debug_pub.add('Top model Present: ' || l_item_id,2);
3292 IF l_item_id is not null
3293 THEN
3294
3295 Begin
3296
3297 Select top_model_line_id
3298 Into x_top_model_line_id
3299 From oe_order_lines_all
3300 Where header_id = p_header_id
3301 -- And config_header_id = l_config_hdr_id
3302 -- And config_rev_nbr = l_config_rev_nbr
3303 AND open_flag = 'Y'
3304 AND inventory_item_id = l_item_id
3305 And top_model_line_id = line_id
3306 AND rownum = 1;
3307
3308 Exception
3309 WHEN OTHERS THEN
3310 oe_debug_pub.add('In when Others of oe_order_lines query',2);
3311 Null;
3312 --RETURN;
3313 End;
3314 END IF;
3315 oe_debug_pub.add('Exiting IS_Container_Present: ' || x_top_model_line_id,2);
3316 Exception
3317 WHEN FND_API.G_EXC_ERROR THEN
3318 IF l_debug_level > 0 THEN
3319 OE_DEBUG_PUB.Add('Expected Error in IS_Container_Present::'
3320 ||sqlerrm,3);
3321 END IF;
3322
3323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3324 IF l_debug_level > 0 THEN
3325 OE_DEBUG_PUB.Add('Unexpected Error IS_Container_Present::'
3326 ||sqlerrm,3);
3327 END IF;
3328
3329 WHEN OTHERS THEN
3330 IF l_debug_level > 0 THEN
3331 OE_DEBUG_PUB.Add('Other error in IS_Container_Present::'
3332 ||sqlerrm,1);
3333 END IF;
3334 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3335 OE_MSG_PUB.Add_Exc_Msg
3336 ( G_PKG_NAME
3337 ,'Validate Action' );
3338 END IF;
3339
3340 End IS_Container_Present;
3341
3342
3343 -- This procedure will validate the action passed by the caller against the line.
3344 -- We will fetch the line details corresponding to the instance information.
3345 -- If the validation does not go through then the call will be failed.
3346
3347 Procedure Validate_action
3348 (p_top_model_line_id IN NUMBER
3349 ,P_instance_item_id IN NUMBER
3350 ,P_macd_action IN VARCHAR2
3351 ,x_config_item_id OUT NOCOPY NUMBER
3352 ,x_component_code OUT NOCOPY VARCHAR2
3353 ,x_return_status OUT NOCOPY VARCHAR2)
3354
3355
3356 IS
3357 L_BASELINE_REV_NBR NUMBER(9);
3358 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3359 BEGIN
3360
3361 -- In case of DELETE operation on order line with a baseline rev number > 0 , OM will error out.
3362 -- This is because CZ will not support REVERT action in this phase.
3363
3364 oe_debug_pub.add('Entering validate action: ' || p_macd_action,2);
3365
3366 x_return_status := FND_API.G_RET_STS_SUCCESS;
3367
3368
3369 IF p_macd_action in ('DELETE', 'DISCONTINUE') then
3370
3371 Begin
3372
3373 SELECT cz_hdr.baseline_rev_nbr
3374 INTO l_baseline_rev_nbr
3375 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
3376 cz_config_details_v czv
3377 WHERE oe_line.top_model_line_id = p_top_model_line_id
3378 AND oe_line.configuration_id = P_instance_item_id
3379 AND czv.config_hdr_id = oe_line.config_header_id
3380 AND czv.config_rev_nbr = oe_line.config_rev_nbr
3381 AND czv.config_item_id = oe_line.configuration_id
3382 AND cz_hdr.config_hdr_id = czv.instance_hdr_id
3383 AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
3384 AND cz_hdr.baseline_rev_nbr IS NOT NULL
3388 oe_debug_pub.add('Base line rev number: ' || l_baseline_rev_nbr,2);
3385 AND rownum = 1;
3386
3387
3389
3390 IF l_baseline_rev_nbr > 0 AND
3391 p_macd_action = 'DELETE' THEN
3392 oe_debug_pub.add('Before raising error',2);
3393 RAISE FND_API.G_EXC_ERROR;
3394 END IF;
3395
3396 Exception
3397 WHEN NO_DATA_FOUND THEN
3398 IF p_macd_action = 'DISCONTINUE' THEN
3399 oe_debug_pub.add('Before raising error no data found',2);
3400 RAISE FND_API.G_EXC_ERROR;
3401 END IF;
3402 End;
3403
3404 End if;
3405 BEGIN
3406
3407 SELECT configuration_id, component_code
3408 INTO x_config_item_id, x_component_code
3409 FROM oe_order_lines_all oe_line
3410 WHERE oe_line.top_model_line_id = p_top_model_line_id
3411 AND oe_line.configuration_id = p_instance_item_id
3412 AND rownum = 1;
3413
3414 EXCEPTION
3415
3416 WHEN OTHERS THEN
3417 Null;
3418 END;
3419 oe_debug_pub.add('component_code ' || x_component_code,1);
3420 oe_debug_pub.add('Before exiting Validate action' || x_return_status,1);
3421 Exception
3422 WHEN FND_API.G_EXC_ERROR THEN
3423 IF l_debug_level > 0 THEN
3424 OE_DEBUG_PUB.Add('Expected Error in validate_action:'
3425 ||sqlerrm,3);
3426 END IF;
3427 x_return_status := FND_API.G_RET_STS_ERROR;
3428
3429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3430 IF l_debug_level > 0 THEN
3431 OE_DEBUG_PUB.Add('Unexpected Error validate_action:'
3432 ||sqlerrm,3);
3433 END IF;
3434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3435
3436 WHEN OTHERS THEN
3437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3438 IF l_debug_level > 0 THEN
3439 OE_DEBUG_PUB.Add('Other error in validate_action:'
3440 ||sqlerrm,1);
3441 END IF;
3442 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3443 OE_MSG_PUB.Add_Exc_Msg
3444 ( G_PKG_NAME
3445 ,'Validate Action' );
3446 END IF;
3447 END Validate_action;
3448
3449
3450 -- This procedure will validate the action passed by the caller against the line.
3451 -- We will fetch the line details corresponding to the instance information.
3452 -- If the validation does not go through then the call will be failed.
3453
3454 Procedure Validate_line_action
3455 (p_line_id IN NUMBER
3456 ,P_config_header_id IN NUMBER
3457 ,P_config_rev_nbr IN NUMBER
3458 ,P_config_item_id IN NUMBER
3459 ,P_macd_action IN NUMBER
3460 ,x_return_status OUT NOCOPY VARCHAR2)
3461
3462 IS
3463 l_baseline_rev_nbr NUMBER(9);
3464 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3465 BEGIN
3466
3467 -- This is very similar to validateaction. In case of DELETE operation on order
3468 -- line with a baseline rev number > 0 , OM will error out.
3469 -- This is because CZ will not support REVERT action in this phase.
3470
3471 oe_debug_pub.add('Enetering validate line action ' || p_macd_action,2);
3472 x_return_status := FND_API.G_RET_STS_SUCCESS;
3473
3474 If p_macd_action in ('DELETE', 'DISCONTINUE') then
3475
3476 Begin
3477 SELECT cz_hdr.baseline_rev_nbr
3478 INTO l_baseline_rev_nbr
3479 FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
3480 cz_config_details_v czv
3481 WHERE oe_line.line_id = p_line_id
3482 AND czv.config_hdr_id = oe_line.config_header_id
3483 AND czv.config_rev_nbr = oe_line.config_rev_nbr
3484 AND czv.config_item_id = oe_line.configuration_id
3485 AND cz_hdr.config_hdr_id = czv.instance_hdr_id
3486 AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
3487 AND cz_hdr.baseline_rev_nbr IS NOT NULL
3488 AND rownum = 1;
3489
3490 oe_debug_pub.add('Base line rev number : ' || l_baseline_rev_nbr,2);
3491 IF l_baseline_rev_nbr > 0 AND
3492 p_macd_action = 'DELETE' THEN
3493 oe_debug_pub.add('Base line rev number greater than 0',2);
3494 RAISE FND_API.G_EXC_ERROR;
3495 END IF;
3496
3497 Exception
3498 WHEN NO_DATA_FOUND THEN
3499 Null;
3500 End;
3501
3502 End if;
3503 oe_debug_pub.add('Exiting validate_line_action',2);
3504 Exception
3505 WHEN FND_API.G_EXC_ERROR THEN
3506 IF l_debug_level > 0 THEN
3507 OE_DEBUG_PUB.Add('Expected Error in Validate_line_action:'
3508 ||sqlerrm,3);
3509 END IF;
3510 x_return_status := FND_API.G_RET_STS_ERROR;
3511
3512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3513 IF l_debug_level > 0 THEN
3514 OE_DEBUG_PUB.Add('Unexpected Error Validate_line_action:'
3515 ||sqlerrm,3);
3516 END IF;
3517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3518
3519 WHEN OTHERS THEN
3520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3521 IF l_debug_level > 0 THEN
3522 OE_DEBUG_PUB.Add('Other error in validate_action:'
3523 ||sqlerrm,1);
3524 END IF;
3525 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3526 OE_MSG_PUB.Add_Exc_Msg
3527 ( G_PKG_NAME
3528 ,'Validate_line_action' );
3529 END IF;
3530 END Validate_line_action;
3531
3532
3533 END OE_CONFIG_TSO_PVT;
[Home] [Help]