VIEW: APPS.MST_AUDIT_EXCEPTIONS_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.delivery_id , mt.trip_number , wc.freight_code , flp1.facility_id , flp2.facility_id , md.delivery_number , decode(mdd.split_from_delivery_detail_id,null,mdd.sr_delivery_detail_id,mdd.delivery_detail_id) , decode(mdd.container_flag,1,null,mdd.source_code) , mdd.source_header_number , mdd.source_line_number , mdd.tp_refer_header_number , mdd.reference_source_type , decode(med.exception_type,405,null,flp1.facility_code) , decode(med.exception_type,405,null,flp1.description) , decode(med.exception_type,405,null,substr(mst_wb_util.get_name(mdd.ship_from_location_id),1,80)) , decode(med.exception_type,405,null,wl1.address1) , decode(med.exception_type,405,null,wl1.city) , decode(med.exception_type,405,null,wl1.state) , decode(med.exception_type,405,null,wl1.postal_code) , decode(med.exception_type,405,null,wl1.country) , decode(med.exception_type,404,null,flp2.facility_code) , decode(med.exception_type,404,null,flp2.description) , decode(med.exception_type,404,null,substr(mst_wb_util.get_name(mdd.ship_to_location_id),1,80)) , decode(med.exception_type,404,null,wl2.address1) , decode(med.exception_type,404,null,wl2.city) , decode(med.exception_type,404,null,wl2.state) , decode(med.exception_type,404,null,wl2.postal_code) , decode(med.exception_type,404,null,wl2.country) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.latest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.latest_acceptable_date),1,40) , mdd.earliest_pickup_date , mdd.latest_pickup_date , mdd.earliest_acceptable_date , mdd.latest_acceptable_date , decode(med.exception_type, 902, null, 903, null, 904, null, substr(mst_wb_util.get_hr_min(nvl(number3,0)),1,20)) , decode(med.exception_type, 902, null, 903, null, 904, null, substr(mst_wb_util.get_hr_min(nvl(number2,0)),1,20)) , decode(med.exception_type, 902, null, 903, null, 904, null, substr(mst_wb_util.get_hr_min(nvl(number2,0)- nvl(number3,0)),1,20)) , substr(mst_wb_util.get_hr_min(decode( sign(mdd.latest_pickup_date - mp.start_date), -1, (mp.start_date - mdd.latest_pickup_date) ,decode( sign(mdd.latest_acceptable_date - mp.start_date) ,-1,(mp.start_date - mdd.latest_acceptable_date) , 0 ) )*24),1,20) , msikfv.concatenated_segments , msitl.description , nvl(mdd.gross_weight,0) , mp.weight_uom , nvl(mdd.volume,0) , mp.volume_uom , round(nvl(mdd.number_of_pallets,0)) , nvl(mdd.requested_quantity,0) , decode(med.exception_type,903,med.number1,904,med.number1, to_number(null)) , decode(med.exception_type,903,med.number2,904,med.number2, to_number(null)) , msikfv.unit_length , msikfv.unit_width , msikfv.unit_height , msikfv.dimension_uom_code , decode(med.exception_type,902,med.number1,fvt.usable_length) , decode(med.exception_type,902,med.number2,fvt.usable_width) , decode(med.exception_type,902,med.number3,fvt.usable_height) , med.char1 , 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 , mst_delivery_details mdd , mst_deliveries md , mst_plans mp , mst_delivery_legs mdl , mst_trips mt , wsh_carriers wc , mtl_system_items_b_kfv msikfv , mtl_system_items_tl msitl , mfg_lookups ml , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , fte_vehicle_types fvt WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mp.plan_id and med.plan_id = mdd.plan_id and med.delivery_detail_id = mdd.delivery_detail_id and med.plan_id = md.plan_id (+) and med.delivery_id = md.delivery_id (+) and med.plan_id = mdl.plan_id (+) and med.delivery_id = mdl.delivery_id (+) and mdl.plan_id = mt.plan_id (+) and mdl.trip_id = mt.trip_id (+) and mt.carrier_id = wc.carrier_id (+) and mdd.inventory_item_id = msikfv.inventory_item_id and mdd.organization_id = msikfv.organization_id and mdd.inventory_item_id = msitl.inventory_item_id and mdd.organization_id = msitl.organization_id and msitl.language = userenv('LANG') and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_from_location_id = flp1.location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.ship_to_location_id = flp2.location_id and mt.vehicle_type_id = fvt.vehicle_type_id (+) and med.exception_type not in (704, 905) union all select med.plan_id , med.exception_type , med.exception_detail_id , to_number(null) , to_number(null) , null , flp1.facility_id , to_number(null) , to_number(null) , to_number(null) , null , null , null , null , null , flp1.facility_code , flp1.description , substr(mst_wb_util.get_name(med.location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , null , null , null , null , null , null , null , null , null , null , null , null , to_date(null) , to_date(null) , to_date(null) , to_date(null) , null , null , null , null , null , null , to_number(null) , null , to_number(null) , null , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , null , to_number(null) , to_number(null) , to_number(null) , null , ml.meaning , null , 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_locations wl1 , fte_location_parameters flp1 where med.exception_type = 704 and med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.location_id = wl1.wsh_location_id and med.location_id = flp1.location_id union all SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.delivery_id , mt.trip_number , wc.freight_code , flp1.facility_id , flp2.facility_id , md.delivery_number , decode(mdd.split_from_delivery_detail_id,null,mdd.sr_delivery_detail_id,mdd.delivery_detail_id) , decode(mdd.container_flag,1,null,mdd.source_code) , mdd.source_header_number , mdd.source_line_number , mdd.tp_refer_header_number , mdd.reference_source_type , flp1.facility_code , flp1.description , substr(mst_wb_util.get_name(mdd.ship_from_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , flp2.facility_code , flp2.description , substr(mst_wb_util.get_name(mdd.ship_to_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.latest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.latest_acceptable_date),1,40) , mdd.earliest_pickup_date , mdd.latest_pickup_date , mdd.earliest_acceptable_date , mdd.latest_acceptable_date , null , null , null , substr(mst_wb_util.get_hr_min(decode( sign(mdd.latest_pickup_date - mp.plan_start_date), -1, (mp.plan_start_date - mdd.latest_pickup_date) ,decode( sign(mdd.latest_acceptable_date - mp.plan_start_date) ,-1,(mp.plan_start_date - mdd.latest_acceptable_date) , 0) )*24),1,20) , msikfv.concatenated_segments , msitl.description , nvl(mdd.gross_weight,0) , mp.weight_uom , nvl(mdd.volume,0) , mp.volume_uom , round(nvl(mdd.number_of_pallets,0)) , nvl(mdd.requested_quantity,0) , med.number1 , med.number2 , msikfv.unit_length , msikfv.unit_width , msikfv.unit_height , msikfv.dimension_uom_code , fvt.usable_length , fvt.usable_width , fvt.usable_height , med.char1 , 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 , mst_delivery_details mdd , mst_deliveries md , mst_plans mp , mst_delivery_legs mdl , mst_delivery_assignments mda , mst_trips mt , wsh_carriers wc , mtl_system_items_b_kfv msikfv , mtl_system_items_tl msitl , mfg_lookups ml , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , fte_vehicle_types fvt WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = mp.plan_id and med.plan_id = mdd.plan_id and med.plan_id = md.plan_id (+) and med.delivery_id = md.delivery_id (+) and med.plan_id = mda.plan_id and med.delivery_id = mda.delivery_id and mda.delivery_detail_id = mdd.delivery_detail_id and med.plan_id = mdl.plan_id (+) and med.delivery_id = mdl.delivery_id (+) and mdl.plan_id = mt.plan_id (+) and mdl.trip_id = mt.trip_id (+) and mt.carrier_id = wc.carrier_id (+) and mdd.inventory_item_id = msikfv.inventory_item_id and mdd.organization_id = msikfv.organization_id and mdd.inventory_item_id = msitl.inventory_item_id and mdd.organization_id = msitl.organization_id and msitl.language = userenv('LANG') and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_from_location_id = flp1.location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.ship_to_location_id = flp2.location_id and mt.vehicle_type_id = fvt.vehicle_type_id (+) and med.exception_type = 905

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.DELIVERY_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, MD.DELIVERY_NUMBER
, DECODE(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID)
, DECODE(MDD.CONTAINER_FLAG
, 1
, NULL
, MDD.SOURCE_CODE)
, MDD.SOURCE_HEADER_NUMBER
, MDD.SOURCE_LINE_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, FLP1.FACILITY_CODE)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, FLP1.DESCRIPTION)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_FROM_LOCATION_ID)
, 1
, 80))
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, WL1.ADDRESS1)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, WL1.CITY)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, WL1.STATE)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, WL1.POSTAL_CODE)
, DECODE(MED.EXCEPTION_TYPE
, 405
, NULL
, WL1.COUNTRY)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, FLP2.FACILITY_CODE)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, FLP2.DESCRIPTION)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_TO_LOCATION_ID)
, 1
, 80))
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, WL2.ADDRESS1)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, WL2.CITY)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, WL2.STATE)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, WL2.POSTAL_CODE)
, DECODE(MED.EXCEPTION_TYPE
, 404
, NULL
, WL2.COUNTRY)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.LATEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.EARLIEST_PICKUP_DATE
, MDD.LATEST_PICKUP_DATE
, MDD.EARLIEST_ACCEPTABLE_DATE
, MDD.LATEST_ACCEPTABLE_DATE
, DECODE(MED.EXCEPTION_TYPE
, 902
, NULL
, 903
, NULL
, 904
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER3
, 0))
, 1
, 20))
, DECODE(MED.EXCEPTION_TYPE
, 902
, NULL
, 903
, NULL
, 904
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER2
, 0))
, 1
, 20))
, DECODE(MED.EXCEPTION_TYPE
, 902
, NULL
, 903
, NULL
, 904
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER2
, 0)- NVL(NUMBER3
, 0))
, 1
, 20))
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(DECODE( SIGN(MDD.LATEST_PICKUP_DATE - MP.START_DATE)
, -1
, (MP.START_DATE - MDD.LATEST_PICKUP_DATE)
, DECODE( SIGN(MDD.LATEST_ACCEPTABLE_DATE - MP.START_DATE)
, -1
, (MP.START_DATE - MDD.LATEST_ACCEPTABLE_DATE)
, 0 ) )*24)
, 1
, 20)
, MSIKFV.CONCATENATED_SEGMENTS
, MSITL.DESCRIPTION
, NVL(MDD.GROSS_WEIGHT
, 0)
, MP.WEIGHT_UOM
, NVL(MDD.VOLUME
, 0)
, MP.VOLUME_UOM
, ROUND(NVL(MDD.NUMBER_OF_PALLETS
, 0))
, NVL(MDD.REQUESTED_QUANTITY
, 0)
, DECODE(MED.EXCEPTION_TYPE
, 903
, MED.NUMBER1
, 904
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 903
, MED.NUMBER2
, 904
, MED.NUMBER2
, TO_NUMBER(NULL))
, MSIKFV.UNIT_LENGTH
, MSIKFV.UNIT_WIDTH
, MSIKFV.UNIT_HEIGHT
, MSIKFV.DIMENSION_UOM_CODE
, DECODE(MED.EXCEPTION_TYPE
, 902
, MED.NUMBER1
, FVT.USABLE_LENGTH)
, DECODE(MED.EXCEPTION_TYPE
, 902
, MED.NUMBER2
, FVT.USABLE_WIDTH)
, DECODE(MED.EXCEPTION_TYPE
, 902
, MED.NUMBER3
, FVT.USABLE_HEIGHT)
, MED.CHAR1
, 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
, MST_DELIVERY_DETAILS MDD
, MST_DELIVERIES MD
, MST_PLANS MP
, MST_DELIVERY_LEGS MDL
, MST_TRIPS MT
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_B_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, FTE_VEHICLE_TYPES FVT
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.PLAN_ID = MDD.PLAN_ID
AND MED.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MED.PLAN_ID = MD.PLAN_ID (+)
AND MED.DELIVERY_ID = MD.DELIVERY_ID (+)
AND MED.PLAN_ID = MDL.PLAN_ID (+)
AND MED.DELIVERY_ID = MDL.DELIVERY_ID (+)
AND MDL.PLAN_ID = MT.PLAN_ID (+)
AND MDL.TRIP_ID = MT.TRIP_ID (+)
AND MT.CARRIER_ID = WC.CARRIER_ID (+)
AND MDD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND MSITL.LANGUAGE = USERENV('LANG')
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = FLP1.LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = FLP2.LOCATION_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID (+)
AND MED.EXCEPTION_TYPE NOT IN (704
, 905) UNION ALL SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, FLP1.FACILITY_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, SUBSTR(MST_WB_UTIL.GET_NAME(MED.LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, ML.MEANING
, NULL
, 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_LOCATIONS WL1
, FTE_LOCATION_PARAMETERS FLP1
WHERE MED.EXCEPTION_TYPE = 704
AND MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.LOCATION_ID = WL1.WSH_LOCATION_ID
AND MED.LOCATION_ID = FLP1.LOCATION_ID UNION ALL SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.DELIVERY_ID
, MT.TRIP_NUMBER
, WC.FREIGHT_CODE
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, MD.DELIVERY_NUMBER
, DECODE(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID)
, DECODE(MDD.CONTAINER_FLAG
, 1
, NULL
, MDD.SOURCE_CODE)
, MDD.SOURCE_HEADER_NUMBER
, MDD.SOURCE_LINE_NUMBER
, MDD.TP_REFER_HEADER_NUMBER
, MDD.REFERENCE_SOURCE_TYPE
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_FROM_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_TO_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.LATEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.EARLIEST_PICKUP_DATE
, MDD.LATEST_PICKUP_DATE
, MDD.EARLIEST_ACCEPTABLE_DATE
, MDD.LATEST_ACCEPTABLE_DATE
, NULL
, NULL
, NULL
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(DECODE( SIGN(MDD.LATEST_PICKUP_DATE - MP.PLAN_START_DATE)
, -1
, (MP.PLAN_START_DATE - MDD.LATEST_PICKUP_DATE)
, DECODE( SIGN(MDD.LATEST_ACCEPTABLE_DATE - MP.PLAN_START_DATE)
, -1
, (MP.PLAN_START_DATE - MDD.LATEST_ACCEPTABLE_DATE)
, 0) )*24)
, 1
, 20)
, MSIKFV.CONCATENATED_SEGMENTS
, MSITL.DESCRIPTION
, NVL(MDD.GROSS_WEIGHT
, 0)
, MP.WEIGHT_UOM
, NVL(MDD.VOLUME
, 0)
, MP.VOLUME_UOM
, ROUND(NVL(MDD.NUMBER_OF_PALLETS
, 0))
, NVL(MDD.REQUESTED_QUANTITY
, 0)
, MED.NUMBER1
, MED.NUMBER2
, MSIKFV.UNIT_LENGTH
, MSIKFV.UNIT_WIDTH
, MSIKFV.UNIT_HEIGHT
, MSIKFV.DIMENSION_UOM_CODE
, FVT.USABLE_LENGTH
, FVT.USABLE_WIDTH
, FVT.USABLE_HEIGHT
, MED.CHAR1
, 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
, MST_DELIVERY_DETAILS MDD
, MST_DELIVERIES MD
, MST_PLANS MP
, MST_DELIVERY_LEGS MDL
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_TRIPS MT
, WSH_CARRIERS WC
, MTL_SYSTEM_ITEMS_B_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, FTE_VEHICLE_TYPES FVT
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.PLAN_ID = MDD.PLAN_ID
AND MED.PLAN_ID = MD.PLAN_ID (+)
AND MED.DELIVERY_ID = MD.DELIVERY_ID (+)
AND MED.PLAN_ID = MDA.PLAN_ID
AND MED.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MED.PLAN_ID = MDL.PLAN_ID (+)
AND MED.DELIVERY_ID = MDL.DELIVERY_ID (+)
AND MDL.PLAN_ID = MT.PLAN_ID (+)
AND MDL.TRIP_ID = MT.TRIP_ID (+)
AND MT.CARRIER_ID = WC.CARRIER_ID (+)
AND MDD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND MSITL.LANGUAGE = USERENV('LANG')
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = FLP1.LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = FLP2.LOCATION_ID
AND MT.VEHICLE_TYPE_ID = FVT.VEHICLE_TYPE_ID (+)
AND MED.EXCEPTION_TYPE = 905