创建销售订单退货单API
先ENTERED再BOOKED
--创建销售订单退货单
DECLARE
v_Api_Version_Number NUMBER := 1;
v_Return_Status VARCHAR2(2000);
v_Msg_Count NUMBER;
v_Msg_Data VARCHAR2(2000);
-- IN Variables --
l_Header_Rec Oe_Order_Pub.Header_Rec_Type;
l_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
l_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
v_Line_Adj_Tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
-- OUT Variables --
x_Header_Rec Oe_Order_Pub.Header_Rec_Type;
x_Line_Tbl Oe_Order_Pub.Line_Tbl_Type;
x_Header_Val_Rec Oe_Order_Pub.Header_Val_Rec_Type;
x_Header_Adj_Tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
x_Header_Adj_Val_Tbl Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
x_Header_Price_Att_Tbl Oe_Order_Pub.Header_Price_Att_Tbl_Type;
x_Header_Adj_Att_Tbl Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
x_Header_Adj_Assoc_Tbl Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
x_Header_Scredit_Tbl Oe_Order_Pub.Header_Scredit_Tbl_Type;
x_Header_Scredit_Val_Tbl Oe_Order_Pub.Header_Scredit_Val_Tbl_Type;
x_Line_Val_Tbl Oe_Order_Pub.Line_Val_Tbl_Type;
x_Line_Adj_Tbl Oe_Order_Pub.Line_Adj_Tbl_Type;
x_Line_Adj_Val_Tbl Oe_Order_Pub.Line_Adj_Val_Tbl_Type;
x_Line_Price_Att_Tbl Oe_Order_Pub.Line_Price_Att_Tbl_Type;
x_Line_Adj_Att_Tbl Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
x_Line_Adj_Assoc_Tbl Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
x_Line_Scredit_Tbl Oe_Order_Pub.Line_Scredit_Tbl_Type;
x_Line_Scredit_Val_Tbl Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
x_Lot_Serial_Tbl Oe_Order_Pub.Lot_Serial_Tbl_Type;
x_Lot_Serial_Val_Tbl Oe_Order_Pub.Lot_Serial_Val_Tbl_Type;
x_Action_Request_Tbl Oe_Order_Pub.Request_Tbl_Type;
l_Count NUMBER;
l_Return_Status VARCHAR2(100);
l_Msg_Data VARCHAR2(4000);
l_Msg_Count NUMBER := 0;
l_Error_Msg VARCHAR2(1000);
l_Line_Count NUMBER;
l_Error_Count NUMBER;
l_Msg_Index_Out NUMBER(10);
l_Order_Type_Id NUMBER;
l_Header_Id NUMBER;
BEGIN
Dbms_Output.Put_Line('Starting of script');
-- Setting the Enviroment --
Mo_Global.Init('ONT');
Fnd_Global.Apps_Initialize(User_Id => 0
,Resp_Id => 50795
,Resp_Appl_Id => 190);
Mo_Global.Set_Policy_Context('S'
,89);
v_Action_Request_Tbl(1) := Oe_Order_Pub.g_Miss_Request_Rec;
FOR r_Header IN c_Header LOOP
SELECT Oe_Order_Headers_s.Nextval
INTO l_Header_Id
FROM Dual;
l_Header_Rec.Request_Id := g_Request_Id;
l_Header_Rec.Request_Date := SYSDATE;
l_Header_Rec := Oe_Order_Pub.g_Miss_Header_Rec;
l_Header_Rec.Operation := Oe_Globals.g_Opr_Create;
l_Header_Rec.Packing_Instructions := r_Header.Packing_Instructions;
l_Header_Rec.Header_Id := l_Header_Id;
l_Header_Rec.Order_Type_Id := l_Order_Type_Id;
l_Header_Rec.Org_Id := g_Org_Id;
l_Header_Rec.Order_Source_Id := 2;
-- l_Header_Rec.Source_Document_Type_Id := 2;
l_Header_Rec.Orig_Sys_Document_Ref := r_Header.Order_Number;
-- l_Header_Rec.Source_Document_Id := r_Header.Header_Id;
l_Header_Rec.Order_Category_Code := 'RETURN';
l_Header_Rec.Lock_Control := 1;
l_Header_Rec.Cust_Po_Number := r_Header.Cust_Po_Number;
l_Header_Rec.Ship_From_Org_Id := r_Header.Ship_From_Org_Id;
l_Header_Rec.Sold_To_Org_Id := r_Header.Sold_To_Org_Id; --g_Order_Customer_Id;
l_Header_Rec.Ship_To_Org_Id := r_Header.Ship_To_Org_Id; --l_Ship_To_Org_Id;
l_Header_Rec.Invoice_To_Org_Id := r_Header.Invoice_To_Org_Id; --g_Cust_Bill_To_Org_Id;
l_Header_Rec.Sold_To_Contact_Id := r_Header.Sold_To_Contact_Id; --1602940;
l_Header_Rec.Ship_To_Contact_Id := r_Header.Ship_To_Contact_Id; --1602940;
l_Header_Rec.Deliver_To_Org_Id := r_Header.Deliver_To_Org_Id; --574823;
l_Header_Rec.Deliver_To_Contact_Id := r_Header.Deliver_To_Contact_Id; --2231149;
l_Header_Rec.Salesrep_Id := r_Header.Salesrep_Id;
l_Header_Rec.Flow_Status_Code := 'ENTERED'; -- 'BOOKED';
l_Header_Rec.Booked_Date := NULL; --
-- SYSDATE;
l_Header_Rec.Ordered_Date := SYSDATE;
l_Header_Rec.Transactional_Curr_Code := r_Header.Transactional_Curr_Code; ---'CNY' ;
l_Header_Rec.Fob_Point_Code := r_Header.Fob_Point_Code;
l_Header_Rec.Price_List_Id := r_Header.Price_List_Id;
l_Header_Rec.Conversion_Type_Code := l_Conversion_Type_Code;
l_Header_Rec.Shipment_Priority_Code := l_Shipment_Priority_Code;
l_Header_Rec.Shipping_Method_Code := NULL;
l_Header_Rec.Demand_Class_Code := l_Demand_Class_Code;
l_Header_Rec.Invoicing_Rule_Id := l_Invoicing_Rule_Id;
l_Header_Rec.Accounting_Rule_Id := l_Accounting_Rule_Id;
l_Header_Rec.Payment_Term_Id := r_Header.Payment_Term_Id;
l_Header_Rec.Creation_Date := SYSDATE;
l_Header_Rec.Created_By := g_User_Id;
l_Header_Rec.Last_Update_Date := SYSDATE;
l_Header_Rec.Last_Updated_By := g_User_Id;
l_Return_Status := 'Begin to get Standard PO line.';
l_Line_Tbl.Delete;
l_Count := 1;
FOR r_Line IN c_Line(r_Header.Inf_Header_Id) LOOP
-- Line Record --
l_Line_Tbl(l_Count) := Oe_Order_Pub.g_Miss_Line_Rec;
l_Line_Tbl(l_Count).Operation := Oe_Globals.g_Opr_Create;
l_Line_Tbl(l_Count).Header_Id := l_Header_Id;
l_Line_Tbl(l_Count).Line_Type_Id := 1229;
l_Line_Tbl(l_Count).Order_Source_Id := 2;
l_Line_Tbl(l_Count).Source_Document_Type_Id := 2;
l_Line_Tbl(l_Count).Line_Category_Code := 'RETURN';
l_Line_Tbl(l_Count).Return_Attribute1 := r_Header.Header_Id;
l_Line_Tbl(l_Count).Reference_Header_Id := r_Header.Header_Id;
l_Line_Tbl(l_Count).Source_Document_Id := r_Header.Header_Id;
l_Line_Tbl(l_Count).Return_Attribute2 := r_Line.Line_Id;
l_Line_Tbl(l_Count).Reference_Line_Id := r_Line.Line_Id;
l_Line_Tbl(l_Count).Source_Document_Line_Id := r_Line.Line_Id;
l_Line_Tbl(l_Count).Orig_Sys_Document_Ref := r_Header.Header_Id;
l_Line_Tbl(l_Count).Orig_Sys_Line_Ref := r_Line.Line_Id;
l_Line_Tbl(l_Count).Operation := Oe_Globals.g_Opr_Create;
l_Line_Tbl(l_Count).Return_Reason_Code := 'CANCELLATION';
l_Line_Tbl(l_Count).Salesrep_Id := r_Header.Salesrep_Id;
l_Line_Tbl(l_Count).Return_Context := 'ORDER';
l_Line_Tbl(l_Count).Inventory_Item_Id := r_Line.Inventory_Item_Id;
l_Line_Tbl(l_Count).Order_Quantity_Uom := r_Line.Order_Quantity_Uom;
l_Line_Tbl(l_Count).Ordered_Quantity := r_Line.Quantity;
l_Line_Tbl(l_Count).Unit_List_Price := r_Line.Unit_List_Price;
l_Line_Tbl(l_Count).Unit_Selling_Price := r_Line.Unit_Selling_Price;
l_Line_Tbl(l_Count).Option_Flag := 'N';
l_Line_Tbl(l_Count).Item_Type_Code := 'STANDARD';
l_Line_Tbl(l_Count).Org_Id := r_Line.Org_Id;
l_Line_Tbl(l_Count).Ship_From_Org_Id := r_Line.Ship_From_Org_Id;
l_Line_Tbl(l_Count).Calculate_Price_Flag := 'N';
l_Line_Tbl(l_Count).Tax_Exempt_Flag := 'S';
l_Line_Tbl(l_Count).Creation_Date := SYSDATE;
l_Line_Tbl(l_Count).Created_By := Fnd_Global.User_Id;
l_Line_Tbl(l_Count).Last_Update_Date := SYSDATE;
l_Line_Tbl(l_Count).Last_Updated_By := Fnd_Global.User_Id;
l_Count := l_Count + 1;
END LOOP;
l_Action_Request_Tbl(1).Entity_Code := Oe_Globals.g_Entity_Header;
Dbms_Output.Put_Line('Starting of API');
-- Calling the API to add a new line to an existing Order --
Oe_Order_Pub.Process_Order(p_Api_Version_Number => 1.0
,p_Init_Msg_List => Fnd_Api.g_False
,p_Return_Values => Fnd_Api.g_False
,x_Return_Status => x_Return_Status
,x_Msg_Count => l_Msg_Count
,x_Msg_Data => l_Msg_Data
,p_Org_Id => l_Header_Rec.Org_Id
,p_Header_Rec => l_Header_Rec
,p_Line_Tbl => l_Line_Tbl
,p_Action_Request_Tbl => l_Action_Request_Tbl
,x_Header_Rec => x_Header_Rec
,x_Header_Val_Rec => x_Header_Val_Rec
,x_Header_Adj_Tbl => x_Header_Adj_Tbl
,x_Header_Adj_Val_Tbl => x_Header_Adj_Val_Tbl
,x_Header_Price_Att_Tbl => x_Header_Price_Att_Tbl
,x_Header_Adj_Att_Tbl => x_Header_Adj_Att_Tbl
,x_Header_Adj_Assoc_Tbl => x_Header_Adj_Assoc_Tbl
,x_Header_Scredit_Tbl => x_Header_Scredit_Tbl
,x_Header_Scredit_Val_Tbl => x_Header_Scredit_Val_Tbl
,x_Line_Tbl => x_Line_Tbl
,x_Line_Val_Tbl => x_Line_Val_Tbl
,x_Line_Adj_Tbl => x_Line_Adj_Tbl
,x_Line_Adj_Val_Tbl => x_Line_Adj_Val_Tbl
,x_Line_Price_Att_Tbl => x_Line_Price_Att_Tbl
,x_Line_Adj_Att_Tbl => x_Line_Adj_Att_Tbl
,x_Line_Adj_Assoc_Tbl => x_Line_Adj_Assoc_Tbl
,x_Line_Scredit_Tbl => x_Line_Scredit_Tbl
,x_Line_Scredit_Val_Tbl => x_Line_Scredit_Val_Tbl
,x_Lot_Serial_Tbl => x_Lot_Serial_Tbl
,x_Lot_Serial_Val_Tbl => x_Lot_Serial_Val_Tbl
,x_Action_Request_Tbl => x_Action_Request_Tbl);
Dbms_Output.Put_Line('Completion of API');
Dbms_Output.Put_Line(x_Return_Status);
IF x_Return_Status = 'S' THEN
Log(' 生成订单号:' || x_Header_Rec.Order_Number);
--判断是否有行信息
SELECT COUNT(1)
INTO l_Line_Count
FROM Oe_Order_Headers_All h
,Oe_Order_Lines_All l
WHERE h.Header_Id = l.Header_Id
AND h.Org_Id = g_Org_Id
AND h.Order_Number = x_Header_Rec.Order_Number;
IF l_Line_Count = 0 THEN
x_Return_Status := g_Ret_Sts_Error;
l_Error_Count := l_Error_Count + 1;
l_Error_Msg := l_Error_Msg || '无法生成行信息！';
ELSE
IF x_Header_Rec.Order_Type_Id = 1022 THEN
BEGIN
Fnd_File.Put_Line(Fnd_File.Log
,'Auto booked not TSS OE...');
Oe_Order_Book_Util.Complete_Book_Eligible(p_Api_Version_Number => 1.0
,p_Init_Msg_List => Fnd_Api.g_True
,p_Header_Id => x_Header_Rec.Header_Id
,x_Return_Status => l_Return_Status
,x_Msg_Count => l_Msg_Count
,x_Msg_Data => l_Msg_Data);
IF (l_Return_Status <> 'S') THEN
l_Msg_Data := 'Errors:';
FOR l_Index IN 1 .. l_Msg_Count LOOP
l_Msg_Data := l_Msg_Data ||
Oe_Msg_Pub.Get(p_Msg_Index => l_Index
,p_Encoded => 'F');
END LOOP;
Log('l_msg_data' || l_Msg_Data);
Log('book_order failed');
-- ROLLBACK TO SAVEPOINT do_create_so_sp;
ELSE
--update po line price
Log('book_order success');
END IF;
EXCEPTION
WHEN OTHERS THEN
Log(SQLERRM || Dbms_Utility.Format_Error_Backtrace);
END;
END IF;
END IF;
ELSE
l_Error_Count := l_Error_Count + 1;
FOR i IN 1 .. l_Msg_Count LOOP
Oe_Msg_Pub.Get(p_Msg_Index => i
,p_Encoded => Fnd_Api.g_False
,p_Data => l_Msg_Data1
,p_Msg_Index_Out => l_Msg_Index_Out);
x_Return_Message := Substr(x_Return_Message || '/' || l_Msg_Data1
,1
,200);
END LOOP;
Log(' api_import:' || l_Return_Status || '#' || x_Return_Message);
END IF;
END;
760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
