1 PACKAGE BODY OE_RSCH_SETS_CONC_REQUESTS AS
2 /* $Header: OEXCRSHB.pls 120.4 2005/12/15 17:36:42 akurella noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_RSCH_SETS_CONC_REQUESTS';
7
8 Procedure Schedule_set_of_lines
9 ( x_old_line_tbl OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
10
11 p_x_line_tbl IN OUT NOCOPY OE_ORDER_PUB.line_tbl_type,
12 x_return_status OUT NOCOPY VARCHAR2)
13
14 IS
15 l_atp_tbl OE_ATP.ATP_Tbl_Type;
16 l_line_tbl OE_ORDER_PUB.line_tbl_type;
17 l_ii_line_tbl OE_ORDER_PUB.line_tbl_type;
18 l_return_status VARCHAR2(1);
19 K NUMBER := 0;
20 J NUMBER := 0;
21 l_sales_order_id NUMBER;
22 l_entity_type VARCHAR2(30);
23 l_set_rec OE_ORDER_CACHE.set_rec_type; -- 2887734
24 l_ship_set_id NUMBER :=0;
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29
30 IF l_debug_level > 0 THEN
31 oe_debug_pub.add( 'ENTERING SCHEDULE_SET_OF_LINES' , 1 ) ;
32 END IF;
33
34 -- This procedure is called from the SETS api. The sets API has taken
35 -- care of of validation that needed to be done for the lines
36 -- to be scheduled together. i.e It has made sure that all the scheduling
37 -- attributes are sames across the line. We will just pass the request to
38 -- Process_Set_of_lines for scheduling. I am introducing this procedure
39 -- in between sets and Process_set_of_lines, just for the sake that if
40 -- we need to add some SET Api specific logic, then we can add that here.
41
42 -- Let's first validate the lines passed to us. We will validate
43 -- the attributes that we need for scheduling.
44
45
46 FOR I IN 1..p_x_line_tbl.count LOOP
47 BEGIN
48 IF p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
49
50 -- Service items cannot be scheduled, so we will skip them.
51 -- Included items will be picked up by their parent, so we will
52 -- skip them.
53 IF l_debug_level > 0 THEN
54 oe_debug_pub.add( 'LINE IS A SERVICE OR INCLUDED ITEM' , 1 ) ;
55 END IF;
56
57 ELSE
58 K := K + 1;
59 l_line_tbl(K) := p_x_line_tbl(I);
60 l_line_tbl(K).schedule_action_code :=
61 OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
62 l_line_tbl(K).schedule_ship_date := l_line_tbl(K).request_date;
63 l_line_tbl(K).schedule_arrival_date := Null;
64 l_line_tbl(K).operation := 'UPDATE';
65
66 x_old_line_tbl(K) := p_x_line_tbl(I);
67
68 IF (p_x_line_tbl(I).ato_line_id is null) AND
69 (p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
70 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
71 p_x_line_tbl(I).item_type_code = OE_GLOBALS.G_ITEM_KIT) THEN
72
73 -- Calling Process_Included_Items. This procedure
74 -- will take care of exploding and updating the picture
75 -- of included_items in the oe_order_lines table.
76
77 l_return_status := OE_CONFIG_UTIL.Process_Included_Items
78 (p_line_rec => p_x_line_tbl(I),
79 p_freeze => FALSE,
80 p_process_requests => TRUE);
81
82 IF l_debug_level > 0 THEN
83 oe_debug_pub.add( 'AFTER CALLING PROCESS_INCLUDED_ITEMS ' , 1 ) ;
84 END IF;
85
86 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91
92 OE_Config_Util.Query_Included_Items
93 (p_line_id => p_x_line_tbl(I).line_id,
94 x_line_tbl => l_ii_line_tbl);
95
96 IF l_debug_level > 0 THEN
97 oe_debug_pub.add( 'MERGING INCLUDED ITEM TABLE WITH LINE TABLE' , 1 ) ;
98 END IF;
99
100 -- Merge the Included Item table to the line table
101 FOR J IN 1..l_ii_line_tbl.count LOOP
102 K := K+1;
103 l_line_tbl(k) := l_ii_line_tbl(J);
104 x_old_line_tbl(K) := l_ii_line_tbl(J);
105 l_line_tbl(k).operation := OE_GLOBALS.G_OPR_UPDATE;
106 l_line_tbl(k).ship_set_id :=
107 p_x_line_tbl(I).ship_set_id;
108 l_line_tbl(k).arrival_set_id :=
109 p_x_line_tbl(I).arrival_set_id;
110 l_line_tbl(k).schedule_ship_date := l_line_tbl(k).request_date;
111 l_line_tbl(k).schedule_arrival_date :=Null;
112 l_line_tbl(k).operation :='UPDATE';
113 l_line_tbl(k).schedule_action_code :=
114 OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE;
115 END LOOP;
116 END IF;
117 END IF;
118
119 EXCEPTION
120
121 WHEN OTHERS THEN
122
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END;
125 END LOOP;
126
127 IF l_debug_level > 0 THEN
128 oe_debug_pub.add( 'CALLING PROCESS_SET_OF_LINES WITH : ' || L_LINE_TBL.COUNT , 1 ) ;
129 END IF;
130
131 FOR I IN 1..l_line_tbl.count LOOP
132
133 IF I = 1 THEN
134
135 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
136 (l_line_tbl(1).HEADER_ID);
137 END IF;
138 -- If any of the lines are previously scheduled, then pass
139 -- action as reschedule to MRP.
140
141
142
143 IF l_line_tbl(I).schedule_status_code is not null THEN
144 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
145
146 OE_LINE_UTIL.Get_Reserved_Quantities( p_header_id => l_sales_order_id
147 ,p_line_id => l_line_tbl(I).line_id
148 ,p_org_id => l_line_tbl(I).ship_from_org_id
149 ,x_reserved_quantity => l_line_tbl(I).reserved_quantity
150 ,x_reserved_quantity2 => l_line_tbl(I).reserved_quantity2
151 );
152 x_old_line_tbl(I).reserved_quantity := l_line_tbl(I).reserved_quantity;
153 x_old_line_tbl(I).reserved_quantity2 := l_line_tbl(I).reserved_quantity2;
154
155 /* l_line_tbl(I).reserved_quantity :=
156 OE_LINE_UTIL.Get_Reserved_Quantity
157 (p_header_id => l_sales_order_id,
158 p_line_id => l_line_tbl(I).line_id,
159 p_org_id => l_line_tbl(I).ship_from_org_id);
160 x_old_line_tbl(I).reserved_quantity := l_line_tbl(I).reserved_quantity;
161
162 l_line_tbl(I).reserved_quantity2 := -- INVCONV
163 OE_LINE_UTIL.Get_Reserved_Quantity2
164 (p_header_id => l_sales_order_id,
165 p_line_id => l_line_tbl(I).line_id,
166 p_org_id => l_line_tbl(I).ship_from_org_id);
167 x_old_line_tbl(I).reserved_quantity2 := l_line_tbl(I).reserved_quantity2; */
168
169 END IF;
170
171 IF l_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
172 OR l_line_tbl(I).reserved_quantity IS NULL THEN
173 l_line_tbl(I).reserved_quantity := 0;
174 END IF;
175 IF x_old_line_tbl(I).reserved_quantity = FND_API.G_MISS_NUM
176 OR x_old_line_tbl(I).reserved_quantity IS NULL THEN
177 x_old_line_tbl(I).reserved_quantity := 0;
178 END IF;
179
180 IF l_line_tbl(I).reserved_quantity2 = FND_API.G_MISS_NUM -- INVCONV
181 OR l_line_tbl(I).reserved_quantity2 IS NULL THEN
182 l_line_tbl(I).reserved_quantity2 := 0;
183 END IF;
184 IF x_old_line_tbl(I).reserved_quantity2 = FND_API.G_MISS_NUM -- INVCONV
185 OR x_old_line_tbl(I).reserved_quantity2 IS NULL THEN
186 x_old_line_tbl(I).reserved_quantity2 := 0;
187 END IF;
188
189
190
191
192
193 --- 2887734
194 -- get set name
195 IF l_line_tbl(I).ship_set_id IS NOT NULL THEN
196 IF NOT OE_GLOBALS.Equal(l_line_tbl(I).ship_set_id,
197 l_ship_set_id) THEN
198 l_ship_set_id := l_line_tbl(I).ship_set_id;
199 l_set_rec := OE_ORDER_CACHE.Load_Set(l_ship_set_id);
200 END IF;
201 l_line_tbl(I).ship_set := l_set_rec.set_name;
202 END IF;
203 --- 2887734
204 END LOOP;
205
206 IF l_line_tbl.count > 0 THEN
207 IF l_debug_level > 0 THEN
208 oe_debug_pub.add( 'CALLING PROCESS_GROUP' || L_LINE_TBL ( 1 ) .SHIP_SET_ID , 1 ) ;
209 END IF;
210
211 Oe_Config_Schedule_Pvt.Process_Group
212 (p_x_line_tbl => l_line_tbl
213 ,p_old_line_tbl => x_old_line_tbl
214 ,p_caller => 'UI_ACTION'
215 ,p_sch_action => OE_SCHEDULE_UTIL.OESCH_ACT_RESCHEDULE
216 ,x_return_status => x_return_status);
217
218 IF l_debug_level > 0 THEN
219 oe_debug_pub.add( 'AFTER CALLING PROCESS_GROUP' || X_RETURN_STATUS , 1 ) ;
220 END IF;
221 END IF;
222
223 p_x_line_tbl := l_line_tbl;
224 IF l_debug_level > 0 THEN
225 oe_debug_pub.add( 'EXITING SCHEDULE_SET_OF_LINES' , 1 ) ;
226 END IF;
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230
231 x_return_status := FND_API.G_RET_STS_ERROR;
232
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236
237 WHEN OTHERS THEN
238
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240
241 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
242 THEN
243 OE_MSG_PUB.Add_Exc_Msg
244 ( G_PKG_NAME
245 , 'Schedule_set_of_lines'
246 );
247 END IF;
248
249 END Schedule_set_of_lines;
250
251 /* Reschedule Set: This concurrent program is to Reschedule the Ship Sets */
252 PROCEDURE Reschedule_Ship_Set(
253 ERRBUF OUT NOCOPY VARCHAR2
254
255 ,RETCODE OUT NOCOPY VARCHAR2
256
257 ,p_order_number_low IN NUMBER
258 ,p_order_number_high IN NUMBER
259 ,p_start_from_no_of_days IN NUMBER
260 ,p_end_from_no_of_days IN NUMBER
261 ,p_set_id IN NUMBER)
262 IS
263 l_set_name varchar2(240);
264 /* MOAC_SQL_CHANGE */
265 CURSOR Line_Set(l_start_date DATE,l_end_date DATE) IS
266 SELECT distinct l.ship_set_id, h.order_number
267 FROM oe_order_headers h
268 ,oe_order_lines_all l
269 , oe_sets s
270 WHERE h.order_number >= nvl(p_order_number_low,h.order_number)
271 AND h.order_number <= nvl(p_order_number_high,h.order_number)
272 AND h.header_id = l.header_id
273 AND h.org_id = l.org_id
274 AND h.open_flag = 'Y'
275 AND trunc(l.Schedule_ship_date) BETWEEN trunc(l_start_date) and trunc(l_end_date)
276 AND l.open_flag = 'Y'
277 AND l.ship_set_id IS NOT NULL
278 AND l.ship_set_id = s.set_id
279 AND h.header_id = s.header_id
280 AND s.set_name = nvl(l_set_name,s.set_name)
281 AND l.shipped_quantity IS NULL
282 AND l.fulfilled_quantity IS NULL
283 ORDER BY l.ship_set_id ;
284
285 CURSOR Lock_Lines_in_set(l_ship_set_id NUMBER) IS
286 SELECT l.line_id
287 FROM oe_order_lines l
288 WHERE l.ship_set_id = l_ship_set_id
289 FOR UPDATE;
290
291 CURSOR Get_Set_Name IS
292 Select Set_Name from
293 oe_sets where
294 set_id = p_set_id;
295
296
297 l_start_date DATE;
298 l_end_date DATE;
299 l_set_line_tbl OE_Order_PUB.Line_Tbl_Type;
300 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
301 l_line_rec OE_Order_PUB.Line_Rec_Type;
302 l_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
303 l_control_rec OE_GLOBALS.control_rec_type;
304 g_set_recursive_flag BOOLEAN := FALSE;
305 l_msg_count number;
306 l_msg_data varchar2(32000);
307
308
309 --
310 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
311 --
312 BEGIN
313 --Initialze retcode #4220950
314 ERRBUF := '';
315 RETCODE := 0;
316
317 l_start_date := SYSDATE + NVL(p_start_from_no_of_days,0);
318 l_end_date := SYSDATE + NVL(p_end_from_no_of_days,0);
319
320
321 fnd_file.put_line(FND_FILE.LOG, 'Parameters:');
322
323 fnd_file.put_line(FND_FILE.LOG, ' order_number_low = '||
324 p_order_number_low);
325 fnd_file.put_line(FND_FILE.LOG, ' order_number_high = '||
326 p_order_number_high);
327 fnd_file.put_line(FND_FILE.LOG, ' Start From Number of Days = '||
328 p_start_from_no_of_days);
329 fnd_file.put_line(FND_FILE.LOG, ' End From Number of Days = '||
330 p_end_from_no_of_days);
331 fnd_file.put_line(FND_FILE.LOG, ' Ship Set Number = '||
332 p_set_id);
333
334 IF l_debug_level > 0 THEN
335 oe_debug_pub.add( 'INSIDE THE RESCHEDULING SHIP SETS CONCURRENT PROGRAM' ) ;
336 END IF;
337 IF p_set_id is not null then
338 OPEN Get_Set_Name;
339 FETCH Get_Set_Name
340 into l_set_name;
341 CLOSE Get_Set_Name;
342 END IF;
343 fnd_file.put_line(FND_FILE.LOG, ' Ship Set Name = '||
344 l_set_name);
345
346
347 FOR Get_Line_set IN Line_Set(l_start_date,l_end_date)
348 LOOP
349
350 SAVEPOINT Process_lines_in_set;
351 IF l_debug_level > 0 THEN
352 oe_debug_pub.add( 'ORDER NUMBER:'||GET_LINE_SET.ORDER_NUMBER ) ;
353 END IF;
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'SHIP SET ID:'||GET_LINE_SET.SHIP_SET_ID ) ;
356 END IF;
357
358 -- With the above cursor we have got the list of all the Ship Sets
359 -- for the orders.
360 -- Now for every Ship Set perform the ReScheduling.
361
362 -- We need to query all the lines that are a part of the Ship Set.
363 OE_SET_UTIL.Query_set_Rows
364 ( p_set_id => Get_Line_set.ship_set_id
365 ,x_line_tbl => l_set_line_tbl );
366
367 IF l_debug_level > 0 THEN
368 oe_debug_pub.add( 'AFTER THE LINES ARE QUERIED' ) ;
369 END IF;
370
371 -- We need to set the Action on each line as OESCH_ACT_RESCHEDULE
372 -- The Scheduled Dates and the Arrival Date have to be set to NULl
373 -- so that the lines get rescheduled.
374 IF l_debug_level > 0 THEN
375 oe_debug_pub.add( 'AFTER SETTING THE SCHEDULE STATUS CODE' ) ;
376 END IF;
377
378 -- Code should change for re-structuring purpose. If it a old code
379 -- follow old path or else follow new path.
380
381 --ELSE -- Re-structure path.
382 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
383 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'N';
384 OPEN Lock_Lines_in_set(Get_Line_set.ship_set_id);
385 CLOSE Lock_Lines_in_set;
386
387 IF l_debug_level > 0 THEN
388 oe_debug_pub.add( 'NEW CODE' , 1 ) ;
389 END IF;
390 Schedule_Set_Of_lines
391 ( p_x_line_tbl => l_set_line_tbl
392 ,x_old_line_tbl => l_old_line_tbl
393 ,x_return_status => l_return_status );
394
395 OE_MSG_PUB.Count_And_Get
396 ( p_count => l_msg_count
397 , p_data => l_msg_data);
398
399 FOR T in 1..l_msg_count loop
400 l_msg_data := OE_MSG_PUB.Get(T,'F');
401
402 -- Write Messages in the log file
403
404 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
405
406 -- Write the message to the database
407
408 END LOOP;
409
410 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
411 IF l_debug_level > 0 THEN
412 oe_debug_pub.add( 'INSIDE THE UNEXPECTED ERROR FOR THE FOLLOWING DATA:' ) ;
413 oe_debug_pub.add( 'ORDER NUMBER:'||GET_LINE_SET.ORDER_NUMBER ||' AND '||
414 'SHIP SET:'||GET_LINE_SET.SHIP_SET_ID||' HAS FAILED RESCHEDULING' ) ;
415 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
416 END IF;
417 ROLLBACK TO SAVEPOINT Process_lines_in_set;
418
419 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
420 IF l_debug_level > 0 THEN
421 oe_debug_pub.add( 'INSIDE THE EXPECTED ERROR FOR THE FOLLOWING DATA :' ) ;
422 oe_debug_pub.add( 'ORDER NUMBER:'||GET_LINE_SET.ORDER_NUMBER ||' AND '||
423 'SHIP SET:'||GET_LINE_SET.SHIP_SET_ID||' HAS FAILED RESCHEDULING' ) ;
424 END IF;
425 ROLLBACK TO SAVEPOINT Process_lines_in_set;
426
427 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
428
429 OE_SCHEDULE_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
430 OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING := 'Y';
431
432 IF l_set_line_tbl.count > 0 AND
433 ( l_set_line_tbl(1).Schedule_Ship_Date <>
434 l_old_line_tbl(1).Schedule_Ship_Date) THEN
435
436 OE_Set_Util.Update_Set
437 (p_Set_Id => l_set_line_tbl(1).ship_set_id,
438 p_Ship_From_Org_Id => l_set_line_tbl(1).Ship_From_Org_Id,
439 p_Ship_To_Org_Id => l_set_line_tbl(1).Ship_To_Org_Id,
440 p_Schedule_Ship_Date => l_set_line_tbl(1).Schedule_Ship_Date,
441 p_Schedule_Arrival_Date => l_set_line_tbl(1).Schedule_Arrival_Date,
442 p_Freight_Carrier_Code => l_set_line_tbl(1).Freight_Carrier_Code,
443 p_Shipping_Method_Code => l_set_line_tbl(1).Shipping_Method_Code,
444 p_shipment_priority_code => l_set_line_tbl(1).shipment_priority_code,
445 X_Return_Status => l_return_status,
446 x_msg_count => l_msg_count,
447 x_msg_data => l_msg_data
448 );
449 END IF;
450
451 -- Now we will commit the entire Set together
452 COMMIT;
453
454
455 END IF; -- Return status
456 END LOOP; -- Main Loop(Get_Line_set)
457
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 IF l_debug_level > 0 THEN
462 oe_debug_pub.add( 'INSIDE THE WHEN OTHERS EXECPTION' ) ;
463 END IF;
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
466 END IF;
467
468 END Reschedule_Ship_Set;
469
470
471 END OE_RSCH_SETS_CONC_REQUESTS;
[Home] [Help]