|
1
|
SELECT
|
|
2
|
V_ORD.INSPECTOR_ID,
|
|
3
|
(SELECT NAME_AR FROM SEC_SYSTEM_USERS S WHERE S.USER_ID = V_ORD.INSPECTOR_ID
|
|
4
|
) AS "INSPECTOR_NAME" ,
|
|
5
|
(SELECT PAR_AR DEPT
|
|
6
|
FROM PAM_DB.SYS_GENERAL_LOOKUPS_DTL
|
|
7
|
WHERE M_ID = 2
|
|
8
|
AND ID IN (1,2,3,4,5,6,7,8,11)
|
|
9
|
AND SYS_GENERAL_LOOKUPS_DTL.ID =
|
|
10
|
(SELECT PRF.GOVERNORATE_ID
|
|
11
|
FROM INS_INSPECTOR_PROFILE PRF
|
|
12
|
WHERE PRF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
|
|
13
|
)
|
|
14
|
)INS_GOV_DESC,
|
|
15
|
(SELECT INS_INSPECTION_SCOPE.INS_SCOPE_NAME
|
|
16
|
FROM INS_INSPECTION_SCOPE
|
|
17
|
WHERE V_ORD.INS_SCOPE_ID = INS_INSPECTION_SCOPE.INS_SCOPE_ID
|
|
18
|
) INSPECTION_SCOP_DESC ,
|
|
19
|
(SELECT N.CIVIL_ID from INS_INSPECTOR_PROFILE N
|
|
20
|
WHERE N.INSPECTOR_ID= V_ORD.INSPECTOR_ID)CivilID,
|
|
21
|
(SELECT SYS_PKG.GET_LOOKUP_NAME ( 81, INS_DECISION_ID ) "Decision" FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID= :VisitId)"Decision",
|
|
22
|
( 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",
|
|
23
|
(SELECT DECISION_DATE FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID = :VisitId ) "DECISIONDATE",
|
|
24
|
(SELECT DECISION_DESC FROM INS_INSPECTION_VISIT_DECISION where VISIT_ID = :VisitId ) "NOTE",
|
|
25
|
l.company_name,
|
|
26
|
l.licn_civil_id,
|
|
27
|
l.licn_typ_recid,
|
|
28
|
l.licn_comm_edate,
|
|
29
|
l.licn_comm_sdate,
|
|
30
|
(SELECT NAME_AR FROM SEC_SYSTEM_USERS where USER_ID=V_ORD.INSPECTOR_ID)InspectorName,
|
|
31
|
(SELECT
|
|
32
|
SYS_GENERAL_LOOKUPS_DTL.PAR_AR PAR_AR
|
|
33
|
FROM
|
|
34
|
SYS_GENERAL_LOOKUPS_DTL
|
|
35
|
where M_Id=37 AND ID=V_ORD.VISIT_STATUS)Result
|
|
36
|
,
|
|
37
|
(SELECT
|
|
38
|
SYS_GENERAL_LOOKUPS_DTL.PAR_AR PAR_AR
|
|
39
|
FROM
|
|
40
|
SYS_GENERAL_LOOKUPS_DTL
|
|
41
|
where M_Id=46 AND ID=:OpId)OpVisit,
|
|
42
|
l.branch_no,
|
|
43
|
NVL(l.trade_name,l.company_name) trade_name,
|
|
44
|
l.ECONOMIC_ACT_RECID act_pur_recid,
|
|
45
|
l.address_auto_no,
|
|
46
|
l.licn_req_recid,
|
|
47
|
lf.lf_recid,
|
|
48
|
f.file_recid,
|
|
49
|
f.onr_id,
|
|
50
|
f.onr_name,
|
|
51
|
lookup_pkg.get_lookup_meaning (14,
|
|
52
|
(SELECT lt.TYPE_CATEGORY
|
|
53
|
FROM PMP_LICN_TYPES lt
|
|
54
|
WHERE l.licn_typ_recid = lt.licn_types_recid
|
|
55
|
)) licence_type_desc,
|
|
56
|
Nmp_Get_Pkg.get_LICNFILE_ACT_CODE ('', LF.LF_RECID ) act_pur_code ,
|
|
57
|
Nmp_Get_Pkg.get_LICNFILE_ACT_DECS ('', LF.LF_RECID ) act_pur_desc
|
|
58
|
,V_ORD.LOCATION_DESCRIPTION,
|
|
59
|
V_ORD.AUTO_NUM_PACI
|
|
60
|
FROM PMP_LICN_REQ l,
|
|
61
|
NMP_LICNFILE lf,
|
|
62
|
PAM_DB.INS_VISIT_ORDER V_ORD,
|
|
63
|
NMP_FILE f
|
|
64
|
|
|
65
|
--PMP_LICN_TYPES lt
|
|
66
|
WHERE lf.lic_recid = l.licn_req_recid
|
|
67
|
AND lf.fle_recid = f.file_recid
|
|
68
|
AND Nmp_Get_Pkg.licn_isvalid (l.licn_req_recid) = 1
|
|
69
|
AND Nmp_Get_Pkg.file_isvalid (f.file_recid) = 1
|
|
70
|
and V_ORD.VISIT_ID = :VisitId
|
|
71
|
AND lf.lf_recid = :p_LfRecId
|
|
72
|
AND lf.lf_type = 1
|