- Firstly, each time the fulfilment runs an email is generated identifying any issues. The email will have the following information:
MAAK = Mailing Code. This should always be populated, if there is no code the supporter will not receive a mailing
2,5,7 = Paragraph Conditions. This should always be populated, if there is no conditions the supporter will not receive a mailing
6443468 = Contact Number
009ENRACK = Mailing. This should always be populated, if this is not populated then nothing will be sent
Batch Number = This may not always be populated if the batch has not been processed
care_admin = The user who made the change
15-MAR-22 = The date this change was made
The Issue is
Every letter in CIS has paragraphs which are based on how the contact has chosen to interact with us
For example Paragraphs
2,7
Generates a letter which confirms they are paying via
DD;
And the customer has chosen the Enjoy Food Inset in their welcome pack.
PACK_H!:COMPGUIDE_ENJOYFOOD!
There is a full guide at
"I:\Working Folders\CIS_Support\df_cmd_conversion.xlsx"
If the output letter does not meet the expected paragraph conditions the Fulfilment letter will not output, taken off ‘CONTACT_MAILING_DOCUMENTs’ and passed to an errored table.
It is unclear if this is part of CIS Design or a rule that been added to the process.
Reasons the system sees the letter as incorrect can be
- The process uses the Activity = WPSEL/Welcome Pack Selection however there two active examples of this
- The source code that has been used for the customer interaction is incorrect for example a DD source code has been used for a CC Membership
-
There are only the examples I have seen and there may be more
Short Term fix is
How we confirm the correct data
- Look at the CIS Record to confirm what it should be and fix accordingly in the case of a record with two active WPSEL Activities end date the oldest WPSEL for a day before the current activity
- In the case of an incorrect source code amend the data to the correct source code on CIS
-
These methods may not be the best way to fix these issue for example if we need to change a source code but the data has already been sent to external systems such as Business Central changing on CIS will not update these external systems.
In all cases where the error has been generated by an individual we try to feed this back to the individual, their Manager relevant people who need to be informed. For example if the error has caused a transaction to have been processed incorrectly we will copy in Finance as they may need to amend their systems.
How we fix the mailing which has been generated
You will need to remote on to Terminal Server if using your computer or not in office
Log on to the Task Manager server (DUKVWCNGP01) You may need to be set at an admin on this server and have a admin logon
- Open ‘Task Manager (Classic)’
- Expand ‘Tasks/Live/Operational’
- Once you have ran OP008 make sure to enable the task by right clicking over and clicking enable
- Then run ‘OP008:DF FINANCIAL Refulfill of documents for the past 7 days due to errored mailing documents - Run adhoc as required - *OFF ALREADY*’ it is unclear when this should be actioned it can be done after fulfilment has run but it has never been made clear if it will cause an issue if it done before fulfillmet has run.
- Once you have ran OP008 make sure to disable the task by right clicking over and clicking disable
- Open ‘SQL Management Studio’
- Open a new query
- Insert a new SQL snippet
- Choose the SQL Snippet – Financial Fulfilment Code Checker
- Run the SQL Snippet and you will see
-
You should pay attention to lines in the 3rd part of the results which have more than 2 selected paragraphs
The results in the 3rd part only highlight letter which have not yet been fulfilled and shows their selected paragraphs. As a rule of thumb any letters which more than 2 paragraphs suggest the need to check there are however 3 letters which are valid and have 3 paragraphs so you may initially see lines which look like they may need to be fixed but upon checking the excel sheet do not. It may be that the decision is taken to change the sql snippets, so these false positives do not show but I am not aware of if there is any Implication to this.
If we have email confirming the lines to be fixed we do not need to pay attention to the bottom part.
- Go to contacts record
- Check their activities to identify which mailing pack they would like to receive, once in activities tab check for WPSEL (Welcome Pack Selection) you can sort in order by clicking on activities menu heading
- You can sense check which pack they would want to receive by choosing the activity with the later valid to date. ( 2,5 = DD payment and Living with type 2 Welcome Pack)
- You will need the Mailing Number. You can access this via the Communications tab (this maybe different if you have a custom view), then Mailings and view in excel by right clicking, selecting grid and View In Excel
- To confirm which mailing to change, you can check the mailing and date provided in the email against the contacts mailing tab on CIS.
-
To confirm what needs to be sent to supporter, use the cmd conversion guide which can be found: There is a full guide at "I:\Working Folders\CIS_Support\df_cmd_conversion.xlsx"
Once you have confirmed the incorrect lines to fix these run a sql update to fix these offending lines
In MSSQl it would be
EXEC('Update contact_mailing_documents
set selected_paragraphs = ''set relevant paragraphs from the control document''
where mailing_document_number = set relevant mailing document number
'
) at [cislive]
;
In Oracle it would be
Update contact_mailing_documents
set selected_paragraphs = 'set relevant paragraphs from the control document'
where mailing_document_number = set relevant mailing document number
Both Oracle or SQL will fix the issue but SQL is auto commit whereas Oracle you will need to commit your change
Once you have fixed the errors simply rerun the SQL Snippet and check there are no lines which have more than 2 paragraphs or if there are 3 paragraphs they are valid.
Long Term Fix
- Stop the generation of the issues at user level by training and alerts to the individual they have made an error which they need to fix
- Automate the process so that when present with an error it would automatically fix all the related parts of the process.