Oracle Apps Technical Consultant

Sunday, 15 May 2016

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;
/

Monday, 21 March 2016

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.

Monday, 14 March 2016

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
);

Friday, 11 March 2016

Bulk binds with error handling

declare
cursor curr_cur is select * from emp;
type emp_tab is table of curr_cur%rowtype;
emp_array emp_tab;
errors number;
dml_errors exception;
pragma exception_init(dml_errors,-24381);
begin
open curr_cur;
loop
fetch curr_cur bulk collect into emp_array limit 35;
forall i in emp_array.first..emp_array.last save exceptions
insert into emp_temp3 values emp_array(i);
exit when curr_cur%notfound;
end loop;
close curr_cur;
exception
when dml_errors then
errors:=sql%bulk_exceptions.count;
dbms_output.put_line('number of statements failed are '||errors);
for i in 1..errors loop
dbms_output.put_line('error #'||i|| ' is occured during '|| 'iterations #' ||sql%bulk_exceptions(i).error_index);
dbms_output.put_line('error message is ' || SQLERRM(-sql%bulk_exceptions(i).error_code));
end loop;
end;
/

How to find the users attached to the particular responsibility?

select distinct fu.USER_ID
,fu.USER_NAME
,frt.RESPONSIBILITY_ID
,frt.RESPONSIBILITY_NAME
,fu.CREATION_DATE
from fnd_user fu
,fnd_responsibility_tl frt
,fnd_user_resp_groups_direct furgd
where 1=1
and frt.RESPONSIBILITY_ID=20420
and fu.USER_ID=furgd.USER_ID
and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
order by fu.CREATION_DATE desc

(OR)

select distinct fu.USER_ID
,fu.USER_NAME
,frt.RESPONSIBILITY_ID
,frt.RESPONSIBILITY_NAME
,fu.CREATION_DATE
from fnd_user fu
,fnd_responsibility_tl frt
,fnd_user_resp_groups_direct furgd
where 1=1
and frt.RESPONSIBILITY_ID=:lvResp_id
and fu.USER_ID=furgd.USER_ID
and frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
order by fu.CREATION_DATE desc