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