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