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