Integrity check - BDX in ODS with incorrect UploadID / Rolling back BDX in Approved state
Problem: https://supporthub.soteria365.com/a/tickets/7027?current_tab=details
BDX item was signed off and loaded into the January 2023 open period is is possible for this to be removed off the report as I am unsure why its showing.
As you can see for the screenshots below it was all posted into the correct systems before the cut off date, please can you look into this and advise.
Investigation Analysis -
This issue has arrived technically because of UploadID. The BDX has been tagged to different upload IDs in Tfiledetails and Lloyds Premium table. See below SS
Because of which the NOT IN condition(circled part in below SS) satisfied and caused error which we're seeing in the report
Also, I verified and it seems this BDX is already posted to ODS
Added a public note 10 days ago (Tue, 16 May 4:52 PM).This BDX has been in every Integrity Checks email since 30.01.2023 when it was last loaded.
It was previously loaded on the 20.01.2023 with a different UploadID. Below is from the Uploads table in database Intrali.
This shows the BDX with three upload ids in Intrali, status 10 = Cancelled, 4 = Complete.
It changed reporting period once, and also changed MapID for the final 30.01.2023 load which then triggered the error.
ODS and Audit refer to the data for the 20.01.2023, but VIPR is showing the data for the new BDX, which cannot be loaded.
When the BDX was deleted from intrali and reloaded on the 30th, it should have triggered a roll back in ODS, to make way for the replacement.
This did not happen.
20/01 Loaded into ODS
23/01 Signed Off Status = 'Approved'
30/01 Loaded into ODS - It did not rollback the old BDX in ODS because it was still Approved
30/01 Integrity check error because Repository ID is in both VIPR and ODS under different UploadID
Summary
- The BDX should not have been reloaded whilst it was still Approved
- This issue should have been picked up by Ops on the 30th January, and not left until May to Address
- According to Dynamics BYOD the current BDX (of the 20/01) is in Dynamics
- We cannot rollback the BDX from ODS (Since it has been booked to Dynamics)
- VIPR-Warehouse contains the latest BDX loaded on 30/01, which is unable to load and flagging an error, RepositoryID already exists in ODS
- ODS contains the latest BDX loaded on 20/01
Next Steps :
- Ops to decide what can/can't be done with old BDX in ODS and new BDX in VIPR-Warehouse
- Data Engineering to consider a new integrity check to flag when a BDX cannot be rolled back due to being 'Approved' to ease troubleshooting in the future
- Timeline od BDX lifecyle -
The 20/01 load of EU-63871 was Approved on the 23/01.
It would have been loaded to Dynamics that same day 23/01.
After it's in Dynamics, after 23/01 you cannot / should not re-submit that BDX.
Ops rolled back and resubmitted on 30/01. ODS will not rollback and replace it because it's already approved.
This was put in place because, historically what's happened, is a BDX has gone to dynamics, it's been resubmitted and allowed to replace the ODS, and now the BDX in ODS does not match Dynamics, the PremIDs are all different and potentially financials are different or policy data. This was deemed unacceptable and therefore a rule was put in place to not allow a BDX to be replaced in ODS if it has already been Approved.