--初期値マスタ

drop table IF EXISTS mst_shokichi CASCADE;

CREATE TABLE mst_shokichi ( kinocd character varying(10) not null,
ctrlid character varying(50) not null,
shokichi character varying(50) not null,
sakuseiuserid character varying(40) not null,
sakuseinichiji timestamp with time zone not null,
koshinuserid character varying(40) not null,
koshinnichiji timestamp with time zone not null) WITH (OIDS = FALSE) ; ALTER TABLE mst_shokichi  OWNER TO postgres;


ALTER TABLE mst_shokichi ADD CONSTRAINT  mst_shokichi_pkey PRIMARY KEY( kinocd,
ctrlid);

COMMENT ON COLUMN mst_shokichi.kinocd IS '機能CD。*指定時は全機能の初期値を意味する';
COMMENT ON COLUMN mst_shokichi.ctrlid IS 'コントロールID。ALLKAISHIBI：すべての開始日を表すコントロール。ALLSHURYOBI：すべての終了日を表すコントロール';
COMMENT ON COLUMN mst_shokichi.shokichi IS '初期値。日付の場合はyyyy/MM/ddで指定する';
COMMENT ON COLUMN mst_shokichi.sakuseiuserid IS '新規作成した時のログインユーザーID';
COMMENT ON COLUMN mst_shokichi.sakuseinichiji IS '新規作成した日時';
COMMENT ON COLUMN mst_shokichi.koshinuserid IS '更新されたときのログインユーザーID';
COMMENT ON COLUMN mst_shokichi.koshinnichiji IS '更新された日時';

insert into mst_shokichi(kinocd,ctrlid,shokichi,sakuseiuserid,sakuseinichiji,koshinuserid,koshinnichiji) values 
('*','ALLKAISHIBI','2001/1/1','admin',clock_timestamp(),'admin',clock_timestamp())
,('*','ALLSHURYOBI','2999/12/31','admin',clock_timestamp(),'admin',clock_timestamp())
,('A000000002','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('B000000002','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('B000000006','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('C000000006','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('C000000007','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('D000000003','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('E000000007','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('F000000006','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('G000000003','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('H000000002','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('J000000007','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('J000000008','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('K000000002','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
,('L000000014','ShoninbiFr','SAISHOBI','admin',clock_timestamp(),'admin',clock_timestamp())
;

insert into public.mst_menu(menucd,oyamenucd,menumei,hyojikb,kinocd,hyojijun,gaiyo,sakuseiuserid,sakuseinichiji,koshinuserid,koshinnichiji,kengengrp,haishifg) values 
    ('2029','0200','初期値マスタ','2','X000000027',1330,'画面初期値をメンテナンスします','admin',clock_timestamp(),'admin',clock_timestamp(),'1',0);



--原価計画
CREATE TABLE dat_m_genkakeikaku ( mitsumorino numeric(10) not null,
oyameisaicd character varying(10) not null,
genkakeikakugyono numeric(4) not null,
meisaikb character varying(1) not null,
shiiresakicd character varying(20) not null,
hinmokucd character varying(40) ,
hinmokumei character varying(100) ,
nisugatacd character varying(4) ,
suryo numeric(15,3) ,
tanicd character varying(4) ,
genkatanka numeric(15,3) ,
kakeritsu numeric(5,2) ,
mitsumoritanka numeric(15,3) ,
biko character varying(100) ,
shanaibiko character varying(100) ,
karikatakamokucd character varying(20) not null,
kashikatakamokucd character varying(20) not null,
sakuseiuserid character varying(40) not null,
sakuseinichiji timestamp with time zone not null,
koshinuserid character varying(40) not null,
koshinnichiji timestamp with time zone not null) WITH (OIDS = FALSE) ; ALTER TABLE dat_m_genkakeikaku  OWNER TO postgres;

CREATE TABLE dat_rm_genkakeikaku ( rirekino bigint not null,
mitsumorino numeric(10) not null,
oyameisaicd character varying(10) not null,
genkakeikakugyono numeric(4) not null,
meisaikb character varying(1) not null,
shiiresakicd character varying(20) not null,
hinmokucd character varying(40) ,
hinmokumei character varying(100) ,
nisugatacd character varying(4) ,
suryo numeric(15,3) ,
tanicd character varying(4) ,
genkatanka numeric(15,3) ,
kakeritsu numeric(5,2) ,
mitsumoritanka numeric(15,3) ,
biko character varying(100) ,
shanaibiko character varying(100) ,
karikatakamokucd character varying(20) not null,
kashikatakamokucd character varying(20) not null,
sakuseiuserid character varying(40) not null,
sakuseinichiji timestamp with time zone not null,
koshinuserid character varying(40) not null,
koshinnichiji timestamp with time zone not null) WITH (OIDS = FALSE) ; ALTER TABLE dat_rm_genkakeikaku  OWNER TO postgres;


ALTER TABLE dat_m_genkakeikaku ADD CONSTRAINT  dat_m_genkakeikaku_pkey PRIMARY KEY( mitsumorino,
oyameisaicd, 
genkakeikakugyono);
ALTER TABLE dat_rm_genkakeikaku ADD CONSTRAINT  dat_rm_genkakeikaku_pkey PRIMARY KEY( rirekino,
mitsumorino, 
oyameisaicd, 
genkakeikakugyono);


COMMENT ON COLUMN dat_m_genkakeikaku.mitsumorino IS '見積No';
COMMENT ON COLUMN dat_m_genkakeikaku.oyameisaicd IS '親明細CD。プロジェクト全体に紐付く場合は空白';
COMMENT ON COLUMN dat_m_genkakeikaku.genkakeikakugyono IS '原価計画明細行No';
COMMENT ON COLUMN dat_m_genkakeikaku.meisaikb IS '明細区分';
COMMENT ON COLUMN dat_m_genkakeikaku.shiiresakicd IS '仕入先CD。仕入先がない場合（販管費など）は空白';
COMMENT ON COLUMN dat_m_genkakeikaku.hinmokucd IS '品目CD';
COMMENT ON COLUMN dat_m_genkakeikaku.hinmokumei IS '品目名';
COMMENT ON COLUMN dat_m_genkakeikaku.nisugatacd IS '荷姿cd';
COMMENT ON COLUMN dat_m_genkakeikaku.suryo IS '数量';
COMMENT ON COLUMN dat_m_genkakeikaku.tanicd IS '単位cd';
COMMENT ON COLUMN dat_m_genkakeikaku.genkatanka IS '原価単価';
COMMENT ON COLUMN dat_m_genkakeikaku.kakeritsu IS '掛率[%]';
COMMENT ON COLUMN dat_m_genkakeikaku.mitsumoritanka IS '見積単価';
COMMENT ON COLUMN dat_m_genkakeikaku.biko IS '備考';
COMMENT ON COLUMN dat_m_genkakeikaku.shanaibiko IS '社内備考';
COMMENT ON COLUMN dat_m_genkakeikaku.karikatakamokucd IS '借方科目CD';
COMMENT ON COLUMN dat_m_genkakeikaku.kashikatakamokucd IS '貸方科目CD';
COMMENT ON COLUMN dat_m_genkakeikaku.sakuseiuserid IS '新規作成した時のログインユーザーID';
COMMENT ON COLUMN dat_m_genkakeikaku.sakuseinichiji IS '新規作成した日時';
COMMENT ON COLUMN dat_m_genkakeikaku.koshinuserid IS '更新されたときのログインユーザーID';
COMMENT ON COLUMN dat_m_genkakeikaku.koshinnichiji IS '更新された日時';
COMMENT ON COLUMN dat_rm_genkakeikaku.rirekino IS '履歴No';
COMMENT ON COLUMN dat_rm_genkakeikaku.mitsumorino IS '見積No';
COMMENT ON COLUMN dat_rm_genkakeikaku.oyameisaicd IS '親明細CD。プロジェクト全体に紐付く場合は空白';
COMMENT ON COLUMN dat_rm_genkakeikaku.genkakeikakugyono IS '原価計画明細行No';
COMMENT ON COLUMN dat_rm_genkakeikaku.meisaikb IS '明細区分';
COMMENT ON COLUMN dat_rm_genkakeikaku.shiiresakicd IS '仕入先CD。仕入先がない場合（販管費など）は空白';
COMMENT ON COLUMN dat_rm_genkakeikaku.hinmokucd IS '品目CD';
COMMENT ON COLUMN dat_rm_genkakeikaku.hinmokumei IS '品目名';
COMMENT ON COLUMN dat_m_genkakeikaku.nisugatacd IS '荷姿cd';
COMMENT ON COLUMN dat_rm_genkakeikaku.suryo IS '数量';
COMMENT ON COLUMN dat_rm_genkakeikaku.tanicd IS '単位cd';
COMMENT ON COLUMN dat_rm_genkakeikaku.genkatanka IS '原価単価';
COMMENT ON COLUMN dat_rm_genkakeikaku.kakeritsu IS '掛率[%]';
COMMENT ON COLUMN dat_rm_genkakeikaku.mitsumoritanka IS '見積単価';
COMMENT ON COLUMN dat_rm_genkakeikaku.biko IS '備考';
COMMENT ON COLUMN dat_rm_genkakeikaku.shanaibiko IS '社内備考';
COMMENT ON COLUMN dat_rm_genkakeikaku.karikatakamokucd IS '借方科目CD';
COMMENT ON COLUMN dat_rm_genkakeikaku.kashikatakamokucd IS '貸方科目CD';
COMMENT ON COLUMN dat_rm_genkakeikaku.sakuseiuserid IS '新規作成した時のログインユーザーID';
COMMENT ON COLUMN dat_rm_genkakeikaku.sakuseinichiji IS '新規作成した日時';
COMMENT ON COLUMN dat_rm_genkakeikaku.koshinuserid IS '更新されたときのログインユーザーID';
COMMENT ON COLUMN dat_rm_genkakeikaku.koshinnichiji IS '更新された日時';



CREATE OR REPLACE FUNCTION insert_r_mitsumori()
  RETURNS trigger AS $$
begin
insert into dat_rd_mitsumori (
rirekino
,sakujofg
,mitsumorino
,mitsumoribi
,tokuisakicd
,tokuisakiatena
,projectcd
,biko
,shanaibiko
,tantoshaid
,bushocd
,tankarankcd
,shoninnichiji
,shoninshaid
,mitsumorikenmei
,yukokigen
,nohinbi
,nohinbasho
,shiharaijoken
,sakuseiuserid
,sakuseinichiji
,koshinuserid
,koshinnichiji
  ) values (
nextval('sqns_mitsumoririrekino')
,false
,new.mitsumorino
,new.mitsumoribi
,new.tokuisakicd
,new.tokuisakiatena
,new.projectcd
,new.biko
,new.shanaibiko
,new.tantoshaid
,new.bushocd
,new.tankarankcd
,new.shoninnichiji
,new.shoninshaid
,new.mitsumorikenmei
,new.yukokigen
,new.nohinbi
,new.nohinbasho
,new.shiharaijoken
,new.sakuseiuserid
,new.sakuseinichiji
,new.koshinuserid
,new.koshinnichiji
  );
insert into dat_rm_mitsumori (
rirekino
,mitsumorino
,gyono
,oyameisaicd
,meisaicd
,meisaikb
,hinmokucd
,hinmokumei
,kikaku
,kataban
,nisugatacd
,suryo
,tanicd
,teika
,tanka
,kazeikb
,shohizeiritsu
,shohizeigaku
,choseishohizeigaku
,kingaku
,noki
,biko
,shanaibiko
,sakuseiuserid
,sakuseinichiji
,koshinuserid
,koshinnichiji
) select
currval('sqns_mitsumoririrekino')
,mu.mitsumorino
,mu.gyono
,mu.oyameisaicd
,mu.meisaicd
,mu.meisaikb
,mu.hinmokucd
,mu.hinmokumei
,mu.kikaku
,mu.kataban
,mu.nisugatacd
,mu.suryo
,mu.tanicd
,mu.teika
,mu.tanka
,mu.kazeikb
,mu.shohizeiritsu
,mu.shohizeigaku
,mu.choseishohizeigaku
,mu.kingaku
,mu.noki
,mu.biko
,mu.shanaibiko
,mu.sakuseiuserid
,mu.sakuseinichiji
,mu.koshinuserid
,mu.koshinnichiji
from dat_m_mitsumori mu
where mu.mitsumorino = new.mitsumorino
;
INSERT 
INTO dat_rm_genkakeikaku( 
rirekino
, mitsumorino
, oyameisaicd
, genkakeikakugyono
, meisaikb
, shiiresakicd
, hinmokucd
, hinmokumei
, nisugatacd
, suryo
, tanicd
, genkatanka
, kakeritsu
, mitsumoritanka
, biko
, shanaibiko
, karikatakamokucd
, kashikatakamokucd
, sakuseiuserid
, sakuseinichiji
, koshinuserid
, koshinnichiji
) 
select
currval('sqns_mitsumoririrekino')
, gk.mitsumorino
, gk.oyameisaicd
, gk.genkakeikakugyono
, gk.meisaikb
, gk.shiiresakicd
, gk.hinmokucd
, gk.hinmokumei
, gk.nisugatacd
, gk.suryo
, gk.tanicd
, gk.genkatanka
, gk.kakeritsu
, gk.mitsumoritanka
, gk.biko
, gk.shanaibiko
, gk.karikatakamokucd
, gk.kashikatakamokucd
, gk.sakuseiuserid
, gk.sakuseinichiji
, gk.koshinuserid
, gk.koshinnichiji
from dat_m_genkakeikaku gk
where gk.mitsumorino = new.mitsumorino
;
return new;
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_r_mitsumori()
  RETURNS trigger AS $$
begin
insert into dat_rd_mitsumori (
rirekino
,sakujofg
,mitsumorino
,mitsumoribi
,tokuisakicd
,tokuisakiatena
,projectcd
,biko
,shanaibiko
,tantoshaid
,bushocd
,tankarankcd
,shoninnichiji
,shoninshaid
,mitsumorikenmei
,yukokigen
,nohinbi
,nohinbasho
,shiharaijoken
,sakuseiuserid
,sakuseinichiji
,koshinuserid
,koshinnichiji
  ) values (
nextval('sqns_mitsumoririrekino')
,true
,old.mitsumorino
,old.mitsumoribi
,old.tokuisakicd
,old.tokuisakiatena
,old.projectcd
,old.biko
,old.shanaibiko
,old.tantoshaid
,old.bushocd
,old.tankarankcd
,old.shoninnichiji
,old.shoninshaid
,old.mitsumorikenmei
,old.yukokigen
,old.nohinbi
,old.nohinbasho
,old.shiharaijoken
,old.sakuseiuserid
,old.sakuseinichiji
,old.koshinuserid
,old.koshinnichiji
  );
insert into dat_rm_mitsumori (
rirekino
,mitsumorino
,gyono
,oyameisaicd
,meisaicd
,meisaikb
,hinmokucd
,hinmokumei
,kikaku
,kataban
,nisugatacd
,suryo
,tanicd
,teika
,tanka
,kazeikb
,shohizeiritsu
,shohizeigaku
,choseishohizeigaku
,kingaku
,noki
,biko
,shanaibiko
,sakuseiuserid
,sakuseinichiji
,koshinuserid
,koshinnichiji
) select
currval('sqns_mitsumoririrekino')
,mu.mitsumorino
,mu.gyono
,mu.oyameisaicd
,mu.meisaicd
,mu.meisaikb
,mu.hinmokucd
,mu.hinmokumei
,mu.kikaku
,mu.kataban
,mu.nisugatacd
,mu.suryo
,mu.tanicd
,mu.teika
,mu.tanka
,mu.kazeikb
,mu.shohizeiritsu
,mu.shohizeigaku
,mu.choseishohizeigaku
,mu.kingaku
,mu.noki
,mu.biko
,mu.shanaibiko
,mu.sakuseiuserid
,mu.sakuseinichiji
,mu.koshinuserid
,mu.koshinnichiji
from dat_m_mitsumori mu
where mu.mitsumorino = old.mitsumorino
;
INSERT 
INTO dat_rm_genkakeikaku( 
rirekino
, mitsumorino
, oyameisaicd
, genkakeikakugyono
, meisaikb
, shiiresakicd
, hinmokucd
, hinmokumei
, nisugatacd
, suryo
, tanicd
, genkatanka
, kakeritsu
, mitsumoritanka
, biko
, shanaibiko
, karikatakamokucd
, kashikatakamokucd
, sakuseiuserid
, sakuseinichiji
, koshinuserid
, koshinnichiji
) 
select
currval('sqns_mitsumoririrekino')
, gk.mitsumorino
, gk.oyameisaicd
, gk.genkakeikakugyono
, gk.meisaikb
, gk.shiiresakicd
, gk.hinmokucd
, gk.hinmokumei
, gk.nisugatacd
, gk.suryo
, gk.tanicd
, gk.genkatanka
, gk.kakeritsu
, gk.mitsumoritanka
, gk.biko
, gk.shanaibiko
, gk.karikatakamokucd
, gk.kashikatakamokucd
, gk.sakuseiuserid
, gk.sakuseinichiji
, gk.koshinuserid
, gk.koshinnichiji
from dat_m_genkakeikaku gk
where gk.mitsumorino = old.mitsumorino
;
return new;
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE function insertjuhatchu(psrclist text, psakuseiuserid text, phatchubi date)
returns numeric
AS $$
DECLARE
    vgyono numeric;
    vreccnt numeric;
    vrecret record;
    vdenpyobkey numeric; 

    vshiiresakicd text;
    vprojectcd text;
    vtantoshaid text;
    vbushocd text;
    vhatchushubetsucd text;
    vkessaikb text;

    vparamhatchu cursor for
        select
            tmp3.hatchuno
            , tmp3.gyono
            , tmp3.oyameisaicd
            , tmp3.meisaicd
            , tmp3.hinmokucd
            , hin.hinmokumei
            , aitesakihinban
            , hin.kikaku
            , hin.kataban
            , tmp3.nisugatacd
            , tmp3.suryo
            , tmp3.tanicd
            , tmp3.tanka
            , tmp3.kazeikb
            , tmp3.shohizeiritsu
            , tmp3.suryo * tmp3.tanka as kingaku
            , tmp3.noki
            , tmp3.nyukosokocd
            , tmp3.projectcd
            , tmp3.hatchushubetsucd
            , tmp3.shiiresakicd
            , shiire.shanaitantoshacd as tantoshaid
            , shiire.tantobushocd as bushocd
            , shiire.kessaikb
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as hatchuno
                    , cast(juchukey [2] as text) as projectcd
                    , cast(juchukey [3] as text) as shiiresakicd
                    , cast(juchukey [4] as text) as oyameisaicd
                    , cast(juchukey [5] as text) as meisaicd
                    , cast(juchukey [6] as numeric) as gyono
                    , cast(juchukey [7] as text) as hinmokucd
                    , cast(juchukey [8] as numeric) as suryo
                    , cast(juchukey [9] as text) as tanicd
                    , cast(juchukey [10] as text) as nyukosokocd
                    , cast(juchukey [11] as text) as aitesakihinban
                    , cast(juchukey [12] as text) as nisugatacd
                    , cast(juchukey [13] as numeric) as tanka
                    , cast(juchukey [14] as date) as noki
                    , cast(juchukey [15] as text) as kazeikb
                    , cast(juchukey [16] as numeric) as shohizeiritsu 
                    , cast(juchukey [17] as text) as hatchushubetsucd
                from
                    ( 
                        select
                            regexp_split_to_array(a, ',') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, ':') as a
                            ) tmp
                    ) tmp2
            ) tmp3 
            inner join mst_hinmoku hin 
                on hin.hinmokucd = tmp3.hinmokucd
            inner join mst_shiiresaki shiire 
                on shiire.shiiresakicd = tmp3.shiiresakicd
            order by tmp3.hatchuno, tmp3.gyono; 


 BEGIN
    vreccnt := 0;
    vgyono := 1;
    select nextval('sqns_hatchuno') into vrecret;
    
    FOR rec IN vparamhatchu LOOP
        if vreccnt = 0 then
            vdenpyobkey := rec.hatchuno;
            vshiiresakicd := rec.shiiresakicd;
            vprojectcd := rec.projectcd;
            vtantoshaid := rec.tantoshaid;
            vbushocd := rec.bushocd;
            vhatchushubetsucd := rec.hatchushubetsucd;
            vkessaikb := rec.kessaikb;
        end if;

        if  vdenpyobkey <> rec.hatchuno then
            
            -- 伝票挿入
            INSERT 
            INTO dat_d_hatchu( 
                hatchuno
                , hatchubi
                , shiiresakicd
                , projectcd
                , biko
                , shanaibiko
                , tantoshaid
                , bushocd
                , hatchushubetsukb
                , shoninnichiji
                , shoninshaid
                , maebaraishiharaibi
                , maebaraikingaku
                , kessaikb
                , denpyosakuseikb
                , sakuseiuserid
                , sakuseinichiji
                , koshinuserid
                , koshinnichiji
            ) values (
                currval('sqns_hatchuno')
                , phatchubi
                , vshiiresakicd
                , vprojectcd
                , null
                , null
                , vtantoshaid
                , vbushocd
                , vhatchushubetsucd
                , null
                , null
                , '1753/1/1'::date
                , 0
                , vkessaikb
                , '2' 
                , psakuseiuserid
                , clock_timestamp()
                , psakuseiuserid
                , clock_timestamp()
            );
            
            vshiiresakicd := rec.shiiresakicd;
            vprojectcd := rec.projectcd;
            vtantoshaid := rec.tantoshaid;
            vbushocd := rec.bushocd;
            vhatchushubetsucd := rec.hatchushubetsucd;
            vkessaikb := rec.kessaikb;

            vgyono := 1;
            select nextval('sqns_hatchuno') into vrecret;
        end if;

        -- 明細挿入
        INSERT 
        INTO dat_m_hatchu( 
            hatchuno
            , gyono
            , oyameisaicd
            , meisaicd
            , meisaikb
            , hinmokucd
            , hinmokumei
            , aitesakihinban
            , kikaku
            , kataban
            , nisugatacd
            , suryo
            , tanicd
            , tanka
            , kazeikb
            , shohizeiritsu
            , kingaku
            , noki
            , biko
            , shanaibiko
            , nyukosokocd
            , kakuteihassobi
            , kakuteichakubi
            , sakuseiuserid
            , sakuseinichiji
            , koshinuserid
            , koshinnichiji
        ) values (
            currval('sqns_hatchuno')
            , vgyono
            , rec.oyameisaicd
            , 'HA' || nextval('sqns_hatchumeisaino')
            , '1'
            , rec.hinmokucd
            , rec.hinmokumei
            , rec.aitesakihinban
            , rec.kikaku
            , rec.kataban
            , rec.nisugatacd
            , rec.suryo
            , rec.tanicd
            , rec.tanka
            , rec.kazeikb
            , rec.shohizeiritsu
            , rec.kingaku
            , rec.noki
            , null
            , null
            , rec.nyukosokocd
            , null
            , null
            , psakuseiuserid
            , clock_timestamp()
            , psakuseiuserid
            , clock_timestamp() 
        );
        
        vgyono := vgyono + 1;
        vreccnt := vreccnt + 1;
     END LOOP;

    -- 伝票挿入
    INSERT 
    INTO dat_d_hatchu( 
        hatchuno
        , hatchubi
        , shiiresakicd
        , projectcd
        , biko
        , shanaibiko
        , tantoshaid
        , bushocd
        , hatchushubetsukb
        , shoninnichiji
        , shoninshaid
        , maebaraishiharaibi
        , maebaraikingaku
        , kessaikb
        , denpyosakuseikb
        , sakuseiuserid
        , sakuseinichiji
        , koshinuserid
        , koshinnichiji
    ) values (
        currval('sqns_hatchuno')
        , phatchubi
        , vshiiresakicd
        , vprojectcd
        , null
        , null
        , vtantoshaid
        , vbushocd
        , vhatchushubetsucd
        , null
        , null
        , '1753/1/1'::date
        , 0
        , vkessaikb
        , '2' 
        , psakuseiuserid
        , clock_timestamp()
        , psakuseiuserid
        , clock_timestamp()
    );
       

   return vreccnt;
END;
$$
LANGUAGE plpgsql;



insert into mst_kbn(kbtype,kbcd,kbmei,hyojijun,hyojimei,biko,haishifg,sakuseiuserid,sakuseinichiji,koshinuserid,koshinnichiji) values 
('HATCHUSHUBETSUKB','4','原価計画',4,'原価計画','原価計画',0,'admin',clock_timestamp(),'admin',clock_timestamp())
,('HATCHUJOTAI','8','原価計画',8,'原価計画','原価計画',0,'admin',clock_timestamp(),'admin',clock_timestamp());

--前受入金日　未指定の場合最小日とする
update dat_d_juchu set maeukenyukinbi = '1753/1/1' where (maeukenyukinbi <= '1753/1/1') or (maeukenyukinbi >= '2998/12/31');
update dat_d_hatchu set maebaraishiharaibi = '1753/1/1' where (maebaraishiharaibi <= '1753/1/1') or (maebaraishiharaibi >= '2998/12/31');


insert into public.mst_menu(menucd,oyamenucd,menumei,hyojikb,kinocd,hyojijun,gaiyo,sakuseiuserid,sakuseinichiji,koshinuserid,koshinnichiji,kengengrp,haishifg) values 
    ('1107','0030','原価計画発注一括作成','2','C000000008',308,'原価計画から発注を一括作成します','admin',clock_timestamp(),'admin',clock_timestamp(),'1',0);

