VIEW: APPS.MST_CARR_LOAD_SUMMARY_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, WLO.OWNER_PARTY_ID CARRIER_ID, 'TRUCK' MODE_OF_TRANSPORT, 1 MODE_TYPE, FLP.FACILITY_ID, MST_AGG_PKG.get_tot_trips_carr_Facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK') TOTAL_TRIPS, MST_AGG_PKG.get_total_cost_carr_fac (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, 'TRUCK') TOTAL_COST, MST_AGG_PKG.get_total_weight_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK') TOTAL_WEIGHT, MST_AGG_PKG.get_total_Cube_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK' ) TOTAL_CUBE, MST_AGG_PKG.get_tot_Pal_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK' ) TOTAL_PALLETS, MST_AGG_PKG.get_tot_Pieces_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK' ) TOTAL_PIECES, MST_AGG_PKG.get_tot_Orders_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'TRUCK' ) TOTAL_ORDERS, MP.CURRENCY_UOM FROM MST_PLANS MP, FTE_LOCATION_PARAMETERS FLP, WSH_LOCATION_OWNERS WLO WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID AND WLO.OWNER_TYPE = 3 AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID FROM MST_TRIPS T WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID, WLO.OWNER_PARTY_ID CARRIER_ID, 'LTL' MODE_OF_TRANSPORT, 2 MODE_TYPE, FLP.FACILITY_ID, MST_AGG_PKG.get_tot_trips_carr_Facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL') TOTAL_TRIPS, MST_AGG_PKG.get_total_cost_carr_fac (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, 'LTL') TOTAL_COST, MST_AGG_PKG.get_total_weight_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL') TOTAL_WEIGHT, MST_AGG_PKG.get_total_Cube_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL' ) TOTAL_CUBE, MST_AGG_PKG.get_tot_Pal_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL' ) TOTAL_PALLETS, MST_AGG_PKG.get_tot_Pieces_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL' ) TOTAL_PIECES, MST_AGG_PKG.get_tot_Orders_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'LTL' ) TOTAL_ORDERS, MP.CURRENCY_UOM FROM MST_PLANS MP, FTE_LOCATION_PARAMETERS FLP, WSH_LOCATION_OWNERS WLO WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID AND WLO.OWNER_TYPE = 3 AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID FROM MST_TRIPS T WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID, WLO.OWNER_PARTY_ID CARRIER_ID, 'PARCEL' MODE_OF_TRANSPORT, 3 MODE_TYPE, FLP.FACILITY_ID, MST_AGG_PKG.get_tot_trips_carr_Facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL') TOTAL_TRIPS, MST_AGG_PKG.get_total_cost_carr_fac (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, 'PARCEL') TOTAL_COST, MST_AGG_PKG.get_total_weight_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL') TOTAL_WEIGHT, MST_AGG_PKG.get_total_Cube_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL' ) TOTAL_CUBE, MST_AGG_PKG.get_tot_Pal_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL' ) TOTAL_PALLETS, MST_AGG_PKG.get_tot_Pieces_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL' ) TOTAL_PIECES, MST_AGG_PKG.get_tot_Orders_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, 'PARCEL' ) TOTAL_ORDERS, MP.CURRENCY_UOM FROM MST_PLANS MP, FTE_LOCATION_PARAMETERS FLP, WSH_LOCATION_OWNERS WLO WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID AND WLO.OWNER_TYPE = 3 AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID FROM MST_TRIPS T WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID, WLO.OWNER_PARTY_ID CARRIER_ID, 'ALL' MODE_OF_TRANSPORT, 0 MODE_TYPE, FLP.FACILITY_ID, MST_AGG_PKG.get_tot_trips_carr_Facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL) TOTAL_TRIPS, MST_AGG_PKG.get_total_cost_carr_fac (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL) TOTAL_COST, MST_AGG_PKG.get_total_weight_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL) TOTAL_WEIGHT, MST_AGG_PKG.get_total_Cube_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL ) TOTAL_CUBE, MST_AGG_PKG.get_tot_Pal_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL ) TOTAL_PALLETS, MST_AGG_PKG.get_tot_Pieces_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL ) TOTAL_PIECES, MST_AGG_PKG.get_tot_Orders_carr_facility (MP.PLAN_ID , WLO.OWNER_PARTY_ID, WLO.WSH_LOCATION_ID, NULL, NULL ) TOTAL_ORDERS, MP.CURRENCY_UOM FROM MST_PLANS MP, FTE_LOCATION_PARAMETERS FLP, WSH_LOCATION_OWNERS WLO WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID AND WLO.OWNER_TYPE = 3 AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID FROM MST_TRIPS T WHERE T.PLAN_ID = MP.PLAN_ID)

View Text - HTML Formatted

SELECT MP.PLAN_ID
, WLO.OWNER_PARTY_ID CARRIER_ID
, 'TRUCK' MODE_OF_TRANSPORT
, 1 MODE_TYPE
, FLP.FACILITY_ID
, MST_AGG_PKG.GET_TOT_TRIPS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK') TOTAL_TRIPS
, MST_AGG_PKG.GET_TOTAL_COST_CARR_FAC (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, 'TRUCK') TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK') TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK' ) TOTAL_CUBE
, MST_AGG_PKG.GET_TOT_PAL_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK' ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOT_PIECES_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK' ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOT_ORDERS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'TRUCK' ) TOTAL_ORDERS
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND WLO.OWNER_TYPE = 3
AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID
FROM MST_TRIPS T
WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID
, WLO.OWNER_PARTY_ID CARRIER_ID
, 'LTL' MODE_OF_TRANSPORT
, 2 MODE_TYPE
, FLP.FACILITY_ID
, MST_AGG_PKG.GET_TOT_TRIPS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL') TOTAL_TRIPS
, MST_AGG_PKG.GET_TOTAL_COST_CARR_FAC (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, 'LTL') TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL') TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL' ) TOTAL_CUBE
, MST_AGG_PKG.GET_TOT_PAL_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL' ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOT_PIECES_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL' ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOT_ORDERS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'LTL' ) TOTAL_ORDERS
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND WLO.OWNER_TYPE = 3
AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID
FROM MST_TRIPS T
WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID
, WLO.OWNER_PARTY_ID CARRIER_ID
, 'PARCEL' MODE_OF_TRANSPORT
, 3 MODE_TYPE
, FLP.FACILITY_ID
, MST_AGG_PKG.GET_TOT_TRIPS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL') TOTAL_TRIPS
, MST_AGG_PKG.GET_TOTAL_COST_CARR_FAC (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, 'PARCEL') TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL') TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL' ) TOTAL_CUBE
, MST_AGG_PKG.GET_TOT_PAL_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL' ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOT_PIECES_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL' ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOT_ORDERS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, 'PARCEL' ) TOTAL_ORDERS
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND WLO.OWNER_TYPE = 3
AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID
FROM MST_TRIPS T
WHERE T.PLAN_ID = MP.PLAN_ID) UNION ALL SELECT MP.PLAN_ID
, WLO.OWNER_PARTY_ID CARRIER_ID
, 'ALL' MODE_OF_TRANSPORT
, 0 MODE_TYPE
, FLP.FACILITY_ID
, MST_AGG_PKG.GET_TOT_TRIPS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL) TOTAL_TRIPS
, MST_AGG_PKG.GET_TOTAL_COST_CARR_FAC (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL ) TOTAL_CUBE
, MST_AGG_PKG.GET_TOT_PAL_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOT_PIECES_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOT_ORDERS_CARR_FACILITY (MP.PLAN_ID
, WLO.OWNER_PARTY_ID
, WLO.WSH_LOCATION_ID
, NULL
, NULL ) TOTAL_ORDERS
, MP.CURRENCY_UOM
FROM MST_PLANS MP
, FTE_LOCATION_PARAMETERS FLP
, WSH_LOCATION_OWNERS WLO
WHERE WLO.WSH_LOCATION_ID = FLP.LOCATION_ID
AND WLO.OWNER_TYPE = 3
AND WLO.OWNER_PARTY_ID IN (SELECT T.CARRIER_ID
FROM MST_TRIPS T
WHERE T.PLAN_ID = MP.PLAN_ID)