金蝶K3BOM展开的SQL语句--实用

set nocount on
create table #Data(FItemID int,
FPPQty decimal(28,10) default(0),
FSOQty decimal(28,10) default(0),
FPInQty decimal(28,10) default(0),
FPOutQty decimal(28,10) default(0),
FDifferent decimal(28,10) default(0),
FPercent decimal(28,10)default(0),
)

create table #PZLItemx(FItemID int,
FQty decimal(28,10) default(0))

create table #Data1(FItemID int,
FPPQty decimal(28,10) default(0),
FSOQty decimal(28,10) default(0),
FPInQty decimal(28,10) default(0),
FPOutQty decimal(28,10) default(0),
FDifferent decimal(28,10) default(0),
FPercent decimal(28,2)default(0),
)

Insert into #Data(FItemID,FPPQty)
select u1.FItemID,u1.FQty
from PPOrder v1 inner join PPOrderEntry u1 on v1.FInterID=u1.FInterID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
Where (v1.FCheckerID>0 or v1.FCheckerID<0) and t1.FErpClsID in(2,3,7)
AND t1.FNumber>='1.01.01.001' AND t1.FNumber<='1.01.100-0070-000' AND u1.FNeedDate>='2008-01-01' AND u1.FNeedDate<='2008-02-01'

insert into #PZLItemx(FItemID,FQty)
select u1.FItemID,SUM(u1.FQty)
from PPOrder v1 inner join PPOrderEntry u1 on v1.FInterID=u1.FInterID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
Where (v1.FCheckerID > 0 Or v1.FCheckerID < 0) And t1.FErpClsID = 8
AND u1.FNeedDate>='2008-01-01' AND u1.FNeedDate<='2008-02-01'
group by u1.FItemID
Set Nocount on
--- GetBomOpen 展开嵌套SQL 开始 by wwq 版本v90 -------
Create Table #PZLItemEntry (
FIndex int IDENTITY,
FItemID int null, --物料ID
FParentItemID int null, --根节点物料ID，当FBomLevel=0时，FItemID = FParentItemID
FOFFSetDay decimal(28,10) DEFAULT(0) NOT NULL,--子项物料提前期偏置 FBOMLevel=0 时 FOFFSetDay=0
FNeedDate datetime null,--需求日期,内部处理，用于计算累计提前期
FNeedQty decimal(28,14) default(0) null, --单位用量
FRelNeedQty decimal(28,14) default(0) null, --实际用量
FBOMLevel int null, --BOM层次
FItemType int null, --10:虚拟件；4:配置；5：特征；6:规划 1：自制OR委外；0：采购
FParentID int default(0) null, --内部使用，上级FINDEX
FUpItemID int default(0) null, --内部使用，上级物料ID
FUpBOM int default(0) null, --内部使用，上级BOM
FRate decimal(28,10) default(0) null, --比率
FScrap decimal(28,10) default(0) null, --损耗率
FRelScrap decimal(28,10) default(0) null, --累计损耗率
FSumParentTime decimal(28,10) default(0) null, --上级累计下来到本次的提前期合计
FFixLeadTime decimal(28,10) default(0) null, --物料固定提前期
FSumLeadtime decimal(28,10) default(0) null,
--物料累计提前期
FLeadTime decimal(28,10) default(0) null, --物料变动提前期
FBatChangeEconomy decimal(28,10) default(0) null, --物料变动批量
FSecInv decimal(28,10) def

ault(0) null, --物料安全库存
FBom int default(0) null, --BOM
FObjID int default(0) null, --跟踪对象
FGroupID int default(0) null, --根节点FINDEX
FOperId int default(0) null, --工序ID
FOrderTrategy varchar(10) default('') null, --订货策略
FPlanTrategy varchar(10) default('') null, --计划策略
FMaterielType varchar(10) default('') null, --BOM子项类型
FMarshalType varchar(10) default('') null, --BOM子项配置属性
FBeginDate datetime default('1900-01-01'), --BOM子项有效开始日期
FEndDate datetime default('2100-01-01'), --BOM子项有效结束日期
FAuxPropID int default(0), --辅助属性
FHaveUptDate int default(0), --工厂日历更新标志
FHaveBomOpen smallint default(0) null,
FSPID int default(0), --发料仓库
FStockID int default(0) --发料仓位
,FIsCharSourceItem Smallint default(0) null, --是否特性来源物料0/1(子项物料)
FSourceItemID int default(0) null, --若是特性物料 , 则记录特性来源物料(子项物料)
FCharConfigID int default(0) null, --特性方案ID (子项物料)
FDetailID uniqueidentifier null, --唯一码 (子项物料)
FBOMSkip smallint default(0) null, --BOM是否跳层
FCalID int default(999) null, --工厂日历ID
FDayID int default(0) null --日历内码
)


--1. --创建临时表
Set nocount on
--- BOM展开表的特性信息 -------
If Object_id('[tempdb].[dbo].[#TempChar]') Is Not Null
TRUNCATE TABLE #TempChar
else
begin
Create Table #TempChar (
FIndex int not null, --关联TempBomOpenDest 的Findex字段
FCharID int null, --特性ID
FCharValID int null ) --特性值
END

--- 特性配置BOM展开临时表（结构相同于sDestTable） -------
If Object_id('[tempdb].[dbo].[#TempBomOpen_Char]') Is Not Null
TRUNCATE TABLE #TempBomOpen_Char
else
begin
SELECT * INTO #TempBomOpen_Char FROM #PZLItemEntry WHERE 1<>1
END

declare @MinDate smalldatetime,@MaxDate smalldatetime
declare @MinDayID int,@MaxDayID int
select @MinDayID=Min(FInterID),@MaxDayID=Max(FInterID),@MinDate=Min(FDay),@MaxDate=Max(FDay) from t_MutiWorkCal where FCalID=999 and FInterID>0


Insert into #PZLItemEntry ( FParentItemID,FItemID ,FNeedDate,FNeedQty,FRelNeedQty,FBOMLevel,FItemType,
FParentID,FFixLeadTime,FObjID,FPlanTrategy,FOrderTrategy,FHave
BomOpen,FBom,FSumParentTime,FLeadTime,FBatChangeEconomy,
FIsCharSourceItem, FSourceItemID, FCharConfigID, FBOMSkip, FAuxPropID, FCalID, FDayID )
Select u1.FItemID,u1.FItemID,u1.FDate,u1.FNeedQty,u1.FRelNeedQty, 0,(case t5.FID when 'WG' then 0 when

'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 when 'TZL' then 5 else 6 end) FItemtype,
0,t1.FFixLeadTime, 0,
isnull(t5_1.FID,'MRP'),isnull(t5_2.FID,'LFL'),0,u1.FBom,0
,t1.FLeadTime,T1.FBatChangeEconomy,
ISNULL(t1.FIsCharSourceItem,0), ISNULL(t6.FSourceItemID,0), ISNULL(t6.FID,0) as FCharConfigID, t7.FBOMSkip, FAuxPropID
,Isnull(t2.FCalID,'999') as FCalID
,Isnull( (select FNxtID as FDayID from t_MutiWorkCal where FCalID=Isnull(t2.FCalID,999) AND FDay = u1.FDate),@MinDayID) as FDayID
From
(select distinct FItemID ,0 FObjID,0 FBom,convert(varchar(10),GetDate() ,121) as FDate,1 AS FNeedQty,1 AS FRelNeedQty From #PZLItemx ) u1
INNER JOIN t_ICItem t1 ON U1.FItemID=T1.FItemID
INNER JOIN t_Submessage t5 ON T1.FErpClsID=T5.FInterID AND T5.FTypeID=210 --物料基本信息
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --计划策略
LEFT JOIN t_SubMessage t5_2 ON t1.FOrderTrategy = t5_2.FInterID and t5_2.FTypeID = 169 --订货策略
LEFT JOIN ICPlan_CharConfig t6 ON u1.FItemID = t6.FItemID AND t6.FCheckerID > 0
LEFT JOIN ICBom t7 ON u1.FBom = t7.FInterID
left join t_Department t2 on T1.FSource=t2.FItemID and T1.FErpClsID in (2,7,9)
create index #idx_FItemID on #PZLItemEntry(FItemID)

create index #idx_FBOM on #PZLItemEntry(FBOM)


Update v1 Set v1.FBom = u1.FInterID, FBomSkip=u1.FBomSkip , v1.FAuxPropID=u1.FAuxPropID
From #PZLItemEntry v1
INNER JOIN ICBom u1 ON v1.FItemID = u1.FItemID
Where isnull(v1.FBom,0) = 0 and u1.FBomType <> 3 and u1.FUseStatus = 1072

Update #PZLItemEntry Set FGroupID = FIndex Where FBomLevel = 0
--更新特性物料的BOM
Update #PZLItemEntry
Set FBOM = B.FBomInterID , FBomSkip=C.FBomSkip, FAuxPropID=C.FAuxPropID
From #PZLItemEntry A
inner join ICPlan_CharConfig B ON A.FitemID=B.FitemID AND B.FCheckerID > 0 --特性方案
INNER JOIN ICBOM C ON B.FBomInterID = C.FInterID
Where A.FIsCharSourceItem=0 AND A.FCharConfigID>0

INSERT INTO #TempChar (Findex, FCharID, FCharValID )
select A1.FIndex,C2.FCharID, C2.FCharValID
From #PZLItemEntry A1 --子项
INNER JOIN ICPlan_CharConfig C1 ON A1.FItemID=C1.FItemID --特性配置方案
INNER JOIN ICPlan_CharConfigEntry C2 ON C1.FID=C2.FID
Where A1.FBomLevel = 0 AND A1.FIsCharSourceItem=0 AND A1.FCharConfigID>0

Set nocount on
Declare @BOMMaxLevel smallint
Declare @BOMLevel smallint
Declare @SelectRows int
Declare @LastBomLevel int

select @BOMMaxLevel = 30
select @BOMLevel =0
Select @SelectRows = 0
Select @SelectRows = isnull(count(*),0) From #PZLItemEntry where FHaveBomOpen = 0 and FBomLevel = @BomLevel and FItemType > 0
and FNeedQty>0 and (FItemTyp
e = 4 or FItemType = 5 or FItemType =6 )
while ( @SelectRows >0 and @BomLevel < @BomMaxLevel)
Begin

--- From ICBOMChild
Insert Into #PZLItemEn

try
( FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FFixLeadTime,FScrap,FRelScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FHaveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FDetailID,
FCalID, FDayID, FBOM )
Select u1.FParentItemID,v2.FItemID,
t10.FPreDay as FNeedDate,
(
convert(decimal(28,15),
(convert(decimal(28,15),u1.FNeedQty) *
convert(decimal(28,15),
(
convert( decimal(28,15),v2.FQty ) / convert( decimal(28,15),v1.FQty )
)
)
)
)*
convert(decimal(28,15),
(
convert( decimal(28,15),(v2.FPercent /100) )

)
)
) as FNeedQty,
(
convert(decimal(28,15),
(convert(decimal(28,15),u1.FRelNeedQty) *
convert(decimal(28,15),
(
convert( decimal(28,15),v2.FQty ) / convert( decimal(28,15),v1.FQty )
)
)
)
)*
convert(decimal(28,15),
(
convert( decimal(28,15),(v2.FPercent /100) )

)
)
) as FRelNeedQty,
(@BOMLevel +1) FBomLevel, (case t5.FID when 'WG' then 0 when 'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 when 'TZL' then 5 else 6 end) FItemtype,
u1.FIndex FParentID,( Cast(v2.FQty As Float) * 1.0 / Cast(v1.FQty As Float)) FRate,
t1.FFixLeadTime FFixLeadTime,0,0, u1.FGroupID,
isnull(t5_1.FID,'MRP'),isnull(t5_2.FID,'LFL'),
(case when (v2.FMaterielType=371 or v2.FMaterielType=375) then 0 else 1 end ) FMeterialType,
u1.FObjID,v2.FOperID,isnull(t5_3.FID,'COM'),isnull(t5_4.FID,'COM'),isnull(t1.FTotalTQQ,0),(isnull(u1.FFixLeadTime,0) + isnull(u1.FSumParentTime,0) ) FSumParentTime
,t1.FLeadTime,T1.FBatChangeEconomy,T1.FSecInv,v2.FBeginDay,V2.FEndDay,u1.FItemID,V1.FInterID,V2.FAuxPropID,V2.FSPID,V2.FStockID ,v2.FOffSetDay , V2.FDetailID
,IsNull(t2.FCalID,'999') as FCalID
,t10.FPreID as FDayID
,0 AS FBOM From (select * from #PZLItemEntry
where FBomLevel = @BomLevel and FHaveBomOpen = 0 and FItemType > 0
and FNeedQty>0 and (FItemType = 4 or FItemType = 5 or FItemType =6 )
) u1
INNER JOIN ICBOM v1 ON /*u1.FItemID=V1.FItemID AND*/ u1.FBOM=V1.FInterID AND V1.FBOMType<>3
INNER JOIN ICBOMChild V2 ON V1.FInterID=V2.FInterID
INNER JOIN T_ICItem t1 ON V2.FItemID=T1.FItemID
INNER JOIN t_SubMessage t5 ON T1.FErpclsID=T5.FInterID
AND T5.FTypeID=210 --子项物料类型
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --子项物料计划策略
LEFT JOIN t_SubMessage t5_2 ON T1.FOrderTrategy=t5_2.FInterID AND T5_2.FTypeID=169 --子项物料订货策略
LEFT JOIN t_Submessage t5_3 ON V2.FMaterielType=T5_3.FInterID AND T5_3.FTypeID=173 --子项物料属性
LEF

T JOIN t_SubMessage t5_4 ON V2.FmarshalType=T5_4.FInterID AND T5_4.FTypeID=174 --子项物料用途 通用、可选
left join t_Department t2 on T1.FSource=t2.FItemID and T1.FErpClsID in (2,7,9)
Left Join t_MutiWorkCal t3 on t3.FCalID = u1.FCalID and t3.FInterID >0
and t3.FInterID = (u1.FDayID - CEILING(u1.FFixLeadTime+ u1.FRelNeedQty*(u1.FLeadTime/u1.FBatChangeEconomy))+ (case when v2.FOffSetDay > 0 then CEILING(v2.FOffSetDay) else FLOOR(v2.FOffSetDay) end ))
Left Join t_MutiWorkCal T10 on t10.FCalID=Isnull(t2.FCalID,999)
and T10.FDay = Isnull(t3.FDay,@MinDate)
WHERE 1=1 and t1.FDeleted=0
--AND ISNULL(t1.FIsCharSourceItem,0)=0 and Isnull(t1.FCharSourceItemID,0)=0 --子项过滤掉特性来源物料和特性物料
and (((v2.FMaterielType=371 or v2.FMaterielType=375)))
and u1.FIsCharSourceItem = 0 And u1.FCharConfigID = 0 --过滤掉特性来源物料和特性物料

--- From ICCustChild
if @BomLevel >= 0
Begin
Insert Into #PZLItemEntry
( FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FFixLeadTime,FScrap,FRelScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FHaveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FDetailID,
FCalID, FDayID, FBOM )
Select u1.FParentItemID,v2.FItemID,
t10.FPreDay as FNeedDate,
(
convert(decimal(28,15),
(convert(decimal(28,15),u1.FNeedQty) *
convert(decimal(28,15),
(
convert( decimal(28,15),v2.FQty ) / convert( decimal(28,15),v1.FQty )
)
)
)
)*
convert(decimal(28,15),
(
convert( decimal(28,15),(v2.FPercent /100) )

)
)
) as FNeedQty,
(
convert(decimal(28,15),
(convert(decimal(28,15),u1.FRelNeedQty) *
convert(decimal(28,15),
(
convert( decimal(28,15),v2.FQty ) / convert( decimal(28,15),v1.FQty )
)
)
)
)*
convert(decimal(28,15),
(
convert( decimal(28,15),(v2.FPercent /100) )

)
)
) as FRelNeedQty,
(@BOMLevel +1) FBomLevel, (case t5.FID when 'WG' then 0 when 'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 whe
n 'TZL' then 5 else 6 end) FItemtype,
u1.FIndex FParentID,( Cast(v2.FQty As Float) * 1.0 / Cast(v1.FQty As Float)) FRate,
t1.FFixLeadTime FFixLeadTime,0,0, u1.FGroupID,
isnull(t5_1.FID,'MRP'),isnull(t5_2.FID,'LFL'),
(case when (v2.FMaterielType=371 or v2.FMaterielType=375) then 0 else 1 end ) FMeterialType,
u1.FObjID,v2.FOperID,isnull(t5_3.FID,'COM'),isnull(t5_4.FID,'COM'),isnull(t1.FTotalTQQ,0),(isnull(u1.FFixLeadTime,0) + isnull(u1.FSumParentTime,0) ) FSumParentTime
,t1.FLeadTime,T1.FBatChangeEconomy,T1.FSecInv,v2.FBeginDay,V2.FEndDay,u1.FItemID,V1.FInterID,V2.FAuxPropID,V2.FSPID,V2.FStockID ,v2.FOffSetDay , V2.FDetailID
,IsNull(t2.FCalID,'999') as FCalID
,t10.FPreID as

FDayID
,ISNULL(V2.FCustBOMCode,0) AS FBOM From (select * from #PZLItemEntry
where FBomLevel = @BomLevel and FHaveBomOpen = 0 and FItemType > 0
and FNeedQty>0 and (FItemType = 4 or FItemType = 5 or FItemType =6 ) ) u1
INNER JOIN ICBOM v1 ON /*u1.FItemID=V1.FItemID AND*/ u1.FBOM=V1.FInterID AND V1.FBOMType=3
INNER JOIN ICCustBOMChild V2 ON V1.FInterID=V2.FInterID
INNER JOIN T_ICItem t1 ON V2.FItemID=T1.FItemID
INNER JOIN t_SubMessage t5 ON T1.FErpclsID=T5.FInterID AND T5.FTypeID=210 --子项物料类型
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --子项物料计划策略
LEFT JOIN t_SubMessage t5_2 ON T1.FOrderTrategy=t5_2.FInterID AND T5_2.FTypeID=169 --子项物料订货策略
LEFT JOIN t_Submessage t5_3 ON V2.FMaterielType=T5_3.FInterID AND T5_3.FTypeID=173 --子项物料属性
LEFT JOIN t_SubMessage t5_4 ON V2.FmarshalType=T5_4.FInterID AND T5_4.FTypeID=174 --子项物料用途 通用、可选
left join t_Department t2 on T1.FSource=t2.FItemID and T1.FErpClsID in (2,7,9)
Left Join t_MutiWorkCal t3 on t3.FCalID = u1.FCalID and t3.FInterID >0
and t3.FInterID = (u1.FDayID - CEILING(u1.FFixLeadTime+ u1.FRelNeedQty*(u1.FLeadTime/u1.FBatChangeEconomy))+ (case when v2.FOffSetDay > 0 then CEILING(v2.FOffSetDay) else FLOOR(v2.FOffSetDay) end ))
Left Join t_MutiWorkCal T10 on t10.FCalID=Isnull(t2.FCalID,999)
and T10.FDay = Isnull(t3.FDay,@MinDate)
WHERE 1=1 and t1.FDeleted=0
and (((v2.FMaterielType=371 or v2.FMaterielType=375)))

End
-------------------------------------特性配置处理开始------------------------------------------
--1. --创建临时表
--判断是否有特性来源物料/特性物料存在
IF EXISTS (SELECT 1 FROM #PZLItemEntry WHERE FBomLevel = @BomLevel AND (FIsCharSourceItem=1 OR FcharConfigID>0) )
BEGIN

--2. 处理父项为特性配置来源物料(特性物料的公用件也一样)
Insert Into #PZLItemEntry
(FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FF
ixLeadTime,FScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FHaveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FDetailID,
FBOM
, FIsCharSourceItem, FSourceItemID, FCharConfigID --, FBOMSkip
,FCalID, FDayID
)
Select u1.FParentItemID,v2.FItemID,
--当日期扣减小于1753-01-01将出现错误,由于BOM有效期最小1900，因此用1900
t10.FPreDay as FNeedDate,
(convert(decimal(28,15),(convert(decimal(28,15),u1.FNeedQty) * convert(decimal(28,15),(convert(decimal(28,15),v2.FQty) * convert(decimal(28,15),CONVERT(DECIMAL(28,15),1.0) / CONVERT(DECIMAL(28,15),v1.FQty))))))*
convert(

decimal(28,15), convert(decimal(28,15),(v2.FPercent /100)))) as FNeedQty,
(convert(decimal(28,15),(convert(decimal(28,15),u1.FRelNeedQty) * convert(decimal(28,15),(convert(decimal(28,15),v2.FQty) * convert(decimal(28,15),CONVERT(DECIMAL(28,15),1.0) / CONVERT(DECIMAL(28,15),v1.FQty))))))*
convert(decimal(28,15), convert(decimal(28,15),(v2.FPercent /100)))) as FRelNeedQty,
(@BOMLevel +1) FBomLevel, (case t5.FID when 'WG' then 0 when 'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 when 'TZL' then 5 else 6 end) FItemtype,
u1.FIndex FParentID,( Cast(v2.FQty As Float) * 1.0 / Cast(v1.FQty As Float)) FRate,
t1.FFixLeadTime FFixLeadTime,0, u1.FGroupID,
isnull(t5_1.FID,'MRP'),isnull(t5_2.FID,'LFL'),
(case when (v2.FMaterielType=371 or v2.FMaterielType=375) then 0 else 1 end ) FMeterialType, --FHaveBomOpen,
u1.FObjID,v2.FOperID,isnull(t5_3.FID,'COM'),isnull(t5_4.FID,'COM'),isnull(t1.FTotalTQQ,0),(isnull(u1.FFixLeadTime,0) + isnull(u1.FSumParentTime,0) ) FSumParentTime
,t1.FLeadTime,T1.FBatChangeEconomy,T1.FSecInv,v2.FBeginDay,V2.FEndDay,u1.FItemID,V1.FInterID,V2.FAuxPropID,V2.FSPID,V2.FStockID ,v2.FOffSetDay ,
v2.FDetailID,0 AS FBOM
,ISNULL(t1.FIsCharSourceItem,0), ISNULL(t6.FSourceItemID,0), ISNULL(t6.FID,0) as FCharConfigID
--, t7.FBOMSkip
,IsNull(t2.FCalID,'999') as FCalID
,t10.FPreID as FDayID

From (select * from #PZLItemEntry
where FBomLevel = @BomLevel and FHaveBomOpen = 0 and FItemType > 0
and FNeedQty>0 and (FItemType = 4 or FItemType = 5 or FItemType =6 )
and ( FIsCharSourceItem=1 OR FcharConfigID>0 ) --特性来源物料和特性物料
) u1
INNER JOIN ICBOM v1 ON /*u1.FItemID=V1.FItemID AND*/ u1.FBOM=V1.FInterID AND V1.FBOMType<>3
INNER JOIN ICBOMChild V2 ON V1.FInterID=V2.FInterID
INNER JOIN T_ICItem t1 ON V2.FItemID=T1.FItemID
INNER JOIN t_SubMessage t5 ON T1.FErpclsID=T5.FInterID AND T5.FTypeID=210 --子项物料类型
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --子项物料计划策略
LEFT JOIN t_SubMessage t5_2 ON T1.FOrderTrategy=t5
_2.FInterID AND T5_2.FTypeID=169 --子项物料订货策略
LEFT JOIN t_Submessage t5_3 ON V2.FMaterielType=T5_3.FInterID AND T5_3.FTypeID=173 --子项物料属性
LEFT JOIN t_SubMessage t5_4 ON V2.FmarshalType=T5_4.FInterID AND T5_4.FTypeID=174 --子项物料用途 通用、可选
LEFT JOIN ICPlan_CharConfig t6 ON V2.FItemID = t6.FItemID and t6.FCheckerID > 0
left join t_Department t2 on T1.FSource=t2.FItemID and T1.FErpClsID in (2,7,9)
Left Join t_MutiWorkCal t3 on t3.FCalID = u1.FCalID and t3.FInterID >0
and t3.FInterID = (u1.FDayID - CEILING(u1.FFixLeadTime+ u1.FRelNeedQty*(u1.FLeadTime/u1.FBatChangeEconomy))+ (case when v2.FOffSetDay

> 0 then CEILING(v2.FOffSetDay) else FLOOR(v2.FOffSetDay) end ))
Left Join t_MutiWorkCal T10 on t10.FCalID=Isnull(t2.FCalID,999)
and T10.FDay = Isnull(t3.FDay,@MinDate)
WHERE 1=1
AND t1.FDeleted=0
and V2.FhasChar=0 --先只处理没有特性的公用件
and (((v2.FMaterielType=371 or v2.FMaterielType=375)))

--3. 处理父项为特性物料:
-- 3.2 插入子项数据
Insert Into #TempBomOpen_Char
(FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FFixLeadTime,FScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FHaveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FBOM
, FIsCharSourceItem, FSourceItemID, FCharConfigID, FDetailID --, FBOMSkip
,FCalID, FDayID
)
Select u1.FParentItemID,v2.FItemID,
--当日期扣减小于1753-01-01将出现错误,由于BOM有效期最小1900，因此用1900
t10.FPreDay as FNeedDate,
(convert(decimal(28,15),(convert(decimal(28,15),u1.FNeedQty) * convert(decimal(28,15),(convert(decimal(28,15),v2.FQty) * convert(decimal(28,15),CONVERT(DECIMAL(28,15),1.0) / CONVERT(DECIMAL(28,15),v1.FQty))))))*
convert(decimal(28,15), convert(decimal(28,15),(v2.FPercent /100)))) as FNeedQty,
(convert(decimal(28,15),(convert(decimal(28,15),u1.FRelNeedQty) * convert(decimal(28,15),(convert(decimal(28,15),v2.FQty) * convert(decimal(28,15),CONVERT(DECIMAL(28,15),1.0) / CONVERT(DECIMAL(28,15),v1.FQty))))))*
convert(decimal(28,15), convert(decimal(28,15),(v2.FPercent /100)))) as FRelNeedQty,
(@BOMLevel +1) FBomLevel, (case t5.FID when 'WG' then 0 when 'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 when 'TZL' then 5 else 6 end) FItemtype,
u1.FIndex FParentID,( Cast(v2.FQty As Float) * 1.0 / Cast(v1.FQty As Float)) FRate,
t1.FFixLeadTime FFixLeadTime,0, u1.FGroupID,
isnull(t5_1.FID,'MRP'),isnull(t5_2.FID,'LFL'),
(case when (v2.FMaterielType=371 or v2.FMaterielType=375) then 0 else 1 end ) FMeterialType,--FHaveBomOpen,
u1.FObjID,v2.FOperID,isnull(t5_3.FID,'COM'),isnull(t5_4.FID,'COM'),isnull(t1.FTotalTQQ,0),(isnull(u1.FFixL
eadTime,0) + isnull(u1.FSumParentTime,0) ) FSumParentTime
,t1.FLeadTime,T1.FBatChangeEconomy,T1.FSecInv,v2.FBeginDay,V2.FEndDay, u1.FItemID,V1.FInterID,V2.FAuxPropID,V2.FSPID,V2.FStockID ,v2.FOffSetDay ,
0 AS FBOM
,ISNULL(t1.FIsCharSourceItem,0), ISNULL(t6.FSourceItemID,0), ISNULL(t6.FID,0) as FCharConfigID, v2.FDetailID
--, t7.FBOMSkip
,IsNull(t2.FCalID,'999') as FCalID
,t10.FPreID as FDayID

From (select * from #PZLItemEntry
where FBomLevel = @BomLevel and FHaveBomOpen = 0 and FItemType > 0
and FNeedQty>0 and (FItemType = 4 or FItemType = 5 or FItemType =6 )
and FcharConfigID>0 --特性物料
) u1

INNER JOIN ICBOM v1 ON /*u1.FItemID=V1.FItemID AND*/ u1.FBOM=V1.FInterID AND V1.FBOMType<>3
INNER JOIN ICBOMChild V2 ON V1.FInterID=V2.FInterID
INNER JOIN T_ICItem t1 ON V2.FItemID=T1.FItemID
INNER JOIN t_SubMessage t5 ON T1.FErpclsID=T5.FInterID AND T5.FTypeID=210 --子项物料类型
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --子项物料计划策略
LEFT JOIN t_SubMessage t5_2 ON T1.FOrderTrategy=t5_2.FInterID AND T5_2.FTypeID=169 --子项物料订货策略
LEFT JOIN t_Submessage t5_3 ON V2.FMaterielType=T5_3.FInterID AND T5_3.FTypeID=173 --子项物料属性
LEFT JOIN t_SubMessage t5_4 ON V2.FmarshalType=T5_4.FInterID AND T5_4.FTypeID=174 --子项物料用途 通用、可选
LEFT JOIN ICPlan_CharConfig t6 ON V2.FItemID = t6.FItemID and t6.FCheckerID > 0
left join t_Department t2 on T1.FSource=t2.FItemID and T1.FErpClsID in (2,7,9)
Left Join t_MutiWorkCal t3 on t3.FCalID = u1.FCalID and t3.FInterID >0
and t3.FInterID = (u1.FDayID - CEILING(u1.FFixLeadTime+ u1.FRelNeedQty*(u1.FLeadTime/u1.FBatChangeEconomy))+ (case when v2.FOffSetDay > 0 then CEILING(v2.FOffSetDay) else FLOOR(v2.FOffSetDay) end ))
Left Join t_MutiWorkCal T10 on t10.FCalID=Isnull(t2.FCalID,999)
and T10.FDay = Isnull(t3.FDay,@MinDate)
WHERE 1=1
AND t1.FDeleted=0
and V2.FhasChar > 0 --有特性的子项
and (((v2.FMaterielType=371 or v2.FMaterielType=375)))

IF EXISTS (SELECT 1 FROM #TempBomOpen_Char WHERE FBomLevel= @BOMLevel +1 ) --判断该层是否有特性物料
BEGIN
--3.3 根据特性值过滤有特性的子项
-- 取得符合条件的有特性的Bom子项
DELETE t1 FROM #TempBomOpen_Char t1
Left JOIN
(
SELECT FIndex /* ,FParentItemID,FBom,FITEMID,FSourceItemID, COUNT(*) */
From
(
SELECT FIndex,FParentItemID,FBom,FITEMID,FSourceItemID, FCHARID,COUNT(*) FCOUNT FROM
(
SELeCT U.FIndex,u3.FItemID as FParentItemID, U.FBom,U.FITEMID,U.FSourceItemID,U.FCharConfigID,D.FCHARID AS D_FCHARID,D.FCHARVALID,E.FNAME ,
V.FCHARID AS FCHARID,b3.FCharID as b3_FCharID,b3.FCharValID as x_FCharValID
--SELECT *
FROM #TempBomOpen_Char U
Full Join --BOM子项外关联特性, 使每个子项都有该BOM全
部的特性(BOM子项的特性 union 表头特性物料特性)
( select DISTINCT FSourceItemID,FCharID from #TempBomOpen_Char a1
inner join ICPlan_BOMChildChar b1 on a1.FDetailID=b1.FDetailID Where a1.FBOMLevel=@BomLevel+1
Union
select DISTINCT b2.FSourceItemID,c2.FCharID
From #PZLItemEntry /*#TempBomOpen_Char*/ a2
inner join icplan_CharConfig b2 on a2.FCharConfigID=b2.Fid
inner join icplan_charconfigEntry c2 on b2.fid=c2.fid
Where a2.FBOMLevel=@BomLevel
) V ON U.FSourceItemID=v.FSourceItemID
Left Join ICPlan_BOMChildChar D --获得BOM子项的特性值
ON U.FDetailID=D.FDetailID and V.FCharID=d.FCharID
LEFT JOIN ICPlan_CharacteristicEntry E ON D.

FCHARVALID=E.FCHARVALID
--获得父项的特性过滤条件
Inner join #PZLItemEntry as u3 on U.FParentID=u3.FIndex and u3.FBOMLevel=@BOMLevel --父项物料
LEFT Join icplan_CharConfig a3 on u3.FItemID=a3.FItemID and a3.FCheckerID > 0
LEFT join icplan_charconfigEntry b3 on a3.fid=b3.fid and D.FCHARID=b3.FCHARID --特性匹配
WHERE ( 1=1 AND U.FBOMLevel=@BOMLevel + 1
AND ISNULL(D.FCHARVALID,0)=0 OR ISNULL(b3.FCHARVALID,0)=0 OR D.FCHARVALID=b3.FCHARVALID
) --ORDER BY B.FINTERID,B.FENTRYID,B.FITEMID, V.FCHARID
) M GROUP BY FIndex,FParentItemID,FBom,FITEMID,FSourceItemID, FCHARID
) N GROUP BY FIndex,FParentItemID,FBom,FITEMID,FSourceItemID
HAVING COUNT(*) >=
( SELECT COUNT(*) FROM
( select DISTINCT FSourceItemID,FCharID from #TempBomOpen_Char a1
inner join ICPlan_BOMChildChar b1 on a1.FDetailID=b1.FDetailID Where a1.FBOMLevel=@BomLevel+1
Union
select DISTINCT a2.FSourceItemID,c2.FCharID
From #PZLItemEntry /*#TempBomOpen_Char*/ a2
inner join icplan_CharConfig b2 on a2.FCharConfigID=b2.Fid
inner join icplan_charconfigEntry c2 on b2.fid=c2.fid
Where a2.FBOMLevel=@BomLevel
) P
Where P.FSourceItemID = N.FSourceItemID
GROUP BY FSourceItemID
)
) t2 on t1.FIndex=t2.FIndex
Where t1.FBomLevel= @BOMLevel +1 and t2.FIndex is Null
--3.4 将#TempBomOpen_Char的数据, INSERT INTO 到sDestTable

INSERT INTO #PZLItemEntry
(FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FFixLeadTime,FScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FHaveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FBOM
, FIsCharSourceItem, FSourceItemID, FCharConfigID, FDetailID --, FBOMSkip
,FCalID, FDayID
)
SELECT
FParentItemID,FItemID,FNeedDate,FNeedQty,FRelNeedQty,FBomLevel,FItemType,
FParentID,FRate,FFixLeadTime,FScrap,FGroupID,FPlanTrategy,FOrderTrategy,
FH
aveBomOpen,FObjID,FOperID,FMaterielType,FMarshalType,FSumLeadTime,FSumParentTime, FLeadTime,FBatChangeEconomy,FSecInv,FBeginDate,FEndDate,FUpItemID,FUpBOM,FAuxPropID,FSPID,FStockID,FOffSetDay, FBOM
, FIsCharSourceItem, FSourceItemID, FCharConfigID, FDetailID --, FBOMSkip
,FCalID, FDayID
FROM #TempBomOpen_Char WHERE FBomLevel= @BOMLevel +1

END --判断该层是否有特性物料

--'更新sDestTable表的BOM信息
Update A
Set FBOM = C.FInterID , FBomSkip=C.FBomSkip
From #PZLItemEntry A
INNER JOIN ICBOM C ON A.FItemID = C.FItemID
Where A.FBOMLevel= @BOMLevel +1 AND A.FBom = 0 and C.FUseStatus = 1072
--5. 更新所有子项为特性配置来源物料, 根据BOM和特性来判断是否已有特性物料，如果有则更新为特性物料。

--5.1 若是子项有特性来源物料，需要继承特性，保存到#TmpChar（参考GetCharBomOpen函数）
--(子项没有父项的特性,则该特性被过滤
-

-子项有父项没有的特性, 则特性值为空)
INSERT into #TempChar (Findex, FCharID, FCharValID )
SELECT A1.FIndex, C2.FcharID, B2.FCharValID
From #PZLItemEntry A1 --子项
INNER JOIN ICPLAN_ItemChar C1 ON A1.FItemID=C1.FItemID --子项特性范围表
INNER JOIN ICPLAN_ItemCharEntry C2 ON C1.FID=C2.FID
INNER JOIN #PZLItemEntry A2 ON A1.FParentID =A2.FIndex --父项
Left Join #TempChar B2 ON A2.Findex=B2.Findex --父项特性
And C2.FCharID=B2.FCharID --过滤子项才有的特性
WHERE A1.FBomLevel=@BomLevel + 1 And A1.FIsCharSourceItem = 1 AND B2.FCharValID > 0
ORDER BY A1.FIndex, C2.FCharID, B2.FCharValID

--5.2 根据BOM和特性来判断是否已有特性物料
UPDATE #PZLItemEntry
SET FCharConfigID=B.FID, FItemID=B.FItemID, FSourceItemID=B.FSourceItemID, FBom=FBomInterID, FIsCharSourceItem = 0
,FFixLeadTime=T1.FFixLeadTime, FSumLeadTime=isnull(t1.FTotalTQQ,0)
,FLeadTime=t1.FLeadTime, FBatChangeEconomy=T1.FBatChangeEconomy, FSecInv=T1.FSecInv
,FItemtype=(case t5.FID when 'WG' then 0 when 'ZZ' then 1 when 'WWJG' then 1 when 'PZL' then 4 when 'XNJ' then 10 when 'TZL' then 5 else 6 end)
,FPlanTrategy=isnull(t5_1.FID,'MRP')
,FOrderTrategy=isnull(t5_2.FID,'LFL')
--SELECT B.FID,C.FID,*
FROM #PZLItemEntry A
Inner join ICPlan_CharConfig B ON A.FItemID=B.FSourceItemID AND B.FCheckerID > 0
Left Join --也可以处理成NOT IN 语句
( --查询出有特性不匹配的特性方案
SELECT DISTINCT FIndex,FID FROM
(
SELECT A1.Findex,CASE WHEN B1.FID IS NULL THEN B2.FID ELSE B1.FID END AS FID,
B1.FITEMID,B1.FSOURCEITEMID,A2.FcharValID AS FSourceCharValID, B2.FcharValID AS FCharValID
FROM (SELECT * FROM #PZLItemEntry WHERE FBOMLevel=@BOMLevel+1 AND FCharConfigID=0 ) A1
Inner join #TempChar A2 ON A1.Findex= A2.FIndex
INNER join ICPlan_CharConfi
g B1 ON A1.FitemID=B1.FsourceItemID and A1.FBOM=B1.FBOMInterID AND B1.FCheckerID > 0
FULL join ICPlan_CharConfigEntry B2 ON B1.FID = B2.FID AND A2.FCharValID= B2.FCharValID
Where (A2.FcharValID Is Null Or B2.FcharValID Is Null)
) AS M
) C ON (A.FIndex=c.FIndex or c.findex is null) and B.FID=C.FID
INNER JOIN T_ICItem t1 ON B.FItemID=T1.FItemID -- AND t1.FDeleted = 0
INNER JOIN t_SubMessage t5 ON T1.FErpclsID=T5.FInterID AND T5.FTypeID=210 --子项物料类型
LEFT JOIN t_Submessage t5_1 ON T1.FPlanTrategy=T5_1.FInterID AND T5_1.FTypeID=167 --子项物料计划策略
LEFT JOIN t_SubMessage t5_2 ON T1.FOrderTrategy=t5_2.FInterID AND T5_2.FTypeID=169 --子项物料订货策略
Where C.FID IS NULL AND A.FBOMLevel= @BOMLevel+1 AND A.FIsCharSourceItem = 1 AND A.FCharConfigID=0

AND EXISTS (SELECT 1 FROM #TempChar X WHERE A.FIndex=X.FIndex)

AND t1.FDeleted=0

END ----判断是否有特性来源物料/特性物料存在

--更新特性物料的相关信息
Upd

ate A
Set FBOM = B.FBomInterID , FBomSkip=C.FBomSkip, FCharConfigID= isnull(b.Fid,0),
FSourceItemId=t1.FCharSourceItemId
From #PZLItemEntry A
INNER JOIN t_icItemPlan t1 on A.FItemID=t1.FItemID
Left join ICPlan_CharConfig B ON A.FitemID=B.FitemID AND B.FCheckerID > 0 --特性方案
INNER JOIN ICBOM C ON B.FBomInterID = C.FInterID
WHERE A.FBomLevel = @BOMLevel +1 AND T1.FCharSourceItemId > 0
INSERT INTO #TempChar (Findex, FCharID, FCharValID )
select A1.FIndex,C2.FCharID, C2.FCharValID
From #PZLItemEntry A1 --子项
INNER JOIN ICPlan_CharConfig C1 ON A1.FItemID=C1.FItemID AND C1.FCheckerID > 0 --特性配置方案
INNER JOIN ICPlan_CharConfigEntry C2 ON C1.FID=C2.FID
Where A1.FBomLevel = @BOMLevel + 1 AND A1.FCharConfigID>0
-------------------------------------特性配置处理结束------------------------------------------

Update v1 Set v1.FBom = u1.FInterID, v1.FBomSkip=u1.FBomSkip
From #PZLItemEntry v1, ICBom u1
Where v1.FBomLevel = @BomLevel + 1 and v1.FItemType > 0 and v1.FItemID = u1.FItemID
and isnull(v1.FBom,0) = 0 and u1.FBomType <> 3 and u1.FUseStatus = 1072
Update #PZLItemEntry Set FHaveBomOpen = 1
Where FBomLevel = @BomLevel and FHaveBomOpen = 0 and FItemType > 0 and FNeedQty>0
Select @BomLevel = @BomLevel + 1
Select @SelectRows = 0
Select @SelectRows = isnull(count(*),0) From #PZLItemEntry where FHaveBomOpen = 0 and FBomLevel = @BomLevel and FItemType > 0
and FNeedQty>0 and (FItemType = 4 or FItemType = 5 or FItemType =6 )
End
----跳层为是,则按虚拟件处理
Update #PZLItemEntry SET FItemType = 10 WHERE FBomSkip=1058

DROP TABLE #TempChar
DROP TABLE #TempBomOpen_Char

-------------- BOM展开嵌套SQL 结束 ------------------------------------


insert into #Data(FItemI
D,FPPQty)
select u1.FItemID,v1.FQty*u1.FNeedQty
from #PZLItemx v1 inner join #PZLItemEntry u1 on v1.FItemID=u1.FParentItemID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
where t1.FErpClsID IN(2,3,7) AND t1.FNumber>='1.01.01.001' AND t1.FNumber<='1.01.100-0070-000'

Insert into #Data(FItemID,FSOQty)
select u1.FItemID,u1.FQty
from SEOrder v1 inner join SEOrderEntry u1 on v1.FInterID=u1.FInterID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
Where (v1.FCheckerID>0 or v1.FCheckerID<0) and t1.FErpClsID in(2,3,7)
AND t1.FNumber>='1.01.01.001' AND t1.FNumber<='1.01.100-0070-000' AND u1.FAdviceConsignDate>='2008-01-01' AND u1.FAdviceConsignDate<='2008-02-01'

Insert into #Data(FItemID,FPInQty)
select u1.FItemID,u1.FQty
from ICStockBill v1 inner join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
Where (v1.FCheckerID>0 or v1.FCheckerID<0) and t1.FErpClsID in(2,3,7)
and v1.FTranType in (2,5) AND t1.FNumber>='1.01.01.001' AND t1.FNumber<='1.01.100-0070-0

00' AND v1.FDate>='2008-01-01' AND v1.FDate<='2008-02-01'

Insert into #Data(FItemID,FPOutQty)
select u1.FItemID,u1.FQty
from ICStockBill v1 inner join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
Where (v1.FCheckerID>0 or v1.FCheckerID<0) and t1.FErpClsID in(2,3,7)
and v1.FTranType=21 AND t1.FNumber>='1.01.01.001' AND t1.FNumber<='1.01.100-0070-000' AND v1.FDate>='2008-01-01' AND v1.FDate<='2008-02-01'

insert into #Data1(FItemID,FPPQty,FSOQty,FPInQty,FPOutQty)
select FitemID,sum(FPPQty),sum(FSOQty),sum(FPInQty),sum(FPOutQty)
from #Data
group by FItemID
update #Data1 set FDifferent=FPPQty-FSOQty,FPercent=(case when FPPQty<>0 then (FPPQty-FSOQty)/FPPQty*100 else 0 end)

select t1.FNumber,t1.FShortNumber,t1.FModel,t1.FName,t3.FName as FUnitName,t2.*
from #Data1 t2 inner join t_ICItem t1 on t1.FItemID=t2.FItemID
inner join t_MeasureUnit t3 on t1.FUnitID=t3.FItemID
order by t1.FNumber

drop table #PZLItemx
drop table #Data1
drop table #Data
drop table #PZLItemEntry