[Home] [Help]
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
432: * 1. The first SQL fetches the content information for not lot controlled items
584: NVL(mmtt.lpn_id,mmtt.content_lpn_id)
585: --lpn status project end
586:
587: FROM mtl_material_transactions_temp mmtt,
588: mtl_transaction_lots_temp mtlt,
589: wms_license_plate_numbers lpn,
590: wms_license_plate_numbers lpn3,
591: mtl_parameters mp,
592: cst_cost_groups ccg,
1483: SELECT mmst.status_code, mmst.status_id,
1484: msik.shelf_life_code, msik.lot_status_enabled, mtlt.lot_expiration_date
1485: FROM mtl_material_statuses_vl mmst,
1486: mtl_system_items_kfv msik,
1487: mtl_transaction_lots_temp mtlt,
1488: mtl_material_transactions_temp mmtt
1489: WHERE mtlt.lot_number = p_lot_number
1490: AND mmtt.organization_id = p_organization_id
1491: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1533: SELECT mmst.status_code, msik.serial_status_enabled, mmst.status_id
1534: FROM mtl_material_statuses_vl mmst,
1535: mtl_serial_numbers_temp msnt,
1536: mtl_material_transactions_temp mmtt,
1537: mtl_transaction_lots_temp mtlt,
1538: mtl_system_items_b msik
1539: WHERE mmtt.organization_id = p_organization_id
1540: AND mmtt.inventory_item_id = p_inventory_item_id
1541: AND msik.organization_id = mmtt.organization_id
1762: OPEN x_serial_numbers FOR
1763: SELECT msnt.fm_serial_number, mmtt.transfer_lpn_id, mtlt.lot_number
1764: FROM mtl_serial_numbers_temp msnt,
1765: mtl_material_transactions_temp mmtt,
1766: mtl_transaction_lots_temp mtlt
1767: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1768: and nvl(mtlt.serial_transaction_temp_id,mmtt.transaction_temp_id) = msnt.transaction_temp_id
1769: and mmtt.inventory_item_id = p_inventory_item_id
1770: and nvl(mmtt.revision,'$@#$%') = DECODE(p_revision,NULL,nvl(mmtt.revision,'$@#$%'),p_revision)