1 / 60

R12 Federal Financials Subledger Accounting Teardown

R12 Federal Financials Subledger Accounting Teardown . Larry Baugh Apps Ingenuity LLC April 24,2012. Introduction. Larry Baugh 13+ years Oracle Federal Financials Functional/Technical/ Mgmt Experience

aspen
Télécharger la présentation

R12 Federal Financials Subledger Accounting Teardown

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. R12 Federal Financials Subledger Accounting Teardown Larry Baugh Apps Ingenuity LLC April 24,2012

  2. Introduction • Larry Baugh • 13+ years Oracle Federal Financials Functional/Technical/Mgmt Experience • Emphasis Areas: GL, Budget Execution, Fed Admin (FV), AR, AP, IBY, FA, Fed Reporting, PA, PO, iProc, Proc Contracts, XLA, XML/BI Publisher, Discoverer, SQL, PL/SQL, etc. • Sample Federal Customers: DOI-NBC, USAF/USTRANSCOM-DEAMS, DFAS, TMA, SBA, Other DOE, DoD, and misc Federal Civilian entities. • Recent Role: Solution Architect/Co-PM for US Dept. of Interior National Business Center (DOI-NBC) Shared Services Environment Release 12.1.3 Upgrade (Go-Live = Feb ‘12).

  3. Agenda • SLA Intro: What is SLA? How is SLA invoked? What does the Engine Do? • SLA Rules Configuration Framework, the Accounting Methods Builder • Event Processing – Which components are applicable When? • Other Key AMB Components • SLA Sources and Custom Sources • Accounting Attributes & other Journal Line Type Pieces • Account Derivation Rules • SLA Debugging and Accounting Event/Entry Analysis • Accounting Methods Builder Context • 11i vs R12 GL Drilldown differences • SLA vs XLA vs FAH • GL Transfer Summarization Level in R12 • Takeaways Goal: To familiarize attendees with the internals of Federal SLA, enabling enhanced capabilities for troubleshooting and maintaining Agency-specific SLA Configurations.

  4. What is SubLedger Accounting (SLA) • A centralized, rules-based accounting engine/service utilized throughout R12 to create an agency’s required USSGL accounting entries. • A schema and toolset to support various features including enhanced drilldown/reconciliation between the GL/Subsidiary modules and additional reporting capability around accounting transactions/balances. Federal Financials Customers must implement SLA when deploying R12. USSGL Transaction Codes are no longer an option for accounting events except for manual or custom interfaced GL Journals. FV PO AP PA AR FA XLA GL Primary Ledger

  5. How is SLA invoked to create the Acctg? • SLA processes are triggered differently depending on whether the transaction event is subject to Budgetary Control/Encumbrance processing. • For events not subject to Budgetary Control, SLA processes are initiated via: • Submitting the Create Accounting concurrent process (or wrapper, e.g. AR Submit Accounting) • Form Actions

  6. How is SLA invoked to create the Acctg? • For events subject to Budgetary Control (i.e. FV Budget, PO Reqs/POs/Releases, AP Invoice Validation), SLA processes are initiated via Funds Check and Funds Reservation Actions. • Funds Check actions will create the SLA entries in a temporary ‘Draft’ status. • Funds Reservation actions will create the SLA entries in a ‘Final’ status.

  7. What does the SLA Engine do? • As the SLA engine processes, it: • Creates the appropriate accounting entries from defined SLA rules consisting of key attributes including base CCIDs, Natural Account Segments, Accounted/Entered Dr/Cr, Journal Line Descriptions, etc. • Creates new accounting journal entry and subsidiary-linkage records in the XLA tables. • Optionally transfers to General Ledger • Updates XLA Balance tables

  8. SLA Rules Configuration Framework - AMB • The rules and configurations which dictate the accounting entries created by SLA are setup using the Accounting Methods Builder (AMB). The AMB Framework provides a hierarchical mechanism for creating and maintaining a Ledger’s SLA configuration. • Organizational Components: • Subledger Accounting Methods (SLAM) (e.g. the US Federal Method) • Application Accounting Definitions (AAD) (e.g. the PO AAD) • Event Classes/Types (e.g. Purchase Order and Agreements) • Journal Line Definitions (JLD) (e.g. Federal Purchasing) • The AMB Components that create the Fundamental Journal entry lines & CCIDs: • Journal Line Types (JLT) (e.g. FV PO Unanticipated Budget) • Account Derivation Rules (ADR) (e.g. Base CCID, SGL) • Supporting Components: Sources, Accounting Attributes, Journal Descriptions, Mapping Sets, Supporting References, Business Flows, etc.

  9. SLA Rules Configuration Framework - AMB AADs and supporting components across modules roll-up to the SLAM.

  10. SLA Rules Configuration Framework - AMB A SLAM is tied to the Ledger using the R12 Accounting Setup Manager.

  11. SLA Rules Configuration Framework - AMB The seeded US Federal Accounting SLAM serves as helpful guide in understanding how SLA can be used for Federal Financials customers, but most Fed installs will likely have to build/copy to custom rules based on their agency-unique USSGL structure, SLA posting requirement variations and to overcome any bugs in the seeded US Federal SLAM. For Federal Upgrades, note that upon initial upgrade, Ledgers will not be automatically assigned to the US Federal SLAM. The upgrade process will assign the “Accrual with Encumbrance Accounting” SLAM. Be sure to update the Ledger SLAM assignment to start becoming familiar with the US Federal SLA baseline.

  12. SLA Rules Configuration Framework - AMB An agency-specific SLAM can be configured with AMB Component objects created (i.e. copied & tweaked) to meet specific requirements. This will also help better insulate a SLAM from future patch releases/changes.

  13. SLA Rules Configuration Framework - AMB Note that in the Seeded Federal AADs, the Natural Account segment ADRs are all tied to the FV_ADR_ACCOUNT value-set. In order to utilize the seeded FV ADRs, an instance’s Natural Account Values must match the values in the FV_ADR_ACCOUNT set.

  14. SLA Rules Configuration Framework - AMB If creating a new agency-specific JLD, if the JLD is mapped to a Chart of Accounts (COA), the FV Seeded ADRs cannot be copied/associated into the new COA-specific JLD. This is because the copy process enforces that the value-set used on any ADRs, must be the value-set of the matching segment in the defined COA.

  15. SLA Processing – What’s Applicable When? With all of the various AMB Components, it can be a bit confusing at first to understand how JLTs and ADRs are turned on/off for specific transactions. execution Which AAD? Which Event Class? Which JLD(s)? Which JLTs? Which ADRs?

  16. SLA Processing – What’s Applicable When? • Factors that control which JLTs/ADRs are applicable and fire for specific transaction events: • Module of the transaction: Controls which AAD of the SLAM will be evaluated • SLA Event Class/Type of the transaction: Controls which JLD(s) of the AAD will be evaluated • Is the transaction subject to Budgetary Control: Controls which JLD(s) of the AAD will be evaluated • JLT Conditions: Once the SLA Event processing identifies a transaction as applicable to a JLD, the SLA engine will evaluate the transaction attributes against all the JLT conditions within the JLD to determine which JLTs will fire for the transaction. • ADR Conditions: For all JLTs turned on, the mapped ADRs will then be evaluated based on their conditions. Which AAD, Event Class, JLD(s), JLTs, ADRs?

  17. SLA Event Processing – Which EventType? The method of associating a specific SLA Event Type code to a transaction differs between modules, but there is no straight-forward way of precisely mapping the SLA Event Type Codes to typical transaction events. However, the Event Type Codes/Names are mostly understandable.

  18. SLA Event Processing – Which EventType? Example: For Purchasing, Event_Type_Codes for PO Transactions are created by the po_encumbrance_postprocessing.get_event_and_entity_codes procedure which is executed by a Purchasing Funds Check/Reserve action, just prior to a call to SLA Event Processing. IF (p_action = g_action_RESERVE) THEN l_action_str := 'RESERVED'; ELSIF (p_action = g_action_UNRESERVE) THEN l_action_str := 'UNRESERVED'; ELSIF (p_action = g_action_ADJUST) THEN l_action_str := 'ADJUSTED'; ELSIF (p_action = g_action_CANCEL) THEN l_action_str := 'CANCELLED'; ELSIF (p_action = g_action_FINAL_CLOSE) THEN l_action_str := 'FINAL_CLOSED'; ELSIF (p_action = g_action_UNDO_FINAL_CLOSE) THEN l_action_str := 'UNDO_FINAL_CLOSED'; ELSIF (p_action = g_action_REJECT) THEN l_action_str := 'REJECTED'; ELSIF (p_action = g_action_RETURN) THEN l_action_str := 'RETURNED'; -- Bug 4684263 Begin ELSIF (p_action = g_action_INVOICE_CANCEL) THEN l_action_str := 'INV_CANCELLED'; ELSIF (p_action = g_action_CR_MEMO_CANCEL) THEN l_action_str := 'CR_MEMO_CANCELLED'; x_event_type_code := l_entity_str||'_'||l_action_str; PROCEDURE get_event_and_entity_codes( p_doc_type IN VARCHAR2, p_doc_subtype IN VARCHAR2, p_action IN VARCHAR2, x_entity_type_code OUT NOCOPY VARCHAR2, x_event_type_code OUT NOCOPY VARCHAR2 ) IS … IF (p_doc_type = 'REQUISITION') THEN x_entity_type_code := 'REQUISITION'; l_entity_str := 'REQ'; ELSIF (p_doc_type IN ('PO','PA')) THEN x_entity_type_code := 'PURCHASE_ORDER'; l_entity_str := 'PO_PA'; ELSIF (p_doc_type = 'RELEASE') THEN x_entity_type_code := 'RELEASE'; -- Bug 5015010 l_entity_str := 'RELEASE'; ELSE l_error_flag := 'Y' ; END IF;

  19. SLA Event Processing – Which EventType? SLA Event Classes and Types are seeded and not modifiable for standard E-Business Suite transactions. Event Class to GL JE Category configurations can be updated if desired. For external E-Business Suite transactions, custom Event Classes/Types & other XLA configs can be accomplished with a Financials Accounting Hub (FAH) license.

  20. SLA Event Processing – Which JLD? In some cases, the Budgetary ControlFlag on the JLD will be a factor on whether a JLD is applicable for a particular transaction event. (Example: Federal Payables AAD - Invoices JLDs) For the Event Class = ‘Invoices’, two JLDs are assigned. During Invoice Validation, the Funds Checking engine invokes SLA to determine any Accounting Impacts subject to Budgetary Control, i.e. any JLDs with the Budgetary Control flag checked. Afterword, the Create Accounting process is run which invokes SLA to create accounting with any JLDs that don’t have the Budgetary Control flag checked.

  21. SLA Event Processing – Which JLTs?? Typically, when a JLD is valid for an event, only some of the JLTs defined within the JLD will be applicable, not all.

  22. SLA Event Processing – Which JLTs?? All conditions of the JLT must be true for the transaction, in order for the JLT to be activated.

  23. SLA Event Processing – Which JLTs?? • For our two example JLTs, key condition differences are: Functionally, the conditions make sense in the Federal environment, but fully understanding the values represented by the sources requires more digging into SLA Sources.

  24. SLA Sources Sources are R12 data elements that can be used in AMB rule definition. Sources are assigned to the Event Classes they are applicable to. R12 provides thousands of seeded sources and also provides the capability for Custom Sources.

  25. SLA Sources SLA Sources map to columns of database views. These views represent numerous data elements directly related to or derivable from a transaction and its data attributes Fv_xla% _ref_%v Views: e.g. FV_XLA_PO_REF_V FV_XLA_AP_REF_V % _extract_%v Views: e.g. PO_EXTRACT_HEADER_V PO_EXTRACT_DETAIL_V PO_DISTS_REF_V SLA processes include creating a variety of temporary “Extracts” during event Processing. Transaction attribute values are gathered and inserted into specific Global Temporary (_GT) tables, to be efficiently exposed as SLA Sources for use in conditions, accounting attributes, journal line descriptions, etc. EBS Core Tables

  26. SLA Sources Multiple Federal Financials-specific sources are collected/derived and inserted into the fv_extract_detail_gt by a variety of R12 FV SLA Processing packages. These sources are made available to the SLA rules engine via a set of fv_xla_%ref_v views. TIP: If the FV: Debug Profile option is enabled, the extract data populated during the XLA event processing is archived into fv_extract_detail_gt_logs. Fv_xla% _ref_%v Views: e.g. FV_XLA_PO_REF_V FV_XLA_AP_REF_V fv_sla_%_processing_pkgs e.g. fv_sla_po_processing_pkg EBS Core Tables FV_EXTRACT_DETAIL_GT FV_EXTRACT_DETAIL_GT_Logs

  27. SLA Sources To 100% understand the driving field or logic behind the seeded SLA Sources, the source’s extract object view needs to be interrogated and in some cases, PL/SQL needs to be searched to understand the data’s origination.

  28. SLA Sources In the case of our Federal Prior Year Source, the originating source is the fv_sla_utl_processing_pkg.get_prior_year_status procedure. The fv_sla_utl_processing_pkg is triggered by a series of “hooks” originating from the xla_accounting_pkg. The procedure compares the period_year of the current transaction’s GL Date versus the BFY on the transaction to determine if the BFY is Prior Year. Note: If the GL Date Year is not setup in the FV Define Federal Options form (aka BFY Mapping table), the procedure will throw an exception, preventing SLA Entry creation. create or replace PACKAGE BODY fv_sla_utl_processing_pkg AS ….. PROCEDURE get_prior_year_status ( p_application_id IN NUMBER, p_ledger_id IN NUMBER, p_bfy_value IN VARCHAR2, p_gl_date IN DATE, p_pya OUT NOCOPY VARCHAR2, p_pya_type OUT NOCOPY VARCHAR2, p_error_code OUT NOCOPY NUMBER, p_error_desc OUT NOCOPY VARCHAR2 )

  29. Custom Sources Although thousands of seeded SLA Sources are available, the potential exists that some data attribute desired for use may not be available as a seeded source. R12 provides the capability for developing custom PL/SQL functions that can then return values to be used as sources. Seeded sources are passed as input parameters into the custom functions, in the order they are configured in the Custom Source definition form. Custom Sources can be utilized as Accounting Attributes if defined.

  30. Custom Sources • Custom Source Development Recommendations: • Consolidate the custom sources into a single or few packages, i.e. XXAAA_SLA_UTL_PKG or XXAAA_PO_SLA_UTL_PKG. • Utilize a debug mechanism to be able to capture key data/variables utilized during custom source execution. The potential exists that some data may only be available inside the XLA Event processing session. • IF l_debug = 'Y' THEN • INSERT INTO CUS01.XXAAA_XLA_DEBUG • (id,creation_date,message_text, module) • VALUES (xxaaa_xla_debug_s.nextval, sysdate, • '2: Values - p_pay_hist_dist_id: '||p_pay_hist_dist_id||', l_exchange_rate: '||l_exchange_rate||' l_event_type: '||l_event_type, l_module); • END IF; • Try to standardize on input Sources that are consistently solid & code from there. Many sources are null, depending on the transaction (even sources like Ledger_ID) • Keep as simple and high performing as possible. • Version control the code.

  31. JLT Components – Accounting Attributes All JLTs must include Accounting Attribute Assignments. The Accounting Attribute Assignments are used to populate numerous columns in the XLA_AE_LINES, XLA_DISTRIBUTION_LINKS and GL_JE_LINES depending on the accounting entry summarization level utilized. For example, the value for the SLA Source mapped to the ‘Accounted Amount’ will be the Accounted_Dr or Accounted_Cr amount created in XLA_AE_LINES/GL_JE_LINES if detail level summarization is utilized. Otherwise, the value will be summarized with other XLA_AE_LINES or XLA_DISTRIBUTION_LINKS records.

  32. JLT Components – Side The “Side” setting on JLTs can be a bit confusing when first getting familiar with SLA. The “Side” value does not represent that a JLT will always 100% create a Credit entry or always create a Debit entry, it depends on the sign of the JLT’s accounted amount value. If accounted_amt > 0 & the side value = ‘Debit’, then the JLT will create a Debit. If accounted_amt < 0 & the side value = ‘Debit’, then the JLT will create a Credit.

  33. JLT Components – Balance Type To create the required Federal Trial Balance journal entries, the ‘Balance Type’ for all typical Federal Journal Entries should be ‘Actual’. In Federal Government business processes, ‘Actual’ journals are used for Funds Budget Distribution, Funds Status reporting and Funds Checking. ‘Encumbrance’ journals are typically utilized more in State & Local Governmental Accounting.

  34. JLT Components – Business Flow In my analysis to-date, I have not implemented the use of Business Flows in Federal JLTs. For US Federal R12 Upgrade customers, I’m concerned about the viability of Business Flows to properly link between 11i and R12 created Budgetary Accounting events. This is due to the lack of a SLA Historical Upgrade process for pre-R12 entries created by USSGL Transaction Codes.

  35. Account Derivation Rules (ADRs) Once the SLA engine determines a JLT will be invoked for an event, the ADRs provide the framework for creating the Code Combination ID (CCID) or Line of Accounting the JLT that will be posted. Every JLT must at least include a mapping to an ‘All Segments’ ADR that will result in a valid CCID. The ‘All Segments’ ADR will lay-down the baseline CCID for the Accounting Entry. Additional segment ADRs, i.e. SGL or Natural Account, can then be mapped and configured to override the basline CCID values for the applicable segment.

  36. Account Derivation Rules (ADRs) Conditions based on SLA Sources are the key components that drive ADR value selection, just like JLTs

  37. Journal Line Descriptions As desired, R12 supports flexible configuration of constant text and dynamic source values to be included in the SLA and GL journal header & line description fields.

  38. Mapping Components to Charts of Accounts Most AMB Components may be mapped to a Transaction and Accounting Chart of Accounts. • Mapping Components to a Transaction Chart of Accounts allows for: • In the case of JLTs, ADRs and Line Descriptions, non-qualified Flexfield Segments (i.e. not the balancing, natural, cost center, etc. segments), become available for use as sources.

  39. Mapping Components to Charts of Accounts Mapping JLDs to an Accounting Chart of Accounts value provides the capability to include ADRs for non-qualified segments. Vs.

  40. Mapping Components to Charts of Accounts The primary downside of mapping AMB Components to Transaction and Accounting Chart of Accounts values is that when multiple Chart of Accounts are used in an environment, the AMB Components will need to be duplicated. As an additional FYI, mapping AMB Components to different ‘Accounting Chart of Accounts’ values is utilized when SLA is utilized to create multiple accounting representations in secondary ledgers.

  41. SLA Debugging and Analysis Debugging of SLA Configurations is likely required when Budgetary Control event errors (i.e. Funds Reservation) prevent document processing Erroneous Accounting entries are being created

  42. SLA Debugging and Analysis • Key SLA Debugging tools/approaches include: • Using SQL Queries to analyze the accounting entries being created for a particular event, including understanding the JLTs which are firing to create the XLA_AE_LINE entries. • When exceptions occur that prevent data from being committed to the XLA tables for an event, the XLA actions can typically be caught by using standard FND Debug functionality. • Turn on FND Debug at Statement Level (Profile Options) • Select Max(Log_Sequence) From Fnd_Log_Messages M • Execute XLA Action (e.g. Funds Check/Reserve, Create Acctg) • Extract the fnd_log_messages created

  43. SLA Debugging and Analysis 3: Enable the FV: Debug Profile option to see the extract data populated during the XLA event processing in fv_extract_detail_gt_logs. Querying this table is the best method to see the FV source values derived for a transaction event. 4: Other extract source values are often written as fnd_log_message records during XLA processing.

  44. SLA Debugging and Analysis • Oracle also provides a standard SLA Diagnostics capability, which is helpful as long as exceptions don’t prevent the processing data from being committed to the XLA tables. • The SLA Diagnostics provides features to help with: • reviewing the source values for the transaction objects during XLA event processing • analyzing issues related to the generation of subledger journal entries. • To engage the Diagnostics features: • Set Profile Option- SLA: Enable Diagnostics to Yes • Enter Transactions • Run Request: Create Accounting • Run Request: Transaction Objects Diagnostics

  45. SLA Debugging and Analysis Lessons Learned: The majority of Purchasing exceptions encountered to-date are due to XLA Event Processing attempting to create unbalanced journal entries. This was often either due to JLTs not being invoked in some cases (sometimes due to bugs in seeded FV amount sources), or the multiple JLT accounted_amounts not balancing to zero. e.g. xla.plsql.XLA_AE_LINES_PKG.SetAcctLineTypep_accounting_line_type_code = C- p_accounting_line_code= FV_RLS_RE_RES_OBL_UNPAID xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_DR = xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_CR = 239.97 xla.plsql.XLA_AE_LINES_PKG.SetAcctLineTypep_accounting_line_type_code = C- p_accounting_line_code= FV_RLS_UNANTIC_BUD xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_DR = 319.96 xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_CR = xla.plsql.XLA_AE_LINES_PKG.SetAcctLineTypep_accounting_line_type_code = C- p_accounting_line_code= XXNBC_FV_RLS_UNPAID_OBL_PYA xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_DR = xla.plsql.XLA_AE_LINES_PKG.SetDebitCreditAmountsAccounted_CR = 319.96

  46. AMB Object Data Model Understanding the AMB Object Data Model helps provide enhanced capability for analyzing a SLAM for any defects, e.g. improper condition configurations between or duplicated across JLTs.

  47. Accounting Methods Builder Context The flexibility for using different AMB Context values is quite helpful in SLA testing and troubleshooting. Utilizing different AMB Context values, allows for working with AMB Component configurations executing transactions independently, without impacting users in other AMB Contexts, e.g. the Default.

  48. Accounting Methods Builder Context To enable the use of a separate AMB Context: Create a new lookup value for lookup type XLA_AMB_CONTEXT_TYPE, e.g. LARRY1 Utilize the ‘Export Application Accounting Definitions’ utility to export AADs from all desired modules from the source AMB Context. Coordinate with the DBA/UNIX team on a temp OS directory if needed. Switch the AMB Context value to the desired new value. Utilize the ‘Import Application Accounting Definitions’ utility to import AADs into all desired modules from the source ldt file created in Step 2.

  49. 11i vs R12 GL Drilldown differences Data model changes in the GL -> Subsidiary Drilldown will likely require significant rework to Agency’s reporting and outbound interfaces. gl_je_lines.referenceX are null in R12 or represent different data than in 11i. An additional complicating factor for Federal agencies upgrading to R12 is that the SLA Historical Upgrade process does not populate the XLA tables with data created from 11i Transaction Codes. Blended detail transactional reporting across both the 11i and R12 data models can get a bit complex.

More Related