Create Sales Orders Using API

DECLARE

   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;

   l_header_adj_tbl               OE_ORDER_PUB.Header_Adj_Tbl_Type;

   l_line_adj_tbl                 OE_ORDER_PUB.line_adj_tbl_Type;

   l_header_scr_tbl               OE_ORDER_PUB.Header_Scredit_Tbl_Type;

   l_line_scredit_tbl             OE_ORDER_PUB.Line_Scredit_Tbl_Type;

   l_request_rec                  OE_ORDER_PUB.Request_Rec_Type;

   x_header_rec                   OE_ORDER_PUB.Header_Rec_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_REC;

   p_old_header_rec               OE_ORDER_PUB.Header_Rec_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_REC;

   p_header_val_rec               OE_ORDER_PUB.Header_Val_Rec_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

   p_old_header_val_rec           OE_ORDER_PUB.Header_Val_Rec_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

   p_Header_Adj_tbl               OE_ORDER_PUB.Header_Adj_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

   p_old_Header_Adj_tbl           OE_ORDER_PUB.Header_Adj_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

   p_Header_Adj_val_tbl           OE_ORDER_PUB.Header_Adj_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

   p_old_Header_Adj_val_tbl       OE_ORDER_PUB.Header_Adj_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

   p_Header_price_Att_tbl         OE_ORDER_PUB.Header_Price_Att_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

   p_old_Header_Price_Att_tbl     OE_ORDER_PUB.Header_Price_Att_Tbl_Type

      := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

   p_Header_Adj_Att_tbl           OE_ORDER_PUB.Header_Adj_Att_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

   p_old_Header_Adj_Att_tbl       OE_ORDER_PUB.Header_Adj_Att_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

   p_Header_Adj_Assoc_tbl         OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type

      := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

   p_old_Header_Adj_Assoc_tbl     OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type

      := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

   p_Header_Scredit_tbl           OE_ORDER_PUB.Header_Scredit_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

   p_old_Header_Scredit_tbl       OE_ORDER_PUB.Header_Scredit_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

   p_Header_Scredit_val_tbl       OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

   p_old_Header_Scredit_val_tbl   OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

   p_line_tbl                     OE_ORDER_PUB.Line_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_TBL;

   p_old_line_tbl                 OE_ORDER_PUB.Line_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_TBL;

   p_line_val_tbl                 OE_ORDER_PUB.Line_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

   p_old_line_val_tbl             OE_ORDER_PUB.Line_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

   p_Line_Adj_tbl                 OE_ORDER_PUB.Line_Adj_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

   p_old_Line_Adj_tbl             OE_ORDER_PUB.Line_Adj_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

   p_Line_Adj_val_tbl             OE_ORDER_PUB.Line_Adj_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

   p_old_Line_Adj_val_tbl         OE_ORDER_PUB.Line_Adj_Val_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

   p_Line_price_Att_tbl           OE_ORDER_PUB.Line_Price_Att_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

   p_old_Line_Price_Att_tbl       OE_ORDER_PUB.Line_Price_Att_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

   p_Line_Adj_Att_tbl             OE_ORDER_PUB.Line_Adj_Att_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

   p_old_Line_Adj_Att_tbl         OE_ORDER_PUB.Line_Adj_Att_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

   p_Line_Adj_Assoc_tbl           OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

   p_old_Line_Adj_Assoc_tbl       OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

   p_Line_Scredit_tbl             OE_ORDER_PUB.Line_Scredit_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

   p_old_Line_Scredit_tbl         OE_ORDER_PUB.Line_Scredit_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

   p_Line_Scredit_val_tbl         OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

   p_old_Line_Scredit_val_tbl     OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

   p_Lot_Serial_tbl               OE_ORDER_PUB.Lot_Serial_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

   p_old_Lot_Serial_tbl           OE_ORDER_PUB.Lot_Serial_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

   p_Lot_Serial_val_tbl           OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

   p_old_Lot_Serial_val_tbl       OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type

      := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

   p_action_request_tbl           OE_ORDER_PUB.Request_Tbl_Type

                                     := OE_ORDER_PUB.G_MISS_REQUEST_TBL;

   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_return_status                VARCHAR2 (1000);

   l_msg_count                    NUMBER;

   l_msg_data                     VARCHAR2 (1000);

   p_api_version_number           NUMBER := 1.0;

   p_init_msg_list                VARCHAR2 (10) := FND_API.G_FALSE;

   p_return_values                VARCHAR2 (10) := FND_API.G_FALSE;

   p_action_commit                VARCHAR2 (10) := FND_API.G_FALSE;

   x_return_status                VARCHAR2 (1);

   x_msg_count                    NUMBER;

   x_msg_data                     VARCHAR2 (100);

   X_DEBUG_FILE                   VARCHAR2 (100);

   l_line_tbl_index               NUMBER;

   l_msg_index_out                NUMBER (10);

BEGIN

   DBMS_OUTPUT.enable (1000000);

   fnd_global.apps_initialize (1318, 21623, 660); -- pass in user_id, responsibility_id, and application_id

   MO_GLOBAL.INIT ('ONT');                                 -- Required for R12

   mo_global.set_org_context (204, NULL, 'ONT');

   fnd_global.set_nls_context ('AMERICAN');

   MO_GLOBAL.SET_POLICY_CONTEXT ('S', 204);                -- Required for R12

   oe_msg_pub.initialize;

   oe_debug_pub.initialize;

   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');

   oe_debug_pub.SetDebugLevel (5); -- Use 5 for the most debuging output, I warn you its a lot of data

   DBMS_OUTPUT.put_line ('START OF NEW DEBUG');

   --This is to CREATE an order header and an order line

   --Create Header record

   --Initialize header record to missing

   l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

   l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';

   l_header_rec.pricing_date := SYSDATE;

   l_header_rec.cust_po_number := 'TestAPI1a';

   l_header_rec.sold_to_org_id := 1290;

   l_header_rec.price_list_id := 1000;

   l_header_rec.ordered_date := SYSDATE;

   l_header_rec.shipping_method_code := 'DHL';

   l_header_rec.sold_from_org_id := 204;

   l_header_rec.salesrep_id := 1006;

   l_header_rec.order_type_id := 1437;

   l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;

   l_line_tbl_index := 1;

   -- FIRST LINE RECORD

   -- Initialize record to missing

   l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

   -- Line attributes

   l_line_tbl (l_line_tbl_index).inventory_item_id := 149;

   l_line_tbl (l_line_tbl_index).ordered_quantity := 2;

   l_line_tbl (l_line_tbl_index).ship_from_org_id := 207;

   --l_line_tbl ( l_line_tbl_index ) .subinventory      := 'FGI';

   l_line_tbl (l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;

   -- CALL TO PROCESS ORDER Check the return status and then commit.

   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,

      p_action_commit            => fnd_api.g_false,

      x_return_status            => l_return_status,

      x_msg_count                => l_msg_count,

      x_msg_data                 => l_msg_data,

      p_header_rec               => l_header_rec,

      p_line_tbl                 => l_line_tbl,

      p_action_request_tbl       => l_action_request_tbl,-- OUT PARAMETERS

      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                 => p_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       => l_action_request_tbl);

   DBMS_OUTPUT.put_line (

      'OM Debug file: ' || oe_debug_pub.G_DIR || '/' || oe_debug_pub.G_FILE);

   DBMS_OUTPUT.put_line ('Header_ID : ' || x_header_rec.header_id);

   -- Retrieve messages

   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_data,

                      p_msg_index_out   => l_msg_index_out);

      DBMS_OUTPUT.PUT_LINE ('message is: ' || l_msg_data);

      DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index_out);

   END LOOP;

   -- Check the return status

   IF l_return_status = FND_API.G_RET_STS_SUCCESS

   THEN

      DBMS_OUTPUT.put_line ('Process Order Sucess');

   ELSE

      DBMS_OUTPUT.put_line ('Failed');

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);

END;

/

COMMIT;

OAF Personalization Profile Options

To Enable the Personalization Option in OAF we need to set the below profiles.

Navigation: System Adminstrator --> Profile --> System

User Preferences in Oracle E-Business Suite

How to get the values from user the Preferences

Current Session Language

SELECT FND_PROFILE.VALUE_SPECIFIC (NAME => 'ICX_LANGUAGE', USER_ID=>9999)    FROM DUAL;

Output :  AMERICAN

Territory

 SELECT FND_PROFILE.VALUE_SPECIFIC (NAME =>'ICX_TERRITORY', USER_ID=>9999)

 FROM DUAL;

Output : AMERICA

Date Format

SELECT FND_PROFILE.VALUE_SPECIFIC (NAME =>'ICX_DATE_FORMAT_MASK', USER_ID=>9999)

  FROM DUAL;

Output : DD-MON-RRRR

Number Format

SELECT FND_PROFILE.VALUE_SPECIFIC ('ICX_NUMERIC_CHARACTERS', 9999)

  FROM DUAL;

Output : .,

Getting Started with OAF!!

Download the Patch and Extract to the system.

Place the .dbc file to the appropriate folder.

Get the dbc file from the above path and place in below local path

Place the Jdeveloper.exe on the Desktop from p9879989_R12_GENERIC\jdevbin and click on the Jdeveloper.exe

Set the Database connection

Choose the Database folder from the Connections. Create a new Connection and provide the details to connect and Test the Connection!!

Set the Default Project Properties to automate at run time.

Create a New Workspace

Set the OA Workspace Path

Create a new Project

Create a new Application Module

Create a new Page



Set the Properties of a page

Run the Program. Right click on the page and click on run.

Output of sample page

BC4J Structure.