FAST Reports Technical Documentation

FAST Reports Technical Documentation

1.1  Chartfield Validation Tables

Budget Transaction Detail Report

1. Detail Report

KK_SOURCE_HDR

-  This table stores information on all Detail transactions. The primary key is KK_TRAN_ID

-  The transaction is categorized as either a Journal entry, a Requisition, a Voucher or a Purchase order.

-  This table is linked to the KK_ACTIVITY_LOG table in 1: M relationship. A particular transaction can have multiples line items in the KK_ACTIVITY_LOG table.

KK_ACTIVITY_LOG

-  This table is the log table for the transactions. It can have multiple line items for each transaction. Its primary key is a combination of KK_TRAN_ID and KK_TRAN_LN (line Item).

-  Each transaction (KK_TRAN_ID) is associated with a particular chartfield combo.

-  The LEDGER field identifies the type of transaction and type of funding source i.e. Pre Encumbrance, Encumbrance or Expended.

For Grants

CC_GMC_PRE (Pre-encumbrance)

CC_GMC_ENC (Encumbrance)

CC_GMC_EXP (Expended)

For Others

CC_DTL_PRE (Pre-encumbrance)

CC_ DTL _ENC (Encumbrance)

CC_ DTL _EXP (Expended)

- The amount field is monetary_amount

JRNL_HEADER

-  This is the Journal Header table. It stores the information about the journal entry. The primary key is JOURNAL_ID.

-  It is linked to KK_SOURCE_HDR and JRNL_LN tables.

JRNL_LN

-  This is the Journal Line table. Its stores detail information about journal transactions. It has a 1: M relationship with the JRNL_HEADER.

-  The composite primary key is JOURNAL_ID and JOURNAL_LINE.

-  The description field is DESCR254_mixed

REQ_HDR

-  This is the Requisition Header table. Its stores the information about each requisition.

-  It bears a 1: M relationship with the REQ_LINE table.

REQ_LINE

- Its stores detail information about requisitions.

-  This table has a composite primary key i.e. REQ_ID and LINE_NBR.

VOUCHER

-  This table stores the information about the vouchers.

-  Has a 1: M relationship with DISTRIB_LINE table.

DISTRIB_LINE

-  This stores the detail information about all the vouchers.

-  It has a composite primary key i.e. VOUCHER_ID,

VOUCHER_LINE_NUM, DISTRIB_LINE_NUM

PO_HDR

-  This is the Purchase Order header table. It stores information about all the purchase orders along with the VENDOR_ID.

PO_LINE

-  This stores the detail information about the purchase orders along with the description in the column DESCR254_MIXED. It has a 1: M relationship with the PO_HDR

-  It has a composite primary key i.e. PO_ID and LINE_NBR.

PS_VENDOR

-  This gives information on vendors wherever applicable.

-  The primary key is VENDOR_ID

Report fields

1. Budget Date (KK_ACITIVTY_LOG.KK_tran_DT)

2.  Document ID (KK_source_HDR.journal_id, req_id, voucher_id, po_id)

3.  Line (KK_ACTIVITY_LOG.KK_TRAN_LN)

4.  Reference

JRNL_LN.JOURNAL_DATE + JRNL_LN.DESCR254_MIXED

VENDOR.Name1 + REQ_LINE.DESCR254_MIXED

VENDOR.Name1 + DISTRIB_LINE.DESCR

VENDOR.Name1 + PO_LINE.DESCR254_MIXED

5.  Pre Encumbered Amt. (KK_TRAN_LOG.monetary_AMT based on type of ledger)

6.  Encumbered Amt. (KK_TRAN_LOG. monetary _AMT based on type of ledger)

7.  Expended Amt. (KK_TRAN_LOG. monetary _AMT based on type of ledger)

SQL Query used:

SELECT ALOG.KK_TRAN_ID,

ALOG.PROJECT_ID,

ALOG.BUSINESS_UNIT,

ALOG.OPERATING_UNIT,

ALOG.DEPTID,

ALOG.FUND_CODE,

ALOG.PRODUCT,

ALOG.CHARTFIELD1,

ALOG.FISCAL_YEAR,

ALOG.ACCOUNTING_PERIOD,

ALOG.LEDGER,

ALOG.KK_TRAN_DT BUDGETPOSTDATE,

ALOG.ACCOUNT,

ALOG.KK_SOURCE_TRAN TRANS,

ALOG.DOCUMENTID,

ALOG.LINE_NBR KK_TRAN_LN,

PREENCUMBER,

ENCUMBER,

EXPENDEDAMOUNT,

NVL(TDESCR.NAME1,'') || ' ' || NVL(TDESCR.DESCR,'') DESCR

FROM

(

SELECT SHDR.KK_SOURCE_TRAN,

CASE

WHEN KK_SOURCE_TRAN LIKE 'GL%' THEN JOURNAL_ID

WHEN KK_SOURCE_TRAN LIKE 'REQ%' THEN REQ_ID

WHEN KK_SOURCE_TRAN LIKE 'PO%' THEN PO_ID

WHEN KK_SOURCE_TRAN LIKE 'AP%' THEN VOUCHER_ID ELSE '' END DOCUMENTID,

ALOG.KK_TRAN_ID, CASE WHEN KK_SOURCE_TRAN LIKE 'GL%' THEN SLINE.JOURNAL_LINE ELSE ALOG.KK_TRAN_LN END LINE_NBR

,ALOG.PROJECT_ID,ALOG.BUSINESS_UNIT,ALOG.OPERATING_UNIT, ALOG.DEPTID, ALOG.FUND_CODE,

ALOG.PRODUCT,ALOG.CHARTFIELD1, ALOG.FISCAL_YEAR, ALOG.ACCOUNTING_PERIOD,

ALOG.LEDGER,ALOG.KK_TRAN_DT ,ALOG.ACCOUNT,

CASE WHEN ALOG.LEDGER LIKE '%PRE' THEN ROUND(NVL(ALOG.MONETARY_AMOUNT,0),2) ELSE 0 END PREENCUMBER,

CASE WHEN ALOG.LEDGER LIKE '%ENC' THEN ROUND(NVL(ALOG.MONETARY_AMOUNT,0),2) ELSE 0 END ENCUMBER,

CASE WHEN ALOG.LEDGER LIKE '%EXP' THEN ROUND(NVL(ALOG.MONETARY_AMOUNT,0),2) ELSE 0 END EXPENDEDAMOUNT, ALOG.KK_TRAN_LN,

SLINE.JOURNAL_LINE,

CASE WHEN KK_SOURCE_TRAN LIKE 'GL%' THEN 'GL'||'-'||journal_id||'-'||journal_line

WHEN KK_SOURCE_TRAN LIKE 'REQ%' THEN 'RE'||'-'||req_ID||'-'||alog.KK_tran_ln

WHEN KK_SOURCE_TRAN LIKE 'PO%' THEN 'PO'||'-'||PO_ID||'-'||alog.KK_tran_ln

WHEN KK_SOURCE_TRAN LIKE 'AP%' THEN 'AP'||'-'||voucher_ID||'-'||alog.KK_tran_ln end docKey

FROM

PS_KK_SOURCE_HDR SHDR,

PS_KK_SOURCE_LN SLINE,

(select * from PS_KK_ACTIVITY_LOG where (ledger like 'CC_DTL%' or ledger like 'CC_GMC%' ) and account >= 50000 ) ALOG

WHERE

ALOG.KK_TRAN_ID = SHDR.KK_TRAN_ID

AND ALOG.KK_TRAN_ID = SLINE.KK_TRAN_ID

AND ALOG.KK_TRAN_LN = SLINE.KK_TRAN_LN

) ALOG,

(

SELECT distinct 'PO' KK_SOURCE_TRAN , PLINE.PO_ID DOCUMENTID ,

PLINE.LINE_NBR LINE_NBR,PVEN.NAME1 NAME1,

PLINE.DESCR254_MIXED DESCR, 'PO'||'-'||pline.PO_ID||'-'||pline.line_nbr DocKey

FROM PS_PO_LINE PLINE, PS_PO_HDR PHDR, PS_VENDOR PVEN

WHERE PHDR.PO_ID = PLINE.PO_ID (+)

AND PHDR.VENDOR_ID = PVEN.VENDOR_ID (+)

UNION ALL

SELECT distinct 'RE' KK_SOURCE_TRAN , RLINE.REQ_ID DOCUMENTID ,RLINE.LINE_NBR LINE_NBR,RVEN.NAME1 NAME1,

RLINE.DESCR254_MIXED DESCR, 'RE'||'-'||rline.req_ID||'-'||rline.line_nbr DocKey

FROM PS_REQ_LINE RLINE, PS_VENDOR RVEN

WHERE RLINE.VENDOR_ID = RVEN.VENDOR_ID (+)

UNION ALL

SELECT distinct 'AP' KK_SOURCE_TRAN , DLINE.VOUCHER_ID DOCUMENTID , dline.voucher_line_num,DVEN.NAME1 NAME1,

DLINE.DESCR || ' ('|| NVL(INVOICE_ID,'') || ')' DESCR, 'AP'||'-'||Pvou.voucher_ID||'-'||nvl(dline.voucher_line_num,'') DocKey

FROM PS_VOUCHER PVOU, PS_DISTRIB_LINE DLINE, PS_VENDOR DVEN

WHERE PVOU.VOUCHER_ID = DLINE.VOUCHER_ID (+)

AND PVOU.VENDOR_ID = DVEN.VENDOR_ID(+)

) TDESCR

WHERE

alog.dockey = tdescr.dockey(+)

ORDER BY ACCOUNT, BUDGETPOSTDATE,KK_TRAN_LN

2. RSA Summary Reports

LEDGER_KK

-  This is the commitment control table

-  The type of transaction entry depends on the ledger field.

For Grants

Budget Field- CC_GMC_BUD

Expense Field- CC_GMC_EXP

Encumbrance Field- CC_GMC_ENC

Pre-encumbrance Field- CC_GMC_PRE

For Other

Budget Field- CC_ORG_BUD or CC_DRG_BUD

Expense Field- CC_DTL_EXP

Encumbrance Field- CC_ DTL _ENC

Pre-encumbrance Field- CC_ DTL _PRE

-  The Account field has the different budget and expense accounts. The association between the budget and expense accounts can be obtained from the USF_ACCOUNT_RSA2 and USF_ACCOUNT_RSA reporting trees.

-  The amount field is POSTED_TOTAL_AMT

-  The Chartfield Combination fields use for report criteria are Projectid, operating_unit, deptid, fund_code, product, chartfield1

LEDGER

-  This is the ACTUALS table.

-  The type of transaction entry depends on the ledger field.

-  The Account field has the different budget and expense accounts. The association between the budget and expense accounts can be obtained from the USF_ACCOUNT_RSA2 and USF_ACCOUNT_RSA reporting trees.

-  The amount field is POSTED_TOTAL_AMT

-  The Chartfield Combination fields use for report criteria are Projectid, operating_unit, deptid, fund_code, product, chartfield1

Report Field Description

1.  Account

This is the account to which the expenditure is charged. e.g. 5002, 50023 etc.

The accounts have a hierarchical structure as in each account will be associated with a node which in turn would be associated with a tree.

All this information is stored in two major tables, TREE_NODE_TBL and TREE_LEAF. The tree name to be used is USF_ACCOUNT_RSA2

The accounts in this reports needs to be grouped according to this structure.

2.  Account Description

Gives information about the account e.g. Wages, Travel etc.

3.  Budgeted Amount (Posted_Total_AMT based on type of ledger)

This is the total budget allocated for the project.

1. Pre Encumbered Amt. (Posted_Total_AMT based on type of ledger)

1. Encumbered Amt. (Posted_Total_AMT based on type of ledger)

1. Expended Amt. (Posted_Total_AMT based on type of ledger)

7.  Remaining Spending Authority (RSA)

This is calculated by subtracting the Expenditure from the Budgeted amount.

Example Grouping Queries using treenode and treeleaf

For Aux, E&G

select n.tree_node,l.range_from,l.range_to

from pstreenode n left outer join pstreeleaf l

on n.tree_name = l.tree_name

where

n.tree_name = 'USF_ACCOUNT_RSA' and n.tree_level_num = 4

and l.tree_node_num >= n.tree_node_num and l.tree_node_num <= n.tree_node_num_end

and range_from > '50000' and n.tree_node > 'NO_BUDGET'

For Grants

select n.tree_node,l.range_from,l.range_to

from pstreenode n left outer join pstreeleaf l

on n.tree_name = l.tree_name

where

n.tree_name = 'USF_ACCOUNT_RSA2' and n.tree_level_num = 3

and l.tree_node_num >= n.tree_node_num and l.tree_node_num <= n.tree_node_num_end

and range_from >= '50000'

Example RSA Reporting Function (T-SQL)

For Grants

select chartfieldcombo chartfieldcombo1, trantype,case when n.tree_node like '81550' then 'indirect' else 'direct' end expensetype,n.tree_node parent_account,t.account,t.descr account_descr, sum(budamt) budamt,

sum(preamt) preamt, sum(encamt) encamt, sum(expamt) expamt,

sum(ActexpAmt) actexpamt,sum(isnull(budamt,0)) - (sum(isnull(expAmt,0)) + sum(isnull(encamt,0)) + sum(isnull(preamt,0))) rsa,sum(isnull(budamt,0)) - (sum(isnull(ActexpAmt,0)) + sum(isnull(encamt,0)) + sum(isnull(preamt,0))) actrsa,

case when t.account like '8%' then 1 else 2 end sortorder

from (

select isnull(project_id,'') chartfieldCombo,

case when fund_code in ('20000','21000','21500','22000','22500') then 'funded' else 'cstshare' end trantype,

ledger,account,0 budamt,0 preamt,0 encamt,0 expamt,sum(isnull(posted_total_amt,0)) ActExpamt,

0 rsa,0 actrsa

from ps_ledger

where business_unit = 'USF01'

and isnull(project_id,'')

like @chartfieldCombo

--and account > 50000 and account < 80000

and ledger in ('ACTUALS')

and accounting_period not in (0,999)

and ( dbo.convertFiscal_period(fiscal_year, accounting_period) between @fiscal_periodStart and @fiscal_periodEnd or dbo.convertFiscal_period(fiscal_year, accounting_period) like @fiscal_period998 )

and dbo.convertFiscal_period(fiscal_year, accounting_period) between @calendarStartPeriod and @calendarEndPeriod

group by isnull(project_id,''),

case when fund_code in ('20000','21000','21500','22000','22500') then 'funded' else 'cstshare' end,

ledger,account

union all

select isnull(project_id,'') chartfieldCombo,

case when fund_code in ('20000','21000','21500','22000','22500') then 'funded' else 'cstshare' end trantype,ledger,account,

case when ledger = 'CC_GMC_BUD' then dbo.convertBudgetAmount(sum(isnull(Posted_total_amt,0))) end budamt,

case when ledger = 'CC_GMC_PRE' and account >= 80000 then sum(isnull(Posted_total_amt,0)) end preamt,

case when ledger = 'CC_GMC_ENC' and account >= 80000 then sum(isnull(Posted_total_amt,0)) end encamt,

case when ledger = 'CC_GMC_EXP' and account >= 80000 then sum(isnull(Posted_total_amt,0)) end expamt,0 actexpamt,0 rsa,0 actrsa

from ps_ledger_kk

where business_unit = 'USF01'

and isnull(project_id,'')

like @chartfieldCombo

and ledger in ( 'CC_GMC_BUD','CC_GMC_EXP','CC_GMC_PRE', 'CC_GMC_ENC')

and dbo.convertFiscal_period(fiscal_year, accounting_period) between @fiscal_periodStart and @fiscal_periodEnd

and dbo.convertFiscal_period(fiscal_year, accounting_period) between @calendarStartPeriod and @calendarEndPeriod

group by isnull(project_id,''),

case when fund_code in ('20000','21000','21500','22000','22500') then 'funded' else 'cstshare' end,

ledger,account

)R , RSASummaryTreeGrant_vw N,ps_gl_account_tbl t

where r.account >= n.range_from and r.account <= n.range_to

and r.account = t.account

and isnull(chartfieldcombo,'') > ''

group by chartfieldcombo,trantype,

n.tree_node,t.account, t.descr

order by chartfieldcombo1, tranType desc, parent_account,sortOrder,t.account

11