SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE 'TB_IL%' ORDER BY 1;
SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS LIKE '%Âü¿©%';
SELECT * FROM TB_IL1104M; --ÀÏÁ¤½ÂÀÎ ¼³Á¤
SELECT * FROM TB_SY1018M; --ºÎÀç¼³Á¤

SELECT DISTINCT TABLE_NAME,COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME LIKE 'TB_IL%' ORDER BY 1;
SELECT * FROM SIMSLIB.TB_IL2029D; --FORM E
--SELECT * FROM TB_IL1121D; --OTHER APPROVAL
SELECT * FROM SIMSLIB.TB_IL1120D; --ÁÖ¿äÅõÀÚÁ¶°Çº¯°æ
--SELECT * FROM TB_IL1108D; --CONTRACT
SELECT * FROM SIMSLIB.TB_IL1103D; --FORM A/FORM D

SELECT * FROM TB_IL1007D order by invtex_seq, hld_seq; --FORM A/FORM D
--Àç¿ø
--¼ø¹ø
--INP_OTSD_TPCD : ³»ºÎ/¿ÜºÎ
--INV_DBTCMTE_TPCD
--EMPNO_NM
SELECT * FROM TB_IL1004D;
SELECT * FROM SIMSLIB.TB_IL1004H order by rsc_seq,his_seq;
SELECT * FROM TB_IL1102D --where his_seq > 0 
order by invtex_seq, hld_seq, his_seq;

SELECT * FROM TB_SY1007M WHERE CDTP='APPR_JOB_TPCD';
SELECT * FROM TB_SY1007M WHERE CDTP='INV_DBTCMTE_TPCD';
SELECT * FROM TB_SY1001M WHERE USERID='ADMIN00';
--02	´ëÇ¥Æݵå¸Å´ÏÀú
--11	Åõ½ÉÀ§¿ø
SELECT * FROM TB_FD1003M WHERE CMBT_CUST_NO ='113';

SELECT * FROM SIMSLIB.TB_IL1151D;
INSERT INTO TB_IL1151D
--SELECT  IB.MAST_FNMN_NM,IA.*
SELECT  IA.INVTEX_SEQ
    ,   IA.HLD_SEQ
    ,   IA.RSC_CUST_NO
    ,   '1'                                             AS INP_OTSD_TPCD
    ,   ROW_NUMBER()OVER(PARTITION BY IA.INVTEX_SEQ
                                  ,   IA.HLD_SEQ
                                  ,   IA.RSC_CUST_NO
                         ORDER BY     IA.INVTEX_SEQ
                                  ,   IA.HLD_SEQ
                                  ,   IA.RSC_CUST_NO
                                  ,   IB.MAST_FNMN_NM)  AS DCSN_SEQ
    ,   NVL2(IB.MAST_FNMN_NM,'01','11')                 AS INV_DBTCMTE_TPCD
    ,   IA.EMPNO_NM
    ,   ''                                              AS BIGO
    ,   IA.REG_DTM
    ,   IA.REG_EMPNO
    ,   IA.REG_IP
    ,   IA.MODY_DTM
    ,   IA.MODY_EMPNO
    ,   IA.MODY_IP
    ,   IA.DEL_YN
FROM
(
    SELECT  A.INVTEX_SEQ
        ,   A.HLD_SEQ
        ,   B.RSC_CUST_NO
        ,   C.EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL1007D  A
    JOIN
    (
        SELECT  DISTINCT INVTEX_SEQ
            ,   HLD_SEQ
            ,   RSC_CUST_NO
        FROM    TB_IL1004D  
        WHERE   DEL_YN = '0'
    )    B
    ON      A.INVTEX_SEQ  = B.INVTEX_SEQ
    AND     A.HLD_SEQ     = B.HLD_SEQ
    JOIN
    (
        SELECT  APPR_JOB_TPCD
            ,   RSC_CUST_NO
            ,   SEQ
            ,   INP_OTSD_TPCD
            ,   EMPNO_NM
            ,   DECODE(APPR_JOB_TPCD,'24','05','03') AS INVTEX_LVL_TPCD
            ,   BIGO
        FROM    TB_IL1104M  CA
        WHERE   CA.DEL_YN = '0'
        AND     CA.APPR_JOB_TPCD IN ('14','24')    
    )   C
    ON      A.INVTEX_LVL_TPCD = C.INVTEX_LVL_TPCD
    AND     B.RSC_CUST_NO     = C.RSC_CUST_NO
    WHERE   A.DEL_YN ='0'
    UNION
    SELECT  A.INVTEX_SEQ
        ,   A.HLD_SEQ
        ,   B.RSC_CUST_NO
        ,   C.MAST_FNMN_NM    AS EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL1007D  A
    JOIN
    (
        SELECT  DISTINCT INVTEX_SEQ
            ,   HLD_SEQ
            ,   RSC_CUST_NO
        FROM    TB_IL1004D  
        WHERE   DEL_YN = '0'
    )    B
    ON      A.INVTEX_SEQ  = B.INVTEX_SEQ
    AND     A.HLD_SEQ     = B.HLD_SEQ
    LEFT JOIN
    (
         SELECT  CMBT_CUST_NO
             ,   CMBT_NM
             ,   MAST_FNMN_NM
         FROM    TB_FD1003M
         WHERE   DEL_YN = '0'
    )   C
    ON     B.RSC_CUST_NO     = C.CMBT_CUST_NO
    WHERE   A.DEL_YN='0'
    AND     C.MAST_FNMN_NM IS NOT NULL
) IA
LEFT JOIN   TB_FD1003M  IB
ON    IA.RSC_CUST_NO  = IB.CMBT_CUST_NO
AND   IA.EMPNO_NM     = IB.MAST_FNMN_NM
JOIN
(
    SELECT  DISTINCT INVTEX_SEQ
        ,   HLD_SEQ
        ,   RSC_CUST_NO
        ,   INP_OTSD_TPCD
    FROM    TB_IL1103D
    WHERE   DEL_YN ='0'
)   IC
ON    IA.INVTEX_SEQ = IC.INVTEX_SEQ
AND   IA.HLD_SEQ    = IC.HLD_SEQ
AND   IA.RSC_CUST_NO= IC.RSC_CUST_NO
    ;


          COMMIT;
SELECT * FROM TB_IL1152D;
INSERT INTO TB_IL1152D
SELECT  IA.INVTEX_SEQ
    ,   IA.HLD_SEQ
    ,   IA.HIS_SEQ
    ,   IA.RSC_CUST_NO
    ,   '1'                                             AS INP_OTSD_TPCD
    ,   ROW_NUMBER()OVER(PARTITION BY IA.INVTEX_SEQ
                                  ,   IA.HLD_SEQ
                                  ,   IA.RSC_CUST_NO
                         ORDER BY     IA.INVTEX_SEQ
                                  ,   IA.HLD_SEQ
                                  ,   IA.RSC_CUST_NO
                                  ,   IB.MAST_FNMN_NM)  AS DCSN_SEQ
    ,   NVL2(IB.MAST_FNMN_NM,'01','11')                 AS INV_DBTCMTE_TPCD
    ,   IA.EMPNO_NM
    ,   ''                                              AS BIGO
    ,   IA.REG_DTM
    ,   IA.REG_EMPNO
    ,   IA.REG_IP
    ,   IA.MODY_DTM
    ,   IA.MODY_EMPNO
    ,   IA.MODY_IP
    ,   IA.DEL_YN
FROM
(
    SELECT  A.INVTEX_SEQ
        ,   A.HLD_SEQ
        ,   A.HIS_SEQ
        ,   B.RSC_CUST_NO
        ,   C.EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL1102D  A
    JOIN
    (
        SELECT  DISTINCT INVTEX_SEQ
            ,   HLD_SEQ
            ,   HIS_SEQ
            ,   RSC_CUST_NO
        FROM    TB_IL1004H  
        WHERE   DEL_YN = '0'
    )    B
    ON      A.INVTEX_SEQ  = B.INVTEX_SEQ
    AND     A.HLD_SEQ     = B.HLD_SEQ
    AND     A.HIS_SEQ     = B.HIS_SEQ
    JOIN
    (
        SELECT  APPR_JOB_TPCD
            ,   RSC_CUST_NO
            ,   SEQ
            ,   INP_OTSD_TPCD
            ,   EMPNO_NM
            ,   BIGO
        FROM    TB_IL1104M  CA
        WHERE   CA.DEL_YN = '0'
        AND     CA.APPR_JOB_TPCD ='34'
    )   C
    ON      B.RSC_CUST_NO     = C.RSC_CUST_NO
    WHERE   A.DEL_YN = '0'
    AND     A.HIS_SEQ > 0
    UNION
    SELECT  A.INVTEX_SEQ
        ,   A.HLD_SEQ
        ,   A.HIS_SEQ
        ,   B.RSC_CUST_NO
        ,   C.MAST_FNMN_NM    AS EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL1102D  A
    JOIN
    (
        SELECT  DISTINCT INVTEX_SEQ
            ,   HLD_SEQ
            ,   HIS_SEQ
            ,   RSC_CUST_NO
        FROM    TB_IL1004H  
        WHERE   DEL_YN = '0'
    )    B
    ON      A.INVTEX_SEQ  = B.INVTEX_SEQ
    AND     A.HLD_SEQ     = B.HLD_SEQ
    AND     A.HIS_SEQ     = B.HIS_SEQ
    LEFT JOIN
    (
         SELECT  CMBT_CUST_NO
             ,   CMBT_NM
             ,   MAST_FNMN_NM
         FROM    TB_FD1003M
         WHERE   DEL_YN = '0'
    )   C
    ON     B.RSC_CUST_NO = C.CMBT_CUST_NO
    WHERE   A.DEL_YN = '0'
    AND     A.HIS_SEQ > 0
    AND     C.MAST_FNMN_NM IS NOT NULL
) IA
LEFT JOIN   TB_FD1003M  IB
ON    IA.RSC_CUST_NO  = IB.CMBT_CUST_NO
AND   IA.EMPNO_NM     = IB.MAST_FNMN_NM
JOIN
(
    SELECT  DISTINCT INVTEX_SEQ
        ,   HLD_SEQ
        ,   HIS_SEQ
        ,   RSC_CUST_NO
        ,   INP_OTSD_TPCD
    FROM    TB_IL1120D
    WHERE   DEL_YN ='0'
)   IC
ON    IA.INVTEX_SEQ = IC.INVTEX_SEQ
AND   IA.HLD_SEQ    = IC.HLD_SEQ
AND   IA.HIS_SEQ    = IC.HIS_SEQ
AND   IA.RSC_CUST_NO= IC.RSC_CUST_NO
    ;
    
SELECT * FROM SIMSLIB.TB_IL2028M;    
SELECT * FROM SIMSLIB.TB_IL2028D;   
SELECT * FROM SIMSLIB.TB_IL2029D;   
SELECT * FROM TB_IL1153D;--
INSERT INTO TB_IL1153D
SELECT  IA.WTHD_SEQ
    ,   IA.RSC_CUST_NO
    ,   1                                               AS HIS_SEQ
    ,   ROW_NUMBER()OVER(PARTITION BY IA.WTHD_SEQ
                                  ,   IA.RSC_CUST_NO
                         ORDER BY     IA.WTHD_SEQ
                                  ,   IA.RSC_CUST_NO
                                  ,   IB.MAST_FNMN_NM)  AS DCSN_SEQ
    ,   NVL2(IB.MAST_FNMN_NM,'01','11')                 AS WTHD_DBTCMTE_TPCD
    ,   IA.EMPNO_NM
    ,   ''                                              AS BIGO
    ,   IA.REG_DTM
    ,   IA.REG_EMPNO
    ,   IA.REG_IP
    ,   IA.MODY_DTM
    ,   IA.MODY_EMPNO
    ,   IA.MODY_IP
    ,   IA.DEL_YN
FROM
(
    SELECT  A.WTHD_SEQ
        ,   B.RSC_CUST_NO
        ,   C.EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL2028M  A
    JOIN
    (
        SELECT  DISTINCT BA.WTHD_SEQ  AS WTHD_SEQ
            ,   BB.CMBT_CUST_NO       AS RSC_CUST_NO
        FROM    TB_IL2028D  BA
        JOIN    TB_IL1043D  BB
        ON      BA.CNTRNO = BB.CNTRNO
        AND     BA.EXE_DGRN = BB.EXE_DGRN
        WHERE   BA.DEL_YN = '0'
    )    B
    ON      A.WTHD_SEQ  = B.WTHD_SEQ
    JOIN
    (
        SELECT  APPR_JOB_TPCD
            ,   RSC_CUST_NO
            ,   SEQ
            ,   INP_OTSD_TPCD
            ,   EMPNO_NM
            ,   BIGO
        FROM    TB_IL1104M  CA
        WHERE   CA.DEL_YN = '0'
        AND     CA.APPR_JOB_TPCD ='55'
    )   C
    ON      B.RSC_CUST_NO = C.RSC_CUST_NO
    WHERE   A.DEL_YN = '0'
    UNION
    SELECT  A.WTHD_SEQ
        ,   B.RSC_CUST_NO
        ,   C.MAST_FNMN_NM    AS EMPNO_NM
        ,   A.REG_DTM
        ,   A.REG_EMPNO
        ,   A.REG_IP
        ,   A.MODY_DTM
        ,   A.MODY_EMPNO
        ,   A.MODY_IP
        ,   A.DEL_YN
    FROM    TB_IL2028M  A
    JOIN
    (
        SELECT  DISTINCT BA.WTHD_SEQ  AS WTHD_SEQ
            ,   BB.CMBT_CUST_NO       AS RSC_CUST_NO
        FROM    TB_IL2028D  BA
        JOIN    TB_IL1043D  BB
        ON      BA.CNTRNO = BB.CNTRNO
        AND     BA.EXE_DGRN = BB.EXE_DGRN
        WHERE   BA.DEL_YN = '0'
    )    B
    ON      A.WTHD_SEQ  = B.WTHD_SEQ
    LEFT JOIN
    (
         SELECT  CMBT_CUST_NO
             ,   CMBT_NM
             ,   MAST_FNMN_NM
         FROM    TB_FD1003M
         WHERE   DEL_YN = '0'
    )   C
    ON     B.RSC_CUST_NO = C.CMBT_CUST_NO
    WHERE   A.DEL_YN = '0'
    AND     C.MAST_FNMN_NM IS NOT NULL
) IA
LEFT JOIN   TB_FD1003M  IB
ON    IA.RSC_CUST_NO  = IB.CMBT_CUST_NO
AND   IA.EMPNO_NM     = IB.MAST_FNMN_NM
JOIN
(
    SELECT  DISTINCT WTHD_SEQ
        ,   RSC_CUST_NO
    FROM    TB_IL2029D
    WHERE   DEL_YN ='0'
)   IC
ON    IA.WTHD_SEQ = IC.WTHD_SEQ
AND   IA.RSC_CUST_NO= IC.RSC_CUST_NO
    ;
    COMMIT;