October 27, 2014 at 3:25 am
hi all,
when i using storedprocedure to execute in sql server 2012 i am getting this following error,tell me how to solve this issue.
Msg 217, Level 16, State 1, Procedure USP_ComputeAll_NPA, Line 759
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
i need help
thanks & regards
Rajnidas
October 27, 2014 at 3:31 am
Without seeing the procedure, hard to say anything specific. You're getting the error because the procedure is calling itself (recursive). SQL limits recursive calls to 32 levels deep.
Take a look at your proc and why it's calling itself and either rewrite it to remove the recursive calls or ensure that it can't recurse more than 32 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 3:46 am
my proc has given below
----------------------------------
USE [indbank]
GO
/****** Object: StoredProcedure [dbo].[USP_ComputeAll_NPA] Script Date: 10/27/2014 2:59:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[USP_ComputeAll_NPA]
@UPLOAD_DATE DATE,
@ERRORFLAG BIT OUT,
@ERRORMSG VARCHAR(MAX) OUT
AS
BEGIN TRY
DECLARE @TRACING VARCHAR(100)
DECLARE @TOTCOUNT INT
DECLARE @COUNT INT
DECLARE @HMCOUNT INT
DECLARE @RESULT VARCHAR(MAX)
-------------------DEBIT CREDIT VARIABLES----------------------
-------------------DEBIT CREDIT VARIABLES----------------------
--if exists (select 1 from sys.tables where object_id = object_id('dbo.#TEMP1'))
drop table #TEMP1 ;
CREATE TABLE #TEMP1
(
LEID VARCHAR(100),
RM_CODE NVARCHAR(50),
CLIENTNAME NVARCHAR(255),
CRG_CODE NVARCHAR(50),
TEAMID INT,
FILETYPE_ID NVARCHAR(100),
FILEID VARCHAR(255),
FILE_DESCRIPTION VARCHAR(MAX),
NPA_AGING INT
)
PRINT 'TEMP1 ALTERD'
SET @TRACING = 'TEMP1 ALTERD'
SET @TOTCOUNT = 22
SET @ERRORMSG = ''
SET @ERRORFLAG = 1
PRINT @ERRORFLAG
PRINT '@TOTCOUNT :' + CAST(@TOTCOUNT AS VARCHAR(10))
------------------------------CHECK DESCRIPENCY IN FILESTATUS--------------------------------------------------------------
SET @RESULT = (SELECT FILE_TYPE_NAME FROM FILE_MASTER WHERE FILE_TYPE_ID IN(
SELECT DISTINCT FILETYPEID FROM FILESTATUS
WHERE FILETYPEID IN (SELECT FILE_TYPE_ID FROM FILE_MASTER) AND CONVERT(DATE,REPORTDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121) AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND
FILEID IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)) FOR XML PATH (''))
SET @RESULT = SUBSTRING(REPLACE(REPLACE(RTRIM(@RESULT),'<FILE_TYPE_NAME>',''),'</FILE_TYPE_NAME>',','),1,LEN(REPLACE(REPLACE(RTRIM(@RESULT),'<FILE_TYPE_NAME>',''),'</FILE_TYPE_NAME>',':'))-1)
PRINT @RESULT
IF @RESULT IS NOT NULL
BEGIN
SET @ERRORMSG ='NO COMPUTATION PERFORMED BECAUSE GIVEN FILE HAVING DESCRIPENCY : '+ @RESULT
SET @ERRORFLAG = 0
RETURN
END
ELSE
BEGIN
SELECT @COUNT = COUNT(DISTINCT FILETYPEID) FROM FILESTATUS
WHERE FILETYPEID IN (SELECT FILE_TYPE_ID FROM FILE_MASTER) AND CONVERT(DATE,REPORTDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 ---AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK WHERE CONVERT(DATE,UPLOAD_DATE,121) = CONVERT(DATE,'2014-10-17',121))
IF @TOTCOUNT <> @COUNT
BEGIN
PRINT '@COUNT : '+ CAST(@COUNT AS VARCHAR(10))
PRINT 'CHECK FILES IN HOLIDAY MASTER'
SELECT @HMCOUNT = COUNT(1) FROM SCB_EMPDM.DBO.HOLIDAY_MASTER AS HM
INNER JOIN SCB_EMPDM.DBO.HOLIDAY_FILETYPEMAPPING AS HFTM ON HFTM.HOLIDAY_ID = HM.ID
WHERE HFTM.FILE_TYPE_ID NOT IN(SELECT DISTINCT FILETYPEID FROM FILESTATUS
WHERE FILETYPEID IN (SELECT FILE_TYPE_ID FROM FILE_MASTER) AND CONVERT(DATE,REPORTDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0)AND HM.DATE = CONVERT(DATE,@UPLOAD_DATE,121)
PRINT '@@HMCOUNT : '+ CAST(@HMCOUNT AS VARCHAR(10))
IF @TOTCOUNT <> (@COUNT + @HMCOUNT)
BEGIN
SET @RESULT =
(SELECT FILE_TYPE_NAME FROM FILE_MASTER WHERE FILE_TYPE_ID NOT IN(
SELECT TAB.FILE_TYPE_ID FROM
(SELECT HFTM.FILE_TYPE_ID FROM SCB_EMPDM.DBO.HOLIDAY_MASTER AS HM
INNER JOIN SCB_EMPDM.DBO.HOLIDAY_FILETYPEMAPPING AS HFTM ON HFTM.HOLIDAY_ID = HM.ID
WHERE CONVERT(DATE,DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121))TAB)
AND FILE_TYPE_ID NOT IN(SELECT DISTINCT FILETYPEID FROM FILESTATUS
WHERE FILETYPEID IN (SELECT FILE_TYPE_ID FROM FILE_MASTER) AND CONVERT(DATE,REPORTDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0)FOR XML PATH (''))
SET @RESULT = SUBSTRING(REPLACE(REPLACE(RTRIM(@RESULT),'<FILE_TYPE_NAME>',''),'</FILE_TYPE_NAME>',','),1,LEN(REPLACE(REPLACE(RTRIM(@RESULT),'<FILE_TYPE_NAME>',''),'</FILE_TYPE_NAME>',':'))-1)
PRINT @RESULT
SET @ERRORMSG =' GIVEN FILES ARE NOT MAINTAINED IN HOLIDAY MASTER: '+ @RESULT
SET @ERRORFLAG = 0
RETURN
END
END
END
--------------------------------- INSERT DEBIT CREDIT VALIDATION 25-10-2014-------------------------------------
--------------------------------- INSERT DEBIT CREDIT VALIDATION 25-10-2014-------------------------------------
----------------------INSERT INTO [COMPUTELOG]-----------------------------------------------------------------
INSERT INTO [SCB_NPA].[DBO].[COMPUTELOG] VALUES(CONVERT(DATE,GETDATE(),121))
----------------------INSERT INTO [COMPUTELOG]-----------------------------------------------------------------
PRINT 'TOTAL FILES : ' + CAST(@TOTCOUNT AS VARCHAR(10))
SET @TRACING = 'TOTAL FILES : ' + CAST(@TOTCOUNT AS VARCHAR(10))
-------------------------------------------FAILED TRADE---------------------------------------------------------
PRINT 'STARTING FILES FROM MVC APPLICATION'
SET @TRACING = 'STARTING FILES FROM MVC APPLICATION'
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 16 AS 'FT', FTRU.ID, FM.FILE_TYPE_DESCRIPTION, FTRU.AGEING_DAYS FROM FAIL_TRADE_REPORT_UPLOAD AS FTRU WITH(NOLOCK)
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[ACCOUNTNUMBER]COLLATE DATABASE_DEFAULT = FTRU.COUNTERPARTY_ID COLLATE DATABASE_DEFAULT
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=16
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
FTRU.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=16 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.AGEING_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=16)
---------------------------UPDATE FILESTATUS--------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 16 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS ='S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------UPDATE FILESTATUS--------------------------------------------------
PRINT '16'
SET @TRACING = '16'
-------------------------------------------FAILED TRADE---------------------------------------------------------
----------------------------------------FX FAILED TRADE REPORT----------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME,17 AS 'FT',FXFT.ID,FM.FILE_TYPE_DESCRIPTION, FXFT.AGEINGDAYS FROM [FXFAILEDTRADEREPORT_UPLOAD] AS FXFT WITH(NOLOCK)
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.ACCOUNTNUMBER = CAST(FXFT.COUNTERPARTYID AS NVARCHAR(100))
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=17
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
FXFT.FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 17 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0 AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.AGEINGDAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 17)
---------------------------UPDATE FILESTATUS--------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 17 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)--- AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0)
---------------------------UPDATE FILESTATUS--------------------------------------------------
PRINT '17'
SET @TRACING = '17'
----------------------------------------FX FAILED TRADE REPORT----------------------------------------
----------------------------------------INDIA PAST DUE FINAL------------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(
SELECT TBL1.[LEID], D.RM_CODE,D.CUSTOMER_NAME, D.CRG_CODE,AM.TEAMNAME, 18 AS 'FT', PDFFU.ID, FM.FILE_TYPE_DESCRIPTION, PDFFU.AGEING_CALENDER_DAYS FROM
[SCB_NPA].[DBO].[PASS_DUE_FINAL_FILE_UPLOAD] AS PDFFU WITH(NOLOCK)
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[ACCOUNTNUMBER] = CAST(PDFFU.COUNTERPARTY_ID AS NVARCHAR(100))
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=18
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
PDFFU.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=18 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0 AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.AGEING_CALENDER_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=18)
---------------------------UPDATE FILESTATUS--------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 18 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)--- AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0)
---------------------------UPDATE FILESTATUS--------------------------------------------------
PRINT '18'
SET @TRACING = '18'
----------------------------------------INDIA PAST DUE FINAL------------------------------------------
---------------------------- TO UPDATE THE OUTSTANDING COLUMNN IN OVERDUE_STOCK_STATEMENT_UPLOAD---------------------------
UPDATE OVERDUE_STOCK_STATEMENT_UPLOAD SET OUTSTANDINGFOUND = 1 WHERE ID IN (SELECT DISTINCT TBL.ID
FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 19 AS 'FT', OVSU.ID, FM.FILE_TYPE_DESCRIPTION, OVSU.NOOF_OVERDUE_DAYS
FROM OVERDUE_STOCK_STATEMENT_UPLOAD AS OVSU WITH (NOLOCK)
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[LEID] = CAST(OVSU.LEGALID AS NVARCHAR(100))
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID = 19
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
OVSU.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=19 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0 AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.NOOF_OVERDUE_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=19)
AND
TBL.[LEID]COLLATE DATABASE_DEFAULT IN (SELECT TBL2.LEID COLLATE DATABASE_DEFAULT FROM
(SELECT DISTINCT SCI_REF_NO AS LEID , '[EBBS_OUTSTANDING_UPLOAD]' AS TABLENAME FROM [EBBS_OUTSTANDING_UPLOAD] WHERE SCI_REF_NO IS NOT NULL AND CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
UNION
SELECT DISTINCT CAST(CUSTOMER_LEID AS VARCHAR), 'ACBS_FULL_LIST_UPLOAD' AS TABLENAME FROM ACBS_FULL_LIST_UPLOAD WHERE CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
UNION
SELECT DISTINCT LEGAL_ENTITY_ID, '[LTP_LIMIT_UTILIZATION_UPLOAD]' FROM [LTP_LIMIT_UTILIZATION_UPLOAD] WHERE LEGAL_ENTITY_ID IS NOT NULL AND CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121))TBL2))
---------------------------- TO UPDATE THE OUTSTANDING COLUMNN IN OVERDUE_STOCK_STATEMENT_UPLOAD---------------------------
----------------------------------------OVERDUE STOCK STATEMENT---------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID,FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 19 AS 'FT', OVSU.ID, FM.FILE_TYPE_DESCRIPTION, OVSU.NOOF_OVERDUE_DAYS
FROM OVERDUE_STOCK_STATEMENT_UPLOAD AS OVSU WITH (NOLOCK)
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[LEID] = CAST(OVSU.LEGALID AS NVARCHAR(100))
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=19
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
OVSU.FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 19 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND OVSU.OUTSTANDINGFOUND = 1)AS TBL
WHERE TBL.NOOF_OVERDUE_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 19)
----------------------------------------OVERDUE STOCK STATEMENT----------------------------------------------------------------
-----------------------------------------------FILESTATUS----------------------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 19 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)--- AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0)
-----------------------------------------------FILESTATUS----------------------------------------------------------------------
PRINT '19'
SET @TRACING = '19'
----------------------------------------TO UPDATE THE OUTSTANDING COLUMNN IN BBTL STOCK OVERDUE--------------------------------------------
UPDATE STOCK_OVERDUE_REPORT_CTA_UPLOAD SET OUTSTANDINGFOUND = 1 WHERE ID IN (
SELECT DISTINCT TBL.ID FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 20 AS 'FT', SORCTAU.ID, FM.FILE_TYPE_DESCRIPTION, SORCTAU.NO_OF_OVERDUE_DAYS FROM [SCB_NPA].[DBO].[STOCK_OVERDUE_REPORT_CTA_UPLOAD] AS SORCTAU
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[LEID]COLLATE DATABASE_DEFAULT = SORCTAU.LEGAL_ID COLLATE DATABASE_DEFAULT
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=20
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
SORCTAU.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=20 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0 AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.NO_OF_OVERDUE_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 20)
AND
TBL.[LEID]COLLATE DATABASE_DEFAULT IN (SELECT TBL2.LEID COLLATE DATABASE_DEFAULT FROM
(SELECT DISTINCT SCI_REF_NO AS LEID , '[EBBS_OUTSTANDING_UPLOAD]' AS TABLENAME FROM [EBBS_OUTSTANDING_UPLOAD] WHERE SCI_REF_NO IS NOT NULL AND CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
UNION
SELECT DISTINCT CAST(CUSTOMER_LEID AS VARCHAR), 'ACBS_FULL_LIST_UPLOAD' AS TABLENAME FROM ACBS_FULL_LIST_UPLOAD WHERE CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
UNION
SELECT DISTINCT LEGAL_ENTITY_ID, '[LTP_LIMIT_UTILIZATION_UPLOAD]' FROM [LTP_LIMIT_UTILIZATION_UPLOAD] WHERE LEGAL_ENTITY_ID IS NOT NULL AND CONVERT(DATE,UPLOADEDDATE,121) = CONVERT(DATE,@UPLOAD_DATE,121))TBL2))
----------------------------------------TO UPDATE THE OUTSTANDING COLUMNN IN BBTL STOCK OVERDUE--------------------------------------------
----------------------------------------BBTL STOCK OVERDUE--------------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(
SELECT TBL1.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 20 AS 'FT',SORCTAU.ID,FM.FILE_TYPE_DESCRIPTION,SORCTAU.NO_OF_OVERDUE_DAYS FROM [SCB_NPA].[DBO].[STOCK_OVERDUE_REPORT_CTA_UPLOAD] AS SORCTAU
INNER JOIN
(SELECT B.[LEID], B.ACCOUNTNUMBER FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B
INNER JOIN
(SELECT ACCOUNTNUMBER,MAX(ID) AS IDD
FROM SCB_EMPDM.DBO.LEID_MASTER
GROUP BY ACCOUNTNUMBER)TBL ON TBL.IDD = B.ID)TBL1 ON TBL1.[LEID]COLLATE DATABASE_DEFAULT = SORCTAU.LEGAL_ID COLLATE DATABASE_DEFAULT
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID = 20
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
TBL1.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
SORCTAU.FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 20 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND SORCTAU.OUTSTANDINGFOUND = 1 )AS TBL
WHERE TBL.NO_OF_OVERDUE_DAYS >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 20)
----------------------------------------BBTL STOCK OVERDUE--------------------------------------------
----------------------------------------FILESTATUS-------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 20 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
----------------------------------------FILESTATUS-------------------------------------------
PRINT '20'
SET @TRACING = '20'
--------------------------------------------------------------------------------------------------------
PRINT 'STARTING FILES FROM CONVERTOR'
SET @TRACING = 'STARTING FILES FROM CONVERTOR'
------------------------------------------INTEREST NOT SERVICED OD---------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(
SELECT B.[LEID], D.RM_CODE, D.CUSTOMER_NAME, D.CRG_CODE, AM.TEAMNAME, 21 AS 'FT', C.ID,FM.FILE_TYPE_DESCRIPTION, C.AGING FROM [SCB_EMPDM].[DBO].[LEID_MASTER] AS B WITH(NOLOCK)
INNER JOIN [SCB_NPA].[DBO].[TBL_INTEREST_DEBIT] AS C ON CAST(C.SCI_REFERENCE_NO AS NVARCHAR(200)) COLLATE DATABASE_DEFAULT = B.[LEID] COLLATE DATABASE_DEFAULT
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=21
INNER JOIN [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D ON
B.[LEID] COLLATE DATABASE_DEFAULT = D.[LE_ID]COLLATE DATABASE_DEFAULT AND CONVERT(DATE,D.UPLOAD_DATE,121) = CONVERT(DATE,@UPLOAD_DATE,121)
LEFT JOIN [SCB_EMPDM].[DBO].[ARM_MASTER] AS AM ON AM.ARM_CODE = D.RM_CODE
WHERE
C.FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 21 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0))AS TBL
WHERE TBL.AGING >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=21)
------------------------------------------INTEREST NOT SERVICED OD---------------------------------------
------------------------------------------FILESTATUS---------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 21 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0 AND UNSUCCESSITEMS = 0 AND ADDEDITEMS > 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
------------------------------------------FILESTATUS---------------------------------------
---------------------------------------------PASTDUE-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 1 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID = 1
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 1 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS ='S' AND ISACTIVE_NPA = 0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 1)
---------------------------------------------PASTDUE-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 1 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS ='S' AND ISACTIVE_NPA = 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C1'
SET @TRACING = 'C1'
---------------------------------------------LTPEXCESS-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 2 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID = 2
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 2 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 2)
---------------------------------------------LTPEXCESS-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA = 1 WHERE
FILEID = (SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 2 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C2'
SET @TRACING = 'C2'
---------------------------------------------LOAN-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID, RM_CODE, CLIENTNAME, CRG_CODE, TEAMID, FILETYPE_ID, FILEID, FILE_DESCRIPTION, NPA_AGING)
SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 3, CD.ID,FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=3
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID = 3 AND REPORTDATE = @UPLOAD_DATE AND FILESTATUS = 'S' AND ISACTIVE_NPA = 0)AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)--- AND UNSUCCESSITEMS=0 AND ADDEDITEMS > 0)
AND NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID = 3)
---------------------------------------------LOAN-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=3 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------
PRINT 'C3'
SET @TRACING = 'C3'
---------------------------------------------OD-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 4 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=4
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=4 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=4)
---------------------------------------------OD-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=4 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C4'
SET @TRACING = 'C4'
---------------------------------------------CSA-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 5 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=5
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=5 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=5)
---------------------------------------------CSA-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=5 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C5'
SET @TRACING = 'C5'
---------------------------------------------DAILY-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 6 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=6
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=6 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=6)
---------------------------------------------DAILY-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=6 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C6'
SET @TRACING = 'C6'
---------------------------------------------IMEX341-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 7 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=7
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=7 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=7)
---------------------------------------------IMEX341-----------------------------------------------------------
---------------------------------------------FILESTATUS---------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=7 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0)AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C7'
SET @TRACING = 'C7'
---------------------------------------------NOOFDAYSPASTDUE------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 8 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=8
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=8 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=8)
---------------------------------------------NOOFDAYSPASTDUE------------------------------------------------------
---------------------------------------------FILESTATUS---------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=8 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS---------------------------------------------------------
PRINT 'C8'
SET @TRACING = 'C8'
---------------------------------------------FACILITYEXCESS-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 9 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=9
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=9 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=9)
---------------------------------------------FACILITYEXCESS-----------------------------------------------------------
---------------------------------------------FILESTATUS---------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=9 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS---------------------------------------------------------
PRINT 'C9'
SET @TRACING = 'C9'
---------------------------------------------OPTPASTDUE-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 10 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=10
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=10 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=10)
---------------------------------------------OPTPASTDUE-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=10 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C10'
SET @TRACING = 'C10'
---------------------------------------------GSAM TRADE PASTDUE-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 11 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=11
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=11 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=11)
---------------------------------------------GSAM TRADE PASTDUE-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=11 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
PRINT 'C11'
SET @TRACING = 'C11'
---------------------------------------------SCMS-----------------------------------------------------------
INSERT INTO #TEMP1 (LEID,RM_CODE,CLIENTNAME,CRG_CODE,TEAMID,FILETYPE_ID,FILEID,FILE_DESCRIPTION,NPA_AGING)
SELECT * FROM
(SELECT CD.LE_ID, CD.ARM_CODE, CD.CLIENT_NAME, CD.CREDIT_RISK_GRADE, CD.TEAM, 12 AS 'FT', CD.ID, FM.FILE_TYPE_DESCRIPTION, CD.NO_OF_DAYS_SINCE_EXCESSPAST_DUE FROM [SCB_NPA].[DBO].[CONSOLIDATED_DATA] CD
INNER JOIN FILE_MASTER FM ON FM.FILE_TYPE_ID=12
WHERE
CD.FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=12 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND CD.FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK))AS TBL
WHERE TBL.NO_OF_DAYS_SINCE_EXCESSPAST_DUE >= (SELECT TOP 1 VALID_DAYS FROM FILE_MASTER WHERE FILE_TYPE_ID=12)
---------------------------------------------SCMS-----------------------------------------------------------
---------------------------------------------FILESTATUS-----------------------------------------------------------
UPDATE FILESTATUS SET ISACTIVE_NPA=1 WHERE
FILEID=(SELECT MAX(FILEID) FROM FILESTATUS WHERE FILETYPEID=12 AND REPORTDATE=@UPLOAD_DATE AND FILESTATUS='S' AND ISACTIVE_NPA=0) AND FILEID NOT IN (SELECT DISTINCT FILEID FROM CONSOLIDATED_DATA_TEMPPARK)
---------------------------------------------FILESTATUS-----------------------------------------------------------
---------------------------------- INSERT INTO MAINTABLE FROM TEMP TABLE-----------------------
PRINT 'START INSERTING DATA INTO MAIN MASTER FROM TEMP1'
SET @TRACING = 'START INSERTING DATA INTO MAIN MASTER FROM TEMP1'
--SELECT * into Rajnidas FROM #TEMP1
--select * from #TEMP1
--INSERT INTO [MAIN_MASTER](LEID,CLIENTNAME,ARM_CODE,CRG_CODE,TEAMID,CONTINUED_FLAG,NPA_AGING,WORKFLOW_FLAG,EFFECTIVE_DATE)
INSERT INTO [MAIN_MASTER](LEID, TP_FILE_REF, CLIENTNAME, ARM_CODE, CRG_CODE, TEAMID, CONTINUED_FLAG, NPA_AGING, WORKFLOW_FLAG, EFFECTIVE_DATE, ON_ACCOUNT_OF)
SELECT
C.LEID
--,STUFF((SELECT DISTINCT '| '
-- + IC.FILETYPE_ID +':'+ IC.FILEID FROM #TEMP1 AS IC
-- WHERE IC.LEID = C.LEID
-- FOR XML PATH(''), TYPE).VALUE('.','NVARCHAR(MAX)'), 1, 2, '')
--,'0' AS TP_REF_FILE
,STUFF((
SELECT DISTINCT '| '
+ IC.FILETYPE_ID +':'+ IC.FILEID FROM #TEMP1 AS IC
WHERE IC.LEID = C.LEID
FOR XML PATH (''))
,1,2,'') AS TP_REF_FILE
,C.CLIENTNAME
, C.RM_CODE AS RM_CODE,
--(SELECT TOP 1 D.RM_CODE FROM [SCB_EMPDM].[DBO].[SCI_UPLOAD] AS D WHERE LE_ID = D.[LE_ID]
--AND D.UPLOAD_DATE = @UPLOAD_DATE
--ORDER BY 1 DESC ) AS RM_CODE ,
C.CRG_CODE,
C.TEAMID,
1 AS CONTINUED_FLAG,
MAX(C.NPA_AGING),
--0 AS NPA_AGING,
10 AS WORKFLOW_FLAG
,@UPLOAD_DATE
,STUFF((SELECT ', '
+ IC.FILE_DESCRIPTION FROM #TEMP1 AS IC
WHERE IC.LEID = C.LEID
GROUP BY IC.FILE_DESCRIPTION
FOR XML PATH('')),1,2,'') AS ON_ACCOUNT_OF
FROM #TEMP1 AS C
--WHERE D.UPLOAD_DATE = @UPLOAD_DATE
GROUP BY C.LEID,C.CLIENTNAME,C.RM_CODE,C.CRG_CODE,C.TEAMID--,C.NPA_AGING
ORDER BY C.LEID,C.CLIENTNAME,C.RM_CODE,C.CRG_CODE
PRINT 'MAIN_MASTER'
SET @TRACING = 'MAIN_MASTER'
---COMMIT TRANSACTION Tran1
END TRY
BEGIN CATCH
---ROLLBACK TRANSACTION Tran1
SET @ERRORFLAG = 0
DECLARE @SQLCODE INT
DECLARE @ERRMSG VARCHAR(MAX)
SELECT @SQLCODE = ERROR_NUMBER(), @ERRMSG =ERROR_MESSAGE();
INSERT INTO SCB_EMPDM.[DBO].[TBL_EXCEPTION_LOGS] (EX_LOG_DATE, EX_LOG_TYPE,EX_LOG_MESSAGE)
VALUES (GETDATE(),'D',@ERRMSG +''+@TRACING);
END CATCH
DECLARE @MSG AS VARCHAR(MAX)
DECLARE @FLAG AS BIT
EXEC USP_COMPUTEALL_NPA '2014-10-27',@FLAG OUTPUT, @MSG OUTPUT
SELECT @FLAG
SELECT @MSG
October 27, 2014 at 4:02 am
Look at the last 5 lines of what you posted. You're calling the procedure itself there. There's no check for how many times it calls itself (nor any obvious reason why there's a recursive call at all), hence the procedure will call itself the 32 times and will fail.
DECLARE @MSG AS VARCHAR(MAX)
DECLARE @FLAG AS BIT
EXEC USP_COMPUTEALL_NPA '2014-10-27',@FLAG OUTPUT, @MSG OUTPUT
SELECT @FLAG
SELECT @MSG
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 4:31 am
hmmm seems to me that execution SP code is not actually the part of the SP itself, comment out the last line from the procedure,
and its always a good practice to use GO statement at the end of the procedure to avoid things like that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply