Home » Applications » Oracle Fusion Apps & E-Business Suite » SQL with SQL PLUS Reports and using multi-org.
SQL with SQL PLUS Reports and using multi-org. [message #241495] Tue, 29 May 2007 15:54 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Is anyone familiar with using sql in sql plus reports in a multi-org. environment? If so, how did you handle the coding in the sql plus report with multiple organizations? Any suggestions would help.


Anne
Re: SQL with SQL PLUS Reports and using multi-org. [message #241496 is a reply to message #241495] Tue, 29 May 2007 15:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean with multiple organizations?

Regards
Michel
Re: SQL with SQL PLUS Reports and using multi-org. [message #241498 is a reply to message #241496] Tue, 29 May 2007 16:00 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
In a multi-org. environment. Meaning, an application that has 2 set of books in different currencies with different organizations.

Anne
Re: SQL with SQL PLUS Reports and using multi-org. [message #241500 is a reply to message #241498] Tue, 29 May 2007 16:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what is the problem?
Give a more specific exemple as I don't see any issue. You surely have a field that can be used to distinct the organization and so determine which rows belong to which.

Regards
Michel
Re: SQL with SQL PLUS Reports and using multi-org. [message #241503 is a reply to message #241500] Tue, 29 May 2007 16:15 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
So in the report for example I am using ap_invoices_all. In this table there is an org_id. Should I just use in the query for example:

select * from ap_invoices_all
where org_id = 100;

I'm not sure if this method would be safe if I'm using a different responsibility not related to org_id 100.

Anne
Re: SQL with SQL PLUS Reports and using multi-org. [message #241504 is a reply to message #241503] Tue, 29 May 2007 16:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With safe, are you talking about:
- confidentiality of data
- security of accesses
- fiability/accuracy of queries?

Regards
Michel
Re: SQL with SQL PLUS Reports and using multi-org. [message #241515 is a reply to message #241504] Tue, 29 May 2007 19:10 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm talking about security of access. For example, an Oracle Applications responsibility for AP Super User Canada. I want this user to be able to run the following sql plus report once they use the responsibility. I don't want AP Super User America to be able to see or run the sql plus report.

So I need to make sure within the sql plus report that I put some type of code in it to make sure it goes to the right responsibility. Any suggestions?

Anne
Re: SQL with SQL PLUS Reports and using multi-org. [message #241547 is a reply to message #241515] Tue, 29 May 2007 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, you should have a look at VPD (Virtual Private Database) aka RLS (Row Level Security) or FGAC (Fine Grained Access Control).
This functionality is made for that.

You also have OLS (Oracle Label Security) which is a built-in option based on FGAC but is not free.

Regards
Michel
Re: SQL with SQL PLUS Reports and using multi-org. [message #241553 is a reply to message #241547] Wed, 30 May 2007 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I'm moving this one to the e-business forum.
I somehow have the idea that this should be a native thing in Apps.
Re: SQL with SQL PLUS Reports and using multi-org. [message #241578 is a reply to message #241553] Wed, 30 May 2007 01:33 Go to previous messageGo to next message
Selvamani
Messages: 2
Registered: May 2007
Location: India
Junior Member
For Multi-org environment, in order to identify the organization mapped to particular responsibility we need to use the following function 'fnd_global.org_id'
So the query will be

select * from ap_invoices_all
where org_id = fnd_global.org_id;

The output of the query will the invoices for the particular responsibility.

Regards
Selva
Re: SQL with SQL PLUS Reports and using multi-org. [message #241597 is a reply to message #241578] Wed, 30 May 2007 02:32 Go to previous message
adragnes
Messages: 241
Registered: February 2005
Location: Oslo, Norway
Senior Member
Anne,

There are also a whole lot of multi-org views you can use. These only return rows for the current organisation.

Multi-org tables by convention end with "_ALL" and there should be a corresponding view with the same name without the "_ALL", e.g. for AP_INVOICES_ALL there is as multi-org view AP_INVOICES.

In order to use these views outside of OA, e.g. when developing in TOAD or SQL*Plus you have to use the APPS_INITIALIZE method in FND_GLOBAL.

--
Aleksander Dragnes
Previous Topic: Item Number Field in Autolockbox
Next Topic: Test script for financial module-india localization
Goto Forum:
  


Current Time: Sat Dec 03 14:01:29 CST 2016

Total time taken to generate the page: 0.09966 seconds