[selectRecords] --&1:画面の年月 --&2:画面の締日 --&3:0:締日全て 1:締切のみ 2:都度のみ --&4:0:全て 1:請求残ありのみ --&5:0:全て 1:オール0除く 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 where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ 2:オール0 and (('0' = '&5') or ((t.zen_seikyu_zan != 0) or (t.nyukin_gaku != 0) or (t.kurikoshi_gaku != 0) or (t.zeinuki_gaku != 0) or (t.seikyu_zan != 0) or (v.nyukin_gaku != 0))) union 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 where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <>0 ) --&4:0:全て 1:請求残ありのみ and (('0' = '&5') or ((t.zen_seikyu_zan != 0) or (t.nyukin_gaku != 0) or (t.kurikoshi_gaku != 0) or (t.zeinuki_gaku != 0) or (t.seikyu_zan != 0) or (v.nyukin_gaku != 0))) group by substring(t.seikyu_ymd,1,6) union 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 -- 処理日 ,t.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 where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' 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 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 where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' 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 ----------------------- 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 where ( t.shime_dd::varchar = '&2' or '' = '&2' ) --&2:画面の締日 and ( '0' = '&3' or '1' = '&3') --&3:0:締日全て 1:締切のみ 2:都度のみ and ( '0' = '&4' or coalesce(t.seikyu_zan,0) <> 0 ) --&4:0:全て 1:請求残ありのみ and (('0' = '&5') or ((t.zen_seikyu_zan != 0) or (t.nyukin_gaku != 0) or (t.kurikoshi_gaku != 0) or (t.zeinuki_gaku != 0) or (t.seikyu_zan != 0) or (v.nyukin_gaku != 0))) group by substring(t.seikyu_ymd,1,6) union 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 where m.tori_kbn <> '1' and t.jucyu_flg = '0' and t.del_flg = '0' 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 = '&1' -- &1 order by f2.type,f2.seikyu_ymd,f2.tok_cd,f2.syori_ymd