Oracle Workflows

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”

Create new WF function for progress tracking

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.

Screenshot of Oracle Workflow after having created 3 Function Attributes

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

Visual representation of Workflow Progress

The source code for Training Lesson 3 is available here .

For any questions/comments on this training session, please leave a comment here.

Anil Passi

Comments   

0 #1 Anil Passi 2006-11-15 00:00

thanks andeo

Quote

0 #2 Anil Passi 2006-11-15 00:00

thanks andeo

Quote

0 #3 Anoop 2006-12-20 00:00

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

Quote

0 #4 Anoop 2006-12-20 00:00

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

Quote

0 #5 Santosh Desai 2006-12-30 00:00

Anil,

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

Quote

0 #6 Santosh Desai 2006-12-30 00:00

Anil,

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

Quote

0 #7 Alla Kishore 2007-04-16 00:00

Thanks Anil,

very nice and informative site.
Lesson was very good.

Quote

0 #8 Alla Kishore 2007-04-16 00:00

Thanks Anil,

very nice and informative site.
Lesson was very good.

Quote

0 #9 bhaskar 2007-06-25 00:00

hi anil ,
how ru, i need workflow software how to dowenloade plz send link

thnks
bhaskar

Quote

0 #10 Tamil Selvan K 2007-07-04 13:45

Hi Anil
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

Quote

0 #11 Anil Passi 2007-07-04 13:50

Hi Tamil

That procedure is listed in link http://scripts4oracle.blogspot.com/2006/09/scripts-for-oracle-workflow-training.html

Thanks
Anil

Quote

0 #12 Tamil Selvan K 2007-07-04 13:51

Please ignore my previous message..i got the link

Quote

0 #13 AK 2007-07-15 04:22

Hi 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

Quote

0 #14 Anil Passi 2007-07-15 07:22

Yes please, use WFLOAD.

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

Quote

0 #15 Manas Rath 2007-08-17 11:55

Hi Anil:

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

Quote

0 #16 AK 2007-08-24 03:04

Hi Anil,

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

Quote

0 #17 MG Ramesh 2007-09-28 12:28

Hi Anil,

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

Quote

0 #18 Subburaj Gurusamy 2007-10-01 17:20

Hi Anil,
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.

Quote

0 #19 Gautham 2007-10-16 06:48

Dear Anil,

Is there any documentation on the types of errors faced at the end-users level for PO, INV, OM MODULES .


Thanks
Gauth am

Quote

0 #20 Sree 2007-11-14 21:12

Excellent Anil!!!

Quote

0 #21 Nilesh K 2007-12-20 15:45

Anil,

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.

Quote

0 #22 srinu reddy 2008-01-10 05:12

Great job Anil. appreciate your work

Quote

0 #23 giriprasad 2008-01-17 09:35

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

Quote

0 #24 Akhil 2008-02-26 05:05

Hello Mr. Anil, your work in OAFramework and Workflow is really amazing.
Can you also start technical trainig on Configurator if possible.
Thank you once again.

KK

Quote

0 #25 Dittu 2008-06-27 11:07

Hi Anil,

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.

Quote

0 #26 Gopal Soni 2010-10-26 03:57

Hi Anil,

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

Quote

0 #27 Alexander Gomez 2011-01-20 15:34

I get the following error when I tried to create a custom notification message and add a From Type attribute to the message.
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

Quote

0 #28 Firas 2011-12-15 09:56

Thank you very much for this useful tutorial.
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

Quote

Refresh comments list
RSS feed for comments to this post

Add comment