Customize SLA Oracle Appication

 Subledger Accounting (SLA) is a dynamic feature of Oracle® R12 that you can use for meeting the accounting needs of the business.

Overview

By using SLA, you can easily modify all the accounting requirements by following a few steps. In this post, I offer five easy steps to customize SLA. By implementing these steps, you can modify the accounting process according to your business needs.

Business Requirement

To illustrate with an example, assume Company ABC Ltd. has included a company, account, and cost center in their chart of account structure, and they use standard accrual at the ledger level for their accounting method.

When they enter a standard invoice in the payables module, SLA generates the following standard accounting entry:

Expense: 001.10100.0001.00000 DR (Manually entered combination)
Liability:  001.20000.0000.00000 CR (Derived from the Supplier/Supplier Site or Financial Options)

The business’s finance department wants the process to pick the Cost Center Segment from the Expense Account entered at the invoice distribution level. Thus, the third segment’s value should be changed from 0000 to 0001 on the Liability account combination.

Solution

To fulfill this business requirement, you need to customize SLA to implement the desired accounting process by using the following steps:

STEP 1: CREATE A NEW ACCOUNT DERIVATION RULE (ADR)

Select Payables > Setup > Accounting Setups > Subledger Accounting Setup > Accounting Method Builder > Journal Entry Setups > Account Derivation Rules.

The rule explains that SLA should pick the value from the standard Oracle source, Invoice Distribution Account, for the COST CENTER segment.

STEP 2: CREATE A JOURNAL LINE DEFINITION (JLD)

A JLD is a set of all the Journal Line Types (JLT) defined for a specific Subledger module.

1. Select Payables > Setup > Accounting Setups > Subledger Accounting Setup > Accounting Method Builder > Methods and Definitions > Journal Line Definitions.

2. Copy the Standard Oracle JLD and create a new JLD, as shown in the following example:

   

3. After you copy the definition, add the ADR rule you created for the COST CENTER segment in Step 1 and save the definition.

Because the example is on the Liability account, you will use the JLT as a Liability, Basic.

STEP 3: CREATE AN APPLICATION ACCOUNTING DEFINITION (AAD)

An AAD is a set of Event Classes or Event Types and JLDs.

1. Select Payables > Setup > Accounting Setups > Subledger Accounting Setup > Accounting Method Builder > Methods and Definitions > Application Accounting Definitions.

2. Copy the Standard Oracle AAD and create a new AAD, as shown in the following example:

   

3. After you copy the definition, add the JLD created in Step 2 and save the definition. You can individually attach the JLD to all the Event Classes. For this session, you can use Event Class as Invoices.

STEP 4: CREATE A SUBLEDGER ACCOUNTING METHOD (SLAM):

A SLAM is a group of AADs defined for the Subledger modules.

1. Select Payables > Setup > Accounting Setups > Subledger Accounting Setup > Accounting Method Builder > Methods and Definitions > Subledger Accounting Method.

2. Copy the standard Oracle SLAM (Standard Accrual) and create a new SLAM, as shown in the following image.

   

3. After you copy the definition, add the AAD you created in Step 3 against the Payables application and save the definition.

4. To run the Validate Accounting Definitions program for Payables, select Payables > View > Request > Submit a New Request.

STEP 5: ATTACH THE SLAM TO THE LEDGER

1. Select Payables > Setup > Accounting Setups > Ledger Setup > Define > Accounting Setups.

   

2. Attach the SLAM created in Step 4 and complete the Ledger setups.

Validation

To validate your SLA setup, create a new invoice, validate it, and run the Create Accounting program.

You can now see the change: The Cost Center value has been changed from 0000 to 0001 for the Liability String.

Expense: 001.10100.0001.00000 DR (Manually entered combination)
Liability:  001.20000.0001.00000 CR (Derived from the SLA)

Conclusion

In R12 Oracle, SLA is one of the most well-organized methodical approaches to confront the accounting needs that various businesses require. Understanding the requirement is the key to this process. After you have a plan ready, you can effortlessly customize the SLA by using the steps in this post to meet your business requirements by customizing SLA.

Value Sets: Related Tables and Query to Fetch the Details

Value Sets are used in Oracle to define a limited sets of values for a particular field. There are 3 main tables in Oracle Fusion to fetch the values assigned to a value set. Here is a brief description of them:

1. FND_VS_VALUE_SETS: Table to hold the value set information ( Header level data).

2. FND_VS_VALUES_B: Table to hold the values of the value set.

3. FND_VS_VALUES_TL: Table to hold the description of the values.

Now let’s cut to the chase and see the query. Pretty simple it is:

select 

vsval.VALUE_ID,

vsval.VALUE_SET_ID,

vsval.VALUE,

vsval.description,

vt.description,

vsval.flex_value_attribute3,

vsval.CREATION_DATE,

vsval.CREATED_BY,

vsval.LAST_UPDATE_DATE,

vsval.LAST_UPDATED_BY

from  

fnd_vs_value_sets vset,

fnd_vs_values_vl vsval,

FND_VS_VALUES_TL vt

where 

1=1

and vset.value_set_id = vsval.value_set_id

and vsval.CREATION_DATE >= (:pcreationdate) and  vsval.CREATION_DATE <= (:pEnddate) 

and vset.VALUE_SET_CODE = 'Project CCC COA'

AND vt.value_id = vsval.value_id

and vt.language = 'AR'

order by vsval.value

 

GL_BALANCES

1. Summary

GL_BALANCES contains actual, budget, and encumbrance balances for detail and summary accounts.

This table stores ledger currency, foreign currency, and statistical balances for each accounting period that has ever been opened.

ACTUAL_FLAG is either ‘A’, ‘B’, or ‘E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ‘E’, then ENCUMBRANCE_TYPE_ID is required.

2. Balances Calculations

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns.

 The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year-to-date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR.

Example

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0),’Liability’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),null) “Ledger Debit Amount”,

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0),’Liability’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),null) “Ledger Credit Amount”,

3. Foreign Currency Transactions or Journals

Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ‘R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

Example

For US Company whose functional currency is USD, and if transaction (AP Invoice) is entered is in INR. Here entered Dr and Entered Cr are in INR and Accounted Dr and Cr is in USD.  (Context is for Liability account)

In GL Balances there will be two rows for this liability account, one for currency INR and other for currency USD.

A row with INR (Foreign Currency) will have TRANSLATED_FLAG set to ‘R’. For foreign currency (INR) rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance (USD).

A row with USD (Functional Currency) will have TRANSLATED_FLAG set to NULL

4. Foreign Currency Translation

Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ‘Y’ or ‘N’. ‘N’ indicates that the translation is out of date (i.e., the account needs to be re-translated). ‘Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

The columns that end in _ADB are not used. Also, the REVALUATION_STATUS column is not used.

What is FlexField Value Set Security Oracle Apps R12.2 (EBS)

 What is FlexField Value Set Security

• FlexField Value Set Security is on the definition or setup side of Value Set Values

• This is the ability to restrict who can view, add or update Value Set Values

• Specifically who can use the Segment Values form (FNDFFMSV) for which specific Value

Sets

• This functionality started in R12.2.2

Quick Simple Fix

• There is a way to get back to the pre-R12.2 functionality quickly

• This is a slam dunk approach, it does not take advantage of the new functionality, but it gets you back in business

quickly with the old functionality

• This quick fix can be undone later when you are ready to tackle this properly

Steps

1) Need to login as SYSADMIN

2) Select the "User Management" responsibility

3) Navigate to "User”

4) Find the particular user then click on update icon

5) Click on "Assign Role”

6) Select "Role" as search by and enter value as "Flexfield Value Set Security: All privileges”

Or

Select "Code" as search by and enter value as"UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE"

7) Select the role from result

8) Enter a justification text.

9) Click on "Apply”

10) Clear the cache from functional administrator responsibility if required.

Creating a Customer in Oracle Fusion Applications

 

This article will show you how to create a Customer in Oracle Fusion Applications. 

First, navigate to Receivables > Billing from your Welcome Springboard:

 

On the Billing screen, click on the Task Panel, and click on the "Create Customer" function:

Fill up the required fields such as:

·                Customer Type - You have two options: Organization or Person. Choose Organization if this Customer is an Entity, and Person if this Customer is a direct individual.

·                Name - The Identifying name of the Customer

·                Registry ID - This is the Auto generated Customer ID as based on the Trading Community Architecture

·                Account Number - This is the Auto generated Customer Number that Oracle uses internally

·                Account Type - You have two options: External (for external customers) or Internal (usually used for Subsidiaries)

 

 

On the Address section, fill up the fields such as Site Name and the appropriate Address.

 

 

 

On the Address Purpose section, make sure to add the "Bill To" and "Ship To" purpose as these are the two most important purpose of a Customer Site.

 

 

Click on "Save and Close" to save your work.

 

 

To review and edit your inputs, you may search your newly-created customer from the "Manage Customers" screen:

 

 

Go to the Customer's Site and on the "Profile History" tab, click on the "Create Site Profile" button:

 

 

On the "Create Site Profile" screen, choose the appropriate Profile Class to auto-populate most of the fields on the Customer, and change other fields that are not applicable. Click on "Save and Close" to save your work.