Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS OE_ORDER_PUB.PROCESS_ORDER Script Generator (Create and Book a Sales Order)

This generator writes a complete PL/SQL anonymous block that creates an Oracle EBS 12.1 or 12.2 sales order with one line through OE_ORDER_PUB.PROCESS_ORDER, prints every API message, and books it when you choose Y. A second block finds the user and responsibility IDs and checks the result.

Fill in

User name (for the lookup query)

Responsibility key (for the lookup query)

User ID

FND_USER.USER_ID, from the lookup query

Responsibility ID

FND_RESPONSIBILITY.RESPONSIBILITY_ID

Responsibility application ID

660 is Order Management (ONT)

Operating unit (ORG_ID)

Order type ID

OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_ID

Sold to (customer account ID)

HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID

Ship to site use ID

HZ_CUST_SITE_USES_ALL.SITE_USE_ID with SITE_USE_CODE SHIP_TO

Bill to site use ID (optional)

Leave blank to let defaulting rules pick it

Price list ID

QP_LIST_HEADERS_B.LIST_HEADER_ID

Customer PO number

Inventory item ID

Ship from (inventory org ID)

Ordered quantity

Request date

Book the order after creation

Generated code

Download:

Create (and optionally book) a sales order

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  -- Session context
  l_user_id              NUMBER       := 1318;
  l_resp_id              NUMBER       := 21623;
  l_resp_appl_id         NUMBER       := 660;
  l_org_id               NUMBER       := 204;
  l_book                 VARCHAR2(1)  := 'N';
  l_invoice_to           VARCHAR2(40) := '[Bill to site use ID (optional)]';

  -- Input records
  l_header_rec           OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
  l_line_tbl             OE_ORDER_PUB.Line_Tbl_Type;
  l_action_request_tbl   OE_ORDER_PUB.Request_Tbl_Type;

  -- Output records (all OUT parameters must be passed)
  x_header_rec               OE_ORDER_PUB.Header_Rec_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_header_payment_tbl       OE_ORDER_PUB.Header_Payment_Tbl_Type;
  x_header_payment_val_tbl   OE_ORDER_PUB.Header_Payment_Val_Tbl_Type;
  x_line_tbl                 OE_ORDER_PUB.Line_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_line_payment_tbl         OE_ORDER_PUB.Line_Payment_Tbl_Type;
  x_line_payment_val_tbl     OE_ORDER_PUB.Line_Payment_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(1);
  l_msg_count            NUMBER;
  l_msg_data             VARCHAR2(4000);
  l_header_id            NUMBER;

  PROCEDURE print_msgs(p_count IN NUMBER) IS
    l_text VARCHAR2(4000);
  BEGIN
    FOR i IN 1 .. NVL(p_count, 0) LOOP
      l_text := OE_MSG_PUB.Get(p_msg_index => i, p_encoded => FND_API.G_FALSE);
      DBMS_OUTPUT.PUT_LINE('  ' || i || ': ' || l_text);
    END LOOP;
  END print_msgs;
BEGIN
  FND_GLOBAL.APPS_INITIALIZE(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);
  MO_GLOBAL.INIT('ONT');
  MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);

  -- Header
  l_header_rec.operation         := OE_GLOBALS.G_OPR_CREATE;
  l_header_rec.org_id            := l_org_id;
  l_header_rec.order_type_id     := 1430;
  l_header_rec.sold_to_org_id    := 1006;
  l_header_rec.ship_to_org_id    := 1024;
  IF l_invoice_to IS NOT NULL THEN
    l_header_rec.invoice_to_org_id := TO_NUMBER(l_invoice_to);
  END IF;
  l_header_rec.price_list_id     := 1000;
  l_header_rec.ordered_date      := SYSDATE;
  l_header_rec.cust_po_number    := 'API-TEST-0001';

  -- Line 1
  l_line_tbl(1)                    := OE_ORDER_PUB.G_MISS_LINE_REC;
  l_line_tbl(1).operation          := OE_GLOBALS.G_OPR_CREATE;
  l_line_tbl(1).inventory_item_id  := 149;
  l_line_tbl(1).ordered_quantity   := 5;
  l_line_tbl(1).ship_from_org_id   := 207;
  l_line_tbl(1).request_date       := TO_DATE('2026-11-15', 'YYYY-MM-DD');

  OE_ORDER_PUB.Process_Order(
    p_api_version_number     => 1.0,
    p_init_msg_list          => FND_API.G_TRUE,
    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,
    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_header_payment_tbl     => x_header_payment_tbl,
    x_header_payment_val_tbl => x_header_payment_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_line_payment_tbl       => x_line_payment_tbl,
    x_line_payment_val_tbl   => x_line_payment_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('Create status: ' || l_return_status);
  print_msgs(l_msg_count);

  IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
    ROLLBACK;
    RETURN;
  END IF;

  l_header_id := x_header_rec.header_id;
  DBMS_OUTPUT.PUT_LINE('Header id: ' || l_header_id || ', order number: ' || x_header_rec.order_number);

  IF l_book = 'Y' THEN
    l_action_request_tbl(1)             := OE_ORDER_PUB.G_MISS_REQUEST_REC;
    l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
    l_action_request_tbl(1).entity_code  := OE_GLOBALS.G_ENTITY_HEADER;
    l_action_request_tbl(1).entity_id    := l_header_id;

    OE_ORDER_PUB.Process_Order(
      p_api_version_number     => 1.0,
      p_init_msg_list          => FND_API.G_TRUE,
      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_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_header_payment_tbl     => x_header_payment_tbl,
      x_header_payment_val_tbl => x_header_payment_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_line_payment_tbl       => x_line_payment_tbl,
      x_line_payment_val_tbl   => x_line_payment_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('Book status: ' || l_return_status || ', request status: ' || x_action_request_tbl(1).return_status);
    print_msgs(l_msg_count);

    IF l_return_status <> FND_API.G_RET_STS_SUCCESS
       OR NVL(x_action_request_tbl(1).return_status, FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
      ROLLBACK;
      RETURN;
    END IF;
  END IF;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Committed order header id ' || l_header_id);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Find IDs and check the result

-- Find the IDs for FND_GLOBAL.APPS_INITIALIZE
SELECT fu.user_id,
       fr.responsibility_id   AS resp_id,
       fr.application_id      AS resp_appl_id,
       fr.responsibility_name
FROM   fnd_user fu,
       fnd_user_resp_groups_direct furg,
       fnd_responsibility_vl fr
WHERE  fu.user_name = UPPER('OMUSER01')
AND    fr.responsibility_key = 'ORDER_MGMT_SUPER_USER'
AND    furg.user_id = fu.user_id
AND    furg.responsibility_id = fr.responsibility_id
AND    furg.responsibility_application_id = fr.application_id
AND    NVL(furg.end_date, SYSDATE + 1) > SYSDATE;

-- Check the result after the run
SELECT h.header_id, h.order_number, h.flow_status_code, h.booked_flag,
       l.line_number, l.ordered_item, l.ordered_quantity, l.unit_selling_price, l.flow_status_code AS line_status
FROM   oe_order_headers_all h,
       oe_order_lines_all l
WHERE  l.header_id = h.header_id
AND    h.org_id = 204
AND    h.cust_po_number = 'API-TEST-0001';

Test on a non-production instance first. Check the OE_ORDER_PUB specification on your instance (SELECT text FROM all_source WHERE name = 'OE_ORDER_PUB' AND type = 'PACKAGE' ORDER BY line) and make sure every x_ parameter in the call exists there; the payment parameters exist in R12 but the list can differ by patch level, which is why the call uses named notation. OE_GLOBALS.G_BOOK_ORDER is the BOOK_ORDER request type. Set profile OM: Debug Level and use OE_DEBUG_PUB when messages are not enough. If booking fails the whole transaction is rolled back, so nothing is saved.

API script review

Send us your PROCESS_ORDER call and the package spec from your instance and we will check parameters, context setup and error handling.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

What an API is in Oracle EBS

In EBS an API is a supported PL/SQL package procedure that applies the same validation, defaulting and workflow as the screens. OE_ORDER_PUB.PROCESS_ORDER is the public API of Order Management: one call can create, update or delete headers, lines, adjustments, sales credits and lot serials, and run actions such as booking through the action request table. Oracle supports data created through public APIs; direct inserts into OE_ORDER_HEADERS_ALL are not supported. For bulk loads from files, the Order Import interface (OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL, program Order Import) is often a better fit, while PROCESS_ORDER suits integrations that create orders one at a time.

How to use the generated script

Run the lookup query first to get USER_ID, RESPONSIBILITY_ID and the responsibility application ID, then fill the fields. IDs must belong to the operating unit you set: the order type, customer sites and price list are validated against it. Run the block in SQL*Plus or SQL Developer with server output on. The script calls FND_GLOBAL.APPS_INITIALIZE, MO_GLOBAL.INIT('ONT') and MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id), then builds the header and line from the G_MISS records so every column you do not set is defaulted. It commits only when the return status is S.

Start every record from G_MISS_HEADER_REC or G_MISS_LINE_REC
operation = OE_GLOBALS.G_OPR_CREATE on header and line
Read messages with OE_MSG_PUB.Get, encoded = FND_API.G_FALSE
Return status S success, E expected error, U unexpected error

Why named notation and every OUT parameter

PROCESS_ORDER has a long parameter list with defaulted IN parameters and many OUT parameters. Positional calls break as soon as a patch adds a parameter, so the script passes every argument by name. All OUT parameters must be supplied, since PL/SQL does not default OUT arguments, which is why the block declares a variable for each x_ record and table. If your instance raises PLS-00306 wrong number or types of arguments, compare the call with the package spec and remove or add the parameter the spec differs on. Do not set p_action_commit to true and then also commit, keep one commit point.

Reading errors and checking the order

Most failures are setup: no price for the item on the price list, a ship to site in another operating unit, an order type without a line type for the line flow, or a missing warehouse. The messages printed by print_msgs name the attribute that failed. When the order is created but booking fails, booking errors usually mention required attributes at booking, such as payment terms, salesperson or a hold. Check the result with the second query, or in the Sales Orders form by customer PO number. For deeper tracing set OM: Debug Level to 5 for the user and read the debug file.

Frequently Asked Questions

What is OE_ORDER_PUB.PROCESS_ORDER?

It is the public Order Management API in Oracle EBS that creates, updates and deletes sales order headers, lines and related entities, and runs actions like booking. It applies the same defaulting and validation as the Sales Orders form, so orders created through it behave like orders entered on screen.

How do I book an order through the API?

Add an action request with request_type OE_GLOBALS.G_BOOK_ORDER, entity_code OE_GLOBALS.G_ENTITY_HEADER and entity_id set to the header id, then call PROCESS_ORDER with p_action_request_tbl. Check both the overall return status and the return_status of the request record.

Why does my PROCESS_ORDER call return status U?

U is an unexpected error, often from a missing context: no FND_GLOBAL.APPS_INITIALIZE, MOAC not set for the operating unit, or IDs from another org. Print all messages with OE_MSG_PUB.Get and check that the responsibility has access to the operating unit in MO: Security Profile or MO: Operating Unit.

Should I use the API or Order Import?

Use PROCESS_ORDER for real-time, one order at a time integrations where the caller needs the result immediately. Use Order Import with the OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL tables for batches of orders from files or EDI, where you can review and correct errors in Order Import Corrections.

If you want order desk staff to ask about order status in plain English, with any order creation going through this API only after human approval, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools