[selectRecords] select substr('&16',1,4)||'年'||substr('&16',5,2)||'月' as syori_ym_s ,m.tok_cd as tok_cd -- ●得意先コード --#19607 2021.06.23 MOD STT --,m.tok_nm as tok_nm -- ●得意先名 ,(case when(m.tok_rnm != '')then(m.tok_rnm)else(m.tok_nm)end) as tok_nm -- ●得意先名 --#19607 2021.06.23 MOD END ,t.zen_seikyu_zan as zen_seikyu_zan -- ●前回請求額 ,t.nyukin_gaku as nyukin_gaku -- ●今回入金額 ,t.kurikoshi_gaku as kurikoshi_gaku -- ●繰越額 ,t.zeikomi_gaku as zeikomi_gaku -- ●税込額 ,t.zeinuki_gaku as zeinuki_gaku -- 税抜額 ,t.zei_gaku as zei_gaku -- 税額 ,t.seikyu_zan as seikyu_zan -- ●今回請求額 from m_tokui m left join t_tok_getsu t on (m.tok_cd = t.tok_cd and t.syori_ym = substr('&16',1,6)) left join m_hanyo h99 on (h99.mst_kbn = '99' and h99.han_cd = 'nzer-r0160') where 1 = 1 and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) and (('' = '&4') or (m.cat_cd = '&4')) and ((m.shime_dd::varchar) = '&31' or ('' = '&31')) and (('' = '&42') or (m.eig_cd = '&42')) and ((COALESCE(h99.han_name,'') != '1') or ( (t.zen_seikyu_zan != 0) or (t.nyukin_gaku != 0) or (t.kurikoshi_gaku != 0) or (t.zeikomi_gaku != 0) or (t.zeinuki_gaku != 0) or (t.zei_gaku != 0) or (t.seikyu_zan != 0) )) --TODO --order by lpad(t.tok_cd,20,'0') order by t.tok_cd [_selectRecords] --&1:画面の年月 --&2:画面の締日 --&3:0:締日全て 1:締切のみ 2:都度のみ --&4:0:全て 1:請求残ありのみ select f2.* from (select 1 as type --締請求タイプ ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,to_date(t.seikyu_ymd,'yyyymmdd') as seikyu_ymd -- ●請求年月日 ,t.tok_cd as tok_cd -- ●得意先コード ,null::date as syori_ymd -- ●処理日 ,m.tok_nm as tok_nm -- ●得意先名 ,'' as den_no -- ●伝票 ,to_date(t.zen_seikyu_ymd,'yyyymmdd') as zen_seikyu_ymd -- ●前回請求年月日 ,t.zen_seikyu_zan as zen_seikyu_zan -- ●前回請求額 ,t.nyukin_gaku as nyukin_gaku -- ●今回入金額 ,t.kurikoshi_gaku as kurikoshi_gaku -- ●繰越額 ,t.zeikomi_gaku as zeikomi_gaku -- ●税込額 ,t.zeinuki_gaku as zeinuki_gaku -- 税抜額 ,t.zei_gaku as zei_gaku -- 税額 ,t.seikyu_zan as seikyu_zan -- ●今回請求額 ,v.nyukin_gaku as kaisyu_gaku -- ●回収額 ,coalesce(t.seikyu_zan,0) - coalesce(v.nyukin_gaku,0) as kaisyu_zan -- ●回収残額 ,mh.han_name as han_name -- ●サイト ,m.pay_d as pay_d -- ●サイト日 from t_urikake t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_kaisyu v on v.tok_cd = t.tok_cd and v.seikyu_ymd = t.seikyu_ymd -- left join m_tanto tn on (tn.tan_cd = t.tan_cd) where ( t.shime_dd::varchar = '&31' or '' = '&31' ) --&2:画面の締日 and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) -- and (('' = '&2') or (tn.bu_cd = '&2')) -- and (('' = '&24') or (t.tan_cd >= '&24')) -- and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ union all select 2 as type --締小計 ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- ●請求年月日 ,'' as tok_cd -- ●得意先コード ,null::date as syori_ymd -- ●処理日 ,'' as tok_nm -- ●得意先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- ●前回請求年月日 ,sum(t.zen_seikyu_zan) as zen_seikyu_zan -- ●前回請求額 ,sum(t.nyukin_gaku) as nyukin_gaku -- ●今回入金額 ,sum(t.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(t.zeikomi_gaku) as zeikomi_gaku -- ●税込額 ,sum(t.zeinuki_gaku) as zeinuki_gaku -- 税抜額 ,sum(t.zei_gaku) as zei_gaku -- 税額 ,sum(t.seikyu_zan) as seikyu_zan -- ●今回請求額 ,sum(v.nyukin_gaku) as kaisyu_gaku -- ●回収額 ,sum(coalesce(t.seikyu_zan,0) - coalesce(v.nyukin_gaku,0)) as kaisyu_zan -- ●回収残額 ,'' as han_name -- ●サイト ,null as pay_d -- ●サイト日 from t_urikake t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_kaisyu v on v.tok_cd = t.tok_cd and v.seikyu_ymd = t.seikyu_ymd -- left join m_tanto tn on (tn.tan_cd = t.tan_cd) where ( t.shime_dd::varchar = '&31' or '' = '&31' ) --&2:画面の締日 and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) -- and (('' = '&2') or (tn.bu_cd = '&2')) -- and (('' = '&24') or (t.tan_cd >= '&24')) -- and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <>0 ) --&4:0:全て 1:請求残ありのみ group by substring(t.seikyu_ymd,1,6) union all select 3 as type --都度請求 ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- 請求年月日 ,t.tok_cd as tok_cd -- 得意先コード ,to_date(t.syori_ymd,'yyyymmdd') as syori_ymd -- 処理日 ,m.tok_nm as tok_nm -- 得意先名 ,t.den_no::varchar as den_no -- 伝票 ,null::date as zen_seikyu_ymd -- 前回請求年月日 ,null as zen_seikyu_zan -- 前回請求額 ,null as nyukin_gaku -- 今回入金額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 税込額 ,null as zeinuki_gaku -- 税抜額 ,null as zei_gaku -- 税額 ,t.zeikomi_gaku::integer as seikyu_zan -- 今回請求額 ,k.nyukin_gaku::integer as kaisyu_gaku -- 回収額 ,(coalesce(t.zeikomi_gaku,0)::integer) - (coalesce(k.nyukin_gaku,0)::integer) as kaisyu_zan -- 回収残額 ,null as han_name -- サイト ,null as pay_d -- サイト日 from t_uri t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select uri_no ,sum(nyukin_gaku) as nyukin_gaku from t_nyu_keshi group by uri_no ) k on k.uri_no = t.den_no left join m_tanto tn on (tn.tan_cd = t.tan_cd) where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ union all select 4 as type --都度小計 ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- 請求年月日 ,'' as tok_cd -- 得意先コード ,null::date as syori_ymd -- 処理日 ,'' as tok_nm -- 得意先名 ,'' as den_no -- 伝票 ,null::date as zen_seikyu_ymd -- 前回請求年月日 ,null as zen_seikyu_zan -- 前回請求額 ,null as nyukin_gaku -- 今回入金額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 税込額 ,null as zeinuki_gaku -- 税抜額 ,null as zei_gaku -- 税額 ,sum(t.zeikomi_gaku) as seikyu_zan -- 今回請求額 ,sum(k.nyukin_gaku) as kaisyu_gaku -- 回収額 ,sum((coalesce(t.zeikomi_gaku,0)) - (coalesce(k.nyukin_gaku,0))) as kaisyu_zan -- 回収残額 ,null as han_name -- サイト ,null as pay_d -- サイト日 from t_uri t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select uri_no ,sum(nyukin_gaku) as nyukin_gaku from t_nyu_keshi group by uri_no ) k on k.uri_no = t.den_no left join m_tanto tn on (tn.tan_cd = t.tan_cd) where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ group by substring(t.syori_ymd,1,6) union all ----------------------- select 5 as type --合計 ,f.orderkey as orderkey ,f.orderkey2 as orderkey2 ,null::date as seikyu_ymd -- ●請求年月日 ,'' as tok_cd -- ●得意先コード ,null::date as syori_ymd -- ●処理日 ,'' as tok_nm -- ●得意先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- ●前回請求年月日 ,sum(f.zen_seikyu_zan) as zen_seikyu_zan -- ●前回請求額 ,sum(f.nyukin_gaku) as nyukin_gaku -- ●今回入金額 ,sum(f.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(f.zeikomi_gaku) as zeikomi_gaku -- ●税込額 ,sum(f.zeinuki_gaku) as zeinuki_gaku -- 税抜額 ,sum(f.zei_gaku) as zei_gaku -- 税額 ,sum(f.seikyu_zan_now) as seikyu_zan_now -- ●今回請求額 ,sum(f.kaisyu_gaku) as kaisyu_gaku -- ●回収額 ,sum(f.kaisyu_zan) as kaisyu_zan -- ●回収残額 ,'' as han_name -- ●サイト ,null as pay_d -- ●サイト日 from (select 5 as type ,substring(t.seikyu_ymd,1,6) as orderkey ,to_date(substring(t.seikyu_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- ●請求年月日 ,'' as tok_cd -- ●得意先コード ,null::date as syori_ymd -- ●処理日 ,'' as tok_nm -- ●得意先名 ,'' as den_no -- ●伝票 ,null::date as zen_seikyu_ymd -- ●前回請求年月日 ,sum(t.zen_seikyu_zan) as zen_seikyu_zan -- ●前回請求額 ,sum(t.nyukin_gaku) as nyukin_gaku -- ●今回入金額 ,sum(t.kurikoshi_gaku) as kurikoshi_gaku -- ●繰越額 ,sum(t.zeikomi_gaku) as zeikomi_gaku -- ●税込額 ,sum(t.zeinuki_gaku) as zeinuki_gaku -- 税抜額 ,sum(t.zei_gaku) as zei_gaku -- 税額 ,sum(t.seikyu_zan) as seikyu_zan_now -- ●今回請求額 ,sum(v.nyukin_gaku) as kaisyu_gaku -- ●回収額 ,sum(coalesce(t.seikyu_zan,0) - coalesce(v.nyukin_gaku,0)) as kaisyu_zan -- ●回収残額 ,'' as han_name -- ●サイト ,null as pay_d -- ●サイト日 from t_urikake t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join v_kaisyu v on v.tok_cd = t.tok_cd and v.seikyu_ymd = t.seikyu_ymd -- left join m_tanto tn on (tn.tan_cd = t.tan_cd) where ( t.shime_dd::varchar = '&31' or '' = '&31' ) --&2:画面の締日 and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) -- and (('' = '&2') or (tn.bu_cd = '&2')) -- and (('' = '&24') or (t.tan_cd >= '&24')) -- and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ group by substring(t.seikyu_ymd,1,6) union all select 4 as type ,substring(t.syori_ymd,1,6) as orderkey ,to_date(substring(t.syori_ymd,1,6)||'01','yyyymmdd') as orderkey2 ,null::date as seikyu_ymd -- 請求年月日 ,'' as tok_cd -- 得意先コード ,null::date as syori_ymd -- 処理日 ,'' as tok_nm -- 得意先名 ,'' as den_no -- 伝票 ,null::date as zen_seikyu_ymd -- 前回請求年月日 ,null as zen_seikyu_zan -- 前回請求額 ,null as nyukin_gaku -- 今回入金額 ,null as kurikoshi_gaku -- 繰越額 ,null as zeikomi_gaku -- 税込額 ,null as zeinuki_gaku -- 税抜額 ,null as zei_gaku -- 税額 ,sum(t.zeikomi_gaku) as seikyu_zan -- 今回請求額 ,sum(k.nyukin_gaku) as kaisyu_gaku -- 回収額 ,sum((coalesce(t.zeikomi_gaku,0)) - (coalesce(k.nyukin_gaku,0))) as kaisyu_zan_now -- 回収残額 ,null as han_name -- サイト ,null as pay_d -- サイト日 from t_uri t left join m_tokui m on m.tok_cd = t.tok_cd left join m_hanyo mh on mh.mst_kbn = '10' and mh.han_cd::numeric = m.pay_m left join (select uri_no ,sum(nyukin_gaku) as nyukin_gaku from t_nyu_keshi group by uri_no ) k on k.uri_no = t.den_no left join m_tanto tn on (tn.tan_cd = t.tan_cd) where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' and (('' = '&16') or (t.seikyu_ymd >= '&16')) and (('' = '&17') or (t.seikyu_ymd <= '&17')) and (('' = '&2') or (tn.bu_cd = '&2')) and (('' = '&24') or (t.tan_cd >= '&24')) and (('' = '&25') or (t.tan_cd <= '&25')) and (('' = '&4') or (m.cat_cd = '&4')) and (('' = '&20') or (m.tok_cd >= '&20')) and (('' = '&21') or (m.tok_cd <= '&21')) -- and ( '0' = '&3' or '2' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ -- and ( '0' = '&4' or (coalesce(t.zeikomi_gaku,0)::integer) <> 0) --&4:0:全て 1:請求残ありのみ group by substring(t.syori_ymd,1,6) ) f group by f.orderkey,f.orderkey2 ----------------------- ) f2 --where orderkey = substring('&16',1,6) -- &1 order by f2.type,f2.seikyu_ymd,f2.tok_cd,f2.syori_ymd