[Home] [Help]
4489: , 2, -ABS(mmtt.primary_quantity)
4490: , 3, -ABS(mmtt.primary_quantity)
4491: , mmtt.primary_quantity
4492: )
4493: FROM mtl_material_transactions_temp mmtt
4494: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4495: AND mmtt.posting_flag = 'Y' -- pending txn
4496: AND NVL(transaction_status, -1) <> 2 -- not suggestions
4497: UNION ALL
4500: , mmtt.inventory_item_id
4501: , mmtt.transfer_subinventory
4502: , mmtt.transfer_to_location
4503: , mmtt.primary_quantity
4504: FROM mtl_material_transactions_temp mmtt
4505: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4506: AND mmtt.posting_flag = 'Y' -- pending txn
4507: AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4508: AND mmtt.transaction_action_id IN (2, 3) -- transfers
4519: , mmtt.subinventory_code
4520: )
4521: , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4522: , ABS(mmtt.primary_quantity)
4523: FROM mtl_material_transactions_temp mmtt
4524: WHERE mmtt.posting_flag = 'Y'
4525: AND mmtt.transaction_status = 2 -- suggestions
4526: AND mmtt.transaction_action_id IN -- put away
4527: (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4564: , 2, -ABS(mmtt.primary_quantity)
4565: , 3, -ABS(mmtt.primary_quantity)
4566: , mmtt.primary_quantity
4567: )
4568: FROM mtl_material_transactions_temp mmtt
4569: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4570: AND mmtt.posting_flag = 'Y' -- pending txn
4571: AND NVL(transaction_status, -1) <> 2 -- not suggestions
4572: UNION ALL
4575: , mmtt.inventory_item_id
4576: , mmtt.transfer_subinventory
4577: , mmtt.transfer_to_location
4578: , mmtt.primary_quantity
4579: FROM mtl_material_transactions_temp mmtt
4580: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4581: AND mmtt.posting_flag = 'Y' -- pending txn
4582: AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4583: AND mmtt.transaction_action_id IN (2, 3) -- transfers
4594: , mmtt.subinventory_code
4595: )
4596: , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4597: , ABS(mmtt.primary_quantity)
4598: FROM mtl_material_transactions_temp mmtt
4599: WHERE mmtt.posting_flag = 'Y'
4600: AND mmtt.transaction_status = 2 -- suggestions
4601: AND mmtt.transaction_action_id IN -- put away
4602: (2, 3, 12, 27, 31, 33) -- only receipts and transfer
4640: , 2, -ABS(mmtt.primary_quantity)
4641: , 3, -ABS(mmtt.primary_quantity)
4642: , mmtt.primary_quantity
4643: )
4644: FROM mtl_material_transactions_temp mmtt
4645: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4646: AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4647: AND mmtt.posting_flag = 'Y' -- pending txn
4648: AND NVL(transaction_status, -1) <> 2 -- not suggestions
4652: , mmtt.inventory_item_id
4653: , mmtt.transfer_subinventory
4654: , mmtt.transfer_to_location
4655: , mmtt.primary_quantity
4656: FROM mtl_material_transactions_temp mmtt
4657: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4658: AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4659: AND mmtt.posting_flag = 'Y' -- pending txn
4660: AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4672: , mmtt.subinventory_code
4673: )
4674: , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4675: , ABS(mmtt.primary_quantity)
4676: FROM mtl_material_transactions_temp mmtt
4677: WHERE mmtt.posting_flag = 'Y'
4678: AND nvl(mmtt.locator_id, 0) > 0 -- Added for bug # 4493640
4679: AND mmtt.transaction_status = 2 -- suggestions
4680: AND mmtt.transaction_action_id IN -- put away
4895: , 2, -ABS(mmtt.primary_quantity)
4896: , 3, -ABS(mmtt.primary_quantity)
4897: , mmtt.primary_quantity
4898: )
4899: FROM mtl_material_transactions_temp mmtt
4900: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4901: AND mmtt.posting_flag = 'Y' -- pending txn
4902: AND NVL(transaction_status, -1) <> 2 -- not suggestions
4903: UNION ALL
4906: , mmtt.inventory_item_id
4907: , mmtt.transfer_subinventory
4908: , mmtt.transfer_to_location
4909: , mmtt.primary_quantity
4910: FROM mtl_material_transactions_temp mmtt
4911: WHERE mmtt.inventory_item_id > 0 -- Index !!!
4912: AND mmtt.posting_flag = 'Y' -- pending txn
4913: AND NVL(mmtt.transaction_status, -1) <> 2 -- not suggestions
4914: AND mmtt.transaction_action_id IN (2, 3) -- transfers
4925: , mmtt.subinventory_code
4926: )
4927: , DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, 3, mmtt.transfer_to_location, mmtt.locator_id)
4928: , ABS(mmtt.primary_quantity)
4929: FROM mtl_material_transactions_temp mmtt
4930: WHERE mmtt.transaction_status = 2 -- suggestions
4931: -- AND mmtt.posting_flag = 'Y' /* 3446963 */
4932: AND mmtt.transaction_action_id IN -- put away
4933: (2, 3, 12, 27, 31, 33) -- only receipts and transfer
5574: AND inventory_item_id <> p_inventory_item_id
5575: UNION ALL
5576: --pending receipts and putaway suggestions
5577: SELECT inventory_item_id
5578: FROM mtl_material_transactions_temp
5579: WHERE organization_id = p_organization_id
5580: AND subinventory_code = p_subinventory_code
5581: AND locator_id = p_locator_id
5582: AND inventory_item_id <> p_inventory_item_id
5583: AND transaction_action_id IN (12, 27, 31)
5584: UNION ALL
5585: --pending transfers and suggestions
5586: SELECT inventory_item_id
5587: FROM mtl_material_transactions_temp
5588: WHERE organization_id = p_organization_id
5589: AND transfer_subinventory = p_subinventory_code
5590: AND transfer_to_location = p_locator_id
5591: AND inventory_item_id <> p_inventory_item_id
5653: AND lot_number <> p_lot_number
5654: UNION ALL
5655: --pending receipts and putaway suggestions (lot in MMTT)
5656: SELECT lot_number
5657: FROM mtl_material_transactions_temp
5658: WHERE organization_id = p_organization_id
5659: AND inventory_item_id = p_inventory_item_id
5660: AND subinventory_code = p_subinventory_code
5661: AND locator_id = p_locator_id
5664: AND transaction_action_id IN (12, 27, 31)
5665: UNION ALL
5666: --pending transfers and suggestions (lot in MMTT)
5667: SELECT lot_number
5668: FROM mtl_material_transactions_temp
5669: WHERE organization_id = p_organization_id
5670: AND inventory_item_id = p_inventory_item_id
5671: AND transfer_subinventory = p_subinventory_code
5672: AND transfer_to_location = p_locator_id
5675: AND transaction_action_id IN (2, 3, 28)
5676: UNION ALL
5677: --pending receipts and putaway suggestions (lot in MTLT)
5678: SELECT mtlt.lot_number
5679: FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5680: WHERE mmtt.organization_id = p_organization_id
5681: AND mmtt.inventory_item_id = p_inventory_item_id
5682: AND mmtt.subinventory_code = p_subinventory_code
5683: AND mmtt.locator_id = p_locator_id
5687: AND mtlt.lot_number <> p_lot_number
5688: UNION ALL
5689: --pending transfers and suggestions (lot in MTLT)
5690: SELECT mtlt.lot_number
5691: FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5692: WHERE mmtt.organization_id = p_organization_id
5693: AND mmtt.inventory_item_id = p_inventory_item_id
5694: AND mmtt.transfer_subinventory = p_subinventory_code
5695: AND mmtt.transfer_to_location = p_locator_id
5761: AND revision <> p_revision
5762: UNION ALL
5763: --pending receipts and putaway suggestions
5764: SELECT revision
5765: FROM mtl_material_transactions_temp
5766: WHERE organization_id = p_organization_id
5767: AND subinventory_code = p_subinventory_code
5768: AND locator_id = p_locator_id
5769: AND inventory_item_id = p_inventory_item_id
5772: AND transaction_action_id IN (12, 27, 31)
5773: UNION ALL
5774: --pending transfer txns and suggestions
5775: SELECT revision
5776: FROM mtl_material_transactions_temp
5777: WHERE organization_id = p_organization_id
5778: AND transfer_subinventory = p_subinventory_code
5779: AND transfer_to_location = p_locator_id
5780: AND inventory_item_id = p_inventory_item_id
6004: INTO l_delivery_id
6005: FROM
6006: wsh_delivery_assignments_v wda,
6007: wsh_delivery_details wdd,
6008: mtl_material_transactions_temp mmtt
6009: WHERE
6010: mmtt.cartonization_id = p_lpn_id
6011: AND mmtt.move_order_line_id = wdd.move_order_line_id -- kkoothan Removed the NVL as part of Bug Fix:2631051
6012: AND wdd.delivery_detail_id = wda.delivery_detail_id
6031: SELECT 'Y'
6032: INTO multiple_lpns
6033: FROM DUAL
6034: WHERE EXISTS( SELECT mmtt.transaction_temp_id
6035: FROM mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments_v wda
6036: WHERE NVL(mmtt.cartonization_id, -1) <> p_lpn_id
6037: AND mmtt.move_order_line_id = wdd.move_order_line_id
6038: AND wdd.delivery_detail_id = wda.delivery_detail_id
6039: AND wda.delivery_id = l_delivery_id);
6099:
6100: BEGIN
6101: SELECT mtrh.move_order_type
6102: INTO l_hdr_type
6103: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
6104: WHERE mmtt.transaction_temp_id = p_transaction_temp_id
6105: AND mtrl.line_id = mmtt.move_order_line_id
6106: AND mtrh.header_id = mtrl.header_id;
6107:
6119: SELECT 'Y'
6120: INTO l_ret
6121: FROM DUAL
6122: WHERE EXISTS( SELECT transaction_temp_id
6123: FROM mtl_material_transactions_temp
6124: WHERE transaction_temp_id = p_transaction_temp_id
6125: AND transaction_type_id IN (inv_globals.g_type_xfer_order_wip_issue, inv_globals.g_type_xfer_order_repl_subxfr));
6126:
6127: RETURN l_ret;