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

April 17, 2011

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

Advertisements

Oralce apps reports, instance producing gibberish character mapping

July 11, 2010

We are @ the final stage of implementing Oracle e-business suit 12 and recently came across a peculiar font mapping problem with custom developed reports. We use both Developer 6i reports developer as well as 10g reports developer.
Once the reports are tested and uploaded to the apps instance, the outputs were always producing gibrish (PDF outputs especially). However after few days of experimenting, finally we found a work around (hopefully)!

Following are the tips:

Design your report, test it.
Do not change the default font until your report is ready to upload to the instance.

Now decide the actual font you need to use with your final report.
And never select the font from the font list drop down, check the image:

Instead click on format menu item, select “font” and choose your font. Generally we use Courier New or Times New Roman (Western) for our reports and this method has solved our months long troubles with the reports. Please check the attached image:

When you need to change the font sizing and weight for individual items, follow the same method and once you are satisfied with the layout upload to the apps instance.
We hope this work around is helpful for few out there.

Windows7bugs team