Scripts for iProcurement - XML Delivery load testing
Written by Anil Passi   
Sunday, 04 February 2007
In this article, we will walk through the scripts that will generate enough Purchase order to simulate thousands of concurrent users in Oracle iProcurement/Purchasing. Please read article in this link to understand the background for these scripts.

The steps in brief are:-
1. Create a temp table wherein you will capture the id's of new purchase orders being created
2. Create an API that will clone an existing Purchase Order. The PO_HEADER_ID of master Purchase Order[that which will be cloned], can be passed in as Parameter to the API.
3. Call the cloning API as many times in a loop
4. Submit each of the newly created purchase orders for Approval. The Approval workflow will then invoke a business event to create and transmit XML Purchase Order Documents to the supplier.


Fine, where are the scripts?
Script 1 Create a table to hold the new Purchase Orders that we will create
create table xx_po_load_new
(
  to_segment1      varchar2(500)
 ,to_po_header_id  varchar2(500)
 ,online_report_id varchar2(500)
 ,return_code      varchar2(500)
 ,processed_flag VARCHAR2(1) 
 ,xml_sent_flag VARCHAR2(1)
) ;


Script 2 Create procedure to create purchase orders
CREATE OR REPLACE PROCEDURE xx_po_create_load
(
 p_main_po_header_id INTEGER DEFAULT 981091 /*For ITUPDV2 981733*/
,p_agent_id INTEGER DEFAULT 222 /*253*/
,p_sob_id INTEGER DEFAULT 1
,p_invorg_id INTEGER DEFAULT 101
)
IS
  x_to_segment1      VARCHAR2(1000);
  x_to_po_header_id  VARCHAR2(1000);
  x_online_report_id VARCHAR2(1000);
  x_return_code      VARCHAR2(1000);
BEGIN
  x_to_segment1      := NULL;
  x_to_po_header_id  := NULL;
  x_online_report_id := NULL;
  x_return_code      := NULL;
  po_copydoc_s1.copy_document(x_action_code        => 'PO'
                             ,x_to_doc_subtype     => 'STANDARD'
                             ,x_to_global_flag     => 'N'
                             ,x_copy_attachments   => FALSE
                             ,x_copy_price         => TRUE
                             ,x_from_po_header_id  => p_main_po_header_id
                             ,x_to_po_header_id    => x_to_po_header_id
                             ,x_online_report_id   => x_online_report_id
                             ,x_to_segment1        => x_to_segment1
                             ,x_agent_id           => p_agent_id
                             ,x_sob_id             => p_sob_id
                             ,x_inv_org_id         => p_invorg_id
                             ,x_wip_install_status => 'S'
                             ,x_return_code        => x_return_code
                             ,x_copy_terms         => 'N');
  INSERT INTO xx_po_load_new
    (to_segment1
     ,to_po_header_id
    ,online_report_id
    ,return_code
    )
  VALUES
    (x_to_segment1 --to_segment1
    ,x_to_po_header_id --to_po_header_id
    ,x_online_report_id --online_report_id
    ,x_return_code --return_code
     );
     COMMIT ;
END;
/


Script 3 Call the procedure xx_po_create_load to create 200 purchase orders in a loop, as below
DECLARE
BEGIN
  FOR i IN 1 .. 200
  LOOP
    --Note 981091 is the PO Header Id of the Purchase Order which which will be cloned 100s of times
    --You can consider passing this as a parameter
    xx_po_create_load(p_main_po_header_id => 981091

                     ,p_agent_id          => 222);
  END LOOP;
  COMMIT ;
END;
/



Script 4 Submit those purchase orders for Approval and XML transmission. This is done by looping through the data in table xx_po_load_new. The data was populated using Script 3
Run this as a concurrent program, after logging in as the person that can self-approve the Purchase Order for given amount/currency & GL Codes.
CREATE OR REPLACE PROCEDURE xx_run_reserve_approve(errbuff OUT VARCHAR2
                                                  ,retcode OUT VARCHAR2) IS
  n_agent_id  INTEGER := 222;
  b           BOOLEAN := FALSE;
  v_item_key  VARCHAR2(50);
  v_po_number VARCHAR2(250);
BEGIN
 
  --UPDATE fnd_user SET employee_id = n_agent_id WHERE user_name = 'PASSIA';
  --COMMIT ;
  FOR p_rec IN (SELECT *
                FROM xx_po_load_new
                WHERE processed_flag IS NULL
                and rownum < 1601
                /*AND to_po_header_id = 981099*/
                )
  LOOP
    UPDATE po_headers_all
    SET xml_flag = 'Y'
    WHERE po_header_id = p_rec.to_po_header_id;
    COMMIT;
    SELECT p_rec.to_po_header_id || '-' || to_char(po_wf_itemkey_s.NEXTVAL)
    INTO v_item_key
    FROM sys.dual;
    SELECT segment1
    INTO v_po_number
    FROM po_headers_all
    WHERE po_header_id = p_rec.to_po_header_id;
    apassi1(' Calling po_reqapproval_init1.start_wf_process for po_id=>' ||
            p_rec.to_po_header_id);
    po_reqapproval_init1.start_wf_process(itemtype              => 'POAPPRV'
                                         ,itemkey               => v_item_key
                                         ,workflowprocess       => 'POAPPRV_TOP'
                                         ,actionoriginatedfrom  => 'PO_FORM'
                                         ,documentid            => p_rec.to_po_header_id
                                         ,documentnumber        => v_po_number
                                         ,preparerid            => n_agent_id
                                         ,documenttypecode      => 'PO'
                                         ,documentsubtype       => 'STANDARD'
                                         ,submitteraction       => 'APPROVE'
                                         ,forwardtoid           => NULL
                                         ,forwardfromid         => NULL
                                         ,defaultapprovalpathid => NULL
                                         ,note                  => NULL
                                         ,printflag             => 'N');
    UPDATE xx_po_load_new
    SET processed_flag = 'Y'
    WHERE to_po_header_id = p_rec.to_po_header_id;
    commit ;
  END LOOP;
  COMMIT;
END;
/


Script 5 Monitor the progress of load test.
DECLARE
  n_ctr INTEGER;
BEGIN
  UPDATE xx_po_load_new
  SET xml_sent_flag = 'Y'
  WHERE xml_sent_flag IS NULL
  AND processed_flag = 'Y'
  AND EXISTS (SELECT 'x'
         FROM po_headers_all ph
         WHERE ph.po_header_id = to_po_header_id
         AND xml_send_date IS NOT NULL);
  COMMIT;
  SELECT COUNT(*) INTO n_ctr FROM xx_po_load_new WHERE xml_sent_flag = 'Y';
  dbms_output.put_line('Number processed as yet are ' || n_ctr);
END;
/




Comments (5)add
...
written by Shank , August 20, 2007
Hi,

Can you please tell me how to send a PO by email to the suppliers.
What setup steps I need to do and on the technical side how should I write the shell script to achieve that.
Thanka a lot in advance
...
written by kishore P , September 18, 2007
Hi Anil,
Pretty good one on iProcurement.
Anil, i was looking for any doc in relation to 'Encumbrance Accounting' in your work. As lot had been said about the same in the metalink, but it complicates. Pls let me know if you have some basic doc on this.

thanks
Kishore
...
written by kishore P , September 18, 2007
Hi Anil,
Pretty good one on iProcurement.
Anil, i was looking for any doc in relation to 'Encumbrance Accounting' in your work. As lot had been said about the same in the metalink, but it complicates. Pls let me know if you have some basic doc on this.

thanks
Kishore
Documentation for Personalizing the Self service web pages
written by Sachin Tayade , December 05, 2007
Hi Anil Sir,

Can you please tell me,is there any documentation available for Personalizing the Self service web pages.
Actually i am a beginner for this, i know form peronalization little bit, i can do little peronalization there , not very complex, but i not getting any thing in self service web pages.

If you have any documentation for this, it will be higly appreaciated.

Thank You.
Sachin Tayade.
iReceiveables - Auditing for 1K License
written by Geoff , December 21, 2007
Hi,

I'm new to a site and I'm concerned that they are exceeding the 1K license metric for iReceiveables, is there a script I can run to determine the number of line items to count towards the 1k license metric?

Regards

Geoff.
You must be logged in to a comment. Please register if you do not have an account yet.

busy