[Home] [Help]
PACKAGE BODY: APPS.GMIVTDX
Source
1 PACKAGE BODY GMIVTDX AS
2 /* $Header: GMIVTDXB.pls 120.3 2006/09/18 16:02:15 jsrivast noship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | GMIVTDXB.pls |
10 | |
11 | PACKAGE NAME |
12 | GMIVTDX |
13 | |
14 | TYPE |
15 | Private |
16 | |
17 | |
18 | DESCRIPTION |
19 | This package contains the private APIs for Process / Discrete Transfer|
20 | inserting transactions, creating lots in ODM and updating balances |
21 | in OPM inventory and Oracle Inventory. |
22 | |
23 | Contents |
24 | create_txn_update_balances |
25 | create_txn_update_bal_in_opm |
26 | complete_transaction_in_opm |
27 | create_txn_update_bal_in_odm |
28 | |
29 | HISTORY |
30 | Created - Jalaj Srivastava |
31 | |
32 | |
33 +==========================================================================+
34 */
35
36 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
37
38 /* Global variables */
39 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVDX';
40 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
41 -- msg level threshhold gobal
42 -- variable.
43 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
44 -- to decide to log a debug msg.
45 /* +==========================================================================+
46 | PROCEDURE NAME |
47 | create_txn_update_balances |
48 | |
49 | TYPE |
50 | Private |
51 | |
52 | USAGE |
53 | Sets up and posts transactions and updates balances in OPM inventory |
54 | and Oracle Inventory. | |
55 | |
56 | RETURNS |
57 | Via x_ OUT parameters |
58 | |
59 | HISTORY |
60 | Created Jalaj Srivastava |
61 | |
62 +==========================================================================+ */
63
64 PROCEDURE create_txn_update_balances
65 ( p_api_version IN NUMBER
66 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
67 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
68 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
69 , x_return_status OUT NOCOPY VARCHAR2
70 , x_msg_count OUT NOCOPY NUMBER
71 , x_msg_data OUT NOCOPY VARCHAR2
72 , p_transfer_id IN NUMBER
73 , p_line_id IN NUMBER
74 , x_transaction_header_id IN OUT NOCOPY NUMBER
75 ) IS
76 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_balances' ;
77 l_api_version CONSTANT NUMBER := 1.0 ;
78 l_txn_vars_rec txn_vars_type;
79 l_odm_txn_type_rec inv_validate.transaction;
80 l_hdr_row gmi_discrete_transfers%ROWTYPE;
81 l_line_row gmi_discrete_transfer_lines%ROWTYPE;
82 l_lot_row_tbl GMIVDX.lot_row_tbl;
83 l_lot_count pls_integer := 0;
84
85 Cursor Cur_get_lot_records(Vtransfer_id NUMBER, Vline_id NUMBER) IS
86 SELECT *
87 FROM gmi_discrete_transfer_lots
88 WHERE transfer_id = Vtransfer_id
89 AND line_id = Vline_id;
90
91 BEGIN
92
93 IF FND_API.to_boolean(p_init_msg_list) THEN
94 FND_MSG_PUB.Initialize;
95 END IF;
96
97 -- Standard call to check for call compatibility.
98 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
99 p_api_version ,
100 l_api_name ,
101 G_PKG_NAME ) THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104 x_return_status :=FND_API.G_RET_STS_SUCCESS;
105
106 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
107 log_msg('Beginning of procedure create_txn_update_balances');
108 END IF;
109
110 SELECT *
111 INTO l_hdr_row
112 FROM gmi_discrete_transfers
113 WHERE transfer_id = p_transfer_id;
114
115 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
116 log_msg('After selecting header row from database. transfer id is '||to_char(l_hdr_row.transfer_id));
117 END IF;
118
119 SELECT *
120 INTO l_line_row
121 FROM gmi_discrete_transfer_lines
122 WHERE transfer_id = p_transfer_id
123 AND line_id = p_line_id;
124
125 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
126 log_msg('After selecting line row from database. line id is '||to_char(l_line_row.line_id));
127 END IF;
128
129 --if lots are defined at lot level
130 IF (l_line_row.lot_level = 1) THEN
131 FOR Cur_get_lot_records_rec IN Cur_get_lot_records (p_transfer_id, p_line_id) LOOP
132 l_lot_count := l_lot_count + 1;
133 l_lot_row_tbl(l_lot_count) := Cur_get_lot_records_rec;
134 END LOOP;
135 END IF;
136
137 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
138 log_msg('After selecting lot rows from database. no of lots is '||to_char(l_lot_row_tbl.count));
139 END IF;
140
141 IF (l_hdr_row.transfer_type = 0 ) THEN
142 l_odm_txn_type_rec.transaction_type_id := 42;
143 l_txn_vars_rec.opm_qty_line_type := -1;
144 l_txn_vars_rec.odm_qty_line_type := 1;
145 ELSIF (l_hdr_row.transfer_type = 1 ) THEN
146 l_odm_txn_type_rec.transaction_type_id := 32;
147 l_txn_vars_rec.opm_qty_line_type := 1;
148 l_txn_vars_rec.odm_qty_line_type := -1;
149 END IF;
150
151 IF (inv_validate.transaction_type (l_odm_txn_type_rec) = inv_validate.F) THEN
152 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
153 log_msg('Failed call to inv_validate.transaction_type in procedure create_txn_update_balances');
154 END IF;
155 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
156 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','TRANSACTION'),FALSE);
157 FND_MSG_PUB.Add;
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160
161 SELECT item_um,item_um2,
162 lot_ctl,lot_indivisible
163 INTO l_txn_vars_rec.opm_item_um, l_txn_vars_rec.opm_item_um2,
164 l_txn_vars_rec.lot_control, l_txn_vars_rec.opm_lot_indivisible
165 FROM ic_item_mst_b
166 WHERE item_id = l_line_row.opm_item_id;
167
168 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
169 log_msg('before calling create_txn_update_bal_in_opm');
170 END IF;
171
172 create_txn_update_bal_in_opm
173 (
174 p_api_version => p_api_version
175 , p_init_msg_list => FND_API.G_FALSE
176 , p_commit => FND_API.G_FALSE
177 , p_validation_level => p_validation_level
178 , x_return_status => x_return_status
179 , x_msg_count => x_msg_count
180 , x_msg_data => x_msg_data
181 , p_hdr_row => l_hdr_row
182 , p_line_row => l_line_row
183 , p_lot_row_tbl => l_lot_row_tbl
184 , p_txn_vars_rec => l_txn_vars_rec
185 );
186
187 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
188 log_msg('After call to procedure create_txn_update_balances_in_opm. return status is '||x_return_status);
189 END IF;
190 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
191 RAISE FND_API.G_EXC_ERROR;
192 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END IF;
195
196 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
197 log_msg('before calling create_txn_update_bal_in_odm');
198 END IF;
199
200 create_txn_update_bal_in_odm
201 (
202 p_api_version => p_api_version
203 , p_init_msg_list => FND_API.G_FALSE
204 , p_commit => FND_API.G_FALSE
205 , p_validation_level => p_validation_level
206 , x_return_status => x_return_status
207 , x_msg_count => x_msg_count
208 , x_msg_data => x_msg_data
209 , p_hdr_row => l_hdr_row
210 , p_line_row => l_line_row
211 , p_lot_row_tbl => l_lot_row_tbl
212 , p_txn_vars_rec => l_txn_vars_rec
213 , p_odm_txn_type_rec => l_odm_txn_type_rec
214 , x_transaction_header_id => x_transaction_header_id
215 );
216 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
217 log_msg('After call to procedure create_txn_update_balances_in_odm. return status is '||x_return_status||' transaction header id is '||to_char(x_transaction_header_id));
218 END IF;
219 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
220 RAISE FND_API.G_EXC_ERROR;
221 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224
225 FND_MSG_PUB.Count_AND_GET
226 (p_count => x_msg_count, p_data => x_msg_data);
227
228
229 EXCEPTION
230
231 WHEN FND_API.G_EXC_ERROR THEN
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 FND_MSG_PUB.Count_AND_GET
234 (p_count => x_msg_count, p_data => x_msg_data);
235
236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 FND_MSG_PUB.Count_AND_GET
239 (p_count => x_msg_count, p_data => x_msg_data);
240
241 WHEN OTHERS THEN
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 IF (SQLCODE IS NOT NULL) THEN
244 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
245 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
246 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
247 FND_MSG_PUB.Add;
248 END IF;
249 FND_MSG_PUB.Count_AND_GET
250 (p_count => x_msg_count, p_data => x_msg_data);
251
252 END create_txn_update_balances;
253
254
255 /* +==========================================================================+
256 | PROCEDURE NAME |
257 | create_txn_update_bal_in_opm |
258 | |
259 | TYPE |
260 | Private |
261 | |
262 | USAGE |
263 | Sets up and posts transactions and updates balances in OPM inventory |
264 | Does a final check to prevent OPM inventory balances from going |
265 | negative and calls the OPM inventory engine to post the transaction |
266 | |
267 | RETURNS |
268 | Via x_ OUT parameters |
269 | |
270 | HISTORY |
271 | Created Jalaj Srivastava |
272 | |
273 +==========================================================================+ */
274
275 PROCEDURE create_txn_update_bal_in_opm
276 ( p_api_version IN NUMBER
277 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
278 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
279 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
280 , x_return_status OUT NOCOPY VARCHAR2
281 , x_msg_count OUT NOCOPY NUMBER
282 , x_msg_data OUT NOCOPY VARCHAR2
283 , p_hdr_row IN gmi_discrete_transfers%ROWTYPE
284 , p_line_row IN gmi_discrete_transfer_lines%ROWTYPE
285 , p_lot_row_tbl IN GMIVDX.lot_row_tbl
286 , p_txn_vars_rec IN txn_vars_type
287 )IS
288 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_bal_in_opm' ;
289 l_api_version CONSTANT NUMBER := 1.0 ;
290 l_tran_row ic_tran_cmp%ROWTYPE;
291 l_ic_loct_inv_row ic_loct_inv%ROWTYPE;
292 TYPE tran_rec_tbl IS TABLE OF GMI_TRANS_ENGINE_PUB.ictran_rec INDEX BY BINARY_INTEGER;
293 l_tran_rec_tbl tran_rec_tbl;
294 BEGIN
295
296 IF FND_API.to_boolean(p_init_msg_list) THEN
297 FND_MSG_PUB.Initialize;
298 END IF;
299
300 SAVEPOINT create_transaction_in_opm;
301
302 -- Standard call to check for call compatibility.
303 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
304 p_api_version ,
305 l_api_name ,
306 G_PKG_NAME ) THEN
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309
310 x_return_status :=FND_API.G_RET_STS_SUCCESS;
311
312 --we need to construct the record for inserting transactions in OPM.
313 --{
314 IF (p_line_row.lot_level = 0) THEN --lots at line level
315
316 l_tran_rec_tbl(1).trans_qty := (p_line_row.opm_primary_quantity) * (p_txn_vars_rec.opm_qty_line_type);
317 l_tran_rec_tbl(1).line_detail_id := NULL;
318 l_tran_rec_tbl(1).lot_id := p_line_row.opm_lot_id;
319 l_tran_rec_tbl(1).qc_grade := p_line_row.opm_grade;
320 l_tran_rec_tbl(1).lot_status := p_line_row.opm_lot_status;
321 l_tran_rec_tbl(1).trans_qty2 := (p_line_row.quantity2) * (p_txn_vars_rec.opm_qty_line_type);
322 l_tran_rec_tbl(1).text_code := p_line_row.text_code;
323
324 ELSIF (p_line_row.lot_level = 1) THEN --lots at lots level
325 --there could be multiple lot records.
326 FOR i in 1..p_lot_row_tbl.count LOOP --{
327 l_tran_rec_tbl(i).trans_qty := (p_lot_row_tbl(i).opm_primary_quantity) * (p_txn_vars_rec.opm_qty_line_type);
328 l_tran_rec_tbl(i).line_detail_id := p_lot_row_tbl(i).line_detail_id;
329 l_tran_rec_tbl(i).lot_id := p_lot_row_tbl(i).opm_lot_id;
330 l_tran_rec_tbl(i).qc_grade := p_lot_row_tbl(i).opm_grade;
331 l_tran_rec_tbl(i).lot_status := p_lot_row_tbl(i).opm_lot_status;
332 l_tran_rec_tbl(i).trans_qty2 := (p_lot_row_tbl(i).quantity2) * (p_txn_vars_rec.opm_qty_line_type);
333 l_tran_rec_tbl(i).text_code := p_lot_row_tbl(i).text_code;
334
335 END LOOP;--}
336
337 END IF;--}
338
339 FOR i IN 1..l_tran_rec_tbl.count LOOP --{
340 l_tran_rec_tbl(i).co_code := p_hdr_row.co_code;
341 l_tran_rec_tbl(i).line_type := p_txn_vars_rec.opm_qty_line_type;
342 l_tran_rec_tbl(i).trans_um := p_txn_vars_rec.opm_item_um;
343 l_tran_rec_tbl(i).trans_um2 := p_txn_vars_rec.opm_item_um2; /* UM2 only for OPM */
344 l_tran_rec_tbl(i).orgn_code := p_hdr_row.orgn_code;
345 l_tran_rec_tbl(i).doc_type := 'DXFR';
346 l_tran_rec_tbl(i).doc_id := p_hdr_row.transfer_id;
347 l_tran_rec_tbl(i).trans_date := p_hdr_row.trans_date;
348 l_tran_rec_tbl(i).doc_line := p_line_row.line_no;
349 l_tran_rec_tbl(i).line_id := p_line_row.line_id;
350 l_tran_rec_tbl(i).item_id := p_line_row.opm_item_id;
351 l_tran_rec_tbl(i).whse_code := p_line_row.opm_whse_code;
352 l_tran_rec_tbl(i).location := p_line_row.opm_location;
353 l_tran_rec_tbl(i).reason_code := p_line_row.opm_reason_code;
354 l_tran_rec_tbl(i).non_inv := 0;
355 l_tran_rec_tbl(i).user_id := FND_GLOBAL.USER_ID;
356 l_tran_rec_tbl(i).trans_stat := NULL;
357 l_tran_rec_tbl(i).staged_ind := NULL;
358 l_tran_rec_tbl(i).event_id := NULL;
359 l_tran_rec_tbl(i).create_lot_index := NULL;
360
361 /* *******************************************************************************************
362 Before posting the transaction we again want to validate whether there is enough onhand
363 for the transaction. this is only when transfer type is 0 (Process to discrete).
364 ******************************************************************************************* */
365 --{
366 IF (l_tran_rec_tbl(i).line_type = -1) THEN
367 --transfer is from process to discrete
368 l_ic_loct_inv_row.whse_code := l_tran_rec_tbl(i).whse_code;
369 l_ic_loct_inv_row.location := l_tran_rec_tbl(i).location;
370 l_ic_loct_inv_row.item_id := l_tran_rec_tbl(i).item_id;
371 l_ic_loct_inv_row.lot_id := l_tran_rec_tbl(i).lot_id;
372
373 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
374 log_msg('validation for quantity being transferrred from OPM such that OPM inventory is not driven negative before posting the transaction.');
375 END IF;
376 --{
377 IF GMIVDBL.ic_loct_inv_select(l_ic_loct_inv_row, l_ic_loct_inv_row) THEN
378
379 IF ( (p_txn_vars_rec.opm_lot_indivisible = 1)
380 AND (l_ic_loct_inv_row.loct_onhand <> abs(l_tran_rec_tbl(i).trans_qty))
381 ) THEN
382 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_INDIVISIBLE_LOT');
383 FND_MESSAGE.SET_TOKEN('LINE_NO',to_char(l_tran_rec_tbl(i).doc_line));
384 FND_MSG_PUB.Add;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387 ELSE
388 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_CANNOT_GET_ONHAND');
389 FND_MESSAGE.SET_TOKEN('LINE_NO',to_char(l_tran_rec_tbl(i).doc_line));
390 FND_MSG_PUB.Add;
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;--}
393
394 END IF;--}
395
396 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
397 log_msg('Before calling GMI_TRANS_ENGINE_PVT.create_completed_transaction for lot id '||to_char(l_tran_rec_tbl(i).lot_id));
398 END IF;
399 --lets call the GMI engine to post completed transaction
400 GMI_TRANS_ENGINE_PVT.create_completed_transaction
401 (
402 p_api_version => 1.0
403 , p_init_msg_list => FND_API.G_FALSE
404 , p_commit => FND_API.G_FALSE
405 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
406 , x_return_status => x_return_status
407 , x_msg_count => x_msg_count
408 , x_msg_data => x_msg_data
409 , p_tran_rec => l_tran_rec_tbl(i)
410 , x_tran_row => l_tran_row
411 );
412
413 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
414 log_msg('After the call to GMI_TRANS_ENGINE_PVT.create_completed_transaction. return status is '||x_return_status);
415 END IF;
416 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
417 RAISE FND_API.G_EXC_ERROR;
418 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421
422 END LOOP;--}
423
424 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
425 log_msg('Posted '||to_char(l_tran_rec_tbl.count)||' completed transactions to OPM');
426 END IF;
427
428 FND_MSG_PUB.Count_AND_GET
429 (p_count => x_msg_count, p_data => x_msg_data);
430
431
432 EXCEPTION
433
434 WHEN FND_API.G_EXC_ERROR THEN
435 ROLLBACK to create_transaction_in_opm;
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 FND_MSG_PUB.Count_AND_GET
438 (p_count => x_msg_count, p_data => x_msg_data);
439
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
441 ROLLBACK to create_transaction_in_opm;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 FND_MSG_PUB.Count_AND_GET
444 (p_count => x_msg_count, p_data => x_msg_data);
445
446 WHEN OTHERS THEN
447 ROLLBACK to create_transaction_in_opm;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 IF (SQLCODE IS NOT NULL) THEN
450 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
451 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
452 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
453 FND_MSG_PUB.Add;
454 END IF;
455 FND_MSG_PUB.Count_AND_GET
456 (p_count => x_msg_count, p_data => x_msg_data);
457
458 END create_txn_update_bal_in_opm;
459
460 /* +==========================================================================+
461 | PROCEDURE NAME |
462 | create_txn_update_bal_in_odm |
463 | |
464 | TYPE |
465 | Private |
466 | |
467 | USAGE |
468 | Sets up and posts transactions and updates balances in OPM inventory |
469 | |
470 | RETURNS |
471 | Via x_ OUT parameters |
472 | |
473 | HISTORY |
474 | Created Jalaj Srivastava |
475 | |
476 +==========================================================================+ */
477
478 PROCEDURE create_txn_update_bal_in_odm
479 ( p_api_version IN NUMBER
480 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
481 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
482 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
483 , x_return_status OUT NOCOPY VARCHAR2
484 , x_msg_count OUT NOCOPY NUMBER
485 , x_msg_data OUT NOCOPY VARCHAR2
486 , p_hdr_row IN gmi_discrete_transfers%ROWTYPE
487 , p_line_row IN gmi_discrete_transfer_lines%ROWTYPE
488 , p_lot_row_tbl IN GMIVDX.lot_row_tbl
489 , p_txn_vars_rec IN txn_vars_type
490 , p_odm_txn_type_rec IN inv_validate.transaction
491 , x_transaction_header_id IN OUT NOCOPY NUMBER
492 ) IS
493 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_bal_in_odm' ;
494 l_api_version CONSTANT NUMBER := 1.0 ;
495 l_mmtt_row mtl_material_transactions_temp%ROWTYPE;
496 TYPE mtlt_row_tbl IS TABLE OF mtl_transaction_lots_temp%ROWTYPE INDEX BY BINARY_INTEGER;
497 l_mtlt_row_tbl mtlt_row_tbl;
498 l_count pls_integer;
499
500 /* Jalaj Srivastava Bug 3812701 */
501 l_odm_quantity_uom_code mtl_units_of_measure.uom_code%TYPE;
502 Cursor Cur_get_uom_code (Vum_code VARCHAR2) IS
503 SELECT uom_code
504 FROM sy_uoms_mst
505 WHERE um_code = Vum_code;
506
507 BEGIN
508
509 IF FND_API.to_boolean(p_init_msg_list) THEN
510 FND_MSG_PUB.Initialize;
511 END IF;
512
513 SAVEPOINT create_transaction_in_odm;
514
515 -- Standard call to check for call compatibility.
516 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
517 p_api_version ,
518 l_api_name ,
519 G_PKG_NAME ) THEN
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522
523 x_return_status :=FND_API.G_RET_STS_SUCCESS;
524
525 --we need to construct the record for inserting transactions in ODM.
526 --then we need to insert into mtl_material_transactions_temp table.
527 --this is done for each line.
528
529 --this procedure is called line by line.
530
531 IF (x_transaction_header_id IS NULL) THEN
532 -- get the transaction_header_id
533 SELECT mtl_material_transactions_s.NEXTVAL
534 INTO x_transaction_header_id
535 FROM DUAL;
536 END IF;
537
538 -- get the transaction_temp_id
539 SELECT mtl_material_transactions_s.NEXTVAL
540 INTO l_mmtt_row.transaction_temp_id
541 FROM DUAL;
542
543 --we need to construct/insert a record in mmtt
544
545 l_mmtt_row.transaction_header_id := x_transaction_header_id;
546 l_mmtt_row.transaction_mode := 1; /* Online */
547 l_mmtt_row.lock_flag := 'N';
548 l_mmtt_row.inventory_item_id := p_line_row.odm_item_id;
549 l_mmtt_row.organization_id := p_line_row.odm_inv_organization_id;
550 l_mmtt_row.subinventory_code := p_line_row.odm_subinventory;
551 l_mmtt_row.locator_id := p_line_row.odm_locator_id;
552 l_mmtt_row.transaction_quantity := round(((p_line_row.quantity) * (p_txn_vars_rec.odm_qty_line_type)),5);
553 l_mmtt_row.primary_quantity := (p_line_row.odm_primary_quantity) * (p_txn_vars_rec.odm_qty_line_type);
554 l_mmtt_row.secondary_transaction_quantity := (p_line_row.quantity2) * (p_txn_vars_rec.odm_qty_line_type);
555
556 /* Jalaj Srivastava Bug 3182701 */
557 /* Get uom_code for this um from sy_uoms_mst */
558
559 OPEN Cur_get_uom_code(p_line_row.quantity_um);
560 FETCH Cur_get_uom_code INTO l_odm_quantity_uom_code;
561 CLOSE Cur_get_uom_code;
562
563 l_mmtt_row.transaction_uom := l_odm_quantity_uom_code;
564
565 --Get item's secondary uom code in ODM
566 SELECT secondary_uom_code
567 INTO l_mmtt_row.secondary_uom_code
568 FROM mtl_system_items_b
569 WHERE inventory_item_id = l_mmtt_row.inventory_item_id
570 AND organization_id = l_mmtt_row.organization_id;
571
572 l_mmtt_row.transaction_type_id := p_odm_txn_type_rec.transaction_type_id;
573 l_mmtt_row.transaction_action_id := p_odm_txn_type_rec.transaction_action_id;
574 l_mmtt_row.transaction_source_type_id := p_odm_txn_type_rec.transaction_source_type_id;
575
576 IF (p_hdr_row.transfer_type = 0) THEN
577 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_FROM_SOURCE_NAME');
578 l_mmtt_row.Transaction_source_name := fnd_message.get;
579 ELSIF (p_hdr_row.transfer_type = 1) THEN
580 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_TO_SOURCE_NAME');
581 l_mmtt_row.transaction_source_name := fnd_message.get;
582 END IF;
583
584 l_mmtt_row.transaction_line_number := p_line_row.line_no;
585 l_mmtt_row.transaction_date := p_hdr_row.trans_date;
586 l_mmtt_row.acct_period_id := p_line_row.odm_period_id;
587 l_mmtt_row.distribution_account_id := p_line_row.odm_charge_account_id;
588 l_mmtt_row.transaction_reference := 'OPM'||' - '||'DXFR'||' - '||p_hdr_row.orgn_code||' - '||p_hdr_row.transfer_number||' - '||to_char(p_line_row.line_no);
589 l_mmtt_row.posting_flag := 'Y';
590 l_mmtt_row.process_flag := 'Y';
591 l_mmtt_row.final_completion_flag := 'N';
592 l_mmtt_row.reason_id := p_line_row.odm_reason_id;
593 l_mmtt_row.transaction_cost := round((p_line_row.odm_unit_cost * (l_mmtt_row.TRANSACTION_QUANTITY/l_mmtt_row.PRIMARY_QUANTITY)),6);
594 l_mmtt_row.revision := p_line_row.odm_item_revision;
595 l_mmtt_row.last_update_date := SYSDATE;
596 l_mmtt_row.last_updated_by := FND_GLOBAL.USER_ID;
597 l_mmtt_row.creation_date := SYSDATE;
598 l_mmtt_row.created_by := FND_GLOBAL.USER_ID;
599 l_mmtt_row.last_update_login := FND_GLOBAL.LOGIN_ID;
600
601 l_mmtt_row.source_code := 'OPM-DXFR-MIGRATION';
602 l_mmtt_row.source_line_id := p_line_row.line_id;
603 l_mmtt_row.transaction_source_id := p_hdr_row.transfer_id;
604
605 -- insert data into mtl_material_transactions_temp table
606 INSERT INTO mtl_material_transactions_temp
607 (
608 TRANSACTION_HEADER_ID,
609 TRANSACTION_TEMP_ID,
610 TRANSACTION_MODE,
611 LOCK_FLAG,
612 INVENTORY_ITEM_ID,
613 REVISION,
614 ORGANIZATION_ID,
615 SUBINVENTORY_CODE,
616 LOCATOR_ID,
617 TRANSACTION_QUANTITY,
618 PRIMARY_QUANTITY,
619 SECONDARY_TRANSACTION_QUANTITY,
620 TRANSACTION_UOM,
621 SECONDARY_UOM_CODE,
622 TRANSACTION_TYPE_ID,
623 TRANSACTION_ACTION_ID,
624 TRANSACTION_SOURCE_TYPE_ID,
625 TRANSACTION_SOURCE_NAME,
626 TRANSACTION_DATE,
627 ACCT_PERIOD_ID,
628 DISTRIBUTION_ACCOUNT_ID,
629 TRANSACTION_REFERENCE,
630 POSTING_FLAG,
631 PROCESS_FLAG,
632 FINAL_COMPLETION_FLAG,
633 TRANSACTION_LINE_NUMBER,
634 REASON_ID,
635 TRANSACTION_COST,
636 SOURCE_CODE,
637 SOURCE_LINE_ID,
638 TRANSACTION_SOURCE_ID,
639 CREATION_DATE,
640 CREATED_BY,
641 LAST_UPDATE_DATE,
642 LAST_UPDATED_BY,
643 LAST_UPDATE_LOGIN
644 )
645 VALUES
646 (
647 l_mmtt_row.transaction_header_id,
648 l_mmtt_row.transaction_temp_id,
649 l_mmtt_row.transaction_mode,
650 l_mmtt_row.lock_flag,
651 l_mmtt_row.inventory_item_id,
652 l_mmtt_row.revision,
653 l_mmtt_row.organization_id,
654 l_mmtt_row.subinventory_code,
655 l_mmtt_row.locator_id,
656 l_mmtt_row.transaction_quantity,
657 l_mmtt_row.primary_quantity,
658 l_mmtt_row.secondary_transaction_quantity,
659 l_mmtt_row.transaction_uom,
660 l_mmtt_row.secondary_uom_code,
661 l_mmtt_row.transaction_type_id,
662 l_mmtt_row.transaction_action_id,
663 l_mmtt_row.transaction_source_type_id,
664 l_mmtt_row.transaction_source_name,
665 l_mmtt_row.transaction_date,
666 l_mmtt_row.acct_period_id,
667 l_mmtt_row.distribution_account_id,
668 l_mmtt_row.transaction_reference,
669 l_mmtt_row.posting_flag,
670 l_mmtt_row.process_flag,
671 l_mmtt_row.final_completion_flag,
672 l_mmtt_row.transaction_line_number,
673 l_mmtt_row.reason_id,
674 l_mmtt_row.transaction_cost,
675 l_mmtt_row.source_code,
676 l_mmtt_row.source_line_id,
677 l_mmtt_row.transaction_source_id,
678 l_mmtt_row.creation_date,
679 l_mmtt_row.created_by,
680 l_mmtt_row.last_update_date,
681 l_mmtt_row.last_updated_by,
682 l_mmtt_row.last_update_login
683 );
684 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
685 log_msg('Inserted 1 record in mtl_material_transactions_temp');
686 END IF;
687
688 --lets see if we have records to be inserted in mtl_transaction_lots_temp
689 --line could have multiple lots
690 --{
691 IF (p_txn_vars_rec.lot_control = 1) THEN --if item is lot controlled
692
693 --{
694 IF (p_line_row.lot_level = 0) THEN --lot at line level
695 l_mtlt_row_tbl(1).transaction_quantity := round(((p_line_row.quantity) * (p_txn_vars_rec.odm_qty_line_type)),5);
696 l_mtlt_row_tbl(1).primary_quantity := (p_line_row.odm_primary_quantity) * (p_txn_vars_rec.odm_qty_line_type);
697 l_mtlt_row_tbl(1).lot_number := p_line_row.odm_lot_number;
698 l_mtlt_row_tbl(1).lot_expiration_date := p_line_row.odm_lot_expiration_date;
699 IF (p_line_row.quantity2 IS NOT NULL) THEN
700 l_mtlt_row_tbl(1).secondary_quantity := p_line_row.quantity2 * (p_txn_vars_rec.odm_qty_line_type);
701 END IF;
702
703 ELSIF (p_line_row.lot_level = 1) THEN --lots at lot level
704 --there could be multiple lot records
705 FOR i in 1..p_lot_row_tbl.count LOOP --{
706 l_mtlt_row_tbl(i).transaction_quantity := round(((p_lot_row_tbl(i).quantity) * (p_txn_vars_rec.odm_qty_line_type)),5);
707 l_mtlt_row_tbl(i).primary_quantity := (p_lot_row_tbl(i).odm_primary_quantity) * (p_txn_vars_rec.odm_qty_line_type);
708 l_mtlt_row_tbl(i).lot_number := p_lot_row_tbl(i).odm_lot_number;
709 l_mtlt_row_tbl(i).lot_expiration_date := p_lot_row_tbl(i).odm_lot_expiration_date;
710 IF (p_lot_row_tbl(i).quantity2 IS NOT NULL) THEN
711 l_mtlt_row_tbl(i).secondary_quantity := (p_lot_row_tbl(i).quantity2) * (p_txn_vars_rec.odm_qty_line_type);
712 END IF;
713
714 END LOOP;--}
715
716 END IF;--}
717
718
719 FOR i IN 1..l_mtlt_row_tbl.count LOOP --{
720
721 l_mtlt_row_tbl(i).transaction_temp_id := l_mmtt_row.transaction_temp_id;
722 l_mtlt_row_tbl(i).group_header_id := x_transaction_header_id;
723 l_mtlt_row_tbl(i).creation_date := SYSDATE;
724 l_mtlt_row_tbl(i).created_by := FND_GLOBAL.USER_ID;
725 l_mtlt_row_tbl(i).last_update_date := SYSDATE;
726 l_mtlt_row_tbl(i).last_updated_by := FND_GLOBAL.USER_ID;
727 l_mtlt_row_tbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
728
729 /* Jalaj Srivastava Bug 5401804
730 When OPM balances are migrated to Oracle Inventory,
731 process lots are first created in discrete lot master due
732 to which lot attribute info is lost in migartion transaction.
733 Populate lot attributes information from lot master
734 for lot transactions */
735 INSERT INTO MTL_TRANSACTION_LOTS_TEMP
736 (
737 transaction_temp_id
738 ,group_header_id
739 ,transaction_quantity
740 ,primary_quantity
741 ,secondary_quantity
742 ,lot_number
743 ,creation_date
744 ,created_by
745 ,last_update_date
746 ,last_updated_by
747 ,last_update_login
748 ,lot_expiration_date
749 ,status_id
750 ,lot_attribute_category
751 ,attribute_category
752 ,attribute1
753 ,attribute2
754 ,attribute3
755 ,attribute4
756 ,attribute5
757 ,attribute6
758 ,attribute7
759 ,attribute8
760 ,attribute9
761 ,attribute10
762 ,attribute11
763 ,attribute12
764 ,attribute13
765 ,attribute14
766 ,attribute15
767 ,c_attribute1
768 ,c_attribute2
769 ,c_attribute3
770 ,c_attribute4
771 ,c_attribute5
772 ,c_attribute6
773 ,c_attribute7
774 ,c_attribute8
775 ,c_attribute9
776 ,c_attribute10
777 ,c_attribute11
778 ,c_attribute12
779 ,c_attribute13
780 ,c_attribute14
781 ,c_attribute15
782 ,c_attribute16
783 ,c_attribute17
784 ,c_attribute18
785 ,c_attribute19
786 ,c_attribute20
787 ,n_attribute1
788 ,n_attribute2
789 ,n_attribute3
790 ,n_attribute4
791 ,n_attribute5
792 ,n_attribute6
793 ,n_attribute7
794 ,n_attribute8
795 ,n_attribute9
796 ,n_attribute10
797 ,d_attribute1
798 ,d_attribute2
799 ,d_attribute3
800 ,d_attribute4
801 ,d_attribute5
802 ,d_attribute6
803 ,d_attribute7
804 ,d_attribute8
805 ,d_attribute9
806 ,d_attribute10
807 ,grade_code
808 ,origination_date
809 ,date_code
810 ,change_date
811 ,age
812 ,retest_date
813 ,maturity_date
814 ,item_size
815 ,color
816 ,volume
817 ,volume_uom
818 ,place_of_origin
819 ,best_by_date
820 ,length
821 ,length_uom
822 ,recycled_content
823 ,thickness
824 ,thickness_uom
825 ,width
826 ,width_uom
827 ,territory_code
828 ,supplier_lot_number
829 ,vendor_name
830 ,parent_lot_number
831 ,origination_type
832 ,expiration_action_code
833 ,expiration_action_date
834 ,hold_date
835 ,reason_id
836 )
837 SELECT
838 l_mtlt_row_tbl(i).transaction_temp_id
839 ,l_mtlt_row_tbl(i).group_header_id
840 ,l_mtlt_row_tbl(i).transaction_quantity
841 ,l_mtlt_row_tbl(i).primary_quantity
842 ,l_mtlt_row_tbl(i).secondary_quantity
843 ,l_mtlt_row_tbl(i).lot_number
844 ,l_mtlt_row_tbl(i).creation_date
845 ,l_mtlt_row_tbl(i).created_by
846 ,l_mtlt_row_tbl(i).last_update_date
847 ,l_mtlt_row_tbl(i).last_updated_by
848 ,l_mtlt_row_tbl(i).last_update_login
849 ,mln.expiration_date
850 ,mln.status_id
851 ,mln.lot_attribute_category
852 ,mln.attribute_category
853 ,mln.attribute1
854 ,mln.attribute2
855 ,mln.attribute3
856 ,mln.attribute4
857 ,mln.attribute5
858 ,mln.attribute6
859 ,mln.attribute7
860 ,mln.attribute8
861 ,mln.attribute9
862 ,mln.attribute10
863 ,mln.attribute11
864 ,mln.attribute12
865 ,mln.attribute13
866 ,mln.attribute14
867 ,mln.attribute15
868 ,mln.c_attribute1
869 ,mln.c_attribute2
870 ,mln.c_attribute3
871 ,mln.c_attribute4
872 ,mln.c_attribute5
873 ,mln.c_attribute6
874 ,mln.c_attribute7
875 ,mln.c_attribute8
876 ,mln.c_attribute9
877 ,mln.c_attribute10
878 ,mln.c_attribute11
879 ,mln.c_attribute12
880 ,mln.c_attribute13
881 ,mln.c_attribute14
882 ,mln.c_attribute15
883 ,mln.c_attribute16
884 ,mln.c_attribute17
885 ,mln.c_attribute18
886 ,mln.c_attribute19
887 ,mln.c_attribute20
888 ,mln.n_attribute1
889 ,mln.n_attribute2
890 ,mln.n_attribute3
891 ,mln.n_attribute4
892 ,mln.n_attribute5
893 ,mln.n_attribute6
894 ,mln.n_attribute7
895 ,mln.n_attribute8
896 ,mln.n_attribute9
897 ,mln.n_attribute10
898 ,mln.d_attribute1
899 ,mln.d_attribute2
900 ,mln.d_attribute3
901 ,mln.d_attribute4
902 ,mln.d_attribute5
903 ,mln.d_attribute6
904 ,mln.d_attribute7
905 ,mln.d_attribute8
906 ,mln.d_attribute9
907 ,mln.d_attribute10
908 ,mln.grade_code
909 ,mln.origination_date
910 ,mln.date_code
911 ,mln.change_date
912 ,mln.age
913 ,mln.retest_date
914 ,mln.maturity_date
915 ,mln.item_size
916 ,mln.color
917 ,mln.volume
918 ,mln.volume_uom
919 ,mln.place_of_origin
920 ,mln.best_by_date
921 ,mln.length
922 ,mln.length_uom
923 ,mln.recycled_content
924 ,mln.thickness
925 ,mln.thickness_uom
926 ,mln.width
927 ,mln.width_uom
928 ,mln.territory_code
929 ,mln.supplier_lot_number
930 ,mln.vendor_name
931 ,mln.parent_lot_number
932 ,mln.origination_type
933 ,mln.expiration_action_code
934 ,mln.expiration_action_date
935 ,mln.hold_date
936 ,l_mmtt_row.reason_id
937 FROM
938 mtl_lot_numbers mln
939 WHERE
940 mln.inventory_item_id = l_mmtt_row.inventory_item_id
941 and mln.organization_id = l_mmtt_row.organization_id
942 and mln.lot_number = l_mtlt_row_tbl(i).lot_number;
943
944 END LOOP;
945
946 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
947 log_msg('Inserted '||to_char(l_mtlt_row_tbl.count)||' lot records for the line in mtl_transactions_lots_temp');
948 END IF;
949
950 END IF;--}
951
952 FND_MSG_PUB.Count_AND_GET
953 (p_count => x_msg_count, p_data => x_msg_data);
954
955
956 EXCEPTION
957
958 WHEN FND_API.G_EXC_ERROR THEN
959 ROLLBACK to create_transaction_in_odm;
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 FND_MSG_PUB.Count_AND_GET
962 (p_count => x_msg_count, p_data => x_msg_data);
963
964 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
965 ROLLBACK to create_transaction_in_odm;
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 FND_MSG_PUB.Count_AND_GET
968 (p_count => x_msg_count, p_data => x_msg_data);
969
970 WHEN OTHERS THEN
971 ROLLBACK to create_transaction_in_odm;
972 IF (SQLCODE IS NOT NULL) THEN
973 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
974 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
975 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
976 FND_MSG_PUB.Add;
977 END IF;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979 FND_MSG_PUB.Count_AND_GET
980 (p_count => x_msg_count, p_data => x_msg_data);
981
982 END create_txn_update_bal_in_odm;
983
984
985 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
986 BEGIN
987
988 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
989 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
990 FND_MSG_PUB.Add;
991
992 END log_msg ;
993
994 END GMIVTDX;