CREATE OR REPLACE Package Body Cux_Pdm_Erp_Sync_Pkg Is
Procedure Debug_Show(d_Message In Varchar2) Is
Begin
If Cux_Tool_Pkg.Err_Show('CAPP_SYNC') Then
Dbms_Output.Put_Line(d_Message);
End If;
End Debug_Show;
Procedure Insert_Log(p_Transaction_Id In Number,
p_Entity_Type In Varchar2,
p_Entity_Name In Varchar2,
p_Transaction_Type In Varchar2,
p_Transaction_Date In Date,
p_Return_Status In Varchar2,
p_Return_Message In Varchar2) Is
Begin
Null;
End;
/* Procedure Insert_Log(p_Transaction_Id In Number,
p_Entity_Type In Varchar2,
p_Entity_Name In Varchar2,
p_Transaction_Type In Varchar2,
p_Transaction_Date In Date,
p_Return_Status In Varchar2,
p_Return_Message In Varchar2) Is
Pragma Autonomous_Transaction;
Begin
Insert Into Cux.Cux_Pdm_Erp_Sync_Log
(Transaction_Id,
Entity_Type,
Entity_Name,
Transaction_Type,
Transaction_Date,
Return_Status,
Return_Message)
Values
(p_Transaction_Id,
p_Entity_Type,
p_Entity_Name,
p_Transaction_Type,
p_Transaction_Date,
p_Return_Status,
Substr(p_Return_Message, 1, 2000));
Commit;
End Insert_Log;*/
---------------------------------------------------------------------------------
-- p_delete_type : 1 bom ; 4 component ; 3 rtg ; 5 operation
Procedure Delete_Group_Api(p_Organization_Code In Varchar2,
p_Delete_Group_Name In Varchar2,
p_Delete_Type In Number) Is
Lx_Errbuf Varchar2(100);
Lx_Retcode Varchar2(100);
l_Delete_Group_Id Number;
v_Req_Id Number;
Begin
Select Dg.Delete_Group_Sequence_Id
Into l_Delete_Group_Id
From Bom_Delete_Groups Dg, Mtl_Parameters Mp
Where Dg.Delete_Group_Name = p_Delete_Group_Name
And Mp.Organization_Id = Dg.Organization_Id
And Mp.Organization_Code = p_Organization_Code;
Debug_Show('-->' || p_Organization_Code || ',' || p_Delete_Group_Name || ',' ||
l_Delete_Group_Id || ',' || p_Delete_Type);
/*Bom_Delete_Groups_Api.Delete_Groups(Errbuf => Lx_Errbuf,
Retcode => Lx_Retcode,
Delete_Group_Id => l_Delete_Group_Id,
Action_Type => 1,
Delete_Type => p_Delete_Type, ---BOM_COMPONENT_DELETED
Archive => 2);
Dbms_Output.Put_Line('Retcode:' || Lx_Retcode);*/
Bom_Delete_Groups_Api.Delete_Groups(Errbuf => Lx_Errbuf,
Retcode => Lx_Retcode,
Delete_Group_Id => l_Delete_Group_Id,
Action_Type => 2,
Delete_Type => p_Delete_Type, ---BOM_COMPONENT_DELETED
Archive => 2);
Debug_Show('Retcode:' || Lx_Retcode);
/* v_Req_Id := Fnd_Request.Submit_Request('BOM',
'BMCDEL',
'',
To_Char(Sysdate,
'yyyy/mm/dd hh24:mi:ss'),
False,
Chr(0));
Debug_Show('v_Req_Id:' || v_Req_Id);*/
End Delete_Group_Api;
---------------------------------------------------------------------------------
Procedure Item_Sync_Category_Proc(d_Category_Rec In Category_Type,
d_Item_Rec In Inv_Item_Grp.Item_Rec_Type,
x_Status Out Varchar2,
x_Status_Code Out Varchar2) Is
x_Return_Status Varchar2(1);
x_Msg_Count Number;
x_Msg_Data Varchar2(2000);
x_Errorcode Varchar2(30);
p_Old_Category_Id Number;
Begin
Begin
Select Micv.Category_Id
Into p_Old_Category_Id
From Mtl_Item_Categories_v Micv
Where Micv.Inventory_Item_Id = d_Item_Rec.Inventory_Item_Id
And Micv.Organization_Id = d_Item_Rec.Organization_Id
And Micv.Category_Set_Id = d_Category_Rec.Category_Set_Id;
End;
Debug_Show('-->>cate_set_id:' || d_Category_Rec.Category_Set_Id);
Debug_Show('-->>p_Old_Category_Id:' || p_Old_Category_Id);
Debug_Show('-->>cate_id:' || d_Category_Rec.Category_Id);
Debug_Show('-->>Inventory_Item_Id:' || d_Item_Rec.Inventory_Item_Id);
Debug_Show('-->>Organization_Id:' || d_Item_Rec.Organization_Id);
Inv_Item_Category_Pub.Update_Category_Assignment(p_Api_Version => '1.0',
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_Old_Category_Id => p_Old_Category_Id,
p_Category_Id => d_Category_Rec.Category_Id, --913,
p_Category_Set_Id => d_Category_Rec.Category_Set_Id, --1 Inventory
p_Inventory_Item_Id => d_Item_Rec.Inventory_Item_Id, --5159,
p_Organization_Id => d_Item_Rec.Organization_Id);
If x_Return_Status <> Fnd_Api.g_Ret_Sts_Success Then
x_Status := 'E';
x_Status_Code := '创建物料分类，出现异常：';
--ROLLBACK;
Debug_Show('error code : ' || x_Errorcode);
Fnd_Msg_Pub.Count_And_Get(p_Count => x_Msg_Count,
p_Data => x_Msg_Data);
Debug_Show('error count : ' || To_Char(x_Msg_Count));
Debug_Show(Replace(x_Msg_Data, Chr(0), ' '));
For i In 2 .. x_Msg_Count Loop
x_Msg_Data := Fnd_Msg_Pub.Get;
Debug_Show(Replace(x_Msg_Data, Chr(0), ' '));
x_Status_Code := x_Status_Code || x_Msg_Data;
End Loop;
If (x_Return_Status = Fnd_Api.g_Ret_Sts_Unexp_Error) Then
Raise Fnd_Api.g_Exc_Unexpected_Error;
Elsif (x_Return_Status = Fnd_Api.g_Ret_Sts_Error) Then
Raise Fnd_Api.g_Exc_Error;
End If;
Else
--COMMIT;
Debug_Show('Successfully.');
x_Status := 'S';
End If;
End Item_Sync_Category_Proc;
--*******************************************************************************
-- 哈机电
-- 程序名称 ： Catalog_Item_Maintain
-- 中文名称 ： 维护物料的目录组名称
-- 功能概要 ： 维护物料的目录组名称（全组织）
-- 版本 ： V 1.1
-- CUX_STMT_UPLOAD
-- 程序员&创建日期 ： lwz ＆ 2011/11/11
--********************************************************************************
Procedure Catalog_Item_Maintain(d_Inventory_Item_Id In Number,
d_Segment1 In Varchar2,
d_Catalog_Group_Id In Number,
--d_Transaction_Type In Varchar2,
x_Inventory_Item_Id Out Number,
x_Status Out Varchar2,
x_Status_Code Out Varchar2) Is
p_Item_Rec Inv_Item_Grp.Item_Rec_Type;
x_Return_Status Varchar2(200);
x_Msg_Data Varchar2(1000);
x_Error_Tbl Inv_Item_Grp.Error_Tbl_Type;
x_Item_Rec Inv_Item_Grp.Item_Rec_Type;
Cursor Cur_Items Is
Select Decode(Msib.Organization_Id,
Cux_Tool_Pkg.Get_Main_Organization_Id,
1,
2),
Msib.Segment1,
Msib.Inventory_Item_Id,
Msib.Organization_Id
From Mtl_System_Items_b Msib
Where Msib.Inventory_Item_Id =
Nvl(d_Inventory_Item_Id, Msib.Inventory_Item_Id)
And Msib.Segment1 = Nvl(d_Segment1, Msib.Segment1)
Order By Decode(Msib.Organization_Id,
Cux_Tool_Pkg.Get_Main_Organization_Id,
1,
2),
Msib.Organization_Id;
Begin
For c_Item In Cur_Items Loop
p_Item_Rec.Inventory_Item_Id := c_Item.Inventory_Item_Id;
p_Item_Rec.Organization_Id := c_Item.Organization_Id;
p_Item_Rec.Item_Catalog_Group_Id := d_Catalog_Group_Id; ---999999;--;--INV_ITEM_GRP.g_MISS_NUM;--8;--
Inv_Item_Grp.Update_Item(p_Template_Name => Null, --p_Template_Name,
p_Item_Rec => p_Item_Rec,
x_Item_Rec => x_Item_Rec,
x_Return_Status => x_Return_Status,
x_Error_Tbl => x_Error_Tbl);
-- COMMIT;
x_Status := x_Return_Status;
x_Inventory_Item_Id := c_Item.Inventory_Item_Id;
If x_Return_Status <> 'S' Then
x_Status_Code := '物料编码：' || '''' || c_Item.Segment1 || '''' ||
'在组织：' || c_Item.Organization_Id ||
'中，更新物料目录组名称时出现异常，异常代码为';
For i In 1 .. x_Error_Tbl.Count Loop
x_Msg_Data := x_Msg_Data || To_Char(i) || '):' || x_Error_Tbl(i)
.Message_Text;
--Debug_Show(x_Msg_Data);
End Loop;
x_Status_Code := x_Status_Code || x_Msg_Data;
Return;
End If;
End Loop;
Exception
When Others Then
x_Status := 'E';
x_Status_Code := '维护物料的目录组名称，出现异常：' || Sqlerrm;
End Catalog_Item_Maintain;
--*******************************************************************************
-- 哈机电
-- 程序名称 ： Catalog_descr_Maintain
-- 中文名称 ： 目录组内容维护
-- 功能概要 ： 目录组内容维护
-- 版本 ： V 1.1
-- CUX_STMT_UPLOAD
-- 程序员&创建日期 ： lwz ＆ 2011/11/11
--********************************************************************************
Procedure Catalog_Descr_Maintain(d_Inventory_Item_Id In Number,
d_Segment1 In Varchar2,
d_Item_Desc_Element_Table In Inv_Item_Catalog_Elem_Pub.Item_Desc_Element_Table,
x_Status Out Varchar2,
x_Status_Code Out Varchar2) Is
p_Api_Version Number;
p_Init_Msg_List Varchar2(200) Default Fnd_Api.g_False;
p_Commit_Flag Varchar2(200) Default Fnd_Api.g_False;
p_Validation_Level Number Default Inv_Item_Catalog_Elem_Pub.g_Validate_All;
p_Inventory_Item_Id Number Default - 999;
p_Item_Number Varchar2(200) Default Null;
p_Item_Desc_Element_Table Inv_Item_Catalog_Elem_Pub.Item_Desc_Element_Table;
x_Generated_Descr Varchar2(200);
x_Return_Status Varchar2(200);
x_Msg_Count Number;
x_Msg_Data Varchar2(200);
Begin
If d_Inventory_Item_Id Is Not Null Then
p_Inventory_Item_Id := d_Inventory_Item_Id;
End If;
If d_Segment1 Is Not Null Then
p_Item_Number := d_Segment1;
End If;
p_Api_Version := 1.0;
p_Init_Msg_List := Fnd_Api.g_True;
p_Validation_Level := Inv_Item_Catalog_Elem_Pub.g_Validate_None;
p_Item_Desc_Element_Table := d_Item_Desc_Element_Table;
Inv_Item_Catalog_Elem_Pub.Process_Item_Descr_Elements(p_Api_Version => p_Api_Version,
p_Init_Msg_List => p_Init_Msg_List,
p_Commit_Flag => p_Commit_Flag,
p_Validation_Level => p_Validation_Level,
p_Inventory_Item_Id => p_Inventory_Item_Id,
p_Item_Number => p_Item_Number,
p_Item_Desc_Element_Table => p_Item_Desc_Element_Table,
x_Generated_Descr => x_Generated_Descr,
x_Return_Status => x_Return_Status,
x_Msg_Count => x_Msg_Count,
x_Msg_Data => x_Msg_Data);
x_Status := x_Return_Status;
If x_Return_Status <> 'S' Then
x_Status_Code := '更新目录组信息时出现异常，错误数量为：' || x_Msg_Count || '，异常代码为：' ||
x_Msg_Data;
Return;
End If;
Exception
When Others Then
x_Status := 'E';
x_Status_Code := '目录组内容维护，出现异常：' || Sqlerrm;
End Catalog_Descr_Maintain;
--*******************************************************************************
-- 哈机电
-- 程序名称 ： Catalog_Maintain
-- 中文名称 ： 目录组维护
-- 功能概要 ： 目录组维护，新建和更改
-- 版本 ： V 1.1
-- CUX_STMT_UPLOAD
-- 程序员&创建日期 ： lwz ＆ 2011/11/11
--********************************************************************************
Procedure Catalog_Maintain(d_Group_Name In Varchar2,
d_Header_Id In Varchar2,
x_Status Out Varchar2,
x_Status_Code Out Varchar2) Is
p_Inventory_Item_Id Number Default - 999;
p_Item_Desc_Element_Table Inv_Item_Catalog_Elem_Pub.Item_Desc_Element_Table;
p_Item_Desc_Element Inv_Item_Catalog_Elem_Pub.Item_Desc_Element;
p_Item_Desc_Element_New Inv_Item_Catalog_Elem_Pub.Item_Desc_Element;
p_Count Number;
p_Item_Rec Inv_Item_Grp.Item_Rec_Type;
--x_Return_Status Varchar2(200);
--x_Msg_Data Varchar2(1000);
x_Error_Tbl Inv_Item_Grp.Error_Tbl_Type;
x_Item_Rec Inv_Item_Grp.Item_Rec_Type;
--p_inventory_item_id Number;
Cursor Cur_Catalog Is
Select Ccht.Header_Id,
Ccht.Item_Catalog_Group_Id,
Ccht.Inventory_Item_Id,
Ccht.Segment1
From Cux_Catalog_Headers_Temp Ccht
Where Ccht.Group_Name = d_Group_Name
And Ccht.Header_Id = d_Header_Id
And Nvl(Ccht.Status, 'C') != 'S' --C = CREATE
Order By Header_Id;
Cursor Cur_Elements(c_Header_Id In Number) Is
Select Cclt.Element_Name, Cclt.Element_Value
From Cux_Catalog_Lines_Temp Cclt
Where Cclt.Group_Name = d_Group_Name
And Cclt.Header_Id = c_Header_Id
Order By Line_Id;
Begin
For c_Catalog In Cur_Catalog Loop
--更新物料数据
Catalog_Item_Maintain(d_Inventory_Item_Id => c_Catalog.Inventory_Item_Id,
d_Segment1 => c_Catalog.Segment1,
d_Catalog_Group_Id => c_Catalog.Item_Catalog_Group_Id,
--d_Transaction_Type In Varchar2,
x_Inventory_Item_Id => p_Inventory_Item_Id,
x_Status => x_Status,
x_Status_Code => x_Status_Code);
-- COMMIT;
If x_Status <> 'S' Then
--Return; --一般只有一个物料，只做一次循环，所以写不写return都可以，此处是 为了防止出现多个重复行
Null;
Else
--添加目录组信息
p_Count := 0;
For c_Element In Cur_Elements(c_Catalog.Header_Id) Loop
p_Count := p_Count + 1;
p_Item_Desc_Element := p_Item_Desc_Element_New;
p_Item_Desc_Element.Element_Name := c_Element.Element_Name;
p_Item_Desc_Element.Element_Value := c_Element.Element_Value;
p_Item_Desc_Element_Table(p_Count) := p_Item_Desc_Element;
End Loop;
Catalog_Descr_Maintain(d_Inventory_Item_Id => p_Inventory_Item_Id, --c_Catalog.Inventory_Item_Id,
d_Segment1 => c_Catalog.Segment1,
d_Item_Desc_Element_Table => p_Item_Desc_Element_Table,
x_Status => x_Status,
x_Status_Code => x_Status_Code);
If x_Status <> 'S' Then
--Return; --一般只有一个物料，只做一次循环，所以写不写return都可以，此处是 为了防止出现多个重复行
Null;
End If;
End If;
Update Cux_Catalog_Headers_Temp Ccht
Set Ccht.Status = x_Status, Ccht.Status_Code = x_Status_Code
Where Ccht.Header_Id = c_Catalog.Header_Id
And Ccht.Group_Name = d_Group_Name;
End Loop;
Exception
When Others Then
x_Status := 'E';
x_Status_Code := '目录组维护，出现异常：' || Sqlerrm;
End Catalog_Maintain;
Procedure Item_Sync_Proc(p_Transaction_Type In Varchar2,
p_Template_Name In Varchar2,
p_Category_Rec In Category_Type,
p_Item_Rec In Inv_Item_Grp.Item_Rec_Type,
x_Item_Id Out Number,
p_Return_Status Out Varchar2,
p_Return_Msg Out Varchar2) Is
x_Return_Status Varchar2(200);
x_Msg_Data Varchar2(1000);
x_Error_Tbl Inv_Item_Grp.Error_Tbl_Type;
x_Item_Rec Inv_Item_Grp.Item_Rec_Type;
-- p_master_exists_count NUMBER; -- ????????????
-- p_exists_count NUMBER; -- ?????????????
-- p_org_id NUMBER;
-- p_temp_count NUMBER;
-- i NUMBER;
Begin
--p_org_id := p_item_rec.organization_id;
If p_Transaction_Type = 'CREATE' Then
Inv_Item_Grp.Create_Item(p_Template_Name => p_Template_Name,
p_Item_Rec => p_Item_Rec,
x_Item_Rec => x_Item_Rec,
x_Return_Status => x_Return_Status,
x_Error_Tbl => x_Error_Tbl);
x_Item_Id := x_Item_Rec.Inventory_Item_Id;
-- COMMIT;
If x_Return_Status <> 'S' Then
For i In 1 .. x_Error_Tbl.Count Loop
x_Msg_Data := x_Msg_Data || To_Char(i) || '):' || x_Error_Tbl(i)
.Message_Text;
End Loop;
Else
Debug_Show('-->>test:' || p_Category_Rec.Category_Id);
If p_Category_Rec.Category_Id Is Not Null Then
Item_Sync_Category_Proc(d_Category_Rec => p_Category_Rec,
d_Item_Rec => x_Item_Rec,
x_Status => x_Return_Status,
x_Status_Code => x_Msg_Data);
End If;
End If;
Elsif p_Transaction_Type = 'UPDATE' Then
Inv_Item_Grp.Update_Item(p_Template_Name => p_Template_Name,
p_Item_Rec => p_Item_Rec,
x_Item_Rec => x_Item_Rec,
x_Return_Status => x_Return_Status,
x_Error_Tbl => x_Error_Tbl);
-- COMMIT;
If x_Return_Status <> 'S' Then
For i In 1 .. x_Error_Tbl.Count Loop
x_Msg_Data := x_Msg_Data || To_Char(i) || '):' || x_Error_Tbl(i)
.Message_Text;
End Loop;
End If;
End If;
p_Return_Status := x_Return_Status;
p_Return_Msg := x_Msg_Data;
Insert_Log(p_Transaction_Id => 1,
p_Entity_Type => 'ITEM',
p_Entity_Name => p_Item_Rec.Segment1,
p_Transaction_Type => p_Transaction_Type,
p_Transaction_Date => Sysdate,
p_Return_Status => p_Return_Status,
p_Return_Message => p_Return_Msg);
End Item_Sync_Proc;
-----------------------------------------------------------------------------------------
Procedure Bom_Sync_Proc --
(p_Transaction_Id In Number,
p_Bom_Header_Rec In Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.g_Miss_Bom_Header_Rec,
p_Bom_Revision_Tbl In Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.g_Miss_Bom_Revision_Tbl,
p_Bom_Component_Tbl In Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.g_Miss_Bom_Component_Tbl,
p_Bom_Ref_Designator_Tbl In Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type := Bom_Bo_Pub.g_Miss_Bom_Ref_Designator_Tbl,
p_Bom_Sub_Component_Tbl In Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.g_Miss_Bom_Sub_Component_Tbl,
x_Return_Status Out Varchar2,
x_Return_Message Out Varchar2) Is
--Pragma Autonomous_Transaction;
--?? delete_group_api?????????commit ???????? ??????pdm??????
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;
l_Bom_Comp_Ops_Tbl Bom_Bo_Pub.Bom_Comp_Ops_Tbl_Type;
l_Return_Status Varchar2(1);
l_Msg_Count Number;
l_Mesg_List Error_Handler.Error_Tbl_Type;
t Varchar2(32000);
Msg_Start Varchar2(20);
p_Organization_Code Varchar2(20);
l_Error_Message_List Error_Handler.Error_Tbl_Type;
Lx_Bom_Header_Rec Bom_Bo_Pub.Bom_Head_Rec_Type;
Lx_Bom_Revision_Tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
Lx_Bom_Component_Tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type;
Lx_Bom_Ref_Designator_Tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
Lx_Bom_Sub_Component_Tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
Lx_Return_Status Varchar2(1);
Lx_Msg_Count Number;
l_Delete_Type Number;
l_Delete_Group_Name Varchar2(100);
Begin
l_Bom_Header_Rec.Assembly_Item_Name := p_Bom_Header_Rec.Assembly_Item_Name;
l_Bom_Header_Rec.Organization_Code := p_Bom_Header_Rec.Organization_Code;
l_Bom_Header_Rec.Alternate_Bom_Code := p_Bom_Header_Rec.Alternate_Bom_Code;
l_Bom_Header_Rec.Assembly_Type := p_Bom_Header_Rec.Assembly_Type;
l_Bom_Header_Rec.Transaction_Type := p_Bom_Header_Rec.Transaction_Type; --'CREATE'
l_Bom_Header_Rec.Delete_Group_Name := p_Bom_Header_Rec.Delete_Group_Name;
l_Bom_Header_Rec.Return_Status := Null;
p_Organization_Code := p_Bom_Header_Rec.Organization_Code;
If l_Bom_Header_Rec.Transaction_Type = 'DELETE' Then
l_Delete_Type := 1;
l_Delete_Group_Name := l_Bom_Header_Rec.Delete_Group_Name;
End If;
For i In 1 .. p_Bom_Component_Tbl.Count Loop
l_Bom_Component_Tbl(i).Organization_Code := l_Bom_Header_Rec.Organization_Code;
l_Bom_Component_Tbl(i).Assembly_Item_Name := l_Bom_Header_Rec.Assembly_Item_Name;
l_Bom_Component_Tbl(i).Alternate_Bom_Code := l_Bom_Header_Rec.Alternate_Bom_Code;
l_Bom_Component_Tbl(i).Delete_Group_Name := p_Bom_Component_Tbl(i)
.Delete_Group_Name;
l_Bom_Component_Tbl(i).Item_Sequence_Number := p_Bom_Component_Tbl(i)
.Item_Sequence_Number; --i * 10;
l_Bom_Component_Tbl(i).Operation_Sequence_Number := p_Bom_Component_Tbl(i)
.Operation_Sequence_Number;
l_Bom_Component_Tbl(i).Transaction_Type := p_Bom_Component_Tbl(i)
.Transaction_Type; -- 'CREATE';
l_Bom_Component_Tbl(i).Quantity_Per_Assembly := p_Bom_Component_Tbl(i)
.Quantity_Per_Assembly;
l_Bom_Component_Tbl(i).Start_Effective_Date := p_Bom_Component_Tbl(i)
.Start_Effective_Date; --to_date(TO_CHAR(p_Bom_Component_Tbl(i).Start_Effective_Date,'YYYY-MM-DD hh24:mi:ss'),'DD-MM-YYYY hh24:mi:ss');
l_Bom_Component_Tbl(i).Disable_Date := p_Bom_Component_Tbl(i)
.Disable_Date;
l_Bom_Component_Tbl(i).Component_Item_Name := p_Bom_Component_Tbl(i)
.Component_Item_Name;
l_Bom_Component_Tbl(i).Alternate_Bom_Code := p_Bom_Component_Tbl(i)
.Alternate_Bom_Code;
l_Bom_Component_Tbl(i).Projected_Yield := p_Bom_Component_Tbl(i)
.Projected_Yield;
l_Bom_Component_Tbl(i).Planning_Percent := p_Bom_Component_Tbl(i)
.Planning_Percent;
l_Bom_Component_Tbl(i).Quantity_Related := p_Bom_Component_Tbl(i)
.Quantity_Related;
l_Bom_Component_Tbl(i).Check_Atp := p_Bom_Component_Tbl(i).Check_Atp;
l_Bom_Component_Tbl(i).Include_In_Cost_Rollup := p_Bom_Component_Tbl(i)
.Include_In_Cost_Rollup;
l_Bom_Component_Tbl(i).Wip_Supply_Type := p_Bom_Component_Tbl(i)
.Wip_Supply_Type;
l_Bom_Component_Tbl(i).So_Basis := p_Bom_Component_Tbl(i).So_Basis;
l_Bom_Component_Tbl(i).Optional := p_Bom_Component_Tbl(i).Optional;
l_Bom_Component_Tbl(i).Mutually_Exclusive := p_Bom_Component_Tbl(i)
.Mutually_Exclusive;
l_Bom_Component_Tbl(i).Shipping_Allowed := p_Bom_Component_Tbl(i)
.Shipping_Allowed;
l_Bom_Component_Tbl(i).Required_To_Ship := p_Bom_Component_Tbl(i)
.Required_To_Ship;
l_Bom_Component_Tbl(i).Required_For_Revenue := p_Bom_Component_Tbl(i)
.Required_For_Revenue;
l_Bom_Component_Tbl(i).Include_On_Ship_Docs := p_Bom_Component_Tbl(i)
.Include_On_Ship_Docs;
l_Bom_Component_Tbl(i).Supply_Subinventory := p_Bom_Component_Tbl(i)
.Supply_Subinventory;
l_Bom_Component_Tbl(i).Location_Name := p_Bom_Component_Tbl(i)
.Location_Name;
l_Bom_Component_Tbl(i).Minimum_Allowed_Quantity := p_Bom_Component_Tbl(i)
.Minimum_Allowed_Quantity;
l_Bom_Component_Tbl(i).Maximum_Allowed_Quantity := p_Bom_Component_Tbl(i)
.Maximum_Allowed_Quantity;
l_Bom_Component_Tbl(i).Comments := p_Bom_Component_Tbl(i).Comments;
l_Bom_Component_Tbl(i).From_End_Item_Unit_Number := p_Bom_Component_Tbl(i)
.From_End_Item_Unit_Number;
l_Bom_Component_Tbl(i).To_End_Item_Unit_Number := p_Bom_Component_Tbl(i)
.To_End_Item_Unit_Number;
l_Bom_Component_Tbl(i).Return_Status := Null;
If l_Bom_Component_Tbl(i)
.Transaction_Type = 'DELETE' And l_Delete_Type Is Null Then
l_Delete_Type := 4;
l_Delete_Group_Name := l_Bom_Component_Tbl(i).Delete_Group_Name;
End If;
End Loop;
If l_Bom_Header_Rec.Transaction_Type = 'MAINTAIN_LINE' Then
l_Bom_Header_Rec := Bom_Bo_Pub.g_Miss_Bom_Header_Rec;
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,
p_Bom_Comp_Ops_Tbl => l_Bom_Comp_Ops_Tbl,
x_Bom_Header_Rec => l_Bom_Header_Rec,
x_Bom_Revision_Tbl => l_Bom_Revision_Tbl,
x_Bom_Component_Tbl => l_Bom_Component_Tbl,
x_Bom_Ref_Designator_Tbl => l_Bom_Ref_Designator_Tbl,
x_Bom_Sub_Component_Tbl => l_Bom_Sub_Component_Tbl,
x_Bom_Comp_Ops_Tbl => l_Bom_Comp_Ops_Tbl,
x_Return_Status => l_Return_Status,
x_Msg_Count => l_Msg_Count,
p_Debug => 'N', --'&DEBUG',
p_Output_Dir => Null, --'&DEBUG_DIR',
p_Debug_Filename => Null --'&USER_NAME' ||'.debug'
);
If (l_Return_Status = 'S') Then
Debug_Show('Return Status : ' || l_Return_Status || ' - Success!');
x_Return_Status := 'S';
If l_Delete_Type > 0 Then
Delete_Group_Api(p_Organization_Code,
l_Delete_Group_Name,
l_Delete_Type);
End If;
x_Return_Status := 'S';
Else
Debug_Show('Return Status : ' || l_Return_Status || ' - Failure!');
x_Return_Status := l_Return_Status;
Error_Handler.Get_Message_List(l_Mesg_List);
Debug_Show('-------------------------------------------------------------------');
For i In 1 .. l_Msg_Count Loop
Debug_Show('Entity Id : ' || l_Mesg_List(i).Entity_Id);
Debug_Show('Index : ' || l_Mesg_List(i).Entity_Index);
Debug_Show('Message Type : ' || l_Mesg_List(i).Message_Type);
t := l_Mesg_List(i).Message_Text;
x_Return_Message := x_Return_Message || l_Mesg_List(i)
.Message_Text;
Msg_Start := 'Message : ';
While Length(t) > 0 Loop
Debug_Show(Msg_Start || Substr(t, 1, 80));
t := Substr(t, 81);
Msg_Start := ' ';
End Loop;
Debug_Show('-------------------------------------------------------------------');
End Loop;
End If;
Else
Error_Handler.Initialize;
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 => Lx_Bom_Header_Rec,
x_Bom_Revision_Tbl => Lx_Bom_Revision_Tbl,
x_Bom_Component_Tbl => Lx_Bom_Component_Tbl,
x_Bom_Ref_Designator_Tbl => Lx_Bom_Ref_Designator_Tbl,
x_Bom_Sub_Component_Tbl => Lx_Bom_Sub_Component_Tbl,
x_Return_Status => Lx_Return_Status,
x_Msg_Count => Lx_Msg_Count,
p_Debug => g_Debug,
p_Output_Dir => g_Debug_Output_Dir,
p_Debug_Filename => g_Debug_Filename);
-- Debug_Show('Return Status = ' || lx_return_status);
-- Debug_Show('Message Count = ' || lx_msg_count);
Error_Handler.Get_Message_List(l_Error_Message_List);
For i In 1 .. Lx_Msg_Count Loop
-- Debug_Show(to_char(i) || ' MESSAGE TEXT ' ||
-- substr(l_error_message_list(i).message_text, 1, 250));
-- Debug_Show(to_char(i) || ' MESSAGE TYPE ' || l_error_message_list(i).message_type);
If l_Error_Message_List(i).Message_Type <> 'W' Then
x_Return_Message := x_Return_Message || l_Error_Message_List(i)
.Message_Text || ' ';
End If;
End Loop;
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
If Lx_Return_Status <> 'S' Then
x_Return_Status := 'E';
Rollback;
Else
If l_Delete_Type > 0 Then
Delete_Group_Api(l_Bom_Header_Rec.Organization_Code,
l_Delete_Group_Name,
l_Delete_Type);
End If;
x_Return_Status := 'S';
Commit;
End If;
End If;
Insert_Log(p_Transaction_Id => p_Transaction_Id,
p_Entity_Type => 'BOM',
p_Entity_Name => p_Bom_Header_Rec.Assembly_Item_Name,
p_Transaction_Type => p_Bom_Header_Rec.Transaction_Type,
p_Transaction_Date => Sysdate,
p_Return_Status => x_Return_Status,
p_Return_Message => x_Return_Message);
End Bom_Sync_Proc;
--------------------------------------------------------------------------------
Procedure Rtg_Sync_Proc --
(p_Transaction_Id In Number,
p_Rtg_Header_Rec Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.g_Miss_Rtg_Header_Rec,
p_Rtg_Revision_Tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type := Bom_Rtg_Pub.g_Miss_Rtg_Revision_Tbl,
p_Operation_Tbl Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.g_Miss_Operation_Tbl,
p_Op_Resource_Tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Resource_Tbl,
p_Sub_Resource_Tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Sub_Resource_Tbl,
p_Op_Network_Tbl Bom_Rtg_Pub.Op_Network_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Network_Tbl,
x_Return_Status Out Varchar2,
x_Return_Message Out Varchar2) Is
--Pragma Autonomous_Transaction;
--?? delete_group_api?????????commit ???????? ??????pdm??????
l_Rtg_Header_Rec Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.g_Miss_Rtg_Header_Rec;
l_Rtg_Revision_Tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type := Bom_Rtg_Pub.g_Miss_Rtg_Revision_Tbl;
l_Operation_Tbl Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.g_Miss_Operation_Tbl;
l_Op_Resource_Tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Resource_Tbl;
l_Sub_Resource_Tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Sub_Resource_Tbl;
l_Op_Network_Tbl Bom_Rtg_Pub.Op_Network_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Network_Tbl;
l_Error_Message_List Error_Handler.Error_Tbl_Type;
Lx_Rtg_Header_Rec Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.g_Miss_Rtg_Header_Rec;
Lx_Rtg_Revision_Tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type := Bom_Rtg_Pub.g_Miss_Rtg_Revision_Tbl;
Lx_Operation_Tbl Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.g_Miss_Operation_Tbl;
Lx_Op_Resource_Tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Resource_Tbl;
Lx_Sub_Resource_Tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type := Bom_Rtg_Pub.g_Miss_Sub_Resource_Tbl;
Lx_Op_Network_Tbl Bom_Rtg_Pub.Op_Network_Tbl_Type := Bom_Rtg_Pub.g_Miss_Op_Network_Tbl;
Lx_Return_Status Varchar2(1);
Lx_Msg_Count Number;
l_Delete_Type Number;
l_Delete_Group_Name Varchar2(100);
p_Organization_Code Varchar2(20);
Begin
Debug_Show('-->>装配件：' || p_Rtg_Header_Rec.Assembly_Item_Name);
Debug_Show('-->>组织：' || p_Rtg_Header_Rec.Organization_Code);
Debug_Show('-->>事物处理类型：' || p_Rtg_Header_Rec.Transaction_Type);
p_Organization_Code := p_Rtg_Header_Rec.Organization_Code;
l_Rtg_Header_Rec.Assembly_Item_Name := p_Rtg_Header_Rec.Assembly_Item_Name;
l_Rtg_Header_Rec.Organization_Code := p_Organization_Code;
l_Rtg_Header_Rec.Eng_Routing_Flag := 2;
l_Rtg_Header_Rec.Transaction_Type := p_Rtg_Header_Rec.Transaction_Type;
l_Rtg_Header_Rec.Delete_Group_Name := p_Rtg_Header_Rec.Delete_Group_Name;
l_Rtg_Header_Rec.Return_Status := Null;
l_Rtg_Header_Rec.Attribute2 := p_Rtg_Header_Rec.Attribute2;
If l_Rtg_Header_Rec.Transaction_Type = 'DELETE' Then
l_Delete_Type := 3;
l_Delete_Group_Name := l_Rtg_Header_Rec.Delete_Group_Name;
End If;
For i In 1 .. p_Operation_Tbl.Count Loop
/*If (Nvl(p_Operation_Tbl(i).Transaction_Type, '~') != '~' And
l_Rtg_Header_Rec.Transaction_Type = 'MAINTAIN_LINE') Or l_Rtg_Header_Rec.Transaction_Type != 'MAINTAIN_LINE' Then*/
l_Operation_Tbl(i).Assembly_Item_Name := p_Rtg_Header_Rec.Assembly_Item_Name;
l_Operation_Tbl(i).Organization_Code := p_Rtg_Header_Rec.Organization_Code;
l_Operation_Tbl(i).Delete_Group_Name := p_Operation_Tbl(i)
.Delete_Group_Name;
l_Operation_Tbl(i).Operation_Sequence_Number := p_Operation_Tbl(i)
.Operation_Sequence_Number;
l_Operation_Tbl(i).Operation_Type := 1;
l_Operation_Tbl(i).Start_Effective_Date := p_Operation_Tbl(i)
.Start_Effective_Date;
l_Operation_Tbl(i).Department_Code := p_Operation_Tbl(i)
.Department_Code;
l_Operation_Tbl(i).Operation_Description := p_Operation_Tbl(i)
.Operation_Description;
l_Operation_Tbl(i).Transaction_Type := p_Operation_Tbl(i)
.Transaction_Type;
l_Operation_Tbl(i).Attribute7 := p_Operation_Tbl(i).Attribute7; --专用工具
Debug_Show(' -->>工序事物处理类型：' || l_Operation_Tbl(i).Transaction_Type);
-- ??????????
If l_Operation_Tbl(i)
.Transaction_Type = 'DELETE' And l_Delete_Type Is Null Then
l_Delete_Type := 5;
l_Delete_Group_Name := l_Operation_Tbl(i).Delete_Group_Name;
End If;
/*End If;*/
End Loop;
For j In 1 .. p_Op_Resource_Tbl.Count Loop
l_Op_Resource_Tbl(j).Assembly_Item_Name := p_Rtg_Header_Rec.Assembly_Item_Name;
l_Op_Resource_Tbl(j).Organization_Code := p_Rtg_Header_Rec.Organization_Code;
l_Op_Resource_Tbl(j).Operation_Type := 1;
l_Op_Resource_Tbl(j).Op_Start_Effective_Date := p_Op_Resource_Tbl(j)
.Op_Start_Effective_Date;
l_Op_Resource_Tbl(j).Operation_Sequence_Number := p_Op_Resource_Tbl(j)
.Operation_Sequence_Number;
l_Op_Resource_Tbl(j).Resource_Sequence_Number := p_Op_Resource_Tbl(j)
.Resource_Sequence_Number;
l_Op_Resource_Tbl(j).Transaction_Type := p_Op_Resource_Tbl(j)
.Transaction_Type;
l_Op_Resource_Tbl(j).Resource_Code := p_Op_Resource_Tbl(j)
.Resource_Code;
l_Op_Resource_Tbl(j).Usage_Rate_Or_Amount := p_Op_Resource_Tbl(j)
.Usage_Rate_Or_Amount;
l_Op_Resource_Tbl(j).Schedule_Flag := p_Op_Resource_Tbl(j)
.Schedule_Flag;
l_Op_Resource_Tbl(j).Autocharge_Type := p_Op_Resource_Tbl(j)
.Autocharge_Type;
l_Op_Resource_Tbl(j).Assigned_Units := p_Op_Resource_Tbl(j)
.Assigned_Units;
l_Op_Resource_Tbl(j).Attribute1 := p_Op_Resource_Tbl(j).Attribute1;
l_Op_Resource_Tbl(j).Attribute3 := p_Op_Resource_Tbl(j).Attribute3;
Debug_Show(' -->>资源处理类型：' || l_Op_Resource_Tbl(j).Transaction_Type);
End Loop;
/*If l_Rtg_Header_Rec.Transaction_Type = 'MAINTAIN_LINE' Then
l_Rtg_Header_Rec := Bom_Rtg_Pub.g_Miss_Rtg_Header_Rec;
End If;*/
Error_Handler.Initialize;
Bom_Rtg_Pub.Process_Rtg(p_Bo_Identifier => 'RTG',
p_Api_Version_Number => 1.0,
p_Init_Msg_List => False,
p_Rtg_Header_Rec => l_Rtg_Header_Rec,
p_Rtg_Revision_Tbl => l_Rtg_Revision_Tbl,
p_Operation_Tbl => l_Operation_Tbl,
p_Op_Resource_Tbl => l_Op_Resource_Tbl,
p_Sub_Resource_Tbl => l_Sub_Resource_Tbl,
p_Op_Network_Tbl => l_Op_Network_Tbl,
x_Rtg_Header_Rec => Lx_Rtg_Header_Rec,
x_Rtg_Revision_Tbl => Lx_Rtg_Revision_Tbl,
x_Operation_Tbl => Lx_Operation_Tbl,
x_Op_Resource_Tbl => Lx_Op_Resource_Tbl,
x_Sub_Resource_Tbl => Lx_Sub_Resource_Tbl,
x_Op_Network_Tbl => Lx_Op_Network_Tbl,
x_Return_Status => Lx_Return_Status,
x_Msg_Count => Lx_Msg_Count,
p_Debug => g_Debug,
p_Output_Dir => g_Debug_Output_Dir,
p_Debug_Filename => g_Debug_Filename);
-- Debug_Show('Return Status = ' || lx_return_status);
-- Debug_Show('Message Count = ' || lx_msg_count);
Error_Handler.Get_Message_List(l_Error_Message_List);
For i In 1 .. l_Error_Message_List.Count Loop
-- Debug_Show('Entity Id : ' || l_error_message_list(i).entity_id);
-- Debug_Show('Index : ' || l_error_message_list(i).entity_index);
-- Debug_Show('Message Type : ' || l_error_message_list(i).message_type);
-- Debug_Show('Mesg : ' || substr(l_error_message_list(i).message_text, 1, 250));
-- Debug_Show('-------------------------------------------------------------------');
If l_Error_Message_List(i).Message_Type <> 'W' Then
If Length(x_Return_Message || l_Error_Message_List(i)
.Message_Text || ' ') < 2000 Then
x_Return_Message := x_Return_Message || l_Error_Message_List(i)
.Message_Text || ' ';
End If;
End If;
End Loop;
If Lx_Return_Status <> 'S' Then
x_Return_Status := 'E';
----Rollback;
Else
If l_Delete_Type > 0 Then
Debug_Show(' -->>删除组：' || l_Delete_Group_Name || ',' ||
p_Organization_Code);
Delete_Group_Api(p_Organization_Code,
l_Delete_Group_Name,
l_Delete_Type);
End If;
x_Return_Status := 'S';
----Commit;
End If;
x_Return_Message := Substr(x_Return_Message, 1, 1000);
Insert_Log(p_Transaction_Id => p_Transaction_Id,
p_Entity_Type => 'RTG',
p_Entity_Name => l_Rtg_Header_Rec.Assembly_Item_Name,
p_Transaction_Type => l_Rtg_Header_Rec.Transaction_Type,
p_Transaction_Date => Sysdate,
p_Return_Status => x_Return_Status,
p_Return_Message => x_Return_Message);
End Rtg_Sync_Proc;
Procedure Item_Sync_Main(p_Group_Name In Varchar2,
p_Total_Num Out Number,
p_Success_Num Out Number) Is
l_Item_Rec Inv_Item_Grp.Item_Rec_Type;
l_Item_Rec2 Inv_Item_Grp.Item_Rec_Type;
l_Template_Name Varchar(100);
x_Return_Status Varchar2(200);
x_Msg_Data Varchar2(1000);
x_Error_Tbl Inv_Item_Grp.Error_Tbl_Type;
x_Item_Rec Inv_Item_Grp.Item_Rec_Type;
p_Transaction_Type Varchar2(10);
t_Total_Num Number;
t_Success_Num Number;
p_Delete_Group_Id Number;
p_Delete_Group_Name Varchar2(100);
p_Delete_Exists Varchar2(1); -- ?????????
p_Commit_Count Number;
x Number;
x_Status Varchar2(20);
x_Status_Code Varchar2(2000);
x_Item_Id Number;
x_Msg_Count Number;
x_Msg_Data2 Varchar2(2000);
--xdp20120504add
V_Wip_Supply_Subinventory VARCHAR2(5);
V_Wip_Supply_Locator_Id VARCHAR2(5);
v_location_id number;
v_ORGANIZATION_CODE varchar2(10);
--xdp20120504add
p_Category_Rec Cux_Pdm_Erp_Sync_Pkg.Category_Type;
Cursor Cur_Line(p_Group_Name Varchar2) Is
Select a.Group_Name,
a.Item_Tran_Id,
a.Segment1, --物料编码
a.Description, --物料说明
a.Primary_Uom_Code, --计量单位
a.Long_Description, --物料备注信息
a.Template_Name, --物料模板
a.Category_Set_Id, --物料类别集
a.Category_Id, --大中小类
a.Organization_Id, --组织分配
a.Organization_Code, --组织分配
a.Lot_Control_Code, --批次控制，库存-批次-控制,
a.Location_Control_Code, --货位控制
a.Buyer_Id, --采购员-只能主层
a.Planning_Make_Buy_Code, --制造或采购
a.Full_Lead_Time, --采购提前期
a.Planner_Code, --计划员
a.Transaction_Type,
a.Receiving_Routing_Id,
a.Attribute1,
a.Attribute2,
a.Attribute3,
a.Attribute4,
a.Attribute5,
a.Attribute6,
a.Attribute7, --xdp20120504add
a.Attribute8,
a.Attribute9,
a.Attribute30, --xdp20120504add
Item_Catalog_Header_Id,
End_Assembly_Pegging_Flag, --MPS/MRP计划-追溯，是否按照项目跟踪,value
List_Price_Per_Unit, --采购-价目表价格，参考采购价格，value
Minimum_Order_Quantity, --总计划-订货量-最小，最小订货量，value
Fixed_Days_Supply, --总计划-订单修改量-固定供应天数，固定供应天数（天），value
Shippable_Item_Flag, --订单管理-可发运，是否可销售，vallue
Shelf_Life_Code, --库存-批次过期-控制，批次过期控制，vallue
Shelf_Life_Days, --库存-批次过期-存储期限，批次过期天数（天），value
Serial_Number_Control_Code, --库存-序列-生成，是否序列控制，vallue
Min_Minmax_Quantity, --总计划-最小数量，库存最小限额，value
Max_Minmax_Quantity, --总计划-最大数量，库存最大限额，value
Days_Early_Receipt_Allowed, --接收-接收日期控制-提前天数，接收提前天数，value
Days_Late_Receipt_Allowed, --接收-接收日期控制-延迟天数，接收延迟天数，value
Receipt_Days_Exception_Code, --接收-接收日期控制-活动，接收活动，value
Qty_Rcv_Tolerance, --接收-超量接收控制-允差，接收超量允差，value
Qty_Rcv_Exception_Code, --接收-超量接收控制-活动，超量接收活动，value
Fixed_Lot_Multiplier, --总计划-订单修改量-固定批次增加，最小包装单位，value
a.Weight_Uom_Code,
a.Unit_Weight,
a.Wip_Supply_Subinventory,
a.Wip_Supply_Locator_code,
a.Default_Receiving_Subinv
From Cux_Mtl_System_Items_Interface a
Where a.Group_Name = p_Group_Name
And a.Return_Status Is Null
Order By a.Segment1, a.Organization_Id;
Cursor Cur_Del(p_Group_Name In Varchar2) Is
Select a.Organization_Id, a.Organization_Code
From Cux_Mtl_System_Items_Interface a
Where a.Group_Name = p_Group_Name
--And a.Return_Status Is Null
And a.Transaction_Type = 'DELETE'
Group By a.Organization_Id, a.Organization_Code;
--c_Cur_Line Cur_Line%Rowtype;
Begin
t_Total_Num := 0;
t_Success_Num := 0;
begin
Select Dg.Delete_Group_Sequence_Id, Dg.Delete_Group_Name
Into p_Delete_Group_Id, p_Delete_Group_Name
From Bom_Delete_Groups Dg, Mtl_Parameters Mp
Where Dg.Delete_Group_Name = 'delete_g'
And Mp.Organization_Id = Dg.Organization_Id;
exception when others then
p_Delete_Group_Id:=0;
p_Delete_Group_Name:=null;
end;
--fnd_file.put_line(fnd_file.output,'33333333333333');
For c_Cur_Line In Cur_Line(p_Group_Name) Loop
/*xdp20120504add*/
select ood.ORGANIZATION_CODE
into v_ORGANIZATION_CODE
from org_organization_definitions ood
where ood.organization_id = c_Cur_Line.Organization_Id
and nvl(ood.DISABLE_DATE, sysdate + 1) > sysdate;
if v_ORGANIZATION_CODE <> 'HEC' and
c_Cur_Line.Wip_Supply_Subinventory is not null then
v_location_id := get_locator_id(p_organization_id => c_Cur_Line.Organization_Id,
p_subinv_code => c_Cur_Line.Wip_Supply_Subinventory,
p_locator_code => c_Cur_Line.Wip_Supply_Locator_code);
V_Wip_Supply_Subinventory := c_Cur_Line.Wip_Supply_Subinventory;
V_Wip_Supply_Locator_id := v_location_id;
ELSE
V_Wip_Supply_Subinventory := NULL;
V_Wip_Supply_Locator_id := NULL;
end if;
/*xdp20120504add*/
l_Item_Rec := Inv_Item_Grp.g_Miss_Item_Rec;
l_Item_Rec.Segment1 := c_Cur_Line.Segment1;
l_Item_Rec.Description := c_Cur_Line.Description;
l_Item_Rec.Primary_Uom_Code := c_Cur_Line.Primary_Uom_Code;
l_Item_Rec.Organization_Id := c_Cur_Line.Organization_Id;
l_Item_Rec.Lot_Control_Code := c_Cur_Line.Lot_Control_Code;
l_Item_Rec.Location_Control_Code := c_Cur_Line.Location_Control_Code;
l_Item_Rec.Buyer_Id := c_Cur_Line.Buyer_Id;
l_Item_Rec.Planning_Make_Buy_Code := c_Cur_Line.Planning_Make_Buy_Code;
l_Item_Rec.Full_Lead_Time := c_Cur_Line.Full_Lead_Time;
l_Item_Rec.Planner_Code := c_Cur_Line.Planner_Code;
l_Item_Rec.Long_Description := c_Cur_Line.Long_Description;
l_Item_Rec.Receiving_Routing_Id := c_Cur_Line.Receiving_Routing_Id;
l_Item_Rec.Attribute1 := c_Cur_Line.Attribute1;
l_Item_Rec.Attribute2 := c_Cur_Line.Attribute2;
l_Item_Rec.Attribute3 := c_Cur_Line.Attribute3;
l_Item_Rec.Attribute4 := c_Cur_Line.Attribute4;
l_Item_Rec.Attribute5 := c_Cur_Line.Attribute5;
l_Item_Rec.Attribute6 := c_Cur_Line.Attribute6;
l_Item_Rec.Attribute7 := c_Cur_Line.Attribute7; --xdp20120504add
l_Item_Rec.Attribute8 := c_Cur_Line.Attribute8;
l_Item_Rec.Attribute9 := c_Cur_Line.Attribute9;
l_Item_Rec.Attribute30 := c_Cur_Line.Attribute30; --xdp20120504add
l_Item_Rec.End_Assembly_Pegging_Flag := c_Cur_Line.End_Assembly_Pegging_Flag; --MPS/MRP计划-追溯，是否按照项目跟踪，value
l_Item_Rec.List_Price_Per_Unit := c_Cur_Line.List_Price_Per_Unit; --采购-价目表价格，参考采购价格，value
l_Item_Rec.Minimum_Order_Quantity := c_Cur_Line.Minimum_Order_Quantity; --总计划-订货量-最小，最小订货量，value
l_Item_Rec.Fixed_Days_Supply := c_Cur_Line.Fixed_Days_Supply; --总计划-订单修改量-固定供应天数，固定供应天数（天），value
l_Item_Rec.Shippable_Item_Flag := c_Cur_Line.Shippable_Item_Flag; --订单管理-可发运，是否可销售，vallue
l_Item_Rec.Shelf_Life_Code := c_Cur_Line.Shelf_Life_Code; --库存-批次过期-控制，批次过期控制，vallue
l_Item_Rec.Shelf_Life_Days := c_Cur_Line.Shelf_Life_Days; --库存-批次过期-存储期限，批次过期天数（天），value
l_Item_Rec.Serial_Number_Control_Code := c_Cur_Line.Serial_Number_Control_Code; --库存-序列-生成，是否序列控制，vallue
l_Item_Rec.Min_Minmax_Quantity := c_Cur_Line.Min_Minmax_Quantity; --总计划-最小数量，库存最小限额，value
l_Item_Rec.Max_Minmax_Quantity := c_Cur_Line.Max_Minmax_Quantity; --总计划-最大数量，库存最大限额，value
l_Item_Rec.Days_Early_Receipt_Allowed := c_Cur_Line.Days_Early_Receipt_Allowed; --接收-接收日期控制-提前天数，接收提前天数，value
l_Item_Rec.Days_Late_Receipt_Allowed := c_Cur_Line.Days_Late_Receipt_Allowed; --接收-接收日期控制-延迟天数，接收延迟天数，value
l_Item_Rec.Receipt_Days_Exception_Code := c_Cur_Line.Receipt_Days_Exception_Code; --接收-接收日期控制-活动，接收活动，value
l_Item_Rec.Qty_Rcv_Tolerance := c_Cur_Line.Qty_Rcv_Tolerance; --接收-超量接收控制-允差，接收超量允差，value
l_Item_Rec.Qty_Rcv_Exception_Code := c_Cur_Line.Qty_Rcv_Exception_Code; --接收-超量接收控制-活动，超量接收活动，value
l_Item_Rec.Fixed_Lot_Multiplier := c_Cur_Line.Fixed_Lot_Multiplier; --总计划-订单修改量-固定批次增加，最小包装单位，value
l_Item_Rec.Weight_Uom_Code := c_Cur_Line.Weight_Uom_Code;
l_Item_Rec.Unit_Weight := c_Cur_Line.Unit_Weight;
l_Item_Rec.Wip_Supply_Subinventory := V_Wip_Supply_Subinventory; /*xdp20120504add*/
l_Item_Rec.Wip_Supply_Locator_Id := V_Wip_Supply_Locator_Id; /*xdp20120504add*/
l_Item_Rec.Created_By := -1;
l_Item_Rec.Last_Updated_By := -1;
l_Item_Rec.Creation_Date := Sysdate;
l_Item_Rec.Last_Update_Date := Sysdate;
p_Transaction_Type := c_Cur_Line.Transaction_Type;
--Fnd_Global.Apps_Initialize(1151, 50659, 401);
If p_Transaction_Type = 'DELETE' Then
/*Select Dg.Delete_Group_Sequence_Id, Dg.Delete_Group_Name
--Mp.Organization_Id
Into p_Delete_Group_Id, p_Delete_Group_Name
From Bom_Delete_Groups Dg, Mtl_Parameters Mp
Where \*Dg.Delete_Group_Name = 'DELITEM'
And*\
Mp.Organization_Id = Dg.Organization_Id
And Mp.Organization_Id = c_Cur_Line.Organization_Id
And Dg.Delete_Type = 1 --类型为物流
And Rownum = 1;*/
Debug_Show('1-->' || p_Delete_Group_Id || ',' ||
p_Delete_Group_Name);
Insert Into Bom_Delete_Entities a
(a.Delete_Entity_Sequence_Id,
a.Delete_Group_Sequence_Id,
a.Delete_Entity_Type,
a.Inventory_Item_Id,
a.Organization_Id,
a.Item_Description,
a.Item_Concat_Segments,
a.Delete_Status_Type,
a.Prior_Process_Flag,
a.Prior_Commit_Flag,
a.Last_Update_Date,
a.Last_Updated_By,
a.Creation_Date,
a.Created_By,
a.Last_Update_Login)
Select Bom_Delete_Entities_s.Nextval,
p_Delete_Group_Id,
1,
Msi.Inventory_Item_Id,
--c_Cur_Line.Organization_Id,
Cux_Tool_Pkg.g_Organization_Id,
Msi.Description,
Msi.Segment1,
1,
2,
1,
Sysdate,
-1,
Sysdate,
-1,
-1
From Mtl_System_Items_b Msi
Where Msi.Organization_Id = Cux_Tool_Pkg.g_Organization_Id --c_Cur_Line.Organization_Id
And Msi.Segment1 = c_Cur_Line.Segment1;
p_Delete_Exists := 'Y';
x_Return_Status := 'S';
Insert_Log(p_Transaction_Id => c_Cur_Line.Item_Tran_Id,
p_Entity_Type => 'ITEM',
p_Entity_Name => c_Cur_Line.Segment1,
p_Transaction_Type => p_Transaction_Type,
p_Transaction_Date => Sysdate,
p_Return_Status => 'S',
p_Return_Message => Null);
Else
p_Category_Rec := Cux_Pdm_Erp_Sync_Pkg.g_Category_Rec;
p_Category_Rec.Category_Id := c_Cur_Line.Category_Id;
p_Category_Rec.Category_Set_Id := c_Cur_Line.Category_Set_Id;
Cux_Pdm_Erp_Sync_Pkg.Item_Sync_Proc(p_Transaction_Type => p_Transaction_Type,
p_Template_Name => c_Cur_Line.Template_Name,
p_Category_Rec => p_Category_Rec,
p_Item_Rec => l_Item_Rec,
x_Item_Id => x_Item_Id,
p_Return_Status => x_Return_Status,
p_Return_Msg => x_Msg_Data);
--更新目录组
If x_Return_Status = 'S' And
Nvl(c_Cur_Line.Item_Catalog_Header_Id, -1) != -1 Then
Catalog_Maintain(d_Group_Name => p_Group_Name,
d_Header_Id => c_Cur_Line.Item_Catalog_Header_Id,
x_Status => x_Status,
x_Status_Code => x_Status_Code);
If c_Cur_Line.Default_Receiving_Subinv Is Not Null Then
Inv_Item_Sub_Default_Pkg.Insert_Upd_Item_Sub_Defaults(x_Return_Status => x_Status_Code,
x_Msg_Count => x_Msg_Count,
x_Msg_Data => x_Msg_Data2,
p_Organization_Id => c_Cur_Line.Organization_Id,
p_Inventory_Item_Id => x_Item_Id,
p_Subinventory_Code => c_Cur_Line.Default_Receiving_Subinv,
p_Default_Type => 2,
p_Creation_Date => Sysdate,
p_Created_By => -1,
p_Last_Update_Date => Sysdate,
p_Last_Updated_By => -1,
p_Process_Code => 'INSERT', --'UPDATE',/*xdp20120504add*/
P_COMMIT => FND_API.G_false);
End If;
End If;
End If;
t_Total_Num := t_Total_Num + 1;
-- Debug_Show(x_msg_data);
If x_Return_Status = 'S' Then
t_Success_Num := t_Success_Num + 1;
End If;
Update Cux_Mtl_System_Items_Interface
Set Return_Status = x_Return_Status,
Error_Msg = (x_Msg_Data || x_Status_Code)
Where Item_Tran_Id = c_Cur_Line.Item_Tran_Id
--2011-11-03 add
And Group_Name = c_Cur_Line.Group_Name;
-------
/*SELECT MOD(t_total_num
,10)
INTO p_commit_count
FROM dual;
IF p_commit_count = 0 THEN
COMMIT;
END IF;*/
End Loop;
If Nvl(p_Delete_Exists, 'N') = 'Y' Then
--Delete_Group_Api('MST', 'DELITEM', 1);
--Debug_Show('-->x:' || x);
/*For c_Del In Cur_Del(p_Group_Name) Loop
Debug_Show('-->delete--c_Del.Organization_Code:' ||
c_Del.Organization_Code || ',' ||
'p_Delete_Group_Name:' || p_Delete_Group_Name);
Select Dg.Delete_Group_Sequence_Id, Dg.Delete_Group_Name
--Mp.Organization_Id
Into p_Delete_Group_Id, p_Delete_Group_Name
From Bom_Delete_Groups Dg, Mtl_Parameters Mp
Where \*Dg.Delete_Group_Name = 'DELITEM'
And*\
Mp.Organization_Id = Dg.Organization_Id
And Mp.Organization_Id = c_Del.Organization_Id
And Dg.Delete_Type = 1 --类型为物流
And Rownum = 1;
Delete_Group_Api(c_Del.Organization_Code, p_Delete_Group_Name, 1);
End Loop;*/
Delete_Group_Api(Cux_Tool_Pkg.g_Organization_Code,
p_Delete_Group_Name,
1);
End If;
Debug_Show('-->delete over');
Delete From Mtl_System_Items_Interface
Where Segment1 In (Select Segment1
From Cux_Mtl_System_Items_Interface t
Where Group_Name = p_Group_Name);
----COMMIT;
p_Total_Num := t_Total_Num;
p_Success_Num := t_Success_Num;
End;
Procedure Bom_Sync_Main(p_Group_Name In Varchar2,
p_Total_Num Out Number,
p_Success_Num Out Number) Is
l_Transaction_Id Number;
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;
x_Return_Status Varchar2(1);
x_Return_Message Varchar2(1000);
p_Count Number;
p_Tran_Type Varchar2(30);
p_Line_Type Varchar2(30);
p_Transaction_Type Varchar2(10);
t_Total_Num Number;
t_Success_Num Number;
Cursor Cur_Head(p_Group_Name Varchar2) Is
Select *
From Cux_Bom_Head_Interface l
Where l.Group_Name = p_Group_Name;
c_Cur_Head Cur_Head%Rowtype;
Cursor Cur_Line(Tran_Id Number) Is
Select *
From Cux_Bom_Line_Interface l
Where l.Bill_Sequence_Id = Tran_Id;
c_Cur_Line Cur_Line%Rowtype;
Begin
t_Total_Num := 0;
t_Success_Num := 0;
Open Cur_Head(p_Group_Name);
Loop
Fetch Cur_Head
Into c_Cur_Head;
Exit When Cur_Head%Notfound;
l_Bom_Header_Rec.Assembly_Item_Name := c_Cur_Head.Assembly_Item_Name;
l_Bom_Header_Rec.Organization_Code := c_Cur_Head.Organization_Code;
-- l_bom_header_rec.alternate_bom_code:= c_cur_head.alternate_bom_code;
l_Bom_Header_Rec.Assembly_Type := c_Cur_Head.Assembly_Type;
l_Bom_Header_Rec.Transaction_Type := c_Cur_Head.Transaction_Type;
p_Tran_Type := c_Cur_Head.Transaction_Type;
Debug_Show('<======================================>');
Debug_Show(l_Bom_Header_Rec.Assembly_Item_Name);
Debug_Show(l_Bom_Header_Rec.Organization_Code);
Debug_Show(l_Bom_Header_Rec.Assembly_Type);
-- p_tran_type :='DELETE';
If p_Tran_Type = 'CREATE' Then
l_Bom_Component_Tbl := Bom_Bo_Pub.g_Miss_Bom_Component_Tbl;
p_Count := 1;
Open Cur_Line(c_Cur_Head.Bill_Sequence_Id);
Loop
Fetch Cur_Line
Into c_Cur_Line;
Exit When Cur_Line%Notfound;
Debug_Show('<---->');
Debug_Show(c_Cur_Line.Item_Sequence_Number);
Debug_Show(c_Cur_Line.Quantity_Per_Assembly);
Debug_Show(c_Cur_Line.Start_Effective_Date);
Debug_Show(c_Cur_Line.Component_Item_Name);
Debug_Show(c_Cur_Line.Operation_Sequence_Number);
l_Bom_Component_Tbl(p_Count).Item_Sequence_Number := c_Cur_Line.Item_Sequence_Number;
l_Bom_Component_Tbl(p_Count).Transaction_Type := p_Tran_Type;
l_Bom_Component_Tbl(p_Count).Quantity_Per_Assembly := c_Cur_Line.Quantity_Per_Assembly;
l_Bom_Component_Tbl(p_Count).Start_Effective_Date := c_Cur_Line.Start_Effective_Date;
l_Bom_Component_Tbl(p_Count).Component_Item_Name := c_Cur_Line.Component_Item_Name;
l_Bom_Component_Tbl(p_Count).Operation_Sequence_Number := c_Cur_Line.Operation_Sequence_Number;
l_Bom_Component_Tbl(p_Count).Attribute1 := c_Cur_Line.Attribute1; --下料尺寸
p_Count := p_Count + 1;
End Loop;
Close Cur_Line;
Elsif p_Tran_Type = 'DELETE' Then
l_Bom_Header_Rec.Delete_Group_Name := 'DELBOM';
l_Bom_Header_Rec.Transaction_Type := 'DELETE';
Elsif p_Tran_Type = 'UPDATE' Then
l_Bom_Header_Rec.Delete_Group_Name := 'DELBOM';
l_Bom_Header_Rec.Transaction_Type := 'DELETE';
Cux_Pdm_Erp_Sync_Pkg.Bom_Sync_Proc --
(p_Transaction_Id => l_Transaction_Id,
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_Return_Status => x_Return_Status,
x_Return_Message => x_Return_Message);
If x_Return_Status = 'S' Then
Select h.Assembly_Item_Name,
h.Organization_Code,
Null,
h.Assembly_Type
Into l_Bom_Header_Rec.Assembly_Item_Name,
l_Bom_Header_Rec.Organization_Code,
l_Bom_Header_Rec.Alternate_Bom_Code,
l_Bom_Header_Rec.Assembly_Type
From Cux_Bom_Head_Interface h
Where h.Bill_Sequence_Id = l_Transaction_Id;
l_Bom_Header_Rec.Transaction_Type := 'CREATE';
p_Count := 1;
Open Cur_Line(l_Transaction_Id);
Loop
Fetch Cur_Line
Into c_Cur_Line;
Exit When Cur_Line%Notfound;
l_Bom_Component_Tbl(p_Count).Item_Sequence_Number := c_Cur_Line.Item_Sequence_Number;
l_Bom_Component_Tbl(p_Count).Transaction_Type := 'CREATE';
l_Bom_Component_Tbl(p_Count).Quantity_Per_Assembly := c_Cur_Line.Quantity_Per_Assembly;
l_Bom_Component_Tbl(p_Count).Start_Effective_Date := c_Cur_Line.Start_Effective_Date;
l_Bom_Component_Tbl(p_Count).Component_Item_Name := c_Cur_Line.Component_Item_Name;
l_Bom_Component_Tbl(p_Count).Operation_Sequence_Number := c_Cur_Line.Operation_Sequence_Number;
l_Bom_Component_Tbl(p_Count).Attribute1 := c_Cur_Line.Attribute1; --下料尺寸-xdpadd
p_Count := p_Count + 1;
End Loop;
Close Cur_Line;
End If;
Elsif p_Tran_Type = 'MAINTAIN_LINE' Then
Debug_Show('-->>事物处理类型:' || p_Tran_Type);
--l_Bom_Header_Rec := Bom_Bo_Pub.g_Miss_Bom_Header_Rec;
l_Bom_Component_Tbl := Bom_Bo_Pub.g_Miss_Bom_Component_Tbl;
p_Count := 1;
Open Cur_Line(c_Cur_Head.Bill_Sequence_Id);
Loop
Fetch Cur_Line
Into c_Cur_Line;
Exit When Cur_Line%Notfound;
Debug_Show('-->>事物处理行类型:' || c_Cur_Line.Transaction_Type);
Debug_Show(' -->>行组织代码:' || c_Cur_Line.Organization_Code);
Debug_Show(' -->>物料:' || c_Cur_Line.Assembly_Item_Name);
Debug_Show(' -->>工序号:' || c_Cur_Line.Operation_Sequence_Number);
Debug_Show(' -->>组件:' || c_Cur_Line.Component_Item_Name);
Debug_Show(' -->>有效日期:' || c_Cur_Line.Start_Effective_Date);
p_Line_Type := c_Cur_Line.Transaction_Type;
l_Bom_Component_Tbl(p_Count).Transaction_Type := p_Line_Type;
l_Bom_Component_Tbl(p_Count).Organization_Code := c_Cur_Head.Organization_Code;
l_Bom_Component_Tbl(p_Count).Assembly_Item_Name := c_Cur_Head.Assembly_Item_Name;
l_Bom_Component_Tbl(p_Count).Alternate_Bom_Code := c_Cur_Head.Alternate_Bom_Designator;
l_Bom_Component_Tbl(p_Count).Operation_Sequence_Number := c_Cur_Line.Operation_Sequence_Number;
l_Bom_Component_Tbl(p_Count).Component_Item_Name := c_Cur_Line.Component_Item_Name;
l_Bom_Component_Tbl(p_Count).Start_Effective_Date := c_Cur_Line.Start_Effective_Date; --To_Date(To_Char(c_Cur_Line.Start_Effective_Date,
-- 'yyyy-mm-dd hh24:mi:ss'),
--'');
If p_Line_Type = 'DELETE' Then
l_Bom_Component_Tbl(p_Count).Delete_Group_Name := 'DELBOM_C';
Else
l_Bom_Component_Tbl(p_Count).Item_Sequence_Number := c_Cur_Line.Item_Sequence_Number; --序号
l_Bom_Component_Tbl(p_Count).Quantity_Per_Assembly := c_Cur_Line.Quantity_Per_Assembly; --数量
l_Bom_Component_Tbl(p_Count).New_Effectivity_Date := c_Cur_Line.New_Effectivity_Date; --新的起效日期
l_Bom_Component_Tbl(p_Count).New_Operation_Sequence_Number := c_Cur_Line.New_Operation_Sequence_Number; --新工序号
l_Bom_Component_Tbl(p_Count).Wip_Supply_Type := c_Cur_Line.Wip_Supply_Type; --供应类型
l_Bom_Component_Tbl(p_Count).Disable_Date := c_Cur_Line.Disable_Date;
l_Bom_Component_Tbl(p_Count).Attribute1 := c_Cur_Line.Attribute1; --下料尺寸
End If;
p_Count := p_Count + 1;
End Loop;
Close Cur_Line;
End If;
Cux_Pdm_Erp_Sync_Pkg.Bom_Sync_Proc --
(p_Transaction_Id => l_Transaction_Id,
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_Return_Status => x_Return_Status,
x_Return_Message => x_Return_Message);
-- Debug_Show('=================================================');
-- Debug_Show(x_return_status);
-- Debug_Show(x_return_message);
Update Cux_Bom_Head_Interface k
Set k.Return_Status = x_Return_Status,
k.Error_Msg = x_Return_Message
Where k.Bill_Sequence_Id = c_Cur_Head.Bill_Sequence_Id;
t_Total_Num := t_Total_Num + 1;
If x_Return_Status = 'S' Then
t_Success_Num := t_Success_Num + 1;
End If;
End Loop;
Close Cur_Head;
----COMMIT;
p_Total_Num := t_Total_Num;
p_Success_Num := t_Success_Num;
End;
Procedure Rou_Sync_Main(p_Group_Name In Varchar2,
p_Total_Num Out Number,
p_Success_Num Out Number) Is
Cursor Cur_Main(p_Group_Name Varchar2) Is
Select Rh.Routing_Sequence_Id,
Rh.Assembly_Item_Number,
Rh.Organization_Code,
Rh.Transaction_Type
From Cux_Routing_Head_Interface Rh
Where Rh.Group_Name = p_Group_Name
And Rh.Return_Status Is Null;
c_Cur_Main Cur_Main%Rowtype;
p_Return_Status Varchar2(1);
t_Total_Num Number;
t_Success_Num Number;
Begin
t_Total_Num := 0;
t_Success_Num := 0;
Open Cur_Main(p_Group_Name);
Loop
Fetch Cur_Main
Into c_Cur_Main;
Exit When Cur_Main%Notfound;
-- Debug_Show(c_cur_main.routing_sequence_id);
Rou_Sync_Line(c_Cur_Main.Routing_Sequence_Id, p_Return_Status);
t_Total_Num := t_Total_Num + 1;
If p_Return_Status = 'S' Then
t_Success_Num := t_Success_Num + 1;
End If;
End Loop;
Close Cur_Main;
p_Total_Num := t_Total_Num;
p_Success_Num := t_Success_Num;
End;
Procedure Rou_Sync_Line(p_Routing_Sequence_Id In Number,
p_Return_Status Out Varchar2) Is
l_Transaction_Id Number;
l_Rtg_Header_Rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
l_Rtg_Revision_Tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
l_Operation_Tbl Bom_Rtg_Pub.Operation_Tbl_Type;
l_Op_Resource_Tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
l_Sub_Resource_Tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
l_Op_Network_Tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
l_Return_Status Varchar2(1);
l_Return_Message Varchar2(20000);
p_Transaction_Type Varchar2(20);
p_Operation_Type Varchar2(20);
p_Resource_Type Varchar2(20);
-- t_total_num NUMBER;
-- t_success_num NUMBER;
-- p_commit_count NUMBER;
p_Count_s Number;
p_Count_r Number;
Cursor Cur_Main(p_Routing_Sequence_Id Number) Is
Select Rh.Routing_Sequence_Id, --cux_operation_id_s
Rh.Assembly_Item_Number, --产品编码
Rh.Organization_Code, --组织code
Rh.Transaction_Type, --事物处理类型，创建为 CREATE
Rh.Attribute1, --工艺文件编号
Rh.Attribute2, --工艺路线
Rh.Alternate_Routing_Designator
From Cux_Routing_Head_Interface Rh
Where Rh.Routing_Sequence_Id = p_Routing_Sequence_Id;
c_Cur_Main Cur_Main%Rowtype;
Cursor Cur_Operation(Tran_Id Number) Is
Select s.Operation_Sequence_Id, --cux_operation_id_s
s.Operation_Seq_Num Operation_Sequence_Number, --部门序列，由小到大，由上到下指定
s.Department_Code, --部门code
s.Effectivity_Date Start_Effective_Date, --起效时间
s.Operation_Description, --部门描述
s.Transaction_Type,
s.Attribute7 --专用工具
From Cux_Routing_Sequence_Interface s
Where s.Routing_Sequence_Id = Tran_Id;
c_Cur_Operation Cur_Operation%Rowtype;
Cursor Cur_Resource(Tran_Id Number) Is
Select r.Effectivity_Date Op_Start_Effective_Date, --起效时间
r.Operation_Seq_Num Operation_Sequence_Number, --部门序列，与Cux_Routing_Sequence_Interface对应
r.Resource_Seq_Num Resource_Sequence_Number, --资源序列，由小到大，由上到下指定
r.Resource_Code, --资源代码
r.Usage_Rate_Or_Amount, --主要-->单位使用量
r.Usage_Rate_Or_Amount_Inverse, --主要-->倒数
r.Schedule_Flag, --计划，计划，1=是，2=否，3=前一个，4=下一个
r.Autocharge_Type, --成本计算-->计费类型
r.Assigned_Units,
r.Attribute1, --单件计奖工时
r.Attribute3, --单件标准工时
r.Transaction_Type
From Cux_Routing_Resource_Interface r
Where r.Operation_Sequence_Id = Tran_Id
And r.Resource_Code Is Not Null
And r.Usage_Rate_Or_Amount >= 0;
c_Cur_Resource Cur_Resource%Rowtype;
Begin
-- t_total_num := 0;
-- t_success_num := 0;
Open Cur_Main(p_Routing_Sequence_Id);
Loop
Fetch Cur_Main
Into c_Cur_Main;
Exit When Cur_Main%Notfound;
p_Transaction_Type := c_Cur_Main.Transaction_Type;
If p_Transaction_Type = 'CREATE' Then
--l_Rtg_Header_Rec := Bom_Rtg_Pub.Rtg_Header_Rec_Type;
l_Rtg_Header_Rec.Transaction_Type := p_Transaction_Type;
l_Rtg_Header_Rec.Assembly_Item_Name := c_Cur_Main.Assembly_Item_Number;
l_Rtg_Header_Rec.Organization_Code := c_Cur_Main.Organization_Code;
l_Rtg_Header_Rec.Attribute2 := c_Cur_Main.Attribute2;
l_Rtg_Header_Rec.Attribute1 := c_Cur_Main.Attribute1;
p_Count_s := 1;
p_Count_r := 1;
Open Cur_Operation(c_Cur_Main.Routing_Sequence_Id);
--l_Operation_Tbl := Bom_Rtg_Pub.Operation_Tbl_Type;
Loop
Fetch Cur_Operation
Into c_Cur_Operation;
Exit When Cur_Operation%Notfound;
l_Operation_Tbl(p_Count_s).Operation_Sequence_Number := c_Cur_Operation.Operation_Sequence_Number;
l_Operation_Tbl(p_Count_s).Start_Effective_Date := c_Cur_Operation.Start_Effective_Date;
l_Operation_Tbl(p_Count_s).Department_Code := c_Cur_Operation.Department_Code;
l_Operation_Tbl(p_Count_s).Transaction_Type := p_Transaction_Type;
l_Operation_Tbl(p_Count_s).Operation_Description := c_Cur_Operation.Operation_Description;
l_Operation_Tbl(p_Count_s).Attribute7 := c_Cur_Operation.Attribute7;
Open Cur_Resource(c_Cur_Operation.Operation_Sequence_Id);
Loop
Fetch Cur_Resource
Into c_Cur_Resource;
Exit When Cur_Resource%Notfound;
l_Op_Resource_Tbl(p_Count_r).Op_Start_Effective_Date := c_Cur_Resource.Op_Start_Effective_Date;
l_Op_Resource_Tbl(p_Count_r).Operation_Sequence_Number := c_Cur_Resource.Operation_Sequence_Number;
l_Op_Resource_Tbl(p_Count_r).Resource_Sequence_Number := c_Cur_Resource.Resource_Sequence_Number;
l_Op_Resource_Tbl(p_Count_r).Resource_Code := c_Cur_Resource.Resource_Code;
l_Op_Resource_Tbl(p_Count_r).Usage_Rate_Or_Amount := c_Cur_Resource.Usage_Rate_Or_Amount;
l_Op_Resource_Tbl(p_Count_r).Schedule_Flag := c_Cur_Resource.Schedule_Flag;
l_Op_Resource_Tbl(p_Count_r).Autocharge_Type := c_Cur_Resource.Autocharge_Type;
l_Op_Resource_Tbl(p_Count_r).Transaction_Type := p_Transaction_Type;
l_Op_Resource_Tbl(p_Count_r).Assigned_Units := c_Cur_Resource.Assigned_Units;
l_Op_Resource_Tbl(p_Count_r).Attribute1 := c_Cur_Resource.Attribute1;
l_Op_Resource_Tbl(p_Count_r).Attribute3 := c_Cur_Resource.Attribute3;
p_Count_r := p_Count_r + 1;
End Loop;
Close Cur_Resource;
p_Count_s := p_Count_s + 1;
End Loop;
Close Cur_Operation;
Elsif p_Transaction_Type = 'DELETE' Then
l_Rtg_Header_Rec.Transaction_Type := p_Transaction_Type;
l_Rtg_Header_Rec.Assembly_Item_Name := c_Cur_Main.Assembly_Item_Number;
l_Rtg_Header_Rec.Organization_Code := c_Cur_Main.Organization_Code;
l_Rtg_Header_Rec.Delete_Group_Name := 'DELROU';
Elsif p_Transaction_Type = 'UPDATE' Then
l_Rtg_Header_Rec.Transaction_Type := p_Transaction_Type;
l_Rtg_Header_Rec.Assembly_Item_Name := c_Cur_Main.Assembly_Item_Number;
l_Rtg_Header_Rec.Organization_Code := c_Cur_Main.Organization_Code;
l_Rtg_Header_Rec.Alternate_Routing_Code := c_Cur_Main.Alternate_Routing_Designator;
l_Rtg_Header_Rec.Attribute1 := c_Cur_Main.Attribute1;
l_Rtg_Header_Rec.Attribute2 := c_Cur_Main.Attribute2;
--l_Rtg_Header_Rec := Bom_Rtg_Pub.g_Miss_Rtg_Header_Rec;
p_Count_s := 1;
p_Count_r := 1;
Open Cur_Operation(c_Cur_Main.Routing_Sequence_Id);
--l_Operation_Tbl := Bom_Rtg_Pub.Operation_Tbl_Type;
Loop
Fetch Cur_Operation
Into c_Cur_Operation;
Exit When Cur_Operation%Notfound;
p_Operation_Type := c_Cur_Operation.Transaction_Type;
If Nvl(p_Operation_Type, '~') != '~' Then
--Debug_Show(' -->>事物处理类型：' || p_Transaction_Type);
l_Operation_Tbl(p_Count_s).Assembly_Item_Name := c_Cur_Main.Assembly_Item_Number;
l_Operation_Tbl(p_Count_s).Organization_Code := c_Cur_Main.Organization_Code;
l_Operation_Tbl(p_Count_s).Alternate_Routing_Code := c_Cur_Main.Alternate_Routing_Designator;
l_Operation_Tbl(p_Count_s).Operation_Sequence_Number := c_Cur_Operation.Operation_Sequence_Number;
l_Operation_Tbl(p_Count_s).Start_Effective_Date := c_Cur_Operation.Start_Effective_Date;
l_Operation_Tbl(p_Count_s).Transaction_Type := p_Operation_Type;
If p_Operation_Type = 'DELETE' Then
l_Operation_Tbl(p_Count_s).Delete_Group_Name := 'DELROUL'; --p_Operation_Tbl(i)
Else
l_Operation_Tbl(p_Count_s).Operation_Type := 1;
l_Operation_Tbl(p_Count_s).Department_Code := c_Cur_Operation.Department_Code;
l_Operation_Tbl(p_Count_s).Operation_Description := c_Cur_Operation.Operation_Description;
l_Operation_Tbl(p_Count_s).Attribute7 := c_Cur_Operation.Attribute7;
End If;
End If;
Open Cur_Resource(c_Cur_Operation.Operation_Sequence_Id);
Loop
Fetch Cur_Resource
Into c_Cur_Resource;
Exit When Cur_Resource%Notfound;
p_Resource_Type := c_Cur_Resource.Transaction_Type;
l_Op_Resource_Tbl(p_Count_r).Assembly_Item_Name := c_Cur_Main.Assembly_Item_Number;
l_Op_Resource_Tbl(p_Count_r).Organization_Code := c_Cur_Main.Organization_Code;
l_Op_Resource_Tbl(p_Count_r).Alternate_Routing_Code := c_Cur_Main.Alternate_Routing_Designator;
--l_Op_Resource_Tbl(i).Operation_Type := 1;
l_Op_Resource_Tbl(p_Count_r).Op_Start_Effective_Date := c_Cur_Operation.Start_Effective_Date;
l_Op_Resource_Tbl(p_Count_r).Operation_Sequence_Number := c_Cur_Operation.Operation_Sequence_Number;
l_Op_Resource_Tbl(p_Count_r).Resource_Sequence_Number := c_Cur_Resource.Resource_Sequence_Number;
l_Op_Resource_Tbl(p_Count_r).Transaction_Type := p_Resource_Type;
l_Op_Resource_Tbl(p_Count_r).Resource_Code := c_Cur_Resource.Resource_Code;
l_Op_Resource_Tbl(p_Count_r).Usage_Rate_Or_Amount := c_Cur_Resource.Usage_Rate_Or_Amount;
l_Op_Resource_Tbl(p_Count_r).Schedule_Flag := c_Cur_Resource.Schedule_Flag;
l_Op_Resource_Tbl(p_Count_r).Autocharge_Type := c_Cur_Resource.Autocharge_Type;
l_Op_Resource_Tbl(p_Count_r).Assigned_Units := c_Cur_Resource.Assigned_Units;
l_Op_Resource_Tbl(p_Count_r).Attribute1 := c_Cur_Resource.Attribute1;
l_Op_Resource_Tbl(p_Count_r).Attribute3 := c_Cur_Resource.Attribute3;
p_Count_r := p_Count_r + 1;
End Loop;
Close Cur_Resource;
p_Count_s := p_Count_s + 1;
End Loop;
Close Cur_Operation;
End If;
Cux_Pdm_Erp_Sync_Pkg.Rtg_Sync_Proc --
(p_Transaction_Id => l_Transaction_Id,
p_Rtg_Header_Rec => l_Rtg_Header_Rec,
p_Rtg_Revision_Tbl => l_Rtg_Revision_Tbl,
p_Operation_Tbl => l_Operation_Tbl,
p_Op_Resource_Tbl => l_Op_Resource_Tbl,
p_Sub_Resource_Tbl => l_Sub_Resource_Tbl,
p_Op_Network_Tbl => l_Op_Network_Tbl,
x_Return_Status => l_Return_Status,
x_Return_Message => l_Return_Message);
-- Debug_Show('=================================================');
-- Debug_Show(x_return_status);
-- Debug_Show(x_return_message);
-- t_total_num := t_total_num + 1;
-- IF l_return_status = 'S' THEN
-- t_success_num := t_success_num + 1;
-- END IF;
p_Return_Status := l_Return_Status;
Update Cux_Routing_Head_Interface k
Set k.Return_Status = l_Return_Status,
k.Error_Msg = Substr(l_Return_Message, 1, 2000)
Where k.Routing_Sequence_Id = c_Cur_Main.Routing_Sequence_Id;
/*
select mod(t_total_num,50) into p_commit_count from dual ;
if p_commit_count =0 then
commit;
end if;
*/
----Commit;
End Loop;
Close Cur_Main;
----Commit;
End;
FUNCTION get_locator_id(p_organization_id number,
p_subinv_code VARCHAR2,
p_locator_code VARCHAR2) RETURN NUMBER is
x_return_status VARCHAR2(1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(1000);
x_inventory_location_id NUMBER;
x_locator_exists VARCHAR2(100);
v_ORGANIZATION_CODE varchar2(10);
BEGIN
BEGIN
fnd_profile.put('MFG_ORGANIZATION_ID', p_organization_id);
END;
select ORGANIZATION_CODE
into v_ORGANIZATION_CODE
from org_organization_definitions
where organization_id = p_organization_id;
inv_loc_wms_pub.create_locator(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_inventory_location_id => x_inventory_location_id,
x_locator_exists => x_locator_exists,
p_organization_id => p_organization_id,
p_organization_code => v_organization_code,
p_concatenated_segments => p_locator_code,
p_description => NULL,
p_inventory_location_type => NULL,
p_picking_order => NULL,
p_location_maximum_units => NULL,
p_subinventory_code => p_subinv_code,
p_location_weight_uom_code => NULL,
p_max_weight => NULL,
p_volume_uom_code => NULL,
p_max_cubic_area => NULL,
p_x_coordinate => NULL,
p_y_coordinate => NULL,
p_z_coordinate => NULL,
p_physical_location_id => NULL,
p_pick_uom_code => NULL,
p_dimension_uom_code => NULL,
p_length => NULL,
p_width => NULL,
p_height => NULL,
p_status_id => 1,
p_dropping_order => NULL);
COMMIT;
RETURN x_inventory_location_id;
end;
Begin
--Fnd_Global.Apps_Initialize(g_User_Id, g_Resp_Id, g_Resp_Appl_Id);
Null;
End Cux_Pdm_Erp_Sync_Pkg;
3588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
