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

  • 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

     

  • Which line is producing the error?

     


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

  • Can you please help

  • 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"

  • 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

     

     

  • What do stored procedure AB_FINANCIAL perform?

     


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

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

  • 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"

  • Thanks,

     

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

  • Did you read this article posted about 2 years ago?

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

  • Hi

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

     

  • 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.

  • 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