Oracle Apps Technical Related

REGEXP_INSTR function for oracle 9i

2012-03-06T15:26:00.001+05:30

I created a procedure which used all these functions in Oracle 10g
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR

Now i want to use the same procedure in Oracle 9i . Then i realized we don't have the above functions in Oracle 9i database. Then googled for string functions available in Oracle 9i. I come to know there's a package called OWA_PATTERN which provides few pattern matching facility. It didn't fulfill my needs. so my further googling gives me a link in which i found function script for REGEXP_LIKE & REGEXP_REPLACE.

so,i started writting code for REGEXP_INSTR.

CREATE OR REPLACE FUNCTION REGEXP_INSTR(LC_IN_STR VARCHAR2,LC_PATTERN VARCHAR2)
return number is
i NUMBER;
LC_LENGTH_STR number;
ln_number_pos NUMBER;
begin
LC_LENGTH_STR:=length(LC_IN_STR);
for i in 1..LC_LENGTH_STR
LOOP
    ln_number_pos:=OWA_PATTERN.AMATCH(lc_in_str,i,lc_pattern);
    EXIT when ln_number_pos>0;
end LOOP;
return ln_number_pos-1;
end;
/

The Function Usage Example :
1) SELECT regexp_instr(banner,'[0-9]')
    FROM v$version where banner='Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production';
      Returns : 7

2)SELECT regexp_instr(banner,'[A-Z]')
   FROM v$version where banner='Oracle9i Enterprise Edition Release   9.2.0.6.0 - 64bit Production';
Returns : 1

Oracle Sample Code Snippets Links

2012-02-14T15:11:00.000+05:30

http://www.oracle.com/technetwork/indexes/samplecode/index.html

we can get the sample code for below tools.

Oracle Forms Short Cut Keys

2011-10-07T15:08:00.000+05:30

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

Flexfield

2011-07-05T15:01:00.000+05:30

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

Value Set

2011-07-04T19:11:00.003+05:30

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.

created_by ( User Who created the value set)
creation_date ( value set creation date)
last_update_date
last_updated_by
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

To know more about value set refer this http://oracle.anilpassi.com/value-set-basics-in-oracle-apps-2.html

Define Valueset-> http://maishareef.com/oracleapps/implemenatation/f_imp_set.html

Enter Values in the Value Set-> http://maishareef.com/oracleapps/implemenatation/f_imp_values.html

Flex value description can be found on FND_FLEX_VALUES_VL table.

TYPES OF VALIDATION

1) None (not validated at all)

Allow users to enter any value.
Only Format Validations will be done.

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

Data Entry and Retrieval From Database Through Oracle Forms

2011-06-29T18:43:00.000+05:30

These are the basic controls must known to both tech and functional people to enter and retrieve data via forms.
I will take concurrent program definition form as an example to explain you this

1) Login to oracle applications
2) System Administrator Responsibility
3) Function->Concurrent : Program-> Define

Note: Yellow colored fields are required fields.Without filling all the yellow colored fields we can't save the data into database.

If you press save button now.It will prompt for a message like "Field must be entered" at left bottom of the form as shown.

Once you enter all the data into all the required fields you can save the data..

Now if you click save button..you will get this prompt below..It means your data saved in database.

Now lets see how to Retrieve the existing data from database through forms

To Retrieve the existing data from database press F11 key..Now you will be able to see you form as shown below..

Type "XX My Demo Conc Pgm" in Program Field and Press "Ctrl+F11" Key
Now you are able to see all the details what you entered in the database as shown below

You can try several cases like this
XX My Demo Conc% then press Ctrl + F11
Press down and up arrow key if you have more than one program name having same prefix.
Thank You.
We Will see What is Flexfield and Valueset in next post