zOs/SQL/VDPS08XX
-- EDITED BY A540769 ON 2007/04/20 AT 18:45
-- EDITED BY A918249 ON 2007/04/17 AT 14:55
-- EDITED BY A540769 ON 2007/04/12 AT 14:56
-- EDITED BY A540769 ON 2007/04/12 AT 14:49
-- EDITED BY A540769 ON 2007/04/10 AT 13:21
-- EDITED BY A540769 ON 2007/04/10 AT 13:20
-- EDITED BY A540769 ON 2007/04/05 AT 14:42
-- EDITED BY A914227 ON 2007/04/03 AT 10:57
-- EDITED BY A914227 ON 2007/03/29 AT 13:26
-- EDITED BY A540769 ON 2007/03/27 AT 17:05
-- EDITED BY A540769 ON 2007/03/27 AT 17:04
-- EDITED BY A914227 ON 2007/03/23 AT 15:58
-- EDITED BY A914227 ON 2007/03/22 AT 13:16
-- EDITED BY A914227 ON 2007/03/20 AT 11:43
-- EDITED BY A914227 ON 2007/03/13 AT 12:24
-- EDITED BY A914227 ON 2007/03/13 AT 12:21
--
SET CURRENT SQLID='S100447';
--
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","VDPS2";
--
--
CREATE VIEW VDPS2.XPS_VRGUI000 AS
SELECT T1.GEOUNITID, T1.VALIDFROMDATE, T1.VALIDUNTILDATE,
T1.CREATETIMESTAMP, T1.UPDATETIMESTAMP,
T2.SCHEMESYMBOL SCHEMESYMBOL_ISOA3,
T2.IDSYMBOL IDSYMBOL_ISOA3,
T2.VALIDFROMDATE VALIDFROMDATE_ISOA3,
T2.VALIDUNTILDATE VALIDUNTILDATE_ISOA3,
T2.CREATETIMESTAMP CREATETIMESTAMP_ISOA3,
T2.UPDATETIMESTAMP UPDATETIMESTAMP_ISOA3,
T3.SCHEMESYMBOL SCHEMESYMBOL_ISOA2,
T3.IDSYMBOL IDSYMBOL_ISOA2,
T3.VALIDFROMDATE VALIDFROMDATE_ISOA2,
T3.VALIDUNTILDATE VALIDUNTILDATE_ISOA2,
T3.CREATETIMESTAMP CREATETIMESTAMP_ISOA2,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_ISOA2,
T4.SCHEMESYMBOL SCHEMESYMBOL_ISON,
T4.IDSYMBOL IDSYMBOL_ISON,
T4.VALIDFROMDATE VALIDFROMDATE_ISON,
T4.VALIDUNTILDATE VALIDUNTILDATE_ISON,
T4.CREATETIMESTAMP CREATETIMESTAMP_ISON,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_ISON,
T5.SCHEMESYMBOL SCHEMESYMBOL_CS, T5.IDSYMBOL IDSYMBOL_CS,
T5.VALIDFROMDATE VALIDFROMDATE_CS,
T5.VALIDUNTILDATE VALIDUNTILDATE_CS,
T5.CREATETIMESTAMP CREATETIMESTAMP_CS,
T5.UPDATETIMESTAMP UPDATETIMESTAMP_CS,
T6.SCHEMESYMBOL SCHEMESYMBOL_TKN, T6.IDSYMBOL IDSYMBOL_TKN,
T6.VALIDFROMDATE VALIDFROMDATE_TKN,
T6.VALIDUNTILDATE VALIDUNTILDATE_TKN,
T6.CREATETIMESTAMP CREATETIMESTAMP_TKN,
T6.UPDATETIMESTAMP UPDATETIMESTAMP_TKN,
T7.SCHEMESYMBOL SCHEMESYMBOL_TKR, T7.IDSYMBOL IDSYMBOL_TKR,
T7.VALIDFROMDATE VALIDFROMDATE_TKR,
T7.VALIDUNTILDATE VALIDUNTILDATE_TKR,
T7.CREATETIMESTAMP CREATETIMESTAMP_TKR,
T7.UPDATETIMESTAMP UPDATETIMESTAMP_TKR, T1.SHORTNAME_G,
T1.SHORTNAME_E, T1.SHORTNAME_F, T1.SHORTNAME_I,
T1.SHORTNAME_N, T1.NAME_G, T1.NAME_E, T1.NAME_F, T1.NAME_I,
T1.NAME_N
FROM VDPS2.VTGEOUNIT T1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T2
ON T2.GEOUNITID = T1.GEOUNITID AND T2.SCHEMESYMBOL =
'ISOA3' AND T2.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T3
ON T3.GEOUNITID = T1.GEOUNITID AND T3.SCHEMESYMBOL =
'ISOA2' AND T3.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T4
ON T4.GEOUNITID = T1.GEOUNITID AND T4.SCHEMESYMBOL =
'ISON' AND T4.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T5
ON T5.GEOUNITID = T1.GEOUNITID AND T5.SCHEMESYMBOL =
'CS-WS80-DOM' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T6
ON T6.GEOUNITID = T1.GEOUNITID AND T6.SCHEMESYMBOL = 'TKN'
AND T6.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T7
ON T7.GEOUNITID = T1.GEOUNITID AND T7.SCHEMESYMBOL = 'TKR'
AND T7.ROWSTATUS = 1
WHERE (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VRGUI000 FOR VDPS2.XPS_VRGUI000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VRIND000 AS
SELECT T1.SCHEMESYMBOL, T1.IDSYMBOL, T1.VALIDFROMDATE,
T1.VALIDUNTILDATE, T1.CREATETIMESTAMP, T1.UPDATETIMESTAMP,
T1.SHORTNAME_G, T1.SHORTNAME_E, T1.SHORTNAME_F,
T1.SHORTNAME_I, T1.SHORTNAME_N, T1.NAME_G, T1.NAME_E,
T1.NAME_F, T1.NAME_I, T1.NAME_N, T1.DESCRIPTION_G,
T1.DESCRIPTION_E, T1.DESCRIPTION_F, T1.DESCRIPTION_I,
T1.DESCRIPTION_N
FROM VDPS2.VTINDUSTRY T1
WHERE (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VRIND000 FOR VDPS2.XPS_VRIND000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VRING000 AS
SELECT T1.SCHEMESYMBOL, T1.IDSYMBOL, T1.VALIDFROMDATE,
T1.VALIDUNTILDATE, T1.CREATETIMESTAMP, T1.UPDATETIMESTAMP,
T1.NAME_G, T1.NAME_E, T1.NAME_F, T1.NAME_I, T1.NAME_N,
T1.DESCRIPTION_G, T1.DESCRIPTION_E, T1.DESCRIPTION_F,
T1.DESCRIPTION_I, T1.DESCRIPTION_N
FROM VDPS2.VTINSTRGROUP T1
WHERE (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VRING000 FOR VDPS2.XPS_VRING000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VRINI000 AS
SELECT T2.SCHEMESYMBOL, T2.IDSYMBOL, T2.VALIDFROMDATE,
T2.VALIDUNTILDATE, T2.CREATETIMESTAMP, T2.UPDATETIMESTAMP,
T1.INSTRUMENTID, T1.FOTYPE, T1.CSSTATUSTYPE, T1.STATUSTYPE,
T1.MAINLANGUAGETYPE, T1.CREATETIMESTAMP CREATETIMESTAMP_IN,
T1.UPDATETIMESTAMP UPDATETIMESTAMP_IN,
T3.SCHEMESYMBOL SCHEMESYMBOL_I2, T3.IDSYMBOL IDSYMBOL_I2,
T3.VALIDFROMDATE VALIDFROMDATE_I2,
T3.VALIDUNTILDATE VALIDUNTILDATE_I2,
T3.CREATETIMESTAMP CREATETIMESTAMP_I2,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_I2,
T4.SCHEMESYMBOL SCHEMESYMBOL_I1, T4.IDSYMBOL IDSYMBOL_I1,
T4.VALIDFROMDATE VALIDFROMDATE_I1,
T4.VALIDUNTILDATE VALIDUNTILDATE_I1,
T4.CREATETIMESTAMP CREATETIMESTAMP_I1,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_I1,
T5.SCHEMESYMBOL SCHEMESYMBOL_CS, T5.IDSYMBOL IDSYMBOL_CS,
T5.VALIDFROMDATE VALIDFROMDATE_CS,
T5.VALIDUNTILDATE VALIDUNTILDATE_CS,
T5.CREATETIMESTAMP CREATETIMESTAMP_CS,
T5.UPDATETIMESTAMP UPDATETIMESTAMP_CS,
T6.SCHEMESYMBOL SCHEMESYMBOL_T2, T6.IDSYMBOL IDSYMBOL_T2,
T6.VALIDFROMDATE VALIDFROMDATE_T2,
T6.VALIDUNTILDATE VALIDUNTILDATE_T2,
T6.CREATETIMESTAMP CREATETIMESTAMP_T2,
T6.UPDATETIMESTAMP UPDATETIMESTAMP_T2,
T7.SCHEMESYMBOL SCHEMESYMBOL_T1, T7.IDSYMBOL IDSYMBOL_T1,
T7.VALIDFROMDATE VALIDFROMDATE_T1,
T7.VALIDUNTILDATE VALIDUNTILDATE_T1,
T7.CREATETIMESTAMP CREATETIMESTAMP_T1,
T7.UPDATETIMESTAMP UPDATETIMESTAMP_T1,
T8.SCHEMESYMBOL SCHEMESYMBOL_1T, T8.IDSYMBOL IDSYMBOL_1T,
T8.VALIDFROMDATE VALIDFROMDATE_1T,
T8.VALIDUNTILDATE VALIDUNTILDATE_1T,
T8.CREATETIMESTAMP CREATETIMESTAMP_1T,
T8.UPDATETIMESTAMP UPDATETIMESTAMP_1T, T1.SHORTNAME_G,
T1.SHORTNAME_E, T1.SHORTNAME_F, T1.SHORTNAME_I,
T1.SHORTNAME_N, T1.NAME_G, T1.NAME_E, T1.NAME_F, T1.NAME_I,
T1.NAME_N, T1.NAME45_G, T1.NAME45_E, T1.NAME45_F,
T1.NAME45_I, T1.NAME45_N
FROM VDPS2.VTINSTRUMENT T1
INNER JOIN
VDPS2.VTINSTRIDENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND T2.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID AND T3.SCHEMESYMBOL =
'I2' AND T3.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T4
ON T4.INSTRUMENTID = T1.INSTRUMENTID AND T4.SCHEMESYMBOL =
'I1' AND T4.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T5
ON T5.INSTRUMENTID = T1.INSTRUMENTID AND T5.SCHEMESYMBOL =
'CS-WS80-WHRG' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T6
ON T6.INSTRUMENTID = T1.INSTRUMENTID AND T6.SCHEMESYMBOL =
'T2' AND T6.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T7
ON T7.INSTRUMENTID = T1.INSTRUMENTID AND T7.SCHEMESYMBOL =
'T1' AND T7.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T8
ON T8.INSTRUMENTID = T1.INSTRUMENTID AND T8.SCHEMESYMBOL =
'1T' AND T8.ROWSTATUS = 1
WHERE T1.FOTYPE = 10
AND (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VRINI000 FOR VDPS2.XPS_VRINI000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VRISI000 AS
SELECT T1.INSTITUTIONID, T1.FOTYPE, T1.ISGLOBALREGACC,
T1.KANTONSCODETYPE, T1.MAINLANGUAGETYPE, T1.STATUSTYPE,
T1.DELETIONDATE, T1.DELETEREASONTYPE, T1.CREATETIMESTAMP,
T1.UPDATETIMESTAMP, T2.SCHEMESYMBOL SCHEMESYMBOL_BCN,
T2.IDSYMBOL IDSYMBOL_BCN,
T2.VALIDFROMDATE VALIDFROMDATE_BCN,
T2.VALIDUNTILDATE VALIDUNTILDATE_BCN,
T2.CREATETIMESTAMP CREATETIMESTAMP_BCN,
T2.UPDATETIMESTAMP UPDATETIMESTAMP_BCN,
T3.SCHEMESYMBOL SCHEMESYMBOL_BCA, T3.IDSYMBOL IDSYMBOL_BCA,
T3.VALIDFROMDATE VALIDFROMDATE_BCA,
T3.VALIDUNTILDATE VALIDUNTILDATE_BCA,
T3.CREATETIMESTAMP CREATETIMESTAMP_BCA,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_BCA,
T4.SCHEMESYMBOL SCHEMESYMBOL_CS, T4.IDSYMBOL IDSYMBOL_CS,
T4.VALIDFROMDATE VALIDFROMDATE_CS,
T4.VALIDUNTILDATE VALIDUNTILDATE_CS,
T4.CREATETIMESTAMP CREATETIMESTAMP_CS,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_CS,
T5.SCHEMESYMBOL SCHEMESYMBOL_MIC, T5.IDSYMBOL IDSYMBOL_MIC,
T5.VALIDFROMDATE VALIDFROMDATE_MIC,
T5.VALIDUNTILDATE VALIDUNTILDATE_MIC,
T5.CREATETIMESTAMP CREATETIMESTAMP_MIC,
T5.UPDATETIMESTAMP UPDATETIMESTAMP_MIC, T1.SHORTNAME_G,
T1.SHORTNAME_E, T1.SHORTNAME_F, T1.SHORTNAME_I,
T1.SHORTNAME_N, T1.NAME_G, T1.NAME_E, T1.NAME_F, T1.NAME_I,
T1.NAME_N
FROM VDPS2.VTINSTITUTION T1
LEFT OUTER JOIN
VDPS2.VTINSTIIDENT T2
ON T2.INSTITUTIONID = T1.INSTITUTIONID AND T2.SCHEMESYMBOL
= 'BCN' AND T2.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTIIDENT T3
ON T3.INSTITUTIONID = T1.INSTITUTIONID AND T3.SCHEMESYMBOL
= 'BCA' AND T3.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTIIDENT T4
ON T4.INSTITUTIONID = T1.INSTITUTIONID AND T4.SCHEMESYMBOL
= 'CS-WS80-BPL' AND T4.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTIIDENT T5
ON T5.INSTITUTIONID = T1.INSTITUTIONID AND T5.SCHEMESYMBOL
= 'MIC' AND T5.ROWSTATUS = 1
WHERE T1.FOTYPE = 104
AND (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VRISI000 FOR VDPS2.XPS_VRISI000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VRRAS000 AS
SELECT T1.SCHEMESYMBOL, T1.IDSYMBOL, T1.RATINGSCALEVALUE,
T1.VALIDFROMDATE, T1.VALIDUNTILDATE, T1.CREATETIMESTAMP,
T1.UPDATETIMESTAMP, T1.DESCRIPTION_G, T1.DESCRIPTION_E,
T1.DESCRIPTION_F, T1.DESCRIPTION_I, T1.DESCRIPTION_N
FROM VDPS2.VTRATINGSYMBOL T1
WHERE (T1.ROWSTATUS IS NULL
OR T1.ROWSTATUS <> 2);
--
COMMIT;
CREATE ALIAS VDPS.XPS_VRRAS000 FOR VDPS2.XPS_VRRAS000;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VINGR004_V02 AS
SELECT A.IDSYMBOL, B.IDSYMBOL IDSYMBOL_CSGTA,
C.IDSYMBOL IDSYMBOL_ISIN, D.IDSYMBOL IDSYMBOL_TKT
FROM VDPS2.VTINSTRIDENT A
INNER JOIN
VDPS2.VTINSTRUMENT INS
ON INS.INSTRUMENTID = A.INSTRUMENTID AND (INS.ROWSTATUS IS
NULL OR INS.ROWSTATUS <> 2)
LEFT OUTER JOIN
VDPS2.VTINSTRINGROUP B
ON A.INSTRUMENTID = B.INSTRUMENTID AND B.SCHEMESYMBOL =
'CSGTA'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT C
ON A.INSTRUMENTID = C.INSTRUMENTID AND C.SCHEMESYMBOL =
'I-' AND C.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRINGROUP D
ON A.INSTRUMENTID = D.INSTRUMENTID AND D.SCHEMESYMBOL =
'TKT'
WHERE A.SCHEMESYMBOL = 'CH'
AND A.ROWSTATUS = 1;
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VINGR004_V02 FOR VDPS2.XPS_VINGR004_V02;
COMMIT;
CREATE VIEW VDPS2.XPS_VCFAM003 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.EFFECTIVEDATE,
T3.EVENTFUNCTIONTYPE, T4.PLANNEDBEGINDATE,
T4.PLANNEDENDDATE, T4.PLANNEDPAYRATE,
T5.EXERCPERIODUNITAMT, T5.EXERCPERIODUNITTP, T6.OPTIONTYPE,
T7.EXERCISEAMOUNT, T7.EXERCISEINSTRUMENT, T7.HELDAMOUNT,
T7.PAYAMOUNT, T7.PAYDIRECTIONTYPE, T7.PAYRATE
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTRUMENTEVENT T3
ON T3.INSTRUMENTID = T2.INSTRUMENTID
INNER JOIN
VDPS2.VTCFEVENT T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.
INSTRUMENTEVENTNO = T3.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTCASHFLSUBPERIOD T5
ON T5.INSTRUMENTID = T4.INSTRUMENTID AND T5.
INSTRUMENTEVENTNO = T4.INSTRUMENTEVENTNO
LEFT OUTER JOIN
VDPS2.VTCFEXERCISE T6
ON T6.INSTRUMENTID = T4.INSTRUMENTID AND T6.
INSTRUMENTEVENTNO = T4.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTCASHFLOWAMOUNT T7
ON T7.INSTRUMENTID = T4.INSTRUMENTID AND T7.
INSTRUMENTEVENTNO = T4.INSTRUMENTEVENTNO
WHERE T1.ROWSTATUS = 1;
COMMIT;
CREATE VIEW VDPS2.XPS_VINDE005 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.CSRISKINDSCHEMESYM,
T3.CSRISKINDIDSYMBOL, T3.CSRISKINSGRPSCHSYM,
T3.CSRISKINSTGRPIDSYM, T4.IDSYMBOL CSRISKCOUNTRYSYM,
T5.IDSYMBOL ISORISKCOUNTRYSYM, T6.IDSYMBOL CSRISKCURRSYM,
T7.IDSYMBOL ISORISKCURRSYM, T8.IDSYMBOL CSRISKINSTRSYM
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.CTINSTRDETAILS T3
ON T3.INSTRUMENTID = T2.INSTRUMENTID
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T4
ON T4.GEOUNITID = T3.CSRISKCOUNTRY AND T4.SCHEMESYMBOL =
'CS-WS80-DOM'
LEFT OUTER JOIN
VDPS2.VTGEOUNITIDENT T5
ON T5.GEOUNITID = T3.CSRISKCOUNTRY AND T5.SCHEMESYMBOL =
'ISOA2'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T6
ON T6.INSTRUMENTID = T3.CSRISKCURRENCY AND T6.SCHEMESYMBOL
= 'CS-WS80-WHRG'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T7
ON T7.INSTRUMENTID = T3.CSRISKCURRENCY AND T7.SCHEMESYMBOL
= 'I2'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T8
ON T8.INSTRUMENTID = T3.CSRISKINSTRUMENTID AND T8.
SCHEMESYMBOL = 'CH'
WHERE T1.ROWSTATUS = 1;
COMMIT;
--
--
--
CREATE ALIAS VDPS.XPS_VCFAM003 FOR VDPS2.XPS_VCFAM003;
COMMIT;
CREATE ALIAS VDPS.XPS_VINDE005 FOR VDPS2.XPS_VINDE005;
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VPOCG002 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.PORTFOLIOFUNCTP,
T5.PORTFOLIONO, T5.PORTFOLIOGROUPNO, T5.PORTFOLIOCRITSETNO,
T5.PORTFOLIOCRITNO, T3.GROUPNUMBER, T3.PORTFOLIOSTRATDATE,
T3.VALIDFROMDATE, T3.VALIDUNTILDATE,
T3.CREATETIMESTAMP CREATETIMESTAMP_PG,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_PG,
T4.CREATETIMESTAMP CREATETIMESTAMP_PS,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_PS,
T5.CREATETIMESTAMP CREATETIMESTAMP_PC,
T5.UPDATETIMESTAMP UPDATETIMESTAMP_PC, T4.RUNNINGNUMBER,
T4.INVESTMENTAMOUNT, T4.INVESTMENTQUOTETP,
T4.INVESTMENTCURRENCY, T7.IDSYMBOL INVESTCURR_I2,
T4.MININVESTMENTAMT, T4.MININVESTQUOTETP,
T4.MININVESTMENTCURR, T8.IDSYMBOL MININVESTCURR_I2,
T4.MAXINVESTMENTAMT, T4.MAXINVESTQUOTETP,
T4.MAXINVESTMENTCURR, T9.IDSYMBOL MAXINVESTCURR_I2,
T5.PORTFOLIOCRITTYPE, T5.ISEXCLUDED, T5.GEOGRAPHICUNIT,
T6.SHORTNAME_G, T6.SHORTNAME_E, T6.SHORTNAME_F,
T6.SHORTNAME_I, T6.SHORTNAME_N, T5.DESCRIPTION
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTPORTFOLIOGROUP T3
ON T3.PORTFOLIONO = T2.PORTFOLIONO
INNER JOIN
VDPS2.VTPORTFOLCRITSET T4
ON T4.PORTFOLIONO = T3.PORTFOLIONO AND T4.PORTFOLIOGROUPNO
= T3.PORTFOLIOGROUPNO
INNER JOIN
VDPS2.VTPORTFOLCRITERIA T5
ON T5.PORTFOLIONO = T4.PORTFOLIONO AND T5.PORTFOLIOGROUPNO
= T4.PORTFOLIOGROUPNO AND T5.PORTFOLIOCRITSETNO = T4.
PORTFOLIOCRITSETNO AND T5.PORTFOLIOCRITTYPE = 6
LEFT OUTER JOIN
VDPS2.VTGEOUNIT T6
ON T6.GEOUNITID = T5.GEOGRAPHICUNIT AND (T6.ROWSTATUS IS
NULL OR T6.ROWSTATUS <> 2)
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T7
ON T7.INSTRUMENTID = T4.INVESTMENTCURRENCY AND T7.
SCHEMESYMBOL = 'I2' AND T7.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T8
ON T8.INSTRUMENTID = T4.MININVESTMENTCURR AND T8.
SCHEMESYMBOL = 'I2' AND T8.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T9
ON T9.INSTRUMENTID = T4.MAXINVESTMENTCURR AND T9.
SCHEMESYMBOL = 'I2' AND T9.ROWSTATUS = 1
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VOPPA003 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T2.FOTYPE,
T5.IDSYMBOL EXERCISECURR_I2, CFA.EXERCISEINSTRUMENT,
CFA.EXERCISEAMOUNT, CAST(NULL AS FLOAT) EXERCISEUNITAMOUNT,
CAST(CASE WHEN CFA.EXERCISEAMOUNT > 0 THEN 2 ELSE NULL END
AS SMALLINT) EXERCISEUNITTYPE, CFA.HELDAMOUNT,
CFA.HELDUNITTYPE, T6.IDSYMBOL SETTLECURR_I2,
CAST(CASE WHEN CFA.UNDERFIXINGNO IS NULL THEN CFA.
PAYINSTRUMENT ELSE NULL END AS INTEGER) SETTLEINSTRUMENT,
CFE.SETTLETYPE, CFA.PAYUNITTYPE SETTLEUNITTYPE,
CFE.OPTIONTYPE, CFE.CREATETIMESTAMP, CFE.UPDATETIMESTAMP,
CAST(CASE WHEN CFA.UNDERFIXINGNO IS NOT NULL THEN 1 ELSE 0
END AS SMALLINT) HASUNDERFIXING, UND.TYPEX,
UND.UNDERSETTLETYPE, UFXC.UNDERINSTRID,
T7.IDSYMBOL UNDERINSTRID_CH, T8.IDSYMBOL UNDERINSTRID_ISIN,
UFXC.CREATETIMESTAMP CREATETIMESTAMP_OU,
UFXC.UPDATETIMESTAMP UPDATETIMESTAMP_OU,
UND.UNDERLYINGNAME_G, UND.UNDERLYINGNAME_E,
UND.UNDERLYINGNAME_F, UND.UNDERLYINGNAME_I,
UND.UNDERLYINGNAME_N
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTCFEVENT CFEV
ON CFEV.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.VTCASHFLOWAMOUNT CFA
ON CFA.INSTRUMENTID = CFEV.INSTRUMENTID AND CFA.
INSTRUMENTEVENTNO = CFEV.INSTRUMENTEVENTNO
LEFT OUTER JOIN
VDPS2.VTCFEXERCISE CFE
ON CFE.INSTRUMENTID = CFA.INSTRUMENTID AND CFE.
INSTRUMENTEVENTNO = CFA.INSTRUMENTEVENTNO
LEFT OUTER JOIN
VDPS2.VTUNDERFIXING UND
ON UND.INSTRUMENTID = CFA.INSTRUMENTID AND UND.
UNDERFIXINGNO = CFA.UNDERFIXINGNO
LEFT OUTER JOIN
VDPS2.VTUNDERFXCOMPONENT UFXC
ON UFXC.INSTRUMENTID = UND.INSTRUMENTID AND UFXC.
UNDERFIXINGNO = UND.UNDERFIXINGNO
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T5
ON T5.INSTRUMENTID = CFA.EXERCISEINSTRUMENT AND T5.
SCHEMESYMBOL = 'I2' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T6
ON T6.INSTRUMENTID = CFA.PAYINSTRUMENT AND T6.SCHEMESYMBOL
= 'I2' AND T6.ROWSTATUS = 1 AND CFA.UNDERFIXINGNO IS NULL
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T7
ON T7.INSTRUMENTID = UFXC.UNDERINSTRID AND T7.SCHEMESYMBOL
= 'CH' AND T7.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T8
ON T8.INSTRUMENTID = UFXC.UNDERINSTRID AND T8.SCHEMESYMBOL
= 'I-' AND T8.ROWSTATUS = 1
WHERE T1.ROWSTATUS = 1
AND (CFA.UNDERFIXINGNO IS NOT NULL
OR CFEV.SETTLESTYLETYPE IS NOT NULL);
--
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VLIID004 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.LISTINGNO,
T4.IDSYMBOL IDSYMBOL_BCN, T5.IDSYMBOL CURRENCY_I2,
T3.TRADINGPLACE, T3.CURRENCY, T3.TRADINGSTATUSTYPE,
T3.LISTINGSTATUSTYPE, T3.EXCHANGETYPE, T3.CREATETIMESTAMP,
T3.UPDATETIMESTAMP, T6.IDSYMBOL IDSYMBOL_RIC,
T7.IDSYMBOL IDSYMBOL_BBI
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTLISTING T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.VTINSTIIDENT T4
ON T4.INSTITUTIONID = T3.TRADINGPLACE AND T4.SCHEMESYMBOL
= 'BCN' AND T4.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T5
ON T5.INSTRUMENTID = T3.CURRENCY AND T5.SCHEMESYMBOL =
'I2' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T6
ON T6.INSTRUMENTID = T3.INSTRUMENTID AND T6.LISTINGNO = T3.
LISTINGNO AND T6.SCHEMESYMBOL = 'RIC' AND (T6.ISSECONDARY
IS NULL OR T6.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T7
ON T7.INSTRUMENTID = T3.INSTRUMENTID AND T7.LISTINGNO = T3.
LISTINGNO AND T7.SCHEMESYMBOL = 'BBI' AND (T7.ISSECONDARY
IS NULL OR T7.ISSECONDARY = 0)
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VVPPL005 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.CFPLANNO, T3.BEGINDATE,
T3.ENDDATE, T3.RATEFIXINGFREQ, T3.RTFIXFREQUNITTYPE,
T3.DAYSFIXEDBFACTPYDT, T3.DAYSFIXEDBFNXPYDT,
T3.CREATETIMESTAMP, T3.UPDATETIMESTAMP
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTPAYMENTPLAN T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VCAFO002 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.INSTRUMENTEVENTNO,
T5.CASHFLOWAMOUNTNO, T3.EVENTFUNCTIONTYPE,
T3.EFFECTIVEDATE, T3.CREATETIMESTAMP, T3.UPDATETIMESTAMP,
T4.SPREADINTERESTRATE, T4.ENTITLEBEGINDATE,
T4.ENTITLEENDDATE, T4.CREATETIMESTAMP CREATETIMESTAMP_CE,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_CE, T5.CFAMOUNTTYPE,
T5.HELDAMOUNT, T5.HELDUNITTYPE, T5.PAYAMOUNT,
T5.PAYUNITTYPE, T5.PAYRATE, T5.PRORATAPAYRATE,
T5.CREATETIMESTAMP CREATETIMESTAMP_CA,
T5.UPDATETIMESTAMP UPDATETIMESTAMP_CA
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTRUMENTEVENT T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.VTCFEVENT T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.
INSTRUMENTEVENTNO = T3.INSTRUMENTEVENTNO
LEFT OUTER JOIN
VDPS2.VTCASHFLOWAMOUNT T5
ON T5.INSTRUMENTID = T4.INSTRUMENTID AND T5.
INSTRUMENTEVENTNO = T4.INSTRUMENTEVENTNO
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
--
CREATE VIEW VDPS2.XPS_VINSH001 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T5.IDSYMBOL IDSYMBOL_BCN,
T9.IDSYMBOL CURRENCY_I2, T3.CURRENCY, T3.TRADINGSTATUSTYPE,
T3.LISTINGSTATUSTYPE, T4.ISSTEERINGFLAG4, T4.MANDATOR,
T6.IDSYMBOL IDSYMBOL_RIC, T7.IDSYMBOL IDSYMBOL_BBI,
T8.IDSYMBOL IDSYMBOL_TK_79,
T3.CREATETIMESTAMP CREATETIMESTAMP_VL,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_VL,
T4.CREATETIMESTAMP CREATETIMESTAMP_CL,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_CL
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTLISTING T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.CTLISTING T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.LISTINGNO = T3.
LISTINGNO AND T4.ISSTEERINGFLAG4 = 1
INNER JOIN
VDPS2.VTINSTIIDENT T5
ON T5.INSTITUTIONID = T3.TRADINGPLACE AND T5.SCHEMESYMBOL
= 'BCN' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T6
ON T6.INSTRUMENTID = T3.INSTRUMENTID AND T6.LISTINGNO = T3.
LISTINGNO AND T6.SCHEMESYMBOL = 'RIC' AND (T6.ISSECONDARY
IS NULL OR T6.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T7
ON T7.INSTRUMENTID = T3.INSTRUMENTID AND T7.LISTINGNO = T3.
LISTINGNO AND T7.SCHEMESYMBOL = 'BBI' AND (T7.ISSECONDARY
IS NULL OR T7.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGGRPKEY T8
ON T8.INSTRUMENTID = T3.INSTRUMENTID AND T8.LISTINGNO = T3.
LISTINGNO AND T8.INSTRGRSCHMSYMBOL = 'TK' AND T8.IDSYMBOL
= '79'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T9
ON T9.INSTRUMENTID = T3.CURRENCY AND T9.SCHEMESYMBOL =
'I2' AND T9.ROWSTATUS = 1
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
------------------------------------------------------------------------
-- View=VDPS2.XPS_VINSH002
------------------------------------------------------------------------
--
--
CREATE VIEW VDPS2.XPS_VINSH002 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T5.IDSYMBOL IDSYMBOL_BCN,
T9.IDSYMBOL CURRENCY_I2, T3.CURRENCY, T3.TRADINGSTATUSTYPE,
T3.LISTINGSTATUSTYPE, T4.ISSTEERINGFLAG4, T4.MANDATOR,
T6.IDSYMBOL IDSYMBOL_RIC, T7.IDSYMBOL IDSYMBOL_BBI,
T8.IDSYMBOL IDSYMBOL_TK_79,
T3.CREATETIMESTAMP CREATETIMESTAMP_VL,
T3.UPDATETIMESTAMP UPDATETIMESTAMP_VL,
T4.CREATETIMESTAMP CREATETIMESTAMP_CL,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_CL
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTLISTING T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.CTLISTING T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.LISTINGNO = T3.
LISTINGNO AND T4.ISSTEERINGFLAG4 = 1
INNER JOIN
VDPS2.VTINSTIIDENT T5
ON T5.INSTITUTIONID = T3.TRADINGPLACE AND T5.SCHEMESYMBOL
= 'BCN' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T6
ON T6.INSTRUMENTID = T3.INSTRUMENTID AND T6.LISTINGNO = T3.
LISTINGNO AND T6.SCHEMESYMBOL = 'RIC' AND (T6.ISSECONDARY
IS NULL OR T6.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T7
ON T7.INSTRUMENTID = T3.INSTRUMENTID AND T7.LISTINGNO = T3.
LISTINGNO AND T7.SCHEMESYMBOL = 'BBI' AND (T7.ISSECONDARY
IS NULL OR T7.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGGRPKEY T8
ON T8.INSTRUMENTID = T3.INSTRUMENTID AND T8.LISTINGNO = T3.
LISTINGNO AND T8.INSTRGRSCHMSYMBOL = 'TK' AND T8.IDSYMBOL
= '79'
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T9
ON T9.INSTRUMENTID = T3.CURRENCY AND T9.SCHEMESYMBOL =
'I2' AND T9.ROWSTATUS = 1
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VPOCG002 FOR VDPS2.XPS_VPOCG002;
COMMIT;
CREATE ALIAS VDPS.XPS_VOPPA003 FOR VDPS2.XPS_VOPPA003;
COMMIT;
CREATE ALIAS VDPS.XPS_VLIID004 FOR VDPS2.XPS_VLIID004;
COMMIT;
CREATE ALIAS VDPS.XPS_VVPPL005 FOR VDPS2.XPS_VVPPL005;
COMMIT;
CREATE ALIAS VDPS.XPS_VCAFO002 FOR VDPS2.XPS_VCAFO002;
COMMIT;
CREATE ALIAS VDPS.XPS_VINSH002 FOR VDPS2.XPS_VINSH002;
COMMIT;
--
--
DROP VIEW VDPS2.XPS_VCFAM002;
COMMIT;
--
CREATE VIEW VDPS2.XPS_VCFAM002 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T6.EVENTFUNCTIONTYPE,
T6.IDSYMBOL HOSTLINK, T6.BUSINESSTYPE, T6.EFFECTIVEDATE,
T8.IRREGULARCPNTYPE, T8.PHYSICALSECURITYNO,
T8.VALIDATIONTYPE, T8.PAYMENTSTATUSTYPE,
T7.ISVALIDRANGEEXDEPO, T7.ISVALIDRANGEPRESEN,
T7.INFORMATIONSOURCE, T6.CSEVENTSTATUSTYPE,
T10.CFAMOUNTTYPE, T10.PAYAMOUNT, T4.IDSYMBOL AMOUNTCURR_CS,
T10.PRORATAPAYRATE, T6.RECORDDATE, T5.PAYFREQUENCY,
T5.PYFREQUNITTYPE, T5.EFFINTERESTRATE,
T5.FIRSTPYDATEPERYEAR, T5.MATURITYDATE, T5.NONVERSEAMOUNT,
T10.PAYRATE, T8.ENTITLETIMENO, T8.ENTITLETIMEUNITTP,
T10.HELDAMOUNT, T10.HELDUNITTYPE, T8.ENTITLEBEGINDATE,
T8.ENTITLEENDDATE, T8.DIVIDENDTYPE, T8.DIVIOCCURENCETYPE,
T6.CSEXDATE, T10.AMOUNTSTATUSTYPE
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTRWITHCF T5
ON T2.INSTRUMENTID = T5.INSTRUMENTID
INNER JOIN
VDPS2.VTINSTRUMENTEVENT T6
ON T2.INSTRUMENTID = T6.INSTRUMENTID AND T6.PROVIDERTYPE =
1
LEFT JOIN
VDPS2.VTCSEVENTDETAIL T7
ON T6.INSTRUMENTID = T7.INSTRUMENTID AND T6.
INSTRUMENTEVENTNO = T7.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTCFEVENT T8
ON T6.INSTRUMENTID = T8.INSTRUMENTID AND T6.
INSTRUMENTEVENTNO = T8.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTCASHFLOWAMOUNT T10
ON T8.INSTRUMENTID = T10.INSTRUMENTID AND T8.
INSTRUMENTEVENTNO = T10.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTINSTRIDENT T4
ON T10.PAYINSTRUMENT = T4.INSTRUMENTID AND T4.SCHEMESYMBOL
= 'CS-WS80-WHRG'
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
--
DROP VIEW VDPS2.XPS_VCFAM001;
COMMIT;
--
CREATE VIEW VDPS2.XPS_VCFAM001 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T6.EVENTFUNCTIONTYPE,
T6.IDSYMBOL HOSTLINK, T6.BUSINESSTYPE, T6.EFFECTIVEDATE,
T8.IRREGULARCPNTYPE, T8.PHYSICALSECURITYNO,
T8.VALIDATIONTYPE, T8.PAYMENTSTATUSTYPE,
T6.CSEVENTSTATUSTYPE, T10.CFAMOUNTTYPE, T10.PAYAMOUNT,
T4.IDSYMBOL AMOUNTCURR_CS, T10.PRORATAPAYRATE,
T6.RECORDDATE, T5.PAYFREQUENCY, T5.PYFREQUNITTYPE,
T5.EFFINTERESTRATE, T5.FIRSTPYDATEPERYEAR, T5.MATURITYDATE,
T5.NONVERSEAMOUNT, T10.PAYRATE, T8.ENTITLETIMENO,
T8.ENTITLETIMEUNITTP, T10.HELDAMOUNT, T10.HELDUNITTYPE,
T8.ENTITLEBEGINDATE, T8.ENTITLEENDDATE, T8.DIVIDENDTYPE,
T8.DIVIOCCURENCETYPE, T6.CSEXDATE, T10.AMOUNTSTATUSTYPE
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTRWITHCF T5
ON T2.INSTRUMENTID = T5.INSTRUMENTID
INNER JOIN
VDPS2.VTINSTRUMENTEVENT T6
ON T2.INSTRUMENTID = T6.INSTRUMENTID AND T6.PROVIDERTYPE =
1
LEFT JOIN
VDPS2.VTCFEVENT T8
ON T6.INSTRUMENTID = T8.INSTRUMENTID AND T6.
INSTRUMENTEVENTNO = T8.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTCASHFLOWAMOUNT T10
ON T8.INSTRUMENTID = T10.INSTRUMENTID AND T8.
INSTRUMENTEVENTNO = T10.INSTRUMENTEVENTNO
LEFT JOIN
VDPS2.VTINSTRIDENT T4
ON T10.PAYINSTRUMENT = T4.INSTRUMENTID AND T4.SCHEMESYMBOL
= 'CS-WS80-WHRG'
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
CREATE VIEW VDPS2.XPS_VDISP000 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL,
T5.SCHEMESYMBOL PARENT_SCHEME, T5.IDSYMBOL PARENT_IDSYMBOL,
T3.BEGINDATE, T3.ENDDATE, T3.TYPEX, T3.MGMTCONTRACTTYPE,
T3.CHILDINSTIID, T3.PARENTINSTIID, T3.INSTISTRUCTNO,
T3.CREATETIMESTAMP, T3.UPDATETIMESTAMP,
T2.CREATETIMESTAMP CREATETIMESTAMP_IC,
T2.UPDATETIMESTAMP UPDATETIMESTAMP_IC,
T4.CREATETIMESTAMP CREATETIMESTAMP_IP,
T4.UPDATETIMESTAMP UPDATETIMESTAMP_IP
FROM VDPS2.VTINSTIIDENT T1
INNER JOIN
VDPS2.VTINSTITUTION T2
ON T2.INSTITUTIONID = T1.INSTITUTIONID AND (T2.ROWSTATUS
IS NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTISTRUCT T3
ON T3.CHILDINSTIID = T2.INSTITUTIONID
INNER JOIN
VDPS2.VTINSTITUTION T4
ON T4.INSTITUTIONID = T3.PARENTINSTIID AND (T4.ROWSTATUS
IS NULL OR T4.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTINSTIIDENT T5
ON T5.INSTITUTIONID = T4.INSTITUTIONID AND T5.ROWSTATUS = 1
WHERE T1.ROWSTATUS = 1;
COMMIT;
CREATE ALIAS VDPS.XPS_VDISP000 FOR VDPS2.XPS_VDISP000;
COMMIT;
--
DROP VIEW VDPS2.XPS_VLIID003;
CREATE VIEW VDPS2.XPS_VLIID003 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.LISTINGNO,
T4.IDSYMBOL IDSYMBOL_BCN, T5.IDSYMBOL CURRENCY_I2,
T3.TRADINGPLACE, T3.CURRENCY, T3.TRADINGSTATUSTYPE,
T3.LISTINGSTATUSTYPE, T3.EXCHANGETYPE, T3.CREATETIMESTAMP,
T3.UPDATETIMESTAMP, T6.IDSYMBOL IDSYMBOL_RIC,
T7.IDSYMBOL IDSYMBOL_BBI
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTLISTING T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.VTINSTIIDENT T4
ON T4.INSTITUTIONID = T3.TRADINGPLACE AND T4.SCHEMESYMBOL
= 'BCN' AND T4.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTINSTRIDENT T5
ON T5.INSTRUMENTID = T3.CURRENCY AND T5.SCHEMESYMBOL =
'I2' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T6
ON T6.INSTRUMENTID = T3.INSTRUMENTID AND T6.LISTINGNO = T3.
LISTINGNO AND T6.SCHEMESYMBOL = 'RIC' AND (T6.ISSECONDARY
IS NULL OR T6.ISSECONDARY = 0)
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T7
ON T7.INSTRUMENTID = T3.INSTRUMENTID AND T7.LISTINGNO = T3.
LISTINGNO AND T7.SCHEMESYMBOL = 'BBI' AND (T7.ISSECONDARY
IS NULL OR T7.ISSECONDARY = 0)
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--
CREATE VIEW VDPS2.XPS_VISCO005 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.PAYMENTDATE,
T3.PAYMENTTYPE, T4.PAYDATE
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID
INNER JOIN
VDPS2.VTISSUECONDITION T3
ON T3.INSTRUMENTID = T2.INSTRUMENTID
LEFT OUTER JOIN
VDPS2.VTISSUEPAYSCHEDULE T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.
ISSUECONDITIONNO = T3.ISSUECONDITIONNO
WHERE T1.ROWSTATUS = 1
AND (T2.ROWSTATUS IS NULL
OR T2.ROWSTATUS <> 2);
--
COMMIT;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--
COMMIT;
CREATE ALIAS VDPS.XPS_VISCO005 FOR VDPS2.XPS_VISCO005;
COMMIT;
------------------------------------------------------------------------
-- VIEW=VDPS2.XPS_VLIID005
-- Nachtrag von Felix Metzler am 17.04.07 / P. Kuhn
------------------------------------------------------------------------
--
CREATE VIEW VDPS2.XPS_VLIID005 AS
SELECT T1.IDSYMBOL, T1.SCHEMESYMBOL, T3.CURRENCY,
T3.CSLISTINGCODETYPE, T3.TRADINGPLACE,
T3.TRADINGSTATUSTYPE, T3.LISTINGSTATUSTYPE,
T4.ISSTEERINGFLAG4 CS_HAUPTBOEPLATZ,
T5.IDSYMBOL IDSYMBOL_BCA, T6.IDSYMBOL IDSYMBOL_TICKER,
T6.ISSECONDARY, T6.SCHEMESYMBOL SCHEMESYMBOL_LIST
FROM VDPS2.VTINSTRIDENT T1
INNER JOIN
VDPS2.VTINSTRUMENT T2
ON T2.INSTRUMENTID = T1.INSTRUMENTID AND (T2.ROWSTATUS IS
NULL OR T2.ROWSTATUS <> 2)
INNER JOIN
VDPS2.VTLISTING T3
ON T3.INSTRUMENTID = T1.INSTRUMENTID
LEFT OUTER JOIN
VDPS2.CTLISTING T4
ON T4.INSTRUMENTID = T3.INSTRUMENTID AND T4.LISTINGNO = T3.
LISTINGNO
INNER JOIN
VDPS2.VTINSTIIDENT T5
ON T5.INSTITUTIONID = T3.TRADINGPLACE AND T5.SCHEMESYMBOL
= 'BCA' AND T5.ROWSTATUS = 1
LEFT OUTER JOIN
VDPS2.VTLISTINGIDENT T6
ON T6.INSTRUMENTID = T3.INSTRUMENTID AND T6.LISTINGNO = T3.
LISTINGNO
WHERE T1.ROWSTATUS = 1;
--
COMMIT;
--
CREATE ALIAS VDPS.XPS_VLIID005 FOR VDPS2.XPS_VLIID005;
COMMIT;