VIEW: APPS.MST_EXCEED_CAR_VEH_AVAILAB_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.carrier_id , wc.freight_code , 'LANE' , med.lane_id , nvl(fl.lane_number,to_char(fl.lane_id)) , msikfv.concatenated_segments , nvl(med.number2,0) , nvl(med.number1,0) , nvl(med.number1,0) - nvl(med.number2,0) , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date1),1,40) , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date2),1,40) , med.date1 , med.date2 , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mfg_lookups ml , wsh_carriers wc , fte_lanes fl , fte_vehicle_types fvt , mtl_system_items_kfv msikfv WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.carrier_id = wc.carrier_id and med.lane_id = fl.lane_id and med.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id UNION ALL select med.plan_id , med.exception_type , med.exception_detail_id , med.carrier_id , wc.freight_code , 'LANE_GROUP' , med.lane_set_id , to_char(flg.lane_group_id) , msikfv.concatenated_segments , nvl(med.number2,0) , nvl(med.number1,0) , nvl(med.number1,0) - nvl(med.number2,0) , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date1),1,40) , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date2),1,40) , med.date1 , med.date2 , ml.meaning , substr(mst_wb_util.get_threshold_value(med.exception_type),1,20) , substr(mst_wb_util.get_workflow_status(med.plan_id, med.exception_detail_id),1,20) from mst_exception_details med , mfg_lookups ml , wsh_carriers wc , fte_lane_groups flg , fte_vehicle_types fvt , mtl_system_items_kfv msikfv where med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.carrier_id = wc.carrier_id and med.lane_set_id = flg.lane_group_id and med.vehicle_type_id = fvt.vehicle_type_id and fvt.inventory_item_id = msikfv.inventory_item_id and fvt.organization_id = msikfv.organization_id

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.CARRIER_ID
, WC.FREIGHT_CODE
, 'LANE'
, MED.LANE_ID
, NVL(FL.LANE_NUMBER
, TO_CHAR(FL.LANE_ID))
, MSIKFV.CONCATENATED_SEGMENTS
, NVL(MED.NUMBER2
, 0)
, NVL(MED.NUMBER1
, 0)
, NVL(MED.NUMBER1
, 0) - NVL(MED.NUMBER2
, 0)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE1)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE2)
, 1
, 40)
, MED.DATE1
, MED.DATE2
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MFG_LOOKUPS ML
, WSH_CARRIERS WC
, FTE_LANES FL
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.CARRIER_ID = WC.CARRIER_ID
AND MED.LANE_ID = FL.LANE_ID
AND MED.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID UNION ALL SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.CARRIER_ID
, WC.FREIGHT_CODE
, 'LANE_GROUP'
, MED.LANE_SET_ID
, TO_CHAR(FLG.LANE_GROUP_ID)
, MSIKFV.CONCATENATED_SEGMENTS
, NVL(MED.NUMBER2
, 0)
, NVL(MED.NUMBER1
, 0)
, NVL(MED.NUMBER1
, 0) - NVL(MED.NUMBER2
, 0)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE1)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE2)
, 1
, 40)
, MED.DATE1
, MED.DATE2
, ML.MEANING
, SUBSTR(MST_WB_UTIL.GET_THRESHOLD_VALUE(MED.EXCEPTION_TYPE)
, 1
, 20)
, SUBSTR(MST_WB_UTIL.GET_WORKFLOW_STATUS(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID)
, 1
, 20)
FROM MST_EXCEPTION_DETAILS MED
, MFG_LOOKUPS ML
, WSH_CARRIERS WC
, FTE_LANE_GROUPS FLG
, FTE_VEHICLE_TYPES FVT
, MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.CARRIER_ID = WC.CARRIER_ID
AND MED.LANE_SET_ID = FLG.LANE_GROUP_ID
AND MED.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID
AND FVT.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND FVT.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID