Register the Report into Oracle Application

Introduction
To register a Report with Oracle Applications in order to use it in Apps.

1. Develop the report(.rdf) file as per client requirement by using reports 6i.
2. move the .rdf file from local machine to server into the respective path

CUS_TOP\11.5.0\Reports\US\ .rdf PO_TOP\11.5.0\Reports\US\ .rdf

1. Connect to the application and select system administrator create Executable
2. Executable Name (Any User defined Name)
3. Application Name(Where the .rdf file located)
4. Execution Method (Oracle Reports for reports)
5. Execution File (.rdf file Name)
6. Create Concurrent program and attach 1)Executable
7. Parameters
8. Incompatible Programs
9. create Request Group attach Concurrent Program
10. Create responsibility attach
    1. Request Group
    2. Menu
    3. Data Group
11. Create User Attach Responsibility to the User
12. User will submit the request from SRS (Standard Request Submission) Window

SELECT user_id UserID, user_name UserName, creation_date Cdate FROM fnd_user Executable = System Administrator => Concurrent = > PRogram =>Executable Concurrent Program = => Concurrent = > Program => Define Request Group = => Security = >Responsibility => Request responsibility = => Security = >responsibility => define user = =>Security =>User =>Define SRS Window = View=>Requests=>Submit New Request => OK =>Select Program Name =>Click Submit Button Press NO =>Select find Button =>

1. Click View output button to open the output file.
2. Click View Log button to open the log file.

1.we will develop the report through report 6i.
2.we will move that report from local machine to oracle apps server
for example if it Po module the path is
PO/11.5/reports/us/xx.rdf
3.we will create exectuable from sysadmin responsiblity.
4.we will create concurent program from sysadmin responsiblity and attch exectable incompatabilites parameters.
5.we will create request group and attach concurent program.
6.we will create responsiblity and attach request group.
7.we will attch responsilty to user .
8.user will sumbit concurent program from srs window.
We will modify
pre_form trigger when_new_form_instance app_custom package we need to change in template.fmb

Essential for Form Customization

Mandatory Form and Libraries
============================

1. Download these forms in bin mode from $AU_TOP/forms/US

TEMPLATE.fmb
APPSTAND.fmb

2. get all PLLs in bin mode from $AU_TOP/resource

Setting up Forms60_Path in Registry
===================================

3. Copy all these PL/SQL libraries and the Template form in one folder.

4. Set the form60 path in the registry.

Go to Start -> Run -> Regedit -> HKEY_LOCAL_MACHINE -> Software -> Oracle
Here we can find the oracle homes.
In these homes check for FORMS60_PATH.
Append the path of your folder where all the libraries and the form is present, prefixed with a semicolon(;).

E.g.: Before Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB”
After Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB;c:\forms\libraries”


Develop the form using Form Builder Tool
========================================

5. Open the Forms Builder tool and click on the “Build a form based on a template” radio button
Choose the TEMPLATE.fmb form from your local working directory.
SAVE your new FORM as with a different filename.
Also rename the module with the same name as the filename.

6. Delete BLOCKNAME & DETAIL BLOCK FROM DATABLOCK,

7. DELETE BLOCKNAME FROM CANVAS AND WINDOW

8. Create a window NEW_WIN, canvas NEW_CAN
Create a new block based on the table you created in your custom schema


Changes to be made in the Triggers and Program Units
====================================================
9. Modify specific trigger properties and program units to ensure the form will appear properly in Oracle Apps.


10. In the Property Palette of your module modify the ‘First Navigation Data Block’ property to the name of your first data block.

11. In the APP_CUSTOM.CLOSE_WINDOW program unit modify the following line:

if (wnd = '') then app_window.close_first_window;

by changing to the name of your first window

Note: Also make sure the ‘Window’ property for your canvases are set to the proper window

12. In PRE-FORM trigger -

app_window.set_window_position('', 'FIRST_WINDOW');

app_window.set_window_position('XXEDP_QA_WIN_INITIAL_INFO', 'FIRST_WINDOW');

13. In WHEN-NEW-FORM-INSTANCE -

FDRCSID('$Header: TEMPLATE.fmb 115.12 2003/12/19 11:02 mzasowsk ship $');


cHANGE IT TO

FDRCSID('$Header: XXEDPQA_READ_CODES 115.12 2009/01/28 mzasowsk ship $');


Form objects
=============

14. Create your blocks, items, triggers and relationships as per requirement.
Save your FORM frequently.

15. Apply property classes to each item to ensure the same look and feel as the rest of Oracle Apps forms.

For each Module, Canvas, Window etc., apply the Property Classes that is most appropriate.

Item Type Property Class
--------- --------------
Module MODULE
Canvas CANVAS
Window WINDOW
Data Block BLOCK
Row ID ROW_ID
Text Item TEXT_ITEM
Display Item DISPLAY_ITEM or EXT_ITEM_DISPLAY_ONLY
Creation Date CREATION_OR_LAST_UPDATE_DATE
Last Update Date CREATION_OR_LAST_UPDATE_DATE
Date fields TEXT_ITEM_DATE

To apply the property class, click on the small button (on the right) on the “Subclass information” property of the property sheet of each item.
In the “Subclass Information” Window choose the property class radio button. Then choose the correct property class for the current item.

Note: You cannot apply property classes to multiple items at once.

Attaching a Calendar to Date field
==================================
Date Item Properties
Subclass information TEXT_ITEM_DATE
List of Values ENABLE_LIST_LAMP
Validate from List No

Add an Item Level Trigger
Trigger KEY-LISTVAL
Code Calendar.Show;


Setting Who Columns
===================

Make sure you have following columns named exactly same in table and text items named exactly same as follows

CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_LOGIN NUMBER NOT NULL

Write fnd_standard.set_who; in Pre-Update and Pre-Insert triggers at block level


Transferring the “.fmb” file from local system to Apps Instance
=================================================================
16. Make sure you transfer the form in "Binary" (bin) mode

Generating the “.fmx” file on the Vision Instance
==================================================
17. Compile command will be something like :

f60gen .fmb userid=apps/@


change mode of file:

chmod 755 .fmx



Register Form, define function, attach to menu
==============================================
18. Register Form

Go to Application Developer Responsibility

Application -> Form


Form:
Application:
User Form Name:
Description:

19. Define Function for the form

Go to From Application Developer Responsibility

Application -> Function

Description tab -
Function:
User Function NAme:
Description:

Type: Form

Form:

20. Assign the menu for the form

Application -> Menu

Here we have to decide, in which responsibility we have to put this form.
Choose menu based on responsibility. Decide under which submenu the form should be kept.

Responsibility-
Menu-
Application-

Query to get the Registered Tables details in Oracle Apps

select T.APPLICATION_ID,A.APPLICATION_NAME,T.TABLE_ID,T.TABLE_NAME,T.USER_TABLE_NAMEfrom FND_TABLES T, FND_APPLICATION_TL A
where T.APPLICATION_ID=A.APPLICATION_IDand T.CREATED_BY<>1
and T.TABLE_NAME like '&name%';
_____________________________________________________________________

desc FND_TABLES;
desc FND_USER; --User table


desc FND_APPLICATION_TL --Application table
Getting Related FND_ table nams with details from database

select object_name,object_type from all_objects where object_name like 'FND_%' and object_type like 'TABLE'; 

-- Query to getting registered table's details in Oracle apps

Oracle Apps Implementing Key Flex field and Descriptive Flex field

Descriptive Flexfield

The descriptive flexfield could be defined for a custom form or a standard form but in either case, the descriptive flexfield is required to be defined in the system first. If the descriptive flexfield is to be defined on a custom table then the custom table is required to be registered with Applications Objects Library before it can be used in the descriptive flexfield definition. To register custom tables under AOL refer to the document on Table Registration. The standard tables come registered with the Oracle Applications therefore you do not need to register them again.

To register the descriptive flexfield, open the register form using the navigation path : Application Developer à Flexfield à Descriptive à Register. The form would look like as shown in figure 12 below.



Figure 12. Descriptive Flexfield Register form

Once you have identified the additional information you want to capture in a Descriptive flexfield, you must determine the usage. There are two types of segments for Descriptive flexfields:

• Global segments
• Context-sensitive segments

Global Segments

A global segment is a segment that will always be displayed in a Descriptive flexfield window common to all context values, and it is the most commonly used type of Descriptive flexfield. Each global segment maps one-to-one to a database column. Descriptive flexfield segments are usually stored in columns named ATTRIBUTE1 to ATTRIBUTEn, where n is the maximum number of segments for a given Descriptive flexfield.

Context-Sensitive Segments

A context-sensitive segment is a segment that may or may not be displayed in a Descriptive flexfield window, depending on a context value. A context value can be derived from another field in the same block or from the context field. The field from which a context value derives its value is called a reference field. Remember, a Descriptive flexfield is associated with a database table, not a single form. A context field is a special field appearing in the Descriptive flexfield window. You can use a context field to allow users to enter their own context values or override what is defaulted from the reference field. The context value, whether from a reference field or a context field, determines which context-sensitive segments will be displayed.

Let’s say, you need to define a DFF for the Human Resource à People à Enter and Maintain form. The DFF field ( [ ] ) is already provided on the form. Depending upon whether the details being entered are for an employee or an applicant, you want to decide on the details to be entered in the database.

For an employee the details required are:

1. Drivers License Jurisdiction
2. Drivers License Number
3. Global ID
4. Country of Origin
5. Security Clearance
6. Badge Number
7. Bonded?
8. Hired Via Acquisition
9. Acquired Company
10. Original Hire Date
11. Proposed Retirement Date
12. Proposed Retirement Reason
13. Service Start Date
14. Union Seniority Date

And the details required if the person is an applicant are:

1. Current Employer
2. Employer's Phone
3. Verification Check Date
4. Verification Checked By
5. Reference Check Date
6. Referenced Checked By
7. Pre-Employment Physical Date

The DFF prompt fields have to depend on the value entered in the context field. If the value entered is EMP then the prompt for the employee details should pop-up and if the value entered is APL then the prompt for Applicant details should pop-up.

The steps involved are :

Register the DFF using the Descriptive Flexfield registration form as shown in figure 13 below. Standard DDF’s are already registered with oracle applications and hence this step can be skipped for seeded DFF’s.



Figure 13. DFF Register Form

Forms and flexfield routines use the combination of application name and flexfield name to uniquely identify your flexfield. The details of the fields on the above forms are:

Application

Application name where you want to register your DFF.

Name

Use this name when you use flexfield routines to call your descriptive flexfield from your forms or programs.

Title

Flexfields displays this unique title at the top of the flexfield window when your users enter your descriptive flexfield.

Table Name

Enter the name of the table that contains your descriptive flexfield columns. Your table must already exist in the database, and it should already have columns for your descriptive flexfield segments, as well as a structure column. These segment columns are usually called ATTRIBUTE1, ATTRIBUTE2, ..., ATTRIBUTEn.

You must register your table with Oracle Applications before you can use it in this field

Protected

In some cases, you may want to create a descriptive flexfield that cannot be inadvertently changed by an installer or user. This type of flexfield is called a protected descriptive flexfield. You build a protected descriptive flexfield the same way you build a normal descriptive flexfield. The main difference is that you check the Protected check box after defining your segment structures. Once a descriptive flexfield is protected, you cannot query or change its definition using the Descriptive Flexfield Segments window. You should define your descriptive flexfield segments before you change the Protected check box.



Reference Fields Window
Use this window to specify any form fields that might serve as descriptive flexfield reference fields. Your flexfield can use values in one of these fields (context field values) to determine which flexfield structure to display.

Columns

These are columns of the table that would to be used in the DFF. You may declare all columns here even if they are not being used in the DFF presently.

Save the changes and close the form. You need to define the structure of the DFF now. To do this open the Segments form using the path Application Developer à Flexfield à Descriptive à Segments.





Figure 14. DFF Segments Form


The segment form as shown in the figure 14 above is used to enter the context field values upon which the prompts in the pop-up would depend. The EMP and APL are the values that a user would enter in the context prompt for the DFF. The pop-up would decide the prompts on the basis of this value.

Global Data Elements are those prompts which would be displayed in the all the values for the context prompt.

To determine the structure of the DFF on APL context value, click on segments button at the bottom when the APL value in Context Field Values is selected. The fields to be entered are shown in figure 15 below.





Figure 15. Prompts for the APL context value


Figure 16 below shows the prompts for the EMP context value.





Figure 16. Prompts for the EMP context value


Save the changes and click on compile to freeze the DFF definition. You may optionally use the Value Sets for the values to be entered in the various prompts.


The DFF is now ready to be used from the form. To enable the DFF on the form, follow the steps below.

Open the form using the form builder and write the following code in WHEN-NEW-FORM-INSTANCE trigger of the form. Standard forms (already defined by oracle) will have this code already present in the appropriate triggers.

FND_DESCR_FLEX.DEFINE (
BLOCK=> 'TEST_DESC',
FIELD=> 'DESC_FLEX_TEST',
APPL_SHORT_NAME=>'HR',
DESC_FLEX_NAME=>'DESC_FLEX_TEST'
);

Where BLOCK is the name of block containing DFF item
FIELD is the DFF item on form
APPL_SHORT_NAME is the short name of the application with which the flex field is registered
DESC_FLEX_NAME is the name of the registered flexfield (Step 3)

Sponsored Links

Also write the following code in 'WHEN-NEW-ITEM-INSTANCE' at the form level:
FND_FLEX.EVENT ('WHEN-NEW-ITEM-INSTANCE');


The DFF would look like as shown in the figure 18 and figure 19 below depending upon the context value provided in the form as shown in figure 17 below.





Figure 17. Context Prompt





Figure 18. Prompt for APL Context Value





Figure 19. Prompt for EMP Context value

Key Flexfield

The basic steps in defining a key Flexfields are as given below. You may or may not use all the steps. The detailed explanation is being followed after the steps.

• Identifying Key flexfields that are required for you Oracle Applications installation
• Designing a Key flexfield structure
• Selecting the appropriate Key flexfield to define
• Defining structure information for the Key flexfield
• Defining segment attributes
• Defining flexfield qualifiers
• Defining segment qualifiers
• Implementing optional features as needed

To define the key Flexfield structure, you need to open the Key Flexfield segment form as shown in the figure 5 below. The navigation path is Application Developer à Flexfield: Key à Segments.





Figure 5. Key Flexfield Segments Form


Lets say you are defining the structure for an Accounting Flexfield. Query for the Flexfield Title “Accounting Flexfield” using the Flexfield Title window as shown in figure 5 above. If there are already existing structures for Accounting Flexfields, they would be listed in the Structures window as shown in figure 6 below.





Figure 6. Listing of existing structures for Accounting Key Flexfield


Lets say you want to define a new structure for the Accounting Flexfield with the structure code as “NEW_ACCOUNTING_STRUCTURE”. Enter the code name in the Code window and the title as “New Accounting Flexfield Struc” in the Title window. Enter an optional description for the new structure which may include the purpose of the new structure. Save the changes. The form would look like as shown in figure 7 below.






Figure 7. Structure definition for a new Accounting Flexfield


There are few check buttons on the form in the figure 7 above which can be left unchecked/checked as shown in the figure above.

The Enabled checkbox is checked by default. You can optionally uncheck it. You cannot delete a Key flexfield structure, but you can disable it by unchecking the Enabled checkbox. Next, you may select a Segment Separator other than the default period. Your choices are period, dash, pipe, or any custom character. You must have a segment separator.

Next, you can select whether you want to Cross-Validate Segments. Unchecking this checkbox will disable cross-validation for this Key flexfield structure. You can then identify whether you want to Freeze Rollup Groups. If you mark this checkbox, you will not be able to change the assigned rollup groups in the Define Segment Values form. As a last step, you can determine whether you want to Allow Dynamic Inserts for Key flexfield structures. If you do not allow dynamic inserts, you must create all flexfield combinations in the combination form.

Sponsored Links

You can freeze your flexfield structure by checking the Freeze Flexfield Definition checkbox. Once frozen, you can only make very limited changes to the flexfield structure definition. If you need to change the flexfield structure definition, first uncheck the Freeze Flexfield Definition checkbox and make the changes. Next, reactivate the Freeze Flexfield Definition checkbox. You will receive a warning message that tells you that unfreezing this flexfield and making changes to the segment definitions could affect the validity of the data already existing for this flexfield. Changing a Key flexfield structure will not update existing flexfield data. Do not freeze your flexfield structure yet, as you have not entered the segment attributes for your flexfield structure.



Once the above fields are defined click on the Seg­ments button at the bottom of the form. The new form would look like as shown in figure 8 below. Enter the values as shown in the form below. You can use the already defined Value Sets for the various segments. If you have not defined them earlier, they can be created using the Value Set button at the bottom of the form. Save the changes. To assign a flexfield qualifier to each segment, select the segment on the form and then click on the Flexfield Qualifiers button as the next step.





Figure 8. Accounting Flexfield Segment Structure form


Select the New Company segment and click on Flexfield Qualifier button. Check the Balancing Segment for the New Company structure as the flexfield qualifier. The form would look like as shown in figure 9 below. Follow the same steps to choose the flexfield qualifiers for the remaining 2 segments. Check Cost Center Segment for New Cost Center and Natural Account for New Account segments respectively. Save the changes and close the Structure form.





Figure 9. Flexfield Qualifier form


As the next step, the form shown in figure 7 would be visible now. Click on the Freeze Flexfield Definition button. Then compile the flexfield structure. You can compile your flexfield structure by hitting the Compile button. Compiling a flexfield structure will automatically save the flexfield structure. You will see the dialogue box as shown in figure 10 below. Choose OK on the dialogue box.





Figure 10. Compiling Flexfield Definition





Figure 11. Dialogue box for compilation of flexfield structure


Click on OK and the flexfield definition would be saved.

Oracle Apps 11i Free Training : Table Registration API

Oracle Apps 11i Free Training : Table Registration API

This chapter provides you with information you need to register tables to be used in Oracle Alerts and Flexfields with Oracle Applications. We will be learning about Procedures in the AD_DD Package, Procedure REGISTER_TABLE, Procedure REGISTER_COLUMN, Procedure DELETE_TABLE, Procedure DELETE_COLUMN and Example of Using the AD_DD Package.

Sponsored Links

Introduction

Though Oracle Applications comes with thousands of seeded database tables, there can be numerous applications in which one might be required to create a custom table to be used. In most of the applications all you need is to create a table in a schema and use it directly in your applications. Flexfields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used.

You register your custom application tables using a PL/SQL procedure in the AD_DD package.
Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.

Procedures in the AD_DD Package

1. Procedure REGISTER_TABLE

procedure register_table ( p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

2. Procedure REGISTER_COLUMN

procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

3. Procedure DELETE_TABLE

procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);

4. Procedure DELETE_COLUMN

procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

VARIABLE NAMES	DESCRIPTION
p_appl_short_name	The application short name of the application that owns the table (usually your custom application).
p_tab_name	The name of the table (in uppercase letters).
p_tab_type	Use ’T’ if it is a transaction table (almost all application tables), or ’S’ for a ”seed data” table (used only by Oracle Applications products).
p_pct_free	The percentage of space in each of the table’s blocks reserved for future updates to the table (1–99). The sum of p_pct_free and p_pct_used must be less than 100.
p_pct_used	Minimum percentage of used space in each data block of the table (1–99). The sum of p_pct_free and p_pct_used must be less than 100.
p_col_name	The name of the column (in uppercase letters).
p_col_seq	The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type	The column type (’NUMBER’, ’VARCHAR2’, ’DATE’, etc.).
p_col_width	The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable	Use ’N’ if the column is mandatory or ’Y’ if the column allows null values.
p_translate	Use ’Y’ if the column values will be translated for an Oracle Applications product release (used only by Oracle Applications products) or ’N’ if the values are not translated (most application columns).
p_next_extent	The next extent size, in kilobytes. Do not include the ’K’.
p_precision	The total number of digits in a number.
p_scale	The number of digits to the right of the decimal point in a number.

Example of Using the AD_DD Package

Here is an example of using the AD_DD package to register a flexfield table and its columns:

Though the use of AD_DD package does not require that the table should exist first, it is always better to create one and proceed further. Use the below mentioned script to create the dummy table. Use the APPS User ID to run the below mentioned queries from TOAD or SQL*PLUS.

CREATE TABLE TEST_DESC ( RESOURCE_NAME VARCHAR2 (150),
RESOURCE_TYPE VARCHAR2 (100),
ATTRIBUTE_CATEGORY VARCHAR2 (40),
ATTRIBUTE1 VARCHAR2 (150),
ATTRIBUTE2 VARCHAR2 (150),
ATTRIBUTE3 VARCHAR2 (150),
ATTRIBUTE4 VARCHAR2 (150),
ATTRIBUTE5 VARCHAR2 (150),
ATTRIBUTE6 VARCHAR2 (150)
);
BEGIN
AD_DD.REGISTER_TABLE ('FND','TEST_DESC','T');
END;


BEGIN
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','RESOURCE_NAME', 1, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','RESOURCE_TYPE', 2, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE_CATEGORY', 3, 'VARCHAR2', 40, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE1', 4, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE2', 5, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE3', 6, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE4', 7, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE5', 8, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE6', 9, 'VARCHAR2', 150, 'Y', 'N');
END;

Check the TABLE wheather register or Not

"select * from FND_TABLES where created_by <> 1"

helps you?
(created_by = 1 are tables created by Autoinstall)

Registering a table in Oracle Applications 11i Ebusiness suite to be used in Alerts and Flexfields
Flexfields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used. Custom application tables can be registered by using the AD_DD PL/SQL Package. If you are planning to use custom tables either in Alerts or in Flexfields, they need to be registered. The following methods describe how you can register the tables.

• ADD_DD.REGISTER_TABLE

ad_dd.register_table ('Application short name', 'EMAIL_TEMPLATES', 'T');

• ADD_DD.REGISTER_COLUMN

ad_dd.register_column ('Application short name',
'EMAIL_TEMPLATES',
'EMAIL_TEMPLATE_ID',--Column
1,--Sequence
'number',--type
4,--width
'N',
'N'
);

• Register all the columns one by one . Now you can use these custom table in your flex field definitions and in Oracle Alerts

Query for Workflow Version

desc wf_resources;

select TEXT from wf_resources where NAME like 'WF_VERSION';

select * from v$version;


select release_name from apps.fnd_product_groups;

Registering new forms into Oracle

Document Summary This document describes the process of registering new forms in oracle applications.
Registering a Form
Navigation – Application Developer -> Application->Form

Click on Form and you will see following screen.

Fields

Form : Enter the file name of your form (the name of the .fmx file) without extension. Your form filename must be all uppercase, and its .fmx file must be located in your application directory structure.

Application : Enter the name of the application which will own this form.

User Form Name : This is the form name you see when selecting a form using the Functions window.

Description : Enter a suitable description for your form.

Register Form Functions

Navigation – Application Developer -> Application->Function

Click on Function and you will see following screen

Click on the form tab and you will see following screen

Fields

Function : Enter a unique function name for your function. This function name can be used while calling this program programmatically. This name is not visible to the user through other forms.

Form : Select the form name which you have registered.

Application : Select the application name for your form.

Parameters : Enter the parameters that you want to pass to your form function. E.g. Query_only.

Creating Menu of Functions

Navigation – Application Developer -> Application->Menu

Click on Menu and you will see following screen

Fields

 

Menu : Enter the descriptive name for the menu. This name is not visible to the user.

 

User Menu Name : The user menu name is used when a responsibility calls a menu.

 

Menu Type : The options in menu type include:

 

• Standard - for menus that would be used in the Navigator form
• Tab - for menus used in self-service applications tabs
• Security - for menus that are used to aggregate functions for data security or specific function security purposes, but would not be used in the Navigator form

Seq : Enter a sequence number.

 

Prompt : Enter the prompt that the users will see for the menu.

 

Submenu : If you want another menu to be called from this menu, then enter this menu name in this field.

 

Function : Enter the form function name in this field.

 

Description : Enter a suitable description for the menu.

 

Grant :The Grant check box should usually be checked. Checking this box indicates that this function is automatically enabled for the user. If this is not checked then the function must be enabled using additional data security rules.

 

View Tree :Click on View Tree Button and you will see following screen with the full hierarchy of the menu.

Creating Responsibilities

Navigation – System Administrator -> Security->Responsibility->Define

Click on Define button and you will see following screen in front of you.

Click on Define button and you will see following screen in front of you.

Find component Version in Apps 11i/R12/12i

Find component Version in Apps 11i/R12/12i

Q. How to find Apps Version (11i/R12/12i).
A. Connect to database as user apps
SQL> select release_name from apps.fnd_product_groups;
Output like 12.0.4 or 11.5.10.2

Q. Web Server/Apache or Application Server in Apps 11i/R12
A. Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Output for 11i should be like
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jan 26 2005 11:06:44 (iAS 1.0.2.2.2 rollup 5)
Output for R12 should be like
Server version: Oracle-Application-Server-10g/10.1.3.0.0Oracle-HTTP-Server
Server built:   Dec  4 2006 14:44:38

Q. Forms & Report version (aka developer 6i) in 11i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/f60run | grep Version | grep Forms
output like
Forms 6.0 (Forms Runtime) Version 6.0.8.25.2 (Production)
Check fourth character in version 25 which means Forms 6i patchset 16 (25-9)
.
Q. Forms & Report version in R12/12i
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release
Output should be like
Report Builder: Release 10.1.2.2.0
You can safely ignore warnings.

Q. Database Version in 11i/R12/12i
A. Go to database section below.

Q. Oracle Jinitiator in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Q. Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

Q. File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Q. Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check
adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Q. OA Framework Version
A.http:// hostname.domainName:port/OA_HTML/OAInfo.jsp (Only for 11i)
A. Log in as Application user, set environment variable and run below query
adident Header $FND_TOP/html/OA.jsp
adident Header $OA_HTML/OA.jsp
output for both should look like
$Header OA.jsp 115.60 2006/03/31 00:47:28 atgops1 noship $
120.21 means OA Framework Version (coming soon..)
115.60 means OA Framework Version (coming soon..)
115.56 means OA Framework Version (coming soon..)
115.36 means OA Framework Version 5.7
115.27 means OA Framework Version 5.6E
115.26 means OA Framework Version 5.5.2E

Q. Discoverer Version for 11i (3i or 4i)
A. Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/disc4ws | grep -i Version

Q. Discoverer Version for 11i or R12 (10g AS)
Check under Application Server Section as 10g AS Discoverer is on standalone

Q. Workflow Version with Apps
A. Connect to Database as apps user
SQL> select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0  Version for Fusion Middleware Component

Identity Management component Version/Release Number

A. Oracle Single Sign On
Connect to database which holds SSO repository
SQL>select version from orasso.wwc_version$;

B. Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
>>> To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
output should look like
oidldapd: Release 10.1.4.0.1 – Production on mon jul 14 14:14:21 2008
Copyright (c) 1982, 2006 Oracle.  All rights reserved.
>>> To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \
-s base “objectclass=*” orcldirectoryversion
and output should be like
version: 1
dn:
orcldirectoryversion: OID 10.1.4.0.1
or run following query in database
SQL> select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;
Output should be like OID 10.1.4.0.1

C. Application Server
1. Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0
2. For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
SQL> select * from ias_versions;
or
SQL>select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
.
D. AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version
.
E. Oracle Portal
SQL> select version from portal.wwc_version$;
.
Database Component
I) Oracle Database
To find database version
SQL> select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail

.Unix Operating System

Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release

1.Q: How to find Oracle Applications Web Server Version ?
Or
How to find Version of Apache used with oracle apps 11i ?
Ans: Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command ./httpd -version
You will see output like:
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19
Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server
Above is output If you have installed 10g Application Server with 11i
2.Q: How to find Jinitiator Version ?
Ans:Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin
defined by environment variable FORMS60_WEB_CONFIG_FILE and
search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ;
if your version is 1.3.1.18 you will see entry like 1,3,1,18
3.Q: How to find Forms Version in 11i from front end?
Login to forms from frontend , on top menu bar of forms click on \”Help\”
and Select \”About Oracle Applications\” go to \”Forms Server \” section.
You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 .
If you want to know whats your forms patchset level then subtract 9 from fourth digit,
which means for above case form patchset 17 is applied.
4.Q: How to find Forms Version in Apps from command Line ?
Ans:Enter \”f60gen\” on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit – 9)
5.Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ?
Ans:In Oracle Applications under ad utilities there is utility called as adident,
Used for Identification purpose.
to find out file version use:
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp.
Similarly you can use adident to find version of any report in 11i.
6.Q: Can I run 64 bit application on 32 bit Operating system ?
Ans:You can run 32 bit application (like oracle application server, web server,
all oracle application server are 32 bit ) on both 32 /64 bit operating system
but a 64 bit application like 64 bit database can run only on 64 bit operating system.
7.Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?
Ans:execute \”file $ORACLE_HOME/bin/oracle\” ,
you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download
8.Q: How to find OUI version ?
OUI stands for Oracle Universal Installer.
In order to find Installer version you have to execute
./runInstaller -help ( From OUI location)
You will get output like
Oracle Universal Installer, Version 10.1.0.4.0 Production Copyright (C) 1999, 2005, Oracle. All rights reserved.
That means OUI version in above case is 10.1.0.4
OUI location is
$ORACLE_HOME/oui/bin
9.Q: How to find Database version ?
Ans:SQL> select * from v$version;
The command returns the release information, such as the following:
Oracle9i Enterprise Edition Release 9.2.0.7.0 – Production
PL/SQL Release 9.2.0.7.0 – Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 – Production
NLSRTL Version 9.2.0.7.0 – Production
10.Q: How to find Oracle Workflow Cartridge Release Version ?
Ans:Log in to the database as the owf_mgr user and issue
select wf_core.translate(\’WF_VERSION\’) from dual;
11.Q: How to find opatch Version ?
Ans:opatch is utility to apply database patch , In order to find opatch version execute
\”$ORACLE_HOME/OPatch/opatch version\”
You should see output like OPatch Version: 1.0.0.0.52
which means your opatch version is 1.0.0.0.52
12.Q How to Discoverer Version installed with Apps ?
Ans:Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i.
To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i \’discoverer version\’
You should see output like
Discoverer Version:Session 4.1.47.09.00
Which means you are on discoverer 4i version 4.1.47.09
13. Q. How to find Version of Apps 11i ?
Ans:Run following SQL from apps user ;
SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
———————–
11.5.10.2
Which means you are on Apps Version 11.5.10.2
14.Q. How to find Workflow Version embedded in Apps 11i ?
Ans:Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME=\’WF_VERSION\’;
You should see output like
TEXT
———————-
2.6.0
Which means you are on Workflow Version 2.6.0
You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.
15.Q: How to find version of JDK Installed on Apps ?
Ans:There might be multiple JDK installed on Operating System .
Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var=\”s_jdktop\” what
so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version
so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like
java version \”1.4.2_10\”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)
Which means you are using JDK 1.4.2 in Oracle Applications 11i.