--
--  出荷明細
--      t_forward_detail
--

-- DROP TABLE t_forward_detail;

CREATE TABLE t_forward_detail
(
    orderno         integer not null,   -- PriKey
    orderrowno      integer not null,   -- PriKey
    forwarddate     date,
    forwardstate    integer,
    returnreason    character varying(1024),
    returndate      timestamp without time zone,

    delflg          boolean,
    adddate         timestamp without time zone,
    upddate         timestamp without time zone,
    updusrnm        character varying(60),
    updpcnm         character varying(60),
    updcnt          integer,

    PRIMARY KEY(orderno, orderrowno)
);


-- トリガー
CREATE TRIGGER trigger_master_stamp
  BEFORE INSERT OR UPDATE
  ON t_forward_detail
  FOR EACH ROW
  EXECUTE PROCEDURE g_master_stamp();


-- トリガー用関数: t_forward_detail
-- 品目基本情報の最低価格を更新
-- DROP g_forward_detail2();
CREATE OR REPLACE FUNCTION g_forward_detail2()
    RETURNS "trigger" AS $BODY$
DECLARE
    _price  integer;
    _code   integer;
BEGIN
    SELECT itemcd INTO _code FROM t_order_detail o
       WHERE o.orderno=NEW.orderno
         AND o.orderrowno=NEW.orderrowno
         AND o.delflg = FALSE;

    -- æ›´æ–°
    IF NEW.delflg = FALSE AND _code IS NOT NULL THEN

        -- 全て
        SELECT * INTO _price FROM s_get_lowprice(1, _code);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET lowprice = _price
         WHERE itemcd = _code;

        -- 中古
        SELECT * INTO _price FROM s_get_lowprice(2, _code);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET oldlowprice = _price
         WHERE itemcd = _code;

        -- 新品
        SELECT * INTO _price FROM s_get_lowprice(3, _code);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET newlowprice = _price
         WHERE itemcd = _code;

    END IF;

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


-- トリガ登録
CREATE TRIGGER trigger_forward_detail2
    AFTER INSERT OR UPDATE
    ON t_forward_detail
    FOR EACH ROW
    EXECUTE PROCEDURE g_forward_detail2();