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.

Accounts Payable department wants employees to receive ACH payments for advances and reimbursements and have the Payroll department maintain the ACH records so that we can save time, effort, and money. 

Accounts Payable is looking to eliminate paper checks to put them in a position to outsource the check-writing function.  Also, this process will reduce the fees being paid to the bank for Special Handling due to invalid addresses.

Assumptions and Dependencies:

Payroll generates a .csv file, using PSQuery, with employee information, excluding undergraduate student employees.

1)       Employees already using direct deposit will need to be informed that their existing payroll direct deposit information will be used by AP for reimbursements.  Any new employees will use a revised direct deposit form allowing AP to use the bank account information.

2)       The bank account has already been pre-noted by Core-CT prenote process.

3)       Email addresses within LDAP are accurate.

4)       Bank Routing Number already exists in Kuali Accounts Payable bank table.

5)       Only employee information will be added or updated.  Undergraduate student employees will be excluded from the payroll feed.  Non-employee records, like vendor records, will need to be manually maintained by AP.

6)       Information will be added or updated, only.  Deleting of information will not be done.

7)       File is encrypted/decrypted when stored on the server using SFTP PGP/GPG key exchange.

8)       Accounts Payable to provide initial exception list of Payee IDs to be bypassed.  Finance Systems will create the new table, using the data from the provided exception list.

9)       Accounts Payable will be responsible for updating the bypass indicator on the ACH table for the exceptions.  The indicator will be updated via the Payee ACH Account maintenance screen.

10)   Accounts Payable will manually update and maintain the ACH Table for the exception payee IDs, using Kuali Financial Systems, Payee ACH Account maintenance screen.

Risks:

1)       Accounts Payable ACH employee information will remain active in the table, even after an employee has left the university.

2)       Employees could potentially have their ACH record(s) updated, if any service requests requesting updates to the exception table are not processed in a timely manner.

Process Flow:

Technical Details: 

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:

1)       Bank Routing Number does not exist on the KFS ACH Bank Table (ROUTING).

2)       Employee does not exist on LDAP (NO LDAP).

3)       Duplicate Employee (DUPLICATE)

4)       Bypass Employee who is on Exception file (VIP)

Payroll Direct Deposit Data Extract:

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

Name of payroll extract file will be ACH<ccyymmdd>.csv

Selection criteria:

1)       Account Type is Checking or Savings

2)       PreNote status is confirmed

3)       Deposit Type is Percent

4)       Amount Percent >=40%

5)       Employee status is active

6)       Position number <> “00065845”  

Sort Order:

                Emplid, EffDate (Descending), AcctType, AcctNum, AmountPct (descending)

Header Row:

Detail Rows:

Account Type “C” means Checking, “S” means Savings.

Exception Table – UCONN_PDP_PAYEE_ACH_ACCT_EXT_T:

Datastage Processing:

Bypass any Payroll Employee ID whose BYPASS_FEED_UPDATE indicator on the UCONN_PDP_PAYEE_ACH_ACCT_EXT_T is set to ‘Y’.  Write a rejection record with the reason code VIP for the records being bypassed due to this condition.

Update the PDP_PAYEE_ACH_ACCT_T table with information from the Payroll extract file.  Payee id does not have leading zeroes when inserting or updating.  If the record does not exist on the table, create a new row using the data mapping below.  On inserts, a new row needs to be added to the UCONN_PDP_PAYEE_ACH_ACCT_EXT_T table as well.

If the row already exists, only update that information that has changed.

Selection Criteria for duplicate records:

1)       Get max effective date per emplid.

2)       If one record has an Account Type of Checking and one record has Account Type of Savings, choose “Checking”.

3)       If both records are checking accounts or both records are savings accounts, choose the higher percentage (AmountPct).

4)       If the AmountPct is 50/50 split, choose the lowest account number.

Reject the record not chosen with a reject reason of DUPLICATE.

When accessing LDAP, emplid must have four leading zeroes added to the emplid.  LDAP uses a numeric (10) format.

Validation Rules:

1)       Employee ID is found on LDAP.  Add four leading zeroes to the payroll emplid, before attempting to find on LDAP.  If not found on LDAP, reject with “No LDAP”.

2)       Payroll Bank Number must exist as a routing number on the PDP_ACH_BNK_T and is active (indicator = ‘Y’).  Payroll Bank Number is the same as the KFS Bank Routing Number.  This field is the key to the table.  If the number is not on the table, reject with “Routing”.

3)       If more than one record is received for an employee, reject the unselected record with “DUPLICATE”.

4)       Encrypt the Bank Account Number, using the KFS Encrypt_String function.

5)       When comparing data on the PDP_PAYEE_ACH_ACCT_T, select only active (ROW_ACTV_IND =’Y’) employee (PAYEE_ID_TYP_CD = ‘E’)  records.  Bank account number would need to be decrypted using Kuali DECRYPT_STRING function.

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

UCONN_PDP_PAYEE_ACH_ACCT_EXT_T:

Schema – prod: kfs_prd

PDP_ ACH_BNK_T:

Schema – prod: kfs_prd

Userid - prod: ACH_UAT_USR_RO

Reject File:

(Copy of input file plus the Reject Reason Code)

Related Documents:

Functional Spec by Amy Tse.docx

ACH_Requirements_20150408.docx

ETL ACH Documentation.docx

Direct Deposit Process.docx

Direct Deposit Process.vsd

Direct Deposit Automation technical specification.doc

Direct DepositTest Cases.doc

ACH Job Definitions

UAT-ETL-ACHDIMsExtractETLJob#1.txt

UAT-ETL-ACHDIMsExtractETLJob#2.txt

Meeting 1 minutes-20141205-UPDATED.docx

Meeting 2 minutes-20141205-UPDATED.docx