WIE_WO_RESERVATIONS_V
Details
-
Schema: FUSION
-
Object owner: WIE
-
Object type: VIEW
Columns
Name |
---|
RESERVATION_ID ORGANIZATION_ID INVENTORY_ITEM_ID DEMAND_SOURCE_TYPE_ID DEMAND_SOURCE_TYPE_ID_DERIVED DEMAND_SOURCE_HEADER_ID DEMAND_SOURCE_HEADER_NUMBER DEMAND_SOURCE_LINE_ID DEMAND_SOURCE_LINE_NUMBER DEMAND_SOURCE_NAME RESERVATION_QUANTITY PRIMARY_RESERVATION_QUANTITY RESERVATION_UOM_CODE PRIMARY_UOM_CODE DEMAND_QUANTITY DEMAND_UOM DUE_DATE CUSTOMER_NUMBER CUSTOMER_NAME CUSTOMER_TYPE SUPPLY_SOURCE_HEADER_ID SRC_FULFILL_LINE_ID ATTACHMENT_ENTITY_NAME LINE_ENTITY_NAME |
Query
SQL_Statement |
---|
SELECT InventoryReservationPEO.reservation_id, InventoryReservationPEO.organization_id, InventoryReservationPEO.inventory_item_id, InventoryReservationPEO.demand_source_type_id, TO_CHAR(InventoryReservationPEO.demand_source_type_id) as demand_source_type_id_derived, InventoryReservationPEO.demand_source_header_id, DECODE(InventoryReservationPEO.demand_source_type_id, 2,NVL(SalesOrderPEO.sales_order_number,InventoryReservationPEO.demand_source_header_id), 8,NVL(SalesOrderPEO.sales_order_number,InventoryReservationPEO.demand_source_header_id), 18,NVL(SalesOrderPEO.sales_order_number,InventoryReservationPEO.demand_source_header_id), 3,(SELECT MAX( fnd_flex_ext.get_segs('GL','GL#',CodeCombinationPEO.chart_of_accounts_id,InventoryReservationPEO.demand_source_header_id)) accountsegments FROM gl_code_combinations CodeCombinationPEO WHERE code_combination_id = InventoryReservationPEO.demand_source_header_id ), 6,(SELECT MAX( fnd_flex_ext.get_segs('INV','MDSP',AccountAliasPEO.structure_instance_number,InventoryReservationPEO.demand_source_header_id,InventoryReservationPEO.organization_id)) accountaliassegs FROM inv_generic_dispositions AccountAliasPEO WHERE disposition_id = InventoryReservationPEO.demand_source_header_id AND organization_id = InventoryReservationPEO.organization_id ), 13,InventoryReservationPEO.demand_source_name, NULL) AS demand_source_header_number, InventoryReservationPEO.demand_source_line_id, DECODE(sales_order_shipment_number,NULL,DECODE(sales_order_line_number,NULL,TO_CHAR(demand_source_line_id),sales_order_line_number), sales_order_line_number ||DECODE(sales_order_line_number,NULL,NULL,'-') ||sales_order_shipment_number) AS demand_source_line_number, InventoryReservationPEO.demand_source_name, InventoryReservationPEO.reservation_quantity, InventoryReservationPEO.primary_reservation_quantity, InventoryReservationPEO.reservation_uom_code, InventoryReservationPEO.primary_uom_code, (SELECT SUM(InventoryReservationPEO1.primary_reservation_quantity) FROM inv_reservations InventoryReservationPEO1 WHERE InventoryReservationPEO1.demand_source_header_id=InventoryReservationPEO.demand_source_header_id AND InventoryReservationPEO1.demand_source_line_id =InventoryReservationPEO.demand_source_line_id ) AS demand_quantity, InventoryReservationPEO.primary_uom_code AS demand_uom, DECODE(InventoryReservationPEO.demand_source_type_id,2,InventoryReservationPEO.requirement_date,8,InventoryReservationPEO.requirement_date,18,InventoryReservationPEO.requirement_date,NULL) AS due_date, DECODE(InventoryReservationPEO.demand_source_type_id,2,PartyPEO1.party_number,8,PartyPEO1.party_number,18,PartyPEO1.party_number,NULL) AS customer_number, DECODE(InventoryReservationPEO.demand_source_type_id,2,PartyPEO1.party_name,8,PartyPEO1.party_name,18,PartyPEO1.party_name,NULL) AS customer_name, PartyPEO1.party_type AS customer_type, InventoryReservationPEO.supply_source_header_id, InventoryReservationPEO.source_fulfillment_line_id AS src_fulfill_line_id, 'DOO_HEADERS_ALL' AS attachment_entity_name, 'DOO_FULFILL_LINES_ALL' AS line_entity_name FROM inv_reservations InventoryReservationPEO, inv_sales_orders SalesOrderPEO, hz_parties PartyPEO1 WHERE InventoryReservationPEO.supply_source_type_id = 5 AND SalesOrderPEO.sales_order_id (+) = InventoryReservationPEO.demand_source_header_id AND PartyPEO1.party_id (+) = SalesOrderPEO.sold_to_party_id AND (NOT EXISTS (SELECT 1 FROM wsh_delivery_details DeliveryLinePEO WHERE DeliveryLinePEO.source_header_id = InventoryReservationPEO.demand_source_header_id AND DeliveryLinePEO.source_line_id = InventoryReservationPEO.demand_source_line_id AND DeliveryLinePEO.released_status NOT IN ('X','D') ) ) UNION ALL SELECT DISTINCT InventoryReservationPEO.reservation_id, DeliveryLinePEO.organization_id, DeliveryLinePEO.inventory_item_id, InventoryReservationPEO.demand_source_type_id, TO_CHAR(InventoryReservationPEO.demand_source_type_id) as demand_source_type_id_derived, DeliveryLinePEO.source_header_id AS demand_source_header_id, DeliveryLinePEO.sales_order_number AS demand_source_header_number, DeliveryLinePEO.source_line_id AS demand_source_line_id, DECODE(DeliveryLinePEO.sales_order_shipment_number,NULL,DECODE(DeliveryLinePEO.sales_order_line_number,NULL,TO_CHAR(DeliveryLinePEO.source_line_id), DeliveryLinePEO.sales_order_line_number), DeliveryLinePEO.sales_order_line_number ||DECODE(DeliveryLinePEO.sales_order_line_number,NULL,NULL,'-') ||DeliveryLinePEO.sales_order_shipment_number) AS demand_source_line_number, InventoryReservationPEO.demand_source_name, InventoryReservationPEO.reservation_quantity, InventoryReservationPEO.primary_reservation_quantity, InventoryReservationPEO.reservation_uom_code, InventoryReservationPEO.primary_uom_code, src_requested_quantity AS demand_quantity, src_requested_quantity_uom AS demand_uom, DeliveryLinePEO.date_scheduled AS due_date, PartyPEO1.party_number AS customer_number, PartyPEO1.party_name AS customer_name, PartyPEO1.party_type AS customer_type, InventoryReservationPEO.supply_source_header_id, InventoryReservationPEO.source_fulfillment_line_id AS src_fulfill_line_id, 'DOO_HEADERS_ALL' AS attachment_entity_name, 'DOO_FULFILL_LINES_ALL' AS line_entity_name FROM inv_reservations InventoryReservationPEO, wsh_delivery_details_v DeliveryLinePEO, hz_parties PartyPEO1 WHERE InventoryReservationPEO.supply_source_type_id = 5 AND InventoryReservationPEO.organization_id = DeliveryLinePEO.organization_id AND InventoryReservationPEO.demand_source_header_id = DeliveryLinePEO.source_header_id AND InventoryReservationPEO.demand_source_line_id = DeliveryLinePEO.source_line_id AND DeliveryLinePEO.released_status NOT IN ('X','D') AND PartyPEO1.party_id (+) = DeliveryLinePEO.sold_to_party_id |