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