《spreadsheet budgeting error check queries电子表格预算错误检查查询》由会员分享，可在线阅读，更多相关《spreadsheet budgeting error check queries电子表格预算错误检查查询（5页珍藏版）》请在金锄头文库上搜索。
1、Spreadsheet BudgetingError Check QueriesRun Before Downloading Spreadsheet to Check Banner Data1) Find all active positions with zero budget and FTE 0Run:Selectnbrptot_posn, nbrplbd_acci_code, nbrplbd_fund_code, nbrplbd_orgn_code, nbrplbd_acct_code, nbrplbd_prog_code, nbrptot_fte, nbrptot_budget, nb
2、rptot_expend, nbrptot_encumbFromnbbposn, nbrptot, nbrplbdWherenbbposn_status= A andnbbposn_coas_code=nbrptot_coas_code andnbbposn_posn=nbrptot_posn andnbrptot_coas_code=nbrplbd_coas_code andnbrptot_posn=nbrplbd_posn andnbrptot_fisc_code= nbrplbd_fisc_code andnbrplbd_fisc_code=2005 andnbrptot_budget
3、= 0 and-(nbrptot_expend = 0 or nbrptot_expend is null) andnbrptot_fte0 andnbrplbd_fund_code like 4% andnbrptot_posn like 4% order by nbrptot_posn asc, nbrplbd_orgn_code ascFisc_code, fund_code, and posn are variables. Fund code and posn lines can be removed if you want to look at all positions for a
4、ll funds.To correct these records, we look to see if there are any encumbrances or expenses associated with the position. If yes, we change the FTE to zero. If not, we change the FTE to zero and “freeze” the position. 2) Find all active positions with zero budget, with expenses zero or null. Run sam
5、e query above with comment removed from nbrptot_expend line and added to nbrptot_fte line. Again, fund code and position lines are optional.Correct encumbrances to zero (our personnel dept. does this), and then “freeze” the positions.3) Find all active positions with zero FTE and budget 0. Run same
6、query as above, changing nbrptot_budget and nbrptot_fte values. Fund code and position lines are optional.Investigate and correct FTE if needed. These may be positions used for overhead cost calculations that require negative budget. The system will not currently allow entry of a negative FTE, so no
7、 correction is available in that circumstance. 4) Active positions with inactive or terminated fund, org, or program components. For Inactive Funds run:select nbrplbd_posn POSN, nbrplbd_fund_code FUND, ftvfund_title TITLE, ftvfund_eff_date, ftvfund_term_datefrom nbrplbd, nbbposn, ftvfundwherenbrplbd
8、_posn =nbbposn_posn and nbrplbd_fund_code =ftvfund_fund_code and-nbrplbd_fund_code like 4% andnbrplbd_fisc_code = 2005 andnbbposn_status = A and(ftvfund_term_date is not null orftvfund_status_ind = I) andftvfund_eff_date =sysdate or ftvfund_nchg_date is null)order by POSN, FUNDFor Inactive Salary Bu
9、dget orgs run:select nbrptot_posn, nbrptot_orgn_code, ftvorgn_status_indfrom nbrptot, ftvorgn where ftvorgn_coas_code = nbrptot_coas_code andnbrptot_orgn_code = ftvorgn_orgn_code andnbrptot_status = A andnbrptot_fisc_code = 2005 and-ftvorgn_orgn_code like 4% and ftvorgn_status_ind = I andftvorgn_eff
10、_date =sysdate or ftvorgn_nchg_date is null)For Inactive Labor Distribution orgs run:select nbrplbd_posn, nbrplbd_orgn_code, ftvorgn_status_indfrom nbrplbd, ftvorgn, nbbposn where ftvorgn_coas_code = nbrplbd_coas_code andnbrplbd_orgn_code = ftvorgn_orgn_code andnbrplbd_posn = nbbposn_posn andnbbposn
11、_status = A andnbrplbd_fisc_code = 2005 and-ftvorgn_orgn_code like 4% and ftvorgn_status_ind = I andftvorgn_eff_date =sysdate or ftvorgn_nchg_date is null)For Inactive Programs run:select nbrplbd_posn, nbrplbd_prog_code, ftvprog_status_ind, ftvprog_prog_codefrom ftvprog, nbrplbd wherenbrplbd_coas_co
12、de = ftvprog_coas_code andnbrplbd_prog_code = ftvprog_prog_code andnbrplbd_fisc_code = 2005 andftvprog_status_ind = I andftvprog_eff_date =sysdate or ftvprog_nchg_date is null)Corrections to FOP elements need to be made in each incorrect position in NBAPBUD. Corrections to Salary Budget orgs may req
13、uire an Org Transfer.5) Active positions where the Salary Budget Org does not match at least one org in the Position Labor Distribution lines. This situation creates NUMEROUS validation errors. Run a “make table” query using the following script:Selectnbrptot_posn, nbrptot_orgn_code, nbrplbd_acci_co
14、de, nbrplbd_fund_code, nbrplbd_orgn_code, nbrplbd_acct_code, nbrplbd_prog_code, nbrplbd_percent, nbrptot_fte, nbrptot_budget, nbrplbd_budget, nbbposn_statusFromnbrptot, nbrplbd, nbbposnWherenbrptot_status= A andnbrptot_coas_code=nbrplbd_coas_code andnbrptot_posn=nbrplbd_posn andnbrptot_fisc_code= nb
15、rplbd_fisc_code andnbrptot_posn = nbbposn_posn andnbrptot_coas_code = nbbposn_coas_code andnbbposn_status = A andnbrplbd_fisc_code=2005 -nbrplbd_fund_code like 4% -nbrptot_posn like 4% order by nbrptot_posn asc, nbrplbd_orgn_code asc I then run a series of Access queries from the table to determine position records where nbrptot_orgn_c
