ACH Table Load
...
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’ |
...