|
1
|
Query For NAT WORKER
|
|
2
|
--------------------------------
|
|
3
|
SELECT distinct msor.SOURCE_ID ,
|
|
4
|
msor.SOURCE_STATUS ,
|
|
5
|
msor.CREATED_DATE,
|
|
6
|
msor.FILE_RECID ,
|
|
7
|
|
|
8
|
(SELECT mv.LICN_CON_NO
|
|
9
|
FROM MOSAL_USER.NMP_LICNFILE_V mv
|
|
10
|
WHERE mv.LF_RECID = dtl.lf_recid
|
|
11
|
) LICN_civil_NO
|
|
12
|
|
|
13
|
FROM INS_SOURCE_ORDER msor ,INS_SOURCE_ORDER_DTL dtl
|
|
14
|
WHERE msor.SOURCE_ID = dtl.SOURCE_ID
|
|
15
|
and msor.SOURCE_TYPE in ( 1,5,6)
|
|
16
|
and dtl.INSPECTION_TYPE_ID IN (4,5)
|
|
17
|
AND msor.FILE_RECID = :b_fileRecid
|
|
18
|
----------------------------------------
|
|
19
|
|
|
20
|
|
|
21
|
Query for workers
|
|
22
|
--------------------------------------
|
|
23
|
SELECT distinct msor.SOURCE_ID ,
|
|
24
|
msor.SOURCE_STATUS ,
|
|
25
|
msor.CREATED_DATE,
|
|
26
|
msor.FILE_RECID ,
|
|
27
|
(SELECT mv.LICN_CON_NO
|
|
28
|
FROM MOSAL_USER.NMP_LICNFILE_V mv ,
|
|
29
|
INS_SOURCE_ORDER_DTL dtl
|
|
30
|
WHERE dtl.lf_recid = mv.LF_RECID
|
|
31
|
AND dtl.SOURCE_ID = msor.SOURCE_ID
|
|
32
|
) LICN_CON_NO ,
|
|
33
|
( SELECT vdtl.visit_id FROM INS_SOURCE_ORDER_DTL dtl , INS_VISIT_ORDER_DTL VDTL
|
|
34
|
WHERE msor.SOURCE_ID = dtl.SOURCE_ID
|
|
35
|
and dtl.ORDER_ID = VDTL.ORDER_ID
|
|
36
|
and dtl.INSPECTION_TYPE_ID IN (3)
|
|
37
|
and VDTL.VISIT_STATUS =2 ) visit_id ,
|
|
38
|
(SELECT SYS_PKG.GET_LOOKUP_NAME(60,vm.LOCATION_STATUS)
|
|
39
|
FROM INS_SOURCE_ORDER_DTL dtl ,
|
|
40
|
INS_VISIT_ORDER_DTL VDTL ,
|
|
41
|
INS_VISIT_ORDER vm
|
|
42
|
WHERE msor.SOURCE_ID = dtl.SOURCE_ID
|
|
43
|
AND VM.VISIT_ID = VDTL.VISIT_ID
|
|
44
|
AND dtl.ORDER_ID = VDTL.ORDER_ID
|
|
45
|
AND dtl.INSPECTION_TYPE_ID IN (3)
|
|
46
|
AND VDTL.VISIT_STATUS =2
|
|
47
|
) LOCATION_STATUS
|
|
48
|
FROM INS_SOURCE_ORDER msor
|
|
49
|
WHERE msor.SOURCE_TYPE = 5
|
|
50
|
AND msor.SOURCE_ID IN
|
|
51
|
( SELECT SOURCE_ID FROM INS_SOURCE_ORDER_DTL WHERE INSPECTION_TYPE_ID IN (3)
|
|
52
|
)
|
|
53
|
--------------------------------------
|