Form Personalization - Launch program

If you want to launch any program using menu button in oracle apps, please follow below simple steps

1) Add personalization to define  menu entry with label which will then be visible in Tools menu of your application

2) You can see this menu entry in Tools drop-down of order form - OEXOEORD like below

3) Next, add personalization to define execute action when you click on this menu i.e. what program should run when you click on this menu button. You can also pass parameters to this program from your form as i have passed order header id in this example

Create CSV File from table

CREATE OR REPLACE PROCEDURE CREATE_CSV (p_query IN VARCHAR2)
AS
   l_cur              PLS_INTEGER;
   l_cur_rows         PLS_INTEGER;
   l_column_cnt       PLS_INTEGER;
   l_desc_tab         DBMS_SQL.desc_tab;
   l_buffer           VARCHAR2 (32767);
   l_file             UTL_FILE.file_type;
   l_string           VARCHAR2 (15);


   g_record_cnt       NUMBER := 0;
   g_directory_path   VARCHAR2 (500) := '/usr/tmp'; -- path must be present in utl_file_dir of v$parameter
   g_file_name        VARCHAR2 (250) := 'ra_terms';
   g_seperator        VARCHAR2 (5) := ',';
BEGIN
   g_record_cnt := 0;

   DBMS_OUTPUT.PUT_LINE (' Start writing in CSV file :' || g_file_name);

   l_cur := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (l_cur, p_query, DBMS_SQL.native);
   DBMS_SQL.describe_columns (l_cur, l_column_cnt, l_desc_tab);

   FOR i IN 1 .. l_column_cnt
   LOOP
      DBMS_SQL.define_column (l_cur,
                              i,
                              l_buffer,
                              32767);
   END LOOP;

   l_cur_rows := DBMS_SQL.EXECUTE (l_cur);
   l_file :=
      UTL_FILE.fopen (g_directory_path,
                      g_file_name || '.csv',
                      'w',
                      32767);

   FOR i IN 1 .. l_column_cnt
   LOOP
      IF i > 1
      THEN
         UTL_FILE.put (l_file, g_seperator);
      END IF;

      UTL_FILE.put (l_file, l_desc_tab (i).col_name);
   END LOOP;

   UTL_FILE.new_line (l_file);

   LOOP
      EXIT WHEN DBMS_SQL.fetch_rows (l_cur) = 0;

      FOR i IN 1 .. l_column_cnt
      LOOP
         IF i > 1
         THEN
            UTL_FILE.put (l_file, g_seperator);
         END IF;

         DBMS_SQL.COLUMN_VALUE (l_cur, i, l_buffer);

         IF INSTR (l_buffer, '"') != 0
         THEN
            SELECT REPLACE (l_buffer, '"', '""') INTO l_buffer FROM DUAL;
         END IF;

         IF INSTR (l_buffer, ',') != 0
         THEN
            l_string := '"';
            l_buffer := l_string || l_buffer || l_string;
         ELSE
            l_string := '';
         END IF;

         UTL_FILE.put (l_file, l_buffer);
      END LOOP;

      g_record_cnt := g_record_cnt + 1;
      UTL_FILE.new_line (l_file);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      ' Number Of rows inserted in CSV : ' || g_record_cnt);

   UTL_FILE.fclose (l_file);

   DBMS_OUTPUT.PUT_LINE (' End procedure');
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      IF DBMS_SQL.is_open (l_cur)
      THEN
         DBMS_SQL.close_cursor (l_cur);
      END IF;

      DBMS_OUTPUT.PUT_LINE (
         'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250));
END CREATE_CSV;

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

-- Call above procedure to generate CSV file data from ra_terms table

DECLARE
BEGIN
   create_csv ('SELECT * FROM ra_terms');
END;

OE_DEPENDENCIES_EXTN in Order Management

In order management, sometimes defaulting rules are driven by source and dependant attributes. This means that whenever you change source, you can derive dependant attribute value based on source value.

For example, if you give order type as "Standard", you can derive value for "Shipping Method" from transaction type setup using pl/sql API. But this dependency between order type and shipping method has to be defined in file $ONT_TOP/patch/115/sql/OEXUDEPB.pls. If you open this file, you  can see dependency written as below between order type and shipping method. Hence your defaulting rule for shipping method will work fine just by writing simple logic in pl/sql API.

IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
   --  Populate dependent attributes for one source at a time.
   l_index := OE_HEADER_UTIL.G_ORDER_TYPE * G_MAX ;
   g_dep_tbl(l_index +4 ).attribute  := OE_HEADER_UTIL.G_SHIPPING_METHOD;
............

However, there are many attributes for which dependency is not defined in file OEXUDEPB.pls. So oracle has provided an extension package - OE_DEPENDENCIES_EXTN which we also call as hook.

You can write simple piece of code in this package and compile it to define dependency between any of the OM attributes.

I am giving one example each for header and line, you can apply the same for any of the attributes in order management form

Example 1 (Header Tab): If you want to derive value for FOB_POINT based on value input in END_CUSTOMER, you can perform below steps to achieve it.

a) Define your defaulting rule for Order Header entity, Attribute - Shipping method and source as PL/SQL API. Write simple logic in your pl/sql procedure to derive fob_point value from hz_cust_accounts based on end_customer_id value (cust_account_id)                
 
b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.

IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN

x_extn_dep_tbl(l_index).source_attribute := OE_HEADER_UTIL.G_END_CUSTOMER;
x_extn_dep_tbl(l_index).dependent_attribute := OE_HEADER_UTIL.G_FOB_POINT;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';      
l_index := l_index + 1;                

c) Run concurrent program " Defaulting Generator" from SRS window

Example 2 (Line Tab): If you want to derive value for DEMAND_CLASS based on value input in SCHEDULE_SHIP_DATE, you can perform below steps to achieve it.

a) Define your defaulting rule for Order Line entity, Attribute - Demand Class and source as PL/SQL API. Write your own logic in pl/sql procedure to derive demand_class value based on SSD value
 
b) Write below code in package - OE_DEPENDENCIES_EXTN and compile it.

ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN

x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_SCHEDULE_SHIP_DATE;
x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_DEMAND_CLASS;
x_extn_dep_tbl(l_index).enabled_flag := 'Y';      
l_index := l_index + 1;

c) Run concurrent program " Defaulting Generator" from SRS window