Script to Assign the Profile option for a specific Level

 The below script is to enable profile option at USER level, for a given list of Users.

For setting a Profile Option Value from back end we need the Following three parameters;
    1. Name of Profile Option
    2. Value for the profile option which we want to set.
    3. The level at which we want to set the profile option value.

    Parameters Explanation:
    Profile_Option_Name  : Profile name you are setting
    Profile_Option_Value : Profile value you are setting
    Level                : Level that you're setting at. It could be 'SITE', 'APPL','RESP', or 'USER'
    Level_Value     : Level value that you are setting at,
                           e.g. user id for 'USER' level. This is not used at site level
    Level_Value_App_id   : Only used for 'RESP' level. Application_Id of the responsibility.

DECLARE

   l_result    BOOLEAN;

   l_profile   VARCHAR2 (150);

BEGIN

   SELECT   profile_option_name

     INTO   l_profile

     FROM   FND_PROFILE_OPTIONS_TL

    WHERE   user_profile_option_name = 'ABC'; 
/* An example of the value to be used in the above condition to match with user_profile_option_name. Use MO: Operating Unit if you are trying to set operating unit. If you use MO: Operating Unit you get ORG_ID in l_profile variable */

   FOR rec IN (SELECT   user_id

                 FROM   fnd_user

                WHERE   user_name IN ('XXXX', 'YYYY', 'ZZZZ'))

   LOOP

      l_result :=

         FND_PROFILE.SAVE (l_profile,

                           'Y',

                           'USER',

                           rec.user_id,

                           NULL,

                           NULL);

      IF l_result

      THEN

         DBMS_OUTPUT.put_line ('Success');

         COMMIT;

      ELSE

         DBMS_OUTPUT.put_line ('Error');

      END IF;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Exception in the Main');

END;

/

***Please leave a comment***

Concatenated Sales Order Line Number - Oracle Applications OrderManagement

Concatenated Sales Order Line Number in Oracle Applications Order Management

Wondering there should be an easy way to get the concatenated sales order line number in Oracle Applications Order Management? Tired of writing a concatenation logic with NVL/DECODE conditions? Here is the amazing solution.

Syntax:

SELECT oe_order_misc_pub.get_concat_line_number (<line_id>) so_line_number

  FROM DUAL

Example:

SELECT oe_order_misc_pub.get_concat_line_number (22366203) so_line_number

  FROM DUAL 

If you follow the traditional way here how it looks like

SELECT oola.line_number

             || '.'

             || oola.shipment_number

             || DECODE (oola.option_number, NULL, NULL, '.' || oola.option_number)

             || DECODE (oola.component_number,

                   NULL, NULL,

                   DECODE (oola.option_number, NULL, '.') || '.' || oola.component_number)

             || DECODE (oola.service_number,

                        NULL,

                        NULL,

                           DECODE (oola.option_number, NULL, '.')

                        || DECODE (oola.component_number, NULL, '.')

                        || '.'

                        || oola.service_number

                ) so_line_number

FROM oe_order_lines_all oola

WHERE oola.line_id = 22366203;

***Please leave a comment***

Script to Assign Responsibility in Oracle Applications

Below is the script that can be used to assign responsibility for a user.

DECLARE

   l_user_id   NUMBER;

   l_resp_id   NUMBER;

   l_appl_id   NUMBER;

   CURSOR cur_resp

   IS

      SELECT   responsibility_id, application_id -- INTO   l_resp_id, l_appl_id

        FROM   fnd_responsibility_vl

       WHERE   UPPER (responsibility_name) LIKE UPPER ('XXXXX');

BEGIN

   SELECT   user_id

     INTO   l_user_id

     FROM   fnd_user

    WHERE   user_name = 'UUUU';

   FOR rec_cur IN cur_resp

   LOOP

      fnd_user_resp_groups_api.insert_assignment (

         user_id                         => l_user_id,

         responsibility_id               => rec_cur.responsibility_id,

         responsibility_application_id   => rec_cur.application_id,

         security_group_id               => 0,

         start_date                      => SYSDATE - 1,

         end_date                        => NULL,

         description                     => NULL

      );

      COMMIT;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.Put_Line ('Exception  in the Main');

END;

***Please leave a comment***

Difference Between Length and Vsize

Difference Between Length and Vsize

It appears that the Oracle Functions Length ( ) and Vsize () return the same results, but practically there is a difference in their usage

For Example :

Select Length ('Have a Nice Day') , Vsize ( 'Have a Nice Day') From Dual; Result : 15 , 15 

Well the result is same for both , but let’s see another example :

Select Length (9.234), Vsize ( 9.234) from Dual;
Result : 5 , 4


More Examples :
Select Length ( Sysdate) , Vsize ( Sysdate)  From Dual;
Result : 9 , 7  ( Current sysdate is '05-May-2013')

So we can see difference in the results, this is because  Vsize returns the number of bytes used to store the internal representation in any datatype, whereas Length returns the length of the passed string. Length function takes the character arguments (Char, Varchar2, Nchar, NVarchar2, Clob or Nclob) , so even if you pass a non-character argument, the Length Function converts the data type implicitly into a string and then returns its length.

***Please leave a comment***

How to convert number of seconds into Oracle date format ( Days Hours :Minutes : Seconds )

How to convert number of seconds into Oracle date format (Days Hours:Minutes:Seconds):

SELECT TO_CHAR(TO_DATE(ROUND('<variable>'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

SELECT ROUND('<variable>'/'86399') || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('<variable>','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Replace <variable> with number of seconds. ROUND function is used to round to nearest integer if you provide seconds in a decimal format

Example1: 

SELECT TO_CHAR(TO_DATE(ROUND('3000'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

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

00:50:00

3000 seconds is displayed in the hours, minutes and seconds format: read as 50 minutes

Example2: 

SELECT TO_CHAR(TO_DATE(ROUND('30007.345'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

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

08:20:07

30007.345 seconds is displayed in the hours, minutes and seconds format: read as 8 hours 20 minutes and 7 seconds

Example3: 
Above examples cover the seconds for a day. Lets take a large number of seconds which goes beyond a day.  Below query provides the output for 90740 seconds

SELECT ROUND('90740'/86399) || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('90740','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Output:

Days_Hours_Minutes_Format

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

1 Day(s) 01:12:21

90740 seconds is displayed in the days, hours, minutes and seconds format: read as 1 Day 1 hours 12 minutes and 21 seconds

Tip: 

This is very useful if you are trying to get the run time of a concurrent request in a readable format in Oracle Applications. Sample query below

SELECT request_id, (actual_completion_date - actual_start_date)*24*60*60 run_time_secs,

       TO_CHAR(TO_DATE((actual_completion_date - actual_start_date)*24*60*60, 'SSSSS'), 'HH24:MI:SS') run_time_Hours_Minutes_Format

FROM fnd_concurrent_requests

WHERE request_id  = 89110175;

Sample Output:

***Please leave a comment***