Query to Update EBS Profile Values for Selective Users at the User Level

 SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

    l_success BOOLEAN;

    CURSOR uid IS

        SELECT DISTINCT fpov.level_value AS user_id

        FROM fnd_profile_option_values fpov

        JOIN fnd_profile_options fpo ON fpov.profile_option_id = fpo.profile_option_id

        WHERE fpo.profile_option_name = 'ICX_FORMS_LAUNCHER'

          AND fpov.level_id = 10004  -- User level

          AND fpov.profile_option_value IS NOT NULL;

    v_counter NUMBER := 0;  -- Counter for users processed

BEGIN

    FOR i IN uid LOOP

        l_success := FND_PROFILE.save(

            'ICX_FORMS_LAUNCHER',

            'http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws',

            'USER',

            i.user_id

        );

        v_counter := v_counter + 1;

        IF MOD(v_counter, 100) = 0 THEN  -- Print output every 100 users to avoid overflow

            DBMS_OUTPUT.PUT_LINE(v_counter || ' users updated...');

        END IF;

        COMMIT; -- Commit after each update

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Profile update completed successfully.');

END;

/

Query to Update EBS Profile Values for All Users at the User Level

set serveroutput on;

DECLARE

l_success  BOOLEAN;

    CURSOR uid IS

    SELECT user_id from fnd_user;

BEGIN

dbms_output.disable;

 dbms_output.enable(100000);

FOR i IN uid LOOP

        l_success := FND_PROFILE.save('ICX_FORMS_LAUNCHER','http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws','USER',i.user_id);

        IF l_success

THEN

 DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Updated successfully ');

DBMS_OUTPUT.put_line('---');

 ELSE

  DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Update Failed . Error:'||sqlerrm);

 DBMS_OUTPUT.put_line('---');

 END IF;

  commit;

 end loop;

end;

/

Protocol is not supported Error in IMAP While Configuring OAuth in EBS Workflow

When configuring OAuth for the EBS workflow application, you may encounter the following error message in IMAP:

Cause:

The error is triggered due to missing or incorrect protocol settings in the WebLogic Server configuration. By default, some WebLogic instances may not be set to use the required HTTP handler or TLS protocol version, leading to connectivity issues.

Solution:

1) Add -DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2 to the Server Start arguments for all OACORE servers and ADMIN server.

2) Restart the OACORE services.

Note: Not required to add parameters on DMZ environment.

How to Check and Update Email Addresses for Workflow Notifications in Oracle EBS

Step 1: Verify the User’s Email Address in FND_USER

Run the following query to check if the user has a valid email address assigned in FND_USER:

SELECT user_name, email_address, employee_id FROM fnd_user WHERE user_name LIKE 'KUMAR';

If the email_address is missing or incorrect, update it using the System Administrator responsibility:

Navigate to System Administrator → Security → User → Define

Search for the user and update the email address

Step 2: Check User Preferences and Email Address in WF_ROLES

The WF_ROLES table determines User preferences for workflow notifications. Run the following query:

SELECT name, email_address, NVL(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference) AS preference FROM wf_roles WHERE name = UPPER('KUMAR');

If preference is MAIL or MAILHTML, email notifications should be sent.

If the email_address is missing, proceed to the next step.

Step 3: Update the Email Address in WF_LOCAL_ROLES

If the email is missing in wf_roles view, update it using the following query:

UPDATE applsys.wf_local_roles SET email_address = 'kumar@xxxx.xxx' WHERE name = 'KUMAR';

COMMIT;

Find DMZ Enabled Responsibility in Oracle EBS Application

SELECT po.profile_option_id,po.USER_PROFILE_OPTION_NAME,usr.user_name,rspt.responsibility_name,rsp.end_date

FROM apps.fnd_profile_options_vl po, apps.fnd_profile_option_values pov,apps.fnd_user usr, apps.fnd_application app, apps.fnd_responsibility rsp,

apps.fnd_responsibility_tl rspt,

(select name,organization_id from HR_OPERATING_UNITS) org

WHERE  pov.application_id = po.application_id

AND pov.profile_option_id =   po.profile_option_id

AND rsp.application_id(+) =  pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+)   = pov.level_value

AND rspt.application_id(+) =  pov.level_value_application_id

AND rspt.responsibility_id(+) = pov.level_value

and usr.user_id=pov.LAST_UPDATED_BY

    and pov.profile_option_value=to_char(org.organization_id (+))

     and po.PROFILE_OPTION_NAME='APPL_SERVER_TRUST_LEVEL'

    -- and rsp.end_date is null

     and language='US';

How to Find the Responsibility Name for a Concurrent Program

SELECT frt.responsibility_name, frg.request_group_name,

frgu.request_unit_type,frgu.request_unit_id,

fcpt.user_concurrent_program_name

FROM fnd_Responsibility fr, fnd_responsibility_tl frt,

fnd_request_groups frg, fnd_request_group_units frgu,

fnd_concurrent_programs_tl fcpt

WHERE frt.responsibility_id = fr.responsibility_id

AND frg.request_group_id = fr.request_group_id

AND frgu.request_group_id = frg.request_group_id

AND fcpt.concurrent_program_id = frgu.request_unit_id

AND frt.LANGUAGE = USERENV('LANG')

AND fcpt.LANGUAGE = USERENV('LANG')

AND fcpt.user_concurrent_program_name = :conc_prg_name

ORDER BY 1,2,3,4

Query to take a backup/Details of all database links in oracle

 

SELECT dl.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',dl.DB_LINK,dl.OWNER) FROM dba_db_links dl;

select dl.OWNER,dl.DB_LINK,dl.USERNAME,dl.HOST,CREATED from dba_db_links dl;

DROP Database Links:

drop database link <db_link_name>;              --Needs to run as a specific user (Owner of the db links)

drop public database link <db_link_name>;    --Needs to run as a sys user.

Query to Find the UTLRP Session in an Oracle Database

In RAC:

select a.inst_id,a.sid,a.serial#,b.sql_text,b.sql_id,a.username,a.SCHEMANAME,a.OSUSER,a.MACHINE,a.PROGRAM,a.sql_id,a.action,a.event 

from gv$session a, gv$sqlarea b 

where a.sql_address=b.address and a.action like '%UTL%';

In Non RAC:

select a.inst_id,a.sid,a.serial#,b.sql_text,b.sql_id,a.username,a.SCHEMANAME,a.OSUSER,a.MACHINE,a.PROGRAM,a.sql_id,a.action,a.event 

from v$session a, v$sqlarea b 

where a.sql_address=b.address and a.action like '%UTL%';

Find SCN Timestamp in Oracle Database

 SELECT SCN_TO_TIMESTAMP(13232543131) from dual;

Database version on SQL Server Management Studio

SELECT @@VERSION AS FullVersion;