OracleApps: 2015

Generate XML Output using SQL & PL/SQL

Example1:
DECLARE
l_refcursor SYS_REFCURSOR;
l_xmltype XMLTYPE;
BEGIN
OPEN l_refcursor FOR
SELECT deptno, dname
FROM dept
WHERE deptno IN (10, 20);

l_xmltype := XMLTYPE (l_refcursor);
DBMS_OUTPUT.put_line (l_xmltype.getClobVal);
END;
/
--===============================================================
Example2:
DECLARE
l_xmltype XMLTYPE;
BEGIN
l_xmltype :=
DBMS_XMLGEN.
getxmltype (
'SELECT deptno , dname FROM dept WHERE deptno IN (10,20)');
DBMS_OUTPUT.put_line (l_xmltype.getClobVal);
END;
/
--================================================================
Example3:
DECLARE
l_xmltype XMLTYPE;
l_ctx DBMS_XMLGEN.ctxhandle;
BEGIN
l_ctx :=
DBMS_XMLGEN.
newcontext (
'SELECT deptno , dname FROM dept WHERE deptno in (10,20)');
DBMS_XMLGEN.setrowsettag (l_ctx, 'Departments');
DBMS_XMLGEN.setrowtag (l_ctx, 'Dept');
l_xmltype := DBMS_XMLGEN.getXmlType (l_ctx);
DBMS_XMLGEN.closeContext (l_ctx);
DBMS_OUTPUT.put_line (l_xmltype.getClobVal);
END;
/
--================================================================
Example4:The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.

DECLARE
l_xmltype XMLTYPE;
l_domdoc DBMS_XMLDOM.DOMDocument;
l_root_node DBMS_XMLDOM.DOMNode;
l_department_element DBMS_XMLDOM.DOMElement;
l_departments_node DBMS_XMLDOM.DOMNode;
l_dept_element DBMS_XMLDOM.DOMElement;
l_dept_node DBMS_XMLDOM.DOMNode;
l_name_element DBMS_XMLDOM.DOMElement;
l_name_node DBMS_XMLDOM.DOMNode;
l_name_text DBMS_XMLDOM.DOMText;
l_name_textnode DBMS_XMLDOM.DOMNode;
l_location_element DBMS_XMLDOM.DOMElement;
l_location_node DBMS_XMLDOM.DOMNode;
l_location_text DBMS_XMLDOM.DOMText;
l_location_textnode DBMS_XMLDOM.DOMNode;
BEGIN
-- Create an empty XML document
l_domdoc := DBMS_XMLDOM.newDomDocument;
-- Create a root node
l_root_node := DBMS_XMLDOM.makeNode (l_domdoc);
-- Create a new node Departments and add it to the root node
l_department_element :=
DBMS_XMLDOM.createElement (l_domdoc, 'Deptartments');
l_departments_node :=
DBMS_XMLDOM.
appendChild (l_root_node, DBMS_XMLDOM.makeNode (l_department_element));

FOR r_dept IN (SELECT dept.department_id, dept.department_name, loc.city
FROM departments dept
JOIN
locations loc
ON loc.location_id = dept.location_id
WHERE dept.department_id IN (10, 20))
LOOP
-- For each record, create a new Dept element with the Department ID as attribute.
-- and add this new Dept element to the Departments node
l_dept_element := DBMS_XMLDOM.createElement (l_domdoc, 'Dept');
DBMS_XMLDOM.
setAttribute (l_dept_element, 'DeptID', r_dept.department_id);
l_dept_node :=
DBMS_XMLDOM.
appendChild (l_departments_node,
DBMS_XMLDOM.makeNode (l_dept_element));
-- Each Dept node will get a Name node which contains the department name as text
l_name_element := DBMS_XMLDOM.createElement (l_domdoc, 'Name');
l_name_node :=
DBMS_XMLDOM.
appendChild (l_dept_node, DBMS_XMLDOM.makeNode (l_name_element));
l_name_text :=
DBMS_XMLDOM.createTextNode (l_domdoc, r_dept.department_name);
l_name_textnode :=
DBMS_XMLDOM.
appendChild (l_name_node, DBMS_XMLDOM.makeNode (l_name_text));
-- Each Dept node will aslo get a Location node which contains the location(city) as text
l_location_element := DBMS_XMLDOM.createElement (l_domdoc, 'Location');
l_location_node :=
DBMS_XMLDOM.
appendChild (l_dept_node,
DBMS_XMLDOM.makeNode (l_location_element));
l_location_text := DBMS_XMLDOM.createTextNode (l_domdoc, r_dept.city);
l_location_textnode :=
DBMS_XMLDOM.
appendChild (l_location_node,
DBMS_XMLDOM.makeNode (l_location_text));
END LOOP;

l_xmltype := DBMS_XMLDOM.getXmlType (l_domdoc);
DBMS_XMLDOM.freeDocument (l_domdoc);
DBMS_OUTPUT.put_line (l_xmltype.getClobVal);
END;

/
--================================================================
Example5:In this example added the employees to each department. Created a new cursor loop on employees inside the department cursor loop, so that the employees of that department are added in a child node of the department. Also changed some code: cast(dbms_xmldom.makeNode) the elements directly to a node, this way need less variables:

DECLARE
l_domdoc DBMS_XMLDOM.DOMDocument;
l_xmltype XMLTYPE;
l_root_node DBMS_XMLDOM.DOMNode;
l_departments_node DBMS_XMLDOM.DOMNode;

l_dept_element DBMS_XMLDOM.DOMElement;

l_dept_node DBMS_XMLDOM.DOMNode;

l_name_node DBMS_XMLDOM.DOMNode;

l_name_textnode DBMS_XMLDOM.DOMNode;

l_location_node DBMS_XMLDOM.DOMNode;

l_location_textnode DBMS_XMLDOM.DOMNode;

l_employees_node DBMS_XMLDOM.DOMNode;

l_emp_element DBMS_XMLDOM.DOMElement;

l_emp_node DBMS_XMLDOM.DOMNode;

l_emp_first_name_node DBMS_XMLDOM.DOMNode;

l_emp_first_name_textnode DBMS_XMLDOM.DOMNode;

l_emp_last_name_node DBMS_XMLDOM.DOMNode;

l_emp_last_name_textnode DBMS_XMLDOM.DOMNode;

BEGIN

-- Create an empty XML document

l_domdoc := DBMS_XMLDOM.newDomDocument;

-- Create a root node

l_root_node := DBMS_XMLDOM.makeNode (l_domdoc);

-- Create a new node Departments and add it to the root node

l_departments_node :=

DBMS_XMLDOM.

appendChild (

l_root_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'Deptartments')));

FOR r_dept IN (SELECT dept.department_id, dept.department_name, loc.city

FROM departments dept

JOIN

locations loc

ON loc.location_id = dept.location_id

WHERE dept.department_id IN (10, 20))

LOOP

-- For each record, create a new Dept element with the Department ID as attribute.

-- and add this new Dept element to the Departments node

l_dept_element := DBMS_XMLDOM.createElement (l_domdoc, 'Dept');

DBMS_XMLDOM.

setAttribute (l_dept_element, 'Deptno', r_dept.Department_Id);

l_dept_node :=

DBMS_XMLDOM.

appendChild (l_departments_node,

DBMS_XMLDOM.makeNode (l_dept_element));

-- Each Dept node will get a Name node which contains the department name as text

l_name_node :=

DBMS_XMLDOM.

appendChild (

l_dept_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'Name')));

l_name_textnode :=

DBMS_XMLDOM.

appendChild (

l_name_node,

DBMS_XMLDOM.

makeNode (

DBMS_XMLDOM.createTextNode (l_domdoc, r_dept.department_name)));

-- Each Dept node will aslo get a Location node which contains the location(city) as text

l_location_node :=

DBMS_XMLDOM.

appendChild (

l_dept_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'Location')));

l_location_textnode :=

DBMS_XMLDOM.

appendChild (

l_location_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createTextNode (l_domdoc, r_dept.city)));

-- For each department, add an Employees node

l_employees_node :=

DBMS_XMLDOM.

appendChild (

l_dept_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'Employees')));

FOR r_emp IN (SELECT employee_id, first_name, last_name

FROM employees

WHERE department_id = r_dept.department_id)

LOOP

-- For each record, create a new Emp element with the Employee ID as attribute.

-- and add this new Emp element to the Employees node

l_emp_element := DBMS_XMLDOM.createElement (l_domdoc, 'Emp');

DBMS_XMLDOM.setAttribute (l_emp_element, 'empid', r_emp.employee_id);

l_emp_node :=

DBMS_XMLDOM.

appendChild (l_employees_node,

DBMS_XMLDOM.makeNode (l_emp_element));

-- Each emp node will get a First name and Last name node which contains the first name and last name as text

l_emp_first_name_node :=

DBMS_XMLDOM.

appendChild (

l_emp_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'FirstName')));

l_emp_first_name_textnode :=

DBMS_XMLDOM.

appendChild (

l_emp_first_name_node,

DBMS_XMLDOM.

makeNode (

DBMS_XMLDOM.createTextNode (l_domdoc, r_emp.first_name)));

l_emp_last_name_node :=

DBMS_XMLDOM.

appendChild (

l_emp_node,

DBMS_XMLDOM.

makeNode (DBMS_XMLDOM.createElement (l_domdoc, 'LastName')));

l_emp_last_name_textnode :=

DBMS_XMLDOM.

appendChild (

l_emp_last_name_node,

DBMS_XMLDOM.

makeNode (

DBMS_XMLDOM.createTextNode (l_domdoc, r_emp.last_name)));

END LOOP;

l_xmltype := DBMS_XMLDOM.getXmlType (l_domdoc);

DBMS_XMLDOM.freeDocument (l_domdoc);

DBMS_OUTPUT.put_line (l_xmltype.getClobVal);

END;

http://www.oracleerpfunctional.com/2013/09/generating-xml-from-sql-and-plsql.html

Cascade Modified Attributes from Sales Order Header to Lines

Oracle Service Contracts Table Joins

Oracle Install Base Stuff

http://oracleerpworld.blogspot.com/2013/07/ib-sql-to-get-extract-for-given-party.html

TCA tables and links

Oracle E-Business Suite Documentation Library

Oracle E-Business Suite Documentation Library

XML / BI Publisher Tags

For Loop

Start: <?for-each:GRP_NAME?>

End: <?end for-each?>

Conditonal Highlighting with Text Decoration(IF Condition)

<?if:col_name>value?><xsl:attribute xdofo:ctx="block" name="font-weight">bold</xsl:attribute><?end if?>

SQL & PL/SQL Stuff

Click on the Link:
PL/SQL Tutorial
http://viralpatel.net/blogs/category/database/oracle-database/
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm
Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
http://psoug.org/browse.htm?page=1&cid=4
http://srikanthtechnologies.com/books/orabook/oraclebook.html
http://www.java2s.com/Tutorial/Oracle/CatalogOracle.htm***
http://docstore.mik.ua/orelly/oracle/prog2/ch19_01.htm
http://www.toadworld.com/platforms/oracle/w/wiki/2169.regular-expressions-overview-and-metacharacters.aspx

Compile all the invalid objects in a Schema

begin
dbms_utility.compile_schema('SCHEMA');
end;
/

Note: Schema name should be in UPPER Case

Order Management API's

Create Sales Order using Process Order 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 ;
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_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;
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 := 'Test-Venu';
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 := -3;
l_header_rec.order_type_id := 1000;
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;

Create Sales Order in Booked status using Process Order 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 ;
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_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;
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 := 'BookTestAPI';
l_header_rec.sold_to_org_id := 5371;
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.ship_from_org_id := 204;
l_header_rec.salesrep_id := 1001;
l_header_rec.order_type_id := 1000;
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 := 204;
l_line_tbl ( l_line_tbl_index ).unit_selling_price := 101;
l_line_tbl ( l_line_tbl_index ).unit_list_price := 101;
--l_line_tbl ( l_line_tbl_index ) .subinventory := 'FGI';
l_line_tbl ( l_line_tbl_index ).calculate_price_flag := 'Y';
l_line_tbl ( l_line_tbl_index ).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl_index := 1;
l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order;
l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header;
-- l_action_request_tbl(l_line_tbl_index).entity_id := 190383;
-- 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 => x_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;

Adding a new line to the existing Sales Order 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;
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);
p_header_rec oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
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;
x_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;
x_debug_file VARCHAR2(100);
l_msg_index_out NUMBER(10);
l_line_tbl_index NUMBER;
BEGIN
dbms_output.ENABLE(1000000);
fnd_global.Apps_initialize(1318, 21623, 660);
-- pass in user_id, responsibility_id, and application_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
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

--X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
--oe_debug_pub.SetDebugLevel(5);
--dbms_output.put_line('START OF NEW DEBUG');
--This is to add a line to an existing order
l_line_tbl_index := 1;
-- Initialize record to missing
L_line_tbl(l_line_tbl_index) := oe_order_pub.g_miss_line_rec;
-- Line attributes
--Populate the header id of the order
L_line_tbl(l_line_tbl_index).header_id := 190337;
--Mandatory fields like qty, inventory item id are to be passed
L_line_tbl(l_line_tbl_index).ordered_quantity := 5;
L_line_tbl(l_line_tbl_index).inventory_item_id := 149;
L_line_tbl(l_line_tbl_index).ship_from_org_id := 207;
L_line_tbl(l_line_tbl_index).subinventory := 'FGI';

--Operation set to Create
L_line_tbl(l_line_tbl_index).operation := oe_globals.g_opr_create;

-- CALL TO PROCESS ORDER
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 => 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_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('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
-- Retrieve messages
dbms_output.Put_line('Line Id: ' ||x_line_tbl(l_line_tbl_index).line_id);

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 Success to add a Line');
ELSE
dbms_output.Put_line('Failed');
END IF;
END;
/
COMMIT;

OracleApps

Generate XML Output using SQL & PL/SQL

Cascade Modified Attributes from Sales Order Header to Lines

Oracle Service Contracts Table Joins

Oracle Install Base Stuff

TCA tables and links

Oracle E-Business Suite Documentation Library

XML / BI Publisher Tags

SQL & PL/SQL Stuff

Compile all the invalid objects in a Schema

Order Management API's

Followers

Blog Archive