OracleAppsBlog

A day in the life of an Oracle Applications Consultant

Public Sector Budgeting (PSB)

Thursday, July 15, 2004

Pre-Validate PSB Worksheet Option now available for Oracle Position Budgeting

This post discussses the pre-validate worksheet option for position budgeting which is now available in the Define Worksheet Form.

Recently when defining a PSB Worksheet I noticed that on the Define Worksheet Form (Form Name: PSBWCDWS) there is now a "Pre-Validate" button. The button is positioned on the bottom left hand side of the form and can only be used if you have checked the "Budget by Position" checkbox and specified a Position Extract (refer to Fig.1). I was curious to know what this button was all about as I hadn’t noticed it before and after some investigation discovered that it was a new feature released as a result of an enhancement request per bug no. 324574 on Metalink.

Figure 1: The Define Worksheet Form in PSB with the “Pre-Validate” Button

The Pre-Validate worksheet option is meant to proactively validate position records before starting the worksheet creation process. In other words, before starting the worksheet creation process you can separately validate whether the positions have the correct salary information and distributions for the worksheet. Previously there was no option to perform a separate validation of the positions outside of the worksheet creation process. This meant that one would have to run a potentially long worksheet creation process to determine whether the position information was correct. 

By clicking the pre-validate button two concurrent requests are launched, namely: -

1. Validate Worksheet
2. Position Worksheet Exception Report (Validation Results Report)

The Position Worksheet Exception report lists all the problematic position records. After you have eliminated any potential problems with the position information you can then run the worksheet creation process.

In order to "install" the Pre-Validate Button you will need to load Minipack J for PSB which will deliver the new version (11.5.44) of the Define Worksheet form.

Saturday, July 10, 2004

Automating the creation of account code combinations (CCID’s) in Oracle General Ledger

This post outlines two methods one can use to automate the creation of account code combinations in General Ledger

In certain instances it may be necessary to manually create account code combinations (CCID’s) in Oracle General Ledger. To some extent this is determined by whether your organization has decided to use dynamic insert or not. Dynamic insert is an option you can turn on or off when creating your accounting flexfield. If the option is turned on it will allow account code combinations to be automatically generated. If dynamic insert is turned off then it will certainly be necessary for a General Ledger Super User to go and create an approved set of combinations for use within your organization. Even if dynamic insert is turned on you may find that at the beginning of an implementation that there are not enough active account combinations. This means you will more than likely have to manually add account code combinations as presently Oracle has no automated facility to perform this. To manually add account code combinations one should login under General Ledger Super User and on the navigation menu go to Setup > Account > Combinations. Having navigated to the form displayed in Fig. 1 you can then insert the needed lines.

Figure 1: The Account Code Combinations Form As manually adding account code combinations can be a rather tiresome job I investigated potential ways to automate the process and have come up with the following means of doing so: -

1. Journal Load via ADI

Using this option entails creating an excel spreadsheet journal via ADI with the account code combinations that you would like insert. The account code combinations should contain zero values. After uploading the spreadsheet journal from ADI an unposted journal will be created in General Ledger. The unposted journal can be deleted and the account code combinations that you created will remain behind. The reason for using the spreadsheet journal as opposed to using a journal in General Ledger is that the spreadsheet makes it easier to create the account code combinations you want to load as you can use the copy and paste features.

2. Use of DataLoad

A dataload template can be created and the account code combinations can be loaded directly into General Ledger.

Another tip

Personally I prefer to use the first method mentioned as it is much faster than using Dataload. Another word of advice when creating these combinations. One should ensure that you have correctly filled in the flexfield qualifiers (displayed in Fig. 2) when capturing your segment values as the account code combinations will use this information to determine how a CCID is created.

Figure 2: The Flexfield Segment Qualifiers

For example, if for any of the accounts in the combination of segment values, you have allow budgeting and allow posting set to “no”, then the account code combinations as displayed in Figure 1 will be created with allow budgeting and allow posting set to “no”. If you create an account code combination with the incorrect value for allow budgeting and allow posting, you can go and manually change the value. However, if you have a large number of lines that have the incorrect values you cannot merely go and upload another blank journal after having corrected the mistake in the flexfield segment qualifiers. You will still have to manually make the amendments as once an account code combination has been created you cannot “overwrite” it.

Lastly, if anyone has come up with any other better method or an automated procedure for performing this activity then please add your comments.

Monday, June 14, 2004

Understanding constraints (ceilings) in Oracle Public Sector Budgeting (PSB)

The objective of this post is to explain in more detail certain concepts relating to constraints. In particular I will be looking at thresholds and severity levels and explaining how they work.

According to the Oracle Public Sector Budgeting documentation, “Constraints are used to notify users regarding specific conditions for account ranges, elements, or position sets. For example, users can be notified if the total expense for a range of accounts exceeds a particular dollar amount.

Account constraints are used to prevent budget amount violations for line items.

Element constraints are used to prevent modification of element rates for a selected group of positions.

Position constraints are used to prevent element cost violations for selected positions or positions that are assigned to invalid element options”.

Constraints (otherwise known as ceilings in most of the Government organisations I have implemented in) are used to place limits on budget estimates. In Oracle Public Sector Budgeting, estimates are prepared in budget worksheets and after these estimates are prepared they are checked against constraints or ceilings that have been put in place by the relevant authorities.

The screen shot below shows the constraint setup screen in Oracle PSB. 

One of the concepts I struggled to understand was severity levels and thresholds and how these worked together. I felt that the Oracle documentation was a bit weak in this area and did not clearly define how these two settings worked together. In the next couple of paragraphs I will attempt to clarify how these “parameters” work.

Essentially two types of ceilings exist, namely: -

Hard Ceilings (can also be referred to as absolute)

This occurs is the threshold is less that or equal to the severity level. In the diagram shown, lines two (“General Fund”) and three (“Finance FTE”) would be classified as hard ceilings.

If a ceiling is hard, when a budget worksheet preparer submits a worksheet for review, a constraint violation will be produced and the user will be required to amend the violation to the worksheet and then re-submit it. Essentially, the worksheet will remain stuck with the preparer until such time as he ensures the constraint violation is rectified.

Soft Ceilings (can also be referred to as advisory)

This occurs if the threshold > severity level. In the diagram shown, line one (“Budget Dept”) would be classified as a soft ceiling.

In this instance, when a budget worksheet preparer submits a worksheet for review a constraint violation will be produced, however, the authoriser will still be able to work on the worksheet and post it to the General Ledger. Essentially, a warning message will be produced that there is a constraint violation but this will not stop the worksheet going through all the remaining processes needed to post it to the General Ledger.

Note: If the severity level is left blank then it is assumed to be less than the threshold level.

Saturday, June 12, 2004

Transfer of Budget Worksheet, Revision and Dossier transactions to the Oracle General Ledger

Having implemented Oracle Public Sector Budgeting at two fairly large sites, I’ve gained a good understanding of how Oracle’s Budgeting solution fits together.  This post aims to outline the different methods used to post Budget data from Public Sector Budgeting and OPSF(I) in to the General Ledger.

I felt the best way to explain how Oracle Budget data is posted into General Ledger would be by using a table as displayed below. The table outlines how budgeting data for a particular area of budgeting is posted in to the General Ledger and also identifies what journal sources and categories would be utilised. One of the main reasons for me writing this post is I felt that these procedures were not clearly outlined in the Oracle Documentation and where it was outlined it was in different manuals. I also get the feeling that the three main mechanisms of getting budget data into the General Ledger, namely worksheets, revisions and dossiers, were perhaps developed by different development teams or at different time periods.

This table I have prepared will consolidate the methodology for posting budget data relating to worksheets, revisions and dossiers into one place. My hope is that this will help someone else implementing the same modules who may be going through the same struggle as I initially went through.

Methodology for transferring Worksheets, Revisions and Dossiers to General Ledger