1 PACKAGE BODY OE_LINE_STATUS_PUB AS
2 /* $Header: OEXPLNSB.pls 120.1 2011/11/03 09:02:37 adandu ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_LINE_STATUS_PUB';
7
8 /*---------------------------------------------
9 PROCEDURE Get_Cancelled_Status (without date)
10
11 This procedure will take a line_id and
12 and check if the line has been cancelled.
13 If the line has been cancelled, it will return
14 a value of 'Y' in x_result. Otherwise a value
15 of 'N' will be returned.
16 ----------------------------------------------- */
17
18 PROCEDURE Get_Cancelled_Status(
19 p_line_id IN NUMBER,
20 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
21
22 IS
23
24 l_cancel_flag VARCHAR2(1);
25
26 --
27 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
28 --
29 BEGIN
30
31 SELECT cancelled_flag
32 INTO l_cancel_flag
33 FROM oe_order_lines_all
34 WHERE line_id = p_line_id;
35
36 -- we are returning Line_Closed_Status,
37 -- so we return Y when open_flag is N
38 -- and return N when open_flag is Y
39
40 IF l_cancel_flag = 'Y' THEN
41 x_result := 'Y';
42 ELSE
43 x_result := 'N';
44 END IF;
45
46 Exception
47 when others then
48 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
49 THEN
50 OE_MSG_PUB.Add_Exc_Msg
51 (
52 G_PKG_NAME,
53 'Get_Cancelled_Status'
54 );
55 END IF;
56 End Get_Cancelled_Status;
57
58
59 /*---------------------------------------------
60 PROCEDURE Get_Cancelled_Status (with date)
61
62 This is the overloaded version, it will
63 not only return the Y/N, but also the date
64 the WF activity happened. If the line is not
65 cancelled, we will return a null result date
66 ----------------------------------------------- */
67
68 PROCEDURE Get_Cancelled_Status(
69 p_line_id IN NUMBER,
70 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
71 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
72
73 IS
74
75 --
76 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
77 --
78 BEGIN
79
80 Get_Cancelled_Status(p_line_id => p_line_id, x_result => x_result);
81 IF x_result = 'Y' THEN
82 SELECT wias.end_date
83 INTO x_result_date
84 FROM wf_item_activity_statuses wias,
85 wf_process_activities wpa
86 WHERE wias.item_type = OE_GLOBALS.G_WFI_LIN
87 AND wias.item_key = to_char(p_line_id)
88 AND wias.process_activity = wpa.instance_id
89 AND wpa.activity_name = 'CLOSE_LINE';
90
91 ELSE
92 x_result_date := null;
93 END IF;
94
95 Exception
96 when others then
97 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
98 THEN
99 OE_MSG_PUB.Add_Exc_Msg
100 (
101 G_PKG_NAME,
102 'Get_Cancelled_Status'
103 );
104 END IF;
105 END Get_Cancelled_Status;
106
107 /*---------------------------------------------
108 PROCEDURE Get_Closed_Status (without date)
109
110 This procedure will take a line_id and
111 and check if the line has been closed.
112 If the line has been closed, it will return
113 a value of 'Y' in x_result. Otherwise a value
114 of 'N' will be returned.
115 ----------------------------------------------- */
116
117 PROCEDURE Get_Closed_Status(
118 p_line_id IN NUMBER,
119 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
120
121 IS
122
123 l_open_flag VARCHAR2(1);
124
125 --
126 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
127 --
128 BEGIN
129
130 SELECT nvl(open_flag, 'Y')
131 INTO l_open_flag
132 FROM oe_order_lines_all
133 WHERE line_id = p_line_id;
134
135 -- we are returning Line_Closed_Status,
136 -- so we return Y when open_flag is N
137 -- and return N when open_flag is Y
138
139 IF l_open_flag = 'Y' THEN
140 x_result := 'N';
141 ELSE
142 x_result := 'Y';
143 END IF;
144
145 Exception
146 when others then
147 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
148 THEN
149 OE_MSG_PUB.Add_Exc_Msg
150 (
151 G_PKG_NAME,
152 'Get_Closed_Status'
153 );
154 END IF;
155 END Get_Closed_Status;
156
157
158 /*---------------------------------------------
159 PROCEDURE Get_Closed_Status (with date)
160
161 This is the overloaded version, it will
162 not only return the Y/N, but also the date
163 the WF activity happened. If the line is not
164 closed, we will return a null result date
165 ----------------------------------------------- */
166
167 PROCEDURE Get_Closed_Status(
168 p_line_id IN NUMBER,
169 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
170 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
171
172 IS
173
174 --
175 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
176 --
177 BEGIN
178
179 Get_Closed_Status(p_line_id => p_line_id, x_result => x_result);
180 IF x_result = 'Y' THEN
181 SELECT wias.end_date
182 INTO x_result_date
183 FROM wf_item_activity_statuses wias,
184 wf_process_activities wpa
185 WHERE wias.item_type = OE_GLOBALS.G_WFI_LIN
186 AND wias.item_key = to_char(p_line_id)
187 AND wias.process_activity = wpa.instance_id
188 AND wpa.activity_name = 'CLOSE_LINE';
189
190 ELSE
191 x_result_date := null;
192 END IF;
193
194 Exception
195 when others then
196 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
197 THEN
198 OE_MSG_PUB.Add_Exc_Msg
199 (
200 G_PKG_NAME,
201 'Get_Closed_Status'
202 );
203 END IF;
204 END Get_Closed_Status;
205
206 /*----------------------------------------------------------
207 PROCEDURE Get_Purchase_Release_Status (without date)
208
209 This procedure is the overloaded version of
210 Get_Purchase_Release_Status (with date). It will
211 ignore the date, and return a 'Y' or 'N' showing if
212 the Purchase Release activity has happened. A 'N' will
213 be returned if the activity is not in your flow.
214 ------------------------------------------------------------ */
215
216 PROCEDURE Get_Purchase_Release_Status(
217 p_line_id IN NUMBER,
218 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
219
220 IS
221
222 l_result_date DATE;
223
224 --
225 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
226 --
227 BEGIN
228
229 Get_Purchase_Release_Status(p_line_id => p_line_id, x_result => x_result, x_result_date => l_result_date);
230
231 Exception
232 when others then
233 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
234 THEN
235 OE_MSG_PUB.Add_Exc_Msg
236 (
237 G_PKG_NAME,
238 'Get_Purchase_Release_Status'
239 );
240 END IF;
241
242 END Get_Purchase_Release_Status;
243
244
245 /*----------------------------------------------------------
246 PROCEDURE Get_Purchase_Release_Status (with date)
247
248 This procedure will take a line_id and check if
249 the Purchas Release workflow activity has happened.
250 If it has happened, a value of 'Y' will be returned,
251 otherwise a 'N' will be returned. Along with the Y/N,
252 the date this activity happened will also be returned.
253 If the activity doesn't exist in your workflow,
254 a 'N' result and a null result date will be returned.
255 ------------------------------------------------------------ */
256
257 PROCEDURE Get_Purchase_Release_Status(
258 p_line_id IN NUMBER,
259 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
260 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
261
262 IS
263
264 --
265 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
266 --
267 BEGIN
268
269 SELECT nvl(wias.activity_result_code, 'N'), wias.end_date
270 INTO x_result, x_result_date
271 FROM wf_item_activity_statuses wias,
272 wf_process_activities wpa
273 WHERE wias.item_type = OE_GLOBALS.G_WFI_LIN
274 AND wias.item_key = to_char(p_line_id)
275 AND wias.process_activity = wpa.instance_id
276 AND wpa.activity_name = 'PUR_REL_THE_LINE';
277
278 IF x_result = OE_GLOBALS.G_WFR_COMPLETE THEN
279 x_result := 'Y';
280 ELSE
281 x_result := 'N';
282 x_result_date := null;
283 -- if activity has not complete, we will return a null end date
284 END IF;
285
286 EXCEPTION
287
288 -- when the activity is not in your flow
289 WHEN NO_DATA_FOUND THEN
290 x_result := 'N';
291 x_result_date := null;
292
293
294 WHEN OTHERS THEN
295 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
296 THEN
297 OE_MSG_PUB.Add_Exc_Msg
298 (
299 G_PKG_NAME,
300 'Get_Purchase_Release_Status'
301 );
302 END IF;
303
304 END Get_Purchase_Release_Status;
305
306 /*---------------------------------------------
307 PROCEDURE Get_Ship_Status (without date)
308
309 This procedure will take a line_id and
310 and check if the line has been shipped.
311 If the line has been shipped, it will return
312 a value of 'Y' in x_result. Otherwise a value
313 of 'N' will be returned.
314 ----------------------------------------------- */
315
316 PROCEDURE Get_Ship_Status(
317 p_line_id IN NUMBER,
318 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
319
320 IS
321
322 l_shipped_quantity NUMBER;
323
324 --
325 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
326 --
327 BEGIN
328
329 SELECT nvl(shipped_quantity, 0)
330 INTO l_shipped_quantity
331 FROM oe_order_lines_all
332 WHERE line_id = p_line_id;
333
334 -- we are returning Line_shipped_Status,
335 -- so we return Y when line is shipped
336 -- and return N when line is not shipped.
337
338 IF l_shipped_quantity = 0 THEN
339 x_result := 'N';
340 ELSE
341 x_result := 'Y';
342 END IF;
343
344 Exception
345 when others then
346 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
347 THEN
348 OE_MSG_PUB.Add_Exc_Msg
349 (
350 G_PKG_NAME,
351 'Get_Ship_Status'
352 );
353 END IF;
354 END Get_Ship_Status;
355
356
357 /*---------------------------------------------
358 PROCEDURE Get_Ship_Status (with date)
359
360 This is the overloaded version, it will
361 not only return the Y/N, but also the date
362 the WF activity happened. If the line is not
363 closed, we will return a null result date
364 ----------------------------------------------- */
365
366 PROCEDURE Get_Ship_Status(
367 p_line_id IN NUMBER,
368 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
369 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
370
371 IS
372 l_shipped_quantity NUMBER;
373 --
374 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
375 --
376 BEGIN
377
378 -- Get_Ship_Status(p_line_id => p_line_id, x_result => x_result);
379 -- IF x_result = 'Y' THEN
380 -- SELECT end_date
381 -- INTO x_result_date
382 -- FROM wf_item_activity_statuses
383 -- WHERE item_type = OE_GLOBALS.G_WFI_LIN
384 -- AND item_key = p_line_id
385 -- AND process_activity IN (SELECT wpa.instance_id
386 -- FROM wf_process_activities wpa
387 -- WHERE wpa.activity_item_type = OE_GLOBALS.G_WFI_LIN
388 -- AND wpa.activity_name = 'SHIP_LINE');
389 -- ELSE
390 -- x_result_date := null;
391 -- END IF;
392
393 SELECT nvl(shipped_quantity, 0),actual_shipment_date
394 INTO l_shipped_quantity,x_result_date
395 FROM oe_order_lines_all
396 WHERE line_id = p_line_id;
397
398 -- we are returning Line_shipped_Status,
399 -- so we return Y when line is shipped
400 -- and return N when line is not shipped.
401
402 IF l_shipped_quantity = 0 THEN
403 x_result := 'N';
404 x_result_date := null;
405 ELSE
406 x_result := 'Y';
407 END IF;
408
409
410 Exception
411 when others then
412 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
413 THEN
414 OE_MSG_PUB.Add_Exc_Msg
415 (
416 G_PKG_NAME,
417 'Get_Ship_Status'
418 );
419 END IF;
420 END Get_Ship_Status;
421
422 /*---------------------------------------------
423 PROCEDURE Get_Pick_Status (without date)
424
425 This procedure will take a line_id and
426 and check if the line has been picked.
427 If the line has been picked, it will return
428 a value of 'Y' in x_result. If the line is not picked
429 it will return 'N', if the line is partially picked it will return 'P'
430 ----------------------------------------------- */
431
432 PROCEDURE Get_Pick_Status(
433 p_line_id IN NUMBER,
434 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
435
436 IS
437
438 l_pick_status VARCHAR2(1);
439 l_picked_quantity NUMBER;
440 l_picked_quantity_uom VARCHAR2(3);
441
442 --
443 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
444 --
445 BEGIN
446
447
448 Get_Pick_Status
449 (
450 p_line_id => p_line_id,
451 x_result => l_pick_status,
452 x_picked_quantity => l_picked_quantity,
453 x_picked_quantity_uom => l_picked_quantity_uom
454 );
455
456 x_result := l_pick_status;
457 -- we are returning Line_Pick_Status,
458 -- so we return Y when line is picked
459 -- and return N when line is not picked and P when the line is partially picked.
460
461 Exception
462 when others then
463 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
464 THEN
465 OE_MSG_PUB.Add_Exc_Msg
466 (
467 G_PKG_NAME,
468 'Get_Pick_Status'
469 );
470 END IF;
471 END Get_Pick_Status;
472
473 /*---------------------------------------------
474 PROCEDURE Get_Pick_Status (with quantity)
475
476 This procedure will take a line_id and
477 and check if the line has been picked.
478 If the line has been picked, it will return
479 a value of 'Y' in x_result and the picked quantity. Otherwise a value
480 of 'N' will be returned.
481 ----------------------------------------------- */
482
483 PROCEDURE Get_Pick_Status(
484 p_line_id IN NUMBER,
485 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
486 x_picked_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER,
487 x_picked_quantity_uom OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
488
489 IS
490
491 CURSOR c_pick IS
492 SELECT PICK_STATUS, REQUESTED_QUANTITY, REQUESTED_QUANTITY_UOM
493 FROM WSH_DELIVERY_LINE_STATUS_V
494 WHERE SOURCE_CODE = 'OE'
495 AND SOURCE_LINE_ID = p_line_id;
496
497 l_pick_status VARCHAR2(1) := 'X';
498 l_picked_quantity NUMBER := 0;
499
500 --
501 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
502 --
503 BEGIN
504
505 FOR l_pick_data IN c_pick LOOP
506
507 IF l_debug_level > 0 THEN
508 oe_debug_pub.add( 'PICK STATUS/QUANTITY :'||L_PICK_DATA.PICK_STATUS||'/'||TO_CHAR ( L_PICK_DATA.REQUESTED_QUANTITY ) , 2 ) ;
509 END IF;
510 IF l_debug_level > 0 THEN
511 oe_debug_pub.add( 'L_PICK_STATUS : '||L_PICK_STATUS , 2 ) ;
512 END IF;
513 IF l_pick_data.pick_status = 'Y' THEN
514 IF l_pick_status = 'X' OR
515 l_pick_status = 'Y' THEN
516 l_pick_status := 'Y';
517 ELSE
518 l_pick_status := 'P';
519 END IF;
520 l_picked_quantity := l_picked_quantity + l_pick_data.requested_quantity;
521 x_picked_quantity_uom := l_pick_data.requested_quantity_uom;
522 IF l_debug_level > 0 THEN
523 oe_debug_pub.add( 'PICK STATUS/QUANTITY IN THE IF :'||L_PICK_STATUS||'/'||TO_CHAR ( L_PICKED_QUANTITY ) , 2 ) ;
524 END IF;
525 ELSIF l_pick_data.pick_status <> 'Y' THEN
526 IF l_pick_status = 'N' OR
527 l_pick_status = 'X' THEN
528 l_pick_status := 'N';
529 ELSE
530 l_pick_status := 'P';
531 END IF;
532 END IF;
533
534 END LOOP;
535
536 -- we are returning Line_Pick_Status,
537 -- so we return Y when line is picked
538 -- and return N when line is not picked
539
540 IF l_pick_status = 'X' THEN
541 x_result := 'N';
542 ELSE
543 x_result := l_pick_status;
544 END IF;
545
546 x_picked_quantity := l_picked_quantity;
547
548 Exception
549 when others then
550 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
551 THEN
552 OE_MSG_PUB.Add_Exc_Msg
553 (
554 G_PKG_NAME,
555 'Get_Pick_Status'
556 );
557 END IF;
558 END Get_Pick_Status;
559
560 /*---------------------------------------------
561 PROCEDURE Get_Received_Status (without date)
562
563 This procedure will take a line_id and
564 check if the line has been received.
565 If the line has been received, it will return a value of 'Y'
566 in x_result. Otherwise a value of 'N' will be returned.
567 ----------------------------------------------- */
568
569 PROCEDURE Get_Received_Status(
570 p_line_id IN NUMBER,
571 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
572
573 IS
574 l_received_quantity NUMBER;
575 --
576 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
577 --
578 BEGIN
579
580
581 SELECT nvl(shipped_quantity, 0)
582 INTO l_received_quantity
583 FROM oe_order_lines_all
584 WHERE line_id = p_line_id;
585
586 -- we are returning Line_Receive_Status,
587 -- so we return Y when line is received
588 -- and return N when line is not received
589
590 IF l_received_quantity = 0 THEN
591 x_result := 'N';
592 ELSE
593 x_result := 'Y';
594 END IF;
595
596 Exception
597 when others then
598 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
599 THEN
600 OE_MSG_PUB.Add_Exc_Msg
601 (
602 G_PKG_NAME,
603 'Get_Received_Status'
604 );
605 END IF;
606 END Get_Received_Status;
607
608
609 /*---------------------------------------------
610 PROCEDURE Get_Received_Status (with date)
611
612 This is the overloaded version of Get_Received_Status
613 (without date). In addition to return a 'Y' or 'N' for
614 the line received status, it will also return the
615 date the activity happened. If the activity hasn't
616 happened, a null result date will be returned.
617 ----------------------------------------------- */
618
619 PROCEDURE Get_Received_Status(
620 p_line_id IN NUMBER,
621 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
622 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
623
624 IS
625
626 --
627 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
628 --
629 BEGIN
630
631 Get_Received_Status(p_line_id => p_line_id, x_result => x_result);
632 IF x_result = 'Y' THEN
633 SELECT wias.end_date
634 INTO x_result_date
635 FROM wf_item_activity_statuses wias,
636 wf_process_activities wpa
637 WHERE wias.item_type = OE_GLOBALS.G_WFI_LIN
638 AND wias.item_key = to_char(p_line_id)
639 AND wias.process_activity = wpa.instance_id
640 AND wpa.activity_name = 'RMA_WAIT_FOR_RECEIVING';
641 ELSE
642 x_result_date := null;
643 END IF;
644
645 Exception
646 when others then
647 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
648 THEN
649 OE_MSG_PUB.Add_Exc_Msg
650 (
651 G_PKG_NAME,
652 'Get_Received_Status'
653 );
654 END IF;
655 END Get_Received_Status;
656
657 /*---------------------------------------------
658 PROCEDURE Get_invoiced_Status (without date)
659
660 This procedure will take a line_id and
661 and check if the line has been invoiced.
662 If the line has been invoiced, it will return
663 a value of 'Y' in x_result. Otherwise a value
664 of 'N' will be returned.
665 ----------------------------------------------- */
666
667 PROCEDURE Get_Invoiced_Status(
668 p_line_id IN NUMBER,
669 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
670
671 IS
672
673 l_invoice_interface_status VARCHAR2(30);
674
675 --
676 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
677 --
678 BEGIN
679
680 SELECT invoice_interface_status_code
681 INTO l_invoice_interface_status
682 FROM oe_order_lines_all
683 WHERE line_id = p_line_id;
684
685 -- return Y when invoice_interface_status_code is YES
686 -- and return N otherwise (for 'NO', 'PARTIAL' and 'NOT_ELIGIBLE')
687
688 IF l_invoice_interface_status = 'YES' THEN
689 x_result := 'Y';
690 ELSE
691 x_result := 'N';
692 END IF;
693
694 Exception
695 when others then
696 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
697 THEN
698 OE_MSG_PUB.Add_Exc_Msg
699 (
700 G_PKG_NAME,
701 'Get_Invoiced_Status'
702 );
703 END IF;
704 End Get_Invoiced_Status;
705
706
707 /*---------------------------------------------
708 PROCEDURE Get_Invoiced_Status (with date)
709
710 This is the overloaded version, it will
711 not only return the Y/N, but also the date
712 the WF activity happened. If the line is not
713 invoiced, we will return a null result date
714 ----------------------------------------------- */
715
716 PROCEDURE Get_Invoiced_Status(
717 p_line_id IN NUMBER,
718 x_result OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
719 x_result_date OUT NOCOPY /* file.sql.39 change */ DATE)
720
721 IS
722 l_count NUMBER;
723 l_header_id NUMBER;
724 --
725 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
726 --
727 BEGIN
728
729 Get_Invoiced_Status(p_line_id => p_line_id, x_result => x_result);
730 IF x_result = 'Y' THEN
731
732 BEGIN
733 SELECT wias.end_date
734 INTO x_result_date
735 FROM wf_item_activity_statuses wias,
736 wf_process_activities wpa
737 WHERE wias.item_type = OE_GLOBALS.G_WFI_LIN
738 AND wias.item_key = to_char(p_line_id)
739 AND wias.process_activity = wpa.instance_id
740 AND wpa.activity_name = 'INVOICE_INTERFACE';
741
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744
745 SELECT header_id
746 INTO l_header_id
747 FROM oe_order_lines_all
748 WHERE line_id = p_line_id;
749
750 SELECT wias.end_date
751 INTO x_result_date
752 FROM wf_item_activity_statuses wias,
753 wf_process_activities wpa
754 WHERE wias.item_type = OE_GLOBALS.G_WFI_HDR
755 AND wias.item_key = l_header_id
756 AND wias.process_activity = wpa.instance_id
757 AND wpa.activity_name = 'HEADER_INVOICE_INTERFACE';
758 END;
759 ELSE
760 x_result_date := null;
761 END IF;
762
763 Exception
764 when others then
765 IF OE_MSG_PUB.CHeck_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
766 THEN
767 OE_MSG_PUB.Add_Exc_Msg
768 (
769 G_PKG_NAME,
770 'Get_Invoiced_Status'
771 );
772 END IF;
773 END Get_Invoiced_Status;
774
775
776 FUNCTION Get_Line_Status(
777 p_line_id IN NUMBER
778 ,p_flow_status_code IN VARCHAR2)
779 RETURN VARCHAR2
780 IS
781 l_flow_status_code VARCHAR2(80);
782 l_flow_meaning VARCHAR2(80);
783 released_count NUMBER;
784 total_count NUMBER;
785 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
786 BEGIN
787
788
789 IF l_debug_level > 0 THEN
790 OE_DEBUG_PUB.Add('Entering OE_LINE_STATUS_PUB.Get_Line_Status...',1);
791 OE_DEBUG_PUB.Add('Flow Status Code:'||p_flow_status_code,1);
792 END IF;
793
794 l_flow_status_code := p_flow_status_code;
795
796 IF p_flow_status_code is null THEN
797 SELECT flow_status_code
798 INTO l_flow_status_code
799 FROM oe_order_lines
800 WHERE line_id=p_line_id;
801 END IF;
802
803 IF l_flow_status_code <> 'AWAITING_SHIPPING' AND
804 l_flow_status_code <> 'PRODUCTION_COMPLETE' AND
805 l_flow_status_code <> 'PICKED' AND
806 l_flow_status_code <> 'PICKED_PARTIAL' AND
807 l_flow_status_code <> 'PO_RECEIVED'
808 /*Starting fix for bug 12324648*/
809 AND l_flow_status_code <> 'SUPPLY_PARTIAL'
810 /*Ending fix for bug 12324648*/
811 THEN
812 SELECT meaning
813 INTO l_flow_meaning
814 FROM fnd_lookup_values lv
815 WHERE lookup_type = 'LINE_FLOW_STATUS'
816 AND lookup_code = l_flow_status_code
817 AND LANGUAGE = userenv('LANG')
818 AND VIEW_APPLICATION_ID = 660
819 AND SECURITY_GROUP_ID =
820 fnd_global.Lookup_Security_Group(lv.lookup_type,
821 lv.view_application_id);
822
823 /* status is AWAITING_SHIPPING or PRODUCTION_COMPLETE etc.
824 get value from shipping table */
825 ELSE
826
827 -- fix for 3696124 begins
828 --SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
829 SELECT sum(decode(released_status, 'Y', 1, 'C', 1, 0)), sum(1)
830 -- fix for 3696124 ends
831 INTO released_count, total_count
832 FROM wsh_delivery_details
833 WHERE source_line_id = p_line_id
834 AND source_code = 'OE'
835 AND released_status <> 'D';
836
837 IF released_count = total_count THEN
838 SELECT meaning
839 INTO l_flow_meaning
840 FROM fnd_lookup_values lv
841 WHERE lookup_type = 'LINE_FLOW_STATUS'
842 AND lookup_code = 'PICKED'
843 AND LANGUAGE = userenv('LANG')
844 AND VIEW_APPLICATION_ID = 660
845 AND SECURITY_GROUP_ID =
846 fnd_global.Lookup_Security_Group(lv.lookup_type,
847 lv.view_application_id);
848
849 ELSIF released_count < total_count and released_count <> 0 THEN
850 SELECT meaning
851 INTO l_flow_meaning
852 FROM fnd_lookup_values lv
853 WHERE lookup_type = 'LINE_FLOW_STATUS'
854 AND lookup_code = 'PICKED_PARTIAL'
855 AND LANGUAGE = userenv('LANG')
856 AND VIEW_APPLICATION_ID = 660
857 AND SECURITY_GROUP_ID =
858 fnd_global.Lookup_Security_Group(lv.lookup_type,
859 lv.view_application_id);
860 ELSE
861 SELECT meaning
862 INTO l_flow_meaning
863 FROM fnd_lookup_values lv
864 WHERE lookup_type = 'LINE_FLOW_STATUS'
865 AND lookup_code = l_flow_status_code
866 AND LANGUAGE = userenv('LANG')
867 AND VIEW_APPLICATION_ID = 660
868 AND SECURITY_GROUP_ID =
869 fnd_global.Lookup_Security_Group(lv.lookup_type,
870 lv.view_application_id);
871 END IF;
872 END IF;
873
874 IF l_debug_level > 0 THEN
875 OE_DEBUG_PUB.Add('Exiting Get_Line_Status:'||l_flow_meaning,1);
876 END IF;
877
878 RETURN l_flow_meaning;
879
880
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 Null;
884 WHEN TOO_MANY_ROWS THEN
885 Null;
886 WHEN OTHERS THEN
887 Null;
888 END Get_Line_Status;
889
890 END OE_LINE_STATUS_PUB;
[Home] [Help]