Value Sets in Oracle Apps

Oracle Application Object Library uses values, value sets and validation tables as important components of key flexfields, descriptive flexfields, and Standard Request Submission.

Value sets are a list of values used for validation of data. 

1. Define a Value set

Navigation:- System Administrator --> Application --> Validation --> Set

2. Enter a unique name for this value set   

If you plan to refer to your value set name in a WHERE clause for a validation table value set, you should use only letters, numbers, and underscores (_) in your value set name. 

• You should not include any spaces, quotes, or other special characters in your value set name.
• Do not use the string $FLEX$ as part of your value set name. 

Note that validation tables are case-sensitive for value set names you use in validation table WHERE clauses.

Suggestion: Make your value set names contain only one case (either upper or lower case) to avoid case-sensitivity problems.

3.  Specify the List Type for your value set.

• Poplist - fewer than 10 values expected
  
• List of Values - between 10 and 200 values expected
  
• Long List of Values - more than 200 values expected

4.  Specify the Security Type 

• No Security - All security is disabled for this value set
• Hierarchical Security :- any security rule that applies to a parent value also applies to its child values.
• Non-Hierarchical Security :-  a security rule that applies to a parent value does not "cascade down" to its child values. 

5. Enter the type of format 

     Valid choices include: Char, Date, DateTime, Number, Standard Date, Standard DateTime, and Time.

6. Select your validation type

    Validation type 'None' in Value sets

    Validation type 'Independent' in Value sets

    Validation type 'Dependent' in Value sets

    Validation type 'Table' in Value sets

    Validation type 'Special' in Value sets

    Validation type 'Translatable Independent' in Value sets

    Validation type 'Translatable Dependent' in Value sets

Using $FLEX$ in Value sets
Using $PROFILES$ in Value sets


Which Oracle table store Value sets and underline information

• FND_FLEX_VALUE_HIERARCHIES
• FND_FLEX_VALUE_SETS
• FND_ID_FLEX_SEGMENTS
• FND_FLEX_VALUE_NORM_HIERARCHY
• FND_FLEX_HIERARCHIES
• FND_FLEX_VALUE
• FND_FLEX_VALIDATION_EVENTS
• FND_FLEX_VALUE_RULE_LINES
• FND_FLEX_VALUE_RULE
• FND_FLEX_VALUE_RULE_USAGE
• FND_ RESPONSIBILITY
• FND_TABLES
• FND_FLEX_VALIDATION_TABLES

Do we have any restriction on value set?

Yes, here are some listed one:

• Table Validated Value Sets
  • We cannot use table-validated id value sets for any accounting flexfield or any other key flexfields.
  • We cannot use :$FLEX$, :$PROFILES$ in table name, value and id of table validated value sets.
  • We cannot use DISTINCT clause in any of the column fields or in the WHERE clause of a table validate value set.
  • In an id value set, the value can be non-unique but id should be unique. In a non-id value set, value should be unique.
  • We can only use columns selected for the table-validated value set must be of type NUMBER, DATE or VARCHAR2.
  • Support for SQL expression in columns of Table Validated value sets will be obsolete in future release.
• Translatable Independent and Translatable Dependent Value-sets
  • The Numbers Only and Uppercase Only option cannot be used.
  • Must have "Char" format type.
• Special/Pair value-sets
  • Special/Pair value sets are user-exit value sets . PL/SQL APIs will not be able to validate them.

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:


1. Independent to Dependent
2. Dependent to Independent
3. None to Dependent
4. Dependent to Table
5. Table to Dependent
6. Translatable Independent to Translatable Dependent
7. Translatable Dependent to Translatable Independent
8. None to Translatable Dependent
9. Translatable Dependent to Table
10. Table to Translatable Dependent


How to create XML Report in Oracle

Leave a comment for any query.
 

Validation Type 'Table' in Value sets in Oracle

Definition:- A table-validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. 

You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set.

Lets create a table validated value set.

Press "Edit Information" button to add details about table/view/query used for table validation.

Table Application:- Mention the relevant application to which table belongs.
Table Name:- You may mention the table name, view or query in this field.

To enter query, enter it in this form i.e. (select employee_number en, full_name fn from per_all_people_f). In my point of view, if data is not fully accessible through table, use a view rather than query.

Because once you entered the query and saved the value set, and applied it to many fields, you will not be able to edit the value sets' query. You will have to remove value set from all the fields.

Value:- Table column values which are shown for selection on the field.
Meaning:- Displays the value outside field, once any selection is made on the field.
ID:- The value which is returned to be saved in the table, or is returned in the report.

Where/Order by:- Name defines itself. To put where clause or order by clause. But you need to follow the order.

Additional Columns:- Any column which is to be shown parallel to one we select in Value field.

NOTE:
if you want to show multiple columns in parallel, keep the meaning field empty and place column names separated by comma in Additional Columns field and also specify their width.

If you specify only the SQL fragment but no alias or width, your column does not show up.

Specify Additional Columns in this format i.e.

column_name_1 "Column 1 Title"(width), column_name_2 "Column 2 Title"(width), ... 

Test and save the value set.

After i applied the value set to a field, result was like shown below.

Also see: Dependent Value sets in Oracle

Value Sets in Oracle Apps

Leave a comment for any query.

Validation Type 'Dependent' in value sets in Oracle

Definition: A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the flexfield structure.

Let's create a Dependent Value set as shown in screenshot below

Now click Edit Information button and select independent value set as i created in an earlier post.

See : Independent Value Set

What you need to do is, to enter values against each parent value i.e XX_INDEPENDENT value set we selected.

After applying the value set to a field, result will be shown as follows.

Validation Type 'None' in Value Sets in Oracle Apps

Leave a comment for any query.

Validation Type 'Independent' in Value Sets in Oracle

Definition: An Independent value set provides a predefined list of values for a segment. These values can have an associated description.

Let's create a value set of Validation Type "Independent"

As you can see in the screenshot, that i selected the List Type : Poplist just for understanding that how Poplist is shown.

Because it is a independent value set, so you need to enter some fixed values against it.

Navigation:- System Administrator --> Application --> Validation --> Values

After the value set is attached to a filed, result is shown below.

Leave a comment for any query.

Validation Type 'None' in Value Sets in Oracle Apps

Definition: You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules.

I have created a value set of Validation type "None". 

As you can see i provided the format type "Number" and Validation Type "None" while creating the value set.

After the value set is created, i attached it to filed to demonstrate its functionality. According to functionality, filed should only allow Numeric values.

NOTE:

As you can see, when i tried to enter a Character value in the field, system thrown an error.

APP-FND-01260 Valid Character are 0-9

So when i entered the Numeric value, system did not throw any error.

So "None" Validation is applied successfully.

Leave a comment for any query.

How to integrate an XML Report with Oracle

Register an RTF File with Oracle APPS:

While integrating a report in System Administrator, set the Output Format to XML

• Go to responsibility XML Publisher Administration

• Press the button “Create Data Definition”

Give the Name and code same as RDF name of the report.  Select the application accordingly e.g. Human Resources.

• Once Data definition is created, you need to create a template after it.

Provide the name and code as reports’ RDF name. Select the data definition as you defined while creating ‘Data Definition’.

Default Output Type: Select the default Output type. E.g. EXCEL if you want to show your report in excel format.

Template File:  Browse and select the RTF file in which you created the format.

Language: Select the language accordingly.

Your feedback is much appreciated.

Value Sets in Oracle Apps

Leave a comment for any query.

How to create XML Report in Oracle

Installing BI Publisher:

Follow the path to download the BI Publisher

Download BI Publisher

When BI Publisher is successfully installed, an additional ribbon is shown in Microsoft Word as 

Add Ins.

Using BI Publisher:

1. Create an XML file from report builder.
2.  You need to run the complete report wizard before creating XML file.
3.  Xml file can be created by following the navigation path as follows.
4. File -> Generate to File -> Choose Xml (or any other format as required).

·         Open Microsoft Word. Go to the ribbon ADD INS.

• Load the XML file from the tab Data -> Load Data

• Once data file is successfully loaded, run the Table wizard from the tab Insert, or you can manually create the format.

•  A wizard will start. Here are its screenshots. 

         Select Table and Click Next

          Click Next

• Select the fields to show in your report

• Define any kind of grouping here

• Define any kind of sorting here

• Edit the Labels of the fields

• When you click Finish, all the fields, which you selected to display in your report, will be displayed as shown in Screenshot.

• Apply the repeating frames or any other conditional formatting as you do in your RDF report.

• Save the report in ‘Rich Text Format’ Format

• Another approach is to create Xml format manually, for which you need to insert table, fields, repeating frames etc without any wizard. 

How to integrate XML Report with Oracle

I hope you like the post. if you like the post, kindly provide feedback.

Leave a comment for any query.

Employee Payment Methods in Oracle Payroll

NOTE: The table structure and procedure may need to be updated according to your configuration.

Number of Segments on Bank Details KFF can be different for your configuration, kindly update it accordingly.

Here are  the details for uploading Employee' Payment methods.

TABLE

CREATE TABLE APPS.DEV_PERSONAL_PAY_METHOD

(

  LINE_ID            NUMBER,

  EMPLOYEE_NUMBER    VARCHAR2(100 BYTE),

  PAY_METHOD_NAME    VARCHAR2(240 BYTE),

  PERCENT            NUMBER,

  BANK_CODE          VARCHAR2(240 BYTE),

  BANK_NAME          VARCHAR2(240 BYTE),

  BRANCH_NAME        VARCHAR2(240 BYTE),

  ACCT_TITLE         VARCHAR2(240 BYTE),

  ACCT_NUMBER        VARCHAR2(240 BYTE),

  BANK_ADDRESS       VARCHAR2(240 BYTE),

  PAYMENT_MODE       VARCHAR2(240 BYTE),

  PER_PAY_METHOD_ID  NUMBER,

  EXT_ACCT_ID        NUMBER,

  OBJ_VER_NUMBER     NUMBER,

  EFFECTIVE_DATE     DATE,

  EFF_START_DATE     DATE,

  EFF_END_DATE       DATE,

  COMMENT_ID         NUMBER,

  PROCESS_FLAG       CHAR(1 BYTE),

  ERROR_DESCRIPTION  VARCHAR2(240 BYTE),

  PERCENTAGE         NUMBER(20),

  PRIORITY           NUMBER(20),

  ACCOUNT_TYPE       VARCHAR2(20 BYTE),

  PAYMENT_TYPE       VARCHAR2(50 BYTE)

)

PROCEDURE

CREATE OR REPLACE PROCEDURE APPS.DEV_CREATE_ASG_PAYMENT_METHOD

IS

   CURSOR C_EMP_PM

   IS

      SELECT   LINE_ID, EMPLOYEE_NUMBER, PAY_METHOD_NAME, PERCENT,

               PAYMENT_TYPE, PAYMENT_MODE, BANK_NAME, BRANCH_NAME,

               ACCT_TITLE, ACCOUNT_TYPE, ACCT_NUMBER, PRIORITY,

               PER_PAY_METHOD_ID, EXT_ACCT_ID, OBJ_VER_NUMBER,

               EFFECTIVE_DATE, PROCESS_FLAG, ERROR_DESCRIPTION, BANK_ADDRESS,

               BANK_CODE

          FROM DEV_PERSONAL_PAY_METHOD

      --WHERE NVL (PROCESS_FLAG, 'N') <> 'Y' AND ERROR_DESCRIPTION IS NULL

      ORDER BY LINE_ID;

   LC_C_EMP_PM                    C_EMP_PM%ROWTYPE;

   L_PERSONAL_PAYMENT_METHOD_ID   NUMBER;

   L_ORG_PAYMENT_METHOD_ID        NUMBER;

   L_EXTERNAL_ACCOUNT_ID          NUMBER;

   L_OBJECT_VERSION_NUMBER        NUMBER;

   L_EFFECTIVE_START_DATE         DATE;

   L_EFFECTIVE_END_DATE           DATE;

   L_COMMENT_ID                   NUMBER;

   ERR_DESC                       VARCHAR2 (240);

   LV_TERRITORY_CODE              CHAR (2);

   LV_PM_FLAG                     CHAR (1);

   L_EMP_ASSG_ID                  NUMBER (20);

   L_PM_ID                        NUMBER;

   AS_EFFECTIVE_START_DATE        DATE;

   L_PAYMENT_TYPE_ID              NUMBER;

BEGIN

   LV_TERRITORY_CODE := 'PK';       -- CHANGE YOUR TERRITORY CODE ACCORDINGLY

   OPEN C_EMP_PM;

   LOOP

      FETCH C_EMP_PM

       INTO LC_C_EMP_PM;

      EXIT WHEN C_EMP_PM%NOTFOUND;

      BEGIN

---------------------------------------------------------------

-- EMPLOYEE ASSIGNMENT ID

---------------------------------------------------------------

         BEGIN

            SELECT PAAF.ASSIGNMENT_ID, PAAF.EFFECTIVE_START_DATE

              INTO L_EMP_ASSG_ID, AS_EFFECTIVE_START_DATE

              FROM PER_ALL_ASSIGNMENTS_F PAAF

             WHERE PAAF.PERSON_ID =

                      (SELECT DISTINCT PAPF.PERSON_ID

                                  FROM PER_ALL_PEOPLE_F PAPF

                                 WHERE PAPF.EMPLOYEE_NUMBER =

                                                   LC_C_EMP_PM.EMPLOYEE_NUMBER)

               AND PAAF.PRIMARY_FLAG = 'Y'

               AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE

                               AND PAAF.EFFECTIVE_END_DATE;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := 'EMPLOYEE NOT FOUND';

         END;

---------------------------------------------------------------

-- PERSONAL PAYMENT METHOD ID

---------------------------------------------------------------

/*         BEGIN

            SELECT PPPM.PERSONAL_PAYMENT_METHOD_ID,

                   PPPM.EFFECTIVE_START_DATE, PPPM.OBJECT_VERSION_NUMBER,

                   PPPM.EXTERNAL_ACCOUNT_ID

              INTO L_PERSONAL_PAYMENT_METHOD_ID,

                   L_EFFECTIVE_START_DATE, L_OBJECT_VERSION_NUMBER,

                   L_EXTERNAL_ACCOUNT_ID

              FROM PAY_PERSONAL_PAYMENT_METHODS_F PPPM

             WHERE SYSDATE BETWEEN PPPM.EFFECTIVE_START_DATE

                               AND PPPM.EFFECTIVE_END_DATE

               AND PPPM.ASSIGNMENT_ID = L_EMP_ASSG_ID;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := 'PERSONAL PAYMENT METHOD NOT FOUND OR ACTIVE';

         END;

*/

         BEGIN

            SELECT OPM.ORG_PAYMENT_METHOD_ID

              INTO L_ORG_PAYMENT_METHOD_ID

              FROM PAY_ORG_PAYMENT_METHODS_F OPM

             WHERE TRIM (OPM.ORG_PAYMENT_METHOD_NAME) =

                                            TRIM (LC_C_EMP_PM.PAY_METHOD_NAME)

--        UPPER (OPM.ORG_PAYMENT_METHOD_NAME) LIKE

--                                           UPPER (LC_C_EMP_PM.PAY_METHOD_NAME)

               AND SYSDATE BETWEEN OPM.EFFECTIVE_START_DATE

                               AND OPM.EFFECTIVE_END_DATE;

         EXCEPTION

            WHEN OTHERS

            THEN

               ERR_DESC := ERR_DESC || 'PAYMENT METHOD NOT FOUND OR ACTIVE.';

         END;

         /*

             IF LC_C_EMP_PM.PAYMENT_TYPE IS NOT NULL THEN

             BEGIN

                SELECT PAYMENT_TYPE_ID

                INTO L_PAYMENT_TYPE_ID

                FROM  PAY_PAYMENT_TYPES

                WHERE CURRENCY_CODE = 'PKR'

                AND PAYMENT_TYPE_NAME = LC_C_EMP_PM.PAYMENT_TYPE;

                EXCEPTION

                WHEN OTHERS

                THEN

                   ERR_DESC :=

                         ERR_DESC

                      || 'PAYMENT TYPE ERROR'

                      || SUBSTR (SQLERRM, 1, 100);

                   L_PAYMENT_TYPE_ID := NULL;

             END ;

             END IF;

           */

         IF AS_EFFECTIVE_START_DATE < LC_C_EMP_PM.EFFECTIVE_DATE

         THEN

            AS_EFFECTIVE_START_DATE := LC_C_EMP_PM.EFFECTIVE_DATE;

         END IF;

         -- IF ERR_DESC IS NULL

         -- THEN

         HR_PERSONAL_PAY_METHOD_API.CREATE_PERSONAL_PAY_METHOD

                (P_VALIDATE                        => FALSE,

                 P_EFFECTIVE_DATE                  => AS_EFFECTIVE_START_DATE,

                 --P_EFFECTIVE_DATE                => LC_C_EMP_PM.EFFECTIVE_DATE,

                 P_ASSIGNMENT_ID                   => L_EMP_ASSG_ID,

                 P_ORG_PAYMENT_METHOD_ID           => L_ORG_PAYMENT_METHOD_ID,

                 --   P_PAYMENT_TYPE_ID               => L_PAYMENT_TYPE_ID,

                 P_PERCENTAGE                      => LC_C_EMP_PM.PERCENT,

                                                                      --- 100,

                 P_PRIORITY                        => LC_C_EMP_PM.PRIORITY,

                                                                         ---1,

                 P_TERRITORY_CODE                  => 'PK',

                                                          --LV_TERRITORY_CODE,

                  --P_ATTRIBUTE_CATEGORY            => 'ABC STOP PAYMENT',

                 -- P_ATTRIBUTE2                    => 'NO',

                 P_SEGMENT1                        => LC_C_EMP_PM.ACCT_NUMBER,

                 P_SEGMENT2                        => LC_C_EMP_PM.ACCT_TITLE,

                 P_SEGMENT3                        => LC_C_EMP_PM.ACCOUNT_TYPE,

                 P_SEGMENT4                        => LC_C_EMP_PM.BANK_CODE,

                 P_SEGMENT5                        => LC_C_EMP_PM.BANK_NAME,

                 P_SEGMENT6                        => LC_C_EMP_PM.BRANCH_NAME,

                 P_SEGMENT7                        => LC_C_EMP_PM.BANK_ADDRESS,

                 P_PERSONAL_PAYMENT_METHOD_ID      => L_PERSONAL_PAYMENT_METHOD_ID,

                 P_EXTERNAL_ACCOUNT_ID             => L_EXTERNAL_ACCOUNT_ID,

                 P_OBJECT_VERSION_NUMBER           => L_OBJECT_VERSION_NUMBER,

                 P_EFFECTIVE_START_DATE            => L_EFFECTIVE_START_DATE,

                 P_EFFECTIVE_END_DATE              => L_EFFECTIVE_END_DATE,

                 P_COMMENT_ID                      => L_COMMENT_ID

                );

         -- END IF;

         IF L_PERSONAL_PAYMENT_METHOD_ID IS NOT NULL

         THEN

            LV_PM_FLAG := 'Y';

            ERR_DESC := 'NO ERROR';

         END IF;

         IF LV_PM_FLAG = 'Y'

         THEN

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = LV_PM_FLAG,

                   PER_PAY_METHOD_ID = L_PERSONAL_PAYMENT_METHOD_ID,

                   OBJ_VER_NUMBER = L_OBJECT_VERSION_NUMBER,

                   EFF_START_DATE = L_EFFECTIVE_START_DATE,

                   EFF_END_DATE = L_EFFECTIVE_END_DATE,

                   COMMENT_ID = L_COMMENT_ID,

                   EXT_ACCT_ID = L_EXTERNAL_ACCOUNT_ID

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

         ELSE

            ERR_DESC := '1.' || ERR_DESC || SQLERRM;

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = 'N'

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

         --   DBMS_OUTPUT.PUT_LINE('EMPLOYEE IS CREATED WITHOUT ANY WARNING');

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            LV_PM_FLAG := 'N';

            ERR_DESC := '2.' || ERR_DESC || SQLERRM;

            UPDATE DEV_PERSONAL_PAY_METHOD

               SET ERROR_DESCRIPTION = ERR_DESC,

                   PROCESS_FLAG = LV_PM_FLAG

             WHERE EMPLOYEE_NUMBER = LC_C_EMP_PM.EMPLOYEE_NUMBER

               AND LINE_ID = LC_C_EMP_PM.LINE_ID;

--            DBMS_OUTPUT.PUT_LINE (SQLERRM);

--COMMIT;

      END;

/*      DBMS_OUTPUT.PUT_LINE (   'PERSONAL PAY METHOD ID: '

                            || L_PERSONAL_PAYMENT_METHOD_ID

                            || '- EMP NO: '

                            || LC_C_EMP_PM.EMPLOYEE_NUMBER

                           );

*/

    /*  L_PERSONAL_PAYMENT_METHOD_ID := '';

      L_ORG_PAYMENT_METHOD_ID := '';

      L_EXTERNAL_ACCOUNT_ID := '';

      L_OBJECT_VERSION_NUMBER := '';

      L_EFFECTIVE_START_DATE := '';

      L_EFFECTIVE_END_DATE := '';

      L_COMMENT_ID := '';

      ERR_DESC := '';

      LV_PM_FLAG := '';

      L_EMP_ASSG_ID := '';

      L_PM_ID := '';

      AS_EFFECTIVE_START_DATE := '';*/

      L_PERSONAL_PAYMENT_METHOD_ID := NULL;

      L_ORG_PAYMENT_METHOD_ID := NULL;

      L_EXTERNAL_ACCOUNT_ID := NULL;

      L_OBJECT_VERSION_NUMBER := NULL;

      L_EFFECTIVE_START_DATE := NULL;

      L_EFFECTIVE_END_DATE := NULL;

      L_COMMENT_ID := NULL;

      ERR_DESC := NULL;

      LV_TERRITORY_CODE := NULL;

      LV_PM_FLAG := NULL;

      L_EMP_ASSG_ID := NULL;

      L_PM_ID := NULL;

      AS_EFFECTIVE_START_DATE := NULL;

      L_PAYMENT_TYPE_ID := NULL;

--COMMIT;

   END LOOP;

   CLOSE C_EMP_PM;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 250));

END;

/

HR_PERSONAL_PAY_METHOD_API Errors: ORA-20001: FLEX-DSQL EXCEPTION

If you come across this issue, this Note ID 974396.1 will help you resolve the issue.

if you could not understand anything, you may ask in the comment section.