KFS GL Black Box Documentation

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.

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.

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.

Second, to derive the KFS Object Code for Balance Sheet/Expense Accounts.

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.

Fourth, derive the values for KFS Org Document Number and KFS Org Reference ID.