At this point we have completed the bare minimum setup required to enable us to insert data into the interface table and have it processed by AutoInvoice to create a transaction in the core AR tables.
The following section gives you sample scripts to populate the table for various scenarios.
Note:
For a detailed discussion of the fields in RA_INTERFACE_LINES_ALL, please review Note 1195997.1, Description and Usage of Fields in RA_INTERFACE_LINES Table.
a. Insert statement for an INVOICE
Page 25 of 55
quantity, unit_selling_price,
term_id, taxable_flag, amount_includes_tax_flag, set_of_books_id, org_id)
VALUES
('TIP', 'TIP SAMPLE INVOICE 1', 'TIP SAMPLE INVOICE 1', 1000.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'TIP SAMPLE INVOICE 1 - ITEM #1', '31-JAN-2010', 'LINE', 11145, 117751,
10, 100.00, 1514, 'Y', 'N', 1, 204);
COMMIT;
For some fields, we pass constant values and for other fields we need to provide values that tie in with the setup we created in Tasks 1 - 14 above. The following section explains how to determine the ID values created during the setup.
• INTERFACE_LINE_CONTEXT - identifies the Transaction Flexfield used by the interface data; this helps AutoInvoice understand what data is being passed in the interface table's fields. The value here ties in with the setup created in Task 2, where we created the Line Transaction Flexfield 'TIP'.
Use the value = 'TIP'
• INTERFACE_LINE_ATTRIBUTE1 and INTERFACE_LINE_ATTRIBUTE2 - the combination of values in these two fields uniquely identify the transaction you are creating.
Use the values = 'TIP SAMPLE INVOICE 1' and 'TIP SAMPLE INVOICE 1'
• AMOUNT - this field contains the value of your transaction; pass in a constant. This value should be the product of the numbers you pass in for QUANTITY * UNIT_SELLING_PRICE.
Use the value = 1000.00
• BATCH_SOURCE_NAME - the value you specify here identifies the transaction batch source to be used by AutoInvoice when interfacing this transaction. In Task 9, we created the Batch Source 'TIP BATCH SOURCE'.
Use the value = 'TIP BATCH SOURCE'
• CONVERSION_RATE - For non-functional currency transactions, this field would contain the exchange rate value. For our test case, we are creating a transaction in the functional currency = USD, so the rate is 1.
Use the value = 1
• CONVERSION_TYPE - Specify the conversion rate type, for our test case we will use 'User'.
Use the value = 'User'
• CURRENCY_CODE - Indicate the currency code of the transaction, for our test case we will use 'USD'.
Use the value = 'USD'
• CUST_TRX_TYPE_ID - identifies the ID associated to the transaction type we created in Task 4where we defined 'TIP INVOICE'.
To determine the ID associated to this transaction type, run the following:
Please note that the ID may be different in your instance.
select cust_trx_type_id from ra_cust_trx_types_all where name = 'TIP INVOICE';
Page 26 of 55
• GL_DATE - the value you specify here will be used by AutoInvoice as the GL_DATE of your transaction. Typically the period in which this GL_DATE value falls should be an open period.
Use the value = '31-JAN-2010'
• LINE_TYPE - indicates the type of line this interface data defines.
Use the value = 'LINE'
• ORIG_SYSTEM_BILL_ADDRESS_ID and ORIG_SYSTEM_BILL_CUSTOMER_ID - identifies the Address ID and Customer ID associated to the invoice.
To determine the ID values to use, run the following:
Please note that the ID may be different in your instance.
select c.cust_acct_site_id orig_system_bill_address_id, b.cust_account_id orig_system_bill_customer_id from hz_parties a,
hz_cust_accounts b, hz_cust_acct_sites_all c, hz_cust_site_uses_all d
where a.party_name = 'TIP CUSTOMER 1' and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id and c.cust_acct_site_id = d.cust_acct_site_id and d.site_use_code = 'BILL_TO';
Use the values = 11145 and 117751
• QUANTITY - specify the number of items.
Use the value = 10
• UNIT_SELLING_PRICE - specify the price of each item.
Use the value = 100.00
• TERM_ID - identifies the term ID associated with the setup created in Task 8.
To determine the ID value to use, run the following:
Please note that the ID may be different in your instance.
select term_id from ra_terms
where name = 'TIP TERM';
Use the value = 1514
• TAXABLE_FLAG - indicates whether or not the transaction is taxable.
Use the value = 'Y'
• AMOUNT_INCLUDES_TAX_FLAG - indicates whether or not the amount is inclusive of taxes.
Page 27 of 55
select set_of_books_id from ar_system_parameters_all where org_id = &org_id;
Use the value = 1
To find your ORG_ID run the script below:
Select organization_id, name From hr_organization_units;
• ORG_ID - indicates the ID of the Operating Unit against which this transaction is created.
Use the value = 204
If you do not know your ORG_ID or SET_OF_BOOKS_ID value, you can use General Setup diagnostics (e.g. in R 12.0.6 this is Note 732193.1). This will show the value of ORG_ID beside the Operating Unit Name, and SET_OF_BOOKS_ID beside the Ledger Name.
b. Insert statement for an APPLIED CREDIT MEMO
This following script will create a Credit Memo against the Invoice created above. The text highlighted in bold are the changes from the insert script for the Invoice.
OPTION 1:
If the Invoice you are crediting was also created via AutoInvoice, then you can populate REFERENCE_LINE_CONTEXT and REFERENCE_LINE_ATTRIBUTE* fields with values that would uniquely identify the invoice. Otherwise, please review Option 2 below.
INSERT INTO ra_interface_lines_all
('TIP', 'TIP SAMPLE CM 1', 'TIP SAMPLE CM 1',
'TIP', 'TIP SAMPLE INVOICE 1', 'TIP SAMPLE INVOICE 1', -10.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3628,
'TIP SAMPLE CM 1 - ITEM #1', '31-JAN-2010', 'LINE', 11145, 117751,
-1, 10.00, null, 'Y', 'N', 1, 204);
Page 28 of 55
• REFERENCE_LINE_CONTEXT
• REFERENCE_LINE_ATTRIBUTE1
• REFERENCE_LINE_ATTRIBUTE2
These fields associate this credit memo to an invoice, the values in these fields need to match the INTERFACE_LINE* fields of the invoice, because this is how you are telling Autoinvoice which Invoice you want to apply this Credit memo to.
• AMOUNT - this field contains the value of your credit memo; pass in a constant. This value should be the product of the numbers you pass in for QUANTITY * UNIT_SELLING_PRICE.
Use the value = -10.00
• CUST_TRX_TYPE_ID - identifies the ID associated to the transaction type we created in Task 4where we defined TIP CM To determine the ID associated to this transaction type, run the following:
Please note that the ID may be different in your instance.
select cust_trx_type_id from ra_cust_trx_types_all where name = 'TIP CM';
Use the value = 3628
• QUANTITY - specify the number of items.
Use the value = -1
• UNIT_SELLING_PRICE - specify the price of each item.
Use the value = 10.00
• TERM_ID
It is important to understand that a Credit Memo does not have a payment term, and this field should be left NULL
OPTION 2:
If the Invoice you are crediting was not created via AutoInvoice, then it would not have values in INTERFACE_LINE_CONTEXT + INTERFACE_LINE_ATTRIBUTE* fields, in this case you cannot use these values to identify the invoice you want this credit memo to credit. Instead you can use REFERENCE_LINE_ID.
INSERT INTO ra_interface_lines_all
('TIP', 'TIP SAMPLE CM 2', 'TIP SAMPLE CM 2', 82141,
-10.00, 'TIP BATCH SOURCE', 1,
Page 29 of 55
The following section explains how to determine the ID values created during the setup. Many of the fields are the same as the insert statement for Option 1 above, we will focus on the difference.
• REFERENCE_LINE_ID - is the CUSTOMER_TRX_LINE_ID of the Invoice line that you wish to apply the credit memo to. To find the right value, you can use the values in the Line Transaction Flexfield. As mentioned earlier the combination of values in the Context and Attributes fields is unique, hence you can find the exact line you wish to credit by using these values in the where condition.
Please note that the Id may be different in your instance.
select customer_trx_line_id from ra_customer_trx_lines_all where interface_line_context = 'TIP',
and interface_line_attribute1 = 'TIP SAMPLE INVOICE 1' and interface_line_attribute2 = 'TIP SAMPLE INVOICE 1';
Use the value = 821481
c. Insert statement for an ON-ACCOUNT CREDIT MEMO The following script will create an On-Account credit memo.
INSERT INTO ra_interface_lines_all
('TIP', 'TIP SAMPLE ON-ACCT CM 1', 'TIP SAMPLE ON-ACCT CM 1', -1000.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3628,
'TIP SAMPLE ON-ACCT CM 1 - ITEM #1', '31-JAN-2010', 'LINE', 11145, 117751,
-10, 100.00, null, 'Y', 'N', 1, 204);
The following section explains how to determine the ID values created during the setup. Many of the fields are the same as for the insert for Invoice above, we will focus on the differences:
• INTERFACE_LINE_ATTRIBUTE1 and INTERFACE_LINE_ATTRIBUTE2 - the combination of values in these two fields uniquely identify the transaction you are creating.
Use the values = 'TIP SAMPLE ON-ACCT CM 1' and 'TIP SAMPLE ON-ACCT CM 1'
• AMOUNT - this field contains the value of your credit memo; pass in a constant. This value should be the product of the numbers you pass in for QUANTITY * UNIT_SELLING_PRICE.
Use the value = -1000.00
• CUST_TRX_TYPE_ID - identifies the ID associated to the transaction type we created in Task 4 where defined TIP CM To determine the ID associated to this transaction type, run the following:
Page 30 of 55
Use the value = 3628
• QUANTITY - specify the number of items.
Use the value = -10
• UNIT_SELLING_PRICE - specify the price of each item.
Use the value = 100.00
• TERM_ID
It is important to understand that a Credit Memo does not have a payment term, and this field should be left NULL
d. Insert statement for an INVOICE and CREDIT MEMO WITH MANUAL TAX LINES
For a sample script on how to pass interface data for an Invoice including manual tax Lines with the intention of bypassing the E-Business Tax engine, please review Note 731149.1, R12 How To Bring In (and Troubleshoot) Manual Tax Lines Through Autoinvoice and E-Business Tax (EBTax).
e. Insert statement for an INVOICE WITH RULES
To create invoices with rules, aside from the setup steps detailed above, an additional setup step is required to define the Accounting Rule.
Responsibility: Receivables Manager
Navigation: Setup > Transactions > Accounting Rules
Page 31 of 55
The following script will create an invoice using the TIP RULE defined above.
('TIP', 'TIP RULE INVOICE 1', 'TIP RULE INVOICE SAMPLE', 1000.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'TIP DESCRIPTION 1 - ITEM #1', '10-AUG-2010', 'LINE', 11145, 117751,
10, 100.00, 1514, 'Y', 'N', 1, 204,
-2, 12086, null);
For some fields, we pass constant values and for other fields we need to provide values that tie in with the setup we created in Tasks 1 - 14 above. The following section explains how to determine the ID values created during the setup.
• INTERFACE_LINE_CONTEXT - identifies the Transaction Flexfield used by the interface data; this helps AutoInvoice understand what data is being passed in the interface table's fields. The value here ties in with the setup created in Task 2, where we created the Line Transaction Flexfield 'TIP'.
Use the value = 'TIP'
• INTERFACE_LINE_ATTRIBUTE1 and INTERFACE_LINE_ATTRIBUTE2 - the combination of values in these two fields uniquely identify the transaction you are creating.
Use the values = 'TIP RULE INVOICE 1' and 'TIP RULE INVOICE SAMPLE'
• AMOUNT - this field contains the value of your transaction; pass in a constant. This value should be the product of the numbers you pass in for QUANTITY * UNIT_SELLING_PRICE.
Use the value = 1000.00
• BATCH_SOURCE_NAME - the value you specify here identifies the transaction batch source to be used by AutoInvoice when interfacing this transaction. In Task 9, we created the Batch Source 'TIP BATCH SOURCE'.
Use the value = 'TIP BATCH SOURCE'
• CONVERSION_RATE - For non-functional currency transactions, this field would contain the exchange rate value. For our test case, we are creating a transaction in the functional currency = USD, so the rate is 1.
Use the value = 1
• CONVERSION_TYPE - Specify the conversion rate type, for our test case we will use 'User'.
Use the value = 'User'
• CURRENCY_CODE - Indicate the currency code of the transaction, for our test case we will use 'USD'.
Page 32 of 55
select cust_trx_type_id from ra_cust_trx_types_all where name = 'TIP INVOICE';
Use the value = 3627
• DESCRIPTION - enter a line item description.
Use the value = 'TIP DESCRIPTION 1 - ITEM #1'
• GL_DATE - the value you specify here will be used by AutoInvoice as the GL_DATE of your transaction. Typically the period in which this GL_DATE value falls should be an open period.
Use the value = '10_AUG-2010'
• LINE_TYPE - indicates the type of line this interface data defines.
Use the value = 'LINE'
• ORIG_SYSTEM_BILL_ADDRESS_ID and ORIG_SYSTEM_BILL_CUSTOMER_ID - identifies the Address ID and Customer ID associated to the invoice.
To determine the ID values to use, run the following:
Please note that the ID may be different in your instance.
select c.cust_acct_site_id orig_system_bill_address_id, b.cust_account_id orig_system_bill_customer_id from hz_parties a,
hz_cust_accounts b, hz_cust_acct_sites_all c, hz_cust_site_uses_all d
where a.party_name = 'TIP CUSTOMER 1' and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id and c.cust_acct_site_id = d.cust_acct_site_id and d.site_use_code = 'BILL_TO';
Use the values = 11145 and 117751
• QUANTITY - specify the number of items.
Use the value = 10
• UNIT_SELLING_PRICE - specify the price of each item.
Use the value = 100.00
• TERM_ID - identifies the term ID associated with the setup created in Task 8.
To determine the ID value to use, run the following:
Please note that the ID may be different in your instance.
select term_id
Page 33 of 55
Use the value = 'Y'
• AMOUNT_INCLUDES_TAX_FLAG - indicates whether or not the amount is inclusive of taxes.
Use the value = 'N'
• SET_OF_BOOKS_ID - identifies the Set of Books ID associated to the Operating Unit you are processing your transaction in.
The set of books id is associated to your Operating Unit. If you know your operating unit ID, you can run the following:
Please note that the ORG_ID in your instance may be different.
select set_of_books_id from ar_system_parameters_all where org_id = &org_id;
Use the value = 1
• ORG_ID - indicates the ID of the Operating Unit against which this transaction is created.
Use the value = 204
If you do not know your ORG_ID or SET_OF_BOOKS_ID value, you can use General Setup diagnostics (e.g. in R 12.0.6 this is Note 732193.1). This will show the value of ORG_ID beside the Operating Unit Name, and SET_OF_BOOKS_ID beside the Ledger Name.
• INVOICING_RULE_ID - indicates manner in which you want the Receivable recognized. There are only 2 values available: -2 (In Advance) and -3 (In Arrears). For more information please review Note 1116934.1
Use the value = -2
• ACCOUNTING_RULE_ID - indicates the manner in which you want the Revenue to be recognized. This ties in with the setup we created above for 'TIP RULE'. For more information please review Note 1116934.1
To determine the ID value to use, run the following:
Please note that the ID may be different in your instance.
select rule_id from ra_rules
where name = 'TIP RULE';
• ACCOUNTING_RULE_DURATION - when the rule you use has variable duration, you need to provide a value in this field. However, in our case, the rule has a fixed duration of 3 periods, so we leave this null.
Use the value = null
f. Insert statement for an INVOICE WITH GL DISTRIBUTIONS
Notes:
• If you have setup AutoAccounting and would like to use the GL accounts derived by your setup, you do not have to pass in data in RA_INTERFACE_DISTRIBUTIONS_ALL. The GL distributions and GL
Page 34 of 55
In the following script, we interface an Invoice and pass in distributions for the Revenue and Receivable account. The setup in this instance will create Rounding rows, and since we do not explicitly pass in that distribution, it will be created using the AutoAccounting setup.
For more on this refer to Note 1228525.1How to Import and Troubleshoot Distributions in AutoInvoice Using RA_INTERFACE_DISTRIBUTIONS_ALL
-- create the "parent" record in RA_INTERFACE_LINES_ALL INSERT INTO ra_interface_lines_all
('TIP', 'TIP DIST INVOICE 1', 'TIP DIST INVOICE SAMPLE', 1000.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'TIP DESCRIPTION 1 - ITEM #1', '10-AUG-2010', 'LINE', 11145, 117751,
10, 100.00, 1514, 'Y', 'N', 1, 204);
-- create distribution records for only REV and REC rows INSERT INTO ra_interface_distributions_all (
account_class, amount, code_combination_id, percent,
interface_line_context, interface_line_attribute1,interface_line_attribute2, org_id) VALUES ('REC', 1000.00, 12833, 100,
'TIP', 'TIP DIST INVOICE 1', 'TIP DIST INVOICE SAMPLE', 204);
INSERT INTO ra_interface_distributions_all ( account_class, amount, code_combination_id, percent,
interface_line_context, interface_line_attribute1,interface_line_attribute2, org_id) VALUES ('REV', 1000.00, 107410, 100,
'TIP', 'TIP DIST INVOICE 1', 'TIP DIST INVOICE SAMPLE', 204);
For an explanation of the fields/values used for the insert statement, please review the first example scriptabove, the values used are similar.
The following provides an explanation of the fields/values used for the 2nd and 3rd insert statements:
• ACCOUNT_CLASS - there are 7 valid values: REV, FREIGHT, TAX, REC, UNBILL (for Arrears), UNEARN (for Advance), CHARGES You may pass values for any of these account classes.
For any required GL distribution that you do not provide in RA_INTERFACE_DISTRIBUTIONS_ALL, the code will invoke AutoAccounting.
Use the value = 'REC' and 'REV'
• AMOUNT - this field contains the value associated to the GL distribution you are creating; pass in a constant. For the REC account, this should be the sum of all the LINE, TAX and FREIGHT amounts in RA_INTERFACE_LINES_ALL for this particular transaction.
Page 35 of 55
Typically, the purpose of passing in rows in RA_INTERFACE_DISTRIBUTIONS_ALL is to bypass AutoAccounting. In our current setup, AutoAccounting would have used the code combination id values:
12835 and 158423 respectively. By using different code_combination_id values we are able to verify that the distributions we passed in are what actually got used when the Invoice was created.
• PERCENT - indicates the percentage associated to the distributions.
Use the value = 100
• INTERFACE_LINE_CONTEXT - identifies the Transaction Flexfield used by the interface data; this helps AutoInvoice understand what data is being passed in the interface table's fields. The value here ties in with the setup created in Task 2, where we created the Line Transaction Flexfield 'TIP'.
• INTERFACE_LINE_ATTRIBUTE1 and INTERFACE_LINE_ATTRIBUTE2 - the combination of values in these two fields uniquely identify the transaction you are creating.
Use the values = 'TIP DIST INVOICE 1' and 'TIP DIST INVOICE SAMPLE'
• ORG_ID - indicates the ID of the Operating Unit against which this transaction is created.
• ORG_ID - indicates the ID of the Operating Unit against which this transaction is created.