create or replace package body bsys.sys_env_api
/* ********************************************************************************************
* 업 무 단 위 : 시스템관리
* 패 키 지 : bsys.sys_env_api
* 사 용 목 적 : 환경설정 패키지
* 스크립트명칭 : 시스템관리_패키지_환경정보_생성
* 생 성 일 자 : 2024-02-01
* 생 성 자 : Admin
--------------------------------------------------------------------------------------------
* 수정일자 수정자 수정내역
--------------------------------------------------------------------------------------------
* 2024-02-01 Admin 최초 작성
******************************************************************************************** */
is
/* ********************************************************************************************
* 사 용 목 적 : 환경정보 생성 함수
* 생 성 일 자 : 2024-02-01
* 생 성 자 : Admin
******************************************************************************************** */
procedure set_env_p
(pi_corp_id in number -- 법인id
,pi_user_cd in varchar2 -- 사용자id
,pi_lang_cd in varchar2 default g_session_lang_cd -- 언어코드
)
is
l_rec sys_env%rowtype default null;
l_user_cd varchar2(0050) default upper(pi_user_cd);
l_client_info varchar2(4000) default null;
pragma autonomous_transaction;
begin
-- 세션의 사용자id 생성
l_client_info := sys_context(upper('userenv'), upper('sessionid')) || ':' || l_user_cd;
dbms_application_info.set_module(upper('app'), upper('user_id'));
dbms_application_info.set_client_info(l_client_info);
-- 로그인id를 기준으로 사용자정보 저장
if l_user_cd is null then
null;
else
l_rec.corp_id := nvl(nvl(pi_corp_id, sys_env_f(upper('corp_id'))), 1);
l_rec.lang_cd := upper(nvl(pi_lang_cd, g_session_lang_cd));
l_rec.last_login_timestamp := systimestamp;
-- emp_id 조회
begin
select cu.user_cd
,cu.emp_id
into l_rec.user_cd
,l_rec.emp_id
from com_user cu
where 1 = 1
and cu.corp_id = l_rec.corp_id
and upper(cu.user_cd) = l_user_cd;
exception
when others then
l_rec.emp_id := null;
end;
-- 발령정보
if l_rec.emp_id is null then
l_rec.biz_id := null;
l_rec.dept_id := null;
else
-- 임직원 최종 발령정보
begin
select v.corp_id
,v.biz_id
,v.dept_id
,v.emp_id
into l_rec.corp_id
,l_rec.biz_id
,l_rec.dept_id
,l_rec.emp_id
from (
select ha.corp_id
,ha.biz_id
,ha.dept_id
,ha.emp_id
,ha.asg_num
,max(ha.asg_num) over(partition by ha.corp_id, ha.emp_id) as emp_last_asg
from hrm_assignment ha
where 1 = 1
and ha.emp_id = l_rec.emp_id
) v
where 1=1
and v.emp_last_asg = v.asg_num; -- 최종발령정보
exception
when others then
l_rec.corp_id := null;
l_rec.biz_id := null;
l_rec.dept_id := null;
l_rec.emp_id := null;
end;
end if;
-- 프로파일정보
begin
select nvl(l_rec.corp_id, v.corp_id)
,nvl(l_rec.lang_cd, v.lang_cd)
,nvl(v.decrypto_yn, 'N')
into l_rec.corp_id
,l_rec.lang_cd
,l_rec.decrypto_yn
from (
select max(decode(sp.profile_cd,upper('corp_id') , sp.profile_value, null)) as corp_id
,max(decode(sp.profile_cd,upper('lang_cd') , sp.profile_value, null)) as lang_cd
,max(decode(sp.profile_cd,upper('decrypto_yn'), sp.profile_value, null)) as decrypto_yn
from sys_profile sp
where 1 = 1
and sp.profile_cd in (upper('corp_id'), upper('lang_cd'), upper('decrypto_yn'))
) v
where 1 = 1;
exception
when others then
l_rec.corp_id := sys_get_api.dft_corp_id_f;
l_rec.lang_cd := sys_init_api.c_lang_cd;
l_rec.decrypto_yn := sys_init_api.c_no;
end;
-- 저장
delete from sys_env where user_cd = l_rec.user_cd;
insert into sys_env values l_rec;
commit;
end if;
end set_env_p;
/* ********************************************************************************************
* 사 용 목 적 : 프로파일 값 변경 함수
* 생 성 일 자 : 2024-02-01
* 생 성 자 : Admin
******************************************************************************************** */
procedure set_profile_p
(pi_corp_id in number -- 법인id
,pi_profile_cd in varchar2 -- 프로파일코드
,pi_profile_value in varchar2 -- 프로파일값
)
is
pragma autonomous_transaction;
begin
-- 프로파일 업데이트
update sys_profile sp
set sp.profile_value = trim(upper(pi_profile_value))
where 1 = 1
and sp.corp_id = pi_corp_id
and sp.profile_cd = trim(upper(pi_profile_cd));
-- 저장
commit;
end set_profile_p;
/* ********************************************************************************************
* 사 용 목 적 : 세션 개인정보 조회 함수
* 생 성 일 자 : 2024-02-01
* 생 성 자 : Admin
******************************************************************************************** */
function get_env_f
(pi_env_cd in varchar2 -- 환경변수코드
,pi_lang_cd in varchar2 default g_session_lang_cd -- 언어코드
) return varchar2
is
l_rec sys_env%rowtype default null;
l_env_cd varchar2(0050) default upper(pi_env_cd);
l_client_info varchar2(4000) default null;
l_user_cd varchar2(0050) default null;
l_return_value varchar2(0200) default null;
begin
-- 현재 세션의 사용자id
dbms_application_info.read_client_info(client_info => l_client_info);
--dbms_output.put_line('l_client_info => ' || l_client_info);
-- 오류처리
if l_env_cd is null or l_client_info is null then
l_return_value := null;
else
-- 사용자코드 조회
l_user_cd := substr(l_client_info, instr(l_client_info, ':') + 1);
begin
select ae.user_cd
,nvl(ae.corp_id, 1)
,nvl(ae.biz_id, 1)
,ae.dept_id
,ae.emp_id
,nvl(ae.lang_cd, g_session_lang_cd)
,nvl(ae.decrypto_yn, 'N')
into l_rec.user_cd
,l_rec.corp_id
,l_rec.biz_id
,l_rec.dept_id
,l_rec.emp_id
,l_rec.lang_cd
,l_rec.decrypto_yn
from sys_env ae
where 1=1
and ae.user_cd = l_user_cd;
exception
when others then
l_rec := null;
end;
end if;
-- 오류처리
if l_rec.user_cd is null then
l_return_value := null;
-- 언어코드
elsif l_env_cd = upper('lang_cd') then
l_return_value := nvl(l_rec.lang_cd, pi_lang_cd);
-- 복호화여부
elsif l_env_cd = upper('decrypto_yn') then
l_return_value := l_rec.decrypto_yn;
-- 사용자id, 사용자코드 및 사용자명
elsif l_env_cd in (upper('user_id'), upper('user_cd'), upper('user_name')) then
begin
select case when l_env_cd = upper('user_id') then to_char(cu.user_id)
when l_env_cd = upper('user_cd') then cu.user_cd
when l_env_cd = upper('user_name') then cu.user_name
else ''
end
into l_return_value
from com_user cu
where 1 = 1
and cu.user_cd = l_rec.user_cd
and trunc(sysdate) between cu.start_date and nvl(cu.end_date, trunc(sysdate));
exception
when others then
l_return_value := null;
end;
-- 법인id, 법인코드, 법인명 및 법인번호
elsif l_env_cd in (upper('corp_id'), upper('corp_cd'), upper('corp_name'), upper('corp_num')) then
begin
select case when l_env_cd = upper('corp_id') then to_char(hu.corp_id)
when l_env_cd = upper('corp_cd') then hu.corp_cd
when l_env_cd = upper('corp_name') then hu.corp_name
--when l_env_cd = upper('corp_num') then sys_crypto_api.decrypt_f(hu.corp_num)
else ''
end
into l_return_value
from hrm_corporation hu
where 1 = 1
and hu.corp_id = l_rec.corp_id
and trunc(sysdate) between hu.start_date and nvl(hu.end_date, trunc(sysdate));
exception
when others then
l_return_value := null;
end;
-- 사업장id, 사업장코드, 사업장명 및 사업자번호
elsif l_env_cd in (upper('biz_id'), upper('biz_cd'), upper('biz_name'), upper('biz_num')) then
begin
select case when l_env_cd = upper('biz_id') then to_char(hbp.biz_id)
when l_env_cd = upper('biz_cd') then hbp.biz_cd
when l_env_cd = upper('biz_name') then hbp.biz_name
--when l_env_cd = upper('biz_num') then sys_crypto_api.decrypt_f(hbp.biz_num)
else ''
end
into l_return_value
from hrm_business_place hbp
where 1 = 1
and hbp.biz_id = l_rec.biz_id
and trunc(sysdate) between hbp.start_date and nvl(hbp.end_date, trunc(sysdate));
exception
when others then
l_return_value := null;
end;
-- 임직원id, 사번, 성명 및 주민등록번호
elsif l_env_cd in (upper('emp_id'), upper('emp_num'), upper('emp_name'), upper('per_ssn')) then
begin
select case when l_env_cd = upper('emp_id') then to_char(he.emp_id)
when l_env_cd = upper('emp_num') then he.emp_num
when l_env_cd = upper('emp_name') then he.emp_name
--when l_env_cd = upper('per_ssn') then sys_crypto_api.decrypt_f(he.per_ssn)
else ''
end
into l_return_value
from hrm_employee he
where 1 = 1
and he.emp_id = l_rec.emp_id
and trunc(sysdate) between he.hire_date and nvl(he.end_date, trunc(sysdate));
exception
when others then
l_return_value := null;
end;
-- 부서id, 부서코드, 부서명
elsif l_env_cd in (upper('dept_id'), upper('dept_cd'), upper('dept_name')) then
begin
select case when l_env_cd = upper('dept_id') then to_char(hd.dept_id)
when l_env_cd = upper('dept_cd') then hd.dept_cd
when l_env_cd = upper('dept_name') then hd.dept_name
else ''
end
into l_return_value
from hrm_dept hd
where 1 = 1
and hd.dept_id = l_rec.dept_id
and trunc(sysdate) between hd.start_date and nvl(hd.end_date, trunc(sysdate));
exception
when others then
l_return_value := null;
end;
-- 발령코드, 발령상세코드, 발령번호, 직급코드, 직급명, 직위코드, 직위명, 직군코드, 직군명, 직무코드, 직무명, 직책코드, 직책명
elsif l_env_cd in (upper('asg_id')
,upper('asg_cd') , upper('asg_name')
,upper('asg_detail_cd') , upper('asg_detail_name')
,upper('grade_cd') , upper('grade_name')
,upper('grade_title_cd'), upper('grade_title_name')
,upper('job_group_cd') , upper('job_group_name')
,upper('job_cd') , upper('job_name')
,upper('position_cd') , upper('position_name')
)
then
begin
select case when l_env_cd = upper('asg_id') then to_char(v.asg_id)
when l_env_cd = upper('asg_cd') then v.asg_cd
when l_env_cd = upper('asg_name') then (select sys_get_api.code_name_f('asg_cd', v.asg_cd) from dual)
when l_env_cd = upper('asg_detail_cd') then asg_detail_cd
when l_env_cd = upper('asg_detail_name') then (select sys_get_api.code_name_f('asg_detail_cd', v.asg_detail_cd) from dual)
when l_env_cd = upper('grade_cd') then v.grade_cd
when l_env_cd = upper('grade_name') then (select sys_get_api.code_name_f('grade_cd', v.grade_cd) from dual)
when l_env_cd = upper('grade_title_cd') then v.grade_title_cd
when l_env_cd = upper('grade_title_name') then (select sys_get_api.code_name_f('grade_title_cd', v.grade_title_cd) from dual)
when l_env_cd = upper('job_group_cd') then v.job_group_cd
when l_env_cd = upper('job_group_name') then (select sys_get_api.code_name_f('job_group_cd', v.job_group_cd) from dual)
when l_env_cd = upper('job_cd') then v.job_cd
when l_env_cd = upper('job_name') then (select sys_get_api.code_name_f('job_cd', v.job_cd) from dual)
when l_env_cd = upper('position_cd') then v.position_cd
when l_env_cd = upper('position_name') then (select sys_get_api.code_name_f('position_cd', v.position_cd) from dual)
else ''
end
into l_return_value
from (
select ha.asg_id
,ha.asg_cd
,ha.asg_detail_cd
,ha.grade_cd
,ha.grade_title_cd
,ha.job_group_cd
,ha.job_cd
,ha.position_cd
,ha.asg_num
,max(ha.asg_num) over(partition by ha.corp_id, ha.emp_id) as last_asg_num
from hrm_assignment ha
where 1 = 1
and ha.emp_id = l_rec.emp_id
) v
where 1=1
and v.last_asg_num = v.asg_num;
exception
when others then
l_return_value := null;
end;
else
l_return_value := null;
end if;
-- 리턴
--dbms_output.put_line('l_rec.user_id => ' || l_rec.user_id);
--dbms_output.put_line('l_return_value => ' || l_return_value);
return l_return_value;
end get_env_f;
end sys_env_api;
create or replace package bsys.sys_env_api
is
-- 고정변수 정의
c_package_name constant varchar2(0200) default 'sys_env_api';
g_session_lang_cd varchar2(0050) default replace(sys_context ('USERENV', 'LANG'), 'US', 'EN');
-- 함수 정의
procedure set_env_p
(pi_corp_id in number -- 법인id
,pi_user_cd in varchar2 -- 사용자id
,pi_lang_cd in varchar2 default g_session_lang_cd -- 언어코드
);
procedure set_profile_p
(pi_corp_id in number -- 법인id
,pi_profile_cd in varchar2 -- 프로파일코드
,pi_profile_value in varchar2 -- 프로파일값
);
function get_env_f
(pi_env_cd in varchar2 -- 환경변수코드
,pi_lang_cd in varchar2 default g_session_lang_cd -- 언어코드
) return varchar2;
end sys_env_api;
