R12 SLA Sources Breakdown
One of the most fundamental components of the R12 Subledger Accounting Architecture (SLA) is the concept of SLA Sources. There is a good bit of documentation on many SLA components, including my prior OAUG Presentations on SLA, but good explanations of SLA sources are a bit harder to find. So I thought I’d take a shot at helping spread better understanding with this post.
SLA Source values play critical roles in XLA event processing and R12 GL journal creation such as:
- fundamental data elements that drive how Journal Line Type (JLT) and Account Derivation Rules (ADR) are selected through the use of Sources in JLT and ADR conditions
- fundamental data elements which are assigned as XLA Accounting Attributes (e.g. the value of the accounted_amount) to the JLTs
- often used in XLA Journal Line descriptions defined
To really understand why the XLA engine selects applicable JLTs/ADRs and how the accounting attributes are derived, a solid comprehension of what’s behind SLA sources is key. At the most basic level, Sources are data elements related to the specific detail transaction being processed by XLA event processing. These data elements may be:
- static and directly related to transaction being accounted, e.g. the CCID for an AP Invoice Distribution event being processed (ap_invoice_distributions_all.dist_code_combination_id),
- static and indirectly related to the transaction, e.g. a DFF value related to a Vendor for an ap invoice (hz_parties.attribute1 joined in via ap_invoices_all),
- dynamically generated or calculated during XLA Event processing and temporarily populated into a global temporary table for use by the XLA processing engine, e.g. the Upward Prior Year Adjustment Amount calculated for an AP Invoice matched to a PO distribution where the invoiced quantity exceeds po distribution quantity within tolerance (fv_extract_detail_gt.acc_unpaid_obl_pya_amount).
The Sources available for an Application (e.g. Purchasing) and the Source Assignments to Application Event Classes (e.g. Purchasing Requisitions) can be queried by navigating Setup > Subledger Accounting Setups > Accounting Methods Builder > Sources > Sources or …. > Sources > Source Assignments.
Great, right?? Well, being the control-freak I can be sometimes, I really needed to know more. What tables or whatever are these Sources pulling from? How are they getting their values?
Upon further analysis and digging, I started understanding that these Sources map directly to numerous database views. The Source_Code is a direct key to the column names within views. These views then can often be deconstructed to figure out the source table data element which is driving the source. For sources which are already utilized in an existing Application Accounting Definition (AAD), the Source to View (aka extract_object_name) relationship can be shown by querying the xla_aad_sources table such as the following:
Furthermore, here is a query I’ve developed to be able to analyze the universe of various SLA sources available by Entity and Event Class (these are bind variables in the query) in SQL Developer: Available SLA Sources by Event.
So that’s it, right? Well, not quite. Some views are simple to deconstruct to find the source data, whereas others point to tables like FV_EXTRACT_DETAIL_GT (for Federal Financials). For example, the FV_XLA_PO_REF_V is like: select …. prior_year_flag…. from fv_extract_detail_gt. This table wasn’t something I was familiar with. The …gt (Global Temporary) tables are tables that are temporarily populated during XLA event processing by some PL/SQL logic and then those derived/calculated values are made available to the XLA event processing engine via the views looking at the ..gt tables. For example, when the XLA event processing engine detects the EBS environment is a federal environment, the XLA event processing will engage an FV “hook” which will then execute add’l FV-specific code as part of the XLA processing. An example, is when the FV_SLA specific code will look at the BFY on a transaction vs the Period Year of the event being processed and then determine if the transaction is a Prior Year (prior_year_flag will be set to ‘Y’) or a Current Year transaction (prior_year_flag will be set to ‘N’). Then an agency’s JLTs (& the standard Federal JLTs) can look at the Federal Prior Year Flag source value to determine if an Upward/Downward Accounting adjustment may be necessary. For troubleshooting SLA, typically the values written to fv_extract_detail_gt can’t be analyzed outside of the XLA event processing db session, however there is an option to dump the source values written to a fv_extract_detail_gt_logs table if the FV: Debug profile is set to Yes.
Kindergarten stuff, right?? Hope this helps and good luck.
Oracle Federal Applications 
This helped with my understanding. Thanks.
Anonymous
15-Aug-12
this helped me, thanks for sharing
damien
5-Mar-14
This was very helpful, thanks for sharing!!
Anonymous
10-Apr-14
This post will assist the internet people for building up new webpage or even a weblog from start to end.
gcf calculator
6-Jun-14
Hi there, this weekend is good in support of me, since this moment i am reading
this wonderful informative paragraph here at my residence.
landscaping in lake havasu city
20-Aug-14
Great blog! Do you have any suggestions for aspiring writers?
I’m hoping to start my own site soon but I’m a little lost on everything.
Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many choices out
there that I’m completely overwhelmed .. Any ideas?
Bless you!
collection agency ohio
11-Sep-14
Its not my first time to visit this web page, i am browsing this website dailly and obtain fastidious data from here everyday.
Nila
9-Oct-14