[selectRecords]
select
--製造指示
  t.shiji_no
, t.input_ymd
, t.syori_ymd
, t.jucyu_no
, t.jucyu_row_no + 1 as jucyu_row_no
, t.suryo
, t.seizou_tan_cd
, t.sekkei_tan_cd
, t.eigyou_tan_cd
, t.kikaku_tan_cd
, fnc_get_report_multiline(t.sekkei_biko) as sekkei_biko
, t.mock_flg
, (case when(t.mock_flg = 1)then('有')else('無')end) as mock_flg_nm
--受注
, uri.biko2
, uri.tok_nm
, (case when(coalesce(coalesce(uri.nonyu_ymd,um.nonyu_ymd),'') != '')then(to_timestamp(coalesce(coalesce(uri.nonyu_ymd,um.nonyu_ymd),''),'yyyymmdd'))else(null)end) as nonyu_ymd
, (case when(coalesce(um.syotei_ymd,'') != '')then(to_timestamp(coalesce(um.syotei_ymd,''),'yyyymmdd'))else(null)end) as syotei_ymd
, uri.den_kbn
, h54.han_name as den_kbn_nm
, uri.nohinsyo_kbn
, h46.han_name as nohinsyo_kbn_nm
--受注明細
, um.hin_nm as uri_hin_nm
, um.koyu_nm
, um.meisai_biko
, um.konpo_biko
, um.jucyu_su
--#19577 2021.08.15 MOD STT
--, fnc_get_3dim_size_nm(um.size_l, um.size_w, um.size_h) as size
, (case when(um.size_free != '')then(um.size_free)else(fnc_get_3dim_size_nm(um.sunpo_kbn, um.size_l, um.size_w, um.size_h))end) as size
--#19577 2021.08.15 MOD END
, um.hosoku3
, um.hosoku4
, um.nonyu_hoho
, h48.han_name as nonyu_hoho_nm
, um.skonpo_keijyo
, h47.han_name as skonpo_keijyo_nm
--担当者マスタ
, '' as tan_nm
, tan1.tan_nm as sekkei_nm
, tan2.tan_nm as eigyou_nm
, uri.kanri_no
, h99a.han_name as rep_mode
, (case when(h99a.koumoku2 != '')then(h99a.koumoku2)else('製造手順書')end) as rep_title
from t_seizou t
left join t_uri uri on t.jucyu_no = uri.den_no and uri.jucyu_flg = 1
left join t_uri_m um on t.jucyu_no = um.den_no and t.jucyu_row_no = um.row_no
left join m_tanto tan1 on t.sekkei_tan_cd = tan1.tan_cd
left join m_tanto tan2 on t.eigyou_tan_cd = tan2.tan_cd
left join m_hin hin on hin.hin_cd = um.hin_cd
left join m_hanyo h46 on (h46.mst_kbn = '46' and h46.han_cd = uri.nohinsyo_kbn)
left join m_hanyo h47 on (h47.mst_kbn = '47' and h47.han_cd = um.skonpo_keijyo)
left join m_hanyo h48 on (h48.mst_kbn = '48' and h48.han_cd = um.nonyu_hoho)
left join m_hanyo h54 on (h54.mst_kbn = '54' and h54.han_cd = uri.den_kbn)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'mode-r0130')
where 1 = 1
and t.shiji_no = &2
order by shiji_no


[selectRecords_K]
select sm.koutei_no, sm.koutei_cd, sm.koutei_nm, sm.seizou_biko
from t_seizou_m sm
where 1 = 1
and sm.shiji_no = &2
order by koutei_no


[selectRecords_Z]
select 
--シート
0 as kind
--材料
, tsz.row_no, tsz.hin_cd, tsz.hin_nm, tsz.suryo as hin_suryo, tsz.tanka, tsz.kingaku, tsz.sir_cd, tsz.sir_nm, tsz.size_l, tsz.size_w, tsz.size_h, tsz.color_cd, tsz.color_nm
, sm.koutei_nm
, fnc_get_3dim_size_ryaku(tsz.size_l, tsz.size_w, tsz.size_h) as size_s
, hm.hacyu_su
, (case when(hm.nyuka_yotei_ymd != '')then(to_char(to_date(hm.nyuka_yotei_ymd,'YYYYMMDD'),'MM/DD'))else('')end) as nyuka_yotei_ymd
from t_shiyou_zairyo tsz
left join t_seizou_m sm on (sm.shiji_no = tsz.shiji_no and sm.koutei_no = tsz.koutei_no)
left join t_sir_m hm on (hm.den_no = tsz.hacyu_no and hm.row_no = tsz.hacyu_row_no)
left join m_hin hin on (hin.hin_cd = tsz.hin_cd)
left join m_hanyo ha9 on (ha9.mst_kbn = 'A9' and ha9.han_cd = hin.ext_cat2_cd)
where 1 = 1
and tsz.shiji_no = &2
--and hin.cat_cd = '901' or hin.cat_cd = '003' or hin.cat_cd = '004'
and ha9.koumoku1 = '1'
union all
select 
--シート以外
1 as kind
--材料
, tsz.row_no, tsz.hin_cd, tsz.hin_nm, tsz.suryo as hin_suryo, tsz.tanka, tsz.kingaku, tsz.sir_cd, tsz.sir_nm, tsz.size_l, tsz.size_w, tsz.size_h, tsz.color_cd, tsz.color_nm
, sm.koutei_nm
, fnc_get_3dim_size_ryaku(tsz.size_l, tsz.size_w, tsz.size_h) as size_s
, hm.hacyu_su
, (case when(hm.nyuka_yotei_ymd != '')then(to_char(to_date(hm.nyuka_yotei_ymd,'YYYYMMDD'),'MM/DD'))else('')end) as nyuka_yotei_ymd
from t_shiyou_zairyo tsz
left join t_seizou_m sm on (sm.shiji_no = tsz.shiji_no and sm.koutei_no = tsz.koutei_no)
left join t_sir_m hm on (hm.den_no = tsz.hacyu_no and hm.row_no = tsz.hacyu_row_no)
left join m_hin hin on (hin.hin_cd = tsz.hin_cd)
left join m_hanyo ha9 on (ha9.mst_kbn = 'A9' and ha9.han_cd = hin.ext_cat2_cd)
where 1 = 1
and tsz.shiji_no = &2
--and hin.cat_cd != '901' or hin.cat_cd != '003' or hin.cat_cd != '004'
and COALESCE(ha9.koumoku1,'') != '1'
order by row_no