최프 SQL 쿼리문

--카멜케이스
create or replace FUNCTION FN_GETCAMEL(COLUMN_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
RSLT VARCHAR2(30);
BEGIN
--카멜표기로 변환(SITE_NUM -> siteNum)
SELECT LOWER(SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),1,1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),2) INTO RSLT
FROM DUAL;
--리턴
RETURN RSLT;
END;
/

commit;

--구글 카멜변환(https://heavenly-appear.tistory.com/270)
SELECT COLUMN_NAME
, DATA_TYPE
, CASE WHEN DATA_TYPE='NUMBER' THEN 'private int ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE IN('VARCHAR2','CHAR') THEN 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE='DATE' THEN 'private Date ' || FN_GETCAMEL(COLUMN_NAME) || ';'
ELSE 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
END AS CAMEL_CASE
, '<result property="'||FN_GETCAMEL(COLUMN_NAME)||'" column="'||COLUMN_NAME||'" />' RESULTMAP
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'BK_APPLY';

--검색
SELECT EMP_NO, EMP_NM FROM EMP
WHERE 1=1
AND (
 EMP_NO LIKE '%ㄱ%' OR 
 EMP_NM LIKE '%ㄱ%'
 )
ORDER BY EMP_NO DESC;

--사원 한명 셀렉트
SELECT A.EMP_NO, A.EMP_NM, A.EMP_PASS , A.GEN_CODE 
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'gender' AND CM_CODE = A.GEN_CODE) AS GEN_CODE_NM
   , A.EMP_MAIL , A.EMP_ADDR1, A.EMP_ADDR2, A.EMP_ADDR3, A.EMP_BRDT, A.REGN
   , A.CP_NO, A.OFC_NO, A.EMP_IMG, A.BANK_CODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'bank' AND CM_CODE = A.BANK_CODE) AS BANK_CODE_NM
   , A.ACCT_NO, A.OWNER, A.ENT_DATE, A.OUT_DATE, A.EMP_MM, A.CTRT_CASE_CODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'contract' AND CM_CODE = A.CTRT_CASE_CODE) AS CTRT_CASE_NM
   , A.ENT_CASE_CODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'join' AND CM_CODE = A.ENT_CASE_CODE) AS ENT_CASE_NM
   , A.BS_WKTIME
   , A.DTCODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'position' AND CM_CODE = A.DTCODE) AS DTCODE_NM
   , A.PTN_CODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'spot' AND CM_CODE = A.PTN_CODE) AS PTN_CODE_NM
   , A.JCODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'job' AND CM_CODE = A.JCODE) AS JCODE_NM
   , A.GRD_CODE
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'year' AND CM_CODE = A.GRD_CODE) AS GRD_CODE_NM
   , A.ENABLED, A.ST_CLF_CD
   ,(SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'tenure' AND CM_CODE = A.ST_CLF_CD) AS ST_CLF_CD_NM
   , A.EMP_SALARY, A.PROFILE_FILE_NO
   , A.SIGN_FILE_NO, A.WORK_METHOD_CODE
   , (SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'workMethod' AND CM_CODE = A.WORK_METHOD_CODE) AS WORK_METHOD_CODE_NM
   , A.DCODE
   , (SELECT DNM FROM DEPT WHERE DCODE = A.DCODE) AS DNM
   
   , B.AUTHRT_ID
   
FROM EMP A 
LEFT JOIN AUTHOR_ALWNC B ON A.EMP_NO = B.EMP_NO
WHERE A.EMP_NO = '202309001';

--계약이력정보 셀렉트
SELECT CNTHX_NO, EMP_NO, CNT_WRITER, BLCT_SDATE
     , BLCT_EDATE, CTRT_CASE_CODE
     , (SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'join' AND CM_CODE = CTRT_CASE_CODE) AS CTRT_CASE_CODE_NM
     , BWCT_SDATE, BWCT_EDATE
     , BCNT_AMT, BFEX, BINC_CLF, CNG_DATE, CNG_MM, PR_SDATE, PR_EDATE
     , CNT_EDITOR, EDIT_DATE, DELYN
     , (SELECT CM_NM FROM CMCODE WHERE CM_GROUP = 'use' AND CM_CODE = DELYN) AS DELYN_NM
FROM CNTHX
WHERE EMP_NO = '202309001';

--계약 추가
INSERT INTO CNTHX(CNTHX_NO, EMP_NO, CNT_WRITER, BLCT_SDATE,
            BLCT_EDATE, CTRT_CASE_CODE, BWCT_SDATE, BWCT_EDATE, BCNT_AMT,
            BFEX, BINC_CLF, CNG_DATE, CNG_MM, PR_SDATE, PR_EDATE, CNT_EDITOR,
            EDIT_DATE, DELYN)
VALUES (1,'202309001','202309002',sysdate,sysdate,'A0601',sysdate,sysdate,100000000,30000,'근로소득',sysdate,'이사람은 아주대단한 사람이기 떄문에 돈을 많이 줘서 데리고 있어야함',sysdate,sysdate,'202309002',sysdate,'B0101');

--학력정보 INSERT
INSERT INTO ACCA(
    EMP_NO,
    AC_NM,
    AC_ETDATE,
    AC_EDATE,
    GRAD_CLF,
    MAJ
)VALUES ('202309001','취업딱대학교','2023.03.21','2023.10.16','A0902','스프링');

--학력정보 SELECT
SELECT EMP_NO, AC_NM, AC_ETDATE, AC_EDATE, GRAD_CLF
     , (SELECT CM_NM FROM CMCODE WHERE CM_GROUP='graduate' AND CM_CODE = GRAD_CLF)AS GRAD_CLF_NM
     , MAJ
FROM ACCA
WHERE EMP_NO = '202309001';

--파일경로 UPDATE
UPDATE EMP
SET EMP_IMG = ''
WHERE EMP_NO = '202309001';

--파일 INSERT
INSERT INTO FILE_INFO(
    FILE_SN,    -- 파일 순번
    ORFI_NAME,  -- 원본파일 이름
    SVFI_NAME,  -- 저장파일 이름
    FILE_SIZE,   -- 파일 사이즈
    SV_DATE,    -- 저장날짜
    FILE_UDTER, -- 파일 등록자
    FILE_ROOT,  -- 파일경로
    FILE_SETTING --카테고리별 넘버
)VALUES(
'2','testFile','saveFile',200,sysdate,'230909001','testFile','1'
);



--파일 SELECT
SELECT FILE_SN, ORFI_NAME, SVFI_NAME, FILE_SIZE, 
       SV_DATE, FILE_UDTER, FILE_ROOT, FILE_SETTING 
FROM FILE_INFO 
WHERE FILE_UDTER= '202309001'
ORDER BY file_sn desc;

--계층형 쿼리
SELECT D.DNM, D.DCODE, D.UPR_DCODE ,E.EMP_NM AS DEPT_NO
FROM DEPT D
LEFT JOIN EMP E ON D.DCODE = E.DCODE
START WITH D.UPR_DCODE IS NULL
CONNECT BY PRIOR D.DCODE = D.UPR_DCODE;

--사원 계층
SELECT D.DCODE, E.EMP_NM AS DEPT_NAME, D.DCODE || 1 AS DEPT_ID
FROM DEPT D
LEFT JOIN EMP E ON D.DCODE = E.DCODE
START WITH D.UPR_DCODE IS NULL
CONNECT BY PRIOR D.DCODE = D.UPR_DCODE;

commit;

DISTINCT
--------------------------------조직도---------------------------
SELECT DISTINCT DCODE, UPR_DCODE, DNM, EMP_NO, DABST, DELYN_CD, LEVEL LVL 
FROM   DEPT
START WITH UPR_DCODE IS NULL
CONNECT BY PRIOR DCODE = UPR_DCODE;

SELECT DISTINCT ROWNUM RNUM, T.*
FROM
(
    SELECT D.DCODE, D.DNM, E.EMP_NM AS DEPT_NAME, D.DCODE || 1 AS DEPT_ID
    FROM DEPT D LEFT JOIN EMP E ON D.DCODE = E.DCODE
    START WITH D.UPR_DCODE IS NULL
    CONNECT BY PRIOR D.DCODE = D.UPR_DCODE
) T;
-----------------------------------------------------------------

		SELECT DISTINCT D.DCODE, E.EMP_NM AS DEPT_NAME, E.EMP_NO AS UPR_DCODE
		FROM DEPT D
		LEFT JOIN EMP E ON D.DCODE = E.DCODE
		START WITH D.UPR_DCODE IS NULL
		CONNECT BY PRIOR D.DCODE = D.UPR_DCODE;


SELECT * FROM DEPT;

--문서종류 가져오기
SELECT DOC_KIND, DOC_DETAIL FROM DOCKIND WHERE DOC_KIND = '비품신청';

--문서작성 INSERT
INSERT INTO DOC_WRI(DFT_NO, EMP_NO, DOC_KIND)
VALUES(1,'202309001','비품신청');
--문서 번호 SELECT
SELECT NVL(MAX(DFT_NO),0) AS DFT_NO  FROM DOC_WRI;

--비품신청서 INSERT
INSERT INTO FIXTURES(DOC_INDEX, DFT_NO, SUBJECT, FIX_NAME, FIX_STANDARD, FIX_NUMBER, FIX_UNIT_PRICE, FIX_PRICE, FIX_USAGE, FIX_TOTAL, APPLICATION_DATE)
VALUES(1,1,'글제목입니다','품명','규격','1','100','100','회사에서 100원만 주세요','100',SYSDATE);

--내가 할일 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 = '202308215' AND SERIAL_NUMBER = 1
OR (B.EMP_NO = '202308215' AND B.SERIAL_NUMBER > 1 AND FN_BEF_APB_PRO(B.EMP_NO,A.DFT_NO) = '승인')
ORDER BY APPLICATION_DATE DESC;

--내가 할일 LIST 휴가신청
SELECT A.DFT_NO, A.VAAP_EMP, A.VAAP_DAYS, A.VAAP_RSN, A.VAAP_APST_CODE, A.VAAP_RT_RS, A.VAAP_CC_ST, TO_CHAR(A.VAAP_START, 'YYYY-MM-DD HH24:MI:SS') AS VAAP_START , TO_CHAR(A.VAAP_END,'YYYY-MM-DD HH24:MI:SS')AS VAAP_END, A.VAAP_TYPE, A.VAC_TYPE_NO,(SELECT VAC_TYPE_NAME FROM VAC_TYPE WHERE VAC_TYPE_NO = A.VAC_TYPE_NO)AS VAC_TYPE_NAME, A.VAAP_DATE 
      ,B.SERIAL_NUMBER ,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.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
FROM VACATIONFORM A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON A.DFT_NO = C.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 VAAP_DATE DESC;

--내가 할일 LIST 근무신청
SELECT A.WA_NO, A.WA_APER, A.WA_RVER, A.WA_DATE, A.WA_APDATE, A.WA_ARSN, A.WA_STIME, A.WA_ETIME, A.WA_ALLDAY, A.WA_EXTIME, A.WA_NIGHTIME, A.HDTIME, A.WA_TYPE, A.EW_APST, A.WA_TLTIME, A.EW_ADATE, A.EW_REJRSN, A.WA_DEL, A.WK_COLOR, A.DFT_NO
      ,B.SERIAL_NUMBER ,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.SERIAL_NUMBER, B.APB_PRO
      ,C.DOC_KIND,C.IMPORTANT ,C.EMP_NO AS FEMP_NO,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.WA_APER) AS EMP_NM
FROM WK_AP A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON b.DFT_NO = C.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 WA_APDATE DESC;

--내가 할일 LIST 휴직신청
SELECT A.BK_NO, A.EMP_NO, A.BK_CODE,(SELECT BK_NM FROM BK_KIND WHERE BK_CODE = A.BK_CODE )AS BK_NM, A.BK_SDATE, A.BK_EDATE, A.BK_RSN, A.BK_APST_CODE,(SELECT CM_NM FROM CMCODE WHERE CM_CODE=A.BK_APST_CODE )AS BK_APST_NM , A.FILE_SETTING, A.BK_CERT_YN, A.DFT_NO, A.ORFI_NAME, A.FILE_SN, A.BKAP_DATE
      ,B.SERIAL_NUMBER ,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.SERIAL_NUMBER, B.APB_PRO
      ,C.DOC_KIND,C.IMPORTANT ,C.EMP_NO AS FEMP_NO,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.EMP_NO) AS EMP_NM
FROM BK_APPLY A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON b.DFT_NO = C.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 BKAP_DATE DESC;

SELECT * FROM BK_KIND;
SELECT * FROM CMCODE;

--결제자 출력
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;
/


--결제자 출력
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 = 529
ORDER BY SERIAL_NUMBER ASC;


-- 결제선 UPDATE
UPDATE APBOX SET AP_DATE=SYSDATE, APB_PRO = '승인', FIN_AUTH_ST = 'N' WHERE EMP_NO = '202309001' AND DFT_NO='26';
UPDATE APBOX SET FIN_AUTH_ST = 'Y' WHERE DFT_NO='1';
SELECT * FROM APBOX WHERE DFT_NO='1';

--내문서 상세정보 SEELCT 비품
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
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 = '202309002' AND C.DFT_NO = '17';

--내문서 상세정보 SEELCT 휴가
SELECT A.DFT_NO, A.VAAP_EMP, A.VAAP_DAYS, A.VAAP_RSN, A.VAAP_APST_CODE, A.VAAP_RT_RS, A.VAAP_CC_ST, A.VAAP_START, A.VAAP_END, A.VAAP_TYPE, A.VAC_TYPE_NO,(SELECT VAC_TYPE_NAME FROM VAC_TYPE WHERE VAC_TYPE_NO = A.VAC_TYPE_NO)AS VAC_TYPE_NAME, A.VAAP_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
FROM VACATIONFORM 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 C.DFT_NO = '3';

--내문서 상세정보 SEELCT 근무
SELECT  A.WA_NO, A.WA_APER, A.WA_RVER, A.WA_DATE, A.WA_APDATE, A.WA_ARSN, A.WA_STIME, A.WA_ETIME, A.WA_ALLDAY, A.WA_EXTIME, A.WA_NIGHTIME, A.HDTIME, A.WA_TYPE, A.EW_APST, A.WA_TLTIME, A.EW_ADATE, A.EW_REJRSN, A.WA_DEL, A.WK_COLOR, A.DFT_NO
       ,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
FROM WK_AP 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 C.DFT_NO = '201';



--결제선 INSERT
INSERT INTO APBOX(EMP_NO, DFT_NO, AP_DATE, REJ_RSN, ARB_ST, FIN_AUTH_ST, SERIAL_NUMBER)
VALUES('202309001',1,SYSDATE,'이러한 이유로 반려함','B0102','B0102',1);

--내 이미지파일 SELECT
SELECT  FILE_SN, ORFI_NAME, SVFI_NAME, FILE_SIZE, 
        SV_DATE, FILE_UDTER, FILE_ROOT, FILE_SETTING 
FROM FILE_INFO
WHERE FILE_UDTER = '202309001' AND FILE_SETTING = 'myimg'
ORDER BY FILE_SN DESC;

--중요문서 등록 UPDATE
UPDATE DOC_WRI SET IMPORTANT = '중요' WHERE DFT_NO = 1 AND EMP_NO ='202309001';

--알림등록 INSERT
INSERT INTO TB_NOTIFICATION(NTCN_ID, NTCN_RCVER_EMP_NO, NTCN_CONTENT, NTCN_URL, NTCN_DT, NTCN_READ_STATUS, NTCN_TITLE ,NTCN_SENDER_EMP_NO)
VALUES(111,'202309001','알림내용','알림주소',SYSDATE,'N','알림제목','202309001');

--알림확인 INSERT
INSERT INTO TB_NOTI_CHECK(EMP_NO, NTCN_ID, CHECK_DATE)
VALUES('202309001',1,SYSDATE);

--알림SELECT
SELECT NTCN_ID, A.NTCN_RCVER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_RCVER_EMP_NO) AS REC_EMP_NM, NTCN_CONTENT
      , NTCN_URL, NTCN_DT, NTCN_READ_STATUS, A.NTCN_TITLE, A.NTCN_SENDER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_SENDER_EMP_NO ) AS SEND_EMP_NM
      , DFT_NO
FROM TB_NOTIFICATION A
WHERE NTCN_RCVER_EMP_NO = '202308215' AND NTCN_READ_STATUS = 'N';

--알림UPDATE
UPDATE TB_NOTIFICATION SET NTCN_READ_STATUS = 'Y' WHERE NTCN_ID = 1 AND NTCN_RCVER_EMP_NO='202309001' ;

--나의 알람 비품 SELECT
SELECT NTCN_ID, A.NTCN_RCVER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_RCVER_EMP_NO) AS REC_EMP_NM
      , NTCN_CONTENT , NTCN_URL, NTCN_DT, NTCN_READ_STATUS
      , A.NTCN_TITLE, A.NTCN_SENDER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_SENDER_EMP_NO ) AS SEND_EMP_NM
      ,A.DFT_NO, T.APB_PRO, T.BEF_APB_PRO
FROM TB_NOTIFICATION A
LEFT OUTER JOIN (
    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.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) AS 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 = '202308215' AND SERIAL_NUMBER = 1)
    OR (B.EMP_NO = '202308215' AND B.SERIAL_NUMBER > 1 AND FN_BEF_APB_PRO(B.EMP_NO,A.DFT_NO) = '승인')
) T ON A.DFT_NO = T.DFT_NO
WHERE A.NTCN_RCVER_EMP_NO = '202308215' AND A.NTCN_READ_STATUS = 'N' AND T.APB_PRO = '진행중'
ORDER BY DFT_NO DESC;



--나의 알람 근무
SELECT 
    NTCN_ID, A.NTCN_RCVER_EMP_NO,
    (SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_RCVER_EMP_NO) AS REC_EMP_NM,
    NTCN_CONTENT, NTCN_URL, NTCN_DT, NTCN_READ_STATUS,
    A.NTCN_TITLE, A.NTCN_SENDER_EMP_NO,
    (SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_SENDER_EMP_NO ) AS SEND_EMP_NM,
    A.DFT_NO, T.APB_PRO
FROM TB_NOTIFICATION A
LEFT OUTER JOIN (
    SELECT 
        A.WA_NO, A.WA_APER, A.WA_RVER, A.WA_DATE, A.WA_APDATE, A.WA_ARSN, 
        A.WA_STIME, A.WA_ETIME, A.WA_ALLDAY, A.WA_EXTIME, A.WA_NIGHTIME, 
        A.HDTIME, A.WA_TYPE, A.EW_APST, A.WA_TLTIME, A.EW_ADATE, A.EW_REJRSN, 
        A.WA_DEL, A.WK_COLOR, A.DFT_NO,
        B.SERIAL_NUMBER, 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.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
    FROM WK_AP A
    LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
    LEFT JOIN DOC_WRI C ON A.DFT_NO = C.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) = '승인'))
) T ON A.DFT_NO = T.DFT_NO
WHERE NTCN_RCVER_EMP_NO = '202309001' AND NTCN_READ_STATUS = 'N' AND T.APB_PRO = '진행중'
ORDER BY DFT_NO DESC;

--나의 알람 휴가
SELECT NTCN_ID, A.NTCN_RCVER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_RCVER_EMP_NO) AS REC_EMP_NM, NTCN_CONTENT
      , NTCN_URL, NTCN_DT, NTCN_READ_STATUS, A.NTCN_TITLE, A.NTCN_SENDER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_SENDER_EMP_NO ) AS SEND_EMP_NM
      , A.DFT_NO, T.APB_PRO
FROM TB_NOTIFICATION A
LEFT OUTER JOIN(
SELECT A.DFT_NO, A.VAAP_EMP, A.VAAP_DAYS, A.VAAP_RSN, A.VAAP_APST_CODE, A.VAAP_RT_RS, A.VAAP_CC_ST, TO_CHAR(A.VAAP_START, 'YYYY-MM-DD HH24:MI:SS') AS VAAP_START , TO_CHAR(A.VAAP_END,'YYYY-MM-DD HH24:MI:SS')AS VAAP_END, A.VAAP_TYPE, A.VAC_TYPE_NO,(SELECT VAC_TYPE_NAME FROM VAC_TYPE WHERE VAC_TYPE_NO = A.VAC_TYPE_NO)AS VAC_TYPE_NAME, A.VAAP_DATE 
      ,B.SERIAL_NUMBER ,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.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
FROM VACATIONFORM A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON A.DFT_NO = C.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) = '승인')
) T ON A.DFT_NO = T.DFT_NO
WHERE NTCN_RCVER_EMP_NO = '202309001' AND NTCN_READ_STATUS = 'N' AND T.APB_PRO = '진행중'
ORDER BY DFT_NO DESC;


SELECT A.DFT_NO, A.VAAP_EMP, A.VAAP_DAYS, A.VAAP_RSN, A.VAAP_APST_CODE, A.VAAP_RT_RS, A.VAAP_CC_ST, TO_CHAR(A.VAAP_START, 'YYYY-MM-DD HH24:MI:SS') AS VAAP_START , TO_CHAR(A.VAAP_END,'YYYY-MM-DD HH24:MI:SS')AS VAAP_END, A.VAAP_TYPE, A.VAC_TYPE_NO,(SELECT VAC_TYPE_NAME FROM VAC_TYPE WHERE VAC_TYPE_NO = A.VAC_TYPE_NO)AS VAC_TYPE_NAME, A.VAAP_DATE 
      ,B.SERIAL_NUMBER ,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.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
FROM VACATIONFORM A
LEFT JOIN APBOX B ON A.DFT_NO = B.DFT_NO
LEFT JOIN DOC_WRI C ON A.DFT_NO = C.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 VAAP_DATE DESC;

--알림SELECT
SELECT NTCN_ID, A.NTCN_RCVER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_RCVER_EMP_NO) AS REC_EMP_NM, NTCN_CONTENT
      , NTCN_URL, NTCN_DT, NTCN_READ_STATUS, A.NTCN_TITLE, A.NTCN_SENDER_EMP_NO
      ,(SELECT EMP_NM FROM EMP WHERE EMP_NO = A.NTCN_SENDER_EMP_NO ) AS SEND_EMP_NM
      , DFT_NO
FROM TB_NOTIFICATION A
WHERE NTCN_RCVER_EMP_NO = '202309001' AND NTCN_READ_STATUS = 'N';

--비품신청 SELECT
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, A.APPLICATION_DATE,
       B.EMP_NO,(SELECT EMP_NM FROM EMP WHERE EMP_NO = B.EMP_NO) EMP_NM
FROM FIXTURES A
LEFT OUTER JOIN DOC_WRI B ON A.DFT_NO = B.DFT_NO
WHERE B.EMP_NO = '202309001';

--비품신청 상세정보
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
       ,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
FROM FIXTURES A
LEFT JOIN DOC_WRI C ON C.DFT_NO = A.DFT_NO
WHERE C.DFT_NO = '16' AND C.EMP_NO = '202309002';


select A.EMP_NO, A.EMP_NM, A.EMP_PASS, A.GEN_CODE, A.EMP_MAIL , A.EMP_ADDR1, A.EMP_ADDR2, A.EMP_ADDR3, A.EMP_BRDT, A.REGN , 
        A.CP_NO, A.OFC_NO, A.EMP_IMG, A.BANK_CODE, A.ACCT_NO , A.OWNER, A.ENT_DATE, A.OUT_DATE, A.EMP_MM, A.CTRT_CASE_CODE , A.ENT_CASE_CODE,
        A.BS_WKTIME, A.DTCODE, A.PTN_CODE, A.JCODE , A.GRD_CODE, A.ENABLED, A.ST_CLF_CD, A.EMP_SALARY,
        A.PROFILE_FILE_NO, A.SIGN_FILE_NO , A.WORK_METHOD_CODE ,A.DCODE , B.AUTHRT_ID
from EMP A , AUTHOR_ALWNC B where A.EMP_NO = '202301054' and A.EMP_NO = B.EMP_NO