KBM Decision Support Meeting Notes 7-2-2014

Changes to ODS:

1) Create a view for csf_tracker_snapshot_t which joins to either uci_ld_intended_incumbent_id or employee_basic to retrieve name

PROVISION_STATUS_CD: O - Open; F - Filled; C - Closed.

If F, then use CSF_TRACKER_T EMPLID to derive employee name from LDAP; if O, then use INTENDED_INCUMENT_ID to derive name from LD_INTENDED_INCUMBENT_T; if C, do not show.

Include in view:

select a.univ_fiscal_yr, a.fin_coa_cd, a.account_nbr, a.fin_object_cd, a.position_nbr, base_fte, base_salary, b.ppm_amt, b.ppm_fte, a.base_salary - b.ppm_amt variance_sal, a.base_fte - b.ppm_fte variance_fte

from

(select univ_fiscal_yr, fin_coa_cd, account_nbr, sub_acct_nbr, fin_object_cd, fin_sub_obj_cd, fin_balance_typ_cd, position_nbr, sum(nvl(trn_total_hr,0)) base_fte, sum(fin_beg_bal_ln_amt + acln_annl_bal_amt) base_salary

from ld_ldgr_bal_t

where fin_balance_typ_cd = 'BB'

group by univ_fiscal_yr, fin_coa_cd, account_nbr, sub_acct_nbr, fin_object_cd, fin_sub_obj_cd, fin_balance_typ_cd, position_nbr)

a,

(select univ_fiscal_yr, fin_coa_cd, account_nbr, fin_object_cd, position_nbr, sum(csf_amt) ppm_amt, sum(fte_qty) ppm_fte

from uci_ld_csf_tracker_t

group by univ_fiscal_yr, fin_coa_cd, account_nbr, fin_object_cd, position_nbr)

b

where a.univ_fiscal_yr = b.univ_fiscal_yr and a.fin_coa_cd = b.fin_coa_cd and a.account_nbr = b.account_nbr and a.fin_object_cd = b.fin_object_cd and a.position_nbr = b.position_nbr

2) Create a view that pivots out the balance columns to separate rows in the ld_ldgr_bal_t

3) Create a view that is a left outer join between ld_ldgr_entr_t and UCI_LD_TRNS_TYP_CD_T (grade=TRNS_TYP_CD) to pull in TRNS_TYP_NM

add employee name and trans_typ_nm (from grade)

Reports should only show csf_tracker, position_data records, intended_incumbent with an active_ind = 'Y'

Link account dimension to the balance and ledger entries tables, not csf tracker.

Take out pps snapshot tables from cognos model for now.