QQ:16441708
MSN:Baconxu@hotmail.com
主需求平台：包Cux_Pss_Relief_Pkg
主需求名称：表Cux_Schedule_Designators
预告：表Cux_Forecast_Items
船期：表Cux_Sailing_Items
预告冲减和船期冲减都是同一个表 Cux_Schedule_Comsumptions
Cux_Relief_Interface 和 Cux_Relief_Transaction 临时表存储信息
CREATE OR REPLACE Package Body Cux_Pss_Relief_Pkg Is
/* $Header: cuxpssrb.pls 1.0.0 2006/08/18 17:37:35 Benny.tang@hand $ */
------------------- Global variables ------------------------
g_Output Number := Fnd_File.Output;
g_Log Number := Fnd_File.Log;
g_Trace_On Varchar2(1) := 'N';
g_Sob_Id Number;
g_Org_Id Number;
g_User_Exception Exception;
g_Package_Name Varchar2(30) := 'Cux_Pss_Relief_Pkg.';
g_Blank Varchar2(2) := ' ';
---------------------- Who variables -------------------------
g_Last_Updated_By Number := Fnd_Global.User_Id;
g_Last_Update_Login Number := Fnd_Global.Conc_Login_Id;
g_Prog_Appl_Id Number := Fnd_Global.Prog_Appl_Id;
g_Prog_Id Number := Fnd_Global.Conc_Program_Id;
g_Request_Id Number := Fnd_Global.Conc_Request_Id;
--------------------------------------------------------------
Procedure Relief(x_Schedule_Designator Varchar2,
x_Organization_Id Number) As
l_Progress Varchar2(500);
Cursor c_Shipped_Trx Is
Select Ri.Rowid Row_Id, Ri.*
From Cux_Relief_Interface Ri
Where Ri.Process_Status = 3;
Cursor c_Schedule_Designator Is
Select t.Schedule_Designator, t.Organization_Id
From Cux_Schedule_Designators t
Where (t.Disable_Date Is Null Or t.Disable_Date > Sysdate)
And t.Organization_Id = x_Organization_Id
And t.Schedule_Designator =
Nvl(x_Schedule_Designator, t.Schedule_Designator);
Cursor c_Sailing(p_Inventory_Item_Id Number, p_Schedule_Designator Varchar2) Is
Select Si.Rowid Row_Id, Si.*
From Cux_Sailing_Items Si
Where Si.Organization_Id = x_Organization_Id
And Si.Schedule_Designator = p_Schedule_Designator
And Si.Inventory_Item_Id = p_Inventory_Item_Id
And Nvl(Si.Required_Quantity, 0) > 0
Order By Si.Need_By_Date Asc;
l_Error_Msg Varchar2(240);
l_Shipped_Qty Number := 0;
l_Relief_Qty Number := 0;
l_Order_Line_Id Number;
l_Order_Header_Id Number;
l_Sales_Order_Id Number;
l_Order_Date Date;
l_Order_Line_Qty Number;
l_Order_Line_Num Varchar2(10);
Begin
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || 'Start Program: ' ||
g_Package_Name || 'Relief');
End If;
------------------------------------------------
-- Step: 1
l_Progress := '冲减船期';
------------------------------------------------
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || l_Progress);
End If;
For Trx In c_Shipped_Trx Loop
l_Progress := '当前处理发运记录: ' || To_Char(Trx.Transaction_Id);
Begin
For Rec In c_Schedule_Designator Loop
l_Progress := '当前处理主需求计划: ' || Rec.Schedule_Designator;
l_Shipped_Qty := Nvl(Trx.Trx_Quantity, 0);
--get order information
l_Order_Line_Id := Trx.Order_Line_Id;
Get_Order_Info(x_Order_Line_Id => l_Order_Line_Id,
x_Order_Header_Id => l_Order_Header_Id,
x_Sales_Order_Id => l_Sales_Order_Id,
x_Order_Date => l_Order_Date,
x_Order_Line_Qty => l_Order_Line_Qty,
x_Order_Line_Num => l_Order_Line_Num);
--relief
For Sailing In c_Sailing(Trx.Inventory_Item_Id,
Rec.Schedule_Designator) Loop
l_Progress := '当前处理船期记录: ' ||
To_Char(Sailing.Sailing_Line_Id);
If l_Shipped_Qty = 0 Then
Exit;
End If;
If Nvl(Sailing.Required_Quantity, 0) >= l_Shipped_Qty Then
l_Relief_Qty := l_Shipped_Qty;
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := 0;
Exit; -- exit current loop
Else
l_Relief_Qty := Nvl(Sailing.Required_Quantity, 0) /*-
Nvl(Sailing.Relief_Quantity, 0)*/
;
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := l_Shipped_Qty - l_Relief_Qty;
End If;
End Loop;
--update relief interface
End Loop;
Update Cux_Relief_Interface t
Set t.Process_Status = 5
Where t.Rowid = Trx.Row_Id;
Exception
When Others Then
l_Error_Msg := Sqlerrm;
Rollback;
Update Cux_Relief_Interface t
Set t.Process_Status = 4, t.Error_Msg = l_Error_Msg
Where t.Rowid = Trx.Row_Id;
End;
Commit;
End Loop;
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || 'End Program: ' ||
g_Package_Name || 'Relief');
End If;
End;
Procedure Mds_Relief(Errbuf Out Nocopy Varchar2,
Retcode Out Nocopy Number,
x_Organization_Id Number,
x_Schedule_Designator Varchar2,
x_Trace_On Varchar2) As
l_Progress Varchar2(500);
l_Conc_Status Boolean;
l_Current_Error_Code Varchar2(20) := Null;
l_Err_Msg Varchar2(500);
l_Err_Code Number;
Cursor c_Schedule_Designator Is
Select t.Schedule_Designator, t.Organization_Id
From Cux_Schedule_Designators t
Where (t.Disable_Date Is Null Or t.Disable_Date > Sysdate)
And t.Organization_Id = x_Organization_Id
And t.Schedule_Designator =
Nvl(x_Schedule_Designator, t.Schedule_Designator);
Begin
g_Trace_On := Nvl(x_Trace_On, 'N');
Cux_Public_Pkg.Log_Start(Buff => '需求冲减');
Cux_Public_Pkg.Blank_Line;
------------------------------------------------
-- Step: 001
l_Progress := '验证参数';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
/*If x_Schedule_Designator Is Null Then
Cux_Public_Pkg.Message(Buff => g_Blank ||
'警告:''需求计划名称(Schedule_Designator)''参数不能为空,请重新提交迸发程序.');
Raise g_User_Exception;
End If;*/
If x_Organization_Id Is Null Then
Cux_Public_Pkg.Message(Buff => g_Blank ||
'警告:''组织标识(Organization_Id)''参数不能为空,请重新提交迸发程序.');
Raise g_User_Exception;
End If;
------------------------------------------------
-- Step: 002
l_Progress := '标记/锁定要处理的数据';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
Update Cux_Relief_Interface Ri
Set Ri.Process_Status = 3
Where Ri.Process_Status In (2, 4)
And Nvl(Trx_Quantity, 0) > 0; --waiting/error
Commit;
------------------------------------------------
-- Step: 003
l_Progress := '船期冲减';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
--For Rec In c_Schedule_Designator Loop
-- Relief(Rec.Schedule_Designator, Rec.Organization_Id);
--End Loop;
Relief(x_Schedule_Designator, x_Organization_Id);
Cux_Public_Pkg.Log_Start(Buff => '完成需求冲减');
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Log_End;
Commit;
Exception
When g_User_Exception Then
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Message(Buff => '+===========================================================================+');
Cux_Public_Pkg.Message(Buff => '因为存在以上警告,迸发程序中止运行; 请更正后重新运行迸发程序. ');
Cux_Public_Pkg.Log_End;
Retcode := 1;
l_Conc_Status := Fnd_Concurrent.Set_Completion_Status('WARNING',
l_Current_Error_Code);
When Others Then
Cux_Public_Pkg.Message(Buff => '错误:' || Sqlcode || ':' || Sqlerrm);
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Message(Buff => '+===========================================================================+');
Cux_Public_Pkg.Message(Buff => '程序运行过程中出现异常,请与系统管理员联系.');
Cux_Public_Pkg.Log_End;
Retcode := 2;
l_Conc_Status := Fnd_Concurrent.Set_Completion_Status('ERROR',
l_Current_Error_Code);
End;
Procedure Get_Order_Info(x_Order_Line_Id In Number,
x_Order_Header_Id In Out Number,
x_Sales_Order_Id Out Number,
x_Order_Date Out Date,
x_Order_Line_Qty Out Number,
x_Order_Line_Num Out Varchar2) As
l_Order_Number Number;
Begin
Select l.Header_Id,
l.Ordered_Quantity,
To_Char(l.Line_Number) || '.' || To_Char(l.Shipment_Number)
Into x_Order_Header_Id, x_Order_Line_Qty, x_Order_Line_Num
From Oe_Order_Lines_All l
Where l.Line_Id = x_Order_Line_Id;
Select h.Ordered_Date, h.Order_Number
Into x_Order_Date, l_Order_Number
From Oe_Order_Headers_All h
Where h.Header_Id = x_Order_Header_Id;
Select k.Sales_Order_Id
Into x_Sales_Order_Id
From Mtl_Sales_Orders_Kfv k
Where k.Segment1 = To_Char(l_Order_Number);
Exception
When Others Then
x_Order_Header_Id := Null;
x_Sales_Order_Id := Null;
x_Order_Date := Null;
x_Order_Line_Qty := Null;
x_Order_Line_Num := Null;
End;
Procedure Trx_Relief(x_Schedule_Designator Varchar2,
x_Organization_Id Number) As
l_Progress Varchar2(500);
Cursor c_Shipped_Trx Is
Select Ri.Rowid Row_Id, Ri.*
From Cux_Relief_Interface Ri
Where Ri.Organization_Id = x_Organization_Id
And Ri.Process_Status = 3
And Ri.Trx_Quantity > 0;
Cursor c_Return_Trx Is
Select Ri.Rowid Row_Id, Ri.*
From Cux_Relief_Interface Ri
Where Ri.Organization_Id = x_Organization_Id
And Ri.Process_Status = 3
And Ri.Trx_Quantity < 0;
Cursor c_Schedule_Designator Is
Select t.Schedule_Designator, t.Organization_Id
From Cux_Schedule_Designators t
Where (t.Disable_Date Is Null Or t.Disable_Date > Sysdate)
And t.Organization_Id = x_Organization_Id
And t.Schedule_Designator =
Nvl(x_Schedule_Designator, t.Schedule_Designator);
Cursor c_Forecast_Asc(p_Inventory_Item_Id Number, p_Schedule_Designator Varchar2) Is
Select Fi.Rowid Row_Id, Fi.*
From Cux_Forecast_Items Fi
Where Fi.Organization_Id = x_Organization_Id
And Fi.Schedule_Designator = p_Schedule_Designator
And Fi.Inventory_Item_Id = p_Inventory_Item_Id
And Nvl(Fi.Required_Quantity, 0) > 0
Order By Fi.Need_By_Date Asc;
Cursor c_Forecast_Desc(p_Inventory_Item_Id Number, p_Schedule_Designator Varchar2) Is
Select Fi.Rowid Row_Id, Fi.*
From Cux_Forecast_Items Fi
Where Fi.Organization_Id = x_Organization_Id
And Fi.Schedule_Designator = p_Schedule_Designator
And Fi.Inventory_Item_Id = p_Inventory_Item_Id
And Nvl(Fi.Relief_Quantity, 0) > 0
Order By Fi.Need_By_Date Desc;
Cursor c_Sailing_Asc(p_Inventory_Item_Id Number, p_Schedule_Designator Varchar2) Is
Select Si.Rowid Row_Id, Si.*
From Cux_Sailing_Items Si
Where Si.Organization_Id = x_Organization_Id
And Si.Schedule_Designator = p_Schedule_Designator
And Si.Inventory_Item_Id = p_Inventory_Item_Id
And Nvl(Si.Required_Quantity, 0) > 0
Order By Si.Need_By_Date Asc;
Cursor c_Sailing_Desc(p_Inventory_Item_Id Number, p_Schedule_Designator Varchar2) Is
Select Si.Rowid Row_Id, Si.*
From Cux_Sailing_Items Si
Where Si.Organization_Id = x_Organization_Id
And Si.Schedule_Designator = p_Schedule_Designator
And Si.Inventory_Item_Id = p_Inventory_Item_Id
And Nvl(Si.Relief_Quantity, 0) > 0
Order By Si.Need_By_Date Desc;
l_Error_Msg Varchar2(240);
l_Shipped_Qty Number := 0;
l_Relief_Qty Number := 0;
l_Order_Line_Id Number;
l_Order_Header_Id Number;
l_Sales_Order_Id Number;
l_Order_Date Date;
l_Order_Line_Qty Number;
l_Order_Line_Num Varchar2(10);
Begin
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || 'Start Program: ' ||
g_Package_Name || 'Relief');
End If;
------------------------------------------------
-- Step: 1
l_Progress := '发运冲减需求';
------------------------------------------------
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || l_Progress);
End If;
For Trx In c_Shipped_Trx Loop
l_Progress := '当前处理发运事务/数量: ' ||
To_Char(Trx.Transaction_Id) || '/' ||
Nvl(Trx.Trx_Quantity, 0);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || l_Progress);
End If;
Begin
For Rec In c_Schedule_Designator Loop
l_Progress := '当前处理主需求计划: ' || Rec.Schedule_Designator;
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
l_Progress);
End If;
l_Shipped_Qty := Nvl(Trx.Trx_Quantity, 0);
--Forecast
For Forecast In c_Forecast_Asc(Trx.Inventory_Item_Id,
Rec.Schedule_Designator) Loop
l_Progress := '当前处理预告记录/需求数量/发运剩余: ' ||
To_Char(Forecast.Forecast_Line_Id || '/' ||
Nvl(Forecast.Required_Quantity, 0) || '/' ||
l_Shipped_Qty);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
g_Blank || l_Progress);
End If;
If l_Shipped_Qty = 0 Then
Exit;
End If;
If Nvl(Forecast.Required_Quantity, 0) >= l_Shipped_Qty Then
l_Relief_Qty := l_Shipped_Qty;
--update forecast
Update Cux_Forecast_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Forecast.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('FORECAST',
Forecast.Forecast_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := 0;
Exit; -- exit current loop
Else
l_Relief_Qty := Nvl(Forecast.Required_Quantity, 0);
--update forecast
Update Cux_Forecast_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Forecast.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('FORECAST',
Forecast.Forecast_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := l_Shipped_Qty - l_Relief_Qty;
End If;
End Loop;
--------------------------------------------------------
l_Shipped_Qty := Nvl(Trx.Trx_Quantity, 0);
--Sailing
For Sailing In c_Sailing_Asc(Trx.Inventory_Item_Id,
Rec.Schedule_Designator) Loop
l_Progress := '当前处理船期记录/需求数量/发运剩余: ' ||
To_Char(Sailing.Sailing_Line_Id || '/' ||
Nvl(Sailing.Required_Quantity, 0) || '/' ||
l_Shipped_Qty);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
g_Blank || l_Progress);
End If;
If l_Shipped_Qty = 0 Then
Exit;
End If;
If Nvl(Sailing.Required_Quantity, 0) >= l_Shipped_Qty Then
l_Relief_Qty := l_Shipped_Qty;
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := 0;
Exit; -- exit current loop
Else
l_Relief_Qty := Nvl(Sailing.Required_Quantity, 0);
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) +
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) -
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := l_Shipped_Qty - l_Relief_Qty;
End If;
End Loop;
End Loop;
--update relief interface
Update Cux_Relief_Interface t
Set t.Process_Status = 5
Where t.Rowid = Trx.Row_Id;
Exception
When Others Then
l_Error_Msg := Sqlerrm;
Rollback;
Update Cux_Relief_Interface t
Set t.Process_Status = 4, t.Error_Msg = l_Error_Msg
Where t.Rowid = Trx.Row_Id;
End;
Commit;
End Loop;
------------------------------------------------
-- Step: 2
l_Progress := '退货冲减需求';
------------------------------------------------
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || l_Progress);
End If;
For Trx In c_Return_Trx Loop
l_Progress := '当前处理发运退货事务/退货数量: ' ||
To_Char(Trx.Transaction_Id) || '/' ||
Nvl(-trx.Trx_Quantity, 0);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || l_Progress);
End If;
Begin
For Rec In c_Schedule_Designator Loop
l_Progress := '当前处理主需求计划: ' || Rec.Schedule_Designator;
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
l_Progress);
End If;
l_Shipped_Qty := -nvl(Trx.Trx_Quantity, 0); --转为正
--Forecast
For Forecast In c_Forecast_Desc(Trx.Inventory_Item_Id,
Rec.Schedule_Designator) Loop
l_Progress := '当前处理预告记录/已冲减数量/退货数量: ' ||
To_Char(Forecast.Forecast_Line_Id || '/' ||
Nvl(Forecast.Relief_Quantity, 0) || '/' ||
l_Shipped_Qty);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
g_Blank || l_Progress);
End If;
If l_Shipped_Qty = 0 Then
Exit;
End If;
If Nvl(Forecast.Relief_Quantity, 0) >= l_Shipped_Qty Then
l_Relief_Qty := l_Shipped_Qty;
--update Forecast
Update Cux_Forecast_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) -
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) +
l_Relief_Qty
Where t.Rowid = Forecast.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('FORECAST',
Forecast.Forecast_Line_Id,
-l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := 0;
Exit; -- exit current loop
Else
l_Relief_Qty := Nvl(Forecast.Relief_Quantity, 0);
--update Forecast
Update Cux_Forecast_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) -
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) +
l_Relief_Qty
Where t.Rowid = Forecast.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('FORECAST',
Forecast.Forecast_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := l_Shipped_Qty - l_Relief_Qty;
End If;
End Loop;
---------------------------------------------------------------------
l_Shipped_Qty := Nvl(Trx.Trx_Quantity, 0);
--relief
For Sailing In c_Sailing_Desc(Trx.Inventory_Item_Id,
Rec.Schedule_Designator) Loop
l_Progress := '当前处理船期记录/已冲减数量/退货数量: ' ||
To_Char(Sailing.Sailing_Line_Id || '/' ||
Nvl(Sailing.Relief_Quantity, 0) || '/' ||
l_Shipped_Qty);
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || g_Blank || g_Blank ||
g_Blank || l_Progress);
End If;
If l_Shipped_Qty = 0 Then
Exit;
End If;
If Nvl(Sailing.Relief_Quantity, 0) >= l_Shipped_Qty Then
l_Relief_Qty := l_Shipped_Qty;
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) -
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) +
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
-l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := 0;
Exit; -- exit current loop
Else
l_Relief_Qty := Nvl(Sailing.Relief_Quantity, 0);
--update sailing
Update Cux_Sailing_Items t
Set t.Relief_Quantity = Nvl(t.Relief_Quantity, 0) -
l_Relief_Qty,
t.Required_Quantity = Nvl(t.Required_Quantity, 0) +
l_Relief_Qty
Where t.Rowid = Sailing.Row_Id;
--insert consumptions
Insert Into Cux_Schedule_Consumptions
(Relief_Source,
Relief_Source_Id,
Relief_Quantity,
Transaction_Id,
Sales_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Date,
Order_Line_Num,
Order_Line_Quantity,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
('SAILING',
Sailing.Sailing_Line_Id,
l_Relief_Qty,
Trx.Transaction_Id,
l_Sales_Order_Id,
l_Order_Header_Id,
l_Order_Line_Id,
l_Order_Date,
l_Order_Line_Num,
l_Order_Line_Qty,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
l_Shipped_Qty := l_Shipped_Qty - l_Relief_Qty;
End If;
End Loop;
--
End Loop;
Update Cux_Relief_Interface t
Set t.Process_Status = 5,
t.Request_Id = g_Request_Id,
t.Last_Update_Date = Sysdate,
t.Last_Updated_By = g_Last_Updated_By,
t.Last_Update_Login = g_Last_Update_Login
Where t.Rowid = Trx.Row_Id;
Exception
When Others Then
l_Error_Msg := Sqlerrm;
Rollback;
Update Cux_Relief_Interface t
Set t.Process_Status = 4,
t.Error_Msg = l_Error_Msg,
t.Request_Id = g_Request_Id,
t.Last_Update_Date = Sysdate,
t.Last_Updated_By = g_Last_Updated_By,
t.Last_Update_Login = g_Last_Update_Login
Where t.Rowid = Trx.Row_Id;
End;
Commit;
End Loop;
If g_Trace_On = 'Y' Then
Cux_Public_Pkg.Message(Buff => g_Blank || 'End Program: ' ||
g_Package_Name || 'Relief');
End If;
End;
Procedure Inv_Trx_Relief(Errbuf Out Nocopy Varchar2,
Retcode Out Nocopy Number,
x_Organization_Id Number,
x_Schedule_Designator Varchar2,
x_Trace_On Varchar2) As
l_Progress Varchar2(500);
l_Conc_Status Boolean;
l_Current_Error_Code Varchar2(20) := Null;
l_Err_Msg Varchar2(500);
l_Err_Code Number;
Cursor c_Relief_Trx Is
Select /* + rule */
Mmt.Transaction_Id,
Mmt.Transaction_Quantity,
Mmt.Transaction_Date,
Mmt.Inventory_Item_Id
From Mtl_Material_Transactions Mmt, Wsh_Shipping_Parameters Wsp
Where Mmt.Organization_Id = x_Organization_Id
And Mmt.Transaction_Date >=
Decode(Sign(Trunc(Sysdate - 10) -
Cux_Pss_Constants_Pkg.Run_Date),
1,
Trunc(Sysdate - 10),
0,
Trunc(Sysdate - 10),
-1,
Cux_Pss_Constants_Pkg.Run_Date)
And Mmt.Transaction_Type_Id != 33 --发运确认
And Mmt.Transaction_Quantity != 0
And Wsp.Organization_Id = x_Organization_Id
And Mmt.Subinventory_Code = Wsp.Default_Stage_Subinventory
And Not Exists
(Select 'y'
From Cux_Relief_Transactions Rt
Where Rt.Transaction_Id = Mmt.Transaction_Id)
Union All
Select /* + rule */
Mmt.Transaction_Id,
-mmt.Transaction_Quantity Transaction_Quantity,
Mmt.Transaction_Date,
Mmt.Inventory_Item_Id
From Mtl_Material_Transactions Mmt
Where Mmt.Organization_Id = x_Organization_Id
And Mmt.Transaction_Date >=
Decode(Sign(Trunc(Sysdate - 10) -
Cux_Pss_Constants_Pkg.Run_Date),
1,
Trunc(Sysdate - 10),
0,
Trunc(Sysdate - 10),
-1,
Cux_Pss_Constants_Pkg.Run_Date)
And Mmt.Transaction_Type_Id = 15 --RMA
And Mmt.Transaction_Quantity != 0
And Not Exists
(Select 'y'
From Cux_Relief_Transactions Rt
Where Rt.Transaction_Id = Mmt.Transaction_Id);
Cursor c_Schedule_Designator Is
Select t.Schedule_Designator, t.Organization_Id
From Cux_Schedule_Designators t
Where (t.Disable_Date Is Null Or t.Disable_Date > Sysdate)
And t.Organization_Id = x_Organization_Id
And t.Schedule_Designator =
Nvl(x_Schedule_Designator, t.Schedule_Designator);
Begin
g_Trace_On := Nvl(x_Trace_On, 'N');
Cux_Public_Pkg.Log_Start(Buff => '需求冲减');
Cux_Public_Pkg.Blank_Line;
------------------------------------------------
-- Step: 000
l_Progress := '验证参数';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
If x_Organization_Id Is Null Then
Cux_Public_Pkg.Message(Buff => g_Blank ||
'警告:''组织标识(Organization_Id)''参数不能为空,请重新提交迸发程序.');
Raise g_User_Exception;
End If;
------------------------------------------------
-- Step: 001
l_Progress := '同步发运事务处理';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
For Rec In c_Relief_Trx Loop
Insert Into Cux.Cux_Relief_Interface
(Transaction_Id,
Inventory_Item_Id,
Organization_Id,
Original_Trx_Quantity,
Trx_Quantity,
Trx_Date,
Salse_Order_Id,
Order_Header_Id,
Order_Line_Id,
Order_Line_Qty,
Process_Status,
Error_Msg,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
(Rec.Transaction_Id,
Rec.Inventory_Item_Id,
x_Organization_Id,
Rec.Transaction_Quantity,
Rec.Transaction_Quantity,
Rec.Transaction_Date,
Null,
Null,
Null,
Null,
2, --waitting
Null,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
Insert Into Cux_Relief_Transactions
(Transaction_Id,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
Values
(Rec.Transaction_Id,
Sysdate,
g_Last_Updated_By,
Sysdate,
g_Last_Updated_By,
g_Last_Update_Login,
g_Request_Id,
g_Prog_Appl_Id,
g_Prog_Id,
Sysdate);
End Loop;
Commit;
------------------------------------------------
-- Step: 002
l_Progress := '标记/锁定要处理的数据';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
Update Cux_Relief_Interface Ri
Set Ri.Process_Status = 3,
Ri.Request_Id = g_Request_Id,
Ri.Last_Update_Date = Sysdate,
Ri.Last_Updated_By = g_Last_Updated_By,
Ri.Last_Update_Login = g_Last_Update_Login
Where Ri.Organization_Id = x_Organization_Id
And Ri.Process_Status In (2, 4)
And Nvl(Trx_Quantity, 0) != 0; --waiting/error
Commit;
------------------------------------------------
-- Step: 003
l_Progress := '需求冲减';
------------------------------------------------
Cux_Public_Pkg.Message(Buff => '>> ' || l_Progress);
Trx_Relief(x_Schedule_Designator, x_Organization_Id);
Cux_Public_Pkg.Log_Start(Buff => '完成需求冲减');
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Log_End;
Commit;
Exception
When g_User_Exception Then
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Message(Buff => '+===========================================================================+');
Cux_Public_Pkg.Message(Buff => '因为存在以上警告,迸发程序中止运行; 请更正后重新运行迸发程序. ');
Cux_Public_Pkg.Log_End;
Retcode := 1;
l_Conc_Status := Fnd_Concurrent.Set_Completion_Status('WARNING',
l_Current_Error_Code);
When Others Then
Cux_Public_Pkg.Message(Buff => '错误:' || Sqlcode || ':' || Sqlerrm);
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Blank_Line;
Cux_Public_Pkg.Message(Buff => '+===========================================================================+');
Cux_Public_Pkg.Message(Buff => '程序运行过程中出现异常,请与系统管理员联系.');
Cux_Public_Pkg.Log_End;
Retcode := 2;
l_Conc_Status := Fnd_Concurrent.Set_Completion_Status('ERROR',
l_Current_Error_Code);
End;
End Cux_Pss_Relief_Pkg;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
