Few Examples on Dynamic SQL

------------------------------------------------------------------
DECLARE
v_table_name varchar2(100) := 'test_dept';
v_stmt varchar2(1000);
BEGIN
v_stmt := 'insert into '||v_table_name||' values(50,''HR'')';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
END;

-------------------------------------------------------------------
DECLARE
type sob_record is record
(
name gl_sets_of_books.NAME%type,
currency_code gl_sets_of_books.CURRENCY_CODE%type,
set_of_books gl_sets_of_books.SET_OF_BOOKS_ID%type
);
v_stmt varchar2(1000);
curr sys_refcursor;
row_rec sob_record;
BEGIN
v_stmt := 'select name,currency_code,set_of_books_id from gl_sets_of_books where set_of_books_id=:input';
open curr for v_stmt using 2023;
loop
fetch curr into row_rec;
exit when curr%notfound;
dbms_output.put_line(row_rec.currency_code || '    '||row_rec.name);
end loop;
END;


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

DECLARE
type status_record is record
(
cid xx_account_status_swap.customer_number%type,
acc_status xx_account_status_swap.new_account_status%type
);
type status_table is table of status_record;

v_stmt varchar2(1000);
curr sys_refcursor;
status_coll status_table;
BEGIN
v_stmt := 'select customer_number,new_account_status from xx_account_status_swap where success_status=:input';

open curr for v_stmt using 'P';
fetch curr bulk collect into status_coll;

for i in 1..status_coll.count loop
dbms_output.put_line(status_coll(i).cid||'   '||status_coll(i).acc_status);
end loop;

dbms_output.put_line('----------------------------------------------');
dbms_output.put_line(status_coll.count);
dbms_output.put_line('----------------------------------------------');
END;

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


Ur's
AmarAlam

Oracle Applications Idle Session Timeout

What is Session Idle time?

If Oracle Apps client is idle for some time (Eg. Application user goes for a coffee break) session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session (both forms & self service) after idle time value is reached to the one mentioned in configuration file.

To enter into application, profile option "ICX Session Timeout" is used.

ICX Session Time out mentioned in profile option ICX: Session Timeout is in minutes, so ICX session timeout=30

From where ICX: Session Timeout & session.timeout get values?

Autoconfig determines value for profile option "ICX: Session Timeout" and "session.timeout" from entry in context file ($APPL_TOP/admin/SID_hostname.xml) with parameter s_sesstimeout where value mentioned is in milliseconds so profile option ICX: Session Timeout value should be s_sesstimeout/ (1000 * 60) which means here its 10 Minutes. This value is also set in zone.properties in $IAS_ORACLE_HOME/Apache/Jserv where number mentioned is in milli second i.e. 600000 (equal to 10 Minutes) session.timeout = 600000.

Eg.  Session Timeout is in minutes, so ICX session timeout=480 is 8Hrs. 

Ur's
Amar Alam

FND_PROFILE and FND_GLOBAL values

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

     

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);


Ur's
Amaralam

Query to find Parameters and Value Sets associated with a Concurrent Program

 Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

-------------------------------------------------------------------------------
-- Query to find Parameters and Value Sets associated with a Concurrent Program
-------------------------------------------------------------------------------

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",
       fcp.concurrent_program_name        "Program Short Name",
       fdfcuv.column_seq_num              "Column Seq #",
       fdfcuv.end_user_column_name        "Parameter Name",
       fdfcuv.form_left_prompt            "Prompt Name",
       fdfcuv.enabled_flag                "Enabled Flag",
       fdfcuv.required_flag               "Required Flag",
       fdfcuv.display_flag                "Display Flag",
       fdfcuv.flex_value_set_id           "Value Set ID",
       ffvs.flex_value_set_name           "Value Set Name",
       flv.meaning                        "Default Type",
       fdfcuv.default_value               "Default Value"
  FROM fnd_concurrent_programs      fcp,
       fnd_concurrent_programs_tl   fcpl,
       fnd_descr_flex_col_usage_vl  fdfcuv,
       fnd_flex_value_sets          ffvs,
       fnd_lookup_values            flv
 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id
   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'
   AND flv.lookup_code(+)                 =  fdfcuv.default_type
   AND fcpl.LANGUAGE                      =  USERENV('LANG')
   AND flv.LANGUAGE(+)                    =  USERENV('LANG')
   AND fdfcuv.enabled_flag                =  'Y'
   AND fcpl.user_concurrent_program_name  =  'IFFCO HRMS : Costing Summary Report - Consolidated'  --
 ORDER BY fdfcuv.column_seq_num;



Ur's
AmarAlam

Query to find Oracle Alert

The following query finds all enabled custom alerts. You can comment out the very last two lines (alr.enabled_flag and alr.created_by) to display all both enabled and disabled alerts.

-------------------------------------------------------------------------------
-- Query to find Custom Oracle Alert
-------------------------------------------------------------------------------
SELECT alr.application_id,
       alr.alert_id,
       alr.alert_name,
       alr.start_date_active,
       alr.description,
       alr.sql_statement_text
  FROM alr.alr_alerts alr
 WHERE 1=1
   AND alr.created_by <> 1      -- show only custom alerts
   AND alr.enabled_flag = 'Y';  -- show only enabled alerts

Ur's
AmarAlam

Active Responsibility List with Active User Count

 SELECT
       fat.application_name         "Application Name",
       frv.responsibility_name      "Active Responsibility Name",
       COUNT(fu.user_name)          "Active User Count"
  FROM
       fnd_user                     fu,
       fnd_user_resp_groups_direct  furgd,
       fnd_responsibility_vl        frv,
       fnd_application_tl           fat
 WHERE
       1=1
   --
   AND furgd.end_date IS NULL
   --
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(furgd.start_date) AND TRUNC(NVL(furgd.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(frv.start_date)   AND TRUNC(NVL(frv.end_date, SYSDATE+1))
   AND TRUNC(SYSDATE) BETWEEN
          TRUNC(fu.start_date)    AND TRUNC(NVL(fu.end_date, SYSDATE+1))
   --
   AND fat.application_id       =  frv.application_id
   --
   AND furgd.responsibility_id  =  frv.responsibility_id
   AND furgd.user_id            =  fu.user_id
   --
 GROUP BY fat.application_name, frv.responsibility_name
 ORDER BY fat.application_name, frv.responsibility_name;



Ur's
AmarAlam

Query to find the first word from a string

 WITH qry
AS
(
   SELECT NULL field_name FROM dual
      UNION ALL
   SELECT 'AMAR' FROM dual
      UNION ALL
   SELECT 'AMAR ALAM' FROM dual
      UNION ALL
   SELECT 'Abdul M Faheem' FROM dual
      UNION ALL
   SELECT 'Sravan P. Kumar' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt, Ltd.' FROM dual
      UNION ALL
   SELECT 'IBM India Pvt Ltd' FROM dual
)
SELECT field_name,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\1')  exp1,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\2')  exp2,
       REGEXP_REPLACE(field_name, '^(\w+)(.*?)(\w*$)', '\3')  exp3
  FROM qry;




Ur's
AmarAlam

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

 -------------------------------------------------------------------------------
-- delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------
DECLARE
  lv_prog_short_name    VARCHAR2(240);
  lv_appl_short_name    VARCHAR2(240);

BEGIN
   -- set the variables first
   lv_prog_short_name := 'XXAJ_IFFCO_COST_SUMARY';     -- concurrent program short name
   lv_appl_short_name := 'XXIFF';          -- application short name
 
   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND
      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)   
   THEN
    
      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);
    
      COMMIT;
 
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');
 
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
 
END;

Ur's
AmarALam

Find Orcale Discoverer Reports in DataBase

SELECT DISTINCT doc_name, obj.obj_name folder_name, bas.ba_name,
                DECODE (doc.doc_created_by,
                        'EUL5_US', 'N/A',
                        (SELECT user_name
                           FROM fnd_user
                          WHERE user_id = SUBSTR (doc.doc_created_by, 2))
                       ) user_name,
                DECODE (doc.doc_created_by,
                        'EUL5_US', 'N/A',
                        (SELECT description
                           FROM fnd_user
                          WHERE user_id = SUBSTR (doc.doc_created_by, 2))
                       ) user_description
           FROM eul_us.eul5_documents doc,
                eul_us.eul5_elem_xrefs xref,
                eul_us.eul5_expressions EXP,
                eul_us.eul5_objs obj,
                eul_us.eul5_ba_obj_links bol,
                eul_us.eul5_bas bas
          WHERE xref.ex_from_id = doc.doc_id
            AND xref.ex_to_id = EXP.exp_id
            AND obj.obj_id = EXP.it_obj_id
            AND bol.bol_obj_id = obj.obj_id
            AND bas.ba_id = bol.bol_ba_id
            AND doc.doc_created_by != 'ORACLE_APPS'
            AND UPPER (doc_name) LIKE UPPER ('%CONS%TRACK%')
--and BAS.BA_NAME like '%Business Area%'
ORDER BY        user_name


Thanks
AmarAlam

Order 2 Cash Cycle Technical Flow in Oracle Apps

SELECT *
  FROM oe_order_headers_all
 WHERE order_number = 66161;

SELECT *
  FROM oe_order_lines_all
 WHERE header_id = (SELECT header_id
                      FROM oe_order_headers_all
                     WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_details
 WHERE source_header_id = (SELECT header_id
                             FROM oe_order_headers_all
                            WHERE order_number = 66161);

SELECT *
  FROM wsh_delivery_assignments
 WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161));

SELECT *
  FROM wsh_new_deliveries
 WHERE delivery_id IN (
          SELECT delivery_id
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)));

SELECT *
  FROM ra_customer_trx_all
 WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute3) IN (
          SELECT TO_CHAR (delivery_id)
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)))

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id IN (
          SELECT cash_receipt_id
            FROM ar_payment_schedules_all
           WHERE customer_trx_id IN (
                    SELECT DISTINCT customer_trx_id
                               FROM ra_customer_trx_all
                              WHERE (interface_header_attribute1 =
                                        (SELECT TO_CHAR (order_number)
                                           FROM oe_order_headers_all
                                          WHERE header_id =
                                                   (SELECT header_id
                                                      FROM oe_order_headers_all
                                                     WHERE order_number =
                                                                         66162))
                                    )));

SELECT *
  FROM ar_receivable_applications_all
 WHERE customer_trx_id =
          (SELECT customer_trx_id
             FROM ra_customer_trx_all
            WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162)));

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id =
          (SELECT cash_receipt_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));

SELECT *
  FROM gl_code_combinations
 WHERE code_combination_id =
          (SELECT code_combination_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));


Ur's
AmarAlam