Friday, February 21, 2014

Query to get the Menu,Submenu and function details For Oracle Menus

  SELECT second.application_id "App ID"
       , second.application_name "App Name"
       , second.responsibility_id "Resp ID"
       , second.responsibility_name "Responsibility"
       , second.menu_id "Menu ID"
       , second.user_menu_name "Main Menu Name"
       , second.entry_sequence "Seq"
       , second.prompt "Prompt"
       , second.function_id "Function ID"
       , second.user_function_name "Function"
       , second.func_descrip "Function Descrip"
       , second.sub_menu_id "SubMenu ID"
       , second.sub_menu_name "SubMenu Name"
       , second.sub_seq "Sub Seq"
       , second.sub_prompt "SubPrompt"
       , second.sub_func_id "SubFunction ID"
       , second.sub_func "SubFunction"
       , second.sub_func_descrip "SubFunction Descrip"
       , second.sub_sub_menu_id "Sub-SubMenu ID"
       , second.grant_flag "Grant Flag"
       , second.resp_end_date "Resp End Date"
       , DECODE( exc.rule_type
               , 'F', ( SELECT 'Ex F: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.function_id = exc.action_id ) )
            excluded_function
       , DECODE( exc.rule_type
               , 'F', ( SELECT 'Ex SF: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_func_id = exc.action_id ) )
            excluded_sub_function
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex M: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.menu_id = exc.action_id ) )
            excluded_menu
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex SM: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_menu_id = exc.action_id ) )
            excluded_sub_menu
       , DECODE( exc.rule_type
               , 'M', ( SELECT 'Ex SSM: ' || exc.action_id
                          FROM fnd_form_functions_vl fnc
                         WHERE fnc.function_id = exc.action_id
                           AND second.sub_sub_menu_id = exc.action_id ) )
            excluded_sub_sub_menu
    FROM (  SELECT FIRST.application_id
                 , FIRST.application_name
                 , FIRST.responsibility_id
                 , FIRST.responsibility_name
                 , FIRST.end_date AS resp_end_date
                 , FIRST.menu_id
                 , FIRST.user_menu_name
                 , FIRST.entry_sequence
                 , FIRST.prompt
                 , FIRST.function_id
                 , ffft.user_function_name
                 , ffft.description AS func_descrip
                 , FIRST.sub_menu_id
                 , fmv2.user_menu_name AS sub_menu_name
                 , fme2.entry_sequence AS sub_seq
                 , fmet2.prompt AS sub_prompt
                 , fme2.function_id AS sub_func_id
                 , ffft2.user_function_name AS sub_func
                 , ffft2.description AS sub_func_descrip
                 , fme2.sub_menu_id AS sub_sub_menu_id
                 , FIRST.grant_flag
              FROM (  SELECT fat.application_id
                           , fat.application_name
                           , fr.responsibility_id
                           , frt.responsibility_name
                           , fr.end_date
                           , fr.menu_id
                           , fmv.user_menu_name
                           , fme.entry_sequence
                           , fmet.prompt
                           , fme.sub_menu_id
                           , fme.function_id
                           , fme.grant_flag
                        FROM apps.fnd_application_tl fat
                           , apps.fnd_responsibility fr
                           , apps.fnd_menus_vl fmv
                           , apps.fnd_responsibility_tl frt
                           , apps.fnd_menu_entries fme
                           , apps.fnd_menu_entries_tl fmet
                       --joins and constant selection
                       WHERE fat.application_id = fr.application_id(+)
                         AND fr.menu_id = fmv.menu_id(+)
                         AND fr.responsibility_id = frt.responsibility_id(+)
                         AND fr.menu_id = fme.menu_id(+)
                         AND fme.menu_id = fmet.menu_id(+)
                         AND fme.entry_sequence = fmet.entry_sequence(+)
                         AND fmet.language = 'US'
                         --------------------------------------
                         -- add specific selection criteria  --
                         --------------------------------------
                         --and   fat.application_id = 840
                         --for DEVL  19080 rows
                         --and fr.responsibility_id = 51856
                         AND fat.application_id = &appid
                    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST ---for application, responsibility and main menu info
                 , apps.fnd_menus_vl fmv2                                                                 ---for submenu info
                 , apps.fnd_menu_entries fme2
                 , apps.fnd_menu_entries_tl fmet2
                 , apps.fnd_form_functions_tl ffft                                                       ---for function info
                 , apps.fnd_form_functions_tl ffft2                                                   ---for subfunction info
             --left outer joins keep original records and add any sub menu and function info
             WHERE FIRST.function_id = ffft.function_id(+)
               AND FIRST.sub_menu_id = fmv2.menu_id(+)
               AND FIRST.sub_menu_id = fme2.menu_id(+)
               AND fme2.menu_id = fmet2.menu_id(+)
               AND fme2.entry_sequence = fmet2.entry_sequence(+)
               AND fme2.function_id = ffft2.function_id(+)
          ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) second -- adds any sub menu and function info
         LEFT OUTER JOIN apps.fnd_resp_functions exc                                                        ---for exclusions
            ON ( second.application_id = exc.application_id
            AND second.responsibility_id = exc.responsibility_id
            AND ( second.function_id = exc.action_id
              OR second.sub_func_id = exc.action_id
              OR second.menu_id = exc.action_id
              OR second.sub_menu_id = exc.action_id
              OR second.sub_sub_menu_id = exc.action_id ) )
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21;

How to Pass the File name as Parameter and populate the request id of the concurrent Program dyanamically using SQL Loader

For one of my client i was having a requirement to pass the file name as parameter to the SQL loader program and capture the file name and the request id in the table.After surfing a lot on google and don't find anything so i come up with the following solution which works for me so thought of sharing the solution. Please find below the details.

1. Create Control file as below 
2. In the control file we can use $FILE as the parameter to pass file name as parameter to the SQL Loader Program

LOAD DATA
INFILE '$FILE'
<APPEND/REPLACE/TRUNCATE>
INTO TABLE XX_STAGING_TBL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
 , MAIL_REQUEST_ID      DECIMAL EXTERNAL NULLIF (MAIL_REQUEST_ID=BLANKS)
 , ORDER_NUMBER         DECIMAL EXTERNAL NULLIF (ORDER_NUMBER=BLANKS)
 , ORDER_HEADER_ID      DECIMAL EXTERNAL NULLIF (ORDER_HEADER_ID=BLANKS)
 , LINE_NUMBER          DECIMAL EXTERNAL NULLIF (LINE_NUMBER=BLANKS)
 , ORDER_LINE_ID        DECIMAL EXTERNAL NULLIF (ORDER_LINE_ID=BLANKS)
 , MAIL_SEND_STATUS     CHAR NULLIF (MAIL_SEND_STATUS=BLANKS)
 , TO_RECIPIENT         CHAR NULLIF (TO_RECIPIENT=BLANKS)
 , CC_RECIPIENT         CHAR NULLIF (CC_RECIPIENT=BLANKS)
 , CREATION_DATE        DATE "MM/DD/YY" NULLIF (CREATION_DATE=BLANKS)
 , CREATED_BY           DECIMAL EXTERNAL NULLIF (CREATED_BY=BLANKS)
 , LAST_UPDATE_DATE     DATE "MM/DD/YY" NULLIF (LAST_UPDATE_DATE=BLANKS)
 , LAST_UPDATED_BY      DECIMAL EXTERNAL NULLIF (LAST_UPDATED_BY=BLANKS)

 , REQUEST_ID           "STRING_TO_REPLACE"
)


3. Save the file and move it to the XXCUST_TOP/bin directoy.
4. After this write a shell script to execute the Control file as below

### Below command is used to replace the "STRING_TO_REPLACE" with the current request id so that i will insert the constant value in the table for column REQUEST_ID.

sed s/STRING_TO_REPLACE/$p_conc_request_id/ $XX_TOP/bin/XX_DEMO_CTL.ctl > $XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl

##go to the directory

cd $XX_TOP/bin

## run the SQLLDR for the New Control file

sqlldr ${p_userid_pwd} control=$XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl log=/usr/tmp/XX_DEMO_CTL.log bad=/usr/tmp/XX_DEMO_CTL.bad data=${p_file_name}

## After completion remove the newly created Control file and soft link.

rm ${XX_DEMO_CTL}_${p_conc_request_id}.ctl

exit

5. Define the HOST concurrent program for the above shell script and pass the file name as parameter.

6. submit the Concurrent program and look for the data in the REQUEST_ID column the request id of the Shell Script Concurrent Program should be populated.

7. Please test it before using it on PROD instance.

Note :- We can create new Control file at run time dynamically and remove it after processing the file. Or we can Update the same Control file to keep the value of request id column as constant and once the processing the csv file is complete again run the above mentioned sed command to replace the request id value to a string "STRING_TO_REPLACE"

Wednesday, February 19, 2014

Script to Get the Nth Position String within a String

CREATE OR REPLACE PACKAGE xx_delimited_string
AS

-- p_str - the string to get the i'th word from
-- p_str can be xyz,yxz,zyx,zxy and you want to get the zxy as return ---- Value
-- p_n - the word to get from the string -- an index into the string
-- p_enclosed_by - what the words might be wrapped in.  In the above -- -- example, chr(34) is a double quote
-- p_terminated_by - what separates the words.  In the above example, ---- chr(44) is a comma.
  

  
   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES(nth_word, WNDS, RNDS);
END xx_delimited_string;
/

CREATE OR REPLACE PACKAGE BODY xx_delimited_string
AS
   TYPE vcarray IS TABLE OF VARCHAR2(2000)
      INDEX BY BINARY_INTEGER;

   g_words                                           vcarray;
   g_empty                                           vcarray;
   g_last_string                                     VARCHAR2(4096);

   FUNCTION de_quote(
      p_str                      IN             VARCHAR2
    , p_enc_by                   IN             VARCHAR2
   )
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN REPLACE(LTRIM(RTRIM(p_str
                               , p_enc_by
                                )
                         , p_enc_by
                          )
                   , p_enc_by || p_enc_by
                   , p_enc_by
                    );
   END de_quote;

   PROCEDURE parse_string(
      p_str                      IN             VARCHAR2
    , p_delim                    IN             VARCHAR2
    , p_sep                      IN             VARCHAR2
   )
   IS
      l_n                                               NUMBER DEFAULT 1;
      l_in_quote                                        BOOLEAN DEFAULT FALSE;
      l_ch                                              CHAR(1);
      l_len                                             NUMBER DEFAULT NVL(LENGTH(p_str)
                                                                         , 0
                                                                          );
   BEGIN
      IF (l_len = 0)
      THEN
         RETURN;
      END IF;

      g_words                                        := g_empty;
      g_words(1)                                     := NULL;

      FOR i IN 1 .. l_len
      LOOP
         l_ch                                           := SUBSTR(p_str
                                                                , i
                                                                , 1
                                                                 );

         IF (l_ch = p_delim)
         THEN
            l_in_quote                                     := NOT l_in_quote;
         END IF;

         IF (    l_ch = p_sep
             AND NOT l_in_quote)
         THEN
            l_n                                            := l_n + 1;
            g_words(l_n)                                   := NULL;
         ELSE
            g_words(l_n)                                   := g_words(l_n) || l_ch;
         END IF;
      END LOOP;

      FOR i IN 1 .. l_n
      LOOP
         g_words(i)                                     := de_quote(g_words(i)
                                                                  , p_delim
                                                                   );
      END LOOP;
   END parse_string;

   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2
   IS
   BEGIN
      IF (   g_last_string IS NULL
          OR p_str <> g_last_string)
      THEN
         g_last_string                                  := p_str;
         parse_string(p_str
                    , p_enclosed_by
                    , p_separated_by
                     );
      END IF;

      RETURN g_words(p_n);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
   END nth_word;
END xx_delimited_string;
/


How to Test 

SELECT xx_delimited_string.nth_word(:p_str
                         , 4
                         , CHR(34)
                         , CHR(44)
                          )

  FROM DUAL

Store timestamp with time zone in VARCHAR2 column and convert it into Date or timestamp Format

Format Timestamp with time zone ('YYYY-MM-DDTHH:MI:SS.FF+TZ')  if stored into VARCHAR2 column and needs to convert into 'YYYY-MM-DD HH:MI:SS' then use the Below select statement for conversion.


SELECT CAST (
          TO_TIMESTAMP_TZ ('2014-02-12T23:02:14.000+0000',
                           'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
             AT TIME ZONE DBTIMEZONE AS DATE)
  FROM DUAL

Tuesday, February 18, 2014

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;

Monday, February 17, 2014

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;
/

Saturday, February 15, 2014

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>