April 2, 2007 at 11:51 pm
Hi All
Problem with the Error in the Subject.
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:18 am
Cross post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=355525
N 56°04'39.16"
E 12°55'05.25"
April 3, 2007 at 12:23 am
Can you please help me with the Error
April 4, 2007 at 6:28 am
My first thought is that you probably should not be doing this operation in a stored procedure.
Sorry.
Next, I would say to make the cursors forward only / read only cursors. They do not seem to be doing anything but fetching and looping and you will have less to worry about relating to schema binding.
April 4, 2007 at 6:40 am
How do you suggest we run this Proc ?
April 4, 2007 at 7:04 am
I'd move this kind of processing into SSIS. You've really stepped outside what TSQL is meant to do with all the file I/O. You'll probably get the same or better performance and you'll have a lot more flexibility & power in the execution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2007 at 8:10 am
I would tend to agree, SSIS, DTS, or a custom application. T-SQL is not really designed for looping processes like this.
I think SSIS is a great option.
April 19, 2007 at 12:18 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
April 23, 2007 at 2:18 am
Good Morning
I have a VB Script below. I want to amend this script to wait for a control file. Can you please help
' VBS Script Generated by CuteFTP (TM) macro recorder.
' Generated at: 2007/03/01 02:15:46 PM.
' Create TEConnection object
Set MySite = CreateObject("CuteFTPPro.TEConnection")
' Initialize remote server host name, protocol, port, etc.
MySite.Host = "00.00.000.00"
MySite.Protocol = "FTP"
MySite.Port = 21
MySite.Retries = 30
MySite.Delay = 30
MySite.MaxConnections = 100
MySite.TransferType = "BINARY"
MySite.DataChannel = "DEFAULT"
MySite.AutoRename = "OFF"
' WARNING!!! SENSITIVE DATA: user name and password.
MySite.Login = "Lawactive"
MySite.Password = "LAWactive9"
MySite.SocksInfo = ""
MySite.ProxyInfo = ""
' Connect to remote server
MySite.Download "/Abtracers/*.*", "E:\xxxxx\xxxxxxx\ABTRACERS\"
MySite.RemoteRemove "/Abtracers/*.*"
MySite.Disconnect
' Initialize remote server host name, protocol, port, etc.
MySite.Host = "http://www.xxxxxxxx.co.za"
MySite.Protocol = "FTP"
MySite.Retries = 30
MySite.Delay = 30
MySite.MaxConnections = 100
MySite.TransferType = "BINARY"
MySite.DataChannel = "DEFAULT"
MySite.AutoRename = "OFF"
MySite.Login = "anonymous"
MySite.SocksInfo = ""
MySite.ProxyInfo = ""
' Connect to remote server
MySite.Connect
MySite.Upload "E:\xxxxxxx\xxxxxxxx\xxxxxxx\*.*", "/ToLaw/"
'Delete local file
MySite.LocalRemove "E:\xxxxxxx\xxxxxx\xxxxxxx\*.*"
MySite.Disconnect
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply