Setting Profile and Policies in E-Business Suite r12
Ao extrair dados da base de dados do E-Business Suite, muitas vezes é necessário setar algumas policies e profiles. Isso se deve ao fato de que inúmeras views utilizam essas informações na hora de exibir os dados.
Ferramentas de BI que necessitam extrair informações dessas views, devem antes obter informações sobre Application Type, Responsibilities, Org IDs e tantas outras variáveis e colocá-las na sessão corrente antes de executar as consultas. Para facilitar esse trabalho, criei a package abaixo.
A função dessa package é facilitar na obtenção das informações necessárias para o set dos profiles e policies. Além de permitir a busca de algumas informações que são importantes para as queries baseadas no E-Busines Suite.
Basicamente possui as seguintes funcionalidades:
- getUserID: Retorna o user ID do usuário;
- getResponsibilities: Retorna um SYS_REFCURSOR com a lista de Responsibilities de um usuário;
- setProfilePolicies: Seta alguns profiles e policies no E-Business Suite.
Existem ainda a getORGID (obtém o ORG ID da Responsibility/Application) e getApplicationType (Aqui utilizo para identificar se é Receivables ou Payables) que estão com acesso privado, mas nada impede de torná-las públicas.
Para criá-la, você deve ter acesso ao objetos do FOUNDATION do E-Business Suite
CREATE OR REPLACE PACKAGE sla_profile AS
FUNCTION getUserID
(
pUserName VARCHAR2
)
RETURN FND_USER.user_id%TYPE;
FUNCTION getResponsibilities
(
pUserName VARCHAR2
)
RETURN SYS_REFCURSOR;
PROCEDURE setProfilePolicies
(
pUserName IN VARCHAR2
,pResponsibilityID IN FND_RESPONSIBILITY_VL.responsibility_id%TYPE
,pApplicationID IN FND_RESPONSIBILITY_VL.application_id%TYPE DEFAULT NULL
,pResult OUT VARCHAR2
);
END sla_profile;
/*******************************************************/
/** **/
/** Package SLA_PROFILE **/
/** Author: Sergio Willians **/
/** Created: 01/09/2016 **/
/** **/
/** Objetivo: Set Profiles and Policies in E-Business **/
/** Suite and return mandatory information to setting **/
/** **/
/*******************************************************/
/** **/
/** - getUserID: Return User ID **/
/** - getResponsibilities: Return List with user res- **/
/** ponsibilities **/
/** - setProfilePolicies: Set Profile and Policies in **/
/** EBS according user/responsibility **/
/** **/
/*******************************************************/
CREATE OR REPLACE PACKAGE BODY sla_profile AS
-- Variables
vStep VARCHAR2(2000);
vAppID FND_RESPONSIBILITY_VL.application_id%TYPE;
--
-- Get User ID From EBS
--
FUNCTION getUserID
(
pUserName VARCHAR2
)
RETURN FND_USER.user_id%TYPE
IS
userID FND_USER.user_id%TYPE;
BEGIN
vStep := ' getUserID ';
BEGIN
SELECT a.user_id
INTO userID
FROM fnd_user a
WHERE a.user_name = pUserName;
EXCEPTION
WHEN NO_DATA_FOUND THEN
userID := -2;
WHEN OTHERS THEN
userID := -2;
END;
RETURN(userID);
END getUserID;
--
-- Get Org ID
--
FUNCTION getOrgID
(
pApplicationID IN FND_RESPONSIBILITY_VL.application_id%TYPE
,pResponsibilityID IN FND_RESPONSIBILITY_VL.responsibility_id%TYPE
)
RETURN fnd_profile_option_values.profile_option_value%TYPE
IS
vOrgID fnd_profile_option_values.profile_option_value%TYPE;
BEGIN
vStep := ' getOrgID ';
BEGIN
SELECT fpov.profile_option_value
,fr.application_id
INTO vOrgID
,vAppID
FROM fnd_profile_option_values fpov
,fnd_profile_options fpo
,fnd_profile_options_tl fpot
,fnd_responsibility_tl fr
WHERE fpo.profile_option_name = fpot.profile_option_name
AND fpo.profile_option_id = fpov.profile_option_id
AND fr.responsibility_id(+) = fpov.level_value
AND fpot.profile_option_name = 'ORG_ID'
AND fr.application_id = NVL
(
pApplicationID
,fr.application_id
)
AND fr.responsibility_id = pResponsibilityID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vOrgID := 0;
WHEN OTHERS THEN
vOrgID := 0;
END;
RETURN(vOrgID);
END getOrgID;
--
-- Get Application Type
--
FUNCTION getApplicationType
(
pApplicationID FND_RESPONSIBILITY_VL.application_id%TYPE
)
RETURN FND_APPLICATION_TL.application_name%TYPE
IS
vApplicationType FND_APPLICATION_TL.application_name%TYPE;
BEGIN
vStep := ' getApplicationType ';
BEGIN
SELECT a.application_name
INTO vApplicationType
FROM fnd_application_tl a
WHERE a.application_id = NVL
(
pApplicationID
,vAppID
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
vApplicationType := NULL;
WHEN OTHERS THEN
vApplicationType := NULL;
END;
RETURN(vApplicationType);
END getApplicationType;
--
-- Get All User Responsibilities
--
FUNCTION getResponsibilities
(
pUserName VARCHAR2
)
RETURN SYS_REFCURSOR
IS
vResult SYS_REFCURSOR;
BEGIN
vStep := ' getResponsibilities ';
BEGIN
OPEN vResult FOR
SELECT UNIQUE
fu.user_id ,
fu.user_name user_name ,
fr.responsibility_id,
fl.responsibility_name,
fr.application_id,
fa.application_name application
FROM fnd_user fu,
fnd_user_resp_groups fg ,
fnd_application_tl fa ,
fnd_responsibility_tl fl,
fnd_responsibility fr
WHERE fg.user_id(+) = fu. user_id
AND fg.responsibility_application_id = fa.application_id
AND fl.application_id = fr.application_id
AND fl.responsibility_id = fr.responsibility_id
AND fa.application_id = fr.application_id
AND fg.responsibility_id = fr.responsibility_id
AND fu.user_id = getUserID
(
pUserName
)
AND fg.end_date IS NULL
ORDER BY fu.user_id,
fa.application_name ,
fr.responsibility_key ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vResult := NULL;
WHEN OTHERS THEN
vResult := NULL;
END;
RETURN(vResult);
END getResponsibilities;
--
-- Set Profile Info
--
PROCEDURE setProfilePolicies
(
pUserName IN VARCHAR2
,pResponsibilityID IN FND_RESPONSIBILITY_VL.responsibility_id%TYPE
,pApplicationID IN FND_RESPONSIBILITY_VL.application_id%TYPE DEFAULT NULL
,pResult OUT VARCHAR2
)
IS
BEGIN
vStep := ' setProfilePolicies ';
-- Set Org ID in context
FND_CLIENT_INFO.set_org_context
(
getOrgID
(
pApplicationID => pApplicationID
,pResponsibilityID => pResponsibilityID
)
);
-- Initialize Profile
fnd_global.apps_initialize
(
user_id => getUserID
(
pUserName
)
,resp_id => pResponsibilityID
,resp_appl_id => NVL
(
pApplicationID
,vAppID
)
);
-- Set Policies
IF getApplicationType(pApplicationID) = 'Receivables'
THEN
MO_GLOBAL.init('AR');
ELSIF getApplicationType(pApplicationID) = 'Payables'
THEN
MO_GLOBAL.init('SQLAP');
END IF;
EXCEPTION
WHEN OTHERS THEN
pResult := DBMS_UTILITY.FORMAT_ERROR_STACK;
END setProfilePolicies;
BEGIN
NULL;
END sla_profile;A idéia de disponibilizar a Package aqui e não em um anexo, é devido ao fato de que ela praticamente é auto-explicativa e também para que possa ser feita uma melhor análise. Por ser uma primeira versão, muitas outras funcionalidades podem ser implementadas.
Abaixo um script simples para testes. Substitua <user> pelo usuário e execute o script:
DECLARE
vRefCursor SYS_REFCURSOR;
vResult VARCHAR2(2000);
TYPE tResp IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
vResp tResp;
BEGIN
-- Get List with all Responsibilities
vRefCursor := SLA_PROFILE.getResponsibilities('<user>');
-- Fetch an Print Result
LOOP
FETCH vRefCursor INTO vResp(1),vResp(2),vResp(3),vResp(4),vResp(5),vResp(6);
EXIT WHEN vRefCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vResp(4));
END LOOP;
CLOSE vRefCursor;
-- Set Profiles
SLA_PROFILE.setProfilePolicies('<user>',vResp(3),NULL,vResult);
DBMS_OUTPUT.put_line(vResult);
END;
/
Ex:
SQL>
1 Alert Manager
2 Application Developer
3 Desktop Integration
4 Diagnostics Tools ResponsabilityPara verificar se o setProfilePolicies funcionou:
SELECT FND_PROFILE.value('ORG_ID') ORG_ID
,FND_PROFILE.value('USER_ID') USER_ID
,FND_PROFILE.value('GL_SET_OF_BKS_ID') GL_SET_OF_BKS
FROM dual
/Espero que essa package possa ser útil a outros, assim como está sendo para mim. Caso tenha alguma idéia de como melhorá-la, conto com sua opinião nos comentários, assim como dúvidas e críticas também serão bem vindas ! 🙂
Um grande abraço
