INV_RESERVATIONS_DEMAND

Details

Schema: FUSION
Object owner: INV
Object type: VIEW

Columns

Name
ORGANIZATION_ID ORGANIZATION_CODE INVENTORY_ITEM_ID ITEM_NAME DEMAND_SOURCE_TYPE_ID DEMAND_SOURCE_TYPE_NAME DEMAND_SOURCE_HEADER_ID DEMAND_SOURCE_HEADER_NUMBER DEMAND_SOURCE_LINE_ID DEMAND_SOURCE_LINE_NUMBER DEMAND_SOURCE_NAME RESERVATION_QUANTITY PICK_QUANTITY DEMAND_QUANTITY UOM DUE_DATE CUSTOMER_ID CUSTOMER_NAME SHIP_READY_FLAG

Name

ORGANIZATION_ID

ORGANIZATION_CODE

INVENTORY_ITEM_ID

ITEM_NAME

DEMAND_SOURCE_TYPE_ID

DEMAND_SOURCE_TYPE_NAME

DEMAND_SOURCE_HEADER_ID

DEMAND_SOURCE_HEADER_NUMBER

DEMAND_SOURCE_LINE_ID

DEMAND_SOURCE_LINE_NUMBER

DEMAND_SOURCE_NAME

RESERVATION_QUANTITY

PICK_QUANTITY

DEMAND_QUANTITY

UOM

DUE_DATE

CUSTOMER_ID

CUSTOMER_NAME

SHIP_READY_FLAG

Query

SQL_Statement
SELECT DISTINCT mr.ORGANIZATION_ID, mp.ORGANIZATION_CODE, mr.INVENTORY_ITEM_ID, msi.item_number ITEM_NAME, mr.DEMAND_SOURCE_TYPE_ID, FLVV.meaning DEMAND_SOURCE_TYPE_NAME, mr.DEMAND_SOURCE_HEADER_ID, decode(mr.DEMAND_SOURCE_TYPE_ID,2,iso.source_order_number,8,iso.source_order_number,null) AS DEMAND_SOURCE_HEADER_number, mr.DEMAND_SOURCE_LINE_ID, mr.SOURCE_LINE_NUMBER DEMAND_SOURCE_LINE_NUMBER, mr.DEMAND_SOURCE_NAME, sum(mr.primary_reservation_quantity) over (partition by mr.organization_id,mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id,mr.demand_source_name) Reservation_Quantity, sum(picks.primary_quantity) over (partition by picks.organization_id,picks.transaction_source_type_id,picks.txn_source_id,picks.txn_source_line_id) pick_quantity, sum(mr.primary_reservation_quantity) over (partition by mr.organization_id, mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id,mr.demand_source_name) Demand_Quantity, mr.PRIMARY_UOM_CODE UOM, decode(mr.DEMAND_SOURCE_TYPE_ID,2,mr.REQUIREMENT_DATE,8,mr.REQUIREMENT_DATE,null) AS DUE_DATE, decode(mr.DEMAND_SOURCE_TYPE_ID,2,iso.sold_to_party_id,8,iso.sold_to_party_id,null) AS CUSTOMER_ID, decode(mr.DEMAND_SOURCE_TYPE_ID,2,hp.party_name,8,hp.party_name,null) AS CUSTOMER_NAME, decode(mr.DEMAND_SOURCE_TYPE_ID,2,'N',8,'N',null) as SHIP_READY_FLAG FROM INV_RESERVATIONS MR, INV_ORG_PARAMETERS_V MP, FND_LOOKUP_VALUES_VL FLVV, INV_SALES_ORDERS ISO, HZ_PARTIES HP, EGP_SYSTEM_ITEMS msi, (select mtrl.transaction_source_type_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mmtt.organization_id, sum(mmtt.primary_quantity) primary_quantity from INV_MATERIAL_TXNS_temp mmtt, INV_TXN_REQUEST_HEADERS mtrh, INV_TXN_REQUEST_LINES mtrl Where mtrl.header_id = mtrh.header_id and mmtt.move_order_line_id = mtrl.line_id Group by mtrl.transaction_source_type_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mmtt.organization_id ) picks WHERE ((MR.demand_source_type_id in ('3','6','9','13','29')) OR ( (MR.demand_source_type_id in ('2','8')) AND (not exists (Select 1 from WSH_DELIVERY_DETAILS WDD where WDD.source_header_id = MR.demand_source_header_id and WDD.source_line_id = MR.demand_source_line_id ) ) )) AND mp.organization_id = mr.organization_id AND msi.inventory_item_id = mr.inventory_item_id AND msi.organization_id = mr.organization_id AND flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES' AND flvv.lookup_code = mr.demand_source_type_id AND flvv.view_application_id = 0 AND iso.sales_order_id (+) = mr.DEMAND_SOURCE_HEADER_ID AND hp.party_id (+) = iso.sold_to_party_id AND picks.transaction_source_type_id (+) = mr.DEMAND_SOURCE_TYPE_ID AND picks.txn_source_id (+) = mr.demand_source_header_id AND picks.txn_source_line_id (+) = mr.demand_source_line_id AND picks.organization_id (+) = mr.organization_id UNION ALL SELECT DISTINCT WDD.ORGANIZATION_ID, mp.ORGANIZATION_CODE, WDD.INVENTORY_ITEM_ID, msi.item_number ITEM_NAME, NVL(WDD.SOURCE_DOCUMENT_TYPE_ID,2) AS DEMAND_SOURCE_TYPE_ID, FLVV.meaning DEMAND_SOURCE_TYPE_NAME, WDD.SOURCE_HEADER_ID AS DEMAND_SOURCE_HEADER_ID, WDD.SOURCE_HEADER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER, WDD.SOURCE_LINE_ID AS DEMAND_SOURCE_LINE_ID, WDD.SOURCE_LINE_NUMBER AS DEMAND_SOURCE_LINE_NUMBER, NULL AS DEMAND_SOURCE_NAME, sum(mr.primary_reservation_quantity) over (partition by mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id) Reservation_Quantity, (sum(picksfromresv.primary_quantity) over (partition by picksfromresv.organization_id,picksfromresv.transaction_source_type_id,picksfromresv.txn_source_id,picksfromresv.txn_source_line_id)) pick_quantity, REQUESTED_QUANTITY AS Demand_Quantity, REQUESTED_QUANTITY_UOM AS UOM, WDD.DATE_SCHEDULED AS DUE_DATE, WDD.sold_to_party_id AS CUSTOMER_ID, hp.party_name AS CUSTOMER_NAME, 'Y' as SHIP_READY_FLAG FROM WSH_DELIVERY_DETAILS WDD, INV_ORG_PARAMETERS_V MP, FND_LOOKUP_VALUES_VL FLVV, HZ_PARTIES HP, INV_RESERVATIONS MR, EGP_SYSTEM_ITEMS msi, (select mtrl.transaction_source_type_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mmtt.organization_id, sum(mmtt.primary_quantity) primary_quantity from INV_MATERIAL_TXNS_temp mmtt, INV_TXN_REQUEST_HEADERS mtrh, INV_TXN_REQUEST_LINES mtrl Where mtrl.header_id = mtrh.header_id and mmtt.move_order_line_id = mtrl.line_id Group by mtrl.transaction_source_type_id, mtrl.txn_source_id, mtrl.txn_source_line_id, mmtt.organization_id ) picksfromresv WHERE wdd.released_status NOT IN ('D','Y','C') AND mp.organization_id = wdd.organization_id AND msi.inventory_item_id = wdd.inventory_item_id AND msi.organization_id = wdd.organization_id AND hp.party_id = wdd.ship_to_party_id AND flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES' AND flvv.lookup_code = NVL(wdd.source_document_type_id,2) and flvv.view_application_id = 0 AND hp.party_id = wdd.sold_to_party_id AND mr.demand_source_type_id (+) =wdd.source_document_type_id AND mr.demand_source_header_id (+) = wdd.SOURCE_HEADER_ID AND mr.demand_source_line_id (+) = wdd.SOURCE_LINE_ID AND picksfromresv.transaction_source_type_id (+) = mr.demand_SOURCE_TYPE_ID AND picksfromresv.txn_source_id (+) = mr.demand_source_header_id AND picksfromresv.txn_source_line_id (+) = mr.demand_source_line_id AND picksfromresv.organization_id (+) = mr.organization_id

SQL_Statement

SELECT DISTINCT

mr.ORGANIZATION_ID,

mp.ORGANIZATION_CODE,

mr.INVENTORY_ITEM_ID,

msi.item_number ITEM_NAME,

mr.DEMAND_SOURCE_TYPE_ID,

FLVV.meaning DEMAND_SOURCE_TYPE_NAME,

mr.DEMAND_SOURCE_HEADER_ID,

decode(mr.DEMAND_SOURCE_TYPE_ID,2,iso.source_order_number,8,iso.source_order_number,null) AS DEMAND_SOURCE_HEADER_number,

mr.DEMAND_SOURCE_LINE_ID,

mr.SOURCE_LINE_NUMBER DEMAND_SOURCE_LINE_NUMBER,

mr.DEMAND_SOURCE_NAME,

sum(mr.primary_reservation_quantity) over (partition by mr.organization_id,mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id,mr.demand_source_name) Reservation_Quantity,

sum(picks.primary_quantity) over (partition by picks.organization_id,picks.transaction_source_type_id,picks.txn_source_id,picks.txn_source_line_id) pick_quantity,

sum(mr.primary_reservation_quantity) over (partition by mr.organization_id, mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id,mr.demand_source_name) Demand_Quantity,

mr.PRIMARY_UOM_CODE UOM,

decode(mr.DEMAND_SOURCE_TYPE_ID,2,mr.REQUIREMENT_DATE,8,mr.REQUIREMENT_DATE,null) AS DUE_DATE,

decode(mr.DEMAND_SOURCE_TYPE_ID,2,iso.sold_to_party_id,8,iso.sold_to_party_id,null) AS CUSTOMER_ID,

decode(mr.DEMAND_SOURCE_TYPE_ID,2,hp.party_name,8,hp.party_name,null) AS CUSTOMER_NAME,

decode(mr.DEMAND_SOURCE_TYPE_ID,2,'N',8,'N',null) as SHIP_READY_FLAG

FROM

INV_RESERVATIONS MR,

INV_ORG_PARAMETERS_V MP,

FND_LOOKUP_VALUES_VL FLVV,

INV_SALES_ORDERS ISO,

HZ_PARTIES HP,

EGP_SYSTEM_ITEMS msi,

(select mtrl.transaction_source_type_id,

mtrl.txn_source_id,

mtrl.txn_source_line_id,

mmtt.organization_id,

sum(mmtt.primary_quantity) primary_quantity

from INV_MATERIAL_TXNS_temp mmtt,

INV_TXN_REQUEST_HEADERS mtrh,

INV_TXN_REQUEST_LINES mtrl

Where mtrl.header_id = mtrh.header_id

and mmtt.move_order_line_id = mtrl.line_id

Group by

mtrl.transaction_source_type_id,

mtrl.txn_source_id,

mtrl.txn_source_line_id,

mmtt.organization_id

) picks

WHERE

((MR.demand_source_type_id in ('3','6','9','13','29'))

OR ( (MR.demand_source_type_id in ('2','8'))

AND (not exists (Select 1

from WSH_DELIVERY_DETAILS WDD

where WDD.source_header_id = MR.demand_source_header_id

and WDD.source_line_id = MR.demand_source_line_id

)

))

AND mp.organization_id = mr.organization_id

AND msi.inventory_item_id = mr.inventory_item_id

AND msi.organization_id = mr.organization_id

AND flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES'

AND flvv.lookup_code = mr.demand_source_type_id

AND flvv.view_application_id = 0

AND iso.sales_order_id (+) = mr.DEMAND_SOURCE_HEADER_ID

AND hp.party_id (+) = iso.sold_to_party_id

AND picks.transaction_source_type_id (+) = mr.DEMAND_SOURCE_TYPE_ID

AND picks.txn_source_id (+) = mr.demand_source_header_id

AND picks.txn_source_line_id (+) = mr.demand_source_line_id

AND picks.organization_id (+) = mr.organization_id

UNION ALL

SELECT DISTINCT

WDD.ORGANIZATION_ID,

mp.ORGANIZATION_CODE,

WDD.INVENTORY_ITEM_ID,

msi.item_number ITEM_NAME,

NVL(WDD.SOURCE_DOCUMENT_TYPE_ID,2) AS DEMAND_SOURCE_TYPE_ID,

FLVV.meaning DEMAND_SOURCE_TYPE_NAME,

WDD.SOURCE_HEADER_ID AS DEMAND_SOURCE_HEADER_ID,

WDD.SOURCE_HEADER_NUMBER AS DEMAND_SOURCE_HEADER_NUMBER,

WDD.SOURCE_LINE_ID AS DEMAND_SOURCE_LINE_ID,

WDD.SOURCE_LINE_NUMBER AS DEMAND_SOURCE_LINE_NUMBER,

NULL AS DEMAND_SOURCE_NAME,

sum(mr.primary_reservation_quantity) over (partition by mr.demand_source_type_id, mr.demand_source_header_id,mr.demand_source_line_id) Reservation_Quantity,

(sum(picksfromresv.primary_quantity) over (partition by picksfromresv.organization_id,picksfromresv.transaction_source_type_id,picksfromresv.txn_source_id,picksfromresv.txn_source_line_id)) pick_quantity,

REQUESTED_QUANTITY AS Demand_Quantity,

REQUESTED_QUANTITY_UOM AS UOM,

WDD.DATE_SCHEDULED AS DUE_DATE,

WDD.sold_to_party_id AS CUSTOMER_ID,

hp.party_name AS CUSTOMER_NAME,

'Y' as SHIP_READY_FLAG

FROM

WSH_DELIVERY_DETAILS WDD,

INV_ORG_PARAMETERS_V MP,

FND_LOOKUP_VALUES_VL FLVV,

HZ_PARTIES HP,

INV_RESERVATIONS MR,

EGP_SYSTEM_ITEMS msi,

(select mtrl.transaction_source_type_id,

mtrl.txn_source_id,

mtrl.txn_source_line_id,

mmtt.organization_id,

sum(mmtt.primary_quantity) primary_quantity

from INV_MATERIAL_TXNS_temp mmtt,

INV_TXN_REQUEST_HEADERS mtrh,

INV_TXN_REQUEST_LINES mtrl

Where mtrl.header_id = mtrh.header_id

and mmtt.move_order_line_id = mtrl.line_id

Group by

mtrl.transaction_source_type_id,

mtrl.txn_source_id,

mtrl.txn_source_line_id,

mmtt.organization_id

) picksfromresv

WHERE wdd.released_status NOT IN ('D','Y','C')

AND mp.organization_id = wdd.organization_id

AND msi.inventory_item_id = wdd.inventory_item_id

AND msi.organization_id = wdd.organization_id

AND hp.party_id = wdd.ship_to_party_id

AND flvv.lookup_type = 'INV_RESERVATION_SOURCE_TYPES'

AND flvv.lookup_code = NVL(wdd.source_document_type_id,2)

and flvv.view_application_id = 0

AND hp.party_id = wdd.sold_to_party_id

AND mr.demand_source_type_id (+) =wdd.source_document_type_id

AND mr.demand_source_header_id (+) = wdd.SOURCE_HEADER_ID

AND mr.demand_source_line_id (+) = wdd.SOURCE_LINE_ID

AND picksfromresv.transaction_source_type_id (+) = mr.demand_SOURCE_TYPE_ID

AND picksfromresv.txn_source_id (+) = mr.demand_source_header_id

AND picksfromresv.txn_source_line_id (+) = mr.demand_source_line_id

AND picksfromresv.organization_id (+) = mr.organization_id