Exhibit 10 DSDS-BATCH_FIN_EFT_SVC_PNOTE_REJECT.pdf

23DPP00838 1 | P a g e

Exhibit 10

BATCH_FIN_EFT_SVC_PNOTE_REJECT

INTRODUCTION

The process BATCH_FIN_EFT_SVC_PNOTE_REJECTS loads the reject file sent by the bank into the temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU and processes the rejects.

Batch reads the rejects from the temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU and updates the Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table as rejected.

In addition to that, the status of the EFT / SVC is updated to cancelled and the disbursement will be moved to Disbursement Hold Screen / LOG_DISBURSEMENT_HOLD (DHLD) table.

1.1.1 BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_LOAD_EFRJ

1.1.1.1. Purpose

This procedure loads the reject file received from the bank into a temporary oracle table LOAD_EFT_REJECT.

1.1.1.2. Database Tables Affected:

1.1.1.2.1. Driver Tables

N/A

1.1.1.2.2. Output Tables

LOAD_EFT_REJECT

1.1.1.3. Process Description

This procedure loads the reject file from the bank into a temporary oracle table LOAD_EFT_REJECT.

Get the run date and last run date from PARM (Parameters table) and validate 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 the LOAD_EFT_REJECT table.

Read the file using the UTL_FILE package given by oracle and validate the number of records/amount in the trailer against the total number of records/amount total 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 the process will be terminated.

Read the file and load the data to the LOAD_EFT_REJECT table using the UTL_FILE package given by oracle.

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

1.1.1.5. Frequency

Daily

23DPP00838

1.1.1.5.1. Successors:

The program BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_PROCESS_EFRJ should be executed after the successful run of this program.

1.1.1.6. Restart Procedures

Start from the beginning

1.1.1.7. Instructions if Job is skipped

Job cannot be skipped.

1.1.1.8. Commit Frequency

Onetime at end of the job

1.1.1.9. Exception Threshold

1.1.1.10. Input File(s)

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

1 EFTERROR - - - -

1.1.1.10.1. File Layout

Header and Trailer Records.

Field Name

Position

Length

Type C-Char D-Date

N-Number

Format / Default Value

Description

TYPE

1-1

C

Header Value 1 Sub header value 5 Sub trailer value 8 Trailer value 9

FILLER 2-94 93 C

Detail Records – Record type 6 and 7

Record Type 6

C-Char

TYPE 1-1 1 C ‘6’

FILLER 2-3 2 C

DFI_IDENT 4-11 8 C Routing Number

CHECK_DIGIT 12-12 1 C

DFI_ACCT_NUM 13-29 17 C Account Number

AMOUNT 30-39 10 N

INDV_ID 40-54 15 C

FIPS 55-59 5 C

CONTROL_NUMBER 60-70 11 C

FILLER 71-94 24 C

Record Type 7

C-Char

TYPE 1-1 1 C ‘7’

RETURN_CODE

(REJECT_REASON)

4-6

Return codes are listed with descriptions below.

CORRECT_DATA

36-64

For return codes starting with C except C04, the bank gives corrected routing number, account number and account type.

C01_CORRECT_DATA

C01_ACCOUNT 36-52 17

C02_CORRECT_DATA

C02_ROUTING 36-44 9

C03_CORRECT_DATA

C03_ROUTING 36-44 9

C03_ACCOUNT 48-64 17

C05_CORRECT_DATA

C05_TRANS 36-37 2

C06_CORRECT_DATA

C06_ACCOUNT 36-52 17

C06_TRANS 56-57 2

C07_CORRECT_DATA

C07_ROUTING 36-44 9

C07_ACCOUNT 45-61 17

C07_TRANS 62-63 2

The following are the possible values for RETURN_CODE

If RETURN_CODE starts with ‘R’, then it is rejected.

If RETURN_CODE starts with ‘C’, then it is modified.

Return Code Description

R01 Insufficient funds

R02 Account closed

R03 No account/unable to locate account

R04 Invalid account number

R05 Required pre-notification not received

R06 Returned per ODFI’s request

R07 Authorization revoked by customer

R08 Payment stopped

R09 Uncollected funds

R10 Customer advises not authorized

R12 Branch sold to another DFI, R13 RDFI not qualified to participate

R14 Account holder deceased

R15 Beneficiary deceased

R16 Account frozen

R17 File record edit criteria

R20 Non-transaction account

R23 Payment refused by biller

R29 Corp. customer advises not authorized

C01 Incorrect DFI, account number

C02 Incorrect transit/routing number

C03 Incorrect transit/routing number and DFI, acct num

C04 Incorrect individual name/receiving co name

C05 Incorrect transaction code

C06 Incorrect DFI, acct number and trans code

C07 Incorrect routing number, DFI, acct number, trans code

1.1.1.11. Output File(s)

1.1.1.11.1. File Layout

1.1.1.12. Contact information

Name of the Agency Technical Contact Functional Contact

1.1.1.13. 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_EFRJ Loading Loading the file to the temporary table - LOAD_EFT_REJECT - Y - Y

2 BATCH_COMMON.SP_GET_RUN_DATE Common Routine Getting Run date - - - - - -

3 BATCH_COMMON.SP_GET_LAST_RUN_DATE Common Routine Getting Last Run date - - - - - -

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

5 SP_BATCH_ERROR_LOG Common Routine Inserting the batch status - - - - - -

1.1.2 BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_PROCESS_EFRJ

1.1.1.15. Purpose

This procedure reads the pre-note rejects; EFT/Stored value card disbursement rejects from the temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU and updates the Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table as rejected.

In addition to that, the status of the EFT / SVC is updated to Cancelled depending on the Return code and the disbursement will be moved to Dis bursement Hold Screen / LOG_DISBURSEMENT_HOLD (DHLD) table.

23DPP00838 8 | P a g e

1.1.1.16. Database Tables Affected:

1.1.1.16.1. Driver Tables

LOAD_EFT_REJECT

LOAD_EFT_REJECT_SDU

LOG_DISBURSEMENT_DETAILS (DSBL)

1.1.1.16.2. Output Tables

LOG_DISBURSEMENT_HEADER (DSBH)

LOG_DISBURSEMENT_HOLD (DHLD)

ELECTRONIC_FUND_TRANSFER (EFTR)

DEBITCARD_RECIPIENTS (DCRS)

GLOBAL_EVENT (GLEV)

EVENT_SEQ_MATRIX (ESEM)

1.1.1.17. Process Description

The procedure reads the Pre-note rejects from the temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU and updates the Manage EFT Instructions (EFTI) screen/ELETRONIC_FUND_TRANSFER (EFTR) table as rejected.

This procedure also reads the EFT / SVC rejects from the temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU and updates the Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table as rejected.

In addition to that, the status of the EFT / SVC is updated to ‘Cancelled’ depending on the return code received in the file and the disbursement will be moved to Disbursement Hold Screen / LOG_DISBURSEMENT_HOLD (DHLD) table.

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 the process will be terminated.

The Direct Deposit Cancellation Notice must be automatically sent to the custodial parent when the batch BATCH_FIN_EFT_SVC_PNOTE_REJECT updates the EFT status to PR- PRE-NOTE REJECT or CA –CANCELLED upon receiving the reject file by the bank.

CP EFT Pre-note

Both FIPS and CONTROL_NUMBER fields in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables should be blank for the EFT pre-note rejections. Get the CP’s EFT pre-note using the following conditions in Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table

NO_ROUTING_BANK = DFI,_IDENT || CHECK_DIGIT from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables NO_ACCOUNT_BANK = DFI_ACCT_NUMBER from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables

CD_STATUS_EFT = ‘PG’ (Pre-note Generated)

DT_END_VALIDITY = '31-DEC-9999'

Generate a sequence number and insert into GLOBAL_EVENT (GLEV)

Update the pre-note status of the Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table from ‘PG’ (Pre-note generated) to ‘PR’ (Pre-Note Rejected) and reason code with the RETURN_CODE from the LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables. This update is done for RETURN_CODE starting with ‘R’ or RETURN_CODE of ‘C04’ in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

Update the pre-note status CD_STATUS_EFT for all return codes starting with C except C04 to ‘AC’ - ACTIVATED and the CP’s / Agency’s EFT account information in the Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table for the following return codes in the field RETURN_CODE in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

23DPP00838 9 | P a g e

Return Code

Fields in Manage EFT Instructions (EFTI) screen /

Fields in

LOAD_EFT_REJECT/

LOAD_EFT_REJECT_SDU table

C01 Incorrect DFI, account number NO_ACCOUNT_BANK DFI_ACCOUNT_NO

C02 Incorrect transit/routing number NO_ROUTING_BANK ROUTING_NO

C03 Incorrect transit/routing number and DFI, acct num NO_ACCOUNT_BANK, NO_ROUTING_BANK

DFI_ACCOUNT_NO,

ROUTING_NO

C05 Incorrect transaction code ACCOUNT_TYPE ‘C’ if TRANS_CODE = ‘23’

‘S’ if TRANS_CODE = ‘33’

C06

Incorrect DFI, acct number and trans code NO_ACCOUNT_BANK, ACCOUNT_TYPE

DFI_ACCOUNT_NO,

‘C’ if TRANS_CODE = ‘23’

C07

Incorrect routing number, DFI, acct number, trans code NO_ACCOUNT_BANK, NO_ROUTING_BANK, ACCOUNT_TYPE

DFI_ACCOUNT_NO,

ROUTING_NO,

‘C’-checking account if TRANS_CODE = ‘23’ ‘S’- Savings account if TRANS_CODE = ‘33’

Insert a record in Electronic Fund Transfer Report (REFT) screen / DSBH_EFT_INFO_ERROR (DERR) table with ‘P’ (Pre-note) in CD_TYPE_EFT field and RETURN_CODE from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables in CD_REASON_ACTION field.

CP / Agency / County’s EFT disbursement Rejects

Get the EFT account information for the CP / Agency / County using the following conditions in Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table

NO_ROUTING_BANK = DFI_IDENT || CHECK_ DIGIT, from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables NO_ACCOUNT_BANK = DFI_ACCT_NUMBER from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables

CD_STATUS_EFT = ‘AC’

Update the status of the Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table to ‘CA’ (Cancelled) and reason code with the RETURN_CODE from the LOAD_EFT_REJECT table if the RETURN_CODE starts with ‘R’ or it is ‘C04’ in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

Update CP’s / Agency’s / County’s EFT account information in the Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table based on the RETURN_CODE in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

Fields in Manage EFT Instructions (EFTI) screen /

Fields in

LOAD_EFT_REJECT/

LOAD_EFT_REJECT_SDU table

C01 Incorrect DFI, account number NO_ACCOUNT_BANK DFI_ACCOUNT_NO

C02 Incorrect transit/routing number NO_ROUTING_BANK ROUTING_NO

C03 Incorrect transit/routing number and DFI, acct num NO_ACCOUNT_BANK, NO_ROUTING_BANK

C05 Incorrect transaction code ACCOUNT_TYPE ‘C’ if TRANS_CODE = ‘23’

C06

Incorrect DFI, acct number and trans code NO_ACCOUNT_BANK, ACCOUNT_TYPE

DFI_ACCOUNT_NO,

C07

Incorrect routing number, DFI, acct number, trans code NO_ACCOUNT_BANK, NO_ROUTING_BANK, ACCOUNT_TYPE

DFI_ACCOUNT_NO,

ROUTING_NO,

23DPP00838 10 | P a g e

Identify the CP’s / Agency’s / County’s EFT disbursement rejections by matching the CONTROL_NUMBER field in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables with ID_MISC field in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table.

Update the status of the disbursement in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table from ‘TR – Transfer EFT’ to ‘RE – Reject EFT’ only for the RETURN_CODE starts with ‘R’ or it is ‘C04’ in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables. Find the disbursement using the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table

CD_CHECK_RECIPIENT = ‘1’ – for CP

‘2’ – for Agency ‘3’ – for County

CD_MEDIUM_DISBURSE = 'E' – EFT disbursement CD_STATUS_CHECK = 'TR' – Transfer EFT

ID_MISC = CONTROL_NO from LOAD_EFT_REJECT table

Insert the functional event that the EFT disbursement is rejected, which can be viewed on EVENT_LOG_MASTER (ELOG) screen.

All the disbursement details corresponding to the EFT from the Disbursement View (DSBV) screen/ LOG_DISBURSEMENT_DETAIL (DSBL) table will be moved to Disbursement Hold Screen / LOG_DISBURSEMENT_HOLD (DHLD) table and a record will be inserted into DHLD table along with additional data elements as explained below:

CD_ACTION = ’R-Release’ CD_TYPE_HOLD = ‘C-Check Hold’ CD_REASON = ‘DR’ - Regular Hold waiting for DISB batch

Keeping the CD_ACTION=’R’ (Release) will ensure that these records will be processed in the next Batch disbursement process.

Insert a record in Electronic Fund Transfer Report (REFT) screen / DSBH_EFT_INFO_ERROR (DERR) table with ‘E’ (Error) in CD_TYPE_EFT field and RETURN_CODE from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables in CD_REASON_ACTION field.

CP’s SVC – Stored Value Card Rejects

Get the Stored Value Card Instructions for the CP using the following conditions in Stored Value Card Instructions (SVCI) screen / DEBITCARD_RECIPIENTS (DCRS) table

NO_ACCOUNT = DFI,_IDENT || CHECK_ DIGIT, from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables NO_DEBITCARD = DFI,_ACCT_NUMBER from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables

CD_STATUS = ‘A’

Update the status in the Stored Value Card Instructions (SVCI) screen / DEBITCARD_RECIPIENTS (DCRS) table to ‘I’ (Inactive) and reason code with the RETURN_CODE from the LOAD_EFT_REJECT table if the RETURN_CODE starts with ‘R’ or it is ‘C04’ in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

Update CP’s SVC – Stored Value card account information in the Stored Value Card Instructions (SVCI) screen / DEBITCARD_RECIPIENTS (DCRS) table based on the following RETURN_CODE in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables.

23DPP00838 11 | P a g e

Fields in Stored Value Card Instructions (SVCI) screen /

DEBITCARD_RECIPIENTS (DCRS) table

Fields in

LOAD_EFT_REJECT/

LOAD_EFT_REJECT_SDU table

C01

Incorrect DFI, account number

NO_DEBITCARD

DFI_ACCOUNT_NO

C02 incorrect transit/routing number NO_ACCOUNT ROUTING_NO

C03 Incorrect transit / routing number and DFI, acct number NO_DEBITCARD, NO_ACCOUNT

Identify the CP’s / Agency’s / County’s EFT disbursement rejections by matching the CONTROL_NUMBER field in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables with ID_MISC field in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table.

Update the status of the disbursement in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table from ‘TR – Transfer EFT’ to ‘RE – Reject EFT’ only for the RETURN_CODE starts with ‘R’ or it is ‘C04’ in LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables. Find the disbursement using the following conditions in Disbursement View (DSBV) screen / LOG_DISBURSMENT_HEADER (DSBH) table

CD_CHECK_RECIPIENT = ‘1’ – for CP CD_MEDIUM_DISBURSE = 'B’ – SVC disbursement CD_STATUS_CHECK = 'TR' – Transfer EFT

ID_MISC = CONTROL_NO from LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU tables

Insert the functional event that the SVC disbursement is rejected, which can be viewed on EVENT_LOG_MASTER (ELOG) screen.

All the disbursement details corresponding to the SVC – Stored Value Card from the Disbursement View (DSBV) screen/ LOG_DISBURSEMENT_DETAIL (DSBL) table will be moved to Disbursement Hold Screen / LOG_DISBURSEMENT_HOLD (DHLD) table with the following information.

CD_ACTION = ’R-Release’ CD_TYPE_HOLD = ‘C-Check Hold’ CD_REASON = ‘DR’ - Regular Hold waiting for DISB batch

Keeping the CD_ACTION=’R’ (Release) will ensure that these records will be processed in the next Batch disbursement process.

Recoupment EFT disbursement Rejects from State/SDU

Get the EFT account information for the Agencies(OFFICE100,OFFIC900) using the following conditions in Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table

NO_ROUTING_BANK = DFI_IDENT || CHECK_ DIGIT, from LOAD_EFT_REJECT_SDU table NO_ACCOUNT_BANK = DFI_ACCT_NUMBER from LOAD_EFT_REJECT_SDU table

CD_STATUS_EFT = ‘AC’

Update the EFT status in Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table for the Agencies to ‘CA’ (Cancelled) if we receive reject reason code from the LOAD_EFT_REJECT_SDU table which starts with ‘R’

Update the EFT corrections in Manage EFT Instructions (EFTI) screen / ELECTRONIC_FUND_TRANSFER (EFTR) table for the Agencies if we receive reject reason code from the LOAD_EFT_REJECT_SDU table which starts with ‘C’ based on the values received in the other fields

Identify the Agency’s EFT disbursement rejections by matching the CONTROL_NUMBER field in LOAD_EFT_REJECT_SDU table with ID_MISC field in CREC_DISBURSEMENTS (CRDB) table.

Insert the functional event that the EFT disbursement is rejected, which can be viewed on EVENT_LOG_MASTER (ELOG) screen.\

23DPP00838 12 | P a g e

Insert a record in Electronic Fund Transfer Report (REFT) screen / DSBH_EFT_INFO_ERROR (DERR) table with ‘E’ (Error) in CD_TYPE_EFT field and RETURN_CODE LOAD_EFT_REJECT_SDU table in CD_REASON_ACTION field.

The status of the records in the temporary load tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU are updated to ‘Y’ (Yes) to indicate that the records are processed.

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

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

1.1.1.18. Flow Chart

1.1.1.19. Frequency

1.1.1.20. Dependencies

1.1.1.20.1. Predecessors:

The program BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_LOAD_EFRJ and BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_LOAD_EFRJ_SDU should be executed successfully before running this program.

1.1.1.20.2. Successors:

The program BATCH_FIN_DISBURSEMENT.SP_DISB_RECOUP should be executed after the successful run of this program.

1.1.1.21. Restart Procedures

Restart the job from the last commit point

1.1.1.22. Instructions if Job is skipped

1.1.1.23. Commit Frequency

1.1.1.24. Exception Threshold

1.1.1.25. Input File(s)

1.1.1.25.1. File Layout

1.1.1.26. Output File(s)

23DPP00838 12 | P a g e

23DPP00838 13 | P a g e

1.1.1.26.1. File Layout

1.1.1.27. Contact information

1.1.1.28. Stored Procedure / Functions matrix

No Stored Procedure Name/ Arguments Nature of the procedure /

Function Description Output Tables / Screens Applicability

I A U D

1 BATCH_FIN_EFT_SVC_PNOTE_REJECT. Processing Reading the reject records from the - LOAD_EFT_REJECT Y - Y - SP_PROCESS_EFRJ temporary tables LOAD_EFT_REJECT and LOAD_EFT_REJECT_SDU LOAD_EFT_REJECT_SDU and updates the Disbursement View Y - Y - (DSBV) screen / VDSBH

LOG_DISBURSMENT_HEADER

(DSBH) table. VDSBL

Y

VEFTR

VDCRS

Y Y Y -

VDERR

VDHLD

VGLEV

- Y - -

VCRDB

VBATE

Y - - -

VCMEM

VRSTL

VCASE

Y - - -

2 SP_PROCESS_CHECK_HOLD Common Routine This procedure is used to recover any recoupment that were created from reversed receipt associated with the disbursement.

3 SPKG_MATRIX.SP_ENTITY_MATRIX Procedure Procedure to process the Parameters and create a VESEM Entry for the Case with the Key Elements in Sequence that is displayed on the ELOG Screen

4 SPKG_COMMON_GETS.SF_GET_REFMDESC Procedure Procedure to fetch the description for a REFM code

5 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine Returns Run date, Last Run date - - - - - -

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

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

8 SP_GENERATE_SEQ Common Routine Common Procedure to generate unique global sequence

9 SP_GENERATE_SEQ_TXN_EVENT Common Routine Common Procedure to generate unique transaction sequence

23DPP00838 14 | P a g e

10 BATCH_COMMON.SP_INSERT_ACTIVITY Common Routine This procedure is used to insert the activity in VDMJR and VDMNR tables for the case and member combination

11 BATCH_COMMON.

SP_BATCH_RESTART_UPDATE

Common Routine This procedure is used to insert / update restart key value in VRSTL table using input job id and Run date

12 BATCH_COMMON.SP_BSTL_LOG Common Routine This procedure is used to insert the batch status into VBSTL table.

13 BATCH_COMMON.SP_RAISE_APPLICATION_ERR

OR

Common Routine

Raises Application error to abort the job - - - - - -

1.1.3 BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_LOAD_EFRJ_SDU

1.1.1.29. Purpose

This procedure loads the reject file received from the bank into a temporary oracle table LOAD_EFT_REJECT_SDU.

1.1.1.30. Database Tables Affected:

1.1.1.30.1. Driver Tables

1.1.1.30.2. Output Tables

LOAD_EFT_REJECT_SDU

1.1.1.31. Process Description

This procedure loads the reject file from the bank into a temporary oracle table LOAD_EFT_REJECT_SDU.

Get the run date and last run date from PARM (Parameters table) and validate 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 the LOAD_EFT_REJECT_SDU table.

Read the file using the UTL_FILE package given by oracle and validate the number of records/amount in the trailer against the total number of records/amount total 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 the process will be terminated.

Read the file and load the data to the LOAD_EFT_REJECT_SDU table using the UTL_FILE package given by oracle.

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

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

1.1.1.32. Flow Chart

1.1.1.33. Frequency

1.1.1.34. Dependencies

1.1.1.34.1. Predecessors:

23DPP00838 15 | P a g e

1.1.1.34.2. Successors:

The program BATCH_FIN_EFT_SVC_PNOTE_REJECT.SP_PROCESS_EFRJ should be executed after the successful run of this program.

1.1.1.35. Restart Procedures

Start from the beginning

23DPP00838 16 | P a g e

1.1.1.36. Instructions if Job is skipped

1.1.1.37. Commit Frequency

1.1.1.38. Exception Threshold

1.1.1.39. Input File(s)

1 LOAD_EFT_SVC_PNOTE_RJCTS_SDU

.TXT

1.1.1.39.1. File Layout

Header and Trailer Records.

C-Char

TYPE

1-1

Header Value 1 Sub header value 5 Sub trailer value 8 Trailer value 9

FILLER 2-94 93 C

Detail Records – Record type 6 and 7

Record Type 6

C-Char

TYPE 1-1 1 C ‘6’

FILLER 2-3 2 C

DFI_IDENT 4-11 8 C Routing Number

CHECK_DIGIT 12-12 1 C

DFI_ACCT_NUM 13-29 17 C Account Number

AMOUNT 30-39 10 N

INDV_ID 40-54 15 C

FIPS 55-59 5 C

CONTROL_NUMBER 60-70 11 C

FILLER 71-94 24 C

Record Type 7

C-Char

TYPE 1-1 1 C ‘7’

RETURN_CODE

(REJECT_REASON)

4-6

Return codes are listed with descriptions below.

23DPP00838 17 | P a g e

CORRECT_DATA

36-64

For return codes starting with C except C04, the bank gives corrected routing number, account number and account type.

C01_CORRECT_DATA

C01_ACCOUNT 36-52 17

C02_CORRECT_DATA

C02_ROUTING 36-44 9

C03_CORRECT_DATA

C03_ROUTING 36-44 9

C03_ACCOUNT 48-64 17

C05_CORRECT_DATA

C05_TRANS 36-37 2

C06_CORRECT_DATA

C06_ACCOUNT 36-52 17

C06_TRANS 56-57 2

C07_CORRECT_DATA

C07_ROUTING 36-44 9

C07_ACCOUNT 45-61 17

C07_TRANS 62-63 2

The following are the possible values for RETURN_CODE

If RETURN_CODE starts with ‘R’, then it is rejected.

If RETURN_CODE starts with ‘C’, then it is modified.

Return Code Description

R01 Insufficient funds

R02 Account closed

R03 No account/unable to locate account

R04 Invalid account number

R05 Required pre-notification not received

R06 Returned per ODFI’s request

R07 Authorization revoked by customer

R08 Payment stopped

R09 Uncollected funds

R10 Customer advises not authorized

R12 Branch sold to another DFI, R13 RDFI not qualified to participate

R14 Account holder deceased

R15 Beneficiary deceased

R16 Account frozen

R17 File record edit criteria

R20 Non-transaction account

R23 Payment refused by biller

R29 Corp. customer advises not authorized

C01 Incorrect DFI, account number

C02 Incorrect transit/routing number

C03 Incorrect transit/routing number and DFI, acct num

C04 Incorrect individual name/receiving co name

C05 Incorrect transaction code

C06 Incorrect DFI, acct number and trans code

C07 Incorrect routing number, DFI, acct number, trans code

1.1.1.40. Output File(s)

23DPP00838 18 | P a g e

1.1.1.40.1. File Layout

1.1.1.41. Contact information

1.1.1.42. 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_EFRJ_SDU Loading Loading the file to the temporary table - LOAD_EFT_REJECT_SDU - Y - Y

2 BATCH_COMMON.SP_GET_RUN_DATE Common Routine Getting Run date - - - - - -

3 BATCH_COMMON.SP_GET_LAST_RUN_DATE Common Routine Getting Last Run date - - - - - -

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

5 SP_BATCH_ERROR_LOG Common Routine Inserting the batch status - - - - - -