SELECT
 V_ORD.INSPECTOR_ID,
  (SELECT NAME_AR FROM SEC_SYSTEM_USERS S WHERE S.USER_ID = V_ORD.INSPECTOR_ID
  ) AS "INSPECTOR_NAME" ,
     (SELECT PAR_AR DEPT
  FROM PAM_DB.SYS_GENERAL_LOOKUPS_DTL
  WHERE M_ID                     = 2
  AND ID                        IN (1,2,3,4,5,6,7,8,11)
  AND SYS_GENERAL_LOOKUPS_DTL.ID =
    (SELECT PRF.GOVERNORATE_ID
    FROM INS_INSPECTOR_PROFILE PRF
    WHERE PRF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
    )
  )INS_GOV_DESC,
  (SELECT INS_INSPECTION_SCOPE.INS_SCOPE_NAME
  FROM INS_INSPECTION_SCOPE
  WHERE V_ORD.INS_SCOPE_ID = INS_INSPECTION_SCOPE.INS_SCOPE_ID
  ) INSPECTION_SCOP_DESC ,
  (SELECT N.CIVIL_ID from INS_INSPECTOR_PROFILE N
  WHERE N.INSPECTOR_ID= V_ORD.INSPECTOR_ID)CivilID,
(SELECT SYS_PKG.GET_LOOKUP_NAME ( 81, INS_DECISION_ID ) "Decision" FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID=  :VisitId)"Decision",
( SELECT NAME_AR FROM SEC_SYSTEM_USERS S WHERE S.USER_ID =(SELECT DECISION_MAKER_ID  FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID = :VisitId ) )"DECISIONMAKER",
(SELECT DECISION_DATE  FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID =   :VisitId ) "DECISIONDATE",
(SELECT DECISION_DESC  FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID =  :VisitId ) "NOTE",
  l.company_name,
  l.licn_civil_id,
  l.licn_typ_recid,
  l.licn_comm_edate,
  l.licn_comm_sdate,
  (SELECT NAME_AR FROM SEC_SYSTEM_USERS where USER_ID=V_ORD.INSPECTOR_ID)InspectorName,
  (SELECT
    SYS_GENERAL_LOOKUPS_DTL.PAR_AR PAR_AR
FROM
    SYS_GENERAL_LOOKUPS_DTL
    where M_Id=37 AND ID=V_ORD.VISIT_STATUS)Result
    ,
(SELECT
    SYS_GENERAL_LOOKUPS_DTL.PAR_AR PAR_AR
FROM
    SYS_GENERAL_LOOKUPS_DTL
    where M_Id=46 AND ID=:OpId)OpVisit,
  l.branch_no,
  NVL(l.trade_name,l.company_name) trade_name,
  l.ECONOMIC_ACT_RECID act_pur_recid,
  l.address_auto_no,
  l.licn_req_recid,
  lf.lf_recid,
  f.file_recid,
  f.onr_id,
  f.onr_name,
  lookup_pkg.get_lookup_meaning (14,
  (SELECT lt.TYPE_CATEGORY
  FROM PMP_LICN_TYPES lt
  WHERE l.licn_typ_recid = lt.licn_types_recid
  )) licence_type_desc,
  Nmp_Get_Pkg.get_LICNFILE_ACT_CODE ('', LF.LF_RECID ) act_pur_code ,
  Nmp_Get_Pkg.get_LICNFILE_ACT_DECS ('', LF.LF_RECID ) act_pur_desc
  ,V_ORD.LOCATION_DESCRIPTION,
   V_ORD.AUTO_NUM_PACI
FROM PMP_LICN_REQ l,
  NMP_LICNFILE lf,
PAM_DB.INS_VISIT_ORDER V_ORD,
  NMP_FILE f

  --PMP_LICN_TYPES lt
WHERE lf.lic_recid                              = l.licn_req_recid
AND lf.fle_recid                                = f.file_recid
AND Nmp_Get_Pkg.licn_isvalid (l.licn_req_recid) = 1
AND Nmp_Get_Pkg.file_isvalid (f.file_recid)     = 1
and  V_ORD.VISIT_ID                             = :VisitId
AND lf.lf_recid                                 = :p_LfRecId
AND lf.lf_type                                  = 1