auto invoice
Post on 12-Sep-2014
96 Views
Preview:
TRANSCRIPT
Categories: Oracle Application Services, Oracle E-Business Suite R12
You have been tasked with developing an interface to AR and you’ve never used Oracle’s Auto Invoice before. What do you do? Unfortunately, at one time I was in this situation. Though I knew the concepts of the process, the real time implementation involved a lot of challenges. The following is for those who have never interfaced invoices through Auto Invoice. My goal is to provide an overall understanding of the process and pitfalls that one should be aware of.
What is Auto Invoice?
Auto Invoice is a very powerful process in Oracle EBS that provides a mechanism to import receivable transactions from external systems. Similar to other modules within EBS, Oracle provides a set of interface tables and concurrent processes to import receivable transactions into Oracle AR performs. The processes validate the transactions and import them into AR.
Pre-requisites
To successfully interfacing the external systems’ invoices to Oracle AR, the following setups/configurations are required. Failure to complete these setups will result in invoices being rejected during the validation stage of Auto Invoice
Transaction Source – The transaction source is specific to the operating unit. The transaction source defines how values in invoice lines will be interpreted and validated, such as grouping rules and customer information.
Transaction Type – The transaction type defines the accounting information for the lines and how each line will be validated and processed.
Line Transaction Flexfield – The line transaction flexfield defines how invoices will be grouped.
Grouping Rules – The grouping rules allow you to customize how credit and revenue lines should be grouped to form an invoice
Customers
Currencies
Items
Payment Terms
What are the tables utilized by Auto Invoice?
Depending upon the type of transactions that are being imported, the Auto Invoice process populates the following tables:
RA_CUSTOMER_TRX_ALL – Stores invoice information RA_CUSTOMER_TRX_LINES_ALL – Invoice line information can be found here
RA_PAYMENT_SCHEDULES_ALL – Oracle records an entry here, except for adjustments and miscellaneous cash receipt transactions
RA_RECEIVABLE_APPLICATIONS_ALL – The accounting entries for cash and credit memos are stored here
RA_INTERFACE_ERRORS_ALL – All errors encountered during the Auto Invoice process are written to this table.
Here is the list of tables that the Auto Invoice process utilizes:
RA_INTERFACE_LINES_ALL RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
Setups are done and I know how data flows through the tables. What’s next?
The interface looks simple and easy to use, but it gets tricky. The requirements for populating the interface tables depend on how the application is configure. For example, let’s the transaction source is defined as ‘My Company US Transaction Source’ for the US Operating Unit. If you use this source for a different operating unit, the invoices will not be selected for validation. Similarly, how the customer information is validated is dependent on how the transaction source is setup. If the transaction source is setup to use “ID”, then usage of internal IDs is required in the interface tables. If the transaction source is setup to use ‘Value’, then the Auto Invoice will not look for the actual value of the customer but the reference fields for the customer and the site.
I’ve populated the interface tables, now how do I run Auto Invoice?
The Auto Invoice process consists of three concurrent programs:
Auto Invoice Master program – Selects and marks invoices in the interface tables for processing based on the parameters, and calls the Auto Invoice Import program
Auto Invoice Import program – Validates the selected invoice lines and distributions and when all are validated, creates the invoices or else populates the error table
Auto Invoice Purge program – Removes processed records from the interface tables. If the system is setup to remove processed records after each run then this process is not necessary.
The Auto Invoice interface performs the following tasks:
Validates all lines
Calculates tax
Determines the GL period
GL accounts are assigned to Auto Accounting
Groups and orders invoice lines
Links credit memos, tax or freight to invoice lines, if applicable
Transfers the invoices to AR tables
After a successful completion of the Auto Invoice Interface process, the Auto Invoice Execution Results Report will list the lines that were successfully transferred and the lines that failed. The errors can be corrected either manually or via the Interface Exceptions window. Once corrected the Auto Invoice process can be resubmitted to pick up the previously rejected transactions. Even if invoices are successfully created, you should validate invoices from the front-end to ensure the fields have been properly brought over to their rightful place and the accounting entries were created as desired.
AR Invoice Interface11 Apr
AR Invoice Interface
The main three steps for AR Invoice Interface are:
1] Put the data into your staging tables.
2] Calls your package to validate the data and load into AR Interface tables (RA_INTERFACE_LINES_ALL & RA_INTERFACE_DISTRIBUTIONS_ALL).
3] Then submits a concurrent request for AutoInvoice.
If any errors occur it can be found in ra_interface_errors_all table. The concurrent program has 2 stages. First the Master program fires which intern kicks of the Import Program. Once this is completed data is inserted into the following tables.
1) ra_customer_trx_all (Invoice Header Info)
2) ra_customer_trx_lines_all (Invoice Line Level Info)
3) ra_cust_trx_line_gl_dist_all (Accounting Info. One record for each Account Type is inserted into this… ex. Receivable Revenue Tax Freight etc)
4) ar_payment_schedules_all (All Payment related info)
Validations:
Validation are generally done on the below columns.
Batch_source_name Set_of_books_id
Orig_sys_batch_name
orig_system_bill_customer_ref
orig_system_bill_address_ref
Line_Type
Currency_Code
Term_name
Transaction_type
Interface_line_attribute1-7
Account_class
Accounting Flexfields segments
1- AR Transaction Type Validation: Check if the Transaction type provided in data file is defined in AR transaction types (RA_CUST_TRX_TYPES_ALL)
2- Transaction Batch Source Validation: Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).
3- Invoice Currency Validation: Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).
4- Customer Validation: Check if the Bill to Customer Number, Ship to Customer Number, Bill to Custom Location, Ship to Customer Location provided in the data file is defined in AR Customer (RA_CUSTOMERS).
5- Primary Sales Representative Validation: Sales representative number to be hardcode to “-3” for “No Sales Credit.”
6- Term Name: Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)
7- Inventory Item Validation: Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).
8- Unit of Measurement validation: Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table
9- Invoice Tax Code Validation: Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.
10- Invoice GL Date Validation: Check if the GL Data of provided invoices is in open period.
For MOAC:
You need to add the below columns and need to do validations if your application supports MOAC.
conversion_type conversion_rate
conversion_date
Sample Code to run Autoinvoice Master Program:
view source
print ?
01 DECLARE
02 v_phase VARCHAR2(100); 03 v_dev_phase VARCHAR2(100);
04 v_status VARCHAR2(100);
05 v_dev_status VARCHAR2(100);
06 v_message VARCHAR2(100);
07 v_reqid NUMBER(15);
08 v_pid BOOLEAN;
09 v_user_id NUMBER(30);
10 v_batch_source_id NUMBER; 11 v_order NUMBER; 12 v_org_id NUMBER;
13 v_resp_id number;
14 v_resp_appl_id number; 15 v_appl_short_name fnd_application.application_short_name%TYPE;
16
17 CURSOR c1 IS
18 select fcr.responsibility_id
19 ,fr.application_id
20 from fnd_concurrent_requests fcr
21 ,fnd_responsibility fr
22 where fcr.request_id = '${4}'23 and fcr.responsibility_id = fr.responsibility_id;
24
25 CURSOR c2 IS
26 select fa.application_short_name
27 from fnd_concurrent_programs fcp, fnd_application fa
28 where fcp.concurrent_program_name = v_program_short_name 29 and fcp.application_id = fa.application_id;
30
31 CURSOR c_batch_id IS
32 SELECT 1, batch_source_id, name
33 FROM apps.ra_batch_sources_all
34 WHERE name IN (SELECT distinct a.batch_source_name
35 FROM xxfin.xxfin_ar_ol_invoices a
36 WHERE a.batch_source_name like '%DEBIT'37 AND filename = '${file1}')
38 UNION
39 SELECT 2, batch_source_id, name
40 FROM apps.ra_batch_sources_all
41 WHERE name IN (SELECT distinct a.batch_source_name
42 FROM xxfin.xxfin_ar_ol_invoices a
43 WHERE a.batch_source_name like '%CREDIT'
44 AND filename = '${file1}')
45 order by 1;
46
47 BEGIN
48 open c1; 49 fetch c1 into v_resp_id,v_resp_appl_id;
50 close c1;
51
52 open c2; 53 fetch c2 into v_appl_short_name;
54 close c2;
55
56 FOR v_batch_data IN c_batch_id LOOP
57
58 fnd_global.apps_initialize('${FCP_USERID}',v_resp_id,v_resp_appl_id);
59
60 v_reqid := fnd_request.submit_request('AR', 61 'RAXMTR',
62 NULL,
63 to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
64 FALSE,
65 '1', 66 -99, 67 v_batch_data.batch_source_id,
68 v_batch_data.name,
69 to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
70 NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL , 71 NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
72 'Y',
73 NULL);
74 commit; 75 v_pid := fnd_concurrent.wait_for_request(v_reqid,
76 3,
77 0,
78 v_phase,
79 v_status,
80 v_dev_phase, 81 v_dev_status,
82 v_message);
83 END LOOP;
84
END;
Auto Invoice interface
This interface is used to import Customer invoices, Credit memos, Debit memos and
On Account credits.
Pre-requisites:
Set of Books
Code combinations
Items
Sales representatives
Customers
Sales Tax rate
Payment Terms
Transaction Types
Freight Carriers
FOB
Batch Sources
Accounting Rules
Interface tables:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)
Base tables:
RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program:
Auto invoice master program
Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.
Validate if the amount includes tax flag.
Some important columns that need to be populated in the interface tables:
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER
top related