Find Sales Order's subtotal, discount, charges and Tax through API

Below script will help you to find Sales Order's subtotal, discount, charges and Tax through API OE_OE_TOTALS_SUMMARY.ORDER_TOTALS

This script was tested in R12.1.1

SET SERVEROUTPUT ON;

 DECLARE

  v_HEADER_ID NUMBER;

  v_SUBTOTAL  NUMBER;

  v_DISCOUNT  NUMBER;

  v_CHARGES   NUMBER;

  v_TAX       NUMBER;

BEGIN

  v_HEADER_ID := 5908;

  OE_OE_TOTALS_SUMMARY.ORDER_TOTALS(

    P_HEADER_ID  => v_HEADER_ID,

    P_SUBTOTAL   => v_SUBTOTAL,

    P_DISCOUNT   => v_DISCOUNT,

    P_CHARGES    => v_CHARGES,

    P_TAX        => V_TAX

  );

  DBMS_OUTPUT.PUT_LINE('SUBTOTAL = ' || v_SUBTOTAL);

  DBMS_OUTPUT.PUT_LINE('DISCOUNT = ' || v_DISCOUNT);

  DBMS_OUTPUT.PUT_LINE('CHARGES  = ' || v_CHARGES);

  DBMS_OUTPUT.PUT_LINE('TAX      = ' || v_TAX);

END;

API to Book a Sales Order - OE_ORDER_PUB.PROCESS_ORDER

Sample Script to Book the Sales Order using OE_ORDER_PUB.PROCESS_ORDER

INSTANCE: R12

SET SERVEROUTPUT ON;

DECLARE

v_api_version_number           NUMBER  := 1;

v_return_status                VARCHAR2 (2000);

v_msg_count                    NUMBER;

v_msg_data                     VARCHAR2 (2000);

-- IN Variables --

v_header_rec                   oe_order_pub.header_rec_type;

v_line_tbl                     oe_order_pub.line_tbl_type;

v_action_request_tbl           oe_order_pub.request_tbl_type;

v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

-- OUT Variables --

v_header_rec_out               oe_order_pub.header_rec_type;

v_header_val_rec_out           oe_order_pub.header_val_rec_type;

v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;

v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;

v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;

v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;

v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;

v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;

v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;

v_line_tbl_out                 oe_order_pub.line_tbl_type;

v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;

v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;

v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;

v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;

v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;

v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;

v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;

v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;

v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;

v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;

v_action_request_tbl_out       oe_order_pub.request_tbl_type;

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');

-- Setting the Enviroment --

mo_global.init('ONT');

fnd_global.apps_initialize ( user_id      => 2585

                            ,resp_id      => 50864

                            ,resp_appl_id => 660);

mo_global.set_policy_context('S',83);

v_action_request_tbl (1)             := oe_order_pub.g_miss_request_rec;

v_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;

v_action_request_tbl(1).entity_code  := OE_GLOBALS.G_ENTITY_HEADER;

v_action_request_tbl(1).entity_id    := 6006;

DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to to Book an Existing Order --

OE_ORDER_PUB.PROCESS_ORDER (

p_api_version_number            => v_api_version_number

, p_header_rec                  => v_header_rec

, p_line_tbl                    => v_line_tbl

, p_action_request_tbl          => v_action_request_tbl

, p_line_adj_tbl                => v_line_adj_tbl

-- OUT variables

, x_header_rec                  => v_header_rec_out

, x_header_val_rec              => v_header_val_rec_out

, x_header_adj_tbl              => v_header_adj_tbl_out

, x_header_adj_val_tbl          => v_header_adj_val_tbl_out

, x_header_price_att_tbl        => v_header_price_att_tbl_out

, x_header_adj_att_tbl          => v_header_adj_att_tbl_out

, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out

, x_header_scredit_tbl          => v_header_scredit_tbl_out

, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out

, x_line_tbl                    => v_line_tbl_out

, x_line_val_tbl                => v_line_val_tbl_out

, x_line_adj_tbl                => v_line_adj_tbl_out

, x_line_adj_val_tbl            => v_line_adj_val_tbl_out

, x_line_price_att_tbl          => v_line_price_att_tbl_out

, x_line_adj_att_tbl            => v_line_adj_att_tbl_out

, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out

, x_line_scredit_tbl            => v_line_scredit_tbl_out

, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out

, x_lot_serial_tbl              => v_lot_serial_tbl_out

, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out

, x_action_request_tbl          => v_action_request_tbl_out

, x_return_status               => v_return_status

, x_msg_count                   => v_msg_count

, x_msg_data                    => v_msg_data

);

DBMS_OUTPUT.PUT_LINE('Completion of API');

IF v_return_status = fnd_api.g_ret_sts_success THEN

    COMMIT;

    DBMS_OUTPUT.put_line ('Booking of an Existing Order is Success ');

ELSE

    DBMS_OUTPUT.put_line ('Booking of an Existing Order failed:'||v_msg_data);

    ROLLBACK;

    FOR i IN 1 .. v_msg_count

    LOOP

      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line( i|| ') '|| v_msg_data);

    END LOOP;

END IF;

END;

/

Buyer Setup

1) In HRMS ‘People > Enter and maintain’, Create New Employee whose Last name must be same as User name Which we are logged in. Go to Assignment; Enter Org, Position and Job. Save the record.

2) In Sysadmin ‘Security > User > Define’, Query for the user & enter ‘person’ field with employee name created in HRMS. Save the record.

3) In PO’ Setup >Personal > Buyers ‘, Create new buyer for our user. Now we can create a PO

4) In PO’ Setup >Approvals > Approval groups ’, Create an approval group.

5) Go to ‘Setup >Approvals > Approval Assignments’, Select the position ( as given in HRMS) and assign approval group for different document types. Now we can approve Documents

DDL, DML, DCL and TCL Commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

AUTHID DEFINER V/S AUTHID CURRENT_USER

There are lot of times we get error in Oracle Apps while trying to execute the API's at development time, due to the AUTHID DEFINER and AUTHID CURRENT_USER. This article gives you good understanding about the AUTHID DEFINER and AUTHID CURRENT_USER.

A stored procedure runs either with the rights of the caller (AUTHID CURRENT_USER) or with the rights of the procedure's owner (AUTHID DEFINER). This behaviour is specified with the AUTHID clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.


AUTHID DEFINER and AUTHID CURRENT_USER
-----------------------------------------------------------
AUTHID DEFINER:-
--------------------
Example:-
---------
The following are done in APPS scheme.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID DEFINER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
---------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.

begin
xyz.XYZ_1;
end;

It have inserted new record in the 'A' table. Note there is no synonym for the table A in SCOTT schema.

Running this program from anywhere, it is as good as running from APPS schema in case of AUTHID DEFINER.

10.2) CURRENT_USER:-
----------------------
Example:-
-----------
The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID CURRENT_USER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
-----------
grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema (scott) try to run the same query.

begin
xyz.XYZ_1;
end;

Got the error message table or view doesn't exist for the A table.

Create view for the a table and run the same program again.

create synonym 'A' for table 'A'

begin
xyz.XYZ_1;
end;

select * from a;

Now there is no error. It is inserting the record with no issue.

WITH NO AUTHID DEFINER and AUTHID CURRENT_USER :-
-----------------------------------------------------------------------
Example:-
---------
The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
---------
grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.


begin
xyz.XYZ_1;
end;

It is working in same way as it have done for the AUTHID DEFINER.

Q) Is it possible to know from the select statement if it is INVOKER(CURRENT_USER) or DEFINER
A) Yes, It is possible to get this information from the select statement. Use
SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = "Your Package Name"
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------
SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

PRAGMA

Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

1) Autonomous Transaction

Autonomous Transactions is the child transaction, which are Independent of Parent transactions. In Our Example, p1 is child transaction, which is used in the Parent transaction.

Example: -

CREATE or REPLACE Procedure p1 IS
Pragma Autonomous_transaction;
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

In the Declaration section, you will declare this Transaction as the Autonomous Transaction.

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1;
ROLLBACK;
END;

NOW Table has (1111,’PHANI’) Record. COMMIT in the PROCEDURE P1 have not commit the Outside (p1) DML operations. It will just commit p1 transactions.

The ROLLBACK will not rollback PHANI record, it will just rollback the JACK record.

CREATE or REPLACE Procedure p1 IS
BEGIN
INSERT INTO TEST_T VALUES (1111,’PHANI1’);
COMMIT;
END;

If I remove the Pragma Autonomous_transaction From the declaration section, then this transaction will become the normal transaction. Now if you try to use the same parent transaction as given below.

>> delete from TEST_T;

DECLARE
A NUMBER;
BEGIN
INSERT INTO TEST_T VALUES (2222,’JACK’);
P1; -- This transaction has ended with the COMMIT;
ROLLBACK;
END;

After executing the above transaction, you can see BOTH records got Inserted (PHANI and JACK records). Here COMMIT in P1 will commit both transactions (PHANI and JACK Records Insert) And then Rollback. Since, there are no transactions happening between COMMIT and ROLLBACK. Our ROLLBACK is not doing any ROLLBACK.

Note: - IF COMMIT is not given in P1 then, the ROLLBACK will do the ROLLBACK both the INSERT transaction (PHANI Record which is in p1 procedure and JACK Record).

2) Pragma Restrict_references

It gives the Purity Level of the Function in the package.

CREATE OR REPLACE PACKAGE PKG12 AS
FUNCTION F1 RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (F1, WNDS,
RNDS,
WNPS,
RNPS);
END PKG12;

CREATE OR REPLACE PACKAGE BODY PKG12 AS
FUNCTION F1 RETURN NUMBER IS
X NUMBER;
BEGIN
SELECT EMPNO INTO X FROM SCOTT.EMP
WHERE ENAME LIKE ‘SCOTT’;
DBMS_OUTPUT.PUT_LINE (X);
RETURN (X);
END F1;
END PKG12;

You will get the Violate It’s Associated Pragma Error. This in Purity Level, we said

It cannot read from the database. RNDS (In Our Function F1, we have SELECT STATEMENT which is reading the data from the database).

3) Pragma SERIALLY_REUSABLE

In my 5 Years of Experience in the Oracle Applications, I have never found the requirement to use this feature :). But, I found this feature is used in some standard Oracle Packages. We may use this feature for improving the performance or to meet certain requirements.

This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.

You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

Examples

WITH PRAGMA SERIALLY_REUSABLE

The following example creates a serially reusable package:

CREATE PACKAGE pkg1 IS

PRAGMA SERIALLY_REUSABLE;

num NUMBER := 0;

PROCEDURE init_pkg_state(n NUMBER);

PROCEDURE print_pkg_state;

END pkg1;

/

CREATE PACKAGE BODY pkg1 IS

PRAGMA SERIALLY_REUSABLE;

PROCEDURE init_pkg_state (n NUMBER) IS

BEGIN

pkg1.num := n;

END;

PROCEDURE print_pkg_state IS

BEGIN

dbms_output.put_line('Num: ' || pkg1.num);

END;

END pkg1;

/

begin

pkg1.init_pkg_state(10);

pkg1.PRINT_PKG_STATE;

end;

Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

Num: 0

Note: - The first block is changing the value of the variable (num) to 10 and if I check the value in same block then it is showing the changed value that is 10. But, if I try to check the value of the (num) variable then it should the default value given to it (i.e.) “0”

WITHOUT PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE pkg1 IS

num NUMBER := 0;

PROCEDURE init_pkg_state(n NUMBER);

PROCEDURE print_pkg_state;

END pkg1;

CREATE PACKAGE BODY pkg1 IS

PROCEDURE init_pkg_state (n NUMBER) IS

BEGIN

pkg1.num := n;

END;

PROCEDURE print_pkg_state IS

BEGIN

dbms_output.put_line('Num: ' || pkg1.num);

END;

END pkg1;

begin

pkg1.init_pkg_state(10);

pkg1.PRINT_PKG_STATE;

end;

>>Num: 10

begin

pkg1.PRINT_PKG_STATE;

end;

>>Num: 10

Note: - Now, you may noticed the difference. The second block is giving us the changed value.

DROP PACKAGE pkg1;

(There are many other pragma's like Pragma Exception_init etc. I have not convered these concepts in this article. I will cover them in Exception concept article)

NOCOPY

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value.

When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.

With the NOCOPY hint the parameters are passed by reference and on successful completion the outcome is the same, but unhandled exceptions may leave the parameters in an altered state, so programs must handle errors or cope with the suspect values.

The nocopy.sql script compares the performance of both methods by passing a populated collection as a parameter.

nocopy.sql

SET SERVEROUTPUT ON
DECLARE

TYPE t_tab IS TABLE OF VARCHAR2(32767);
l_tab t_tab := t_tab();
l_start NUMBER;

PROCEDURE in_out (p_tab IN OUT t_tab) IS
BEGIN
NULL;
END;

PROCEDURE in_out_nocopy (p_tab IN OUT NOCOPY t_tab) IS
BEGIN
NULL;
END;

BEGIN
l_tab.extend;
l_tab(1) := '1234567890123456789012345678901234567890';
l_tab.extend(999999, 1); -- Copy element 1 into 2..1000000


-- Time normal IN OUT
l_start := DBMS_UTILITY.get_time;

in_out(l_tab);

DBMS_OUTPUT.put_line('IN OUT : ' ||
(DBMS_UTILITY.get_time - l_start));

-- Time IN OUT NOCOPY
l_start := DBMS_UTILITY.get_time;

in_out_nocopy(l_tab); -- pass IN OUT NOCOPY parameter

DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' ||
(DBMS_UTILITY.get_time - l_start));
END;
/

The output of the script clearly demonstrates the performance improvements possible when using the NOCOPY hint.

SQL> @nocopy.sql

IN OUT : 122
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.