Oracle Applications FND Useful Tables


Hello Friends , here is some of quite commonly used AOL FND ( Foundation) tables and their usage. There are many other tables also in FND but here i am putting only few commonly used tables. for other table if needed we can dig furthur. Let go through below article and let me know if it useful.

1- FND_ID_FLEXS

2- FND_ID_FLEX_SEGMENTS

3- FND_ID_FLEX_STRUCTURES

4- FND_FLEX_VALUES

5- FND_FLEX_VALUE_HIERARCHIES

FND_ID_FLEXS stores registration information about key flexfields. Each row includes the four–character code that identifies the key flexfield, the title of the flexfield (by which a user identifies theflexfield), the name of the combinations table that contains the key flexfield columns, and the name of the structure defining (MultiFlex) column for the flexfield (SET_DEFINING_COLUMN_NAME). Each row also contains values that identify the application that owns the combination table and the application that owns the key flexfield, a table–type flag that specifies whether the combinations table is specificor generic (S or G), whether dynamic inserts are feasible for the flexfield(Y or N), whether the key flexfield can use ID type value sets, and the name of the unique ID column in the combinations table. You need one row for each key flexfield in each application. Oracle Application ObjectLibrary uses this information to generate a compiled key flexfield definition

FND_ID_FLEX_SEGMENTS: FND_ID_FLEX_SEGMENTS stores setup information about keyflexfield segments, as well as the correspondences between application table columns and the key flexfield segments the columns are used for. Each row includes a flexfield application identifier, the flexfield code,which identifies the key flexfield, the structure number(ID_FLEX_NUM), the value set application identifier, the segment number (the segment’s sequence in the flexfield window), the name of the column the segment corresponds to (usually SEGMENTn, where n is an integer). Each row also includes the segment name, whether security is enabled for the segment, whether the segment is required, whether the segment is one of a high, low segment pair, whether the segment is displayed, whether the segment is enabled (Y or N), type of default value, display information about the segment such as prompts and display size, and the value set the segment uses. Each row also includes a flag for whether the table column is indexed; this value is normally Y. You need one row for each segment of each structure for each flexfield. Oracle Application Object Library uses this information to generate a compiled key flexfield definition to store in the FND_COMPILED_ID_FLEXS table Thanks – Shivmohan Purohit

FND_ID_FLEX_STRUCTURES : FND_ID_FLEX_STRUCTURES stores structure information about keyflexfields. Each row includes the flexfield code and the structurenumber (ID_FLEX_NUM), which together identify the structure, and the name and description of the structure. Each row also includes values that indicate whether the flexfield structure is currently frozen, whether rollup groups are frozen (FREEZE_STRUCTURED_HIER_FLAG), whether users can dynamically insert new combinations of segment values through the flexfield pop–up window, and whether the flexfield should use segment cross–validation rules. Each row also contains information about shorthand flexfield entry for this structure, including whether shorthand entry is enabled, the prompt for the shorthand window, and the length of the shorthand alias field in the shorthandwindow. You need one row for each structure of each key flexfield. Oracle Application Object Library uses this information to generate acompiled key flexfield definition to store in the FND_COMPILED_ID_FLEXS table


FND_FLEX_VALUES stores valid values for key and descriptive flexfield segments. Oracle Application Object Library uses this table when users define values for independent or dependent type value sets. Oracle Application Object Library also uses this table when users define parent values for ranges of child values that exist in a validation table(Oracle Application Object Library stores the parent values in this table). Each row includes the value (FLEX_VALUE) and its hierarchy level if applicable as well as the identifier of the value set the value belongs to. If the value is a dependent value, PARENT_FLEX_VALUE_LOW contains the independent value this value depends upon. Oracle Application Object Library does not use the PARENT_FLEX_VALUE_HIGH column. If ENABLED_FLAG contains N, this value is currently invalid, regardless of the start and end dates.

If ENABLED_FLAG contains Y, the start and end dates indicate if this value is currently valid.SUMMARY_FLAG indicates if this value is a parent value that has child values, and STRUCTURED_HIERARCHY_LEVEL contains the rollup group the parent value belongs to, if any (1 through 9). COMPILED_VALUE_ATTRIBUTES contains the compiled values of anysegment qualifiers assigned to this value. These values are in a special Oracle Application Object Library format, and you should never modify them.

VALUE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE50 are descriptive flexfield columns, where VALUE_CATEGORY is the context (structure defining) column.

These descriptive flexfield columns do not contain values unless you have defined the descriptive flexfield at your site. You need one row for each independent, dependent or parent value belonging to a value set.Oracle Application Object Library uses this information to ensure that users enter valid values in flexfield segments


FND_FLEX_VALUE_HIERARCHIES stores information about child value ranges for key flexfield segment values. Each row includes an identification of the parent value the range belongs to, as well as the low and high values that make up the range of child values. FLEX_VALUE_SET_ID identifies the value set to which the parent value belongs. You need one row for each range of child values (you can have more than one row for each parent value). Oracle Application Object Library provides this information for applications reporting purposes.

 

Oracle Technical – Improve Import/ Export performance?


EXPORT:     

 

Ø       Set the BUFFER parameter to a high value (e.g. 2M)

 

 

Ø       Set the RECORDLENGTH parameter to a high value (e.g. 64K)

Ø       Stop unnecessary applications to free-up resources for your job.

Ø       If you run multiple export sessions, ensure they write to different physical disks.

Ø       DO NOT exports to an NFS mount filesystem. It will take forever.

 

IMPORT:

Ø       Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

Ø       Place the file to be imported on a separate physical disk from the oracle data files

Ø       Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

Ø       Set the LOG_BUFFER to a big value and restart oracle.

Ø       Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG)

Ø       Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

Ø       Use COMMIT=N in the import parameter file if you can afford it

Ø       Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements

Ø       Remember to run the indexfile previously created

 Thanks – Shivmohan Purohit 

 

 

 

 

 

Oracle System Administration – Oracle Apps DBA – Application Developer


 

Hello Friends, a long awaited , here are some of Questions – for Application DBA role, also if you like to know more about oracle application technical insight, this will be useful, do share your thought on whether these are simple , medium or complex questions so i can work out to find more … keep readking

 

What is US directory in $AD_TOP or under various product TOP’s .

US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

What are main concurrent Manager types.

ICM – Internal Concurrent Manager which manages concurrent Managers

Standard Managers – Which Manage processesing of requests.

CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

 

What is difference between ICM, Standard Managers & CRM in Concurrent Manager?

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

 

Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

 

What is multi node system?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

 

Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

 

What is multi node system?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

 

 

What is .dbc file, where its stored, what use of .dbc file?

dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

How to determine Oracle Apps 11i Version?

Select RELEASE_NAME from fnd_product_groups;
You should see output like

RELEASE_NAME

———————–

11.5.9 or 11.5.10.2

 

 

Whats is TWO_TASK in Oracle Database?

TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

 

What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

Where GWYUID defined & what is its used in Oracle Applications?

GWYUID is defined in dbc i.e. Database Connect Descriptor file. It is used to connect to database by think clients

 

How to check number of forms users at any time?

Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc -l

 

 

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD?

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.

‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

 

 

If your system has more than one Jinitiator, how will the system know, which one to pick. ?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used.

 

What are .ldt & .lct files which you see in apps patch or with FNDLOAD?

ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

What is dev60cgi & f60cgi?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms?

Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.
 

 

What is ps -ef or ps command in Unix ?

ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

Thanks – Shivmohan Purohit

Oracle DBA Interview Questions

 

Trace a Concurrent Request And Generate TKPROF File


Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program 

  • Responsibility: System Administrator
  • Navigate: Concurrent > Program > Define
  • Query Concurrent Program
  • Select the Enable Trace Checkbox 

Turn On Tracing

  • Responsibility: System Administrator
  • Navigate: Profiles > System
  • Query Profile Option Concurrent: Allow Debugging
  • Set profile to Yes

 Run Concurrent Program With Tracing Turned On

  • Logon to the Responsibility that runs the Concurrent Program 
  •  In the Submit Request Screen click on Debug Options (B)
  • Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ‘Request id: ‘||request_id ,  ‘Trace id: ‘||oracle_Process_id,  ‘Trace Flag: ‘||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ‘||prog.user_concurrent_program_name,  ‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ‘||decode(phase_code,’R’,’Running’)  ||’-‘||decode(status_code,’R’,’Normal’),  ‘SID Serial: ‘||ses.sid||’,’|| ses.serial#,  ‘Module : ‘||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and dest.name=’user_dump_dest’  and dbnm.name=’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id; 

 

 

 

 

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10” long running queries

 

 

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 

 

 

 

 

Thanks – Shivmohan Purohit 

 

How To Open A Worksheet Directly From Oracle Applications Menu ?


Hello Friends, many of my past work involve integrating oracle discoverer with oracle applications, here there is one of the ways to leverage both to integrate within applications.

 How To Open A Worksheet Directly From Oracle Applications Menu ?

 

 If a workbook has 4 worksheets how to default to a particular worksheet. This article will help you to pass the worksheet identifier and parameter directly while opening a workbook. The worksheet identifier and the worksheet parameters for the workbook has to be specified in the form function with below mentioned guidlines.
 

Form Function  Definition

 

Function Name

Internal Function Name

Function User Name

Function name (as shown on the menu)

Type

WWW

Description

Function description

Parameters

workbook=workbookname
&PARAMETERS=sheetid~worksheet id*param_parameter name One~Parameter One Value*param_parameter name Two~Parameter Two Value*

HTML Call

OracleOasis.RunDiscoverer

 

 Parameters in the parameter list are delimited by the characters

“param_” at the beginning of each parameter and
“~” as delimiter between parameter name and value and
“*” at the end of each parameter

 ICX interprets the PARAMETER list and passes the parameters to Discoverer in the required URL format.

 Example 1

 If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT and worksheet id is 1 then parameter will be as follows

 workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*

 Example 2

If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT , worksheet id is 1 and the value for worksheet parameter “deptname”  to be passed is 10 then parameter will be as follows

workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*param_deptname~10*

 Example 3

If a workbook has two parameters “Company” and “City” you would specify in the Parameter list of the Form funtion:

param_Company~Oracle Corp.*param_City~Redwood Shores* 

Thanks – Shivmohan . do share your feedback

Export a concurrent program and executable using FNDLOAD


Hello Friends, a very useful easy to do……. How to export a concurrent program and executable using FNDLOAD?  

 

 

 

 

 

 

 

Please use the following FNDLOAD command to download a concurrent program to a flat file :
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”<your_application_short_name>” CONCURRENT_PROGRAM_NAME=”<your_concurrent_name>”

To upload it to another environment, use the following command :
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt

You may found additional explanation in the Oracle Applications System Administrator’s Guide – configuration Release 11i ( appendix C)

 thanks – Shivmohan

Discoverer SQL: Long Running Discoverer Report


Discoverer SQL: Long Running Discoverer Report

Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.

SELECT q.qs_doc_name Worksheet,

 q.qs_doc_details Sheet ,

 q.qs_num_rows Lines,

 QS_ACT_CPU_TIME CPU_Time,

 q.qs_act_elap_time Seconds,

 FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes, 

 q.qs_created_date Executed,

 q.QS_DOC_OWNER Owner,

 u.user_name APPS_User,

CASE

WHEN q.qs_state = 0 THEN ‘ERROR :CANCELLED’

WHEN q.qs_state = 1 THEN ‘RUNNING’

WHEN q.qs_state = 2 THEN ‘COMPLETED’

END “CURRENT STATUS”

  FROM discovr_apps.eul5_qpp_stats q,

 fnd_user u

 WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1

AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id

–AND u.user_name LIKE ‘D%’

–AND q.qs_state =1

  –AND FLOOR(q.qs_act_elap_time/60) >= 30 

  ORDER BY q.qs_act_elap_time DESC;

Thanks – Shivmohan Purohit