|
1
|
|
|
2
|
|
|
3
|
select ECONOMIC_ACTS_RECID , ECONOMIC_ACT_DESC , CLAUSE_CODE , CLAUSE_ARTICLE_ID , CLAUSE_Desc , DEPT_NAME , CNT from
|
|
4
|
(select TBL.ECONOMIC_ACTS_RECID , TBL.ECONOMIC_ACT_DESC , TBL.CLAUSE_CODE , TBL.CLAUSE_ARTICLE_ID , TBL.CLAUSE_Desc , TBL.DEPT_NAME , TBL.CNT , ROWNUM RN from
|
|
5
|
( SELECT m.ECONOMIC_ACTS_RECID ,
|
|
6
|
(SELECT act.ECONOMIC_ACT_DESC
|
|
7
|
FROM MOSAL_USER.PMP_ECONMIC_ACTS act
|
|
8
|
WHERE act.ECONOMIC_ACTS_RECID = m.ECONOMIC_ACTS_RECID
|
|
9
|
) ECONOMIC_ACT_DESC,
|
|
10
|
d.CLAUSE_CODE,
|
|
11
|
(SELECT cla.LAW_ARTICLE_ID
|
|
12
|
FROM INS_LAW_CLAUSE cla
|
|
13
|
WHERE cla.CLAUSE_CODE = d.CLAUSE_CODE
|
|
14
|
) CLAUSE_ARTICLE_ID ,
|
|
15
|
(SELECT cla.LAW_ARTICLE_DESC
|
|
16
|
FROM INS_LAW_CLAUSE cla
|
|
17
|
WHERE cla.CLAUSE_CODE = d.CLAUSE_CODE
|
|
18
|
) CLAUSE_Desc ,
|
|
19
|
SYS_PKG.GET_LOOKUP_NAME(2,DEPT_CODE) DEPT_name,
|
|
20
|
COUNT(*) CNT
|
|
21
|
FROM INS_VISIT_ORDER R,
|
|
22
|
INS_VIOLATION_AVOIDANCE_DTL D,
|
|
23
|
(SELECT LF_RECID,ECONOMIC_ACTS_RECID,DEPT_CODE FROM NMP_LICNFILE_MV
|
|
24
|
)M
|
|
25
|
WHERE M.LF_RECID = R.LF_RECID
|
|
26
|
AND D.VISIT_ID = R.VISIT_ID
|
|
27
|
AND M.ECONOMIC_ACTS_RECID IS NOT NULL
|
|
28
|
AND DEPT_CODE = :pDeptId
|
|
29
|
GROUP BY ECONOMIC_ACTS_RECID ,
|
|
30
|
CLAUSE_CODE,
|
|
31
|
DEPT_CODE
|
|
32
|
order by COUNT(*)DESC ) TBL)
|