Oracle HRMS – Define Organization – Functional and Technical Overview

Organizations in HRMS

1. Organizations in HRMS
2. Creating Organizations and Classifications
3. Organizations in Other Products
4. Operating Units and Multiorg
5. Business Groups
6. Organizations and Managers
7. Org Developer DFF
8. Technical Details

1. Organizations in HRMS
Organizations are used in HRMS to represent a business.
Business groups, External organizations (for example, tax offices, insurance carriers, disability organizations, benefit carriers, or recruitment agencies), Internal organizations (for example, departments, sections or cost centers) and GREs/Legal Entities can be created as organizations.
In HRMS an employee assignment will be assigned to an organization to show where he works within the business. When the assignment is created (a default assignment is automatically created when a person is hired as an employee) the business group is defaulted in as the assignment’s organization. This would usually be corrected to a lower level organization to identify his department. As many organizations as are required can be created. This will depend on the business and to what level it is required to hold information. Organization hierarchies can be created to show how organizations fit together in a tree structure. Hierarchies are not covered in this note.

2. Creating Organizations and Classifications

An organization requires a start date and a name. You can optionally enter location details and Organization Type. The Organization Type is used for reporting only. There is a seeded LOV for this field and it is possible to add additional types to that LOV.
The organization can be either Internal or External. People can only be assigned to Internal organizations (in the Assignments screen). The Classification of the organization allows specific information to be held according to the purpose of the organization. Examples of classifications are: Business Group, HR Organization, Inventory Organization, GRE/Legal Entity, Company Cost Center.
The LOV for classifications comes from the lookup type ORG_CLASS. An organization can have as many classifications as it requires. To be able to use the organization on the Assignment screen in HRMS it will need a classification of ‘HR Organization’.
For each classification selected, more information can be held about that organization. Each classification has a number of Organization Information Types each of which comprises a set of fields. When the ‘Others’ button is pressed in the Define organization screen, a LOV shows the information types for the selected classification. When a information type is selected from this list the set of fields for that org type are displayed.
For example for the Business Group classification there are information types:
Business group Info
Benefits Defaults
Budget Value Defaults
Tax Details Reference
Work Day Information
….. etc
And each of these information types has a number of fields associated with it.

3. Organizations in other products
Other products within Oracle Applications use organizations. The classifications and information types are used to ensure the required information about any kind of organization is stored.
All organizations are created and maintained using the Define Organization screen which is owned by the HR product group. So while the data being entered may not be HR related, it is often the case that HRMS Support would work on a SR relating to, for example, an Inventory Organization. When you understand the concepts of classifications and organization types and how the Org Developer DF flexfield works, it is usually possible to investigate a problem with any organization.

4. Operating Units
You can create an operating unit using the Define Organizations form but this requires that you first create a Set of Books.

5. Business Groups
Business Groups are just organizations which have the classification ‘Business Group’ Each organization will have a BUSINESS_GROUP_ID to identify which business group it was created from. A business group will have identical values for BUSINESS_GROUP_ID and ORGANIZATION_ID. Organizations from all business groups can be queried up in the Define Organizations screen. However only organizations for the current business group and the classification ‘HR Organization’ can be used in the Assignment screen.

6. Organizations and Managers
The Organization Manager Relationship screen (Work Structures->Organization->Organization Manager) is used to enter and query the manager for an organization. This information can also be entered in the Define Organization screen for organizations with a classification of Company Cost Center:

Query up the organization and highlight the classification Company Cost Center.
Press the Others button and select Report Information.
Managers for this organization can be entered here and, once saved, will also be displayed in the Organization Manager Relationship screen.

7. Org Developer dff
The seeded descriptive flexfield ‘Org Developer DF’ is used to define Organization Information Types.
This DFF is context sensitive and each context value is an information type. The segments defined for each context map to the fields displayed for that information type when the information type is selected from the ‘Others’ button on the Define organization screen.  When the ‘Other’s button is pressed, not all of the information types are listed. The table HR_ORG_INFO_TYPES_BY_CLASS lists which org types are available for each classification and so the list is restricted according to the highlighted classification.
When one of the listed information types is selected, the dff segments for that information type (context) are displayed in a popup box. The dff is seeded and should not be unfrozen and changed by a customer.

8. Technical Details
The basic details of an organization are stored here. There is also a translation table HR_ALL_ORGANIZATION_UNITS_TL for the organization name in each language installed.

This holds the different types of organization information. Each type is defined in the Org Developer DF as a context value. eg Work Day Information, Business Group Information, etc. When using the Organizations screen you would see the information types relevant to the org classification.

This table holds the org information types that are available for each classification. This data is seeded and there is no screen that displays these groupings. When you select a classification on the Org screen and press the Other button, this table is referenced to display the relevant org information types.

This table is more complicated as it stores two distinct sets of information.
To know which type of information is stored you need to check the value in column ORG_INFORMATION_CONTEXT. When the value is ‘CLASS’ the row is used to link an organization to a classification. There will be one row for every classification used by an organization. The classification name is held in column ORG_INFORMATION1. If you create an organization, a row will be added here for every classification you save against that org.
When the value is set to an information type (from HR_ORG_INFORMATION_TYPES) the columns ORG_INFORMATION1-20 are used to hold the values for that information type.
So when you are in the Define Organizations screen and you press the others button and select an information type, the values you see in the fields on the screen come from this table.
The information type is a dff context with some segments defined. Each segment is mapped to one of the ORG_INFORMATIONx columns in this table. When you open the information type field you see the individual segments and the value for each segment is held in the column in table HR_ORGANIZATION_INFORMATION that matches the column specified in the segment definition.
The lookup ORG_TYPE holds values for the ‘Type’ field on the Define Organization screen.
The lookup ORG_CLASS holds values for the Classifications Name field on the Define Organization screen.

How can I find the LOV used by a segment in the Information Type I select from the ‘Others’ button?
Query up the dff Org Developer DF and select the context whose name matches the name of the Information type you selected from the ‘Others’ button.
The Segments screen lists the fields defined for that information type. The LOV will come from the validation used by the relevant segment.
If a valueset is used then the LOV will be based on a sql statements if Table Validation is specified; or values defined in the Segment Values screen (Application->Validation->Values) if dependent or independent validation is specified.

Can I define 2 organizations with the same name?
This is possible but is not recommended, and certainly not recommended when the system profile HR: Cross Business Group is set to ‘Yes’.

Where is the information from the Organization Manager Relationship screen held?
The Organization Manager Relationship screen uses the Org Developer DF dff. It is just another UI to the information type ‘Reporting Information’ for an organization with a classification of Cost Center.
Only organizations with a classification of Cost Centre can be queried in the Organization Manager Relationship screen.

What sql can I run to show the classifications defined for an organization?
This sql will prompt you for an organization name

select i.organization_id,, l.meaning
from hr_all_organization_units o
, hr_organization_information i
, fnd_lookup_values l
where o.organization_id = i.organization_id
and = ‘&Organization_Name’
and i.org_information1 = l.lookup_code
and l.lookup_type = ‘ORG_CLASS’
and i.org_information_context = ‘CLASS’;

What sql can I run to see the segment values for a specific Information Type for my organization?
This will be different depending on which Information Type you select.
First open the Org Developer DF and find the structure for the Information type you require eg Work Day Information
See the COLUMN value for each segment.
In this example:
Segment Name Column
Normal Start Time ORG_INFORMATION1
Now create the sql selecting those columns from HR_ORGANIZATION_INFORMATION
This sql will prompt you for an organization name

, i.org_information1 Normal_Start_Time
, i.org_information2 Normal_End_Time
, i.org_information3 Working_Hours
, i.org_information4 Frequency
from hr_organization_information i
, hr_all_organization_units o
where = ‘&Organization_Name’
and o.organization_id = i.organization_id
and i.org_information_context = ‘Work Day Information’;

To create the sql for a different Information Type simply replace the SELECT section with the COLUMNs and aliases for that Information Type.

thanks – Shiv

Oracle iExpense – How To Configure Expense Report Numbers?

You can configure Expense Report Number  like customize the format of the expense report number to include such things as employee ids and the date an expense report was submitted.
To acheive this, You create custom expense report numbers by modifying the plsql function


If you do not implement this function, Internet Expenses uses the current numbering sequence.

The AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice function is located in the directory $AP_TOP/patch/115/sql/apwcstmb.pls.

The input parameters passed for this function are:
p_employeeId – IN ap_expense_report_headers.employee_id%TYPE
p_userId – IN fnd_user.user_id%TYPE
p_reportHeaderId – IN ap_expense_report_headers.report_header_id%TYPE

The returned value is the new expense report number.

Oracle iExpense – How Can the Global Policy Icon on the Top of the Page in Internet Expenses be Removed?

How Can the Global Policy Icon on the Top of the Page in Internet Expenses be Removed?

In order to remove the Global Policy button, please follow the following steps:

1) Login to the Application Developer responsibility
-Navigate: Application -> Menu

2) Query the menu ‘OIE_EXPENSES_GLOBAL’

3) Delete the ‘Global Policy’ function from the menu

4) Save

5) Bounce the Apache Server

Oracle Payables – Interview FAQ – Invoice Payment

What is the difference between the Electronic and Wire payment methods?

In short, the Electronic method enforces the existence of a supplier bank
account and the Wire method does not.  Therefore, use the Electronic payment
method to generate instructions to your bank to make payment to a supplier bank account.  Use the Wire payment method to record payment when you have used a process outside of your Oracle Payables system to instruct your bank to pay a supplier.

Electronic Payment

Use the Electronic payment method when Payables will create instructions for your bank to make payment to a supplier bank account. Typically, this communication is an electronic file that instructs your disbursement bank to pay your suppliers, and is in the specific format that your bank requires.  However, you should use the Electronic payment method whenever you need to generate a document that requires a supplier bank account.  For example, use it if your Payables system is set up to print letters that you send to your bank to request that the bank make an electronic funds transfer directly into the supplier’s bank account.

Payables ensure that you have recorded supplier bank account information when you use the Electronic payment method. Typically, to pay invoices with the Electronic payment method, users use a payment document with a Computer Generated disbursement type and use a payment batch or Quick payment to create a payment instruction file.  The payment instruction file is saved in the ap.out directory for delivery to the bank, unless one of the following features is used to automatically transmit the instruction file to the bank: e-Commerce Gateway (for EDI payments), Automatic Bank Transmission, XML Payment Processing.

However, you can use localizations, or custom payment methods and payment formats to create any type of communication with your bank when you use the Electronic payment method.

Wire Payment

Use the Wire payment method to manually record payment when you have used a process outside of your Oracle Payables system to instruct your disbursement bank to pay a supplier. Oracle Payables does not require supplier bank account information when you use the Wire payment method. When you define payment documents for these payments, we recommend you use the Recorded disbursement type because you are simply recording a payment made outside of the system.  Further Oracle recommends that you record the transaction with a manual payment.

(However, the system will allow you to use any disbursement type.  For example, some users who regularly record Wire payments for multiple suppliers use payment documents with the Computer Generated disbursement type, create an electronic payment batch, and then delete the resulting electronic file.)


What is the Payment Batch processing flow?

AutoSelect – First you initiate the Payment Batch by entering criteria for invoices
Build – The system then builds payments based on the selection criteria.
Modify – You can optionally modify the payment batch.
Format – Format payments to have Payables produce an output file.
Print – Print checks from the output file or deliver the output file to your bank.
Confirm  – Confirm the payment batch.

I selected a Payment Document for my Bank Account that says it is in use.

When you selected the Payment Document for the Bank Account, the Document Names window displays a column “In Use By”.  This is the Payment Batch name that is using the Payment Document.  You must complete the Payment Batch using that document before you can use it.  You will only get the list of payment documents if there is more than one payment document associated with that bank account.  If there is only one payment document for that bank account, you will simply get the message that the payment document is in use.  If you still do not see a Payment Batch, then most likely a QuickCheck did not complete successfully.

How do I add an invoice to the Payment Batch?

1. From the Actions Window, unselect Format and select Modify Payment Batch.
2. Enter the Supplier Name and Site.
3. Select Yes for Pay Supplier.
4. Select the invoice you wish to add to this Payment Batch.
5. Select Done.
The system will automatically submit the Build Payments program to rebuild the payments.

When can I modify a Payment Batch?

After the Payment Batch has been Built and before it has been Formatted.  Once the payment batch has been Formatted, Modify is no longer an option.

Can I add an invoice that has been excluded because the payment batch exceeds the maximum outlay.

Yes.  Select the Supplier and Site and choose ‘Force’ as the Pay Option in the Modify Payments Window.


How do I  reprint checks after payment batch has been formatted.

1. Navigate to the concurrent request summary form (Other -> Concurrent)
2. Either query the format payment concurrent request, or query all and manually search for the format payments request.
3. Select Special…Reprint from the toolbar.
4. This opens the reprint dialog box.
5. Select the number of copies, the printer, and the print style
6. Press OK

Can I cancel a Payment Batch that has been confirmed.

No.  You must void each payment created by the Payment Batch to accomplish this

How do I determine the status of the Payment Batch?

Query up the Payment Batch in the Payment Batches Summary window to view the status.  If you can not get into the application, you can select the status from SQL*Plus:

    SELECT status
    FROM ap_inv_selection_criteria_all
    WHERE checkrun_name = ‘<payment batch name>’



Oracle EBS – Commonly used / basic Unix commands

Here showing and sharing some basic commonly used Unix commands used in support purpose, Do refer regular Unix book or guide to know more about, below is just a quick reference.



This command show a calendar for a specified month and/or year.


To show this month’s calendar, enter:

> cal

To show the twelve-month calendar for 2010, enter:

> cal 2010

To show a calendar for just the month of May 1963, enter:

> cal 5 1963



This is one of the most flexible Unix commands. You can use to create, view and concatenate files.


To create a file called test:

> cat >test   

To view a file use cat in a different way:

> cat test

To write the contents of the file mytestfile to standard output with

each line numbered:

> cat -n mytestfile

To concatenate two files into one, files test1 and test2 and write the

result to test.all use:

> cat test1 test2 > test.all



To change directory. Use this command to change directories.


To change FND_TOP directory enter:

> globsuptxxxxx:/home/globsupt cd $FND_TOP

cd ~ Go back to home directory, useful if you’re lost.

cd .. Go back one directory.



To changes the permissions on a file.


To change the permissions on file ‘test.txt’ to read only:

> chmod 444 test.txt

chmod 600 {filespec} You can read and write; the world can’t. Good for files.

chmod 700 {filespec} You can read, write, and execute; the world can’t. Good for scripts.

chmod 644 {filespec} You can read and write; the world can only read. Good for web pages.

chmod 755 {filespec} You can read, write, and execute; the world can read and execute. Good for programs you want to share, and your public_html directory.


This command clears the terminal screen.


To clear the terminal screen, enter

> clear


Use this to copy files.


To make a copy of the file ‘test.txt’ as ‘test.bak’:

> cp test.txt test.bak

Use this command to check the date and time.


> date

Thu Mar 11 19:42:14 GMT 2010


It shows the available disk space.


> df (-k will show values in kilobytes)

The echo command echoes its arguments. Use it to determine if certain environment variables are set. Precede the variable you would like to see by a dollar sign ($).


To find out the location of the AR_TOP:

> globsuptxxxxx:/home/globsupt echo $AR_TOP

To find the default printer:

> echo $PRINTER

To show the contents of an environment variable, for example, ‘ORACLE_HOME’:


Use this command to find a particular file or groups of files.


To find the file wfver.sql, you may use:

> find / -name filename.wfver.sql -print

Modify the above command to search the current directory and its

subdirectories by replacing the slash with a period.

> find . -name filename.wfver.sql -print

To list all files in your home directory by using the following:

> find $HOME -print

Other examples of usage:

find / -name filename.GLYRLJGE.rdf -print

find /$FND_TOP/sql -name wf* -print

find / -name wfver -type f -print

find -name wfver -type f -print


Use this command to search for information in a file or files.


To check the status of the managers from the OS.

> ps -ef | grep LIB

To get a list of process on the unix machine corresponding for a user:

> ps -ef| grep <username>

If you are tracing a material transaction, you might want to grep for mtl_material_transactions to find the correct trace.


Provides the hostname of the box you are currently working on. This is particularly useful when you have telnet sessions to several boxes at one time and when there is confusion as to which box you are currently on.


>  hostname

Prints a file on a printer connected to the computer network


To print the file test.txt to printer named hp123:

>  lp -d hp23 test.txt

This shows the contents of a particular directory.



Useful command that can help you figure out the syntax and purpose of most UNIX commands. Enter ‘man’ followed by the command you would like to execute, and you will get a quick rundown of what the command does and a description of how to use it.


> man ls         Displays details about the ls command.


Use this command to create new directory.


To create a subdirectory in the home directory of your Unix account, use the mkdir command. For example, to create a subdirectory called Test, at the Unix prompt from within your home directory, enter.

>  mkdir Test

Shows listing one screen at a time. More is a command used to read text files.


Use this command to move or rename files.


To rename the file ‘test.txt’ to ‘test.old’:

> mv test.txt test.old

To move a file named Test from a subdirectory named my_new to another subdirectory named my_old (both subdirectories of the&nbsp;current directory), enter:

> mv -i my_new/Test my_old

To see list of all the operating system processes that are running.



It means present working directory. Use this command to find out what directory you are working in.



Use this command to set envirorment variables.


To change the default printer from hpx123 to myprinter:

> echo $PRINTER
> setenv PRINTER myprinter
> echo $PRINTER

The vi editor (short for visual editor) is a screen editor which is available on almost all Unix systems.


To see the file, afffcfb.sql in editor, enter:

> vi afffcfb.sql
:q is the command to exit vi editor

Screen Movement

To move the cursor to a line within your current screen use the following keys:

H moves the cursor to the top line of the screen.

M moves the cursor to the middle line of the screen.

L moves the cursor to the last line of the screen.

To scroll through the file and see other screens use:

ctrl-f scrolls down one screen

ctrl-b scrolls up one screen

ctrl-u scrolls up a half a screen

ctrl-d scrolls down a half a screen

Since oracle files are not to be modified, editing commands are omitted intentionally.

Miscellaneous Unix commands

chgrp – changes the group ownership of a file

chown – changes owner of file

r – To redo the last command typed. Useful when you have typed a long command and do not want to type the entire command again.

w – who is logged in, and what are they doing

wc – display a count of lines, words and characters in a file

which – locate a command; display its pathname or alias

who – who is on the system

whoami – display the effective current username

whois – Internet user name directory service

Oracle Apps EBS – Technical Common SQL/ Scripts for regular investigations

These scripts are meant to provide the most commonly requested information. Functional analysts with SQL and Unix access should be able to run these scripts and provide the information to Oracle Support.

1. How to find versions of files in packages?

select text from dba_source
where name like ‘%&PKG_NAME%’
and line = 2;


select text
from dba_source
where name = ‘GLRX_JOURNAL_PKG’
and line = 2;


2. How to check if a patch is applied?

select * from ad_bugs where bug_number = &bug_number;

select * from ad_applied_patches where patch_name = &bug_number;
SELECT DISTINCT a.bug_number,e.patch_name,
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE ‘123456’

3. How to find the patch set level for an application?

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%&short_name%’;


select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like ‘%AP%’;

4. How to find instance name, host name, apps and RDBMS versions of the instance user is logged into?

select i.instance_name, i.host_name,
f.release_name release, i.version

from v$instance i,
fnd_product_groups f where upper(substr(i.instance_name,1,4)) =

5. How to find the latest version of a file on a given instance?

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘&file_name’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id =

Example 1 – finding java class version of a file).

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%FrmSheet1VBA%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Example 2 – Finding version of .fmb file.

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE ‘%GLXJEENT.fmb%’ AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Note: This works for .class, .fmb .pls, .o, and .sql files only. Doens’t work
for .lpc,.lc files etc. If querying for a Form, please use GLXJEENT.fmb
as glxjeent.fmb will not work.

6. How to check the installation status and patch set level for a product?

Example 1

select patch_level, status from fnd_product_installations
where patch_level like ‘%FND%’;

Example 2

select patch_level, status from fnd_product_installations
where patch_level like ‘%XDO%’;

7. How to backup a table before users use sql to update the apps tables?

Example 1:

Create table ap_invoices_all_bkp as select * from ap_invoices_all;

Example 2:

Create table gl_interface_bkp as select * from gl_interface;

8. How to find the table(s) name with the column name?

User knows the column_name but not sure what table(s) the column name is in.


select * from dba_tab_columns where column_name like ‘%SET_OF_BOOKS_ID%’;

This will provide the names of all the tables that has column_name SET_OF_BOOKS_ID.

9. How to check for invalid objects in a particular module?

where OBJECT_NAME like ‘FND_%’

where OBJECT_NAME like ‘AP_%’

10. How to check for invalid objects in all the modules?

select owner, object_name, object_type from dba_objects
where status = ‘INVALID’
order by object_name, object_type;

11. How to find the applications in the system that are either installed shared?

select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level FROM
fnd_product_installations FDI
and fdi.status in (‘I’, ‘S’)

Note: Status ‘I’ meaning installed and status ‘S’ meaning shared.

12. How to determine database character set?

select value from nls_database_parameters
where parameter = ‘NLS_CHARACTERSET’;

The following scripts will provide NLS parameter and value for database, instance and session.

select * from nls_database_parameters;
select * from nls_instance_parameters;
select * from nls_session_parameters;

13. How to check the indexes on a table?


select index_owner owner,table_name tab, index_name ind,
column_name colu, column_position position
where table_name = ‘GL_CODE_COMBINATIONS’;

14. How to check for custom triggers on seeded tables?

select trigger_name, owner
from dba_triggers
where table_name = ‘GL_BALANCES’;

15. How to get the header file versions for an executable in Unix?

Example 1
Log into UNIX.

cd $AP_TOP/bin
strings -a APXXTR | grep Header

Example 2
cd $RG_TOP/bin
Strings -a RGRARG |grep Header
The above will provide the versions of all the header files in those executables.