1 PACKAGE BODY SHPBKLOG AS
2 /* $Header: SHPBLOGB.pls 115.1 99/07/16 08:17:20 porting shi $ */
3
4 ------------------------------------------------------------------------
5
6 /* This function returns the backlogged amount of an order */
7
8 function ORDER_BACKLOG_AMOUNT(
9 O_HEADER_ID IN NUMBER DEFAULT NULL,
10 O_BACKLOG_QTY IN OUT NUMBER
11 )
12 return NUMBER
13 IS
14 L_BACKLOG_PRICE NUMBER := 0;
15 L_BACKLOG_QUANTITY NUMBER := 0;
16 O_BACKLOG_AMOUNT NUMBER := 0;
17
18 CURSOR backlog_line(a_header_id IN NUMBER) IS
19 select
20 nvl(l.selling_price,0),
21 SHPBKLOG.BACKLOG_QTY(l.line_id, l.item_type_code)
22 from
23 so_lines l
24 where
25 l.header_id = a_header_id and
26 l.line_type_code <> 'PARENT';
27
28 BEGIN
29
30 O_BACKLOG_QTY := 0;
31
32 OPEN backlog_line(O_HEADER_ID);
33 LOOP
34 FETCH backlog_line INTO
35 L_BACKLOG_PRICE,
36 L_BACKLOG_QUANTITY;
37 EXIT WHEN backlog_line%NOTFOUND;
38
39 O_BACKLOG_AMOUNT := O_BACKLOG_AMOUNT + (L_BACKLOG_PRICE * L_BACKLOG_QUANTITY);
40 O_BACKLOG_QTY := O_BACKLOG_QTY + L_BACKLOG_QUANTITY;
41
42 END LOOP;
43
44 RETURN(O_BACKLOG_AMOUNT);
45 END;
46
47
48
49
50 ------------------------------------------------------------------------
51
52 /* This function return the quantity backlogged per line */
53
54 FUNCTION BACKLOG_QTY(
55 O_LINE_ID IN NUMBER DEFAULT NULL,
56 ITEM_TYPE_CODE IN VARCHAR2 DEFAULT NULL
57 )
58 RETURN NUMBER
59 IS
60
61 SHIPPABLE VARCHAR2(1) := 'N';
62
63 SOMETHING_RELEASED VARCHAR2(1) := 'Y';
64
65 ALL_SHIPPABLE_RELEASED VARCHAR2(1) := 'Y';
66
67 ALL_DETAILS_RELEASED VARCHAR2(1) := 'N';
68
69 ORDERED_QTY NUMBER DEFAULT NULL;
70
71 CANCELLED_QTY NUMBER DEFAULT NULL;
72 BACK_QTY NUMBER := 0;
73 STANDARD_II_BACK_QTY NUMBER := 0;
74 MODEL_CLASS_BACK_QTY NUMBER := 0;
75 DUMMY NUMBER;
76
77 /* Assumptions:
78
79 1. Something was released (SOMETHING_RELEASED = 'Y'). This
80 flag is not restricted to shippable lines only.
81
82
83
84 2. All shippable details were released
85 (ALL_SHIPPABLE_RELEASED = 'Y').
86 Even though (below) we calculate the ALL_SHIPPABLE_RELEASED
87 flag only if the line is shippable and something was released,
88 that flag could be set to 'Y' by its default here.
89 Therefore whenever we want to make sure that a shippable
90 line was fully released we have to check all three flags.
91 (see caculcation of shippable lines and lines with included
92 items downstairs ...)
93
94 3. Line is not shippable
95
96 */
97
98 BEGIN
99
100
101 /* determine if line is shippable */
102
103 select nvl(min('Y'), 'N')
104 into SHIPPABLE
105 from dual
106 where exists
107 (select 'shippable detail'
108 from so_line_details
109 where line_id = O_LINE_ID
110 and shippable_flag = 'Y');
111
112 /* determine if anything was released */
113
114
115 select nvl(min('Y'), 'N')
116 into SOMETHING_RELEASED
117 from dual
118 where exists
119 (select 'released details'
120 from so_line_details
121 where nvl(released_flag, 'N') = 'Y'
122 and line_id = O_LINE_ID);
123
124
125 select nvl(min('Y'), 'N')
126 into ALL_DETAILS_RELEASED
127 from dual
128 where not exists
129 (select 'unreleased detail'
130 from so_line_details
131 where nvl(released_flag, 'N') = 'N'
132 and line_id = O_LINE_ID);
133
134
135 /* select ordered and cancelled quantity for the order line */
136
137 select
138 ordered_quantity,
139 nvl(cancelled_quantity, 0)
140 into
141 ORDERED_QTY,
142 CANCELLED_QTY
143 from
144 so_lines
145 where
146 line_id = O_LINE_ID;
147
148
149 /* if line is shippable and something was released
150 make sure that there are no unrepresented shippable
151 lines is so_picking_lines */
152
153
154 IF (SHIPPABLE = 'Y' and SOMETHING_RELEASED = 'Y')
155 then
156 select nvl(min('Y'), 'N')
157 into ALL_SHIPPABLE_RELEASED
158 from so_line_details ld
159 where ld.line_id = O_LINE_ID
160 and ld.shippable_flag = 'Y'
161 and ld.inventory_item_id+0 in
162 (select distinct spl.inventory_item_id+0
163 from so_picking_lines spl
164 where spl.order_line_id = O_LINE_ID
165 and spl.picking_header_id+0 not in (0, -1));
166
167 end if;
168
169
170 /* Determine Backlog Quantity */
171
172 /* shippable lines where not all distinct items
173 have been released are fully backlogged
174 OR
175 lines where nothing has been released yet */
176
177 IF (ALL_SHIPPABLE_RELEASED = 'N'
178 OR
179 SOMETHING_RELEASED = 'N') /* could be non-shippable */
180 THEN
181
182 BACK_QTY := ORDERED_QTY - CANCELLED_QTY;
183 RETURN(BACK_QTY);
184
185 END IF;
186
187 /* models and classes might have children
188 which have shippable details */
189
190 IF ((ITEM_TYPE_CODE = 'MODEL' or ITEM_TYPE_CODE = 'CLASS') and
191 SOMETHING_RELEASED = 'Y')
192 THEN
193 BEGIN
194
195 select
196 pl.picking_line_id
197 into
198 dummy
199 from
200 so_picking_headers ph,
201 so_picking_lines pl,
202 so_lines child,
203 so_lines parent
204 where
205 parent.line_id = O_LINE_ID and
206 child.parent_line_id = parent.line_id and
207 child.line_id = pl.order_line_id and
208 pl.picking_header_id = ph.picking_header_id and
209 ph.status_code in ('CLOSED', 'PENDING', 'OPEN') and
210 ROWNUM = 1;
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 RETURN(0);
215
216 END;
217
218 select
219 ORDERED_QTY -
220 CANCELLED_QTY -
221 nvl(floor(min(sum(nvl(pl.shipped_quantity,0))/ max(pl.component_ratio))), 0)
222 into
223 MODEL_CLASS_BACK_QTY
224 from
225 so_picking_lines pl,
226 so_picking_headers ph,
227 so_lines child,
228 so_lines parent
229 where
230 parent.line_id = O_LINE_ID and
231 child.parent_line_id = parent.line_id and
232 child.line_id = pl.order_line_id and
233 pl.picking_header_id = ph.picking_header_id and
234 ph.status_code in ('CLOSED', 'PENDING', 'OPEN') and
235 exists
236 (select 'child is shippable'
237 from so_line_details ld
238 where ld.line_id = child.line_id) and
239 not exists
240 (SELECT 'UNREPRESENTED COMPONENT'
241 FROM SO_LINE_DETAILS LD
242 WHERE LD.LINE_ID = child.LINE_ID
243 AND LD.SHIPPABLE_FLAG = 'Y'
244 AND LD.INVENTORY_ITEM_ID+0 NOT IN
245 (SELECT DISTINCT SPL.INVENTORY_ITEM_ID+0
246 FROM SO_PICKING_LINES SPL
247 WHERE SPL.ORDER_LINE_ID = LD.LINE_ID
248 AND SPL.PICKING_HEADER_ID+0 NOT IN (0, -1)))
249 group by
250 pl.inventory_item_id;
251
252 IF SQL%FOUND
253 THEN
254 IF MODEL_CLASS_BACK_QTY > BACK_QTY
255 THEN
256 BACK_QTY := MODEL_CLASS_BACK_QTY;
257 END IF;
258 RETURN(BACK_QTY);
259 END IF;
260 END IF;
261
262 /* shippable standard lines and lines w/ shippable included items
263 which have been fully released */
264
265 IF (ALL_SHIPPABLE_RELEASED = 'Y' AND SHIPPABLE = 'Y' AND SOMETHING_RELEASED = 'Y')
266 THEN
267
268 select
269 ORDERED_QTY -
270 CANCELLED_QTY -
271 nvl(floor(min(sum(nvl(pl.shipped_quantity,0)) / max(pl.component_ratio))), 0)
272 into
273 STANDARD_II_BACK_QTY
274 from
275 so_picking_lines pl,
276 so_picking_headers ph
277 where
278 pl.order_line_id = O_LINE_ID and
279 ph.picking_header_id = pl.picking_header_id and
280 ph.status_code in ('CLOSED', 'PENDING', 'OPEN')
281 group by
282 pl.inventory_item_id;
283
284 IF SQL%FOUND
285 THEN
286 IF STANDARD_II_BACK_QTY > BACK_QTY
287 THEN
288 BACK_QTY := STANDARD_II_BACK_QTY;
289 END IF;
290 END IF;
291
292 ELSE /* unrepresented components */
293
294 BACK_QTY := ORDERED_QTY - CANCELLED_QTY;
295
296
297 END IF;
298
299 /* If non-shippable and all the details are released and the item_type
300 is a standard, then do not show as backlog. */
301
302 IF (SHIPPABLE = 'N' AND ALL_DETAILS_RELEASED = 'Y' AND
303 ITEM_TYPE_CODE IN ('STANDARD', 'KIT'))
304 THEN
305
306 BACK_QTY := 0;
307
308 END IF;
309
310
311 RETURN(BACK_QTY);
312
313
314 END;
315
316
317 END SHPBKLOG;
[Home] [Help]