ACH Table Load

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

...

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:

...

Payroll Direct Deposit Data Extract:

Payroll extract file is .csv format. The first row of the file will be the column headers.

...

6) Bypass any employee with a BYPASS_FEED_UPDATE indicator = ‘Y’ on the UCONN_ PDP_PAYEE_ACH_ACCT_EXT_T.

PDP_PAYEE_ACH_ACCT_T:

Schema – prod: kfs_prd

Field Name	Data Type	Description
ACH_ACCT_GNRTD_ID	NUMBER(10,0)	PDP_ACH_ACCT_GNRID_ID_SEQ
OBJ_ID	VARCHAR2(36 BYTE)	SYS_GUID()
VER_NBR	NUMBER(8,0)	Hardcode – 1
BNK_RTNG_NBR	VARCHAR2(9 BYTE)	From Payroll – RoutNum - Verify it exists on PDP_ACH_BNK_T and is active – Reject if does not exist, using reject code Routing
BNK_ACCT_NBR	VARCHAR2(255 BYTE)	From Payroll – AccountNum – needs to be encrypted using Kuali ENCRYPT_STRING function
PAYEE_NM	VARCHAR2(123 BYTE)	From LDAP – cn (VARCHAR) - First MiddleInitial Last emplid is used as the key.
PAYEE_EMAIL_ADDR	VARCHAR2(200 BYTE)	From LDAP – mail (VARCHAR) - emplid is used as the key.
PAYEE_ID_TYP_CD	VARCHAR2(1 BYTE)	Hardcode – “E” for Employee ID
ACH_TRANS_TYP	VARCHAR2(4 BYTE)	Hardcode – “ACH”
ROW_ACTV_IND	VARCHAR2(1 BYTE)	Hardcode – “Y” for inserts only, otherwise do not update this field
BNK_ACCT_TYP_CD	VARCHAR2(2 BYTE)	Hardcode = If Payroll Account Type = “C”, then “22”, If Payroll Account Type = “S”, then “32”
PAYEE_ID_NBR	VARCHAR2(40 BYTE)	From Payroll – emplid

...

UCONN_PDP_PAYEE_ACH_ACCT_EXT_T:

Schema – prod: kfs_prd

Field Name	Data Type	Description
ACH_ACCT_GNRTD_ID	NUMBER(10,0)	From PDP_PAYEE_ACH_ACCT_T
OBJ_ID	VARCHAR2(36 BYTE)	SYS_GUID()
VER_NBR	NUMBER(8,0)	Hardcode – 1
BYPASS_FEED_UPDATE	VARCHAR2(1 BYTE)	Hardcode – “N”

...

PDP_ ACH_BNK_T:

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