|
1
|
SELECT V_ORD.VISIT_DATE ,
|
|
2
|
V_ORD.VISIT_ID ,
|
|
3
|
V_ORD.AUTO_NUM_PACI ,
|
|
4
|
V_ORD.VISIT_SHIFTTIME ,
|
|
5
|
V_ORD.LF_RECID LOCATION_DESCRIPTION_LF ,
|
|
6
|
V_ORD.ADDRESSER_NAME ,
|
|
7
|
V_ORD.ADDRESSER_ID_TYPE ,
|
|
8
|
V_ORD.ADDRESSER_NAT ,
|
|
9
|
V_ORD.ADDRESSER_ROLE ,
|
|
10
|
(SELECT DTL.PAR_AR
|
|
11
|
FROM PAM_DB.SYS_GENERAL_LOOKUPS_DTL DTL
|
|
12
|
WHERE DTL.M_ID = 86
|
|
13
|
AND DTL.ID = V_ORD.ADDRESSER_ROLE
|
|
14
|
)ADDRESSER_ROLE_DESC ,
|
|
15
|
V_ORD.ADDRESSER_ID ,
|
|
16
|
MOSAL_USER.SAL_EMPLOYEE_DATA.GET_COUNTRY_DESC (
|
|
17
|
(SELECT SAL.COUNTRY_CODE
|
|
18
|
FROM MOSAL_USER.SAL_EMPLOYEE SAL
|
|
19
|
WHERE SAL.CIVIL_ID = V_ORD.ADDRESSER_ID
|
|
20
|
AND ROWNUM =1
|
|
21
|
)) ADDRESSER_NAT_DESC ,
|
|
22
|
V_ORD.INSPECTOR_SIGNATURE ,
|
|
23
|
V_ORD.VISIT_STATUS ,
|
|
24
|
V_ORD.LOCATION_STATUS ,
|
|
25
|
V_ORD.NOTES ,
|
|
26
|
V_ORD.ADDRESSER_NOTE ,
|
|
27
|
V_ORD_DTL.ORDER_ID ,
|
|
28
|
(SELECT NAME_AR
|
|
29
|
FROM SEC_SYSTEM_USERS S
|
|
30
|
WHERE S.USER_ID = V_ORD.INSPECTOR_ID
|
|
31
|
) AS "INSPECTOR_NAME" ,
|
|
32
|
NVL(
|
|
33
|
(SELECT INS_VIOLATION_AVOIDANCE.AVOIDANCE_PRINTING_DATE--VIO_DATE
|
|
34
|
FROM INS_VIOLATION_AVOIDANCE
|
|
35
|
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
|
|
36
|
AND ROWNUM = 1
|
|
37
|
) , V_ORD.RESPONSE_DATE )AS "V_DATE" ,
|
|
38
|
NVL(
|
|
39
|
(SELECT TO_CHAR (INS_VIOLATION_AVOIDANCE.AVOIDANCE_PRINTING_DATE--VIO_DATE
|
|
40
|
, 'HH:MI')
|
|
41
|
FROM INS_VIOLATION_AVOIDANCE
|
|
42
|
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
|
|
43
|
AND ROWNUM = 1
|
|
44
|
) ,TO_CHAR ( V_ORD.RESPONSE_DATE , 'HH:MI') )AS "NOW_TIME" ,
|
|
45
|
------ new time _type--------------------------------------------------
|
|
46
|
(
|
|
47
|
CASE
|
|
48
|
(SELECT TO_CHAR ( NVL(
|
|
49
|
(SELECT AVD.AVOIDANCE_PRINTING_DATE
|
|
50
|
FROM INS_VIOLATION_AVOIDANCE AVD
|
|
51
|
WHERE AVD.VISIT_ID = :VisitId
|
|
52
|
AND ROWNUM =1
|
|
53
|
) ,(V_ORD.RESPONSE_DATE)), 'AM' )
|
|
54
|
FROM DUAL
|
|
55
|
)
|
|
56
|
WHEN 'PM'
|
|
57
|
THEN 'مساءً'
|
|
58
|
WHEN 'م'
|
|
59
|
THEN 'مساءً'
|
|
60
|
WHEN 'AM'
|
|
61
|
THEN 'صباحا'
|
|
62
|
WHEN 'ص'
|
|
63
|
THEN 'صباحا'
|
|
64
|
END ) AS "TIME_TYPE" ,
|
|
65
|
----------------------------------------------------------------------------------------------------
|
|
66
|
------new date
|
|
67
|
PAM_DB.SYS_PKG.GET_WEEK_DAY_AR (NVL(
|
|
68
|
(SELECT AVD.AVOIDANCE_PRINTING_DATE
|
|
69
|
FROM INS_VIOLATION_AVOIDANCE AVD
|
|
70
|
WHERE AVD.VISIT_ID = :VisitId
|
|
71
|
AND ROWNUM =1
|
|
72
|
) , V_ORD.RESPONSE_DATE) ) DATE_WEEK
|
|
73
|
-------------------------
|
|
74
|
,
|
|
75
|
(SELECT WV_BLDG_NAME
|
|
76
|
FROM NDB.ADDRESS_AUTO_NO
|
|
77
|
WHERE WV_UNIT_COMPUTER_NO IN
|
|
78
|
(SELECT ADDRESS_AUTO_NO
|
|
79
|
FROM PMP_LICN_REQ REQ ,
|
|
80
|
NMP_LICNFILE LF
|
|
81
|
WHERE REQ.LICN_REQ_RECID = LF.LIC_RECID
|
|
82
|
AND LF.LF_RECID = V_ORD.LF_RECID
|
|
83
|
)
|
|
84
|
) AS "المالك" ,
|
|
85
|
(SELECT INSPECTOR_ROLE
|
|
86
|
FROM INS_INSPECTOR_PROFILE PROF
|
|
87
|
WHERE PROF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
|
|
88
|
) AS "INSPECTOR_ROLE" ,
|
|
89
|
(SELECT ' بموقع العمل الكائن في '
|
|
90
|
|| SYS_PKG.GET_LOOKUP_NAME (2,GOVERNORATE_ID)
|
|
91
|
|| ', رقم الي= '
|
|
92
|
|| AUTO_NUM_PACI
|
|
93
|
|| ' , '
|
|
94
|
|| LOCATION_DESC
|
|
95
|
||' , '
|
|
96
|
|| NOTES
|
|
97
|
FROM INS_LOCATION_WORKPLACE
|
|
98
|
WHERE LOCATION_ID =S_DTL.LOCATION_ID
|
|
99
|
) WORKPLACE_adderss ,
|
|
100
|
(SELECT 'احداثيات '
|
|
101
|
|| ' خط العرض X = '
|
|
102
|
|| LOCATION_X
|
|
103
|
|| ' , خط الطول Y = '
|
|
104
|
|| LOCATION_Y
|
|
105
|
FROM INS_LOCATION_WORKPLACE
|
|
106
|
WHERE LOCATION_ID =S_DTL.LOCATION_ID
|
|
107
|
AND LOCATION_X IS NOT NULL
|
|
108
|
) WORKPLACE_xy ,
|
|
109
|
(SELECT NVL(TOTAL_MALE_WORKERS ,0)
|
|
110
|
FROM INS_LOCATION_HOUSING
|
|
111
|
WHERE LOC_HOUSE_ID =S_DTL.LOCATION_ID
|
|
112
|
) TOT_MALE_WORKERS ,
|
|
113
|
(SELECT NVL( TOTAL_FEMALE_WORKERS ,0)
|
|
114
|
FROM INS_LOCATION_HOUSING
|
|
115
|
WHERE LOC_HOUSE_ID = S_DTL.LOCATION_ID
|
|
116
|
) TOT_FEMALE_WORKERS ,
|
|
117
|
(SELECT ORD_DTL.RESULT_ID
|
|
118
|
FROM INS_VISIT_ORDER_DTL ORD_DTL
|
|
119
|
WHERE ORD_DTL.ORDER_ID = V_ORD_DTL.ORDER_ID
|
|
120
|
AND ROWNUM = 1
|
|
121
|
) AS "RES_ID" ,
|
|
122
|
(SELECT F.ONR_NAME
|
|
123
|
FROM MOSAL_USER.PMP_LICN_REQ P,
|
|
124
|
MOSAL_USER.NMP_LICNFILE N,
|
|
125
|
MOSAL_USER.NMP_FILE F
|
|
126
|
WHERE P.LICN_REQ_RECID = N.LIC_RECID
|
|
127
|
AND F.FILE_RECID = N.FLE_RECID
|
|
128
|
AND N.LF_RECID = V_ORD.LF_RECID
|
|
129
|
) AS "صاحب العمل" ,
|
|
130
|
(SELECT P.COMPANY_NAME
|
|
131
|
FROM MOSAL_USER.PMP_LICN_REQ P,
|
|
132
|
MOSAL_USER.NMP_LICNFILE N,
|
|
133
|
MOSAL_USER.NMP_FILE F
|
|
134
|
WHERE P.LICN_REQ_RECID = N.LIC_RECID
|
|
135
|
AND F.FILE_RECID = N.FLE_RECID
|
|
136
|
AND N.LF_RECID = V_ORD.LF_RECID
|
|
137
|
) AS "الاسم التجاري",
|
|
138
|
(SELECT P.LICN_CIVIL_ID
|
|
139
|
FROM MOSAL_USER.PMP_LICN_REQ P,
|
|
140
|
MOSAL_USER.NMP_LICNFILE N,
|
|
141
|
MOSAL_USER.NMP_FILE F
|
|
142
|
WHERE P.LICN_REQ_RECID = N.LIC_RECID
|
|
143
|
AND F.FILE_RECID = N.FLE_RECID
|
|
144
|
AND N.LF_RECID = V_ORD.LF_RECID
|
|
145
|
) AS "رقم الترخيص" ,
|
|
146
|
(SELECT F.ONR_ID
|
|
147
|
FROM MOSAL_USER.PMP_LICN_REQ P,
|
|
148
|
MOSAL_USER.NMP_LICNFILE N,
|
|
149
|
MOSAL_USER.NMP_FILE F
|
|
150
|
WHERE P.LICN_REQ_RECID = N.LIC_RECID
|
|
151
|
AND F.FILE_RECID = N.FLE_RECID
|
|
152
|
AND N.LF_RECID = V_ORD.LF_RECID
|
|
153
|
) AS "رقم الملف" ,
|
|
154
|
S_DTL.INSPECTION_TYPE_ID INSPECTION_TYPE_ID ,
|
|
155
|
(SELECT TYP.INSPECTOR_TYPE_NAME
|
|
156
|
FROM PAM_DB.INS_INSPECTION_TYPE TYP
|
|
157
|
WHERE TYP.INSPECTION_TYPE_ID = S_DTL.INSPECTION_TYPE_ID
|
|
158
|
) INSPECTION_TYPE_DESC ,
|
|
159
|
S_DTL.INS_SCOPE_ID INSPECTION_SCOP_ID ,
|
|
160
|
(SELECT INS_INSPECTION_SCOPE.INS_SCOPE_NAME
|
|
161
|
FROM INS_INSPECTION_SCOPE
|
|
162
|
WHERE S_DTL.INS_SCOPE_ID = INS_INSPECTION_SCOPE.INS_SCOPE_ID
|
|
163
|
) INSPECTION_SCOP_DESC ,
|
|
164
|
(SELECT DISTINCT LRDB.ECONOMIC_ACTS_DESC
|
|
165
|
FROM LRD_BAL_V LRDB
|
|
166
|
WHERE LRDB.LF_RECID = V_ORD.LF_RECID
|
|
167
|
AND LRDB.ECONOMIC_ACTS_RECID = S_DTL.ECONOMIC_ACT_RECID
|
|
168
|
) ECO_DESCRIPTION ,
|
|
169
|
DECODE (
|
|
170
|
(SELECT COUNT(CLS.CLAUSE_CODE) FROM PAM_DB.INS_VIOLATION_AVOIDANCE_DTL DTL ,
|
|
171
|
PAM_DB.INS_LAW_CLAUSE CLS WHERE DTL.CLAUSE_CODE = CLS.CLAUSE_CODE
|
|
172
|
AND DTL.CLAUSE_STATUS = 1
|
|
173
|
AND DTL.VISIT_ID = V_ORD.VISIT_ID
|
|
174
|
),0,0,1 )COUNT_VIO ,
|
|
175
|
(SELECT INS_VIOLATION_AVOIDANCE.VIO_ID
|
|
176
|
FROM INS_VIOLATION_AVOIDANCE
|
|
177
|
WHERE V_ORD.VISIT_ID = INS_VIOLATION_AVOIDANCE.VISIT_ID
|
|
178
|
AND ROWNUM = 1
|
|
179
|
) VIO_ID,
|
|
180
|
V_ORD.LF_RECID ,
|
|
181
|
(SELECT LICN.LICN_CON_NO
|
|
182
|
FROM MOSAL_USER.NMP_LICNFILE_V LICN
|
|
183
|
WHERE LICN.LF_RECID = V_ORD.LF_RECID
|
|
184
|
) LICN_ID ,
|
|
185
|
(SELECT MV.COMM
|
|
186
|
FROM MOSAL_USER.NMP_LICNFILE_MV MV,
|
|
187
|
MOSAL_USER.NMP_FILE F
|
|
188
|
WHERE F.FILE_RECID = MV.FLE_RECID
|
|
189
|
AND MV.LF_RECID = V_ORD.LF_RECID
|
|
190
|
) AS "رقم السجل" ,
|
|
191
|
S_DTL.WORKER_ID ,
|
|
192
|
(SELECT EMP.EMP_NAME
|
|
193
|
FROM MOSAL_USER.SNL_EMPLOYEE_REQUESTS EMP
|
|
194
|
WHERE EMP.CIVIL_ID = S_DTL.WORKER_ID
|
|
195
|
AND ROWNUM = 1
|
|
196
|
) WORKER_NAME ,
|
|
197
|
MOSAL_USER.SAL_EMPLOYEE_DATA.GET_COUNTRY_DESC (
|
|
198
|
(SELECT SAL.COUNTRY_CODE
|
|
199
|
FROM MOSAL_USER.SAL_EMPLOYEE SAL
|
|
200
|
WHERE SAL.CIVIL_ID = S_DTL.WORKER_ID
|
|
201
|
AND ROWNUM =1
|
|
202
|
)) WORKER_NAT ,
|
|
203
|
V_ORD_DTL.RESULT_ID RESULT_ID ,
|
|
204
|
(SELECT S.CIVIL_ID
|
|
205
|
FROM SEC_SYSTEM_USERS S
|
|
206
|
WHERE S.USER_ID = V_ORD.INSPECTOR_ID
|
|
207
|
) AS "CIVIL_ID" ,
|
|
208
|
(SELECT NVL(sal_employee_data.get_emp_name (civil_id) ,l.COMPANY_NAME) ownr_nam
|
|
209
|
FROM pmp_licn_req l ,
|
|
210
|
NMP_LICNFILE LF
|
|
211
|
WHERE L.LICN_REQ_RECID = LF.LIC_RECID
|
|
212
|
AND L.LICN_STAGE >= 12
|
|
213
|
AND lf.LF_RECID = V_ORD.LF_RECID
|
|
214
|
) manager ,
|
|
215
|
(SELECT (civil_id) ownr_Id
|
|
216
|
FROM pmp_licn_req l ,
|
|
217
|
NMP_LICNFILE LF
|
|
218
|
WHERE L.LICN_REQ_RECID = LF.LIC_RECID
|
|
219
|
AND L.LICN_STAGE >= 12
|
|
220
|
AND lf.LF_RECID = V_ORD.LF_RECID
|
|
221
|
) manager_id ,
|
|
222
|
LPAD(
|
|
223
|
(SELECT SUM (ld.ext_num_approve) + SUM (ld.int_num_approve)
|
|
224
|
FROM nmp_labreqdetails ld,
|
|
225
|
nmp_licnfile lf,
|
|
226
|
nmp_labestimationreq lb
|
|
227
|
WHERE ld.ler_recid = lb.ler_recid
|
|
228
|
AND lb.lf_recid = lf.lf_recid
|
|
229
|
AND lf.LICN_STATUS NOT IN ('C')
|
|
230
|
AND lf.fle_recid =
|
|
231
|
(SELECT LICN.FLE_RECID
|
|
232
|
FROM MOSAL_USER.NMP_LICNFILE_V LICN
|
|
233
|
WHERE LICN.LF_RECID = V_ORD.LF_RECID
|
|
234
|
)
|
|
235
|
) , 5, 0) labest_emp_count ,
|
|
236
|
(SELECT SYS_PKG.GET_LOOKUP_NAME (60 ,V_ORD.LOCATION_STATUS )
|
|
237
|
FROM PAM_DB.INS_VISIT_ORDER V_ORD
|
|
238
|
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
|
|
239
|
) Arrival_status ,
|
|
240
|
(SELECT V_ORD.LOCATION_STATUS
|
|
241
|
FROM PAM_DB.INS_VISIT_ORDER V_ORD
|
|
242
|
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
|
|
243
|
) Arrival_status_id ,
|
|
244
|
TO_NUMBER(V_ORD_DTL.WORKER_LOCATION_SATISFY ) WORKER_LOCATION_SATISFY ,
|
|
245
|
(SELECT PRF.GOVERNORATE_ID
|
|
246
|
FROM INS_INSPECTOR_PROFILE PRF
|
|
247
|
WHERE PRF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
|
|
248
|
) AS "INS_GOV_ID" ,
|
|
249
|
(SELECT PAR_AR DEPT
|
|
250
|
FROM PAM_DB.SYS_GENERAL_LOOKUPS_DTL
|
|
251
|
WHERE M_ID = 2
|
|
252
|
AND ID IN (1,2,3,4,5,6,7,8,11)
|
|
253
|
AND SYS_GENERAL_LOOKUPS_DTL.ID =
|
|
254
|
(SELECT PRF.GOVERNORATE_ID
|
|
255
|
FROM INS_INSPECTOR_PROFILE PRF
|
|
256
|
WHERE PRF.INSPECTOR_ID = V_ORD.INSPECTOR_ID
|
|
257
|
)
|
|
258
|
) AS "INS_GOV_DESC" ,
|
|
259
|
V_ORD_DTL.notes AS ADDER_NOTE ,
|
|
260
|
LPAD (
|
|
261
|
(SELECT COUNT (S.SAL_EMPLOYEE_RECID)
|
|
262
|
FROM SAL_EMPLOYEE s
|
|
263
|
WHERE lf_recid = V_ORD.LF_RECID
|
|
264
|
AND employee_status = 1
|
|
265
|
AND S.COUNTRY_CODE IN
|
|
266
|
( SELECT COUNTRY_CODE# FROM hr_g_country# WHERE NVL(GROUP_CODE,0)=1
|
|
267
|
)
|
|
268
|
), 5, 0 ) KW_EMP_COUNT ,
|
|
269
|
LPAD (
|
|
270
|
(SELECT COUNT (S.SAL_EMPLOYEE_RECID)
|
|
271
|
FROM SAL_EMPLOYEE s
|
|
272
|
WHERE lf_recid IN
|
|
273
|
(SELECT lf_recid
|
|
274
|
FROM NMP_LICNFILE lf
|
|
275
|
WHERE lf.fle_recid =
|
|
276
|
(SELECT lf.fle_recid FROM NMP_LICNFILE lf WHERE LF.LF_RECID =V_ORD.LF_RECID
|
|
277
|
)
|
|
278
|
)
|
|
279
|
AND employee_status = 1
|
|
280
|
), 5, 0 ) EMP_COUNT_FILE ,
|
|
281
|
LPAD (
|
|
282
|
(SELECT COUNT (1)
|
|
283
|
FROM nmp_workathreq N
|
|
284
|
WHERE REQ_STAGE = 3
|
|
285
|
AND EAZ_STATUS IN (1, 4)
|
|
286
|
AND N.LF_RECID =V_ORD.LF_RECID
|
|
287
|
), 5, 0 ) COUNT_WORKEZN ,
|
|
288
|
LPAD (
|
|
289
|
(SELECT COUNT (S.SAL_EMPLOYEE_RECID)
|
|
290
|
FROM SAL_EMPLOYEE s
|
|
291
|
WHERE lf_recid = V_ORD.LF_RECID
|
|
292
|
AND employee_status = 1
|
|
293
|
), 5, 0 ) EMP_COUNT,
|
|
294
|
(SELECT v_o_att.ATTACHMENT_ID
|
|
295
|
FROM INS_VISIT_ORDER_ATTACHMENT v_o_att
|
|
296
|
WHERE V_O_ATT.VISIT_ID = :VisitId
|
|
297
|
AND ATTACHMENT_TYPE = 1
|
|
298
|
AND rownum =1
|
|
299
|
)AS "ATTACHMENT_ID"
|
|
300
|
FROM PAM_DB.INS_VISIT_ORDER V_ORD ,
|
|
301
|
PAM_DB.INS_VISIT_ORDER_DTL V_ORD_DTL ,
|
|
302
|
PAM_DB.INS_SOURCE_ORDER_DTL S_DTL
|
|
303
|
WHERE V_ORD.VISIT_ID = V_ORD_DTL.VISIT_ID
|
|
304
|
AND V_ORD_DTL.ORDER_ID = :OrderID
|
|
305
|
AND S_DTL.ORDER_ID = V_ORD_DTL.ORDER_ID
|
|
306
|
AND V_ORD.VISIT_ID = :VisitId
|