[Home] [Help]
423: WHERE lpn_id = p_parent_lpn_id;
424:
425: /********************************************************************************
426: * The formation of cursor is different when the LPN Context is Packing Context.
427: * 1) If the LPN Context is Packing then the tables MTL_MATERIAL_TRANSACTIONS_TEMP
428: * and MTL_TRANSACTION_LOTS_TEMPare used to get the required information.
429: * 2) For all other types of LPN Context WMS_LPN_CONTENTS is used.
430: * Bug #4191414 - Modifications to showing the contents for packing context LPNs
431: * The cursor is split in to two select statements
476: -- INVCONV end
477: --lpn status project start
478: NVL(mmtt.lpn_id,mmtt.content_lpn_id)
479: --lpn status project end
480: FROM mtl_material_transactions_temp mmtt,
481: wms_license_plate_numbers lpn,
482: wms_license_plate_numbers lpn3,
483: mtl_parameters mp,
484: cst_cost_groups ccg,
545: --lpn status project
546: NVL(mmtt.lpn_id,mmtt.content_lpn_id)
547: --lpn status project end
548:
549: FROM mtl_material_transactions_temp mmtt,
550: mtl_transaction_lots_temp mtlt,
551: wms_license_plate_numbers lpn,
552: wms_license_plate_numbers lpn3,
553: mtl_parameters mp,
1035: and mlk.lookup_type = 'WMS_LPN_CONTEXT'
1036: and mlk.lookup_code = lpn.lpn_context(+)
1037: and not exists (select wlpn.lpn_id
1038: from wms_license_plate_numbers wlpn,
1039: mtl_material_transactions_temp t,
1040: wms_dispatched_tasks w
1041: where w.status = 4
1042: and w.task_type <> 2
1043: and w.transaction_temp_id = t.transaction_temp_id
1408: msik.shelf_life_code, msik.lot_status_enabled, mtlt.lot_expiration_date
1409: FROM mtl_material_statuses_vl mmst,
1410: mtl_system_items_kfv msik,
1411: mtl_transaction_lots_temp mtlt,
1412: mtl_material_transactions_temp mmtt
1413: WHERE mtlt.lot_number = p_lot_number
1414: AND mmtt.organization_id = p_organization_id
1415: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1416: AND msik.inventory_item_id = p_inventory_item_id
1456: OPEN x_serial_attributes FOR
1457: SELECT mmst.status_code, msik.serial_status_enabled, mmst.status_id
1458: FROM mtl_material_statuses_vl mmst,
1459: mtl_serial_numbers_temp msnt,
1460: mtl_material_transactions_temp mmtt,
1461: mtl_transaction_lots_temp mtlt,
1462: mtl_system_items_b msik
1463: WHERE mmtt.organization_id = p_organization_id
1464: AND mmtt.inventory_item_id = p_inventory_item_id
1651: AND nvl(ms.cost_group_id, 0) = decode(p_cost_group_id, NULL, nvl(ms.cost_group_id, 0), p_cost_group_id)
1652: AND nvl(ms.lot_number, '$@#$%') = decode(p_lot_number, NULL, nvl(ms.lot_number, '$@#$%'), p_lot_number)
1653: AND ms.current_status =3
1654: and not exists (select w.status
1655: from mtl_material_transactions_temp t,
1656: wms_dispatched_tasks w
1657: where w.status = 4
1658: and w.task_type <> 2
1659: and w.transaction_temp_id = t.transaction_temp_id
1685: /* PACKING CONTEXT */
1686: OPEN x_serial_numbers FOR
1687: SELECT msnt.fm_serial_number, mmtt.transfer_lpn_id, mtlt.lot_number
1688: FROM mtl_serial_numbers_temp msnt,
1689: mtl_material_transactions_temp mmtt,
1690: mtl_transaction_lots_temp mtlt
1691: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1692: and nvl(mtlt.serial_transaction_temp_id,mmtt.transaction_temp_id) = msnt.transaction_temp_id
1693: and mmtt.inventory_item_id = p_inventory_item_id
1710: --Bug no 3589766
1711: --Show serial numbers irrespective of the status of the serial number.
1712: --AND ms.current_status in (3, 4, 5, 7)
1713: AND NOT EXISTS (SELECT w.status
1714: FROM mtl_material_transactions_temp t,
1715: wms_dispatched_tasks w
1716: WHERE w.status = 4
1717: AND w.task_type <> 2
1718: AND w.transaction_temp_id = t.transaction_temp_id
2544: AND nvl(ms.serial_number, '$@#$%') = decode(p_serial_number, NULL, nvl(ms.serial_number, '$@#$%'), p_serial_number)
2545: AND nvl(ms.end_item_unit_number, '$@#$%') = decode(p_unit_number, NULL, nvl(ms.end_item_unit_number, '$@#$%'), p_unit_number)
2546: AND ms.current_status =3
2547: and not exists (select w.status
2548: from mtl_material_transactions_temp t,
2549: wms_dispatched_tasks w
2550: where w.status = 4
2551: and w.task_type <> 2
2552: and w.transaction_temp_id = t.transaction_temp_id