Query to find all APPLICATION (module) information

 

-------------------------------------------------------------------------------

-- Query to find all APPLICATION (module) information

-------------------------------------------------------------------------------

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name",

       fa.basepath                 "Basepath"

  FROM fnd_application     fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   -- AND fat.application_name = 'Payables'  -- <change it>

 ORDER BY fat.application_name

SQL To Extract the Account Derivation Rules (ADR) In OPM SUbledger Accounting (SLA) (Doc ID 1929297.1)

 Select on ADRs and conditions

1)


select * from xla_seg_rules_b
where segment_rule_code = '&SegmentRuleCode';

'Segment Rule Code' is the value displayed in the 'Rule Code' field in the Accounting Derivation Rules form


2)
select * from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode';


3)
select * from xla_conditions
where segment_rule_detail_id in (select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code =
'&SegmentRuleCode';


4)
select * from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode'));


5)
select * from fnd_flex_value_sets
where flex_value_set_id in
(select flex_value_set_id
from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode')));


6)
select * from fnd_flex_validation_tables
where flex_value_set_id in
(select flex_value_set_id
from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode')));
 

R12 Subledger Accounting (SLA)

What is Subledger Accounting? 

#Subledger Accounting is a Service, not an Application. 

#There are no SLA responsibilities and there is no direct login to SLA. 

# # SLA forms and programs are embedded within standard Oracle Application responsibilities(e.g. Payables Manager). 

=> Simply, It is a rule-based accounting engine, toolset & repository supporting Oracle E-Business Suite modules. 

=> Allows multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements. 

=> Retains the most granular level of detail in the subledger accounting model, with different summarization options in the General Ledger, allowing full auditability and reconciliation. 

=> Introduces a common data model and UI across subledgers, replaces various disparate 11i setups, providing single source of truth for financial and management analysis. Screen shots of sample Invoice Distribution and It's Accounting Journal Entries 

Simple Illustration on Journal Line Creation in SLA 

Please go thorough the AMB components explained below to understand more about this picture.

What is Accounting Methods Builder(AMB) in SLA? 

A set of screens which provides flexibility to create your own subledger accounting set up or use seeded subledger accounting setup. 

You can use the AMB to define the way in which subledger transactions are accounted. This enables you to create and modify subledger journal line setups and application accounting definitions. These definitions define the journal entries that enable an organization to meet specific fiscal, regulatory, and analytical requirements. These definitions are grouped into subledger accounting methods and assigned collectively to the ledger. 

Following picture shows hierarchy of the components in the AMB. 

 

# Each ledger is assigned with SLAM. 

# Subledger accounting method(SLAM) which tells what type of accounting method you are using(Ex: Cash or Accrual) in your ledger. 

# Under SLAM, you will find set of Application Accounting Definitions(AAD) for different subledgers(Ex: Payables, Receivables) 

# Under AAD, you will find set of journal line definitions(JLD) for each Event Class(Ex: Invoices, Prepayments, Payments) and Event Type(Ex: Invoice Validated, Prepay Application, Payment Created) combination. 

# Each journal line definitions(JLD) holds group of JLT's(Ex: Name it Liability, Item Expense, Gain, Loss, etc), ADR(rules), JED(description) for each Event Class and Event Type combination. 

# Journal Line Types(JLT) contains.. 

1. Accounting Attributes(Ex: Accounting Date, Entered Amount, Accounted Amount, Party Id, etc ) 

2. Basic Info which determines journal line properties( Ex: Side(Cr or Dr), Balance Type(Actual or Encum), TransferToGL(Summary or Detail), etc) 

3. Conditions(This condition need to be satisfied to use this JLT) 

# Account Derivation Rules(ADR) (Ex: Accounting segment values. It is nothing but GL account) 

# Journal Entry Descriptions(JED): Which give more information about transaction (Ex: Invoice/Check details) 

Navigation Paths to SLA Forms

Explain AMB Components? 

Event Model(Definition of the subledger transaction types and lifecycle) 

Event Entities 

Group event classes into technical transaction models called event entities. For example, group the event classes Invoices and Prepayments into the event entity Invoices because both classes of transaction are stored in the Payables invoice transaction table (AP_INVOICES_ALL). Event entities enable you to treat events for a single transaction model in the same way. The event entity often logically corresponds to a single document used as a basis for several related transactions. 

Event Class 

Group accounting event types into user-orientated transaction categories called event classes. For example, group the event types Invoice Approved, Invoice Adjusted, and Invoice Canceled into the event class Invoices. Then assign AMB components, such as journal line types, by event class within the application accounting definition. This assignment simplifies setup when the accounting requirements for all event types in a class are the same. Also, sources assigned to an event class are available for the accounting of all event types in that event class. 

Example Payables: 

Invoice, Debit Memo, Prepayment, Payments, Refunds 

Receivables: Invoice, Deposit, Receipt, Bill Receivable 

Event Type 

Each accounting event should be represented by an accounting event type. These types are registered in the AMB. When subledger journal entries need to be created, the event type determines which application accounting definitions should be used to process the accounting event. Application accounting definitions created in the AMB determine the lines, descriptions, accounts, and other elements of subledger journal entries.

Example 

AP Invoice Events: Validated, Adjusted, Cancelled 

AR Receipt Events: Created, Applied, Unapplied, Updated, Reversed 

Subledger Accounting Method(SLAM) 

The subledger accounting method(SLAM) is a collection of accounting definitions for all the applications that you will be generating accounting for. Each primary and subledger level or adjustment secondary ledger is associated with a SLAM, which determines the accounting rules and standards that will be applied when generating entries for that ledger. 

Example: 

Standard Accrual, Standard Cash, etc

Application Accounting Definition(AAD) 

Use Application Accounting Definitions(AADs) to assign journal line definitions and header descriptions to event classes and event types. AADs must be included in a subledger accounting method and assigned to a ledger. You can group accounting definitions from multiple products, such as Oracle Payables, receivables Assets into a single accounting method. 

Journal Line Definition(JLD) 

Journal line type, description, account derivations rules grouped together as a journal line definition to create the rule for particular event type.

Journal Line Type(JLT) 

-Identify the natural side: Debit, Credit, Gain/Loss 

-Determine the accounting class 

-Set under which conditions the rule will create a line 

-Define the values needed for entry line generation, such as amount, currency, conversion rate information 

-Control behavior for certain features i.e. multi period accounting, business flows, line merging and summarization 

Account Derivation Rules(ADR) 

Account derivation rules are used to determine the account combinations for subledger journal entries. You can define various rules in te AMB to determine how a journal entry account is derived. You can derive accounts segment by segment or as a complete account combination. This picture shows an Account Derivation Rule with conditional logic. If the condition holds for priority 1, then this source (Invoice Liability Account) is used. If not, SLA uses the source for priority 2(If it is available). 

Journal Entry Description 

This is useful in finding the actual transaction object details(Ex: Invoice/Payment details from journal line) 

Transaction Object 

Example for transaction objects: 

ap_invoice_extract_details_v.xdf 

ap_invoice_extract_header_v.xdf 

ap_payment_extract_details_v.xdf 

ap_payment_extract_header_v.xdf 

ap_prepayapp_extract_details_v.xdf 

ap_system_parameters_extract_v.xdf 

Transaction Object is nothing but a view which fetches all transaction information required to create journal line for particular event class. AP_INVOICE_EXTRACT_HEADER_V, AP_INVOICE_EXTRACT_DETAILS_V are transaction objects for event class Invoices. So, accounting for all invoice type events get transaction information from these transaction objects.

Sources Each column in the transaction object is defined as Source in the AMB. AMB uses these sources to get transaction information from Transaction Objects

Accounting Attributes 

Sources are mapped with Accounting Attributes. Accounting Attributes are bridge between JLT and Sources. 

Example 

GL Date, Entered Currency Code, Entered Amount, Accounted Amount, Conversion Rate Date, Conversion Rate Type, Conversion Rate, Distribution Type, Party Type, Party Identifier, Party Site Identifier

What are the important tables in SLAAccounting? 

The XLA_EVENTS table stores records for accounting events generated by subledger applications. Each product team populates this table by calling Subledger Accounting API and the respective product team will decide when this table is to be populated during the transaction life cycle. 

The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers. 

The XLA_DISTRIBUTION_LINKS table stores detailed distributions for journal entries. This table stores the data at most granular level and represents data contained in respective subledger product’s distribution tables. The detailed distributions stored in this table are merged into accounting lines and stored in XLA_AE_LINES table. Subledger Accounting uses this table for processing reversals and business flows. 

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. This table will store at least one row for debit and one row for credit for each accounting entry created. If multiple debit or credit journal entry lines exists for any specific event type and if the journal line type allows merge matching lines then these lines will be merged into single line. The unmerged granular level of detail for each accounting line will be available in XLA_DISTRIBUTION_LINKS table. 

What are the Accounting Methods seeded in SLA? 

Standard Accrual 

Standard Cash 

Encumbrance Accrual and Encumbrance Cash 

United States Federal 

China Standard Accrual 

What are the reports available in SLA? 

Journal Entries Report 

Account Analysis Report 

Third Party Balances Report 

Period Close Exceptions Report 

Open Account Balances Listing 

Period Moving Average Cost(PMAC)

 

Multi - Org Concept in Oracle Apps R12

Multi-Org in simple term means the implementation of multiple business units (or Organization) under a single installation of Oracle Applications. The concept of Multi-Org will manage the operations of an enterprise which has got subsidiaries across globe under a single oracle apps window, taking appropriate care of data security and data maintenance. Below are some of the features of multiple organization functionality.

• Any number of Business Units in an Enterprise can be supported within a single installation of Oracle Application

• User can access the data corresponding to and limited to the operating unit

• Reporting can be managed at different organization levels like, Business Group, Ledger, Operating unit etc

• Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same Party Can be Performed through Different Organization and can be managed internally through inter company postings

A real time organization construct in R12

Here in this example construct, CCS Company has organization structure as follows

1. 1 Business Group - Which controls the organization in America and Australia
2. 2 Legal Entities - one in US and one in AU
3. 2 Primary Ledgers - one in US and one in AU
4. 2 Operating Units - one in US and one in AU
5. 3 Inventory Organizations – two in US and one in AU

How Organization Hierarchy flow in Oracle R12

Multi-Org and Multi-Org Access Control in R12 (MOAC)

Prior to R12, user has to switch between responsibilities to enter transaction and for doing other activities for a particular organization. This is very time consuming to do activities in an environment like this if you have 100 operating units. To overcome this factor, oracle has introduced a new feature in R12 which allow the user to switch the organization from the same responsibility which enables the user to access different organization and its data from a single responsibility. 

To achieve the new objective, Oracle has introduced new functionality called Multi-Org Access Control (MOAC) in release 12. Following are the set up steps needs to follow for implementing the MOAC architecture for a particular application

Multi – Org Setup Steps in R12

1. Define Location

Open HRMS Manager Responsibility and navigate to Work Structure à Location

Define your location Specific Details (BG Address and time zone) for your Business Group in ‘Address Details’ tab and Shipping details in ‘Shipping Details’ tab as below.

Save the Changes

Following table will hold location details

 HR_LOCATIONS_ALL  

2. Define Business Group

Open HRMS Manger responsibility and navigate to

Work StructureàOrganizationàDescription

Enter the business group name and assign the location created in the previous step and save the changes

Select the LOV as business group under the ‘Organization Classification’ block

Select ‘Business Group from the LOV and check ‘Enabled’ check box for the business group name and save changes

Now click on ‘Others’ button and select the Business Group info from the additional window and click OK, then another window will get opened with name ‘Additional Organizations Information’ like below

Press TAB and enter the mandatory details like below

Click OK Button, then you will be prompted to save the changes. Press on YES button. The details will get saved.

Following Table will hold business group information

HR_ALL_ORGANIZATION_UNIT

3. Create Legal Entity

As per the structure defined, we have to create the one legal entity for AU operation.

 Switch the responsibility to General Ledger and navigate to the following

 SetupàFinancialsàAccounting Setup ManageràAccounting Setups

 In Release R12, the legal entity setup is a part of accounting setup.

Press button ‘Create Legal Entity’ and enter the Details

Click on ‘Create New Address’ to create the new address

Click ‘APPLY’ to save the changes.

Legal Entity details will be available in the following table

 XLE_LE_OU_LEDGER_V

4. Define Ledger

Navigate to Accounting Setup Manager and define the New Ledger for the Legal Entity created

Ledger Details Will be available in the Following table

 GL_LEDGERS

5. Create and assign Operating units to Legal Entities to Ledger

In this step we will assign operating unit to Legal Entity. This will be defining from General Ledger responsibility

Navigate to Setup –> Financials –> Accounting Setup Manager –> Accounting Setups

In the Ledger Definition window, enter the Ledger name and press GO button. Once the search finishes and resulted with the Ledger name, Click on ‘Update Accounting Options’ button

Then Assign the Legal Entity to the ledger by clicking ‘Add Legal Entity’ button

Search for the legal entity created in our previous step and click on apply button to save the changes. Now the legal Entity is assigned to Ledger

Now click on Update button next to the operating unit setup option

Click on Add operating unit

Enter the details. Assign the business group and legal entity created from the above steps to the operating unit and click on apply button

After completing all ledger option press ‘Complete’ button to complete the accounting setup

6. Create Inventory Information

Switch to Human Resource responsibility and Navigate to the following

 Work Structure àOrganizationàDescription

 Click on New Button to create the new Inventory Organization

From the Organization Classifications Frame, select the option Inventory Organization from the LOV.

Press OK to save the Details

No click on ‘Others’ button and select ‘Accounting Information’ option. A small window will open, Press TAB to enter the details in this window

Assign the following to the inventory organization we have created in our previous steps

Primary Ledger

Legal Entity

Operating Unit

Click OK and Click Yes to save the changes

Again Click ‘Others’ button and select the ‘Inventory Information’ from the list

Then add all inventory details and save the changes.

Once the setup is done, run the following reports/programs to use the operating unit we created

In order to use the operating unit, run the following program and it should be run for all the new operating unit structure

Switch the responsibility to ‘System Administrator’

Program :- Replicate Seed Data

Parameter: - <Operating Unit>

Run the following program

Multi-Org Setup Validation Report

Now the operating units and other related setups are ready to use. Now we have to think how we can enable the multiple organization can be enabled from a single responsibility.

Enabling Multi – Org Access Control (MOAC)

MOAC is implemented in R12 to allow the users to submit requests and access data of different operating units in a single responsibility. This functionality can be done by setting the SECURITY PROFILES under HRMS module

There are 2 security profiles:

• SECURITY PROFILE:  is used for the selection of operating units from the same business group       
• GLOBAL SECURITY PROFILE: is used for the selection of operating units from the different business group

Set up of Multi-Org Access Control:

• Setup Security Profile in HRMS

Switch the responsibility to Human resource

Navigate to the following

Security àProfile

Select the name for the profile and attach the business group created

In order to have access to the security profile we created, we need to create a responsibility and assign this profile option to the responsibility

Switch to System Administrator responsibility and navigate to the following

SecurityàResponsibilityàDefine

Once the responsibility is defined, assign the new security profile option to the responsibility by navigating the following

Profile à System

This is it!!! 

We have done with the setup of Multi-Org.  This is a key functionality in Oracle R12 which is serving as the stepping stone to Oracle Multi-Org implementation.

Following SQL query can give the relation between Ledger, Legal entity and Operating Units in Oracle Apps R12

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,

       lep.legal_entity_id, lep.NAME legal_entity,

       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,

       hrl.location_id,

       hrl.location_code,

       glev.FLEX_SEGMENT_VALUE

  FROM xle_entity_profiles lep,

       xle_registrations reg,

       hr_locations_all hrl,

       hz_parties hzp,

       fnd_territories_vl ter,

       hr_operating_units hro,

       hr_all_organization_units_tl hroutl_bg,

       hr_all_organization_units_tl hroutl_ou,

       hr_organization_units gloperatingunitseo,

       gl_legal_entities_bsvs glev

 WHERE lep.transacting_entity_flag = 'Y'

   AND lep.party_id = hzp.party_id

   AND lep.legal_entity_id = reg.source_id

   AND reg.source_table = 'XLE_ENTITY_PROFILES'

   AND hrl.location_id = reg.location_id

   AND reg.identifying_flag = 'Y'

   AND ter.territory_code = hrl.country

   AND lep.legal_entity_id = hro.default_legal_context_id

   AND gloperatingunitseo.organization_id = hro.organization_id

   AND hroutl_bg.organization_id = hro.business_group_id

   AND hroutl_ou.organization_id = hro.organization_id

   AND glev.legal_entity_id = lep.legal_entity_id