VIEW: APPS.MST_INSUF_CONECT_TIME_ORDER_V

Source

View Text - Preformatted

SELECT med.plan_id , med.delivery_id , med.delivery_detail_id , med.exception_type , flp.facility_id , med.exception_detail_id , md.delivery_number , substr(mst_wb_util.get_name(med.location_id),1,80) , flp.facility_code , flp.description , wl.address1 , wl.city , wl.state , wl.postal_code , wl.country , 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 , substr(mst_wb_util.get_hr_min((med.date2 - med.date1) * 24),1,20) , (med.date2 - med.date1) * 24 , substr(mst_wb_util.get_hr_min(nvl(number1,0) + nvl(number2,0) + nvl(number3,0)),1,20) , nvl(number1,0) + nvl(number2,0) + nvl(number3,0) , substr(mst_wb_util.get_hr_min(nvl(med.number1,0)),1,20) , nvl(med.number1,0) , substr(mst_wb_util.get_hr_min(nvl(med.number2,0)),1,20) , nvl(med.number2,0) , substr(mst_wb_util.get_hr_min(nvl(med.number3,0)),1,20) , nvl(med.number3,0) , substr(mst_wb_util.get_hr_min(nvl(number1,0) + nvl(number2,0) + nvl(number3,0) - (med.date2 - med.date1)*24),1,20) , nvl(number1,0) + nvl(number2,0) + nvl(number3,0) - (med.date2 - med.date1)*24 , nvl(sum(md.gross_weight),0) , mp.weight_uom , nvl(sum(md.volume),0) , mp.volume_uom , nvl(sum(md.number_of_pallets),0) , nvl(sum(md.number_of_pieces),0) , 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_deliveries md , mst_plans mp , mfg_lookups ml , wsh_locations wl , fte_location_parameters flp WHERE med.status = ml.lookup_code and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.plan_id = md.plan_id and med.delivery_id = md.delivery_id and med.plan_id = mp.plan_id and med.location_id = wl.wsh_location_id and med.location_id = flp.location_id group by med.plan_id , med.delivery_id , med.delivery_detail_id , med.exception_type , flp.facility_id , med.exception_detail_id , md.delivery_number , substr(mst_wb_util.get_name(med.location_id),1,80) , flp.facility_code , flp.description , wl.address1 , wl.city , wl.state , wl.postal_code , wl.country , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date1),1,40) , med.date1 , substr(mst_wb_util.get_local_chardtzone(med.location_id, med.date2),1,40) , med.date2 , substr(mst_wb_util.get_hr_min((med.date2 - med.date1) * 24),1,20) , (med.date2 - med.date1) * 24 , substr(mst_wb_util.get_hr_min(nvl(number1,0) + nvl(number2,0) + nvl(number3,0)),1,20) , nvl(number1,0) + nvl(number2,0) + nvl(number3,0) , substr(mst_wb_util.get_hr_min(nvl(med.number1,0)),1,20) , nvl(med.number1,0) , substr(mst_wb_util.get_hr_min(nvl(med.number2,0)),1,20) , nvl(med.number2,0) , substr(mst_wb_util.get_hr_min(nvl(med.number3,0)),1,20) , nvl(med.number3,0) , substr(mst_wb_util.get_hr_min(nvl(number1,0) + nvl(number2,0) + nvl(number3,0) - (med.date2 - med.date1)*24),1,20) , nvl(number1,0) + nvl(number2,0) + nvl(number3,0) - (med.date2 - med.date1)*24 , mp.weight_uom , mp.volume_uom , ml.meaning

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.DELIVERY_ID
, MED.DELIVERY_DETAIL_ID
, MED.EXCEPTION_TYPE
, FLP.FACILITY_ID
, MED.EXCEPTION_DETAIL_ID
, MD.DELIVERY_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MED.LOCATION_ID)
, 1
, 80)
, FLP.FACILITY_CODE
, FLP.DESCRIPTION
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, 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
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MED.DATE2 - MED.DATE1) * 24)
, 1
, 20)
, (MED.DATE2 - MED.DATE1) * 24
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0))
, 1
, 20)
, NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER1
, 0))
, 1
, 20)
, NVL(MED.NUMBER1
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER2
, 0))
, 1
, 20)
, NVL(MED.NUMBER2
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER3
, 0))
, 1
, 20)
, NVL(MED.NUMBER3
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0) - (MED.DATE2 - MED.DATE1)*24)
, 1
, 20)
, NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0) - (MED.DATE2 - MED.DATE1)*24
, NVL(SUM(MD.GROSS_WEIGHT)
, 0)
, MP.WEIGHT_UOM
, NVL(SUM(MD.VOLUME)
, 0)
, MP.VOLUME_UOM
, NVL(SUM(MD.NUMBER_OF_PALLETS)
, 0)
, NVL(SUM(MD.NUMBER_OF_PIECES)
, 0)
, 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_DELIVERIES MD
, MST_PLANS MP
, MFG_LOOKUPS ML
, WSH_LOCATIONS WL
, FTE_LOCATION_PARAMETERS FLP
WHERE MED.STATUS = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.PLAN_ID = MD.PLAN_ID
AND MED.DELIVERY_ID = MD.DELIVERY_ID
AND MED.PLAN_ID = MP.PLAN_ID
AND MED.LOCATION_ID = WL.WSH_LOCATION_ID
AND MED.LOCATION_ID = FLP.LOCATION_ID GROUP BY MED.PLAN_ID
, MED.DELIVERY_ID
, MED.DELIVERY_DETAIL_ID
, MED.EXCEPTION_TYPE
, FLP.FACILITY_ID
, MED.EXCEPTION_DETAIL_ID
, MD.DELIVERY_NUMBER
, SUBSTR(MST_WB_UTIL.GET_NAME(MED.LOCATION_ID)
, 1
, 80)
, FLP.FACILITY_CODE
, FLP.DESCRIPTION
, WL.ADDRESS1
, WL.CITY
, WL.STATE
, WL.POSTAL_CODE
, WL.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE1)
, 1
, 40)
, MED.DATE1
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MED.LOCATION_ID
, MED.DATE2)
, 1
, 40)
, MED.DATE2
, SUBSTR(MST_WB_UTIL.GET_HR_MIN((MED.DATE2 - MED.DATE1) * 24)
, 1
, 20)
, (MED.DATE2 - MED.DATE1) * 24
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0))
, 1
, 20)
, NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER1
, 0))
, 1
, 20)
, NVL(MED.NUMBER1
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER2
, 0))
, 1
, 20)
, NVL(MED.NUMBER2
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(MED.NUMBER3
, 0))
, 1
, 20)
, NVL(MED.NUMBER3
, 0)
, SUBSTR(MST_WB_UTIL.GET_HR_MIN(NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0) - (MED.DATE2 - MED.DATE1)*24)
, 1
, 20)
, NVL(NUMBER1
, 0) + NVL(NUMBER2
, 0) + NVL(NUMBER3
, 0) - (MED.DATE2 - MED.DATE1)*24
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, ML.MEANING