Oracle EBS R12 how to call standard API/packages from custom form or reports
来源:互联网 发布:美橙互联域名管理 编辑:程序博客网 时间:2024/05/20 03:08
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, finally 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’ andapps.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
- Oracle EBS R12 how to call standard API/packages from custom form or reports
- How to invoke Standard Choose Organizations Form From your form
- How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++ How
- Oracle EBS R12 - SQL to find session ID of the running request from request ID
- Oracle EBS Form CUSTOM.PLL详解
- Oracle EBS Form CUSTOM.PLL详解
- Oracle EBS Form CUSTOM.PLL详解
- How to extract RPM or DEB packages
- How to extract RPM or DEB packages
- How to call an external C function from within Oracle
- Setup Organization Hierachy in Oracle EBS 11i or R12
- How to write your own custom Form
- Oracle EBS R12 - Steps and Issues/Resolutions during R12.1.1 to R12.1.3 Upgration
- How to use script to get all oracle EBS Form name and corresponding fmb file name
- How to export Oracle Database and packages
- Oracle EBS R12 - Clone EBS R12.1.1 on Oracle Linux 64 5.7 to Oracle Linux 64 5.7
- Oracle EBS R12 - Use Rman to Clone Oracle EBS R12.1.1 without shutting down source Database and MT
- How to call function from register view
- Struts+Ibtis+Spring整合基础案例
- EXT renderer store 动态拼接ur
- TabActivity 返回键 dispatchKeyEvent
- Ubuntu下如何切换到ROOT登录
- 控制 WCF 服务主机的自动启动
- Oracle EBS R12 how to call standard API/packages from custom form or reports
- configurate source insight with CLF
- Java加解密艺术之DES对称加密算法
- TMS320C6474(六核)使用经验总结
- Android调用手机拍照以及从相册选择照片 不指定
- iOS中assign、copy 、retain等关键字的含义
- 第十五周实验报告任务2
- kryptonHeaderGroup2控件
- Java加解密艺术之DESede对称加密算法