CREATE OR REPLACE function insertshukka(psrclist text, psakuseiuserid text)
returns numeric
AS $$
DECLARE
    vgyono numeric;
    vreccnt numeric;
    vrecret record;
    vtorihikisakicd text;
    vhaisosakino numeric;
    vshukkosokocd text;
    vyoteihaisobi date;
    vrirekino bigint;
    vjuchuno numeric;
    vhaisogyoshacd text;
    vhaisobinkb1 text;
    vhaisobinkb2 text;
    vtraceno text;
    vhaisomemo1 text;
    vhaisomemo2 text;
    vhaisomemo3 text;
    vhaisomemo4 text;
    vhaisoshanaimemo text;
    paramshukka cursor for
        select
            tmp3.*
            , tok.torihikisakicd
            , rmj.meisaicd
            , rmj.suryo
            , rmj.yoteihaisobi
            , rmj.haisosakino
            , rmj.hinmokucd
            , rmj.hinmokumei
            , rmj.kikaku
            , rmj.kataban
            , rmj.lotno
            , rmj.tanicd
            , rmj.shukkosokocd
            , rmj.tanka
            , rmj.kingaku
            , rmj.kazeikb
            , rmj.shohizeiritsu
            , tmp3.tsuisekibango as traceno
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as juchuno
                    , cast(juchukey [2] as numeric) as rirekino
                    , cast(juchukey [3] as text) as juchumeisaicd
                    , cast(juchukey [4] as text) as haisogyoshacd
                    , cast(juchukey [5] as text) as haisobinkb1
                    , cast(juchukey [6] as text) as haisobinkb2
                    , cast(juchukey [7] as text) as tsuisekibango
                    , cast(juchukey [8] as text) as haisomemo1
                    , cast(juchukey [9] as text) as haisomemo2
                    , cast(juchukey [10] as text) as haisomemo3
                    , cast(juchukey [11] as text) as haisomemo4
                    , cast(juchukey [12] as text) as haisoshanaimemo
                    , cast(juchukey [13] as text) as meisaihaisomemo
                    , cast(juchukey [14] as text) as meisaishanaihaisomemo
                    , cast(juchukey [15] as numeric) as gyono 
                from
                    ( 
                        select
                            regexp_split_to_array(a, '_,') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, '_:') as a
                            ) tmp
                    ) tmp2
            ) tmp3 
            inner join dat_rm_juchu rmj 
                on rmj.juchuno = tmp3.juchuno 
                and rmj.rirekino = tmp3.rirekino 
                and rmj.meisaicd = tmp3.juchumeisaicd 
            inner join dat_rd_juchu rdj 
                on rdj.juchuno = tmp3.juchuno 
                and rdj.rirekino = tmp3.rirekino 
            inner join mst_tokuisaki tok 
                on tok.tokuisakicd = rdj.tokuisakicd
            order by 
                rmj.shukkosokocd
                ,rmj.yoteihaisobi
                ,tok.torihikisakicd
                ,rmj.haisosakino
                ,tmp3.juchuno
                ,tmp3.gyono;
 BEGIN
    vreccnt := 0;
    vgyono := 1;
    select nextval('sqns_shukkano') into vrecret;
    FOR rec IN paramshukka LOOP
        if vreccnt = 0 then
            vtorihikisakicd := rec.torihikisakicd;
            vhaisosakino := rec.haisosakino;
            vshukkosokocd := rec.shukkosokocd;
            vyoteihaisobi := rec.yoteihaisobi;
            vrirekino := rec.rirekino;
            vjuchuno := rec.juchuno;
            vhaisogyoshacd := rec.haisogyoshacd;
            vhaisobinkb1 := rec.haisobinkb1;
            vhaisobinkb2 := rec.haisobinkb2;
            vtraceno := rec.traceno;
            vhaisomemo1 := rec.haisomemo1;
            vhaisomemo2 := rec.haisomemo2;
            vhaisomemo3 := rec.haisomemo3;
            vhaisomemo4 := rec.haisomemo4;
            vhaisoshanaimemo := rec.haisoshanaimemo;
        end if;
        if  vshukkosokocd <> rec.shukkosokocd or
            vyoteihaisobi <> rec.yoteihaisobi or
            vtorihikisakicd <> rec.torihikisakicd or
            vhaisosakino <> rec.haisosakino or
            vjuchuno <> rec.juchuno then
            -- 伝票挿入
            INSERT 
            INTO dat_d_shukka( 
                shukkano
                , torihikisakicd
                , haisosakino
                , shukkosokocd
                , shukkabi
                , juchurirekino
                , juchuno
                , haisogyoshacd
                , haisobinkb1
                , haisobinkb2
                , traceno
                , haisomemo1
                , haisomemo2
                , haisomemo3
                , haisomemo4
                , shanaihaisomemo
                , shoninnichiji
                , shoninshaid
                , insatsukaisu
                , sakuseiuserid
                , sakuseinichiji
                , koshinuserid
                , koshinnichiji
            ) values (
                currval('sqns_shukkano')
                , vtorihikisakicd
                , vhaisosakino
                , vshukkosokocd
                , vyoteihaisobi
                , vrirekino
                , vjuchuno
                , vhaisogyoshacd
                , vhaisobinkb1
                , vhaisobinkb2
                , vtraceno
                , vhaisomemo1
                , vhaisomemo2
                , vhaisomemo3
                , vhaisomemo4
                , vhaisoshanaimemo
                , null
                , null
                , 0 
                , psakuseiuserid
                , clock_timestamp()
                , psakuseiuserid
                , clock_timestamp()
            );
            vtorihikisakicd := rec.torihikisakicd;
            vhaisosakino := rec.haisosakino;
            vshukkosokocd := rec.shukkosokocd;
            vyoteihaisobi := rec.yoteihaisobi;
            vrirekino := rec.rirekino;
            vjuchuno := rec.juchuno;
            vhaisogyoshacd := rec.haisogyoshacd;
            vhaisobinkb1 := rec.haisobinkb1;
            vhaisobinkb2 := rec.haisobinkb2;
            vtraceno := rec.traceno;
            vhaisomemo1 := rec.haisomemo1;
            vhaisomemo2 := rec.haisomemo2;
            vhaisomemo3 := rec.haisomemo3;
            vhaisomemo4 := rec.haisomemo4;
            vhaisoshanaimemo := rec.haisoshanaimemo;
            vgyono := 1;
            select nextval('sqns_shukkano') into vrecret;
        end if;
        -- 明細挿入
        INSERT 
        INTO dat_m_shukka( 
            shukkano
            , shukkagyono
            , meisaicd
            , hinmokucd
            , hinmokumei
            , kikaku
            , kataban
            , lotno
            , suryo
            , tanicd
            , tanka
            , kazeikb
            , shohizeiritsu
            , kingaku
            , haisomemo
            , shanaihaisomemo
            , sakuseiuserid
            , sakuseinichiji
            , koshinuserid
            , koshinnichiji
        ) values (
            currval('sqns_shukkano')
            , vgyono
            , rec.meisaicd
            , rec.hinmokucd
            , rec.hinmokumei
            , rec.kikaku
            , rec.kataban
            , rec.lotno
            , rec.suryo
            , rec.tanicd
            , rec.tanka
            , rec.kazeikb
            , rec.shohizeiritsu
            , rec.kingaku
            , rec.meisaihaisomemo
            , rec.meisaishanaihaisomemo
            , psakuseiuserid
            , clock_timestamp()
            , psakuseiuserid
            , clock_timestamp() 
        );
        vgyono := vgyono + 1;
        vreccnt := vreccnt + 1;
     END LOOP;
    -- 伝票挿入
    INSERT 
    INTO dat_d_shukka( 
        shukkano
        , torihikisakicd
        , haisosakino
        , shukkosokocd
        , shukkabi
        , juchurirekino
        , juchuno
        , haisogyoshacd
        , haisobinkb1
        , haisobinkb2
        , traceno
        , haisomemo1
        , haisomemo2
        , haisomemo3
        , haisomemo4
        , shanaihaisomemo
        , shoninnichiji
        , shoninshaid
        , insatsukaisu
        , sakuseiuserid
        , sakuseinichiji
        , koshinuserid
        , koshinnichiji
    ) values (
        currval('sqns_shukkano')
        , vtorihikisakicd
        , vhaisosakino
        , vshukkosokocd
        , vyoteihaisobi
        , vrirekino
        , vjuchuno
        , vhaisogyoshacd
        , vhaisobinkb1
        , vhaisobinkb2
        , vtraceno
        , vhaisomemo1
        , vhaisomemo2
        , vhaisomemo3
        , vhaisomemo4
        , vhaisoshanaimemo
        , null
        , null
        , 0 
        , psakuseiuserid
        , clock_timestamp()
        , psakuseiuserid
        , clock_timestamp()
   );
   return vreccnt;
END;
$$
LANGUAGE plpgsql;
