Friday, October 7, 2011

Oracle Forms Short Cut Keys

Some users hate to use mouse buttons to work with oracle forms.
I will be mentioning here the often used short cut keys.
To Switch responsibility -> Alt + F then W
To close form                -> F4
To click the LOV          -> Ctrl + L
To Save                        -> Ctrl + S
To Copy the above
record(entire row)        ->Shift + F6
To Copy the above
record( Single Column) ->Shift + F5
To Click the buttons      -> Alt + (the letter underlined below the character)
To know more shortcut keys press ctrl + K in any Oracle form.
Enjoy






Tuesday, July 5, 2011

Flexfield

Define:
Flexfield, as the name suggests, is a flexible data field that your organization can customize to your business needs without programming. Combining multiple columns into single Value.. for example:( bank account, university registration number)
It allows you to enter multi–segment values such as part numbers, account numbers
So all the columns augmented into one column called Flexfield.

Why:
Flex field concept is basically implemented in all oracle standard tables becuase if you are looking for any customization or enhancement of a particular oracle form then you can do so by using the additional columns provided in each standard oracle table.

Types:
1) Key Flexfield
2) Descriptive Flexfield
      
Key Flexfield:
key flexfield is a field made up of segments, where each segment has both a value and a meaning.
I will take my university registration number as an example.

My University Registration number is 120-06-205-114
Segment
Value
Meaning
Segment 1
120
College Code
Segment 2
06
2006 Batch
Segment 3
205
Department ID ( IT Dept )
Segment 4
114
Roll no

Consider Bank account number
A complete bank account number may consists of various segments like the country code, area code, city code, branch code, account type, account number etc.
Same way Indian Railways PNR Number will have several segments to trace the particular traveler details.
Key Flexfields in Oracle Applications
The Oracle Applications products provide many key flexfields as integral parts of the products.
Here is a table listing all the key flexfields in Oracle Applications, ordered by the application that ”owns” the key flexfield. Note that other applications may also use a particular flexfield

Owner
Key Flexfield Name
Flexfield Code
Oracle Assets
Asset Key Flexfield
KEY#
Oracle Assets
Category Flexfield
CAT#
Oracle Assets
Location Flexfield
LOC#
Oracle General Ledger
Accounting Flexfield
GL#
Oracle Human Resources
Grade Flexfield
GRD
Oracle Human Resources
Job Flexfield
JOB
Oracle Human Resources
Personal Analysis Flexfield
PEA
Oracle Human Resources
Position Flexfield
POS
Oracle Human Resources
Soft Coded KeyFlexfield
SCL
Oracle Inventory
Account Aliases
MDSP
Oracle Inventory
Item Catalogs
MICG
Oracle Inventory
Item Categories
MCAT
Oracle Inventory
SalesOrders
RLOC
Oracle Inventory
Stock Locators
MTLL
Oracle Inventory
System Items
MSTK
Oracle Payroll
Bank Details KeyFlexField
BANK
Oracle Payroll
Cost Allocation Flexfield
COST
Oracle Payroll
People Group Flexfield
GRP
Oracle Receivables
Sales Tax Location Flexfield
MKTS
Oracle Receivables
Territory Flexfield
CT#
Oracle Service
Oracle Service Item Flexfield
SERV
Oracle Training Administration
Training Resources
RES

 Yet to complete this post....................

Monday, July 4, 2011

Value Set


Define:
Set of values

Why do we need value sets?
Answer:
You do not always want a user to enter junk free text into all the fields. Hence, Oracle Apps uses value set to validate that correct data is being entered in the fields in screen.
For Example: when you register in any websites it will ask for Country, City,
If the designer provides a free text box like this..

Then if user enters like this which is wrong and it will get stored into database.So to avoid this oracle provides a feature called Value sets .So designer can create a value set naming “Country”  and having values like India ,USA ,UK ,Australia , Srilanka etc
The navigation path: Application Developer :-  Application :- Validation :- Sets
 

Important Tables in Value Set:
1) select * from FND_FLEX_VALUE_SETS;
This table stores the value set names and each value set can be identified by a primary key FLEX_VALUE_SET_ID.
NOTE: Displayed only important columns :

FLEX_VALUE_SET_ID
FLEX_VALUE_SET_NAME
DESCRIPTION
WHO COLUMNS
VALIDATION TYPE
1017586
XX_COUNTRY
Country Names
************
++++++++++++




***********: WHO columns are used to track the information about who updated or inserted by the data against the tables.
  1. created_by ( User Who created the value set)
  2. creation_date ( value set creation date)
  3. last_update_date
  4. last_updated_by
  5. last_update_login
+++++++:Validation Types
1. None (not validated at all) ---------------------------‘N’
2. Independent --------------------------------------------- ‘I’
3. Dependent ----------------------------------------------- ‘D’
4. Table ------------------------------------------------------- ‘F’
5. Special (advanced) -------------------------------------‘U’
6. Pair (advanced) ------------------------------------------‘P’
7. Translatable Independent ---------------------------- ‘Y’
8. Translatable Dependent-------------------------------‘X’
To know more about this table refer

2) Select * from FND_FLEX_VALUES where flex_value_set_id=1017586

FLEX_VALUE_SET_ID
FLEX_VALUE_ID
FLEX_VALUE
WHO COLUMNS
1017586
20137
India
************
1017586
20138
China
1017586
20139
Australia

For each value there Is a primary key FLEX_VALUE_ID.

SELECT
 FFVS.FLEX_VALUE_SET_NAME, FFV.FLEX_VALUE
FROM
FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV
WHERE
FFVS.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID

To create a value set and adding values in it… please refer the below link

Flex value description can be found on FND_FLEX_VALUES_VL table.

TYPES OF VALIDATION

1)  None (not validated at all)
2)  Independent
  • Provides a predefined list of values.
  • Independent values are stored in an Oracle Application Object Library table.
Independent Value Set Creation
Responsibility sysadmin-> Application -> Validation -> ValueSet

This Form Information gets stored in FND_FLEX_VALUE_SETS.


Values Stored
Responsibility sysadmin-> Application -> Validation -> Values


 This Table Values stored in FND_FLEX_VALUES table.


 3) Dependent
  • Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
  • Must define your independent value set before you define the dependent value set that depends on it.
  • Advisable to create your independent values first.
  • Must create at least one dependent value for each independent value, or else it wont allow you to enter into that segment or field.
  • ex: Country, State, District Here district can be filtered by state and state can be filtered by country.
Scenario:
     3 countries (XX_SAMPLE_INDEPENDENT) (Type: Independent)
  • India
  • China
  • Australia
Create a Dependent value Set XX_SAMPLE_STATES

Click Edit Information


It shows that the States are dependent on the Above value set.
Now we going to set the state values for each country like below

Country
States
India


Kerala

Tamil Nadu
China


Beijing

Hainan

Hebel
Australia


Adelaide

Brisbane

Kingston




Press down arrow key to go for next country in the Independent Value Field

Press down arrow key to go for next country in the Independent Value Field

 Save...So Now you Created a dependent Value Set and you can use it for any concurrent programs.
Youe parameter window looks like this


I Selected India as Country
Now I will click the States Parameter field

This query might be a helpful one.
select ffv.flex_value_set_name,ffvs.flex_value,ffv.validation_type
from   fnd_flex_value_sets ffv,
       fnd_flex_values ffvs
where  ffv.flex_value_set_name like '%XX_SAMPLE%'
AND    ffv.flex_value_set_id=ffvs.flex_value_set_id;

 dependent Value Set Done.

4) Table
Table type value set is often used by all technical consultants.

  • It use your own application tables as value sets for flex field segments and report parameters instead of the special values tables which Oracle Applications provides.
  • You can also use validation tables with other special arguments to make your segments depend on profile options or field values.
  • You can use any existing application table, view, or synonym as a validation table.
  • If we are using non registered table for your value set, then we have to Create the necessary grants and synonyms to APPS Schema.
  • The value column and the defined ID column in the table must return a unique row for a given value or ID.
  • If the Hidden Id column is provided the value passed to the report will be Hidden and not the Value column.
  • Similarly, when you specify :$FLEX$.Value_Set_Name, your flex field segment or report parameter defaults to always use the hidden ID column to compare with your WHERE clause .
  • We can use Special BIND variable such as :$PROFILES$.Option_name, :$FLEX$.Value_set_name, :block.field in the WHERE clause.
For More Information On Value Sets

Thank you