-- システム情報テーブル  銀行情報変更
CREATE OR REPLACE FUNCTION f_systeminfo_bank_update(
    IN integer,

    IN varchar, -- 銀行名
    IN varchar,
    IN varchar,
    IN integer,
    IN varchar,
    IN varchar,
    
    IN character varying,
    IN character varying,
    IN integer,

    OUT errcd integer,
    OUT errmsg character varying,
    OUT code integer) AS
$$

DECLARE
    kubun ALIAS FOR $1;               -- 区分

    i_bankname ALIAS FOR $2;          -- 銀行名
    i_shitenname ALIAS FOR $3;        -- 支店名
    i_shitennumberkey ALIAS FOR $4;   -- 支店番号
    i_kouzadiv ALIAS FOR $5;         -- 口座種類
    i_kouzanumberkey ALIAS FOR $6;    -- 口座番号
    i_kouzaname ALIAS FOR $7;         -- 口座名義
    
    i_updusrnm ALIAS FOR $8;              -- 更新ユーザー名
    i_updpcnm ALIAS FOR $9;               -- 更新マシン名
    i_updcnt ALIAS FOR $10;               -- UpdCnt
    
    --cntcheck integer;
    rec RECORD;
    nowdate timestamp;
    newupdcnt integer;

BEGIN
-- 必須チェック --

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

    IF i_updusrnm IS NULL THEN
        RAISE EXCEPTION '更新ユーザー名は必須です';
    END IF;
    IF i_updpcnm IS NULL THEN
        RAISE EXCEPTION '更新PC名は必須です';
    END IF;
    IF i_updcnt IS NULL THEN
        RAISE EXCEPTION 'UpdCntは必須です';
    END IF;
    
    -- 更新カウンタチェック
    /*
    IF kubun IN (2) THEN
        SELECT updcnt INTO cntcheck FROM t_systeminfo WHERE validflg=TRUE;
          IF NOT FOUND OR cntcheck <> i_updcnt THEN
            RAISE EXCEPTION '更新カウンタが不正です';
          END IF;
    END IF;
    */
    
    IF kubun IN (2) THEN
        SELECT * INTO rec FROM t_systeminfo WHERE validflg=TRUE;
          IF NOT FOUND OR rec.updcnt <> i_updcnt THEN
            RAISE EXCEPTION '更新カウンタが不正です';
          END IF;
    END IF;
    
    --- 更新処理 ---
    IF kubun = 2 THEN
        UPDATE t_systeminfo SET
            bankname     = i_bankname,
            shitenname     = i_shitenname,
            shitennumberkey     = i_shitennumberkey,
            kouzadiv = i_kouzadiv,
            kouzanumberkey = i_kouzanumberkey,
            kouzaname = i_kouzaname,
            
            upddate      = nowdate,
            updusrnm     = i_updusrnm,
            updpcnm      = i_updpcnm
            WHERE validflg=TRUE;

        SELECT * INTO rec FROM t_bank
          WHERE shitennumberkey = i_shitennumberkey
            AND kouzanumberkey = i_kouzanumberkey
            AND delflg=FALSE;

        IF NOT FOUND THEN
            UPDATE t_bank SET delflg=TRUE; -- 新しい行以外は削除
            INSERT INTO t_bank (shitennumberkey, kouzanumberkey) VALUES (i_shitennumberkey, i_kouzanumberkey);
        ELSE
            UPDATE t_bank SET 
                shitennumberkey = i_shitennumberkey,
                kouzanumberkey = i_kouzanumberkey
                WHERE delflg=FALSE;
        END IF;
            
    END IF;
    
    code := 1;

END;
$$
LANGUAGE 'plpgsql' VOLATILE;