iProcurement

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;
/

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel