40
Handling Dates 33-11
my_date2 := my_date my_date2 := to_date(my_rr_date,'DD-MON-RR')
Implicit Conversions: Accidental conversions of this type may occur by performing a
TO_DATE on a date type value. This only occurs in SQL or server side PL/SQL. In SQL,
performing a TO_DATE on a date type implicitly does a TO_CHAR on that value since
TO_DATE requires a character argument. The TO_CHAR is done using a nine-digit
format mask (DD-MON-YY), which causes the problems discussed above. This problem
occurs in server-side PL/SQL such as C programs, SQL*Forms 2.3 code, and dynamic
SQL in Developer 2000.
select to_date(my_date,'DD-MON-YYYY')... [WRONG]
Instead, avoid the unnecessary conversion:
select my_date...
Similar accidental conversions can be done by using NVL and DECODE carelessly. If a
NVL or DECODE is returning a character instead of a date, trying to correct this error
by converting the returned value to a date can cause the first century error:
to_date(DECODE(char_col,'<NULL>',null,sysdate),
'DD-MON-YYYY') [WRONG]
to_date(NVL(null,sysdate),'DD-MON-YYYY') [WRONG]
Instead, ensure that the returned value is a date type:
DECODE(char_col,'<NULL>',to_date(null),sysdate)
NVL( to_date(null),sysdate)
ORA-1841 Problems: In the year 2000, transferring dates to the first century causes an
immediate problem. For dates occurring in the year 2000, there is no first century
equivalent (there is no year 0). If your code converts a date to year 0, the error
"ORA-01841: (full) year must be between -4713 and +9999, and not be 0" occurs.
Comparison Problems: Also, when comparing date values, converting the dates to the
first century causes problems with comparisons across the century boundary. Although
01-JAN-99 occurs before 01-JAN-01 in the DD-MON-RR format, 01-JAN-0099 is later
than 01-JAN-0001 if the dates are accidentally moved to the first century.
DE-4. Associating Any Hardcoded Date Mask with a Form Field
Any Oracle Forms field with a hardcoded mask associated with it behaves incorrectly
since the standard date fields use the mask DD-MON-RRRR.
In Release 12, flexible date formats allow the format to change depending on the
environment.
DE-5. Using a Pre-1950 Date with a Two-Digit Year
Oracle E-Business Suite uses DD-MON-RR mask as the default date mask. If century
information is missing, the default code "assumes" a date is between 1950 and 2049.
Hardcoded dates before 1950 stored with a two-digit year will be misinterpreted. A
hardcoded date with a four-digit year (using an explicit format mask) between 1900 and
1949 is only incorrect if the date is stored without century information (usually meaning
37
33-12 Oracle E-Business Suite Developer's Guide
it is stored as a DD-MON-RR string). Most problems of this time are in C code or
concurrent program arguments although they are possible in PL/SQL.
Use the standard negative and positive infinity dates in all new code. Of course, in SQL
and PL/SQL you still need to ensure that the century information is not lost.
For example, the code fragment to_date('01-JAN-00') would be interpreted as January 1,
2000, while the code fragment to_date('01/01/1000', 'DD/MM/YYYY) would be
unambiguous.
Translated Date Issues
These issues will affect any dates that must work in a multilingual environment. Oracle
E-Business Suite Release 12 can run in multiple languages and can support multiple
date formats.
TD-1. Hardcoded English Month
English months fail in other languages. Use a numeric month instead.
TO_DATE('1000/01/01','YYYY/MM/DD')
Not:
TO_DATE('01-JAN-1000','DD-MON-YYYY') [WRONG]
TD-2. NEXT_DAY with English Day or Ordinal
A next_day call is not translatable if you pass in a hardcoded English day (i.e. MON).
However, it is also incorrect to pass it a hardcoded ordinal (i.e. 1), since which days
map to which numbers varies by territory.
Use a currently known date (i.e. 11/3/1997 is a Monday) to determine what the 3
character day in the current language is and then pass that in.
next_day(my_date,to_char(to_date('1997/03/11',
'YYYY/MM/DD'),'DY'))
Client Date Issues
The following is a client date issue.
Client Date Issues - CD-1. Getting the Date from the Client
These problems are caused by the program getting the current day or time from the
client machine (a PC in the smart client release) instead of the database. The database is
preferable. Oracle E-Business Suite currently gets all current times from the server
because neither PC vendors nor Microsoft are providing Year 2000 warranties.
Do not use $$DATE$$ to default the current date into a Forms field. This gets the client
date. Instead use the $$DBDATE$$ built-in which gets the database date. Better still,
default the date programmatically in WHEN-CREATE-RECORD or WHEN-NEW-
FORM-INSTANCE using FND_STANDARD.SYSTEM_DATE. The use of $$DATE$$ is
not a problem in character mode (it uses code similar to the SYSTEM_DATE call).
32
Customization Standards 34-1
34
Customization Standards
Overview of Customizing Oracle E-Business Suite
This section provides you with standards for building custom application components
that integrate with Oracle E-Business Suite. Using these guidelines, you reduce the
administrative effort to build and maintain custom components.
Because all Oracle E-Business Suite products are built using Oracle Application Object
Library, you should use Oracle Application Object Library, with other Oracle tools, to
customize Oracle E-Business Suite. You should be familiar with the concepts presented
in the relevant chapters of the Oracle E-Business Suite Developer's Guide.
The following topics are covered:
•
Overview of Customizing Oracle E-Business Suite
•
Customization By Extension
•
Customization by Modification
•
Oracle E-Business Suite Database Customization
•
Oracle E-Business Suite Upgrades and Patches
•
Upgrading Custom Forms to Release 12
There are several different ways you might want to customize Oracle E-Business Suite.
Some of the most common types of customizations include:
•
Changing forms
•
appearance
•
validation logic
42
34-2 Oracle E-Business Suite Developer's Guide
•
behavior
•
Changing reports or programs
•
appearance
•
logic
•
Database customizations
•
adding read-only schemas
•
augment logic with database triggers
•
Integrating third party or custom software
•
relinking programs
Basic Business Needs
These suggestions provide you with the ability to satisfy the following basic business
needs. You should be able to:
•
Enhance Oracle E-Business Suite to meet your needs by:
•
Developing new components
•
Modifying existing components
•
Improve the Oracle E-Business Suite upgrade process by:
•
Preserving custom components across upgrades
•
Simplifying the upgrade of custom components
Definitions
More information on many of these features is available in the references below.
Overview of Building an Application, page 1-11
Overview of Setting Up Your Application Framework, page 2-1
Customization By Extension
Develop new components for existing Oracle E-Business Suite applications and develop
new applications using the development features of Oracle Application Object Library.
Customization by extension can be as simple as copying an existing Oracle E-Business
33
Customization Standards 34-3
Suite component to a custom application directory and modifying the copy.
Customization By Modification
Also known as "customization in place". Modify existing Oracle E-Business Suite
components to meet your specific requirements. Modifications range from changing
boilerplate text to altering validation logic.
Important: Oracle E-Business Suite recommends that you avoid doing
customization by modification. You should do customization by
extension instead. You should be aware that modifications are often not
preserved during an upgrade or patch process.
Component
A module of code (such as forms, reports, or SQL*Plus scripts) or an Oracle Application
Object Library object (such as menus, responsibilities, and messages), or a database
object (such as tables, views, packages, or functions).
Custom Application
A custom application is a non-Oracle E-Business Suite application that is registered
with Oracle Application Object Library and typically has (at least) its own directory
structure and other components.
Database Object
A table, index, view, sequence, database trigger, package, grant, or synonym.
Application Short Name
A short reference name for your application that contains no spaces. You use an
application short name when you request a concurrent process from a form, call
Message Dictionary routines, and so on.
Application Basepath
The name of an environment variable that translates into the top directory of your
application's directory tree. Oracle Application Object Library searches specific
directories beneath the basepath for your application's executable files, including form
files.
Application Directory Structure
The hierarchy of directories for an application. The Oracle E-Business Suite directory
structures are created when you install or upgrade Oracle E-Business Suite. You create
the directory structure for a custom application.
39
34-4 Oracle E-Business Suite Developer's Guide
For more information, see: Oracle E-Business Suite Concepts.
Applications Environment Files
Defines the environment variables for your Oracle E-Business Suite installation. The
environment files include <dbname>.env and adovars.env (for UNIX platforms). The
<dbname>.env file contains application basepath variables for Oracle E-Business Suite
products and other environment variables and is regenerated automatically by Oracle
E-Business Suite administration utilities. The adovars.env file contains information on
virtual directory structures used with web servers and is modified at installation time
by applications system administrators to configure users' environments.
Determining Your Needs
Follow these steps to determine your customization needs:
•
Determine the specific requirement that is not met by Oracle E-Business Suite
•
Try to meet this requirement by altering the definition parameters of the standard
applications, as described in your implementation and user manuals. Often, you
can eliminate the need for customization by altering the application configuration
(such as by setting up a descriptive flexfield)
•
Document the requirement that you wish to meet by customization
•
Determine if you can meet this requirement by extension (adding a new
component) or if you must modify an existing component
Whenever possible, you should customize by extension rather than by modification. By
doing so, you eliminate the risk of overwriting or losing a required piece of application
logic or validation in the existing components.
You may customize by modification, but we strongly discourage this. These
modifications introduce a level of risk, and are not supported by Oracle Support
Services, nor the Applications Division. A simple change in form navigation may
eliminate a validation check resulting in data integrity problems, and may be lost
during an upgrade or patch.
If you must modify an Oracle E-Business Suite form, your first choice should be to
determine whether you can accomplish your modification using the CUSTOM library.
You can use the CUSTOM library to modify the behavior of Oracle E-Business Suite
forms without making invasive changes to the form code, making your modifications
less difficult to upgrade. Modifications you can do using the CUSTOM library include
hiding fields, opening other forms from the Zoom button, enforcing business rules,
adding choices to the Special menu, and so on.
40
Customization Standards 34-5
Customization By Extension
Separate your application extensions from Oracle E-Business Suite components for easy
identification and to reduce the risk of loss during an upgrade or patch. To keep new
components separate, you implement a custom application and make it the owner of
the new components.
You may implement one custom application that owns all custom components, or many
custom applications that own custom components. For example, you may want to
define a custom general ledger application for extensions to Oracle General Ledger, and
a custom payables application for extensions to Oracle Payables. Use many custom
applications if you will create more than a few components for each Oracle E-Business
Suite product. Use a single custom application if you will only create a few components
for all Oracle E-Business Suite products.
Follow these steps for each custom application you wish to implement:
•
Define your custom application
•
Create your custom application directory structure
•
Add your custom application to your Applications Environment File
•
Add new components to your custom application
•
Document your new components
Tip: Use a revision control system to help you keep track of your
changes and custom components.
See: Overview of Building an Application, page 1-11
Overview of Setting Up Your Application Framework, page 2-1
Documenting New Components
You should document at least the following for each new component:
•
Purpose
•
Input parameters (for reports and programs)
•
Sample output (for reports and programs)
•
Processing logic
•
Database objects used and type of access (select, update, insert, delete)
38
34-6 Oracle E-Business Suite Developer's Guide
You thoroughly document each extension to simplify each of the following tasks:
•
Component modification due to changing business requirements
•
Component modification due to Oracle E-Business Suite upgrades or patches
•
Identification of obsolete extensions following an Oracle E-Business Suite upgrade
or patch
Use the Register Flagged Files utility in Oracle Applications Manager to register and
document your extensions. Refer to the Oracle E-Business Suite Maintenance Guide for
more information on this utility.
Defining Your Custom Application
Register your application using adsplice. See: AD Splicer, Oracle E-Business Suite Setup
Guide and My Oracle Support Knowledge Document 1577707.1, "Creating a Custom
Application in Oracle E-Business Suite Release 12.2."
Use an intuitive application name and short name for your custom application; relate
the names to the Oracle E-Business Suite application being extended if appropriate. For
example: extensions to Oracle General Ledger could belong to a custom application
named Custom General Ledger with an application shortname XXGL.
Note: Your application short name must be no more than eight (8)
characters long.
To reduce the risk that your custom application short name could conflict with a future
Oracle E-Business Suite short name, we recommend that your custom application short
name begins with "XX". Oracle reserves all three to four character codes starting with
the letter O, the letters CP, and the letter E, as well as all names currently used by
Oracle E-Business Suite products (query all applications in the Applications window).
See: Register Your Application, page 2-1
Creating Your Custom Application Directory Structure
Use the appropriate operating system commands to create your application directory
structure. You should define a release number as part of the application basepath to
allow for multiple versions of your custom application in the future. Use the release
number of the Oracle E-Business Suite release in your installation, such as 12.0 for
Release 12.
Modifying Your Applications Environment File
Modify your applications environment file to define an environment variable for your
application basepath. Add your custom application basepath environment variable to
the adovars.env file (or platform equivalent), not the <dbname>.env file.
Documents you may be interested
Documents you may be interested
