Transaction flexfields are descriptive flexfields that AutoInvoice uses to identify transactions and transaction 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 if you use AutoInvoice. You can use the Line Transaction Flexfield to reference and link to other lines because the Line Transaction Flexfield is unique for each transaction line. AutoInvoice always uses the Line Transaction Flexfield structure for both the Link-to and Reference information when importing invoices. You must 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 Invoice Transaction Flexfield information in the Reference column of invoice lists of values. Use the Reference Field Default Value field in the Transaction Sources window to select the Invoice Transaction Flexfield segment that you want to display. For example, if you want to be able to reference the order number for imported invoices when using an invoice list of values, you must assign the transaction flexfield segment that holds the order number in the Reference Field Default Value field in the Transaction Sources window. The order number will now display in the Reference column of invoice lists of values.
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.
The context that you specify in the INTERFACE_LINE_CONTEXT column of the RA_INTERFACE_LINES_ALL table determines what information AutoInvoice places in the INTERFACE_LINE_ATTRIBUTE1-15 columns. Oracle Receivables provides contexts for other Oracle applications that you use with AutoInvoice, for example Order Management. If you import transactions with AutoInvoice from a legacy system, you can define a new context for the Line Transaction Flexfield to distinguish these transactions from transactions that originated in Oracle applications.
Reference Transaction Flexfields have the same structure as the Line Transaction Flexfields.
Reference Transaction Flexfields are used to apply a credit memo to an invoice or associate an invoice to a specific commitment. For example, 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. To refer an invoice to a specific commitment, use the REFERENCE_LINE_ATTRIBUTE1-15 and REFERENCE_LINE_CONTEXT columns of the invoice to enter the Line Transaction Flexfield of the commitment.
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.
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.
This example illustrates how records described in the Line Transaction Flexfield are linked in the interface table using the Link-To or the Reference Transaction Flexfield columns.
Consider an invoice against a commitment with four records: two Line records, one header Freight record, and one Tax record. The transaction type for records of an invoice is INV.
The table below shows how the four invoice records are represented in the interface table. There are two segments enabled for the Line Transaction Flexfield OM (Order Management) context. The combination of context plus the two segments is unique for each record. Because the invoice is against an existing commitment, the Reference_line_id (Reference ID) column of the two Line records is populated with the unique identifier (customer_trx_line_id) of the commitment:
In this table, Line TF means Line Transaction Flexfield, Link-To TF means Link-To Transaction Flexfield, and Ref TF means Reference Transaction Flexfield. Also, Cont. means Context, Seg. means Segment, and Ref means Reference.
| Line Type | Line TF Cont. | Line TF Seg. 1 | Line TF Seg. 2 | Link-To TF Cont. | Link-To TF Seg. 1 | Link-To TF Seg. 2 | Ref TF Cont. | Ref TF Seg. 1 | Ref TF Seg. 2 | Ref ID |
|---|---|---|---|---|---|---|---|---|---|---|
| Line | OM | A | 1 | C1 | ||||||
| Line | OM | A | 2 | C1 | ||||||
| Freight | OM | A | T1 | |||||||
| Tax | OM | A | 3 | OM | A | 1 |
Note: You can also link the invoice to the commitment using the Reference Transaction Flexfield.
Note: Records with different contexts can be grouped together into one invoice. See Using Grouping Rules to Create Transactions.
The Tax record is linked to the first line record by the Link-To Transaction Flexfield. Since the Freight record is at the header level, it is not linked to any line record.
Now consider a credit memo that credits the Freight and the first Line of the previous invoice. The transaction type for credit memos is CM. The table below shows how the Reference Transaction Flexfield is used to link the credit memo to the invoice.
In this table, Line TF means Line Transaction Flexfield, Link-To TF means Link-To Transaction Flexfield, and Ref TF means Reference Transaction Flexfield. Also, Cont. means Context, Seg. means Segment, and Ref means Reference.
| Line Type | Line TF Cont. | Line TF Seg. 1 | Line TF Seg. 2 | Link-To TF Cont. | Link-To TF Seg. 1 | Link-To TF Seg. 2 | Ref TF Cont. | Ref TF Seg. 1 | Ref TF Seg. 2 | Ref ID |
|---|---|---|---|---|---|---|---|---|---|---|
| Freight | OM | A | T2 | OM | A | T1 | ||||
| Line | OM | A | T3 | OM | A | 1 |
Note: You can also link the credit memo to the invoice using the reference_line_id (Reference ID column).
AutoInvoice assumes that all records with the transaction type CM are on-account credits, as long as there are no values in the Reference Transaction Flexfield or the reference_line_id (Reference ID column). The table below shows how an on-account credit is represented in the Line Transaction Flexfield:
In this table, Line TF means Line Transaction Flexfield, Link-To TF means Link-To Transaction Flexfield, and Ref TF means Reference Transaction Flexfield. Also, Cont. means Context, Seg. means Segment, and Ref means Reference.
| Line Type | Line TF Cont. | Line TF Seg. 1 | Line TF Seg. 2 | Link-To TF Cont. | Link-To TF Seg. 1 | Link-To TF Seg. 2 | Ref TF Cont. | Ref TF Seg. 1 | Ref TF Seg. 2 | Ref ID |
|---|---|---|---|---|---|---|---|---|---|---|
| Line | OM | B | 1 |
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 non-unique, concatenated indexes on the tables and columns that you use for your Transaction Flexfield header and line information. The tables and columns are described in this table:
| Table | Columns |
|---|---|
| RA_CUSTOMER_TRX_LINES_ALL | interface_line_attribute1-15 |
| RA_CUSTOMER_TRX_ALL | interface_header_ attribute1-15 |
| RA_INTERFACE_LINES_ALL | interface_line_attribute1-15 |
| RA_INTERFACE_DISTRIBUTIONS_ALL | interface_line_attribute1-15 |
| RA_INTERFACE_SALESCREDITS_ALL | interface_line_attribute1-15 |
To determine which indexes you might need to create, 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_ALL table.
You should then create non-unique, concatenated indexes for the same interface line attribute columns in the RA_CUSTOMER_TRX_LINES_ALL and RA_INTERFACE_LINES_ALL tables and for the same interface header attribute columns in the RA_CUSTOMER_TRX_ALL table.
Next, if you are importing sales credit and accounting information, then create indexes for the same interface line attribute columns in the the RA_INTERFACE_SALESCREDITS_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL tables. Create these indexes only if you are using these tables to import sales credit and accounting information.
For example, you have set up a Transaction Flexfield context that uses INTERFACE_LINE_ATTRIBUTE1-3. In addition, you are populating sales credits in the RA_INTERFACE_SALESCREDITS_ALL table.
For best performance, you should create indexes for these tables:
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
The indexes that you create should reference the three enabled segments. For example, an index that you create for the RA_CUSTOMER_TRX_LINES_ALL table might look like this:
CREATE UNIQUE INDEX index_name ON RA_CUSTOMER_TRX_LINES_ALL (INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3);
Suggestion: Including the context column in your indexes is optional. However, if you use multiple active contexts (three or more), then you should include the context column as the first column in your indexes to improve performance.
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.
The table below shows a Line Transaction Flexfield with three contexts. Context1 has two attribute columns, Context2 has three attribute columns, and Context3 has two attribute columns. Context1 and Context2 share two attribute columns:
| Flexfield Context | Attribute Columns assigned to Enabled Segments |
|---|---|
| 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 |
Define the combination of indexes that best meets your needs. In the example above, you can create three indexes per table, one for each context, or create just two indexes: one for Context3 and another for Context1. In the latter case, Context2 would use the same index as Context1, because Context1 and Context2 have the same first two attribute columns.
In other words, if you are using the same, or similar, attribute columns for two or more contexts, then you can optionally create a single index instead of creating an index for each context.
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];