Oracle EBS R12 how to call standard API/packages from custom form or reports

Okay guys and gals

We had a requirement to use certain Oracle standard API/packages from custom developer forms. After loads of struggle and asking various Oracle related forums, we hardly had a chance to get any satisfactory answers to our query “how to call oracle standard API or packages through custom forms or reports”

Finally with the help of a long term friend cum technical support person Mr. Anil Menon, we were able to successfully call the standard API or package call with our custom (test) form.

For the example we used “apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES” procedure which indirectly refers fnd_api.g_false and apps.inv_quantity_tree_pub.g_transaction_mode parameters/constants defined in the package definitions.

Actual call to the API through a PL/SQL environment (Toad/Oracle PL/SQL) code is as following

SET SERVEROUTPUT ON;
DECLARE
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2(1000);
v_inventory_item_id VARCHAR2(250);
v_organization_id VARCHAR2(10);
BEGIN

v_inventory_item_id := 24445;
v_organization_id := 901;

inv_quantity_tree_grp.clear_quantity_cache;
DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id);
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => 'ABC-01', --NULL for whole org
p_locator_id => NULL,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr);
DBMS_OUTPUT.put_line ('on hand Quantity :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr);
END;

This PL/SQL procedure will successfully print the results during a PL/SQL session through Toad or Oracle PL/SQL session. However, if you would try to invoke the same code through a PL/SQL procedure from a developer form development instance, the errors will start from the call to “fnd_api.g_false” stating “Implementation restriction: ‘APPS.FND_API.G_FALSE’ cannot directly access remote package variable or cursor. Under the package specification you will find G_FALSE constant has the the value ‘F’ and apps.inv_quantity_tree_pub.g_transaction_mode has a value ‘2’ pre-defined.

Umm, not a straight forward method to dwell the packages, find the constant values and modifying the PL/SQL script. For a small API call like above one, a programmer could use this approach as a work around, however, while dealing with APIs which take 10s of parameters, it could be really tiring.

Here is the workaround we used

Created a custom database level package


CREATE OR REPLACE PACKAGE XX_CHK_QTY IS
PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER);
END;

and created corresponding package body as following


CREATE OR REPLACE PACKAGE BODY XX_CHK_QTY AS
PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER) IS
v_api_return_status  VARCHAR2 (1);
v_qty_oh             NUMBER;
v_qty_res_oh         NUMBER;
v_qty_res            NUMBER;
v_qty_sug            NUMBER;
v_qty_att            NUMBER;
v_qty_atr            NUMBER;
v_msg_count          NUMBER;
v_msg_data           VARCHAR2(1000);
--v_inventory_item_id  VARCHAR2(250);
--v_organization_id    VARCHAR2(10) ;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number  => 1.0,
p_init_msg_lst        => apps.fnd_api.g_false,
--p_init_msg_lst        => 'F',
x_return_status       => v_api_return_status,
x_msg_count           => v_msg_count,
x_msg_data            => v_msg_data,
p_organization_id     => org_id, --v_organization_id,
p_inventory_item_id   => item_id, --v_inventory_item_id,
p_tree_mode           => apps.inv_quantity_tree_pub.g_transaction_mode,
--p_tree_mode           => 2,
p_onhand_source       => 3,
p_is_revision_control => FALSE,
p_is_lot_control      => FALSE,
p_is_serial_control   => FALSE,
p_revision            => NULL,
p_lot_number          => NULL,
p_subinventory_code   => subinv,
p_locator_id          => NULL,
x_qoh                 => v_qty_oh,
x_rqoh                => v_qty_res_oh,
x_qr                  => v_qty_res,
x_qs                  => v_qty_sug,
x_att                 => v_qty_att,
x_atr                 => v_qty_atr);
oqtt := v_qty_att;
oqtr := v_qty_atr;
END;
END XX_CHK_QTY;


As you could see we are using 2 numbers of OUT variables to store the output data, which will be referred in the forms at later stages.

Our sample form has a control block and 3 items

:ITEM_ID, :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE and under the WVI(WHEN-VALIDATE-ITEM) scope for the block item :ITEM_ID  we wanted to populate total quantity available to transact and total quantity available to reserve into block items :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE

Following is how we call the custom procedure through WVI trigger:

Declare
oqtt number := 0;
oqtr number := 0;
Begin
apps.XX_CHK_QTY.retrive_quantity(:CTRL.ITEM_ID,901,’ABC-01′,oqtt,oqtr);
:CTRL.QTY_AVL_TRANSACT := oqtt;
:CTRL.QTY_AVL_RESERVE := oqtr;
End;

and bingo! as the procedure was called from a database package, there were no compilation errors pointing towards referring remote variables or cursors and once executed all intended results were fetched to corresponding block items.

As we are also learning how to use Oracle APIs within custom forms/reports, we hope this guideline would be useful for those few who are trying to find a way to start calling Oracle’s standard API/packages from such environments.

for Windows7bugs

Admin

3 Responses to Oracle EBS R12 how to call standard API/packages from custom form or reports

  1. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,trunc…

    […]Oracle EBS R12 how to call standard API/packages from custom form or reports « Duh! Microsoft did it again[…]…

  2. Qr Code says:

    Qr Codes…

    […]Oracle EBS R12 how to call standard API/packages from custom form or reports « Duh! Microsoft did it again[…]…

  3. Wafaa says:

    Hello
    So many thanks , this really solved my problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: