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