Infolinks

Monday, 16 July 2012

SCRIPTS1

Terminating Concurrent program sessions :

Hi ALL,
One way of finding the session id for concurrent is through fnd_cocurrent_request...as discussed in my earlier post
But there is another easy way...


select sid,serial#,MODULE,ACTION,status from V$session

the module will contain the concurrent program short name...If only one instance of the concurrent program is running
we can easily identify the sessions of it..

If we terminate the program and the session not got released..simply find th session

and use the command
alter sytem kill session 'SID,SERIAL#'
Please the values you got from the above query  


======================

Program for Formatting options for fnd_file.put_line.out and Log :

create or replace procedure XXBOB_FND_MULTILINE_PRINT_PROC(P_TEXT varchar2,P_FRAME_SIZE NUMBER )as
v_text varchar2(1000) ;
v_cut number;
v_print_text varchar2(1000);
v_next_start number;
v_tot_length number;
v_cut_length number;
v_fram_size number :=4;
x number;
begin
v_text:=P_TEXT;
If p_frame_size is null then
V_fram_size:=60;
end if;
v_fram_size:=p_frame_size;
v_cut:=instr(v_text,' ',v_fram_size);
v_tot_length:=length(v_text);
v_print_text:=substr( v_text,1,v_cut);
v_next_start:=v_cut+1;
dbms_output.put_line('Total Length:'v_tot_length);
x:=length(v_print_Text);
dbms_output.put_line('X:'X);
if length(v_text)>v_fram_size then
loop
--dbms_output.put_line(v_print_Text);
fnd_file.put_line(fnd_file.output,v_print_Text);
--dbms_output.put_line(v_print_Text);
v_cut:=instr(v_text,' ',v_next_start+v_fram_size);
if v_cut=0 then
v_cut:=v_tot_length+1;
end if;
v_cut_length:=v_cut-v_next_start;
--dbms_output.put_line('v_next_start:'v_next_start);
--dbms_output.put_line('v_cut:'v_cut);
--dbms_output.put_line('v_lenghtcut:'v_cut_length);
v_print_text:=substr( v_text,v_next_start,v_cut_length);
x:=nvl(length(v_print_Text),0);
--dbms_output.put_line('X:'X);
if xv_next_start and v_cut!=v_tot_length then
v_next_start:=v_cut+1;
end if;
exit when x
end loop;
else
fnd_file.put_line(fnd_file.output,v_Text);
--dbms_output.put_line(v_Text);
end if;
end;

====================================

to create users and attach responsibilities ?

 Hi all,
We can fnd_user_pkg api to create users and attach responsibilities all at one shot..
Here i am providing the code which will update or create new users and their repsonsibilities
This script need to be improved a bit more to meet your changes
/******************************************************************************/

create or replace package body XX_FND_USER_PKG as
procedure create_user(p_user_name in varchar2,
p_emp_num in varchar2,
p_resp_id in number) as
v_user_present varchar2(1):='N';
v_err varchar2(1):='N';
v_user_id number;
v_resp_present varchar2(1):='N';
v_empnum_err varchar2(1):='N';
v_resp_key varchar2(50);
v_application_name varchar2(50);
v_person_id number;
v_erromsg varchar2(1000);
begin
begin
select user_id
into v_user_id
from fnd_user
where user_name=p_user_name and sysdate>nvl(end_Date,trunc(sysdate));
exception
when no_data_found then
v_user_present:='N';
end;
if v_user_id is null then
begin
select person_id
into v_person_id
from per_all_people_f papf
where employee_number=p_emp_num
and sysdate between effective_start_Date and effective_End_Date
and rownum=1;
exception
when others then
v_empnum_err :='Y';
end;
if v_empnum_err='N' then
fnd_user_pkg.createuser(x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => 'ganjam123'
,x_employee_id=>v_person_id);
end if;
end if;
if v_user_id is not null then
begin
select 'Y'
into v_resp_present
from fnd_user_resp_groups_direct
where user_id=v_user_id
and responsibility_id=p_resp_id;
exception
when no_data_found then
v_resp_present:='N';
end;
end if;
if v_resp_present ='N' then
begin
select responsibility_key,fa.application_short_name
into v_resp_key,v_application_name
from FND_RESPONSIBILITY FR,
fnd_application FA
where FR.application_id=fa.application_id
and responsibility_id=p_resp_id
and rownum=1;
exception
when others then
v_err:='Y';
end;
if v_err ='N' then
fnd_user_pkg.addresp(username => p_user_name
,resp_app => v_application_name
,resp_key => v_resp_key
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE
,end_date => null);
end if;
end if;

end;
end XX_FND_USER_PKG;/ 

No comments:

Post a Comment