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
| SELECT to_char(to_date('29/02/2008', 'DD/MM/YYYY'),'YYYYMMDD') "Business day",
'ACOMITY' "REC type",
I.COINT "Legal Entity",
'ACC' "Account description",
HD.NUCPT "Account",
DECODE(C.FSANG,'NET','N','GROSS','G','') "Net/Gross client",
CO.RGCOD "Segregated",
C.RGCOD "Regulatory Code",
C.CRDS "Counterparty",
C.CRDSP "Cost Center",
HC.CODEV "Currency",
'' "deal_id",
'' "int_deal_id",
'' "Product",
'' "Instrument",
'' "Buy/Sell",
'' "Put/Call",
'' "EXC/OTC",
'' "Give-out",
'' "Collateral type",
DECODE(C.HSACT,'O','I','N','E','') "Internal",
ROUND(SUM(HC.MHPND),2) "Amount"
FROM INTERV I, HISNEG HN, HISDEP HD, CFLAID C, COMPTE CO,
(SELECT NUBIX,NUFDP,MHPND,CODEV FROM HISCPN HC1 WHERE NCCOF=7 AND DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')
AND DAHIS=(SELECT MAX(HC2.DAHIS) FROM HISCPN HC2 WHERE HC1.NUFDP=HC2.NUFDP AND HC1.NUBIX=HC2.NUBIX AND HC2.NCCOF=7 AND HC2.DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')) ) HC
WHERE I.CTINT='G' AND HD.DATRA <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')
AND HC.MHPND!=0
AND HD.COINC=C.COINT AND HD.NUCPT=C.NUCPT (+) AND HD.COINI NOT IN (SELECT NUCPT FROM INTERV I2 WHERE I2.CTINT='I')
AND CO.COINT=HD.COINC AND HD.NUCPT=CO.NUCPT AND HN.NUBIX=HD.NUBIX AND HD.NUFDP=HC.NUFDP AND HD.NUBIX=HC.NUBIX
GROUP BY
HD.NUCPT ,
I.COINT ,
DECODE(C.FSANG,'NET','N','GROSS','G','') ,
CO.RGCOD,
C.RGCOD,
C.CRDS ,
C.CRDSP ,
HC.CODEV ,
DECODE(C.HSACT,'O','I','N','E','') |
Partager