Prerequisites:

  • Change request created containing a completed configuration spreadsheet. If this has not been completed, return the New Contractor/Contract/CCCD/SORs Configuration Template to the requestor.
  • If the Contract Definitions are required to sit under a New Contract, follow New Contract - Cx Knowledge Article first.
  • The contractor in Cx must have live Insurances in Cx. Check its been added/ not expired.
  • NB. If the contract is a development defect one, instead of adding a new contract definition, you need to link your new contract on the existing development contract definition by opening contract definitions in the quick menu, opening the contact definition 'Developer - Aftercare and Latent Defects', click on new contract and select the new contract you have just created in the list. (Its normally at the bottom of the list.) Then continue from update financial stored procedure step.


In Cx:


The following steps will require the information within the 'CCCD' tab on the template. 

  1. Type 'Contract Definitions' into the Quick Access Menu and select 'Contract Definitions'. 
  2. Select 'New Definition'.
  3. Copy the Description from the 'Contract definition name (same as contract name) column from the template. N.B. The contract definition description and the contract name should always be the same.
  4. Select 'LWSORBOOK' from the SOR table drop down. 
  5. Select the magnifying glass next to All functional units.
  6. Search for the default functional unit, in the search bar.
  7. Select the correct option in the left hand list and select 'Add'.
  8. Select 'Select'.
  9. Select all required trades in the All trades dropdown as provided on the CCCD spreadsheet. Make sure SOR's used are covered by trades. E.G Major Repairs; General Repairs.
  10. Select all required repair types in the All repair types dropdown as provided on the spreadsheet.
  11. Select all required priorities in the All priorities dropdown as provided on the spreadsheet. E.G Planned for planned works.
  12. All inspection types can be left blank as we do not use this module.

 

The following steps will require the information within the 'SOR&Contract' tab on the template. 

  1. Select the magnifying glass next to All SORs.
  2. Select 'Remove All' before adding SORs to clear the right hand list.
  3. Search for the SOR code provided in the spreadsheet, in the search bar. You must add a minimum of 4 characters in the search box.
  4. Open the accordions in the left hand list until the correct code is displayed and select the check box. You can select/tick multiple in a group.
  5. Select 'Add' to add the SORs to the right hand list.  Take care when adding Round Ups to make sure selecting the right VAT rate.
  6. Check that SORs have been moved across to the right hand list.
  7. Select 'Select'.
  8. Save.
  9. Search for Contact in the quick menu.
  10. Select the contract.
  11. Click on the Definition tabs.
  12. Click on New Definition to search and add the definition you have just added.
  13. Add the Definition you have created, likely at the bottom of the list.
  14. Save. Refresh the page and make sure green highlight disappears from the Definition.


Update the finance code stored procedure.


  1. Run the SOR contract definitions report. 
  2. Download the report as CSV (comma delimited). N.B. Any other file type will distort the report. 
  3. Open the report from the downloads bar. N.B. Copying the formatting from the top row across the whole sheet will make it easier to read. 
  4. Apply filtering and filter the Contract Description column to the Contract the SORs were created under. N.B. You will be able to see the SORs that were added in the SORCode Column (L).
  5. Open the SP Coding Matrix. Open in desktop app rather than working in the browser version. 
  6. Access the 'Multiple SORS' tab. 
  7. If the SOR codes being added are Service Chargeable, change the values in Column D under Service charge category to 'Service Chargeable' prior to importing any data into the sheet. 
  8. From the Contract Definitions report copy the values from the SORCode Column (L) and paste into the SORCode Column (F3) of the matrix. When pasting, use paste values option. 
  9. From the Contract Definitions report copy the values from the ContractDescription Column (I) and paste into the SORTable Column (G3) of the matrix. When pasting, use paste values option. 
  10. From the Contract Definitions report copy the values within the ShortDescription Column (M) and paste into the SORDescription Column (H3) of the matrix. When pasting, use paste values option. 
  11. The rest of the sheet will populate based on the data entered. Go to cell A96.
  12. Bulk copy the data from F96 - K96 down to where the data ends and the cell values are set to 'To enter'.
  13. Paste into a brand new CSV file and save as CSV (comma delimited). This file will need to be saved to the C drive in order to access it via local login to SSMS. If you don't already have one, create a new folder in the C drive named 'Imports' and save the file there. This file will be imported into the database, use a naming convention that is logical and memorable. This will be referred to as the Import CSV
  14. Open SQL Server Management Studio and log in to either the test (tsql01) or live (sql01) database based on where you are making the change. 


Both Test and LIVE need to be updated. Update Test first and then send on to a member of the Business Analysis Team with access to run the Query in LIVE.

  1. Connect to the required Cx database

  2. Backup the LW_repairs_Finance_code_lookup table (BackupLW_repairs_Finance_code_lookup table.txt)

  3. Import your csv file
    Right click on the database, access 'Tasks' and select 'Import Flat File'.
    Select 'Next'.
    Select 'Browse' and locate the Import CSV file.
    Copy the 'New table name' prior to selecting 'Next'.
    Select 'Next' until you can select 'Close'.

  4. Start a new query.
    Copy and paste the Insert Finance Codes to SP Query into the Query Manager. 
    Paste the New table name where the query states '-- Put the CSVImport Table name in here'.
    Highlight the Query from BEGIN TRAN to the New table name and 'Execute'. 

  5. Commit the above to the database
    Check the query has executed ok and if so
    Highlight COMMIT in the query and execute

  6. Validate that the data has gone into the table by running the below query
    Copy and paste ValidateInsertFinaceCodesIntoSP Query

  7. Drop the table that was created when importing the flat file using the below 
    drop table --insert file name here
    execute


N.B: if new/existing SORs are to be added to an existing contract that also exists in Keystone then the contract in Keystone will need to be cancelled and re-raised before the SORs become visible.

The Keystone interface, from Cx, runs overnight, if the SORs are being used in Keystone, open the Keystone Configuration Menu > KPM Schedule of rates > Highlight the LWSORBOOK and click on Import Cx SOR button at the bottom of the screen.




Testing the new contract set-up in Cx:

  1. Search for an Asset in Cx Test.
  2. Click on the Repairs tab.
  3. Click on the New Request button.
  4. Repairs Wizard will load, add the problem text.
  5. Click on Next.
  6. Review any alerts if this comes up, by clicking on the contacts name, and click on Next.
  7. You must enter the Repair Type, Analysis, and Priority which matches the contract definition you have set up. E.G for Bloor Homes Exeter you would select Routine Priority of Routine, Repair Type - D1, and Analysis of Latent Defects Recoverable.
  8. Search, add, and select you SORs, again these must match the SORs you have added to the contract definition.
  9. Add Location and Analysis, then click on Add.
  10. Click on Next.
  11. Select Contractor screen loads. Your newly added contractor should appear here. (N.B. You may see more than just your contractor as some contract definitions, such as 'Developer - Aftercare and Latent Defects',  have multiple linked contractors.)
  12. Highlight your Contactor and click on Confirm No Appointment button.
  13. Repair Request Summary is displayed.
  14. Click on Close.


Testing planned works via Keystone:

  1. Please follow steps 1.1 - 1.5 of the KPM Guidance in Keystone Test
  2. Then run the Export Work Items to CX Interface from Interfacing > Manage Interfaces > Export Work Items to Cx > Run now.
  3. Search for your Assets in CX Test to check that repair requests, work orders, and SOR Tasks have been created.