Thursday 1 August 2019




Issue: Oracle Custom Form Enable List Lamp does not appear in Date field Property Palette LOV level.


At the time of open custom form or compilation time into Form builder following Error received.

ERROR

FRM-18108: Failed to load the following objects
.
Source Module: APPSTAND Source Object: STANDARD_PC_AND_VA
Source Module: APPSTAND Source Object: STANDARD_TOOLBAR
Source Module: APPSTAND Source Object: STANDARD_CALENDAR


Solution: Required to load all standard object source file before compiling the form or opening the form.

Step 1: Download APPSTAND.fmb  file from ($AU_TOP/forms/US)  using FTP server.



Step 2: Copy this APPSTAND.fmb file at local machine Form Path Level and paste it at the local machine level form path location.

We can check the local machine form path.

Navigation:
My Computer > Right Click > Property > Advance System Settings > Advanced > Environment Variable > FORM_PATH



Upload .fmb file




Step 3: Open the APPSTAND.fmb file into the form builder after that open your custom form .fmb file.

Step 4: Now an onward custom form will never give any error for missing source object files.

Step 5: Now you can able to see ENABLE_LIST_LAMP option in text field Property palette LOV level.

After loading the APPSTAND.fmb many standard object subclass also you can able to use.











Sunday 21 July 2019

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.








Thursday 18 July 2019

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.