ACH Table Load

...

Excerpts below are taken from the requirements document. Complete documents can be found at the end of this document.

Purpose:

This process uses a flat file feed from Payroll to update the ACH Payee table in KFS. By scheduling a batch process to load data directly into the database, the Accounts Payable department is looking reduce the number of checks printed and distributed. The department is also looking to replace the dual ACH entry process, where the Payroll department is maintaining records and the Accounts Payable office is maintaining records.

...

DataStage(ETL) procedure will process the payroll file, updating/inserting ACH Payee Account records. It uses userid ACH_UAT_USR_RO.

A reject file will be produced for any errors and emailed to AP_Vend_Coord@uconn.edu. The file will be timestamped when stored on a server. Each row on the file will duplicate the input file plus an additional field that contains the reject code. The Bank account number will be masked except for the last four digits. Errors may include the following:

...

Schema – prod: kfs_prd

Userid - prod: ACH_UAT_USR_RO

Field Name	Data Type	Description
BNK_RTNG_NBR	VARCHAR2(9 BYTE)	Bank Routing Number
ACTV_IND	VARCHAR2(1 BYTE)	Active indicator = ‘Y’

...

Field Name	Pos	Data Type	Description
emplid	1	Char 6
EffDate	2	Date
AccountNum	3	Char 17	Must mask, leaving last four characters showing
AcctType	4	Char 1
RoutNum	5	Char 11
AmountPct	6	Double 5.2
ReasonCode	7	String