Search from 700 + Posts

Nov 11, 2013

How to Export Sales Orders from Oracle Applications To External Applications

How to Export Sales Orders from Oracle Applications To External Applications

In case you have requirement to  Export Sales Orders from Oracle Applications To External Applications, one easy way to use Oracle Standard API 
OE_ORDER_PUB.get_order.

Below is complete running example 

How Does One Export Sales Orders To External Applications 

Declare
--L_Header_Rec     Header_Rec_Type;
L_Return_Status   Varchar2(10);
L_Msg_Count       Number;
L_Msg_Data        Varchar2(5000);
l_msg_index NUMBER;
l_data VARCHAR2(2000);
L_Loop_Count Number;
L_Lin_Count  Number:=1;
i NUMBER;
--------------------------
L_Header_Rec                      Oe_Order_Pub.Header_Rec_Type;
L_Header_Val_Rec                  Oe_Order_Pub.Header_Val_Rec_Type;
l_Header_Adj_tbl                  OE_ORDER_PUB.Header_Adj_Tbl_Type;
L_Header_Adj_Val_Tbl              Oe_Order_Pub.Header_Adj_Val_Tbl_Type;
L_Header_Price_Att_Tbl            Oe_Order_Pub.Header_Price_Att_Tbl_Type;
L_Header_Adj_Att_Tbl              Oe_Order_Pub.Header_Adj_Att_Tbl_Type;
L_Header_Adj_Assoc_Tbl            Oe_Order_Pub.Header_Adj_Assoc_Tbl_Type;
L_Header_Scredit_Tbl              Oe_Order_Pub.Header_Scredit_Tbl_Type;
l_Header_Scredit_Val_Tbl          OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
L_Header_Payment_Tbl              Oe_Order_Pub.Header_Payment_Tbl_Type;
l_Header_Payment_Val_Tbl          OE_ORDER_PUB.Header_Payment_Val_Tbl_Type;
L_Line_Tbl                        Oe_Order_Pub.Line_Tbl_Type;
L_Line_Val_Tbl                    Oe_Order_Pub.Line_Val_Tbl_Type;
L_Line_Adj_Tbl                    Oe_Order_Pub.Line_Adj_Tbl_Type;
L_Line_Adj_Val_Tbl                OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
L_Line_Price_Att_Tbl              Oe_Order_Pub.Line_Price_Att_Tbl_Type;
L_Line_Adj_Att_Tbl                Oe_Order_Pub.Line_Adj_Att_Tbl_Type;
L_Line_Adj_Assoc_Tbl              Oe_Order_Pub.Line_Adj_Assoc_Tbl_Type;
L_Line_Scredit_Tbl                Oe_Order_Pub.Line_Scredit_Tbl_Type;
L_Line_Scredit_Val_Tbl            Oe_Order_Pub.Line_Scredit_Val_Tbl_Type;
L_Line_Payment_Tbl                Oe_Order_Pub.Line_Payment_Tbl_Type;
L_Line_Payment_Val_Tbl            Oe_Order_Pub.Line_Payment_Val_Tbl_Type;
L_Lot_Serial_Tbl                  Oe_Order_Pub.Lot_Serial_Tbl_Type;
L_Lot_Serial_Val_Tbl              OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;        
Begin
  
  Fnd_Global.Apps_Initialize(1089,23423,660); -- pass in user_id,responsibility_id, and application_id
Mo_Global.Init('ONT'); 
  MO_GLOBAL.SET_POLICY_CONTEXT('S', 204);  -- this may not be needed since passing org_id to the API

  Oe_Order_Pub.Get_Order
  (P_Api_Version_Number => 1.0,
   X_Return_Status      => L_Return_Status,
   X_Msg_Count          =>L_Msg_Count,
   X_Msg_Data           => L_Msg_Data,
   P_Header_Id          => 904386,
  -- p_header             => '100000329',
   P_Org_Id             =>204,
   --p_operating_unit     => '204',
   --x_header_rec         => L_header_rec   
  X_Header_Rec                     => L_Header_Rec,       
  X_Header_Val_Rec                 => l_Header_Val_Rec,      
  X_Header_Adj_Tbl                 => L_Header_Adj_Tbl,                
  X_Header_Adj_Val_Tbl             => L_Header_Adj_Val_Tbl,            
  X_Header_Price_Att_Tbl           => L_Header_Price_Att_Tbl,          
  x_Header_Adj_Att_Tbl             => l_Header_Adj_Att_Tbl,            
  X_Header_Adj_Assoc_Tbl           => L_Header_Adj_Assoc_Tbl,          
  x_Header_Scredit_tbl             => l_Header_Scredit_tbl,            
  X_Header_Scredit_Val_Tbl         => L_Header_Scredit_Val_Tbl,        
  X_Header_Payment_Tbl             => L_Header_Payment_Tbl,            
  X_Header_Payment_Val_Tbl         => L_Header_Payment_Val_Tbl,        
  X_Line_Tbl                       => L_Line_Tbl,                      
  X_Line_Val_Tbl                   => L_Line_Val_Tbl,                  
  x_Line_Adj_Tbl                   => l_Line_Adj_Tbl,                  
  X_Line_Adj_Val_Tbl               => L_Line_Adj_Val_Tbl,              
  X_Line_Price_Att_Tbl             => L_Line_Price_Att_Tbl,            
  X_Line_Adj_Att_Tbl               => L_Line_Adj_Att_Tbl,              
  X_Line_Adj_Assoc_Tbl             => L_Line_Adj_Assoc_Tbl,            
  X_Line_Scredit_Tbl               => L_Line_Scredit_Tbl,              
  x_Line_Scredit_Val_Tbl           => l_Line_Scredit_Val_Tbl,          
  X_Line_Payment_Tbl               => L_Line_Payment_Tbl,              
  X_Line_Payment_Val_Tbl           => L_Line_Payment_Val_Tbl,          
  X_Lot_Serial_Tbl                 => L_Lot_Serial_Tbl,                
  x_Lot_Serial_val_tbl             => l_Lot_Serial_val_tbl    
   );
Dbms_Output.Put_Line('Status ='||L_Return_Status||'-'||L_Header_Adj_Tbl.Count||'-'||L_Line_Tbl.Count);
If L_Return_Status ='S' Then 
  While L_Lin_Count <=  L_Line_Tbl.Count
  Loop
  Dbms_Output.Put_Line(L_Header_Rec.Sold_To_Org_Id||'-'||L_Header_Rec.Order_Number||'-'||L_Line_Tbl(L_Lin_Count).Header_Id||'-'||L_Line_Tbl(L_Lin_Count).Line_Id);  
  Dbms_Output.Put_Line(L_Header_Adj_Tbl.Count||L_Line_Adj_Tbl.Count||'-'||L_Line_Price_Att_Tbl.count||'-'||L_Line_Scredit_Tbl.count);
  L_Lin_Count:=L_Lin_Count+1;
  End Loop;
End If;

--Dbms_Output.Put_Line('Status ='||L_Return_Status||'---'||L_Header_Adj_Tbl.Count||'-'||L_Line_Tbl.Count||'-'||
--L_Line_Tbl(1).Header_Id||'-'||L_Header_Rec.Header_Id||'-'||L_Header_Rec.Order_Number);   
--Dbms_Output.Put_Line('Address -'||L_Header_Rec.sold_to_address1);--||'-'||L_Header_Rec.Ship_from_org||'-'||L_Header_Rec.Ship_from_org);
--Dbms_Output.Put_Line(L_Header_Rec.Sold_To_Address2);
--Dbms_Output.Put_Line(L_Header_Rec.Ship_To_Address1);
--Dbms_Output.Put_Line(L_Header_Rec.Ship_To_Address2);
--Dbms_Output.Put_Line(L_Header_Rec.price_list);
/*
Dbms_Output.Put_Line('data ='||L_Msg_Data);   
DBMS_OUTPUT.PUT_LINE('Status ='||l_Msg_count);  

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_data
,P_Msg_Index_Out => L_Msg_Index);
Dbms_Output.Put_Line('message is: '|| L_Data);
Dbms_Output.Put_Line('message index is: '||L_Msg_Index);
End Loop;
*/
END;

2 comments:

  1. Hi
    Can you explain elaborately please (How to Export Sales Orders from Oracle Applications To External Applications)

    ReplyDelete
  2. Oe_Order_Pub.Get_Order API will help you pull all details of sales Order from Oracle Order Management, only thing you need is read the output parameters which are actually pl/sql tables.

    ReplyDelete