#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_DELETE_001)
#--在庫処理済データ削除処理
#-----------------------------------------------------------

--KEY:FU_DELETE_001

DELETE FROM
    FU_T_FRNT_HISUK_ZKUP
WHERE
    (
        HISUK_ZKUP_RSLT_CD='0' AND
        SUBSTR(ZAIKO_CFM_DTM,1,8) < TO_CHAR(TO_DATE(?,'YYYYMMDD') - 60,'YYYYMMDD')
    )
    OR
    (
        ZKUP_FAIL_LIST_OPUTZM_FLG = '1' AND
        SUBSTR(ZKUP_FAIL_LIST_OPUT_YMD,1,8) < TO_CHAR(TO_DATE(?,'YYYYMMDD') - 180,'YYYYMMDD')
    )

#-

#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_SELECT_001)
#--在庫処理未済データ抽出処理
#-----------------------------------------------------------

--KEY:FU_SELECT_001

SELECT
    FU0321.FRNT_HISUK_ZKUP_NO,
    FU0321.KKTK_SVC_KEI_NO,
    FU0321.KIKI_CHG_NO,
    FU0321.HISUK_ZKUP_RSLT_CD,
    FU0321.HISUK_ZKUP_RSLT_DTL_CD,
    FU0321.ZKUP_FAIL_LIST_OPUTZM_FLG,
    FU0321.ZKUP_FAIL_LIST_OPUT_YMD,
    FU0321.HISUK_ZKUP_SBT_CD,
    KK0341.TAKNKIKI_SBT_CD
FROM
    FU_T_FRNT_HISUK_ZKUP FU0321
    INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ON
    FU0321.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND
    FU0321.KIKI_CHG_NO = KK0341.KIKI_CHG_NO AND
    (KK0341.KKTK_SVC_KEI_NO, KK0341.GENE_ADD_DTM) =
            (
                SELECT
                    KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.GENE_ADD_DTM)
                FROM
                    KK_T_KKTK_SVC_KEI KK0341_GENE
                WHERE
                    KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                    AND KK0341_GENE.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                    AND KK0341_GENE.MK_FLG = '0'
                GROUP BY
                    KK0341_GENE.KKTK_SVC_KEI_NO, KK0341_GENE.KIKI_CHG_NO
            ) AND
    FU0321.ZAIKO_CFM_ZM_FLG='0' AND
    FU0321.MK_FLG='0'

#-

#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_SELECT_002)
#--Ｗｅｂ受付処理結果エラーリスト抽出処理
#-----------------------------------------------------------

--KEY:FU_SELECT_002

SELECT
    FU0321.FRNT_HISUK_ZKUP_NO,
    KK0751.SVC_NM,
    KK0341.SVC_KEI_NO,
    KK0341.GENE_ADD_DTM,
    (SELECT ZM0171.CD_DIV_ALI FROM ZM_M_CD_NM_KANRI ZM0171 WHERE ZM0171.CD_SBT_CD = 'CD01580' AND ZM0171.CD_DIV = FU0321.HISUK_ZKUP_RSLT_CD AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0') AS HISUK_ZKUP_RSLT_NM,
    FU0321.HISUK_ZKUP_RSLT_DTL_CD,
    (SELECT ZM0171.CD_DIV_ALI FROM ZM_M_CD_NM_KANRI ZM0171 WHERE ZM0171.CD_SBT_CD = 'CD01581' AND ZM0171.CD_DIV = FU0321.HISUK_ZKUP_RSLT_DTL_CD AND ? BETWEEN ZM0171.CD_TSTAYMD AND ZM0171.CD_TENDYMD AND ZM0171.MK_FLG = '0') AS HISUK_ZKUP_RSLT_DTL_NM,
    FU0321.HISUK_ZKUP_RSLT_CD,
    FU0321.KKTK_SVC_KEI_NO,
    FU0321.KIKI_CHG_NO,
    FU0321.ZAIKO_CFM_ZM_FLG,
    FU0321.ZAIKO_CFM_DTM,
    FU0321.ZKUP_FAIL_LIST_OPUT_YMD
FROM
    FU_T_FRNT_HISUK_ZKUP FU0321
    INNER JOIN KK_T_KKTK_SVC_KEI KK0341 ON
        FU0321.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO AND
        FU0321.KIKI_CHG_NO = KK0341.KIKI_CHG_NO AND
        TO_NUMBER(FU0321.HISUK_ZKUP_RSLT_CD) > 1 AND
        (KK0341.KKTK_SVC_KEI_NO, KK0341.GENE_ADD_DTM) =
                (
                    SELECT
                        KK0341_GENE.KKTK_SVC_KEI_NO, MAX(KK0341_GENE.GENE_ADD_DTM)
                    FROM
                        KK_T_KKTK_SVC_KEI KK0341_GENE
                    WHERE
                        KK0341_GENE.KKTK_SVC_KEI_NO = KK0341.KKTK_SVC_KEI_NO
                        AND KK0341_GENE.KIKI_CHG_NO = KK0341.KIKI_CHG_NO
                        AND KK0341_GENE.MK_FLG = '0'
                    GROUP BY
                        KK0341_GENE.KKTK_SVC_KEI_NO, KK0341_GENE.KIKI_CHG_NO
                ) AND
        FU0321.ZKUP_FAIL_LIST_OPUTZM_FLG = '0' AND
        FU0321.MK_FLG = '0'
    INNER JOIN KK_T_SVC_KEI KK0081 ON
        KK0081.SVC_KEI_NO = KK0341.SVC_KEI_NO AND
        (KK0081.RSV_APLY_YMD || KK0081.GENE_ADD_DTM) =
                (
                    SELECT
                        MAX(KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) AS KK0081_MAX
                    FROM
                        KK_T_SVC_KEI KK0081_GENE
                    WHERE
                        KK0081_GENE.SVC_KEI_NO = KK0081.SVC_KEI_NO AND
                        KK0081_GENE.RSV_APLY_YMD <= ? AND
                        KK0081_GENE.RSV_APLY_CD = '2' AND
                        KK0081_GENE.MK_FLG = '0'
                )
    INNER JOIN KK_M_SVC KK0751 ON
        KK0751.SVC_CD = KK0081.SVC_CD AND
        (KK0751.RSV_APLY_YMD || KK0751.GENE_ADD_DTM) =
                (
                    SELECT
                        MAX(KK0751_GENE.RSV_APLY_YMD || KK0751_GENE.GENE_ADD_DTM) AS KK0751_MAX
                    FROM
                        KK_M_SVC KK0751_GENE
                    WHERE
                        KK0751_GENE.SVC_CD = KK0751.SVC_CD AND
                        KK0751_GENE.RSV_APLY_YMD <= ? AND
                        KK0751_GENE.MK_FLG = '0'
                )
ORDER BY HISUK_ZKUP_RSLT_CD,HISUK_ZKUP_RSLT_DTL_CD,FRNT_HISUK_ZKUP_NO

#-

#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_UPDATE_001)
#--フロント配送受付在庫結果更新処理
#-----------------------------------------------------------

--KEY:FU_UPDATE_001

UPDATE FU_T_FRNT_HISUK_ZKUP SET
    ZAIKO_CFM_ZM_FLG = ?,
    ZAIKO_CFM_DTM = ?,
    HISUK_ZKUP_RSLT_CD = ?,
    HISUK_ZKUP_RSLT_DTL_CD = ?,
    ZKUP_FAIL_LIST_OPUTZM_FLG = '0',
    UPD_DTM = 'UPD_DTM',
    UPD_UNYO_YMD = 'UPD_UNYO',
    UPD_TRN_ID = 'UPD_TRN',
    UPD_OPEACNT = 'UPD_OPE',
    MK_FLG = '0'
WHERE
    FRNT_HISUK_ZKUP_NO = ?

#-

#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_UPDATE_002)
#--Ｗｅｂ受付処理結果エラーリスト出力結果更新処理
#-----------------------------------------------------------

--KEY:FU_UPDATE_002

UPDATE FU_T_FRNT_HISUK_ZKUP SET
    ZKUP_FAIL_LIST_OPUTZM_FLG = ?,
    ZKUP_FAIL_LIST_OPUT_YMD = ?,
    UPD_DTM = 'UPD_DTM',
    UPD_UNYO_YMD = 'UPD_UNYO',
    UPD_TRN_ID = 'UPD_TRN',
    UPD_OPEACNT = 'UPD_OPE',
    MK_FLG = '0'
WHERE
    FRNT_HISUK_ZKUP_NO = ?

#-

#-----------------------------------------------------------
#--フロント配送受付在庫更新(FU_T_FRNT_HISUK_ZKUP-FU_SELECT_003)
#--在庫処理未済データロック処理
#-----------------------------------------------------------

--KEY:FU_SELECT_003

SELECT
    FU0321.FRNT_HISUK_ZKUP_NO
FROM
    FU_T_FRNT_HISUK_ZKUP FU0321
WHERE
    FU0321.FRNT_HISUK_ZKUP_NO = ?
FOR UPDATE NOWAIT

#-