A better way to record, process and report pre-paid expenses.
This article focuses on the basic steps in setting up multi‐period accounting in Oracle E-Business Suite (EBS) Payables using Sub‐Ledger Accounting and the common mistakes that people make during setup.
We also cover the lack of functionality in EBS to support transfer of multi‐period entries to the Projects module. We will share how we overcame the deficiency to successfully make multi‐period accounting work in projects mirroring the accounts payable entries
Multi‐period Accounting
Multi‐period Accounting is a standard Oracle EBS function in the Accounts Payable module that enables expenses to be recognized over multiple periods. This is an option available at the invoice line level that can be enabled for both purchase order (PO) matched and unmatched manual invoices. You need to select the "Deferred Option" checkbox and specify the start date, number of periods and period type (days, weeks, months or years).
Environment and Expectations
Founded in 1915, the National Board of Medical Examiners (NBME) is an independent, not-for-profit organization that serves the public through its high-quality assessments of healthcare professionals. NBME is a project-driven organization using Oracle E-Business Suite Release 12.1.3, in a single legal entity, single operating unit and non-Multiple Reporting Currencies (non-MRC) environment. Modules used include AP, AR, GL, FA, Purchasing, i-expenses, OTL, Projects, Cash Management, Order Management and HR.
Purchase orders are created from purchase requisitions entered by end users with expense account coding. Purchase orders also contain Project, Organization, Expenditure Type, Task and Expenditure Date (POET) information. Invoices for these are created by matching to purchase orders. In some cases, these are pre‐paid invoices that are paid well in advance for future services/goods.
We wanted to record expense of such pre‐paid invoice lines over a given period. Also, we wanted the information to flow into projects in the same fashion as information flows into GL from AP. We reconcile the Projects amounts by expenditure type to GL account balances. In addition, we wanted the capability to report on the amortized expense details for reconciling the pre‐paid expense account balances in GL.
Setting up Multi‐period Accounting
The setup of multi‐period accounting is straightforward, but it is a multi‐step process. The setup starts at the lowest level, which is the journal line type, and ends when the ledger setup is updated to use the new Subledger Accounting Method (SLAM). The setup has flexibility to allow invoking rules based on certain conditions, but care should be taken while setting up the rules to handle both "meets" and "not meets" conditions. For example, we would like to use the PO charge account while expensing monthly amortized amounts for invoice lines that have PO line information but use the invoice distribution account entered for other lines that do not have the PO line information. Failure to handle the in and out conditions will result in exceptions during create accounting process.
Complete multi-period account setup steps are detailed in Exhibit A for our requirement above.
Shortcomings of the Standard Multi‐period Accounting Process
The standard multi‐period accounting process does have some major shortcomings that did not help us attain the time savings and efficiency we were hoping to gain (See Figure 1):
- Lack of functionality to feed projects module with information about multi‐period amounts is one of them. When Payables Invoice information is interfaced to projects module, the full invoice line amount is transferred to projects in the same month of the invoice, irrespective of the start date of multi‐period accounting specified at the invoice line. But the information is sent to GL properly based on the multi‐period setup.
- The projects and GL amounts are different, so these transactions have to be reconciled manually.
- There is no standard reporting available to see what has been expensed so far and what is due for expensing in the future. This makes it very difficult to reconcile the pre‐paid account balance in GL.
Missing in the Standard Multi-period Accounting Process
For a given invoice line with deferred expense recognition.
- Full invoice line amount is transferred to projects and accounted in the current period.
- Amount transferred to GL from payables for a given period does not match amount interfaced to projects.
- Unable to report on expense amortization information.
- Unable to easily reconcile pre-paid account balance in GL with subledger.
NBME's Solutions to the Missing Functionality
We addressed the missing functionality with this custom solution (See Figure 2):
- We created a custom program that pulls information from the SLA accounting tables and interfaces it to the Projects module like a Supplier Invoice transaction. The logic is to first reverse (create similar transaction with negative amount) the full invoice line amount transaction for the multi‐period invoice line created in projects by the standard "PRC: Interface Supplier Costs" program. Then, pull accounted information that has been transferred to GL for the given period from SLA module and stage the records in the PA_TRANSACTIONS_INTERFACE_ALL table.
Once the information is staged, it is marked as processed in the SLA tables using a descriptive flexfield (DFF) to prevent it from getting pulled again. The staged records can then be pulled into projects via the standard "PRC: Transaction Import" program. This enables us to easily reconcile the GL amounts to Project amounts.
See Exhibit B for the query to get the information from SLA tables for a given invoice line and period.
- We created custom reports using information from the SLA tables. The report provides detailed information on which invoice line has been expensed, how much and what is left of it, and when it will be expensed. This gives us a complete picture of all the multi‐period transactions, and we are able to reconcile our GL pre‐paid balance accurately. This report can be run any time, giving you up-to-date information.
See Exhibit B for the query, which can be slightly modified to obtain balance/reconciliation reports by eliminating the clause in the query that limits the selection of record for reporting.
NBME's Solution to Address Missing Functionality
- Created custom concurrent program.
– To pull information from SLA tables.
– Reverse the full invoice line amount in projects.
– Create new expenditures in projects for the GL amount transferred in a given month.
– Used the standard "PRC: Transaction Import" process to pull above adjustments into projects.
– This custom program is run as part of month-end process.
- Created custom reports.
– To select information from SLA tables.
– Provide detail amortization information.
– Provide information on what has been expensed/remaining.
– Used the standard "PRC: Transaction Import" process to pull above adjustments into projects.
– Can be run at any time
Lessons Learned During Setup and Implementation
There is good documentation available for setting up multi‐period accounting, but here are some of the things we learned during the setup and implementation:
- When creating a new setup, use the copy functionality on the screens to duplicate the standard item and make changes to the new one. This way, all the standard functionality is still maintained, and you do not risk overwriting the standard setup.
- When setting up account derivation rules, make sure to use conditions to execute the rule only when the deferred option is checked in the invoice line. If not, the rule will fire for every line of your invoice, and you will have incomplete/incorrect accounting.
When setting up the account derivation rule for the journal line description, have the first rule to derive the complete accounting combination without any conditions. Subsequently, add rules to derive specific values for segments of the combination by using conditions. This way, the create accounting will not error out because of an incomplete accounting combination.
- When setting up the multi‐period options for the journal line description, make sure to select the prorate type based on your needs. If you would like equal amounts in every period, select the "360 days" value instead of period name. Also, the line assignment should be set up completely to avoid any accounting entries.
- Any change to the setup at any level may invalidate the Application Accounting Definition (AAD). So after every change, make sure the invoice class in the AAD is "Valid."
Conclusion
Implementing multi‐period accounting saved us more than eight hours during month-end processing. The basic setup needed has been documented well and is straightforward. The key to implementing this successfully is to understand how the rules and conditions work. Our custom process enhancements made it more worthwhile for reporting and made the process completely automatic by eliminating manual uploads and reconciliation. (See Figure 3.)
What We Achieved
- Savings of 8 hours per month.
- Automated process to manage pre-paid expense recognition.
- Visibility to detail amortization information.
- Better reporting capability.
- Easier reconciliation to GL.
References
Oracle Subledger Accounting Implementation Guide Release 12.1 Part No. E13628‐04 1315106.1.
R12.x: Subledger Accounting: White Paper on Multi Period Accounting.
R12.x: Subledger Accounting: White Paper on Multi Period Accounting.
Exhibit A:
Setup Steps
Please note that these steps for using multi-period accounting are based on Oracle E‐Business Suite Release 12.1.3
1) Setup two new Journal Line Types.
Payables SuperUser – Setup‐Accounting Setups – Subledger Accounting Setup – Accounting Methods Builder – Journal Entry Setups – Journal Line Types
- The first Journal Line Type is a new Journal Line Type created for Expense Recognition.
- The Second Journal Line Type is for Item expense and is copied from the standard "Item Expense" Journal Line Type.
- The Second Journal Line Type is for Item expense and is copied from the standard "Item Expense" Journal Line Type.
2) Set up New Journal Line Definition.
It is always recommended to copy from the existing standard Journal Line definition.
Create a new Journal Line definition NBME MPA ACCRUAL_INVOICES_ALL by copying definition from ACCRUAL_INVOICES_ALL. Uncheck the Active flag on the Standard Item Expense Journal Line Type. This way, the Journal Line Type will be used instead of the standard line type.
Create a new record for the Journal Line Type you created in Step 1.
The next step is to derive the Account Derivation Rule. But before that, create the "Mapping Set" to derive the pre‐paid expense Natural account. This is specifically needed for Invoice lines matched to a PO that you are planning to defer, as the distribution chart of accounts is inherited from a PO document that usually has an expense account coded and cannot be changed in the invoice.
– Create a mapping set with list of input and output values.
– Create a new account derivation rule using the mapping set above, and make sure the condition is set up properly. In this case, this account derivation rule will kick in only when the "Deferred" flag is checked on the invoice line.
– Create another rule for deriving the "Unit" segment of the chart of accounts. Make sure the condition is set up properly to execute when you want it to.
Now, set up the Journal Line Type with the Account Derivation Rules created above. It is important to have the first rule set up to generate a default chart of account value without any conditions. The custom rules can then be applied subsequently to derive values for individual chart of account segments.
Select the newly created Journal Line Type and click on the Multi‐Period Accounting button. In the options section, select the options needed. Please note that selecting "360 days" for "proration type," expenses the amount equally over the number of periods selected during invoice line creation.
Click on the Line Assignments to assign the line types created earlier.
- The first line type is the credit account derivation for each month.
- The second line type is the debit account derivation for the expense recognition
The setup for the NBME MPA Expense Rule is below. This is important to handle both purchase order matched invoices and manually entered invoices.
3) Set up a new AAD (Application Accounting Definition) by copying from standard Accrual Basis and assign the new Journal Line definition created above to the Invoice event class. Make sure to validate the complete AAD.
Payables SuperUser – Setup‐Accounting Setups – Subledger Accounting Setup – Accounting Methods Builder – Methods and Definitions – Application Accounting Definitions
Payables SuperUser – Setup‐Accounting Setups – Subledger Accounting Setup – Accounting Methods Builder – Methods and Definitions – Application Accounting Definitions
4) Create a new Subledger Accounting Method (SLAM) by copying from standard SLAM Standard Accrual. Assign the new AAD to the Payables Application.
Payables SuperUser – Setup‐Accounting Setups – Subledger Accounting Setup – Accounting Methods Builder – Methods and Definitions – Subledger Accounting Methods
5) Update the Ledger Setup to use the new SLAM for your operating unit.
Payables SuperUser – Setup‐Accounting Setups – Ledger Setup – Define – Accounting setups
Click on Update against your operating unit and update the SLAM to the new one you created. Save the changes.
6) Now when the Create Accounting is run in payables, the invoice lines deferred for multi-period expense recognition are staged in the SLA module with the line amounts split across periods.
7) To account the multi‐period expense for every period, run the concurrent request "Complete –Multi-period Accounting" to transfer entries from the SLA module to General Ledger.
Exhibit B:
Query to Pull Information from SLA Table for Custom Processing and Reporting
select a.event_type_code, a.ae_header_id,
b.ae_line_num, b.description,
b.gl_sl_link_id, a.accounting_date,
a.accounting_entry_status_code, a.gl_transfer_status_code,
b.accounting_class_code, d.concatenated_segments, decode(b.accounting_class_code,'LIABILITY',(‐ 1*f.unrounded_entered_cr),f.unrounded_entered_dr) amount,
a.period_name, f.applied_to_dist_id_num_1,
g.project_id, g.task_id,
g.expenditure_type, g.invoice_line_number inv_line_num,
h.name org_name, g.expenditure_organization_id exp_org_id,
decode(b.accounting_class_code,'LIABILITY',g.dist_code_combination_id,b.code_combination_i
d) code_combination_id, g.invoice_id invoice_id
b.ae_line_num, b.description,
b.gl_sl_link_id, a.accounting_date,
a.accounting_entry_status_code, a.gl_transfer_status_code,
b.accounting_class_code, d.concatenated_segments, decode(b.accounting_class_code,'LIABILITY',(‐ 1*f.unrounded_entered_cr),f.unrounded_entered_dr) amount,
a.period_name, f.applied_to_dist_id_num_1,
g.project_id, g.task_id,
g.expenditure_type, g.invoice_line_number inv_line_num,
h.name org_name, g.expenditure_organization_id exp_org_id,
decode(b.accounting_class_code,'LIABILITY',g.dist_code_combination_id,b.code_combination_i
d) code_combination_id, g.invoice_id invoice_id
from xla_ae_headers a,
xla_Ae_lines b,
ap_invoices_all c,
gl_code_combinations_kfv d,
xla_transaction_entities e,
xla_distribution_links f,
ap_invoice_distributions_all g,
hr_organization_units h
xla_Ae_lines b,
ap_invoices_all c,
gl_code_combinations_kfv d,
xla_transaction_entities e,
xla_distribution_links f,
ap_invoice_distributions_all g,
hr_organization_units h
where a.ae_header_id = b.ae_header_id
and d.code_combination_id = decode(b.accounting_class_code,'LIABILITY',g.dist_code_combination_id,b.code_combination_i d)and e.source_id_int_1 = c.invoice_id
and e.entity_code = 'AP_INVOICES'
and b.accounting_class_code in ('LIABILITY','EXPENSE')
and g.invoice_distribution_id = f.applied_to_dist_id_num_1
and e.entity_id = a.entity_id
and g.expenditure_organization_id = h.organization_id
and b.ae_header_id = f.ae_header_id
and b.ae_line_num = f.ae_line_num
and c.invoice_id =<invoice_id>
and g.invoice_line_number =<invoice_line_number>
and a.period_name =<period_name>
and a.accounting_entry_status_code = 'F'
and a.gl_transfer_status_code = 'Y'
and b.attribute15 is null ;.
and d.code_combination_id = decode(b.accounting_class_code,'LIABILITY',g.dist_code_combination_id,b.code_combination_i d)and e.source_id_int_1 = c.invoice_id
and e.entity_code = 'AP_INVOICES'
and b.accounting_class_code in ('LIABILITY','EXPENSE')
and g.invoice_distribution_id = f.applied_to_dist_id_num_1
and e.entity_id = a.entity_id
and g.expenditure_organization_id = h.organization_id
and b.ae_header_id = f.ae_header_id
and b.ae_line_num = f.ae_line_num
and c.invoice_id =<invoice_id>
and g.invoice_line_number =<invoice_line_number>
and a.period_name =<period_name>
and a.accounting_entry_status_code = 'F'
and a.gl_transfer_status_code = 'Y'
and b.attribute15 is null ;.
Major Components for Oracle Application Testing Suite
