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

Oracle EBS Public PL/SQL API Reference (OM, Inventory, PO, AP, AR, TCA, FND)

A reference of the public PL/SQL APIs EBS 12.1/12.2 developers call most often outside HR: Order Management, Shipping, Inventory, BOM, Purchasing, Payables, Receivables, TCA, AOL and Workflow. Each row gives the key parameters, the session context it needs and how to read its errors.

Download:

Package.Procedure	Module	What it does	Key IN parameters / record types	Context required	Error retrieval
OE_ORDER_PUB.PROCESS_ORDER	OM	Create, update, cancel or delete orders and lines; can also book via action requests	p_api_version_number => 1.0; p_header_rec (OE_ORDER_PUB.HEADER_REC_TYPE); p_line_tbl (LINE_TBL_TYPE); p_action_request_tbl (REQUEST_TBL_TYPE); operation set with OE_GLOBALS.G_OPR_CREATE / G_OPR_UPDATE	FND_GLOBAL.APPS_INITIALIZE with an OM responsibility; MO_GLOBAL.INIT('ONT') and MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id)	x_return_status, x_msg_count; loop OE_MSG_PUB.GET(p_msg_index => i, p_encoded => FND_API.G_FALSE)
OE_ORDER_PUB.GET_ORDER	OM	Read a full order into the API record structures	p_header_id (or the order number/value parameters); returns x_header_rec, x_line_tbl and other tables	APPS_INITIALIZE and MO context for the order's org	x_return_status; OE_MSG_PUB.GET
OE_HOLDS_PUB.APPLY_HOLDS / RELEASE_HOLDS	OM	Apply or release holds on orders, lines or hold sources	p_api_version => 1.0; p_hold_source_rec (OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE: HOLD_ID, HOLD_ENTITY_CODE, HOLD_ENTITY_ID, HEADER_ID, LINE_ID); release also takes p_hold_release_rec (RELEASE_REASON_CODE)	APPS_INITIALIZE; MO context	x_return_status, x_msg_count; OE_MSG_PUB.GET
WSH_DELIVERIES_PUB.DELIVERY_ACTION	Shipping	Run delivery actions such as ship confirm, pick release or close	p_action_code (for example 'CONFIRM', 'PICK-RELEASE'); p_delivery_id or p_delivery_name; ship confirm options p_sc_action_flag, p_sc_intransit_flag, p_sc_close_trip_flag, p_sc_defer_interface_flag	APPS_INITIALIZE with a shipping responsibility	x_return_status, x_msg_count; FND_MSG_PUB.GET
WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY	Shipping	Assign or unassign delivery details to a delivery	p_TabOfDelDets (table of DELIVERY_DETAIL_ID); p_action 'ASSIGN' or 'UNASSIGN'; p_delivery_id or p_delivery_name	APPS_INITIALIZE	x_return_status; FND_MSG_PUB.GET
INV_ITEM_GRP.CREATE_ITEM / UPDATE_ITEM	Inventory	Create or update an item in an organization	p_Item_rec (INV_ITEM_GRP.ITEM_REC_TYPE: ORGANIZATION_ID, SEGMENT1, DESCRIPTION, TEMPLATE_ID and attributes); p_commit; p_validation_level	APPS_INITIALIZE with an inventory responsibility	x_return_status and x_Error_tbl (INV_ITEM_GRP.ERROR_TBL_TYPE) with MESSAGE_TEXT per error
EGO_ITEM_PUB.PROCESS_ITEM	Inventory / PIM	Create or update an item; the API Oracle positions for item maintenance in R12	p_api_version => 1.0; p_transaction_type 'CREATE' or 'UPDATE'; item columns as named parameters (p_segment1, p_organization_id, p_description, p_template_id and others)	APPS_INITIALIZE	x_return_status, x_msg_count; ERROR_HANDLER.GET_MESSAGE_LIST for the messages
INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES	Inventory	Return on-hand, reservable, reserved and available-to-transact quantities	p_organization_id, p_inventory_item_id, p_tree_mode, p_is_revision_control, p_is_lot_control, p_is_serial_control, p_revision, p_lot_number, p_subinventory_code, p_locator_id; OUT x_qoh, x_rqoh, x_qr, x_qs, x_att, x_atr	None beyond a database session for most uses; clear cache with INV_QUANTITY_TREE_PUB.CLEAR_QUANTITY_CACHE between calls	x_return_status, x_msg_count, x_msg_data
INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS	Inventory	Process rows already inserted into MTL_TRANSACTIONS_INTERFACE online	Function returning NUMBER; p_api_version 1.0; p_table 1 (interface table); p_header_id = TRANSACTION_HEADER_ID of your rows; OUT x_trans_count	APPS_INITIALIZE	Return value non-zero or x_return_status; ERROR_CODE and ERROR_EXPLANATION on the interface rows
INV_RESERVATION_PUB.CREATE_RESERVATION	Inventory	Create a reservation against a demand such as a sales order line	p_rsv_rec (INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE); p_serial_number; p_partial_reservation_flag; p_force_reservation_flag; OUT x_quantity_reserved, x_reservation_id	APPS_INITIALIZE	x_return_status, x_msg_count; FND_MSG_PUB.GET
INV_LOT_API_PUB.CREATE_INV_LOT	Inventory	Create a lot number for a lot-controlled item	Overloaded; key values are organization, inventory item and lot number plus lot attributes	APPS_INITIALIZE	x_return_status, x_msg_count; FND_MSG_PUB.GET
BOM_BO_PUB.PROCESS_BOM	BOM	Create, update or delete bills, revisions and components	p_bom_header_rec (BOM_BO_PUB.BOM_HEAD_REC_TYPE); p_bom_component_tbl (BOM_COMPS_TBL_TYPE); TRANSACTION_TYPE 'CREATE', 'UPDATE', 'DELETE' in each record	APPS_INITIALIZE; ERROR_HANDLER.INITIALIZE before the call	x_return_status, x_msg_count; ERROR_HANDLER.GET_MESSAGE
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT	Purchasing	Cancel, close or finally close a PO, release, line or shipment	p_doc_type ('PO', 'PA', 'RELEASE'); p_doc_subtype ('STANDARD', 'BLANKET' and others); p_doc_id or p_doc_num; line and shipment IDs or numbers; p_action ('CANCEL', 'CLOSE', 'FINALLY CLOSE' and others); p_action_date; p_cancel_reason	APPS_INITIALIZE; MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id)	x_return_status; FND_MSG_PUB.GET
PO_CHANGE_API1_S.UPDATE_PO	Purchasing	Change quantity, price, promised date or need-by date on an approved PO and optionally re-approve	Function returning NUMBER (1 success); X_PO_NUMBER, X_RELEASE_NUMBER, X_REVISION_NUMBER, X_LINE_NUMBER, X_SHIPMENT_NUMBER, NEW_QUANTITY, NEW_PRICE, NEW_PROMISED_DATE, NEW_NEED_BY_DATE, LAUNCH_APPROVALS_FLAG, UPDATE_SOURCE, VERSION, X_OVERRIDE_DATE, X_API_ERRORS	APPS_INITIALIZE; MO context for the PO's org	X_API_ERRORS (PO_API_ERRORS_REC_TYPE) message_text table
AP_VENDOR_PUB_PKG.CREATE_VENDOR / CREATE_VENDOR_SITE / CREATE_VENDOR_CONTACT	Payables	Create suppliers, supplier sites and contacts	p_api_version => 1.0; p_vendor_rec (AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE), p_vendor_site_rec (R_VENDOR_SITE_REC_TYPE), p_vendor_contact_rec (R_VENDOR_CONTACT_REC_TYPE); OUT x_vendor_id, x_party_id, x_vendor_site_id	APPS_INITIALIZE; MO context for sites	x_return_status, x_msg_count; FND_MSG_PUB.GET
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE	Payables	Cancel one invoice as the Invoice Workbench cancel action does	Function returning BOOLEAN; P_invoice_id, P_last_updated_by, P_last_update_login, P_accounting_date, plus several OUT parameters including P_message_name and P_Token, and P_calling_sequence	APPS_INITIALIZE; MO context	Returns FALSE; read P_message_name and P_Token. Signature differs across 12.x patch levels, so check the package spec
AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE	Receivables	Create one invoice, debit memo or credit memo transaction without AutoInvoice	p_batch_source_rec; p_trx_header_tbl; p_trx_lines_tbl; p_trx_dist_tbl; p_trx_salescredits_tbl; OUT x_customer_trx_id	APPS_INITIALIZE; MO_GLOBAL.INIT('AR') and SET_POLICY_CONTEXT	x_return_status, x_msg_count; validation errors also in AR_TRX_ERRORS_GT
AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST	Receivables	Create a credit memo request against an invoice; with workflow skipped it creates the credit memo directly	p_customer_trx_id; p_line_credit_flag; p_line_amount, p_tax_amount, p_freight_amount; p_cm_reason_code; p_skip_workflow_flag; p_batch_source_name; OUT x_request_id	APPS_INITIALIZE; MO context	x_return_status, x_msg_count; FND_MSG_PUB.GET
AR_RECEIPT_API_PUB.CREATE_CASH / APPLY	Receivables	Create a cash receipt; apply a receipt to a transaction (CREATE_AND_APPLY does both)	CREATE_CASH: p_amount, p_receipt_number, p_receipt_date, p_customer_id, p_receipt_method_id, p_currency_code; OUT p_cr_id. APPLY: p_cash_receipt_id, p_customer_trx_id, p_amount_applied	APPS_INITIALIZE; MO context	x_return_status, x_msg_count; FND_MSG_PUB.GET
HZ_PARTY_V2PUB.CREATE_ORGANIZATION	TCA	Create an organization party	p_organization_rec (HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE: ORGANIZATION_NAME, CREATED_BY_MODULE); OUT x_party_id, x_party_number, x_profile_id	APPS_INITIALIZE; CREATED_BY_MODULE must be a valid lookup value	x_return_status, x_msg_count; FND_MSG_PUB.GET
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA	Create a customer account, optionally creating the party and profile	p_cust_account_rec; p_organization_rec (or p_person_rec); p_customer_profile_rec; p_create_profile_amt; OUT x_cust_account_id, x_account_number, x_party_id	APPS_INITIALIZE	x_return_status, x_msg_count; FND_MSG_PUB.GET
HZ_LOCATION_V2PUB.CREATE_LOCATION	TCA	Create an address location	p_location_rec (ADDRESS1, CITY, POSTAL_CODE, COUNTRY, CREATED_BY_MODULE); OUT x_location_id	APPS_INITIALIZE	x_return_status; FND_MSG_PUB.GET
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE	TCA	Link a location to a party as a party site	p_party_site_rec (PARTY_ID, LOCATION_ID, CREATED_BY_MODULE); OUT x_party_site_id, x_party_site_number	APPS_INITIALIZE	x_return_status; FND_MSG_PUB.GET
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE / CREATE_CUST_SITE_USE	TCA	Create the account site in an operating unit and its BILL_TO or SHIP_TO use	p_cust_acct_site_rec (CUST_ACCOUNT_ID, PARTY_SITE_ID, ORG_ID); OUT x_cust_acct_site_id. p_cust_site_use_rec (SITE_USE_CODE, CUST_ACCT_SITE_ID); OUT x_site_use_id	APPS_INITIALIZE; MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id) because account sites are org-striped	x_return_status; FND_MSG_PUB.GET
GL_DAILY_RATES_INTERFACE (insert)	GL	Load daily exchange rates; there is no separate public procedure, the interface trigger processes the insert	FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE, TO_CONVERSION_DATE, USER_CONVERSION_TYPE, CONVERSION_RATE, MODE_FLAG 'I'	Plain insert as APPS	Rows left in the table with ERROR_CODE
FND_FLEX_EXT.GET_CCID	FND / GL	Return the CODE_COMBINATION_ID for a segment string, creating it if dynamic insert is allowed	Function returning NUMBER; application_short_name 'SQLGL', key_flex_code 'GL#', structure_number (CHART_OF_ACCOUNTS_ID), validation_date (character in FND_FLEX_EXT.DATE_FORMAT), concatenated_segments	APPS_INITIALIZE for cross-validation and security rules	Returns 0 on failure; FND_FLEX_EXT.GET_MESSAGE
FND_GLOBAL.APPS_INITIALIZE	FND	Set user, responsibility and application context in a database session	user_id, resp_id, resp_appl_id (optional security_group_id)	Call first in any script that uses APIs or WHO columns	No return; verify with FND_GLOBAL.USER_ID afterwards
MO_GLOBAL.INIT / SET_POLICY_CONTEXT	FND	Initialise Multi-Org Access Control and set the operating unit for secured synonyms	INIT(p_appl_short_name, for example 'ONT', 'PO', 'AR'); SET_POLICY_CONTEXT(p_access_mode 'S' single or 'M' multiple, p_org_id)	After APPS_INITIALIZE	Raises an exception if the OU is not accessible
FND_REQUEST.SUBMIT_REQUEST	FND	Submit a concurrent request from PL/SQL	Function returning NUMBER; application (short name), program (short name), description, start_time, sub_request (FALSE), argument1 to argument100; call FND_REQUEST.SET_ORG_ID first for org-aware programs	APPS_INITIALIZE; COMMIT after the call or the request never runs	Returns 0 on failure; FND_MESSAGE.GET
FND_CONCURRENT.WAIT_FOR_REQUEST	FND	Wait for a request to complete and return its phase and status	Function returning BOOLEAN; request_id, interval (seconds), max_wait (seconds, 0 = no limit); OUT phase, status, dev_phase, dev_status, message	Request must be committed first	dev_phase 'COMPLETE' with dev_status 'NORMAL', 'WARNING' or 'ERROR'
FND_USER_PKG.CREATEUSER / ADDRESP	FND	Create an application user; assign a responsibility	CREATEUSER: x_user_name, x_owner, x_unencrypted_password, x_start_date, x_email_address. ADDRESP: username, resp_app, resp_key, security_group, description, start_date, end_date	Run as APPS; commit afterwards	Raises exceptions; check FND_USER and FND_USER_RESP_GROUPS_DIRECT
FND_PROFILE.VALUE / SAVE	FND	Read a profile value in the current context; set a value at a level	VALUE(name). SAVE returns BOOLEAN: x_name, x_value, x_level_name ('SITE', 'APPL', 'RESP', 'USER'), x_level_value, x_level_value_app_id	VALUE needs APPS_INITIALIZE to resolve user and responsibility levels; SAVE needs a COMMIT	SAVE returns FALSE on failure
WF_ENGINE.CREATEPROCESS / STARTPROCESS / COMPLETEACTIVITY / HANDLEERROR	Workflow	Create and start a workflow item; push a blocked activity forward; retry or skip an errored activity	CREATEPROCESS(itemtype, itemkey, process, user_key, owner_role); STARTPROCESS(itemtype, itemkey); COMPLETEACTIVITY(itemtype, itemkey, activity, result); HANDLEERROR(itemtype, itemkey, activity, command 'RETRY' or 'SKIP', result)	APPS session; activity is the instance label, optionally prefixed with process name and colon	Raises WF_CORE exceptions; check WF_ITEM_ACTIVITY_STATUSES
FND_MSG_PUB.GET / COUNT_MSG	FND	Read messages from the standard API message stack	GET(p_msg_index, p_encoded => FND_API.G_FALSE, p_data OUT, p_msg_index_out OUT); FND_MSG_PUB.INITIALIZE clears the stack	Same session as the API call	Use in a loop from 1 to x_msg_count

Scope: non-HR public APIs in EBS 12.1 and 12.2 (HR APIs are in a separate reference). Parameter lists are abbreviated to the ones you usually set; always check the package specification on your instance or the Integration Repository before coding.

Get an API wrapper reviewed

Send us your PL/SQL wrapper and your EBS patch level. We will check signatures, context setup and error handling against your release.

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

Was this tool useful?

The standard calling pattern

Most Oracle public APIs follow the same contract. IN parameters start with p_api_version (or p_api_version_number), p_init_msg_list (FND_API.G_TRUE to clear old messages) and p_commit (FND_API.G_FALSE so you control the transaction). OUT parameters include x_return_status, x_msg_count and x_msg_data. x_return_status is 'S' (FND_API.G_RET_STS_SUCCESS), 'E' (expected validation error) or 'U' (unexpected error). Business data is passed as record or table types declared in the package spec, such as OE_ORDER_PUB.HEADER_REC_TYPE; initialise them from the package's G_MISS constants (for example OE_ORDER_PUB.G_MISS_HEADER_REC) so unset columns mean 'not supplied'. A few older APIs are functions returning BOOLEAN or NUMBER instead, such as PO_CHANGE_API1_S.UPDATE_PO and AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE.

Use named notation (p_header_rec => l_hdr) so signature changes between patch levels fail loudly
Commit or roll back yourself after checking x_return_status
Run as APPS, never as a product schema

Setting the context

An API called from SQL*Plus or a database job has no user, responsibility or operating unit until you set them. Call FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id) with a user and responsibility that could do the task in the forms; this sets WHO columns and profile values. For org-striped data call MO_GLOBAL.INIT with the product short name and then MO_GLOBAL.SET_POLICY_CONTEXT('S', org_id). Missing context is the most common reason an API returns 'E' with a vague message, or succeeds but creates data in the wrong operating unit. For concurrent requests submitted from PL/SQL, also call FND_REQUEST.SET_ORG_ID before SUBMIT_REQUEST when the program is org-aware. Inside a concurrent program the manager has already set context, so do not reset it.

Where to find APIs: the Integration Repository

Oracle publishes supported interfaces in the Integration Repository, reached in 12.2 through the Integrated SOA Gateway responsibility. Search by product family or by name to see each PL/SQL package marked as public, with procedures, parameters and descriptions. Anything not listed there, or marked private (packages ending in _PVT), is not supported for customer use and can change in a patch. On a running instance the package spec in ALL_SOURCE (WHERE NAME = 'OE_ORDER_PUB' AND TYPE = 'PACKAGE') is the final word on the signature at your patch level. Product implementation and API guides on Oracle Help Center give worked examples for OM, TCA, Receivables and Purchasing APIs.

Worked error loop

After calling an API that uses the FND message stack, read every message, not just x_msg_data: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN FOR i IN 1 .. l_msg_count LOOP l_msg := FND_MSG_PUB.GET(p_msg_index => i, p_encoded => FND_API.G_FALSE); DBMS_OUTPUT.PUT_LINE(i || ': ' || l_msg); END LOOP; ROLLBACK; END IF. Order Management keeps its own stack, so use OE_MSG_PUB.GET with the same arguments after PROCESS_ORDER. BOM and EGO APIs use ERROR_HANDLER, and INV_ITEM_GRP returns an error table. In production code, write messages to your own log table with the source record key rather than DBMS_OUTPUT, so a failed batch can be reworked row by row.

Frequently Asked Questions

What are APIs in Oracle Apps R12?

They are Oracle-supported PL/SQL packages, mostly named with a _PUB suffix, that create or change EBS data with the same validation as the forms. Examples are OE_ORDER_PUB.PROCESS_ORDER for sales orders, AP_VENDOR_PUB_PKG for suppliers and HZ_ V2PUB packages for customers. Public ones are listed in the Integration Repository.

Which API creates a credit memo in Oracle EBS?

AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST creates a credit memo request against an existing invoice using p_customer_trx_id, amounts and a reason code; with p_skip_workflow_flag set to 'Y' it creates the credit memo without the approval workflow. AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE can also create transactions of a credit memo type.

Why does my API return success but nothing is created?

Usually because p_commit was FND_API.G_FALSE and the session ended without a COMMIT, or the data went to another operating unit because MO context was set to a different org. For FND_REQUEST.SUBMIT_REQUEST, the request ID is returned but the request is invisible until you commit.

Are these APIs safe to call from an external integration?

Yes, through a supported channel: a PL/SQL wrapper called by your middleware, or the REST or SOAP services Integrated SOA Gateway can deploy over the same public packages in 12.2. Always run with a dedicated integration user and responsibility, log every call, and never call private _PVT packages.

If you want a private AI agent that prepares these API calls from plain-English requests and waits for a human approval before anything is written to EBS, we can build that on your own servers.

Talk to an Expert Explore More Free Tools