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'

BITAND function in sql

Purpose

The BITAND function treats its inputs and its output as vectors of bits; the output is the bitwise AND of the inputs.

The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument to BITAND is NULL, the result is NULL.

The arguments must be in the range -(2^(n-1)) .. ((2^(n-1))-1). If an argument is out of this range, the result is undefined.

The result is computed in several steps. First, each argument A is replaced with the value SIGN(A)*FLOOR(ABS(A)). This conversion has the effect of truncating each argument towards zero. Next, each argument A (which must now be an integer value) is converted to an n-bit two's complement binary integer value. The two bit values are combined using a bitwise AND operation. Finally, the resulting n-bit two's complement value is converted back to NUMBER.

Notes on the BITAND Function

• The current implementation of BITAND defines n = 128.
• PL/SQL supports an overload of BITAND for which the types of the inputs and of the result are all BINARY_INTEGER and for which n = 32.

Examples

The following example performs an AND operation on the numbers 6 (binary 1,1,0) and 3 (binary 0,1,1):

SELECT BITAND(6,3) FROM DUAL;

BITAND(6,3)
-----------
          2

This is the same as the following example, which shows the binary values of 6 and 3. The BITAND function operates only on the significant digits of the binary values:

SELECT BITAND(
   BIN_TO_NUM(1,1,0),
   BIN_TO_NUM(0,1,1)) "Binary"
FROM DUAL;
 
    Binary
----------
         2

Error while order import in R12 Unable to determine the Country code for the Location id: XXXXXX and Location type: BILL_TO

Reason for the issue. 

  If a new country added in the FND_TERRITORIES_TL and FND_TERRITORIES and the same data is not available in HZ_GEOGRAPHIES.

How to resolve the issue
   Login to unix box and go the below top
$AR_TOP/patch/115/sql

Then download the below file.
ARHGEFTR.sql

and execute it from apps schema. 

Attaching the concurrent program to the request group from back end script

BEGIN
   fnd_program.add_to_group (
      'XXFINTSTCPDB', -- Concurrent Program Short Name
      'XXFIN', -- Application Short Name
      'All Reports', -- Report Group Name
      'SQLAP'-- Report Group Application
   );
   COMMIT;
END;

How to see the concurrent program Execution Methods from back end

SELECT MEANING "Execution Method"
FROM fnd_lookup_values
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND enabled_flag  = 'Y'

How to see information about environment name and value from back end

This table stores information about environment name and value 

select * from FND_ENV_CONTEXT

How to check the concurrent managers from back end

select * from FND_CONCURRENT_QUEUES_VL

Concurrent Request Life Cycle

error stack vs dbms_output.put_line

DBMS_UTILITY.FORMAT_CALL_STACK  is used to display the stack information of a procedure or a function. 

PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. 

Who Raised That Exception?

When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled.

 example :

  CREATE OR REPLACE PROCEDURE proc1 IS

BEGIN
   DBMS_OUTPUT.put_line ('running proc1');
   RAISE NO_DATA_FOUND;
END;
/
CREATE OR REPLACE PROCEDURE proc2 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc1');
   proc1;  
END;
/
CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
   DBMS_OUTPUT.put_line ('calling proc2');
   proc2;
END;
/

CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
  DBMS_OUTPUT.put_line ('calling proc2');
  proc2;
EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/




SQL> SET SERVEROUTPUT ON
SQL> exec proc3
calling proc2
calling proc1
running proc1
ORA-01403: no data found



DBMS_OUTPUT.PUT_LINE  is used to display the information on the screen. This also we can use it for debugging purpose. Only thing we have to use it in all the places of the procedure or function. 

Some of the useful profiles in Order Management

OM: Show Line Details

ONT_SHOW_LINE_DETAILS

This profile option determines whether the line details of a model are displayed in the Sales Orders window. You can also toggle the display of line details using the Tools menu from the Sales Orders window.

OM: Sales Order Form: Refresh Method (Query Coordination)

OM_UI_REFRESH_METHOD

This profile option determines whether the screen is refreshed after every save. There are four options available:

• Automatic Refresh with Repositioning of Cursor: When the profile is set to this option then screen is refreshed and also the cursor is repositioned to the original line from which the save operation was performed.

• Automatic Refresh Without Repositioning Of Cursor: When the profile is set to this option then screen is refreshed and the cursor is always positioned on the first line.

• Manual: With this option, users have to explicitly requery to see the latest changes. User are also not prompted with a message.

• Askme: A dialog box is prompted for the users to decide whether they want to refresh the screen to see the new data. If the user selects yes, then the screen is refreshed. If the user selects No then screen is not refreshed.

OM: Prevent Booking for Line Generic Holds

ONT_PREVENT_BOOKING

If a generic hold has been applied the transaction will fail booking.

OM: Enforce Check For Duplicate Purchase Order

ONT_ENFORCE_DUP_PO

When this profile option is set to Yes or is blank, a check will be made to verify if the entered PO number is duplicate or not. It will display a warning if a duplicate number is found. When the profile option is set to No, a check to verify for duplicate PO number will not be carried out.

OM: E-Mail Required on New Customers

ONT_MANDATE_CUSTOMER_EMAIL

This profile option determines whether the field E-mail address is required for any customer or customer contact you define within Order Management. Select from:

• Yes: E-mail address is required, for both the customer and customer contact, when defining or updating a customer or customer contact.

• No or NULL (the default): E-mail address is not required when defining or updating either a customer or customer contact.

OM: Display Actions Button vs. Poplist

Controls whether the traditional button for Actions or a pop list will be used to display available Actions. Valid values are: Button and Pop list. The default value will be Button for backward compatibility.

How to set the User-Level Multiple Organizations in Oracle apps R12

The MO: Security Profile controls the list of operating units a user with a specific
responsibility can access and the MO: Default Operating Unit controls the default
operating unit that defaults in all pages that display the operating unit field. Users can
further tailor the number of operating units they want to work with and specify a
different default operating unit by using the Multi-Org Preferences page. This allows
users to have complete control over their work environment to help them process data
more efficiently.
This page can be accessed using the menu function called User Preferences.
Note: If you do not see the User Preferences function from your application, inform
your system administrator to add the user preferences function called FNDMOPREFS
to your product's submenu. The default operating unit set on the Multi-Org Preferences
page will be saved to the MO: Default Operating Unit profile option at the user level.
To define a user preference:
1. Open the Multi-Org Preferences page.
The following information will appear at the header level:
• User Name: The user name of the user
• Responsibility Name: The name of the responsibility that accessed the page.
• Security Profile: The name of the security profile assigned to the MO: Security
Profile for the responsibility/user.
2. You can specify a default operating unit. The list of operating units will be limited
Setup and Implementation 2-25
to those assigned to the Security Profile.
3. You can select one or more operating units and then set them as preferred operating
units by clicking the Set as Preferred button.

Details about OM: Debug Level profile in Order Management

OM: Debug Level  -  ONT_DEBUG_LEVEL

This profile option is used to store debug messages to assist in problem identification. The OM: Debug profile option controls which debug messages get written to the debug log based on their assigned level. The directory to be specified for this profile should be available in utl_file_dir parameter of the init.ora file (or check v$parameter) of the application database instance.

Assigning a value greater than 0 or NULL causes debugging to be turned on. The value of this option determines the level of debug messages printed within a log file. For example, if OM: Debug Level has a current value of 3, all debugging messages with a level of 1,2 or 3 will be spooled out to the debug log.

Valid values for this profile option are:

NULL: Do not print any debug messages

1:Level 1; Print a limited subset of debug messages (level 1)

2:Level 2; Print a limited subset of debug messages, including level 1

3:Level 3; Print a limited subset of debug messages, including levels 1,2

4:Level 4; Print a limited subset of debug messages, including levels 1,2 3

5:Level 5; Print all debug messages

If you set this profile option to a value other than NULL, system performance may be affected, because creating and updating a debug log is a task that will consume a lot of system resources as you enter higher debug levels.

Order Management recommends you set this profile option only if you are attempting to gather additional details for unexpected application issues and then reset the value back to NULL once you have gathered the necessary debug details.

The default for this profile option is NULL.

Sample update script using oe_order_pub.process_order api

DECLARE
   v_header_rec   oe_order_pub.header_rec_type;
   v_line_tbl     oe_order_pub.line_tbl_type;
BEGIN
 
-- Line Record --
   v_line_tbl (1) := oe_order_pub.g_miss_line_rec; --oe_order_pub.G_MISS_LINE_TBL;
   v_line_tbl (1).last_update_date := SYSDATE;
   v_line_tbl (1).last_updated_by := fnd_global.user_id;
   v_line_tbl (1).last_update_login := fnd_global.login_id;
   v_line_tbl (1).header_id := l_header_id; --fetch the value
   v_line_tbl (1).line_id := l_line_id; --fetch the value
   v_line_tbl (1).operation := oe_globals.g_opr_update;
   v_line_tbl (1).change_reason := 'MANUAL';
   v_line_tbl (1).ship_to_org_id := 2456;

-- API Begin
   oe_order_pub.process_order (
      p_api_version_number=> v_api_version_number,
      p_header_rec=> v_header_rec,
      p_line_tbl=> v_line_tbl
-- OUT variables
                             ,
      x_header_rec=> v_header_rec_out,
      x_header_val_rec=> v_header_val_rec_out,
      x_header_adj_tbl=> v_header_adj_tbl_out,
      x_header_adj_val_tbl=> v_header_adj_val_tbl_out,
      x_header_price_att_tbl=> v_header_price_att_tbl_out,
      x_header_adj_att_tbl=> v_header_adj_att_tbl_out,
      x_header_adj_assoc_tbl=> v_header_adj_assoc_tbl_out,
      x_header_scredit_tbl=> v_header_scredit_tbl_out,
      x_header_scredit_val_tbl=> v_header_scredit_val_tbl_out,
      x_line_tbl=> v_line_tbl_out,
      x_line_val_tbl=> v_line_val_tbl_out,
      x_line_adj_tbl=> v_line_adj_tbl_out,
      x_line_adj_val_tbl=> v_line_adj_val_tbl_out,
      x_line_price_att_tbl=> v_line_price_att_tbl_out,
      x_line_adj_att_tbl=> v_line_adj_att_tbl_out,
      x_line_adj_assoc_tbl=> v_line_adj_assoc_tbl_out,
      x_line_scredit_tbl=> v_line_scredit_tbl_out,
      x_line_scredit_val_tbl=> v_line_scredit_val_tbl_out,
      x_lot_serial_tbl=> v_lot_serial_tbl_out,
      x_lot_serial_val_tbl=> v_lot_serial_val_tbl_out,
      x_action_request_tbl=> v_action_request_tbl_out,
      x_return_status=> v_return_status,
      x_msg_count=> v_msg_count,
      x_msg_data=> v_msg_data
   );
END;

Some of the Order Management R12 new features

Multi-Organization Access Control (MOAC)

  Multi-Org Access Control enables uses to access multiple operating units data from single responsibility. Users can access reports , Concurrent programs , all setup screens of multiple operating units from single responsibility without switching responsibilities. They can also use Order Import to bring in orders for different Operating Units from within a single responsibility

Mass Scheduling Enhancements

 Mass Scheduling can now schedule lines that never been scheduled or those that have failed manual scheduling. Mass Scheduling also supports unscheduling and rescheduling.

Exception Management Enhancements

·         R12 Provides a Concurrent program to preview and tetry stuck transactions(Due to errors in workflow)

·         A Concurrent program was provided to retry these errors Worflow.

·         The program name is "Retry Errored Workflow Activities"

  

Cascading Attributes

   Cascading the header level attributes to lines of the order. This will reduce the manual entry / update by the user.

 To active this functionality

   Set the profile option : OM: Sales Order Form: Cascade Header Changes to Line

Also enable the cascading attributes by setting up the quick codes.

Setup --> Quick Codes--> Order Management --> Type - 'OM_HEADER_TO_LINE_CASCADE'

Trading Community Usage Within Order Management

Trading Community
• Party: This is a generic term for any entity which can have any type of relationship
with your company. The three primary party types are:
• Person: This party type is typically used when you are creating an entity that
operates within a business to customer environment.
• Organization: This party type is typically used when you are creating an entity
that operates within a business to business environment.
• Party Relationship: This party type represents a binary relationship between
two parties such as a partnership. Party relationship types can be seeded or
user-defined. A party relationship is optionally a party itself, meaning certain
party relationships can enter into relationships themselves (currently not
supported within Order Management).
• Party Site: Party Site represents the link between a party and a location (a valid
location for the party. Typically, your organizational hierarchy is used when
2-182 Oracle Order Management Implementation Manual
modeling using party relationships.
• Account: An account represents a relationship between your company and a party
in the Trading Community model who is your customer. Accounts contain the
attributes of the selling relationship between your organization and a party.
Account attributes do not describe a party; they only exist when a selling
relationship is present between the your organization and a party. The information
which is used for processing orders and receivables transactions is part of the
account information.
• Account Site: A party site address that is used within the context of an account.
• Account Site Usage: An account site created for specific usage; for example, billing
or shipping purposes. If a new account site usage is created for a Customer, then
that Account Site Usage will be marked as Primary.
Mapping Order Management terminology to Trading Community terminology
• Customer Site, Customer Location, Customer Address: These terms, within Order
Management equate to Trading Community Account Sites.
• Bill To, Ship To, Deliver To: These terms, within Order Management, equate to
Trading Community Account Sites Usages.
The following figure describes the conceptual usage of the Trading Community model
as utilized currently within Order Management.

Type of RMA orders in Order Management

Overview of Returns
Order Management supports a variety of methods for returning products so your
return polices can respond to the changing needs of your marketplace. For example, a
shipment is damaged in transit and your customer calls to return the item. The type of
product, your customer's needs, and your company's polices can all affect the way you
process this request for return.
Order Management lets you decide at the time you authorize the return how to process
the request. You can accept the return and process a credit for the customer, updating
all sales activity and credit balances. Or you can accept the return for replacement, and
enter a replacement order instead of issuing a credit.
Return Material Authorization Types
1. RMA with Credit Only
Your company issues a credit without the customer returning the product.
2. RMA with Repair
Your customer returns a damaged product. Your company repairs and returns the
2-98 Oracle Order Management User's Guide
product to the customer.
3. RMA with Replacement
Your customer returns a product and your company sends a replacement product
rather than issuing a credit.
4. RMA with Receipt and No Credit
Your customer returns a product you sent to them on a trial basis or at no charge,
therefore they receive no credit.
5. RMA With Receipt and Credit
Customer returns a product and receives credit.
6. Returned Item Fails Inspection (Exception case)
Your customer returns product, Company inspects product and rejects it. Company
scraps product or sends product back to Customer. In this case, you have the option of
performing an RMA transaction of type:
RMA with Repair, RMA with Replacement, RMA with Receipt and No Credit, or RMA
with Receipt and Credit

Restrictions on DISTINCT and UNIQUE Queries

DISTINCT , UNIQUE

Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

Restrictions on DISTINCT and UNIQUE Queries These types of queries are subject to the following restrictions:

• When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
• You cannot specify DISTINCT if the select_list contains LOB columns.

How to find which are application patches installed from back end

SELECT fav.application_name,
DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl fav, apps.fnd_product_installations fpi
WHERE fav.application_id = fpi.application_id;

Forms Personalization back end tables in oracle apps

• FND_FORM_CUSTOM_RULES
• FND_FORM_CUSTOM_SCOPES
• FND_FORM_CUSTOM_ACTIONS
• FND_FORM_CUSTOM_PARAMS
• FND_FORM_CUSTOM_PROP_VALUES
• FND_FORM_CUSTOM_PROP_LIST

Where to see the pricing Modifier / Qualifiers related data in Oracle Order Management

Step 1 : Open the Sales order workbench
Step 2 : Query the Sales order
Step 3: Click on the Actions button of Header / Line
Step 4: select the View Adjustments.

IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement 11g new sql feature

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows. This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

How to define Order Import source in Order Management

When you are planning to import the orders from different sources, you need to setup the order source.
Here are steps to setup of the Order Import Source.

Once you login to Order Management switch to Order Management Superuser.

Setup --> Orders --> Import Sources

Once the Import Source form is opened, Click on the new button or press Ctrl+ down arrow and then
Enter the Order Import Source , Description and the check the Enabled check box. Here is screen shot for the same.

REUSE SETTINGS in the alter trigger

Prevents Oracle Database from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.

ALTER TRIGGER oe.get_bal COMPILE REUSE SETTINGS

PURGE new SQL feature in Oracle 11g

Purpose

Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;

Caution:

You cannot roll back a PURGE statement, nor can you recover an object after it is purged.

Prerequisites

The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.

Semantics

TABLE or INDEX

Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.

• If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
• System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.

When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.

RECYCLEBIN

Use this clause to purge the current user's recycle bin. Oracle Database will remove all objects from the user's recycle bin and release all space associated with objects in the recycle bin.

DBA_RECYCLEBIN

This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.

TABLESPACE tablespace

Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.

USER user Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.

Examples

Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:

PURGE TABLE test;

To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to 

the following statement. (The system-generated name will differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;

How to copy the FND tables data from one instance to another instance using FNDLOAD

FNDLOAD apps/apps@devdb 0 Y DOWNLOAD testcfg.lct out.ldt FND_APPLICATION_TL APPSNAME=FND

how to insert the data into FND_TERRITORIES from back end

Using the below package we can insert / update the data into FND_TERRITORIES_TL and FND_TERRITORIES.

   fnd_territories_pkg.load_row (

      x_territory_code=> :territory_code,

      x_eu_code=> :eu_code,

      x_iso_numeric_code=> :iso_numeric_code,

      x_alternate_territory_code=> :alternate_territory_code,

      x_nls_territory=> :nls_territory,

      x_address_style=> :address_style,

      x_address_validation=> :address_validation,

      x_bank_info_style=> :bank_info_style,

      x_bank_info_validation=> :bank_info_validation,

      x_territory_short_name=> :territory_short_name,

      x_description=> :description,

      x_owner=> :owner,

      x_last_update_date=> :last_update_date,

      x_custom_mode=> :custom_mode,

      x_obsolete_flag=> :obsolete_flag,

      x_iso_territory_code=> :iso_territory_code

   );

Apart from the above package we need to use the below package to insert the data into FND_CURRENCIES

fnd_currencies_pkg.LOAD_ROW (  X_CURRENCY_CODE in VARCHAR2,

  X_DERIVE_EFFECTIVE in DATE,

  X_DERIVE_TYPE in VARCHAR2,

  X_GLOBAL_ATTRIBUTE1 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE2 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE3 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE4 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE5 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE6 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE7 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE8 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE9 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE10 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE11 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE12 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE13 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE14 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE15 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE16 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE17 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE18 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE19 in VARCHAR2,

  X_GLOBAL_ATTRIBUTE20 in VARCHAR2,

  X_DERIVE_FACTOR in NUMBER,

  X_ENABLED_FLAG in VARCHAR2,

  X_CURRENCY_FLAG in VARCHAR2,

  X_ISSUING_TERRITORY_CODE in VARCHAR2,

  X_PRECISION in NUMBER,

  X_EXTENDED_PRECISION in NUMBER,

  X_SYMBOL in VARCHAR2,

  X_START_DATE_ACTIVE in DATE,

  X_END_DATE_ACTIVE in DATE,

  X_MINIMUM_ACCOUNTABLE_UNIT in NUMBER,

  X_CONTEXT in VARCHAR2,

  X_ATTRIBUTE1 in VARCHAR2,

  X_ATTRIBUTE2 in VARCHAR2,

  X_ATTRIBUTE3 in VARCHAR2,

  X_ATTRIBUTE4 in VARCHAR2,

  X_ATTRIBUTE5 in VARCHAR2,

  X_ATTRIBUTE6 in VARCHAR2,

  X_ATTRIBUTE7 in VARCHAR2,

  X_ATTRIBUTE8 in VARCHAR2,

  X_ATTRIBUTE9 in VARCHAR2,

  X_ATTRIBUTE10 in VARCHAR2,

  X_ATTRIBUTE11 in VARCHAR2,

  X_ATTRIBUTE12 in VARCHAR2,

  X_ATTRIBUTE13 in VARCHAR2,

  X_ATTRIBUTE14 in VARCHAR2,

  X_ATTRIBUTE15 in VARCHAR2,

  X_ISO_FLAG in VARCHAR2,

  X_GLOBAL_ATTRIBUTE_CATEGORY in VARCHAR2,

  X_NAME in VARCHAR2,

  X_DESCRIPTION in VARCHAR2,

  X_OWNER in VARCHAR2)