eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;