Nilesh Jethwa

Please read the Multi-Org Background information

Virtual Private Database (VPD)

The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.

Database Schema - Access to one operating unit

Database Schema - Access to multiple operating units

You can rewrite reference views that join data from multiple single organization views with the security policy attached to one secured synonym and the remaining reference to _ALL tables instead of single organization views. This improves performance because the policy is used once for the reference views that join data from multiple single organization views.
Multiple Organizations Security Policy Predicate

Synonyms replace single organization views that contain the CLIENT_INFO predicate attached to them. When installing, you must attach a security policy function to the multiple organizations synonyms. This indicates that the security is in place irrespective of the tools used to access the data.

The security policy function returns different predicate based on the number of accessible operating units. An application context attribute “ACCESS_MODE” is set based on the accessible operating units. Context sensitive security policy is used for multiple organizations access control to minimize the coding impact. The multiple organizations code in previous releases works in the context of only one operating unit. It was not anticipated that multiple organizations access would be supported. A solution to code impact is to change the policy predicate whenever needed. For example, when you open a form using a responsibility that can access multiple operating units and when you select an operating unit, the operating unit context is established and you do not need to modify the code that is used for validation from that point onwards, if the synonyms return data for the selected operating unit.

If the access mode is M (Multiple), then the policy predicate issues an EXISTS sub-query to a global temporary table. The global temporary table is a new feature in Oracle 8i. The table stores and manipulates data specific to a SESSION or TRANSACTION. If the access_mode is S (Single), then a simple equality predicate is used for performance reasons, since it is cost effective in comparison to the temporary table. An access mode A (All) is incorporated to bypass the security for functionality that needs full table access. If the access mode is not set or is NULL, then a simple predicate that uses the CLIENT_INFO value for ORG_ID is used for the policy predicate to support backward compatibility.

MO_GLOBAL.Org_Security function:

FUNCTION org_security(obj_schema VARCHAR2
                                           obj_name VARCHAR2)RETURN VARCHAR2
IS
BEGIN
  --
  -- Returns different predicates based on the access_mode
  -- The codes for access_mode are
  -- M - Multiple OU Access
  -- A - All OU Access
  -- S - Single OU Access
  -- Null - Backward Compatibility - CLIENT_INFO case
  --
 IF g_access_mode IS NOT NULL THEN
   IF g_access_mode = 'M' THEN
     RETURN 'EXISTS (SELECT 1
                       FROM mo_glob_org_access_tmp oa
                       WHERE oa.organization_id = org_id)';
   ELSIF g_access_mode = 'A' THEN -- for future use
     RETURN NULL;
   ELSIF g_access_mode = 'S' THEN
     RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';
   END IF;
 ELSE
   RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
 END IF;
END org_security;

The simple predicate using CLIENT_INFO is used for the following case:

* Access control is not enabled for older releases of the applications, it is not backward compatible: You cannot enable the multiple organizations access control feature for all products simultaneously because multiple organizations views are shared between products at different levels. For example, if you choose to upgrade Payables but choose to keep an earlier version of Purchasing then Payables is access control enabled, but Purchasing is not. Therefore, Purchasing must replace the views it shares with Payables, such as PO_VENDOR_SITES, and PO_HEADERS, with secured synonyms. The secured synonyms must work as before for Purchasing, since you have not upgraded Purchasing and Purchasing still relies on CLIENT_INFO.

The simple predicate using current_org_id is used for the following cases:

* Access control is limited to only one operating unit: In this case, the access mode is 'S'. An example is when a user can access to only one operating unit through the MO: Security Profile or the MO: Security Profile is not set and the user access depends on MO: Operating Unit.* Access control is enabled with access to multiple operating units: The security profile provides access to multiple operating units, but in the scope of a transaction since the operating unit is controlled, a simple predicate eliminates additional changes to the server and client side code.

The complex predicate is used for these cases:

* Access is enabled and the security profile gives access to multiple Operating Units. The access mode is set to 'M' for this case.

For example, any statement on RA_CUSTOMER_TRX (synonym to which the security policy is attached) is dynamically modified to use the policy predicate.

A simple query by the user:

SELECT trx_number from ra_customer_trx

is modified at runtime if the responsibility can access multiple operating units to:

SELECT trx_number from ra_customer_trxWHERE (EXISTS (SELECT 1FROM mo_glob_org_access_tmp oaWHERE oa.organization_id = org_id))

or is modified at runtime if the user's access responsibility can access one Operating Unit with access control enabled for the module to:

SELECT trx_number from ra_customer_trxORG_ID = sys_context(’multi_org2′,’current_org_id’)

Overview of Multiple Organizations Architecture for release 11 and prior

The primary objective of multiple organizations architecture, introduced in Oracle Applications Release 10.6, is to secure data from unauthorized access by individuals belonging to different operating units in an enterprise.

A new or fresh installation of an Oracle Applications instance does not automatically enable multiple organizations. The system administrator creates operating units using the Define Organizations window in Oracle Human Resources Management System (HRMS), and runs the Convert to Multiple Organization program from AD Administrator to enable the multiple organizations feature. Typically, the system administrator defines “MO: Operating Unit” profile at Responsibility and/or User level. The “organization_id” of the “MO: Operating Unit” profile option value filters the transactional data. The CLIENT_INFO application context space stores the multiple organizations context value.

Multi-Org views use the following WHERE clause to filter application records:
‘org_id = substrb(userenv(”CLIENT_INFO”),1,10)’

Overview of Multiple Organizations Access Control Architecture (Release 12)

The Access Control feature in Release 12 allows the user to enter or query records in one or more operating units without changing application responsibility. It is the system administrator’s discretion to either implement the feature or use the same multiple organizations profile option setting available before Release 12 by using the single operating unit mode (i.e. one operating unit for a responsibility).

In Release 12, the multiple organizations context value is no longer initialized by the FND_GLOBAL.APPS_INITIALIZE routine thereby reducing unnecessary context setting and resource consumption for applications that do not use operating unit context for data security.

To use the single operating unit mode, you must set the value for the “Initialization SQL Statement – Custom profile” to “mo_global.init(’S',null);”. This initializes the operating unit context based on the “MO: Operating Unit” profile option and the “MO: Security Profile” profile option must not be set.

Fresh install of Release 12 Application is enabled with multiple organizations, however, the system administrator must create operating units to use multi organizations sensitive application products. The user can create new operating units in the Accounting Setup Manager page in addition to HRMS’s Define Organizations page.

Here is some background information

Multiple organizations architecture (Multi-Org) was introduced in Release 10.6 to secure the data by operating unit. In Release 10.7, Oracle added a column ORG_ID to each base table to partition the data by operating units. The partitioned tables are renamed with the suffix, ‘_ALL’, and their corresponding secured views are created in Applications (APPS) schema. The following diagram shows a single organization view in the APPS schema.

Multiple organizations views restrict access by filtering records for an operating unit assigned to the application responsibility set for the “MO: Operating Unit” profile option. This profile option value is cached in application context, and is initialized when calling the FND initialization routine. The FND CLIENT_INFO predicate includes all multiple organizations views and SQL statements that require multiple organizations security. The FND_CLIENT_INFO function retrieves the ORG_ID value stored in the application context. This value is valid for a session, unless explicitly changed by the calling procedure.

Use the _ALL table in the SQL statement to retrieve information irrespective of the operating unit. To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

Continue reading Multi-Org structure - Vitual Private Database

Data Conversion for Oracle Applications

This topic is shared to provide an overview of Data Conversion for Oracle ERP.Approach used for Data Conversion with details on each and every steps as below find here

data-conversion-for-oracle-applications

1. Conversion Data Mapping
2. Download Programs
3. ASCII Flat File
4. Upload Program
5. Interface
Interface Table
Creation of Interface Table
6. Translation Programs
7. Interface Programs
8. Application Production Table
9. Testing
10. Write and Perform Conversion Execution Plan

Posted on June 30th, 2008 by admin

AIM (Application Implementation Method )
Every Oracle apps consultant will be and should be aware of the documents related to his/her work profile depending if they are Functional or Technical consultant.
Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop […]

http://www.infopurple.com/aim-application-implementation-method

Search results are very dynamic. They jump around everyday as new content is added and as new websites are added.

I don’t normally use Yahoo for my search needs but today I happened to casually google search for Dashboards on Yahoo and was pleasantly surprised by what I found.

Yep, InfoCaptor is one of the top three dashboards that show up on Yahoo, just behind Cognos!

It would be a real party time when it happens on Google

When is a Data Warehouse Appliance not a Data Warehouse Appliance?
Data warehouse appliances are so-called because they are used for storing and managing the data associated with data warehousing projects. Strictly speaking though these products should be called database appliances, or simply data appliances, because they support only the database processing component of a data […]

http://www.infopurple.com/when-is-a-data-warehouse-appliance-not-a-data-warehouse-appliance

Posted on July 2nd, 2008 by admin

BUSINESS INTELLIGENCE IN ACTION
continued from here <<
“We were a very different university about one year ago,” says Keith Werosh, registrar at the National University of Health Sciences (Ill.). He is referring to the operation pre-BI. NUHS installed BI from Business Objects a year ago. “Prior to having this system, we were departmentalized. Information was kept […]

http://www.infopurple.com/business-intelligence-in-action

This short Article shows the basic Installation steps in installing OBIEE on your windows PC.
Pre-requisite
Computer with Windows (Home Basic) ,RAM 2GB, HDD approx 80 GB
JDK 1.5 installed and JAVA_HOME created.
Download the OBIEE setup from the www.Oracle.OTN.com. After downloading, I recommend to unplug the internet (turn off wireless internet or unplug the LAN wire )from […]

obiee-installation-steps

How Valuable is Business Intelligence to the Enterprise?
continued from <<
Finding 1
The BI Learning Curve Has Just Begun
Business intelligence use grows considerably as companies gain experience with this technology. Nearly 90 percent of our respondents use BI, and most others will probably start doing so in the next two years. Still, there are relatively few BI […]

http://www.infopurple.com/how-valuable-is-business-intelligence-to-the-enterprise-2

Business Intelligence and On-Demand: The Perfect Marriage?
Both established vendors and upstarts now offer BI applications as on-demand services. And more customers are saying yes to faster deployment times, less onerous IT demands and speedier access to reporting data.

Ask Dennis Hernreich, COO and CFO of Casual Male Retail Group, what his life was like before he switched to an on-demand business intelligence reporting application, and he remembers the frustration all too easily.

read more >>http://www.infopurple.com/business-intelligence-and-on-demand-the-perfect-marriage

Four Tips for Better Business Intelligence in 2008
Avalanches of data and the need for better decisions mean it’s time to take BI to the next level. Aberdeen offers ways to make achieving that next level easier.

Companies want more actionable data, and they want more users to have it. But extending the use of business intelligence throughout the organization remains a challenge. One reason: Lack of BI and IT skill sets continue to plague companies interested in taking BI to the next level, …… read more at http://www.infopurple.com/four-tips-for-better-business-intelligence-in-2008

The Business Intelligence and Data Warehousing Glossary
I have collected and compiled this from a very nice website.Some of the terms are relating to Microsoft’s tool (Microsoft’s Analysis Services ) but that can be generalised to other non-microsoft tools also.
Link >>
Agent
An application that searches the data and sends an alert when a particular pattern is found.
 
Aggregations
Information […]

http://www.infopurple.com/the-business-intelligence-and-data-warehousing-glossary

Is Data Warehousing Essential to Business Intelligence?
The role of data warehousing is changing as companies move toward using new business intelligence approaches.
Over the last few issues of this newsletter, I have been writing about the impact of Enterprise 2.0 approaches on business intelligence (BI). I intended this time to write about how business intelligence […]

http://www.infopurple.com/is-data-warehousing-essential-to-business-intelligence

What is the Difference Between Querying and Browsing Data?
Search is beginning to gain interest in the business intelligence (BI) space, and several people have begun asking about the difference between querying data (using a traditional database query language) and browsing data using a search tool, and which approach to use when. This blog entry is […]

http://www.infopurple.com/what-is-the-difference-between-querying-and-browsing-data

Business Intelligence

A new technology can analyze data at amazing speeds. So why is higher ed slow to adopt?

Listen to higher ed futurists and you’ll be convinced that business intelligence is the wave of the future. It is the next “it” thing that will connect every part of the enterprise. Best yet, BI will link all data, allowing disparate software programs, provided by numerous and sometimes-competing vendors, to “talk” to each other.

Admissions files will connect to financial aid information will connect to student services files will connect to academic records, and so on. The end result will be data that can actually predict what that ….REAd MOre >>

http://www.infopurple.com/business-intelligence

Top 5 Ways To Increase BI Value
What are the most effective ways to increase the value your company gets from its business intelligence systems?
N=215

1)Better align our business intelligence systems with our business strategy [50% ]
2)Improve data quality [47% ]
3)Better integrate our business intelligence systems with other systems such as CRM or ERP [41% ]
4)Better understand […] read more >>>

http://www.infopurple.com/top-5-ways-to-increase-bi-value

Database Normalization and Table structures
Normalisation is the term used to describe how you break a file down into tables to create a database.
There are 3 or 4 major steps involved known as
1NF (First Normal Form),
2NF (Second Normal Form),
3NF (Third Normal Form) and
BCNF (Boyce-Codd Normal Form).
There are others but they are […]

http://www.infopurple.com/database-normalization-and-table-structures

Interview Questions - Oracle AR (Functional)
Accounts Receivables Interview Questions AND aSWERS

some of the questions and answers listed here might repeat .from
http://www.infopurple.com/interview-questions-oracle-ar-functional-accounts-receivables-02
http://www.infopurple.com/interview-questions-oracle-ar-functional-accounts-receivables-01
1. How do you create a Chargeback Invoice ?
A. Using Receipts window
2. How do you adjust the amount of advance already received against an Invoice ?A. Create an Invoice for Deposit and receive an […]

http://www.infopurple.com/interview-questions-oracle-ar-functional-accounts-receivables-03

Business Intelligence and On-Demand: The Perfect Marriage?
Both established vendors and upstarts now offer BI applications as on-demand services. And more customers are saying yes to faster deployment times, less onerous IT demands and speedier access to reporting data.

Ask Dennis Hernreich, COO and CFO of Casual Male Retail Group, what his life was like before he switched to an on-demand business intelligence reporting application, and he remembers the frustration all too easily.read more >>

http://www.infopurple.com/business-intelligence-and-on-demand-the-perfect-marriage

Dear Consultants, Developers and all readers,

Here is an interesting thought and let me know if it chimes well with you.

I believe the following idea if implemented could give a significant advantage and boost your marketability.

Now, everybody has a resume or a CV, well ofcourse,this is the first step you execute when you step out of college.

All of us store our resume in a more or less standard Doc format, so how about having an online version of your resume?

Sounds interesting?

This online version would be just a single web page having your resume content. You can either put a summary of your resume or complete details if you like.

So what will a single page resume serve for?

When I suggest someone to start their own blog, I see lot of enthusiasm but no follow through. Few of the main reasons cited is “Don’t know what to write about” or simply “Have no time” or “Is it worth investing the time?”

Creating a single page blog is just the beginning and helps in breaking the inertia. Once you create your resume blog, the web-url becomes your signature URL and you can put this URL back on your resume or when you update your CV on job sites like Monster or Dice.

Appsbi.com started as a professional journal and it has become modestly huge than I orginally thought of. But it did not happen overnight. No big success happens overnight unless you have a Genie in a bottle. But interestingly all successes appeart to be an overnight success, isn’t it?

Start small, take the first step and do it now

Appsbi.com has tremendously helped me for my consulting career and is now helping as a marketing engine for InfoCaptor

Similarly, I believe that creating your own blog will help you in one way or the other, if not today then sometime in future.

The blog that you will create will act as one of the building block in your career.

Now let us attack the negatives or the holding back thoughts

“Don’t know what to write about”

Just make it your online professional journal. Start by putting up your resume online and slowly add more pages. Don’t feel pressurised to add more contents. Just add what comes naturally to you. Don’t worry about things like “is it worth posting”. Even if you have a small piece of code or found a new technique, just post it.

You should keep personal stuff out and stick with work related things. Better have a seperate personal blog.

“Have no time”

It would take 5 minutes to create a blog and add your resume to it. Then add a weekly recap of your work related things to it. This would take another 15 minutes. If weekly is too much then stick with bi-weekly or monthly. Is the initial 5 minutes and 15 minutes / week or /month asking too much?

“Is it worth investing the time?”

Do you call 15 minutes/month investment of time? You will eventually know if it is worth the time if you stick to it.

So where do you begin?

Create a blogger account http://www.blogger.com , pick appropriate blog name and add your first post right now within 5 minutes.

Blogger is a free service by Google.

Another free service similar to blogger is to create an account at http://wordpress.com/

Let me know if this is a good idea and if you really followed through send me your blog url by commenting to this post.