SQL 함수 만들기 및 사용....

 

--내가 할일 LIST 비품신청
SELECT  A.DOC_INDEX, A.DFT_NO, A.SUBJECT, A.FIX_NAME, A.FIX_STANDARD, A.FIX_NUMBER, A.FIX_UNIT_PRICE, A.FIX_PRICE, A.FIX_USAGE, A.FIX_TOTAL, TO_CHAR(A.APPLICATION_DATE, 'YYYY-MM-DD HH24:MI:SS') AS APPLICATION_DATE
       ,B.EMP_NO,TO_CHAR(B.AP_DATE, 'YYYY-MM-DD HH24:MI:SS') AS AP_DATE, B.REJ_RSN, B.FIN_AUTH_ST, B.ARB_ST, B.FIN_AUTH_ST, B.SERIAL_NUMBER, B.APB_PRO
       ,C.DOC_KIND,C.IMPORTANT ,C.EMP_NO AS FEMP_NO,(SELECT EMP_NM FROM EMP WHERE EMP_NO = C.EMP_NO) AS EMP_NM
       ,FN_BEF_APB_PRO(B.EMP_NO, A.DFT_NO) BEF_APB_PRO
FROM FIXTURES A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON C.DFT_NO = A.DFT_NO
WHERE B.EMP_NO = '202309001' AND SERIAL_NUMBER = 1
OR (B.EMP_NO = '202309001' AND B.SERIAL_NUMBER > 1 AND FN_BEF_APB_PRO(B.EMP_NO,A.DFT_NO) = '승인')
ORDER BY APPLICATION_DATE DESC;

--결제자 출력
CREATE OR REPLACE FUNCTION FN_BEF_APB_PRO(P_EMP_NO IN VARCHAR2, P_DFT_NO IN NUMBER)
/*
절달된 사번의 이전 사람의 결재상태를 리턴
*/
RETURN VARCHAR2
IS
    V_BEF_APB_PRO VARCHAR2(30);
BEGIN
    WITH S AS(
        SELECT T.EMP_NO
             , T.EMP_NM
             , T.DFT_NO
             , T.AP_DATE
             , T.REJ_RSN
             , T.ARB_ST
             , T.FIN_AUTH_ST
             , T.SERIAL_NUMBER
             , LAG(T.APB_PRO) OVER (ORDER BY T.SERIAL_NUMBER ASC) BEF_APB_PRO
             , T.APB_PRO
             , T.MY_IMG
             , T.SIGN_IMG     
        FROM
        (
            SELECT A.EMP_NO,(SELECT EMP_NM FROM EMP E WHERE A.EMP_NO = E.EMP_NO) AS EMP_NM ,A.DFT_NO, A.AP_DATE, A.REJ_RSN, A.ARB_ST, A.FIN_AUTH_ST, A.SERIAL_NUMBER, A.APB_PRO 
                 ,(SELECT DISTINCT SVFI_NAME FROM FILE_INFO WHERE FILE_UDTER = A.EMP_NO AND FILE_SN = (SELECT MAX(FILE_SN) FROM FILE_INFO WHERE FILE_SETTING = 'myimg' AND FILE_UDTER = A.EMP_NO)) AS MY_IMG
                 ,(SELECT DISTINCT SVFI_NAME FROM FILE_INFO WHERE FILE_UDTER = A.EMP_NO AND FILE_SN = (SELECT MAX(FILE_SN) FROM FILE_INFO WHERE FILE_SETTING = 'temp' AND FILE_UDTER = A.EMP_NO)) AS SIGN_IMG
                 ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'position' AND CM_CODE = (SELECT DTCODE FROM EMP B WHERE A.EMP_NO = B.EMP_NO)) AS DTCODENM
            FROM APBOX A
            WHERE A.DFT_NO = P_DFT_NO
        ) T
    )
    SELECT  S.BEF_APB_PRO INTO V_BEF_APB_PRO FROM S
    WHERE S.EMP_NO = P_EMP_NO;
    
    RETURN V_BEF_APB_PRO;
END;
/