Financial Accounting System

Identifying Pending PAS Transactions that will be rejected in month 1 and month 25 Processes

Run the query on the sdbsqlc1db1 server (the database is under Database Snapshots) and copy the result to an Excel spreadsheet

This example query checks for stacked pending PAS transactions code 40's that are presumably the source of rejects in the first full month 01 and month 25 runs.

SELECT  pas.budget_number, pas.extract_flag, bgt.budget_status, pas.account_code, pas.requisition_no ,
		pas.pca_project, pas.pca_task, pas.pca_option, pas.prior_yr,
		pas.addl_description, pas.line_amount,
		bgt.budget_name, bgt.organ_code, bgt.fed_doc_num,
		bgt.revenue_class + bgt.revenue_source as Revenue_Code, bgt.curr_per_end_dt, bgt.prin_invgtr,
		bgt.dept_con_name, bgt.dept_con_phone, substring (bgt.budget_number_6,1,2) as local_prog,
		bgt.budget_type
FROM   UWpasdb.dbo.pending_fas_trans pas
INNER JOIN  FINDB.dbo.budget_no_index  bgt
ON  bgt.biennium_year =  pas.biennium_year
AND bgt.budget_number_6 = pas.budget_number
WHERE pas.extract_flag != 'E' and pas.biennium_year > '2009' and pas.trans_code = '40'
AND bgt.budget_status != '1'
ORDER BY pas.budget_number

Note:  pas.biennium_year must be greater than the previous biennium year.



The following example queries check for encumbrance crossover transactions that may reject due to the encumbrance budget number not being
crossed over to the new biennium.

A simple example that doesn't take into account that G&C budgets in budget status 3 are exempt from the "status must=1" edit at biennium crossover,
nor does it take into account that if Account-Flag 24 is set to "1" that the budget will be purged.  Adding those qualifications makes for a much
more complex query that is not warranted give the fact that you can copy your results into Excel and filter out Grant/Gift Budgets and there are things you can do for Account Flag 24 as well.

The more complex example is further down below.

SELECT  bgt.budget_number, bgt.budget_status,
		enc.req_prefix + enc.req_number + enc.req_suffix as requisition_number,
		enc.account_code, enc.total_encum_bal, enc.original_amount, enc.adjustments, enc.total_liq_amt,
		enc.additional_desc, enc.reference_name, bgt.budget_name, bgt.organ_code,
		bgt.fed_doc_num, bgt.revenue_class + bgt.revenue_source as Revenue_Code, bgt.curr_per_end_dt,
		bgt.prin_invgtr, bgt.dept_con_name, bgt.dept_con_phone, substring (bgt.budget_number_6,1,2) as local_prog, bgt.budget_type
FROM   MISDB.dbo.encumbranc_ledger enc
INNER JOIN  FINDB.dbo.budget_no_index  bgt
ON enc.biennium_ind = bgt.biennium_ind
AND enc.budget_number = bgt.budget_number_6
WHERE enc.record_type = 10 and enc.biennium_ind = '11' and enc.total_encum_bal > 0
AND bgt.budget_status != '1'
ORDER BY bgt.budget_number

Note:  enc.biennium_ind must be equal to the current biennium ind.



A slightly more complex query that includes the use of Budget_Type to exclude G&C budgets in status 3.  However, there is some risk using
Budget_Type rather than Local Program to identify G&C budgets.  Could be rewritten to target Local Program but it becomes rather verbose
to try to do it that way.

SELECT  bgt.budget_number, bgt.budget_status,
		enc.req_prefix + enc.req_number + enc.req_suffix as requisition_number,
		enc.account_code, enc.total_encum_bal, enc.original_amount, enc.adjustments, enc.total_liq_amt,
		enc.additional_desc, enc.reference_name, bgt.budget_name, bgt.organ_code,
		bgt.fed_doc_num, bgt.revenue_class + bgt.revenue_source as Revenue_Code, bgt.curr_per_end_dt,
		bgt.prin_invgtr, bgt.dept_con_name, bgt.dept_con_phone, substring (bgt.budget_number_6,1,2) as local_prog, bgt.budget_type
FROM   MISDB.dbo.encumbranc_ledger enc
INNER JOIN  FINDB.dbo.budget_no_index  bgt
ON enc.biennium_ind = bgt.biennium_ind
AND enc.budget_number = bgt.budget_number_6
WHERE enc.record_type = 10 and enc.biennium_ind = '11' and enc.total_encum_bal > 0
AND ((bgt.budget_status !='1' and (bgt.budget_type !='05' and bgt.budget_type != '06')) OR
     (bgt.budget_status ='4' and (bgt.budget_type ='05' OR bgt.budget_type = '06')))
ORDER BY bgt.budget_number

Note:  enc.biennium_ind must be equal to the current biennium ind.



Here is the example that includes checking for Account-Flag 24 = "1".  As you can see it requires a double inner join.

SELECT  enc.budget_number, bgt.budget_status,
		enc.req_prefix + enc.req_number + enc.req_suffix as requisition_number,
		enc.account_code, enc.total_encum_bal, enc.original_amount, enc.adjustments, enc.total_liq_amt,
		enc.additional_desc, enc.reference_name, bgt.budget_name, bgt.organ_code,
		bgt.fed_doc_num, bgt.revenue_class + bgt.revenue_source as Revenue_Code, bgt.curr_per_end_dt,
		bgt.prin_invgtr, bgt.dept_con_name, bgt.dept_con_phone, substring (bgt.budget_number_6,1,2) as local_prog, bgt.budget_type
FROM   MISDB.dbo.encumbranc_ledger enc
INNER JOIN  FINDB.dbo.budget_no_index  bgt
ON enc.biennium_ind = '11' AND
   bgt.biennium_ind = '13' AND
   enc.budget_number = bgt.budget_number_6
INNER JOIN  FINDB.dbo.budget_no_index_account_flag flag
ON    flag.biennium_year = '2013' AND
   flag.budget_number = bgt.budget_number_6 AND
   flag.index1 = 24
WHERE enc.record_type = 10 and enc.biennium_ind = '11' and enc.total_encum_bal > 0
AND (bgt.budget_status != '1' OR flag.account_flag = '1')
ORDER BY enc.budget_number

Note:  enc.biennium_ind must be equal to the current biennium ind.  bgt.biennium_ind must be equal to the future biennium ind.
flag.biennium_year must be equal to the future biennium year.