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, convert ARC Trans Code to KFS Object Code. 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.

Derive the values for KFS Org Document Number and KFS Org Reference ID.

ARC Value	KFS Value
Filler 2 or Source	If Filler 2 is blank, place Source in KFS Org Document Number. Otherwise, the first 10 chars of Filler 2 gets placed in KFS Org Document Number
Reference	KFS Org Reference ID

Browser not supported