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;