ECO 导入API
CREATE OR REPLACE PROCEDURE Eco_Test_Harry(p_Test_Tag IN 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_Orgai_Code VARCHAR2(10);
x_Eco_Rec Eng_Eco_Pub.Eco_Rec_Type;
x_Eco_Revision_Tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
x_Revised_Item_Tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
x_Rev_Component_Tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
x_Sub_Component_Tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
x_Ref_Designator_Tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
x_Rev_Operation_Tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type;
x_Rev_Op_Resource_Tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type;
x_Rev_Sub_Resource_Tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type;
l_Application_Id NUMBER;
l_Resp_Id NUMBER;
l_Resp_Name VARCHAR2(30) := 'ENGINEERING';
l_Return_Status VARCHAR2(1);
l_Msg_Count NUMBER;
l_Msg_Data VARCHAR2(2000);
--l_Error_Table Eng_Eco_Pub.Error_Tbl_Type;
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_Text VARCHAR2(2000);
CURSOR c_Eco_Rec IS
SELECT *
FROM Eng_Eng_Changes_Interface
WHERE Eng_Changes_Ifce_Key LIKE p_Test_Tag;
CURSOR c_Eco_Rev IS
SELECT *
FROM Eng_Eco_Revisions_Interface
WHERE Eng_Eco_Revisions_Ifce_Key LIKE p_Test_Tag;
CURSOR c_Rev_Items IS
SELECT *
FROM Eng_Revised_Items_Interface
WHERE Eng_Revised_Items_Ifce_Key LIKE p_Test_Tag;
CURSOR c_Rev_Comps IS
SELECT *
FROM Bom_Inventory_Comps_Interface
WHERE Bom_Inventory_Comps_Ifce_Key LIKE p_Test_Tag;
CURSOR c_Sub_Comps IS
SELECT *
FROM Bom_Sub_Comps_Interface
WHERE Bom_Sub_Comps_Ifce_Key LIKE p_Test_Tag;
CURSOR c_Ref_Desgs IS
SELECT *
FROM Bom_Ref_Desgs_Interface
WHERE Bom_Ref_Desgs_Ifce_Key LIKE p_Test_Tag;
i NUMBER;
BEGIN
-- 取当前用户登录的语言环境
SELECT Userenv('LANG')
INTO v_Lang
FROM Dual;
-- 依不同环境设定值
IF v_Lang = 'ZHS' THEN
v_Approval_Status_Name := '批准';
v_Status_Name := '打开';
ELSE
v_Approval_Status_Name := 'Approved';
v_Status_Name := 'Open';
END IF;
-- Query all the records and call the Private API.
FOR Eco_Rec IN c_Eco_Rec LOOP
l_Eco_Rec.Eco_Name := Eco_Rec.Change_Notice;
l_Eco_Rec.Organization_Code := Eco_Rec.Organization_Code;
l_Eco_Rec.Change_Type_Code := Eco_Rec.Change_Order_Type;
--l_eco_rec.status_type := eco_rec.status_type; --HARRY
l_Eco_Rec.Status_Name := Eco_Rec.Status_Name; --HARRY
-- l_eco_rec.eco_department_name := eco_rec.responsible_org_code;
l_Eco_Rec.Eco_Department_Name := Eco_Rec.Eco_Department_Name;
l_Eco_Rec.Priority_Code := Eco_Rec.Priority_Code;
l_Eco_Rec.Approval_List_Name := Eco_Rec.Approval_List_Name;
--l_eco_rec.approval_status_type := eco_rec.approval_status_type; --HARRY
l_Eco_Rec.Approval_Status_Name := Eco_Rec.Approval_Status_Name; --HARRY
l_Eco_Rec.Reason_Code := Eco_Rec.Reason_Code;
l_Eco_Rec.Eng_Implementation_Cost := Eco_Rec.Estimated_Eng_Cost;
l_Eco_Rec.Mfg_Implementation_Cost := Eco_Rec.Estimated_Mfg_Cost;
l_Eco_Rec.Cancellation_Comments := Eco_Rec.Cancellation_Comments;
--l_eco_rec.requestor := eco_rec.requestor; --HARRY
l_Eco_Rec.Requestor := Eco_Rec.Requestor_User_Name; --HARRY
-- l_eco_rec.requestor := eco_rec.requestor_id; --HARRY
l_Eco_Rec.Description := Eco_Rec.Description;
l_Eco_Rec.Transaction_Type := Eco_Rec.Transaction_Type;
l_Eco_Rec.Plm_Or_Erp_Change := 'ERP';
END LOOP;
-- Fetch ECO Revisions
i := 1;
FOR Rev IN c_Eco_Rev LOOP
l_Eco_Revision_Tbl(i).Eco_Name := Rev.Change_Notice;
l_Eco_Revision_Tbl(i).Organization_Code := Rev.Organization_Code;
l_Eco_Revision_Tbl(i).Revision := Rev.Revision;
l_Eco_Revision_Tbl(i).New_Revision := Rev.New_Revision;
l_Eco_Revision_Tbl(i).Comments := Rev.Comments;
l_Eco_Revision_Tbl(i).Transaction_Type := Rev.Transaction_Type;
i := i + 1;
END LOOP;
-- Fetch revised items
i := 1;
FOR Ri IN c_Rev_Items LOOP
l_Revised_Item_Tbl(i).Eco_Name := Ri.Change_Notice;
l_Revised_Item_Tbl(i).Organization_Code := Ri.Organization_Code;
l_Revised_Item_Tbl(i).Revised_Item_Name := Ri.Revised_Item_Number;
IF Ri.New_Item_Revision = Fnd_Api.g_Miss_Char THEN
l_Revised_Item_Tbl(i).New_Revised_Item_Revision := NULL;
ELSE
l_Revised_Item_Tbl(i).New_Revised_Item_Revision := Ri.New_Item_Revision;
END IF;
l_Revised_Item_Tbl(i).Start_Effective_Date := Ri.Scheduled_Date;
l_Revised_Item_Tbl(i).Alternate_Bom_Code := Ri.Alternate_Bom_Designator;
l_Revised_Item_Tbl(i).Status_Type := Ri.Status_Type;
l_Revised_Item_Tbl(i).Mrp_Active := Ri.Mrp_Active;
l_Revised_Item_Tbl(i).Earliest_Effective_Date := Ri.Early_Schedule_Date;
l_Revised_Item_Tbl(i).Use_Up_Item_Name := Ri.Use_Up_Item_Number;
l_Revised_Item_Tbl(i).Use_Up_Plan_Name := Ri.Use_Up_Plan_Name;
l_Revised_Item_Tbl(i).Disposition_Type := Ri.Disposition_Type;
l_Revised_Item_Tbl(i).Update_Wip := Ri.Update_Wip;
l_Revised_Item_Tbl(i).Cancel_Comments := Ri.Cancel_Comments;
l_Revised_Item_Tbl(i).Change_Description := Ri.Descriptive_Text;
l_Revised_Item_Tbl(i).Transaction_Type := Ri.Transaction_Type;
i := i + 1;
END LOOP;
-- Fetch revised components
i := 1;
FOR Rc IN c_Rev_Comps LOOP
l_Rev_Component_Tbl(i).Eco_Name := Rc.Change_Notice;
l_Rev_Component_Tbl(i).Organization_Code := Rc.Organization_Code;
l_Rev_Component_Tbl(i).Revised_Item_Name := Rc.Assembly_Item_Number;
l_Rev_Component_Tbl(i).New_Revised_Item_Revision := NULL;
l_Rev_Component_Tbl(i).Start_Effective_Date := Rc.Effectivity_Date;
l_Rev_Component_Tbl(i).Disable_Date := Rc.Disable_Date;
l_Rev_Component_Tbl(i).Operation_Sequence_Number := Rc.Operation_Seq_Num;
l_Rev_Component_Tbl(i).Component_Item_Name := Rc.Component_Item_Number;
l_Rev_Component_Tbl(i).Alternate_Bom_Code := Rc.Alternate_Bom_Designator;
l_Rev_Component_Tbl(i).Acd_Type := Rc.Acd_Type;
l_Rev_Component_Tbl(i).Old_Effectivity_Date := Rc.Old_Effectivity_Date;
l_Rev_Component_Tbl(i).Old_Operation_Sequence_Number := Rc.Old_Operation_Seq_Num;
l_Rev_Component_Tbl(i).Item_Sequence_Number := Rc.Item_Num;
l_Rev_Component_Tbl(i).Quantity_Per_Assembly := Rc.Component_Quantity;
l_Rev_Component_Tbl(i).Planning_Percent := Rc.Planning_Factor;
l_Rev_Component_Tbl(i).Projected_Yield := Rc.Component_Yield_Factor;
l_Rev_Component_Tbl(i).Include_In_Cost_Rollup := Rc.Include_In_Cost_Rollup;
l_Rev_Component_Tbl(i).Wip_Supply_Type := Rc.Wip_Supply_Type;
l_Rev_Component_Tbl(i).So_Basis := Rc.So_Basis;
l_Rev_Component_Tbl(i).Optional := Rc.Optional;
l_Rev_Component_Tbl(i).Mutually_Exclusive := Rc.Mutually_Exclusive_Options;
l_Rev_Component_Tbl(i).Check_Atp := Rc.Check_Atp;
l_Rev_Component_Tbl(i).Shipping_Allowed := Rc.Shipping_Allowed;
l_Rev_Component_Tbl(i).Required_To_Ship := Rc.Required_To_Ship;
l_Rev_Component_Tbl(i).Required_For_Revenue := Rc.Required_For_Revenue;
l_Rev_Component_Tbl(i).Include_On_Ship_Docs := Rc.Include_On_Ship_Docs;
l_Rev_Component_Tbl(i).Quantity_Related := Rc.Quantity_Related;
l_Rev_Component_Tbl(i).Supply_Subinventory := Rc.Supply_Subinventory;
l_Rev_Component_Tbl(i).Location_Name := Rc.Location_Name;
l_Rev_Component_Tbl(i).Minimum_Allowed_Quantity := Rc.Low_Quantity;
l_Rev_Component_Tbl(i).Maximum_Allowed_Quantity := Rc.High_Quantity;
--l_rev_component_tbl(i).component_remarks :=
--rc.component_remarks; --HARRY
l_Rev_Component_Tbl(i).Comments := Rc.Component_Remarks; --HARRY
l_Rev_Component_Tbl(i).Transaction_Type := Rc.Transaction_Type;
i := i + 1;
END LOOP;
-- Fetch substitute component records
i := 1;
FOR Sc IN c_Sub_Comps LOOP
l_Sub_Component_Tbl(i).Eco_Name := Sc.Change_Notice;
l_Sub_Component_Tbl(i).Organization_Code := Sc.Organization_Code;
l_Sub_Component_Tbl(i).Revised_Item_Name := Sc.Assembly_Item_Number;
l_Sub_Component_Tbl(i).Start_Effective_Date := Sc.Effectivity_Date;
l_Sub_Component_Tbl(i).New_Revised_Item_Revision := NULL;
l_Sub_Component_Tbl(i).Component_Item_Name := Sc.Component_Item_Number;
l_Sub_Component_Tbl(i).Alternate_Bom_Code := Sc.Alternate_Bom_Designator;
l_Sub_Component_Tbl(i).Substitute_Component_Name := Sc.Substitute_Comp_Number;
l_Sub_Component_Tbl(i).Acd_Type := Sc.Acd_Type;
l_Sub_Component_Tbl(i).Operation_Sequence_Number := Sc.Operation_Seq_Num;
l_Sub_Component_Tbl(i).Substitute_Item_Quantity := Sc.Substitute_Item_Quantity;
l_Sub_Component_Tbl(i).Transaction_Type := Sc.Transaction_Type;
i := i + 1;
END LOOP;
-- Fetch reference designators
i := 1;
FOR Rd IN c_Ref_Desgs LOOP
l_Ref_Designator_Tbl(i).Eco_Name := Rd.Change_Notice;
l_Ref_Designator_Tbl(i).Organization_Code := Rd.Organization_Code;
l_Ref_Designator_Tbl(i).Revised_Item_Name := Rd.Assembly_Item_Number;
l_Ref_Designator_Tbl(i).Start_Effective_Date := Rd.Effectivity_Date;
l_Ref_Designator_Tbl(i).New_Revised_Item_Revision := NULL;
l_Ref_Designator_Tbl(i).Operation_Sequence_Number := Rd.Operation_Seq_Num;
l_Ref_Designator_Tbl(i).Component_Item_Name := Rd.Component_Item_Number;
l_Ref_Designator_Tbl(i).Alternate_Bom_Code := Rd.Alternate_Bom_Designator;
l_Ref_Designator_Tbl(i).Reference_Designator_Name := Rd.Component_Reference_Designator;
l_Ref_Designator_Tbl(i).Acd_Type := Rd.Acd_Type;
l_Ref_Designator_Tbl(i).Ref_Designator_Comment := Rd.Ref_Designator_Comment;
l_Ref_Designator_Tbl(i).New_Reference_Designator := Rd.New_Designator;
l_Ref_Designator_Tbl(i).Transaction_Type := Rd.Transaction_Type;
END LOOP;
SELECT Application_Id
,Responsibility_Id
INTO l_Application_Id
,l_Resp_Id
FROM Fnd_Responsibility
WHERE Responsibility_Key = l_Resp_Name;
Fnd_Global.Apps_Initialize(User_Id => Fnd_Global.User_Id
,Resp_Id => l_Resp_Id
,Resp_Appl_Id => l_Application_Id);
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);
--
-- On return from the PUB API
-- Perform all the error handler operations to verify that the
-- error or warning are displayed and all the error table interface
-- function provided to the user work correctly;
--
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 Eco_Test_Harry;
787
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
