VIEW: APPS.MST_R_FREIGHT_HEADER_V

Source

View Text - Preformatted

SELECT mp.plan_id , flp.facility_id , mst_wb_util.r_get_company_name(wlo.wsh_location_id,wlo.owner_type) , fnd_profile.value('MST_DEPARTMENT_NAME') , mst_wb_util.get_contact_info(wlo.owner_party_id,'NAME',1) , mst_wb_util.get_contact_info(wlo.owner_party_id,'PHONE',1) , mst_wb_util.get_contact_info(wlo.owner_party_id,'EMAIL',1) , fnd_profile.value('MST_COMPANY_NAME') , mst_wb_util.get_contact_info(wlo.owner_party_id,'NAME',1) , mst_wb_util.get_contact_info(wlo.owner_party_id,'PHONE',1) , mst_wb_util.get_contact_info(wlo.owner_party_id,'EMAIL',1) , flp.facility_code , mp.compile_designator , SYSDATE , mp.start_date , mp.cutoff_date , mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK', 'L') + mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK', 'U') , mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL', 'L') + mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL', 'U') , mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL', 'L') + mst_agg_pkg.get_total_trips_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL', 'U') , mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') , mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') , mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_unloading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') + mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_loading_piece_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_unloading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') + mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_loading_weight_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') , mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_unloading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') + mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'TRUCK') + mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'LTL') + mst_agg_pkg.get_loading_cube_for_myfac(mp.plan_id, wl.wsh_location_id, 'PARCEL') from mst_plans mp , wsh_location_owners wlo , wsh_locations wl , fte_location_parameters flp WHERE flp.location_id = wl.wsh_location_id and wl.wsh_location_id = wlo.wsh_location_id

View Text - HTML Formatted

SELECT MP.PLAN_ID
, FLP.FACILITY_ID
, MST_WB_UTIL.R_GET_COMPANY_NAME(WLO.WSH_LOCATION_ID
, WLO.OWNER_TYPE)
, FND_PROFILE.VALUE('MST_DEPARTMENT_NAME')
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'NAME'
, 1)
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'PHONE'
, 1)
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'EMAIL'
, 1)
, FND_PROFILE.VALUE('MST_COMPANY_NAME')
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'NAME'
, 1)
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'PHONE'
, 1)
, MST_WB_UTIL.GET_CONTACT_INFO(WLO.OWNER_PARTY_ID
, 'EMAIL'
, 1)
, FLP.FACILITY_CODE
, MP.COMPILE_DESIGNATOR
, SYSDATE
, MP.START_DATE
, MP.CUTOFF_DATE
, MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK'
, 'L') + MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK'
, 'U')
, MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL'
, 'L') + MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL'
, 'U')
, MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL'
, 'L') + MST_AGG_PKG.GET_TOTAL_TRIPS_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL'
, 'U')
, MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK')
, MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL')
, MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_UNLOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL') + MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_LOADING_PIECE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_UNLOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL') + MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_LOADING_WEIGHT_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
, MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_UNLOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL') + MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'TRUCK') + MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'LTL') + MST_AGG_PKG.GET_LOADING_CUBE_FOR_MYFAC(MP.PLAN_ID
, WL.WSH_LOCATION_ID
, 'PARCEL')
FROM MST_PLANS MP
, WSH_LOCATION_OWNERS WLO
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
WHERE FLP.LOCATION_ID = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID