Transaction Flexfields
Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines. Because they are unique for each transaction line, they can also be used to reference and link to other lines. Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture.
There are four types of transaction flexfields:
- Line Transaction Flexfield
- Reference Transaction Flexfield
- Link-To Transaction Flexfield
- Invoice Transaction Flexfield
You must define the Line Transaction flexfield. AutoInvoice always uses the Line Transaction flexfield structure for both the Link-to and Reference information when importing invoices. You need to explicitly define the Link-to, Reference, and Invoice Transaction flexfield structures only if this information is to be displayed on a custom window.
Receivables gives you the option of displaying transaction flexfield information for imported invoices in lists of values throughout the product. Use the System Profile Option AR: Transaction Flexfield QuickPick to select the Transaction Flexfield Segment you want to display. For example, if you want to be able to reference the order number for imported invoices when using any invoice list of values, you need to assign the transaction flexfield segment that holds the order number to the AR: Transaction Flexfield QuickPick profile option. The order number will now display in the reference column of all invoice lists for imported invoices.
Line Transaction Flexfield
Use columns INTERFACE_LINE_ATTRIBUTE1-15 and INTERFACE_LINE_CONTEXT to define the line transaction flexfield. Line transaction flexfields are unique for each record in the interface table and therefore can be used as record identifiers.
Reference Transaction Flexfield
Reference Transaction flexfields have the same structure as the Line Transaction flexfields.
These are used to refer to previously processed transactions. For example, you might want to import a credit memo and apply it to an invoice or associate an invoice to a specific commitment. To refer a credit memo to a specific invoice use the REFERENCE_LINE_ATTRIBUTE1-15 and REFERENCE_LINE_CONTEXT columns of the credit memo to enter the Line Transaction flexfield of the invoice.
Link-To Transaction Flexfield
Link-To Transaction flexfields also have the same structure as the Line Transaction flexfield.
Use Link-To Transaction flexfields to link transaction lines together in the interface table. For example, you might want to import tax and freight charges that are associated with specific transaction lines. If you want to associate a specific tax line with a specific transaction line, use the LINK_TO_LINE_ATTRIBUTE1-15 and LINK_TO_LINE_CONTEXT columns of the tax line to enter the Line Transaction flexfield of the invoice.
Invoice Transaction Flexfields
Create a new flexfield with a similar structure as the Line Transaction flexfield, but only include header level segments. For example, if the Line Transaction flexfield structure has four segments and the last two segments contain line level information, define your Invoice Transaction flexfield using the first two segments only. Segments included in the Invoice Transaction Flexfield should be included in the AutoInvoice grouping rules.
Transaction Flexfields: An example
The following example illustrates how records are linked in the interface table using the Link-To or the Reference Transaction flexfield columns.
Consider an invoice against a commitment. This invoice has 2 line records, 1 header freight record and one tax record. The records will be represented in the interface table as follows:
Notes:
- The transaction type for records of an invoice is INV.
- Each record in the interface table is uniquely identified by its Line Transaction flexfield. In the above example 2 segments have been enabled for the Line Transaction flexfield service context. Therefore the combination of context plus the 2 segments should be unique.
- Tax records always have to be linked to a line record. In the above example, the tax record is linked to the first line record, Service A 1.
- Since the freight is at the header level, it is not linked to any line record via the Link-To Transaction flexfield.
- Records with different contexts can be grouped together into one invoice.
- If the invoice is against an existing commitment, populate the Reference_line_id (Ref ID) column with the unique identifier (customer_trx_line_id) of the commitment.
Consider a credit memo that credits the freight and the first line of the previous invoice. It will be represented in the interface table as follows:
- The transaction type for credit memos is CM.
- Link credit memos to invoices either via the Reference Transaction flexfield or the reference_line_id. The reference_line_id is the unique id within Receivables.
An on-account credit will be represented as follows:
- The transaction type for on-account credits is CM.
- AutoInvoice assumes all records with transaction type CM to be on-account credits if there are no values filled in the reference_line_id column (Ref ID) or the Reference Transaction flexfield.
Indexing Transaction Flexfields
We suggest that you create indexes on your Transaction Flexfield columns if you want to query transaction flexfield information in your invoice headers and lines. Additionally, without the indexes the validation portions of the AutoInvoice program could be slow. You should define unique, concatenated indexes on the following tables and columns that you use for your Transaction Flexfield header and line information:
Table
|
| Columns
|
RA_CUSTOMER_TRX_LINES
|
| interface_line_attribute1-15
|
RA_CUSTOMER_TRX
|
| interface_header_attribute1-15
|
RA_INTERFACE_LINES
|
| interface_line_attribute1-15
|
Navigate to the Descriptive Flexfield Segments window, then query your Line Transaction Flexfield. Note each context of this Flexfield and, for each context, note which segments are enabled using interface line attribute columns from the RA_INTERFACE_LINES table.
You should then create unique, concatenated indexes for the same interface line attribute columns in the RA_CUSTOMER_TRX_LINES and RA_INTERFACE_LINES tables and for the same interface header attribute columns in the RA_CUSTOMER_TRX table.
If you just have one context defined, then you only need to create one index for each table mentioned above. However, if you have multiple contexts defined, you may want to create multiple indexes per table. Use the example below to help you decide how to set up your indexes.
Suppose your Line Transaction flexfield has three contexts which are set up as follows:
|
Context1
| Interface_line_attribute1
|
Context1
| Interface_line_attribute2
|
|
Context2
| Interface_line_attribute1
|
Context2
| Interface_line_attribute2
|
Context2
| Interface_line_attribute3
|
|
Context3
| Interface_line_attribute3
|
Context3
| Interface_line_attribute9
|
You should define the combination of indexes which best meets your needs. In the example above, you could create three indexes per table, one for each context. Alternatively, you could create just two indexes: one for context3 and another for context1. The latter would be used by context2 because contexts1 and 2 have the same first two attribute columns.
Use the following syntax for your Create Index Statement:
$ sqlplus <AR username>/<AR password>
SQL> CREATE [UNIQUE] INDEX index ON
{Table (column1, column2, ...)
|CLUSTER cluster}
|INITRANS n] [MAXTRANS n]
[TABLESPACE tablespace]
[STORAGE storage]
[PCTFREE n]
[NOSORT];
See Also
Using AutoAccounting
Using Grouping Rules to Create Transactions