Welcome to the lesson three of the Oracle Workflow Training.
One of the most common business requests for Oracle Workflows has been to track the progress of Workflow at its various stages.
If your business user desires to track the progress of a Workflow, they can do so by viewing the workflow process diagram. However such approach has limitations related to security of the workflow attributes. Also, that approach is not user friendly, as the workflow may contain various technical but important steps that can confuse end user.
Here is the deal, we will write a common workflow function, and re-use that workflow function at various stages of the workflow. The workflow function will be attached to a pl/sql procedure that will insert record into a progress table. User can then trace the progress either via a screen on that table or by some report.
The source code of Oracle Workflow Training Lesson 2 is being carried forward here.
Please click here if you wish to step back for Training Lesson 2 for Oracle Workflow.
This requirement will be accomplished in following steps:-
1. Create a table to track the progress.
2. Create a Workflow Function to pass parameters to indicate progress.
Note: For each parameter for Workflow function, we will create one function attribute.
3. Write the pl/sql function to insert record into progress table.
Below are steps for Oracle Workflow Training Lesson 3
Step 1. Open process “Main training processâ€, and create a new WF Function by right clicking the space between “Set Role†& “SET_VALIDATE_FLAGâ€
Step 2. Now create the function named LOG_STATUS_OF_ACTION. Assign it a function xxxx_po_wf_training_pkg.log_status_of_action
Make this function join to “Set Role†and “SET_VALIDATE_FLAGâ€
Step 3. In the Oracle Workflow Designer Navigator, as in picture below, right click on Function “Log Action Status for Progressâ€and select new attribute(as created in Step 2 above).
Step 4. Create attribute “ACTION_STAGE†( Progress Action stage )
Step 5. Create attribute “ACTION_NAME†(Progress Action to indicate status)
Step 6. Drag the Attribute PO_ID from “XX Training Workflow†and drop that into function “Log Action Status for Progressâ€
As a result of above three steps, you will see something similar to below.
Step 7. Now, navigate back to process “Main Training Processâ€. Double click on WF Function “Log Action Status for Progressâ€. Click on Tab “Node Attributeâ€
Select drop down list in section Attribute Name, select “Progress Action Stageâ€.
Effectively, here we will pass the parameters to the WF Function.
Attribute: Progress Action Stage
Type: Constant
Value: Validation
Click on Apply
Step 8.
Repeat the step 7 above for second attribute
Attribute: Progress Action to indicate status
Type: Constant
Value: Before Validation
Click On Apply
Step 9. Do nothing really, leave the PO_ID to its default value. Remember we had dragged this from WF level Attribute. Hence the function attribute will inherit value for PO_IF from Workflow Level.
Step 10. Right click between SET_VALIDATE_FLAG and “PO Notif Infoâ€, to create a new function, but instead of creating new Function, simply pick the function created in “Step 2â€. i.e. from drop down list for INTERNAL, select “LOG_STATUS_OF_ACTIONâ€
Step 11. You will see the WF Diagram
Step 12.
Now repeat steps 7, 8 & 9.
However, the difference being that instead of Step 8, this time our values will be
Attribute: Progress Action to indicate status
Type: Constant
Value: After Validation
Step 13. Create the procedure below in package “xxxx_po_wf_training_pkgâ€
PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
n_po_id INTEGER;
v_action_name VARCHAR2(200);
v_action_stage VARCHAR2(200);
BEGIN
IF funcmode = 'RUN'
THEN
--get the PO id, as it is after the hyphen of itemkey in this case
n_po_id := get_request_id_from_item_key(itemkey);
v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_STAGE');
v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_NAME');
INSERT INTO xx_po_wf_progress
(po_id
,p_wf_action_stage
,p_wf_action_name)
VALUES
(n_po_id
,v_action_stage
,v_action_name);
END IF;
RESULT := 'COMPLETE:Y';
END log_status_of_action;
Note that the above pl/sql procedure simply reads the value from parameters passed under Node Attributes. Hence there is no hardcoding in the pl/sql.
By doing the above, you will be able to track the progress of each transaction in a very simple way.
Now for testing….
Step 14.
Save the wft file in database( Source code at end of this article)
Step 15. Initiate workflow as below….
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,This email address is being protected from spambots. You need JavaScript enabled to view it.' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
You will then see two records created
The workflow progress will be visible as below
The source code for Training Lesson 3 is available here .
For any questions/comments on this training session, please leave a comment here.
Comments
Nice job !! Congratulations & Thanks for such a nice trang.
A small suggestion, why not you also start one Oracle workflow forum too.
I do have some queries like:
1. Electronic Form with workflow routing:
Just in case If we want to show some e-form (like PO application form) with process. The business rule is user should fill some mandatory fields in PO app form and than whole approval routing can be done and every performer can see/view that PO- app form ?
Will you like to teach us how to do this too
Although Training lessons are quiet good to start up. But I feel that Training Lesson -1 was best, since it was audio-visual it gives you a feeling that one to one training is going on. I guess Trainee will always be with trainer in audio-visual trang, & secnondly, you were also giving some tips in audio-visual which i guess are quiet important.
T hanks & Regards
Anoop
Nice job !! Congratulations & Thanks for such a nice trang.
A small suggestion, why not you also start one Oracle workflow forum too.
I do have some queries like:
1. Electronic Form with workflow routing:
Just in case If we want to show some e-form (like PO application form) with process. The business rule is user should fill some mandatory fields in PO app form and than whole approval routing can be done and every performer can see/view that PO- app form ?
Will you like to teach us how to do this too
Although Training lessons are quiet good to start up. But I feel that Training Lesson -1 was best, since it was audio-visual it gives you a feeling that one to one training is going on. I guess Trainee will always be with trainer in audio-visual trang, & secnondly, you were also giving some tips in audio-visual which i guess are quiet important.
T hanks & Regards
Anoop
Could you pleae also load the audio-visual model for the workflow. It easier to go through once and then close the eyes and just listen while you speak. Fantabulous work !!!
Could you pleae also load the audio-visual model for the workflow. It easier to go through once and then close the eyes and just listen while you speak. Fantabulous work !!!
very nice and informative site.
Lesson was very good.
very nice and informative site.
Lesson was very good.
how ru, i need workflow software how to dowenloade plz send link
thnks
bhaskar
The training was very useful..Thanks for your time.
i am getting the error while running the log_status_of_a ction procedure that get_request_id_ from_item_key should be declared
n_po_i d := get_request_id_ from_item_key(itemkey);
Could you send the complete script file for this session
Thanks
Tamil
That procedure is listed in link http://scripts4oracle.blogspot.com/2006/09/scripts-for-oracle-workflow-training.html
Thanks
Anil
I need to release my workflow customization work to the Production box after testing.before releasing to production box i am going to take a backup of the current version on the prod. But to release it to prod or other environments from Development which tool you normally prefer? Workflow builder or WFLOAD utility.
Thanks
Kumar
WFLOAD can be used in DOWNLOAD and UPLOAD mode.
Hence if you wish, take a backup on production prior to running UPLOAD
Thanks,
Anil Passi
I have added a new workflow function which in turn will invoke a new pl/sql proc.
This changed workflow file was saved to the database and the new pl/sql proc. has been successfully compiled. But when i execute the workflow,i'm getting a initialization error. The error says the function can't invoke the new pl/sql proc. I tried invoking an existing pl/sql proc. from the new workflow function, it worked. This clearly indicates that the new function is not able to invoke the new pl/sql proc. Can you please help me out?
Thanks in advance.
Manas
I want to launch a workflow process by clicking on the "Launch process"
link under Workflow Administrator Web Applications responsibility.
but I can't find that option under Workflow Administrator
Web Applications responsibility in Oracle Apps 11.5.10.2.
Any Idea?
Thanks for your help
Cheers
Kum ar
I have an assisgnment for whcih i require start to end flow chart for Order to Cash and Procure to Pay. I searched in Google & Metalink but,i didn't get the exact result.
Thanks in advance,
Ramesh
I am customizing the Project status workflow(PA:PRO JECT WORKFLOW) in Oracle Projects Module. Instead of that workflow we have customized a worfkflow to send notification to all
stake holders of the project. i am facing the issue "while changin the status of the project the custom workflow is triggering and sending notifications to the members of the proj but even after the workflow is complted the status change buttton is not getting enabled (workflow in Progress checked )but in status monitor worlfow is completed. could u please guide me how to proceed with this issue. Give me some idea to handle this issue by ur extensive training
Regar ds,
Subburaj G.
Is there any documentation on the types of errors faced at the end-users level for PO, INV, OM MODULES .
Thanks
Gauth am
This is some excellent work you are doing and I dont know if you have any idea as how much this kind of training lessons are helpful. Thanks and thanks a lot.
How to get the workflow file from database, I tried lot of times to open the existing workflow file. i followed this navigation,
opened the workflow builder-->conne ct to database
here iam strucking. The workflow builder is halted.
so give me some tips to customize the existing workflow file..
Thanks& Regards,
Giripr asad
Can you also start technical trainig on Configurator if possible.
Thank you once again.
KK
I get the following error when I tried to create a custom notification message and add a From Type attribute to the message.
ORA-0 6510: PL/SQL: unhandled user-defined exception ORA-06512: at "APPS.OE_ORDER_ WF_UTIL", line 249 ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-01403: no data found ORA-06512: at "APPS.WF_NOTIFI CATION", line 5382 ORA-06512: at line 5
Please respond back with your insights on this.
I got following error, when i trigger my worflow package throgh database trigger.
Error is : ORA-06502: PL/SQL: numeric or value error: character to number conversion error
I am not getting any error , if i am processing workflow package through TOAD.
Please respond back, it is very urgent.
Regard s
ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "APPS.OE_ORDER_ WF_UTIL", line 249 ORA-01403: no data found ORA-06512: at "APPS.WF_NOTIFI CATION", line 5474 ORA-06512: at line 5
But i have a question to ask you
if i want to create an action upon approval required another approval from another user, how can i make such thing ?
Thank you in advanced
RSS feed for comments to this post