[selectRecords] select trunc((uri_row)/ 7) + 1 as uri_row_kai ,'page:'||trunc((uri_row)/ 7) + 1 as uri_row_kai_s ,(case when (syori_ymd = '') then '日付を入力してください' else to_char(to_date(syori_ymd,'yyyymmdd'),'yyyy/mm/dd') end) as u_ymd ,'お客様コード：'||tokui_cd as tokui_cd_s ,(case when (coalesce(tokui_nm,'') = '') then '' else tokui_nm ||'　'|| (case coalesce(keisyo,'') when '' then '様' else keisyo end) end) as tokui_nm_s ,(case when ((case when (coalesce(w.nonyu_nm,'') = coalesce(w.tokui_nm,'')) then '' else coalesce(w.nonyu_nm,'')end) = '') then '' else (case when ('&14' = '1') then '現場：' else '納入先：' end) || (case when (coalesce(w.nonyu_nm,'') = coalesce(w.tokui_nm,'')) then '' else coalesce(w.nonyu_nm,'')end) end) as nonyu_nm_s --juha-syncがONの場合&11は1 ,'担当：'||(case when ('&24' = '1') then eigyou_tan else tok_tan end) as tan_nm_s --juha-syncがONの場合&11は1 ,(case when uri_s is null then null else uri_g end) as uri_g ,(case when (w.zei_calc_kbn = '1') then null else w.zei_gaku end) as z_gaku ,(case when (w.zei_calc_kbn = '1') then w.zeinuki_gaku else w.zeikomi_gaku end) as zi_gaku ,(case when (coalesce(w.nonyu_nm,'') = coalesce(w.tokui_nm,'')) then '' else coalesce(w.nonyu_nm,'')end) as nonyu_nm ,(case when ('&23' = '1') then tok_zip_no_s else nonyu_zip_no_s end) as tok_zip_no ,(case when ('&23' = '1') then w.tok_addr1 else w.nonyu_addr1 end) as tok_addr1--書類送付先機能がONの場合&10は1が入りtok_addrシリーズを使う。OFFの場合&10は0が入りnonyu_addrシリーズを使う、画面上が空白なら空になる ,(case when ('&23' = '1') then w.tok_addr2 else w.nonyu_addr2 end) as tok_addr2 ,(case when ('&23' = '1') then w.tok_addr3 else w.nonyu_addr3 end) as tok_addr3 ,(case when ('&23' = '1') then w.tok_addr4 else w.nonyu_addr4 end) as tok_addr4 ,(case when ('&23' = '1') then coalesce(w.tok_tel_no,'') else coalesce(w.nonyu_tel_no,'') end) as tok_tel ,(case when ('&23' = '1') then coalesce(w.tok_fax_no,'') else coalesce(w.nonyu_fax_no,'') end) as tok_fax ,w.* , '&1' , '&2' , '&3' , '&4' , '&5' , '&6' , '&7' , '&8' , '&9' , '&10' , '&11' , '&12' , '&13' , '&14' , '&15' , '&16' , '&17' , '&18' , '&19' , '&20' from( select --uh uh.den_no as uri_no ,uh.jucyu_no as jyucyu ,uh.syori_ymd as syori_ymd ,uh.tok_cd as tokui_cd ,uh.zeinuki_gaku as zn_gaku ,uh.zei_gaku ,uh.zeinuki_gaku ,uh.zeikomi_gaku ,uh.seikyu_ymd as s_ymd ,uh.tok_nm as tokui_nm ,uh.nonyu_no ,uh.nonyu_nm ,replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1 ,uh.tok_zip_no ,(case when (coalesce(uh.tok_zip_no,'') = '') then '' else '〒'||left(uh.tok_zip_no,3)||'-'||right(uh.tok_zip_no,4) end) as tok_zip_no_s ,uh.tok_addr1 ,uh.tok_addr2 ,uh.tok_addr3 ,uh.tok_addr4 ,uh.tok_tel_no ,uh.tok_fax_no ,uh.nonyu_zip_no ,(case when (coalesce(uh.nonyu_zip_no,'') = '') then '' else '〒'||left(uh.nonyu_zip_no,3)||'-'||right(uh.nonyu_zip_no,4) end) as nonyu_zip_no_s ,uh.nonyu_addr1 ,uh.nonyu_addr2 ,uh.nonyu_addr3 ,uh.nonyu_addr4 ,uh.nonyu_tel_no ,uh.nonyu_fax_no --um ,um.row_no as uri_row ,um.jucyu_no as jyucyum ,um.jucyu_row_no as jyucyu_row ,um.tani as tani ,um.suryo as uri_s ,um.tanka as uri_t ,um.kingaku as uri_g ,um.hin_nm ,um.meisai_biko --mt ,mt.tok_kana as tokui_kana ,mt.keisyo as keisyo ,mt.zei_calc_kbn --tan ,tan.tan_nm as tok_tan --eigyou_tan ,eigyou_tan.tan_nm as eigyou_tan from t_uri uh left join t_uri_m um on (uh.den_no = um.den_no) left join m_tokui mt on (uh.tok_cd = mt.tok_cd) left join m_tanto tan on (uh.tan_cd = tan.tan_cd) left join m_tanto eigyou_tan on (uh.eigyou_tan_cd = eigyou_tan.tan_cd) where 1=1 and uh.del_flg = 0 and jucyu_flg = 0 and COALESCE(uh.mitsumori_flg,0) = 0 and ((cast(uh.den_no as varchar(8))= '&3') or ('&3' = '')) and (uh.tok_cd = '&4' or '' = '&4') and (uh.syori_ymd >= '&1' or '' = '&1') and (uh.syori_ymd <= '&2' or '' = '&2') )w order by uri_no,tokui_cd,u_ymd,uri_row ; [SelectRecords_Jisya] select * , '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 ,(case j_daihyo when '' then '' else '担当者:' || j_daihyo end) as j_daihyo_s , '登録番号:' || nullif(j_reg_no,'') j_reg_no_s , '下記のとおり、納品致しました。' as mongon1 , split_part('&1',',',1) as title1 , split_part('&1',',',2) as title2 , split_part('&1',',',3) as title3 from fnc_query_jisya('{"":""}' :: json) [_SelectRecords_Jisya] select '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 ,w.* 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 , '' as mongon4 , '' as mongon5 ,(case when coalesce(ar1.han_name,'') = '1' then coalesce(ar1.han_name,'') else '0' end) as uri_tokadr_kbn ,(case when coalesce(juha.han_name,'') = '1' then coalesce(juha.han_name,'') else '0' end) as juha_sync_flg ,(case when coalesce(uritndec.han_name,'') = '1' then coalesce(uritndec.han_name,'') else '0' end) as uritndec_flg , split_part('&1',',',1) as title1 , split_part('&1',',',2) as title2 , split_part('&1',',',3) as title3 from m_hanyo j left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2') left join m_hanyo ar1 on (ar1.mst_kbn='99' and ar1.han_cd='uri-tokadr') --住所出力区分 left join m_hanyo juha on (juha.mst_kbn='99' and juha.han_cd='juha-sync') --住所出力区分 left join m_hanyo uritndec on (uritndec.mst_kbn='99' and uritndec.han_cd='uri-tn-dec') --売上単価小数点管理 where 1=1 and j.mst_kbn = '90' and j.han_cd = 'jisya' )w