what are the tables effected by employee creation ?

Tables effected by employee creation is

PER_ALL_PEOPLE_F

PER_PEOPLE_F
PER_ALL_ASSIGNMENTS_F

HR_EMPLOYEES

PER_PERIODS_OF_SERVICES

How to find out which user hook package and procedure have to use for the requirement ?

We have to find out that what user hook package and procedure we have to use to satisfy the requirement.

for that we have to know on which table we have to do modifications, after knowing that
we can run below query to find out the user hook package and procedure.

Here for example I am taking per_pay_proposals table.

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or  ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

How to create a user hook in Oracle HRMS ?

step 1:
=======

select ahk.api_hook_id,
ahk.hook_package,
ahk.hook_procedure,
ahm.API_MODULE_ID
from hr_api_hooks ahk,
hr_api_modules ahm
where (ahm.module_name='PER_PAY_PROPOSALS'
or  ahm.module_name='PER_PAY_PROPOSALS')
and ahm.api_module_type = 'RH'
and ahk.api_hook_type = 'AI'
and ahk.api_module_id=ahm.api_module_id;

step2:
======

create or replace package XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id               in number,
   p_assignment_id                 in number,
   p_business_group_id             in number,
   p_change_date                   in date,
   p_comments                      in varchar2,
   p_next_sal_review_date          in date,
   p_proposal_reason               in varchar2,
   p_proposed_salary_n             in number,
   p_forced_ranking                in number,
   p_date_to    in date,
   p_performance_review_id         in number,
   p_attribute_category            in varchar2,
   p_attribute1                    in varchar2,
   p_attribute2                    in varchar2,
   p_attribute3                    in varchar2,
   p_attribute4                    in varchar2,
   p_attribute5                    in varchar2,
   p_attribute6                    in varchar2,
   p_attribute7                    in varchar2,
   p_attribute8                    in varchar2,
   p_attribute9                    in varchar2,
   p_attribute10                   in varchar2,
   p_attribute11                   in varchar2,
   p_attribute12                   in varchar2,
   p_attribute13                   in varchar2,
   p_attribute14                   in varchar2,
   p_attribute15                   in varchar2,
   p_attribute16                   in varchar2,
   p_attribute17                   in varchar2,
   p_attribute18                   in varchar2,
   p_attribute19                   in varchar2,
   p_attribute20                   in varchar2,
   p_object_version_number         in number,
   p_multiple_components           in varchar2,
   p_approved                      in varchar2,
   p_inv_next_sal_date_warning     in boolean,
   p_proposed_salary_warning    in boolean,
   p_approved_warning              in boolean,
   p_payroll_warning    in boolean);
end XX_SEH_PKG_HK_SAL;

create or replace package body XX_SS_PKG_HK_SAL is
PROCEDURE XX_HK_SAL(p_pay_proposal_id               in number,
   p_assignment_id                 in number,
   p_business_group_id             in number,
   p_change_date                   in date,
   p_comments                      in varchar2,
   p_next_sal_review_date          in date,
   p_proposal_reason               in varchar2,
   p_proposed_salary_n             in number,
   p_forced_ranking                in number,
   p_date_to    in date,
   p_performance_review_id         in number,
   p_attribute_category            in varchar2,
   p_attribute1                    in varchar2,
   p_attribute2                    in varchar2,
   p_attribute3                    in varchar2,
   p_attribute4                    in varchar2,
   p_attribute5                    in varchar2,
   p_attribute6                    in varchar2,
   p_attribute7                    in varchar2,
   p_attribute8                    in varchar2,
   p_attribute9                    in varchar2,
   p_attribute10                   in varchar2,
   p_attribute11                   in varchar2,
   p_attribute12                   in varchar2,
   p_attribute13                   in varchar2,
   p_attribute14                   in varchar2,
   p_attribute15                   in varchar2,
   p_attribute16                   in varchar2,
   p_attribute17                   in varchar2,
   p_attribute18                   in varchar2,
   p_attribute19                   in varchar2,
   p_attribute20                   in varchar2,
   p_object_version_number         in number,
   p_multiple_components           in varchar2,
   p_approved                      in varchar2,
   p_inv_next_sal_date_warning     in boolean,
   p_proposed_salary_warning    in boolean,
   p_approved_warning              in boolean,
   p_payroll_warning    in boolean)
   is
   nl_pay_proposal_id number(20);
   nl_assignment_id number(20);
   lv_pay_proposal_id number(10);
   lv_count number(1);
   begin

   select count(pps.PAY_PROPOSAL_ID)
          into lv_count
   from per_pay_proposals pps
   where pps.PAY_PROPOSAL_ID=p_pay_proposal_id;

   if lv_count=1 then
   update per_pay_proposals pps1 set pps1.PROPOSED_SALARY= ROUND(pps1.PROPOSED_SALARY,2)
   where pps1.PAY_PROPOSAL_ID=p_pay_proposal_id;
   commit;
   else
   fnd_file.put_line(fnd_file.log,'Invalid Pay Proposal Id');
   end if;
   end XX_HK_SAL;
   end XX_SEH_PKG_HK_SAL;
   /



   select * from HR_API_HOOK_CALLS;


step 3:
=======


DECLARE
L_API_HOOK_ID NUMBER:= 3035;
L_API_HOOK_CALL_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_SEQUENCE NUMBER;

BEGIN

SELECT HR_API_HOOKS_S.NEXTVAL
INTO L_SEQUENCE
FROM DUAL;

HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL

(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => TO_DATE('01-JAN-1950','DD-MON-YYYY'),
P_API_HOOK_ID =>L_API_HOOK_ID,
P_API_HOOK_CALL_TYPE => 'PP',
P_SEQUENCE => L_SEQUENCE,
P_ENABLED_FLAG => 'Y',
P_CALL_PACKAGE => 'XX_SS_PKG_HK_SAL',
P_CALL_PROCEDURE => 'XX_HK_SAL',
P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID,
P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.PUT_LINE('L_API_HOOK_CALL_ID '|| L_API_HOOK_CALL_ID);
END;
/




select * from HR_API_HOOK_CALLS where call_package='XX_SS_PKG_HK_SAL';

select * from HR_API_HOOK_CALLS where call_procedure='XX_HK_SAL';

step 4:
=======


declare
l_api_module_id number := 1416; --Value 1416 is derived from Step 1 above using following query
begin
hr_api_user_hooks_utility.create_hooks_one_module(l_api_module_id);
dbms_output.put_line('Success');
exception when others then
dbms_output.put_line('Exception : '||SQLERRM);
end;
/

How to delete a user hook in Oracle apps HRMS?

Before going to delete a user hook we have to find out hook call id and object version number

-> We can find out those values by using the below the query.

SELECT api_hook_call_id,object_version_number
FROM HR_API_HOOK_CALLS
WHERE call_package = 'XX_SS_PKG_HK_SAL'
AND call_procedure = UPPER('XX_HK_SAL');

After finding the api hook call id and OVN

Run the following program

BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1539,
p_object_version_number =>5
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
commit;
END;
/

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