ORACLE PROJECTS: Profile Option Query with Responsibility, User, Site level

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

--SITE LEVEL PROFILE OPTION--

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

SELECT *

FROM apps.fnd_profile_options_tl

WHERE USER_PROFILE_OPTION_NAME = 'CSF: eLocation Map host name'

AND language = 'US';

--select FND_PROFILE.value('CONC_GSM_ENABLED') Profile_value from dual;

-----

-- EMR IT Profile Administrator-> System Profiles->

-------1--Enter “Signon Password Failure Limit”->Site Level>> find-> should be NULL

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('SIGNON_PASSWORD_FAILURE_LIMIT',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('SIGNON_PASSWORD_FAILURE_LIMIT') Profile_value from dual;

--2--“Concurrent:GSM Enabled”-> Site Level >> should be Y

SELECT *

FROM apps.fnd_profile_options_tl

WHERE USER_PROFILE_OPTION_NAME = 'Concurrent:GSM Enabled' --CONC_GSM_ENABLED

AND language = 'US'; */

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('CONC_GSM_ENABLED',

'Y',

'SITE',

NULL,

NULL);

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('CONC_GSM_ENABLED') Profile_value from dual;

--3--“XXWIP: Shop%” ->Site Level >> all four should be set to NULL

--XXWIP_SHOPPACK_DESTDIR_VLVS

--XXWIP_SHOPPACK_DESTSERVER_VLVS

--XXWIP_SHOPPACK_PRINT_TGR_DESTDIR_VLVS

--XXWIP_SHOPPACK_PRINT_TGR_DESTSERVER_VLVS

--1

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('XXWIP_SHOPPACK_DESTDIR_VLVS',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('XXWIP_SHOPPACK_DESTDIR_VLVS') Profile_value from dual;

--2

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('XXWIP_SHOPPACK_DESTSERVER_VLVS',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('XXWIP_SHOPPACK_DESTSERVER_VLVS') Profile_value from dual;

--3

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('XXWIP_SHOPPACK_PRINT_TGR_DESTDIR_VLVS',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('XXWIP_SHOPPACK_PRINT_TGR_DESTDIR_VLVS') Profile_value from dual;

--4

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('XXWIP_SHOPPACK_PRINT_TGR_DESTSERVER_VLVS',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('XXWIP_SHOPPACK_PRINT_TGR_DESTSERVER_VLVS') Profile_value from dual;

--4--“ASO : Print Quote Reports Directory” -> Site Level point to the instance cloned>>(Example : /betsyk8ap/betsyk8appl/aso/11.5.0/reports” (substitute for betsyk8))

--/betsyd8ap/betsyd8appl/aso/11.5.0/reports

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('ASO_PRINT_QUOTE_REPORTS_DIR',

'/betsyg6ap/betsyg6appl/aso/11.5.0/reports', -- '/betsyn12ap/r12/fs1/EBSapps/appl/aso/12.0.0',

'SITE',

NULL,

NULL);

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('ASO_PRINT_QUOTE_REPORTS_DIR') Profile_value from dual

--5--“Local Login Mask”-> Site Level >> should be NULL.

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('FND_SSO_LOCAL_LOGIN_MASK',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('FND_SSO_LOCAL_LOGIN_MASK') Profile_value from dual

--6--Applications Help Web Agent >>Site Level>>http://upkoracle.emersonprocess.com/EBS_Content/oracle/hemi/oracle_gateway.html?dbc=d:\oracle\o11iappl\fnd\11.5.0\secure\/betsyap/betsyappl/fnd/11.5.0/secure/betsydb1.emrsn.com_betsy.dbc

DECLARE

stat BOOLEAN;

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('HELP_WEB_AGENT',

'http://upkoracle.emersonprocess.com/EBS_Content/oracle/hemi/oracle_gateway.html?dbc=d:\oracle\o11iappl\fnd\11.5.0\secure\/betsyap/betsyappl/fnd/11.5.0/secure/betsydb1.emrsn.com_betsy.dbc',

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('HELP_WEB_AGENT') Profile_value from dual

--XXFND: DB NAME >> Site level (Should be Instance name Example BETSYD10)

--select FND_PROFILE.value('XXFND_DB_NAME') Profile_value from dual;

DECLARE

stat BOOLEAN;

--v_instance varchar2(20) := UPPER (:instance_name);

BEGIN

-- DBMS_OUTPUT.disable;

-- DBMS_OUTPUT.enable (100000);

stat :=

FND_PROFILE.SAVE ('XXFND_DB_NAME',

'BETSYX2',--'BETSYX4',

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

SELECT * FROM v$DATABASE

--8--XXOM: SIZING URL MMI >> Site level >. should be Null in all instacne (Should be Null other than n1/s0.)

DECLARE

stat BOOLEAN;

BEGIN

stat :=

FND_PROFILE.SAVE ('XXOM_SIZING_URL_MMI',

NULL,

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('XXOM_SIZING_URL_MMI') Profile_value from dual;

9--CSF: eLocation Map host name >> Sitelevel >> localhost

DECLARE

stat BOOLEAN;

BEGIN

stat :=

FND_PROFILE.SAVE ('CSF_EMAP_DS_HOST',

'localhost',

'SITE', --Level that you’re setting at: ‘SITE’,’APPL’,’RESP’,’USER’, etc.

NULL,-- Level value that you are setting at, e.g. user id for ‘USER’ level. X_LEVEL_VALUE is not used at site level. */

NULL);--Used for ‘RESP’ and ‘SERVRESP’ level; Resp Application_Id. */

IF stat

THEN

DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');

ELSE

DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');

END IF;

COMMIT;

END;

--select FND_PROFILE.value('CSF_EMAP_DS_HOST') Profile_value from dual;

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

--RESPONSIBILITY LEVEL PROFILE OPTION--

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

---PROFILE1: 'XXOM: Product Label Printer Copies VLVS'

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Product Label Printer Copies VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: 'XXOM: Product Label Printer VLVS'

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Product Label Printer VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE1: 'XXOM: Parts Pick List Printer Copies VLV'

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name = 'XXOM: Parts Pick List Printer Copies VLV'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: XXOM: Parts Pick List Printer VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Parts Pick List Printer VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE1: XXOM: Certificate of Conformity Printer Copies VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name = 'XXOM: Certificate of Conformity Printer Copies VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: XXOM: Certificate of Conformity Printer VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU' );

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Certificate of Conformity Printer VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile ' || c.responsibility_name || ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

/* Formatted on 31-08-2016 14:27:23 (QP5 v5.163.1008.3004) */

---PROFILE1: XXOM: Box Label Printer Copies VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU');

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Box Label Printer Copies VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: XXOM: Box Label Printer VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR OM Shipping Clerk QSZ VLVS HU',

'EMR WIP Production Supervisor QSZ VLVS HU');

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name = 'XXOM: Box Label Printer VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE1: Concurrent:Report Copies

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR PO Receiver QSZ VLVS HU',

'EMR CSA Level2 SSZ VLVS HU');

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'Concurrent:Report Copies'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: Printer

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT application_id, responsibility_name, responsibility_id

FROM fnd_responsibility_tl

WHERE language = 'US'

AND responsibility_name IN

('EMR PO Receiver QSZ VLVS HU',

'EMR CSA Level2 SSZ VLVS HU');

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name = 'Printer'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'RESP',

c.responsibility_id,

c.application_id);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.responsibility_name

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.responsibility_name

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

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

--USER LEVEL PROFILE OPTION--

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

---PROFILE1: XXOM: Product Label Printer Copies VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT USER_ID, USER_NAME

FROM apps.FND_USER

WHERE USER_NAME = 'EMR_VLVS_ASSY_INTERFACE_QSZ';

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Product Label Printer Copies VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'USER',

C.USER_ID,

NULL);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.USER_NAME

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.USER_NAME

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.USER_NAME

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

---PROFILE2: XXOM: Product Label Printer Copies VLVS

DECLARE

stat BOOLEAN;

v_pro_op_name VARCHAR2 (32);

lc_status VARCHAR2 (1);

lc_error_msg VARCHAR2 (240);

CURSOR xxcur

SELECT USER_ID, USER_NAME

FROM apps.FND_USER

WHERE USER_NAME = 'EMR_VLVS_ASSY_INTERFACE_QSZ';

BEGIN

FOR c IN xxcur

LOOP

lc_status := 'Y';

BEGIN

SELECT profile_option_name

INTO v_pro_op_name

FROM apps.fnd_profile_options_tl

WHERE user_profile_option_name =

'XXOM: Product Label Printer VLVS'

AND language = 'US';

DBMS_OUTPUT.put_line ('ID fetched successfully.');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

lc_status := 'E';

END;

DBMS_OUTPUT.put_line ('lc_status is: ' || lc_status);

IF lc_status = 'Y'

THEN

stat :=

fnd_profile.save (v_pro_op_name,

NULL,

'USER',

C.USER_ID,

NULL);

IF stat

THEN

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.USER_NAME

|| ' Updated');

ELSE

DBMS_OUTPUT.put_line (

'Stat = TRUE - profile '

|| c.USER_NAME

|| ' NOT updated');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'failed to add profile option for '

|| c.USER_NAME

|| ' : '

|| SQLERRM);

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

ORACLE PROJECTS

MENUS

Monday, 18 September 2017

Profile Option Query with Responsibility, User, Site level