Manual Tax Lines Through Autoinvoice and E-Business Tax in Oracle APPS

Interface data fields required to pass a Manual Tax Line (731149.1)

The columns required to create a manual tax line for an invoice include:

Column Name	Validation
Amount	Required
Batch_source_name	Required
Conversion_type	Required
Conversion_Rate	Required if conversion type = 'USER'
Currency_code	Required
Cust_trx_type_id	Required
Description	Required
Interface_line_attribute(1..X)	Multiple lines required - # depends upon setup of trx source
Interface_line_context	Set based upon transaction setup
Line_type	TAX
Link_to_line_Attribute(1..x)	Value from line type of line that this trx is linked
Link_to_line_context	Value from line type of line that this trx is linked
Org_id	Required
Tax_code	Conditionally required if tax setup uses tax classification code to derive tax attributes
Tax_rate_code	Required:  Fields below are not required if tax_rate_code is unique across setup
tax_rate	Conditionally required - set if tax_rate_code is not unique
tax_Jurisdiction_code	Conditionally required - set if tax_rate_code is not unique
Tax_Regime_code	Conditionally required - set if tax_rate_code is not unique
Tax	Conditionally required - set if tax_rate_code is not unique
Tax_Status_code	Conditionally required - set if tax_rate_code is not unique

In addition to setting these columns on the tax line, the line type of "Line" should have the taxable flag set to "N" to avoid having additional taxes calculated.

Note:  the AR Reference Manual released for Receivables was not accurate when referencing manual tax.  It does not include all mandatory fields and has fields listed as mandatory that are not required.  Documentation bugs have been logged and this should be corrected in the next documentation patch.

3. Sample Scripts for Invoice and Credit Memo with Manual Tax Lines

The following scripts illustrate how you can pass manual tax lines for an Invoice and an Applied Credit Memo.

TIP: When building scripts to insert data into the Interface tables for AutoInvoice, it is always a good idea to first create a similar transaction manually using the Transactions form. Then you can review the data used in the various Tax-related fields and use this when you write the script to manually insert the data. This helps confirm that the data you are using has at least passed the validation in the manual form and will save you several iterations of  trial-and-error when writing the SQL script.

Invoice Script

The scripts below are to insert an Invoice with Manual Tax lines using the standard US Sales Tax. Hence it contains 1 record for LINE and 3 records for TAX (State, County, City):

-- Insert record for LINE
INSERT INTO ra_interface_lines_all
(interface_line_context, interface_line_attribute1, interface_line_attribute2,
amount, batch_source_name, conversion_rate,
conversion_type, currency_code, cust_trx_type_id,
description, gl_date, line_type,
orig_system_bill_address_id, orig_system_bill_customer_id,
quantity, unit_selling_price,
term_id, taxable_flag, amount_includes_tax_flag,
set_of_books_id, org_id)
VALUES
('TIP', 'TIP TAX INVOICE 2', 'INVOICE LINE',
1000.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'TIP TAX INVOICE 2 - ITEM #1', '10-AUG-2010', 'LINE',
11145, 117751,
10, 100.00,
1514, 'N', 'N',
1, 204);

-- Insert Record for State Tax
INSERT INTO ra_interface_lines_all
(interface_line_context, interface_line_attribute1, interface_line_attribute2,
LINK_TO_LINE_CONTEXT, LINK_TO_LINE_ATTRIBUTE1, LINK_TO_LINE_ATTRIBUTE2,
amount, batch_source_name, conversion_rate,
conversion_type, currency_code, cust_trx_type_id,
description, gl_date, line_type,
orig_system_bill_address_id, orig_system_bill_customer_id,
quantity, unit_selling_price,
term_id, taxable_flag, amount_includes_tax_flag,
set_of_books_id, org_id,
tax_regime_code, tax, tax_status_code,
tax_rate_code, tax_jurisdiction_code, tax_rate)
VALUES
('TIP', 'TIP TAX INVOICE 2', 'TAX LINE ST',
'TIP', 'TIP TAX INVOICE 2', 'INVOICE LINE',
60.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'State Tax', '10-AUG-2010', 'TAX',
11145, 117751,
null, null,
1514, null, null,
1, 204,
'US-SALES-TAX-101', 'STATE', 'STANDARD',
'STANDARD', 'ST-CA-11651', 6.0);

-- Insert Record for County Tax
INSERT INTO ra_interface_lines_all
(interface_line_context, interface_line_attribute1, interface_line_attribute2,
LINK_TO_LINE_CONTEXT, LINK_TO_LINE_ATTRIBUTE1, LINK_TO_LINE_ATTRIBUTE2,
amount, batch_source_name, conversion_rate,
conversion_type, currency_code, cust_trx_type_id,
description, gl_date, line_type,
orig_system_bill_address_id, orig_system_bill_customer_id,
quantity, unit_selling_price,
term_id, taxable_flag, amount_includes_tax_flag,
set_of_books_id, org_id,
tax_regime_code, tax, tax_status_code,
tax_rate_code, tax_jurisdiction_code, tax_rate)
VALUES
('TIP', 'TIP TAX INVOICE 2', 'TAX LINE CO',
'TIP', 'TIP TAX INVOICE 2', 'INVOICE LINE',
20.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'County Tax', '10-AUG-2010', 'TAX',
11145, 117751,
null, null,
1514, null, null,
1, 204,
'US-SALES-TAX-101', 'COUNTY', 'STANDARD',
'STANDARD','CO-CA-SAN FRANCISC-11760', 2.0);

-- Insert Record for City Tax
INSERT INTO ra_interface_lines_all
(interface_line_context, interface_line_attribute1, interface_line_attribute2,
LINK_TO_LINE_CONTEXT, LINK_TO_LINE_ATTRIBUTE1, LINK_TO_LINE_ATTRIBUTE2,
amount, batch_source_name, conversion_rate,
conversion_type, currency_code, cust_trx_type_id,
description, gl_date, line_type,
orig_system_bill_address_id, orig_system_bill_customer_id,
quantity, unit_selling_price,
term_id, taxable_flag, amount_includes_tax_flag,
set_of_books_id, org_id,
tax_regime_code, tax, tax_status_code,
tax_rate_code, tax_jurisdiction_code, tax_rate)
VALUES
('TIP', 'TIP TAX INVOICE 2', 'TAX LINE CI',
'TIP', 'TIP TAX INVOICE 2', 'INVOICE LINE',
30.00, 'TIP BATCH SOURCE', 1,
'User', 'USD', 3627,
'City Tax', '10-AUG-2010', 'TAX',
11145, 117751,
null, null,
1514, null, null,
1, 204,
'US-SALES-TAX-101', 'CITY', 'STANDARD',
'STANDARD','CI-CA-SAN FRANCISC-11761', 3.0);

COMMIT;