VIEW: APPS.MST_CM_V

Source

View Text - Preformatted

SELECT tp.plan_id, tp.continuous_move_id, tp.cm_trip_number, tp.carrier_id, car.freight_code, tp.service_level, wlk.meaning, msikfv.concatenated_segments , loc1.wsh_location_id, flp1.facility_code , flp1.description , loc1.city , loc1.state , loc1.postal_code , loc1.country , loc2.wsh_location_id, flp2.facility_code , flp2.description , loc2.city , loc2.state , loc2.postal_code , loc2.country , mst_cm_details.get_number_of_loads(tp.plan_id, tp.continuous_move_id), mst_cm_details.get_distance(tp.plan_id, tp.continuous_move_id), tp.TOTAL_transportation_COST, mp.currency_uom, mst_cm_details.get_savings(tp.plan_id, tp.continuous_move_id, tp.TOTAL_transportation_COST), SUBSTR(MST_WB_UTIL.get_local_chardtzone (loc1.WSH_LOCATION_ID, tp.start_date),1,80) FIRST_DEPARTURE_DATE, SUBSTR(MST_WB_UTIL.get_local_chardtzone (loc2.WSH_LOCATION_ID, tp.end_date ),1,80) LAST_ARRIVAL_DATE, tp.planned_flag, mfg1.meaning, nvl(tp.selected_for_release,2), mfg2.meaning, tp.release_date, fnd_date.date_to_chardt(tp.release_date), tp.release_status, mfg3.meaning, tp.auto_release_flag from mst_plans mp, mst_cm_trips tp, wsh_carriers car , wsh_locations loc1, wsh_locations loc2, fte_location_parameters flp1 , fte_location_parameters flp2 , fte_vehicle_types fte, mtl_system_items_kfv msikfv , mfg_lookups mfg1, mfg_lookups mfg2, mfg_lookups mfg3, wsh_lookups wlk WHERE mp.plan_id = tp.plan_id and tp.carrier_id = car.carrier_id and tp.start_location_id = loc1.wsh_location_id and tp.end_location_id = loc2.wsh_location_id and tp.start_location_id = flp1.location_id and tp.end_location_id = flp2.location_id and tp.vehicle_type_id = fte.vehicle_type_id and fte.inventory_item_id = msikfv.inventory_item_id and fte.organization_id = msikfv.organization_id and tp.planned_flag = mfg1.lookup_code (+) and mfg1.lookup_type (+) = 'MST_CM_TRIP_PLANNED_FLAG' and nvl(tp.selected_for_release,2) = mfg2.lookup_code and mfg2.lookup_type = 'MST_SELECTED_FOR_RELEASE' and NVL(tp.release_status,5) = mfg3.lookup_code and mfg3.lookup_type = 'MST_RELEASE_STATUS' and wlk.lookup_code = tp.service_level and wlk.lookup_type = 'WSH_SERVICE_LEVELS'

View Text - HTML Formatted

SELECT TP.PLAN_ID
, TP.CONTINUOUS_MOVE_ID
, TP.CM_TRIP_NUMBER
, TP.CARRIER_ID
, CAR.FREIGHT_CODE
, TP.SERVICE_LEVEL
, WLK.MEANING
, MSIKFV.CONCATENATED_SEGMENTS
, LOC1.WSH_LOCATION_ID
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, LOC1.CITY
, LOC1.STATE
, LOC1.POSTAL_CODE
, LOC1.COUNTRY
, LOC2.WSH_LOCATION_ID
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, LOC2.CITY
, LOC2.STATE
, LOC2.POSTAL_CODE
, LOC2.COUNTRY
, MST_CM_DETAILS.GET_NUMBER_OF_LOADS(TP.PLAN_ID
, TP.CONTINUOUS_MOVE_ID)
, MST_CM_DETAILS.GET_DISTANCE(TP.PLAN_ID
, TP.CONTINUOUS_MOVE_ID)
, TP.TOTAL_TRANSPORTATION_COST
, MP.CURRENCY_UOM
, MST_CM_DETAILS.GET_SAVINGS(TP.PLAN_ID
, TP.CONTINUOUS_MOVE_ID
, TP.TOTAL_TRANSPORTATION_COST)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (LOC1.WSH_LOCATION_ID
, TP.START_DATE)
, 1
, 80) FIRST_DEPARTURE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE (LOC2.WSH_LOCATION_ID
, TP.END_DATE )
, 1
, 80) LAST_ARRIVAL_DATE
, TP.PLANNED_FLAG
, MFG1.MEANING
, NVL(TP.SELECTED_FOR_RELEASE
, 2)
, MFG2.MEANING
, TP.RELEASE_DATE
, FND_DATE.DATE_TO_CHARDT(TP.RELEASE_DATE)
, TP.RELEASE_STATUS
, MFG3.MEANING
, TP.AUTO_RELEASE_FLAG
FROM MST_PLANS MP
, MST_CM_TRIPS TP
, WSH_CARRIERS CAR
, WSH_LOCATIONS LOC1
, WSH_LOCATIONS LOC2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, FTE_VEHICLE_TYPES FTE
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MFG_LOOKUPS MFG1
, MFG_LOOKUPS MFG2
, MFG_LOOKUPS MFG3
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = TP.PLAN_ID
AND TP.CARRIER_ID = CAR.CARRIER_ID
AND TP.START_LOCATION_ID = LOC1.WSH_LOCATION_ID
AND TP.END_LOCATION_ID = LOC2.WSH_LOCATION_ID
AND TP.START_LOCATION_ID = FLP1.LOCATION_ID
AND TP.END_LOCATION_ID = FLP2.LOCATION_ID
AND TP.VEHICLE_TYPE_ID = FTE.VEHICLE_TYPE_ID
AND FTE.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FTE.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND TP.PLANNED_FLAG = MFG1.LOOKUP_CODE (+)
AND MFG1.LOOKUP_TYPE (+) = 'MST_CM_TRIP_PLANNED_FLAG'
AND NVL(TP.SELECTED_FOR_RELEASE
, 2) = MFG2.LOOKUP_CODE
AND MFG2.LOOKUP_TYPE = 'MST_SELECTED_FOR_RELEASE'
AND NVL(TP.RELEASE_STATUS
, 5) = MFG3.LOOKUP_CODE
AND MFG3.LOOKUP_TYPE = 'MST_RELEASE_STATUS'
AND WLK.LOOKUP_CODE = TP.SERVICE_LEVEL
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'