检品送达接收

select count(1)
from (select l.isrepeat
from lims_folders t
join base_corp p
on t.submitcorp = p.corpcode
join lims_clearanceform l
on t.clearanceid = l.clearanceid
join lims_testrequesttypes lt
on lt.testrequesttypesid = l.reqsubtypid
and lt.datalevel = '3'--为3的都是正常在用的  报告书编码规则表
left join lims_sample_programs lsp
on lsp.sampleprogramsid = t.sampleprogramsid
left join lims_domainreport ld
on ld.reqsubtypeid = l.reqsubtypid
and ld.dept = l.dept
left join lims_prodgroup lp
on lp.prodgroupid = l.inspectionitem
/* left join (select max(limit.enddate) enddate,
limit.foldersid,
max(limit.timelimits) timelimits
from (select max(t9.enddate) enddate,
t9.foldersid,
sum(timelimit) timelimits
from lims_folderstimelimit t9
group by t9.foldersid, t9.dept) limit
group by limit.foldersid) t8
on t.foldersid = t8.foldersid*/
WHERE ld.dept = 'NJIFDC' --没啥用
and l.type = (case --type保健食品,化妆品,药品,食品,食用农产品  领域字段区分食品所药品所
when (select count(1)
from (select wm_concat(r.datacode) as stp
from right_rightuser r
where r.usercode = 'hesu') t --hesu所有的科室代码
where instr(stp, 'YWKYP') > 0) > 0 then --用instr 来代替 like instr(title,'oracle’)>0 相当于like instr(title,'oracle’)=0 相当于not like
--药品业务科 YWKYP
'药品'
else
'食品'
end)
and (exists
(select 1
from lims_ordtask ot
where ot.foldersid = t.foldersid
and upper(ot.wfnodecode) = 'JPSD') or not exists --转化为大写字母 存在JPSD或者lo表里没有数据都是检品送达
(select 1
from lims_ordtask ot
where ot.foldersid = t.foldersid))--不返回结果为真，意思是把lo表没有的数据留下
and t.dept = 'NJIFDC' --没啥用都是这个
and t.category = 'food'
and (nvl(t.status, '111') = '111')) --都是空的