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