项目系统与EBS系统对接，项目系统每次都抛全部的BOM资料，自己比较得出更改的部分create or replace package body JW_PLM_PROCESS_BOM_PKG is
PROCEDURE PROCESS_BOM(Errbuf Out Varchar2,
Errcode Out Varchar2,
p_id number) 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;
-- 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 jib.pid,
jib.assembly_item,
'1ZW' ORG_CODE,
jib.component_item,
jib.component_quantity,
jib.ref_no,
jib.bom_remark,
jib.replace_group,
jib.replace_range,
decode(trim(JIB.PRODUCT_ATTR),
'SMT',
10,
'DIP',
20,
'ASS',
30,
'PK',
40,
'COM',
50,10)
OPER,
1 - TO_NUMBER(nvl(MSIB.ATTRIBUTE2, 0)) YIELD
from JX_ITF_BOM jib, mtl_system_items_b msib
where jib.component_item = msib.segment1
and msib.organization_id = 118
and jib.pid = p_id
AND NVL(JIB.REPLACE_RANGE, 'A') = 'A';
CURSOR C_SUB(P_replace_group NUMBER) IS ---替代料
SELECT jib.assembly_item, jib.component_item, jib.component_quantity
FROM JX_ITF_BOM JIB
WHERE jib.pid = p_id
AND JIB.replace_group = P_replace_group
AND NVL(JIB.REPLACE_RANGE, 'A') <> 'A';
BEGIN
-- Get the user_id
-- intiialize applications information
FND_GLOBAL.APPS_INITIALIZE(1553, 51182, 702); -- Mfg / Mfg & Dist Mgr / INV
N_item_sequence_number := 0;
l_cnt := 0;
i := 0;
j := 0;
-- dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
for v_com in c_bom_com loop
l_cnt := l_cnt + 1;
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
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_com.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 := N_item_sequence_number;
l_bom_component_tbl(l_cnt).operation_sequence_number := v_com.oper;
l_bom_component_tbl(l_cnt).transaction_type := 'CREATE';
l_bom_component_tbl(l_cnt).Projected_Yield := v_com.yield; --产出率
l_bom_component_tbl(l_cnt).quantity_per_assembly := v_com.component_quantity;
l_bom_component_tbl(l_cnt).return_status := NULL;
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);
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;
PROCEDURE JW_ECN_PROC(Errbuf Out Varchar2,
Errcode Out Varchar2,
p_ecn varchar2) is
l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
l_rev_operation_tbl Bom_Rtg_Pub.rev_operation_tbl_Type;
l_rev_op_resource_tbl Bom_Rtg_Pub.rev_op_resource_Tbl_Type;
l_rev_sub_resource_tbl Bom_Rtg_Pub.rev_sub_resource_Tbl_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_text VARCHAR2(2000);
i number := 0;
j number := 0;
k number := 0;
m number := 0;
ln_request_id NUMBER;
v_eco_name varchar2(50);
v_start_effective_date date := sysdate + 0.001;
N_OPER_NUMBER NUMBER;
cursor c_ecn is
select distinct at.assembly_id, at.assembly_item
from jw_plm_ecn_assembly_t at,jw_plm_ecn_component_t ct
where at.assembly_id = ct.assembly_id
and at.ecn_name = p_ecn;
cursor c_com(p_ass_id number) is
select ct.component_id,
ct.assembly_id,
ct.acd_type,
ct.component_item,
ct.qty,
ct.bom_remark,
CT.OPERATION_SEQUENCE_NUMBER,
ct.component_sequence_id
from jw_plm_ecn_component_t ct
where ct.assembly_id = p_ass_id;
cursor c_sub(p_ass_id number,p_com_id number) is
select st.assembly_id,
st.component_id,
st.acd_type,
st.substitute_item,
st.qty
from jw_plm_ecn_substitute_t st
where st.assembly_id = p_ass_id
and st.component_id = p_com_id;
cursor c_ref(p_ass_id number,p_com_id number) is
select rt.assembly_id,
rt.component_id,
rt.acd_type,
rt.reference_code
from jw_plm_ecn_reference_t rt
where rt.assembly_id = p_ass_id
and rt.component_id = p_com_id;
begin
Errcode := 'E';
fnd_global.apps_initialize(user_id => 1553, --x_User_Id,
resp_id => 51263,
resp_appl_id => 703);
--v_eco_name := p_ecn;
------change order
l_eco_rec.eco_name := p_ecn;
l_eco_rec.organization_code := '1ZW';
--l_eco_rec.requestor := 406681;
l_eco_rec.change_type_code := 'ECO';
--l_eco_rec.eco_department_name :='Design Engineering';
l_eco_rec.reason_code := 'RD';
-- l_eco_rec.priority_code :='Medium';
-- l_eco_rec.approval_list_name :='YYavllist';
l_eco_rec.approval_status_NAME := 'Approved';
l_eco_rec.PLM_OR_ERP_CHANGE := 'ERP';
l_eco_rec.Status_Name := 'Open';
l_eco_rec.transaction_type := 'CREATE';
for v_ecn in c_ecn loop
i := i + 1;
l_revised_item_tbl(i).eco_name := p_ecn;
l_revised_item_tbl(i).organization_code := '1ZW';
l_revised_item_tbl(i).revised_item_name := v_ecn.assembly_item;
-- l_revised_item_tbl(1).new_revised_item_revision := 'B';
-- l_revised_item_tbl(1).updated_revised_item_revision := NULL;
l_revised_item_tbl(i).start_effective_date := v_start_effective_date;
l_revised_item_tbl(i).Status_Type := 1;
-- l_revised_item_tbl(1).new_effective_date := NULL;
l_revised_item_tbl(i).mrp_active := 1;
l_revised_item_tbl(i).disposition_type := 1;
l_revised_item_tbl(i).update_wip := 1;
l_revised_item_tbl(i).transaction_type := 'CREATE';
BEGIN
SELECT MAX(BCB.ITEM_NUM)
INTO N_OPER_NUMBER
FROM MTL_SYSTEM_ITEMS_B MSIB,BOM_STRUCTURES_B BSB,BOM_COMPONENTS_B BCB
WHERE MSIB.SEGMENT1 = v_ecn.assembly_item
AND MSIB.ORGANIZATION_ID = 118
AND MSIB.INVENTORY_ITEM_ID = BSB.ASSEMBLY_ITEM_ID
AND MSIB.ORGANIZATION_ID = BSB.ORGANIZATION_ID
AND BSB.BILL_SEQUENCE_ID = BCB.BILL_SEQUENCE_ID;
EXCEPTION
WHEN OTHERS THEN
N_OPER_NUMBER := 0 ;
END;
for v_com in c_com(v_ecn.assembly_id) loop
j := j + 1;
l_rev_component_tbl(j).eco_name := p_ecn;
l_rev_component_tbl(j).organization_code := '1ZW';
l_rev_component_tbl(j).revised_item_name := v_ecn.assembly_item;
-- l_rev_component_tbl(1).new_revised_item_revision := 'B';
l_rev_component_tbl(j).alternate_bom_code := '';
l_rev_component_tbl(j).start_effective_date := v_start_effective_date;
l_rev_component_tbl(j).operation_sequence_number := v_com.Operation_Sequence_Number;
l_rev_component_tbl(j).component_item_name := v_com.component_item; --Q2CM-389307-00 Q2RE-375614-00
l_rev_component_tbl(j).acd_type := v_com.Acd_Type;
-- l_rev_component_tbl(j).item_sequence_number := 130 ;
l_rev_component_tbl(j).Quantity_Per_Assembly := v_com.Qty;
l_rev_component_tbl(j).transaction_type := 'CREATE';-------------------公用
------------------------------------------------------------------
if l_rev_component_tbl(j).acd_type = 1 then -------------------添加
l_rev_component_tbl(j).COMMENTS := v_com.bom_remark;
N_OPER_NUMBER := N_OPER_NUMBER + 10;
l_rev_component_tbl(j).item_sequence_number := N_OPER_NUMBER;
elsif l_rev_component_tbl(j).acd_type = 2 then ---------------------更新
select bcb.effectivity_date,
bcb.operation_seq_num,
bcb.operation_seq_num,
bcb.item_num,
v_com.qty
into l_rev_component_tbl(j).old_effectivity_date,
l_rev_component_tbl(j).old_operation_sequence_number,
l_rev_component_tbl(j).operation_sequence_number,
l_rev_component_tbl(j).item_sequence_number,
l_rev_component_tbl(j).Quantity_Per_Assembly
from bom_components_b bcb
where bcb.component_sequence_id =
v_com.component_sequence_id;
elsif l_rev_component_tbl(j).acd_type = 3 then -----------------------------禁用
l_rev_component_tbl(j).DISABLE_DATE := v_start_effective_date + 0.001;
select bcb.effectivity_date,
bcb.operation_seq_num,
bcb.operation_seq_num,
bcb.item_num
into l_rev_component_tbl(j).old_effectivity_date,
l_rev_component_tbl(j).old_operation_sequence_number,
l_rev_component_tbl(j).operation_sequence_number,
l_rev_component_tbl(j).item_sequence_number
from bom_components_b bcb
where bcb.component_sequence_id =
v_com.component_sequence_id;
end if;
for v_sub in c_sub(v_ecn.assembly_id,v_com.component_id) loop -----------------替代料
k := k + 1;
l_sub_component_tbl(k).eco_name := p_ecn;
l_sub_component_tbl(k).organization_code := '1ZW';
l_sub_component_tbl(k).revised_item_name := v_ecn.assembly_item;
l_sub_component_tbl(k).start_effective_date := v_start_effective_date;
-- l_sub_component_tbl(1).new_revised_item_revision := 'B';
l_sub_component_tbl(k).component_item_name := v_com.component_item;
l_sub_component_tbl(k).alternate_bom_code := '';
l_sub_component_tbl(k).substitute_component_name := v_sub.substitute_item;
l_sub_component_tbl(k).acd_type := v_sub.acd_type;
l_sub_component_tbl(k).operation_sequence_number := v_com.Operation_Sequence_Number;
l_sub_component_tbl(k).substitute_item_quantity := v_sub.qty;
l_sub_component_tbl(k).transaction_type := 'CREATE';
end loop;
for v_ref in c_ref(v_ecn.assembly_id,v_com.component_id) loop
m := m + 1;
l_ref_designator_tbl(m).eco_name := p_ecn;
l_ref_designator_tbl(m).organization_code := '1ZW';
l_ref_designator_tbl(m).revised_item_name := v_ecn.assembly_item;
l_ref_designator_tbl(m).start_effective_date := v_start_effective_date;
-- l_ref_designator_tbl(1).new_revised_item_revision :='B';
l_ref_designator_tbl(m).operation_sequence_number := v_com.Operation_Sequence_Number;
l_ref_designator_tbl(m).component_item_name := v_com.component_item;
l_ref_designator_tbl(m).alternate_bom_code := '';
l_ref_designator_tbl(m).reference_designator_name := v_ref.reference_code;
l_ref_designator_tbl(m).acd_type := v_ref.acd_type;
l_ref_designator_tbl(m).transaction_type := 'CREATE';
end loop;
end loop;
end loop;
dbms_output.put_line('Comes before process_eco call');
-- Call the private API
Eng_Eco_PUB.Process_Eco(p_api_version_number => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
p_eco_rec => l_eco_rec,
p_eco_revision_tbl => l_eco_revision_tbl,
p_revised_item_tbl => l_revised_item_tbl,
p_rev_component_tbl => l_rev_component_tbl,
p_sub_component_tbl => l_sub_component_tbl,
p_ref_designator_tbl => l_ref_designator_tbl,
x_eco_rec => l_eco_rec,
x_eco_revision_tbl => l_eco_revision_tbl,
x_revised_item_tbl => l_revised_item_tbl,
x_rev_component_tbl => l_rev_component_tbl,
x_sub_component_tbl => l_sub_component_tbl,
x_ref_designator_tbl => l_ref_designator_tbl,
x_rev_operation_tbl => l_rev_operation_tbl,
x_rev_op_resource_tbl => l_rev_op_resource_tbl,
x_rev_sub_resource_tbl => l_rev_sub_resource_tbl);
--
if l_return_status = 'S' THEN
ln_request_id := fnd_request.submit_request(application => 'ENG',
program => 'ENCACN',
sub_request => FALSE,
argument1 => 118 --v_split.OPERATING_UNIT
,
argument2 => 2 --tl_rcv_headers_interface.group_id--v_split.order_source_id
,
argument3 => NULL --v_split.orig_sys_document_ref
,
argument4 => p_ecn,
argument5 => '',
argument6 => '',
argument7 => '',
argument8 => '',
argument9 => '',
argument10 => '',
argument11 => '',
argument12 => '',
argument13 => '',
argument14 => '',
argument15 => '');
commit;
Errcode := 'S';
dbms_output.put_line('提交成功');
else
Errcode := 'E';
end if;
/*Error_Handler.Get_Message_List(x_message_list => l_error_table);
FOR i IN 1 .. l_error_table.COUNT LOOP
dbms_output.put_line('Entity Id:' || l_error_table(i).entity_id);
dbms_output.put_line('Index:' || l_error_table(i).entity_index);
dbms_output.put_line('Mesg:' || l_error_table(i).message_text);
dbms_output.put_line('---------------------------------------');
END LOOP;
dbms_output.put_line('Total Messages:' || to_char(i));
l_msg_count := Error_Handler.Get_Message_Count;
dbms_output.put_line('Message Count Function:' || to_char(l_msg_count));
Error_Handler.Dump_Message_List;
Error_Handler.Get_Entity_Message(p_entity_id => 'ECO',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'REV',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RI',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'SC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RD',
x_message_list => l_error_table);*/
COMMIT;
JW_PLM_PROCESS_BOM_PKG.JW_DELETE_T(p_ecn);
null;
exception
when others then
JW_PLM_PROCESS_BOM_PKG.JW_DELETE_T(p_ecn);
Errcode := 'E';
end;
PROCEDURE JW_ECN_ADD is
l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
l_rev_operation_tbl Bom_Rtg_Pub.rev_operation_tbl_Type;
l_rev_op_resource_tbl Bom_Rtg_Pub.rev_op_resource_Tbl_Type;
l_rev_sub_resource_tbl Bom_Rtg_Pub.rev_sub_resource_Tbl_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_text VARCHAR2(2000);
i number;
ln_request_id NUMBER;
v_eco_name varchar2(50);
BEGIN
v_eco_name := 'ZBC011122';
------change order
l_eco_rec.eco_name := v_eco_name;
l_eco_rec.organization_code := '1ZW';
--l_eco_rec.requestor := 406681;
l_eco_rec.change_type_code := 'ECO';
--l_eco_rec.eco_department_name :='Design Engineering';
l_eco_rec.reason_code := 'RD';
-- l_eco_rec.priority_code :='Medium';
-- l_eco_rec.approval_list_name :='YYavllist';
l_eco_rec.approval_status_NAME := 'Approved';
l_eco_rec.PLM_OR_ERP_CHANGE := 'ERP';
l_eco_rec.Status_Name := 'Open';
l_eco_rec.transaction_type := 'CREATE';
------change order revision
/* l_eco_revision_tbl(1).eco_name := v_eco_name;
l_eco_revision_tbl(1).organization_code := '1ZW';
-- l_eco_revision_tbl(1).revision := 'A';
-- l_eco_revision_tbl(1).new_revision := 'A';
l_eco_revision_tbl(1).comments := 'test';
l_eco_revision_tbl(1).transaction_type := 'CREATE';*/
-------revised items
l_revised_item_tbl(1).eco_name := v_eco_name;
l_revised_item_tbl(1).organization_code := '1ZW';
l_revised_item_tbl(1).revised_item_name := '4WXX-776111-00';
-- l_revised_item_tbl(1).new_revised_item_revision := 'B';
-- l_revised_item_tbl(1).updated_revised_item_revision := NULL;
l_revised_item_tbl(1).start_effective_date := sysdate;
l_revised_item_tbl(1).Status_Type := 1;
-- l_revised_item_tbl(1).new_effective_date := NULL;
l_revised_item_tbl(1).mrp_active := 1;
l_revised_item_tbl(1).disposition_type := 1;
l_revised_item_tbl(1).update_wip := 1;
l_revised_item_tbl(1).transaction_type := 'CREATE';
-------bom components
l_rev_component_tbl(1).eco_name := v_eco_name;
l_rev_component_tbl(1).organization_code := '1ZW';
l_rev_component_tbl(1).revised_item_name := '4WXX-776111-00';
-- l_rev_component_tbl(1).new_revised_item_revision := 'B';
l_rev_component_tbl(1).alternate_bom_code := '';
l_rev_component_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
l_rev_component_tbl(1).operation_sequence_number := 20;
l_rev_component_tbl(1).component_item_name := 'Q2CM-387864-00'; --Q2CM-389307-00 Q2RE-375614-00
l_rev_component_tbl(1).acd_type := '1';
l_rev_component_tbl(1).item_sequence_number := 130;
l_rev_component_tbl(1).transaction_type := 'CREATE';
-------sub
l_sub_component_tbl(1).eco_name := v_eco_name;
l_sub_component_tbl(1).organization_code := '1ZW';
l_sub_component_tbl(1).revised_item_name := '4WXX-776111-00';
l_sub_component_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
-- l_sub_component_tbl(1).new_revised_item_revision := 'B';
l_sub_component_tbl(1).component_item_name := 'Q2CM-387864-00';
l_sub_component_tbl(1).alternate_bom_code := '';
l_sub_component_tbl(1).substitute_component_name := 'Q2CM-389307-00';
l_sub_component_tbl(1).acd_type := 1;
l_sub_component_tbl(1).operation_sequence_number := 20;
l_sub_component_tbl(1).substitute_item_quantity := 1;
l_sub_component_tbl(1).transaction_type := 'CREATE';
--------dis
l_ref_designator_tbl(1).eco_name := v_eco_name;
l_ref_designator_tbl(1).organization_code := '1ZW';
l_ref_designator_tbl(1).revised_item_name := '4WXX-776111-00';
l_ref_designator_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
-- l_ref_designator_tbl(1).new_revised_item_revision :='B';
l_ref_designator_tbl(1).operation_sequence_number := 20;
l_ref_designator_tbl(1).component_item_name := 'Q2CM-387864-00';
l_ref_designator_tbl(1).alternate_bom_code := '';
l_ref_designator_tbl(1).reference_designator_name := 'YYdes';
l_ref_designator_tbl(1).acd_type := 1;
l_ref_designator_tbl(1).transaction_type := 'CREATE';
fnd_global.apps_initialize(user_id => 1553, --x_User_Id,
resp_id => 51263,
resp_appl_id => 703);
dbms_output.put_line('Comes before process_eco call');
-- Call the private API
Eng_Eco_PUB.Process_Eco(p_api_version_number => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
p_eco_rec => l_eco_rec,
p_eco_revision_tbl => l_eco_revision_tbl,
p_revised_item_tbl => l_revised_item_tbl,
p_rev_component_tbl => l_rev_component_tbl,
p_sub_component_tbl => l_sub_component_tbl,
p_ref_designator_tbl => l_ref_designator_tbl,
x_eco_rec => l_eco_rec,
x_eco_revision_tbl => l_eco_revision_tbl,
x_revised_item_tbl => l_revised_item_tbl,
x_rev_component_tbl => l_rev_component_tbl,
x_sub_component_tbl => l_sub_component_tbl,
x_ref_designator_tbl => l_ref_designator_tbl,
x_rev_operation_tbl => l_rev_operation_tbl,
x_rev_op_resource_tbl => l_rev_op_resource_tbl,
x_rev_sub_resource_tbl => l_rev_sub_resource_tbl);
--
if l_return_status = 'S' THEN
ln_request_id := fnd_request.submit_request(application => 'ENG',
program => 'ENCACN',
sub_request => FALSE,
argument1 => 118 --v_split.OPERATING_UNIT
,
argument2 => 2 --tl_rcv_headers_interface.group_id--v_split.order_source_id
,
argument3 => NULL --v_split.orig_sys_document_ref
,
argument4 => v_eco_name,
argument5 => '',
argument6 => '',
argument7 => '',
argument8 => '',
argument9 => '',
argument10 => '',
argument11 => '',
argument12 => '',
argument13 => '',
argument14 => '',
argument15 => '');
commit;
dbms_output.put_line('提交成功');
end if;
Error_Handler.Get_Message_List(x_message_list => l_error_table);
FOR i IN 1 .. l_error_table.COUNT LOOP
dbms_output.put_line('Entity Id:' || l_error_table(i).entity_id);
dbms_output.put_line('Index:' || l_error_table(i).entity_index);
dbms_output.put_line('Mesg:' || l_error_table(i).message_text);
dbms_output.put_line('---------------------------------------');
END LOOP;
dbms_output.put_line('Total Messages:' || to_char(i));
l_msg_count := Error_Handler.Get_Message_Count;
dbms_output.put_line('Message Count Function:' || to_char(l_msg_count));
Error_Handler.Dump_Message_List;
Error_Handler.Get_Entity_Message(p_entity_id => 'ECO',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'REV',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RI',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'SC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RD',
x_message_list => l_error_table);
COMMIT;
END;
PROCEDURE JW_ECN_DELETE is
l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
l_rev_operation_tbl Bom_Rtg_Pub.rev_operation_tbl_Type;
l_rev_op_resource_tbl Bom_Rtg_Pub.rev_op_resource_Tbl_Type;
l_rev_sub_resource_tbl Bom_Rtg_Pub.rev_sub_resource_Tbl_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_text VARCHAR2(2000);
i number;
v_eco_name varchar2(50);
BEGIN
v_eco_name := 'ZBC011114';
------change order
l_eco_rec.eco_name := v_eco_name;
l_eco_rec.organization_code := '1ZW';
--l_eco_rec.requestor := 406681;
l_eco_rec.change_type_code := 'ECO';
--l_eco_rec.eco_department_name :='Design Engineering';
l_eco_rec.reason_code := 'RD';
-- l_eco_rec.priority_code :='Medium';
-- l_eco_rec.approval_list_name :='YYavllist';
l_eco_rec.approval_status_NAME := 'Approved';
l_eco_rec.PLM_OR_ERP_CHANGE := 'ERP';
l_eco_rec.Status_Name := 'Open';
l_eco_rec.transaction_type := 'CREATE';
------change order revision
/* l_eco_revision_tbl(1).eco_name := v_eco_name;
l_eco_revision_tbl(1).organization_code := '1ZW';
-- l_eco_revision_tbl(1).revision := 'A';
-- l_eco_revision_tbl(1).new_revision := 'A';
l_eco_revision_tbl(1).comments := 'test';
l_eco_revision_tbl(1).transaction_type := 'CREATE';*/
-------revised items
l_revised_item_tbl(1).eco_name := v_eco_name;
l_revised_item_tbl(1).organization_code := '1ZW';
l_revised_item_tbl(1).revised_item_name := '4PPP-100007-00';
-- l_revised_item_tbl(1).new_revised_item_revision := 'B';
-- l_revised_item_tbl(1).updated_revised_item_revision := NULL;
l_revised_item_tbl(1).start_effective_date := sysdate;
l_revised_item_tbl(1).Status_Type := 1;
-- l_revised_item_tbl(1).new_effective_date := NULL;
l_revised_item_tbl(1).mrp_active := 1;
l_revised_item_tbl(1).disposition_type := 1;
l_revised_item_tbl(1).update_wip := 1;
l_revised_item_tbl(1).transaction_type := 'CREATE';
-------bom components
l_rev_component_tbl(1).eco_name := v_eco_name;
l_rev_component_tbl(1).organization_code := '1ZW';
l_rev_component_tbl(1).revised_item_name := '4PPP-100007-00';
-- l_rev_component_tbl(1).COMPONENT_SEQUENCE_ID := 1331016;
-- l_rev_component_tbl(1).new_revised_item_revision := 'B';
l_rev_component_tbl(1).alternate_bom_code := '';
l_rev_component_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
l_rev_component_tbl(1).DISABLE_DATE := sysdate + 1;
l_rev_component_tbl(1).old_effectivity_date := to_date('2017-11-21 11:28:09',
'yyyy-mm-dd hh24:mi:ss');
l_rev_component_tbl(1).old_operation_sequence_number := 10;
l_rev_component_tbl(1).operation_sequence_number := 10;
l_rev_component_tbl(1).component_item_name := 'B2A9-387951-00'; --Q2CM-389307-00 Q2RE-375614-00
l_rev_component_tbl(1).acd_type := 3;
l_rev_component_tbl(1).item_sequence_number := 10;
l_rev_component_tbl(1).transaction_type := 'CREATE';
-------sub
/*l_sub_component_tbl(1).eco_name := v_eco_name;
l_sub_component_tbl(1).organization_code := '1ZW';
l_sub_component_tbl(1).revised_item_name :='YYRMapple';
l_sub_component_tbl(1).start_effective_date :=l_revised_item_tbl(1).start_effective_date;
l_sub_component_tbl(1).new_revised_item_revision := 'B';
l_sub_component_tbl(1).component_item_name :='YY001';
l_sub_component_tbl(1).alternate_bom_code :='';
l_sub_component_tbl(1).substitute_component_name :='YYM';
l_sub_component_tbl(1).acd_type := 1;
l_sub_component_tbl(1).operation_sequence_number :=1;
l_sub_component_tbl(1).substitute_item_quantity :=1;
l_sub_component_tbl(1).transaction_type := 'CREATE';
--------dis
l_ref_designator_tbl(1).eco_name := v_eco_name;
l_ref_designator_tbl(1).organization_code := 'V1';
l_ref_designator_tbl(1).revised_item_name := 'YYRMapple';
l_ref_designator_tbl(1).start_effective_date:=l_revised_item_tbl(1).start_effective_date;
l_ref_designator_tbl(1).new_revised_item_revision :='B';
l_ref_designator_tbl(1).operation_sequence_number :=1;
l_ref_designator_tbl(1).component_item_name :='YY001';
l_ref_designator_tbl(1).alternate_bom_code :='';
l_ref_designator_tbl(1).reference_designator_name :='YYdes';
l_ref_designator_tbl(1).acd_type :=1;
l_ref_designator_tbl(1).transaction_type := 'CREATE';*/
fnd_global.apps_initialize(user_id => 1553, --x_User_Id,
resp_id => 51263,
resp_appl_id => 703);
dbms_output.put_line('Comes before process_eco call');
-- Call the private API
Eng_Eco_PUB.Process_Eco(p_api_version_number => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
p_eco_rec => l_eco_rec,
p_eco_revision_tbl => l_eco_revision_tbl,
p_revised_item_tbl => l_revised_item_tbl,
p_rev_component_tbl => l_rev_component_tbl,
p_sub_component_tbl => l_sub_component_tbl,
p_ref_designator_tbl => l_ref_designator_tbl,
x_eco_rec => l_eco_rec,
x_eco_revision_tbl => l_eco_revision_tbl,
x_revised_item_tbl => l_revised_item_tbl,
x_rev_component_tbl => l_rev_component_tbl,
x_sub_component_tbl => l_sub_component_tbl,
x_ref_designator_tbl => l_ref_designator_tbl,
x_rev_operation_tbl => l_rev_operation_tbl,
x_rev_op_resource_tbl => l_rev_op_resource_tbl,
x_rev_sub_resource_tbl => l_rev_sub_resource_tbl);
--
Error_Handler.Get_Message_List(x_message_list => l_error_table);
FOR i IN 1 .. l_error_table.COUNT LOOP
dbms_output.put_line('Entity Id:' || l_error_table(i).entity_id);
dbms_output.put_line('Index:' || l_error_table(i).entity_index);
dbms_output.put_line('Mesg:' || l_error_table(i).message_text);
dbms_output.put_line('---------------------------------------');
END LOOP;
dbms_output.put_line('Total Messages:' || to_char(i));
l_msg_count := Error_Handler.Get_Message_Count;
dbms_output.put_line('Message Count Function:' || to_char(l_msg_count));
Error_Handler.Dump_Message_List;
Error_Handler.Get_Entity_Message(p_entity_id => 'ECO',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'REV',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RI',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'SC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RD',
x_message_list => l_error_table);
COMMIT;
END;
PROCEDURE JW_ECN_CHANGE is
l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
l_rev_operation_tbl Bom_Rtg_Pub.rev_operation_tbl_Type;
l_rev_op_resource_tbl Bom_Rtg_Pub.rev_op_resource_Tbl_Type;
l_rev_sub_resource_tbl Bom_Rtg_Pub.rev_sub_resource_Tbl_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_text VARCHAR2(2000);
i number;
v_eco_name varchar2(50);
v_ass_item varchar2(50);
BEGIN
--http://blog.163.com/ahongname_2008/blog/static/178556952011111339619/
v_eco_name := 'ZBC011126';
v_ass_item := '4PXX-761989-00';
------change order
l_eco_rec.eco_name := v_eco_name;
l_eco_rec.organization_code := '1ZW';
--l_eco_rec.requestor := 406681;
l_eco_rec.change_type_code := 'ECO';
--l_eco_rec.eco_department_name :='Design Engineering';
l_eco_rec.reason_code := 'RD';
-- l_eco_rec.priority_code :='Medium';
-- l_eco_rec.approval_list_name :='YYavllist';
l_eco_rec.approval_status_NAME := 'Approved';
l_eco_rec.PLM_OR_ERP_CHANGE := 'ERP';
l_eco_rec.Status_Name := 'Open';
l_eco_rec.transaction_type := 'CREATE';
------change order revision
/* l_eco_revision_tbl(1).eco_name := v_eco_name;
l_eco_revision_tbl(1).organization_code := '1ZW';
-- l_eco_revision_tbl(1).revision := 'A';
-- l_eco_revision_tbl(1).new_revision := 'A';
l_eco_revision_tbl(1).comments := 'test';
l_eco_revision_tbl(1).transaction_type := 'CREATE';*/
-------revised items
l_revised_item_tbl(1).eco_name := v_eco_name;
l_revised_item_tbl(1).organization_code := '1ZW';
l_revised_item_tbl(1).revised_item_name := v_ass_item;
-- l_revised_item_tbl(1).new_revised_item_revision := 'B';
-- l_revised_item_tbl(1).updated_revised_item_revision := NULL;
l_revised_item_tbl(1).start_effective_date := sysdate;
l_revised_item_tbl(1).Status_Type := 1;
-- l_revised_item_tbl(1).new_effective_date := NULL;
l_revised_item_tbl(1).mrp_active := 1;
l_revised_item_tbl(1).disposition_type := 1;
l_revised_item_tbl(1).update_wip := 1;
l_revised_item_tbl(1).transaction_type := 'CREATE';
-------bom components
l_rev_component_tbl(1).eco_name := v_eco_name;
l_rev_component_tbl(1).organization_code := '1ZW';
l_rev_component_tbl(1).revised_item_name := v_ass_item;
-- l_rev_component_tbl(1).COMPONENT_SEQUENCE_ID := 1331016;
-- l_rev_component_tbl(1).new_revised_item_revision := 'B';
l_rev_component_tbl(1).comments := 1;
l_rev_component_tbl(1).alternate_bom_code := '';
l_rev_component_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
-- l_rev_component_tbl(1).DISABLE_DATE := sysdate + 1;
l_rev_component_tbl(1).old_effectivity_date := to_date('2017-06-28 10:10:59',
'yyyy-mm-dd hh24:mi:ss');
l_rev_component_tbl(1).old_operation_sequence_number := 10;
l_rev_component_tbl(1).operation_sequence_number := 10;
l_rev_component_tbl(1).component_item_name := 'B2BF-365313-00'; --Q2CM-389307-00 Q2RE-375614-00
l_rev_component_tbl(1).Quantity_Per_Assembly := 2;
l_rev_component_tbl(1).acd_type := 2;
l_rev_component_tbl(1).item_sequence_number := 10;
l_rev_component_tbl(1).transaction_type := 'CREATE';
-------sub
l_sub_component_tbl(1).eco_name := v_eco_name;
l_sub_component_tbl(1).organization_code := '1ZW';
l_sub_component_tbl(1).revised_item_name := v_ass_item;
l_sub_component_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
-- l_sub_component_tbl(1).new_revised_item_revision := 'B';
l_sub_component_tbl(1).component_item_name := 'B2BF-365313-00';
l_sub_component_tbl(1).alternate_bom_code := '';
l_sub_component_tbl(1).substitute_component_name := 'Q2CM-389307-00';
l_sub_component_tbl(1).acd_type := 1;
l_sub_component_tbl(1).operation_sequence_number := 10;
l_sub_component_tbl(1).substitute_item_quantity := 2;
l_sub_component_tbl(1).transaction_type := 'CREATE';
--------dis
l_ref_designator_tbl(1).eco_name := v_eco_name;
l_ref_designator_tbl(1).organization_code := '1ZW';
l_ref_designator_tbl(1).revised_item_name := v_ass_item;
l_ref_designator_tbl(1).start_effective_date := l_revised_item_tbl(1)
.start_effective_date;
-- l_ref_designator_tbl(1).new_revised_item_revision :='B';
l_ref_designator_tbl(1).operation_sequence_number := 10;
l_ref_designator_tbl(1).component_item_name := 'B2BF-365313-00';
l_ref_designator_tbl(1).alternate_bom_code := '';
l_ref_designator_tbl(1).reference_designator_name := 'YYdesd1';
l_ref_designator_tbl(1).acd_type := 1;
l_ref_designator_tbl(1).transaction_type := 'CREATE';
fnd_global.apps_initialize(user_id => 1553, --x_User_Id,
resp_id => 51263,
resp_appl_id => 703);
dbms_output.put_line('Comes before process_eco call');
-- Call the private API
Eng_Eco_PUB.Process_Eco(p_api_version_number => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
p_eco_rec => l_eco_rec,
p_eco_revision_tbl => l_eco_revision_tbl,
p_revised_item_tbl => l_revised_item_tbl,
p_rev_component_tbl => l_rev_component_tbl,
p_sub_component_tbl => l_sub_component_tbl,
p_ref_designator_tbl => l_ref_designator_tbl,
x_eco_rec => l_eco_rec,
x_eco_revision_tbl => l_eco_revision_tbl,
x_revised_item_tbl => l_revised_item_tbl,
x_rev_component_tbl => l_rev_component_tbl,
x_sub_component_tbl => l_sub_component_tbl,
x_ref_designator_tbl => l_ref_designator_tbl,
x_rev_operation_tbl => l_rev_operation_tbl,
x_rev_op_resource_tbl => l_rev_op_resource_tbl,
x_rev_sub_resource_tbl => l_rev_sub_resource_tbl);
--
Error_Handler.Get_Message_List(x_message_list => l_error_table);
FOR i IN 1 .. l_error_table.COUNT LOOP
dbms_output.put_line('Entity Id:' || l_error_table(i).entity_id);
dbms_output.put_line('Index:' || l_error_table(i).entity_index);
dbms_output.put_line('Mesg:' || l_error_table(i).message_text);
dbms_output.put_line('---------------------------------------');
END LOOP;
dbms_output.put_line(l_return_status || 'Total Messages:' ||
to_char(i));
l_msg_count := Error_Handler.Get_Message_Count;
dbms_output.put_line(l_return_status || 'Message Count Function:' ||
to_char(l_msg_count));
Error_Handler.Dump_Message_List;
Error_Handler.Get_Entity_Message(p_entity_id => 'ECO',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'REV',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RI',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'SC',
x_message_list => l_error_table);
Error_Handler.Get_Entity_Message(p_entity_id => 'RD',
x_message_list => l_error_table);
COMMIT;
END;
PROCEDURE JW_DELETE_T(P_CN VARCHAR2) IS----------------------删除ecn临时表
CURSOR C1 IS
SELECT ST.ASSEMBLY_ID
FROM jw_plm_ecn_assembly_t ST
WHERE ST.ECN_NAME = P_CN;
BEGIN
FOR V1 IN C1 LOOP
INSERT INTO jw_plm_ecn_assembly_t_HIS
SELECT *
FROM jw_plm_ecn_assembly_t ST
WHERE st.assembly_id = v1.assembly_id;
INSERT INTO jw_plm_ecn_component_t_HIS
SELECT *
FROM jw_plm_ecn_component_t CT
WHERE ct.assembly_id = v1.assembly_id;
INSERT INTO jw_plm_ecn_substitute_t_HIS
SELECT *
FROM jw_plm_ecn_substitute_t ET
WHERE et.assembly_id = v1.assembly_id;
INSERT INTO jw_plm_ecn_reference_t_HIS
SELECT *
FROM jw_plm_ecn_reference_t FT
WHERE ft.assembly_id = v1.assembly_id;
COMMIT;
DELETE FROM jw_plm_ecn_assembly_t ST
WHERE st.assembly_id = v1.assembly_id;
DELETE FROM jw_plm_ecn_component_t CT
WHERE ct.assembly_id = v1.assembly_id;
DELETE FROM jw_plm_ecn_substitute_t ET
WHERE et.assembly_id = v1.assembly_id;
DELETE FROM jw_plm_ecn_reference_t FT
WHERE ft.assembly_id = v1.assembly_id;
COMMIT;
END LOOP;
END;
PROCEDURE JW_COMPARE_PLM_BOM(P_id number) is
v_assembly_id 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;
n_num1 number;
n_num2 number;
cursor c_COMPONENT is ----得到替代料
select ct.component_id,ct.component_item,ct.operation_sequence_number
from jw_plm_ecn_COMPONENT_t ct
where ct.assembly_id = v_assembly_id
and ct.acd_type = 1;
cursor c_com_ref is ----得到指示符
select ect.assembly_id,ect.component_id,ect.reference_code
from jw_plm_ecn_COMPONENT_t ect
where ect.assembly_id = v_assembly_id
and Ect.acd_type = 1;
CURSOR c_COMPONENT_2 IS
select jw_plm_ecn_COMPONENT_t_S.Nextval component_id,
JIB.COMPONENT_ITEM,
JIB.COMPONENT_QUANTITY NEW_QTY,
JIB.REPLACE_GROUP,
jib.replace_range,
BCB.COMPONENT_QUANTITY OLD_QTY,
BCB.COMPONENT_SEQUENCE_ID,
jib.ref_no,
decode(jib.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10) OPERATION_SEQ_NUM
from JX_ITF_BOM jib,
jx_itf_item jii,
bom_structures_b bsb,
MTL_SYSTEM_ITEMS_B MSIB1,
bom_components_b BCB,
MTL_SYSTEM_ITEMS_B MSIB2
where jib.pid = jii.pid
and jii.item_number = msib1.segment1
and msib1.inventory_item_id = bsb.assembly_item_id
and msib1.organization_id = 118
and jib.component_item = msib2.segment1
and msib2.inventory_item_id = bcb.component_item_id
and bsb.bill_sequence_id = bcb.bill_sequence_id
and msib2.organization_id = 118
and NVL(jib.replace_range, 'A') = 'A'
AND BCB.DISABLE_DATE IS NULL
AND decode(jib.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10) = BCB.OPERATION_SEQ_NUM
AND jib.pid = p_id;
begin
DBMS_OUTPUT.put_line('比较开始');
select jw_plm_ecn_assembly_t_s.nextval into v_assembly_id from dual;
insert into jw_plm_ecn_assembly_t(assembly_id,ecn_name,assembly_item) -----------------insert jw_plm_ecn_assembly_t table
select v_assembly_id,jic.change_name,jii.item_number
from JX_ITF_ITEM jii,JX_ITF_CHANGE jic
where jii.ato_number = jic.ato_number
and jii.pid = P_id;
COMMIT;
INSERT INTO jw_plm_ecn_COMPONENT_t -----------------insert jw_plm_ecn_COMPONENT_t table acd_type =1
(COMPONENT_id, assembly_id, acd_type, COMPONENT_item, qty, reference_code, BOM_REMARK,operation_sequence_number)
SELECT jw_plm_ecn_COMPONENT_t_S.Nextval,
v_assembly_id,
1,----add
JIB.COMPONENT_ITEM,
JIB.COMPONENT_QUANTITY,
jib.ref_no,
jib.bom_remark,
decode(jib.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10)
FROM JX_ITF_BOM JIB,
(select JB.COMPONENT_ITEM,decode(JB.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10) product_attr
from JX_ITF_ITEM ji, JX_ITF_BOM jb
where ji.PID = jb.PID
and ji.item_number = jb.assembly_item
and NVL(jb.replace_range, 'A') = 'A'
MINUS
SELECT MSIB2.SEGMENT1 COMPONENT_ITEM,BCB.OPERATION_SEQ_NUM
FROM bom_structures_b BSB,
bom_components_b BCB,
MTL_SYSTEM_ITEMS_B MSIB1,
MTL_SYSTEM_ITEMS_B MSIB2,
JX_ITF_ITEM J
WHERE J.ITEM_NUMBER = MSIB1.SEGMENT1
AND MSIB1.INVENTORY_ITEM_ID = BSB.ASSEMBLY_ITEM_ID
AND MSIB1.ORGANIZATION_ID = BSB.ORGANIZATION_ID
AND MSIB1.ORGANIZATION_ID = 118
AND BSB.BILL_SEQUENCE_ID = BCB.BILL_SEQUENCE_ID
AND BCB.DISABLE_DATE IS NULL
AND BCB.COMPONENT_ITEM_ID = MSIB2.INVENTORY_ITEM_ID
AND MSIB2.ORGANIZATION_ID = 118
AND J.PID = P_id) AAA
WHERE JIB.COMPONENT_ITEM = AAA.COMPONENT_ITEM
AND decode(jib.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10) = AAA.product_attr
AND JIB.PID = P_id;
COMMIT;
--新增料的替代料
for v_COMPONENT in c_COMPONENT loop ----------- acd_type =1 insert into jw_plm_ecn_substitute_t
insert into jw_plm_ecn_substitute_t
(substitute_id,
COMPONENT_id,
assembly_id,
acd_type,
substitute_item,
qty)
select jw_plm_ecn_substitute_t_s.nextval,
v_COMPONENT.Component_Id,
v_assembly_id,
1,
jib2.component_item,
jib2.component_quantity
from JX_ITF_BOM jib1, JX_ITF_BOM jib2
where jib1.pid = p_id
and jib1.component_item = v_COMPONENT.Component_Item
and jib1.replace_group = jib2.replace_group
and decode(jib1.product_attr,'SMT',10,'DIP',20,'ASS',30,'PK',40,'COM',50,10) = v_COMPONENT.operation_sequence_number
and jib2.pid = p_id
and jib2.component_item <> jib1.component_item;
end loop;
COMMIT;
--新增料的指示符
for v_com_ref in c_com_ref loop----------- acd_type =1 insert into jw_plm_ecn_reference_t
N_NUMBER := 0;
N_NUMBER1 := 0;
N_NUMBER2 := 0;
N_NUMBER3 := 1;
V_CHAR_REF := null;
if v_com_ref.reference_code is not null then
---指示符
SELECT (length(v_com_ref.reference_code || ',') -
length(replace(v_com_ref.reference_code || ',', ','))) / length(',')
INTO N_NUMBER
FROM DUAL;
FOR N_NUMBER1 IN 1 .. N_NUMBER LOOP
SELECT INSTR(v_com_ref.reference_code || ',', ',', 1, N_NUMBER1)
INTO N_NUMBER2
from DUAL;
SELECT SUBSTR(v_com_ref.reference_code || ',',
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
insert into jw_plm_ecn_reference_t
(reference_id, COMPONENT_id, assembly_id, acd_type, reference_code)
select jw_plm_ecn_reference_t_s.nextval,
v_com_ref.component_id,
v_assembly_id,
1,
V_CHAR_REF
from dual;
end if;
END LOOP;
COMMIT;
end if;
end loop;
---------------------------------------------禁用的部分 acd_type = 3
INSERT INTO jw_plm_ecn_COMPONENT_t -----------------insert jw_plm_ecn_COMPONENT_t table acd_type =3
(COMPONENT_id, assembly_id, acd_type, COMPONENT_item,operation_sequence_number,component_sequence_id)
SELECT jw_plm_ecn_COMPONENT_t_S.Nextval,
v_assembly_id,
3, ----delete
AAA.COMPONENT_ITEM,
AAA.OPERATION_SEQ_NUM,
c1.component_sequence_id
FROM (select MSIB2.SEGMENT1 COMPONENT_ITEM, BCB.OPERATION_SEQ_NUM
FROM bom_structures_b BSB,
bom_components_b BCB,
MTL_SYSTEM_ITEMS_B MSIB1,
MTL_SYSTEM_ITEMS_B MSIB2,
JX_ITF_ITEM J
WHERE J.ITEM_NUMBER = MSIB1.SEGMENT1
AND MSIB1.INVENTORY_ITEM_ID = BSB.ASSEMBLY_ITEM_ID
AND MSIB1.ORGANIZATION_ID = BSB.ORGANIZATION_ID
AND MSIB1.ORGANIZATION_ID = 118
AND BSB.BILL_SEQUENCE_ID = BCB.BILL_SEQUENCE_ID
AND BCB.DISABLE_DATE IS NULL
AND BCB.COMPONENT_ITEM_ID = MSIB2.INVENTORY_ITEM_ID
AND MSIB2.ORGANIZATION_ID = 118
AND J.PID = P_id
MINUS
select JB.COMPONENT_ITEM,
decode(JB.product_attr,
'SMT',
10,
'DIP',
20,
'ASS',
30,
'PK',
40,
'COM',
50,
10) product_attr
from JX_ITF_ITEM ji, JX_ITF_BOM jb
where ji.PID = jb.PID
and ji.item_number = jb.assembly_item
and NVL(jb.replace_range, 'A') = 'A') AAA,
JX_ITF_ITEM j1,
MTL_SYSTEM_ITEMS_B m1,
MTL_SYSTEM_ITEMS_B m2,
bom_structures_b s1,
bom_components_b c1
where J1.ITEM_NUMBER = m1.SEGMENT1
AND m1.INVENTORY_ITEM_ID = s1.ASSEMBLY_ITEM_ID
AND m1.ORGANIZATION_ID = s1.ORGANIZATION_ID
AND m1.ORGANIZATION_ID = 118
AND s1.BILL_SEQUENCE_ID = c1.BILL_SEQUENCE_ID
AND c1.DISABLE_DATE IS NULL
AND c1.COMPONENT_ITEM_ID = m2.INVENTORY_ITEM_ID
and AAA.COMPONENT_ITEM = M2.SEGMENT1
AND AAA.OPERATION_SEQ_NUM = C1.OPERATION_SEQ_NUM
AND m2.ORGANIZATION_ID = 118
AND J1.PID = P_id;
COMMIT;
---------------------------------------ACD_TYPE = 2 更新的部分--------------------
FOR V_COMPONENT_2 IN c_COMPONENT_2 LOOP
insert into jw_plm_ecn_substitute_t------insert into sub acd_type = 1
(substitute_id,
component_id,
assembly_id,
acd_type,
substitute_item,
qty)
SELECT jw_plm_ecn_substitute_t_S.Nextval,
V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
1,
JB.COMPONENT_ITEM,
AAA.component_quantity
FROM JX_ITF_BOM JB,
(select jib2.component_item,TO_NUMBER(jib2.component_quantity) component_quantity
from JX_ITF_BOM jib1, JX_ITF_BOM jib2
where jib1.pid = p_id
and jib1.component_item =
V_COMPONENT_2.COMPONENT_ITEM
and jib2.pid = p_id
and jib1.replace_group = jib2.replace_group
and jib2.component_item <>
V_COMPONENT_2.COMPONENT_ITEM
minus
select msib.segment1,bsc.substitute_item_quantity
from BOM_SUBSTITUTE_COMPONENTS bsc,
mtl_system_items_b msib
where bsc.substitute_component_id =
msib.inventory_item_id
and msib.organization_id = 118
AND NVL(BSC.ACD_TYPE,1) <> 3
and bsc.component_sequence_id =
V_COMPONENT_2.COMPONENT_SEQUENCE_ID
) AAA
WHERE JB.COMPONENT_ITEM = AAA.COMPONENT_ITEM
AND JB.REPLACE_GROUP = V_COMPONENT_2.REPLACE_GROUP
AND JB.PID = p_id
AND NVL(JB.REPLACE_RANGE, 'A') <> 'A';
COMMIT;
insert into jw_plm_ecn_substitute_t------insert into sub acd_type = 3
(substitute_id,
component_id,
assembly_id,
acd_type,
substitute_item/*,
qty*/)
SELECT jw_plm_ecn_substitute_t_S.Nextval,
V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
3,
BBB.COMPONENT_ITEM/*,
BBB.SUBSTITUTE_ITEM_QUANTITY*/
FROM
(select msib.segment1 component_item,BSC.SUBSTITUTE_ITEM_QUANTITY
from BOM_SUBSTITUTE_COMPONENTS bsc,
mtl_system_items_b msib
where bsc.substitute_component_id =
msib.inventory_item_id
and msib.organization_id = 118
AND NVL(BSC.ACD_TYPE,1) <> 3
and bsc.component_sequence_id =
V_COMPONENT_2.COMPONENT_SEQUENCE_ID
minus
select jib2.component_item,TO_NUMBER(JIB2.COMPONENT_QUANTITY)
from JX_ITF_BOM jib1, JX_ITF_BOM jib2
where jib1.pid = p_id
and jib1.component_item =
V_COMPONENT_2.COMPONENT_ITEM
and jib2.pid = p_id
and jib1.replace_group = jib2.replace_group
and jib2.component_item <>
V_COMPONENT_2.COMPONENT_ITEM
) BBB;
COMMIT;
/*insert into jw_plm_ecn_substitute_t ------insert into sub acd_type = 2 --替代料只有增加和删除 没有修改
(substitute_id,
component_id,
assembly_id,
acd_type,
substitute_item,
qty)
select jw_plm_ecn_substitute_t_S.Nextval,
V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
2,
jib2.component_item,
JIB2.COMPONENT_QUANTITY
from JX_ITF_BOM jib1,
JX_ITF_BOM jib2,
BOM_SUBSTITUTE_COMPONENTS bsc,
mtl_system_items_b msib
where jib1.pid = p_id
and jib1.component_item = V_COMPONENT_2.COMPONENT_ITEM
and jib2.pid = p_id
and jib1.replace_group = jib2.replace_group
and jib2.component_item <> V_COMPONENT_2.COMPONENT_ITEM
AND bsc.substitute_component_id = msib.inventory_item_id
and msib.organization_id = 118
AND NVL(BSC.ACD_TYPE,1) <> 3
and bsc.component_sequence_id =
V_COMPONENT_2.COMPONENT_SEQUENCE_ID
AND jib2.component_item = msib.segment1
AND JIB2.COMPONENT_QUANTITY <>
BSC.SUBSTITUTE_ITEM_QUANTITY;*/
COMMIT;
if V_COMPONENT_2.REF_NO is not null then
N_NUMBER := 0;
N_NUMBER1 := 0;
N_NUMBER2 := 0;
N_NUMBER3 := 1;
---指示符
SELECT (length(V_COMPONENT_2.REF_NO || ',') -
length(replace(V_COMPONENT_2.REF_NO || ',', ','))) / length(',')
INTO N_NUMBER
FROM DUAL;
V_CHAR_REF := null;
FOR N_NUMBER1 IN 1 .. N_NUMBER LOOP--------------------------------比较位标
SELECT INSTR(V_COMPONENT_2.REF_NO || ',', ',', 1, N_NUMBER1)
INTO N_NUMBER2
from DUAL;
SELECT SUBSTR(V_COMPONENT_2.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
insert into JW_PLM_COM_REF_TEMP(component_id,REF_CODE)
values (V_COMPONENT_2.COMPONENT_ID,V_CHAR_REF);
COMMIT;
end if;
END LOOP;
end if;
insert into JW_PLM_ECN_REFERENCE_T ----要添加的ref
(reference_id,
component_id,
assembly_id,
acd_type,
reference_code)
SELECT JW_PLM_ECN_REFERENCE_T_S.NEXTVAL,
V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
1,
CCC.ref_code
FROM (select t.ref_code
from JW_PLM_COM_REF_TEMP t
where t.component_id = V_COMPONENT_2.COMPONENT_ID
minus
select f.component_reference_designator
from BOM_REFERENCE_DESIGNATORS f
where nvl(f.acd_type,1) <> 3
and f.component_sequence_id =
V_COMPONENT_2.Component_Sequence_Id) CCC;
COMMIT;
insert into JW_PLM_ECN_REFERENCE_T --要禁用的ref
(reference_id,
component_id,
assembly_id,
acd_type,
reference_code)
SELECT JW_PLM_ECN_REFERENCE_T_S.NEXTVAL,
V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
3,
DDD.ref_code
FROM (select f.component_reference_designator ref_code
from BOM_REFERENCE_DESIGNATORS f
where nvl(f.acd_type,1) <> 3
and f.component_sequence_id =
V_COMPONENT_2.Component_Sequence_Id
minus
select t.ref_code
from JW_PLM_COM_REF_TEMP t
where t.component_id = V_COMPONENT_2.COMPONENT_ID
) DDD;
commit;
select count(*)
into n_num1
from JW_PLM_ECN_REFERENCE_T rt
where rt.component_id = V_COMPONENT_2.COMPONENT_ID;
select count(*)
into n_num2
from jw_plm_ecn_substitute_t st
where st.component_id = V_COMPONENT_2.COMPONENT_ID;
IF (V_COMPONENT_2.OLD_QTY <> V_COMPONENT_2.NEW_QTY) or (n_num1 > 0) or (n_num2 > 0) THEN --数量有变则更新
INSERT INTO jw_plm_ecn_COMPONENT_t(COMPONENT_id,
assembly_id,
acd_type,
COMPONENT_item,
qty,
COMPONENT_SEQUENCE_ID ,
operation_sequence_number
)
SELECT V_COMPONENT_2.COMPONENT_ID,
v_assembly_id,
2,
V_COMPONENT_2.COMPONENT_ITEM,
V_COMPONENT_2.NEW_QTY,
V_COMPONENT_2.COMPONENT_SEQUENCE_ID,
V_COMPONENT_2.OPERATION_SEQ_NUM
FROM DUAL;
COMMIT;
else --数量没有变化则查看替代料和指示符
null;
END IF;
END LOOP;
DBMS_OUTPUT.put_line('比较结束');
null;
end;
end JW_PLM_PROCESS_BOM_PKG;
1956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
