April 2, 2007 at 11:49 pm
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
April 3, 2007 at 12:28 am
Which line is producing the error?
N 56°04'39.16"
E 12°55'05.25"
April 3, 2007 at 12:33 am
Can you please help
April 3, 2007 at 12:41 am
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"
April 3, 2007 at 12:59 am
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
April 3, 2007 at 1:08 am
What do stored procedure AB_FINANCIAL perform?
N 56°04'39.16"
E 12°55'05.25"
April 3, 2007 at 1:49 am
It take's financial transactions from a Txt file and insert's it into a Table
April 3, 2007 at 2:53 am
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"
April 3, 2007 at 2:58 am
Thanks,
But how do I locate the FilesInDirectory or INTERFACEFILENAMES that changed ?
April 3, 2007 at 8:45 am
Did you read this article posted about 2 years ago?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=247574
April 3, 2007 at 8:54 am
Hi
Just went through it now only. But I still don't know what to do ?
April 4, 2007 at 7:43 am
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.
April 19, 2007 at 12:19 am
I am receing an Error.
60118 row(s) affected)
(17904 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply