Exhibit 9 DSDS-BATCH_FIN_CLEARED_CHECKS.pdf

23DPP00838 1 | P a g e

Exhibit 9

BATCH_FIN_CLEARED_CHECKS

INTRODUCTION

The process BATCH_FIN_CLEARED_CHECKS loads the Bank Reconciliation file received from Disbursements bank to the temporary table and processes the records to validate the existing check status and update the status of the check disbursements to ‘Cashed’, in the Disbursements Header in NJKiDS. This file also includes the Void and Stop payments from the Bank.

1 BATCH_FIN_CLEARED_CHECKS.SP_LOAD_CCLR_SDU

1.1.1.1. Purpose

The process loads the Bank Reconciliation file from the Disbursement Bank to the temporary table for further processing.

1.1.1.2. Database Tables Affected:

1.1.1.2.1. Driver Tables

N/A

1.1.1.2.2. Output Tables

> LOAD_CHECK_CLEAR_SDU

1.1.1.3. Process Description

This procedure loads the Bank cleared check file from the bank to the temporary table LOAD_CHECK_CLEAR_SDU using the oracle package

UTL_FILE.

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table. Otherwise, an error message to that effect will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Delete the processed records from LOAD_CHECK_CLEAR_SDU table.

Read the file using the UTL_FILE package given by oracle and validate the number of records in the trailer against the total number of records in the detail. If they don’t match, an error message will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Insert the detail data into the temporary table LOAD_CHECK_CLEAR_SDU.

Log the error encountered or successful completion in the Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table for future references.

Update last run date in the PARM table with the run date, upon successful completion.

1.1.1.4. Flow Chart

23DPP00838 2 | P a g e

1.1.1.5. Frequency

Daily

1.1.1.6. Dependencies

1.1.1.6.1. Predecessors:

1.1.1.6.2. Successors:

The program BATCH_FIN_CLEARED_CHECKS. SP_PROCESS_CLEARED_CHECKS should be executed after the successful run of SP_LOAD_CCLR_SDU these program.

1.1.1.7. Restart Procedures

Start from the beginning

1.1.1.8. Instructions if Job is skipped

Job cannot be skipped.

1.1.1.9. Commit Frequency

Onetime at end of the job

1.1.1.10. Exception Threshold

1.1.1.11. Input File(s)

S.No Name of the File File Format Transfer Mechanism File location Estimated size (in MBs)

1 LOAD_CLEARED_CHECKS_SDU.

TXT

- SFTP - -

23DPP00838 3 | P a g e

1.1.1.11.1. File Layout

Field Name

Position

Length

Type C-Char D-Date

N-Number

Format / Default Value

Description

HEADER

TYPE 1-2 2 C ‘HD’ Header type

DATE 3-10 8 D ‘YYYYMMDD’ The date when the file is generated

FILLER 11-32 22 C Spaces

DETAILS

TYPE 1-2 2 C ‘DT’ Detail type

CHECK_NO 3-11 9 C Check number

AMT_CHECK 12-22 11 N Check amount

CD_STATUS_CHECK 23-24 2 C ‘CA’ Status of the Check

DT_PAID_CHECK 25-32 8 D YYYYMMDD The date when the check was Cashed

TRAILER

TYPE 1-2 2 C ‘TR’ Trailer Type

TRAILER_COUNT 3-12 10 N # of records given

FILLER 13-32 20 C Spaces

Data from input file is stored in the VDSBH table and the mapping is shown as below.

Source Target

CHECK_NO VDSBH.NO_CHECK

AMT_CHECK VDSBH.AMT_DISBURSE

CD_STATUS_CHECK VDSBH.CD_STATUS_CHECK

DT_PAID_CHECK VDSBH.DT_STATUS_CHECK

1.1.1.12. Output File(s)

1.1.1.12.1 File Layout

1.1.1.13. Contact information

Name of the Agency Technical Contact Functional Contact

23DPP00838 4 | P a g e

1.1.1.14. Stored Procedure / Functions matrix

No Stored Procedure Name/ Arguments Nature of the procedure

/ Function Description Output Tables / Screens Applicability

I A U D

1 SP_LOAD_CCLR_SDU Loading Load the data from the file to the temporary table

- LOAD_CHECK_CLE

AR_SDU

- Y - Y

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Returns batch Run date, Last Run date, Out file location.

3 BATCH_COMMON.SP_LOAD_DATA Common Routine Loading file - - - - - -

4 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

5 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

6 BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine This procedure is used to display the error occurred due to unhandled exceptions

23DPP00838 5 | P a g e

ATCH_FIN_CLEARED_CHECKS.SP_PROCESS_CLEARED_CHECKS

1.1.2.1. Purpose

The process reads the cashed check information from the temporary table and updates the check status and status date in Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table.

The checks with void or stop payment status in the Bank reconciliation file will be validated against the data in NJKiDS and the mismatches are recorded in the system.

1.1.2.2. Database Tables Affected:

1.1.2.2.1. Driver Tables

> LOAD_CHECK_CLEAR_SDU

1.1.2.2.2. Output Tables

> LOG_DISBURSEMENT_HEADER (DSBH)

> BATCH_ERROR (BATE)

> GLOBAL_EVENT (GLEV)

1.1.2.3. Process Description

This procedure reads the Cashed Check information from the temporary table LOAD_CHECK_CLEAR_SDU and updates the check status on the NJKiDS disbursement (DSBH) table to the Status of ‘CA’ – Cashed. The Void and Stopped payments in the file are matched with data in NJKIDS and mismatch checks are reported as errors. The status date on the disbursement record will be updated with the Date on the CCLR file. The updated data can be viewed on Disbursement View (DSBV) screen.

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table. Otherwise, an error message to that effect will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Read the cleared checks from the temporary table table LOAD_CHECK_CLEAR_SDU and fetch all check disbursement records from Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table for the check numbers from the input file using the following conditions.

CD_MEDIUM_DISBURSE = 'C' -- Check NO_CHECK = CHECK_NO from the temporary table

DT_END_VALIDITY = '31-DEC-9999'

If no record exists in the LOG_DISBURSEMENT_HEADER table for the given check number, insert into Batch Status Log (BSTL) Screen/ BATCH_ERROR – (BATE) table with the message ‘Check number not in the system’.

If the check amount on the Disbursement record does not match the check amount on the input file, insert into Batch Status Log (BSTL) Screen/ BATCH_ERROR (BATE) table with the reason ‘Amount on the check does not match’. Use the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table

AMT_DISBURSE is not equal to AMT_CHECK from the input file table

If the check status is not ‘Outstanding’ on Disbursement header record for the cashed checks in the temporary table, Insert into Batch Status Log (BSTL) Screen/ BATCH_ERROR (BATE) table with the reason ‘Check is not in Outstanding status: Current status = <Current Check status>’. Use the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table

23DPP00838 6 | P a g e

STATUS_CHECK is not equal to 'OU'--Outstanding CD_MEDIUM_DISBURSE = 'C' -- Check CD_STATUS_CHECK = ‘CA’ in the temporary table

If the check status is not ‘Void No-Reissue’ or ‘Void Re-issue’ on Disbursement header record for the Voided checks in the temporary table, insert into Batch Status Log (BSTL) Screen/ BATCH_ERROR (BATE) table with the reason ‘Check is not in Voided status: Current status = <Current Check status>’. Use the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table

STATUS_CHECK Not In (‘VN’ – Void No Reissue, ‘VR’ – Void Reissue’)

NO_CHECK = CHECK_NO from the temporary table CD_STATUS_CHECK = ‘VD’ in the temporary table

If the check status is not Stop No reissue/ Stop Reissue’ on Disbursement header record, Insert into Batch Status Log (BSTL) Screen /BATCH_ERROR (BATE) table with the reason ‘Check is not in Stopped status: Current status = <Current Check status>’. Use the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSEMENT_HEADER (DSBH) table

STATUS_CHECK Not In (‘SN’ – Stop No reissue, ‘SR’ – Stop Reissue)

NO_CHECK = CHECK_NO from the temporary table CD_STATUS_CHECK = ‘ST’ in the temporary table

Generate a sequence number and insert into GLOBAL_EVENT (GLEV)

End validate the existing DISBURSEMENT_HEADER record.

DT_END_VALIDITY = System date SEQ_EVENT_GLOBAL_END = the generated global event

Insert into DISBURSEMENT_HEADER to record the cashed status.

CD_STATUS_CHECK = ‘CA’

SEQ_EVENT_GLOBAL_BEG = the generated global event

DT_STATUS_CHECK = DT-PAID_CHECK

SEQ_EVENT_GLOBAL_END = 0

DT_END_VALIDITY = ’31-DEC-9999’

And all other fields same as in the updated record

Log the error encountered or successful completion in the BATCH STATUS LOG Screen / BATCH_STATUS_LOG (BSTL) table

1.1.2.4. Flow Chart

23DPP00838 7 | P a g e

1.1.2.5. Frequency

Daily

1.1.2.6. Dependencies

1.1.2.6.1. Predecessors:

The program BATCH_FIN_CLEARED_CHECKS.SP_LOAD_CCLR_SDU should be executed successfully before running this program.

1.1.2.6.2. Successors:

The program BATCH_FIN_STALE_CHECKS.SP_LOAD_STALE should be executed after successful run of this program.

1.1.2.7. Restart Procedures

Restart the job from the last commit point

1.1.2.8. Instructions if Job is skipped

1.1.2.9. Commit Frequency

1.1.2.10. Exception Threshold

23DPP00838 8 | P a g e

1.1.2.11. Input File(s)

1.1.2.11.1. File Layout

1.1.2.12. Output File(s)

1.1.2.12.1. File Layout

1.1.2.13. Contact information

1.1.2.14. Stored Procedure / Functions matrix

B

ATCH_FIN_CLEARED_CHECKS.SP_LOAD_VOIDS_STOPS_SDU

No Stored Procedure Name/ Arguments Nature of the procedure / Function

Description Output Tables / Screens Applicability

I A U D

1 SP_PROCESS_CLEARED_CHECKS Processing Read the data from the temporary tables and update the check status to ‘CA’ –

- VDSBH Y Y Y -

Cashed and status date in VBATE - Y - -

LOG_DISBURSEMENT_HEADER

(DSBH) Y - Y -

VGLEV

- - Y -

LOAD_CHECK_CLE

AR_SDU

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Returns batch Run date, Last Run date, Out file location.

3 BATCH_COMMON.SP_LOAD_DATA Common Routine Loading file - - - - -

4 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

5 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

6 BATCH_COMMON.SP_BATE_LOG Common Routine This procedure is used to insert the unprocessed record error codes into VBATE table.

7 SP_GENERATE_SEQ Common Routine This Procedure is used to create sequence number to identify a transaction

8 BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine This procedure is used to display the error occurred due to unhandled exceptions

23DPP00838 9 | P a g e

1.1.3.1. Purpose

The process loads the Bank Reconciliation file from the Disbursement Bank to the temporary table for further processing of monthly voids and stops checks.

1.1.3.2. Database Tables Affected:

1.1.3.2.1. Driver Tables

23DPP00838 11 | P a g e

1.1.3.2.2. Output Tables

LOAD_VOIDS_STOPS_SDU

1.1.3.3. Process Description

This procedure loads the Bank Reconciliation file from the bank to the temporary table LOAD_VOIDS_STOPS_SDU using the oracle package

UTL_FILE.

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table. Otherwise, an error message to that effect will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Delete the records from LOAD_VOIDS_STOPS_SDU table.

Read the file using the UTL_FILE package given by oracle and validate the number of records in the trailer against the total number of records in the detail. If they don’t match, an error message will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Insert the detail data into the temporary table LOAD_VOIDS_STOPS_SDU.

Log the error encountered or successful completion in the Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table

1.1.3.4. Flow Chart

1.1.3.5. Frequency

Monthly

1.1.3.6. Dependencies

1.1.3.6.1. Predecessors:

23DPP00838 13 | P a g e

1.1.3.6.2. Successors:

The program BATCH_FIN_CLEARED_CHECKS.SP_PROCESS_VOIDS_STOPS should be executed after the successful run of this program.

1.1.3.7. Restart Procedures

Start from the beginning

1.1.3.8. Instructions if Job is skipped

1.1.3.9. Commit Frequency

1.1.3.10. Exception Threshold

1.1.3.11. Input File(s)

1 LOAD_VOIDS_STOPS_SDU.TXT - SFTP - -

1.1.3.11.1. File Layout

Field Name

Position

Length

Type C-Char D-Date

N-Number

Format / Default Value

Description

HEADER

TYPE 1-2 2 C ‘HD’ Header type

DATE 3-10 8 D ‘YYYYMMDD’ The date when the file is generated

FILLER 11-32 22 C Spaces

DETAILS

TYPE 1-2 2 C ‘DT’ Detail type

CHECK_NO 3-11 9 C Check number

AMT_CHECK 12-22 11 N Check amount

CD_STATUS_CHECK 23-24 2 C Status of the Check. Possible values are ‘VD’ – Voided ‘ST’ - Stopped

DT_PAID_CHECK 25-32 8 D YYYYMMDD The date when the check was Cashed

TRAILER

TYPE 1-2 2 C ‘TR’ Trailer Type

TRAILER_COUNT 3-12 10 N # of records given

FILLER 13-32 20 C Spaces

Data from input file is stored in the VDSBH table and the mapping is shown as below.

Source Target

CHECK_NO VDSBH.NO_CHECK

AMT_CHECK VDSBH.AMT_DISBURSE

CD_STATUS_CHECK VDSBH.CD_STATUS_CHECK

DT_PAID_CHECK VDSBH.DT_STATUS_CHECK

1.1.3.12. Output File(s)

23DPP00838 14 | P a g e

1.1.3.12.1. File Layout

1.1.3.13. Contact information

1.1.3.14. Stored Procedure / Functions matrix

Description Output Tables / Screens Applicability

I A U D

1 SP_LOAD_VOIDS_STOPS_SDU Loading Load the data from the file to the temporary table

- LOAD_VOIDS_STOP

S_SDU

- Y - Y

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Returns batch Run date, Last Run date, Out file location.

3 BATCH_COMMON.SP_LOAD_DATA Common Routine Loading file - - - - - -

4 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

5 BATCH_COMMON.SP_BATE_LOG Common Routine This procedure is used to insert the unprocessed record error codes into VBATE table.

BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine

This procedure is used to display the error occurred due to unhandled exceptions

1.1.4 BATCH_FIN_CLEARED_CHECKS.SP_PROCESS_VOIDS_STOPS

1.1.4.1. Purpose

The process reads the voids and stopped check information from the LOAD_VOIDS_STOPS_SDU temporary. If check is not present in VDSBH it will be logged in VBATE table.

1.1.4.2. Database Tables Affected:

1.1.4.2.1. Driver Tables

1.1.4.2.2. Output Tables

BATCH_ERROR (BATE)

1.1.4.3. Process Description

This procedure reads the Cashed Check information from the temporary table LOAD_VOIDS_STOPS_SDU and checks for the following conditions.

23DPP00838 16 | P a g e

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table. Otherwise, an error message to that effect will be written into Batch Status Log (BSTL) screen / BATCH_STATUS_LOG (BSTL) table and terminate the process.

Checks if the check number is NULL or amount on the check is zero dollars or check code status is NULL. If any of the conditions are satisfied then throw an error ‘KEY DATA IS NOT FOUND’ with error code E0958.

Then verify check code status is VR (void reissue) or SR (stop reissue). If not throw an error ‘NOT VALID’ with error code E0085.

If the check number is not NULL or amount on the check is not zero dollars or check code status is not NULL then load amount disbursed ( amt_disbursed ) and check code status from (cd_status_check) from VDSBH table ,where no_check = <Current no_check> and

If ‘NO_DATA_FOUND’ then raise error ‘CHECK NO NOT IN SYSTEM’ with code E0963. If ‘TOO_MANY_ROWS’ then raise error

‘MORE THAN ONE CHECK FOUND IN THE SYSTEM FOR THE CHECK NO’ with code E0964.

If the check number is not NULL or amount on the check is not zero dollars or check code status is not NULL and check code status check is not (‘VR’,’VN’) and code status check is equal to ‘VD’ then raise error ‘CHECK IS NOT IN VOIDED STATUS’ with code E0967.

If the check number is not NULL or amount on the check is not zero dollars or check code status is not NULL and check code status check is not (‘SR’,’SN’) and code status check is equal to ‘ST’ then raise error ‘CHECK IS NOT IN STOPPED STATUS’ with code E0968.

Update LOAD_VOIDS_STOPS_SDU tables with id_process =’Y’ for current row_id.

If error are found, then LOG_DISBURSEMENT_HEADER table for the given check number, insert into BATCH_ERROR – (BATE) table with the appropriate message.

Log the error encountered or successful completion in the BATCH STATUS LOG Screen / BATCH_STATUS_LOG (BSTL) table

1.1.4.4. Flow Chart

23DPP00838 17 | P a g e

1.1.4.5. Frequency

Monthly

1.1.4.6. Dependencies

1.1.4.6.1. Predecessors:

The program BATCH_FIN_CLEARED_CHECKS.SP_LOAD_VOIDS_STOPS_SDU should be executed successfully before running this program.

1.1.4.6.2. Successors:

1.1.4.7. Restart Procedures

Restart the job from the last commit point

1.1.4.8. Instructions if Job is skipped

1.1.4.9. Commit Frequency

1.1.4.10. Exception Threshold

1.1.4.11. Input File(s)

1.1.4.11.1. File Layout

23DPP00838 18 | P a g e

1.1.4.12. Output File(s)

23DPP00838 19 | P a g e

1.1.4.12.1. File Layout

1.1.4.13. Contact information

1.1.4.14. Stored Procedure / Functions matrix

Description Output Tables / Screens Applicability

I A U D

1 SP_PROCESS_VOIDS_STOPS Processing Read the data from the temporary tables and check of errors

- VDSBH Y - - -

VBATE - Y - -

LOAD_VOIDS_STOP

S_SDU

Y - - -

2 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Returns batch Run date, Last Run date, Out file location.

3 BATCH_COMMON.SP_LOAD_DATA Common Routine Loading file - - - - -

4 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

5 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

6 BATCH_COMMON.SP_BATE_LOG Common Routine This procedure is used to insert the unprocessed record error codes into VBATE table.

7 BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine This procedure is used to display the error occurred due to unhandled exceptions