[Home] [Help]
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
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
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 */
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:
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
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
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
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:
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;
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
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:
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
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);
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
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;
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
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
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
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
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:
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
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
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:
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
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
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;
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:
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:
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
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
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;
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;
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);
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 ??
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: /*=============================================================================*/
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(
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;
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,
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;
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;
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',