Oracle Applications Standard Forms Code Path in Unix Server

Path for Standard forms given in Oracle Apps:

/u002/appl/XXX/12.1/XXX/appl/au/12.0.0/forms/US

Form Personalization

Changing the color of an Item in Forms using Forms Personalization

On request here are the steps to change the Font colors of an item using Forms Personalization.
I am using Item Screen for an example and displaying Item number in Red Color.
This is how form looks before customization. Note that Item number is displayed in its default color.

Now lets change the color. Select Help->Diagnostics->Custom Code->Personalize to open the forms personalization screen.
At this point of time there is no condition specified and the Item number is displayed in red in all the cases. So the trigger is written in WHEN-NEW_FORMS-INSTANCE

Click on Actions tab and enter as shown in the screen shot

Save your changes and re-open and query for an item


Note the change in the font color of Item Number.

The Foreground color used in above example is r255g0b0, which can be explained as follows

Any Color is made using combination of Red(r), Green(g) and Blue(b) color with a value ranging from 0 to 255. To create a red color give the value of r to maximum 255 and leave g and b to zero.
Similarly green color can be defined as r0g255b0

List of Tables for Forms Personalization

Below is the list of tables that are populated when any Forms Personalization is done

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

Click here For FNDLOAD on forms personalization

Hide a Button in the form using Forms Personlization

In one of the forum, there was a requirement to allow bookings only by certain user.
This can be achieved using forms personalization for users on 11.5.10 or higher.
Open the sales Order Form and click on Help->Diagnostics->Custom Code->Personalize to open the forms personlization screen.
(Click on the image to zoom it)






P.S.
In the example above I have hardcoded user name which is not the advisable way.
The better approach could be to create a custom profile and assign a value to the custom profile at user level. Based on the value entered the button can be enabled or disabled.

Note: The same result can also be achieved by using custom.pll

Case Insensitive Query in Forms

One of the inconsistency Oracle Apps Forms is that some forms are case sensitive and some are not. For e.g. If we query the create concurrent request form these are case insensitive whereas the create DFF form is case sensitive.
Below I discuss how do we know whether the form is case insensitive and how to change its property using forms personalization.

As an example I have taken the DFF form.
Step to know if form is case insensitive.
Open the form and click on the field. Select Help-->Diagnostic -->Properties-->Item
In the property field, select case insensitive query. If the value is FALSE, then you will know that Case Insensitive Query is not possible on this field.

For changing the property select forms personalization. Select Help-->Diagnostics-->Custom Code -->Personalize

Enter any meaninful text for description. Click on Actions Tab.
Select the Target Object for which the property needs to be changed then select case_insensitive_query property name and change the value to TRUE. Save the changes.
Close the DFF form and check if the changes are saved.


Thats its ... now the title field is no longer a case sensitive field. Query by entering text in lower case.
Hurray... there we go .. the case insensitive query is now working fine.

Profile Value at any level

Below is another handy query to get profile Values at all levels.
The query prompts for 2 parameters, the profile name and the level name that could be application/responsibility/username. Its a wild search so will retrieve data if any of parameter is passed a blank.

SELECT   distinct pot.user_profile_option_name Profile
     , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) Value
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , '????'
              ) Level_identifier
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, d.user_name
             , '-'
              ) Level_Name
FROM     applsys.fnd_application_tl e
     , applsys.fnd_user d
     , applsys.fnd_responsibility_tl c
     , applsys.fnd_profile_option_values a
     , applsys.fnd_profile_options b
     , applsys.fnd_profile_options_tl pot
WHERE    1=1
AND      UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')
AND      pot.profile_option_name = b.profile_option_name
AND      b.application_id = a.application_id(+)
AND      b.profile_option_id = a.profile_option_id(+)
AND      a.level_value = c.responsibility_id(+)
AND      a.level_value = d.user_id(+)
AND      a.level_value = e.application_id(+)
AND      (   UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')
        OR UPPER (c.responsibility_name) LIKE UPPER ('%&&appname_respname_username%')
        OR UPPER (d.user_name) LIKE UPPER ('%&&appname_respname_username%')
       )
ORDER BY Profile
     , Level_identifier
     , Level_name
     , Value