|
发表于 2015-6-26 15:34:46
|
显示全部楼层
复制此
create procedure [dbo].[USP_STCheckVouchDate]
(@cVouchType as varchar(5),@TableName as nvarchar(400))
as
select id into #a from rdrecord where 1=0
exec ('insert into #a select distinct id from '+@TableName)
if @cVouchType='10'
begin
select cinvcode,ddate,impoids,isnull(brelated,0) as brelated into #P
from rdrecord a with (nolock)
inner join rdrecords b with (nolock) on a.id =b.id
where a.id in (select id from #a) and isnull(impoids,0) <>0
insert into #STCheckVouchDate (cinvcode,dstdate,doridate,error)
select a.cinvcode,a.ddate,b.relsdate,
replace(
replace(
replace( e.enumname,'{0}',a.cinvcode),
'{1}',convert(varchar(10),a.ddate,121))
,'{2}',convert(varchar(10),b.relsdate,121))
from #P a inner join mom_orderdetail b with (nolock) on a.impoids=b.modid
and a.brelated=0
left join v_aa_enum e with (nolock) on e.enumtype='ST.CheckoriDate' and EnumCode='MO'
where a.ddate<b.relsdate
insert into #STCheckVouchDate (cinvcode,dstdate,doridate,error)
select a.cinvcode,a.ddate,c.relsdate,
replace(
replace(
replace( e.enumname,'{0}',a.cinvcode),
'{1}',convert(varchar(10),a.ddate,121))
,'{2}',convert(varchar(10),c.relsdate,121))
from #P a inner join mom_moallocate b with (nolock)
on a.impoids=b.AllocateId and a.brelated=1
inner join mom_orderdetail c with (nolock) on b.modid=c.modid
left join v_aa_enum e with (nolock) on e.enumtype='ST.CheckoriDate' and EnumCode='MO'
where a.ddate<c.relsdate
return
end
if @cVouchType='11'
begin
select cinvcode,ddate,impoids into #M from rdrecord a with (nolock)
inner join rdrecords b with (nolock) on a.id =b.id
where a.id in (select id from #a) and isnull(impoids,0) <>0
insert into #STCheckVouchDate (cinvcode,dstdate,doridate,error)
select a.cinvcode,a.ddate,c.relsdate,
replace(
replace(
replace( e.enumname,'{0}',a.cinvcode),
'{1}',convert(varchar(10),a.ddate,121))
,'{2}',convert(varchar(10),c.relsdate,121))
from #M a inner join mom_moallocate b with (nolock) on a.impoids=b.AllocateId
inner join mom_orderdetail c with (nolock) on b.modid=c.modid
left join v_aa_enum e with (nolock) on e.enumtype='ST.CheckoriDate' and EnumCode='MO'
where a.ddate<c.relsdate
return
end
|
|