Intake | RAMP EndProductNotFound error needs EP assignment

[pkarman]

e.g. https://sentry.ds.va.gov/department-of-veterans-affairs/caseflow/issues/3992/events/270606/

When a new RAMP refiling Intake is started, there is an initial check to see if all existing EPs are active. If the EndProductEstablishment for an existing RampElection row has a nil reference_id then it will raise a EstablishedEndProductNotFound error.

Before raising that error, we should try and check for an existing EP matching the date and code the veteran and assign it to the EPE. It may be that there are orphans on the Caseflow side and orphans on the VBMS side that merely need to be connected.

Tech notes

• Could either rescue the exception in https://github.com/department-of-veterans-affairs/caseflow/blob/master/app/models/ramp_review.rb#L85 or let EPE .sync! do the matching attempt itself. Probably better to do it in RampReview since it is RAMP specific.

[leikkisa]

There are 2463 established RAMP Elections that have a successful intake, but that are missing an End Product Establishment (EPE).

RampElection.joins("LEFT OUTER JOIN end_product_establishments epe on ramp_elections.id = epe.source_id AND epe.source_type='RampElection'").where("epe.id IS NULL and ramp_elections.established_at IS NOT NULL")

1668 Are not connected to an existing end product and have a single successful intake. The first one was intaken on 2018-01-30 and the last one was intaken on 2018-07-03. We believe these are from before using end product establishments and were not backfilled. EPEs were backfilled for most of these RampElections, some of them are missing established_at dates, but the oldest established_at date for a RampElection EPE is 2017-11-15.

RampElection.joins("LEFT OUTER JOIN end_product_establishments epe on ramp_elections.id = epe.source_id AND epe.source_type='RampElection'").where("epe.id IS NULL and ramp_elections.established_at IS NOT NULL").select{ |r| r.intakes.where(completion_status:"success").count == 1 && r.intakes.where(error_code: "connected_preexisting_ep").count == 0}

795 are connected to an existing end product, and so should not have an end product establishment. There was previously a bug in the code which could lead to these being connected to a canceled end product, so it's possible that some of these were not connected to an active end product. This bug was fixed in late February, allowing only active end products to be connected.

RampElection.joins("LEFT OUTER JOIN end_product_establishments epe on ramp_elections.id = epe.source_id AND epe.source_type='RampElection'").where("epe.id IS NULL and ramp_elections.established_at IS NOT NULL").order(established_at: :desc).select{ |r| r.intakes.where(completion_status:"success", error_code: "connected_preexisting_ep").count >= 1}

An important note for triaging is that 58 of these have multiple intakes (up to 3 in prod), which seems to be caused by the RampElection being rolled back, and intaken again. Upon being intaken again, established_at on the RampElection gets updated.

RampElection.joins("LEFT OUTER JOIN end_product_establishments epe on ramp_elections.id = epe.source_id AND epe.source_type='RampElection'").where("epe.id IS NULL and ramp_elections.established_at IS NOT NULL").order(established_at: :desc).select{ |r| r.intakes.where(completion_status:"success").count > 1}

Because of the re-use of RampElections, one RampElection can have multiple end products in BGS with the same duplicate information (but potentially different statuses). During our research, we found one that had three end products, two canceled and one pending, but no end product establishment. Previously, before the above bug fix, it was possible for a RAMP Election to get connected to a canceled end product as well.

In early March some fields were cleaned up from the RampElection table in favor of the EndProductEstablishment table, including the claim_id for connected RampElectionIntakes. This is leading to prior connected RampElectionIntakes being unable to find the connected end product to check its status, which is causing this bug for RampRefilings. Ideally, these point to the most recent RampElection end product.

For the short term, if the RAMP refiling cannot find the end product from the end product establishment in order to check the status, we will look for the most recent RAMP Election end product for that Veteran to check its status.

We have decided not to backfill data at this time due to the complexity of this issue, and not 100% understanding the RampElections with multiple intakes. However, we should consider backfilling EPEs for unconnected RampElections that are missing EPEs at some point.

[leikkisa]

Validation

This is going to be difficult to manually validate, because in UAT there are no RampElections missing an EndProductEstablishment, and the code doesn't allow new ones to be created like that.

So I suggest we validate it by looking for this error in Slack. Let's take a look at it after Wednesday, April 17th and see if there were any recent instances of this.

[Sjones352]

Looked through the alert slack messages since April 17th and I see no errors so closing the issue.