Balances in KFS Decision Support
- vjones (Unlicensed)
Balances are aggregated by Fiscal Year, Fiscal Period, Chart, Account, Sub-Account, Object, Sub-Object and Object Type
| Column Name | Business Name | Old Logic |
|---|---|---|
| GL_BEG_BASE_BUD_AMT | Beginning Base Budget | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'BB' |
| GL_BASE_BUDGET_AMT | Base Budget Monthly | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current_month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'BB' |
| GL_BASE_BUDGET_YTD_AMT | Base Budget Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'BB' |
| GL_BDGT_REVERSION_AMT | Budget Reversion | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'RE' |
| GL_MONTHLY_BDGT_AMT | Monthly Budget | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'MB' |
| GL_CUR_BDGT_AMT | Current Budget | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current_month_column> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' |
| GL_CUR_BDGT_QTD_AMT | Current Budget Quarter Amount | Step 1: Find the months that are in the current quarter: select FPERIOD_YEAR_NBR from KFS_D_FISCAL_PERIOD where getdate() between FPERIOD_QUARTER_START_DT and FPERIOD_QUARTER_END_DT Step 2: Add up all the corresponding columns for the months in the quarter: select sum( MO<FPERIOD_YEAR_NBR>_ACCT_LN_AMT ) from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' |
| GL_CUR_BDGT_YTD_AMT | Current Budget Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' |
GL_CUR_BDGT_INC_ACCTS
| Current Budget - Income Style Accounts | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current_month_column> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' and FIN_OBJ_TYP_CD = 'IN' and OFT_IND = 'O' |
| GL_CUR_BDGT_PROJ_ACCTS | Current Budget (Project Style Accounts) | Step 1: Find the current month: '100210', '100600', '100700', '101200', '102100', '406330', '408100') |
| GL_ITD_BUDGET_PROJ_ACCTS | Budget Inception to Date (Project Style Accounts) | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select FIN_BEG_BAL_LN_AMT + CONTR_GR_BB_AC_AMT +<all months up to current month> from GL_BALANCE_T, CA_ACCOUNT_T where GL_BALANCE_T.ACCOUNT_NBR = CA_ACCOUNT_T.ACCOUNT_NBR where CA_ACCOUNT_T.SUB_FUND_GRP_CD in (000010;100100;100200;100210;100300;100400;100510;100590;100600;100700;100800;101100;101200;102000;102100;102200;201000;202100;202900;203000;404110;404120;404210;404220;405210;405220;406100;406110;406210;406220;406230;406310;406330;406340;406320;408100;408200;408300;408410;408420;408430 '100210', '100600', '100700', '101200', '102100', '406330', '408100') |
| GL_CUR_INC_BDGT_YTD | Current Income Budget YTD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' and FIN_OBJ_TYP_CD = 'IN' and OFT_IND = 'O' |
| GL_CUR_EXP_BDGT_YTD | Current Expense Budget YTD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' and FIN_OBJ_TYP_CD = 'EX' |
| GL_ACTUALS_AMT | Actuals | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' |
| GL_ACTUALS_QTD_AMT | Actuals Quarter Amount | step 1: find the months that are in the current quarter: select FPERIOD_YEAR_NBR from KFS_D_FISCAL_PERIOD where getdate() between FPERIOD_QUARTER_START_DT and FPERIOD_QUARTER_END_DT step 2: add up all the corresponding columns for the months in the quarter select sum( MO<FPERIOD_YEAR_NBR>_ACCT_LN_AMT ) from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' |
| GL_ACTUALS_YTD_AMT | Actuals Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' |
| GL_ITD_ACTUALS_PROJ_ACCTS | Inception to Date Actuals for Project Accounts | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select CONTR_GR_BB_AC_AMT+FIN_BEG_BAL_LN_AMT+<all months up to current month> from GL_BALANCE_T, CA_ACCOUNT_T where GL_BALANCE_T.ACCOUNT_NBR = CA_ACCOUNT_T.ACCOUNT_NBR and FIN_BALANCE_TYP_CD = 'AC' and CA_ACCOUNT_T.SUB_FUND_GRP_CD in (000010;100100;100200;100210;100300;100400;100510;100590;100600;100700;100800;101100;101200;102000;102100;102200;201000;202100;202900;203000;404110;404120;404210;404220;405210;405220;406100;406110;406210;406220;406230;406310;406330;406340;406320;408100;408200;408300;408410;408420;408430 '100210', '100600', '100700', '101200', '102100', '406330', '408100') |
| GL_INCOME_AMT | Income Actuals | Step 1: Find the current month: OLD LOGIC: select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and FIN_OBJ_TYP_CD = 'IN' and OFT_IND = 'O' |
| GL_INCOME_YTD_AMT | Income Actuals Year to Date | Step 1: Find the current month and all months up to the current month: OLD LOGIC: NEW LOGIC: select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and ACCTG_CTGRY_CD = 'IN' and OFT_IND = 'O' |
| GL_EXPENSE_AMT | Expense Actuals | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and ACCTG_CTGRY_CD = 'EX' |
| GL_EXPENSE_YTD_AMT | Expense Actuals Year to Date | Step 1: Find the current month and all months up to the current month:
NEW LOGIC: select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and ACCTG_CTGRY_CD = 'EX' |
| GL_EXPENSE_YTD_NO_TRNS_AMT | Expense YTD (No Transfers) | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T, UCI_OBJ_MAP where GL_BALANCE_T.FIN_OBJECT_CD = UCI_OBJ_MAP.OBJ_CD and GL_BALANCE_T.FIN_SUB_OBJ_CD = UCI_OBJ_MAP.OBJ_SUB_TYP_CD and GL_BALANCE_T.FIN_OBJ_TYP_CD = UCI_OBJ_MAP.OBJ_TYPE and GL_BALANCE_T.FIN_OBJ_TYP_CD = 'EX' and UCI_OBJ_MAP.UCI_TYPE_IND not in ('T') |
select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T, CA_ACCOUNT_T where GL_BALANCE_T.ACCOUNT_NBR = CA_ACCOUNT_T.ACCOUNT_NBR and GL_BALANCE_T.FIN_OBJ_TYP_CD in ('EX','TE') and CA_ACCOUNT_T.SUB_FUND_GRP_CD in ('000010','100100','100200','100300','100400','100510','100590','100800','101100','10200','102200','201000','202100','202900','203000','404110','404120','404210','404220','405210','405220','406100','406110','406210','406220','406230','406310','406340','406320','408200','408300','408410','408430') | ||
| GL_PROJECT_YTD_AMT | Project Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T, CA_ACCOUNT_T where GL_BALANCE_T.ACCOUNT_NBR = CA_ACCOUNT_T.ACCOUNT_NBR and CA_ACCOUNT_T.SUB_FUND_GRP_CD in (000010;100100;100200;100210;100300;100400;100510;100590;100600;100700;100800;101100;101200;102000;102100;102200;201000;202100;202900;203000;404110;404120;404210;404220;405210;405220;406100;406110;406210;406220;406230;406310;406330;406340;406320;408100;408200;408300;408410;408420;408430 '100210', '100600', '100700', '101200', '102100', '406330', '408100') |
| GL_EXTERNAL_ENCUM_AMT | External Encumbrance | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'EX' |
| GL_EXTERNAL_ENCUM_YTD_AMT | External Encumbrance Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'EX' |
| GL_INTERNAL_ENC_AMT | Internal Encumbrance | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'IE' |
| GL_INTERNAL_ENC_YTD_AMT | Internal Encumbrance Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'IE' |
| GL_PRE_ENCUMB_AMT | Pre-Encumbrance | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'PE' |
| GL_PRE_ENCUMB_YTD_AMT | Pre-Encumbrance Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'PE' |
| GL_ALL_ENCUM_AMT_YTD | Encumbrances and Pre-Encumbrances YTD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD in ('PE','IE','EX') |
| no logic | ||
| no logic | ||
| GL_CLOSE_NOMINAL_BAL | Close Nominal Balance | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT+ CONTR_GR_BB_AC_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'NB' |
| GL_BEG_BAL_AS_LI_FB | Beginning Balance for Assets, Liability and Fund Balance | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_OBJ_TYP_CD in ('AS','LI','FB') |
| GL_BEG_CURR_ASSETS_AMT | Beginning Current Assets | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT from GL_BALANCE_T, CA_OBJECT_CODE_T where GL_BALANCE_T.FIN_OBJECT_CD = CA_OBJECT_CODE_T.FIN_OBJECT_CD and GL_BALANCE_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD and CA_OBJECT_CODE_T.FIN_OBJ_TYP = 'AS' |
| GL_CASH_ACTUALS_YTD | Cash Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT
select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and FIN_OBJ_TYP_CD in ('CH','IN') |
| GL_BEG_CUR_LI_AMT | Beginning Balance for Current Liability | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT from GL_BALANCE_T ,CA_OBJECT_CODE_T where GL_BALANCE_T.FIN_OBJECT_CD = CA_OBJECT_CODE_T.FIN_OBJECT_CD and GL_BALANCE_T.FIN_COA_CD = CA_OBJECT_CODE_T.FIN_COA_CD and GL_BALANCE_T.FIN_OBJ_TYP_CD = 'LI' |
| GL_LIABILITY_AMT | Liability Actuals | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current month> from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and FIN_OBJ_TYP_CD = 'LI' |
| GL_LIABILITY_YTD_AMT | Liability Year to Date | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'AC' and FIN_OBJ_TYP_CD = 'LI' |
| GL_BEG_FUND_BALANCE | Beginning Fund Balance | select GL_BALANCE_T.FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_OBJ_TYP_CD = 'FB' |
| GL_BEG_FUND_BAL_CASH_ACCTS | no logic | |
| GL_BEG_FUND_BAL_IN_ACCTS | Beginning Fund Balance - Income Style Accounts | |
| GL_CUR_FUND_BAL | Current Fund Balance | Step 1: Find the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select GL_BALANCE_T.<current_month> from GL_BALANCE_T where FIN_OBJ_TYP_CD = 'FB' or (FIN_OBJ_TYP = 'IN' and OFT_IND = 'T') |
| GL_CUR_FUND_BAL_INC_ACCTS | ||
| GL_CUR_FUND_BAL_CASH_ACCTS | ||
| GL_FUND_BAL_BUDGET_ITD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+CONTR_GR_BB_AC_AMT from GL_BALANCE_T where BAL_TYP_CD = 'CB' and ACCTG_CTGRY_CD = 'FB' or (object_typ = 'IN' and OFT_IND = 'T') and CA_ACCOUNT_T.SUB_FUND_GRP_CD in (000010;100100;100200;100210;100300;100400;100510;100590;100600;100700;100800;101100;101200;102000;102100;102200;201000;202100;202900;203000;404110;404120;404210;404220;405210;405220;406100;406110;406210;406220;406230;406310;406330;406340;406320;408100;408200;408300;408410;408420;408430 '100210', '100600', '100700', '101200', '102100', '406330', '408100') | |
| GL_FUND_BAL_BUDGET_YTD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where BAL_TYP_CD = 'CB' ACCTG_CTGRY_CD = 'FB' or (OBJ_TYP_CD = 'IN' and OFT_IND = 'T')
| |
| GL_FUND_BAL_ACTUALS_ITD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+CONTR_GR_BB_AC_AMT from GL_BALANCE_T where BAL_TYP_CD = 'AC' and ACCTG_CTGRY_CD = 'FB' or (object_typ = 'IN' and OFT_IND = 'T') and CA_ACCOUNT_T.SUB_FUND_GRP_CD in (000010;100100;100200;100210;100300;100400;100510;100590;100600;100700;100800;101100;101200;102000;102100;102200;201000;202100;202900;203000;404110;404120;404210;404220;405210;405220;406100;406110;406210;406220;406230;406310;406330;406340;406320;408100;408200;408300;408410;408420;408430 '100210', '100600', '100700', '101200', '102100', '406330', '408100') | |
| GL_FUND_BAL_ACTUALS_YTD | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where BAL_TYP_CD = 'AC' and ACCTG_CTGRY_CD = 'FB' or (OBJ_TYP_CD = 'IN' and OFT_IND = 'T')
| |
| GL_AVAIL_BALANCE | Ledger Available Balance | If object_type in ('EX', 'EE', 'AS' , 'ES', 'TE') then (GL_CUR_BDGT_YTD_AMT - GL_ACTUALS_YTD_AMT - GL_ALL_ENCUM_AMT_YTD ) If object_type in ('IN', 'IC', 'FB', 'LI') then (GL_ACTUALS_YTD_AMT + GL_ALL_ENCUM_AMT_YTD) - (GL_CUR_BDGT_YTD_AMT) |
| GL_AVAIL_BALANCE_EXCL_ENC | Ledger Available Balance Excluding Encumbrances | If object_type in ('EX', 'EE', 'AS' , 'ES', 'TE') then (GL_CUR_BDGT_YTD_AMT - GL_ACTUALS_YTD_AMT ) If object_type in ('IN', 'IC', 'FB', 'LI') then (GL_ACTUALS_YTD_AMT - (GL_CUR_BDGT_YTD_AMT) ) |
| GL_ITD_AVAIL_BAL_PROJ_ACCTS | Inception to Date Available Balance Project Accounts | If object_type in ('EX', 'EE', 'AS' , 'ES', 'TE') then (GL_ITD_BUDGET_PROJ_ACCTS - GL_ITD_ACTUALS_PROJ_ACCTS - GL_ALL_ENCUM_AMT_YTD ) If object_type in ('IN', 'IC', 'FB', 'LI') then (GL_ITD_ACTUALS_PROJ_ACCTS + GL_ALL_ENCUM_AMT_YTD ) - GL_ITD_BUDGET_PROJ_ACCTS ) ) |
| GL_ITD_AVAIL_BAL_PROJ_ACCTS_EXCL_ENC | Inception to Date Available Balance Project Accounts Excluding Encumbrances | If object_type in ('EX', 'EE', 'AS' , 'ES', 'TE') then (GL_ITD_BUDGET_PROJ_ACCTS - GL_ITD_ACTUALS_PROJ_ACCTS ) If object_type in ('IN', 'IC', 'FB', 'LI') then (GL_ITD_ACTUALS_PROJ_ACCTS ) - GL_ITD_BUDGET_PROJ_ACCTS ) ) |
| GL_NET_CASH_AVAIL | ||
| GL_NET_CASH_CHANGE | ||
| GL_UNCOMM_CASH_EXP | ||
| GL_SOURCES_OF_FUNDS_YTD | ||
| GL_USES_OF_FUNDS_YTD | ||
| GL_ACTUALS_GENERAL | ||
| GL_ACTUALS_GENERAL_YTD | ||
| GL_ACTUALS_RESTRICTED | ||
| GL_ACTUALS_RESTRICTED_YTD | ||
| GL_ACTUALS_LOAN_FUNDS | ||
| GL_ACTUALS_LOAN_FUNDS_YTD | ||
| GL_ACTUALS_ENDOWMENTS | ||
| GL_ACTUALS_ENDOWMENTS_YTD | ||
| GL_ACTUALS_PLANT | ||
| GL_ACTUALS_PLANT_YTD | We will discuss after KFSDW-890 is in production. | |
| GL_ACTUALS_OPEB | ||
| GL_ACTUALS_OPEB_YTD | ||
| GL_ACTUALS_RP | ||
| GL_ACTUALS_RP_YTD | ||
| GL_ACTUALS_UCRS | ||
| GL_ACTUALS_UCRS_YTD | ||
| GL_ACTUALS_AGENCY | ||
| GL_ACTUALS_AGENCY_YTD | ||
| GL_ACTUALS_OTHER | ||
| GL_ACTUALS_OTHER_YTD | ||
| GL_ACTUALS_DESIGNATED | ||
| GL_ACTUALS_DESIGNATED_YTD | ||
| GL_ACTUALS_YTD_AMT_ADJ | populated from KFS_F_LEDGER_ENTRIES_SCRENP | |
| GL_ACTUALS_AMT_ADJ | populated from KFS_F_LEDGER_ENTRIES_SCRENP | |
| GL_MD_AMOUNT | Med Ctr Depreciation Balance | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT
select <current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'MD' |
| GL_MD_YTD_AMOUNT | Med Ctr Depreciation YTD Balance | Step 1: Find the current month and all months up to the current month: CASE if July then MO1_ACCT_LN_AMT if August then MO2_ACCT_LN_AMT if September then MO3_ACCT_LN_AMT if October then MO4_ACCT_LN_AMT if November then MO5_ACCT_LN_AMT if December then MO6_ACCT_LN_AMT if January then MO7_ACCT_LN_AMT if February then MO8_ACCT_LN_AMT if March then MO9_ACCT_LN_AMT if April then MO10_ACCT_LN_AMT if May then MO11_ACCT_LN_AMT if June and not Final then MO12_ACCT_LN_AMT if June and Final then MO13_ACCT_LN_AMT
select <all months up to current month>+FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'MD' |
| GL_BEG_CUR_BUDGET_AMT | Beginning Current Budget Amount | select FIN_BEG_BAL_LN_AMT from GL_BALANCE_T where FIN_BALANCE_TYP_CD = 'CB' |
| NEW COLUMNS | ||
| GL_BUDGET_YTD_CARRY_FWD | Carryforward Amount | Aggregate ledger details where DOC_TYP = 'CFWD'. All details lines for the current year. Inserted into every period. JIRA: KFSDW-1016 |
| ITD Current Year Beginning Budget Available Balance | Katherine will send functional specs. Test with FG14356 | |
| Fund Total for Actuals | Katherine will send functional specs. Compare with fund summary report | |
.
Notes from 8/18 meeting:
Control Account Summary Report
- Add a fund balance section at the top, then income, then expense
- Remove trans code from income balances
- Include trans code balances in fund balance section