-- 商品在庫 変更

CREATE OR REPLACE FUNCTION f_stock_update_price(
    IN _itemcd        integer,    -- 商品C
    IN _structcd      integer,    -- 構成C
    IN _stockcd       integer,    -- 在庫C

    IN _sellingprice  integer,    -- 販売価格
    IN _state         integer,    -- ステータス

    IN _updusrnm      varchar,    -- 更新ユーザ
    IN _updpcnm       varchar,    -- 更新マシン

    OUT errcd       integer,
    OUT errmsg      varchar,
    OUT code        integer) AS
$BODY$

DECLARE
    stocktitle integer;
BEGIN

 -- 必須チェック --
    IF _itemcd IS NULL THEN
        RAISE EXCEPTION '商品Cは必須です';
    END IF;
    IF _structcd IS NULL THEN
        RAISE EXCEPTION '構成Cは必須です';
    END IF;
    IF _stockcd IS NULL THEN
        RAISE EXCEPTION '在庫Cは必須です';
    END IF;

    IF _updusrnm IS NULL THEN
        RAISE EXCEPTION '更新ユーザ名が指定されていません';
    END IF;
    IF _updpcnm IS NULL THEN
        RAISE EXCEPTION '更新マシン名が指定されていません';
    END IF;

    -- 販売金額更新
    SELECT stocktitlecd INTO stocktitle FROM t_ex_stock_detail WHERE itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd AND delflg = false;

    IF stocktitle IS NULL THEN
        --更新
        IF _state = 0 THEN
            UPDATE t_ex_stock_detail SET sellingprice = _sellingprice, updusrnm = _updusrnm, updpcnm = _updpcnm WHERE itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;
        ELSIF _state = 1 THEN
            UPDATE t_ex_stock_detail SET sellingprice = _sellingprice, stockstate = 1, updusrnm = _updusrnm, updpcnm = _updpcnm WHERE itemcd = _itemcd AND structcd = _structcd AND stockcd = _stockcd;
        END IF;
    ELSE
        IF _state = 0 THEN
            UPDATE t_ex_stock_detail SET sellingprice = _sellingprice, updusrnm = _updusrnm, updpcnm = _updpcnm WHERE stocktitlecd = stocktitle;
        ELSIF _state = 1 THEN
            UPDATE t_ex_stock_detail SET sellingprice = _sellingprice, stockstate = 1, updusrnm = _updusrnm, updpcnm = _updpcnm WHERE stocktitlecd = stocktitle;
        END IF;
    END IF;

    errcd := 1;
    RETURN;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
