How to find the tables which are registered with Oracle Apps

select * from APPLSYS.FND_TABLES

oracle application module short name and description using back end table

select * from APPLSYS.FND_ORACLE_USERID

How to get the table or index syntax from sql

We can use the DBMS_METADATA.GET_DDL which will retrieve the complete syntax of the table / index

Here is the example.

 SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_PRA','APPS') ddl FROM dual;

Oracle Alerts back end tables and it is description

ALR_ALERTS -  It stores the alerts definition and the query
ALR_PERIODIC_ALERTS_VIEW -- It stores the periodic information of an alert
ALR_ACTIONS - It stores alert action information, ex. email subject, body and other infromation
ALR_ACTIONS_V -- It is a view based on ALR_ACTIONS

Adding a form function to menu or submenu using back end script

DECLARE
   xrowid   VARCHAR2 (2000);
BEGIN
   fnd_menu_entries_pkg.insert_row (
      xrowid, -- Rowid
      87216, -- Menu ID
      201, -- Sequence Number
      NULL, -- Sub menu ID
      50138, -- Function ID
      'Y', -- Grant Flag
      NULL, -- Prompt
      NULL, -- Description
      SYSDATE, -- Creation Date
      -1, -- Created by
      SYSDATE, Last update date
      -1, -- Last Updated by
      -1 -- Last update login
   );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;

What is difference between AD_BUGS and AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation. 

AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

How add a concurrent program to a concurrent manager using back end script

BEGIN
   fnd_manager.specialize (
      concurrent_manager_short_name,
      conc_manager_application_name,
      program_type, --(include/exclude)
      object_type, -- 'Program'
      concurrent_program_short_name,
      concurrent_program_application,
      last_update_date,
      last_update_by
   );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

How to find how which concurrent manager picked up which concurrent program

SELECT fcqt.user_concurrent_queue_name, fcqc.include_flag, fcqc.type_code,
       fcpt.user_concurrent_program_name
  FROM fnd_concurrent_queues_tl fcqt,
       fnd_concurrent_queue_content fcqc,
       fnd_concurrent_programs_tl fcpt
 WHERE fcqt.application_id = fcqc.queue_application_id
   AND fcqt.concurrent_queue_id = fcqc.concurrent_queue_id
   AND fcpt.LANGUAGE = 'US'
   AND fcpt.application_id = fcqc.type_application_id
   AND fcpt.concurrent_program_id = fcqc.type_id
   AND fcpt.user_concurrent_program_name LIKE 'Concurrent program name%'

Steps to develop a form in using 10g forms

Step 1: Download the TEMPLATE.fmb.
Step 2: Rename the TEMPLATE.fmb as per your company naming standards.
Step 3: Change the below information on the PER-FORM trigger at form level trigger
      FND_STANDARD.FORM_INFO('$Revision: <Number>$', 

                   '<Form Name>', 
                   '<Application Shortname>', 
                   '$Date: <YY/MM/DD HH24:MI:SS> $',
                   '$Author: <developer name> $');
APP_STANDARD.EVENT('PRE-FORM');  
APP_WINDOW.SET_WINDOW_POSITION('BLOCKNAME',
                   'FIRST_WINDOW');

 Step 4: Add the custom blocks as per your requirement. 

 Step 5: Assign the proper property classes to the items defined in the block

 Step 6: Write the custom logic to achive the desired functionality.

 Step 7: Register the form with Oracle Apps.

 Step 8: Assign the form function to a responsibility

 Step 9: Upload the form in to custom top of unix / Liunx box 

 Step 10: Compile the form using the below compilation script

frmcmp_batch module=$AU_TOP/forms/US/ABC.fmb userid=APPS/apps output_file=$CUSTOM_TOP/forms/US/ABC.fmx module_type=form

How to find the sql where clause of a value set from back end

select application_table_name,
       value_column_name,
       meaning_column_name,
       additional_where_clause
  from fnd_flex_validation_tables a, fnd_flex_value_sets b
 where a.flex_value_set_id = b.flex_value_set_id
       and b.flex_value_set_name = :flex_value_set_name

How to find the long running sqls

select sid,
       opname,
       target,
       sofar,
       totalwork,
       units,
       (totalwork-sofar)/time_remaining bps,
       time_remaining,
       sofar/totalwork*100 fertig
from   v$session_longops
where  time_remaining > 0

Workflow lookup table and it is details

The below table contains the look up types related to workflow
select * from WF_LOOKUP_TYPES_TL

The below table will have the meaning of each lookup type. I gave one example query below.

select * from WF_LOOKUPS_TL where lookup_type like 'WFSTD_DAY_OF_MONTH%' and language ='US'

How to find the processing constraints and related to templates from back end.

select OEPCC.CONSTRAINT_ID
, OEPCC.CONDITION_ID
, OEPCC.last_update_date
, OEPCCT.USER_MESSAGE
, OEPCC.VALIDATION_TMPLT_ID
, OEVT.ENTITY_ID
, OEVT.VALIDATION_TMPLT_SHORT_NAME
, OEVT.VALIDATION_TYPE
, OEVT.SYSTEM_FLAG
, OEVT.ACTIVITY_NAME
, OEVT.ACTIVITY_STATUS_CODE
, OEVT.ACTIVITY_RESULT_CODE
, OEVT.WF_ITEM_TYPE
from OE_PC_CONDITIONS OEPCC
, OE_PC_CONDITIONS_TL OEPCCT
, OE_PC_VTMPLTS OEVT
where OEPCC.CONSTRAINT_ID = &constraint_id
and OEPCCT.CONDITION_ID = OEPCC.CONDITION_ID
and (OEVT.VALIDATION_TMPLT_ID = OEPCC.VALIDATION_TMPLT_ID
and OEPCCT.language='US'    );

--

select OEPCC.CONSTRAINT_ID
, OEPCC.CONDITION_ID
, OEPCC.last_update_date
, OEPCCT.USER_MESSAGE
, OEPCC.VALIDATION_TMPLT_ID
, OEVTC.COLUMN_NAME
, OEVTC.VALIDATION_OP
, OEVTC.VALUE_STRING
from OE_PC_CONDITIONS OEPCC
, OE_PC_CONDITIONS_TL OEPCCT
, OE_PC_VTMPLT_COLS OEVTC
where OEPCC.CONSTRAINT_ID = &constraint_id
and OEPCCT.CONDITION_ID = OEPCC.CONDITION_ID
and (OEVTC.VALIDATION_TMPLT_ID = OEPCC.VALIDATION_TMPLT_ID
and OEPCCT.language='US' );

How to know the concurrent program is assigned to which concurrent manager

SELECT a.include_flag, a.queue_application_id , c.user_concurrent_queue_name,
       d.user_concurrent_program_name
  FROM applsys.fnd_concurrent_queue_content a,
       applsys.fnd_concurrent_programs b,
       apps.fnd_concurrent_queues_vl c,
       fnd_concurrent_programs_tl d
 WHERE type_id = b.concurrent_program_id
  AND c.concurrent_queue_id = a.concurrent_queue_id
   AND b.concurrent_program_id = d.concurrent_program_id
   AND d.user_concurrent_program_name LIKE '%Lines on Credit Check Hold Report%'

How to find the sid from concurrent request id

The below sql retrieve the sid information for the running concurrent program.

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
AND a.phase_code = 'R';

Also you can use the below sql to find out which sql is running for the above concurrent request id.

select a.sid, a.serial#, b.sql_text
   from v$session a, v$sqlarea b
    where a.sql_address=b.address
     and a.sid = &sid

how to pass the same value to more than one report with same parameter in request sets in oracles apps

Ex. If you have more than one report in a request set for which the parameter value is same and the user don't want to enter it more than one time.

Here is the setup.

Use the Shared Parameter field to set a default value for a parameter that occurs in more than one report or program of a request set. Once you enter the same parameter label in the Shared Parameter field for each occurrence of the same parameter, the value that you assign to the first occurrence of the parameter becomes the default value for all subsequent occurrences of the parameter. The shared parameter label simply enables you to set an initial default value for all occurrences of the same parameter so you can avoid typing the same value all over again for every occurrence of the parameter.

For example, suppose you define a request set that includes three reports, and all reports include a parameter called “Set of Books". You want the “Set of Books" parameter to default to the same value in all reports. To accomplish this, enter a label called “Book" in the Shared Parameter field for the first occurrence of this parameter. You can also assign a value in the Default Value field of this parameter now, or wait until you run the request set to assign a default value when the parameter first appears. Enter the label “Book" in the Shared Parameter field of all other occurrences of the “Set of Books" parameter in your request set. When you submit this request set from the Submit Requests window, every parameter that you label “Book" defaults to the value you assign to the first occurrence of the “Set of Books" parameter.

Important: Note that if you later change the value of a parameter that contains a shared parameter label, you change only the value for that instance of the parameter, and not the value for all other occurrences of that labelled parameter.

Creating order using oe_order_pub.process_order api in order management

The major difference between 11i API parameter and R12 parameter is Org_Id . In R12 Org_ID as parameter we need to pass it to the API.

DECLARE
   l_return_status                VARCHAR2 (2000);
   l_msg_count                    NUMBER;
   l_msg_data                     VARCHAR2 (2000);
   -- PARAMETERS
   l_debug_level                  NUMBER                                   := 5; -- OM DEBUG LEVEL (MAX 5)
   l_org                          VARCHAR2 (20)                          := '111'; -- OPERATING UNIT
   l_no_orders                    NUMBER                                   := 1; -- NO OF ORDERS
   -- INPUT VARIABLES FOR PROCESS_ORDER API
   l_header_rec                   oe_order_pub.header_rec_type;
   l_line_tbl                     oe_order_pub.line_tbl_type;
   l_action_request_tbl           oe_order_pub.request_tbl_type;
   -- OUT VARIABLES FOR PROCESS_ORDER API
   l_header_rec_out               oe_order_pub.header_rec_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;
   l_msg_index                    NUMBER;
   l_data                         VARCHAR2 (2000);
   l_loop_count                   NUMBER;
   l_debug_file                   VARCHAR2 (200);
   l_user_id                      NUMBER;
   l_resp_id                      NUMBER;
   l_resp_appl_id                 NUMBER;
BEGIN
   -- INITIALIZATION REQUIRED FOR R12
   mo_global.set_policy_context ('S', l_org);
   mo_global.init ('ONT');

   -- INITIALIZE DEBUG INFO
   IF (l_debug_level > 0)
   THEN
      l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
      oe_debug_pub.initialize;
      oe_msg_pub.initialize;
      oe_debug_pub.setdebuglevel (l_debug_level);
   END IF;

 
--    INITIALIZE ENVIRONMENT
   fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
   -- INITIALIZE HEADER RECORD
   l_header_rec := oe_order_pub.g_miss_header_rec;
   -- POPULATE REQUIRED ATTRIBUTES
   l_header_rec.operation := oe_globals.g_opr_create;
   l_header_rec.pricing_date := SYSDATE;
   l_header_rec.cust_po_number := 'TSTPO30';
   l_header_rec.sold_to_org_id := 45928;
   l_header_rec.price_list_id := 29061;
   l_header_rec.ordered_date := SYSDATE;
   l_header_rec.sold_from_org_id := 400674685;
   --l_header_rec.ship_from_org_id := 857545;
   l_header_rec.ship_to_org_id := 416327212;
   l_header_rec.invoice_to_org_id := 401869206;
   l_header_rec.salesrep_id := -3;
   --  l_header_rec.flow_status_code := 'ENTERED';
   l_header_rec.order_type_id := 700203;
   -- INITIALIZE ACTION REQUEST RECORD
   l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
   -- INITIALIZE LINE RECORD
   l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
   l_line_tbl (1).operation := oe_globals.g_opr_create; -- Mandatory Operation to Pass
   l_line_tbl (1).inventory_item_id := 4408;
   l_line_tbl (1).ordered_quantity := 1;
   l_line_tbl (1).ship_from_org_id := 1163;
   l_line_tbl (1).subinventory := NULL;

   FOR i IN 1 .. l_no_orders
   LOOP -- BEGIN LOOP
      -- CALLTO PROCESS ORDER API
      oe_order_pub.process_order (
         p_org_id=> l_org,
         --     p_operating_unit           => NULL,
         p_api_version_number=> 1.0,
         p_header_rec=> l_header_rec,
         p_line_tbl=> l_line_tbl,
         p_action_request_tbl=> l_action_request_tbl,
         -- OUT variables
         x_header_rec=> l_header_rec_out,
         x_header_val_rec=> l_header_val_rec_out,
         x_header_adj_tbl=> l_header_adj_tbl_out,
         x_header_adj_val_tbl=> l_header_adj_val_tbl_out,
         x_header_price_att_tbl=> l_header_price_att_tbl_out,
         x_header_adj_att_tbl=> l_header_adj_att_tbl_out,
         x_header_adj_assoc_tbl=> l_header_adj_assoc_tbl_out,
         x_header_scredit_tbl=> l_header_scredit_tbl_out,
         x_header_scredit_val_tbl=> l_header_scredit_val_tbl_out,
         x_line_tbl=> l_line_tbl_out,
         x_line_val_tbl=> l_line_val_tbl_out,
         x_line_adj_tbl=> l_line_adj_tbl_out,
         x_line_adj_val_tbl=> l_line_adj_val_tbl_out,
         x_line_price_att_tbl=> l_line_price_att_tbl_out,
         x_line_adj_att_tbl=> l_line_adj_att_tbl_out,
         x_line_adj_assoc_tbl=> l_line_adj_assoc_tbl_out,
         x_line_scredit_tbl=> l_line_scredit_tbl_out,
         x_line_scredit_val_tbl=> l_line_scredit_val_tbl_out,
         x_lot_serial_tbl=> l_lot_serial_tbl_out,
         x_lot_serial_val_tbl=> l_lot_serial_val_tbl_out,
         x_action_request_tbl=> l_action_request_tbl_out,
         x_return_status=> l_return_status,
         x_msg_count=> l_msg_count,
         x_msg_data=> l_msg_data
      );

      -- CHECK RETURN STATUS
      IF l_return_status = fnd_api.g_ret_sts_success
      THEN
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('Sales Order Successfully Created');
         END IF;

         COMMIT;
      ELSE
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('Failed to Create Sales Order');
         END IF;

         ROLLBACK;
      END IF;
   END LOOP;

   -- DISPLAY RETURN STATUS FLAGS
   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line (
            'Process Order Return Status is: ========>'
         || l_return_status
      );
      DBMS_OUTPUT.put_line (
            'Process Order msg data is: ===========>'
         || l_msg_data
      );
      DBMS_OUTPUT.put_line (
            'Process Order Message Count is:=======>'
         || l_msg_count
      );
      DBMS_OUTPUT.put_line (
            'Sales Order Created is:===============>'
         || TO_CHAR (l_header_rec_out.order_number)
      );
      DBMS_OUTPUT.put_line (
            'Booked Flag for the Sales Order is:======>'
         || l_header_rec_out.booked_flag
      );
      DBMS_OUTPUT.put_line (
            'Header_id for the Sales Order is:========>'
         || l_header_rec_out.header_id
      );
      DBMS_OUTPUT.put_line (
            'Flow_Status_Code For the Sales Order is=>:'
         || l_header_rec_out.flow_status_code
      );
   END IF;

   -- DISPLAY ERROR MSGS
   IF (l_debug_level > 0)
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         oe_msg_pub.get (
            p_msg_index=> i,
            p_encoded=> fnd_api.g_false,
            p_data=> l_data,
            p_msg_index_out=> l_msg_index
         );
         DBMS_OUTPUT.put_line (   'message is:'
                               || l_data);
         DBMS_OUTPUT.put_line (   'message index is:'
                               || l_msg_index);
      END LOOP;
   END IF;

   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line (   'Debug = '
                            || oe_debug_pub.g_debug);
      DBMS_OUTPUT.put_line (
            'Debug Level = '
         || TO_CHAR (oe_debug_pub.g_debug_level)
      );
      DBMS_OUTPUT.put_line (
            'Debug File ='
         || oe_debug_pub.g_dir
         || '/'
         || oe_debug_pub.g_file
      );
      oe_debug_pub.debug_off;
   END IF;
END;

How to Copy Sales Order Line level sets in Order Management (R12i Feature)

In R12 Oracle has introduced a new feature, where you can copy the Line Sets (fulfillment/Ship/Arrival) while copying the sales order, this will be great help to customers where they have very large sales order with Line sets. Till now user has no option , but to copy the sales order without sets and then manually added line to the sets in new-copied sales order , this process is very tedious and time consuming if the # of lines Order are large and these lines belongs to multiple Sets. But with new functionality their job is really very easy.

This new feature to copy the Sets , has nothing to do with the existing feature of fulfillment set at the order header level , but it do results in some issues/error is not take care properly.

For this new feature Oracle has provided 2 additional checkbox in the Copy Order Form.
1. Fulfillment Set
2. Ship /arrival Set
By default these will be unchecked and also if user is not copying the Order line then these 2 check boxes will be disabled.

How to find which patch applied on database related to oracle bugs

select * from ad_bugs where bug_number ='12945067' order by bug_number

how to find the number of users connected to Oracle Apps in the past 1 day using SQL

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1'