, , ,

Multiple Organizations Access Control

Multiple Organizations Access Control is an enhancement to the Multiple
Organizations feature of Oracle Applications. Multiple Organizations Access Control
allows a user to access data from one or many Operating Units while within a given
responsibility. Data security is maintained using the Multiple Organizations Security
Profile, defined in Oracle HRMS, which specifies a list of operating units and
determines the data access privileges for a user.

In Release 12, several controls are moved from the Payables Options or Financials Options forms to a new setup form that is common for Oracle Payables across all
operating units, the Payables System Setup form. If the upgrade finds conflicts in the
settings across multiple operating units, it will choose the most frequently occurring
setting.

Oracle Applications will not automatically create security profiles during the Release 12
upgrade. If you want to use Multiple Organizations Access Control, you will first need
to define security profiles, then link them to responsibilities or users.
/by
, ,

EXECUTE IMMEDIATE

Note:- This type of Dynamic SQL would not work in the 10.7 version Database.

For the Select statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———-

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER; 
BEGIN 
EXECUTE IMMEDIATE ‘select max(sal) from emp 
where deptno = :l_deptno’
INTO L_SAL
USING L_DEPTNO;
DBMS_OUTPUT.PUT_LINE(L_SAL);
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———–

DECLARE
L_ENAME VARCHAR2(20) DEFAULT ‘PHANI’;
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE ‘INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES
(:L_ENAME,:L_EMPNO,:L_DEPTNO)’
USING L_ENAME,
L_EMPNO,
L_DEPTNO;
END;

For the Update Statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———–

DECLARE
L_ENAME VARCHAR2(20) DEFAULT ‘PHANI’;
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE ‘UPDATE EMP
SET ENAME = ”RAHUL” 
WHERE ENAME = :l_ENAME’
USING L_ENAME;
END;
/by
, ,

Introduction of DYNAMIC SQL

If we have to create some PROCEDURE, FUNCTION or PACKAGE and run them, then we have to do two steps.

1) Compile the code.
2) Execute the code which you have compiled.

Errors can occur at any above steps.

At the compile Time (Step 1), system will check for the syntax of the code and also if all the objects used in the code are exisiting in database or not.

If you want to hide the objects at the compile time so that you do not get the error message at the compile time (step 1) then use DYNAMIC SQL.

Difference between DBMS_SQL and EXECUTE IMMEDIATE
———————————————————————–

EXECUTE IMMEDIATE type of Dynamic SQL would not work in the old versions of Oracle Database like 10.7 or older then this version.

DBMS_SQL type of Dynamic SQL would work in all the version of Oracle Database.
/by
, ,

DBMS_SQL

For the Select statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
BEGIN
— Code converted in dynamic SQL start(phani).
L_SQL := ‘select max(sal) from emp where deptno = :l_deptno’;
L_CUR := dbms_sql.OPEN_CURSOR;
dbms_sql.PARSE( L_CUR, L_SQL, dbms_sql.NATIVE );
dbms_sql.BIND_VARIABLE( L_CUR, ‘:l_deptno’, L_DEPTNO );
— describe defines
dbms_sql.DEFINE_COLUMN( L_CUR, 1, L_SAL );
— execute
L_RC := dbms_sql.EXECUTE( L_CUR );
LOOP
— fetch a row
IF dbms_sql.FETCH_ROWS( L_CUR ) > 0 THEN
— fetch columns from the row
dbms_sql.COLUMN_VALUE( L_CUR, 1, L_SAL );
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.CLOSE_CURSOR( L_CUR );
dbms_output.PUT_LINE( L_SAL );
— Code converted in dynamic SQL end (phani).
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_empno NUMBER := 1;
g_ename VARCHAR2(30) := ‘REDDY’;
g_deptno NUMBER := 10;
BEGIN
— Code converted in dynamic SQL start. 
l_sql:= ‘insert into emp 
(EMPNO,
ENAME,
DEPTNO)
VALUES
( :p_empno,
:p_ename,
:p_deptno)’;

l_cur := dbms_sql.open_cursor; 
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ‘:p_empno’, g_empno); 
dbms_sql.bind_variable(l_cur, ‘:p_ename’, g_ename); 
dbms_sql.bind_variable(l_cur, ‘:p_deptno’,g_deptno);
— execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);
END;

For the Update Statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_new_ename VARCHAR2(20) := ‘REDDY01’;
g_old_ename VARCHAR2(20) := ‘REDDY’;
BEGIN
— Code converted in dynamic SQL start. 

l_sql:= ‘update emp 
set ename = :p_new_ename
where ename = :p_old_ename’;

l_cur := dbms_sql.open_cursor; 
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);

dbms_sql.bind_variable(l_cur, ‘:p_new_ename’,g_new_ename);
dbms_sql.bind_variable(l_cur, ‘:p_old_ename’,g_old_ename);

— execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);

— Code converted in dynamic SQL end. 
END;

/by
, ,

Steps To Register Unix Shell Script As A Concurrent Program

Steps To Register Unix Shell Script As A Concurrent Program

We can register an Unix shell script in our oracle application through a concurrent program. Here are the steps.
Step 1:
Copy the .prog script in ASCII mode to the bin directory of your application top directory. For example, call the script XXSHELL.prog and place it under $XXCUST_TOP/bin
step 2:
Check the file permissions. Sometimes it is required to give full permission to the script.
step 3:
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called XXSHELL.prog , then use this:
cd $XXCUST_TOP/bin
ln -s $FND_TOP/bin/fndcpesr XXSHELL
This link should be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.
step 4:
Register the concurrent program, using an execution method of ‘Host’. Use the name of your script without the .prog extension as the name of the executable.
For the example above: Use XXSHELL as executable name.
Note:
Your script will be passed at least 4 parameters, from $1 to $4.
$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id
Any other parameters you define will be passed in as $5 and higher. Make sure your script returns an exit status also.
fndcpesr is a standard utility available in $FND_TOP directory. It is mainly used by the application to parse the above four arguments to the shell scripts.

/by