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.
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.
|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|
|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|
|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).|
|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|
WHERE EXPENDITURE_ITEM_ID = :1
|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.
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.
|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|
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.
You assign rules to AutoAccounting transactions using the Assign Rules window.
Rule Name. Enter a unique, descriptive name for this AutoAccounting rule.
Source. Enter the source from which you want to determine an intermediate value for this rule. Oracle Projects provides the following intermediate value sources:
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.
Lookup Sets button. This button navigates to the AutoAccounting Lookup Sets window.
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.