USE [SAMA_DATAMART] GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDWH ETL_DIM_OTC_CNRT_HIST]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spDWHETL_DIM_OTC_CNRT_HIST] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================================================= ===================================================================== -- SP Name : spDWHETL_DIM_OTC_CNRT_HIST -- Author Name : P. Rajalakshmi
-- Description : This stored procedure will load data into DIM_OTC_CNRT_HIST Table.
-- This will handle Insert, VersionedInsert,Update. -- Created Date : 31-Oct-2012
-- Returns : ProcessedCount,InsertedCount,UPdatedCount,ErrorFlag,ErrorCode,Error Message and ErrorSource.
-- Modified History :
-- Release TaskID Initials Modified Date Released for CostBefore CostAfter Path(in TFS) Description
-- 1.0
-- Stored Procedures that depends on this : NA -- Stored Procedures that which this SP depends: NA -- Execution : Exec spDWHETL_DIM_OTC_CNRT_HIST
-- Cost Estimation : Task Initials CostBefore CostAfter -- :
-- ============================================================================= ======================================================================
CREATE PROCEDURE [dbo].[spDWHETL_DIM_OTC_CNRT_HIST] @intETL_BTH_KEY INT
AS BEGIN
DECLARE @intProcessedCount INT , @intInsertedCount INT = 0, @intInsertedCount1 INT, @intUpdatedCount INT = 0, @intUpdatedCount1 INT, @intUpdatedCount2 INT, @intDimCNRTCnt INT, @intPreTarCNRTCnt INT, @intCnt INT
SET NOCOUNT ON; SET XACT_ABORT ON;
CREATE TABLE #DIM_OTC_CNRT_HIST(
[MyRowCount] INT IDENTITY(1,1),[CNRT_ID] [varchar](20) NOT NULL,
D] [varchar](15) NOT NULL,
[RCRD_STRT_DTS] [datetime] NOT NULL,[AMDNT_NBR] [varchar](15) NOT NULL,[CNRT_RPS TRY_RPTD_DATE] [date] NOT NULL,
[CNRT_STRT_DATE] [date] NOT NULL,[CNRT_END_DATE] [date] NOT NULL,[MSTR_AGRMT_TYP _NM] [varchar](50) NOT NULL,
[MSTR_AGRMT_TYP_CD] [varchar](15) NOT NULL,[MSTR_AGRMT_DATE] [date] NOT NULL,[CN RT_CLRG_ENT_NM] [varchar](50) NOT NULL,
[CNRT_CLRG_EXMTN_IND] [bit] NOT NULL,[CNRT_STAT_NM] [varchar](50) NOT NULL,[CNRT _STAT_CD] [varchar](15) NOT NULL,
[CNRT_CLR_IND] [bit] NOT NULL,[CNRT_CNFMD_IND] [bit] NOT NULL,[ELCNC_PLTFM_TRDD_ IND] [bit] NOT NULL,
[ELCNC_MTCHD_IND] [bit] NOT NULL,[PROD_GRD_DESC] [varchar](200) NOT NULL,[CNRT_O PT_TYP_NM] [varchar](50) NOT NULL,
[CNRT_OPT_TYP_CD] [varchar](15) NOT NULL,[CNRT_OPT_STYLE_NM] [varchar](50) NOT N ULL,[CNRT_OPT_STYLE_CD] [varchar](15) NOT NULL,
[SETMT_TYP_NM] [varchar](50) NOT NULL,[SETMT_TYP_CD] [varchar](15) NOT NULL,[RPT G_CP_SETMT_AGENT_NM] [varchar](50) NOT NULL,
[NRPTG_CP_SETMT_AGENT_NM] [varchar](50) NOT NULL,[DELIV_TYP_NM] [varchar](50) NO T NULL,[DELIV_TYP_CD] [varchar](15) NOT NULL,
[CP_PAY_STRM_DESC] [varchar](200) NOT NULL,[COMDTY_MESRE_UNIT_NM] [varchar](50) NOT NULL,[CNRT_DIRN_NM] [varchar](50) NOT NULL,
[PAYG_RT_DAY_CNT_FRCTN_CD] [varchar](10) NOT NULL,[PAYG_PAY_FREQ_NM] [varchar](5 0) NOT NULL,[PAYG_PAY_FREQ_CD] [varchar](15) NOT NULL,
[RCVG_PAY_FREQ_NM] [varchar](50) NOT NULL,[RCVG_PAY_FREQ_CD] [varchar](15) NOT N ULL,[RT_RESET_FREQ_NM] [varchar](50) NOT NULL,
[RT_RESET_FREQ_CD] [varchar](15) NOT NULL,[RCVG_RT_DAY_CNT_FRCTN_CD] [varchar](1 0) NOT NULL,[CNRT_TENOR_NM] [varchar](50) NOT NULL,
[CNRT_TENOR_CD] [varchar](15) NOT NULL,[PAYG_FLOTG_RT_IDX_TENOR_NM] [varchar](50 ) NOT NULL,[PAYG_FLOTG_RT_IDX_TENOR_CD] [varchar](15) NOT NULL,
[PAYG_FLOTG_RT_IDX_NM] [varchar](50) NOT NULL,[PAYG_FLOTG_RT_IDX_CD] [varchar](5 0) NOT NULL,[RCVG_FLOTG_RT_IDX_TENOR_NM] [varchar](50) NOT NULL,
[RCVG_FLOTG_RT_IDX_TENOR_CD] [varchar](15) NOT NULL,[RCVG_FLOTG_RT_IDX_NM] [varc har](50) NOT NULL,[RCVG_FLOTG_RT_IDX_CD] [varchar](15) NOT NULL,
[RGTRG_AUTHY_NM] [varchar](50) NOT NULL,[ERR_DES] [varchar](100) NULL,[CORR_REC_ IND] [int] NOT NULL)
INSERT INTO #DIM_OTC_CNRT_HIST(
[CNRT_ID],[CNRT_SRC_ID],[CNRT_BANK_KEY],[CNRT_BANK_CD], [RCRD_STRT_DTS],[AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE], [CNRT_STRT_DATE],[CNRT_END_DATE],[MSTR_AGRMT_TYP_NM], [MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM], [CNRT_CLRG_EXMTN_IND],[CNRT_STAT_NM],[CNRT_STAT_CD], [CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND], [ELCNC_MTCHD_IND],[PROD_GRD_DESC],[CNRT_OPT_TYP_NM], [CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_NM],[CNRT_OPT_STYLE_CD], [SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGENT_NM], [NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD], [CP_PAY_STRM_DESC],[COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM], [PAYG_RT_DAY_CNT_FRCTN_CD],[PAYG_PAY_FREQ_NM],[PAYG_PAY_FREQ_CD], [RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_FREQ_NM], [RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM], [CNRT_TENOR_CD],[PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD], [PAYG_FLOTG_RT_IDX_NM],[PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM], [RCVG_FLOTG_RT_IDX_TENOR_CD],[RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD], [RGTRG_AUTHY_NM],[ERR_DES],[CORR_REC_IND]) SELECT PTCT.[CNRT_ID],PTCT.[CNRT_SRC_ID],ISNULL(DB.[BANK_KEY],-10) AS [CNRT_BANK_KEY],P TCT.[CNRT_BANK_CD], PTCT.[AMDNT_DTS] AS [RCRD_STRT_DTS],PTCT.[AMDNT_NBR],PTCT.[CNRT_RPSTRY_RPTD_DATE ],
PTCT.[CNRT_STRT_DATE],PTCT.[CNRT_END_DATE],ISNULL(MSTAGR.[LKP_VAL_TXT],-10) AS [ MSTR_AGRMT_TYP_NM], PTCT.[MSTR_AGRMT_TYP_CD],PTCT.[MSTR_AGRMT_DATE],PTCT.[CNRT_CLRG_ENT_NM], PTCT.[CNRT_CLRG_EXMTN_IND],ISNULL(CTST.[LKP_VAL_TXT],-10) AS [CNRT_STAT_NM],PTCT .[CNRT_STAT_CD], PTCT.[CNRT_CLR_IND],PTCT.[CNRT_CNFMD_IND],PTCT.[ELCNC_PLTFM_TRDD_IND], PTCT.[ELCNC_MTCHD_IND],PTCT.[PROD_GRD_DESC],ISNULL(CTOT.[LKP_VAL_TXT],-10) AS [C NRT_OPT_TYP_NM], PTCT.[CNRT_OPT_TYP_CD],ISNULL(CNSTY.[LKP_VAL_TXT],-10) AS[CNRT_OPT_STYLE_NM],PTC T.[CNRT_OPT_STYLE_CD], ISNULL(SETY.[LKP_VAL_TXT],-10) AS [SETMT_TYP_NM],PTCT.[SETMT_TYP_CD],PTCT.[RPTG_ CP_SETMT_AGENT_NM], PTCT.[NRPTG_CP_SETMT_AGENT_NM],ISNULL(DELTY.[LKP_VAL_TXT],-10) AS [DELIV_TYP_NM] ,PTCT.[DELIV_TYP_CD], PTCT.[CP_PAY_STRM_DESC],PTCT.[COMDTY_MESRE_UNIT_NM],PTCT.[CNRT_DIRN_NM], PTCT.[PAYG_RT_DAY_CNT_FRCTN_CD],ISNULL(PFQ.[LKP_VAL_TXT],-10) AS [PAYG_PAY_FREQ_ NM],PTCT.[PAYG_PAY_FREQ_CD], ISNULL(RFQ.[LKP_VAL_TXT],-10) AS [RCVG_PAY_FREQ_NM],PTCT.RCVG_PAY_FREQ_CD,ISNULL (RRFQ.[LKP_VAL_TXT],-10) AS [RT_RESET_FREQ_NM], PTCT.[RT_RESET_FREQ_CD],PTCT.[RCVG_RT_DAY_CNT_FRCTN_CD],ISNULL(TEN.[LKP_VAL_TXT] ,-10) AS [CNRT_TENOR_NM], PTCT.[CNRT_TENOR_CD],ISNULL(TEN.[LKP_VAL_TXT],-10) AS [PAYG_FLOTG_RT_IDX_TENOR_N M],PTCT.[PAYG_FLOTG_RT_IDX_TENOR_CD], ISNULL(PART.[LKP_VAL_TXT],-10) AS [PAYG_FLOTG_RT_IDX_NM],PTCT.[PAYG_FLOTG_RT_IDX _CD],ISNULL(RCTEN.[LKP_VAL_TXT],-10) AS [RCVG_FLOTG_RT_IDX_TENOR_NM], PTCT.[RCVG_FLOTG_RT_IDX_TENOR_CD],ISNULL(RCIDX.[LKP_VAL_TXT],-10) AS [RCVG_FLOTG _RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],
PTCT.[RGTRG_AUTHY_NM],NULL AS [ERR_DES],1 AS [CORR_REC_IND] FROM [SAMA_STAGE].[dbo].[PRE_TARGET_OTC_CNRT] AS PTCT
LEFT JOIN [dbo].[DIM_BANK] AS DB ON PTCT.CNRT_BANK_CD = DB.BANK_CD LEFT JOIN (SELECT DL.LKP_CD,
DL.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT ON DLT.LKP_TYP_KEY=DL.LKP_TYP_KEY
AND DLT.LKP_TYP_CD='MSTR_AGRMT_TYP') AS MSTAGR ON PTCT.MSTR_AGRMT_TYP_CD = MST AGR.LKP_CD
LEFT JOIN (SELECT DL1.LKP_CD, DL1.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL1
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT1 ON DLT1.LKP_TYP_KEY=DL1.LKP_TYP_KEY
AND DLT1.LKP_TYP_CD='CNRT_STAT') AS CTST ON PTCT.CNRT_STAT_CD = CTST.LKP_CD LEFT JOIN (SELECT DL2.LKP_CD,
DL2.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL2
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT2 ON DLT2.LKP_TYP_KEY=DL2.LKP_TYP_KEY
AND DLT2.LKP_TYP_CD='CNRT_OPT_TYP') AS CTOT ON PTCT.CNRT_TENOR_CD = CTOT.LKP_C D
LEFT JOIN (SELECT DL3.LKP_CD, DL3.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL3
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT3 ON DLT3.LKP_TYP_KEY=DL3.LKP_TYP_KEY
AND DLT3.LKP_TYP_CD='SETMT_TYP') AS SETY ON PTCT.RCVG_FLOTG_RT_IDX_CD = SETY.L KP_CD
LEFT JOIN (SELECT DL4.LKP_CD, DL4.LKP_VAL_TXT
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT4 ON DLT4.LKP_TYP_KEY=DL4.LKP_TYP_KEY
AND DLT4.LKP_TYP_CD='DELIV_TYP') AS DELTY ON PTCT.DELIV_TYP_CD = DELTY.LKP_CD LEFT JOIN (SELECT DL5.LKP_CD,
DL5.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL5
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT5 ON DLT5.LKP_TYP_KEY=DL5.LKP_TYP_KEY
AND DLT5.LKP_TYP_CD='PAYG_PAY_FREQ') AS PFQ ON PTCT.PAYG_FLOTG_RT_IDX_CD = PFQ .LKP_CD
LEFT JOIN (SELECT DL6.LKP_CD, DL6.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL6
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT6 ON DLT6.LKP_TYP_KEY=DL6.LKP_TYP_KEY
AND DLT6.LKP_TYP_CD='RCVG_PAY_FREQ') AS RFQ ON PTCT.RCVG_FLOTG_RT_IDX_CD = RFQ .LKP_CD
LEFT JOIN (SELECT DL7.LKP_CD, DL7.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL7
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT7 ON DLT7.LKP_TYP_KEY=DL7.LKP_TYP_KEY
AND DLT7.LKP_TYP_CD='RT_RESET_FREQ') AS RRFQ ON PTCT.RT_RESET_FREQ_CD = RRFQ.L KP_CD
LEFT JOIN (SELECT DL8.LKP_CD, DL8.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL8
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT8 ON DLT8.LKP_TYP_KEY=DL8.LKP_TYP_KEY
AND DLT8.LKP_TYP_CD='CNRT_TENOR') AS TEN ON PTCT.PAYG_FLOTG_RT_IDX_CD = TEN.LK P_CD
LEFT JOIN (SELECT DL9.LKP_CD, DL9.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL9
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT9 ON DLT9.LKP_TYP_KEY=DL9.LKP_TYP_KEY
AND DLT9.LKP_TYP_CD='PAYG_FLOTG_RT_IDX_TENOR') AS PATEN ON PTCT.RCVG_FLOTG_RT_ IDX_CD = PATEN.LKP_CD
LEFT JOIN (SELECT DL10.LKP_CD, DL10.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL10
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT10 ON DLT10.LKP_TYP_KEY=DL10.LKP_TYP_KEY
AND DLT10.LKP_TYP_CD='PAYG_FLOTG_RT_IDX') AS PART ON PTCT.PAYG_FLOTG_RT_IDX_CD = PART.LKP_CD
LEFT JOIN (SELECT DL11.LKP_CD, DL11.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL11
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT11 ON DLT11.LKP_TYP_KEY=DL11.LKP_TYP_KEY
AND DLT11.LKP_TYP_CD='RCVG_FLOTG_RT_IDX_TENOR') AS RCTEN ON PTCT.RCVG_FLOTG_RT _IDX_CD = RCTEN.LKP_CD
LEFT JOIN (SELECT DL12.LKP_CD, DL12.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL12
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT12 ON DLT12.LKP_TYP_KEY=DL12.LKP_TYP_KEY
AND DLT12.LKP_TYP_CD='RCVG_FLOTG_RT_IDX') AS RCIDX ON PTCT.RCVG_FLOTG_RT_IDX_C D = RCIDX.LKP_CD
DL13.LKP_VAL_TXT
FROM [dbo].[DIM_LKP] AS DL13
INNER JOIN [dbo].[DIM_LKP_TYP] AS DLT13 ON DLT13.LKP_TYP_KEY=DL13.LKP_TYP_KEY
AND DLT13.LKP_TYP_CD='CNRT_OPT_STYLE_CD') AS CNSTY ON PTCT.CNRT_OPT_STYLE_CD = CNSTY.LKP_CD
ORDER BY PTCT.[AMDNT_DTS]
DECLARE @strERR_DESC VARCHAR(50), @intCnt INT, @intMax INT, @intCNRT_BANK_KEY INT, @intMSTR_AGRMT_TYP_NM INT, @intCNRT_STAT_NM INT, @intCNRT_OPT_TYP_NM INT, @intSETMT_TYP_NM INT, @intDELIV_TYP_NM INT, @intPAYG_PAY_FREQ_NM INT, @intRCVG_PAY_FREQ_NM INT, @intRT_RESET_FREQ_NM INT, @intCNRT_TENOR_NM INT, @intPAYG_FLOTG_RT_IDX_TENOR_NM INT, @intPAYG_FLOTG_RT_IDX_NM INT, @intRCVG_FLOTG_RT_IDX_TENOR_NM INT, @intRCVG_FLOTG_RT_IDX_NM INT, @intCNRT_OPT_STYLE_NM INT SET @intCnt = 1
SET @intMax = (SELECT MAX([MyRowCount]) FROM #DIM_OTC_CNRT_HIST) WHILE @intCnt <= @intMax
BEGIN
IF (SELECT COUNT(1) FROM #DIM_OTC_CNRT_HIST WHERE ([CNRT_BANK_KEY] = -10 OR [MST R_AGRMT_TYP_NM] = -10 OR [CNRT_STAT_NM] = -10 OR [CNRT_OPT_TYP_NM] = -10 OR [SETMT_TYP_NM] = -10 OR [DELIV_TYP_NM] = -10 OR [PAYG_PAY_FREQ_NM] = -10 OR [RCVG_PAY_FREQ_NM] = -10 OR [RT_RESET_FREQ_NM] = -10 OR [CNRT_TENOR_NM] = -10 OR [PAYG_FLOTG_RT_IDX_TENOR_NM] = -10 OR [PAYG_FLOTG_RT_IDX_NM] = -10 OR
[RCVG_FLOTG_RT_IDX_TENOR_NM] = -10 OR [RCVG_FLOTG_RT_IDX_NM] = -10 OR [CNRT_OPT_ STYLE_NM] = -10)
AND [MyRowCount] = @intCnt) = 1 BEGIN
SELECT @intCNRT_BANK_KEY = [CNRT_BANK_KEY],@intMSTR_AGRMT_TYP_NM = [MSTR_AGRMT_T YP_NM],
@intCNRT_STAT_NM = [CNRT_STAT_NM],@intCNRT_OPT_TYP_NM = [CNRT_OPT_TYP_NM], @intSETMT_TYP_NM = [SETMT_TYP_NM],@intDELIV_TYP_NM = [DELIV_TYP_NM],
@intPAYG_PAY_FREQ_NM = [PAYG_PAY_FREQ_NM],@intRCVG_PAY_FREQ_NM = [RCVG_PAY_FRE Q_NM],
@intRT_RESET_FREQ_NM = [RT_RESET_FREQ_NM],@intCNRT_TENOR_NM = [CNRT_TENOR_NM], @intPAYG_FLOTG_RT_IDX_TENOR_NM = [PAYG_FLOTG_RT_IDX_TENOR_NM],@intPAYG_FLOTG_R T_IDX_NM = [PAYG_FLOTG_RT_IDX_NM],
@intRCVG_FLOTG_RT_IDX_TENOR_NM = [RCVG_FLOTG_RT_IDX_TENOR_NM],@intRCVG_FLOTG_R T_IDX_NM = [RCVG_FLOTG_RT_IDX_NM],@intCNRT_OPT_STYLE_NM = [CNRT_OPT_STYLE_NM] FROM #DIM_OTC_CNRT_HIST
IF @intCNRT_BANK_KEY = -10 BEGIN
SET @strERR_DESC = 'CNRT_BANK_CD-' END
IF @intMSTR_AGRMT_TYP_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'MSTR_AGRMT_TYP_CD-' END
IF @intCNRT_STAT_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'CNRT_STAT_CD-' END
IF @intCNRT_OPT_TYP_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'CNRT_TENOR_CD-' END
IF @intSETMT_TYP_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'RCVG_FLOTG_RT_IDX_CD-' END
IF @intDELIV_TYP_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'DELIV_TYP_CD-' END
IF @intPAYG_PAY_FREQ_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'PAYG_FLOTG_RT_IDX_CD-' END
IF @intRCVG_PAY_FREQ_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'RCVG_FLOTG_RT_IDX_CD-' END
IF @intRT_RESET_FREQ_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'RT_RESET_FREQ_CD-' END
IF @intCNRT_TENOR_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'PAYG_FLOTG_RT_IDX_CD-' END
IF @intPAYG_FLOTG_RT_IDX_TENOR_NM = -10 BEGIN
SET @strERR_DESC = 'RCVG_FLOTG_RT_IDX_CD-' END
IF @intPAYG_FLOTG_RT_IDX_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'PAYG_FLOTG_RT_IDX_CD-' END
IF @intRCVG_FLOTG_RT_IDX_TENOR_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'RCVG_FLOTG_RT_IDX_CD-' END
IF @intRCVG_FLOTG_RT_IDX_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'RCVG_FLOTG_RT_IDX_CD-' END
IF @intCNRT_OPT_STYLE_NM = -10 BEGIN
SET @strERR_DESC = @strERR_DESC+'CNRT_OPT_STYLE_NM-' END
SET @strERR_DESC = @strERR_DESC+'Not Available' UPDATE #DIM_OTC_CNRT_HIST
SET [ERR_DES] = @strERR_DESC, [CORR_REC_IND] = 0
WHERE [MyRowCount] = @intCnt END
SET @intCnt = @intCnt + 1 END
--Loading the Data from #DIM_OTC_CNRT_HIST to DIM_OTC_CNRT_HIST table BEGIN TRY
BEGIN
IF (SELECT COUNT(1) FROM [Dbo].[DIM_OTC_CNRT_HIST] WITH (NOLOCK)) = 1 BEGIN
--Full Load
BEGIN TRANSACTION
INSERT INTO [dbo].[DIM_OTC_CNRT_HIST]
([CNRT_HIST_KEY],[CNRT_KEY],[CNRT_ID],[CNRT_SRC_ID],[CNRT_BANK_KEY] ,[CNRT_BANK_CD],[RCRD_STRT_DTS],[RCRD_END_DTS],[RCRD_STAT_IND],[RCRD_ CD_STAT_IND] ,[AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE] ,[MSTR_AGRMT_TYP_NM] ,[MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_ EXMTN_IND],[CNRT_STAT_NM] ,[CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND ],[ELCNC_MTCHD_IND] ,[PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_ NM] ,[CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGE NT_NM] ,[NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM _DESC] ,[COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],[PA YG_PAY_FREQ_NM] ,[PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_F REQ_NM] ,[RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM],[CNRT_ TENOR_CD] ,[PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],[PAYG_FLOT G_RT_IDX_NM] ,[PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],[RCVG_FLOTG_RT_I DX_TENOR_CD] ,[RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],[RGTRG_AUTHY_NM] ,[ETL_CRTD_DTS],[ETL_UPDT_DTS],[ETL_BTH_KEY]) SELECT 1 AS [CNRT_HIST_KEY],1 AS [CNRT_KEY],[CNRT_ID],[CNRT_SRC_ID],[CNRT_BANK_KEY],
[CNRT_BANK_CD],[RCRD_STRT_DTS],'9999-99-99' AS [RCRD_END_DTS],1 AS [RCRD_STAT_IN D],1 AS [RCRD_CD_STAT_IND], [AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE],[MSTR_AGRMT _TYP_NM], [MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_EXMTN_IND],[ CNRT_STAT_NM], [CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND],[ELCNC_MTC HD_IND], [PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_NM], [CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGENT_NM], [NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM_DESC], [COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],[PAYG_PAY_FREQ_ NM], [PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_FREQ_NM], [RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM],[CNRT_TENOR_CD], [PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],[PAYG_FLOTG_RT_IDX_NM] , [PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],[RCVG_FLOTG_RT_IDX_TENOR_CD] , [RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],[RGTRG_AUTHY_NM],
,GETDATE() AS [ETL_CRTD_DTS],NULL AS [ETL_UPDT_DTS],@intETL_BTH_KEY AS [ETL_BTH_ KEY] FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 1 COMMIT TRANSACTION END ELSE --Increemental Load BEGIN
DECLARE @intCnt1 INT, @intMax1 INT
SET @intCnt1 = 1
SET @intMax1 = (SELECT MAX([MyRowCount]) FROM #DIM_OTC_CNRT_HIST) WHILE @intCnt1 <= @intMax1
BEGIN
--Check whether the [CNRT_ID] per Bank is available or not
IF(SELECT COUNT(1) FROM #DIM_OTC_CNRT_HIST AS tDCH WHERE [CORR_REC_IND] = 1 AND [MyRowCount] = @intCnt1
AND NOT EXISTS(SELECT [CNRT_ID],[CNRT_BANK_CD] FROM [DIM_OTC_CNRT_HIST] AS DCH WHERE tDCH.[CNRT_ID] = DCH.[CNRT_ID] AND tDCH.[CNRT_BANK_CD] = DCH.[CNRT_BANK_CD ])) = 0
BEGIN
BEGIN TRANSACTION
DECLARE intMaxCNRT_HIST_KEY INT, intMaxCNRT_KEY INT
SET intMaxCNRT_HIST_KEY = (SELECT MAX(CNRT_HIST_KEY) FROM [Dbo].[DIM_OTC_CNRT_HI ST])
SET intMaxCNRT_KEY = (SELECT MAX(CNRT_KEY) FROM [Dbo].[DIM_OTC_CNRT_HIST] GROUP BY [CNRT_BANK_KEY],[CNRT_ID])
--Insert the [CNRT_ID],which is not in the [DIM_OTC_CNRT_HIST] INSERT INTO [dbo].[DIM_OTC_CNRT_HIST]
([CNRT_HIST_KEY],[CNRT_KEY],[CNRT_ID],[CNRT_SRC_ID],[CNRT_BANK_KEY] ,[CNRT_BANK_CD],[RCRD_STRT_DTS],[RCRD_END_DTS],[RCRD_STAT_IND],[RCRD_ CD_STAT_IND] ,[AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE] ,[MSTR_AGRMT_TYP_NM] ,[MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_ EXMTN_IND],[CNRT_STAT_NM] ,[CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND ],[ELCNC_MTCHD_IND] ,[PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_ NM] ,[CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGE NT_NM] ,[NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM _DESC] ,[COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],[PA YG_PAY_FREQ_NM] ,[PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_F REQ_NM] ,[RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM],[CNRT_ TENOR_CD] ,[PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],[PAYG_FLOT G_RT_IDX_NM] ,[PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],[RCVG_FLOTG_RT_I DX_TENOR_CD] ,[RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],[RGTRG_AUTHY_NM] ,[ETL_CRTD_DTS],[ETL_UPDT_DTS],[ETL_BTH_KEY]) SELECT
intMaxCNRT_HIST_KEY AS [CNRT_HIST_KEY],intMaxCNRT_KEY AS [CNRT_KEY],[CNRT_ID],[C NRT_SRC_ID],[CNRT_BANK_KEY],
[CNRT_BANK_CD],[RCRD_STRT_DTS],'9999-99-99' AS [RCRD_END_DTS],1 AS [RCRD_STAT_IN D],1 AS [RCRD_CD_STAT_IND], [AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE],[MSTR_AGRMT _TYP_NM], [MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_EXMTN_IND],[ CNRT_STAT_NM], [CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND],[ELCNC_MTC HD_IND], [PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_NM], [CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGENT_NM], [NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM_DESC], [COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],tDCH.[PAYG_PAY_ FREQ_NM], [PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],tDCH.[RCVG_PAY_FREQ_CD],tDCH.[RT_RESET_FRE Q_NM], [RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],tDCH.[CNRT_TENOR_NM],tDCH.[CNRT_TE NOR_CD], [PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],tDCH.[PAYG_FLOTG_RT_ID X_NM], [PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],tDCH.[RCVG_FLOTG_RT_IDX_TENO R_CD], [RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],tDCH.[RGTRG_AUTHY_NM],
,GETDATE() AS [ETL_CRTD_DTS],NULL AS [ETL_UPDT_DTS],@intETL_BTH_KEY AS [ETL_BTH_ KEY]
FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 1 AND [MyRowCount] = @intCnt1
SELECT @intInsertedCount1 = COUNT(1) FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND ] = 1 AND [MyRowCount] = @intCnt1
COMMIT TRANSACTION END
ELSE BEGIN
DECLARE @intCurr_RCRD_STRT_DTS DATETIME, @intPrev_RCRD_END_DTS DATETIME, @intPrev_RCRD_STAT_IND INT, @intPrev_CNRT_HIST_KEY INT, @intPrev_CNRT_KEY INT, @intCnt INT, @intMaxCNRT_HIST_KEY INT SELECT @intCnt = COUNT(1),
@intCurr_RCRD_STRT_DTS = tempDIMCNT.[RCRD_STRT_DTS], @intPrev_RCRD_END_DTS = DIMCNT.[RCRD_END_DTS], @intPrev_RCRD_STAT_IND = DIMCNT.[RCRD_STAT_IND], @intPrev_CNRT_HIST_KEY = DIMCNT.[CNRT_HIST_KEY], @intPrev_CNRT_KEY = DIMCNT.[CNRT_KEY]
FROM #DIM_OTC_CNRT_HIST AS tempDIMCNT, [Dbo].[DIM_OTC_CNRT_HIST] AS DIMCNT
WHERE tempDIMCNT.[CORR_REC_IND] = 1 AND tempDIMCNT.[MyRowCount] = @intCnt1 AND t empDIMCNT.[CNRT_ID] = DIMCNT.[CNRT_ID] AND tempDIMCNT.[CNRT_BANK_KEY] = DIMCNT.[ CNRT_BANK_KEY]
AND tempDIMCNT.[RCRD_STRT_DTS] >= DIMCNT.[RCRD_STRT_DTS] AND tempDIMCNT.[R CRD_STRT_DTS] < DIMCNT.[RCRD_END_DTS]
SET @intMaxCNRT_HIST_KEY = (SELECT MAX(CNRT_HIST_KEY) FROM [Dbo].[DIM_OTC_CNRT_H IST])
IF @intCnt = 1 BEGIN
BEGIN TRANSACTION
--SCD2 Insertion which are availabe within the period of time
--SELECT @intUpdatedCount1 = COUNT(1) WHERE CNRT_HIST_KEY = @intPrev_CNRT_HIST_K EY
INSERT INTO [dbo].[DIM_OTC_CNRT_HIST]
([CNRT_HIST_KEY],[CNRT_KEY],[CNRT_ID],[CNRT_SRC_ID],[CNRT_BANK_KEY] ,[CNRT_BANK_CD],[RCRD_STRT_DTS],[RCRD_END_DTS],[RCRD_STAT_IND],[RCRD_ CD_STAT_IND]
,[AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE] ,[MSTR_AGRMT_TYP_NM]
,[MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_ EXMTN_IND],[CNRT_STAT_NM] ,[CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND ],[ELCNC_MTCHD_IND] ,[PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_ NM] ,[CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGE NT_NM] ,[NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM _DESC] ,[COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],[PA YG_PAY_FREQ_NM] ,[PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_F REQ_NM] ,[RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM],[CNRT_ TENOR_CD] ,[PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],[PAYG_FLOT G_RT_IDX_NM] ,[PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],[RCVG_FLOTG_RT_I DX_TENOR_CD] ,[RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],[RGTRG_AUTHY_NM] ,[ETL_CRTD_DTS],[ETL_UPDT_DTS],[ETL_BTH_KEY]) SELECT
@intMaxCNRT_HIST_KEY AS [CNRT_HIST_KEY],@intPrev_CNRT_KEY AS [CNRT_KEY],[CNRT_ID ],[CNRT_SRC_ID],[CNRT_BANK_KEY],
[CNRT_BANK_CD],[RCRD_STRT_DTS],@intPrev_RCRD_END_DTS AS [RCRD_END_DTS],@intPrev_ RCRD_STAT_IND AS [RCRD_STAT_IND],1 AS [RCRD_CD_STAT_IND],
[AMDNT_NBR],[CNRT_RPSTRY_RPTD_DATE],[CNRT_STRT_DATE],[CNRT_END_DATE],[MSTR_AGRMT _TYP_NM], [MSTR_AGRMT_TYP_CD],[MSTR_AGRMT_DATE],[CNRT_CLRG_ENT_NM],[CNRT_CLRG_EXMTN_IND],[ CNRT_STAT_NM], [CNRT_STAT_CD],[CNRT_CLR_IND],[CNRT_CNFMD_IND],[ELCNC_PLTFM_TRDD_IND],[ELCNC_MTC HD_IND], [PROD_GRD_DESC],[CNRT_OPT_TYP_NM],[CNRT_OPT_TYP_CD],[CNRT_OPT_STYLE_NM], [CNRT_OPT_STYLE_CD],[SETMT_TYP_NM],[SETMT_TYP_CD],[RPTG_CP_SETMT_AGENT_NM], [NRPTG_CP_SETMT_AGENT_NM],[DELIV_TYP_NM],[DELIV_TYP_CD],[CP_PAY_STRM_DESC], [COMDTY_MESRE_UNIT_NM],[CNRT_DIRN_NM],[PAYG_RT_DAY_CNT_FRCTN_CD],[PAYG_PAY_FREQ_ NM], [PAYG_PAY_FREQ_CD],[RCVG_PAY_FREQ_NM],[RCVG_PAY_FREQ_CD],[RT_RESET_FREQ_NM], [RT_RESET_FREQ_CD],[RCVG_RT_DAY_CNT_FRCTN_CD],[CNRT_TENOR_NM],[CNRT_TENOR_CD], [PAYG_FLOTG_RT_IDX_TENOR_NM],[PAYG_FLOTG_RT_IDX_TENOR_CD],[PAYG_FLOTG_RT_IDX_NM] , [PAYG_FLOTG_RT_IDX_CD],[RCVG_FLOTG_RT_IDX_TENOR_NM],[RCVG_FLOTG_RT_IDX_TENOR_CD] , [RCVG_FLOTG_RT_IDX_NM],[RCVG_FLOTG_RT_IDX_CD],[RGTRG_AUTHY_NM],
,GETDATE() AS [ETL_CRTD_DTS],NULL AS [ETL_UPDT_DTS],@intETL_BTH_KEY AS [ETL_BTH_ KEY]
FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 1 AND [MyRowCount] = @intCnt1 UPDATE [dbo].[DIM_OTC_CNRT_HIST]
SET [RCRD_END_DTS] = @intCurr_RCRD_STRT_DTS, 0 AS [RCRD_STAT_IND]
WHERE CNRT_HIST_KEY = @intPrev_CNRT_HIST_KEY
SELECT @intUpdatedCount1 = COUNT(1) FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 1 AND [MyRowCount] = @intCnt1
END ELSE BEGIN
BEGIN TRANSACTION
--Update for the same [CNRT_ID] per bank and the period of time also same /*
SELECT @intUpdatedCount2 = COUNT(1) FROM DIM_OTC_CNRT_HIST AS DIMCNT INNER JOIN #DIM_OTC_CNRT_HIST AS tempDIMCNT
WHERE tempDIMCNT.[CORR_REC_IND] = 1 AND tempDIMCNT.[MyRowCount] = @intCnt1 AND t empDIMCNT.[CNRT_ID] = DIMCNT.[CNRT_ID] AND tempDIMCNT.[CNRT_BANK_KEY] = DIMCNT.[ CNRT_BANK_KEY]
AND tempDIMCNT.[RCRD_STRT_DTS] = DIMCNT.[RCRD_STRT_DTS] AND tempDIMCNT.[RC RD_STRT_DTS] = DIMCNT.[RCRD_END_DTS]
*/
UPDATE DIMCNT
SET [CNRT_SRC_ID] = tempDIMCNT.[CNRT_SRC_ID], [CNRT_BANK_CD] = tempDIMCNT.[CNRT_BANK_CD], [AMDNT_NBR] = tempDIMCNT.[AMDNT_NBR], [CNRT_RPSTRY_RPTD_DATE] = tempDIMCNT.[CNRT_RPSTRY_RPTD_DATE], [CNRT_STRT_DATE] = tempDIMCNT.[CNRT_STRT_DATE], [CNRT_END_DATE] = tempDIMCNT.[CNRT_END_DATE], [MSTR_AGRMT_TYP_NM] = tempDIMCNT.[MSTR_AGRMT_TYP_NM], [MSTR_AGRMT_TYP_CD] = tempDIMCNT.[MSTR_AGRMT_TYP_CD], [MSTR_AGRMT_DATE] = tempDIMCNT.[MSTR_AGRMT_DATE], [CNRT_CLRG_ENT_NM] = tempDIMCNT.[CNRT_CLRG_ENT_NM], [CNRT_CLRG_EXMTN_IND] = tempDIMCNT.[CNRT_CLRG_EXMTN_IND], [CNRT_STAT_NM] = tempDIMCNT.[CNRT_STAT_NM], [CNRT_STAT_CD] = tempDIMCNT.[CNRT_STAT_CD], [CNRT_CLR_IND] = tempDIMCNT.[CNRT_CLR_IND], [CNRT_CNFMD_IND] = tempDIMCNT.[CNRT_CNFMD_IND], [ELCNC_PLTFM_TRDD_IND] = tempDIMCNT.[ELCNC_PLTFM_TRDD_IND], [ELCNC_MTCHD_IND] = tempDIMCNT.[ELCNC_MTCHD_IND], [PROD_GRD_DESC] = tempDIMCNT.[PROD_GRD_DESC], [CNRT_OPT_TYP_NM] = tempDIMCNT.[CNRT_OPT_TYP_NM], [CNRT_OPT_TYP_CD] = tempDIMCNT.[CNRT_OPT_TYP_CD], [CNRT_OPT_STYLE_NM] = tempDIMCNT.[CNRT_OPT_STYLE_NM], [CNRT_OPT_STYLE_CD] = tempDIMCNT.[CNRT_OPT_STYLE_CD], [SETMT_TYP_NM] = tempDIMCNT.[SETMT_TYP_NM], [SETMT_TYP_CD] = tempDIMCNT.[SETMT_TYP_CD], [RPTG_CP_SETMT_AGENT_NM] = tempDIMCNT.[RPTG_CP_SETMT_AGENT_NM], [NRPTG_CP_SETMT_AGENT_NM] = tempDIMCNT.[NRPTG_CP_SETMT_AGENT_NM], [DELIV_TYP_NM] = tempDIMCNT.[DELIV_TYP_NM], [DELIV_TYP_CD] = tempDIMCNT.[DELIV_TYP_CD], [CP_PAY_STRM_DESC] = tempDIMCNT.[CP_PAY_STRM_DESC], [COMDTY_MESRE_UNIT_NM] = tempDIMCNT.[COMDTY_MESRE_UNIT_NM], [CNRT_DIRN_NM] = tempDIMCNT.[CNRT_DIRN_NM], [PAYG_RT_DAY_CNT_FRCTN_CD] = tempDIMCNT.[PAYG_RT_DAY_CNT_FRCTN_CD], [PAYG_PAY_FREQ_NM] = tempDIMCNT.[PAYG_PAY_FREQ_NM], [PAYG_PAY_FREQ_CD] = tempDIMCNT.[PAYG_PAY_FREQ_CD], [RCVG_PAY_FREQ_NM] = tempDIMCNT.[RCVG_PAY_FREQ_NM], [RCVG_PAY_FREQ_CD] = tempDIMCNT.[RCVG_PAY_FREQ_CD], [RT_RESET_FREQ_NM] = tempDIMCNT.[RT_RESET_FREQ_NM], [RT_RESET_FREQ_CD] = tempDIMCNT.[RT_RESET_FREQ_CD],
[RCVG_RT_DAY_CNT_FRCTN_CD] = tempDIMCNT.[RCVG_RT_DAY_CNT_FRCTN_CD], [CNRT_TENOR_NM] = tempDIMCNT.[CNRT_TENOR_NM], [CNRT_TENOR_CD] = tempDIMCNT.[CNRT_TENOR_CD], [PAYG_FLOTG_RT_IDX_TENOR_NM] = tempDIMCNT.[PAYG_FLOTG_RT_IDX_TENOR_NM], [PAYG_FLOTG_RT_IDX_TENOR_CD] = tempDIMCNT.[PAYG_FLOTG_RT_IDX_TENOR_CD], [PAYG_FLOTG_RT_IDX_NM] = tempDIMCNT.[PAYG_FLOTG_RT_IDX_NM], [PAYG_FLOTG_RT_IDX_CD] = tempDIMCNT.[PAYG_FLOTG_RT_IDX_CD], [RCVG_FLOTG_RT_IDX_TENOR_NM] = tempDIMCNT.[RCVG_FLOTG_RT_IDX_TENOR_NM], [RCVG_FLOTG_RT_IDX_TENOR_CD] = tempDIMCNT.[RCVG_FLOTG_RT_IDX_TENOR_CD], [RCVG_FLOTG_RT_IDX_NM] = tempDIMCNT.[RCVG_FLOTG_RT_IDX_NM], [RCVG_FLOTG_RT_IDX_CD] = tempDIMCNT.[RCVG_FLOTG_RT_IDX_CD], [RGTRG_AUTHY_NM] = tempDIMCNT.[RGTRG_AUTHY_NM], ,GETDATE() AS [ETL_UPDT_DTS], @intETL_BTH_KEY AS [ETL_BTH_KEY] FROM DIM_OTC_CNRT_HIST AS DIMCNT
INNER JOIN #DIM_OTC_CNRT_HIST AS tempDIMCNT
WHERE tempDIMCNT.[CORR_REC_IND] = 1 AND tempDIMCNT.[MyRowCount] = @intCnt1 AND t empDIMCNT.[CNRT_ID] = DIMCNT.[CNRT_ID] AND tempDIMCNT.[CNRT_BANK_KEY] = DIMCNT.[ CNRT_BANK_KEY]
AND tempDIMCNT.[RCRD_STRT_DTS] = DIMCNT.[RCRD_STRT_DTS] AND tempDIMCNT.[RC RD_STRT_DTS] = DIMCNT.[RCRD_END_DTS]
SELECT @intUpdatedCount2 = COUNT(1) FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 1 AND [MyRowCount] = @intCnt1
COMMIT TRANSACTION END
END
SET @intCnt1 = @intCnt1 +1
SET @intInsertedCount = @intInsertedCount+@intInsertedCount1
SET @intUpdatedCount = @intUpdatedCount+@intUpdatedCount1+@intUpdatedCount2 END
@intProcessedCount = @intInsertedCount + @intUpdatedCount END
INSERT INTO [SAMA_STAGE].[dbo].[ERR_OTC_CNRT] ([CNRT_ID] ,[ETL_BTH_KEY] ,[CNRT_BANK_KEY] ,[CNRT_BANK_CD] ,[ERR_RMK_TXT] ,[ETL_CRTD_DTS] ,[ETL_UPDT_DTS]) SELECT [CNRT_ID] ,@intETL_BTH_KEY AS [ETL_BTH_KEY] ,[CNRT_BANK_KEY] ,[CNRT_BANK_CD] ,[ERR_DESC] AS [ERR_RMK_TXT] ,GETDATE AS [ETL_CRTD_DTS] ,NULL AS [ETL_UPDT_DTS] FROM #DIM_OTC_CNRT_HIST WHERE [CORR_REC_IND] = 0 UPDATE DIM_OTC_CNRT_HIST
SET [RCRD_CD_STAT_IND] = 1 WHERE [CNRT_HIST_KEY] IN (
SELECT MAX([CNRT_HIST_KEY]) FROM DIM_OTC_CNRT_HIST GROUP BY [CNRT_KEY],[CNRT_BANK_KEY],[RCRD_STRT_DTS]) END END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN SET @IntErrorFlag = 1
--The transaction is in an uncommittable state. ROLLBACK TRANSACTION; END; IF (XACT_STATE()) = 1 BEGIN SET @IntErrorFlag = 1
--The transaction is committable but some other error has occured COMMIT TRANSACTION;
END;
SET @StrErrorMessage= ERROR_MESSAGE () SET @IntErrorCode=ERROR_NUMBER ()
SELECT @IntErrorFlag AS ErrorFlag,
@intProcessedCount AS ProcessedCount, @intInsertedCount as InsertCount, @intUpdatedCount AS UpdateCount,
CONVERT(Varchar,@IntErrorCode) AS ErrorCode, @StrErrorMessage AS ErrorMessage ,
'spDWHETL_DIM_OTC_CNRT_HIST Failed' as ErrorSrc
END CATCH
IF @IntErrorFlag = 0 BEGIN
SELECT @IntErrorFlag AS ErrorFlag,
@intProcessedCount AS ProcessedCount, @intInsertedCount as InsertCount, @intUpdatedCount AS UpdateCount, CONVERT(Varchar,@IntErrorCode) As ErrorCode, 'A' AS ErrorMessage, 'spDWHETL_DIM_OTC_CNRT_HIST' as ErrorSrc END END
SET NOCOUNT OFF; SET XACT_ABORT OFF; GO
