1 PACKAGE BODY GMI_LOCT_INV_PVT AS
2 /* $Header: GMIVBULB.pls 115.17 2004/07/07 14:46:47 jgogna ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVBULB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private definitions For Business Layer |
13 | Logic For IC_LOCT_INV |
14 | |
15 | HISTORY |
16 | 12-JAN-2000 H.Verdding |
17 | 11-OCT-2000 Jalaj Srivastava Bug 1427888 |
18 | Jalaj Srivastava Bug 2483644 06/08/2002 |
19 | Included changes to process journal transactions also. |
20 | For destination move line the status needs to be updated. |
21 | 28-OCT-2002 Joe DiIorio Bug #2643440 - 11.5.1J - added nocopy. |
22 | Jalaj Srivastava Bug 3158806 |
23 | Update ic_lots_cpg with hold_date if it is the first yielding |
24 | transaction. |
25 | 27-FEB-2004 Jatinder Gogna - 3470841 |
26 | Compute the lot dates for the first yielding transaction|
27 | 7-JUN-2004 Teresa Wong B3415691 - Enhancement for Serono |
28 | Added code to recheck negative inventory and lot |
29 | status at the time of save. |
30 | 24-JUN-2004 Teresa Wong B3415691 - Enhancement for Serono |
31 | Added code to update lot status in ic_loct_inv |
32 | for yield transaction only if GMI: Move Different Status|
33 | was set to 2 and the onhand of the lot in the location |
34 | into which it was being yielded to was 0. |
35 | 6-JUL-2004 Jatinder Gogna - 3739308 |
36 | Set the dates to MAX date if the item is grade |
37 | controlled and intervals are NULL or zero |
38 +=========================================================================+
39 API Name : GMI_LOCT_INV_PVT
40 Type : Public
41 Function : This package contains private procedures used to create
42 IC_LOCT_INV transactions
43 Pre-reqs : N/A
44 Parameters: Per function
45
46 Current Vers : 1.0
47
48 Previous Vers : 1.0
49
50 Initial Vers : 1.0
51 Notes
52
53 Body end of comments
54 */
55 /* Global variables */
56 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_LOCT_INV_PVT';
57
58 PROCEDURE UPDATING_IC_LOCT_INV
59 (
60 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec,
61 x_return_status OUT NOCOPY VARCHAR2
62 )
63 IS
64 err_msg VARCHAR2(200);
65 err_num NUMBER;
66 l_loct_inv IC_LOCT_INV%ROWTYPE;
67 l_loct_inv_rec ic_loct_inv%ROWTYPE; /* TKW B3415691 */
68
69 BEGIN
70
71 /* Initialize return status to sucess */
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 /* Copy required Fields From Ic_tran_rec record Type. */
75
76 l_loct_inv.item_id := p_tran_rec.item_id;
77 l_loct_inv.lot_id := p_tran_rec.lot_id;
78 l_loct_inv.whse_code := p_tran_rec.whse_code;
79 l_loct_inv.lot_status := p_tran_rec.lot_status;
80 l_loct_inv.loct_onhand := p_tran_rec.trans_qty;
81 l_loct_inv.loct_onhand2 := p_tran_rec.trans_qty2;
82 l_loct_inv.text_code := p_tran_rec.text_code;
83 l_loct_inv.delete_mark := 0;
84 l_loct_inv.location := p_tran_rec.location;
85
86 /* Do we Just need User_id Only ----- */
87
88 l_loct_inv.last_updated_by := p_tran_rec.user_id;
89 l_loct_inv.created_by := p_tran_rec.user_id;
90 l_loct_inv.last_update_date := SYSDATE;
91 l_loct_inv.creation_date := SYSDATE;
92
93
94 /* TKW 6/7/2004 B3415691 */
95 IF NOT GMIUTILS.NEG_INV_CHECK
96 ( p_item_id => l_loct_inv.item_id,
97 p_whse_code => l_loct_inv.whse_code,
98 p_lot_id => l_loct_inv.lot_id,
99 p_location => l_loct_inv.location,
100 p_qty => p_tran_rec.trans_qty,
101 p_qty2 => p_tran_rec.trans_qty2
102 )
103 THEN
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106
107 /* TKW 6/7/2004 B3415691 */
108 IF NOT GMIUTILS.LOT_STATUS_CHECK
109 ( p_item_id => l_loct_inv.item_id,
110 p_whse_code => l_loct_inv.whse_code,
111 p_lot_id => l_loct_inv.lot_id,
112 p_location => l_loct_inv.location,
113 p_doc_type => p_tran_rec.doc_type,
114 p_line_type => p_tran_rec.line_type,
115 p_trans_qty => p_tran_rec.trans_qty,
116 p_lot_status => p_tran_rec.lot_status
117 )
118 THEN
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122 /* Update Inventory LOCTary Table -- */
123 /* Jalaj Srivastava Bug 1427888 11-OCT-2000 */
124 /* For status txn it the status needs to be updated in ic_loct_inv */
125 /* For destination move transaction, the status needs to be updated */
126 /* For grade txn nothing needs to be updated in ic_loct_inv */
127 /* ****************************************************
128 Jalaj Srivastava Bug 2483644
129 Now journal txns would also be posted using these APIs
130 ***************************************************** */
131 IF (substr(p_tran_rec.doc_type,1,3) <> 'GRD') THEN
132 IF (substr(p_tran_rec.doc_type,1,3) ='STS') THEN
133 IF (GMI_LOCT_INV_DB_PVT.UPDATE_IC_LOCT_INV
134 ( p_loct_inv => l_loct_inv,
135 p_status_updated => 1,
136 p_qty_updated => 0
137 )
138 ) THEN
139 RETURN;
140 END IF;
141 ELSE
142 /* Jalaj Srivastava Bug 3158806
143 Update ic_lots_cpg with hold_date if it is the first yielding transaction.
144 and hold date is null. update_ic_loct_inv internally calls insert_ic_loct_inv
145 if there is no existing inventory*/
146 Update ic_lots_cpg
147 Set ic_hold_date = (select p_tran_rec.trans_date + ic_hold_days
148 from ic_item_cpg
149 where item_id = l_loct_inv.item_id
150 )
151 Where item_id = l_loct_inv.item_id and
152 lot_id = l_loct_inv.lot_id and
153 ic_hold_date is NULL and
154 not exists (select 1
155 from ic_loct_inv
156 where item_id = l_loct_inv.item_id
157 and lot_id = l_loct_inv.lot_id
158 );
159
160 /* JG - 3470841 - Compute the lot dates for the first yielding transactions */
161
162 IF ((p_tran_rec.doc_type = 'PROD' and p_tran_rec.line_type = -1) or (p_tran_rec.doc_type = 'OMSO')) THEN
163 NULL; /* Not a yielding transaction */
164 ELSE
165 /* 3739308 - Set the dates to MAX date if the item is grade controlled and intervals
166 are NULL or zero */
167 update ic_lots_mst
168 set (lot_created, expire_date, retest_date, expaction_date) =
169 ( select nvl(lot_created, p_tran_rec.trans_date),
170 nvl(expire_date, decode (grade_ctl + nvl(shelf_life,0), 0,
171 GMA_GLOBAL_GRP.SY$MAX_DATE, p_tran_rec.trans_date + nvl(shelf_life,0))),
172 nvl(retest_date, decode (grade_ctl + nvl(shelf_life,0), 0,
173 GMA_GLOBAL_GRP.SY$MAX_DATE, p_tran_rec.trans_date + nvl(retest_interval,0))),
174 nvl(expaction_date, decode (grade_ctl + nvl(shelf_life,0), 0,
175 GMA_GLOBAL_GRP.SY$MAX_DATE, p_tran_rec.trans_date + nvl(shelf_life,0) +
176 nvl(expaction_interval,0)))
177 from ic_item_mst_b
178 where item_id = l_loct_inv.item_id)
179 Where item_id = l_loct_inv.item_id and
180 lot_id = l_loct_inv.lot_id and
181 lot_created is NULL;
182 END IF;
183
184
185 IF (GMI_LOCT_INV_DB_PVT.UPDATE_IC_LOCT_INV
186 ( p_loct_inv => l_loct_inv,
187 p_status_updated => 0,
188 p_qty_updated => 1
189 )
190 ) THEN
191
192 /* ************************************************
193 Jalaj Srivastava BUg 2483644
194 Now, we know the row exists.
195 Update with the new lot status (if any)
196 *********************************************** */
197 IF ( (substr(p_tran_rec.doc_type,1,3) ='TRN')
198 AND (p_tran_rec.line_type = 1)
199 ) THEN
200 IF (GMI_LOCT_INV_DB_PVT.UPDATE_IC_LOCT_INV
201 ( p_loct_inv => l_loct_inv,
202 p_status_updated => 1,
203 p_qty_updated => 0
204 )
205 ) THEN
206 RETURN;
207 END IF;
208 END IF;
209
210 /**********************************************
211 TKW B3415691 - Update with new lot status
212 for yield transaction if profile
213 GMI: Move Different Status is Not Allowed with
214 Exception and onhand of the lot in the location
215 into which it is being yielded is 0
216 ***********************************************/
217 IF (p_tran_rec.doc_type = 'PROD'
218 AND p_tran_rec.line_type = 1
219 AND FND_PROFILE.VALUE('IC$MOVEDIFFSTAT') = 2 ) THEN
220 gmigutl.get_loct_inv (
221 p_item_id => p_tran_rec.item_id,
222 p_whse_code => p_tran_rec.whse_code,
223 p_lot_id => p_tran_rec.lot_id,
224 p_location => p_tran_rec.location,
225 x_ic_loct_inv_row => l_loct_inv_rec
226 );
227
228 IF (NVL (l_loct_inv_rec.loct_onhand, 0) - p_tran_rec.trans_qty = 0) THEN
229 IF (GMI_LOCT_INV_DB_PVT.UPDATE_IC_LOCT_INV
230 ( p_loct_inv => l_loct_inv,
231 p_status_updated => 1,
232 p_qty_updated => 0
233 )
234 ) THEN
235 RETURN;
236 END IF; /* update lot status */
237 END IF; /* onhand is 0 */
238 END IF; /* yield transaction and profile = 2 */
239 ELSE /* We are Going to create New record */
240 /* Jalaj Srivastava Bug 3158806
241 This part actually never gets called. update internally calls insert_ic_loct_inv.*/
242
243 IF(GMI_LOCT_INV_DB_PVT.INSERT_IC_LOCT_INV( p_loct_inv => l_loct_inv)) THEN
244 RETURN;
245 ELSE
246 FND_MESSAGE.SET_NAME('GMI','GMI_IC_LOCT_INV_INSERT');
247 FND_MSG_PUB.ADD;
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250 END IF;
251 END IF;
252 END IF;
253
254 EXCEPTION
255
256 WHEN FND_API.G_EXC_ERROR THEN
257
258 x_return_status := FND_API.G_RET_STS_ERROR;
259
260 WHEN OTHERS THEN
261
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263
264 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
265 , 'UPDATING_IC_LOCT_INV'
266 );
267
268
269
270 END UPDATING_IC_LOCT_INV;
271
272 END GMI_LOCT_INV_PVT;
[Home] [Help]