Workflow Basics

Overview:

This article will illustrate how to create or define workflow attributes, notifications, messages, roles or users, functions, processes and last but not the least, how to launch a workflow from PL/SQL. The workflow concepts are better explained using an example.

Business Requirement:

When an item is created in inventory, workflow needs to be launched and it should collect the details of the item created and sends a notification to group of users along with the details and link to master item form

Process flow: When an item is created it will create/insert a record in MTL_SYSTEM_ITEMS_B so create a database trigger on the table and launch workflow from that trigger. All you need to do is create the workflow, create the trigger, pl/sql package, roles and finally create an item in inventory.

· Open WFSTD and save as new workflow

· Create Attributes

· Create Functions

· Create Notification

· Create Messages

· Create Roles

· Create database trigger

· Create PL/SQL Package

1)Open WFSTD and save as new workflow:

 

Navigation: File >> Open

 

Click Browse then navigate to Workflow installation directory

Navigation: Workflow Installation Directory\ WF\DATA\US\WFSTD

Now Click File >Save as, Enter “ErpSchools Demo” and click OK

Right click on WFSTD and select New Item type

Enter the fields as below

Internal Name: ERP_DEMO

Display Name: ErpSchools Demo

Description: ErpSchools Demo



Now you will see ErpSchools Demo icon in the Navigator

 

Expand the node to see attributes, processes, notifications, functions, Events, Messages and lookup types.

Double click on Process to open up the properties window as shown below

Enter the fields

Internal Name: ERPSCHOOLS_PROCESS

Display Name: ErpSchools Process

Description: ErpSchools Process

 



Double click ErpSchools Process Icon

2) Create Workflow Attributes:

Navigation: Window menu > Navigator

Right click on Attributes and click New Attribute

Enter the fields

Internal Name: ERP_ITEM_NUMBER

Display Name: Item Number

Description: Item Number

Type: Text

 Default Value: Value Not Assigned

 

Click Apply and then OK

Create one more attribute

Right click on Attributes and click New Attribute

Enter the attribute fields

Internal Name: ERP_SEND_ITEM_FORM_LINK

Display Name: Send Item Form Link

Description: Send Item Form Link

Type: Form

Value: INVIDITM

 

Click Apply and then OK

3) Create Workflow Function:

Right click and then click on New Function

Properties window will open as shown below

Change/Enter the fields as below

Change Item Type to Standard from ErpSchools Demo

Select Internal Name as Start

Remaining fields will be populated automatically

 

Click Apply then OK

Again Right click on white space and click New Function 

Change the properties as below

Item Type: Standard

Internal Name: END

Click Apply and then OK

Right click on white space and then click New Function

Enter the fields

Internal Name: ERP_GET_DETAILS

Display Name: Get New Inventory Item Details

Description: Get New Inventory Item Details

Function Name: erpschools_demo_pkg.get_item_details

Click Apply and then OK

4) Create Workflow Notifications:

Right click on white space and then click New Notification

Enter fields

Internal Name: ERP_SEND_ITEM_DET

Display  Name: Send Item Detials

Description: Send Item Detials

Message: Sned Item Details Message

Click Apply and then OK

5) Create Workflow Messages:

Right click on Message and click New



Properties window will pop up as show below

Enter the fields

Internal Name: ERP_SEND_ITEM_DET_MSG

Display Name: Send Item Details Message

Description: Send Item Details Message

Go to Body Tab and enter as shown below


Click Apply and then OK

Navigation: Window Menu > Navigator

Select Item Form Link Attribute

Drag and drop both attributes to “Send Item Details Message”

6)Create Roles:

Adhoc roles can be created through PL/SQL from database or they can be created from Applications using User Management Responsibility. If you use PL/SQL to create roles make sure you give all user names and role names in UPPER case to avoid some problems

· Script to Create a Adhoc Role

· Script to Add user to existing Adhoc Role

· Script to Remove user from existing Adhoc Role

· Using Adhoc roles in workflow notifications

· Adhoc Roles Tables

Script to Create a Adhoc Role

DECLARE

lv_role varchar2(100) := ‘ERPSCHOOLS_DEMO_ROLE’;

lv_role_desc varchar2(100) := ‘ ERPSCHOOLS_DEMO_ROLE’;

BEGIN

wf_directory.CreateAdHocRole(lv_role,

lv_role_desc,

NULL,

NULL,

‘Role Demo for erpschool users’,

‘MAILHTML’,

‘NAME1 NAME2′, –USER NAME SHOULD BE IN CAPS

NULL,

NULL,

‘ACTIVE’,

NULL);

dbms_output.put_line(‘Created Role’ ||’ ‘||lv_role);

End;

/

Script to Add user to already existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := ‘ERPSCHOOLS_DEMO_ROLE’;

v_user_name := ‘NAME3′;

WF_DIRECTORY.AddUsersToAdHocRole(v_role_name, v_user_name);

–USER NAMES SHOULD BE in CAPS

END;

Script to Remove user from existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := ‘ERPSCHOOLS_DEMO_ROLE’;

v_user_name := ‘NAME3′;

WF_DIRECTORY.RemoveUsersFromAdHocRole(v_role_name, v_user_name); –USER NAMES in CAPS

END;

Using Adhoc roles in workflow notifications:

Navigation: File > Load Roles from Database

Select roles you want to use and then click OK.

Open the notification properties and then navigate to node tab, select performer as the role you just created and loaded from database.

Tables:

· WF_ROLES

· WF_USER_ROLES

· WF_LOCAL_ROLES

· WF_USER_ROLE_ASSIGNMENTS

7) Launching workflow from PL/SQL:

First create a database trigger as below to call a PL/SQL procedure from which you kick off the workflow.

·         Create Database Trigger

CREATE OR REPLACE TRIGGER “ERP_SCHOOLS_DEMO_TRIGGER” AFTER INSERT ON INV.MTL_SYSTEM_ITEMS_B REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

DECLARE

lv_id                 NUMBER          := :NEW.inventory_item_id;

lv_item_segment1      VARCHAR2(100)   := :NEW.segment1;

lv_itemtype           VARCHAR2(80)    := :NEW.item_type;

lv_user_id            NUMBER          := -1;

lv_itemkey            VARCHAR2(10);

lv_orgid              NUMBER          :=2;

error_msg             VARCHAR2(2000);

error_code            NUMBER;

BEGIN

lv_user_id := fnd_global.user_id;

lv_orgid := fnd_global.org_id;

lv_itemkey := 1132; – This should be unique value

ERP_DEMO.LAUNCH_WORKFLOW(‘ERP_DEMO’

,lv_itemkey

,’ERPSCHOOLS_PROCESS’ –process name

,lv_id

,lv_orgid

,lv_item_segment1

);

EXCEPTION

WHEN OTHERS THEN

error_code := SQLCODE;

error_msg  := SQLERRM(SQLCODE);

RAISE_APPLICATION_ERROR(-20150,error_msg);

END;

/

·         Create PL/SQL Package to kickoff workflow

CREATE OR REPLACE PACKAGE APPS.ERP_DEMO IS

PROCEDURE LAUNCH_WORKFLOW

(

itemtype        IN VARCHAR2,

itemkey         IN VARCHAR2,

process         IN VARCHAR2,

item_id         IN NUMBER,

org_id          IN NUMBER,

item_segment1   IN VARCHAR2

);

END ERP_DEMO;

/

CREATE OR REPLACE PACKAGE BODY APPS.ERP_DEMO IS

PROCEDURE LAUNCH_WORKFLOW(

itemtype         IN VARCHAR2,

itemkey          IN VARCHAR2,

process          IN VARCHAR2,

item_id          IN NUMBER,

org_id           IN NUMBER,

item_segment1    IN VARCHAR2

)

IS

v_master_form_link varchar2(5000);

v_item_number varchar2(100);

error_code varchar2(100);

error_msg varchar2(5000);

BEGIN

v_add_item_id := ‘ ITEM_ID=”‘ || item_id || ‘”‘;

v_item_number := item_segment1;

v_master_form_link := v_master_form_link || v_add_item_id;

WF_ENGINE.Threshold := -1;

WF_ENGINE.CREATEPROCESS(itemtype, itemkey, process);

– Get the value of attribute assigned in workflow

v_master_form_link := wf_engine.getitemattrtext(

itemtype => itemtype

,itemkey  => itemkey

,aname    => ‘ERP_SEND_ITEM_FORM_LINK’);

- assign values to variables so that you can usethe attributes

v_master_form_link varchar2(5000) := v_master_form_link||’:#RESP_KEY=”INVENTORY” #APP_SHORT_NAME=”INV” ORG_MODE=”Y” ‘;

v_master_form_link := v_master_form_link || v_add_item_id;

–set the attribute values in workflow so that you can use them in notifications

WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘MASTERFORM’, v_master_form_link);

WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘ERP_ITEM_NUMBER’, item_segment1);

– start the workflow process

WF_ENGINE.STARTPROCESS(itemtype, itemkey);

EXCEPTION WHEN OTHERS THEN

error_code := SQLCODE;

error_msg  := SQLERRM(SQLCODE);

– add dbms or fnd_output messages as required

END LAUNCH_WORKFLOW;

– This procedure will just put the item number into workflow attribute ERP_ITEM_NUMBER

PROCEDURE GET_ITEM_DETAILS(

itemtype        IN VARCHAR2,

itemkey         IN VARCHAR2,

actid           IN NUMBER,

funcmode        IN VARCHAR2,

resultout       OUT NOCOPY VARCHAR2

)

IS

v_GET_ITEM_NUMBER VARCHAR2(1000);

BEGIN

SELECT SEGMENT1 INTO V_GET_ITEM_NUMBER FROM MTL_SYSTEM_ITEMS_B WHERE ROWNUM =1;

WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘ERP_ITEM_NUMBER’,v_GET_ITEM_NUMBER );

– you can use the get function as below.

–v_GET_ITEM_NUMBER := wf_engine.getitemattrtext(

–                                        itemtype => itemtype

–                                        ,itemkey  => itemkey

–                                        ,aname    => ‘X_ATTRIBUTE’);

resultout:=’COMPLETE:’||’Y';

exception when others then

dbms_output.put_line(‘Entered Exception’);

fnd_file.put_line(fnd_file.log,’Entered Exception’);

END GET_ITEM_DETAILS;

END ERP_DEMO;

/

 

Working with Discoverer Desktop - Part04

7. Building Worksheets and Workbooks

Your company's database administrator creates worksheets and workbooks based on your requirements for specific data analysis and reporting. However, while working with Discoverer Desktop, you may want to create additional worksheets and workbooks.

For example, you may want to create a special worksheet as a scheduled report that gets printed each month as part of your business reporting requirements. Or, you may want to consolidate project-specific information in a separate workbook that you share with other team members.

If you have the appropriate access rights (usually granted by the database administrator), you can use the steps explained in this chapter to create worksheets and workbooks.

Note: You use the same basic process to build workbooks and worksheets. In fact, to build a new workbook, you create the initial worksheet for the new workbook. Thus, the steps described in this section are for both processes—building a new workbook and building a new worksheet.

Topics in this chapter include:

• "Building a New Workbook or Worksheet"
• "Editing a Worksheet"

7.1 Building a New Workbook or Worksheet

The basic steps to create a new workbook or worksheet are as follows:

Required Steps

• Select the type of display for the new worksheet or workbook.
• Select the data that belongs on the worksheet or in the workbook.

Optional Steps

• Arrange the data on a table or crosstab layout.
• Sort the data (for tables only).
• Select conditions to apply to the data.
• Select calculations to apply to the data.

To start the process to build a new workbook or worksheet, choose either of the following:

1. Choose Sheet | New Sheet to build a new worksheet.
2. Choose File | New to create a new workbook.

The New Sheet dialog box appears (Figure 7-1).

Note: The sample dialog boxes in the rest of this section are for creating a new worksheet. The dialog boxes for creating a new workbook are the same, except the dialog box titles are ÒWorkbook WizardÓ instead of ÒNew Sheet.Ó

7.1.1 Selecting Display Type

1. Click the icon for the type of display for the new worksheet.
2. Click Next.

Figure 7-1 Workbook Wizard Dialog Box

 

Description of "Figure 7-1 Workbook Wizard Dialog Box" 

The next dialog box is for selecting the data that you want on the new worksheet.

7.1.2 Selecting the Data

This dialog box lists the data in the business area that you can use to build the new worksheet. To include data on the new worksheet, you move it from the Available column to the Selected column.

Figure 7-2 Step 2: Select the Data

 

Description of "Figure 7-2 Step 2: Select the Data" 

The icons in the list are:

Business area - the file cabinet icon displays a business area created by the database administrator; to select another business area for the new worksheet, click the drop-down arrow and choose from the list of business areas.

Find button - click the torch icon to find a specific item or folder by name in the current business area, or all business areas.

Folders - the folder icon holds the items that you can select for your worksheets.

To add data to a new worksheet:

1.    Select the business area from the drop-down menu at the top of the Available list.

2.    Click the plus (+) sign next to a folder to see all of the items in it.

Folders containing items available for the current worksheet are active. Others are grayed out.

The items may have plus signs next to them too indicating you can select values for those items as well. For example, the City item contains the names of the cities in the database. You can select a specific city to add to the worksheet.

The following figure shows that the Video Analysis folder is expanded to show its items (such as Department and Region), and the Region item is expanded to show the list of values that correspond to Region (Central, East, West) and the ways to aggregate the data (Count, Max, Min).

1. 
   

   Figure 7-3 See Available Items

    
   Description of "Figure 7-3 See Available Items" 

1. Key to Figure 7-3:
   a. Click a + sign to expand the item and see its values.
   b. drop-down button.
   c. Browse button. 

The icons in the expanded list are:

Axis Item - for example Region, corresponds to a column on a table or a level on a crosstab; axis items remain constant and have relatively few values, such as the names of Departments in your company, or the names of your Sales Regions. The values of an axis item are shown as a list of values.

Axis Item Value - one of the values of an axis item.

Numeric Item - for example Profit, represents numeric data; corresponds to the data in the body of a crosstab. The values of numeric items can change as you analyze the data, such as summing profits for cities compared to regions. Numeric items also behave as Axis items on tables.

Aggregations - for example SUM, the mathematical functions to aggregate the data; for text items such as Region, the typical aggregations are Count, Max, and Min. That is, you can count the number of text items, or find the highest or lowest (where A might be the highest and Z the lowest).

For numeric data, the typical aggregations are Sum, Count, Max, Min, Average, and Detail. For example, you can find the Sum or Average of the numeric data with the aggregation. The aggregation in boldface type is the default. The database administrator sets which aggregation is the default.

Condition - a filter for finding specific data. The database administrator defines conditions for the folder, not for individual items.

Clicking the Options button displays a dialog box for selecting options for the new worksheet or workbook. See Section 8.5, "Setting Options" for details.

Add specific data to your worksheet as follows:

1. Select the item(s) to add to the new worksheet. Shift-click on items to add multiple items.
   
   

   Figure 7-4 Selecting Items for your Worksheet

    
   Description of "Figure 7-4 Selecting Items for your Worksheet" 
   
   Key to Figure 7-4: 
   a. Click an Item to select the Item. 
   b. The Add button. 
   c. The Selected list, containing Items that will be displayed on your Worksheet. 
2. The Add button becomes active.
3. Click the Add button to add the item(s) to the new worksheet. You can also drag the selected items from the Available list to the Selected box, (see Figure 7-4).
   The Selected list shows the data for the new worksheet:
   You can select data at various levels in the Available list. For example, selecting a folder and moving it to the Selected list, moves all the data within the folder to the Selected list. Similarly, adding an item adds all the values in it to the worksheet. For example, adding the City item adds all the names of the cities.
   Adding a numeric item automatically adds its default aggregation functions. All values are automatically added too. Adding an axis item does not automatically add aggregate functions.
   To remove an item from the Selected list, click it and drag it back to the Available list, or click the Remove button (under the Add button).
4. At this point, you can click Finish to create a new worksheet or workbook. However, clicking Next shows the next (optional) dialog box for adding other features to the new worksheet or workbook.
   Note: to change selections on the previous dialog boxes, click the Back button.
5. Click Next to see the dialog box for step 3 of the process.
   
   
   

   Note: If you add an Aggregated Data Point and a Detail Data Point to the same Worksheet, Discoverer Desktop displays a warning that you may get unexpected results. Click OK to continue.

   
   

7.1.3 Arranging the Layout

In this step, you move the items on the worksheet to display them in the proper columns, rows, and page axes. It is the same basic process as on the worksheet itself; that is, drag the item to a new position on the worksheet. See Section 4.1, "Pivoting Data" for a description of moving items to the page axis on a worksheet.

The layout that appears on the dialog box depends on the selection—table or crosstab—in step 1 of the new worksheet/new workbook process. The following figure shows a tabular layout.

Figure 7-5 Step 3: Arrange the Layout

 
Description of "Figure 7-5 Step 3: Arrange the Layout" 

The columns on the table correspond to the items selected for the new sheet.

Show Page Items—If an item is selected as a Page Item (as Department is in the example above), this option is not available. If no item is selected as a Page Item, the option becomes available. Removing the checkmark hides the Page Item section of the layout. Click the option again to see the Page Item section.

Hide Duplicate Rows—Select this option to hide duplicate rows of data.

To rearrange the data on the table:

1. Select the item to move.
2. Drag it to its new position. The following figure shows moving Year to the Page Axis.
   
   

   Figure 7-6 Creating Page Items

    
   Description of "Figure 7-6 Creating Page Items" 
   
   Key to Figure 7-6: 
   a. Drag an item to the Page Items area to create a Page Item. The Show Page Items check box must be selected first. You can also move Items to the left and right to change the display layout. 
   Rearranging items on this layout does not preclude you from rearranging them directly on the worksheet.
3. Click Next or click Finish if you're finished rearranging the data and creating the new worksheet or workbook.

7.1.4 Selecting Conditions for the Data

Conditions defined for the items added to the Selected list appear in the next dialog box. You can select the conditions as part of the new worksheet or workbook, or create new conditions.

Figure 7-7 Step 4: Select Data Conditions

 
Description of "Figure 7-7 Step 4: Select Data Conditions" 

1. Click the boxes in front of the conditions that you want to add to the new worksheet or workbook.
   In the figure above, the first two conditions are selected.
   • To create a new condition, click the New button.
   • To edit a condition, select it in the list and click the Edit button.
   See Section 5.5, "Finding Data that Meets Conditions" for a description of creating and editing conditions. See Section 8.4, "Creating Advanced Conditions" for a description of creating advanced conditions.
2. Click Next or click Finish if you're finished selecting conditions for the data items and creating the new worksheet or workbook.

7.1.5 Sorting the Data in a New Worksheet or Workbook

This step only applies to tables. If you are creating a new worksheet or workbook as a crosstab, the sorting dialog box does not appear.

Figure 7-8 Step 5: Sort the Data

 
Description of "Figure 7-8 Step 5: Sort the Data" 

To sort the data in the new worksheet or workbook:

1. Click the Add button.
   The items on the new table appear in the drop-down list.
   
   

   Figure 7-9 Select Sort Item

    
   Description of "Figure 7-9 Select Sort Item" 
2. Select an item on which you want to sort the table from the pull-down list. The selected is added to the dialog box.
   
   

   Figure 7-10 Sort the Data

    
   Description of "Figure 7-10 Sort the Data" 
3. Set the Direction, Group, Line, and Spaces options as required. See Section 4.3.1, "Sorting Data on Tables" for a complete description of the sorting options.
4. When you have defined your Sort, click Next to move to the next step in the Wizard, or click Finish to create your Workbook with the configuration that you have defined so far.

7.1.6 Selecting Calculations for the Data

Calculations defined for the items added to the Selected list appear in the final dialog box. You can select the calculations as part of the new worksheet or workbook, or create new calculations.

Figure 7-11 Step 6: Select Calculations

 
Description of "Figure 7-11 Step 6: Select Calculations" 

To select calculations for the data:

1. Click the boxes in front of the conditions for the new worksheet or workbook.
   In the figure above, all the calculations are selected.
   • To create a new calculation click the New button.
   • To edit a calculation, select it in the list and click the Edit button.
   See Section 8.3, "Creating Calculations" for a complete description of creating and editing calculations.
2. Click Finish. The new worksheet or workbook appears on the screen.

7.2 Editing a Worksheet

The dialog boxes for editing a worksheet offer the same selections and features that you use to create a worksheet or workbook.

To edit a worksheet:

1. Display the worksheet that you want to edit.
2. Click the Edit Sheet icon on the toolbar, or choose Sheet | Edit Sheet.
   The Edit Sheet dialog box appears.

The tabs across the top of the dialog box are for editing the various features of the worksheet. Clicking on a tab displays the options for the feature. When the Select Items tab is selected, the items shown in the Selected list are the items currently in use on the worksheet. Items not grayed out in the Available list can be added to the worksheet.

Figure 7-12 Edit Sheet Items

 
Description of "Figure 7-12 Edit Sheet Items" 

The figure above is for editing a tabular worksheet. A similar dialog box appears for crosstab worksheets, except Table Layout tab becomes Crosstab Layout and the dialog box does not include the Sort tab. To sort crosstab data, choose Tools | Sort.

7.2.1 Adding and Deleting Items on a Worksheet

The first tab on the Edit Sheet dialog box is for adding or deleting items on a worksheet. For example, if the original item on the worksheet is Region, but does not include City names, you can add a column for the cities within the regions.

Note: Adding a new item to a worksheet adds a column to the table or crosstab, but you can see the same item as part of drilling into data. For example, if you included Cities as part of the original worksheet, drilling down into the Region data displays the cities under the regions.

To add an item to the current worksheet:

1. Click the plus (+) sign next to folders and items to see their contents.
2. Select the item in the Available list.
3. Click the Add button or drag the item to the Selected list.

To delete an item from the current worksheet:

1. Select the item in the Selected list.
2. Click the Remove button (the button under the Add button).

7.2.2 Editing the Layout

Use the Layout tab (Figure 7-13) to arrange the items on the worksheet. Drag an item to its new position on the worksheet just as you do on the worksheet itself. ClickHide Duplicate Rows to hide rows that contain the same data.

To remove the Page Items box from the top of the worksheet, drag all items from that box to the report body, then click Shows Page Items to remove the checkmark.

Figure 7-13 Edit Sheet Layout

 
Description of "Figure 7-13 Edit Sheet Layout " 

7.2.3 Editing the Conditions

Use the Conditions tab to edit the conditions on the worksheet. Select the conditions for the worksheet. Click to remove the checkmarks next to the conditions that you donot want as part of the worksheet. SeeSection 5.5, "Finding Data that Meets Conditions" for a complete description of conditions.

Figure 7-14 Edit Sheet Conditions

 
Description of "Figure 7-14 Edit Sheet Conditions " 

7.2.4 Editing the Sorting

Use the Sorting tab to change the conditions for sorting on a table. The sort information shows the current sorting applied to the worksheet. See Section 4.3.1, "Sorting Data on Tables" for a complete description of the sorting options for tables.

Note: The Sorting tab is only available for tabular worksheets.

Figure 7-15 Edit Sheet Sorting

 
Description of "Figure 7-15 Edit Sheet Sorting " 

7.2.5 Editing Calculations

Use the Calculation tab (Figure 7-16) to edit the calculations applied to the worksheet. The selected calculations are active for the worksheet. See Section 8.3, "Creating Calculations" for a complete description of the calculations.

Figure 7-16 Edit Sheet Calculations

 
Description of "Figure 7-16 Edit Sheet Calculations "