POL00028744
POL00028744
Peak Incident Management System
Call Reference PC0224126 Call Logger Deleted User -- EDSC
Release Reported In -- HNG-X R6.50 Top Ref PC0223870
Call Type Cloned call Priority B -- Progress stopped
Contact _ Deleted Contact Call Status Closed -- Avoidance Action Supplied
Target Date 26/04/2013 Effort (Man Days) 0
Summary Branch 011458 has an unexplained discrepancy - old BTS_DATA recs
All References Type Value
QFP Review QFP RC
DevintRel-Director Live Supp.Test
Clone Master PC0223870
DevintRel-Director Live Supp.Test
MSC 04350378913
TRIOLE for Service A2401368
Progress Narrative
[oate:08-Mar-2013 13:41:56 Use
(FALL PC0224126 opened
Details entered are:~
summary:Branch 011458 has an unexplained discrepancy
all Type:c
call Priority:8
flarget Release: HNG-X R6.50
jouted to:EDSC - Anne Chambers
‘Anne Chambers
ate:25-Feb-2013 16:55:53 User:_customer Call_
FALL PC0223870 opened
lbetails entered are:~
summary: The Branch as an unexplained discrepancy
all Type:L
all Priority:¢
ffarget Release: HNG-X R6.50
outed to:EDSC - _Unassigned_
ate/Time Raised: Feb 25 2013 4:51PM
Priority: C
contact Name
contact Phont
wiginator: xXxXxx@TFSsoT
riginator's reference: A2401368
product Serial No:
Product Site: 011458
ffransfer Note: Transfer Group from 'POA-HSD IMT" to 'POA-FJ-PEAK"
Ihe Branch as an unexplained discrepancy. They balanced and rolled trading period on 6 Feb, they have one stock unit AA and this
as balanced with a loss of £39.57 which was transferred to local suspense however the figure that was cleared out from local
suspense was much higher £9839.45.
It have carried out transaction logs for all transactions from date range 31 Jan to 06 Feb. Branch has submitted copies of the final
balance reports from TP 10 BP4 and TP10 BP 5 the balance report for TP10 BP5 shows discrepancy transferred of £39.57 and then
Hiscrepancy resolved £9839.45. The transaction log completed for all transaction does not show any other figures being entered into
pr removed from housekeeping/local suspense account.
jode 1
16/02/2013 16:50 to 16:51
JAA TP 10 BP4/5
fASOO1
incident History:
2013-02-25 16:51:20 [I Richardson, Mark
INIT : Create a new request /incident/problem/change/issue
F/1048.1/1
POL00028744
POL00028744
Pors-02-25 Tersar19 I Richardson, Mark I
Jzneun_en_poa : Open Notification
2013-02-25 16:54:19 [ Richardson, Mark }
kneut_en poa : Transfer Notification
0013-02-25 16:54:54 [ Richardson, Mark ]
lneut_en_poa : Transfer Notification
[Date:25-Feb-2013 16:59:22 Uscr:Glive Turrell
Product General/Other/Misc -- Unknown General/Other/Mise (version unspecified) added.
[e:25-Feb-2013 1
[fhe Call re
progress was delivered to Consumer
2:16 User:Clive Turrell
rd has been assigned to the Team Member: Anne Chambers
jbate:25-Feb-2013 17:47:51 User:Anne Chambers
[the call summary has been changed from
Ihe Branch as an unexplained discrepancy
the call summary is no
Branch 011458 has an unexplained discrepancy
jbate:26-Feb-2013 13:18:12 User:Anne Chambers
[Start of Response]
hen they completed the balance on 6th Feb and cleared the loss from Local Suspense, the amount cleared was £9839.
floss they had put into local suspense, which was £39.57
5 instead of theI
Whis appears to be a consequence of something that happened during the previous TP rollover on 2nd Jan: a ‘gain to local suspense’
f £9799.88 was included in the DEF opening figures.
Ht don't think any of the local suspense products should ever appear in opening figures, however I have found 14 such lines (all
rod 6295 Gain to LS, all different branches). Unfortunately all created Nov-Dec last year, so there is almost no remaining counterI
evidence.
ontinuing to invetigate cause and implications.
[End of Response]
esponse code to call type L as Category 40 -- Pending ~~ Incident Under Investigation
esponse was delivered to Consumer
[Date:26-Feb-2013 13:33:23 User:Kevin McKeown
[the call Priority has been changed from C
[fhe call Priority is now B
[Date:26-Feb-2013 1.
evidence Added - 5:
ing bal
[e:26-Feb-2013 13:53:26 User:Anne Chambers
ie only keep opening figures for 3 old trading periods, so can't be sure when problem started.
Jpate:26-Feb-2013 1.
[Start of Response
sked Ubrahim at NBSC
[End of Response]
esponse code to call type L as Category 40 -- Pending -- Incident Under Investigation
Response was delivered to Consumer
if the suspense report from 2nd January is available - he'll obtain it and email to HSD IMT.
jate:26-Feb-2013 1
[Start of Response]
suspense report for TP9 (2nd Jan 2013) received, which shows no anomaly. I thought it might show 9799.88- c/f, since that amount isI
b/f on the TP10 report, but it doesn't.
1:32 User:Anne Chambers
[the branch also sent the suspense report for TP10 2012, with the comment that they had the same problem last year - and although it]
lis a very faint copy, it appears to show 9799.88- b/f.
[End of Response]
Response code to call type L as Category 40 -- Pending —- Incident Under Investigation
Response was delivered to Consumer
Te:26-Feb-2013 16:53:46 Uscr:Anne Chambers
evidence Added - Branch
sus
Jbate:26-Feb-2013 17:56:09 User:Anne Chambers
[Start of Response]
lave asked what the branch did about the problem last year.
so checking what the archiving strategy has been for BRDB_RX SU_OPENING BALANC
in the past, and when it changed. F/1048.1/2
POL00028744
POL00028744
Tend of Response!
lkesponse code to call type L as Category 40 -- Pending -- Incident Under Investigation
esponse was delivered to Consumer
Te:27-Feb-2013 16:18:08 User:Anne Chambers
lkvidence File Updated - Updated notes / oxtracts ©
jate:27-Feb-2013 1
[Start of Response]
I have now found the cause of the problem: some data from autumn 2010 has been retained in table BRDB_RX_BTS DATA, for 14 separate
ranches. These branches will all have been affected by this problem late 2011 and late 2012, though in some cases the amounts
involved are small.
0:17 User:Anne Chambers
ranch Affected TP AMOUNT
02647 9 6.71
02840 9 140.61
100079 0.01
11458 10 9,799.88
12004 9:16.12
54011 9 3.34
ho1832. 95.84
04937 9-49.62
155025. 9 --113.14
56715 9:11.55
11844. 9-41.77
43242 9 -0.51
pee4is8 9 «3,186.70
b97611 9 160.92
[End of Response]
esponse code to call type L as Category 40 -- Pending -- Incident Under Investigation
kkesponse was delivered to Consumer
joate:27-Feb-2013 16:22:43 User:Anne Chambers
still to be investigated:
LExactly how did these records cause the observed effect?
Lwhy were the records not removed by the normal archiving process?
HWhat impact has the problem had on the branch accounts?
What impact has the problem had on POLSAP?
[How do we remove the records to prevent future problems?
Were there any affected branches which have since closed?
[There are 19 other branches which have old data in the affected table, but not relating to Local Suspense ? could this cause any
roblems with the branch accounts? (some may be temporarily closed)
Te:28-Feb-2013 14:06:00 User:Anne Chambers
[Start of Response]
Further investigations:
* Exactly how did these records cause the observed effect?
[the old data is picked up, along with the correct data, during the Branch Trading Statement production (at the end of the period
BEFORE the problem is visible at the branch). The old opening figure (which may be for one or more of the Local Suspense products
295-6298, associated with an unknown stock unit) gets converted during branch rollover, to prod 6295 Gain to Local Suspense
(though the sign may be positive or negative), and written as an opening figure for the subsequent period, stock unit DEF.
Jat the end of the second period, this is included when the amount to be cleared from Local Suspense is calculated (when the last
stock unit is rolled over), so the branch is forced to clear it.
* How to identify the problem from branch reports
ranch Trading Statement: the sum of the two D:
Lines
Suspense Account report: the B/Fwd figure on the report does not match the C/Fwd figure on the report for the previous TP.
epancy Transferred lines does not match the total of the two Discrepancy ResolvedI
h* Why were the records not removed by the normal archiving process? The records, created Autumn 2010, belonged to stock units thatI
ave since been deleted. Normally they would have been removed before the ‘deleted stock unit' entry itself was removed from the
flist of stock units, but there were some teething problems with archiving and they were ‘orphaned’ and are now ignored by the
lrchiving process.
h* How do we remove the records to prevent future problems? Once well away from the problem TP, eg once into TP 1, get the branches}
to recreate a stock unit with the same name as the problem one, then delete it again. Or we just delete them from the database
nder MSC.
H+ Were there any affected branches which have since closed? I have checked branches closed within the last 90 days, all ok. For
branches closed longer ago which might be under investigation, the suspense account reports and BTS could be checked (see above) .
** There are 19 other branches which have old data in the affected table, but not relating to Local Suspense ? could this cause an:
problems with the branch accounts?
It have checked very carefully and conclude that these extra lines have no ongoing impact on the branch accounts.
* There is old data in some other tables that use the same archiving strategy - this doesn't affect the branch balancing in the
same way, but what would happen if a stock unit with the same name was recreated?
F/1048.1/3
POL00028744
POL00028744
Fe Any corrective actions?
tocal suspense should always be zero when the branch is rolled over - should this be checked and reported?
It think the archiving strategy is now ok, but this may need to be reviewed.
Still to do:
* What impact has the problem had on the branch accounts?
* What impact has the problem had on POLSAP?
[End of Response]
esponse code to call type L as Category 40 -- Pending
lkesponse was delivered to Consumer
Incident Under Investigation
[pate:28-Feb-2013 1.
[Start of Response]
jorrection:
4:24 User:Anne Chambers
* Exactly how did these records cause the observed effect?
Ihe old data is picked up, along with the correct data, during the Branch Trading Statement production (at the end of the period
BEFORE the problem is visible at the branch). The ***¥old BTS data line**** (which may be for one or more of the Local Suspense
products 6295-6298, associated with an unknown stock unit) gets converted during branch rollover, to prod 6295 Gain to Local
suspense (though the sign may be positive or negative), and written as an opening figure for the subsequent period, stock unit DEF.
t the end of the second period, this is included when the amount to be cleared from Local Suspense is calculated (when the last
stock unit is rolled over), so the branch is forced to clear it.
[End of Response]
esponse code to call type L as Category 40 -- Pending -- Incident Under Investigation
esponse was delivered to Consumer
[Date:28-Feb-2013 17:17:58 Uscr:Anne Chambers
lrvidence File Updated = Anothe: upd. otes / extracts
[Date:28-Feb-2013 17:18:31 User:Anne Chambers
evidence Added - Spreadsheet sent to POL
JDate:06-Mar-2013 16:05:10 User:Anne Chambers
[Start of Response]
here was a conference call with POL (Laura Darby, Mark Wardle and others) on 28th Feb about this call, and the spreadsheet showing]
the impact of the problem on the 14 branches was sent to them by Steve Bansal. We are waiting to hear from Mark whether this is
sufficient information for them to resolve the consequences on the branches and POLSAP.
le will then need to get the old data causing the problem removed from the database (before TP 9 - Oct-Nov 2013) and consider
ihether extra checks should be put in place to trap similar anomalies in the future.
[End of Response]
esponse code to call type L as Category 40 -- Pending ~~ Ti
esponse was delivered to Consumer
cident Under Investigation
joate:08-Mar-2013 13:37:01 User:Anne Chambers
[Start of Response]
POL will be arranging another conf call next week.
[End of Response]
esponse code to call type L as Category 40 -- Pending -- Incident Under Investigation
esponse was delivered to Consumer
Date:08-Mar-2013 13:41:46 User:Anne Chambers
Sloning call so that we can get the old data removed....
joate:08-Mar-2013 13:41:56 User:Anne Chambers
all cloned from original call:PC0223870 by User:Anne Chambers
joate:08-Mar-2013 14:54:35 User:Anne Chambers
[Start of Response]
here are records in table BRDB_RX_BTS_DATA, created Autumn 2010, belonging to stock units that have since been deleted. Normally
they would have been removed before the ‘deleted stock unit! entry itself was removed from the list of stock units, but there were
some teething problems with archiving and they were ‘orphaned’ and are now ignored by the archiving process.
[thee are also some old records in BRDR SU OPENING BALANCE for the game reason.
Additional notes and extracts attached.
lease provide script and get MSC raised to remove old data belonging to non-existent stock units from these tables, and consider
nether any changes are needed to the archiving strategy to prevent this happening again.
[End of Response]
code to call type C as Category 40 -- Pending ~~ In
espons
dent Under Investigation F/1048.1/4
7
POL00028744
POL00028744
[late:08-Mar-2015 I!
[the call summary has been changed from.
ranch 011458 has an unexplained discrepancy
fhe call summary is now:
Branch 011458 has an unexplained discrepancy - old BTS_DATA recs
[Date:08-Max-2013 1:
lsvidence Added - Acai.
[Date:08-Max-2013 15:19:54 Uscr:Anne Chambers
The Call record has been transferred to the team: BDB-Host-Dev
te:i1-Mar-2013 09:49:47 User:Vijesh Pandya
eference Added: QFP Review QFP RC
jbate:13-Mar-2013 09:47:25 User:Anne Chambers
le need to make sure a copy of the records being removed is kept somewhere in case there are questions to be answered about any of
the affected branches’ accounts.
Fe:2i-Mar-2013 16:02:39 User: Steve Goddard
Ihe Call record has been assigned to the Team Member: Wing Pang
jbate:27-Mar-2013 13:59:43 User:Wing Pang
[he extract which Anne done shows that all the records that we want deleted were inserted in 2010. We need to delete ONLY the old
hecords in BRDB_RX BTS DATA and BRDB SU_OPENING BALANCE; ie those inserted before 01-JAN-2011.
bperat ion:
1) copy of the records being removed
REATE TABLE OPS$BRDB.SAV_BRDB_RX_BTS DATA
Beuicr + from OFS¢BRD-9ROB_RX_B8S DATA
HERE trunc(insert timestamp) < TO_DATE('20110101', *YYYYMMDD') ;
REATE TABLE OPS$BRDB.SAV_BRDB_SU_ OPENING BALANCE
SELECT * from OPS$BRDB.SAV_BRDB_SU_OPENING BALANCE
ERE trunc (insert timestamp) < TO_DATE('20110101", 'YYYYMMDD') ;
) get the number of records in SAV_BRDB_RX BTS DATA and
SAV_BRDB_SU_OPENING BALANCE
belect count (*) from OPS$BRDB.SAV_BRDB_RX BTS DATA;
select count (*) from OPS$BRDB.SAV_BRDB_SU OPENING BALANCE;
) delete records
ELETE FROM OPSSBRDB.SAV_BRDB_RX_BTS_DATA
HERE trunc (insert timestamp) < TO_DATE('20110101', 'YYYYMMDD") ;
ELETE FROM OPSS$BRDB.SAV_BRDB SU OPENING BALANCE
HERE trunc(insert timestamp) < TO_DATE(*20110101', 'YYYYMMDD");
4) commit if nos of deleted records = nos of records in sav table
[Date:10-Apr-2013 08:44:08 Uscr:Gareth Seemungal
Peer review alteration:
[the extract which Anne done shows that all the records that we want deleted were inserted in 2010. We need to delete ONLY the old
becords in BRDB_RX BTS DATA and BRDB_SU_OPENING BALANCE; ie those inserted before 01-JAN-2011.
Operation:
h) copy of the records being removed
CREATE TABLE OPS$BRDB.SAV_BRDB_RX_BTS DATA
As
SELECT * from OPS$BRDB.BRDB_RX_ BT'S DATA
HERE trunc(insert timestamp) < TO DATE(*20110101", 'YYYYMMDD") ;
F/1048.1/5
POL00028744
POL00028744
CREATE TABLE OPSSBRDB.SAV_BRDB_SU_OPENING BALANCE
ls
SELECT * from OPS$BRDB.SAV_BRDB_SU_OPENING_BALANC!
HERE trunc (insert timestamp) < TO DATE('20110101
YYYYMMDD")
) get the number of records in SAV_BRDB_RX BTS DATA and
ISAV_BRDB SU OPENING BALANCE
SELECT COUNT(*) FROM OPS§BRDB.SAV_BRDB_RX BTS DATA;
ELECT COUNT (*) FROM OPS$BRDB.SAV_BRDB_SU_OPENING BALANCE;
b) delete records
DELETE FROM OPS$BRDB.BRDB_RX_BTS_DATA
WHERE trunc (insert timestamp) < TO DATE('20110101", 'YYYYMMDD");
DELETE FROM OPSSBRDB.BRDB_SU_OPENING BALANCE
HERE trunc(insert_timestamp) < TO_DATE(*20110101', ‘YYYY¥MMDD")
) commit if nos of deleted records = nos of records in sav table
joate:10-Apr-2013 08:50:55 User:Wing Pang
[fhe Call record has been transferred to the team: EDSC
Date:10-Apr-2013 09:14:41 User:Mike Croshaw
fhe Call record has been assigned to the Team Member: Anne Chambers
7i5-Apr-2013 15:40:01 Uscr: Mail Manager_
jbate:24-Apr-2013 14:03:50 User:Anne Chambers
ffarget Date/Time updated: new value is 26/04/2013 13:41
[Start of Response]
ISC 0430378913 raised to remove recs from BRDB - Thurs 25th April.
[End of Response]
esponse code to call type C as Category 40 -- Pending -- Incident Under Investigation
[Date:24-Apr-2013 14:04:03 User:Anne Chambers
eference Added: 33037991
Date:25-Apr-2013 11:02:46 User:Anne Chambers
Evidence Added - Removal of »
[Date:25-Apr-2013 11:57:37 User:Anne Chambers
[Start of Response]
ecords have been removed, details attached. Witnessed by Dave Seddon.
{End of Response]
esponse code to call type C as Category 40 -- Pending -- Incident Under Investigation
[Date:25-Apr-2013 16:07:27 Uscr:Anne Chambers
[Start of Response]
closing call.
[End of Response]
Response code to call type C as Category 70 -- Final -~ Avoidance Action Supplied
Routing to Call Logger following Final Progress update.
Defect cause updated to 14 -~ Development = Code
[Date:25-Apr-2013 16:08:40 Uscr:Anne Chambers
“ALL PC0224126 close:
Category 70 Type C
Root Cause Development - Code
Logger Deleted User -- EDSC
Subject Product General/Other/Misc -- Unknown (version unspecified)
Assignee Deleted User -- EDSC :
Last Progress 25-Apr-2013 16:08 -- Anne Chambers
F/1048.1/6