CREATE OR REPLACE function getzaikomotocho(kijunbi date, phinmokucd text) returns table ( 
    denpyoshurui text
    , denpyono numeric
    , meisaicd text
    , oyameisaicd text
    , denpyobi date
    , sokocd text
    , hinmokucd text
    , lotno text
    , locationcd text
    , suryo numeric
    , tanicd text
    , zaikotanka numeric
    , zaikokingaku numeric
    , sumsuryo numeric
) AS $$ with tana as ( 
    select
        '1棚卸' ::text as denpyoshurui
        , dtana.tanaoroshino as denpyono
        , ' '::text as meisaicd
        , ' '::text as oyameisaicd
        , dtana.tanaoroshibi as denpyobi
        , dtana.sokocd
        , mtana.hinmokucd
        , mtana.lotno
        , mtana.locationcd
        , mtana.jittanasuryo as suryo
        , mtana.tanicd
        , mtana.zaikotanka
        , mtana.zaikokingaku 
    from
        ( 
            select
                t.* 
            from
                dat_d_tanaoroshi t 
                inner join getmaxtanaoroshibi(kijunbi) m 
                    on m.sokocd = t.sokocd 
                    and m.tanaoroshibi = t.tanaoroshibi
        ) dtana 
        inner join dat_m_tanaoroshi mtana 
            on mtana.tanaoroshino = dtana.tanaoroshino 
    where
        mtana.hinmokucd = phinmokucd
) 
, ukenyuko as ( 
    select
        '2入庫' ::text as denpyoshurui
        , duke.ukeharaino as denpyono
        , muke.meisaicd
        , muke.oyameisaicd
        , duke.ukeharaibi as denpyobi
        , muke.nyukosokocd as sokocd
        , muke.hinmokucd
        , muke.lotno
        , muke.nyukolocationcd as locationcd
        , muke.suryo
        , muke.tanicd
        , muke.zaikotanka
        , muke.zaikokingaku 
    from
        dat_d_ukeharai duke 
        inner join dat_m_ukeharai muke 
            on muke.ukeharaino = duke.ukeharaino 
            and muke.meisaikb = '1' 
            and muke.nyukosokocd <> ' ' 
        left join getmaxtanaoroshibi(kijunbi) m 
            on m.sokocd = muke.nyukosokocd 
    where
        muke.hinmokucd = phinmokucd 
        and coalesce(m.sokocd, muke.nyukosokocd) = muke.nyukosokocd 
        and coalesce(m.tanaoroshibi, '1900/1/1' ::date) < duke.ukeharaibi
) 
, ukeshukko as ( 
    select
        '3出庫' ::text as denpyoshurui
        , duke.ukeharaino as denpyono
        , muke.meisaicd
        , muke.oyameisaicd
        , duke.ukeharaibi as denpyobi
        , muke.shukkosokocd as sokocd
        , muke.hinmokucd
        , muke.lotno
        , muke.shukkolocationcd as locationcd
        , muke.suryo * - 1 as suryo
        , muke.tanicd
        , muke.zaikotanka
        , muke.zaikokingaku * -1 as zaikokingaku
    from
        dat_d_ukeharai duke 
        inner join dat_m_ukeharai muke 
            on muke.ukeharaino = duke.ukeharaino 
            and muke.meisaikb = '1' 
            and muke.shukkosokocd <> ' ' 
        left join getmaxtanaoroshibi(kijunbi) m 
            on m.sokocd = muke.shukkosokocd 
    where
        muke.hinmokucd = phinmokucd 
        and coalesce(m.sokocd, muke.shukkosokocd) = muke.shukkosokocd 
        and coalesce(m.tanaoroshibi, '1900/1/1' ::date) < duke.ukeharaibi
) 
select
    zai.denpyoshurui
    , zai.denpyono
    , zai.meisaicd
    , zai.oyameisaicd
    , zai.denpyobi
    , zai.sokocd
    , zai.hinmokucd
    , zai.lotno
    , zai.locationcd
    , zai.suryo
    , zai.tanicd
    , zai.zaikotanka
    , zai.zaikokingaku
    , sum(zai.suryo) over ( 
        partition by
            zai.sokocd
            , zai.hinmokucd
            , zai.lotno 
            , zai.locationcd
        order by
            zai.denpyobi
            , zai.denpyoshurui
            , zai.denpyono
    ) as sumsuryo 
from
    ( 
        select
            * 
        from
            tana 
        union all 
        select
            * 
        from
            ukeshukko 
        union all 
        select
            * 
        from
            ukenyuko
    ) zai $$ LANGUAGE sql
