eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MST_ORG_ORDERS_V

Source

View Text - Preformatted


SELECT MP.PLAN_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.NET_WEIGHT) WEIGHT, SUM(MDD.VOLUME) CUBE, SUM(MDD.NUMBER_OF_PALLETS) PALLETS, SUM(MDD.REQUESTED_QUANTITY) PIECES, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, 'Dummy_Field' ORIGIN_COMPANY, -1 ORG_FACILITY_ID, 'Dummy_Field' ORIGIN_FACILITY, 'Dummy_Field' ORIGIN_CITY, 'Dummy_Field' ORIGIN_STATE, 'Dummy_Field' ORIGIN_COUNTRY, 'Dummy_Field' ORIGIN_ZIP, 'Dummy_Field' DESTINATION_COMPANY, -1 DEST_FACILITY_ID, 'Dummy_Field' DESTINATION_FACILITY, 'Dummy_Field' DESTINATION_CITY, 'Dummy_Field' DESTINATION_STATE, 'Dummy_Field' DESTINATION_COUNTRY, 'Dummy_Field' DESTINATION_ZIP, 'Dummy_Field' EARLIEST_PICKUP_DATE, 'Dummy_Field' LATEST_PICKUP_DATE, 'Dummy_Field' EARLIEST_ACCEPTABLE_DATE, 'Dummy_Field' LATEST_ACCEPTABLE_DATE 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 MDD.CONTAINER_FLAG = 2 AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND WL.WSH_LOCATION_ID IN (SELECT DISTINCT MD.PICKUP_LOCATION_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 = MDA.PLAN_ID AND MD.DELIVERY_ID = MDA.DELIVERY_ID UNION SELECT DISTINCT MD.DROPOFF_LOCATION_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 = MDA.PLAN_ID AND MD.DELIVERY_ID = MDA.DELIVERY_ID) GROUP BY MP.PLAN_ID, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, 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, 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(MDD.NUMBER_OF_PALLETS) PALLETS, SUM(MDD.REQUESTED_QUANTITY) PIECES, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, 'Dummy_Field' ORIGIN_COMPANY, -1 ORG_FACILITY_ID, 'Dummy_Field' ORIGIN_FACILITY, 'Dummy_Field' ORIGIN_CITY, 'Dummy_Field' ORIGIN_STATE, 'Dummy_Field' ORIGIN_COUNTRY, 'Dummy_Field' ORIGIN_ZIP, 'Dummy_Field' DESTINATION_COMPANY, -1 DEST_FACILITY_ID, 'Dummy_Field' DESTINATION_FACILITY, 'Dummy_Field' DESTINATION_CITY, 'Dummy_Field' DESTINATION_STATE, 'Dummy_Field' DESTINATION_COUNTRY, 'Dummy_Field' DESTINATION_ZIP, 'Dummy_Field' EARLIEST_PICKUP_DATE, 'Dummy_Field' LATEST_PICKUP_DATE, 'Dummy_Field' EARLIEST_ACCEPTABLE_DATE, 'Dummy_Field' LATEST_ACCEPTABLE_DATE 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 MDD.CONTAINER_FLAG = 2 AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND WL.WSH_LOCATION_ID IN (SELECT DISTINCT MD.PICKUP_LOCATION_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 = MDA.PLAN_ID AND MD.DELIVERY_ID = MDA.DELIVERY_ID UNION SELECT DISTINCT MD.DROPOFF_LOCATION_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 = MDA.PLAN_ID AND MD.DELIVERY_ID = MDA.DELIVERY_ID) GROUP BY MP.PLAN_ID, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM, 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
, 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.NET_WEIGHT) WEIGHT
, SUM(MDD.VOLUME) CUBE
, SUM(MDD.NUMBER_OF_PALLETS) PALLETS
, SUM(MDD.REQUESTED_QUANTITY) PIECES
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, 'DUMMY_FIELD' ORIGIN_COMPANY
, -1 ORG_FACILITY_ID
, 'DUMMY_FIELD' ORIGIN_FACILITY
, 'DUMMY_FIELD' ORIGIN_CITY
, 'DUMMY_FIELD' ORIGIN_STATE
, 'DUMMY_FIELD' ORIGIN_COUNTRY
, 'DUMMY_FIELD' ORIGIN_ZIP
, 'DUMMY_FIELD' DESTINATION_COMPANY
, -1 DEST_FACILITY_ID
, 'DUMMY_FIELD' DESTINATION_FACILITY
, 'DUMMY_FIELD' DESTINATION_CITY
, 'DUMMY_FIELD' DESTINATION_STATE
, 'DUMMY_FIELD' DESTINATION_COUNTRY
, 'DUMMY_FIELD' DESTINATION_ZIP
, 'DUMMY_FIELD' EARLIEST_PICKUP_DATE
, 'DUMMY_FIELD' LATEST_PICKUP_DATE
, 'DUMMY_FIELD' EARLIEST_ACCEPTABLE_DATE
, 'DUMMY_FIELD' LATEST_ACCEPTABLE_DATE
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 MDD.CONTAINER_FLAG = 2
AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID IN (SELECT DISTINCT MD.PICKUP_LOCATION_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 = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID UNION SELECT DISTINCT MD.DROPOFF_LOCATION_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 = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID) GROUP BY MP.PLAN_ID
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, 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
, 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(MDD.NUMBER_OF_PALLETS) PALLETS
, SUM(MDD.REQUESTED_QUANTITY) PIECES
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, 'DUMMY_FIELD' ORIGIN_COMPANY
, -1 ORG_FACILITY_ID
, 'DUMMY_FIELD' ORIGIN_FACILITY
, 'DUMMY_FIELD' ORIGIN_CITY
, 'DUMMY_FIELD' ORIGIN_STATE
, 'DUMMY_FIELD' ORIGIN_COUNTRY
, 'DUMMY_FIELD' ORIGIN_ZIP
, 'DUMMY_FIELD' DESTINATION_COMPANY
, -1 DEST_FACILITY_ID
, 'DUMMY_FIELD' DESTINATION_FACILITY
, 'DUMMY_FIELD' DESTINATION_CITY
, 'DUMMY_FIELD' DESTINATION_STATE
, 'DUMMY_FIELD' DESTINATION_COUNTRY
, 'DUMMY_FIELD' DESTINATION_ZIP
, 'DUMMY_FIELD' EARLIEST_PICKUP_DATE
, 'DUMMY_FIELD' LATEST_PICKUP_DATE
, 'DUMMY_FIELD' EARLIEST_ACCEPTABLE_DATE
, 'DUMMY_FIELD' LATEST_ACCEPTABLE_DATE
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 MDD.CONTAINER_FLAG = 2
AND FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID IN (SELECT DISTINCT MD.PICKUP_LOCATION_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 = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID UNION SELECT DISTINCT MD.DROPOFF_LOCATION_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 = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID) GROUP BY MP.PLAN_ID
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, FLP.FACILITY_ID
, MDD.SOURCE_CODE
, MDD.SOURCE_HEADER_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE