VIEW: APPS.MST_CUSTOMER_FACILITIES_V

Source

View Text - Preformatted

SELECT HZC.CUST_ACCOUNT_ID CUSTOMER_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, HZC.CUST_ACCOUNT_ID, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'DTL') DIRECT_TLS, MST_AGG_PKG.get_total_tl_count_c_s_fac (MP.PLAN_ID, HZC.CUST_ACCOUNT_ID, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'MTL') MULTI_STOP_TLS, MST_AGG_PKG.get_total_trips_c_s_fac (MP.PLAN_ID, HZC.CUST_ACCOUNT_ID, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'LTL') LTLS, MST_AGG_PKG.get_total_trips_c_s_fac (MP.PLAN_ID, HZC.CUST_ACCOUNT_ID, WLO.OWNER_TYPE, WL.WSH_LOCATION_ID, 'PARCEL') PARCELS, MST_AGG_PKG.GET_TOTAL_COST_C_S_FAC (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_COST, MST_AGG_PKG.get_total_weight_c_s_fac (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_WEIGHT, MST_AGG_PKG.get_total_cube_c_s_fac (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_CUBE, MST_AGG_PKG.get_total_pallets_c_s_fac (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_PALLETS, MST_AGG_PKG.get_total_pieces_c_s_fac (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_PIECES, MST_AGG_PKG.get_total_order_c_s_fac (MP.PLAN_ID, 0, HZC.CUST_ACCOUNT_ID, WL.WSH_LOCATION_ID) TOTAL_ORDERS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, WSH_LOCATION_OWNERS WLO, WSH_LOCATIONS WL, FTE_LOCATION_PARAMETERS FLP, HZ_CUST_ACCOUNTS HZC, HZ_CUST_ACCT_SITES_ALL HZCASA, HZ_PARTY_SITES HZPS WHERE FLP.LOCATION_ID = WL.WSH_LOCATION_ID AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID AND WLO.OWNER_TYPE = 2 AND WLO.OWNER_PARTY_ID = HZC.PARTY_ID AND HZPS.PARTY_ID = HZC.PARTY_ID AND HZPS.LOCATION_ID = WL.WSH_LOCATION_ID AND HZPS.PARTY_SITE_ID = HZCASA.PARTY_SITE_ID AND HZCASA.CUST_ACCOUNT_ID = HZC.CUST_ACCOUNT_ID 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.DROPOFF_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 HZC.CUST_ACCOUNT_ID CUSTOMER_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
, HZC.CUST_ACCOUNT_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'DTL') DIRECT_TLS
, MST_AGG_PKG.GET_TOTAL_TL_COUNT_C_S_FAC (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'MTL') MULTI_STOP_TLS
, MST_AGG_PKG.GET_TOTAL_TRIPS_C_S_FAC (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'LTL') LTLS
, MST_AGG_PKG.GET_TOTAL_TRIPS_C_S_FAC (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID
, WLO.OWNER_TYPE
, WL.WSH_LOCATION_ID
, 'PARCEL') PARCELS
, MST_AGG_PKG.GET_TOTAL_COST_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_CUBE
, MST_AGG_PKG.GET_TOTAL_PALLETS_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOTAL_PIECES_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_PIECES
, MST_AGG_PKG.GET_TOTAL_ORDER_C_S_FAC (MP.PLAN_ID
, 0
, HZC.CUST_ACCOUNT_ID
, WL.WSH_LOCATION_ID) TOTAL_ORDERS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, WSH_LOCATION_OWNERS WLO
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
, HZ_CUST_ACCOUNTS HZC
, HZ_CUST_ACCT_SITES_ALL HZCASA
, HZ_PARTY_SITES HZPS
WHERE FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID
AND WLO.OWNER_TYPE = 2
AND WLO.OWNER_PARTY_ID = HZC.PARTY_ID
AND HZPS.PARTY_ID = HZC.PARTY_ID
AND HZPS.LOCATION_ID = WL.WSH_LOCATION_ID
AND HZPS.PARTY_SITE_ID = HZCASA.PARTY_SITE_ID
AND HZCASA.CUST_ACCOUNT_ID = HZC.CUST_ACCOUNT_ID
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.DROPOFF_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)))