Download - Inter Company Invoicing
![Page 1: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/1.jpg)
Oracle E-Business Suite:Coping with EBS Intercompany Invoicing
![Page 2: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/2.jpg)
Objectives
• Explore intercompany (I/C) invoicing topics and issues not addressed in the Inventory User’s Guide
• Learn tips for coping with I/C invoicing in areas of:– Reconciliation– Scheduling– G/L Dating– Error Detection– Valuation
2
![Page 3: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/3.jpg)
Presentation Scope
• In Scope– Shipments to fulfill customer sales orders
• Loosely referred to as “internal drop shipments”– Simple pricing of I/C invoices from price lists– EBS 11i functionality; applicable to Release 12
• Out of Scope– Step-by-step configuration– Internal Sales Order / Internal Req-specific scenarios– Global Purchase Agreement-specific scenarios– “External” Drop Shipments (EBS SO spawns a PO)– Advanced Pricing to value intercompany invoices
3
![Page 4: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/4.jpg)
Intercompany Invoicing Overview
• Standard functionality in EBS 11i and R12• Produces intercompany invoices for “internal” drop
shipments when SOs are sourced across operating units (OUs):– SO entered in a selling (OU)– Shipment for SO is sourced from an org linked to a
different OU, the shipping OU• I/C Receivables (A/R) invoice in the shipping OU
– Bill an internal customer, the selling OU• I/C Payables (A/P) invoice in the selling OU
– Pay an internal supplier, the shipping OU
4
![Page 5: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/5.jpg)
Classic Intercompany Invoicing Flow
5
Note: Child not created until Parent record has been Transacted and Costed
![Page 6: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/6.jpg)
Typical Accounting for Classic I/C Invoicing
Step Event Operating Unit
Typical Accounting Valued At
1 Order Entry / Booking: Japan OU Customer SO sourced from U.S. Inventory
Japan Sales
No accounting transaction n/a
2 Product Shipment from U.S. Inventory directly to Japan Customer: Sales Order Issue / Issue From Stores
U.S. MFG Dr Cost of Goods Sold (COGS) Cr Finished Goods [Staging] InventoryNot an intercompany transaction
Standard Cost
3 Generate Customer Invoice via AR AutoInvoice
Japan Sales
Dr Accounts Receivable Cr Sales RevenueNot an intercompany transaction
Sales Order Price
4 Create Intercompany A/R Invoice U.S. MFG Dr Intercompany Receivables Cr Intercompany Sales
Transfer Price
5 Create Intercompany A/P InvoiceUse Account Generator WF to code the debit to Intercompany COGS if account default is inadequate
Japan Sales
Dr [Intercompany] COGS Cr Intercompany Payables
Transfer Price
6 Receive Customer Payment Japan Sales
Dr Cash Cr Accounts ReceivableNot an intercompany transaction
Sales Order Price
7a Record Payment Against Intercompany Payable
Japan Sales
Dr Intercompany Payables Cr Cash [or I/C Netting acct.]
Transfer Price
7b Receive Payment for Intercompany Receivable [or record I/C netting]
U.S. MFG Dr Cash [or I/C Netting acct.] Cr Intercompany Receivables
Transfer Price
6
![Page 7: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/7.jpg)
Intercompany Relation
7
![Page 8: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/8.jpg)
Reconciliation
• How do you ensure the completeness of all intercompany Payables invoices?– Each I/C Receivable should have matching I/C Payable
• No reconciliation report provided in Oracle EBS
8
![Page 9: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/9.jpg)
Reconciliation: Discoverer Worksheet
9
![Page 10: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/10.jpg)
Tip # 1: Discoverer Worksheet
• Create a custom Discoverer worksheet for every existing intercompany relation
• Use a custom SQL script to query I/C A/R invoices and any matching I/C A/P invoices– Sample script provided in white paper on My
Oracle Support site or upon e-mail request
10
![Page 11: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/11.jpg)
Intercompany A/P Invoice Numbering
• Prior to release 11.5.9, the I/C A/P invoice # equaled the I/C A/R invoice #
• Starting in 11.5.9, the A/P invoice # has a suffix (dash + shipping OU’s org ID) to ensure uniqueness– Example: I/C A/P invoice # 47391-32 created to match
I/C A/R invoice # 47391• The SQL script provided in the white paper can
accommodate matching to A/P invoice #s with or without the suffix
11
![Page 12: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/12.jpg)
I/C Invoicing Concurrent Programs
• Four separate concurrent programs must run to produce I/C A/R and A/P invoices:1) “Create Intercompany AR Invoices” in the INV module
2) “Autoinvoice Master Program” for Intercompany source in the Receivables module
3) “Create Intercompany AP Invoices” in the INV module
4) “Expense Report Import” for Intercompany source in the Payables module
• The 2nd, 3rd & 4th programs are dependent on predecessor
12
![Page 13: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/13.jpg)
Tip # 2: Daily Scheduling
• Don’t wait until month-end to generate a whole month’s worth of intercompany invoices!
• Schedule the four concurrent programs to run daily– Reason: Errors can be detected & resolved more
quickly through frequent scheduling
13
![Page 14: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/14.jpg)
Example of Daily Scheduling List
14
![Page 15: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/15.jpg)
Tip # 3: Meaningful Description
• Use meaningful Header Description values when submitting the “Create Intercompany AR Invoices” or “Create Intercompany AP Invoices” programs– Reason: Parameter value becomes the invoice
description
15
![Page 16: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/16.jpg)
Header Description Parameter
16
![Page 17: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/17.jpg)
Description on Imported Invoice
17
![Page 18: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/18.jpg)
Error Detection
• Request status does not necessarily indicate whether errors occurred with some records– For 3 of the 4 concurrent programs (except “Create
Intercompany AR Invoices”), the request status will be Normal even if the output or log file indicates some records were rejected
• Do not just monitor at month-end!– Reason: Daily scheduling won’t minimize issues at
month-end if monitoring is only done at month-end– People should be responsible for periodic
monitoring errors with these scheduled requests
18
![Page 19: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/19.jpg)
Typical Detection Methods
• “Create Intercompany AR Invoices”– Warning request status indicates transaction errors– User must review the log file to identify error messages
• “Autoinvoice Master Program”– Request status will be Normal even if records error out– Review the output file of spawned Autoinvoice Import
Program request for rejected record count– Error records can be monitored by querying the
RA_INTERFACE_ERRORS_ALL table• I/C autoinvoice errors are not so common
19
![Page 20: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/20.jpg)
Log File from Warning Request
20
![Page 21: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/21.jpg)
Typical Detection Methods [continued]
• “Create Intercompany AP Invoices” – Request status will be Normal even if records error out– User must review the log file to identify error
messages• Contains separate counts for records selected,
processed, and rejected
• “Expense Report Import”– Request status will be Normal even if records error out– User must review the output file to detect errors in the
Exceptions Report section
21
![Page 22: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/22.jpg)
Tip # 4: Alert for Warning Requests
• Create an Oracle Alert to detect “Create Intercompany AR Invoices” requests that ended with a Warning status– Sample alert script provided in white paper available
upon e-mail request• Common reason for Warning: Missing transfer price
– For example, log file will indicate “APP-INV-05706: Warning: Cannot retrieve transfer price for the item LASER123. Warning: Cannot create invoice line”
22
![Page 23: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/23.jpg)
Example of Oracle Alert E-Mail
23
![Page 24: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/24.jpg)
Scheduling Issue: Unique Batch
• Issue in EBS prior to release 11.5.8• Payables invoice batch names must be unique
within an operating unit if batch control is enabled• Bug 2366334: If an “Expense Report Import” request
is scheduled to run repeatedly, each submission will have the same batch name parameter value (won’t be unique)– A/P batch from 1st request run will be created– Subsequent runs will fail, because they will attempt to
create the same batch name• Resolved in release 11.5.8 and patch 3114322;
request concatenates the batch_id to batch name
24
![Page 25: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/25.jpg)
G/L Dating Issue: Payables Options
• Setting GL Date Basis to System Date is appropriate for trade payables but not for intercompany payables– Would cause some I/C A/P invoices to have G/L dates
different from their corresponding I/C A/R invoices– Could push an I/C A/P invoice’s G/L date into another
G/L period• If GL Date Basis for I/C A/P invoices were set to
Invoice Date, then their G/L Date would always match the G/L Date of their I/C A/R Invoice
I/C A/P Invoice’s G/L Date =
I/C A/P Invoice’s Invoice Date =
I/C A/R Invoice’s Invoice Date =
I/C A/R Invoice’s G/L Date
25
![Page 26: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/26.jpg)
G/L Date Basis Option in Selling OU
26
![Page 27: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/27.jpg)
No Solution Offered in EBS
• Neither EBS 11i or R12 offer a way to vary the GL Date Basis of A/P invoices based on source type [Intercompany vs. non-Intercompany]
• Issue raised in numerous Oracle Support bugs– Bug 2359320 – INCIAP INTERCOMPANY INVOICING
PICKING UP WRONG DATE– Bug 3104204 – AP INTERCOMPANY INVOICES
HAVE A DIFFERENT GL DATE FROM AR INVOICES
27
![Page 28: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/28.jpg)
Unofficial Tip: Custom Triggers
• Keep the GL Date Basis set to System Date in the selling OU if desired for trade payables
• Add custom triggers to two Payables tables to override the A/P invoice’s G/L date [only for intercompany invoices!], inserting its invoice date value:– AP_INVOICES_ALL– AP_INVOICE_DISTRIBUTIONS_ALL
• Custom triggers guarantee that the G/L dates of I/C A/P and A/R invoices match
• Add triggers at your own risk!
28
![Page 29: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/29.jpg)
Example of Custom Trigger for One Table
29
![Page 30: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/30.jpg)
Payments
• Oracle EBS 11i offers no automation:– For corporate netting of I/C A/R and A/P invoices– To apply cash collections to I/C receivables when a
payment is recorded against I/C A/P invoices• One intercompany cash collection could be for
hundreds or thousands of I/C A/R invoices depending on shipping volumes
30
![Page 31: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/31.jpg)
Valuation
• Two methods for valuing intercompany invoices:– Static price list in Order Mgmt. module– Advanced Pricing engine rules for more complexity
• Static price list for an internal customer is defined within the customer’s bill-to location
31
![Page 32: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/32.jpg)
Price List Chosen for Customer Site
32
![Page 33: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/33.jpg)
Transfer Price List
33
![Page 34: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/34.jpg)
Basis for Selecting a Transfer Price
• The “Create Intercompany AR Invoices” request selects a transfer price based on which price is effective as of the system date– Request run system date = I/C A/R inv. creation date
• The G/L date of the I/C invoice has no bearing on the transfer price selection
34
![Page 35: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/35.jpg)
Example of Transfer Price Selection
• I/C A/R invoice for item LASER123– G/L date: 01-APR-2007– Invoice date: 01-APR-2007
• If this I/C A/R invoice were created on:– 01-APR-2007, it would be valued at the $1,320 price– 05-APR-2007, it would be valued at the $1,100 price
• Issue: Some companies want the invoice to be priced at the new price ($1,100) based on the G/L date regardless of the invoice creation date
35
![Page 36: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/36.jpg)
Potential Valuation Issue
• Dilemma: How do you ensure that intercompany invoices with G/L date in:– March 2007 are valued at the old transfer prices?– April 2007 are be valued at the new prices?
36
![Page 37: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/37.jpg)
Tip # 5: Control the Valuation Cutoff
• Use the Cost Cutoff Date (CCD) functionality in EBS Inventory to indirectly control the I/C invoice valuation cutoff– Introduced in EBS release 11.5.7– Controlled by one simple Inventory parameter date– Defers the costing of any MTL transactions in the org
with G/L date on or after the cost cutoff date value
37
![Page 38: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/38.jpg)
Inventory Org Parameter: Cost Cutoff Date
38
![Page 39: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/39.jpg)
CCD Impact on I/C Invoice Valuation
• Set CCD to 01-APR-2007 to defer the costing of April-dated shipments until perhaps April 6th
• I/C invoices for April-dated shipments will not be created & valued so long as their ‘Sales Order Issue’ shipment transactions remain uncosted
• The cost cutoff date indirectly defers I/C A/R invoice creation through the Parent-Child dependency– Deferring the I/C invoice creation also postpones the
transfer price selection
39
![Page 40: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/40.jpg)
Cost Cutoff Date Impact on a SO Issue
40
![Page 41: Inter Company Invoicing](https://reader033.vdocuments.us/reader033/viewer/2022061205/548003425806b5a95e8b492b/html5/thumbnails/41.jpg)
Additional Resources
• I/C Invoicing for Internal Reqs / Internal Orders: Han Kolbe presentation [2006 NorCal OAUG Seminar on Intercompany Transactions]
• COGS Account Generator for I/C Payables: John Peters presentation [2006 NorCal OAUG Seminar on Intercompany Transactions]
• “Overview of Intercompany Invoicing” Oracle white paper by Sharma Manda [Oracle Support doc # 336129.1]
• “Internal Drop-Shipping: Multi-Org Inter-company Invoicing in 11.5.10” by David S. McCurry [OAUG COL’06 white paper presentation]
• “Multiple Organizations in Oracle Applications” Oracle white paper, Appendix B – “Using The Account Generator to Generate the Intercompany COGS Account” [Oracle Support doc # 226456.1]
• “11i Cost Cutoff Date to Retroactively Cost Transactions” by Eric Guether [NorCal OAUG 2007 Training Day presentation and Oracle Support doc # 432515.1 ]
41