After refresh is completed we need to scrub data

 

For Tier 2 and higher environments (UAT, INT, Tier5) – you can connect to the Azure SQL DB from the Management Studio on any of the Application Servers (AOS)

 

Only connections from AOS will be allowed as those IP are whitelisted for DB connections.

 

Steps for connecting to the Azure SQL DB for Tier 2 and higher environments:

 

“To determine which SQL Connection string to use, locate the Database Accounts section on the Environment Details page in Lifecycle Services.

 

To build the correct connection, use the SQL Server\Database Name combination in SQL Server Management Studio. The server will require that you append “.database.windows.net” to the SQL Server name value. Sign in using the axdbadmin account.

You will also need to explicitly connect to your required database by selecting the Options button and updating the database name.

Once connected run the below on the respective DB you are working on.

 

--STEP #1

Delete from VENDBANKACCOUNT

 

Delete from CUSTBANKACCOUNT

 

update VENDTABLE

set BANKACCOUNT = ''

where BANKACCOUNT <> ''

 

update CUSTTABLE

set BANKACCOUNT = ''

where BANKACCOUNT <> ''

 

--STEP #2

Please find the Sql scripts to verify/update method of payment, Payment specification and BGRSPAYEEBANKACCOUNT in UAT:

 

--This sql script to verify the data prior to update

Select INVOICE,PAYMMODE,PAYMSPEC,BGRSPAYEEBANKACCOUNT,DATAAREAID

from VENDTRANS

where EXISTS (Select REFRECID from VendTransOpen where VendTransOpen.REFRECID = VENDTRANS.RECID)

 

 

--This sql script will update the data

update VENDTRANS

set PAYMMODE = 'CI_OFFLINE',

    PAYMSPEC = '',

       BGRSPAYEEBANKACCOUNT = ''

where EXISTS (Select REFRECID from VendTransOpen where VendTransOpen.REFRECID = VENDTRANS.RECID)

 

 

--Step#3 Enable Users

update userinfo set enable='1' where id in ('Theresa.Davis' , 'Nick.Oconner' , 'Bayo.Oyebanjo' , 'Nikhil.Dolle' , 'Sean.Lumbania'  , 'Chris.Russell')