| Problem : Parameter passing [message #87268] |
Wed, 15 December 2004 20:57  |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Friends!
My user have different roles and the role denotes the country which he can access to that particular countries records.
For eg. I have a user 'PROD_USER1' and he has the roles of 'PROD_US','PROD_AUS','PROD_UK',etc. I have another user 'PROD_USER2' and he has the role of 'PROD_US' only. I get the country name using INSTR & SUBSTR functions from role and assigning to a parameter(P_COUNTRY) in my form. In my form, in main block at DEFAULT_WHERE Clause I wrote the code as PROD_COUNTRY = :PARAMETER.P_COUNTRY. This is working fine if my user(eg PROD_USER2) has a single role (that means one country). If my user(eg PROD_USER1) has multiple roles then it's showing only one country's(i.e., 'US') records. That means it is taking only one country. So my question is if I have many roles like for the user 'PROD_USER1' then how to show all the coutries records?
I will be very glad if you give any solution for it.
|
|
|
|
| Re: Problem : Parameter passing [message #87269 is a reply to message #87268] |
Wed, 15 December 2004 21:28  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Make use of Pre-query trigger & set the default_where of your Block in this trigger.
To do so ,make use of Set_block_property('block_name',default_where,'where clause');
HTH
regards
Himanshu
|
|
|
|
|
|
|
|
| Re: Problem : Parameter passing [message #87278 is a reply to message #87275] |
Thu, 16 December 2004 00:05 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi!
This is the code I wrote in first form(i.e., LOGON form). After I successfully logged in this form then this code will pass the Role Name to (i.e.,:GLOBAL.USR_ROLE) second form(some application, where I get all the Production Information of all the countries).
DECLARE
CURSOR CUR_ROLE IS SELECT GRANTED_ROLE FROM
PROD_ROLEPRIVS_TAB
WHERE USR_NAME = :GLOBAL.USER_NAME;
BEGIN
:GLOBAL.USER_NAME :=
GET_APPLICATION_PROPERTY(USERNAME);
FOR C1 IN CUR_ROLE
LOOP
BEGIN
:GLOBAL.USR_ROLE := Z1.GRANTED_ROLE;
END;
END LOOP;
END;
In the second form at WHEN-NEW-FORM-INSTANCE I wrote the code as:
BEGIN
SELECT SUBSTR(:GLOBAL.USR_ROLE,INSTR(:GLOBAL.USR_ROLE,'_' ) + 1, LENGTH(:GLOBAL.USR_ROLE))
INTO :PARAMETER.P_COUNTRY FROM DUAL;
SET_BLOCK_PROPERTY('PROD_MAIN_BLK',DEFAULT_WHERE,'PROD_COUNTRY = :PARAMETER.P_COUNTRY');
GO_BLOCK('PROD_MAIN_BLK');
EXECUTE_QUERY;
END;
For user 'PROD_USER1', I have 'PROD_US','PROD_AUS',
'PROD_UK',etc roles.
By using INSTR&SUBSTR I get the suffix as country from role. I am using this as PROD_COUNTRY an trying to select the values from my table. But I am getting only the first country i.e., 'US' records. I suspect I might be doing mistake at LOOP, but couldn't trace it.
Thanks for helping me.
|
|
|
|
|
|
| Re: Problem : Parameter passing [message #87301 is a reply to message #87280] |
Thu, 16 December 2004 21:19 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Rupa,
Modify your code as follows:
In your first form:
DECLARE
CURSOR CUR_ROLE IS SELECT GRANTED_ROLE FROM
PROD_ROLEPRIVS_TAB
WHERE USR_NAME = :GLOBAL.USER_NAME;
BEGIN
:GLOBAL.USER_NAME :=
GET_APPLICATION_PROPERTY(USERNAME);
FOR C1 IN CUR_ROLE
LOOP
BEGIN
If :GLOBAL.USR_ROLE Is null Then
:GLOBAL.USR_ROLE := ''''||SUBSTR(C1.GRANTED_ROLE,6)||'''';
Else
:GLOBAL.USR_ROLE:=:GLOBAL.USR_ROLE||','||''''||SUBSTR(Z1.GRANTED_ROLE,6)||'''';
End If;
END;
END LOOP;
END;
Pass this :GLOBAL.USR_ROLE to parameter P_COUNTRY of your second form.
In When-new_form-instance write:
Declare
L_Default_where varchar2(1000):='Where 1=1 and Prod_country in('||:PARAMETER.P_COUNTRY||')';
BEGIN
SET_BLOCK_PROPERTY('PROD_MAIN_BLK',DEFAULT_WHERE,L_Default_where);
GO_BLOCK('PROD_MAIN_BLK');
EXECUTE_QUERY;
END;
In the second form write in Pre-query Trigger :
Declare
L_Default_where varchar2(1000):='Where 1=1 and Prod_country in('||:PARAMETER.P_COUNTRY||')';
BEGIN
SET_BLOCK_PROPERTY('PROD_MAIN_BLK',DEFAULT_WHERE,L_Default_where);
END;
HTH
Regards
Himanshu
|
|
|
|
| Re: Problem : Parameter passing [message #87305 is a reply to message #87301] |
Thu, 16 December 2004 23:19 |
rupa
Messages: 41
Registered: August 2002
|
Member |
|
|
Hi Himanshu!
Thank you very much. I used the logic that you sent and it worked well. Actually I was not getting two countries at a time, this below code helped me to get
two countries.
If :GLOBAL.USR_ROLE Is null Then
:GLOBAL.USR_ROLE := ''''||SUBSTR(C1.GRANTED_ROLE,6)||'''';
Else
:GLOBAL.USR_ROLE:=:GLOBAL.USR_ROLE||','||''''||SUBSTR(Z1.GRANTED_ROLE,6)||'''';
End If;
Many Many thanks for your help once again.
MERRY CHRISTMAS & WISH YOU A HAPPY NEW YEAR.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
