Instructions to setup the creditors' portal (technical guide)

This knowledge base article contains information to assist when configuring your Insol2 database to work with the Creditors’ Portal.

This configuration involves installing the Insolvency Data Sync Service (InsolDataSync) on your server to handle the data transfers between your database and the Creditors Portal.

Please create an Insolvency Support ticket requesting an Insolvency staff member install the InsolDataSync service.

Preparation

You will need to register your database’s unique identifier with the developers of the Creditors’ Portal to obtain your Global Practice ID.

Please run the following SQL queries against your insol2 database, and email the results to [e-mail] with the Subject: {PracticeName} Insoldatasync data

1

Select Fieldname = 'A. DatabaseVersion', Setting = isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 1
UNION
select 'B. MBL flags', isnull(cast(IBFlags as varchar), 'NULL') from bank where code like 'MBL'
UNION 
select 'C. CBA flags', isnull(cast(IBFlags as varchar), 'NULL') from bank where code like 'CBA'
UNION
SELECT 'D. PracticeUID' ,Setting = isnull(cast(UID as varchar(50)), 'NULL') FROM Admin WHERE adminid = 1
UNION
Select 'E. WebPortalPracticeID', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2000005
UNION
Select 'F. PortalBaseUrl', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2000004
UNION
Select 'G. PortalActivated', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2000000
UNION
SELECT 'H. Email From Mailbox', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 95005
UNION
SELECT 'H2. Authorised Email Sender', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 95002
UNION
SELECT 'I. Bounced Alerts Mailbox',isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 95008
UNION 
Select 'J. PortalNoNotificationEmails', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2000012
UNION
Select 'K. BatchPayloadPageSize', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2005050
UNION
Select 'L. DocumentPayloadPageSize', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2005040
UNION
Select 'M. CreditorPayloadPageSize', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2005030
UNION
Select 'N. CreditorPayloadPageSize', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2005020
UNION
Select 'O. CounterpartyMatchPayloadPageSize', isnull(Setting, 'NULL') FROM dbo.ApplicationSetting WHERE SettingID = 2005000

2 Listing of Drive Letters that will need a Windows Administrator to supply their UNC equivalent:

SELECT DISTINCT Left(Setting,3) as DriveLetter from ApplicationSetting where Setting like '%:\%' 
UNION select DISTINCT Left(Setting,3) as DriveLetter from AdminSetting where Setting like '%:\%' 
UNION select DISTINCT Left(Setting,3) as DriveLetter from UserSetting where Setting like '%:\%' 
UNION select DISTINCT Left(documentfolder,3) as DriveLetter from Admin where DocumentFolder like '%:\%'  
UNION select DISTINCT Left(path,3) from Document where Path like '%:\%';

Pre-requisites

The pre-requisites are detailed: here.

Database Configuration

1. Replace Drive mapped file paths for existing Documents with UNC file path equivalents.
    
2. Open the file 050ServiceBrokerInstall.sql into SQL Server Management Studio
   
   

   replace @dbname with databasename
   replace 'https://creditors.azurewebsites.net/' with (include quotes) 'https://{subdomain.}creditors.accountants/' -- or test: 'https://creditors.azurewebsites.net/'
   replace newpracticeid with portal practiceID e.g. 33
   replace insol6portalactivated with 1 if the portal is to be activated, else 0
   replace 'https://www.bing.com.au/' with (include quotes) practice website URL, e.g. 'https://www.practicewebsite.com.au/'
   replace portalhostedonazure with 1 if the local portal database is on AzureSQL, else 0 

    

Service Installation NEW

4. If any of these are open, close: Computer Management, Services, Event Viewer.

5. Open older OSs Windows Explorer | View | Options | Change folder and search options | View | Advanced Settings | Hide extensions for know file types | UNCHECK

 

6. On modern Windows Server OSs:

7. Open PowerShell ISE as an ADMINISTRATOR, open the script 010InstallDataSyncSvc.ps1 from Source Control

8. Open 010InstallDataSyncSvc.ps1

9. Edit the parameters in the top section.

10. SAVE THE SCRIPT

11. Backup the OLD version's config from: "C:\Program Files (x86)\MYOB\InsolDataSync\InsolSyncWinSvc.exe.config"

12. If the OLD version exists in "c:\Program Files (x86)\MYOB\", manually stop it using the console: sc stop InsolDataSync. Uninstall the OLD version using Add/Remove programs.

13. The script will download the latest DLL and ZIP. It will expand them into a temp location. Unregister the previous new version if it exists. Move the unzipped files into: "C:\Program Files (x86)\Insol6\InsolDataSync". Update the config file with the values set in the PowerShell variables in the script. It will fail if the local DotNetFramework is less than v4.6.2. 

14. Press F5 to run the script, and answer all the prompts carefully.

15. When complete you must manually set the user account to the service.

16. The Service Account credentials are used for the service:

The service should be set to start automatically after shut down.

17. Configure the Recovery tab to Restart after a failure:

18. Start the service

Post Installation Checking

1 Open the Event Viewer.

2 Check the Application Log 'Insol' for any errors occurred after starting the service (any errors would appear as shown to below). After waiting 2 minutes: if there were any errors, please stop the service and contact Support.

   3 Confirm the email settings are correct

exec websp_ApplicationSetting

    4 Run the following SQL

Declare @ContactData As dbo.Contact
Declare @Ret as int, @adminone as int = 1;
Declare @subjecttxt as nvarchar(100) = 'tested at ' + convert(nvarchar(19),sysdatetimeoffset());
 INSERT INTO   @ContactData
([Email],[FirstName],[LastName],[EmailDisplayName])
VALUES
('[e-mail]','simon','the','great')
exec @Ret = websp_TransferWebRequestEmailCreate
        @ContactData=@ContactData
       ,@Subject=@subjecttxt
       ,@AdminName = 'JobNameTst'
       ,@MessageText = 'test text'
       ,@AdminID=@adminone
       ,@UserID=1--unused in proc
       ,@CreateWebRequest=1
       ,@IsDelayedRequest=0--0=creates a watch record
       ,@RequestGroupUID=NULL

select top 2 * from TransferWebRequest order by id desc
select top 5 * from TransferWebResponse order by id desc
--note top record of last recordset

--run this until a new record appears, with an ID bigger than the one from last recordset
-- OR you recieve an email in your [e-mail] mailbox.
 select top 5 * from TransferWebResponse order by id desc

5 Confirm an email was received by '[e-mail]'

Email Configuration

If emails are enabled for the portal, then a port is required. See https://exalt.zendesk.com/hc/en-us/articles/218486707-Configuring-the-software-settings-for-email For TLS and SSL supply a host name in the format: <hostname>,<portnumber>

Emails for Creditors added to the portal and emails for Documents posted to active creditors on live jobs, can be disabled per Job with an admin setting:
INSERT INTO dbo.AdminSetting(AdminID,SettingID,Setting,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,TS)
VALUES (@AdminID,7000006, '1', GETDATE(),'svcI', GETDATE(),'svcI', NEWID() )