SELECT V_ORD.VISIT_DATE ,
V_ORD.VISIT_ID ,
V_ORD.AUTO_NUM_PACI ,
V_ORD.VISIT_SHIFTTIME ,
V_ORD.LF_RECID LOCATION_DESCRIPTION_LF ,
V_ORD.ADDRESSER_NAME ,
V_ORD.ADDRESSER_ID_TYPE ,
V_ORD.ADDRESSER_NAT ,
V_ORD.ADDRESSER_ROLE ,
(SELECT DTL.PAR_AR
FROM PAM_DB.SYS_GENERAL_LOOKUPS_DTL DTL
WHERE DTL.M_ID = 86
AND DTL.ID = V_ORD.ADDRESSER_ROLE
)ADDRESSER_ROLE_DESC ,
V_ORD.ADDRESSER_ID ,
MOSAL_USER.SAL_EMPLOYEE_DATA.GET_COUNTRY_DESC (
(SELECT SAL.COUNTRY_CODE
FROM MOSAL_USER.SAL_EMPLOYEE SAL
WHERE SAL.CIVIL_ID = V_ORD.ADDRESSER_ID
AND ROWNUM =1
)) ADDRESSER_NAT_DESC ,
V_ORD.INSPECTOR_SIGNATURE ,
V_ORD.VISIT_STATUS ,
V_ORD.LOCATION_STATUS ,
V_ORD.NOTES ,
V_ORD.ADDRESSER_NOTE ,
V_ORD_DTL.ORDER_ID ,
(SELECT NAME_AR
FROM SEC_SYSTEM_USERS S
WHERE S.USER_ID = V_ORD.INSPECTOR_ID
) AS "INSPECTOR_NAME" ,
NVL(
(SELECT INS_VIOLATION_AVOIDANCE.AVOIDANCE_PRINTING_DATE--VIO_DATE
FROM INS_VIOLATION_AVOIDANCE
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
AND ROWNUM = 1
) , V_ORD.RESPONSE_DATE )AS "V_DATE" ,
NVL(
(SELECT TO_CHAR (INS_VIOLATION_AVOIDANCE.AVOIDANCE_PRINTING_DATE--VIO_DATE
, 'HH:MI')
FROM INS_VIOLATION_AVOIDANCE
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
AND ROWNUM = 1
) ,TO_CHAR ( V_ORD.RESPONSE_DATE , 'HH:MI') )AS "NOW_TIME" ,
------ new time _type--------------------------------------------------
(
CASE
(SELECT TO_CHAR ( NVL(
(SELECT AVD.AVOIDANCE_PRINTING_DATE
FROM INS_VIOLATION_AVOIDANCE AVD
WHERE AVD.VISIT_ID = :VisitId
AND ROWNUM =1
) ,(V_ORD.RESPONSE_DATE)), 'AM' )
FROM DUAL
)
WHEN 'PM'
THEN 'مساءً'
WHEN 'م'
THEN 'مساءً'
WHEN 'AM'
THEN 'صباحا'
WHEN 'ص'
THEN 'صباحا'
END ) AS "TIME_TYPE" ,
----------------------------------------------------------------------------------------------------
------new date
PAM_DB.SYS_PKG.GET_WEEK_DAY_AR (NVL(
(SELECT AVD.AVOIDANCE_PRINTING_DATE
FROM INS_VIOLATION_AVOIDANCE AVD
WHERE AVD.VISIT_ID = :VisitId
AND ROWNUM =1
) , V_ORD.RESPONSE_DATE) ) DATE_WEEK
-------------------------
,
(SELECT WV_BLDG_NAME
FROM NDB.ADDRESS_AUTO_NO
WHERE WV_UNIT_COMPUTER_NO IN
(SELECT ADDRESS_AUTO_NO
FROM PMP_LICN_REQ REQ ,
NMP_LICNFILE LF
WHERE REQ.LICN_REQ_RECID = LF.LIC_RECID
AND LF.LF_RECID = V_ORD.LF_RECID
)
) AS "المالك" ,
(SELECT INSPECTOR_ROLE
FROM INS_INSPECTOR_PROFILE PROF
WHERE PROF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
) AS "INSPECTOR_ROLE" ,


(SELECT WV_STREET
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "STREET" ,
(SELECT WV_BLOCK
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "BLOCK" ,
(SELECT WV_DISTRICT_TEXT
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "DISTRICT" ,
(SELECT WV_GOERNORATE
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "GOERNORATE" ,
(SELECT WV_PLOT_NO
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "PLOT" ,
(SELECT WV_UNIT_NO
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "UNIT_NO" ,
(SELECT DECODE(FLOOR_NO,'0','الأرضي',FLOOR_NO)
FROM V_ADDRESS_AUTO_NO ADDR
WHERE ADDR.WV_UNIT_COMPUTER_NO = V_ORD.AUTO_NUM_PACI
) AS "FLOOR" ,


(SELECT ORD_DTL.RESULT_ID
FROM INS_VISIT_ORDER_DTL ORD_DTL
WHERE ORD_DTL.ORDER_ID = V_ORD_DTL.ORDER_ID
AND ROWNUM = 1
) AS "RES_ID" ,
(SELECT F.ONR_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 "صاحب العمل" ,
(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 "الاسم التجاري",
(SELECT P.LICN_CIVIL_ID
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 "رقم الترخيص" ,
(SELECT F.ONR_ID
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 "رقم الملف" ,
S_DTL.INSPECTION_TYPE_ID INSPECTION_TYPE_ID ,
(SELECT TYP.INSPECTOR_TYPE_NAME
FROM PAM_DB.INS_INSPECTION_TYPE TYP
WHERE TYP.INSPECTION_TYPE_ID = S_DTL.INSPECTION_TYPE_ID
) INSPECTION_TYPE_DESC ,
S_DTL.INS_SCOPE_ID INSPECTION_SCOP_ID ,
(SELECT INS_INSPECTION_SCOPE.INS_SCOPE_NAME
FROM INS_INSPECTION_SCOPE
WHERE S_DTL.INS_SCOPE_ID = INS_INSPECTION_SCOPE.INS_SCOPE_ID
) INSPECTION_SCOP_DESC ,
 (SELECT DISTINCT LRDB.ECONOMIC_ACTS_DESC
FROM LRD_BAL_V LRDB
WHERE LRDB.LF_RECID = V_ORD.LF_RECID
and LRDB.ECONOMIC_ACTS_RECID = S_DTL.ECONOMIC_ACT_RECID
) ECO_DESCRIPTION ,
DECODE (
(SELECT COUNT(CLS.CLAUSE_CODE) FROM PAM_DB.INS_VIOLATION_AVOIDANCE_DTL DTL ,
PAM_DB.INS_LAW_CLAUSE CLS WHERE DTL.CLAUSE_CODE = CLS.CLAUSE_CODE
AND DTL.CLAUSE_STATUS = 1
AND DTL.VISIT_ID = V_ORD.VISIT_ID
),0,0,1 )COUNT_VIO ,
(SELECT INS_VIOLATION_AVOIDANCE.VIO_ID
FROM INS_VIOLATION_AVOIDANCE
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
AND ROWNUM = 1
) VIO_ID,
V_ORD.LF_RECID ,
(SELECT LICN.LICN_CON_NO
FROM MOSAL_USER.NMP_LICNFILE_V LICN
WHERE LICN.LF_RECID = V_ORD.LF_RECID
) LICN_ID ,
(SELECT MV.COMM
FROM MOSAL_USER.NMP_LICNFILE_MV MV,
MOSAL_USER.NMP_FILE F
WHERE F.FILE_RECID = MV.FLE_RECID
AND MV.LF_RECID = V_ORD.LF_RECID
) AS "رقم السجل" ,
S_DTL.WORKER_ID ,
(SELECT EMP.EMP_NAME
  FROM MOSAL_USER.SNL_EMPLOYEE_REQUESTS   EMP
  WHERE EMP.CIVIL_ID = S_DTL.WORKER_ID
  AND ROWNUM         = 1
  ) WORKER_NAME ,
MOSAL_USER.SAL_EMPLOYEE_DATA.GET_COUNTRY_DESC (
(SELECT SAL.COUNTRY_CODE
FROM MOSAL_USER.SAL_EMPLOYEE SAL
WHERE SAL.CIVIL_ID = S_DTL.WORKER_ID
AND ROWNUM =1
)) WORKER_NAT ,
V_ORD_DTL.RESULT_ID RESULT_ID ,
(SELECT S.CIVIL_ID
FROM SEC_SYSTEM_USERS S
WHERE S.USER_ID = V_ORD.INSPECTOR_ID
) AS "CIVIL_ID" ,
(SELECT NVL(sal_employee_data.get_emp_name (civil_id) ,l.COMPANY_NAME) ownr_nam
FROM pmp_licn_req l ,
NMP_LICNFILE LF
WHERE L.LICN_REQ_RECID = LF.LIC_RECID
AND L.LICN_STAGE >= 12
AND lf.LF_RECID = V_ORD.LF_RECID
) manager ,
(SELECT (civil_id) ownr_Id
FROM pmp_licn_req l ,
NMP_LICNFILE LF
WHERE L.LICN_REQ_RECID = LF.LIC_RECID
AND L.LICN_STAGE >= 12
AND lf.LF_RECID = V_ORD.LF_RECID
) manager_id ,
LPAD(
(SELECT SUM (ld.ext_num_approve) + SUM (ld.int_num_approve)
FROM nmp_labreqdetails ld,
nmp_licnfile lf,
nmp_labestimationreq lb
WHERE ld.ler_recid = lb.ler_recid
AND lb.lf_recid = lf.lf_recid
AND lf.LICN_STATUS NOT IN ('C')
AND lf.fle_recid =
(SELECT LICN.FLE_RECID
FROM MOSAL_USER.NMP_LICNFILE_V LICN
WHERE LICN.LF_RECID = V_ORD.LF_RECID
)
) , 5, 0) labest_emp_count ,
(SELECT SYS_PKG.GET_LOOKUP_NAME (60 ,V_ORD.LOCATION_STATUS )
FROM PAM_DB.INS_VISIT_ORDER V_ORD
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
) Arrival_status ,
(SELECT V_ORD.LOCATION_STATUS
FROM PAM_DB.INS_VISIT_ORDER V_ORD
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
) Arrival_status_id ,
TO_NUMBER(V_ORD_DTL.WORKER_LOCATION_SATISFY ) WORKER_LOCATION_SATISFY ,
(SELECT PRF.GOVERNORATE_ID
FROM INS_INSPECTOR_PROFILE PRF
WHERE PRF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
) AS "INS_GOV_ID" ,
(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
)
) AS "INS_GOV_DESC" ,
V_ORD_DTL.notes AS ADDER_NOTE ,
LPAD (
(SELECT COUNT (S.SAL_EMPLOYEE_RECID)
FROM SAL_EMPLOYEE s
WHERE lf_recid = V_ORD.LF_RECID
AND employee_status = 1
AND S.COUNTRY_CODE IN
( SELECT COUNTRY_CODE# FROM hr_g_country# WHERE NVL(GROUP_CODE,0)=1
)
), 5, 0 ) KW_EMP_COUNT ,
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 ,
LPAD (
(SELECT COUNT (1)
FROM nmp_workathreq N
WHERE REQ_STAGE = 3
AND EAZ_STATUS IN (1, 4)
AND N.LF_RECID =V_ORD.LF_RECID
), 5, 0 ) COUNT_WORKEZN ,
LPAD (
(SELECT COUNT (S.SAL_EMPLOYEE_RECID)
FROM SAL_EMPLOYEE s
WHERE lf_recid = V_ORD.LF_RECID
AND employee_status = 1
), 5, 0 ) EMP_COUNT,
(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"
FROM PAM_DB.INS_VISIT_ORDER V_ORD ,
PAM_DB.INS_VISIT_ORDER_DTL V_ORD_DTL ,
PAM_DB.INS_SOURCE_ORDER_DTL S_DTL
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
 AND V_ORD_DTL.ORDER_ID = :OrderID
AND S_DTL.ORDER_ID   = V_ORD_DTL.ORDER_ID
  AND V_ORD.VISIT_ID = :VisitId