1. Tables and Views for SCM
  2. WIE_WO_RESERVATIONS_V

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