[selectRecords] select w.skbn , cast(w.tait as text) tait ,row_number()over(partition by tok_cd,skbn) as row_no ,trunc((row_number()over(partition by tok_cd,skbn)-1)/35) as row_page ,(trunc((row_number()over(partition by tok_cd,skbn))/35)+1)||'ページ' as row_B , W.tok_cd , w.tan_nm , w.seikyu_ymd , cast(w.seikyu_no as text) seikyu_no , 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 , w.tok_tan , w.tel_no , w.keisyou , w.keisyou2 ,yubin_no ,address1 ,address2 ,address3 ,den_dt ,den_no ,kbn ,hin_nm ,suryo ,tanka ,kingaku ,zei_per ,color ,sunpo , w.sort , w.m_row , w.mst_kbn , w.han_cd , w.han_name , w.koumoku1 , w.koumoku2 , w.koumoku3 , w.koumoku4 , w.koumoku5 , w.koumoku6 , w.koumoku7 , w.koumoku8 , w.koumoku9 from( select '1' as skbn , '御 請 求 書' as tait , t.tok_cd as tok_cd , t.tan_nm as tan_nm , to_char(to_date(t.seikyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as seikyu_ymd , '123456'as seikyu_no , 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.keisyou as keisyou , t.keisyou2 as keisyou2 ,yubin_no as yubin_no ,address1 as address1 ,address2 as address2 ,address3 as address3 ,den_dt as den_dt ,den_no as den_no ,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 , t.mst_kbn , t.han_cd , t.han_name , t.koumoku1 , t.koumoku2 , t.koumoku3 , t.koumoku4 , t.koumoku5 , t.koumoku6 , t.koumoku7 , t.koumoku8 , t.koumoku9 from w_basr0030 w 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.tel_no , coalesce(m1.tok_tan,'') as tok_tan , case m1.cat_cd when '1' then case coalesce(m1.tok_tan,'') when '' then '御中' else '' end when '2' then '様' else '御中' end as keisyou ,case coalesce(m1.tok_tan,'') when '' then '' else '様' end as keisyou2 ,'〒'||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 ,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 , j.mst_kbn , j.han_cd , j.han_name , j.koumoku1 , j.koumoku2 , j.koumoku3 , j.koumoku4 , j.koumoku5 , j.koumoku6 , j.koumoku7 , j.koumoku8 , j.koumoku9 from t_urikake v left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya') 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.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) 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.tel_no , coalesce(m1.tok_tan,'') as tok_tan , case m1.cat_cd when '1' then case coalesce(m1.tok_tan,'') when '' then '御中' else '' end when '2' then '様' else '御中' end as keisyou ,case coalesce(m1.tok_tan,'') when '' then '' else '様' end as keisyou2 ,'〒'||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 ,'入金' as kbn ,' ('||han.han_name||')' as hin_nm ,'' as suryo ,null as tanka ,n.total_gaku as kingaku ,'' ,'' ,'' , 1 as sort , null as m_row , j.mst_kbn , j.han_cd , j.han_name , j.koumoku1 , j.koumoku2 , j.koumoku3 , j.koumoku4 , j.koumoku5 , j.koumoku6 , j.koumoku7 , j.koumoku8 , j.koumoku9 from t_urikake v left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya') 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 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.tel_no , coalesce(m1.tok_tan,'') as tok_tan , case m1.cat_cd when '1' then case coalesce(m1.tok_tan,'') when '' then '御中' else '' end when '2' then '様' else '御中' end as keisyou ,case coalesce(m1.tok_tan,'') when '' then '' else '様' end as keisyou2 ,'〒'||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 ,v.zeikomi_gaku as kingaku ,'' ,'' ,'' , 2 as sort , null as m_row , j.mst_kbn , j.han_cd , j.han_name , j.koumoku1 , j.koumoku2 , j.koumoku3 , j.koumoku4 , j.koumoku5 , j.koumoku6 , j.koumoku7 , j.koumoku8 , j.koumoku9 from t_urikake v left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya') 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.tel_no , coalesce(m1.tok_tan,'') as tok_tan , case m1.cat_cd when '1' then case coalesce(m1.tok_tan,'') when '' then '御中' else '' end when '2' then '様' else '御中' end as keisyou ,case coalesce(m1.tok_tan,'') when '' then '' else '様' end as keisyou2 ,'〒'||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 ,case v.nyukin_gaku when 0 then null else v.nyukin_gaku end as kingaku ,'' ,'' ,'' , 3 as sort , null as m_row , j.mst_kbn , j.han_cd , j.han_name , j.koumoku1 , j.koumoku2 , j.koumoku3 , j.koumoku4 , j.koumoku5 , j.koumoku6 , j.koumoku7 , j.koumoku8 , j.koumoku9 from t_urikake v left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya') 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,skbn,den_dt,sort,den_no,m_row )w