VIEW: APPS.MST_SUPPLIER_FACILITIES_V

Source

View Text - Preformatted

SELECT POV.VENDOR_id SUPPLIER_ID, MP.PLAN_ID , MP.COMPILE_DESIGNATOR , MP.DESCRIPTION , WL.WSH_LOCATION_ID , WL.LOCATION_SOURCE_CODE, SUBSTR(MST_WB_UTIL.GET_NAME (WL.WSH_LOCATION_ID), 1,80) COMPANY, WL.ADDRESS1 ADDRESS, WL.ADDRESS2, WL.ADDRESS3, WL.ADDRESS4, WL.CITY, WL.STATE, WL.COUNTY, WL.COUNTRY, WL.PROVINCE, WL.POSTAL_CODE , MST_WB_UTIL.GET_CONTACT_NAME(FLP.FACILITY_CONTACT_ID) CONTACT, MST_WB_UTIL.GET_PHONE_NUMBER(FLP.FACILITY_CONTACT_ID) PHONE, FLP.FACILITY_ID, FLP.FACILITY_CODE , FLP.DESCRIPTION FACILITY_DESCRIPTION, MST_AGG_PKG.get_total_tl_count_c_s_fac (MP.PLAN_ID, POV.VENDOR_id, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'DTL') DIRECT_TLS, MST_AGG_PKG.get_total_tl_count_c_s_fac (MP.PLAN_ID, POV.VENDOR_id, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'MTL') MULTI_STOP_TLS, MST_AGG_PKG.get_total_trips_c_s_fac (MP.PLAN_ID, POV.VENDOR_id, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'LTL') LTLS, MST_AGG_PKG.get_total_trips_c_s_fac (MP.PLAN_ID, POV.VENDOR_id, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'PARCEL') PARCELS, MST_AGG_PKG.GET_TOTAL_COST_C_S_FAC (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_COST, MST_AGG_PKG.get_total_weight_c_s_fac (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_WEIGHT, MST_AGG_PKG.get_total_cube_c_s_fac (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_CUBE, MST_AGG_PKG.get_total_pallets_c_s_fac (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_PALLETS, MST_AGG_PKG.get_total_pieces_c_s_fac (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_PIECES, MST_AGG_PKG.get_total_order_c_s_fac (MP.PLAN_ID, 1, POV.VENDOR_id, WL.WSH_LOCATION_ID) TOTAL_ORDERS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, PO_VENDORS POV, WSH_LOCATION_OWNERS WLO, WSH_LOCATIONS WL, hz_relationships hzr, hz_parties hz, FTE_LOCATION_PARAMETERS FLP WHERE FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID AND WLO.OWNER_TYPE = 4 AND WLO.OWNER_PARTY_ID = hz.party_id AND pov.vendor_id = hzr.subject_id AND hzr.object_id = hz.party_id AND hzr.relationship_type = 'POS_VENDOR_PARTY' AND ( WL.WSH_LOCATION_ID IN (SELECT TS.STOP_LOCATION_ID FROM MST_TRIP_STOPS TS, MST_DELIVERY_LEGS MDL WHERE TS.PLAN_ID = MP.PLAN_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)) OR WL.WSH_LOCATION_ID IN (SELECT MD.PICKUP_LOCATION_ID FROM MST_DELIVERIES MD WHERE MD.PLAN_ID = MP.PLAN_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)))

View Text - HTML Formatted

SELECT POV.VENDOR_ID SUPPLIER_ID
, MP.PLAN_ID
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, WL.WSH_LOCATION_ID
, WL.LOCATION_SOURCE_CODE
, SUBSTR(MST_WB_UTIL.GET_NAME (WL.WSH_LOCATION_ID)
, 1
, 80) COMPANY
, WL.ADDRESS1 ADDRESS
, WL.ADDRESS2
, WL.ADDRESS3
, WL.ADDRESS4
, WL.CITY
, WL.STATE
, WL.COUNTY
, WL.COUNTRY
, WL.PROVINCE
, WL.POSTAL_CODE
, MST_WB_UTIL.GET_CONTACT_NAME(FLP.FACILITY_CONTACT_ID) CONTACT
, MST_WB_UTIL.GET_PHONE_NUMBER(FLP.FACILITY_CONTACT_ID) PHONE
, FLP.FACILITY_ID
, FLP.FACILITY_CODE
, FLP.DESCRIPTION FACILITY_DESCRIPTION
, MST_AGG_PKG.GET_TOTAL_TL_COUNT_C_S_FAC (MP.PLAN_ID
, POV.VENDOR_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'DTL') DIRECT_TLS
, MST_AGG_PKG.GET_TOTAL_TL_COUNT_C_S_FAC (MP.PLAN_ID
, POV.VENDOR_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'MTL') MULTI_STOP_TLS
, MST_AGG_PKG.GET_TOTAL_TRIPS_C_S_FAC (MP.PLAN_ID
, POV.VENDOR_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'LTL') LTLS
, MST_AGG_PKG.GET_TOTAL_TRIPS_C_S_FAC (MP.PLAN_ID
, POV.VENDOR_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'PARCEL') PARCELS
, MST_AGG_PKG.GET_TOTAL_COST_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_CUBE
, MST_AGG_PKG.GET_TOTAL_PALLETS_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOTAL_PIECES_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_PIECES
, MST_AGG_PKG.GET_TOTAL_ORDER_C_S_FAC (MP.PLAN_ID
, 1
, POV.VENDOR_ID
, WL.WSH_LOCATION_ID) TOTAL_ORDERS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, PO_VENDORS POV
, WSH_LOCATION_OWNERS WLO
, WSH_LOCATIONS WL
, HZ_RELATIONSHIPS HZR
, HZ_PARTIES HZ
, FTE_LOCATION_PARAMETERS FLP
WHERE FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID
AND WLO.OWNER_TYPE = 4
AND WLO.OWNER_PARTY_ID = HZ.PARTY_ID
AND POV.VENDOR_ID = HZR.SUBJECT_ID
AND HZR.OBJECT_ID = HZ.PARTY_ID
AND HZR.RELATIONSHIP_TYPE = 'POS_VENDOR_PARTY'
AND ( WL.WSH_LOCATION_ID IN (SELECT TS.STOP_LOCATION_ID
FROM MST_TRIP_STOPS TS
, MST_DELIVERY_LEGS MDL
WHERE TS.PLAN_ID = MP.PLAN_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)) OR WL.WSH_LOCATION_ID IN (SELECT MD.PICKUP_LOCATION_ID
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MP.PLAN_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)))