1 PACKAGE BODY GME_RESERVE_CONC AS
2 /* $Header: GMECRSVB.pls 120.1 2008/01/09 16:14:31 srpuri noship $ */
3
4 -- Global constant holding the package name
5
6 G_DEBUG VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GME_RESERVE_CONC';
8 G_SET_ID NUMBER;
9 G_PROGRAM_APPLICATION_ID NUMBER;
10 G_PROGRAM_ID NUMBER;
11 G_RESERVATION_MODE VARCHAR2(30);
12 G_TOTAL_CONSUMED NUMBER :=0;
13 G_CONSUMED_FOR_LOT NUMBER :=0;
14 G_TOTAL_CONSUMED2 NUMBER :=0; -- INVCONV
15 G_CONSUMED_FOR_LOT2 NUMBER :=0; -- INVCONV
16
17
18 /*----------------------------------------------------------------
19 PROCEDURE : Reserve_Eligible
20 DESCRIPTION: This Procedure is to check if the Line that is being
21 considered needs Reservation
22 ----------------------------------------------------------------*/
23 -- this is a clone of Reserve_Eligible, but it does not look at existing reservations
24 Procedure Reserve_Eligible
25 ( p_line_rec IN OE_ORDER_PUB.line_rec_type,
26 p_use_reservation_time_fence IN VARCHAR2,
27 x_return_status OUT NOCOPY VARCHAR2
28 )
29 IS
30 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
31 l_result Varchar2(30);
32 l_scheduling_level_code VARCHAR2(30) := NULL;
33 l_out_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
34 l_type_code VARCHAR2(30);
35 l_org_id NUMBER;
36 l_time_fence BOOLEAN;
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(1000);
39 l_dummy VARCHAR2(100);
40
41 --
42 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
43 --
44
45 BEGIN
46 IF l_debug_level > 0 THEN
47 OE_DEBUG_PUB.Add('Inside Reserve Eligible Procedure',1);
48 END IF;
49
50 /* Check if line is open, if not open ignore the line */
51 IF ( p_line_rec.open_flag = 'N' ) THEN
52 IF l_debug_level > 0 THEN
53 OE_DEBUG_PUB.Add('Line is closed, not eligible for reservation', 1);
54 END IF;
55 l_return_status := FND_API.G_RET_STS_ERROR;
56
57 /* Check if line is shipped, if shipped then ignore the line */
58 ELSIF ( nvl(p_line_rec.shipped_quantity, -99) > 0 ) THEN
59 IF l_debug_level > 0 THEN
60 OE_DEBUG_PUB.Add('Line is shipped, not eligible for reservation', 1);
61 END IF;
62 l_return_status := FND_API.G_RET_STS_ERROR;
63 END IF;
64
65 IF l_return_status = FND_API.G_RET_STS_SUCCESS
66 AND NVL(g_reservation_mode,'*') <> 'PARTIAL'
67 AND 1=2 THEN -- Force this section to be skipped
68 /* We need to check for Existing Reservations on the Line */
69 BEGIN
70 IF l_debug_level > 0 THEN
71 OE_DEBUG_PUB.Add('Before checking Existing Reservations',1);
72 END IF;
73
74 SELECT 'Reservation Exists'
75 INTO l_dummy
76 FROM MTL_RESERVATIONS
77 WHERE DEMAND_SOURCE_LINE_ID = p_line_rec.line_id;
78
79 IF l_debug_level > 0 THEN
80 OE_DEBUG_PUB.Add('Reservations exists on the line',3);
81 END IF;
82
83 RAISE FND_API.G_EXC_ERROR;
84 EXCEPTION
85 WHEN FND_API.G_EXC_ERROR THEN
86 IF l_debug_level > 0 THEN
87 OE_DEBUG_PUB.Add('In Expected Error for Check Reservation',3);
88 END IF;
89 l_return_status := FND_API.G_RET_STS_ERROR;
90
91 WHEN NO_DATA_FOUND THEN
92 NULL;
93 WHEN TOO_MANY_ROWS THEN
94 l_return_status := FND_API.G_RET_STS_ERROR;
95 END;
96 END IF;
97 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
98 BEGIN
99 IF l_debug_level > 0 THEN
100 OE_DEBUG_PUB.Add('Before checking for Staged/Closed deliveries', 1);
101 END IF;
102
103 SELECT 'Staging Exists'
104 INTO l_dummy
105 FROM WSH_DELIVERY_DETAILS
106 WHERE SOURCE_LINE_ID = p_line_rec.line_id
107 AND SOURCE_CODE = 'OE'
108 AND RELEASED_STATUS IN ('Y', 'C');
109
110 IF l_debug_level > 0 THEN
111 OE_DEBUG_PUB.Add('Staged/Closed deliveries exist for the line', 3);
112 END IF;
113
114 RAISE FND_API.G_EXC_ERROR;
115
116 EXCEPTION
117 WHEN FND_API.G_EXC_ERROR THEN
118 IF l_debug_level > 0 THEN
119 OE_DEBUG_PUB.Add('In Expected Error for Checking Staged/Closed deliveries', 3);
120 END IF;
121 l_return_status := FND_API.G_RET_STS_ERROR;
122 WHEN NO_DATA_FOUND THEN
123 NULL;
124 WHEN TOO_MANY_ROWS THEN
125 l_return_status := FND_API.G_RET_STS_ERROR;
126 END;
127 END IF;
128 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
129
130 -- WE NEED TO CHECK FOR THE reservation_time_fence Value.
131 -- If the Value of the parameter passed to the concurrent
132 -- program is "NO' then we reserve the lines irrespective
133 -- of the profile option: OM : Reservation_Time_fence.
134 -- By default this parameter will have a value of YES.
135
136 IF (NVL(p_use_reservation_time_fence,'Y') = 'Y' or
137 NVL(p_use_reservation_time_fence,'Yes') = 'Yes') THEN
138 IF l_debug_level > 0 THEN
139 OE_DEBUG_PUB.Add('Schedule Ship Date:'||
140 p_line_rec.schedule_ship_date,3);
141 END IF;
142
143 IF NOT OE_SCHEDULE_UTIL.Within_Rsv_Time_Fence
144 (p_line_rec.schedule_ship_date, p_line_rec.org_id) THEN
145 IF l_debug_level > 0 THEN
146 OE_DEBUG_PUB.Add('The Schedule Date for Line falls
147 beyond reservation Time Fence',3);
148 END IF;
149 RAISE FND_API.G_EXC_ERROR ;
150
151 END IF;
152 END IF;
153
154
155 IF l_debug_level > 0 THEN
156 OE_DEBUG_PUB.Add('check scheduling level for header:'||p_line_rec.header_id ,1);
157 OE_DEBUG_PUB.Add('check scheduling level for line type:'||p_line_rec.line_type_id,1);
158 END IF;
159 l_scheduling_level_code := OE_SCHEDULE_UTIL.Get_Scheduling_Level
160 (p_line_rec.header_id
161 ,p_line_rec.line_type_id);
162 IF l_debug_level > 0 THEN
163 OE_DEBUG_PUB.Add('l_scheduling_level_code:'||l_scheduling_level_code,1);
164 END IF;
165
166 IF l_scheduling_level_code is not null AND
167 (l_scheduling_level_code = SCH_LEVEL_ONE
168 OR l_scheduling_level_code = SCH_LEVEL_TWO
169 OR l_scheduling_level_code = SCH_LEVEL_FIVE)
170 THEN
171 IF p_line_rec.schedule_action_code = OESCH_ACT_RESERVE OR
172 (p_line_rec.schedule_status_code is null AND
173 (p_line_rec.schedule_ship_date is NOT NULL OR
174 p_line_rec.schedule_arrival_date is NOT NULL))
175 THEN
176 IF l_debug_level > 0 THEN
177 OE_DEBUG_PUB.Add('Order Type Does not Allow Scheduling',3);
178 END IF;
179 RAISE FND_API.G_EXC_ERROR;
180 END IF;
181 END IF;
182
183 END IF; -- Check for Reservation Exists Clause
184 x_return_status := l_return_status;
185
186 IF l_debug_level > 0 THEN
187 OE_DEBUG_PUB.Add('..Exiting GME_RESERVE_CONC.Need_Reservation' ||
188 l_return_status ,1);
189 END IF;
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 IF l_debug_level > 0 THEN
193 OE_DEBUG_PUB.Add('In Expected Error...in Proc Reserve_Eligible',3);
194 END IF;
195 x_return_status := FND_API.G_RET_STS_ERROR;
196
197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
198 IF l_debug_level > 0 THEN
199 OE_DEBUG_PUB.Add('In UnExpected Error...in Proc Reserve_Eligible',3);
200 END IF;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202
203 WHEN OTHERS THEN
204
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206
207 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
208 THEN
209 OE_MSG_PUB.Add_Exc_Msg
210 ( G_PKG_NAME
211 , 'Action_Reserve'
212 );
213 END IF;
214
215 End Reserve_Eligible;
216
217
218 /*----------------------------------------------------------------
219 PROCEDURE : Create_Reservation
220 DESCRIPTION: This Procedure send the line to the Inventory for
221 Reservation
222 -----------------------------------------------------------------*/
223 Procedure Create_Reservation
224 (p_line_rec IN OE_ORDER_PUB.line_rec_type,
225 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
226 IS
227 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
228 l_reservation_rec Inv_Reservation_Global.Mtl_Reservation_Rec_Type;
229 l_msg_count NUMBER;
230 l_dummy_sn Inv_Reservation_Global.Serial_Number_Tbl_Type;
231 l_msg_data VARCHAR2(1000);
232 l_buffer VARCHAR2(1000);
233 l_quantity_reserved NUMBER;
234 l_quantity_to_reserve NUMBER;
235 l_rsv_id NUMBER;
236
237 l_quantity2_reserved NUMBER;
238 l_quantity2_to_reserve NUMBER;
239
240 --
241 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
242 --
243
244
245 BEGIN
246 IF l_debug_level > 0 THEN
247 OE_Debug_pub.Add('In the Procedure Create Reservation',1);
248 OE_Debug_pub.Add('Before call of Load_INV_Request',1);
249 END IF;
250
251
252 IF p_line_rec.ordered_quantity2 = 0 -- INVCONV
253 THEN
254 l_quantity2_to_reserve := NULL;
255 END IF;
256
257
258 OE_SCHEDULE_UTIL.Load_Inv_Request
259 ( p_line_rec => p_line_rec
260 , p_quantity_to_reserve => p_line_rec.ordered_quantity
261 , p_quantity2_to_reserve => l_quantity2_to_reserve -- INVCONV
262 , x_reservation_rec => l_reservation_rec);
263
264
265 -- Call INV with action = RESERVE
266 IF l_debug_level > 0 THEN
267 OE_DEBUG_PUB.Add('Before call of inv_reservation_pub.create_reservation',1);
268 END IF;
269
270 INV_RESERVATION_PUB.Create_Reservation
271 ( p_api_version_number => 1.0
272 , p_init_msg_lst => FND_API.G_TRUE
273 , x_return_status => l_return_status
274 , x_msg_count => l_msg_count
275 , x_msg_data => l_msg_data
276 , p_rsv_rec => l_reservation_rec
277 , p_serial_number => l_dummy_sn
278 , x_serial_number => l_dummy_sn
279 , p_partial_reservation_flag => FND_API.G_FALSE
280 , p_force_reservation_flag => FND_API.G_FALSE
281 , p_validation_flag => FND_API.G_TRUE
282 , x_quantity_reserved => l_quantity_reserved
283 , x_secondary_quantity_reserved => l_quantity2_reserved
284 , x_reservation_id => l_rsv_id
285 );
286 IF l_debug_level > 0 THEN
287 OE_DEBUG_PUB.Add('1. After Calling Create Reservation' ||
288 l_return_status,1);
289 OE_DEBUG_PUB.Add(l_msg_data,1);
290 END IF;
291
292 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
293 IF l_debug_level > 0 THEN
294 OE_DEBUG_PUB.Add('Raising Unexpected error',1);
295 END IF;
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297
298 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
299 IF l_debug_level > 0 THEN
300 OE_DEBUG_PUB.Add('Raising Expected error',1);
301 END IF;
302 IF l_msg_data is not null THEN
303 fnd_message.set_encoded(l_msg_data);
304 l_buffer := fnd_message.get;
305 OE_MSG_PUB.Add_text(p_message_text => l_buffer);
306 IF l_debug_level > 0 THEN
307 OE_DEBUG_PUB.Add(l_msg_data,1);
308 END IF;
309 END IF;
310 RAISE FND_API.G_EXC_ERROR;
311
312 END IF;
313 IF l_debug_level > 0 THEN
314 OE_DEBUG_PUB.Add('..Exiting GME_RESERVE_CONC.Create_reservation' ||
315 l_return_status ,1);
316 END IF;
317 x_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 IF l_debug_level > 0 THEN
322 OE_DEBUG_PUB.Add('In Expected Error...in Proc Create_Reservation',1);
323 END IF;
324 x_return_status := FND_API.G_RET_STS_ERROR;
325
326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
327 IF l_debug_level > 0 THEN
328 OE_DEBUG_PUB.Add('In Unexpected Error...in Proc Create_Reservation');
329 END IF;
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331
332 WHEN OTHERS THEN
333 IF l_debug_level > 0 THEN
334 OE_DEBUG_PUB.Add('In others error...in Proc Create_Reservation');
335 END IF;
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337
338 END;
339
340
341 /*----------------------------------------------------------------------
342 PROCEDURE : OPM_MTO
343 DESCRIPTION: Create and Reserve OPM Production Batch Concurrent Request
344 ----------------------------------------------------------------------*/
345
346 Procedure Make_to_Order
347 (ERRBUF OUT NOCOPY VARCHAR2,
348 RETCODE OUT NOCOPY VARCHAR2,
349 p_org_id IN NUMBER,
350 p_use_reservation_time_fence IN CHAR,
351 p_order_number_low IN NUMBER,
352 p_order_number_high IN NUMBER,
353 p_customer_id IN VARCHAR2,
354 p_order_type IN VARCHAR2,
355 p_line_type_id IN VARCHAR2,
356 p_warehouse IN VARCHAR2,
357 p_inventory_item_id IN VARCHAR2,
358 p_request_date_low IN VARCHAR2,
359 p_request_date_high IN VARCHAR2,
360 p_schedule_ship_date_low IN VARCHAR2,
361 p_schedule_ship_date_high IN VARCHAR2,
362 p_schedule_arrival_date_low IN VARCHAR2,
363 p_schedule_arrival_date_high IN VARCHAR2,
364 p_ordered_date_low IN VARCHAR2,
365 p_ordered_date_high IN VARCHAR2,
366 p_demand_class_code IN VARCHAR2,
367 p_planning_priority IN NUMBER,
368 p_booked IN VARCHAR2 DEFAULT NULL,
369 p_line_id IN NUMBER
370 )IS
371
372 l_api_name CONSTANT VARCHAR2 (30) := 'Make_to_Order';
373 l_stmt VARCHAR2(4000) :=NULL;
374 l_line_rec OE_ORDER_PUB.line_rec_type;
375 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
376 l_request_date_low DATE;
377 l_request_date_high DATE;
378 l_schedule_ship_date_low DATE;
379 l_schedule_ship_date_high DATE;
380 l_schedule_arrival_date_low DATE;
381 l_schedule_arrival_date_high DATE;
382 l_ordered_date_low DATE;
383 l_ordered_date_high DATE;
384 l_line_id NUMBER;
385 l_rsv_tbl Rsv_Tbl_Type;
386 l_temp_rsv_tbl Rsv_Tbl_Type;
387 l_cursor_id INTEGER;
388 l_retval INTEGER;
389 l_set_id NUMBER :=0;
390 l_process_flag VARCHAR2(1);
391 l_request_id NUMBER;
392 l_msg_data VARCHAR2(2000);
393 l_errbuf VARCHAR2(2000);
394 l_retcode VARCHAR2(2000);
395
396
397 --
398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
399 --
400 -- Moac
401 l_single_org BOOLEAN := FALSE;
402 l_old_org_id NUMBER := -99;
403 l_org_id NUMBER;
404 l_user_set_id NUMBER :=0;
405 l_created_by NUMBER;
406 BEGIN
407 ERRBUF := 'Create and Reserve OPM Production Batch Request completed successfully';
408 RETCODE := 0;
409
410 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
411
412 FND_FILE.Put_Line(FND_FILE.LOG, 'Parameters:');
413 FND_FILE.Put_Line(FND_FILE.LOG, ' ORG_ID = '||
414 p_org_id);
415 FND_FILE.Put_Line(FND_FILE.LOG, ' Use_reservation_time_fence = '||
416 p_use_reservation_time_fence);
417 FND_FILE.Put_Line(FND_FILE.LOG, ' order_number_low = '||
418 p_order_number_low);
419 FND_FILE.Put_Line(FND_FILE.LOG, ' order_number_high = '||
420 p_order_number_high);
421 FND_FILE.Put_Line(FND_FILE.LOG, ' Customer = '||
422 p_customer_id);
423 FND_FILE.Put_Line(FND_FILE.LOG, ' order_type = '||
424 p_order_type);
425 FND_FILE.Put_Line(FND_FILE.LOG, ' Warehouse = '||
426 p_Warehouse);
427 FND_FILE.Put_Line(FND_FILE.LOG, ' request_date_low = '||
428 p_request_date_low);
429 FND_FILE.Put_Line(FND_FILE.LOG, ' request_date_high = '||
430 p_request_date_high);
431 FND_FILE.Put_Line(FND_FILE.LOG, ' schedule_date_low = '||
432 p_schedule_ship_date_low);
433 FND_FILE.Put_Line(FND_FILE.LOG, ' schedule_date_high = '||
434 p_schedule_ship_date_high);
435 FND_FILE.Put_Line(FND_FILE.LOG, ' ordered_date_low = '||
436 p_ordered_date_low);
437 FND_FILE.Put_Line(FND_FILE.LOG, ' ordered_date_high = '||
438 p_ordered_date_high);
439 FND_FILE.Put_Line(FND_FILE.LOG, ' Demand Class = '||
440 p_demand_class_code);
441 FND_FILE.Put_Line(FND_FILE.LOG, ' item = '||
442 p_inventory_item_id);
443 FND_FILE.Put_Line(FND_FILE.LOG, ' Planning Priority = '||
444 p_Planning_priority);
445 FND_FILE.Put_Line(FND_FILE.LOG, ' Booked Flag = '||
446 p_booked);
447 FND_FILE.Put_Line(FND_FILE.LOG, ' Line ID = '||
448 p_line_id );
449
450 IF g_debug <= gme_debug.g_log_procedure THEN
451 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
452 END IF;
453
454 IF g_debug <= gme_debug.g_log_procedure THEN
455 gme_debug.put_line('setting dates ');
456 END IF;
457
458
459 SELECT FND_DATE.Canonical_To_Date(p_request_date_low),
460 FND_DATE.Canonical_To_Date(p_request_date_high),
461 FND_DATE.Canonical_To_Date(p_schedule_ship_date_low),
462 FND_DATE.Canonical_To_Date(p_schedule_ship_date_high),
463 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_low),
464 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_high),
465 FND_DATE.Canonical_To_Date(p_ordered_date_low),
466 FND_DATE.Canonical_To_Date(p_ordered_date_high)
467 INTO l_request_date_low,
468 l_request_date_high,
469 l_schedule_ship_date_low,
470 l_schedule_ship_date_high,
471 l_schedule_arrival_date_low,
472 l_schedule_arrival_date_high,
473 l_ordered_date_low,
474 l_ordered_date_high
475 FROM DUAL;
476
477 -- Moac Start
478 IF MO_GLOBAL.get_access_mode = 'S' THEN
479 l_single_org := TRUE;
480 ELSIF p_org_id IS NOT NULL THEN
481 l_single_org := TRUE;
482 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
483 END IF;
484 -- Moac End
485
486 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
487 -- Start constructing retrieval syntax
488 IF g_debug <= gme_debug.g_log_procedure THEN
489 gme_debug.put_line('Start building retrieval syntax');
490 END IF;
491 FND_FILE.Put_Line(FND_FILE.LOG, 'Starting syntax construction');
492 l_stmt := 'SELECT Line_id, l.org_id FROM OE_ORDER_LINES l, OE_ORDER_HEADERS_ALL h ,MTL_SYSTEM_ITEMS msi ';
493 l_stmt := l_stmt|| ' WHERE NVL(h.cancelled_flag,'||'''N'''||') <> ' ||'''Y'''||
494 ' AND h.header_id = l.header_id'||
495 ' AND h.open_flag = '||'''Y'''||
496 ' AND NVL(l.cancelled_flag,'||'''N'''||') <> '||'''Y'''||
497 ' AND NVL(l.line_category_code,'||'''ORDER'''||') <> '||'''RETURN''';
498 IF NVL(p_booked,'*') = 'Y' THEN
499 l_stmt := l_stmt||' AND h.booked_flag = '||'''Y''';
500 ELSIF NVL(p_booked,'*') = 'N' THEN
501 l_stmt := l_stmt||' AND h.booked_flag = '||'''N''';
502 END IF;
503
504 IF p_org_id IS NOT NULL THEN
505 l_stmt := l_stmt ||' AND l.org_id = :org_id'; -- p_org_id
506 END IF;
507
508 IF p_line_id IS NOT NULL THEN
509 l_stmt := l_stmt ||' AND l.line_id = :line_id'; -- p_line_id
510 END IF;
511
512 IF p_order_number_low IS NOT NULL THEN
513 l_stmt := l_stmt ||' AND h.order_number >=:order_number_low'; -- p_order_number_low
514 END IF;
515 IF p_order_number_high IS NOT NULL THEN
516 l_stmt := l_stmt ||' AND h.order_number <=:order_number_high'; -- p_order_number_high
517 END IF;
518 IF p_customer_id IS NOT NULL THEN
519 l_stmt := l_stmt ||' AND h.sold_to_org_id =:customer_id'; --p_customer_id
520 END IF;
521 IF p_order_type IS NOT NULL THEN
522 l_stmt := l_stmt ||' AND h.order_type_id =:order_type'; --p_order_type
523 END IF;
524 IF l_ordered_date_low IS NOT NULL THEN
525 FND_FILE.Put_Line(FND_FILE.LOG, 'GME Ordered date low here ');
526 l_stmt := l_stmt ||' AND h.ordered_date >=:ordered_date_low'; --l_ordered_date_low
527 END IF;
528 IF l_ordered_date_high IS NOT NULL THEN
529 l_stmt := l_stmt ||' AND h.ordered_date <=:ordered_date_high'; --l_ordered_date_high;
530 END IF;
531 IF p_line_type_id IS NOT NULL THEN
532 l_stmt := l_stmt ||' AND l.line_type_id =:line_type_id'; --p_line_type_id
533 END IF;
534 l_stmt := l_stmt ||' AND l.open_flag = '||'''Y''';
535 IF p_warehouse IS NOT NULL THEN
536 l_stmt := l_stmt ||' AND l.ship_from_org_id =:warehouse'; --p_warehouse
537 END IF;
538 IF l_request_date_low IS NOT NULL THEN
539 l_stmt := l_stmt ||' AND l.request_date >=:request_date_low'; --l_request_date_low;
540 END IF;
541 IF l_request_date_high IS NOT NULL THEN
542 l_stmt := l_stmt ||' AND l.request_date <=:request_date_high'; --l_request_date_high
543 END IF;
544 IF l_schedule_ship_date_low IS NOT NULL THEN
545 l_stmt := l_stmt ||' AND l.schedule_ship_date >=:schedule_ship_date_low'; --l_schedule_ship_date_low
546 END IF;
547 IF l_schedule_ship_date_high IS NOT NULL THEN
548 l_stmt := l_stmt ||' AND l.schedule_ship_date <=:schedule_ship_date_high'; --l_schedule_ship_date_high
549 END IF;
550 IF l_schedule_arrival_date_low IS NOT NULL THEN
551 l_stmt := l_stmt ||' AND l.Schedule_Arrival_Date >=:schedule_arrival_date_low'; --l_schedule_arrival_date_low
552 END IF;
553 IF l_schedule_arrival_date_high IS NOT NULL THEN
554 l_stmt := l_stmt ||' AND l.Schedule_Arrival_Date <=:schedule_arrival_date_high'; --l_schedule_arrival_date_high
555 END IF;
556 IF p_inventory_item_id IS NOT NULL THEN
557 l_stmt := l_stmt ||' AND l.inventory_item_id =:inventory_item_id'; -- p_inventory_item_id
558 END IF;
559 IF p_demand_class_code IS NOT NULL THEN
560 l_stmt := l_stmt ||' AND NVL(l.demand_class_code,'||'''-99'''||') =:demand_class_code'; --p_demand_class_code
561 END IF;
562 IF p_planning_priority IS NOT NULL THEN
563 l_stmt := l_stmt ||' AND NVL(l.planning_priority,-99)=:planning_priority'; --p_planning_priority
564 END IF;
565
566 /* Investigate partial Reservation */
567
568 l_stmt := l_stmt|| ' AND l.shipped_quantity IS NULL'||
569 ' AND l.source_type_code = '||'''INTERNAL'''||
570 ' AND NVL(l.shippable_flag,'||'''N'''||') = '||'''Y'''||
571 ' AND l.ship_from_org_id = msi.organization_id'||
572 ' AND l.inventory_item_id = msi.inventory_item_id'||
573 ' AND msi.service_item_flag <> '||'''Y'''||
574 ' AND msi.reservable_type <> 2';
575
576 IF g_debug <= gme_debug.g_log_procedure THEN
577 gme_debug.put_line('Main syntax built now add order by clause');
578 END IF;
579
580 l_stmt := l_stmt || ' ORDER BY l.inventory_item_id,l.ship_from_org_id,l.subinventory';
581 --OE_DEBUG_PUB.Add(substr(l_stmt,1,length(l_stmt)),1);
582 IF g_debug <= gme_debug.g_log_procedure THEN
583 gme_debug.put_line(substr(l_stmt,1,length(l_stmt)),1);
584 END IF;
585 DBMS_SQL.PARSE(l_cursor_id,l_stmt,DBMS_SQL.NATIVE);
586
587 FND_FILE.Put_Line(FND_FILE.LOG, 'GME parse done now');
588 IF g_debug <= gme_debug.g_log_procedure THEN
589 gme_debug.put_line('PARSE done ');
590 gme_debug.put_line('Start processing bind variables ');
591 END IF;
592 -- ================= BIND VARIABLES ======================
593 IF p_org_id IS NOT NULL THEN
594 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':org_id',p_org_id);
595 END IF;
596 IF p_line_id IS NOT NULL THEN
597 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':line_id',p_line_id);
598 END IF;
599 IF p_order_number_low IS NOT NULL THEN
600 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_low',p_order_number_low);
601 END IF;
602 IF p_order_number_high IS NOT NULL THEN
603 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_high',p_order_number_high);
604 END IF;
605 IF p_customer_id IS NOT NULL THEN
606 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':customer_id',p_customer_id);
607 END IF;
608 IF p_order_type IS NOT NULL THEN
609 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_type',p_order_type);
610 END IF;
611 IF l_ordered_date_low IS NOT NULL THEN
612 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':ordered_date_low',l_ordered_date_low);
613 END IF;
614 IF l_ordered_date_high IS NOT NULL THEN
615 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':ordered_date_high',l_ordered_date_high);
616 END IF;
617 IF p_line_type_id IS NOT NULL THEN
618 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':line_type_id',p_line_type_id);
619 END IF;
620 IF p_warehouse IS NOT NULL THEN
621 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':warehouse',p_warehouse);
622 END IF;
623 IF l_request_date_low IS NOT NULL THEN
624 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_date_low',l_request_date_low);
625 END IF;
626 IF l_request_date_high IS NOT NULL THEN
627 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_date_high',l_request_date_high);
628 END IF;
629 IF l_schedule_ship_date_low IS NOT NULL THEN
630 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_ship_date_low',l_schedule_ship_date_low);
631 END IF;
632 IF l_schedule_ship_date_high IS NOT NULL THEN
633 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_ship_date_high',l_schedule_ship_date_high);
634 END IF;
635 IF l_schedule_arrival_date_low IS NOT NULL THEN
636 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_arrival_date_low',l_schedule_arrival_date_low);
637 END IF;
638 IF l_schedule_arrival_date_high IS NOT NULL THEN
639 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':schedule_arrival_date_high',l_schedule_arrival_date_high);
640 END IF;
641 IF p_inventory_item_id IS NOT NULL THEN
642 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':inventory_item_id',p_inventory_item_id);
643 END IF;
644 IF p_demand_class_code IS NOT NULL THEN
645 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':demand_class_code',p_demand_class_code);
646 END IF;
647 IF p_planning_priority IS NOT NULL THEN
648 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':planning_priority',p_planning_priority);
649 END IF;
650 --R12.MOAC
651 IF g_debug <= gme_debug.g_log_procedure THEN
652 gme_debug.put_line('bind variables done');
653 gme_debug.put_line('start output variables ');
654 END IF;
655
656 -- ================= OUTPUT VARIABLES ======================
657 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_line_id);
658
659 -- ================= EXECUTE ==========================
660 IF g_debug <= gme_debug.g_log_procedure THEN
661 gme_debug.put_line(substr(l_stmt,1,length(l_stmt)),1);
662 END IF;
663 FND_FILE.Put_Line(FND_FILE.LOG, 'EXECUTE data retrieval ');
664
665 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
666
667 -- ================= PROCESS ORDER LINES ==================
668 IF g_debug <= gme_debug.g_log_procedure THEN
669 gme_debug.put_line('Start looping through rows here');
670 END IF;
671 LOOP
672 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
673 FND_FILE.Put_Line(FND_FILE.LOG, 'Zero order line rows to process so exit');
674 EXIT;
675 END IF;
676 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_line_id);
677
678 FND_FILE.Put_Line(FND_FILE.LOG, '***** Processing Line id '|| l_line_id||' *****');
679 l_return_status := FND_API.G_RET_STS_SUCCESS;
680 OE_LINE_UTIL.Lock_Row
681 (p_line_id => l_Line_id,
682 p_x_line_rec => l_line_rec,
683 x_return_status => l_return_status);
684
685 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
686 IF l_debug_level > 0 THEN
687 OE_DEBUG_PUB.Add('Lock row returned with error',1);
688 END IF;
689 END IF;
690
691 l_line_rec.schedule_action_code := OESCH_ACT_RESERVE; -- do we need to update action code ??
692 -- Make to Order Assessment
693 -- ========================
694 IF g_debug <= gme_debug.g_log_procedure THEN
695 gme_debug.put_line('Determine whether this line qualifies for MTO '||l_Line_id);
696 END IF;
697
698 IF GME_MAKE_TO_ORDER_PVT.line_qualifies_for_mto(l_line_rec.line_id) THEN
699 IF g_debug <= gme_debug.g_log_procedure THEN
700 gme_debug.put_line('Yes this line qualifies for Make to Order ');
701 gme_debug.put_line('Now determine if the line is eligible for reservation processing ');
702 END IF;
703 Reserve_Eligible(
704 p_line_rec => l_line_rec
705 ,p_use_reservation_time_fence => 'N'
706 ,x_return_status => l_return_status);
707
708 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
709 IF g_debug <= gme_debug.g_log_procedure THEN
710 gme_debug.put_line('This line is not eligible for creating reservations so cannot proceed with MTO');
711 END IF;
712 GOTO NEXT_RECORD;
713 END IF;
714
715 IF g_debug <= gme_debug.g_log_procedure THEN
716 gme_debug.put_line('Lock Row for line_id '||l_Line_id);
717 END IF;
718 OE_LINE_UTIL.Lock_Row
719 (p_line_id => l_Line_id,
720 p_x_line_rec => l_line_rec,
721 x_return_status => l_return_status);
722
723 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
724 IF g_debug <= gme_debug.g_log_procedure THEN
725 gme_debug.put_line('Failure to Lock Row for line_id '||l_Line_id);
726 END IF;
727 GOTO NEXT_RECORD;
728 END IF;
729
730 /* Need to create an OPM batch */
731 /* Need to reserve the order line to the new production batch */
732 /* ========================================================== */
733 IF g_debug <= gme_debug.g_log_procedure THEN
734 gme_debug.put_line('Proceeding with Make to Order so invoke create batch for order line here');
735 END IF;
736
737 GME_MAKE_TO_ORDER_PVT.create_batch_for_order_line(
738 p_api_version => 1.0
739 -- ,p_init_msg_lst => FND_API.G_TRUE
740 -- ,p_commit => FND_API.G_TRUE
741 ,p_so_line_id => l_line_id);
742
743 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN -- change create_batch above to return return_status
744 IF g_debug <= gme_debug.g_log_procedure THEN
745 gme_debug.put_line('Failure during create batch for order line : line is '||l_line_rec.line_id);
746 GOTO NEXT_RECORD;
747 END IF;
748 END IF;
749 ELSE
750 FND_FILE.Put_Line(FND_FILE.LOG, '***** order line DOES NOT qualify for MAKE to ORDER '|| l_line_id||' *****');
751 END IF; -- End of Line Qualifies for MAKE to ORDER
752
753 <<NEXT_RECORD>>
754 NULL;
755 END LOOP; -- End of lines_cur
756 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
757
758 FND_FILE.Put_Line(FND_FILE.LOG, 'End of processing for OPM Make to Order');
759 COMMIT;
760
761 <<END_OF_PROCESS>>
762 IF g_debug <= gme_debug.g_log_procedure THEN
763 gme_debug.put_line('End of Processing for MAKE to ORDER');
764 END IF;
765
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF l_debug_level > 0 THEN
770 OE_DEBUG_PUB.Add('Inside the When Others Execption',1);
771 OE_DEBUG_PUB.Add(substr(sqlerrm, 1, 2000));
772 END IF;
773 END Make_to_order;
774 /*=============================================================================*/
775 PROCEDURE set_parameter_for_wf(
776 p_itemtype in VARCHAR2, /* workflow item type */
777 p_itemkey in VARCHAR2, /* sales order line id */
778 p_actid in number, /* ID number of WF activity */
779 p_funcmode in VARCHAR2, /* execution mode of WF activity */
780 x_result out NoCopy VARCHAR2 /* result of activity */
781 )
782 IS
783 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
784 l_stmt_num number := 0;
785 l_quantity number := 0;
786 l_class_code number;
787 l_wip_group_id number;
788 l_mfg_org_id number;
789 l_afas_line_id number;
790 l_msg_name varchar2(30);
791 l_msg_txt varchar2(500);
792 l_return_status varchar2(1);
793 l_user_id varchar2(30);
794 l_msg_count number;
795 l_hold_result_out varchar2(1);
796 l_hold_return_status varchar2(1);
797 l_ato_line_id number;
798 l_line_id number;
799 record_locked exception;
800 pragma exception_init (record_locked, -54);
801
802 l_result varchar2(20) := null;
803
804 l_build_in_wip varchar2(1); --bugfix 2318060
805
806 BEGIN
807 savepoint before_process;
808 x_result := 'FAILURE' ;
809 IF l_debug_level <> 0 THEN
810 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'Function Mode: ' || p_funcmode, 1);
811 END IF;
812 OE_STANDARD_WF.Set_Msg_Context(p_actid);
813 if (p_funcmode = 'RUN') then
814 wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey,
815 'AFAS_LINE_ID', p_itemkey);
816 x_result := 'SUCCESS' ;
817 end if; /* p_funcmode = 'RUN' */
818 OE_STANDARD_WF.Save_Messages;
819 OE_STANDARD_WF.Clear_Msg_Context;
820
821
822 EXCEPTION
823 when FND_API.G_EXC_ERROR then
824 IF l_debug_level <> 0 THEN
825 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised exc error. ' ||
826 to_char(l_stmt_num) );
827 END IF;
828 OE_STANDARD_WF.Save_Messages;
829 OE_STANDARD_WF.Clear_Msg_Context;
830 x_result := 'COMPLETE:INCOMPLETE';
831 rollback to savepoint before_process;
832 return;
833
834
835 when FND_API.G_EXC_UNEXPECTED_ERROR then
836 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
837 IF l_debug_level <> 0 THEN
838 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised unexc error. ' ||
839 to_char(l_stmt_num) );
840 END IF;
841 OE_STANDARD_WF.Save_Messages;
842 OE_STANDARD_WF.Clear_Msg_Context;
843 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
844 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
845 raise;
846
847
848 when NO_DATA_FOUND then
849 OE_STANDARD_WF.Save_Messages;
850 OE_STANDARD_WF.Clear_Msg_Context;
851 -- Set the result to INCOMPLETE so that the wf returns to Create Supply Order Eligible
852 x_result := 'COMPLETE:INCOMPLETE';
853 return;
854
855 when OTHERS then
856
857 IF l_debug_level <> 0 THEN
858 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_for_wf: '
859 || to_char(l_stmt_num) || ':' ||
860 substrb(sqlerrm, 1, 100));
861 END IF;
862 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
863 p_itemtype, p_itemkey, to_char(p_actid),
864 p_funcmode);
865
866 raise;
867
868 END set_parameter_for_wf;
869
870
871 END GME_RESERVE_CONC;
[Home] [Help]