Oracle Applications Multiple Organizations
Access Control for Custom Report
Steps to be carried out in reprt RDF file:-
1 1. Add following parameters
P_REPORTING_LEVEL (CHAR 100)
P_REPORTING_ENTITY_ID
(NUMBER)
P_REPORTING_LEVEL_NAME (CHAR 100)
P_REPORTING_ENTITY_NAME (CHAR 100)
GC_ORG_ID (CHAR 1000) -> used
as lexical parameter in AFTER PARAMETER FORM report trigger
GC_REPORTING_ENTITY (CHAR 1000)
-> used as lexical parameter in AFTER PARAMETER FORM report trigger
22. Add lexical parameters in the report data model SQL query
33. Add gl_ledgers table and related condition in the data model SQL query
if required.
example:
and gled.ledger_id = ract.set_of_books_id
44. Add rp_message (CHAR 1000) place holder column at ‘Report Level’ in data
model.
55. Code to be added in AFTER PARAMETER FORM trigger
--=========== added for mo reporting
=====================================================
srw.user_exit('FND SRWINIT');
/*
7320623 - reinitialize XLA routine to support NVLs for acct_site
this
is necessary because acct_site is outer joined to other
tables so the NVL situation is required to support that join */
XLA_MO_REPORTING_API.Initialize(:p_reporting_level, :p_reporting_entity_id,
'AUTO');
:p_reporting_entity_name :=
substrb(XLA_MO_REPORTING_API.get_reporting_entity_name,1,80);
:p_reporting_level_name :=
substrb(XLA_MO_REPORTING_API.get_reporting_level_name,1,30);
/* 7320623 - Passing use_nvl as N
to improve performance */
XLA_MO_REPORTING_API.Initialize(:p_reporting_level,
:p_reporting_entity_id, 'AUTO',
'N');
IF :P_REPORTING_LEVEL = 1000
THEN
-- Implies Reporting Level
is Ledger
:gc_reporting_entity := ' AND ract.set_of_books_id = :P_REPORTING_ENTITY_ID
';
:gc_org_id := '
AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
--
gc_rcpt_org_id := ' AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
ELSIF :P_REPORTING_LEVEL =
3000 THEN
-- Implies Reportintg Level
is Operating Unit
-- gc_reporting_entity
:= ' AND hou.organization_id = :P_REPORTING_ENTITY_ID ';
END
IF;
--==========================================================================================
6. Code to be added in BEFORE REPORT trigger
--================= added for mo
reporting ==============================
/*Added for value retriving for
bug 4327441*/
l_ld_sp varchar2(1);
begin
declare
lclient varchar2(100);
begin
--SRW.USER_EXIT('FND SRWINIT');
:rp_message:=null;
IF to_number(:p_reporting_level) =
1000 THEN
l_ld_sp:=
mo_utils.check_ledger_in_sp(TO_NUMBER(:p_reporting_entity_id));
IF l_ld_sp = 'N' THEN
FND_MESSAGE.SET_NAME('FND','FND_MO_RPT_PARTIAL_LEDGER');
:rp_message := FND_MESSAGE.get;
END IF;
END IF;
COMMIT;
--SET TRANSACTION READ ONLY;
select userenv('client_info')
into lclient
from dual;
--================= added for mo
reporting ==============================
Steps to be carried out in Concurrent Program parameters:-
1.
Add following
parameters as FIRST TWO PARAMATERS (Display – Y, Required - Y)
Reporting Level (Value Set:
FND_MO_REPORTING_LEVEL, Prompt: Reporting Level, Token: P_REPORTING_LEVEL)
Default type: SQL statement, Default Value: SELECT MEANING FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'FND_MO_REPORTING_LEVEL' AND LOOKUP_CODE = 1000
Reporting Context (Value Set:
FND_MO_REPORTING_ENTITY, Prompt: Reporting Context, Token: P_REPORTING_ENTITY_ID)
Default type: SQL statement, Default Value: SELECT entity_name, entity_id FROM
FND_MO_REPORTING_ENTITIES_V WHERE reporting_level =
:$FLEX$.FND_MO_REPORTING_LEVEL and rownum=1
2.
Set Operating Unit
Mode as Multiple
Navigation: System Administration
> Concurrent Programs
Query the concurrent program and
click on Update
3.
Go to Request tab and
update Operating Unit Mode to Multiple. Click on Apply.
4.
Add the concurrent
program to ‘XX_TT_Request_Group’ Request Group
5.
Run the concurrent
program from TT Time Keeper (MO) responsibility which is attached to custom
MO:Security Profile which have access to all OU’s
Under Tadmur Trading company
(Legal Entity)
a) When Report run at Ledger
b) When Report run at Operating Unit
-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-
If set_of_books_id is not found report query tables, we need to modify
the lexical parameter condition in After Parameter form trigger like below.
IF :P_REPORTING_LEVEL = 1000 THEN
-- Implies Reporting Level
is Ledger
--
:gc_reporting_entity := ' AND ract.set_of_books_id =
:P_REPORTING_ENTITY_ID '; --comment this line
-- :gc_org_id :=
' AND gled.ledger_id = :P_REPORTING_ENTITY_ID '; --replace this line
with below query
:gc_org_id := 'AND
alias.org_id IN (SELECT organization_id
FROM hr_operating_units
WHERE set_of_books_id =:p_reporting_entity_id)
ELSIF :P_REPORTING_LEVEL =
3000 THEN
-- Implies Reportintg Level
is Operating Unit
--
gc_reporting_entity := ' AND hou.organization_id = :P_REPORTING_ENTITY_ID
';
:gc_org_id := '
AND ract.org_id = :P_REPORTING_ENTITY_ID ';
END IF;
