1 PACKAGE BODY OE_QUERY as
2 /* $Header: OEXQRYSB.pls 120.1 2006/11/19 18:01:41 ssurapan noship $ */
3
4
5 ------------------------------------------------------------------------
6 -- 1. if LINE_TYPE_CODE is 'PARENT', get the extended_price from the
7 -- shipment_schedule_line_id = current line_id
8 -- 2. if not, check the ITEM_TYPE_CODE
9 -- 2.1 if 'MODEL', get current line's extended_price
10 -- + parent_line_id = current line_id 's extended_price
11 -- + service_parent_line_id = current line_id 's extended_price
12 -- 2.2 if'SERVICE', check serviceable_duration, if 0 then total is 0
13 -- if not,get current extended_price
14 -- 2.3 otherwise, check service_flag='Y',
15 -- 2.3.1 TRUE : get current line's extend_price +
16 -- service_parent_line_id = current line_id 's extended price
17 -- 2.3.2 FALSE : get current line's extended_price
18 ------------------------------------------------------------------------
19 function LINE_TOTAL(
20 ORDER_ROWID IN VARCHAR2
21 , ORDER_LINE_ID IN NUMBER DEFAULT NULL
22 , LINE_TYPE_CODE IN VARCHAR2
23 , ITEM_TYPE_CODE IN VARCHAR2
24 , SERVICE_DURATION IN NUMBER
25 , SERVICEABLE_FLAG IN VARCHAR2
26 , ORDERED_QTY IN NUMBER
27 , CANCELLED_QTY IN NUMBER
28 , SELLING_PRICE IN NUMBER
29 )
30 return NUMBER
31 IS
32 order_line_total NUMBER := NULL ;
33 BEGIN
34
35 if ( LINE_TYPE_CODE = 'PARENT' )
36 then
37 SELECT NVL(SUM( (NVL( ORDERED_QUANTITY, 0 ) -
38 NVL( CANCELLED_QUANTITY, 0 )) *
39 NVL(SELLING_PRICE, 0 ))
40 , 0)
41 INTO order_line_total
42 FROM SO_LINES
43 WHERE SHIPMENT_SCHEDULE_LINE_ID = ORDER_LINE_ID;
44 elsif ( ITEM_TYPE_CODE = 'MODEL' )
45 then
46 SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
47 NVL( CANCELLED_QUANTITY, 0 )) *
48 NVL(SELLING_PRICE, 0 ))
49 , 0)
50 INTO order_line_total
51 FROM SO_LINES
52 WHERE (ROWID = ORDER_ROWID
53 OR PARENT_LINE_ID = ORDER_LINE_ID
54 OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
55
56 elsif (ITEM_TYPE_CODE = 'SERVICE')
57 then
58 if (SERVICE_DURATION = 0)
59 then
60 order_line_total := 0;
61 else
62 order_line_total := ( ORDERED_QTY - CANCELLED_QTY)
63 * SELLING_PRICE;
64 end if;
65 elsif (SERVICEABLE_FLAG= 'Y')
66 then
67 SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
68 NVL( CANCELLED_QUANTITY, 0 )) *
69 NVL(SELLING_PRICE, 0 ))
70 , 0)
71 INTO order_line_total
72 FROM SO_LINES
73 WHERE (ROWID = ORDER_ROWID
74 OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
75 else
76 order_line_total := ( nvl(ORDERED_QTY,0) - nvl(CANCELLED_QTY,0))
77 * nvl(SELLING_PRICE,0);
78 end if ;
79
80 return(order_line_total);
81
82 Exception WHEN NO_DATA_FOUND then
83 return(NULL);
84
85 END;
86
87 ------------------------------------------------------------------------
88 -- Service Total for shipments and options
89 ------------------------------------------------------------------------
90
91 function SERVICE_TOTAL(
92 P_ROWID IN VARCHAR2
93 , P_LINE_ID IN NUMBER
94 , P_LINES_LINE_ID IN NUMBER
95 , P_ITEM_TYPE_CODE IN VARCHAR2
96 , P_SERVICEABLE_FLAG IN VARCHAR2
97 )
98 return NUMBER
99
100 IS
101
102 Service_Total NUMBER;
103
104 BEGIN
105
106 if (P_serviceable_Flag <> 'Y') then
107 return (NULL);
108 end if;
109
110 if (P_Item_Type_Code = 'MODEL') then
111
112 SELECT NVL( SUM( DECODE( SERVICE_PARENT_LINE_ID, P_LINES_LINE_ID,
113 (NVL( ORDERED_QUANTITY, 0 ) -
114 NVL( CANCELLED_QUANTITY, 0 ) ) *
115 NVL( SELLING_PRICE, 0 )
116 , 0 ) ), 0 )
117 INTO Service_Total
118 FROM SO_LINES
119 WHERE (ROWID = P_ROWID
120 OR PARENT_LINE_ID = P_LINE_ID
121 OR SERVICE_PARENT_LINE_ID = P_LINE_ID );
122 else
123 SELECT NVL( SUM( DECODE( SERVICE_PARENT_LINE_ID, P_LINES_LINE_ID,
124 (NVL( ORDERED_QUANTITY, 0 ) -
125 NVL( CANCELLED_QUANTITY, 0 ) ) *
126 NVL( SELLING_PRICE, 0 )
127 , 0 ) ), 0 )
128 INTO Service_Total
129 FROM SO_LINES
130 WHERE (ROWID = P_ROWID
131 OR SERVICE_PARENT_LINE_ID = P_LINE_ID );
132
133 end if;
134
135 return(Service_Total);
136
137 EXCEPTION WHEN NO_DATA_FOUND then
138
139 return(NULL);
140
141 END;
142
143
144 function SCHEDULE_STATUS(
145 SCHEDULE_STATUS_CODE IN VARCHAR2
146 )
147 return VARCHAR2 is
148
149 schedule_status VARCHAR2(80) := NULL;
150
151 begin
152
153 if ( schedule_status_code is not null )
154 then
155 select meaning
156 into schedule_status
157 from so_lookups
158 where lookup_code = schedule_status_code
159 and lookup_type = 'SCHEDULE STATUS';
160 end if;
161
162 return( SCHEDULE_STATUS );
163 Exception
164
165 WHEN NO_DATA_FOUND
166 then
167 return(NULL);
168
169 end SCHEDULE_STATUS;
170
171
172 ------------------------------------------------------------------------
173 -- The schedule status code priority is 'RESERVED' > 'SUPPLY RESERVED'
174 -- > 'DEMAND', return the highest priority code to the line. If none
175 -- of above, the status code is null;
176 ------------------------------------------------------------------------
177 function SCHEDULE_STATUS(
178 ORDER_LINE_ID IN NUMBER DEFAULT NULL
179 )
180 return VARCHAR2
181 IS
182 schedule_status_code VARCHAR2(30) := NULL;
183 schedule_status VARCHAR2(80) := NULL;
184 begin
185 SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
186 'RESERVED', SLD.QUANTITY, 0 ) ), 0),
187 0,
188 DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
189 'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
190 0,
191 DECODE( NVL( SUM( DECODE(
192 SLD.SCHEDULE_STATUS_CODE,
193 'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
194 0, NULL,
195 'DEMANDED' ),
196 'SUPPLY RESERVED' ),
197 'RESERVED' )
198 INTO schedule_status_code
199 FROM SO_LINE_DETAILS SLD
200 WHERE SLD.LINE_ID = ORDER_LINE_ID;
201
202 if ( schedule_status_code is not null )
203 then
204 select meaning
205 into schedule_status
206 from so_lookups
207 where lookup_code = schedule_status_code
208 and lookup_type = 'SCHEDULE STATUS';
209 end if;
210
211 return( SCHEDULE_STATUS );
212 Exception
213 WHEN NO_DATA_FOUND
214 then
215 return(NULL);
216 end ;
217
218 ------------------------------------------------------------------------
219 -- The schedule status code priority is 'RESERVED' > 'SUPPLY RESERVED'
220 -- > 'DEMAND', return the highest priority code to the line. If none
221 -- of above, the status code is null;
222 ------------------------------------------------------------------------
223 function SCHEDULE_STATUS_CODE (
224 ORDER_LINE_ID IN NUMBER DEFAULT NULL
225 )
226 return VARCHAR2
227 IS
228 schedule_status_code VARCHAR2(30) := NULL;
229 begin
230 SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
231 'RESERVED', SLD.QUANTITY, 0 ) ), 0),
232 0,
233 DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
234 'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
235 0,
236 DECODE( NVL( SUM( DECODE(
237 SLD.SCHEDULE_STATUS_CODE,
238 'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
239 0, NULL,
240 'DEMANDED' ),
241 'SUPPLY RESERVED' ),
242 'RESERVED' )
243 INTO schedule_status_code
244 FROM SO_LINE_DETAILS SLD
245 WHERE SLD.LINE_ID = ORDER_LINE_ID;
246
247 return( SCHEDULE_STATUS_CODE );
248
249 Exception
250 WHEN NO_DATA_FOUND
251 then
252 return(NULL);
253 end ;
254
255 ------------------------------------------------------------------------
256 -- To get the ato_indicator for model
257 ------------------------------------------------------------------------
258
259 function ATO_Indicator( P_Line_Id IN NUMBER,
260 P_Item_Type_Code IN VARCHAR2)
261 return VARCHAR2
262 IS
263 ATO_Ind VARCHAR2(1);
264 begin
265
266 if (P_Item_Type_Code <> 'MODEL') then
267 ATO_Ind := 'N';
268 else
269 SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
270 INTO ATO_Ind
271 FROM SO_LINES
272 WHERE PARENT_LINE_ID = P_Line_Id
273 AND SERVICE_PARENT_LINE_ID IS NULL
274 AND ATO_FLAG = 'Y'
275 AND ROWNUM = 1;
276 end if;
277
278 return(ATO_Ind);
279
280 Exception
281 WHEN OTHERS
282 then
283 return(NULL);
284 end ;
285
286 ------------------------------------------------------------------------
287 -- To get the ato_indicator for model
288 ------------------------------------------------------------------------
289
290 function Get_ATO_Indicator( P_Line_Id IN NUMBER,
291 P_Item_Type_Code IN VARCHAR2)
292 return VARCHAR2
293 IS
294 ATO_Ind VARCHAR2(1);
295 begin
296
297 if (P_Item_Type_Code <> 'MODEL') then
298 ATO_Ind := 'N';
299 else
300 SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
301 INTO ATO_Ind
302 FROM SO_LINES
303 WHERE PARENT_LINE_ID = P_Line_Id
304 AND SERVICE_PARENT_LINE_ID IS NULL
305 AND ATO_FLAG = 'Y'
306 AND ROWNUM = 1;
307 end if;
308
309 return(ATO_Ind);
310
311 Exception
312 WHEN OTHERS
313 then
314 return(NULL);
315 end ;
316
317
318
319 ------------------------------------------------------------------------
320 -- from So_Line_DETAILS table to get released quantity for a line.
321 ------------------------------------------------------------------------
322 FUNCTION Released_Quantity(P_Line_Id IN NUMBER) RETURN NUMBER IS
323 Config_Item_Flag VARCHAR2(1) := NULL;
324 rel_qty NUMBER := 0;
325 total_rel_qty NUMBER := 0;
326 CURSOR C_Get_Released_Quantity (X_Line_Id IN NUMBER) IS
327 SELECT sum(nvl(quantity, 0)), configuration_item_flag
328 FROM so_line_details
329 WHERE line_id = X_Line_Id
330 AND nvl(included_item_flag, 'N') = 'N'
331 AND nvl(released_flag, 'Y') = 'Y'
332 GROUP BY configuration_item_flag;
333 BEGIN
334
335 OPEN C_Get_Released_Quantity (P_Line_Id);
336
337 LOOP
338
339 FETCH C_Get_Released_Quantity
340 INTO rel_qty, config_item_flag;
341
342 EXIT WHEN C_Get_Released_Quantity%NOTFOUND;
343
344 IF (config_item_flag = 'Y') THEN
345 RETURN (rel_qty);
346 END IF;
347
348 total_rel_qty := total_rel_qty + rel_qty;
349
350 END LOOP;
351
352 RETURN (total_rel_qty);
353
354 EXCEPTION
355
356 WHEN OTHERS THEN
357 RAISE;
358
359 END Released_Quantity;
360
361 FUNCTION P_Line_Released_Quantity(P_Line_Id IN NUMBER) RETURN NUMBER IS
362 rel_qty NUMBER := 0;
363 BEGIN
364
365 SELECT SUM(NVL(REQUESTED_QUANTITY,0))
366 INTO rel_qty
367 FROM SO_PICKING_LINE_DETAILS
368 WHERE PICKING_LINE_ID = P_LINE_ID
369 AND NVL(RELEASED_FLAG,'N')='Y';
370 RETURN (rel_qty);
371
372 EXCEPTION
373
374 WHEN OTHERS THEN
375 RAISE;
376
377 END P_LINE_Released_Quantity;
378
379
380
381
382 ------------------------------------------------------------------------
383 -- from So_Line_DETAILS table to get all reserved details quantity
384 ------------------------------------------------------------------------
385 function RESERVED_QUANTITY(
386 ORDER_LINE_ID IN NUMBER DEFAULT NULL
387 )
388 return NUMBER
389 IS
390 RESERVED_QTY NUMBER := NULL ;
391 BEGIN
392
393 select sum( decode( SCHEDULE_STATUS_CODE,
394 'RESERVED', QUANTITY,
395 0))
396 into RESERVED_QTY
397 from SO_LINE_DETAILS
398 where line_id = ORDER_LINE_ID
399 and NVL(INCLUDED_ITEM_FLAG, 'N')='N';
400
401 return( RESERVED_QTY);
402
403 EXCEPTION
404 when NO_DATA_FOUND
405 then
406 return(NULL);
407 END ;
408
409
410 ------------------------------------------------------------------------
411 -- from So_Line_DETAILS table to get all reserved details quantity
412 -- for the included item.
413 ------------------------------------------------------------------------
414 function II_RESERVED_QUANTITY(
415 P_LINE_ID IN NUMBER,
416 P_COMPONENT_CODE IN VARCHAR2
417 )
418 return NUMBER
419 IS
420 RESERVED_QTY NUMBER := NULL ;
421 BEGIN
422
423 /* Modified the following where clause to fix bug# 925562, propagated from
424 Rel 11 - 896589. Replaced ltrim(... by p_component_code
425 */
426 select sum(nvl(det.quantity, 0))
427 into reserved_qty
428 from so_lines l,
429 so_line_details det
430 where
431 l.line_id = p_line_id
432 and det.line_id = l.line_id
433 and det.included_item_flag = 'Y'
434 and det.schedule_status_code = 'RESERVED'
435 -- and det.component_code = l.component_code ||
436 -- ltrim(p_component_code, '0123456789');
437 and det.component_code = p_component_code;
438
439 return( RESERVED_QTY);
440
441 EXCEPTION
442 when NO_DATA_FOUND
443 then
444 return(NULL);
445 END ;
446
447
448
449 ------------------------------------------------------------------------
450 -- from So_Line_DETAILS table to get all reserved details quantity
451 -- for the included item.
452 ------------------------------------------------------------------------
453
454 function II_RELEASED_QUANTITY(
455 P_LINE_ID IN NUMBER,
456 P_COMPONENT_CODE IN VARCHAR2
457 )
458 return NUMBER
459 IS
460 RELEASED_QTY NUMBER := NULL ;
461 BEGIN
462
463 /* Modified the following where clause to fix bug# 925562, propagated from
464 Rel 11 - 896589. Replaced ltrim(... by p_component_code
465 */
466 select sum(nvl(det.quantity, 0))
467 into released_qty
468 from so_lines l,
469 so_line_details det
470 where
471 l.line_id = p_line_id
472 and det.line_id = l.line_id
473 and det.released_flag = 'Y'
474 and det.included_item_flag = 'Y'
475 -- and det.component_code = l.component_code ||
476 -- ltrim(p_component_code, '0123456789');
477 and det.component_code = p_component_code;
478
479 return( RELEASED_QTY);
480
481 EXCEPTION
482 when NO_DATA_FOUND
483 then
484 return(NULL);
485 END ;
486
487
488
489 ------------------------------------------------------------------------
490 -- The schedule status code priority is 'RESERVED' > 'SUPPLY RESERVED'
491 -- > 'DEMAND', return the highest priority code to the line. If none
492 -- of above, the status code is null;
493 ------------------------------------------------------------------------
494 function II_SCHEDULE_STATUS_CODE (
495 P_LINE_ID IN NUMBER,
496 P_COMPONENT_CODE IN VARCHAR2
497 )
498 return VARCHAR2
499 IS
500 schedule_status_code VARCHAR2(30) := NULL;
501 begin
502 /* Modified the following where clause to fix bug# 925562, propagated from
503 Rel 11 - 896589. Replaced ltrim(... by p_component_code
504 */
505 SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
506 'RESERVED', SLD.QUANTITY, 0 ) ), 0),
507 0,
508 DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
509 'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
510 0,
511 DECODE( NVL( SUM( DECODE(
512 SLD.SCHEDULE_STATUS_CODE,
513 'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
514 0, NULL,
515 'DEMANDED' ),
516 'SUPPLY RESERVED' ),
517 'RESERVED' )
518 INTO schedule_status_code
519 FROM
520 SO_LINES SL,
521 SO_LINE_DETAILS SLD
522 WHERE
523 SL.line_id = P_Line_Id
524 and SLD.Line_id = SL.Line_Id
525 and SLD.Included_Item_Flag = 'Y'
526 --and SLD.Component_Code = SL.Component_Code ||
527 -- ltrim(P_Component_Code, '0123456789');
528 and SLD.Component_Code = P_Component_Code;
529
530 return( SCHEDULE_STATUS_CODE );
531
532 Exception
533 WHEN NO_DATA_FOUND
534 then
535 return(NULL);
536 end ;
537
538
539
540 ------------------------------------------------------------------------
541 -- Return 'Y' if there are supply reserved details for this line.
542 -- Otherwise, return 'N'
543 -----------------------------------------------------------------------
544
545 function SUPPLY_RES_DETAILS(
546 P_Line_Id IN NUMBER DEFAULT NULL
547 )
548 return VARCHAR2
549 IS
550 Supply_Reserved_Details VARCHAR2(1):= 'N';
551
552 BEGIN
553
554 select 'Y'
555 into Supply_Reserved_Details
556 from so_line_details
557 where
558 line_id = P_Line_Id
559 and schedule_status_code = 'SUPPLY RESERVED'
560 and rownum = 1;
561
562 return(Supply_Reserved_Details);
563
564 EXCEPTION
565 when NO_DATA_FOUND
566 then
567 return('N');
568 END ;
569
570
571
572 ------------------------------------------------------------------------
573 -- Check any hold from line or header in the table SO_ORDER_HOLDS_ALL.
574 -----------------------------------------------------------------------
575 function HOLD(
576 ORDER_LINE_ID IN NUMBER DEFAULT NULL
577 , ORDER_HEADER_ID IN NUMBER DEFAULT NULL
578 )
579 return VARCHAR2
580 IS
581 HOLD_FLAG VARCHAR2(1);
582 BEGIN
583 SELECT DECODE( NVL(SUM(DECODE( HOLD_RELEASE_ID, NULL, 1, 0)),0),
584 0,'N', 'Y')
585 INTO HOLD_FLAG
586 FROM SO_ORDER_HOLDS_ALL
587 WHERE LINE_ID = ORDER_LINE_ID
588 OR (LINE_ID IS NULL AND HEADER_ID = ORDER_HEADER_ID);
589
590 RETURN( HOLD_FLAG );
591
592 EXCEPTION
593 WHEN NO_DATA_FOUND
594 THEN
595 RETURN('N');
596 END; -- HOLD
597
598 ----------------------------------------------------------------------
599 -- SHIPMENT_SCHEDULE_NUMBER is a better function to figure out
600 -- shipment_number because it takes service lines into consideration.
601 -- Should use SHIPMENT_SCHEDULE_NUMBER whenever possible.
602 ----------------------------------------------------------------------
603 function SHIPMENT_NUMBER(
604 ORDER_LINE_ID IN NUMBER DEFAULT NULL
605 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
606 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
607 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
608 )
609 return NUMBER
610 IS
611 SHIP_NUMBER NUMBER := NULL; -- default is NULL
612 BEGIN
613 IF ( ORDER_SHIP_SCHEDULE_LINE_ID IS NOT NULL ) -- if null, return null
614 THEN
615 IF( ORDER_PARENT_LINE_ID IS NULL )
616 THEN
617 SHIP_NUMBER := ORDER_LINE_NUMBER;
618 ELSE
619 SELECT LINE_NUMBER
620 INTO SHIP_NUMBER
621 FROM SO_LINES
622 WHERE LINE_ID = ORDER_PARENT_LINE_ID;
623 END IF;
624 END IF;
625
626 RETURN( SHIP_NUMBER);
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND
630 THEN
631 RETURN( NULL );
632 END ; -- SHIPMENT_NUMBER
633
634
635 ----------------------------------------------------------------------
636 -- BASE_LINE_NUMBER is a better function to figure out line_number
637 -- because it takes service lines into consideration.
638 -- Should use BASE_LINE_NUMBER whenever possible.
639 ----------------------------------------------------------------------
640 function LINE_NUMBER(
641 ORDER_LINE_ID IN NUMBER DEFAULT NULL
642 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
643 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
644 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
645 )
646 return NUMBER
647 IS
648 LINES_NUMBER NUMBER := NULL;
649 BEGIN
650
651 IF ( ORDER_SHIP_SCHEDULE_LINE_ID IS NULL)
652 THEN
653 IF ( ORDER_PARENT_LINE_ID IS NULL)
654 THEN
655 LINES_NUMBER := ORDER_LINE_NUMBER ;
656 ELSE
657 SELECT LINE_NUMBER
658 INTO LINES_NUMBER
659 FROM SO_LINES
660 WHERE LINE_ID = ORDER_PARENT_LINE_ID;
661 END IF;
662 ELSE
663 SELECT LINE_NUMBER
664 INTO LINES_NUMBER
665 FROM SO_LINES
666 WHERE LINE_ID = ORDER_SHIP_SCHEDULE_LINE_ID;
667 END IF;
668
669 RETURN( LINES_NUMBER);
670
671 EXCEPTION
672 WHEN NO_DATA_FOUND
673 THEN
674 RETURN( NULL );
675 END ;
676
677
678 ------------------------------------------------------------------------
679 function ITEM_CONC_SEG(
680 ITEM_ID IN NUMBER DEFAULT NULL
681 , ORG_ID IN NUMBER DEFAULT NULL
682 )
683 return VARCHAR2
684 IS
685 ITEM_NAME VARCHAR2(81);
686 BEGIN
687 SELECT CONCATENATED_SEGMENTS
688 INTO ITEM_NAME
689 FROM MTL_SYSTEM_ITEMS_KFV
690 WHERE INVENTORY_ITEM_ID = ITEM_ID
691 AND ORGANIZATION_ID = ORG_ID;
692
693 RETURN(ITEM_NAME);
694
695 EXCEPTION
696 WHEN NO_DATA_FOUND
697 THEN
698 RETURN( NULL );
699 END ; -- ITEM
700
701
702 ------------------------------------------------------------------------
703 -- Input a Order_Type_Id, search for so_headers to get the Order_Type
704 -- of that line.
705 ------------------------------------------------------------------------
706 function ORDER_TYPE(
707 ID IN NUMBER DEFAULT NULL
708 )
709 return VARCHAR2
710 IS
711 ORDER_TYPE VARCHAR2(80);
712 BEGIN
713 SELECT NAME
714 INTO ORDER_TYPE
715 FROM SO_ORDER_TYPES
716 WHERE ORDER_TYPE_ID = ID;
717
718 RETURN(ORDER_TYPE);
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND
722 THEN
723 RETURN( NULL );
724 END ;
725
726 --
727 -- NAME
728 -- Order_Total
729 --
730 --
731 FUNCTION Order_Total(Header_Id IN NUMBER) Return NUMBER IS
732
733 CURSOR C_Ord_Total(X_Header_Id NUMBER) IS
734 SELECT NVL(SUM( (NVL( l.ordered_quantity, 0) -
735 NVL( l.cancelled_quantity, 0)) *
736 NVL( l.selling_price, 0)), 0) ORDER_TOTAL
737 FROM so_lines l
738 WHERE l.header_id = X_Header_Id
739 AND l.line_type_code IN ('REGULAR', 'DETAIL','RETURN');
740
741 Ord_Total NUMBER;
742 begin
743 -- For bug# 610993. This was changing the record status to INSERT in case
744 -- of NEW records eventhough nothing is changed.(since the above select
745 -- returns value '0' if header_id is null)
746 IF Header_Id is NULL then
747 return NULL;
748 END IF;
749 OPEN C_Ord_Total(Header_Id);
750 FETCH C_Ord_Total INTO Ord_Total;
751 CLOSE C_Ord_Total;
752 return(Ord_Total);
753 end Order_Total;
754
755 function Shipment_Total(P_Line_Id IN NUMBER) return NUMBER
756 IS
757 line_total NUMBER := NULL ;
758 BEGIN
759
760 SELECT NVL(SUM( (NVL( ORDERED_QUANTITY, 0 ) -
761 NVL( CANCELLED_QUANTITY, 0 )) *
762 NVL(SELLING_PRICE, 0 ))
763 , 0)
764 INTO line_total
765 FROM SO_LINES
766 WHERE SHIPMENT_SCHEDULE_LINE_ID = P_Line_Id;
767
768 return(line_total);
769
770 Exception WHEN NO_DATA_FOUND then
771 return(NULL);
772
773 END;
774
775
776 FUNCTION Configuration_Total
777 (
778 Config_Parent_Line_Id IN NUMBER
779 ) RETURN NUMBER
780
781 is
782
783 L_Configuration_Total NUMBER;
784
785 begin
786
787
788 SELECT NVL(SUM((NVL(ORDERED_QUANTITY, 0 ) -
789 NVL( CANCELLED_QUANTITY, 0 )) *
790 NVL(SELLING_PRICE, 0 )),0)
791 INTO L_Configuration_Total
792 FROM SO_LINES
793 WHERE (LINE_ID = Config_Parent_Line_Id
794 OR PARENT_LINE_ID = Config_Parent_Line_Id
795 OR SERVICE_PARENT_LINE_ID =
796 Config_Parent_Line_Id );
797
798 return(L_Configuration_Total);
799
800 end Configuration_Total;
801
802
803 ----------------------------------------------------------------
804 -- Returns Order Number if Copied Order
805 ----------------------------------------------------------------
806 Function Source_Order_number
807 ( P_ORIGINAL_SYSTEM_SOURCE_CODE VARCHAR2,
808 P_ORIGINAL_SYSTEM_REFERENCE VARCHAR2
809 ) return VARCHAR2 is
810 rtn_value varchar2(50);
811 begin
812 if P_ORIGINAL_SYSTEM_SOURCE_CODE = '2' then
813 begin
814 select to_char(ord.order_number)
815 into rtn_value
816 from so_headers ord
817 where ord.header_id = decode(rtrim(p_original_system_reference,
818 '0123456789'),
819 null,to_number(p_original_system_reference),
820 null);
821 exception when others then
822 rtn_value := p_original_system_reference;
823 end;
824 else
825 rtn_value := p_original_system_reference;
826 end if;
827 return(rtn_value);
828 end Source_Order_number;
829 ----------------------------------------------------------------
830 -- Returns Order Type of the source Order
831 ----------------------------------------------------------------
832 Function Source_Order_Type
833 ( P_ORIGINAL_SYSTEM_SOURCE_CODE VARCHAR2,
834 P_ORIGINAL_SYSTEM_REFERENCE VARCHAR2
835 ) return VARCHAR2 is
836 rtn_value varchar2(50);
837 begin
838 if P_ORIGINAL_SYSTEM_SOURCE_CODE = '2' then
839 begin
840 select typ.name
841 into rtn_value
842 from so_headers ord,
843 so_order_types typ
844 where typ.order_type_id = ord.order_type_id
845 and ord.header_id = decode(rtrim(p_original_system_reference,
846 '0123456789'),
847 null,to_number(p_original_system_reference),
848 null);
849 exception when others then
850 rtn_value := NULL;
851 end;
852 else
853 rtn_value := NULL;
854 end if;
855 return(rtn_value);
856 end Source_Order_Type;
857
858
859
860 -----------------------------------------------------------
861 --
862 -- RETURN LINES VIEW
863 --
864 -- Returns total received quantity of the given line_id
865 -- from the MTL_SO_RMA_INTERFACE table
866 --
867 -----------------------------------------------------------
868 function Received_qty( p_line_id NUMBER)
869 RETURN NUMBER IS
870
871 L_Qty_Ordered NUMBER;
872 l_qty_received number;
873 l_interface_id number;
874 l_cancel_qty so_lines.cancelled_quantity%TYPE;
875 l_link_to_line_id so_lines.link_to_line_id%TYPE;
876 l_item_type so_lines.item_type_code%TYPE;
877 l_ato_flag so_lines.ato_flag%TYPE;
878 l_included_item_count number;
879 L_S29 NUMBER;
880 L_Shipped_Quantity NUMBER;
881
882 BEGIN
883
884 select Ordered_Quantity,
885 nvl(cancelled_quantity, 0),
886 link_to_line_id,
887 item_type_code,
888 Ato_Flag,
889 S29,
890 Shipped_quantity
891 into L_Qty_Ordered,
892 l_cancel_qty,
893 l_link_to_line_id,
894 l_item_type,
895 L_Ato_Flag,
896 L_S29,
897 L_Shipped_quantity
898 from so_lines
899 where line_id = p_line_id;
900
901
902 IF (l_qty_ordered > l_cancel_qty) THEN
903 select floor(
904 nvl( min( mtlint.received_quantity *
905 (lin.Ordered_Quantity - nvl(lin.cancelled_quantity,0)) /
906 mtlint.interfaced_quantity),0))
907 into l_qty_received
908 from so_rma_mtl_int_v mtlint,
909 so_lines lin
910 where mtlint.rma_line_id = p_line_id
911 and lin.line_id = p_line_id
912 group by lin.line_id,
913 lin.ordered_quantity,
914 lin.cancelled_quantity;
915 ELSE /* Case where there are no items to receive for this line */
916 /* All RMA'ed items were cancelled. This causes divide by */
917 /* zero error in the above statement */
918
919 /* Original order reference exists for this line */
920 IF (l_link_to_line_id is not NULL) THEN
921
922 /* For PTO Models, we need get the included item ratios */
923 IF ((l_ato_flag = 'N') AND
924 ((l_item_type = 'CLASS') OR
925 (l_item_type = 'MODEL') OR
926 (l_item_type = 'KIT'))) THEN
927
928 SELECT count (line_detail_id)
929 INTO l_included_item_count
930 FROM so_line_details
931 WHERE line_id = l_link_to_line_id
932 AND included_item_flag = 'Y';
933
934 /* Components are frozen so ratios exist */
935 IF (l_included_item_count > 0) THEN
936 SELECT floor(nvl(min(MTLINT.Received_Quantity),0))
937 INTO l_qty_received
938 FROM SO_RMA_MTL_INT_DETAIL_V MTLINT
939 WHERE MTLINT.Detail_line_id = l_link_to_line_id
940 AND MTLINT.RMA_LINE_ID = P_Line_Id;
941
942 /* Components were not frozen at the time return was created. */
943 /* No ratios exist, so null out the field. */
944 ELSE
945 l_qty_received := NULL;
946
947 END IF;
948
949 /* Not a PTO model. No BOM explosion occurred, so received */
950 /* calcuation is straight forward, not requiring any ratios. */
951 ELSE
952 SELECT sum(nvl(MTLSRR.received_quantity,0))
953 INTO l_qty_received
954 FROM MTL_SO_RMA_RECEIPTS MTLSRR
955 , MTL_SO_RMA_INTERFACE MTLSRI
956 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
957 AND MTLSRI.RMA_LINE_ID = p_line_id;
958 END IF;
959
960 /* No original reference exists. No BOM explosion occurred */
961 /* so received calcuation is straight forward, not requiring */
962 /* any ratios. */
963 ELSE
964 SELECT sum(nvl(MTLSRR.received_quantity,0))
965 INTO l_qty_received
966 FROM MTL_SO_RMA_RECEIPTS MTLSRR
967 , MTL_SO_RMA_INTERFACE MTLSRI
968 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
969 AND MTLSRI.RMA_LINE_ID = p_line_id;
970 END IF;
971 END IF;
972
973 IF (l_s29 = 17 and l_qty_received = 0 ) then
974 select min(rma_interface_id)
975 into l_interface_id
976 from mtl_so_rma_interface
977 where rma_line_id = p_line_id;
978 ELSE
979 RETURN(l_qty_received);
980 END IF;
981
982 RETURN(l_qty_received);
983
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 l_qty_received := nvl(l_shipped_quantity,0);
987 RETURN(l_qty_received);
988
989 END;
990
991
992
993 -----------------------------------------------------------
994 --
995 -- RETURN LINES VIEW
996 --
997 -- Returns the total accepted quantity of the given line_id
998 -- from the MTL_SO_RMA_INTERFACE table
999 --
1000 -----------------------------------------------------------
1001 function Accepted_qty( p_line_id NUMBER)
1002 RETURN NUMBER IS
1003
1004 L_Qty_Ordered NUMBER;
1005 l_qty_received number;
1006 l_interface_id number;
1007 l_cancel_qty so_lines.cancelled_quantity%TYPE;
1008 l_link_to_line_id so_lines.link_to_line_id%TYPE;
1009 l_item_type so_lines.item_type_code%TYPE;
1010 l_ato_flag so_lines.ato_flag%TYPE;
1011 l_included_item_count number;
1012 L_S29 NUMBER;
1013 L_Shipped_Quantity NUMBER;
1014
1015 BEGIN
1016
1017 select Ordered_Quantity,
1018 nvl(cancelled_quantity, 0),
1019 link_to_line_id,
1020 item_type_code,
1021 Ato_Flag,
1022 S29,
1023 Shipped_quantity
1024 into L_Qty_Ordered,
1025 l_cancel_qty,
1026 l_link_to_line_id,
1027 l_item_type,
1028 L_Ato_Flag,
1029 L_S29,
1030 L_Shipped_quantity
1031 from so_lines
1032 where line_id = p_line_id;
1033
1034
1035 IF (l_qty_ordered > l_cancel_qty) THEN
1036 select floor(
1037 nvl( min( mtlint.accepted_quantity *
1038 (lin.Ordered_Quantity - nvl(lin.cancelled_quantity,0)) /
1039 mtlint.interfaced_quantity),0))
1040 into l_qty_received
1041 from so_rma_mtl_int_v mtlint,
1042 so_lines lin
1043 where mtlint.rma_line_id = p_line_id
1044 and lin.line_id = p_line_id
1045 group by lin.line_id,
1046 lin.ordered_quantity,
1047 lin.cancelled_quantity;
1048 ELSE /* Case where there are no items to receive for this line */
1049 /* All RMA'ed items were cancelled. This causes divide by */
1050 /* zero error in the above statement */
1051
1052 /* Original order reference exists for this line */
1053 IF (l_link_to_line_id is not NULL) THEN
1054
1055 /* For PTO Models, we need get the included item ratios */
1056 IF ((l_ato_flag = 'N') AND
1057 ((l_item_type = 'CLASS') OR
1058 (l_item_type = 'MODEL') OR
1059 (l_item_type = 'KIT'))) THEN
1060
1061 SELECT count (line_detail_id)
1062 INTO l_included_item_count
1063 FROM so_line_details
1064 WHERE line_id = l_link_to_line_id
1065 AND included_item_flag = 'Y';
1066
1067 /* Components are frozen so ratios exist */
1068 IF (l_included_item_count > 0) THEN
1069 SELECT floor(nvl(min(MTLINT.Accepted_Quantity),0))
1070 INTO l_qty_received
1071 FROM SO_RMA_MTL_INT_DETAIL_V MTLINT
1072 WHERE MTLINT.Detail_line_id = l_link_to_line_id
1073 AND MTLINT.RMA_LINE_ID = P_Line_Id;
1074
1075 /* Components were not frozen at the time return was created. */
1076 /* No ratios exist, so null out the field. */
1077 ELSE
1078 l_qty_received := NULL;
1079
1080 END IF;
1081
1082 /* Not a PTO model. No BOM explosion occurred, so received */
1083 /* calcuation is straight forward, not requiring any ratios. */
1084 ELSE
1085 SELECT sum(nvl(MTLSRR.accepted_quantity,0))
1086 INTO l_qty_received
1087 FROM MTL_SO_RMA_RECEIPTS MTLSRR
1088 , MTL_SO_RMA_INTERFACE MTLSRI
1089 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
1090 AND MTLSRI.RMA_LINE_ID = p_line_id;
1091 END IF;
1092
1093 /* No original reference exists. No BOM explosion occurred */
1094 /* so received calcuation is straight forward, not requiring */
1095 /* any ratios. */
1096 ELSE
1097 SELECT sum(nvl(MTLSRR.accepted_quantity,0))
1098 INTO l_qty_received
1099 FROM MTL_SO_RMA_RECEIPTS MTLSRR
1100 , MTL_SO_RMA_INTERFACE MTLSRI
1101 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
1102 AND MTLSRI.RMA_LINE_ID = p_line_id;
1103 END IF;
1104 END IF;
1105
1106 IF (l_s29 = 17 and l_qty_received = 0 ) then
1107 select min(rma_interface_id)
1108 into l_interface_id
1109 from mtl_so_rma_interface
1110 where rma_line_id = p_line_id;
1111 ELSE
1112 RETURN(l_qty_received);
1113 END IF;
1114
1115 RETURN(l_qty_received);
1116
1117 EXCEPTION
1118 WHEN NO_DATA_FOUND THEN
1119 l_qty_received := nvl(l_shipped_quantity,0);
1120 RETURN(l_qty_received);
1121
1122 END;
1123
1124
1125
1126 -----------------------------------------------------------
1127 --
1128 -- RETURN LINES VIEW
1129 --
1130 -- Returns the latest received date for the given line_id
1131 -- from the MTL_SO_RMA_INTERFACE table
1132 --
1133 -----------------------------------------------------------
1134 function Received_Date( p_line_id NUMBER,
1135 P_S29_DATE DATE)
1136 RETURN DATE IS
1137
1138 L_Received_Date DATE;
1139
1140 BEGIN
1141
1142 SELECT MAX( MTLSRR.RECEIPT_DATE )
1143 INTO L_Received_Date
1144 FROM MTL_SO_RMA_RECEIPTS MTLSRR
1145 , MTL_SO_RMA_INTERFACE MTLSRI
1146 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
1147 AND MTLSRI.RMA_LINE_ID = p_line_id
1148 AND MTLSRR.RECEIVED_QUANTITY > 0;
1149
1150 RETURN (L_RECEIVED_DATE);
1151
1152 EXCEPTION
1153 WHEN NO_DATA_FOUND THEN
1154 RETURN (P_S29_DATE);
1155 END;
1156
1157
1158
1159
1160
1161 -----------------------------------------------------------
1162 --
1163 -- RETURN LINES VIEW
1164 --
1165 -- Returns the latest accetped date for the given line_id
1166 -- from the MTL_SO_RMA_INTERFACE table
1167 --
1168 -----------------------------------------------------------
1169 function Accepted_Date( p_line_id NUMBER,
1170 P_S29_DATE DATE)
1171 RETURN DATE IS
1172
1173 L_Accepted_Date DATE;
1174
1175 BEGIN
1176
1177 SELECT MAX( MTLSRR.RECEIPT_DATE )
1178 INTO L_Accepted_Date
1179 FROM MTL_SO_RMA_RECEIPTS MTLSRR
1180 , MTL_SO_RMA_INTERFACE MTLSRI
1181 WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
1182 AND MTLSRI.RMA_LINE_ID = p_line_id
1183 AND MTLSRR.ACCEPTED_QUANTITY > 0;
1184
1185 RETURN (L_ACCEPTED_DATE);
1186
1187 EXCEPTION
1188 WHEN NO_DATA_FOUND THEN
1189 RETURN (P_S29_DATE);
1190 END;
1191
1192
1193
1194 -----------------------------------------------------------
1195 --
1196 -- RETURN LINES VIEW
1197 --
1198 -- Given the reference code, select tax exemption flag from
1199 -- the proper source ( reference invoice, reference order,
1200 -- or return order ) and return the tax exemption flag
1201 -- display value.
1202 --
1203 -----------------------------------------------------------
1204 FUNCTION GET_TAX_EXEMPT_FLAG(
1205 P_Reference_Code VARCHAR2,
1206 P_Invoice_Flag VARCHAR2,
1207 P_Order_Flag VARCHAR2,
1208 P_No_Ref_Flag VARCHAR2,
1209 P_Open_Flag VARCHAR2
1210 )
1211 RETURN VARCHAR2 IS
1212
1213 L_FLAG VARCHAR2(5);
1214 L_FLAG_DISPLAY VARCHAR2(80);
1215
1216 BEGIN
1217
1218 IF (P_Reference_Code = 'INVOICE') THEN
1219 IF (P_OPEN_FLAG = 'Y') THEN
1220 L_Flag := P_Invoice_Flag;
1221 ELSE
1222 L_FLag := NULL;
1223 END IF;
1224 ELSIF (P_Reference_Code IN ('ORDER','PO')) THEN
1225 L_Flag := P_Order_Flag;
1226 ELSE
1227 L_Flag := P_No_Ref_Flag;
1228 END IF;
1229
1230 SELECT MEANING
1231 INTO L_Flag_Display
1232 FROM AR_LOOKUPS
1233 WHERE LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
1234 AND LOOKUP_CODE = L_Flag;
1235
1236
1237 RETURN L_FLAG_DISPLAY;
1238
1239 EXCEPTION
1240 WHEN NO_DATA_FOUND THEN
1241 RETURN NULL;
1242
1243 END;
1244
1245
1246 -----------------------------------------------------------
1247 --
1248 -- RETURN LINES VIEW
1249 --
1250 -- Given the reference code, select tax exemption reason
1251 -- from theproper source ( reference invoice, reference
1252 -- order, or returnorder ) and return the tax exemption
1253 -- reason display value.
1254 --
1255 -----------------------------------------------------------
1256 FUNCTION GET_TAX_EXEMPT_REASON(
1257 P_Reference_Code VARCHAR2,
1258 P_Invoice_reason VARCHAR2,
1259 P_Order_reason VARCHAR2,
1260 P_No_Ref_reason VARCHAR2,
1261 P_Open_Flag VARCHAR2
1262 )
1263 RETURN VARCHAR2 IS
1264
1265 L_REASON VARCHAR2(30);
1266 L_REASON_DISPLAY VARCHAR2(80);
1267
1268 BEGIN
1269
1270 IF (P_Reference_Code = 'INVOICE') THEN
1271 IF (P_OPEN_FLAG = 'Y') THEN
1272 L_REASON := P_Invoice_Reason;
1273 ELSE
1274 L_REASON := NULL;
1275 END IF;
1276 ELSIF (P_Reference_Code IN ('ORDER','PO')) THEN
1277 L_REASON := P_Order_Reason;
1278 ELSE
1279 L_REASON := P_No_Ref_Reason;
1280 END IF;
1281
1282 SELECT MEANING
1283 INTO L_REASON_DISPLAY
1284 FROM AR_LOOKUPS
1285 WHERE LOOKUP_CODE = L_REASON
1286 AND LOOKUP_TYPE = 'TAX_REASON';
1287
1288 RETURN L_REASON_DISPLAY;
1289
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 RETURN NULL;
1293
1294 END;
1295
1296
1297 -----------------------------------------------------------
1298 --
1299 -- RETURN LINES VIEW
1300 --
1301 -- Return the price adjustment total for the given line.
1302 -- This isthe sum of the line and header price adjustments
1303 -- for the given line.
1304 --
1305 -----------------------------------------------------------
1306 FUNCTION GET_PRICE_ADJ_TOTAL(
1307 P_HEADER_ID NUMBER,
1308 P_LINE_ID NUMBER
1309 )
1310 RETURN NUMBER IS
1311
1312 L_Total NUMBER;
1313
1314 BEGIN
1315
1316 SELECT NVL( SUM( PERCENT ), 0 )
1317 INTO L_TOTAL
1318 FROM SO_PRICE_ADJUSTMENTS
1319 WHERE HEADER_ID = P_HEADER_ID
1320 AND (LINE_ID IS NULL
1321 OR LINE_ID = P_LINE_ID);
1322
1323 RETURN L_TOTAL;
1324
1325 END;
1326
1327 --
1328 -- NAME
1329 -- Std_Tax_Exemption
1330 --
1331 PROCEDURE Get_Std_Tax_Exemption(Ship_To_Site_Use_Id IN NUMBER,
1332 Invoice_To_customer_id IN NUMBER,
1333 Date_Ordered IN DATE,
1334 Tax_Exempt_Number OUT NOCOPY VARCHAR2,
1335 Tax_Exempt_Reason OUT NOCOPY VARCHAR2) is
1336
1337 CURSOR C_Std_Tax_Exemption(X_Ship_To_Site_Use_Id NUMBER,
1338 X_Invoice_To_customer_id NUMBER,
1339 X_Date_Ordered DATE) is
1340 SELECT tax.tax_exempt_number,
1341 tax.tax_exempt_reason_meaning
1342 FROM tax_exemptions_qp_v tax
1343 WHERE tax.ship_to_site_use_id = X_Ship_To_Site_Use_Id
1344 AND tax.bill_to_customer_id = X_Invoice_To_customer_id
1345 AND trunc(NVL(X_Date_Ordered, SYSDATE))
1346 between trunc(tax.start_date) and
1347 trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
1348 AND tax.status_code = 'PRIMARY';
1349
1350 begin
1351 OPEN C_Std_Tax_Exemption(Ship_To_Site_Use_Id,
1352 Invoice_To_customer_id,
1353 Date_Ordered);
1354 FETCH C_Std_Tax_Exemption INTO Tax_Exempt_Number, Tax_Exempt_Reason;
1355 CLOSE C_Std_Tax_Exemption;
1356
1357 end;
1358
1359
1360 --
1361 -- Std_Tax_Exempt_Number
1362 --
1363 FUNCTION Std_Tax_Exempt_Number(Ship_To_Site_Use_Id IN NUMBER,
1364 Invoice_To_customer_id IN NUMBER,
1365 Date_Ordered IN DATE)
1366 Return VARCHAR2 is
1367 Tax_Exempt_Number VARCHAR2(80);
1368 Tax_Exempt_Reason VARCHAR2(80);
1369 begin
1370
1371 Get_Std_Tax_Exemption(Ship_To_Site_Use_Id,
1372 Invoice_To_customer_id,
1373 Date_Ordered,
1374 Tax_Exempt_Number,
1375 Tax_Exempt_Reason);
1376
1377 Return(Tax_Exempt_Number);
1378 end;
1379
1380 --
1381 -- Std_Tax_Exempt_Reason
1382 --
1383 FUNCTION Std_Tax_Exempt_Reason(Ship_To_Site_Use_Id IN NUMBER,
1384 Invoice_To_customer_id IN NUMBER,
1385 Date_Ordered IN DATE)
1386 Return VARCHAR2 is
1387 Tax_Exempt_Number VARCHAR2(80);
1388 Tax_Exempt_Reason VARCHAR2(80);
1389 begin
1390
1391 Get_Std_Tax_Exemption(Ship_To_Site_Use_Id,
1392 Invoice_To_customer_id,
1393 Date_Ordered,
1394 Tax_Exempt_Number,
1395 Tax_Exempt_Reason);
1396
1397 Return(Tax_Exempt_Reason);
1398
1399 end;
1400
1401 FUNCTION line_config_item_exists(X_line_id IN NUMBER) RETURN VARCHAR2 IS
1402 l_flag VARCHAR2(1) := 'N';
1403 BEGIN
1404
1405 SELECT 'Y'
1406 INTO l_flag
1407 FROM sys.dual
1408 WHERE EXISTS (SELECT 1
1409 FROM so_line_details
1410 WHERE line_id = X_line_id
1411 AND configuration_item_flag = 'Y');
1412
1413 IF (l_flag = 'Y') THEN
1414 RETURN('Y');
1415 ELSE
1416 RETURN('N');
1417 END IF;
1418
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN RETURN('N');
1421
1422
1423
1424 END line_config_item_exists;
1425
1426 FUNCTION line_released_qty(X_line_id IN NUMBER) RETURN NUMBER IS
1427 l_qty NUMBER := 0;
1428 BEGIN
1429
1430 SELECT SUM(NVL(quantity,0))
1431 INTO l_qty
1432 FROM so_line_Details
1433 WHERE line_id = X_line_id
1434 AND NVL(included_item_flag, 'N') = 'N'
1435 AND NVL(released_flag, 'Y') = 'Y'
1436 AND NVL(configuration_item_flag, 'N') =
1437 OE_QUERY.line_config_item_exists(X_line_id);
1438
1439 RETURN(l_qty);
1440
1441 EXCEPTION
1442 WHEN NO_DATA_FOUND THEN RETURN(NULL);
1443
1444 END line_released_qty;
1445
1446
1447 FUNCTION lot_expiration(X_inventory_item_id IN NUMBER,
1448 X_organization_id IN NUMBER,
1449 X_lot_number IN VARCHAR2) RETURN DATE IS
1450 l_expiration_date DATE;
1451 BEGIN
1452
1453 SELECT expiration_date
1454 INTO l_expiration_date
1455 FROM mtl_lot_numbers
1456 WHERE inventory_item_id = X_inventory_item_id
1457 AND organization_id = X_organization_id
1458 AND lot_number = X_lot_number;
1459
1460 RETURN(l_expiration_date);
1461
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN RETURN(NULL);
1464
1465 END lot_expiration;
1466
1467
1468 FUNCTION picking_line_reserved_qty(X_picking_line_id IN NUMBER)
1469 RETURN NUMBER IS
1470 l_num NUMBER := 0;
1471 BEGIN
1472
1473 SELECT NVL( SUM( NVL(requested_quantity,0)), 0)
1474 INTO l_num
1475 FROM so_picking_line_details
1476 WHERE schedule_status_code = 'RESERVED'
1477 AND picking_line_id = X_picking_line_id;
1478
1479 RETURN(l_num);
1480
1481 END picking_line_reserved_qty;
1482
1483
1484 --
1485 -- Open_Backordered_Quantity: This function returns the unreleased quantity
1486 -- from the backordered picking lines for a given line. It sums up the
1487 -- quantity in the SO_PICKING_LINE_DETAILS table for unreleased details.
1488 -- This function ignores picking lines for included items.
1489 -- For ATO Model lines, this quantity will be the backorered amount of the
1490 -- config item
1491 -- For Option Lines for ATO Models, it will return 0, as the options for ATO
1492 -- models never get backordered.
1493 --
1494 -- Input Argument: X_Line_Id is the line_id from the SO_LINES tables
1495 -- corresponding to the Order Line that you are interested in.
1496 --
1497 -- Return Value: Returns the total backordered amount for this order line,
1498 -- barring any included items.
1499 --
1500
1501 FUNCTION Open_Backordered_Quantity(X_line_id NUMBER) RETURN NUMBER IS
1502 l_backordered_quantity NUMBER := 0;
1503 BEGIN
1504
1505 SELECT Nvl(SUM(pld.requested_quantity),0)
1506 INTO l_backordered_quantity
1507 FROM
1508 so_picking_lines pl,
1509 so_picking_line_details pld
1510 WHERE
1511 Nvl(pld.released_flag, 'Y') = 'N'
1512 AND pld.picking_line_id = pl.picking_line_id
1513 AND pl.picking_header_id = 0
1514 AND Nvl(pl.included_item_flag, 'N') = 'N'
1515 AND pl.order_line_id = x_line_id;
1516
1517 RETURN l_backordered_quantity;
1518
1519 EXCEPTION
1520 WHEN NO_DATA_FOUND THEN
1521 RETURN 0;
1522 END Open_Backordered_Quantity;
1523
1524
1525 FUNCTION picking_line_item_id(X_picking_line_id IN NUMBER) return NUMBER is
1526 item_id NUMBER;
1527 begin
1528
1529 select inventory_item_id
1530 into item_id
1531 from so_picking_lines
1532 where picking_line_id = X_picking_line_id;
1533
1534 return (item_id);
1535
1536 end picking_line_item_id;
1537
1538 function ATP_Date_Line_Id(
1539 P_Session_id In Number,
1540 P_Line_Id In Varchar2,
1541 P_Inventory_Item_Id In Number)
1542 return Date
1543 Is
1544 L_ATP_Date Date;
1545 Begin
1546 Select NVL(Min(Group_Available_Date), Min(Request_ATP_Date))
1547 Into L_ATP_Date
1548 From MTL_Demand_Interface
1549 Where Session_Id = P_Session_Id
1550 And Demand_Source_Line = P_Line_Id
1551 And Inventory_Item_Id = P_Inventory_Item_Id
1552 And N_Column4 is Null
1553 Having Count(Distinct Group_Available_Date) = 1
1554 Or (Count(Distinct Group_Available_Date) = 0
1555 And Count(Distinct Request_ATP_Date) = 1);
1556
1557 return L_ATP_Date;
1558
1559 Exception WHEN Others Then
1560 return (NULL);
1561
1562 End ATP_Date_Line_Id;
1563
1564 function Available_Quantity_Line_Id(
1565 P_Session_id In Number,
1566 P_Line_Id In Varchar2,
1567 P_Inventory_Item_Id In Number)
1568 return Number
1569 Is
1570 L_Available_Qty Number;
1571 Begin
1572 Select Min(Request_Date_ATP_Quantity)
1573 Into L_Available_Qty
1574 From MTL_Demand_Interface
1575 Where Session_Id = P_Session_Id
1576 And Demand_Source_Line = P_Line_Id
1577 And Inventory_Item_Id = P_Inventory_Item_Id
1578 And Line_Item_Quantity > 0
1579 And N_Column4 is Null
1580 Having Count(Demand_Source_Delivery) = 1;
1581
1582 return L_Available_Qty;
1583
1584 Exception WHEN Others Then
1585 return (NULL);
1586 End Available_Quantity_Line_Id;
1587
1588 function Demand_Interface_RowId_Line_Id(
1589 P_Session_id In Number,
1590 P_Line_Id In Varchar2)
1591 return Varchar2
1592 Is
1593 L_Demand_Interface_RowId Varchar2(30);
1594 Begin
1595 Select Min(RowIdtoChar(RowId))
1596 Into L_Demand_Interface_RowId
1597 From MTL_Demand_Interface
1598 Where Session_Id = P_Session_Id
1599 And Demand_Source_Line = P_Line_Id
1600 And N_Column4 is Null
1601 And Line_Item_Quantity <> 0
1602 And Action_Code = 100
1603 Having Count(Distinct ATP_Group_Id) = 1;
1604
1605 return L_Demand_Interface_RowId;
1606 Exception
1607 When Others Then
1608 return (NULL);
1609 End Demand_Interface_RowId_Line_Id;
1610
1611 function ATP_Date_Delivery(
1612 P_Session_id In Number,
1613 P_Delivery In Varchar2)
1614 return Date
1615 Is
1616 L_ATP_Date Date;
1617 Begin
1618 Select NVL(Min(Group_Available_Date), Min(Request_ATP_Date))
1619 Into L_ATP_Date
1620 From MTL_Demand_Interface
1621 Where Session_Id = P_Session_Id
1622 And Demand_Source_Delivery = P_Delivery
1623 And N_Column4 is Null
1624 Having Count(Distinct Group_Available_Date) = 1
1625 Or (Count(Distinct Group_Available_Date) = 0
1626 And Count(Distinct Request_ATP_Date) = 1);
1627
1628 return L_ATP_Date;
1629
1630 Exception WHEN Others Then
1631 return (NULL);
1632
1633 End ATP_Date_Delivery;
1634
1635 function Available_Quantity_Delivery(
1636 P_Session_id In Number,
1637 P_Delivery In Varchar2)
1638 return Number
1639 Is
1640 L_Available_Qty Number;
1641 Begin
1642 Select Min(Request_Date_ATP_Quantity)
1643 Into L_Available_Qty
1644 From MTL_Demand_Interface
1645 Where Session_Id = P_Session_Id
1646 And Demand_Source_Delivery = P_Delivery
1647 And Line_Item_Quantity > 0
1648 And N_Column4 is Null
1649 Having Count(Demand_Source_Delivery) = 1;
1650
1651 return L_Available_Qty;
1652
1653 Exception WHEN Others Then
1654 return (NULL);
1655 End Available_Quantity_Delivery;
1656
1657 function Demand_Interface_RowId_Del(
1658 P_Session_id In Number,
1659 P_Delivery In Varchar2)
1660 return Varchar2
1661 Is
1662 L_Demand_Interface_RowId Varchar2(30);
1663 Begin
1664 Select Min(RowIdtoChar(RowId))
1665 Into L_Demand_Interface_RowId
1666 From MTL_Demand_Interface
1667 Where Session_Id = P_Session_Id
1668 And Demand_Source_Line = P_Delivery
1669 And N_Column4 is Null
1670 And Line_Item_Quantity <> 0
1671 And Action_Code = 100
1672 Having Count(Distinct ATP_Group_Id) = 1;
1673
1674 return L_Demand_Interface_RowId;
1675 Exception
1676 When Others Then
1677 return (NULL);
1678 End Demand_Interface_RowId_Del;
1679
1680 function picking_line_schedule_status(
1681 P_LINE_ID IN NUMBER DEFAULT NULL
1682 )
1683 return VARCHAR2
1684 IS
1685 schedule_status_code VARCHAR2(30) := NULL;
1686 schedule_status VARCHAR2(80) := NULL;
1687 begin
1688 SELECT DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
1689 'RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
1690 0,
1691 DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
1692 'SUPPLY RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
1693 0,
1694 DECODE( NVL( SUM( DECODE(
1695 SPLD.SCHEDULE_STATUS_CODE,
1696 'DEMANDED', SPLD.REQUESTED_QUANTITY, 0 ) ),0 ),
1697 0, NULL,
1698 'DEMANDED' ),
1699 'SUPPLY RESERVED' ),
1700 'RESERVED' )
1701 INTO schedule_status_code
1702 FROM SO_PICKING_LINE_DETAILS SPLD
1703 WHERE SPLD.PICKING_LINE_ID = P_LINE_ID;
1704
1705 if ( schedule_status_code is not null )
1706 then
1707 select meaning
1708 into schedule_status
1709 from so_lookups
1710 where lookup_code = schedule_status_code
1711 and lookup_type = 'SCHEDULE STATUS';
1712 end if;
1713
1714 return( SCHEDULE_STATUS );
1715 Exception
1716 WHEN NO_DATA_FOUND
1717 then
1718 return(NULL);
1719 end picking_line_schedule_status;
1720
1721 function p_line_schedule_status_code(
1722 P_LINE_ID IN NUMBER DEFAULT NULL
1723 )
1724 return VARCHAR2
1725 IS
1726 schedule_status_code VARCHAR2(30) := NULL;
1727 begin
1728 SELECT DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
1729 'RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
1730 0,
1731 DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
1732 'SUPPLY RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
1733 0,
1734 DECODE( NVL( SUM( DECODE(
1735 SPLD.SCHEDULE_STATUS_CODE,
1736 'DEMANDED', SPLD.REQUESTED_QUANTITY, 0 ) ),0 ),
1737 0, NULL,
1738 'DEMANDED' ),
1739 'SUPPLY RESERVED' ),
1740 'RESERVED' )
1741 INTO schedule_status_code
1742 FROM SO_PICKING_LINE_DETAILS SPLD
1743 WHERE SPLD.PICKING_LINE_ID = P_LINE_ID;
1744
1745 return( SCHEDULE_STATUS_CODE );
1746
1747 Exception
1748 WHEN NO_DATA_FOUND
1749 then
1750 return(NULL);
1751 end p_line_schedule_status_code;
1752
1753 Function get_organization_name
1754 return VARCHAR2
1755 IS
1756 org_name VARCHAR2(60) := NULL;
1757 org_id NUMBER := NULL;
1758 Begin
1759 SELECT org_id
1760 INTO org_id
1761 from so_headers
1762 where rownum = 1;
1763
1764 if org_id IS NULL Then
1765 return(NULL);
1766 else
1767 Select name
1768 into org_name
1769 from hr_operating_units
1770 where organization_id = org_id
1771 and rownum = 1;
1772 return(org_name);
1773 end if;
1774 Exception
1775 WHEN NO_DATA_FOUND
1776 then
1777 return(NULL);
1778 end get_organization_name;
1779
1780 /* The order status is displayed as cancelled or Closed if the order
1781 is in the state of Cancelld or Closed and the entry status field
1782 will be set to non updatable. For other headers the entry status
1783 will display the result for the value in the column s1
1784 */
1785 function get_entry_status_name(p_open_flag in varchar2,
1786 p_cancelled_flag in varchar2,
1787 p_s1_id in number)
1788 return VARCHAR2 is
1789 p_entry_status_name varchar2(30);
1790 cursor c_entry_status(p_id number) is
1791 select name from so_results
1792 where result_id = p_id;
1793 begin
1794 if p_cancelled_flag = 'Y' then
1795 p_entry_status_name :=
1796 nvl(substr(fnd_message.get_string('OE','OE_MSG_CANCELLED'),1,30),
1797 'OE_MSG_CANCELLED');
1798 elsif nvl(p_open_flag,'N') = 'N' then
1799 p_entry_status_name :=
1800 nvl(substr(fnd_message.get_string('OE','OE_MSG_CLOSED'),1,30),
1801 'OE_MSG_CLOSED');
1802 else
1803 open c_entry_status(p_s1_id);
1804 fetch c_entry_status into p_entry_status_name;
1805 close c_entry_status;
1806 end if;
1807 return(p_entry_status_name);
1808 end;
1809
1810 function OPTION_LINE_NUMBER(
1811 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1812 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1813 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
1814 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
1815 )
1816 return NUMBER
1817 IS
1818 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
1819 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
1820 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
1821 OPTION_LINE_NUMBER NUMBER := NULL; -- default is NULL
1822 BEGIN
1823 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
1824 THEN
1825 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
1826 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
1827 V_LINE_NUMBER := P_LINE_NUMBER;
1828 ELSE
1829 SELECT SHIPMENT_SCHEDULE_LINE_ID,
1830 PARENT_LINE_ID,
1831 LINE_NUMBER
1832 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
1833 V_PARENT_LINE_ID,
1834 V_LINE_NUMBER
1835 FROM SO_LINES
1836 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
1837 END IF;
1838 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
1839 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
1840 OPTION_LINE_NUMBER := V_LINE_NUMBER;
1841 END IF;
1842 ELSE
1843 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
1844 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
1845 OPTION_LINE_NUMBER := V_LINE_NUMBER;
1846 END IF;
1847 END IF;
1848 END IF;
1849
1850 RETURN(OPTION_LINE_NUMBER);
1851
1852
1853 EXCEPTION
1854 WHEN NO_DATA_FOUND
1855 THEN
1856 RETURN( NULL );
1857 END ; -- OPTION_LINE_NUMBER
1858
1859 function INVOICE_BALANCE(
1860 P_CUSTOMER_TRX_ID IN NUMBER
1861 )
1862 return NUMBER
1863 IS
1864 v_balance NUMBER := NULL ;
1865 BEGIN
1866
1867 IF ( P_CUSTOMER_TRX_ID IS NOT NULL )
1868 THEN
1869 SELECT NVL(SUM(AMOUNT_DUE_REMAINING),0)
1870 INTO v_balance
1871 FROM AR_PAYMENT_SCHEDULES
1872 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
1873 END IF;
1874 RETURN(v_balance);
1875 EXCEPTION
1876 WHEN NO_DATA_FOUND
1877 THEN
1878 return(NULL);
1879 END; -- INVOICE_BALANCE
1880
1881 function INVOICE_AMOUNT(
1882 P_CUSTOMER_TRX_ID IN NUMBER
1883 )
1884 return NUMBER
1885 IS
1886 v_invoice NUMBER := NULL ;
1887 BEGIN
1888
1889 IF ( P_CUSTOMER_TRX_ID IS NOT NULL )
1890 THEN
1891 SELECT NVL(SUM(EXTENDED_AMOUNT),0)
1892 INTO v_invoice
1893 FROM RA_CUSTOMER_TRX_LINES
1894 WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
1895 END IF;
1896 RETURN(v_invoice);
1897 EXCEPTION
1898 WHEN NO_DATA_FOUND
1899 THEN
1900 return(NULL);
1901 END; -- INVOICE_AMOUNT
1902
1903 function CYCLE_REQUEST return number is
1904 request_id number := NULL;
1905 Begin
1906 Savepoint A;
1907 request_id := FND_REQUEST.SUBMIT_REQUEST (
1908 'OE',
1909 'OECMWC','',
1910 to_char(sysdate,'YYYY/MM/DD HH24:MI'),FALSE,
1911 chr(0),
1912 '', '', '', '', '', '', '', '', '',
1913 '', '', '', '', '', '', '', '', '', '',
1914 '', '', '', '', '', '', '', '', '', '',
1915 '', '', '', '', '', '', '', '', '', '',
1916 '', '', '', '', '', '', '', '', '', '',
1917 '', '', '', '', '', '', '', '', '', '',
1918 '', '', '', '', '', '', '', '', '', '',
1919 '', '', '', '', '', '', '', '', '', '',
1920 '', '', '', '', '', '', '', '', '', '',
1921 '', '', '', '', '', '', '', '', '', '');
1922 if request_id <> 0 Then
1923 Commit;
1924 Else
1925 Rollback to A;
1926 End If;
1927 Return Request_id;
1928 End;
1929
1930 function BASE_LINE_NUMBER(
1931 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1932 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1933 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
1934 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
1935 )
1936 return NUMBER
1937 IS
1938 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
1939 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
1940 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
1941 BASE_LINE_NUMBER NUMBER := NULL; -- default is NULL
1942 BEGIN
1943 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
1944 THEN
1945 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
1946 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
1947 V_LINE_NUMBER := P_LINE_NUMBER;
1948 ELSE
1949 SELECT SHIPMENT_SCHEDULE_LINE_ID,
1950 PARENT_LINE_ID,
1951 LINE_NUMBER
1952 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
1953 V_PARENT_LINE_ID,
1954 V_LINE_NUMBER
1955 FROM SO_LINES
1956 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
1957 END IF;
1958 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
1959 IF (V_PARENT_LINE_ID IS NULL) THEN
1960 BASE_LINE_NUMBER := V_LINE_NUMBER;
1961 ELSE
1962 SELECT LINE_NUMBER
1963 INTO BASE_LINE_NUMBER
1964 FROM SO_LINES
1965 WHERE LINE_ID = V_PARENT_LINE_ID;
1966 END IF;
1967 ELSE
1968 SELECT LINE_NUMBER
1969 INTO BASE_LINE_NUMBER
1970 FROM SO_LINES
1971 WHERE LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID;
1972 END IF;
1973
1974 RETURN(BASE_LINE_NUMBER);
1975
1976 EXCEPTION
1977 WHEN NO_DATA_FOUND
1978 THEN
1979 RETURN( NULL );
1980 END ; -- BASE_LINE_NUMBER
1981
1982 function SHIPMENT_SCHEDULE_NUMBER(
1983 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1984 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
1985 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
1986 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
1987 )
1988 return NUMBER
1989 IS
1990 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
1991 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
1992 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
1993 SCHEDULE_LINE_NUMBER NUMBER := NULL; -- default is NULL
1994 BEGIN
1995 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
1996 THEN
1997 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
1998 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
1999 V_LINE_NUMBER := P_LINE_NUMBER;
2000 ELSE
2001 SELECT SHIPMENT_SCHEDULE_LINE_ID,
2002 PARENT_LINE_ID,
2003 LINE_NUMBER
2004 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
2005 V_PARENT_LINE_ID,
2006 V_LINE_NUMBER
2007 FROM SO_LINES
2008 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
2009 END IF;
2010 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
2011 IF (V_PARENT_LINE_ID IS NULL) THEN
2012 SCHEDULE_LINE_NUMBER := V_LINE_NUMBER;
2013 ELSE
2014 SELECT LINE_NUMBER
2015 INTO SCHEDULE_LINE_NUMBER
2016 FROM SO_LINES
2017 WHERE LINE_ID = V_PARENT_LINE_ID;
2018 END IF;
2019 END IF;
2020
2021 RETURN(SCHEDULE_LINE_NUMBER);
2022
2023 EXCEPTION
2024 WHEN NO_DATA_FOUND
2025 THEN
2026 RETURN( NULL );
2027 END ; -- SHIPMENT_LINE_NUMBER
2028
2029 END OE_QUERY;
[Home] [Help]