GL Funds check functionality

 

 

Filed Under  Actual, Advisory, Budget, calc_funds, code combination, Encumbrance, Funds, General ledger, gl_funds_available_pkg, Year To Date, YTD

 

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301

CREATE OR REPLACE PROCEDURE is_fund_available (    p_segment1        IN       VARCHAR2,    p_segment2        IN       VARCHAR2,    p_segment3        IN       VARCHAR2,    p_segment4        IN       VARCHAR2,    p_segment5        IN       VARCHAR2,    p_segment6        IN       VARCHAR2,    p_org_id          IN       NUMBER,    p_amount          IN       VARCHAR2,    p_rei_curr_code   IN       VARCHAR2 DEFAULT 'AED',    x_msg_data        OUT      VARCHAR2,    x_msg_status      OUT      VARCHAR2 ) IS    lx_budget                     NUMBER;    lx_encumbrance                NUMBER;    lx_actual                     NUMBER;    lx_funds_available            NUMBER;    lx_req_encumbrance_amount     NUMBER;    lx_po_encumbrance_amount      NUMBER;    lx_other_encumbrance_amount   NUMBER;    l_conversion_rate             NUMBER;    l_encumbrance_type_id         NUMBER;    l_closing_status              VARCHAR2 (2);    l_set_of_books_id             NUMBER;    l_period_name                 VARCHAR2 (30);    l_period_set_name             VARCHAR2 (50);    l_period_type                 VARCHAR2 (30);    l_period_num                  VARCHAR2 (30);    l_quarter_num                 VARCHAR2 (30);    l_period_year                 VARCHAR2 (30);    l_currency_code               VARCHAR2 (10);    l_budget_version_id           NUMBER;    l_code_combination_id         NUMBER;    lx_funds_available_usd        NUMBER;    ln_fund_exclusion             NUMBER;    expense_exception             EXCEPTION;    l_rei_curr_code               VARCHAR2 (10);    ln_period_year                gl_periods.period_year%TYPE;    lc_period_name                gl_periods.period_year%TYPE;      CURSOR get_period_year    IS       SELECT period_year         FROM gl_periods        WHERE TRUNC (SYSDATE) BETWEEN start_date AND end_date;      CURSOR get_last_period    IS       SELECT   gp.period_type, gsob.period_set_name, gp.period_name, gp.period_num, gp.quarter_num, gp.period_year, gp.start_date,                gp.end_date, gp.year_start_date           FROM gl_sets_of_books gsob, gl_periods gp          WHERE gsob.period_set_name = gp.period_set_name            AND gp.period_year = ln_period_year            AND gsob.set_of_books_id = l_set_of_books_id            AND adjustment_period_flag != 'Y'       ORDER BY period_num;      lcu_get_last_period           get_last_period%ROWTYPE; BEGIN    BEGIN       SELECT code_combination_id         INTO l_code_combination_id         FROM gl_code_combinations_kfv        WHERE segment1 = p_segment1          AND segment2 = p_segment2          AND segment3 = p_segment3          AND segment4 = p_segment4          AND segment5 = p_segment5          AND segment6 = p_segment6;    EXCEPTION       WHEN OTHERS       THEN          x_msg_status := 'N';          RAISE expense_exception;    END;      DBMS_OUTPUT.put_line ('Code combination id is ' || l_code_combination_id);      BEGIN       SELECT DISTINCT set_of_books_id                  INTO l_set_of_books_id                  FROM org_organization_definitions                 WHERE operating_unit = p_org_id;    EXCEPTION       WHEN OTHERS       THEN          x_msg_status := 'N';          RAISE expense_exception;    END;      DBMS_OUTPUT.put_line ('Set of books id is ' || l_set_of_books_id);      BEGIN       SELECT 1         INTO ln_fund_exclusion         FROM gl_budget_assignments        WHERE set_of_books_id = l_set_of_books_id          AND code_combination_id = l_code_combination_id          AND amount_type = 'YTD'          AND funds_check_level_code = 'B';    EXCEPTION       WHEN NO_DATA_FOUND       THEN          ln_fund_exclusion := 0;       WHEN OTHERS       THEN          ln_fund_exclusion := 0;    END;      IF NVL (ln_fund_exclusion, 0) = 1    THEN       BEGIN          SELECT period_name, period_set_name, period_type, period_num, quarter_num, period_year            INTO l_period_name, l_period_set_name, l_period_type, l_period_num, l_quarter_num, l_period_year            FROM gl_periods           WHERE period_name = TO_CHAR (SYSDATE, 'Mon-rr');       EXCEPTION          WHEN OTHERS          THEN             x_msg_status := 'N';             RAISE expense_exception;       END;         BEGIN          SELECT currency_code            INTO l_currency_code            FROM gl_sets_of_books           WHERE set_of_books_id = NVL (l_set_of_books_id, 1001);       EXCEPTION          WHEN OTHERS          THEN             l_currency_code := 'AED';       END;         BEGIN          SELECT budget_version_id            INTO l_budget_version_id            FROM gl_budgets_with_dates_v           WHERE set_of_books_id = NVL (l_set_of_books_id, 1001) AND status != 'R' AND status = 'C'                 -- Status 'C' denotes the Current Active Budget                 AND SYSDATE BETWEEN start_date AND end_date;       EXCEPTION          WHEN OTHERS          THEN             l_budget_version_id := 1000;   --Approved Budget       END;         BEGIN          SELECT encumbrance_type_id   --  ,encumbrance_type            INTO l_encumbrance_type_id            FROM gl_all_enc_types_view           WHERE encumbrance_type = 'ALL';       EXCEPTION          WHEN OTHERS          THEN             l_encumbrance_type_id := -1;       END;         /*       Amount Type as in Funds Available inquiry from       YTDE - Year To date extended       QTDE - Quarter to date extended       PTD - Period_to Date       PJTD - Project to Date       */       /* Below code is added for the Budget Validation*/       OPEN get_period_year;         FETCH get_period_year        INTO ln_period_year;         CLOSE get_period_year;         IF ln_period_year IS NOT NULL       THEN          OPEN get_last_period;            LOOP             FETCH get_last_period              INTO lcu_get_last_period;               EXIT WHEN get_last_period%NOTFOUND;          END LOOP;            CLOSE get_last_period;       END IF;         BEGIN          SELECT closing_status            INTO l_closing_status            FROM gl_period_statuses           WHERE period_name = l_period_name AND application_id = 101 AND set_of_books_id = NVL (l_set_of_books_id, 1001);       EXCEPTION          WHEN OTHERS          THEN             x_msg_status := 'N';             RAISE expense_exception;       END;         DBMS_OUTPUT.put_line ('Last Period Name ' || lcu_get_last_period.period_name);       DBMS_OUTPUT.put_line ('Period Closing status ' || l_closing_status);         l_period_type := lcu_get_last_period.period_type;       l_period_set_name := lcu_get_last_period.period_set_name;       l_period_name := lcu_get_last_period.period_name;       l_period_num := lcu_get_last_period.period_num;       l_quarter_num := lcu_get_last_period.quarter_num;       l_period_year := lcu_get_last_period.period_year;         /* Budget Validation ends here */       gl_funds_available_pkg.calc_funds (x_amount_type                 => 'YTDE',                                          x_code_combination_id         => l_code_combination_id,                                          x_account_type                => 'A',                                          x_template_id                 => NULL,                                          x_set_of_books_id             => l_set_of_books_id,                                          x_currency_code               => l_currency_code,                                          x_po_install_flag             => 'Y',                                          x_accounted_period_type       => l_period_type,                                          x_period_set_name             => l_period_set_name,                                          x_period_name                 => l_period_name,                                          x_period_num                  => l_period_num,                                          x_quarter_num                 => l_quarter_num,                                          x_period_year                 => l_period_year,                                          x_closing_status              => l_closing_status,                                          x_budget_version_id           => l_budget_version_id,                                          x_encumbrance_type_id         => NVL (l_encumbrance_type_id, -1),                                          x_req_encumbrance_id          => NULL,                                          x_po_encumbrance_id           => NULL,                                          x_budget                      => lx_budget,                                          x_encumbrance                 => lx_encumbrance,                                          x_actual                      => lx_actual,                                          x_funds_available             => lx_funds_available,                                          x_req_encumbrance_amount      => lx_req_encumbrance_amount,                                          x_po_encumbrance_amount       => lx_po_encumbrance_amount,                                          x_other_encumbrance_amount    => lx_other_encumbrance_amount                                         );         DBMS_OUTPUT.put_line ('Budget amount is ' || lx_budget);       DBMS_OUTPUT.put_line ('Encumbrance amount is ' || lx_encumbrance);       DBMS_OUTPUT.put_line ('Actual amount is ' || lx_actual);       DBMS_OUTPUT.put_line ('Funds Available is ' || lx_funds_available);         l_rei_curr_code := RTRIM (LTRIM (SUBSTR (p_rei_curr_code,                                                INSTR (p_rei_curr_code,                                                       ' ',                                                       -1                                                      ),                                                LENGTH (p_rei_curr_code)                                               )));       DBMS_OUTPUT.put_line ('From Currency is ' || l_currency_code);       DBMS_OUTPUT.put_line ('To Currency is ' || l_rei_curr_code);         IF l_rei_curr_code != l_currency_code       THEN          BEGIN             SELECT conversion_rate               INTO l_conversion_rate               FROM gl_daily_rates              WHERE from_currency = l_currency_code                AND to_currency = l_rei_curr_code                AND conversion_type = 'Corporate'                AND TRUNC (conversion_date) = TRUNC (SYSDATE);               IF l_conversion_rate IS NOT NULL             THEN                lx_funds_available_usd := lx_funds_available * l_conversion_rate;             END IF;          EXCEPTION             WHEN OTHERS             THEN                x_msg_status := 'N';                DBMS_OUTPUT.put_line (   'Corporate conversion rate from '                                      || l_currency_code                                      || ' to '                                      || l_rei_curr_code                                      || ' is not available. Using conversion rate 1.');                RAISE expense_exception;          END;       ELSE          lx_funds_available_usd := lx_funds_available;       END IF;         IF (NVL (lx_funds_available_usd, 0) < NVL (p_amount, 0))       THEN          x_msg_data := 'Insufficient Funds';          x_msg_status := 'N';       ELSE          x_msg_data := NULL;          x_msg_status := 'Y';       END IF;    ELSE       x_msg_data := NULL;       x_msg_status := 'Y1';    END IF; EXCEPTION    WHEN OTHERS    THEN       x_msg_data := 'Insufficient Funds';       x_msg_status := 'N'; END is_fund_available;

Test the procedure
You can use the following SQL to execute the procedure and test it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DECLARE    v_msg_data     VARCHAR2 (1000);    v_msg_status   VARCHAR2 (1000); BEGIN    is_fund_available (p_segment1                    => '11',                       p_segment2                    => '193',                       p_segment3                    => '673',                       p_segment4                    => '5653',                       p_segment5                    => '0000',                       p_segment6                    => '000',                       p_org_id                      => 3376,                       p_amount                      => 2000,                       p_rei_curr_code               => 'AED',                       x_msg_data                    => v_msg_data,                       x_msg_status                  => v_msg_status                      ); END;

Output

1 2 3 4 5 6 7 8 9 10

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