Subledger Accounting Query to download all SLA Setups - for your BR-100 documentation

Here is a SQL that can be used to download all the information on the Subledger Accounting Setups from Application Accounting Definition to Account Derivation Rule




-- This SQL will pull all the details of Application Accounting Definition all the way from AAD name to ADR (Account Derivation Rule).
-- Two of the Oracle Forms are captured in this query


SELECT distinct A.PRODUCT_RULE_CODE     AAD,
                A.EVENT_CLASS_CODE      Event_Class,
                A.EVENT_TYPE_CODE       Event_Type,
                A.HEADER_DESC_NAME,
                B.line_definition_code  JLD_Code,
                B.Line_definition_name  JLD_Name,
                B.line_definition_desc  JLD_Desc,
                A.HEADER_DESC_TYPE_DSP,
                T.line_definition_code  JLD_CODE,
                T.accounting_line_name  Journal_Line_Type,
                T.description_name      Journal_Line_Description,
                T.accounting_line_code,
                N.segment_rule_name     ADR_NAME,
                N.segment_rule_code     ADR_CODE,
                N.segment_rule_owner,
                N.segment_rule_desc     ADR_DESCRIPTION,
                N.flexfield_segment_num
  FROM apps.xla_prod_header_desc_fvl     A,
              apps.xla_aad_line_defn_assgns_f_v B,
              apps.xla_line_defn_jlt_assgns_f_v T, -- To get all the details of Journal Line Type
              apps.xla_line_defn_adr_assgns_f_v N -- To get all the details of ADR
 WHERE (A.APPLICATION_ID = 140) -- Application_id from FND_APPLICATION.
   and (A.AMB_CONTEXT_CODE = 'DEFAULT')
   and (A.PRODUCT_RULE_TYPE_CODE = 'C')
  -- and (A.PRODUCT_RULE_CODE LIKE 'XXFA_ASSET_AAD')-- If you want Product rule code --specific, write the name here
  -- and (A.ENTITY_CODE = 'TRANSACTIONS') -- IF you want to use the query for just a --particular entity,
   and A.product_rule_code = B.product_rule_code
   and A.application_id = B.application_id
   and A.amb_context_code = B.amb_context_code
   and A.event_class_code = B.event_class_code
   and A.event_type_code = B.event_type_code
   and T.application_id = 140 -- Application_id from FND_APPLICATION.
   and T.application_id = B.application_id
   and T.event_class_code = A.event_class_code
   and B.line_definition_code = T.line_definition_code
   and B.event_class_code = T.event_class_code
   and B.line_definition_code = T.line_definition_code
   and B.event_type_code = T.event_type_code
   and T.event_Class_code = N.event_Class_code
   and T.line_definition_code = N.line_definition_code
   and T.application_id = N.application_id
   and T.line_definition_code = N.line_definition_code
   and T.event_type_code = N.event_type_code
   and N.accounting_line_code = T.accounting_line_code
 order by 2, 3, 5, 10, 11, 16;

My Presentations/Panel at the COLLABORATE-14 Conference

April 7th 2014

1 to 2 PM Pacific

OAUG Oracle Business Intelligence - Big Data SIG Meeting (Customer Round Table)

Level 3, San Polo - 3506

April 8th 2014

6:45 PM Pacific

RadiOAUG powered by Business RadioX

 OAUG Membership Booth #1039 (OAUG Customer Support Council)

April 9th 2014

 2 to 3 PM Pacific

 "How I wish I Knew SLA" - (Oracle Subledger Accounting)

Session ID : 14843

Level 1, Marco Polo - 803



Look forward to meeting you all on tomorrow (7th April 2014)

Regards
Lakshmi

How to view other User's Request in Oracle EBS R12

This is one of the interesting issues I came across during our EBS Upgrade from 11i to R12.1.3

In 11i, All the users can view other's concurrent requests through a simple Profile Option called  'Concurrent: Report Access Level'.  When we assign the above profile option value to Responsibility, all users logging into that Responsibility can view other's requests.

Ofcourse R12 has a lot more security and is based on role based access. Hence, that profile option to allow anyone to view other's requests at the responsibility level is Disabled.

So here is what you need to do to enable the same functionality in R12. I have provided step by step instructions on how to do that.

You will be using the UMX Role based Access Control (RBAC) to control who can view other User's requests.

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

Step 1:

Login as Sysadmin User (Not responsibility) and Navigate to User Management Responsibility, Click on Users Tab and search for the User Name and then assign Security Administrator, Functional Developer and Functional Administrator  roles to that user .

     Click On Update

  Click on Assign Roles and search for Security Administrator role and assign to the user.

When assigning the role, it will ask for justification. Provide a reason for justification and save.

You will get a confirmation that you have successfully updated the information, but may not see the changes in the application until after the Workflow Background engine's next run.

Assign Functional Developer and Functional Administrator roles to the same user. You need Functional developer role to create new roles.

PLEASE NOTE: User Management is an inherited role from Security Administrator role. User Management will only work if you have the Security Administrator role assigned to the user.

 

Step 2:

 The steps described in Step 2  is a one time step 

2(a) Creation of Object Instance Set Code

Login as Functional Developer and click on objects (You can do this either using Sysadmin user or your own user who has the Functional Developer role)

Click on objects tab, then search for 'concurrent requests' in the name and click go as shown below:

Click on Concurrent Requests link above (Not Update)

 Click on Create Instance Set and create a new instance set as follows. Make sure your object instance set code is a single word without space.



Enter the following as your predicate:
&TABLE_ALIAS.request_id in (select cr.request_id from fnd_concurrent_requests cr where cr.responsibility_id = fnd_global.resp_id and cr.responsibility_application_id = fnd_global.resp_appl_id)

 Click on Apply and save. 

You will get a confirmation that your object instance set is created successfully.

2(b)  Creation of Role and Grant

     (i)  Creation of Role

Now Login as User Management, Role and Role inheritance

Click on Create Role in the above screen and create a new role as follows:

Click on save, then Click on Create Grant

ii) Creation of Grant - 

Step 1 of 4

Click Next to go to the next step

Step 2 of 4

Click the Data context type as Instance set and search for your instance set you created a few minutes ago (in this case XX Dell View Other Requests)

Step 3 of 4

Click Next and in the screen select set as "Request Operations"

Step 4 of 4

Click Next
Click on Finish. You will get a confirmation as below.

 Now the Role to view other user's requests with the grant is fully created.
-----------------------------------------------------------------------------------------------------------------------

Step3:

This is the only step you will be repeating to assign other users the ability to view other's requests.  

a)  Login to User Management Responsibility.

Navigate to Users, Search for the user whom you are planning to assign the role to view other's requests.

b) Click on Update, and Click on Assign Roles

c)  Search for the Role that you created in Step 2 as shown below. Then click Select 

d) The form will ask you for Justification. Enter a justification and click on save.

Now the User Vishal_Pande has the access to view other user's concurrent requests. 

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


Hope you find this information useful. Please feel free to drop a note if you have any questions on this.

Oracle Releases R12.2 version for Oracle EBS (Much awaited Release)

Yesterday Oracle Corporation released Oracle E-Business Suite 12.2 - The next version of Oracle EBS 12.1.3.

We all have been waiting for this Release for quite sometime. One of the significant advantage of  EBS 12.2 is that it includes Online Patching. We don't need maintenance downtimes for applying any of our patches.

Also there are some significant enhancements across the entire E-Business Suite. For e.g. Oracle Financials Integration with Fusion Accounting Hub, Enhanced cost breakdown structure and labor costing with Oracle Projects, New subscription services solution with Oracle Order Management, Improved User Interface with Oracle Supplier Lifecycle Management etc.

Here is a link to Oracle's Release Announcement:

http://www.oracle.com/us/corporate/press/2016931


Thanks and Regards
Lakshmi

R12 Upgrade - Options for upgrading your historical data to Subledger Accounting Model (SLA)

If you are currently on Oracle EBS 11i (Financials or Order to Cash or Procure to Pay),  what options do you have for upgrading your historical data in 11i to Subledger Accounting Model? Hope this posting will clarify your questions.

First of all, let us understand what will 11i Upgrade process do with your Subledger Data.

By Default, it will upgrade 6 months of data. But the fact is, it is not always 6 months. Here is an example of how much data will be upgraded to the SLA model by default.

If you have more than six months of data in the current open fiscal calendar year, the current fiscal calendar start date is taken as the default start date for SLA conversion. If you have less than 6 months of data in the current open fiscal calendar, Upgrade automatically converts 6 months from the day the upgrade is run.

Also, it is really interesting to note that the entire AP data is converted to SLA model by default. Basically all your data from ap_accounting_events_all, ap_ae_headers_all and ap_ae_lines_all are upgraded to the respective SLA tables. Only the xla_distribution_links records for AP are migrated based on the xla_upgrade_dates and the accounting period in the gl_period_statuses tables.

So, what are your options to convert the rest of the data to SLA Data model? Hopefully the following picture will explain it all.

 So, you have a few options for upgrade

You can leave the default option or do a Pre-Upgrade or do a Post-upgrade using Hot Patch with Default or do a Post-upgrade using Concurrent Program (XLAONDEUPG) along with Default.

If you are using Project Accounting module and you are planning to convert the PA data to SLA datamodel, then you can't use the option 4 above. SLA: Upgrade Historical Subledger Transaction Accounting Program is not compatible with Project Accounting module.

On the other hand, there a lot of advantages with using the option 4. You can run this program by product, ledger or period. This gives a lot of flexibility to upgrade your historical data post-upgrade. Also, since this is run after the upgrade is completed, the actual upgrade time can be conserved.

If you are using Project Accounting module and would like to convert your entire PA data to SLA model, you have no option but to choose Option 2 or 3.  Option 3 is probably the best bet because, you are not forcing yourself to converting the entire data in one-go that too during upgrade like Option 2.  Here is a picture representing the various options you have to convert your SLA data during Upgrade.

COLLABORATE-13 Conference Highlights

Thank you OAUG, IOUG and Quest for a wonderful COLLABORATE-2013 conference in Denver, Colorado last week. It was an amazing experience as usual - Meeting all my friends, networking, knowledge sharing, Information Exchange etc.

Our OBISIG meeting was held on Sunday April 7th from 1:15 to 2:15 PM. Our SIG Meeting went very well. Glenn Hoormann from Ludus Inc, Our chairman for the OBISIG hosted the meeting. We had two customer panel sessions. Andrea Buckman from Humana shared her experiences about the OBIEE 11g upgrade details. I shared about the OBIEE landscape at Dell. There were a lot of questions from the audience. Very interactive session overall.

I attended a few more sessions and then comes the "Meet the leaders" kick-off by OAUG. It was great to get to know the OAUG board members and the various OAUG leaders. This is truly a unique User group. A group by the users for the users. Go OAUG, go! We had a fun filled event Welcome Reception at the Museum. Lots of knowledge sharing and networking there as well.


Monday morning started off with a key note from Aron Ralston, author, adventurer and the subject of the film, "127 Hours" who shared his perspectives gained from his harrowing experience of being pinned by a boulder for more than five days in a Utah canyon. It was a captivating speech.

I attended a lot of interesting Intercompany sessions and ADF sessions. We also had our OAUG customer support council meeting on the same day (IOUC Global support meeting). We had presentations from Oracle support. Oracle is trying to get feedback from customers through the Customer Advisory Panel. If you are an Oracle customer and are willing to provide your feedback through surveys, you can sign up at http://oracle.com/ORACLECAP

We had our OAUG Education committee meeting that evening. The objective of the Education Committee is to develop, implement and monitor educational offerings, separate from annual conferences, for OAUG members. It was a pleasure to get to know everyone from the OAUG Education committee in person.

Tuesday morning started off with a keynote from Mark Hurd, President of Oracle and Team Oracle Aviator Sean D. Tucker. Very interesting session.

I had my presentation on 'OIC at Dell: An Upgrade to Tell'  on Oracle Incentive Compensation. We had a very interactive session. Lots of interesting questions from the audience. I also talked through the Fusion co-existence strategy proposal especially Fusion OIC now has Quota management module which is not available in the R12 or other previous versions. I also went through some of the tips and tricks we learnt with the OIC module discovered during our POC sessions at Dell. Some of the audience came and told me that they learnt something very interesting and unique from my session - something they have never heard before. Lots of compliments. Overall it was a very interesting session.

I also participated in the Oracle Usability Feedback sessions this time. It was a really interesting and a unique way to participate in the 2 way communication between customers and Oracle.

Overall, I had a great conference. Thoroughly enjoyed it. Look forward to Oracle Openworld and other conferences.

Join me in Denver, Colorado at COLLABORATE - 13 - My presentations and Panel Agenda

1) 

Oracle Business Intelligence Special Interest Group (OBI SIG) Meeting

Date:
Sunday, April 7
1:15 pm - 2:15 pm
Location:
Room 111
Product Line:
Fusion Middleware, Oracle BI (OBIEE Suite), Oracle Business Intelligence Applications, Oracle Business Intelligence Enterprise Edition, Oracle Data Mining, Oracle OLAP
Description:
Overview of OBI SIG - Oracle BI Roadmap, Direction & Vision - Customer (3 total) Panel where customers explain their BI solutions and take questions from the audience.
Objective 1: Attendees hear from 3 large Oracle Business Intelligence customers about their BI Footprint.
Objective 2: Hear about BI challenges and value received from BI Investments
Objective 3: Attendees can ask questions of customers in RoundTable format.
Objective 4: Attendees hear from Oracle about the Direction, Vision and Roadmap of Oracle Business Intelligence.
Objective 5: Attendees get to network with customer speakers, Oracle and OBI SIG Board.

Speakers:

Andrea Buckman
Humana
Glenn Hoormann
Ludus Inc.
Lakshmi Sampath
Dell

2)

OIC at Dell - An Upgrade to Tell

Date:
Tuesday, April 9
10:45 am - 11:45 am
Location:
Room 506
Category:
-- Not Applicable --
Product Line:
Fusion Applications, Oracle E-Business Suite
Track:
Customer Relationship Management
Description:
This paper provides a comprehensive overview of the 11i to R12 Upgrade for the OIC module at Dell. It also delves into the functional overview of R12 Oracle Incentive Compensation (OIC) and its integration with Territory Manager and Resource Manager along with the undocumented secrets discovered and lessons learnt during our implementation at Dell. As an added bonus, this paper will also cover the aspects of co-existence strategy of R12 OIC with Fusion(Sales Planning module - Quota Management& Territory Man
Objective 1: Functional Overview of upgrade from 11i to R12 (Oracle Incentive Compensation, TM & RM)
Objective 2: Revealation of lessons learned and challenges faced during R12 (re)implementation
Objective 3: Setup & configuration of Multi-org concepts using MOAC; Role based access control
Objective 4: Comparison of OIC version 11.5.10, R12.1 and Fusion, new functionality and changes in features
Objective 5: step by step information on implementing R12 OIC ; its integration with other modules
Handouts:

1. Presentation
2. White Paper

Speaker:

Lakshmi Sampath
Dell

Look forward to a great conference. See you at Denver.

Regards
Lakshmi

Top 3 Reasons to consider R12 Upgrade

Based on 2012 OAUG (Oracle Applications User Group ) Survey on ERP Upgrade Economics,

As of 2012, About 46 percent of the OAUG survey respondents reported that they have already moved to the latest Oracle ERP solution – version 12.1 of EBS. Another 51 percent are still running version 11i (11.5.10). Also, 7 percent are running the latest version of PeopleSoft (9.1).  For many respondents, an upgrade is imminent. A total of 42 percent say that they are either in the process of an upgrade or planning to do so within the next 12 months. There is a segment,totaling 17 percent however, who say they either have no plans or aren’t aware of any plans.

ERP Upgrade Plans (as of 2012)

Among the segment of respondents who have no plans for an ERP upgrade, the main reason is a perception that their current systems are still adequate—as cited by about half. One-fifth are troubled by the lack of budget resources to execute an upgrade.

For those who have no plans for upgrading to R12 in the near future or those who are currently in the process of upgrading to R12, Here are some critical reasons to consider R12 Upgrade.

 

1) Oracle Support

EBS 11i is currently on Extended Support until Dec 2013. After that 11i will be on Sustaining Support. During Oracle OpenWorld 2012, Oracle announced an exception that will be provided for the first 13 months of Sustaining Support on Oracle E-Business Suite Release 11.5.10 (11i10), valid from December 1, 2013 – December 31, 2014.

This exception support will be comprised of components:

1. 1. New fixes for Severity 1 production issues,
   2. Critical Patch Updates (CPUs), up to and including the October 2014 CPU,*
   3. United States Form 1099 2013 year-end updates (for Oracle Financials/Accounts Payable product), and
   4. Payroll regulatory updates for the United States, Canada, United Kingdom, and Australia for each country’s fiscal year ending in 2014.

* POST OPENWORLD ANNOUNCEMENT: Exception for CPUs until October 2014 is newly added feature as of October 29, 2012. 

As per Oracle’s Lifetime Support Policy for Applications, E-Business Suite 11.5.10 is in Extended Support. There is an uplift fee waiver for all 3 years of Extended Support through November, 2013. As long as customers are current on their Software Update License & Support fees and renewals, their release version will automatically receive the Severity 1 Production Support Exception to Sustaining Support for 11.5.10. Customers must meet, or be above, the required minimum baseline patch level as identified in Doc ID 883202.1 - Patch Requirements for Extended Support of Oracle E-Business Suite Release 11.5.10.

So it is highly critical that all customers who are currently on 11i plan and upgrade their system to R12 to keep their Extended Support active.

2) New Features

Better User Experience in R12

Reduced pop-ups and duplicate screens
Re-designed and streamlined entire workflows
Reduced the number of steps to complete key tasks
Improved overall look & feel and visual style
Increased personalization capabilities

Financials R12 New Features

Ledgers and ledger sets
Centralized rules-based accounting engine
Single, global tax engine
Centralized payments engine
Common global intercompany system
Shared services support with multi-org access
Centralized data model for suppliers and banks
Incentive Compensation enhancements
Improved Collections Functionality
New Project Management tools
Enhanced Rev Recognition
Enhanced Revenue Matching Subledger Accounting

XML Publisher Reports, Embedded BI

Other Features

iRep

Enhanced Patch Management and Cloning

Better Security

Simpler Management, Better UI

User Management, Globalization

UPK (User Productivity Kit)

3) Fusion Middleware

AIA, BPEL, SOA, ESB, BAM

Here are some additional reading materials on R12 - EBS Release 12 Information Center on Metalink:
The link for R12 Information Center

EBS Release 12 RCD’s (Release Content Documents) (Metalink Note: 404152.1): 
EBS Release 12 Documentation Resources Overview (Metalink Note: 394692.1):
EBS Release 12 Translation Matrix (Metalink Note: 412218.1):

My article in the OAUG Insight Magazine - Summer 2012 Edition (Cover Page)

Meet your SLA with SLA -

Service Level Agreement with Subledger Accounting

My White paper on “Meet your SLA with SLA”  is published in the OAUG Insight Magazine, a quarterly magazine published by the Oracle Applications Users Group.  

Here are some of the highlights of my article:

The article aims to provide a comprehensive knowledge and  understanding of the Subledger Accounting (SLA) architecture in Oracle E-Business Suite (EBS) R12. It also uncovers some of the unpublished implementation tips and tricks and show how users can meet their financial and reporting needs on time using SLA. In addition, the article highlights how we used the SLA functionality to automate and control various accounting entries using specific business rules.

SLA is a new functionality introduced in EBS R12.SLA is a powerful and flexible rules-based accounting engine with a centralized data model and repository that generates accounting entries based on source transactions for all Oracle Applications transactions. With the introduction of SLA, customers now have a unified view of all the Oracle Application Subledgers across all modules with accounting entries. SLA helps to do a single-step posting to all ledgers and also provides realtime/online accounting information as well as an audit trail for all transactions

.

Subledger Accounting comprises five components: Ledger, Subledger Accounting Method, Application Accounting Definition, Journal Line Definitions and Sources 

Using SLA - Case Study: Prior to the EBS R12 upgrade, in our Order to Cash system, when an AR receipt was created, the existing process was mapping the receipt to the default cost center as per the system setup. There was a lot of manual effort involved to reclassify them to the correct customer cost center.

As a part of the EBS R12 Upgrade, using Subledger Accounting rules, we wanted to automate this and make the system automatically apply the receipts to the correct cost center for the customer. Basically, our requirement was to ensure that the create accounting process derives the correct accounting Flexfield segments based on the receipts applied to the customer based on the sales channel to Accounting Flexfield mapping. We wanted this to work for any cash application – receipt applied, receipt unapplied, receipt reversed, etc. The event type for this case study is receipt, and event class is applied, unapplied, reversed, etc.

Solution Steps we used to configure SLA rules:

1.      Create a PL/SQL code – to derive the correct AFF Segments based on receipt and other input parameters.

2.      Define Custom Sources – Associate the PL/SQL code to Custom Source.

3.      Define Account Derivation Rules (ADR).

4.     Define Journal Line Types (JLT).

5.      Define Journal Entry Descriptions (JED).

6.     Define Journal Line Definitions (JLD) – Associate the ADR, JLT and JED together.

7.      Define/Validate Application Accounting Definition (AAD).

8.     Define Subledger Accounting Method (SLAM).

9.     Assign Subledger Accounting Method to Ledger.
10.     Run the Create Accounting Process

After the accounting process completes, you should be able to view the accounting entries from the Receipts window