Oracle E-Business Suite Developer's Guide

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Overview of Coding Standards

Overview of Coding Standards
The Standard Development Environment
Overview of Building an Oracle E-Business Suite Application

Setting Up Your Application Framework

Overview of Setting Up Your Application Framework

Building Database Objects

Overview of Building Your Database Objects
Table Registration API
XML Definition File Utility

Using PL/SQL in Oracle E-Business Suite

Overview of Using PL/SQL in Oracle E-Business Suite
Replacements for Oracle Forms Built-ins
Coding Item, Event and Table Handlers

Setting the Properties of Container Objects

Setting the Properties of Container Objects
Modules
Windows
Canvases
Blocks
Regions

Setting the Properties of Widget Objects

Setting the Properties of Widget Objects
Text Items
      Date Fields
Display Items
Poplists
Option Groups
Check Boxes
Buttons
Lists of Values (LOVs)
      LOV Behaviors
      LOVs in ENTER-QUERY Mode
Alerts
Editors
Flexfields
Setting Item Properties
      Using APP_ITEM_PROPERTY.SET_PROPERTY
      Item Properties with Unique Oracle E-Business Suite Behavior
      Impact of Item-level and Item-instance-level Settings
      Setting Properties at Design Time
      Setting Visual Attributes Programmatically

Controlling Window, Block, and Region Behavior

Controlling Window Behavior
Positioning Windows Upon Opening
Closing Windows
Setting Window Titles Dynamically
Controlling Block Behavior
Coding Master-Detail Relations
Coordination Between Windows
Implementing a Combination Block
Coding Tabbed Regions
      Definitions
      Tabbed Region Behavior
      Three Degrees of Coding Difficulty
Implementing Tabbed Regions
Creating the Layout in Forms Developer
Coding Your Tab Handler
Tab Handler Logic
WHEN-TAB-PAGE-CHANGED Logic
WHEN-NEW-ITEM-INSTANCE Logic
Handling Dynamic Tabs
Other Code You May Need
Coding Alternative Region Behavior
      Alternative Regions
      Example: Coding an Alternative Region
Controlling Records in a Window
      Duplicating Records
      Renumbering All Records in a Window
Passing Instructions to a Form

Enabling Query Behavior

Overview of Query Find
      Raising Query Find on Form Startup
Implementing Row-LOV
      Implementing Find Windows

Coding Item Behavior

Item Relations
      Dependent Items
      Conditionally Dependent Item
      Multiple Dependent Items
      Two Master Items and One Dependent Item
      Cascading Dependence
      Mutually Exclusive Items
      Mutually Inclusive Items
      Mutually Inclusive Items with Dependent Items
      Conditionally Mandatory Items
Defaults
Integrity Checking
      Uniqueness Check
      Referential Integrity Check
The Calendar
      Advanced Calendar Options
      Calendar Examples
CALENDAR: Calendar Package

Controlling the Toolbar and the Default Menu

Pulldown Menus and the Toolbar
      Menu and Toolbar Entries
      Save and Proceed
      Synchronizing
      Application-Specific Entries: Special Menus
      Customizing Right-Mouse Menus (Popup Menus)
APP_POPUP: Right-Mouse Menu Control
      APP_POPUP.INSTANTIATE
APP_SPECIAL: Menu and Toolbar Control
      APP_SPECIAL.INSTANTIATE
      APP_SPECIAL.ENABLE
      APP_SPECIAL.GET_CHECKBOX
      APP_SPECIAL.SET_CHECKBOX

Menus and Function Security

Overview of Menus and Function Security
      Terms
      Forms and Subfunctions
      How Function Security Works
      Using Form Functions
Function Security Standards
      General Function and Menu Standards
      Form Function Standards
      Subfunction Standards
Function Security Reports
Function Security APIs for PL/SQL Procedures
      FND_FUNCTION.TEST
      FND_FUNCTION.QUERY
      FND_FUNCTION.EXECUTE
      FND_FUNCTION.USER_FUNCTION_NAME
      FND_FUNCTION.CURRENT_FORM_FUNCTION
Forms Window
      Forms Block

Message Dictionary

Overview of Message Dictionary
      Major Features
      Definitions
Implementing Message Dictionary
      Create Your Message Directories
      Define Your Messages
      Create Your Message Files
      Code Logic to Set Up Messages
      Code Logic to Display Messages
Message Dictionary APIs for PL/SQL Procedures
      FND_MESSAGE.CLEAR
      FND_MESSAGE.DEBUG
      FND_MESSAGE.ERASE
      FND_MESSAGE.ERROR
      FND_MESSAGE.GET
      FND_MESSAGE.HINT
      FND_MESSAGE.QUESTION
      FND_MESSAGE.RETRIEVE
      FND_MESSAGE.SET_NAME
      FND_MESSAGE.SET_STRING
      FND_MESSAGE.SET_TOKEN
      FND_MESSAGE.SHOW
      FND_MESSAGE.WARN
Application Message Standards
      Definitions
      Message Naming Standards
      Message Numbering Standards
      Message Type Standards
      Message Description Standards
Message Content Standards
      Message Token Standards
      A Few General Guidelines for Writing Good Messages
      When the User Needs to Get Help
      Complex Messages
      Specific Types of Message Content
      Message Writing Style
      Special Purpose Messages
Messages Window

User Profiles

Overview of User Profiles
      Definitions
      Defining New User Profile Options
      Setting User Profile Option Values
      Setting Your Personal User Profile
Implementing User Profiles
      Predefined User Profile Options
FND_PROFILE: User Profile APIs
      FND_PROFILE.PUT
      FND_PROFILE.GET
      FND_PROFILE.VALUE
User Profile C Functions
      afpoget
      afpoput
Profiles Window

Flexfields

Overview of Flexfields
      Definitions
      Building a Flexfield into Your Application
      Flexfields and Application Upgrades
Implementing Key Flexfields
      Defining Key Flexfield Database Columns
      Registering Your Key Flexfield Table
      Registering Your Key Flexfield
      Add Your Flexfield to Your Forms
Implementing Descriptive Flexfields
      Planning for Reference Fields
      Defining Descriptive Flexfield Database Columns
      Adding a Descriptive Flexfield to a Table with Existing Data
      Protected Descriptive Flexfields
      Registering Your Descriptive Flexfield Table
      Registering Your Descriptive Flexfield
      Add Your Flexfield to Your Forms
Adding Flexfields to Your Forms
      Create Your Hidden Fields
      Create Your Displayed Fields
      Create Your Flexfield Definition
      Invoke Your Flexfield Definition from Several Event Triggers
Flexfield Definition Procedures
      Key Flexfield Definition Syntax
      Range (Type) Flexfield Definition Syntax
      Descriptive Flexfield Definition Syntax
      Flexfield Definition Arguments
      Flexfield Definition Examples
      Updating Flexfield Definitions
      Update Key Flexfield Definition Syntax
      Update Range (Type) Flexfield Definition Syntax
      Update Descriptive Flexfield Definition Syntax
      Updating Flexfield Definition Example
      Using Key Flexfields in Find Windows
      Using Range Flexfields in Query Find Windows
Troubleshooting Flexfields
Register Key Flexfields
      Register Key Flexfields Block
      Qualifiers Window
      Columns Window
Register Descriptive Flexfields
      Register Descriptive Flexfields Block
      Reference Fields Window
      Columns Window

Overview of Concurrent Processing

Overview of Concurrent Processing
      Basic Application Development Needs
      Major Features
      Definitions
Overview of Designing Concurrent Programs
      Submitting Concurrent Programs on the Client
      Using Concurrent Processing
      Automated Recovery for Concurrent Processing
Overview of Implementing Concurrent Processing
      Choosing Your Implementation

Defining Concurrent Programs

Defining Concurrent Programs
Concurrent Program Libraries Window

Coding Oracle Tools Concurrent Programs

Oracle Tool Concurrent Programs
      SQL*PLUS Programs
      PL/SQL Stored Procedures
      SQL*Loader
      Accepting Input Parameters For Oracle Tool Programs
      Naming Your Oracle Tool Concurrent Program

Coding Oracle Reports Concurrent Programs

Oracle Reports
      Concurrent Processing with Oracle Reports
      Oracle Reports Parameters
      Accessing User Exits and Profile Options
User Exits Used in Oracle Reports
      FND SRWINIT / FND SRWEXIT
      FND FLEXIDVAL / FND FLEXSQL
Using Dynamic Currency in Oracle Reports
      FND FORMAT_CURRENCY User Exit
Example Report Using FND FORMAT_CURRENCY
      Sample Report Output
      Procedure for Sample Report
Oracle Reports Troubleshooting
      Frequently Asked Questions

Coding C and Pro*C Concurrent Programs

Coding C and Pro*C Concurrent Programs
      Pro*C Concurrent Programs
      Header Files Used With Concurrent Programs
Concurrent Processing Pro*C Utility Routines

PL/SQL APIs for Concurrent Processing

Overview
FND_CONC_GLOBAL Package
      FND_CONC_GLOBAL.REQUEST_DATA
      FND_CONC_GLOBAL.SET_REQ_GLOBALS
      Example
FND_CONCURRENT Package
      FND_CONCURRENT.AF_COMMIT
      FND_CONCURRENT.AF_ROLLBACK
      FND_CONCURRENT.GET_REQUEST_STATUS (Client or Server)
      FND_CONCURRENT.WAIT_FOR_REQUEST (Client or Server)
      FND_CONCURRENT.SET_COMPLETION_STATUS (Server)
FND_FILE: PL/SQL File I/O
      FND_FILE.PUT
      FND_FILE.PUT_LINE
      FND_FILE.NEW_LINE
      FND_FILE.PUT_NAMES
      FND_FILE.CLOSE
      Error Handling
FND_PROGRAM: Concurrent Program Loaders
      FND_PROGRAM.MESSAGE
      FND_PROGRAM.EXECUTABLE
      FND_PROGRAM.DELETE_EXECUTABLE
      FND_PROGRAM.REGISTER
      FND_PROGRAM.DELETE_PROGRAM
      FND_PROGRAM.PARAMETER
      FND_PROGRAM.DELETE_PARAMETER
      FND_PROGRAM.INCOMPATIBILITY
      FND_PROGRAM.DELETE_INCOMPATIBILITY
      FND_PROGRAM.REQUEST_GROUP
      FND_PROGRAM.DELETE_GROUP
      FND_PROGRAM.ADD_TO_GROUP
      FND_PROGRAM.REMOVE_FROM_GROUP
      FND_PROGRAM.PROGRAM_EXISTS
      FND_PROGRAM.PARAMETER_EXISTS
      FND_PROGRAM.INCOMPATIBILITY_EXISTS
      FND_PROGRAM.EXECUTABLE_EXISTS
      FND_PROGRAM.REQUEST_GROUP_EXISTS
      FND_PROGRAM.PROGRAM_IN_GROUP
      FND_PROGRAM.ENABLE_PROGRAM
FND_REQUEST Package
      FND_REQUEST.SET_OPTIONS (Client or Server)
      FND_REQUEST.SET_REPEAT_OPTIONS (Client or Server)
      FND_REQUEST.SET_PRINT_OPTIONS (Client or Server)
      FND_REQUEST.SUBMIT_REQUEST (Client or Server)
      FND_REQUEST.SET_MODE (Server)
      Example Request Submissions
FND_REQUEST_INFO and Multiple Language Support (MLS)
      FND_REQUEST_INFO.GET_PARAM_NUMBER
      FND_REQUEST_INFO.GET_PARAM_INFO
      FND_REQUEST_INFO.GET_PROGRAM
      FND_REQUEST_INFO.GET_PARAMETER
      Example MLS Function
FND_SET: Request Set Loaders
      FND_SET.MESSAGE
      FND_SET.CREATE_SET
      FND_SET.DELETE_SET
      FND_SET.ADD_PROGRAM
      FND_SET.REMOVE_PROGRAM
      FND_SET.PROGRAM_PARAMETER
      FND_SET.DELETE_PROGRAM_PARAMETER
      FND_SET.ADD_STAGE
      FND_SET.REMOVE_STAGE
      FND_SET.LINK_STAGES
      FND_SET.INCOMPATIBILITY
      FND_SET.DELETE_INCOMPATIBILITY
      FND_SET.ADD_SET_TO_GROUP
      FND_SET.REMOVE_SET_FROM_GROUP
FND_SUBMIT: Request Set Submission
      FND_SUBMIT.SET_MODE
      FND_SUBMIT.SET_REL_CLASS_OPTIONS
      FND_SUBMIT.SET_REPEAT_OPTIONS
      FND_SUBMIT_SET.REQUEST_SET
      FND_SUBMIT.SET_PRINT_OPTIONS
      FND_SUBMIT.ADD_PRINTER
      FND_SUBMIT.ADD_NOTIFICATION
      FND_SUBMIT.SET_NLS_OPTIONS
      FND_SUBMIT.SUBMIT_PROGRAM
      FND_SUBMIT.SUBMIT_SET
      Examples of Request Set Submission

Standard Request Submission

Overview of Standard Request Submission
      Basic Application Development Needs
      Major Features
      Definitions
      Controlling Access to Your Reports and Programs
Implementing Standard Request Submission
      Developing Reports for Standard Request Submission
      Defining Parameter Validation
      Defining Your Report or Other Program
      Cross-application Reporting

Request Sets

Overview of Request Sets
      Stage Functions

Logging Framework Overview

Overview
Target Audience
Key Features
Terminology
Logging Configuration Parameters
      Overview
      AFLOG_ENABLED
      AFLOG_LEVEL
      AFLOG_MODULE
      AFLOG_FILENAME
      AFLOG_ECHO

Logging Guidelines for Developers

Overview
APIs
Handling Errors
Performance Standards
Module Source
      Module Name Standards
      Module Name Examples
Severities
Large Text and Binary Message Attachments
Automatic Logging and Alerting for Seeded Message Dictionary Messages
General Logging Tips
How to Log from Java
How to Log from PL/SQL
How to Log from C
How to Log in Concurrent Programs
How to Raise System Alerts

The Template Form

Overview of the TEMPLATE Form
Libraries in the TEMPLATE Form
Special Triggers in the TEMPLATE form
      Triggers That Often Require Some Modification
      Triggers That Cannot Be Modified

Attachments

Overview of Attachments
      Definitions
      How Attachments Work
      Attachments for Forms or Form Functions
      Attachments and Reports
Planning and Defining the Attachments Feature
      Planning to Add the Attachments Feature to Your Application
      Setting Up the Attachments Feature for Your Form
Document Entities Window
      Document Entities Block
Document Categories Window
      Document Categories Block
      Category Assignments Window
Attachment Functions Window
      Attachment Functions Block
      Categories Window
      Block Declaration Window
      Entity Declaration Window

Handling Dates

Overview
Year 2000 Compliance in Oracle E-Business Suite
Dates in Oracle E-Business Suite
Date Coding Standards
      Using Dates While Developing Application Forms
Troubleshooting
      Use the DATECHECK Script to Identify Issues
      Problems Observed During Testing
      Date Checklist

Customization Standards

Overview of Customizing Oracle E-Business Suite
      Basic Business Needs
      Definitions
      Determining Your Needs
Customization By Extension
      Defining Your Custom Application
      Adding a Form
      Adding a Report or Concurrent Program
      Adding a New Report Submission Form
      Adding Online Help
      Adding Menus
      Adding Responsibilities
      Adding Message Dictionary Messages
Customization By Modification
      Modifying an Existing Form
      Modifying an Existing Report
      Modifying an Existing C Program
      Modifying an Existing PL/SQL Stored Procedure
      Modifying Existing Online Help
      Modifying Existing Message Dictionary Messages
      Modifying Existing Menus and Responsibilities
Oracle E-Business Suite Database Customization
Oracle E-Business Suite Upgrades and Patches
Building Online Help for Custom Applications
      How the Help System Works
      Prepare Your Forms
      Create HTML Help Files
      Create a Help Navigation Tree
      Upgrades and Patches
Integrating Custom Objects and Schemas
Upgrading Custom Forms

Using the Custom Library

Customizing Oracle E-Business Suite with the CUSTOM Library
      Writing Code for the CUSTOM Library
      Events Passed to the CUSTOM Library
      When to Use the CUSTOM Library
      Coding Zoom
      Coding Generic Form Events
      Coding Product-Specific Events
      Support and Upgrading
Product-Specific Events in Oracle Application Object Library
      WHEN-LOGON-CHANGED Event
      WHEN-RESPONSIBILITY-CHANGED Event
CUSTOM Package
      CUSTOM.ZOOM_AVAILABLE
      CUSTOM.STYLE
      CUSTOM.EVENT
Example of Implementing Zoom Using the CUSTOM Library
      Modify the Form
      Modify the CUSTOM Library

APPCORE Routine APIs

Introduction to APPCORE Routine APIs
APP_COMBO: Combination Block API
      APP_COMBO.KEY_PREV_ITEM
APP_DATE and FND_DATE: Date Conversion APIs
      List of Date Terms
      APP_DATE.CANONICAL_TO_DATE and FND_DATE.CANONICAL_TO_DATE
      APP_DATE.DISPLAYDATE_TO_DATE and FND_DATE.DISPLAYDATE_TO_DATE
      APP_DATE.DISPLAYDT_TO_DATE and FND_DATE.DISPLAYDT_TO_DATE
      APP_DATE.DATE_TO_CANONICAL and FND_DATE.DATE_TO_CANONICAL
      APP_DATE.DATE_TO_DISPLAYDATE and FND_DATE.DATE_TO_DISPLAYDATE
      APP_DATE.DATE_TO_DISPLAYDT and FND_DATE.DATE_TO_DISPLAYDT
      APP_DATE.DATE_TO_FIELD
      APP_DATE.FIELD_TO_DATE
      APP_DATE.VALIDATE_CHARDATE
      APP_DATE.VALIDATE_CHARDT
      FND_DATE.STRING_TO_DATE
      FND_DATE.STRING_TO_CANONICAL
APP_EXCEPTION: Exception Processing APIs
      APP_EXCEPTION.RAISE_EXCEPTION
      APP_EXCEPTION.RETRIEVE
      APP_EXCEPTION.GET_TYPE
      APP_EXCEPTION.GET_CODE
      APP_EXCEPTION.GET_TEXT
      APP_EXCEPTION.RECORD_LOCK_EXCEPTION
      APP_EXCEPTION.RECORD_LOCK_ERROR
      APP_EXCEPTION.DISABLED
APP_FIELD: Item Relationship Utilities
      APP_FIELD.CLEAR_FIELDS
      APP_FIELD.CLEAR_DEPENDENT_FIELDS
      APP_FIELD.SET_DEPENDENT_FIELD
      APP_FIELD.SET_EXCLUSIVE_FIELD
      APP_FIELD.SET_INCLUSIVE_FIELD
      APP_FIELD.SET_REQUIRED_FIELD
APP_FIND: Query-Find Utilities
      APP_FIND.NEW
      APP_FIND.CLEAR
      APP_FIND.CLEAR_DETAIL
      APP_FIND.FIND
      APP_FIND.QUERY_RANGE
      APP_FIND.QUERY_FIND
APP_ITEM: Individual Item Utilities
      APP_ITEM.COPY_DATE
      APP_ITEM.IS_VALID
      APP_ITEM.SIZE_WIDGET
APP_ITEM_PROPERTY: Property Utilities
      APP_ITEM_PROPERTY.GET_PROPERTY
      APP_ITEM_PROPERTY.SET_PROPERTY
      APP_ITEM_PROPERTY.SET_VISUAL_ATTRIBUTE
APP_NAVIGATE: Open a Form Function
      APP_NAVIGATE.EXECUTE
APP_RECORD: Record Utilities
      APP_RECORD.TOUCH_RECORD
      APP_RECORD.HIGHLIGHT
      APP_RECORD.FOR_ALL_RECORDS
      APP_RECORD.DELETE_ROW
      APP_RECORD.VALIDATE_RANGE
APP_REGION: Region Utilities
      APP_REGION.ALT_REGION
APP_STANDARD Package
      APP_STANDARD.APP_VALIDATE
      APP_STANDARD.EVENT
      APP_STANDARD.SYNCHRONIZE
      APP_STANDARD.PLATFORM
APP_WINDOW: Window Utilities
      APP_WINDOW.CLOSE_FIRST_WINDOW
      APP_WINDOW.PROGRESS
      APP_WINDOW.SET_COORDINATION
      APP_WINDOW.SET_WINDOW_POSITION
      APP_WINDOW.SET_TITLE

FNDSQF Routine APIs

Introduction to FNDSQF Routine APIs
FND_CURRENCY: Dynamic Currency APIs
      FND_CURRENCY.GET_FORMAT_MASK (Client or Server)
      Currency Examples
FND_DATE: Date Conversion APIs
FND_GLOBAL: WHO Column Maintenance and Database Initialization
      FND_GLOBAL.USER_ID (Server)
      FND_GLOBAL.APPS_INITIALIZE (Server)
      FND_GLOBAL.LOGIN_ID (Server)
      FND_GLOBAL.CONC_LOGIN_ID (Server)
      FND_GLOBAL.PROG_APPL_ID (Server)
      FND_GLOBAL.CONC_PROGRAM_ID (Server)
      FND_GLOBAL.CONC_REQUEST_ID (Server)
FND_ORG: Organization APIs
      FND_ORG.CHANGE_LOCAL_ORG
      FND_ORG.CHANGE_GLOBAL_ORG
      FND_ORG.CHOOSE_ORG
FND_STANDARD: Standard APIs
      FND_STANDARD.FORM_INFO
      FND_STANDARD.SET_WHO
      FND_STANDARD.SYSTEM_DATE
      FND_STANDARD.USER
FND_UTILITIES: Utility Routines
      FND_UTILITIES.OPEN_URL
      FND_UTILITIES.PARAM_EXISTS

Naming Standards

Overview of Naming Standards
Naming Standards and Definitions
      Database Objects
      Form Objects
      File Standards
      PL/SQL Packages, Procedures and Source Files
      Reserved Words

Additional Features

Application Utilities Lookups and Oracle Application Object Library Lookups
Tables
Sequences
Views
Flexfields Test Form

SQL Trace Options

SQL Trace Options

Index