|
1
|
SELECT FS.seq ,
|
|
2
|
FS.FILE_RECID,
|
|
3
|
fs.RELEASE_SEQ ,
|
|
4
|
FS.FILE_SUSPEND_RECID FS_RECID,
|
|
5
|
(SELECT NF.ONR_ID
|
|
6
|
FROM NMP_FILE NF
|
|
7
|
WHERE NF.FILE_RECID = FS.FILE_RECID
|
|
8
|
) FIL#LICN_RECID,
|
|
9
|
NVL(
|
|
10
|
(SELECT SR.SUSPEND_REASON_DESC
|
|
11
|
FROM NMP_SUSPEND_REASON SR
|
|
12
|
WHERE SR.SUSPEND_REASON_RECID = FS.SUSPEND_REASON_RECID
|
|
13
|
),FS.SUSPEND_NOTE )SUSPEND_CAUSE ,
|
|
14
|
FS.SUSPEND_USER SUSPEND_USER,
|
|
15
|
FS.SUSPEND_DATE SUSPEND_DATE,
|
|
16
|
FS.RELEASED RELASE,
|
|
17
|
NVL(
|
|
18
|
(SELECT RR.RELEASE_REASON_DESC
|
|
19
|
FROM NMP_RELEASE_REASON RR
|
|
20
|
WHERE RR.RELEASE_REASON_RECID = FS.RELEASE_REASON_RECID
|
|
21
|
), FS.RELEASE_NOTE) RELEASE_CAUSE,
|
|
22
|
FS.UNSUSPEND_USER RELASE_USER,
|
|
23
|
FS.UNSUSPEND_DATE RELASE_DATE ,
|
|
24
|
(SELECT DECODE( NF.FILE_STATUS, 'R','????? ?? ???? ?????', 'A','????? ????', 'S','????? ????? ??????''P','????? ????? ?????', 'C','????? ????', 'I','????? ??? ????' )
|
|
25
|
FROM NMP_FILE NF
|
|
26
|
WHERE NF.FILE_RECID = FS.FILE_RECID
|
|
27
|
) FINAL_STATUS ,
|
|
28
|
(SELECT F.SUSPEND_DESC
|
|
29
|
FROM nmp_suspend f
|
|
30
|
WHERE F.SUSPEND_RECID = fs.SUSPEND_RECID
|
|
31
|
) suspend_desc,
|
|
32
|
(SELECT F.SUSPEND_code
|
|
33
|
FROM nmp_suspend f
|
|
34
|
WHERE F.SUSPEND_RECID = fs.SUSPEND_RECID
|
|
35
|
) suspend_code
|
|
36
|
FROM NMP_FILE_SUSPEND FS
|
|
37
|
where FILE_RECID= :P_FILE_RECID
|
|
38
|
order by SUSPEND_DATE;
|