General Ledger

GL Funds check functionality

By Jag - August 16, 2012
If Budgets are enabled in General Ledger then several transactions will check for budget availability before the transaction is completed. These transactions are mainly in Payables, Purchasing, Projects modules. The idea is to check whether an account has the respective funds before any outflow from the account can take place.
The transactions are Payables Invoices, Expense reports, Purchase Orders, Projects etc. I have illustrated below how a user can check the available funds through the front end as well as how we can incorporate funds check from the back end, if we need to add the check into a custom component.
  
From the front end: Checking for available fund

Login to Oracle
Responsibility: GL responsibility
Navigation: Budgets > Define > Organization
Open the form and click on Ctrl+F11 to query for all the budget organizations. Generally 1 set of book contains 1 budget organization.
Click on Assignments button
Now all the accounts status is shown. We shall query for a single account, 11.193.673.5653.0000.000.
We shall check the funds for this account shortly.
Note: Budgetary control is based on the Funds check level on an account. There are 3 options for Funds check level.


Funds Check Level
Budgetary Control
Absolute
Enabled
Advisory
Enabled
None
Disabled
Check the funds available for a particular account

Responsibility: GL responsibility
Navigation: Inquiry > Funds
Modify the header parameters as per the requirement and click on Account box in the detail section.
Enter the account range
Press OK to see the accounts
Now the amounts are displayed for the account for funds availability, budget, encumbrance.
Note:
Calculation for Funds availability

As per the previous form, to get the amount of Funds Available field the calculation goes like this,

Funds Available = Budget – Encumbrance – Actual
From the database: Funds check procedure

001CREATE OR REPLACE PROCEDURE is_fund_available (
002   p_segment1        IN       VARCHAR2,
003   p_segment2        IN       VARCHAR2,
004   p_segment3        IN       VARCHAR2,
005   p_segment4        IN       VARCHAR2,
006   p_segment5        IN       VARCHAR2,
007   p_segment6        IN       VARCHAR2,
008   p_org_id          IN       NUMBER,
009   p_amount          IN       VARCHAR2,
010   p_rei_curr_code   IN       VARCHAR2 DEFAULT 'AED',
011   x_msg_data        OUT      VARCHAR2,
012   x_msg_status      OUT      VARCHAR2
013)
014IS
015   lx_budget                     NUMBER;
016   lx_encumbrance                NUMBER;
017   lx_actual                     NUMBER;
018   lx_funds_available            NUMBER;
019   lx_req_encumbrance_amount     NUMBER;
020   lx_po_encumbrance_amount      NUMBER;
021   lx_other_encumbrance_amount   NUMBER;
022   l_conversion_rate             NUMBER;
023   l_encumbrance_type_id         NUMBER;
024   l_closing_status              VARCHAR2 (2);
025   l_set_of_books_id             NUMBER;
026   l_period_name                 VARCHAR2 (30);
027   l_period_set_name             VARCHAR2 (50);
028   l_period_type                 VARCHAR2 (30);
029   l_period_num                  VARCHAR2 (30);
030   l_quarter_num                 VARCHAR2 (30);
031   l_period_year                 VARCHAR2 (30);
032   l_currency_code               VARCHAR2 (10);
033   l_budget_version_id           NUMBER;
034   l_code_combination_id         NUMBER;
035   lx_funds_available_usd        NUMBER;
036   ln_fund_exclusion             NUMBER;
037   expense_exception             EXCEPTION;
038   l_rei_curr_code               VARCHAR2 (10);
039   ln_period_year                gl_periods.period_year%TYPE;
040   lc_period_name                gl_periods.period_year%TYPE;
041
042   CURSOR get_period_year
043   IS
044      SELECT period_year
045        FROM gl_periods
046       WHERE TRUNC (SYSDATE) BETWEEN start_date AND end_date;
047
048   CURSOR get_last_period
049   IS
050      SELECT   gp.period_type, gsob.period_set_name, gp.period_name, gp.period_num, gp.quarter_num, gp.period_year, gp.start_date,
051               gp.end_date, gp.year_start_date
052          FROM gl_sets_of_books gsob, gl_periods gp
053         WHERE gsob.period_set_name = gp.period_set_name
054           AND gp.period_year = ln_period_year
055           AND gsob.set_of_books_id = l_set_of_books_id
056           AND adjustment_period_flag != 'Y'
057      ORDER BY period_num;
058
059   lcu_get_last_period           get_last_period%ROWTYPE;
060BEGIN
061   BEGIN
062      SELECT code_combination_id
063        INTO l_code_combination_id
064        FROM gl_code_combinations_kfv
065       WHERE segment1 = p_segment1
066         AND segment2 = p_segment2
067         AND segment3 = p_segment3
068         AND segment4 = p_segment4
069         AND segment5 = p_segment5
070         AND segment6 = p_segment6;
071   EXCEPTION
072      WHEN OTHERS
073      THEN
074         x_msg_status := 'N';
075         RAISE expense_exception;
076   END;
077
078   DBMS_OUTPUT.put_line ('Code combination id is ' || l_code_combination_id);
079
080   BEGIN
081      SELECT DISTINCT set_of_books_id
082                 INTO l_set_of_books_id
083                 FROM org_organization_definitions
084                WHERE operating_unit = p_org_id;
085   EXCEPTION
086      WHEN OTHERS
087      THEN
088         x_msg_status := 'N';
089         RAISE expense_exception;
090   END;
091
092   DBMS_OUTPUT.put_line ('Set of books id is ' || l_set_of_books_id);
093
094   BEGIN
095      SELECT 1
096        INTO ln_fund_exclusion
097        FROM gl_budget_assignments
098       WHERE set_of_books_id = l_set_of_books_id
099         AND code_combination_id = l_code_combination_id
100         AND amount_type = 'YTD'
101         AND funds_check_level_code = 'B';
102   EXCEPTION
103      WHEN NO_DATA_FOUND
104      THEN
105         ln_fund_exclusion := 0;
106      WHEN OTHERS
107      THEN
108         ln_fund_exclusion := 0;
109   END;
110
111   IF NVL (ln_fund_exclusion, 0) = 1
112   THEN
113      BEGIN
114         SELECT period_name, period_set_name, period_type, period_num, quarter_num, period_year
115           INTO l_period_name, l_period_set_name, l_period_type, l_period_num, l_quarter_num, l_period_year
116           FROM gl_periods
117          WHERE period_name = TO_CHAR (SYSDATE, 'Mon-rr');
118      EXCEPTION
119         WHEN OTHERS
120         THEN
121            x_msg_status := 'N';
122            RAISE expense_exception;
123      END;
124
125      BEGIN
126         SELECT currency_code
127           INTO l_currency_code
128           FROM gl_sets_of_books
129          WHERE set_of_books_id = NVL (l_set_of_books_id, 1001);
130      EXCEPTION
131         WHEN OTHERS
132         THEN
133            l_currency_code := 'AED';
134      END;
135
136      BEGIN
137         SELECT budget_version_id
138           INTO l_budget_version_id
139           FROM gl_budgets_with_dates_v
140          WHERE set_of_books_id = NVL (l_set_of_books_id, 1001) AND status != 'R' AND status = 'C'
141                -- Status 'C' denotes the Current Active Budget
142                AND SYSDATE BETWEEN start_date AND end_date;
143      EXCEPTION
144         WHEN OTHERS
145         THEN
146            l_budget_version_id := 1000;   --Approved Budget
147      END;
148
149      BEGIN
150         SELECT encumbrance_type_id   --  ,encumbrance_type
151           INTO l_encumbrance_type_id
152           FROM gl_all_enc_types_view
153          WHERE encumbrance_type = 'ALL';
154      EXCEPTION
155         WHEN OTHERS
156         THEN
157            l_encumbrance_type_id := -1;
158      END;
159
160      /*
161      Amount Type as in Funds Available inquiry from
162      YTDE - Year To date extended
163      QTDE - Quarter to date extended
164      PTD - Period_to Date
165      PJTD - Project to Date
166      */
167      /* Below code is added for the Budget Validation*/
168      OPEN get_period_year;
169
170      FETCH get_period_year
171       INTO ln_period_year;
172
173      CLOSE get_period_year;
174
175      IF ln_period_year IS NOT NULL
176      THEN
177         OPEN get_last_period;
178
179         LOOP
180            FETCH get_last_period
181             INTO lcu_get_last_period;
182
183            EXIT WHEN get_last_period%NOTFOUND;
184         END LOOP;
185
186         CLOSE get_last_period;
187      END IF;
188
189      BEGIN
190         SELECT closing_status
191           INTO l_closing_status
192           FROM gl_period_statuses
193          WHERE period_name = l_period_name AND application_id = 101 AND set_of_books_id = NVL (l_set_of_books_id, 1001);
194      EXCEPTION
195         WHEN OTHERS
196         THEN
197            x_msg_status := 'N';
198            RAISE expense_exception;
199      END;
200
201      DBMS_OUTPUT.put_line ('Last Period Name ' || lcu_get_last_period.period_name);
202      DBMS_OUTPUT.put_line ('Period Closing status ' || l_closing_status);
203
204      l_period_type := lcu_get_last_period.period_type;
205      l_period_set_name := lcu_get_last_period.period_set_name;
206      l_period_name := lcu_get_last_period.period_name;
207      l_period_num := lcu_get_last_period.period_num;
208      l_quarter_num := lcu_get_last_period.quarter_num;
209      l_period_year := lcu_get_last_period.period_year;
210
211      /* Budget Validation ends here */
212      gl_funds_available_pkg.calc_funds (x_amount_type                 => 'YTDE',
213                                         x_code_combination_id         => l_code_combination_id,
214                                         x_account_type                => 'A',
215                                         x_template_id                 => NULL,
216                                         x_set_of_books_id             => l_set_of_books_id,
217                                         x_currency_code               => l_currency_code,
218                                         x_po_install_flag             => 'Y',
219                                         x_accounted_period_type       => l_period_type,
220                                         x_period_set_name             => l_period_set_name,
221                                         x_period_name                 => l_period_name,
222                                         x_period_num                  => l_period_num,
223                                         x_quarter_num                 => l_quarter_num,
224                                         x_period_year                 => l_period_year,
225                                         x_closing_status              => l_closing_status,
226                                         x_budget_version_id           => l_budget_version_id,
227                                         x_encumbrance_type_id         => NVL (l_encumbrance_type_id, -1),
228                                         x_req_encumbrance_id          => NULL,
229                                         x_po_encumbrance_id           => NULL,
230                                         x_budget                      => lx_budget,
231                                         x_encumbrance                 => lx_encumbrance,
232                                         x_actual                      => lx_actual,
233                                         x_funds_available             => lx_funds_available,
234                                         x_req_encumbrance_amount      => lx_req_encumbrance_amount,
235                                         x_po_encumbrance_amount       => lx_po_encumbrance_amount,
236                                         x_other_encumbrance_amount    => lx_other_encumbrance_amount
237                                        );
238
239      DBMS_OUTPUT.put_line ('Budget amount is ' || lx_budget);
240      DBMS_OUTPUT.put_line ('Encumbrance amount is ' || lx_encumbrance);
241      DBMS_OUTPUT.put_line ('Actual amount is ' || lx_actual);
242      DBMS_OUTPUT.put_line ('Funds Available is ' || lx_funds_available);
243
244      l_rei_curr_code := RTRIM (LTRIM (SUBSTR (p_rei_curr_code,
245                                               INSTR (p_rei_curr_code,
246                                                      ' ',
247                                                      -1
248                                                     ),
249                                               LENGTH (p_rei_curr_code)
250                                              )));
251      DBMS_OUTPUT.put_line ('From Currency is ' || l_currency_code);
252      DBMS_OUTPUT.put_line ('To Currency is ' || l_rei_curr_code);
253
254      IF l_rei_curr_code != l_currency_code
255      THEN
256         BEGIN
257            SELECT conversion_rate
258              INTO l_conversion_rate
259              FROM gl_daily_rates
260             WHERE from_currency = l_currency_code
261               AND to_currency = l_rei_curr_code
262               AND conversion_type = 'Corporate'
263               AND TRUNC (conversion_date) = TRUNC (SYSDATE);
264
265            IF l_conversion_rate IS NOT NULL
266            THEN
267               lx_funds_available_usd := lx_funds_available * l_conversion_rate;
268            END IF;
269         EXCEPTION
270            WHEN OTHERS
271            THEN
272               x_msg_status := 'N';
273               DBMS_OUTPUT.put_line (   'Corporate conversion rate from '
274                                     || l_currency_code
275                                     || ' to '
276                                     || l_rei_curr_code
277                                     || ' is not available. Using conversion rate 1.');
278               RAISE expense_exception;
279         END;
280      ELSE
281         lx_funds_available_usd := lx_funds_available;
282      END IF;
283
284      IF (NVL (lx_funds_available_usd, 0) < NVL (p_amount, 0))
285      THEN
286         x_msg_data := 'Insufficient Funds';
287         x_msg_status := 'N';
288      ELSE
289         x_msg_data := NULL;
290         x_msg_status := 'Y';
291      END IF;
292   ELSE
293      x_msg_data := NULL;
294      x_msg_status := 'Y1';
295   END IF;
296EXCEPTION
297   WHEN OTHERS
298   THEN
299      x_msg_data := 'Insufficient Funds';
300      x_msg_status := 'N';
301END is_fund_available;
Test the procedure
You can use the following SQL to execute the procedure and test it.
01DECLARE
02   v_msg_data     VARCHAR2 (1000);
03   v_msg_status   VARCHAR2 (1000);
04BEGIN
05   is_fund_available (p_segment1                    => '11',
06                      p_segment2                    => '193',
07                      p_segment3                    => '673',
08                      p_segment4                    => '5653',
09                      p_segment5                    => '0000',
10                      p_segment6                    => '000',
11                      p_org_id                      => 3376,
12                      p_amount                      => 2000,
13                      p_rei_curr_code               => 'AED',
14                      x_msg_data                    => v_msg_data,
15                      x_msg_status                  => v_msg_status
16                     );
17END;
Output

01Code combination id is 892868
02Set of books id is 16004
03Last Period Name Dec-12
04Budget amount is 23361.3
05Encumbrance amount is 0
06Actual amount is 4458
07Funds Available is 18903.3
08From Currency is RUB
09To Currency is AED
10Corporate conversion rate from RUB to AED is not available. Using conversion rate 1.

  • Share:

You Might Also Like

0 comments