How to preserve Leading zeros in XML Publisher Excel Ouputs

In XML Pub Excel Reports excel removes the leading Zeros and prints the whole number. There are many instances where we need to print the zeros for eg item numbers starting with 0.

There are many ways to achieve this, one of the simple ways is to convert the number to character by adding space at the end.

concatenate two spaces after the xml tag as shown below in your template.

<?concat(<XMLTag>,’  ‘)?>

Downside of it is you will not be able to use excel features like sum , count etc on this specific column.

Advertisements

Query to Get Profile Values From Oracle Backend

SELECT fpos.profile_option_name ,
fpots.user_profile_option_name ,
DECODE (fpovs.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User ‘,
10005, ‘Server’,
10006, ‘OU’,
‘None’)
Level_Name,
DECODE (TO_CHAR (fpovs.level_id),
‘10001’, ‘None’,
‘10002’, fa.application_short_name,
‘10003’, frt.responsibility_name,
‘10004’, fu.user_name,
‘10005’, fn.node_name,
‘10006’, hou.name,
‘None’)
Level_Value,
fpovs.profile_option_value
FROM fnd_profile_options fpos,
fnd_profile_option_values fpovs,
fnd_profile_options_tl fpots,
fnd_user fu,
fnd_application fa,
fnd_responsibility_tl frt,
fnd_nodes fn,
hr_operating_units hou
WHERE fpots.profile_option_name = ‘<Enter Your Profile Option Name here>’
AND fpos.profile_option_id = fpovs.profile_option_id(+)
AND fpos.profile_option_name = fpots.profile_option_name
AND fu.user_id(+) = fpovs.level_value
AND frt.application_id(+) = fpovs.level_value_application_id
AND frt.responsibility_id(+) = fpovs.level_value
AND frt.language(+) = ‘US’
AND fa.application_id(+) = fpovs.level_value
AND fn.node_id(+) = fpovs.level_value
AND hou.organization_id(+) = fpovs.level_value
AND fpots.language = ‘US’

How to generate Session Trace from Toad or SQL Plus

alter session set max_dump_file_size = 50000000;

alter session set sql_trace = true;

========
Run your SQL’s
========
alter session set sql_trace = false;

— Run the following query to find the trc file

select vpar.value || ‘/’ || instance || ‘_ora_’ || ltrim(to_char(vp.spid,’fm99999′)) || ‘.trc’
from v$process vp, v$session vs, v$parameter vpar, v$thread vthr
where vp.addr = vs.paddr
and vs.audsid = userenv(‘sessionid’)
and vpar.name = ‘user_dump_dest’
Once you have the trc file use the standard tkprof procedure to get the tkp / tkprof file

Reset Password from Backend in Oracle Apps

DECLARE
v_chr_user_name fnd_user.user_name%TYPE := UPPER (‘&Enter_User_Name’);
v_chr_new_password VARCHAR2 (30) := ‘&Enter_New_Password’;
v_boo_status BOOLEAN;
BEGIN
v_boo_status :=
fnd_user_pkg.ChangePassword (username => v_chr_user_name,
newpassword => v_chr_new_password);

IF v_boo_status = TRUE
THEN
DBMS_OUTPUT.put_line (
‘Password Reset Successful for ‘ || v_chr_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (
‘Failed to Reset Password’ || SQLCODE || ‘ ‘ || SQLERRM);
END IF;
END;

Kill/Terminate session of an object in Oracle backend

–Get sid and serial# for object
SELECT object_name,
machine,
s.sid,
s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND o.object_name = :object_name

–KILL SESSION SQL:
ALTER SYSTEM KILL SESSION ‘sid, serial#’

How to Add a Responsibility from Backend

PL/SQL Script to be executed from APPS

BEGIN
fnd_user_resp_groups_api.load_row
(x_user_name           => <UserName>,
x_resp_key            => <Resp Key of Responsibility>,
x_app_short_name      => ‘SYSADMIN’,
x_security_group      => ‘STANDARD’,
x_owner               => ‘APPS’,
x_start_date          => SYSDATE,
x_end_date            => NULL,
x_description         => ‘Description’
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (   ‘Resp. not assigned to : ‘
|| ‘USER_NAME’
|| ‘ Error : ‘
|| SQLERRM
);
END;