VIEW: APPS.MST_PLN_LATE_EARLY_DELIVERY_V

Source

View Text - Preformatted

SELECT mdd.plan_id , med.exception_type , med.exception_detail_id , md.delivery_id , md.delivery_number , decode(mdd.split_from_delivery_detail_id,null,mdd.sr_delivery_detail_id,mdd.delivery_detail_id) , substr(mst_wb_util.get_name(mdd.ship_from_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , 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) , mdd.earliest_pickup_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.latest_pickup_date),1,40) , mdd.latest_pickup_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, med.date1),1,40) , med.date1 , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , mdd.earliest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.latest_acceptable_date),1,40) , mdd.latest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, med.date2),1,40) , med.date2 , substr(mst_wb_util.get_hr_min(decode(med.exception_type ,100,med.date2-mdd.latest_acceptable_date ,101,mdd.earliest_acceptable_date-med.date2 ,102,med.date1-mdd.latest_pickup_date ,103,mdd.earliest_pickup_date-med.date1)*24),1,20) , decode(med.exception_type ,100,med.date2-mdd.latest_acceptable_date ,101,mdd.earliest_acceptable_date-med.date2 ,102,med.date1-mdd.latest_pickup_date ,103,mdd.earliest_pickup_date-med.date1)*24 , 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_delivery_details mdd , mst_deliveries md , mst_exception_details med , wsh_locations wl1 , wsh_locations wl2 , mfg_lookups ml WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' 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 mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id

View Text - HTML Formatted

SELECT MDD.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, DECODE(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID)
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_FROM_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, 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)
, MDD.EARLIEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.LATEST_PICKUP_DATE)
, 1
, 40)
, MDD.LATEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MED.DATE1)
, 1
, 40)
, MED.DATE1
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.EARLIEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.LATEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MED.DATE2)
, 1
, 40)
, MED.DATE2
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(DECODE(MED.EXCEPTION_TYPE
, 100
, MED.DATE2-MDD.LATEST_ACCEPTABLE_DATE
, 101
, MDD.EARLIEST_ACCEPTABLE_DATE-MED.DATE2
, 102
, MED.DATE1-MDD.LATEST_PICKUP_DATE
, 103
, MDD.EARLIEST_PICKUP_DATE-MED.DATE1)*24)
, 1
, 20)
, DECODE(MED.EXCEPTION_TYPE
, 100
, MED.DATE2-MDD.LATEST_ACCEPTABLE_DATE
, 101
, MDD.EARLIEST_ACCEPTABLE_DATE-MED.DATE2
, 102
, MED.DATE1-MDD.LATEST_PICKUP_DATE
, 103
, MDD.EARLIEST_PICKUP_DATE-MED.DATE1)*24
, 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_DELIVERY_DETAILS MDD
, MST_DELIVERIES MD
, MST_EXCEPTION_DETAILS MED
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MFG_LOOKUPS ML
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
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 MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID