How to dowload Worklflow file(.wft) from Front end:

Workflow Definitions Loader

To run the Workflow Definitions Loader for the version of Oracle Workflow embedded in Oracle Applications:

1. Navigate to the Submit Requests form in Oracle Applications to submit the Workflow Definitions Loader concurrent program. When you install and set up Oracle Applications and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from. See: Overview of Concurrent Programs and Requests

2. Submit the Workflow Definitions Loader concurrent program as a request. See: Submitting a Request.

3. In the Parameters window, enter values for the following parameters:

Mode	Specify "Download" to download a process definition from the database to a flat file.
	Specify "Upgrade" to apply a seed data upgrade to a database from an input file. The Workflow Definitions Loader assumes the access level of the file's creator (seed data provider) and overwrites any objects protected at a level equal to or above the upgrade file's access level. The Loader program preserves any customizations made to customizable seed data in the database.
	Specify "Upload" to load a process definition from a flat file into the database. The upload mode is useful to someone who is developing a workflow process. It allows the developer to save definitions to the database without concern that accidental customizations to existing objects might prevent the upload of some process definition elements. The Workflow Definitions Loader uses the access level defined by the input file to upload the process definitions from the file and therefore will overwrite objects in the database that are protected at a level equal to or higher than that file's access level.
	Specify "Force" to force an upload of the process definitions from an input file to a database regardless of an object's protection level You should be certain that the process definition in the file is correct as it overwrites the entire process stored in the database. The Force mode is useful for fixing data integrity problems in a database with a known, reliable file backup and for loading .wft files from Oracle Workflow Release 1.0 or 1.0.1, which reflect an older data model.

Note: When using the Force mode to load a .wft file from Oracle Workflow Release 1.0 or 1.0.1 into a database, you must also complete a manual step once the .wft file is loaded. You must associate the lookup types that you load with an item type. To do this, in the Navigator window of Oracle Workflow Builder, drag the lookup types from the independent Lookup Types branch to a Lookup Types branch associated with an item type.

File	Specify the full path and name of the file that you want to download a process definition to, or upgrade or upload a process definition from.
Item Type	If you set Mode to "Download", use the List button to choose the item type for the process definition you want to download.

Note: When you submit the Workflow Definitions Loader from the Submit Requests form to download process definitions to a file, you can only specify to download one item type at a time. If you wish to download multiple or all item types simultaneously, you should submit the Workflow Definitions Loader concurrent program from the command line. See Step 6 below for details.

4. Choose OK to close the Parameters window.

5. When you finish modifying the print and run options for this request, choose Submit to submit the request.

6. Rather than use the Submit Requests form, you can also run the Workflow Definitions Loader concurrent program from the command line by entering the following commands:

To upgrade-- WFLOAD apps/pwd 0 Y UPGRADE file.wft

To upload-- WFLOAD apps/pwd 0 Y UPLOAD file.wft

To force-- WFLOAD apps/pwd 0 Y FORCE file.wft

To download-- WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2 ... ITEMTYPEN]

Replace apps/pwd with username and password to the APPS schema, replace file.wft with the file specification of a workflow process definition file, and replace ITEMTYPE1, ITEMTYPE2, ... ITEMTYPENwith the one or more item type(s) you want to download. You can also download all item types simultaneously by replacing ITEMTYPE1 with '*' (make sure you enclose the asterisk in single quotes).

A file specification is specified as:

               @<application_short_name>:[<dir>/.../]file.ext

or

               <native path>

WorkFlow Commands for Dowlaond and Upload:

WorkFlow Commands and Instructions:
------------
The workflow definitions are stored into the database. The standard workflow files (wft) files are in
the $PA_TOP/patch/115/import/US/ and $PO_TOP/patch/115/import/US. The custom files are located in
$PA_TOP/patch/115/import and $PO_TOP/patch/115/import respectively.

When ever any patch is applied that impacts the workflow customizations, follow the below steps to
reapply the customizations.

1) Log on to the environment on which the customizations need to be applied.
2) Set the environment.
Ex: ./oracle/devl/app/prodappl/APPSDEVL_mmpsodevl.env
3) Fire the workflow definition download command to download the workflow definition from the database

How to Download Workflow from Oracle Applications?

DOWNLOAD WORKFLOW:

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/PAAPINVW.wft PAAPINVW

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/poxwfpag.wft POWFPOAG

The above commands creates the workflow definition files (wft) in the local directory $HOME/

4) Verify whether the workflow changes are intact.

5) If the workflow definitions are overwritten then apply the custom workflow definitions from the
mentioned directory.


How to Download Workflow from Oracle Applications?
UPLOAD WORKFLOW:

WFLOAD apps/apps 0 Y UPLOAD $PA_TOP/patch/115/import/PAAPINVW.wft

WFLOAD apps/apps 0 Y UPLOAD $PO_TOP/patch/115/import/poxwfpag.wft

verify the log and output file for any errors.

Workflow tables and its usage

Workflow tables and its usage

WF_ITEM_TYPES:

The wf_item_types table contains one record for each item_type created. The eight character name of the item_type represents the “Internal Name” of the item. It also functions as the primary key for this table. Some key columns are:

§  NAME: It is a mandatory field. It represents the internal name of the item type.

§  PROTECT_LEVEL: Level at which the data is protected. A mandatory field.

§  CUSTOM_LEVEL: Level of user who last updated the row. Again a mandatory field.

§  WF_SELECTOR: It stores the name of the PL/SQL procedure which implements selector function. This is an optional field.

§  PERSISTENCE_TYPE: Indicates whether item type is temporary or permanent.

§  PERSISTENCE_DAYS: Number of days until purge if persistence is temporary.

Workflow Item Type Display Name and description can be found in WF_ITEM_TYPES _TL table. 

WF_ITEM_ATTRIBUTES:

This table stores definitions of attributes associated with a process. The entries in this table correspond to the “Attributes” subheading in the Workflow Builder. An item attribute works like a variable which can hold values that are specific to the process instance or which may change at run time. Some key columns are:

1. §  ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.
2. §  NAME: Internal name of the attribute. A mandatory field.
3. §  SEQUENCE: Order of the attribute within the message
4. §  TYPE: Each item attribute is assigned a datatype, such as “Character”, “Number”, or “Date”.

There are three fields to hold a default value, but only one of them will be populated for any item attribute, depending upon the datatype. For example, if you create an item attribute with a datatype of “Number”, and then supply a default value, that value would be stored in the “number_default” field.

The “format” field stores information about a format mask that should be applied to number or date values, and the “subtype” field contains “SEND” or “RECEIVE”. The Translation table is WF_ITEM_ATTRIBUTES_TL and the related view is WF_ITEM_ATTRIBUTES_VL.

WF_ACTIVITIES:

This table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub-process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, but it provides a means of grouping activities. Some key columns are:

§  ITEM_TYPE: Internal name for the Item Type that owns the message.

§  NAME: Internal name for the activity.

§  VERSION: It is used to support multiple versions of the same process running at the same time. The version number works in concert with the “begin_date” and “end_date” fields, to ensure that only one version of any activity is active at any given time. By versioning, the previously launched processes retain the process definition that was in force at the time they were launched.

§  TYPE: The “type” field is the way that the individual types of activities can be distinguished. There are five valid values found in the “type” field: “FUNCTION”, “NOTICE”, “EVENT”, “PROCESS”, and “FOLDER”.

§  RERUN: Determines if activity is rerun during looping.

§  EXPAND_ROLE: Determines how many roles are required to respond to a notification activity.

§  FUNCTION: For function activities only, the field is used to store the name of the PLSQL procedure that the Workflow Engine should call to implement the function.

§  RESULT_TYPE: If you intend to model transitions in a process based upon values returned by an activity node, then the expected results must be predefined by supplying a lookup type, which is stored in this field.

§  ICON_NAME: Name of activity icon used in process window.

§  MESSAGE: For notification activities only, the field called “message” will be populated. In these cases, it will contain the internal name of the message that the notification will deliver.

§  ERROR_PROCESS: Workflow process to run in case of an error.

§  ERROR_ITEM_TYPE: Name of item type to execute in case of error.

§  RUNNABLE_FLAG: Flag (Y or N) to indicate if activity is runnable.

§  FUNCTION_TYPE: Indicates whether function type is pl/sql or internal.

WF_ACTIVITY_ATTRIBUTES:

This table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities. Each row includes the associated activity, type of attribute, and the format used by the activity. Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2. Notice that the table requires three fields just to identify to which activity the attribute is attached: the item_type, name, and version of the activity. To join this table to the wf_activities tables you must join all three of these fields to their corresponding fields in that table. Some key columns are:

§  ACTIVITY_ITEM_TYPE: Item type the activity is associated with

§  ACTIVITY_NAME: Internal name of the activity

§  ACTIVITY_VERSION: Version of the activity

§  NAME: Internal name of the attribute

§  SEQUENCE: Order of the attribute within the message

§  TYPE: This field refers to the datatype of the values that the attribute will contain.

§  VALUE_TYPE: Defines if the default is a constant or a reference to an item attribute.

WF_ACTIVITY_ATTR_VALUES:

This table used to track values contained in activity attributes. This table is identical in purpose to wf_item_attribute_values except it holds values for activity attributes instead of item attributes. Each row includes the process activity id and the associated value for the attribute. The interesting thing about this table is that it uses the process_activity_id to identify the activity to which the attribute is attached. The same activity can be inserted into a process more than one time, so the only way to uniquely identify the node to which this attribute is attached is to use the process_activity_id.

WF_MESSAGES:

The messages that are associated with notifications are stored in this table. Each message, which is uniquely identified by the combination of item_type and message_name (stored in the fields “type” and “name”) receives a single record in the wf_messages table. The actual text of the message is stored only in its localization table (wf_messages_tl). They can found in the “body” and “html_body” fields.

WF_MESSAGE_ATTRIBUTES:

This table contains message attribute definitions. Each message may have zero or more message attributes. Message attributes define additional information that is to be sent to, or received from the user. These attributes can be used as tokens in the subject or body of a message template to place variables values into the message at runtime.

WF_PROCESS_ACTIVITIES:

A process is a sequence of activities performed in a pre-determined order. When you create a process definition in the Workflow Builder by dragging various notifications and functions into the process window, the records created by the Builder are stored into this table.

WF_ACTIVITY_TRANSITIONS:

The flow of a process from node to node as indicated by the transition arrows is not saved in the wf_process_activities table. Instead this information is stored in this table.

A transition is defined by three discrete pieces of information: the node where the arrow begins, the node toward which the arrow points, and the result which, when returned by the beginning node, causes the transition to be followed. Not surprisingly, it is those three fields which are the most important fields in this table: “from_process_activity”, “to_process_activity”, and “result_code”. The values stored in “from_process_activity” and “to_process_activity” are numbers which represent the instance_id of the records from wf_process_activities from which and to which the transition is moving.

WF_LOOKUP_TYPES_TL & WF_LOOKUPS_TL:

Wf_lookup_types_tl is the table used to set up the types of results expected from Workflow activities like functions and notifications. This table does not contain the actual result values, it holds the groupings of the result_codes – the names you see in the Workflow Builder as the names of the Lookups. Wf_lookups_tl is the table that stores the component values that comprise a lookup_type.

Script to find Oracle API's for any module?

Script to find Oracle API's for any module:

select substr(a.OWNER,1,20)

, substr(a.NAME,1,30)

, substr(a.TYPE,1,20)

, substr(u.status,1,10) Stat

, u.last_ddl_time

, substr(text,1,80) Description

from dba_source a, dba_objects u

WHERE 2=2

and u.object_name = a.name 

and a.text like '%Header%'

and a.type = u.object_type

and a.name like 'AP_%API%' –- Checking for AP Related APIs

order by

a.owner, a.name

FOR KILLING A CONCURRENT REQUEST AND FOR KILLING A SESSION

FOR KILLING A CONCURRENT REQUEST:

UPDATE fnd_concurrent_requests

   SET phase_code = ‘C’,

       status_code = ‘X’

 WHERE request_ID = xxxx;

FOR KILLING A SESSION:

Syntax:

alter system kill session ‘<sid>,<Serial#>’;

How to submit XMLP Report using a PL/SQL Script:

How to submitXMLP Report using a PL/SQL Script:

Generally we use FND_REQUEST.SUBMIT_REQUEST to submit a concurrent program using a PL/SQL script. But we can not attach a layout to the concurrent request using the above said API.

We can attach a layout to the concurrent request by using another procedure ADD_LAYOUT which belongs to the same package FND_REQUEST

Below is the signature of the procedure ADD_LAYOUT:

fnd_request.add_layout (

template_appl_name  => 'Template Application',

template_code       => 'Template Code',

template_language   => 'en', --Use language from template definition

template_territory  => 'US', --Use territory from template definition

output_format       => 'PDF' --Use output format from template definition

);

Note:

1. ADD_LAYOUT procedure should be called only when there is a layout associated to a concurrent program
2. Session context should be set using ‘FND_GLOBAL.APPS_INITIALIZE’ before calling the ADD_LAYOUT procedure

Example:

DECLARE

--

l_responsibility_id NUMBER;

l_application_id     NUMBER;

l_user_id           NUMBER;

l_request_id            NUMBER;

l_layout                NUMBER;

--

BEGIN

  --

  SELECT DISTINCT fr.responsibility_id,

    frx.application_id

     INTO l_responsibility_id,

    l_application_id

     FROM apps.fnd_responsibility frx,

    apps.fnd_responsibility_tl fr

    WHERE fr.responsibility_id = frx.responsibility_id

  AND LOWER (fr.responsibility_name) LIKE LOWER('XXTest Resp');

  --

   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'STHALLAM';

  --

  --To set environment context.

  --

  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);

  --

  --Setting Layout for the Request

  --

  l_layout := apps.fnd_request.add_layout(

                            template_appl_name => 'XXCUST',

                            template_code      => 'XXEMP',

                            template_language  => 'en',

                            template_territory => 'US',

                            output_format      => 'EXCEL');

  --

  --Submitting Concurrent Request

  --

  l_request_id := fnd_request.submit_request ( 

                            application   => 'XXCUST', 

                            program       => 'XXEMP', 

                            description   => 'XXTest Employee Details', 

                            start_time    => sysdate, 

                            sub_request   => FALSE,

   argument1     => 'Smith'

  );

  --

  COMMIT;

  --

  IF l_request_id = 0

  THEN

     dbms.output.put_line ('Concurrent request failed to submit');

  ELSE

     dbms_output.put_line('Successfully Submitted the Concurrent Request');

  END IF;

  --

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||SQLERRM);

END;

/

XMLP tables in oracle apps

XMLP tables:

Table Name	Description
XDO_CONFIG_PROPERTIES_B	Stores the XML Publisher Administration configuration properties that are accessible from the OA Framework interface.
XDO_CONFIG_PROPERTIES_TL	Translation table for XDO_CONFIG_PROPERTIES_B.
XDO_CONFIG_VALUES	Stores the values assigned to the property in Administration Configuration Data
XDO_CURRENCY_FORMATS	Stores the format masks for various currencies. A collection of these formats forms a currency format set.
XDO_CURRENCY_FORMAT_SETS_B	Stores the Currency Format Sets
XDO_CURRENCY_FORMAT_SETS_TL	Stores the Currency Format Sets
XDO_DS_DEFINITIONS_B	Stores data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and these information are stored in XDO_DS_ELEMENTS_B
XDO_DS_DEFINITIONS_TL	Translation table for XDO_DS_DEFINITIONS_B
XDO_FONT_MAPPINGS	Stores the mappings from a base font to a target Truetype or Type 1 font. A collection of these mappings forms a font mapping set
XDO_FONT_MAPPING_SETS_B	Stores the header information for a font mapping set, which is a collection of font mappings
XDO_FONT_MAPPING_SETS_TL	Translation table for XDO_FONT_MAPPING_SETS_B
XDO_LOBS	Stores Template(RTF File), XML File, XML Schema File, locale(langauge and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
XDO_TEMPLATES_B	Stores template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information are stored in the XDO_TEMPLATE_FILES.
XDO_TEMPLATES_TL	Translatable table for XDO_TEMPLATES_B
XDO_TEMPLATE_FIELDS	Stores information of the fields of template file. Each field belongs to one of physical template files
XDO_TRANS_UNITS	Stores the header information regarding each segment of translatable text in layout templates
XDO_TRANS_UNIT_PROPS	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations
XDO_TRANS_UNIT_VALUES	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translationsd