Multiple oraganizations Access control(MOAC)-R12--Part1

Hi ALL,
Here i am trying to put the MOAC techical architecture in simple terms..

we are might have heard the term multi organization till 11.5.10 prior it is only one organization

what does multiorganization mean??
Managing multiple organizations data in a single system...putting it in lay man terms
For example lets take GEfinancial
It is headquarted in US and operations in india also..assume it declares the Profit and loss results in both the countries..
Indian accounting rules, financial Calendar is different between the US and India..
since it belongs to same company with in one system I define two operating units one for us and one for India
different ledgers and calendars...if I want I can have different chart of accounts also..

to manage data for both the organizations with in single instance oracle introduced the column org_id in all table which holds organization specific data..

For data security purpose people of a organization should able to see their own data to achieve this
they created view on the base table like _all _B which restricts the data of the organization to which the user/user responsibility is attached.
for this purpose they defined a profile MO:operating unit which is set at the responsibility/user level
based on this value the system context org_id is set to the operating unitid set at the profile level
and a additional conditiion is added to the where clause of the views like

'org_id = substrb(userenv(''CLIENT_INFO''),1,10)'

By this way they are able to restrict data to the one organization in all the forms,reports,concprograms

fnd_global.apps_intialize--will set the applciation context org_id

thats the resason when ever we try to access the data from toad/sql plus for single org specific views we will not get any results..

select * from po_headers--no data found

if we set the application context we will able to see data for that org_id

execute dbms_Application_info.set_client_info('101');
or
fnd_global.apps_intialize(userid,responsibilityid,applicationid) --this inturn will get the org_id and set the context


From R12:Multiple organization access control

Till 11.5.10.2 one responsibility is able to see data of only one operating unit..
when ever we want to see another operating unit data we need to change resposnsibility..
From R12 oracle introduced the concept of Multiple organization access control(MOAC) so that being in the same responsibility the user should
be able to see the data for which he is give access..
let see how they achieved it..

1.First we will define a policy which will have access for different operating units
2.they attach that policy to the responsibility/user at profile option level

technically..till 11.5.10 the query is getting chagned as org_id=101
if we have to access multiple organization it should changed to org_id in (101,102) or exits...

for achieving this oracle used the concept of Virtual Private Database (VPD)..
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.

First lets decide whethere the access mode to a responsibility is single or multiple or all.Based on the security policy oracle decides the access mode
let assume our security policy which is attached to our respobility has access to the two operating units.
now oracle will populate the global temporary table(session controlled) mo_glob_org_access_tmp with all the operating units attached to the security policy
of yours..

based on the vpd a concept from oracle 9i onwards they defined a policy 'ORG_SEC' which call the function 'MO_GLOBAL.ORG_SECURITY' for all the objects to which the policy is attached..



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;


let see what happend po_headers view(11.5.10.2) in R12

1.Dropped the view po_headers
2.Created a synonym for the base table
create synnonym for po_headers for po_headers_all

3.attach the security policy org_sec to this synonym


so when ever we access this synonym from any where the policy will call the mo_global.org_Security funcion to all a condition to the select statement we isssued

select * from po_headers will be changed as

select * from po_headerS_all where exists 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)'

By this they are able to access all data of all organizations assigned to the security policy.

Dynamic IN clause

Hi ALl,
This is one good ways of writing dynamic in clause..

if you have a select stament where you are uing in clause to restrict the data.if you are not sure on the inclause statment
what you will do..
what i used to do is make a dynamica sql build inclause on run time and then execute it ..But there is another smart way of building it dynamically..

Example1:

cursor c_cust_info is
select * from hz_cust_accounts where customer_class_code in ('CEMP','CEMP20','INDIV')

for making the inclause to by dynamic


cusror c_cust_info(c_in_statement varchar2)
select * from hz_cust_accounts where instr(c_in_statement,customer_class_code)>0

and open the cusor with the in clause you require..like

for rec in c_cust_info('CEMP,CEMP20') loop

end loop;

QP_CUSTOM--OE_MSG_PUB

Hi All
QP_CUSTOM is a standard package given by oracle to customize the price derivation based on our custom logic.
Recently i customisee this package to get freight charge based on the wegith of the line items.
This was not so tough to achieve but the user want to show some messages when ever any exception occurs....But the qp_custom is supposed to return a number..
This api is called from dynanic formual on some event like saving entering ..
The solution we thought of is form personalization by returning some constant number an throw exception when we encouter that values...But still i didn't work...the possible ways form personalization works are far less than the ways it didnt work...

after sorm R&D on google and metalink i found that there is api oe_msg_pub to put error messages...
But unfortunately i didnt find right code to use it properly after some hit and try
i figured out that
oe_msg_pub.add_text will add a message
oe_msg_pub.add will put the message on the stack..This will display messages in the
message window in order entry screen
i thought it would be better if we display message in a modal window
so i gave a blind try like define a message in application developer message window


fnd_message.set_name ('XXCUST', 'XX_ERROR_MSG');
fnd_message.set_token ('MESSAGE', 'No Shipping Charges defined');
oe_msg_pub.ADD;
"Message is a token in the message"
This really works...every time you change qp_custom package close the order entry form ,reload it and try...

Date parameter in Concurrent programs-FND_DATE

Hi All,
Normally when ever we want to use date parameter for pl/sql stored procedures we ecounter a common error saying that format string doenst match...
then we do a data conversion to_date(p_date,'RRRR/MM/DD HH24:MI:SS') and then use it..

The better way of doing it is define the data parameter as varchar2 and use the function FND_DATE.date_to_canonical(p_date).This will direclty convert the text string comming from the concurrent programs to date value

ln_processing_date := NVL (fnd_date.canonical_to_date (p_processing_date), SYSDATE);

Oracle Apps-Changing LOV Query-Form Pesonalization

How to change a LOV query from Forms Personalization??

This is one of the requrirement that we want to add a additional condition to the LOV query or you want to populate the LOV all together from your SQL.
Its a simple four step process

Step1:
Get the Query that of the LOV of the standard form. You can do it by opening the standard form in the form builder or Get the query by taking the trace of the form and getting the query from the trace..

Step2:
If you want to add only a few more conditions to the query just add the conditions
In other case create a view with column same as in the standard query.

Step3:
In this step on when new form instance we will create the record Group from query

Step4:
Attach the record group to the LOV you want to change…

Step1:Get the query
The standard query is

select event_name, description from fa_maint_schedule_dtl WHERE EVENT_NAME LIKE :1

I want to populate from my own sql so I create a view

create or replace view XX_AMC_EVENT_DETAILS as
select 'SERVICE' EVENT_name ,'ATM SERVICE' DESCRIPTION from dual
union
select 'AMC' ,'AMC CONTRACT' from dual
union
select 'TEST1' ,'TEST2' from dual

Step2:
Plz find teh screen shots attached..

Terminating Concurrent program sessions

Hi ALL,
One way of finding the session id for concurrent is through fnd_cocurrent_request...as discussed in my earlier post
But there is another easy way...


select sid,serial#,MODULE,ACTION,status from V$session

the module will contain the concurrent program short name...If only one instance of the concurrent program is running
we can easily identify the sessions of it..

If we terminate the program and the session not got released..simply find th session

and use the command
alter sytem kill session 'SID,SERIAL#'
Please the values you got from the above query

7 Ways for a Better LOG--My View..Suggestions Welcomed

Hi All,
we all use the log files for debugging issues by writting text into them when ever a exception occurs or to know the process flow of our code...Here are few steps that will make us to have better log and make our life easy during supporting phase..

7 ways for writing a Better Log
1.Always Display the Name of the package/procedure in the LOG File.
The main advantage of doing this is no need query the concurrent program name and executable to find the procedure of
concurrent program

2.It is always better to have a common package having the log function.So that every time we want to write into a log
there is no need to type entire fnd_file.put_line(fnd_file.log,'message'
it can be easily done like
xxab_util_pkg.log('message');
it is better to have out also in the same way...

3.If the code is running very long number of lines define a stage variables and set it at different levels..and the dispaly the stage completed value when ever the exception occurs...This helps to debug the issue faster...

4.Display all the derived values in the log

5.When ever you are displaying statements like no data found for any sqlstatments in the code display all the values passed to the SQL along with nodatafound or too many row exception.This inturn will reduce a lot of time during support...

6.Always make sure you attach the SQLERRM variable with the when other exceptions handled...and diplay the section name which is causing this exception
exception
when others then
fnd_File.put_line('Unhandled exception occured in XX_GL_INTF_PKG.Check_PERIOD:'||SQLERRM);

7.Always End the concurrent program either in ERROR or warning when ever a exception occurs in the programs unless it is a business requirement.