[SelectRecords]
select
  w1.sir_cd
, w1.row_no
, trunc(w1.row_no / 20) as row_page
, (trunc(w1.row_no / 20) + 1)||'ページ' as row_B
, w1.sir_nm
, w1.shime_dd
, w1.kbn_no
, w1.den_dt
, w1.from_to_ymd
, w1.den_no
, w1.hin_cd
, w1.hin_nm
, w1.suryo
, w1.tanka
, w1.kingaku
, w1.zei_gaku
, w1.zeikomi_gaku
, w1.syukin_gaku
, w1.kaikake_gaku
, w1.biko1
, w1.biko2
, w1.zei_calc_kbn
from (
select
  w.sir_cd
, row_number()over(order by w.sir_cd, w.syori_ymd, (case(w.motocyo_kind)when(5)then(4)when(3)then(2)else(w.motocyo_kind)end), w.motocyo_key1, w.motocyo_key2, w.motocyo_kind) as row_no
, mt.sir_nm
, coalesce('締日：' || mt.shime_dd || '日','未設定') as shime_dd
, w.motocyo_kind as kbn_no
, case to_char(to_date(w.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') 
	when '0001/01/01' then '' 
	else to_char((to_date(w.syori_ymd, 'YYYYMMDD')), 'yyyy/mm/dd')
	end as den_dt
, case to_char((to_date('&16', 'YYYYMMDD')), 'yyyy/mm/dd') 
	when '0001/01/01' then '未入力' 
	else to_char((to_date('&16', 'YYYYMMDD')), 'yyyy/mm/dd') 
  end || '〜' || 
  case to_char((to_date('&17', 'YYYYMMDD')), 'yyyy/mm/dd') 
	when '0001/01/01' then '未入力' 
	else to_char((to_date('&17', 'YYYYMMDD')), 'yyyy/mm/dd')
  end as from_to_ymd
, w.den_no as den_no
, w.hin_cd as hin_cd
, w.hin_nm as hin_nm
, w.suryo as suryo
, w.tanka as tanka
, w.zeinuki_gaku as kingaku
, w.zei_gaku as zei_gaku
, w.zeikomi_gaku as zeikomi_gaku
, w.syukin_gaku as syukin_gaku
, w.seikyu_zan as kaikake_gaku
, w.biko1 as biko1
, w.biko2 as biko2
, coalesce(mt.zei_calc_kbn,'0') as zei_calc_kbn
from w_sir_motocyo w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
where 1 = 1
and w.s_id = &32
) w1
where 1 = 1
order by sir_cd, row_no




[_SelectRecords]
select
  w.sir_cd
, row_number()over(order by w.sir_cd,w.syori_ymd,w.den_no,w.kbn_no) as row_no
, trunc((row_number()over(order by w.sir_cd,w.syori_ymd,w.den_no,w.kbn_no)-1)/20) as row_page
, (trunc((row_number()over(order by w.sir_cd,w.syori_ymd,w.den_no,w.kbn_no))/20)+1)||'ページ' as row_B
, mt.sir_nm
, coalesce('締日：' || mt.shime_dd || '日','未設定') as shime_dd
, w.kbn_no as kbn_no
, w.kbn as kbn
, case to_char(to_date(w.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') 
	when '0001/01/01' then '' 
	else to_char((to_date(w.syori_ymd, 'YYYYMMDD')), 'yyyy/mm/dd')
	end as den_dt
, case to_char((to_date('&16', 'YYYYMMDD')), 'yyyy/mm/dd') 
	when '0001/01/01' then '未入力' 
	else to_char((to_date('&16', 'YYYYMMDD')), 'yyyy/mm/dd') 
  end || '〜' || 
  case to_char((to_date('&17', 'YYYYMMDD')), 'yyyy/mm/dd') 
	when '0001/01/01' then '未入力' 
	else to_char((to_date('&17', 'YYYYMMDD')), 'yyyy/mm/dd')
  end as from_to_ymd
, w.den_no as den_no
, w.cd as hin_cd
, w.kbn_nm as hin_nm
, w.suryo as suryo
, w.tanka as tanka
, w.kingaku as kingaku
, w.zei_gaku as zei_gaku
, w.zeikomi_gaku as zeikomi_gaku
, w.syukin_gaku as syukin_gaku
, w.kaikake_gaku as kaikake_gaku
, w.biko1 as biko1
, coalesce(mt.zei_calc_kbn,'0') as zei_calc_kbn
from
(select 
	  tuk.sir_cd
	,  '0' as kbn_no
	, '繰越' as kbn
	, null as sort
	, tuk.seikyu_ymd as syori_ymd
	, tuk.seikyu_no as den_no
	, '' as cd
	, '【繰越金額】' as kbn_nm
	, null::numeric as suryo
	, null::numeric as tanka
	, null::numeric as kingaku
	, null::numeric as zei_gaku
	, null::numeric as zeikomi_gaku
	, null::numeric as syukin_gaku
 	, coalesce(tuk.seikyu_zan,0) as kaikake_gaku
	, null as biko1
from t_kaikake tuk
left join (
select
	tu.sir_cd
	, sum(tu.zeinuki_gaku) as zeinuki_gaku
	, sum(tu.zeikomi_gaku) as zeikomi_gaku
from t_sir tu
left join t_kaikake tuk on (tu.sir_cd = tuk.sir_cd
and tuk.del_flg = '0'
and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '&23' or ('&23' = ''))
and (tuk.sir_cd,tuk.seikyu_ymd) in (select uk.sir_cd,max(uk.seikyu_ymd) from t_kaikake uk where del_flg = '0' and (uk.sir_cd >= '&22' or ('&22' = '')) and (uk.sir_cd <= '&23' or ('&23' = '')) and (uk.seikyu_ymd <'&16') or ('&16' = '') group by uk.sir_cd)
)
where 
tu.del_flg = '0'
and coalesce(hacyu_flg, '0') = '0'
and coalesce(seikyu_flg, '0') = '1'
and tu.syori_ymd > tuk.seikyu_ymd
and tu.syori_ymd < '&16'
group by tu.sir_cd
order by 1,2
		) w on (w.sir_cd = tuk.sir_cd)
where 1=1
and tuk.del_flg = '0'
and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '&23' or ('&23' = ''))
and (tuk.sir_cd,tuk.seikyu_ymd) in (select uk.sir_cd,max(uk.seikyu_ymd) from t_kaikake uk where del_flg = '0' and (uk.sir_cd >= '&22' or ('&22' = '')) and (uk.sir_cd <= '&23' or ('&23' = '')) and (uk.seikyu_ymd <'&16') or ('&16' = '') group by uk.sir_cd)
and coalesce(tuk.seikyu_zan,0) != 0
union all(
select 
	  tuk.sir_cd
	,  '1' as kbn_no
	, '' as kbn
	, null as sort
	, to_char(to_date('&16','yyyymmdd') - CAST('1 day' AS INTERVAL),'yyyymmdd') as syori_ymd
	, tuk.seikyu_no as den_no
	, '' as cd
	, to_char(to_date(tuk.seikyu_ymd,'yyyymmdd') + CAST('1 day' AS INTERVAL),'yyyy年mm月dd日') || '〜' || to_char(to_date('&16','yyyymmdd') - CAST('1 day' AS INTERVAL),'yyyy年mm月dd日') as kbn_nm
	, null::numeric as suryo
	, null::numeric as tanka
	, coalesce(w.zeinuki_gaku,0) as kingaku
	, coalesce(w.zei_gaku,0) as zei_gaku
	, coalesce(w.zeikomi_gaku,0) as zeikomi_gaku
	, coalesce(w2.syukin_gaku,0) as syukin_gaku
	, case (coalesce(mt.zei_calc_kbn,'1'))
		when '1' then  coalesce(tuk.seikyu_zan,0) + coalesce(w.zeinuki_gaku,0) - coalesce(w2.syukin_gaku,0)
		else  coalesce(tuk.seikyu_zan,0) + coalesce(w.zeikomi_gaku,0) - coalesce(w2.syukin_gaku,0)
		end as kaikake_gaku
	--, coalesce(tuk.seikyu_zan,0) + coalesce(w.zeikomi_gaku,0) - coalesce(w2.syukin_gaku,0) as kaikake_gaku
	, null as biko1
from t_kaikake tuk
left join (
	select
		tu.sir_cd
		, sum(tu.zeinuki_gaku) as zeinuki_gaku
		, sum(tu.zeikomi_gaku) as zeikomi_gaku
		, sum(tu.zei_gaku) as zei_gaku
	from t_sir tu
	left join t_kaikake tuk on (tu.sir_cd = tuk.sir_cd
	and tuk.del_flg = '0'
	and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '&23' or ('&23' = ''))
	and (tuk.sir_cd,tuk.seikyu_ymd) in (select uk.sir_cd,max(uk.seikyu_ymd) from t_kaikake uk where del_flg = '0' and (uk.sir_cd >= '&22' or ('&22' = '')) and (uk.sir_cd <= '&23' or ('&23' = '')) and (uk.seikyu_ymd <'&16') or ('&16' = '') group by uk.sir_cd)
	)
	where 
	tu.del_flg = '0'
	and coalesce(hacyu_flg, '0') = '0'
	--and coalesce(seikyu_flg, '0') = '1'
	and tu.syori_ymd > tuk.seikyu_ymd
	and tu.syori_ymd < '&16'
	group by tu.sir_cd
	order by tu.sir_cd
		) w on (w.sir_cd = tuk.sir_cd)
left join (
	select
		tn.sir_cd
		, sum(tn.total_gaku) as syukin_gaku
	from t_syukin tn
	left join t_kaikake tuk on (tn.sir_cd = tuk.sir_cd
	and tuk.del_flg = '0'
	and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '' or ('' = ''))
	and (tuk.sir_cd,tuk.seikyu_ymd) in (select uk.sir_cd,max(uk.seikyu_ymd) from t_kaikake uk where del_flg = '0' and (uk.sir_cd >= '&22' or ('&22' = '')) and (uk.sir_cd <= '&23' or ('&23' = '')) and (uk.seikyu_ymd <'&16') or ('&16' = '') group by uk.sir_cd)
	)
	where 
	tn.del_flg = '0'
	and tn.syukin_ymd > tuk.seikyu_ymd
	and tn.syukin_ymd < '&16'
	group by tn.sir_cd
	order by tn.sir_cd
		) w2 on (w2.sir_cd = tuk.sir_cd)
left join m_sir mt on (mt.sir_cd = tuk.sir_cd)
where 1=1
and tuk.del_flg = '0'
and (coalesce(w.zeinuki_gaku,0) != 0 or coalesce(w2.syukin_gaku,0) != 0)
and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '&23' or ('&23' = ''))
and (tuk.sir_cd,tuk.seikyu_ymd) in (select uk.sir_cd,max(uk.seikyu_ymd) from t_kaikake uk where del_flg = '0' and (uk.sir_cd >= '&22' or ('&22' = '')) and (uk.sir_cd <= '&23' or ('&23' = '')) and (uk.seikyu_ymd <'&16') or ('&16' = '') group by uk.sir_cd)
order by sir_cd,kbn_no
)
union all((
select 
	  sir_cd
	, '2' as kbn_no
	, case (coalesce(tu.hacyu_flg,'0')) 
		when '1' then '発注'
		else '仕入'
	  end as kbn
	, lpad ((row_number()over(order by tum.den_no,tum.row_no)::text),3,'0') as sort
	, tu.syori_ymd
	, tu.den_no
	, tum.hin_cd as cd
	, tum.hin_nm
--#18334 2021.08.08 MOD STT
--	, tum.suryo
--	, tum.tanka
--	, tum.kingaku as kingaku
	, (case when(h90a.koumoku1 = tum.hin_cd)then(null)else(tum.suryo)end) as suryo
	, (case when(h90a.koumoku1 = tum.hin_cd)then(null)else(tum.tanka)end) as tanka
	, (case when(h90a.koumoku1 = tum.hin_cd)then(null)else(tum.kingaku)end) as kingaku
--#18334 2021.08.08 MOD END
	, null::numeric as zei_gaku
	, null::numeric as zeikomi_gaku
	, null::numeric as syukin_gaku
	, null::numeric as kaikake_gaku
	, trim(both chr(10) from substring(tum.meisai_biko,-1,(position(chr(10) in (replace(tum.meisai_biko,'','')))))) as biko1
from t_sir_m tum
left join t_sir tu on (tu.den_no = tum.den_no)
left join m_hanyo han on (han.mst_kbn = '99' and han.han_cd = 'seisan-grp')
--#18334 2021.08.08 MOD STT
left join m_hanyo h90a on (h90a.mst_kbn = '90' and h90a.han_cd = 'hin-title')
--#18334 2021.08.08 MOD END
where 1=1
and tu.del_flg = '0'
and coalesce(tu.hacyu_flg,'0') = '0'
--and coalesce(tu.seikyu_flg,'0') = '1'
and (tu.sir_cd >= '&22' or ('&22' = '')) and (tu.sir_cd <= '&23' or ('&23' = ''))
and (tu.syori_ymd >= '&16' or ('&16' = '')) and (tu.syori_ymd <= '&17' or ('&17' = ''))
and coalesce(han.han_name,'0') = '0'
order by tum.den_no,tum.row_no
)
union all
select 
	  tu.sir_cd
	, '3' as kbn_no
	, case (coalesce(tu.hacyu_flg,'0')) 
		when '1' then '発注'
		else '仕入'
	  end as kbn
	, null as sort
	, tu.syori_ymd
	, tu.den_no
	, '' as cd
	, '【伝票金額】'
	, null::numeric as suryo
	, null::numeric as tanka
	, tu.zeinuki_gaku as kingaku
	, tu.zei_gaku as zei_gaku
	, tu.zeikomi_gaku as zeikomi_gaku
	, null::numeric as syukin_gaku
	, tu.zei_gaku as kaikake_gaku
	, trim(both chr(10) from substring(biko1,-1,(position(chr(10) in (replace(biko1,'','')))))) as biko1
from t_sir tu
left join m_hanyo han on (han.mst_kbn = '99' and han.han_cd = 'seisan-grp')
where 1=1
and tu.del_flg = '0'
and coalesce(tu.hacyu_flg,'0') = '0'
--and coalesce(tu.seikyu_flg,'0') = '1'
and (tu.sir_cd >= '&22' or ('&22' = '')) and (tu.sir_cd <= '&23' or ('&23' = ''))
and (tu.syori_ymd >= '&16' or ('&16' = '')) and (tu.syori_ymd <= '&17' or ('&17' = ''))
and coalesce(han.han_name,'0') = '0'
)
union all
select
	  sir_cd
	, '4' as kbn_no
	, '出金' as kbn 
	, null as sort
	, tn.syukin_ymd
	, tn.syukin_no
	, tm.syukin_kbn as cd
	,'【'  || coalesce(han.han_name,'') ||'】' as  kbn_name
	, null::numeric as suryo
	, null::numeric as tanka
	, null::numeric as kingaku
	, null::numeric as zei_gaku
	, null::numeric as zeikomi_gaku
	, tm.syukin_gaku as syukin_gaku
	, null as kaikake_gaku
	, tn.biko as biko1
from t_syukin tn
left join t_syukin_m tm on (tn.syukin_no = tm.syukin_no)
left join m_hanyo han on (han.han_cd = tm.syukin_kbn and mst_kbn = '19')
left join m_hanyo flg1 on (flg1.mst_kbn = '99' and flg1.han_cd = 'syukin-m')
where 1=1
and tn.del_flg = '0'
and flg1.han_name = '1'
and (tn.sir_cd >= '&22' or ('&22' = '')) and (tn.sir_cd <= '&23' or ('&23' = ''))
and (tn.syukin_ymd >= '&16' or ('&16' = '')) and (tn.syukin_ymd <= '&17' or ('&17' = ''))
union all
select
	  sir_cd
	, '5' as kbn_no
	, '出金' as kbn 
	, null as sort
	, tn.syukin_ymd
	, tn.syukin_no
	, '' as cd
	--,'【'  || coalesce(han.han_name,'') ||'】' as  kbn_name
	,'【伝票金額】' as  kbn_name
	, null::numeric as suryo
	, null::numeric as tanka
	, null::numeric as kingaku
	, null::numeric as zei_gaku
	, null::numeric as zeikomi_gaku
	, tn.total_gaku as syukin_gaku
	--, tm.syukin_gaku as syukin_gaku
	, null as kaikake_gaku
	, tn.biko as biko1
from t_syukin tn
--left join t_syukin_m tm on (tn.syukin_no = tm.syukin_no)
left join m_hanyo han on (han.han_cd = tn.syukin_kbn and mst_kbn = '19')
where 1=1
and tn.del_flg = '0'
and (tn.sir_cd >= '&22' or ('&22' = '')) and (tn.sir_cd <= '&23' or ('&23' = ''))
and (tn.syukin_ymd >= '&16' or ('&16' = '')) and (tn.syukin_ymd <= '&17' or ('&17' = ''))
union all
select 
	  sir_cd
	, '6' as kbn_no
	, '締処理' as kbn
	, null as sort
	, tuk.seikyu_ymd
	, tuk.seikyu_no
	, '' as cd
	, '【締請求】' as kbn_nm
	, null::numeric as suryo
	, null::numeric as tanka
	, coalesce(zeinuki_gaku,0) as kingaku
	, coalesce(zei_gaku,0) as zei_gaku
	, coalesce(zeikomi_gaku,0) as zeikomi_gaku
	, tuk.syukin_gaku as syukin_gaku
	, seikyu_zan kaikake_gaku
	, null as biko1
from t_kaikake tuk
where 1=1
and tuk.del_flg = '0'
and (
coalesce(tuk.zeinuki_gaku,0) != 0
or coalesce(tuk.zei_gaku,0) != 0
or coalesce(tuk.zeikomi_gaku,0) != 0
or coalesce(tuk.syukin_gaku,0) != 0
)
and (tuk.sir_cd >= '&22' or ('&22' = '')) and (tuk.sir_cd <= '&23' or ('&23' = ''))
and (tuk.seikyu_ymd >= '&16' or ('&16' = '')) and (tuk.seikyu_ymd <= '&17' or ('&17' = ''))
) w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
where 1=1
and (mt.shime_dd = fnc_to_number('&31') or ('&31' = ''))
order by w.sir_cd,w.syori_ymd,w.den_no,w.kbn_no,w.sort


[SelectRecords_Jisya]
select
	 w.*
 	,(case coalesce(j_tel,'') when '' then '' else 'TEL:' || j_tel end) as j_tel_s
 	,(case coalesce(j_fax,'') when '' then '' else 'FAX:' || j_fax end) as j_fax_s
 	,(case coalesce(j_url,'') when '' then '' else 'URL:' || j_url end) as j_url_s
 	,(case coalesce(j_mail,'') when '' then '' else 'EMAIL:' || j_mail end) as j_mail_s
from(
	select
		 j3.han_name as tait--得意先元帳
		,j.mst_kbn
		,j.han_cd
		,j.han_name as j_nm
		,j.koumoku1 as j_post_s
		,j.koumoku2 || j.koumoku3 as j_address
--		,j.koumoku3 as j_address2
--		,j.koumoku4
		,j.koumoku5 as j_tel
		,j.koumoku6 as j_fax
		,j.koumoku7 as j_url
		,j.koumoku8 as j_mail
--		,j.koumoku9
--		,j.koumoku10
		,j.sort_key
		,j2.mst_kbn
		,j2.han_cd
		,j2.han_name
		,j2.koumoku1 as koumoku1
		,j2.koumoku2 as koumoku2
		,j2.koumoku3 as koumoku3
		,j2.koumoku4 as koumoku4
		,j2.koumoku5 as koumoku5
		,j2.koumoku6 as koumoku6
		,j2.koumoku7 as koumoku7
		,j2.koumoku8 as koumoku8
		,j2.koumoku9 as koumoku9
		,j2.koumoku10 as koumoku10
		, '毎度お引き立てくださいましてありがとうございます。' as mongon1
		, '下記の通りご請求させて頂きます。' as mongon2
		, '尚、締切日以降の入金は含まれておりませんのでご了承ください。' as mongon3
		, '※お振込手数料はお客様にてご負担願います。' as mongon4
		, '' as mongon5
		, case when coalesce(ar1.han_name,'') = '1' then coalesce(ar1.han_name,'')
			else '0' end as midasi_kbn
		, coalesce('&15','') || coalesce('&16','')
		, case when coalesce(m2.koumoku1,'') = 'M' then coalesce(m2.koumoku1,'')
			else '' end as midasi_FLG
		, case when coalesce(m6.han_name,'') != '' then '1'
			else '0' end as dec_FLG
	from m_hanyo j
	left join m_hanyo m2 on (m2.mst_kbn = '90' and m2.han_cd = 'hin-title') --見出し行対応（数量・単価・金額の非表示）
	left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2')
	left join m_hanyo j3 on (j3.mst_kbn = '59' and j3.han_cd = '1')--得意先元帳
	left join m_hanyo ar1 on (ar1.mst_kbn='99' and ar1.han_cd='uri-jknrno')
	left join m_hanyo m6 on (m6.mst_kbn = '99' and m6.han_cd = 'sir-su-dec') --数量小数管理
	where 1=1
	and j.mst_kbn = '90'
	and j.han_cd = 'jisya'
)w

[InsertWork]
select fnc_update_motocyo_sir_range(&1, '&2', '&3', '&4', '&5', '&6', '&7', 'BASR0220')