-- 入金テーブル 追加
-- 区分は   1,追加   2,変更     3,削除     のみ
CREATE OR REPLACE FUNCTION f_forward_detail_update(
    IN kubun             integer,   -- 区分

    IN _orderno          integer,   -- 受注No
    IN _orderrowno       integer,   -- 受注行No
    IN _forwarddate      date,      -- 出荷日
    IN _forwardstate     integer,   -- 進捗状況
    IN _returnreason     varchar,   -- 返品理由

    IN usr               varchar,   -- 更新ユーザ
    IN pc                varchar,   -- 更新マシン

    OUT errcd            integer,
    OUT errmsg           varchar,
    OUT code             integer) AS
$$
DECLARE
    rec      RECORD;
    cntcheck integer;
    i integer;
BEGIN
    --
    -- パラメータチェック
    --

    -- 処理区分
    IF kubun IS NULL OR kubun NOT IN (1,2,3) THEN
        RAISE EXCEPTION '処理区分が不正です';
    END IF;


    IF kubun IN (1,2,3) THEN
        IF _orderno IS NULL THEN
            RAISE EXCEPTION '受注NOは必須です';
        END IF;
        IF s_check_upd(usr, pc) = FALSE THEN
            RAISE EXCEPTION '引数 usr&pc が不正です';
        END IF;
    END IF;

    IF kubun IN (1,2) THEN
        IF _forwardstate IS NULL THEN
            RAISE EXCEPTION '進捗状況は必須です';
        END IF;
        IF s_check_length(_returnreason, 1024) = FALSE THEN
            RAISE EXCEPTION '引数 name が不正です';
        END IF;
    END IF;

    -- 存在チェック
    IF kubun = 2 THEN
        SELECT orderno INTO cntcheck FROM t_order_title WHERE orderno = _orderno;
        IF NOT FOUND THEN
            RAISE EXCEPTION '引数 orderno がt_order_titleに存在しません';
        END IF;
        IF _orderrowno IS NOT NULL THEN
            SELECT orderrowno INTO cntcheck FROM t_order_detail WHERE orderno = _orderno AND orderrowno = _orderrowno;
            IF NOT FOUND THEN
                RAISE EXCEPTION '引数 orderrowno がt_order_detailに存在しません';
            END IF;
        END IF;
    END IF;




    ---- 処理
    -- 追加
    IF kubun = 1 THEN
        SELECT orderno INTO cntcheck FROM t_forward_detail WHERE orderno = _orderno;
        IF FOUND THEN
            UPDATE t_forward_detail SET
                forwarddate = _forwarddate,
                forwardstate = _forwardstate,
                returnreason = _returnreason,
                updusrnm = usr,
                updpcnm = pc
                WHERE orderno = _orderno;
            errcd := 1;
            errmsg := 'æ›´æ–°';

        ELSE
            i := 1;
            LOOP
                INSERT INTO t_forward_detail(orderno, orderrowno, forwarddate, forwardstate, returnreason, updusrnm, updpcnm)
                        VALUES(_orderno, i, _forwarddate, _forwardstate, _returnreason, usr, pc);
                EXIT WHEN i >= _orderrowno;
                i := i + 1;
            END LOOP;
            errcd := 1;
            errmsg := '追加';
        END IF;

    -- æ›´æ–°
    ELSIF kubun = 2 THEN

        -- 受注NO と 受注行NOで検索
        SELECT orderrowno INTO cntcheck FROM t_forward_detail WHERE orderno = _orderno AND orderrowno = _orderrowno;

        IF FOUND THEN   -- 両方ある○○
            UPDATE t_forward_detail SET
                forwarddate = _forwarddate,
                forwardstate = _forwardstate,
                returnreason = _returnreason,

                updusrnm = usr,
                updpcnm = pc
                 WHERE orderno = _orderno AND orderrowno = _orderrowno;
            errcd := 1;
            errmsg := 'æ›´æ–°';

        ELSE

            SELECT orderno INTO cntcheck FROM t_forward_detail WHERE orderno = _orderno;

            IF FOUND THEN   -- 受注NOはある
                IF _orderrowno IS NULL THEN    -- ○×
                    UPDATE t_forward_detail SET
                        forwarddate = _forwarddate,
                        forwardstate = _forwardstate,
                        returnreason = _returnreason,

                        updusrnm = usr,
                        updpcnm = pc
                        WHERE orderno = _orderno;
                errcd := 1;
                errmsg := 'æ›´æ–°';

                ELSE    -- â—‹â–³
                    INSERT INTO t_forward_detail(orderno, orderrowno, forwarddate, forwardstate, returnreason, updusrnm, updpcnm)
                        VALUES(_orderno, _orderrowno, _forwarddate, _forwardstate, _returnreason, usr, pc);
                    errcd := 1;
                    errmsg := '追加';

                END IF;

            ELSE -- 受注NOもない
                IF _orderrowno IS NOT NULL THEN    -- △△
                    INSERT INTO t_forward_detail(orderno, orderrowno, forwarddate, forwardstate, returnreason, updusrnm, updpcnm)
                        VALUES(_orderno, _orderrowno, _forwarddate, _forwardstate, _returnreason, usr, pc);
                    errcd := 1;
                    errmsg := '追加';

                ELSE    -- △×
                    RAISE EXCEPTION '受注No[%]のレコードはありません',_orderno;
                END IF;
            END IF;
        END IF;



    -- 削除
    ELSIF kubun = 3 THEN

        SELECT orderrowno INTO cntcheck FROM t_forward_detail WHERE orderno = _orderno AND orderrowno = _orderrowno;
        IF FOUND THEN
            UPDATE t_forward_detail SET
                delflg = TRUE,
                updusrnm = usr,
                updpcnm = pc
                WHERE orderno = _orderno AND orderrowno = _orderrowno;


        ELSIF _orderrowno IS NULL THEN
            SELECT orderrowno INTO cntcheck FROM t_forward_detail WHERE orderno = _orderno;
            IF FOUND THEN
                UPDATE t_forward_detail SET
                    delflg = TRUE,
                    updusrnm = usr,
                    updpcnm = pc
                    WHERE orderno = _orderno;
            ELSE
                RAISE EXCEPTION '受注No[%]のレコードがありません',_orderno;
            END IF;
        ELSE
            RAISE EXCEPTION '受注No[%]と受注行No[%]のレコードがありません', _orderno,_orderrowno;
        END IF;

        errcd := 1;
        code := _orderno;
        errmsg := '削除';

    END IF;

    RETURN ;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;