This a set of scripts that will generate api code templates, for all procedures in all packages where package name like the parameter.. Beware: this script will generate a lot of code if parameter is less restrictive. Pls. feel free to customize for your need. The api3.sql will generate template and mapping info for the “Excel For Oracle Applications” tool. The api3.sql has the spool command, spooling to a directory named “api” – pls. create this directory before use, or alter the api3.sql for your need.
SQL> @api HZ%ACC%V2pub%
@api2 HZ_CUST_ACCOUNT_ROLE_V2PUB
@api2 HZ_CUST_ACCOUNT_ROLE_V2PUB_JW
@api2 HZ_CUST_ACCOUNT_SITE_V2PUB
@api2 HZ_CUST_ACCOUNT_SITE_V2PUB_JW
@api2 HZ_CUST_ACCOUNT_V2PUB
@api2 HZ_CUST_ACCOUNT_V2PUB_JW
…
@api3 HZ_CUST_ACCOUNT_SITE_V2PUB CREATE_CUST_ACCT_SITE 0
– *****************************************************************************
– Oracle API Mapping script for the “Excel for Oracle Applications” tool
– *****************************************************************************
– copyright 2008 - www.thomaslundqvist.dk
– *****************************************************************************
– package HZ_CUST_ACCOUNT_SITE_V2PUB
– procedure CREATE_CUST_ACCT_SITE
– overload 0
– extract date 31-jul-2008 16:24
– *****************************************************************************
– copy to the plsql sheet:
– *****************************************************************************
1
DECLARE
– copyright 2008 - www.thomaslundqvist.dk
P_INIT_MSG_LIST VARCHAR2(2000);
P_CUST_ACCT_SITE_REC APPS.HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE;
X_CUST_ACCT_SITE_ID NUMBER;
X_RETURN_STATUS VARCHAR2(2000);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
BEGIN
–$MAPPING
– P_INIT_MSG_LIST := ”;
– P_CUST_ACCT_SITE_REC.CUST_ACCT_SITE_ID := 0;
– P_CUST_ACCT_SITE_REC.CUST_ACCOUNT_ID := 0;
– P_CUST_ACCT_SITE_REC.PARTY_SITE_ID := 0;
– P_CUST_ACCT_SITE_REC.ATTRIBUTE_CATEGORY := ”;
– P_CUST_ACCT_SITE_REC.ATTRIBUTE1 := ”;
…
– P_CUST_ACCT_SITE_REC.ATTRIBUTE20 := ”;
– P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE_CATEGORY := ”;
– P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE1 := ”;
…
– P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE20 := ”;
– P_CUST_ACCT_SITE_REC.ORIG_SYSTEM_REFERENCE := ”;
– P_CUST_ACCT_SITE_REC.ORIG_SYSTEM := ”;
– P_CUST_ACCT_SITE_REC.STATUS := ”;
– P_CUST_ACCT_SITE_REC.CUSTOMER_CATEGORY_CODE := ”;
– P_CUST_ACCT_SITE_REC.LANGUAGE := ”;
– P_CUST_ACCT_SITE_REC.KEY_ACCOUNT_FLAG := ”;
– P_CUST_ACCT_SITE_REC.TP_HEADER_ID := 0;
– P_CUST_ACCT_SITE_REC.ECE_TP_LOCATION_CODE := ”;
– P_CUST_ACCT_SITE_REC.PRIMARY_SPECIALIST_ID := 0;
– P_CUST_ACCT_SITE_REC.SECONDARY_SPECIALIST_ID := 0;
– P_CUST_ACCT_SITE_REC.TERRITORY_ID := 0;
– P_CUST_ACCT_SITE_REC.TERRITORY := ”;
– P_CUST_ACCT_SITE_REC.TRANSLATED_CUSTOMER_NAME := ”;
– P_CUST_ACCT_SITE_REC.CREATED_BY_MODULE := ”;
– P_CUST_ACCT_SITE_REC.APPLICATION_ID := 0;
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
(P_INIT_MSG_LIST => P_INIT_MSG_LIST
,P_CUST_ACCT_SITE_REC => P_CUST_ACCT_SITE_REC
,X_CUST_ACCT_SITE_ID => X_CUST_ACCT_SITE_ID
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
);
for i in 1..x_msg_count loop
x_msg_data := x_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_TRUE );
END loop;
:x_status := :x_status || x_return_status;:
:x_msg_data1 := substr(x_msg_data,1,125);
if x_return_status <> ‘S’ then
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
end if;
–$ROW_COMMIT
EXCEPTION WHEN OTHERS THEN
:x_status := ‘E’;
:x_msg_data1 := substr(SQLERRM,1,125);
END;
/
– *****************************************************************************
– copy to the mapping sheet:
– *****************************************************************************
column datatype default mapping
P_INIT_MSG_LIST V
P_CUST_ACCT_SITE_REC.CUST_ACCT_SITE_ID N
P_CUST_ACCT_SITE_REC.CUST_ACCOUNT_ID N
P_CUST_ACCT_SITE_REC.PARTY_SITE_ID N
P_CUST_ACCT_SITE_REC.ATTRIBUTE_CATEGORY V
P_CUST_ACCT_SITE_REC.ATTRIBUTE1 V
…
P_CUST_ACCT_SITE_REC.ATTRIBUTE20 V
P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE_CATEGORY V
P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE1 V
…
P_CUST_ACCT_SITE_REC.GLOBAL_ATTRIBUTE20 V
P_CUST_ACCT_SITE_REC.ORIG_SYSTEM_REFERENCE V
P_CUST_ACCT_SITE_REC.ORIG_SYSTEM V
P_CUST_ACCT_SITE_REC.STATUS V
P_CUST_ACCT_SITE_REC.CUSTOMER_CATEGORY_CODE V
P_CUST_ACCT_SITE_REC.LANGUAGE V
P_CUST_ACCT_SITE_REC.KEY_ACCOUNT_FLAG V
P_CUST_ACCT_SITE_REC.TP_HEADER_ID N
P_CUST_ACCT_SITE_REC.ECE_TP_LOCATION_CODE V
P_CUST_ACCT_SITE_REC.PRIMARY_SPECIALIST_ID N
P_CUST_ACCT_SITE_REC.SECONDARY_SPECIALIST_ID N
P_CUST_ACCT_SITE_REC.TERRITORY_ID N
P_CUST_ACCT_SITE_REC.TERRITORY V
P_CUST_ACCT_SITE_REC.TRANSLATED_CUSTOMER_NAME V
P_CUST_ACCT_SITE_REC.CREATED_BY_MODULE V
P_CUST_ACCT_SITE_REC.APPLICATION_ID N
Syntax: @api <partial package name>
You will find the api scripts here: sql4oracle.com
Pingback: xls4oracle » Blog Archive » Developing the load_user_resp_v102.xls
Pingback: xls4oracle » Blog Archive » New functionality in 3.0.0.165