#-----------------------------------------------------------
#--工事作業項目ワーク(KU_T_KOJI_WRKITEM_WK-KU_SELECT_001)
#--【工事作業項目反映】
#--工事作業項目とワークの比較に使用
#-----------------------------------------------------------

--KEY:KU_SELECT_001

SELECT KU0131_WK.KOJIHI_KOJI_BUNRUI_CD AS WK_KOJIHI_KOJI_BUNRUI_CD,
       KU0131_WK.KOJIHI_KMK_CD AS WK_KOJIHI_KMK_CD,
       KU0131_WK.KOJIHI_TANKA_CD AS WK_KOJIHI_TANKA_CD,
       KU0131.KOJIHI_KOJI_BUNRUI_CD AS KOJIHI_KOJI_BUNRUI_CD,
       KU0131.KOJIHI_KMK_CD AS KOJIHI_KMK_CD,
       KU0131.KOJIHI_TANKA_CD AS KOJIHI_TANKA_CD,
       KU0131_WK.OPMS_KOJIHI_TANKA_APLY_YMD AS WK_OPMS_KOJIHI_TANKA_APLY_YMD,
       KU0131_WK.KOJIHI_TANKA_NM AS WK_KOJIHI_TANKA_NM,
       KU0131_WK.KOJI_KOSYU_TANI_NM AS WK_KOJI_KOSYU_TANI_NM,
       KU0131_WK.KJ_ATTKJ_TANKA_TUJYO AS WK_KJ_ATTKJ_TANKA_TUJYO,
       KU0131_WK.KJ_ATTKJ_TANKA_HLDY AS WK_KJ_ATTKJ_TANKA_HLDY,
       KU0131_WK.KJ_ATTKJ_TANKA_MNGT AS WK_KJ_ATTKJ_TANKA_MNGT,
       KU0131_WK.KJ_TKKJ_TANKA_TUJYO AS WK_KJ_TKKJ_TANKA_TUJYO,
       KU0131_WK.KJ_TKKJ_TANKA_HLDY AS WK_KJ_TKKJ_TANKA_HLDY,
       KU0131_WK.KJ_TKKJ_TANKA_MNGT AS WK_KJ_TKKJ_TANKA_MNGT,
       KU0131_WK.KJ_KOJIHI_CUST_FTN_AMNT AS WK_KJ_KOJIHI_CUST_FTN_AMNT,
       KU0131_WK.KJ_KOJIHI_PRC_ICRT_FLG AS WK_KJ_KOJIHI_PRC_ICRT_FLG,
       KU0131_WK.KOJI_WRKITEM_BIKO AS WK_KOJI_WRKITEM_BIKO,
       KU0131.OPMS_KOJIHI_TANKA_APLY_YMD AS OPMS_KOJIHI_TANKA_APLY_YMD,
       KU0131.KOJIHI_TANKA_NM AS KOJIHI_TANKA_NM,
       KU0131.KOJI_KOSYU_TANI_NM AS KOJI_KOSYU_TANI_NM,
       KU0131.KJ_ATTKJ_TANKA_TUJYO AS KJ_ATTKJ_TANKA_TUJYO,
       KU0131.KJ_ATTKJ_TANKA_HLDY AS KJ_ATTKJ_TANKA_HLDY,
       KU0131.KJ_ATTKJ_TANKA_MNGT AS KJ_ATTKJ_TANKA_MNGT,
       KU0131.KJ_TKKJ_TANKA_TUJYO AS KJ_TKKJ_TANKA_TUJYO,
       KU0131.KJ_TKKJ_TANKA_HLDY AS KJ_TKKJ_TANKA_HLDY,
       KU0131.KJ_TKKJ_TANKA_MNGT AS KJ_TKKJ_TANKA_MNGT,
       KU0131.KJ_KOJIHI_CUST_FTN_AMNT AS KJ_KOJIHI_CUST_FTN_AMNT,
       KU0131.KJ_KOJIHI_PRC_ICRT_FLG AS KJ_KOJIHI_PRC_ICRT_FLG,
       KU0131.KOJI_WRKITEM_BIKO AS KOJI_WRKITEM_BIKO
FROM 
       KU_T_KOJI_WRKITEM_WK KU0131_WK
LEFT OUTER JOIN
       KU_M_KOJI_WRKITEM KU0131
ON
       KU0131_WK.KOJIHI_KOJI_BUNRUI_CD = KU0131.KOJIHI_KOJI_BUNRUI_CD
       AND
       KU0131_WK.KOJIHI_KMK_CD = KU0131.KOJIHI_KMK_CD
       AND
       KU0131_WK.KOJIHI_TANKA_CD = KU0131.KOJIHI_TANKA_CD
WHERE
       KU0131_WK.OPMS_KOJIHI_TANKA_APLY_YMD = 
       (SELECT 
               MAX(KU0131_WK_GENE.OPMS_KOJIHI_TANKA_APLY_YMD)
        FROM 
               KU_T_KOJI_WRKITEM_WK KU0131_WK_GENE
        WHERE 
               KU0131_WK.KOJIHI_KOJI_BUNRUI_CD = KU0131_WK_GENE.KOJIHI_KOJI_BUNRUI_CD
               AND
               KU0131_WK.KOJIHI_KMK_CD = KU0131_WK_GENE.KOJIHI_KMK_CD
               AND
               KU0131_WK.KOJIHI_TANKA_CD = KU0131_WK_GENE.KOJIHI_TANKA_CD
               AND
               KU0131_WK_GENE.MK_FLG = '0'
        )
       AND
       KU0131.GENE_ADD_DTM = 
       (SELECT 
               MAX(KU0131_GENE.GENE_ADD_DTM)
        FROM 
               KU_M_KOJI_WRKITEM KU0131_GENE
        WHERE 
               KU0131.KOJIHI_KOJI_BUNRUI_CD = KU0131_GENE.KOJIHI_KOJI_BUNRUI_CD
               AND
               KU0131.KOJIHI_KMK_CD = KU0131_GENE.KOJIHI_KMK_CD
               AND
               KU0131.KOJIHI_TANKA_CD = KU0131_GENE.KOJIHI_TANKA_CD
               AND
               KU0131_GENE.MK_FLG = '0'
        )
        AND 
        KU0131_WK.MK_FLG = '0'
UNION ALL
SELECT KU0131_WK.KOJIHI_KOJI_BUNRUI_CD,
       KU0131_WK.KOJIHI_KMK_CD,
       KU0131_WK.KOJIHI_TANKA_CD,
       NULL,
       NULL,
       NULL,
       KU0131_WK.OPMS_KOJIHI_TANKA_APLY_YMD,
       KU0131_WK.KOJIHI_TANKA_NM,
       KU0131_WK.KOJI_KOSYU_TANI_NM,
       KU0131_WK.KJ_ATTKJ_TANKA_TUJYO,
       KU0131_WK.KJ_ATTKJ_TANKA_HLDY,
       KU0131_WK.KJ_ATTKJ_TANKA_MNGT,
       KU0131_WK.KJ_TKKJ_TANKA_TUJYO,
       KU0131_WK.KJ_TKKJ_TANKA_HLDY,
       KU0131_WK.KJ_TKKJ_TANKA_MNGT,
       KU0131_WK.KJ_KOJIHI_CUST_FTN_AMNT,
       KU0131_WK.KJ_KOJIHI_PRC_ICRT_FLG,
       KU0131_WK.KOJI_WRKITEM_BIKO,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL
FROM 
       KU_T_KOJI_WRKITEM_WK KU0131_WK
WHERE
       KU0131_WK.OPMS_KOJIHI_TANKA_APLY_YMD = 
       (SELECT 
               MAX(KU0131_WK_GENE.OPMS_KOJIHI_TANKA_APLY_YMD)
        FROM 
               KU_T_KOJI_WRKITEM_WK KU0131_WK_GENE
        WHERE 
               KU0131_WK.KOJIHI_KOJI_BUNRUI_CD = KU0131_WK_GENE.KOJIHI_KOJI_BUNRUI_CD
               AND
               KU0131_WK.KOJIHI_KMK_CD = KU0131_WK_GENE.KOJIHI_KMK_CD
               AND
               KU0131_WK.KOJIHI_TANKA_CD = KU0131_WK_GENE.KOJIHI_TANKA_CD
               AND
               KU0131_WK_GENE.MK_FLG = '0'
        )
        AND 
        KU0131_WK.MK_FLG = '0'     
        AND 
        NOT EXISTS
        (SELECT 1
         FROM 
                KU_M_KOJI_WRKITEM KU0131
         WHERE 
                KU0131_WK.KOJIHI_KOJI_BUNRUI_CD = KU0131.KOJIHI_KOJI_BUNRUI_CD
         AND
                KU0131_WK.KOJIHI_KMK_CD = KU0131.KOJIHI_KMK_CD
         AND
                KU0131_WK.KOJIHI_TANKA_CD = KU0131.KOJIHI_TANKA_CD
         AND
                KU0131.MK_FLG = '0'     
         )
UNION ALL
SELECT NULL,
       NULL,
       NULL,
       KU0131.KOJIHI_KOJI_BUNRUI_CD,
       KU0131.KOJIHI_KMK_CD,
       KU0131.KOJIHI_TANKA_CD,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       KU0131.OPMS_KOJIHI_TANKA_APLY_YMD,
       KU0131.KOJIHI_TANKA_NM,
       KU0131.KOJI_KOSYU_TANI_NM,
       KU0131.KJ_ATTKJ_TANKA_TUJYO,
       KU0131.KJ_ATTKJ_TANKA_HLDY,
       KU0131.KJ_ATTKJ_TANKA_MNGT,
       KU0131.KJ_TKKJ_TANKA_TUJYO,
       KU0131.KJ_TKKJ_TANKA_HLDY,
       KU0131.KJ_TKKJ_TANKA_MNGT,
       KU0131.KJ_KOJIHI_CUST_FTN_AMNT,
       KU0131.KJ_KOJIHI_PRC_ICRT_FLG,
       KU0131.KOJI_WRKITEM_BIKO
FROM 
       KU_M_KOJI_WRKITEM KU0131
WHERE
       KU0131.GENE_ADD_DTM = 
       (SELECT 
               MAX(KU0131_GENE.GENE_ADD_DTM)
        FROM 
               KU_M_KOJI_WRKITEM KU0131_GENE
        WHERE 
               KU0131.KOJIHI_KOJI_BUNRUI_CD = KU0131_GENE.KOJIHI_KOJI_BUNRUI_CD
               AND
               KU0131.KOJIHI_KMK_CD = KU0131_GENE.KOJIHI_KMK_CD
               AND
               KU0131.KOJIHI_TANKA_CD = KU0131_GENE.KOJIHI_TANKA_CD
               AND
               KU0131_GENE.MK_FLG = '0'
        )
        AND 
        KU0131.MK_FLG = '0'     
        AND 
        NOT EXISTS
        (SELECT 1
         FROM 
                KU_T_KOJI_WRKITEM_WK KU0131_WK
         WHERE 
                KU0131_WK.KOJIHI_KOJI_BUNRUI_CD = KU0131.KOJIHI_KOJI_BUNRUI_CD
         AND
                KU0131_WK.KOJIHI_KMK_CD = KU0131.KOJIHI_KMK_CD
         AND
                KU0131_WK.KOJIHI_TANKA_CD = KU0131.KOJIHI_TANKA_CD
         AND
                KU0131_WK.MK_FLG = '0'     
         )

#-

#-----------------------------------------------------------
#--工事作業項目ワーク(KU_T_KOJI_WRKITEM_WK-KU_DELETE_001)
#--【工種関連マスタ反映】
#--工事作業項目ワークのレコードを全削除する。
#-----------------------------------------------------------

--KEY:KU_DELETE_001

DELETE
FROM
    KU_T_KOJI_WRKITEM_WK KU0131_WK

#-