Oracle Ebs R12 SLA vs. GL Relationship Changes

Http://www.cnblogs.com/bruce_zhao/p/3809886.html

Oracle Ebs R12 SLA vs. GL Relationship Changes

SLA concept: SLA (subledger Accounting) Sub-Ledger is the abbreviation for Subledger accounting, which literally means sub-Ledger ledger entries

SLA&GL Relationship:

the R12 function module is enabled with the MOAC feature, new submodule and tax module, OPM and discrete inventory are also integrated, all sub-module accounting entries can be configured with a specific company, the relationship between the three kinds:

1, sub-ledger accounting is actually the link between the sub-module accounting and GL Certificate bridge. Is the bridge between the sub-module and GL. All sub-modules (including FA) generate accounting entries that are generated using SLAs, are stored in the SLA table, and then posted to GL through the posting program. A little class gl_interface the function of the table.

2, sub-ledger accounting of the second layer of meaning: in each sub-module has a set of independent accounting entries, it looks like GL is not much different, which means that in the sub-module can actually calculate the account balance. Unfortunately, so far I have not had a table like gl_balance to hold account balances.

3, each sub-module is still can have their own allocation account (that is, the previous view of the accounts to see things), respectively, stored in their own distribution table, for example, the AP or stored in the ap_invoices_distributions, after the introduction of the SLA, the function is called "Transaction accounting" , and Sub-ledger accounting, the difference is that the transaction accounting is generated by automatic accounting or distribution, and sub-ledger accounting is based on the definition of accounting events and other formulas generated, respectively, stored in different places.

SLA&GL Relational Model correlation model,

1, sub-Ledger generation there are two ways, one way is directly from the sub-module of things to deal with accounting, one is directly from the sub-module of the processing of things to obtain.
2, sub-module of the things to deal with accounting and sub-ledger accounting is two different things, must be treated differently, transferred to GL is sub-Ledger accounting, is not a matter of processing accounting.
3, because the source of sub-ledger accounting may be the processing of things, may also be the matter of processing accounting, so there may be differences, which is the current shortcomings of the SLA.

SLA benefits to be brought

1. Flexible definition of the rules for the generation of accounting entries, including summaries, debits and credits
2, a thing processing can be posted to multiple ledger (that is, 11i of Books), which gives multinational groups to manage a number of books bring great benefits
3, unified the sub-module accounting entries of the deposit and generation rules, that is, each sub-module can be set according to their own circumstances accounting rules, but the accounting entries generated by these rules are stored back in the SLA table.
4, facilitate the expansion, Oracle Outsourcing sub-module generated by the accounting entries more easily integrated into the EBS SLA several key keywords
Base event Diagram

SLA the keywords in

accounting event   Accounting event is a record of the different event types that a thing deals with, combining the main ledger, event type, and event classification. There may be more than one accounting time for a thing to handle, because a transaction can take on multiple actions, and each action needs to generate the corresponding accounting voucher. Therefore, we can think of an accounting event as a complete voucher, we put this voucher into the sub-module of the Accounting Entry table to form a complete accounting entry. Therefore, my understanding of accounting events is generally summarized as follows:
1, accounting events equivalent to a voucher, input to GL is a pair of accounting entries
2, the same thing processing, such as the collection may correspond to a number of accounting events, because the creation of collections will generate accounting events, collection verification is also an accounting event.
View Path: Sub-module Super User/query/accounting Events

The concept of the main ledger (Leadger) Ledger in 12i represents the book of accounts, which is the sob of 11i, and the main ledger determines which sob to post to
the Event entity event entity determines the source of the accounting entries and, as an example, the event entity determines whether it comes from "receivables" or "collections". Stored in table: XLA_ENTITY_TYPES_VL, the table in which the scorecard is associated with the thing processing is xla_transaction_entities, and the event entity defines which field the associated identity is, and is stored in the Xla_entity_id_ mappings, I'll show you how to do this in detail below.
Set PATH Path: Sub-module Super User/setup/Accounting/Subledger Accounting/Event/Event model
event class event classification is a way to further differentiate accounting entries based on event entities. For example, the collections are divided into "collections" and "Miscellaneous collections", and the table of event classifications is: Xla_event_classes_v, which belongs to the Xla_transaction_entities child table.
Set PATH Path: Sub-module Super User/setup/Accounting/Subledger Accounting/Event/Event model
Event Type event types are smaller event partitioning methods than event classifications, and each event classification is subdivided into multiple event types. For example: The collection will be divided into: The collection has been cancelled, the collection is not written off, the collection has been updated and so on. stored in the table: XLA_EVENT_TYPES_VL.
Set PATH Path: Sub-module Super User/setup/Accounting/Subledger Accounting/Event/Event model

Base event Diagram

XLA_ENTITY_TYPES_VL (Event entity)

|――xla_entity_id_mappings (Entity ID correspondence table)

|――XLA_EVENT_CLASSES_VL (Event category)

|――XLA_EVENT_TYPES_VL (Event type)

Sub Ledger diagram

Xla_transaction_entities (accounting thing handling entity)

|――xla_events (Accounting events)

|――xla_ae_headers (Sub-Ledger head)

|――xla_ae_lines (Child ledger line)

|――xla_distribution_links (related things processing information)

Sub-module and GL Diagram

Gl_import_references (General Ledger Reference)

| (gl_sl_link_id,gl_sl_link_table)

Xla_ae_lines (Child ledger line)

Description: The association between GL and Submodule is implemented through Gl_import_reference, and the key field is gl_sl_link_id,gl_sl_link_table.

Gl-> Sub-module traceability

The

Front is the business aspect, now carries on the technical analysis, the more content is still in the reorganization.
    The most common technical aspect of the SLA is the journal source traceability, which differs significantly from GL to SLA and 11i in the retroactive process, and
makes it through the Gl_import_references table, the je_batch_id of the table, Je_header_id,je_line_num
is associated with GL, and the table field gl_sl_link_id is associated with the row tables (Xla_ae_lines) in the SLA,
      Some of the important tables in the SLA are as follows:   

Xla_transaction_entities:the Table Xla_entities contains information about
Sub-ledger document or transactions.
Xla_events:the xla_events table record all information related to a specific
Event. This table is created as a type Xla_array_event_type.
Xla_ae_headers:the xla_ae_headers table stores subledger journal entries.


There is a one-to-many relationship between accounting events and journal entry headers.
Xla_ae_lines:the Xla_ae_lines table stores the Subledger journal entry LINES.
There is a one-to-many relationship between Subledger Journal entry headers and Subledger
Journal Entry Lines
Xla_distribution_links:the xla_distribution_links table stores the link between
Transactions and Subledger journal entry lines.
In a system pkg has the following structure of the interpolation statements, so you can clearly draw a link between several tables:

Insert All When
  (Line_Id = 1) Then
Into Xla_Transaction_Entities
  (Upg_Batch_Id ,
   Entity_Id,
   ……)
Values
  (……) Into Xla_Events
  (Upg_Batch_Id, Entity_Id, Event_Id, ……)
Values
  (……)
Into Xla_Ae_Headers
  (Upg_Batch_Id,
   Entity_Idnt_Id ,
   Ae_Header_Id  ,
   ……)
Values
  (……) When
  (1 = 1) Then Into Xla_Ae_Lines
  (Upg_Batch_Id, Ae_Header_Id, Ae_Line_Num, ……)
Values
  (……)
Into Xla_Distribution_Links
  (Upg_Batch_Id ,
   Event_Id,
   Ae_Header_Id,
   Ae_Line_Num ,
   ……)
Values
  (……)
  Select …… From ……;

The gl_import_references.gl_sl_link_id field mentioned above is associated with the xla_ae_lines.gl_sl_link_id field in which the fields associated with the submodule are Xla_transaction_ In entities, the table has the following form of field
Source Table Association Fields
Source_id_int_num the corresponding source ID for example: Ap_invoices_all
Source_id_char_num
These two fields are used to associate with the source module, and the field's settings are in each sub-module:
Path is: Settings, ledger account settings, sub ledger accounting settings, accounting method builder, event model
The interface is as follows: The entity code for the interface is the Entity_code field in the corresponding Xla_transaction_entities table, and each source indicates which module generated the sub-ledger.

Click [Mark] to enter the following interface: The interface of the Entity table column field when the sub-module related source table field, the indicator column is the SLA

Xla_transaction_entities The table columns, the SLA and the word module are linked by the settings of the interface.

Security Control Fields
Security_id_char_num
Security_id_int_num
These two fields are used for security verification, and data masking is used

Xla_transaction_entities is a table with VDP validation, and the policy functions used by each module are

Set up by setting >> ledger account Settings >> subledger ledger accounting settings >> subledger app Products

If the AP module is using: Xla_security_policy_pkg. Mo_policy, it's important to have a sentence in this method.

View Code

The General MOAC
VPD uses a field org_id where org_id is replaced with security_id_int_1, it's obvious
Now that we have analyzed the relationship between GL and SLA and the SLA and word modules, it is easy to organize the corresponding table relationships in the above interpolation code.
Out of the common retrospective relationship, there may also be special cases not involved.

Here is an example:

First Look at * * This side of the allocation, which corresponds to the table in the SLA xla_distribution_links

In this case, you can query the details of each accounting account, which is the allocation of ap**

Select Aid.Invoice_Id,
        Aid.Invoice_Distribution_Id distribution ID,
        Aid.Invoice_Line_Number line number,
        Distribution_Line_Number,
        Aid.Rcv_Transaction_Id receives the transaction id,
        Aid.Amount amount
   From Ap_Invoice_Distributions Aid, Ap_Invoices Api
  Where Api.Invoice_Id = Aid.Invoice_Id
   -And Aid.Invoice_Id = 35357
  Order By Aid.Invoice_Line_Number, Distribution_Line_Number;

Begin
   mo_global.init (‘M’);
   End;

-- And Xal.Ae_Header_Id = 14012  
   And Xdl.Application_Id = 200
 Order By Xdl.Source_Distribution_Id_Num_1;
 
Begin
  mo_global.init(‘M‘);
  End;

Analysis: Accounts in a payables distribution generate two rows of data in the SLA, and the two lines of data borrow the opposite amount.

From the GL check row data, the line under one header in GL may be a merged row, so the line of GL does not accurately represent account information from the account source, and the same account line in GL may correspond to the account information of the AP side many * *, However, each line of the Subledger journal entry line in the accounting line must correspond to the same * *.

The following code is the collation of the general code of traceability

Select ‘_ ^ _’ Key,
       Gir.Je_Batch_Id,
       -====== xla_transaction_entities ========= --------
       Xte.Application_Id application,
       Xte.Entity_Id,
       Xte.Ledger_Id ledger sob,
       Xte.Entity_Code,
       Xett.Name transaction entity type,
       Le.Name legal entity,
       Le.Legal_Entity_Identifier income tax registration of the person,
       
       ------------------------------
       / * --AP_INVOICES
        Ap ** Invoice_Id
       
       --AP_PAYMENTS
        Ap payment Check_Id
       
       --RECEIPTS
        Collection
       
        Cash_Receipt_Id
       
       --TRANSACTIONS transaction processing
        Sales ** Customer_Trx_Id
       ,
       Xte.Source_Id_Int_1 transaction source corresponding ID,
       ------------------------------
       / *-The following two fields will have values when depreciated
       , xte.source_id_int_2
       , xte.source_id_int_3 * /
       Xte.Security_Id_Int_1 Org_Id,
       Xte.Source_Application_Id source corresponding application,
       -====== xla_event ========== --------
       Xe.Event_Type_Code, --Event type code
       Xent.Name event type,
       Xe.Event_Status_Code, --Event status code
       Xe.Process_Status_Code, --Processing status code
       / * This flag indicates whether the event is on hold or not.
       
       possible values: (Y)-yes, (N)-No * /
       Xe.On_Hold_Flag,
       -================
       Xah.Ledger_Id Sob,
       Xah.Je_Category_Name, --General Ledger category name
       Xah.Accounting_Date,
       Xah.Period_Name period,
       
       / *, xah.balance_type_code --Balance type (Actual, Budget, or Encumbrance)
       , xah.gl_transfer_date
       , xah.accounting_entry_status_code
       , xah.accounting_entry_type_code
       , xah.zero_amount_flag * /
       -================
       
       Xal.Ae_Line_Num line number,
       Xal.Code_Combination_Id account id,
       Gjl.Code_Combination_Id journal gcc,
       / *, xal.gl_transfer_mode_code
       , xal.accounting_class_code "Accounting Classification" * /
       Xlp.Meaning Accounting Classification,
       Xal.Accounted_Dr credited debit currency,
       Xal.Accounted_Cr credit credit currency,
       Xal.Currency_Code currency,
       Xal.Entered_Dr account original currency debit,
       Xal.Entered_Cr account original currency credit,
       Gir.Je_Line_Num journal line number,
       Xte.Entity_Id,
       Xte.Application_Id,
       Xe.Event_Id,
       Xah.Ae_Header_Id,
       Xal.Ae_Line_Num
  From Xla_Transaction_Entities Xte,
       Xla_Entity_Types_Tl Xett,
       Xle_Entity_Profiles Le,
       Xla_Events Xe,
       Xla_Event_Types_Tl Xent,
       Xla_Ae_Headers Xah,
       Xla_Ae_Lines Xal,
       Xla_Lookups Xlp,
       Gl_Import_References Gir,
       Gl_Je_Lines Gjl
 Where 1 = 1
   And Xte.Entity_Id = Xe.Entity_Id
   And Xte.Application_Id = Xe.Application_Id
   And Xte.Legal_Entity_Id = Le.Legal_Entity_Id (+)
   And Xah.Event_Id = Xe.Event_Id
   And Xah.Application_Id = Xe.Application_Id
   And Xent.Event_Type_Code = Xe.Event_Type_Code
   And Xent.Application_Id = Xe.Application_Id
   And Xent.Language = ‘ZHS’
   And Xah.Ae_Header_Id = Xal.Ae_Header_Id
   And Xah.Application_Id = Xal.Application_Id
   And Xlp.Lookup_Type (+) = ‘XLA_ACCOUNTING_CLASS’
   And Xlp.Lookup_Code (+) = Xal.Accounting_Class_Code
   And Gir.Gl_Sl_Link_Id = Xal.Gl_Sl_Link_Id
   And Gir.Gl_Sl_Link_Table = Xal.Gl_Sl_Link_Table
   And Gjl.Je_Header_Id = Gir.Je_Header_Id
   And Gjl.Je_Line_Num = Gir.Je_Line_Num
   And Xett.Entity_Code = Xte.Entity_Code
   And Xett.Application_Id = Xte.Application_Id
   And Xett.Language = ‘ZHS’
   And Gir.Je_Batch_Id = 5511
   ; 

Oracle Ebs R12 SLA vs. GL Relationship Changes