SET NOCOUNT ON
SET ANSI_WARNINGS OFF
CREATE TABLE #Data(
FRow int default(0), --判断是车间汇总还是车间+任务单汇总
FDeptID int Null, --部门ID
FDeptName varchar(255) Null, --部门名称
FDeptType smallint default(0) null, --0：生产车间，1：委外单位，2：总计
FICMOInterID int null, --任务单内码
FICMOBillNo varchar(300) null, --任务单号
FICMOEntryID INT DEFAULT(0) NULL, --任务单分录
FCheckStatus varchar(128) NULL, --任务单状态
FPPBOMInterID INT NULL, --投料单内码
FPPBOMBillNo varchar(300) null, --投料单号
FItemId int null, --物料代码
FItemName varchar(255) null, --物料名称
FItemNumber varchar(255) null, --物料编号
FModel varchar(255) null, --物料规格型号
FPPBomEntryId int Null, --分录号
FUnitID INT NULL, --物料单位
FUnitName varchar(128) null, --物料单位名称
FCUUnitID INT NULL, --物料常用单位
FCUUnitName varchar(128) null, --物料常用单位名称
FProductId int null, --产品代码
FProductName varchar(255) null, --产品名称
FProductNumber varchar(255) null, --产品编号
FProductModel varchar(255) null, --产品规格型号
FPlanQty decimal(28,10) default(0) null, --计划生产数量
FPlanQtyCU decimal(28,10) default(0) null, --计划生产数量(常用)
FProductUnitID INT NULL, --产品单位
FProductUnitName varchar(128) null, --产品单位名称
FProductCUUnitID INT NULL, --产品常用单位
FProductCUUnitName varchar(128) null, --产品常用单位名称
FPPBOMAuxQtyMust decimal(28,10) default(0) null, --计划投料数量（常用）
FPPBOMQtyMust decimal(28,10) default(0) null, --计划投料数量（基本）
FPPBOMAuxStockQty decimal(28,10) default(0) null, --已领数量（常用）
FPPBOMStockQty decimal(28,10) default(0) null, --已领数量（基本）
FPPBOMAuxScrapQty decimal(28,10) default(0) null, --报废数量（常用）
FPPBOMScrapQty decimal(28,10) default(0) null, --报废数量（基本）
FAuxWIPQty decimal(28,10) default(0) null, --在制品数量（常用）
FWIPQty decimal(28,10) default(0) null, --在制品
数量（基本）
FSourceTranType int default(0) null, --源单类型
FTranTypeName varchar(128) null, --源单名称
FSourceInterID INT default(0) NULL, --源单内码
FSourceBillNo varchar(300) null, --源单编号
FSourceEntryID INT default(0) null, --源单分录号
FAuxFinishQty decimal(28,10) default(0) null, --实作数量（常用）
FFinishQty decimal(28,10) default(0) null, --实作数量（基本）
FConsumeInterID int default(0) null, --耗用记录内码
FConsumeEntryID int default(0) null, --耗用记录行号
FAuxConsumeQty decimal(28,10) default(0) null, --消耗数量（常用）
FConsumeQty decimal(28,10) default(0) null, --消耗数量（基本）
FAuxSupplyQty decimal(28,10) default(0) null, --补料数量（常用）
FSupplyQty decimal(28,10) default(0) null, --补料数量（基本）
FNote varchar(255) null, --
FSumSort int default(0) null,
FBillerId int null,
FBillerName varchar(128) null,
FQtyDecimal int default(0) null, --精度
FDataType int default(0) null, --数据类型，用来汇总排序 0：非汇总行 1：汇总行
FICMOType int default(0) null, --生产任务类型，0：非汇总行 1：汇总行
FProductQtyDecimal int default(0) null, --产品精度
FMrpClod INT default(0) null --委外订单行业务关闭标识
)
DECLARE @_UPDATEWHENSAVE AS INT--库存更新方式
DECLARE @_UShortNumber AS INT--启用短代码
DECLARE @_WIPDeductMode AS INT--在制品扣减方式
SET @_UShortNumber = 1
SELECT @_UPDATEWHENSAVE = FValue FROM t_SystemProfile WHERE FCategory='IC' and FKey='UPSTOCKWHENSAVE'
SELECT @_WIPDeductMode = FValue FROM t_SystemProfile WHERE FCategory='SH' and FKey='WIPDeductMode'
--取任务单/委外任务单对应的消耗记录
INSERT INTO #DATA
(
FDeptID, FICMOInterID,FICMOEntryID, FPPBOMInterID,FPPBomEntryID,
FProductID,FItemID, FICMOBillNo, FPPBOMBillNo, FCheckStatus
关于春节古诗词
,FMrpClod, FRow
)
SELECT icmo.FSupplyID, icmo.FInterID,icmoEntry.FEntryID, pp.FInterID AS FPPBomInterID, ppe.FEntryID AS FPPBomEntryID
,icmoEntry.FItemID, ppe.FItemID, icmo.FBillNo, pp.FBillNo, icmo.FStatus
,icmoEntry.FMrpClod, 123
FROM ICSubContract icmo
INNER JOIN ICSubContractEntry icmoEntry ON icmo.FInterID = icmoEntry.FInterID
澳大利亚英文INNER JOIN PPBom pp ON pp.FICMOInterID = icmo.FInterID AND pp.FOrderEntryID = icmoEntry.FEntryID
INNER JOIN PPBomEntry ppe ON pp.FInterID = ppe.FInterID
WHERE icmo.FCancelLation=0 AND ppe.FMaterielType IN (371,375) --物料类型
AND icmo.FBillNO>='EO20120111002' AND icmo.FBillNo<='EO20120111002' AND icmo.FStatus IN
(1,2,3) And icmo.FCancelLation=0
AND convert(datetime,convert(varchar(10),icmo.FDate,121)) >='2011-12-01'
欢迎致辞AND conv
ert(datetime,convert(varchar(10),icmo.FDate,121)) <='2012-08-01'
INSERT INTO #DATA
(
FDeptID, FICMOInterID,FICMOEntryID, FPPBOMInterID,FPPBomEntryID,
FProductID,FItemID, FICMOBillNo, FPPBOMBillNo, FCheckStatus
,FConsumeInterID,FConsumeEntryID
)
SELECT v1.FDeptID, CASE WHEN ISNULL(v1.FPPBOMInterID,0)=0 THEN t11.FICMOInterID ELSE v1.FICMOInterID END,
CASE WHEN ISNULL(v1.FPPBOMInterID,0)=0 THEN t11.FICMOEntryID ELSE v1.FICMOEntryID END,
v1.FPPBOMInterID, v1.FPPBomEntryID,
CASE WHEN ISNULL(v1.FPPBOMInterID,0)=0 THEN t11.FProductID ELSE v1.FProductID END AS FProductID,
CASE WHEN ISNULL(v1.FPPBOMInterID,0)=0 THEN t11.FItemID ELSE v1.FItemID END AS FItemID,
CASE WHEN ISNULL(v1.FPPBOMInterID,0)=0 THEN t11.FICMOBillNO ELSE v1.FICMOBillNO END,
v1.FPPBOMBillNo, v1.FCheckStatus
,t11.FInterID, t11.FEntryID
FROM
股票平仓
#DATA v1
FULL OUTER JOIN
(
SELECT con.FInterID, cone.FEntryID, con.FSourceInterID, con.FSourceEntryID, con.FSourceBillNO, con.FSourceTranType,
cone.FQtyConsume,con.FBillerId, cone.FNote,con.FICMOInterID, con.FICMOEntryID, cone.FItemID AS FItemID,cone.FPPBomEntryID,
con.FPPBOMInterID, con.FItemID AS FProductID, con.FICMOBillNO AS FICMOBillNO
FROM ICShop_ItemConsume con
INNER JOIN ICShop_ItemConsumeEntry cone ON con.FInterID = cone.FInterID
WHERE con.FCancellation = 0 AND con.FSourceTranType = 5
) t11 ON v1.FICMOInterID = t11.FICMOInterID AND v1.FICMOEntryID = t11.FICMOEntryID
AND v1.FPPBOMInterID = t11.FPPBOMInterID AND v1.FPPBOMEntryID = t11.FPPBOMEntryID
儒林外史第一回概括
WHERE v1.FRow=123--t11.FICMOEntryID IN (SELECT DISTINCT FICMOEntryID FROM #DATA t9521 WHERE t9521.FICMOInterID = t11.FICMOInterID AND t9521.FRow = 123)
--AND t11.FICMOInterID IN ( SELECT DISTINCT FICMOInterID FROM #DATA WHERE FRow = 123)
UPDATE v1
SET v1.FDeptID = icmo.FSupplyID
FROM #DATA v1
INNER JOIN ICSubContract icmo ON v1.FICMOInterID = icmo.FInterID
DELETE FROM #DATA WHERE FRow = 123
--更新材料耗用的相关信息
UPDATE v1
SET --v1.FConsumeInterID = t1.FInterID, FConsumeEntryID = t1.FEntryID,
v1.FItemID = t1.FItemID,
v1.FSourceInterID = t1.FSourceInterID,
v1.FSourceEntryID = t1.FSourceEntryID,
v1.FSourceBillNO = t1.FSourceBillNO,
v1.FSourceTranType = t1.FSourceTranType,
v1.FConsumeQty = t1.FQtyConsume,
v1.FBillerId = t1.FBillerId,
v1.FBillerName = u.FName,
v1.FNote = t1.FNote
FROM #DATA v1
INNER JOIN
(
SELECT con.FInterID, cone.FEntryID, con.FSourceInterID, con.FSourceEntryID, con.FSourceBillNO, con.FSourceTranType,
cone.FQtyConsume,con.FBillerId, cone.FNote,con.FICMOInterID, con.FICMOEntryID, cone.FItemID,cone.FPPBomEntryID
FROM ICShop_ItemConsume con
INNER JOIN ICShop_ItemConsumeEntry cone ON con.FInterID = cone.FInterID
WHERE con.FCancellation = 0
) t1 ON v1.FICMOInterID = t1.FICMOInterID AND v1.FICMOEntryID = t1.FICMOEntryID AND v1.FConsumeInterID = t1.FInterID AND v1.FConsumeEntryID = t1.FEntryID
LEFT JOIN t_Ur u ON t1.FBillerId = u.FUrId
--根据入
库单的状态，更新消耗数量
UPDATE v1
SET v1.FConsumeQty =
(
CASE WHEN @_UPDATEWHENSAVE = 0 THEN
CASE WHEN ISNULL(t1.FStatus,0) = 0 THEN 0 ELSE ISNULL(v1.FConsumeQty, 0) END
ELSE ISNULL(v1.FConsumeQty, 0)
END
)
FROM #DATA v1
INNER JOIN
(
SELECT a.FInterID, b.FICMOInterID, b.FEntryID, b.FOrderInterID, b.FOrderEntryID, a.FStatus, a.FBillNo
FROM ICStockBill a
INNER JOIN ICStockBillEntry b ON a.FInterID = b.FInterID
Where a.FCancelLation = 0
) t1 ON v1.FICMOInterID = t1.FOrderInterID AND v1.FICMOEntryID = t1.FOrderEntryID AND v1.FSourceInterID = t1.FInterID AND v1.FSourceEntryID = t1.FEntryID
WHERE v1.FSourceInterID > 0 AND v1.FSourceTranType = 5
--关联投料单取计划投料数, 在制品数量, 已领数量，补料数量，报废数量, 消耗数量
--常用单位数量, 物料信息, 部门信息
UPDATE v1 SET
--产品信息
指南针怎么画
v1.FProductName = itemProd.FName, v1.FProductModel = itemProd.FModel, v1.FProductQtyDecimal = itemProd.FQtyDecimal
,v1.FProductNumber = CASE WHEN @_UShortNumber = 1 THEN itemProd.FShortNumber ELSE itemProd.FNumber END
,v1.FProductUnitID = itemProd.FUnitID, v1.FProductUnitName = unitProd.FName
,v1.FProductCUUnitID = unitProdCU.FMeasureUnitID, v1.FProductCUUnitName = unitProdCU.FName
--物料信息
,v1.FItemName = item.FName, v1.FModel = item.FModel, v1.FQtyDecimal = item.FQtyDecimal
,v1.FItemNumber = CASE WHEN @_UShortNumber = 1 THEN item.FShortNumber ELSE item.FNumber END
,v1.FUnitID = item.FUnitID, v1.FUnitName = unitItem.FName
,v1.FCUUnitID = unitItemCU.FMeasureUnitID, v1.FCUUnitName = unitItemCU.FName
,v1.FPPBOMQtyMust = ISNULL(ppe.FQtyMust,0)--计划投料数
,v1.FWIPQty = ISNULL(ppe.FWIPQty,0)--在制品数
,v1.FPPBOMStockQty = ISNULL(ppe.FStockQty,0)--已领数量
,v1.FSupplyQty = ISNULL(ppe.FQtySupply,0)--补料数量
,v1.FPPBOMScrapQty = ISNULL(ppe.FDiscardQty,0)--作废数量
,v1.FPPBOMAuxQtyMust = ROUND(ISNULL(ppe.FQtyMust,0)/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)
保税区物流,v1.FAuxWIPQty = ROUND(ISNULL(ppe.FWIPQty,0)/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)
,
v1.FPPBOMAuxStockQty = ROUND(ISNULL(ppe.FStockQty,0)/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)
,v1.FAuxSupplyQty = ROUND(ISNULL(ppe.FQtySupply,0)/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)
,v1.FPPBOMAuxScrapQty = ROUND(ISNULL(ppe.FDiscardQty,0)/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)
,v1.FAuxConsumeQty = ROUND(v1.FConsumeQty/(CASE WHEN isnull(unitItemCU.FCoefficient,0)=0 THEN 1 ELSE isnull(unitItemCU.FCoefficient,0) END),item.FQtyDecimal)--消耗数量
FROM #DATA v1
INNER JOIN t_ICItem itemProd ON itemPr
od.FItemID = v1.FProductID
INNER JOIN t_ICItem item ON item.FItemID = v1.FItemID
LEFT JOIN t_MeasureUnit unitProd ON unitProd.FMeasureUnitID = itemProd.FUnitID
LEFT JOIN t_MeasureUnit unitItem ON unitItem.FMeasureUnitID = item.FUnitID
LEFT JOIN t_MeasureUnit unitProdCU ON unitProdCU.FMeasureUnitID = itemProd.FOrderUnitID
LEFT JOIN t_MeasureUnit unitItemCU ON unitItemCU.FMeasureUnitID = item.FOrderUnitID--委外统一采用采购单位！！！
LEFT JOIN PPBomEntry ppe ON v1.FPPBomInterID = ppe.FInterID AND v1.FPPBomEntryID = ppe.FEntryID AND ppe.FOrderEntryID>0--之关联委外订单对应的投料单
--单据类型名称
UPDATE v1
SET FTranTypeName = t1.FName_CHS
FROM #DATA v1
INNER JOIN ICClassType t1 ON t1.FID = CASE WHEN v1.FSourceTranType < 10000 THEN -v1.FSourceTranType ELSE v1.FSourceTranType END
WHERE v1.FSourceTranType IS NOT NULL AND v1.FSourceTranType <> 0
--等级品在制品为0
--入库的等级品
UPDATE v1
SET v1.FWIPQty = 0, v1.FAuxWIPQty = 0--在制品数
FROM #DATA v1
INNER JOIN
(
SELECT (CASE WHEN t1.FSourceTranType = 1007105 THEN t1.FOrderInterID
ELSE t1.FICMOInterID END) AS FICMOInterID,
t1.FPPBomEntryID, t1.FEntryID AS FSourceEntryID, CASE WHEN t1.FSourceTranType = 1007105 THEN t1.FOrderEntryID ELSE 0 END AS FICMOEntryID
FROM ICStockBillEntry t1
INNER JOIN PPBomEntry p1 ON p1.FICMOInterID = CASE WHEN t1.FSourceTranType = 1007105 THEN t1.FOrderInterID
ELSE t1.FICMOInterID END
AND t1.FPPBomEntryID = p1.FEntryID
WHERE t1.FPPBomEntryID > 0 AND p1.FMaterielType = 374
) t11 ON v1.FICMOInterID = t11.FICMOInterID AND v1.FSourceEntryID = t11.FSourceEntryID AND v1.FICMOEntryID = t11.FICMOEntryID
WHERE v1.FSourceInterID > 0
-
-汇报的等级品在制品为0, 任务单汇报， 工序汇报, 流转卡汇报
--委外工序接收单, 委外入库
--按照任务单小计, 委外的先按照委外订单分录汇总
INSERT INTO #Data(FDeptID, FICMOInterID, FICMOEntryID, FProductID, --FPPBOMAuxQtyMust, FPPBOMQtyMust,
--FPPBOMAuxStockQty,FPPBOMStockQty,
--FPPBOMAuxScrapQty, FPPBOMScrapQty ,
--FAuxWIPQty, FWIPQty,
FAuxConsumeQty,FConsumeQty, FSumSort, FProductQtyDecimal, FQtyDecimal)
SELECT FDeptID, FICMOInterID, FICMOEntryID, FProductID, --SUM(ISNULL(FPPBOMAuxQtyMust,0)), SUM(ISNULL(FPPBOMQtyMust,0)),
--SUM(ISNULL(FPPBOMAuxStockQty,0)), SUM(ISNULL(FPPBOMStockQty,0)),
--SUM(ISNULL(FPPBOMAuxScrapQty,0)), SUM(ISNULL(FPPBOMScrapQty,0)) ,
--SUM(ISNULL(FAuxWIPQty,0)), SUM(ISNULL(FWIPQty,0)),
SUM(ISNULL(FAuxConsumeQty,0)), SUM(ISNULL(FConsumeQty,0)), 100, MAX(FProductQtyDecimal), MAX(FQtyDecimal)
FROM #DATA
GROUP BY FDeptID, FICMOInterID, FICMOEntryID, FProductID
--更新实做数/任务单, 计划生产数/任务单
--要不要处理参数切换的情况？
--如果做过参数
切换，先为入库时扣减，已入库，然后又切换成汇报时扣减 反之亦然
UPDATE v1
SET v1.FPlanQty = icmoEntry.FQty --计划生产数量
,v1.FPlanQtyCU = ROUND(icmoEntry.FQty/isnull(unitProdCU.FCoefficient,1),ISNULL(itemProd.FQtyDecimal, 0)) --计划生产数量（常用）
,v1.FFinishQty = icmoEntry.FStockQty
,v1.FProductNumber = CASE WHEN @_UShortNumber = 1 THEN
CASE v1.FSumSort WHEN 100 THEN itemProd.FShortNumber + '(小计)' ELSE itemProd.FShortNumber END
ELSE
CASE v1.FSumSort WHEN 100 THEN itemProd.FNumber + '(小计)' ELSE itemProd.FNumber END
END
,v1.FProductQtyDecimal = itemProd.FQtyDecimal,v1.FICMOBillNO = icmo.FBillNo
FROM #DATA v1
INNER JOIN ICSubContract icmo ON v1.FICMOInterID = icmo.FInterID
INNER JOIN ICSubContractEntry icmoEntry ON icmoEntry.FInterID = icmo.FInterID AND v1.FICMOEntryID = icmoEntry.FEntryID AND v1.FProductID = icmoEntry.FItemID
LEFT JOIN t_ICItem itemProd ON itemProd.FItemID = icmoEntry.FItemID
特拉华大学LEFT JOIN t_MeasureUnit unitProdCU ON unitProdCU.FMeasureUnitID = v1.FProductCUUnitID
--普通件的产成品数量再加上等级品的实做数
--委外入库，等级品汇报
UPDATE v1
SET v1.FFinishQty = ISNULL(v1.FFinishQty, 0) + ISNULL(v2.FQty, 0)
FROM #DATA v1
INNER JOIN
(
SELECT icmo.FInterID AS FICMOInterID, (t1.FQty/(p1.FQtyScrap))*ISNULL(unit.FCoefficient,1) AS FQty, icmo.FEntryID AS FICMOEntryID
FROM ICStockBillEntry t1
INNER JOIN PPBomEntry p1 ON t1.FOrderInterID = p1.FICMOInterID AND t1.FPPBomEntryID = p1.FEntryID AND p1.FMaterielType = 374
INNER JOIN ICSubContractEntry icmo ON icmo.FInterID = t1.FOrderInterID AND t1.FOrderEntryID = icmo.FEntryID
INNER JOIN ICStockBill t11 ON t1.FInterID = t11.FInterID AND t11.FCancelLation = 0
LEFT JOIN t_MeasureUnit unit ON icmo.FUnitID = unit.FMeasureUnitID
WHERE t1.FSourceInterID IN (SELECT DISTINCT FICMOInterID FROM #DATA)
) v2 ON v1.FICMOInterID = v2.FICMOInterID AND v2.FICMOEntryID = v1.FICMOEntryID
UPDATE v1
SET v1.FFinishQty = ISNULL(v2.FQty, 0)
FROM #DATA v1
INNER JOIN ICStockBillEntry v2 ON v1.FSourceInterID = v2.FInterID AND v1.FSourceEntryID = v2.FEntryID
WHERE v1.FSourceTranType = 5
UPDATE v1
SET v1.FFinishQty = ISNULL(v2.FQty, 0)
FROM #DATA v1
INNER JOIN
(
SELECT icmo.FInterID AS FICMOInterID,t1.FInterID, t1.FEntryID, (t1.FQty/(p1.FQtyScrap))*ISNULL(unit.FCoefficient,1) AS FQty, icmo.FEntryID AS FICMOEntryID
FROM ICStockBillEntry t1
INNER JOIN PPBomEntry p1 ON t1.FOrderInterID = p1.FICMOInterID AND t1.FPPBomEntryID = p1.FEntryID AND p1.FMaterielType = 374
INNER JOIN ICSubContractEntry icmo ON icmo.FInterID = t1.FOrderInterID AND t1.FOrderEntryID = icmo.FEntryID
INNER JOIN ICStockBill t11 ON t1.FInterID = t11.FInterID AND t11.FCancelLation = 0
LEFT JOIN t_MeasureUnit unit ON icmo.FUnitID = unit.FMeasureUnitID
WHERE t1.FSourceInte
