set nocount on
declare @fbdate datetime,
@fedate datetime,
@fbsupnum nvarchar,
@fesupnum nvarchar,
@fbillno nvarchar
set @fbdate=’********’
set @fedate=’########’
set @fbillno=‘FBillNoCommon’
set @fbsupnum=‘SuppNo’
set @fesupnum=’#SuppNo#’
select a.fbillno as fpobillno ,
a.fdate as fpodate , a.fcheckdate as fpocheckdate ,
c.fnumber as fsupnumber ,c.fname as fsupname ,
d.fname as fusename,
e.fnumber as fbmnumber ,e.fname as fbmname ,
b.finterid,b.fentryid ,
f.fnumber as fwlnumber , f.fname as fwlname , f.fmodel as fwlmodel ,
b.fqty as fpoqty , b.fstockqty as fpostockqty , dateadd(dd,-3,b.fdate) as fyjdate,b.fdate as fporedate ,
b.fsourcebillno as fsebillno , b.fentryselfp0267 as fplredate
into #temp01
from poorder a
left join poorderentry b on b.finterid=a.finterid
left join t_supplier c on c.fitemid=a.fsupplyid
left join t_user d on d.fuserid=a.fbillerid
left join t_department e on e.fitemid=a.fdeptid
left join t_icitem f on f.fitemid=b.fitemid
where a.fcancellation=‘0’ and
b.fqty>b.fstockqty and
(a.fdate>=’********’ or @fbdate=’’ ) and
( a.fdate<=’########’ or @fedate=’’ ) AND
(a.fbillno LIKE ‘%FBillNoCommon%’ OR @fbillno=’’) and
(c.fnumber=‘SuppNo’ or @fbsupnum=’’ )and
(c.fnumber=’#SuppNo#’ or @fesupnum=’’) and
(a.fbillno LIKE ‘%FBillNoCommon%’ OR @fbillno=’’)
select max(c.fdate) as fmaxstdate ,
f.fnumber as fsupnumber , f.fname as fsupname ,
d.forderinterid ,d.forderentryid,
e.fnumber as fwlnumber , e.fname as fwlname ,e.fmodel as fwlmodel
into #temp02
from icstockbill c
left join icstockbillentry d on d.finterid=c.finterid
left join t_icitem e on e.fitemid=d.fitemid
left join t_supplier f on f.fitemid=c.fsupplyid
where c.ftrantype=‘1’
group by f.fnumber , f.fname ,
d.forderinterid ,d.forderentryid,
e.fnumber , e.fname ,e.fmodel
select a.fpobillno , a.fpodate ,a.fpocheckdate , a.fsupnumber , a.fsupname , a.fbmname , a.fusename ,
a.fwlnumber , a.fwlname , a.fpoqty , a.fpostockqty ,a.fporedate , dateadd(dd,-3,a.fporedate) as fyjdate ,a.fplredate ,
b.fmaxstdate , a.fsebillno
from #temp01 a
left join #temp02 b on b.forderinterid=a.finterid and b.forderentryid=a.fentryid
ORDER BY a.fpobillno , a.fpodate
drop table #temp01
drop table #temp02
