VIEW: APPS.MST_SUPPLIER_ORDERS_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, MDD.SUPPLIER_ID, FLP.FACILITY_ID, MDD.SOURCE_CODE, MDD.SOURCE_HEADER_NUMBER, MDD.TP_REFER_HEADER_NUMBER, MDD.REFERENCE_SOURCE_TYPE, SUM(MDD.ALLOCATED_COST) COST, SUM(MDD.GROSS_WEIGHT) WEIGHT, SUM(MDD.VOLUME) CUBE, SUM(CEIL(MDD.NUMBER_OF_PALLETS)) PALLETS, SUM(MDD.REQUESTED_QUANTITY) PIECES, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_DELIVERY_DETAILS MDD, MST_PLANS MP, WSH_LOCATIONS WL, FTE_LOCATION_PARAMETERS FLP WHERE MP.PLAN_ID = MDD.PLAN_ID AND MDD.DELIVERY_DETAIL_ID IN (SELECT MDA.DELIVERY_DETAIL_ID FROM MST_DELIVERIES MD, MST_DELIVERY_ASSIGNMENTS MDA, MST_TRIP_STOPS TS, MST_DELIVERY_LEGS MDL WHERE MDA.PLAN_ID = MDD.PLAN_ID AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID AND MD.PLAN_ID = MDD.PLAN_ID AND MDA.DELIVERY_ID = MD.DELIVERY_ID AND WL.WSH_LOCATION_ID = TS.STOP_LOCATION_ID AND TS.PLAN_ID = MDL.PLAN_ID AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID) AND MDL.PLAN_ID = MD.PLAN_ID AND MDL.DELIVERY_ID = MD.DELIVERY_ID) AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND MDD.SOURCE_CODE IN ('PO','RTV') GROUP BY MP.PLAN_ID, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, MDD.SUPPLIER_ID, FLP.FACILITY_ID, MDD.SOURCE_CODE, MDD.SOURCE_HEADER_NUMBER, MDD.TP_REFER_HEADER_NUMBER, MDD.REFERENCE_SOURCE_TYPE union ALL SELECT MP.PLAN_ID, MDD.SUPPLIER_ID, FLP.FACILITY_ID, MDD.SOURCE_CODE, MDD.SOURCE_HEADER_NUMBER, MDD.TP_REFER_HEADER_NUMBER, MDD.REFERENCE_SOURCE_TYPE, SUM(MDD.ALLOCATED_COST) COST, SUM(MDD.GROSS_WEIGHT) WEIGHT, SUM(MDD.VOLUME) CUBE, SUM(CEIL(MDD.NUMBER_OF_PALLETS)) PALLETS, SUM(MDD.REQUESTED_QUANTITY) PIECES, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_DELIVERY_DETAILS MDD, MST_PLANS MP, WSH_LOCATIONS WL, FTE_LOCATION_PARAMETERS FLP WHERE MP.PLAN_ID = MDD.PLAN_ID AND MDD.DELIVERY_DETAIL_ID IN (SELECT MDA.DELIVERY_DETAIL_ID FROM MST_DELIVERIES MD, MST_DELIVERY_ASSIGNMENTS MDA WHERE MDA.PLAN_ID = MDD.PLAN_ID AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID AND MD.PLAN_ID = MDD.PLAN_ID AND MDA.DELIVERY_ID = MD.DELIVERY_ID AND ( WL.WSH_LOCATION_ID = MD.DROPOFF_LOCATION_ID OR WL.WSH_LOCATION_ID = MD.PICKUP_LOCATION_ID ) AND NOT EXISTS (SELECT 1 FROM MST_DELIVERY_LEGS MDL WHERE MDL.PLAN_ID = MD.PLAN_ID AND MDL.DELIVERY_ID = MD.DELIVERY_ID)) AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND MDD.SOURCE_CODE IN ('PO','RTV') GROUP BY MP.PLAN_ID, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, MDD.SUPPLIER_ID, FLP.FACILITY_ID, MDD.SOURCE_CODE, MDD.SOURCE_HEADER_NUMBER, MDD.TP_REFER_HEADER_NUMBER, MDD.REFERENCE_SOURCE_TYPE

View Text - HTML Formatted

SELECT MP.PLAN_ID
, MDD.SUPPLIER_ID
, FLP.FACILITY_ID
, MDD.SOURCE_CODE
, MDD.SOURCE_HEADER_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE
, SUM(MDD.ALLOCATED_COST) COST
, SUM(MDD.GROSS_WEIGHT) WEIGHT
, SUM(MDD.VOLUME) CUBE
, SUM(CEIL(MDD.NUMBER_OF_PALLETS)) PALLETS
, SUM(MDD.REQUESTED_QUANTITY) PIECES
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_DELIVERY_DETAILS MDD
, MST_PLANS MP
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
WHERE MP.PLAN_ID = MDD.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID IN (SELECT MDA.DELIVERY_DETAIL_ID
FROM MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_TRIP_STOPS TS
, MST_DELIVERY_LEGS MDL
WHERE MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND WL.WSH_LOCATION_ID = TS.STOP_LOCATION_ID
AND TS.PLAN_ID = MDL.PLAN_ID
AND ( TS.STOP_ID = MDL.PICK_UP_STOP_ID OR TS.STOP_ID = MDL.DROP_OFF_STOP_ID)
AND MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID)
AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND MDD.SOURCE_CODE IN ('PO'
, 'RTV') GROUP BY MP.PLAN_ID
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MDD.SUPPLIER_ID
, FLP.FACILITY_ID
, MDD.SOURCE_CODE
, MDD.SOURCE_HEADER_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE UNION ALL SELECT MP.PLAN_ID
, MDD.SUPPLIER_ID
, FLP.FACILITY_ID
, MDD.SOURCE_CODE
, MDD.SOURCE_HEADER_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE
, SUM(MDD.ALLOCATED_COST) COST
, SUM(MDD.GROSS_WEIGHT) WEIGHT
, SUM(MDD.VOLUME) CUBE
, SUM(CEIL(MDD.NUMBER_OF_PALLETS)) PALLETS
, SUM(MDD.REQUESTED_QUANTITY) PIECES
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_DELIVERY_DETAILS MDD
, MST_PLANS MP
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
WHERE MP.PLAN_ID = MDD.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID IN (SELECT MDA.DELIVERY_DETAIL_ID
FROM MST_DELIVERIES MD
, MST_DELIVERY_ASSIGNMENTS MDA
WHERE MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MD.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND ( WL.WSH_LOCATION_ID = MD.DROPOFF_LOCATION_ID OR WL.WSH_LOCATION_ID = MD.PICKUP_LOCATION_ID )
AND NOT EXISTS (SELECT 1
FROM MST_DELIVERY_LEGS MDL
WHERE MDL.PLAN_ID = MD.PLAN_ID
AND MDL.DELIVERY_ID = MD.DELIVERY_ID))
AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND MDD.SOURCE_CODE IN ('PO'
, 'RTV') GROUP BY MP.PLAN_ID
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MDD.SUPPLIER_ID
, FLP.FACILITY_ID
, MDD.SOURCE_CODE
, MDD.SOURCE_HEADER_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE