[selectRecords]
select
  uh.den_no
, to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd
, to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd
, uh.tok_cd
, uh.tok_nm
, uh.tok_addr1 as addr1
, uh.tok_addr2 as addr2
, uh.tok_addr3 as addr3
, uh.tok_addr4 as addr4
, uh.tok_tel_no
, uh.tok_fax_no
, uh.nonyu_nm
, uh.nonyu_tan
, uh.nonyu_addr1
, uh.nonyu_addr2
, uh.nonyu_addr3
, uh.nonyu_addr4
, uh.nonyu_tel_no
, uh.nonyu_fax_no
, uh.zeinuki_gaku
, uh.zei_gaku
, uh.zeikomi_gaku
, uh.kanri_no
, uh.tok_hacyu_no as tok_hacyu_no_h
, uh.payment_term
, uh.biko4
, uh.biko5
, uh.biko6
, uh.pay_kbn
, h76.koumoku1 as pay_kbn_nm
, jh.kanri_no as j_kanri_no
, 'OSAKA, JAPAN' as from_s
, uh.biko3 as trans_s
, um.row_no
, 1+um.row_no as row_no_v
, um.hin_cd
, um.hin_nm
, um.tok_hacyu_no
, um.hosoku2
, um.tani
, (case when('&7' = '1')then(coalesce(um.hosoku3,'')||' / '||coalesce(um.hosoku4,''))else(um.hin_nm)end) as hin_nm_s
, (case when('&7' = '1')then(dh.hin_nm)else(um.hin_cd)end) as dhin_nm
, h.size_h
, to_char(h.size_h,'FM990.0')||' '||h26.koumoku3 as kind_s
, pl.pack_seq
, pl.pack_seq_c
, pl.suryo
, (1 + pl.pack_seq_c)+(select count(*) from t_uri_pack_m pm0 where pm0.den_no = pm.den_no and pm0.pack_seq < pm.pack_seq) as pack_no_i
, ph.konpo_volume
, ph.konpo_keijyo
, h42.han_name as konpo_keijyo_nm
, pm.pack_net_weight
, pm.pack_gross_weight
, fnc_get_uri_pack_total(uh.den_no) as pack_total
from t_uri uh
inner join t_uri_pack ph on (ph.den_no = uh.den_no)
inner join t_uri_pack_m pm on (pm.den_no = ph.den_no and pm.pack_seq = ph.pack_seq)
inner join t_uri_pack_link pl on (pl.den_no = pm.den_no and pl.pack_seq = pm.pack_seq and pl.pack_seq_c = pm.pack_seq_c)
inner join t_uri_m um on (um.den_no = pl.den_no and um.row_no = pl.row_no)
left join t_uri jh on (jh.den_no = uh.jucyu_no)
left join m_hin h on(um.hin_cd = h.hin_cd)
left join m_hin dh on(dh.hin_cd = h.dhin_cd)
left join m_hanyo h26 on (h26.mst_kbn = '26' and h26.han_name = um.hosoku2)
left join m_hanyo h42 on (h42.mst_kbn = '42' and h42.han_cd = ph.konpo_keijyo)
left join m_hanyo h76 on (h76.mst_kbn = '76' and h76.han_cd = uh.pay_kbn)
where 1 = 1
and uh.jucyu_flg = 0
and uh.del_flg = 0
--and um.hosoku2 != ''
and (('&7' != '1') or (um.hosoku2 != ''))
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((uh.den_no = fnc_to_number('&3')) or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
order by den_no, pack_seq, pack_seq_c, row_no


[SelectRecords_Jisya]
select
  han_name as j_nm
, koumoku1 as j_address
, 'TEL: '||koumoku5||(case when(koumoku6 != '')then(' FAX: '||koumoku6)else('')end)||(case when(koumoku8 != '')then(' E-MAIL: '||koumoku8)else('')end) as j_tel_fax
from m_hanyo
where mst_kbn = '90' and han_cd = 'jisyak'

[selectDaihyoItems]
select distinct mhd.hin_cd, mhd.hin_nm, mhd.biko1, mhd.sort_key
from t_uri_m um
inner join m_hin mh on (mh.hin_cd = um.hin_cd)
inner join m_hin mhd on (mhd.hin_cd = mh.dhin_cd)
where den_no = &1
and um.hosoku2 != ''
order by mhd.sort_key, mhd.hin_cd




