Home » Developer & Programmer » Forms » Restrict the user to access the records [merged 2 by jd] (Forms 6i)
Restrict the user to access the records [merged 2 by jd] [message #548819] Mon, 26 March 2012 08:29 Go to next message
kandimalla
Messages: 17
Registered: November 2011
Location: Bahrain
Junior Member

Hi,

I am using Forms 6i,Oracle Apps... I have database block....
generally we can retrieve the all the records by press F11 and Ctrl F11.... but my requirement is i have to restrict the user that they can access the records based on the condition ...

For example....let we take emp table....I have to restrict the user that they can query deptno 10 employees only........

How can i ?

Thanks in advance .........

Re: Restrict the user to access the records [message #548823 is a reply to message #548819] Mon, 26 March 2012 08:51 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
i could not understand your question what do u want. u want to keep for each user different criteria for example

if user---thiss then
deptno 10 or for whole users same criteria can u provide little more details


Regards
Re: Restrict the user to access the records [message #548825 is a reply to message #548823] Mon, 26 March 2012 08:53 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
ok let me give u some exaples note down

ONETIME_WHERE exists in 10g but not in 6i.

So then you have to use the other approach:

In WHEN-NEW-FORM-INSTANCE-trigger do the same as before but set an additional flag fpr AUTOQUERY into a global:

   DEFAULT_VALUE(NULL, 'GLOBAL.TXTDEPTNO');
  IF :GLOBAL.TXTDEPTNO IS NOT NULL THEN
          -- restrict the query of the EMP-block
           :GLOBAL.AUTOQUERY:='TRUE';
       GO_BLOCK('EMPLOYEES');
         -- automatic query
       EXECUTE_QUERY;
          :GLOBAL.AUTOQUERY:='FALSE';
  END IF;



Add a PRE-QUERY-trigger to your block EMPLOYEES with the following code:

    IF :GLOBAL.AUTOQUERY='TRUE' THEN
       :EMPLOYEES.TXTDEPTNO:=:GLOBAL.TXTDEPTNO;
    END IF;



-----------And also try this on sql prompt u will get some idea----

DECLARE
      v_empno employees.employee_id%TYPE;
      v_name employees.last_name%TYPE;
  CURSOR emp_cursor IS
         SELECT employee_id,
                last_name
                FROM employees
                WHERE department_id = 
                &p_deptno;
   BEGIN
          OPEN emp_cursor;
          LOOP
          FETCH emp_cursor INTO v_empno, v_name;
   EXIT WHEN emp_cursor%NOTFOUND;

          DBMS_OUTPUT.PUT_LINE
          ('The number of employee is '
          ||
            TO_CHAR (v_empno)
          ||
           ' and his last name is '
          || TO_CHAR(v_name));
   END LOOP;
         CLOSE emp_cursor;
   END;
/

hope u got something from this

[Updated on: Mon, 26 March 2012 08:59]

Report message to a moderator

Re: Restrict the user to access the records [message #548830 is a reply to message #548825] Mon, 26 March 2012 09:15 Go to previous messageGo to next message
kandimalla
Messages: 17
Registered: November 2011
Location: Bahrain
Junior Member

Thank u very much for your reply....
Let me explain ........
For example.....
Form having database block based on emp table....
Form is for Data entry......User can enter the data empno,ename,sal and deptno and save......
U know that in Oracle Apps, user can querry the records in Forms using F11 and Ctrl F11.......
Now, i have to restrict the user that he can access the Dept10 employees only with querrying......
and programatically only i have to set this......

Thank u very much for your reply....




Re: Restrict the user to access the records [message #548831 is a reply to message #548830] Mon, 26 March 2012 09:48 Go to previous messageGo to next message
kandimalla
Messages: 17
Registered: November 2011
Location: Bahrain
Junior Member


Thank u very much frds.......I got it np
Re: Restrict the user to access the records [message #548832 is a reply to message #548830] Mon, 26 March 2012 09:55 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
download and run
Re: Restrict the user to access the records [message #548833 is a reply to message #548832] Mon, 26 March 2012 10:05 Go to previous messageGo to next message
kandimalla
Messages: 17
Registered: November 2011
Location: Bahrain
Junior Member

Thank you Very much baba.......
For me i should not use like what u explained, Because no. of diffrent users access the same form and block from different responsibilities, so i should not set the where clause in property pellet of block.....
For this what i got is......
when new form instance, i use
if responsibility_id = 'xxx' then
set_block_property('',where_clause,)
end if;
now when ever the user querried the records, he gets that particular records only..............

Re: Restrict the user to access the records [message #548834 is a reply to message #548832] Mon, 26 March 2012 10:05 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
you are always welcome we are always here to help new commers and learning from seniors. it was so simple in block-->property

"WHERE Clause"


Regards Enjoy Cool
Re: Restrict the user to access the records [message #548835 is a reply to message #548834] Mon, 26 March 2012 10:11 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
use PRE-QUERY

 IF :CONTROL.A IS NOT NULL THEN 
       SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.JOB LIKE :CONTROL.A');
   END IF;
   
   IF :CONTROL.B IS NOT NULL THEN 
       SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.SAL LIKE :CONTROL.B');
     ELSE
       SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,'EMP.JOB LIKE :CONTROL.A OR EMP.SAL LIKE :CONTROL.B');
   END IF;




AND MAKE ONE CONTROL BLOCK ----->ONE BUTTON ---->AND WRITE THIS

GO_BLOCK('EMP');
EXECUTE_QUERY;


Hope u got it now


[Updated on: Mon, 26 March 2012 10:14]

Report message to a moderator

Re: Restrict the user to access the records [message #548836 is a reply to message #548835] Mon, 26 March 2012 10:23 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
if you are still facing any problem i dont think this time u should face any problem everything is clear despite this any problem then let me know i will make fmb file according to your requirement




Best Regards Cool
Re: Restrict the user to access the records [message #548837 is a reply to message #548836] Mon, 26 March 2012 10:31 Go to previous messageGo to next message
kandimalla
Messages: 17
Registered: November 2011
Location: Bahrain
Junior Member



Thank you very much Baba.....
Problem solved..........
Re: Restrict the user to access the records [message #548838 is a reply to message #548837] Mon, 26 March 2012 10:41 Go to previous message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
Now Cheer Laughing Cool
Previous Topic: Two Languages in Form 6i
Next Topic: sending email from forms with report in pdf as an attachment
Goto Forum:
  


Current Time: Wed Aug 20 19:27:30 CDT 2014

Total time taken to generate the page: 0.10607 seconds