SELECT
v_worker.visit_exist_id,
(SELECT con.COUNTRY_DESC FROM MOSAL_USER.V_HR_G_COUNTRY con , NDB.CIVIL_INFO info WHERE con.COUNTRY_CODE# = info.NATIONALITY AND info.CIVIL_ID = v_worker.identific_num  )
 as country_desc,
  v_worker.identific_num,
v_worker.NATIONALITY_ID,
  V_WORKER.WORKER_NAME,
(SELECT info.NAME FROM NDB.CIVIL_INFO info WHERE CIVIL_ID =  v_worker.identific_num)worker_name_civil_info,
  v_worker.visit_dtl_id,
  v_worker.identific_notes,
  v_worker.emp_lf_recid,
  v_worker.is_legal_on_lf,
( SELECT COUNTRY_DESC
          FROM V_HR_G_COUNTRY vr where VR.COUNTRY_CODE# = V_WORKER.NATIONALITY_ID )country_name,
  (SELECT created_date
  FROM pam_db.ins_visit_order_dtl v_ord_dtl
  WHERE v_ord_dtl.visit_id =:visitid
  AND ROWNUM               = 1
  ) created_date,
  -----------------------------------------------------------------
  TO_CHAR ( v_ord.RESPONSE_DATE, 'HH:MI') AS "NOW_TIME" ----- الساعة
  ,
  (
  CASE ( TO_CHAR ( v_ord.RESPONSE_DATE, 'AM') )
    WHEN 'PM'
    THEN 'مساء'
    WHEN 'AM'
    THEN 'صباحا'
  END ) AS "TIME_TYPE" ,
  ------الاسم التجاري
  (
  SELECT P.COMPANY_NAME
  FROM MOSAL_USER.PMP_LICN_REQ P,
    MOSAL_USER.NMP_LICNFILE N,
    MOSAL_USER.NMP_FILE F
  WHERE P.LICN_REQ_RECID = N.LIC_RECID
  AND F.FILE_RECID       = N.FLE_RECID
  AND N.LF_RECID         = V_ORD.LF_RECID
  ) AS "الاسم التجاري",
  ------اجمالي عدد العمالة
  LPAD (
  (SELECT COUNT (S.SAL_EMPLOYEE_RECID)
  FROM SAL_EMPLOYEE s
  WHERE lf_recid IN
    (SELECT lf_recid
    FROM NMP_LICNFILE lf
    WHERE lf.fle_recid =
      (SELECT lf.fle_recid FROM NMP_LICNFILE lf WHERE LF.LF_RECID =V_ORD.LF_RECID
      )
    )
  AND employee_status = 1
  ), 5, 0 ) EMP_COUNT_FILE,
  v_ord.LICN_ID ,-- رقم الرخصة
  (SELECT l.licn_civil_id
  FROM pmp_licn_req l ,
    nmp_licnfile lf
  WHERE l.licn_req_recid = lf.lic_recid
  AND LF.LF_RECID        =V_ORD.LF_RECID
  ) licn_civil_id,---- رقم الجهة المدنية
  (SELECT f.onr_name
  FROM mosal_user.nmp_file f,
    mosal_user.nmp_licnfile lf,
    pam_db.ins_source_order_dtl order_id,
    pam_db.ins_visit_order_dtl v_ord_dtl
  WHERE f.file_recid     = lf.fle_recid
  AND lf.lf_recid        = order_id.licn_id
  AND order_id.order_id  = v_ord_dtl.order_id
  AND v_ord_dtl.visit_id =:visitid
  AND ROWNUM             =1
  ) file_name,
  (SELECT f.onr_id
  FROM mosal_user.nmp_file f,
    mosal_user.nmp_licnfile lf,
    pam_db.ins_source_order_dtl order_id,
    pam_db.ins_visit_order_dtl v_ord_dtl
  WHERE f.file_recid     = lf.fle_recid
  AND lf.lf_recid        = order_id.licn_id
  AND order_id.order_id  = v_ord_dtl.order_id
  AND v_ord_dtl.visit_id =:visitid
  AND ROWNUM             =1
  ) file_id,
  (
  CASE v_worker.is_legal_on_lf
    WHEN 1
    THEN 'مسجل على الملف'
    WHEN 0
    THEN 'غير مسجل على الملف'
    ELSE '-'
  END ) AS is_legal_on_lf_desc,
  (SELECT lok.par_ar
  FROM pam_db.sys_general_lookups_dtl lok
  WHERE lok.m_id = 64
  AND lok.id     = v_worker.identification_type_id
  ) AS identification_type_desc,
  mosal_user.sal_employee_data.get_country_desc(
  (SELECT sal.country_code
  FROM mosal_user.sal_employee sal
  WHERE sal.civil_id = v_worker.identific_num
  AND ROWNUM         = 1
  ) ) nationality_desc,
  (SELECT lok.par_ar
  FROM pam_db.sys_general_lookups_dtl lok
  WHERE lok.m_id = 66
  AND lok.id     = v_worker.clarification_id
  ) AS clarification_desc,
  v_ord_dtl.order_id,
  --new
  (
  SELECT NAME_AR
  FROM SEC_SYSTEM_USERS S
  WHERE S.USER_ID = V_ORD.INSPECTOR_ID
  ) AS "INSPECTOR_NAME" ,
  (SELECT v_o_att.ATTACHMENT_ID
  FROM INS_VISIT_ORDER_ATTACHMENT v_o_att
  WHERE V_O_ATT.VISIT_ID = :visitid
  AND ATTACHMENT_TYPE    = 1
  AND rownum             =1
  )AS "ATTACHMENT_ID",
  (SELECT COUNT (S.SAL_EMPLOYEE_RECID)
  FROM SAL_EMPLOYEE s
  WHERE lf_recid      = V_ORD.LF_RECID
  AND employee_status = 1
  ) EMP_COUNT,
  V_ORD.AUTO_NUM_PACI,
  (SELECT ins_s.INSPECTION_TYPE_ID
  FROM ins_source_order_dtl ins_s
  WHERE ORDER_ID= v_ord_dtl.ORDER_ID
  )INSPECTION_TYPE_ID
  -----------------
  ,
  ADDRESS.WV_UNIT_COMPUTER_NO ,
  ADDRESS.WV_BLDG_COMPUTER_NAME ,
  ADDRESS.WV_GOV_CODE ,
  ADDRESS.DISTRICT ,
  ADDRESS.WV_BLOCK ,
  ADDRESS.WV_PLOT_NO ,
  ADDRESS.WV_STREET ,
  ADDRESS.WV_UNIT_NO ,
  ADDRESS.WV_UNIT_TYPE_TEXT ,
  ADDRESS.UNIT_TYPE ,
  ADDRESS.WV_BLDG_NAME ,
  ADDRESS.WV_GOERNORATE ,
  ADDRESS.WV_DISTRICT_TEXT ,
  ADDRESS.CONSTRUCTION_NO ,
  ADDRESS.FLOOR_NO ,
  ADDRESS.IN_USE
  ---الاخطار
  ,
  TO_CHAR (
  (SELECT AVD.VOILATION_PRINTING_DATE
  FROM INS_VIOLATION_AVOIDANCE AVD
  WHERE AVD.VISIT_ID = :visitid
  AND ROWNUM         =1
  ), 'HH:MI' ) AS "INS_TIME_visit1" ,
  (
  CASE TO_CHAR (
      (SELECT AVD.VOILATION_PRINTING_DATE
      FROM INS_VIOLATION_AVOIDANCE AVD
      WHERE AVD.VISIT_ID = :visitid
      AND ROWNUM         =1
      ), 'AM')
    WHEN 'PM'
    THEN 'مساء'
    WHEN 'AM'
    THEN 'صباحا'
  END ) AS "TIME_TYPE_visit1"
  --  *----------------------------------------------------------المخالفة
  ,
  (SELECT TO_CHAR (
    (SELECT AVD.AVOIDANCE_PRINTING_DATE
    FROM INS_VIOLATION_AVOIDANCE AVD
    WHERE AVD.VISIT_ID = :visitid
    AND ROWNUM         =1
    ), 'HH:MI')
  FROM DUAL
  ) AS "INS_TIME_visit_2" ,
  (
  CASE
      (SELECT TO_CHAR (
        (SELECT AVD.AVOIDANCE_PRINTING_DATE
        FROM INS_VIOLATION_AVOIDANCE AVD
        WHERE AVD.VISIT_ID = :visitid
        AND ROWNUM         =1
        ), 'AM')
      FROM DUAL
      )
    WHEN 'PM'
    THEN 'مساء'
    WHEN 'AM'
    THEN 'صباحا'
  END ) AS "TIME_TYPE_visit_2"
FROM V_ADDRESS_AUTO_NO ADDRESS ,
  pam_db.ins_visit_existing_workers v_worker,
  pam_db.ins_visit_order_dtl v_ord_dtl ,
  INS_VISIT_ORDER v_ord
WHERE ADDRESS.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
AND v_worker.visit_dtl_id         = v_ord_dtl.visit_id_dtl
AND v_ord_dtl.visit_id            = V_ORD.VISIT_ID
AND v_ord_dtl.visit_id            =:visitid
order by  worker_name