PeopleSoft Newsletter for Finance

TRANSACTION DETAIL QUERY

(SB_BDGTS_ACT_JRNL_LNS_FOR_COA)

This Query:  Provides detail for all budget and actual expenditure and encumbrance. When subtotaled this query should agree to the 020 report and when combined with using AP query and PO Report (or Query) and Requisition Query you should be able to reconcile your accounts.

Navigation in PS Financials:

Step 1.             From the Main Menu

GO>PROCESS FINANCIAL INFORMATION>REVIEW FINANCIAL INFORMATION

Step 2.             REVIEW FINANCIAL INFORMATION>VIEW>NAVIGATOR DISPLAY>QUERY

Step 3.             Query Name:    Click on Query Name & type SB then scroll down to SB_BDGTS_ACT_JRNL_LNS_FOR_COA (same process for SPEC_VAL query)

                                                Highlight Query Name

                                                Right Click on Mouse

Choose “Run to Excel” to run query to Excel for manipulation

Be Patient and wait for Enter Values Box

Step 4.             Enter Parameters or Values:

Business Unit – use down arrow and select your business unit (for most of campus it is SBCMP)

Fiscal Year – type in Fiscal Year you need (for example 200X)

Accounting period (From) – enter period

Accounting period (To) – enter same period (or ending period if you are looking at multiple periods)

DeptID – use your Dept ID

Account – enter account # (if you want all expense accounts enter 6%*)

Fund – type in Fund (example AD20X)

Class – enter a specific Class (if there is no class use the space bar or use % for all classes*)

Program – enter a specific Program (or use % for all programs*)

Project – enter a specific Project (if there is not project use the space bar or % for all projects*)

*TIP:  it is not recommended that you use % because you will get all the data for all departments, all programs, all classes, and all projects which makes it too difficult to sort and subtotal.  Therefore, it is recommended that you do one chartfield string at a time.

Click OK (again be patient and wait for data to return in the excel spreadsheet)

Excel Spreadsheet Manipulation

Step 1.             Highlight entire spreadsheet by clicking on gray box in upper left corner of the spreadsheet grid (it is the box to the left of the column A and above row 1).

Step 2.             On the Excel Menu bar go to>Data>Sort

                        The Sort dialog box will appear.

Step 3.             Sort by:            Source (this assumes you are only doing one specific chartfield string)

                        Then sort by: Journal

                        At the Sort dialog box find My list has Select the Header Row option

                        Click OK Repeat Step 1 to highlight the spreadsheet prior to going to step 4.

Step 4.             At the Menu bar go to >Data>Subtotals

                        Click Ok on the dialog box that appears on the screen

Step 5.             At each change in:            select Source

                        Use function:                        Sum

Add Subtotal to: the only box that should have a check mark in this area will be monetary amount everything else should be clear.

                        Replace Current Subtotals:            check for your first subtotal

                        Summary below data:               defaults to have a check in the box

                        Click OK

Step 6.             Now format for printing (delete blank columns, squeeze column widths, adjust to fit page, etc)

Step 7.             Compare the totals  to the 020 report.  The total in the actuals column on the query should agree  to the actuals and the encumbrance column on the 020 report. 

If not, follow up and if you  have any questions call the appropriate department.

                        Source equal to POS call purchasing

                        Source equal to GA, INT or CSH or SP call General Accounting