, , , , , , ,

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

API for Closing/Finally Closing PO Using po_actions.close_po

Do we have any API for Finally Closing PO??
Yes, we are having an API for closing or finally closing the POs. In the API, there is a parameter “p_action” which we need to set as either CLOSE (if we want to close the PO) or FINALLY CLOSE (If we want to Finally Close) the PO. Another Parameter which needs to set properly is “p_auto_close”. This parameter should be set to ‘N’.
— R12 – PO – Script to Close / Finally Close PO using PO_ACTIONS CLOSE_PO API.sql

DECLARE

x_action constant varchar2(20) := ‘FINALLY CLOSE’; — Change this parameter as per requirement
x_calling_mode constant varchar2(2) := ‘PO’;
x_conc_flag constant varchar2(1) := ‘N’;
x_return_code_h varchar2(100);
x_auto_close constant varchar2(1) := ‘N’;
x_origin_doc_id number;
x_returned boolean;

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.closed_code,
pha.closed_date
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 = ‘APPROVED’
AND pha.closed_code <> ‘FINALLY CLOSED’
AND segment1 = ‘379329’; — Enter the PO Number if one PO needs to be finally closed/Closed

begin

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

for po_head in c_po_details

LOOP

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

DBMS_OUTPUT.PUT_LINE (‘Calling PO_Actions.close_po for Closing/Finally Closing PO =>’ po_head.segment1);

x_returned :=
po_actions.close_po(
p_docid => po_head.po_header_id,
p_doctyp => po_head.document_type_code,
p_docsubtyp => po_head.document_subtype,
p_lineid => NULL,
p_shipid => NULL,
p_action => x_action,
p_reason => NULL,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL);

IF x_returned = TRUE THEN

DBMS_OUTPUT.PUT_LINE (‘Purchase Order which just got Closed/Finally Closed is ‘ po_head.segment1);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE (‘API Failed to Close/Finally Close the Purchase Order’);

END IF;

END LOOP;

END;

/by