需求：将PL/SQL的程序包通过Oracle E-BUSINESS SUITE(EBS)发布为WEB SERVICE（SOAP），然后通过axis2客户端调用WEB SERVICE（SOAP），从而执行相应的存储过程。
具体通过Oracle E-BUSINESS SUITE发布Web Service的步骤如下：
1.创建 PRODUCT FAMILY(cux_pf为例)(如果之前环境中已经部署过可以不进行此操作)
begin
-- Call the procedure
ad_pa_insert_package.insert_ad_pm_product_info(x_product_abbreviation => 'cux_pf',
x_pseudo_product_flag => 'N',
x_product_family_flag => 'Y',
x_application_short_name => NULL,
x_product_name => 'CUX Developer',
x_product_family_abbreviation => NULL,
x_product_family_name => NULL,
x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_last_updated_by => -1,
x_created_by => -1);
COMMIT;
end;
2.注册PRODUCT (如果之前环境中已经部署过可以不进行此操作)
--注册CUX应用到产品家族中
begin
-- Call the procedure
ad_pa_insert_package.insert_ad_pm_product_info(x_product_abbreviation => 'cux',
x_pseudo_product_flag => 'N',
x_product_family_flag => 'N',
x_application_short_name => 'CUX',
x_product_name => 'CUX Developer',
x_product_family_abbreviation => NULL,
x_product_family_name => NULL,
x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_last_updated_by => -1,
x_created_by => -1);
COMMIT;
end;
3.关联CUX Developer应用到产品家族CUX Developer下 (如果之前环境中已经部署过可以不进行此操作)
--关联CUX应用到产品家族CUX Developer下
begin
-- Call the procedure
ad_pa_insert_package.insert_ad_pm_prod_family_map(x_product_abbreviation => 'cux',
x_product_family_abbreviation => 'cux_pf',
x_aru_update_date => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_currdate => to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),
x_last_updated_by => -1,
x_created_by => -1);
COMMIT;
end;
执行完成后通过下面2条SQL验证是否添加成功，查出3条数据表示添加成功。
SELECT * FROM ad_pm_product_info x WHERE x.product_name LIKE 'CUX%';
SELECT * FROM ad_pm_prod_family_map m WHERE m.product_abbreviation LIKE 'cux%';
4.增加一个BUSINESS_ENTITY的lookup_code（如果你想要在原来建好的lookup_code下发布服务，则这一步骤也不是必须的）
这里新建的代码为SIX_WS_CLIENT，这个代码会在下面发布服务的程序里的注释中（ @rep:category BUSINESS_ENTITY SIX_WS_CLIENT）用到，具体增加lookup_code的路径如下图（应用开发员职责->应用产品->代码->Oracle Application Object Library）：
5.发布服务
1)根据规范编写PL/SQL程序包的包头代码：
CREATE OR REPLACE PACKAGE HDPI_FDL_XJ_T_PKG AS
/* $Header: $ */
/*#
* Mobile WS Client For GONGHAO FDL STORAGE REPORT
* @rep:scope public
* @rep:product CUX
* @rep:lifecycle active
* @rep:displayname HDPI_FDL_XJ_T_PKG
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY SIX_WS_CLIENT
* @rep:ihelp FND/@o_funcsec#o_funcsec See the related online help
*/
/*#
* 查询各个电厂当日发电量
* @param P_DATE_STR String1
* @param R_FDL_DR_COLUMN TYPE_FDL_DR_COLUMN
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname QUERY_FDL_COLUMN
* @rep:compatibility S
* @rep:ihelp FND/@mesgdict#mesgdict See the related online help
*/
TYPE RECORD_FDL_DR_OBJ IS RECORD(
FDL_DR NUMBER,
REGION_ID NUMBER,
ENTITY_ORG_ID NUMBER,
CMIS_COMPANY_NAME VARCHAR2(250),
REGION_NAME VARCHAR2(250));
TYPE TYPE_FDL_DR_COLUMN IS TABLE OF RECORD_FDL_DR_OBJ INDEX BY BINARY_INTEGER;
PROCEDURE QUERY_FDL_COLUMN(P_DATE_STR IN VARCHAR2,
R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN);
/*#
* 查询当日发电量小计.
* @param P_DATE_STR String1
* @param R_FDL_DR_XJ_COLUMN TYPE_FDL_DR_XJ_COLUMN
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname QUERY_FDL_COLUMN
* @rep:compatibility S
* @rep:ihelp FND/@mesgdict#mesgdict See the related online help
*/
TYPE RECORD_FDL_DR_XJ_OBJ IS RECORD(
FDL_DR_XJ NUMBER,
REGION_ID NUMBER,
REGION_NAME VARCHAR2(250));
TYPE TYPE_FDL_DR_XJ_COLUMN IS TABLE OF RECORD_FDL_DR_XJ_OBJ INDEX BY BINARY_INTEGER;
PROCEDURE QUERY_FDL_XJ_COLUMN(P_DATE_STR IN VARCHAR2,
R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN);
/*#
* 查询报表结果集，返回所有需要的数据
* @param P_DATE_STR String1
* @param R_FDL_DR_COLUMN TYPE_FDL_DR_COLUMN
* @param R_FDL_DR_XJ_COLUMN TYPE_FDL_DR_XJ_COLUMN
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname QUERY_GH_REPORT
* @rep:compatibility S
* @rep:ihelp FND/@mesgdict#mesgdict See the related online help
*/
PROCEDURE QUERY_GH_REPORT(P_DATE_STR IN VARCHAR2,
R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN,
R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN);
end HDPI_FDL_XJ_T_PKG;
这个代码中：
@rep:product CUX表示发布的服务属于这个产品；@rep:displayname HDPI_FDL_XJ_T_PKG表示服务显示为这个名称；@rep:category BUSINESS_ENTITY SIX_WS_CLIENT表示服务的目录结构，这里服务表示发布在SIX_WS_CLIENT（测试6）下面；
程序包的包体代码如下：
CREATE OR REPLACE PACKAGE BODY HDPI_GH_REPORT_PKG AS
PROCEDURE QUERY_FDL_COLUMN(P_DATE_STR IN VARCHAR2,
R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN) AS
BEGIN
select ds2.FDL_DR, ds1.REGION_ID, ds1.ENTITY_ORG_ID,ds1.CMIS_COMPANY_NAME,ds1.REGION_NAME
BULK COLLECT
INTO R_FDL_DR_COLUMN
FROM (SELECT e.entity_org_id,
e.entity_code,
e.CMIS_COMPANY_NAME,
e.entity_id,
r.region_cd,
decode(r.region_name, '宁夏', '宁夏省', r.region_name) region_name,
r.REGION_ID,
e.sort_id
FROM datacenter.entity@connect_to_dtcenter e,
datacenter.region@connect_to_dtcenter r
WHERE e.entity_type = '电厂'
and e.level_three = '火电'
and e.region_id = r.region_id
and e.CMIS_COMPANY_NAME is not null
order by case
when r.region_name like '山东%' then
0
when r.region_name like '宁夏%' then
1
when r.region_name like '安徽%' then
2
when r.region_name like '河南%' then
3
when r.region_name like '四川%' then
4
when r.region_name like '河北%' then
5
when r.region_name like '广东%' then
6
when r.region_name like '浙江%' then
7
end,
E.SORT_ID) ds1,
(SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A,
datacenter.HDPI_SET@connect_to_dtcenter B
WHERE A.SET_ID = B.SET_ID
AND A.CALENDAR_ID = P_DATE_STR
GROUP BY B.ENTITY_ID) ds2
WHERE ds1.ENTITY_ID = ds2.ENTITY_ID;
END QUERY_FDL_COLUMN;
PROCEDURE QUERY_FDL_XJ_COLUMN(P_DATE_STR IN VARCHAR2,
R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN) AS
BEGIN
SELECT ROUND(sum(ds2.FDL_DR),2) AS FDL_DR_XJ,ds1.REGION_ID,ds1.REGION_NAME
BULK COLLECT
INTO R_FDL_DR_XJ_COLUMN
FROM (SELECT e.entity_org_id,
e.entity_code,
e.CMIS_COMPANY_NAME,
e.entity_id,
r.region_cd,
decode(r.region_name, '宁夏', '宁夏省', r.region_name) region_name,
r.REGION_ID,
e.sort_id
FROM datacenter.entity@connect_to_dtcenter e,
datacenter.region@connect_to_dtcenter r
WHERE e.entity_type = '电厂'
and e.level_three = '火电'
and e.region_id = r.region_id
and e.CMIS_COMPANY_NAME is not null
order by case
when r.region_name like '山东%' then
0
when r.region_name like '宁夏%' then
1
when r.region_name like '安徽%' then
2
when r.region_name like '河南%' then
3
when r.region_name like '四川%' then
4
when r.region_name like '河北%' then
5
when r.region_name like '广东%' then
6
when r.region_name like '浙江%' then
7
end,
E.SORT_ID) ds1,
(SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A,
datacenter.HDPI_SET@connect_to_dtcenter B
WHERE A.SET_ID = B.SET_ID
AND A.CALENDAR_ID = P_DATE_STR
GROUP BY B.ENTITY_ID) ds2
WHERE ds1.ENTITY_ID = ds2.ENTITY_ID
group by ds1.REGION_ID,ds1.REGION_NAME;
END QUERY_FDL_XJ_COLUMN;
PROCEDURE QUERY_GH_REPORT(P_DATE_STR IN VARCHAR2,
R_FDL_DR_COLUMN OUT TYPE_FDL_DR_COLUMN,
R_FDL_DR_XJ_COLUMN OUT TYPE_FDL_DR_XJ_COLUMN) AS
BEGIN
QUERY_FDL_COLUMN(P_DATE_STR, R_FDL_DR_COLUMN);
QUERY_FDL_XJ_COLUMN(P_DATE_STR, R_FDL_DR_XJ_COLUMN);
END QUERY_GH_REPORT;
END;
在数据库中创建上面的程序包，创建成功之后，将包头的创建代码复制一份，另存为.pls格式文件，比如我这里叫HDPI_FDL_XJ_T_PKG.pls。
2)将保存的.pls文件上传（我使用的是WinSCP）到$CUX_TOP/patch/115/sql目录下；
3)执行如下命令来生成.ildt文件：
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin cux:patch/115/sql:HDPI_FDL_XJ_T_PKG.pls:12.0=HDPI_FDL_XJ_T_PKG.pls
4)执行如下命令将上面生成的iLDT文件通过FNDLOAD命令工具上传到Oracle Integration Repositoy中：
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct HDPI_FDL_XJ_T_PKG_pls.ildt
5)接下来用管理员身份登录，到集成SOA网关->集成信息库->下的HDPI Custom Application Family->Custom Application->测试6中（或者直接在集成信息库中搜索发布的服务名）找到发布的服务名：
全选你发布的方法，点击创建授权，搜索你要授权的用户名，点击应用；
6)接下来生成wsdl文件；
7)生成wsdl文件之后，勾选“用户名变量”，进行部署；
8)接下来到你生成wsdl文件的目录下（我的是/sourcing/prod/oraapp/inst/apps/test_dev03/soa/PLSQL/4710）找到你程序包中的几个过程名的wsdl文件，删除文件中的下列行：
IRepOverloadSeq = 1
到此，服务发布结束，你可以用soapui测试一下发布的soap服务（不知为何我的授权要一个多小时才生效）。
参考文章：
- http://blog.csdn.net/wx110120121wx/article/details/64915967；
- https://jingyan.baidu.com/article/6079ad0e7bfde428ff86db90.html；
- http://www.cnblogs.com/xiyuanbaiyun/p/4277881.html；
