PAMInspection

SELECT VORD.VISIT_ID ,VORD.LF_RECID,

  VORD.VISIT_DATE ,

  VORD.INSPECTOR_ID,

  VDEC.DECISION_DESC ,

  SYS_PKG.GET_LOOKUP_NAME ( 81, VDEC.INS_DECISION_ID ) Decision ,

  VDEC.OBSTACLE_ID ,

  VDEC.OBSTACLE_EXPIRY_DATE

  , (select PROF.GOVERNORATE_ID     from INS_INSPECTOR_PROFILE PROF where VORD.INSPECTOR_ID = PROF.INSPECTOR_ID)  GOVERNORATE_ID

FROM INS_VISIT_ORDER VORD ,

  INS_INSPECTION_VISIT_DECISION VDEC

WHERE VORD.VISIT_ID    = VDEC.VISIT_ID

AND VORD.VISIT_STATUS IN (5,6);