1 PACKAGE BODY OEXVWLIN AS
2 /* $Header: OEXVWLNB.pls 115.1 99/07/16 08:17:13 porting shi $ */
3
4 ------------------------------------------------------------------------
5 -- 1. if LINE_TYPE_CODE is 'PARENT', get the extended_price from the
6 -- shipment_schedule_line_id = current line_id
7 -- 2. if not, check the ITEM_TYPE_CODE
8 -- 2.1 if 'MODEL', get current line's extended_price
9 -- + parent_line_id = current line_id 's extended_price
10 -- + service_parent_line_id = current line_id 's extended_price
11 -- 2.2 if'SERVICE', check serviceable_duration, if 0 then total is 0
12 -- if not,get current extended_price
13 -- 2.3 otherwise, check service_flag='Y',
14 -- 2.3.1 TRUE : get current line's extend_price +
15 -- service_parent_line_id = current line_id 's extended price
16 -- 2.3.2 FALSE : get current line's extended_price
17 ------------------------------------------------------------------------
18 function LINE_TOTAL(
19 ORDER_ROWID IN VARCHAR2
20 , ORDER_LINE_ID IN NUMBER DEFAULT NULL
21 , LINE_TYPE_CODE IN VARCHAR2
22 , ITEM_TYPE_CODE IN VARCHAR2
23 , SERVICE_DURATION IN NUMBER
24 , SERVICEABLE_FLAG IN VARCHAR2
25 , ORDERED_QTY IN NUMBER
26 , CANCELLED_QTY IN NUMBER
27 , SELLING_PRICE IN NUMBER
28 )
29 return NUMBER
30 IS
31 order_line_total NUMBER := NULL ;
32 BEGIN
33
34 if ( LINE_TYPE_CODE = 'PARENT' )
35 then
36 SELECT NVL(SUM( (NVL( ORDERED_QUANTITY, 0 ) -
37 NVL( CANCELLED_QUANTITY, 0 )) *
38 NVL(SELLING_PRICE, 0 ))
39 , 0)
40 INTO order_line_total
41 FROM SO_LINES
42 WHERE SHIPMENT_SCHEDULE_LINE_ID = ORDER_LINE_ID;
43 elsif ( ITEM_TYPE_CODE = 'MODEL' )
44 then
45 SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
46 NVL( CANCELLED_QUANTITY, 0 )) *
47 NVL(SELLING_PRICE, 0 ))
48 , 0)
49 INTO order_line_total
50 FROM SO_LINES
51 WHERE (ROWID = ORDER_ROWID
52 OR PARENT_LINE_ID = ORDER_LINE_ID
53 OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
54 elsif (ITEM_TYPE_CODE = 'SERVICE')
55 then
56 if (SERVICE_DURATION = 0)
57 then
58 order_line_total := 0;
59 else
60 order_line_total := ( ORDERED_QTY - CANCELLED_QTY)
61 * SELLING_PRICE;
62 end if;
63 elsif (SERVICEABLE_FLAG= 'Y')
64 then
65 SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
66 NVL( CANCELLED_QUANTITY, 0 )) *
67 NVL(SELLING_PRICE, 0 ))
68 , 0)
69 INTO order_line_total
70 FROM SO_LINES
71 WHERE (ROWID = ORDER_ROWID
72 OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
73 else
74 order_line_total := ( ORDERED_QTY - CANCELLED_QTY)
75 * SELLING_PRICE;
76 end if ;
77
78 return(order_line_total);
79
80 Exception WHEN NO_DATA_FOUND then
81 return(NULL);
82
83 END;
84
85
86 ------------------------------------------------------------------------
87 -- The schedule status code priority is 'RESERVED' > 'SUPPLY RESERVED'
88 -- > 'DEMAND', return the highest priority code to the line. If none
89 -- of above, the status code is null;
90 ------------------------------------------------------------------------
91 function SCHEDULE_STATUS(
92 ORDER_LINE_ID IN NUMBER DEFAULT NULL
93 )
94 return VARCHAR2
95 IS
96 schedule_status_code VARCHAR2(30) := NULL;
97 schedule_status VARCHAR2(80) := NULL;
98 begin
99 SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
100 'RESERVED', SLD.QUANTITY, 0 ) ), 0),
101 0,
102 DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
103 'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
104 0,
105 DECODE( NVL( SUM( DECODE(
106 SLD.SCHEDULE_STATUS_CODE,
107 'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
108 0, NULL,
109 'DEMANDED' ),
110 'SUPPLY RESERVED' ),
111 'RESERVED' )
112 INTO schedule_status_code
113 FROM SO_LINE_DETAILS SLD
114 WHERE SLD.LINE_ID = ORDER_LINE_ID;
115
116 if ( schedule_status_code is not null )
117 then
118 select meaning
119 into schedule_status
120 from so_lookups
121 where lookup_code = schedule_status_code
122 and lookup_type = 'SCHEDULE STATUS';
123 end if;
124
125 return( SCHEDULE_STATUS );
126 Exception
127 WHEN NO_DATA_FOUND
128 then
129 return(NULL);
130 end ;
131
132
133 ------------------------------------------------------------------------
134 -- from So_Line_DETAILS table to get all reserved details quantity
135 ------------------------------------------------------------------------
136 function RESERVED_QUANTITY(
137 ORDER_LINE_ID IN NUMBER DEFAULT NULL
138 )
139 return NUMBER
140 IS
141 RESERVED_QTY NUMBER := NULL ;
142 BEGIN
143
144 select sum( decode( SCHEDULE_STATUS_CODE,
145 'RESERVED', QUANTITY,
146 0))
147 into RESERVED_QTY
148 from SO_LINE_DETAILS
149 where line_id = ORDER_LINE_ID
150 and NVL(INCLUDED_ITEM_FLAG, 'N')='N';
151
152 return( RESERVED_QTY);
153
154 EXCEPTION
155 when NO_DATA_FOUND
156 then
157 return(NULL);
158 END ;
159
160
161 ------------------------------------------------------------------------
162 -- Check any hold from line or header in the table SO_ORDER_HOLDS_ALL.
163 -----------------------------------------------------------------------
164 function HOLD(
165 ORDER_LINE_ID IN NUMBER DEFAULT NULL
166 , ORDER_HEADER_ID IN NUMBER DEFAULT NULL
167 )
168 return VARCHAR2
169 IS
170 HOLD_FLAG VARCHAR2(1);
171 BEGIN
172 SELECT DECODE( NVL(SUM(DECODE( HOLD_RELEASE_ID, NULL, 1, 0)),0),
173 0,'N', 'Y')
174 INTO HOLD_FLAG
175 FROM SO_ORDER_HOLDS_ALL
176 WHERE LINE_ID = ORDER_LINE_ID
177 OR (LINE_ID IS NULL AND HEADER_ID = ORDER_HEADER_ID);
178
179 RETURN( HOLD_FLAG );
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND
183 THEN
184 RETURN('N');
185 END; -- HOLD
186
187
188 ------------------------------------------------------------------------
189 function SHIPMENT_NUMBER(
190 ORDER_LINE_ID IN NUMBER DEFAULT NULL
191 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
192 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
193 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
194 )
195 return NUMBER
196 IS
197 SHIP_NUMBER NUMBER := NULL; -- default is NULL
198 BEGIN
199 IF ( ORDER_SHIP_SCHEDULE_LINE_ID IS NOT NULL ) -- if null, return null
200 THEN
201 IF( ORDER_PARENT_LINE_ID IS NULL )
202 THEN
203 SHIP_NUMBER := ORDER_LINE_NUMBER;
204 ELSE
205 SELECT LINE_NUMBER
206 INTO SHIP_NUMBER
207 FROM SO_LINES
208 WHERE LINE_ID = ORDER_PARENT_LINE_ID;
209 END IF;
210 END IF;
211
212 RETURN( SHIP_NUMBER);
213
214 EXCEPTION
215 WHEN NO_DATA_FOUND
216 THEN
217 RETURN( NULL );
218 END ; -- SHIPMENT_NUMBER
219
220
221 ------------------------------------------------------------------------
222 function LINE_NUMBER(
223 ORDER_LINE_ID IN NUMBER DEFAULT NULL
224 , ORDER_SHIP_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
225 , ORDER_PARENT_LINE_ID IN NUMBER DEFAULT NULL
226 , ORDER_LINE_NUMBER IN NUMBER DEFAULT NULL
227 )
228 return NUMBER
229 IS
230 LINES_NUMBER NUMBER := NULL;
231 BEGIN
232
233 IF ( ORDER_SHIP_SCHEDULE_LINE_ID IS NULL)
234 THEN
235 IF ( ORDER_PARENT_LINE_ID IS NULL)
236 THEN
237 LINES_NUMBER := ORDER_LINE_NUMBER ;
238 ELSE
239 SELECT LINE_NUMBER
240 INTO LINES_NUMBER
241 FROM SO_LINES
242 WHERE LINE_ID = ORDER_PARENT_LINE_ID;
243 END IF;
244 ELSE
245 SELECT LINE_NUMBER
246 INTO LINES_NUMBER
247 FROM SO_LINES
248 WHERE LINE_ID = ORDER_SHIP_SCHEDULE_LINE_ID;
249 END IF;
250
251 RETURN( LINES_NUMBER);
252
253 EXCEPTION
254 WHEN NO_DATA_FOUND
255 THEN
256 RETURN( NULL );
257 END ;
258
259
260 ------------------------------------------------------------------------
261 function ITEM_CONC_SEG(
262 ITEM_ID IN NUMBER DEFAULT NULL
263 , ORG_ID IN NUMBER DEFAULT NULL
264 )
265 return VARCHAR2
266 IS
267 ITEM_NAME VARCHAR2(81);
268 BEGIN
269 SELECT CONCATENATED_SEGMENTS
270 INTO ITEM_NAME
271 FROM MTL_SYSTEM_ITEMS_KFV
272 WHERE INVENTORY_ITEM_ID = ITEM_ID
273 AND ORGANIZATION_ID = ORG_ID;
274
275 RETURN(ITEM_NAME);
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND
279 THEN
280 RETURN( NULL );
281 END ; -- ITEM
282
283
284 ------------------------------------------------------------------------
285 -- Input a Order_Type_Id, search for so_headers to get the Order_Type
286 -- of that line.
290 )
287 ------------------------------------------------------------------------
288 function ORDER_TYPE(
289 ID IN NUMBER DEFAULT NULL
291 return VARCHAR2
292 IS
293 ORDER_TYPE VARCHAR2(80);
294 BEGIN
295 SELECT NAME
296 INTO ORDER_TYPE
297 FROM SO_ORDER_TYPES
298 WHERE ORDER_TYPE_ID = ID;
299
300 RETURN(ORDER_TYPE);
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND
304 THEN
305 RETURN( NULL );
306 END ;
307
308
309 END OEXVWLIN;
[Home] [Help]