[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: )
117: , lot_number
118: , 0
119: , current_status
120: , mms.status_code
121: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
122: WHERE msn.inventory_item_id = p_item_id
123: AND (group_mark_id IS NULL
124: OR group_mark_id = -1
125: )
145: , lot_number
146: , 0
147: , current_status
148: , mms.status_code
149: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
150: WHERE inventory_item_id = p_item_id
151: AND (group_mark_id IS NULL
152: OR group_mark_id = -1
153: )
233: , mms.status_code
234: FROM rcv_serials_supply rss
235: , rcv_shipment_lines rsl
236: , mtl_serial_numbers msn
237: , mtl_material_statuses_tl mms
238: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
239: --BUG 3417870: The RSL.shipment_line_status_code will be FULLY
240: -- RECEIVED, so we need to comment it out.
241: -- AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
286: , msn.current_status
287: , mms.status_code
288: FROM mtl_serial_numbers msn
289: , rcv_shipment_lines rsl
290: , mtl_material_statuses_tl mms
291: WHERE msn.inventory_item_id = p_item_id
292: AND rsl.shipment_header_id = p_shipment_header_id
293: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
294: AND ( ( msn.current_status IN (1, 6)
356: , lot_number
357: , 0
358: , current_status
359: , mms.status_code
360: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
361: WHERE inventory_item_id = p_item_id
362: AND (group_mark_id IS NULL
363: OR group_mark_id = -1
364: OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
446: ) THEN
447: BEGIN
448: SELECT NVL(status_code, '')
449: INTO x_lot_status
450: FROM mtl_material_statuses_tl mms
451: WHERE mms.status_id = NVL(l_number, p_default_lot_status_id)
452: AND mms.language = userenv('LANG');
453: EXCEPTION
454: WHEN NO_DATA_FOUND THEN
494: ) THEN
495: BEGIN
496: SELECT NVL(status_code, '')
497: INTO x_lot_status
498: FROM mtl_material_statuses_tl mms
499: WHERE mms.status_id = p_default_lot_status_id
500: AND mms.language = userenv('LANG');
501: EXCEPTION
502: WHEN NO_DATA_FOUND THEN
563: SELECT msn.current_status
564: , NVL(mms.status_code, '')
565: INTO x_current_status
566: , x_serial_status
567: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
568: WHERE msn.inventory_item_id = p_item_id
569: AND msn.serial_number = p_serial
570: AND msn.status_id = mms.status_id(+)
571: AND mms.language (+) = userenv('LANG');
589: ) THEN
590: BEGIN
591: SELECT NVL(mms.status_code, '')
592: INTO x_serial_status
593: FROM mtl_material_statuses_tl mms
594: WHERE mms.status_id = p_default_serial_status
595: AND mms.language (+) = userenv('LANG');
596: EXCEPTION
597: WHEN OTHERS THEN
638: , inv_project.get_project_id
639: , inv_project.get_project_number
640: , inv_project.get_task_id
641: , inv_project.get_task_number
642: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
643: WHERE a.current_organization_id = p_current_organization_id
644: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
645: AND a.inventory_item_id = p_inventory_item_id
646: AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
690: , inv_project.get_project_id
691: , inv_project.get_project_number
692: , inv_project.get_task_id
693: , inv_project.get_task_number
694: FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
695: WHERE a.current_organization_id = p_current_organization_id
696: AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
697: AND a.inventory_item_id = p_inventory_item_id
698: AND a.current_subinventory_code = p_current_subinventory_code
784: ,lot_number
785: ,0
786: ,current_status
787: ,mms.status_code
788: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
789: WHERE inventory_item_id = p_inventory_item_id
790: AND (group_mark_id IS NULL
791: OR group_mark_id = -1
792: )
812: ,lot_number
813: ,0
814: ,current_status
815: ,mms.status_code
816: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
817: WHERE inventory_item_id = p_inventory_item_id
818: AND (group_mark_id IS NULL OR group_mark_id = -1)
819: AND ((current_organization_id = p_organization_id
820: AND current_status =1
835: , msn.lot_number
836: , 0
837: , msn.current_status
838: , mms.status_code
839: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
840: WHERE msn.inventory_item_id = p_inventory_item_id
841: AND (msn.group_mark_id IS NULL
842: OR msn.group_mark_id = -1
843: )
899: ,current_status
900: ,status_code
901: FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
902: lot_number, 0, current_status, mms.status_code
903: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
904: WHERE inventory_item_id = p_inventory_item_id
905: AND (group_mark_id IS NULL OR group_mark_id = -1)
906: AND (( current_organization_id = p_organization_id
907: AND current_status IN (1, 3, 4, 6)
935: ) = 'Y'
936: UNION
937: SELECT serial_number, current_subinventory_code, current_locator_id,
938: lot_number, 0, current_status, mms.status_code
939: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
940: WHERE inventory_item_id = p_inventory_item_id
941: AND (group_mark_id IS NULL OR group_mark_id = -1)
942: AND ( ( current_organization_id = p_organization_id
943: AND current_status = 1
973: , msn.lot_number
974: , 0
975: , msn.current_status
976: , mms.status_code
977: FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
978: WHERE msn.inventory_item_id = p_inventory_item_id
979: AND (msn.group_mark_id IS NULL
980: OR msn.group_mark_id = -1
981: )
1163: , lot_number
1164: , 0
1165: , current_status
1166: , mms.status_code
1167: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1168: WHERE inventory_item_id = p_inventory_item_id
1169: AND (group_mark_id IS NULL
1170: OR group_mark_id = -1
1171: )
1248: , msn.lot_number
1249: , 0
1250: , msn.current_status
1251: , mms.status_code
1252: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1253: WHERE msn.inventory_item_id = p_inventory_item_id
1254: AND ((msn.current_organization_id = p_organization_id
1255: AND msn.current_status IN (3, 4)
1256: )
1300: , 0
1301: , msn.current_status
1302: , mms.status_code
1303: FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
1304: mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
1305: WHERE msn.inventory_item_id = p_inventory_item_id
1306: AND (msn.group_mark_id IS NULL
1307: OR msn.group_mark_id = -1
1308: )
1396: , lot_number
1397: , 0
1398: , current_status
1399: , mms.status_code
1400: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1401: WHERE inventory_item_id = p_inventory_item_id
1402: AND (group_mark_id IS NULL
1403: OR group_mark_id = -1
1404: )
1483: , msn.lot_number
1484: , 0
1485: , msn.current_status
1486: , mms.status_code
1487: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
1488: WHERE msn.inventory_item_id = p_inventory_item_id
1489: AND ((msn.current_organization_id = p_organization_id
1490: AND msn.current_status IN (3, 4)
1491: )
1537: , 0
1538: , msn.current_status
1539: , mms.status_code
1540: FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
1541: mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
1542: WHERE msn.inventory_item_id = p_inventory_item_id
1543: AND (msn.group_mark_id IS NULL
1544: OR msn.group_mark_id = -1
1545: )
1743: , lot_number
1744: , 0
1745: , current_status
1746: , mms.status_code
1747: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1748: WHERE current_organization_id = p_organization_id
1749: AND inventory_item_id = p_inventory_item_id
1750: --AND current_status IN (1, 3, 5)
1751: AND current_status IN (1, 3, 5, 7)
1780: , lot_number
1781: , 0
1782: , current_status
1783: , status_code
1784: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1785: WHERE current_organization_id = p_organization_id
1786: AND inventory_item_id = p_inventory_item_id
1787: --AND current_status IN (1, 3, 5)
1788: AND current_status IN (1, 3, 5, 7)
1848: , lot_number
1849: , 0
1850: , current_status
1851: , mms.status_code
1852: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
1853: WHERE msn.inventory_item_id = p_item_id
1854: AND msn.lpn_id = p_lpn_id
1855: AND msn.current_organization_id = p_organization_id
1856: --AND msn.current_status = 5 /* Intransit */
2263: , mil.concatenated_segments
2264: , msn.revision
2265: , msn.cost_group_id
2266: , ccg.cost_group
2267: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2268: WHERE (group_mark_id IS NULL
2269: OR group_mark_id = -1
2270: )
2271: AND mms.status_id(+) = msn.status_id
2301: , mil.concatenated_segments
2302: , msn.revision
2303: , msn.cost_group_id
2304: , ccg.cost_group
2305: FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
2306: WHERE (group_mark_id IS NULL
2307: OR group_mark_id = -1
2308: )
2309: AND mms.status_id(+) = msn.status_id
2407: , lot_number
2408: , 0
2409: , current_status
2410: , mms.status_code
2411: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2412: WHERE inventory_item_id = p_item_id
2413: AND (group_mark_id IS NULL
2414: OR group_mark_id = -1
2415: )
2473: , msn.lot_number
2474: , 0
2475: , msn.current_status
2476: , mms.status_code
2477: FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2478: WHERE msn.inventory_item_id = p_item_id
2479: AND msn.current_organization_id = p_organization_id
2480: AND msn.current_status = 3
2481: AND msn.current_subinventory_code = p_subinv_code
2497: , msn.lot_number
2498: , 0
2499: , msn.current_status
2500: , mms.status_code
2501: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2502: WHERE msn.inventory_item_id = p_item_id
2503: AND msn.current_organization_id = p_organization_id
2504: AND msn.current_status = 3
2505: AND msn.current_subinventory_code = p_subinv_code
2539: , lot_number
2540: , 0
2541: , current_status
2542: , mms.status_code
2543: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2544: WHERE inventory_item_id = p_item_id
2545: AND (group_mark_id IS NULL
2546: OR group_mark_id = -1
2547: )
2594: , msn.lot_number
2595: , 0
2596: , msn.current_status
2597: , mms.status_code
2598: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2599: WHERE msn.inventory_item_id = p_item_id
2600: AND msn.current_organization_id = p_organization_id
2601: AND msn.current_status = 3
2602: AND msn.current_subinventory_code = p_subinv_code
2620: , msn.lot_number
2621: , 0
2622: , msn.current_status
2623: , mms.status_code
2624: FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
2625: WHERE msn.inventory_item_id = p_item_id
2626: AND msn.current_organization_id = p_organization_id
2627: AND msn.current_status = 3
2628: AND msn.current_subinventory_code = p_subinv_code
2866: , current_status
2867: , mms.status_code
2868: , ''
2869: , msn.revision
2870: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2871: WHERE inventory_item_id = p_item_id
2872: AND (group_mark_id IS NULL
2873: OR group_mark_id = -1
2874: OR group_mark_id IN (SELECT transaction_temp_id
2924: , lot_number
2925: , ''
2926: , current_status
2927: , mms.status_code
2928: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2929: WHERE current_organization_id = p_organization_id
2930: AND inventory_item_id = p_item_id
2931: AND msn.status_id = mms.status_id(+)
2932: AND mms.language (+) = userenv('LANG')
2977: , lot_number
2978: , ''
2979: , current_status
2980: , mms.status_code
2981: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
2982: WHERE current_organization_id = p_organization_id
2983: AND inventory_item_id = p_item_id
2984: AND current_status = 3
2985: AND msn.status_id = mms.status_id(+)
3043: , lot_number
3044: , ''
3045: , current_status
3046: , mms.status_code
3047: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3048: WHERE current_organization_id = p_organization_id
3049: AND inventory_item_id = p_item_id
3050: AND msn.status_id = mms.status_id(+)
3051: AND mms.language (+) = userenv('LANG')
3107: , lot_number
3108: , 0
3109: , current_status
3110: , mms.status_code
3111: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3112: WHERE inventory_item_id = p_item_id
3113: AND (group_mark_id IS NULL
3114: OR group_mark_id = -1
3115: )
3152: , lot_number
3153: , 0
3154: , current_status
3155: , mms.status_code
3156: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3157: WHERE inventory_item_id = p_item_id
3158: AND (group_mark_id IS NULL
3159: OR group_mark_id = -1
3160: )
3216: , lot_number
3217: , 0
3218: , current_status
3219: , mms.status_code
3220: from mtl_serial_numbers msn, mtl_material_statuses_tl mms
3221: where inventory_item_id = l_wip_assembly_id
3222: and msn.status_id = mms.status_id(+)
3223: AND mms.language (+) = userenv('LANG')
3224: AND CURRENT_organization_id = p_organization_id
3674: , lot_number
3675: , 0
3676: , current_status
3677: , mms.status_code
3678: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3679: WHERE lpn_id = p_lpn_id
3680: AND current_organization_id = p_organization_id
3681: AND inventory_item_id = p_item_id
3682: AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
3718: , lot_number
3719: , 0
3720: , current_status
3721: , mms.status_code
3722: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3723: WHERE lpn_id = p_lpn_id
3724: AND current_organization_id = p_organization_id
3725: AND inventory_item_id = p_item_id
3726: AND (group_mark_id IS NULL
3933: , lot_number
3934: , 0
3935: , current_status
3936: , mms.status_code
3937: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3938: WHERE msn.inventory_item_id = p_item_id
3939: AND (group_mark_id IS NULL
3940: OR group_mark_id = -1
3941: )
3962: , lot_number
3963: , 0
3964: , current_status
3965: , mms.status_code
3966: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
3967: WHERE inventory_item_id = p_item_id
3968: AND (group_mark_id IS NULL
3969: OR group_mark_id = -1
3970: )
4013: , lot_number
4014: , 0
4015: , current_status
4016: , mms.status_code
4017: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4018: WHERE inventory_item_id = p_item_id
4019: AND (group_mark_id IS NULL
4020: OR group_mark_id = -1
4021: )
4039: , mms.status_code
4040: FROM rcv_serials_supply rss
4041: , rcv_shipment_lines rsl
4042: , mtl_serial_numbers msn
4043: , mtl_material_statuses_tl mms
4044: WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
4045: AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
4046: AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
4047: AND rsl.shipment_header_id = p_shipment_header_id
4180: , lot_number
4181: , 0
4182: , current_status
4183: , status_code
4184: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4185: WHERE current_organization_id = p_organization_id
4186: AND inventory_item_id = p_inventory_item_id
4187: --AND current_status IN (1, 3, 5)
4188: AND current_status IN (1, 3, 5, 7)
4209: , lot_number
4210: , 0
4211: , current_status
4212: , mms.status_code
4213: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4214: WHERE current_organization_id = p_organization_id
4215: AND inventory_item_id = p_inventory_item_id
4216: --AND current_status IN (1, 3, 5)
4217: AND current_status IN (1, 3, 5, 7)
4248: , lot_number
4249: , 0
4250: , current_status
4251: , status_code
4252: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4253: WHERE current_organization_id = p_organization_id
4254: AND inventory_item_id = p_inventory_item_id
4255: AND (p_lot_number IS NULL
4256: OR lot_number = p_lot_number)
4279: , lot_number
4280: , 0
4281: , current_status
4282: , mms.status_code
4283: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4284: WHERE current_organization_id = p_organization_id
4285: AND inventory_item_id = p_inventory_item_id
4286: --AND current_status IN (1, 3, 5)
4287: AND current_status IN (1, 3, 5, 7)
4327: , lot_number
4328: , 0
4329: , current_status
4330: , mms.status_code
4331: FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
4332: WHERE inventory_item_id = p_inventory_item_id
4333: AND current_organization_id = p_organization_id
4334: AND (group_mark_id IS NULL OR group_mark_id = -1)
4335: AND current_status = 1