[Home] [Help]
PACKAGE BODY: APPS.OE_SPLIT_UTIL
Source
1 PACKAGE BODY OE_split_util AS
2 /* $Header: OEXUSPLB.pls 120.26.12020000.10 2013/04/15 06:20:29 jialiali ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Split_Util';
4 G_min_model NUMBER;
5 G_max_model NUMBER;
6 g_over_shipment boolean := false;
7 g_remnant_only Boolean := FALSE;
8 g_qry_out_rec OE_ORDER_PUB.Line_rec_Type := OE_ORDER_PUB.G_MISS_LINE_REC;
9 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; -- 8706868
10 Procedure Create_Line_Set_For_Options(p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type ) IS
11 --
12 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
13 --
14 BEGIN
15 IF l_debug_level > 0 THEN
16 oe_debug_pub.add( 'ENTER CREATE LINE SET FOR OPTIONS:' ) ;
17 END IF;
18 FOR I in 1..p_x_line_tbl.count LOOP
19 IF p_x_line_tbl(I).line_set_id is null AND
20 p_x_line_tbl(I).operation = oe_globals.g_opr_update THEN
21 IF l_debug_level > 0 THEN
22 oe_debug_pub.add( 'BEFORE CREATING SET : ' ||P_X_LINE_TBL ( I ) .LINE_ID ) ;
23 END IF;
24 IF l_debug_level > 0 THEN
25 oe_debug_pub.add( 'BEFORE CREATING SET : ' ||P_X_LINE_TBL ( I ) .ITEM_TYPE_CODE ) ;
26 END IF;
27 Oe_Set_Util.Create_Line_Set(p_x_line_rec => p_x_line_tbl(I));
28 END IF;
29 FOR J in 1..p_x_line_tbl.count LOOP
30 IF p_x_line_tbl(J).split_from_line_id = p_x_line_tbl(I).line_id AND
31 p_x_line_tbl(J).operation = oe_globals.g_opr_create THEN
32 IF l_debug_level > 0 THEN
33 oe_debug_pub.add( 'ADDING INTO SET:' ) ;
34 END IF;
35 p_x_line_tbl(J).line_Set_id := p_x_line_tbl(I).line_set_id;
36 END IF;
37 END LOOP;
38 END LOOP;
39 EXCEPTION
40 WHEN OTHERS THEN
41 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
42 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'Create_Line_Set_For_Options' );
43 END IF;
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END Create_Line_Set_For_Options;
46
47
48 Procedure Update_Drop_Ship_Source(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type) IS
49 --
50 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
51 --
52 BEGIN
53
54 FOR I in 1..p_line_tbl.count LOOP
55 BEGIN
56 IF (p_line_tbl(I).operation = oe_globals.g_opr_create AND
57 p_line_tbl(I).split_from_line_id IS NOT NULL AND
58 p_line_tbl(I).source_type_code = 'EXTERNAL' ) THEN
59 UPDATE oe_drop_ship_sources
60 SET line_id = p_line_tbl(I).line_id
61 WHERE line_id = p_line_tbl(I).split_from_line_id;
62 END IF;
63 EXCEPTION WHEN NO_DATA_FOUND THEN
64 Null;
65 WHEN OTHERS THEN
66 NULL;
67 END;
68 END LOOP;
69 End Update_Drop_Ship_Source;
70
71 Procedure Get_Nonprop_Service_lines(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type,
72 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_Tbl_Type)
73 IS
74 l_ser_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
75 l_ser_line_rec OE_ORDER_PUB.Line_rec_Type := OE_ORDER_PUB.G_MISS_LINE_rec;
76 l_line_id NUMBER;
77 l_service_count NUMBER := 0;
78 Cursor Sertbl IS
79 SELECT ORDERED_QUANTITY
80 , HEADER_ID
81 , LINE_ID
82 FROM OE_ORDER_LINES_ALL
83 WHERE SERVICE_REFERENCE_LINE_ID = l_line_id
84 AND ITEM_TYPE_CODE = 'SERVICE'
85 AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
86 AND OPEN_FLAG <> 'N' -- Bug 7555831 and 7555832
87 AND NVL(CANCELLED_FLAG,'N') <> 'Y';
88 --
89 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
90 --
91 BEGIN
92 IF l_debug_level > 0 THEN
93 oe_debug_pub.add('Enter non prop service lines',5);
94 OE_DEBUG_PUB.add('#5112495, select service reference type code ORDER only',1);
95 END IF;
96 x_line_tbl := p_line_tbl;
97 FOR I in 1 .. p_line_tbl.count LOOP
98 IF p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
99 l_line_id := p_line_tbl(I).line_id;
100 ELSE
101 l_line_id := p_line_tbl(I).split_from_line_id;
102 END IF;
103 FOR Ser_rec IN Sertbl LOOP
104 l_service_count := l_service_count + 1;
105 l_ser_line_tbl(l_service_count).line_id := ser_rec.line_id;
106 oe_line_util.query_row(ser_rec.line_id,x_line_rec => l_ser_line_rec);
107 l_ser_line_tbl(l_service_count) := l_ser_line_rec;
108 IF p_line_tbl(I).operation = OE_GLOBALS.G_OPR_CREATE THEN
109 l_ser_line_tbl(l_service_count).line_id := fnd_api.g_miss_num;
110 l_ser_line_tbl(l_service_count).split_from_line_id := l_ser_line_rec.line_id; --9111247
111 END IF;
112 IF p_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE THEN
113 l_ser_line_tbl(l_service_count).split_action_code := 'SPLIT';
114 /* Start Audit Trail */
115 l_ser_line_tbl(l_service_count).change_reason := 'SYSTEM';
116 /* End Audit Trail */
117 END IF;
118 l_ser_line_tbl(l_service_count).split_by := 'SYSTEM';
119 l_ser_line_tbl(l_service_count).header_id := ser_rec.header_id;
120 l_ser_line_tbl(l_service_count).ordered_quantity := p_line_tbl(I).ordered_quantity;
121 l_ser_line_tbl(l_service_count).operation := p_line_tbl(I).operation;
122 l_ser_line_tbl(l_service_count).service_reference_line_id := p_line_tbl(I).line_id;
123 END LOOP;
124 END LOOP;
125 IF l_debug_level > 0 THEN
126 oe_debug_pub.add('service lines',5) ;
127 END IF;
128 FOR I in 1..l_ser_line_tbl.count LOOP
129 IF l_debug_level > 0 THEN
130 oe_debug_pub.add(l_ser_line_tbl(I).item_type_code);
131 oe_debug_pub.add(l_ser_line_tbl(I).operation);
132 oe_debug_pub.add(l_ser_line_tbl(I).service_reference_line_id);
133 END IF;
134 END LOOP;
135 -- Populate Out table
136 l_service_count := p_line_tbl.count + 1;
137 FOR I in 1..l_ser_line_tbl.count LOOP
138 x_line_tbl(l_service_count) := l_ser_line_tbl(I);
139 l_service_count := l_service_count + 1;
140 END LOOP;
141 IF l_debug_level > 0 THEN
142 oe_debug_pub.add('Exit non service lines',5);
143 END IF;
144 EXCEPTION
145 WHEN OTHERS THEN
146 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
147 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME ,'Get_Non_Service_line' );
148 END IF;
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END Get_Nonprop_Service_Lines;
151
152 /* This is duplicated here since defaulting goes by g_line_rec%view and
153 has technical dificulties to get the currect shipment number */
154
155 FUNCTION Get_Shipment_Number(p_line_rec oe_order_pub.line_rec_type) RETURN NUMBER IS
156 l_ship_number NUMBER := NULL;
157 l_config_rec OE_ORDER_PUB.line_rec_type;
158 --
159 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
160 --
161
162 --9534576
163 l_line_set_id NUMBER ;
164 l_max_ship_number number ;
165 l_chk_ship_number NUMBER ;
166 i NUMBER;
167 CURSOR Models(p_line_set_id NUMBER) IS
168 SELECT line_id FROM oe_order_lines_all
169 WHERE line_set_id= p_line_set_id;
170 --9534576
171 BEGIN
172 IF l_debug_level > 0 THEN
173 oe_debug_pub.add( 'IN PKG OE_DEFAULT_LINE: FUNCTION GET_SHIPMENT_NUMBER' ) ;
174 END IF;
175 IF p_line_rec.top_model_line_id IS NULL
176 OR p_line_rec.top_model_line_id = FND_API.G_MISS_NUM THEN
177 SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
178 INTO l_ship_number
179 FROM OE_ORDER_LINES
180 WHERE HEADER_ID = p_line_rec.header_id
181 AND LINE_NUMBER = p_line_rec.line_number;
182 RETURN l_ship_number;
183
184 --START 9534576
185 ELSIF p_line_rec.model_Remnant_flag='Y'
186 AND p_line_rec.top_model_line_id IS NOT NULL
187 AND p_line_rec.split_from_line_id IS NOT NULL THEN
188 --first get the line set id of the top model line.
189 --based on the line set id, get all the line ids. that means get all the model lines,
190 -- that are part of the split.
191 --now for the option, component, service combination, get the max shipment number.
192
193
194 SELECT line_set_id
195 INTO l_line_set_id
196 FROM oe_order_lines
197 WHERE line_id=p_line_rec.top_model_line_id;
198 if l_line_set_id is null then
199
200 SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
201 INTO l_ship_number
202 FROM OE_ORDER_LINES
203 WHERE HEADER_ID = p_line_rec.header_id
204 AND LINE_NUMBER = p_line_rec.line_number
205 AND Nvl(option_number,-1)=Nvl(p_line_rec.option_number,-1)
206 AND Nvl(component_number,-1)=Nvl(p_line_rec.component_number,-1)
207 AND Nvl(service_number,-1)=Nvl(p_line_rec.service_number,-1);
208
209 RETURN l_ship_number;
210
211 else
212
213 FOR i IN models(l_line_set_id) LOOP
214 IF l_debug_level > 0 THEN
215 oe_debug_pub.add( 'for the mode line:'|| i.line_id) ;
216 END IF;
217
218 SELECT NVL(MAX(SHIPMENT_NUMBER),-1)
219 INTO l_chk_ship_number
220 FROM oe_order_lines
221 WHERE top_model_line_id=i.line_id
222 AND line_number=p_line_rec.line_number
223 AND Nvl(option_number,-1)=Nvl(p_line_rec.option_number,-1)
224 AND Nvl(component_number,-1)=Nvl(p_line_rec.component_number,-1)
225 AND Nvl(service_number,-1)=Nvl(p_line_rec.service_number,-1);
226
227
228 IF Nvl(l_chk_ship_number,-1) > Nvl(l_max_ship_number,-1) THEN
229
230 IF l_debug_level > 0 THEN
231 oe_debug_pub.add( 'checking the value of max shipment number'||i.line_id);
232 oe_debug_pub.add(l_max_ship_number);
233 oe_debug_pub.add(l_CHK_ship_number);
234 END IF;
235 l_max_ship_number:= l_chk_ship_number;
236 END IF;
237 END LOOP;
238 l_ship_number:= l_max_ship_number+1;
239 RETURN l_ship_number;
240 oe_debug_pub.add('returned value is:'||l_ship_number);
241 end if ;
242 --9534576
243
244 ELSE
245 -- 2605065: We will not use Cache info. Select direct from table.
246 SELECT shipment_number
247 INTO l_ship_number
248 FROM oe_order_lines
249 WHERE line_id = p_line_rec.top_model_line_id;
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'SHIPMENT_NUMBER '||l_ship_number,1 ) ;
252 END IF;
253 /*
254 l_config_rec := OE_Order_Cache.Load_Top_Model_Line(p_line_rec.top_model_line_id );
255 l_ship_number := l_config_rec.shipment_number;
256 */
257 RETURN l_ship_number;
258 END IF;
259 EXCEPTION
260 WHEN OTHERS THEN
261 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
262 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME ,'Get_Shipment_Number' );
263 END IF;
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END Get_Shipment_Number;
266
267 FUNCTION Check_Complete_Shipment(p_line_tbl IN OE_ORDER_PUB.line_tbl_type,
268 p_line_id IN number) RETURN VARCHAR2 IS
269 l_line_id number;
270 CURSOR OPTIONTBL IS
271 SELECT ORDERED_QUANTITY
272 , HEADER_ID
273 , LINE_ID
274 FROM OE_ORDER_LINES_ALL
275 WHERE TOP_MODEL_LINE_ID = l_line_id
276 AND LINE_ID <> l_line_id
277 AND NVL(SHIPPABLE_FLAG,'N')='Y';
278 l_line_tbl OE_ORDER_PUB.line_tbl_type := p_line_tbl;
279 l_exist varchar2(1) := 'N';
280 --
281 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
282 --
283 BEGIN
284 l_line_id := p_line_id;
285 FOR optionrec in optiontbl LOOP
286 IF l_debug_level > 0 THEN
287 oe_debug_pub.add( 'INTO OPTION TABLE-' ) ;
288 END IF;
289 l_exist := 'N';
290 FOR I in 1 .. l_line_tbl.count LOOP
291 IF l_debug_level > 0 THEN
292 oe_debug_pub.add( 'LINE_ID-' ||OPTIONREC.LINE_ID ) ;
293 END IF;
294 IF optionrec.line_id = l_line_tbl(I).line_id THEN
295 IF l_debug_level > 0 THEN
296 oe_debug_pub.add( 'EQUAL OPTION TABLE-' ) ;
297 END IF;
298 l_exist := 'Y';
299 EXIT;
300 END IF;
301 END LOOP;
302 IF l_exist = 'N' THEN
303 RETURN l_exist;
304 END IF;
305 END LOOP;
306 IF l_debug_level > 0 THEN
307 oe_debug_pub.add( 'RESULT-'||L_EXIST ) ;
308 END IF;
309 RETURN l_exist;
310 EXCEPTION WHEN OTHERS THEN
311 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
312 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Check_Complete_Shipment');
313 END IF;
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END Check_Complete_Shipment;
316
317 Procedure Get_Service_lines(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type,
318 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_Tbl_Type,
319 g_split_line_tbl oe_split_util.split_line_tbl) IS
320 l_ser_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
321 l_line_id NUMBER;
322 l_service_count NUMBER := 1;
323 l_ser_rec OE_ORDER_PUB.Line_rec_Type := OE_ORDER_PUB.G_MISS_LINE_REC;
324 l_query_out_rec OE_ORDER_PUB.Line_rec_Type := OE_ORDER_PUB.G_MISS_LINE_REC;
325
326 CURSOR Sertbl IS
327 SELECT ORDERED_QUANTITY
328 , HEADER_ID
329 , LINE_ID
330 FROM OE_ORDER_LINES_ALL
331 WHERE SERVICE_REFERENCE_LINE_ID = l_line_id
332 AND ITEM_TYPE_CODE = 'SERVICE'
333 AND SERVICE_REFERENCE_TYPE_CODE = 'ORDER'
334 AND OPEN_FLAG <> 'N' -- Bug 6710212
335 AND NVL(CANCELLED_FLAG,'N') <> 'Y';
336 --
337 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
338 --
339 BEGIN
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add('Enter get_service_lines()',5);
342 END IF;
343 x_line_tbl := p_line_tbl;
344 l_line_id := g_split_line_tbl(1).line_id;
345 FOR Ser_rec IN Sertbl LOOP
346 oe_line_util.query_row(ser_rec.line_id,x_line_rec => l_ser_rec);
347 -- for bug 2035100
348 IF p_line_tbl(1).split_by IS NOT NULL THEN
349 l_ser_rec.split_by := p_line_tbl(1).split_by;
350 END IF;
351 -- end 2035100
352 l_ser_line_tbl(l_service_count) := l_ser_rec;
353 /* Start Audit Trail */
354 l_ser_line_tbl(l_service_count).change_reason := 'SYSTEM';
355 l_ser_line_tbl(l_service_count).change_comments := 'Split line';
356 /* End Audit Trail */
357 l_ser_line_tbl(l_service_count).line_id := ser_rec.line_id;
358 l_ser_line_tbl(l_service_count).header_id := ser_rec.header_id;
359 l_ser_line_tbl(l_service_count).ordered_quantity := g_split_line_tbl(1).quantity;
360 l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_update;
361 l_ser_line_tbl(l_service_count).split_action_code := 'SPLIT';
362 l_service_count := l_service_count + 1;
363 FOR I in 2..g_split_line_tbl.count LOOP
364 l_ser_line_tbl(l_service_count) := l_ser_rec;
365 l_ser_line_tbl(l_service_count).line_id := fnd_api.g_miss_num;
366 l_ser_line_tbl(l_service_count).split_from_line_id := l_ser_rec.line_id;
367 l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_create;
368 g_qry_out_rec := l_ser_line_tbl(l_service_count) ;
369 OE_Split_Util.Default_Attributes(
370 p_x_line_rec =>g_qry_out_rec
371 , p_old_line_rec => l_ser_line_tbl(l_service_count)
372 );
373 l_ser_line_tbl(l_service_count) := g_qry_out_rec;
374 l_ser_line_tbl(l_service_count).header_id := ser_rec.header_id;
375 l_ser_line_tbl(l_service_count).service_reference_line_id := g_split_line_tbl(I).line_id;
376 l_ser_line_tbl(l_service_count).ordered_quantity := g_split_line_tbl(I).quantity;
377 l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_create;
378 l_ser_line_tbl(l_service_count).item_type_code:='SERVICE';
379 l_service_count := l_service_count + 1;
380 END LOOP;
381 END LOOP;
382
383 -- Populate Out table
384 l_service_count := p_line_tbl.count + 1;
385 FOR I in 1..l_ser_line_tbl.count LOOP
386 x_line_tbl(l_service_count) := l_ser_line_tbl(I);
387 l_service_count := l_service_count + 1;
388 END LOOP;
389
390 FOR I in 1..x_line_tbl.count LOOP
391 --While i is not null loop
392 IF l_debug_level > 0 THEN
393 oe_debug_pub.add('Quantity '||x_line_tbl(i).ordered_quantity,5);
394 oe_debug_pub.add('Operation '||x_line_tbl(i).operation,5);
395 oe_debug_pub.add('Item Type '||x_line_tbl(i).item_type_code,5) ;
396 oe_debug_pub.add('Line ID '||x_line_tbl(i).line_id,5);
397 oe_debug_pub.add('Split from line ID '||x_line_tbl(i).split_from_line_id,5);
398 oe_debug_pub.add('Line set '||x_line_tbl(i).line_set_id,5);
399 oe_debug_pub.add(x_line_tbl(i).split_action_code,5);
400 END IF;
401 --i:= l_line_tbl.next(i);
402 END LOOP;
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add('Exit service lines()',5 ) ;
405 END IF;
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
409 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Get_Service_line');
410 END IF;
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END Get_Service_lines;
413
414 Procedure Get_non_Model_Configuration(p_line_tbl IN OE_ORDER_PUB.line_tbl_type,
415 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.line_tbl_type) IS
416 l_line_id NUMBER;
417 l_line_tbl OE_ORDER_PUB.line_tbl_type := p_line_tbl;
418 l_reminant_tbl OE_ORDER_PUB.line_tbl_type;
419 l_shippable_tbl OE_ORDER_PUB.line_tbl_type;
420 l_shipped_tbl OE_ORDER_PUB.line_tbl_type;
421 l_temp_tbl OE_ORDER_PUB.line_tbl_type;
422 l_ratio NUMBER;
423 l_model_ratio NUMBER;
424 l_line_rec OE_ORDER_PUB.line_rec_type;
425 l_model_rec OE_ORDER_PUB.line_rec_type;
426 l_option_line OE_ORDER_PUB.line_rec_type;
427 l_parent_line OE_ORDER_PUB.line_rec_type;
428 l_option_line_tbl OE_ORDER_PUB.line_tbl_type;
429 l_tbl_count NUMBER := 0;
430 l_option_count NUMBER := 0;
431 l_original_qty NUMBER := 0;
432 l_min_model NUMBER := 0;
433 l_temp_min_model NUMBER := 0;
434 l_insert_quantity NUMBER := 0;
435 l_update_quantity NUMBER := 0;
436 option_updated BOOLEAN := FALSE;
437 l_exist BOOLEAN := FALSE;
438 l_parent_quantity NUMBER := 0;
439 l_Set_id NUMBER;
440 l_option_not_updated BOOLEAN := TRUE;
441 l_update_line_reqd BOOLEAN := TRUE;
442 l_Rem_top_model_line_id NUMBER;
443 l_actual_shipment_date Date;
444 l_ato_line_id number:=0; -- bug 14198118
445 CURSOR OPTIONTBL IS
446 SELECT ORDERED_QUANTITY,
447 HEADER_ID,
448 LINE_ID
449 FROM OE_ORDER_LINES_ALL
450 WHERE TOP_MODEL_LINE_ID = l_line_id
451 AND LINE_ID <> l_line_id
452 AND NVL(CANCELLED_FLAG,'N')<> 'Y'
453 ORDER BY LINE_ID;
454 -- added for bug 12758138
455 l_config_ato_line_id NUMBER := 0;
456 l_config_ato_line_id2 OE_ORDER_PUB.line_tbl_type ;
457 l_config_ato_count NUMBER := 0;
458
459 CURSOR CONFIGTBL IS
460 SELECT ORDERED_QUANTITY,
461 HEADER_ID,
462 LINE_ID
463 FROM OE_ORDER_LINES_ALL
464 WHERE ATO_LINE_ID = l_config_ato_line_id
465 AND ITEM_TYPE_CODE <> 'CONFIG'
466 AND NVL(CANCELLED_FLAG,'N')<> 'Y'
467 ORDER BY LINE_ID;
468 --end bug 12758138
469 --
470 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
471 --
472 BEGIN
473 IF l_debug_level > 0 THEN
474 oe_debug_pub.add('Entering splitting non-model configuration with following picture ',5);
475 END IF;
476 FOR I in 1..p_line_tbl.count LOOP
477 IF l_debug_level > 0 THEN
478 oe_debug_pub.add('line id : '||p_line_tbl(i).line_id , 1 ) ;
479 oe_debug_pub.add('ordered quantity : '||p_line_tbl(i).ordered_quantity , 1 ) ;
480 oe_debug_pub.add('item type code : '||p_line_tbl(i).item_type_code , 1 ) ;
481 oe_debug_pub.add('operation : '||p_line_tbl(i).operation , 1 ) ;
482 oe_debug_pub.add('shipped quantity : '||p_line_tbl(i).shipped_quantity , 1 ) ;
483 oe_debug_pub.add('model remnant flag : '||p_line_tbl(i).model_remnant_flag , 1 ) ;
484 oe_debug_pub.add('top model line id : '||p_line_tbl(i).top_model_line_id , 1 ) ;
485 oe_debug_pub.add('actual shipment on : '||p_line_tbl(i).actual_shipment_date,1);
486 END IF;
487 END LOOP;
488 x_line_tbl := p_line_tbl;
489 l_line_id := p_line_tbl(1).top_model_line_id;
490 oe_line_util.query_row(p_line_id => l_line_id,x_line_rec => l_line_rec);
491 l_parent_quantity := l_line_rec.ordered_quantity - p_line_tbl(1).ordered_quantity;
492 l_parent_line := l_line_rec;
493 -- Get Complete shipped Model if any
494 -- Form Complete Shipped Model if g_max_model > 0
495 IF l_debug_level > 0 THEN
496 oe_debug_pub.add('********* Completing shipped model *************** ',5);
497 END IF;
498 IF g_max_model > 0 THEN
499 l_parent_line := l_line_rec;
500 l_parent_line.ordered_quantity := g_max_model;
501 l_parent_line.shipped_quantity := g_max_model;
502 l_parent_line.operation := oe_globals.g_opr_update;
503 l_parent_line.split_action_code := 'SPLIT';
504 l_parent_line.split_by := 'SYSTEM';
505 --l_option_count := l_option_count + 1;
506 IF l_parent_line.line_set_id IS NULL THEN
507 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
508 END IF;
509 l_set_id := l_parent_line.line_set_id;
510 l_option_count := l_option_count + 1;
511 l_option_line_tbl(l_option_count) := l_parent_line;
512 FOR optionrec in optiontbl LOOP
513 oe_line_util.query_row(p_line_id => optionrec.line_id, x_line_rec => l_option_line);
514 l_model_ratio := l_option_line.ordered_quantity/l_line_rec.ordered_quantity;
515 option_updated := FALSE;
516 FOR I in 1..P_line_tbl.count LOOP
517 IF p_line_tbl(I).line_id = optionrec.line_id THEN
518 IF l_debug_level > 0 THEN
519 oe_debug_pub.add( X_LINE_TBL ( I ) .ORDERED_QUANTITY ) ;
520 END IF;
521 x_line_tbl(I).ordered_quantity := g_max_model * l_model_ratio;
522 x_line_tbl(I).operation := oe_globals.g_opr_update;
523 x_line_tbl(I).shipped_quantity := x_line_tbl(I).ordered_quantity;
524 option_updated := TRUE;
525 IF l_debug_level > 0 THEN
526 oe_debug_pub.add( 'ORD QTY' ||X_LINE_TBL ( I ) .ORDERED_QUANTITY ) ;
527 END IF;
528 EXIT;
529 END IF;
530 END LOOP;
531 IF not option_updated THEN
532 l_option_count := l_option_count + 1;
533 l_option_line_tbl(l_option_count) := l_option_line;
534 l_option_line_tbl(l_option_count).ordered_quantity := g_max_model * l_model_ratio;
535 /* Start Audit Trail */
536 l_option_line_tbl(l_option_count).change_reason := 'SYSTEM';
537 /* End Audit Trail */
538 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
539 l_option_line_tbl(l_option_count).split_action_code := 'SPLIT';
540 l_option_line_tbl(l_option_count).split_by := 'SYSTEM' ;
541 l_option_line_tbl(l_option_count).shipped_quantity := l_option_line_tbl(l_option_count).ordered_quantity;
542 END IF; -- Option not updated
543 option_updated := false;
544 END LOOP;
545 IF l_debug_level > 0 THEN
546 oe_debug_pub.add('After maximum shipped model',5 ) ;
547 END IF;
548 FOR I in 1..l_option_line_tbl.count LOOP
549 IF l_debug_level > 0 THEN
550 oe_debug_pub.add('Option table ordered Quantity : '|| l_option_line_tbl ( i ) .ordered_quantity , 1 ) ;
551 oe_debug_pub.add('Item Type Code : '|| l_option_line_tbl ( i ) .item_type_code , 1 ) ;
552 oe_debug_pub.add('Operation : '|| l_option_line_tbl ( i ) .operation , 1 ) ;
553 END IF;
554 END LOOP;
555 END IF; -- g_max_model
556 -- End Complete Shipped Model
557 -- Get Complete shippable Model if any
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.add('Completing unshipped model ',1);
560 END IF;
561 IF g_min_Model > 0 THEN
562 l_parent_line := l_line_rec;
563 l_parent_line.ordered_quantity := g_min_model;
564 l_parent_line.operation := oe_globals.g_opr_create;
565 l_parent_line.split_by := 'SYSTEM';
566 l_parent_line.split_from_line_id := l_parent_line.line_id;
567 IF l_debug_level > 0 THEN
568 oe_debug_pub.add('Line set id is : '||l_set_id,5) ;
569 END IF;
570 l_parent_line.line_id := fnd_api.g_miss_num;
571 l_parent_line.orig_sys_line_ref :=fnd_api.g_miss_char; -- Bug5207907
572 OE_Split_Util.Default_Attributes
573 (p_x_line_rec => l_parent_line,
574 p_old_line_rec => l_parent_line );
575 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
576 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
577 l_set_id := l_parent_line.line_set_id;
578 ELSIF l_set_id is not null then
579 l_parent_line.line_set_id := l_set_id;
580 END IF;
581 l_option_count := l_option_count + 1;
582 l_option_line_tbl(l_option_count) := l_parent_line;
583 FOR optionrec in optiontbl LOOP
584 oe_line_util.query_row(p_line_id => optionrec.line_id, x_line_rec => l_option_line);
585 l_model_ratio := l_option_line.ordered_quantity /l_line_rec.ordered_quantity;
586 l_option_count := l_option_count + 1;
587 l_option_line_tbl(l_option_count) := l_option_line;
588 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
589 l_option_line_tbl(l_option_count).split_by:='SYSTEM';
590 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
591 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
592 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
593 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
594 OE_Split_Util.Default_Attributes
595 ( p_x_line_rec => g_qry_out_rec
596 ,p_old_line_rec => l_option_line_tbl(l_option_count)
597 );
598 l_option_line_tbl(l_option_count) := g_qry_out_rec;
599 l_option_line_tbl(l_option_count).top_model_line_id := l_parent_line.line_id;
600 l_ato_line_id := l_option_line_tbl(l_option_count).ato_line_id; --bug 14198118
601 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
602 -- l_option_line_tbl(l_option_count).ato_line_id := l_parent_line.line_id; Bug14198118
603 /*bug 14198118 start*/
604
605 FOR I in 1..l_option_line_tbl.count LOOP
606 IF l_option_line_tbl(I).split_from_line_id = l_ato_line_id
607 AND l_option_line_tbl(I).top_model_line_id = l_parent_line.line_id THEN
608 oe_debug_pub.add('Setting ATO Line ID '||l_option_line_tbl(I).line_id);
609 l_option_line_tbl(l_option_count).ato_line_id := l_option_line_tbl(I).line_id;
610 END IF;
611 END LOOP;
612 /*bug 14198118 end*/
613 END IF;
614 l_option_line_tbl(l_option_count).ordered_quantity := g_min_model * l_model_ratio ;
615 END LOOP;
616 END IF;
617 IF l_debug_level > 0 THEN
618 oe_debug_pub.add('after get shippable model',5);
619 END IF;
620 FOR I in 1..l_option_line_tbl.count LOOP
621 IF l_debug_level > 0 THEN
622 oe_debug_pub.add('Option table ordered Quantity : '|| l_option_line_tbl ( i ) .ordered_quantity , 1 ) ;
623 oe_debug_pub.add('Item Type : '||l_option_line_tbl ( i ) .item_type_code , 1 ) ;
624 oe_debug_pub.add('Operation : '||l_option_line_tbl ( i ) .operation , 1 ) ;
625 oe_debug_pub.add('Line Set ID : '||l_option_line_tbl ( i ) .line_set_id , 1 ) ;
626 END IF;
627 END LOOP;
628 -- Get Remnant shipped
629
630 l_parent_line := l_line_rec;
631 IF l_debug_level > 0 THEN
632 oe_debug_pub.add('Process completely shipped remnant lines',1);
633 oe_debug_pub.add('Parent quantity 1 : ' || L_PARENT_LINE.ORDERED_QUANTITY ) ;
634 END IF;
635 l_parent_line.ordered_quantity := l_line_rec.ordered_quantity - g_max_model - g_min_model ;
636 IF l_debug_level > 0 THEN
637 oe_debug_pub.add('Parent quantity 2 : ' || L_PARENT_LINE.ORDERED_QUANTITY ) ;
638 END IF;
639 IF l_parent_line.ordered_quantity > 0 THEN
640 IF g_max_model = 0 THEN
641 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
642 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
643 l_set_id := l_parent_line.line_set_id;
644 ELSIF l_set_id is not null then
645 l_parent_line.line_set_id := l_set_id;
646 END IF;
647 l_rem_top_model_line_id := l_parent_line.line_id;
648 l_parent_line.operation := oe_globals.g_opr_update;
649 l_parent_line.split_action_code := 'SPLIT';
650 l_parent_line.split_by := 'SYSTEM';
651 ELSE -- g_max_model
652 l_parent_line.operation := oe_globals.g_opr_create;
653 l_parent_line.split_by := 'SYSTEM';
654 l_parent_line.split_from_line_id := l_parent_line.line_id;
655 l_parent_line.line_id := fnd_api.g_miss_num;
656 l_parent_line.config_header_id := NULL;
657 l_parent_line.config_rev_nbr := NULL;
658 l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
659 OE_Split_Util.Default_Attributes
660 (p_x_line_rec => l_parent_line
661 ,p_old_line_rec => l_parent_line
662 );
663 l_rem_top_model_line_id := l_parent_line.line_id;
664 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
665 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
666 l_set_id := l_parent_line.line_set_id;
667 ELSIF l_set_id is not null then
668 l_parent_line.line_set_id := l_set_id;
669 END IF;
670 l_parent_line.ordered_quantity :=l_line_rec.ordered_quantity - g_max_model- g_min_model ;
671 --l_parent_line.shipped_quantity := --l_parent_line.ordered_quantity;
672 END IF; -- Gmaxmodel
673 l_parent_line.model_remnant_flag := 'Y';
674 --IF l_parent_line.ordered_quantity > 0 THEN
675 l_option_count := l_option_count + 1;
676 l_option_line_tbl(l_option_count) := l_parent_line;
677 END IF;
678 FOR optionrec in optiontbl LOOP
679 oe_line_util.query_row(p_line_id => optionrec.line_id,x_line_rec => l_option_line);
680 l_model_ratio := l_option_line.ordered_quantity /l_line_rec.ordered_quantity;
681 FOR I in 1..P_line_tbl.count LOOP
682 l_actual_shipment_date := p_line_tbl(i).actual_shipment_date;
683 IF p_line_tbl(I).line_id = optionrec.line_id THEN
684 IF g_max_model = 0 THEN
685 IF x_line_tbl(I).ordered_quantity < 0 THEN
686 x_line_tbl(I).ordered_quantity := optionrec.ordered_quantity;
687 ELSE
688 x_line_tbl(I).ordered_quantity := p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
689 x_line_tbl(I).shipped_quantity := x_line_tbl(I).ordered_quantity;
690 END IF;
691 x_line_tbl(I).model_remnant_flag := 'Y';
692 ELSE -- G_Max_Model
693 l_option_line.ordered_quantity :=
694 p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
695 IF l_option_line.ordered_quantity > 0 THEN
696 l_option_count := l_option_count + 1;
697 l_option_line_tbl(l_option_count) := l_option_line;
698 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
699 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
700 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
701 l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
702 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
703 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
704 OE_Split_Util.Default_Attributes
705 ( p_x_line_rec => g_qry_out_rec
706 ,p_old_line_rec => l_option_line_tbl(l_option_count)
707 );
708 l_option_line_tbl(l_option_count) := g_qry_out_rec;
709 l_option_line_tbl(l_option_count).ordered_quantity:=p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
710 l_option_line_tbl(l_option_count).shipped_quantity :=
711 l_option_line_tbl(l_option_count).ordered_quantity;
712 l_option_line_tbl(l_option_count).actual_shipment_date := l_actual_shipment_date;
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add('Model Ratio : ' ||l_model_ratio,1);
715 oe_debug_pub.add('Index : ' ||I , 1 ) ;
716 oe_debug_pub.add('Ordered Quantity on remnant : '||x_line_tbl ( i ) .ordered_quantity , 1 ) ;
717 oe_debug_pub.add('actual shipment date : '||l_option_line_tbl(l_option_count).actual_shipment_date,5);
718 oe_debug_pub.add('shipped quantity on remnant : '||l_option_line_tbl( l_option_count ) .ordered_quantity,5);
719 END IF;
720 IF l_rem_top_model_line_id is not null then
721 l_option_line_tbl(l_option_count).top_model_line_id := l_rem_top_model_line_id;
722 ELSE
723 l_option_line_tbl(l_option_count).top_model_line_id := l_parent_line.line_id;
724 END IF;
725 l_option_line_tbl(l_option_count).line_set_id := l_set_id;
726 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
727 l_ato_line_id := l_option_line_tbl(l_option_count).ato_line_id; --bug 14198118
728
729 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
730 -- l_option_line_tbl(l_option_count).ato_line_id := l_parent_line.line_id; bug 14198118
731 /*bug 14198118 start*/
732
733 FOR I in 1..l_option_line_tbl.count LOOP
734 IF l_option_line_tbl(I).split_from_line_id = l_ato_line_id
735 AND l_option_line_tbl(I).top_model_line_id = l_option_line_tbl(l_option_count).top_model_line_id
736 AND l_option_line_tbl(I).model_remnant_flag = 'Y' THEN
737 oe_debug_pub.add('Setting ATO Line ID - '||l_option_line_tbl(I).line_id);
738 l_option_line_tbl(l_option_count).ato_line_id := l_option_line_tbl(I).line_id;
739 END IF;
740 END LOOP;
741 /*bug 14198118 end*/
742 END IF;
743 END IF; -- Ordere qty > 0
744 END IF; -- Gmaxmodel
745 -- added for bug 12758138
746 IF ( p_line_tbl(I).item_type_code = 'CONFIG'
747 AND ( p_line_tbl(I).ordered_quantity <> 0)
748 AND (p_line_tbl(I).ordered_quantity <> g_min_model * l_model_ratio)
749 AND (p_line_tbl(I).shipped_quantity <> g_max_model * l_model_ratio)
750 AND (g_min_model <> 0 OR g_max_model <> 0) --14840826
751 )THEN
752
753 l_config_ato_line_id:= p_line_tbl(I).ato_line_id;
754
755 FOR configrec in CONFIGTBL LOOP
756 oe_line_util.query_row(p_line_id => configrec.line_id,x_line_rec => l_option_line);
757 l_model_ratio := l_option_line.ordered_quantity /l_line_rec.ordered_quantity;
758
759 l_option_line.ordered_quantity :=
760 p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
761 IF l_option_line.ordered_quantity > 0 THEN
762 l_option_count := l_option_count + 1;
763 l_option_line_tbl(l_option_count) := l_option_line;
764 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
765 l_option_line_tbl(l_option_count).split_from_line_id := configrec.line_id;
766 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
767 l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
768 IF l_debug_level > 0 THEN
769 oe_debug_pub.add('adding for ato model in procedure Get_non_Model_Configuration',5) ;
770 END IF;
771 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5223102
772 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
773
774 OE_Split_Util.Default_Attributes
775 ( p_x_line_rec => g_qry_out_rec
776 ,p_old_line_rec => l_option_line_tbl(l_option_count)
777 );
778
779 l_option_line_tbl(l_option_count) := g_qry_out_rec;
780 l_option_line_tbl(l_option_count).ordered_quantity
781 :=p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
782 l_option_line_tbl(l_option_count).shipped_quantity :=
783 l_option_line_tbl(l_option_count).ordered_quantity;
784
785 IF l_debug_level > 0 THEN
786 oe_debug_pub.add(' Model Ratio : ' ||l_model_ratio,1);
787 oe_debug_pub.add(' Index : ' ||I , 1 ) ;
788 oe_debug_pub.add(' Ordered Quantity on remnant : '||x_line_tbl ( i ) .ordered_quantity , 1 ) ;
789 oe_debug_pub.add(' shipped quantity on remnant : '||l_option_line_tbl( l_option_count ) .ordered_quantity,5);
790 END IF;
791 IF l_rem_top_model_line_id is not null then
792 l_option_line_tbl(l_option_count).top_model_line_id := l_rem_top_model_line_id;
793 ELSE
794 l_option_line_tbl(l_option_count).top_model_line_id := l_parent_line.line_id;
795 END IF;
796 l_option_line_tbl(l_option_count).line_set_id := l_set_id;
797 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
798 l_ato_line_id := l_option_line_tbl(l_option_count).ato_line_id; --bug 14198118
799
800 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
801 FOR I in 1..l_option_line_tbl.count LOOP
802 IF l_option_line_tbl(I).split_from_line_id = l_ato_line_id
803 AND l_option_line_tbl(I).top_model_line_id = l_option_line_tbl(l_option_count).top_model_line_id
804 AND l_option_line_tbl(I).model_remnant_flag = 'Y' THEN
805 oe_debug_pub.add(' Setting ATO Line ID - '||l_option_line_tbl(I).line_id);
806 l_option_line_tbl(l_option_count).ato_line_id := l_option_line_tbl(I).line_id;
807 END IF;
808 END LOOP;
809 /*bug 14198118 end*/
810 END IF;
811 END IF; -- Ordere qty > 0
812 END LOOP;
813 l_config_ato_line_id := 0;
814 END IF;--is config line?
815 --end bug 12758138
816 EXIT;
817 END IF;
818 END LOOP;
819 END LOOP;
820 -- Get Remanant Unshipped
821 /*l_parent_line := l_line_rec;
822 l_parent_line.ordered_quantity :=
823 l_line_rec.ordered_quantity - p_line_tbl(1).shipped_quantity - g_min_model;
824 IF l_debug_level > 0 THEN
825 oe_debug_pub.add('Parent line ordered quantity ' || l_parent_line.ordered_quantity);
826 oe_debug_pub.add('This line ordered quantity ' || l_line_rec.ordered_quantity);
827 oe_debug_pub.add('Ordered quantity on the table ' || x_line_tbl(1).ordered_quantity);
828 END IF;
829 IF l_parent_line.ordered_quantity <> 0 THEN
830 l_parent_line.operation := oe_globals.g_opr_create;
831 l_parent_line.split_by := 'SYSTEM';
832 l_parent_line.split_from_line_id := l_parent_line.line_id;
833 l_parent_line.line_id := fnd_api.g_miss_num;
834 l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
835 OE_Split_Util.Default_Attributes
836 ( p_x_line_rec => l_parent_line
837 , p_old_line_rec => l_parent_line
838 );
839 l_parent_line.line_set_id := l_set_id;
840 l_rem_top_model_line_id := l_parent_line.line_id;
841 l_option_count := l_option_count + 1;
842 l_option_line_tbl(l_option_count) := l_parent_line;
843 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
844 END IF;*/
845 FOR optionrec in optiontbl LOOP
846 oe_line_util.query_row(p_line_id => optionrec.line_id, x_line_rec => l_option_line);
847 l_model_ratio := l_option_line.ordered_quantity / l_line_rec.ordered_quantity;
848 option_updated := FALSE;
849 l_insert_quantity := 0;
850 FOR I in 1..P_line_tbl.count LOOP
851 IF p_line_tbl(I).line_id = optionrec.line_id THEN
852 l_insert_quantity := p_line_tbl(I).shipped_quantity;
853 option_updated := true;
854 -- bug 12758138
855 IF ( p_line_tbl(I).item_type_code = 'CONFIG'
856 AND ( p_line_tbl(I).ordered_quantity <> 0)
857 AND (p_line_tbl(I).ordered_quantity <> g_min_model * l_model_ratio)
858 AND (p_line_tbl(I).shipped_quantity <> g_max_model * l_model_ratio)
859 AND (g_min_model <> 0 OR g_max_model <> 0) --14840826
860 )THEN
861 l_config_ato_line_id:=p_line_tbl(I).ato_line_id;
862 FOR configrec in CONFIGTBL LOOP
863 l_config_ato_count := l_config_ato_count+1;
864 l_config_ato_line_id2(l_config_ato_count).line_id := configrec.line_id;
865 l_config_ato_line_id2(l_config_ato_count).ordered_quantity
866 := configrec.ordered_quantity- g_min_model * l_model_ratio - p_line_tbl(I).shipped_quantity;
867 END LOOP;
868 END IF;
869 --end bug 12758138
870 EXIT;
871 END IF;
872 END LOOP;
873 IF l_insert_quantity = 0 AND g_max_model > 0 THEN
874 l_insert_quantity := g_max_model *l_model_ratio;
875 END IF;
876 l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
877 IF l_debug_level > 0 THEN
878 oe_debug_pub.add('Update Quantity : '||l_update_quantity,5) ;
879 oe_debug_pub.add('Option line Ordered Quantity : '||l_option_line.ordered_quantity,5);
880 oe_debug_pub.add('Ratio : ' || g_min_model * l_model_ratio,5);
881 oe_debug_pub.add('Insert Quantity : ' || l_insert_quantity,5);
882 END IF;
883 IF l_update_quantity > 0 THEN
884 l_option_count := l_option_count + 1;
885 IF NOT option_updated AND g_max_model = 0 THEN
886 l_option_line_tbl(l_option_count) := l_option_line;
887 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
888 l_option_line_tbl(l_option_count).split_action_code := 'SPLIT';
889 l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
890 l_option_line_tbl(l_option_count).ship_set_id := null;
891 l_option_line_tbl(l_option_count).arrival_Set_id := null;
892 ELSE
893 l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
894 l_option_line_tbl(l_option_count) := l_option_line;
895 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
896 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
897 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
898 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
899 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
900 OE_Split_Util.Default_Attributes
901 ( p_x_line_rec => g_qry_out_rec
902 ,p_old_line_rec => l_option_line_tbl(l_option_count)
903 );
904 l_option_line_tbl(l_option_count) := g_qry_out_rec;
905 IF l_rem_top_model_line_id IS NOT NULL THEN
906 l_option_line_tbl(l_option_count).top_model_line_id := l_rem_top_model_line_id;
907 ELSE
908 l_option_line_tbl(l_option_count).top_model_line_id := l_parent_line.line_id;
909 END IF;
910 /* Commenting for bug 4941632
911 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
912 IF l_rem_top_model_line_id is not null then
913 l_option_line_tbl(l_option_count).ato_line_id := l_rem_top_model_line_id;
914 ELSE
915 l_option_line_tbl(l_option_count).ato_line_id := l_parent_line.line_id;
916 END IF;
917 END IF; */
918 /*bug 14198118 start*/
919 l_ato_line_id := l_option_line_tbl(l_option_count).ato_line_id; --bug 14198118
920
921 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
922
923 FOR I in 1..l_option_line_tbl.count LOOP
924 IF l_option_line_tbl(I).split_from_line_id = l_ato_line_id
925 AND l_option_line_tbl(I).top_model_line_id = l_option_line_tbl(l_option_count).top_model_line_id THEN
926 oe_debug_pub.add('Setting ATO Line ID - '||l_option_line_tbl(I).line_id);
927 l_option_line_tbl(l_option_count).ato_line_id := l_option_line_tbl(I).line_id;
928 END IF;
929 END LOOP;
930 END IF;
931 /*bug 14198118 end*/
932 END IF; -- Option updated or g max model
933 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
934 l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
935 END IF; -- Update Quantity
936 option_updated := false;
937 END LOOP;
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('Out table count : ' || x_line_tbl.count,1) ;
940 END IF;
941 l_option_count := x_line_tbl.count + 1;
942 FOR I in 1..l_option_line_tbl.count LOOP
943 --bug 12758138
944 FOR j IN 1..l_config_ato_line_id2.Count LOOP
945 IF (l_config_ato_line_id2(j).line_id = l_option_line_tbl(I).split_from_line_id
946 AND l_option_line_tbl(I).model_remnant_flag ='Y'
947 AND l_option_line_tbl(I).shipped_quantity IS NULL )THEN
948 l_option_line_tbl(I).ordered_quantity := l_config_ato_line_id2(j).ordered_quantity;
949 END IF;
950 END LOOP;
951 --end bug 12758138
952 x_line_tbl(l_option_count) := l_option_line_tbl(I);
953 l_option_count := l_option_count + 1;
954 END LOOP;
955 IF l_debug_level > 0 THEN
956 oe_debug_pub.add('Final table picture : ',5);
957 END IF;
958 FOR I in 1..x_line_tbl.count LOOP
959 IF l_debug_level > 0 THEN
960 oe_debug_pub.add('line id : '||x_line_tbl(i).line_id , 1 ) ;
961 oe_debug_pub.add('ordered quantity : '||x_line_tbl(i).ordered_quantity , 1 ) ;
962 oe_debug_pub.add('item type code : '||x_line_tbl(i).item_type_code , 1 ) ;
963 oe_debug_pub.add('operation : '||x_line_tbl(i).operation , 1 ) ;
964 oe_debug_pub.add('line set id : '||x_line_tbl(i).line_set_id , 1 ) ;
965 oe_debug_pub.add('shipped quantity : '||x_line_tbl(i).shipped_quantity , 1 ) ;
966 oe_debug_pub.add('model remnant flag : '||x_line_tbl(i).model_remnant_flag , 1 ) ;
967 oe_debug_pub.add('split from line id : '||x_line_tbl(i).split_from_line_id , 1 ) ;
968 oe_debug_pub.add('top model line id : '||x_line_tbl(i).top_model_line_id , 1 ) ;
969 oe_debug_pub.add('actual shipment on : '||x_line_tbl(i).actual_shipment_date,1);
970 oe_debug_pub.add('ATO Line ID : '||x_line_tbl(i).ato_line_id);
971 END IF;
972 END LOOP;
973 IF l_debug_level > 0 THEN
974 OE_DEBUG_PUB.add('Leaving get_non_model_configuration()',5);
975 END IF;
976 EXCEPTION
977 WHEN OTHERS THEN
978 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
979 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,'Get_Non_Model_Configuration' );
980 END IF;
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END Get_Non_Model_Configuration;
983
984 Procedure Get_Model_Configuration(p_line_tbl IN OE_ORDER_PUB.line_tbl_type,
985 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.line_tbl_type) IS
986 l_line_id number;
987
988 l_line_tbl OE_ORDER_PUB.line_tbl_type := p_line_tbl;
989 l_reminant_tbl OE_ORDER_PUB.line_tbl_type;
990 l_shippable_tbl OE_ORDER_PUB.line_tbl_type;
991 l_shipped_tbl OE_ORDER_PUB.line_tbl_type;
992 l_temp_tbl OE_ORDER_PUB.line_tbl_type;
993 l_ratio number;
994 l_model_ratio number;
995 l_line_rec OE_ORDER_PUB.line_rec_type;
996 l_model_rec OE_ORDER_PUB.line_rec_type;
997 l_option_line OE_ORDER_PUB.line_rec_type;
998 l_parent_line OE_ORDER_PUB.line_rec_type;
999 l_option_line_tbl OE_ORDER_PUB.line_tbl_type;
1000 l_tbl_count number := 0;
1001 l_option_count number := 0;
1002 l_original_qty number := 0;
1003 l_min_model number := 0;
1004 l_temp_min_model number := 0;
1005 l_insert_quantity number := 0;
1006 l_update_quantity number := 0;
1007 option_updated BOOLEAN := FALSE;
1008 l_exist BOOLEAN := FALSE;
1009 l_parent_quantity number := 0;
1010 l_Set_id Number;
1011 l_option_not_updated boolean := TRUE;
1012 l_update_line_reqd boolean := TRUE;
1013 l_Rem_top_model_line_id number;
1014 l_Rem_shp_top_model_line_id number;
1015 l_top_model_line_id number;
1016
1017 CURSOR Optiontbl IS
1018 SELECT ORDERED_QUANTITY,
1019 HEADER_ID,
1020 LINE_ID
1021 FROM OE_ORDER_LINES_ALL
1022 WHERE TOP_MODEL_LINE_ID = l_line_id
1023 AND LINE_ID <> l_line_id
1024 AND NVL(CANCELLED_FLAG,'N') <> 'Y'
1025 ORDER BY LINE_ID;
1026 --
1027 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1028 --
1029 BEGIN
1030 x_line_tbl := p_line_tbl;
1031 l_line_id := p_line_tbl(1).line_id;
1032 oe_line_util.query_row(p_line_id => l_line_id, x_line_rec => l_line_rec);
1033 l_parent_quantity := l_line_rec.ordered_quantity - p_line_tbl(1).ordered_quantity;
1034 l_parent_line := l_line_rec;
1035 -- Get Complete shipped Model if any
1036 -- Form Complete Shipped Model if g_max_model > 0
1037 IF l_debug_level > 0 THEN
1038 oe_debug_pub.add( 'COMPLETE SHIPPED MODEL' ) ;
1039 END IF;
1040 IF g_max_model > 0 THEN
1041 l_parent_line := l_line_rec;
1042 l_parent_line.ordered_quantity := g_max_model;
1043 l_parent_line.shipped_quantity := g_max_model;
1044 l_parent_line.operation := oe_globals.g_opr_update;
1045 l_parent_line.split_action_code := 'SPLIT';
1046 l_parent_line.split_by := 'SYSTEM';
1047 --l_option_count := l_option_count + 1;
1048 IF l_parent_line.line_set_id IS NULL THEN
1049 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
1050 END IF;
1051 l_set_id := l_parent_line.line_set_id;
1052 x_line_tbl(1) := l_parent_line;
1053 --l_option_line_tbl(l_option_count) := l_parent_line;
1054 FOR optionrec IN optiontbl LOOP
1055 oe_line_util.query_row(p_line_id => optionrec.line_id,
1056 x_line_rec => l_option_line);
1057 l_model_ratio := l_option_line.ordered_quantity / l_line_rec.ordered_quantity;
1058 option_updated := FALSE;
1059 FOR I in 1..P_line_tbl.count LOOP
1060 IF p_line_tbl(I).line_id = optionrec.line_id THEN
1061 x_line_tbl(I).ordered_quantity := g_max_model * l_model_ratio;
1062 x_line_tbl(I).operation := OE_GLOBALS.g_opr_update;
1063 x_line_tbl(I).shipped_quantity := x_line_tbl(I).ordered_quantity;
1064 option_updated := TRUE;
1065 EXIT;
1066 END IF;
1067 END LOOP;
1068 IF NOT option_updated THEN
1069 l_option_count := l_option_count + 1;
1070 l_option_line_tbl(l_option_count) := l_option_line;
1071 l_option_line_tbl(l_option_count).ordered_quantity := g_max_model * l_model_ratio;
1072 /* Start Audit Trail */
1073 l_option_line_tbl(l_option_count).change_reason := 'SYSTEM';
1074 /* End Audit Trail */
1075 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
1076 l_option_line_tbl(l_option_count).split_action_code := 'SPLIT';
1077 l_option_line_tbl(l_option_count).split_by := 'SYSTEM' ;
1078 l_option_line_tbl(l_option_count).shipped_quantity :=l_option_line_tbl(l_option_count).ordered_quantity;
1079 END IF; -- Option not updated
1080 option_updated := false;
1081 END LOOP;
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add( 'AFTER MAXIMUM SHIPPED MODEL' ) ;
1084 END IF;
1085 FOR I in 1..l_option_line_tbl.count LOOP
1086 IF l_debug_level > 0 THEN
1087 oe_debug_pub.add( L_OPTION_LINE_TBL ( I ) .ORDERED_QUANTITY , 1 ) ;
1088 oe_debug_pub.add( L_OPTION_LINE_TBL ( I ) .ITEM_TYPE_CODE , 1 ) ;
1089 oe_debug_pub.add( L_OPTION_LINE_TBL ( I ) .OPERATION , 1 ) ;
1090 END IF;
1091 END LOOP;
1092 END IF; -- g_max_model
1093 -- End Complete Shipped Mo
1094 -- Get Complete shippable Model if any
1095 IF g_min_Model > 0 THEN
1096 l_parent_line := l_line_rec;
1097 l_parent_line.ordered_quantity := g_min_model;
1098 l_parent_line.operation := oe_globals.g_opr_create;
1099 l_parent_line.split_by := 'SYSTEM';
1100 l_parent_line.split_from_line_id := l_parent_line.line_id;
1101 IF l_debug_level > 0 THEN
1102 oe_debug_pub.add( 'LINE SET IS-'||L_SET_ID ) ;
1103 END IF;
1104 l_parent_line.line_id := fnd_api.g_miss_num;
1105 l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1106 OE_Split_Util.Default_Attributes
1107 (p_x_line_rec => l_parent_line
1108 ,p_old_line_rec => l_parent_line );
1109 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
1110 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
1111 l_set_id := l_parent_line.line_set_id;
1112 ELSIF l_set_id is not null then
1113 l_parent_line.line_set_id := l_set_id;
1114 END IF;
1115 l_option_count := l_option_count + 1;
1116 l_option_line_tbl(l_option_count) := l_parent_line;
1117 FOR optionrec in optiontbl LOOP
1118 oe_line_util.query_row(p_line_id => optionrec.line_id,
1119 x_line_rec => l_option_line);
1120 l_model_ratio :=l_option_line.ordered_quantity / l_line_rec.ordered_quantity;
1121 l_option_count := l_option_count + 1;
1122 l_option_line_tbl(l_option_count) := l_option_line;
1123 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1124 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1125 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1126 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1127 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1128 OE_Split_Util.Default_Attributes
1129 (p_x_line_rec => g_qry_out_rec
1130 ,p_old_line_rec => l_option_line_tbl(l_option_count)
1131 );
1132 l_option_line_tbl(l_option_count) := g_qry_out_rec;
1133 l_option_line_tbl(l_option_count).top_model_line_id := l_parent_line.line_id;
1134 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
1135 l_option_line_tbl(l_option_count).ato_line_id := l_parent_line.line_id;
1136 END IF;
1137 l_option_line_tbl(l_option_count).ordered_quantity := g_min_model * l_model_ratio ;
1138 END LOOP;
1139 END IF;
1140 IF l_debug_level > 0 THEN
1141 oe_debug_pub.add( 'AFTER GET SHIPPABLE MODEL' ) ;
1142 END IF;
1143 FOR I in 1..l_option_line_tbl.count LOOP
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add( 'ORDERED QUANTITY : '||L_OPTION_LINE_TBL ( I ) .ORDERED_QUANTITY , 1 ) ;
1146 oe_debug_pub.add( 'ITEM TYPE CODE : '||L_OPTION_LINE_TBL ( I ) .ITEM_TYPE_CODE , 1 ) ;
1147 oe_debug_pub.add( 'OPERATION : '||L_OPTION_LINE_TBL ( I ) .OPERATION , 1 ) ;
1148 oe_debug_pub.add( 'LINE SET ID : '||L_OPTION_LINE_TBL ( I ) .LINE_SET_ID , 1 ) ;
1149 END IF;
1150 END LOOP;
1151 -- Get Remanant shipped
1152 l_parent_line := l_line_rec;
1153 IF g_max_model = 0 THEN
1154 IF x_line_tbl(1).ordered_quantity < 0 THEN
1155 x_line_tbl(1).ordered_quantity := l_parent_line.ordered_quantity;
1156 ELSE
1157 x_line_tbl(1).ordered_quantity := p_line_tbl(1).shipped_quantity - g_max_model ;
1158 x_line_tbl(1).shipped_quantity := x_line_tbl(1).ordered_quantity;
1159 END IF;
1160 x_line_tbl(1).model_remnant_flag := 'Y';
1161 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
1162 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
1163 l_set_id := l_parent_line.line_set_id;
1164 ELSIF l_set_id is not null then
1165 x_line_tbl(1).line_set_id := l_set_id;
1166 END IF;
1167 l_rem_shp_top_model_line_id := x_line_tbl(1).line_id;
1168 ELSE -- Gmaxmodel
1169 IF l_debug_level > 0 THEN
1170 oe_debug_pub.add( 'PARENT ORDERED QUANTITY : ' || L_PARENT_LINE.ORDERED_QUANTITY ) ;
1171 END IF;
1172 l_parent_line.ordered_quantity := p_line_tbl(1).shipped_quantity - g_max_model ;
1173 IF l_debug_level > 0 THEN
1174 oe_debug_pub.add( 'PARENT ORDERED QUANTITY : ' || L_PARENT_LINE.ORDERED_QUANTITY ) ;
1175 END IF;
1176 IF l_parent_line.ordered_quantity > 0 THEN
1177 l_parent_line.operation := oe_globals.g_opr_create;
1178 l_parent_line.split_by := 'SYSTEM';
1179 l_parent_line.split_from_line_id := l_parent_line.line_id;
1180 l_parent_line.line_id := fnd_api.g_miss_num;
1181 l_parent_line.config_header_id := NULL;
1182 l_parent_line.config_rev_nbr := NULL;
1183 l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1184 OE_Split_Util.Default_Attributes
1185 ( p_x_line_rec => l_parent_line
1186 ,p_old_line_rec => l_parent_line );
1187 l_rem_shp_top_model_line_id := l_parent_line.line_id;
1188 IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
1189 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
1190 l_set_id := l_parent_line.line_set_id;
1191 ELSIF l_set_id is not null then
1192 l_parent_line.line_set_id := l_set_id;
1193 END IF;
1194 l_parent_line.ordered_quantity := p_line_tbl(1).shipped_quantity - g_max_model ;
1195 l_parent_line.shipped_quantity := l_parent_line.ordered_quantity;
1196 l_parent_line.model_remnant_flag := 'Y';
1197 --IF l_parent_line.ordered_quantity > 0 THEN
1198 l_option_count := l_option_count + 1;
1199 l_option_line_tbl(l_option_count) := l_parent_line;
1200 END IF;
1201 END IF; -- Gmaxmodel
1202 -- Remanant Unshipped Model
1203 l_parent_line := l_line_rec;
1204 l_parent_line.ordered_quantity := l_line_rec.ordered_quantity - p_line_tbl(1).shipped_quantity - g_min_model;
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add('Parent line unshipped qty 1 : ' || L_PARENT_LINE.ORDERED_QUANTITY ) ;
1207 oe_debug_pub.add('This line unshipped qty 2 : ' || L_LINE_REC.ORDERED_QUANTITY ) ;
1208 oe_debug_pub.add('unshipped qty 3 : ' || X_LINE_TBL ( 1 ) .ORDERED_QUANTITY ) ;
1209 END IF;
1210 IF l_parent_line.ordered_quantity > 0 THEN
1211 l_parent_line.operation := oe_globals.g_opr_create;
1212 l_parent_line.split_by := 'SYSTEM';
1213 l_parent_line.split_from_line_id := l_parent_line.line_id;
1214 l_parent_line.line_id := fnd_api.g_miss_num;
1215 OE_Split_Util.Default_Attributes
1216 ( p_x_line_rec => l_parent_line
1217 ,p_old_line_rec => l_parent_line
1218 );
1219 l_parent_line.line_set_id := l_set_id;
1220 l_rem_top_model_line_id := l_parent_line.line_id;
1221 l_option_count := l_option_count + 1;
1222 l_option_line_tbl(l_option_count) := l_parent_line;
1223 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
1224 END IF;
1225 IF l_rem_shp_top_model_line_id is not null THEN
1226 l_top_model_line_id := l_rem_shp_top_model_line_id ;
1227 ELSE
1228 l_top_model_line_id := l_rem_top_model_line_id ;
1229 END IF;
1230 -- Get remanat shipped options
1231 FOR optionrec in optiontbl LOOP
1232 oe_line_util.query_row(p_line_id => optionrec.line_id,
1233 x_line_rec => l_option_line);
1234 l_model_ratio := l_option_line.ordered_quantity / l_line_rec.ordered_quantity;
1235 FOR I in 1..P_line_tbl.count LOOP
1236 IF p_line_tbl(I).line_id = optionrec.line_id THEN
1237 IF g_max_model = 0 THEN
1238 IF x_line_tbl(I).ordered_quantity < 0 THEN
1239 x_line_tbl(I).ordered_quantity := optionrec.ordered_quantity;
1240 ELSE
1241 -- add IF for bug 4590044
1242 IF (p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio) >0 THEN
1243 x_line_tbl(I).ordered_quantity :=
1244 p_line_tbl(I).shipped_quantity - g_max_model * l_model_ratio;
1245 x_line_tbl(I).shipped_quantity := x_line_tbl(I).ordered_quantity;
1246 end if;
1247 END IF;
1248 x_line_tbl(I).top_model_line_id := l_top_model_line_id;
1249 x_line_tbl(I).model_remnant_flag := 'Y';
1250 ELSE -- G_Max_Model
1251 --bug 4590044 :create shipped remanant option line only when ordered_qty > 0
1252 IF ( p_line_tbl(I).shipped_quantity-g_max_model * l_model_ratio >0) THEN
1253 l_option_count := l_option_count + 1;
1254 l_option_line_tbl(l_option_count) := l_option_line;
1255 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1256 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1257 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1258 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1259 OE_Split_Util.Default_Attributes
1260 (p_x_line_rec => g_qry_out_rec
1261 , p_old_line_rec => l_option_line_tbl(l_option_count));
1262 l_option_line_tbl(l_option_count) := g_qry_out_rec;
1263 l_option_line_tbl(l_option_count).ordered_quantity :=
1264 p_line_tbl(I).shipped_quantity-g_max_model * l_model_ratio;
1265 l_option_line_tbl(l_option_count).shipped_quantity :=l_option_line_tbl(l_option_count).ordered_quantity;
1266 IF l_debug_level > 0 THEN
1267 oe_debug_pub.add( 'RATIO : ' ||L_MODEL_RATIO , 1 ) ;
1268 oe_debug_pub.add( 'LOOP COUNTER : ' ||I , 1 ) ;
1269 oe_debug_pub.add( 'ORDERED QUANTITY ON REMNANT : ' || X_LINE_TBL ( I ) .ORDERED_QUANTITY , 1 ) ;
1270 oe_debug_pub.add( 'ORDERED QUTY 2 : ' || L_OPTION_LINE_TBL ( L_OPTION_COUNT ) .ORDERED_QUANTITY ) ;
1271 END IF;
1272 IF l_top_model_line_id is not null then
1273 l_option_line_tbl(l_option_count).top_model_line_id :=l_top_model_line_id;
1274 END IF;
1275 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
1276 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
1277 l_option_line_tbl(l_option_count).ato_line_id := l_top_model_line_id;
1278 END IF;
1279 END IF; --4590044 end of Order Qut check if greater then Zero?
1280 END IF; -- Gmaxmodel
1281 EXIT;
1282 END IF;
1283 END LOOP;
1284 END LOOP; --Option rec loop.
1285 -- Get Remanant Unshipped
1286 IF l_rem_top_model_line_id is not null THEN
1287 l_top_model_line_id := l_rem_top_model_line_id ;
1288 ELSE
1289 l_top_model_line_id := l_rem_shp_top_model_line_id ;
1290 END IF;
1291 FOR optionrec in optiontbl LOOP
1292 oe_line_util.query_row(p_line_id => optionrec.line_id,
1293 x_line_rec => l_option_line);
1294 l_model_ratio := l_option_line.ordered_quantity / l_line_rec.ordered_quantity;
1295 option_updated := FALSE;
1296 l_insert_quantity := 0;
1297 FOR I in 1..P_line_tbl.count LOOP
1298 IF p_line_tbl(I).line_id = optionrec.line_id THEN
1299 l_insert_quantity := p_line_tbl(I).shipped_quantity;
1300 option_updated := true;
1301 EXIT;
1302 ELSE
1303 --Add this condition for the bug#4590097
1304 l_insert_quantity := (g_max_model*l_model_ratio);
1305 END IF;
1306 END LOOP;
1307 l_update_quantity := l_option_line.ordered_quantity - g_min_model * l_model_ratio - l_insert_quantity ;
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add( 'UPDATE QUANTITY : ' || L_UPDATE_QUANTITY ) ;
1310 oe_debug_pub.add( 'OPTION LINE ORDERED QUANTITY : ' || L_OPTION_LINE.ORDERED_QUANTITY ) ;
1311 oe_debug_pub.add( 'RATIO : ' || G_MIN_MODEL * L_MODEL_RATIO ) ;
1312 oe_debug_pub.add( 'INSERT QUANTITY : ' || L_INSERT_QUANTITY ) ;
1313 END IF;
1314 IF l_update_quantity > 0 THEN
1315 l_option_count := l_option_count + 1;
1316 IF NOT option_updated AND g_max_model = 0 THEN
1317 l_option_line_tbl(l_option_count) := l_option_line;
1318 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
1319 l_option_line_tbl(l_option_count).split_action_code := 'SPLIT';
1320 l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
1321 l_option_line_tbl(l_option_count).ship_set_id := null;
1322 l_option_line_tbl(l_option_count).arrival_Set_id := null;
1323 l_option_line_tbl(l_option_count).top_model_line_id := l_top_model_line_id;
1324 ELSE
1325 l_option_line_tbl(l_option_count) := l_option_line;
1326 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1327 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1328 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1329 l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1330 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1331 OE_Split_Util.Default_Attributes
1332 ( p_x_line_rec => g_qry_out_rec
1333 , p_old_line_rec => l_option_line_tbl(l_option_count)
1334 );
1335 l_option_line_tbl(l_option_count) := g_qry_out_rec;
1336 if l_top_model_line_id is not null then
1337 l_option_line_tbl(l_option_count).top_model_line_id := l_top_model_line_id;
1338 end if;
1339 IF l_option_line_tbl(l_option_count).ato_line_id IS NOT NULL THEN
1340 IF l_top_model_line_id IS NOT NULL THEN
1341 l_option_line_tbl(l_option_count).ato_line_id := l_top_model_line_id;
1342 END IF;
1343 END IF;
1344 END IF; -- Option updated or g max model
1345 l_option_line_tbl(l_option_count).model_remnant_flag := 'Y';
1346 l_option_line_tbl(l_option_count).ordered_quantity := l_update_quantity;
1347 END IF; -- Update Quantity
1348 option_updated := false;
1349 END LOOP;
1350 IF l_debug_level > 0 THEN
1351 oe_debug_pub.add( 'COUNT IN X_LINE_TBL' || X_LINE_TBL.COUNT , 1 ) ;
1352 END IF;
1353 l_option_count := x_line_tbl.count + 1;
1354 FOR I in 1..l_option_line_tbl.count LOOP
1355 x_line_tbl(l_option_count) := l_option_line_tbl(I);
1356 l_option_count := l_option_count + 1;
1357 END LOOP;
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'FINAL OUT TABLE' ) ;
1360 END IF;
1361 FOR I in 1..x_line_tbl.count LOOP
1362 IF l_debug_level > 0 THEN
1363 oe_debug_pub.add( 'LINE ID : '|| X_LINE_TBL ( I ) .LINE_ID ) ;
1364 oe_debug_pub.add( 'ORDERED QUANTITY : '||X_LINE_TBL ( I ) .ORDERED_QUANTITY ) ;
1365 oe_debug_pub.add( 'ITEM TYPE CODE : '||X_LINE_TBL ( I ) .ITEM_TYPE_CODE ) ;
1366 oe_debug_pub.add( 'OPERATION : ' ||X_LINE_TBL ( I ) .OPERATION ) ;
1367 oe_debug_pub.add( 'LINE SET ID : ' || X_LINE_TBL ( I ) .LINE_SET_ID ) ;
1368 oe_debug_pub.add( 'SHIPPED QUANTITY : '|| X_LINE_TBL ( I ) .SHIPPED_QUANTITY ) ;
1369 oe_debug_pub.add( 'REMNANT FLAG : '||X_LINE_TBL ( I ) .MODEL_REMNANT_FLAG ) ;
1370 oe_debug_pub.add( 'SPLIT FROM LINE ID : '||X_LINE_TBL ( I ) .SPLIT_FROM_LINE_ID ) ;
1371 oe_debug_pub.add( 'TOP MODEL LINE ID : '||X_LINE_TBL ( I ) .TOP_MODEL_LINE_ID ) ;
1372 END IF;
1373 END LOOP;
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1377 OE_MSG_PUB.Add_Exc_Msg
1378 ( G_PKG_NAME ,
1379 'Get_Model_Configuration'
1380 );
1381 END IF;
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 END Get_Model_Configuration;
1384
1385 Procedure Cascade_Proportional_Split(p_line_tbl IN OE_ORDER_PUB.Line_tbl_type,
1386 Parent_ordered_quantity NUMBER,
1387 p_Index NUMBER,
1388 x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_tbl_type,
1389 x_line_adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_adj_tbl_type,
1390 x_line_scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_scredit_tbl_type)
1391 IS
1392 l_option_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1393 l_line_out_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1394 l_line_id NUMBER;
1395 l_top_model_line_id NUMBER;
1396 l_set_id NUMBER;
1397 TYPE optrec_type is RECORD (
1398 Ordered_quantity NUMBER,
1399 header_id NUMBER,
1400 Line_id NUMBER);
1401 optionrec optrec_type;
1402 l_option_count NUMBER := 1;
1403 l_model_ratio NUMBER;
1404 l_option_line OE_ORDER_PUB.line_rec_type;
1405 l_line_adj_tbl OE_Order_Pub.Line_Adj_tbl_type;
1406 l_line_adj_temp_tbl OE_Order_Pub.Line_Adj_tbl_type;
1407 l_line_scredit_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1408 l_line_scredit_temp_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1409 l_adjustment_count NUMBER := 0;
1410 l_scredit_count NUMBER := 0;
1411 l_split_line_tbl oe_split_util.split_line_tbl;
1412 l_split_count NUMBER := 0;
1413 l_model_map_tbl oe_split_util.model_map_tbl;
1414 l_map_count NUMBER := 0;
1415 L_FOUND BOOLEAN; --8706868
1416 l_order_date_type_code VARCHAR2(20); --bug 13954474
1417 Type Optioncur IS REF CURSOR;
1418 Optrec Optioncur;
1419 --
1420 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1421 --
1422 BEGIN
1423 x_line_tbl := p_line_tbl;
1424 IF l_debug_level > 0 THEN
1425 oe_debug_pub.add( 'ENTER CASCADE SPLITS ' , 1 ) ;
1426 END IF;
1427 l_line_id := p_line_tbl(p_index).line_id;
1428 IF (p_line_tbl(p_index).line_id = p_line_tbl(p_index).ato_line_id AND p_line_tbl(p_index).item_type_code = 'CLASS') THEN
1429 OPEN optrec FOR
1430 SELECT ORDERED_QUANTITY,
1431 HEADER_ID,
1432 LINE_ID
1433 FROM OE_ORDER_LINES_ALL
1434 WHERE ATO_LINE_ID = l_line_id
1435 AND LINE_ID <> l_line_id
1436 AND NVL(CANCELLED_FLAG,'N')<>'Y'
1437 ORDER BY LINE_ID;
1438 ELSE
1439 OPEN Optrec FOR
1440 SELECT ORDERED_QUANTITY,
1441 HEADER_ID,
1442 LINE_ID
1443 FROM OE_ORDER_LINES_ALL
1444 WHERE TOP_MODEL_LINE_ID = l_line_id
1445 AND LINE_ID <> l_line_id
1446 AND NVL(CANCELLED_FLAG,'N') <> 'Y'
1447 ORDER BY LINE_ID;
1448 END IF;
1449 --FOR Optionrec IN Optiontbl
1450 LOOP
1451 FETCH Optrec INTO optionrec;
1452 EXIT WHEN optrec%NOTFOUND;
1453 l_model_ratio := optionrec.ordered_quantity/ parent_ordered_quantity;
1454 IF l_debug_level > 0 THEN
1455 oe_debug_pub.add( 'MODEL RATIO : ' || L_MODEL_RATIO , 1 ) ;
1456 END IF;
1457 IF l_debug_level > 0 THEN
1458 oe_debug_pub.add( 'ORDERED QUANTITY RATIO : ' || OPTIONREC.ORDERED_QUANTITY , 1 ) ;
1459 END IF;
1460 oe_line_util.query_row(p_line_id => optionrec.line_id, x_line_rec => l_option_line);
1461 --bug 13954474
1462 l_option_line.REQUEST_DATE := P_LINE_TBL ( P_INDEX ) .REQUEST_DATE;
1463 l_option_line.SHIP_FROM_ORG_ID := P_LINE_TBL ( P_INDEX ) .SHIP_FROM_ORG_ID;
1464 l_option_line.SHIP_TO_ORG_ID := P_LINE_TBL ( P_INDEX ) .SHIP_TO_ORG_ID;
1465 l_option_line.SPLIT_REQUEST_DATE := P_LINE_TBL ( P_INDEX ) .SPLIT_REQUEST_DATE;
1466 l_option_line.SPLIT_SHIP_FROM := P_LINE_TBL ( P_INDEX ) .SPLIT_SHIP_FROM;
1467 l_option_line.SPLIT_SHIP_TO := P_LINE_TBL ( P_INDEX ) .SPLIT_SHIP_TO;
1468 IF l_option_line.Schedule_ship_date IS NOT NULL THEN
1469 IF NVL(l_option_line.SPLIT_REQUEST_DATE,'N') ='Y' THEN -- 10278858
1470 IF l_option_line.schedule_ship_date IS NOT NULL
1471 --AND NOT OE_GLOBALS.EQUAL(p_x_line_rec.request_date
1472 -- ,l_line_rec.request_date)
1473 AND NVL(OE_SYS_PARAMETERS.value('RESCHEDULE_REQUEST_DATE_FLAG'),'Y') = 'Y' THEN -- 12833832
1474 l_order_date_type_code := NVL(oe_schedule_util.Get_Date_Type(l_option_line.header_id),'SHIP');
1475 IF l_order_date_type_code = 'SHIP' THEN
1476 -- Its a scheduled line. Reschedule with new date
1477 l_option_line.schedule_ship_date := l_option_line.request_date;
1478 ELSE
1479 l_option_line.schedule_arrival_date := l_option_line.request_date;
1480 END IF;
1481 END IF;
1482 END IF;
1483 END IF;
1484 --bug 13954474
1485 l_line_scredit_tbl.delete;
1486 oe_line_scredit_util.query_rows( p_line_id => optionrec.line_id, x_line_scredit_tbl => l_line_scredit_tbl);
1487 l_option_line_tbl(l_option_count) := l_option_line;
1488 l_option_line_tbl(l_option_count).line_id := optionrec.line_id;
1489 l_option_line_tbl(l_option_count).ordered_quantity := p_line_tbl(p_index).ordered_quantity * l_model_ratio;
1490 IF l_debug_level > 0 THEN
1491 oe_debug_pub.add( 'RATIO : ' || P_LINE_TBL ( P_INDEX ) .ORDERED_QUANTITY ) ;
1492 oe_debug_pub.add( 'REQUEST DATE : ' || P_LINE_TBL ( P_INDEX ) .REQUEST_DATE ) ;
1493 oe_debug_pub.add( 'WAREHOUSE ' || P_LINE_TBL ( P_INDEX ) .SHIP_FROM_ORG_ID) ;
1494 oe_debug_pub.add( 'SHIP TO ' || P_LINE_TBL ( P_INDEX ) .SHIP_TO_ORG_ID) ;
1495 --bug 13954474
1496 oe_debug_pub.add( 'SPLIT REQUEST DATE ' || P_LINE_TBL ( P_INDEX ) .SPLIT_REQUEST_DATE) ;
1497 oe_debug_pub.add( 'SPLIT SHIP FROM ' || P_LINE_TBL ( P_INDEX ) .SPLIT_SHIP_FROM) ;
1498 oe_debug_pub.add( 'SPLIT SHIP TO ' || P_LINE_TBL ( P_INDEX ) .SPLIT_SHIP_TO) ;
1499 --bug 13954474
1500 END IF;
1501 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_update;
1502 l_option_line_tbl(l_option_count).split_action_code := 'SPLIT';
1503 l_option_line_tbl(l_option_count).split_by := p_line_tbl(p_index).split_by;
1504
1505 /* Populate Line set id if set id is not already populated bug - 2103004 */
1506
1507 IF ( l_option_line_tbl(l_option_count).line_set_id IS NULL ) THEN
1508 IF l_debug_level > 0 THEN
1509 oe_debug_pub.add( 'CREATING LINE SETS : ' , 1 ) ;
1510 END IF;
1511 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_option_line_tbl(l_option_count));
1512 END IF;
1513 l_set_id := l_option_line_tbl(l_option_count).line_Set_id;
1514 l_split_count := l_split_count + 1;
1515 l_split_line_tbl(l_split_count).quantity := l_option_line_tbl(l_option_count).ordered_quantity;
1516 l_split_line_tbl(l_split_count).line_id := l_option_line_tbl(l_option_count).line_id;
1517 l_option_count := l_option_count + 1;
1518 FOR I IN 2 .. g_split_line_tbl.count LOOP
1519 l_option_line_tbl(l_option_count) := l_option_line;
1520 l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1521 l_option_line_tbl(l_option_count).ordered_quantity := g_split_line_tbl(I).quantity * l_model_ratio;
1522 IF l_debug_level > 0 THEN
1523 oe_debug_pub.add( 'RATIO 3 : ' || G_SPLIT_LINE_TBL ( I ) .QUANTITY , 1 ) ;
1524 END IF;
1525 l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1526 l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1527 l_option_line_tbl(l_option_count).split_by := p_line_tbl(p_index).split_by;
1528 g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1529 g_qry_out_rec.orig_sys_line_ref :=fnd_api.g_miss_char; --bug5207907
1530 OE_Split_Util.Default_Attributes ( p_x_line_rec => g_qry_out_rec
1531 ,p_old_line_rec => l_option_line_tbl(l_option_count));
1532 l_option_line_tbl(l_option_count) := g_qry_out_rec;
1533
1534 -- l_option_line_tbl(l_option_count).top_model_line_id := g_split_line_tbl(I).line_id; -- commeneted for bug11714193
1535 l_option_line_tbl(l_option_count).top_model_line_id := g_split_line_tbl(I).top_model_line_id; -- bug11714193
1536
1537 IF l_option_line_tbl(l_option_count).ato_line_id is not null AND
1538 l_option_line_tbl(l_option_count).ato_line_id <>
1539 l_option_line_tbl(l_option_count).line_id THEN
1540 l_option_line_tbl(l_option_count).ato_line_id := g_split_line_tbl(I).line_id;
1541 END IF;
1542 l_option_line_tbl(l_option_count).line_Set_id := l_set_id;
1543 l_option_line_tbl(l_option_count).config_header_id := NULL;
1544 l_option_line_tbl(l_option_count).config_rev_nbr := NULL;
1545
1546 -- WE are splitting a class here hence record ato line id and link to lineid
1547
1548 l_split_count := l_split_count + 1;
1549 l_split_line_tbl(l_split_count).quantity := l_option_line_tbl(l_option_count).ordered_quantity;
1550 l_split_line_tbl(l_split_count).line_id := l_option_line_tbl(l_option_count).line_id;
1551 l_split_line_tbl(l_split_count).split_from_line_id := l_option_line_tbl(l_option_count).split_from_line_id;
1552 -- Copy Credits for this option
1553 IF l_debug_level > 0 THEN
1554 oe_debug_pub.add( 'COPYING SALES CREDITS TO NEW OPTION ' , 1 ) ;
1555 END IF;
1556 FOR SCRD_REC IN 1..l_line_Scredit_tbl.count LOOP
1557 l_scredit_count := l_scredit_count + 1;
1558 l_line_scredit_temp_tbl(l_scredit_count) := l_line_scredit_tbl(SCRD_REC);
1559 l_line_scredit_temp_tbl(l_scredit_count).sales_credit_id := FND_API.G_MISS_NUM;
1560 l_line_scredit_temp_tbl(l_scredit_count).Operation := OE_GLOBALS.G_OPR_CREATE;
1561 l_line_scredit_temp_tbl(l_scredit_count).line_id := l_option_line_tbl(l_option_count).line_id;
1562 END LOOP;
1563 IF l_debug_level > 0 THEN
1564 oe_debug_pub.add( 'DONE COPYING SALES CREDITS TO NEW OPTION ' , 1 ) ;
1565 END IF;
1566 l_option_count := l_option_count + 1;
1567 END LOOP;
1568 Get_Service_Lines( p_line_tbl => x_line_tbl,
1569 x_line_tbl => l_line_out_tbl,
1570 g_split_line_tbl => l_split_line_tbl);
1571
1572 x_line_tbl := l_line_out_tbl; -- Swapping can be fixed, opportunity to optimize
1573 l_split_line_tbl.delete;
1574 l_split_count := 0;
1575 IF l_debug_level > 0 THEN
1576 oe_debug_pub.add( 'AFTER OPTION PROCESSING' , 1 ) ;
1577 END IF;
1578 END LOOP;
1579 CLOSE optrec;
1580 l_option_count := x_line_tbl.count + 1;
1581 FOR I in 1..l_option_line_tbl.count LOOP
1582 x_line_tbl(l_option_count) := l_option_line_tbl(I);
1583 l_option_count := l_option_count + 1;
1584 END lOOP;
1585
1586 -- 8706868 Deriving Link to Line_id for components with CREATE operation
1587 Begin
1588 FOR I in 1..x_line_tbl.count LOOP
1589 L_FOUND := FALSE;
1590 IF X_LINE_TBL(I).OPERATION = OE_GLOBALS.G_OPR_CREATE
1591 AND X_LINE_TBL(I).LINE_ID <> X_LINE_TBL(I).TOP_MODEL_LINE_ID
1592 AND X_LINE_TBL(I).TOP_MODEL_LINE_ID IS NOT NULL THEN
1593 FOR J in 1..x_line_tbl.count LOOP
1594 IF X_LINE_TBL(J).OPERATION = OE_GLOBALS.G_OPR_UPDATE THEN
1595 IF X_LINE_TBL(J).LINE_ID = X_LINE_TBL(I).SPLIT_FROM_LINE_ID THEN
1596 FOR K in 1..x_line_tbl.count LOOP
1597 IF X_LINE_TBL(J).LINK_TO_LINE_ID = X_LINE_TBL(K).SPLIT_FROM_LINE_ID AND
1598 X_LINE_TBL(I).TOP_MODEL_LINE_ID = X_LINE_TBL(K).TOP_MODEL_LINE_ID THEN
1599 X_LINE_TBL(I).LINK_TO_LINE_ID := X_LINE_TBL(K).LINE_ID;
1600 -- bug 16039593 begins
1601 IF ( X_LINE_TBL(I).item_type_code ='CONFIG' AND
1602 X_LINE_TBL(I).LINK_TO_LINE_ID <> X_LINE_TBL(I).ATO_LINE_ID) THEN
1603
1604 X_LINE_TBL(I).ATO_LINE_ID := X_LINE_TBL(I).LINK_TO_LINE_ID ;
1605
1606 IF l_debug_level > 0 THEN
1607 oe_debug_pub.add( 'X_LINE_TBL(i).LINE_ID '||X_LINE_TBL(i).LINE_ID , 1 ) ;
1608 oe_debug_pub.add( 'X_LINE_TBL(i).ATO_LINE_ID '||X_LINE_TBL(i).ATO_LINE_ID , 1 ) ;
1609 END IF ;
1610
1611 END IF ;
1612 --bug 16039593 ends
1613
1614 L_FOUND := TRUE;
1615 EXIT;
1616 END IF;
1617 END LOOP;
1618 END IF;
1619 END IF;
1620 IF L_FOUND THEN
1621 EXIT;
1622 END IF;
1623 END LOOP;
1624 END IF;
1625 END LOOP;
1626 End;
1627
1628 FOR I in 1..x_line_tbl.count LOOP
1629 IF l_debug_level > 0 THEN
1630 oe_debug_pub.add( 'QUANTITY : ' || X_LINE_TBL ( I ) .ORDERED_QUANTITY ) ;
1631 oe_debug_pub.add( 'OPERATION : ' || X_LINE_TBL ( I ) .OPERATION ) ;
1632 oe_debug_pub.add( 'ITEM : ' || X_LINE_TBL ( I ) .ITEM_TYPE_CODE ) ;
1633 oe_debug_pub.add( 'LINE : ' || X_LINE_TBL ( I ) .LINE_ID ) ;
1634 oe_debug_pub.add( 'LINK TO LINE ID : '|| X_LINE_TBL ( I ) .LINK_TO_LINE_ID );
1635 oe_debug_pub.add( 'SPLIT LINE : ' || X_LINE_TBL ( I ) .SPLIT_FROM_LINE_ID ) ;
1636 oe_debug_pub.add( 'LINE SET : ' || X_LINE_TBL ( I ) .LINE_SET_ID ) ;
1637 oe_debug_pub.add( X_LINE_TBL ( I ) .SPLIT_ACTION_CODE ) ;
1638 END IF;
1639 END LOOP;
1640
1641 -- Populate Credits into out table
1642 IF l_debug_level > 0 THEN
1643 oe_debug_pub.add( 'POPULATING SALES CREDIT OUT TABLE...' , 1 ) ;
1644 END IF;
1645 IF l_line_scredit_temp_tbl.count > 0 THEN
1646 l_scredit_count := x_line_scredit_tbl.count + 1;
1647 FOR I in 1..l_line_scredit_temp_tbl.count LOOP
1648 x_line_scredit_tbl(l_scredit_count) := l_line_scredit_temp_tbl(I);
1649 l_scredit_count := l_scredit_count + 1;
1650 END LOOP;
1651 END IF;
1652 IF l_debug_level > 0 THEN
1653 oe_debug_pub.add( 'EXIT CASCADE PROPORTIONAL SPLIT' , 1 ) ;
1654 END IF;
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 IF l_debug_level > 0 THEN
1658 oe_debug_pub.add( 'ERROR IN CASCADE PROPORTIONAL SPLIT ..'||SQLERRM , 1 ) ;
1659 END IF;
1660 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1661 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , 'Cascade_Proportional_Split');
1662 END IF;
1663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1664
1665 End Cascade_Proportional_Split;
1666
1667 Procedure Default_Attributes
1668 (p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
1669 ,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
1670 )
1671 IS
1672 l_line_rec OE_Order_PUB.Line_Rec_Type := p_x_line_rec;
1673 g_multiple_shipments VARCHAR2(3);
1674 l_code_level VARCHAR2(30);
1675 l_shipment_number NUMBER;
1676 l_order_date_type_code VARCHAR2(20); -- 8706868
1677 --
1678 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1679 --
1680 BEGIN
1681 fnd_profile.get('ONT_IMP_MULTIPLE_SHIPMENTS', g_multiple_shipments);
1682 g_multiple_shipments := nvl(g_multiple_shipments, 'NO');
1683 IF l_debug_level > 0 THEN
1684 oe_debug_pub.add( 'G_MULTIPLE_SHIPMENTS = '||G_MULTIPLE_SHIPMENTS , 5 ) ;
1685 END IF;
1686 l_code_level := OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL;
1687 IF l_debug_level > 0 THEN
1688 oe_debug_pub.add( 'L_CODE_LEVEL = '||L_CODE_LEVEL , 5 ) ;
1689 END IF;
1690 IF oe_split_util.g_sch_recursion = 'FALSE' THEN
1691 IF l_debug_level > 0 THEN
1692 oe_debug_pub.add( 'ENTER DEFAULT ATTRIBUTES FOR SPLIT' ) ;
1693 oe_debug_pub.add( 'LINEID:'||P_X_LINE_REC.LINE_ID ) ;
1694 oe_debug_pub.add( 'SPLITLINEID:'||P_X_LINE_REC.SPLIT_FROM_LINE_ID ) ;
1695 oe_debug_pub.add( 'REQUEST DATE FLAG:'||P_X_LINE_REC.SPLIT_REQUEST_DATE ) ;
1696 oe_debug_pub.add( 'SHIP FROM FLAG:'||P_X_LINE_REC.SPLIT_SHIP_FROM ) ;
1697 oe_debug_pub.add( 'SHIP TO FLAG:'||P_X_LINE_REC.SPLIT_SHIP_TO ) ;
1698 END IF;
1699 IF (p_x_line_rec.operation = oe_globals.g_opr_create and
1700 (p_x_line_rec.split_from_line_id IS NOT NULL AND
1701 p_x_line_rec.split_from_line_id <> FND_API.G_MISS_NUM) AND
1702 (p_x_line_rec.line_id IS NULL OR
1703 p_x_line_rec.line_id = FND_API.G_MISS_NUM)) THEN
1704 IF l_debug_level > 0 THEN
1705 oe_debug_pub.add( 'ENTER INTO DEFAULTING SPLITS' ) ;
1706 END IF;
1707 oe_line_util.query_row(p_line_id => p_x_line_rec.split_from_line_id,
1708 x_line_rec => l_line_rec);
1709 l_line_rec.line_id := OE_Default_Line.get_Line;
1710 IF (P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID IS NOT NULL
1711 AND P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID <> FND_API.G_MISS_NUM) THEN --9111247
1712
1713 L_LINE_REC.SERVICE_REFERENCE_LINE_ID:=P_X_LINE_REC.SERVICE_REFERENCE_LINE_ID ;
1714 END IF ;
1715 --online changes
1716 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110508' AND
1717 g_multiple_shipments = 'YES' THEN
1718 -- AND nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1719 -- Commented the above condition for bug 6712010
1720 l_shipment_number := get_shipment_number(l_line_rec);
1721 l_line_rec.orig_sys_shipment_ref := substr(l_line_rec.orig_sys_shipment_ref,0,instr(l_line_rec.orig_sys_shipment_ref,'.'))||l_shipment_number;
1722 ELSIF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110508' AND
1723 nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1724 -- l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1725
1726 --Changed for bug 4302022
1727 IF (p_x_line_rec.orig_sys_line_ref IS NULL OR
1728 p_x_line_rec.orig_sys_line_ref = FND_API.G_MISS_CHAR) THEN
1729
1730 l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1731 ELSE
1732 OE_DEBUG_PUB.add('Orig Sys Line Ref was populated',1);
1733 l_line_rec.orig_sys_line_ref := p_x_line_rec.orig_sys_line_ref;
1734 END IF;
1735
1736 ELSIF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110508' AND
1737 g_multiple_shipments = 'NO' AND
1738 nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1739 -- l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1740 --Changed for bug 4302022
1741 IF (p_x_line_rec.orig_sys_line_ref IS NULL OR
1742 p_x_line_rec.orig_sys_line_ref = FND_API.G_MISS_CHAR) THEN
1743
1744 l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1745 ELSE
1746 OE_DEBUG_PUB.add('Orig Sys Line Ref was populated',1);
1747 l_line_rec.orig_sys_line_ref := p_x_line_rec.orig_sys_line_ref;
1748 END IF;
1749
1750
1751 END IF;
1752 --l_line_rec.shipment_number := get_shipment_number(l_line_rec);
1753 IF l_debug_level > 0 THEN
1754 oe_debug_pub.add( 'SHIPMENT_NUMBER:'||L_LINE_REC.SHIPMENT_NUMBER ) ;
1755 END IF;
1756 l_line_rec.split_from_line_id := p_x_line_rec.split_from_line_id;
1757 l_line_rec.ordered_quantity := p_x_line_rec.ordered_quantity;
1758 l_line_rec.ordered_quantity2 := p_x_line_rec.ordered_quantity2; -- OPM B1661023 04/02/01
1759 l_line_rec.split_by := p_x_line_rec.split_by;
1760 -- 8706868
1761
1762 IF p_x_line_rec.request_date IS NOT NULL AND
1763 p_x_line_rec.request_date <> fnd_api.G_MISS_DATE AND
1764 NVL(P_X_LINE_REC.SPLIT_REQUEST_DATE,'N') ='Y' THEN -- 10278858
1765
1766 IF l_line_rec.schedule_ship_date IS NOT NULL
1767 /*bug 13954474
1768 AND NOT OE_GLOBALS.EQUAL(p_x_line_rec.request_date
1769 ,l_line_rec.request_date)
1770 bug 13954474*/
1771 AND NVL(OE_SYS_PARAMETERS.value('RESCHEDULE_REQUEST_DATE_FLAG'),'Y') = 'Y' THEN -- 12833832
1772 l_order_date_type_code := NVL(oe_schedule_util.Get_Date_Type(l_line_rec.header_id),'SHIP');
1773 IF l_order_date_type_code = 'SHIP' THEN
1774 -- Its a scheduled line. Reschedule with new date
1775 l_line_rec.schedule_ship_date := p_x_line_rec.request_date;
1776 ELSE
1777 l_line_rec.schedule_arrival_date := p_x_line_rec.request_date;
1778 END IF;
1779 END IF;
1780
1781 l_line_rec.request_date := p_x_line_rec.request_date;
1782 END IF;
1783 IF p_x_line_rec.ship_from_org_id IS NOT NULL AND
1784 p_x_line_rec.ship_from_org_id <> fnd_api.G_MISS_NUM AND
1785 NVL(P_X_LINE_REC.SPLIT_SHIP_FROM,'N') = 'Y' THEN --10278858
1786 l_line_rec.ship_from_org_id := p_x_line_rec.ship_from_org_id;
1787 END IF;
1788 IF p_x_line_rec.ship_to_org_id IS NOT NULL AND
1789 p_x_line_rec.ship_to_org_id <> fnd_api.G_MISS_NUM AND
1790 NVL(P_X_LINE_REC.SPLIT_SHIP_TO,'N') = 'Y' THEN --10278858
1791 l_line_rec.ship_to_org_id := p_x_line_rec.ship_to_org_id;
1792 END IF;
1793 -- 8706868
1794 l_line_rec.cancelled_quantity := 0; --13817331
1795 l_line_rec.cancelled_quantity2 := 0; -- 13817331 NULL; -- INVCONV
1796 l_line_rec.shipped_quantity := NULL;
1797 l_line_rec.shipping_quantity := NULL;
1798 l_line_rec.shipped_quantity2 := NULL; -- B1661023 OPM 04/02/01
1799 l_line_rec.shipping_quantity2 := NULL; -- B1661023 OPM 04/02/01
1800 l_line_rec.shipping_quantity_uom := NULL;
1801 l_line_rec.actual_shipment_date := NULL;
1802 l_line_rec.over_ship_reason_code := NULL;
1803 l_line_rec.over_ship_resolved_flag := NULL;
1804 -- l_line_rec.reserved_quantity := 0;
1805 l_line_rec.config_header_id := NULL;
1806 l_line_rec.config_rev_nbr := NULL;
1807 l_line_rec.ship_set_id := NULL;
1808 l_line_rec.arrival_set_id := NULL;
1809 -- fix for bug 2250017
1810 l_line_rec.fulfilled_quantity := NULL;
1811 l_line_rec.fulfilled_quantity2 := NULL; -- INVCONV
1812
1813 IF (l_line_rec.Item_Type_Code = 'MODEL' OR
1814 l_line_rec.Item_Type_Code = 'CLASS' OR
1815 l_line_rec.Item_Type_Code = 'KIT') THEN
1816 IF (l_line_rec.top_model_line_id =
1817 l_line_rec.split_from_line_id) THEN
1818 l_line_rec.top_model_line_id := l_line_rec.line_id;
1819 IF l_line_rec.ato_line_id is not null THEN
1820 l_line_rec.ato_line_id := l_line_rec.line_id;
1821 END IF;
1822 -- bug11714193
1823 ELSIF (l_line_rec.ato_line_id =l_line_rec.split_from_line_id) THEN
1824 l_line_rec.ato_line_id := l_line_rec.line_id;
1825 --end bug11714193
1826 END IF;
1827 END IF;
1828 -- Populating ato line id for a standard ato item
1829 IF (l_line_rec.Item_Type_Code = 'STANDARD' AND
1830 l_line_rec.ato_line_id IS NOT NULL) THEN
1831 l_line_rec.ato_line_id := l_line_rec.line_id;
1832 END IF;
1833 -- The px_line_rec is the image of original line. IF the parent is ato then
1834 -- child will be ato and ato line id will be populated. This condition is true
1835 -- for ato under PTO
1836
1837 IF (l_line_rec.Item_Type_Code IN ('OPTION' ,'INCLUDED')AND --9775352
1838 p_x_line_rec.split_from_line_id = l_line_rec.ato_line_id ) THEN
1839 l_line_rec.ato_line_id := l_line_rec.line_id;
1840 END IF;
1841
1842 -- This code is commented since the scheduling is moved out of splits
1843
1844 /* IF l_line_rec.schedule_status_code IS NOT NULL THEN
1845 l_line_rec.schedule_action_code := OE_ORDER_SCH_UTIL.OESCH_ACT_DEMAND;
1846 l_line_rec.schedule_status_code := NULL;
1847 END IF;*/
1848 oe_line_util.convert_miss_to_null(p_x_line_rec => l_line_rec);
1849 l_line_rec.operation := oe_globals.g_opr_create;
1850 p_x_line_rec := l_line_rec;
1851 ELSIF (l_line_rec.line_id IS NOT NULL AND
1852 l_line_rec.line_id <> FND_API.G_MISS_NUM AND
1853 l_line_rec.operation = oe_globals.g_opr_create) THEN
1854 IF l_debug_level > 0 THEN
1855 oe_debug_pub.add( 'LINE ID :'||P_X_LINE_REC.LINE_ID ) ;
1856 oe_debug_pub.add( 'SPLIT FROM LINE ID :'||P_X_LINE_REC.SPLIT_FROM_LINE_ID ) ;
1857 oe_debug_pub.add( 'TOP MODEL LINE ID : '||P_X_LINE_REC.TOP_MODEL_LINE_ID ) ;
1858 END IF;
1859 IF p_x_line_rec.top_model_line_id = p_x_line_rec.line_id THEN
1860 l_line_rec.top_model_line_id := NULL;
1861 END IF;
1862
1863 l_line_rec.shipment_number := get_shipment_number(l_line_rec);
1864 l_line_rec.top_model_line_id := p_x_line_rec.top_model_line_id;
1865 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110508' AND
1866 g_multiple_shipments = 'YES' AND
1867 nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1868 l_line_rec.orig_sys_shipment_ref := substr(p_x_line_rec.orig_sys_shipment_ref,0,instr(p_x_line_rec.orig_sys_shipment_ref,'.'))||l_line_rec.shipment_number;
1869 ELSIF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110508' AND
1870 nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1871 -- l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1872 --Changed for bug 4302022
1873 IF (p_x_line_rec.orig_sys_line_ref IS NULL OR
1874 p_x_line_rec.orig_sys_line_ref = FND_API.G_MISS_CHAR) THEN
1875
1876 l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1877 ELSE
1878 OE_DEBUG_PUB.add('Orig Sys Line Ref was populated',1);
1879 l_line_rec.orig_sys_line_ref := p_x_line_rec.orig_sys_line_ref;
1880 END IF;
1881 ELSIF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110508' AND
1882 g_multiple_shipments = 'NO' AND
1883 nvl(l_line_rec.source_document_type_id,0) <> 10 THEN --Bug 5435726
1884 -- l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1885 --Changed for bug 4302022
1886 IF (p_x_line_rec.orig_sys_line_ref IS NULL OR
1887 p_x_line_rec.orig_sys_line_ref = FND_API.G_MISS_CHAR) THEN
1888
1889 l_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||l_line_rec.line_id;
1890 ELSE
1891 OE_DEBUG_PUB.add('Orig Sys Line Ref was populated',1);
1892 l_line_rec.orig_sys_line_ref := p_x_line_rec.orig_sys_line_ref;
1893 END IF;
1894
1895 END IF;
1896 p_x_line_rec := l_line_rec;
1897 END IF;
1898 END IF;
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1902 OE_MSG_PUB.Add_Exc_Msg
1903 ( G_PKG_NAME ,
1904 'Split_line'
1905 );
1906 END IF;
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908 END Default_Attributes;
1909
1910 PROCEDURE Split_Line
1911 (p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
1912 ,p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
1913 ) IS
1914 --
1915 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1916 --
1917 BEGIN
1918 NULL;
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1922 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'Split_line');
1923 END IF;
1924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1925 END Split_Line;
1926
1927 Procedure Check_split_Course(p_x_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_tbl_type,
1928 p_x_line_adj_tbl IN OUT NOCOPY OE_Order_Pub.Line_Adj_tbl_type,
1929 p_x_line_scredit_tbl IN OUT NOCOPY OE_Order_Pub.Line_scredit_Tbl_type)
1930 IS
1931 l_parent_ordered_quantity NUMBER;
1932 l_child_quantity NUMBER := 0;
1933 l_line_adj_tbl OE_ORDER_PUB.Line_Adj_tbl_type;
1934 l_line_tbl OE_Order_Pub.Line_tbl_type;
1935 l_line_out_tbl OE_Order_Pub.Line_tbl_type;
1936 l_line_rec OE_Order_Pub.Line_rec_type;
1937 l_line_adj_temp_tbl OE_Order_Pub.Line_Adj_tbl_type;
1938 l_line_scredit_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1939 l_line_scredit_temp_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1940 l_adjustment_count NUMBER := 0;
1941 l_scredit_count NUMBER := 0;
1942 l_Split_line_Tbl OE_SPLIT_UTIL.Split_line_Tbl;
1943 l_split_count NUMBER := 0;
1944 l_miss_rec OE_Order_Pub.Line_rec_type := oe_order_pub.g_miss_line_rec;
1945 l_sch_tbl OE_Order_Pub.Line_tbl_type;
1946 l_sch_count NUMBER := 0;
1947 last_count NUMBER := 0;
1948 l_return_status VARCHAR2(30);
1949 lfirst PLS_INTEGER;
1950 l_order_date_type_code VARCHAR2(20); -- 8706868
1951 l_sales_order_id NUMBER; -- 8706868
1952 --
1953 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1954 --
1955 BEGIN
1956 IF l_debug_level > 0 THEN
1957 oe_debug_pub.add( 'ENTER CHECK SPLIT COURSE' ) ;
1958 END IF;
1959 l_line_tbl := p_x_line_tbl;
1960 IF oe_split_util.g_sch_recursion = 'FALSE' AND NOT g_non_prop_split THEN
1961 -- Loop Through Lines Table to find Split Action
1962 -- i := l_line_tbl.First;
1963 -- This Check is to make sure Split will not process any table
1964 -- that is not in sesequence. It will fail if there is a gap in the
1965 -- input table.
1966 IF l_line_tbl.count <> 0 THEN
1967 lfirst := l_line_tbl.First;
1968 IF lfirst <> 1 THEN
1969 GOTO END_1;
1970 END IF;
1971 END IF;
1972 FOR I IN 1..l_line_tbl.count LOOP
1973 --While i is not null Loop
1974
1975 IF l_debug_level > 0
1976 THEN
1977 oe_debug_pub.add('OUT side the LOOP ORDERED QUANTITY IS => '|| l_line_tbl(I).ordered_quantity
1978 || ' Count : '||I);
1979 oe_debug_pub.add('OUT side the LOOP ORD QTY IS :'|| l_line_tbl(I).ordered_quantity || ' Count : '
1980 ||I ||' Operation : '||l_line_tbl(I).operation);
1981 end if;
1982 IF l_line_tbl(I).split_action_code = 'SPLIT' AND
1983 l_line_tbl(I).operation = OE_GLOBALS.G_OPR_UPDATE
1984 THEN
1985 /* Added this condition to spuress the spliting of the line with zero quantiy.
1986 Bug: 3318920
1987 By: Srini
1988 */
1989 IF l_line_tbl(I).ordered_quantity = 0
1990 THEN
1991 IF l_debug_level > 0 THEN
1992 oe_debug_pub.add( 'ORDERED QUANTITY IS INVALID with UPDATE OPER => '|| l_line_tbl(I).ordered_quantity ) ;
1993 END IF;
1994 FND_MESSAGE.SET_NAME('ONT','OE_SPLIT_WITH_ZERO_QTY');
1995 FND_MESSAGE.SET_TOKEN('QUANTITY',L_line_tbl(I).ordered_quantity);
1996 OE_MSG_PUB.ADD;
1997 IF l_debug_level > 0 THEN
1998 oe_debug_pub.add( 'SPLIT- NOT ALLOWED TO UPDATE WITH QUANTITY ZERO' ) ;
1999 END IF;
2000 RAISE FND_API.G_EXC_ERROR;
2001 END IF;
2002 oe_line_util.query_row(p_line_id => l_line_tbl(I).line_id,
2003 x_line_rec => l_line_rec);
2004 IF l_debug_level > 0
2005 THEN
2006 oe_debug_pub.add( 'ITEM TYPE CODE : '|| L_LINE_REC.ITEM_TYPE_CODE ) ;
2007 END IF;
2008 IF nvl(L_line_rec.model_remnant_flag,'N') <> 'Y'
2009 THEN
2010 IF (L_line_rec.item_type_code <> 'STANDARD' AND
2011 L_line_rec.item_type_code <> 'MODEL' AND
2012 L_line_rec.Item_type_code <> 'KIT' AND
2013 NOT (nvl(L_line_rec.ato_line_id,-99) = l_line_rec.line_id AND L_line_rec.item_type_code = 'CLASS' ))
2014 THEN
2015 IF l_debug_level > 0 THEN
2016 oe_debug_pub.add( 'ITEM TYPE CODE INVALID => '|| L_LINE_REC.ITEM_TYPE_CODE ) ;
2017 END IF;
2018 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SPLIT_OPR');
2019 FND_MESSAGE.SET_TOKEN('ITEMTYPE',L_line_rec.item_type_code);
2020 OE_MSG_PUB.ADD;
2021 IF l_debug_level > 0 THEN
2022 oe_debug_pub.add( 'SPLIT- NOT ALLOWED THIS ITEMTYPE' ) ;
2023 END IF;
2024 RAISE FND_API.G_EXC_ERROR;
2025 END IF;
2026 END IF;
2027 oe_line_util.query_row(p_line_id => l_line_tbl(I).line_id,
2028 x_line_rec => l_line_rec);
2029 l_parent_ordered_quantity := l_line_rec.ordered_quantity;
2030 l_split_count := l_split_count + 1;
2031 g_split_line_tbl(l_split_count).line_id := l_line_tbl(I).line_id;
2032 g_split_line_tbl(l_split_count).quantity := l_line_tbl(I).ordered_quantity;
2033 IF ( l_line_rec.line_set_id IS NULL )
2034 THEN
2035 --l_line_tbl(I).line_set_id <> FND_API.G_MISS_NUM) THEN
2036 IF l_debug_level > 0 THEN
2037 oe_debug_pub.add( 'ENTER CREATE LINE SET:' ) ;
2038 END IF;
2039 Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_line_rec);
2040 l_line_tbl(I).line_set_id := l_line_rec.line_set_id;
2041 oe_lot_serial_util.Set_Line_Set_ID ( p_Line_ID => l_line_tbl(I).line_id
2042 , p_Line_Set_ID => l_line_tbl(I).line_set_id);
2043 ELSE
2044 l_line_tbl(I).line_set_id := l_line_rec.line_set_id;
2045 END IF;
2046 l_sch_count := l_sch_count + 1;
2047 l_sch_tbl(l_sch_count) := l_line_tbl(I);
2048 --8706868
2049 -- Unreserve the line if warehouse being chabged and reservation is there.
2050 IF NOT OE_GLOBALS.EQUAL(l_line_rec.ship_from_org_id, l_line_tbl(I).ship_from_org_id) AND
2051 l_line_rec.schedule_ship_date is NOT NULL AND
2052 NVL(l_line_tbl(I).SPLIT_SHIP_FROM,'N') ='Y' THEN -- 10338643
2053 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id(l_line_rec.HEADER_ID);
2054 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
2055 ,p_line_id => l_line_rec.line_id
2056 ,p_org_id => l_line_rec.ship_from_org_id
2057 ,p_order_quantity_uom => l_line_rec.order_quantity_uom
2058 ,p_inventory_item_id => l_line_rec.inventory_item_id --bug 16434168/16480604
2059 ,x_reserved_quantity => l_line_rec.reserved_quantity
2060 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
2061 );
2062 IF l_line_rec.reserved_quantity is not null THEN
2063 OE_SCHEDULE_UTIL.Unreserve_Line
2064 (p_line_rec => l_line_rec,
2065 p_quantity_to_unreserve => l_line_rec.reserved_quantity,
2066 p_quantity2_to_unreserve => l_line_rec.reserved_quantity2 , -- INVCONV
2067 x_return_status => l_return_status);
2068 oe_schedule_util.oe_split_rsv_tbl(MOD(l_line_rec.line_id,G_BINARY_LIMIT)).line_id :=l_line_rec.line_id;
2069 END IF;
2070
2071 END IF;
2072 IF l_line_rec.schedule_ship_date is NOT NULL AND
2073 NOT OE_GLOBALS.EQUAL(l_line_rec.request_date, l_line_tbl(I).request_date) AND
2074 NVL(l_line_tbl(I).SPLIT_REQUEST_DATE,'N') ='Y' AND -- 10338643
2075 NVL(OE_SYS_PARAMETERS.value('RESCHEDULE_REQUEST_DATE_FLAG'),'Y') = 'Y' THEN -- 12833832
2076 l_order_date_type_code := NVL(oe_schedule_util.Get_Date_Type(l_line_rec.header_id),'SHIP');
2077 IF l_order_date_type_code = 'SHIP' THEN
2078 l_sch_tbl(l_sch_count).schedule_ship_date := l_line_tbl(I).request_date;
2079 ELSE
2080 l_sch_tbl(l_sch_count).schedule_arrival_date := l_line_tbl(I).request_date;
2081 END IF;
2082 END IF;
2083 -- 8706868
2084 l_child_quantity := l_line_tbl(I).ordered_quantity;
2085 IF l_debug_level > 0 THEN
2086 oe_debug_pub.add( 'PARENT QUANTITY:'|| L_PARENT_ORDERED_QUANTITY ) ;
2087 oe_debug_pub.add( 'CHILD_QUANTITY:'|| L_CHILD_QUANTITY ) ;
2088 END IF;
2089 l_line_scredit_tbl.delete;
2090 oe_line_scredit_util.query_rows( p_line_id => l_line_tbl(I).line_id,
2091 x_line_scredit_tbl => l_line_scredit_tbl);
2092 --j := l_line_tbl.First;
2093 --While j is not null Loop
2094 FOR J IN 1..l_Line_Tbl.Count LOOP
2095 IF l_debug_level > 0 THEN
2096 oe_debug_pub.add('CREATE ORDERED QUANTITY IS INVALID => '|| l_line_tbl(J).ordered_quantity);
2097 oe_debug_pub.add('OUT side the LOOP ORD QTY IS :'|| l_line_tbl(J).ordered_quantity || ' Count : '
2098 ||J ||' Operation : '||l_line_tbl(J).operation);
2099 END if;
2100 IF (l_Line_Tbl(J).split_from_line_id = l_line_tbl(I).Line_Id AND
2101 l_line_tbl(J).operation = OE_GLOBALS.G_OPR_CREATE )
2102 THEN
2103 /* Added this condition to spuress the spliting of the line with zero quantiy.
2104 Bug: 3318920
2105 By: Srini
2106 */
2107 IF l_line_tbl(J).ordered_quantity = 0
2108 THEN
2109 IF l_debug_level > 0 THEN
2110 oe_debug_pub.add('ORDERED QUANTITY IS INVALID with CREATE OPER=> '
2111 ||l_line_tbl(J).ordered_quantity ) ;
2112 END IF;
2113 FND_MESSAGE.SET_NAME('ONT','OE_SPLIT_WITH_ZERO_QTY');
2114 FND_MESSAGE.SET_TOKEN('QUANTITY',L_line_tbl(J).ordered_quantity);
2115 OE_MSG_PUB.ADD;
2116 IF l_debug_level > 0 THEN
2117 oe_debug_pub.add( 'SPLIT- NOT ALLOWED TO CREATE WITH QUANTITY ZERO' ) ;
2118 END IF;
2119 RAISE FND_API.G_EXC_ERROR;
2120 END IF;
2121 g_qry_out_rec := l_line_tbl(J);
2122 OE_Split_Util.Default_Attributes (p_x_line_rec => g_qry_out_rec
2123 ,p_old_line_rec => l_line_tbl(J));
2124
2125 l_line_tbl(J) := g_qry_out_rec;
2126 l_split_count := l_split_count + 1;
2127 g_split_line_tbl(l_split_count).split_from_line_id := l_line_tbl(J).split_from_line_id;
2128 g_split_line_tbl(l_split_count).quantity := l_line_tbl(J).ordered_quantity;
2129 g_split_line_tbl(l_split_count).line_id := l_line_tbl(J).line_id;
2130 g_split_line_tbl(l_split_count).top_model_line_id := l_line_tbl(J).top_model_line_id;-- bug11714193
2131 l_line_tbl(J).line_set_id := l_line_tbl(I).line_set_id;
2132 l_sch_count := l_sch_count + 1;
2133 l_sch_tbl(l_sch_count) := l_line_tbl(J);
2134 l_child_quantity := l_child_quantity + l_line_tbl(J).ordered_quantity;
2135 IF l_debug_level > 0 THEN
2136 oe_debug_pub.add( 'CHILD_QUANTITY2:'|| L_CHILD_QUANTITY ) ;
2137 END IF;
2138 -- Copy Sales Credits for the new line
2139 FOR SCRD_REC IN 1..L_line_Scredit_tbl.count LOOP
2140 l_scredit_count := l_scredit_count + 1;
2141 l_line_scredit_temp_tbl(l_scredit_count):= L_line_scredit_tbl(SCRD_REC);
2142 l_line_scredit_temp_tbl(l_scredit_count).sales_credit_id:= FND_API.G_MISS_NUM;
2143 l_line_scredit_temp_tbl(l_scredit_count).Operation := OE_GLOBALS.G_OPR_CREATE;
2144 l_line_scredit_temp_tbl(l_scredit_count).line_id := l_line_tbl(J).line_id;
2145 END LOOP;
2146 END IF; -- IF split from line id and operation create
2147 --j:= l_line_tbl.next(j);
2148 END LOOP; -- Loop for Insert on line table
2149 -- Check If quantities sum up to total ordered quantity
2150 IF l_debug_level > 0 THEN
2151 oe_debug_pub.add( 'PARENT QUANTITY3:'|| L_PARENT_ORDERED_QUANTITY ) ;
2152 oe_debug_pub.add( 'CHILD_QUANTITY3:'|| L_CHILD_QUANTITY ) ;
2153 END IF;
2154 IF l_parent_ordered_quantity <> l_child_quantity THEN
2155 IF l_debug_level > 0 THEN
2156 oe_debug_pub.add( 'PARENT QUANTITY3:'|| L_PARENT_ORDERED_QUANTITY ) ;
2157 oe_debug_pub.add( 'CHILD_QUANTITY3:'|| L_CHILD_QUANTITY ) ;
2158 END IF;
2159 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_SPLIT_QTY');
2160 OE_MSG_PUB.ADD;
2161 IF l_debug_level > 0 THEN
2162 oe_debug_pub.add( 'RAJ:SPLIT-QUNATITES NOT EQUAL' ) ;
2163 END IF;
2164 RAISE FND_API.G_EXC_ERROR;
2165 END IF;
2166 l_child_quantity := 0;
2167 -- Call service API to get service Lines
2168 Get_Service_Lines(p_line_tbl => l_line_tbl,
2169 x_line_tbl => l_line_out_tbl,
2170 g_split_line_tbl => g_split_line_tbl);
2171
2172 l_line_tbl := l_line_out_tbl; -- Swapping can be fixed. Opportunity to optimize
2173
2174 -- Call Models API to get all Option/Class lines
2175 Last_count := l_line_tbl.count;
2176 IF ((l_line_rec.item_type_code = 'MODEL') OR
2177 (l_line_rec.item_type_code = 'KIT') OR
2178 (l_line_rec.item_type_code = 'CLASS' AND l_line_rec.ato_line_id = l_line_rec.line_id))
2179 THEN
2180 IF l_debug_level > 0 THEN
2181 oe_debug_pub.add( 'INTO MODEL AND CLASS IF' ) ;
2182 oe_debug_pub.add( 'REMNANT FLAG : '||L_LINE_TBL ( I ) .MODEL_REMNANT_FLAG ) ;
2183 END IF;
2184 IF (nvl(l_line_rec.model_remnant_flag,'N') <> 'Y') OR
2185 (l_line_rec.item_type_code = 'CLASS' AND l_line_rec.ato_line_id = l_line_rec.line_id)
2186 THEN
2187 IF l_debug_level > 0 THEN
2188 oe_debug_pub.add( 'BEFORE CALLING CASCADE PROP' ) ;
2189 END IF;
2190 Last_count := l_line_tbl.count+1;
2191 Cascade_Proportional_split(p_line_tbl => l_line_tbl,
2192 parent_ordered_quantity => l_parent_ordered_quantity,
2193 p_index => I,
2194 x_line_tbl => l_line_out_tbl,
2195 x_line_adj_tbl =>p_x_line_adj_tbl,
2196 x_line_scredit_tbl => p_x_line_scredit_tbl);
2197 l_line_tbl := l_line_out_tbl; -- Swapping can be fixed, opportunity to optimize
2198
2199 IF Last_count <> l_line_tbl.count
2200 THEN
2201 FOR M IN Last_count+1 .. l_line_tbl.count LOOP
2202 IF (l_line_tbl(M).item_type_code = 'OPTION' OR
2203 l_line_tbl(M).item_type_code = 'CLASS')
2204 THEN
2205 l_sch_count := l_sch_count + 1;
2206 l_sch_tbl(l_sch_count) := l_line_tbl(M);
2207 END IF;
2208 END LOOP;
2209 END IF;
2210 END IF;
2211 END IF;
2212 -- Call scheduling
2213 -- This call to scheduling is commented to fix splitting of reservations
2214 -- issue. The call is moved to post line loop and is in control of scheduling
2215 OE_SPLIT_UTIL.G_SPLIT_ACTION := TRUE;
2216 /* OE_ORDER_SCH_UTIL.Split_Scheduling(p_line_tbl => l_sch_tbl,
2217 x_line_tbl => l_sch_tbl,
2218 x_return_status => l_return_status);
2219 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2220 oe_debug_pub.add('Into split scheduling unexpected failure');
2221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2222 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2223 oe_debug_pub.add('Into split scheduling expected failure');
2224 RAISE FND_API.G_EXC_ERROR;
2225 END IF; */
2226 -- Delete split line tbl for this line
2227 g_split_line_tbl.delete;
2228 l_split_count := 0;
2229 END IF; -- Operation Update
2230 --i:= l_line_tbl.next(i);
2231 END LOOP ; -- Parent Loop
2232 l_scredit_count := 0;
2233 -- Populate and Sales Credits
2234 IF l_line_scredit_temp_tbl.count > 0 THEN
2235 l_scredit_count := p_x_line_scredit_tbl.count + 1;
2236 FOR I in 1..l_line_scredit_temp_tbl.count LOOP
2237 p_x_line_scredit_tbl(l_scredit_count) := l_line_scredit_temp_tbl(I);
2238 l_scredit_count := l_scredit_count + 1;
2239 END LOOP;
2240 END IF;
2241 NULL;
2242 FOR G IN 1..l_sch_tbl.count LOOP
2243 FOR H in 1..l_line_tbl.count LOOP
2244 IF l_line_tbl(H).line_id = l_sch_tbl(G).line_id THEN
2245 l_line_tbl(H) := l_sch_tbl(G);
2246 EXIT;
2247 END IF;
2248 END LOOP;
2249 END LOOP;
2250 p_X_line_Tbl := l_line_tbl;
2251 -- Update Drop Ship Sources
2252 --Update_Drop_Ship_Source(p_line_tbl => X_Line_Tbl);
2253 IF l_debug_level > 0 THEN
2254 oe_debug_pub.add( 'SPLIT TABLE PICTURE IS - ' ) ;
2255 END IF;
2256 --i:= l_line_tbl.First;
2257 FOR I in 1..l_line_tbl.count LOOP
2258 --While i is not null loop
2259 IF l_debug_level > 0 THEN
2260 oe_debug_pub.add( L_LINE_TBL ( I ) .ORDERED_QUANTITY , 5 ) ;
2261 oe_debug_pub.add( L_LINE_TBL ( I ) .OPERATION , 5 ) ;
2262 oe_debug_pub.add( L_LINE_TBL ( I ) .ITEM_TYPE_CODE , 5 ) ;
2263 oe_debug_pub.add( L_LINE_TBL ( I ) .LINE_ID , 5 ) ;
2264 oe_debug_pub.add( L_LINE_TBL ( I ) .LINE_SET_ID , 5 ) ;
2265 oe_debug_pub.add( L_LINE_TBL ( I ) .SPLIT_ACTION_CODE , 5 ) ;
2266 oe_debug_pub.add( L_LINE_TBL ( I ) .REQUEST_DATE , 5 ) ;
2267 oe_debug_pub.add( L_LINE_TBL ( I ) .SHIP_FROM_ORG_ID , 5 ) ;
2268 oe_debug_pub.add( 'Link To Line id '||L_LINE_TBL ( I ) .LINK_TO_LINE_ID , 5) ;
2269 END IF;
2270 --i:= l_line_tbl.next(i);
2271 END LOOP;
2272 IF l_debug_level > 0 THEN
2273 oe_debug_pub.add( 'EXIT CHECK SPLIT COURSE' ) ;
2274 END IF;
2275 END IF;
2276 <<END_1>>
2277 NULL;
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2281 THEN
2282 OE_MSG_PUB.Add_Exc_Msg
2283 ( G_PKG_NAME ,
2284 'Check_Split_Course'
2285 );
2286 END IF;
2287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2288 END Check_Split_Course;
2289
2290 Procedure Cascade_non_proportional_Split(p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
2291 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
2292 l_line_id NUMBER;
2293 l_control_rec OE_GLOBALS.Control_Rec_Type;
2294 l_api_name CONSTANT VARCHAR2(30) := 'Cascade Non Proportonal splits';
2295 l_return_status VARCHAR2(30);
2296 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
2297 l_old_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2298 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2299 l_Line_Scredit_temp_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2300 l_line_tbl OE_ORDER_PUB.line_tbl_type := p_x_line_tbl;
2301 l_line_out_tbl OE_ORDER_PUB.line_tbl_type;
2302 l_model_ratio NUMBER;
2303 l_line_rec OE_ORDER_PUB.line_rec_type;
2304 l_option_line OE_ORDER_PUB.line_rec_type;
2305 l_tbl_count NUMBER := 0;
2306 l_min_model NUMBER := 0;
2307 l_max_ship_model NUMBER := 0;
2308 l_temp_min_model NUMBER := 0;
2309 l_temp_max_model NUMBER := 0;
2310 x_msg_count NUMBER;
2311 x_msg_data VARCHAR2(2000);
2312 l_model_flag VARCHAR2(1) := 'Y';
2313 l_complete_shipment VARCHAR2(1) ;
2314 l_scredit_count NUMBER := 0;
2315
2316 --
2317 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2318 --
2319 BEGIN
2320
2321 x_return_status := FND_API.G_RET_STS_SUCCESS;
2322 IF l_debug_level > 0 THEN
2323 OE_DEBUG_PUB.add('Entering Cascade_Non_Proportional_split() ',1);
2324 OE_DEBUG_PUB.add('Table count : '||p_x_line_tbl.count,5) ;
2325 END IF;
2326
2327 FOR I in 1 .. l_line_tbl.count LOOP
2328 IF l_line_tbl(I).ordered_quantity < 0 THEN
2329 g_over_shipment := true;
2330 END IF;
2331 IF l_debug_level > 0 THEN
2332 oe_debug_pub.add('Line id : '||l_line_tbl(i).line_id,5) ;
2333 oe_debug_pub.add('Operation : ' ||l_line_tbl(i).operation,5) ;
2334 oe_debug_pub.add('Ordered quantity : '||l_line_tbl(i).ordered_quantity,5) ;
2335 oe_debug_pub.add('Item type code : ' ||l_line_tbl(i).item_type_code,5) ;
2336 oe_debug_pub.add('Shipped quantity : '||l_line_tbl(i).shipped_quantity,5) ;
2337 END IF;
2338 END LOOP;
2339
2340 oe_line_util.query_row(p_line_id => l_line_tbl(1).line_id, x_line_rec => l_line_rec);
2341
2342 IF (l_line_rec.item_type_code = 'MODEL' OR
2343 l_line_rec.item_type_code = 'KIT') THEN
2344 IF l_line_rec.shippable_flag = 'Y' THEN
2345 l_tbl_count := 1 ;
2346 ELSE
2347 l_tbl_count := 2;
2348 END IF;
2349 ELSE
2350 oe_line_util.query_row(p_line_id => l_line_tbl(1).top_model_line_id, x_line_rec => l_line_rec);
2351 l_model_flag := 'N';
2352 l_tbl_count := 1;
2353 END IF;
2354
2355 IF NOT g_over_shipment THEN
2356
2357 IF l_debug_level > 0 THEN
2358 oe_debug_pub.add('Before calling check complete shipment',5) ;
2359 END IF;
2360 l_complete_shipment := Check_Complete_shipment( p_line_tbl => l_line_tbl, p_line_id => l_line_rec.line_id );
2361 IF (l_model_flag = 'N' AND nvl(l_line_rec.shippable_flag,'N') = 'Y' ) THEN
2362 l_max_ship_model := 0;
2363 END IF;
2364
2365 --Begin Added for the bug #3474977
2366 if l_model_flag = 'N' AND l_line_rec.shippable_flag = 'Y' THEN
2367 l_complete_shipment := 'N';
2368 end if;
2369 --End of the bug fix 3474977
2370 FOR I in l_tbl_count .. L_Line_tbl.count LOOP
2371 oe_line_util.query_row(p_line_id => l_line_tbl(I).line_id, x_line_rec => l_option_line);
2372 l_model_ratio := l_option_line.ordered_quantity/l_line_rec.ordered_quantity;
2373 l_temp_min_model := Floor(l_line_tbl(I).ordered_quantity/l_model_ratio);
2374 IF l_complete_shipment = 'Y' THEN
2375 l_temp_max_model := Floor(l_line_tbl(I).shipped_quantity/l_model_ratio);
2376 IF I=1 THEN
2377 l_max_ship_model := l_temp_max_model;
2378 ELSE
2379 IF l_temp_max_model < l_max_ship_model THEN
2380 l_max_ship_model := l_temp_max_model;
2381 END IF;
2382 END IF;
2383 END IF;
2384 IF I = 1 THEN
2385 l_min_model := l_temp_min_model;
2386 ELSE
2387 IF l_temp_min_model < l_min_model THEN
2388 l_min_model := l_temp_min_model;
2389 END IF;
2390 END IF;
2391 END LOOP;
2392
2393 END IF;
2394
2395 IF g_over_shipment THEN
2396 IF l_debug_level > 0 THEN
2397 oe_debug_pub.add('Over shipment true' , 1 ) ;
2398 END IF;
2399 l_min_model := 0;
2400 l_max_ship_model := 0;
2401 END IF;
2402 g_min_model := l_min_model;
2403 g_max_model := l_max_ship_model;
2404 IF l_debug_level > 0 THEN
2405 oe_debug_pub.add('Min model : '||l_min_model,5 ) ;
2406 oe_debug_pub.add('Max shipped model : '||l_max_ship_model,5) ;
2407 END IF;
2408 IF g_min_model = 0 THEN
2409 g_remnant_only := TRUE;
2410 END IF;
2411 IF l_model_flag = 'Y' THEN
2412 Get_Model_Configuration(p_line_tbl => l_line_tbl, x_line_tbl => l_line_out_tbl);
2413 l_line_tbl := l_line_out_tbl;
2414 IF l_debug_level > 0 THEN
2415 oe_debug_pub.add('Minimum model : '||l_min_model,5);
2416 oe_debug_pub.add('Minimum model : '||g_min_model,5);
2417 END IF;
2418 ELSE
2419 Get_non_Model_Configuration(p_line_tbl => l_line_tbl, x_line_tbl => l_line_out_tbl);
2420 l_line_tbl := l_line_out_tbl;
2421 IF l_debug_level > 0 THEN
2422 oe_debug_pub.add('Minimum model : '|| l_min_model,5);
2423 oe_debug_pub.add('Minimum model : '|| g_min_model,5);
2424 END IF;
2425 END IF;
2426 g_remnant_only := FALSE;
2427 -- Call Scheduling
2428 -- This call to scheduling is commented to fix splitting of reservations
2429 -- issue. The call is moved to post line loop and is in control of scheduling
2430 IF l_debug_level > 0 THEN
2431 oe_debug_pub.add('Scheduling call has been disabled',5);
2432 END IF;
2433 /*OE_ORDER_SCH_UTIL.Split_Scheduling(p_line_tbl => l_line_tbl,
2434 x_line_tbl => l_line_tbl,
2435 x_return_status => l_return_status);*/
2436 FOR I in 1 .. l_line_tbl.count LOOP
2437 IF l_debug_level > 0 THEN
2438 oe_debug_pub.add('Line ID : '||l_line_tbl(I).line_id,5);
2439 oe_debug_pub.add('Operation : '||l_line_tbl(I).operation,5) ;
2440 oe_debug_pub.add('Ordered Quantity : '||l_line_tbl(I).ordered_quantity,5) ;
2441 oe_debug_pub.add('Item Type Code : '||l_line_tbl(I).item_type_code,5) ;
2442 oe_debug_pub.add('Remnant Flag : '||nvl(l_line_tbl(I).model_remnant_flag,'N'),5) ;
2443 END IF;
2444 IF l_line_tbl(I).model_remnant_flag = 'Y' THEN
2445 l_line_tbl(I).split_by := 'SYSTEM';
2446 l_line_tbl(I).ship_set_id := NULL;
2447 l_line_tbl(I).arrival_set_id := NULL;
2448 END IF;
2449 END LOOP;
2450 -- Get Sales Credits
2451 FOR I in 1 .. l_line_tbl.count LOOP
2452 IF l_line_tbl(I).operation = OE_GLOBALS.G_OPR_CREATE THEN
2453 -- 13913678
2454 l_line_scredit_temp_tbl.delete;
2455 oe_line_scredit_util.query_rows( p_line_id => l_line_tbl(I).split_from_line_id,
2456 x_line_scredit_tbl => l_line_scredit_temp_tbl);
2457 -- 13913678
2458 -- l_scredit_count := l_line_scredit_tbl.count + 1;
2459 FOR Scr IN 1 .. l_line_scredit_temp_tbl.count LOOP
2460 --13913678
2461 l_scredit_count := l_scredit_count+1;
2462 l_line_scredit_tbl(l_scredit_count) := l_line_scredit_temp_tbl(Scr);
2463 l_line_scredit_tbl(l_scredit_count).operation := OE_GLOBALS.G_OPR_CREATE;
2464 l_line_scredit_tbl(l_scredit_count).line_id := l_line_tbl(I).line_id;
2465 l_line_scredit_tbl(l_scredit_count).sales_credit_id := fnd_api.g_miss_num;
2466 END LOOP;
2467 END IF;
2468 END LOOP;
2469 -- get Service Lines
2470 Get_nonprop_Service_Lines(p_line_tbl => l_line_tbl, x_line_tbl => l_line_out_tbl);
2471
2472 l_line_tbl := l_line_out_tbl; -- Swaping, opportunity for future optimization.
2473
2474 /* Populate line set id for all the option split lines from its parent bug 2103004 */
2475 Create_Line_Set_For_Options(p_x_line_tbl => l_line_tbl);
2476 p_x_line_tbl := l_line_tbl;
2477 g_non_prop_split := TRUE;
2478 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'N' ;
2479 l_control_rec.process := FALSE;
2480 l_control_rec.controlled_operation := TRUE;
2481 l_control_rec.check_security := FALSE;
2482 l_control_rec.change_attributes := TRUE;
2483 l_control_rec.default_attributes := TRUE;
2484 OE_SPLIT_UTIL.G_SPLIT_ACTION := TRUE;
2485 IF l_debug_level > 0 THEN
2486 oe_debug_pub.add('Before calling process order in splits',1) ;
2487 END IF;
2488 oe_order_pvt.Lines
2489 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
2490 , p_control_rec => l_control_rec
2491 , p_x_line_tbl => p_x_line_tbl
2492 , p_x_old_line_tbl => l_old_line_tbl
2493 , x_return_status => l_return_status
2494 );
2495 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2496 IF l_debug_level > 0 THEN
2497 oe_debug_pub.add('Split Line: Process order returns unexpected error : '||sqlerrm,1) ;
2498 END IF;
2499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2500 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2501 IF l_debug_level > 0 THEN
2502 oe_debug_pub.add('Split Line: Process order returns execution error : '||sqlerrm,1) ;
2503 END IF;
2504 RAISE FND_API.G_EXC_ERROR;
2505 END IF;
2506
2507 oe_order_pvt.Line_Scredits
2508 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
2509 , p_control_rec => l_control_rec
2510 , p_x_line_Scredit_tbl => l_Line_Scredit_tbl
2511 , p_x_old_line_Scredit_tbl => l_old_line_Scredit_tbl
2512 , x_return_status => l_return_status
2513 );
2514 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2516 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2517 RAISE FND_API.G_EXC_ERROR;
2518 END IF;
2519 OE_Order_PVT.Process_Requests_And_Notify
2520 ( p_process_requests => TRUE
2521 , p_notify => FALSE --lchen
2522 , x_return_status => l_return_status
2523 , p_line_tbl => l_line_tbl
2524 , p_old_line_tbl => l_old_line_tbl
2525 , p_line_scredit_tbl => l_Line_Scredit_tbl
2526 , p_old_line_scredit_tbl => l_old_line_Scredit_tbl
2527 );
2528 g_non_prop_split := FALSE;
2529 OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'Y' ;
2530 OE_SPLIT_UTIL.G_SPLIT_ACTION := FALSE;
2531 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2533 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2534 RAISE FND_API.G_EXC_ERROR;
2535 END IF;
2536 EXCEPTION
2537 WHEN OTHERS THEN
2538 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2539 THEN
2540 OE_MSG_PUB.Add_Exc_Msg
2541 ( G_PKG_NAME ,
2542 'Cascade_Non_Proportional_Split'
2543 );
2544 END IF;
2545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2546 END Cascade_Non_Proportional_Split;
2547
2548 PROCEDURE Record_line_History
2549 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
2550 )IS
2551 l_return_status varchar2(30);
2552 --
2553 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2554 --
2555 BEGIN
2556 OE_CHG_ORDER_PVT.RecordLineHist
2557 ( p_line_id => p_line_rec.line_id
2558 ,p_hist_type_code => 'SPLIT'
2559 ,p_reason_code => p_line_rec.change_reason
2560 ,p_comments => p_line_rec.change_comments
2561 ,x_return_status => l_return_status);
2562
2563 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2565 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2566 RAISE FND_API.G_EXC_ERROR;
2567 END IF;
2568 EXCEPTION
2569 WHEN OTHERS THEN
2570 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2571 THEN
2572 OE_MSG_PUB.Add_Exc_Msg
2573 ( G_PKG_NAME ,
2574 'Record_Line_History'
2575 );
2576 END IF;
2577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2578 End Record_line_History;
2579
2580
2581 Procedure Add_To_Fulfillment_Set(p_line_rec IN oe_order_pub.line_rec_type) IS
2582 -- 4925992
2583 l_top_model_line_id NUMBER;
2584 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2585
2586 Cursor C1 is SELECT SET_ID
2587 FROM OE_LINE_SETS
2588 WHERE LINE_ID = p_line_rec.split_from_line_id;
2589 BEGIN
2590 IF p_line_rec.split_from_line_id IS NOT NULL THEN
2591 FOR C1rec IN C1 LOOP
2592 -- 4925992
2593 IF p_line_rec.line_id = p_line_rec.top_model_line_id
2594 AND p_line_rec.operation <> 'CREATE'
2595 THEN
2596 l_top_model_line_id := p_line_rec.line_id;
2597 ELSE
2598 l_top_model_line_id := NULL;
2599 END IF;
2600 oe_set_util.Create_Fulfillment_Set(p_line_id => p_line_rec.line_id,
2601 p_top_model_line_id => l_top_model_line_id,
2602 p_set_id => c1rec.set_id);
2603 END LOOP;
2604 END IF;
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2608 OE_MSG_PUB.Add_Exc_Msg
2609 (G_PKG_NAME,'Add to Fulfillment Set');
2610 END IF;
2611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2612 End Add_To_Fulfillment_Set;
2613
2614 /* Defer Split ER Changes Start */
2615 PROCEDURE Defer_Split
2616 ( Errbuf OUT NOCOPY VARCHAR2
2617 , retcode OUT NOCOPY VARCHAR2
2618 , P_line_id IN VARCHAR DEFAULT NULL
2619 )
2620 IS
2621
2622 -- Cursor Decleration
2623
2624 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
2625 l_x_line_tbl OE_Order_PUB.Line_Tbl_Type;
2626 l_x_header_rec OE_Order_PUB.Header_Rec_Type;
2627 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2628 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2629 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2630 l_x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2631 l_x_Action_Request_tbl OE_Order_PUB.Request_Tbl_Type;
2632 l_x_Lot_Serial_Tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2633 l_x_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
2634 l_x_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
2635 l_x_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
2636 l_x_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
2637 l_x_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
2638 l_x_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
2639 l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2640 l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2641 l_control_rec OE_GLOBALS.Control_Rec_Type;
2642 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2643 l_line_conc_rec OE_Split_Util.Split_Line_Rec_Type;
2644 l_line_conc_tbl OE_Split_Util.Split_Line_Tbl_Type;
2645 l_rec_count number;
2646 l_return_status VARCHAR2(1);
2647 l_msg_data VARCHAR2(2000);
2648 l_msg_count NUMBER;
2649 l_msg_total NUMBER;
2650 l_count NUMBER := 0;
2651
2652 l_ord_qty_sum NUMBER := 0;
2653 l_ord_qty2_sum NUMBER := 0;
2654 l_line_id NUMBER;
2655 l_orig_ord_qty NUMBER := 0;
2656 l_orig_ord_qty2 NUMBER := 0;
2657 l_process_add_attributes BOOLEAN :=FALSE;
2658 l_init_line_id NUMBER;
2659 l_org_id Number;
2660 --10278858
2661 l_org_request_date DATE;
2662 l_org_ship_from_org_id NUMBER;
2663 l_org_ship_to_org_id NUMBER;
2664 l_ref_split_ship_from VARCHAR2(10);--16525601
2665
2666
2667 CURSOR c_split_details IS
2668 SELECT * FROM oe_line_split_details
2669 WHERE line_id = l_init_line_id
2670 AND request_id = FND_GLOBAL.CONC_REQUEST_ID
2671 FOR UPDATE NOWAIT;
2672
2673
2674 BEGIN
2675 oe_debug_pub.add('Entering Procedure OE_Split_Util.Defer_Split', 1);
2676 l_init_line_id :=to_number(p_line_id);
2677
2678 select org_id
2679 into l_org_id
2680 from oe_order_lines_all
2681 where line_id=l_init_line_id;
2682
2683 MO_GLOBAL.set_policy_context('S',l_org_id);
2684 OE_GLOBALS.Set_Context();
2685
2686
2687 Retcode := 0;
2688 Errbuf := NULL;
2689 OE_MSG_PUB.Initialize;
2690 l_msg_total := 0;
2691
2692 BEGIN
2693 OPEN c_split_details;
2694 FETCH c_split_details BULK COLLECT
2695 INTO l_line_conc_tbl;
2696 CLOSE c_split_details;
2697 EXCEPTION
2698 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2699 oe_debug_pub.add('record_lock exception in Oe_Split_Util.Defer_Split',1);
2700 Retcode := 2;
2701 errbuf := sqlerrm;
2702 raise;
2703 END;
2704
2705 select count(*) into l_count
2706 from oe_line_split_details where line_id = l_init_line_id
2707 AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
2708
2709 IF l_count = 0 THEN
2710 Fnd_Message.Set_Name('ONT','OE_CANCEL_SPLIT_SUCCESS');
2711 Fnd_Message.Set_Token('REQUEST_ID',FND_GLOBAL.CONC_REQUEST_ID);
2712 OE_Msg_Pub.Add;
2713 FND_FILE.put_line(FND_FILE.output,OE_MSG_PUB.Get(OE_MSG_PUB.G_LAST,FND_API.G_FALSE));
2714 RETURN;
2715 END IF;
2716
2717
2718 IF l_count > 1 THEN
2719
2720 SELECT sum(ORDERED_QUANTITY), sum(ORDERED_QUANTITY2)
2721 INTO l_ord_qty_sum, l_ord_qty2_sum
2722 FROM oe_line_split_details
2723 WHERE line_id = l_init_line_id
2724 AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
2725
2726 SELECT ordered_quantity, ordered_quantity2
2727 INTO l_orig_ord_qty, l_orig_ord_qty2
2728 FROM oe_order_lines_all
2729 WHERE open_flag = 'Y'
2730 AND line_id = l_init_line_id;
2731
2732 IF l_orig_ord_qty > 0 AND l_ord_qty_sum <> l_orig_ord_qty THEN
2733 FND_MESSAGE.SET_NAME('ONT','OE_UI_SPLIT_UNEQUAL_QTY');
2734 OE_MSG_PUB.ADD;
2735 oe_debug_pub.add('Total shipment quantity must equal original quantity',1);
2736 RAISE FND_API.G_EXC_ERROR;
2737 ELSIF l_orig_ord_qty2 > 0 AND (l_ord_qty2_sum <> l_orig_ord_qty2) THEN
2738 FND_MESSAGE.SET_NAME('ONT','OE_UI_SPLIT_UNEQUAL_QTY');
2739 OE_MSG_PUB.ADD;
2740 oe_debug_pub.add('Total shipment quantity must equal original quantity',1);
2741 RAISE FND_API.G_EXC_ERROR;
2742 END IF;
2743
2744 /* OPEN c_split_details;
2745 FETCH c_split_details BULK COLLECT --moved this statement to the top.
2746 INTO l_line_conc_tbl LIMIT 100;
2747 CLOSE c_split_details;*/
2748
2749 IF l_line_conc_tbl.count > 0 then
2750 FOR i IN l_line_conc_tbl.FIRST .. l_line_conc_tbl.LAST LOOP
2751 IF i = 1 THEN
2752 IF l_line_conc_tbl(i).line_id IS NOT NULL THEN
2753 l_line_id := l_line_conc_tbl(i).line_id;
2754 IF l_debug_level > 0 THEN
2755 oe_debug_pub.add(' Query Line for Split'||l_line_conc_tbl(i).LINE_ID,3) ;
2756 END IF;
2757 l_x_line_tbl(i).line_id := l_line_conc_tbl(i).line_id;
2758 OE_Line_Util.Lock_Row
2759 ( x_return_status => l_return_status
2760 , p_x_line_rec => l_x_line_tbl(i)
2761 , p_line_id => l_line_conc_tbl(i).line_id);
2762
2763 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2765 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2766 RAISE FND_API.G_EXC_ERROR;
2767 END IF;
2768 oe_debug_pub.add('After Query Line for Split',3) ;
2769 --10278858
2770 l_org_request_date := l_x_line_tbl(i).request_date;
2771 l_org_ship_from_org_id := l_x_line_tbl(i).ship_from_org_id;
2772 l_org_ship_to_org_id := l_x_line_tbl(i).ship_to_org_id;
2773
2774 END IF;
2775 l_x_line_tbl(i).line_id := l_line_conc_tbl(i).line_id;
2776 l_x_line_tbl(i).split_action_code := 'SPLIT';
2777 IF l_line_conc_tbl(i).split_by = 'SCHEDULER' THEN
2778 l_x_line_tbl(i).split_by := 'SYSTEM';
2779 l_x_line_tbl(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
2780 ELSE
2781 l_x_line_tbl(i).split_by := 'USER';
2782 l_x_line_tbl(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
2783 END IF;
2784 l_x_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
2785 IF l_debug_level > 0 THEN
2786 OE_DEBUG_PUB.add('Audit Trail Reason Code being passed as '||
2787 l_line_conc_tbl(i).change_reason_code,1);
2788 END IF;
2789 l_x_line_tbl(i).change_reason := l_line_conc_tbl(i).change_reason_code;
2790 l_x_line_tbl(i).change_comments := l_line_conc_tbl(i).change_reason_comment;
2791 ELSE
2792 IF l_line_id is not null then
2793 l_x_line_tbl(i).split_from_line_id := l_line_id;
2794 END IF;
2795 l_x_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
2796 IF l_line_conc_tbl(i).split_by = 'SCHEDULER' THEN
2797 l_x_line_tbl(i).split_by := 'SYSTEM';
2798 l_x_line_tbl(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
2799 ELSE
2800 l_x_line_tbl(i).split_by := 'USER';
2801 l_x_line_tbl(i).request_id := FND_GLOBAL.CONC_REQUEST_ID;
2802 END IF;
2803 END IF;
2804 l_x_line_tbl(i).ordered_quantity := l_line_conc_tbl(i).ordered_quantity;
2805 l_x_line_tbl(i).ordered_quantity2 := l_line_conc_tbl(i).ordered_quantity2;
2806 --8706868 Start
2807 IF l_line_conc_tbl(i).ship_to_org_id IS NOT NULL THEN
2808 IF NVL(l_x_line_tbl(i).ship_to_org_id,-1) <>
2809 NVL(l_line_conc_tbl(i).ship_to_org_id,-1) THEN
2810 l_x_line_tbl(i).ship_to_org_id := l_line_conc_tbl(i).ship_to_org_id;
2811 --10278858
2812 IF l_x_line_tbl(i).ship_to_org_id <> l_org_ship_to_org_id THEN
2813 l_x_line_tbl(i).SPLIT_SHIP_TO := 'Y';
2814 END IF;
2815 -- l_process_add_attributes := TRUE;
2816 END IF;
2817 END IF;
2818
2819 IF l_line_conc_tbl(i).request_date IS NOT NULL THEN
2820 IF NVL(l_x_line_tbl(i).request_date,SYSDATE) <>
2821 NVL(l_line_conc_tbl(i).request_date,SYSDATE) THEN
2822 l_x_line_tbl(i).request_date := l_line_conc_tbl(i).request_date;
2823 --10278858
2824 IF l_x_line_tbl(i).request_date <> l_org_request_date THEN
2825 l_x_line_tbl(i).SPLIT_REQUEST_DATE := 'Y';
2826 END IF;
2827 -- l_process_add_attributes := TRUE;
2828 END IF;
2829 END IF;
2830
2831 IF l_line_conc_tbl(i).ship_from_org_id IS NOT NULL THEN
2832 IF NVL(l_x_line_tbl(i).ship_from_org_id,-1) <>
2833 NVL(l_line_conc_tbl(i).ship_from_org_id,-1) THEN
2834 l_x_line_tbl(i).ship_from_org_id := l_line_conc_tbl(i).ship_from_org_id;
2835 l_x_line_tbl(i).subinventory := null;
2836 --10278858
2837 IF l_x_line_tbl(i).ship_from_org_id <> l_org_ship_from_org_id THEN
2838 l_x_line_tbl(i).SPLIT_SHIP_FROM := 'Y';
2839 END IF;
2840 -- l_process_add_attributes := TRUE;
2841 END IF;
2842 END IF;
2843 --8706868 End
2844 END LOOP;
2845 oe_debug_pub.add('From Defer Split - Calling Process Order',1) ;
2846
2847 Oe_Order_Pvt.Process_order
2848 ( p_api_version_number => 1.0
2849 , p_init_msg_list => FND_API.G_TRUE
2850 , x_return_status => l_return_status
2851 , x_msg_count => l_msg_count
2852 , x_msg_data => l_msg_data
2853 , p_control_rec => l_control_rec
2854 , p_x_header_rec => l_x_header_rec
2855 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
2856 , p_x_header_price_att_tbl => l_x_header_price_att_tbl
2857 , p_x_Header_Adj_att_tbl => l_x_Header_Adj_att_tbl
2858 , p_x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
2859 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
2860 , p_x_Header_Payment_tbl => l_x_Header_Payment_tbl
2861 , p_x_line_tbl => l_x_line_tbl
2862 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
2863 , p_x_Line_Price_att_tbl => l_x_Line_Price_att_tbl
2864 , p_x_Line_Adj_att_tbl => l_x_Line_Adj_att_tbl
2865 , p_x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
2866 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
2867 , p_x_Line_Payment_tbl => l_x_Line_Payment_tbl
2868 , p_x_action_request_tbl => l_x_Action_Request_tbl
2869 , p_x_lot_serial_tbl => l_x_lot_serial_tbl
2870
2871 );
2872
2873 END IF;
2874 oe_debug_pub.add('From Defer Split - After Calling Process Order',1) ;
2875
2876 l_msg_total := l_msg_total + l_msg_count;
2877
2878 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2880 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2881 RAISE FND_API.G_EXC_ERROR;
2882 END IF;
2883
2884 IF l_x_line_tbl.count > 0 THEN
2885 l_line_tbl.delete;
2886 /* 8706868
2887 FOR i IN l_line_conc_tbl.FIRST .. l_line_conc_tbl.LAST LOOP
2888 l_line_tbl(i):= OE_ORDER_PUB.G_MISS_LINE_REC;
2889 IF l_line_conc_tbl(i).ship_to_org_id IS NOT NULL THEN
2890 IF NVL(l_x_line_tbl(i).ship_to_org_id,-1) <>
2891 NVL(l_line_conc_tbl(i).ship_to_org_id,-1) THEN
2892 l_line_tbl(i).ship_to_org_id := l_line_conc_tbl(i).ship_to_org_id;
2893 l_process_add_attributes := TRUE;
2894 END IF;
2895 END IF;
2896
2897 IF l_line_conc_tbl(i).request_date IS NOT NULL THEN
2898 IF NVL(l_x_line_tbl(i).request_date,SYSDATE) <>
2899 NVL(l_line_conc_tbl(i).request_date,SYSDATE) THEN
2900 l_line_tbl(i).request_date := l_line_conc_tbl(i).request_date;
2901 l_process_add_attributes := TRUE;
2902 END IF;
2903 END IF;
2904
2905 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
2906 oe_debug_pub.add('Reason code being passed : '||l_line_tbl(i).change_reason,1);
2907
2908 l_line_tbl(i).change_reason := l_line_conc_tbl(i).change_reason_code;
2909 l_line_tbl(i).change_comments := l_line_conc_tbl(i).change_reason_comment;
2910 l_line_tbl(i).split_action_code:= FND_API.G_MISS_CHAR;
2911 l_line_tbl(i).line_id := l_x_line_tbl(i).line_id;
2912 l_line_tbl(i).header_id := l_x_line_tbl(i).header_id;
2913
2914 IF l_line_conc_tbl(i).ship_from_org_id IS NOT NULL THEN
2915 IF NVL(l_x_line_tbl(i).ship_from_org_id,-1) <>
2916 NVL(l_line_conc_tbl(i).ship_from_org_id,-1) THEN
2917 l_line_tbl(i).ship_from_org_id := l_line_conc_tbl(i).ship_from_org_id;
2918 -- ship_from_org_id is changed during split, null out subinventory
2919 l_line_tbl(i).subinventory := null;
2920 l_process_add_attributes := TRUE;
2921 END IF;
2922 END IF;
2923 END LOOP;
2924 */ -- 8706868
2925 /* Extra loop being handled so as to deal with the service lines */
2926 -- At this point l_line_tbl contains the order line that was split as well as the
2927 -- the order lines that are created as a result of the split. Prior to this
2928 -- modification only these records were being passed to the Process Order API
2929 -- ( second call ) and there is no code in the Process Order API that would update
2930 -- the service lines if split attributes are modified in the order lines to which
2931 -- they refer to.
2932 -- In order to make the Process Order API handle the service lines, the service
2933 -- lines are explicitly being bundled with the order lines in the l_line_tbl. The
2934 -- Following loop takes care of that. The loop is coded taking Models, Standard
2935 -- items and Kits into consideration
2936 -- Local variables j, k, l_rec_count have been added. The variables j, k
2937 -- are used as a loop indices, and l_rec_count is used to work on the l_line_tbl.
2938 --
2939 -- l_line_id would contain the line_id of the line to which the service line is
2940 -- attached to or the top_most_line_id if the line to which its attached to is a
2941 -- part of a Model.
2942 -- This is done because splitting happens at the top most level.
2943
2944 --8706868
2945 --l_rec_count := l_line_tbl.last + 1;
2946 l_rec_count := 1;
2947
2948 FOR i IN l_x_line_tbl.FIRST .. l_x_line_tbl.LAST LOOP
2949 IF l_x_line_tbl(i).item_type_code = 'SERVICE' THEN
2950 l_line_tbl(l_rec_count) := OE_ORDER_PUB.G_MISS_LINE_REC;
2951 FOR j IN l_x_line_tbl.FIRST .. l_x_line_tbl.LAST LOOP
2952 IF l_x_line_tbl(i).service_reference_line_id = l_x_line_tbl(j).line_id THEN
2953 l_line_id := NVL(l_x_line_tbl(j).top_model_line_id,
2954 l_x_line_tbl(j).line_id);
2955 l_ref_split_ship_from := l_x_line_tbl(j).split_ship_from; --16525601
2956 EXIT;
2957 END IF; -- service_Ref_line = line_id
2958 END LOOP; -- loop on l_x_line_tbl
2959
2960 FOR k in l_line_conc_tbl.first .. l_line_conc_tbl.last LOOP
2961 IF l_line_id = l_x_line_tbl(k).line_id THEN
2962 IF l_line_conc_tbl(k).ship_to_org_id is not null THEN
2963 IF NVL(l_x_line_tbl(i).ship_to_org_id,-1) <>
2964 NVL(l_line_conc_tbl(k).ship_to_org_id,-1) THEN
2965 l_line_tbl(l_rec_count).ship_to_org_id :=
2966 l_line_conc_tbl(k).ship_to_org_id;
2967 l_process_add_attributes := TRUE;
2968 END IF; -- if ship_to_changed
2969 END IF; -- ship_to not null
2970 -- Code added for bug 2216899
2971 IF l_line_conc_tbl(k).ship_from_org_id is not null THEN
2972 IF NVL(l_x_line_tbl(i).ship_from_org_id,-1) <>
2973 NVL(l_line_conc_tbl(k).ship_from_org_id,-1) THEN
2974 IF l_ref_split_ship_from = 'Y' THEN --16525601
2975 l_line_tbl(l_rec_count).ship_from_org_id :=
2976 l_line_conc_tbl(k).ship_from_org_id;
2977 l_line_tbl(l_rec_count).subinventory := fnd_api.g_miss_char; --Added as a part of 16525601 for Bug 13560415 , do not use nullnull;
2978 ELSE
2979 oe_debug_pub.ADD('Inside else Condition');
2980 l_line_tbl(l_rec_count).ship_from_org_id := l_x_line_tbl(i).ship_from_org_id;
2981 l_line_tbl(l_rec_count).subinventory := fnd_api.g_miss_char; --Added as a part of 16525601 for Bug 13560415, do not use nullnull;
2982 end if ; --16525601
2983 l_process_add_attributes := TRUE;
2984 END IF; -- if ship_from_changed
2985 END IF; -- ship_from not null
2986 -- end 2216899
2987 IF l_line_conc_tbl(k).request_date is not null THEN
2988 IF NVL(l_x_line_tbl(i).request_date,SYSDATE) <>
2989 NVL(l_line_conc_tbl(k).request_date,SYSDATE) THEN
2990 l_line_tbl(l_rec_count).request_date :=
2991 l_line_conc_tbl(k).request_date;
2992 l_process_add_attributes := TRUE;
2993 END IF;
2994 END IF;
2995
2996 l_line_tbl(l_rec_count).operation := OE_GLOBALS.G_OPR_UPDATE;
2997 oe_debug_pub.add('Reason code being passed : '||
2998 l_line_tbl(l_rec_count).change_reason,1);
2999 l_line_tbl(l_rec_count).change_reason :=
3000 l_line_conc_tbl(k).change_reason_code;
3001 l_line_tbl(l_rec_count).change_comments :=
3002 l_line_conc_tbl(k).change_reason_comment;
3003 l_line_tbl(l_rec_count).split_action_code := FND_API.G_MISS_CHAR;
3004 l_line_tbl(l_rec_count).line_id := l_x_line_tbl(i).line_id;
3005 l_line_tbl(l_rec_count).header_id := l_x_line_tbl(i).header_id;
3006
3007 l_rec_count := l_rec_count + 1;
3008 EXIT;
3009 END IF; -- if l_line_id matches a line_id in l_line_conc_tbl
3010 END LOOP; -- loop on index k
3011 END IF; -- If item_type_code = 'SERVICE'
3012 END LOOP; -- First For Loop
3013 /* end of 1988144 */
3014 /* this l_line_tbl is passed to the process Order API */
3015
3016 l_x_Header_Adj_tbl.DELETE;
3017 l_x_header_price_att_tbl.DELETE;
3018 l_x_Header_Adj_att_tbl.DELETE;
3019 l_x_Header_Adj_Assoc_tbl.DELETE;
3020 l_x_Header_Scredit_tbl.DELETE;
3021 l_x_Line_Adj_tbl.DELETE;
3022 l_x_Line_Price_att_tbl.DELETE;
3023 l_x_Line_Adj_att_tbl.DELETE;
3024 l_x_Line_Adj_Assoc_tbl.DELETE;
3025 l_x_Line_Scredit_tbl.DELETE;
3026 l_x_lot_serial_tbl.DELETE;
3027
3028 IF l_process_add_attributes THEN
3029 oe_debug_pub.add('calling process_order for service lines from oe_split_util.defer_split',5);
3030 Oe_Order_Pvt.Process_order
3031 ( p_api_version_number => 1.0
3032 , p_init_msg_list => FND_API.G_TRUE
3033 , x_return_status => l_return_status
3034 , x_msg_count => l_msg_count
3035 , x_msg_data => l_msg_data
3036 , p_control_rec => l_control_rec
3037 , p_x_line_tbl => l_line_tbl
3038 , p_x_header_rec => l_x_header_rec
3039 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
3040 , p_x_header_price_att_tbl => l_x_header_price_att_tbl
3041 , p_x_Header_Adj_att_tbl => l_x_Header_Adj_att_tbl
3042 , p_x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
3043 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
3044 , p_x_Header_Payment_tbl => l_x_Header_Payment_tbl
3045 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
3046 , p_x_Line_Price_att_tbl => l_x_Line_Price_att_tbl
3047 , p_x_Line_Adj_att_tbl => l_x_Line_Adj_att_tbl
3048 , p_x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
3049 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
3050 , p_x_Line_Payment_tbl => l_x_Line_Payment_tbl
3051 , p_x_action_request_tbl => l_x_Action_Request_tbl
3052 , p_x_lot_serial_tbl => l_x_lot_serial_tbl
3053
3054 );
3055 END IF;
3056
3057 l_msg_total := l_msg_total + l_msg_count;
3058
3059 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3061 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3062 RAISE FND_API.G_EXC_ERROR;
3063 END IF;
3064 END IF;
3065
3066 -- Irrespective of program result, deleting the data from table
3067 DELETE FROM oe_line_split_details
3068 WHERE line_id = l_init_line_id
3069 AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
3070
3071 ELSE -- l_count < 2
3072 Fnd_Message.Set_Name('ONT','OE_CANCEL_SPLIT_SUCCESS');
3073 Fnd_Message.Set_Token('REQUEST_ID',FND_GLOBAL.CONC_REQUEST_ID);
3074 OE_Msg_Pub.Add;
3075 FND_FILE.put_line(FND_FILE.output,OE_MSG_PUB.Get(OE_MSG_PUB.G_LAST,FND_API.G_FALSE));
3076 RETURN;
3077
3078 END IF;
3079
3080 -- Get message count and data
3081 oe_msg_pub.count_and_get
3082 ( p_count => l_msg_count
3083 , p_data => l_msg_data
3084 ); l_msg_total := l_msg_count;
3085
3086 FND_FILE.put_line(FND_FILE.output,'Please check the debug log for errors');
3087 IF NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'), 0) <> 0 THEN
3088 -- Called from concurrent request
3089 IF l_msg_total > 0 THEN
3090 FOR I IN 1 .. l_msg_total LOOP
3091 l_msg_data := to_char(I)||'. '||OE_MSG_PUB.Get(I,FND_API.G_FALSE);
3092 FND_FILE.put_line(FND_FILE.output, l_msg_data);
3093 -- Writing messages into the concurrent request output file
3094 END LOOP;
3095 oe_debug_pub.add(' Saving the Messages');
3096 oe_msg_pub.save_messages(p_request_id => FND_GLOBAL.CONC_REQUEST_ID);
3097 -- Bug 6964815
3098
3099 ELSE
3100 FND_FILE.put_line(FND_FILE.output,' << No Errors or Warnings reported >>');
3101 END IF;
3102 END IF;
3103 COMMIT;
3104 oe_debug_pub.add('Exiting Procedure OE_Split_Util.Defer_Split', 1);
3105
3106 EXCEPTION
3107 WHEN OTHERS THEN
3108 retcode := 2;
3109 oe_debug_pub.add('Inside the exception block of oe_split_util.defer_split',5);
3110 IF NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'), 0) <> 0 THEN
3111 -- Called from concurrent request */
3112 IF l_msg_total > 0 THEN
3113 FOR I IN 1 .. l_msg_total LOOP
3114 l_msg_data := to_char(I)||'. '||OE_MSG_PUB.Get(I,FND_API.G_FALSE);
3115 FND_FILE.put_line(FND_FILE.output, l_msg_data);
3116 -- Writing messages into the concurrent request output file
3117 END LOOP;
3118 oe_msg_pub.save_messages(p_request_id => FND_GLOBAL.CONC_REQUEST_ID);
3119 -- Bug 6964815
3120 END IF;
3121 END IF;
3122
3123 IF sqlcode <> 0 THEN
3124 errbuf := sqlerrm;
3125 ELSE
3126 errbuf := 'Concurrent program did not finish successfully. See error messages and log file for more details';
3127 END IF;
3128 ROLLBACK;
3129 -- Irrespective of program result, deleting the data from table
3130 DELETE FROM oe_line_split_details
3131 WHERE line_id = l_init_line_id
3132 AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
3133 COMMIT;
3134 END Defer_Split;
3135
3136 PROCEDURE Bulk_Insert (p_line_conc_tbl IN Split_Line_Tbl_Type) is
3137
3138 begin
3139 oe_debug_pub.add('entering oe_split_util.bulk_insert');
3140
3141 FORALL j IN p_line_conc_tbl.FIRST .. p_line_conc_tbl.LAST
3142 INSERT INTO OE_LINE_SPLIT_DETAILS
3143 VALUES p_line_conc_tbl(j);
3144
3145 exception
3146 WHEN OTHERS THEN
3147 oe_debug_pub.add('failed in bulk insert API'||sqlerrm);
3148 raise;
3149 end Bulk_Insert;
3150
3151 /* Defer Split ER Changes END */
3152
3153 END OE_Split_Util;