VIEW: APPS.MST_FAC_FAC_INCOMP_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.exception_sub_type , med.delivery_id , flp1.facility_id , flp2.facility_id , med.location_id , med.number1 , (select ml1.meaning from mfg_lookups ml1 where ml1.lookup_type='MST_STRING' and ml1.lookup_code=decode(med.number2,2,9,8)) , flp1.facility_code , substr ( mst_wb_util.get_name ( flp1.location_id ), 1, 80 ) , substr ( mst_wb_util.get_company_type ( flp1.facility_id ), 1, 80 ) , md.delivery_number , flp1.description , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , flp2.facility_code , substr ( mst_wb_util.get_name ( flp2.location_id ), 1, 80 ) , flp2.description , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , ml.meaning , substr ( mst_wb_util.get_workflow_status ( med.plan_id, med.exception_detail_id ), 1, 20 ) from mst_exception_details med , fte_location_parameters flp1 , wsh_locations wl1 , fte_location_parameters flp2 , wsh_locations wl2 , mfg_lookups ml , mst_deliveries md WHERE med.plan_id = md.plan_id and med.delivery_id = md.delivery_id and med.location_id = flp1.location_id and med.location_id = wl1.wsh_location_id and med.number1 = flp2.location_id and med.number1 = wl2.wsh_location_id and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.status = ml.lookup_code

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_SUB_TYPE
, MED.DELIVERY_ID
, FLP1.FACILITY_ID
, FLP2.FACILITY_ID
, MED.LOCATION_ID
, MED.NUMBER1
, (SELECT ML1.MEANING
FROM MFG_LOOKUPS ML1
WHERE ML1.LOOKUP_TYPE='MST_STRING'
AND ML1.LOOKUP_CODE=DECODE(MED.NUMBER2
, 2
, 9
, 8))
, FLP1.FACILITY_CODE
, SUBSTR ( MST_WB_UTIL.GET_NAME ( FLP1.LOCATION_ID )
, 1
, 80 )
, SUBSTR ( MST_WB_UTIL.GET_COMPANY_TYPE ( FLP1.FACILITY_ID )
, 1
, 80 )
, MD.DELIVERY_NUMBER
, FLP1.DESCRIPTION
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, FLP2.FACILITY_CODE
, SUBSTR ( MST_WB_UTIL.GET_NAME ( FLP2.LOCATION_ID )
, 1
, 80 )
, FLP2.DESCRIPTION
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, ML.MEANING
, SUBSTR ( MST_WB_UTIL.GET_WORKFLOW_STATUS ( MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID )
, 1
, 20 )
FROM MST_EXCEPTION_DETAILS MED
, FTE_LOCATION_PARAMETERS FLP1
, WSH_LOCATIONS WL1
, FTE_LOCATION_PARAMETERS FLP2
, WSH_LOCATIONS WL2
, MFG_LOOKUPS ML
, MST_DELIVERIES MD
WHERE MED.PLAN_ID = MD.PLAN_ID
AND MED.DELIVERY_ID = MD.DELIVERY_ID
AND MED.LOCATION_ID = FLP1.LOCATION_ID
AND MED.LOCATION_ID = WL1.WSH_LOCATION_ID
AND MED.NUMBER1 = FLP2.LOCATION_ID
AND MED.NUMBER1 = WL2.WSH_LOCATION_ID
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.STATUS = ML.LOOKUP_CODE