[Home] [Help]
PACKAGE BODY: APPS.OE_INTERNAL_REQUISITION_PVT
Source
1 PACKAGE BODY OE_INTERNAL_REQUISITION_PVT AS
2 /* $Header: OEXVIRQB.pls 120.5.12020000.2 2012/07/03 10:24:38 amallik ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_Name CONSTANT VARCHAR2(30) := 'OE_INTERNAL_REQUISITION_PVT';
6
7 -- Added for 8583903
8 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10 Procedure Get_Eligible_ISO_Shipment -- Body definition
11 ( P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
12 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
13 , X_line_ids_rec OUT NOCOPY Line_Id_Rec_Type
14 , X_return_status OUT NOCOPY VARCHAR2
15 ) IS
16 --
17 l_line_ids_rec Line_Id_Rec_Type;
18 --
19 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
20 --
21
22 Begin
23
24 IF l_debug_level > 0 THEN
25 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Get_Eligible_ISO_Shipment', 1 ) ;
26 oe_debug_pub.add( ' Requisition Line id :'||P_internal_req_line_id , 5 ) ;
27 oe_debug_pub.add( ' Requisition Header id :'||P_internal_req_header_id , 5) ;
28 END IF;
29
30 X_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 select l.line_id
33 , l.line_number
34 , l.shipment_number
35 , l.header_id
36 , l.ordered_quantity
37 , l.ordered_quantity2
38 , l.request_date
39 into l_line_ids_rec.line_id
40 , l_line_ids_rec.line_number
41 , l_line_ids_rec.shipment_number
42 , l_line_ids_rec.header_id
43 , l_line_ids_rec.ordered_quantity
44 , l_line_ids_rec.ordered_quantity2
45 , l_line_ids_rec.request_date
46 from oe_order_headers_all h
47 , oe_order_lines_all l
48 -- OE_Order_Header_All table is used in this query to use
49 -- the OE_Order_Headers_N7 index for performance reasons
50 where h.header_id = l.header_id
51 and h.source_document_id = p_internal_req_header_id
52 and l.source_document_line_id = p_internal_req_line_id
53 and h.source_document_type_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
54 and h.open_flag = 'Y'
55 and l.open_flag = 'Y'
56 and nvl(l.cancelled_flag,'N') = 'N'
57 and nvl(l.fulfilled_flag,'N') = 'N'
58 and nvl(l.shipped_quantity,0) = 0
59 and nvl(l.fulfilled_quantity,0) = 0
60 and l.actual_shipment_date is null
61 and not exists (select 1 from wsh_delivery_details w
62 where w.source_line_id = l.line_id
63 and w.source_header_id = h.header_id
64 and w.source_code = 'OE'
65 and w.released_status = 'C')
66 order by l.shipment_number;
67 -- If delivery detail is pick release: Not verifying!, because
68 -- OM Pick Released constraint is not a seeded constraint
69
70 X_line_ids_rec := l_line_ids_rec;
71
72 IF l_debug_level > 0 THEN
73 oe_debug_pub.add( ' EXITING OE_Internal_Requisition_Pvt.Get_Eligible_ISO_Shipment', 1 ) ;
74 END IF;
75
76 Exception
77
78 WHEN NO_DATA_FOUND THEN
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( ' EXITING Get_Eligible_ISO_Shipment With No Data Found Error', 1 ) ;
81 END IF;
82 FND_Message.Set_Name('ONT', 'OE_IRCMS_NOT_ELIGIBLE');
83 -- There is no sales order line shipment eligible for update/cancellation.
84 OE_MSG_PUB.Add;
85 X_return_status := FND_API.G_RET_STS_ERROR;
86
87 WHEN TOO_MANY_ROWS THEN
88 IF l_debug_level > 0 THEN
89 oe_debug_pub.add( ' EXITING Get_Eligible_ISO_Shipment with Too Many Rows error', 1 ) ;
90 END IF;
91 FND_Message.Set_Name('ONT', 'OE_IRCMS_TOO_MANY_ROWS');
92 -- There are multiple sales order line shipments eligible for update.
93 -- This is not allowed.
94 OE_MSG_PUB.Add;
95 X_return_status := FND_API.G_RET_STS_ERROR;
96
97 WHEN OTHERS THEN
98 IF l_debug_level > 0 THEN
99 oe_debug_pub.add( ' EXITING Get_Eligible_ISO_Shipment with others error'||sqlerrm,1);
100 END IF;
101 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Get_Eligible_ISO_Shipment');
103 -- Pkg Body global variable = 'OE_internal_Requisition_Pv'
104 END IF;
105 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
106 End Get_Eligible_ISO_Shipment;
107
108
109 Function Update_Allowed -- Body definition
110 ( P_line_id IN NUMBER
111 , P_Attribute IN VARCHAR2
112 ) RETURN BOOLEAN
113 IS
114 --
115 l_line_rec OE_Order_Pub.Line_Rec_Type;
116 l_return_status VARCHAR2(1);
117 l_line_rowtype_rec OE_AK_ORDER_LINES_V%ROWTYPE;
118 l_attr_update_allowed BOOLEAN := FALSE;
119 l_entity_update_allowed BOOLEAN := FALSE;
120 l_action NUMBER;
121 l_result NUMBER := OE_PC_GLOBALS.NO;
122 --
123 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
124 --
125 Begin
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Update_Allowed', 1) ;
128 END IF;
129
130 OE_LINE_UTIL.QUERY_ROW( p_line_id => P_line_id
131 , x_line_rec => l_line_rec );
132
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add( ' Converting to Rowtype record', 5);
135 END IF;
136
137 OE_LINE_Util_Ext.API_Rec_To_Rowtype_Rec( p_LINE_rec => l_line_rec
138 , x_rowtype_rec => l_line_rowtype_rec);
139
140 --Initialize security global record
141 OE_LINE_SECURITY.g_record := l_line_rowtype_rec;
142
143 IF P_Attribute IS NULL OR P_Attribute in ('REQUEST_DATE','ALL') THEN
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( ' Checking if update of Request Date is allowed', 5);
146 END IF;
147 l_result := OE_Line_Security.Request_Date -- Is_OP_Constrained
148 ( p_operation => OE_PC_GLOBALS.UPDATE_OP
149 -- , p_column_name => 'REQUEST_DATE'
150 , p_record => l_line_rowtype_rec
151 , x_on_operation_action => l_action );
152
153 IF l_result = OE_PC_GLOBALS.NO THEN
154 IF l_debug_level > 0 THEN
155 oe_debug_pub.add( ' Update of Request Date is allowed. Action'||l_action,1);
156 END IF;
157 l_attr_update_allowed := TRUE;
158 ELSE
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add( ' Update of Request Date is not allowed.',1);
161 oe_debug_pub.add( ' Action / Result : '||l_action||' / '||l_result,1);
162 END IF;
163 l_attr_Update_Allowed := FALSE;
164 END IF; -- l_result
165 END IF; -- P_Attribute
166
167
168 IF (NOT l_attr_update_allowed AND P_Attribute IS NULL)
169 OR P_Attribute in ('ORDERED_QUANTITY', 'ALL') THEN
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add( ' Checking if update of Ordered Quantity is allowed',5);
172 END IF;
173
174 l_result := OE_Line_Security.Ordered_Quantity --Is_OP_Constrained
175 ( p_operation => OE_PC_GLOBALS.UPDATE_OP
176 -- , p_column_name => 'ORDERED_QUANTITY'
177 , p_record => l_line_rowtype_rec
178 , x_on_operation_action => l_action );
179
180 IF l_result = OE_PC_GLOBALS.NO THEN
181 IF l_debug_level > 0 THEN
182 oe_debug_pub.add( 'Update of Ordered Quantity is allowed. Action'||l_action,1);
183 END IF;
184 l_attr_Update_Allowed := TRUE;
185 ELSE
186 IF l_debug_level > 0 THEN
187 oe_debug_pub.add( ' Update of Ordered Quantity is not allowed.',1);
188 oe_debug_pub.add( ' Action / Result : '||l_action||' / '||l_result,1);
189 END IF;
190 l_attr_Update_Allowed := FALSE;
191 END IF; -- l_result
192 END IF; -- P_Attribute
193
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( ' Checking if Update operation is allowed for a record',5);
196 END IF;
197 IF ( NOT l_entity_update_allowed ) AND ( l_attr_update_allowed ) THEN
198
199 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
200
201 OE_Line_Security.Entity -- Is_OP_Constrained
202 ( p_LINE_rec => l_line_rec
203 , x_result => l_result
204 , x_return_status => l_return_status );
205
206 IF l_result = OE_PC_GLOBALS.NO THEN
207 IF l_debug_level > 0 THEN
208 oe_debug_pub.add( 'Update is allowed for Entity. Action'||l_action,1);
209 END IF;
210 l_entity_Update_Allowed := TRUE;
211 ELSE
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( ' Entity Update is not allowed.',1);
214 oe_debug_pub.add( ' Action / Result : '||l_action||' / '||l_result,1);
215 END IF;
216 l_entity_Update_Allowed := FALSE;
217 END IF; -- l_result
218 END IF; -- l_entity_update_allowed
219
220 IF l_entity_update_allowed AND l_attr_update_allowed THEN
221 IF l_debug_level > 0 THEN
222 oe_debug_pub.add( ' Order Line is allowed to UPDATE.',1);
223 END IF;
224 RETURN TRUE;
225 ELSE
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add( ' Order Line is NOT allowed to UPDATE.',1);
228 END IF;
229 -- Resetting the Boolean for the next iteration of the loop
230 l_entity_update_allowed := FALSE;
231 l_attr_update_allowed := FALSE;
232 RETURN FALSE;
233 END IF;
234
235 IF l_debug_level > 0 THEN
236 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Update_Allowed', 1 ) ;
237 END IF;
238 Exception
239 WHEN OTHERS THEN
240 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Update_Allowed '||sqlerrm,1);
241 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
242 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Allowed');
243 -- Pkg Body global variable = 'OE_internal_Requisition_Pv'
244 END IF;
245 RETURN FALSE;
246 End Update_Allowed;
247
248
249 Function Cancel_Allowed -- Body definition
250 ( P_line_id IN NUMBER
251 ) RETURN BOOLEAN
252 IS
253 --
254 l_line_rec OE_Order_Pub.Line_Rec_Type;
255 l_result NUMBER := OE_PC_GLOBALS.NO;
256 l_rowtype_rec OE_AK_ORDER_LINES_V%ROWTYPE;
257 l_return_status VARCHAR2(1);
258 l_action NUMBER;
259 l_constraint_id NUMBER;
260 l_grp NUMBER;
261 l_on_operation_action NUMBER;
262 l_column_name VARCHAR2(120);
263 --
264 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
265 --
266 Begin
267 IF l_debug_level > 0 THEN
268 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Cancel_Allowed', 1) ;
269 END IF;
270
271 OE_LINE_UTIL.QUERY_ROW( p_line_id => P_line_id
272 , x_line_rec => l_line_rec );
273
274 IF l_debug_level > 0 THEN
275 oe_debug_pub.add( ' Calling OE_Line_SEcurity.Entity',5);
276 END IF;
277
278 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
279 l_line_rec.ordered_quantity := 0;
280 IF l_line_rec.ordered_quantity2 IS NOT NULL AND l_line_rec.ordered_quantity2 <> 0 THEN
281 l_line_rec.ordered_quantity2 := 0;
282 END IF;
283
284 OE_Line_Security.Entity -- Is_OP_Constrained
285 ( p_LINE_rec => l_line_rec
286 , x_result => l_result
287 , x_return_status => l_return_status );
288
289 IF l_result = OE_PC_GLOBALS.NO THEN
290 -- Vaibhav
291 OE_LINE_Util_Ext.API_Rec_To_Rowtype_Rec
292 ( p_LINE_rec => l_line_rec
293 , x_rowtype_rec => l_rowtype_rec);
294
295 --Initialize security global record
296 OE_LINE_SECURITY.g_record := l_rowtype_rec;
297
298 -- Modified the code for bug 7675256
299 l_result := OE_Line_Security.Ordered_Quantity --Is_OP_Constrained
300 ( p_operation => OE_PC_GLOBALS.UPDATE_OP
301 -- , p_column_name => 'ORDERED_QUANTITY'
302 , p_record => l_rowtype_rec
303 , x_on_operation_action => l_action );
304
305 /* -- Commented for bug 7675256
306 l_column_name := NULL;
307
308 l_result := OE_PC_Constraints_Admin_PVT.Is_OP_constrained
309 ( p_responsibility_id => nvl(fnd_global.resp_id, -1)
310 , p_application_id => nvl(fnd_global.resp_appl_id,-1)
311 , p_operation => OE_PC_Globals.CANCEL_OP
312 , p_qualifier_attribute => l_rowtype_rec.transaction_phase_code
313 , p_entity_id => OE_PC_GLOBALS.G_ENTITY_LINE
314 , p_column_name => l_column_name
315 , p_check_all_cols_constraint => 'N' -- g_check_all_cols_constraint ???
316 , p_is_caller_defaulting => 'N' -- g_is_caller_defaulting ???
317 , p_use_cached_results => 'Y' -- ???
318 , x_constraint_id => l_constraint_id
319 , x_constraining_conditions_grp => l_grp
320 , x_on_operation_action => l_on_operation_action
321 );
322 */
323
324 IF l_result = OE_PC_GLOBALS.NO THEN
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add( 'Cancel is allowed for this shipment.',1);
327 END IF;
328 RETURN TRUE;
329 ELSE
330 IF l_debug_level > 0 THEN
331 oe_debug_pub.add( 'Cancel is Not allowed for this shipment',1);
332 END IF;
333 RETURN FALSE;
334 END IF;
335 ELSE
336 IF l_debug_level > 0 THEN
337 oe_debug_pub.add( 'Cancel is not allowed for this shipment.',1);
338 END IF;
339 RETURN FALSE;
340 END IF;
341 IF l_debug_level > 0 THEN
342 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Cancel_Allowed', 1 ) ;
343 END IF;
344
345 Exception
346 WHEN OTHERS THEN
347 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Cancel_Allowed '||sqlerrm,1);
348 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
349 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Cancel_Allowed');
350 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
351 END IF;
352 RETURN FALSE;
353 End Cancel_Allowed;
354
355
356 Function Cancel_Header_Allowed -- Body definition
357 ( P_header_id IN NUMBER
358 ) RETURN BOOLEAN
359 IS
360 --
361 l_header_rec OE_Order_Pub.Header_Rec_Type;
362 l_header_rowtype_rec OE_AK_ORDER_HEADERS_V%ROWTYPE;
363 l_action NUMBER;
364 l_result NUMBER := OE_PC_GLOBALS.NO;
365 --
366 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
367 --
368 Begin
369 IF l_debug_level > 0 THEN
370 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Cancel_Header_Allowed', 1 ) ;
371 END IF;
372
373 OE_HEADER_UTIL.QUERY_ROW( p_header_id => P_header_id
374 , x_header_rec => l_header_rec );
375
376 IF l_debug_level > 0 THEN
377 oe_debug_pub.add( ' Converting to Rowtype record',5);
378 END IF;
379
380 OE_HEADER_Util.API_Rec_To_Rowtype_Rec( p_header_rec => l_header_rec
381 , x_rowtype_rec => l_header_rowtype_rec);
382
383 -- Initialize security global record
384 OE_Header_SECURITY.g_record := l_header_rowtype_rec;
385
386 l_result := OE_Header_Security.Is_OP_Constrained
387 ( p_operation => OE_PC_GLOBALS.CANCEL_OP
388 , p_record => l_header_rowtype_rec
389 , x_on_operation_action => l_action );
390
391 IF l_result = OE_PC_GLOBALS.NO THEN
392 IF l_debug_level > 0 THEN
393 oe_debug_pub.add( 'Cancel is allowed for this Order. Action'||l_action,1);
394 END IF;
395 RETURN TRUE;
396 ELSE
397 IF l_debug_level > 0 THEN
398 oe_debug_pub.add( 'Cancel is not allowed for this Order. Action'||l_action,1);
399 END IF;
400 RETURN FALSE;
401 END IF;
402 IF l_debug_level > 0 THEN
403 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Cancel_Header_Allowed', 1 ) ;
404 END IF;
405
406 Exception
407 WHEN OTHERS THEN
408 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Cancel_Header_Allowed '||sqlerrm,1);
409 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
410 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Cancel_Header_Allowed');
411 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
412 END IF;
413 RETURN FALSE;
414 End Cancel_Header_Allowed;
415
416
417 PROCEDURE Process_Line_Entity -- Body definition
418 (p_line_Tbl IN OE_Order_PUB.Line_Tbl_Type
419 ,P_mode IN VARCHAR2
420 ,P_Cancel IN BOOLEAN
421 ,x_return_status OUT NOCOPY VARCHAR2
422 )
423 IS
424
425 l_x_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
426 l_x_old_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
427 l_control_rec OE_GLOBALS.Control_Rec_Type;
428 l_return_status VARCHAR2(1);
429 l_header_id NUMBER;
430 l_msg_count NUMBER;
431 l_msg_data VARCHAR2(2000);
432 --
433 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
434 --
435 BEGIN
436
437 IF l_debug_level > 0 THEN
438 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Process_Line_Entity', 1 ) ;
439 oe_debug_pub.add( ' Count of Lines :'||P_Line_Tbl.COUNT, 5 ) ;
440 oe_debug_pub.add( ' Mode :'||P_Mode, 5 ) ;
441 END IF;
442
443 l_x_line_tbl := p_line_tbl;
444 l_header_id := l_x_line_tbl(1).header_id;
445
446 IF p_mode = 'V' THEN
447 l_control_rec.controlled_operation := TRUE;
448 l_control_rec.write_to_db := FALSE;
449 l_control_rec.process := FALSE;
450 END IF;
451 IF p_cancel THEN
452 l_control_rec.controlled_operation := TRUE;
453 l_control_rec.default_attributes := FALSE;
454 END IF;
455
456
457 OE_ORDER_PVT.Lines
458 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
459 , p_control_rec => l_control_rec
460 , p_x_line_tbl => l_x_line_tbl
461 , p_x_old_line_tbl => l_x_old_line_tbl
462 , x_return_status => l_return_status);
463
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add( ' After OE_Order_Pvt.Lines: '||l_return_status);
466 END IF;
467
468 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
470 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473
474 IF p_mode = 'P' THEN -- Mode is PROCESS
475
476 OE_Order_PVT.Process_Requests_And_Notify
477 ( p_process_requests => TRUE
478 -- , p_notify => FALSE -- Not Needed
479 , p_line_tbl => l_x_line_tbl
480 , p_old_line_tbl => l_x_old_line_tbl
481 , x_return_status => l_return_status );
482
483 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
484 IF l_debug_level > 0 THEN
485 oe_debug_pub.add( ' Process_requests_and_notify UNEXP_ERROR',1 ) ;
486 END IF;
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
489 IF l_debug_level > 0 THEN
490 oe_debug_pub.add( 'Process_requests_and_notify RET_STS_ERROR',1 ) ;
491 END IF;
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494 IF P_Cancel THEN
495 OE_SALES_CAN_UTIL.Call_Process_Fulfillment(p_header_id => l_header_id);
496 END IF;
497 END IF; -- P_Mode = Process
498
499 x_return_status := l_return_status;
500
501 IF l_debug_level > 0 THEN
502 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Process_Line_Entity', 1 ) ;
503 END IF;
504
505 EXCEPTION
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
508 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
509 OE_MSG_PUB.Add_Exc_Msg
510 ( G_PKG_NAME
511 , 'Process_Line_Entity' );
512 END IF;
513
514 WHEN FND_API.G_EXC_ERROR THEN
515 x_return_status := FND_API.G_RET_STS_ERROR;
516
517 WHEN OTHERS THEN
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
519 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
520 OE_MSG_PUB.Add_Exc_Msg
521 ( G_PKG_NAME
522 , 'Process_Line_Entity' );
523 END IF;
524 IF l_debug_level > 0 THEN
525 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
526 END IF;
527 END Process_Line_Entity;
528
529
530 Procedure Apply_Hold_for_IReq -- Body definition
531 ( P_API_Version IN NUMBER
532 , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
533 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
534 , X_msg_count OUT NOCOPY NUMBER
535 , X_msg_data OUT NOCOPY VARCHAR2
536 , X_return_status OUT NOCOPY VARCHAR2
537 ) IS
538
539 --
540 l_API_name Constant Varchar2(30) := 'APPLY_HOLD_FOR_IREQ';
541 l_API_version Constant Number := 1.0;
542
543 l_req_hdr_id PO_Requisition_Headers_All.Requisition_Header_id%TYPE;
544 l_line_ids_rec Line_Id_rec_Type;
545 l_return_status VARCHAR2(1);
546 l_msg_count NUMBER;
547 l_msg_data VARCHAR2(2000);
548 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
549 --
550 -- Modified for 8583903
551 l_file_val VARCHAR2(2000);
552 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
553 --
554
555 Begin
556
557 -- Added IF condition for 8583903
558 IF (g_fnd_debug = 'Y') THEN
559
560 oe_debug_pub.debug_on;
561 oe_debug_pub.initialize;
562 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); -- Dir/File
563 oe_Debug_pub.setdebuglevel(5);
564
565 l_debug_level := oe_debug_pub.g_debug_level;
566
567 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
568 FND_LOG.string( log_level => FND_LOG.LEVEL_STATEMENT
569 , module => 'po.plsql.'||G_PKG_Name||'.'||l_API_name
570 , message => '*** The Order Mangement Debug Log Dir/File is '||l_file_val);
571 END IF;
572 END IF;
573
574 IF Not FND_API.Compatible_API_Call
575 ( l_API_version
576 , p_API_version
577 , l_API_name
578 , G_PKG_Name) THEN
579 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582 SavePoint Apply_Hold_For_IReq;
583
584 IF l_debug_level > 0 THEN
585 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Apply_Hold_for_IReq', 1 ) ;
586 oe_debug_pub.add( ' Requisition Line id :'||P_internal_req_line_id , 5 ) ;
587 oe_debug_pub.add( ' Requisition Header id :'||P_internal_req_header_id , 5) ;
588 END IF;
589
590 -- OE_MSG_PUB.set_msg_context();
591 -- No need to set the message context as the caller of this API is PO
592 -- and Message window is not applicable in Requesting organization
593
594 l_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 IF P_internal_req_line_id is NULL THEN
597 IF l_debug_level > 0 THEN
598 oe_debug_pub.add( ' Invalid value passed for Requisition Line',1);
599 END IF;
600 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
601 -- FND_Message.Set_Token('REQ_LIN_ID',' P_internal_req_line_id');
602 OE_MSG_PUB.Add;
603 RAISE FND_API.G_EXC_ERROR;
604 END IF;
605 IF P_internal_req_header_id is NULL THEN
606 IF l_debug_level > 0 THEN
607 oe_debug_pub.add( ' No value passed for Requisition Header',1);
608 END IF;
609 Begin
610 oe_debug_pub.add( ' Retrieving Requisition Header id',5);
611 select requisition_header_id
612 into l_req_hdr_id
613 from po_requisition_lines_all
614 where requisition_line_id = P_internal_req_line_id;
615
616 IF l_req_hdr_id IS NULL THEN
617 oe_debug_pub.add( ' Invalid value for Requisition Header',5);
618 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_HDR');
619 -- FND_Message.Set_Token('REQ_HDR_ID',P_internal_req_header_id);
620 OE_MSG_PUB.Add;
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 Exception
624 When No_Data_Found Then
625 oe_debug_pub.add( ' Invalid value passed for Requisition Line',5);
626 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
627 -- FND_Message.Set_Token('REQ_LIN_ID',P_internal_req_line_id);
628 OE_MSG_PUB.Add;
629 RAISE FND_API.G_EXC_ERROR;
630 End;
631 ELSE
632 l_req_hdr_id := P_internal_req_header_id;
633 END IF;
634
635 Get_Eligible_ISO_Shipment
636 ( P_internal_req_line_id => P_internal_req_line_id
637 , P_internal_req_header_id => P_internal_req_header_id
638 , X_line_ids_rec => l_line_ids_rec
639 , X_return_status => l_return_status );
640
641 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
642 RAISE FND_API.G_EXC_ERROR;
643 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645 END IF;
646
647 IF l_debug_level > 0 THEN
648 oe_debug_pub.add( ' Applying hold for line_id '||l_line_ids_rec.line_id,5);
649 END IF;
650
651 l_hold_source_rec.hold_id := 17;
652 -- Ensure that the new seeded hold_id should be 17 in seed database
653 l_hold_source_rec.hold_entity_code := 'O';
654 l_hold_source_rec.hold_entity_id := l_line_ids_rec.header_id;
655 l_hold_source_rec.line_id := l_line_ids_rec.line_id; -- Line level hold
656 l_hold_source_rec.header_id := l_line_ids_rec.header_id;
657
658 OE_GLOBALS.G_SYS_HOLD := TRUE; -- bug 9494397
659 OE_Holds_Pvt.apply_holds
660 ( p_hold_source_rec => l_hold_source_rec
661 , x_return_status => l_return_status
662 , x_msg_count => l_msg_count
663 , x_msg_data => l_msg_data );
664 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
665
666 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
667 RAISE FND_API.G_EXC_ERROR;
668 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
670 END IF;
671
672 x_return_status := l_return_status;
673
674 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
675 P_Count => x_msg_Count,
676 P_Data => x_msg_Data);
677
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Apply_Hold_for_IReq', 1 ) ;
680 END IF;
681
682 -- Added for 8583903
683 oe_debug_pub.debug_off;
684 oe_Debug_pub.setdebuglevel(0);
685
686 Exception
687 WHEN FND_API.G_EXC_ERROR THEN
688 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
691 P_Count => x_msg_Count,
692 P_Data => x_msg_Data);
693 ROLLBACK TO Apply_Hold_For_IReq;
694
695 -- Added for 8583903
696 oe_debug_pub.debug_off;
697 oe_Debug_pub.setdebuglevel(0);
698 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
699
700 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
704 P_Count => x_msg_Count,
705 P_Data => x_msg_Data);
706 ROLLBACK TO Apply_Hold_For_IReq;
707
708 -- Added for 8583903
709 oe_debug_pub.debug_off;
710 oe_Debug_pub.setdebuglevel(0);
711 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
712
713 WHEN OTHERS THEN
714 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Apply_Hold_for_IReq '||sqlerrm,1);
715 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
718 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Apply_Hold_for_IReq');
719 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
720 END IF;
721 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
722 P_Count => x_msg_Count,
723 P_Data => x_msg_Data);
724 ROLLBACK TO Apply_Hold_For_IReq;
725
726 -- Added for 8583903
727 oe_debug_pub.debug_off;
728 oe_Debug_pub.setdebuglevel(0);
729 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
730
731 End Apply_Hold_for_IReq;
732
733
734 Procedure Release_Hold_for_IReq -- Body definition
735 ( P_API_Version IN NUMBER
736 , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
737 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
738 , X_msg_count OUT NOCOPY NUMBER
739 , X_msg_data OUT NOCOPY VARCHAR2
740 , X_return_status OUT NOCOPY VARCHAR2
741 ) IS
742 --
743 l_API_name Constant Varchar2(30) := 'RELEASE_HOLD_FOR_IREQ';
744 l_API_version Constant Number := 1.0;
745
746 l_req_hdr_id PO_Requisition_Headers_All.Requisition_Header_id%TYPE;
747 l_line_id NUMBER;
748 l_header_id NUMBER;
749 l_return_status VARCHAR2(1);
750 l_msg_count NUMBER;
751 l_msg_data VARCHAR2(2000);
752 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
753 l_hold_release_rec OE_Holds_Pvt.Hold_Release_REC_Type;
754 --
755 -- Added for 8583903
756 l_file_val VARCHAR2(2000);
757 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
758 --
759
760 Begin
761
762 -- Added IF condition for 8583903
763 IF (g_fnd_debug = 'Y') THEN
764
765 oe_debug_pub.debug_on;
766 oe_debug_pub.initialize;
767 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); --Dir/File
768 oe_Debug_pub.setdebuglevel(5);
769
770 l_debug_level := oe_debug_pub.g_debug_level;
771
772 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
773 FND_LOG.string( log_level => FND_LOG.LEVEL_STATEMENT
774 , module => 'po.plsql.'||G_PKG_Name||'.'||l_API_name
775 , message => '*** The Order Mangement Debug Log Dir/File is '||l_file_val);
776 END IF;
777 END IF;
778
779 IF Not FND_API.Compatible_API_Call
780 ( l_API_version
781 , p_API_version
782 , l_API_name
783 , G_PKG_Name) THEN
784 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786
787 SavePoint Release_Hold_For_IReq;
788
789 IF l_debug_level > 0 THEN
790 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Release_Hold_for_IReq', 1 ) ;
791 oe_debug_pub.add( ' Requisition Line id :'||P_internal_req_line_id , 5 ) ;
792 oe_debug_pub.add( ' Requisition Header id :'||P_internal_req_header_id , 5) ;
793 END IF;
794
795 -- OE_MSG_PUB.set_msg_context();
796 -- No need to set the message context as the caller of this API is PO
797 -- and Message window is not applicable in Requesting organization
798
799 l_return_status := FND_API.G_RET_STS_SUCCESS;
800
801 IF P_internal_req_line_id is NULL THEN
802 IF l_debug_level > 0 THEN
803 oe_debug_pub.add( ' Invalid value passed for Requisition Line',1);
804 END IF;
805 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
806 -- FND_Message.Set_Token('REQ_LIN_ID',P_internal_req_line_id);
807 OE_MSG_PUB.Add;
808 RAISE FND_API.G_EXC_ERROR;
809 END IF;
810 IF P_internal_req_header_id is NULL THEN
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( ' No value passed for Requisition Header',1);
813 END IF;
814 Begin
815 oe_debug_pub.add( ' Retrieving Requisition Header id',5);
816 select requisition_header_id
817 into l_req_hdr_id
818 from po_requisition_lines_all
819 where requisition_line_id = P_internal_req_line_id;
820
821 IF l_req_hdr_id IS NULL THEN
822 oe_debug_pub.add( ' Invalid value for Requisition Header',5);
823 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_HDR');
824 -- FND_Message.Set_Token('REQ_HDR_ID',P_internal_req_header_id);
825 OE_MSG_PUB.Add;
826 RAISE FND_API.G_EXC_ERROR;
827 END IF;
828 Exception
829 When No_Data_Found Then
830 oe_debug_pub.add( ' Invalid value passed for Requisition Line',5);
831 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
832 -- FND_Message.Set_Token('REQ_LIN_ID',P_internal_req_line_id);
833 OE_MSG_PUB.Add;
834 RAISE FND_API.G_EXC_ERROR;
835 End;
836 ELSE
837 l_req_hdr_id := P_internal_req_header_id;
838 END IF;
839
840 Begin
841
842 select l.line_id, l.header_id
843 into l_line_id, l_header_id
844 from oe_order_headers_all h
845 , oe_order_lines_all l
846 , oe_order_holds_all oh
847 , oe_hold_sources_all hs
848 -- OE_Order_Header_All table is used in this query to use
849 -- the OE_Order_Headers_N7 index for performance reasons
850 where h.header_id = l.header_id
851 and h.header_id = oh.header_id
852 and l.line_id = oh.line_id
853 and oh.hold_source_id = hs.hold_source_id
854 and hs.hold_id = 17
855 and oh.hold_release_id is null
856 and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
857 and h.source_document_id = l_req_hdr_id
858 and l.source_document_line_id = P_internal_req_line_id;
859
860 IF l_debug_level > 0 THEN
861 oe_debug_pub.add( ' Releasing hold for line_id '||l_line_id,5);
862 END IF;
863
864 l_hold_source_rec.hold_id := 17;
865 -- Ensure that the new seeded hold_id should be 17 in seed database
866 l_hold_source_rec.hold_entity_code := 'O';
867 l_hold_source_rec.hold_entity_id := l_header_id;
868 l_hold_source_rec.line_id := l_line_id; -- Line level hold
869 l_hold_source_rec.header_id := l_header_id;
870
871 l_hold_release_rec.release_reason_code := 'IR_ISO_HOLD';
872 -- We need to seed a new reason as a lookup code
873 l_hold_release_rec.release_comment := 'IR ISO Change Management System hold is released';
874
875 OE_GLOBALS.G_SYS_HOLD := TRUE; -- bug 9494397
876 OE_Holds_Pvt.Release_Holds( p_hold_source_rec => l_hold_source_rec
877 , p_hold_release_rec => l_hold_release_rec
878 , x_return_status => l_return_status
879 , x_msg_count => l_msg_count
880 , x_msg_data => l_msg_data );
881 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
882
883 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
884 RAISE FND_API.G_EXC_ERROR;
885 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887 END IF;
888
889 Exception
890 When No_Data_Found Then
891 IF l_debug_level > 0 THEN
892 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Release_Hold_for_Ireq with No Data Found Error');
893 END IF;
894 FND_Message.Set_Name('ONT', 'OE_IRCMS_NO_HOLD_RELEASED');
895 -- There is no sales order line shipment on hold.
896 OE_MSG_PUB.Add;
897 X_return_status := FND_API.G_RET_STS_ERROR;
898
899 When Too_Many_Rows Then
900 IF l_debug_level > 0 THEN
901 oe_debug_pub.add('EXITING OE_Internal_Requisition_Pvt.Release_Hold_for_Ireq with Too Many rows error') ;
902 END IF;
903 FND_Message.Set_Name('ONT', 'OE_IRCMS_MANY_HOLD');
904 -- There are many sales order line shipments eligible for hold release. This is not allowed.
905 OE_MSG_PUB.Add;
906 X_return_status := FND_API.G_RET_STS_ERROR;
907 End;
908
909 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
910 -- Not resetting the message because it was never initialized
911 -- Same is the case in Exception block
912
913 x_return_status := l_return_status;
914
915 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
916 P_Count => x_msg_Count,
917 P_Data => x_msg_Data);
918
919 IF l_debug_level > 0 THEN
920 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Release_Hold_for_IReq', 1 ) ;
921 END IF;
922
923 -- Added for 8583903
924 oe_debug_pub.debug_off;
925 oe_Debug_pub.setdebuglevel(0);
926
927
928 Exception
929 WHEN FND_API.G_EXC_ERROR THEN
930 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
933 P_Count => x_msg_Count,
934 P_Data => x_msg_Data);
935 ROLLBACK TO Release_Hold_For_IReq;
936
937 -- Added for 8583903
938 oe_debug_pub.debug_off;
939 oe_Debug_pub.setdebuglevel(0);
940 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
941
942
943 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
944 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
947 P_Count => x_msg_Count,
948 P_Data => x_msg_Data);
949 ROLLBACK TO Release_Hold_For_IReq;
950
951 -- Added for 8583903
952 oe_debug_pub.debug_off;
953 oe_Debug_pub.setdebuglevel(0);
954 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
955
956 WHEN OTHERS THEN
957 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Release_Hold_for_IReq '||sqlerrm,1);
958 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
960 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
961 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Release_Hold_for_IReq');
962 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
963 END IF;
964 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
965 P_Count => x_msg_Count,
966 P_Data => x_msg_Data);
967 ROLLBACK TO Release_Hold_For_IReq;
968
969 -- Added for 8583903
970 oe_debug_pub.debug_off;
971 oe_Debug_pub.setdebuglevel(0);
972 OE_GLOBALS.G_SYS_HOLD := FALSE; -- bug 9494397
973
974 End Release_Hold_for_IReq;
975
976
977 Procedure Is_IReq_Changable -- Body definition
978 ( P_API_Version IN NUMBER
979 , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
980 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
981 , X_Update_Allowed OUT NOCOPY BOOLEAN
982 , X_Cancel_Allowed OUT NOCOPY BOOLEAN
983 , X_msg_count OUT NOCOPY NUMBER
984 , X_msg_data OUT NOCOPY VARCHAR2
985 , X_return_status OUT NOCOPY VARCHAR2
986 ) IS
987
988 --
989 CURSOR All_Order_Lines (v_order_header_id NUMBER) IS
990 select l.line_id
991 from oe_order_lines_all l
992 where l.header_id = v_order_header_id
993 and nvl(l.cancelled_flag,'N') = 'N';
994 --
995 l_API_name Constant Varchar2(30) := 'IS_IREQ_CHANGABLE';
996 l_API_version Constant Number := 1.0;
997
998 l_req_hdr_id PO_Requisition_Headers_All.Requisition_Header_id%TYPE;
999 l_header_id NUMBER;
1000 l_line_id NUMBER;
1001 l_lines_ctr NUMBER := 0;
1002 l_cancel_eligble_lin NUMBER := 0;
1003 l_line_ids_rec Line_Id_rec_Type;
1004 l_update_allowed BOOLEAN := FALSE;
1005 l_cancel_allowed BOOLEAN := FALSE;
1006 l_skip_ctr_chk BOOLEAN := FALSE;
1007 l_Cancel_Allowed_ctr NUMBER := 0;
1008 l_loop_counter NUMBER := 0;
1009 l_return_status VARCHAR2(1);
1010 l_msg_count NUMBER;
1011 l_msg_data VARCHAR2(2000);
1012 l_hold_applied_count NUMBER := 0;
1013 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
1014 --
1015 -- Added for 8583903
1016 l_file_val VARCHAR2(2000);
1017 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
1018 --
1019
1020 Begin
1021
1022 -- Added IF condition for 8583903
1023 IF (g_fnd_debug = 'Y') THEN
1024
1025 oe_debug_pub.debug_on;
1026 oe_debug_pub.initialize;
1027 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); --Dir/File
1028 oe_Debug_pub.setdebuglevel(5);
1029
1030 l_debug_level := oe_debug_pub.g_debug_level;
1031
1032 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1033 FND_LOG.string( log_level => FND_LOG.LEVEL_STATEMENT
1034 , module => 'po.plsql.'||G_PKG_Name||'.'||l_API_name
1035 , message => '*** The Order Mangement Debug Log Dir/File is '||l_file_val);
1036 END IF;
1037 END IF;
1038
1039 IF Not FND_API.Compatible_API_Call
1040 ( l_API_version
1041 , p_API_version
1042 , l_API_name
1043 , G_PKG_Name) THEN
1044 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1045 END IF;
1046
1047 IF l_debug_level > 0 THEN
1048 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Is_IReq_Changable', 1 ) ;
1049 oe_debug_pub.add( ' Requisition Line id :'||P_internal_req_line_id , 5 ) ;
1050 oe_debug_pub.add( ' Requisition Header id :'||P_internal_req_header_id , 5) ;
1051 END IF;
1052
1053 -- OE_MSG_PUB.set_msg_context();
1054 -- No need to set the message context as the caller of this API is PO
1055 -- and Message window is not applicable in Requesting organization
1056
1057 l_return_status := FND_API.G_RET_STS_SUCCESS;
1058 X_Update_Allowed := FALSE;
1059 X_Cancel_Allowed := FALSE;
1060
1061 IF P_internal_req_header_id is NULL AND P_internal_req_line_id is NULL THEN
1062 IF l_debug_level > 0 THEN
1063 oe_debug_pub.add( ' Invalid value for Requisition Header/Line',1);
1064 END IF;
1065 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_INFO');
1066 OE_MSG_PUB.Add;
1067 RAISE FND_API.G_EXC_ERROR;
1068 END IF;
1069
1070 IF P_internal_req_header_id is NOT NULL AND P_internal_req_line_id is NULL THEN
1071 IF l_debug_level > 0 THEN
1072 oe_debug_pub.add('Requisition Line is NULL. We cannot check if Requisition Line is allowed to Update',1);
1073 END IF;
1074 X_UPDATE_Allowed := FALSE;
1075
1076 IF l_debug_level > 0 THEN
1077 oe_debug_pub.add( ' Checking if Requisition header is allowed to Cancel',5);
1078 END IF;
1079 Begin
1080 select h.header_id
1081 into l_header_id
1082 from oe_order_headers_all h
1083 where h.source_document_id = p_internal_req_header_id
1084 and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
1085 and h.open_flag = 'Y';
1086
1087 IF l_debug_level > 0 THEN
1088 oe_debug_pub.add( ' Header id is '||l_header_id,5);
1089 END IF;
1090
1091 OPEN All_Order_Lines (l_header_id);
1092 LOOP
1093 FETCH All_Order_Lines INTO l_line_id;
1094 EXIT WHEN All_Order_Lines%NOTFOUND;
1095 l_lines_ctr := l_lines_ctr + 1;
1096 IF l_debug_level > 0 THEN
1097 oe_debug_pub.add( ' Checking cancellation allowed for line_id '||l_line_id,5);
1098 END IF;
1099
1100 IF Cancel_Allowed( p_Line_id => l_line_id) THEN
1101 l_cancel_eligble_lin := l_cancel_eligble_lin + 1;
1102 ELSE
1103 IF l_debug_level > 0 THEN
1104 oe_debug_pub.add( ' Line is not allowed for cancellation',5);
1105 oe_debug_pub.add( ' Since line cancel is not allowed, setting header FULL Cancel as FALSE',5);
1106 oe_debug_pub.add( ' Exiting out of the loop ',5);
1107 END IF;
1108 X_Cancel_Allowed := FALSE;
1109 l_skip_ctr_chk := TRUE;
1110 EXIT;
1111 END IF;
1112 END LOOP;
1113 CLOSE All_Order_Lines;
1114
1115 IF NOT l_skip_ctr_chk THEN
1116 IF l_debug_level > 0 THEN
1117 oe_debug_pub.add( ' Total number of order lines are '||l_lines_ctr);
1118 oe_debug_pub.add( ' Total number of lines eligible for cancellation are '||l_cancel_eligble_lin);
1119 END IF;
1120
1121 IF l_lines_ctr = l_cancel_eligble_lin THEN
1122 IF l_debug_level > 0 THEN
1123 oe_debug_pub.add( ' All Lines are eligible for cancellation',5);
1124 END IF;
1125 X_Cancel_Allowed := Cancel_Header_Allowed( P_header_id => l_header_id);
1126 IF X_Cancel_Allowed THEN
1127 IF l_debug_level > 0 THEN
1128 oe_debug_pub.add( ' Header cancellation is TRUE',5);
1129 END IF;
1130 ELSE
1131 IF l_debug_level > 0 THEN
1132 oe_debug_pub.add( ' Header cancellation is FALSE',5);
1133 END IF;
1134 END IF;
1135 ELSE
1136 IF l_debug_level > 0 THEN
1137 oe_debug_pub.add( ' Header cancellation is not allowed' , 5);
1138 END IF;
1139 X_Cancel_Allowed := FALSE;
1140 END IF;
1141 END IF; -- NOT l_skip_ctr_chk
1142
1143 GOTO SKIP_REQ_LINE_CHK;
1144
1145 Exception
1146 When No_Data_Found Then
1147 IF l_debug_level > 0 THEN
1148 oe_debug_pub.add( ' Requisition is not allowed to cancel',1);
1149 END IF;
1150 X_Cancel_Allowed := FALSE;
1151 GOTO SKIP_REQ_LINE_CHK;
1152 When Others Then
1153 IF l_debug_level > 0 THEN
1154 oe_debug_pub.add( ' Invalid value for Requisition Header',1);
1155 END IF;
1156 X_Cancel_Allowed := FALSE;
1157 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_HDR');
1158 OE_MSG_PUB.Add;
1159 RAISE FND_API.G_EXC_ERROR;
1160 End;
1161 END IF;
1162
1163 IF P_internal_req_header_id is NULL AND P_internal_req_line_id is NOT NULL THEN
1164 IF l_debug_level > 0 THEN
1165 oe_debug_pub.add( ' No value passed for Requisition Header',1);
1166 END IF;
1167 Begin
1168 oe_debug_pub.add( ' Retrieving Requisition Header id',5);
1169 select requisition_header_id
1170 into l_req_hdr_id
1171 from po_requisition_lines_all
1172 where requisition_line_id = P_internal_req_line_id;
1173
1174 IF l_req_hdr_id IS NULL THEN
1175 oe_debug_pub.add( ' Invalid value for Requisition Header',5);
1176 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_HDR');
1177 -- FND_Message.Set_Token('REQ_HDR_ID',P_internal_req_header_id);
1178 OE_MSG_PUB.Add;
1179 RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181 Exception
1182 When No_Data_Found Then
1183 oe_debug_pub.add( ' Invalid value passed for Requisition Line',5);
1184 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
1185 -- FND_Message.Set_Token('REQ_LIN_ID',P_internal_req_line_id);
1186 OE_MSG_PUB.Add;
1187 RAISE FND_API.G_EXC_ERROR;
1188 End;
1189 ELSE
1190 l_req_hdr_id := P_internal_req_header_id;
1191 END IF;
1192
1193 Get_Eligible_ISO_Shipment
1194 ( P_internal_req_line_id => P_internal_req_line_id
1195 , P_internal_req_header_id => l_req_hdr_id
1196 , X_line_ids_rec => l_line_ids_rec
1197 , X_return_status => l_return_status );
1198
1199 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1200 RAISE FND_API.G_EXC_ERROR;
1201 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1203 END IF;
1204
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add( ' Line id: '||l_line_ids_rec.line_id,5);
1207 END IF;
1208
1209 IF NOT X_Update_Allowed THEN
1210 IF l_debug_level > 0 THEN
1211 oe_debug_pub.add( ' Checking if Update is allowed',5);
1212 END IF;
1213 l_Update_Allowed := Update_Allowed(P_Line_id => l_line_ids_rec.line_id);
1214 IF l_Update_Allowed THEN
1215 IF l_debug_level > 0 THEN
1216 oe_debug_pub.add( ' Update is Allowed',5);
1217 END IF;
1218 X_Update_Allowed := TRUE;
1219 ELSE
1220 IF l_debug_level > 0 THEN
1221 oe_debug_pub.add(' Update is Not Allowed for this requisition line',1);
1222 END IF;
1223 END IF;
1224 END IF;
1225
1226 IF NOT X_Cancel_Allowed THEN
1227 IF l_debug_level > 0 THEN
1228 oe_debug_pub.add( ' Checking if Cancel is allowed',5);
1229 END IF;
1230 l_Cancel_Allowed := Cancel_Allowed(p_line_id => l_line_ids_rec.line_id);
1231
1232 IF l_Cancel_Allowed THEN
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add( ' Cancel is Allowed',5);
1235 END IF;
1236 X_Cancel_Allowed := TRUE;
1237 ELSE
1238 IF l_debug_level > 0 THEN
1239 oe_debug_pub.add(' Cancel is Not Allowed for this requisition line',1);
1240 END IF;
1241 END IF;
1242 END IF;
1243
1244 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1245 -- Not resetting the message because it was never initialized
1246 -- Same is the case in Exception block
1247
1248 <<SKIP_REQ_LINE_CHK>>
1249 null;
1250
1251 IF l_debug_level > 0 THEN
1252 IF X_Update_Allowed THEN
1253 oe_debug_pub.add( ' Record is allowed to Update',5);
1254 END IF;
1255 IF X_Cancel_Allowed THEN
1256 oe_debug_pub.add( ' Record is allowed to Cancel',5);
1257 END IF;
1258 END IF;
1259
1260 x_return_status := l_return_status;
1261
1262 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1263 P_Count => x_msg_Count,
1264 P_Data => x_msg_Data);
1265
1266 IF l_debug_level > 0 THEN
1267 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Is_IReq_Changable', 1 ) ;
1268 END IF;
1269
1270 -- Added for 8583903
1271 oe_debug_pub.debug_off;
1272 oe_Debug_pub.setdebuglevel(0);
1273
1274 Exception
1275 WHEN FND_API.G_EXC_ERROR THEN
1276 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278 X_Update_Allowed := FALSE;
1279 X_Cancel_Allowed := FALSE;
1280 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1281 P_Count => x_msg_Count,
1282 P_Data => x_msg_Data);
1283
1284 -- Added for 8583903
1285 oe_debug_pub.debug_off;
1286 oe_Debug_pub.setdebuglevel(0);
1287
1288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1289 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1291 X_Update_Allowed := FALSE;
1292 X_Cancel_Allowed := FALSE;
1293 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1294 P_Count => x_msg_Count,
1295 P_Data => x_msg_Data);
1296
1297 -- Added 8583903
1298 oe_debug_pub.debug_off;
1299 oe_Debug_pub.setdebuglevel(0);
1300
1301 WHEN OTHERS THEN
1302 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Is_IReq_Changable '||sqlerrm,1);
1303 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1305 X_Update_Allowed := FALSE;
1306 X_Cancel_Allowed := FALSE;
1307 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1308 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Is_IReq_Changable');
1309 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
1310 END IF;
1311 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1312 P_Count => x_msg_Count,
1313 P_Data => x_msg_Data);
1314
1315 -- Added for 8583903
1316 oe_debug_pub.debug_off;
1317 oe_Debug_pub.setdebuglevel(0);
1318
1319 End Is_IReq_Changable;
1320
1321
1322 Procedure Call_Process_Order_for_IReq -- Body definition
1323 ( P_API_Version IN NUMBER
1324 , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
1325 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
1326 , P_Mode IN VARCHAR2
1327 , P_Cancel_ISO IN BOOLEAN
1328 , P_Cancel_ISO_lines IN BOOLEAN
1329 , P_New_Request_Date IN DATE
1330 , P_Delta_Ordered_Qty IN NUMBER
1331 , X_msg_count OUT NOCOPY NUMBER
1332 , X_msg_data OUT NOCOPY VARCHAR2
1333 , X_return_status OUT NOCOPY VARCHAR2
1334 ) IS
1335 --
1336 CURSOR All_Order_Lines (v_order_header_id NUMBER) IS
1337 select l.line_id, l.header_id, l.ordered_quantity2
1338 from oe_order_lines_all l
1339 -- , oe_order_headers_all h
1340 where nvl(l.shipped_quantity,0) = 0
1341 -- and h.orig_sys_document_ref = p_internal_req_header_id
1342 -- and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
1343 -- and h.header_id = v_order_header_id
1344 -- and h.header_id = l.header_id
1345 and l.source_document_id = p_internal_req_header_id
1346 and l.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
1347 and l.header_id = v_order_header_id
1348 -- and h.open_flag = 'Y'
1349 and nvl(cancelled_flag,'N') = 'N'
1350 and l.open_flag = 'Y'
1351 and not exists (select 1 from wsh_delivery_details w
1352 where w.source_line_id = l.line_id
1353 and w.source_code = 'OE'
1354 and released_status = 'C')
1355 order by l.line_id;
1356 --
1357 l_API_name Constant Varchar2(30) := 'CALL_PROCESS_ORDER_FOR_IREQ';
1358 l_API_version Constant Number := 1.0;
1359 --
1360 l_req_hdr_id PO_Requisition_Headers_All.Requisition_Header_id%TYPE;
1361 l_line_ids_rec Line_Id_rec_Type;
1362 l_line_orig_rec OE_Order_PUB.Line_Rec_Type := OE_ORDER_PUB.G_MISS_LINE_REC;
1363 l_line_tbl OE_Order_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1364 l_control_rec OE_GLOBALS.Control_Rec_Type;
1365 l_return_status VARCHAR2(1);
1366 l_header_rec OE_Order_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1367 l_old_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1368 l_line_id NUMBER;
1369 l_header_id NUMBER;
1370 l_order_header_id NUMBER;
1371 l_line_ord_qty2 NUMBER;
1372 l_lin_update NUMBER := 0;
1373 l_lin_cancel NUMBER := 0;
1374 l_lines_ctr NUMBER := 0;
1375 l_cancel_eligble_lin NUMBER := 0;
1376 l_count_of_lines NUMBER;
1377 l_msg_count NUMBER;
1378 l_msg_data VARCHAR2(2000);
1379 l_cancel_request BOOLEAN := FALSE;
1380 l_Process_Line_Entity BOOLEAN := FALSE;
1381 l_Cancel_Allowed BOOLEAN := FALSE;
1382 --
1383 -- Added for 8583903
1384 l_file_val VARCHAR2(2000);
1385 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
1386 --
1387
1388 Begin
1389
1390 -- Added IF condition for 8583903
1391 IF (g_fnd_debug = 'Y') THEN
1392
1393 oe_debug_pub.debug_on;
1394 oe_debug_pub.initialize;
1395 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); -- Dir/File
1396 oe_Debug_pub.setdebuglevel(5);
1397
1398 l_debug_level := oe_debug_pub.g_debug_level;
1399
1400 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1401 FND_LOG.string( log_level => FND_LOG.LEVEL_STATEMENT
1402 , module => 'po.plsql.'||G_PKG_Name||'.'||l_API_name
1403 , message => '*** The Order Mangement Debug Log Dir/File is '||l_file_val);
1404 END IF;
1405 END IF;
1406
1407 IF Not FND_API.Compatible_API_Call
1408 ( l_API_version
1409 , p_API_version
1410 , l_API_name
1411 , G_PKG_Name) THEN
1412 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1413 END IF;
1414
1415 SavePoint Call_Process_Order_for_IReq;
1416
1417 IF l_debug_level > 0 THEN
1418 oe_debug_pub.add( 'ENTERING OE_Internal_Requisition_Pvt.Call_Process_Order_for_IReq', 1 ) ;
1419 oe_debug_pub.add( ' P_internal_req_line_id :'||P_internal_req_line_id , 5 ) ;
1420 oe_debug_pub.add( ' P_internal_req_header_id :'||P_internal_req_header_id , 5 ) ;
1421 oe_debug_pub.add( ' P_Mode :'||P_Mode , 5 ) ;
1422 oe_debug_pub.add( ' P_New_Request_Date :'||P_New_Request_Date , 5 ) ;
1423 oe_debug_pub.add( ' P_Delta_Ordered_Qty :'||P_Delta_Ordered_Qty , 5 ) ;
1424 IF P_Cancel_ISO THEN
1425 oe_debug_pub.add( ' Header level cancellation',5);
1426 ELSE
1427 oe_debug_pub.add( ' Not a header level cancellation',5);
1428 END IF;
1429 IF P_Cancel_ISO_Lines THEN
1430 oe_debug_pub.add( ' Line level cancellation',5);
1431 ELSE
1432 oe_debug_pub.add( ' Not a line level cancellation',5);
1433 END IF;
1434 END IF;
1435
1436 -- OE_MSG_PUB.set_msg_context();
1437 -- No need to set the message context as the caller of this API is PO
1438 -- and Message window is not applicable in Requesting organization
1439
1440 OE_msg_PUB.Initialize;
1441
1442 l_return_status := FND_API.G_RET_STS_SUCCESS;
1443
1444 IF (NOT P_Cancel_ISO AND NOT P_Cancel_ISO_Lines AND
1445 P_New_Request_Date is NULL AND P_Delta_Ordered_Qty = 0) OR
1446 (P_Cancel_ISO AND P_Cancel_ISO_Lines) OR
1447 (P_Cancel_ISO AND P_internal_req_header_id is null) OR
1448 (P_Cancel_ISO_Lines AND P_internal_req_line_id is null) OR
1449 (NOT P_Cancel_ISO AND P_internal_req_line_id is NULL) OR
1450 (P_internal_req_header_id is null AND P_internal_req_line_id is null) OR
1451 (P_Cancel_ISO AND P_New_Request_Date IS NOT NULL) OR
1452 (P_Cancel_ISO AND P_Delta_Ordered_Qty IS NOT NULL AND P_Delta_Ordered_Qty <> 0) OR
1453 (P_Cancel_ISO_Lines AND P_New_Request_Date IS NOT NULL) OR
1454 (P_Cancel_ISO_Lines AND P_Delta_Ordered_Qty IS NOT NULL AND P_Delta_Ordered_Qty <> 0) OR
1455 (P_New_Request_Date IS NOT NULL AND P_internal_req_line_id IS NULL) OR
1456 (P_Delta_Ordered_Qty IS NOT NULL AND P_Delta_Ordered_Qty <> 0 AND P_internal_req_line_id IS NULL) THEN
1457 IF l_debug_level > 0 THEN
1458 oe_debug_pub.add( ' Invalid call to Order Management', 5 ) ;
1459 oe_debug_pub.add( ' Please provide a valid argument values', 5 ) ;
1460 END IF;
1461 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_OM_CALL');
1462 OE_MSG_PUB.Add;
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465
1466 IF P_Mode NOT IN ('V','P') THEN
1467 IF l_debug_level > 0 THEN
1468 oe_debug_pub.add( ' Mode is Invalid. The Valid values are V or P', 1 ) ;
1469 oe_debug_pub.add( ' Invalid Mode passed to Fulfillment Organization',5);
1470 END IF;
1471 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_MODE');
1472 OE_MSG_PUB.Add;
1473 RAISE FND_API.G_EXC_ERROR;
1474 END IF;
1475
1476 IF P_internal_req_header_id is NULL THEN
1477 IF l_debug_level > 0 THEN
1478 oe_debug_pub.add( ' No value passed for Requisition Header',1);
1479 END IF;
1480 Begin
1481 IF l_debug_level > 0 THEN
1482 oe_debug_pub.add( ' Retrieving Requisition Header id',5);
1483 END IF;
1484 select requisition_header_id
1485 into l_req_hdr_id
1486 from po_requisition_lines_all
1487 where requisition_line_id = P_internal_req_line_id;
1488
1489 IF l_req_hdr_id IS NULL THEN
1490 IF l_debug_level > 0 THEN
1491 oe_debug_pub.add( ' Invalid value for Requisition Header',5);
1492 END IF;
1493 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_HDR');
1494 -- FND_Message.Set_Token('REQ_HDR_ID',P_internal_req_header_id);
1495 OE_MSG_PUB.Add;
1496 RAISE FND_API.G_EXC_ERROR;
1497 END IF;
1498 Exception
1499 When No_Data_Found Then
1500 IF l_debug_level > 0 THEN
1501 oe_debug_pub.add( ' Invalid value passed for Requisition Line',5);
1502 END IF;
1503 FND_Message.Set_Name('ONT', 'OE_IRCMS_INVALID_REQ_LIN');
1504 -- FND_Message.Set_Token('REQ_LIN_ID',P_internal_req_line_id);
1505 OE_MSG_PUB.Add;
1506 RAISE FND_API.G_EXC_ERROR;
1507 End;
1508 ELSE
1509 l_req_hdr_id := P_internal_req_header_id;
1510 END IF;
1511
1512 G_Update_ISO_From_Req := TRUE; -- Confirming IR initiated change
1513
1514 l_lin_update := 0;
1515
1516 IF P_Cancel_ISO THEN
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add(' Order level cancellation request',5);
1519 END IF;
1520
1521 Begin
1522 select header_id
1523 into l_order_header_id
1524 from oe_order_headers_all h
1525 where h.source_document_id = l_req_hdr_id
1526 and h.order_source_id = OE_Globals.G_ORDER_SOURCE_INTERNAL
1527 and h.open_flag = 'Y';
1528 Exception
1529 WHEN No_Data_found THEN
1530 IF l_debug_level > 0 THEN
1531 oe_debug_pub.add( ' Invalid value passed for Requisition Header: no data found',1);
1532 END IF;
1533 FND_Message.Set_name('ONT','OE_IRCMS_INVALID_REQ_HDR');
1534 RAISE FND_API.G_EXC_ERROR;
1535 WHEN Too_Many_Rows THEN
1536 IF l_debug_level > 0 THEN
1537 oe_debug_pub.add( ' Invalid value passed for Requisition Header: too many rows',1);
1538 END IF;
1539 FND_Message.Set_name('ONT','OE_IRCMS_INVALID_REQ_HDR');
1540 RAISE FND_API.G_EXC_ERROR;
1541 End;
1542
1543 l_Cancel_Allowed := Cancel_Header_Allowed( P_header_id => l_order_header_id);
1544
1545 IF l_Cancel_Allowed THEN
1546 IF l_debug_level > 0 THEN
1547 oe_debug_pub.add( ' No Header level cancellation constraint',5);
1548 END IF;
1549
1550 OPEN All_Order_Lines (l_order_header_id);
1551 LOOP
1552 FETCH All_Order_Lines INTO l_line_id, l_header_id, l_line_ord_qty2;
1553 EXIT WHEN All_Order_Lines%NOTFOUND;
1554 l_lines_ctr := l_lines_ctr + 1;
1555 IF Cancel_Allowed( p_Line_id => l_line_id) THEN
1556
1557 l_cancel_eligble_lin := l_cancel_eligble_lin + 1;
1558
1559 OE_LINE_UTIL.QUERY_ROW( p_line_id => l_line_id
1560 , x_line_rec => l_line_orig_rec);
1561
1562 l_line_tbl(l_cancel_eligble_lin) := l_line_orig_rec;
1563
1564 -- l_line_tbl(l_cancel_eligble_lin).line_id := l_line_id;
1565 -- l_line_tbl(l_cancel_eligble_lin).header_id := l_header_id;
1566 l_line_tbl(l_cancel_eligble_lin).operation := OE_GLOBALS.G_OPR_UPDATE;
1567 l_line_tbl(l_cancel_eligble_lin).ordered_quantity := 0;
1568 l_line_tbl(l_cancel_eligble_lin).change_reason := 'IR_ISO_CMS_CHG'; -- 'Internal requisition initiated change';
1569 IF (l_line_ord_qty2 IS NOT NULL AND l_line_ord_qty2 <> 0 ) THEN
1570 l_line_tbl(l_cancel_eligble_lin).ordered_quantity2 := 0;
1571 END IF;
1572 END IF;
1573 END LOOP;
1574 CLOSE All_Order_Lines;
1575
1576 select count(line_id)
1577 into l_count_of_lines
1578 from oe_order_lines_all
1579 where header_id = l_order_header_id
1580 and nvl(cancelled_flag,'N') = 'N';
1581
1582 IF l_cancel_eligble_lin > 0 OR l_count_of_lines = 0 THEN
1583 IF l_debug_level > 0 THEN
1584 oe_debug_pub.add('Order cancellation request is valid',5);
1585 END IF;
1586
1587 IF l_count_of_lines = l_cancel_eligble_lin THEN
1588
1589 IF l_debug_level > 0 THEN
1590 oe_debug_pub.add(' Cancel FULL Order request',5);
1591 END IF;
1592
1593 OE_MSG_PUB.set_msg_context
1594 ( p_entity_code => 'HEADER'
1595 , p_entity_id => l_order_header_id
1596 , p_header_id => l_order_header_id
1597 , p_line_id => null
1598 , p_orig_sys_document_ref => l_req_hdr_id
1599 , p_orig_sys_document_line_ref => null
1600 , p_change_sequence => null
1601 , p_source_document_id => l_req_hdr_id
1602 , p_source_document_line_id => null
1603 , p_order_source_id => OE_Globals.G_ORDER_SOURCE_INTERNAL
1604 , p_source_document_type_id => OE_Globals.G_ORDER_SOURCE_INTERNAL);
1605
1606 OE_Header_Util.lock_Row
1607 ( p_header_id => l_order_header_id
1608 , p_x_header_rec => l_header_rec
1609 , x_return_status => l_return_status );
1610
1611 l_old_header_rec := l_header_rec;
1612
1613 l_header_rec.cancelled_flag :='Y';
1614 l_header_rec.change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
1615 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1616
1617 l_control_rec.controlled_operation := TRUE;
1618 l_control_rec.default_attributes := FALSE;
1619
1620 IF p_mode = 'V' THEN
1621 l_control_rec.write_to_db := FALSE;
1622 l_control_rec.process := FALSE;
1623 END IF;
1624
1625 OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can := TRUE;
1626 -- This global is required to be set to TRUE if it is an internal sales
1627 -- order level cancellation.
1628
1629 oe_order_pvt.Header
1630 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
1631 , p_control_rec => l_control_rec
1632 , p_x_header_rec => l_header_rec
1633 , p_x_old_header_rec => l_old_header_rec
1634 , x_return_status => l_return_status );
1635
1636
1637 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1638 OE_MSG_PUB.Reset_Msg_Context('HEADER');
1639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1641 OE_MSG_PUB.Reset_Msg_Context('HEADER');
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644
1645 IF P_Mode = 'P' THEN -- Mode is Process
1646 OE_Order_PVT.Process_Requests_And_Notify
1647 ( p_process_requests => TRUE
1648 -- , p_notify => FALSE -- Not needed
1649 , x_return_status => l_return_status
1650 , p_header_rec => l_header_rec
1651 , p_old_header_rec => l_old_header_rec );
1652
1653
1654 x_return_status := l_return_status;
1655 IF l_debug_level > 0 THEN
1656 oe_debug_pub.add( ' Return Status is '||l_return_status,1) ;
1657 END IF;
1658 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1659 IF l_debug_level > 0 THEN
1660 oe_debug_pub.add( ' CANCELLATION UNEXPECTED FAILURE',1 ) ;
1661 END IF;
1662 OE_MSG_PUB.Reset_Msg_Context('HEADER');
1663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1664 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1665 IF l_debug_level > 0 THEN
1666 oe_debug_pub.add( ' CANCELLATION EXPECTED FAILURE',1 ) ;
1667 END IF;
1668 OE_MSG_PUB.Reset_Msg_Context('HEADER');
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671 END IF; -- P_Mode = PROCESS
1672
1673 OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can := FALSE;
1674 -- Resetting the global to FALSE, as processing is done.
1675
1676 OE_MSG_PUB.Reset_Msg_Context('HEADER');
1677
1678 ELSIF l_count_of_lines > l_cancel_eligble_lin THEN
1679 IF l_debug_level > 0 THEN
1680 oe_debug_pub.add(' Cancel REMAINING Order request',5);
1681 END IF;
1682 l_Process_Line_Entity := TRUE;
1683 l_cancel_request := TRUE;
1684 END IF; -- l_count_of_lines = l_cancel_eligble_lin
1685 END IF;
1686 ELSE
1687 IF l_debug_level > 0 THEN
1688 oe_debug_pub.add( ' There are Header level cancellation constraint',5);
1689 END IF;
1690 l_return_status := FND_API.G_RET_STS_ERROR;
1691 END IF;
1692 END IF; -- P_Cancel_Order
1693
1694 IF (P_New_Request_Date IS NOT NULL OR ( P_Delta_Ordered_Qty IS NOT NULL
1695 AND p_Delta_Ordered_Qty <> 0) OR P_Cancel_ISO_Lines) AND NOT l_Process_Line_Entity THEN
1696
1697 Get_Eligible_ISO_Shipment
1698 ( P_internal_req_line_id => P_internal_req_line_id
1699 , P_internal_req_header_id => l_req_hdr_id
1700 , X_line_ids_rec => l_line_ids_rec
1701 , X_return_status => l_return_status );
1702
1703 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1704 RAISE FND_API.G_EXC_ERROR;
1705 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END IF;
1708
1709 IF P_Cancel_ISO_Lines THEN
1710 IF l_debug_level > 0 THEN
1711 oe_debug_pub.add(' Line level Cancel request',5);
1712 oe_debug_pub.add(' Check for Cancel of Line_id '||l_line_ids_rec.line_id,5);
1713 END IF;
1714 IF Cancel_Allowed( p_Line_id => l_line_ids_rec.line_id) THEN
1715 l_lin_cancel := l_lin_cancel + 1;
1716 OE_LINE_UTIL.QUERY_ROW( p_line_id => l_line_ids_rec.line_id
1717 , x_line_rec => l_line_orig_rec);
1718 l_line_tbl(l_lin_cancel) := l_line_orig_rec;
1719 -- l_line_tbl(l_lin_cancel).line_id := l_line_ids_rec.line_id;
1720 -- l_line_tbl(l_lin_cancel).header_id := l_line_ids_rec.header_id;
1721 l_line_tbl(l_lin_cancel).operation := OE_GLOBALS.G_OPR_UPDATE;
1722 l_line_tbl(l_lin_cancel).ordered_quantity := 0;
1723 l_line_tbl(l_lin_cancel).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
1724 IF (l_line_ids_rec.ordered_quantity2 IS NOT NULL
1725 AND l_line_ids_rec.ordered_quantity2 <> 0 ) THEN
1726 l_line_tbl(l_lin_cancel).ordered_quantity2 := 0;
1727 END IF;
1728 l_Process_Line_Entity := TRUE;
1729 l_cancel_request := TRUE;
1730 ELSE
1731 IF l_debug_level > 0 THEN
1732 oe_debug_pub.add(' Cancel is not allowed for this line. Setting the status to Error',5);
1733 END IF;
1734 l_return_status := FND_API.G_RET_STS_ERROR;
1735 END IF;
1736
1737 ELSIF P_New_Request_Date IS NOT NULL OR ( P_Delta_Ordered_Qty IS NOT NULL
1738 AND p_Delta_Ordered_Qty <> 0) THEN -- This is an update request
1739
1740 IF l_debug_level > 0 THEN
1741 oe_debug_pub.add(' This is an Update request',5);
1742 END IF;
1743
1744 IF P_New_Request_Date IS NOT NULL AND ( P_Delta_Ordered_Qty IS NULL
1745 OR p_Delta_Ordered_Qty = 0) THEN
1746
1747 IF l_debug_level > 0 THEN
1748 oe_debug_pub.add(' Only Request Date is changed',5);
1749 oe_debug_pub.add(' Check if update is allowed for line_id '||l_line_ids_rec.line_id,5);
1750 END IF;
1751 IF NOT OE_Globals.Equal(P_New_Request_Date, l_line_ids_rec.request_date) AND
1752 Update_Allowed( p_Line_id => l_line_ids_rec.line_id
1753 , P_Attribute => 'REQUEST_DATE') THEN
1754
1755 IF l_debug_level > 0 THEN
1756 oe_debug_pub.add(' Request Date is different w.r.t. sales order line ',5);
1757 oe_debug_pub.add(' Request Date is allowed to change',5);
1758 END IF;
1759
1760 l_lin_update := l_lin_update + 1;
1761
1762 OE_LINE_UTIL.QUERY_ROW( p_line_id => l_line_ids_rec.line_id
1763 , x_line_rec => l_line_orig_rec);
1764 l_line_tbl(l_lin_update) := l_line_orig_rec;
1765
1766 -- l_line_tbl(l_lin_update).line_id := l_line_ids_rec.line_id;
1767 -- l_line_tbl(l_lin_update).header_id := l_line_ids_rec.header_id;
1768 l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
1769 l_line_tbl(l_lin_update).request_date := P_New_Request_Date;
1770 l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
1771
1772 l_Process_Line_Entity := TRUE;
1773
1774 ELSE
1775 IF l_debug_level > 0 THEN
1776 oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
1777 END IF;
1778 l_return_status := FND_API.G_RET_STS_ERROR;
1779 END IF;
1780
1781 ELSIF P_New_Request_Date IS NULL AND (P_Delta_Ordered_Qty IS NOT NULL
1782 OR p_Delta_Ordered_Qty <> 0) THEN
1783
1784 IF l_debug_level > 0 THEN
1785 oe_debug_pub.add(' Only Ordered Quantity is changed',5);
1786 oe_debug_pub.add(' Check for update of Line_id '||l_line_ids_rec.line_id,5);
1787 END IF;
1788 IF Update_Allowed( p_Line_id => l_line_ids_rec.line_id
1789 , P_Attribute => 'ORDERED_QUANTITY') THEN
1790
1791 l_lin_update := l_lin_update + 1;
1792
1793 OE_LINE_UTIL.QUERY_ROW( p_line_id => l_line_ids_rec.line_id
1794 , x_line_rec => l_line_orig_rec);
1795 l_line_tbl(l_lin_update) := l_line_orig_rec;
1796
1797 -- l_line_tbl(l_lin_update).line_id := l_line_ids_rec.line_id;
1798 -- l_line_tbl(l_lin_update).header_id := l_line_ids_rec.header_id;
1799 l_line_tbl(l_lin_update).ordered_quantity := l_line_ids_rec.ordered_quantity + P_Delta_Ordered_Qty;
1800 l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
1801 l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
1802
1803 l_Process_Line_Entity := TRUE;
1804
1805 ELSE
1806 IF l_debug_level > 0 THEN
1807 oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
1808 END IF;
1809 l_return_status := FND_API.G_RET_STS_ERROR;
1810 END IF;
1811
1812 ELSIF P_New_Request_Date IS NOT NULL AND (P_Delta_Ordered_Qty IS NOT NULL
1813 OR p_Delta_Ordered_Qty <> 0) THEN
1814
1815 IF l_debug_level > 0 THEN
1816 oe_debug_pub.add(' Both Request Date and Ordered Quantity are changed',5);
1817 oe_debug_pub.add(' Check for update of Line_id '||l_line_ids_rec.line_id,5);
1818 END IF;
1819 IF NOT OE_Globals.Equal(P_New_Request_Date, l_line_ids_rec.request_date) AND
1820 Update_Allowed( p_Line_id => l_line_ids_rec.line_id
1821 , P_Attribute => 'ALL') THEN
1822
1823 l_lin_update := l_lin_update + 1;
1824
1825 OE_LINE_UTIL.QUERY_ROW( p_line_id => l_line_ids_rec.line_id
1826 , x_line_rec => l_line_orig_rec);
1827 l_line_tbl(l_lin_update) := l_line_orig_rec;
1828
1829 -- l_line_tbl(l_lin_update).line_id := l_line_ids_rec.line_id;
1830 -- l_line_tbl(l_lin_update).header_id := l_line_ids_rec.header_id;
1831 l_line_tbl(l_lin_update).ordered_quantity := l_line_ids_rec.ordered_quantity + P_Delta_Ordered_Qty;
1832 l_line_tbl(l_lin_update).operation := OE_GLOBALS.G_OPR_UPDATE;
1833 l_line_tbl(l_lin_update).request_date := P_New_Request_Date;
1834 l_line_tbl(l_lin_update).change_reason := 'IR_ISO_CMS_CHG'; --'Internal requisition initiated change';
1835
1836 l_Process_Line_Entity := TRUE;
1837 ELSE
1838 IF l_debug_level > 0 THEN
1839 oe_debug_pub.add(' Update is not allowed for this line. Setting the status to Error',5);
1840 END IF;
1841 l_return_status := FND_API.G_RET_STS_ERROR;
1842 END IF;
1843 END IF; -- P_New_Request_Date/P_Delta_Ordered_Qty
1844 END IF; -- something has changed
1845 END IF; -- Update/Cancel Request
1846
1847 IF l_Process_Line_Entity THEN
1848 IF l_debug_level > 0 THEN
1849 oe_debug_pub.add( ' Before calling Process_Line_Entity', 5 ) ;
1850 END IF;
1851
1852 OE_MSG_PUB.set_msg_context
1853 ( p_entity_code => 'LINE'
1854 , p_entity_id => l_line_ids_rec.line_id
1855 , p_header_id => l_line_ids_rec.header_id
1856 , p_line_id => l_line_ids_rec.line_id
1857 , p_orig_sys_document_ref => l_req_hdr_id
1858 , p_orig_sys_document_line_ref => P_internal_req_line_id
1859 , p_change_sequence => null
1860 , p_source_document_id => l_req_hdr_id
1861 , p_source_document_line_id => P_internal_req_line_id
1862 , p_order_source_id => OE_Globals.G_ORDER_SOURCE_INTERNAL
1863 , p_source_document_type_id => OE_Globals.G_ORDER_SOURCE_INTERNAL);
1864
1865 Process_Line_Entity
1866 ( p_line_tbl => l_line_tbl
1867 , p_mode => P_mode
1868 , p_Cancel => l_cancel_request
1869 , x_return_status => l_return_status
1870 );
1871
1872 OE_MSG_PUB.Reset_Msg_Context('LINE');
1873
1874 IF l_debug_level > 0 THEN
1875 oe_debug_pub.add( ' After calling Process_Line_Entity'||l_return_status, 5 ) ;
1876 END IF;
1877 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1879 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1880 RAISE FND_API.G_EXC_ERROR;
1881 END IF;
1882 END IF;
1883
1884 x_return_status := l_return_status;
1885 G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
1886
1887 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1888 P_Count => x_msg_Count,
1889 P_Data => x_msg_Data);
1890
1891 IF l_debug_level > 0 THEN
1892 oe_debug_pub.add( 'EXITING OE_Internal_Requisition_Pvt.Call_Process_Order_for_IReq', 1 ) ;
1893 END IF;
1894
1895 -- Added for 8583903
1896 oe_debug_pub.debug_off;
1897 oe_Debug_pub.setdebuglevel(0);
1898
1899 Exception
1900 WHEN FND_API.G_EXC_ERROR THEN
1901 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1902 x_return_status := FND_API.G_RET_STS_ERROR;
1903 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1904 P_Count => x_msg_Count,
1905 P_Data => x_msg_Data);
1906 G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
1907 ROLLBACK TO Call_Process_Order_for_IReq;
1908
1909 -- Added for 8583903
1910 oe_debug_pub.debug_off;
1911 oe_Debug_pub.setdebuglevel(0);
1912
1913 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1914 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1916 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1917 P_Count => x_msg_Count,
1918 P_Data => x_msg_Data);
1919 G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
1920 ROLLBACK TO Call_Process_Order_for_IReq;
1921
1922 -- Added for 8583903
1923 oe_debug_pub.debug_off;
1924 oe_Debug_pub.setdebuglevel(0);
1925
1926 WHEN OTHERS THEN
1927 oe_debug_pub.add( ' When Others of OE_Internal_Requisition_Pvt.Call_Process_Order_for_IReq '||sqlerrm,1);
1928 -- OE_MSG_PUB.Reset_Msg_Context('LINE');
1929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1930 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1931 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Call_Process_Order_for_IReq');
1932 -- Pkg Body global variable = 'OE_internal_Requisition_Pvt'
1933 END IF;
1934 OE_MSG_PUB.Count_And_Get (P_encoded =>'F', --added for bug 13992154
1935 P_Count => x_msg_Count,
1936 P_Data => x_msg_Data);
1937 G_Update_ISO_From_Req := FALSE; -- Confirming IR initiated change
1938 ROLLBACK TO Call_Process_Order_for_IReq;
1939
1940 -- Added for 8583903
1941 oe_debug_pub.debug_off;
1942 oe_Debug_pub.setdebuglevel(0);
1943
1944 End Call_Process_Order_for_IReq;
1945
1946 END OE_INTERNAL_REQUISITION_PVT;