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.

How to define event-based Oracle Alert on Custom Tables Insert/Update

Subject: Required to send a notification when data Insert/Update on a custom table.

Oracle Alert Drawback: As per the oracle standards, we cannot enable oracle event-based alert on the custom table directly.

Solution:

Step 1: Required to register your custom table into oracle apps.

-                  Custom table name: MWANI_MARINE_TBL

Table Definition:

-        We have required to register custom table using Oracle API as from Oracle Application, we can not able to register custom table to APPS.

-        We have required to register table in Receivable (AR) application.

PLSQL Code:

begin

ad_dd.register_table

(p_appl_short_name => 'AR',    --Application name in which you want to register

p_tab_name      =>'MWANI_MARINE_TBL', --Table Name

p_tab_type      =>'T',   -- T for Transaction data , S for seeded data

p_next_extent     =>512,   -- default 512

p_pct_free        =>10,   -- Default 10

p_pct_used        =>70 --Default 70

);

commit;

end;

Step 2: Required to register all custom table column into oracle apps using Oracle API.

-         As in custom table having many columns, We have designed temp table where I will insert all column configuration as per the below screenshots, based on that column configuration details we will register all column using Oracle API.

Temporary Table Data:

Temp Table Definition: (Temporary Table Name: XX_TEMP_TBL)

create table XX_TEMP_TBL

(SEQ_NO  NUMBER,

COL_NAME VARCHAR2(240),

COL_TYPE VARCHAR2(240),

COL_WIDTH NUMBER,

TABLE_NAME VARCHAR2(240)

);

PLSQL Code for Oracle API for register Table Column in Oracle Apps:

declare

CURSOR C_MARINE_DTLS

IS

select *from XX_TEMP_TBL where table_name='MWANI_MARINE_TBL';

begin

begin

      mo_global.init ('AR');

      mo_global.set_policy_context ('S', 81);

      fnd_global.apps_initialize (fnd_profile.value('user_id'),fnd_profile.value('resp_id') ,fnd_profile.value('resp_appl_id'));

      commit;

end;

FOR y IN C_MARINE_DTLS

LOOP

     

    ad_dd.register_column

    (p_appl_short_name =>'AR',

    p_tab_name      =>'MWANI_MARINE_TBL',

    p_col_name     =>trim(y.COL_NAME),

    p_col_seq      =>trim(y.SEQ_NO),

    p_col_type     =>trim(y.COL_TYPE),

    p_col_width    =>trim(y.COL_WIDTH),

    p_nullable        =>'Y',

    p_translate       =>'N',

    p_precision       => null,

    p_scale           =>null

    );

    commit;

 END LOOP;

end;

Step 3: Once data submitted. Required to check this table registered in oracle application or not.

-          Navigation: Application Developer > Application > Database > Table

-          You can query with the table name. You will find all the table configuration.

-          Now table successfully registered with Oracle Receivable Application.

Step 4: Required to enable event-based oracle alert.

-          Navigation: Alert Manager > Alert > Define

-          Create oracle alert using the registered custom table as per the requirement.

-          Create action for the event click on Action Button.

-          Click on Action Details.

-          Filled up required details for send notification to respective person.

-          Save the data.

-          Close the tab.

-          Click on Action Sets. Enter Action Sets name.

-          Click on Action Set Details.

-          Enter the member details.

-          Close the window.

-          Click on Alert Details

-          Go to the Installation tab.

-          Assign Operating Units.

(Note: Required to Assign the Operating Units because event-based trigger have security profile check restriction)

Step 5: After Event Alert Trigger fire you can check the alert status.

-                 Navigation: Alert Manager > History.

-          You can search by custom oracle alert name.

-          Click on Find Checks.

-          It will give you the history of Alert fire events.

-          It’s showing status: Error because currently, our SMTP server was down.

-          You can check alert message output.

-          Click on Find Actions.

-          Click on Action Log.

-          You can check the notification output.

How to define oracle Day360 Formula function equivalent to Excel Day360 Function

create or replace function days_360(
       p_start_date           date,
       p_end_date             date,
       p_rule_type            char default 'F'
       )
    RETURN number
IS
  v_mm1    pls_integer;
  v_dd1    pls_integer;
  v_yyyy1  pls_integer;
  v_mm2    pls_integer;
  v_dd2    pls_integer;
  v_yyyy2  pls_integer;
BEGIN
  v_yyyy1 := to_number(to_char(p_start_date,'yyyy'));
  v_mm1   := to_number(to_char(p_start_date,'mm'));
  v_dd1   := to_number(to_char(p_start_date,'dd'));
  v_yyyy2 := to_number(to_char(p_end_date,'yyyy'));
  v_mm2   := to_number(to_char(p_end_date,'mm'));
  v_dd2   := to_number(to_char(p_end_date,'dd'));
  IF p_rule_type = 'F' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31
          THEN IF v_dd1 < 30
                    THEN v_dd2 := 1;
                         v_mm2 := v_mm2 + 1;
                         IF v_mm2 = 13 THEN v_mm2 := 1;
                                            v_yyyy2 := v_yyyy2 +1;
                         END IF;
                    ELSE v_dd2 := 30;
               END IF;
     END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30;
               IF  (v_dd1 < 30)
                   THEN v_dd2 := 1;
                        v_mm2 := 3;
               END IF;
     END IF;
     IF v_mm2 IN (4, 6, 9, 11) AND v_dd2 = 30
          AND v_dd1 < 30
          THEN v_dd2 := 1;
               v_mm2 := v_mm2 + 1;
     END IF;
  ELSIF p_rule_type = 'T' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31 THEN v_dd2 := 30; END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30; END IF;
  ELSE RAISE_APPLICATION_ERROR('-20002','3VL Not Allowed Here');
  END IF;
  RETURN (v_yyyy2 - v_yyyy1) * 360
       + (v_mm2 - v_mm1) * 30
       + (v_dd2 - v_dd1);
END;

/

Testing SQL Script:

select days_360('03-FEB-2016','31-MAR-2019') from dual;