zOs/SQL/AV15047C
set current sqlid = 'S100447';
set current schema OA1A;
CREATE VIEW OA1A.VAV160B1
( PARTITIONID , REPRTUUID , OBJECTTYPECD , ELEMENTNBR ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , TOTALMKTVALINREPRTCCY ,
VALTYPECD , ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD ,
MSCISECTCD , MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD ,
OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD ) AS
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND ISINCLFUNDUNBUNDLING = 'N' AND
MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND ISINCLFUNDUNBUNDLING = 'N' AND
MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , 'FuBu-Pos' ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV113A1
WHERE MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV122J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , OPICSDEALNO ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
COUNTERCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, '000' , '05' , BVGCATCD , '00' , '00' , '00' , '00' , OBJUUID
, ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV132A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , TRADEID ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
CTRNOTIONALCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00' , '00' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV133A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MKTVALINREPRTCCY , 0 , COALESCE (
MKTVALINREPRTCCY , 0 ) , '0' , COUNTERCCYCD , ANALYSISCNTRYCD ,
ASSETMAINCLASS , ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00'
, '00' , '00' , '00' , OBJUUID , ANALYSISCCYDERIVED ,
ASSETCLASSDERIVED , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED ,
''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
;
-- OA1A.VAV160E1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160E1
( PARTITIONID , REPRTUUID , OBJECTTYPECD , ELEMENTNBR ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , TOTALMKTVALINREPRTCCY ,
VALTYPECD , ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD ,
MSCISECTCD , MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD ,
OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD ) AS
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV122J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , OPICSDEALNO ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
COUNTERCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, '000' , '05' , BVGCATCD , '00' , '00' , '00' , '00' , OBJUUID
, ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV132A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , TRADEID ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
CTRNOTIONALCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00' , '00' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV133A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MKTVALINREPRTCCY , 0 , COALESCE (
MKTVALINREPRTCCY , 0 ) , '0' , COUNTERCCYCD , ANALYSISCNTRYCD ,
ASSETMAINCLASS , ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00'
, '00' , '00' , '00' , OBJUUID , ANALYSISCCYDERIVED ,
ASSETCLASSDERIVED , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED ,
''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
;
-- OA1A.VAV160G1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160G1
( PARTITIONID , REPRTUUID , OBJECTTYPECD , ELEMENTNBR , MTYDT ,
SUBPERIODMONTH , SUBPERIODYEAR , REDEMPTCCYCD ,
REDEMPTVALINREDEMPTCCY , REDEMPTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD ) AS
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , REDEMPTCCYCD ,
REDEMPTVALINREDEMPTCCY , REDEMPTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND REDEMPTVALINREDEMPTCCY IS NOT NULL AND
REDEMPTVALINREPRTCCY IS NOT NULL AND VALTYPECD = '0' AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , CASE WHEN ( SECPOSCCYCD IN (
' ' , '0000' ) ) THEN SECCCYCD ELSE SECPOSCCYCD END ,
MKTVALINPOSCCY , MKTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0' AND REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO , '01.01.0001' ,
'000000' , '0000' , ACCCCYCD , MKTVALINACCCCY , MKTVALINREPRTCCY
, OBJUUID , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , ACCCCYCD , MKTVALINACCCCY ,
MKTVALINREPRTCCY , OBJUUID , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MTYDT , SUBSTR ( CHAR ( MTYDT , ISO ) , 1
, 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) , SUBSTR (
CHAR ( MTYDT , ISO ) , 1 , 4 ) , TRDCCYCD ,
BALANCEOFTRDPARTINTRDCCY , BALANCEOFTRDPARTINREPRTCCY , OBJUUID
, ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
TREASURYTYPECD = 1 AND ASSETSUBCLASS = '01' AND REPRTBLOCCD =
'10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MTYDT , SUBSTR ( CHAR ( MTYDT , ISO ) , 1
, 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) , SUBSTR (
CHAR ( MTYDT , ISO ) , 1 , 4 ) , COUNTERCCYCD ,
BALANCEOFCTRPARTINCTRCCY , BALANCEOFCTRPARTINREPRTCCY , OBJUUID
, ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
;
-- OA1A.VAV160C1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160C1
( PARTITIONID , REPRTUUID , OBJECTTYPECD , ELEMENTNBR , MTYDT ,
SUBPERIODMONTH , SUBPERIODYEAR , REDEMPTCCYCD ,
REDEMPTVALINREDEMPTCCY , REDEMPTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD ) AS
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , REDEMPTCCYCD ,
REDEMPTVALINREDEMPTCCY , REDEMPTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND REDEMPTVALINREDEMPTCCY IS NOT NULL AND
REDEMPTVALINREPRTCCY IS NOT NULL AND VALTYPECD = '0'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , CASE WHEN ( SECPOSCCYCD IN (
' ' , '0000' ) ) THEN SECCCYCD ELSE SECPOSCCYCD END ,
MKTVALINPOSCCY , MKTVALINREPRTCCY , OBJUUID ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO , '01.01.0001' ,
'000000' , '0000' , ACCCCYCD , MKTVALINACCCCY , MKTVALINREPRTCCY
, OBJUUID , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO , CASE WHEN (
MTYDT IS NULL ) THEN '31.12.9999' ELSE MTYDT END , CASE WHEN (
MTYDT IS NULL ) THEN '999999' ELSE SUBSTR ( CHAR ( MTYDT , ISO )
, 1 , 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) END ,
CASE WHEN ( MTYDT IS NULL ) THEN '9999' ELSE SUBSTR ( CHAR (
MTYDT , ISO ) , 1 , 4 ) END , ACCCCYCD , MKTVALINACCCCY ,
MKTVALINREPRTCCY , OBJUUID , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MTYDT , SUBSTR ( CHAR ( MTYDT , ISO ) , 1
, 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) , SUBSTR (
CHAR ( MTYDT , ISO ) , 1 , 4 ) , TRDCCYCD ,
BALANCEOFTRDPARTINTRDCCY , BALANCEOFTRDPARTINREPRTCCY , OBJUUID
, ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
TREASURYTYPECD = 1 AND ASSETSUBCLASS = '01'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MTYDT , SUBSTR ( CHAR ( MTYDT , ISO ) , 1
, 4 ) CONCAT SUBSTR ( CHAR ( MTYDT , ISO ) , 6 , 2 ) , SUBSTR (
CHAR ( MTYDT , ISO ) , 1 , 4 ) , COUNTERCCYCD ,
BALANCEOFCTRPARTINCTRCCY , BALANCEOFCTRPARTINREPRTCCY , OBJUUID
, ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
;
-- OA1A.VAV160F1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160F1
( PARTITIONID , REPRTUUID , OBJECTTYPECD , ELEMENTNBR ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , TOTALMKTVALINREPRTCCY ,
VALTYPECD , ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD ,
MSCISECTCD , MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD ,
OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , RATEGRPCD ) AS
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND ISINCLFUNDUNBUNDLING = 'N' AND
MKTVALINREPRTCCY IS NOT NULL AND REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , SECNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , RATEGRPCD
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND ISINCLFUNDUNBUNDLING = 'N' AND
MKTVALINREPRTCCY IS NOT NULL AND REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , 'FuBu-Pos' ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , MSCISECTCD ,
MSCIGROUPCD , MSCIINDSTRYCD , MSCISUBINDSTRYCD , OBJUUID ,
ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV113A1
WHERE MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV122J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , ACCNO ,
MKTVALINREPRTCCY , ACCRINTINREPRTCCY , COALESCE (
MKTVALINREPRTCCY , 0 ) + ACCRINTINREPRTCCY , VALTYPECD ,
ANALYSISCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, ANALYSISSECTCD , ANALYSISDEBTORCD , BVGCATCD , CASE WHEN (
ANALYSISSECTCD = '260' ) THEN '15' ELSE '40' END , '10' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , OPICSDEALNO ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
COUNTERCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS , ASSETSUBCLASS
, '000' , '05' , BVGCATCD , '00' , '00' , '00' , '00' , OBJUUID
, ANALYSISCCYDERIVED , ASSETCLASSDERIVED , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED , ''
FROM OA1A.VAV132A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , TRADEID ,
MKTVALINREPRTCCY , 0 , COALESCE ( MKTVALINREPRTCCY , 0 ) , '0' ,
CTRNOTIONALCCYCD , ANALYSISCNTRYCD , ASSETMAINCLASS ,
ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00' , '00' , '00' ,
'00' , OBJUUID , ANALYSISCCYDERIVED , ASSETCLASSDERIVED ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED , ''
FROM OA1A.VAV133A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , OBJECTTYPECD , CONCAT ( CONCAT (
TRDCCYCD , '/' ) , CONCAT ( COUNTERCCYCD , CONCAT ( '@' , CHAR (
MTYDT , ISO ) ) ) ) , MKTVALINREPRTCCY , 0 , COALESCE (
MKTVALINREPRTCCY , 0 ) , '0' , COUNTERCCYCD , ANALYSISCNTRYCD ,
ASSETMAINCLASS , ASSETSUBCLASS , '000' , '05' , BVGCATCD , '00'
, '00' , '00' , '00' , OBJUUID , ANALYSISCCYDERIVED ,
ASSETCLASSDERIVED , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED ,
''
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
;
-- OA1A.VAV160H1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160H1
( PARTITIONID , REPRTUUID , REDEMPTCCYCD , TOTALMKTVALINREPRTCCY
, DURATION , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED ) AS
SELECT PARTITIONID , REPRTUUID , REDEMPTCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , DURATION , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0'
UNION ALL
SELECT PARTITIONID , REPRTUUID , CASE WHEN ( SECPOSCCYCD IN ( ' ' ,
'0000' ) ) THEN SECCCYCD ELSE SECPOSCCYCD END , MKTVALINREPRTCCY
+ ACCRINTINREPRTCCY , DURATION , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0'
UNION ALL
SELECT PARTITIONID , REPRTUUID , ACCCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , 0 , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , ACCCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , 0 , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
UNION ALL
SELECT PARTITIONID , REPRTUUID , TRDCCYCD , MKTVALINREPRTCCY , 0 ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
TREASURYTYPECD = 1 AND ASSETSUBCLASS = '01'
UNION ALL
SELECT PARTITIONID , REPRTUUID , COUNTERCCYCD , MKTVALINREPRTCCY , 0 ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL
;
-- OA1A.VAV160J1 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
CREATE VIEW OA1A.VAV160J1
( PARTITIONID , REPRTUUID , REDEMPTCCYCD , TOTALMKTVALINREPRTCCY
, DURATION , ASSETCLASS01DERIVED , ASSETCLASS02DERIVED ) AS
SELECT PARTITIONID , REPRTUUID , REDEMPTCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , DURATION , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV111J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0' AND REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , CASE WHEN ( SECPOSCCYCD IN ( ' ' ,
'0000' ) ) THEN SECCCYCD ELSE SECPOSCCYCD END , MKTVALINREPRTCCY
+ ACCRINTINREPRTCCY , DURATION , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV112J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
VALTYPECD = '0' AND REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , ACCCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , 0 , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV121J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , ACCCCYCD , MKTVALINREPRTCCY +
ACCRINTINREPRTCCY , 0 , ASSETCLASS01DERIVED ,
ASSETCLASS02DERIVED
FROM OA1A.VAV123J1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , TRDCCYCD , MKTVALINREPRTCCY , 0 ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
TREASURYTYPECD = 1 AND ASSETSUBCLASS = '01' AND REPRTBLOCCD =
'10'
UNION ALL
SELECT PARTITIONID , REPRTUUID , COUNTERCCYCD , MKTVALINREPRTCCY , 0 ,
ASSETCLASS01DERIVED , ASSETCLASS02DERIVED
FROM OA1A.VAV135A1
WHERE POSITIONTYPECD = 1 AND MKTVALINREPRTCCY IS NOT NULL AND
REPRTBLOCCD = '10'
;