MSC_AP_HARD_RESERVATIONS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
TRANSACTION_ID INVENTORY_ITEM_ID ITEM_NAME SUPPLY_SOURCE_TYPE_ID ORGANIZATION_ID ORGANIZATION_CODE REQUIREMENT_DATE RESERVED_QUANTITY DISPOSITION_TYPE DISPOSITION_ID SUB_INVENTORY_CODE LOT_NUMBER ROW_ID PJC_PROJECT_ID PJC_TASK_ID WORK_ORDER_NUMBER OP_SEQ

Query

SQL_Statement

SELECT DECODE(SUPPLY_SOURCE_TYPE_ID,5,supply_source_header_id,7,supply_source_header_id,SUPPLY_SOURCE_LINE_ID) TRANSACTION_ID , resv.INVENTORY_ITEM_ID , ITEMSV.ITEM_NUMBER ITEM_NAME , SUPPLY_SOURCE_TYPE_ID, resv.ORGANIZATION_ID, ITEMSV.organization_code ORGANIZATION_CODE , REQUIREMENT_DATE , PRIMARY_RESERVATION_QUANTITY RESERVED_QUANTITY, DEMAND_SOURCE_TYPE_ID DISPOSITION_TYPE, source_fulfillment_line_id DISPOSITION_ID , DECODE(SUPPLY_SOURCE_TYPE_ID,13,SUBINVENTORY_CODE,NULL) SUB_INVENTORY_CODE, DECODE(SUPPLY_SOURCE_TYPE_ID,13,LOT_NUMBER,NULL) LOT_NUMBER, resv.rowid ROW_ID, resv.PROJECT_ID PJC_PROJECT_ID, resv.TASK_ID PJC_TASK_ID, null work_order_number , null op_seq FROM INV_RESERVATIONS resv, MSC_PLANNED_EGP_SYSTEM_ITEMS_V ITEMSV WHERE SUPPLY_SOURCE_TYPE_ID IN ( 5,7,13,8,1,17) AND DEMAND_SOURCE_TYPE_ID IN (2,8) AND ITEMSV.INVENTORY_ITEM_ID = resv.INVENTORY_ITEM_ID AND ITEMSV.organization_id = resv.organization_id AND resv.source_fulfillment_line_id IS NOT NULL UNION ALL SELECT DECODE(supply_source_type_id, 5, supply_source_header_id, 7, supply_source_header_id, supply_source_line_id) transaction_id, resv.inventory_item_id, itemsv.item_number item_name, supply_source_type_id, resv.organization_id, itemsv.organization_code organization_code, requirement_date, primary_reservation_quantity reserved_quantity, demand_source_type_id disposition_type, wdd.source_shipment_id disposition_id, DECODE(supply_source_type_id, 13, subinventory_code, NULL) sub_inventory_code, DECODE(resv.supply_source_type_id,13, resv.lot_number, NULL) lot_number, resv.rowid row_id, resv.project_id pjc_project_id, resv.task_id pjc_task_id, NULL work_order_number, NULL op_seq FROM inv_reservations resv, msc_planned_egp_system_items_v itemsv, wsh_delivery_details wdd WHERE supply_source_type_id IN ( 5, 7, 13, 8, 1, 17 ) AND demand_source_type_id IN ( 2, 8 ) AND itemsv.inventory_item_id = resv.inventory_item_id AND itemsv.organization_id = resv.organization_id AND wdd.source_header_id = resv.demand_source_header_id AND wdd.source_line_id = resv.demand_source_line_id AND wdd.inventory_item_id = resv.inventory_item_id AND wdd.organization_id = resv.organization_id AND wdd.source_shipment_id is not null AND resv.source_fulfillment_line_id IS NULL union all SELECT DECODE(SUPPLY_SOURCE_TYPE_ID,5,supply_source_header_id,7,supply_source_header_id,SUPPLY_SOURCE_LINE_ID) TRANSACTION_ID , resv.INVENTORY_ITEM_ID , ITEMSV.ITEM_NUMBER ITEM_NAME , SUPPLY_SOURCE_TYPE_ID, resv.ORGANIZATION_ID, ITEMSV.organization_code ORGANIZATION_CODE , REQUIREMENT_DATE , PRIMARY_RESERVATION_QUANTITY RESERVED_QUANTITY, DEMAND_SOURCE_TYPE_ID DISPOSITION_TYPE, resv.demand_source_line_id DISPOSITION_ID , DECODE(SUPPLY_SOURCE_TYPE_ID,13,SUBINVENTORY_CODE,NULL) SUB_INVENTORY_CODE, DECODE(SUPPLY_SOURCE_TYPE_ID,13,LOT_NUMBER,NULL) LOT_NUMBER, resv.rowid ROW_ID, resv.PROJECT_ID PJC_PROJECT_ID, resv.TASK_ID PJC_TASK_ID, wo.work_order_number , wop.operation_seq_number op_seq FROM INV_RESERVATIONS resv, WIE_WORK_ORDERS_B wo, WIE_WO_OPERATIONS_B wop, MSC_PLANNED_EGP_SYSTEM_ITEMS_V ITEMSV WHERE SUPPLY_SOURCE_TYPE_ID IN ( 5,7,13,8,1,17) AND DEMAND_SOURCE_TYPE_ID = 5 and ITEMSV.INVENTORY_ITEM_ID = resv.INVENTORY_ITEM_ID and ITEMSV.organization_id = resv.organization_id and resv.demand_source_header_id = wo.work_order_id and wo.work_order_id = wop.work_order_id and resv.demand_source_line_id=wop.wo_operation_id