--[@rs1] [selectRecords_hed] select u.den_no || to_char(m.row_no,'FM00') as shiji_no -- , m.row_no + 1 as row_no , m.row_no , u.den_no , trunc((m.row_no)/ 5) + 1 as row_page , u.jucyu_no , u.tan_cd , u.tok_tan , tan.tan_nm , m.jucyu_row_no , m.jucyu_su , m.tani , m.hosoku2 , case when u.syori_ymd = '' then '日付を入力してください' else to_char(to_date(u.syori_ymd,'yyyymmdd'),'yyyy/mm/dd') end as syori_ymd , case when tun.noki = '' then '日付を入力してください' else to_char(to_date(tun.noki,'yyyymmdd'),'yyyy/mm/dd') end as nonyu_ymd , case u.nonyu_tan when '' then '御中' else '' end as keisyou_tok , case when coalesce(u.nonyu_tan,'') = '' then '' else '様' end as keisyou_tan , u.tok_cd , u.tok_nm , m.suryo , m.tanka , m.kingaku , u.zeinuki_gaku , u.zei_gaku , u.zeikomi_gaku , u.seikyu_ymd , t.tok_nm as tokui_nm , case t.keisyo when '' then '様' else t.keisyo end as keisyo , t.tok_kana as tokui_kana , left(t.zip_no,3)||'-'||right(t.zip_no,4) as post_n , m.hin_nm , m.meisai_biko , u.biko1 , 0.8 as jimu_cost , coalesce(ts.unchin,0) as unchin , kigata1.mock_no as kigata_no_01 , kigata1.mock_info as kigata_info_01 , kigata2.mock_no as kigata_no_02 , kigata2.mock_info as kigata_info_02 , kigata3.mock_no as kigata_no_03 , kigata3.mock_info as kigata_info_03 , kigata4.mock_no as kigata_no_04 , kigata4.mock_info as kigata_info_04 , kigata5.mock_no as kigata_no_05 , kigata5.mock_info as kigata_info_05 , kigata6.mock_no as kigata_no_06 , kigata6.mock_info as kigata_info_06 , ts.kingaku as rui_kingaku , ts.genka_gaku , ts.zairyo_gaku , ts.unchin , ts.arari_gaku , ts.zairyo_tanka , ts.koutei_tanka , ts.arari_tanka , ts.saeki_tanka , ts.gentanka , ts.price_rate , ts.kibo_tanka , ts.tanka as rui_tanka , replace(replace(ts.sekkei_biko,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as sekkei_biko from t_uri u left join t_uri_m m on (u.den_no = m.den_no) left join t_seizou ts on (m.den_no = ts.jucyu_no) and (m.row_no = ts.jucyu_row_no) left join t_shiyou_zairyo tsz on (ts.shiji_no = tsz.shiji_no) left join m_tokui t on (u.tok_cd = t.tok_cd) left join m_tanto tan on (u.tan_cd = tan.tan_cd) left join w_report rep on (rep.den_no = m.den_no and m.row_no = rep.row_no) left join t_uri_nouki tun on (tun.den_no = u.den_no) left join t_seizou_mock kigata1 on (kigata1.row_no = '0' and kigata1.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) left join t_seizou_mock kigata2 on (kigata2.row_no = '1' and kigata2.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) left join t_seizou_mock kigata3 on (kigata3.row_no = '2' and kigata3.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) left join t_seizou_mock kigata4 on (kigata4.row_no = '3' and kigata4.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) left join t_seizou_mock kigata5 on (kigata5.row_no = '4' and kigata5.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) left join t_seizou_mock kigata6 on (kigata6.row_no = '5' and kigata6.shiji_no = cast(u.den_no || to_char(m.row_no,'FM00')as integer)) where 1=1 and u.del_flg = 0 and rep.s_id = '&7' and ((u.input_ymd >= '&1') or ('&1' = '')) and ((u.input_ymd <= '&2') or ('&2' = '')) and ((cast(u.den_no as varchar(8))= '&3') or ('&3' = '')) and ((t.tok_cd = '&4') or ('&4' = '')) and ((m.hin_nm like '&5') or ('&5' like '')) and ((cast(m.row_no as varchar(8))= '&10') or ('&10' = '')) order by den_no,tok_cd,syori_ymd,m.row_no; --[@rs2] [selectRecords_kounyu] select tsz.row_no + 1 as row_no , tsz.hin_nm , tsz.size_l , tsz.size_w , tsz.size_lw , tsz.weight , tsz.renryo , tsz.wtanka , tsz.tanka , tsz.calc_su , tsz.yobi_su , tsz.suryo , tsz.hacyu_leadtime , tsz.sir_nm , (coalesce(tsz.kingaku,0)) as kingaku from t_shiyou_zairyo tsz where 1=1 and shiyou_zairyo_kbn = '0' --and ((cast(tsz.shiji_no as varchar(10))= '700000700') or ('700000700' = '')) and ((cast(tsz.shiji_no as varchar(10))= '&1') or ('&1' = '')) order by row_no ; --[@rs3] [selectRecords_gaicyu] select tsz.row_no + 1 as row_no , tsz.zairyo_no , tsz.hin_nm , tsz.size_l , tsz.size_w , tsz.size_lw , tsz.weight , tsz.renryo , tsz.wtanka , tsz.tanka , tsz.calc_su , tsz.yobi_su , tsz.suryo , tsz.hacyu_leadtime , tsz.sir_nm , tsz.sir_tan , tsz.kingaku from t_shiyou_zairyo tsz where 1=1 and shiyou_zairyo_kbn = '1' --and ((cast(tsz.shiji_no as varchar(10))= '700000700') or ('700000700' = '')) and ((cast(tsz.shiji_no as varchar(10))= '&1') or ('&1' = '')) order by row_no ; --[@rs4] [selectRecords_kakou] select tsm.shiji_no , tsm.zairyo_no , tsm.koutei_no , tsm.koutei_cd , tsm.koutei_nm , tsm.kousu , tsm.seizou_tan_cd , tan.tan_nm , tsm.seizou_biko , tsm.status , tsm.yotei_stt_time , tsm.yotei_end_time , tsm.stt_time , tsm.end_time , tsm.stop_time , tsm.restart_time , tsm.stop_kikan , tsm.stt_tan_cd , tsm.end_tan_cd , tsm.stop_tan_cd , tsm.restart_tan_cd , tsm.iko_key , tsm.del_flg , tsm.cre_id , tsm.cre_time , tsm.upd_id , tsm.upd_time , tsm.suryo , tsm.seizou_tan_cnt , tsm.tanka , tsm.kingaku , tsm.seizou_hosoku , tsm.fixed_cost from t_seizou_m tsm left join m_tanto tan on (tan.tan_cd = tsm.seizou_tan_cd) where 1=1 and koutei_cd != '99' --and ((cast(tsm.shiji_no as varchar(10))= '700000700') or ('700000700' = '')) and ((cast(tsm.shiji_no as varchar(10))= '&1') or ('&1' = '')) order by koutei_no,zairyo_no ; --[@rs5] [selectRecords_syukka] select tsm.shiji_no , tsm.zairyo_no , tsm.koutei_no , tsm.koutei_cd , tsm.koutei_nm , tsm.kousu , tsm.seizou_tan_cd , tan.tan_nm , tsm.seizou_biko , tsm.status , tsm.yotei_stt_time , tsm.yotei_end_time , tsm.stt_time , tsm.end_time , tsm.stop_time , tsm.restart_time , tsm.stop_kikan , tsm.stt_tan_cd , tsm.end_tan_cd , tsm.stop_tan_cd , tsm.restart_tan_cd , tsm.iko_key , tsm.del_flg , tsm.cre_id , tsm.cre_time , tsm.upd_id , tsm.upd_time , tsm.suryo , tsm.seizou_tan_cnt , tsm.tanka , tsm.kingaku , tsm.seizou_hosoku , tsm.fixed_cost from t_seizou_m tsm left join m_tanto tan on (tan.tan_cd = tsm.seizou_tan_cd) where 1=1 and koutei_cd = '99' --and ((cast(tsm.shiji_no as varchar(10))= '700000700') or ('700000700' = '')) and ((cast(tsm.shiji_no as varchar(10))= '&1') or ('&1' = '')) order by koutei_no,zairyo_no;