Task Manager Jobs

Task TaskDescription TaskSummary Enabled DUK_ID EffectiveSchedule createdBy createdDate AmendedBy AmendedDate RP017:Fortnightly Determine Invalid Gift Aid Declaration 1.0.97 - Adding post code to selection
1.0.98 - Adding David Cruden to email distribution.
Y RP017 every 2nd Mon 06:30 colinb 12/11/2020 paulm_a 5/7/2021 RP019:Daily Project Local VolGroup Extract

Y RP019 every weekday 17:00 colinb 12/11/2020 colinb 12/16/2020 DQ001:DUK_Daily_Cancel_Family_Members: v1.3 - DONOTUSE CR791 - Family Membership correction after payplan cancellation

Firts run 25/11/2011 ... 16:48:15, had to reset it based on march failures
N DQ001 every day 08:00 colinb 12/11/2020 paulm_a 12/21/2020 DQ010:DUK_Weekly_Insert_Sales_Persons: v 1.5 Add to SALES_PERSONS those USERS who are not historic and have a contact number but are currently from SALES_PERSONS.

20160711 - RM12182 - Meena Shah
As instructed by Clive Shername, re-enabled this task and amended the schedule to run every night at 09:00 p.m. instead of every week on Monday at 08:00 a.m.
Y DQ010 every day 14:11 colinb 12/11/2020 colinb 12/16/2020 DQ012:DUK_Weekly_Update_Contacts_Salutation_Sex: v1.2 CR693 - Update Contacts Salutaion and Sex upon Contact merge
Y DQ012 every Mon 07:00 colinb 12/11/2020 colinb 12/16/2020 DQ013:Daily Check for Active JUNior suppresion with no stored DOB in CIS v1.0 - LIVE 24/11/12 A staff member has created and active suppression meaning the reocrd will get moved into the CHI group following the charities safe guarding children initative.

However an issue has been identified that staff are creating manual JUNior suppressions for supporter records, however they are failing to add the DOB.

A script has been written to identify the non historic occurences of this and then it can email the staff memeber concerned to request thet resolve the. If the issue remains unfixed after a week then it needs escalating, so that the matter can be resolved
Y DQ013 every weekday 12:00 colinb 12/11/2020 clives_a 2/7/2020 DQ014:Daily Legacy Activity Hierarchy: v 1.3 CR 939: For the following legacy activities:

LPLegacy Pledger
LPILegacy Pledge Intender
LELegacy Enquirer
LEGCONLegacy Contact

Only one should be current for a contact. If mulitple current activities exist, end-date them so only one is current,
using the order shown above as the priority.

CR 1218: End-date LPIs if we have a NLM suppression.

Also, change the hierarchy update so that it is based on the latest "valid_from" date. This is because,
as a result of Pure telemarketing campaign, previous LPIs can be "downgraded" to LEs or LEGCONs.

Y DQ014 every weekday 07:05 colinb 12/11/2020 colinb 12/16/2020 DQ015:Daily Populate Diabetes_Voices contacts table: v 1.1 CR 779: Populates the duk_admin.DVContacts table with list of current contacts who have signed up to the Diabetes Voices network.

This is exposed as a view and made available through CIS List manager.

20150506 - RM5743
This task has been failing since at least 08-Apr-2015 but has not been picked up by Monday's Daily Checks.
Reselected the package procedure, added a debug success message, and amended the spelling mistake in the failure message.
Y DQ015 every weekday 07:15 colinb 12/11/2020 colinb 12/16/2020 DQ021:DUK_Weekly_End_Date_GAD_GA: v1.1 RM9356 - End_Date GADs for GAs
20160105
Meena Shah

We are explicitly not calling the Webservice to end_date these GADS but only updating the gift_aid_declarations.end_date.

This is adhering to the Work Package as last updated by Seetha and her summary of requirements.

This now also includes Hakeem's latest requirements - 20160216

20160503

RM9356 updated by Hakeem to add Olga Ionova and CAtherine McMillan as recipients of the report
Y DQ021 every Tue 00:30 colinb 12/11/2020 colinb 12/16/2020 DQ022:RM11993 - Add paying_in_slip number for missing batches Some batches for bank_account in ('TNS', 'TNSM','WP') that DO NOT have paying_slip_number populated, Add the transaction date to that column to match other transactional data currently processed
Y DQ022 every day 11:59 colinb 12/11/2020 colinb 12/16/2020 DQ023:Daily Cancel Gift Aid Declarations - DONOTUSE

N DQ023 every hour colinb 12/11/2020 paulm_a 12/21/2020 DQ024:Weekly UKChanges - Mortascreen extraction: V2.0 Automation of Java process.
Y DQ024 every Wed 19:19 colinb 12/11/2020 abdulghanima_a 1/25/2022 DQ025:Cancel Gift Aid Declarations RM21992 - Fixing the task for the missing filepath - M Shah - 20210513
RM21992 - Fixing the task for the loading of the datafile - M Shah - 20210712
Y DQ025 every day 15:01 colinb 12/11/2020 clives_a 10/22/2021 OP004:Sterling_Lottery_weekly DD File generation and transfer v 2.5 New dates The task copies the files from the output directory from the Cognos server to the NAS1 NFS share that Theon / ZENO have mounts to.

Theon can then be used for the secure FTP transfer of the files

Original Filenames - CF016_a_Direct_Debit_Rejection_Report_for_Weekly_Lotteries.csv

CF017_Positive_Payment_Confirmation_for_Weekly_Lotteries.csv

Add DEceased file. 27/02/2018 - RM14485
Also 4th report for change of bank account created

RM18521 Meena Shah 04-Dec-2018
Use the optimised lottery date functions and procedure

03/03/21
http://camdukcppv01:8080/redmine/issues/22558
Hard coded dates on each of the select statements to rig output
All information on the ticket, details in each step

Reset sechedule to 08.15 on teusdays, been 20.53 on fridays for some time
Y OP004 every Tue 08:15 colinb 12/11/2020 paulm_a 3/3/2021 OP008:DF FINANCIAL Refulfill of documents for the past 7 days due to errored mailing documents - Run adhoc as required - *OFF ALREADY* 07/08/2020
Package updated
Now looks back 4 weeks
OP008:DF FINANCIAL Refulfill of documents for the past seven days due to errored mailing documents - Run adhoc as required - *OFF ALREADY*

This task will move an errored mailing documents from the current day from dataforce2.df_deleted_mailing_docs and place them for fulfilment as part of the next working days run in care_admin.contact_mailing_documents.

This process would normally be used by James / Guillaume to save CIS Development getting involved in resupply if required.
Y OP008 ad-hoc colinb 12/11/2020 clives_a 8/26/2021 OP011:Daily close open CC batches: v1.3 Combined TNSM, TNS and WP Version Converted for POC testing CR 1122. Close open CC batch each night to prevent next day's transactions going into the same batch.

George said it is only CC - REC1 batches that need closing (ie those created by payments taken internally). No need to do anything with those created by web services.

10/10/13: As reqeusted by George, updated to close CC - TNS batches as well.

10/07/2015 : Amended to close TNSM batches as REC1 is not used anymore for CC batches

09/11/2020: Combined for TNS, TNSM and WP bank accounts
Y OP011 every weekday 23:45 colinb 12/11/2020 colinb 12/16/2020 OP012:Daily update Contact Number for records to be Merged on the CARE table Duplicate Contacts and DBA Notes v1.1 If lots of records relating to one contact are palced on the duplicate_contacts table at the same time. Depending on the order of merge, sometimes entries get orphaned as their assocaited contact number gets merged into a new number. CIS does look look for hte new MASTER merged contact number. Consequently this code checks for any records left after the 2AM nightly run and corrects the contact number if it has been merged.

As a side issue the code will also remove any records that have already been merged, but are showing as outstanding on the duplicate_contacts table. i.e. The records may have been subject to a manual merge during the day before the automated process does this anyway.

DBA_NOTES can be left with orphaned reocrds as a result of the merge process. So the process has been extended to update DBA_NOTES master column if any records ahve been merged and the master contact number has been updated.

TO/CC

AS is
N OP012 every day 02:30 colinb 12/11/2020 colinb 12/16/2020 OP014:Income coding Overnight source script - RM7175

Y OP014 every day 22:00 colinb 12/11/2020 clives_a 11/9/2021 OP015:Overnight Full tablescan dedupe JFI the following code can be used to calculate monthly dedupe totals:

SELECT TO_CHAR( dban.merged_on, 'MON-YYYY' ) MONTH_YEAR
, ( SELECT COUNT( 1 )
FROM dba_notes db
WHERE TO_CHAR( db.merged_on, 'MON-YYYY' ) = TO_CHAR( dban.merged_on, 'MON-YYYY' ) ) AS MONTHLY_COUNT
FROM dba_notes dban
WHERE merged_on BETWEEN '01-JAN-2007' AND '31-DEC-2015'
GROUP BY TO_CHAR( dban.merged_on, 'MON-YYYY' )
ORDER BY TO_DATE( '01-' || TO_CHAR( dban.merged_on, 'MON-YYYY' ), 'DD-MON-YYYY' ) DESC
Y OP015 every day 06:00 colinb 12/11/2020 colinb 12/16/2020 OP018:Daily UKChanges to CIS TPS integration This job retrieves the contact amended yesterday and pass those contacts in UKSuppression webservice to find out the suppression status and based on the resultset it updates the Contact Communication Usage Table on the CIS database

Updates:
1.0.37 - Removed weekends from schedule to stop unnecessary emails from being received and ignored.
Y OP018 every weekday 02:00 colinb 12/11/2020 abdulghanima_a 1/12/2022 OP020:GiftAid Cancellation Deceased contacts This Job retrieves the list of contacts that are Deceased and have a active Gift Aid Declarations
Cancel the Gift Aid Declarations for those contacts by calling a Care Webservice of @CancelGiftAidDeclarations
Update the JobStatus in Sql Server Database
Send the email to Stakeholder/CIS team

http://camdukcppv01:8080/redmine/issues/23066
Added Olga Ionova to email list
Y OP020 every day 22:00 colinb 12/11/2020 paulm_a 6/15/2021 OP021:Monthly GiftAid Cancellation Financially Inactive contacts This Job retrieves the list of contacts that are Deceased and have a active Gift Aid Declarations
Cancel the Gift Aid Declarations for those contacts by calling a Care Webservice of @CancelGiftAidDeclarations
Update the JobStatus in Sql Server Database
Send the email to Stakeholder/CIS team

Added Olga to Email list 15 06 2021
http://camdukcppv01:8080/redmine/issues/23067
Y OP021 every month 24th 20:00 colinb 12/11/2020 paulm_a 6/15/2021 OP023:Weekly UKChanges to CIS TPS integration Compare a full extract from CIS to the telephone preference service list from UKChanges

Updates:
1.0.8 - Updating schedule to run 17:30 on Sunday, otherwise it's easy to miss with daily checks on Monday morning
Y OP023 every Sun 17:30 colinb 12/11/2020 abdulghanima_a 1/12/2022 OP025:Overnight Automatic Java merge process

Y OP025 every 12th hour colinb 12/11/2020 colinb 4/20/2021 OP026:Repopulate DUK_CONTACT_COMMS_ANALYSED - CONSENT SYNC

Y OP026 every day 22:30 colinb 12/11/2020 paulm_a 6/25/2021 OP027:Suppressions to Documents & Activities Synchronisation

N OP027 every day 21:00 colinb 12/11/2020 colinb 12/16/2020 OP028:Supporter Legitimate Interest Statuses Update Task updated to run 7 days a week. CB. 26062020 - RM21338

Changed runtime from 12:20 to 18:00. Linked to issues using data with deterministic matcher CB. 14/02/2022. Reverted back as data issues linked to duplicate active membership, not process over runnng, CB 17022022
Y OP028 every day 21:00 colinb 12/11/2020 colinb 2/12/2022 OP029:SM Membership Monthly Transistion 1st Weds - Step 1 - Reviewed CB & PM 131120 21/01/2020 - http://camdukcppv01:8080/redmine/issues/20676 - changed final step email receipents - Paul Mehta
N OP029
colinb 12/11/2020 farisb_a 10/26/2021 OP030:SM Create Monthly Renewals Jobs - Step 2 - Reviewed CB & PM 131120 21/01/2020 - http://camdukcppv01:8080/redmine/issues/20676 - changed final step email receipents - Paul Mehta
N OP030
colinb 12/11/2020 farisb_a 10/26/2021 OP031:SM Create Monthly RRL Output - Step 3 - Reviewed CB & PM 131120 24/06/2020 - http://camdukcppv01:8080/redmine/issues/20532
Changed output location to 2020 folder
Changed Select Mailing Data SM, to include 'SMCs'

20//05/2020 - http://camdukcppv01:8080/redmine/issues/21226
Changed output location to 2020 folder

26/05 changed output folder back to 2019

21/01/2020 - http://camdukcppv01:8080/redmine/issues/20676 - changed final step email receipents - Paul Mehta

22/01/2020 - http://camdukcppv01:8080/redmine/issues/20604 - changed final step email receipents - Paul Mehta
Select DUK New 2015 Sources Changed
Programme codes where varriable by year.
Now fixed to bt FRMB0X for SM and FRMP0X for PM
Full audit of the change in the Redmine ticket

AS Was

SELECT DISTINCT MAILING_CODE as source,
'' AS new_product,
'' AS region,
MAILING_CODE AS new_source,
'care_admin' AS amended_by,
TO_CHAR(TO_DATE(CURRENT_DATE),'DD/MM/YYYY') AS amended_on,
'FIGM' AS new_cost_centre,
(CASE
WHEN LOWER(MAILING_DESC) LIKE '%prof%' THEN
'FRPR' || CHR(ASCII('Q') + (extract (YEAR from CURRENT_DATE) - 2017)) || 'X'
ELSE
'FRMB' || CHR(ASCII('Q') + (extract (YEAR from CURRENT_DATE) - 2017)) || 'X'
END) AS new_programme,
(CASE
WHEN LOWER(MAILING_DESC) LIKE '%prof%' THEN
'FIGM' || 'FRPR' || CHR(ASCII('Q') + (extract (YEAR from CURRENT_DATE) - 2017)) || 'X'
ELSE
'FIGM' || 'FRMB' || CHR(ASCII('Q') + (extract (YEAR from CURRENT_DATE) - 2017)) || 'X'
END) AS new_source_desc
FROM CARE_ADMIN.DUK_RRL_OUTPUT_DATA
WHERE LOWER(MAILING_DESC) NOT LIKE '%seed%'
ORDER BY MAILING_CODE;

TO BE from 22 jan

SELECT DISTINCT MAILING_CODE as source,
'' AS new_product,
'' AS region,
MAILING_CODE AS new_source,
'care_admin' AS amended_by,
TO_CHAR(TO_DATE(CURRENT_DATE),'DD/MM/YYYY') AS amended_on,
'FIGM' AS new_cost_centre,
(CASE
WHEN LOWER(MAILING_DESC) LIKE '%prof%' THEN
'FRMP0X'
ELSE
'FRMB0X'
END) AS new_programme,
(CASE
WHEN LOWER(MAILING_DESC) LIKE '%prof%' THEN
'FIGM' || 'FRMP0X'
ELSE
'FIGM' || 'FRMB0X'
END) AS new_source_desc
FROM CARE_ADMIN.DUK_RRL_OUTPUT_DATA
WHERE LOWER(MAILING_DESC) NOT LIKE '%seed%'
ORDER BY MAILING_CODE;
N OP031
colinb 12/11/2020 usmaana_a 2/15/2022 OP032:Move Closed CC Batches To Cash Book

Y OP032 every 2 hour colinb 12/11/2020 colinb 12/16/2020 OP033:MAST Daily Membership Issues Checks v1.0

Y OP033 every day 05:00 colinb 12/11/2020 colinb 12/16/2020 OP034:Update Payment Plan Error Analysis

Y OP034 every day 04:00 colinb 12/11/2020 colinb 12/16/2020 OP035: MAST Produce Bronto Updates v2.0 --20191204 M Shah
--Select Updates data amended as per Matthew's instructions.

--20210211 M Shah
--RM22450 - Renamed and moved to LIVE as instructed after applying all the changes .

--20210305 M Shah
--RM22450 - Reset the path to '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\' from '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\'

--20210322 M Shah
--RM22450 - Reset the path to '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\' from '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\'.

--20211007
--See Redmine http://camdukcppv01:8080/redmine/issues/23401
--Select stament changed by Paul Mehta 07/10/21
Y OP035 every day 19:45 abdulghanima_a 10/1/2021 paulm_a 10/8/2021 OP003:BrontoAPI - download unsubscribers v 2.0 This has been replaced by OP035

N OP003
colinb 12/17/2020 abdulghanima_a 1/26/2022 OP035: MAST Produce Bronto Updates v1.0 --20191204 M Shah
--Select Updates data amended as per Matthew's instructions.

--20210211 M Shah
--RM22450 - Renamed and moved to LIVE as instructed after applying all the changes .

--20210305 M Shah
--RM22450 - Reset the path to '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\' from '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\'

--20210322 M Shah
--RM22450 - Reset the path to '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\' from '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\'.

N OP035
meenas_a 2/22/2021 abdulghanima_a 1/26/2022 OP035: MAST Produce Bronto Updates v2.0 AMA --20191204 M Shah
--Select Updates data amended as per Matthew's instructions.

--20210211 M Shah
--RM22450 - Renamed and moved to LIVE as instructed after applying all the changes .

--20210305 M Shah
--RM22450 - Reset the path to '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\' from '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\'

--20210322 M Shah
--RM22450 - Reset the path to '\\duk-inf-fs01\cis-prod$\BRONTO LIST FILES\List Updates\' from '\\gs1dukfilv01\shared$\BRONTO LIST FILES\List Updates\'.

N OP035
abdulghanima_a 2/20/2021 abdulghanima_a 1/26/2022 OP036: MAST Upload Bronto Unsubscribes v1.0 --20210211 M Shah
--RM22450 - Renamed and moved to LIVE as instructed after applying all the changes .
N OP036
meenas_a 2/22/2021 abdulghanima_a 1/26/2022 AF001:Daily Community_Fundraising_Materials_Upload 04/06/2020 add ed Kelly and Stephen to daily email confirmation
Y AF001 every day 07:55 colinb 12/11/2020 abdulghanima_a 1/24/2022 AF002:Weekly 0XPGVXG_HCP_Eating_Well_Postcard_Upload

Y AF002 every Mon 07:35 colinb 12/11/2020 colinb 12/16/2020 AF003:Weekly 0XPEDXY_Make_The_Grade_Pack

Y AF003 every Mon 07:56 colinb 12/11/2020 colinb 12/16/2020 AF004:Daily Type 1 Gift Bag Fullfilment - Online MF Parents Form BLENDER DATA LOAD DEPDENCY IN - CHARLOTTE AUSTION - 17/12/2020
[LIVE] Type 1 Bag parent
'/Usr/manifesto/MF_T1_BAG_PARENT'
MF_T1_BAG_Parent_YYYYMMDD.csv
Source = 0XOTOZP02

BLENDER DATA LOAD DEPDENCY MAILING HISTORY - CIS - 17/12/2020
[LIVE] Mailing Histories Type1 Bag Phase2 Fullfillment
must drop data off to \\DUKVWBLNDRP01\auto\MailingHistories

MOSAIC Fulfillment files droped here
/Usr/Mosaic/Type 1 Bag - Fulfilment files/phase2
file format DiabetesUK_0XOTOZP02_type1bagphase2_YYYYMMDD.csv

17/12 - Took 20 minutes to run E2E. PASS
Y AF004 every weekday 08:15 colinb 12/11/2020 abdulghanima_a 12/8/2021 AF005:Daily Type 1 Gift Bag report v2 - MF Online Form and CCC MF Form BLENDER DATA LOAD DEPDENCY MAILING HISTORY - CIS - 17/12/2020
[LIVE] Mailing Histories - Type1 Bag fulfillment
must drop data off to \\DUKVWBLNDRP01\auto\MailingHistories

MOSAIC Fulfillment files droped here
/Usr/Mosaic/Type 1 Bag - Fulfilment files
file format DiabetesUK_0XOTOZL01_type1bagp_YYYYMMDD.csv

N AF005 every weekday 07:05 colinb 12/11/2020 abdulghanima_a 12/8/2021 AF006:Weekly Mosaic RG_DD Cancellation Letter: v1.0 - Order_type
- Amount
- Cancellation reason - please exclude 'DE' and 'GA'
- Orders cancelled that week - using cancelled on date

We would like the file to go to Mosaic via our SFTP and we discussed a Task Manager process for this. Could we look at running this on a Thursday evening for a Friday delivery?

Thanks

Paul

05 Mar 2020
PM updated the distribution list to include CIS and CIS Support

28 Apr 2020
Meena Shah removed from theh success email recipients.

01/10/2020
Added grace, niall and serena to cc
N AF006 every Wed 20:00 colinb 12/11/2020 clives_a 2/3/2022 AF007:MOSAIC SFTP Transfer -MORH test
Y AF007 every weekday 10:00 colinb 12/11/2020 clives_a 12/10/2022 DE005:Weekly eTouches Contacts Extraction: v1.0 http://camdukcppv01:8080/redmine/issues/20431
11 Feb 2020
Task disabled as part of the Task maanger Upgrade in Summer of 2019.
Tests being run to tunr back on. DE005 then DE006.

12Feb
Updating email distribution list to add CISSupport
Y DE005 every Mon 06:00 colinb 12/11/2020 abdulghanima_a 11/3/2021