Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

iProcurement
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 
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;
/





Anil Passi

Comments   

0 #1 Shank 2007-08-20 09:31
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
Quote
0 #2 kishore P 2007-09-18 12:25
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
K ishore
Quote
0 #3 kishore P 2007-09-18 13:04
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
K ishore
Quote
0 #4 Sachin Tayade 2007-12-05 08:34
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.
Quote
0 #5 Geoff 2007-12-21 15:27
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?

Regard s

Geoff.
Quote
0 #6 tKrishnaKishore 2008-10-23 15:26
Hi Anil,

I need to develop a script that is very similar to the one you discussed above. The only difference is, my script needs to copy requisitions (instead of PO's) and the requisition approval WF needs to be started for the requisitions created. Could you please provide me the API to copy requisitions and let me know the package to start the Requisition approval WF.

Thanks,
Kr ishna
Quote
0 #7 Nikhil Bidwaikar 2009-01-05 04:39
Hi all,

can anyone let me know ,how to trace invoices that were ordered through I procurment. It would be greatfull
if sm one can provide sql for it.

thanks
nikhil
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner