CREATE OR REPLACE function insertukeharai(psrclist text, psakuseiuserid text)
returns numeric
AS $$
DECLARE
    vreccnt numeric;
    vrecret record;
    
    vparamukeharai cursor for
        select distinct
            tmp3.denpyosaibanno
            , tmp3.uriageno
            , tmp3.shiireno
            , tmp3.ukeharaibi
            , tmp3.projectcd
            , null as biko
            , null as shanaibiko
            , coalesce(du.tantoshaid, ds.tantoshaid) as tantoshaid
            , coalesce(du.bushocd, ds.bushocd) as bushocd
            , null ::timestamp as shoninnichiji
            , null ::text as shoninshaid
            , '2' ::text as denpyosakuseikb 
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as denpyosaibanno
                    , cast(juchukey [2] as date) as ukeharaibi
                    , cast(juchukey [3] as numeric) as shiireno
                    , cast(juchukey [4] as numeric) as uriageno
                    , cast(juchukey [5] as text) as projectcd
                    , cast(juchukey [6] as text) as oyameisaicd
                    , cast(juchukey [7] as text) as meisaicd
                    , cast(juchukey [8] as numeric) as gyono
                    , cast(juchukey [9] as text) as hinmokucd
                    , cast(juchukey [10] as text) as lotno
                    , cast(juchukey [11] as text) as locationcd
                    , cast(juchukey [12] as numeric) as suryo
                    , cast(juchukey [13] as text) as tanicd
                    , cast(juchukey [14] as text) as nyukosokocd
                    , cast(juchukey [15] as text) as shukkosokocd 
                from
                    ( 
                        select
                            regexp_split_to_array(a, ',') as juchukey 
                        from
                            ( 
                                select
                                    regexp_split_to_table(psrclist, ':') as a
                            ) tmp
                    ) tmp2
            ) tmp3 
            left join dat_d_uriage du 
                on du.uriageno = tmp3.uriageno 
            left join dat_d_shiire ds 
                on ds.shiireno = tmp3.shiireno 
        order by
            tmp3.denpyosaibanno;
    vparamukeharai_meisai cursor (pdenpyosaibanno numeric) for
        select
            tmp3.denpyosaibanno
            , tmp3.gyono
            , tmp3.oyameisaicd
            , tmp3.meisaicd
            , '1' ::text meisaikb
            , tmp3.hinmokucd
            , hin.hinmokumei
            , hin.kikaku
            , hin.kataban
            , case 
                when coalesce(tmp3.lotno, '') = '' 
                    then ' ' 
                else tmp3.lotno 
                end as lotno
            , tmp3.seizobi
            , tmp3.shohikigen
            , tmp3.suryo
            , tmp3.tanicd
            , 0 as zaikotanka
            , 0 as zaikokingaku
            , null as biko
            , null as shanaibiko
            , tmp3.shukkosokocd
            , case 
                when tmp3.shukkosokocd = ' ' 
                    then ' ' 
                else tmp3.locationcd 
                end as shukkolocationcd
            , tmp3.nyukosokocd
            , case 
                when tmp3.nyukosokocd = ' ' 
                    then ' ' 
                else tmp3.locationcd 
                end as nyukolocationcd
            , null as ukeharaikarikatakamokucd
            , null as ukeharaikashikatakamokucd
        from
            ( 
                select
                    cast(juchukey [1] as numeric) as denpyosaibanno
                    , cast(juchukey [2] as date) as ukeharaibi
                    , cast(juchukey [3] as numeric) as shiireno
                    , cast(juchukey [4] as numeric) as uriageno
                    , cast(juchukey [5] as text) as projectcd
                    , cast(juchukey [6] as text) as oyameisaicd
                    , cast(juchukey [7] as text) as meisaicd
                    , cast(juchukey [8] as numeric) as gyono
                    , cast(juchukey [9] as text) as hinmokucd
                    , cast(juchukey [10] as text) as lotno
                    , cast(juchukey [11] as text) as locationcd
                    , cast(juchukey [12] as numeric) as suryo
                    , cast(juchukey [13] as text) as tanicd
                    , cast(juchukey [14] as text) as nyukosokocd
                    , cast(juchukey [15] as text) as shukkosokocd 
                    , case when juchukey [16] = '' then null else cast(juchukey [16] as date) end as seizobi
                    , case when juchukey [17] = '' then null else cast(juchukey [17] as date) end as shohikigen
                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
        where
            tmp3.denpyosaibanno = pdenpyosaibanno
        order by
            tmp3.gyono;
BEGIN
    vreccnt := 0;
    FOR recd IN vparamukeharai LOOP
        select nextval('sqns_ukeharaino') into vrecret;
        FOR recm IN vparamukeharai_meisai(recd.denpyosaibanno) LOOP
            -- 明細挿入
            INSERT 
            INTO dat_m_ukeharai( 
                ukeharaino
                , gyono
                , oyameisaicd
                , meisaicd
                , meisaikb
                , hinmokucd
                , hinmokumei
                , kikaku
                , kataban
                , lotno
                , seizobi
                , shohikigen
                , suryo
                , tanicd
                , zaikotanka
                , zaikokingaku
                , biko
                , shanaibiko
                , shukkosokocd
                , shukkolocationcd
                , nyukosokocd
                , nyukolocationcd
                , ukeharaikarikatakamokucd
                , ukeharaikashikatakamokucd
                , sakuseiuserid
                , sakuseinichiji
                , koshinuserid
                , koshinnichiji
            ) values (
                currval('sqns_ukeharaino')
                , recm.gyono
                , recm.oyameisaicd
                , 'UK' || nextval('sqns_ukeharaimeisaino')
                , recm.meisaikb
                , recm.hinmokucd
                , recm.hinmokumei
                , recm.kikaku
                , recm.kataban
                , recm.lotno
                , recm.seizobi
                , recm.shohikigen
                , recm.suryo
                , recm.tanicd
                , recm.zaikotanka
                , recm.zaikokingaku
                , recm.biko
                , recm.shanaibiko
                , recm.shukkosokocd
                , recm.shukkolocationcd
                , recm.nyukosokocd
                , recm.nyukolocationcd
                , recm.ukeharaikarikatakamokucd
                , recm.ukeharaikashikatakamokucd
                , psakuseiuserid
                , clock_timestamp()
                , psakuseiuserid
                , clock_timestamp() 
            );
        END LOOP;
        -- 伝票挿入
        INSERT 
        INTO dat_d_ukeharai( 
            ukeharaino
            , uriageno
            , shiireno
            , ukeharaibi
            , projectcd
            , biko
            , shanaibiko
            , tantoshaid
            , bushocd
            , shoninnichiji
            , shoninshaid
            , denpyosakuseikb
            , sakuseiuserid
            , sakuseinichiji
            , koshinuserid
            , koshinnichiji
        ) values (
            currval('sqns_ukeharaino')
            , recd.uriageno
            , recd.shiireno
            , recd.ukeharaibi
            , recd.projectcd
            , recd.biko
            , recd.shanaibiko
            , recd.tantoshaid
            , recd.bushocd
            , recd.shoninnichiji
            , recd.shoninshaid
            , recd.denpyosakuseikb 
            , psakuseiuserid
            , clock_timestamp()
            , psakuseiuserid
            , clock_timestamp()
        );
        vreccnt := vreccnt + 1;
     END LOOP;
       
   return vreccnt;
END;
$$
LANGUAGE plpgsql;
