Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed.

Question

Post reply

Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed.

Anthonio

Ten Centuries

Points: 1099
More actions
April 2, 2007 at 11:49 pm

#119275

Hi All

I am a Junior and have a problem.We are running a Financial Proc during the night and I came across this Error when it failed. Scary part is that all record inserted into the database but the Step in the Job failed

Can you help please
"Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed."
Here's the Proc

DECLARE @Commandline VARCHAR(500),
  @IntRootDirectory VARCHAR(300),
  @ERRORTEXT VARCHAR(500),
  @error INT,
  @FileName VARCHAR(50),
  @rowcounter INT,
  @V_LOANREFNO VARCHAR(50),
  @V_STATUS VARCHAR(50),
  @v_localfolder VARCHAR (350),
  @v_debtfolder VARCHAR (350),
  @v_filename VARCHAR(300),
  @v_financialfolder VARCHAR (350),
  @dbusername VARCHAR(50),
  @v_query VARCHAR (350),
  @databaselog VARCHAR (350),
  @text VARCHAR (1000),
  @ignoredfiles VARCHAR (1000)
--Get the location of the interfaceroot directory
EXEC @error=DBO.TM_GET_PROPERTIES @IntRootDirectory OUTPUT
IF @Error <> 0
BEGIN
SET @ERRORTEXT = 'Error in running the TM_GET_PROPERTIES procedure'
GOTO ERR_HANDLER
END

--Get the database username
EXEC @error=DBO.GETDBUSERNAME @dbusername OUTPUT
IF @Error <> 0
BEGIN
SET @ERRORTEXT = 'Error in running the GETDBUSERNAME procedure'
GOTO ERR_HANDLER
END
SET @databaselog=@IntRootDirectory+'\Database.log'
SET @text = convert(varchar, getdate(),100)+' Beginning AB_AUTOMATE_BATCH to process financial and overduebalance interfaces..'
EXEC DBO.logtext @text, @databaselog
SET @v_debtfolder = @IntRootDirectory+'/debt'
SET @v_financialfolder = @IntRootDirectory+'/financial'
--Import the local financial file names
EXEC @error = DBO.GET_DIR_FILE_LIST @v_financialfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Financial folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a financial stub format and execute them
DECLARE CURFINANCIALS CURSOR FOR
SELECT 'EXEC AB_FINANCIAL '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'FINANCIAL'
OPEN CURFINANCIALS
FETCH NEXT FROM CURFINANCIALS INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  exec (@v_query)
  FETCH NEXT FROM CURFINANCIALS INTO @v_query
END
CLOSE CURFINANCIALS
DEALLOCATE CURFINANCIALS
SET @text = 'Financial Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
--Import the overdue balance interface file names and execute
EXEC @error = DBO.GET_DIR_FILE_LIST @v_debtfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Debt folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a overdue balance stub format and execute them
DECLARE CURODBALS CURSOR FOR
SELECT 'EXEC AB_OVERDUE_BALANCES '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'OVERDUEBALANCE'
OPEN CURODBALS
FETCH NEXT FROM CURODBALS INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  exec (@v_query)
  FETCH NEXT FROM CURODBALS INTO @v_query
END
CLOSE CURODBALS
DEALLOCATE CURODBALS
SET @text = 'Overdue Balances Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
--Import the repopulation interface file names and execute
EXEC @error = DBO.GET_DIR_FILE_LIST @v_financialfolder,0
IF @error <> 0
BEGIN
SET @errortext = 'Error in calling procedure GET_DIR_FILE_LIST for Financial folder'
GOTO ERR_HANDLER
END
--Runthrough each file that matches a repopulation stub format and execute them
DECLARE CURREPOP CURSOR FOR
SELECT 'EXEC AB_RECONCILIATION '''+ PATH+ ''''
FROM FilesInDirectory A
JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB
AND B.INTERFACENAME = 'REPOPULATION'
OPEN CURREPOP
FETCH NEXT FROM CURREPOP INTO @v_query
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  exec (@v_query)
  FETCH NEXT FROM CURREPOP INTO @v_query
END
CLOSE CURREPOP
DEALLOCATE CURREPOP
SET @text = 'Repopulation Interfaces uploaded'
EXEC DBO.logtext @text, @databaselog
SET @text = convert(varchar, getdate(),100)+' AB_AUTOMATE_BATCH generation complete'
EXEC DBO.logtext @text, @databaselog

RETURN(0)
ERR_HANDLER:
   IF (@ERRORTEXT != '')
BEGIN
SET @text = convert(varchar, getdate(),100)+' Error in running overduebalances, financial and repopulation interfaces: '+@ERRORTEXT
EXEC DBO.logtext @text, @databaselog
RETURN (1)
END -- Error Handler

GO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Which line is producing the error?

N 56°04'39.16"
E 12°55'05.25"

Anthonio Ten Centuries Points: 1099 More actions · Answer 2

Anthonio

Ten Centuries

Points: 1099

April 3, 2007 at 12:33 am

#698222

Can you please help

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Not without more information from you.

I don't like to resort to my crystal ball...

N 56°04'39.16"
E 12°55'05.25"

Anthonio Ten Centuries Points: 1099 More actions · Answer 4

Sorry

It's when we start with this step below. Will this maybe help ?

DECLARE CURFINANCIALS CURSOR FOR

SELECT 'EXEC AB_FINANCIAL '''+ PATH+ ''''

FROM FilesInDirectory A

JOIN INTERFACEFILENAMES B ON A.PATH LIKE FILESTUB

AND B.INTERFACENAME = 'FINANCIAL'

OPEN CURFINANCIALS

FETCH NEXT FROM CURFINANCIALS INTO @v_query

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec (@v_query)

FETCH NEXT FROM CURFINANCIALS INTO @v_query

END

CLOSE CURFINANCIALS

DEALLOCATE CURFINANCIALS

SET @text = 'Financial Interfaces uploaded'

EXEC DBO.logtext @text, @databaselog

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

What do stored procedure AB_FINANCIAL perform?

N 56°04'39.16"
E 12°55'05.25"

Anthonio Ten Centuries Points: 1099 More actions · Answer 6

It take's financial transactions from a Txt file and insert's it into a Table

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Somehow one or both of the table "FilesInDirectory" or "INTERFACEFILENAMES" has it's schema changed during execution.

N 56°04'39.16"
E 12°55'05.25"

Anthonio Ten Centuries Points: 1099 More actions · Answer 8

Thanks,

But how do I locate the FilesInDirectory or INTERFACEFILENAMES that changed ?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 9

Did you read this article posted about 2 years ago?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=247574

Anthonio Ten Centuries Points: 1099 More actions · Answer 10

Hi

Just went through it now only. But I still don't know what to do ?

Ross McMicken SSCarpal Tunnel Points: 4435 More actions · Answer 11

We had this problem when there was a SET IDENTITY INSERT ON statement inside the cursor. Apparently, there are some non-obvious actions that are considered schema changes.

Anthonio Ten Centuries Points: 1099 More actions · Answer 12

I am receing an Error.

60118 row(s) affected)

(17904 row(s) affected)

(0 row(s) affected)

,,,

0,0,0,0

(1 row(s) affected)

Server: Msg 203, Level 16, State 2, Line 239

The name '

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = '01'

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top 0 * From ABTRACERSNEWMATTERS where jurisdiction = 'N/A') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

--================================...

Please have a look at the code below

--Create Daily files of new matters and changed records:

--Process: After morning interfaces have been processed, run the following:

--Step 1: Run the following SQL:

begin

TRUNCATE TABLE ABTRACERSDATA_TEMP

insert into ABTRACERSDATA_TEMP

select DISTINCT

c.idnumber,

b.loanrefno,

c.firstname1,

c.surname,

c.title,

c.employercd2,

c.employercd2 as CompanyDescription,

a.balance,

convert(varchar, LoanStartDate,111) as LoanStartDate,

CONVERT(VARCHAR,(GETDATE() + (b.remainingterm * 31)),111) AS END_DATE,

(case when b.instalmentagreed is not null then b.instalmentagreed else b.instalment end),

(select case when dbo.NUMBERTOWORDS (b.instalmentagreed) is null then

(select case when dbo.NUMBERTOWORDS (b.instalment) is null then 'Two Hundred And Fifty Rands Zero Zero Cents'

ELSE dbo.NUMBERTOWORDS (b.instalment) end)

ELSE dbo.NUMBERTOWORDS (b.instalmentagreed) end) as AMOUNTINWORDS,

c.employercd1,

c.employeeclockno,

e.DLDWFIRSTREGION as region,

e.DLDWFIRSTSUBREGION as subregion,

'N/A' as jurisdiction,

c.email,

convert(varchar, getdate(), 111) as TodayDate,

(case when b.previousreasoncode = '182' then 'Confirmed not Deceased by Home Affairs'

when b.previousreasoncode = '184' then 'Admin Order Status Unknown'

when b.previousreasoncode = '531' then 'Insurance Claim - Insufficient Info'

when b.previousreasoncode = '534' then 'Insurance Claim - Insufficient Info'

when b.previousreasoncode = '841' then 'Admin Order - Insufficient Info'

else '' END)

AS comments,

(case when b.previousreasoncode = '182' then 'Obtain Signed Section 58'

when b.previousreasoncode = '184' then 'Obtain Admin Order application, Court Order, List of Creditors or Signed Section 58 with payment arrangement'

when b.previousreasoncode = '531' then 'Obtain Retrenchment letter, sworn affidavit, copy of ID and 6 months bank statement, if no bank statement it should be stipulated in affidavit.'

when b.previousreasoncode = '534' then 'Obtain Retrenchment letter, sworn affidavit, copy of ID and 6 months bank statement, if no bank statement it should be stipulated in affidavit.'

when b.previousreasoncode = '841' then 'Obtain Signed Section 58'

else '' END)

AS specialinstructions,

'' as alchemyindicator,

'' as insurancedeliverable,

(case when loanstartdate < '2004-06-24' then 15 else APR end) AS ContractInterestRate,

b.lastpaymentdate,

b.lastpaymentamount,

c.nokfullname,

b.loancode1,

getdate () as date_updated_to_law

from accounts a (NOLOCK), accountdetails b (NOLOCK), customerdetails c (NOLOCK),datawash2 e (NOLOCK)--, employers d

where

a.id = b.accounts1

and

a.customerid = c.customers1

and

a.id = e.accounts1

--c.employercd2 = d.e01subgroup

--and

and

b.reasoncode1 IN ('800','811','890')

and

a.status <> '1'

end

-- 1.102494

-- 2.102494

begin

DELETE FROM ABTRACERSDATA_TEMP

WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)

AND LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSADDRESSES)

AND LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSTELEPHONES)

end

--step 2: Identify New Matters

begin

TRUNCATE TABLE ABTRACERSNEWMATTERS

INSERT INTO ABTRACERSNEWMATTERS

SELECT DISTINCT

IDNUMBER,

LOANREFNO,

FIRSTNAME1,

SURNAME,

TITLE,

EMPLOYERCD2,

companydescription,

BALANCE,

LoanStartDate,

END_DATE,

INSTALMENTAGREED,

amountinwords,

EMPLOYERCD1,

EMPLOYEECLOCKNO,

region,

subregion,

jurisdiction,

EMAIL,

TodayDate,

comments,

specialinstructions,

alchemyindicator,

insurancedeliverable,

contractinterestrate,

loancode1

FROM ABTRACERSDATA_TEMP

WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)

and LOANREFNO IN (SELECT LOANREFNO FROM ABTRACERSADDRESSES)

OR

LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)

and LOANREFNO IN (SELECT LOANREFNO FROM ABTRACERSTELEPHONES)

end

--(497 row(s) affected)

-- (497 row(s) affected)

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

begin

update ABTRACERSNEWMATTERS

set jurisdiction = '03'

where region = 'Eastern Cape'

update ABTRACERSNEWMATTERS

set jurisdiction = '06'

where region in ('Central','Free State')

update ABTRACERSNEWMATTERS

set jurisdiction = '07'

where region = 'Western Cape'

update ABTRACERSNEWMATTERS

set jurisdiction = '08'

from ABTRACERSNEWMATTERS a left join accountdetails b

on a.loanrefno = b.loanrefno

left join datawash2 c

on c.accounts1 = b.accounts1

where region = 'KwaZulu Natal'

and c.persalindicator = 'Y'

end

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--Update Juridistcion in NewMatters

begin

Declare @total numeric

, @60 varchar(10)

, @20 varchar(10)

, @10 varchar(10)

Set @total = (Select COUNT(*) From ABTRACERSNEWMATTERS where jurisdiction = 'N/A' )

Set @60 = Convert(Numeric, (@total * 0.6))

Set @20 = Convert(Numeric, (@Total * 0.2))

Set @10 = Convert(Numeric, (@Total * 0.1))

Select @total, @60, @20,@10

Declare @SQL varchar(3000)

Set @SQL =

'

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''01''

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top ' +@60 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''02''

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top ' + @20 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''04''

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top ' + @10 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''05''

where jurisdiction = ''N/A''

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

'

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

-- Update ABTRACERSNEWMATTERS

-- Set jurisdiction = ''06''

-- From

-- ABTRACERSNEWMATTERS a

-- Inner join

-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

-- ON a.IDNUMBER = b.IDNUMBER

-- AND a.LOANREFNO = b.LOANREFNO

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

--

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

-- Update ABTRACERSNEWMATTERS

-- Set jurisdiction = ''07''

-- From

-- ABTRACERSNEWMATTERS a

-- Inner join

-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

-- ON a.IDNUMBER = b.IDNUMBER

-- AND a.LOANREFNO = b.LOANREFNO

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

--

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

-- Update ABTRACERSNEWMATTERS

-- Set jurisdiction = ''08''

-- From

-- ABTRACERSNEWMATTERS a

-- Inner join

-- (Select Top ' + @125 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''N/A'') b

-- ON a.IDNUMBER = b.IDNUMBER

-- AND a.LOANREFNO = b.LOANREFNO

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

Exec (@SQL)

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

--ADDED THE FOLLOWING PIECE OF CODE TO THE PROCESS FOR CHANGE REQUEST CO17299

--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--Update Juridistcion in NewMatters

begin

Declare @total1 numeric

, @50_1 varchar(10)

, @50_2 varchar(10)

-- , @10 varchar(10)

Set @total1 = (Select COUNT(*) From ABTRACERSNEWMATTERS where jurisdiction = '07' )

Set @50_1 = Convert(Numeric, (@total1 * 0.5))

Set @50_2 = Convert(Numeric, (@Total1 * 0.5))

--Set @10 = Convert(Numeric, (@Total * 0.1))

Select @total1, @50_1, @50_2 --,@10

Declare @SQL1 varchar(3000)

Set @SQL1 =

'

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''09''

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top ' +@50_1 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''07'') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

Update ABTRACERSNEWMATTERS

Set jurisdiction = ''10''

From

ABTRACERSNEWMATTERS a

Inner join

(Select Top ' + @50_2 + ' * From ABTRACERSNEWMATTERS where jurisdiction = ''07'') b

ON a.IDNUMBER = b.IDNUMBER

AND a.LOANREFNO = b.LOANREFNO

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

'

EXEC @SQL1

end

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

--SELECT TOP 1 * FROM ABTRACERSNEWMATTERS

begin transaction

update ABTRACERSNEWMATTERS set jurisdiction = '01'

where loanrefno in (select a.loanrefno from accountdetails a,

creditcard b where a.accounts1 = b.accounts1)

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 15.5 WHERE JURISDICTION = '01' AND LOANSTARTDATE < '2004-07-01'

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION <> '01' AND LOANSTARTDATE < '2004-07-01'

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 15.5 WHERE JURISDICTION = '01' AND CONTRACTINTERESTRATE = 0

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION <> '01' AND CONTRACTINTERESTRATE = 0

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '03' AND LOANSTARTDATE < '2004-07-01'

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '06' AND LOANSTARTDATE < '2004-07-01'

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '07' AND LOANSTARTDATE < '2004-07-01'

COMMIT

BEGIN TRANSACTION

UPDATE ABTRACERSNEWMATTERS

SET CONTRACTINTERESTRATE = 17 WHERE JURISDICTION = '08' AND LOANSTARTDATE < '2004-07-01'

COMMIT

end

--step 3: Identify ABTRACERS ADDRESSES changes

--IMPORT G:\interface\ABTRACERS\ADD.TXT INTO DBO.PHASEII.ABTRACERSADDRESSES_HOLD

--INSERT INTO ABTRACERSADDRESSES

--SELECT

--IDNUMBER,

--LOANREFNO,

--ADDRTYPE,

--LINE1,

--LINE2,

--LINE3,

--LINE4,

--LINE5,

--LINE6,

--DATEFROM,

--DATETO,

--GETDATE () AS date_inserted

--FROM

--ABTRACERSADDRESSES_HOLD

--step 4: Identify ABTRACERS TELEPHONES changes

--INSERT INTO ABTRACERSTELEPHONES

--SELECT

--IDNUMBER,

--LOANREFNO,

--PHTYPE,

--CODE,

--TELNO,

--DATEFROM,

--DATETO,

--GETDATE () AS date_inserted

--FROM

--ABTRACERSTELEPHONES_HOLD

--step 5: Identify ABTRACERS LOAN INFO changes, AND ADD THE LOAN INFO details for New Matters:

begin

truncate table ABTRACERSLOANINFO

Insert into ABTRACERSLOANINFO

Select

a.IDNUMBER,

a.LOANREFNO,

a.LOANSTARTDATE,

a.BALANCE,

a.INSTALMENTAGREED,

a.LASTPAYMENTDATE,

a.LASTPAYMENTAMOUNT

From ABTRACERSDATA_TEMP a (NOLOCK)

WHERE LOANREFNO NOT IN (SELECT LOANREFNO FROM ABTRACERSDATA)

Insert into ABTRACERSLOANINFO

Select

b.IDNUMBER,

b.LOANREFNO,

b.LOANSTARTDATE,

b.BALANCE,

b.INSTALMENTAGREED,

b.LASTPAYMENTDATE,

b.LASTPAYMENTAMOUNT

From ABTRACERSDATA a (NOLOCK), ABTRACERSDATA_TEMP b (NOLOCK)

WHERE a.LOANREFNO = B.LOANREFNO

AND a.LOANSTARTDATE <> B.LOANSTARTDATE

OR

a.LOANREFNO = B.LOANREFNO

AND a.BALANCE <> B.BALANCE

OR

a.LOANREFNO = B.LOANREFNO

AND a.INSTALMENTAGREED <> B.INSTALMENTAGREED

OR

a.LOANREFNO = B.LOANREFNO

AND a.LASTPAYMENTDATE <> B.LASTPAYMENTDATE

OR

a.LOANREFNO = B.LOANREFNO

AND a.LASTPAYMENTAMOUNT <> B.LASTPAYMENTAMOUNT

end

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

--step 6: Identify ABTRACERS CIF UPDATES changes:

begin

truncate table ABTRACERSCIFUPDATES

insert into ABTRACERSCIFUPDATES

select

a.IDNUMBER,

a.LOANREFNO,

a.FIRSTNAME1,

a.SURNAME,

a.TITLE,

a.NOKFULLNAME,

a.EMPLOYEECLOCKNO,

a.EMPLOYERCD1,

a.EMAIL

From ABTRACERSDATA a (NOLOCK)

WHERE a.LOANREFNO NOT IN (SELECT b.LOANREFNO FROM ABTRACERSDATA_TEMP b)

insert into ABTRACERSCIFUPDATES

select

a.IDNUMBER,

a.LOANREFNO,

a.FIRSTNAME1,

a.SURNAME,

a.TITLE,

a.NOKFULLNAME,

a.EMPLOYEECLOCKNO,

a.EMPLOYERCD1,

a.EMAIL

From ABTRACERSDATA a (NOLOCK), ABTRACERSDATA_TEMP b

WHERE a.LOANREFNO = b.LOANREFNO

AND a.FIRSTNAME1 <> b.FIRSTNAME1

OR

a.LOANREFNO = b.LOANREFNO

AND a.SURNAME <> b.SURNAME

OR

a.LOANREFNO = b.LOANREFNO

AND a.TITLE <> b.TITLE

OR

a.LOANREFNO = b.LOANREFNO

AND a.NOKFULLNAME <> b.NOKFULLNAME

OR

a.LOANREFNO = b.LOANREFNO

AND a.EMPLOYEECLOCKNO <> b.EMPLOYEECLOCKNO

OR

a.LOANREFNO = b.LOANREFNO

AND a.EMPLOYERCD1 <> b.EMPLOYERCD1

OR

a.LOANREFNO = b.LOANREFNO

AND a.EMAIL <> b.EMAIL

end

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

--step 7: Identify ABTRACERS BALANCE UPDATES changes:

begin

truncate table ABTRACERSBALANCEUPDATES

insert into ABTRACERSBALANCEUPDATES

SELECT

a.IDNUMBER,

a.LOANREFNO,

a.BALANCE,

a.INSTALMENTAGREED

FROM ABTRACERSDATA_TEMP a (NOLOCK)

WHERE a.LOANREFNO NOT IN (SELECT b.LOANREFNO FROM ABTRACERSDATA b)

insert into ABTRACERSBALANCEUPDATES

SELECT

a.IDNUMBER,

a.LOANREFNO,

a.BALANCE,

a.INSTALMENTAGREED

FROM ABTRACERSDATA_TEMP a (NOLOCK), ABTRACERSDATA b (NOLOCK)

WHERE a.LOANREFNO = b.LOANREFNO

AND

a.BALANCE <> b.BALANCE

OR

a.LOANREFNO = b.LOANREFNO

AND

a.INSTALMENTAGREED <> b.INSTALMENTAGREED

end

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

--STEP 8: Identify matters to be closed (Status Update) - Case when reason code <> 800 any more:

--id no, loanref, status, comment

begin

TRUNCATE TABLE ABTRACERSDELETIONS

INSERT INTO ABTRACERSDELETIONS

select a.idnumber, a.loanrefno, '0' as Comment

from ABTRACERSDATA_TEMP A, ACCOUNTDETAILS B

WHERE A.LOANREFNO = B.LOANREFNO

AND A.LOANREFNO NOT IN (SELECT B.LOANREFNO FROM ABTRACERSDATA B)

AND B.REASONCODE1 <> '890'

INSERT INTO ABTRACERSDELETIONS

select a.idnumber, a.loanrefno, '1' as Comment

from ABTRACERSDATA_TEMP A, ACCOUNTDETAILS B

WHERE A.LOANREFNO = B.LOANREFNO

AND A.LOANREFNO NOT IN (SELECT B.LOANREFNO FROM ABTRACERSDATA B)

AND B.REASONCODE1 = '890'

end

--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

begin

INSERT INTO ABTRACERSDELETIONS

select a.idnumber, a.loanrefno,

(case

when B.PREVIOUSREASONCODE IN ('800','820') AND B.REASONCODE1 = '890' then '1'

when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 = '811' then '2'

when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 in ('802','803','804','805','806','807','808','809','812','830','870','600','601') then '3'

when B.PREVIOUSREASONCODE = '800' AND B.REASONCODE1 in ('802','803','804','805','806','807','808','809','812','830','870') then '9'

when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 in ('155','156','157') then '4'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 > '499' and B.REASONCODE1 < '600' then '4'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 > '399' and B.REASONCODE1 < '500' then '5'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 in ('163','164','815','899') then '6'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 = '159' then '7'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 = '158' then '8'

when B.PREVIOUSREASONCODE in ('800','811','890') AND B.REASONCODE1 in ('816','818') then '9'

when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '299' and B.REASONCODE1 < '400' then '10'

when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '599' and B.REASONCODE1 < '700' then '10'

when B.PREVIOUSREASONCODE in ('800','811') AND B.REASONCODE1 > '699' and B.REASONCODE1 < '800' then '10'

when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 in ('155','156','157','158','159','163','164','169','814','816','817','818','897','899') then '11'

when B.PREVIOUSREASONCODE = '890' AND B.REASONCODE1 > '399' AND B.REASONCODE1 < '600' then '11'

when B.PREVIOUSREASONCODE = '811' AND B.REASONCODE1 = '820' then '12'

else '13' end)

as Comment

from

ABTRACERSDATA a (NOLOCK),

ACCOUNTDETAILS B (NOLOCK),

ACCOUNTS C (NOLOCK),

ACCOUNTCLASSIFICATIONS D (NOLOCK)

where

a.date_updated_to_law < b.reasoncodedate --B.REASONCODE > GETDATE () -1

AND

A.LOANREFNO = B.LOANREFNO

AND

B.ACCOUNTS1 = C.ID

AND

C.ID = D.ACCOUNTID

end

begin

-- Delete All loans in the Status file with an PREVIOUSREASONCODE 800 and reasoncode1 800

DELETE FROM ABTRACERSDELETIONS

where ABTRACERSDELETIONS.loanrefno in (select b.loanrefno from ABTRACERSDELETIONS a left join accountdetails b

on a.loanrefno = b.loanrefno

where a.comment = '13'

and b.PREVIOUSREASONCODE = '800'

and b.reasoncode1 = '800'

OR

(b.PREVIOUSREASONCODE = '890'

AND b.reasoncode1 = '890')

OR

(b.PREVIOUSREASONCODE = '820'

AND b.reasoncode1 = '820')

OR

(b.PREVIOUSREASONCODE = '811'

AND b.reasoncode1 = '811')

and ABTRACERSDELETIONS.loanrefno not in(select z.loanrefno from abtracersnewmatters z))

end

--END

--After the Agent job has successfully FTP'd the files, run the following:

--Truncate table ABTRACERSDATA -- This is the old data

--Insert into ABTRACERSDATA (select * from ABTRACERSDATA_TEMP) -- This is the new fixed population.

--select * from ABTRACERSNEWMATTERS WHERE IDNUMBER IS NULL --YES

--select * from ABTRACERSLOANINFO --YES

--select * from ABTRACERSCIFUPDATES --YES

--select * from ABTRACERSBALANCEUPDATES --YES

--select * from ABTRACERSDELETIONS -- YES

--SELECT IDNUMBER, LOANREFNO, THIRDPARTIES1, PRINTINDICATOR FROM ABTRACERSINITIALHANDOVERSTATUS --YES

--select * from ABTRACERSADDRESSES --YES -- 2,206,018

--SELECT * FROM ABTRACERSTELEPHONES --YES -- 1,565,667

--CREATE INITIALHANDOVERSTATUS TABLE:

--SELECT TOP 1 * FROM ABTRACERSDATA --LOANREFNO

--SELECT TOP 1 * FROM ACCOUNTDETAILS --ACCOUNTS1

--SELECT TOP 1 * FROM THIRDPARTY --ACCOUNTS1

--TRUNCATE TABLE ABTRACERSINITIALHANDOVERSTATUS

--INSERT INTO ABTRACERSINITIALHANDOVERSTATUS

--SELECT A.IDNUMBER, A.LOANREFNO, (CASE WHEN C.THIRDPARTIES1 IS NOT NULL THEN C.THIRDPARTIES1 ELSE 'DLBL' END) AS THIRDPARTIES1, (CASE WHEN B.REASONCODEDATE < '2006/03/23' THEN 'Y' ELSE 'Y' END) AS PRINTINDICATOR

--FROM THIRDPARTY C INNER JOIN

-- ACCOUNTDETAILS B ON C.ACCOUNTS1 = B.ACCOUNTS1 RIGHT OUTER JOIN

-- ABTRACERSDATA A ON B.LOANREFNO = A.loanrefno

--SELECT TOP 1 * FROM ABTRACERSINITIALHANDOVERSTATUS

--SELECT * FROM ABTRACERSDATA A where A.IDNUMBER+A.LOANREFNO

--NOT IN (SELECT B.IDNUMBER+B.LOANREFNO FROM ABTRACERSINITIALHANDOVERSTATUS B)

--select distinct(loanrefno) from abtracersnewmatters where loanrefno not in (select loanrefno from abtracersaddresses