[SelectRecords_normal]
select
	'お客様コード：'||tok_cd as tokui_cd_s,
	(case  tokui_nm
		when '' then ''
		else tokui_nm ||'　'|| (case keisyo when '' then '様' else keisyo end)
	end) as tokui_nm_s,
	left(zip_no,3)||'-'||right(zip_no,4) as post_n,
	(case when(nyukin_ymd != '')then(to_char(to_date(nyukin_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as nyukin_ymd,
	(case when(seikyu_ymd != '')then(to_char(to_date(seikyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as seikyu_ymd,
	(case when(tegata_ymd != '')then(to_char(to_date(tegata_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as tegata_ymd,
	'担当：'||tan_nm as tan_nm_s,
	v.*
from(
select
	tn.nyukin_no
	, tn.tok_cd
	, tn.nyukin_ymd
	--, tn.nyukin_kbn
	--, tn.nyukin_gaku
	, coalesce(tnm.nyukin_kbn, tn.nyukin_kbn) as nyukin_kbn
	, coalesce(han1.han_name, han2.han_name) as nyukin_nm
	, coalesce(tnm.nyukin_gaku, tn.nyukin_gaku) as nyukin_gaku
	, coalesce(tnm.tegata_ymd, tn.tegata_ymd) as tegata_ymd
	, coalesce(tnm.tegata_no, tn.tegata_no) as tegata_no
	, tn.total_gaku
	, coalesce(tnm.biko, tn.biko) as biko
	, tn.seikyu_flg
	, tn.seikyu_ymd
	, tn.tan_cd
	, coalesce(mt.keisyo,'') as keisyo
	, coalesce(mt.tok_nm,'') as tokui_nm
	, mt.tok_kana as tokui_kana
	, mt.zip_no
	, mt.addr1
	, mt.addr2
	, mt.addr3
	, tan.tan_nm
	, w.total_gaku_sum
from t_nyukin tn
left join m_tokui mt on (tn.tok_cd = mt.tok_cd)
left join m_tanto tan on (tn.tan_cd = tan.tan_cd)
left join t_nyukin_m tnm on (tn.nyukin_no = tnm.nyukin_no)
left join m_hanyo han1 on (tnm.nyukin_kbn = han1.han_cd and han1.mst_kbn = '13')
left join m_hanyo han2 on (tn.nyukin_kbn = han2.han_cd and han2.mst_kbn = '13')
left join (
	select 
		tok_cd,
		nyukin_ymd,
		trunc(sum(total_gaku)) as total_gaku_sum
	from t_nyukin tn
	where 1=1
	and (tn.nyukin_no = fnc_to_number('&8') or '' = '&8')
	and (tn.tok_cd >= '&3' or '' = '&3') and (tn.tok_cd <= '&4' or '' = '&4')
	and (tn.tan_cd >= '&5' or '' = '&5') and (tn.tan_cd <= '&6' or '' = '&6')
	and (tn.nyukin_ymd >= '&1' or '' = '&1') and (tn.nyukin_ymd <= '&2' or '' = '&2')
	group by nyukin_ymd,tok_cd
) w on (tn.tok_cd = w.tok_cd and tn.nyukin_ymd = w.nyukin_ymd)--入金ヘッダ合計
where 1=1
and (tn.nyukin_no = fnc_to_number('&8') or '' = '&8')
and (tn.tok_cd >= '&3' or '' = '&3') and (tn.tok_cd <= '&4' or '' = '&4')
and (tn.tan_cd >= '&5' or '' = '&5') and (tn.tan_cd <= '&6' or '' = '&6')
and (tn.nyukin_ymd >= '&1' or '' = '&1') and (tn.nyukin_ymd <= '&2' or '' = '&2')
)v
order by v.nyukin_no,v.nyukin_ymd,v.tok_cd

[SelectRecords_uri]
select
	tok_cd,
	soto_gaku2 ,
	'お客様コード：'|| tok_cd as tokui_cd_s,
	tok_nm || ' ' ||fnc_get_keisyo_1(tok_nm, tok_nm2, '御中') tokui_nm_s,
	nullif(tok_nm2,'') || '御中' tok_nm2,
	fnc_get_report_zip_no_base(tok_zip_no,null) post_n,
	to_char(to_date(syori_ymd ,'yyyymmdd'),'yyyy/mm/dd') nyukin_ymd,
	den_no nyukin_no,
	tok_addr1 addr1,
	tok_addr2 addr2,
	tok_addr3 addr3,
	case when soto_gaku2 = 0 then '' else '※ 軽減税率対象を含む。'end biko,

	case 
		when soto_gaku2 = 0 
		then concat(chr(92),to_char(zeikomi_gaku,'fm999,999,999'))
		else concat(chr(92),to_char(zeikomi_gaku,'fm999,999,999'),'※')
	end total_gaku_sum,
	
	--#27490
	case 
		when coalesce(kazei_gaku,0) != 0
		then 	concat(COALESCE(mzei.koumoku3,'') ,'%対象 ', to_char(coalesce(zeinuki_gaku,0),'fm999,999,999'),'円')
		else 	concat(COALESCE(mzei.koumoku5,'') ,'%対象 ', to_char(coalesce(zeinuki_gaku2,0),'fm999,999,999'),'円') end  kazei1,
	case 
		when coalesce(kazei_gaku,0) = 0 and coalesce(kazei_gaku2,0) != 0
		then null
		when coalesce(kazei_gaku,0) != 0 and coalesce(kazei_gaku2,0) = 0
		then null
		else concat(COALESCE(mzei.koumoku5,'') ,'%対象 ', to_char(coalesce(zeinuki_gaku2,0),'fm999,999,999'),'円') end kazei2,

	case 
		when coalesce(kazei_gaku,0) != 0
		then 	concat('消費税 ', to_char(coalesce(uchi_gaku,0) + coalesce(soto_gaku,0),'fm999,999,999'),'円')
		else 	concat('消費税 ', to_char(coalesce(uchi_gaku2,0) + coalesce(soto_gaku2,0),'fm999,999,999'),'円') end  zei1,
	case 
		when coalesce(kazei_gaku,0) = 0 and coalesce(kazei_gaku2,0) != 0
		then null
		when coalesce(kazei_gaku,0) != 0 and coalesce(kazei_gaku2,0) = 0
		then null
		else concat('消費税 ', to_char(coalesce(uchi_gaku2,0) + coalesce(soto_gaku2,0),'fm999,999,999'),'円') end zei2,

	case 
		when coalesce(kazei_gaku,0) != 0
		then 	concat(to_char(coalesce(zeinuki_gaku,0) + coalesce(uchi_gaku,0) + coalesce(soto_gaku,0),'fm999,999,999'),'円')
		else 	concat(to_char(coalesce(zeinuki_gaku2,0) + coalesce(uchi_gaku2,0) + coalesce(soto_gaku2,0),'fm999,999,999'),'円') end  kei1,
	case 
		when coalesce(kazei_gaku,0) = 0 and coalesce(kazei_gaku2,0) != 0
		then null
		when coalesce(kazei_gaku,0) != 0 and coalesce(kazei_gaku2,0) = 0
		then null
		else concat(to_char(coalesce(zeinuki_gaku2,0) + coalesce(uchi_gaku2,0) + coalesce(soto_gaku2,0),'fm999,999,999'),'円') end kei2
		from t_uri uh
		left join m_hanyo mzei on (mzei.mst_kbn = '93' and uh.syori_ymd between mzei.koumoku1 and mzei.koumoku2)
where den_no = '&1' 

[SelectRecords_Jisya]
select
	  *
	, '発注書' as tait
	, 'TEL:' || j_tel as j_tel_s
	, 'FAX:' || j_fax as j_fax_s
	, (case j_url
		when '' then ''
		else 'URL:' || j_url
	end) as j_url_s
	--,'URL:' || j_url as j_url_s
	, (case j_daihyo
		when '' then ''
		else '担当者:' || j_daihyo
	end) as j_daihyo_s
	, '登録番号:' || nullif(j_reg_no,'') j_reg_no_s
	, '(注)社印がない場合は、無効です。' as mongon1
	, '但し、' as mongon2
	, '' as mongon3
	, '' as mongon4
	, '' as mongon5
from fnc_query_jisya('{"":""}' :: json)

[SelectRecords_flg]
select 
	  flg_1 as uri_flg
	, flg_2 
	, flg_3
	, flg_4
	, flg_5
	, flg_6
from fnc_query_set_flg('&1')