VIEW: APPS.MST_AL_AP_LOADS_V

Source

View Text - Preformatted

SELECT mt.plan_id , mt.trip_id , mt.trip_number , mt.carrier_id , mt.origin_location_id , mt.destination_location_id , wc.freight_code , mt.service_level , wlk.meaning , mt.mode_of_transport , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mt.origin_location_id , mt.trip_start_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mt.destination_location_id , mt.trip_end_date),1,40) , mt.trip_start_date , mt.trip_end_date , nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) , mp.currency_uom , nvl(sum(md.gross_weight),0) , nvl(sum(md.volume),0) , nvl(sum(md.number_of_pallets),0) , nvl(sum(md.number_of_pieces),0) , nvl(mst_wb_util.Get_Trip_Orders(mt.plan_id,mt.trip_id),0) , mp.weight_uom , mp.volume_uom , mt.planned_flag , ml1.meaning , ml2.meaning , ml3.meaning , mt.release_date , fnd_date.date_to_chardt(mt.release_date) , mt.auto_release_flag , mt.selected_for_release , mt.release_status from mst_plans mp , mst_trips mt , mst_delivery_legs mdl , mst_deliveries md , wsh_carriers wc , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , mfg_lookups ml1 , mfg_lookups ml2 , mfg_lookups ml3 , wsh_lookups wlk WHERE mp.plan_id = mt.plan_id and mt.plan_id = mdl.plan_id (+) and mt.trip_id = mdl.trip_id (+) and mdl.plan_id = md.plan_id (+) and mdl.delivery_id = md.delivery_id (+) and mt.carrier_id = wc.carrier_id and mt.origin_location_id = wl1.wsh_location_id and mt.destination_location_id = wl2.wsh_location_id and mt.origin_location_id = flp1.location_id and mt.destination_location_id = flp2.location_id and nvl(mt.planned_flag,3) = ml1.lookup_code and ml1.lookup_type = 'MST_TRIP_PLANNED_FLAG' and nvl(mt.selected_for_release,2) = ml2.lookup_code and ml2.lookup_type = 'MST_SELECTED_FOR_RELEASE' and nvl(mt.release_status,5) = ml3.lookup_code and ml3.lookup_type = 'MST_RELEASE_STATUS' and wlk.lookup_code = mt.service_level and wlk.lookup_type = 'WSH_SERVICE_LEVELS' group by mt.plan_id , mt.trip_id , mt.trip_number , mt.carrier_id , mt.origin_location_id , mt.destination_location_id , wc.freight_code , mt.service_level , wlk.meaning , mt.mode_of_transport , substr(mst_wb_util.get_name(mt.origin_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mt.destination_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mt.origin_location_id , mt.trip_start_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mt.destination_location_id , mt.trip_end_date),1,40) , mt.trip_start_date , mt.trip_end_date , nvl(mt.total_basic_transport_cost,0) + nvl(mt.total_accessorial_cost,0) , mp.currency_uom , nvl(mst_wb_util.Get_Trip_Orders(mt.plan_id,mt.trip_id),0) , mp.weight_uom , mp.volume_uom , mt.planned_flag , ml1.meaning , ml2.meaning , ml3.meaning , mt.release_date , fnd_date.date_to_chardt(mt.release_date) , mt.auto_release_flag , mt.selected_for_release , mt.release_status

View Text - HTML Formatted

SELECT MT.PLAN_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.CARRIER_ID
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.ORIGIN_LOCATION_ID
, MT.TRIP_START_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.DESTINATION_LOCATION_ID
, MT.TRIP_END_DATE)
, 1
, 40)
, MT.TRIP_START_DATE
, MT.TRIP_END_DATE
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0)
, MP.CURRENCY_UOM
, NVL(SUM(MD.GROSS_WEIGHT)
, 0)
, NVL(SUM(MD.VOLUME)
, 0)
, NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
, NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
, NVL(MST_WB_UTIL.GET_TRIP_ORDERS(MT.PLAN_ID
, MT.TRIP_ID)
, 0)
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MT.PLANNED_FLAG
, ML1.MEANING
, ML2.MEANING
, ML3.MEANING
, MT.RELEASE_DATE
, FND_DATE.DATE_TO_CHARDT(MT.RELEASE_DATE)
, MT.AUTO_RELEASE_FLAG
, MT.SELECTED_FOR_RELEASE
, MT.RELEASE_STATUS
FROM MST_PLANS MP
, MST_TRIPS MT
, MST_DELIVERY_LEGS MDL
, MST_DELIVERIES MD
, WSH_CARRIERS WC
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, WSH_LOOKUPS WLK
WHERE MP.PLAN_ID = MT.PLAN_ID
AND MT.PLAN_ID = MDL.PLAN_ID (+)
AND MT.TRIP_ID = MDL.TRIP_ID (+)
AND MDL.PLAN_ID = MD.PLAN_ID (+)
AND MDL.DELIVERY_ID = MD.DELIVERY_ID (+)
AND MT.CARRIER_ID = WC.CARRIER_ID
AND MT.ORIGIN_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MT.ORIGIN_LOCATION_ID = FLP1.LOCATION_ID
AND MT.DESTINATION_LOCATION_ID = FLP2.LOCATION_ID
AND NVL(MT.PLANNED_FLAG
, 3) = ML1.LOOKUP_CODE
AND ML1.LOOKUP_TYPE = 'MST_TRIP_PLANNED_FLAG'
AND NVL(MT.SELECTED_FOR_RELEASE
, 2) = ML2.LOOKUP_CODE
AND ML2.LOOKUP_TYPE = 'MST_SELECTED_FOR_RELEASE'
AND NVL(MT.RELEASE_STATUS
, 5) = ML3.LOOKUP_CODE
AND ML3.LOOKUP_TYPE = 'MST_RELEASE_STATUS'
AND WLK.LOOKUP_CODE = MT.SERVICE_LEVEL
AND WLK.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS' GROUP BY MT.PLAN_ID
, MT.TRIP_ID
, MT.TRIP_NUMBER
, MT.CARRIER_ID
, MT.ORIGIN_LOCATION_ID
, MT.DESTINATION_LOCATION_ID
, WC.FREIGHT_CODE
, MT.SERVICE_LEVEL
, WLK.MEANING
, MT.MODE_OF_TRANSPORT
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.ORIGIN_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MT.DESTINATION_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.ORIGIN_LOCATION_ID
, MT.TRIP_START_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MT.DESTINATION_LOCATION_ID
, MT.TRIP_END_DATE)
, 1
, 40)
, MT.TRIP_START_DATE
, MT.TRIP_END_DATE
, NVL(MT.TOTAL_BASIC_TRANSPORT_COST
, 0) + NVL(MT.TOTAL_ACCESSORIAL_COST
, 0)
, MP.CURRENCY_UOM
, NVL(MST_WB_UTIL.GET_TRIP_ORDERS(MT.PLAN_ID
, MT.TRIP_ID)
, 0)
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MT.PLANNED_FLAG
, ML1.MEANING
, ML2.MEANING
, ML3.MEANING
, MT.RELEASE_DATE
, FND_DATE.DATE_TO_CHARDT(MT.RELEASE_DATE)
, MT.AUTO_RELEASE_FLAG
, MT.SELECTED_FOR_RELEASE
, MT.RELEASE_STATUS