Every 2 weeks I will need to send a file to a benefit provider. The file will contain ~7,000 employee records. It will contain both active & terminated employees. If an employee has been terminated, their record needs to be sent to the provider in 3 consecutive files. Once the terminated employee has been sent to the provider on 3 occasions, they can be excluded from the file.
I am looking for a way to track how many times a terminated employee has been included in a file so that I may exclude them at the appropriate time. This interface will not be scheduled. It will run once every 2 weeks, but some weeks it may be done on a Monday morning, the next time it may be a Wednesday afternoon etc. It will depend on whenever an internal resource decides to execute the interface. The source system is Workday.
I was thinking of archiving the file locally each time it runs. On subsequent runs, load the past 3 files into a cache. When processing the current file, for each terminated employee, see if they were included in past files as a terminated employee.
The challenge with this approach is that until the process has been executed 4 times, I will not have 3 past files to reference. I will also need to name previous files in such a way that I can figure out which files are the 3 previous files.
Has anyone had a similar requirement or can suggest some approaches?