, ,

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
, , , ,

R12 – Procure To Pay Cycle in Oracle Apps (P2P)

I tried to explain the steps involved in Procure to Pay Cycle. This is a pure functional Stuff and helps you to understand the navigation steps.
I tried to keep as simple as Possible for clear understanding. The screenshots given below are taken from R12.1.1 apps instance.
Stage 1: Choosing an Item
Let us choose an item to be procured in our example.
Go to Purchasing Responsibility and follow the below navigation to check for the suitable item.


The item picked for our example should be purchasable item as above. Click on tools and choose “Categories” to view the below screen.

Stage 2: Creation of Requisition
Follow the below Navigation to reach Requisition Form.

Create a new Requisition for the item viewed in Stage 1.

Click on Distributions to View the charge Account.

Save and Submit for Approval

Now note down the Requisition number and open the “Requisition Summary Form” to view the status of it. For our Example, Requisition number is: 14855

Stage 3 : Checking the Status of Requisition

Query for the Requisition Number and click Find.

Here for our example purpose, I kept the submitted and approved person has same and hence it shows the status as approved.

To see the approval status, please follow the below navigation.

Stage 4 : Creation of Purchase Order
For creating a Purchase order, let us use the “Autocreate Documents” Form. Follow the below Navigation


Query for the Requisition

Click on Automatic as shown in the above figure to create a Purchase Order

Click on “Create” button to create a Purchase order

View the shipment screen to change the “Match Approval Level” to “2-Way”.

Click the “Receiving Controls” to make sure that the “Routing” is made as “Direct Routing”

Click Save and submit for Approval.


Note down the PO Number.

Stage 5: Creation of Receipts


Query with the Purchase order created in the above stage.

Check the check box near to the lines that are received and click save.

Click the “Header Button” to view the Receipt Number.

Stage 6: Checking the On Hand
Go to any Inventory Responsibility and follow the below Navigation

Query for our Receipt and make sure the Organization is the same as we received.

Below screen will show that our inventory has been increased by 5 quantities.

Stage 7: Check the Material Transactions
Follow the below Navigation to reach “Material Transactions” Form


Query for the item and date as below


Below screen shows the source and transaction Type

Below screen shows you the Serial Numbers of the items received.

Stage 8: Creation of Invoice
Navigate to any Purchasing Responsibility and view à Requests
Submit the below requests by providing the Receipt number as Parameter to create an invoice.

Check the status of the program.

Stage 9: Checking the Invoice
Change to any Payables Responsibility and open the invoices Form.
Query for the Purchase order as below,


Click “Actions” Button then tick the “Validate Check Box” and press “Ok” to validate the invoice

Below screenshot will give you the status of the invoice

Stage 10: Creation of Accounting and Payment
Once invoice got approved, we can “Create Accounting” and “Create Payments” via “Action” Button in the “Invoice Form” as we validated the invoice.

/by