FND_MESSAGE Displaying only message code and not message text

--Application: The short name of the application this message is associated with. This short name references the application
--you associate with your message when
--you define it using the Messages form.
--
--Name: The message name that identifies your message. This name is identical to the name you use when you define your message

--using the Messages form. Message Dictionary names are not case sensitive (for example, MESSAGE_NAME is the same name as message_name).

--

--Procedure FND_MESSAGE.SET_NAME(application IN varchar2,
--                               name IN varchar2);
DECLARE
   l_message VARCHAR2 (4000);

BEGIN
   FND_MESSAGE.SET_NAME ('XXTST', 'XX_TEST_MSG_NAME');
   l_message := FND_MESSAGE.GET;   DBMS_OUTPUT.put_line(l_message);

END;

--Note:-IF FND_MESSAGE.GET displays only the message code whatever
--is passed in the 2nd parameter and not message text, then, it
--means either the "Application Short Name" or the "Message Name" is
--incorrect 
--Also, make sure the "Generate Messages" Concurrent Program is run
--from "Application Developer" responsibility

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

SELECT Statement to retrieve Oracle AOL Descriptive Flexfield Segment(DFF) Details

-- Application Developer > Navigate > Flexfields > Descriptive > Segments
-- Search for relevant Title, second half of the screen, under "Context Field Values"
-- lists the main parts of the Flexfield
-- Click into a name on the "Context Field Values" section in the lower part of the
-- screen, and click "Segments"
-- This lists the bits users see in Core Applications when they click into the DFF
-- plus shows if there is a LOV linked to the field
-- Following SELECT statement retrieves DFF details related to an application,DFF
-- title, DFF Context or
-- the entire application if no parameter is passed

-- Optional Parameters:-
-- P_TITLE -- DFF Title
-- P_APPLICATION -- Application Name
-- P_DESC_FLEX_CD, DFF Context 


  SELECT fat.application_name
       , fdfv.title
       , fdfcv.descriptive_flex_context_code context
       , fdfcuv.column_seq_num num
       , fdfcuv.end_user_column_name name
       , fdfcuv.application_column_name column_
       , ffvs.flex_value_set_name value_set
       , ffvs.description value_set_description
       , fdfcuv.required_flag
       , fdfcuv.display_flag
       , fdfcuv.enabled_flag
       , fdfcuv.security_enabled_flag
       , fdfcuv.default_value
    FROM apps.fnd_descriptive_flexs_vl fdfv
       , applsys.fnd_application_tl fat
       , apps.fnd_descr_flex_contexts_vl fdfcv
       , apps.fnd_descr_flex_col_usage_vl fdfcuv
       , applsys.fnd_flex_value_sets ffvs
   WHERE fdfv.application_id = fat.application_id
     AND fdfcv.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
     AND fdfcv.descriptive_flexfield_name = fdfcuv.descriptive_flexfield_name
     AND fdfcv.descriptive_flex_context_code =  
                                       fdfcuv.descriptive_flex_context_code
     AND fdfcuv.flex_value_set_id = ffvs.flex_value_set_id(+)
     AND fdfv.title = NVL(:P_TITLE,fdfv.title)
     AND fat.application_name = NVL(:P_APPLICATION,fat.application_name)
     AND fdfcv.descriptive_flex_context_code =
                         NVL(:P_DESC_FLEX_CD,fdfcv.descriptive_flex_context_code)

ORDER BY fat.application_name

       , fdfv.title

       , fdfcv.descriptive_flex_context_code

       , fdfcuv.column_seq_num;

How to enable the MOAC parameter “Operating Unit” for a Concurrent Program in Oracle EBS R12

You can notice that the new field “Operating Unit” which is new field in R12 and by default is in disabled mode:-

With the introduction of multi-org access control in release 12, an applications responsibility
can access multiple operating units. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of 'S' for single operating unit or 'M'(concurrent program will be in this mode by default) for multiple operating units. If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The “Operating Unit” field/parameter is known as “Reporting Context” in MOAC(Multi Org Access Control) terminology.

For a concurrent program though there may be a need to only run for Single Operating Unit, though the Responsibility has access to Multiple Operating Units’ data based on the “MO: Security Profile” profile option.

Running a particular concurrent program for only a Single Operating Unit can be achieved by a simple update statement or from the front-end application:-

--Execute the following update statement in the database for the particular concurrent program you want set to run for "Single Operating Unit"

UPDATE fnd_concurrent_programs

   SET multi_org_category = 'S'

 WHERE concurrent_program_name = '<your program name>';

Now we will see how we can achieve from the Oracle Applications front-end.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil icon of your program 

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit
     Mode' field
7. Save changes by clicking on 'Apply' button

8. Change responsibility to the responsibility where the “Concurrent Program” can be run, in

    this example “TESTOU” is assigned to “AR Super User” responsibility                              

9. Select the “Concurrent Program” from the “SRS Window”, now you can see that “Operating 

    Unit” field is enabled and you can see the Operating Units in the LOV

10. This particular selected Operating Unit value can be accessed using the standard MOAC API: MO_GLOBAL.get_current_org_id

Note:- The return value for the “Operating Unit” reporting context is corresponding “ORG_ID” for the selected “Operating Unit Name”

How to update a Sales Order Line using OE_ORDER_PUB

-- OE_ORDER_PUB.PROCESS_ORDER will be used to update sales order line details in this example the Bill-to Site of the sales order line is updated.

 DECLARE

   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;

   l_header_adj_tbl             oe_order_pub.header_adj_tbl_type;

   l_line_adj_tbl               oe_order_pub.line_adj_tbl_type;

   l_header_scr_tbl             oe_order_pub.header_scredit_tbl_type;

   l_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;

   l_request_rec                oe_order_pub.request_rec_type;

   l_return_status              VARCHAR2(1000);

   l_msg_count                  NUMBER;

   l_msg_data                   VARCHAR2(1000);

   p_api_version_number         NUMBER := 1.0;

   p_init_msg_list              VARCHAR2(10) := fnd_api.g_false;

   p_return_values              VARCHAR2(10) := fnd_api.g_false;

   p_action_commit              VARCHAR2(10) := fnd_api.g_false;

   x_return_status              VARCHAR2(1);

   x_msg_count                  NUMBER;

   x_msg_data                   VARCHAR2(100);

   p_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

   x_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

   p_old_header_rec             oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

   p_header_val_rec             oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

   p_old_header_val_rec         oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

   p_header_adj_tbl             oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

   p_old_header_adj_tbl         oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

   p_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

   p_old_header_adj_val_tbl     oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

   p_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

   p_old_header_price_att_tbl   oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

   p_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

   p_old_header_adj_att_tbl     oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

   p_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

   p_old_header_adj_assoc_tbl   oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

   p_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

   p_old_header_scredit_tbl     oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

   p_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

   p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

   x_line_tbl                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

   p_old_line_tbl               oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

   p_line_val_tbl               oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

   p_old_line_val_tbl           oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

   p_line_adj_tbl               oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

   p_old_line_adj_tbl           oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

   p_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

   p_old_line_adj_val_tbl       oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

   p_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

   p_old_line_price_att_tbl     oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

   p_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

   p_old_line_adj_att_tbl       oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

   p_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

   p_old_line_adj_assoc_tbl     oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

   p_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

   p_old_line_scredit_tbl       oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

   p_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

   p_old_line_scredit_val_tbl   oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

   p_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

   p_old_lot_serial_tbl         oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

   p_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

   p_old_lot_serial_val_tbl     oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

   p_action_request_tbl         oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;

   x_header_val_rec             oe_order_pub.header_val_rec_type;

   x_header_adj_tbl             oe_order_pub.header_adj_tbl_type;

   x_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type;

   x_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type;

   x_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type;

   x_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type;

   x_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type;

   x_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type;

   x_line_val_tbl               oe_order_pub.line_val_tbl_type;

   x_line_adj_tbl               oe_order_pub.line_adj_tbl_type;

   x_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type;

   x_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type;

   x_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type;

   x_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type;

   x_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;

   x_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type;

   x_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type;

   x_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type;

   x_action_request_tbl         oe_order_pub.request_tbl_type;

   x_debug_file                 VARCHAR2(100);

   l_msg_index_out              NUMBER(10);

   l_line_tbl_index             NUMBER;

BEGIN

   dbms_output.ENABLE(1000000);

   fnd_global.Apps_initialize(1318, 21623, 660);

   -- pass in user_id, responsibility_id, and application_id 

   oe_msg_pub.initialize;

   oe_debug_pub.initialize;

   mo_global.Init ('ONT'); -- Required for R12

   fnd_global.Set_nls_context ('AMERICAN');

   mo_global.Set_policy_context ('S', 87); -- Required for R12

   --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); 

   --oe_debug_pub.SetDebugLevel(5); 

   --dbms_output.put_line('START OF NEW DEBUG'); 

   --This is to update a line to an existing order 

   l_line_tbl_index :=1;

   -- Changed attributes

   l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

   l_line_tbl(l_line_tbl_index).invoice_to_org_id := 322;

   -- Primary key of the entity i.e. the order line

   l_line_tbl(l_line_tbl_index).line_id := 388459;

   l_line_tbl(l_line_tbl_index).change_reason := 'Not provided';

   -- Indicates to process order that this is an update operation

   l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

   -- CALL TO PROCESS ORDER 

   oe_order_pub.Process_order (  p_api_version_number   => 1.0                 ,

                                 p_init_msg_list        => fnd_api.g_false     ,

                                 p_return_values        => fnd_api.g_false     ,

                                 p_action_commit        => fnd_api.g_false     ,

                                 x_return_status        => l_return_status     ,

                                 x_msg_count            => l_msg_count         ,

                                 x_msg_data             => l_msg_data          ,

                                 p_header_rec           => l_header_rec        ,

                                 p_line_tbl             => l_line_tbl          ,

                                 p_action_request_tbl   => l_action_request_tbl

                                 -- OUT PARAMETERS 

                                 , x_header_rec             => x_header_rec

                                 , x_header_val_rec         => x_header_val_rec

                                 , x_header_adj_tbl         => x_header_adj_tbl

                                 , x_header_adj_val_tbl     => x_header_adj_val_tbl

                                 , x_header_price_att_tbl   => x_header_price_att_tbl

                                 , x_header_adj_att_tbl     => x_header_adj_att_tbl

                                 , x_header_adj_assoc_tbl   => x_header_adj_assoc_tbl

                                 , x_header_scredit_tbl     => x_header_scredit_tbl

                                 , x_header_scredit_val_tbl => x_header_scredit_val_tbl

                                 , x_line_tbl               => x_line_tbl

                                 , x_line_val_tbl           => x_line_val_tbl

                                 , x_line_adj_tbl           => x_line_adj_tbl

                                 , x_line_adj_val_tbl       => x_line_adj_val_tbl

                                 , x_line_price_att_tbl     => x_line_price_att_tbl

                                 , x_line_adj_att_tbl       => x_line_adj_att_tbl

                                 , x_line_adj_assoc_tbl     => x_line_adj_assoc_tbl

                                 , x_line_scredit_tbl       => x_line_scredit_tbl

                                 , x_line_scredit_val_tbl   => x_line_scredit_val_tbl

                                 , x_lot_serial_tbl         => x_lot_serial_tbl

                                 , x_lot_serial_val_tbl     => x_lot_serial_val_tbl

                                 , x_action_request_tbl     => x_action_request_tbl

                              );

   --dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE); 

   -- Retrieve messages 

   dbms_output.Put_line('Line Id: ' ||x_line_tbl(l_line_tbl_index).line_id);

   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_msg_data      ,

                        p_msg_index_out => l_msg_index_out

                     );

      dbms_output.Put_line('message is: ' || l_msg_data);

      dbms_output.Put_line('message index is: ' || l_msg_index_out);

   END LOOP;

   -- Check the return status 

   IF l_return_status = fnd_api.g_ret_sts_success

   THEN

     dbms_output.put_line('Line Quantity Update Sucessful');

   ELSE

      dbms_output.put_line('Line Quantity update Failed');

   END IF;

END;

/

COMMIT;

--Note:- In R12 it is very important to have different input and output parameter/s to pass to the oe_order_pub API so as to successfully update the record