1. This is the process to handle the incoming Spring/Summer/Christmas Raffle data in Blender
2. Daily file expected

1. Access to Blender portal
2. Access to CIS/NG Care LIVE application
3. Access to FileZilla with SFTP connection

 

1. Download today's file from Blender. 

   

2. Connect to SFTP connection in FileZilla

3. Download corresponding control file from /Usr/sterlinglottery/DATA_TO_LOAD/RAFFLE_DATA/xxx_Raffle_YYYY (xxx represents the raffle at the time - Spring, Summer, Christmas)
   
   
   

4. Check control file matches with data in the corresponding file downloaded from blender
   • Check ‘SOURCE_CODES_ST_RAFFLE_xxx’ control file matches the incoming data file
     • Create a pivot table in the incoming data file to get count of each source code
     • Check counts match with the source code control file
   • Check ‘CONTROL_ST_RAFFLE_xxx’ control file matches incoming data file
     • Check record counts match
     • Check each different payment method counts match
     • Check each payment method's total amount matches
     • For CHQ payments, check the pay-in slip numbers match
     • Check Gift Aid image count for both Electronic and Written method

External Reference Error

NOTE: This error might pop up for the same External ref and CIS number multiple times in the load.

There have been cases where the following error appears:

The above error is telling you that the External reference might not be linked to the provided CIS number. In order to confirm or deny this do the following:

1. Take that External Reference and search for it in CIS.
   1. Click 'Find Contacts'
   2. External Reference (CON)
   3. Search the copied external reference
   4. Open the record
      
2. Once in the record first check if the CIS contact number matches what you see in the error
   
   
   
3. 
   
   1. If they match then you can click the little X on the error to dismiss it. You can then refresh and you are done. 
   2. If they do not match then continue
4. If they do not match this could be due to changes in details, duplicates or data errors. The next step is to investigate the details in the file vs the CIS number and see if they are even the same person. 
5. If they are clearly to separate individuals Sterling will need to be contacted to see what is going on. 

If you are at any point worried do feel free to consult IT.  

Gift Aid

We are only supplied PDF documents for those with Written Gift Aid on file, no file for the Electronic method.

You can filter in the data file to see the list of GAD (Gift aid documents) we should have.

Exceptions appearing under ‘Gift Aid Declarations Electronic’ template in blender. These are cold supporters with an Electronic GA declaration. As these supporters are not in CIS database, the blender load will flag as an exception as it cannot validate the incoming data vs data in CIS for these supporters

1. Here, click into the exception under the template (highlighted red) and click the ‘x’ to ignore the exception. These declarations will still load into CIS.
2. Click save and click return, when ready refresh the load. 

Exceptions under 'Documents - Gift Aid Images' template in load

1. If any exceptions click the refresh button (highlighted below). 
   
   
   
2. If you are still seeing the error click on the error to view what it is saying. You should see the following
   
3. If this occurs there are two stages to checking if we have the GA document
   1. Go into the SFTP and check the location Sterling provides us with GA images. It should be the following location:
      
      If there are any documents inform IT as this means the automation is not working. 
      
      
   2. If you go there and see there is no GA document, copy the following file path into your file explorer:
      \\duk-inf-fs01\cis-prod$\GiftAid\Sterling\Raffle
      and proceed to the correct path
      E.G. If Spring 2024 then you must go into 2024 and then Spring
      Search the GA document in the location. If it is not there you most likely need a re-supply.
      
      
      

---

---

AS AN FYI - this process is managed by IT, you WILL NOT need to manage this

The corresponding Gift Aid PDFs are downloaded automatically PDFs from the SFTP location daily at 06:00 am to the network share location that blender reads from: 

\\duk-inf-fs01\cis-prod$\GiftAid\Sterling\Raffle\2023\xxxxx

This is done automatically via SQL Server Agent job: ctq_GetRafflePDF (Covered under SSMS Server Agent Step)

Moving control files and GA image files on SFTP once data loaded

For files:

• Now go to SFTP location:
  1. /Usr/sterlinglottery/DATA_TO_LOAD/RAFFLE_DATA/xxx_Raffle_YYYY
• Move control files to:
  1. /Usr/sterlinglottery/DATA_TO_LOAD/RAFFLE_DATA/xxx_Raffle_YYYY/Control_Files

1. For GA images:

   • As part of the SQL Server Agent job (Step 4) the PDF Gift Aid docs have already been moved on the SFTP to: 
     • /Usr/sterlinglottery/DATA_TO_LOAD/IMAGES/xxxYYYY/Downloaded

SQL Server Management Studio (SSMS)

As part of streamlining the process, an auto-job has been put in place to download the PDFs for GA from the SFTP to the network share location.

To access this stored procedure - Go to SSMS, on the Object Explorer navigate to > SQL Server Agent and then expand the 'Jobs' folder and navigate to 'ctq_GetRafflePDF', see below:

 

If after the refresh in Step 5, there continue to be exceptions under the 'Documents - Gift Aid Images' then you will need to check the number of PDFs on the SFTP location against the number of PDFs in the network share location, see below:

• SFTP location for GA PDF documents: /Usr/sterlinglottery/DATA_TO_LOAD/IMAGES/xxxYYYY/Downloaded
• Network share location for GA PDF documents: \\duk-inf-fs01\cis-prod$\GiftAid\Sterling\Raffle\YYYY\xxx (xxx - represents the folder for which raffle at the time: Summer, Spring or Christmas)

Here, if there is a discrepancy with the number of PDF documents between the two folders, you will need to raise with UA who will decide if this needs escalating with ctq/MG incase the SSMS Server Agent Job is deviating or escalate with Sterling if GA PDF documents appear to be missing.

---

---

Tests from Sterling

It is very common for Sterling to send through test payments through to CIS. Because there are payments associated with these tests we are required to push them into CIS for Finance to ensure payments are all logged. As long as the protocol is followed we can allow these into CIS. If you feel that it is a test payment from Sterling, check the file to see if there are any payments allocated to it in the file. If so, please process them as usual, if no payment is associated, these tests could be to ensure contact status updates or other amendments to supporters details are captured, please process these as usual. 

Cleared all exceptions

• Once the load has refreshed, check that any exceptions before refresh have disappeared
• Check the success rate is 100% before loading the data
• To load the data into CIS click on the blue ‘lightning bolt’ button
• Once data has finished loading you should see colour change from red to green and the blue button will disappear for that instance.
• The data has now been loaded

Asking for re-supplies

If  there are any problems with the data such as:

• Incoming data does not match control files
  1. Missing or incorrectly named GA images
  2. Missing Paying in Slip numbers for CHQ payments
  3. Incorrect source codes in the incoming file
     
     

Then DO NOT load the data. Contact the following via email:

Elizabeth Millican emillican@sterlinglotteries.co.uk;

Cc in the following DUK stakeholders

Catherine McMillan Catherine.McMillan@diabetes.org.uk;

               Development team DevelopmentTeam@diabetes.org.uk

Olga Ionova Olga.Ionova@diabetes.org.uk

Usmaan Arif usmaan.arif@diabetes.org.uk 

Veda Vakili veda.vakili@diabetes.org.uk

And advise them of the issue. Ask Elizabeth Millican for a resupply of the data to SFTP location:

For files

Once you have received the resupply move it to:

The file will then be loaded into the blender portal for you to check against the control files

For GA images

You can then repeat step 4 – 6 to process the resupplied GA image