[Home] [Help]
PACKAGE BODY: APPS.GMI_QUANTITY_PUB
Source
1 PACKAGE BODY GMI_QUANTITY_PUB AS
2 --$Header: GMIPQTYB.pls 115.9 2002/11/04 20:48:04 jdiiorio gmigapib.pls $
3 -- Body start of comments
4 --+==========================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+==========================================================================+
9 --| FILE NAME |
10 --| GMIPQTYB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMI_QUANTITY_PUB |
14 --| |
15 --| DESCRIPTION |
19 --| CONTENTS |
16 --| This package conatains all APIs related to the Inventory Quantity |
17 --| Engine |
18 --| |
20 --| Inventory_Posting |
21 --| |
22 --| HISTORY |
23 --| 25-FEB-1999 M.Godfrey Upgrade to R11 |
24 --| 20/AUG/1999 H.Verdding Bug 951828 Change GMS package Calls to GMA |
25 --| 02/JAN/2000 Liz Enstone Bug 1159923 Change message names from SY_ to |
26 --| IC_ |
27 --| 28-OCT-2002 J.DiIorio Bug#2643440 - 11.5.1J - added nocopy |
28 --| |
29 --+==========================================================================+
30 -- Body end of comments
31 -- Global variables
32 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_QUANTITY_PUB';
33 -- Api start of comments
34 --+==========================================================================+
35 --| PROCEDURE NAME |
36 --| Inventory_Posting |
37 --| |
38 --| TYPE |
39 --| Public |
40 --| |
41 --| USAGE |
42 --| Updates an inventory quantity posting. This may be one of |
43 --| - Create Inventory |
44 --| - Adjust Inventory |
45 --| - Move Inventory |
46 --| - Change Lot Status |
47 --| - Change QC Grade |
48 --| |
49 --| DESCRIPTION |
50 --| This procedure validates and updates inventory posting |
51 --| |
52 --| PARAMETERS |
53 --| p_api_version IN NUMBER - Api Version |
54 --| p_init_msg_list IN VARCHAR2 - Message Initialization Ind. |
55 --| p_commit IN VARCHAR2 - Commit Indicator |
56 --| p_validation_level IN VARCHAR2 - Validation Level Indicator |
57 --| p_trans_rec IN trans_rec_typ - Item Master details |
58 --| x_return_status OUT NOCOPY VARCHAR2 - Return Status |
59 --| x_msg_count OUT NOCOPY NUMBER - Number of messages |
60 --| x_msg_data OUT NOCOPY VARCHAR2 - Messages in encoded format |
61 --| |
62 --| RETURNS |
63 --| None |
64 --| |
65 --| HISTORY |
66 --| |
67 --+==========================================================================+
68 PROCEDURE Inventory_Posting
69 ( p_api_version IN NUMBER
70 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
71 , p_commit IN VARCHAR2 := FND_API.G_FALSE
72 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
73 , p_trans_rec IN trans_rec_typ
74 , x_return_status OUT NOCOPY VARCHAR2
75 , x_msg_count OUT NOCOPY NUMBER
76 , x_msg_data OUT NOCOPY VARCHAR2
77 )
78 IS
79 l_api_name CONSTANT VARCHAR2 (30) := 'Inventory_Posting';
80 l_api_version CONSTANT NUMBER := 2.0;
81 l_item_id ic_item_mst.item_id%TYPE;
82 l_lot_id ic_lots_mst.lot_id%TYPE DEFAULT 0;
83 l_old_qc_grade qc_grad_mst.qc_grade%TYPE;
84 l_old_lot_status ic_lots_sts.lot_status%TYPE;
85 l_journal_id ic_jrnl_mst.journal_id%TYPE;
86 l_doc_id ic_adjs_jnl.doc_id%TYPE;
87 l_line_id ic_tran_cmp.line_id%TYPE;
88 l_trans_rec trans_rec_typ;
89 l_loop_ctr NUMBER(2);
90 l_num_rows NUMBER(2);
91 l_msg_count NUMBER :=0;
92 l_msg_data VARCHAR2(2000);
93 l_return_status VARCHAR2(1);
94 l_ic_jrnl_mst_rec ic_jrnl_mst%ROWTYPE;
95 l_ic_adjs_jnl_rec ic_adjs_jnl%ROWTYPE;
96 l_lot_rec GMI_LOTS_PUB.lot_rec_typ;
97 l_cmp_tran_rec GMI_CMP_TRAN_PVT.cmp_tran_typ;
98 l_user_name fnd_user.user_name%TYPE DEFAULT 'OPM';
99 l_user_id fnd_user.user_id%TYPE;
100
101 BEGIN
102
103 -- Standard Start OF API savepoint
104 SAVEPOINT Inventory_Posting;
105 -- Standard call to check for call compatibility.
106 IF NOT FND_API.Compatible_API_CALL ( l_api_version
107 , p_api_version
108 , l_api_name
109 , G_PKG_NAME
110 )
111 THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 END IF;
114
118 FND_MSG_PUB.Initialize;
115 -- Initialize message list if p_int_msg_list is set TRUE.
116 IF FND_API.to_boolean(p_init_msg_list)
117 THEN
119 END IF;
120
121 -- Initialize API return status to sucess
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 -- Populate WHO columns
125 l_user_name :=p_trans_rec.user_name;
126 GMA_GLOBAL_GRP.Get_who( p_user_name => l_user_name
127 , x_user_id => l_user_id
128 );
129
130 IF l_user_id = 0
131 THEN
132 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_USER_NAME');
133 FND_MESSAGE.SET_TOKEN('USER_NAME',l_user_name);
134 FND_MSG_PUB.Add;
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 -- Move transaction record to local
139 l_trans_rec := p_trans_rec;
140
141 -- Ensure Upper-case columns are converted
142 l_trans_rec.item_no := UPPER(l_trans_rec.item_no);
143 l_trans_rec.item_um := l_trans_rec.item_um;
144 l_trans_rec.item_um2 := l_trans_rec.item_um2;
145 l_trans_rec.from_whse_code := UPPER(l_trans_rec.from_whse_code);
146 l_trans_rec.to_whse_code := UPPER(l_trans_rec.to_whse_code);
147 l_trans_rec.lot_no := UPPER(l_trans_rec.lot_no);
148 l_trans_rec.sublot_no := UPPER(l_trans_rec.sublot_no);
149 l_trans_rec.from_location := UPPER(l_trans_rec.from_location);
150 l_trans_rec.to_location := UPPER(l_trans_rec.to_location);
151 l_trans_rec.qc_grade := UPPER(l_trans_rec.qc_grade);
152 l_trans_rec.lot_status := UPPER(l_trans_rec.lot_status);
153 l_trans_rec.co_code := UPPER(l_trans_rec.co_code);
154 l_trans_rec.orgn_code := UPPER(l_trans_rec.orgn_code);
155 l_trans_rec.reason_code := UPPER(l_trans_rec.reason_code);
156
157 -- Perform validation of transaction
158
159 GMI_QUANTITY_PVT.Validate_Inventory_posting
160 ( p_trans_rec => l_trans_rec
161 , x_item_id => l_item_id
162 , x_lot_id => l_lot_id
163 , x_old_lot_status => l_old_lot_status
164 , x_old_qc_grade => l_old_qc_grade
165 , x_return_status => l_return_status
166 , x_msg_count => l_msg_count
167 , x_msg_data => l_msg_data
168 , x_trans_rec => l_trans_rec
169 );
170
171 -- If no errors were found then proceed with posting the
172 -- transaction.
173
174 IF (l_return_status = FND_API.G_RET_STS_ERROR)
175 THEN
176 RAISE FND_API.G_EXC_ERROR;
177 ELSIF
178 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181
182 -- Get the surrogate key (journal_id) for the journal
183 SELECT gem5_journal_id_s.nextval INTO l_journal_id FROM dual;
184 IF (l_journal_id <=0)
185 THEN
186 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
187 FND_MESSAGE.SET_TOKEN('SKEY','journal_id');
188 FND_MSG_PUB.Add;
189 RAISE FND_API.G_EXC_ERROR;
190 END IF;
191
192 -- Set up PL/SQL record and insert item into ic_jrnl_mst
193 l_ic_jrnl_mst_rec.journal_id := l_journal_id;
194 l_ic_jrnl_mst_rec.journal_no := l_trans_rec.journal_no;
195 l_ic_jrnl_mst_rec.journal_comment := NULL;
196 l_ic_jrnl_mst_rec.posting_id := 0;
197 l_ic_jrnl_mst_rec.print_cnt := 0;
198 l_ic_jrnl_mst_rec.posted_ind := 1;
199 l_ic_jrnl_mst_rec.orgn_code := l_trans_rec.orgn_code;
200 l_ic_jrnl_mst_rec.creation_date := SYSDATE;
201 l_ic_jrnl_mst_rec.last_update_date := SYSDATE;
202 l_ic_jrnl_mst_rec.created_by := l_user_id;
203 l_ic_jrnl_mst_rec.last_updated_by := l_user_id;
204 l_ic_jrnl_mst_rec.last_update_login := TO_NUMBER(FND_PROFILE.Value(
205 'LOGIN_ID'));
206 l_ic_jrnl_mst_rec.delete_mark := 0;
207 l_ic_jrnl_mst_rec.text_code := NULL;
208 l_ic_jrnl_mst_rec.in_use := 0;
209 l_ic_jrnl_mst_rec.program_application_id :=NULL;
210 l_ic_jrnl_mst_rec.program_id := NULL;
211 l_ic_jrnl_mst_rec.program_update_date := SYSDATE;
212 l_ic_jrnl_mst_rec.request_id := NULL;
213 l_ic_jrnl_mst_rec.last_update_login := l_user_id;
214
215 IF NOT GMI_QUANTITY_PVT.insert_ic_jrnl_mst(l_ic_jrnl_mst_rec)
216 THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 -- Write transactions to ic_adjs_jnl
221 -- First get the surrogate key (doc_id) for the transaction.
222 SELECT gem5_doc_id_s.nextval INTO l_doc_id FROM dual;
223 IF (l_doc_id <=0)
224 THEN
225 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
226 FND_MESSAGE.SET_TOKEN('SKEY','doc_id');
227 FND_MSG_PUB.Add;
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230
231 -- If create or Adjust transaction then insert 1 row to
232 -- ic_adjs_jnl. If Move, Change lot status or Change
233 -- QC Grade then need to insert 2 rows into ic_adjs_jnl.
234
235 IF (l_trans_rec.trans_type <= 2)
236 THEN
237 l_num_rows := 1;
238 ELSE
239 l_num_rows := 2;
240 END IF;
241
242 FOR l_loop_ctr IN 1..l_num_rows LOOP
243
244 -- Set up PL/SQL record and insert row into ic_adjs_jnl
245 -- Get the surrogate key (line_id) for the line except for
246 -- 'New' QC grade and lot status changes where the line_id
247 -- is duplicated.
248
249 IF (l_loop_ctr = 1 OR l_trans_rec.trans_type = 3)
250 THEN
251 SELECT gem5_line_id_s.nextval INTO l_line_id FROM dual;
252 IF (l_line_id <=0)
253 THEN
254 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
255 FND_MESSAGE.SET_TOKEN('SKEY','line_id');
256 FND_MSG_PUB.Add;
257 RAISE FND_API.G_EXC_ERROR;
258 END IF;
259 END IF;
260
261 IF (l_trans_rec.trans_type = 1)
262 THEN
263 l_ic_adjs_jnl_rec.trans_type :='CREI';
264 ELSIF (l_trans_rec.trans_type = 2)
265 THEN
266 l_ic_adjs_jnl_rec.trans_type :='ADJI';
267 ELSIF (l_trans_rec.trans_type = 3)
268 THEN
269 l_ic_adjs_jnl_rec.trans_type :='TRNI';
270 ELSIF (l_trans_rec.trans_type = 4)
271 THEN
272 l_ic_adjs_jnl_rec.trans_type :='STSI';
273 ELSIF (l_trans_rec.trans_type = 5)
274 THEN
275 l_ic_adjs_jnl_rec.trans_type :='GRDI';
276 END IF;
277
278 l_ic_adjs_jnl_rec.trans_flag := 0;
279 l_ic_adjs_jnl_rec.doc_id := l_doc_id;
280 l_ic_adjs_jnl_rec.doc_line := l_loop_ctr;
281 l_ic_adjs_jnl_rec.journal_id := l_journal_id;
282 l_ic_adjs_jnl_rec.completed_ind := 1;
283 l_ic_adjs_jnl_rec.reason_code := l_trans_rec.reason_code;
284 l_ic_adjs_jnl_rec.doc_date := l_trans_rec.trans_date;
285 l_ic_adjs_jnl_rec.item_id := l_item_id;
286 l_ic_adjs_jnl_rec.item_um := l_trans_rec.item_um;
287 l_ic_adjs_jnl_rec.item_um2 := l_trans_rec.item_um2;
288 l_ic_adjs_jnl_rec.lot_id := l_lot_id;
289
290
291 -- For Move , QC Grade and lot Status change set values according
292 -- to 'Old' and 'New' transaction.
293 IF (l_loop_ctr = 1)
294 THEN
295 l_ic_adjs_jnl_rec.whse_code := l_trans_rec.from_whse_code;
296 l_ic_adjs_jnl_rec.location := l_trans_rec.from_location;
297 l_ic_adjs_jnl_rec.qc_grade := l_old_qc_grade;
298 l_ic_adjs_jnl_rec.lot_status := l_old_lot_status;
299 IF (l_trans_rec.trans_type < 3)
300 THEN
301 l_ic_adjs_jnl_rec.qty := l_trans_rec.trans_qty;
302 l_ic_adjs_jnl_rec.qty2 := l_trans_rec.trans_qty2;
303 l_ic_adjs_jnl_rec.line_type := 0;
304 -- 02/06/99 Add this here, to get the correct lot status for
305 -- Create and Adjust
306 l_ic_adjs_jnl_rec.lot_status := l_trans_rec.lot_status;
307 ELSE
308 l_ic_adjs_jnl_rec.qty := 0 - l_trans_rec.trans_qty;
309 l_ic_adjs_jnl_rec.qty2 := 0 - l_trans_rec.trans_qty2;
310 l_ic_adjs_jnl_rec.line_type := -1;
311 END IF;
312 ELSE
313 l_ic_adjs_jnl_rec.whse_code := l_trans_rec.to_whse_code;
314 l_ic_adjs_jnl_rec.location := l_trans_rec.to_location;
315 l_ic_adjs_jnl_rec.qty := l_trans_rec.trans_qty;
316 l_ic_adjs_jnl_rec.qty2 := l_trans_rec.trans_qty2;
317 l_ic_adjs_jnl_rec.qc_grade := l_trans_rec.qc_grade;
318 l_ic_adjs_jnl_rec.lot_status := l_trans_rec.lot_status;
319 l_ic_adjs_jnl_rec.line_type := 1;
320 END IF;
321
322 l_ic_adjs_jnl_rec.line_id := l_line_id;
323 l_ic_adjs_jnl_rec.co_code := l_trans_rec.co_code;
324 l_ic_adjs_jnl_rec.orgn_code := l_trans_rec.orgn_code;
325 l_ic_adjs_jnl_rec.no_inv := 0;
326 l_ic_adjs_jnl_rec.no_trans := 0;
327 l_ic_adjs_jnl_rec.creation_date := SYSDATE;
328 l_ic_adjs_jnl_rec.created_by := l_user_id;
329 l_ic_adjs_jnl_rec.last_update_date := SYSDATE;
330 l_ic_adjs_jnl_rec.trans_cnt := 0;
331 l_ic_adjs_jnl_rec.last_updated_by := l_user_id;
332 l_ic_adjs_jnl_rec.program_application_id :=NULL;
333 l_ic_adjs_jnl_rec.program_id := NULL;
334 l_ic_adjs_jnl_rec.program_update_date := SYSDATE;
335 l_ic_adjs_jnl_rec.request_id := NULL;
336 l_ic_adjs_jnl_rec.last_update_login :=l_user_id;
337
338
339 IF NOT GMI_QUANTITY_PVT.insert_ic_adjs_jnl(l_ic_adjs_jnl_rec)
340 THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343
344 --
345 -- Now polpulate the completed transaction PL/SQL record
346 -- and call the completed transaction processor.
347 --
348 l_cmp_tran_rec.item_id := l_item_id;
349 l_cmp_tran_rec.line_id := l_line_id;
350 l_cmp_tran_rec.trans_id := 0;
351 l_cmp_tran_rec.co_code := l_trans_rec.co_code;
352 l_cmp_tran_rec.orgn_code := l_trans_rec.orgn_code;
353 l_cmp_tran_rec.whse_code := l_ic_adjs_jnl_rec.whse_code;
354 l_cmp_tran_rec.lot_id := l_lot_id;
355 l_cmp_tran_rec.location := l_ic_adjs_jnl_rec.location;
356 l_cmp_tran_rec.doc_id := l_doc_id;
357 l_cmp_tran_rec.doc_type := l_ic_adjs_jnl_rec.trans_type;
358 l_cmp_tran_rec.doc_line := l_ic_adjs_jnl_rec.doc_line;
359 l_cmp_tran_rec.line_type := l_ic_adjs_jnl_rec.line_type;
360 l_cmp_tran_rec.reason_code := l_ic_adjs_jnl_rec.reason_code;
361 l_cmp_tran_rec.creation_date := SYSDATE;
362 l_cmp_tran_rec.trans_date := l_ic_adjs_jnl_rec.doc_date;
363 l_cmp_tran_rec.trans_qty := l_ic_adjs_jnl_rec.qty;
364 l_cmp_tran_rec.trans_qty2 := l_ic_adjs_jnl_rec.qty2;
365 l_cmp_tran_rec.qc_grade := l_ic_adjs_jnl_rec.qc_grade;
366 l_cmp_tran_rec.lot_status := l_ic_adjs_jnl_rec.lot_status;
367 l_cmp_tran_rec.trans_stat := NULL;
368 l_cmp_tran_rec.trans_um := l_trans_rec.item_um;
369 l_cmp_tran_rec.trans_um2 := l_trans_rec.item_um2;
370 l_cmp_tran_rec.user_id := l_user_id;
371 l_cmp_tran_rec.gl_posted_ind := 0;
372 l_cmp_tran_rec.event_id := 0;
373 l_cmp_tran_rec.text_code := NULL;
374
375 IF NOT GMI_CMP_TRAN_PVT.Update_quantity_transaction(l_cmp_tran_rec)
376 THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379
380 END LOOP;
381
382 -- END of API Body
383
384 -- Standard Check of p_commit.
385 IF FND_API.to_boolean(p_commit)
386 THEN
387 COMMIT WORK;
388 END IF;
389 -- Success message
390 IF (l_trans_rec.trans_type = 1)
391 THEN
392 FND_MESSAGE.SET_NAME('GMI','IC_API_CRE_TRAN_POSTED');
393 ELSIF (l_trans_rec.trans_type = 2)
394 THEN
395 FND_MESSAGE.SET_NAME('GMI','IC_API_ADJ_TRAN_POSTED');
396 ELSIF (l_trans_rec.trans_type = 3)
397 THEN
398 FND_MESSAGE.SET_NAME('GMI','IC_API_TRN_TRAN_POSTED');
399 ELSIF (l_trans_rec.trans_type = 4)
400 THEN
401 FND_MESSAGE.SET_NAME('GMI','IC_API_STS_TRAN_POSTED');
402 ELSIF (l_trans_rec.trans_type = 5)
403 THEN
404 FND_MESSAGE.SET_NAME('GMI','IC_API_GRD_TRAN_POSTED');
405 END IF;
406
407 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_trans_rec.item_no);
408 FND_MESSAGE.SET_TOKEN('LOT_NO',p_trans_rec.lot_no);
409 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',p_trans_rec.sublot_no);
410 FND_MSG_PUB.Add;
411 -- Standard Call to get message count and if count is 1,
412 -- get message info.
413
414 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
415 , p_count => x_msg_count
416 , p_data => x_msg_data
417 );
418
419 EXCEPTION
420 WHEN FND_API.G_EXC_ERROR THEN
421 ROLLBACK TO Inventory_Posting;
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
424 , p_count => x_msg_count
425 , p_data => x_msg_data
426 );
427
428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
429 ROLLBACK TO Inventory_Posting;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
432 , p_count => x_msg_count
433 , p_data => x_msg_data
434 );
435 WHEN OTHERS THEN
436 ROLLBACK TO Inventory_Posting;
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438 --IF FND_MSG_PUB.check_msg_level
439 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
440 --THEN
441
442 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
443 , l_api_name
444 );
445 -- END IF;
446 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
447 , p_count => x_msg_count
448 , p_data => x_msg_data
449 );
450
451 END Inventory_Posting;
452
453 END GMI_QUANTITY_PUB;