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;
   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
Posted by at No comments:

Cascade Modified Attributes from Sales Order Header to Lines





Posted by at No comments:

Oracle Service Contracts Table Joins


Posted by at No comments:

Oracle Install Base Stuff

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

Posted by at No comments:

TCA tables and links


Posted by at No comments:

Oracle E-Business Suite Documentation Library

Oracle E-Business Suite Documentation Library
Posted by at No comments:

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?>
Posted by at No comments:

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
Posted by at No comments:

Compile all the invalid objects in a Schema

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

Note: Schema name should be in UPPER Case
Posted by at No comments:

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;
Posted by at No comments:

Oracle Apps Supply Chain Management

http://www.confluentminds.com/Trainings/SCM/index.html

Posted by at No comments:

Creating Service Contracts from Order Management

http://www.oraclebusinessapps.com/videos/omtooks/Presentation_Files/index.html
http://www.oraclebusinessapps.com/2008/07/26/service-contracts-intergraton-with-order-management/


Posted by at No comments:

Oracle Order Management Interview Questions

1. What are base tables and interface tables in Order Management?
A: Interface Tables:
OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL, OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL & OE_CREDITS_IFACE_ALL(Order holds like credit check holds etc.)
Base Tables: 
OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, OE_PRICE_ADJUSTMENTS & OE_SALES_CREDITS
Shipping Tables:
WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS & WSH_DELIVERIES

2: Explain Order to Cash Flow?
A: Create Order
Book the Order
Pick Release & Pick Confirm
Ship Confirm
Invoice to AR

3: Explain Drop Ship Order Flow?
A:  Note: For Drop Shipment Orders Line Source Type will be set to "External"
  1. Create an Order
  2. Book the Order
  3. Run the Workflow Background Process
  4. Line status will changed to "Awaiting Receipt"
  5. Run the Requisition Import (can run with default parameters)
  6. Create Releases concurrent program will submitted automatically(Note: After completion of Requisition Import, Line status remains same "Awaiting Receipt" and we can see the Requisition information using the navigation, Actions(B) -> Additional Line Information(O) -> Drop Ship(T))
  7. Purchasing Responsibility and Autocreate PO using Requisition Number
  8. Click on Automatic(B) and enter the supplier name in supplier field and click on Create(B)
  9. In PO Shipments, we can see the Sales Order Information
  10. In PO Distributions, we can see the Requisition Information
  11. Approve the PO
  12. PO Receipt(Note:After entering the receipt qty and subinventory and once we click on Save, "Receiving Transaction Processor" CP submits in Background)
  13. After completion of PO Receipt, Sales Order Line status changes from "Awaiting Receipt" to "Shipped"
  14.  Run the Workflow Background Process, after completion of this program, Line status changes from "Shipped" to "Closed" 
4: Processing Constraints?
A: Processing Constraints prevent users from adding, updating, deleting, cancelling, splitting of order/lines information beyond certain points in the order cycle. Oracle has provided certain processing constraints which prevent data integrity violations.  Processing Constraints are defined for entities and attributes. Entities includes regions of sales order window such as order, line, order/line price adjustments, order/line sales credits. Attributes includes individual fields of a particular entity such as warehouse, ship to location or quantity etc.

5: Describe Scheduling in Sales Order?
A: It is process of communicating the balance between customer demand and a company's ability to fulfill an order from current inventory and supply sources

6: What are the various ways of Scheduling the sales order?
A: 

  1. Setting Profile Option: OM: AutoSchedule to "Yes" 
  2. From Tools Menu -> Check AutoSchedule checkbox in Sales Order Screen
  3. Right Click on Sales Order Line -> Scheduling -> Schedule

Posted by at No comments:

Oracle Purchasing Interview Questions

A: It is a formal request intended to procure /buy something that is needed by the organization.  These are two types:
Internal Requisition: Created if the items are to be obtained from one inventory location to another location within the same organization.  Here the source of the requisition would be INVENTORY.  There is no approval process for internal requisition.
External Requistion / Purchase Requistion: Created if the items are to be obtained from the external suppliers.  Here the source of the requisition would be SUPPLIERS.  The purchase requisitions are sent for approvals.  

2: What are the tables for Purchase Requistion:
A: PO_REQUISITION_HEADERS_ALL, 
     PO_REQUISITION_LINES_ALL, 
     PO_REQ_DISTRIBUTIONS_ALL.

3: What is Requisition Import Program? and requisition interface tables
A: It is a concurrent program which is used to import requisition from Oracle / Non-Oracle Systems. Interface tables are:
PO_REQUISITION_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_INTERFACE_ERRORS

4: What are the reports associated with Requisition Import Process?
A: Requisition Import Run Report: We can view the number of requisitions created and the number of records that showed an error.
Requisition Import Exceptions Report: We can view all the rows that fail validation.

5: What is the purpose of choosing a Category when creating a requisition?
A: Category box is a required field and is used to define goods and services at a summary or detailed level.

6: What is the difference between rejected and a returned requisition?
A: Only an approver or reviewer can reject requisition.  Only buyer can return a requisition.  A buyer will return a requisition to the preparer for incomplete or inaccurate information.

7: What is RFQ and different types of RFQ's?
A: A RFQ(Request For Quotation) is a formal request sent to the suppliers to find the pricing and other information for an item or items.  Based on the information supplied, the supplier quotes a quotation against the RFQ form. Below are the types of RFQ's:
Catalog: Used for high volume items or items for which your supplier sends you information regularly.  A Catalog quotation or RFQ also includes price breaks at different quantity levels.
Standard: Used for items you will need only once or not very often, but not necessarily for a specific, fixed quantity, location and date.
Bid: Used for a specific, fixed quantity, location and date.  For example, a Bid would be used for a large expensive piece of equipment that you have never ordered before, or for an item that incurs transportation or other special costs.  You cannot specify price breaks for a Bid quotation or RFQ.

8: What is a Purchase Order and explain different types of PO's available
A: A Purchase Order is a commercial document and first official order issued by the buyer to the supplier, indicating types, quantities and agreed prices for products or services the supplier will provide to the buyer.
Four different types of PO's are available:
Standard Purchase Order: Created for One-time purchases for goods and services. Here we know the item, price, payment terms and delivery schedule.
Planned Purchase Order: Created when you have long-term agreement with the supplier. We must specify the details of goods and services, payment terms and the tentative delivery schedule.
Blanket Purchase Agreement: Created when the details of goods and services, payment terms are known but not specific about the delivery schedule.
Contract Purchase Agreement: Created when the terms and conditions of a purchase are known but specific goods and services are not.

9: What is 2-Way, 3-Way & 4-Way matching?
A: Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or Billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and / or received.
2-Way: Purchase order and Invoice quantities must match within tolerance before the corresponding invoice can be paid.
3-Way: Purchase Order, Receipt and Invoice quantity must match within tolerance before the corresponding invoice can be paid.
4-Way: Purchase Order, Receipt, Accepted & Invoice quantity must match within tolerance before the corresponding invoice can be paid.

10: Can the original PO be viewed in anyway without resorting to SQL, for a revised PO?
A: The original version of a revised PO cannot be viewed from the PO form or PO Summary form. Information on the original PO can be obtained from the PO_HEADERS_ARCHIVE & PO_LINES_ARCHIVE tables using the PO_HEADER_ID column as a common reference using SQL only.

11: Can we automatically Close the PO without receiving the full quantity?
A: The Receipt Close Tolerance lets you specify a quantity percentage within which Purchasing closes a partially received shipment.  For Ex: if the Receipt Close Tolerance is 5% and we receive 96% of an expected shipment, Purchasing automatically closes this shipment for receiving.

12: When does the PO line get the status Closed for Receiving?
A: Goods have been received against the PO line but an invoice has not been matched to the order.

13: Can we match an Invoice against a line even when it is Closed for Invoicing?
A: The Close for Invoicing status does not prevent you from matching an invoice to a purchase order or to a receipt.

14: What does Create Internal Order concurrent request do?
A: Create Internal Order concurrent request will transfer the Internal Requisition information to OM Interface tables.

15: Explain the Receipt Routing?
A: Receipt Routing is of 3 types:
Direct: Once the goods are at the destination, we directly move them to a specific sub-inventory.
Standard: Once the goods are at the destination, we receive it at the receiving point and then we move them to a specific sub-inventory.
Inspection: Once the goods are at the destination, we receive it at the receiving point and then we perform inspection and accordingly we either accept it or reject them.  

16: What are different purchasing modes in Receiving?
A: There are 3 Modes,
Online: Receipts are processed online. If there are any errors, they are shown on the Form itself, and don't let you ignore and proceed.
Immediate: Receipts are processed immediately, but no errors are shown. Errors are recorded in RCV_TRANSACTIONS_INTERFACE & PO_INTERFACE_ERRORS
Batch: Receipts are processed in batch, but no errors are shown. Errors are recorded in RCV_TRANSACTIONS_INTERFACE & PO_INTERFACE_ERRORS

Note: In all the above two cases, it requires Receiving Transaction Processor to be run periodically.

17: What the major transactions in Receiving?
A: PO Receipts, Internal Requisition Receipts, Inventory Inter-Org Transfer Receipts and Customer Return Receipts.

18: What is Pay on Receipt AutoInvoice Program?
A: By running this program, we can automatically create an invoice for a PO when we enter a receipt for the respective PO.

19: What is invoice Validation Process?
A: Before you pay or create accounting entries for any invoice, the invoice validation process must validate the invoice.
Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter and automatically applies holds to exception invoices. if an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or to the purchase order, or changing the invoice tolerances.

20: Requisition autocreated in the system to be grouped by Buyer.  How to achieve this functionality?
A: In Purchasing Option, Set requisition group by parameter to "Buyer"

21: How do we ensure if Purchase Order is cancelled then corresponding requisition is also cancelled?
A: In Purchasing Option, Set Cancel requisition to "Optionally" or "Always"

22: If the preparer of Purchase Requisition is not authorized to Approve that requisition then what setup should be performed in the system?
A: In document type, Set Owner can approve flag to No.

23: What is the difference between the control actions "On Hold" and "Freeze" for a Purchase Order?
A: Freeze or Un-Freeze, documents to control whether modifications are allowed or not
On Hold, prevent document from printing, receiving, invoicing and approval until the hold is removed.

24: What is the different document statuses used in Oracle Purchasing?
A: Frozen, Cancelled, Finally Closed, Incomplete, In-Process, Pre-Approved & Approved

25: Can we re-open "Closed" & "Finally Closed" PO
A: We can re-open "Closed" and we can make changes too, but we cannot re-open "Finally Closed" PO

26: How do I change the supplier on an approved PO?
A: No, we cannot change supplier name for an approved PO, you have to create new PO or use copy document if you want to change supplier name but you will get different PO number on this.

Indeed, but if you want to have option that enable you to change "Supplier Site", you can set a profile "PO: Change Supplier Site" to "Yes". Yes indicates whether user can change the supplier site on approved PO's. Only sites with the same currency as the previous site can be used. If you change the supplier site, the revision will be incremented and will require re-approval.

27: When an user enters a new PO, the preparer (User) is always defaulted as the buyer. User is not able to change it too. How do we allow user to change this?
A: This is because an option on "Purchasing Options", Purchasing Options > Document Control > Enforce Buyer Name - If you enable it, PO Preparer cannot change default Buyer name, - by disabling it, you can change buyer seeded data.

28: Payment due days if 30 days mean will it be from invoice date or material receiving date?
A: Due days calculation is based on what we setup in the Payment tab window of supplier master form. It has four options, 1. Goods Received, 2. Invoice, 3. Invoice Received & 4. System

29: Where do we define matching option for purchasing?
A: This is defined in supplier master form under Control tab window. Two options are given as Purchase Order or Receipts. 
Posted by at No comments:
Subscribe to: Posts (Atom)