料號導入接口程式說明文檔
大批量的Excel數據料號用接口導入,提高工作效率
1 臨時料號表结构
表名 | 字段描述 | 字段名 | 字段类型 | 长度 |
接口表 | 模板 | ITEM_MODEL | NVARCHAR2() |
|
CUX.CUX_ITEM_IMPORT | 料號 | ITEM_NUMBER | INT |
|
| 物料描述 | ITEM_DES | INT |
|
| 物料長描述 | ITEM_DES_LONG | VARCHAR(10) |
|
| 單位名稱 | ITEM_DW | VARCHAR(10) |
|
| 類型 | ITEM_TYPE | datetime |
|
| 分類 | ITEM_CATE | VARCHAR(15) |
|
createtablecux.cux_item_import
(
ITEM_MODEL NVARCHAR2(30) null ,
ITEM_NUMBER NVARCHAR2(100) null,
ITEM_DES NVARCHAR2(200) null,
ITEM_DES_LONG NVARCHAR2(300) null,
ITEM_DW NVARCHAR2(20) null,
ITEM_TYPE NVARCHAR2(20) null,
ITEM_CATE NVARCHAR2(100) null
)
把整理好的excel表中料號復制粘貼到此臨時料號表中
料號Excel模板如下:
2 處理接口數據过程代码
ALTERSESSIONSETNLS_LANGUAGE='AMERICAN'; --US英語
altersessionsetnls_language='SIMPLIFIED CHINESE' ; --ZHS簡體中文
altersessionsetNLS_language='TRADITIONALCHINESE' ; --ZHT繁體中文
begin
dbms_application_info.set_client_info(81);
end;
/*
用途:从临时表的料号导入正式表中
步骤:1> 先把料号分组织导入到接口表中,再执行料号汇入程式
料号接口表:inv.mtl_system_items_interface
料号接口出错表:mtl_interface_errors
2> 再把料号的默认分类从表中删除
料号分类表: mtl_item_categories mic
3> 再把料号分类数据插入接口表中
料号分类接口表:mtl_item_categories_interface
4> 用游标把接口表中的料号分类产生到正式表中,只需要对主组织料号进行分类分配的执行就可以了
一、料號接口導入
*/--删除料号接口表数据
select *
-- delete
frominv.mtl_system_items_interface mi;
--主組織料號
insertinto inv.mtl_system_items_interface
(
last_update_date,
last_updated_by,
creation_date,
created_by,
process_flag ,
transaction_type ,
set_process_id,
organization_id,
organization_code,
segment1 ,
primary_uom_code,
description,
long_description,
template_id,
item_type
)
select * --主組織料號
from
(
select
sysdateaslast_update_date ,
1653aslast_update_by,
sysdateascreation_date,
1653ascreated_by,
1asprocess_flag ,
'CREATE'transaction_type ,
0set_process_id,
82organization_id,
'00'organization_code,
item_numberassegment1 ,
uo.UOM_CODEasprimary_uom_code,
item_desdescription,
item_des_longlong_description,
te.template_id,
substr(it.item_cate,1,2) item_type
FROMCUX.CUX_ITEM_IMPORTit ,
MTL_ITEM_TEMPLATES_ALL_Vte ,
MTL_UNITS_OF_MEASURE_VL uo
whereit.item_model=te.template_name
and it.item_dw=uo.unit_of_measure_tl
-- and item_numberin('GBD010100100000','GBD010100540000')
unionall--庫存組織料號
select
sysdateaslast_update_date ,
1653aslast_update_by,
sysdateascreation_date,
1653ascreated_by,
1asprocess_flag ,
'CREATE'transaction_type ,
0set_process_id,
83organization_id,
'10'organization_code,
item_numberassegment1 ,
uo.UOM_CODEasprimary_uom_code,
item_desdescription,
item_des_longlong_description,
te.template_id,
substr(it.item_cate,1,2) item_type
FROMCUX.CUX_ITEM_IMPORTit ,
MTL_ITEM_TEMPLATES_ALL_Vte ,
MTL_UNITS_OF_MEASURE_VL uo
whereit.item_model=te.template_name
and it.item_dw=uo.unit_of_measure_tl
--and item_number in('GBD010100100000','GBD010100540000')
) v_i
wherenotexists(select * frominv.mtl_system_items_bmt
wheremt.segment1=v_i.segment1 );
--进客户端執行請求:
料號匯入
執行路徑:3G-10-INV_倉庫管理超級用戶\料號\匯入\匯入料號,出現如下圖
二 料號分類指定
--删除分类初始数据
select *
--delete
frommtl_item_categoriesmic
whereexists(select * frominv.mtl_system_items_bmb ,
CUX.CUX_ITEM_IMPORTit
wheremb.organization_id=mic.organization_id
andmb.inventory_item_id=mic.inventory_item_id
andmb.segment1=it.item_number
--and mb.segment1in('GBD010100100000','GBD010100540000')
);
--删除分类接口表
select *
-- delete
--update mtl_item_categories_interface settransaction_type='CREATE'
frommtl_item_categories_interfacemci;
--把分类集插入接口表
insertintomtl_item_categories_interface
(
last_update_date,
last_updated_by,
creation_date,
created_by,
process_flag ,
transaction_type ,
set_process_id,
organization_id,
inventory_item_id,
CATEGORY_SET_ID,
CATEGORY_ID
)
selectsysdateaslast_update_date ,
1653aslast_update_by,
sysdateascreation_date,
1653ascreated_by,
1asprocess_flag ,
'CREATE'transaction_type ,
0set_process_id,
mb.organization_id,
mb.inventory_item_id,
1CATEGORY_SET_ID,
--MC.CATEGORY_CONCAT_SEGS,
MC.CATEGORY_ID
FROMCUX.CUX_ITEM_IMPORTit ,
inv.mtl_system_items_bmb,
MTL_CATEGORIES_VMC
where mb.segment1=it.item_number
andit.item_cate=MC.CATEGORY_CONCAT_SEGS
andnotexists(select *
frommtl_item_categoriesmic
wheremic.inventory_item_id=mb.inventory_item_id)
--and mb.segment1in('GBD010100100000','GBD010100540000') ;
--执行分类程序
方法一:執行標准請求
執行路徑:3G-10-INV_倉庫管理超級用戶\料號\匯入\匯入料號分類指定,出現如下圖
方法二:執行下面代碼
/*
用途:把料号分类接口表中主组织料号的数据产生到正式分类表中
*/
DECLARE
x_return_statusVARCHAR2(1);
x_msg_countNUMBER;
x_msg_dataVARCHAR2(2000);
x_errorcodeVARCHAR2(30);
l_cur_mfg_org_idNUMBER := 82; --Current Inv Organization
l_user_idNUMBER := 1653; --User ID, Sysadminhere
cursorcur_interis
selectmti.category_set_id,
mti.category_id,
mti.inventory_item_id,
mti.organization_id
frommtl_item_categories_interfacemti
wheremti.organization_id=82; --主组织
BEGIN
--Initialize first, or create_by will be -1
fnd_global.apps_initialize(user_id => l_user_id,
resp_id => 50637,
resp_appl_id => 401);
for l_cux_interin cur_interloop
inv_item_category_pub.create_category_assignment(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_category_id => l_cux_inter.category_id,
p_category_set_id => l_cux_inter.category_set_id,
p_inventory_item_id => l_cux_inter.inventory_item_id,
p_organization_id => l_cux_inter.organization_id);
commit;
IFx_return_status <> fnd_api.g_ret_sts_successTHEN
ROLLBACK;
dbms_output.put_line('error code : ' || x_errorcode);
fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
dbms_output.put_line('error count : ' || to_char(x_msg_count));
dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
FORiIN2 .. x_msg_countLOOP
x_msg_data := fnd_msg_pub.get;
dbms_output.put_line(REPLACE(x_msg_data, chr(0), ' '));
ENDLOOP;
/*
IF(x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
RAISEfnd_api.g_exc_unexpected_error;
ELSIF(x_return_status = fnd_api.g_ret_sts_error) THEN
RAISEfnd_api.g_exc_error;
END IF;
ELSE
COMMIT;
dbms_output.put_line('Successfully.');*/
ENDIF;
endloop;
END;