[Home] [Help]
PACKAGE BODY: APPS.GML_INTORD_LOT_STS
Source
1 PACKAGE BODY GML_INTORD_LOT_STS AS
2 /* $Header: GMLIOLSB.pls 115.1 2004/01/20 16:30:41 pbamb noship $*/
3
4
5 -----------------------------------------------------------------------------
6 -- Define private package constants.
7 -----------------------------------------------------------------------------
8
9
10 -----------------------------------------------------------------------------
11 -- Declare private package variables.
12 -----------------------------------------------------------------------------
13
14
15 --------------------------------------------------------------------------------
16 -- Forward procedure declarations
17 --------------------------------------------------------------------------------
18
19
20 -------------------------------------------------------------------------------
21 --Start of Comments
22 --Name: derive_porc_lot_status
23 --Pre-reqs:
24 -- None
25 --Modifies:
26 -- None
27 --Locks:
28 -- None.
29 --Function:
30 -- This procedure returns the lot status which is used for creating PORC
31 -- transactions.
32 --Parameters:
33 -- Otherwise, include the IN:, IN OUT:, and/or OUT: sections as needed.
34 --IN:
35 -- p_item_id
36 -- item id
37 -- p_whse_code
38 -- receiving warehouse
39 -- p_lot_id
40 -- items Lot id
41 -- p_location
42 -- receiving warehouse location
43 -- p_ship_lot_status
44 -- shipped lot status
45 --IN OUT:
46 -- x_rcpt_lot_status
47 -- receipt lot status
48 --OUT:
49 -- x_txn_allowed
50 -- whether transaction is allowed, Y or N
51 -- x_return_status
52 -- return status of the procedure
53 -- x_msg_data
54 -- message returned from procedure
55 --Notes:
56 --
57 --End of Comments
58 -------------------------------------------------------------------------------
59
60 PROCEDURE derive_porc_lot_status
61 ( p_item_id IN NUMBER
62 , p_whse_code IN VARCHAR2
63 , p_lot_id IN NUMBER
64 , p_location IN VARCHAR2
65 , p_ship_lot_status IN VARCHAR2
66 , x_rcpt_lot_status IN OUT NOCOPY VARCHAR2
67 , x_txn_allowed OUT NOCOPY VARCHAR2
68 , x_return_status OUT NOCOPY VARCHAR2
69 , x_msg_data OUT NOCOPY VARCHAR2
70 ) IS
71
72 -- declare cursor to fetch ic_loct_inv lot status and onhand qty
73 CURSOR get_inv_sts_qty IS
74 SELECT loct_onhand, lot_status
75 FROM ic_loct_inv
76 WHERE item_id = p_item_id
77 AND whse_code = p_whse_code
78 AND lot_id = p_lot_id
79 AND location = p_location;
80
81 l_onhand_lot_qty NUMBER := 0;
82 l_onhand_lot_status VARCHAR2(4);
83 l_return_status VARCHAR2(1);
84 l_msg_data VARCHAR2(1000);
85
86 BEGIN
87
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 IF p_ship_lot_status IS NULL THEN
91 x_msg_data := 'null p_ship_lot_status passed to proc derive_porc_lot_status';
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 x_txn_allowed := 'N';
94 x_rcpt_lot_status := NULL;
95 RETURN;
96 END IF;
97
98
99 -- fetch the inv lot status and onhand qty
100 OPEN get_inv_sts_qty;
101 FETCH get_inv_sts_qty INTO l_onhand_lot_qty,l_onhand_lot_status;
102 -- it is a new lot or inventory does not exist
103 IF get_inv_sts_qty%NOTFOUND THEN
104 l_onhand_lot_status := NULL;
105 l_onhand_lot_qty := NULL;
106 END IF;
107 CLOSE get_inv_sts_qty;
108
109 /* if it is a new lot or inventory does not exist
110 , allow the transaction irrespective of move_diff_sts */
111 IF (l_onhand_lot_qty IS NULL AND l_onhand_lot_status IS NULL) THEN
112 x_txn_allowed := 'Y';
113 x_rcpt_lot_status := p_ship_lot_status;
114 RETURN;
115 END IF;
116
117 -- it is an existing lot and move_diff_sts = 0
118 IF GML_INTORD_LOT_STS.G_move_diff_stat = 0 THEN
119 IF (l_onhand_lot_qty >= 0 AND p_ship_lot_status <> l_onhand_lot_status) THEN
120 x_txn_allowed := 'N';
121 x_rcpt_lot_status := NULL;
122 RETURN;
123 ELSIF (p_ship_lot_status = l_onhand_lot_status) THEN
124 x_txn_allowed := 'Y';
125 x_rcpt_lot_status := p_ship_lot_status;
126 RETURN;
127 END IF;
128 END IF;
129
130 /* it is an existing lot and move_diff_sts = 1
131 allow the transaction irrespective of onhand qty */
132 IF GML_INTORD_LOT_STS.G_move_diff_stat = 1 THEN
133 x_txn_allowed := 'Y';
134 x_rcpt_lot_status := l_onhand_lot_status;
135 RETURN;
136 END IF;
137
138 -- it is an existing lot and move_diff_sts = 2
139 IF GML_INTORD_LOT_STS.G_move_diff_stat = 2 THEN
140 IF (p_ship_lot_status = l_onhand_lot_status) THEN
141 x_txn_allowed := 'Y';
142 x_rcpt_lot_status := p_ship_lot_status;
143 RETURN;
144 ELSIF (l_onhand_lot_qty <> 0 AND p_ship_lot_status <> l_onhand_lot_status) THEN
145 x_txn_allowed := 'N';
146 x_rcpt_lot_status := NULL;
147 RETURN;
148 ELSIF (l_onhand_lot_qty = 0 AND p_ship_lot_status <> l_onhand_lot_status) THEN
149 x_txn_allowed := 'Y';
150 x_rcpt_lot_status := p_ship_lot_status;
151 -- change the onhand_lot_status status to ship_lot_status in ic_lot_inv
152 GML_INTORD_LOT_STS.change_inv_lot_status( p_item_id => p_item_id
153 , p_whse_code => p_whse_code
154 , p_lot_id => p_lot_id
155 , p_location => p_location
156 , p_to_status => p_ship_lot_status
157 , x_return_status => l_return_status
158 , x_msg_data => l_msg_data );
159 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
160 x_msg_data := l_msg_data;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 x_txn_allowed := 'N';
163 x_rcpt_lot_status := NULL;
164 END IF;
165 RETURN;
166 END IF;
167 END IF;
168
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 x_msg_data := 'derive_porc_lot_status failed unexpected error';
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
174 x_txn_allowed := 'N';
175 x_rcpt_lot_status := NULL;
176
177 END derive_porc_lot_status;
178
179 -------------------------------------------------------------------------------
180 --Start of Comments
181 --Name: change_inv_lot_status
182 --Pre-reqs:
183 -- None
184 --Modifies:
185 -- None
186 --Locks:
187 -- None.
188 --Function:
189 -- This procedure ic called for changing the onhand lot status.
190 --
191 --Parameters:
192 -- Otherwise, include the IN:, IN OUT:, and/or OUT: sections as needed.
193 --IN:
194 -- p_item_id
195 -- item id
196 -- p_whse_code
197 -- ic_loct_inv whse
198 -- p_lot_id
199 -- items Lot id
200 -- p_location
201 -- ic_loct_inv location
202 -- p_to_status
203 -- status to which the ic_loct_inv needs to be changed.
204 --IN OUT:
205 --
206 --OUT:
207 -- x_return_status
208 -- return status of the procedure
209 -- x_msg_data
210 -- message returned from procedure
211 --Notes:
212 --
213 --End of Comments
214 -------------------------------------------------------------------------------
215
216 PROCEDURE change_inv_lot_status
217 ( p_item_id IN NUMBER
218 , p_whse_code IN VARCHAR2
219 , p_lot_id IN NUMBER
220 , p_location IN VARCHAR2
221 , p_to_status IN VARCHAR2
222 , x_return_status OUT NOCOPY VARCHAR2
223 , x_msg_data OUT NOCOPY VARCHAR2
224 ) IS
225
226 l_item_rec IC_ITEM_MST%ROWTYPE;
227 l_trans_rec GMIGAPI.qty_rec_typ;
228 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
229 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
230 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
231 l_status VARCHAR2(1);
232 l_count NUMBER;
233 l_data VARCHAR2(1000);
234 l_count_msg NUMBER;
235 l_dummy_cnt NUMBER := 0;
236 l_reason_code_security VARCHAR2(1) := 'N';
237
238 l_message_data VARCHAR2(2000);
239
240 CURSOR lot_details IS
241 SELECT lot_no, sublot_no
242 FROM ic_lots_mst
243 WHERE lot_id = p_lot_id;
244
245 l_lot_details lot_details%ROWTYPE;
246
247 CURSOR Get_Reason_Code IS
248 SELECT reason_code
249 FROM sy_reas_cds
250 WHERE delete_mark = 0
251 AND (l_reason_code_security = 'Y')
252 AND (reason_code in (select reason_code from gma_reason_code_security
253 where (doc_type = 'PORC' or doc_type IS NULL) and
254 (responsibility_id = FND_GLOBAL.RESP_id or responsibility_id IS NULL)))
255 UNION ALL
256 SELECT reason_code
257 FROM sy_reas_cds
258 WHERE delete_mark = 0;
259
260
261 BEGIN
262
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 SELECT *
266 INTO l_item_rec
267 FROM ic_item_mst
268 WHERE item_id = p_item_id;
269
270 IF l_item_rec.status_ctl = 0 THEN
271 x_msg_data := 'Item is not status controlled';
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 RETURN;
274 END IF;
275
276 SELECT s.co_code, w.orgn_code
277 INTO l_trans_rec.co_code, l_trans_rec.orgn_code
278 FROM ic_whse_mst w, sy_orgn_mst s
279 WHERE w.whse_code = p_whse_code
280 AND w.orgn_code = s.orgn_code;
281
282 OPEN lot_details;
283 FETCH lot_details INTO l_lot_details;
284 CLOSE lot_details;
285
286 l_trans_rec.trans_type := 4;
287 l_trans_rec.trans_date := SYSDATE;
288 l_trans_rec.item_no := l_item_rec.item_no;
289 l_trans_rec.lot_no := l_lot_details.lot_no;
290 l_trans_rec.sublot_no := l_lot_details.sublot_no;
291 l_trans_rec.from_whse_code := p_whse_code;
292 l_trans_rec.from_location := p_location;
293 l_trans_rec.lot_status := p_to_status;
294
295 l_reason_code_security := nvl(fnd_profile.value('GMA_REASON_CODE_SECURITY'), 'N');
296 OPEN Get_Reason_Code;
297 FETCH Get_Reason_Code into l_trans_rec.reason_code;
298 IF Get_Reason_Code%NOTFOUND THEN
299 CLOSE Get_Reason_Code;
300
301 BEGIN
302 UPDATE IC_LOCT_INV
303 SET lot_status = p_to_status
304 WHERE item_id = p_item_id
305 AND whse_code = p_whse_code
306 AND location = p_location
307 AND lot_id = p_lot_id;
308
309 EXCEPTION WHEN OTHERS THEN
310 x_msg_data := 'Error updating the status in ic_loct_inv';
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 END;
313 RETURN;
314
315 IF Get_Reason_Code%ISOPEN THEN
316 CLOSE Get_Reason_Code;
317 END IF;
318 END IF; /* IF Get_Reason_Code%NOTFOUND */
319
320 l_trans_rec.trans_qty := NULL;
321 l_trans_rec.user_name := FND_GLOBAL.USER_NAME;
322
323 -- Set the context for the GMI APIs
324 IF( NOT Gmigutl.Setup(l_trans_rec.user_name) ) THEN
325 x_msg_data := 'Error during Gmigutl.Setup';
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 RETURN;
328 END IF;
329
330 -- Call the standard API and check the return status
331 BEGIN
332 Gmipapi.Inventory_Posting
333 ( p_api_version => 3.0
334 , p_init_msg_list => 'T'
335 , p_commit => 'F'
336 , p_validation_level => 100
337 , p_qty_rec => l_trans_rec
338 , x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
339 , x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
340 , x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
341 , x_return_status => l_status
342 , x_msg_count => l_count
343 , x_msg_data => l_data
344 );
345
346 IF( l_status IN ('U','E') ) THEN
347 -- API Failed. Error message must be on stack.
348 l_count_msg := fnd_msg_pub.Count_Msg;
349
350 FOR l_loop_cnt IN 1..l_count_msg
351 LOOP
352 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
353 P_data => l_data,
354 P_encoded => 'F',
355 P_msg_index_out => l_dummy_cnt);
356
357 l_message_data := l_message_data||l_data;
358 END LOOP;
359 x_msg_data := 'Inv Posting Failed '||l_message_data;
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 RETURN;
362 END IF;
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 x_msg_data := 'Inv Posting Failed, Unexpected error';
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368 END;
369
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 x_msg_data := 'change_inv_lot_status failed, unexpected error';
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375
376 END change_inv_lot_status;
377
378 -------------------------------------------------------------------------------
379 --Start of Comments
380 --Name: get_omso_lot_status
381 --Pre-reqs:
382 -- None
383 --Modifies:
384 -- None
385 --Locks:
386 -- None.
387 --Function:
388 -- This procedure returns a plsql table containing lot id and lot status.
389 -- for shipped transactions.
390 --Parameters:
391 -- Otherwise, include the IN:, IN OUT:, and/or OUT: sections as needed.
392 --IN:
393 -- p_req_line_id
394 -- requisition line id.
395 -- p_item_id
396 -- item id
397 --IN OUT:
398 -- x_lot_sts_tbl
399 -- plsql table containing lot id and lot status.
400 --OUT:
401 -- x_return_status
402 -- return status of the procedure
403 -- x_msg_data
404 -- message returned from procedure
405 --Notes:
406 --
407 --End of Comments
408 -------------------------------------------------------------------------------
409
410 PROCEDURE get_omso_lot_status
411 ( p_req_line_id IN NUMBER
412 , p_item_id IN NUMBER
413 , x_lot_sts_tab IN OUT NOCOPY lot_sts_table
414 , x_return_status OUT NOCOPY VARCHAR2
415 , x_msg_data OUT NOCOPY VARCHAR2
416 ) IS
417
418 -- declare cursor to fetch shipped lot and status
419 CURSOR get_omso_lots IS
420 SELECT lot_id,lot_status
421 FROM ic_tran_pnd
422 WHERE doc_type = 'OMSO'
423 AND item_id = p_item_id
424 AND completed_ind = 1
425 AND delete_mark = 0
426 AND line_id IN(SELECT line_id FROM oe_order_lines_all
427 WHERE source_document_line_id = p_req_line_id);
428
429 l_number NUMBER := 1;
430
431 BEGIN
432
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434
435 --delete plsql table before populating
436 x_lot_sts_tab.DELETE;
437
438 FOR r_get_omso_lots in get_omso_lots LOOP
439 x_lot_sts_tab(l_number).lot_id := r_get_omso_lots.lot_id;
440 x_lot_sts_tab(l_number).lot_status := r_get_omso_lots.lot_status;
441 l_number := l_number + 1;
442 END LOOP;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 x_msg_data := 'get_omso_lot_status failed unexpected error';
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448
449 END get_omso_lot_status;
450
451
452 END GML_INTORD_LOT_STS;