File of 527EZ applications since launch for downstream reporting

[matt4su]

The purpose of this ticket is to produce an output file (csv, or xls) of all successfully submitted pension applications from 1/31/24 to present. The file will be used to match to records housed in the backend database for VBMS to differentiate between online and paper application submissions. The file shall contain at least the following data elements for each record:

• application submission date/time

• application id

• participant-id

• applicant first, last, middle name

• applicant ssn

• applicant date of birth

• file number

• confirmation number

• The file will be placed in a TBD location in Sharepoint as it contains PII

• Name the files such that is easily identified as online submissions and the date range (e.g. 527ezonline 013024_080224)

[TaiWilkin]

@matt4su Here is a censored version of the data. When looking at submitted forms, we have several data sources we can pull from - the SavedClaim, which has a copy of the encrypted form data the user submitted, but which doesn't track the submitting user otherwise; the FormSubmissions, which track attempted submissions to Lighthouse (and which we very recently started recording user_account_id for); and the FormSubmissionAttempts, which track submission attempts and whether they succeeded, but no user data. If we have a user_account_id (which we didn't, until very recently!), we can also sometimes get the ICN and EDIPI from UserAccount and Account objects; if we have an EDIPI, we can request data from MPI to get participant_id, SSN, etc. I've attempted to name the columns in such a way that the source is clear in case of inconsistencies. 013124-080824.csv If this looks good to you, we figure out the final location for the data and the best way to get it there securely.

[matt4su]

Looks great, thanks Tai. Not seeing the File number that we collect in the Applicant Information section, am I missing it? If no, is that something we can add? That is a data element our VBA stakeholder asked for specifically that, when present, can help tie the application to the VBMS record.

[TaiWilkin]

@matt4su Yes - here's a version with the Veteran File Number. 013124-080924.csv

[matt4su]

perfect, I am going to run this by our VBA stakeholder for feedback.

[matt4su]

Feedback from VBA stakeholder (Derek Uttley) was file looked good but he'd also like the name of the PDF we produce added to the file as that is something he has access to downstream and it could help him match up the records . Can we add that PDF Document Name?

[TaiWilkin]

@matt4su Looking at the code, I believe our claim PDFs will be always be named tmp/pdfs/21P-527EZ_.pdf.

[matt4su]

Have the question out to Derek in Teams chat if he can work with the file without that value, and if so we'll create the actual extract. Will let you know.

[matt4su]

He gave the go ahead, we have the green light to create the extract.

[TaiWilkin]

@matt4su Where would we like the file put in Sharepoint?

[matt4su]

https://dvagov.sharepoint.com/:u:/s/vaabdvro/EfiAZZKEhEZGjtSy0xi_V18B5U2QQalFEIo6Cm17muvOJQ?email=TAI.WILKIN%40va.gov&e=UBjXci

[TaiWilkin]

@matt4su Added under Pensions/submissions_013124_081424.csv. Please confirm that sharing/security settings are correct, if you don't mind, because I'm not very familiar with SharePoint.

[matt4su]

Access settings checked out. It is a folder with limited access and both Derek and I can get to it.