接口概览
一.创建接口表（中间表）
1.1 创建表
create table CUX.CUX_INTF_ITEMS
(
id NUMBER not null,
organization_id NUMBER,
item_id NUMBER,
sys_code VARCHAR2(10),
item_code VARCHAR2(40),
item_name VARCHAR2(2000),
item_spec VARCHAR2(80),
unit VARCHAR2(30),
is_qc NUMBER,
stock_max_num NUMBER,
stock_min_num NUMBER,
gross_weight NUMBER,
net_weight NUMBER,
vendor VARCHAR2(240),
shelf_life NUMBER,
shelf_life_unit VARCHAR2(30),
factory VARCHAR2(30),
item_status VARCHAR2(30),
enabled_flag VARCHAR2(1),
cud_flag VARCHAR2(1),
creator_id VARCHAR2(80),
created_at VARCHAR2(80),
updater_id VARCHAR2(80),
updated_at VARCHAR2(80),
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240),
trans_flag VARCHAR2(1),
trans_date DATE,
trans_msg VARCHAR2(2000),
request_id NUMBER,
trans_group NUMBER,
batch_id NUMBER,
record_last_update_date DATE,
creation_date DATE,
created_by NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
item_type VARCHAR2(80),
key_flag VARCHAR2(1),
item_full_code VARCHAR2(50)
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
一般来说：
代码/code长度 ：30；名称/描述：240；详细描述 ：2000
1.2 给字段加上注释
comment on column CUX.CUX_INTF_ITEMS.id
is '主键';
comment on column CUX.CUX_INTF_ITEMS.organization_id
is '组织ID';
comment on column CUX.CUX_INTF_ITEMS.item_id
is '物料ID';
comment on column CUX.CUX_INTF_ITEMS.sys_code
is '目标系统';
comment on column CUX.CUX_INTF_ITEMS.item_code
is '物料编码';
comment on column CUX.CUX_INTF_ITEMS.item_name
is '物料名称';
comment on column CUX.CUX_INTF_ITEMS.item_spec
is '物料规格';
comment on column CUX.CUX_INTF_ITEMS.unit
is '单位';
comment on column CUX.CUX_INTF_ITEMS.is_qc
is '是否需要检验';
comment on column CUX.CUX_INTF_ITEMS.stock_max_num
is '最大库存';
comment on column CUX.CUX_INTF_ITEMS.stock_min_num
is '最小库存';
comment on column CUX.CUX_INTF_ITEMS.gross_weight
is '毛重';
comment on column CUX.CUX_INTF_ITEMS.net_weight
is '净重';
comment on column CUX.CUX_INTF_ITEMS.vendor
is '供应商';
comment on column CUX.CUX_INTF_ITEMS.shelf_life
is '有效期';
comment on column CUX.CUX_INTF_ITEMS.shelf_life_unit
is '有效期单位';
comment on column CUX.CUX_INTF_ITEMS.factory
is '工厂';
comment on column CUX.CUX_INTF_ITEMS.item_status
is '物料状态';
comment on column CUX.CUX_INTF_ITEMS.enabled_flag
is '启用标志';
comment on column CUX.CUX_INTF_ITEMS.cud_flag
is '处理标识';
comment on column CUX.CUX_INTF_ITEMS.creator_id
is '创建人';
comment on column CUX.CUX_INTF_ITEMS.created_at
is '创建时间';
comment on column CUX.CUX_INTF_ITEMS.updater_id
is '更新人';
comment on column CUX.CUX_INTF_ITEMS.updated_at
is '更新时间';
comment on column CUX.CUX_INTF_ITEMS.attribute1
is '弹性域';
comment on column CUX.CUX_INTF_ITEMS.attribute2
is '弹性域';
comment on column CUX.CUX_INTF_ITEMS.attribute3
is '弹性域';
comment on column CUX.CUX_INTF_ITEMS.attribute4
is '弹性域';
comment on column CUX.CUX_INTF_ITEMS.attribute5
is '弹性域';
comment on column CUX.CUX_INTF_ITEMS.trans_flag
is '同步状态（S：成功；E：失败；N：未处理）';
comment on column CUX.CUX_INTF_ITEMS.trans_date
is '同步日期';
comment on column CUX.CUX_INTF_ITEMS.trans_msg
is '同步消息';
comment on column CUX.CUX_INTF_ITEMS.request_id
is '请求ID';
comment on column CUX.CUX_INTF_ITEMS.trans_group
is '数据处理组ID（同一批次下，分组多次处理）';
comment on column CUX.CUX_INTF_ITEMS.batch_id
is '批次ID';
comment on column CUX.CUX_INTF_ITEMS.record_last_update_date
is '记录最后更新日期';
comment on column CUX.CUX_INTF_ITEMS.last_update_date
is '最后更新时间';
comment on column CUX.CUX_INTF_ITEMS.item_type
is '物料类型';
comment on column CUX.CUX_INTF_ITEMS.key_flag
is '是否关键性物料';
comment on column CUX.CUX_INTF_ITEMS.item_full_code
is '库存组织代码 + "-" + 物料编码，仅供WMS使用';
1.3 创建索引
根据查询的时候用到次数多的字段创建索引（这一步可以后面再做）
create index CUX.CUX_INTF_ITEMS_N1 on CUX.CUX_INTF_ITEMS (ORGANIZATION_ID, ITEM_ID, SYS_CODE)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
1.4创建主键和外键
alter table CUX.CUX_INTF_ITEMS
add primary key (ID)
using index
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
二.创建表的序列
create sequence CUX.CUX_INTF_ITEMS_S
minvalue 1
maxvalue 9999999999999999999999999999
start with 341
increment by 1
cache 20;
三.修改包头
先修改包头的名字，在create和end中。
再修改传入的参数名称，
比如collect_data和main中
四.修改包体
4.1 declaration
修改g_pkg_name为对应的包名
4.2 get_batch_id
get_batch_id中修改序列为对应名称
4.3 gen_ws_clob
根据接口概览拼接对应格式的报文
4.4 group_data
修改对应的地方
4.5 collect_data
collect_data中有多个逻辑,修改插入的数据
4.5.1 物料数据
CURSOR cur_data IS
SELECT msi.organization_id org_id,
msi.inventory_item_id item_id,
msi.segment1 item_code,
msi.description item_desc,
msi.attribute1 item_spec,
msi.PRIMARY_UOM_CODE unit,
decode(msi.RECEIVING_ROUTING_ID, 2, 0, 1) is_qc,
nvl(msi.MAX_MINMAX_QUANTITY, 0) stock_max_num,
nvl(msi.MIN_MINMAX_QUANTITY, 0) stock_min_num,
msi.UNIT_WEIGHT gross_weight,
msi.UNIT_WEIGHT net_weight,
null vendor,
msi.SHELF_LIFE_DAYS shelf_life,
msi.SHELF_LIFE_CODE shelf_life_unit,
ood.ORGANIZATION_CODE factory,
msi.INVENTORY_ITEM_STATUS_CODE item_status,
msi.ENABLED_FLAG enabled_flag,
'C' cud_flag,
fuc.USER_NAME creator_id,
msi.CREATION_DATE created_at,
fud.USER_NAME updater_id,
msi.LAST_UPDATE_DATE updated_at,
msi.last_update_date record_last_update_date
FROM mtl_system_items_b msi,
org_organization_definitions ood,
fnd_user fuc,
fnd_user fud
WHERE msi.organization_id = ood.ORGANIZATION_ID
and ood.ORGANIZATION_CODE = 'S02'
and msi.CREATED_BY = fuc.USER_ID
and msi.LAST_UPDATED_BY = fud.USER_ID
AND msi.segment1 = nvl(p_item_code, msi.segment1)
AND NOT EXISTS
(SELECT 1
FROM cux_intf_items his
WHERE his.organization_id = msi.organization_id
AND his.item_id = msi.inventory_item_id
and his.sys_code = g_system_code);
TYPE rec_data_tbl IS TABLE OF cur_data%ROWTYPE INDEX BY BINARY_INTEGER;
tbl rec_data_tbl;
4.5.2 状态发生变化的数据
CURSOR cur_data_change IS
SELECT msi.organization_id org_id,
msi.inventory_item_id item_id,
msi.segment1 item_code,
msi.description item_desc,
msi.attribute1 item_spec,
msi.PRIMARY_UOM_CODE unit,
decode(msi.RECEIVING_ROUTING_ID, 2, 0, 1) is_qc,
msi.MAX_MINMAX_QUANTITY stock_max_num,
msi.MIN_MINMAX_QUANTITY stock_min_num,
msi.UNIT_WEIGHT gross_weight,
msi.UNIT_WEIGHT net_weight,
null vendor,
msi.SHELF_LIFE_DAYS shelf_life,
msi.SHELF_LIFE_CODE shelf_life_unit,
ood.ORGANIZATION_CODE factory,
msi.INVENTORY_ITEM_STATUS_CODE item_status,
msi.ENABLED_FLAG enabled_flag,
'U' cud_flag,
fuc.USER_NAME creator_id,
msi.CREATION_DATE created_at,
fud.USER_NAME updater_id,
msi.LAST_UPDATE_DATE updated_at,
msi.last_update_date record_last_update_date,
his.sys_code
FROM mtl_system_items_b msi,
org_organization_definitions ood,
fnd_user fuc,
fnd_user fud,
cux_intf_items his
WHERE msi.organization_id = his.organization_id
AND msi.inventory_item_id = his.item_id
and his.sys_code = g_system_code /*来源系统*/
and msi.organization_id = ood.ORGANIZATION_ID
and ood.ORGANIZATION_CODE = his.factory
and msi.CREATED_BY = fuc.USER_ID
and msi.LAST_UPDATED_BY = fud.USER_ID
AND msi.segment1 = nvl(p_item_code, msi.segment1)
AND msi.LAST_UPDATE_DATE > his.record_last_update_date;
TYPE rec_change_tbl IS TABLE OF cur_data_change%ROWTYPE INDEX BY BINARY_INTEGER;
tbl_c rec_change_tbl;
BEGIN
o_return_code := fnd_api.g_ret_sts_success;
o_return_msg := NULL;
-- 加载至缓存
OPEN cur_data;
FETCH cur_data BULK COLLECT
INTO tbl;
CLOSE cur_data;
4.5.3 未写入的新物料
FOR idx IN 1 .. tbl.count LOOP
BEGIN
rec.id := cux_intf_items_s.nextval;
rec.sys_code := g_system_code;
rec.batch_id := g_batch_id;
rec.request_id := g_request_id;
rec.organization_id := tbl(idx).org_id;
rec.item_id := tbl(idx).item_id;
rec.item_code := tbl(idx).item_code;
rec.item_name := tbl(idx).item_desc;
rec.item_spec := tbl(idx).item_spec;
rec.is_qc := tbl(idx).is_qc;
rec.item_status := tbl(idx).item_status;
rec.stock_max_num := tbl(idx).stock_max_num;
rec.stock_min_num := tbl(idx).stock_min_num;
rec.unit := tbl(idx).unit;
rec.gross_weight := tbl(idx).gross_weight;
rec.net_weight := tbl(idx).net_weight;
rec.vendor := tbl(idx).vendor;
rec.shelf_life := tbl(idx).shelf_life;
rec.shelf_life_unit := tbl(idx).shelf_life_unit;
rec.factory := tbl(idx).factory;
rec.enabled_flag := tbl(idx).enabled_flag;
rec.cud_flag := tbl(idx).cud_flag;
rec.creator_id := tbl(idx).creator_id;
rec.created_at := tbl(idx).created_at;
rec.updater_id := tbl(idx).updater_id;
rec.updated_at := tbl(idx).updated_at;
rec.record_last_update_date := tbl(idx).record_last_update_date;
rec.trans_group := NULL;
rec.trans_flag := 'N';
rec.trans_date := NULL;
rec.trans_msg := NULL;
rec.creation_date := SYSDATE;
rec.created_by := g_user_id;
rec.last_update_date := SYSDATE;
rec.last_updated_by := g_user_id;
rec.last_update_login := g_login_id;
INSERT INTO cux_intf_items VALUES rec;
END;
END LOOP;
COMMIT;
4.5.4 有更新的物料数据
-- 加载至缓存
OPEN cur_data_change;
FETCH cur_data_change BULK COLLECT
INTO tbl_c;
CLOSE cur_data_change;
FORALL i IN 1 .. tbl_c.count
UPDATE cux_intf_items his
SET his.item_status = tbl_c(i).item_status,
his.record_last_update_date = tbl_c(i).record_last_update_date,
his.trans_flag = 'N',
his.trans_msg = NULL,
his.batch_id = g_batch_id,
his.request_id = g_request_id,
his.last_update_date = SYSDATE,
his.last_updated_by = g_user_id,
his.sys_code = tbl_c(i).sys_code,
his.item_code = tbl_c(i).item_code,
his.item_name = tbl_c(i).item_desc,
his.item_spec = tbl_c(i).item_spec,
his.is_qc = tbl_c(i).is_qc,
his.stock_max_num = tbl_c(i).stock_max_num,
his.stock_min_num = tbl_c(i).stock_min_num,
his.unit = tbl_c(i).unit,
his.gross_weight = tbl_c(i).gross_weight,
his.net_weight = tbl_c(i).net_weight,
his.vendor = tbl_c(i).vendor,
his.shelf_life = tbl_c(i).shelf_life,
his.shelf_life_unit = tbl_c(i).shelf_life_unit,
his.enabled_flag = tbl_c(i).enabled_flag,
his.cud_flag = tbl_c(i).cud_flag,
his.creator_id = tbl_c(i).creator_id,
his.created_at = tbl_c(i).created_at,
his.updater_id = tbl_c(i).updater_id,
his.updated_at = tbl_c(i).updated_at
WHERE his.organization_id = tbl_c(i).org_id
AND his.item_id = tbl_c(i).item_id
AND his.sys_code = tbl_c(i).sys_code;
COMMIT;
4.5.5 同步失败的数据
BEGIN
UPDATE cux_intf_items his
SET his.trans_flag = 'N',
his.trans_msg = NULL,
his.batch_id = g_batch_id,
his.request_id = g_request_id,
his.last_update_date = SYSDATE,
his.last_updated_by = g_user_id
WHERE his.trans_flag IN ('N', 'E')
AND his.item_code = nvl(p_item_code, his.item_code)
AND his.sys_code = g_system_code;
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
END collect_data;
4.6 proc_ws_json
4.7 main
CREATE OR REPLACE PACKAGE cux_intf_wms_items_pkg IS
/******************************************************************
REM Package Name: cux_intf_wms_items_pkg |
REM Current Version : V1.0 |
REM +===============================================================+
REM | Copyright (C) 2025 isuzu Corporation |
REM | All Rights Reserved. |
REM +===============================================================+
REM Author: Nownew |
REM Created: 2025-09-22 |
REM Last_Updated: 2025-09-22 |
REM |
REM Purpose |
REM EBS2WMS.物料接口（从EBS同步至WMS） |
REM +===============================================================+
********************************************************************/
--物料类别
FUNCTION get_item_category(p_org_id IN NUMBER,
p_item_id IN NUMBER) RETURN VARCHAR2;
--物料类型
FUNCTION get_item_type(p_type_code IN VARCHAR2) RETURN VARCHAR2;
-- Author : Nownew
-- Created : 2025-09-22 15:36:30
-- Purpose : EBS2WMS.物料接口（从EBS同步至WMS）
/* =======================================================================
* Program Name:
* collect_data()
* Description :
* 收集物料状态变化 写入数据表cux_intf_items内
*
* Reference :
*
* Parameter :
* @o_return_code out varchar2 返回状态
* @o_return_msg out varchar2 返回消息
* @p_item_code in varchar2 物料代码
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE collect_data(o_return_code OUT VARCHAR2,
o_return_msg OUT VARCHAR2,
p_item_code IN VARCHAR2);
/* =======================================================================
* Program Name:
* main()
* Description :
* EBS2WMS.物料状态同步接口
*
* Reference :
*
* Parameter :
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_item_code IN VARCHAR2 DEFAULT NULL);
END cux_intf_wms_items_pkg;
CREATE OR REPLACE PACKAGE BODY cux_intf_wms_items_pkg IS
-- Private constant declarations
g_pkg_name CONSTANT VARCHAR2(240) := 'cux_intf_wms_items_pkg';
g_output_flag CONSTANT VARCHAR2(10) := 'Y';
g_iface_code VARCHAR2(240) := 'WMS_ITEMS';
g_system_code varchar2(30) := 'WMS';
g_database CONSTANT VARCHAR2(240) := fnd_profile.value('APPS_DATABASE_ID');
g_trans_count CONSTANT NUMBER := 1; ---一次传输的数据量
g_data_fmt CONSTANT varchar2(30) := 'yyyy-MM-dd hh24:mi:ss';
-- Private variable declarations
g_user_id NUMBER := fnd_global.user_id;
g_login_id NUMBER := fnd_global.login_id;
g_request_id NUMBER := fnd_global.conc_request_id;
g_batch_id NUMBER;
g_raise_exception EXCEPTION;
g_req_message VARCHAR2(2000);
-- 请求报文
soap_request CLOB;
-- 响应结果
soap_respond CLOB;
-- Function and procedure implementations
/* =======================================================================
* Program Name:
* log()
* Description :
* 输出
*
* Reference :
*
* Parameter :
* @p_str in number 待输出的内容
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE output(p_str IN VARCHAR2) IS
BEGIN
IF g_output_flag = 'Y' THEN
fnd_file.put_line(fnd_file.output,
to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ':' ||
p_str);
dbms_output.put_line(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ':' ||
p_str);
END IF;
END output;
PROCEDURE log(p_str IN VARCHAR2) IS
BEGIN
IF g_output_flag = 'Y' THEN
fnd_file.put_line(fnd_file.log,
to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ':' ||
p_str);
dbms_output.put_line(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || ':' ||
p_str);
END IF;
END log;
-- 布尔型
FUNCTION js_bool(p_json IN OUT NOCOPY pljson, p_tag IN VARCHAR2)
RETURN BOOLEAN IS
x_return BOOLEAN;
BEGIN
x_return := pljson_ext.get_bool(p_json, p_tag);
RETURN x_return;
END;
-- 兼容字符和数字
FUNCTION js_string(p_json IN OUT NOCOPY pljson, p_tag IN VARCHAR2)
RETURN VARCHAR2 IS
x_return VARCHAR2(30000);
BEGIN
x_return := TRIM(pljson_ext.get_string(p_json, p_tag));
IF x_return IS NULL THEN
x_return := to_char(pljson_ext.get_number(p_json, p_tag));
END IF;
RETURN x_return;
END;
FUNCTION js_number(p_json IN OUT NOCOPY pljson, p_tag IN VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN pljson_ext.get_number(p_json, p_tag);
END;
/**
释放clob资源
*/
PROCEDURE free_clob IS
BEGIN
IF soap_request IS NOT NULL AND dbms_lob.isopen(soap_request) > 0 THEN
BEGIN
dbms_lob.freetemporary(soap_request);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
IF soap_respond IS NOT NULL AND dbms_lob.isopen(soap_respond) > 0 THEN
BEGIN
dbms_lob.freetemporary(soap_respond);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
END;
/**
生成batch_id，标识每次请求运行的一组数据
*/
FUNCTION get_batch_id RETURN NUMBER IS
x_batch_id NUMBER;
BEGIN
x_batch_id := cux_intf_item_his_s.nextval;
RETURN x_batch_id;
END;
/* =======================================================================
* Program Name:
* gen_ws_clob()
* Description :
* 组合数据信息报文
*
* Reference :
*
* Parameter :
* @p_trans_group in
* @o_ws_clob out clob 反馈报文
* @o_return_code out varchar2 返回状态
* @o_return_msg out varchar2 返回消息
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
/*PROCEDURE gen_ws_clob(p_trans_group IN NUMBER,
o_ws_clob OUT CLOB,
o_return_code OUT VARCHAR2,
o_return_msg OUT VARCHAR2) IS
l_api_name VARCHAR2(30) := 'gen_ws_clob';
js_line json;
js_line_list json_list;
js_body json;
v_ws_clob CLOB;
CURSOR cur_data IS
SELECT rownum row_id, his.*
FROM cux_intf_item_his his
WHERE his.batch_id = g_batch_id
AND his.trans_group = p_trans_group
ORDER BY his.item_code;
TYPE type_data_tbl IS TABLE OF cur_data%ROWTYPE INDEX BY BINARY_INTEGER;
tbl type_data_tbl;
BEGIN
o_return_code := fnd_api.g_ret_sts_success;
o_return_msg := NULL;
OPEN cur_data;
FETCH cur_data BULK COLLECT
INTO tbl;
CLOSE cur_data;
IF tbl.count <= 0 THEN
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := '没有可传输的数据.';
RETURN;
END IF;
js_line_list := json_list();
FOR idx IN 1 .. tbl.count LOOP
BEGIN
js_line := json();
js_line.put('MaterialCode', tbl(idx).item_code); -- 物料编码
js_line.put('MaterialName', tbl(idx).item_name); -- 物料名称
js_line.put('MaterialDesc', tbl(idx).item_name); -- 物料描述
js_line.put('Spec', tbl(idx).item_spec); -- 物料规格
js_line.put('Unit', tbl(idx).unit); -- 物料单位
js_line.put('IsQC', tbl(idx).is_qc); -- 是否免检
js_line.put('StockMaxNum', tbl(idx).stock_max_num); -- 最大库存
js_line.put('StockMinNum', tbl(idx).stock_min_num); -- 最小库存
js_line.put('QCByDate', tbl(idx).shelf_life); -- 保质期天数
js_line.put('Factory', tbl(idx).factory); -- 工厂
js_line.put('IsDisable', tbl(idx).Enabled_flag); -- 是否禁用
js_line.put('HandleFlag', tbl(idx).cud_flag); -- 处理标识
js_line.put('ReservedFields1', tbl(idx).attribute1); -- 预留字段
js_line.put('ReservedFields2', tbl(idx).attribute2); -- 预留字段
js_line.put('ReservedFields3', tbl(idx).attribute3); -- 预留字段
js_line.put('ReservedFields4', tbl(idx).attribute4); -- 预留字段
js_line.put('ReservedFields5', tbl(idx).attribute5); -- 预留字段
js_line_list.append(js_line);
END;
END LOOP;
js_body := json();
js_body.put('intefaceSystem', 'ERP');
js_body.put('batchNo', 'ERP');
js_body.put('data', js_line_list);
BEGIN
dbms_lob.createtemporary(v_ws_clob, TRUE);
js_body.to_clob(v_ws_clob);
o_ws_clob := v_ws_clob;
dbms_lob.freetemporary(v_ws_clob);
EXCEPTION
WHEN OTHERS THEN
log('error:' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
END gen_ws_clob;*/
PROCEDURE gen_ws_clob(p_trans_group IN NUMBER,
o_ws_clob OUT CLOB,
o_return_code OUT VARCHAR2,
o_return_msg OUT VARCHAR2) IS
l_api_name VARCHAR2(30) := 'gen_ws_clob';
js_line pljson;
v_ws_clob CLOB;
CURSOR cur_data IS
SELECT rownum row_id, his.*
FROM cux_intf_item_his his
WHERE his.batch_id = g_batch_id
AND his.trans_group = p_trans_group
ORDER BY his.item_code;
TYPE type_data_tbl IS TABLE OF cur_data%ROWTYPE INDEX BY BINARY_INTEGER;
tbl type_data_tbl;
BEGIN
o_return_code := fnd_api.g_ret_sts_success;
o_return_msg := NULL;
OPEN cur_data;
FETCH cur_data BULK COLLECT
INTO tbl;
CLOSE cur_data;
IF tbl.count <= 0 THEN
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := '没有可传输的数据.';
RETURN;
END IF;
FOR idx IN 1 .. tbl.count LOOP
BEGIN
js_line := pljson();
js_line.put('MaterialCode', tbl(idx).item_code); -- 物料编码
js_line.put('MaterialFullCode', tbl(idx).item_full_code); -- 物料完整编码
js_line.put('MaterialName', tbl(idx).item_name); -- 物料名称
js_line.put('MaterialDesc', tbl(idx).item_name); -- 物料描述
js_line.put('MaterialCatg', tbl(idx).item_category); -- 物料类别 --add by xs 20251017
js_line.put('Spec', tbl(idx).item_spec); -- 物料规格
js_line.put('Unit', tbl(idx).unit); -- 物料单位
js_line.put('IsQC', tbl(idx).is_qc); -- 是否免检(1：免检品 0：非免检品)
js_line.put('StockMaxNum', tbl(idx).stock_max_num); -- 最大库存
js_line.put('StockMinNum', tbl(idx).stock_min_num); -- 最小库存
js_line.put('QCByDate', tbl(idx).shelf_life); -- 保质期天数
js_line.put('Factory', tbl(idx).factory); -- 工厂
-- 是否禁用(1：正常 2：禁用)
if tbl(idx).Enabled_flag = 'Y' and tbl(idx).Item_Status = 'Active' then
js_line.put('IsDisable', 1);
else
js_line.put('IsDisable', 2);
end if;
-- 处理标识(1：新增 2：修改)
if tbl(idx).Cud_flag = 'C' then
js_line.put('HandleFlag', 1);
else
js_line.put('HandleFlag', 2);
end if;
js_line.put('ReservedFields1', tbl(idx).attribute1); -- 预留字段
js_line.put('ReservedFields2', tbl(idx).attribute2); -- 预留字段
js_line.put('ReservedFields3', tbl(idx).attribute3); -- 预留字段
js_line.put('ReservedFields4', tbl(idx).attribute4); -- 预留字段
js_line.put('ReservedFields5', tbl(idx).attribute5); -- 预留字段
END;
END LOOP;
BEGIN
dbms_lob.createtemporary(v_ws_clob, TRUE);
js_line.to_clob(v_ws_clob);
o_ws_clob := v_ws_clob;
dbms_lob.freetemporary(v_ws_clob);
EXCEPTION
WHEN OTHERS THEN
log('error:' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
END gen_ws_clob;
/**
更新接口处理标记, 刷新同步标识
*/
PROCEDURE update_sync_flag(p_batch_id IN NUMBER,
p_trans_group IN NUMBER,
p_process_code IN VARCHAR2,
p_process_msg IN VARCHAR2) IS
BEGIN
UPDATE cux_intf_item_his t
SET t.trans_flag = p_process_code,
t.trans_date = SYSDATE,
t.trans_msg = p_process_msg
WHERE t.batch_id = p_batch_id
AND t.trans_group = p_trans_group;
COMMIT;
END;
FUNCTION get_item_type(p_type_code IN VARCHAR2) RETURN VARCHAR2 IS
l_item_type VARCHAR2(100);
BEGIN
SELECT flv.meaning
INTO l_item_type
FROM fnd_lookup_values_vl flv
WHERE lookup_type = 'ITEM_TYPE'
AND lookup_code = p_type_code
AND flv.enabled_flag = 'Y';
RETURN l_item_type;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
FUNCTION get_item_category(p_org_id IN NUMBER,
p_item_id IN NUMBER) RETURN VARCHAR2 IS
l_item_category VARCHAR2(240);
l_segment1 VARCHAR2(100);
l_segment2 VARCHAR2(100);
l_segment3 VARCHAR2(100);
l_first_desc VARCHAR2(100);
l_second_desc VARCHAR2(100);
l_third_desc VARCHAR2(100);
BEGIN
BEGIN
SELECT mic.segment1,
mic.segment2,
mic.segment3
INTO l_segment1,
l_segment2,
l_segment3
FROM mtl_item_categories_v mic
WHERE mic.organization_id = p_org_id
AND mic.category_set_id = 1 --库存类别
AND mic.inventory_item_id = p_item_id;
EXCEPTION
WHEN OTHERS THEN
l_segment1 := NULL;
l_segment2 := NULL;
l_segment3 := NULL;
END;
--物料大类
BEGIN
SELECT ffvv.description
INTO l_first_desc
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE 1 = 1
AND ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'YN_ITEM_CATEGORY_01'
AND ffvv.flex_value = l_segment1;
EXCEPTION
WHEN OTHERS THEN
l_first_desc := '00';
END;
--物料中类
BEGIN
SELECT ffvv.description
INTO l_second_desc
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE 1 = 1
AND ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'YN_ITEM_CATEGORY_02'
AND ffvv.flex_value = l_segment2;
EXCEPTION
WHEN OTHERS THEN
l_second_desc := '0';
END;
--物料小类
BEGIN
SELECT ffvv.description
INTO l_third_desc
FROM fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv
WHERE 1 = 1
AND ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'YN_ITEM_CATEGORY_03'
AND ffvv.flex_value = l_segment3;
EXCEPTION
WHEN OTHERS THEN
l_third_desc := '00';
END;
l_item_category := l_first_desc || '.' || l_second_desc || '.' || l_third_desc;
RETURN l_item_category;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/* =======================================================================
* Program Name:
* group_data()
* Description :
* 将数据表cux_bom_e2b_item_status_his内的数据分组，便于分批传送BOM
*
* Reference :
*
* Parameter :
* @p_batch_id in number batch_id数据批
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE group_data(p_batch_id IN NUMBER) IS
CURSOR cur_data IS
SELECT t.trans_group,
ceil(row_number() over(ORDER BY t.organization_id, t.item_id) /
g_trans_count) AS rn,
ROWID row_id
FROM cux_intf_item_his t
WHERE t.batch_id = p_batch_id
AND t.trans_flag = 'N'
ORDER BY t.organization_id, t.item_id;
TYPE rec_data_tbl IS TABLE OF cur_data%ROWTYPE INDEX BY BINARY_INTEGER;
tbl rec_data_tbl;
BEGIN
-- 初始化数组
tbl.delete();
-- 数据批量加载至内存数组
OPEN cur_data;
FETCH cur_data BULK COLLECT
INTO tbl;
CLOSE cur_data;
-- 批量更新分组数
FORALL idx IN 1 .. tbl.count
UPDATE cux_intf_item_his t
SET t.trans_group = tbl(idx).rn
WHERE t.rowid = tbl(idx).row_id;
COMMIT;
END;
/* =======================================================================
* Program Name:
* collect_data()
* Description :
* 收集物料状态变化 写入数据表cux_intf_item_his内
*
* Reference :
*
* Parameter :
* @o_return_code out varchar2 返回状态
* @o_return_msg out varchar2 返回消息
* @p_item_code in varchar2 物料代码
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE collect_data(o_return_code OUT VARCHAR2,
o_return_msg OUT VARCHAR2,
p_item_code IN VARCHAR2) IS
l_api_name VARCHAR2(30) := 'collect_data';
rec cux_intf_item_his%ROWTYPE;
l_item_category VARCHAR2(240);
-- ERP物料数据
CURSOR cur_data IS
SELECT msi.organization_id org_id,
msi.inventory_item_id item_id,
msi.segment1 item_code,
ood.ORGANIZATION_CODE || '-' || msi.SEGMENT1 item_full_code,
msi.description item_desc,
msi.attribute1 item_spec,
get_item_category(msi.organization_id, msi.inventory_item_id) item_category, --add by xs 20251017
msi.PRIMARY_UOM_CODE unit,
decode(msi.RECEIVING_ROUTING_ID, 2, 0, 1) is_qc,
nvl(msi.MAX_MINMAX_QUANTITY, 0) stock_max_num,
nvl(msi.MIN_MINMAX_QUANTITY, 0) stock_min_num,
msi.UNIT_WEIGHT gross_weight,
msi.UNIT_WEIGHT net_weight,
null vendor,
msi.SHELF_LIFE_DAYS shelf_life,
msi.SHELF_LIFE_CODE shelf_life_unit,
ood.ORGANIZATION_CODE factory,
msi.INVENTORY_ITEM_STATUS_CODE item_status,
msi.ENABLED_FLAG enabled_flag,
'C' cud_flag,
fuc.USER_NAME creator_id,
msi.CREATION_DATE created_at,
fud.USER_NAME updater_id,
msi.LAST_UPDATE_DATE updated_at,
msi.last_update_date record_last_update_date
FROM mtl_system_items_b msi,
org_organization_definitions ood,
fnd_user fuc,
fnd_user fud
WHERE msi.organization_id = ood.ORGANIZATION_ID
and ood.ORGANIZATION_CODE IN ('S01', 'S02', 'S03')
and msi.CREATED_BY = fuc.USER_ID
and msi.LAST_UPDATED_BY = fud.USER_ID
AND msi.segment1 = nvl(p_item_code, msi.segment1)
AND NOT EXISTS
(SELECT 1
FROM cux_intf_item_his his
WHERE his.organization_id = msi.organization_id
AND his.item_id = msi.inventory_item_id
and his.sys_code = g_system_code);
TYPE rec_data_tbl IS TABLE OF cur_data%ROWTYPE INDEX BY BINARY_INTEGER;
tbl rec_data_tbl;
-- 物料状态发生变化的数据
CURSOR cur_data_change IS
SELECT msi.organization_id org_id,
msi.inventory_item_id item_id,
msi.segment1 item_code,
ood.ORGANIZATION_CODE || '-' || msi.SEGMENT1 item_full_code,
msi.description item_desc,
msi.attribute1 item_spec,
get_item_category(msi.organization_id, msi.inventory_item_id) item_category, --add by xs 20251017
msi.PRIMARY_UOM_CODE unit,
decode(msi.RECEIVING_ROUTING_ID, 2, 0, 1) is_qc,
msi.MAX_MINMAX_QUANTITY stock_max_num,
msi.MIN_MINMAX_QUANTITY stock_min_num,
msi.UNIT_WEIGHT gross_weight,
msi.UNIT_WEIGHT net_weight,
null vendor,
msi.SHELF_LIFE_DAYS shelf_life,
msi.SHELF_LIFE_CODE shelf_life_unit,
ood.ORGANIZATION_CODE factory,
msi.INVENTORY_ITEM_STATUS_CODE item_status,
msi.ENABLED_FLAG enabled_flag,
'U' cud_flag,
fuc.USER_NAME creator_id,
msi.CREATION_DATE created_at,
fud.USER_NAME updater_id,
msi.LAST_UPDATE_DATE updated_at,
msi.last_update_date record_last_update_date,
his.sys_code
FROM mtl_system_items_b msi,
org_organization_definitions ood,
fnd_user fuc,
fnd_user fud,
cux_intf_item_his his
WHERE msi.organization_id = his.organization_id
AND msi.inventory_item_id = his.item_id
and his.sys_code = g_system_code /*来源系统*/
and msi.organization_id = ood.ORGANIZATION_ID
and ood.ORGANIZATION_CODE = his.factory
and msi.CREATED_BY = fuc.USER_ID
and msi.LAST_UPDATED_BY = fud.USER_ID
AND msi.segment1 = nvl(p_item_code, msi.segment1)
AND msi.LAST_UPDATE_DATE > his.record_last_update_date;
TYPE rec_change_tbl IS TABLE OF cur_data_change%ROWTYPE INDEX BY BINARY_INTEGER;
tbl_c rec_change_tbl;
BEGIN
o_return_code := fnd_api.g_ret_sts_success;
o_return_msg := NULL;
-- 加载至缓存
OPEN cur_data;
FETCH cur_data BULK COLLECT
INTO tbl;
CLOSE cur_data;
-- 10.未写入接口表的新物料
FOR idx IN 1 .. tbl.count LOOP
BEGIN
rec.id := cux_intf_item_his_s.nextval;
rec.sys_code := g_system_code;
rec.batch_id := g_batch_id;
rec.request_id := g_request_id;
rec.organization_id := tbl(idx).org_id;
rec.item_id := tbl(idx).item_id;
rec.item_code := tbl(idx).item_code;
rec.item_full_code := tbl(idx).item_full_code;
rec.item_name := tbl(idx).item_desc;
rec.item_category := tbl(idx).item_category; --add by xs 20251017
rec.item_spec := tbl(idx).item_spec;
rec.is_qc := tbl(idx).is_qc;
rec.item_status := tbl(idx).item_status;
rec.stock_max_num := tbl(idx).stock_max_num;
rec.stock_min_num := tbl(idx).stock_min_num;
rec.unit := tbl(idx).unit;
rec.gross_weight := tbl(idx).gross_weight;
rec.net_weight := tbl(idx).net_weight;
rec.vendor := tbl(idx).vendor;
rec.shelf_life := tbl(idx).shelf_life;
rec.shelf_life_unit := tbl(idx).shelf_life_unit;
rec.factory := tbl(idx).factory;
rec.enabled_flag := tbl(idx).enabled_flag;
rec.cud_flag := tbl(idx).cud_flag;
rec.creator_id := tbl(idx).creator_id;
rec.created_at := tbl(idx).created_at;
rec.updater_id := tbl(idx).updater_id;
rec.updated_at := tbl(idx).updated_at;
rec.record_last_update_date := tbl(idx).record_last_update_date;
rec.trans_group := NULL;
rec.trans_flag := 'N';
rec.trans_date := NULL;
rec.trans_msg := NULL;
rec.creation_date := SYSDATE;
rec.created_by := g_user_id;
rec.last_update_date := SYSDATE;
rec.last_updated_by := g_user_id;
rec.last_update_login := g_login_id;
INSERT INTO cux_intf_item_his VALUES rec;
END;
END LOOP;
COMMIT;
-- 20.已写入接口表，但物料状态变化的数据
-- 加载至缓存
OPEN cur_data_change;
FETCH cur_data_change BULK COLLECT
INTO tbl_c;
CLOSE cur_data_change;
FORALL i IN 1 .. tbl_c.count
UPDATE cux_intf_item_his his
SET his.item_status = tbl_c(i).item_status,
his.record_last_update_date = tbl_c(i).record_last_update_date,
his.trans_flag = 'N',
his.trans_msg = NULL,
his.batch_id = g_batch_id,
his.request_id = g_request_id,
his.last_update_date = SYSDATE,
his.last_updated_by = g_user_id,
his.sys_code = tbl_c(i).sys_code,
his.item_code = tbl_c(i).item_code,
his.item_full_code = tbl_c(i).item_full_code,
his.item_name = tbl_c(i).item_desc,
his.item_spec = tbl_c(i).item_spec,
his.item_category = tbl_c(i).item_category, --add by xs 20251017
his.is_qc = tbl_c(i).is_qc,
his.stock_max_num = tbl_c(i).stock_max_num,
his.stock_min_num = tbl_c(i).stock_min_num,
his.unit = tbl_c(i).unit,
his.gross_weight = tbl_c(i).gross_weight,
his.net_weight = tbl_c(i).net_weight,
his.vendor = tbl_c(i).vendor,
his.shelf_life = tbl_c(i).shelf_life,
his.shelf_life_unit = tbl_c(i).shelf_life_unit,
his.enabled_flag = tbl_c(i).enabled_flag,
his.cud_flag = tbl_c(i).cud_flag,
his.creator_id = tbl_c(i).creator_id,
his.created_at = tbl_c(i).created_at,
his.updater_id = tbl_c(i).updater_id,
his.updated_at = tbl_c(i).updated_at
WHERE his.organization_id = tbl_c(i).org_id
AND his.item_id = tbl_c(i).item_id
AND his.sys_code = tbl_c(i).sys_code;
COMMIT;
-- 30.之前同步失败的数据
BEGIN
UPDATE cux_intf_item_his his
SET his.trans_flag = 'N',
his.trans_msg = NULL,
his.batch_id = g_batch_id,
his.request_id = g_request_id,
his.last_update_date = SYSDATE,
his.last_updated_by = g_user_id
WHERE his.trans_flag IN ('N', 'E')
AND his.item_code = nvl(p_item_code, his.item_code)
AND his.sys_code = g_system_code;
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
END collect_data;
/* =======================================================================
* Program Name:
* proc_ws_json()
* Description :
* 获取物料数据信息并组合报文
*
* Reference :
*
* Parameter :
* @x_return_status out varchar2 返回状态
* @x_msg_count out number
* @x_msg_data out varchar2 返回消息
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE proc_ws_json(p_wsdl_url IN VARCHAR2,
p_trans_group IN NUMBER,
p_item_code IN VARCHAR2,
o_return_code OUT NOCOPY VARCHAR2,
o_return_msg OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30) := 'proc_ws_json';
x_log_id NUMBER;
x_error_message VARCHAR2(2000);
x_return_status VARCHAR2(30);
js_ret pljson;
l_resultcode BOOLEAN;
l_ret_message VARCHAR2(2000);
p_header_param cux_rest_api.header_params_tbl;
BEGIN
x_return_status := fnd_api.g_ret_sts_success;
x_error_message := NULL;
---2..组合报文
gen_ws_clob(p_trans_group => p_trans_group,
o_ws_clob => soap_request,
o_return_code => x_return_status,
o_return_msg => x_error_message);
IF x_return_status <> 'S' THEN
o_return_code := 'E';
o_return_msg := '组合报文出现异常:' || x_error_message;
log(o_return_msg);
-- 释放clob
free_clob;
RETURN;
END IF;
---3.将请求报文无条件存储在报文记录表中
log('.....将拼接的报文写入日志表...............');
cux_intf_log_pkg.log(p_config_code => g_iface_code,
p_system_code => g_system_code,
p_status => 'S',
p_request_parameter => p_item_code,
p_request_data => soap_request,
p_response_code => NULL,
p_response_data => NULL,
p_remark => NULL,
p_reference_info => NULL,
x_log_id => x_log_id);
--4.开始报文服务
log('.....开始报文服务...............');
BEGIN
-- 调用WMS物料接口
p_header_param.delete();
p_header_param(1).param_name := 'serviceId';
p_header_param(1).param_value := 'RECEIVE_ERP_PARTSTATUS';
p_header_param(2).param_name := 'credential';
p_header_param(2).param_value := '111';
cux_rest_api.post(p_web_url => p_wsdl_url,
p_header_param => p_header_param,
p_request_clob => soap_request,
p_response_clob => soap_respond,
x_return_status => x_return_status,
x_error_message => x_error_message);
log('x_error_message:' || x_error_message);
log('soap_respond:' || soap_respond);
IF x_return_status = 'S' THEN
log('.....开始解析反馈报文..............,logId:' || x_log_id);
js_ret := pljson(soap_respond);
l_resultcode := js_bool(js_ret, 'Success');
IF l_resultcode THEN
x_return_status := 'S';
x_error_message := '推送成功';
output(to_char(SYSDATE, g_data_fmt));
ELSE
x_return_status := 'E';
l_ret_message := js_string(js_ret, 'Message');
x_error_message := l_ret_message;
output(to_char(SYSDATE, g_data_fmt) || ':' || l_ret_message);
END IF;
ELSE
output(to_char(SYSDATE, g_data_fmt) || ':' || x_error_message);
END IF;
o_return_code := x_return_status;
o_return_msg := x_error_message;
-- 更新接口日志
cux_intf_log_pkg.upd(p_log_id => x_log_id,
p_response_code => x_return_status,
p_response_data => soap_respond,
p_remark => x_error_message,
p_reference_info => p_item_code);
END;
-- 释放clob
free_clob;
EXCEPTION
WHEN OTHERS THEN
o_return_code := fnd_api.g_ret_sts_error;
o_return_msg := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
-- 更新接口日志
cux_intf_log_pkg.upd(p_log_id => x_log_id,
p_response_code => o_return_code,
p_response_data => soap_respond,
p_remark => o_return_msg,
p_reference_info => p_item_code);
-- 释放clob
free_clob;
END proc_ws_json;
/**
请求不允许多个实例同时运行
*/
PROCEDURE req_duplicate_check IS
x_count NUMBER;
BEGIN
SELECT COUNT(1)
INTO x_count
FROM fnd_concurrent_requests crs, fnd_concurrent_requests run
WHERE crs.concurrent_program_id = run.concurrent_program_id
AND crs.phase_code = 'R'
AND run.request_id = fnd_global.conc_request_id
AND crs.request_id <> fnd_global.conc_request_id;
IF x_count > 0 THEN
g_req_message := '本请求不允许同时运行两个相同实例，请重试!';
log(g_req_message);
RAISE g_raise_exception;
END IF;
END;
/* =======================================================================
* Program Name:
* main()
* Description :
* EBS2WMS.物料状态同步接口
*
* Reference :
*
* Parameter :
*
* Changed History:
* Author Version Creation_Date Description
* Nownew 1.0 2025-09-22 Initial version
*
* ========================================================================*/
PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_item_code IN VARCHAR2 DEFAULT NULL) IS
l_api_name VARCHAR2(240) := 'main';
v_return_code VARCHAR2(30);
v_return_msg VARCHAR2(32000);
v_wsdl_url VARCHAR2(260);
v_success_count NUMBER;
v_failure_count NUMBER;
CURSOR cur_group IS
SELECT his.batch_id, his.trans_group
FROM cux_intf_item_his his
WHERE his.batch_id = g_batch_id
AND his.trans_flag <> 'S'
GROUP BY his.batch_id, his.trans_group
ORDER BY his.batch_id, his.trans_group;
TYPE rec_group_tbl IS TABLE OF cur_group%ROWTYPE INDEX BY BINARY_INTEGER;
tbl_group rec_group_tbl;
BEGIN
-- 请求重复运行检查
req_duplicate_check;
/*g_trans_date := SYSDATE;*/
g_batch_id := get_batch_id;
log(' g_batch_id:' || to_char(g_batch_id));
---拼接服务地址
v_wsdl_url := cux_intf_api.url(g_iface_code, g_database);
IF v_wsdl_url IS NULL THEN
retcode := '2';
errbuf := '接口IP和端口号不存在;';
RETURN;
END IF;
---1.生成本次传输的数据
log(' collect_data开始生成本次传递数据...............');
collect_data(o_return_code => v_return_code,
o_return_msg => v_return_msg,
p_item_code => p_item_code);
IF v_return_code <> fnd_api.g_ret_sts_success THEN
retcode := '2';
errbuf := '提取传输数据出现异常:' || v_return_msg;
log(errbuf);
RETURN;
END IF;
-- 进行数据分组
log(' group_data进行数据分组...............');
group_data(p_batch_id => g_batch_id);
---2.拼接报文，调用ws
v_success_count := 0;
v_failure_count := 0;
log(' cur_group处理每批次数据...............');
OPEN cur_group;
FETCH cur_group BULK COLLECT
INTO tbl_group;
CLOSE cur_group;
FOR idx IN 1 .. tbl_group.count LOOP
log(' 开始接口推送流程......:' || tbl_group(idx).batch_id || '-' || tbl_group(idx).trans_group);
log(' json拼接报文，调用接口...............');
proc_ws_json(p_wsdl_url => v_wsdl_url,
p_trans_group => tbl_group(idx).trans_group,
p_item_code => p_item_code,
o_return_code => v_return_code,
o_return_msg => v_return_msg);
IF v_return_code != fnd_api.g_ret_sts_success THEN
v_failure_count := v_failure_count + 1;
-- 更新接口处理标记
update_sync_flag(p_batch_id => g_batch_id,
p_trans_group => tbl_group(idx).trans_group,
p_process_code => v_return_code,
p_process_msg => v_return_msg);
ELSE
v_success_count := v_success_count + 1;
-- 更新接口处理标记
log(' update_sync_flag更新接口处理标记...............');
update_sync_flag(p_batch_id => g_batch_id,
p_trans_group => tbl_group(idx).trans_group,
p_process_code => v_return_code,
p_process_msg => v_return_msg);
END IF;
END LOOP;
-- 如果存在错误数据，标记请求状态为黄色警告
SELECT COUNT(1)
INTO v_failure_count
FROM cux_intf_item_his t
WHERE t.batch_id = g_batch_id
AND t.trans_flag <> 'S';
IF v_failure_count > 0 THEN
retcode := '1';
ELSE
retcode := '0';
END IF;
EXCEPTION
WHEN g_raise_exception THEN
retcode := '2';
errbuf := g_req_message;
log(' g_raise_exception:' || g_req_message);
WHEN OTHERS THEN
retcode := '2';
errbuf := g_pkg_name || '.' || l_api_name || ':' || SQLERRM;
log(' others_exception:' || errbuf);
END main;
END cux_intf_wms_items_pkg;
扫一扫
4388
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
