22,207
社区成员
发帖
与我相关
我的任务
分享
select a.ncrb_no, a.matl_tp, a.matl_gb, case a.iqc_stat when '1' then 'Approval Waiting' when '2' then 'drafting/decision'
when '3' then 'approval Complete' when '4' then 'Return' else 'approval Wait' end iqc_stat, a.iqc_stat iqc_stat2,
a.matl_cd, c.matl_nm , a.iqc_no ,
z.user_name,
substring(a.iqc_no,1,4)+substring(a.iqc_no,6,8) iqc_no2, isnull(f.incom_no,'') incom_no,
substring(a.recv_dt,1,4)+'/'+substring(a.recv_dt,5,2)+'/'+
substring(a.recv_dt,7,2) recv_dt,
a.incom_qty, d.unit,
substring(a.result_dtm,1,4)+'/'+substring(a.result_dtm,5,2)+'/'+substring(a.result_dtm,7,2) result_dt,
substring(a.result_dtm,9,2)+':'+substring(a.result_dtm,11,2)+':'+substring(a.result_dtm,13,2) result_tm,
b.vend_nm,
case when a.result = 'A' then 'Accept' when a.result = 'R' and a.re_judge = 'Y' then 'Reject [Waive]' when a.result = 'R' and a.re_judge is null then 'Reject' when a.result = 'W' then 'Waive' else null end result_nm,
substring(a.life_time,1,4)+'/'+substring(a.life_time,5,2)+'/'+
substring(a.life_time,7,2) life_time, a.iqc_insp_id,
e.user_name iqc_insp_nm,
a.spec_tp, a.mgrp_cd, a.vend_cd ,a.result,
case isnull(y.veri_nm, 'NULL') when 'NULL' then case x.defect_cn when 0 then null else (case when a.result = 'A' then '' else x.para_nm end) end else y.veri_nm end para_nm, case when a.result = 'R' then x.defect_cn else '0' end defect_cn,
sign( convert( int, getdate(), 112) - convert(int, convert( datetime, life_time, 112 ), 112 ) ) over_due,
f.urgent,
a.remarks,
case
when t3.ulm_gb='U' then cast(t3.u_qty as varchar)+' '+ t3.u_unit+' / '+
case
when t3.u_ls_gb='S' then 'Shipment'
else 'Lot'
end
when t3.ulm_gb='L' then 'LTPD '+t3.l_ratio+'% C='+t3.l_cnt
when t3.ulm_gb='M' then t3.m_insp_lvl+', '+t3.m_insp_gb+'/'+t3.m_insp_mth+', AQL '+t3.aql+'%'
end sam_rule
from iqc_iqc_mst a LEFT OUTER JOIN (select a.iqc_no, max(a.defect_cn) defect_cn,
substring(max(str(isnull(a.defect_cn, 0), 10)+a.para_cd), 11, 8) para_cd,
substring(max(str(isnull(a.defect_cn, 0), 10)+b.para_nm), 11, 20) para_nm
from iqc_calc_result a,
iqc_para b
where b.para_cd = a.para_cd
group by a.iqc_no) x ON x.iqc_no = a.iqc_no
LEFT OUTER JOIN (select distinct a.iqc_no, b.veri_nm
from iqc_veri_result a, iqc_veri b
where b.veri_cd = a.veri_cd
and a.result = 'R'
) y ON y.iqc_no = a.iqc_no
LEFT OUTER JOIN iqc_req_insp f ON f.iqc_no = a.iqc_no
LEFT OUTER JOIN
(select t1.Iqc_no, t1.ulm_gb, t1.u_qty, t1.u_unit, t1.u_ls_gb, t1.l_ratio, t1.m_insp_lvl, t1.m_insp_gb, t1.m_insp_mth, t1.aql, t1.l_cnt
from iqc_insp_size t1, iqc_pgrp t2
where t1.pgrp_cd=t2.pgrp_cd
and t2.Visual_yn='1'
) t3 on t3.Iqc_no=a.iqc_no,
(select a.user_name, b.iqc_no
from qis_user a, iqc_apprv b
where a.user_id = b.user_id
and b.odr = 0)z ,
iqc_vendor b, iqc_vendor_matl c,iqc_matl d,
qis_user e
where a.result_dtm between case a.loc_cd when 'H' then N'20120324070000' else N'20120324070000' end
and case a.loc_cd when 'H' then N'20120325065959' else N'20120325065959' end
and a.loc_cd in ('H')
and b.vend_cd = a.vend_cd
and a.mgrp_cd = N'CA'
and a.matl_gb in ('P')
and a.result in (N'A', N'R', N'W')
and c.matl_cd = a.matl_cd
and c.vend_cd = a.vend_cd
and a.matl_cd = d.matl_cd
and e.user_id = a.iqc_insp_id
and z.iqc_no = a.iqc_no
order by a.iqc_no
and e.user_id = a.iqc_insp_id
and z.iqc_no = a.iqc_no