Contents Show
To accommodate requirements for the ENH-585 and ENH-586 enhancements to the SPIRIT WIC system, a new BatchProcessing_Core.zip was created and distributed with Release 2.33.00 to improve the efficiency of End of Day Processing.
To accommodate requirements for the ENH-493, ENH-613, ENH-616, ENH-623, ENH-625, ENH-626, ENH-627, ENH-629, ENH-630, and ENH-638 enhancements to the SPIRIT WIC system, the BatchProcessing_Core.zip was updated and distributed with Release 2.38.00 to improve the efficiency of End of Month Processing.
The file processor was updated to version .NET 3.1 and distributed with Release 2.40 to improve the efficiency of End of Day Processing and End of Month Processing.
The BatchProcessing_Core.zip file contains three files:
appsettings.json containing application configuration information
BatchProcessor.exe containing the batch processing application
nlog.config containing the logging configuration information
Once the required environment variables are created within the Windows operating system, the files contained within the BatchProcessing_Core.zip are extracted, and the configuration files are updated for your state's environment, the BatchProcessor.exe file can be run successfully from the Command Prompt.
|
|
NOTE: The BatchProcessor.exe is intended to be stored and run directly on the server in your state's environment. The following are required to successfully run the BatchProcessor.exe from the Command Prompt to execute End of Day Processing and/or End of Month Processing:
|
The tutorial below outlines how to run End of Month Processing using the BatchProcessor.exe at the Command Prompt.
States/ITOs should run End of Day and End of Month processes in the following order:
Run the original End of Day Process - only required if your state's environment supports checks
Run the new End of Day Command Line Batch Processing
Run the new End of Month Command Line Batch Processing
Complete the following steps to Run End of Month Command Line Batch Processing:
1. Log in or remotely connect as an administrator to the server in your state's environment.
2. Run the Command Prompt as an administrator. The Administrator: Windows Command Processor screen displays.
Administrator: Windows Command Processor window
|
|
NOTE: If the Windows Command Processor screen does not show "Administrator:" in the title bar text, then the Command Prompt is not running in administrator mode. The Command Prompt must be run as an administrator to successfully complete the steps in this tutorial. |
3. Change the directory to the folder in which the contents of the BatchProcessing_Core.zip were extracted. See Step 4 of the BatchProcessing_Core.zip Installation and Configuration topic for more information.
As a default, tasks completed, services performed, and exceptions that occur during BatchProcessor processing not only display color-coded in real time at the Command Prompt, but also are written as new records to the EXCEPTIONLOG and SCHEDULEDJOBLOG tables of the SPIRIT database. See the BatchProcessor Logging Processes topic for more information.
|
|
NOTE: Tasks completed, services performed, and exceptions that occur during BatchProcessor processing can also be viewed on the Event Log - WIC Scheduled Job Administration in the Scheduled Job Administration application module. |
4. Enter the following command and press the Enter key on your keyboard to run the BatchProcessor:
BatchProcessor.exe -EOM [CN]
|
|
NOTE: In the command above, the [CN] attribute identifies the [State Identifier Code] attribute specified in the Windows environment variable for your state's environment. Windows environment variables are added during the Create Environment Variables within the Windows Operating System process of the BatchProcessing_Core.zip Installation and Configuration. See the State Identifier Codes topic for more information. |
The Command Prompt displays the BatchProcessor processes that are executing in real time.
The following processes occur when the BatchProcessor.exe -EOM [CN] command is run at the Command Prompt:
STEP 1
Launch the RunMonthEndProcess stored procedure.
Update the value of the SCHEDULED column in the row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBCONTROL table.
If a conflicting process is not running, set the PROCESSID column equal to 'MONTHEND' within the CURRENTLYEXECUTING table.
Retrieve the value of the STATUSCD column of the most recent row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBSTATUS table.
When SCHEDULEDJOBSTATUS. STATUSCD = '1', run in Restart Last Month mode.
When SCHEDULEDJOBSTATUS. STATUSCD = '0', run in New Month mode and call the RollProcessControlTableToNextMonth stored procedure to update the PROCESSCONTROL table to the next set of dates.
Increment the value of the LASTNUMBERUSED column by '1' where the value of the KEYCODE column is equal to 'MONTHEND' within the SEQNUMBERS table and set parameter @mProcessNbr value equal to the new value.
Retrieve the values of the CURRENTMONTHENDDT, PRIORMONTHENDDT, and CLOSEMONTHENDDT columns within the PROCESSCONTROL table.
Retrieve the values of the following business rules in the STATEBUSINESSRULES table:
MONTHEND_INCL_CASELOADCOUNTS
MONTHEND_INCL_HIGHRISK_DATA
MONTHEND_INCL_CASELOADUNDUP_TBLS
MONTHEND_INCL_DUALENROLLMENT_TBLS
MONTHEND_INCL_REDEMPRECON_TBL
MONTHEND_INCL_CDC_FILES
STEP 2 Caseload Count and Caseload Enrollment Tables
If MONTHEND_INCL_CASELOADCOUNTS business rule equals 'Y', call the following stored procedures. Otherwise, skip this step.
Call the InsertIntoCaseLoadCount stored procedure to insert records into the CASELOADCOUNT table:
Insert food instrument records
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
MODIFYUSERID = 'BATCHUSER'
MODIFYDTTM = Current date and time
Call the InsertIntoCaseLoadCountEnrollment stored procedure to insert records into the CASELOADCOUNTENROLLMENT table:
Insert Enrollment Data and Reported Participant Data records
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
MODIFYUSERID = 'BATCHUSER'
MODIFYDTTM = Current date and time
Call the InsertFullyBFIntoCaseLoadCount stored procedure to insert records into the CASELOADCOUNT table:
Insert fully-breastfed infant participant records
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
MODIFYUSERID = 'BATCHUSER'
MODIFYDTTM = Current date and time
Call the InsertInfantsMostlyBF stored procedure to insert records for infants who are age birth-1 month old, with breastfeeding amount = '2', and have not received benefits into the CASELOADCOUNT and CASELOADCOUNTENROLLMENT tables:
Insert mostly-breastfed infant participant records, but exclude those whose mother did not receive benefits.
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
MODIFYUSERID = 'BATCHUSER'
MODIFYDTTM = Current date and time
Call the InsertIntoCaseLoadCountRebateItems stored procedure to insert records into the CASELOADCOUNTREBATEITEMS table:
Insert rebate item records
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
STEP 3 Redemption History Table
If MONTHEND_INCL_HIGHRISK_DATA business rule equals 'Y', call the following stored procedure. Otherwise, skip this step.
Call the InsertRedemptionHistory stored procedure to insert records into the REDEMPTIONHISTORY table:
Insert redemption history records
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
STEP 4 Caseload Unduplicated Tables
If MONTHEND_INCL_CASELOADUNDUP_TBLS business rule equals 'Y', call the following stored procedures. Otherwise, skip this step.
Call the GetFiscalYear stored procedure to retrieve the current fiscal year.
Call the InsertCaseLoadCountUndupe stored procedure to insert records into the CASELOADCOUNTUNDUPSTATE, CASELOADCOUNTUNDUPFEDERAL, and CASELOADCOUNTUNDUPCALENDAR tables:
Insert unduplicated participant records
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
STEP 5 Dual Enrollment
If MONTHEND_INCL_DUALENROLLMENT_TBLS business rule equals 'Y', call the following stored procedure. Otherwise, skip this step.
Call the InsertIntoDuplicateEnrollParticipation stored procedure to insert records into the DUPLICATEENROLLPARTICIPATION table:
Insert dual enrollment participant records
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
STEP 6 Redemption Reconciliation Tables
If MONTHEND_INCL_REDEMPRECON_TBL business rule equals 'Y', call the following stored procedure. Otherwise, skip this step.
Call the InsertIntoRedemptionReconciliation stored procedure to insert records into the REDEMPTIONRECONCILIATION table:
Insert redemption reconciliation records
PROCESSNBR = @mProcessNbr
CREATEUSERID = 'BATCHUSER'
CREATEDTTM = Current date and time
STEP 7 Pediatric and Pregnancy CDC File Tables
If MONTHEND_INCL_CASELOADCOUNTS and MONTHEND_INCL_CDC_FILES business rules equal 'Y', call the following stored procedures. Otherwise, skip this step.
If the Pregnancy CDC files have been generated for the current quarter, call the CreateCdcPregnancyFileData stored procedure to insert pregnancy records into the CDCPregnancyFile temporary table.
Call the CreateCdcPediatricFileData stored procedure to insert pediatric records into the CDCPediatricFile temporary table.
STEP 8
If a preceding step fails, update the following tables:
Write error messages to the EXCEPTIONLOG and SCHEDULEDJOBLOG tables.
Update the SCHEDULEDJOBSTATUS table by adding a row as follows:
PROCESSID = 'MONTHEND'
STARTDTTM = Code passes the date and time the process started.
ENDDTTM = Code passes the date and time the process failed.
STATUSCD = '1' to prompt the next instance of End of Month processing to run in Restart Last Month mode.
Delete the row where the value of the PROCESSID column equals 'MONTHEND' within the CURRENTLYEXECUTING table.
If all preceding steps succeed, update the following tables:
Update the SCHEDULEDJOBSTATUS table by adding a row as follows:
PROCESSID = 'MONTHEND'
STARTDTTM = Code passes the date and time the process started.
ENDDTTM = Code passes the date and time the process completed.
STATUSCD = '0' to prompt the next instance of End of Month processing to run in New Month mode.
Delete the row where PROCESSID column = 'MONTHEND' within the CURRENTLYEXECUTING table.
SCHEDULEDJOBCONTROL. SCHEDULED = 'N' where PROCESSID = 'MONTHEND' to clear the RunMonthEndProcess scheduled flag
STEP 9 Write the CDC Files to Output Folders
If data exists within the CDCPediatricFile temporary table, call the GetCDCPediatricFile stored procedure to write the CDC Pediatric File to the output folder path value in the ROWTEXT column in the second row within the CDCPediatricFile temporary table.
If data exists within the CDCPregnancyFile temporary table, call the GetCDCPregnancyFile stored procedure to write the CDC Pregnancy File to the output folder path value in the ROWTEXT column in the second row within the CDCPregnancyFile temporary table.
STEP 10 Finalize the CDC Files Process
If the CDC files write successfully, completes the following:
Call the DropCDCFileTables stored procedure to delete the CDCPediatricFile and CDCPregnancyFile temporary tables.
Update the following tables:
SCHEDULEDJOBSTATUS. STATUSCD = '0' in the most recent row where PROCESSID column = 'MONTHEND' to prompt the next instance of End of Month processing to run in New Month mode
SCHEDULEDJOBCONTROL. SCHEDULED = 'N' where PROCESSID = 'MONTHEND'
If a CDC file fails, completes the following:
Call the InsertScheduledJobLog stored procedure to log information to the scheduled job log. Write error messages to the EXCEPTIONLOG and SCHEDULEDJOBLOG tables.
Update the following tables:
SCHEDULEDJOBSTATUS. STATUSCD = '1' in the most recent row where PROCESSID column = 'MONTHEND' to prompt the next instance of End of Month processing to run in Restart Last Month mode
SCHEDULEDJOBCONTROL. SCHEDULED = 'Y' where PROCESSID = 'MONTHEND'
STEP 1
Launch the RunMonthEndProcess stored procedure.
a) Validate the RunMonthEndProcess is scheduled where the value of the SCHEDULED column equals 'Y' in the row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBCONTROL table. If the value of the SCHEDULED column equals 'N', the system writes the E0430 standard error message to the SCHEDULEDJOBLOG table and displays it on the console.
|
|
NOTE: The End of Month Process is scheduled using the Scheduled Job Administration screen. For more information, see the End of Month Process topic. |
b) Validate no conflicting processes are currently running:
i. If a conflicting process is running, the system writes the E0431 standard error message to the SCHEDULEDJOBLOG table and displays it on the console.
ii. If a conflicting process is not running, set the PROCESSID column equal to 'MONTHEND' within the CURRENTLYEXECUTING table to ensure only one copy runs at a time.
c) Determine the run mode.
i. The following are valid values for the @RunMode parameter:
'R' = '1', run in Restart Last Month mode.
'N' = '0', run in New Month mode.
'A' = base the parameter on the value of the STATUSCD column of the most recent row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBSTATUS table:
When SCHEDULEDJOBSTATUS. STATUSCD = '1', run in Restart Last Month mode.
When SCHEDULEDJOBSTATUS. STATUSCD = '0', run in New Month mode.
ii. If the RunMonthEndProcess stored procedure is launched normally, the @RunMode parameter is set to 'A'.
iii. If the RunMonthEndProcess stored procedure is launched directly, outside of the normal process, the following processes occur:
The CDC files are not be processed as described in Step 9.
If the @RunMode parameter is manually set to a value other than 'A', 'R', or 'N', the system writes the E0432 standard error message to the SCHEDULEDJOBLOG table and displays it on the console.
If the @RunMode parameter is manually set to 'N', and SCHEDULEDJOBSTATUS. STATUSCD = '1', the system writes the E0433 standard error message to the SCHEDULEDJOBLOG table and displays it on the console.
d) If the process runs in New Month mode, call the RollProcessControlTableToNextMonth stored procedure to update the PROCESSCONTROL table to the next set of dates. Otherwise, continue the process.
e) Set the parameter @mProcessNbr value equal to the value of the LASTNUMBERUSED column where the value of the KEYCODE column is equal to 'MONTHEND' within the SEQNUMBERS table.
f) Retrieve the values of the PRIORMONTHENDDT, CURRENTMONTHENDDT, and CLOSEMONTHENDDT columns within the PROCESSCONTROL table. If the value of the CURRENTMONTHENDDT column is in the future, the system writes the E0434 standard error message to the SCHEDULEDJOBLOG table and displays it on the console.
g) Retrieve the values of the following business rules in the STATEBUSINESSRULES table:
MONTHEND_INCL_CASELOADCOUNTS
MONTHEND_INCL_HIGHRISK_DATA
MONTHEND_INCL_CASELOADUNDUP_TBLS
MONTHEND_INCL_DUALENROLLMENT_TBLS
MONTHEND_INCL_REDEMPRECON_TBL
MONTHEND_INCL_CDC_FILES
STEP 2 CaseloadCount and CaseloadEnrollment Tables
If the value of the MONTHEND_INCL_CASELOADCOUNTS business rule equals "N", proceed to Step 3. Otherwise, continue with the current step.
Call the InsertIntoCaseLoadCount stored procedure to insert food instrument data records into the CASELOADCOUNT table.
Call the InsertIntoCaseLoadCountEnrollment stored procedure to insert enrollment data and reported participant data records into the CASELOADCOUNTENROLLMENT table.
Call the InsertFullyBFIntoCaseLoadCount stored procedure to insert fully-breastfed infant data records into the CASELOADCOUNT table.
Call the InsertInfantsMostlyBF stored procedure to insert records for infants who are age birth-1 month old, with breastfeeding amount = '2', and have not received benefits, but exclude those whose mother did not receive benefits, into the CASELOADCOUNT and CASELOADCOUNTENROLLMENT tables.
Call the InsertIntoCaseLoadCountRebateItems stored procedure to insert rebate item data records into the CASELOADCOUNTREBATEITEMS table.
STEP 3 Redemption History Table
If the value of the MONTHEND_INCL_HIGHRISK_DATA business rule equals "N", proceed to Step 4. Otherwise, continue with the current step.
Call the InsertRedemptionHistory stored procedure to insert food instrument redemption history records into the REDEMPTIONHISTORY table.
STEP 4 Caseload Unduplicated Tables
If the value of the MONTHEND_INCL_CASELOADUNDUP_TBLS business rule equals "N", proceed to Step 5. Otherwise, continue with the current step.
Call the GetFiscalYear stored procedure to retrieve the current fiscal year.
Call the InsertCaseLoadCountUndupe stored procedure to insert unduplicated participant records into the CASELOADCOUNTUNDUPSTATE, CASELOADCOUNTUNDUPFEDERAL, and CASELOADCOUNTUNDUPCALENDAR tables.
If the value of thethe MONTHEND_INCL_DUALENROLLMENT_TBLS business rule equals "N", proceed to Step 6. Otherwise, continue with the current step.
Call the InsertIntoDuplicateEnrollParticipation stored procedure to insert unduplicated participant records into the DUPLICATEENROLLPARTICIPATION table.
STEP 6 Redemption Reconciliation Table
If the value of the MONTHEND_INCL_REDEMPRECON_TBL business rule equals "N", proceed to Step 7. Otherwise, continue with the current step.
Call the InsertIntoRedemptionReconciliation stored procedure to insert redemption reconciliation records into the REDEMPTIONRECONCILIATION table.
STEP 7 Pediatric and Pregnancy CDC File Tables
If the value of either the MONTHEND_INCL_CASELOADCOUNTS or MONTHEND_INCL_CDC_FILES equal "N", proceed to Step 8. Otherwise, continue with the current step.
a) If the Pregnancy CDC files have been generated for the current quarter, the RunMonthEndProcess calls the CreateCdcPregnancyFileData stored procedure to insert pregnancy records into the CDCPregnancyFile temporary table. Otherwise, proceed to Step 7b.
|
|
NOTE: Pregnancy CDC files are generated in February, May, August and November. Therefore, this step will process at the end of each quarter: March, June, September, and December. |
b) Call the CreateCdcPediatricFileData stored procedure to insert pediatric records into the CDCPediatricFile temporary table.
Assess step success or failure. If a preceding step fails, the proceed to Step 8a. If all preceding steps succeed, proceed to Step 8b.
a) Update the following when a preceding step fails:
i. Write error messages to the EXCEPTIONLOG and SCHEDULEDJOBLOG tables.
ii. Update the SCHEDULEDJOBSTATUS table by adding a row as follows:
SCHEDULEDJOBSTATUS. PROCESSID = 'MONTHEND'
SCHEDULEDJOBSTATUS. STARTDTTM = Code passes the date and time the process started.
SCHEDULEDJOBSTATUS. ENDDTTM = Code passes the date and time the process failed.
SCHEDULEDJOBSTATUS. STATUSCD = '1' to prompt the next instance of End of Month processing to run in Restart Last Month mode.
iii. Clear the currently running processes list by deleting the row where the value of the PROCESSID column equals 'MONTHEND' within the CURRENTLYEXECUTING table.
iv. Do not clear the RunMonthEndProcess scheduled flag within the SCHEDULEDJOBCONTROL table.
v. End the process.
b) Update the following when all preceding steps succeed:
i. Update the SCHEDULEDJOBSTATUS table by adding a row as follows:
SCHEDULEDJOBSTATUS. PROCESSID = 'MONTHEND'
SCHEDULEDJOBSTATUS. STARTDTTM = Code passes the date and time the process started.
SCHEDULEDJOBSTATUS. ENDDTTM = Code passes the date and time the process completed.
SCHEDULEDJOBSTATUS. STATUSCD = '0' to prompt the next instance of End of Month processing to run in New Month mode.
ii. Clear the currently running processes list by deleting the row where the value of the PROCESSID column equals 'MONTHEND' within the CURRENTLYEXECUTING table.
iii. Clear the RunMonthEndProcess scheduled flag by setting the value of the SCHEDULED column to 'N' in the row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBCONTROL table.
iv. Continue with the next step.
STEP 9 Write the CDC Files to Output Folders
If data exists within the CDCPediatricFile temporary table, call the GetCDCPediatricFile stored procedure to write the CDC Pediatric File to the output folder path value in the ROWTEXT column in the second row within the CDCPediatricFile temporary table. Otherwise, skip the CDC Pediatric File processing.
If data exists within the CDCPregnancyFile temporary table, call the GetCDCPregnancyFile stored procedure to write the CDC Pregnancy File to the output folder path value in the ROWTEXT column in the second row within the CDCPregnancyFile temporary table. Otherwise, skip the CDC Pregnancy File processing.
STEP 10 Finalize the CDC Files Process
Assess step success or failure. If the CDC files write successfully, proceed to Step 10a. If a CDC file fails, proceed to Step 10b.
a) Update the following when all CDC files succeed:
i. Call the DropCDCFileTables stored procedure to delete the CDCPediatricFile and CDCPregnancyFile temporary tables.
ii. Set the STATUSCD column to '0' within the most recent row within SCHEDULEDJOBSTATUS table where the value of the PROCESSID column equals 'MONTHEND' to prompt the next instance of End of Month processing to run in New Month mode.
iii. Clear the RunMonthEndProcess scheduled flag by setting the value of the SCHEDULED column to 'N' in the row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBCONTROL table.
iv. End the process.
b) Call the MarkEOMAsFailed procedure when a CDC file fails.
i. Call the InsertScheduledJobLog stored procedure to log information to the scheduled job log. Writes an error message to the EXCEPTIONLOG and SCHEDULEDJOBLOG tables.
ii. Set the STATUSCD column to '1' within the most recent row within SCHEDULEDJOBSTATUS table where the value of the PROCESSID column equals 'MONTHEND' to prompt the next instance of End of Month processing to run in Restart Last Month mode.
iii. Set the RunMonthEndProcess scheduled flag by setting the value of the SCHEDULED column to 'Y' in the row where the value of the PROCESSID column equals 'MONTHEND' within the SCHEDULEDJOBCONTROL table.
iv. End the process.
Updated in Rel 2.40 per SWEB US 152726
