1 PACKAGE BODY OEXVWCAN AS
2 /* $Header: OEXVWCNB.pls 115.3 99/07/16 08:17:08 porting shi $ */
3
4 ------------------------------------------------------------------------
5 function SHIPMENT_SCHEDULE_NUMBER(
6 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
7 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
8 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
9 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
10 )
11 return NUMBER
12 IS
13 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
14 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
15 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
16 SCHEDULE_LINE_NUMBER NUMBER := NULL; -- default is NULL
17 BEGIN
18 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
19 THEN
20 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
21 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
22 V_LINE_NUMBER := P_LINE_NUMBER;
23 ELSE
24 SELECT SHIPMENT_SCHEDULE_LINE_ID,
25 PARENT_LINE_ID,
26 LINE_NUMBER
27 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
28 V_PARENT_LINE_ID,
29 V_LINE_NUMBER
30 FROM SO_LINES
31 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
32 END IF;
33 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
34 IF (V_PARENT_LINE_ID IS NULL) THEN
35 SCHEDULE_LINE_NUMBER := V_LINE_NUMBER;
36 ELSE
37 SELECT LINE_NUMBER
38 INTO SCHEDULE_LINE_NUMBER
39 FROM SO_LINES
40 WHERE LINE_ID = V_PARENT_LINE_ID;
41 END IF;
42 END IF;
43
44 RETURN(SCHEDULE_LINE_NUMBER);
45
46 EXCEPTION
47 WHEN NO_DATA_FOUND
48 THEN
49 RETURN( NULL );
50 END ; -- SHIPMENT_LINE_NUMBER
51
52
53 ------------------------------------------------------------------------
54 function BASE_LINE_NUMBER(
55 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
56 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
57 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
58 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
59 )
60 return NUMBER
61 IS
62 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
63 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
64 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
65 BASE_LINE_NUMBER NUMBER := NULL; -- default is NULL
66 BEGIN
67 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
68 THEN
69 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
70 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
71 V_LINE_NUMBER := P_LINE_NUMBER;
72 ELSE
73 SELECT SHIPMENT_SCHEDULE_LINE_ID,
74 PARENT_LINE_ID,
75 LINE_NUMBER
76 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
77 V_PARENT_LINE_ID,
78 V_LINE_NUMBER
79 FROM SO_LINES
80 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
81 END IF;
82 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
83 IF (V_PARENT_LINE_ID IS NULL) THEN
84 BASE_LINE_NUMBER := V_LINE_NUMBER;
85 ELSE
86 SELECT LINE_NUMBER
87 INTO BASE_LINE_NUMBER
88 FROM SO_LINES
89 WHERE LINE_ID = V_PARENT_LINE_ID;
90 END IF;
91 ELSE
92 SELECT LINE_NUMBER
93 INTO BASE_LINE_NUMBER
94 FROM SO_LINES
95 WHERE LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID;
96 END IF;
97
98 RETURN(BASE_LINE_NUMBER);
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND
102 THEN
103 RETURN( NULL );
104 END ; -- BASE_LINE_NUMBER
105
106 ------------------------------------------------------------------------
107 function OPTION_LINE_NUMBER(
108 P_PARENT_LINE_ID IN NUMBER DEFAULT NULL
109 , P_SERVICE_PARENT_LINE_ID IN NUMBER DEFAULT NULL
110 , P_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER DEFAULT NULL
111 , P_LINE_NUMBER IN NUMBER DEFAULT NULL
112 )
113 return NUMBER
114 IS
115 V_SHIPMENT_SCHEDULE_LINE_ID NUMBER := NULL; -- default is NULL
116 V_PARENT_LINE_ID NUMBER := NULL; -- default is NULL
117 V_LINE_NUMBER NUMBER := NULL; -- default is NULL
118 OPTION_LINE_NUMBER NUMBER := NULL; -- default is NULL
119 BEGIN
120 IF ( P_SERVICE_PARENT_LINE_ID IS NULL )
121 THEN
122 V_SHIPMENT_SCHEDULE_LINE_ID := P_SHIPMENT_SCHEDULE_LINE_ID;
123 V_PARENT_LINE_ID := P_PARENT_LINE_ID;
124 V_LINE_NUMBER := P_LINE_NUMBER;
125 ELSE
126 SELECT SHIPMENT_SCHEDULE_LINE_ID,
127 PARENT_LINE_ID,
128 LINE_NUMBER
129 INTO V_SHIPMENT_SCHEDULE_LINE_ID,
130 V_PARENT_LINE_ID,
131 V_LINE_NUMBER
132 FROM SO_LINES
133 WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
134 END IF;
135 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NULL) THEN
136 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
137 OPTION_LINE_NUMBER := V_LINE_NUMBER;
138 END IF;
139 ELSE
140 IF (V_SHIPMENT_SCHEDULE_LINE_ID IS NOT NULL) THEN
141 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
142 OPTION_LINE_NUMBER := V_LINE_NUMBER;
143 END IF;
144 END IF;
145 END IF;
146
147 RETURN(OPTION_LINE_NUMBER);
148
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND
152 THEN
153 RETURN( NULL );
154 END ; -- OPTION_LINE_NUMBER
155
156 ----------------------------------------------------------------
157 function SUBTREE_EXISTS(
158 V_LINE_ID IN NUMBER
159 , V_REAL_PARENT_LINE_ID IN NUMBER
160 )
161 return VARCHAR2
162 IS
163 V_SUBTREE_EXISTS VARCHAR2(1);
164 BEGIN
165
166 SELECT 'Y'
167 INTO V_SUBTREE_EXISTS
168 FROM SO_LINES
169 WHERE LINK_TO_LINE_ID = V_LINE_ID
170 AND PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
171 AND ITEM_TYPE_CODE <> 'SERVICE'
172 AND LINE_TYPE_CODE <> 'RETURN'
173 AND ROWNUM = 1;
174
175
176 RETURN(V_SUBTREE_EXISTS);
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND
180 THEN
181 RETURN('N');
182 END ; -- SUBTREE_EXISTS
183
184 ----------------------------------------------------------------
185 function IN_CONFIGURATION(
186 V_PARENT_LINE_ID IN NUMBER
187 , V_ITEM_TYPE_CODE IN VARCHAR2
188 , V_SERVICE_PARENT_LINE_ID IN NUMBER
189 )
190 return VARCHAR2
191 IS
192 RESULT VARCHAR2(1);
193 TEMP_ITEM_TYPE_CODE VARCHAR2(30);
194 BEGIN
195 IF (V_PARENT_LINE_ID IS NOT NULL) THEN
196 -- If parent_line_id is not null, we know this line is in a configuration
197 RESULT := 'Y';
198 ELSE
199 IF (V_ITEM_TYPE_CODE = 'MODEL') THEN
200 -- Top model line is also in a configuration
201 RESULT := 'Y';
202 ELSIF (V_ITEM_TYPE_CODE = 'SERVICE') THEN
203 -- Because there is no way that you can tell if a service line is attached
207 -- the service line is attached to.
204 -- to a top model line, in which case it is in a configuration,
205 -- or if the service line is attached to a standard line, in which case it
206 -- is not in a congifuration, we have to detect item_type_code of the line
208 IF (V_SERVICE_PARENT_LINE_ID IS NULL) THEN
209 -- A service line may have a NULL SERVICE_PARENT_LINE_ID, yet is not part of
210 -- a configuration.
211 RESULT := 'N';
212 ELSE
213 SELECT ITEM_TYPE_CODE
214 INTO TEMP_ITEM_TYPE_CODE
215 FROM SO_LINES
216 WHERE LINE_ID = V_SERVICE_PARENT_LINE_ID;
217
218 IF (TEMP_ITEM_TYPE_CODE = 'MODEL') THEN
219 RESULT := 'Y';
220 ELSE
221 RESULT := 'N';
222 END IF;
223 END IF;
224 ELSE
225 RESULT := 'N';
226 END IF;
227 END IF;
228 RETURN (RESULT);
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND
232 THEN
233 RETURN('N');
234 END ; -- IN_CONFIGURATION
235
236 ----------------------------------------------------------------
237 function OPEN_PICKING_SLIPS(
238 V_LINE_ID IN NUMBER
239 , V_REAL_PARENT_LINE_ID IN NUMBER
240 , V_COMPONENT_CODE IN VARCHAR2
241 )
242 return VARCHAR2
243 IS
244 V_OPEN_PICKING_SLIPS VARCHAR2(1);
245 V_SUBTREE_EXISTS VARCHAR2(1);
246 BEGIN
247
248 V_SUBTREE_EXISTS := OEXVWCAN.SUBTREE_EXISTS(V_LINE_ID,
249 V_REAL_PARENT_LINE_ID);
250
251 SELECT 'Y'
252 INTO V_OPEN_PICKING_SLIPS
253 FROM SO_PICKING_LINES SOL,
254 SO_PICKING_HEADERS SPH
255 WHERE SOL.PICKING_HEADER_ID = SPH.PICKING_HEADER_ID
256 AND SOL.ORDER_LINE_ID in
257 (SELECT LINE_ID
258 FROM SO_LINES
259 WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
260 AND NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE || '%'
261 AND V_SUBTREE_EXISTS = 'Y'
262 UNION
263 SELECT LINE_ID
264 FROM SO_LINES
265 WHERE LINE_ID = V_LINE_ID)
266 AND SPH.STATUS_CODE IN ('OPEN' ,'PENDING', 'IN PROGRESS')
267 AND ROWNUM = 1;
268
272 WHEN NO_DATA_FOUND
269 RETURN(V_OPEN_PICKING_SLIPS);
270
271 EXCEPTION
273 THEN
274 RETURN('N');
275 END ; -- OPEN_PICKING_SLIPS
276
277 ----------------------------------------------------------------
278 function PRICE_ADJUST_EXISTS(
279 V_HEADER_ID IN NUMBER
280 , V_LINE_ID IN NUMBER
281 )
282 return VARCHAR2
283 IS
284 V_PRICE_ADJUST_EXISTS VARCHAR2(1);
285 V_PRICE_ADJUST_COUNT NUMBER := NULL; -- default is NULL
286 BEGIN
287
288 SELECT COUNT(0)
289 INTO V_PRICE_ADJUST_COUNT
290 FROM SO_PRICE_ADJUSTMENTS PA,
291 SO_DISCOUNT_LINES DL
292 WHERE PA.HEADER_ID = V_HEADER_ID
293 AND PA.LINE_ID = V_LINE_ID
294 AND PA.DISCOUNT_LINE_ID = DL.DISCOUNT_LINE_ID
295 AND DL.PERCENT IS NULL
296 AND DL.AMOUNT IS NULL
297 AND DL.PRICE IS NULL;
298
299 IF V_PRICE_ADJUST_COUNT = 0
300 THEN
301 V_PRICE_ADJUST_EXISTS := 'N';
302 ELSE
303 V_PRICE_ADJUST_EXISTS := 'Y';
304 END IF;
305
306 RETURN(V_PRICE_ADJUST_EXISTS);
307 END ; -- PRICE_ADJUST_EXISTS
308
309
310 ----------------------------------------------------------------
311 function TOP_BILL_SEQUENCE_ID(
312 V_LINE_ID IN NUMBER DEFAULT NULL
313 , V_PARENT_LINE_ID IN NUMBER DEFAULT NULL
314 )
315 return NUMBER
316 IS
317 V_TOP_BILL_SEQUENCE_ID NUMBER;
318 BEGIN
319
320 SELECT COMPONENT_SEQUENCE_ID
321 INTO V_TOP_BILL_SEQUENCE_ID
322 FROM SO_LINES
323 WHERE LINE_ID = NVL(V_PARENT_LINE_ID,V_LINE_ID);
324
325 RETURN(V_TOP_BILL_SEQUENCE_ID);
326
327 EXCEPTION
328 WHEN NO_DATA_FOUND
329 THEN
330 RETURN(NULL);
331 END ; -- TOP_BILL_SEQUENCE_ID
332
333 ----------------------------------------------------------------
334 function SECURITY_OBJECT(
335 V_PARENT_LINE_ID IN NUMBER
336 , V_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER
337 , V_SERVICE_PARENT_LINE_ID IN NUMBER
338 , V_LINE_TYPE_CODE IN VARCHAR2
342 V_SECURITY_OBJECT VARCHAR(30);
339 )
340 return VARCHAR2
341 IS
343 BEGIN
344
345 if (V_LINE_TYPE_CODE = 'RETURN') then
346 V_SECURITY_OBJECT := 'RETURN_LINE';
347 RETURN(V_SECURITY_OBJECT);
348 end if;
349
350 if (V_SERVICE_PARENT_LINE_ID is NULL) then
351 if (V_SHIPMENT_SCHEDULE_LINE_ID is NULL) then
352 if (V_PARENT_LINE_ID is NULL) then
353 V_SECURITY_OBJECT := 'LINE';
354 else
355 V_SECURITY_OBJECT := 'OPTION';
356 end if;
357 else
358 if (V_PARENT_LINE_ID is NULL) then
359 V_SECURITY_OBJECT := 'SHIPMENT';
360 else
361 V_SECURITY_OBJECT := 'SHIP_OPTION';
362 end if;
363 end if;
364 else
365 if (V_SHIPMENT_SCHEDULE_LINE_ID is NULL) then
366 if (V_PARENT_LINE_ID is NULL) then
367 V_SECURITY_OBJECT := 'SERVICE';
368 else
369 V_SECURITY_OBJECT := 'OPTION_SERVICE';
370 end if;
371 else
372 if (V_PARENT_LINE_ID is NULL) then
373 V_SECURITY_OBJECT := 'SHIPMENT_SERVICE';
374 else
375 V_SECURITY_OBJECT := 'SHIP_OPTION_SERVICE';
376 end if;
377 end if;
378 end if;
379
380 RETURN(V_SECURITY_OBJECT);
381
382 END ; -- SECURITY_OBJECT
383
384
385 END OEXVWCAN;
[Home] [Help]