1 PACKAGE BODY GMIVDX AS
2 /* $Header: GMIVDXB.pls 120.6 2011/11/04 17:04:42 asatpute ship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | GMIVDXB.pls |
10 | |
11 | PACKAGE NAME |
12 | GMIVDX |
13 | TYPE |
14 | Private |
15 | |
16 | DESCRIPTION |
17 | This package contains the private API for Process / Discrete Transfer |
18 | |
19 | CONTENTS |
20 | Create_discrete_transfer_pvt |
21 | Validate_transfer |
22 | construct_post_records |
23 | |
24 | |
25 | HISTORY |
26 | Created Jalaj Srivastava
27 | Removed the opm_costed_flag update to improve the performance |
28 | as this is being updated to null in caller code. 13335019
29 ============================================================================
30 */
31
32 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
33
34 /* Global variables */
35 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVDX';
36 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
37 -- msg level threshhold gobal
38 -- variable.
39 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
40 -- to decide to log a debug msg.
41
42
43 /* +==========================================================================+
44 | PROCEDURE NAME |
45 | Create_discrete_transfer_pvt |
46 | |
47 | USAGE |
48 | Sets up and posts process/discrete transfer |
49 | |
50 | RETURNS |
51 | Via x_ OUT parameters |
52 | |
53 | HISTORY |
54 | Created Jalaj Srivastava |
55 | |
56 +==========================================================================+ */
57 PROCEDURE Create_transfer_pvt
58 ( p_api_version IN NUMBER
59 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
60 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
61 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
62 , x_return_status OUT NOCOPY VARCHAR2
63 , x_msg_count OUT NOCOPY NUMBER
64 , x_msg_data OUT NOCOPY VARCHAR2
65 , p_hdr_rec IN hdr_type
66 , p_line_rec_tbl IN line_type_tbl
67 , p_lot_rec_tbl IN lot_type_tbl
68 , x_hdr_row OUT NOCOPY gmi_discrete_transfers%ROWTYPE
69 , x_line_row_tbl OUT NOCOPY line_row_tbl
70 , x_lot_row_tbl OUT NOCOPY lot_row_tbl
71 , x_transaction_set_id OUT NOCOPY mtl_material_transactions.transaction_set_id%TYPE
72 )
73 IS
74 l_api_name CONSTANT VARCHAR2(30) := 'Create_transfer_pvt' ;
75 l_api_version CONSTANT NUMBER := 1.0 ;
76 l_hdr_rec GMIVDX.hdr_type;
77 l_line_rec_tbl GMIVDX.line_type_tbl;
78 l_lot_rec_tbl GMIVDX.lot_type_tbl;
79
80 BEGIN
81
82 IF FND_API.to_boolean(p_init_msg_list) THEN
83 FND_MSG_PUB.Initialize;
84 END IF;
85
86 -- Standard call to check for call compatibility.
87 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
88 p_api_version ,
89 l_api_name ,
90 G_PKG_NAME ) THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 x_return_status :=FND_API.G_RET_STS_SUCCESS;
95
96 l_hdr_rec := p_hdr_rec;
97 l_line_rec_tbl := p_line_rec_tbl;
98 l_lot_rec_tbl := p_lot_rec_tbl;
99
100 --Validate the transfer
101 Validate_transfer
102 (
103 p_api_version => p_api_version
104 , p_init_msg_list => FND_API.G_FALSE
105 , p_commit => FND_API.G_FALSE
106 , p_validation_level => p_validation_level
107 , x_return_status => x_return_status
108 , x_msg_count => x_msg_count
109 , x_msg_data => x_msg_data
110 , p_hdr_rec => l_hdr_rec
111 , p_line_rec_tbl => l_line_rec_tbl
112 , p_lot_rec_tbl => l_lot_rec_tbl
113 );
114
115 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
116 log_msg('return code from Validate_transfer. return status is '||x_return_status);
117 END IF;
118
119 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
120 RAISE FND_API.G_EXC_ERROR;
121 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 --if the validation of header, lines and lots is successful then
126 --lets construct and post records to gmi_discrete_transfers, gmi_discrete_transfers |
127 --and gmi_discrete_transfer_lots. |
128 --In this call, we will also create lots in OPM and ODM if the lot does not exist.
129 --Transaction tables in OPM/ODM and onhand balcnces in OPM/ODM would also be updated.
130
131 construct_post_records
132 (
133 p_api_version => p_api_version
134 , p_init_msg_list => FND_API.G_FALSE
135 , p_commit => FND_API.G_FALSE
136 , p_validation_level => p_validation_level
137 , x_return_status => x_return_status
138 , x_msg_count => x_msg_count
139 , x_msg_data => x_msg_data
140 , p_hdr_rec => l_hdr_rec
141 , p_line_rec_tbl => l_line_rec_tbl
142 , p_lot_rec_tbl => l_lot_rec_tbl
143 , x_hdr_row => x_hdr_row
144 , x_line_row_tbl => x_line_row_tbl
145 , x_lot_row_tbl => x_lot_row_tbl
146 );
147
148 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
149 x_transaction_set_id := l_hdr_rec.transaction_header_id;
150 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_TXN_POSTED');
151 FND_MESSAGE.SET_TOKEN('ORGN_CODE' ,x_hdr_row.orgn_code);
152 FND_MESSAGE.SET_TOKEN('TRANSFER_NO',x_hdr_row.transfer_number);
153 FND_MSG_PUB.Add;
154
155 IF FND_API.to_boolean(p_commit) THEN
156 COMMIT WORK;
157 END IF;
158 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
159 RAISE FND_API.G_EXC_ERROR;
160 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 FND_MSG_PUB.Count_AND_GET
165 (p_count => x_msg_count, p_data => x_msg_data);
166
167
168 EXCEPTION
169
170 WHEN FND_API.G_EXC_ERROR THEN
171 --empty the quantity tree cache
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 FND_MSG_PUB.Count_AND_GET
174 (p_count => x_msg_count, p_data => x_msg_data);
175
176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
177 --empty the quantity tree cache
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 FND_MSG_PUB.Count_AND_GET
180 (p_count => x_msg_count, p_data => x_msg_data);
181
182 WHEN OTHERS THEN
183 --empty the quantity tree cache
184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
185 FND_MSG_PUB.Count_AND_GET
186 (p_count => x_msg_count, p_data => x_msg_data);
187
188 END Create_transfer_pvt;
189
190
191 /* +==========================================================================+
192 | PROCEDURE NAME |
193 | Validate_transfer |
194 | |
195 | TYPE |
196 | Private |
197 | |
198 | USAGE |
199 | Validates process/discrete transfer records. |
200 | |
201 | |
202 | RETURNS |
203 | Via x_ OUT parameters |
204 | |
205 | HISTORY |
206 | Created Jalaj Srivastava |
207 | Supriya Malluru 04-Feb-2004 Bug#4114621 |
208 | Included two cursors cur_item_gl_cls,Cur_gl_cls (which accept opm |
209 | item_id as the parameter),to populate/clear gl business class and |
210 | gl product line based on the item.And modified call to |
211 | gmf_get_mappings.get_account_mappings to send category ids of the two |
212 | new item attributes GL Business Class and GL Product Line. |
213 | Archana Mundhe 23-Mar-2009 Bug 8359386 |
214 | Modified code to validate against ic_loct_inv instead of in_loct_mst |
215 | for validated location controlled item and warehouse. |
216
217 +==========================================================================+ */
218 PROCEDURE Validate_transfer
219 ( p_api_version IN NUMBER
220 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
221 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
222 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 , p_hdr_rec IN OUT NOCOPY hdr_type
227 , p_line_rec_tbl IN OUT NOCOPY line_type_tbl
228 , p_lot_rec_tbl IN OUT NOCOPY lot_type_tbl
229 )
230 IS
231 l_api_name CONSTANT VARCHAR2(30) := 'Validate_transfer' ;
232 l_api_version CONSTANT NUMBER := 1.0 ;
233 l_return_val NUMBER;
234 x_ic_item_mst_row ic_item_mst%ROWTYPE;
235 x_ic_whse_mst_row ic_whse_mst%ROWTYPE;
236 x_sy_reas_cds_row sy_reas_cds%ROWTYPE;
237 x_ic_lots_mst_row ic_lots_mst%ROWTYPE;
238 x_ic_loct_inv_row ic_loct_inv%ROWTYPE;
239 l_check_qty NUMBER;
240 l_org INV_Validate.org;
241 l_item INV_Validate.item;
242 l_sub INV_Validate.sub;
243 l_locator INV_Validate.locator;
244 l_odm_lot INV_VALIDATE.lot;
245 l_is_revision_control BOOLEAN;
246 l_is_lot_control BOOLEAN;
247 l_is_serial_control BOOLEAN;
248 l_qoh NUMBER;
249 l_rqoh NUMBER;
250 l_qr NUMBER;
251 l_qs NUMBER;
252 l_att NUMBER;
253 l_atr NUMBER;
254 l_count pls_integer;
255 l_sqoh NUMBER;
256 l_srqoh NUMBER;
257 l_sqr NUMBER;
258 l_sqs NUMBER;
259 l_satt NUMBER;
260 l_satr NUMBER;
261 l_lot_count pls_integer;
262 l_lot_rec_count pls_integer;
263 l_concat_segs VARCHAR2(4000);
264 l_check_flag VARCHAR2(1);
265 l_odm_txn_type_rec inv_validate.transaction;
266 l_opm_item_primary_uom_code VARCHAR2(3);
267 l_opm_item_secondary_uom_code VARCHAR2(3);
268
269 Cursor Cur_get_assigment_type (Vorgn_code VARCHAR2) is
270 SELECT d.assignment_type, o.co_code
271 FROM sy_docs_seq d, sy_orgn_mst o
272 WHERE o.orgn_code = Vorgn_code
273 AND o.delete_mark = 0
274 AND d.orgn_code = o.orgn_code
275 AND d.doc_type = 'DXFR'
276 AND d.delete_mark = 0;
277
278 Cursor Cur_get_opm_fiscal_details (Vwhse_code VARCHAR2) IS
279 SELECT p.co_code, p.sob_id, w.orgn_code, p.base_currency_code
280 FROM gl_plcy_mst p,
281 ic_whse_mst w,
282 sy_orgn_mst o
283 WHERE w.whse_code = Vwhse_code
284 AND o.orgn_code = w.orgn_code
285 AND p.co_code = o.co_code;
286
287 Cursor Cur_get_odm_fiscal_details (Vorganization_id NUMBER) IS
288 SELECT o.organization_code, o.set_of_books_id sob_id
289 FROM org_organization_definitions o,
290 hr_all_organization_units h
291 WHERE o.organization_id = Vorganization_id
292 AND h.organization_id = o.organization_id
293 AND sysdate between nvl(h.date_from, sysdate) and nvl(h.date_to,sysdate);
294
295
296 Cursor Cur_transfer_no_exists (Vtransfer_number VARCHAR2, Vorgn_code VARCHAR2) IS
297 SELECT count(1)
298 FROM gmi_discrete_transfers
299 WHERE transfer_number = Vtransfer_number
300 AND orgn_code = Vorgn_code;
301
302 /* Jalaj Srivastava Bug 3812701 */
303 Cursor Cur_get_uom_code (Vum_code VARCHAR2) IS
304 SELECT uom_code
305 FROM sy_uoms_mst
306 WHERE um_code = Vum_code;
307
308 l_get_opm_fiscal_details_row Cur_get_opm_fiscal_details%ROWTYPE;
309 l_get_odm_fiscal_details_row Cur_get_odm_fiscal_details%ROWTYPE;
310
311 CURSOR cur_item_gl_cls (p_item_id NUMBER) IS
312 SELECT iim.gl_class
313 FROM ic_item_mst_b iim, ic_gled_cls igc
314 WHERE iim.item_id = p_item_id
315 AND iim.gl_class = igc.icgl_class;
316
317 CURSOR Cur_gl_cls (p_item ic_item_mst.item_id%TYPE) IS
318 SELECT gic.item_id, gcs.opm_class, gic.category_id, kfv.CONCATENATED_SEGMENTS,
319 mcv.description
320 FROM mtl_categories_vl mcv, mtl_categories_b_kfv kfv, gmi_category_sets gcs, gmi_item_categories gic
321 WHERE gcs.category_set_id IS NOT NULL
322 AND gic.item_id = p_item
323 AND gcs.opm_class IN ('GL_BUSINESS_CLASS', 'GL_PRODUCT_LINE')
324 AND gcs.category_set_id = gic.category_set_id
325 AND kfv.category_id = gic.category_id
326 AND mcv.category_id = gic.category_id
327 ORDER BY gic.item_id, gcs.opm_class ;
328
329 v_business_class_found BOOLEAN := FALSE;
330 v_product_line_found BOOLEAN := FALSE;
331 gl_business_class_cat_id gmi_item_categories.category_id%TYPE := NULL;
332 gl_product_line_cat_id gmi_item_categories.category_id%TYPE := NULL;
333 item_gl_class ic_item_mst.gl_class%TYPE;
334 --End Supriya Malluru Bug#4114621
335
336 BEGIN
337
338 IF FND_API.to_boolean(p_init_msg_list) THEN
339 FND_MSG_PUB.Initialize;
340 END IF;
341
342 SAVEPOINT validate_transfer;
343
344 -- Standard call to check for call compatibility.
345 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
346 p_api_version ,
347 l_api_name ,
348 G_PKG_NAME ) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351
352 x_return_status :=FND_API.G_RET_STS_SUCCESS;
353
354 --this call will set up the profiles needed by the create lot engine
355 --and transaction engine in OPM.
356 IF (NOT GMIGUTL.SETUP(FND_GLOBAL.USER_NAME)) THEN
357 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
358 log_msg('Failed call to GMIGUTL.SETUP');
359 END IF;
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END IF;
362
363 INV_TRANS_DATE_OPTION := FND_PROFILE.Value('TRANSACTION_DATE');
364 IF (INV_TRANS_DATE_OPTION IS NULL) THEN
365 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
366 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','TRANSACTION_DATE');
367 FND_MSG_PUB.Add;
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371 --check for the WMS installation in FND_PRODUCT_INSTALLATION
372 --this is used for checking material status for subinventory and locators.
373 IF (inv_install.adv_inv_installed(NULL)) THEN
374 WMS_INSTALLED := 'TRUE';
375 ELSE
376 WMS_INSTALLED := 'FALSE';
377 END IF;
378
379 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
380 log_msg('Begin validation of header record');
381 END IF;
382
383 -- Validate the orgn_code.
384 IF NOT GMA_VALID_GRP.Validate_orgn_code(p_hdr_rec.orgn_code) THEN
385 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ORGN_CODE');
386 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_hdr_rec.orgn_code);
387 FND_MSG_PUB.Add;
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390
391 OPEN Cur_get_assigment_type(p_hdr_rec.orgn_code);
392 FETCH Cur_get_assigment_type INTO p_hdr_rec.assignment_type, p_hdr_rec.co_code;
393 IF (Cur_get_assigment_type%NOTFOUND) THEN
394 CLOSE Cur_get_assigment_type;
395 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_NO_DCMNT_NMBRNG');
396 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_hdr_rec.orgn_code);
397 FND_MSG_PUB.Add;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400 CLOSE Cur_get_assigment_type;
401
402 --For manual doc numbering, transfer no needs to be specified.
403 IF (p_hdr_rec.assignment_type = 1) THEN
404 IF (p_hdr_rec.transfer_number IS NULL) THEN
405 FND_MESSAGE.SET_NAME ('GMI', 'GMI_DXFR_NULL_TRANSFER_NO');
406 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_hdr_rec.orgn_code);
407 FND_MSG_PUB.Add;
408 RAISE FND_API.G_EXC_ERROR;
409 ELSIF (p_hdr_rec.transfer_number IS NOT NULL) THEN
410 --check if the transfer no doesnt exist already
411 OPEN Cur_transfer_no_exists(p_hdr_rec.transfer_number,p_hdr_rec.orgn_code) ;
412 FETCH Cur_transfer_no_exists INTO l_count;
413 CLOSE Cur_transfer_no_exists;
414 IF (l_count > 0) THEN
415 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
416 log_msg('Manual document numbering. This transfer no is in use already');
417 END IF;
418 FND_MESSAGE.SET_NAME ('GMI', 'GMI_DXFR_TRANSFER_NO_INVALID');
419 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_hdr_rec.orgn_code);
420 FND_MESSAGE.SET_TOKEN('TRANSFER_NO',p_hdr_rec.transfer_number);
421 FND_MSG_PUB.Add;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424 END IF;
425 END IF;
426
427 --validate the transfer type
428 IF (p_hdr_rec.transfer_type NOT IN (0,1)) THEN
429 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_INVALID_TRANSFER_TYPE');
430 FND_MESSAGE.SET_TOKEN('TRANSFER_TYPE',p_hdr_rec.transfer_type);
431 FND_MSG_PUB.Add;
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434
435 --Validate the transaction type in ODM.
436 --If transfer is Process to Discrete then the transaction type for ODM is MISC Receipt (42)
437 --If transfer is Discrete to Process then the transaction type for ODM is MISC Issue (32)
438 IF (p_hdr_rec.transfer_type = 0 ) THEN
439 l_odm_txn_type_rec.transaction_type_id := 42;
440 ELSIF (p_hdr_rec.transfer_type = 1 ) THEN
441 l_odm_txn_type_rec.transaction_type_id := 32;
442 END IF;
443
444 IF (inv_validate.transaction_type (l_odm_txn_type_rec) = inv_validate.F) THEN
445 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
446 log_msg('Failed call to inv_validate.transaction_type.');
447 END IF;
448 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ATTRIBUTE');
449 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('INV','TRANSACTION'),FALSE);
450 FND_MSG_PUB.Add;
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453
454 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
455 log_msg('End validation of header record');
456 END IF;
457
458
459 /* ****start of validation for line level ***** */
460
461 /* All transaction types need an item. Make sure we have */
462 /* one which can be used */
463 IF (p_line_rec_tbl.count = 0) THEN
464 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_NO_LINES');
465 FND_MSG_PUB.Add;
466 RAISE FND_API.G_EXC_ERROR;
467 END IF;
468
469 FOR i in 1..p_line_rec_tbl.count LOOP --{
470 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
471 log_msg('Begin validation of Line record '||to_char(p_line_rec_tbl(i).line_no));
472 END IF;
473
474 BEGIN
475 --line no should be unique for this transfer.
476 FOR z in 1..p_line_rec_tbl.count LOOP --{
477 IF (z <> i) AND (p_line_rec_tbl(z).line_no = p_line_rec_tbl(i).line_no) THEN
478 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SAME_LINE_NO');
479 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
480 FND_MSG_PUB.Add;
481 RAISE FND_API.G_EXC_ERROR;
482
483 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
484 log_msg('Line number '||to_char(p_line_rec_tbl(i).line_no)||' present in more than one line');
485 END IF;
486
487 END IF;
488 END LOOP;--}
489
490 --check the odm inventory organization first for validity as we need it for
491 --validating item in ODM.
492 l_org.organization_id := p_line_rec_tbl(i).odm_inv_organization_id;
493
494 IF (INV_Validate.Organization(l_org) = inv_validate.F) THEN
495 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
496 log_msg('failed call to INV_Validate.Organization');
497 END IF;
498 FND_MESSAGE.SET_NAME('INV','INV_INT_ORGCODE');
499 FND_MSG_PUB.Add;
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502
503 /* All lines need an item. Make sure we have one which can be used */
504 x_ic_item_mst_row.item_no := NULL;
505 x_ic_item_mst_row.item_id := p_line_rec_tbl(i).opm_item_id;
506 IF ( GMIVDBL.ic_item_mst_select(x_ic_item_mst_row, x_ic_item_mst_row) ) THEN
507 IF (x_ic_item_mst_row.noninv_ind = 1) THEN
508 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
509 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
510 FND_MSG_PUB.Add;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513 ELSE
514 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_OPM_ITEM_ID_NOT_FOUND');
515 FND_MESSAGE.SET_TOKEN('ITEM_ID',p_line_rec_tbl(i).opm_item_id);
516 FND_MSG_PUB.Add;
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519 p_line_rec_tbl(i).opm_item_no := x_ic_item_mst_row.item_no;
520
521 IF (x_ic_item_mst_row.lot_ctl = 0) THEN
522 p_line_rec_tbl(i).lot_control := 0;
523 ELSIF (x_ic_item_mst_row.lot_ctl = 1) THEN
524 p_line_rec_tbl(i).lot_control := 1;
525 END IF;
526
527 --Validate the item in ODM
528 l_item.inventory_item_id := p_line_rec_tbl(i).odm_item_id;
529 IF (inv_validate.Inventory_Item (l_item, l_org) = inv_validate.F) THEN
530 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
531 log_msg('failed call to inv_validate.Inventory_Item');
532 END IF;
533 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_ITEM');
534 FND_MSG_PUB.Add;
535 RAISE FND_API.G_EXC_ERROR;
536 END IF;
537
538 IF ( (nvl(l_item.INVENTORY_ITEM_FLAG,'N') ='N')
539 OR (nvl(l_item.MTL_TRANSACTIONS_ENABLED_FLAG,'N') = 'N')
540 OR (nvl(l_item.SERIAL_NUMBER_CONTROL_CODE,1) <> 1)
541 ) THEN
542 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_ITEM');
543 FND_MSG_PUB.Add;
544 RAISE FND_API.G_EXC_ERROR;
545 END IF;
546
547 IF (l_item.revision_qty_control_code = 1) THEN
548 p_line_rec_tbl(i).odm_item_revision := NULL;
549 ELSIF (l_item.revision_qty_control_code = 2) THEN
550 --Validate the item revision in ODM
551 IF (p_line_rec_tbl(i).odm_item_revision IS NULL) THEN
552 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
553 log_msg('For revision controlled item revision is null');
554 END IF;
555 FND_MESSAGE.SET_NAME('INV', 'INV_INT_REVCODE');
556 FND_MSG_PUB.Add;
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559
560 IF (inv_validate.revision (p_line_rec_tbl(i).odm_item_revision, l_org, l_item) = inv_validate.F) THEN
561 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
562 log_msg('failed call to inv_validate.revision');
563 END IF;
564 FND_MESSAGE.SET_NAME('INV', 'INV_INT_REVCODE');
565 FND_MSG_PUB.Add;
566 RAISE FND_API.G_EXC_ERROR;
567 END IF;
568
569 SELECT count(1)
570 INTO l_count
571 FROM mtl_item_revisions
572 WHERE inventory_item_id = l_item.inventory_item_id
573 AND organization_id = l_org.organization_id
574 AND revision = p_line_rec_tbl(i).odm_item_revision
575 AND implementation_date IS NOT NULL;
576
577 IF (l_count = 0) THEN
578 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
579 log_msg('For revision controlled item revision implementation date is null');
580 END IF;
581 FND_MESSAGE.SET_NAME('INV', 'INV_INT_REVCODE');
582 FND_MSG_PUB.Add;
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586 END IF;
587
588 --We should be dealing with the same item in OPM/ODM
589 IF (l_item.segment1 <> x_ic_item_mst_row.item_no) THEN
590 FND_MESSAGE.SET_NAME ('GMI','GMI_DXFR_DIFF_ITEM');
591 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
592 FND_MSG_PUB.Add;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595
596 /* **************************************************************
597 Item should be either lot controlled both in opm/discrete or not
598 lot controlled both in opm/discrete.
599 ************************************************************** */
600 IF (l_item.lot_control_code <> x_ic_item_mst_row.lot_ctl + 1) THEN
601 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_DIFF_LOT_CONTROL');
602 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
603 FND_MSG_PUB.Add;
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 --Get uom_code for OPM item's primary UOM
608 OPEN Cur_get_uom_code(x_ic_item_mst_row.item_um);
609 FETCH Cur_get_uom_code INTO l_opm_item_primary_uom_code;
610 CLOSE Cur_get_uom_code;
611
612 IF (l_item.primary_uom_code <> l_opm_item_primary_uom_code) THEN
613 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_DIFF_PRIM_UOM');
614 FND_MSG_PUB.Add;
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617
618 --Get uom_code for OPM item's secondary UOM
619 IF (x_ic_item_mst_row.item_um2 IS NOT NULL) THEN
620 OPEN Cur_get_uom_code(x_ic_item_mst_row.item_um2);
621 FETCH Cur_get_uom_code INTO l_opm_item_secondary_uom_code;
622 CLOSE Cur_get_uom_code;
623 END IF;
624
625 IF ( nvl(l_item.secondary_uom_code,' ') <> nvl(l_opm_item_secondary_uom_code,' ') ) THEN
626 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_DIFF_SEC_UOM');
627 FND_MSG_PUB.Add;
628 RAISE FND_API.G_EXC_ERROR;
629 END IF;
630
631 --lets see if the OPM warehouse is valid
632 x_ic_whse_mst_row.whse_code := p_line_rec_tbl(i).opm_whse_code;
633 IF ( GMIVDBL.ic_whse_mst_select(x_ic_whse_mst_row, x_ic_whse_mst_row) ) THEN
634 NULL;
635 ELSE
636 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_WHSE_CODE_NOT_FOUND');
637 FND_MESSAGE.SET_TOKEN('WHSE_CODE',p_line_rec_tbl(i).opm_whse_code);
638 FND_MSG_PUB.Add;
639 RAISE FND_API.G_EXC_ERROR;
640 END IF;
641
642 --lets validate the ODM subinventory
643 --when transfer is process to discrete then the FROM subinventory is the default
644 --subinventory for the OPM warehouse which is a Inventory Org.
645 --in this case the subinventory name is same as the whse code in OPM.
646 --when transfer is discrete to process then the TO subinventory is the default
647 --subinventory for the OPM warehouse which is a Inventory Org.
648 --in this case the subinventory name is same as the whse code in OPM
649
650 --we would not validate the subinventory for the process org.
651 --We will only validate for ODM.
652
653
654 --subinventory would be validated differently depending on whether
655 --item is restricted subinventory controlled, profile INV:EXPENSE_TO_ASSET_TRANSFER.
656 --and transaction type.
657 --p_acct_txn is 1 when transaction_action_id is 1 (Misc Issue)
658 --0 when any other transaction_action_id (27 for Misc receipt)
659
660 l_sub.secondary_inventory_name := p_line_rec_tbl(i).odm_subinventory;
661 IF (INV_VALIDATE.From_Subinventory
662 ( p_sub => l_sub
663 ,p_org => l_org
664 ,p_item => l_item
665 ,p_acct_txn => p_hdr_rec.transfer_type
666 ) = inv_validate.F
667 ) THEN
668 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
669 log_msg('failed call to INV_Validate.from_subinventory');
670 END IF;
671 FND_MESSAGE.SET_NAME('INV','INV_INVALID_SUBINV');
672 FND_MSG_PUB.Add;
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675
676 --need to call overloaded INV_Validate.validatelocator depending on whether the
677 --item is restricted locator controlled
678 l_locator.inventory_location_id := p_line_rec_tbl(i).odm_locator_id;
679 --{
680 IF ( (l_org.stock_locator_control_code = 1)
681 OR ( (l_org.stock_locator_control_code = 4)
682 AND (l_sub.locator_type = 1)
683 )
684 OR ( (l_org.stock_locator_control_code = 4)
685 AND (l_sub.locator_type = 5)
686 AND (l_item.location_control_code = 1)
687 )
688 ) THEN
689 IF (p_line_rec_tbl(i).odm_locator_id IS NOT NULL) THEN
690 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
691 log_msg('ODM locator id is not required as org/sub/item combination is non location controlled');
692 END IF;
693 FND_MESSAGE.SET_NAME('INV','INV_INT_LOCCODE');
694 FND_MSG_PUB.ADD;
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697 ELSE
698 IF (p_line_rec_tbl(i).odm_locator_id IS NULL) THEN
699 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
700 log_msg('ODM locator id is required as org/sub/item combination is location controlled');
701 END IF;
702 FND_MESSAGE.SET_NAME('INV','INV_INT_LOCCODE');
703 FND_MSG_PUB.ADD;
704 RAISE FND_API.G_EXC_ERROR;
705 END IF;
706
707
708 END IF;--}
709
710
711 --Get opm warehouse company and sob
712 OPEN Cur_get_opm_fiscal_details(p_line_rec_tbl(i).opm_whse_code);
713 FETCH Cur_get_opm_fiscal_details INTO l_get_opm_fiscal_details_row;
714 IF (Cur_get_opm_fiscal_details%NOTFOUND) THEN
715 CLOSE Cur_get_opm_fiscal_details;
716 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_OPM_NO_FISCAL_POLICY');
717 FND_MESSAGE.SET_TOKEN('WHSE_CODE',p_line_rec_tbl(i).opm_whse_code);
718 FND_MSG_PUB.Add;
719 RAISE FND_API.G_EXC_ERROR;
720 END IF;
721 CLOSE Cur_get_opm_fiscal_details;
722
723 --Get ODM sob
724 OPEN Cur_get_odm_fiscal_details(p_line_rec_tbl(i).odm_inv_organization_id);
725 FETCH Cur_get_odm_fiscal_details INTO l_get_odm_fiscal_details_row;
726 IF (Cur_get_odm_fiscal_details%NOTFOUND) THEN
727 CLOSE Cur_get_odm_fiscal_details;
728 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_ODM_NO_FISCAL_POLICY');
729 FND_MSG_PUB.Add;
730 RAISE FND_API.G_EXC_ERROR;
731 END IF;
732 CLOSE Cur_get_odm_fiscal_details;
733
734 --sets of books should be same for inventory organization and opm warehouse
735 IF (l_get_opm_fiscal_details_row.sob_id <> l_get_odm_fiscal_details_row.sob_id) THEN
736 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_DIFF_SOB');
737 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
738 FND_MSG_PUB.Add;
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741
742 --Lets validate the transaction date in OPM
743 l_return_val := GMICCAL.trans_date_validate
744 ( p_hdr_rec.trans_date
745 , p_hdr_rec.orgn_code
746 , p_line_rec_tbl(i).opm_whse_code
747 );
748 IF (l_return_val <> 0) THEN
749 FND_MESSAGE.SET_NAME('GMI','IC_API_TXN_POST_CLOSED');
750 FND_MESSAGE.SET_TOKEN('WAREH', p_line_rec_tbl(i).opm_whse_code);
751 FND_MESSAGE.SET_TOKEN('DATE', p_hdr_rec.trans_date);
752 FND_MSG_PUB.Add;
753 RAISE FND_API.G_EXC_ERROR;
754 ELSIF (p_hdr_rec.trans_date > SYSDATE) THEN
755 FND_MESSAGE.SET_NAME('GMI','IC_API_CANNOT_POST_FUTURE');
756 FND_MESSAGE.SET_TOKEN('ITEM_NO' , x_ic_item_mst_row.item_no);
757 FND_MESSAGE.SET_TOKEN('TRANS_DATE', p_hdr_rec.trans_date);
758 FND_MSG_PUB.Add;
759 RAISE FND_API.G_EXC_ERROR;
760 END IF;
761
762 --lets us validate the transaction date for discrete
763 -- Check if past date is allowed...
764 IF ((INV_TRANS_DATE_OPTION = 3) OR (INV_TRANS_DATE_OPTION = 4)) THEN
765 INV_OPEN_PAST_PERIOD := TRUE;
766 ELSE
767 INV_OPEN_PAST_PERIOD := FALSE;
768 END IF;
769 -- Validate that the inventory period is open
770 invttmtx.tdatechk (p_line_rec_tbl(i).odm_inv_organization_id,
771 trunc(p_hdr_rec.trans_date),
772 p_line_rec_tbl(i).odm_period_id,
773 INV_OPEN_PAST_PERIOD
774 );
775
776 IF (p_line_rec_tbl(i).odm_period_id = 0) THEN
777 FND_MESSAGE.SET_NAME('INV','INV_NO_OPEN_PERIOD');
778 FND_MSG_PUB.Add;
779 RAISE FND_API.G_EXC_ERROR;
780 ELSIF (p_line_rec_tbl(i).odm_period_id = -1) THEN
781 FND_MESSAGE.SET_NAME('INV', 'INV_RETRIEVE_PERIOD');
782 FND_MSG_PUB.Add;
783 RAISE FND_API.G_EXC_ERROR;
784 END IF;
785
786 IF (INV_TRANS_DATE_OPTION = 3) THEN
787 IF ( NOT INV_OPEN_PAST_PERIOD) THEN
788 FND_MESSAGE.SET_NAME('INV','INV_NO_PAST_PERIOD');
789 FND_MESSAGE.SET_TOKEN('ENTITY',p_hdr_rec.trans_date,TRUE);
790 FND_MSG_PUB.Add;
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793 ELSIF (INV_TRANS_DATE_OPTION = 4) THEN
794 IF ( NOT INV_OPEN_PAST_PERIOD) THEN
795 FND_MESSAGE.SET_NAME('INV','INV_NO_PAST_PERIOD');
796 FND_MESSAGE.SET_TOKEN('ENTITY',p_hdr_rec.trans_date,TRUE);
797 FND_MSG_PUB.Add;
798 END IF;
799 END IF;
800
801 --validate the reason code for the transfer
802 x_sy_reas_cds_row.reason_code := p_line_rec_tbl(i).opm_reason_code;
803 IF GMIVDBL.sy_reas_cds_select(x_sy_reas_cds_row, x_sy_reas_cds_row) THEN
804 IF (x_sy_reas_cds_row.reason_type = 1 AND p_hdr_rec.transfer_type = 0) THEN
805 FND_MESSAGE.SET_NAME('GMI','IC_REASONTYPEINCREASE');
806 FND_MSG_PUB.Add;
807 RAISE FND_API.G_EXC_ERROR;
808 ELSIF (x_sy_reas_cds_row.reason_type = 2 AND p_hdr_rec.transfer_type = 1) THEN
809 FND_MESSAGE.SET_NAME('GMI','IC_REASONTYPEDECREASE');
810 FND_MSG_PUB.Add;
811 RAISE FND_API.G_EXC_ERROR;
812 END IF;
813 ELSE
814 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_REASON_CODE');
815 FND_MESSAGE.SET_TOKEN('REASON_CODE',p_line_rec_tbl(i).opm_reason_code);
816 FND_MSG_PUB.Add;
817 RAISE FND_API.G_EXC_ERROR;
818 END IF;
819
820 --Validate the discrete reason id
821 --Entering the discrete reason id is optional.
822 IF (p_line_rec_tbl(i).odm_reason_id IS NOT NULL) THEN
823 IF (INV_Validate.Reason(p_line_rec_tbl(i).odm_reason_id) = inv_validate.F) THEN
824 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
825 log_msg('failed call to INV_Validate.Reason');
826 END IF;
827 FND_MESSAGE.SET_NAME('INV','INV_INT_REACODE');
828 FND_MSG_PUB.Add;
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 END IF;
832
833 --For transfers the quantity should be positive
834 IF (p_line_rec_tbl(i).quantity IS NULL) THEN
835 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_NULL_QTY');
836 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
837 FND_MSG_PUB.Add;
838 RAISE FND_API.G_EXC_ERROR;
839 ELSIF (p_line_rec_tbl(i).quantity < 0) THEN
840 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_QTY_NOT_NEG');
841 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
842 FND_MSG_PUB.Add;
843 RAISE FND_API.G_EXC_ERROR;
844 ELSIF (p_line_rec_tbl(i).quantity = 0) THEN
845 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_ZERO_QTY');
846 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
847 FND_MSG_PUB.Add;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850
851 --lets validate the UOM .
852 --UOM should be same for lines and associated lot records
853 /* Jalaj Srivastava Bug 3812701 */
854 IF ( (p_line_rec_tbl(i).quantity_um <> x_ic_item_mst_row.item_um)
855 AND (NOT GMA_VALID_GRP.Validate_um(p_line_rec_tbl(i).quantity_um))
856 ) THEN
857
858 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
859 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
860 FND_MESSAGE.SET_TOKEN('UOM',p_line_rec_tbl(i).quantity_um);
861 FND_MSG_PUB.Add;
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 /* Jalaj Srivastava Bug 3812701 */
866 /* Get uom_code for this um from sy_uoms_mst */
867
868 OPEN Cur_get_uom_code(p_line_rec_tbl(i).quantity_um);
869 FETCH Cur_get_uom_code INTO p_line_rec_tbl(i).odm_quantity_uom_code;
870 CLOSE Cur_get_uom_code;
871
872 /* Jalaj Srivastava Bug 3812701 */
873 /* Validate odm_quantity_uom_code in discrete */
874 IF (inv_validate.uom(p_line_rec_tbl(i).odm_quantity_uom_code,l_org,l_item) = inv_validate.F) THEN
875 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
876 log_msg('failed call to inv_validate.uom');
877 END IF;
878 FND_MESSAGE.SET_NAME('INV','INV-NO ITEM UOM');
879 FND_MSG_PUB.Add;
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882
883 --Lets Validate the locations in OPM
884 IF ( (x_ic_whse_mst_row.loct_ctl = 0)
885 OR (x_ic_item_mst_row.loct_ctl = 0)
886 ) THEN
887 IF (nvl(p_line_rec_tbl(i).opm_location,GMIGUTL.IC$DEFAULT_LOCT) <> GMIGUTL.IC$DEFAULT_LOCT) THEN
888 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
889 log_msg('Failed while validating OPM location. Item and/or warehouse are not location controlled');
890 END IF;
891 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCATION');
892 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
893 FND_MESSAGE.SET_TOKEN('LOCATION',p_line_rec_tbl(i).opm_location);
894 FND_MESSAGE.SET_TOKEN('WHSE_CODE',p_line_rec_tbl(i).opm_whse_code);
895 FND_MSG_PUB.Add;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898 p_line_rec_tbl(i).opm_location := GMIGUTL.IC$DEFAULT_LOCT;
899 ELSIF ( (x_ic_whse_mst_row.loct_ctl = 1)
900 AND (x_ic_item_mst_row.loct_ctl = 1)
901 ) THEN
902 SELECT count(1) INTO l_count
903 FROM ic_loct_inv -- Bug 8359386
904 WHERE whse_code = p_line_rec_tbl(i).opm_whse_code
905 AND location = p_line_rec_tbl(i).opm_location
906 AND location <> GMIGUTL.IC$DEFAULT_LOCT;
907
908 IF (l_count = 0) THEN
909 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
910 log_msg('Failed while validating OPM location. Item and warehouse are validated location controlled');
911 END IF;
912 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCATION');
913 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
914 FND_MESSAGE.SET_TOKEN('LOCATION',p_line_rec_tbl(i).opm_location);
915 FND_MESSAGE.SET_TOKEN('WHSE_CODE',p_line_rec_tbl(i).opm_whse_code);
916 FND_MSG_PUB.Add;
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919 ELSIF ( (x_ic_whse_mst_row.loct_ctl = 2)
920 OR (x_ic_item_mst_row.loct_ctl = 2)
921 ) THEN
922 SELECT count(1) INTO l_count
923 FROM ic_loct_inv
924 WHERE whse_code = p_line_rec_tbl(i).opm_whse_code
925 AND location = p_line_rec_tbl(i).opm_location
926 AND location <> GMIGUTL.IC$DEFAULT_LOCT;
927
928 --we could have non validated locations in OPM when transfer is from discrete to process.
929 IF (l_count = 0) AND (p_hdr_rec.transfer_type = 0) THEN
930 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
931 log_msg('Failed while validating OPM location. Item and/or warehouse are non validated location controlled');
932 END IF;
933 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOCATION');
934 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
935 FND_MESSAGE.SET_TOKEN('LOCATION',p_line_rec_tbl(i).opm_location);
936 FND_MESSAGE.SET_TOKEN('WHSE_CODE',p_line_rec_tbl(i).opm_whse_code);
937 FND_MSG_PUB.Add;
938 RAISE FND_API.G_EXC_ERROR;
939 END IF;
940
941 END IF;
942
943 --lets validate the unit cost if entered by the user.
944 IF (p_line_rec_tbl(i).odm_unit_cost IS NOT NULL) THEN
945 IF ( (l_org.primary_cost_method NOT IN ('2', '5', '6') )
946 OR (l_item.inventory_asset_flag <> 'Y')
947 OR (l_sub.asset_inventory <> 1)
948 ) THEN
949 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_COST_SHOULD_BE_NULL');
950 FND_MSG_PUB.Add;
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953 END IF;
954
955 --Quantity at line level should be the same as the sum of quantities
956 --at the lot levels.
957 l_check_qty := 0;
958 l_lot_count := 0;
959 FOR k in 1..p_lot_rec_tbl.count LOOP
960 IF (p_lot_rec_tbl(k).line_no = p_line_rec_tbl(i).line_no) THEN
961 --If lot is specified at line level then it cannot be specified at lot level.
962 --for non lot controlled items default lot could be specified only at the line level.
963 IF (x_ic_item_mst_row.lot_ctl = 0) THEN
964 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_LOT_RECORD_NOT_NEEDED');
965 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
966 FND_MSG_PUB.Add;
967 RAISE FND_API.G_EXC_ERROR;
968 END IF;
969
970 IF ( (p_line_rec_tbl(i).opm_lot_id IS NOT NULL)
971 OR (p_line_rec_tbl(i).odm_lot_number IS NOT NULL)
972 ) THEN
973 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_INVALID_LOT_RECORDS');
974 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
975 FND_MSG_PUB.Add;
976 RAISE FND_API.G_EXC_ERROR;
977 END IF;
978
979 l_check_qty := l_check_qty + p_lot_rec_tbl(k).quantity;
980 l_lot_count := l_lot_count + 1;
981
982 END IF;
983
984 END LOOP;
985 IF (l_lot_count > 0) THEN
986 p_line_rec_tbl(i).lot_level := 1;
987 IF (p_line_rec_tbl(i).quantity <> l_check_qty) THEN
988 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_LINE_LOT_QTY_DIFF');
989 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
990 FND_MSG_PUB.Add;
991 RAISE FND_API.G_EXC_ERROR;
992 END IF;
993 ELSIF (l_lot_count = 0) THEN
994 p_line_rec_tbl(i).lot_level := 0;
995 -- we will also assign a default lot_type record so that validations for lot can take place
996 l_lot_rec_count := p_lot_rec_tbl.count + 1;
997 p_lot_rec_tbl(l_lot_rec_count).line_no := p_line_rec_tbl(i).line_no;
998 p_lot_rec_tbl(l_lot_rec_count).opm_lot_id := p_line_rec_tbl(i).opm_lot_id;
999 p_lot_rec_tbl(l_lot_rec_count).odm_lot_number := p_line_rec_tbl(i).odm_lot_number;
1000 p_lot_rec_tbl(l_lot_rec_count).quantity := p_line_rec_tbl(i).quantity;
1001 p_lot_rec_tbl(l_lot_rec_count).quantity2 := p_line_rec_tbl(i).quantity2;
1002 END IF;
1003
1004 IF (x_ic_item_mst_row.dualum_ind > 0) THEN
1005 p_line_rec_tbl(i).quantity2 := 0;
1006 ELSIF (x_ic_item_mst_row.dualum_ind = 0) THEN
1007 p_line_rec_tbl(i).quantity2 := NULL;
1008 END IF;
1009
1010 --lets start validating the lots and the quantities
1011 --{
1012 --We need to capture this as ODM may change the transaction qty at lot level
1013 --to conform to rules defined in MTL.
1014 FOR j in 1..p_lot_rec_tbl.count LOOP
1015 BEGIN
1016 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1017 log_msg('Begin validation of lot. lot record no '||to_char(j));
1018 END IF;
1019 IF (p_lot_rec_tbl(j).line_no = p_line_rec_tbl(i).line_no) THEN --{
1020
1021 IF (p_lot_rec_tbl(j).quantity < 0) THEN
1022 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_LOT_QTY_NOT_NEG');
1023 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1024 FND_MESSAGE.SET_TOKEN('LOT_ID',p_lot_rec_tbl(j).opm_lot_id);
1025 FND_MSG_PUB.Add;
1026 RAISE FND_API.G_EXC_ERROR;
1027 ELSIF (p_lot_rec_tbl(j).quantity = 0) THEN
1028 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_LOT_ZERO_QTY');
1029 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1030 FND_MESSAGE.SET_TOKEN('LOT_ID',p_lot_rec_tbl(j).opm_lot_id);
1031 FND_MSG_PUB.Add;
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034
1035 --transfer is from process to discrete
1036 --{
1037 IF (p_hdr_rec.transfer_type = 0) THEN
1038 IF (x_ic_item_mst_row.lot_ctl = 0) THEN
1039 IF (p_lot_rec_tbl(j).opm_lot_id <> 0) THEN
1040 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_OPM_LOT_IS_NOT_DEFAULT');
1041 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1042 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1043 FND_MSG_PUB.Add;
1044 RAISE FND_API.G_EXC_ERROR;
1045
1046 END IF;
1047
1048 ELSIF (x_ic_item_mst_row.lot_ctl = 1) THEN
1049 IF (p_lot_rec_tbl(j).opm_lot_id = 0) THEN
1050 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_OPM_LOT_IS_DEFAULT');
1051 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1052 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1053 FND_MSG_PUB.Add;
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1057 log_msg('Start validating OPM lot when transfer type is 0');
1058 END IF;
1059
1060 BEGIN
1061 SELECT * INTO x_ic_lots_mst_row
1062 FROM ic_lots_mst
1063 WHERE item_id = p_line_rec_tbl(i).opm_item_id
1064 AND lot_id = p_lot_rec_tbl(j).opm_lot_id;
1065
1066 EXCEPTION
1067 WHEN NO_DATA_FOUND THEN
1068 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_LOT_NOT_FOUND');
1069 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1070 FND_MESSAGE.SET_TOKEN('LOT_ID',p_lot_rec_tbl(j).opm_lot_id);
1071 FND_MSG_PUB.Add;
1072 RAISE FND_API.G_EXC_ERROR;
1073
1074 END;
1075
1076 END IF;
1077
1078
1079 p_lot_rec_tbl(j).opm_lot_no := x_ic_lots_mst_row.lot_no;
1080 p_lot_rec_tbl(j).opm_sublot_no := x_ic_lots_mst_row.sublot_no;
1081 p_lot_rec_tbl(j).opm_lot_expiration_date := x_ic_lots_mst_row.expire_date;
1082 p_lot_rec_tbl(j).opm_grade := x_ic_lots_mst_row.qc_grade;
1083
1084 --now lets check whether there is inventory to transfer from OPM to ODM
1085 x_ic_loct_inv_row.whse_code := p_line_rec_tbl(i).opm_whse_code;
1086 x_ic_loct_inv_row.location := p_line_rec_tbl(i).opm_location;
1087 x_ic_loct_inv_row.item_id := p_line_rec_tbl(i).opm_item_id;
1088 x_ic_loct_inv_row.lot_id := p_lot_rec_tbl(j).opm_lot_id;
1089
1090
1091
1092 --{
1093 IF GMIVDBL.ic_loct_inv_select(x_ic_loct_inv_row, x_ic_loct_inv_row) THEN
1094 --store quantities in opm item UOM and ODM item UOM
1095
1096 p_lot_rec_tbl(j).opm_lot_status := x_ic_loct_inv_row.lot_status;
1097 l_return_val := GMICUOM.uom_conversion
1098 (
1099 x_ic_item_mst_row.item_id,
1100 nvl(x_ic_lots_mst_row.lot_id,0),
1101 p_lot_rec_tbl(j).quantity,
1102 p_line_rec_tbl(i).quantity_um,
1103 x_ic_item_mst_row.item_um,
1104 0
1105 );
1106 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1107 log_msg('After calling GMICUOM.uom_conversion to get opm_primary_quantity when transfer type is 0. return val is '||l_return_val);
1108 END IF;
1109 IF(l_return_val >= 0) THEN
1110 p_lot_rec_tbl(j).opm_primary_quantity := l_return_val;
1111 END IF;
1112
1113 IF (l_return_val < 0) THEN
1114 IF (l_return_val = -1) THEN
1115 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
1116 ELSIF (l_return_val = -3) THEN
1117 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
1118 ELSIF (l_return_val = -4) THEN
1119 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
1120 ELSIF (l_return_val = -5) THEN
1121 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1122 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1123 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um);
1124 ELSIF (l_return_val = -6) THEN
1125 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
1126 ELSIF (l_return_val = -7) THEN
1127 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
1128 ELSIF (l_return_val = -10) THEN
1129 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1130 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1131 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um);
1132 ELSIF (l_return_val = -11) THEN
1133 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
1134 ELSIF (l_return_val < -11) THEN
1135 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
1136 END IF;
1137 FND_MSG_PUB.ADD;
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140
1141 IF ( (x_ic_item_mst_row.lot_indivisible = 1)
1142 AND (x_ic_loct_inv_row.loct_onhand <> p_lot_rec_tbl(j).opm_primary_quantity)
1143 ) THEN
1144 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_INDIVISIBLE_LOT');
1145 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1146 FND_MESSAGE.SET_TOKEN('LOT_ID',p_lot_rec_tbl(j).opm_lot_id);
1147 FND_MSG_PUB.Add;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150 ELSE
1151 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_CANNOT_GET_ONHAND');
1152 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1153 FND_MESSAGE.SET_TOKEN('LOT_ID',p_lot_rec_tbl(j).opm_lot_id);
1154 FND_MSG_PUB.Add;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;--}
1157
1158 --lets check the deviation between OPM primary and secondary if secondary is passed
1159 --if it is not passed we calculate the secondary qty.
1160 IF (x_ic_item_mst_row.dualum_ind > 0) THEN
1161 IF (x_ic_loct_inv_row.loct_onhand = p_lot_rec_tbl(j).opm_primary_quantity) THEN
1162 p_lot_rec_tbl(j).quantity2 := x_ic_loct_inv_row.loct_onhand2;
1163 ELSE
1164 IF (p_lot_rec_tbl(j).quantity2 IS NULL) THEN
1165
1166 IF (x_ic_item_mst_row.dualum_ind = 3) THEN
1167 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_NULL_QTY2');
1168 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1169 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1170 FND_MSG_PUB.Add;
1171 RAISE FND_API.G_EXC_ERROR;
1172 END IF;
1173
1174 l_return_val := GMICUOM.uom_conversion
1175 (
1176 x_ic_item_mst_row.item_id,
1177 nvl(x_ic_lots_mst_row.lot_id,0),
1178 p_lot_rec_tbl(j).quantity,
1179 p_line_rec_tbl(i).quantity_um,
1180 x_ic_item_mst_row.item_um2,
1181 0
1182 );
1183 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1184 log_msg('After calling GMICUOM.uom_conversion to get quantity2 when transfer type is 0. return val is '||l_return_val);
1185 END IF;
1186
1187 IF(l_return_val >= 0) THEN
1188 p_lot_rec_tbl(j).quantity2 := l_return_val;
1189 END IF;
1190
1191 IF (l_return_val < 0) THEN
1192 IF (l_return_val = -1) THEN
1193 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
1194 ELSIF (l_return_val = -3) THEN
1195 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
1196 ELSIF (l_return_val = -4) THEN
1197 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
1198 ELSIF (l_return_val = -5) THEN
1199 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1200 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1201 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um2);
1202 ELSIF (l_return_val = -6) THEN
1203 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
1204 ELSIF (l_return_val = -7) THEN
1205 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
1206 ELSIF (l_return_val = -10) THEN
1207 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1208 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1209 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um2);
1210 ELSIF (l_return_val = -11) THEN
1211 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
1212 ELSIF (l_return_val < -11) THEN
1213 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
1214 END IF;
1215 FND_MSG_PUB.ADD;
1216 RAISE FND_API.G_EXC_ERROR;
1217 END IF;
1218 END IF;
1219 END IF;
1220 ELSIF (x_ic_item_mst_row.dualum_ind = 0) THEN
1221 p_lot_rec_tbl(j).quantity2 := NULL;
1222 END IF;
1223
1224 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1225 log_msg('Transfer type is 0. checking if the OPM lot already exists in discrete');
1226 END IF;
1227
1228 --Lets see if the OPM lot already exists in discrete
1229 --lets get the ODM lot
1230 --{
1231 IF (x_ic_item_mst_row.lot_ctl = 1) THEN
1232
1233 l_odm_lot.lot_number := p_lot_rec_tbl(j).odm_lot_number;
1234
1235 --{
1236 IF (INV_Validate.lot_number (p_lot => l_odm_lot,
1237 p_org => l_org,
1238 p_item => l_item
1239 ) = inv_validate.F) THEN
1240
1241
1242 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1243 log_msg('ODM lot does not previously exist when transfer type is 0');
1244 END IF;
1245
1246 IF (l_item.shelf_life_code = 1) THEN
1247 p_lot_rec_tbl(j).odm_lot_expiration_date := NULL;
1248 ELSE
1249 p_lot_rec_tbl(j).odm_lot_expiration_date := p_lot_rec_tbl(j).opm_lot_expiration_date;
1250 END IF;
1251 END IF;--}
1252
1253 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1254 log_msg('Transfer type is 0. End of check whether the OPM lot already exists in discrete');
1255 END IF;
1256
1257 --OPM is migrating all lot specific conversions to discrete as is
1258 --we can assume that discrete qtys are same as opm qtys
1259 p_lot_rec_tbl(j).odm_primary_quantity := p_lot_rec_tbl(j).opm_primary_quantity;
1260
1261 --ODM primary quantity validations
1262 -- if item has indivisible flag set, then make sure that quantity is integer in
1263 -- primary UOM
1264
1265 IF ( ( l_item.indivisible_flag = 'Y' )
1266 AND ( Round(p_lot_rec_tbl(j).odm_primary_quantity,(38-1)) <> TRUNC(p_lot_rec_tbl(j).odm_primary_quantity))
1267 ) then
1268 FND_MESSAGE.SET_NAME('INV', 'DIVISIBILITY_VIOLATION');
1269 FND_MSG_PUB.Add;
1270 RAISE FND_API.G_EXC_ERROR ;
1271 END IF;
1272
1273 END IF;--}
1274 --transfer is from discrete to process
1275 ELSIF (p_hdr_rec.transfer_type = 1) THEN
1276 --lets validate the ODM Lot number
1277 --{
1278 IF (l_item.lot_control_code = 1) THEN
1279 IF (p_lot_rec_tbl(j).odm_lot_number IS NOT NULL) THEN
1280 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_ODM_LOT_IS_NOT_NULL');
1281 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1282 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1283 FND_MSG_PUB.Add;
1284 RAISE FND_API.G_EXC_ERROR;
1285 END IF;
1286
1287 END IF;--}
1288
1289 l_odm_lot.lot_number := p_lot_rec_tbl(j).odm_lot_number;
1290 --{
1291 IF (l_item.lot_control_code = 2) THEN
1292
1293 IF (p_lot_rec_tbl(j).odm_lot_number IS NULL) THEN
1294 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_ODM_LOT_IS_NULL');
1295 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1296 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1297 FND_MSG_PUB.Add;
1298 RAISE FND_API.G_EXC_ERROR;
1299 END IF;
1300
1301 END IF;--}
1302
1303 --Lets see if the ODM lot already exists in OPM
1304 IF (x_ic_item_mst_row.lot_ctl = 1) THEN
1305
1306 --get the opm lot row
1307 SELECT *
1308 INTO x_ic_lots_mst_row
1309 FROM ic_lots_mst
1310 WHERE ITEM_ID = x_ic_item_mst_row.item_id
1311 AND lot_id = p_lot_rec_tbl(j).opm_lot_id;
1312
1313
1314 p_lot_rec_tbl(j).opm_lot_no := x_ic_lots_mst_row.lot_no;
1315 p_lot_rec_tbl(j).opm_sublot_no := x_ic_lots_mst_row.sublot_no;
1316 p_lot_rec_tbl(j).opm_grade := x_ic_lots_mst_row.qc_grade;
1317 p_lot_rec_tbl(j).opm_lot_expiration_date := x_ic_lots_mst_row.expire_date;
1318
1319 --We need this get the lot_status of the OPM lot.
1320 x_ic_loct_inv_row.whse_code := p_line_rec_tbl(i).opm_whse_code;
1321 x_ic_loct_inv_row.location := p_line_rec_tbl(i).opm_location;
1322 x_ic_loct_inv_row.item_id := p_line_rec_tbl(i).opm_item_id;
1323 x_ic_loct_inv_row.lot_id := p_lot_rec_tbl(j).opm_lot_id;
1324
1325 IF GMIVDBL.ic_loct_inv_select(x_ic_loct_inv_row, x_ic_loct_inv_row) THEN
1326 p_lot_rec_tbl(j).opm_lot_status := x_ic_loct_inv_row.lot_status;
1327 ELSE
1328 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1329 log_msg('Failed call to GMIVDBL.ic_loct_inv_select when transfer type is 1. Not an error.');
1330 END IF;
1331 END IF;
1332
1333 END IF;
1334
1335 --Now lets calcualte the primary qty for OPM.
1336 --we needed the lot id before that since if the lot existed
1337 --conversion could have been lot specific.
1338
1339 l_return_val := GMICUOM.uom_conversion
1340 (
1341 x_ic_item_mst_row.item_id,
1342 nvl(p_lot_rec_tbl(j).opm_lot_id,0),
1343 p_lot_rec_tbl(j).quantity,
1344 p_line_rec_tbl(i).quantity_um,
1345 x_ic_item_mst_row.item_um,
1346 0
1347 );
1348
1349 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1350 log_msg('After calling GMICUOM.uom_conversion to get opm_primary_quantity when transfer type is 1. return val is '||l_return_val);
1351 END IF;
1352
1353 IF(l_return_val >= 0) THEN
1354 p_lot_rec_tbl(j).opm_primary_quantity := l_return_val;
1355 END IF;
1356
1357 IF (l_return_val < 0) THEN
1358 IF (l_return_val = -1) THEN
1359 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
1360 ELSIF (l_return_val = -3) THEN
1361 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
1362 ELSIF (l_return_val = -4) THEN
1363 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
1364 ELSIF (l_return_val = -5) THEN
1365 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1366 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1367 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um);
1368 ELSIF (l_return_val = -6) THEN
1369 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
1370 ELSIF (l_return_val = -7) THEN
1371 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
1372 ELSIF (l_return_val = -10) THEN
1373 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1374 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1375 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um);
1376 ELSIF (l_return_val = -11) THEN
1377 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
1378 ELSIF (l_return_val < -11) THEN
1379 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
1380 END IF;
1381 FND_MSG_PUB.ADD;
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385 IF (x_ic_item_mst_row.dualum_ind > 0) THEN
1386 IF (p_lot_rec_tbl(j).quantity2 IS NULL) THEN
1387
1388 IF (x_ic_item_mst_row.dualum_ind = 3) THEN
1389 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_NULL_QTY2');
1390 FND_MESSAGE.SET_TOKEN('LINE_NO',p_line_rec_tbl(i).line_no);
1391 FND_MESSAGE.SET_TOKEN('ITEM_NO',x_ic_item_mst_row.item_no);
1392 FND_MSG_PUB.Add;
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396 l_return_val := GMICUOM.uom_conversion
1397 (
1398 x_ic_item_mst_row.item_id,
1399 nvl(p_lot_rec_tbl(j).opm_lot_id,0),
1400 p_lot_rec_tbl(j).quantity,
1401 p_line_rec_tbl(i).quantity_um,
1402 x_ic_item_mst_row.item_um2,
1403 0
1404 );
1405
1406 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1407 log_msg('After calling GMICUOM.uom_conversion to get quantity2 when transfer type is 1. return val is '||l_return_val);
1408 END IF;
1409
1410 IF(l_return_val >= 0) THEN
1411 p_lot_rec_tbl(j).quantity2 := l_return_val;
1412 END IF;
1413
1414 IF (l_return_val < 0) THEN
1415 IF (l_return_val = -1) THEN
1416 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
1417 ELSIF (l_return_val = -3) THEN
1418 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
1419 ELSIF (l_return_val = -4) THEN
1420 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
1421 ELSIF (l_return_val = -5) THEN
1422 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1423 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1424 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um2);
1425 ELSIF (l_return_val = -6) THEN
1426 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
1427 ELSIF (l_return_val = -7) THEN
1428 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
1429 ELSIF (l_return_val = -10) THEN
1430 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1431 FND_MESSAGE.set_token('FROMUOM',p_line_rec_tbl(i).quantity_um);
1432 FND_MESSAGE.set_token('TOUOM',x_ic_item_mst_row.item_um2);
1433 ELSIF (l_return_val = -11) THEN
1434 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
1435 ELSIF (l_return_val < -11) THEN
1436 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
1437 END IF;
1438 FND_MSG_PUB.ADD;
1439 RAISE FND_API.G_EXC_ERROR;
1440 END IF;
1441
1442 END IF;
1443 ELSIF (x_ic_item_mst_row.dualum_ind = 0) THEN
1444 p_lot_rec_tbl(j).quantity2 := NULL;
1445 END IF;
1446 END IF;--}
1447 p_lot_rec_tbl(j).odm_primary_quantity := p_lot_rec_tbl(j).opm_primary_quantity;
1448 --add the quantities at lot to get quantities at the line level
1449 p_line_rec_tbl(i).odm_primary_quantity := nvl(p_line_rec_tbl(i).odm_primary_quantity,0) + p_lot_rec_tbl(j).odm_primary_quantity;
1450 p_line_rec_tbl(i).opm_primary_quantity := nvl(p_line_rec_tbl(i).opm_primary_quantity,0) + p_lot_rec_tbl(j).opm_primary_quantity;
1451
1452 IF (x_ic_item_mst_row.dualum_ind > 0) THEN
1453 p_line_rec_tbl(i).quantity2 := p_line_rec_tbl(i).quantity2 + p_lot_rec_tbl(j).quantity2;
1454 END IF;
1455
1456 IF (p_line_rec_tbl(i).lot_control = 0) THEN
1457 p_lot_rec_tbl(j).opm_lot_id := 0;
1458 p_lot_rec_tbl(j).opm_lot_status := NULL;
1459 p_lot_rec_tbl(j).opm_grade := NULL;
1460 p_lot_rec_tbl(j).odm_lot_number := NULL;
1461 p_lot_rec_tbl(j).odm_lot_expiration_date := NULL;
1462 p_lot_rec_tbl(j).opm_lot_expiration_date := NULL;
1463 END IF;
1464
1465 END IF ;
1466 --}
1467 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1468 log_msg('Ending validation of lot.lot record no '||to_char(j));
1469 END IF;
1470
1471 END;
1472 END LOOP; --This for lot belonging to the line.
1473
1474 /* if the line has the lot information then we will assign line level attributes from
1475 the lot record */
1476
1477 IF (p_line_rec_tbl(i).lot_level = 0) THEN
1478 --this means a default lot record was created for this line
1479 p_line_rec_tbl(i).opm_lot_no := p_lot_rec_tbl(l_lot_rec_count).opm_lot_no;
1480 p_line_rec_tbl(i).opm_sublot_no := p_lot_rec_tbl(l_lot_rec_count).opm_sublot_no;
1481 p_line_rec_tbl(i).odm_lot_number := p_lot_rec_tbl(l_lot_rec_count).odm_lot_number;
1482 p_line_rec_tbl(i).opm_lot_expiration_date := p_lot_rec_tbl(l_lot_rec_count).opm_lot_expiration_date;
1483 p_line_rec_tbl(i).odm_lot_expiration_date := p_lot_rec_tbl(l_lot_rec_count).odm_lot_expiration_date;
1484 p_line_rec_tbl(i).opm_lot_id := p_lot_rec_tbl(l_lot_rec_count).opm_lot_id;
1485 p_line_rec_tbl(i).opm_lot_status := p_lot_rec_tbl(l_lot_rec_count).opm_lot_status;
1486 p_line_rec_tbl(i).opm_grade := p_lot_rec_tbl(l_lot_rec_count).opm_grade;
1487
1488 --we dont need the dummy lot record anymore.
1489 p_lot_rec_tbl.DELETE(l_lot_rec_count);
1490 END IF;
1491 --}
1492 --OK line has been validated and lots belonging to the lines have been validated
1493 --lets get the charge accounts.
1494
1495 --Begin Supriya Malluru Bug#4114621
1496 IF (p_line_rec_tbl(i).opm_item_no IS NOT NULL) THEN
1497 item_gl_class := x_ic_item_mst_row.gl_class;
1498 OPEN cur_item_gl_cls(p_line_rec_tbl(i).opm_item_id);
1499 FETCH cur_item_gl_cls
1500 INTO item_gl_class;
1501
1502 IF cur_item_gl_cls%NOTFOUND THEN
1503 item_gl_class := NULL;
1504 END IF;
1505
1506 -- Populate GL Business Class and GL Product Line for the Item entered.
1507 FOR x in Cur_gl_cls(p_line_rec_tbl(i).opm_item_id)
1508 LOOP
1509 IF x.opm_class = 'GL_BUSINESS_CLASS' THEN
1510 v_business_class_found := TRUE;
1511 IF x.category_id IS NULL THEN
1512 gl_business_class_cat_id := '';
1513 ELSE
1514 IF (gl_business_class_cat_id IS NULL) OR
1515 (gl_business_class_cat_id <> x.category_id) THEN
1516 gl_business_class_cat_id := x.category_id;
1517 END IF;
1518 END IF;
1519 END IF;
1520 IF x.opm_class = 'GL_PRODUCT_LINE' THEN
1521 v_product_line_found := TRUE;
1522 IF x.category_id IS NULL THEN
1523 gl_product_line_cat_id := '';
1524 ELSE
1525 IF (gl_product_line_cat_id IS NULL) OR
1526 (gl_product_line_cat_id <> x.category_id) THEN
1527 gl_product_line_cat_id := x.category_id;
1528 END IF;
1529 END IF;
1530 END IF;
1531 END LOOP;
1532
1533 IF (NOT v_business_class_found) THEN
1534 gl_business_class_cat_id := NULL;
1535 END IF;
1536
1537 IF (NOT v_product_line_found) THEN
1538 gl_product_line_cat_id := NULL;
1539 END IF;
1540
1541 END IF;
1542 --End Supriya Malluru Bug#4114621
1543
1544 gmf_get_mappings.get_account_mappings
1545 ( v_co_code => l_get_opm_fiscal_details_row.co_code
1546 ,v_orgn_code => l_get_opm_fiscal_details_row.orgn_code
1547 ,v_whse_code => p_line_rec_tbl(i).opm_whse_code
1548 ,v_item_id => p_line_rec_tbl(i).opm_item_id
1549 ,v_reason_code => p_line_rec_tbl(i).opm_reason_code
1550 ,v_sub_event_type => 31010 /* IADJ */
1551 ,v_acct_ttl_type => 6000 /* IVA */
1552 ,v_source => 7 /* IC */
1553 ,v_vendor_id => NULL
1554 ,v_cust_id => NULL
1555 ,v_icgl_class => x_ic_item_mst_row.gl_class
1556 ,v_vendgl_class => NULL
1557 ,v_custgl_class => NULL
1558 ,v_currency_code => l_get_opm_fiscal_details_row.base_currency_code
1559 ,v_routing_id => NULL
1560 ,v_charge_id => NULL
1561 ,v_taxauth_id => NULL
1562 ,v_aqui_cost_id => NULL
1563 ,v_resources => NULL
1564 ,v_cost_cmpntcls_id => NULL
1565 ,v_cost_analysis_code => NULL
1566 ,v_order_type => NULL
1567 ,v_acct_id => p_line_rec_tbl(i).opm_charge_acct_id
1568 ,v_acctg_unit_id => p_line_rec_tbl(i).opm_charge_au_id
1569 ,v_business_class_cat_id => gl_business_class_cat_id --Bug#4114621
1570 ,v_product_line_cat_id => gl_product_line_cat_id --Bug#4114621
1571 );
1572
1573 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1574 log_msg('After call to gmf_get_mappings.get_account_mappings ');
1575 END IF;
1576 --now lets get the ccid (same as ODM charge acct id)
1577 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1578 log_msg('Before call to gmf_validate_account.get_accu_acct_ids ');
1579 END IF;
1580
1581 --get ccid which is the same as odm_charge_account_id
1582 gmf_validate_account.validate_segments
1583 (
1584 p_co_code => l_get_opm_fiscal_details_row.co_code,
1585 p_acctg_unit_id => p_line_rec_tbl(i).opm_charge_au_id,
1586 p_acct_id => p_line_rec_tbl(i).opm_charge_acct_id,
1587 p_acctg_unit_no => NULL,
1588 p_acct_no => NULL,
1589 p_create_combination => 'Y',
1590 x_ccid => p_line_rec_tbl(i).odm_charge_account_id,
1591 x_concat_seg => l_concat_segs,
1592 x_status => x_return_status,
1593 x_errmsg => x_msg_data
1594 );
1595
1596 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1597 log_msg('After call to gmf_validate_account.validate_segments. return status is ' ||x_return_status);
1598 END IF;
1599
1600 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1601 FND_MESSAGE.SET_NAME ('GMI','GMI_SET_STRING');
1602 FND_MESSAGE.SET_TOKEN('STRING', substrb(x_msg_data,1,240));
1603 FND_MSG_PUB.Add;
1604 RAISE FND_API.G_EXC_ERROR;
1605 END IF;
1606
1607 END;
1608 END LOOP; -- this for the lines.
1609 --}
1610
1611 FND_MSG_PUB.Count_AND_GET
1612 (p_count => x_msg_count, p_data => x_msg_data);
1613
1614 EXCEPTION
1615
1616 WHEN FND_API.G_EXC_ERROR THEN
1617 ROLLBACK to validate_transfer;
1618 x_return_status := FND_API.G_RET_STS_ERROR;
1619 FND_MSG_PUB.Count_AND_GET
1620 (p_count => x_msg_count, p_data => x_msg_data);
1621
1622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1623 ROLLBACK to validate_transfer;
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625 FND_MSG_PUB.Count_AND_GET
1626 (p_count => x_msg_count, p_data => x_msg_data);
1627
1628 WHEN OTHERS THEN
1629 ROLLBACK to validate_transfer;
1630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1631 IF (SQLCODE IS NOT NULL) THEN
1632 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
1633 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1634 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1635 END IF;
1636
1637 FND_MSG_PUB.Count_AND_GET
1638 (p_count => x_msg_count, p_data => x_msg_data);
1639
1640
1641 END Validate_transfer;
1642
1643 /* +==========================================================================+
1644 | PROCEDURE NAME |
1645 | construct_post_records |
1646 | |
1647 | TYPE |
1648 | Private |
1649 | |
1650 | USAGE |
1651 | constructs and posts process/discrete transfer records to |
1652 | tables gmi_discrete_transfers, gmi_discrete_transfers |
1653 | and gmi_discrete_transfer_lots. |
1654 | It will also create lots in OPM and ODM if the lot does not exist. |
1655 | It would created transactions and update balances in OPM inventory |
1656 | and Oracle Inventory |
1657 | |
1658 | |
1659 | RETURNS |
1660 | Via x_ OUT parameters |
1661 | |
1662 | HISTORY |
1663 | Created Jalaj Srivastava |
1664 | RLNAGARA Material Status Migration ME - Updating Status in MOQD |
1665 | |
1666 +==========================================================================+ */
1667
1668 PROCEDURE construct_post_records
1669 ( p_api_version IN NUMBER
1670 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1671 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1672 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1673 , x_return_status OUT NOCOPY VARCHAR2
1674 , x_msg_count OUT NOCOPY NUMBER
1675 , x_msg_data OUT NOCOPY VARCHAR2
1676 , p_hdr_rec IN OUT NOCOPY hdr_type
1677 , p_line_rec_tbl IN OUT NOCOPY line_type_tbl
1678 , p_lot_rec_tbl IN OUT NOCOPY lot_type_tbl
1679 , x_hdr_row OUT NOCOPY gmi_discrete_transfers%ROWTYPE
1680 , x_line_row_tbl OUT NOCOPY line_row_tbl
1681 , x_lot_row_tbl OUT NOCOPY lot_row_tbl
1682 ) IS
1683
1684 l_api_name CONSTANT VARCHAR2(30) := 'construct_post_records' ;
1685 l_api_version CONSTANT NUMBER := 1.0 ;
1686 l_no_violation BOOLEAN;
1687 l_return_val NUMBER;
1688
1689 --rlnagara 2 Material Status Migration ME
1690 l_lot_ctl NUMBER;
1691 l_lot_sts VARCHAR2(10);
1692
1693 BEGIN
1694
1695 IF FND_API.to_boolean(p_init_msg_list) THEN
1696 FND_MSG_PUB.Initialize;
1697 END IF;
1698
1699 SAVEPOINT create_transfer;
1700
1701 -- Standard call to check for call compatibility.
1702 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1703 p_api_version ,
1704 l_api_name ,
1705 G_PKG_NAME ) THEN
1706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END IF;
1708 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1709
1710 --lets prepare the header record.
1711 IF (p_hdr_rec.assignment_type = 2) THEN
1712 --automatic numbering
1713 p_hdr_rec.transfer_number := GMIVDBX.Get_doc_no
1714 (
1715 x_return_status => x_return_status
1716 , x_msg_count => x_msg_count
1717 , x_msg_data => x_msg_data
1718 , p_doc_type => 'DXFR'
1719 , p_orgn_code => p_hdr_rec.orgn_code
1720 );
1721
1722 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1723 log_msg('After calling GMIVDBX.Get_doc_no.return status is '||x_return_status);
1724 END IF;
1725
1726 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1727 RAISE FND_API.G_EXC_ERROR;
1728 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1730 END IF;
1731
1732 END IF;
1733
1734 IF (NVL(p_hdr_rec.transfer_number, ' ') = ' ') THEN
1735 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_DOC_NO');
1736 FND_MESSAGE.SET_TOKEN('DOC_TYPE','DXFR');
1737 FND_MESSAGE.SET_TOKEN('ORGN_CODE',p_hdr_rec.orgn_code);
1738 FND_MSG_PUB.Add;
1739 RAISE FND_API.G_EXC_ERROR;
1740 END IF;
1741
1742 --lets insert the header record.
1743
1744 GMIVDBX.header_insert
1745 (
1746 p_api_version => p_api_version
1747 , p_init_msg_list => FND_API.G_FALSE
1748 , p_commit => FND_API.G_FALSE
1749 , p_validation_level => p_validation_level
1750 , x_return_status => x_return_status
1751 , x_msg_count => x_msg_count
1752 , x_msg_data => x_msg_data
1753 , p_hdr_rec => p_hdr_rec
1754 , x_hdr_row => x_hdr_row
1755 );
1756
1757 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1758 log_msg('After calling GMIVDBX.header_insert.return status is '||x_return_status);
1759 END IF;
1760
1761 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1762 RAISE FND_API.G_EXC_ERROR;
1763 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END IF;
1766
1767 --now we will insert the lines and the lots.
1768 FOR i in 1..p_line_rec_tbl.count LOOP --{
1769 BEGIN
1770 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1771 log_msg('Constructing record for posting line no '||to_char(i));
1772 END IF;
1773 --lot could be sepcified at the lot level or the line level.
1774 --we are ready to insert records in gmi_discrete_transfer_lines
1775 GMIVDBX.line_insert
1776 (
1777 p_api_version => p_api_version
1778 , p_init_msg_list => FND_API.G_FALSE
1779 , p_commit => FND_API.G_FALSE
1780 , p_validation_level => p_validation_level
1781 , x_return_status => x_return_status
1782 , x_msg_count => x_msg_count
1783 , x_msg_data => x_msg_data
1784 , p_hdr_row => x_hdr_row
1785 , p_line_rec => p_line_rec_tbl(i)
1786 , x_line_row => x_line_row_tbl(i)
1787 );
1788
1789 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1790 log_msg('After call to procedure GMIVDBX.line_insert return status is '||x_return_status);
1791 END IF;
1792 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1793 RAISE FND_API.G_EXC_ERROR;
1794 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1796 END IF;
1797
1798
1799 --{
1800 IF (p_line_rec_tbl(i).lot_level = 1) THEN
1801 --start processing lots.
1802 FOR j in 1..p_lot_rec_tbl.count LOOP --{
1803 --{
1804 IF (p_lot_rec_tbl(j).line_no = p_line_rec_tbl(i).line_no) THEN
1805
1806 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1807 log_msg('Constructing record for posting line no '||to_char(i)||' and lot record '||to_char(j));
1808 END IF;
1809
1810 --OK lets insert records in gmi_transfer_lots.
1811 GMIVDBX.lot_insert
1812 (
1813 p_api_version => p_api_version
1814 , p_init_msg_list => FND_API.G_FALSE
1815 , p_commit => FND_API.G_FALSE
1816 , p_validation_level => p_validation_level
1817 , x_return_status => x_return_status
1818 , x_msg_count => x_msg_count
1819 , x_msg_data => x_msg_data
1820 , p_line_row => x_line_row_tbl(i)
1821 , p_lot_rec => p_lot_rec_tbl(j)
1822 , x_lot_row => x_lot_row_tbl(j)
1823 );
1824
1825 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1826 log_msg('After call to procedure GMIVDBX.lot_insert return status is '||x_return_status);
1827 END IF;
1828 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834 END IF;--}
1835
1836 END LOOP;--} --LOOP for lots with lot specified at the lot level.
1837
1838 END IF; --}
1839
1840 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1841 log_msg('Calling GMIVTDX.create_txn_update_balances for posting line no '||to_char(i));
1842 END IF;
1843
1844 --below procedure is called once for each line
1845 --we get the header, line and lots rows from the database based on the ids passed
1846 --it returns x_transaction_header_id which needs to be stored
1847 --for subsequent lines
1848 GMIVTDX.create_txn_update_balances
1849 (
1850 p_api_version => p_api_version
1851 , p_init_msg_list => FND_API.G_FALSE
1852 , p_commit => FND_API.G_FALSE
1853 , p_validation_level => p_validation_level
1854 , x_return_status => x_return_status
1855 , x_msg_count => x_msg_count
1856 , x_msg_data => x_msg_data
1857 , p_transfer_id => x_hdr_row.transfer_id
1858 , p_line_id => x_line_row_tbl(i).line_id
1859 , x_transaction_header_id => p_hdr_rec.transaction_header_id
1860 );
1861 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1862 log_msg('After call to procedure GMIVTDX.create_txn_update_balances.return status is '||x_return_status);
1863 END IF;
1864 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1865 RAISE FND_API.G_EXC_ERROR;
1866 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1868 END IF;
1869 END;--Line block
1870
1871 END LOOP;--} --LOOP for line
1872
1873 -- we need to call ODM transaction manager to process rows in mmtt and mmlt.
1874 -- all the records have been inserted in mmtt and mmlt
1875 l_return_val := INV_LPN_TRX_PUB.PROCESS_LPN_TRX
1876 (
1877 p_trx_hdr_id => p_hdr_rec.transaction_header_id
1878 ,p_commit => fnd_api.g_false
1879 ,x_proc_msg => x_msg_data
1880 ,p_proc_mode => 1 /* Online Processing */
1881 ,p_process_trx => fnd_api.g_true
1882 ,p_atomic => fnd_api.g_true
1883 ,p_business_flow_code => NULL
1884 );
1885 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1886 log_msg('After call to procedure INV_LPN_TRX_PUB.PROCESS_LPN_TRX .return val is '||l_return_val);
1887 END IF;
1888
1889 IF (l_return_val <> 0) THEN
1890 FND_MESSAGE.SET_NAME ('GMI','GMI_SET_STRING');
1891 FND_MESSAGE.SET_TOKEN('STRING', substrb(x_msg_data,1,240));
1892 FND_MSG_PUB.Add;
1893 RAISE FND_API.G_EXC_ERROR;
1894 END IF;
1895
1896 --everything is good. set costed flag to yes in mmt
1897 update mtl_material_transactions
1898 set costed_flag = null,
1899 opm_costed_flag = null
1900 where transaction_set_id = p_hdr_rec.transaction_header_id;
1901
1902
1903 --rlnagara 2 Material Status Migration ME start - Added the below code to update the status in MOQD
1904 select lot_control_code, lot_status_enabled
1905 into l_lot_ctl, l_lot_sts
1906 from mtl_system_items_b
1907 where inventory_item_id = (select inventory_item_id from mtl_material_transactions where transaction_set_id = p_hdr_rec.transaction_header_id)
1908 and organization_id = (select organization_id from mtl_material_transactions where transaction_set_id = p_hdr_rec.transaction_header_id);
1909
1910 IF l_lot_ctl = 2 and l_lot_sts = 'Y' THEN
1911 update mtl_onhand_quantities_detail
1912 set status_id = (select status_id from mtl_material_statuses
1913 where status_code = (select opm_lot_status from gmi_discrete_transfer_lots
1914 where transfer_id = (select transaction_source_id from mtl_material_transactions
1915 where transaction_set_id = p_hdr_rec.transaction_header_id)) )
1916 where create_transaction_id = (select transaction_id from mtl_material_transactions
1917 where transaction_set_id = p_hdr_rec.transaction_header_id) ;
1918 END IF;
1919 --rlnagara 2 Material Status Migration ME end.
1920
1921
1922 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1923 log_msg('After update to mmt.costed_flag');
1924 END IF;
1925
1926
1927 FND_MSG_PUB.Count_AND_GET
1928 (p_count => x_msg_count, p_data => x_msg_data);
1929
1930
1931 EXCEPTION
1932
1933 WHEN FND_API.G_EXC_ERROR THEN
1934 ROLLBACK to create_transfer;
1935 x_return_status := FND_API.G_RET_STS_ERROR;
1936 FND_MSG_PUB.Count_AND_GET
1937 (p_count => x_msg_count, p_data => x_msg_data);
1938
1939 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1940 ROLLBACK to create_transfer;
1941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1942 FND_MSG_PUB.Count_AND_GET
1943 (p_count => x_msg_count, p_data => x_msg_data);
1944
1945 WHEN OTHERS THEN
1946 ROLLBACK to create_transfer;
1947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1948 FND_MSG_PUB.Count_AND_GET
1949 (p_count => x_msg_count, p_data => x_msg_data);
1950
1951 END construct_post_records;
1952
1953 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
1954 BEGIN
1955
1956 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
1957 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
1958 FND_MSG_PUB.Add;
1959
1960 END log_msg ;
1961
1962 END GMIVDX;
[Home] [Help]