How to check budget fund for specific account - Oracle Query

Subject: Required to check fund is available for the specific account during oracle form validation or reporting level.

Solution: Following procedure will return the available fund amount & status (Y- Available, N-InSufficient Fund)

PLSQL Procedure:

create or replace PROCEDURE is_fund_available (p_gl_account        IN     VARCHAR2,
                                p_amount            IN     NUMBER,
                                p_date              IN     DATE,
                                p_module            IN     VARCHAR2,
                                px_fund_available      OUT NUMBER,
                                px_result              OUT VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      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;
      p_end_date                    DATE;
      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;
      x_msg_status                  VARCHAR2 (1000) := NULL;
      x_msg_data                    VARCHAR2 (100);
      p_ledger_id                   NUMBER := 2021;
      p_rei_curr_code               VARCHAR2 (10) := 'QAR';
      l_req_encumbrance_id          NUMBER;
      l_po_encumbrance_id           NUMBER;
      l_oblig_revresal_amount       NUMBER := 0;
      l_oblig_revresal_enucm_id     NUMBER := NULL;
      lx_funds_available_temp       NUMBER := 0;


      CURSOR get_period_year
      IS
         SELECT period_year
           FROM gl_periods
          WHERE p_date 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 = p_ledger_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 x
          WHERE x.concatenated_segments = TRIM (p_gl_account);
      EXCEPTION
         WHEN OTHERS
         THEN
            x_msg_status := x_msg_status || 'ERR:CCD';
      END;

      DBMS_OUTPUT.put_line (
         'Code combination id is ' || l_code_combination_id);
      DBMS_OUTPUT.put_line ('Set of books id is :' || p_ledger_id);

      BEGIN
         SELECT 1
           INTO ln_fund_exclusion
           FROM gl_budget_assignments
          WHERE     ledger_id = p_ledger_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;

      DBMS_OUTPUT.put_line ('Fund Exclusion:' || ln_fund_exclusion);

      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 (p_date, 'Mon-rr');
         EXCEPTION
            WHEN OTHERS
            THEN
               x_msg_status := x_msg_status || 'ERR:Period';
         --RAISE expense_exception;
         END;

         BEGIN
            SELECT currency_code
              INTO l_currency_code
              FROM gl_sets_of_books
             WHERE set_of_books_id = NVL (p_ledger_id, 1001);
         EXCEPTION
            WHEN OTHERS
            THEN
               l_currency_code := 'QAR';
         END;

         BEGIN
            SELECT budget_version_id
              INTO l_budget_version_id
              FROM gl_budgets_with_dates_v
             WHERE     set_of_books_id = NVL (p_ledger_id, 1001)
                   AND status != 'R'
                   AND status = 'C'
                   -- Status 'C' denotes the Current Active Budget
                   AND p_date BETWEEN start_date AND end_date;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_budget_version_id := 1000;                  --Approved Budget
         END;
   

         OPEN get_period_year;

         FETCH get_period_year INTO ln_period_year;

         CLOSE get_period_year;

         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 (p_ledger_id, 1001);
         EXCEPTION
            WHEN OTHERS
            THEN
               x_msg_status := x_msg_status || 'ERR:Closing status';

         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_ledger_id                  => p_ledger_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 (-1,  --l_encumbrance_type_id,
                                                    -1),                 ---1,
            x_req_encumbrance_id         => NULL, --1000,--l_req_encumbrance_id       ,
            x_po_encumbrance_id          => NULL, --1001,--l_po_encumbrance_id,
            x_budget                     => lx_budget,
            x_encumbrance                => lx_encumbrance,
            x_actual                     => lx_actual,
            x_funds_available            => lx_funds_available_temp,
            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 (
               'Amount Available (lx_funds_available_temp):'
            || lx_funds_available_temp);


         ------------------------

         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 ('From Currency is ' || l_currency_code);

         DBMS_OUTPUT.put_line ('To Currency is ' || l_rei_curr_code);

         IF (NVL (lx_funds_available_temp, 0) < NVL (p_amount, 0))
         THEN
            x_msg_data := 'Insufficient Funds';
            x_msg_status := 'N';
         ELSE
            x_msg_data := 'sufficient Funds';
            x_msg_status := 'Y';

         END IF;


      END IF;

      px_result := x_msg_status;
      px_fund_available := NVL (lx_funds_available_temp, 0);
   EXCEPTION
      WHEN OTHERS
      THEN
         x_msg_data := 'Insufficient Funds';
         DBMS_OUTPUT.put_line ('Error:' || SQLERRM);
         x_msg_status := 'insufficient fund3';
         px_result := x_msg_status;

   END is_fund_available;




----Testing PLSQL Script----

DECLARE
px_fund_available  NUMBER:=0;
px_result         VARCHAR2(1000):=NULL;
begin

is_fund_available('100.21000.120402.0000000.000.0002.000',0,'01-JUL-19',NULL,px_fund_available,px_result);
insert into temp_tbl values(1,px_fund_available);
insert into temp_tbl values(2,px_result);
commit;
end;

---Output---

You can able check fund for the specific account from oracle application screen.

Navigation: General Ledger Super User > Inquiry > Fund

You can query that specific account code for a specific period and check the funds available.