About

 
 

 exec [dbo].[etl_DATA_LINEAGE_CRUD] 'DELETE','dim_ACCOUNT_TYPE','WANLINK\pbhung', '{"AccountTypeID":29}'

-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'UPDATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID": 3, "AssetTypeCode": "Asset Type Code Changed", "AssetTypeName": "Asset Type Name Changed", "ModifiedUser": "WANLINK\pbhung", "ModifiedDate": "2016-10-17T21:16:35Z"}'

-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'CREATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID":0,"AssetTypeCode":"Test Code 3","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


CREATE PROCEDURE [dbo].[etl_DATA_LINEAGE_CRUD] 

(@RequestType varchar(255), @TableName varchar(255), @UserName varchar(255), @JSON varchar(MAX), @ClaimsKey varchar(MAX)=NULL)


AS

BEGIN


declare @key int

set @key = 0


--tableName varchar

--userName varchar

--json varchar


--DECLARE @userName varchar(8000)

--DECLARE @RequestType varchar(8000)

--DECLARE @JSON varchar(8000)

--DECLARE @TableName varchar(255)


--SET @TableName = 'dim_ASSET_TYPE'


--SET @RequestType='CREATE'


--SET @JSON='{"AssetTypeID":0,"AssetTypeCode":"TEST301","AssetTypeName":"TEST301","AssetTypeDisplayName":"TEST301","IsActive":true,"CreatedDate":"2017-01-24T20:42:25.972Z","CreatedUser":"WANLINK\\MVWaller","ModifiedDate":"2017-01-24T20:42:25.972Z","ModifiedUser":"WANLINK\\MVWaller"}'

--SET @UserName ='WALLER'


----SET @JSON  = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-09-28T18:12:37.597-00:00","CreatedUser":"WANLINK\PBHung","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


----@RequestType=UPDATE--

----SET @JSON  = '{"AssetTypeCode":"Asset Type Code Changed","AssetTypeName":"Asset Type Name Changed","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "AssetTypeCode": "Asset Type Code Changed",

--  "AssetTypeName": "Asset Type Name Changed",

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:16:35Z"

--}'


----@RequestType=DELETE--

----SET @JSON = '{"IsActive":false,"ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "IsActive": false,

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:47:24Z"

--}'


SET @JSON =REPLACE(@JSON,'''','''''')


-----FULL ARCHIVE


SET @TABLENAME =

(SELECT MAX(name)

FROM sys.tables tt 

WHERE REPLACE(REPLACE(REPLACE(REPLACE(tt.Name,'_',''),'dim',''),'ref',''),'fct','') = REPLACE(REPLACE(REPLACE(REPLACE(@TableName,'_',''),'dim',''),'ref',''),'fct','') )



INSERT INTO dbo.ref_DATA_LINEAGE_TRANSACTION_HISTORY

SELECT 

@RequestType as RequestType, 

@TableName as TableName, 

@UserName UserName, 

@JSON as JSON,

GETDATE() as RunDateTime



--------------------------------------------------

--SELECT *,@TableName as TableName

--INTO #JSON

--FROM parseJson (@JSON)




CREATE TABLE #JSON

(element_id  [int] IDENTITY(1,1) NOT NULL,

sequenceNo int,

parent_ID int,

Object_ID int,

NAME varchar(MAX),

StringValue varchar(MAX),

ValueType varchar(250),

TableName varchar(250))



IF @TableName <> 'ref_AG_GRID_USER_MODEL' OR @RequestType NOT IN ('CREATE','UPDATE') 

BEGIN


INSERT INTO #JSON

SELECT sequenceNo, parent_ID, Object_ID, NAME, StringValue,ValueType,@TableName as TableName

FROM parseJson (@JSON)


END



IF @TableName = 'ref_AG_GRID_USER_MODEL' AND @RequestType IN ('CREATE','UPDATE')

BEGIN


INSERT INTO #JSON

SELECT --ROW_NUMBER() OVER (PARTITION BY [key] ORDER BY [key]) as element_id,

0 as sequenceNo,

1 as parent_ID,

NULL as ObjectID,

[key] as NAME, 

value as StringValue, 

'string' as ValueType,

@TableName as TableName

FROM OPENJSON (@JSON)


END





-----------------------------NEED TO DETERMINE THIS BASED ON MORE SPECIFIC DESIGINATION IN JSON STRING ----------------------


--SELECT * FROM #JSON


--DO PK LOOKUP FROM SYS OBJECTS --


DECLARE @IdentifierColumn varchar(55)


SELECT  @IdentifierColumn = COL_NAME(ic.OBJECT_ID,ic.column_id) 

---i.name AS IndexName,

        --OBJECT_NAME(ic.OBJECT_ID) AS TableName,    

FROM    sys.indexes AS i INNER JOIN 

        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID

                                AND i.index_id = ic.index_id

WHERE   i.is_primary_key = 1

AND OBJECT_NAME(ic.OBJECT_ID)  = @TABLENAME


--SET @IdentifierColumn = (SELECT MAX(NAME) FROM #JSON WHERE Element_ID = 1)


DECLARE @IdentifierID varchar(55)

SET @IdentifierID = (SELECT MAX(StringValue) FROM #JSON WHERE NAME = @IdentifierColumn)




---DELETE BACKUP--



IF @RequestType = 'DELETE'

BEGIN


DECLARE @DELETELOGSQL varchar(MAX)

SET @DELETELOGSQL = 'DECLARE @DELETELOGJSON NVARCHAR(MAX) = (SELECT * FROM ' + @TableName + ' '

  --+ 'SET IsActive = 0 '

  + 'WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID

  + '  FOR JSON AUTO ) 

 


  INSERT INTO dbo.ref_DATA_LINEAGE_TRANSACTION_HISTORY

SELECT ' +

'''' +'DeleteBackup' + '''' + ' as RequestType,  ' +

'''' + @TableName + ''''  + ' as TableName, ' +

'''' + @UserName + '''' + ' UserName, 

@DELETELOGJSON as JSON,

GETDATE() as RunDateTime '


--SELECT @DELETELOGSQL


EXEC (@DELETELOGSQL)


END





----------------------------------------------------------------------------------------

--AUDIT FIELDS--


IF @RequestType = 'CREATE'

BEGIN


INSERT INTO #JSON


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'IsActive' as NAME, '1' as StringValue, 'int' as ValueType, @TableName as TableName


UNION ALL


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'CreatedDate' as NAME, LEFT(CONVERT(VARCHAR, GETDATE(), 121), 28) as StringValue, 'datetime' as ValueType, @TableName as TableName


UNION ALL


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'CreatedUser' as NAME, @UserName as StringValue, 'string' as ValueType , @TableName as TableName


END



IF @RequestType = 'UPDATE'

BEGIN


INSERT INTO #JSON


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'ModifiedDate' as NAME, LEFT(CONVERT(VARCHAR, GETDATE(), 121), 28) as StringValue, 'datetime' as ValueType , @TableName as TableName


UNION ALL


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'ModifiedUser' as NAME, @UserName as StringValue, 'string' as ValueType , @TableName as TableName


END


------------------------------------------------------------------------



IF @RequestType = 'DELETE'

BEGIN


DECLARE @SQL varchar(MAX)


DECLARE @UpdateString varchar(MAX)


SELECT @UpdateString=STUFF( (   SELECT ', ' + CONVERT(VARCHAR(500), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(500), StringValue) + '''' 

                            FROM #JSON 

WHERE NAME <> @IdentifierColumn

                            --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')


SET @UpdateString = REPLACE(@UpdateString,'&lt;&gt;','<>')

SET @UpdateString = REPLACE(@UpdateString,'&lt;','<')

SET @UpdateString = REPLACE(@UpdateString,'&gt;','>')


SET @SQL = 'DELETE ' + @TableName + ' '

  --+ 'SET IsActive = 0 '

  + 'WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID


--SET @SQL = 'UPDATE ' + @TableName + ' '

--   --+ 'SET IsActive = 0 '

--   + 'SET ' + @UpdateString

--   + 'WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID


SET @SQL = REPLACE(@SQL,'''NULL''','NULL')


SET @SQL = REPLACE(@SQL,'''''','NULL')

--SELECT @UpdateString


EXEC (@SQL)


END






IF @RequestType = 'UPDATE'

BEGIN


DECLARE @SQL2 varchar(MAX)


DECLARE @UpdateString2 varchar(MAX)


--SELECT * 

--FROM #JSON


SELECT @UpdateString2=STUFF( (   SELECT ', ' + CONVERT(VARCHAR(500), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(MAX), StringValue) + '''' 

                            FROM #JSON 

                            --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')



SET @UpdateString2 = REPLACE(@UpdateString2,'&lt;&gt;','<>')

SET @UpdateString2 = REPLACE(@UpdateString2,'&lt;','<')

SET @UpdateString2 = REPLACE(@UpdateString2,'&gt;','>')


--DECLARE @UpdateString2 varchar(MAX)

--SET @UpdateString2 = ''


--SELECT @UpdateString2= @UpdateString2 + CONVERT(VARCHAR(500), NAME) + ' '+ ' = '   + '''' +   CONVERT(VARCHAR(500), StringValue) + ''''   --+ ', ' + CONVERT(VARCHAR(500), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(500), StringValue) + '''' 

--FROM #JSON 

--WHERE NAME <> @IdentifierColumn

--AND parent_ID IS NOT NULL



--SELECT @UpdateString2


SET @SQL2 = 'UPDATE ' + @TableName + ' '

-- + 'SET IsActive = 1 , '

  + 'SET ' + @UpdateString2

  + ' WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID


SET @SQL2 = REPLACE(@SQL2,'''NULL''','NULL')


SET @SQL2 = REPLACE(@SQL2,''''',','NULL,')


--SELECT @SQL2

--SELECT @UpdateString2

EXEC (@SQL2)


--SELECT @SQL2

--RETURN VALUES BACK--

DECLARE @SQLReturn2 varchar(MAX)


SET @SQLReturn2 = 

'SELECT * FROM ' + @TableName + 

  + ' WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID

EXEC (@SQLReturn2)


END




IF @RequestType = 'CREATE'

BEGIN


DECLARE @SQL3 varchar(MAX)


DECLARE @ValuesString varchar(MAX)


DECLARE @CreateString varchar(MAX)


--SELECT * 

--FROM #JSON


SELECT @ValuesString=STUFF( (   SELECT ', ' +   CONVERT(VARCHAR(500), NAME) 

                            FROM #JSON 

                            --WHERE 1=1--

    --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')



SET @ValuesString = REPLACE(@ValuesString,'&lt;&gt;','<>')

SET @ValuesString = REPLACE(@ValuesString,'&lt;','<')

SET @ValuesString = REPLACE(@ValuesString,'&gt;','>')

-------------------------------


SELECT @CreateString=STUFF( (   SELECT ', ' + '''' +   CONVERT(VARCHAR(MAX), StringValue) + '''' 

                            FROM #JSON 

    --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')


SET @CreateString = REPLACE(@CreateString,'&lt;&gt;','<>')

SET @CreateString = REPLACE(@CreateString,'&lt;','<')

SET @CreateString = REPLACE(@CreateString,'&gt;','>')



-------------------------------



SET @SQL3 = 'INSERT INTO ' + @TableName + ' '

  + '(' + 

  CASE WHEN @TableName <> 'ref_DIG_RULES_ENGINE' THEN @ValuesString

  ELSE @ValuesString + ', ' +  'ReportFilterSQL' END 

  + ')'

  + 'SELECT '

  + 

  CASE WHEN @TableName <> 'ref_DIG_RULES_ENGINE' THEN @CreateString

  ELSE @CreateString + ', ' +  '''' + (SELECT ReportFilterSQL FROM [GI_MASTER].[dbo].[dim_REPORT_FILTER] 

          WHERE ReportFilterID = 5) + '''' END 


   

--SET @SQL3 = 'INSERT INTO ' + @TableName + ' '

--   + '(' + @ValuesString + ')'

--   + 'SELECT '

--   + @CreateString


--SELECT @@ValuesString

--SELECT @CreateString


--SELECT @SQL3


SET @SQL3 = REPLACE(@SQL3,'''NULL''','NULL')


EXEC (@SQL3) 


DECLARE @SQLReturn3 varchar(MAX)


SET @SQLReturn3 = 

'SELECT * FROM ' + @TableName + 

  + ' WHERE ' + @IdentifierColumn + ' = ' + CONVERT(varchar(255),@@IDENTITY)


--SELECT @SQLReturn3

EXEC (@SQLReturn3)


--set @key = @@IDENTITY


--SELECT @key


END



END





GO

/****** Object:  StoredProcedure [dbo].[etl_DATA_LINEAGE_CRUD_arch_0731]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO






-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'DELETE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID": 3,"IsActive": false,"ModifiedUser": "WANLINK\pbhung","ModifiedDate": "2016-10-17T21:47:24Z"}'

--exec [dbo].[etl_DATA_LINEAGE_CRUD] 'UPDATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID": 3, "AssetTypeCode": "Asset Type Code Changed!", "AssetTypeName": "Asset Type Name Changed"}'

-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'CREATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID":0,"AssetTypeCode":"Test Code 3","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


CREATE PROCEDURE [dbo].[etl_DATA_LINEAGE_CRUD_arch_0731] 

(@RequestType varchar(255), @TableName varchar(255), @UserName varchar(255), @JSON varchar(MAX))


AS

BEGIN


declare @key int

set @key = 0


--tableName varchar

--userName varchar

--json varchar


--DECLARE @userName varchar(8000)

--DECLARE @RequestType varchar(8000)

--DECLARE @JSON varchar(8000)

--DECLARE @TableName varchar(255)


--SET @TableName = 'dim_ASSET_TYPE'


--SET @RequestType='CREATE'


--SET @JSON='{"AssetTypeID":0,"AssetTypeCode":"TEST301","AssetTypeName":"TEST301","AssetTypeDisplayName":"TEST301","IsActive":true,"CreatedDate":"2017-01-24T20:42:25.972Z","CreatedUser":"WANLINK\\MVWaller","ModifiedDate":"2017-01-24T20:42:25.972Z","ModifiedUser":"WANLINK\\MVWaller"}'

--SET @UserName ='WALLER'


----SET @JSON  = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-09-28T18:12:37.597-00:00","CreatedUser":"WANLINK\PBHung","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


----@RequestType=UPDATE--

----SET @JSON  = '{"AssetTypeCode":"Asset Type Code Changed","AssetTypeName":"Asset Type Name Changed","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "AssetTypeCode": "Asset Type Code Changed",

--  "AssetTypeName": "Asset Type Name Changed",

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:16:35Z"

--}'


----@RequestType=DELETE--

----SET @JSON = '{"IsActive":false,"ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "IsActive": false,

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:47:24Z"

--}'


SET @JSON =REPLACE(@JSON,'''','''''')


-----FULL ARCHIVE


SET @TABLENAME =

(SELECT MAX(name)

FROM sys.tables tt 

WHERE REPLACE(REPLACE(REPLACE(REPLACE(tt.Name,'_',''),'dim',''),'ref',''),'fct','') = REPLACE(REPLACE(REPLACE(REPLACE(@TableName,'_',''),'dim',''),'ref',''),'fct','') )



INSERT INTO dbo.ref_DATA_LINEAGE_TRANSACTION_HISTORY

SELECT 

@RequestType as RequestType, 

@TableName as TableName, 

@UserName UserName, 

@JSON as JSON,

GETDATE() as RunDateTime


--------------------------------------------------

SELECT *,@TableName as TableName

INTO #JSON

FROM parseJson (@JSON)

-----------------------------NEED TO DETERMINE THIS BASED ON MORE SPECIFIC DESIGINATION IN JSON STRING ----------------------


--SELECT * FROM #JSON


--DO PK LOOKUP FROM SYS OBJECTS --


DECLARE @IdentifierColumn varchar(55)


SELECT  @IdentifierColumn = COL_NAME(ic.OBJECT_ID,ic.column_id) 

---i.name AS IndexName,

        --OBJECT_NAME(ic.OBJECT_ID) AS TableName,    

FROM    sys.indexes AS i INNER JOIN 

        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID

                                AND i.index_id = ic.index_id

WHERE   i.is_primary_key = 1

AND OBJECT_NAME(ic.OBJECT_ID)  = @TABLENAME


--SET @IdentifierColumn = (SELECT MAX(NAME) FROM #JSON WHERE Element_ID = 1)


DECLARE @IdentifierID varchar(55)

SET @IdentifierID = (SELECT MAX(StringValue) FROM #JSON WHERE NAME = @IdentifierColumn)



-- create a table of ids

SELECT cast(cast(StringValue as float) as int) as ID

INTO #IDENTIFIERS

FROM #JSON 

WHERE name = @IdentifierColumn




----------------------------------------------------------------------------------------

--AUDIT FIELDS--


IF @RequestType = 'CREATE'

BEGIN


INSERT INTO #JSON


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'IsActive' as NAME, '1' as StringValue, 'int' as ValueType, @TableName as TableName


UNION ALL


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'CreatedDate' as NAME, LEFT(CONVERT(VARCHAR, GETDATE(), 121), 28) as StringValue, 'datetime' as ValueType, @TableName as TableName


UNION ALL


SELECT --MAX(element_id) + 1 as elementID, 

0 as sequenceNo, 1 as ParentID, NULL as ObjectId,

'CreatedUser' as NAME, @UserName as StringValue, 'string' as ValueType , @TableName as TableName


END



IF @RequestType = 'UPDATE'

BEGIN


-- update modified date

MERGE #JSON J

USING

(

    (SELECT parent_id, 'ModifiedDate',  LEFT(CONVERT(VARCHAR, GETDATE(), 121), 28) AS Value

        FROM #JSON

        WHERE parent_id IS NOT NULL

        GROUP BY parent_id)

    

) AS NewDate(ParentID, Name, Value)

ON J.parent_id = NewDate.ParentID AND J.NAME = 'ModifiedDate'

WHEN NOT MATCHED THEN

    INSERT (sequenceNo, Parent_id, OBJECT_ID, Name, StringValue, ValueType)

    VALUES (0, NewDate.ParentID, NULL, NewDate.Name, NewDate.Value, 'datetime')

WHEN MATCHED THEN

    UPDATE

    SET StringValue = NewDate.Value, ValueType = 'datetime';


-- update modified user

MERGE #JSON J

USING

(

    (SELECT parent_id, 'ModifiedUser',  @UserName AS Value

        FROM #JSON

        WHERE parent_id IS NOT NULL

        GROUP BY parent_id)

    

) AS NewUser(ParentID, Name, Value)

ON J.parent_id = NewUser.ParentID AND J.NAME = 'ModifiedUser'

WHEN NOT MATCHED THEN

    INSERT (sequenceNo, Parent_id, OBJECT_ID, Name, StringValue, ValueType)

    VALUES (0, NewUser.ParentID, NULL, NewUser.Name, NewUser.Value, 'varchar')

WHEN MATCHED THEN

    UPDATE

    SET StringValue = NewUser.Value, ValueType = 'varchar';


END

------------------------------------------------------------------------



IF @RequestType = 'DELETE'

BEGIN


DECLARE @SQL varchar(MAX)


DECLARE @UpdateString varchar(MAX)


SELECT @UpdateString=STUFF( (   SELECT ', ' + CONVERT(VARCHAR(MAX), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(MAX), StringValue) + '''' 

                            FROM #JSON 

WHERE NAME <> @IdentifierColumn

                            --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')


SET @UpdateString = REPLACE(@UpdateString,'&lt;&gt;','<>')

SET @UpdateString = REPLACE(@UpdateString,'&lt;','<')

SET @UpdateString = REPLACE(@UpdateString,'&gt;','>')


SET @SQL = 'DELETE ' + @TableName + ' '

  --+ 'SET IsActive = 0 '

  + 'WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID



SET @SQL = REPLACE(@SQL,'''NULL''','NULL')


EXEC (@SQL)


END






IF @RequestType = 'UPDATE'

BEGIN



DECLARE @SQL2 varchar(MAX)


DECLARE @UpdateString2 varchar(MAX)



DECLARE @Keys varchar(max) = 

(SELECT STUFF((SELECT DISTINCT ', [' +  [NAME] + ']' 

FROM #JSON 

WHERE Parent_id IS NOT NULL 

AND [NAME] IS NOT NULL 

FOR XML PATH('')), 1, 1, ''))


DECLARE @SelectList varchar(max) = 

(SELECT STUFF((SELECT DISTINCT ', MAX([' +  [NAME] + ']) [' + [Name] +'] '

FROM #JSON 

WHERE Parent_id IS NOT NULL 

AND [NAME] IS NOT NULL 

FOR XML PATH('')), 1, 1, ''))


DECLARE @PivotSql varchar(max) = 

'SELECT * FROM (SELECT ' + @SelectList + ' ' +

'FROM #JSON ' +

'PIVOT (MAX(StringValue) FOR [NAME] in (' + @Keys + ')) as P ' +

'WHERE ValueType <> ''NULL'' GROUP BY Parent_id) as Pivoted 

 WHERE ' + @IdentifierColumn + ' IS NOT NULL'



SELECT @UpdateString2=STUFF( (   SELECT ', T.' + CONVERT(VARCHAR(MAX), NAME) + ' = '  + 'U.' +   CONVERT(VARCHAR(MAX), NAME) 

                            FROM (SELECT DISTINCT Name 

FROM #JSON 

                            --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL) J

                            FOR xml path('')

                        ), 1, 1, '')




SET @UpdateString2 = REPLACE(@UpdateString2,'&lt;&gt;','<>')

SET @UpdateString2 = REPLACE(@UpdateString2,'&lt;','<')

SET @UpdateString2 = REPLACE(@UpdateString2,'&gt;','>')



SET @SQL2 = 'UPDATE T' +

+ ' SET ' + @UpdateString2

+ ' FROM ' + @TableName + ' T '

+ ' INNER JOIN (' + @PivotSql + ') U'

+ ' ON T.' + @IdentifierColumn + ' = ' + 'U.' + @IdentifierColumn



--SET @SQL2 = REPLACE(@SQL2,'''NULL''','NULL')


EXEC (@SQL2)


IF @TableName = 'ref_LIQUIDITY_OWNERSHIP'

BEGIN

DECLARE @CreateDT DATETIME = GETDATE()

INSERT INTO [dbo].[ref_LIQUIDITY_PMBUCKET_CHARACTERISTICS]

(TraderID, 

TraderCode, 

PMBucketGroupID, 

PMBucketGroupCode, 

LiquidityCharacteristicGroupID, 

LiquidityCharacteristicGroupCode,

LiquidityCharacteristicID, 

LiquidityCharacteristicCode,

LiquidityCharacteristicOrderNo, 

ReportBucketTypeID,

ReportBucketTypeCode,

KnowledgeStartDate,

KnowledgeEndDate,

IsActive,

CreatedDate,

CreatedUser)

SELECT DISTINCT DT.TraderID, 

RLO.TraderCode, 

DPG.PMBucketGroupID, 

RLO.PMBucketGroupCode , 

DLCG.LiquidityCharacteristicGroupID, 

DLCG.LiquidityCharacteristicGroupCode,

DLC.LiquidityCharacteristicID, 

DLC.LiquidityCharacteristicCode,

LiquidityCharacteristicOrderNo = 1, 

DRBT.ReportBucketTypeID,

DRBT.ReportBucketTypeCode,

KnowledgeStartDate = '1/1/2017',

KnowledgeEndDate = '1/1/2050',

IsActive = 1,

CreatedDate = @CreateDT,

CreatedUser = SYSTEM_USER

FROM [dbo].ref_LIQUIDITY_OWNERSHIP RLO

LEFT JOIN (SELECT DISTINCT LiquidityCharacteristicID, PMBucketGroupID 

FROM [dbo].[ref_LIQUIDITY_PMBUCKET_CHARACTERISTICS]

WHERE LiquidityCharacteristicGroupCode = 'Default') RMC 

ON RLO.PMBucketGroupID = RMC.PMBucketGroupID

LEFT JOIN dim_TRADER DT ON DT.TraderID = RLO.TraderID

LEFT JOIN dim_PMBUCKET_GROUP DPG ON DPG.PMBucketGroupID = RLO.PMBucketGroupID

LEFT JOIN dim_LIQUIDITY_CHARACTERISTIC_GROUP DLCG ON DLCG.LiquidityCharacteristicGroupCode = 'Default'

LEFT JOIN dim_LIQUIDITY_CHARACTERISTIC DLC ON DLC.LiquidityCharacteristicCode = 'Default'

LEFT JOIN dim_REPORT_BUCKET_TYPE DRBT ON DRBT.ReportBucketTypeCode = 'Default'

WHERE RMC.LiquidityCharacteristicID IS NULL

AND GETDATE() BETWEEN RLO.KnowledgeStartDate AND RLO.KnowledgeEndDate

AND RLO.PMBucketGroupCode <> 'Unassigned'

END



--SELECT @SQL2


--RETURN VALUES BACK--


DECLARE @SQLReturn2 varchar(MAX)


SET @SQLReturn2 = 

  'SELECT * FROM ' + @TableName + 

  + ' join #IDENTIFIERS i on i.ID = ' + @TableName + '.' + @IdentifierColumn


EXEC (@SQLReturn2)


END




IF @RequestType = 'CREATE'

BEGIN


DECLARE @SQL3 varchar(MAX)


DECLARE @ValuesString varchar(MAX)


DECLARE @CreateString varchar(MAX)


--SELECT * 

--FROM #JSON


SELECT @ValuesString=STUFF( (   SELECT ', ' +   CONVERT(VARCHAR(MAX), NAME) 

                            FROM #JSON 

                            --WHERE 1=1--

    --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')



SET @ValuesString = REPLACE(@ValuesString,'&lt;&gt;','<>')

SET @ValuesString = REPLACE(@ValuesString,'&lt;','<')

SET @ValuesString = REPLACE(@ValuesString,'&gt;','>')

-------------------------------


SELECT @CreateString=STUFF( (   SELECT ', ' + '''' +   CONVERT(VARCHAR(MAX), StringValue) + '''' 

                            FROM #JSON 

                            --WHERE 1=1--

    --WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

WHERE NAME <> @IdentifierColumn

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')


SET @CreateString = REPLACE(@CreateString,'&lt;&gt;','<>')

SET @CreateString = REPLACE(@CreateString,'&lt;','<')

SET @CreateString = REPLACE(@CreateString,'&gt;','>')



-------------------------------



SET @SQL3 = 'INSERT INTO ' + @TableName + ' '

  + '(' + 

  CASE WHEN @TableName <> 'ref_DIG_RULES_ENGINE' THEN @ValuesString

  ELSE @ValuesString + ', ' +  'ReportFilterSQL' END 

  + ')'

  + 'SELECT '

  + 

  CASE WHEN @TableName <> 'ref_DIG_RULES_ENGINE' THEN @CreateString

  ELSE @CreateString + ', ' +  '''' + (SELECT ReportFilterSQL FROM [GI_MASTER].[dbo].[dim_REPORT_FILTER] 

          WHERE ReportFilterID = 5) + '''' END 


--SET @SQL3 = 'INSERT INTO ' + @TableName + ' '

--   + '(' + @ValuesString + ')'

--   + 'SELECT '

--   + @CreateString


--SELECT @@ValuesString

--SELECT @CreateString


--SELECT @SQL3


SET @SQL3 = REPLACE(@SQL3,'''NULL''','NULL')


EXEC (@SQL3) 


DECLARE @SQLReturn3 varchar(MAX)


SET @SQLReturn3 = 

'SELECT * FROM ' + @TableName + 

  + ' WHERE ' + @IdentifierColumn + ' = ' + CONVERT(varchar(MAX),@@IDENTITY)


--SELECT @SQLReturn3

EXEC (@SQLReturn3)


--set @key = @@IDENTITY


--SELECT @key


END



--CREATE,UPDATE,DELETE


--dim_ASSET_TYPE

--wanlink\PBHung

--{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-09-28T18:12:37.597-00:00","CreatedUser":"WANLINK\PBHung","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}


--dim_ASSET_TYPE

--wanlink\PBHung

--1

--{"AssetTypeCode":"Asset Type Code Changed","AssetTypeName":"Asset Type Name Changed","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}


--dim_ASSET_TYPE

--wanlink\PBHung

--1

--{"IsActive":true,"ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}



--INSERT INTO stg_GI_CRUD

--SELECT @RequestType , @TableName, @UserName, @JSON



--EXEC GI_MASTER.dbo.[etl_BACKFILL_DAILY_BRS_ANALYTICS_ST] '2/1/2016', '10/07/2016', 0

--EXEC GI_MASTER.dbo.pvt_ANALYTICS_BUILD '2/1/2016'  


--SELECT @key


END







GO

/****** Object:  StoredProcedure [dbo].[etl_DATA_LINEAGE_CRUD_BULK]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[etl_DATA_LINEAGE_CRUD_BULK] 

(@RequestType varchar(255), @TableName varchar(255), @UserName varchar(255), @JSON varchar(MAX), @ClaimsKey varchar(MAX)=NULL)

AS

BEGIN


SET @TABLENAME =

(SELECT MAX(name)

FROM sys.tables tt 

WHERE REPLACE(REPLACE(REPLACE(REPLACE(tt.Name,'_',''),'dim',''),'ref',''),'fct','') = REPLACE(REPLACE(REPLACE(REPLACE(@TableName,'_',''),'dim',''),'ref',''),'fct','') )


SET @JSON = REPLACE(@JSON,'""','null')


---LOGGING---

INSERT INTO dbo.ref_DATA_LINEAGE_TRANSACTION_HISTORY

SELECT 

@RequestType as RequestType, 

@TableName as TableName, 

@UserName UserName, 

@JSON as JSON,

GETDATE() as RunDateTime



-- Get the PK column

DECLARE @IdentifierColumn varchar(55)


SELECT  @IdentifierColumn = COL_NAME(ic.OBJECT_ID,ic.column_id) 

---i.name AS IndexName,

        --OBJECT_NAME(ic.OBJECT_ID) AS TableName,    

FROM    sys.indexes AS i INNER JOIN 

        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID

                                AND i.index_id = ic.index_id

WHERE   i.is_primary_key = 1

AND OBJECT_NAME(ic.OBJECT_ID)  = @TABLENAME


-- Column Names and Data Types

SELECT DISTINCT c.name as ColumnName,   t.Name as DataType,

   c.max_length 'MaxLength'

INTO #WITH_PREP

FROM    

    sys.columns c

INNER JOIN 

    sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN 

    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

LEFT OUTER JOIN 

    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

INNER JOIN 

sys.tables tt ON tt.object_id = c.object_id

WHERE

    c.object_id = OBJECT_ID(@TableName)



-- Get only those fields present in the uploaded JSON

SELECT DISTINCT [Key]

INTO #JSON_FIELDS

FROM OPENJSON (

(

SELECT TOP 1 Value

FROM OPENJSON(@JSON)

))


DELETE FROM #JSON_FIELDS

WHERE [Key] in ('ModifiedDate', 'CreatedDate', 'ModifiedUser', 'CreatedUser', 'IsActive')


-- Remove fields not present in the uploaded JSON

DELETE P

FROM #WITH_PREP P

LEFT OUTER JOIN #JSON_FIELDS F ON F.[Key] = P.ColumnName COLLATE Latin1_General_CI_AS 

WHERE F.[Key] IS NULL


-- Parse JSON

DECLARE @QUERY nvarchar(MAX) = N'SELECT *

INTO #JSON

FROM OPENJSON(''' + @JSON + ''')' +

'WITH (' +

(SELECT STUFF(( SELECT ',' + ColumnName + ' ' + DataType + ' ' + case when DataType = 'varchar' then '(' + cast(MaxLength as varchar) + ') ' else ' ' end + '''$.' +  ColumnName + ''''

FROM #WITH_PREP

FOR XML PATH('')), 1, 1, '')) +

');'


-- Remove the Identifier Column so we don't attempt to update it

DELETE FROM #JSON_FIELDS

WHERE [Key] = @IdentifierColumn COLLATE Latin1_General_CI_AS 


DECLARE @SQL VARCHAR(MAX)


-- BULK INSERT

IF @RequestType = 'CREATE'

BEGIN


DECLARE @Fields VARCHAR(MAX) = (SELECT STUFF((SELECT DISTINCT ',' + ColumnName 

FROM #WITH_PREP

WHERE ColumnName <> @IdentifierColumn

FOR XML PATH('')), 1, 1, ''))


SELECT getdate() as CreatedDate, @UserName as CreatedUser, 1 as IsActive INTO #AUDITING


SET @SQL = 

'INSERT INTO ' + @TableName + 

' (' + @Fields + ', CreatedDate, CreatedUser, IsActive)' +

' OUTPUT INSERTED.*' + 

' SELECT ' + @Fields + ', CreatedDate, CreatedUser, IsActive FROM #JSON CROSS JOIN #AUDITING'


END


-- BULK UPDATE

IF @RequestType = 'UPDATE'

BEGIN

DECLARE @UpdateString2 varchar(MAX) = STUFF(

(

SELECT ', Existing.' + QUOTENAME([Key]) + ' = '  + 'New.' +   QUOTENAME([Key])

FROM #JSON_FIELDS

FOR XML PATH('')

), 1, 1, '')


SET @SQL = 'UPDATE Existing' +

+ ' SET ' + @UpdateString2 + ', '

+ ' ModifiedDate = LEFT(CONVERT(VARCHAR, GETDATE(), 121), 28),'

+ ' ModifiedUser = ''' + @UserName + ''''

+ ' FROM ' + @TableName + ' Existing '

+ ' INNER JOIN #JSON New'

+ ' ON Existing.' + @IdentifierColumn + ' = ' + 'New.' + @IdentifierColumn

+ '; '

+ 'SELECT T.* FROM ' + @TableName + ' T JOIN #JSON J ON J.' + @IdentifierColumn + ' = ' + 'T.' + @IdentifierColumn


END


exec (@QUERY + '; ' +  @SQL)


END





GO

/****** Object:  StoredProcedure [dbo].[etl_DATA_LINEAGE_CRUD_SINGLE]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO




-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'DELETE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID": 3,"IsActive": false,"ModifiedUser": "WANLINK\pbhung","ModifiedDate": "2016-10-17T21:47:24Z"}'


-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'UPDATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID": 3, "AssetTypeCode": "Asset Type Code Changed", "AssetTypeName": "Asset Type Name Changed", "ModifiedUser": "WANLINK\pbhung", "ModifiedDate": "2016-10-17T21:16:35Z"}'


-- exec [dbo].[etl_DATA_LINEAGE_CRUD] 'CREATE','dim_ASSET_TYPE','WANLINK\pbhung', '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


CREATE PROCEDURE [dbo].[etl_DATA_LINEAGE_CRUD_SINGLE] 

(@RequestType varchar(255), @TableName varchar(255), @UserName varchar(255), @JSON varchar(8000)=NULL)


AS

BEGIN


--tableName varchar

--userName varchar

--json varchar


--DECLARE @JSON varchar(8000)

--DECLARE @TableName varchar(255)


--SET @TableName = 'dim_ASSET_TYPE'


----@RequestType=CREATE--

----SET @JSON  = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-09-28T18:12:37.597-00:00","CreatedUser":"WANLINK\PBHung","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-10-17T21:45:41Z","CreatedUser":"WANLINK\pbhung","ModifiedDate":"2016-10-17T21:45:41Z","ModifiedUser":"WANLINK\pbhung"}'


----@RequestType=UPDATE--

----SET @JSON  = '{"AssetTypeCode":"Asset Type Code Changed","AssetTypeName":"Asset Type Name Changed","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "AssetTypeCode": "Asset Type Code Changed",

--  "AssetTypeName": "Asset Type Name Changed",

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:16:35Z"

--}'


----@RequestType=DELETE--

----SET @JSON = '{"IsActive":false,"ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}'

--SET @JSON = '{

--  "AssetTypeID": 3,

--  "IsActive": false,

--  "ModifiedUser": "WANLINK\pbhung",

--  "ModifiedDate": "2016-10-17T21:47:24Z"

--}'



SELECT *,@TableName as TableName

INTO #JSON

FROM parseJson (@JSON)


-----------------------------NEED TO DETERMINE THIS BASED ON MORE SPECIFIC DESIGINATION IN JSON STRING ----------------------


--SELECT * FROM #JSON


--DO PK LOOKUP FROM SYS OBJECTS --


DECLARE @IdentifierColumn varchar(55)

SET @IdentifierColumn = (SELECT MAX(NAME) FROM #JSON WHERE Element_ID = 1)


DECLARE @IdentifierID varchar(55)

SET @IdentifierID = (SELECT MAX(StringValue) FROM #JSON WHERE Element_ID = 1)



----------------------------------------------------------------------------------------



IF @RequestType = 'DELETE'

BEGIN


DECLARE @SQL varchar(MAX)


DECLARE @UpdateString varchar(MAX)


SELECT @UpdateString=STUFF( (   SELECT ', ' + CONVERT(VARCHAR(500), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(500), StringValue) + '''' 

                            FROM #JSON 

                            WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')



SET @SQL = 'UPDATE ' + @TableName + ' '

  --+ 'SET IsActive = 0 '

  + 'SET ' + @UpdateString

  + 'WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID


SET @SQL = REPLACE(@SQL,'''NULL''','NULL')

--SELECT @UpdateString


EXEC (@SQL)


END






IF @RequestType = 'UPDATE'

BEGIN


DECLARE @SQL2 varchar(MAX)


DECLARE @UpdateString2 varchar(MAX)


--SELECT * 

--FROM #JSON


SELECT @UpdateString2=STUFF( (   SELECT ', ' + CONVERT(VARCHAR(500), NAME) + ' = '  + '''' +   CONVERT(VARCHAR(500), StringValue) + '''' 

                            FROM #JSON 

                            WHERE Element_ID <> 1 --NAME NOT LIKE '%ID' 

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')



SET @SQL2 = 'UPDATE ' + @TableName + ' '

-- + 'SET IsActive = 1 , '

  + 'SET ' + @UpdateString2

  + ' WHERE ' + @IdentifierColumn + ' = ' + @IdentifierID


SET @SQL2 = REPLACE(@SQL2,'''NULL''','NULL')

--SELECT @UpdateString2

EXEC (@SQL2)


END




IF @RequestType = 'CREATE'

BEGIN


DECLARE @SQL3 varchar(MAX)


DECLARE @ValuesString varchar(MAX)


DECLARE @CreateString varchar(MAX)


--SELECT * 

--FROM #JSON


SELECT @ValuesString=STUFF( (   SELECT ', ' +   CONVERT(VARCHAR(500), NAME) 

                            FROM #JSON 

                            WHERE 1=1

AND Element_ID <> 1

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')


-------------------------------


SELECT @CreateString=STUFF( (   SELECT ', ' + '''' +   CONVERT(VARCHAR(500), StringValue) + '''' 

                            FROM #JSON 

                            WHERE 1=1--

AND Element_ID <> 1 

AND parent_ID IS NOT NULL

                            FOR xml path('')

                        ), 1, 1, '')

-------------------------------


SET @SQL3 = 'INSERT INTO ' + @TableName + ' '

  + '(' + @ValuesString + ')'

  + 'SELECT '

  + @CreateString


--SELECT @@ValuesString

--SELECT @CreateString


--SELECT @SQL3


SET @SQL3 = REPLACE(@SQL3,'''NULL''','NULL')


EXEC (@SQL3) 


END



--CREATE,UPDATE,DELETE


--dim_ASSET_TYPE

--wanlink\PBHung

--{"AssetTypeID":0,"AssetTypeCode":"Test Code","AssetTypeName":"Fake Asset Type","AssetTypeDisplayName":"Fake Asset Type","IsActive":true,"CreatedDate":"2016-09-28T18:12:37.597-00:00","CreatedUser":"WANLINK\PBHung","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}


--dim_ASSET_TYPE

--wanlink\PBHung

--1

--{"AssetTypeCode":"Asset Type Code Changed","AssetTypeName":"Asset Type Name Changed","ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}


--dim_ASSET_TYPE

--wanlink\PBHung

--1

--{"IsActive":true,"ModifiedDate":"2016-09-28T18:12:37.597-00:00","ModifiedUser":"WANLINK\PBHung"}



--INSERT INTO stg_GI_CRUD

--SELECT @RequestType , @TableName, @UserName, @JSON



--EXEC GI_MASTER.dbo.[etl_BACKFILL_DAILY_BRS_ANALYTICS_ST] '2/1/2016', '10/07/2016', 0

--EXEC GI_MASTER.dbo.pvt_ANALYTICS_BUILD '2/1/2016'  


END




GO

/****** Object:  StoredProcedure [dbo].[etl_DERIVATIVE_REPORT_FUND]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Levin Liu>

-- Create date: <2018-06-28>

-- Description: <Update fund report in [ref_DERIVATIVE_REPORT_FUND]>

-- =============================================


CREATE PROCEDURE [dbo].[etl_DERIVATIVE_REPORT_FUND]

@ASOFDATE DATETIME = NULL

AS

BEGIN


SET NOCOUNT ON;

/****** Generate Report  ******/

select distinct BoardBookName, BRSAccount, ExposureType, InstrumentType, Strategy, NotionalPCT, UnrealizedGainLossPCT

into #tempresult

from [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_INSTRUMENT]

where BoardBookDate = @ASOFDATE


select @ASOFDATE AS BoardBookDate, BoardBookName, BRSAccount, ExposureType, InstrumentType, Strategy, 

CASE WHEN Strategy like '%spread%' THEN SUM(ABS(NotionalPCT))

ELSE SUM(NotionalPCT) END ExposureCurrent, SUM(UnrealizedGainLossPCT) AS PnLCurrent

into #result

from #tempresult

group by BRSAccount, BoardBookName, ExposureType, InstrumentType, Strategy

order by BoardBookName asc, ExposureType asc, InstrumentType asc, Strategy asc


select * 

into #prev

from [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_FUND] 

where BoardBookDate = (select top (1) BoardBookDate from [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_FUND] where BoardBookDate < @ASOFDATE)



select res.BoardBookDate, res.BoardBookName, res.BRSAccount, res.ExposureType, res.InstrumentType, res.Strategy, 

CASE WHEN pre.ExposureCurrent is null THEN 0

ELSE pre.ExposureCurrent END ExposurePrevious, res.ExposureCurrent, 

CASE WHEN pre.ExposureCurrent is null THEN res.ExposureCurrent

ELSE (res.ExposureCurrent-pre.ExposureCurrent) END ExposureChange, res.PnLCurrent

from #result res

left outer join #prev pre on res.BRSAccount = pre.BRSAccount and res.ExposureType = pre.ExposureType and res.InstrumentType = pre.InstrumentType and res.Strategy = pre.Strategy 


INSERT INTO [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_FUND] (BoardBookDate, BoardBookName, BRSAccount, ExposureType, InstrumentType, Strategy, ExposureCurrent, PnLCurrent)

SELECT * from #result


/****** Cleanup  ******/

drop table #result

drop table #tempresult

drop table #prev


END


GO

/****** Object:  StoredProcedure [dbo].[etl_DERIVATIVE_REPORT_INSTRUMENT]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Levin Liu>

-- Create date: <2018-06-28>

-- Description: <Update instrument list in [ref_DERIVATIVE_REPORT_INSTRUMENT]>

-- =============================================


--DECLARE @ASOFDATE DATETIME = '2018-06-29 00:00:00.000'

--DECLARE @DATE VARCHAR(50) = '2018-06-29'


CREATE PROCEDURE [dbo].[etl_DERIVATIVE_REPORT_INSTRUMENT]

@DATE VARCHAR(50) = NULL,

@ASOFDATE DATETIME = NULL


AS

BEGIN

SET NOCOUNT ON;


/****** GetFundNAV  ******/

SELECT distinct [Date]

  ,[Fund]

  ,[Class]

  ,[NetAssets]

  ,[UnitsOutstanding]

  ,[StatusId]

  into #temp

  FROM [GI_STAGING].[dbo].[stg_INVESTONE_FUNDNAV]

  where date = @ASOFDATE and statusid = (select max(StatusId) from [GI_STAGING].[dbo].[stg_INVESTONE_FUNDNAV] where date = @ASOFDATE)

  order by fund asc


Select Fund, sum(NetAssets) AS NAV

into #NAV

from #temp

group by Fund

order by fund asc


Insert into #NAV (Fund, NAV)

Values ('3102', (select NAV from #NAV where fund = 3002))

Insert into #NAV (Fund, NAV)

Values ('3202', (select NAV from #NAV where fund = 3002))

Insert into #NAV (Fund, NAV)

Values ('1102', (select NAV from #NAV where fund = 1002))

Insert into #NAV (Fund, NAV)

Values ('1210', (select NAV from #NAV where fund = 1010))


/****** Update Notional for Options  ******/

SELECT distinct accountcode,securityname, cusip, quantity, net_notional, TAG_option_delta,

  CASE WHEN left(cusip,1) = 'B' THEN Net_Notional/TAG_option_delta

  ELSE net_notional*0.25/TAG_option_delta END CorrectNotional

  into #cor

  FROM [GI_MASTER].[dbo].[fct_HOLDINGS_ACCOUNT_PIVOT_VALUE] ha

  join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] am on ha.AccountID = am.AccountID

  join [GI_MASTER].[dbo].[ref_SECURITY_MASTER_PIVOT] sm on ha.SecurityID = sm.SecurityID

  join [GI_STAGING].[dbo].[stg_BRS_ALD_ANALYTICS] brs on brs.TAG_sm_cusip = sm.BlackrockCusip and brs.TAG_portf_list = am.PortfolioCode and (CAST(YEAR(brs.as_of) AS bigint) * 100 + MONTH(brs.as_of)) * 100 + DAY(brs.as_of) = ha.PeriodEndDate

  where brs.as_of = @ASOFDATE and securitytype like '%Option' and am.boardbookindicator = 1 and sourceid = 1006 

  order by accountcode asc, securityname asc


SELECT ieh.* into #ieh

FROM [GI_STAGING].[dbo].[stg_INVESTONE_ENHANCED_HOLDINGS] ieh

inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

where date = @DATE

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW','XX') 

and MarketValue <> 0

and amp.BoardBookIndicator = 1

order by assetgroup asc, account asc


delete from #ieh

where AssetGroup = 'XX' and Price = 0


update #ieh

set notional = cor.CorrectNotional

from 

#ieh ieh

inner join #cor cor 

on ieh.cusip = cor.cusip and round(ieh.shares,0,1) = cor.quantity



/****** GetDerivativePositions  ******/

SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account, ieh.AssetGroup, #NAV.NAV,

  CASE WHEN ieh.AssetGroup = 'CC' THEN ieh.Currency 

  ELSE ieh.CUSIP END AssetID, 

  CASE WHEN ieh.AssetGroup = 'CC' THEN CONCAT(ieh.Currency, '_', ieh.Cusip)

  ELSE ieh.Name END Description, 

  ieh.Shares, ieh.Price, 

  CASE WHEN ieh.AssetGroup in ('DW','IW') THEN ieh.Shares 

  ELSE ieh.Notional END Notional, ieh.MarketValue,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  INTO #Instrument

  FROM #ieh ieh    

  inner join #NAV on ieh.Account = #NAV.Fund

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description) and round(ieh.Shares,0,1) = drs.Shares--and ieh.CostUSD = drs.CostUSD

  where ieh.date=@DATE

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW') 

and MarketValue <> 0

--and Price <> 1E-06

and amp.BoardBookIndicator = 1

and not(BRSAccount = 'GIO' and Currency in ('EUR', 'GBP', 'JPY') and ieh.AssetGroup = 'CC')

  order by ieh.account asc, assetid asc


INSERT INTO #Instrument

SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account, ieh.AssetGroup, #NAV.NAV,

  CASE WHEN ieh.AssetGroup = 'CC' THEN ieh.Currency 

  ELSE ieh.CUSIP END AssetID, 

  CASE WHEN ieh.AssetGroup = 'CC' THEN CONCAT(ieh.Currency, '_', ieh.Cusip)

  ELSE ieh.Name END Description, 

  ieh.Shares, ieh.Price, 

  CASE WHEN ieh.AssetGroup in ('DW','IW') THEN ieh.Shares 

  ELSE ieh.Notional END Notional, ieh.MarketValue,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  FROM #ieh ieh

  inner join #NAV on ieh.Account = #NAV.Fund

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description) and round(ieh.Shares,0,1) = drs.Shares--and ieh.CostUSD = drs.CostUSD

  where ieh.date= @DATE 

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW') 

and MarketValue <> 0

--and Price <> 1E-06

and amp.BoardBookIndicator = 1

and (BRSAccount = 'GIO' and Currency in ('EUR', 'GBP', 'JPY') and ieh.AssetGroup = 'CC')

  order by ieh.account asc, assetid asc

 

INSERT INTO #Instrument

SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account, ieh.AssetGroup, #NAV.NAV,

  CASE WHEN ieh.AssetGroup = 'CC' THEN ieh.Currency 

  ELSE ieh.CUSIP END AssetID, 

  CASE WHEN ieh.AssetGroup = 'CC' THEN CONCAT(ieh.Currency, '_', ieh.Cusip)

  ELSE ieh.Name END Description, 

  ieh.Shares, ieh.Price, 

  CASE WHEN ieh.AssetGroup in ('DW','IW') THEN ieh.Shares 

  ELSE ieh.Notional END Notional, ieh.MarketValue,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  FROM #ieh ieh

  inner join #NAV on ieh.Account = #NAV.Fund

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description) and round(ieh.Shares,0,1) = drs.Shares--and ieh.CostUSD = drs.CostUSD

  where ieh.date= @DATE 

and ieh.AssetGroup = 'XX'

and Price <> 0

and amp.BoardBookIndicator = 1

  order by ieh.account asc, assetid asc 


/****** Get Final Instrument List  ******/

Select i.Date, fn.BoardBookName, BRSAccount, AssetID, Description, Shares, Price, Notional, Notional/NAV AS NotionalPCT, MarketValue AS UnrealizedGainLoss, MarketValue/NAV AS UnrealizedGainLossPCT, DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

into #result

from #Instrument i

inner join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_FUND_NAME] fn on i.BRSAccount = fn.AccountCode

order by BRSAccount asc, AssetID asc


INSERT INTO [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_INSTRUMENT] (BoardBookDate, BoardBookName, BRSAccount, AssetName, Description, Shares, Price, Notional, NotionalPCT, UnrealizedGainLoss, UnrealizedGainLossPCT, ExposureType, InstrumentType, Strategy)

SELECT * from #result


/****** Cleanup  ******/

drop table #ieh

drop table #cor

drop table #temp

drop table #NAV

drop table #Instrument

drop table #result




END


GO

/****** Object:  StoredProcedure [dbo].[etl_DERIVATIVE_REPORT_STRATEGY]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Levin Liu>

-- Create date: <2018-06-28>

-- Description: <Update strategy map in [ref_DERIVATIVE_REPORT_STRATEGY_MAP]>

-- =============================================


CREATE PROCEDURE [dbo].[etl_DERIVATIVE_REPORT_STRATEGY]

@DATE VARCHAR(50) = NULL


AS

BEGIN

SET NOCOUNT ON;


SELECT drs.MappingID

into #EXISTING

from [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs

inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on drs.InvestOneAccountNumber = iam.InvestOneAccountNumber

inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

left outer join [GI_STAGING].dbo.[stg_INVESTONE_ENHANCED_HOLDINGS] ieh on ieh.Account = drs.InvestOneAccountNumber and round(ieh.Shares,0,1) = drs.Shares and ieh.AssetGroup = drs.AssetGroup and ieh.CostUSD = drs.CostUSD

where ieh.date= @DATE 

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW') 

and MarketValue <> 0

--and Price <> 1E-06

and amp.BoardBookIndicator = 1

order by drs.MappingID


-- Need to set IsActive = 0 for swaps in 'XX'


--update [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP]

--set IsActive = 0 

--where MappingID in (select mappingid 

-- from [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP]

-- where mappingid not in (select * from #EXISTING))

-- and IsActive = 1


drop table #EXISTING


--------------------


SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account, 

  ieh.CUSIP, ieh.Name, ieh.AssetGroup, ieh.Currency, ieh.Shares, ieh.CostUSD,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  INTO #instrument

  FROM [GI_STAGING].[dbo].[stg_INVESTONE_ENHANCED_HOLDINGS] ieh

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description)

  where ieh.date= @DATE 

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW') 

and MarketValue <> 0

--and Price <> 1E-06

and amp.BoardBookIndicator = 1

and not(iam.BRSAccount = 'GIO' and ieh.Currency in ('EUR', 'GBP', 'JPY') and ieh.AssetGroup = 'CC')

  order by ieh.account asc


INSERT INTO #instrument

SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account,

  ieh.CUSIP, ieh.Name, ieh.AssetGroup, ieh.Currency, ieh.Shares, ieh.CostUSD,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  FROM [GI_STAGING].[dbo].[stg_INVESTONE_ENHANCED_HOLDINGS] ieh

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description)

  where ieh.date= @DATE 

and ieh.AssetGroup in ('CC','DW','FT','IW','O','TW') 

and MarketValue <> 0

--and Price <> 1E-06

and amp.BoardBookIndicator = 1

and (BRSAccount = 'GIO' and Currency in ('EUR', 'GBP', 'JPY') and ieh.AssetGroup = 'CC')

  order by ieh.account asc


INSERT INTO #Instrument

SELECT distinct ieh.Date, amp.PortfolioPM, iam.BRSAccount, ieh.Account,

  ieh.CUSIP, ieh.Name, ieh.AssetGroup, ieh.Currency, ieh.Shares, ieh.CostUSD,

  DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode

  FROM [GI_STAGING].[dbo].[stg_INVESTONE_ENHANCED_HOLDINGS] ieh

  inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on ieh.Account = iam.InvestOneAccountNumber

  inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

  left outer join [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] drs on ieh.Account = drs.InvestOneAccountNumber and (ieh.CUSIP = drs.AssetName or CONCAT(ieh.Currency, '_', ieh.Cusip) = drs.Description)

  where ieh.date= @DATE 

and ieh.AssetGroup = 'XX'

and Price <> 0

and amp.BoardBookIndicator = 1

  order by ieh.account asc



select @DATE AS BoardBookDate, t.Account AS InvestOneAccountNumber, amp.AccountID, amp.AccountCode, amp.PortfolioID, amp.PortfolioCode,

pm.PortfolioManagerID, pm.PortfolioManagerCode, 

CASE WHEN t.AssetGroup = 'CC' THEN t.Currency 

ELSE t.CUSIP END AssetName, 

CASE WHEN t.AssetGroup = 'CC' THEN CONCAT(t.Currency, '_', t.Cusip)

ELSE t.Name END Description, t.Shares AS Shares,

t.CostUSD, t.AssetGroup, 'UNASSIGNED' AS DerivativeExposureTypeCode, 'UNASSIGNED' AS DerivativeInstrumentTypeCode, 'UNASSIGNED' AS DerivativeStrategyCode,

0 AS IsActive, GETDATE() AS CreatedDate, 'WANLINK\XILiu' AS CreatedUser

--into #newpos 

from #Instrument t

inner join [GI_MASTER].[dbo].[ref_INVESTONE_ACCOUNT_MAP] iam on t.Account = iam.InvestOneAccountNumber

inner join [GI_MASTER].[dbo].[ref_ACCOUNT_MASTER_PIVOT] amp on iam.BRSAccount = amp.PortfolioCode

inner join [GI_MASTER].[dbo].[dim_PORTFOLIO_MANAGER] pm on amp.PortfolioPM = pm.PortfolioManagerCode

where amp.AccountCode <> 'GOF 07/07' and t.DerivativeExposureTypeCode is null

order by AccountCode asc, AssetName asc


--INSERT INTO [GI_MASTER].[dbo].[ref_DERIVATIVE_REPORT_STRATEGY_MAP] (BoardBookDate, InvestOneAccountNumber, AccountID, AccountCode, PortfolioID, PortfolioCode, PortfolioManagerID, PortfolioManagerCode, AssetName, Description, Shares, CostUSD, AssetGroup, DerivativeExposureTypeCode, DerivativeInstrumentTypeCode, DerivativeStrategyCode, IsActive, CreatedDate, CreatedUser)

--SELECT * from #newpos


drop table #instrument

--drop table #newpos


END


GO

/****** Object:  StoredProcedure [dbo].[etl_EQDB_V_HOLDINGS_VALUE_BUILD]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO




-- [etl_EQDB_V_HOLDINGS_VALUE_BUILD] '8/1/2016'


CREATE PROCEDURE [dbo].[etl_EQDB_V_HOLDINGS_VALUE_BUILD]

(@RunDate datetime = NULL, @PortfolioCode varchar(255)= '*',@SkipSecMaster int = 0)


AS

BEGIN


------------------------------------------------------------------


--DECLARE @RunDate datetime

--SET @RunDate = '2016-06-29'

--DECLARE @PortfolioCode varchar(255)

--SET @PortfolioCode = '*'



--HANDLE DATE PARAMTER FORMATTING--


DECLARE @PeriodEndDate int

SET @PeriodEndDate = CONVERT(int,CONVERT(varchar(12),@RunDate,112))


DECLARE @RunDateT2 date

SET @RunDateT2 = (SELECT MIN(DateTime)

  FROM dim_DATE

  WHERE TradeDateInd = 1

  AND DateTime > @RunDate)



----HANDLE INCREMENTAL MANAGER RERUN--

DECLARE @PortfolioId int 

SET @PortfolioId = ISNULL((SELECT PortfolioID FROM dim_PORTFOLIO WHERE PortfolioCode = @PortfolioCode),0) 


-----RUN SECURITY MASTER------------

IF @SkipSecMaster = 0

BEGIN

PRINT 'START=' + CONVERT(VARCHAR, GETDATE(),109) 

exec [etl_MULTI_SECURITY_MASTER_BUILD] @RunDate, 'Equity Holdings'

PRINT 'SECMASTER COMPLETE=' + CONVERT(VARCHAR, GETDATE(),109) 

END

------------------------------------------------------------------

--RAW PULL FROM EQ V_HOLDINGS-

SELECT A.*,

CASE WHEN idType = 'custom' 

  AND id like 'LX%' THEN 'LoanXID'

  ELSE idType END as SecurityWaterfallField,

  CONVERT(float,[marketvalueUSD]) as TAG_mkt_value,  

  CONVERT(Float,[shares]) as TAG_cur_face,

  @PeriodEndDate as PeriodEndDate,

  CASE WHEN odsfundid like '01%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (BONY)')

  WHEN odsfundid like '02%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (PAM)')

  WHEN odsfundid like '03%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (Invest One)')

  ELSE 000 END as SourceID

INTO #RAW_EQ

FROM GI_STAGING.[dbo].[stg_EQDB_V_HOLDINGS] A

WHERE Date = @RunDate

AND odsfundid not like '03%' 



INSERT INTO #RAW_EQ

SELECT A.*,

CASE WHEN idType = 'custom' 

  AND id like 'LX%' THEN 'LoanXID'

  ELSE idType END as SecurityWaterfallField,

  CONVERT(float,[marketvalueUSD]) as TAG_mkt_value,  

  CONVERT(Float,[shares]) as TAG_cur_face,

  @PeriodEndDate as PeriodEndDate,

  CASE WHEN odsfundid like '01%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (BONY)')

  WHEN odsfundid like '02%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (PAM)')

  WHEN odsfundid like '03%' 

  THEN  (SELECT SourceID FROM GI_MASTER..dim_SOURCE WHERE SourceName = 'Equity Holdings (Invest One)')

  ELSE 000 END as SourceID

FROM GI_STAGING.[dbo].[stg_EQDB_V_HOLDINGS] A

WHERE Date = @RunDateT2

    AND odsfundid like '03%' 


--CLEANING UP BLANK FIELDS--

UPDATE #RAW_EQ 

SET sedol = NULLIF(sedol,''), isin = NULLIF(isin,''), cusip = NULLIF(cusip,'')


---LOGGING

PRINT '#RAW DONE=' + CONVERT(VARCHAR, GETDATE(),109) 


--DROP TABLE #RAW_EQ


-------DO COMPUSTAT OVERRIDE CLEANSING---------------

ALTER TABLE #RAW_EQ

ADD Exchange varchar(50),

ExchangeCountry varchar(50)



UPDATE R

SET R.sedol = H.Sedol,

R.isin = H.isin,

R.cusip = H.cusip,

R.name = H.conm,

R.ticker = H.tic,

R.bbgTicker = H.bbgTicker,

R.Exchange = H.exchg,

R.ExchangeCountry = H.excntry_iso_num

FROM #RAW_EQ R

INNER JOIN GI_STAGING..stg_EQDB_V_SECMASTERHISTORY H ON R.gvkey = H.gvkey

AND R.iid = H.iid

WHERE obs_start < @RunDate

AND obs_end > @RunDate



------------------MAINTAIN SECURITY DIMENSIONS---------------

-------------------EXISTING SECURITY LOOKUP LOGIC------------


ALTER TABLE #RAW_EQ  

ADD SecurityID int,

PortfolioID int,

SourcePriorityId int


--SELECT * FROM #RAW_EQ 


-------------MULTI-LEVEL WATERFALL------------

UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON T.SEDOL = S.SEDOL

WHERE T.SecurityId IS NULL 


UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON T.ISIN = S.ISIN

WHERE T.SecurityId IS NULL 

UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON T.CUSIP = S.CUSIP

WHERE T.SecurityId IS NULL 


UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON T.id = S.LoanXID

WHERE T.SecurityId IS NULL 


UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON (T.gvkey = S.gvkey AND T.iid = S.iid)

WHERE T.SecurityId IS NULL 


UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON (T.assetGroup = 'O' 

    AND S.SecurityType in ('Equity - Index - Option','O')

AND T.name = S.SecurityName)

WHERE T.SecurityId IS NULL 

UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S ON (T.id = S.SecurityCode)

WHERE T.SecurityId IS NULL 

--- CASH CURRENCY --- 

UPDATE T

SET T.SecurityId = S.SecurityID,

T.PortfolioID = P.PortfolioId,

SourcePriorityId = LEFT(odsfundid,2)

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

LEFT OUTER JOIN GI_MASTER..dim_SECURITY S 

ON (T.assetGroup = 'CU' AND (T.name = S.SecurityName OR T.ticker = S.Ticker))

WHERE T.SecurityId IS NULL  





-- --------MAINTAIN SECURITY DIMENSIONS---------------------------------------------------------------------

---------------------EXISTING SECURITY LOOKUP LOGIC------------


-- ALTER TABLE #RAW_EQ  

-- ADD SecurityID int,

-- PortfolioID int,

-- SourcePriorityId int


-- --SELECT * FROM #RAW_EQ 

-- --select * from GI_MASTER..dim_SECURITY

 

-- UPDATE T

-- SET T.SecurityId = S.SecurityID,

-- T.PortfolioID = P.PortfolioId,

-- SourcePriorityId = LEFT(odsfundid,2)

-- FROM #RAW_EQ T

-- LEFT OUTER JOIN GI_MASTER..dim_SECURITY S  

-- -------------MULTI-LEVEL WATERFALL------------

-- ON T.CUSIP = S.CUSIP

-- OR T.SEDOL = S.SEDOL

--   -- OR T.Ticker = S.Ticker

-- OR T.id = S.LoanXID

-- OR T.isin = S.ISIN

-- OR (T.gvkey = S.gvkey AND T.iid = S.iid)

-- OR (T.assetGroup = 'O' 

--     AND S.SecurityType in ('Equity - Index - Option','O')

-- AND T.name = S.SecurityName)

-- OR (T.id = S.SecurityCode)

--       --- CASH CURRENCY ---

--                        OR (T.assetGroup = 'CU'

-- AND (T.name = S.SecurityName

--     OR T.ticker = S.Ticker))

-- -- OR T.name = S.SecurityName ---SecurityType---

--   ---PORTFOLIO---

-- LEFT OUTER JOIN dim_PORTFOLIO P ON T.portfolioCode = P.PortfolioCode

-- ------------CASE PRIORITY MAP-------------

-- --ON T.id = S.SecurityCode

PRINT '#EXISTING SECID MAPPING DONE=' + CONVERT(VARCHAR, GETDATE(),109) 


--------------------------DIMENSION DATA DRIVEN UPDATES----------------


---MAINTAIN ACCOUNT DIMENSION---


   INSERT INTO dim_PORTFOLIO

   (PortfolioCode,IsActive,CreatedDate,CreatedUser)

   SELECT DISTINCT portfolioCode as PorfolioCode 

   ,1 as IsActive

   ,GETDATE() as CreatedDate

   ,SourceID  as CreatedUser

   FROM #RAW_EQ T

   WHERE PortfolioID IS NULL


   PRINT 'INSERT UNMAPPED PORTDONE=' + CONVERT(VARCHAR, GETDATE(),109) 


---- UPDATE FOR ANY NEW PORTFOLIOS ----

    UPDATE T

SET T.PortfolioID = P.PortfolioID

FROM #RAW_EQ T

LEFT OUTER JOIN dim_PORTFOLIO P ON T.PortfolioCode = P.PortfolioCode

WHERE T.PortfolioID IS NULL


    PRINT 'UPDATE FK FOR NEW PORTFOLIOS=' + CONVERT(VARCHAR, GETDATE(),109) 



---- UPDATE FOR ANY NEW ACCOUNTS ----


ALTER TABLE #RAW_EQ

ADD AccountCode varchar(50),

AccountName varchar(200)


UPDATE R

SET R.AccountCode = ISNULL([BRS Account], R.PortfolioCode),

R.AccountName = ISNULL([Master Fund],R.PortfolioCode) 

FROM #RAW_EQ R

LEFT OUTER JOIN [dbo].[ref_INVESTONE_ACCOUNT_MAP] I ON R.PortfolioCode = I.[InvestOne Account Number]

WHERE SourceID = (SELECT SourceID FROM dim_SOURCE WHERE SourceName = 'Equity Holdings (Invest One)')

UPDATE P

SET P.AccountCode = ISNULL(R.AccountShortNamePrimary,P.PortfolioCode),

P.AccountName = ISNULL(R.AccountLongNamePrimary,P.PortfolioCode) 

FROM #RAW_EQ P

LEFT OUTER JOIN GI_STAGING..[stg_BRS_AMPORTFOLIOMASTER] R ON P.PortfolioCode = R.PortfolioShortName

WHERE SourceID <> (SELECT SourceID FROM dim_SOURCE WHERE SourceName = 'Equity Holdings (Invest One)')



INSERT INTO dim_ACCOUNT

    (AccountCode, AccountName, AccountDisplayName, IsActive, CreatedDate, CreatedUser)

SELECT DISTINCT Z.AccountCode, 

MAX(Z.AccountName) as AccountName,

MAX(Z.AccountName) as AccountDisplayName,

1 as IsActive,

GETDATE() as CreatedDate,

MAX(SourceID) as CreatedUser

FROM #RAW_EQ Z

LEFT OUTER JOIN dim_ACCOUNT A ON Z.AccountCode = A.AccountCode

WHERE A.AccountID IS NULL

GROUP BY Z.AccountCode



ALTER TABLE #RAW_EQ

ADD AccountID int


UPDATE R

SET R.AccountID = A.AccountID

FROM #RAW_EQ R

INNER JOIN dim_ACCOUNT A ON R.AccountCode = A.AccountCode


  -------------------------PORTFOLIO/SLEEVE ROLLUP---------

SELECT     

--CORE COMPOSITE HOLDINGS KEY-- 

PeriodEndDate,        

PortfolioID,        

SecurityID,

SourceID,

--DIM VALUE UNPIVOT LOOKUP --

(SELECT ValueID FROM [GI_MASTER].[dbo].[dim_VALUE] WHERE ValueCode = Measure) as ValueId,        

--SUM ACCROSS ALL SUMMABLE VALUES--

CASE WHEN  Measure in ('TAG_cur_face','TAG_mkt_value')

THEN SUM(CONVERT(float,Value)) ELSE 

MAX(CONVERT(float,Value)) END as Value        

---

INTO  #KEY_VALUE

----------------------------------

FROM  #RAW_EQ P

UNPIVOT (Value FOR Measure IN   

(TAG_cur_face, TAG_mkt_value)) as unpvt   

--ROLLUP OF LOT LEVEL---

GROUP BY 

PeriodEndDate,        

PortfolioID,  

SecurityID,

SourceID,

Measure



PRINT '#ANALYTICS KEY VALUE PIVOTING IS DONE=' + CONVERT(VARCHAR, GETDATE(),109) 


----------------------------------ACCOUNT ROLLUP-----------------------


SELECT     

--CORE COMPOSITE HOLDINGS KEY-- 

PeriodEndDate,        

AccountID,        

SecurityID,

SourceID,

--DIM VALUE UNPIVOT LOOKUP --

(SELECT ValueID FROM [GI_MASTER].[dbo].[dim_VALUE] WHERE ValueCode = Measure) as ValueId,        

--SUM ACCROSS ALL SUMMABLE VALUES--

CASE WHEN  Measure in ('TAG_cur_face','TAG_mkt_value')

THEN SUM(CONVERT(float,Value)) ELSE 

MAX(CONVERT(float,Value)) END as Value        

---

INTO  #KEY_VALUE_ACCOUNT

----------------------------------

FROM  #RAW_EQ P

UNPIVOT (Value FOR Measure IN   

(

---[shares],

TAG_cur_face,

--[price],

--[priceUSD],

--[marketvalue],

--[marketvalueUSD] 

  TAG_mkt_value  

 

)

) as unpvt   



--ROLLUP OF LOT LEVEL---

GROUP BY 

PeriodEndDate,        

AccountID,  

SecurityID,

SourceID,

Measure




PRINT '#ANALYTICS KEY VALUE PIVOTING IS DONE=' + CONVERT(VARCHAR, GETDATE(),109) 




------------------------------------------------------------------------

--------------------------GOLDEN COPY STORAGE / AUDIT----------------


    --SELECT * FROM #KEY_VALUE

DELETE [fct_HOLDINGS_PORTFOLIO_VALUE]

WHERE PeriodEndDate = @PeriodEndDate

AND SourceId in (1106,1107,1108)


PRINT 'DELETE HOLDINGS DONE=' + CONVERT(VARCHAR, GETDATE(),109) 



--TRUNCATE TABLE [fct_ANALYTICS_VALUE]

INSERT INTO [fct_HOLDINGS_PORTFOLIO_VALUE]

(PeriodEndDate, PortfolioID, SecurityID, ValueID, ValueHomeAmt, SourceID)

SELECT PeriodEndDate,

PortfolioId,

ISNULL(SecurityID,-999999),

ValueID,

Value as ValueHomeAmt,

SourceID

FROM #KEY_VALUE L



    DELETE [fct_HOLDINGS_ACCOUNT_VALUE]

WHERE PeriodEndDate = @PeriodEndDate

AND SourceId in (1106,1107,1108)


PRINT 'DELETE HOLDINGS ACCOUNT DONE=' + CONVERT(VARCHAR, GETDATE(),109) 


INSERT INTO [fct_HOLDINGS_ACCOUNT_VALUE]

(PeriodEndDate, AccountId, SecurityID, ValueID, ValueHomeAmt, SourceID)

SELECT PeriodEndDate,

AccountId,

ISNULL(SecurityID,-999999),

ValueID,

Value as ValueHomeAmt,

SourceID

FROM #KEY_VALUE_ACCOUNT L

UPDATE STATISTICS [fct_HOLDINGS_ACCOUNT_VALUE]


PRINT '#HOLDINGS ACCOUNT FACT TABLE INSERT IS DONE=' + CONVERT(VARCHAR, GETDATE(),109) 



END





GO

/****** Object:  StoredProcedure [dbo].[etl_FACTSET_MASTER_ATTRIBUTE_BUILD]    Script Date: 10/4/2018 11:19:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO