[Home] [Help]
PACKAGE BODY: APPS.OE_PROCESS_REQUISITION_PVT
Source
1 PACKAGE BODY OE_PROCESS_REQUISITION_PVT AS
2 /* $Header: OEXVPIRB.pls 120.7.12020000.2 2012/09/14 08:09:56 rahujain ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_Name CONSTANT VARCHAR2(30) := 'OE_PROCESS_REQUISITION_PVT';
6
7 g_requisition_number VARCHAR2(20);
8 g_requisition_line_number NUMBER;
9 g_need_by_date DATE;
10 g_requested_quantity NUMBER;
11 g_sales_order_number NUMBER;
12 g_order_cancellation_date DATE;
13 g_sales_ord_line_num NUMBER;
14 g_line_cancellation_date DATE;
15 g_ISO_cancelled_quantity NUMBER;
16 g_inventory_item_name VARCHAR2(2000); --bug10631734
17 g_updated_quantity NUMBER;
18 g_line_updated_date DATE;
19 g_schedule_ship_date DATE;
20 g_schedule_arrival_date DATE;
21 g_reason VARCHAR2(400);
22 g_requested_quantity2 NUMBER; --Bug 14211120
23 g_updated_quantity2 NUMBER; --Bug 14211120
24
25
26 PROCEDURE SET_ORG_CONTEXT -- Body details
27 ( p_org_id IN NUMBER
28 ) IS
29
30 l_progress VARCHAR2(3) := NULL;
31 l_current_org_id hr_all_organization_units_tl.organization_id%TYPE;
32 l_access_mode VARCHAR2(1);
33 l_ou_count NUMBER;
34 l_is_mo_init_done VARCHAR2(1);
35 --
36 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
37 --
38
39 BEGIN
40 l_progress := '00';
41 l_is_mo_init_done := MO_GLOBAL.is_mo_init_done;
42 l_progress := '10';
43 IF (l_is_mo_init_done <> 'Y') THEN
44 MO_GLOBAL.INIT('ONT');
45 END IF;
46 l_progress := '20';
47 IF (p_org_id IS NOT NULL) THEN
48 l_access_mode := MO_GLOBAL.get_access_mode;
49 l_progress := '30';
50 IF l_access_mode = 'S' THEN
51 l_current_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
52 l_progress := '40';
53 IF ( l_current_org_id IS NULL OR
54 l_current_org_id <> p_org_id) THEN
55 MO_GLOBAL.SET_POLICY_CONTEXT('S', p_org_id);
56 l_progress := '50';
57 END IF;
58 ELSE
59 l_progress := '60';
60 MO_GLOBAL.SET_POLICY_CONTEXT('S', p_org_id);
61 l_progress := '70';
62 END IF;
63 ELSE
64 l_progress := '80';
65 l_ou_count := MO_GLOBAL.GET_OU_COUNT();
66 l_progress := '90';
67 IF (l_ou_count > 1) THEN
68 MO_GLOBAL.SET_POLICY_CONTEXT('M', NULL);
69 l_progress := '99';
70 END IF;
71 END IF;
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF l_debug_level > 0 THEN
75 oe_debug_pub.add(' Set_org_context '||l_progress||' : '||sqlerrm);
76 END IF;
77 RAISE;
78 END Set_Org_Context;
79
80 Procedure Prepare_Notification -- Body definition
81 ( p_header_id IN NUMBER
82 , p_Line_Id_tbl IN Line_id_tbl
83 , p_performer IN VARCHAR2
84 , p_cancel_order IN BOOLEAN
85 , p_notify_for IN VARCHAR2
86 , p_req_header_id IN NUMBER
87 , p_req_line_id IN NUMBER
88 , x_return_status OUT NOCOPY VARCHAR2
89 ) IS
90 --
91 l_line_ids_tbl Line_id_tbl;
92 l_chgord_item_type VARCHAR2(30) := 'OECHGORD'; -- OM Change Order Item Type
93 --
94 l_return_status VARCHAR2(1);
95
96 l_wf_item_key VARCHAR2(240);
97 l_process_name VARCHAR2(30);
98 l_user_name VARCHAR2(255);
99 l_flow_created BOOLEAN := FALSE;
100
101 --
102 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
103 --
104
105 Begin
106
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( ' ENTERING OE_Process_Requisition_Pvt.Prepare_Notification', 1 ) ;
109 oe_debug_pub.add( ' P_Header_id :'||P_Header_id , 5 ) ;
110 oe_debug_pub.add( ' P_Line_id_tbl Count :'||P_Line_id_tbl.count , 5 ) ;
111 oe_debug_pub.add( ' P_performer :'||p_performer,5);
112 oe_debug_pub.add( ' p_notify_for :'||p_notify_for,5);
113 oe_debug_pub.add( ' P_Req_Header_id :'||P_Req_Header_id , 5 ) ;
114 oe_debug_pub.add( ' P_Req_Line_id :'||P_Req_Line_id , 5 ) ;
115 IF P_Cancel_Order THEN
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( ' Header Level cancellation', 5 ) ;
118 END IF;
119 ELSE
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add( ' Not a header Level cancellation', 5 ) ;
122 END IF;
123 END IF;
124 END IF;
125
126 l_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 IF p_notify_for is NULL AND NOT P_cancel_order THEN
129 IF l_debug_level > 0 THEN
130 oe_debug_pub.add( ' RETURN. No notification will be send as nothing has updated on the requisition', 5);
131 END IF;
132 RETURN;
133 END IF;
134
135 -- Generate a unique item key to create a flow
136 select to_char(oe_wf_key_s.nextval) into l_wf_item_key
137 from dual;
138
139 IF l_debug_level > 0 THEN
140 oe_debug_pub.add( ' WF ITEM KEY IS :'|| L_WF_ITEM_KEY ) ;
141 END IF;
142
143 IF P_cancel_order THEN
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'Sending notification for requisition header cancellation', 5);
146 END IF;
147
148 l_process_name := 'ISO_CANCEL';
149
150 -- Create the Notification flow for Header Cancellation
151 WF_ENGINE.CreateProcess(l_chgord_item_type, l_wf_item_key, l_process_name);
152 l_flow_created := TRUE;
153
154 -- Set the resolving role for the notification.
155 WF_Engine.SetItemAttrText( l_chgord_item_type
156 , l_wf_item_key
157 , 'RESOLVING_ROLE'
158 , p_performer);
159
160 -- Set the Sales Order Number
161 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
162 , l_wf_item_key
163 , 'ORDER_NUMBER'
164 , g_sales_order_number);
165
166 -- Set the Requisition Header Number
167 WF_ENGINE.SetItemAttrText( l_chgord_item_type
168 , l_wf_item_key
169 , 'REQ_HDR_NUMBER'
170 , g_requisition_number);
171
172 -- Set the Sales Order Cancellation Date
173 WF_Engine.SetItemAttrDate( l_chgord_item_type
174 , l_wf_item_key
175 , 'ORDER_CANCEL_DATE'
176 , g_order_cancellation_date);
177
178 /* -- Set the Sales Order cancellation Reason
179 WF_Engine.SetItemAttrText( l_chgord_item_type
180 , l_wf_item_key
181 , 'REASON'
182 , g_reason);
183 */
184 ELSIF p_notify_for = 'Q' THEN
185 IF l_debug_level > 0 THEN
186 oe_debug_pub.add( 'Sending notification for requisition line quantity update', 5);
187 END IF;
188
189 l_process_name := 'ISO_QTY_UPDATE';
190
191 -- Create the Notification flow for Line Quantity update
192 WF_ENGINE.CreateProcess(l_chgord_item_type, l_wf_item_key, l_process_name);
193 l_flow_created := TRUE;
194
195 -- Set the resolving role for the notification.
196 WF_Engine.SetItemAttrText( l_chgord_item_type
197 , l_wf_item_key
198 , 'RESOLVING_ROLE'
199 , p_performer);
200
201 -- Set the Sales Order Number
202 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
203 , l_wf_item_key
204 , 'ORDER_NUMBER'
205 , g_sales_order_number);
206
207 -- Set the Sales Line Number
208 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
209 , l_wf_item_key
210 , 'LINE_NUMBER'
211 , g_sales_ord_line_num);
212
213 -- Set the Requisition Header Number
214 WF_ENGINE.SetItemAttrText( l_chgord_item_type
215 , l_wf_item_key
216 , 'REQ_HDR_NUMBER'
217 , g_requisition_number);
218
219 -- Set the Requisition Line Number
220 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
221 , l_wf_item_key
222 , 'REQ_LINE_NUMBER'
223 , g_requisition_line_number);
224
225 -- Set the Sales Order Line Inventory Item Name
226 WF_Engine.SetItemAttrText( l_chgord_item_type
227 , l_wf_item_key
228 , 'INVENTORY_ITEM'
229 , g_inventory_item_name);
230
231 -- Set the Requisition Line Requested Quantity
232 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
233 , l_wf_item_key
234 , 'REQUESTED_QTY'
235 , g_requested_quantity);
236
237 -- Set the Sales Order Line updated Quantity
238 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
239 , l_wf_item_key
240 , 'UPDATED_QTY'
241 , g_updated_quantity);
242
243 -- Set the Sales Order Line update Date
244 WF_Engine.SetItemAttrDate( l_chgord_item_type
245 , l_wf_item_key
246 , 'LINE_UPDATE_DATE'
247 , g_line_updated_date);
248
249 --Bug 14211120 Start
250 -- Set the Requisition Line Secondary Requested Quantity
251 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
252 , l_wf_item_key
253 , 'REQUESTED_QTY2'
254 , g_requested_quantity2);
255
256 -- Set the Sales Order Line Secondary updated Quantity
257 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
258 , l_wf_item_key
259 , 'UPDATED_QTY2'
260 , g_updated_quantity2);
261 --Bug 14211120 End
262
263 /* -- Set the Sales Order cancellation Reason
264 WF_Engine.SetItemAttrText( l_chgord_item_type
265 , l_wf_item_key
266 , 'REASON'
267 , g_reason);
268 */
269 ELSIF p_notify_for = 'D' THEN
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add( 'Sending notification for requisition line date update', 5);
272 END IF;
273
274 l_process_name := 'ISO_SCH_DATE_UPDATE';
275
276 -- Create the Notification flow for Line Schedule Ship Date update
277 WF_ENGINE.CreateProcess(l_chgord_item_type, l_wf_item_key, l_process_name);
278 l_flow_created := TRUE;
279
280 -- Set the resolving role for the notification.
281 WF_Engine.SetItemAttrText( l_chgord_item_type
282 , l_wf_item_key
283 , 'RESOLVING_ROLE'
284 , p_performer);
285
286 -- Set the Sales Order Number
287 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
288 , l_wf_item_key
289 , 'ORDER_NUMBER'
290 , g_sales_order_number);
291
292 -- Set the Sales Line Number
293 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
294 , l_wf_item_key
295 , 'LINE_NUMBER'
296 , g_sales_ord_line_num);
297
298 -- Set the Requisition Header Number
299 WF_ENGINE.SetItemAttrText( l_chgord_item_type
300 , l_wf_item_key
301 , 'REQ_HDR_NUMBER'
302 , g_requisition_number);
303
304 -- Set the Requisition Line Number
305 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
306 , l_wf_item_key
307 , 'REQ_LINE_NUMBER'
308 , g_requisition_line_number);
309
310 -- Set the Sales Order Line Inventory Item Name
311 WF_Engine.SetItemAttrText( l_chgord_item_type
312 , l_wf_item_key
313 , 'INVENTORY_ITEM'
314 , g_inventory_item_name);
315
316 -- Set the Requisition Line Requested Quantity
317 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
318 , l_wf_item_key
319 , 'REQUESTED_QTY'
320 , g_requested_quantity);
321
322 -- Set the Requisition Line Need By Date
323 WF_Engine.SetItemAttrDate( l_chgord_item_type
324 , l_wf_item_key
325 , 'REQ_LIN_NEED_BY_DATE'
326 , g_need_by_date);
327
328 -- Set the Sales Order Line Schedule Ship Date
329 WF_Engine.SetItemAttrDate( l_chgord_item_type
330 , l_wf_item_key
331 , 'LINE_SCH_ARRIVAL_DATE'
332 , g_schedule_arrival_date);
333
334 -- Set the Sales Order Line update Date
335 WF_Engine.SetItemAttrDate( l_chgord_item_type
336 , l_wf_item_key
337 , 'LINE_UPDATE_DATE'
338 , g_line_updated_date);
339
340 /* -- Set the Sales Order cancellation Reason
341 WF_Engine.SetItemAttrText( l_chgord_item_type
342 , l_wf_item_key
343 , 'REASON'
344 , g_reason);
345 */
346 ELSIF p_notify_for = 'B' THEN
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add( 'Sending notification for requisition line quantity and date update', 5);
349 END IF;
350
351 l_process_name := 'ISO_QTY_SCH_DATE_UPDATE';
352
353 -- Create the Notification flow for Line Quantity and Schedule Ship Date update
354 WF_ENGINE.CreateProcess(l_chgord_item_type, l_wf_item_key, l_process_name);
355 l_flow_created := TRUE;
356
357 -- Set the resolving role for the notification.
358 WF_Engine.SetItemAttrText( l_chgord_item_type
359 , l_wf_item_key
360 , 'RESOLVING_ROLE'
361 , p_performer);
362
363 -- Set the Sales Order Number
364 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
365 , l_wf_item_key
366 , 'ORDER_NUMBER'
367 , g_sales_order_number);
368
369 -- Set the Sales Line Number
370 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
371 , l_wf_item_key
372 , 'LINE_NUMBER'
373 , g_sales_ord_line_num);
374
375 -- Set the Requisition Header Number
376 WF_ENGINE.SetItemAttrText( l_chgord_item_type
377 , l_wf_item_key
378 , 'REQ_HDR_NUMBER'
379 , g_requisition_number);
380
381 -- Set the Requisition Line Number
382 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
383 , l_wf_item_key
384 , 'REQ_LINE_NUMBER'
385 , g_requisition_line_number);
386
387 -- Set the Sales Order Line Inventory Item Name
388 WF_Engine.SetItemAttrText( l_chgord_item_type
389 , l_wf_item_key
390 , 'INVENTORY_ITEM'
391 , g_inventory_item_name);
392
393 -- Set the Requisition Line Requested Quantity
394 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
395 , l_wf_item_key
396 , 'REQUESTED_QTY'
397 , g_requested_quantity);
398
399 -- Set the Sales Order Line updated Quantity
400 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
401 , l_wf_item_key
402 , 'UPDATED_QTY'
403 , g_updated_quantity);
404
405 -- Set the Requisition Line Need By Date
406 WF_Engine.SetItemAttrDate( l_chgord_item_type
407 , l_wf_item_key
408 , 'REQ_LIN_NEED_BY_DATE'
409 , g_need_by_date);
410
411 -- Set the Sales Order Line Schedule Ship Date
412 WF_Engine.SetItemAttrDate( l_chgord_item_type
413 , l_wf_item_key
414 , 'LINE_SCH_ARRIVAL_DATE'
415 , g_schedule_arrival_date);
416
417 -- Set the Sales Order Line update Date
418 WF_Engine.SetItemAttrDate( l_chgord_item_type
419 , l_wf_item_key
420 , 'LINE_UPDATE_DATE'
421 , g_line_updated_date);
422
423 --Bug 14211120 Start
424 -- Set the Requisition Line Secondary Requested Quantity
425 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
426 , l_wf_item_key
427 , 'REQUESTED_QTY2'
428 , g_requested_quantity2);
429
430 -- Set the Sales Order Line Secondary updated Quantity
431 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
432 , l_wf_item_key
433 , 'UPDATED_QTY2'
434 , g_updated_quantity2);
435 --Bug 14211120 End
436
437 /* -- Set the Sales Order cancellation Reason
438 WF_Engine.SetItemAttrText( l_chgord_item_type
439 , l_wf_item_key
440 , 'REASON'
441 , g_reason);
442 */
443 ELSIF p_notify_for = 'C' THEN
444 IF l_debug_level > 0 THEN
445 oe_debug_pub.add( 'Sending notification for requisition line cancellation', 5);
446 END IF;
447
448 l_process_name := 'ISO_LINE_CANCEL';
449
450 -- Create the Notification flow for Line Cancellation
451 WF_ENGINE.CreateProcess(l_chgord_item_type, l_wf_item_key, l_process_name);
452 l_flow_created := TRUE;
453
454 -- Set the resolving role for the notification.
455 WF_Engine.SetItemAttrText( l_chgord_item_type
456 , l_wf_item_key
457 , 'RESOLVING_ROLE'
458 , p_performer);
459
460 -- Set the Sales Order Number
461 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
462 , l_wf_item_key
463 , 'ORDER_NUMBER'
464 , g_sales_order_number);
465
466 -- Set the Sales Line Number
467 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
468 , l_wf_item_key
469 , 'LINE_NUMBER'
470 , g_sales_ord_line_num);
471
472 -- Set the Requisition Header Number
473 WF_ENGINE.SetItemAttrText( l_chgord_item_type
474 , l_wf_item_key
475 , 'REQ_HDR_NUMBER'
476 , g_requisition_number);
477
478 -- Set the Requisition Line Number
479 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
480 , l_wf_item_key
481 , 'REQ_LINE_NUMBER'
482 , g_requisition_line_number);
483
484 -- Set the Sales Order Line Inventory Item Name
485 WF_Engine.SetItemAttrText( l_chgord_item_type
486 , l_wf_item_key
487 , 'INVENTORY_ITEM'
488 , g_inventory_item_name);
489
490 -- Set the Requisition Line Requested Quantity
491 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
492 , l_wf_item_key
493 , 'REQUESTED_QTY'
494 , g_requested_quantity);
495
496 -- Set the Sales Order Line Cancelled Quantity
497 WF_ENGINE.SetItemAttrNumber( l_chgord_item_type
498 , l_wf_item_key
499 , 'CANCELLED_QTY'
500 , g_ISO_cancelled_quantity);
501
502 -- Set the Sales Order Line Cancel Date
503 WF_Engine.SetItemAttrDate( l_chgord_item_type
504 , l_wf_item_key
505 , 'LINE_CANCEL_DATE'
506 , g_line_cancellation_date);
507
508 /* -- Set the Sales Order cancellation Reason
509 WF_Engine.SetItemAttrText( l_chgord_item_type
510 , l_wf_item_key
511 , 'REASON'
512 , g_reason);
513 */
514 END IF;
515
516 IF l_flow_created THEN
517
518 IF l_debug_level > 0 THEN
519 oe_debug_pub.add(' Starting the workflow process for sending the notification', 5);
520 END IF;
521 BEGIN
522 select user_name
523 into l_user_name
524 from fnd_user
525 where user_id = FND_GLOBAL.USER_ID;
526 EXCEPTION
527 WHEN OTHERS THEN
528 l_user_name := null; -- do not set FROM_ROLE then
529 END;
530
531 IF (l_user_name is not NULL) THEN
532 WF_ENGINE.SetItemAttrText( l_chgord_item_type
533 , l_wf_item_key
534 , 'NOTIFICATION_FROM_ROLE'
535 , l_user_name);
536 END IF;
537
538 WF_ENGINE.StartProcess(l_chgord_item_type, l_wf_item_key);
539 IF l_debug_level > 0 THEN
540 oe_debug_pub.add(' Workflow process is successfully started',5);
541 END IF;
542 END IF;
543
544 x_return_status := l_return_status;
545
546 -- OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
547 -- P_Data => x_msg_Data);
548
549 IF l_debug_level > 0 THEN
550 oe_debug_pub.add( 'EXITING OE_Process_Requisition_Pvt.Prepare_Notification', 1 ) ;
551 END IF;
552
553 Exception
554 WHEN FND_API.G_EXC_ERROR THEN
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 -- OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
557 -- P_Data => x_msg_Data);
558
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 -- OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
562 -- P_Data => x_msg_Data);
563
564 WHEN OTHERS THEN
565 oe_debug_pub.add( ' When Others of OE_Process_Requisition_Pvt.Prepare_Notification '||sqlerrm,1);
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
568 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Prepare_Notification');
569 -- Pkg Body global variable = OE_Process_Requisition_Pvt
570 END IF;
571 -- OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
572 -- P_Data => x_msg_Data);
573
574 End Prepare_Notification;
575
576 Procedure Update_Internal_Requisition -- Body definition
577 ( P_Header_id IN NUMBER
578 , P_Line_id IN NUMBER
579 , P_Line_ids IN VARCHAR2
580 , p_num_records IN NUMBER
581 , P_Req_Header_id IN NUMBER
582 , P_Req_Line_id IN NUMBER
583 , P_Quantity_Change IN NUMBER
584 , P_Quantity2_Change IN NUMBER --Bug 14211120
585 , P_New_Schedule_Ship_Date IN DATE
586 , P_Cancel_Order IN BOOLEAN
587 , P_Cancel_Line IN BOOLEAN
588 , X_msg_count OUT NOCOPY NUMBER
589 , X_msg_data OUT NOCOPY VARCHAR2
590 , X_return_status OUT NOCOPY VARCHAR2
591 ) IS
592 --
593 -- TYPE Line_id_tbl_TYPE is TABLE OF NUMBER;
594 l_line_ids_tbl Line_id_tbl := Line_id_tbl();
595 --
596 l_return_status VARCHAR2(1);
597 l_create_notification BOOLEAN := FALSE;
598 l_access_mode VARCHAR2(1) := mo_global.Get_access_mode();
599 l_original_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
600 -- hr_all_organization_units_tl.organization_id%TYPE
601 -- If context is SINGLE, returns current session ORG_ID
602 -- If context is MULTI, returns NULL
603 l_target_org_id hr_all_organization_units_tl.organization_id%TYPE;
604
605 l_Req_Line_NeedByDate DATE;
606 l_New_Schedule_Ship_Date DATE;
607 l_New_Schedule_Arrival_Date DATE;
608 l_preparer_name VARCHAR2(150);
609 l_call_po_api_for_update BOOLEAN := FALSE;
610 l_line_id NUMBER;
611 L_change VARCHAR2(1) := NULL;
612 l_index NUMBER := 0;
613 l_line_exists BOOLEAN := FALSE;
614 l_nxt_position integer;
615 l_initial integer;
616 J integer;
617 --l_separator VARCHAR2(1); -- Added for bug 8920840
618 -- Commented for bug 8974535
619 --
620
621 l_req_line_id_tbl Dbms_Sql.number_table;
622 l_req_can_qty_tbl Dbms_Sql.number_table; -- Bug12970870
623 l_req_can_qty2_tbl Dbms_Sql.number_table; --Bug 14211120 Secondary cancel qty
624
625 CURSOR Cancel_Lines IS
626 select source_document_line_id
627 from oe_order_lines_all
628 where header_id = p_header_id
629 and open_flag = 'N'
630 and nvl(cancelled_flag, 'N') = 'Y'; -- Cursor added for 8583903
631 --
632
633 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
634 --
635
636 Begin
637
638 IF l_debug_level > 0 THEN
639 oe_debug_pub.add( 'ENTERING OE_Process_Requisition_Pvt.Update_Internal_Requisition', 1 ) ;
640 oe_debug_pub.add( ' P_Header_id :'||P_Header_id , 5 ) ;
641 oe_debug_pub.add( ' P_Line_id :'||P_Line_id , 5 ) ;
642 oe_debug_pub.add( ' P_Num_Records :'||p_num_records,5);
643 oe_debug_pub.add( ' P_Req_Header_id :'||P_Req_Header_id , 5 ) ;
644 oe_debug_pub.add( ' P_Req_Line_id :'||P_Req_Line_id , 5 ) ;
645 oe_debug_pub.add( ' P_Quantity_Change :'||P_Quantity_Change, 5 );
646 oe_debug_pub.add( ' P_Quantity2_Change :'||P_Quantity2_Change, 5 );
647 oe_debug_pub.add( ' P_New_Schedule_Ship_Date :'||P_New_Schedule_Ship_Date, 5 ) ;
648 IF P_Cancel_Order THEN
649 oe_debug_pub.add( ' Header level cancellation',5);
650 ELSE
651 oe_debug_pub.add( ' Not a header level cancellation',5);
652 END IF;
653 IF P_Cancel_Line THEN
654 oe_debug_pub.add( ' Line level cancellation',5);
655 ELSE
656 oe_debug_pub.add( ' Not a line level cancellation',5);
657 END IF;
658 END IF;
659
660 l_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 IF NOT P_Cancel_Line and NOT P_Cancel_Order
663 AND (P_Quantity_Change is null OR P_Quantity_Change = 0)
664 AND (P_Quantity2_Change is null OR P_Quantity2_Change = 0) --Bug 14211120
665 AND P_New_Schedule_Ship_Date is null THEN
666 IF l_debug_level > 0 THEN
667 oe_debug_pub.add( ' Nothing to update on Requisition', 5 ) ;
668 END IF;
669 -- RAISE FND_API.G_EXC_ERROR;
670 x_return_status := l_return_status;
671 RETURN;
672 END IF;
673
674 -- Retrieve the calling application operating unit.
675 SELECT org_id, segment1
676 INTO l_target_org_id, g_requisition_number
677 FROM po_requisition_Headers_all
678 WHERE requisition_header_id = p_req_Header_id;
679
680 IF l_debug_level > 0 THEN
681 oe_debug_pub.add( ' OE_GLOBALS.G_REASON_CODE for this change is '||OE_GLOBALS.G_REASON_CODE,5);
682 END IF;
683
684 IF P_Cancel_Order THEN
685 IF l_debug_level > 0 THEN
686 oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqCancel_from_SO', 1 ) ;
687 oe_debug_pub.add( ' Cancelling Requisition Header', 1 ) ;
688 END IF;
689
690 select order_number, last_update_date
691 into g_sales_order_number, g_order_cancellation_date
692 from oe_order_headers_all
693 where header_id = p_header_id;
694
695 g_reason := OE_GLOBALS.G_REASON_CODE;
696 -- 'OE_GLOBALS.G_REASON_COMMENTS'
697
698 -- Added for 8583903
699 open Cancel_Lines;
700 loop
701 EXIT WHEN Cancel_Lines%NOTFOUND;
702 FETCH Cancel_Lines BULK COLLECT INTO l_req_line_id_tbl;
703 end loop;
704 close Cancel_Lines;
705
706 SET_ORG_CONTEXT(l_target_org_id);
707
708 PO_RCO_Validation_GRP.Update_ReqCancel_from_SO
709 ( p_api_version => 4.0 -- Bug12970870, 14211120
710 , p_req_line_id_tbl => l_req_line_id_tbl
711 --, p_req_can_qty_tbl => l_req_can_qty_tbl -- Bug12970870
712 , p_req_can_prim_qty_tbl => l_req_can_qty_tbl -- Bug 14211120
713 , p_req_can_sec_qty_tbl => l_req_can_qty2_tbl -- Bug 14211120
714 , p_req_can_all => TRUE -- Bug 12970870
715 -- , p_req_line_id => NULL -- Header Level Cancellation -- Api Modified for 8583903
716 -- , p_req_hdr_id => P_Req_Header_id
717 , x_return_status => l_return_status
718 );
719
720 SET_ORG_CONTEXT(l_original_org_id);
721
722 IF l_debug_level > 0 THEN
723 oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqCancel_from_SO '||l_return_status, 1 ) ;
724 END IF;
725
726 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
727 -- Sales order and corresponding internal requisition has been cancelled
728 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_CANCEL');
729 OE_MSG_PUB.Add;
730 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
731 -- Unable to save the changes as the corresponding Internal Requisition can not be updated
732 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_FAIL');
733 OE_MSG_PUB.Add;
734 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
735 RAISE FND_API.G_EXC_ERROR;
736 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 END IF;
739 END IF;
740
741 l_create_notification := TRUE;
742
743 ELSIF P_Cancel_Line THEN
744 IF l_debug_level > 0 THEN
745 oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqCancel_from_SO', 1 ) ;
746 oe_debug_pub.add( ' Cancelling Requisition Line', 1 ) ;
747 END IF;
748
749 select order_number
750 into g_sales_order_number
751 from oe_order_headers_all
752 where header_id = p_header_id;
753
754 -- Added for bug #8920840
755 -- select SubStr(Value,-1,1) into l_separator from v$parameter
756 -- where name = 'nls_numeric_characters'; -- Commented for bug 8974535
757
758 -- select line_number||'.'||shipment_number
759 -- select line_number||l_separator||shipment_number -- Commented for bug 8974535
760 -- Modified for bug #8920840
761 select line_number+(shipment_number/power(10,length(shipment_number))) as line_num
762 -- Added for bug #8974535
763 , nvl(cancelled_quantity,0)
764 , last_update_date
765 into g_sales_ord_line_num
766 , g_ISO_cancelled_quantity
767 , g_line_cancellation_date
768 from oe_order_lines_all
769 where line_id = p_line_id;
770
771 select items.concatenated_segments
772 , prl.line_num
773 , prl.quantity
774 , prl.secondary_quantity --Bug 14211120
775 into g_inventory_item_name
776 , g_requisition_line_number
777 , g_requested_quantity
778 , g_requested_quantity2 --Bug 14211120
779 from mtl_system_items_b_kfv items
780 , po_requisition_lines_all prl
781 , financials_system_params_all fsp
782 where fsp.org_id = prl.org_id
783 and fsp.inventory_organization_id = items.organization_id
784 and prl.item_id = items.inventory_item_id
785 and prl.requisition_line_id = P_Req_Line_id;
786
787 g_reason := OE_GLOBALS.G_REASON_CODE;
788 -- 'OE_GLOBALS.G_REASON_COMMENTS'
789
790 -- Added for 8583903
791 l_req_line_id_tbl(1) := p_req_line_id;
792 l_req_can_qty_tbl(1) := -P_Quantity_Change ; --Bug12970870
793 l_req_can_qty2_tbl(1) := -P_Quantity2_Change ; --Bug 14211120
794
795 SET_ORG_CONTEXT(l_target_org_id);
796
797 PO_RCO_Validation_GRP.Update_ReqCancel_from_SO
798 ( p_api_version => 4.0 --Bug12970870 14211120
799 , p_req_line_id_tbl => l_req_line_id_tbl
800 --, p_req_can_qty_tbl => l_req_can_qty_tbl -- Bug12970870
801 , p_req_can_prim_qty_tbl => l_req_can_qty_tbl -- Bug 14211120
802 , p_req_can_sec_qty_tbl => l_req_can_qty2_tbl -- Bug 14211120
803 , p_req_can_all => FALSE --Bug12970870
804 -- , p_req_line_id => P_Req_Line_id
805 -- , p_req_hdr_id => P_Req_Header_id -- Api modified for 8583903
806 , x_return_status => l_return_status
807 );
808
809 SET_ORG_CONTEXT(l_original_org_id);
810
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqCancel_from_SO '||l_return_status, 5 ) ;
813 END IF;
814
815 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
816 -- Sales order and corresponding internal requisition line has been cancelled
817 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_LIN_CANCEL');
818 OE_MSG_PUB.Add;
819 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
820 -- Unable to save the changes as the corresponding Internal Requisition can not be updated
821 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_FAIL');
822 OE_MSG_PUB.Add;
823 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
824 RAISE FND_API.G_EXC_ERROR;
825 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828 END IF;
829
830 L_change := 'C'; -- Line cancellation
831 l_create_notification := TRUE;
832
833 ELSIF (P_Quantity_Change IS NOT NULL AND P_Quantity_Change <> 0)
834 OR (P_Quantity2_Change IS NOT NULL AND P_Quantity2_Change <> 0) --Bug 14211120
835 OR (P_New_Schedule_Ship_Date IS NOT NULL) THEN
836
837 IF l_debug_level > 0 THEN
838 oe_debug_pub.add( ' Quantity OR Date changed. Req Line has to be updated', 5 ) ;
839 END IF;
840
841 select order_number
842 into g_sales_order_number
843 from oe_order_headers_all
844 where header_id = p_header_id;
845
846 -- Added for bug #8920840
847 -- select SubStr(Value,-1,1) into l_separator from v$parameter
848 -- where name = 'nls_numeric_characters'; -- Commented for bug 8974535
849
850 -- select line_number||'.'||shipment_number
851 -- select line_number||l_separator||shipment_number -- Commented for bug 8974535
852 -- Modified for bug #8920840
853 select line_number+(shipment_number/power(10,length(shipment_number))) as line_num
854 -- Added for bug 8974535
855 -- , ordered_quantity
856 , schedule_ship_date
857 , schedule_arrival_date
858 , last_update_date
859 into g_sales_ord_line_num
860 -- , g_updated_quantity
861 , g_schedule_ship_date
862 , g_schedule_arrival_date
863 , g_line_updated_date
864 from oe_order_lines_all
865 where line_id = p_line_id;
866
867
868 select items.concatenated_segments
869 , prl.line_num
870 , prl.quantity
871 , prl.secondary_quantity --Bug 14211120
872 , prl.need_by_date
873 , prl.need_by_date
874 into g_inventory_item_name
875 , g_requisition_line_number
876 , g_requested_quantity
877 , g_requested_quantity2 --Bug 14211120
878 , g_need_by_date
879 , l_Req_Line_NeedByDate
880 from mtl_system_items_b_kfv items
881 , po_requisition_lines_all prl
882 , financials_system_params_all fsp
883 where fsp.org_id = prl.org_id
884 and fsp.inventory_organization_id = items.organization_id
885 and prl.item_id = items.inventory_item_id
886 and prl.requisition_line_id = P_Req_Line_id;
887
888 g_updated_quantity := g_requested_quantity + NVL(P_Quantity_Change,0);
889 g_updated_quantity2 := g_requested_quantity2 + NVL(P_Quantity2_Change,0); --Bug 14211120
890 -- Added NVL for bug 8920840
891
892 IF l_debug_level > 0 THEN
893 oe_debug_pub.add( ' Need By Date on Req Line is '||l_Req_Line_NeedByDate, 5 ) ;
894 END IF;
895
896 --IF (P_Quantity_Change IS NOT NULL AND P_Quantity_Change <> 0)
897 --AND (P_New_Schedule_Ship_Date IS NOT NULL) THEN
898 --Bug 14211120
899 IF ((P_Quantity_Change IS NOT NULL AND P_Quantity_Change <> 0)
900 OR (P_Quantity2_Change IS NOT NULL AND P_Quantity2_Change <> 0))
901 AND (P_New_Schedule_Ship_Date IS NOT NULL) THEN
902
903
904 -- IF oe_globals.equal(P_New_Schedule_Ship_Date , l_Req_Line_NeedByDate) THEN
905 IF oe_globals.equal(g_schedule_arrival_date, l_Req_Line_NeedByDate) THEN
906 l_New_Schedule_Ship_Date := NULL;
907 g_schedule_ship_date := NULL;
908 g_schedule_arrival_date := NULL;
909 IF l_debug_level > 0 THEN
910 oe_debug_pub.add( ' Ordered Quantity is changed. Update Req', 5 ) ;
911 END IF;
912 L_change := 'Q'; -- Quantity change
913 l_call_po_api_for_update := TRUE;
914 ELSE
915 l_New_Schedule_Arrival_Date := g_schedule_arrival_date;
916 -- l_New_Schedule_Ship_Date := P_New_Schedule_Ship_Date;
917 IF l_debug_level > 0 THEN
918 oe_debug_pub.add( ' Ordered Quantity and Schedule Ship/Arrival Date are changed. Update Req', 5 ) ;
919 END IF;
920 L_change := 'B'; -- Both Date and Quantity change
921 l_call_po_api_for_update := TRUE;
922 END IF;
923
924 --Bug 14211120
925 --ELSIF P_Quantity_Change IS NOT NULL AND P_Quantity_Change <> 0 THEN
926 ELSIF (P_Quantity_Change IS NOT NULL AND P_Quantity_Change <> 0) OR
927 (P_Quantity2_Change IS NOT NULL AND P_Quantity2_Change <> 0) THEN
928 IF l_debug_level > 0 THEN
929 oe_debug_pub.add( ' Ordered Quantity is changed. Update Req', 5 ) ;
930 END IF;
931 L_change := 'Q'; -- Quantity change
932 g_schedule_ship_date := NULL;
933 g_schedule_arrival_date := NULL;
934 l_call_po_api_for_update := TRUE;
935
936 ELSIF P_New_Schedule_Ship_Date IS NOT NULL THEN
937 -- IF oe_globals.equal(P_New_Schedule_Ship_Date , l_Req_Line_NeedByDate) THEN
938 IF oe_globals.equal(g_schedule_arrival_date, l_Req_Line_NeedByDate) THEN
939 l_New_Schedule_Ship_Date := NULL;
940 g_schedule_ship_date := NULL;
941 g_schedule_arrival_date := NULL;
942 g_updated_quantity := NULL;
943 l_call_po_api_for_update := FALSE;
944 l_change := NULL; -- No date or quantity change
945
946 IF l_debug_level > 0 THEN
947 oe_debug_pub.add( ' Neither Quantity nor date has changed', 5 ) ;
948 END IF;
949 ELSE
950 -- l_New_Schedule_Ship_Date := P_New_Schedule_Ship_Date;
951 l_New_Schedule_Arrival_Date := g_schedule_arrival_date;
952 IF l_debug_level > 0 THEN
953 oe_debug_pub.add( ' Schedule Ship/Arrival Date is changed. Update Req', 5 ) ;
954 END IF;
955 g_updated_quantity := NULL;
956 L_change := 'D'; -- Date Change
957 l_call_po_api_for_update := TRUE;
958 END IF;
959
960 END IF; -- Quantity or Date change
961
962 IF l_call_po_api_for_update THEN
963
964 IF l_debug_level > 0 THEN
965 oe_debug_pub.add( ' Calling PO_RCO_Validation_GRP.Update_ReqChange_from_SO', 1 ) ;
966 END IF;
967
968 SET_ORG_CONTEXT(l_target_org_id);
969
970 PO_RCO_Validation_GRP.Update_ReqChange_from_SO
971 ( p_api_version => 3.0 --Bug 14211120
972 , p_req_line_id => P_Req_Line_id
973 --, p_delta_quantity => P_Quantity_Change --Bug 14211120
974 , p_delta_quantity_prim => P_Quantity_Change --Bug 14211120
975 , p_delta_quantity_sec => P_Quantity2_Change --Bug 14211120
976 , p_new_need_by_date => l_New_Schedule_Arrival_Date -- l_New_Schedule_Ship_Date
977 , x_return_status => l_return_status
978 );
979
980 SET_ORG_CONTEXT(l_original_org_id);
981
982 IF l_debug_level > 0 THEN
983 oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Update_ReqChange_from_SO '||l_return_status, 1);
984 END IF;
985
986 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
987 IF L_change = 'Q' THEN -- Q => Quantity
988 -- Ordered quantity, Supply picture, and corresponding internal
989 -- requisition line have been updated
990 g_reason := OE_GLOBALS.G_REASON_CODE;
991 -- 'OE_GLOBALS.G_REASON_COMMENTS'
992 FND_Message.Set_Name('ONT', 'OE_IRCMS_QTY_UDPATE');
993 OE_MSG_PUB.Add;
994 ELSIF L_change = 'D' THEN -- D => Date
995 -- Schedule Ship/Arrival date and corresponding internal
996 -- requisition line have been updated
997 g_reason := OE_GLOBALS.G_REASON_CODE;
998 -- 'OE_GLOBALS.G_REASON_COMMENTS'
999 FND_Message.Set_Name('ONT', 'OE_IRCMS_DATE_UDPATE');
1000 OE_MSG_PUB.Add;
1001 ELSIF L_change = 'B' THEN -- B => Both Date and Quantity
1002 -- Schedule Ship/Arrival date, Ordered quantity, Supply picture,
1003 -- and corresponding internal requisition line have been updated
1004 g_reason := OE_GLOBALS.G_REASON_CODE;
1005 -- 'OE_GLOBALS.G_REASON_COMMENTS'
1006 FND_Message.Set_Name('ONT', 'OE_IRCMS_QTY_DATE_UDPATE');
1007 OE_MSG_PUB.Add;
1008 END IF;
1009 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1010 -- Unable to save the changes as the corresponding Internal Requisition can not be updated
1011 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_FAIL');
1012 OE_MSG_PUB.Add;
1013 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1014 RAISE FND_API.G_EXC_ERROR;
1015 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1017 END IF;
1018 END IF;
1019
1020 l_create_notification := TRUE;
1021 ELSE
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.add( ' Update_ReqChange_from_SO is not called as there is no valid change to process', 1);
1024 END IF;
1025 END IF;
1026 END IF; -- p_cancel_order
1027
1028 IF OE_Schedule_GRP.G_ISO_Planning_Update THEN
1029 IF l_debug_level > 0 THEN
1030 oe_debug_pub.add(' The caller for this change is Planning. Hence notification will not be send',5);
1031 END IF;
1032 l_create_notification := FALSE;
1033 END IF;
1034
1035 IF l_create_notification THEN
1036 IF l_debug_level > 0 THEN
1037 oe_debug_pub.add( ' Preparing Notification ', 1 ) ;
1038 END IF;
1039
1040 IF P_Cancel_Order THEN
1041 IF p_num_records > 0 THEN
1042 J := 1;
1043 l_initial := 1;
1044 l_nxt_position := INSTR(p_line_ids,',',1,J);
1045
1046 FOR I IN 1 .. p_num_records LOOP
1047 l_line_id := to_number(substr(p_line_ids,l_initial, l_nxt_position - l_initial));
1048 IF l_line_ids_tbl.COUNT > 0 THEN
1049 l_line_exists := FALSE;
1050 FOR k IN l_line_ids_tbl.FIRST .. l_line_ids_tbl.LAST LOOP
1051 IF l_line_ids_tbl(k) = l_line_id THEN
1052 l_line_exists := TRUE;
1053 EXIT;
1054 END IF;
1055 END LOOP;
1056 END IF;
1057 IF NOT l_line_exists THEN
1058 l_line_ids_tbl.Extend(1);
1059 l_index := l_index + 1;
1060 l_line_ids_tbl(l_index) := l_line_id;
1061 END IF;
1062
1063 l_initial := l_nxt_position + 1;
1064 j := j + 1;
1065 l_nxt_position := INSTR(p_line_ids,',',1,J);
1066 END LOOP;
1067 END IF;
1068 ELSE
1069 l_line_ids_tbl.Extend(1);
1070 l_line_ids_tbl(1) := p_line_id;
1071 IF l_debug_level >0 THEN
1072 oe_debug_pub.add(' Not a header cancellation. Line is : '||l_line_ids_tbl(1));
1073 END IF;
1074 END IF;
1075
1076 SET_ORG_CONTEXT(l_target_org_id);
1077
1078 PO_RCO_Validation_GRP.Get_Preparer_Name
1079 ( P_API_Version => 1.0
1080 , P_Req_Hdr_id => p_req_header_id
1081 , x_return_status => l_return_status
1082 , x_preparer_name => l_preparer_name -- Notification performer
1083 );
1084
1085 SET_ORG_CONTEXT(l_original_org_id);
1086
1087 IF l_debug_level > 0 THEN
1088 oe_debug_pub.add( ' After PO_RCO_Validation_GRP.Get_Preparer_Name '||l_return_status, 1 ) ;
1089 END IF;
1090
1091 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1092 -- Unable to save the changes as the corresponding Internal Requisition can not be updated
1093 FND_Message.Set_Name('ONT', 'OE_IRCMS_REQ_FAIL');
1094 OE_MSG_PUB.Add;
1095 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1096 RAISE FND_API.G_EXC_ERROR;
1097 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1099 END IF;
1100 END IF;
1101
1102 IF l_preparer_name IS NULL THEN
1103 IF l_debug_level > 0 THEN
1104 oe_debug_pub.add( ' The preparer name is null. Setting it to SYSADMIN ',1);
1105 END IF;
1106 l_preparer_name := 'SYSADMIN';
1107 ELSE
1108 IF l_debug_level > 0 THEN
1109 oe_debug_pub.add( ' The preparer name is '||l_preparer_name);
1110 END IF;
1111 END IF;
1112
1113 Prepare_Notification
1114 ( p_Line_Id_tbl => l_line_ids_tbl
1115 , p_performer => l_preparer_name
1116 , p_cancel_order => p_cancel_order
1117 , p_notify_for => l_change -- (C for Line cancellation, D for Date change, Q for quantity change,
1118 -- B for both Quantity and Date change, NULL for nothing updated. No
1119 -- notification)
1120 , p_header_id => p_header_id
1121 , p_req_header_id => p_req_header_id
1122 , p_req_line_id => p_req_line_id
1123 , x_return_status => l_return_status
1124 );
1125
1126 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1127 RAISE FND_API.G_EXC_ERROR;
1128 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 END IF;
1131
1132 END IF;
1133
1134 -- Setting all package body level globals to NULL;
1135 g_requisition_number := NULL;
1136 g_requisition_line_number := NULL;
1137 g_need_by_date := NULL;
1138 g_requested_quantity := NULL;
1139 g_sales_order_number := NULL;
1140 g_order_cancellation_date := NULL;
1141 g_sales_ord_line_num := NULL;
1142 g_line_cancellation_date := NULL;
1143 g_ISO_cancelled_quantity := NULL;
1144 g_inventory_item_name := NULL;
1145 g_updated_quantity := NULL;
1146 g_line_updated_date := NULL;
1147 g_schedule_ship_date := NULL;
1148 g_schedule_arrival_date := NULL;
1149 g_reason := NULL;
1150 g_requested_quantity2 := NULL; --Bug 14211120
1151 g_updated_quantity2 := NULL; --Bug 14211120
1152
1153 x_return_status := l_return_status;
1154
1155 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1156 P_Data => x_msg_Data);
1157
1158 IF l_debug_level > 0 THEN
1159 oe_debug_pub.add( 'EXITING OE_Process_Requisition_Pvt.Update_Internal_Requisition', 1 ) ;
1160 END IF;
1161
1162 Exception
1163 WHEN FND_API.G_EXC_ERROR THEN
1164 x_return_status := FND_API.G_RET_STS_ERROR;
1165 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1166 P_Data => x_msg_Data);
1167
1168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1171 P_Data => x_msg_Data);
1172
1173 WHEN OTHERS THEN
1174 oe_debug_pub.add( ' When Others of OE_Process_Requisition_Pvt.Update_Internal_Requisition '||sqlerrm,1);
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1176 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1177 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Internal_Requisition');
1178 -- Pkg Body global variable = OE_Process_Requisition_Pvt
1179 END IF;
1180 OE_MSG_PUB.Count_And_Get (P_Count => x_msg_Count,
1181 P_Data => x_msg_Data);
1182
1183 End Update_Internal_Requisition;
1184
1185 END OE_PROCESS_REQUISITION_PVT;