How to create concurrent programs from database?


Here are couple of APIs useful for creating any concurrent programs from the backend database.

1)    Registering the Executable from back-end:

Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too. Below is the PL/SQL code to create an executable from back-end.

BEGIN
  FND_PROGRAM.executable(executable => 'XXFIN TEST EXECUTABLE' , -- Executable Name
  application=>'XXFIN' , -- Application Short Name
  short_name=>'XXFINTSTEXE' , -- Executable Short Name
  description=>'Test Executable created from Backend' ,     -- Description,DEFAULT NULL
  execution_method=>'PL/SQL Stored Procedure',              -- Execution Method
  execution_file_name=>'XXFIN_TEST_PROC' ,                  -- Execution File Name,DEFAULT NULL
  subroutine_name=>NULL ,                                   -- Subroutine Name,DEFAULT NULL
  icon_name=>NULL ,                                         -- Icon Name,DEFAULT NULL
  language_code=>'US' ,                                     -- Language Code,DEFAULT 'US'
  execution_file_path=>NULL                                 -- Execution File Path, DEFAULT NULL
  );
  COMMIT;
END;

View from Frontend:

Creating Executable

Notes:

1] The above API inserts the new records in FND_EXECUTABLES and FND_EXECUTABLES_TL table.

2] You can use the below query to get all the Execution Methods available:

SELECT MEANING “Execution Method”

FROM fnd_lookup_values

WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’

AND enabled_flag  = ‘Y’;

2)    Registering the Concurrent program from back-end:     

Usually we create Concurrent program in the front-end, but this can be done from the database tier too. Below is the program to create a Concurrent program from back-end.

BEGIN
  FND_PROGRAM.register(program =>'Test CP from DB', -- CP Name
  application =>'XXFIN' , -- Application Short Name
  enabled =>'Y',                                    -- Flag to Enable/Disable a CP
  short_name =>'XXFINTSTCPDB', -- CP Short Name
  description =>'Test CP created from Backend' ,    -- Description,DEFAULT NULL
  executable_short_name =>'XXFINTSTEXE', -- Executable Short Name
  executable_application =>'XXFIN' , -- Executable Application Short Name
  execution_options => NULL,                        -- Execution Options,DEFAULT NULL,
  priority => NULL,                                 -- Priority,DEFAULT NULL,
  save_output =>'Y',                                -- Save Output,DEFAULT 'Y',
  PRINT =>'Y' ,                                     -- Print,DEFAULT 'Y',
  cols => NULL, -- DEFAULT NULL,
  rows => NULL, -- DEFAULT NULL,
  style => NULL,                                    -- DEFAULT NULL,
  style_required =>'N' ,                            -- DEFAULT 'N',
  printer => NULL,                                  -- DEFAULT NULL,
  request_type => NULL,                             -- DEFAULT NULL,
  request_type_application => NULL,                 -- DEFAULT NULL,
  use_in_srs =>'N' ,                                -- DEFAULT 'N',
  allow_disabled_values =>'N' ,                     -- DEFAULT 'N',
  run_alone =>'N' ,                                 -- DEFAULT 'N',
  output_type =>'TEXT',                             -- DEFAULT 'TEXT'
  enable_trace =>'N' ,                              -- DEFAULT 'N',
  restart =>'Y' ,                                   -- DEFAULT 'Y',
  nls_compliant =>'Y' ,                             -- DEFAULT 'Y',
  icon_name => NULL,                                -- DEFAULT NULL,
  language_code => 'US',                            -- DEFAULT 'US',
  mls_function_short_name => NULL,                  -- DEFAULT NULL,
  mls_function_application => NULL,                 -- DEFAULT NULL,
  incrementor => NULL, -- DEFAULT NULL,
  refresh_portlet => NULL                           -- DEFAULT NULL,
  );
  COMMIT;
END;

View from Frontend:

Creating CP

Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.

2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

3)    Attaching the concurrent program to the request group

Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too. Below is the program to Attach Concurrent program to the request group from back-end.

BEGIN
  FND_PROGRAM.add_to_group('XXFINTSTCPDB', -- Concurrent Program Short Name
  'XXFIN' , -- Application Short Name
  'All Reports',                           -- Report Group Name
  'SQLAP'); -- Report Group Application
  COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all.

Advertisements

How to design An Accounting Flexfield Parameter for your Report?


If you want to design similar functionality as below in your report parameter, do the below steps.

Concurrent Program

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

CP Parameters

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD

2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:

XLA_SRS_ACCOUNTING_FLEXFIELD

Edit:

FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”

  VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”

  NAVIGATE=”!DIR” VDATE=””

Validate:

FND VALIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS VALIDATE=”NONE”

  REQUIRED=”Y” DESC=”:!MEANING” SEG=”:!VALUE” VDATE=””

Special Value set

How to Clear Cache from Oracle Application without Bouncing Listener?


Caching Framework in oracle apps R12 comes with an administration User interface and it is available under the Functional Administrator responsibility. This interface can be used to perform administrative operations including changing the time-out values for cache components, looking at cache usage statistics, and clearing caches.

Here are the steps to clear all Global Cache:

1] Login to Functional Administrator responsibility – then choose Home.

2] Choose the ‘Core Services’ Tab – then the “Caching Framework” Sub-Menu.

3] Proceed to choose ‘Global Configuration’ from the left hand side menu.

4] In the far right choose ‘Clear all Cache’ button.

5] A screen prompts and confirms that the action will clear all cache on the middle tier server – choose Yes. Essentially, this just forces all user sessions to engage and validate – rather than using cached values.

6] A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.

7] Proceed to test and confirm whatever change was made to the preference, profile, etc….

Please note that clearing the OA Framework cache can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared. Please use this utility with proper care.

You can also clear cache for specific component. To do that you need to go to Core Services -> Caching Framework -> Tuning. Query the application (for example iProcurement) or by Name or code.  Select the component and clear the cache.

Messages in Oracle Application


What is a message in Oracle Application?

Oracle Application uses the Message Dictionary to store translatable Error and Warning messages that can be used by any programs written in Forms, Reports, Java, or PL/SQL.

These messages mainly provide information about business rule errors, such as missing or incorrect data, and how to resolve them, warn about the consequences of intended actions, inform about the status of an application, pages, or business objects, and indicate that processes and actions are performing or are completed.

By using the messages in the Message Dictionary, you can define standard messages that you can use in all your applications(Oracle Form, Reports, OAF and ADF), provide consistency for messages within and across all your applications, define flexible messages, and change or translate the text of your messages without regenerating or recompiling your application code.

How will you create a Message?

In Application Developer Responsibility, navigate to (N) Application | Messages.

Using that screen, the new messages can be created by entering records. The contents entered in field message text become visible to the end user. The content of the message can optionally have tokens, which act as placeholders for dynamically substituted values at runtime.

You can also create messages from OAF Page. For that-

1] Login to Functional Administrator responsibility – then choose Home.

2] Choose the ‘Core Services’ Tab – then the “Messages” Sub-Menu.

The components of a Message:

Component Name Description
Name Every message must have a unique name. You should include a unique prefix that makes it easier to find your custom messages and that helps to avoid name conflicts with non-custom messages.
Language Select the language that your message is written in.
Application Select the application that the message belongs, this will usually be the custom application.
Current Message Text Message text is required. This is a brief statement of the operation attempted and the problem that occurred as a result, or information that the user needs to know. The maximum field size for messages stored in the Message Dictionary is 240 characters.
Number A unique and persistent message number can be included with each message. When displayed, the number takes the format of (Application ShortnameNumber). If the message does not have a message number, the formatted number is not displayed.
Type The message type indicates which message components are applicable, determines whether implicit logging and incident creation occurs, and determines the logging level if the message is logged.
Maximum Length Maximum number of display characters the translators can use to translate the message.
Description Description of the Message.
Alert Category This will allow user interfaces and other programs to filter exception messages based on category. The types are Product, System, Security and User.
Alert Severity This will allow user interfaces and other programs to filter exception messages based on severity. The types can be: Critical, Error or Warning.
Log Severity This group indicates the Log severity levels like: Unexpected, Error, Exception, Event, Procedure, Statement or Off.

About Tokens:

Tokens are identified in the message text by their use of & and all uppercase letters. The token values are supplied at runtime by the code that raises the message. For example, the following token &FIELD_NAME is replaced by a field when the user receives the error message on their screen:

“A Value must be entered for &FIELD_NAME.

Becomes: “A Value must be entered for PO Number.”.

Table Used by Messages Dictionary:

FND_NEW_MESSAGES stores application messages for Message Dictionary. Each row includes the application to which the message belongs, the language the message is in, the message name, the message text, and the message number. You need one row for each application message in each of the language.

APIs to Set, Retrieve, Clear the messages:

Use the Message Dictionary APIs to retrieve a Message Dictionary message. The PL/SQL methods are in the FND_MESSAGE package and the Java methods are in the messageService package. There are many functions and procedures in the package- FND_MESSAGE. However I have given the details of three most used procedures below.

procedure SET_NAME(APPLICATION in varchar2, NAME in varchar2)

In Database Server, this Sets a message name in the global area without actually retrieving the message from Message Dictionary.

procedure SET_TOKEN(TOKEN     in varchar2,

                        VALUE     in varchar2,

                        TRANSLATE in boolean default false)

This procedure defines a message token with a value. In Database Server, SET_TOKEN adds a token/value pair to the global area without actually doing the substitution. Call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message.

function GET return varchar2

This function gets a translated and token substituted message from the message dictionary database. It returns NULL if the message cannot be found. If this function is called from a stored procedure on the database server side, the message is retrieved from the Message Dictionary table. If the function is called from a form or forms library, the message is retrieved from the messages file on the forms server.

How to use message in PL/SQL Concurrent Program:

fnd_message.clear;
fnd_message.set_name ('XXSCM', 'XXSCM_MANDATORY_FIELD');
fnd_message.set_token('FIELD_NAME', 'PO Number');
--Now get the final string
l_message := fnd_message.get;
--Display the message text in output of concurrent program
fnd_file.put_line(fnd_file.OUTPUT, l_message);

How to use message in Oracle Forms:

fnd_message.set_name('XXSCM', 'XXSCM_MANDATORY_FIELD');
fnd_message.set_token('FIELD_NAME', 'PO Number');
fnd_message.show;

How to use message in OA Framework Controller:

String sReturnMsg = oapagecontext.getMessage("XXSCM", " XXSCM_MANDATORY_FIELD ", new MessageToken[] {new MessageToken(" FIELD_NAME ", "PO Number") });

Example of retrieving message from the Stack:

-- setting INVALID_USER as the current message
fnd_message.set_name('FND', 'INVALID_USER');
-- setting value for token NAME for INVALID_USER message
fnd_message.set_token('NAME', 'TESTUSER');
-- saving the current message onto stack
fnd_message.push;
-- setting LOGIN_FAILED as the current message
fnd_message.set_name('FND', 'LOGIN_FAILED');
-- saving the current message onto stack
fnd_message.push;
-- poping one message out of stack and set it as the current message
fnd_message.pop;
-- get the translated and token subsituted LOGIN_FAILED message
-- then clear the current message
msg := fnd_message.get;
-- poping one message out of stack and set it as the current message
fnd_message.pop;
-- get the translated and token subsituted INVALID_USER message
-- then clear the message
msg := fnd_message.get;

FNDLOAD for Messages:

Download:

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXSCM_MANDATORY_FIELD_MSG.ldt  FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’XXSCM’ MESSAGE_NAME=”XXSCM_MANDATORY_FIELD”

Upload:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXSCM_MANDATORY_FIELD_MSG.ldt

SQL related to Oracle Application Messages:

SELECT m.message_name,
  m.message_text,
  m.message_number,
  a.application_short_name
FROM FND_NEW_MESSAGES M,
  FND_APPLICATION a
WHERE upper(m.message_text) LIKE upper('%&Enter_Message_Text%')
AND m.language_code  = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID;

SELECT m.message_name,
  m.message_text,
  m.message_number,
  a.application_short_name
FROM FND_NEW_MESSAGES M,
  FND_APPLICATION a
WHERE m.message_name LIKE '%&Enter_Message_Name%'
AND m.language_code  = 'US'
AND M.APPLICATION_ID = a.APPLICATION_ID;

API to Load Values into Value Sets


DECLARE
----------------------------Local Variables---------------------------
   l_enabled_flag             VARCHAR2 (2);
   l_summary_flag             VARCHAR2 (2);
   l_who_type                 FND_FLEX_LOADER_APIS.WHO_TYPE;
   l_user_id                  NUMBER                := FND_GLOBAL.USER_ID;
   l_login_id                 NUMBER                := FND_GLOBAL.LOGIN_ID;
   l_value_set_name           FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_NAME%TYPE;
   l_value_set_value          FND_FLEX_VALUES.FLEX_VALUE%TYPE;
BEGIN

   l_value_set_name             :='VALUE_SET_NAME';
   l_value_set_value            :='VALUE_SET_VALUE';
   l_enabled_flag               := 'Y';
   l_summary_flag               := 'N';
   l_who_type.created_by        := l_user_id;
   l_who_type.creation_date     := SYSDATE;
   l_who_type.last_updated_by   := l_user_id;
   l_who_type.last_update_date  := SYSDATE;
   l_who_type.last_update_login := l_login_id;

     fnd_flex_loader_apis.up_value_set_value
                  (p_upload_phase               => 'BEGIN',
                   p_upload_mode                => NULL,
                   p_custom_mode                => 'FORCE',
                   p_flex_value_set_name        => l_value_set_name,
                   p_parent_flex_value_low      => NULL,
                   p_flex_value                 => l_value_set_value,
                   p_owner                      => NULL,
                   p_last_update_date           => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_enabled_flag               => l_enabled_flag,
                   p_summary_flag               => l_summary_flag,
                   p_start_date_active          => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                   p_end_date_active            => NULL,
                   p_parent_flex_value_high     => NULL,
                   p_rollup_flex_value_set_name => NULL,
                   p_rollup_hierarchy_code      => NULL,
                   p_hierarchy_level            => NULL,
                   p_compiled_value_attributes  => NULL,
                   p_value_category             => 'VALUE_SET_NAME',
                   p_attribute1                 => '40912',
                   p_attribute2                 => NULL,
                   p_attribute3                 => NULL,
                   p_attribute4                 => NULL,
                   p_attribute5                 => NULL,
                   p_attribute6                 => NULL,
                   p_attribute7                 => NULL,
                   p_attribute8                 => NULL,
                   p_attribute9                 => NULL,
                   p_attribute10                => NULL,
                   p_attribute11                => NULL,
                   p_attribute12                => NULL,
                   p_attribute13                => NULL,
                   p_attribute14                => NULL,
                   p_attribute15                => NULL,
                   p_attribute16                => NULL,
                   p_attribute17                => NULL,
                   p_attribute18                => NULL,
                   p_attribute19                => NULL,
                   p_attribute20                => NULL,
                   p_attribute21                => NULL,
                   p_attribute22                => NULL,
                   p_attribute23                => NULL,
                   p_attribute24                => NULL,
                   p_attribute25                => NULL,
                   p_attribute26                => NULL,
                   p_attribute27                => NULL,
                   p_attribute28                => NULL,
                   p_attribute29                => NULL,
                   p_attribute30                => NULL,
                   p_attribute31                => NULL,
                   p_attribute32                => NULL,
                   p_attribute33                => NULL,
                   p_attribute34                => NULL,
                   p_attribute35                => NULL,
                   p_attribute36                => NULL,
                   p_attribute37                => NULL,
                   p_attribute38                => NULL,
                   p_attribute39                => NULL,
                   p_attribute40                => NULL,
                   p_attribute41                => NULL,
                   p_attribute42                => NULL,
                   p_attribute43                => NULL,
                   p_attribute44                => NULL,
                   p_attribute45                => NULL,
                   p_attribute46                => NULL,
                   p_attribute47                => NULL,
                   p_attribute48                => NULL,
                   p_attribute49                => NULL,
                   P_ATTRIBUTE50                => NULL,
                   p_flex_value_meaning         => l_value_set_value,
                   p_description                => NULL
                   );
      COMMIT;

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT,PUT_LINE('Error is ' || SUBSTR (SQLERRM, 1, 1000));
   END;

Deriving Oracle GL Account Code Combination ID’s (CCID’s) through APIs


1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.

The Combination_id output may be NULL if combination is invalid.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_application_short_name VARCHAR2(240);
  l_key_flex_code          VARCHAR2(240);
  l_structure_num          NUMBER;
  l_validation_date        DATE;
  n_segments               NUMBER;
  SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_combination_id         NUMBER;
  l_data_set               NUMBER;
  l_return                 BOOLEAN;
  l_message                VARCHAR2(240);
BEGIN
  l_application_short_name := 'SQLGL';
  l_key_flex_code          := 'GL#';

  SELECT id_flex_num
  INTO l_structure_num
  FROM apps.fnd_id_flex_structures
  WHERE ID_FLEX_CODE        = 'GL#'
  AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;

  l_validation_date        := SYSDATE;
  n_segments               := 6;
  segments(1)              := '00101';
  segments(2)              := '28506';
  segments(3)              := '00000';
  segments(4)              := '09063';
  segments(5)              := '00000';
  segments(6)              := '00000';
  l_data_set               := NULL;

  l_return := FND_FLEX_EXT.GET_COMBINATION_ID(
                    application_short_name => l_application_short_name,
                    key_flex_code          => l_key_flex_code,
                    structure_number       => l_structure_num,
                    validation_date        => l_validation_date,
                    n_segments             => n_segments,
                    segments               => segments,
                    combination_id         => l_combination_id,
                    data_set               => l_data_set
                    );
  l_message:= FND_FLEX_EXT.GET_MESSAGE;

  IF l_return THEN
    DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error: '||l_message);
  END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.

This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).

Various Operations that can be performed are:

  • ‘FIND_COMBINATION’ – Combination must already exist.
  • ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
  • ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
  • ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
  • ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
  • ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1                 VARCHAR2(240);
  p_error_msg2                 VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid:=NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
  ELSE
  DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;

AP Table Handler APIs


These APIs are handful when you want to do Insert, Update or Delete in some AP Base tables i.e. AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS, AP_INVOICE_PAYMENTS and AP_CHECKS_ALL programmatically for some business requirements (rare cases!).

1] AP_AI_TABLE_HANDLER_PKG:

  • Procedure Insert_Row:  Inserts a row in AP_INVOICES_ALL table.
  • Procedure Update_Row:  Updates a row in AP_INVOICES_ALL table.
  • Procedure Delete_Row:  Deletes a row in AP_INVOICES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL.

2] AP_AIL_TABLE_HANDLER_PKG:

  • Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
  • Procedure Insert_Row:  Inserts a row in AP_INVOICE_LINES_ALL table.
  • Procedure Update_Row:  Updates a row in AP_INVOICE_LINES_ALL table.
  • Procedure Delete_Row:  Deletes a row in AP_INVOICE_LINES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL.

3] AP_AID_TABLE_HANDLER_PKG:

  • Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
  • Procedure Insert_Row:  Inserts a row in AP_INVOICE_DISTRIBUTIONS table.
  • Procedure Update_Row:  Updates a row in AP_INVOICE_DISTRIBUTIONS table.
  • Procedure Delete_Row:  Deletes a row in AP_INVOICE_DISTRIBUTIONS table.

4] AP_AIP_TABLE_HANDLER_PKG:

  • Procedure Insert_Row:  Inserts a row in AP_INVOICE_PAYMENTS table.
  • Procedure Update_Amounts: Update amounts in AP_INVOICE_PAYMENTS table.

5] AP_AC_TABLE_HANDLER_PKG:

  • Procedure Insert_Row:  Inserts a row in AP_CHECKS_ALL table.
  • Procedure Update_Row:  Updates a row in AP_CHECKS table.
  • Procedure Delete_Row:  Deletes a row in AP_CHECKS table.
  • Procedure Update_Amounts: Update amounts in AP_CHECKS table.

How to Use Global Variables in Form Personalization?


Here let’s say the requirement is to run a concurrent program through a custom menu from an oracle form and in that concurrent program parameters, we need to fetch some values from the oracle form itself. In that case we need to first assign the form values to some Global Variables and then use them in the concurrent program parameters. Here are the Steps:

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.

3] On the triggering event: SPECIAL15, do the following actions:

  • Select Type as ‘Property’ and select Object Type as ‘Global Variable’.
  • Give a name to the global variable in the ‘Target Object’ tab. (Ex. G_ITEM_NAME, G_ORG_CODE)
  • Select the Property Name as ‘VALUE’.
  • In the value tab- Give ‘=:Block_name.Field_name’ (Use: Help > Diagnostics > Examine). This is the value which you want to put in the global variable.

4] Select Type as ‘Builtin’ and Select Builtin Type as ‘Launch SRS Form’.

5] Select your Concurrent Program in the ‘Program Name’ tab. Please note that you need to assign the Function – ‘Requests: Submit’ to the Main Menu of the responsibility to which your concurrent program is attached.

  • Use: System Administrator > Security > Responsibility > Define to find the Menu Name.
  • Go to System Administrator > Application > Menu and add the Function – ‘Requests: Submit’ at the end and Save.
  • Also don’t forget to attach the concurrent program to proper Request Group.

6] Go to the concurrent program parameters. Select the Default Type as SQL Statement in the Validation tab and give the default values as

  • select :GLOBAL.G_ITEM_NAME from dual
  • select :GLOBAL.G_ORG_CODE from dual

7] Save and Test the functionality.

An Alternate Way:

Create a PL/SQL function in the database that calls fnd_request.submit_request and commits in an AUTONOMOUS TRANSACTION. The function returns a message to the user, with the request_id.

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.

3] On the triggering event: SPECIAL15, do the following actions:

  • Define a global variable for the message (Ex. XX_CONC_PROG_RESULT)
  • Assign the above global variable the following value: =SELECT <Your Custom PL/SQL Function> from dual. You can pass parameters to the function as: Block_name.Field_name.
  • Define a message to show as =:GLOBAL.XX_CONC_PROG_RESULT

API to Update Task Information in Oracle Projects


Here is one API to update Task Information in Oracle Projects. Here I have used the API to update the Task Manager Information.

DECLARE

  l_return_status                    VARCHAR(10);
  l_msg_count                        VARCHAR(240);
  l_MSG_DATA                         VARCHAR(240);
  l_rowid                            VARCHAR2(240);
  task_record                        PA_TASKS%ROWTYPE;
  task_struc_record                  PA_PROJ_ELEMENTS%ROWTYPE;
  l_last_updated_by                  NUMBER := FND_GLOBAL.USER_ID;
  l_last_update_date                 DATE;
  l_last_update_login                NUMBER := FND_GLOBAL.LOGIN_ID;
  pt_task_name                       PA_TASKS.TASK_NAME%TYPE;
  l_task_name                        PA_PROJ_ELEMENTS.NAME%TYPE;
  l_task_name1                       PA_PROJ_ELEMENTS.NAME%TYPE;
  l_project_id                       PA_PROJECTS_ALL.PROJECT_ID%TYPE;
  l_task_id                          PA_TASKS.TASK_ID%TYPE;
  l_task_manager_person_id           PA_TASKS.TASK_MANAGER_PERSON_ID%TYPE;
  l_output                           VARCHAR2 (2000);
  l_msg_dummy                        VARCHAR2 (2000);
  n                                  NUMBER := 0;

BEGIN

     l_project_id :=590;
     l_task_id    :=3355;
     l_task_manager_person_id :=136263;    --Koch, Dibyajyoti

    BEGIN
      SELECT *
       INTO task_record
       FROM pa_tasks
      WHERE project_id =l_project_id
        AND task_id =l_task_id;
    EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END;

    BEGIN
      SELECT *
        INTO task_struc_record
        FROM pa_proj_elements
       WHERE PROJECT_ID=l_project_id
         AND PROJ_ELEMENT_ID=l_task_id;
    EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END;

    BEGIN
      SELECT ROWID
        INTO l_rowid
        FROM pa_tasks
       WHERE project_id =l_project_id
         AND task_id =l_task_id;
    EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END;

    BEGIN
      SELECT SYSDATE
        INTO l_last_update_date
        FROM DUAL;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;

    BEGIN
      SELECT task_name
        INTO pt_task_name
        FROM pa_tasks
       WHERE task_id = l_task_id;

      SELECT name
        INTO l_task_name
        FROM pa_proj_elements
       WHERE proj_element_id = l_task_id;

      IF pt_task_name = l_task_name THEN
         l_task_name1 :=l_task_name;
      ELSE
        l_task_name1 := pt_task_name;
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END;

        PA_TASKS_PKG.UPDATE_ROW(
                    X_ROWID                        =>l_rowid,
                    X_TASK_ID                      =>task_record.task_id,
                    X_PROJECT_ID                   =>task_record.project_id,
                    X_TASK_NUMBER                  =>task_record.task_number,
                    X_LAST_UPDATE_DATE             =>l_last_update_date, --Updated Value
                    X_LAST_UPDATED_BY              =>l_last_updated_by, --Updated Value
                    X_LAST_UPDATE_LOGIN            =>l_last_update_login,--Updated Value
                    X_Task_Name                    =>task_record.task_name,
                    X_Long_Task_Name               =>task_record.long_task_name,
                    X_TOP_TASK_ID                  =>task_record.top_task_id,
                    X_WBS_LEVEL                    =>task_record.wbs_level,
                    X_READY_TO_BILL_FLAG           =>task_record.ready_to_bill_flag,
                    X_READY_TO_DISTRIBUTE_FLAG     =>task_record.ready_to_distribute_flag,
                    X_PARENT_TASK_ID               =>task_record.parent_task_id,
                    X_DESCRIPTION                  =>task_record.description,
                    X_CARRYING_OUT_ORGANIZATION_ID =>task_record.carrying_out_organization_id,
                    X_SERVICE_TYPE_CODE            =>task_record.service_type_code,
                    X_TASK_MANAGER_PERSON_ID       =>l_task_manager_person_id,--Updated Value
                    X_CHARGEABLE_FLAG              =>task_record.chargeable_flag,
                    X_BILLABLE_FLAG                =>task_record.billable_flag,
                    X_LIMIT_TO_TXN_CONTROLS_FLAG   =>task_record.limit_to_txn_controls_flag,
                    X_START_DATE                   =>task_record.start_date,
                    X_COMPLETION_DATE              =>task_record.completion_date,
                    X_ADDRESS_ID                   =>task_record.address_id,
                    X_LABOR_BILL_RATE_ORG_ID       =>task_record.labor_bill_rate_org_id,
                    X_LABOR_STD_BILL_RATE_SCHDL    =>task_record.labor_std_bill_rate_schdl,
                    X_LABOR_SCHEDULE_FIXED_DATE    =>task_record.labor_schedule_fixed_date,
                    X_LABOR_SCHEDULE_DISCOUNT      =>task_record.labor_schedule_discount,
                    X_NON_LABOR_BILL_RATE_ORG_ID   =>task_record.non_labor_bill_rate_org_id,
                    X_NL_STD_BILL_RATE_SCHDL       =>task_record.non_labor_std_bill_rate_schdl,
                    X_NL_SCHEDULE_FIXED_DATE       =>task_record.non_labor_schedule_fixed_date,
                    X_NON_LABOR_SCHEDULE_DISCOUNT  =>task_record.non_labor_schedule_discount,
                    X_LABOR_COST_MULTIPLIER_NAME   =>task_record.labor_cost_multiplier_name,
                    X_ATTRIBUTE_CATEGORY           =>task_record.attribute_category,
                    X_ATTRIBUTE1                   =>task_record.attribute1,
                    X_ATTRIBUTE2                   =>task_record.attribute2,
                    X_ATTRIBUTE3                   =>task_record.attribute3,
                    X_ATTRIBUTE4                   =>task_record.attribute4,
                    X_ATTRIBUTE5                   =>task_record.attribute5,
                    X_ATTRIBUTE6                   =>task_record.attribute6,
                    X_ATTRIBUTE7                   =>task_record.attribute7,
                    X_ATTRIBUTE8                   =>task_record.attribute8,
                    X_ATTRIBUTE9                   =>task_record.attribute9,
                    X_ATTRIBUTE10                  =>task_record.attribute10,
                    X_COST_IND_RATE_SCH_ID         =>task_record.cost_ind_rate_sch_id,
                    X_REV_IND_RATE_SCH_ID          =>task_record.rev_ind_rate_sch_id,
                    X_INV_IND_RATE_SCH_ID          =>task_record.inv_ind_rate_sch_id,
                    X_COST_IND_SCH_FIXED_DATE      =>task_record.cost_ind_sch_fixed_date,
                    X_REV_IND_SCH_FIXED_DATE       =>task_record.rev_ind_sch_fixed_date,
                    X_INV_IND_SCH_FIXED_DATE       =>task_record.inv_ind_sch_fixed_date,
                    X_LABOR_SCH_TYPE               =>task_record.labor_sch_type,
                    X_NON_LABOR_SCH_TYPE           =>task_record.non_labor_sch_type,
                    X_ALLOW_CROSS_CHARGE_FLAG      =>task_record.allow_cross_charge_flag,
                    X_PROJECT_RATE_DATE            =>task_record.project_rate_date,
                    X_PROJECT_RATE_TYPE            =>task_record.project_rate_type,
                    X_CC_PROCESS_LABOR_FLAG        =>task_record.cc_process_labor_flag,
                    X_LABOR_TP_SCHEDULE_ID         =>task_record.labor_tp_schedule_id,
                    X_LABOR_TP_FIXED_DATE          =>task_record.labor_tp_fixed_date,
                    X_CC_PROCESS_NL_FLAG           =>task_record.cc_process_nl_flag,
                    X_NL_TP_SCHEDULE_ID            =>task_record.nl_tp_schedule_id,
                    X_NL_TP_FIXED_DATE             =>task_record.nl_tp_fixed_date,
                    X_RECEIVE_PROJECT_INVOICE_FLAG =>task_record.receive_project_invoice_flag,
                    X_WORK_TYPE_ID                 =>task_record.work_type_id,
                    X_JOB_BILL_RATE_SCHEDULE_ID    =>task_record.job_bill_rate_schedule_id,
                    X_emp_bill_rate_schedule_id    =>task_record.emp_bill_rate_schedule_id,
                    X_taskfunc_cost_rate_type      =>task_record.taskfunc_cost_rate_type,
                    X_taskfunc_cost_rate_date      =>task_record.taskfunc_cost_rate_date,
                    X_non_lab_std_bill_rt_sch_id   =>task_record.non_lab_std_bill_rt_sch_id,
                    X_labor_disc_reason_code       =>task_record.labor_disc_reason_code,
                    X_non_labor_disc_reason_code   =>task_record.non_labor_disc_reason_code,
                    x_retirement_cost_flag         =>task_record.retirement_cost_flag,
                    x_cint_eligible_flag           =>task_record.cint_eligible_flag,
                    X_CINT_STOP_DATE               =>task_record.cint_stop_date,
                    X_GEN_ETC_SRC_CODE             =>task_record.gen_etc_source_code
                    );

        PA_PROJ_TASK_STRUC_PUB.UPDATE_TASK_STRUCTURE2(
                    p_calling_module	              =>'FORMS',
                    p_task_id                      =>task_record.task_id,
                    p_task_number	              =>task_record.task_number,
                    p_task_name	                =>l_task_name1,
                    P_TASK_DESCRIPTION	        =>TASK_RECORD.DESCRIPTION,
                    p_task_manager_id	        =>l_task_manager_person_id,--Updated Value
                    p_carrying_out_organization_id =>task_record.carrying_out_organization_id,
                    p_pm_product_code	      =>task_record.pm_product_code,
                    p_pm_task_reference	      =>task_record.pm_task_reference,
                    p_location_id                  =>task_struc_record.location_id,
                    p_ref_task_id                  =>NULL,
                    p_project_id                   =>task_struc_record.project_id,
                    x_msg_count                    =>l_msg_count,
                    x_msg_data                     =>l_msg_data,
                    x_return_status                =>l_return_status
              );
COMMIT;

IF l_return_status <> 'S'
THEN
FOR n IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (n, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (n) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line
( 'Error: API Error while updating the Task: '
|| l_output
);
COMMIT;
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('Sucessfully Update the task');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Other Error in Project: ' || SQLERRM);
END;

Understanding the Workflow Definition (Set up) Tables


Oracle workflow is also a database application as many other application of Oracle, which means that it also utilizes database tables as the basis of its operation. Behind its very pleasant and user-friendly GUI, It’s the database tables which store every piece of information regarding the attributes, functions, process, messages you create while designing a workflow. If you really want to know Workflow and discover how it works, you have to understand its table structures.

In this article, I have covered the tables which got affected, when you create or modify a workflow process. However it doesn’t include the tables which capture information at run time when you run a workflow. I have taken the ‘PO Approval Workflow’ (POAPPRV) for example purpose.

1] 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. Also check the view WF_ITEM_TYPES_VL.

 

SELECT * FROM WF_ITEM_TYPES WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_TL WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_VL WHERE NAME='POAPPRV';

2] 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:

  • ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.
  • NAME: Internal name of the attribute. A mandatory field.
  • SEQUENCE: Order of the attribute within the message
  • 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.

 

SELECT * FROM WF_ITEM_ATTRIBUTES WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';

3] 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.

The Translation table is WF_ACTIVITIES_TL and the related view is WF_ACTIVITIES_VL.

SELECT * FROM WF_ACTIVITIES WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='FIND_APPROVER';

4] 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.

The Translation table is WF_ACTIVITY_ATTRIBUTES_TL and the related view is WF_ACTIVITY_ATTRIBUTES_VL.

SELECT *
FROM WF_ACTIVITY_ATTRIBUTES
WHERE ACTIVITY_ITEM_TYPE='POAPPRV'
AND ACTIVITY_NAME       ='GET_NOTIFICATION_ATTRIBUTE'
AND ACTIVITY_VERSION    =
  (SELECT VERSION
  FROM WF_ACTIVITIES
  WHERE ITEM_TYPE='POAPPRV'
  AND NAME       ='GET_NOTIFICATION_ATTRIBUTE'
  AND TRUNC(SYSDATE) BETWEEN TRUNC(BEGIN_DATE) AND TRUNC(NVL(END_DATE,SYSDATE))
  );

5] 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.

SELECT * FROM WF_ACTIVITY_ATTR_VALUES WHERE NAME='NTF_USER_NAME';

6] 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.

SELECT * FROM WF_MESSAGES
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

SELECT * FROM WF_MESSAGES_TL
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

7] 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.

SELECT * FROM WF_MESSAGE_ATTRIBUTES
WHERE MESSAGE_TYPE='POAPPRV'
AND MESSAGE_NAME  ='NOTIFY_BUYER'
AND NAME          ='BUYER_DISPLAY_NAME';

8] 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.

SELECT * FROM WF_PROCESS_ACTIVITIES
WHERE PROCESS_ITEM_TYPE='POAPPRV'
AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS';

9] 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.

SELECT *
FROM WF_ACTIVITY_TRANSITIONS
WHERE FROM_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='START'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='START'
    )
  )
AND TO_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
    )
  );

10] 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.

SELECT *
FROM WF_LOOKUP_TYPES_TL
WHERE ITEM_TYPE='POAPPRV'
AND LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';

SELECT * FROM WF_LOOKUPS_TL
WHERE LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';