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