Useful Scripts for Discoverer

--List of Business Areas
SELECT ba_name "Business Area"
     , ba_created_by "Creator"
     , ba_created_date "Creation Date"
     , ba_updated_by "Updated By "
     , ba_updated_date "Last Update Date"
     , ba_id
  FROM eul5_us.eul5_bas
/
--List of Folders
SELECT   b.ba_name
       , f.obj_name folder_name
       , f.obj_id
       , f.obj_ext_owner owner
    FROM eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE 1 = 1
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
/
--List of Folder Items
SELECT   i.exp_name item_name
       , i.exp_id
       , i.it_ext_column
       , f.obj_name folder_name
       , b.ba_name
    FROM eul5_us.eul5_expressions i
       , eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE f.obj_id = i.it_obj_id
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (i.exp_name) LIKE UPPER ('%'||'&ITEM_NAME'||'%')
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
       , i.exp_name
/
--List of Workbooks
SELECT doc_name "Document"
     , doc_developer_key
     , doc_description "Description"
  FROM eul5_us.eul5_documents
/

Way to search a String for LONG data type In oracle using Object types and Pipeline Functions

CREATE OR REPLACE TYPE XX_LONG_DATA_REC_TYPE AS OBJECT(OBJECT_NAME VARCHAR2(240), LONG_DATA_TEXT CLOB);
/

CREATE OR REPLACE TYPE XX_LONG_DATA_TBL AS TABLE OF XX_LONG_DATA_REC_TYPE
/

CREATE OR REPLACE FUNCTION XX_SEARCH_STRING( P_SEARCH_STRING VARCHAR2 )
   RETURN XX_LONG_DATA_TBL
   PIPELINED
IS
   XX_LONG_DATA_REC            XX_LONG_DATA_REC_TYPE;
BEGIN
   FOR Z IN ( SELECT VIEW_NAME, TEXT
                FROM DBA_VIEWS
               WHERE ROWNUM <=10 )
   LOOP
      IF ( UPPER(Z.TEXT) LIKE UPPER(P_SEARCH_STRING) )
      THEN
         XX_LONG_DATA_REC         := XX_LONG_DATA_REC_TYPE( Z.VIEW_NAME, Z.TEXT );
         PIPE ROW ( XX_LONG_DATA_REC );
      END IF;
   END LOOP;
END;
/

Script to test the same 

select * from table(xx_search_string('%SELECT%'));



Another way without creating any DB object

DECLARE

   L_CLOB_STRING                 CLOB;

   L_LONG_STRING                 LONG;

   L_OBJECT_NAME                 VARCHAR2( 240 );

BEGIN

   SELECT VIEW_NAME, TEXT

     INTO L_OBJECT_NAME, L_LONG_STRING

     FROM DBA_VIEWS

    WHERE ROWNUM = 1;

   L_CLOB_STRING              := L_LONG_STRING;

   IF UPPER( L_CLOB_STRING ) LIKE '%SELECT%'

   THEN

      DBMS_OUTPUT.PUT_LINE( 'OBJECT NAME : ' || L_OBJECT_NAME );

   ELSE

      DBMS_OUTPUT.PUT_LINE( 'SELECT SEARCH STRING NOT FOUND IN THE OBJECT NAME :  ' || L_OBJECT_NAME );

   END IF;

END;

Script to delete the Task in Oracle CRM (JTF_TASKS_PUB.DELETE_TASK)

SET serveroutput on

DECLARE
   l_object_ver_num                             jtf_tasks_v.object_version_number%TYPE := 1;
   l_task_status_id                             jtf_tasks_v.task_status_id%TYPE := 9;
   l_task_status_name                           jtf_tasks_v.task_status%TYPE;
   l_task_id                                    jtf_tasks_v.task_id%TYPE := 1333920;
   l_task_number                                jtf_tasks_v.task_number%TYPE;
   l_return_status                              VARCHAR2 (1);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2 (1000);
BEGIN
   jtf_tasks_pub.delete_task (p_api_version                           => 1.0
                            , p_init_msg_list                         => fnd_api.g_true
                            , p_commit                                => fnd_api.g_false
                            , p_object_version_number                 => l_object_ver_num
                            , p_task_id                               => l_task_id
                            , p_task_number                           => l_task_number
                            , p_delete_future_recurrences             => fnd_api.g_false
                            , x_return_status                         => l_return_status
                            , x_msg_count                             => l_msg_count
                            , x_msg_data                              => l_msg_data
                             );

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      IF l_msg_count > 0
      THEN
         l_msg_data                                     := NULL;

         FOR i IN 1 .. l_msg_count
         LOOP
            l_msg_data                                     := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
         END LOOP;

         fnd_message.set_encoded (l_msg_data);
         DBMS_OUTPUT.put_line (SUBSTR (l_msg_data
                                     , 1.1
                                     , 200
                                      ));
      END IF;

      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
      DBMS_OUTPUT.put_line ('Task Status = ' || l_task_status_id);
      DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
      COMMIT;
   END IF;
END;
/

Script to Update the task in Oracle CRM (JTF_TASKS_PUB.UPDATE_TASK)

DECLARE
   l_object_ver_num                             jtf_tasks_v.object_version_number%TYPE := 1;
   l_task_status_id                             jtf_tasks_v.task_status_id%TYPE := 9;
   l_task_status_name                           jtf_tasks_v.task_status%TYPE;
   l_task_id                                    jtf_tasks_v.task_id%TYPE := 1333920;
   l_task_number                                jtf_tasks_v.task_number%TYPE;
   l_return_status                              VARCHAR2 (1);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2 (1000);
BEGIN
   JTF_TASKS_PUB.UPDATE_TASK (p_api_version                           => 1.0
                            , p_init_msg_list                         => fnd_api.g_true
                            , p_commit                                => fnd_api.g_false
                            , p_object_version_number                 => l_object_ver_num
                            , p_task_id                               => l_task_id
                            , p_task_number                           => l_task_number
                            , p_task_status_id                        => l_task_status_id
                            , p_source_object_type_code               => 'TASK'
                            , p_source_object_id                      => l_task_id
                            , p_source_object_name                    => l_task_id
                            , x_return_status                         => l_return_status
                            , x_msg_count                             => l_msg_count
                            , x_msg_data                              => l_msg_data
                             );

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      IF l_msg_count > 0
      THEN
         l_msg_data                                     := NULL;

         FOR i IN 1 .. l_msg_count
         LOOP
            l_msg_data                                     := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
         END LOOP;

         fnd_message.set_encoded (l_msg_data);
         DBMS_OUTPUT.put_line (SUBSTR (l_msg_data
                                     , 1.1
                                     , 200
                                      ));
      END IF;

      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
      DBMS_OUTPUT.put_line ('Task Status = ' || l_task_status_id);
      DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
      COMMIT;
   END IF;
END;
/

Script to Create the tasks in Oracle CRM (JTF_TASKS_PUB.CREATE_TASK)

SET serveroutput on;

DECLARE
   l_user_name                                  fnd_user.user_name%TYPE := 'OPERATIONS';
   l_task_name                                  jtf_tasks_tl.task_name%TYPE := 'Task1';
   l_task_priority                              jtf_task_priorities_tl.NAME%TYPE := 'Medium';
   l_task_status                                jtf_task_statuses_tl.NAME%TYPE := 'Open';
   l_show_on_cal                                jtf_task_all_assignments.show_on_calendar%TYPE := 'N';
   l_planned_start_date                         DATE := SYSDATE;
   l_planned_end_date                           DATE := SYSDATE;
   l_user_id                                    NUMBER := 1318;   -- User Id for Operations - Select user_id from fnd_user where user_name = 'OPERATIONS';
   l_resource_id                                NUMBER := 199;   -- Resource Id for Operations - select resource_id from jtf_rs_resource_extns where user_name = 'OPERATIONS';
   l_task_type_id                               NUMBER := 9;   -- Meeting.
   l_task_status_id                             NUMBER := 10;   -- Open;
   l_task_priority_id                           NUMBER;
   l_task_id                                    NUMBER;
   l_return_status                              VARCHAR2 (1);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2 (1000);
BEGIN
   jtf_tasks_pub.create_task (p_api_version                           => 1.0
                            , p_init_msg_list                         => fnd_api.g_true
                            , p_commit                                => fnd_api.g_false
                            , p_task_name                             => l_task_name
                            , p_task_type_id                          => l_task_type_id
                            , p_task_status_id                        => l_task_status_id
                            , p_task_priority_id                      => l_task_priority_id
                            , p_owner_type_code                       => 'RS_EMPLOYEE'
                            , p_owner_id                              => l_resource_id
                            , p_show_on_calendar                      => l_show_on_cal
                            , p_planned_start_date                    => l_planned_start_date
                            , p_planned_end_date                      => l_planned_end_date
                            , p_date_selected                         => 'P'
                            , x_return_status                         => l_return_status
                            , x_msg_count                             => l_msg_count
                            , x_msg_data                              => l_msg_data
                            , x_task_id                               => l_task_id
                             );

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      IF l_msg_count > 0
      THEN
         l_msg_data                                     := NULL;

         FOR i IN 1 .. l_msg_count
         LOOP
            l_msg_data                                     := l_msg_data || ' ' || fnd_msg_pub.get (1, 'F');
         END LOOP;

         fnd_message.set_encoded (l_msg_data);
         DBMS_OUTPUT.put_line (l_msg_data);
      END IF;

      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('Task Id = ' || l_task_id);
      DBMS_OUTPUT.put_line ('Return Status = ' || l_return_status);
      COMMIT;
   END IF;
END;
/

Function to get the list of Sting with covered with Quotes for individual columns in the List.

CREATE OR REPLACE FUNCTION get_not_in_clause (
   p_list_of_columns                    IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   ---------------------------------------------------------------------------------
   -- Created By : Vijaykumar
   -- Creation Date :  15-Feb-2014
   -- purpose : purpose of this function is to split the string which is comma or any other
   --           separator and return the string with inverted quote string for each column in
   --          parameter list.
   --          ex :- 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY'
   --          the output of the this function will as below
   --          'CREATED_BY','CREATION_DATE','LAST_UPDATE_DATE','LAST_UPDATED_BY'
   -------------------------------------------------------------------------------
   l_count                                      NUMBER;
   l_string                                     VARCHAR2 (50);
   l_concatenated_string                        VARCHAR2 (2000);
BEGIN
   ------------------------------------------------------------------------
   --- below select statement is used to  Get the total count of Commas in
   --- the parameter string and adding 1 to consider the last column in the
   --- parameter list
   ------------------------------------------------------------------------
   BEGIN
      SELECT (LENGTH (REGEXP_REPLACE (p_list_of_columns, '[^,]')) / LENGTH (',')) + 1
        INTO l_count
        FROM DUAL;
   END;

   DBMS_OUTPUT.put_line ('Count Of Comma occurenece : ' || l_count);

   IF l_count IS NULL
   THEN
      l_concatenated_string                          := '''' || p_list_of_columns || '''';
   ELSE
      l_count                                        := l_count;
      DBMS_OUTPUT.put_line ('Count Of Comma occurenece 1: ' || l_count);

      FOR i IN 1 .. l_count
      LOOP
         DBMS_OUTPUT.put_line ('i : ' || i);

         SELECT REGEXP_SUBSTR (p_list_of_columns
                             , '[^,]+'
                             , 1
                             , i
                              )
           INTO l_string
           FROM DUAL;

         DBMS_OUTPUT.put_line ('String : ' || l_string);

         IF l_concatenated_string IS NULL
         THEN
            l_concatenated_string                          := '''' || l_string || '''';
         ELSE
            l_concatenated_string                          := l_concatenated_string || ',' || '''' || l_string || '''';
         END IF;
      END LOOP;
   END IF;

   RETURN l_concatenated_string;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UNexpected Error : ' || SUBSTR (SQLERRM
                                                           , 1
                                                           , 250
                                                            ));
      RETURN NULL;
END;

Scrpt to get the Nth Occurrence of a string in the String

User below script to get the Nth occurrence of the string within a string separated by any string separator.

Ex.. i have a string like 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY' and within this string if i want to split the string for Individual column then use the below script.


SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

Please find below the use and output for the above select statement.

SQL> SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL;

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 1

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUB

----------

CREATED_BY

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 2

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR

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

CREATION_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 3

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('C

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

LAST_UPDATE_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 4

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('

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

LAST_UPDATED_BY

SQL> 

How to split comma separated string and pass to IN clause of select statement/ Create Comma Separated String using Select statement

SELECT   COLUMN_NAME
       , COLUMN_ID
    FROM DBA_TAB_COLUMNS
   WHERE TABLE_NAME = :P_TABLE_NAME
     AND OWNER = :P_OWNER
     AND COLUMN_NAME NOT IN (SELECT REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      )
                                   FROM DUAL
                             CONNECT BY REGEXP_SUBSTR (:L_STRING
                                                     , '[^,]+'
                                                     , 1
                                                     , LEVEL
                                                      ) IS NOT NULL)
ORDER BY COLUMN_ID ASC

WHERE AS THE l_STRING CAN BE PASSED AS 'COLUMN1,COLUMN2,COLUMN3'

Use Below SQL to create comma separated string for multiple rows.
Limitations : The below SQL has String length Constraint. If the length of number of rows in a column getting converted into one row searating each other by using any separator, in that case the length should not exceed 2000 characters. 

SELECT SUBSTR(SYS_CONNECT_BY_PATH(''''||lookup_code||''''
                                    , ',')
                , 2)
              lookup_code
      FROM (SELECT lookup_code
                 , ROW_NUMBER() OVER (ORDER BY lookup_code) rownumber
                 , COUNT( *) OVER () cunt
              FROM fnd_lookup_values
             WHERE lookup_type = 'ITEM_TYPE'
               )
     WHERE rownumber = cunt
START WITH rownumber = 1

CONNECT BY rownumber = PRIOR rownumber + 1

How to Search a String for a LONG/CLOB datatype column

CREATE OR REPLACE FUNCTION apps.xx_search_long(
   p_obj_type                 IN             VARCHAR2
 , p_obj_name                 IN             VARCHAR2
 , p_owner                    IN             VARCHAR2
 , p_search_str               IN             VARCHAR2
)
   RETURN VARCHAR2
IS
   temporary_varchar                                 VARCHAR2(31000);
   v1                                                LONG;
   v_src_code                                        CLOB;
   v_length                                          NUMBER;
BEGIN
   BEGIN
      SELECT DBMS_METADATA.get_ddl(p_obj_type
                                 , p_obj_name
                                 , p_owner
                                  )
        INTO v_src_code
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line('Exception : ' || SUBSTR(SQLERRM
                                                     , 1
                                                     , 150
                                                      ));
   END;
   v_length                                       := DBMS_LOB.getlength(v_src_code);
   DBMS_OUTPUT.put_line('Length of CLOB - ' || v_length);
   IF DBMS_LOB.INSTR(v_src_code
                   , p_search_str
                    ) > 0
   THEN
      DBMS_OUTPUT.put_line('Found ....');
      RETURN('FOUND');
   ELSE
      DBMS_OUTPUT.put_line('Not Found ....');
      RETURN('NOT FOUND');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN('NOT_FOUND');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line('Error .' || SQLERRM);
      RETURN('EXCEPTION' || SUBSTR(SQLERRM
                                 , 1
                                 , 150
                                  ));
END;
/

Sample Scripts to Convert the Rows into One Column with seperator

 SELECT header_id, RTRIM(EXTRACT(EXML,'//ordereditem/text()'),',') ORDERED_ITEM
   FROM (SELECT E.header_id,
          xmlelement("HDR",
              XMLAGG(xmlelement("ordereditem",E.ordered_item||',') ORDER BY ordered_item))EXML
   FROM oe_order_lines_all E
   where header_id= 278563
   GROUP BY E.header_id);
***************************************************************************
SELECT header_id, LTRIM( SYS_CONNECT_BY_PATH( ORDERED_ITEM, ',' ), ',' ) iTEM_STRING
      FROM (SELECT LINE_ID, HEADER_ID, ORDERED_ITEM, ROW_NUMBER( ) OVER (PARTITION BY HEADER_ID ORDER BY ROWNUM) RN
              FROM OE_ORDER_LINES_aLL
             WHERE header_id = 278563)
     WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY HEADER_ID = PRIOR HEADER_ID
       AND RN = PRIOR RN + 1
START WITH rn = 1
  ORDER BY HEADER_ID;
***************************************************************************
SELECT   header_id
       , RTRIM(XMLAGG(XMLELEMENT(e
                               , ordered_item || ','
                                )).EXTRACT('//text()')
             , ','
              ) ordered_item
    FROM oe_order_lines_All
    where header_id = 278563
GROUP BY header_Id
***************************************************************************
SELECT     header_id
         , SUBSTR(SYS_CONNECT_BY_PATH(ordered_item
                                    , ','
                                     )
                , 2
                 ) name_list
      FROM (SELECT ordered_item
                 , header_id
                 , COUNT(*) OVER(PARTITION BY header_id) cnt
                 , ROW_NUMBER() OVER(PARTITION BY header_id ORDER BY ordered_item) seq
              FROM oe_order_lines_all
             WHERE header_id = 278563)
     WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq
       AND PRIOR header_id = header_id;

Script to Calculate the Price and Tax details of the Product Line in the Service Contract and Update to the Service Contract.( oks_qp_int_pvt.compute_price)

Please use this Script Carefully as the API updates the values to the base tables.


DECLARE
   l_input_details                                   oks_qp_pkg.input_details;
   l_output_details                                  oks_qp_pkg.price_details;
   l_modif_details                                   qp_preq_grp.line_detail_tbl_type;
   l_pb_details                                      oks_qp_pkg.g_price_break_tbl_type;
   l_return_status                                   VARCHAR2(20);
   l_msg_count                                       NUMBER;
   l_msg_data                                        VARCHAR2(2000);
   l_status_tbl                                      oks_qp_int_pvt.pricing_status_tbl;
   l_final_status_tbl                                oks_qp_int_pvt.pricing_status_tbl;
   l_count                                           NUMBER;
   l_msg_index_out                                   NUMBER;
   l_num                                             NUMBER;
   l_error_message                                   VARCHAR2(2000);
BEGIN
   l_num                                          := 10;
   l_input_details.intent                         := 'SP';
--   l_input_details.lse_id                         := 1;
--   l_input_details.chr_id                         := 1549063;
--   l_input_details.line_id                        := 364326736554344799961890872824534166768;
   l_input_details.subline_id                     := 364326736554364142775004706891329465584; --364326736554353262442628175228757110000;
   l_num                                          := 20;
   oks_qp_int_pvt.compute_price(p_api_version                 => 1.0
                              , p_init_msg_list               => 'T'
                              , p_detail_rec                  => l_input_details
                              , x_price_details               => l_output_details
                              , x_modifier_details            => l_modif_details
                              , x_price_break_details         => l_pb_details
                              , x_return_status               => l_return_status
                              , x_msg_count                   => l_msg_count
                              , x_msg_data                    => l_msg_data
                               );
   l_num                                          := 30;

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      l_num                                          := 40;

      IF l_msg_count > 0
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            l_num                                          := 50;
            apps.fnd_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);

            IF l_error_message IS NULL
            THEN
               l_error_message                                := SUBSTR(l_msg_data, 1, 250);
            ELSE
               l_error_message                                := l_error_message || ' /' || SUBSTR(l_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line('*****************************************');
      ELSE
         apps.fnd_msg_pub.get(p_msg_index                   => 1, p_encoded => fnd_api.g_false, p_data => l_msg_data
                            , p_msg_index_out               => l_msg_index_out);
         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_msg_data);
         DBMS_OUTPUT.put_line('*****************************************');
      END IF;
   ELSE
      l_num                                          := 60;
      DBMS_OUTPUT.put_line('API Success');
      DBMS_OUTPUT.put_line('Product Quantity : ' || l_output_details.prod_qty);
      DBMS_OUTPUT.put_line('Product Quantity UOM : ' || l_output_details.prod_qty_uom);
      DBMS_OUTPUT.put_line('Service Quantity : ' || l_output_details.serv_qty);
      DBMS_OUTPUT.put_line('Service Quantity UOM : ' || l_output_details.serv_qty_uom);
      DBMS_OUTPUT.put_line('Product Price List Id : ' || l_output_details.prod_price_list_id);
      DBMS_OUTPUT.put_line('Service Price List Id : ' || l_output_details.serv_price_list_id);
      DBMS_OUTPUT.put_line('Product Price List Line Id : ' || l_output_details.prod_price_list_line_id);
      DBMS_OUTPUT.put_line('Service Price List Line Id : ' || l_output_details.serv_price_list_line_id);
      DBMS_OUTPUT.put_line('Product List Unit Price : ' || l_output_details.prod_list_unit_price);
      DBMS_OUTPUT.put_line('Service List Unit Price : ' || l_output_details.serv_list_unit_price);
      DBMS_OUTPUT.put_line('Product Adjustment Unit Price : ' || l_output_details.prod_adj_unit_price);
      DBMS_OUTPUT.put_line('Service Adjustment Unit Price : ' || l_output_details.serv_adj_unit_price);
      DBMS_OUTPUT.put_line('Product Priced Quantity : ' || l_output_details.prod_priced_qty);
      DBMS_OUTPUT.put_line('Product Priced UOM : ' || l_output_details.prod_priced_uom);
      DBMS_OUTPUT.put_line('Product Extension Amount : ' || l_output_details.prod_ext_amount);
      DBMS_OUTPUT.put_line('Service Priced Quantity : ' || l_output_details.serv_priced_qty);
      DBMS_OUTPUT.put_line('Service Priced UOM : ' || l_output_details.serv_priced_uom);
      DBMS_OUTPUT.put_line('Service Extended Amount : ' || l_output_details.serv_ext_amount);
      DBMS_OUTPUT.put_line('Service OPerand : ' || l_output_details.serv_operand);
      DBMS_OUTPUT.put_line('Service Operator : ' || l_output_details.serv_operator);
      DBMS_OUTPUT.put_line('Status Code : ' || l_output_details.status_code);
      DBMS_OUTPUT.put_line('Status Text : ' || l_output_details.status_text);

      IF l_modif_details.COUNT > 0
      THEN
         l_num                                          := 70;

         FOR i IN l_modif_details.FIRST .. l_modif_details.LAST
         LOOP
            l_num                                          := 80;
            DBMS_OUTPUT.put_line('List Price : ' || l_modif_details(i).list_price);
         END LOOP;
      END IF;

      l_num                                          := 90;

      IF l_pb_details.COUNT > 0
      THEN
         l_num                                          := 100;

         FOR i IN l_pb_details.FIRST .. l_pb_details.LAST
         LOOP
            l_num                                          := 110;
            DBMS_OUTPUT.put_line('Price Break List Price : ' || l_pb_details(i).list_price);
         END LOOP;
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(l_num || ' Unexpected Error : ' || SUBSTR(SQLERRM, 1, 250));
END;