[selectRecords] select cast(w.tait as text) tait ,row_number()over(partition by tok_cd) as row_no , w.sort , w.m_row , W.tok_cd , tan_nm_s , w.seikyu_ymd_s , w.seikyu_ymd , w.zen_seikyu_ymd , w.zen_seikyu_zan , w.nyukin_gaku , w.kurikoshi_gaku , w.zeinuki_gaku , w.zei_gaku , w.zeikomi_gaku , w.seikyu_zan , w.den_cnt , w.tok_nm ,(case coalesce(w.tok_nm,'') when '' then '' else w.tok_nm ||'　'|| (case coalesce(w.keisyou,'') when '' then '様' else w.keisyou end) end) as tok_nm_s , w.tok_tan , w.tel_no , w.keisyou ,yubin_no ,address1 ,address2 ,address3 ,den_dt ,den_no ,nonyu_no ,nonyu_nm ,concat_nonyu ,kbn ,hin_nm ,suryo ,tanka ,kingaku ,zei_per ,color ,sunpo , mst_kbn , han_cd , han_name , post_s , koumoku2 , koumoku3 , koumoku4 , koumoku5 , tel_s , koumoku6 , fax_s , koumoku7 , url_s , koumoku8 , koumoku9 , tani , meisai_biko ,'※ご請求金額が10,000円未満の場合は' as mongon1 ,'　　振込手数料は貴社負担にてお願い致します。' as mongon2 ,furikikan1 || '　' || furikikan2 || '　' || furikikan3 as furikikan1 from( select '請求明細書' as tait , t.tok_cd as tok_cd , t.sort as sort , t.m_row as m_row , '担当:' || t.tan_nm as tan_nm_s , to_char(to_date(t.seikyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as seikyu_ymd_s , t.seikyu_ymd , t.zen_seikyu_ymd 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.zeinuki_gaku as zeinuki_gaku , t.zei_gaku as zei_gaku , t.zeikomi_gaku as zeikomi_gaku , t.seikyu_zan as seikyu_zan , t.den_cnt as den_cnt , t.tok_nm as tok_nm , t.tel_no as tel_no , t.tok_tan as tok_tan , t.keisyo as keisyou ,yubin_no as yubin_no ,address1 as address1 ,address2 as address2 ,address3 as address3 ,den_dt as den_dt ,den_no as den_no ,nonyu_no as nonyu_no ,nonyu_nm as nonyu_nm ,concat(coalesce(('' || nonyu_no),''),nonyu_nm) as concat_nonyu ,kbn as kbn ,hin_nm as hin_nm ,suryo as suryo ,tanka as tanka ,kingaku as kingaku ,zei_per as zei_per ,color as color ,sunpo as sunpo , j.mst_kbn , j.han_cd , j.han_name , j.koumoku1 as post_s , j.koumoku2 , j.koumoku3 , j.koumoku4 , j.koumoku5 , 'TEL:' || j.koumoku5 as tel_s , j.koumoku6 , 'FAX:' || j.koumoku6 as fax_s , j.koumoku7 , 'URL:' || j.koumoku7 as url_s , j.koumoku8 , j.koumoku9 , tani , meisai_biko , jf.koumoku1 as furikikan1 , jf.koumoku2 as furikikan2 , jf.koumoku3 as furikikan3 from w_basr0030 w left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya') left join m_hanyo jf on (jf.mst_kbn='09' and jf.han_cd='0') inner join ( select v.seikyu_ymd , v.tok_cd , ta.tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'mm/dd') as den_dt ,uh.den_no as den_no ,uh.nonyu_no as nonyu_no ,uh.nonyu_nm as nonyu_nm ,case uh.henpin_flg when '1' then '返品' else '売上' end as kbn ,um.hin_nm as hin_nm ,cast(um.suryo as varchar) as suryo ,um.tanka as tanka ,um.kingaku as kingaku ,case when uh.zei_kbn = '2' then '' else'(8.00%)' end as zei_per ,'' as color ,'' as sunpo , 0 as sort , um.row_no as m_row , um.tani , um.meisai_biko from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join t_uri uh on (v.seikyu_ymd = uh.seikyu_ymd and v.tok_cd = uh.tok_cd and uh.del_flg = 0 and uh.jucyu_flg = 0) left join t_uri_m um on (uh.den_no = um.den_no) left join m_tanto ta on (m1.tan_cd = ta.tan_cd) left join m_hanyo han on (han.mst_kbn = '01' and han.han_cd = uh.prc_sts ) where 1=1 and coalesce(han.koumoku3, '') = '' union all select v.seikyu_ymd , v.tok_cd , null as tan_nm , null as zen_seikyu_ymd , null as zen_seikyu_zan , null as nyukin_gaku , null as kurikoshi_gaku , null as zeinuki_gaku -- , null as zei_gaku , null as zeikomi_gaku , null as seikyu_zan , null as den_cnt , null as tok_nm , null as keisyo , null as tel_no , null as tok_tan , null as yubin_no , null as address1 , null as address2 , null as address3 , null as den_dt , null as den_no , null as nonyu_no -- ,uh.nonyu_nm as nonyu_nm , null as nonyu_nm , null as kbn , '('||uh.nonyu_no||')'||coalesce(uh.nonyu_nm,'') as hin_nm , null as suryo , null as tanka , sum(uh.zeinuki_gaku) as kingaku , null as zei_per ,'' as color ,'' as sunpo , 3 as sort ,uh.nonyu_no as m_row , null as tani , null from t_urikake v left join t_uri uh on (v.seikyu_ymd = uh.seikyu_ymd and v.tok_cd = uh.tok_cd and uh.jucyu_flg = 0) left join m_hanyo han on (han.mst_kbn = '01' and han.han_cd = uh.prc_sts ) where 1=1 and coalesce(han.koumoku3, '') = '' group by v.seikyu_ymd,v.tok_cd,uh.nonyu_no,uh.nonyu_nm,'('||uh.nonyu_no||')'||coalesce(uh.nonyu_nm,'') union all select v.seikyu_ymd , v.tok_cd , ta.tan_nm as tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,to_char(to_date(n.nyukin_ymd, 'YYYYMMDD'), 'mm/dd') as den_dt ,n.nyukin_no as den_no , null , null ,'入金' as kbn ,han.han_name as hin_nm ,'' as suryo ,null as tanka --2018.11.12 #6326 MOD STT -- ,n.total_gaku as kingaku ,n.nyukin_gaku as kingaku --2018.11.12 #6326 MOD END ,'' ,'' ,'' , 2 as sort --2018.11.12 #6326 MOD STT -- , n.nyukin_no as m_row , (10 * n.nyukin_no) as m_row --2018.11.12 #6326 MOD END ,'' ,n.biko from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join t_nyukin n on (v.seikyu_ymd = n.seikyu_ymd and v.tok_cd = n.tok_cd) left join m_hanyo han on (n.nyukin_kbn = han.han_cd and han.mst_kbn = '13') left join m_tanto ta on (m1.tan_cd = ta.tan_cd) where 1=1 and n.nyukin_gaku is not null --2018.11.12 #6326 ADD STT --振込手数料 union all select v.seikyu_ymd , v.tok_cd , ta.tan_nm as tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,to_char(to_date(n.nyukin_ymd, 'YYYYMMDD'), 'mm/dd') as den_dt ,n.nyukin_no as den_no , null , null ,'入金' as kbn ,han.han_name as hin_nm ,'' as suryo ,null as tanka ,n.tesuryo as kingaku ,'' ,'' ,'' , 2 as sort , (10 * n.nyukin_no + 1) as m_row ,'' ,'' as biko from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join t_nyukin n on (v.seikyu_ymd = n.seikyu_ymd and v.tok_cd = n.tok_cd) left join m_hanyo han on ('tesuryo' = han.koumoku2 and han.mst_kbn = '13') left join m_tanto ta on (m1.tan_cd = ta.tan_cd) where 1=1 and n.tesuryo != 0 --調整・その他 union all select v.seikyu_ymd , v.tok_cd , ta.tan_nm as tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,to_char(to_date(n.nyukin_ymd, 'YYYYMMDD'), 'mm/dd') as den_dt ,n.nyukin_no as den_no , null , null ,'入金' as kbn ,han.han_name as hin_nm ,'' as suryo ,null as tanka ,n.cyosei_gaku as kingaku ,'' ,'' ,'' , 2 as sort , (10 * n.nyukin_no + 2) as m_row ,'' ,'' as biko from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join t_nyukin n on (v.seikyu_ymd = n.seikyu_ymd and v.tok_cd = n.tok_cd) left join m_hanyo han on ('cyosei_gaku' = han.koumoku2 and han.mst_kbn = '13') left join m_tanto ta on (m1.tan_cd = ta.tan_cd) where 1=1 and n.cyosei_gaku != 0 --2018.11.12 #6326 ADD END union all select v.seikyu_ymd , v.tok_cd , ta.tan_nm as tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,to_char(to_date(v.seikyu_ymd, 'YYYYMMDD'), 'mm/dd') as den_dt ,null , null , null ,'' ,'請求時一括消費税' ,'' ,null ,v.zei_gaku as kingaku ,'' ,'' ,'' , 1 as sort , null as m_row ,'' ,'' from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join m_tanto ta on (m1.tan_cd = ta.tan_cd) union all select v.seikyu_ymd , v.tok_cd , ta.tan_nm as tan_nm , v.zen_seikyu_ymd , v.zen_seikyu_zan , v.nyukin_gaku , v.kurikoshi_gaku , v.zeinuki_gaku , v.zei_gaku , v.zeikomi_gaku , v.seikyu_zan , v.den_cnt , m1.tok_nm , m1.keisyo , m1.tel_no , coalesce(m1.tok_tan,'') as tok_tan ,'〒'||substr(replace(m1.zip_no,'-',''),1,3)||'-'||substr(replace(m1.zip_no,'-',''),4,6) as yubin_no ,m1.addr1 as address1 ,m1.addr2 as address2 ,m1.addr3||m1.addr4 as address3 ,null ,null , null , null ,'' ,'前回請求額' ,'' ,null ,case v.nyukin_gaku when 0 then null else v.nyukin_gaku end as kingaku ,'' ,'' ,'' , 4 as sort , null as m_row ,'' ,'' from t_urikake v left join m_tokui m1 on (m1.tok_cd = v.tok_cd) left join m_tanto ta on (m1.tan_cd = ta.tan_cd) ) t on (t.seikyu_ymd = w.seikyu_ymd and t.tok_cd = w.tok_cd) where 1=1 and w.s_id = &1 order by tok_cd,sort desc,nonyu_no,den_dt,den_no,m_row )w [selectRecords_mei] select (case mt.zei_calc_kbn when '1' then null else zei_gaku end) as zei_gaku ,(case mt.zei_calc_kbn when '1' then zeinuki_gaku else zeikomi_gaku end) as zeikomi_gaku ,v.* from( select uh.seikyu_ymd, uh.tok_cd, uh.nonyu_no, -- uh.nonyu_nm, coalesce(uh.nonyu_no::varchar,'')||coalesce(uh.nonyu_nm,'') as concat_nonyu, sum(uh.zeinuki_gaku) as zeinuki_gaku, sum(uh.zei_gaku) as zei_gaku, sum(uh.zeikomi_gaku) as zeikomi_gaku from t_uri uh left join m_hanyo han on (han.mst_kbn = '01' and han.han_cd = uh.prc_sts ) where 1 = 1 and coalesce(han.koumoku3, '') = '' and uh.tok_cd = '&2' and uh.seikyu_ymd = '&3' --and ((coalesce(uh.nonyu_no::varchar,'')||coalesce(uh.nonyu_nm,'')) = ('&4'||'&5') or (('&4'||'&5') = '')) and ((coalesce(uh.nonyu_no::varchar,'')||coalesce(uh.nonyu_nm,'')) = ('&4'||'&5')) group by uh.seikyu_ymd,uh.tok_cd,uh.nonyu_no,uh.nonyu_nm )v left join m_tokui mt on (v.tok_cd = mt.tok_cd) order by v.seikyu_ymd,v.tok_cd,v.nonyu_no [SelectRecords_Jisya] select w.* ,'TEL:' || j_tel as j_tel_s ,'FAX:' || j_fax as j_fax_s ,'URL:' || j_url as j_url_s ,'EMAIL:' ||j_daihyo as j_daihyo_s from( select '請求明細書' as tait ,j.mst_kbn ,j.han_cd ,j.han_name as j_nm ,j.koumoku1 as j_post_s ,j.koumoku2 as j_address ,j.koumoku3 as j_address2 ,j.koumoku4 as j_address3 ,j.koumoku5 as j_tel ,j.koumoku6 as j_fax ,j.koumoku7 as j_url ,j.koumoku8 as j_daihyo -- ,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 , '※ご請求金額が10,000円未満の場合は' as mongon4 , '　　振込手数料は貴社負担にてお願い致します。' as mongon5 from m_hanyo j left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2') where 1=1 and j.mst_kbn = '90' and j.han_cd = 'jisya' )w [SelectRecords_Furi] select han_name || ' ' || furikikan1 || ' ' || furikikan2 || ' ' || furikikan3 as furikikan from( select mst_kbn, han_cd, han_name, koumoku1 as furikikan1, koumoku2 as furikikan2, koumoku3 as furikikan3, koumoku4, koumoku5, coalesce(koumoku6,''), koumoku7, koumoku8, koumoku9, koumoku10, sort_key from m_hanyo where 1=1 and mst_kbn = '09' and coalesce(koumoku6,'') = '' )w order by han_cd