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