[load_grdList] select distinct seikyu_ymd from t_urikake order by seikyu_ymd desc [load_grdList2] select t.tok_cd , t.seikyu_ymd , t.zen_seikyu_ymd , t.zen_seikyu_zan , t.nyukin_gaku , t.kurikoshi_gaku , t.zeinuki_gaku , t.zei_gaku , t.zeikomi_gaku , t.seikyu_zan , t.den_cnt , m1.tok_nm from t_urikake t left join m_tokui m1 on (m1.tok_cd = t.tok_cd) where 1 = 1 and t.seikyu_ymd = ':seikyu_ymd' and ((':chk_zero' = '1') or (t.seikyu_zan != 0)) order by tok_cd [delete_wk] delete from w_basr0030 where 1=1 and s_id = :s_id [insert_wk] insert into w_basr0030(s_id, seikyu_ymd, tok_cd, del_flg, cre_id, cre_time, upd_id, upd_time) values(:s_id, ':seikyu_ymd', ':tok_cd', 0, ':login_id', now(), ':login_id', now()) [selectRecords] select w.skbn ,cast(w.tait as text) tait ,row_number()over(partition by tok_cd,skbn) as row_no , 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 ,kbn ,hin_nm ,suryo ,tanka ,kingaku ,zei_per ,color ,sunpo , w.sort , w.m_row , 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 '1' as skbn , '請求明細書' as tait , t.tok_cd as tok_cd , '担当:' || 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 ,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 , t.sort as sort , t.m_row as m_row , 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 , 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 ,'99'--nonyu_no ,'' ,'入金' as kbn -- ,' ('||han.han_name||')' as hin_nm ,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 -- , null as m_row , 0 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 ,'99'--nonyu_no ,'' ,'入金' as kbn -- ,' ('||han.han_name||')' as hin_nm ,han.han_name as hin_nm ,'' as suryo ,null as tanka ,n.tesuryo as kingaku ,'' ,'' ,'' , 2 as sort , 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 ,'99'--nonyu_no ,'' ,'入金' as kbn -- ,' ('||han.han_name||')' as hin_nm ,han.han_name as hin_nm ,'' as suryo ,null as tanka ,n.cyosei_gaku as kingaku ,'' ,'' ,'' , 2 as sort , 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 ,'99'--nonyu_no ,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 ,'99'--nonyu_no ,null ,'' -- ,'　　　　　　　　　　　　　　　　　【入金計】' ,'前回請求額' ,'' ,null ,case v.nyukin_gaku when 0 then null else v.nyukin_gaku end as kingaku ,'' ,'' ,'' , 3 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,skbn,nonyu_no,den_dt,den_no,m_row )w [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