1 PACKAGE BODY INV_MATERIAL_STATUS_GRP as
2 /* $Header: INVMSGRB.pls 120.34.12010000.2 2008/10/24 10:26:09 aambulka ship $ */
3
4
5
6 -- Global constant holding package name
7 g_pkg_name constant varchar2(50) := 'INV_MATERIAL_STATUS_GRP';
8
9 g_status_id NUMBER;
10 g_transaction_type_id NUMBER;
11 g_is_allowed VARCHAR2(1);
12
13 g_organization_id NUMBER;
14 g_inventory_item_id NUMBER;
15 g_lot_status_enabled VARCHAR2(1);
16 g_default_lot_status_id NUMBER;
17 g_serial_status_enabled VARCHAR2(1);
18 g_default_serial_status_id NUMBER;
19
20 g_isa_trx_type_id NUMBER;
21 g_isa_trx_status_enabled VARCHAR2(1);
22 g_isa_sub_status_id NUMBER;
23 g_isa_loc_status_id NUMBER;
24 g_isa_organization_id NUMBER;
25 g_isa_sub_code VARCHAR2(10);
26 g_isa_locator_id NUMBER;
27 --Bug 3804629, changed the datatype from number to varchar2
28 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
29 g_isa_lot_number VARCHAR2(80);
30 g_isa_lot_number_status_id NUMBER;
31
32 --Bug #5367711
33 --Cache the variables for old item, item trackable and freeze flag
34 g_old_item_id NUMBER;
35 g_freeze_flag csi_install_parameters.freeze_flag%TYPE;
36 g_item_trackable mtl_system_items.comms_nl_trackable_flag%TYPE;
37 g_transaction_action_id NUMBER;
38 g_transaction_source_type_id NUMBER;
39
40 -- Onhand Material Status Support
41 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
42 g_sub_code VARCHAR2(10);
43 g_locator_control NUMBER;
44 -- Onhand Material Status Support
45
46
47 /*LPN Status Project*/
48 FUNCTION is_status_applicable_lpns
49 (p_wms_installed IN VARCHAR2,
50 p_trx_status_enabled IN NUMBER,
51 p_trx_type_id IN NUMBER,
52 p_lot_status_enabled IN VARCHAR2,
53 p_serial_status_enabled IN VARCHAR2,
54 p_organization_id IN NUMBER,
55 p_inventory_item_id IN NUMBER,
56 p_sub_code IN VARCHAR2,
57 p_locator_id IN NUMBER,
58 p_lot_number IN VARCHAR2,
59 p_serial_number IN VARCHAR2,
60 p_object_type IN VARCHAR2,
61 p_fromlpn_id IN NUMBER,
62 p_xfer_lpn_id IN NUMBER,
63 p_xfer_sub_code IN VARCHAR2,
64 p_xfer_locator_id IN NUMBER,
65 p_xfer_org_id IN NUMBER)
66 RETURN NUMBER IS
67
68 l_allow_mixed_status number := NVL(FND_PROFILE.VALUE('WMS_ALLOW_MIXED_STATUS'),2);
69 l_allow_status VARCHAR2(1):='Y';
70 l_allow_transaction VARCHAR2(1):='Y';
71 l_serial_controlled number := 0;
72 l_serial_status_enabled number := 0;
73 l_counter number := 0;
74 l_count number := 0;
75 l_xfer_locator_id number := -1;
76 l_xfer_sub_code VARCHAR2(50) := NULL;
77 l_xfer_org_id number := -1;
78 l_xferlpn_context number := 5;
79 l_lpn_context number := 5; --bug 6918618
80 l_return_status_id number := -99; --bug 6918618
81 temp_status_id number := NULL; --7007389
82 c_api_name varchar2(30) := 'is_status_applicable';
83
84 l_validate number := 0 ;
85
86 CURSOR c_lpn_item
87 IS
88 SELECT *
89 FROM wms_lpn_contents wlc
90 WHERE wlc.parent_lpn_id IN
91 (SELECT lpn_id
92 FROM wms_license_plate_numbers plpn
93 start with lpn_id = p_fromlpn_id
94 connect by parent_lpn_id = prior lpn_id
95 );
96
97
98 BEGIN
99 inv_trx_util_pub.TRACE('Entered is_status_applicable ', 'Material Status', 9);
100 inv_trx_util_pub.TRACE('p_wms_installed'||p_wms_installed||'p_trx_status_enabled'
101 ||p_trx_status_enabled||'p_trx_type_id'||p_trx_type_id
102 ||'p_lot_status_enabled'||p_lot_status_enabled||'p_serial_status_enabled'
103 ||p_serial_status_enabled, 'Material Status', 9);
104 inv_trx_util_pub.TRACE('p_organization_id'||p_organization_id||'p_inventory_item_id'
105 ||p_inventory_item_id||'p_sub_code'||p_sub_code||'p_locator_id'
106 ||p_locator_id||'p_lot_number'||p_lot_number||'p_serial_number'
107 ||p_serial_number||'p_object_type'||p_object_type,'Material Status', 9);
108 inv_trx_util_pub.TRACE('p_fromlpn_id'||p_fromlpn_id||'p_xfer_lpn_id'||p_xfer_lpn_id||'p_xfer_sub_code'||p_xfer_sub_code||'p_xfer_locator_id'||p_xfer_locator_id||'p_xfer_org_id'||p_xfer_org_id, 'Material Status', 9);
109
110 IF p_xfer_lpn_id IS NOT NULL THEN
111 BEGIN
112 inv_trx_util_pub.TRACE('mixed status: .. Xfer LPN is New LPN or not?? .. 1','Material Status', 9);
113
114 l_xfer_locator_id := p_xfer_locator_id;
115 l_xfer_sub_code := p_xfer_sub_code;
116 l_xfer_org_id := p_xfer_org_id;
117
118 SELECT lpn_context
119 into l_xferlpn_context
120 from wms_license_plate_numbers where lpn_id = p_xfer_lpn_id
121 AND EXISTS(select 1 from mtl_onhand_quantities_detail moqd
122 where moqd.organization_id = p_xfer_org_id
123 AND moqd.lpn_id IN
124 (
125 SELECT lpn_id
126 FROM wms_license_plate_numbers
127 WHERE outermost_lpn_id =
128 (SELECT outermost_lpn_id
129 FROM wms_license_plate_numbers
130 WHERE lpn_id = p_xfer_lpn_id
131 )
132 ));
133 inv_trx_util_pub.TRACE('mixed status: .. 1.1.1 - l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 l_xferlpn_context := 5;
138 inv_trx_util_pub.TRACE('mixed status: .. New LPN[Ctrl+G] .. 2','Material Status', 9);
139 END;
140
141
142
143 IF (l_xfer_locator_id is NULL or l_xfer_locator_id <= 0) AND l_xferlpn_context <> 5 THEN
144 BEGIN
145 SELECT subinventory_code,locator_id,organization_id
146 into l_xfer_sub_code,l_xfer_locator_id,l_xfer_org_id
147 from wms_license_plate_numbers where lpn_id = p_xfer_lpn_id;
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 l_xferlpn_context := 5;
151 inv_trx_util_pub.TRACE('mixed status: .. 2.1','Material Status', 9);
152 END;
153 END IF;
154
155 inv_trx_util_pub.TRACE('l_xferlpn_context'||l_xferlpn_context,'Material Status', 9);
156 END IF; --transfer lpnid not null
157 inv_trx_util_pub.TRACE('l_xfer_locator_id:'||l_xfer_locator_id||'l_xfer_sub_code'||l_xfer_sub_code||'l_xfer_org_id'||l_xfer_org_id,'Material Status', 9);
158 --for bug 6918618
159 IF p_fromlpn_id is NOT NULL and p_inventory_item_id IS NOT NULL AND (p_trx_type_id = 42 OR p_trx_type_id = 41) AND l_allow_mixed_status = 2 THEN --7173146
160 BEGIN
161 select lpn_context into l_lpn_context
162 from wms_license_plate_numbers
163 where lpn_id = p_fromlpn_id;
164
165 inv_trx_util_pub.TRACE('l_lpn_context::'||l_lpn_context,'Material Status', 9);
166 EXCEPTION
167 when no_data_found then
168 inv_trx_util_pub.TRACE('l_lpn_context'||l_lpn_context,'Material Status', 9);
169 l_lpn_context := 5;
170 END;
171
172 IF l_lpn_context = 1 THEN
173
174 inv_trx_util_pub.TRACE('l_lpn_context'||l_lpn_context,'Material Status', 9);
175 l_return_status_id := get_default_status --calling function to get the MOQD status
176 (p_organization_id => p_organization_id,
177 p_inventory_item_id => p_inventory_item_id,
178 p_sub_code =>p_sub_code,
179 p_loc_id => p_locator_id,
180 p_lot_number => p_lot_number,
181 p_lpn_id => p_fromlpn_id,
182 p_transaction_action_id=> NULL,
183 p_src_status_id => NULL);
184 inv_trx_util_pub.TRACE('l_return_status_id'||l_return_status_id,'Material Status', 9);
185
186
187 IF l_return_status_id <> -1 THEN
188 BEGIN
189 SELECT 'Y'
190 INTO l_allow_status FROM DUAL
191 where l_return_status_id IN
192 (SELECT moqddst.status_id
193 FROM mtl_onhand_quantities_detail moqddst
194 WHERE moqddst.organization_id = p_organization_id
195 AND moqddst.lpn_id IN
196 (
197 SELECT lpn_id
198 FROM wms_license_plate_numbers
199 WHERE outermost_lpn_id =
200 (SELECT outermost_lpn_id
201 FROM wms_license_plate_numbers
202 WHERE lpn_id = p_fromlpn_id
203 )
204 ));
205 inv_trx_util_pub.TRACE('l_allow_status::::'||l_allow_status,'Material Status', 9);
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 l_allow_status := 'N';
209 inv_trx_util_pub.TRACE('l_allow_status::::(exception block)'||l_allow_status,'Material Status', 9);
210 END;
211 END IF; --l_return_status_id <> -1
212 END IF; -- lpn context
213 END IF; --6918618
214
215 IF p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 AND l_allow_mixed_status = 2 THEN
216
217 IF p_inventory_item_id IS NOT NULL THEN
218 BEGIN
219 inv_trx_util_pub.TRACE('mixed status: inv id not null .. 10','Material Status', 9);
220
221 l_allow_status := 'N';
222 --Added for Bug 7007389
223 BEGIN
224 SELECT moqdsrc.status_id
225 INTO temp_status_id
226 FROM mtl_onhand_quantities_detail moqdsrc
227 WHERE moqdsrc.organization_id = p_organization_id
228 AND moqdsrc.inventory_item_id = p_inventory_item_id
229 AND moqdsrc.subinventory_code = p_sub_code
230 AND moqdsrc.locator_id = p_locator_id
231 AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
232 AND NVL(moqdsrc.lpn_id, 0) = NVL(p_fromlpn_id, 0)
233 AND ROWNUM = 1;
234
235 inv_trx_util_pub.TRACE('mixed status: before excep block SerialCheck.. 10.1','Material Status', 9);
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 inv_trx_util_pub.TRACE('mixed status: in excep block SerialCheck.. 10.2','Material Status', 9);
240
241 END;
242
243 IF temp_status_id is NULL THEN
244 inv_trx_util_pub.TRACE('mixed status: temp_status_id is null .. Serial 10.3','Material Status', 9);
245 l_allow_status := 'Y';
246 END IF;
247 /*End of changes for Bug # 7007389 */
248 /*Following condition has also added as part of Bug # 7007389 */
249 IF temp_status_id is NOT NULL THEN
250 inv_trx_util_pub.TRACE('mixed status: inside if .. 10.4','Material Status', 9);
251 SELECT 'Y'
252 INTO l_allow_status
253 FROM mtl_onhand_quantities_detail moqdsrc
254 WHERE moqdsrc.organization_id = p_organization_id
255 AND moqdsrc.inventory_item_id = p_inventory_item_id
256 AND moqdsrc.subinventory_code = p_sub_code
257 AND moqdsrc.locator_id = p_locator_id
258 AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
259 AND NVL(moqdsrc.lpn_id, 0) = NVL(p_fromlpn_id, 0)
260 AND ROWNUM = 1
261 AND moqdsrc.status_id IN
262 (
263 SELECT moqddst.status_id
264 FROM mtl_onhand_quantities_detail moqddst
265 WHERE moqddst.organization_id = l_xfer_org_id
266 AND moqddst.lpn_id IN
267 (
268 SELECT lpn_id
269 FROM wms_license_plate_numbers
270 WHERE outermost_lpn_id =
271 (SELECT outermost_lpn_id
272 FROM wms_license_plate_numbers
273 WHERE lpn_id = p_xfer_lpn_id
274 )
275 )
276 );
277 END IF; --added as part of 7007389
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 l_allow_status := 'N';
281 inv_trx_util_pub.TRACE('mixed status: came here .. 20 l_allow_status'||l_allow_status,'Material Status', 9);
282 END ;
283
284 ELSIF p_inventory_item_id IS NULL AND p_fromlpn_id is NOT NULL THEN
285
286 BEGIN
287 inv_trx_util_pub.TRACE('mixed status: inv id is null and from LPN ID not null .. 30','Material Status', 9);
288
289 SELECT 'N'
290 INTO l_allow_status
291 FROM dual
292 WHERE EXISTS (
293 (SELECT DISTINCT moqdsrc.status_id
294 FROM mtl_onhand_quantities_detail moqdsrc
295 WHERE moqdsrc.organization_id = p_organization_id
296 AND moqdsrc.subinventory_code = p_sub_code
297 AND moqdsrc.locator_id = p_locator_id
298 -- AND NVL(moqdsrc.lot_number,'@@@') = NVL(p_lot_number,'@@@')
299 AND moqdsrc.lpn_id IN
300 (SELECT lpn_id
301 FROM wms_license_plate_numbers plpn
302 START WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = prior lpn_id
303 AND plpn.organization_id = p_organization_id
304 )
305 )
306 MINUS
307 (
308 SELECT DISTINCT moqddst.status_id
309 FROM mtl_onhand_quantities_detail moqddst
310 WHERE moqddst.organization_id = l_xfer_org_id
311 AND moqddst.lpn_id IN
312 (
313 SELECT lpn_id
314 FROM wms_license_plate_numbers
315 WHERE outermost_lpn_id =
316 (SELECT outermost_lpn_id
317 FROM wms_license_plate_numbers
318 WHERE lpn_id = p_xfer_lpn_id
319 )
320 )
321 ));
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 l_allow_status := 'Y';
325 inv_trx_util_pub.TRACE('mixed status: ..40 l_allow_status'||l_allow_status,'Material Status', 9);
326
327 END ;
328
329 END IF; --p_inventory_item_id
330
331 END IF; --p_xfer_lpn_id condition
332
333 --moved the code out of p_xfer_lpn_id condition bcoz another check added for 6918618
334 IF l_allow_status = 'N' THEN
335 l_validate := 1;
336 inv_trx_util_pub.TRACE('mixed status: .. 50 returning l_validate'||l_validate,'Material Status', 9);
337 return l_validate;
338 END IF;
339
340 IF p_trx_type_id IS NOT NULL AND l_allow_status = 'Y' THEN --Checking for allow/disallow of source
341 IF p_inventory_item_id IS NOT NULL THEN
342
343 BEGIN
344 inv_trx_util_pub.TRACE('txn allowed or not: .. 60 verifying at source, inv id not null','Material Status', 9);
345 --First check whether the source material ALLOWS/DISALLOWS the transaction whether from LPN or LOOSE.
346
347
348 inv_trx_util_pub.TRACE('txn allowed or not: ..60.1 verifying at source','Material Status', 9);
349 SELECT 'N'
350 INTO l_allow_transaction
351 FROM dual
352 WHERE EXISTS
353 (SELECT 1
354 FROM mtl_onhand_quantities_detail moqd,
355 mtl_status_transaction_control mtc
356 WHERE moqd.organization_id = p_organization_id
357 AND moqd.inventory_item_id = p_inventory_item_id
358 AND moqd.subinventory_code = p_sub_code
359 AND nvl(moqd.locator_id,-999) = nvl(p_locator_id,-999) --6974887
360 AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
361 AND Nvl(moqd.lpn_id,0) = Nvl(p_fromlpn_id,0)
362 AND moqd.status_id = mtc.status_id
363 AND mtc.transaction_type_id = p_trx_type_id
364 AND mtc.is_allowed = 2
365 --AND ROWNUM = 1
366 ) ;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 l_allow_transaction := 'Y' ;
371 inv_trx_util_pub.TRACE('txn allowed or not: .. 70 Reached here for source:'||l_allow_transaction,'Material Status', 9);
372
373 END ;
374
375
376 --destination check if into another LPN else part for xfer into as LOOSE
377 IF l_allow_transaction = 'Y' THEN
378 IF p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
379 inv_trx_util_pub.TRACE('txn allowed or not: .. 80 verifying at dest, xfer lpn id not null','Material Status', 9);
380 BEGIN
381 inv_trx_util_pub.TRACE('txn allowed or not: .. 80.1 verifying at dest','Material Status', 9);
382 SELECT 'N'
383 INTO l_allow_transaction
384 FROM dual
385 WHERE EXISTS
386 (SELECT 1
387 FROM mtl_onhand_quantities_detail moqd,
388 mtl_status_transaction_control mtc
389 WHERE moqd.organization_id = l_xfer_org_id
390 AND moqd.inventory_item_id = p_inventory_item_id
391 AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
392 AND moqd.lpn_id = p_xfer_lpn_id
393 AND moqd.status_id = mtc.status_id
394 AND mtc.transaction_type_id = p_trx_type_id
395 AND mtc.is_allowed = 2
396 ) ;
397
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 l_allow_transaction := 'Y' ;
401 inv_trx_util_pub.TRACE('txn allowed or not: .. 90 Reached here l_allow_transaction:'||l_allow_transaction,'Material Status', 9);
402
403 END ;
404
405
406 ELSIF p_xfer_lpn_id is NULL THEN -- transfer lpn id is NULL so making the qty as loose at destination
407
408 BEGIN
409 inv_trx_util_pub.TRACE('txn allowed or not: .. 100 verifying at dest, xfer lpn id is null','Material Status', 9);
410 SELECT 'N'
411 INTO l_allow_transaction
412 FROM dual
413 WHERE EXISTS
414 (SELECT 1
415 FROM mtl_onhand_quantities_detail moqd,
416 mtl_status_transaction_control mtc
417 WHERE moqd.organization_id = l_xfer_org_id
418 AND moqd.inventory_item_id = p_inventory_item_id
419 AND moqd.subinventory_code = l_xfer_sub_code
420 AND nvl(moqd.locator_id,-999) = nvl(l_xfer_locator_id,-999) --6974887 --could be null for INV sub-inventories(doubt)
421 AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@') --only place where used xfer lot
422 AND moqd.status_id = mtc.status_id
423 AND mtc.transaction_type_id = p_trx_type_id
424 AND mtc.is_allowed = 2
425 -- AND ROWNUM = 1
426 ) ;
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 l_allow_transaction := 'Y' ;
430 inv_trx_util_pub.TRACE('txn allowed or not: .. 110 ','Material Status', 9);
431 END ;
432
433 END IF;
434 END IF;
435
436
437 ELSIF p_inventory_item_id is NULL and p_fromlpn_id is NOT NULL THEN -- p_inventory_item_id NULL so passing full LPN which just need to be checked at source
438
439 BEGIN
440 inv_trx_util_pub.TRACE('txn allowed or not: .. 120 full LPN Case','Material Status', 9);
441 --checking whether the source material ALLOWS/DISALLOWS the transaction.
442 --if l_serial_status_enabled is 1 then it is serial controlled item
443 l_counter := 0;
444 FOR l_cur_wlc IN c_lpn_item LOOP
445 l_serial_controlled := 0;
446 l_serial_status_enabled := 0;
447
448 IF inv_cache.set_item_rec(p_organization_id, l_cur_wlc.inventory_item_id) THEN
449 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.0','Material Status', 9);
450 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
451 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.1 serial controlled','Material Status', 9);
452 l_serial_controlled := 1; -- Item is serial controlled
453 END IF;
454
455 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
456 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.2 serial status enabled-true','Material Status', 9);
457 l_serial_status_enabled := 1;
458 END IF;
459 END IF;
460 --if it is serial controlled and its serial status is enabled
461 IF l_serial_controlled = 1 AND l_serial_status_enabled=1 THEN
462 l_counter := l_counter + 1;
463 inv_trx_util_pub.TRACE('txn allowed or not: .. Exitting from the loop','Material Status', 9);
464 EXIT; --exit even one item is serial controlled and its serial status enabled
465 END IF;
466 END LOOP;
467 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.3 Serial Count:'||l_counter,'Material Status', 9);
468 --check for all non-serial items
469
470 SELECT 'N'
471 INTO l_allow_transaction
472 FROM dual
473 WHERE EXISTS
474 (SELECT 1
475 FROM mtl_onhand_quantities_detail moqd,
476 mtl_status_transaction_control mtc
477 WHERE moqd.organization_id = p_organization_id
478 AND moqd.subinventory_code = p_sub_code
479 AND moqd.locator_id = p_locator_id
480 -- AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
481 AND moqd.lpn_id IN
482 (SELECT lpn_id
483 FROM wms_license_plate_numbers plpn
484 START WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = prior lpn_id
485 AND plpn.organization_id = p_organization_id
486 )
487 AND moqd.status_id = mtc.status_id
488 AND mtc.transaction_type_id = p_trx_type_id
489 AND mtc.is_allowed = 2
490 -- AND ROWNUM = 1
491 ) ;
492
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 l_allow_transaction := 'Y' ;
496 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.4 came here','Material Status', 9);
497
498 END;
499 --if still the transaction is allowed and the lpn contains serials
500 BEGIN
501 IF l_counter <> 0 AND l_allow_transaction = 'Y' THEN --if l_counter !=0 then serials exist in the LPN
502 inv_trx_util_pub.TRACE('txn allowed or not: .. 120.5 Serials Exist so need to check MSN Status','Material Status', 9);
503 SELECT 'N'
504 INTO l_allow_transaction
505 FROM dual
506 WHERE EXISTS
507 (SELECT 1
508 FROM wms_lpn_contents wlc ,
509 mtl_serial_numbers msn,
510 mtl_status_transaction_control mtc
511 WHERE wlc.parent_lpn_id IN
512 (SELECT lpn_id
513 FROM wms_license_plate_numbers START
514 WITH lpn_id = p_fromlpn_id CONNECT BY parent_lpn_id = PRIOR lpn_id
515 AND organization_id = p_organization_id
516 )
517 AND wlc.serial_summary_entry = 1
518 AND wlc.parent_lpn_id = msn.lpn_id
519 AND msn.status_id = mtc.status_id
520 AND mtc.transaction_type_id = p_trx_type_id
521 AND mtc.is_allowed = 2
522 );
523 END IF;
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 l_allow_transaction := 'Y' ;
527 inv_trx_util_pub.TRACE('txn allowed or not: .. 130 came here','Material Status', 9);
528
529 END;
530 --checking whether the destination LPN ALLOWS/DISALLOWS the transaction.
531 IF l_allow_transaction = 'Y' AND p_xfer_lpn_id IS NOT NULL AND l_xferlpn_context <> 5 THEN
532 inv_trx_util_pub.TRACE('txn allowed or not: .. 140 verifying at dest-- xfer lpn allows/not','Material Status', 9);
533 BEGIN
534 SELECT 'N'
535 INTO l_allow_transaction
536 FROM dual
537 WHERE EXISTS
538 (SELECT 1
539 FROM mtl_onhand_quantities_detail moqd,
540 mtl_status_transaction_control mtc
541 WHERE moqd.organization_id = l_xfer_org_id
542 AND moqd.lpn_id = p_xfer_lpn_id
543 AND moqd.status_id = mtc.status_id
544 AND mtc.transaction_type_id = p_trx_type_id
545 AND mtc.is_allowed = 2
546 ) ;
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 l_allow_transaction := 'Y' ;
551 inv_trx_util_pub.TRACE('txn allowed or not: .. 150 came here','Material Status', 9);
552 END ;
553 END IF; --l_allow_transaction = 'Y' AND p_xfer_lpn_id condition
554
555
556
557 END IF; --p_inventory_item_id check
558 IF l_allow_transaction = 'N' THEN
559 l_validate := 2;
560 inv_trx_util_pub.TRACE('txn allowed or not: .. 160 came here','Material Status', 9);
561 END IF;
562 inv_trx_util_pub.TRACE('txn allowed or not: .. 170 returning the value l_validate'||l_validate,'Material Status', 9);
563 RETURN l_validate;
564 END IF; --p_trx_type_id check
565
566 END is_status_applicable_lpns;
567
568 /*LPN Status Project*/
569
570 FUNCTION is_trx_allowed
571 (
572 p_status_id IN NUMBER
573 , p_transaction_type_id IN NUMBER
574 , x_return_status OUT NOCOPY VARCHAR2
575 , x_msg_count OUT NOCOPY NUMBER
576 , x_msg_data OUT NOCOPY VARCHAR2
577 ) return varchar2
578 IS
579 allowed number := 1;
580 c_api_name varchar2(30) := 'is_trx_allowed';
581 BEGIN
582 -- Onhand Material Status Support : Return true if the material status profile is not enabled.
583 -- Added this check as now we are calling this method from QtyManager.
584 IF NVL(FND_PROFILE.VALUE('INV_MATERIAL_STATUS'),2) <> 1 THEN
585 RETURN 'Y';
586 END IF;
587
588 -- Onhand Material Status Support : Return true if status_id or transaction_type_id is null
589 IF p_status_id is null or p_transaction_type_id is null THEN
590 RETURN 'Y';
591 END IF;
592
593 x_return_status := fnd_api.g_ret_sts_success ;
594
595 IF p_status_id <> nvl(g_status_id,-9999) OR
596 p_transaction_type_id <> nvl(g_transaction_type_id,-9999) THEN
597 select is_allowed
598 into allowed
599 from mtl_status_transaction_control
600 where status_id = p_status_id
601 and transaction_type_id = p_transaction_type_id;
602
603 g_status_id := p_status_id;
604 g_transaction_type_id := p_transaction_type_id;
605
606 if allowed = 1 then
607 g_is_allowed := 'Y';
608 else
609 g_is_allowed := 'N';
610 end if;
611 END IF;
612
613 return nvl(g_is_allowed,'Y');
614
615 exception
616 when NO_DATA_FOUND THEN
617 --Begin bug 4536902
618 g_status_id := p_status_id;
619 g_transaction_type_id := p_transaction_type_id;
620 --End bug 4536902
621 g_is_allowed := 'Y'; --Added as it was not being set for exception case Bug#6633612
622 return 'Y';
623
624 when others then
625 x_return_status := fnd_api.g_ret_sts_unexp_error ;
626
627 if (fnd_msg_pub.check_msg_level
628 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
629 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
630 end if;
631 -- always return 'Y' when any error occurred
632 --Begin bug 4536902
633 g_status_id := p_status_id;
634 g_transaction_type_id := p_transaction_type_id;
635 --End bug 4536902
636 g_is_allowed := 'Y'; --Added as it was not being set for exception case Bug#6633612
637 return 'Y';
638 END is_trx_allowed;
639
640 /* Bug 6918409: Added a wrapper to call the is_trx_allowed function */
641 FUNCTION is_trx_allowed_wrap
642 (
643 p_status_id IN NUMBER
644 , p_transaction_type_id IN NUMBER
645 ) return varchar2
646 IS
647 allowed VARCHAR2(5) := 'Y';
648 c_api_name VARCHAR2(30) := 'is_trx_allowed_wrap';
649 l_return_status VARCHAR2(1);
650 l_msg_count NUMBER;
651 l_msg_data VARCHAR2(256);
652 BEGIN
653
654
655 allowed := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
656 p_status_id =>p_status_id
657 ,p_transaction_type_id=> p_transaction_type_id
658 ,x_return_status => l_return_status
659 ,x_msg_count => l_msg_count
660 ,x_msg_data => l_msg_data);
661
662 return nvl(allowed,'Y');
663
664 exception
665 when others then
666 return 'Y';
667 END is_trx_allowed_wrap;
668
669
670 PROCEDURE get_lot_serial_status_control
671 (
672 p_organization_id IN NUMBER
673 , p_inventory_item_id IN NUMBER
674 , x_return_status OUT NOCOPY VARCHAR2
675 , x_msg_count OUT NOCOPY NUMBER
676 , x_msg_data OUT NOCOPY VARCHAR2
677 , x_lot_status_enabled OUT NOCOPY VARCHAR2
678 , x_default_lot_status_id OUT NOCOPY NUMBER
679 , x_serial_status_enabled OUT NOCOPY VARCHAR2
680 , x_default_serial_status_id OUT NOCOPY NUMBER
681 ) IS
682 c_api_name varchar2(30) := 'get_lot_serial_status_control';
683 BEGIN
684 x_return_status := fnd_api.g_ret_sts_success ;
685
686 IF p_organization_id <> nvl(g_organization_id,-9999) OR
687 p_inventory_item_id <> nvl(g_inventory_item_id,-9999) THEN
688
689 -- Onhand Material Status Support: If status_enabled flags are null then return 'N'.
690 SELECT nvl(lot_status_enabled,'N'), Default_Lot_Status_ID,
691 nvl(serial_status_enabled,'N'), Default_serial_status_ID
692 INTO g_lot_status_enabled, g_default_lot_status_id,
693 g_serial_status_enabled, g_default_serial_status_id
694 FROM MTL_SYSTEM_ITEMS
695 WHERE organization_id = p_organization_id
696 AND inventory_item_id = p_inventory_item_id;
697 --bug3713809
698 g_organization_id := p_organization_id;
699 g_inventory_item_id := p_inventory_item_id;
700
701 /*if x_serial_status_enabled is null then
702 x_serial_status_enabled := 'Y';
703 end if;
704
705 if x_lot_status_enabled is null then
706 x_lot_status_enabled := 'Y';
707 end if;*/
708 END IF;
709
710 x_lot_status_enabled := g_lot_status_enabled;
711 x_default_lot_status_id := g_default_lot_status_id;
712 x_serial_status_enabled := g_serial_status_enabled;
713 x_default_serial_status_id := g_default_serial_status_id;
714
715 exception
716 when others then
717 x_return_status := fnd_api.g_ret_sts_unexp_error ;
718
719 if (fnd_msg_pub.check_msg_level
720 (fnd_msg_pub.g_msg_lvl_unexp_error)) then
721 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
722 end if;
723
724 -- Get message count and data
725 FND_MSG_PUB.Count_And_Get
726 ( p_count => x_msg_count
727 , p_data => x_msg_data
728 );
729
730 END get_lot_serial_status_control;
731
732 -- Onhand Material Status Support: Calling the overloaded function.
733 Function is_status_applicable(p_wms_installed IN VARCHAR2,
734 p_trx_status_enabled IN NUMBER,
735 p_trx_type_id IN NUMBER,
736 p_lot_status_enabled IN VARCHAR2,
737 p_serial_status_enabled IN VARCHAR2,
738 p_organization_id IN NUMBER,
739 p_inventory_item_id IN NUMBER,
740 p_sub_code IN VARCHAR2,
741 p_locator_id IN NUMBER,
742 p_lot_number IN VARCHAR2,
743 p_serial_number IN VARCHAR2,
744 p_object_type IN VARCHAR2)
745 return varchar2 is
746
747 p_lpn_id NUMBER := NULL;
748 l_return_status VARCHAR2(1);
749 BEGIN
750
751 if (g_debug = 1) then
752 inv_trx_util_pub.TRACE('inside non-overloaded is_status_applicable ', 'INV_MATERIAL_STATUS_GRP', 14);
753 end if;
754
755 l_return_status:= is_status_applicable(p_wms_installed ,
756 p_trx_status_enabled ,
757 p_trx_type_id ,
758 p_lot_status_enabled ,
759 p_serial_status_enabled ,
760 p_organization_id ,
761 p_inventory_item_id ,
762 p_sub_code ,
763 p_locator_id ,
764 p_lot_number ,
765 p_serial_number ,
766 p_object_type ,
767 p_lpn_id);
768
769 return l_return_status;
770
771 EXCEPTION
772 when others then
773 return 'Y';
774 END;
775
776 Function is_status_applicable(p_wms_installed IN VARCHAR2,
777 p_trx_status_enabled IN NUMBER,
778 p_trx_type_id IN NUMBER,
779 p_lot_status_enabled IN VARCHAR2,
780 p_serial_status_enabled IN VARCHAR2,
781 p_organization_id IN NUMBER,
782 p_inventory_item_id IN NUMBER,
783 p_sub_code IN VARCHAR2,
784 p_locator_id IN NUMBER,
785 p_lot_number IN VARCHAR2,
786 p_serial_number IN VARCHAR2,
787 p_object_type IN VARCHAR2,
788 p_lpn_id IN NUMBER) -- Onhand Material Status Support
789 return varchar2 is
790 l_status_id number;
791 l_return_status VARCHAR2(1);
792 l_msg_count NUMBER;
793 l_msg_data VARCHAR2(256);
794 l_trx_status_enabled number := 1;
795 l_lot_status_enabled VARCHAR2(1);
796 l_default_lot_status_id number;
797 l_serial_status_enabled VARCHAR2(1);
798 l_default_serial_status_id number;
799 l_wms_installed varchar2(30);
800
801 -- New variables for MACD Validations
802 --l_old_item_id NUMBER := FND_API.g_miss_num;
803 l_item_trackable VARCHAR2(1) := 'N';
804 l_freeze_flag VARCHAR2(1) := NULL;
805 l_trx_action_id NUMBER;
806 l_trx_source_type NUMBER;
807 l_trx_id NUMBER := NULL;
808 l_ib_cz_keys VARCHAR2(1) := 'Y';
809 l_default_status_id NUMBER; -- Onhand Material Status Support
810 count_status_id NUMBER:= -1;-- Onhand Material Status Support
811 l_default_item_status_id NUMBER; -- Onhand Material Status Support
812 l_locator_id NUMBER; -- Onhand Material Status Support
813
814 /* Bug 6918409 */
815 l_serial_controlled NUMBER:=0;
816 l_count NUMBER:=0;
817
818 BEGIN
819 --INCONV kkillams
820 -- Bug 4121999
821 IF NVL(FND_PROFILE.VALUE('INV_MATERIAL_STATUS'),2) <> 1 THEN
822 RETURN 'Y';
823 END IF;
824 --END INCONV kkillams
825
826 -- Onhand Material Status Support
827 if (inv_cache.set_org_rec(p_organization_id)) then
828 l_default_status_id := inv_cache.org_rec.default_status_id;
829 end if;
830
831
832 -- Call the new CSI Function at the start and disregard if
833 -- WMS is installed or not
834
835 -- Check to see if the item is trackable. Only run this for Each Item and
836 -- Not every time. Only call the MACD Validations for Serial Checks
837 -- p_object_type = S
838
839 --Bug #5367711
840 --Caching the values of item_id, freeze_flag, item_trackable to avoid redundant calls
841 IF p_object_type = 'S' THEN
842 BEGIN
843 IF p_inventory_item_id <> NVL(g_old_item_id,-9999) THEN
844 SELECT NVL(msi.comms_nl_trackable_flag,'N')
845 INTO g_item_trackable
846 FROM mtl_system_items msi,
847 mtl_parameters mp
848 WHERE msi.inventory_item_id = p_inventory_item_id
849 AND msi.enabled_flag = 'Y'
850 AND nvl (msi.start_date_active, sysdate) <= sysdate
851 AND nvl (msi.end_date_active, sysdate+1) > sysdate
852 AND msi.organization_id = mp.master_organization_id
853 AND mp.organization_id = p_organization_id;
854
855 g_old_item_id := p_inventory_item_id;
856 END IF;
857 EXCEPTION
858 WHEN others THEN
859 g_item_trackable := 'N';
860 END;
861
862 -- Get the source and action for the transaction type being passed
863 -- in and use that to decide wheather or not to execute the MACD
864 -- Validations.
865
866 BEGIN
867 --IF l_trx_id IS NULL THEN
868 IF ( (p_trx_type_id IS NOT NULL) AND
869 (p_trx_type_id <> NVL(g_isa_trx_type_id,-9999))
870 ) THEN
871 SELECT transaction_action_id,
872 transaction_source_type_id
873 INTO g_transaction_action_id,
874 g_transaction_source_type_id
875 FROM mtl_transaction_types mtt
876 WHERE mtt.transaction_type_id = p_trx_type_id;
877 g_isa_trx_type_id := p_trx_type_id;
878 ELSE
879 g_transaction_action_id := NULL;
880 g_transaction_source_type_id := NULL;
881 END IF;
882 EXCEPTION
883 WHEN others THEN
884 g_transaction_action_id := FND_API.g_miss_num;
885 g_transaction_source_type_id := FND_API.g_miss_num;
886 END;
887
888 -- Check to see if IB is active Only run this 1 time per session
889
890 BEGIN
891 IF g_freeze_flag IS NULL THEN
892 SELECT nvl(freeze_flag, 'N')
893 INTO g_freeze_flag
894 FROM csi_install_parameters
895 WHERE rownum = 1;
896
897 END IF;
898 EXCEPTION
899 WHEN others THEN
900 g_freeze_flag := 'N';
901 END;
902
903 IF g_item_trackable = 'Y' AND
904 g_freeze_flag = 'Y' AND
905 g_transaction_action_id = 27 AND
906 g_transaction_source_type_id <> 12 THEN -- RMA
907
908 l_ib_cz_keys := csi_utility_grp.check_inv_serial_cz_keys(p_inventory_item_id,
909 p_organization_id,
910 p_serial_number);
911
912 IF l_ib_cz_keys = 'Y' THEN
913 Return 'N';
914 ELSE
915 Return 'Y';
916 END IF;
917
918 END IF;
919
920 END IF; -- p_object_type = S
921
922
923
924 --INCONV kkillams
925 /*
926 l_wms_installed := p_wms_installed;
927 if p_wms_installed is null then
928 IF not wms_install.check_install(l_return_status,
929 l_msg_count,
930 l_msg_data,
931 NULL ) then
932 return 'Y';
933 ELSE l_wms_installed := 'TRUE';
934 END IF;
935 end if;
936
937 if UPPER(l_wms_installed) <>'TRUE' then
938 return 'Y';
939 end if;
940 */
941 --END INVCONV kkillams
942 -- In case user doesn't pass p_trx_status_enabled
943 if p_trx_status_enabled is null then
944 if p_trx_type_id <> nvl(g_isa_trx_type_id,-9999) THEN
945 select status_control_flag
946 into g_isa_trx_status_enabled
947 from mtl_transaction_types
948 where transaction_type_id = p_trx_type_id;
949 g_isa_trx_type_id := p_trx_type_id;
950 end if;
951 l_trx_status_enabled := g_isa_trx_status_enabled;
952 if l_trx_status_enabled = 2 then return 'Y'; end if;
953 elsif p_trx_status_enabled = 2 then
954 return 'Y';
955 end if;
956
957 -- In case user doesn't pass p_lot_status_enabled and
958 -- p_serial_status_enabled
959 l_lot_status_enabled := p_lot_status_enabled;
960 l_serial_status_enabled := p_serial_status_enabled;
961 -- Onhand Material Status Support: We need lot and serial control even if the object type is not lot or serial.
962 if ( (p_lot_status_enabled is null) or
963 (p_serial_status_enabled is null) )then
964 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
965 p_organization_id =>p_organization_id
966 , p_inventory_item_id =>p_inventory_item_id
967 , x_return_status =>l_return_status
968 , x_msg_count =>l_msg_count
969 , x_msg_data =>l_msg_data
970 , x_lot_status_enabled =>l_lot_status_enabled
971 , x_default_lot_status_id =>l_default_lot_status_id
972 , x_serial_status_enabled =>l_serial_status_enabled
973 , x_default_serial_status_id =>l_default_serial_status_id);
974
975 -- Bug 6829224 : Should check for lot_status_enabled only if the org is NOT tracking the
976 -- material status at onhand level.
977 if ( p_object_type = 'O' and l_lot_status_enabled = 'N' and l_default_status_id is null) or
978 ( p_object_type = 'S' and l_serial_status_enabled = 'N') then
979 return 'Y';
980 end if;
981 elsif ( p_object_type = 'O' and p_lot_status_enabled = 'N' and l_default_status_id is null) or
982 ( p_object_type = 'S' and p_serial_status_enabled = 'N') then
983 return 'Y';
984 end if;
985
986 -- Onhand Material Status Support
987 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
988 l_default_item_status_id := inv_cache.item_rec.default_material_status_id;
989 end if;
990
991 /* Bug 6918409 */
992 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
993 if (inv_cache.item_rec.serial_number_control_code not in (1,6)) then
994 l_serial_controlled := 1;
995 end if;
996 end if;
997 /* Bug 6918409 */
998
999 if (g_debug = 1) then
1000 inv_trx_util_pub.TRACE('default org status id ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1001 end if;
1002
1003 if (p_sub_code is not null ) and (p_object_type = 'Z' or p_object_type = 'A') then
1004 if(( p_organization_id <> nvl(g_isa_organization_id,-9999) ) or
1005 ( p_sub_code <> nvl(g_isa_sub_code,-9999)) or
1006 ( l_default_status_id is not null) -- Onhand Material Status Support: No caching if status is at onhand level
1007 )THEN
1008 -- Onhand Material Status Support : If status is tracked at the onhand level,
1009 -- then retrieve status_id from MOQD.
1010 if (l_default_status_id is null) then
1011 select status_id
1012 into g_isa_sub_status_id
1013 from mtl_secondary_inventories
1014 where organization_id = p_organization_id
1015 and secondary_inventory_name = p_sub_code;
1016 g_isa_organization_id := p_organization_id;
1017 g_isa_sub_code := p_sub_code;
1018 else
1019 if (p_inventory_item_id is not null) then
1020 begin
1021
1022 if (g_debug = 1) then
1023 inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1024 end if;
1025
1026 select nvl(status_id, -1)
1027 into g_isa_sub_status_id
1028 from mtl_onhand_quantities_detail
1029 where inventory_item_id = p_inventory_item_id
1030 and organization_id = p_organization_id
1031 and subinventory_code = p_sub_code
1032 and lot_number is null
1033 and locator_id is null
1034 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1035 and rownum = 1 ;
1036
1037 if (g_debug = 1) then
1038 inv_trx_util_pub.TRACE('sub status id ' || g_isa_sub_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1039 end if;
1040
1041 g_isa_organization_id := p_organization_id;
1042 g_isa_sub_code := p_sub_code;
1043 exception
1044 when no_data_found then
1045 -- If no onhand record exists and the item is not locator/lot/serial controlled then
1046 -- we need to check whether the subinv status allows the transaction as that is going to be
1047 -- the status in MOQD except for the transfer transactions.
1048 if (g_debug = 1) then
1049 inv_trx_util_pub.TRACE('locator control '|| get_locator_control(p_organization_id, p_inventory_item_id, p_sub_code), 'INV_MATERIAL_STATUS_GRP', 14);
1050 end if;
1051
1052 /* Bug 6918409 */
1053 if(l_lot_status_enabled = 'Y' or l_serial_controlled = 1
1054 or get_locator_control(p_organization_id, p_inventory_item_id, p_sub_code) <> 1) then
1055 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1056 if(p_object_type <> 'A' ) then
1057 return 'Y';
1058 end if;
1059 else
1060 if (get_action_id(p_trx_type_id) in (2,3,21,28)) then -- Need to use action Id.
1061
1062 if (g_debug = 1) then
1063 inv_trx_util_pub.TRACE('returning Y as its a transfer transaction', 'INV_MATERIAL_STATUS_GRP', 14);
1064 end if;
1065
1066 return 'Y';
1067 else
1068 /* Bug 6918409 */
1069 l_count := 0;
1070 if (l_lot_status_enabled <> 'Y' ) then
1071 if (g_debug = 1) then
1072 inv_trx_util_pub.TRACE('lot status is not enabled', 'INV_MATERIAL_STATUS_GRP', 14);
1073 end if;
1074 /* Bug 6975416 : Modified the SQL for 10g
1075 * compliance
1076 */
1077 begin
1078 select 1
1079 into l_count
1080 from mtl_onhand_quantities_detail moqd
1081 where moqd.inventory_item_id = p_inventory_item_id
1082 and moqd.organization_id = p_organization_id
1083 and moqd.subinventory_code = p_sub_code
1084 and INV_MATERIAL_STATUS_GRP.is_trx_allowed_wrap(
1085 moqd.status_id
1086 ,p_trx_type_id) = 'Y'
1087 and rownum = 1;
1088 exception
1089 when others then
1090 l_count := 0;
1091 end;
1092
1093 if (g_debug = 1) then
1094 inv_trx_util_pub.TRACE('sub, l_count: '||l_count, 'INV_MATERIAL_STATUS_GRP', 14);
1095 end if;
1096
1097 if (l_count = 1 and p_object_type <> 'A') then
1098 return 'Y';
1099 end if;
1100 end if;
1101
1102 if (l_count <> 1 ) then
1103 if (l_default_item_status_id is not null) then
1104 g_isa_sub_status_id := l_default_item_status_id;
1105 elsif inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1106 g_isa_sub_status_id := inv_cache.tosub_rec.status_id;
1107 end if;
1108 if (g_debug = 1) then
1109 inv_trx_util_pub.TRACE('sub, l_count is 0, sub_status_id: '||g_isa_sub_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1110 end if;
1111 end if;
1112 end if;
1113 end if;
1114 end;
1115
1116 if (g_isa_sub_status_id is null or g_isa_sub_status_id = 0 or g_isa_sub_status_id = -1)
1117 and p_object_type <> 'A' then
1118
1119 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1120 if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1121
1122 if (g_debug = 1) then
1123 inv_trx_util_pub.TRACE('sub, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1124 end if;
1125
1126 FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1127 FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1128 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
1129 FND_MESSAGE.SET_TOKEN('SUB', p_sub_code);
1130 FND_MESSAGE.SET_TOKEN('LOC_ID', p_locator_id );
1131 FND_MESSAGE.SET_TOKEN('LOT', p_lot_number);
1132 FND_MESSAGE.SET_TOKEN('LPN_ID', p_lpn_id);
1133 FND_MSG_PUB.ADD;
1134
1135 return 'N';
1136
1137 elsif ((inv_cache.item_rec.serial_number_control_code not in (1,6)) and g_isa_sub_status_id = -1) then
1138 return 'Y';
1139 end if;
1140 end if;
1141 end if;
1142 else
1143 if (g_debug = 1) then
1144 inv_trx_util_pub.TRACE('Item id is null for sub', 'INV_MATERIAL_STATUS_GRP', 14);
1145 end if;
1146
1147 begin
1148 select count(distinct status_id)
1149 into count_status_id
1150 from mtl_onhand_quantities_detail
1151 where organization_id = p_organization_id
1152 and subinventory_code = p_sub_code
1153 and lot_number is null
1154 and locator_id is null
1155 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999);
1156
1157 if ( count_status_id = 1) then
1158 select status_id
1159 into g_isa_sub_status_id
1160 from mtl_onhand_quantities_detail
1161 where organization_id = p_organization_id
1162 and subinventory_code = p_sub_code
1163 and lot_number is null
1164 and locator_id is null
1165 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1166 and rownum = 1 ;
1167 else
1168 g_isa_organization_id := p_organization_id;
1169 g_isa_sub_code := p_sub_code;
1170 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1171 if(p_object_type <> 'A' ) then
1172 return 'Y';
1173 end if;
1174 end if;
1175
1176 g_isa_organization_id := p_organization_id;
1177 g_isa_sub_code := p_sub_code;
1178 exception
1179 when no_data_found then
1180 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1181 if(p_object_type <> 'A' ) then
1182 return 'Y';
1183 end if;
1184 end;
1185 end if;
1186
1187 end if;
1188 end if;
1189 l_status_id := g_isa_sub_status_id;
1190
1191 if (g_debug = 1) then
1192 inv_trx_util_pub.TRACE('sub, l_status_id ' || l_status_id ||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1193 end if;
1194
1195 l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1196 p_status_id =>l_status_id
1197 ,p_transaction_type_id=> p_trx_type_id
1198 ,x_return_status => l_return_status
1199 ,x_msg_count => l_msg_count
1200 ,x_msg_data => l_msg_data);
1201
1202 if (g_debug = 1) then
1203 inv_trx_util_pub.TRACE('sub, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1204 end if;
1205
1206 if (p_object_type = 'Z') or (p_object_type = 'A' and
1207 l_return_status = 'N') then
1208 if( l_return_status = 'N' ) then
1209 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_NOT_APP');
1210 FND_MESSAGE.SET_TOKEN('TOKEN', 'Subinventory');
1211 FND_MSG_PUB.ADD;
1212 end if;
1213 return(l_return_status);
1214 end if;
1215 end if;
1216
1217 if (p_locator_id is not null) and (p_object_type = 'L' or
1218 p_object_type = 'A' ) then
1219 if(( p_organization_id <> nvl(g_isa_organization_id,-9999) ) or
1220 ( p_locator_id <> nvl(g_isa_locator_id,-9999)) or
1221 ( l_default_status_id is not null) -- Onhand Material Status Support: No caching if status is at onhand level
1222 ) THEN
1223 -- Onhand Material Status Support : If status is tracked at the onhand level,
1224 -- then retrieve status_id from MOQD.
1225 if (l_default_status_id is null) then
1226 select status_id
1227 into g_isa_loc_status_id
1228 from mtl_item_locations
1229 where inventory_location_id = p_locator_id
1230 and organization_id = p_organization_id;
1231
1232 g_isa_organization_id := p_organization_id;
1233 g_isa_locator_id := p_locator_id;
1234 else
1235 if (p_inventory_item_id is not null) then
1236 begin
1237
1238 if (g_debug = 1) then
1239 inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1240 end if;
1241
1242 select nvl(status_id, -1)
1243 into g_isa_loc_status_id
1244 from mtl_onhand_quantities_detail
1245 where inventory_item_id = p_inventory_item_id
1246 and organization_id = p_organization_id
1247 and lot_number is null
1248 and locator_id = p_locator_id
1249 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1250 and rownum = 1 ;
1251
1252 if (g_debug = 1) then
1253 inv_trx_util_pub.TRACE('loc status id ' || g_isa_loc_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1254 end if;
1255
1256 g_isa_organization_id := p_organization_id;
1257 g_isa_locator_id := p_locator_id;
1258 exception
1259 when no_data_found then
1260 /* Bug 6918409 */
1261 if(l_lot_status_enabled = 'Y' or l_serial_controlled = 1) then
1262 -- If object_type is A then we need to validate other objects(lot, serial) before returning
1263 if(p_object_type <> 'A' ) then
1264 return 'Y';
1265 end if;
1266 else
1267 if (get_action_id(p_trx_type_id) in (2,3,21,28)) then -- Need to change to action ID.
1268 return 'Y';
1269 else
1270 /* Bug 6918409 */
1271 l_count := 0;
1272 if (l_lot_status_enabled <> 'Y' ) then
1273 if (g_debug = 1) then
1274 inv_trx_util_pub.TRACE('loc, lot status is not enabled', 'INV_MATERIAL_STATUS_GRP', 14);
1275 end if;
1276 /* Bug 6975416 : Modified the SQL for 10g
1277 * compliance
1278 */
1279 begin
1280 select 1
1281 into l_count
1282 from mtl_onhand_quantities_detail moqd
1283 where moqd.inventory_item_id = p_inventory_item_id
1284 and moqd.organization_id = p_organization_id
1285 and moqd.locator_id = p_locator_id
1286 and INV_MATERIAL_STATUS_GRP.is_trx_allowed_wrap(
1287 moqd.status_id
1288 ,p_trx_type_id) = 'Y'
1289 and rownum = 1;
1290 exception
1291 when others then
1292 l_count := 0;
1293 end;
1294
1295 if (g_debug = 1) then
1296 inv_trx_util_pub.TRACE('loc, l_count: '||l_count, 'INV_MATERIAL_STATUS_GRP', 14);
1297 end if;
1298
1299 if (l_count = 1 and p_object_type <> 'A') then
1300 return 'Y';
1301 end if;
1302 end if;
1303
1304 if (l_count <> 1 ) then
1305 if (l_default_item_status_id is not null) then
1306 g_isa_loc_status_id := l_default_item_status_id;
1307 elsif inv_cache.set_loc_rec(p_organization_id, p_locator_id) then
1308 if (inv_cache.loc_rec.status_id is not null) then
1309 g_isa_loc_status_id := inv_cache.loc_rec.status_id;
1310 else -- Locator is dynamic
1311 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1312 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1313 g_isa_loc_status_id := inv_cache.tosub_rec.default_loc_status_id;
1314 else
1315 g_isa_loc_status_id := inv_cache.tosub_rec.status_id;
1316 end if;
1317 elsif p_sub_code is null and p_object_type <> 'A' then -- Bug 6918409
1318 return 'Y';
1319 end if;
1320 end if;
1321 else
1322 if p_sub_code is null and p_object_type <> 'A' then -- Bug 6787033
1323 return 'Y';
1324 elsif inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1325 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1326 g_isa_loc_status_id := inv_cache.tosub_rec.default_loc_status_id;
1327 else
1328 g_isa_loc_status_id := inv_cache.tosub_rec.status_id;
1329 end if;
1330 end if;
1331 end if;
1332 end if;
1333
1334 end if;
1335 end if;
1336 end;
1337
1338 if (g_isa_loc_status_id is null or g_isa_loc_status_id = 0 or g_isa_loc_status_id = -1)
1339 and p_object_type <> 'A' then
1340 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1341 if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1342
1343 if (g_debug = 1) then
1344 inv_trx_util_pub.TRACE('Loc, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1345 end if;
1346
1347 FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1348 FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1349 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
1350 FND_MESSAGE.SET_TOKEN('SUB', p_sub_code);
1351 FND_MESSAGE.SET_TOKEN('LOC_ID', p_locator_id );
1352 FND_MESSAGE.SET_TOKEN('LOT', p_lot_number);
1353 FND_MESSAGE.SET_TOKEN('LPN_ID', p_lpn_id);
1354 FND_MSG_PUB.ADD;
1355 return 'N';
1356 elsif ((inv_cache.item_rec.serial_number_control_code not in (1,6)) and g_isa_loc_status_id = -1) then
1357 return 'Y';
1358 end if;
1359 end if;
1360 end if;
1361 else
1362
1363 if (g_debug = 1) then
1364 inv_trx_util_pub.TRACE('Item id is null for loc', 'INV_MATERIAL_STATUS_GRP', 14);
1365 end if;
1366
1367 begin
1368 select count(distinct status_id)
1369 into count_status_id
1370 from mtl_onhand_quantities_detail
1371 where organization_id = p_organization_id
1372 and lot_number is null
1373 and locator_id = p_locator_id
1374 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999);
1375
1376 if ( count_status_id = 1) then
1377 select status_id
1378 into g_isa_loc_status_id
1379 from mtl_onhand_quantities_detail
1380 where organization_id = p_organization_id
1381 and locator_id = p_locator_id
1382 and lot_number is null
1383 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1384 and rownum = 1 ;
1385 else
1386 g_isa_organization_id := p_organization_id;
1387 g_isa_locator_id := p_locator_id;
1388 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1389 if(p_object_type <> 'A' ) then
1390 return 'Y';
1391 end if;
1392 end if;
1393
1394 g_isa_organization_id := p_organization_id;
1395 g_isa_locator_id := p_locator_id;
1396 exception
1397 when no_data_found then
1398 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1399 if(p_object_type <> 'A' ) then
1400 return 'Y';
1401 end if;
1402 end;
1403 end if;
1404
1405 end if;
1406 end if;
1407 l_status_id := g_isa_loc_status_id;
1408
1409 if (g_debug = 1) then
1410 inv_trx_util_pub.TRACE('loc, l_status_id ' || l_status_id ||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1411 end if;
1412
1413 l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1414 p_status_id =>l_status_id
1415 ,p_transaction_type_id=> p_trx_type_id
1416 ,x_return_status => l_return_status
1417 ,x_msg_count => l_msg_count
1418 ,x_msg_data => l_msg_data);
1419
1420 if (g_debug = 1) then
1421 inv_trx_util_pub.TRACE('loc, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1422 end if;
1423
1424 if (p_object_type = 'L') or (p_object_type = 'A' and
1425 l_return_status = 'N') then
1426 if( l_return_status = 'N' ) then
1427 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_NOT_APP');
1428 FND_MESSAGE.SET_TOKEN('TOKEN', 'Locator');
1429 FND_MSG_PUB.ADD;
1430 end if;
1431 return(l_return_status);
1432 end if;
1433 end if;
1434
1435 -- Onhand Material Status Support: If org is tracking status at onhand level, then we should not check
1436 -- for lot_status_enabled
1437 if ((p_lot_number is not null) and ((l_lot_status_enabled = 'Y') or (l_default_status_id is not null))
1438 ) and (p_object_type = 'O' or p_object_type = 'A') then
1439 if( p_organization_id <> nvl(g_organization_id, -9999) OR
1440 p_inventory_item_id <> nvl(g_inventory_item_id, -9999) OR
1441 p_lot_number <> nvl(g_isa_lot_number, '@@@') OR
1442 (NOT(inv_cache.is_pickrelease) OR (inv_cache.is_pickrelease IS NULL)) OR
1443 ( l_default_status_id is not null) -- Onhand Material Status Support: No caching if status is at onhand level
1444 ) THEN --Bug 5457445
1445
1446 -- Onhand Material Status Support : If status is tracked at the onhand level,
1447 -- then retrieve status_id from MOQD.
1448 if (l_default_status_id is null) then
1449 select status_id
1450 INTO g_isa_lot_number_status_id
1451 from mtl_lot_numbers
1452 where inventory_item_id = p_inventory_item_id
1453 and organization_id = p_organization_id
1454 and lot_number = p_lot_number;
1455
1456 g_isa_lot_number := p_lot_number;
1457 else
1458 begin
1459
1460 if (g_debug = 1 ) then
1461 inv_trx_util_pub.TRACE('Inside lot ', 'INV_MATERIAL_STATUS_GRP', 14);
1462 inv_trx_util_pub.TRACE('org ' || p_organization_id, 'INV_MATERIAL_STATUS_GRP', 14);
1463 inv_trx_util_pub.TRACE('item ' || p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 14);
1464 inv_trx_util_pub.TRACE('sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1465 inv_trx_util_pub.TRACE('loc ' || p_locator_id, 'INV_MATERIAL_STATUS_GRP', 14);
1466 inv_trx_util_pub.TRACE('lot ' || p_lot_number, 'INV_MATERIAL_STATUS_GRP', 14);
1467 inv_trx_util_pub.TRACE('lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
1468 end if;
1469
1470 --Adding following locator id logic to support locator_id = -1
1471 --which is being passed from some mobile pages for null value.
1472 l_locator_id := p_locator_id;
1473 IF(l_locator_id = -1 ) THEN
1474 l_locator_id := NULL;
1475 END IF;
1476
1477 select nvl(status_id, -1)
1478 into g_isa_lot_number_status_id
1479 from mtl_onhand_quantities_detail
1480 where inventory_item_id = p_inventory_item_id
1481 and organization_id = p_organization_id
1482 and subinventory_code = p_sub_code
1483 and nvl(locator_id, -9999) = nvl(l_locator_id, -9999)
1484 and lot_number = p_lot_number
1485 and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
1486 and rownum = 1 ;
1487
1488 if (g_debug = 1 ) then
1489 inv_trx_util_pub.TRACE('lot status id ' || g_isa_lot_number_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1490 end if;
1491
1492 g_isa_lot_number := p_lot_number;
1493 exception
1494 when no_data_found then
1495 /* Bug 6918409 */
1496 if(l_serial_controlled = 1) then
1497 -- If object_type is A then we need to validate other objects(loc, lot, serial) before returning
1498 if(p_object_type <> 'A' ) then
1499 return 'Y';
1500 end if;
1501 else
1502 if (get_action_id(p_trx_type_id) in (2,3,21,28)) then -- Need to put action IDs.
1503 return 'Y';
1504 else
1505 if (l_lot_status_enabled = 'Y') then
1506 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1507 g_isa_lot_number_status_id := inv_cache.item_rec.default_lot_status_id;
1508 end if;
1509 else
1510 /* Bug 6918409 */
1511 if (l_default_item_status_id is not null) then
1512 g_isa_lot_number_status_id := l_default_item_status_id;
1513 elsif (get_locator_control(p_organization_id, p_inventory_item_id, p_sub_code) <> 1) then
1514 if inv_cache.set_loc_rec(p_organization_id, l_locator_id) then
1515 if (inv_cache.loc_rec.status_id is not null) then
1516 g_isa_lot_number_status_id := inv_cache.loc_rec.status_id;
1517 else -- Locator is dynamic
1518 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1519 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1520 g_isa_lot_number_status_id := inv_cache.tosub_rec.default_loc_status_id;
1521 else
1522 g_isa_lot_number_status_id := inv_cache.tosub_rec.status_id;
1523 end if;
1524 elsif p_sub_code is null and p_object_type <> 'A' then
1525 return 'Y';
1526 end if;
1527 end if;
1528 else
1529 if p_sub_code is null and p_object_type <> 'A' then -- Bug 6787033
1530 return 'Y';
1531 elsif inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1532 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1533 g_isa_lot_number_status_id := inv_cache.tosub_rec.default_loc_status_id;
1534 else
1535 g_isa_lot_number_status_id := inv_cache.tosub_rec.status_id;
1536 end if;
1537 end if;
1538 end if;
1539 elsif ( p_sub_code is not null) then
1540 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1541 g_isa_lot_number_status_id := inv_cache.tosub_rec.status_id;
1542 end if;
1543 else
1544 return 'Y';
1545 end if;
1546
1547 end if;
1548 end if;
1549 end if;
1550 end;
1551
1552 if (g_isa_lot_number_status_id is null or g_isa_lot_number_status_id = 0 or g_isa_lot_number_status_id = -1)
1553 and p_object_type <> 'A' then
1554 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1555 if (inv_cache.item_rec.serial_number_control_code in (1,6)) then
1556
1557 if (g_debug = 1) then
1558 inv_trx_util_pub.TRACE('lot, status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1559 end if;
1560
1561 FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1562 FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1563 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
1564 FND_MESSAGE.SET_TOKEN('SUB', p_sub_code);
1565 FND_MESSAGE.SET_TOKEN('LOC_ID', p_locator_id );
1566 FND_MESSAGE.SET_TOKEN('LOT', p_lot_number);
1567 FND_MESSAGE.SET_TOKEN('LPN_ID', p_lpn_id);
1568 FND_MSG_PUB.ADD;
1569 return 'N';
1570 elsif ((inv_cache.item_rec.serial_number_control_code not in (1,6)) and g_isa_lot_number_status_id = -1) then
1571 return 'Y';
1572 end if;
1573 end if;
1574 end if;
1575
1576 end if;
1577 end if;
1578 l_status_id := g_isa_lot_number_status_id;
1579
1580 if (g_debug = 1) then
1581 inv_trx_util_pub.TRACE('lot, l_status_id ' || l_status_id ||',trx type id '||p_trx_type_id, 'INV_MATERIAL_STATUS_GRP', 14);
1582 end if;
1583
1584 l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1585 p_status_id =>l_status_id
1586 ,p_transaction_type_id=> p_trx_type_id
1587 ,x_return_status => l_return_status
1588 ,x_msg_count => l_msg_count
1589 ,x_msg_data => l_msg_data);
1590
1591 if (g_debug = 1) then
1592 inv_trx_util_pub.TRACE('lot, l return status ' || l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
1593 end if;
1594
1595 if (p_object_type = 'O') or (p_object_type = 'A' and
1596 l_return_status = 'N') then
1597 if( l_return_status = 'N') then
1598 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_NOT_APP');
1599 FND_MESSAGE.SET_TOKEN('TOKEN', 'Lot Number');
1600 FND_MSG_PUB.ADD;
1601 end if;
1602 return(l_return_status);
1603 end if;
1604 end if;
1605
1606 if (p_serial_number is not null) and (l_serial_status_enabled = 'Y')
1607 and (p_object_type = 'S' or p_object_type = 'A') then
1608 select status_id
1609 into l_status_id
1610 from mtl_serial_numbers
1611 where inventory_item_id = p_inventory_item_id
1612 and current_organization_id = p_organization_id
1613 and serial_number = p_serial_number;
1614
1615 l_return_status := INV_MATERIAL_STATUS_GRP.is_trx_allowed(
1616 p_status_id =>l_status_id
1617 ,p_transaction_type_id=> p_trx_type_id
1618 ,x_return_status => l_return_status
1619 ,x_msg_count => l_msg_count
1620 ,x_msg_data => l_msg_data);
1621 if (p_object_type = 'S') or (p_object_type = 'A' and
1622 l_return_status = 'N') then
1623 if( l_return_status = 'N' ) then
1624 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_NOT_APP');
1625 FND_MESSAGE.SET_TOKEN('TOKEN', 'Serial Number');
1626 FND_MSG_PUB.ADD;
1627 end if;
1628 return(l_return_status);
1629 end if;
1630 end if;
1631
1632 return 'Y';
1633
1634 exception
1635 when others then
1636 return 'Y';
1637 END is_status_applicable;
1638
1639 PROCEDURE update_status
1640 ( p_api_version_number IN NUMBER
1641 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1642 , x_return_status OUT NOCOPY VARCHAR2
1643 , x_msg_count OUT NOCOPY NUMBER
1644 , x_msg_data OUT NOCOPY VARCHAR2
1645 , p_update_method IN NUMBER
1646 , p_status_id IN NUMBER
1647 , p_organization_id IN NUMBER
1648 , p_inventory_item_id IN NUMBER:=NULL
1649 , p_sub_code IN VARCHAR2:=NULL
1650 , p_locator_id IN NUMBER:=NULL
1651 , p_lot_number IN VARCHAR2:=NULL
1652 , p_serial_number IN VARCHAR2:=NULL
1653 , p_to_serial_number IN VARCHAR2:=NULL
1654 , p_object_type IN VARCHAR2
1655 , p_update_reason_id IN NUMBER:=NULL
1656 , p_lpn_id IN NUMBER:=NULL -- Onhand Material Status Support
1657 , p_initial_status_flag IN VARCHAR2:='N' -- Onhand Material Status Support
1658 ) IS
1659 l_api_version_number CONSTANT NUMBER := 1.0;
1660 l_api_name CONSTANT VARCHAR2(30) := 'update_status';
1661 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1662 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
1663 BEGIN
1664 --
1665 -- Standard call to check for call compatibility
1666 IF NOT fnd_api.compatible_api_call(l_api_version_number
1667 , p_api_version_number
1668 , l_api_name
1669 , G_PKG_NAME
1670 ) THEN
1671 RAISE fnd_api.g_exc_unexpected_error;
1672 END IF;
1673 --
1674 -- Initialize message list.
1675 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1676 fnd_msg_pub.initialize;
1677 END IF;
1678
1679 l_status_rec.organization_id := p_organization_id;
1680 l_status_rec.inventory_item_id := p_inventory_item_id;
1681 l_status_rec.lot_number := p_lot_number;
1682 l_status_rec.serial_number := p_serial_number;
1683 l_status_rec.to_serial_number := p_to_serial_number;
1684 l_status_rec.update_method := p_update_method;
1685 l_status_rec.status_id := p_status_id;
1686 l_status_rec.zone_code := p_sub_code;
1687 l_status_rec.locator_id := p_locator_id;
1688 l_status_rec.update_reason_id := p_update_reason_id;
1689 -- Onhand Material Status Support
1690 l_status_rec.lpn_id := p_lpn_id; -- Setting the value of lpn_id
1691 l_status_rec.initial_status_flag := p_initial_status_flag; -- Setting the value of initial_status_flag
1692
1693
1694 INV_MATERIAL_STATUS_PUB.update_status(
1695 p_api_version_number => p_api_version_number
1696 , p_init_msg_lst => p_init_msg_lst
1697 , x_return_status =>l_return_status
1698 , x_msg_count => x_msg_count
1699 , x_msg_data => x_msg_data
1700 , p_object_type => p_object_type
1701 , p_status_rec => l_status_rec );
1702
1703 x_return_status := l_return_status;
1704
1705 END update_status;
1706
1707 --Function added for Bug# 2879164
1708 FUNCTION loc_valid_for_item
1709 ( p_loc_id NUMBER
1710 , p_org_id NUMBER
1711 , p_inventory_item_id NUMBER
1712 , p_sub_code VARCHAR2
1713 ) RETURN VARCHAR2 IS
1714 l_temp NUMBER := -1;
1715 l_restrict_loc_code NUMBER := 2;
1716 BEGIN
1717
1718 --Bug 5500255, if p_loc_id is null, should return Y
1719 IF (p_loc_id is NULL) THEN
1720 RETURN 'Y';
1721 END IF;
1722
1723
1724 SELECT restrict_locators_code
1725 INTO l_restrict_loc_code
1726 FROM mtl_system_items
1727 WHERE organization_id = p_org_id
1728 AND inventory_item_id = p_inventory_item_id;
1729
1730 IF (l_restrict_loc_code = 2) THEN
1731 RETURN 'Y';
1732 ELSE
1733 SELECT count(*)
1734 INTO l_temp
1735 FROM mtl_item_locations a, mtl_secondary_locators b
1736 WHERE b.organization_id = p_org_id
1737 AND b.inventory_item_id = p_inventory_item_id
1738 AND b.subinventory_code = p_sub_code
1739 AND a.inventory_location_id = b.secondary_locator
1740 AND a.organization_id = b.organization_id
1741 AND a.inventory_location_id = p_loc_id;
1742 END IF;
1743
1744 IF (l_temp = 0) THEN
1745 RETURN 'N';
1746 ELSE
1747 RETURN 'Y';
1748 END IF;
1749 --Bug 3328939:Added the exception block to handle the case when an
1750 --exception is raised from the select queries in this function.
1751 exception
1752 when others then
1753 return 'Y';
1754 END loc_valid_for_item;
1755
1756 --Function added for Bug# 2879164
1757 FUNCTION sub_valid_for_item(p_org_id NUMBER:=NULL,
1758 p_inventory_item_id NUMBER:=NULL,
1759 p_sub_code VARCHAR2:=NULL)
1760 RETURN VARCHAR2 IS
1761 l_temp NUMBER := -1;
1762 l_restrict_sub_code NUMBER := 2;
1763 BEGIN
1764
1765 SELECT restrict_subinventories_code
1766 INTO l_restrict_sub_code
1767 FROM mtl_system_items
1768 WHERE organization_id = p_org_id
1769 AND inventory_item_id = p_inventory_item_id;
1770
1771 IF (l_restrict_sub_code = 2) THEN
1772 RETURN 'Y';
1773 ELSE
1774 SELECT count(*)
1775 INTO l_temp
1776 FROM mtl_secondary_inventories a, mtl_item_sub_inventories b
1777 WHERE a.organization_id = p_org_id
1778 AND b.inventory_item_id = p_inventory_item_id
1779 AND a.organization_id = b.organization_id
1780 AND a.secondary_inventory_name = b.secondary_inventory
1781 AND a.secondary_inventory_name = p_sub_code;
1782
1783 IF (l_temp = 0) THEN
1784 RETURN 'N';
1785 ELSE
1786 RETURN 'Y';
1787 END IF;
1788 END IF;
1789 --Bug 3328939:Added the exception block to handle the case when an
1790 --exception is raised from the select queries in this function.
1791 exception
1792 when others then
1793 return 'Y';
1794 END sub_valid_for_item;
1795
1796 -- On-hand Material Status support
1797 Function get_default_status(p_organization_id IN NUMBER,
1798 p_inventory_item_id IN NUMBER,
1799 p_sub_code IN VARCHAR2,
1800 p_loc_id IN NUMBER :=NULL,
1801 p_lot_number IN VARCHAR2 :=NULL,
1802 p_lpn_id IN NUMBER := NULL,
1803 p_transaction_action_id IN NUMBER := NULL,
1804 p_src_status_id IN NUMBER := NULL,
1805 p_lock_id IN NUMBER := 0,
1806 p_header_id IN NUMBER :=NULL)
1807 RETURN NUMBER IS
1808
1809 l_default_status_id NUMBER := 1; -- Status: Active
1810 l_default_org_status_id NUMBER := 0;
1811 c_api_name varchar2(30) := 'get_default_status';
1812 l_serial_controlled NUMBER := 0;
1813 BEGIN
1814
1815 if (g_debug = 1) then
1816 inv_trx_util_pub.TRACE('Inside get default status ', 'INV_MATERIAL_STATUS_GRP', 14);
1817 inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
1818 inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' action ' || p_transaction_action_id || ' src ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1819 inv_trx_util_pub.TRACE('lock id '|| p_lock_id || ' header id '||p_header_id, 'INV_MATERIAL_STATUS_GRP', 14);
1820 end if;
1821
1822 if inv_cache.set_org_rec(p_organization_id) then
1823 l_default_org_status_id := inv_cache.org_rec.default_status_id;
1824 end if;
1825
1826 if (g_debug = 1) then
1827 inv_trx_util_pub.TRACE('default org status ' || l_default_org_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1828 end if;
1829
1830 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1831 if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
1832 l_serial_controlled := 1; -- Item is serial controlled
1833 end if;
1834 end if;
1835
1836 if (l_default_org_status_id is null) then -- Org is not tracking status at onhand level
1837 return null;
1838 else
1839 IF p_lpn_id is null then /*LPN Status Project */
1840 SELECT nvl(status_id, -1)
1841 INTO l_default_status_id
1842 FROM MTL_ONHAND_QUANTITIES_DETAIL
1843 WHERE inventory_item_id = p_inventory_item_id
1844 AND organization_id = p_organization_id
1845 AND subinventory_code = p_sub_code
1846 AND nvl( locator_id, -9999) =nvl( p_loc_id, -9999)
1847 AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
1848 --AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999) /*LPN Status Project */
1849 AND rownum = 1;
1850 ELSE
1851 SELECT nvl(status_id, -1)
1852 INTO l_default_status_id
1853 FROM MTL_ONHAND_QUANTITIES_DETAIL
1854 WHERE inventory_item_id = p_inventory_item_id
1855 AND organization_id = p_organization_id
1856 AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
1857 AND lpn_id = p_lpn_id /*LPN Status Project */
1858 AND rownum = 1;
1859 END IF ; /*LPN Status Project */
1860
1861 if (g_debug = 1) then
1862 inv_trx_util_pub.TRACE('default status in MOQD ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1863 end if;
1864
1865 if (l_default_status_id = -1) then
1866 if (l_serial_controlled = 0) then
1867
1868 if (g_debug = 1) then
1869 inv_trx_util_pub.TRACE('status is null in MOQD for non-serial controlled item', 'INV_MATERIAL_STATUS_GRP', 14);
1870 end if;
1871
1872 FND_MESSAGE.SET_NAME('INV', 'INV_NULL_MOQD_STATUS');
1873 FND_MESSAGE.SET_TOKEN('ORG_ID', p_organization_id);
1874 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
1875 FND_MESSAGE.SET_TOKEN('SUB', p_sub_code);
1876 FND_MESSAGE.SET_TOKEN('LOC_ID', p_loc_id );
1877 FND_MESSAGE.SET_TOKEN('LOT', p_lot_number);
1878 FND_MESSAGE.SET_TOKEN('LPN_ID', p_lpn_id);
1879 FND_MSG_PUB.ADD;
1880 else
1881 return null;
1882 end if;
1883 end if;
1884
1885 if (g_debug = 1) then
1886 inv_trx_util_pub.TRACE(' 1 default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1887 end if;
1888
1889 return l_default_status_id;
1890 end if;
1891
1892 EXCEPTION
1893 WHEN NO_DATA_FOUND THEN
1894
1895 --The records need to be locked to avoid comingling of status if multiple workers are
1896 --running in parallel.
1897 --Revision is passed as null
1898 --Issuereceipt is passed as 1 since we want the locking to occur irrespective of onhand
1899 --p_lock_id is only passed from QtyManager, all other calls to the defaulting logic do not
1900 --pass p_lock_id and p_header_id
1901
1902 if (p_lock_id <> 0 and INV_TABLE_LOCK_PVT.lock_onhand_records(p_organization_id,p_inventory_item_id,null
1903 ,p_lot_number,p_sub_code,p_loc_id,1,p_header_id)
1904 ) then
1905 if (g_debug = 1) then
1906 inv_trx_util_pub.TRACE('Locked the MOQD record', 'INV_MATERIAL_STATUS_GRP', 14);
1907 end if;
1908 else
1909 if (g_debug = 1) then
1910 inv_trx_util_pub.TRACE('Unbale to lock MOQD ', 'INV_MATERIAL_STATUS_GRP', 14);
1911 end if;
1912 end if;
1913
1914 --If onhand record doesnt exist and if it's a transfer transaction then return
1915 --the source onhand record's status_id as the status_id of the source record
1916 --needs to be carried over to the new destination record.
1917
1918 If (p_transaction_action_id is not null and p_transaction_action_id in (2,3,28,50,51,52) ) then --ADDED 50,51,52 FOR LPN STATUS PROJECT
1919 If(p_src_status_id is not null) then
1920
1921 if (g_debug = 1) then
1922 inv_trx_util_pub.TRACE('src status ex ' || p_src_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
1923 end if;
1924 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1925 -- the table : mtl_material_status_history
1926 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1927 ,p_lpn_id, p_src_status_id, p_lock_id);
1928
1929 return p_src_status_id;
1930 End if;
1931 end if;
1932
1933 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
1934 if (l_serial_controlled <> 0) then
1935 return null;
1936 elsif (inv_cache.item_rec.lot_status_enabled = 'Y') then
1937 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1938 -- the table : mtl_material_status_history
1939 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1940 ,p_lpn_id, inv_cache.item_rec.default_lot_status_id, p_lock_id);
1941
1942 return inv_cache.item_rec.default_lot_status_id;
1943 elsif (inv_cache.item_rec.default_material_status_id is not null) then
1944 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1945 -- the table : mtl_material_status_history
1946 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1947 ,p_lpn_id, inv_cache.item_rec.default_material_status_id, p_lock_id);
1948
1949 return inv_cache.item_rec.default_material_status_id;
1950 end if;
1951 end if;
1952
1953 if p_loc_id is not null then
1954 if inv_cache.set_loc_rec(p_organization_id, p_loc_id) then
1955 if (inv_cache.loc_rec.status_id is not null) then
1956 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1957 -- the table : mtl_material_status_history
1958 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1959 ,p_lpn_id, inv_cache.loc_rec.status_id, p_lock_id);
1960
1961 return inv_cache.loc_rec.status_id;
1962 else -- Locator is dynamic
1963 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1964 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1965 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1966 -- the table : mtl_material_status_history
1967 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1968 ,p_lpn_id, inv_cache.tosub_rec.default_loc_status_id, p_lock_id);
1969
1970 return inv_cache.tosub_rec.default_loc_status_id;
1971 end if;
1972 end if;
1973 end if;
1974 else -- Locator is dynamic
1975 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1976 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
1977 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1978 -- the table : mtl_material_status_history
1979 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1980 ,p_lpn_id, inv_cache.tosub_rec.default_loc_status_id, p_lock_id);
1981
1982 return inv_cache.tosub_rec.default_loc_status_id;
1983 else
1984 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1985 -- the table : mtl_material_status_history
1986 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
1987 ,p_lpn_id, inv_cache.tosub_rec.status_id, p_lock_id);
1988
1989 return inv_cache.tosub_rec.status_id;
1990 end if;
1991 end if;
1992 end if;
1993 end if;
1994
1995 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
1996 if (inv_cache.tosub_rec.status_id is not null) then
1997 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
1998 -- the table : mtl_material_status_history
1999 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
2000 ,p_lpn_id, inv_cache.tosub_rec.status_id, p_lock_id);
2001
2002 return inv_cache.tosub_rec.status_id;
2003 end if;
2004 end if;
2005
2006 if inv_cache.set_org_rec(p_organization_id) then
2007 -- Bug 6798024 : Calling the insert procedure to insert the status of the new onhand record into
2008 -- the table : mtl_material_status_history
2009 insert_status_history(p_organization_id, p_inventory_item_id, p_sub_code, p_loc_id, p_lot_number
2010 ,p_lpn_id, inv_cache.org_rec.default_status_id, p_lock_id);
2011
2012 return inv_cache.org_rec.default_status_id;
2013 end if;
2014
2015 WHEN OTHERS THEN
2016
2017 if (g_debug = 1) then
2018 inv_trx_util_pub.TRACE('Exception default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2019 end if;
2020
2021 return l_default_status_id;
2022
2023 END get_default_status;
2024
2025 -- On-hand Material Status support, Bug 6798024
2026 Procedure insert_status_history(p_organization_id IN NUMBER,
2027 p_inventory_item_id IN NUMBER,
2028 p_sub_code IN VARCHAR2,
2029 p_loc_id IN NUMBER :=NULL,
2030 p_lot_number IN VARCHAR2 :=NULL,
2031 p_lpn_id IN NUMBER := NULL,
2032 p_status_id IN NUMBER := NULL,
2033 p_lock_id IN NUMBER := 0)
2034 IS
2035 c_api_name varchar2(30) := 'insert_status_history';
2036 l_update_method NUMBER := 2;
2037 l_api_version_number NUMBER := 1.0;
2038 l_init_msg_lst VARCHAR2(5) := 'F';
2039 l_initial_Status_Flag VARCHAR2(4) := 'Y';
2040 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2041 l_msg_count NUMBER;
2042 l_msg_data VARCHAR2(240);
2043
2044 BEGIN
2045
2046 if (g_debug = 1) then
2047 inv_trx_util_pub.TRACE('Inside insert status history ', 'INV_MATERIAL_STATUS_GRP', 14);
2048 inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2049 inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id || ' status id ' || p_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2050 inv_trx_util_pub.TRACE('lock id '|| p_lock_id, 'INV_MATERIAL_STATUS_GRP', 14);
2051 end if;
2052
2053
2054 if (p_lock_id <> 0) then
2055 update_status(l_api_version_number, l_init_msg_lst, l_return_status ,l_msg_count
2056 ,l_msg_data ,l_update_method ,p_status_id ,p_organization_id
2057 ,p_inventory_item_id ,p_sub_code ,p_loc_id ,p_lot_number
2058 ,NULL ,NULL ,'Q' ,NULL ,p_lpn_id, l_initial_status_flag);
2059 end if;
2060
2061 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2062 RAISE fnd_api.g_exc_unexpected_error;
2063 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2064 RAISE fnd_api.g_exc_error;
2065 END IF;
2066
2067 EXCEPTION
2068
2069 WHEN OTHERS THEN
2070
2071 if (g_debug = 1) then
2072 inv_trx_util_pub.TRACE('Exception in insert_status_history, l_ret_status: '|| l_return_status, 'INV_MATERIAL_STATUS_GRP', 14);
2073 inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_count: '|| l_msg_count, 'INV_MATERIAL_STATUS_GRP', 14);
2074 inv_trx_util_pub.TRACE('Exception in insert_status_history, l_msg_data: '||l_msg_data, 'INV_MATERIAL_STATUS_GRP', 14);
2075 end if;
2076
2077 END insert_status_history;
2078
2079 -- On-hand Material Status support
2080 -- Defaulting logic for the concurrent program
2081 Function get_default_status_conc(p_organization_id IN NUMBER,
2082 p_inventory_item_id IN NUMBER,
2083 p_sub_code IN VARCHAR2,
2084 p_loc_id IN NUMBER :=NULL,
2085 p_lot_number IN VARCHAR2 :=NULL,
2086 p_lpn_id IN NUMBER := NULL)
2087 RETURN NUMBER IS
2088
2089 l_default_status_id NUMBER := 1; -- Status: Active
2090 l_default_org_status_id NUMBER := 0;
2091 c_api_name varchar2(30) := 'get_default_status_conc';
2092 l_serial_controlled NUMBER := 0;
2093 BEGIN
2094
2095 if (g_debug = 1) then
2096 inv_trx_util_pub.TRACE('Inside get default status conc ', 'INV_MATERIAL_STATUS_GRP', 14);
2097 inv_trx_util_pub.TRACE('org id ' ||p_organization_id || ' Item id ' || p_inventory_item_id || ' sub ' || p_sub_code, 'INV_MATERIAL_STATUS_GRP', 14);
2098 inv_trx_util_pub.TRACE('loc '|| p_loc_id || ' lot ' || p_lot_number || ' lpn ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 14);
2099 end if;
2100
2101 if inv_cache.set_org_rec(p_organization_id) then
2102 l_default_org_status_id := inv_cache.org_rec.default_status_id;
2103 end if;
2104
2105 l_default_status_id := l_default_org_status_id;
2106
2107 if (g_debug = 1) then
2108 inv_trx_util_pub.TRACE('default org status ' || l_default_org_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2109 end if;
2110
2111 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
2112 if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
2113 l_serial_controlled := 1; -- Item is serial controlled
2114 end if;
2115 end if;
2116
2117 if (l_default_org_status_id is null) then
2118
2119 if inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) then
2120 if (l_serial_controlled <> 0) then -- serial
2121 return null;
2122 elsif (inv_cache.item_rec.lot_status_enabled = 'Y') then -- lot
2123 return inv_cache.item_rec.default_lot_status_id;
2124 elsif (inv_cache.item_rec.default_material_status_id is not null) then -- item
2125 return inv_cache.item_rec.default_material_status_id;
2126 end if;
2127 end if;
2128
2129 if p_loc_id is not null then
2130 if inv_cache.set_loc_rec(p_organization_id, p_loc_id) then
2131 if (inv_cache.loc_rec.status_id is not null) then
2132 return inv_cache.loc_rec.status_id;
2133 else -- Locator is dynamic
2134 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
2135 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
2136 return inv_cache.tosub_rec.default_loc_status_id;
2137 end if;
2138 end if;
2139 end if;
2140 else -- Locator is dynamic
2141 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
2142 if (inv_cache.tosub_rec.default_loc_status_id is not null) then
2143 return inv_cache.tosub_rec.default_loc_status_id;
2144 else
2145 return inv_cache.tosub_rec.status_id;
2146 end if;
2147 end if;
2148 end if;
2149 end if;
2150
2151 if inv_cache.set_tosub_rec(p_organization_id, p_sub_code) then
2152 if (inv_cache.tosub_rec.status_id is not null) then
2153 return inv_cache.tosub_rec.status_id;
2154 end if;
2155 end if;
2156
2157 if inv_cache.set_org_rec(p_organization_id) then
2158 return inv_cache.org_rec.default_status_id;
2159 end if;
2160
2161 end if;
2162
2163 if (g_debug = 1) then
2164 inv_trx_util_pub.TRACE(' 1 default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2165 end if;
2166
2167 return l_default_status_id;
2168
2169 EXCEPTION
2170
2171 WHEN OTHERS THEN
2172
2173 if (g_debug = 1) then
2174 inv_trx_util_pub.TRACE('Exception default status returned ' || l_default_status_id, 'INV_MATERIAL_STATUS_GRP', 14);
2175 end if;
2176
2177 return l_default_status_id;
2178
2179 END get_default_status_conc;
2180
2181 --Function added for Onhand Material Status Support
2182 FUNCTION get_locator_control
2183 ( p_org_id NUMBER
2184 , p_inventory_item_id NUMBER
2185 , p_sub_code VARCHAR2
2186 ) RETURN NUMBER IS
2187 l_loc_control NUMBER := 1;
2188 BEGIN
2189
2190 -- Bug 6828620 : Added the NVLs
2191 if(nvl(g_organization_id, -9999) <> nvl(p_org_id, -9999)
2192 or nvl(g_inventory_item_id, -9999) <> nvl(p_inventory_item_id, -9999)
2193 or nvl(g_sub_code, '@@@@') <> nvl(p_sub_code, '@@@@')) then
2194
2195 SELECT (decode(P.STOCK_LOCATOR_CONTROL_CODE,4,
2196 decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE,S.LOCATOR_TYPE),
2197 P.STOCK_LOCATOR_CONTROL_CODE))
2198 INTO l_loc_control
2199 FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
2200 WHERE I.INVENTORY_ITEM_ID = p_inventory_item_id
2201 AND I.ORGANIZATION_ID = p_org_id
2202 AND S.SECONDARY_INVENTORY_NAME = p_sub_code
2203 AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
2204 AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
2205 AND P.ORGANIZATION_ID = I.ORGANIZATION_ID;
2206
2207 g_organization_id := p_org_id;
2208 g_inventory_item_id := p_inventory_item_id;
2209 g_sub_code := p_sub_code;
2210
2211 g_locator_control := l_loc_control;
2212
2213 end if;
2214
2215 return nvl(g_locator_control,1);
2216
2217 exception
2218 when others then
2219 return 1;
2220 END get_locator_control;
2221
2222 --Function added for Onhand Material Status Support
2223 FUNCTION get_action_id( p_trx_type_id NUMBER)
2224 RETURN NUMBER IS
2225
2226 l_action_id NUMBER := -1;
2227 BEGIN
2228
2229 select transaction_action_id
2230 into l_action_id
2231 from mtl_transaction_types
2232 where transaction_type_id = p_trx_type_id;
2233
2234 return l_action_id;
2235
2236 exception
2237 when others then
2238 return -1;
2239 END get_action_id;
2240
2241 --Bug #6633612, Adding following Procedure for onhand status support project
2242 PROCEDURE get_onhand_status_id
2243 ( p_organization_id IN NUMBER
2244 ,p_inventory_item_id IN NUMBER
2245 ,p_subinventory_code IN VARCHAR2
2246 ,p_locator_id IN NUMBER
2247 ,p_lot_number IN VARCHAR2
2248 ,p_lpn_id IN NUMBER
2249 ,x_onhand_status_id OUT NOCOPY NUMBER )
2250
2251 IS
2252 l_organization_id NUMBER := p_organization_id;
2253 l_inventory_item_id NUMBER := p_inventory_item_id;
2254 l_subinventory_code VARCHAR2(80) := p_subinventory_code ;
2255 l_locator_id NUMBER := p_locator_id ;
2256 l_lot_number VARCHAR2(80) := p_lot_number ;
2257 l_lpn_id NUMBER := p_lpn_id ;
2258 l_onhand_status_id NUMBER ;
2259
2260 BEGIN
2261 IF (g_debug = 1) then
2262 inv_trx_util_pub.TRACE('Inside get_onhand_status_id' , 'INV_MATERIAL_STATUS_GRP', 9);
2263 END IF;
2264 BEGIN
2265 SELECT NVL( status_id ,-1 )
2266 INTO l_onhand_status_id
2267 FROM mtl_onhand_quantities_detail
2268 WHERE inventory_item_id = l_inventory_item_id
2269 AND organization_id = l_organization_id
2270 AND subinventory_code = l_subinventory_code
2271 AND NVL(locator_id, -9999) = NVL(l_locator_id,-9999)
2272 AND NVL(lot_number,'@@@@') = NVL(l_lot_number,'@@@@')
2273 AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
2274 --AND ((l_lpn_id is NULL) OR (lpn_id = l_lpn_id ))
2275 AND rownum = 1;
2276
2277 EXCEPTION
2278 WHEN NO_DATA_FOUND THEN
2279 l_onhand_status_id := 0 ;
2280 END;
2281
2282 x_onhand_status_id := l_onhand_status_id ;
2283
2284 END get_onhand_status_id ;
2285
2286
2287 --Bug #6633612, Adding following Procedure for onhand status support project
2288 PROCEDURE check_move_diff_status(
2289 p_org_id IN NUMBER
2290 , p_inventory_item_id IN NUMBER
2291 , p_subinventory_code IN VARCHAR2
2292 , p_locator_id IN NUMBER
2293 , p_transfer_org_id IN NUMBER
2294 , p_transfer_subinventory IN VARCHAR2
2295 , p_transfer_locator_id IN NUMBER
2296 , p_lot_number IN VARCHAR2
2297 , p_transaction_action_id IN NUMBER
2298 , p_object_type IN VARCHAR2
2299 , p_lpn_id IN NUMBER
2300 , p_demand_src_header_id IN NUMBER
2301 , p_revision IN VARCHAR2
2302 , x_return_status OUT NOCOPY VARCHAR2
2303 , x_msg_count OUT NOCOPY NUMBER
2304 , x_msg_data OUT NOCOPY VARCHAR2
2305 , x_post_action OUT NOCOPY VARCHAR2
2306 ) IS
2307 c_api_name varchar2(30) := 'check_move_diff_status';
2308 l_allow_different_status NUMBER;
2309 l_org_id NUMBER;
2310 l_transfer_org_id NUMBER;
2311 l_lot_status_id NUMBER;
2312
2313 l_lot_control_code_source NUMBER;
2314 l_serial_control_code_source NUMBER;
2315 l_lot_source_status varchar2(1);
2316 l_revision_control_code_destin NUMBER;
2317
2318 l_default_source_status_id NUMBER;
2319 l_default_source_status varchar2(1);
2320
2321 l_default_destin_status_id NUMBER;
2322 l_default_destin_status varchar2(1);
2323 l_lot_destin_status varchar2(1);
2324 l_lot_control_code_destin NUMBER;
2325 l_serial_control_code_destin NUMBER;
2326 l_grade_code VARCHAR2(150);
2327
2328 l_onhand_source_status_id NUMBER;
2329 l_onhand_destin_status_id NUMBER;
2330 l_locator_control_code NUMBER;
2331
2332 l_go BOOLEAN := TRUE;
2333 l_sqoh NUMBER;
2334 l_srqoh NUMBER;
2335 l_sqr NUMBER;
2336 l_sqs NUMBER;
2337 l_satt NUMBER;
2338 l_satr NUMBER;
2339 l_qoh NUMBER;
2340 l_rqoh NUMBER;
2341 l_qr NUMBER;
2342 l_qs NUMBER;
2343 l_att NUMBER;
2344 l_atr NUMBER;
2345 l_return_status VARCHAR2(1) ;
2346 l_msg_count NUMBER;
2347 l_msg_data VARCHAR2(2000);
2348 l_revision_control BOOLEAN;
2349 l_serial_control BOOLEAN;
2350 BEGIN
2351 x_return_status := fnd_api.g_ret_sts_success ;
2352 x_post_action := 'N' ;
2353 --First get the value of move different status parameter.
2354 --Printing all input parameters to debug file
2355
2356 if (g_debug = 1) then
2357 inv_trx_util_pub.TRACE('inside check_move_diff: p_org_id = ' || p_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2358 inv_trx_util_pub.TRACE('inside check_move_diff: p_inventory_item_id = '|| p_inventory_item_id, 'INV_MATERIAL_STATUS_GRP', 9);
2359 inv_trx_util_pub.TRACE('inside check_move_diff: p_subinventory_code = ' || p_subinventory_code, 'INV_MATERIAL_STATUS_GRP', 9);
2360 inv_trx_util_pub.TRACE('inside check_move_diff: p_locator_id = '|| p_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2361 inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_org_id = ' || p_transfer_org_id, 'INV_MATERIAL_STATUS_GRP', 9);
2362 inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_subinventory = '|| p_transfer_subinventory, 'INV_MATERIAL_STATUS_GRP', 9);
2363 inv_trx_util_pub.TRACE('inside check_move_diff: p_transfer_locator_id = ' || p_transfer_locator_id, 'INV_MATERIAL_STATUS_GRP', 9);
2364 inv_trx_util_pub.TRACE('inside check_move_diff: p_lot_number = '|| p_lot_number, 'INV_MATERIAL_STATUS_GRP', 9);
2365 inv_trx_util_pub.TRACE('inside check_move_diff: p_transaction_action_id = ' || p_transaction_action_id, 'INV_MATERIAL_STATUS_GRP', 9);
2366 inv_trx_util_pub.TRACE('inside check_move_diff: p_object_type = '|| p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2367 inv_trx_util_pub.TRACE('inside check_move_diff: p_lpn_id = ' || p_lpn_id, 'INV_MATERIAL_STATUS_GRP', 9);
2368 inv_trx_util_pub.TRACE('inside check_move_diff: p_demand_src_header_id = '|| p_demand_src_header_id, 'INV_MATERIAL_STATUS_GRP', 9);
2369 inv_trx_util_pub.TRACE('inside check_move_diff: p_revision = ' || p_revision, 'INV_MATERIAL_STATUS_GRP', 9);
2370 end if;
2371
2372
2373 l_org_id := p_org_id ;
2374 IF p_transaction_action_id IN (2 ,28,50,51,52) THEN
2375 l_transfer_org_id := p_org_id;
2376 ELSIF p_transaction_action_id IN ( 3 ,21) THEN
2377 l_transfer_org_id := p_transfer_org_id;
2378 END IF;
2379
2380 BEGIN
2381 SELECT allow_different_status
2382 INTO l_allow_different_status
2383 FROM mtl_parameters
2384 WHERE organization_id = l_transfer_org_id ;
2385 EXCEPTION
2386 WHEN NO_DATA_FOUND THEN
2387 l_allow_different_status := 1;
2388 END;
2389
2390 IF (p_transfer_subinventory IS NOT NULL OR p_transaction_action_id = 21 )
2391 AND NVL(l_allow_different_status , 1) <> 1
2392 AND p_transaction_action_id IN (3, 21 ,2 ,28)
2393 THEN
2394 l_locator_control_code := get_locator_control(
2395 l_transfer_org_id
2396 , p_inventory_item_id
2397 , p_transfer_subinventory);
2398
2399 --Get lot and serial control for item from source and destination orgs:
2400
2401 IF inv_cache.set_item_rec(l_org_id,p_inventory_item_id) THEN
2402 l_lot_source_status := NVL(inv_cache.item_rec.LOT_STATUS_ENABLED,'N');
2403 l_lot_control_code_source := NVL(inv_cache.item_rec.LOT_CONTROL_CODE,1);
2404 l_serial_control_code_source := NVL(inv_cache.item_rec.SERIAL_NUMBER_CONTROL_CODE,1);
2405 END IF;
2406
2407 IF inv_cache.set_item_rec(l_transfer_org_id,p_inventory_item_id) THEN
2408 l_revision_control_code_destin := NVL(inv_cache.item_rec.REVISION_QTY_CONTROL_CODE,1);
2409 l_lot_destin_status := NVL(inv_cache.item_rec.LOT_STATUS_ENABLED,'N');
2410 l_lot_control_code_destin := NVL(inv_cache.item_rec.LOT_CONTROL_CODE,1);
2411 l_serial_control_code_destin := NVL(inv_cache.item_rec.SERIAL_NUMBER_CONTROL_CODE,1);
2412 END IF;
2413
2414 if (g_debug = 1) then
2415
2416 inv_trx_util_pub.TRACE('inside check_move_diff: object_type = ' || p_object_type, 'INV_MATERIAL_STATUS_GRP', 9);
2417 inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_source_status = '||l_lot_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2418 inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_source = '||l_lot_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2419
2420 inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_source = ' || l_serial_control_code_source, 'INV_MATERIAL_STATUS_GRP', 9);
2421 inv_trx_util_pub.TRACE('inside check_move_diff: l_revision_control_code_destin = '||l_revision_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2422 inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_destin_status = '||l_lot_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2423
2424 inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_control_code_destin = ' || l_lot_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2425 inv_trx_util_pub.TRACE('inside check_move_diff: l_serial_control_code_destin= '||l_serial_control_code_destin, 'INV_MATERIAL_STATUS_GRP', 9);
2426 inv_trx_util_pub.TRACE('inside check_move_diff: l_locator_control_code = '||l_locator_control_code, 'INV_MATERIAL_STATUS_GRP', 9);
2427
2428 end if;
2429
2430 IF (
2431 (
2432 ( p_object_type = 'Z' AND p_transfer_subinventory IS NOT NULL
2433 AND NVL(l_locator_control_code,1 )= 1 )
2434 OR ( p_object_type = 'L' AND p_transfer_locator_id IS NOT NULL)
2435 )
2436 AND NVL(l_lot_control_code_source,1) <> 2
2437 )
2438 OR
2439 ( p_object_type = 'O' AND p_lot_number IS NOT NULL)
2440 THEN
2441 IF inv_cache.set_org_rec(l_org_id) THEN
2442 l_default_source_status_id := NVL(inv_cache.org_rec.default_status_id,0);
2443 IF l_default_source_status_id <> 0 THEN
2444 l_default_source_status := 'Y';
2445 ELSE
2446 l_default_source_status := 'N';
2447 END IF;
2448 END IF;
2449
2450 IF inv_cache.set_org_rec(l_transfer_org_id) THEN
2451 l_default_destin_status_id := NVL(inv_cache.org_rec.default_status_id,0);
2452 IF l_default_destin_status_id <> 0 THEN
2453 l_default_destin_status := 'Y';
2454 ELSE
2455 l_default_destin_status := 'N';
2456 END IF;
2457 END IF;
2458
2459 if (g_debug = 1) then
2460 inv_trx_util_pub.TRACE('inside check_move_diff: l_default_source_status = ' || l_default_source_status, 'INV_MATERIAL_STATUS_GRP', 9);
2461 inv_trx_util_pub.TRACE('inside check_move_diff: l_default_destin_status = ' ||l_default_destin_status, 'INV_MATERIAL_STATUS_GRP', 9);
2462 end if;
2463
2464 IF (l_default_source_status = 'Y' AND l_default_destin_status ='Y'
2465 AND NVL(l_allow_different_status,1) = 2) --(O,O) Third if
2466 -- If both the organizations are onhand status controlled
2467 -- then get the status from corresponding SKU's and compare.
2468 THEN
2469 if (g_debug = 1) then
2470 inv_trx_util_pub.TRACE('Inside O=O', 'INV_MATERIAL_STATUS_GRP', 9);
2471 end if ;
2472 IF l_serial_control_code_source IN (1,6) AND l_serial_control_code_destin IN (1,6) THEN
2473 --Get onhand status id from source org.
2474
2475 inv_material_status_grp.get_onhand_status_id
2476 ( p_organization_id => l_org_id
2477 , p_inventory_item_id => p_inventory_item_id
2478 , p_subinventory_code => p_subinventory_code
2479 , p_locator_id => p_locator_id
2480 , p_lot_number => p_lot_number
2481 , p_lpn_id => p_lpn_id
2482 , x_onhand_status_id => l_onhand_source_status_id );
2483
2484
2485 --Get onhand status id from destination org.
2486 inv_material_status_grp.get_onhand_status_id
2487 ( p_organization_id => l_transfer_org_id
2488 , p_inventory_item_id => p_inventory_item_id
2489 , p_subinventory_code => p_transfer_subinventory
2490 , p_locator_id => p_transfer_locator_id
2491 , p_lot_number => p_lot_number
2492 , p_lpn_id => p_lpn_id
2493 , x_onhand_status_id => l_onhand_destin_status_id );
2494
2495 if (g_debug = 1) then
2496 inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_source_status_id = ' || l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2497 inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '||l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2498 end if;
2499
2500
2501 IF NVL(l_onhand_source_status_id , -1) = 0 --no onhand in source org
2502 OR NVL(l_onhand_destin_status_id , -1) = 0 --no onhand in destin org
2503 OR NVL(l_onhand_source_status_id , -1) = NVL(l_onhand_destin_status_id, -1)
2504 THEN
2505 l_go := TRUE;
2506 ELSE
2507 l_go := FALSE;
2508 END IF ;
2509
2510 END IF ; --IF l_serial_control_code_source IN (1,6)
2511
2512 ELSIF ( l_lot_source_status = 'Y' AND l_default_destin_status = 'Y'
2513 AND p_object_type = 'O' AND NVL(l_allow_different_status,1) = 2)
2514 --(L ,0)
2515 THEN
2516 if (g_debug = 1) then
2517 inv_trx_util_pub.TRACE('Inside L-O', 'INV_MATERIAL_STATUS_GRP', 9);
2518 end if ;
2519 -- In source org, item is lot status enabled and destination org is onhand so
2520 -- get the lot status from source org and MOQD status from destination org
2521 IF l_serial_control_code_destin IN (1,6) THEN
2522 BEGIN
2523 SELECT status_id
2524 INTO l_lot_status_id
2525 FROM mtl_lot_numbers
2526 WHERE organization_id = l_org_id
2527 AND inventory_item_id = p_inventory_item_id
2528 AND lot_number = p_lot_number;
2529 EXCEPTION
2530 WHEN NO_DATA_FOUND THEN
2531 l_lot_status_id := 0;
2532 END;
2533
2534 inv_material_status_grp.get_onhand_status_id
2535 ( p_organization_id => l_transfer_org_id
2536 , p_inventory_item_id => p_inventory_item_id
2537 , p_subinventory_code => p_transfer_subinventory
2538 , p_locator_id => p_transfer_locator_id
2539 , p_lot_number => p_lot_number
2540 , p_lpn_id => p_lpn_id
2541 , x_onhand_status_id => l_onhand_destin_status_id );
2542
2543 if (g_debug = 1) then
2544 inv_trx_util_pub.TRACE('inside check_move_diff: l_lot_status_id = ' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2545 inv_trx_util_pub.TRACE('inside check_move_diff: l_onhand_destin_status_id = '|| l_onhand_destin_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2546 end if;
2547
2548
2549 IF NVL(l_lot_status_id, -1) = 0 -- lot doesnt exists in source org
2550 OR NVL(l_onhand_destin_status_id,-1) = 0 -- No onhand in destin org
2551 OR NVL(l_lot_status_id,-1) = NVL(l_onhand_destin_status_id,-1)
2552 THEN
2553 l_go := TRUE;
2554 ELSE
2555 l_go := FALSE;
2556 END IF ;
2557
2558 END IF ; -- IF l_serial_control_code_destin IN (1,6) THEN
2559
2560 ELSIF (l_default_source_status = 'Y' AND l_lot_destin_status = 'Y' AND p_object_type = 'O')
2561 --(O,L)
2562 THEN
2563 if (g_debug = 1) then
2564 inv_trx_util_pub.TRACE('Inside O-L', 'INV_MATERIAL_STATUS_GRP', 9);
2565 end if ;
2566 -- source org is onhand status enabled and destination is lot status enabled
2567 -- Check onhand status id from source org and lot status id from destination org
2568
2569 IF l_serial_control_code_source IN (1,6) THEN
2570 inv_material_status_grp.get_onhand_status_id
2571 ( p_organization_id => l_org_id
2572 , p_inventory_item_id => p_inventory_item_id
2573 , p_subinventory_code => p_subinventory_code
2574 , p_locator_id => p_locator_id
2575 , p_lot_number => p_lot_number
2576 , p_lpn_id => p_lpn_id
2577 , x_onhand_status_id => l_onhand_source_status_id);
2578
2579 BEGIN
2580 SELECT status_id, grade_code
2581 INTO l_lot_status_id, l_grade_code
2582 FROM mtl_lot_numbers
2583 WHERE organization_id = l_transfer_org_id
2584 AND inventory_item_id = p_inventory_item_id
2585 AND lot_number = p_lot_number;
2586 EXCEPTION
2587 WHEN NO_DATA_FOUND THEN
2588 l_lot_status_id := 0;
2589 END;
2590
2591 if (g_debug = 1) then
2592 inv_trx_util_pub.TRACE('l_onhand_source_status_id'||l_onhand_source_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2593 inv_trx_util_pub.TRACE('l_lot_status_id' || l_lot_status_id, 'INV_MATERIAL_STATUS_GRP', 9);
2594 end if ;
2595
2596 IF NVL(l_onhand_source_status_id,-1)= 0
2597 OR NVL(l_lot_status_id ,-1) = 0
2598 OR NVL(l_lot_status_id,-1) = NVL(l_onhand_source_status_id,-1)
2599 THEN
2600 l_go := TRUE ;
2601 ELSIF l_allow_different_status = 3 THEN
2602
2603 IF NVL(l_revision_control_code_destin,1) = 1 THEN
2604 l_revision_control := FALSE;
2605 ELSE
2606 l_revision_control := TRUE;
2607 END IF;
2608 IF l_serial_control_code_destin IN (1, 6) THEN
2609 l_serial_control := FALSE;
2610 ELSE
2611 l_serial_control := TRUE;
2612 END IF;
2613
2614 inv_quantity_tree_pub.query_quantities
2615 (
2616 p_api_version_number => 1.0
2617 , p_init_msg_lst => 'T'
2618 , x_return_status => l_return_status
2619 , x_msg_count => l_msg_count
2620 , x_msg_data => l_msg_data
2621 , p_organization_id => l_transfer_org_id
2622 , p_inventory_item_id => p_inventory_item_id
2623 , p_tree_mode => 1
2624 , p_is_revision_control => l_revision_control
2625 , p_is_lot_control => TRUE
2626 , p_is_serial_control => l_serial_control
2627 , p_demand_source_type_id => p_demand_src_header_id
2628 , p_revision => p_revision
2629 , p_lot_number => p_lot_number
2630 , p_subinventory_code => p_transfer_subinventory
2631 , p_locator_id => p_transfer_locator_id
2632 , p_onhand_source => 3
2633 , x_qoh => l_qoh
2634 , x_rqoh => l_rqoh
2635 , x_qr => l_qr
2636 , x_qs => l_qs
2637 , x_att => l_att
2638 , x_atr => l_atr
2639 , p_grade_code => l_GRADE_CODE
2640 , x_sqoh => l_sqoh
2641 , x_satt => l_satt
2642 , x_satr => l_satr
2643 , x_srqoh => l_srqoh
2644 , x_sqr => l_sqr
2645 , x_sqs => l_sqs
2646 , p_lpn_id => null
2647 , p_demand_source_header_id => -1
2648 , p_demand_source_line_id => -1
2649 , p_demand_source_name => -1
2650 );
2651
2652 IF l_return_status <> 'S' THEN
2653 FND_MESSAGE.set_name('INV','INV_INTERNAL_ERROR');
2654 FND_MESSAGE.set_token('token1','XACT_QTY1');
2655 fnd_msg_pub.ADD;
2656 RAISE fnd_api.g_exc_error;
2657 END IF;
2658
2659 IF nvl(l_qoh,0) = 0 THEN
2660 l_go := TRUE;
2661 x_post_action := 'Y' ; --Added for bug7418564
2662 ELSE
2663 l_go := FALSE;
2664 END IF;
2665 ELSIF l_allow_different_status = 2 THEN
2666 l_go := FALSE;
2667 END IF ; --IF NVL(l_lot_status_id ,-1) = 0 OR ..
2668
2669 END IF; --IF l_serial_control_code_source IN (1,6) THEN
2670
2671 END IF ; --Third if
2672 IF NOT l_go THEN
2673 if (g_debug = 1) then
2674 inv_trx_util_pub.TRACE('Comingling Occurs', 'INV_MATERIAL_STATUS_GRP', 9);
2675 end if ;
2676
2677 fnd_message.set_name('INV','INV_TXF_MOVE_DIFF_MAT_STAT');
2678 fnd_msg_pub.ADD;
2679 RAISE fnd_api.g_exc_error;
2680 END IF ;
2681 END IF ;--Second if
2682
2683 END IF ; --First if
2684 EXCEPTION
2685 WHEN fnd_api.g_exc_error THEN
2686 x_return_status := fnd_api.g_ret_sts_error;
2687 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2688 WHEN OTHERS THEN
2689 x_return_status := fnd_api.g_ret_sts_unexp_error;
2690 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2691 END check_move_diff_status;
2692
2693 --adding following procedure for lpn status project to get the lpn status
2694 PROCEDURE get_lpn_status
2695 (
2696 p_organization_id IN NUMBER,
2697 p_lpn_id IN NUMBER,
2698 p_sub_code IN VARCHAR2 := NULL,
2699 p_loc_id IN NUMBER := NULL,
2700 p_lpn_context IN NUMBER,
2701 x_return_status_id OUT NOCOPY NUMBER,
2702 x_return_status_code OUT NOCOPY VARCHAR2
2703 )
2704 IS
2705 l_lpn_sub VARCHAR2(30) ;
2706 l_lpn_loc NUMBER;
2707 l_lpn_context NUMBER;
2708 l_lpn_org_id NUMBER;
2709 l_def_org_status NUMBER;
2710 l_return_status_id NUMBER := NULL;
2711 l_return_status_code VARCHAR2(30) := NULL;
2712 l_counter NUMBER := 0;
2713 l_inventory_item_id NUMBER;
2714 l_lot_number NUMBER;
2715 l_lpn_id NUMBER;
2716 l_lot_control_code NUMBER;
2717 l_status_id NUMBER := NULL;
2718 l_serial_controlled NUMBER := 0;
2719 l_lot_controlled NUMBER := 0;
2720 l_serial_status_enabled NUMBEr := 0;
2721
2722 CURSOR c_lpn_item
2723 IS
2724 SELECT *
2725 FROM wms_lpn_contents wlc
2726 WHERE wlc.parent_lpn_id IN
2727 (SELECT lpn_id
2728 FROM wms_license_plate_numbers plpn
2729 start with lpn_id = p_lpn_id
2730 connect by parent_lpn_id = prior lpn_id
2731 )
2732 ORDER BY wlc.serial_summary_entry DESC ;
2733
2734 CURSOR mmtt_cur
2735 IS
2736 SELECT mmtt.transaction_temp_id , mmtt.subinventory_code ,
2737 mmtt.locator_id , mmtt.inventory_item_id ,
2738 mmtt.lpn_id , mmtt.item_lot_control_code
2739 FROM mtl_material_transactions_temp mmtt
2740 WHERE mmtt.transfer_lpn_id = p_lpn_id
2741 AND NVL(mmtt.lpn_id,-99) <> p_lpn_id
2742 AND NVL(mmtt.content_lpn_id,-99) <> p_lpn_id;
2743 CURSOR mtlt_cur(l_transaction_temp_id NUMBER)
2744 IS
2745 SELECT mtlt.lot_number
2746 FROM mtl_transaction_lots_temp mtlt
2747 where transaction_temp_id = l_transaction_temp_id;
2748 CURSOR msn_cur(l_cur_lpn_id NUMBER , l_cur_inventory_item_id NUMBER)
2749 IS
2750 SELECT msn.status_id
2751 FROM mtl_serial_numbers msn
2752 where msn.inventory_item_id = l_cur_inventory_item_id
2753 AND msn.lpn_id = l_cur_lpn_id;
2754 CURSOR msnt_cur(l_transaction_temp_id NUMBER)
2755 IS
2756 SELECT msn.status_id
2757 FROM mtl_serial_numbers msn , mtl_serial_numbers_temp msnt
2758 WHERE msnt.transaction_temp_id = l_transaction_temp_id
2759 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
2760
2761
2762
2763 BEGIN
2764 if(g_debug = 1)THEN
2765 inv_trx_util_pub.TRACE('In get_lpn_status','INV_MATERIAL_STATUS_GRP',9);
2766 END if;
2767 l_lpn_org_id := p_organization_id;
2768 l_lpn_sub := p_sub_code;
2769 l_lpn_loc := p_loc_id;
2770 l_lpn_context := p_lpn_context;
2771 IF(l_lpn_sub IS NULL or l_lpn_loc IS NULL or l_lpn_context IS NULL) THEN
2772
2773 BEGIN
2774 SELECT wlpn.organization_id , wlpn.subinventory_code , wlpn.locator_id , wlpn.lpn_context into
2775 l_lpn_org_id , l_lpn_sub ,l_lpn_loc ,l_lpn_context
2776 FROM wms_license_plate_numbers wlpn
2777 where lpn_id = p_lpn_id;
2778 EXCEPTION
2779 WHEN NO_DATA_FOUND THEN
2780 if(g_debug = 1)THEN
2781 inv_trx_util_pub.TRACE('Unable to find the LPN''INV_MATERIAL_STATUS_GRP',9);
2782 END IF;
2783 x_return_status_code := NULL;
2784 x_return_status_id :=NULL;
2785 RETURN;
2786 END;
2787 END IF;
2788 IF l_lpn_context IN (WMS_Container_PUB.LPN_CONTEXT_PREGENERATED,
2789 WMS_Container_PUB.LPN_CONTEXT_VENDOR) THEN
2790 IF(g_debug = 1)THEN
2791 inv_trx_util_pub.TRACE('LPN CONTEXT IS '||l_lpn_context||' Status should be NULL for that','INV_MATERIAL_STATUS_GRP',9);
2792 END IF;
2793 l_return_status_id := NULL; --if lpn_context is 5 or 7 lpn status should be NULL
2794 ELSIF l_lpn_context IN (WMS_Container_PUB.LPN_CONTEXT_STORES,
2795 WMS_Container_PUB.LPN_CONTEXT_INTRANSIT) THEN
2796
2797 SELECT default_status_id
2798 INTO l_def_org_status
2799 FROM mtl_parameters
2800 WHERE organization_id = l_lpn_org_id;
2801 IF(g_debug = 1)THEN
2802 inv_trx_util_pub.TRACE('LPN CONTEXT IS '||l_lpn_context|| ' Status should be default org level staus which is '||l_return_status_id,'INV_MATERIAL_STATUS_GRP',9);
2803 END IF;
2804 l_return_status_id := l_def_org_status; --If lpn_context is 4 or 6 lpn status should be derived from default org parameters
2805 ELSE
2806
2807 IF (l_lpn_context = WMS_Container_PUB.LPN_CONTEXT_PACKING )THEN--wlc don't exists for the lpn therefore checking mmtt
2808 IF(g_debug = 1)THEN
2809 inv_trx_util_pub.TRACE('WLC is not there and no child record is there for the lpn therefor querying mmtt for details','INV_MATERIAL_STATUS_GRP',9);
2810 END IF;
2811 FOR l_mmtt_cur IN mmtt_cur
2812 LOOP
2813 l_serial_status_enabled := 0;
2814 l_serial_controlled := 0;
2815 l_lot_controlled := 0;
2816 IF inv_cache.set_item_rec(l_lpn_org_id, l_mmtt_cur.inventory_item_id) THEN
2817 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
2818 l_serial_controlled := 1; -- Item is serial controlled
2819 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
2820 l_serial_status_enabled := 1;
2821 END IF;
2822 END IF;
2823 IF (inv_cache.item_rec.lot_control_code = 2) THEN
2824 l_lot_controlled := 1;
2825 END IF;
2826 END IF;
2827
2828 IF (l_lot_controlled = 1 AND l_serial_controlled = 0) THEN
2829 --item is lot controlled so need to loop through mtlt also
2830 FOR l_mtlt_cur IN mtlt_cur(l_mmtt_cur.transaction_temp_id)
2831 LOOP
2832 l_counter := l_counter + 1;
2833 l_return_status_id := INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
2834 (p_organization_id => p_organization_id,
2835 p_inventory_item_id => l_mmtt_cur.inventory_item_id,
2836 p_sub_code => l_mmtt_cur.subinventory_code,
2837 p_loc_id => l_mmtt_cur.locator_id,
2838 p_lot_number => l_mtlt_cur.lot_number,
2839 p_lpn_id => l_mmtt_cur.lpn_id,
2840 p_transaction_action_id=> NULL,
2841 p_src_status_id => NULL);
2842 IF l_counter = 1 THEN
2843 -- Assigning status for the first time
2844 l_status_id := l_return_status_id;
2845 END IF;
2846 IF NVL(l_return_status_id,-99) <> NVL(l_status_id,-99) THEN --checking current status from the first status
2847 --There are mixed status so returning -1 and exiting the loop
2848 l_return_status_id := -1;
2849 IF(g_debug = 1)THEN
2850 inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 1','INV_MATERIAL_STATUS_GRP',9);
2851 END IF;
2852 EXIT;
2853 END IF;
2854 END LOOP; --mtlt_cur loop finished
2855 ELSIF (l_serial_controlled = 1) THEN
2856 IF (l_serial_status_enabled = 1) THEN
2857 FOR l_msnt_cur IN msnt_cur(l_mmtt_cur.transaction_temp_id) LOOP
2858 l_counter := l_counter + 1;
2859 l_return_status_id := l_msnt_cur.status_id;
2860 IF l_counter = 1 THEN
2861 -- Assigning status for the first time
2862 l_status_id := l_return_status_id;
2863 END IF;
2864 IF l_return_status_id <> l_status_id THEN --checking current status from the first status
2865 --There are mixed status so returning -1 and exiting the loop
2866 l_return_status_id := -1;
2867 IF(g_debug = 1)THEN
2868 inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 2','INV_MATERIAL_STATUS_GRP',9);
2869 END IF;
2870 EXIT;
2871 END IF;
2872 END LOOP; --l_msnt_cur loop finished
2873 END IF;
2874
2875 ELSE
2876 l_counter := l_counter + 1;
2877 l_return_status_id :=
2878 INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
2879 (p_organization_id => p_organization_id,
2880 p_inventory_item_id => l_mmtt_cur.inventory_item_id,
2881 p_sub_code => l_mmtt_cur.subinventory_code,
2882 p_loc_id => l_mmtt_cur.locator_id,
2883 p_lot_number => NULL,
2884 p_lpn_id => l_mmtt_cur.lpn_id,
2885 p_transaction_action_id=> NULL,
2886 p_src_status_id => NULL);
2887
2888 IF l_counter = 1 THEN
2889 l_status_id := l_return_status_id;
2890 END IF;
2891 IF NVL(l_return_status_id,-99) <> NVL(l_status_id,-99) THEN
2892 l_return_status_id := -1;
2893
2894 END IF;
2895 END IF;
2896 IF l_return_status_id = -1 THEN
2897 EXIT ;
2898 END IF;
2899 END LOOP;--mmtt_cur loop finished
2900 END IF;
2901
2902
2903 IF(NVL(l_return_status_id ,-99)<> -1) THEN
2904 FOR l_cur_wlc IN c_lpn_item
2905 LOOP
2906 l_serial_controlled := 0;
2907 l_serial_status_enabled := 0;
2908 IF inv_cache.set_item_rec(p_organization_id, l_cur_wlc.inventory_item_id) THEN
2909 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
2910 l_serial_controlled := 1; -- Item is serial controlled
2911 END IF;
2912 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
2913 l_serial_status_enabled := 1;
2914 END IF;
2915 END IF;
2916 IF (l_serial_controlled <> 1) then
2917 l_counter := l_counter + 1;
2918 l_return_status_id :=
2919 INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
2920 (p_organization_id => p_organization_id,
2921 p_inventory_item_id => l_cur_wlc.inventory_item_id,
2922 p_sub_code => l_lpn_sub,
2923 p_loc_id => l_lpn_loc,
2924 p_lot_number => l_cur_wlc.lot_number,
2925 p_lpn_id => l_cur_wlc.parent_lpn_id,
2926 p_transaction_action_id=> NULL, p_src_status_id => NULL);
2927
2928 IF (l_counter = 1) THEN
2929 l_status_id := l_return_status_id ; --assigning it for the first to check further if all the statuses are same or not
2930 END IF;
2931 IF (NVL(l_status_id,-99) <> NVL(l_return_status_id,-99)) THEN
2932 IF(g_debug = 1)THEN
2933 inv_trx_util_pub.TRACE('lpn has item of mixed statuses so returning mixed at 3','INV_MATERIAL_STATUS_GRP',9);
2934 END IF;
2935 l_return_status_id := -1;
2936 END IF;
2937 ELSE --item is serial controlled therefor checkin msn for status
2938 IF(l_serial_status_enabled = 1) THEN
2939 FOR l_msn_cur in msn_cur(l_cur_wlc.parent_lpn_id , l_cur_wlc.inventory_item_id) loop
2940 l_counter := l_counter + 1;
2941 l_return_status_id := l_msn_cur.status_id;
2942 IF(l_counter = 1) Then
2943 l_status_id := l_return_status_id ;
2944 END IF;
2945 IF(NVL(l_return_status_id,-99) <> NVL(l_status_id,-99)) THEN
2946 l_return_status_id := -1;
2947 EXIT;
2948 END IF;
2949 END LOOP; --exiting msn_cur
2950 END IF;
2951 END IF;
2952 IF(NVL(l_return_status_id,-99) = -1) THEN
2953 EXIT;
2954 END IF;
2955 END LOOP; --exiting c_lpn_item
2956 END IF;
2957 END IF;
2958
2959 If (l_return_status_id IS NOT NULL AND l_return_status_id <> -1) THEN
2960 BEGIN
2961 SELECT status_code
2962 INTO l_return_status_code
2963 FROM mtl_material_statuses
2964 WHERE status_id =l_return_status_id ;
2965 EXCEPTION
2966 WHEN NO_DATA_FOUND THEN
2967 l_return_status_id := NULL; --as status_id is not found in mtl_material_statuses therefore returning NULL
2968 l_return_status_code := NULL;
2969 END;
2970 ELSIF (NVL(l_return_status_id,-99) = -1)THEN
2971 l_return_status_code := FND_MESSAGE.get_string('WMS','WMS_LPN_STATUS_MIXED');
2972 END IF;
2973 IF(g_debug = 1)THEN
2974 inv_trx_util_pub.TRACE('Return Status id is '||l_return_status_id||' Return staus code is '||l_return_status_code,'INV_MATERIAL_STATUS_GRP',9);
2975 END IF;
2976 x_return_status_id := l_return_status_id;
2977 x_return_status_code := l_return_status_code;
2978 END get_lpn_status;
2979
2980 --end of lpn status project
2981 /* -- LPN Status Project --*/
2982 FUNCTION Status_Commingle_Check (
2983 p_item_id IN NUMBER
2984 , p_lot_number IN VARCHAR2 := NULL
2985 , p_org_id IN NUMBER
2986 , p_trx_action_id IN NUMBER
2987 , p_subinv_code IN VARCHAR2
2988 , p_tosubinv_code IN VARCHAR2 := NULL
2989 , p_locator_id IN NUMBER := NULL
2990 , p_tolocator_id IN NUMBER := NULL
2991 , p_xfr_org_id IN NUMBER := NULL
2992 , p_from_lpn_id IN NUMBER := NULL
2993 , p_cnt_lpn_id IN NUMBER := NULL
2994 , p_xfr_lpn_id IN NUMBER := NULL )
2995
2996 RETURN VARCHAR2
2997 IS
2998
2999 CURSOR c_wlc_status IS
3000 SELECT moqd.inventory_item_id inventory_item_id,moqd.lot_number lot_number,moqd.status_id status_id
3001 FROM mtl_onhand_quantities_detail moqd, wms_lpn_contents wlc
3002 WHERE moqd.organization_id = p_org_id
3003 AND moqd.inventory_item_id = nvl(p_item_id,moqd.inventory_item_id)
3004 AND moqd.subinventory_code = p_subinv_code
3005 AND moqd.locator_id = p_locator_id
3006 AND moqd.lpn_id = p_from_lpn_id
3007 AND moqd.containerized_flag = 1
3008 AND wlc.parent_lpn_id=moqd.lpn_id
3009 AND wlc.inventory_item_id=nvl(p_item_id,wlc.inventory_item_id)
3010 AND wlc.serial_summary_entry <> 1 -- To query only non serial controlled items.
3011 GROUP BY moqd.inventory_item_id,moqd.lot_number,moqd.status_id;
3012
3013 l_source_status_id NUMBER ;
3014 l_count NUMBER;
3015 l_comingle VARCHAR2(1):= 'N' ;
3016 l_allow_diff_status VARCHAR2(1) ;
3017 l_progress VARCHAR2(15);
3018 --l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3019
3020 BEGIN
3021 /* Have to check the exact meaning of With Exception for plain items.*/
3022 /*
3023 l_allow_diff_status --> 1 Means 'Yes'
3024 l_allow_diff_status --> 2 Means 'No'
3025 l_allow_diff_status --> 3 Means 'With Exception'
3026 l_allow_diff_status --> Null Means 'Yes'
3027 */
3028 BEGIN
3029 SELECT Nvl(allow_different_status,1) INTO l_allow_diff_status
3030 FROM mtl_parameters
3031 WHERE organization_id =p_xfr_org_id ;
3032
3033 IF(g_debug = 1)THEN
3034 inv_trx_util_pub.TRACE('Status_Commingle_Check: allow_different_status: '||l_allow_diff_status, 1);
3035 END IF;
3036 IF l_allow_diff_status<>1 THEN
3037 l_comingle:='Y';
3038 END IF;
3039 EXCEPTION
3040 WHEN OTHERS THEN
3041 IF (g_debug = 1) THEN
3042 l_progress := 'WMSSCC-0001';
3043 IF(g_debug = 1)THEN
3044 inv_trx_util_pub.TRACE('Status_Commingle_Check: allow_different_status is not available'||l_progress, 1);
3045 END IF;
3046 END IF;
3047 END;
3048
3049 IF l_comingle = 'Y' THEN
3050 if (p_from_lpn_id is null and p_cnt_lpn_id is null ) then /* Non LPN transaction */
3051 l_comingle := 'N'; --added for 6868145
3052 /*
3053 ** Look at MTL_ONHAND_QUANTIES, the on hand table for the source status
3054 Loose -> Loose
3055 Loose -> LPN (Like packing Trx)
3056 */
3057 BEGIN
3058 SELECT nvl(status_id,-9999) INTO l_source_status_id
3059 FROM mtl_onhand_quantities_detail
3060 WHERE organization_id = p_org_id
3061 AND inventory_item_id = p_item_id
3062 AND (lot_number = p_lot_number
3063 OR (lot_number is null and p_lot_number is NULL))
3064 AND subinventory_code = p_subinv_code
3065 AND locator_id = p_locator_id
3066 AND lpn_id is NULL
3067 AND containerized_flag = 2 -- (loose material)
3068 AND ROWNUM=1;
3069
3070
3071 EXCEPTION
3072
3073 WHEN No_Data_Found THEN
3074 IF(g_debug = 1)THEN
3075 inv_trx_util_pub.TRACE('No onhand is available with this combination ');
3076 END IF;
3077
3078 WHEN too_many_rows THEN
3079 l_source_status_id := NULL;
3080 l_progress := 'WMSSCC-0002';
3081 IF(g_debug = 1)THEN
3082 inv_trx_util_pub.TRACE('Status_Commingle_Check: More than one status for the comming material: ', 1);
3083 inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3084 END IF;
3085 RAISE fnd_api.g_exc_error;
3086 WHEN OTHERS THEN
3087 l_progress := 'WMSSCC-0003';
3088 l_source_status_id := null;
3089 inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3090 RAISE fnd_api.g_exc_error;
3091 END;
3092
3093 IF l_source_status_id <> -9999 and p_item_id is not null THEN
3094 BEGIN
3095 SELECT 'Y' INTO l_comingle
3096 FROM DUAL WHERE EXISTS
3097 (SELECT 1
3098 FROM mtl_onhand_quantities_detail
3099 WHERE organization_id = p_xfr_org_id
3100 AND inventory_item_id = p_item_id
3101 AND (lot_number = p_lot_number
3102 OR (lot_number is null and p_lot_number is null))
3103 AND subinventory_code = p_tosubinv_code
3104 AND locator_id = p_tolocator_id
3105 AND Nvl(lpn_id,-9999)=Nvl(p_xfr_lpn_id,-9999)
3106 AND l_source_status_id <> Nvl(status_id,-9999));
3107
3108
3109 EXCEPTION
3110 WHEN NO_DATA_FOUND THEN
3111 l_comingle := 'N';
3112 END;
3113 END IF ; -- IF l_source_status_id IS NOT NULL
3114
3115
3116 ELSIF p_cnt_lpn_id IS NOT NULL THEN
3117 -- Entire LPN is moving so we need not worry abt comingling.
3118 l_comingle := 'N';
3119 ELSIF p_from_lpn_id IS NOT NULL THEN
3120 -- LPN -> Loose (Like Unpacking Trx)
3121 -- LPN -> LPN ( Like moving material from one LPN to another LPN)
3122 l_comingle := 'N'; -- if the following loop contains zero records then default we have to throw is 'N'
3123 -- This case will occure if LPN contains all serial controlled items.
3124 FOR l_wlc_rec IN c_wlc_status() LOOP
3125 BEGIN
3126 IF(g_debug = 1)THEN
3127 inv_trx_util_pub.TRACE('In loop ');
3128 inv_trx_util_pub.TRACE('p_xfr_org_id: ' ||p_xfr_org_id);
3129 inv_trx_util_pub.TRACE('l_wlc_rec.inventory_item_id: '||l_wlc_rec.inventory_item_id);
3130 inv_trx_util_pub.TRACE('Lot number: '||l_wlc_rec.lot_number);
3131 inv_trx_util_pub.TRACE('p_tosubinv_code: '||p_tosubinv_code);
3132 inv_trx_util_pub.TRACE('p_tolocator_id: '||p_tolocator_id);
3133 inv_trx_util_pub.TRACE('p_xfr_lpn_id: '||p_xfr_lpn_id);
3134 inv_trx_util_pub.TRACE('l_wlc_rec.status_id: '||l_wlc_rec.status_id);
3135 END IF;
3136
3137 SELECT 'Y' INTO l_comingle
3138 FROM DUAL WHERE EXISTS
3139 (SELECT 1
3140 FROM mtl_onhand_quantities_detail
3141 WHERE organization_id = p_xfr_org_id
3142 AND inventory_item_id =l_wlc_rec.inventory_item_id
3143 AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_rec.lot_number,'@@@@')
3144 AND subinventory_code = p_tosubinv_code
3145 AND locator_id = p_tolocator_id
3146 AND Nvl(lpn_id,-9999) =Nvl(p_xfr_lpn_id,-9999)
3147 AND nvl(status_id,-9999)<>nvl(l_wlc_rec.status_id,-9999));
3148 EXCEPTION
3149 WHEN NO_DATA_FOUND THEN
3150 l_comingle := 'N';
3151 END;
3152
3153
3154 IF l_comingle='Y' THEN
3155 EXIT;
3156 END IF;
3157
3158 END LOOP;
3159 END IF; -- if (p_from_lpn_id is null and p_cnt_lpn_id is null and p_xfr_lpn_id is null )
3160
3161 END IF; -- IF l_comingle = 'Y' THEN
3162
3163 RETURN l_comingle;
3164 EXCEPTION
3165 WHEN OTHERS THEN
3166 IF(g_debug = 1)THEN
3167 inv_trx_util_pub.TRACE('l_progress: '|| l_progress,1);
3168 END IF;
3169 RAISE fnd_api.g_exc_error;
3170 END Status_Commingle_Check;
3171
3172
3173 FUNCTION is_trx_allow_lpns(
3174 p_wms_installed IN VARCHAR2,
3175 p_trx_status_enabled IN NUMBER,
3176 p_trx_type_id IN NUMBER,
3177 p_lot_status_enabled IN VARCHAR2,
3178 p_serial_status_enabled IN VARCHAR2,
3179 p_organization_id IN NUMBER,
3180 p_inventory_item_id IN NUMBER,
3181 p_sub_code IN VARCHAR2,
3182 p_locator_id IN NUMBER,
3183 p_lot_number IN VARCHAR2,
3184 p_serial_number IN VARCHAR2,
3185 p_object_type IN VARCHAR2,
3186 p_fromlpn_id IN NUMBER,
3187 p_xfer_lpn_id IN NUMBER,
3188 p_xfer_sub_code IN VARCHAR2,
3189 p_xfer_locator_id IN NUMBER,
3190 p_xfer_org_id IN NUMBER)
3191 RETURN NUMBER IS
3192
3193 l_allow_mixed_status number := NVL(FND_PROFILE.VALUE('WMS_ALLOW_MIXED_STATUS'),2);
3194 l_lpn_context number;
3195 l_return_status number :=-1;
3196 l_lpn_loc number;
3197 l_lpn_sub VARCHAR2(30);
3198 l_lpn_org_id number;
3199 l_trx_allowed varchar2(1):='Y';
3200 l_trx_allowed_count number :=0;
3201 l_trx_not_allowed_count number :=0;
3202 l_trx_allow NUMBER:=0;
3203 l_trx_type_id NUMBER:=0;
3204 c_api_name varchar2(30) := 'is_trx_allow_lpns';
3205 l_serial_controlled number;
3206 l_serial_status_enabled number;
3207 l_msg_count number;
3208 l_msg_data varchar(30);
3209 l_lot_controlled NUMBER:=0;
3210
3211 CURSOR l_lpn_mtrl(l_cur_inventory_item_id NUMBER)
3212 IS
3213 SELECT *
3214 FROM mtl_txn_request_lines mtrl
3215 WHERE mtrl.lpn_id IN
3216 (SELECT lpn_id
3217 FROM wms_license_plate_numbers plpn
3218 start with lpn_id = p_fromlpn_id
3219 connect by parent_lpn_id = prior lpn_id
3220 )
3221 AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id
3222 AND organization_id=p_organization_id
3223 AND line_status=7;
3224
3225
3226 CURSOR c_lpn_item(l_cur_inventory_item_id NUMBER)
3227 IS
3228 SELECT *
3229 FROM wms_lpn_contents wlc
3230 WHERE wlc.parent_lpn_id IN
3231 (SELECT lpn_id
3232 FROM wms_license_plate_numbers plpn
3233 start with lpn_id = p_fromlpn_id
3234 connect by parent_lpn_id = prior lpn_id
3235 )
3236 AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id
3237 ORDER BY wlc.serial_summary_entry DESC ;
3238
3239 CURSOR msn_cur(l_cur_lpn_id NUMBER , l_cur_inventory_item_id NUMBER)
3240 IS
3241 SELECT msn.status_id
3242 FROM mtl_serial_numbers msn
3243 where msn.inventory_item_id = l_cur_inventory_item_id
3244 AND msn.lpn_id = l_cur_lpn_id;
3245
3246 CURSOR mmtt_cur(l_cur_inventory_item_id NUMBER)
3247 IS SELECT mmtt.transaction_temp_id , mmtt.subinventory_code,
3248 mmtt.transaction_type_id,
3249 mmtt.locator_id , mmtt.inventory_item_id ,
3250 nvl(mmtt.lpn_id,mmtt.content_lpn_id) lpn_id , mmtt.item_lot_control_code
3251 FROM mtl_material_transactions_temp mmtt
3252 where mmtt.transfer_lpn_id = p_fromlpn_id
3253 AND mmtt.transaction_source_type_id = 2
3254 AND mmtt.transaction_type_id = 52
3255 AND Nvl(l_cur_inventory_item_id,inventory_item_id)=inventory_item_id;
3256
3257 CURSOR mtlt_cur(l_transaction_temp_id NUMBER)
3258 IS
3259 SELECT mtlt.lot_number
3260 FROM mtl_transaction_lots_temp mtlt
3261 where transaction_temp_id = l_transaction_temp_id;
3262
3263 CURSOR msnt_cur(l_transaction_temp_id NUMBER)
3264 IS
3265 SELECT msn.status_id
3266 FROM mtl_serial_numbers msn , mtl_serial_numbers_temp msnt
3267 WHERE msnt.transaction_temp_id = l_transaction_temp_id
3268 AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
3269
3270
3271 -- CURSOR msnt_cur(l_transaction_temp_id NUMBER)
3272 -- IS
3273 -- SELECT msn.status_id
3274 -- FROM mtl_serial_numbers msn , mtl_serial_numbers_temp msnt
3275 -- WHERE msnt.transaction_temp_id = l_transaction_temp_id
3276 -- AND msn.serial_number BETWEEN msnt.fm_serial_number AND msnt.to_serial_number;
3277 BEGIN
3278 IF(g_debug = 1)THEN
3279 inv_trx_util_pub.TRACE(c_api_name||':Entered is_status_applicable ', 'inv_material_status_grp', 9);
3280 inv_trx_util_pub.TRACE('p_wms_installed: '||p_wms_installed,'inv_material_status_grp', 9);
3281 inv_trx_util_pub.TRACE('p_trx_status_enabled: '||p_trx_status_enabled,'inv_material_status_grps', 9);
3282 inv_trx_util_pub.TRACE('p_trx_type_id: '||p_trx_type_id,'inv_material_status_grp', 9);
3283 inv_trx_util_pub.TRACE('p_lot_status_enabled: '||p_lot_status_enabled,'inv_material_status_grp', 9);
3284 inv_trx_util_pub.TRACE('p_serial_status_enabled: '||p_serial_status_enabled, 'inv_material_status_grp', 9);
3285 inv_trx_util_pub.TRACE('p_organization_id: '||p_organization_id,'inv_material_status_grp', 9);
3286 inv_trx_util_pub.TRACE('p_inventory_item_id: '||p_inventory_item_id,'inv_material_status_grp', 9);
3287 inv_trx_util_pub.TRACE('p_sub_code: '||p_sub_code,'inv_material_status_grp', 9);
3288 inv_trx_util_pub.TRACE('p_locator_id: '||p_locator_id,'inv_material_status_grp', 9);
3289 inv_trx_util_pub.TRACE('p_lot_number: '||p_lot_number,'inv_material_status_grp', 9);
3290 inv_trx_util_pub.TRACE('p_serial_number: '||p_serial_number,'inv_material_status_grp', 9);
3291 inv_trx_util_pub.TRACE('p_object_type: '||p_object_type,'inv_material_status_grp', 9);
3292
3293 inv_trx_util_pub.TRACE('p_fromlpn_id: '||p_fromlpn_id,'inv_material_status_grp', 9);
3294 inv_trx_util_pub.TRACE('p_xfer_lpn_id: '||p_xfer_lpn_id,'inv_material_status_grp', 9);
3295 inv_trx_util_pub.TRACE('p_xfer_sub_code: '||p_xfer_sub_code,'inv_material_status_grp', 9);
3296 inv_trx_util_pub.TRACE('p_xfer_locator_id: '||p_xfer_locator_id,'inv_material_status_grp', 9);
3297 inv_trx_util_pub.TRACE('p_xfer_org_id: '||p_xfer_org_id, 'inv_material_status_grp', 9);
3298 END IF;
3299
3300 BEGIN
3301
3302 SELECT wlpn.organization_id , wlpn.subinventory_code , wlpn.locator_id , wlpn.lpn_context into
3303 l_lpn_org_id , l_lpn_sub ,l_lpn_loc ,l_lpn_context
3304 FROM wms_license_plate_numbers wlpn
3305 where lpn_id = p_fromlpn_id;
3306 EXCEPTION
3307 WHEN NO_DATA_FOUND THEN
3308 IF(g_debug = 1)THEN
3309 inv_trx_util_pub.TRACE('Unable to find the LPN -- Error occured');
3310 END IF;
3311 RAISE fnd_api.g_exc_unexpected_error;
3312
3313 END;
3314
3315 IF(g_debug = 1)THEN
3316 inv_trx_util_pub.TRACE('LPN Context: '|| l_lpn_context,'inv_material_status_grp', 9);
3317 inv_trx_util_pub.TRACE('LPN Org Id: '|| l_lpn_org_id,'inv_material_status_grp', 9);
3318 inv_trx_util_pub.TRACE('LPN Sub: '|| l_lpn_sub,'inv_material_status_grp', 9);
3319 inv_trx_util_pub.TRACE('LPN Locator: '|| l_lpn_loc,'inv_material_status_grp', 9);
3320 END IF;
3321
3322 if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
3323
3324 IF p_trx_type_id IS NULL THEN
3325 -- This will execute only for Putaway pages not for cycle count pages.
3326 l_trx_type_id:=64; -- Move order Transfer
3327 ELSE
3328 -- For Cycle count and physical count pages.
3329 l_trx_type_id:=p_trx_type_id;
3330 END IF;
3331 --call kamesh api
3332 FOR l_cur_wlc IN c_lpn_item(p_inventory_item_id)
3333 LOOP
3334 IF(g_debug = 1)THEN
3335 inv_trx_util_pub.TRACE('In loop, Checking the material status for the item: '||l_cur_wlc.inventory_item_id);
3336 END IF;
3337 l_trx_allow := inv_material_status_grp.is_status_applicable_lpns(p_wms_installed => p_wms_installed,
3338 p_trx_status_enabled =>p_trx_status_enabled,
3339 p_trx_type_id => l_trx_type_id,
3340 p_lot_status_enabled => p_lot_status_enabled,
3341 p_serial_status_enabled => p_serial_status_enabled,
3342 p_organization_id => p_organization_id,
3343 p_inventory_item_id => l_cur_wlc.inventory_item_id,
3344 p_sub_code => l_lpn_sub,
3345 p_locator_id => l_lpn_loc,
3346 p_lot_number => l_cur_wlc.lot_number,
3347 p_serial_number => p_serial_number,
3348 p_object_type => p_object_type,
3349 p_fromlpn_id => p_fromlpn_id,
3350 p_xfer_lpn_id => p_xfer_lpn_id,
3351 p_xfer_sub_code => p_xfer_sub_code,
3352 p_xfer_locator_id => p_xfer_locator_id,
3353 p_xfer_org_id => p_xfer_org_id);
3354
3355 IF(g_debug = 1)THEN
3356 inv_trx_util_pub.TRACE('l_trx_allow status for the item: '||l_cur_wlc.inventory_item_id||' is: '|| l_trx_allow,'Material Status', 9);
3357 END IF;
3358
3359 if l_trx_allow=0 THEN
3360 l_trx_allowed_count := l_trx_allowed_count+1;
3361 if l_trx_not_allowed_count <> 0 then
3362 exit;
3363 end if;
3364 elsif l_trx_allow=2 then
3365 l_trx_not_allowed_count := l_trx_not_allowed_count+1;
3366 if l_trx_allowed_count <> 0 then
3367 exit;
3368 end if;
3369 end if;
3370
3371 END LOOP; --FOR l_cur_wlc IN c_lpn_item
3372
3373
3374 elsif l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_RCV or
3375 l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_WIP then
3376 -- LPN is in receiving or WIP
3377 IF(g_debug = 1)THEN
3378 inv_trx_util_pub.TRACE('LPN is in Receiving:','inv_material_status_grp', 9);
3379 END IF;
3380
3381 --IF p_trx_type_id IS NULL AND l_lpn_context= WMS_Container_PUB.LPN_CONTEXT_WIP THEN
3382 --p_trx_type_id:=43; -- WIP Component Return.
3383 --END IF;
3384
3385
3386 FOR l_cur_mtrl IN l_lpn_mtrl(p_inventory_item_id)
3387 LOOP
3388 IF(g_debug = 1)THEN
3389 inv_trx_util_pub.TRACE('In loop, Checking the material status for the item: '||l_cur_mtrl.inventory_item_id);
3390 END IF;
3391 l_serial_controlled := 0;
3392 l_serial_status_enabled := 0;
3393 IF inv_cache.set_item_rec(p_organization_id, l_cur_mtrl.inventory_item_id) THEN
3394 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
3395 l_serial_controlled := 1; -- Item is serial controlled
3396 END IF;
3397 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
3398 l_serial_status_enabled := 1;
3399 END IF;
3400 END IF;
3401 IF (l_serial_controlled <> 1) then
3402 -- Non serial controled item. It may be plain item or lot controlled item.
3403 IF(g_debug = 1)THEN
3404 inv_trx_util_pub.TRACE('Before calling get_default_status');
3405 END IF;
3406 l_return_status :=
3407 INV_MATERIAL_STATUS_GRP.get_default_status
3408 (p_organization_id => p_organization_id,
3409 p_inventory_item_id => l_cur_mtrl.inventory_item_id,
3410 p_sub_code => l_lpn_sub,
3411 p_loc_id => l_lpn_loc,
3412 p_lot_number => l_cur_mtrl.lot_number,
3413 p_lpn_id => l_cur_mtrl.lpn_id,
3414 p_transaction_action_id=> NULL, p_src_status_id => NULL);
3415 IF(g_debug = 1)THEN
3416 inv_trx_util_pub.TRACE('Value of l_return_status: '||l_return_status);
3417 END IF;
3418
3419 l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3420 p_status_id => l_return_status
3421 ,p_transaction_type_id => l_cur_mtrl.transaction_type_id
3422 ,x_return_status => l_trx_allowed
3423 ,x_msg_count => l_msg_count
3424 ,x_msg_data => l_msg_data);
3425 IF(g_debug = 1)THEN
3426 inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
3427 END If;
3428 if l_trx_allowed='Y' then
3429 l_trx_allowed_count:=l_trx_allowed_count+1;
3430 if l_trx_not_allowed_count > 0 then
3431 exit;
3432 end if;
3433 ELSE
3434 l_trx_not_allowed_count := l_trx_not_allowed_count+1;
3435
3436 if l_trx_allowed_count > 0 then
3437 exit;
3438 end if;
3439
3440 end if;
3441
3442 ELSE --IF (l_serial_controlled <> 1) then
3443 --item is serial controlled therefor checkin msn for status
3444 IF(l_serial_status_enabled = 1) THEN
3445 FOR l_msn_cur in msn_cur(l_cur_mtrl.lpn_id , l_cur_mtrl.inventory_item_id) loop
3446 --l_counter := l_counter + 1;
3447 l_return_status := l_msn_cur.status_id;
3448
3449 l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3450 p_status_id => l_return_status
3451 ,p_transaction_type_id => l_cur_mtrl.transaction_type_id
3452 ,x_return_status => l_trx_allowed
3453 ,x_msg_count => l_msg_count
3454 ,x_msg_data => l_msg_data);
3455
3456 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3457 RAISE fnd_api.g_exc_unexpected_error;
3458 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3459 RAISE fnd_api.g_exc_error;
3460 END IF;
3461
3462 IF l_trx_allowed='N' THEN
3463 l_trx_allowed_count := l_trx_allowed_count+1;
3464 if l_trx_not_allowed_count > 0 then
3465 exit;
3466 end if;
3467 ELSE
3468 l_trx_not_allowed_count:=l_trx_not_allowed_count+1;
3469 if l_trx_allowed_count > 0 then
3470 exit;
3471 end if;
3472 END IF;
3473 END LOOP; --exiting msn_cur
3474 END IF; -- IF(l_serial_status_enabled = 1)
3475 END IF; -- IF (l_serial_controlled <> 1) then
3476
3477 IF(g_debug = 1)THEN
3478 inv_trx_util_pub.TRACE('Completed if condition execution');
3479 END IF;
3480
3481 if l_trx_not_allowed_count>0 and l_trx_allowed_count>0 then
3482 exit;
3483 end if;
3484 IF(g_debug = 1)THEN
3485 inv_trx_util_pub.TRACE('Completed one iteration');
3486 END IF;
3487 END LOOP; --FOR l_cur_wlc IN c_lpn_item
3488
3489 --elsif l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_PACKING then
3490 -- LPN is in packing context.
3491 --l_trx_allowed_count:=l_trx_allowed_count+1;
3492 --l_trx_allowed_count:=l_trx_allowed_count-1;
3493 elsif l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_PACKING then
3494 inv_trx_util_pub.TRACE('LPN is in Packing context');
3495 inv_trx_util_pub.TRACE('Querying MMTT to get the status id and transaction type id');
3496
3497 FOR l_mmtt_cur IN mmtt_cur(p_inventory_item_id)
3498 LOOP
3499 l_serial_status_enabled := 0;
3500 l_serial_controlled := 0;
3501 l_lot_controlled := 0;
3502 IF inv_cache.set_item_rec(l_lpn_org_id, l_mmtt_cur.inventory_item_id) THEN
3503 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
3504 l_serial_controlled := 1; -- Item is serial controlled
3505 IF (NVL(inv_cache.item_rec.serial_status_enabled,'Y') = 'Y') THEN
3506 l_serial_status_enabled := 1;
3507 END IF;
3508 END IF;
3509 IF (inv_cache.item_rec.lot_control_code = 2) THEN
3510 l_lot_controlled := 1; -- item is lot controlled
3511 END IF;
3512 END IF;
3513 inv_trx_util_pub.TRACE('L_SERIAL_CONTROLLED_FLAG IS '||l_serial_controlled||'l_serial_status_enabled flag is '||l_serial_status_enabled,9);
3514 IF (l_lot_controlled = 1 AND l_serial_controlled = 0) THEN
3515 -- Item is lot controlled item. We are not taking care of both lot and serial controlled items.
3516 FOR l_mtlt_cur IN mtlt_cur(l_mmtt_cur.transaction_temp_id)
3517 LOOP
3518 l_return_status := INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
3519 (p_organization_id => p_organization_id,
3520 p_inventory_item_id => l_mmtt_cur.inventory_item_id,
3521 p_sub_code => l_mmtt_cur.subinventory_code,
3522 p_loc_id => l_mmtt_cur.locator_id,
3523 p_lot_number => l_mtlt_cur.lot_number,
3524 p_lpn_id => l_mmtt_cur.lpn_id,
3525 p_transaction_action_id=> NULL,
3526 p_src_status_id => NULL);
3527
3528
3529
3530
3531 IF(g_debug = 1)THEN
3532 inv_trx_util_pub.TRACE('Value of l_return_status: '||l_return_status);
3533
3534 END IF;
3535
3536 l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3537 p_status_id => l_return_status
3538 ,p_transaction_type_id => l_mmtt_cur.transaction_type_id
3539 ,x_return_status => l_trx_allowed
3540 ,x_msg_count => l_msg_count
3541 ,x_msg_data => l_msg_data);
3542 IF(g_debug = 1)THEN
3543 inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
3544 -- inv_trx_util_pub.TRACE('Value of l_allow_transaction: '||l_allow_transaction);
3545
3546 END If;
3547
3548 /* BEGIN
3549 SELECT 'N'
3550 INTO l_allow_transaction
3551 FROM dual
3552 WHERE EXISTS
3553 (SELECT 1
3554 FROM mtl_onhand_quantities_detail moqd,
3555 mtl_status_transaction_control mtc
3556 WHERE moqd.organization_id = p_xfer_org_id
3557 AND moqd.inventory_item_id = l_mmtt_cur.inventory_item_id
3558 AND NVL(moqd.lot_number,'@@@') = NVL(l_mtlt_cur.lot_number,'@@@')
3559 AND moqd.lpn_id = p_xfer_lpn_id
3560 AND moqd.status_id = mtc.status_id
3561 AND mtc.transaction_type_id = l_mmtt_cur.transaction_type_id
3562 AND mtc.is_allowed = 2
3563 ) ;
3564 EXCEPTION
3565 WHEN No_Data_Found THEN
3566 l_allow_transaction:='Y';
3567 END;
3568 */
3569 if l_trx_allowed='Y' then
3570 l_trx_allowed_count:=l_trx_allowed_count+1;
3571 if l_trx_not_allowed_count > 0 then
3572 exit;
3573 end if;
3574 ELSE
3575 l_trx_not_allowed_count := l_trx_not_allowed_count+1;
3576 if l_trx_allowed_count > 0 then
3577 exit;
3578 end if;
3579 end if;
3580
3581 END LOOP; -- FOR l_mtlt_cur IN mtlt_cur(l_mmtt_cur.transaction_temp_id)
3582
3583 ELSIF (l_serial_controlled = 1) THEN
3584 IF (l_serial_status_enabled = 1) THEN
3585 inv_trx_util_pub.TRACE('It is serial controlled item ', 9);
3586 inv_trx_util_pub.TRACE('Querying MSN and MSNT to know the status ', 9);
3587 FOR l_msnt_cur IN msnt_cur(l_mmtt_cur.transaction_temp_id) LOOP
3588 l_return_status := l_msnt_cur.status_id;
3589 l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3590 p_status_id => l_return_status
3591 ,p_transaction_type_id => l_mmtt_cur.transaction_type_id
3592 ,x_return_status => l_trx_allowed
3593 ,x_msg_count => l_msg_count
3594 ,x_msg_data => l_msg_data);
3595 IF(g_debug = 1)THEN
3596 inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
3597 END If;
3598 if l_trx_allowed='Y' then
3599 l_trx_allowed_count:=l_trx_allowed_count+1;
3600 if l_trx_not_allowed_count > 0 then
3601 exit;
3602 end if;
3603 ELSE
3604 l_trx_not_allowed_count := l_trx_not_allowed_count+1;
3605 if l_trx_allowed_count > 0 then
3606 exit;
3607 end if;
3608 end if;
3609
3610 END LOOP; -- FOR l_msnt_cur IN msnt_cur(l_mmtt_cur.transaction_temp_id) LOOP
3611 END IF; -- IF (l_serial_status_enabled = 1) THEN
3612
3613 ELSE
3614 -- Not lot controlled and not serial controlled item
3615 l_return_status :=
3616 INV_MATERIAL_STATUS_GRP.get_default_status --calling function to get the MOQD status
3617 (p_organization_id => p_organization_id,
3618 p_inventory_item_id => l_mmtt_cur.inventory_item_id,
3619 p_sub_code => l_mmtt_cur.subinventory_code,
3620 p_loc_id => l_mmtt_cur.locator_id,
3621 p_lot_number => NULL,
3622 p_lpn_id => l_mmtt_cur.lpn_id,
3623 p_transaction_action_id=> NULL,
3624 p_src_status_id => NULL);
3625 l_trx_allowed := inv_material_status_grp.is_trx_allowed(
3626 p_status_id => l_return_status
3627 ,p_transaction_type_id => l_mmtt_cur.transaction_type_id
3628 ,x_return_status => l_trx_allowed
3629 ,x_msg_count => l_msg_count
3630 ,x_msg_data => l_msg_data);
3631 IF(g_debug = 1)THEN
3632 inv_trx_util_pub.TRACE('Value of l_trx_allowed: '||l_trx_allowed);
3633 END If;
3634 if l_trx_allowed='Y' then
3635 l_trx_allowed_count:=l_trx_allowed_count+1;
3636 if l_trx_not_allowed_count > 0 then
3637 exit;
3638 end if;
3639 ELSE
3640 l_trx_not_allowed_count := l_trx_not_allowed_count+1;
3641
3642 if l_trx_allowed_count > 0 then
3643 exit;
3644 end if;
3645
3646 end if;
3647
3648 END IF; -- IF (l_lot_controlled = 1 AND l_serial_controlled = 0) THEN
3649
3650 if l_trx_not_allowed_count>0 and l_trx_allowed_count>0 then
3651 exit;
3652 end if;
3653 inv_trx_util_pub.TRACE('Completed one iteration of the MMTT');
3654 END LOOP; -- FOR l_mmtt_cur IN mmtt_cur(p_inventory_item_id)
3655
3656
3657 end if; --if l_lpn_context=WMS_Container_PUB.LPN_CONTEXT_INV then
3658
3659
3660 IF(g_debug = 1)THEN
3661 inv_trx_util_pub.TRACE('Before returning the from API is_trx_allow_lpns','Material Status', 9);
3662 inv_trx_util_pub.TRACE('l_trx_allowed_count: '||l_trx_allowed_count,'Material Status', 9);
3663 inv_trx_util_pub.TRACE('l_trx_not_allowed_count: ' || l_trx_not_allowed_count,'Material Status', 9);
3664 END IF;
3665
3666 if l_trx_allowed_count=0 AND l_trx_not_allowed_count <> 0 then
3667 -- All the contents of the LPN dis allowed this transaction
3668 return 0; --0
3669 elsif l_trx_not_allowed_count=0 AND l_trx_allowed_count<>0 then
3670 -- All the contents of the LPN allows this transaction
3671 return 2;
3672 ELSIF l_trx_allowed_count<>0 AND l_trx_not_allowed_count <> 0 then
3673 -- Some contents of the LPN allows and some contents of the LPN dis allows this transaction.
3674 return 1;
3675 ELSE
3676 -- No contents in the LPN. It may be new LPN. So we are allowing the transaction.
3677 RETURN 2;
3678 end if;
3679
3680 EXCEPTION
3681 WHEN OTHERS THEN
3682 inv_trx_util_pub.TRACE('Exception occured in is_trx_allow_lpns in function');
3683
3684 END is_trx_allow_lpns;
3685 /* -- LPN Status Project --*/
3686
3687
3688 END INV_MATERIAL_STATUS_GRP;
[Home] [Help]