--这只是一条查询语句。(
select
'' item41
, decode(a.shiage_tenkai,'1','disabled','5','disabled','') item37
, decode(decode(b.s_seihin_kb,'s',a.press_tenkai,a.shiage_tenkai),'0','未展開','2','未展開','1','展開済','5','展開済','3','展開','未展開') item42
, b.hinmei item43
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.jutyu_butsuryo , b.juchu_suryo) ,0) item44
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item45
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item47
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item49
, '手動引当' item50
, '' item60
, decode( b.s_seihin_kb, 'm', nvl(hin.seizouhinmei,' '),
nvl(a.shiage_seizouhinmei, siage.shiage_seizouhinmei)
) item61
, decode( b.s_seihin_kb, 'm', nvl(hin.pack_hin ,' '),
nvl(a.shiage_pack_hinmei, siage.shiage_pack_hin)
) item188
, nvl(a.shiage_torisu ,siage.shiage_kite_kom ) item62
, decode( b.s_seihin_kb, 's', nvl(hin.seizouhinmei,' '),
nvl(a.press_seizouhinmei,press.press_seizouhinmei)
) item63
, decode( b.s_seihin_kb, 's', nvl(hin.pack_hin ,' '),
nvl(a.press_pack_hinmei , press.press_pack_hin)
) item189
, nvl( a.press_teijaku_size, press.press_kite_kom ) item64
, nvl(a.shiage_tenkai_hitsuyo , nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0)) item65
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item66
, 'プレス展開' item67
, to_char(a.shiage_tonyu_ymd,'yy/mm/dd') item68
, a.press_hitsuyo item69
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item70
, '仕上展開' item71
, to_char( decode( a.press_ymd, null, b.noki_base_press_ymd,
decode( sign(to_char(a.press_ymd,'hh24mi')-'0800'),-1,a.press_ymd-1,a.press_ymd )
), 'rr/mm/dd' ) item72
, a.press_kai item73
, nvl(a.press_jikan,'08:00') item74
, nvl( a.press_setsubiname , press.press_setsubiname ) item75
, nvl( a.press_teijaku_size, press.press_kite_kom ) item76
, to_char(a.tokou_yokyuu_ymd,'yy/mm/dd hh24:mi') item77
, to_char(a.tokou_press_ymd,'yy/mm/dd') item78
, b.ukeban item79
, b.chumonnushi item80
, decode(nvl(b.noki,''),'','',decode(sign(to_number(substr(b.noki,length(b.noki)-1))-31),1, substr(b.noki,3,2)||'/'||substr(b.noki,5,2)||'/'||substr(b.noki,length(b.noki)-1),to_char(to_date(b.noki,'yyyy/mm/dd'),'yy/mm/dd'))) item81
, to_char(b.uketsuke_ymd,'yy/mm/dd') item82
, '0' item83
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item84
, nvl(0,0) item85
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item86
, nvl(0,0) item87
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item88
, '' item89
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item90
解决方案 »
- Oracle 安装问题！
- 数据库恢复
- 用PowerDesigner生成oracle数据库表问题
- 一个简单的表创建不了
- 如何把excel文件数据导入到oracle数据库
- 怎样恢复Oracle数据（没有归档）
- 关于.Net中开发Oracle数据库ExecuteNonQuery的返回值问题！！！！
- 除了关系数据库外，听说还有个实时数据库（内存数据库），是啥呀？讨论讨论！
- 求解一个sql,这种判断怎么写啊？求大神帮助
- 在无法将应用的代码修改为绑定变量情况下,oracle如何绑定变量
- 在往分了区的表中添加记录时总是提示“这类索引的分区处于不可用状态”
- oracle中的多媒体数据如果不断递增，多大之后，会遇到备份，存储等各方面的问题，请问大家是如何解决的
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou1,' '), nvl( a.shiage_shiyou1, siage.shiage_shiyou1 ) )
item100
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou2,' '), nvl( a.shiage_shiyou2, siage.shiage_shiyou2 ) )
item101
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou3,' '), nvl( a.shiage_shiyou3, siage.shiage_shiyou3 ) )
item102
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou1,' '), nvl( a.press_shiyou1, press.press_shiyou1 ) )
item103
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou2,' '), nvl( a.press_shiyou2, press.press_shiyou2 ) )
item104
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou3,' '), nvl( a.press_shiyou3, press.press_shiyou3 ) )
item105
, decode(nvl(a.shiage_hyojun_budomari , siage.shiage_budomari),'',100,0,100,nvl(a.shiage_hyojun_budomari , siage.shiage_budomari)) item106
, '%' item107
, nvl(decode(nvl(a.shiage_henko_budomari,nvl(a.shiage_hyojun_budomari , siage.shiage_budomari)),0,a.shiage_hyojun_budomari,
nvl(a.shiage_henko_budomari,nvl(a.shiage_hyojun_budomari , siage.shiage_budomari))),0) item108
, '%' item109
, nvl(to_char(a.shiage_ymd,'hh24:mi'),'08:00')
item110
, nvl(a.press_hyoujun_budomari , press.press_budomari) item111
, '%' item112
, nvl(decode(nvl(a.press_henko_budomari, nvl(a.press_hyoujun_budomari , press.press_budomari)),0,a.press_hyoujun_budomari,
nvl(a.press_henko_budomari, nvl(a.press_hyoujun_budomari , press.press_budomari))),0) item113
, '%' item114
, a.press_keikaku_bikou item115
, a.press_size item116
, to_char(a.tokou_kakutei_ymd,'yy/mm/dd hh24:mi') item117
, a.tokou_press_kai item118
, a.tokou_press_jikan item119
, decode(b.tokkyu_kb,'0','普通','1','特急','2','超特急','普通') item120
, decode(b.zaikokb,'a','仕込品','b','一時仕込品','c','営業見込品','d','受託品','e','営業申請仕込品','f','超完品','g','内示品','h','注文品','j','本社外注','k','預託品','l','売未品','m','貸付品','n','未着戻り品','p','常備半製品','s','現場材料','t','特殊治工具','r','積送品','q','非常備半製品','z','薄外品') item121
, b.juyouka item122
, to_char(nvl(c.ukeki,b.ukeki_ymd),'yy/mm/dd') item123
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item124
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0) item125
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item126
, b.bikou item127
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0) item128
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item129
, nvl(nvl( a.shiage_seizouspcno, siage.shiage_siyo_no )
, (select shiage_seizouspcno from dat_mcl_tehai_sub
where jigyoushocode = 'f'
and length(jigyoushocode) = 1
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(ukeban) < 8
and rownum = 1
)
) item139
, decode(nvl(a.shiage_seizoubumon ,nvl(b.shiage_seizou_bumon,'')),'u0','エポキシ板','t0','タソウ板') item140
, nvl( a.shiage_kokyaku, siage.shiage_juryaku ) item141
, nvl(nvl( a.press_seizouspcno, press.press_siyo_no )
, (select press_seizouspcno from dat_mcl_tehai_sub
where jigyoushocode = 'f'
and length(jigyoushocode) = 1
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(ukeban) < 8
and rownum = 1
)
) item142
, decode(nvl(a.press_seizoubumon ,nvl(b.press_seizou_bumon,'')),'u0','エポキシ板','t0','タソウ板') item143
, nvl( a.press_kokyaku, press.press_juryaku ) item144
, nvl(a.shiage_riron_tonyu , nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0)) item145
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item146
, nvl( a.shiage_jikko_tonyu, nvl( decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou,b.tehaisuryo) ,0)*nvl(siage.shiage_budomari,100)/100 )
item147
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item148
, nvl(a.shiage_tyokan_mikomi , 0) item149
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item150
, nvl(a.press_hyoujun_tonyu , 0) item151
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item152
, nvl(a.press_jikkou_tonyu , 0) item153
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item154
, nvl(a.press_shiryo,0) item155
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item156
, nvl(a.press_tyokan_mikomi,0) item157
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item158
, decode(nvl(a.press_size_su,0),0,decode(nvl(nvl(a.press_teijaku_torisu ,b.press_torisu),0) ,0 ,0 ,nvl(a.press_jikkou_tonyu,0) / nvl(a.press_teijaku_torisu ,b.press_torisu)),a.press_size_su) item159
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item160
, nvl(a.press_teijaku_torisu ,b.press_torisu) item161
, b.sakuban item162
, nvl(a.tokou_press_keikaku_tien , (a.tokou_kakutei_ymd - a.tokou_yokyuu_ymd)*24 ) item163
, b.sakuban item164
, b.ukeban item165
, c.jumot_c item166
, a.saku_edaban item167
, a.lot_no item168
, upper(b.tatene_shiji_tani) item187
, b.jigyoushocode item190
, b.kanjoucode item191
, b.yokoucode item192
, b.bumoncode item193
, b.seizou_tateya_bumon_code item194
, b.chumonnushi item195
, b.juyouka item196
, b.hinmeicode item197
, b.pack_hinmei item198
, nvl( a.shiage_setsubiname, siage.shiage_setsubiname ) item204
, nvl( a.shiage_setsubicode, siage.shiage_setsubicode ) item205
, nvl(a.shiage_sagyoukanousu ,0) item206
, nvl( a.shiage_seizoutani, siage.shiage_seizoutani ) item207
, nvl( a.press_setsubiname, press.press_setsubiname ) item208
, nvl( a.press_setsubicode, press.press_setsubicode ) item209
, nvl(a.press_sagyoukanousu ,0) item210
, nvl( a.press_seizoutani, press.press_seizoutani ) item211
, a.shiage_bikou item212
, a.press_bikou item213
, siage.shiage_seizoutanitani item214
, press.press_seizoutanitani item215
, decode(a.press_tenkai,'5','5',a.shiage_tenkai) item216
, b.s_seihin_kb item219
where jigyoushocode = 'f'
and length(jigyoushocode) = 1
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(ukeban) < 8
and nvl(hikiate_flg,'0') not in ('1')
and (nvl(shiage_tenkai_hitsuyo,0)+ nvl(press_hitsuyo,0))!=0
) a,
dat_sekisou_tehai b,
ord_jzanmain c,
( select a.pack_hin shiage_pack_hin
, d.szsy_no shiage_siyo_no
, a.seizouhinmei shiage_seizouhinmei
, a.shiyou1 shiage_shiyou1
, a.shiyou2 shiage_shiyou2
, a.shiyou3 shiage_shiyou3
, d.juryaku shiage_juryaku
, h.budomari shiage_budomari
, nvl(g.kite_kom,'1') shiage_kite_kom
, d.stub_c shiage_setsubicode
, c.setsubiname shiage_setsubiname
, nvl(d.kn_ryo,1) shiage_seizoutani
, d.kntani shiage_seizoutanitani
from hinme_m a
, ( select distinct a.setsubiguncode setsubicode, a.setsubigunname setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
union
select distinct b.setsubicode, b.setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
) c
, view_ksh_siyo_kyotu d
, view_ksh_siyo_s_naiyou g
, ( select budomari,spc_no from std_hyojun
where kanricode(+) = 'cmcl外観検査'
and ki_y(+) = decode(to_char(sysdate, 'mm'),'01',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy') ,'02',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),'03',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),to_char(sysdate, 'yyyy'))
and ki_kb(+) = decode(to_char(sysdate, 'mm'), '01',1,'02',1,'03',1,'04',0,'05',0,'06',0,'07',0,'08',0,'09',0,'10',1,'11',1,'12',1,'')
and spc_no in (' ')
) h
and d.pack_hin in (' ','pe67wk1.08x514x1033n70d')
and a.pack_hin in (' ','pe67wk1.08x514x1033n70d')
and d.pack_hin = a.pack_hin
and a.kojyo_c = 'f'
and a.kanjyo_c in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and d.szsy_no like '3%'
and d.szsy_no = h.spc_no(+)
and d.szsy_no = g.szsy_no(+)
and d.stub_c = c.setsubicode(+)
and g.dai_kom(+) = '取数'
and d.szsy_no in (' ')
) siage
, ( select distinct
a.pack_hin press_pack_hin
, d.szsy_no press_siyo_no
, a.seizouhinmei press_seizouhinmei
, a.shiyou1 press_shiyou1
, a.shiyou2 press_shiyou2
, a.shiyou3 press_shiyou3
, d.juryaku press_juryaku
, h.budomari press_budomari
, d.stub_c press_setsubicode
, c.setsubiname press_setsubiname
, nvl(d.kn_ryo,1) press_seizoutani
, d.kntani press_seizoutanitani
, nvl(g.syo_kom,' ') press_kite_kom
from hinme_m a
, ( select distinct a.setsubiguncode setsubicode, a.setsubigunname setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
union
select distinct b.setsubicode, b.setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
) c
, view_ksh_siyo_kyotu d
, view_ksh_siyo_s_naiyou g
, ( select budomari,spc_no from std_hyojun
where kanricode(+) = 'cプレス'
and ki_y(+) = decode(to_char(sysdate, 'mm'),'01',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy') ,'02',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),'03',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),to_char(sysdate, 'yyyy'))
and ki_kb(+) = decode(to_char(sysdate, 'mm'), '01',1,'02',1,'03',1,'04',0,'05',0,'06',0,'07',0,'08',0,'09',0,'10',1,'11',1,'12',1,'')
and spc_no in (' ')
) h
and d.pack_hin = a.pack_hin
and a.kojyo_c = 'f'
and a.kanjyo_c in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and d.szsy_no like '3%'
and d.szsy_no = h.spc_no(+)
and d.szsy_no = g.szsy_no(+)
and g.dai_kom(+) = '定尺サイズ'
and d.stub_c = c.setsubicode(+)
and d.szsy_no in (' ')
) press
, ( select max(a.ukeki) ukeki ,
min(a.ukeki) min_ukeki ,
a.kanjyo_c kanjyo_c ,
a.jgyb_c jgyb_c ,
a.pack_hinme pack_hinme,
a.kanrino kanrino ,
a.jumot_c jumot_c
from ukeki_bunkatu a
where a.jgyb_c = 'f'
and a.kanjyo_c in (select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(a.jgyb_c) = 1
and length(a.kanjyo_c) = 1
and a.pack_hinme in ('pe67wk1.08x514x1033n70d')
group by a.jgyb_c, a.kanjyo_c,
a.pack_hinme,a.kanrino,
a.jumot_c
) ukeki
, hinme_m hin
where 1 = 1
and b.pack_hinmei in ('pe67wk1.08x514x1033n70d')
and b.jigyoushocode = a.jigyoushocode(+)
and b.kanjoucode = a.kanjoucode(+)
and b.bumoncode = a.bumoncode(+)
and b.yokoucode = a.yokoucode(+)
and b.ukeban = a.ukeban(+)
and b.sakuban = a.sakuban(+)
and b.jigyoushocode = c.kojo_c(+)
and b.ukeban = c.juchu_no(+)
and b.pack_hinmei = c.pack_hinme(+)
and b.jigyoushocode = hin.kojyo_c(+)
and b.pack_hinmei = hin.pack_hin(+)
and b.jigyoushocode = ukeki.jgyb_c(+)
and b.jumotcode = ukeki.jumot_c(+)
and b.ukeban = ukeki.kanrino(+)
and b.kanjoucode = ukeki.kanjyo_c(+)
and b.pack_hinmei = ukeki.pack_hinme(+)
and b.seihin_spcno = siage.shiage_siyo_no(+)
and b.press_spcno = press.press_siyo_no(+)
and ( b.s_seihin_kb = 'm' or b.s_seihin_kb = 's' )
and ( nvl(b.zaikokb,' ') != 'a' or (nvl(b.zaikokb,' ') = 'a' and nvl(b.shikomitehai,' ') = '0' ))
and b.jigyoushocode = 'f'
and nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0) !=0
and b.kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and b.s_seihin_kb in (
'm' )
and b.juri_kb = '1'
and to_date(b.uketsuke_ymd,'rr/mm/dd') between to_date('05/08/14','rr/mm/dd') and to_date('08/09/14','rr/mm/dd')
and b.ukeban in (' ','0za00r0')
and nvl(b.press_spcno,' ') in (' ')
and nvl(b.seihin_spcno,' ') in (' ')
and nvl(b.zaikokb,' ') not in ('1','2','3','4')
and length(b.jigyoushocode) = 1
and length(b.ukeban) < 8
)
select
'' item41
, decode(a.shiage_tenkai,'1','disabled','5','disabled','') item37
, decode(decode(b.s_seihin_kb,'s',a.press_tenkai,a.shiage_tenkai),'0','未展開','2','未展開','1','展開済','5','展開済','3','展開','未展開') item42
, b.hinmei item43
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.jutyu_butsuryo , b.juchu_suryo) ,0) item44
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item45
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item47
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item49
, '手動引当' item50
, '' item60
, decode( b.s_seihin_kb, 'm', nvl(hin.seizouhinmei,' '),
nvl(a.shiage_seizouhinmei, siage.shiage_seizouhinmei)
) item61
, decode( b.s_seihin_kb, 'm', nvl(hin.pack_hin ,' '),
nvl(a.shiage_pack_hinmei, siage.shiage_pack_hin)
) item188
, nvl( a.shiage_torisu, siage.shiage_kite_kom ) item62
, decode( b.s_seihin_kb, 's', nvl(hin.seizouhinmei,' '),
nvl(a.press_seizouhinmei, press.press_seizouhinmei)
) item63
, decode( b.s_seihin_kb, 's', nvl(hin.pack_hin ,' '),
nvl(a.press_pack_hinmei, press.press_pack_hin)
) item189
, nvl( a.press_teijaku_size, press.press_kite_kom ) item64
, nvl(a.shiage_tenkai_hitsuyo , nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0)) item65
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item66
, 'プレス展開' item67
, to_char(a.shiage_tonyu_ymd,'yy/mm/dd') item68
, nvl(a.press_hitsuyo,0) item69
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item70
, '仕上展開' item71
, to_char( decode( a.press_ymd, null, b.noki_base_press_ymd,
decode( sign(to_char(a.press_ymd,'hh24mi')-'0800'),-1,a.press_ymd-1,a.press_ymd )
), 'rr/mm/dd' ) item72
, a.press_kai item73
, nvl(a.press_jikan,'08:00') item74
, nvl( a.press_setsubiname , press.press_setsubiname) item75
, nvl( a.press_teijaku_size, press.press_kite_kom ) item76
, to_char(a.tokou_yokyuu_ymd,'yy/mm/dd hh24:mi') item77
, to_char(a.tokou_press_ymd,'yy/mm/dd') item78
, b.ukeban item79
, b.chumonnushi item80
, decode(nvl(b.noki,''),'','',decode(sign(to_number(substr(b.noki,length(b.noki)-1))-31),1, substr(b.noki,3,2)||'/'||substr(b.noki,5,2)||'/'||substr(b.noki,length(b.noki)-1),to_char(to_date(b.noki,'yyyy/mm/dd'),'yy/mm/dd'))) item81
, to_char(b.uketsuke_ymd,'yy/mm/dd') item82
, '0' item83
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item84
, nvl(0,0) item85
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item86
, nvl(0,0) item87
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item88
, '' item89
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item90
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou1,' '), nvl( a.shiage_shiyou1, siage.shiage_shiyou1 ) )
item100
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou2,' '), nvl( a.shiage_shiyou2, siage.shiage_shiyou2 ) )
item101
, decode( b.s_seihin_kb, 'm', nvl(hin.shiyou3,' '), nvl( a.shiage_shiyou3, siage.shiage_shiyou3 ) )
item102
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou1,' '), nvl( a.press_shiyou1, press.press_shiyou1 ) )
item103
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou1,' '), nvl( a.press_shiyou1, press.press_shiyou1 ) )
item104
, decode( b.s_seihin_kb, 's', nvl(hin.shiyou1,' '), nvl( a.press_shiyou1, press.press_shiyou1 ) )
item105
, '%' item107
, nvl(decode(nvl(a.shiage_henko_budomari , nvl(a.shiage_hyojun_budomari , siage.shiage_budomari)),0,a.shiage_hyojun_budomari
, nvl(a.shiage_henko_budomari , nvl(a.shiage_hyojun_budomari , siage.shiage_budomari))),0) item108
, '%' item109
, nvl(to_char(a.shiage_ymd,'hh24:mi'),'08:00') item110
, nvl( a.press_hyoujun_budomari, press.press_budomari ) item111
, '%' item112
, nvl(decode(nvl(press_henko_budomari , nvl(a.press_hyoujun_budomari , press.press_budomari)),0,a.press_hyoujun_budomari,nvl(a.press_henko_budomari
, nvl(a.press_hyoujun_budomari , press.press_budomari))),0) item113
, '%' item114
, a.press_keikaku_bikou item115
, a.press_size item116
, to_char(a.tokou_kakutei_ymd,'yy/mm/dd hh24:mi') item117
, a.tokou_press_kai item118
, a.tokou_press_jikan item119
, decode(b.tokkyu_kb,'0','普通','1','特急','2','超特急','普通') item120
, decode(b.zaikokb,'1','他部門受注品','2','仕込手配品','3','試作投入品') item121
, b.juyouka item122
, to_char(nvl(c.ukeki,b.ukeki_ymd),'yy/mm/dd') item123
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item124
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0) item125
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item126
, b.bikou item127
, nvl(decode(upper(b.tatene_shiji_tani),'x',b.jutyu_butsuryo , b.juchu_suryo) ,0) item128
, decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku ) item129
, nvl(nvl( a.shiage_seizouspcno, siage.shiage_siyo_no )
, (select shiage_seizouspcno from dat_mcl_tehai_sub
where jigyoushocode = 'f'
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(ukeban) < 8
and rownum = 1
) )item139
, decode(nvl(a.shiage_seizoubumon ,nvl(b.shiage_seizou_bumon,'')),'u0','エポキシ板','t0','タソウ板') item140
, nvl( a.shiage_kokyaku, siage.shiage_juryaku ) item141
, nvl(nvl( a.press_seizouspcno, press.press_siyo_no )
, (select press_seizouspcno from dat_mcl_tehai_sub
where jigyoushocode = 'f'
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(ukeban) < 8
and rownum = 1
) )item142
, decode(nvl(a.press_seizoubumon,nvl(b.press_seizou_bumon,'')),'u0','エポキシ板','t0','タソウ板') item143
, nvl( a.press_kokyaku , press.press_juryaku ) item144
, nvl(a.shiage_riron_tonyu , nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0)) item145
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item146
, nvl( a.shiage_jikko_tonyu, nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou,b.tehaisuryo) ,0)*nvl(siage.shiage_budomari,100)/100 )
item147
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item148
, nvl(a.shiage_tyokan_mikomi,0) item149
, nvl(a.shiage_tenkai_hitsuyo_tani,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item150
, nvl(a.press_hyoujun_tonyu,0) item151
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item152
, nvl(a.press_jikkou_tonyu,0) item153
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item154
, nvl(a.press_shiryo,0) item155
, nvl(a.press_tyokan_mikomi,0) item157
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item158
, nvl(a.press_size_su, decode(nvl(nvl(a.press_teijaku_torisu ,b.press_torisu),0) ,0 ,0 ,nvl(a.press_jikkou_tonyu,0) / nvl(a.press_teijaku_torisu ,b.press_torisu))) item159
, nvl(a.press_hitsuyo_tani ,decode(upper(b.tatene_shiji_tani), 'x' , b.butsuryoutaniryaku , b.suryotaniryaku )) item160
, nvl(a.press_teijaku_torisu ,b.press_torisu) item161
, b.sakuban item162
, nvl(a.tokou_press_keikaku_tien , (a.tokou_kakutei_ymd - a.tokou_yokyuu_ymd)*24 ) item163
, b.sakuban item164
, b.ukeban item165
, c.jumot_c item166
, a.saku_edaban item167
, a.lot_no item168
, upper(b.tatene_shiji_tani) item187
, b.jigyoushocode item190
, b.kanjoucode item191
, b.yokoucode item192
, b.bumoncode item193
, b.seizou_tateya_bumon_code item194
, b.chumonnushi item195
, b.juyouka item196
, b.hinmeicode item197
, b.pack_hinmei item198
, nvl( a.shiage_setsubiname, siage.shiage_setsubiname ) item204
, nvl( a.shiage_setsubicode, siage.shiage_setsubicode ) item205
, nvl(a.shiage_sagyoukanousu ,0) item206
, nvl( a.shiage_seizoutani, siage.shiage_seizoutani ) item207
, nvl( a.press_setsubiname, press.press_setsubiname ) item208
, nvl( a.press_setsubicode, press.press_setsubicode ) item209
, nvl(a.press_sagyoukanousu ,0) item210
, nvl( a.press_seizoutani, press.press_seizoutani ) item211
, a.shiage_bikou item212
, a.press_bikou item213
, siage.shiage_seizoutanitani item214
, press.press_seizoutanitani item215
, decode(a.press_tenkai,'5','5',a.shiage_tenkai) item216
, b.s_seihin_kb item219
from ( select * from dat_mcl_tehai_sub
where jigyoushocode = 'f'
and juchuu_pack_hinmei in ('pe67wk1.08x514x1033n70d')
and kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and nvl(hikiate_flg,'0') not in ('1')
and (nvl(shiage_tenkai_hitsuyo,0)+ nvl(press_hitsuyo,0))!=0
and length(ukeban) < 8
) a,
dat_sekisou_tehai b,
ord_tabumon_tehai c,
( select
a.pack_hin shiage_pack_hin
, d.szsy_no shiage_siyo_no
, a.seizouhinmei shiage_seizouhinmei
, a.shiyou1 shiage_shiyou1
, a.shiyou2 shiage_shiyou2
, a.shiyou3 shiage_shiyou3
, d.juryaku shiage_juryaku
, decode(nvl(h.budomari,0),0,100,h.budomari) shiage_budomari
, nvl(g.kite_kom,'1') shiage_kite_kom
, d.stub_c shiage_setsubicode
, c.setsubiname shiage_setsubiname
, nvl(d.kn_ryo,1) shiage_seizoutani
, d.kntani
, ( select distinct a.setsubiguncode setsubicode, a.setsubigunname setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
union
select distinct b.setsubicode, b.setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
) c
, view_ksh_siyo_kyotu d
, view_ksh_siyo_s_naiyou g
, ( select budomari,spc_no from std_hyojun
where kanricode(+) = 'cmcl外観検査'
and ki_y(+) = decode(to_char(sysdate, 'mm'),'01',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy') ,'02',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),'03',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),to_char(sysdate, 'yyyy'))
and ki_kb(+) = decode(to_char(sysdate, 'mm'), '01',1,'02',1,'03',1,'04',0,'05',0,'06',0,'07',0,'08',0,'09',0,'10',1,'11',1,'12',1,'')
and spc_no in (' ')
) h
where d.prc_c = 'cmcl仕上プロセス'
and d.pack_hin in (' ','pe67wk1.08x514x1033n70d')
and a.pack_hin in (' ','pe67wk1.08x514x1033n70d')
and d.pack_hin = a.pack_hin
and a.kojyo_c = 'f'
and a.kanjyo_c in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and d.szsy_no like '3%'
and d.szsy_no = h.spc_no(+)
and d.szsy_no = g.szsy_no(+)
and d.stub_c = c.setsubicode(+)
and g.dai_kom(+) = '取数'
and d.szsy_no in (' ')
) siage
, ( select distinct
a.pack_hin press_pack_hin
, d.szsy_no press_siyo_no
, a.seizouhinmei press_seizouhinmei
, a.shiyou1 press_shiyou1
, a.shiyou2 press_shiyou2
, a.shiyou3 press_shiyou3
, d.juryaku press_juryaku
, h.budomari press_budomari
, d.stub_c press_setsubicode
, c.setsubiname press_setsubiname
, nvl(d.kn_ryo,1) press_seizoutani
, d.kntani press_seizoutanitani
, nvl(g.syo_kom,' ') press_kite_kom
, ( select distinct a.setsubiguncode setsubicode, a.setsubigunname setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
union
select distinct b.setsubicode, b.setsubiname, b.setsubikb
from mst_setsubi_gun a, mst_setsubi b
where 1 = 1
and a.koujoucode = 'f'
and length(a.koujoucode) = 1
and a.kanjoucode in ('u','t','c','d')
and length(a.kanjoucode) = 1
and a.koujoucode = b.koujoucode
and a.kanjoucode = b.kanjoucode
and a.genkabumoncode = b.genkabumoncode
and a.setsubicode = b.setsubicode
) c
, view_ksh_siyo_kyotu d
, view_ksh_siyo_s_naiyou g
, ( select budomari,spc_no from std_hyojun
where kanricode(+) = 'cプレス'
and ki_y(+) = decode(to_char(sysdate, 'mm'),'01',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy') ,'02',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),'03',to_char(add_months(to_char(sysdate,'yyyy/mm/dd'),-12),'yyyy'),to_char(sysdate, 'yyyy'))
and ki_kb(+) = decode(to_char(sysdate, 'mm'), '01',1,'02',1,'03',1,'04',0,'05',0,'06',0,'07',0,'08',0,'09',0,'10',1,'11',1,'12',1,'')
and spc_no in (' ')
) h
where d.prc_c = 'cプレスプロセス'
and d.pack_hin = a.pack_hin
and a.kojyo_c = 'f'
and a.kanjyo_c in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and d.szsy_no like '3%'
and d.szsy_no = h.spc_no(+)
and d.szsy_no = g.szsy_no(+)
and g.dai_kom(+) = '定尺サイズ'
and d.stub_c = c.setsubicode(+)
and d.szsy_no in (' ')
)
, ( select
max(a.ukeki) ukeki ,
min(a.ukeki) min_ukeki ,
a.kanjyo_c kanjyo_c ,
a.jgyb_c jgyb_c ,
a.pack_hinme pack_hinme,
a.kanrino kanrino ,
a.jumot_c jumot_c
from ukeki_bunkatu a
where a.jgyb_c = 'f'
and a.kanjyo_c in (select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and length(a.jgyb_c) = 1
and length(a.kanjyo_c) = 1
and a.pack_hinme in ('pe67wk1.08x514x1033n70d')
group by a.jgyb_c, a.kanjyo_c,
a.pack_hinme,a.kanrino,
a.jumot_c
) ukeki
, hinme_m hin
where 1 = 1
and b.jigyoushocode = 'f'
and length(b.jigyoushocode) = 1
and b.kanjoucode in ('u','t','c','d')
and length(b.kanjoucode) = 1
and b.pack_hinmei in ('pe67wk1.08x514x1033n70d')
and b.jigyoushocode = a.jigyoushocode(+)
and b.kanjoucode = a.kanjoucode(+)
and b.bumoncode = a.bumoncode(+)
and b.yokoucode = a.yokoucode(+)
and b.ukeban = a.ukeban(+)
and b.sakuban = a.sakuban(+)
and b.jigyoushocode = c.kojo_c(+)
and b.ukeban = c.juchu_no(+)
and b.pack_hinmei = c.pack_hinme(+)
and b.jigyoushocode = hin.kojyo_c(+)
and b.pack_hinmei = hin.pack_hin(+)
and b.jigyoushocode = ukeki.jgyb_c(+)
and b.jumotcode = ukeki.jumot_c(+)
and b.ukeban = ukeki.kanrino(+)
and b.kanjoucode = ukeki.kanjyo_c(+)
and b.pack_hinmei = ukeki.pack_hinme(+)
and b.seihin_spcno = siage.shiage_siyo_no(+)
and b.press_spcno = press.press_siyo_no(+)
and ( b.s_seihin_kb = 'm' or b.s_seihin_kb = 's' )
and ( nvl(b.zaikokb,' ') != 'a' or (nvl(b.zaikokb,' ') = 'a' and nvl(b.shikomitehai,' ') = '0' ))
and b.jigyoushocode = 'f'
and nvl(decode(upper(b.tatene_shiji_tani),'x',b.tehaibutsuryou , b.tehaisuryo) ,0) !=0
and b.kanjoucode in ( select kanjoucode from dat_sys_kubun_m where system_name='積層' )
and b.s_seihin_kb in (
'm' )
and b.juri_kb = '1'
and to_date(b.uketsuke_ymd,'rr/mm/dd') between to_date('05/08/14','rr/mm/dd') and to_date('08/09/14','rr/mm/dd')
and b.ukeban in (' ','0za00r0')
and nvl(b.press_spcno,' ') in (' ')
and nvl(b.seihin_spcno,' ') in (' ')
and nvl(b.zaikokb,' ') in ('1','2','3','4')
and length(b.jigyoushocode) = 1
and length(b.ukeban) < 8
)
order by
item79,item164,item42 ,item167
看了脑袋疼.就欣赏一下长度
得了.呵呵.