从之前的系统转BOM到新系统来
首先料号全部导入，再次转BOM
1、从旧系统捞出此BOM,建DBlink
create public database link PTEST connect to username identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =PTEST)
)
)';
create synonym p_mtl_system_items_b for mtl_system_items_b@ptest
用如下代码捞取旧系统的BOM到新系统的接口表
create or replace procedure jw_bom_import_proc(Errbuf Out Varchar2, --先冠不含税报表
Errcode Out Varchar2) is
P_ASSEMBLY_ITEM VARCHAR2(20);
P_ORG VARCHAR2(3) := 83;
CURSOR C1 IS
SELECT distinct t.item_number
FROM JW_20171227_T T
/*WHERE SUBSTR(T.ITEM_NUMBER,1,1) = '4'*/;
begin
delete from bom_op_routings_interface a
-- where (a.process_flag = 3 or a.transaction_type = 'NO_OP')
;
delete from bom_op_sequences_interface b;
--where (b.process_flag = 3 or b.transaction_type = 'NO_OP');
delete from bom_bill_of_mtls_interface c;
-- where (c.process_flag = 3 or c.transaction_type = 'NO_OP');
delete from bom_inventory_comps_interface d;
-- where (d.process_flag = 3 or d.transaction_type = 'NO_OP');
delete from BOM_REF_DESGS_INTERFACE e;
-- where (e.process_flag = 3 or e.transaction_type = 'NO_OP');
delete from BOM_SUB_COMPS_INTERFACE f;
-- where (f.process_flag = 3 or f.transaction_type = 'NO_OP');
commit;
for v1 in c1 loop
P_ASSEMBLY_ITEM := v1.item_number;
----------------工艺路线 头
INSERT INTO bom_op_routings_interface
(ASSEMBLY_ITEM_NUMBER,
Organization_Code,
Process_Flag,
Transaction_Type,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
COMMIT;
--------------------工艺路线表体
INSERT INTO bom_op_sequences_interface
(OPERATION_SEQ_NUM,
OPERATION_CODE,
DEPARTMENT_CODE,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(10,
'SMT',
'SMT',
SYSDATE,
P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
INSERT INTO bom_op_sequences_interface
(OPERATION_SEQ_NUM,
OPERATION_CODE,
DEPARTMENT_CODE,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(20,
'DIP',
'DIP',
SYSDATE,
P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
INSERT INTO bom_op_sequences_interface
(OPERATION_SEQ_NUM,
OPERATION_CODE,
DEPARTMENT_CODE,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(30,
'ASS',
'ASS',
SYSDATE,
P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
INSERT INTO bom_op_sequences_interface
(OPERATION_SEQ_NUM,
OPERATION_CODE,
DEPARTMENT_CODE,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(40,
'PK',
'PK',
SYSDATE,
P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
IF SUBSTR(P_ASSEMBLY_ITEM,1,1) IN ('1','2','3','5') THEN
INSERT INTO bom_op_sequences_interface
(OPERATION_SEQ_NUM,
OPERATION_CODE,
DEPARTMENT_CODE,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
ORGANIZATION_CODE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES
(50,
'COM',
'COM',
SYSDATE,
P_ASSEMBLY_ITEM,
P_ORG,
'1',
'CREATE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'));
END IF;
COMMIT;
------------------------------表头
INSERT INTO bom_bill_of_mtls_interface
(ORGANIZATION_CODE,
ITEM_NUMBER,
TRANSACTION_TYPE,
PROCESS_FLAG,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
attribute1,
attribute2,
LAST_UPDATED_BY)
select P_ORG,
P_ASSEMBLY_ITEM,
'CREATE',
'1',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
trim(bsb.attribute1),
trim(bsb.attribute2),
FND_PROFILE.VALUE('USER_ID')
from p_bom_structures_b bsb, p_mtl_system_items_b msib----------------------------db link table
where bsb.assembly_item_id = msib.inventory_item_id
and bsb.organization_id = msib.organization_id
and msib.segment1 = P_ASSEMBLY_ITEM
and msib.organization_id = 118;
COMMIT;
------------------------表体
INSERT INTO bom_inventory_comps_interface
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_NUMBER,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
EFFECTIVITY_DATE,
ASSEMBLY_ITEM_NUMBER,
PROCESS_FLAG,
ORGANIZATION_CODE,
TRANSACTION_TYPE,
-- WIP_SUPPLY_TYPE,
Creation_Date,
CREATED_BY,
attribute10,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select bcb.operation_seq_num,
msib2.segment1,
bcb.item_num,
bcb.component_quantity,
bcb.component_yield_factor,
SYSDATE,
P_ASSEMBLY_ITEM,
'1',
P_ORG,
'CREATE',
-- '1', --虚拟键：6 推式：1
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
trim(bcb.attribute10),
SYSDATE,
FND_PROFILE.VALUE('USER_ID')
from p_bom_structures_b bsb,----------------------------db link table
p_mtl_system_items_b msib1,----------------------------db link table
p_bom_components_b bcb,----------------------------db link table
p_mtl_system_items_b msib2----------------------------db link table
where bsb.assembly_item_id = msib1.inventory_item_id
and bsb.organization_id = msib1.organization_id
and msib1.organization_id = 118
and msib1.segment1 = P_ASSEMBLY_ITEM
and bsb.bill_sequence_id = bcb.bill_sequence_id
and bcb.component_item_id = msib2.inventory_item_id
and msib2.organization_id = 118
and bcb.disable_date is null
and nvl(bcb.acd_type, 1) <> 3;
commit;
----------------------------指示符
INSERT INTO BOM_REF_DESGS_INTERFACE
(COMPONENT_REFERENCE_DESIGNATOR,
ASSEMBLY_ITEM_NUMBER,
COMPONENT_ITEM_NUMBER,
ORGANIZATION_CODE,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select brd.component_reference_designator,
msib1.segment1,
msib2.segment1,
P_ORG,
bcb.operation_seq_num,
SYSDATE,
'1',
'CREATE',
-- '1', --虚拟键：6 推式：1
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID')
from p_bom_structures_b bsb,----------------------------db link table
p_mtl_system_items_b msib1,----------------------------db link table
p_bom_components_b bcb,----------------------------db link table
p_mtl_system_items_b msib2, ----------------------------db link table
p_bom_reference_designators brd----------------------------db link table
where bsb.assembly_item_id = msib1.inventory_item_id
and bsb.organization_id = msib1.organization_id
and msib1.organization_id = 118
and msib1.segment1 = P_ASSEMBLY_ITEM
and bsb.bill_sequence_id = bcb.bill_sequence_id
and bcb.component_item_id = msib2.inventory_item_id
and msib2.organization_id = 118
and bcb.disable_date is null
and nvl(bcb.acd_type, 1) <> 3
and bcb.component_sequence_id = brd.component_sequence_id
and nvl(brd.acd_type,1) <> 3;
commit;
------------------------------替代料
INSERT INTO BOM_SUB_COMPS_INTERFACE
(SUBSTITUTE_COMP_NUMBER,
ASSEMBLY_ITEM_NUMBER,
COMPONENT_ITEM_NUMBER,
SUBSTITUTE_ITEM_QUANTITY,
ORGANIZATION_CODE,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
PROCESS_FLAG,
TRANSACTION_TYPE,
Creation_Date,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select msib3.segment1,
msib1.segment1,
msib2.segment1,
bsc.substitute_item_quantity,
P_ORG,
bcb.operation_seq_num,
SYSDATE,
'1',
'CREATE',
-- '1', --虚拟键：6 推式：1
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID')
from p_bom_structures_b bsb,----------------------------db link table
p_mtl_system_items_b msib1,----------------------------db link table
p_bom_components_b bcb,----------------------------db link table
p_mtl_system_items_b msib2,----------------------------db link table
p_BOM_SUBSTITUTE_COMPONENTS bsc,---------------------------db link table
p_mtl_system_items_b msib3---------------------------db link table
where bsb.assembly_item_id = msib1.inventory_item_id
and bsb.organization_id = msib1.organization_id
and msib1.organization_id = 118
and msib1.segment1 = P_ASSEMBLY_ITEM
and bsb.bill_sequence_id = bcb.bill_sequence_id
and bcb.component_item_id = msib2.inventory_item_id
and msib2.organization_id = 118
and bcb.disable_date is null
and nvl(bcb.acd_type, 1) <> 3
and bsc.component_sequence_id = bcb.component_sequence_id
and bsc.substitute_component_id = msib3.inventory_item_id
and msib3.organization_id = 118
and nvl(bsc.acd_type,1) <> 3;
commit;
end loop;
end;
/
2、得到旧系统的BOM资料之后用api导入即可
CREATE OR REPLACE PROCEDURE JW_PROCESS_BOM_PROC(Errbuf Out Varchar2,
Errcode Out Varchar2,
P_ASS_ITEM VARCHAR2) is
-- API input variables
l_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
l_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
l_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
l_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
l_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
-- API output variables
x_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
x_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
x_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
x_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
x_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
x_message_list Error_Handler.Error_Tbl_Type;
l_error_table Error_Handler.Error_Tbl_Type;
-- l_output_dir VARCHAR2(500) := '/usr/tmp/visus25';
-- l_debug_filename VARCHAR2(60) := 'su_debug_07_16.dbg';
l_return_status VARCHAR2(1) := NULL;
l_msg_count NUMBER := 0;
l_cnt NUMBER;
v_start_effective_date date := sysdate;
N_item_sequence_number NUMBER;
i number; --替代料
j number; --指示符
V_CHAR_REF varchar2(15);
N_NUMBER number DEFAULT 0;
N_NUMBER1 number DEFAULT 0;
N_NUMBER2 number DEFAULT 0;
N_NUMBER3 number DEFAULT 1;
v_error_status varchar2(1000);
v_msg_data varchar2(1000);
-- WHO columns
/*l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;*/
cursor c_bom_com is
SELECT BBOI.ITEM_NUMBER assembly_item,'ZW' ORG_CODE
FROM bom_bill_of_mtls_interface BBOI
WHERE BBOI.ITEM_NUMBER = P_ASS_ITEM
;
CURSOR C_COM(P_ITEM_NUMBER VARCHAR2) IS
SELECT BIC.OPERATION_SEQ_NUM oper,
BIC.ITEM_NUM,
bic.attribute10,
BIC.COMPONENT_QUANTITY component_quantity,
BIC.COMPONENT_YIELD_FACTOR yield,
BIC.COMPONENT_ITEM_NUMBER component_item
FROM bom_inventory_comps_interface BIC
WHERE BIC.ASSEMBLY_ITEM_NUMBER = P_ITEM_NUMBER;
CURSOR C_SUB(P_ITEM_NUMBER VARCHAR2,P_COMPONENT_NUMBER VARCHAR2) IS
SELECT BSC.SUBSTITUTE_COMP_NUMBER,BSC.SUBSTITUTE_ITEM_QUANTITY
FROM BOM_SUB_COMPS_INTERFACE BSC
WHERE BSC.ASSEMBLY_ITEM_NUMBER = P_ITEM_NUMBER
AND BSC.COMPONENT_ITEM_NUMBER = P_COMPONENT_NUMBER;
CURSOR C_REF(P_ITEM_NUMBER VARCHAR2,P_COMPONENT_NUMBER VARCHAR2) IS
SELECT BRD.COMPONENT_REFERENCE_DESIGNATOR
FROM BOM_REF_DESGS_INTERFACE BRD
WHERE BRD.ASSEMBLY_ITEM_NUMBER = P_ITEM_NUMBER
AND BRD.COMPONENT_ITEM_NUMBER = P_COMPONENT_NUMBER;
BEGIN
N_item_sequence_number := 0;
l_cnt := 0;
i := 0;
j := 0;
-- Get the user_id
-- intiialize applications information
FND_GLOBAL.APPS_INITIALIZE(1111, 50645, 702); -- Mfg / Mfg & Dist Mgr / INV
-- dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
for v_com in c_bom_com loop
JW_PROCESS_RTG_PROC(83,v_com.assembly_item,SUBSTR(v_com.assembly_item,1,1),v_error_status,v_msg_data);
N_item_sequence_number := N_item_sequence_number + 10;
-- initialize BOM header
l_bom_header_rec.assembly_item_name := v_com.assembly_item;
l_bom_header_rec.organization_code := v_com.ORG_CODE;
l_bom_header_rec.assembly_type := 1;
l_bom_header_rec.transaction_type := 'CREATE';
l_bom_header_rec.return_status := NULL;
-- initialize BOM components
-- component 1
FOR V_COMPONENT IN C_COM(v_com.assembly_item) LOOP
l_cnt := l_cnt + 1;
l_bom_component_tbl(l_cnt).organization_code := v_com.ORG_CODE;
l_bom_component_tbl(l_cnt).assembly_item_name := v_com.assembly_item;
l_bom_component_tbl(l_cnt).start_effective_date := v_start_effective_date; -- to_date('16-JUL-2010 19:30:39','DD-MON-YY HH24:MI:SS'); -- should match timestamp for UPDATE
l_bom_component_tbl(l_cnt).component_item_name := V_COMPONENT.component_item;
l_bom_component_tbl(l_cnt).alternate_bom_code := NULL;
-- l_bom_component_tbl (l_cnt).supply_subinventory := 'RIP';
l_bom_component_tbl(l_cnt).location_name := NULL; -- '6.6.6..'; -- provide concatenated segments for locator
-- l_bom_component_tbl(l_cnt).comments := v_com.bom_remark;
l_bom_component_tbl(l_cnt).item_sequence_number := V_COMPONENT.ITEM_NUM;
l_bom_component_tbl(l_cnt).operation_sequence_number := V_COMPONENT.oper;
l_bom_component_tbl(l_cnt).transaction_type := 'CREATE';
l_bom_component_tbl(l_cnt).attribute10 := V_COMPONENT.attribute10;
l_bom_component_tbl(l_cnt).Projected_Yield := V_COMPONENT.yield; --产出率
l_bom_component_tbl(l_cnt).quantity_per_assembly := V_COMPONENT.component_quantity;
l_bom_component_tbl(l_cnt).return_status := NULL;
for v_sub in c_sub(v_com.assembly_item,V_COMPONENT.component_item) loop
i := i + 1;
l_bom_sub_component_tbl(i).organization_code := v_com.ORG_CODE;
l_bom_sub_component_tbl(i).Assembly_Item_Name := v_com.assembly_item;
l_bom_sub_component_tbl(i).Start_Effective_Date := v_start_effective_date;
l_bom_sub_component_tbl(i).Operation_Sequence_Number := l_bom_component_tbl(l_cnt)
.Operation_Sequence_Number;
l_bom_sub_component_tbl(i).Component_Item_Name := V_COMPONENT.component_item;
l_bom_sub_component_tbl(i).Substitute_Component_Name := v_sub.SUBSTITUTE_COMP_NUMBER;
l_bom_sub_component_tbl(i).Substitute_Item_Quantity := v_sub.SUBSTITUTE_ITEM_QUANTITY;
l_bom_sub_component_tbl(i).transaction_type := 'CREATE';
l_bom_sub_component_tbl(i).return_status := NULL;
END LOOP;
for v_REF in c_REF(v_com.assembly_item,V_COMPONENT.component_item) loop
j := j + 1;
l_bom_ref_designator_tbl(j).Organization_Code := v_com.ORG_CODE;
l_bom_ref_designator_tbl(j).Assembly_Item_Name := v_com.assembly_item;
l_bom_ref_designator_tbl(j).Start_Effective_Date := v_start_effective_date;
l_bom_ref_designator_tbl(j).Operation_Sequence_Number := l_bom_component_tbl(l_cnt)
.Operation_Sequence_Number;
l_bom_ref_designator_tbl(j).Component_Item_Name := V_COMPONENT.component_item;
l_bom_ref_designator_tbl(j).Reference_Designator_Name := v_REF.COMPONENT_REFERENCE_DESIGNATOR;
l_bom_ref_designator_tbl(j).transaction_type := 'CREATE';
l_bom_ref_designator_tbl(j).return_status := NULL;
END LOOP;
END LOOP;
/*if v_com.replace_group is not null then
for v_sub in c_sub(v_com.replace_group) loop
---替代料
i := i + 1;
l_bom_sub_component_tbl(i).organization_code := v_com.ORG_CODE;
l_bom_sub_component_tbl(i).Assembly_Item_Name := v_sub.assembly_item;
l_bom_sub_component_tbl(i).Start_Effective_Date := v_start_effective_date;
l_bom_sub_component_tbl(i).Operation_Sequence_Number := l_bom_component_tbl(l_cnt)
.Operation_Sequence_Number;
l_bom_sub_component_tbl(i).Component_Item_Name := v_com.component_item;
l_bom_sub_component_tbl(i).Substitute_Component_Name := v_sub.component_item;
l_bom_sub_component_tbl(i).Substitute_Item_Quantity := v_sub.component_quantity;
l_bom_sub_component_tbl(i).transaction_type := 'CREATE';
l_bom_sub_component_tbl(i).return_status := NULL;
end loop;
end if;
N_NUMBER := 0;
N_NUMBER1 := 0;
N_NUMBER2 := 0;
N_NUMBER3 := 1;
if v_com.ref_no is not null then
---指示符
SELECT (length(v_com.ref_no || ',') -
length(replace(v_com.ref_no || ',', ','))) / length(',')
INTO N_NUMBER
FROM DUAL;
FOR N_NUMBER1 IN 1 .. N_NUMBER LOOP
SELECT INSTR(v_com.ref_no || ',', ',', 1, N_NUMBER1)
INTO N_NUMBER2
from DUAL;
SELECT SUBSTR(v_com.ref_no || ',',
N_NUMBER3,
N_NUMBER2 - N_NUMBER3)
INTO V_CHAR_REF
FROM DUAL;
N_NUMBER3 := N_NUMBER2 + 1;
if V_CHAR_REF is not null then
j := j + 1;
l_bom_ref_designator_tbl(j).Organization_Code := v_com.ORG_CODE;
l_bom_ref_designator_tbl(j).Assembly_Item_Name := v_com.assembly_item;
l_bom_ref_designator_tbl(j).Start_Effective_Date := v_start_effective_date;
l_bom_ref_designator_tbl(j).Operation_Sequence_Number := l_bom_component_tbl(l_cnt)
.Operation_Sequence_Number;
l_bom_ref_designator_tbl(j).Component_Item_Name := v_com.component_item;
l_bom_ref_designator_tbl(j).Reference_Designator_Name := V_CHAR_REF;
l_bom_ref_designator_tbl(j).transaction_type := 'CREATE';
l_bom_ref_designator_tbl(j).return_status := NULL;
end if;
END LOOP;
end if;*/
end loop;
-- initialize error stack for logging errors
Error_Handler.initialize;
-- call API to create / update bill
DBMS_OUTPUT.PUT_LINE('=======================================================');
DBMS_OUTPUT.PUT_LINE('Calling Bom_Bo_Pub.process_bom API');
Bom_Bo_Pub.process_bom(p_bo_identifier => 'BOM',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_bom_header_rec => l_bom_header_rec,
p_bom_revision_tbl => l_bom_revision_tbl,
p_bom_component_tbl => l_bom_component_tbl,
p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
p_bom_sub_component_tbl => l_bom_sub_component_tbl,
x_bom_header_rec => x_bom_header_rec,
x_bom_revision_tbl => x_bom_revision_tbl,
x_bom_component_tbl => x_bom_component_tbl,
x_bom_ref_designator_tbl => x_bom_ref_designator_tbl,
x_bom_sub_component_tbl => x_bom_sub_component_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count -- ,
-- p_debug => 'Y',
-- p_output_dir => l_output_dir,
-- p_debug_filename => l_debug_filename
);
-- DBMS_OUTPUT.PUT_LINE('=======================================================');
-- DBMS_OUTPUT.PUT_LINE('Return Status: ' || l_return_status);
COMMIT;
/*IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
ROLLBACK;
Errcode := 'E';
dbms_output.put_line('x_msg_count:' || l_msg_count);
Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
DBMS_OUTPUT.PUT_LINE('Error Message Count :' || l_error_table.COUNT);
\* FOR i IN 1 .. l_error_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(to_char(i) || ':' || l_error_table(i)
.entity_index || ':' || l_error_table(i)
.table_name);
DBMS_OUTPUT.PUT_LINE(to_char(i) || ':' || l_error_table(i)
.message_text);
END LOOP;*\
ELSE
COMMIT;
Errcode := 'S';
DBMS_OUTPUT.PUT_LINE('成功');
END IF;
DBMS_OUTPUT.PUT_LINE('=======================================================');*/
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('=======================================================');
RAISE;
END;
/
create or replace procedure jw_bom_excure_proc(Errbuf Out Varchar2,
Errcode Out Varchar2) IS
V_Errbuf VARCHAR2(1000);
V_Errcode VARCHAR2(1000);
CURSOR C1 IS
SELECT distinct t.item_number
FROM JW_20171227_T T;
BEGIN
FOR V1 IN C1 LOOP
JW_PROCESS_BOM_PROC(V_Errbuf,
V_Errcode,
V1.ITEM_NUMBER);
END LOOP;
END;
/
1630
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
