-- 出荷明細TBのステータス更新SP
CREATE OR REPLACE FUNCTION f_forward_detail_state_update(
 IN _orderno        integer,    -- 受注No
 IN _orderrowno     integer,    -- 受注行No
 IN _forwarddate    date,       -- 出荷日
 IN _forwardstate   integer,    -- 進捗状況
 IN _returnreason   varchar,    -- 返品理由

 IN upduser         varchar,    -- 更新ユーザ
 IN pc              varchar,    -- 更新マシン
 IN count           integer,    -- 更新カウンタ

 OUT errcd          integer,
 OUT errmsg         varchar,
 OUT code           integer) AS
$BODY$
DECLARE
    cntcheck  integer;
    checkdate date;      -- 出荷日
    checkreason varchar; -- 返品理由
    rec       RECORD;
    _kubun integer;
BEGIN
    --
    -- パラメータチェック
    --

    -- 必須チェック
    IF _orderno IS NULL THEN
        RAISE EXCEPTION '引数 受注Noは必須です';
    END IF;
    IF _orderrowno IS NULL THEN
        RAISE EXCEPTION '引数 受注行Noは必須です';
    END IF;


    -- 出荷日と返品理由取得
    SELECT forwarddate, returnreason, updcnt INTO rec FROM t_forward_detail WHERE orderno = _orderno AND orderrowno = _orderrowno AND delflg = FALSE;
    IF FOUND THEN
        checkdate := rec.forwarddate;
        checkreason := rec.returnreason;
    END IF;


    -- 更新カウンタチェック
    IF rec.updcnt IS NOT NULL AND rec.updcnt <> count THEN
        RAISE EXCEPTION '更新カウンタが不正です';
    END IF;

    -- 処理チェック
    IF _forwardstate = 4 THEN   -- 出荷済み のとき
        IF _forwarddate IS NULL THEN
            RAISE EXCEPTION '引数 出荷日は必須です';
        END IF;
        checkdate :=_forwarddate;

    ELSIF _forwardstate = 8 THEN
        IF _returnreason IS NULL THEN
            RAISE EXCEPTION '引数 返却理由は必須です';
        END IF;
        checkreason := _returnreason;
    END IF;


    IF rec.updcnt IS NULL THEN
        INSERT INTO
            t_forward_detail(orderno, orderrowno, forwarddate, forwardstate, returnreason, updusrnm, updpcnm)
             VALUES(_orderno, _orderrowno, _forwarddate, _forwardstate, _returnreason, upduser, pc);
    ELSE
        -- ステータス更新
        UPDATE t_forward_detail SET
            forwarddate = checkdate,
            forwardstate = _forwardstate,
            returnreason = checkreason,

            updusrnm = upduser,
            updpcnm = pc
            WHERE orderno = _orderno AND orderrowno = _orderrowno;
    END IF;

    errcd := 1;
    code := _orderno;
    errmsg := '完了';
    RETURN;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;