===== Start script =====
/* $Header: fnd_initialize.sql 11.5.10 2 15.04.2008 oebs-ru $ */
-- Назначение
-- Ининциализации APPS-овой сессии
-- Параметры
-- 1 - имя пользователя OEBS. Регистр знаков не важен
-- 2 - имя полномочий.
-- Регистр знаков важен
-- Используется LANGUAGE от текущей сессии
-- Возможно указание шаблона (для LIKE), типа 'Сис%админ%'
-- Если искомому шаблону удовлетворяют несколько полномочий,
-- то будет выбрано случайное
-- Особенности
-- Необрабатывается ситуация, когда одно и тоже имя полномочий используется
-- в разных приложениях.
-- В случае успешной инициализации, в логе скрипта выводятся имена
-- и ID (в скобках) для пользователя, полномочий и приложения
--
SET VERIFY OFF
SET FEEDBACK OFF
DEFINE user_name = "&&1"
DEFINE resp_name = "&&2"
DECLARE
user_name_v fnd_user.user_name%TYPE := '&user_name';
resp_name_v fnd_responsibility_tl.responsibility_name%TYPE := '&resp_name';
--
user_id_v fnd_user.user_id%TYPE;
responsibility_id_v fnd_responsibility_tl.responsibility_id%TYPE;
responsibility_name_v fnd_responsibility_tl.responsibility_name%TYPE;
application_id_v fnd_responsibility_tl.application_id%TYPE;
application_name_v fnd_application_tl.application_name%TYPE;
security_group_id_v fnd_security_groups.security_group_id%TYPE
:= Fnd_Global.security_group_id;
err_msg_v VARCHAR2(255);
BEGIN
err_msg_v := 'User "'||user_name_v||'" does not exist or inactive';
SELECT fu.user_id
INTO user_id_v
FROM fnd_user fu
WHERE fu.user_name = UPPER(user_name_v)
AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE);
err_msg_v := 'Responsibility "'||resp_name_v||'" does not exist or inactive';
SELECT frt.application_id
,frt.responsibility_id
,frt.responsibility_name
,(SELECT fav.application_name
FROM fnd_application_vl fav
WHERE fav.application_id = fr.application_id
) AS application_name
INTO application_id_v
,responsibility_id_v
,responsibility_name_v
,application_name_v
FROM fnd_responsibility_tl frt
,fnd_responsibility fr
WHERE fr.application_id = frt.application_id
AND fr.responsibility_id = frt.responsibility_id
AND SYSDATE BETWEEN fr.start_date AND NVL(fr.end_date, SYSDATE)
AND frt.responsibility_name LIKE resp_name_v
AND frt.LANGUAGE = USERENV ('LANG')
AND ROWNUM = 1;
err_msg_v := 'Error in Fnd_Global.apps_initialize';
Fnd_Global.apps_initialize (
user_id => user_id_v
,resp_id => responsibility_id_v
,resp_appl_id => application_id_v
,security_group_id => security_group_id_v
);
DBMS_OUTPUT.PUT_LINE ('Session initialized');
DBMS_OUTPUT.PUT_LINE (
' User : '||UPPER(user_name_v)||' ('||user_id_v ||')'
);
DBMS_OUTPUT.PUT_LINE (
' Responsibility: '||responsibility_name_v||' ('||responsibility_id_v||')'
);
DBMS_OUTPUT.PUT_LINE (SUBSTR(
' Application : '||application_name_v||' ('||application_id_v||')'
,1,255));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (err_msg_v);
DBMS_OUTPUT.PUT_LINE (SUBSTR(sqlerrm,1,255));
END;
/
SET FEEDBACK ON
UNDEFINE user_name
UNDEFINE resp_name
===== End script =====
Скрипт
Сообщения
