1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
SELECT
M.ID_BREACH_MAIN,
P.ID_BREACH_PED,
M.IS_ARCHIVED AS MAIN_IS_ARCHIVED,
P.IS_ARCHIVED AS PED_IS_ARCHIVED,
M.ISIN,
nvl(M.PTF_ID,'') AS PTF_ID,
S.ID_PED_STATUS AS STATUS_ID,
S.NAME AS STATUS_NAME,
TO_CHAR(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),'999G999G999G990') AS NOMINAL,
TO_CHAR((nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)),'999G999G999G990') AS DELTA_NOMINAL_PREC,
CASE
WHEN (nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) < 0 THEN
'~/Images/Down.gif'
WHEN (nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) > 0 THEN
'~/Images/Up.gif'
ELSE
'~/Images/Stable.gif'
END AS IMG_PREV_NOMINAL,
TO_CHAR(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),'999G999G999G990') AS AMOUNT,
TO_CHAR((nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)),'999G999G999G990') AS DELTA_AMOUNT_PREC,
CASE
WHEN (nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) < 0 THEN
'~/Images/Down.gif'
WHEN (nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) > 0 THEN
'~/Images/Up.gif'
ELSE
'~/Images/Stable.gif'
END AS IMG_PREV_AMOUNT,
(
NVL
(
CONCAT
(
TO_CHAR ((PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME)/PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME))*100,'990D00')
,
' %'
)
,''
)
) AS RATIO,
PAK_BHR_V2.GET_POSITION_LIMIT(T.NAME) AS BREACH_RATIO,
P.FK_TYP_BREACH,
T.NAME AS BREACH_TYPE,
PAK_BHR_V2.Get_POSITION_IS_NEW_BREACH(P.ID_BREACH_PED) AS IS_NEW_BREACH,
TO_CHAR(P.START_DATE,'DD/MM/YYYY') AS START_DATE,
nvl(PAK_BHR_V2.Get_POSITION_LAST_DATE(P.ID_BREACH_PED),'<span style="color:#cccccc;font-style:italic;">open</span>') AS END_DATE,
P.RM_MSG,
P.IS_ARCHIVED
FROM
BHR_BREACH_MAIN M
INNER JOIN BHR_BREACH_PED P
ON P.FK_BREACH_MAIN = M.ID_BREACH_MAIN
RIGHT OUTER JOIN BHR_PED_STATUS S
ON P.FK_PED_STATUS = S.ID_PED_STATUS
RIGHT OUTER JOIN BHR_TYP_BREACH T
ON P.FK_TYP_BREACH = T.ID_TYP_BREACH
LEFT OUTER JOIN BHR_CALCULS C
ON M.ISIN = C.ISIN AND M.PTF_ID = C.ID_PTF
ORDER BY
MAIN_IS_ARCHIVED,
PED_IS_ARCHIVED,
M.ISIN,
M.PTF_ID,
P.START_DATE DESC |
