INV_PICKS_RSVS_V

Details

• Schema: FUSION

• Object owner: FUSION

• Object type: VIEW

Columns

Name
OBJECTCHOICE ORGANIZATION_ID INVENTORY_ITEM_ID LOT_NUMBER SUBINVENTORY_CODE LOCATOR_ID DEMAND_SOURCE_TYPE_ID DEMAND_SOURCE_HEADER_ID DEMAND_SOURCE_LINE_ID DEMAND_SOURCE_NAME SUPPLY_SOURCE_TYPE_ID DUE_DATE SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_LINE_ID REVISION PROJECT_ID TASK_ID ORDER_INTERFACE_STATUS

Query

SQL_Statement

SELECT /* Pick Demand for Cases where Reservation rows match */ /* Reservation rows match with the criteria Specified */ '1' ObjectChoice , mr.organization_id , mr.inventory_item_id , mr.lot_number lot_number , mr.Subinventory_Code , mr.Locator_id , mr.Demand_source_type_id , mr.Demand_source_header_id, mr.Demand_source_line_id , mr.Demand_source_name , mr.supply_source_type_id , mr.requirement_date due_date , mr.supply_source_header_id supply_source_header_id , mr.supply_source_line_id supply_source_line_id , mr.revision revision, mr.project_id, mr.task_id, mr.order_interface_status FROM INV_RESERVATIONS mr UNION ALL /* Picks which are Reservations and matched with the */ /* Serach Criteria Specified by User */ /* Pickup only Pick wave move Order for this case */ SELECT '2' ObjectChoice, /* Picks */ mtl.organization_id , mtl.inventory_item_id , mtlt.lot_number , mmtt.Subinventory_code , mmtt.Locator_id , mr.Demand_source_type_id , mr.demand_source_header_id Demand_source_header_id, mr.demand_source_line_id Demand_source_line_id , mr.demand_source_name demand_source_name , 13 supply_source_type_id , mtl.date_required due_date , 0 supply_source_header_id , 0 supply_source_line_id , NULL revision , mmtt.project_id, mmtt.task_id, mr.order_interface_status FROM INV_RESERVATIONS mr , INV_TXN_REQUEST_HEADERS mth, INV_TXN_REQUEST_LINES mtl , INV_MATERIAL_TXNS_temp mmtt, INV_TRANSACTION_LOTS_TEMP mtlt WHERE mr.demand_source_header_id = mtl.txn_source_id AND mr.demand_source_line_id = mtl.txn_source_line_id AND mth.move_order_type IN ('3') AND mth.header_id = mtl.header_id AND mmtt.move_order_line_id = mtl.line_id AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+) AND mmtt.transaction_status =2 /* Pure Picks which are not reservations */ /* MMTT rows Must Exist for this case */ /* MTLT may or may not exist */ UNION ALL SELECT '2' ObjectChoice, /* Picks */ mtl.organization_id , mtl.inventory_item_id , mtlt.lot_number , mmtt.Subinventory_code , mmtt.Locator_id , decode(mth.move_order_type,5,5,4) as Demand_source_type_id, /*hard Coded as 4 for Move Order */ decode(mth.move_order_type,5,mtl.txn_source_id,mth.header_id) Demand_source_header_id, decode(mth.move_order_type,5,mmtt.operation_seq_number_id,mtl.line_id) Demand_source_line_id , decode(mth.move_order_type,5,null,mth.request_number) demand_source_name , 13 supply_source_type_id , mtl.date_required due_date , 0 supply_source_header_id , 0 supply_source_line_id , NULL revision, mmtt.project_id, mmtt.task_id, null as order_interface_status FROM INV_TXN_REQUEST_HEADERS mth, INV_TXN_REQUEST_LINES mtl , INV_MATERIAL_TXNS_temp mmtt , INV_TRANSACTION_LOTS_TEMP mtlt WHERE mth.move_order_type IN ('1','2','5') AND mth.header_id = mtl.header_id AND mmtt.move_order_line_id = mtl.line_id AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+) AND mmtt.transaction_status =2