[Home] [Help]
11: , lot_number
12: , 0
13: , current_status
14: , mms.status_code
15: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
16: WHERE inventory_item_id = p_item_id
17: AND (group_mark_id IS NULL
18: OR group_mark_id = -1
19: )
103: , lot_number
104: , 0
105: , current_status
106: , mms.status_code
107: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
108: WHERE msn.inventory_item_id = p_item_id
109: AND (group_mark_id IS NULL
110: OR group_mark_id = -1
111: )
131: , lot_number
132: , 0
133: , current_status
134: , mms.status_code
135: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
136: WHERE inventory_item_id = p_item_id
137: AND (group_mark_id IS NULL
138: OR group_mark_id = -1
139: )
218: , mms.status_code
219: FROM rcv_serials_supply rss
220: , rcv_shipment_lines rsl
221: , mtl_serial_numbers msn
222: , mtl_material_statuses_tl mms
223: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
224: --BUG 3417870: The RSL.shipment_line_status_code will be FULLY
225: -- RECEIVED, so we need to comment it out.
226: -- AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
268: , msn.current_status
269: , mms.status_code
270: FROM mtl_serial_numbers msn
271: , rcv_shipment_lines rsl
272: , mtl_material_statuses_tl mms
273: WHERE msn.inventory_item_id = p_item_id
274: AND rsl.shipment_header_id = p_shipment_header_id
275: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
276: AND ( ( msn.current_status IN (1, 6)
335: , lot_number
336: , 0
337: , current_status
338: , mms.status_code
339: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
340: WHERE inventory_item_id = p_item_id
341: AND (group_mark_id IS NULL
342: OR group_mark_id = -1
343: OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
417: ) THEN
418: BEGIN
419: SELECT NVL(status_code, '')
420: INTO x_lot_status
421: FROM mtl_material_statuses_tl mms
422: WHERE mms.status_id = NVL(l_number, p_default_lot_status_id)
423: AND mms.language = userenv('LANG');
424: EXCEPTION
425: WHEN NO_DATA_FOUND THEN
461: ) THEN
462: BEGIN
463: SELECT NVL(status_code, '')
464: INTO x_lot_status
465: FROM mtl_material_statuses_tl mms
466: WHERE mms.status_id = p_default_lot_status_id
467: AND mms.language = userenv('LANG');
468: EXCEPTION
469: WHEN NO_DATA_FOUND THEN
509: SELECT msn.current_status
510: , NVL(mms.status_code, '')
511: INTO x_current_status
512: , x_serial_status
513: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
514: WHERE msn.inventory_item_id = p_item_id
515: AND msn.serial_number = p_serial
516: AND msn.status_id = mms.status_id(+)
517: AND mms.language (+) = userenv('LANG');
531: ) THEN
532: BEGIN
533: SELECT NVL(mms.status_code, '')
534: INTO x_serial_status
535: FROM mtl_material_statuses_tl mms
536: WHERE mms.status_id = p_default_serial_status
537: AND mms.language (+) = userenv('LANG');
538: EXCEPTION
539: WHEN OTHERS THEN
580: , inv_project.get_project_id
581: , inv_project.get_project_number
582: , inv_project.get_task_id
583: , inv_project.get_task_number
584: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
585: WHERE a.current_organization_id = p_current_organization_id
586: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
587: AND a.inventory_item_id = p_inventory_item_id
588: AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
632: , inv_project.get_project_id
633: , inv_project.get_project_number
634: , inv_project.get_task_id
635: , inv_project.get_task_number
636: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
637: WHERE a.current_organization_id = p_current_organization_id
638: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
639: AND a.inventory_item_id = p_inventory_item_id
640: AND a.current_subinventory_code = p_current_subinventory_code
725: ,lot_number
726: ,0
727: ,current_status
728: ,mms.status_code
729: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
730: WHERE inventory_item_id = p_inventory_item_id
731: AND (group_mark_id IS NULL
732: OR group_mark_id = -1
733: )
751: ,lot_number
752: ,0
753: ,current_status
754: ,mms.status_code
755: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
756: WHERE inventory_item_id = p_inventory_item_id
757: AND (group_mark_id IS NULL OR group_mark_id = -1)
758: AND ((current_organization_id = p_organization_id
759: AND current_status =1
772: , msn.lot_number
773: , 0
774: , msn.current_status
775: , mms.status_code
776: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
777: WHERE msn.inventory_item_id = p_inventory_item_id
778: AND (msn.group_mark_id IS NULL
779: OR msn.group_mark_id = -1
780: )
836: ,current_status
837: ,status_code
838: FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
839: lot_number, 0, current_status, mms.status_code
840: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
841: WHERE inventory_item_id = p_inventory_item_id
842: AND (group_mark_id IS NULL OR group_mark_id = -1)
843: AND (( current_organization_id = p_organization_id
844: AND current_status IN (1, 3, 4, 6)
872: ) = 'Y'
873: UNION
874: SELECT serial_number, current_subinventory_code, current_locator_id,
875: lot_number, 0, current_status, mms.status_code
876: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
877: WHERE inventory_item_id = p_inventory_item_id
878: AND (group_mark_id IS NULL OR group_mark_id = -1)
879: AND ( ( current_organization_id = p_organization_id
880: AND current_status = 1
910: , msn.lot_number
911: , 0
912: , msn.current_status
913: , mms.status_code
914: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
915: WHERE msn.inventory_item_id = p_inventory_item_id
916: AND (msn.group_mark_id IS NULL
917: OR msn.group_mark_id = -1
918: )
1100: , lot_number
1101: , 0
1102: , current_status
1103: , mms.status_code
1104: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1105: WHERE inventory_item_id = p_inventory_item_id
1106: AND (group_mark_id IS NULL
1107: OR group_mark_id = -1
1108: )
1185: , msn.lot_number
1186: , 0
1187: , msn.current_status
1188: , mms.status_code
1189: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1190: WHERE msn.inventory_item_id = p_inventory_item_id
1191: AND ((msn.current_organization_id = p_organization_id
1192: AND msn.current_status IN (3, 4)
1193: )
1237: , 0
1238: , msn.current_status
1239: , mms.status_code
1240: FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
1241: mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
1242: WHERE msn.inventory_item_id = p_inventory_item_id
1243: AND (msn.group_mark_id IS NULL
1244: OR msn.group_mark_id = -1
1245: )
1333: , lot_number
1334: , 0
1335: , current_status
1336: , mms.status_code
1337: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1338: WHERE inventory_item_id = p_inventory_item_id
1339: AND (group_mark_id IS NULL
1340: OR group_mark_id = -1
1341: )
1420: , msn.lot_number
1421: , 0
1422: , msn.current_status
1423: , mms.status_code
1424: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1425: WHERE msn.inventory_item_id = p_inventory_item_id
1426: AND ((msn.current_organization_id = p_organization_id
1427: AND msn.current_status IN (3, 4)
1428: )
1474: , 0
1475: , msn.current_status
1476: , mms.status_code
1477: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1478: mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
1479: WHERE msn.inventory_item_id = p_inventory_item_id
1480: AND (msn.group_mark_id IS NULL
1481: OR msn.group_mark_id = -1
1482: )
1680: , lot_number
1681: , 0
1682: , current_status
1683: , mms.status_code
1684: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1685: WHERE current_organization_id = p_organization_id
1686: AND inventory_item_id = p_inventory_item_id
1687: --AND current_status IN (1, 3, 5)
1688: AND current_status IN (1, 3, 5, 7)
1717: , lot_number
1718: , 0
1719: , current_status
1720: , status_code
1721: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1722: WHERE current_organization_id = p_organization_id
1723: AND inventory_item_id = p_inventory_item_id
1724: --AND current_status IN (1, 3, 5)
1725: AND current_status IN (1, 3, 5, 7)
1785: , lot_number
1786: , 0
1787: , current_status
1788: , mms.status_code
1789: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1790: WHERE msn.inventory_item_id = p_item_id
1791: AND msn.lpn_id = p_lpn_id
1792: AND msn.current_organization_id = p_organization_id
1793: --AND msn.current_status = 5 /* Intransit */
2176: , mil.concatenated_segments
2177: , msn.revision
2178: , msn.cost_group_id
2179: , ccg.cost_group
2180: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2181: WHERE (group_mark_id IS NULL
2182: OR group_mark_id = -1
2183: )
2184: AND mms.status_id(+) = msn.status_id
2214: , mil.concatenated_segments
2215: , msn.revision
2216: , msn.cost_group_id
2217: , ccg.cost_group
2218: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2219: WHERE (group_mark_id IS NULL
2220: OR group_mark_id = -1
2221: )
2222: AND mms.status_id(+) = msn.status_id
2320: , lot_number
2321: , 0
2322: , current_status
2323: , mms.status_code
2324: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2325: WHERE inventory_item_id = p_item_id
2326: AND (group_mark_id IS NULL
2327: OR group_mark_id = -1
2328: )
2386: , msn.lot_number
2387: , 0
2388: , msn.current_status
2389: , mms.status_code
2390: FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2391: WHERE msn.inventory_item_id = p_item_id
2392: AND msn.current_organization_id = p_organization_id
2393: AND msn.current_status = 3
2394: AND msn.current_subinventory_code = p_subinv_code
2410: , msn.lot_number
2411: , 0
2412: , msn.current_status
2413: , mms.status_code
2414: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2415: WHERE msn.inventory_item_id = p_item_id
2416: AND msn.current_organization_id = p_organization_id
2417: AND msn.current_status = 3
2418: AND msn.current_subinventory_code = p_subinv_code
2452: , lot_number
2453: , 0
2454: , current_status
2455: , mms.status_code
2456: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2457: WHERE inventory_item_id = p_item_id
2458: AND (group_mark_id IS NULL
2459: OR group_mark_id = -1
2460: )
2507: , msn.lot_number
2508: , 0
2509: , msn.current_status
2510: , mms.status_code
2511: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2512: WHERE msn.inventory_item_id = p_item_id
2513: AND msn.current_organization_id = p_organization_id
2514: AND msn.current_status = 3
2515: AND msn.current_subinventory_code = p_subinv_code
2533: , msn.lot_number
2534: , 0
2535: , msn.current_status
2536: , mms.status_code
2537: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2538: WHERE msn.inventory_item_id = p_item_id
2539: AND msn.current_organization_id = p_organization_id
2540: AND msn.current_status = 3
2541: AND msn.current_subinventory_code = p_subinv_code
2778: , current_status
2779: , mms.status_code
2780: , ''
2781: , msn.revision
2782: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2783: WHERE inventory_item_id = p_item_id
2784: AND (group_mark_id IS NULL
2785: OR group_mark_id = -1
2786: OR group_mark_id IN (SELECT transaction_temp_id
2835: , lot_number
2836: , ''
2837: , current_status
2838: , mms.status_code
2839: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2840: WHERE current_organization_id = p_organization_id
2841: AND inventory_item_id = p_item_id
2842: AND msn.status_id = mms.status_id(+)
2843: AND mms.language (+) = userenv('LANG')
2888: , lot_number
2889: , ''
2890: , current_status
2891: , mms.status_code
2892: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2893: WHERE current_organization_id = p_organization_id
2894: AND inventory_item_id = p_item_id
2895: AND current_status = 3
2896: AND msn.status_id = mms.status_id(+)
2954: , lot_number
2955: , ''
2956: , current_status
2957: , mms.status_code
2958: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2959: WHERE current_organization_id = p_organization_id
2960: AND inventory_item_id = p_item_id
2961: AND msn.status_id = mms.status_id(+)
2962: AND mms.language (+) = userenv('LANG')
3018: , lot_number
3019: , 0
3020: , current_status
3021: , mms.status_code
3022: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3023: WHERE inventory_item_id = p_item_id
3024: AND (group_mark_id IS NULL
3025: OR group_mark_id = -1
3026: )
3063: , lot_number
3064: , 0
3065: , current_status
3066: , mms.status_code
3067: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3068: WHERE inventory_item_id = p_item_id
3069: AND (group_mark_id IS NULL
3070: OR group_mark_id = -1
3071: )
3127: , lot_number
3128: , 0
3129: , current_status
3130: , mms.status_code
3131: from mtl_serial_numbers msn, mtl_material_statuses_tl mms
3132: where inventory_item_id = l_wip_assembly_id
3133: and msn.status_id = mms.status_id(+)
3134: AND mms.language (+) = userenv('LANG')
3135: AND CURRENT_organization_id = p_organization_id
3585: , lot_number
3586: , 0
3587: , current_status
3588: , mms.status_code
3589: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3590: WHERE lpn_id = p_lpn_id
3591: AND current_organization_id = p_organization_id
3592: AND inventory_item_id = p_item_id
3593: AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
3629: , lot_number
3630: , 0
3631: , current_status
3632: , mms.status_code
3633: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3634: WHERE lpn_id = p_lpn_id
3635: AND current_organization_id = p_organization_id
3636: AND inventory_item_id = p_item_id
3637: AND (group_mark_id IS NULL
3844: , lot_number
3845: , 0
3846: , current_status
3847: , mms.status_code
3848: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3849: WHERE msn.inventory_item_id = p_item_id
3850: AND (group_mark_id IS NULL
3851: OR group_mark_id = -1
3852: )
3873: , lot_number
3874: , 0
3875: , current_status
3876: , mms.status_code
3877: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3878: WHERE inventory_item_id = p_item_id
3879: AND (group_mark_id IS NULL
3880: OR group_mark_id = -1
3881: )
3924: , lot_number
3925: , 0
3926: , current_status
3927: , mms.status_code
3928: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3929: WHERE inventory_item_id = p_item_id
3930: AND (group_mark_id IS NULL
3931: OR group_mark_id = -1
3932: )
3950: , mms.status_code
3951: FROM rcv_serials_supply rss
3952: , rcv_shipment_lines rsl
3953: , mtl_serial_numbers msn
3954: , mtl_material_statuses_tl mms
3955: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
3956: AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
3957: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
3958: AND rsl.shipment_header_id = p_shipment_header_id
4091: , lot_number
4092: , 0
4093: , current_status
4094: , status_code
4095: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4096: WHERE current_organization_id = p_organization_id
4097: AND inventory_item_id = p_inventory_item_id
4098: --AND current_status IN (1, 3, 5)
4099: AND current_status IN (1, 3, 5, 7)
4120: , lot_number
4121: , 0
4122: , current_status
4123: , mms.status_code
4124: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4125: WHERE current_organization_id = p_organization_id
4126: AND inventory_item_id = p_inventory_item_id
4127: --AND current_status IN (1, 3, 5)
4128: AND current_status IN (1, 3, 5, 7)
4159: , lot_number
4160: , 0
4161: , current_status
4162: , status_code
4163: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4164: WHERE current_organization_id = p_organization_id
4165: AND inventory_item_id = p_inventory_item_id
4166: AND (p_lot_number IS NULL
4167: OR lot_number = p_lot_number)
4190: , lot_number
4191: , 0
4192: , current_status
4193: , mms.status_code
4194: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4195: WHERE current_organization_id = p_organization_id
4196: AND inventory_item_id = p_inventory_item_id
4197: --AND current_status IN (1, 3, 5)
4198: AND current_status IN (1, 3, 5, 7)