, , , , , , , , ,

R12: Create, Allocate and Transact a Move Order using APIs

Move Orders in Oracle provide good functionality for approval based movement of material with in an inventory organization. Move Order Approvals use a workflow (INV: Move Order Approval) for approvals and hence provide flexibility to extend it for approvals.
Move Orders can be created from different sources. These move orders are identified by Move order type at the header level.

Different types of move orders are:
Move Order Requisitions: These are created from the move order form. Based on the setup at the organization and item level, move orders require approval before they can be allocated and transacted. These requisitions can be created to charge an account or move the material from one subinventory to another.
Replenishment Move Orders: These are auto-created with pre approved status. Depending on the planning type you can replenish the quantity in a subinventory for an item. Three types of replenishment plans (in relation to move orders) are available:

  1. Min-max Replenishment Planning
  2. Kanban Replenishment Planning
  3. Replenishing Count planning

Based on these sources, when appropriate programs are run, move orders are automatically created.
Pick Wave Move Order Requisitions: These move orders are specifically for the sales order picking process. When Pick Releasing program is run move orders are created as preapproved and allocated. Pick Confirm process transacts these pick wave move orders.
Move orders contain headers and lines. All types of approved move orders have to be allocated (basically reserved) before they can be transacted. Depending on the item transaction controls (Subinventory, Locator, Revision, Lot or Serial number) move order creation and allocation gets complex.
The process is simple. Create and approve Move orders, allocate move order lines and transact them.
In this article let us review creating, allocating, and transacting a move order requisition of first type (requisitions) using APIs. These move orders will be created in pre approved status and hence do not use workflow. Move Order Issue transaction type is used in this example. An account is chosen while creating this move order. When successfully transacted, this account gets debited, crediting inventory account of the subinventory. From the user interface, to create Move orders, Move Orders window is used.
The lines are allocated when allocation step is performed. In this example the logic for allocation and transaction is borrowed from pick confirmation code. To allocate and transact same form is used: Transact Move Orders.
If the item is lot and serial number controlled (as in this example), you have to pass the lot number and serial number information into the move order lines table parameter. And also make sure to pass another parameter (p_suggess_serial) as true. This will automatically take care of allocating the lot and serial numbers into the appropriate table as mentioned in the process flow. Also when we allocate the move order from the user interface, the records in the form are created in the mtl_material_transactions_temp table with transaction_status as 2, which allows the user to change the values from user interface. But in order to perform transaction from SQL, you have to change this value to 3 so that the transaction manager can pick these records up. You find that logic in the allocating code.
The tables affected in each process step are mentioned in the process flow. Code for each step as well as complete code to create, allocate and transact can be see here:
Oracle R12 Create Order using APIs
Oracle R12 Allocate Order using APIs
Oracle R12 Transact Order using APIs
Oracle R12 Move Order using APIs

/by
, , , , , , , ,

How SLA derives all the rules required for Create Accounting?

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?
We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.
This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.
Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C’;

DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);
FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);
BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ’0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ’0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;

/by
, , , , , , ,

Product Life Cycle Management

Here is the code snippet for creating items using API (traditionally we are used to use item open interface) in the Product Life Cycle Management process. While this does not replace loading items using open interface, this API can be used to manage the lifecycle of the item on a dialy basis.

Over a period time, companies conceptualize products, design them, build them, manage them and finally, retire them.

Here in the article I am introducing the API first. In the subsequent articles, we will manage the “lifecycle” of the product using the same API.

These code snippets are tested in 12.0.4.

CREATE OR REPLACE PACKAGE xx_create_item
IS
   g_miss_num    CONSTANT NUMBER       := 9.99e125;
   g_miss_char   CONSTANT VARCHAR2 (1) := CHR (0);
   g_miss_date   CONSTANT DATE         := TO_DATE (‘1’, ‘j’);
   g_false       CONSTANT VARCHAR2 (1) := fnd_api.g_false;                                                               
   g_true        CONSTANT VARCHAR2 (1) := fnd_api.g_true;

   PROCEDURE create_item (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   );

   PROCEDURE create_item1 (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   );
END xx_create_item;
/

CREATE OR REPLACE PACKAGE BODY xx_create_item
IS
   PROCEDURE create_item (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   )
   IS
      l_template_id       NUMBER;
      x_item_id           NUMBER;
      x_org_id            NUMBER;
      l_item_number       VARCHAR2 (100);
      l_description       VARCHAR2 (4000);
      l_organization_id   NUMBER;
      v_msg_index_out     NUMBER;
      v_message           VARCHAR2 (100);
   BEGIN
      SELECT template_id
        INTO l_template_id
        FROM mtl_item_templates_b
       WHERE template_name = p_item_type;                                                                    

      l_item_number := p_item_number;
      l_description := p_description;
      l_organization_id := l_organization_id;
      ego_item_pub.process_item (p_api_version                 => 1.0
                               , p_transaction_type            => ‘CREATE’
                               , p_language_code               => ‘US’
                               , p_template_id               => 207
                               , p_organization_id             => 204
                               , p_master_organization_id      => 204
                               , p_description                 => ‘TEST113’
                               , p_long_description            => ‘TEST113’
                               , p_item_number                 => ‘TEST113’
                               , p_segment1                    => ‘TEST113’
                               , x_inventory_item_id           => x_inventory_item_id
                               , x_organization_id             => x_organization_id
                               , x_return_status               => x_return_status
                               , x_msg_count                   => x_msg_count
                               , x_msg_data                    => x_msg_data
                                );

      IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => ‘F’, p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            v_message := SUBSTR (x_msg_data, 1, 200);
            DBMS_OUTPUT.put_line (x_msg_data);
            DBMS_OUTPUT.put_line (‘============================================================’);
         END LOOP;

         DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
         DBMS_OUTPUT.put_line (‘============================================================’);
      END IF;
   END;

   PROCEDURE create_item1 (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   )
   IS
      l_template_id       NUMBER;
      l_item_number       VARCHAR2 (100);
      l_description       VARCHAR2 (4000);
      l_organization_id   NUMBER;
      v_msg_index_out     NUMBER;
      v_message           VARCHAR2 (100);
   BEGIN
      l_item_number := p_item_number;
      l_description := p_description;
      l_organization_id := p_organization_id;
      ego_item_pub.process_item (p_api_version            => 1.0
                               , p_transaction_type       => ‘CREATE’
                               , p_language_code          => ‘US’
                               , p_template_name          => p_item_type
                               , p_item_number            => l_item_number
                               , p_segment1               => l_item_number
                               , p_organization_id        => l_organization_id
                               , p_description            => l_item_number
                               , p_long_description       => l_item_number
                               , x_inventory_item_id      => x_inventory_item_id
                               , x_organization_id        => x_organization_id
                               , x_return_status          => x_return_status
                               , x_msg_count              => x_msg_count
                                );
   END;
END xx_create_item;
/

SHOW errors
/
DECLARE
   v_msg_index_out   NUMBER;
   x_item_id         NUMBER;
   x_org_id          NUMBER;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   v_message         VARCHAR2 (4000);
BEGIN
   fnd_global.apps_initialize (1318, 50583, 401);
   inv_globals.set_org_id (204);
   xx_create_item.create_item1 (p_item_number            => ‘TEST111’
                             , p_description            => ‘TEST111’
                             , p_organization_id        => 204
                             , p_item_type              => ‘Finished Good’
                             , x_inventory_item_id      => x_item_id
                             , x_organization_id        => x_org_id
                             , x_return_status          => x_return_status
                             , x_msg_count              => x_msg_count
                             , x_msg_data               => x_msg_data
                              );
         DBMS_OUTPUT.put_line (‘Return Status is :’||x_return_status);                            
         DBMS_OUTPUT.put_line (‘Message Count is :’||x_msg_count);
         DBMS_OUTPUT.put_line (‘Create Item ID  is :’||x_item_id);
         DBMS_OUTPUT.put_line (‘Created in Organization is :’||x_org_id);

   IF x_msg_count > 0
   THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index => v_index, p_encoded => ‘F’, p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
         v_message := SUBSTR (x_msg_data, 1, 200);
         DBMS_OUTPUT.put_line (x_msg_data);
         DBMS_OUTPUT.put_line (‘============================================================’);
      END LOOP;

      DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
      DBMS_OUTPUT.put_line (‘============================================================’);
   END IF;
END;
/

DECLARE
   v_msg_index_out   NUMBER;
   x_item_id         NUMBER;
   x_org_id          NUMBER;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   v_message         VARCHAR2 (4000);
BEGIN
   –fnd_global.apps_initialize (1318, 50583, 401);
   inv_globals.set_org_id (204);
   xx_create_item.create_item1 (p_item_number            => ‘TEST111’
                             , p_description            => ‘TEST111’
                             , p_organization_id        => 204
                             , p_item_type              => ‘Finished Good’
                             , x_inventory_item_id      => x_item_id
                             , x_organization_id        => x_org_id
                             , x_return_status          => x_return_status
                             , x_msg_count              => x_msg_count
                             , x_msg_data               => x_msg_data
                              );
         DBMS_OUTPUT.put_line (‘Return Status is :’||x_return_status);                            
         DBMS_OUTPUT.put_line (‘Message Count is :’||x_msg_count);
         DBMS_OUTPUT.put_line (‘Create Item ID  is :’||x_item_id);
         DBMS_OUTPUT.put_line (‘Created in Organization is :’||x_org_id);

   IF x_msg_count > 0
   THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index => v_index, p_encoded => ‘F’, p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
         v_message := SUBSTR (x_msg_data, 1, 200);
         DBMS_OUTPUT.put_line (x_msg_data);
         DBMS_OUTPUT.put_line (‘============================================================’);
      END LOOP;

      DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
      DBMS_OUTPUT.put_line (‘============================================================’);
   END IF;
END;

/by
, , , , , , , , ,

Use Of Oe_Order_Pub.Process_Order To Create Sale Order

Use Of Oe_Order_Pub.Process_Order To Create Sale Order
    Process Order API is a PL/SQL packaged procedure which can be used to manipulate the sales order data by performing Insert, update or delete operation on the following sales Order business object entities.
Analogous to other public API’s, Process Order API also validates the data before inserting them into the application tables.
Though Process Order API has packaged procedures which will insert, update, delete data into the tables, they can not be run on their own. Either they need to be called from another package procedure or can be executed as PL/SQL block via the sql*plus.

Related Data Table Names

Order Header===> OE_ORDER_HEADERS_ALL
Order Line===> OE_ORDER_LINES_ALL
Order Price Adjustments===> OE_PRICE_ADJUSTMENTS
Order Sales Credits===> OE_SALES_CREDITS
Order Pricing Attributes===> OE_ORDER_PRICE_ATTRIBS
Order Adjustment Attributes===> OE_PRICE_ADJ_ATTRIBS
Order Adjustment Associations===> OE_PRICE_ADJ_ASSOCS
Line Sales Credits===> OE_SALES_CREDITS
Line Price Adjustments> OE_PRICE_ADJUSTMENTS
Line Pricing Attributes===> OE_ORDER_PRICE_ATTRIBS
Line Adjustment Attributes===> OE_PRICE_ADJ_ATTRIBS
Line Adjustment Associations===> OE_PRICE_ADJ_ASSOCS
Lot Serial Numbers ===>OE_LOT_SERIAL_NUMBERS

R12 – SAMPLE SCRIPT TO CREATE SALES ORDER USING OE_ORDER_PUB.PROCESS_ORDER

DECLARE
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
— PARAMETERS
l_debug_level number := 5; — OM DEBUG LEVEL (MAX 5)
l_org number := 308; — OPERATING UNIT
l_no_orders number := 1; — NO OF ORDERS
— INPUT VARIABLES FOR PROCESS_ORDER API
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;
— OUT VARIABLES FOR PROCESS_ORDER API
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
BEGIN
— INITIALIZATION REQUIRED FOR R12
mo_global.set_policy_context (‘S’, l_org);
mo_global.init(‘ONT’);
— INITIALIZE DEBUG INFO
IF (l_debug_level > 0) THEN
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode(‘FILE’);
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
END IF;
— INITIALIZE ENVIRONMENT
fnd_global.apps_initialize (user_id => 2083,
resp_id => 21623,
resp_appl_id => 660);

— INITIALIZE HEADER RECORD
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
— POPULATE REQUIRED ATTRIBUTES
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.TRANSACTIONAL_CURR_CODE := ‘AUD’;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.cust_po_number := ‘TSTPO30’;
l_header_rec.sold_to_org_id := 1006685;
l_header_rec.price_list_id := 33019;
l_header_rec.ordered_date := SYSDATE;
l_header_rec.shipping_method_code := ‘000001_Toll IPEC_T_2T5DGRD’;
l_header_rec.sold_from_org_id := 308;
l_header_rec.ship_from_org_id := 381;
l_header_rec.ship_to_org_id := 2005460;
l_header_rec.salesrep_id := 100000069;
l_header_rec.flow_status_code:=’ENTERED’;
l_header_rec.order_type_id := 5389;
— REQUIRED HEADER DFF INFORMATIONS
l_header_rec.attribute1 :=193; — Entering Branch
l_header_rec.attribute3 := ‘Y’; — Indexation applicable
l_header_rec.attribute5 := ‘2.5’; — Indexation Tolerance percentage
l_header_rec.attribute7 := 100000045; — Field Sales representative
l_header_rec.attribute11 := ‘100’; — Indexation Applicability
— INITIALIZE ACTION REQUEST RECORD
l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
— INITIALIZE LINE RECORD
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE; — Mandatory Operation to Pass
l_line_tbl(1).inventory_item_id := 102775;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).ship_from_org_id := 381;
l_line_tbl(1).subinventory := ‘SELLABLE’;

— REQUIRED LINE DFF INFORMATIONS
l_line_tbl(1).attribute2 := ‘20.99998’; — Gross Margin
l_line_tbl(1).attribute3 := ‘2.493288’; — Business Cost
l_line_tbl(1).attribute10 := ‘1000’; — Original Cust Requested Qty
l_line_tbl(1).attribute11 := ‘662.772’; — Baseline Margin
l_line_tbl(1).attribute16 := ‘DBP’; — Buy Price Basis
for i in 1..l_no_orders loop — BEGIN LOOP
— CALLTO PROCESS ORDER API
oe_order_pub.process_order(
p_org_id => l_org,
p_operating_unit => NULL,
p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
— OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
— CHECK RETURN STATUS
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE(‘Sales Order Successfully Created’);
END IF;

COMMIT;
ELSE
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE(‘Failed to Create Sales Order’);
END IF;
ROLLBACK;
END IF;
END LOOP;
— DISPLAY RETURN STATUS FLAGS
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE(‘Process Order Return Status is: ========>’ l_return_status);
DBMS_OUTPUT.PUT_LINE(‘Process Order msg data is: ===========>’ l_msg_data);
DBMS_OUTPUT.PUT_LINE(‘Process Order Message Count is:=======>’ l_msg_count);
DBMS_OUTPUT.PUT_LINE(‘Sales Order Created is:===============>’ to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE(‘Booked Flag for the Sales Order is:======>’ l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE(‘Header_id for the Sales Order is:========>’ l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE(‘Flow_Status_Code For the Sales Order is=>:’ l_header_rec_out.flow_status_code);
END IF;
— DISPLAY ERROR MSGS
IF (l_debug_level > 0) THEN
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_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE(‘message is:’ l_data);
DBMS_OUTPUT.PUT_LINE(‘message index is:’ l_msg_index);
END LOOP;
END IF;
IF (l_debug_level > 0) THEN
DBMS_OUTPUT.PUT_LINE( ‘Debug = ‘ OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE( ‘Debug Level = ‘ to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE( ‘Debug File =’ OE_DEBUG_PUB.G_DIR’/’OE_DEBUG_PUB.G_FILE);
OE_DEBUG_PUB.DEBUG_OFF;
END IF;
END;

/by
, , , , , , , , ,

Use of PO_REQAPPROVAL_INIT1.START_WF_PROCESS API

Use of PO_REQAPPROVAL_INIT1.START_WF_PROCESS API to Approve Purchase Orders, Blanket Purchase Agreements & Purchase Requisition …
Some times, we are wondering what exactly Oracle is doing when we are clicking on the Approve button in the Purchase Order/Requisition Form. And How does Approve button call the Purchasing approval workflow.

  • When the Approve button is clicked, the approval modal window form for purchasing approvals iscalled (this is form POXDOAPP.fmb and its attached corresponding library file POXAPAPC.pll.). Both Enter Requisition and Enter Purchase Order forms call the the same approval form.
  • The library file POXAPAPC.pll has a procedure PO_WF_APPROVE_C.SetUpWorkFlow that calls the procedure PO_REQAPPROVAL_INIT1.Start_WF_Process in package file POXWPA1B.pls.
  • This server side procedure calls the workflow and initiates the workflow and processes the document through the workflow…

— R12 – PO – SAMPLE SCRIPT TO APPROVE PURCHASE ORDER
DECLARE
v_item_key VARCHAR2(100);

Cursor c_po_details is

SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.authorization_status
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = ‘PO’
AND authorization_status in (‘INCOMPLETE’, ‘REQUIRES REAPPROVAL’)
AND segment1 = ‘11170000860’; — Enter the Purchase Order Number
BEGIN
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);
FOR p_rec IN c_po_details
LOOP

mo_global.init (p_rec.document_type_code);
mo_global.set_policy_context (‘S’, p_rec.org_id);

SELECT p_rec.po_header_id ‘-‘ to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;
dbms_output.put_line (‘ Calling po_reqapproval_init1.start_wf_process for po_id=>’ p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => ‘POAPPRV’
, ItemKey => v_item_key
, WorkflowProcess => ‘POAPPRV_TOP’
, ActionOriginatedFrom => ‘PO_FORM’
, DocumentID => p_rec.po_header_id — po_header_id
, DocumentNumber => p_rec.segment1 — Purchase Order Number
, PreparerID => p_rec.agent_id — Buyer/Preparer_id
, DocumentTypeCode => p_rec.document_type_code–‘PO’
, DocumentSubtype => p_rec.document_subtype –‘STANDARD’
, SubmitterAction => ‘APPROVE’
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => ‘N’
, FaxFlag => ‘N’
, FaxNumber => NULL
, EmailFlag => ‘N’
, EmailAddress => NULL
, CreateSourcingRule => ‘N’
, ReleaseGenMethod => ‘N’
, UpdateSourcingRule => ‘N’
, MassUpdateReleases => ‘N’
, RetroactivePriceChange => ‘N’
, OrgAssignChange => ‘N’
, CommunicatePriceChange => ‘N’
, p_Background_Flag => ‘N’
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => ‘N’
, p_source_type_code => NULL);
commit;

DBMS_OUTPUT.PUT_LINE (‘The PO which is Approved Now =>’ p_rec.segment1);
END LOOP;
END;

— R12 – PO – SAMPLE SCRIPT TO APPROVE BLANKET PURCHASE AGREEMENT

DECLARE

v_item_key VARCHAR2(100);

Cursor c_po_details is
SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.authorization_status,
pha.approved_flag,
pha.wf_item_type,
pha.wf_item_key
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = ‘PA’
AND authorization_status in (‘INCOMPLETE’, ‘REQUIRES REAPPROVAL’)
AND segment1 = ‘11170000021’; — Enter the BPA Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

FOR p_rec IN c_po_details

LOOP
mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, p_rec.org_id);

SELECT p_rec.po_header_id ‘-‘ to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;

dbms_output.put_line (‘Calling po_reqapproval_init1.start_wf_process for po_id=>’ p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => ‘POAPPRV’
, ItemKey => v_item_key
, WorkflowProcess => ‘POAPPRV_TOP’
, ActionOriginatedFrom => ‘PO_FORM’
, DocumentID => p_rec.po_header_id — po_header_id
, DocumentNumber => p_rec.segment1 — Purchase Order Number
, PreparerID => p_rec.agent_id — Buer/Preparer_id
, DocumentTypeCode => p_rec.document_type_code–‘PA’
, DocumentSubtype => p_rec.document_subtype –‘BLANKET’
, SubmitterAction => ‘APPROVE’
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => ‘N’
, FaxFlag => ‘N’
, FaxNumber => NULL
, EmailFlag => ‘N’
, EmailAddress => NULL
, CreateSourcingRule => ‘N’
, ReleaseGenMethod => ‘N’
, UpdateSourcingRule => ‘N’
, MassUpdateReleases => ‘N’
, RetroactivePriceChange => ‘N’
, OrgAssignChange => ‘N’
, CommunicatePriceChange => ‘N’
, p_Background_Flag => ‘N’
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => ‘N’
, p_source_type_code => NULL);
commit;
dbms_output.put_line (‘The BPA which is Approved Now =>’ p_rec.segment1);

END LOOP;
END;

— R12 – PO – SAMPLE SCRIPT TO APPROVE PURCHASE REQUISITION

DECLARE

v_item_key VARCHAR2(100);

Cursor c_req_details is

SELECT
prh.requisition_header_id,
prh.org_id,
prh.preparer_id,
prh.segment1,
pdt.document_subtype,
pdt.document_type_code,
prh.authorization_status
FROM apps.po_requisition_headers_all prh, apps.po_document_types_all pdt
WHERE prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND pdt.document_type_code = ‘REQUISITION’
AND NVL (authorization_status, ‘INCOMPLETE’) = ‘INCOMPLETE’
AND segment1 = ‘21170000200’; — Enter The Requisition Number
BEGIN

fnd_global.apps_initialize (user_id => 1805,
resp_id => 20707,
resp_appl_id => 201);

FOR p_rec IN c_req_details

LOOP

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, p_rec.org_id);

SELECT p_rec.requisition_header_id ‘-‘ to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;

dbms_output.put_line (‘ Calling po_reqapproval_init1.start_wf_process for requisition =>’ p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => NULL
, ItemKey => v_item_key
, WorkflowProcess => ‘POAPPRV_TOP’
, ActionOriginatedFrom => ‘PO_FORM’
, DocumentID => p_rec.requisition_header_id — requisition_header_id
, DocumentNumber => p_rec.segment1 — Requisition Number
, PreparerID => p_rec.preparer_id
, DocumentTypeCode => p_rec.document_type_code– REQUISITION
, DocumentSubtype => p_rec.document_subtype — PURCHASE
, SubmitterAction => ‘APPROVE’
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => ‘N’
, FaxFlag => ‘N’
, FaxNumber => NULL
, EmailFlag => ‘N’
, EmailAddress => NULL
, CreateSourcingRule => ‘N’
, ReleaseGenMethod => ‘N’
, UpdateSourcingRule => ‘N’
, MassUpdateReleases => ‘N’
, RetroactivePriceChange => ‘N’
, OrgAssignChange => ‘N’
, CommunicatePriceChange => ‘N’
, p_Background_Flag => ‘N’
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => ‘N’
, p_source_type_code => NULL);

commit;

dbms_output.put_line (‘The Requisition which is Approved =>’ p_rec.segment1);
END LOOP;
END;

/by