[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
5588: AND inventory_item_id <> p_inventory_item_id
5589: UNION ALL
5590: --pending receipts and putaway suggestions
5591: SELECT inventory_item_id
5592: FROM mtl_material_transactions_temp
5593: WHERE organization_id = p_organization_id
5594: AND subinventory_code = p_subinventory_code
5595: AND locator_id = p_locator_id
5596: AND inventory_item_id <> p_inventory_item_id
5597: AND transaction_action_id IN (12, 27, 31)
5598: UNION ALL
5599: --pending transfers and suggestions
5600: SELECT inventory_item_id
5601: FROM mtl_material_transactions_temp
5602: WHERE organization_id = p_organization_id
5603: AND transfer_subinventory = p_subinventory_code
5604: AND transfer_to_location = p_locator_id
5605: AND inventory_item_id <> p_inventory_item_id
5667: AND lot_number <> p_lot_number
5668: UNION ALL
5669: --pending receipts and putaway suggestions (lot in MMTT)
5670: SELECT lot_number
5671: FROM mtl_material_transactions_temp
5672: WHERE organization_id = p_organization_id
5673: AND inventory_item_id = p_inventory_item_id
5674: AND subinventory_code = p_subinventory_code
5675: AND locator_id = p_locator_id
5678: AND transaction_action_id IN (12, 27, 31)
5679: UNION ALL
5680: --pending transfers and suggestions (lot in MMTT)
5681: SELECT lot_number
5682: FROM mtl_material_transactions_temp
5683: WHERE organization_id = p_organization_id
5684: AND inventory_item_id = p_inventory_item_id
5685: AND transfer_subinventory = p_subinventory_code
5686: AND transfer_to_location = p_locator_id
5689: AND transaction_action_id IN (2, 3, 28)
5690: UNION ALL
5691: --pending receipts and putaway suggestions (lot in MTLT)
5692: SELECT mtlt.lot_number
5693: FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5694: WHERE mmtt.organization_id = p_organization_id
5695: AND mmtt.inventory_item_id = p_inventory_item_id
5696: AND mmtt.subinventory_code = p_subinventory_code
5697: AND mmtt.locator_id = p_locator_id
5701: AND mtlt.lot_number <> p_lot_number
5702: UNION ALL
5703: --pending transfers and suggestions (lot in MTLT)
5704: SELECT mtlt.lot_number
5705: FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
5706: WHERE mmtt.organization_id = p_organization_id
5707: AND mmtt.inventory_item_id = p_inventory_item_id
5708: AND mmtt.transfer_subinventory = p_subinventory_code
5709: AND mmtt.transfer_to_location = p_locator_id
5775: AND revision <> p_revision
5776: UNION ALL
5777: --pending receipts and putaway suggestions
5778: SELECT revision
5779: FROM mtl_material_transactions_temp
5780: WHERE organization_id = p_organization_id
5781: AND subinventory_code = p_subinventory_code
5782: AND locator_id = p_locator_id
5783: AND inventory_item_id = p_inventory_item_id
5786: AND transaction_action_id IN (12, 27, 31)
5787: UNION ALL
5788: --pending transfer txns and suggestions
5789: SELECT revision
5790: FROM mtl_material_transactions_temp
5791: WHERE organization_id = p_organization_id
5792: AND transfer_subinventory = p_subinventory_code
5793: AND transfer_to_location = p_locator_id
5794: AND inventory_item_id = p_inventory_item_id
6018: INTO l_delivery_id
6019: FROM
6020: wsh_delivery_assignments_v wda,
6021: wsh_delivery_details wdd,
6022: mtl_material_transactions_temp mmtt
6023: WHERE
6024: mmtt.cartonization_id = p_lpn_id
6025: AND mmtt.move_order_line_id = wdd.move_order_line_id -- kkoothan Removed the NVL as part of Bug Fix:2631051
6026: AND wdd.delivery_detail_id = wda.delivery_detail_id
6045: SELECT 'Y'
6046: INTO multiple_lpns
6047: FROM DUAL
6048: WHERE EXISTS( SELECT mmtt.transaction_temp_id
6049: FROM mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments_v wda
6050: WHERE NVL(mmtt.cartonization_id, -1) <> p_lpn_id
6051: AND mmtt.move_order_line_id = wdd.move_order_line_id
6052: AND wdd.delivery_detail_id = wda.delivery_detail_id
6053: AND wda.delivery_id = l_delivery_id);
6113:
6114: BEGIN
6115: SELECT mtrh.move_order_type
6116: INTO l_hdr_type
6117: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
6118: WHERE mmtt.transaction_temp_id = p_transaction_temp_id
6119: AND mtrl.line_id = mmtt.move_order_line_id
6120: AND mtrh.header_id = mtrl.header_id;
6121:
6133: SELECT 'Y'
6134: INTO l_ret
6135: FROM DUAL
6136: WHERE EXISTS( SELECT transaction_temp_id
6137: FROM mtl_material_transactions_temp
6138: WHERE transaction_temp_id = p_transaction_temp_id
6139: AND transaction_type_id IN (inv_globals.g_type_xfer_order_wip_issue, inv_globals.g_type_xfer_order_repl_subxfr));
6140:
6141: RETURN l_ret;