Oracle Apps 11i: OATM migration utility

How to run OATM migration utility Note: 404954.1

Applies to: Oracle Application Object Library - Versio 11.5.10.2
Information in this document applies to any platform.

Goal

1- Review OATM Model for Oracle Applications 11i
2- Install OATM migration utility
3- Run OATM migration utility
4- Parameters needed while starting OATM utility
5- OATM Menu Structure

Solution

1- Review OATM Model for Oracle Applications 11i

The Oracle Applications Tablespace Model (OATM) introduces a new, consolidated tablespace model in Oracle Applications uses 12 locally managed tablespaces for all products including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.
The Oracle Applications Tablespace Migration Utility is an interactive menu-based PERL program including a set of sizing estimate reports. It enables customers to convert their Oracle Applications databases to the new tablespace model (OATM) for either all schemas at once or partially for select schema(s) at a time, depending on acceptable down time and available disk space.

You can find more information about OATM migration utility in the following note:
Note 248857.1 - OATM Release 11i - Tablespace Migration Utility

Also please refer to Oracle Documentation:
http://download-uk.oracle.com/docs/cd/B25516_14/current/acrobat/115sacg.zip

2- Install OATM migration utility
You can install the OATM migration utility by downloading & applying the following patch through Metalink:Patch 3942506 - AOL/FND: OATM MIGRATION UTILITY ROLLUP C
(Note: Above patch is already included in 11.5.10 and higher, so if you have Oracle Applications 11.5.10 or higher you do not need to install it)

/u01/MIG/apps/prodappl/admin/mig/log

3- Run OATM migration utility
After installing the utility through above patch, please use the following command in order to start running the utility:
perl $FND_TOP/bin/fndtsmig.pl
4- Parameters needed while starting OATM utility

After running the PERL script in order to start the OATM utility, the following parameters screen will appear:
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+

Welcome to the Oracle Applications Tablespace Migration Utility

+=======================================================================+

Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.

+=======================================================================+
Enter OATM configuration file or press enter to continue:
Enter APPL_TOP [/u01/MIG/apps/prodappl]:
Enter FND_TOP [/u01/MIG/apps/prodappl/fnd/11.5.0]:
Enter the Database Connect String [MIG]:
Enter the password for your 'SYSTEM' ORACLE schema:
Successfully connected to database MIG using SYSTEM user.
Enter the ORACLE APPS Schema name[APPS]: apps
Enter the password for APPS:
Successfully connected to database MIG using APPS user.
Enter the ORACLE Application Object Library Schema name[APPLSYS]: applsys
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================

Please review and press 'Y' to accept or 'N' to re-enter[Y]: y
Performing database check...
Database check completed.
Checking required components...
Required components check completed.

5- OATM Menu Structure
After providing all parameters as in the previous step, you will get the Main Menu for Oracle Applications Tablespace Migration Utility which consists of 8 options as following:
Important Note: Some Menu Options are available only on 11.5.10.2 and later such as: Migrate CTXSYS Schema, Generate script to drop empty tablespaces.


Oracle Applications Tablespace Migration Utility
Main Menu

1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option -
Here are the details for each menu option:

5.1. Migration Sizing Reports
Migration Sizing Reports

These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs

1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report

[Q]uit [B]ack [N]ext

Please enter your option -
5.2. Create New Tablespaces
Create New Tablespaces


1. Generate new tablespace creation script
2. Create new tablespaces


[Q]uit [B]ack [N]ext

Please enter your option -

5.3. Generate Migration Commands

Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.


1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas


[Q]uit [B]ack [N]ext

Please enter your option -
5.4. Execute Migration Commands

Execute Migration Commands
Execution of Migration commands including disable commands for triggers constraints, stop/start for queues.

PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION

1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema


Note: Migrate CTXSYS schema when no other migration process is in progress.

[Q]uit [B]ack [N]ext

Please enter your option -
5.5. Run Migration Status Reports
Run Migration Status Reports

1. Run migration progress report
2. Run migration error report


[Q]uit [B]ack [N]ext

Please enter your option -
5.6. Run Post Migration Steps
Run Post Migration Steps

1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces


Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.

[Q]uit [B]ack

Please enter your option -
5.7. Customization
Customization

1. Register new tablespace - tablespace type
2. Change name of the existing tablespace
3. Register object classification
4. Change object classification


[Q]uit [B]ack
Please enter your option -
5.8. Batch Mode Migration
Batch Mode Migration

1. Run Migration in Batch Mode
2. Run Migration Monitor

[Q]uit [B]ack

Please enter your option -


OATM migration


applprod@vezora1:~ $ cd /u01/MIG/apps/prodappl/fnd/11.5.0/bin
applprod@vezora1:/u01/MIG/apps/prodappl/fnd/11.5.0/bin $ perl fndtsmig.pl
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+

Welcome to the Oracle Applications Tablespace Migration Utility

+=======================================================================+

Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.

+=======================================================================+

Enter OATM configuration file or press enter to continue:

Enter APPL_TOP[/u01/MIG/apps/prodappl]:

Enter FND_TOP[/u01/MIG/apps/prodappl/fnd/11.5.0]:

Enter the Database Connect String[MIG]:

Enter the password for your 'SYSTEM' ORACLE schema:

Successfully connected to database MIG using SYSTEM user.

Please create log directory /u01/MIG/apps/prodappl/admin/mig/log to continue:
Enter the ORACLE APPS Schema name[APPS]: ma6utoza

Enter the ORACLE APPS Schema name[APPS]:

Enter the password for APPS:

Successfully connected to database MIG using APPS user.

Enter the ORACLE Application Object Library Schema name[APPLSYS]:
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================


Please review and press 'Y' to accept or 'N' to re-enter[Y]: Y

Performing database check...

Database check completed.

Checking required components...

Required components check completed.

Oracle Applications Tablespace Migration Utility
Main Menu


1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode

[Q]uit [N]ext

Please enter your option - 1

Migration Sizing Reports

These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs


1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report

[Q]uit [B]ack [N]ext

Please enter your option - 1


Migration Progress Report
Report Date : February 14, 2008 PAGE: 1
Total generated % commands
Schema commands successfully executed
--------------- --------------- -----------------------------------------
ABM 0 0%
AHL 0 0%
AHM 0 0%
AK 0 0%
ALR 0 0%
AMF 0 0%
………………………
…………………………
………………….
XNB 0 0%
XNC 0 0%
XNI 0 0%
XNM 0 0%
XNP 0 0%
XNS 0 0%
XTR 0 0%
ZFA 0 0%
ZPB 0 0%
ZSA 0 0%
ZX 0 0%

Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep0.txt


Press Return key to continue...

Migration Sizing Reports

These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs


1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report

[Q]uit [B]ack [N]ext

Please enter your option - 2
Sizing Program has never been run.


Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:
Executing the Sizing program for all schemas. This may take upto 20min. Please wait...
Sizing program executed successfully.
PAGE: 1
Summary Report-Amount of space by tablespace
Report Date : February 14, 2008
Last Sizing Run Date : February 14, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>

Current space
Recommended taken up by
Estimated space space in KB existing
Tablespace Name required in KB (20% Buffer) objects in KB
------------------------------ ---------------- --------------- ---------------------------------------
APPS_TS_ARCHIVE 6,228,992 7,474,791 2,061,240
APPS_TS_INTERFACE 15,163,392 18,196,071 11,089,784
APPS_TS_MEDIA 3,638,272 4,365,927 3,570,144
APPS_TS_NOLOGGING 662,528 795,034 342,200
APPS_TS_QUEUES 3,369,984 4,043,981 2,825,328
APPS_TS_SEED 7,327,744 8,793,293 2,129,744
APPS_TS_SUMMARY 6,267,904 7,521,485 1,135,208
APPS_TS_TX_DATA 61,418,496 73,702,196 50,858,192
APPS_TS_TX_IDX 66,878,464 80,254,157 45,371,752
------------------------------------- --------------- ------------------------------------------------
Total (in KB): 170,955,776 205,146,935 119,383,592

Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt

Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt

Press Return key to continue...

Migration Sizing Reports

These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs


1. Generate a Report with the list of all the Oracle
Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant) for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report

[Q]uit [B]ack [N]ext

Please enter your option - 2

Sizing Program was last run on 14-FEB-08
Do you want to run the Sizing program again before running the report[N]: Y


PAGE: 1
Summary Report-Amount of Space by Schema by Object
Report Date : February 15, 2008
Last Sizing Run Date : February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>

Object Estimated Space Sizing
Schema Name Required (in KB) Errors
-------------------- ------------------------------ ---------------- ------------
ABM ABM_VIEW_SEC_OBJS_U2 1,024 No Error
ABM_CALC_TMPLTS_TL_PK 1,024 No Error
ABM_CALC_TMPLT_VARS_TL_PK 1,024 No Error
ABM_CURRS_TL_PK 1,024 No Error
ABM_IMP_ENT_TYPES_TL_PK 1,024 No Error
ABM_IMP_TAGS_TL_PK 1,024 No Error
ABM_IMP_TAG_SETS_TL_PK 1,024 No Error
ABM_REP_TMPLTS_TL_PK 1,024 No Error
ABM_BOR_WIZ_CHILD_RES_TREE_PK 1,024 No Error
ABM_API_TEMPLATES_PK 1,024 No Error
ABM_API_TEMPLATE_ATTRIBUTES_PK 1,024 No Error
ABM_NAV_STEPS_PK 1,024 No Error
ABM_NAV_STEPS_U1 1,024 No Error
ABM_IMP_TAGS 1,024 No Error
ABM_IMP_TAG_ENTS 1,024 No Error
ABM_IMP_TAG_SETS 1,024 No Error
ABM_IMP_UOM_CV_RULES 1,024 No Error
ABM_LAUNCHPAD_METADATA 1,024 No Error
ABM_MAP_BASES 1,024 No Error
ABM_MAP_EXPTN_REP 1,024 No Error
ABM_MAP_FORM_LOG 1,024 No Error
ABM_MAP_FORM_MASTERS 1,024 No Error
ABM_MAP_RULE_SETS 1,024 No Error
ABM_MAT_UNIT_COST 1,024 No Error
******************** ----------------
Total (in KB) : 424,960

PAGE: 2
Summary Report-Amount of Space by Schema by Object
Report Date: February 15, 2008
Last Sizing Run Date: February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>


Migration Sizing Reports

These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs

1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report

[Q]uit [B]ack [N]ext

Please enter your option - 5

Enter the Schema name[%]:
Running Exception Report ..............

There are no objects in errors

Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep5.txt
Press Return key to continue...

Oracle Applications Tablespace Migration Utility
Main Menu


1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode

[Q]uit [N]ext

Please enter your option - 2
Create New Tablespaces


1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext

Please enter your option - 1
*****************************************************************
The utility will append a sequence number to the tablespace name
and a .dbf extension to generate the datafile names.
Datafile size should not be greater than OS file size limit.
Please edit the generated script to change the file name/size
*****************************************************************
Enter the Extent Allocation type A(utoallocate) or U(niform Extent Size)[U]: U

Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:

Enter the absolute path for the datafiles directory: /u02/MIG/dbf
Enter the Number of Datafiles for APPS_TS_TX_DATA tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_DATA tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_TX_IDX tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_IDX tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SEED tablespace[1]: 2
Enter the Datafile Size for APPS_TS_SEED tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_INTERFACE tablespace[1]: 5
Enter the Datafile Size for APPS_TS_INTERFACE tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SUMMARY tablespace[1]: 5
Enter the Datafile Size for APPS_TS_SUMMARY tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_NOLOGGING tablespace[1]: 2
Enter the Datafile Size for APPS_TS_NOLOGGING tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_ARCHIVE tablespace[1]: 7
Enter the Datafile Size for APPS_TS_ARCHIVE tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_QUEUES tablespace[1]: 5
Enter the Datafile Size for APPS_TS_QUEUES tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_MEDIA tablespace[1]: 5
Enter the Datafile Size for APPS_TS_MEDIA tablespace (MB): 1000

Tablespace creation script /u01/MIG/apps/prodappl/admin/mig/log/crtts.sql created

/u01/MIG/apps/prodappl/admin/mig/log ***scripts is in this directory **

Press Return key to continue...

Create New Tablespaces

1. Generate new tablespace creation script
2. Create new tablespaces

[Q]uit [B]ack [N]ext
Please enter your option - 2
Creating Tablespaces. Please wait...
Connected.

PL/SQL procedure successfully completed.

Tablespace created.

Tablespace created.

Tablespace created.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.
Tablespace altered.


Tablespaces creation script executed. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/crtts20080216215009.log for errors.
Press Return key to continue...

Create New Tablespaces


1. Generate new tablespace creation script
2. Create new tablespaces


[Q]uit [B]ack [N]ext

Please enter your option - B

Oracle Applications Tablespace Migration Utility
Main Menu


1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode

[Q]uit [N]ext

Please enter your option - 3

Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.


1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas


[Q]uit [B]ack [N]ext

Please enter your option - 1


Report created /u01/MIG/apps/prodappl/admin/mig/log/fndinvld.txt

Press Return key to continue...
Press Return key to continue...

Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.


1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas


[Q]uit [B]ack [N]ext

Please enter your option - 2

Generating Migration commands for all schemas. This may take upto 30min. Please wait...

Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log

Press Return key to continue...

Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.


1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas


[Q]uit [B]ack [N]ext

Please enter your option - 2


Generating Migration commands for all schemas. This may take upto 30min. Please wait...


Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log

Press Return key to continue...

Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.


1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas


[Q]uit [B]ack [N]ext

Please enter your option - B

Oracle Applications Tablespace Migration Utility
Main Menu


1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode

[Q]uit [N]ext

Please enter your option - 4

Execute Migration Commands
Execution of Migration commands including disable
commands for triggers constraints, stop/start for queues.

PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION

1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema

Note: Migrate CTXSYS schema when no other migration process is in progress.

[Q]uit [B]ack [N]ext

Please enter your option - 1

Are you sure you want to migrate all schemas[N]: Y
Enter the maximum number of parallel processes for MIG[8]:

Starting the Migration process for all schemas. Please wait...

Migration processes for tables with LONG and LONG RAW columns started in the background. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndmlong20080216230605.log for errors and completion of this migration process.

Sequential migration process started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemseq20080216230606.log for errors and completion of this migration process.

Parallel migration processes started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemcmd20080216230622.log for errors and completion of this migration process.

OATM Utility processes are running in the background. sql*plus sessions and one JRE session are spawned in the background. You can monitor those sessions from the OS. Please do not kill any of those sessions as it will adversely affect migration process and result in errors. Monitor migration by running migration progress report.

Press Return key to return to the menu...

Press Return key to continue...

Run Post Migration Steps


1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces


Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.

[Q]uit [B]ack

Please enter your option - 2

Enter a comma separated list of Schema names[%]:


Enabling Constraints/triggers/policies and starting queues. Please wait...


All constraints/trigger/policies are enabled and queues started

Press Return key to continue...

Run Post Migration Steps


1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces


Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.

[Q]uit [B]ack

Please enter your option - 4
+=======================================================================+
Warning: Please ensure you do not have any residual data segments
in the tablespaces attempted to be dropped to avoid irrevocable
loss of data.
+=======================================================================+


Drop empty tablespace script /u01/MIG/apps/prodappl/admin/mig/log/fndtsdrp.sql has been created.


Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @fndtsdrp.sql;

Tablespace dropped.


Tablespace dropped.
Tablespace dropped.


Tablespace dropped.


Tablespace dropped.

SQL> commit;

Commit complete.


OATM How to move objects left in the old tablespace after oatm migration?

Solution
1) Run the 'Migration Error Report' and correct the errors that cause the migration to fail.

2) Make sure that queue 'TBLMIG_MESSAGEQUE' is started

select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where owner ='SYSTEM' and name = 'TBLMIG_MESSAGEQUE' ;

In case the queue is not started, run from sqlplus as the system user:

exec dbms_aqadm.start_queue('SYSTEM.TBLMIG_MESSAGEQUE');

3) Run the migration command again and the migration utility will try to move the objects which are still in the old tablespaces



Drop SWALLACE and SPOTLIGHT USERS


drop user spotlight cascade;
drop user swallace cascade;

Note:430604.1
CREATE TEXT INDEX DRG-11446 DROLDUS.DAT NOT INSTALLED



PATCH 4575748 OATM RE-SIZE OLD TABLESPACES DOES NOT RESIZE DATAFILES


IMPORTANT NOTE :

One-off patches are not fully certified by all Oracle products, and so there is some risk of unexpected side effects. If the fix in this patch is included in the latest released ATG Family Packs and/or ATG PF Consolidated Updates (CU), then it is highly recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) that is available in Recommended Patch List (Metalink Note 123456.1). It is also recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) before applying this patch.

This patch provides implementation that will correctly resize database file after migration is done

ERROR resolution


SQL> @irctxido1.sql HR APPLSYS APPS CTXSYS

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @irctxipt HR APPLSYS APPS CTXSYS

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @peredind.sql HR APPLSYS APPS CTXSYS

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @ibcinctx.sql

PL/SQL procedure successfully completed.


Commit complete.
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:26:35 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @icxintm2.sql icx icx CTXSYS
DECLARE
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10502: index ICX_QUES_CTX does not exist
DRG-13201: KOREAN_LEXER is desupported
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 31

HACK THE USER SEE SCRIPTS

applprod@vezora1:/u01/MIG/apps/prodappl/icx/11.5.0/patch/115/sql


SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:29:25 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @jtfiimt.sql jtf jtf
ERROR:
ORA-01017: invalid username/password; logon denied

HACK THE USER SEE SCRIPTS

applprod@vezora1:/u01/MIG/apps/prodappl/jtf/11.5.0/patch/115/sql


Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @amviimt.sql amv amv

PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
DECLARE
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10701: preference AMV.KOREAN_LEXER already exists
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 26
ORA-06512: at line 76

CHECK ISSUE

applprod@vezora1:/u01/MIG/apps/prodappl/amv/11.5.0/patch/115/sql


POST MIGRATION


1) APPLSPE

OWNER SEGMENT_NAME SEGMENT_TYPE
SYS 17.22 SPACE HEADER

SQL> alter tablespace APPLSPE offline;

Tablespace altered.

SQL> drop tablespace APPLSPE including contents and datafiles cascade constraints;

Tablespace dropped.


2) AMVD

select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CSD';

alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);

alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$R move lob(data) store as (tablespace APPS_TS_TX_DATA);

alter table cs.DR$SUMMARY_CTX_INDEX$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);

alter table cs.DR$SUMMARY_CTX_INDEX$R move lob(data) store as (tablespace APPS_TS_TX_DATA);


select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = 'CSD';

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
CS SYS_IOT_TOP_2516234 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$K CSD
CS SYS_IOT_TOP_2516196 IOT - TOP DR$SUMMARY_CTX_INDEX$K CSD
CS SYS_IOT_TOP_2516249 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$N CSD
CS SYS_IOT_TOP_2516231 IOT - TOP DR$SUMMARY_CTX_INDEX$N CSD

3) CTXD

select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CTXD';

alter table ctxsys.DR$PART_STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);

alter table ctxsys.DR$STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);

alter table ctxsys.DR$NVTAB move lob(val) store as (tablespace APPS_TS_TX_DATA); ISSUE !!


4) APPLSYSX

select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'APPLSYSX';

no rows selected

select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = ' APPLSYSX';

no rows selected



---------------------------------------------
alter table AQ$_WF_CONTROL_D move lob(data) store as (tablespace APPS_TS_TX_DATA)
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online

Procedures to Remove Orphan AQ Objects which Fail to Drop via DBMS_AQADM Note:236898.1

DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.AQ$_WF_CONTROL_D');

OATM migration leaves WF_CONTROL objects in the old tablespace Note:418238.1

Solution
1.First stop WF related services like WF Mailer Agent, WF listener, etc.

2.Then run following SQL :
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfctqrec.sql apps/apps

(This script should recreate the WF_CONTROL queue) PROBLEM






Migration of 02-March 2008

1) APPLSYSX


select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME

APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX

Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_QUEUES;
Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_QUEUES;



2) APPLSYSD

SQL> select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';

SEGMENT_NAME SEGMENT_TYPE

AD_FRAGMENT_REPORT TABLE
FND_LOBS TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
18.51829 SPACE HEADER


select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'AD_FRAGMENT_REPORT';

select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_IL0000076155C00004$$';


select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';

OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$

select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';

OWNER TABLESPACE_NAME
----------------------------------------------------------- ------------------------------
APPLSYS APPS_TS_TX_DATA

select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';

OWNER TABLESPACE_NAME
--------------------------------------------------------------------- ------------------------------
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA

alter table system.AD_FRAGMENT_REPORT move tablespace APPS_TS_TX_DATA;

Table altered.



3) APPLSYSX

select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';

SEGMENT_NAME SEGMENT_TYPE

FND_LOBS_U1 INDEX
19.38358 SPACE HEADER

alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
alter tablespace APPLSYSX offline;
drop tablespace APPLSYSX including contents and datafiles cascade constraints;


4) SCSX


select segment_name, segment_type from dba_segments where tablespace_name ='SCSX';

SEGMENT_NAME SEGMENT_TYPE
384.14 SPACE HEADER

alter tablespace SCSX offline;
drop tablespace SCSX including contents and datafiles cascade constraints;

5) APPLSCS

select segment_name, segment_type from dba_segments where tablespace_name ='APPLSCS';

SEGMENT_NAME SEGMENT_TYPE
16.72 SPACE HEADER


alter tablespace APPLSCS offline;
drop tablespace APPLSCS including contents and datafiles cascade constraints;


6) CTXD

select segment_name, segment_type from dba_segments where tablespace_name ='CTXD';

SEGMENT_NAME SEGMENT_TYPE
37.1161 SPACE HEADER


SQL> alter tablespace CTXD offline;
Tablespace altered.
SQL> drop tablespace CTXD including contents and datafiles cascade constraints;





MIGRATION 09/03/2008

APPLSYSD


select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';

SEGMENT_NAME SEGMENT_TYPE

AD_FRAGMENT_REPORT TABLE
FND_LOB TABLE

DR$FND_LOBS_CTX$ TABLE
DR$FND_LOBS_CTX$R TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_IL0002516153C00006$$ LOBINDEX
SYS_IL0002516158C00002$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
SYS_LOB0002516158C00002$$ LOBSEGMENT
SYS_LOB0002516153C00006$$ LOBSEGMENT

18.51829 SPACE HEADER


11 rows selected.

select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';

OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$


select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';

OWNER TABLESPACE_NAME
APPLSYS APPLSYSD

SQL> select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';

OWNER TABLESPACE_NAME
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA

Alter table AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;

Table altered.

Alter table applsys.FND_LOBS move tablespace apps_ts_tx_data;

Table altered.

Alter table applsys.DR$FND_LOBS_CTX$R move tablespace apps_ts_tx_data;

Table altered.

Alter table applsys.DR$FND_LOBS_CTX$I move tablespace apps_ts_tx_data;

Table altered.

Alter table sys.AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;
APPLSYSX

select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';

SEGMENT_NAME SEGMENT_TYPE

FND_LOBS_U1 INDEX

SYS_IOT_TOP_2516161 INDEX

SYS_IOT_TOP_2516156 INDEX

DR$FND_LOBS_CTX$X INDEX

19.38358 SPACE HEADER


alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
Index altered.

alter index applsys.DR$FND_LOBS_CTX$X rebuild tablespace APPS_TS_TX_IDX;

Index altered.


select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME

APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX

Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_TX_IDX;

Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_TX_IDX;

select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';

SEGMENT_NAME SEGMENT_TYPE
19.38358 SPACE HEADER


SQL> alter tablespace APPLSYSX offline;

Tablespace altered.

SQL> drop tablespace APPLSYSX including contents and datafiles cascade constraints;

Tablespace dropped.

Oracle Financials important tables

Oracle General Ledger

Gl_code_combinations

 Setup > Accounts > Combinations

This table stores the valid account combinations.

The value in your chart of account segments are stored in the columns segment1 to segment30 depending on your application configuration.

For example, say your chart of accounts is
Company – Cost Centre – Account
then segment1 = company, segment 2 = cost centre and segment3 = account.
However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.

Another important column is the account_type which signifies your account is an Asset, Liability, Revenue, Expense or Owners Equity account.

Gl_je_batches

 Journals > Enter

This table stores the journal entry batches. Journal entries are batched in General Ledger.

Some columns of interest includes :

• Name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Status
• Default_period_name
• Posted_date
• Posting_run_id

Gl_je_headers

 Journals > Enter

This table stores the journal entry headers. There is always two journal lines for each journal header.

Some columns of interest includes :

• Je_category
• Period_name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Posted_flag
• Je_source
• Name
• Status

Gl_je_lines

 Journals > Enter

This table stores the journal entry lines.

The entered_dr and entered_cr stores the amount in the entered currency whereas the accounted_dr and accounted_cr stores the amount in the functional currency.

Other columns of interest includes :

• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Period_name
• Status
• Description
• Reference_1..reference10 (these columns links back to your Subledgers)

For example, for Purchasing transactions

Reference_1 = ‘PO’
Reference_2 = po_headers_all.po_header_id
Reference_3 = po_distributions_all.po_distribution_id
Reference_4 = po_headers_all.segment? (this is the purchase order number)

Oracle Payables

Ap_invoices_all

 Invoices > Entry > Invoices

This table stores all the invoices you enter. For an invoice to be approved, the total invoice amount must be stored in ap_invoice_distributions_all and ap_payment_schedules_all.

Some columns of interest includes :

• Invoice_num
• Invoice_date
• Amount_paid
• Invoice_currency_code
• Invoice_type_lookup_code
• Payment_status_flag

Ap_invoice_distributions_all

 Invoices > Entry > Invoices

This table stores the accounting information for the invoice you have entered. There is one row for each invoice disribution, that is this table corresponds to the Distributions window.

Some columns of interest includes :

• Line_type_lookup_code
• Dist_code_combination_id (credit entry)
• Accts_pay_code_combination_id (debit_entry)
• Base_amount (in functional currency)

Ap_checks_all

 Payments > Entry > Payments

This table stores payments to suppliers.

Some columns of interest includes :

• Amount (in functional currency)
• Check_date
• Bank_account_name
• Check_number
• Payment_method_lookup_code
• Payment_type_flag

Ap_invoice_payments_all

 Payments > Entry > Payments

This table stores invoice payments to suppliers. This table is updated when you confirm an automatic payment batch, enter a manual payment or process a Quick Payment. Void payments are represented as a negative of the original payment line.

Some columns of interest includes :

• Accounting_date
• Period_name
• Amount
• Payment_num

Ap_payment_distributions_all

 Payments > Entry > Payments

This table stores accounting information for payments. There is at least one CASH payment distribution for each invoice payment. Additional rows may include DISCOUNT, GAIN and LOSS distributions where appropriate.

Some columns of interest includes :

• Line_type_lookup_code (CASH/DISCOUNT/GAIN/LOSS)
• Base_amount

Oracle Purchasing

Po_vendors

 Supply Base > Suppliers

This table stores supplier information.

Some columns of interest includes :

• Segment1 (supplier number)
• Vendor_name
• Terms_id
• Vendor_type
• Ship_to_location (link to hr_locations for location information)
• Bill_to_location (link to hr_locations for location information)

Po_vendor_sites_all

 Supply Base > Suppliers

This table stores supplier sites information.

Some columns of interest includes :

• Pay_site_flag
• Purchasing_site_flag
• Address_line1 to address_line3
• City
• State
• Area_code
• Zip

Po_headers_all

 Purchase Orders > Purchase Orders

This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.

Segment1 is the document number (i.e. purchase order number)

Some columns of interest includes :

• Agent_id (link to per_people_f for the buyer)
• Type_lookup_code

Po_lines_all

 Purchase Orders > Purchase Orders

This table stores purchasing document lines.

Some columns of interest includes :

• Line_num
• Item_description
• Unit_price
• Unit_meas_lookup_code (unit of measure)
• Quantity
• Item_id (link to mtl_system_items for the item number)
• Category_id (link to mtl_categories for the category name)

Po_line_locations_all

 Purchase Orders > Purchase Orders

This table stores purchase order shipment schedules and blanket agreement price breaks. A purchase order is closed when QUANTITY is equal to QUANTITY_RECEIVED.

Some columns of interest includes :

• Quantity
• Quantity_accepted
• Quantity_received
• Quantity_cancelled
• Need_by_date
• Ship_to_organization_id (link to org_organization_definitions for the organization code)

Po_distributions_all

 Purchase Orders > Purchase Orders

This table stores the accounting information on a purchase order shipment. This table is used for Standard and Planned Purchase Orders and Planned and Blanket Purchase Order Release.

Some columns of interest includes :

• Quantity_ordered
• Quantity_billed
• Amount_billed
• Quantity_delivered
• Quantity_cancelled
• Destination_organization_id (link to org_organization_definitions for the organization code)
• Destination_subinventory

Rcv_shipment_headers

 Receiving > Receipts

This table stores the receiving information. The three receipt sources are Supplier, Inventory and Internal Order. There is one receipt header per receipt source.

Some columns of interest includes :

• Receipt_num
• Shipment_num
• Receipt_source_code
• Shipped_date
• Ship_to_org_id

Rcv_shipment_lines

 Receiving > Receipts

This table stores information about items that have been shipped and/or received from a receipt source.

Some columns of interest includes :

• Line_num
• Quantity_shipped
• Unit_of_measure
• Item_id (link to mtl_system_items for item number)
• To_organization_id (link to org_organization_definitions for organization code)
• To_subinventory
• Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
• Quantity_received
• Quantity_shipped

Oracle Inventory

Org_organization_definitions

 Setup > Organizations > Parameters

This view contains basic information on all inventory organisations.

Some columns of interest includes :

• Organization_code
• Organization_name
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Inventory_enabled_flag

Mtl_secondary_inventories

 Setup > Organizations > Subinventories

This table stores all subinventory information for an inventory organisation.

Some columns of interest includes :

• Secondary_inventory_name
• Description

Mtl_material_transactions

 Transactions > Material Transactions (Inquiry)

This table stores all inventory transactions including cost updates.

Some columns of interest includes :

• Transaction_quantity
• Transaction_type_id
• Transaction_source_type_id
• Transaction_source_name

Mtl_transaction_accounts

 Transactions > Material Distributions (Inquiry)

This table stores the inventory accounting information. There are two rows in this table for each transaction in mtl_material_transactions.

Some columns of interest includes :

• Transaction_date
• Gl_batch_id
• Accounting_line_type
• Base_transaction_value

Mtl_system_items

 Items > Master Items or Items > Organization Items

This table stores the item definition. An item must exist in an inventory organisation.

Your item number is stored in the columns segment1 to segment20 depending on your application configuration. If you have configured your items to have to segments then you may be using segment1 and segment2

Some columns of interest includes :

• Segment1 to segment20
• Description
• Invetory_item_flag
• Purchasing_item_flag
• Inventory_asset_flag
• Stock_enabled_flag
• Invoiceable_item_flag
• Shippable_item_flag
• So_transaction_flag
• Mtl_transactions_enabled_flag
• Primary_unit_of_measure

Mtl_onhand_quantities

 On-hand, Availability > On-hand Quantities

This table stores quantity on hand in a location for each item.

Some columns of interest includes :

• Date_received
• Transaction_quantity
• Subinventory_code

Cst_item_costs

 Costs > Item Costs

This table stores the item cost information. Note that there can be multiple costs per item and the actual cost is where the cost type is Frozen.

Some columns of interest includes :

• Cost_type_id (link to cst_cost_types)
• Item_cost

Oracle Receivables

Ra_customers

 Customers > Standard

This table stores customer information.

Some columns of interest includes :

• Customer_name
• Customer_number
• Status
• Customer_prospect_code
• Customer_type
• Orig_system_reference (for imported customers from an external source)

Ra_addresses_all

 Customers > Standard

This table stores customer address information and your remit-to addresses.

Some columns of interest includes :

• Status
• Orig_system_reference (for imported customer addresses from an external source)
• Address1 to address4
• City
• State
• Postal_code

Ra_site_uses_all

 Customers > Standard

This table stores the customer’s site and site purpose. You must have one row for each address. A customer must have one bill to address for Receivables. A customer must have one ship to address and one bill to address for Order Entry.

Some columns of interest includes :

• Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
• Primary_flag
• Status
• Location

Ra_customer_trx_all

 Transactions > Transactions

This table stores invoice, debit memo, chargeback, commitment and credit memo header information.

Some columns of interest includes :

• Cust_trx_type_id (link to ra_cust_trx_types_all)
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Terms_id (link to ra_terms)
• Trx_number (invoice number)
• Trx_date (invoice date)

Ra_customer_trx_lines_all

 Transactions > Transactions

This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.

Some columns of interest includes :

• Line_number
• Description
• Quantity_ordered
• Quantity_credited
• Quantity_invoiced
• Unit_standard_price
• Unit_selling_price
• Line_type
• Extended_amount
• Revenue_amount

Ra_cust_trx_line_gl_dist_all

 Transactions > Transactions

This table stores the accounting information for revenue, unearned revenue, unbilled receivables, receivables, charges, freight and tax for each invoice or credit memo line.

Some columns of interest includes :

• Amount_gl_date
• Gl_posted_date
• Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
• Acctd_amount (functional currency)

Ar_cash_receipts

 Receipts > Receipts

This table stores the payment information.

Some columns of interest includes :

• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
• Status (APP, UNAPP, UNID, NSF, STOP, REV)
• Type (CASH, MISC)
• Receipt_number
• Amount
• Currency_code
• Pay_from_customer
• Receipt_date

Ar_receivable_applications

 Receipts > Receipts

This table stores accounting entries for cash and credit memo applications.

Some columns of interest includes :

• Amount_applied
• Line_applied
• Tax_applied
• Application_type
• Display
• Gl_date
• Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)

Ar_payment_schedules

 Transactions > Transactions and Receipts > Receipts

This table stores all transactions except adjustments and miscellaneous cash receipts. This table is updated when a transaction occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Some columns of interest includes :

• Amount_due_original
• Status
• Class (DEP, DM, PMT, GUAR, CM, CB, INV)
• Due_date
• Amount_due_remaining
• Invoice_currency_code
• Amount_applied
• Anmount_credited
• Amount_adjusted

HRMS: Oracle Fast Formula

Oracle Fast Formula (FF) is a simple way to write formulas using English words & basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language. Oracle stores fast formulas as a database packages

 
Common tables
SELECT *
FROM all_objects
WHERE object_type = 'TABLE' AND object_name LIKE 'FF%'

FF_FUNCTIONS
FF_FUNCTION_PARAMETERS
FF_FORMULAS_F
FF_FORMULA_TYPES
FF_DATABASE_ITEMS
FF_GLOBALS_F


SELECT *
FROM all_objects
WHERE object_type like  'Pack%' 
AND (object_name LIKE 'FFP_%' or object_name LIKE 'FFW_%')


Uses of Oracle FastFormula
In HRMS, Oracle FastFormula is used for validation, to perform calculations, and to specify rules. Here are some examples.

In Payroll, you use formulas to:
• Validate element inputs
• Calculate element pay values and run results during payroll processing
• Specify the rules for skipping an element during payroll processing
• Perform legislative checks during a payroll run

In Compensation and Benefits Management, you use formulas to:
• Specify the rules for Paid Time Off accrual plans, such as how much time is accrued and over what period, when new starters are eligible to begin accruing time, and how much time can be carried over to the next accrual term.
• Define custom calculations for benefits administration.
• Calculate the duration of an absence given the start and end dates and times.
• Create rules for benefits administration such as eligibility determination.

In People Management, fast formulas are used to:
• Check that element entry values are valid for an assignment
• Specify the criteria for including an assignment in an assignment set and to edit assignment sets
• Configure the people management templates in a number of ways such as supplying additional information to be available from fields on the template and validating field entries.
• Define collective agreements
• Generate custom global person number sequences for employees, applicants, and contingent workers.

Components of Formulas
Formulas are made up of a number of different components. These can include assignment statements, different types of input including database items, functions, nested expressions, and conditions.

1) Assignment and Return Statements.
To start with a simple example, suppose you wanted to calculate the pay value for the element Wage by multiplying the number of hours an employee works each week by hourly rate. You could write this formula:
wage = hours_worked * hourly_rate
RETURN wage
The first line is an Assignment statement that simply assigns a value to the element Wage. The second line is a Return statement that passes back the Wage value to the payroll run.

2) Constants and Variables.
In this example, the Wage value is calculated, but it could be a constant value, such as: wage = 200. To calculate the Wage value, Oracle FastFormula needs to get values for the variables hours_worked and hourly_rate.

3) Data Types.
Both variables and constants can be one of three data types:
• Numeric
• Text
• Date

4) Types of Input.
Values for the variables hours_worked and hourly_rate can be populated using three ways:
• Receiving them as input when the formula is called.
• Finding the values in the database from database items.
• Using global values, which you enter in the Globals window.

To use a database item or global value in your formula, you simply refer to it by name. You can browse through lists of database items in the Formulas window. To use a value passed to the formula at run time, you must write an Inputs statement.

Input Statements
In our Wage example, suppose that hours_worked is an input value to the element Wage. To pass the element input values to the formula during a payroll run, you define
an Inputs statement, as follows:
INPUTS ARE hours_worked
wage = hours_worked * hourly_rate
RETURN wage
The name you use in the Inputs statement must be the same as the name of the element input value, and multiple words must be joined by underscores. In this example, the input value hours_worked is numeric. If the input value is not numeric, you must tell Oracle FastFormula whether it is text or date. For example:
INPUTS ARE start_date (date)

Database Items
Suppose that hourly_rate is a standard rate taken from the Grade Rates table. This is an example of a database item. A database item has a label, or special piece of code, telling Oracle FastFormula the path to take to access the data. These items include both information unique to your enterprise, which you hold in flexfield segments & standard information such as assignment numbers and grades etc. In the Formulas window, you pick database items from a list. There are two types of DB Items: Static and Dynamic.

Static database items are shipped with the system and you cannot modify them.
Dynamic database items are created by Oracle HRMS processes whenever you define new elements or other related entities.

Element Database Items:
When you define a new element, Oracle HRMS runs a process to create a number of related database items for it. To ensure easy recognition of these items, the process adds the element name to each one. It also creates further database items for each pay and input value you use .

Global Variables
Use global values to store information that does not change often, but you refer to frequently, such as Company Name, or company-wide percentages used to calculate certain types of bonus. You define the global value and change its value using the Globals window.

Local Variables
Local variables exist in one formula only. You can change the value of a local variable by assigning it a value in an Assignment statement. In the Wage example, the variable wage itself is a local variable. It receives a value within the formula by the Assignment statement:
wage = hours_worked * hourly_rate

Functions
Oracle FastFormula provides functions that manipulate data in different ways.
GREATEST,INITCAP,LEAST LENTH,INSTR,LOWER,RTRIM,LTRIM,ABS,REPLACE SUBSTRING,TRANSLATE, CALCULATE_HOURS_WORKED,FLOOR,ROUND,TRUNC, ETC( For full list refer the Fast Formula Guide).
There are special functions that convert variables from:
• numbers to text (TO_TEXT)
• dates to text (TO_TEXT)
• text to date (TO_DATE)
• text to number (TO_NUM)

Nested Expressions
Each function or calculation is one expression, and you can nest expressions to create more complex calculations. You must use brackets to make clear to Oracle FastFormula the order in which the calculations are performed. For example:
ANNUAL_BONUS = trunc(((((salary_amount/100)*
bonus_percentage)/183)*(days_between(end_period_date,
start_date) + 1)), 2)
Oracle FastFormula begins calculating inside the brackets and from left to right, in the
following steps:

1. salary_amount/100
2. 1. * bonus_percentage
3. 2. / 183
4. days_between (end_period_date, start_date)
5. 4. + 1
6. 3. * 5.
7. TRUNC(6.,2)

Incorporating Conditions
In Wage element example, only one value is returned, and it is calculated in the same way for every assignment. However you may need to perform different calculations depending on the particular group of employee assignments, or the time of the year, or some other factors. You can do this by incorporating conditions in your formula.

Simple Conditions
For example:
IF age < training_allowance =" 30" training_allowance =" 0">

IF (DAYS_BETWEEN(end_period_date, start_date)+1) >= threshold_value WAS DEFAULTED
There is a special type of condition called WAS DEFAULTED. Use this to test whether a default value has been placed in an input value or database item. Default values are placed using the Default statement. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ‘Warning: hourly rate defaulted’
In this example, if the database item hourly_rate is empty (NULL), the formula uses the default value of 3.00 and issues a warning message.

Combined Conditions
You can combine conditions using the logical operators AND, OR, NOT.

Commenting Formula
You must include comments in your formulas to make them easier to read and understand what the formula does. For example, you can name the formula as:

/* Formula: Calculates Duration of Absence */
Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.

Alias Statements
Sometimes DB Item names are too long to conveniently use in a formula. Set up an alternative shorter name to use within the formula. For example: ALIAS as_qualifying_length_of_service AS as_los In the rest of the formula, you can use the alias (in this example, as_los) as if it were
the actual variable (as_qualifying_length_of_service).

Default Statements
It is used to set a default value for an input value or a database item. The formula uses the default value if the database item is empty or no input value is provided when you run the formula. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = ’Warning: hourly rate defaulted’
This example sets a default of 3.00 for the database item hourly_rate. If hourly_rate is empty (NULL) in the database, the formula uses the default value of 3.00. The formula uses the ’WAS DEFAULTED’ test to detect when a default value is used, in which case it issues a warning message.

Important: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labeled Default Required. This check box is checked for database items that can be empty. The Database Items window appears when you choose the Show Items button on the Formulas window.
How to Compile FastFormula?
After creating or editing a formula in the Formula window, choose the Verify button to compile it.
If you need to compile many formulas at the same time, you can run the concurrent program “Bulk Compile Formulas process” in the Submit Requests window.

Formula Errors
There are two types of error that can occur when using Oracle FastFormula:
• Verify-time errors occur in the Formulas window when you run the formula verification. An error message explains the nature of the error. Common verify-time errors are syntax errors resulting from typing mistakes.

• Run-time errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. The basic Oracle FastFormula errors that can occur at run-time are:

• Uninitialized Variables: An uninitialized local variable is one that has no value when the formula runs. The term ’uninitialized’ means you have not assigned any value to the variable before you try to use it. This causes an error in all statements except the Return statement. For example:
IF (tax_band < tax =" salary"> 2000)
THEN tax = salary / 10
IF tax > 1000
THEN...
This formula fails with an ’Uninitialized variable’ message (for the variable tax) if the tax band is set to 2000.

• Divide by Zero: Dividing a number by zero is an operation that provides no logical result. If this situation ever arises, Oracle FastFormula passes a code back to the application indicating an error (the application then takes the appropriate action).Always check for the possibility of a divide by zero error if there is any chance it could occur. For example, the formula:
x = salary/contribution_proportion
produces an error if the contribution proportion is set to zero. In this formula, check
for the divide by zero condition as follows:
IF contribution_proportion = 0
THEN

(

message = 'The contribution proportion is not valid.'

RETURN message

)
ELSE x = salary/contribution_proportion

• No Data Found: A database item supposed to be in the database was not found. This
represents an error in the application data.

• Too Many Rows: The database item definition within the application caused more than one value to be fetched from the database.

• Value Exceeded Allowable Range: This can occur for a variety of reasons such as:

• exceeding the maximum allowable length of a string (which is 240 characters)
• rounding up a number to an excessive number of places, for example, round (1,100)
• using an invalid date, for example, 39-DEC-1990.

• Invalid Number: This occurs only when a database item contains an item that does
not make sense as a number.

• Null Data Found: A database item was found to have a null value when it should have had a non-null value. Use the Default statement for database items marked as Default Required in the Database Items window.

Also you can call PL/SQL procedure from the Fast formula.