Names
ASCX Default schema; for all general ASC customizations that are not IE, interface, transition or migration related.
IEXX Invoice Engine schema
INFX Schema for tables used to interface outside of this environment on a permanent basis.
TRNX Tables needed to contain data during the transition between an organization's previous environment and this environment. These are not permanent interface tables, but they will be needed for a time longer than the initial data migration.
MIGX Tables needed for migration of an organization into the environment. These objects may be dropped and then re-created for each new organization.
TXWI Taxware International schema.
Tablespaces
Each schema should have a
Object Creation
1- Tables/Indexes/Sequences: All custom tables, indexes, and sequences should be created in a custom schema.
2. Views/Triggers/Packages/Functions: All views, triggers, stored procedures, packages and functions should be placed in the APPS schema.
Naming Standards: Database object names should be:
a. Unique (relative to applications at the site);
b. End user oriented;
c. Unambiguous;
d. Consistent;
e. Brief.
Prefix:
. All Custom objects should have a name that begins ASC_
. Logical Grouping should be two to four characters that represent the project, application short name or other area that is responsible for maintaining it the customization.
Some Logical Grouping suggestions:
ONT Order Management
ARX Accounts Receivable
IE Invoice Engine
CIF CIF
SHR Objects shared by all customizations
Tables:
Naming Standard
a. All tables must be prefixed with ASC_
b. Should be Plural
Good Example: ASC_ONT_SOFTWARE_ATTRIBUTES
Bad Example: ASC_SOFTWARE
c. Translation Tables should have a suffix of _TL. A translation table is a table used to maintain data that can be different based on language.
Schema
Tables should be created in a custom schema – see schema section above.
Table Structures
All parameters should be fully specified in all DDL statements, even if the default value is to be accepted.
Rather than do: create table SAMPLE (COL1 VARCHAR2 20);
Do: create table ASC_ONT_SOFTWARE
(COL1 varchar2(20) not null,
COL2 date)
pctfree 10
pctused 70
initrans 1
maxtrans 99
tablespace ASCX_DATA
storage (initial 128K
next 128K
minextents 1
maxextents 550
pctincrease 0)
nocache;
The following chart should be used as a guideline for establishing extent sizes:
Please consult a dba on large table.
Table Size - Initial Extents - Next Extents
Small - 128K - 128K
Medium - 512K - 512K
Large - 1M - 1M
X-Large - 2M - 2M
Every table should include the following columns to support the Who processing feature:
CREATION_DATE default sysdate (Not Null, DATE)
CREATED_BY (Not Null, NUMBER)
LAST_UPDATE_LOGIN (Null Allowed, NUMBER)
LAST_UPDATE_DATE default sysdate (Not Null, DATE)
LAST_UPDATED_BY (Not Null, NUMBER)
The only exception is when a table is created for the sole purpose of maintaining temporary data.
Order table columns as follows:
a. Primary key column(s)(major to minor);
b. CREATION_DATE;
c. CREATED_BY;
d. LAST_UPDATE_LOGIN;
e. LAST_UPDATE_DATE;
f. LAST_UPDATED_BY;
g. Mandatory (NOT NULL) columns;
h. Key flexfield columns if any (SEGMENT1 mandatory, others optional);
i. Optional columns;
j. Descriptive Flexfield context column, if any;
k. Descriptive Flexfield segment columns, if any.
l. ORG_ID, if data must be separated by operating unit
Quickcodes/Lookups: Two custom tables have been created under the ASCX schema to maintain all lookup codes and their values.
ASC_SHR_LOOKUP_TYPES
ASC_SHR_LOOKUP_VALUES
Do not create additional lookup tables.
Views
Naming Standards
a. Start with ASC_
b. Suffix with _V or _TLV for views that retrieve data from a translation table(s) for a specific language.
c. Are end user oriented:
d. Are brief while avoiding unnecessary abbreviation:
Schema: Views should be created under the apps schema.
Columns in Table and Views
Columns should have names that:
a. Omit a table prefix:
Good: ACTIVE_DATE
Bad: LPV_ACTIVE_DATE (in GL_PLAN_VERSIONS table)
b. Include a table prefix only when part of a primary key (i.e. an “ID”, “NAME”, “NUM” column) to allow foreign key columns to have the same name:
Good: PLAN_VERSION_ID
TIME_PERIOD_NAME
REQUISITION_LINE_NUM
Bad: ID
NAME
NUM
c. Are singular:
Good: FIRST_TIME_PERIOD
Bad: FIRST_TIME_PERIODS
d. Are brief (up to 15 characters), while avoiding unnecessary abbreviations:
Good: DESCRIPTION
Bad: DESC
Make a foreign key column name match the corresponding primary key column name, e.g.;
Good: TIME_PERIOD_NAME (to refer to column TIME_PERIOD_NAME in the table GL_TIME_PERIODS).
Bad: TP_NAME, PERIOD_NAME or TIME_PERIOD.
Column Suffixes
The following column types should be named as follows:
Date Columns: should have a meaningful name followed by "_DATE"
Flags: should have a descriptive name followed by "_FLAG"
Sequences: should have a descriptive name followed by "_ID"
Lookup code: should match the value of the lookup_type which should end with _CODE
For example: (ACTIVE_DATE, MERGE_ID, MOVE_FLAG, SYSTEM_CODE)
Multi Org column: should be org_id
Language for translation: should be LANGUAGE NOT NULL VARCHAR2(4)
Sequences:
Naming Standard
a. Start with ASC_
b. Ends with _S
c. Contain a description or table name, if applicable
Sequence ASC_DDDDDD_S DDDDDD = Description/Table Name if
used to generate a primary key for a table.
S = Sequence
Schema
Sequences should be created in a custom schema, not the apps schema.
Data Types
Naming Standard
a. Start with ASC_
b. Ends with _TYPE
Schema
Data types should be created in a custom schema, not the apps schema. They will be included in the grant_synonym script so the apps schema will become aware of them.
Triggers
Naming Standard
a. For a custom table; start with table or view name.
b. For a standard Oracle table, prefix with ASC_ and followed by the table or view name
c. Ends with type of trigger
Database Trigger ASC_TABLE_XYZN TABLE = Base table or view name
X: B = Before A = After O =Other;instead of
Y: S = Statement R = Row
Z: I = Insert U = Update
D = Delete
N: One digit trigger id (if more
than 1 trigger of a type exist)
Schema
Triggers should be created in the apps schema.
Stored Packages
Naming Standard
a. Starts with ASC_
b. Ends with _PKG
Query Function Packages ASC_XXX_QUERY_PKG
A query function package is a package that contains only query functions. This naming standard is consistent with the applications query function packages.
Stored Package ASC_XXX_DDDDDD_PKG
DDDDDD = Description
PKG = Package (up to 32 char)
Schema
Procedure packages should be created in the apps schema.
Stored Procedures
Naming Standard
a. Starts with ASC_
b. Ends with _PROC
Procedures that are created within a package should have a descriptive name.
Procedures that are stand alone (not created within a package) should be named as follows:
Stored Procedure ASC_XXXX_DDDDDD_PROC
XXXX – logical grouping
DDDDDD = Description
PROC = signifies procedure
Schema
Stand alone procedures should be created in the apps schema.
Stored Functions
Naming Standard
a. Starts with ASC_
b. Ends with _FCN
Functions that are created within a package should be named descriptive.
Functions that are stand alone (not created within a package) should be named as follows:
Stored Function ASC_XXXX_DDDDDD_FCN
DDDDDD = Description
FCN = signifies function
Query functions – Needs Validated in 11i
When you are creating query functions (i.e. for a view), you must add the following line in the package specification after defining the function
pragma RESTRICT_REFERENCES (SOC_LOOKUP_VALUE, WNDS, WNPS);
A query function is a function that does not perform an insert, update, or delete.
This statement tells the Oracle parser that the function does not change any database values. This statement is important because you cannot use a function in a select statement that alters data within the database (i.e., it cannot update, insert or delete records or alter database objects).
Schema
Stand alone functions should be created in the apps schema.
Indexes
Naming Standard
Indexes _PK, _N1, _U1, etc (no need to put the 'C' if the name begins with ASC)
a. For a custom table should start with tablename
b. For a standard Oracle table; should start with ASC_ and followed by table name.
c. Ends with type of index PK=primary key
OR
U=unique N=Non-unique and sequential number
Index ASC_DDDDDD_Tn DDDDDD = Description
PK = primary key index
OR
T: U= Unique N= Non-Unique
n = sequential number
Indexes should have names that:
a. Reflect the name of the table they index:
Good: ASC_GL_TIME_PERIODS_U1
Bad: TP_INDEX
b. Show they are a primary key index via a suffix of “_PK”
Good: ASC_GL_TIME_PERIODS_PK
Bad: GL_TIME_PERIODS
c. Show they are unique via a suffix of “_Un” (where “n” is a number from 1 to 9):
Good: ASC_GL_TIME_PERIODS_U1
Bad: GL_TIME_PERIODS
d. Show that they are non-unique via a suffix of “_Nn” (where “n” is a number from 1 to 9):
Good: ASC_GL_TIME_PERIODS_N1
Bad: GL_TIME_PERIODS
Schema
Indexes should be created in a custom schema, not the apps schema.
APPL_TOP Stuff
. Under the standard APPL_TOP there will be an asc_custom directory. All custom files will be placed somewhere under this directory.
. Under asc_custom there will be the following directories:
ascx
iexx
infx
trnx
migx
txwi
. The standard structure under each of these subdirectories will be:
bin
forms
help
install
log
mesg
out
reports
sql
No comments:
Post a Comment