Writing PL/SQL Procedures/Functions (Oracle Cost Management)


	Previous	Next		Contents	Index	Glossary	Library

Writing PL/SQL Procedures/Functions

To help you to write PL/SQL functions for client extensions, we first provide you with a brief technical background of PL/SQL functions. Then, we provide you with information on how to use predefined functions and parameters in writing your own functions. We recommend that you read the PL/SQL User's Guide and Reference to learn more about PL/SQL.

Packages

Packages are database objects that group logically related PL/SQL types, objects, and subprograms. Packages usually consist of two files: a package specification file and a package body file. The specification file is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use in the package. It contains the name of the package and functions function declarations. The package body defines cursors and subprograms, contains the actual PL/SQL code for the functions, and so implements the specification.

Functions

Functions are subprograms within a package that are invoked by the application, perform a specific action, and compute a value. Functions define what parameters will be passed in as context for the program, how the inputs are processed, and what output is returned. A function consists of the following elements:

Inputs Each function has predefined input parameters, which must be passed in the predefined order. The parameters identify the transaction being processed and the context in which the program is called. You can derive additional inputs from any Oracle table based on the predefined input parameters.

Logic The function uses the inputs and performs any logical processing and calculations. The program can be a simple program, such that it returns a fixed number, or it can be a complex algorithm that performs a number of functions.

Outputs Each function returns whatever value you define it to return. For example, your function for account generation extensions may return a null value if the transaction passes all validation rules; or an error message if validation fails.

Syntax for Functions

A function consists of two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the function name or a parameter list. The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional error handling part. You write functions using the following syntax:

	FUNCTION name [  (parameter [, parameter,...])  ] RETURN DATATYPE IS
		[local declarations]
	BEGIN 
		executable statements
	[EXCEPTION
		exception handlers]
	END [name];

The parameter syntax above expands to the following syntax:

parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr]

For more information, refer to the PL/SQL User's Guide and Reference Manual.

Using Template Functions

Cost Management provides you with template functions for each client extension that you can use to write your own functions. Each function contains predefined parameters that are passed into the function by the program that calls the function; you cannot change these predefined input parameters.

The Client Extensions table lists each client extension and its predefined template function filenames. The template function files are stored in the Cost Management admin/sql directory.

Suggestion: Review the appropriate files before you design and implement a client extension. They provide a lot of useful information, including the predefined input parameter list and example case studies.

Suggestion: You should make a copy of these template files in a directory used by your company to store code that you have written. You should make changes to these copies of the files instead of writing directly into these template files. These template files will be replaced when the software is upgraded between releases. Use your modified files to reinstall your functions after an upgrade to a new release of Cost Management.

Writing Logic in Your PL/SQL Functions

You write the logic in the PL/SQL functions based on the functional specifications created during the design process. Before you begin to write the client extension PL/SQL functions, you should have a clear understanding of the client extension functions; including the inputs and outputs, the error handling of the extension, along with any example functions provided for each extension. Read the appropriate client extension essays and template functions to obtain detailed information about the client extensions.

As you determine how to best write the client extension, you should consider these issues:

Can I derive every derived input parameter based on the data structures known?

What outputs should the client extension return?

How does the client extension handle exceptions?

Are there functions which I can write which are reusable across similar client extensions?

How I can write logical, well commented code that is easy to maintain and debug?

How do I test and debug this client extension?

Are there any performance considerations in the client extension? If so, what are they and how do I address them?

Attention: You must not commit data within your PL/SQL function. Cost Management processes that call your functions handle the commit logic.

Storing Your Functions

After you write your functions and ensure that the specification file correctly includes any functions that you have defined, you need to compile and store the functions in the database in the Applications Oracle username. You must install the package specification before the package body.

The syntax for compiling and storing PL/SQL functions is included in the template function files. Assuming you have written your functions using copies of these template function files, you can use these steps to compile and store your functions:

Change to the directory in which your files are stored (use the command that is appropriate to your operating system)

$ sqlplus <apps username>/<apps password>

SQL> @<spec_filename>.pls

SQL> @<body_filename>.pls

For example, you use the following commands to install your account generation extension (assuming your Oracle Applications Oracle username/password is apps/apps):

$ sqlplus apps/apps

SQL> @CSTPSCHK.pls apps apps @ CSTSCHKS.pls

SQL> @CSTPSCHK.pls apps apps @ CSTSCHKB.pls

If you encounter compilation errors in trying to create your packages and its functions, you must debug the errors, correct your package definitions, and try to create your packages again. You must successfully compile and store your package and its functions in the database before you can use the client extensions in Cost Management.

Testing Your Functions

After you have created your client extension functions, you must test your client extension definitions within the processing flow of Cost Management to verify the results are as you expect.

Contents

Index

Glossary

Library

Inputs	Each function has predefined input parameters, which must be passed in the predefined order. The parameters identify the transaction being processed and the context in which the program is called. You can derive additional inputs from any Oracle table based on the predefined input parameters.
Logic	The function uses the inputs and performs any logical processing and calculations. The program can be a simple program, such that it returns a fixed number, or it can be a complex algorithm that performs a number of functions.
Outputs	Each function returns whatever value you define it to return. For example, your function for account generation extensions may return a null value if the transaction passes all validation rules; or an error message if validation fails.