Funds Control

11i ‘Program – Restore Journals’ resolves GL Journals Stuck with Funds Reservation In Process

Posted on 13-Jan-11. Filed under: Funds Control | Tags: , , , |

Oracle released a new 11i concurrent program a few months back which is meant to resolve issues with GL Journals when the gl journal batch is perpetually stuck with Funds Reservation “In Process”.  I’ve dealt with this issue numerous times and from my experience, the issue normally has been caused by getting a connection/network hiccup while in the process of reserving Funds for a large GL Journal Entry.  Until now, a datafix has been required to fix these issues.  The datafix essentially clears out gl_bc_packets & gl_bc_packet_arrival_order of any fund reservation entries for the batch & sets the je batch back to an an unreserved status.  Then the user should take another shot at reserving the journal.

This datafix process can now be retired due to Patch 8975321 in 11i.  This Patch provides a new concurrent program ‘Program – Restore Journals’ as a replacement for the previously required datafix steps.  The program requires journal batch name as an input parameter and the program essentially executes the same steps as the datafix did. 

The My Oracle Support (MOS) note on this new functionality is:

GLXJEENT Funds Status Is Showing ‘In-Process’, Can Not Post Journal [ID 1141794.1]

It is also interesting to note that the original bug for this issue (944420: NEED A CONCURRENT PROCESS TO FIX JE AFTER FAILED FUNDS RESERVATIONS) dates back to 1999!!

Just wanted to share as this new functionality should save a good amount of time when encountering this issue going forward and mitigate the risk of having to run the datafix update.

Advertisements
Read Full Post | Make a Comment ( None so far )

11i10 Patch 8448145: Allows entry of FV Budget Execution Entries for Expired Appropriations

Posted on 11-Oct-10. Filed under: Budget Execution, Enhancements, Funds Control | Tags: , , , |

I wanted to share an enhancement that we got Oracle to deliver a ways back for USAF DEAMS & share that the patch is available for download.

We have a requirement to enter Budget Authority increases/decreases, even after Appropriations are expired.  This is because we need to move money around to cover various Expired Year adjustments necessary. I’ve seen other Federal organizations that have the same requirement, so wanted to pass this along. Until recently in 11i10, these adjustments could only be done via GL journals, since the FV Budget Execution functionality had a constraint to prevent Budget Execution entries against Expired Treasury Symbols. We had noticed this constraint had been removed from R12 Fed Financials and asked Oracle for the same capability in 11i10. They came through for us with patch 8448145 which removed the constraint and allows us to enter Funding Decreases/Increases through the standard Fed Admin Budget Execution form Functionality. To fully utilize the new capability, we then setup some new Budget Execution Transaction Types/Transaction Codes to use for Expired Funding Adjustments to drive the proper USSGL accounting for Expired Year transactions.  The removal of the constraint along with the set of Expired Approp TransTypes/TransCodes provided what the Budget Users needed to allow the standardized use of Fed Admin Budget Execution functionality for both current and expired funds.

This consistent, standardized Increase/Decrease adjustment method provides a one-stop shop for adjustments and eliminates the need of the manual journals for the expired year Budget Authority adjustments.  The elimination of the manual journal need is key, because it mitigates the risk of erroneous entries being created.  As of now, we’ve focused on utilizing the capability via the forms.  As time permits, we also want to explore if the constraint has also been removed from the FV Budget Execution Interface as well.

If your agency deals with this requirement/issue, I believe this functionality could benefit.

Read Full Post | Make a Comment ( None so far )

Validate updated version of FVBEFDCB.pls is installed to leverage ‘FV: Enable funds check / reservation based on the line level’ profile option functionality

Posted on 21-Nov-08. Filed under: Budget Execution, Funds Control | Tags: , , |

I’ve run into 2 instances just recently related to the profile option ‘FV: Enable funds check / reservation based on the line level’.  One is where we’re doing testing on my current USAF implementation (discovered the profile option wasn’t having any effect) and the 2nd was in helping a different agency through some year-end stuff.  I first learned about this profile option a while back in reviewing metalink note: 430836.1 titled “US Federal Financials Budget Execution – New Profile Options”.  This profile option is intended to control how the FV Budget Execution transactions create the Accounting Flexfield (AFF) values (aka GL code
combinations) for the Debit and Credit entries generated.  Here’s an explanation of what it does and the issue we seemed to have.

The Old School Way:

For background, the method I remember seeing historically for creating the Debit & Credit entries from the budget execution transactions was that both the DR & CR were created based on the AFF elements entered on the budget transaction line (table: fv.fv_be_trx_dtls).  So for example, the following would occur (just using a sample basic AFF of Fund, Org, USSGL, ObjClass):

Approp:

DR 0100.0.411901.0

CR 0100.0.445001.0

Apportionment:

DR 0100.0.445001.0

CR 0100.0.451001.0

Allotment:

DR 0100.A.451001.200

CR 0100.A.461001.200


Then subsequently, the funding is obligated:

DR 0100.A.461002.211

CR 0100.A.480101.211


For the above example, assume that funds are managed by a summary template where Fund = D, Org =D, USSGL = ALLOTMENT, ObjClass = OC_RG (a rollup group to control at the labor (100) vs non labor (200) summary balances.  This method creates a problem at year-end.  When the balances from 445001, 451001, 461001, 461002 all roll into 465001, then the balance in the 0100.0.465001.0 is positive (funds available), yet the balance in 0100.A.465001.200 is negative (deficient/overspent).  These funds balances need to be realigned back into the proper place if detailed level funds controlled is continued to be desired (i.e. below the fund level).

New way, achievable with FV: Enable funds check / reservation based on the line level set to ‘No’:

Utilizing the referenced profile option set to ‘No’, will enable the following behavior (notice the difference between the AFF values created in the Allotment Debit):

Approp:

DR 0100.0.411901.0

CR 0100.0.445001.0

Apportionment:

DR 0100.0.445001.0

CR 0100.0.451001.0

Allotment:

DR 0100.0.451001.0

CR 0100.A.461001.200

Then subsequently, the funding is obligated:

DR 0100.A.461002.211

CR 0100.A.480101.211

This option preserves keeping the funding in the proper distributions throughout the closing process.  It also provides additional flexibility for distributing and controlling funds in the budget execution process.  Note 430836.1 mentions this functionality is “highly recommended for US Federal Financials implementations using Budget Execution functionality.”

Supposedly, per the metalink docs and in looking at patch 4699472, this profile option and the updated FVBEFDCB.pls (115.28 2005/10/03) are delivered in the same patch.  However, we were seeing that the profile option existed, yet we had an older version of the package. If anyone else notices these are out of sync, suggest applying/reapplying patch 4699472 (4699472 is for 11i10 customers, other patches available for 11i8,11i9 customers).  There isn’t much on this patch, but Note: 341043.1 is the metalink reference note for it.  This patch provides an updated version of the Fv_Be_Fund_Pkg body (FVBEFDCB.pls).   This package does funds checking for the Budget Exection transactions and produces the AFF values to be used for funds checking in gl_bc_packets and eventual journal posting in gl.  Prior versions of this package don’t check against the profile option value, but this package introduces the condition to check against the profile option thus making the functionality available, i.e. it now includes the following:

IF
fnd_profile.value(‘FV_BE_FUNDS_CHK_LINE_LVL’) = ‘Y’ THEN

–Initialize dr arrays with values from document header

–Initialize cr arrays with values from document detail

–Modified the following line for bug# 3870382

–initialize_gl_segments(l_header_segments,l_gl_dr_segments);

initialize_gl_segments(l_detail_segments,l_gl_dr_segments);

ELSE

initialize_gl_segments(l_header_segments,l_gl_dr_segments);

END
IF;

initialize_gl_segments(l_detail_segments,l_gl_cr_segments);

Good Luck.

Read Full Post | Make a Comment ( None so far )

Treating Budgetary Accounts as Balance Sheet Accounts for Year-End Rollover

Posted on 23-Oct-08. Filed under: Funds Control, Year End | Tags: , |

In-case anyone might have missed it, Oracle has provided functionality to streamline the Federal year-end process a bit.  Oracle released patch 5200606 in June 2006 that allows for budgetary accounts to be automatically rolled over (just as asset & liability accounts have always been) as part of the year-end.  This mitigates the necessity of running the Carry Forward Budgetary Account Balances process or a Mass Allocation (what we had to use prior to the Carry Forward Budgetary Account Balances process existence), which essentially created a large journal of offsetting debits/credits in the first period (this is often has been setup as an adjusting period in the calendar definition) of each fiscal year (FY).  This new balance sheet treatment of budgetary accounts seems to save time and eliminates one of the year-end process steps.  I’m familiar with two agencies who have done this in production: National Endowment for Humanities (NEH) & Small Business Administration (SBA).  NEH seemed to be the first as they identified Bug 6620773 (fixed in patch 6753414).  I recently helped SBA just a bit with it for this latest year-end and it works well (there were a few other non-related issues that complicated things just a bit though!).  We are also planning to use this functionality for USAF DEAMS.  Documentation on the functionality/process is available in metalink notes: 373163.1 (http://tinyurl.com/5w3g6f), 373649.1 (http://tinyurl.com/5qnmlc), the above Bug/Patch & the R12 Fed Financials User Guide speaks a little bit to it.

The effort to update Fed Financials to utilize the new process is actually fairly simple.  Upon patch application and prior to opening the first period of the next FY, run the GL Concurrent request: ‘Program – Track Budgetary Debit/Credit Accounts as Balance Sheet Accounts’ for the set of books.  After this concurrent request is run, when opening the first period of the next FY, the budgetary account balances will be automatically established.  Furthermore, the old Carry Forward Budgetary Account Balances can never be run again.

Required Summary Template Changes

While the process to initialize this functionality is pretty straight forward, there is an impact to the summary account balances based on using this functionality when transitioning from the old method.  Traditionally, summary templates for budgetary funds control are often setup with an Amount Type of ‘Year to Date’.  This worked well because the carry forward journal would always re-establish any 4000 account balances (e.g. 4650) in the current year, thus the YTD accounts would always have enough funding.  By eliminating the carry forward posting, if summary accounts are still set to YTD, the accounts don’t include remaining availability from the prior FY.  The solution to this issue is to update the summary templates to use an Amount Type of ‘Project to Date’.  Project to Date (PJTD) essentially means Inception to Date.  This will allow cumulative remaining balances at the end of one FY to be available in the next FY.

Set amount type to: Project to Date

However, setting the summary templates to PJTD can also result in an issue that some funds available balances will be inflated.  This is the case for customers who have previously used the Carry Forward Budgetary Account Balances or Mass Allocation processes to re-establish the balances for each prior year.  This issue exists where the earliest period of the summary template definition is prior to the last carry forward period.  Since PJTD will include 1) the initial establishment of the funds balances as well as 2) the re-establishment of remaining funds available balances as part of each carry forward, the PJTD balances will be inflated by the sum of the amounts re-established.  For example, if the earliest period of a template is set to 2002 and remaining 2004 funding was carried over at each year-end  (2005, 2006,2007,2008), the funds available balances would be inflated due to the carry over in each of the 4 years after the initial appropriation year.  A solution to this issue is to update the earliest period of the template definition to the last time the Carry Forward Budgetary Account Balances ran/was posted.  This will update the summary account balances to just include activity since the earliest period defined. This is well defined in the GL User Guide: “Enter the Earliest Period for which you want General Ledger to maintain your actual, encumbrance and budget summary account balances. General Ledger maintains summary account balances for this accounting period and for subsequent periods.”    So if an agency is looking to implement the new functionality effective FY 2009, then prior to beginning 2009 activity, the summary templates’ earliest period should be updated to OCT-BEG-2008 (or whatever is the first period in 2008 (likely an adjusting period) where the 2007 carry forward journal was posted to.

Update Earliest Period to: The period corresponding to the last time the Carry Forward Budgetary Account Balances ran/was posted.

Another consideration in making this change is that if Funds Availability FSGs or custom reports are built to look at YTD summary account balances (such as using some of the YTD-Actual seeded FSG column sets), those would likely need to be updated to refer to Project-Actual.

Update 9 Nov 2008: Seems we’re still having issues with the excess balances even with the Earliest Period change suggestion above.  Need to figure out what’s going, but that may not be the total solution.

Read Full Post | Make a Comment ( 3 so far )

11i10 FV Status of Funds Availability Patch 7327551 Corrects Inconsistent Results

Posted on 2-Oct-08. Filed under: Funds Control | Tags: , |

For those out there who have been wanting to utilize the Fed Admin Status of Funds Availability Reports more (Concurrent Program Name: Status of Funds Availability Process), but have been frustrated by inconsistent results, we recently worked with Oracle Support to resolve some issues around the inconsistencies. As a result, Oracle recently published bug/patch 7327551 to correct two key issues we identified. For clarification, we worked with the “Available Balances” report available as part of this process.

The behavior we were seeing was:

Problem #1: If we ran the report multiple times back to back we got incorrect results. This seemed indicative that the process was not completely clearing itself out after each run. Honestly we didn’t dive too deep into the code to see exactly why, we left it up to Oracle to fix (isn’t that why they get those support fees!), but it seemed that the process wasn’t clearing out a temp table (or something of that sort) for each run and the leftover data was also included in subsequent outputs. For example, if we ran the same report 3 times back to back to back with the exact same parameters, we found that:

1. The first report output displayed a correct appropriation balance.
2. The second report output displayed the amount of the appropriation balance doubled.
3. The third report output didn’t show any results. i.e. The report displayed *End of Report* and did not print out the accounting flexfield elements or the appropriation totals.

We uploaded examples of 3 report outputs to demonstrate this issue, which Oracle was able to quickly replicate following our instructions.

Problem #2: If multiple users run the report at the same time, the report printed out results from other users’ concurrent request results. There’s nothing quite like entering parameters such as fund or whatever and then receiving an output that doesn’t even reflect the parameters entered. To prove out this issue, we had multiple users simultaneously submitting the requests and sharing the outputs. Again, Oracle was also able to quickly replicate the issue, which I’m sure was tied to the first problem.

The issues were referred to development and we got a patch back to test in one week’s time. Our testing has been successful and the patch definitely seems to have solved the issues. I’d also like to point out that these reports are Real-Time, so if FSGs or something more gl_balance-based is being used, these reports can now give more accurate results. They utilize similar logic as the Funds Available Inquiry form (Fed Admin: Inquiry>Funds Available), by combining posted balances from gl_balances and unposted balances from gl_bc_packets.

Hope this helps. If these reports aren’t being used, they’re relatively easy to setup for someone who has a decent understanding of what the USSGLs represent.  Thanks to Leslie Steinkamp, a co-worker of mine at CapCity, for helping to tag-team on solving these issues.


Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...