| SQL with SQL PLUS Reports and using multi-org. [message #241495] |
Tue, 29 May 2007 15:54  |
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 #241503 is a reply to message #241500] |
Tue, 29 May 2007 16:15  |
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 #241515 is a reply to message #241504] |
Tue, 29 May 2007 19:10 |
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 #241578 is a reply to message #241553] |
Wed, 30 May 2007 01:33 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
