VIEW: APPS.MST_PQ_ORDER_DETAILS_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, MP.COMPILE_DESIGNATOR PLAN_NAME, DD.SOURCE_LINE_NUMBER, DD.SOURCE_CODE, DD.SOURCE_HEADER_NUMBER, DECODE(DD.REFERENCE_SOURCE_TYPE,'SO',DD.REFERENCE_SOURCE_TYPE,'PO',DD.REFERENCE_SOURCE_TYPE,'XXXXX') REFERENCE_SOURCE_TYPE, DD.SOURCE_CODE||':'||DD.SOURCE_HEADER_NUMBER ORDER_NUMBER, DD.TP_REFER_HEADER_NUMBER, D.CUSTOMER_ID, D.SUPPLIER_ID, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.CUSTOMER_ID, 1), 1,80) CUSTOMER_NAME, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.SUPPLIER_ID, 2), 1,80) SUPPLIER_NAME, LP1.FACILITY_ID ORIGIN_FACILITY_ID, LP1.FACILITY_CODE ORIGIN_FACILITY, WL1.CITY ORIGIN_CITY, WL1.STATE ORIGIN_STATE, WL1.POSTAL_CODE ORIGIN_ZIP, WL1.COUNTRY ORIGIN_COUNTRY, LP2.FACILITY_ID DESTINATION_FACILITY_ID, LP2.FACILITY_CODE DESTINATION_FACILITY, WL2.CITY DESTINATION_CITY, WL2.STATE DESTINATION_STATE, WL2.POSTAL_CODE DESTINATION_ZIP, WL2.COUNTRY DESTINATION_COUNTRY, DD.INVENTORY_ITEM_ID, MSIKFV.CONCATENATED_SEGMENTS ITEM_NAME, DD.GROSS_WEIGHT TOTAL_WEIGHT, DD.VOLUME TOTAL_CUBE, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, MST_DELIVERIES D, MST_DELIVERY_ASSIGNMENTS DA, MST_DELIVERY_DETAILS DD, WSH_LOCATIONS WL1, WSH_LOCATIONS WL2, FTE_LOCATION_PARAMETERS LP1, FTE_LOCATION_PARAMETERS LP2, MTL_SYSTEM_ITEMS_KFV MSIKFV WHERE MP.PLAN_ID = D.PLAN_ID AND D.PLAN_ID = DA.PLAN_ID AND D.DELIVERY_ID = DA.DELIVERY_ID AND DA.PLAN_ID = DD.PLAN_ID AND DA.DELIVERY_DETAIL_ID = DD.DELIVERY_DETAIL_ID AND WL1.WSH_LOCATION_ID = D.pickup_LOCATION_ID AND WL2.WSH_LOCATION_ID = D.dropoff_LOCATION_ID AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID AND DD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID AND DD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID

View Text - HTML Formatted

SELECT MP.PLAN_ID
, MP.COMPILE_DESIGNATOR PLAN_NAME
, DD.SOURCE_LINE_NUMBER
, DD.SOURCE_CODE
, DD.SOURCE_HEADER_NUMBER
, DECODE(DD.REFERENCE_SOURCE_TYPE
, 'SO'
, DD.REFERENCE_SOURCE_TYPE
, 'PO'
, DD.REFERENCE_SOURCE_TYPE
, 'XXXXX') REFERENCE_SOURCE_TYPE
, DD.SOURCE_CODE||':'||DD.SOURCE_HEADER_NUMBER ORDER_NUMBER
, DD.TP_REFER_HEADER_NUMBER
, D.CUSTOMER_ID
, D.SUPPLIER_ID
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.CUSTOMER_ID
, 1)
, 1
, 80) CUSTOMER_NAME
, SUBSTR( MST_WB_UTIL.GET_PARTNER_NAME(D.SUPPLIER_ID
, 2)
, 1
, 80) SUPPLIER_NAME
, LP1.FACILITY_ID ORIGIN_FACILITY_ID
, LP1.FACILITY_CODE ORIGIN_FACILITY
, WL1.CITY ORIGIN_CITY
, WL1.STATE ORIGIN_STATE
, WL1.POSTAL_CODE ORIGIN_ZIP
, WL1.COUNTRY ORIGIN_COUNTRY
, LP2.FACILITY_ID DESTINATION_FACILITY_ID
, LP2.FACILITY_CODE DESTINATION_FACILITY
, WL2.CITY DESTINATION_CITY
, WL2.STATE DESTINATION_STATE
, WL2.POSTAL_CODE DESTINATION_ZIP
, WL2.COUNTRY DESTINATION_COUNTRY
, DD.INVENTORY_ITEM_ID
, MSIKFV.CONCATENATED_SEGMENTS ITEM_NAME
, DD.GROSS_WEIGHT TOTAL_WEIGHT
, DD.VOLUME TOTAL_CUBE
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, MST_DELIVERIES D
, MST_DELIVERY_ASSIGNMENTS DA
, MST_DELIVERY_DETAILS DD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS LP1
, FTE_LOCATION_PARAMETERS LP2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MP.PLAN_ID = D.PLAN_ID
AND D.PLAN_ID = DA.PLAN_ID
AND D.DELIVERY_ID = DA.DELIVERY_ID
AND DA.PLAN_ID = DD.PLAN_ID
AND DA.DELIVERY_DETAIL_ID = DD.DELIVERY_DETAIL_ID
AND WL1.WSH_LOCATION_ID = D.PICKUP_LOCATION_ID
AND WL2.WSH_LOCATION_ID = D.DROPOFF_LOCATION_ID
AND WL1.WSH_LOCATION_ID = LP1.LOCATION_ID
AND WL2.WSH_LOCATION_ID = LP2.LOCATION_ID
AND DD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND DD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID