Search

OracleDollars

Forms Personalization and Custom.PLL


What is this?

Oracle Supplied forms are the seeded forms and have a standard functionality attached to them. Whenever there is a need to change the functionality or change the look and feel of the forms, we use Forms Personalization or Custom.PLL.

Custom.PLL or Forms personalization is used to alter the standard functionality of an Oracle Seeded form without affecting the base form and without altering its coding. In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.


Advantages of Forms Personalization over Custom.PLL

Forms personalization can be used by an user with limited PL/SQL knowledge.
Changes take place immediately on reopening the form.
Anything which can be done using Custom.PLL can be done using Forms Personalization also.
Personalizations are stored in base tables related to Form Personalization.


Restrictions of Custom.PLL and Forms Personalization:

It is not possible to create any new item in the Standard form using Custom.PLL or Forms Personalization.


How to

Normally a user with some basic knowledge of Oracle Forms and PL/SQL is allowed to modify a standard form. This can be achieved by setting the profile Option

FND: Diagnostics (Set to Yes at User Level)
Hide Diagnostics (Set to NO by default)

This enables the diagnostics menu when the particular user logs in and provides access to the personalization form. Once the form is open, the user can make any modifications for the specific form and then save and check the effect.
Custom.PLL is mainly used by the developers who have a vast knowledge of PL/SQL and can attach any logic using PL/SQL which in-turn will implement the business logic when the form opens. To achieve this, the developer has to first edit Custom.PLL and then add the code in it, compile and save it in the UNIX box and then deploy it in the applications. The changes will be reflected only the next time when the application is restarted.


Some typical uses


  • Changing the property of an Item. It can be any property associated with the item.
  • Calling a different form and passing a value from the calling form to the called form.
  • Addition of special menus apart from the standard menu.
  • Displaying custom messages for debugging purposes.

Feel free to post your comments and your queries.


$FLEX$ and $PROFILES$


What is $FLEX$ and $PROFILES$?

$FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold values at runtime.
Every Oracle Apps technical consultant will be familiar with the term $FLEX$ and $PROFILES$. Whenever we are working with value sets, we will be using both of these for modifying the data fetched, for basing the values of a parameter on the other parameter and also fetching the Profile Option values.
To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.

Where is it used?

Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.

Syntax:

:$FLEX$.previous_value_set_name
Important:

  • $FLEX$ must always be in capitals.
  • A ‘:’ must precede the declaration of $FLEX$.
  • The previous value set name must have already been assigned and saved on a different parameter.
:$PROFILES$.Profile_option_name

Important:
  • $PROFILES$ must be always in capitals.
  • ‘:’ must always precede the declaration.
  • Profile option name is the Profile Name and not to be confused with the User profile Name.

Some use of the Special Variables are as below:

Pre-Requisites:

Created an Executable and a concurrent program which is registered in the Application Object Library. The Query for the report is as below:

SELECT e.ename, e.empno, e.sal, e.hiredate, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE d.deptno = :x_deptno;
The name of the concurrent program is taken as “XX_Checking_flex_and_profile_use”


Scenario 1:

Changing the value of Parameter B based on the Parameter A:
In this, we created two value sets and they are: XX_SCENARIO_1 and XX_Sub1

Steps:

Create the value set XX_SCENARIO_1 using the validation type as ‘Table’.
Create the second value set XX_Sub1 using the validation type as ‘Table’ and in the where clause field specify the following code:
where deptno <= :$FLEX$.XX_SCENARIO_1

Working:

To check the working of this concurrent program, lets submit this


In the picture here, the First parameter contains no value, so the second parameter is disabled as the WHERE clause in value set is equated to NULL, making the field disabled.



When a value is selected in the first parameter, the second parameter gets enabled and when the LOV is clicked, it shows the departments which are in department number 20 and below it, as we have specified <= in the where clause.



Scenario 2:

Use of :$FLEX$ in the default type option of a Parameter form

Steps:

The query used in the Default Value test field is as below: Select dname from dept where deptno =:$FLEX$.XX_SCENARIO_1
Ensure that the default value for the field Department Number is given as SQL Statement.
Select deptno from dept where deptno=20

Working:

Lets submit the concurrent program and check.

Since the default value was given as a SQL statement, the result of that is shown in the first parameter and the second parameter has the value based on the first parameter as specified in the SQL statement.


Scenario 3:

Use of $PROFILES$ in where clause and Default Value. It is done in the same way as is used in $FLEX$.



Modifying the where clause using $PROFILES$ returns Successful upon validation.





Scenario 4:

Use of Parameter name instead of a value set name in the $FLEX$ Special Variable.
Where Clause of the value set of table type using the parameter name.

Reason:
When we provide the name of the value set in $FLEX$ then we will be able to change the value of the parameter which is dependent on it. If the value set is used by more than one parameter then it becomes difficult to get the exact value in the dependent parameter as it will fetch the values based on the last used value of the value set. It could have been last used by the first parameter or any other parameter in the form. To avoid this kind of confusion, the alternative is to provide the name of the Parameter itself in the $FLEX$ variable. No matter how many times the value set is used, if we provide the parameter name, we will get the dependent parameter value based on its parent parameter only. Let me explain this using the pictures below:

Where Clause of the value set of table type using the parameter
name.



The first and third parameter are based on value set names and the second and fourth parameter are based on parameter name. Since the second parameter is not initialized, the third parameter (value set based )is also not initialized.




Since the latest value is from Second parameter, hence the value in third is based on the second parameter. The third value is based on the value of the second parameter and is irrespective of the value set. Shown in the next picture.





Here the third parameter takes the latest value of the value set, hence shows the department name corresponding to that and the fourth parameter values are dependent on the value of the second parameter.







These were some of the basic uses of $FLEX$ and $PROFILES$. Feel free to post your queries and comments on this topic.

Enabling Descriptive Flexfield in Oracle Applications


Summary:
The key for enabling a Descriptive Flexfield is to identify the Base table of the related form and then use the table name to query the Descriptive Flexfield Title. Once the title of the Descriptive Flexfield is obtained, then we just have to Query the name in the Application Developer responsibility and then add the desired columns. This will enable the Descriptive Flexfield.

Getting Started:
Lets enable the Descriptive Flexfield of Purchase Order form. The initial screen looks as shown below and the DFF field is Disabled.



Get the name of the view from which this form is created using the Tools=>Record History option




Use the utility TOAD to find the base table of this view. We will find that the base table involved here is PO_HEADERS_ALL.

Now, switch to Application Developer Responsibility and open the form for registering the Flexfields.

Navigation:

Application Developer => Flexfields => Descriptive => Register

The window which opens is as shown below:





Now, query this form by providing the values for Application as ‘Purchasing’ and the Table Name as ‘PO_HEADERS_ALL’. The result will be as shown below:



Once the window is queried, then select the Title ‘PO Headers’ from this window and then navigate to
Application Developer => Flexfield=> Descriptive => Segments



Now, query this window using the title and the application name. The window is as shown below:




Now, uncheck the freeze flexfield definition and then add the context field values as per the need. This is illustrated in the screen below.





Here, I have added A and B as the code and ‘Global Data Elements’ is default and is always enabled.

Now, select the code and then add columns to it. It is shown as below



When clicked on Segments, we will get the window as below. We then specify the columns which will get displayed when this code is selected.



If desired, we can attach the value set also to restrict the user from entering any garbage value.

Now, save this and follow the same steps for enabling the other code(B) also and also ensure that to show the difference between code A and code B, enable three columns in Code B.

Now, the last step is to specify a default value to the Context Field. I am providing here a reference field as TYPE_LOOKUP_CODE. Ensure that the checkboxes are checked. The screen is as below.



Once this is done, we then have to compile the flexfield and save the window. For compiling the Flexfield definition, we need to first save the window and then check the freeze flexfield checkbox and then click on Compile.



Now, Login to Purchasing responsibility and then open the Purchase Order form.



This window shows that the DFF field is now enabled. When we click on the DFF window, we will be getting a screen as below



When the LOV is clicked, we get the values populated by the reference column which we gave as ‘TYPE_LOOKUP_CODE'.It is shown below




Now, based on the value selected we will get the corresponding columns which is shown in the subsequent screen.



Hence, this is how a flexfield works. When the data is saved, it will be saved in the base table on the corresponding Attribute columns as specified in the Application developer responsibility.

Your comments and queries are always appreciated.




Flexfields in Oracle Applications


What is a Flexfield?
As the name suggests, a Flexfield is a ‘flexible field’. A flexfield is a field made up of segments. Segments are nothing but columns of the base table. Each segment has a name that can be assigned and a set of valid values. There are two types of Flexfields in Oracle Applications and they are called as
• Key Flexfield
• Descriptive Flexfield
With both of these flexfields, users get the ability to customize their application to match their business needs without programming.


When and where are these flexfields used?
Descriptive Flexfields are used in places whenever the user feels a need of extra information apart from the Oracle supplied standard information in the forms.
Key Flexfields are used whenever there is a need for storing Key information related to the organization.

What is the difference between Key and Descriptive flexfields?

  • Key flexfield is used to store the key information about an organization whereas Descriptive flexfields are used to store any additional related information.
  • Key Flexfield values are stored in Segment columns in the base table whereas descriptive flexfield values are stored in the Attribute columns of the base table.
  • Key Flexfields are non-updatable and mostly have only restricted values as input (Attached Value sets) wheras a the fields in Descriptive Flexfield are updatable.
  • Key flexfields do not have any context sensitive segments whereas Descriptive flexfields can be made into context sensitive fields.
  • Key Flexfields are the mandatory fields whereas descriptive flexfields are not mandatory by default but can be made mandatory.

What are the Base Tables involved in Flexfields creation?
The tables involved are
FND_FLEX_VALUES_TL, FND_ID_FLEXS, FND_FLEX_VALUES, FND_FLEX_VALUE_SETS, FND_ID_FLEX_STRUCTURES

Query to find the number of oracle Supplied key flexfields in each application

SELECT fa.application_name application, COUNT (fif.application_id) flexfield_count
FROM fnd_id_flexs fif, fnd_application_tl fa
WHERE fif.application_id = fa.application_id
GROUP BY fa.application_name

A sample output of this query is as below:

These were some of the basics involved with Flexfields in Oracle Applications. The next post will be on how to enable a Descriptive Flexfield in Oracle Applications.

Please feel free to post your comments or queries




Analytical Functions in SQL


Analytical Functions:
Introduced in Oracle 8i and are used for various purposes like calculating a running total, computing a percentage within a group, compute a moving average and many more in SELECT statements. Analytical functions add extensions to SQL language and not only make it easier to code but also makes the query execute faster.

How Analytic Functions Work?
It works on a group of records and it actually computes the aggregate value on a group of rows and returns the result. The main difference between Aggregate functions and Analytical functions is that, analytical functions return multiple rows for each group.

Important:
  • Analytical functions can only be used in a SELECT clause and ORDER BY Clause.
  • Difference between Analytical Function and Aggregate Function is that Aggregate functions return one row from a group of data’s whereas Analytical functions return multiple rows in a group of data.

Syntax:

Analytic-Function(Argument,Argument,...)
OVER (
Query-Partition-Clause
Order-By-Clause
Windowing-Clause
)

Some of the Analytical functions are as below:

AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

Some examples of Analytical functions in SQL Queries

a) Calculating a Running total:

Query:

SELECT ename, deptno, sal, SUM (sal) OVER (ORDER BY sal) "Running Total"
FROM emp

Output:


Note: One thing to note here is that, whenever ‘SAL’ is same for the same dept, the running total displayed here is a sum of both the salaries.

b) Retrieving the first and Last values in a group:

Query:
SELECT ename, sal, deptno, first_value(ename) over(Partition by deptno) FIRST, Last_value(ename) over(Partition by deptno) Last FROM emp

Output:



The above query gives the first and last values in a group.


c) Calculating a running average:

Query:

SELECT deptno, sal, AVG (sal) OVER (ORDER BY sal DESC) "Avg Sal"
FROM emp

Output:




d) TOP ‘n’ Analysis:

Query:

SELECT * FROM (SELECT ename, sal, ROW_NUMBER () OVER (ORDER BY sal DESC) RANK FROM emp) WHERE RANK <= 3

Output:



e) Accessing rows around a current row:

Query:

SELECT deptno, ename, sal, LAG (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LAG",
LEAD (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LEAD"
FROM emp

Output:



This query gives the LAG and LEAD values of an organization. LAG means the value previous to the current value and LEAD means the value next to the current value.
The syntax can be explained as below:


LAG(Column_name, Offset, Default_value)

LEAD(Column_name, Offset, Default_value)

Where:
Column_name is the name of the column
Offset defines on how many rows need to be skipped
Default value places a user defined value when there are now values in the offset range.

Windowing Clauses:
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. The default window is an anchored window that simply starts at the first row of a group and continues to the current row.

The various keywords used here are:
ROWS n PRECEDING, RANGE n PRECEDING

Note: RANGE option can only be used for number and Date datatypes.

Example:

1) RANGE window clause:

Query:

SELECT empno, sal, COUNT (*) OVER (ORDER BY sal ASC RANGE 100 PRECEDING) "counter"
FROM emp
ORDER BY empno

Output:



Explanation:
To understand the above query, we need to first look at the clause ‘RANGE 100 PRECEDING’, This creates a range from the current row for sal which is in the range of current value to (current value-100) and counts the salaries which falls within this range.
If we take the first row(1600) the count for this is 2. This is because there exists a value 1500, which is exactly at the start of the range and the number itself (1600), hence the count 2. Similarly, 1250 has a count 2 because of the existence of another value 1250 and 2975 has a count of only 1 because the value prior to this is 2850 which is more than 100 points below it and doesn’t fall in the range created. The other counts and the values follow the same criterion.


2) ROW window Clauses:

Query:

SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM (sal) OVER (ORDER BY ename ROWS 2 PRECEDING) "Sliding Total"
FROM emp

Output:



Explanation:
This is a row type windowing clause and the important thing to note here is the clause ‘ROWS 2 PRECEDING’. What this implies here is that, the total which is calculated here is based on the previous two rows of the current columns.
For instance, take the first row, the ‘Sliding total’ is 1100, same as the value of ‘Sal’ and the second row value for ‘Sliding total’ stands as the sum of the first and the second row. This is because, the second row does not have two rows preceding it, hence the second value is taken from the immediate column. If we take the case of the third row, the ‘Sliding total’ value is the sum of the third row, first and the second row, that is (2850+1100+1600=5550) and lets take the fourth row, the value of ‘Sliding total’ here is the sum of the immediate two rows preceding it and that is (2450+2850+1600=6900) and so on.

Hence, this concludes a small introduction of Analytical Functions in SQL. Feel free to post any comments or queries on the above topic.