1.Discoverer administrator custom folder sql:
select * from table(PIPE_EMP_MANAGER(USERENV('CLIENT_INFO')))
也可以改用session level table
2.Discoverer desktop/plus worksheet condition
a. select folder with step 1 creation
b. add condition=> where 1=USERPARAM(:empno)
ps::empno 為parameter
3. create db function USERPARAM
CREATE OR REPLACE function USERPARAM(emp_no in varchar2)
RETURN NUMBER
is
begin
dbms_application_info.set_client_info(emp_no);
RETURN 1;
END;
/
ps:當然可以改成動態接受各種參數的function
4.create pipe function & object
ps:by case 依照需要自行定義
--員工record類型
CREATE OR REPLACE TYPE T_REC_EMPLOYEE AS object
(
UDM04_ID NUMBER,
COM_CODE CHAR(3 BYTE),
EMPNO VARCHAR2(36 BYTE),
CARDNO VARCHAR2(36 BYTE),
EMPNM VARCHAR2(60 BYTE),
ENAME VARCHAR2(120 BYTE),
DUDYSORT VARCHAR2(18 BYTE),
DUTYNAME VARCHAR2(50 BYTE),
DEPT_ID NUMBER,
DEPTNO VARCHAR2(120 BYTE),
DEPT_LV NUMBER,
DEPT_NAME VARCHAR2(80 BYTE),
DEPTNO_PATH VARCHAR2(1000 BYTE),
DEPTNM_PATH VARCHAR2(1000 BYTE),
DEPT_ROOT_NO VARCHAR2(20 BYTE),
DEPT_ROOT_ID NUMBER(12),
MANAGER1_NO VARCHAR2(20 BYTE),
MANAGER2_NO VARCHAR2(20 BYTE),
MANAGER3_NO VARCHAR2(20 BYTE),
MANAGER_NO_PATH VARCHAR2(1000 BYTE),
MANAGER2_NO_PATH VARCHAR2(1000 BYTE),
MANAGER3_NO_PATH VARCHAR2(1000 BYTE),
DEPT_OBJID_PATH VARCHAR2(1000 BYTE),
BIRTHDAY DATE,
MAIL VARCHAR2(80 BYTE),
SEX VARCHAR2(1 BYTE),
MARRY VARCHAR2(18 BYTE),
RS_IN DATE,
DEBZ VARCHAR2(18 BYTE),
BZNAME VARCHAR2(16 BYTE),
ZHIWU VARCHAR2(18 BYTE),
ZWNAME VARCHAR2(120 BYTE),
ZHIJI VARCHAR2(18 BYTE),
ZJNAME VARCHAR2(12 BYTE),
LZDATE DATE,
LZCODE VARCHAR2(40 BYTE),
LZCAUSE VARCHAR2(30 BYTE)
)
--員工類型集合
CREATE OR REPLACE TYPE COL_EMPLOYEE AS TABLE OF T_REC_EMPLOYEE
/
-- 主要pipe function
CREATE OR REPLACE FUNCTION PIPE_EMP_MANAGER (
/*==========================================================================+
此pipe function 為尋找某員工之部門主管資料,可循序往該部門部門層級上階進行巡訪,
且可指定公司,部門名稱,職務,部門層級 等相關參數作為模糊及等值定義搜尋條件,
若人事系統部門資料未定義部門主管,無法取得任何主管筆數,
則會再次尋找員工部門代號列於˙該員工上階所有部門階層資料中的員工
參數說明:
pempno IN VARCHAR2, --員工工號
pcom_code IN VARCHAR2 DEFAULT NULL , --公司code , 金橋=GBE ,達辰=DDE
pdept_lv IN NUMBER DEFAULT NULL , --部門階級,pdept_lv 以上的部門層級主管才會抓取
pjob IN VARCHAR2 DEFAULT NULL , --主管職務說明,可用模糊搜尋% or _
pdeptname IN VARCHAR2 DEFAULT NULL --主管部門名稱,可用模糊搜尋% or _
Revision 1.0
Nolem lin creation
| |
+==========================================================================*/
pempno IN VARCHAR2,
pcom_code IN VARCHAR2 DEFAULT NULL ,
pdept_lv IN NUMBER DEFAULT NULL ,
pjob IN VARCHAR2 DEFAULT NULL ,
pdeptname IN VARCHAR2 DEFAULT NULL
)
RETURN COL_EMPLOYEE
PIPELINED
AS
rec_employee MV_ALL_EMPLOYEE%ROWTYPE;
manager_cnt PLS_INTEGER := 0;
manager_where VARCHAR2 (1000) := NULL;
deptid_where VARCHAR2 (1000) := NULL;
vsql VARCHAR2 (1000) := NULL;
recrow T_REC_EMPLOYEE :=T_REC_EMPLOYEE(null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null);
recempty T_REC_EMPLOYEE :=T_REC_EMPLOYEE(null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null);
TYPE t_managerlist IS TABLE OF MV_ALL_EMPLOYEE%ROWTYPE;
managerlist t_managerlist;
inx1 PLS_INTEGER;
BEGIN
BEGIN
SELECT *
INTO rec_employee
FROM MV_ALL_EMPLOYEE
WHERE empno = pempno;
IF TRIM (rec_employee.MANAGER_NO_PATH) IS NOT NULL
THEN
manager_where :=
manager_where || '\' || TRIM (rec_employee.MANAGER_NO_PATH);
END IF;
IF TRIM (rec_employee.MANAGER2_NO_PATH) IS NOT NULL
THEN
manager_where :=
manager_where || '\' || TRIM (rec_employee.MANAGER2_NO_PATH);
END IF;
IF TRIM (rec_employee.MANAGER3_NO_PATH) IS NOT NULL
THEN
manager_where :=
manager_where || '\' || TRIM (rec_employee.MANAGER3_NO_PATH);
END IF;
manager_where :=
'('''
|| REPLACE (LTRIM (LTRIM (manager_where, '\'), ','), '\', ''',''')
|| ''')';
vsql :=
'SELECT * FROM MV_ALL_EMPLOYEE WHERE lzdate is null and empno in ';
vsql := vsql || manager_where;
IF pcom_code IS NOT NULL
THEN
vsql := vsql || ' and com_code=''' || pcom_code || '''';
END IF;
IF pdept_lv IS NOT NULL
THEN
vsql := vsql || ' and DEPT_LV<=' || pdept_lv;
END IF;
IF pjob IS NOT NULL
THEN
vsql := vsql || ' and ZWNAME like ''' || pjob || '''';
END IF;
IF pdeptname IS NOT NULL
THEN
vsql := vsql || ' and DEPTNM_PATH like ''' || pdeptname || '''';
END IF;
-- SYS.DBMS_OUTPUT.PUT_LINE(vsql);
EXECUTE IMMEDIATE vsql BULK COLLECT INTO managerlist;
FOR inx1 IN 1 .. managerlist.COUNT
LOOP
recrow.UDM04_ID := managerlist (inx1).UDM04_ID;
recrow.COM_CODE := managerlist (inx1).COM_CODE;
recrow.EMPNO := managerlist (inx1).EMPNO;
recrow.CARDNO := managerlist (inx1).CARDNO;
recrow.EMPNM := managerlist (inx1).EMPNM;
recrow.ENAME := managerlist (inx1).ENAME;
recrow.DUDYSORT := managerlist (inx1).DUDYSORT;
recrow.DUTYNAME := managerlist (inx1).DUTYNAME;
recrow.DEPTNO := managerlist (inx1).DEPTNO;
recrow.DEPT_LV := managerlist (inx1).DEPT_LV;
recrow.DEPT_NAME := managerlist (inx1).DEPT_NAME;
recrow.DEPTNO_PATH := managerlist (inx1).DEPTNO_PATH;
recrow.DEPTNM_PATH := managerlist (inx1).DEPTNM_PATH;
recrow.MANAGER1_NO := managerlist (inx1).MANAGER1_NO;
recrow.DEPT_ROOT_NO := managerlist (inx1).DEPT_ROOT_NO;
recrow.DEPT_ROOT_ID := managerlist (inx1).DEPT_ROOT_ID;
recrow.MANAGER2_NO := managerlist (inx1).MANAGER2_NO;
recrow.MANAGER3_NO := managerlist (inx1).MANAGER3_NO;
recrow.MANAGER_NO_PATH := managerlist (inx1).MANAGER_NO_PATH;
recrow.MANAGER2_NO_PATH := managerlist (inx1).MANAGER2_NO_PATH;
recrow.MANAGER3_NO_PATH := managerlist (inx1).MANAGER3_NO_PATH;
recrow.DEPT_OBJID_PATH := managerlist (inx1).DEPT_OBJID_PATH;
recrow.BIRTHDAY := managerlist (inx1).BIRTHDAY;
recrow.MAIL := managerlist (inx1).MAIL;
recrow.SEX := managerlist (inx1).SEX;
recrow.MARRY := managerlist (inx1).MARRY;
recrow.RS_IN := managerlist (inx1).RS_IN;
recrow.DEBZ := managerlist (inx1).DEBZ;
recrow.BZNAME := managerlist (inx1).BZNAME;
recrow.ZHIWU := managerlist (inx1).ZHIWU;
recrow.ZWNAME := managerlist (inx1).ZWNAME;
recrow.ZHIJI := managerlist (inx1).ZHIJI;
recrow.ZJNAME := managerlist (inx1).ZJNAME;
recrow.LZDATE := managerlist (inx1).LZDATE;
recrow.LZCODE := managerlist (inx1).LZCODE;
recrow.LZCAUSE := managerlist (inx1).LZCAUSE;
PIPE ROW (recrow);
manager_cnt := manager_cnt + 1;
--DBMS_OUTPUT.PUT_LINE (managerlist(inx1).empno);
END LOOP;
IF manager_cnt = 0
THEN
vsql := NULL;
vsql := 'SELECT * FROM MV_ALL_EMPLOYEE WHERE lzdate is null and DEPT_ID in ';
manager_where := '('|| REPLACE (LTRIM (LTRIM (rec_employee.DEPT_OBJID_PATH, '\'), ','),'\',',')|| ')';
vsql := vsql || manager_where;
IF pcom_code IS NOT NULL
THEN
vsql := vsql || ' and com_code=''' || pcom_code || '''';
END IF;
IF pdept_lv IS NOT NULL
THEN
vsql := vsql || ' and DEPT_LV<=' || pdept_lv;
END IF;
IF pjob IS NOT NULL
THEN
vsql := vsql || ' and ZWNAME like ''' || pjob || '''';
END IF;
IF pdeptname IS NOT NULL
THEN
vsql := vsql || ' and DEPTNM_PATH like ''' || pdeptname || '''';
END IF;
-- SYS.DBMS_OUTPUT.PUT_LINE(vsql);
EXECUTE IMMEDIATE vsql BULK COLLECT INTO managerlist;
FOR inx1 IN 1 .. managerlist.COUNT
LOOP
recrow.UDM04_ID := managerlist (inx1).UDM04_ID;
recrow.COM_CODE := managerlist (inx1).COM_CODE;
recrow.EMPNO := managerlist (inx1).EMPNO;
recrow.CARDNO := managerlist (inx1).CARDNO;
recrow.EMPNM := managerlist (inx1).EMPNM;
recrow.ENAME := managerlist (inx1).ENAME;
recrow.DUDYSORT := managerlist (inx1).DUDYSORT;
recrow.DUDYSORT := managerlist (inx1).DUDYSORT;
recrow.DUDYSORT := managerlist (inx1).DUDYSORT;
recrow.DEPT_ID := managerlist (inx1).DEPT_ID;
recrow.DEPTNO := managerlist (inx1).DEPTNO;
recrow.DEPT_LV := managerlist (inx1).DEPT_LV;
recrow.DEPT_NAME := managerlist (inx1).DEPT_NAME;
recrow.DEPTNO_PATH := managerlist (inx1).DEPTNO_PATH;
recrow.DEPTNM_PATH := managerlist (inx1).DEPTNM_PATH;
recrow.MANAGER1_NO := managerlist (inx1).MANAGER1_NO;
recrow.DEPT_ROOT_NO := managerlist (inx1).DEPT_ROOT_NO;
recrow.DEPT_ROOT_ID := managerlist (inx1).DEPT_ROOT_ID;
recrow.MANAGER2_NO := managerlist (inx1).MANAGER2_NO;
recrow.MANAGER3_NO := managerlist (inx1).MANAGER3_NO;
recrow.MANAGER_NO_PATH := managerlist (inx1).MANAGER_NO_PATH;
recrow.MANAGER2_NO_PATH := managerlist (inx1).MANAGER2_NO_PATH;
recrow.MANAGER3_NO_PATH := managerlist (inx1).MANAGER3_NO_PATH;
recrow.DEPT_OBJID_PATH := managerlist (inx1).DEPT_OBJID_PATH;
recrow.BIRTHDAY := managerlist (inx1).BIRTHDAY;
recrow.MAIL := managerlist (inx1).MAIL;
recrow.SEX := managerlist (inx1).SEX;
recrow.MARRY := managerlist (inx1).MARRY;
recrow.RS_IN := managerlist (inx1).RS_IN;
recrow.DEBZ := managerlist (inx1).DEBZ;
recrow.BZNAME := managerlist (inx1).BZNAME;
recrow.ZHIWU := managerlist (inx1).ZHIWU;
recrow.ZWNAME := managerlist (inx1).ZWNAME;
recrow.ZHIJI := managerlist (inx1).ZHIJI;
recrow.ZJNAME := managerlist (inx1).ZJNAME;
recrow.LZDATE := managerlist (inx1).LZDATE;
recrow.LZCODE := managerlist (inx1).LZCODE;
recrow.LZCAUSE := managerlist (inx1).LZCAUSE;
PIPE ROW (recrow);
manager_cnt := manager_cnt + 1;
--DBMS_OUTPUT.PUT_LINE (managerlist(inx1).empno);
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RAISE;
END;
END;
/
留言列表
Oracle (6)
