Introduction
The GL Black Box is a program which helps in translating legacy Financial System upload transactions into an equivalent KFS Collector transaction. It utilizes a series of Crosswalk table for translating the transactions. This documentation was written specifically for the ARC system in order to duplicate the logic from the Talend ETL job.
- Account Crossswalk table (UCI_CA_ACCOUNT_XW_T)
UC_LOC_CD VARCHAR2(2 BYTE)
UC_ACCT_NBR VARCHAR2(6 BYTE)
UC_FUND_NBR VARCHAR2(5 BYTE)
FIN_COA_CD VARCHAR2(2 BYTE)
ACCOUNT_NBR VARCHAR2(7 BYTE)
SUB_ACCT_NBR VARCHAR2(5 BYTE)
ACTIVE_IND CHAR(1 BYTE)
VER_NBR NUMBER(8,0)
OBJ_ID VARCHAR2(36 BYTE)
XW_ID NUMBER
- Object Code Crosswalk table (UCI_CA_OBJECT_CODE_XW_T)
XW_ID NUMBER
UNIV_FISCAL_YR NUMBER(4,0)
FIN_COA_CD VARCHAR2(2 BYTE)
FIN_OBJECT_CD VARCHAR2(4 BYTE)
UC_LOC_CD VARCHAR2(2 BYTE)
UC_SUB_CD VARCHAR2(2 BYTE)
UC_OBJECT_CD VARCHAR2(4 BYTE)
UC_ACCT_NBR VARCHAR2(6 BYTE)
ACTIVE_IND CHAR(1 BYTE)
OBJ_ID VARCHAR2(36 BYTE)
VER_NBR NUMBER(22,0)
ELIMINATED_FLAG VARCHAR2(1 BYTE)
- Balance Sheet Account Crosswalk dynamic table (DYNAMIC_BAL_SHEET_ACCOUNT_XW)
SELECT ACCT.FIN_COA_CD,
ACCT.ACCOUNT_NBR,
ACCT.ACCT_TYP_CD,
ACCTX.UC_LOC_CD,
ACCTX.UC_FUND_NBR
FROM CA_ACCOUNT_T ACCT, CA_ACCOUNT_EXT_T ACCTX
WHERE ACCT.ACCOUNT_NBR = ACCTX.ACCOUNT_NBR
AND ACCT.FIN_COA_CD = ACCTX.FIN_COA_CD
AND ACCT.ACCT_TYP_CD = 'CA'
Balance Sheet Object Code Crosswalk dynamic table (DYNAMIC_BAL_SHEET_OBJECT_XW)
SELECT DISTINCT
UCI_CA_OBJECT_CODE_XW_T.UNIV_FISCAL_YR,
UCI_CA_OBJECT_CODE_XW_T.FIN_COA_CD,
UCI_CA_OBJECT_CODE_XW_T.UC_LOC_CD,
UCI_CA_OBJECT_CODE_XW_T.UC_ACCT_NBR,
UCI_CA_OBJECT_CODE_XW_T.FIN_OBJECT_CD,
UCI_CA_OBJECT_CODE_XW_T.UC_OBJECT_CD
FROM UCI_CA_OBJECT_CODE_XW_T
Main Logic
First translate a legacy FS Account provide the following inputs to the KFS Account Crosswalk table in order to look up the KFS values.
| FS Value | KFS Value | Notes |
|---|---|---|
| Location | UCI_CA_ACCOUNT_XW_T.UC_LOC_CD | |
| Account Number | UCI_CA_ACCOUNT_XW_T.UC_ACCT_NBR | |
| Fund Number | UCI_CA_ACCOUNT_XW_T.UC_FUND_NBR | If the legacy FS Fund Number is blank use '18888' |
| Result | Notes |
|---|---|
| UCI_CA_ACCOUNT_XW_T.FIN_COA_CD | This will be the Chart Code used for the Account |
| UCI_CA_ACCOUNT_XW_T.ACCOUNT_NBR | This will be the KFS Account mapped to the FS Account and FS Fund. In the case that no KFS Account is returned, then use the default account 'BF10002' |
Second translate a legacy FS Sub and FS Object code to the KFS Object code use the KFS Object Code Crosswalk in order to look up the KFS values.
| FS Value | KFS Value | Notes |
|---|---|---|
| Sub | UCI_CA_OBJECT_CODE_XW_T.UC_SUB_CD | |
| Object | UCI_CA_OBJECT_CODE_XW_T.UC_OBJECT_CD | |
| Location | UCI_CA_OBJECT_CODE_XW_T.UC_LOC_CD | |
UCI_CA_OBJECT_CODE_XW_T.FIN_COA_CD | Pass in the KFS Chart from the Account look up in the first step | |
| UCI_CA_OBJECT_CODE_XW_T.UNIV_FISCAL_YR | Derive the fiscal year by using the FS Transaction Date and pass it in. KFS Fiscal Year will be the last year of the fiscal period. For Example for the Fiscal Period 2015-2016 which run from July 2015 to June 2016, you need to use '2016' and the KFS Fiscal Year. For a transaction which has a date of 8/20/2015, the KFS Fiscal Year will be '2016' for this transaction. |
| Result | Notes |
|---|---|
| UCI_CA_OBJECT_CODE_XW_T.FIN_OBJECT_CD | This will be the KFS Object Code. In the case that no KFS Object Code is returned, then use the default KFS Object Code '9999'. |
Balance Sheet/Expense Account Logic
First, if the FS Account is a Balance Sheet or Expense Account (FS Account starts with '1' or '2') then the logic is different to derive the KFS Account and KFS Object Code.
| FS Value | KFS Value | Notes |
|---|---|---|
| Location | DYNAMIC_BAL_SHEET_ACCOUNT_XW.UC_LOC_CD | |
| DYNAMIC_BAL_SHEET_ACCOUNT_XW.ACCOUNT_NBR | If the legacy FS Fund Number is blank use 'UC18888' as the Account Number. Otherwise use 'UC' + FS Fund Number. | |
| Fund Number | DYNAMIC_BAL_SHEET_ACCOUNT_XW.UC_FUND_NBR | If the legacy FS Fund Number is blank use '18888' |
| Result | Notes |
|---|---|
| DYNAMIC_BAL_SHEET_ACCOUNT_XW.FIN_COA_CD | This will be the Chart Code used for the Account |
| DYNAMIC_BAL_SHEET_ACCOUNT_XW.ACCOUNT_NBR | This will be the KFS Account. In the case that no KFS Account is returned, then use the default account 'BF10002' |
Second, to derive the KFS Object Code for Balance Sheet/Expense Accounts.
| FS Value | KFS Value | Notes |
|---|---|---|
| Account Number | DYNAMIC_BAL_SHEET_OBJECT_XW.UC_ACCT_NBR | |
| Location | DYNAMIC_BAL_SHEET_OBJECT_XW.UC_LOC_CD | |
DYNAMIC_BAL_SHEET_OBJECT_XW.FIN_COA_CD | Pass in the KFS Chart from the Account look up in the first step | |
| DYNAMIC_BAL_SHEET_OBJECT_XW.UNIV_FISCAL_YR | Derive the fiscal year by using the FS Transaction Date and pass it in. KFS Fiscal Year will be the last year of the fiscal period. For Example for the Fiscal Period 2015-2016 which run from July 2015 to June 2016, you need to use '2016' and the KFS Fiscal Year. For a transaction which has a date of 8/20/2015, the KFS Fiscal Year will be '2016' for this transaction. |
| Result | Notes |
|---|---|
| DYNAMIC_BAL_SHEET_OBJECT_XW.FIN_OBJECT_CD | This will be the KFS Object Code. In the case that no KFS Object Code is returned, then use the default KFS Object Code '9999'. |
Third, in the FS Upload Upload FIle layout, the column Filler 3 ( location 122, char(4) ) contains the ARC Trans Code. If we are dealing with a Balance Sheet/Expense Accounts and the derived KFS Object Code is between 'N001' and 'N016'. Then take the ARC Trans Code and convert it with the following rule:
Change the first digit to a letter based on this table
1 = A
2 = B
3 = C
4 = D
5 = E
6 = F
7 = G
8 = H
9 = I
0 = X
Example: '1205' becomes 'A205' which is the new KFS Object Code
This logic is described in KFSI-12642.