SQLize Online / PHPize Online  /  SQLtest Online

USE [RC_APP] GO /****** Object: StoredProcedure [dbo].[spLGD_Main] Script Date: 01/07/2024 14:33:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spLGD_Main] -- Add the parameters for the stored procedure here @D_STARTDATE varchar(6), @D_ENDDATE varchar(6), @T_JOB nvarchar(100), @ID_EXECJOB INT, @C_STEP INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET XACT_ABORT ON for error handling SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY /*** STEP 0: Declare parameteres ***/ DECLARE @T_STEPNAME nvarchar(4000), @T_STEPDESC nvarchar(4000), --ALTERADO DE VARCHAR(8000) @C_STATUS INT, @T_STATUS nvarchar(100), @ID_LGDSCENARIO INT, @T_LGDSCENARIODESC nvarchar(100), @ID_LGDTREE INT, @ID_LGDTREEPARMTREE INT, @C_SEGMAPPROACH INT, @C_EXRAPPROACH INT, @F_WOFFLOSSEVENT BIT, @C_REESTRUCTUREDAPPROACH INT, @ID_LGDEXCLUSIONSCENARIO INT, @D_MINREFERENCEDATE varchar(6), @D_MAXREFERENCEDATE varchar(6), @T_INPUTTBL nvarchar(MAX) = 'RC_APP..Account', @C_OUTTBL INT = 0, @C_OTHSEGM INT = 0, @F_LINKREESACCOUNTS BIT = 0, @F_INCLUDEFIRSTMOMENTDEF BIT, @F_NETEADCALCULATION BIT DECLARE @SP_NAME nvarchar(255) = (SELECT DB_NAME() + '..' + OBJECT_NAME(@@PROCID)) EXEC RC_APP..spMain @SP_NAME /*** STEP 1: Get active configuration ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Configuration' SET @T_STEPDESC = 'Getting process active configuration.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_GetActiveConfig SET @F_LINKREESACCOUNTS = ISNULL((SELECT MAX(CASE WHEN F_LinkReestructuredAccounts = 1 THEN 1 ELSE 0 END) FROM LGD_ConfigScenarios), 0) SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 2: Create accounts input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Accounts Input' SET @T_STEPDESC = 'Creating accounts data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputAccounts @D_STARTDATE, @D_ENDDATE -- '201101', '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 3: Create workouts input ***/ IF @F_LINKREESACCOUNTS = 1 BEGIN SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Workouts Input' SET @T_STEPDESC = 'Creating workouts data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputWorkouts @D_STARTDATE, @D_ENDDATE -- '201101', '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END /*** STEP 4: Create segmentation input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Segmentation Input' SET @T_STEPDESC = 'Creating segmentation data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spTreeSegm_RunSegm @T_INPUTTBL, @C_OUTTBL, @D_STARTDATE, @D_ENDDATE, @C_OTHSEGM -- 'RC_APP..TreeSegm_InputAccount', 0, '201101', '201408', 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5: Iterate through scenarios ***/ IF CURSOR_STATUS('global','CursorScenarios')>=-1 BEGIN CLOSE CursorScenarios DEALLOCATE CursorScenarios END DECLARE CursorScenarios CURSOR FAST_FORWARD FOR SELECT DISTINCT ID_LGDScenario, T_LGDScenarioDescription, ID_LGDTree, ID_LGDTreeParmTree, C_SegmApproach, C_ExRApproach, F_WOffLossEvent, C_ReestructuredApproach, ID_LGDExclusionScenario, F_LinkReestructuredAccounts, F_IncludeFirstMomentDefault, F_NetEADCalculation FROM RC_APP..LGD_ConfigScenarios OPEN CursorScenarios FETCH NEXT FROM CursorScenarios INTO @ID_LGDSCENARIO, @T_LGDSCENARIODESC, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_LINKREESACCOUNTS, @F_INCLUDEFIRSTMOMENTDEF, @F_NETEADCALCULATION WHILE @@FETCH_STATUS=0 BEGIN /*** STEP 5.1: Create scenario input ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Scenario Input' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Creating LGD scenario data input.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputScenario @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_INCLUDEFIRSTMOMENTDEF -- 7, 1049, 1048, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.2: Identify LGD Strategies ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - LGD Strategies' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Identifying LGD strategies.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_IdentifyStrategies @D_STARTDATE, @D_ENDDATE, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH -- '201101', '201408', 0, 1, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP IF @F_LINKREESACCOUNTS = 1 BEGIN /*** STEP 5.3: Create scenario input for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Scenario Input' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Creating LGD scenario data input for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CreateInputScenarioWorkouts @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @ID_LGDEXCLUSIONSCENARIO -- 7, 1049, 1048, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.4: Identify LGD Strategies for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts LGD Strategies' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Identifying LGD strategies for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_IdentifyStrategiesWorkouts @D_STARTDATE, @D_ENDDATE, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH -- '201101', '201408', 0, 1, 0 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END /*** STEP 5.5: Calculate Recovery Flows ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Recovery Flows' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating recovery flows.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRecoveryFlows @F_WOFFLOSSEVENT SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP IF @F_LINKREESACCOUNTS = 1 BEGIN /*** STEP 5.6: Calculate Recovery Flows for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Recovery Flows' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating recovery flows for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRecoveryFlowsWorkouts @F_WOFFLOSSEVENT SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.7: Calculate Losses for workouts ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Workouts Losses' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating LGD losses for workouts.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcLossesWorkouts @ID_LGDTREE, @ID_LGDTREEPARMTREE, @F_NETEADCALCULATION -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP END ELSE BEGIN TRUNCATE TABLE LGD_RecoveryFlowsWorkouts TRUNCATE TABLE LGD_RecoveryFlowsByTimeProfileWorkouts END /*** STEP 5.8: Calculate Probs and Losses ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Probs and Losses' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating LGD probabilities and losses.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcProbsAndLosses @ID_LGDTREE, @ID_LGDTREEPARMTREE, @F_NETEADCALCULATION -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.9: Calculate Collateral Indexes Before Repossession ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Idxs to Repossession' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Calculating collateral indexes before repossession.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_CalcRepossessionIdxs @ID_LGDTREE, @ID_LGDTREEPARMTREE -- 1049, 1048 SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP /*** STEP 5.10: Store Outputs ***/ SET @C_STEP = @C_STEP + 1 SET @T_STEPNAME = 'Scenario ' + @T_LGDSCENARIODESC + ' - Output Results' SET @T_STEPDESC = 'Scenario ' + @T_LGDSCENARIODESC + ' - Storing LGD output results.' SET @C_STATUS = 109001 SET @T_STATUS = 'Running...' INSERT INTO RC_APP..Log (IdExecJob, NomeJob, Passo, NomePasso, Estado, Descricao, Status) VALUES(@ID_EXECJOB, @T_JOB, @C_STEP, @T_STEPNAME, @C_STATUS, @T_STEPDESC, @T_STATUS) EXEC RC_APP..spLGD_StoreOutputResults @ID_LGDSCENARIO, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @D_ENDDATE -- 7, 1049, 1048, '201408' SET @C_STATUS = 109002 SET @T_STATUS = 'Done' UPDATE RC_APP..Log SET DtExecFim=GETDATE(), Estado=@C_STATUS, Status=@T_STATUS WHERE IdExecJob=@ID_EXECJOB AND Passo=@C_STEP FETCH NEXT FROM CursorScenarios INTO @ID_LGDSCENARIO, @T_LGDSCENARIODESC, @ID_LGDTREE, @ID_LGDTREEPARMTREE, @C_SEGMAPPROACH, @C_EXRAPPROACH, @F_WOFFLOSSEVENT, @C_REESTRUCTUREDAPPROACH, @ID_LGDEXCLUSIONSCENARIO, @F_LINKREESACCOUNTS, @F_INCLUDEFIRSTMOMENTDEF, @F_NETEADCALCULATION END IF CURSOR_STATUS('global','CursorScenarios')>=-1 BEGIN CLOSE CursorScenarios DEALLOCATE CursorScenarios END END TRY BEGIN CATCH DECLARE @ID_LOG INT, @ErrorMessage nvarchar(4000), @ErrorSeverity INT, @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END GO