[Home] [Help]
PACKAGE BODY: APPS.GML_AUTO_ALLOC
Source
1 PACKAGE BODY GML_AUTO_ALLOC AS
2 /*$Header: GMLALLCB.pls 115.6 2002/11/08 15:44:48 gmangari noship $*/
3
4 FUNCTION Get_Available_Lots (V_session_id NUMBER, V_alloc_mode VARCHAR2, V_line_id NUMBER, V_item_id NUMBER, V_shipcust_id NUMBER,
5 V_whse_code VARCHAR2, V_qty NUMBER, V_order_um1 VARCHAR2, V_qty2 NUMBER,
6 V_grade_wanted VARCHAR2, V_sched_shipdate DATE) RETURN NUMBER IS
7 CURSOR Cur_get_item_details IS
8 SELECT item_no, lot_ctl, loct_ctl, grade_ctl, alloc_class, item_um, item_um2, dualum_ind,
9 lot_indivisible
10 FROM ic_item_mst
11 WHERE item_id = V_item_id;
12 ItemDetailsRec Cur_get_item_details%ROWTYPE;
13
14 CURSOR Cur_get_whse_details IS
15 SELECT loct_ctl
16 FROM ic_whse_mst
17 WHERE whse_code = V_whse_code;
18 WhseDetailsRec Cur_get_whse_details%ROWTYPE;
19 CURSOR Cur_check_alloc IS
20 SELECT 1
21 FROM sys.dual
22 WHERE EXISTS (SELECT 1
23 FROM ic_tran_pnd
24 WHERE line_id = V_line_id
25 AND (lot_id > 0 OR location <> P_default_loct)
26 AND delete_mark = 0
27 AND doc_type = 'OPSO'
28 AND trans_qty <> 0);
29 X_exists NUMBER(5);
30 X_plan_qty NUMBER;
31 X_alloc_method NUMBER(5);
32 X_shelf_days NUMBER(5);
33 X_alloc_horizon NUMBER(5);
34 X_alloc_type NUMBER(5);
35 X_lot_qty NUMBER(5);
36 X_partial_ind NUMBER(5);
37 X_prefqc_grade VARCHAR2(4);
38 X_trans_date DATE;
39 X_temp_date DATE;
40 X_return_val NUMBER(5);
41 UOM_CONVERSION_ERROR EXCEPTION;
42 ITEM_NOT_FOUND EXCEPTION;
43 WHSE_NOT_FOUND EXCEPTION;
44 ITEM_WHSE_NOT_LOT_LOCT EXCEPTION;
45 ITEM_ALLOCATION_EXISTS EXCEPTION;
46 INVALID_ALLOCATION_MODE EXCEPTION;
47 ITEM_MISSING_ALLOCATION_CLASS EXCEPTION;
48 X_PROCEED_ALLOC BOOLEAN DEFAULT TRUE;
49
50 BEGIN
51 P_session_id := V_session_id;
52 OPEN Cur_get_item_details;
53 FETCH Cur_get_item_details INTO ItemDetailsRec;
54 IF Cur_get_item_details%NOTFOUND THEN
55 CLOSE Cur_get_item_details;
56 RAISE ITEM_NOT_FOUND;
57 END IF;
58 CLOSE Cur_get_item_details;
59
60 OPEN Cur_get_whse_details;
61 FETCH Cur_get_whse_details INTO WhseDetailsRec;
62 IF Cur_get_whse_details%NOTFOUND THEN
63 RAISE WHSE_NOT_FOUND;
64 END IF;
65 CLOSE Cur_get_whse_details;
66
67 IF ItemDetailsRec.alloc_class IS NULL THEN
68 RAISE ITEM_MISSING_ALLOCATION_CLASS;
69 ELSIF (ItemDetailsRec.lot_ctl = 0) AND
70 (ItemDetailsRec.loct_ctl * WhseDetailsRec.loct_ctl = 0) THEN
71 RAISE ITEM_WHSE_NOT_LOT_LOCT;
72 END IF;
73
74 P_default_loct := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
75
76 /* Check to see wether allocations exist */
77 OPEN Cur_check_alloc;
78 FETCH Cur_check_alloc INTO X_exists;
79 IF Cur_check_alloc%FOUND THEN
80 CLOSE Cur_check_alloc;
81 RAISE ITEM_ALLOCATION_EXISTS;
82 END IF;
83 CLOSE Cur_check_alloc;
84
85 /* convert to inventory uom */
86 IF (V_order_um1 <> ItemDetailsRec.item_um) THEN
87 X_plan_qty := GMICUOM.uom_conversion(V_item_id, 0, V_qty, V_order_um1, ItemDetailsRec.item_um, 0);
88 IF X_plan_qty < 0 THEN
89 RAISE UOM_CONVERSION_ERROR;
90 END IF;
91 ELSE
92 X_plan_qty := V_qty;
93 END IF;
94
95 Get_Alloc_Parameters(V_shipcust_id, ItemDetailsRec.alloc_class, X_alloc_method,
96 X_shelf_days, X_alloc_horizon, X_alloc_type, X_lot_qty,
97 X_partial_ind, X_prefqc_grade);
98 IF ItemDetailsRec.grade_ctl > 0 THEN
99 IF V_grade_wanted IS NOT NULL THEN
100 X_prefqc_grade := V_grade_wanted;
101 END IF;
102 ELSE
103 X_prefqc_grade := NULL;
104 END IF;
105
106
107 X_shelf_days := NVL(X_shelf_days,0);
108 X_trans_date := V_sched_shipdate + X_shelf_days;
109
110
111 X_temp_date := SYSDATE + NVL(X_alloc_horizon,0);
112
113 IF (X_temp_date < V_sched_shipdate) THEN
114 X_PROCEED_ALLOC := FALSE;
115 END IF;
116
117
118 /* Check to see wether the allocation class has been defined for auto or user inititated */
119 IF (((X_alloc_type = 1 AND V_alloc_mode = 'auto_all') OR (V_alloc_mode = 'auto_one')) AND
120 V_qty > 0) THEN
121
122 IF (X_PROCEED_ALLOC = TRUE) THEN
123 X_Return_Val := fetch_lots(V_item_id, V_whse_code, X_prefqc_grade,
124 X_trans_date, X_alloc_type, X_plan_qty, V_qty2,
125 ItemDetailsRec.lot_ctl, WhseDetailsRec.loct_ctl, ItemDetailsRec.lot_indivisible,
126 X_lot_qty, V_order_um1, ItemDetailsRec.item_um, ItemDetailsRec.item_um2, ItemDetailsRec.dualum_ind);
127 IF X_return_val = 0 THEN
128 IF X_partial_ind = 0 THEN
129 clear_table;
130 X_return_val := -1;
131 END IF;
132 END IF;
133 RETURN X_return_val;
134 ELSE
135 RETURN(-3);
136 END IF;
137 ELSE
138 RAISE INVALID_ALLOCATION_MODE;
139 END IF;
140 EXCEPTION
141 WHEN UOM_CONVERSION_ERROR THEN
142 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
143 FND_MESSAGE.SET_TOKEN('FROM_UOM', V_order_um1);
144 FND_MESSAGE.SET_TOKEN('TO_UOM', ItemDetailsRec.item_um);
145 FND_MESSAGE.SET_TOKEN('ITEM_NO', ItemDetailsRec.item_no);
146 FND_MSG_PUB.ADD;
147 RETURN(-1);
148 WHEN ITEM_NOT_FOUND THEN
149 FND_MESSAGE.SET_NAME('GMI', 'IC_API_INVALID_ITEM_NO');
150 FND_MESSAGE.SET_TOKEN('ITEM_NO', V_item_id);
151 FND_MSG_PUB.ADD;
152 RETURN(-1);
153 WHEN WHSE_NOT_FOUND THEN
154 FND_MESSAGE.SET_NAME('GMI', 'IC_API_INVALID_WHSE_CODE');
155 FND_MESSAGE.SET_TOKEN('WHSE_CODE', V_whse_code);
156 FND_MSG_PUB.ADD;
157 RETURN(-1);
158 WHEN ITEM_WHSE_NOT_LOT_LOCT THEN
159 FND_MESSAGE.SET_NAME('GML', 'OP_NOTLOT_LOCT');
160 FND_MSG_PUB.ADD;
161 RETURN(-1);
162 WHEN ITEM_ALLOCATION_EXISTS THEN
163 FND_MESSAGE.SET_NAME('GML', 'OP_SHIPQTYALLOCUSESUMMARY');
164 FND_MSG_PUB.ADD;
165 RETURN(-1);
166 WHEN INVALID_ALLOCATION_MODE THEN
167 FND_MESSAGE.SET_NAME('GMI', 'IC_INVALIDMODE');
168 FND_MSG_PUB.ADD;
169 RETURN(-1);
170 WHEN ITEM_MISSING_ALLOCATION_CLASS THEN
171 FND_MESSAGE.SET_NAME('GMI', 'IC_INV_ALLOC_CLASS');
172 FND_MSG_PUB.ADD;
173 RETURN(-1);
174 WHEN OTHERS THEN
175 FND_MESSAGE.SET_NAME('GMI', 'IC_AS_UNEXPECTED_ERROR');
176 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', sqlerrm);
177 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GML_AUTO_ALLOC');
178 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', 'GET_AVAILABLE_LOTS');
179 FND_MSG_PUB.ADD;
180 RETURN (-1);
181 END Get_Available_Lots;
182
183
184 PROCEDURE Get_Alloc_Parameters(V_shipcust_id NUMBER, V_alloc_class VARCHAR2, V_alloc_method IN OUT NOCOPY NUMBER,
185 V_shelf_days IN OUT NOCOPY NUMBER, V_alloc_horizon IN OUT NOCOPY NUMBER,
186 V_alloc_type IN OUT NOCOPY NUMBER, V_lot_qty IN OUT NOCOPY NUMBER,
187 V_partial_ind IN OUT NOCOPY NUMBER, V_prefqc_grade IN OUT NOCOPY VARCHAR2) IS
188 CURSOR Cur_qry_allocation(V_cust_id NUMBER) IS
189 SELECT alloc_method, shelf_days, alloc_horizon,
190 alloc_type, lot_qty, partial_ind, prefqc_grade
191 FROM op_alot_prm
192 WHERE NVL(cust_id,0) = V_cust_id -- This enables us to open
193 -- the cursor when V_cust_id is null
194 AND alloc_class = V_alloc_class
195 AND delete_mark = 0;
196
197 X_ans NUMBER;
198 X_cust_id NUMBER;
199 BEGIN
200 /* try the specific class/cust_id combination first */
201 OPEN Cur_qry_allocation(V_shipcust_id);
202 FETCH Cur_qry_allocation INTO V_alloc_method,
203 V_shelf_days,
204 V_alloc_horizon,
205 V_alloc_type,
206 V_lot_qty,
207 V_partial_ind,
208 V_prefqc_grade;
209 IF(Cur_qry_allocation% NOTFOUND) THEN
210 /* try the generic one next */
211 CLOSE Cur_qry_allocation;
212 OPEN Cur_qry_allocation(0);
213 FETCH Cur_qry_allocation INTO V_alloc_method,
214 V_shelf_days,
215 V_alloc_horizon,
216 V_alloc_type,
217 V_lot_qty,
218 V_partial_ind,
219 V_prefqc_grade;
220 IF(Cur_qry_allocation% NOTFOUND) THEN
221 /* if all else fails use the default values */
222 V_alloc_method := FND_PROFILE.VALUE('IC$ALLOC_METHOD');
223 V_shelf_days := FND_PROFILE.VALUE('IC$SHELF_DAYS');
224 V_alloc_horizon := FND_PROFILE.VALUE('IC$ALLOC_HORIZON');
225 V_alloc_type := FND_PROFILE.VALUE('IC$ALLOC_TYPE');
226 V_lot_qty := FND_PROFILE.VALUE('IC$LOT_QTY');
227 V_partial_ind := NVL(FND_PROFILE.VALUE('IC$PARTIAL_IND'), 1);
228 END IF;
229 END IF;
230 IF Cur_qry_allocation%ISOPEN THEN
231 CLOSE Cur_qry_allocation;
232 END IF;
233 END Get_Alloc_Parameters;
234
235 FUNCTION fetch_lots (V_item_id NUMBER, V_whse_code VARCHAR2, V_qc_grade VARCHAR2,
236 V_trans_date DATE, V_alloc_method NUMBER, V_qty NUMBER, V_qty2 NUMBER,
237 V_lot_ctl NUMBER, V_loct_ctl NUMBER, V_lot_indivisible NUMBER, V_lot_alloc NUMBER,
238 V_order_um1 VARCHAR2, V_item_um VARCHAR2, V_item_um2 VARCHAR2, V_dualum_ind NUMBER) RETURN NUMBER IS
239 CURSOR Cur_get_lots IS
240 SELECT lot_no, sublot_no, lot_id, lot_created ,
241 expire_date, qc_grade, location,
242 sum(loct_onhand) onhand_qty, sum(loct_onhand2) onhand_qty2,
243 sum(commit_qty) commit_qty, sum(commit_qty2) commit_qty2,
244 sum(loct_onhand) + sum(commit_qty) avail_qty,
245 sum(loct_onhand2) + sum(commit_qty2) avail_qty2,
246 sum(alloc_qty) as alloc_qty, sum(alloc_qty2) as alloc_qty2,
247 trans_id,count(*) numb_trans_line
248 FROM op_tran_tmp
249 WHERE session_id = P_session_id
250 AND item_id = V_item_id
251 AND whse_code = V_whse_code
252 AND expire_date > SYSDATE
253 GROUP BY lot_no, sublot_no, lot_id, lot_created,
254 trans_id,expire_date, qc_grade, location
255 ORDER BY DECODE(V_alloc_method, 0, lot_created, expire_date), qc_grade, DECODE(V_alloc_method, 0, expire_date, lot_created);
256
257 LotDetailsRec Cur_get_lots%ROWTYPE;
258 X_Return_val NUMBER :=0;
259 X_unalloc_qty NUMBER;
260 X_unalloc_qty2 NUMBER;
261 X_numb_zero_trans NUMBER := 0;
262 X_alloc_qty NUMBER :=0;
263 X_alloc_qty2 NUMBER :=0;
264 X_full_alloc NUMBER := 0;
265 UOM_CONVERSION_ERROR EXCEPTION;
266 BEGIN
267 clear_table;
268 insert_temp_rows (V_item_id, V_whse_code, V_qc_grade,
269 V_trans_date);
270 OPEN Cur_get_lots;
271 clear_table;
272 X_unalloc_qty := V_qty;
273 X_unalloc_qty2 := V_qty2;
274 FETCH Cur_get_lots INTO LotDetailsRec;
275 IF Cur_get_lots%FOUND THEN
276 WHILE Cur_get_lots%FOUND LOOP
277 X_alloc_qty := 0;
278 X_alloc_qty2 := 0;
279 IF (LotDetailsRec.avail_qty <= 0) AND (LotDetailsRec.alloc_qty = 0) THEN
280 NULL;
281 ELSE
282 /* process lot divisible (lots may be divided) */
283 IF (V_lot_indivisible <> 1) THEN
284 /* Allow multiple lot allocation */
285 IF (V_lot_alloc = 0) THEN
286 IF (LotDetailsRec.avail_qty >= X_unalloc_qty) THEN
287 X_alloc_qty := X_unalloc_qty;
288 X_alloc_qty2:= X_unalloc_qty2;
289 ELSE
290 X_alloc_qty := LotDetailsRec.avail_qty;
291 X_alloc_qty2:= LotDetailsRec.avail_qty2;
292 END IF;
293 ELSE
294 IF (LotDetailsRec.avail_qty >= X_unalloc_qty) THEN
295 X_alloc_qty := X_unalloc_qty;
296 X_alloc_qty2:= X_unalloc_qty2;
297 END IF;
298 END IF;
299 /* lot_indivisible item */
300 ELSE
301 IF (LotDetailsRec.avail_qty <= X_unalloc_qty) THEN
302 X_alloc_qty := LotDetailsRec.avail_qty;
303 X_alloc_qty2:= LotDetailsRec.avail_qty2;
304 END IF;
305 END IF;
306 IF X_alloc_qty > 0 THEN
307 IF (V_dualum_ind = 1) THEN
308 X_alloc_qty2 := GMICUOM.uom_conversion(V_item_id, LotDetailsRec.lot_id, X_alloc_qty, V_item_um, V_item_um2, 0);
309 IF X_alloc_qty2 < 0 THEN
310 RAISE UOM_CONVERSION_ERROR;
311 END IF;
312 ELSIF V_dualum_ind = 0 THEN
313 X_alloc_qty2 := NULL;
314 END IF;
315 X_unalloc_qty := X_unalloc_qty - X_alloc_qty;
316 X_unalloc_qty2 := X_unalloc_qty2 - X_alloc_qty2;
317 /* B2064204 IF V_order_um1 <> V_item_um THEN
318 X_alloc_qty := GMICUOM.uom_conversion(V_item_id, LotDetailsRec.lot_id, X_alloc_qty, V_item_um, V_order_um1, 0); */
319 IF X_alloc_qty < 0 THEN
320 RAISE UOM_CONVERSION_ERROR;
321 END IF;
322 /* END IF; */
323 INSERT INTO op_tran_tmp (session_id, doc_id, line_id, item_id, lot_no, sublot_no,
324 lot_id, lot_created, expire_date, qc_grade, whse_code, location,
325 alloc_qty, alloc_qty2)
326 VALUES (P_session_id, 1, 1, V_item_id, LotDetailsRec.lot_no, LotDetailsRec.sublot_no,
327 LotDetailsRec.lot_id, LotDetailsRec.lot_created, LotDetailsRec.expire_date,
328 LotDetailsRec.qc_grade, V_whse_code, LotDetailsRec.location,
329 X_alloc_qty, X_alloc_qty2);
330 END IF;
331 IF (X_unalloc_qty <= 0) THEN
332 X_full_alloc := 1;
333 EXIT;
334 END IF;
335 END IF;
336 FETCH Cur_get_lots INTO LotDetailsRec;
337 EXIT WHEN Cur_get_lots%NOTFOUND;
338 END LOOP;
339 RETURN(X_full_alloc);
340 END IF;
341 EXCEPTION
342 WHEN UOM_CONVERSION_ERROR THEN
343 FND_MESSAGE.SET_NAME('GMI', 'IC_API_UOM_CONVERSION_ERROR');
344 FND_MESSAGE.SET_TOKEN('FROM_UOM', V_item_um);
345 FND_MESSAGE.SET_TOKEN('TO_UOM', V_order_um1);
346 FND_MESSAGE.SET_TOKEN('ITEM_NO', V_item_id);
347 FND_MSG_PUB.ADD;
348 RETURN(-1);
349 WHEN OTHERS THEN
350 FND_MESSAGE.SET_NAME('GMI', 'IC_AS_UNEXPECTED_ERROR');
351 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', sqlerrm);
352 FND_MESSAGE.SET_TOKEN('PKG_NAME', 'GML_AUTO_ALLOC');
353 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME', 'FETCH_LOTS');
354 FND_MSG_PUB.ADD;
355 RETURN (-1);
356 END fetch_lots;
357
358
359 PROCEDURE insert_temp_rows (V_item_id NUMBER, V_whse_code VARCHAR2, V_qc_grade VARCHAR2,
360 V_trans_date DATE) IS
361 X_Return_val number := 0;
362 BEGIN
363
364 -- Note that shelf days is taken into account by
365 -- V_trans_date below.
366
367 INSERT INTO op_tran_tmp
368 (session_id,item_id, line_id, doc_id, lot_no, sublot_no,
369 lot_id, lot_created, expire_date, qc_grade, whse_code, location,
370 loct_onhand, loct_onhand2, commit_qty, commit_qty2, trans_id,
371 id_count, alloc_qty, alloc_qty2 )
372 SELECT
373 P_session_id,l.item_id, -1, -1, l.lot_no, l.sublot_no,
374 l.lot_id, l.lot_created, l.expire_date,
375 l.qc_grade, b.whse_code, b.location,
376 b.loct_onhand, b.loct_onhand2, 0,
377 0, 0, 0, 0, 0
378 FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s
379 WHERE l.item_id = V_item_id
380 AND whse_code = V_whse_code
381 AND expire_date > V_trans_date
382 AND l.inactive_ind = 0
383 AND b.item_id = l.item_id
384 AND b.lot_id = l.lot_id
385 AND (V_qc_grade IS NULL OR l.qc_grade = V_qc_grade)
386 AND s.lot_status (+) = b.lot_status
387 AND nvl(s.order_proc_ind,1) = 1
388 AND nvl(s.rejected_ind,0) = 0
389 AND b.loct_onhand > 0 ;
390
391 INSERT INTO op_tran_tmp
392 (session_id,item_id, line_id, doc_id, lot_no, sublot_no,
393 lot_id, lot_created, expire_date, qc_grade, whse_code, location,
394 loct_onhand, loct_onhand2, commit_qty, commit_qty2, trans_id,
395 id_count, alloc_qty, alloc_qty2 )
396 SELECT
397 P_session_id,t.item_id, -1, -1, l.lot_no, l.sublot_no, t.lot_id, l.lot_created,
398 l.expire_date, l.qc_grade, t.whse_code, t.location, 0, 0, t.trans_qty,
399 t.trans_qty2, 0, 0, 0, 0
400 FROM ic_item_mst i, ic_lots_mst l, ic_tran_pnd t
401 WHERE t.item_id = V_item_id
402 AND whse_code = V_whse_code
403 AND expire_date > V_trans_date
404 AND l.item_id = t.item_id
405 AND l.inactive_ind = 0
406 AND t.lot_id = l.lot_id
407 AND (V_qc_grade IS NULL OR t.qc_grade = V_qc_grade)
408 AND t.delete_mark = 0
409 AND t.completed_ind = 0
410 AND t.trans_qty < 0
411 AND (t.lot_id <> 0 OR (i.lot_ctl = 0 AND i.loct_ctl > 0) )
412 AND l.item_id = i.item_id;
413 END insert_temp_rows;
414
415 PROCEDURE clear_table IS
416 BEGIN
417 DELETE FROM op_tran_tmp
418 WHERE session_id = p_session_id;
419 END clear_table;
420
421 END GML_AUTO_ALLOC;