, , , , , , , ,

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
, , , , , , , , ,

Cancelling PO Using API PO_Document_Control_PUB.control_document

How to use the PO Cancel API PO_Document_Control_PUB.control_document ?

  • The PL/SQL procedure, PO_Document_Control_PUB.control_document , provides the ability to cancel Oracle Purchasing documents directly through an API.
  • The API will perform all of the same processing that would be done if a cancellation was requested through the PO Summary Control Window.
  • Prior to calling the API we should set our global context to reflect the application, user and responsibility used to perform the cancel action. If we do not set this context, the API will not be able to identify or update your data.

–R12 – PO – Sample Script to cancel PO using po_document_control_pub API.sql

DECLARE

l_return_status VARCHAR2 (10);

CURSOR C_PO_CANCEL is

SELECT pha.po_header_id,
pha.org_id,
pha.segment1 po_number,
pha.type_lookup_code,
pha.cancel_flag,
pha.closed_code
FROM po_headers_all pha
WHERE 1=1
AND pha.segment1 = ‘376729’ — Enter The Purchase Order Number
AND nvl(pha.closed_code,’OPEN’) = ‘OPEN’
AND nvl(pha.cancel_flag, ‘N’) = ‘N’
AND approved_flag = ‘Y’;

BEGIN

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

FOR i IN c_po_cancel

LOOP

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

DBMS_OUTPUT.PUT_LINE (‘Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents’);

po_document_control_pub.control_document
(p_api_version => 1.0, — p_api_version
p_init_msg_list => fnd_api.g_true, — p_init_msg_list
p_commit => fnd_api.g_true, — p_commit
x_return_status => l_return_status, — x_return_status
p_doc_type => ‘PO’, — p_doc_type
p_doc_subtype => ‘STANDARD’, — p_doc_subtype
p_doc_id => i.po_header_id, — p_doc_id
p_doc_num => NULL, — p_doc_num
p_release_id => NULL, — p_release_id
p_release_num => NULL, — p_release_num
p_doc_line_id => NULL, — p_doc_line_id
p_doc_line_num => NULL, — p_doc_line_num
p_doc_line_loc_id => NULL, — p_doc_line_loc_id
p_doc_shipment_num => NULL, — p_doc_shipment_num
p_action => ‘CANCEL’, — p_action
p_action_date => SYSDATE, — p_action_date
p_cancel_reason => NULL, — p_cancel_reason
p_cancel_reqs_flag => ‘N’, — p_cancel_reqs_flag
p_print_flag => NULL, — p_print_flag
p_note_to_vendor => NULL, — p_note_to_vendor
p_use_gldate =>NULL ,
p_org_id => i.org_id
);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘The Return Status of the API is => ‘ l_return_status);
If l_return_status = ‘S’ Then

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order Which is Cancelled Now => ‘ i.po_number);
Else

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order =>’ i.po_number ‘Failed to cancel Due To Following Reason’);

— Get any messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
DBMS_OUTPUT.put_line (fnd_msg_pub.get
(p_msg_index => j,
p_encoded => ‘F’));
END LOOP;
END IF;
END LOOP;
END;

/by