Table Functions

Example of how to use Table Functions

Checking that argument is present in Table

Returning value of a given Table entry

Returning part of value of given Table entry

About Table Functions

You use Table Functions to access data stored in a Table object to check that the given argument is present in the Table or return the value or part of the value of a given Table entry.

The syntax for the Table Function depends on the function:

  • check that the given argument is present in the Table with respect to the Date of application for rules (DAR) or a specified date:
    $CHECKTAB(
    table_name; operand1)
    $CHECKTABD(
    table_name; operand1; operand2)
  • return the value of a given Table entry depending on the argument and either the Date of application for rules (DAR) or a specified date:
    $ACCTAB(
    table_name; operand1)
    $ACCTABD(
    table_name; operand1; operand2)
  • return a part of the value of a given Table entry depending on the argument and either the Date of application for rules (DAR) or a specified date:
    $EXTTAB(
    table_name;"pppplllt"; operand1)
    $EXTTABD(
    table_name;"pppplllt"; operand1; operand2)

For more information on Tables, refer to Table: start here

Example of how to use Table Functions

Each column in a Table is defined by a name and a Data Class. Each line in a Table is accessible by its key and has a start date and an end date. The start and end dates enable you to define a validity period for the data in a line of the Table. For example, a Table could store monetary exchange rates that change over time. You can set a validity period for each exchange rate value listed in the Table, and retrieve the values depending on the date that you specify.

The validity periods for a given set of key columns cannot overlap. The end date of the first version defines the start date of the next one. That is, end date +1.

The following Table, "COMM_RT", relates to commission rate information for transactions. It contains:

  • Three Key columns, clearly indicated by the key icon , that uniquely identify a line in the Table.
    • TRANS: Transaction Type (three alphanumeric characters)
    • SRC_CD: Source Country Code (ten alphanumeric characters)
    • DEST_CD: Destination Country Code (ten alphanumeric characters)
  • Two Value columns which identify columns that contain the values you can retrieve:
    • RATE: Rate (three numeric characters)
    • COMM_FEE: Minimum Commission Fee (three numeric characters)
  • Default Start Date and End Date columns which identify a validity period for the data in each line of the Table. The dates are given in the format defined on your Windows computer.

Table: COMM_RT

TRANS

SRC_ID

DEST_CD

RATE

COMM_FEE Start Date End Date

PTM

US

UK

0.10

100

06/01/2003

07/31/2003

PTM

US

UK

0.13

110

08/01/2003

08/31/2003

PTM

US

EURO

0.15

125

07/01/2003

08/31/2003

PTM

US

JAPAN

0.13

120

07/01/2003

08/31/2003

PTM

EURO

JAPAN

0.10

100

07/01/2003

08/31/2003

PTL

US

EURO

0.17

130

07/01/2003

08/31/2003

To access and retrieve the values stored in the COMM_RT Table, use the following parameters:

  • Table name
    Identifies the Table. In this example the Table name is COMM_RT.
  • Table key
    Represents the concatenation of key columns defined in the interface.
    For example, PTLUSEURO.
  • Table value
    Represents the concatenation of value columns defined in the interface.
    For example,"0.17130" corresponding to the rate and commission fee values for the "PTLUSEURO" key
  • Date expression
    Sets the date used to search the Table to retrieve a Table entry associated with a specific key column whose validity period contains the given date.
    For example, you can include the $SYSDATE function in the date expression to return the system date and then use the system date as a search criterion.

Specifying the Table key

In each of the Table Functions, AccountingIntegrator Enabler uses the operand1 parameters to search for the Table key. The operand1 is an expression that returns a value with a data type compatible with the key data type defined in the Table.

  • Example with Business-Document field
    For example, if you have previously defined three Business-Document fields TRANS, for the transaction type, SRC_ID for the source country code and DEST_CD for the destination country code, use the following syntax to search for the COMM_RT Table entry that corresponds to the Table key:
    $ACCTAB
    (COMM_RT;TRANS&SRC_ID&DEST_CD)
    In this example, each field corresponds to a column in the table.
  • Example with Constant value
    For example, to search for the Table entry in the COMM_RT Table corresponding to the Table key PTLUSEURO, use the following syntax:
    $ACCTAB
    (COMM_RT;PTLUSEURO)
  • Example with Table value
    For example, to search for the Table entry in the COMM_RT Table corresponding to the Table key given by the value of a Table entry in the COUNTRIES Table, use the following syntax:
    $ACCTAB
    (COMM_RT; $ACCTAB (COUNTRIES;"JAPAN")
  • Example with Pre-Calculation
    For example, if you previously defined a Pre-Calculation called PCC1 containing the expression PTLUSEURO, use the following syntax to search for the COMM_RT Table entry that corresponds to the PTLUSEURO Table key:
    $ACCTAB
    (COMM_RT;PCC1)
  • Example with Variable
    For example, if you previously defined a Variable TRANSACTION set to PTLUSEURO, use the following syntax to search for the COMM_RT Table entry that corresponds to this Table key:
    $ACCTAB(ACCOUNT;@TRANSACTION)

$CHECKTAB

Description

$CHECKTAB checks that the given argument is present in the Table with respect to the Date of application for rules (DAR) or a specified date.

Syntax

$CHECKTAB(table_name; operand1)

Return

If the Table entry exists, $CHECKTAB returns the value of the Table key.

If the value does not exist, AccountingIntegrator Rule Engine rejects the Input-Event.

Parameters

table_name
Enter the name of the Table.

operand1is an expression whose value is used to search for the Table key.

You can specify this parameter with any expression that returns a value with a data type compatible with the key data type defined in the Table.

[FOR DETAILS Specifying the Table key]

Examples

Target field

Mapping Rule

ZONEOUTEV1

IF $CHECKTAB ( COMM_RT ; ZONEINPEV1 ) = ZONEINPEV1 THEN ZONEINPEV1&VALDEFINPEV

If the value of ZONEINPEV1 exists as an argument in theCOMM_RT Table, AccountingIntegrator  Rule Engine sets the value of the  ZONEOUTEV1 target field to ZONEINPEV1&VALDEFINPEV.

Otherwise, AccountingIntegrator Rule Engine rejects the Input-Event.

For example, if the value of ZONEINPEV1 is "PTMUSUK", this corresponds to a Table entry and so the function returns without error. However, if the value of ZONEINPEV1 is "XXX USFRANCE", this key does not exist in the Table and so AccountingIntegrator Rule Engine rejects the Input-Event.

ZONEOUTEV2

$CHECKTAB ( COMM_RT ; REFA&REFB )

If the value of REFA&REFB exists as an argument in theCOMM_RT Table, AccountingIntegrator Rule Engine uses this value to complete the ZONEOUTEV2 field.

Otherwise, AccountingIntegrator Rule Engine rejects the Input-Event.

$CHECKTABD

Description

$CHECKTABD checks that an entry is present in the Table for the date that you specify by the operand2 parameter.

Syntax

$CHECKTABD(table_name; operand1; operand2)

Return

If the Table entry exists in the given validity period, the value retrieved is the value of the Table key.

If the value does not exist, AccountingIntegrator Rule Engine rejects the Input-Event.

Parameters

table_name

operand1

operand2

You can specify this parameter using any of the objects listed for operand1.
The data type used for operand2 must be either:

  • Date
  • Numeric (The length must be compatible with a date, that is N(6), N(7) or N(8)). In this case, only Business-Document fields or Pre-Calculation are authorized.

Example

$CHECKTABD(COMM_RT;ZONEINPEV1;$SYSDATE)

The Function searches the COMM_RT Table for the key value ZONEINPEV1, with a validity period that includes the current system date. In this case, no Table entry meets this date criterion since all validity periods only go as far as 08/31/2003.

$ACCTAB

Description

$ACCTAB searches for the line specified by the Table key parameter (operand1), and then returns the corresponding value.

Syntax

$ACCTAB(table_name; operand1)

Return

If the Table entry exists, the value returned is a concatenation of the associated key columns.

If the Table entry does not exist and you have not defined a default value, AccountingIntegrator Rule Engine rejects the Input-Event by implementing theInput-Event rejection rules you specified in the General tab.

Parameters

table_name

operand1

Examples

A Business-Field is defined using the expression $ACCTAB(COMM_RT;ZONEINPEV1).

  • If ZONEINPEV1 corresponds to an existing key in the COMM_RT Table, for example, "PTLUSEURO", the valueis 017130.
  • If ZONEINPEV1 does not correspond to an existing key, and you have not defined a default value, the rejection of the Input-Event depends on the options you specified in the General tab .

$ACCTABD

Description

$ACCTABD returns the value of the Value column depending on the date specified by operand2.

Syntax

$ACCTABD(table_name; operand1; operand2)

Return

If the Table entry exists in the given validity period,the value returned is a concatenation of the associated key columns.

If the Table entry does not exist and you have not defined a default value,  AccountingIntegrator Rule Engine rejects the Input-Event by implementing theInput-Event rejection rules you specified in the General tab.

Parameters

table_name

operand1

operand2

Examples

A Business-Field is defined using the expression  $ACCTAB(COMM_RT;ZONEINPEV1;08112000)

  • If ZONEINPEV1 corresponds to an existing key in the COMM_RT Table (for example,  "PTMUSUK" which has a validity period that includes the date 08/11/2003, the value of the enrichment is 013110.
  • If ZONEINPEV1 does not correspond to an existing key, and you have not defined a default value, the rejection of the Input-Event depends on the options you specified in the General tab.

$EXTTAB

Description

$EXTTAB returns part of the contents of the specified Value column in a given Table.

Syntax

$EXTTAB(table_name;"pppplllt"; operand1)

Return

If the Table entry exists, the value returned is an extraction of the associated key columns defined by the parameter pppplllt.

If the Table entry does not exist and you have not defined a default value,  AccountingIntegrator Rule Engine rejects the Input-Event by implementing the Input-Event rejection rules you specified in the General tab.

Parameters

table_name

pppplllt

  • pppp is the position in the Table value from which part of the value (four numeric characters) is to be extracted
  • lll is the length of the part of the Table value (three numeric characters) to be extracted
  • t is the data type of the part to be extracted (Alphanumeric, Numeric, Date)

pppp + lll must not exceed the total length defined for the value in the Table structure.

t must be compatible with the data type defined for the value in the Table structure.

operand1

Example

Extending the example used in $ACCTAB, a Business-Field is defined using the expression $EXTTAB(COMM_RT;"0001003N";ZONEINPEV1).

  • If ZONEINPEV1 corresponds to an existing key in theCOMM_RT Table, for example "PTLUSEURO", the value of the enrichmentusing $ACCTAB is 017130. However using the $EXTTAB function, the value extracted is a numeric starting from position 1, of three characters in length and is therefore 017.
  • For the same key value, if the expression is  $EXTTAB(COMM_RT;"0004003N";ZONEIE1), the result is 130.
  • If ZONEINPEV1 does not correspond to an existing key, and you have not defined a default value, the rejection of the Input-Event depends on the options you specified in the General tab.

$EXTTABD

Description

$EXTTABD returns part of the contents of the specified Value column in a given Table for a given date, provided by operand2.

Syntax

$EXTTABD(table_name;"pppplllt"; operand1; operand2)

Return

If the Table entry exists in the given validity period, the value returned is an extraction of the associated key columns defined by the parameter pppplllt.

If the Table entry does not exist and you have not defined a default value, AccountingIntegrator Rule Engine rejects the Input-Event by implementing theInput-Event rejection rules you specified in the General tab.

Parameters

table_name

pppplllt

operand1

operand2

Example

Extending the example used in $ACCTABD, a Business-Field is defined using the expression $EXTTABD(COMM_RT;"0001003N";ZONEINPEV1;08112003).

  • If ZONEINPEV1 corresponds to an existing key in the COMM_RT Table, for example "PTMUSUK", which has a validity period that includes the date 08/11/2003, the value of the enrichment using $ACCTABD is  013110. However, using the $EXTTABD function, the value extracted is a numeric starting from position 1, of three characters in length and is therefore 013.
  • If ZONEINPEV1 does not correspond to an existing key, and you have not defined a default value, the rejection of the Input-Event depends on the options you specified in theGeneral tab.

Back to top

Related Links