Previous	Next		Contents	Index	Navigation	Glossary	Library

For example, suppose you want to define an AutoAccounting rule that provides a region code based upon the organization that is managing a project. You specify an intermediate value source of Parameter, since the project-managing organization is a predefined parameter; you specify Project Organization as the parameter name.

SQL statements to derive new parameters

This type of use may affect processing performance and may not be supported based on the AutoAccounting function. If you determine that you need rules that reference application tables to derive additional values not provided by the AutoAccounting parameters, please contact your Oracle technical support representative before proceeding.

Using a SQL Select Statement in AutoAccounting Rules

Examples of AutoAccounting Rules with a SQL statement
The business rule translates to this logic:
If Project Type is 'Overhead' then use Expenditure Organization else use Project Organization
Rule
Name	Indirect Cost Center
Description	Determine the cost center for indirect costs, using Expenditure Organization for Overhead projects and Project Organization for all other indirect projects
Intermediate Value Source	SQL Statement
SQL Select Statement	select decode(:1, :2, :3, 'Overhead') from sys.dual
Segment Value Source	Segment Value Lookup Set
Lookup Set	Organization to Cost Center
Rule Parameters
Sequence	Parameter Name
1	Project Type
2	Expenditure Organization
3	Project Organization
In another example, Fremont Corporation wants to base the account segment value on the first descriptive flexfield segment for the expenditure item (PA_EXPENDITURE_ITEMS_ALL.ATTRIBUTE1).
Rule
Name	Descriptive flexfield segment
Description	Populate the AutoAccounting account segment value with the first descriptive flexfield segment value in the expenditure item.
Intermediate Value Source	SQL Statement
SQL Select Statement	SELECT attribute1 FROM PA_EXPENDITURE_ITEMS_ALL WHERE EXPENDITURE_ITEM_ID = :1
Rule Parameter
Sequence	Parameter Name
1	Expenditure Item ID

The SQL statement in the first example uses the SQL function of decode, which provides if/then logic - it compares values of an expression to determine a resulting value.

The decode statement refers the table sys.dual which always contains only one record.

For more information on the function 'decode', refer the SQL Language Reference Manual. For more information on the table 'dual', refer to the Oracle RDBMS Database Administrator's Guide.

Notation of SQL statement rule

You must define rule parameters with SQL statement rules. You must reference each parameter that you use with a different number, even if you are using the same parameter many times in a SQL statement. You can use up to 19 rule parameters per rule. You must reference the rule parameter sequence numbers in numerical order. For example, :3 cannot be referenced before :2.

You must not add a semicolon at the end of the SQL statement.

Selecting a Segment Value Source

Intermediate Value	Supply the intermediate value as a segment value; do not use a lookup set
Segment Value Lookup Set	Look up the intermediate value in a lookup set; translate the intermediate value into the corresponding segment value

You do not always need to use a lookup set when you write an AutoAccounting rule. If you define a simple constant rule, you probably do not need to use a lookup set to supply a segment value, since you generally supply a valid segment value as the constant.

For example, if you use a constant intermediate value, such as account number 4100, the intermediate value is already a segment value and therefore needs no translation. Or, if the value of a parameter already is a suitable segment value (as might be the case if you use the project number as part of your chart of accounts), you do not need a lookup set to translate it into a segment value.

However, if you use a parameter-based intermediate value such as an organization name, you need to specify the name of a lookup set that maps the names of organizations to the corresponding segment value.

You need to define a lookup set before you can use it in a rule. However, if you prefer to define your rules before completing your lookup sets, you can define each lookup set's name and description, then define the intermediate values and segment values later.

AutoAccounting Rules Window Reference

You assign rules to AutoAccounting transactions using the Assign Rules window.

Rule Name. Enter a unique, descriptive name for this AutoAccounting rule.

Intermediate value region

Source. Enter the source from which you want to determine an intermediate value for this rule. Oracle Projects provides the following intermediate value sources:

• Constant. Use a particular intermediate value which is not context sensitive (usually an Accounting Flexfield segment code).

• Parameter. Use a predefined parameter as an intermediate value; make rule context-sensitive based on one value.

• SQL Select Statement. Execute a SQL select statement to retrieve an intermediate value; make rule context sensitive based on more than one value.

If you specified Parameter as this rule's intermediate value source, enter the parameter you want Oracle Projects to supply as the intermediate value.

If you specified SQL Select Statement the SQL Editor appears when you enter this field. Enter the statement you want Oracle Projects to use to retrieve an intermediate value.

Segment Value region

• Intermediate Value. Choose this source if this rule's intermediate value is already a valid segment value.

• Segment Value Lookup Set. Choose this source if this rule's intermediate value is not a valid segment value and must be mapped to a segment value through a lookup set.

Lookup Sets button. This button navigates to the AutoAccounting Lookup Sets window.

Rule Parameters region

If you specified either Constant or Parameter as this rule's intermediate value source, Oracle Projects skips this region.

Sequence. Enter the numeric value that corresponds to a parameter in the SQL select statement this AutoAccounting rule uses.

Parameter Name. Select the parameter that corresponds to the sequence number you entered in the previous field. You can choose any AutoAccounting parameter.

See Also