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 ItemLet 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.
Multiple Organizations Access Control
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.
Share this:
EXECUTE IMMEDIATE
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;
Share this:
Introduction of DYNAMIC SQL
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.
Share this:
DBMS_SQL
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;
Share this:
R12 – Procure To Pay Cycle in Oracle Apps (P2P)
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.
Share this: