ALIAS: If you are writing code that joins to more than two tables, it is recommended that aliases be used. The use of aliases resolves the problem of ambiguity, when the same column name resides in more than one table. The alias should be used on each table in the where clause, and on each table’s corresponding columns in the select statement. The alias name should be something short, but meaningful. For example, the alias for the table mtl_system_items_b could be msi. The alias for oe_order_headers_all could be ooh.
Who Columns: Which user: Default values for “CREATED_BY and LAST_UDPATED_BY columns should not utilize -1 or 2.
If you have a custom program that performs DML, you need to set the created by and last_updated_by columns in the table. If possible, use the actual user who is performing the database change. If it is a concurrent program, you can use the username DISTBATCH, CONVERSION (for conversion programs), or PURGEUSER (for purge programs).
Date columns: CREATION_DATE and LAST_UPDATE_DATE should default to sysdate. Date columns need to be on all tables.
Database Object Script Naming Standards:
These scripts should contain all of the statements necessary to create custom tables, indexes, sequences, functions, procedures, packages, triggers, and views. Scripts will be created to handle standard grants and synonyms (those between the custom schema and APPS). Special grants and synonym scripts may be necessary.
- Table, Index, and Sequence Scripts - The scripts that contain statements for tables, indexes, or sequences must have something in the name that makes it obvious that they will need to be run under a custom schema. They can contain "tbl" for tables, "idx" for indexes, "seq" for sequences, or "tis" if the script creates more than one of these objects. Do not include drop statements in these scripts if this is the initial release of the tables. Alter scripts should be separate from create scripts. The name of the alter script should be very similar to initial table creation script. For example, if the original script to create the table was arxcurr_tbls.sql, then an alter for one of the tables should be put in a script called arxcurr_tblsXXXX.sql (where XXXX is the release number). The original script should also be updated to include the additional column(s).
- All modules are installed as if part of a separate product. A directory structure is created under $APPL_TOP.
- Customizations on standard Oracle tables – The only type of customizations allowed on standard Oracle tables are for the creation of indexes and triggers. However, during some upgrades we are asked to drop or disable these objects. Assuming that triggers follow the naming standards, the installation should be able to identify custom triggers on standard Oracle table and simply disable/enable them. However, since indexes must be dropped, we will need to have custom indexes on standard Oracle tables placed in separate scripts so that the scripts can be re-run to put the objects back after the upgrade. These scripts should be placed in $ASCX_TOP/install/db. The naming of the index scripts should be ora__description_idx.sql where XXX is the Oracle schema that contains the index.
- Create or Replace – Any objects that allow "create or replace" should use this. The name of the object should follow immediately on the same line and be in upper case. On the line below should be the version number of the object. For example,
create or replace procedure ASC_APPS_TRUNCATE_PROC
/* Version: ERP 1.0 */ - Script Header and Footer – So that the installation works properly, each script should have the following information at the beginning and ending.
set echo on
set feedback on
/* Name: lookup_tbl.sql */
/* User: Run this script as ASCX */
.
.
blah, blah, blah
.
.
/* end of lookup_tbl.sql */
exit - Spool Commands – Spooling to a log file is not necessary. The installation process will generate a log file of its own. The log for a script will be placed in the $XXXX_TOP/install/db/logs directory.
- Connect Commands – Do not add the connect command to the script since it will require that you hard code the user name and password. The automated installation process will determine the actual user name and password to use.
Code Alignment Indentation: All programs need to indent for each level within a program. Proper alignment will ensure readability of code when nested IF’s are required.
Comments and In-Line Documentation
All programs should be thoroughly commented. The purpose should be to impart as much information about the program as possible. Therefore, all SQL*Forms triggers should be commented, the structure of the queries in a SQL*Reportwriter program should be described in the comments, etc. The specific recommendations will be described in later sections. However, the most important comment in any program will be the Program History Log (PHL). The PHL will contain the following program details:
a. Copyright information;
b. Project identification;
c. File name;
d. File type (for example, Stored Procedure, PL/SQL, SQL, Unix Shell);
e. Program Short Name;
f. Program Full Name;
g. Description;
h. Called from (indicate the calling program, if any);
i. Executes (indicate any programs executed by, if any);
j. Parameters:
k. Version Control format = Header: 99.99.99.99.99 (where 99.99.99.99.99 follows the standards for the Version Number)
l. Change History;
i. Date of Change;
ii. Author of Change;
iii. Change Reference;
iv. Comments / Description of Change;
m. Any information required for the Configuration Management software.
The PHL will be created by the original author of the program and then updated by subsequent authors.
Example 1:
/*************************************************************************/
/* Copyright 2001 by ASC Corporation */
/* All Rights Reserved */
/* */
/* Project: ERP 1.0 */
/* File: INTAPLRP.SQL */
/* File Type: Stored Procedure */
/* Program Short Name: N/A */
/* Program Full Name: N/A */
/* */
/* Description: ie_add_lookup_stp */
/* This procedure adds a lookup entry to a table. */
/* */
/* Called from: lookups.sql */
/* Executes: N/A */
/* */
/* Parameters: N/A */
/* */
/* Header: 01.00.00.00.00 */
/* Date of Author of Change Comments / Description */
/* Change Change Ref. of Change */
/* --------- ----------- ------ ------------------------------ */
/* 30-OCT-01 J.Public N/A Initial Release 1.0 */
/* */
/* */
/*************************************************************************/
Example 2:
/*************************************************************************/
/* Copyright 2001 by ASC Corporation */
/* All Rights Reserved */
/* */
/* Project: ERP 1.0 */
/* File: GL000000.frm */
/* File Type: GL form */
/* Program Short Name: GL000 */
/* Program Full Name: REPORT ACCOUNTING INFORMATION */
/* */
/* Description: This dummy report is used to show the */
/* development standards. */
/* */
/* Called from: N/A */
/* Executes: N/A */
/* */
/* Parameters: N/A */
/* */
/* Header: 01.00.00.00.00 */
/* Date of Author of Change Comments /Description */
/* Change Change Ref. of Change */
/* --------- ----------- ------ ------------------------------ */
/* 30-OCT-01 J.Public N/A Initial Release 1.0 */
/* */
/* */
/*************************************************************************/
This standard PHL will ensure that the team will be able to keep an accurate record of any changes to any of the customized programs developed on the project.
In addition to adding header level comments add comments within the body of any code that explain constants, any anything that would occur out of the order. Comments need to explain the business reasons or process for code. Make sure that comments are not just a verbal translation of the statement.
Document constants:
IF (i = 1) THEN – Business Case 1 to trigger customer set-up
V := ‘First Case’;
ELSIF (i=2) THEN – Business Case 2 to trigger interface
V := ‘Second Case’;
ELSE
V := ‘Default’; -- Default Case all others numbers 3-9
END IF;
Unix Shell Script Standards:
Header Standards: Each script will include a header section at the beginning of each file.
Example:
#!/bin/sh
#*************************************************************************
#* Copyright 2001 by ASC Corporation *
#* All Rights Reserved *
#* *
#* $Header: pocjfprn.sh (VERSION 1.0.0.0.0) *
#* *
#* Project: ERP 1.0 (RFC 115347) *
#* File: pocjfprn.sh *
#* File Type: Unix Shell Script *
#* Program Short Name: N/A *
#* Program Full Name: N/A *
#* *
#* Description: Driver to print or fax PO's to JetForm. *
#* *
#* Called from: Oracle submit requests form *
#* Executes: N/A *
#* *
#* Parameters: -j - optional, flag to indicate *
#* PO's should be faxed and *
#* not printed *
#* -d - optional, debug flag *
#* -p printer_name - user selected printer *
#* -p printer_name - user selected printer *
#* -f filename - PO's to be printed or faxed *
#* *
#* Version Date of Author of Change Comments / Description *
#* Number Change Change Ref. of Change *
#* ------- --------- ----------- ------ ----------------------------- *
#* 1.0 30-OCT-01 A. Smits N/A Initial Release 1.0 *
#* *
#* *
#*************************************************************************
Version Control Standards: All Unix Shell Script files will be version controlled. When making changes to script files at the header section of the file is an area to keep COS/FMS version control.
Example:
#* $Header: pocjfprn.sh (VERSION 1.0.0.0.0) 110.1 97/09/17 13:10:24 pejohnso ship $
This line describes the
$Header: file name - (ASC's Version Control) – [Oracle's Version Control - Date/Time Created – Author]
Note: “Oracle’s Version Control – Date/Time Created - Author” is option and should be include only if the original script was released by Oracle.
As the script file needs changing the internal development version number will increment by 1.
Example:
Initial Version put in CME 1.0.0.0.0
Fix during CIT 1.0.0.0.1
--------Phase 1 released -------
Bug fixed in Patch 3 1.0.0.3.0
Fix during Patch 3 CIT 1.0.0.3.1
Fix during Patch 3 SAT 1.0.0.3.2
-------Patch 3 released----------
New Stuff added for Phase 2 1.1.0.0.0
More new stuff for Phase 2 1.1.0.0.1
Fix during SAT 1.1.0.0.2
Version Control:
All programs will be placed under Software Configuration Management (SCM) control. Subsequent changes to a program will require the version number to be changed. The breakdown of a version is as follow:
Version number 99.99.99.99.99. Where
_ Internal development version number
_ Patch number
_ Maintenance release number **
Point release number
Major release number
** Since there will be fairly regularly scheduled point releases, there will be no need for maintenance releases on a quarterly basis.
The internal development version number starts with 0 with any new release. See examples below:
Example 1:
The first version for a ERP piece of code would be 1.0.0.0.0
Example 2:
If the code needs to be changed during testing (before released for production), the developer should change the internal development version to 1 and increment the number by 1 for each subsequent change. During CIT a problem is detected. The code is checked out of the repository and changes are made. The version should be updated to ERP 1.0.0.0.1. If an another change is required, the version should be 1.0.0.0.2
Example 3:
If the code needs changed for ERP 1.1, the version should be changed to 1.1.0.0.0
Example Summary:
Initial version put in ACME 1.0.0.0.0
Fix during CIT 1.0.0.0.1
------Phase 1 released ---------------
Bug fixed in patch 3 1.0.0.3.0
Fix during patch 3 CIT 1.0.0.3.1
Fix during patch 3 SAT 1.0.0.3.2
------Patch 3 released ----------------
New stuff added for Phase 2 1.1.0.0.0
More new stuff for Phase 2 1.1.0.0.1
Fix during SAT 1.1.0.0.2
Each type of file (i.e. pl*sql, form, library, database script) has a specific standard on how and where to include the version number.
Invoker vs. Define Rights:
A definer-rights procedure (or package or function) executes with its owner's privileges. Roles are disabled in a definer-rights procedure and in any procedure invoke directly or indirectly by a definer-rights procedure.
An invoker-rights procedure (or package or function) executes with all of the invoker's privileges, including currently enabled roles. If the invoker-rights procedure was invoked directly or indirectly by a definer-rights procedure, no roles are enabled.
The Oracle database default is definer-rights. However, there is an administrator function within the apps to set these rights for you if you have not explicitly done so. When this runs, it will set it to invoker-rights for any package, procedure and function it finds under the apps schema. Yes, Oracle will change custom objects. So to prevent this from happening, it is best to be explicit.
If your package is owned by APPS and it has definer-rights, no matter who you are when executing the package, it will run with the privileges of APPS. (This is how it had functioned before 8i.) However, if your package is owned by APPS and has invoker-rights, it will run with the privileges of who you are when you are executing it. For example, if you log into sqlplus using IEXX and run an APPS package with invoker-rights, you will have the privileges of IEXX, not APPS.
Here is an example. The words in red are what you need to add if you want it to run with definers rights. If you are adding this to a package, you only need to do this to the package header (not the body).
create or replace procedure ASC_APPS_TRUNCATE_PROC
/* Version: ERP 1.0 */
(v_syn_name IN VARCHAR2,
v_reuse_flag IN VARCHAR2 DEFAULT 'NO')
authid definer IS
BEGIN
DECLARE
blah, blah, blah
END ASC_APPS_TRUNCATE_PROC;
Performance Coding:
1) When necessary, include in your code a call to asc_appl_info_pkg and set the name of your code.
For example,
ASC_APPL_INFO_PKG.SET_MODULE('ASC_WSH_DEL_ASSIGNMENTS_ARIU1','');
When the performance team is reviewing code that is being executed, they can tell what package/trigger/function is being executed.
2) Also, when the code is complete you should reset the value to null. This will prevent your trigger/package/function displaying as database object that is executing after your code.
For example, in your exception handler, before a return statement
ASC_APPL_INFO_PKG.RESET();
OM Debug Statements: If you want to include OM debug statements in your code following the steps below:
Declare
X_DEBUG_FILE varchar2(100);
begin
oe_msg_pub.initialize;
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.SetDebugLevel(5);
Oe_debug_pub.add('ASC: put your debug message here');
For subsequent debug statements, you only need to include the
Oe_debug_pub.add('ASC: debug message here');
SQL Error Debugging Standards : For debugging purposes add a location variable to all code (nLoc_Num NUMBER). Assign a unique number to each SQL statement so in the event of a failure a support analyst can track back to the specific SQL statement that failed. The location variable, procedure/function name needs to be displayed along with the SQL error message and code in the exception handler. This process will help a support analyst debug the code in the event that it fails. Wherever possible if an error message can be placed to an error table this is helpful for support. Display counts as needed to verify inserts.
Listed below are examples of exception handlers with error processing:
CREATE OR REPLACE PROCEDURE asc_ar_event_revenue_proc ()
nLoc_num NUMBER;
vCode VARCHAR2(32) := 'ASC_AR_EVENT_REVENUE_PROC';
vErrBuf VARCAHR2(2000);
vPlace VARCHAR2(10);
nCount NUMBER;
BEGIN
nLoc_num := 10;
SELECT ‘10’ -- First Select Statement in Code
INTO vPlace
FROM DUAL;
nLoc_num := 20;
SELECT ‘20’ -- Second Select Statement in Code
INTO vPlace
FROM DUAL;
nLoc_num := 30;
SELECT ‘30’ -- Third Select Statement in Code
INTO vPlace
FROM DUAL;
nLoc_num := 40;
INSERT INTO ASC_TEMP
SELECT 10, 20, 30
FROM DUAL;
nCount := SQL%ROWCOUNT;
DBMS_OUTPUT.put_line (vCode ‘ Insert total: ‘ TO_CHAR (nCount));
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
pErrBuf := SUBSTRB(vCode ‘-‘ 'Loc: ' TO_CHAR(nLoc_num)
' Oracle Code-Message:' SQLCODE '-' SQLERRM, 1,2000);
INSERT INTO ASC_ERROR_TBL
(ID, RECORD_ID, MESSAGE, CREATED_BY, CREATION_DATE, PROGRAM, LOC)
VALUES
(ASC_ERROR_TBL _S.nextval, v_place, pErrBuf, '-1', SYSDATE, vCode, nLoc_num);
COMMIT;
raise_application_error(-20000, pErrBuf);
END;
Triggers cannot contain rollback so here is another example of code.
CREATE OR REPLACE TRIGGER ASC_TRIGGER_UI1
BEFORE
UPDATE
ON ASC.ASC_TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
nLoc_num NUMBER;
vCode VARCHAR2(32) := ‘ASC_TRIGGER_UI1’;
vErrBuf VARCAHR2(2000);
vPlace VARCHAR2(10);
BEGIN
nLoc_num := 10;
SELECT ‘10’ -- First Select Statement in Code
INTO vPlace
FROM DUAL;
nLoc_num := 20;
SELECT ‘20’ -- Second Select Statement in Code
INTO vPlace
FROM DUAL;
SELECT ‘30’ -- Third Select Statement in Code
INTO vPlace
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
pErrBuf := SUBSTRB(vCode ‘-‘ 'Loc: ' TO_CHAR(nLoc_num)
' Oracle Code-Message:' SQLCODE '-' SQLERRM, 1,2000);
raise_application_error (-20000, vErrBuf);
END;
