What is Pragma Restrict References ? when we use it ? what is WNDS, RNDS, WNPS, RNPS, TRUST ?

Pragma Restrict Reference:
----------------------------------

• The RESTRICT_REFERENCES  pragma asserts  that a user defined sub program does not read or write database tables or package variables.

• Sub programs that read or write database tables or package variables are difficult to optimize, because any call to the sub program might produce different results or encounter errors.
• To restrict these unexpected results we use pragma RESTRICT_REFERENCES.

PRAGMA:

--------------

                       signifies that the statement is pragma ( compiler directive). pragmas are processed at compile time, not at run time. they pass information to the compiler at compile time.

Syntax for PRAGMA RESTRICT_REFERENCES:

Sub program name:
------------------------
                                     The name of user defined sub program, usually a function. if sub program name is over loaded, the pragma applies only to the most recent sub program declaration.


Default:
----------

                   Specifies that the pragma applies to all sub programs in the package specification or object type specification. (including the system defined construct for object types).

 you can still declare the pragma for individual subprograms, overriding the default pragma.

RNDS:
---------      Asserts that the sub program reads no database state (doesn't query database tables).

WNDS:
----------
                 Asserts that the sub program writes no database state (doesn't modify database tables).

RNPS:
---------
            Asserts that the sub program reads no PACKAGE state (doesn't reference the value of the package variables).

you can not specify RNPS, if the sub program invokes the SQLCODE OR SQLERRM function.


WNPS:
----------
              Asserts that the sub program writes no PACKAGE state (doesn't change the value of the package variables).

you can not specify RNPS, if the sub program invokes the SQLCODE OR SQLERRM function.

TRUST:
-----------
                Asserts that the sub program can be trusted not to voilate one or more rules.
       when you specify TRUST, the sub program body is not checked for violations of the constraints listed in the pragma. The suprogram is trusted not to voilate them skipping these checks improves the performance.

TRUST is needed for functions written in c, java that are invoked from the plsql, since plsql can't verify them at run time.

How to create a employee by using hr employee API

CREATE OR REPLACE Procedure APPS.K_EMP11(errbuf   out varchar2,

                                    retcode  out varchar2) as

cursor c1 is select * from EMP_STAGE;

L_PID NUMBER(30);

l_AID NUMBER(30);

L_OVN NUMBER(9);

L_AOVN NUMBER(9);

L_ESD DATE;

L_EED DATE;

L_FULL_NAME VARCHAR2(100);

L_CID NUMBER(9);

L_AS NUMBER(9);

L_AN VARCHAR2(100);

L_CW  BOOLEAN;

L_PW BOOLEAN;

L_HW BOOLEAN;

L_EMPNO VARCHAR2(20);

l_bid number(9);

l_flag varchar2(1);

l_count number(9) default 0;

Begin

For x1 in c1 loop

l_count:=l_count+1;

l_flag :='A';

--Business Group ID Validation

Begin

select business_group_id

into   l_bid

from  HRFV_BUSINESS_GROUPS

where business_group_id = X1.BUSINESS_GROUP_ID;

Exception

When others then

l_flag :='E';

Fnd_File.put_line(Fnd_File.log,'Invalid Business Group ID'||'Record Number ='||l_count);

End;

If(l_flag !='E') then

HR_EMPLOYEE_API.CREATE_EMPLOYEE(p_validate                 => false

                                ,p_hire_date                => TRUNC(SYSDATE)

                                ,p_business_group_id       =>x1.BUSINESS_GROUP_ID

                                ,p_last_name                =>x1.last_name

                                ,p_sex                      =>x1.sex

                                ,p_person_type_id           =>x1.PERSON_TYPE_ID

                                ,p_date_of_birth            =>x1.DATE_OF_BIRTH

                                ,p_email_address            =>x1.email

                                ,p_employee_number          =>L_EMPNO

                                ,p_first_name               =>x1.first_name

                                ,p_marital_status          =>x1.MARITAL_STATUS

                                ,p_person_id               =>L_PID

                                ,p_assignment_id           =>L_AID

                                ,p_per_object_version_number    => L_OVN

                                ,p_asg_object_version_number    =>L_AOVN

                                ,p_per_effective_start_date    =>L_ESD

                                ,p_per_effective_end_date      =>L_EED

                                ,p_full_name                   =>L_FULL_NAME

                                ,p_per_comment_id              =>L_CID

                                ,p_assignment_sequence         =>L_AS

                                ,p_assignment_number           =>L_AN

                                ,p_name_combination_warning     =>L_CW

                                ,p_assign_payroll_warning       =>L_PW

                                ,p_orig_hire_warning            =>L_HW

                                ,p_national_identifier          => x1.SSID);

End If;

End Loop;

End;

/

How to use decode in where clause ?

select 
       emp.empno
      ,emp.ename
      ,emp.deptno
      ,dept.deptno
      ,dept.dname 
from  emp
     ,dept
where emp.deptno=dept.deptno
  and (decode(emp.deptno,10,'ACCOUNTING')=dept.dname
      or
       decode(emp.deptno,20,'RESEARCH')=dept.dname
      or
       decode(emp.deptno,30,'SALES','OPERATIONS')=dept.dname);

How to use case in where clause ?

select a.col1
      ,a.col2
      ,b.col1
      ,b.col2
from A a
    ,B b
where a.bookid=b.bookid
   and 1=
   (
   case
      when a.secid=0 and a.prodid=b.prodid then 1
      when a.secid=b.secid then 1
      else
         0
   end
   );

Bulk binds with error handling

1. declare
2. cursor curr_cur is select * from emp;
3. type emp_tab is table of curr_cur%rowtype;
4. emp_array emp_tab;
5. errors number;
6. dml_errors exception;
7. pragma exception_init(dml_errors,-24381);
8. begin
9. open curr_cur;
10. loop
11. fetch curr_cur  bulk collect into emp_array limit 35;
12. forall  i in emp_array.first..emp_array.last save exceptions
13. insert into emp_temp3 values emp_array(i);
14. exit when curr_cur%notfound;
15. end loop;
16. close curr_cur;
17. exception
18. when dml_errors then
19. errors:=sql%bulk_exceptions.count;
20. dbms_output.put_line('number of statements failed are '||errors);
21. for i in 1..errors loop
22. dbms_output.put_line('error #'||i|| ' is occured during '|| 'iterations #' ||sql%bulk_exceptions(i).error_index);
23. dbms_output.put_line('error  message is ' || SQLERRM(-sql%bulk_exceptions(i).error_code));
24. end loop;
25. end;
26. /

How to find the users attached to the particular responsibility?

1. select distinct fu.USER_ID
2.                ,fu.USER_NAME
3.                ,frt.RESPONSIBILITY_ID
4.                ,frt.RESPONSIBILITY_NAME
5.                ,fu.CREATION_DATE
6. from fnd_user fu
7.     ,fnd_responsibility_tl frt
8.     ,fnd_user_resp_groups_direct furgd
9. where 1=1
10.    and frt.RESPONSIBILITY_ID=20420
11.    and fu.USER_ID=furgd.USER_ID
12.    and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
13. order by fu.CREATION_DATE desc

      (OR)

1. select distinct fu.USER_ID
2.                ,fu.USER_NAME
3.                ,frt.RESPONSIBILITY_ID
4.                ,frt.RESPONSIBILITY_NAME
5.                ,fu.CREATION_DATE
6. from fnd_user fu
7.     ,fnd_responsibility_tl frt
8.     ,fnd_user_resp_groups_direct furgd
9. where 1=1
10.    and frt.RESPONSIBILITY_ID=:lvResp_id   
11.    and fu.USER_ID=furgd.USER_ID
12.    and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
13. order by fu.CREATION_DATE desc