Bulk Submission (covers multiple reporting periods)

In addition to weekly submissions covering weekly reporting periods, an additional feature has been incorporated into the NHSD OCVC validation pipeline whereby one-off bulk submissions can be made that cover multiple reporting periods. The bulk submission feature is a temporary feature which has been implemented which allows a system supplier to submit historical data in one go rather than requiring them to split historical data into separate weeks and to have to make separate individual weekly submissions. It is crucial that a supplier follows the following process and in the image below should they decide to use the bulk upload feature.

The process is as follows:

1. The supplier extracts the OCVC bulk submission data from their supplier system in CSV format.

2. The supplier sends the bulk data to the OCVC TEST MESH mailbox X260T213 using workflow ID BULK_UPLOAD.

3. The supplier awaits the submission Excel report which is sent back to the same mailbox and is generated after the submitted data has been processed by the OCVC validation pipeline.

4. If there are file rejection errors these should be fixed first, the reason for the file rejection will be contained in the Excel report.

5. The supplier attempts to submit their corrected OCVC bulk data submission to the TEST environment again.

6. If there are record rejection errors, the supplier should fix as many (ideally all) of these errors as possible so that all valid data can be captured.

7. Once the supplier is happy that all file rejections and as many of the record rejection errors are resolved as possible, only then should the supplier submit to the live pipeline.

8. Upon receiving the submission in the live pipeline, it will be processed, a submission report will be generated and returned back to the sender and a flag will be set which means that a supplier will no longer be able to make another bulk submission again (for the tables processed).

It is important that suppliers make sure that their bulk submission is processed successfully using the test pipeline and they must be confident that there are no file and limited record rejection errors in their test submission before submitting to the live production environment.

It is not a requirement to have zero record rejection errors and the pipeline will still ingest the good records that pass validation, however, the number of record rejection errors should be minimised. This is because each supplier has a one-time opportunity to make a bulk submission of historical data (per table) and once a supplier has made a bulk submission, they will no longer be able to make another bulk submission again and they will be restricted to using the standard weekly submission functionality again.

The only means of transferring bulk OCVC submission data to NHSD is via CSV format. JSON and XML submissions covering multiple reporting periods are not permitted. In order to take advantage of this functionality a supplier must extract bulk data as CSV delimited from their supplier systems and as is the case for weekly standard submissions, data for each table should be stored in individual CSV. The bulk submission CSV files must be pipe (|) delimited and each CSV must have a corresponding .ctl file so that it can be transferred from a supplier MESH mailbox to the NHSD mailbox by the MESH transport mechanism.

The difference between a bulk CSV submission and a standard weekly CSV submission is that records are permitted for multiple reporting weeks in the bulk submission files. Since multiple weeks worth of data can be submitted in one go, records are distinguished from each other based on the header fields which, as is also the case for weekly standard submissions, are included in every row; these header fields include the reporting period start date and the reporting period end date associated with each line record in the CSV file. A single standard submission only contains data for one reporting period, whereas a bulk data submission can contain data for multiple reporting periods and therefore the header fields in each CSV table can refer to numerous weekly reporting periods.

Only 1 bulk submission per table per supplier is allowed in the production environment. Therefore ALL data for a given table should be submitted at once. Any missing weeks can then be submitted via the usual weekly submission route.

In terms of functionality, the same validation pipeline software is used to process both standard weekly and bulk submissions, however, for a bulk submission, it is run multiple times to process the bulk data week by week. Notable features are

• for a bulk submission, a single submission report per table is generated and returned to the supplier e.g. if 26 weeks of historical data is included in a bulk submission then a single submission Excel report will be returned to the supplier which covers all the warnings and errors found across all 26 weeks worth of data
• if an individual week within the bulk data is processed and raises a file rejection error (for example if the difference between the reporting period start and end dates is not a week), then the whole bulk submission will be rejected and an error returned back to the supplier in the submissions report stating that this has happened
•  data set created date and time should be the date and time that the bulk extract was generated and therefore this field will be the same for every record in each of the bulk submission CSV table files
• data set created date and time should be after the last reporting period end date (i.e. no future data is permitted)
• the bulk submission feature will be available for the rest of this financial year only and therefore any bulk submissions must be made before 1st April 2023
• prior to 1April 2023 bulk submissions can be back-dated to 27th December 2021 (reporting period start date)

The MESH mailbox IDs for a bulk submission are the same as those for a standard submission however a different workflow ID needs to be used.

Example JSON, XML and CSV templates and .ctl files for both the standard weekly submission and the bulk submission formats can be found along with this guidance.