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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| SELECT header.DATE_H AS DATE, header.FACTURE_NO, MASTR.ACNO AS NBR_COMPTES, MASTR.ISIN, cag.BASESECURITYID, fim.OTHRLONGNAME, --ramener le libellé de la valeur mère pour les coupons ou le libellé du coupon pour les obligations
CASE
WHEN fim.INSTRCTGRY='BDT' THEN fim.OTHRLONGNAME
WHEN fim.INSTRTYPE='CPNS' THEN
(SELECT fim2.OTHRLONGNAME
FROM BANCSOPRPRD.FIM_PRMYDTLS fim2
WHERE cag.BASESECURITYID= fim2.INSTRID)
END AS ISIN_OR_BASEISIN_LIB, BANCSOPRPRD.FCT_GET_PENULTIMATE_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_nbr,--même libellé valeur mère et valeur fille Edit(26/11/2010): Au sens maturité
BANCSOPRPRD.FCT_GET_LAST_WORD_USING_TOKEN(fim.OTHRLONGNAME, ' ') AS maturite_lib,--même libellé valeur mère et valeur fille Edit(26/11/2010): Au sens maturité
CASE
WHEN fim.INSTRCTGRY='BDT' THEN 'OBLIGATION(s)'
WHEN fim.INSTRTYPE='CPNS' THEN 'COUPON(s)'
END AS grouptype, cag.DISBSECURITYID AS coupons, header.BP_NAME, header.POAHOLDER_NAME, mastr.amount
FROM BANCSOPRPRD.FBL_MASTRDTLS_FACT MASTR
LEFT OUTER JOIN BANCSOPRPRD.CAG_SECBASIS cag ON ( cag.EVENTID IN
(SELECT EVENTID
FROM TABLE(BANCSOPRPRD.FCT_GET_PROPER_EVENTIDS4DATE(11, 2010)) /*select camaster.EVENTID
from BANCSOPRPRD.CAG_CAMASTER camaster
where camaster.EVENTSTATUS= 'CPTD'
and month(camaster.EXECUTIONDATE)=11
and year(camaster.EXECUTIONDATE)=2010*/ /*
2010002920,
2010002922,
2010002923,
2010002924,
2010002925,
2010002926,
2010002927,
2010002928,
2010002929,
2010002930,
2010002935,
2010002931,
2010002932,
2010002933,
2010002934,
2010002936,
2010002937,
2010002938,
2010002939,
2010002940,
2010002941,
2010002942,
2010002943,
2010002944,
2010002945,
2010002946,
2010002947,
2010002948,
2010002949,
2010002950,
2010002951,
2010002952,
2010002953,
2010002954,
2010002955,
2010002956,
2010002957,
2010002958,
2010002959,
2010002960,
2010002961,
2010002962,
2010002963,
2010002964
*/ )
AND MASTR.ISIN= cag.DISBSECURITYID ) , BANCSOPRPRD.FBL_BILL_FACT bill_fact, BANCSOPRPRD.FBL_BILLHEADER_FACT header, BANCSOPRPRD.FIM_PRMYDTLS fim
WHERE MONTH(DATE) = 11--$P{MOIS}
AND YEAR(DATE) = 2010--$P{ANNEE}
AND MASTR.feetype IN ('P025',
'P023',
'P022',
'P024',
'P030')
AND bill_fact.COM_NO=MASTR.COM_SEQ
AND header.FACTURE_SEQ=bill_fact.FACTURE_SEQ
AND fim.INSTRID= MASTR.ISIN
AND (fim.INSTRCTGRY='BDT'
OR fim.INSTRTYPE='CPNS')
AND fim.ISSUERCD='00000000101'
AND MASTR.BPID_LIABLE='00000000101'
AND MASTR.BPID_RECIPIENT='00000000101'
AND header.FACTURE_NO IS NOT NULL
ORDER BY grouptype DESC, maturite_lib DESC, int(maturite_nbr) ASC, isin ASC, MASTR.amount DESC WITH UR |
Partager