USE [wkim1102]
GO
/****** Object: StoredProcedure [dbo].[WK_Financial_age] Script Date: 2019/12/27 15:26:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WK_Financial_age]
@Org varchar(50),
@Period varchar(30),
@Items varchar(50),
@wh varchar(50),
@xianmu varchar(100),
@BegDate varchar(30),
@EndDate datetime
as
begin
SET NOCOUNT ON
--库存表
--select project.Code project,ItemOwnOrg,StoreQty,ItemInfo_ItemID,ItemInfo_ItemName,ItemInfo_ItemCode,wh
--into #trans
-- from InvTrans_WhQoh wh
-- left join CBO_Project project on wh.Project=project.ID
-- left join Base_Organization org on wh.ItemOwnOrg=org.ID
-- left join CBO_Wh whs on wh.Wh=whs.ID
-- where StoreQty!=0 and
-- org.Code=@Org
-- and 1=case when isnull(@Items,'')='' then 1 when ItemInfo_ItemCode=@Items then 1 else 0 end
--and whs.ID in(case when (@xianmu='板材数量' or @xianmu='板材金额') then (select whs.ID where whs.ID IN('1001506025044619','1001506025044627','1001506025044635','1001506025044611'
-- ,'1001403062214690'
-- ,'1001403062214983','1001702170258093','1001502172634111'
-- ,'1001909081671392','1001909081692600'
-- ,'1001403062215027','1001507265439432','1001507265439484'
-- ,'1002108071009778'
-- ) and whs.Org=org.ID)
-- when @xianmu='铜排件金额' then (select whs.ID where whs.ID IN('1001403062214746','1001403062214794'
-- ,'1001403062214698'
-- ,'1001403062214875','1001403062214859','1002005185180121'
-- ,'1001909081671335','1001909081671552'
-- ,'1001403062215044','1001403062215061','1001403062215035'
-- ,'1002108071009778'
-- ) and whs.Org=org.ID)
-- when @xianmu='元器件金额' then (select whs.ID where whs.ID IN('1001403062214762','1001403062214770'
-- ,'1001403062214698'
-- ,'1001403062214843'
-- ,'1001909081671127'
-- ,'1001403062215019','1001506024983053'
-- ,'1002012058415993','1002012058410355'
-- ) and whs.Org=org.ID)
-- when @xianmu='库存商品金额' then (select whs.ID where whs.ID IN('1001403062214835',
-- '1001403062214730',
-- '1001403062215009',
-- '1001909081686245'
-- ,'1001403062215119'
-- ,'1002012058428258','1002108071007708','1002012058428360'
-- ) and whs.Org=org.ID)
-- when @xianmu='其它金额' then (select whs.ID where whs.ID not in('1001506025044619','1001506025044627','1001506025044635','1001506025044611'
-- ,'1001403062214690'
-- ,'1001403062214983','1001702170258093','1001502172634111'
-- ,'1001909081671392','1001909081692600'
-- ,'1001403062215027','1001507265439432','1001507265439484'
-- ,'1002108071009778'
-- ,'1001403062214746','1001403062214794'
-- ,'1001403062214698'
-- ,'1001403062214875','1001403062214859','1002005185180121'
-- ,'1001909081671335','1001909081671552'
-- ,'1001403062215044','1001403062215061','1001403062215035'
-- ,'1002108071009778'
-- ,'1001403062214762','1001403062214770'
-- ,'1001403062214698'
-- ,'1001403062214843'
-- ,'1001909081671127'
-- ,'1001403062215019','1001506024983053'
-- ,'1002012058415993','1002012058410355'
-- ,'1001403062214835',
-- '1001403062214730',
-- '1001403062215009',
-- '1001909081686245'
-- ,'1001403062215119'
-- ,'1002012058428258','1002108071007708','1002012058428360'
-- ) and whs.Org=org.ID)
-- ELSE '' END)
select project.Code projectr,orgtr.Name OrgName,LEFT(Convert(varchar(10),period.FromDate,120),7) Period,wh.ID WhID,whtr.Name WhName,
itemtype.Code itemtypeCode,itemtypetr.[Name] as itemtypeName,A1.SPECS,A5S.Name CU_Name,
A1.[Code] as [ItemID_Code], A1.[Name] as [ItemID_Name],
sum(A.[CostSubBalQty]) as [INVTotal_CostSubBalQty],
sum( case when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end ) as [MF_BalMoney],
A6.[Code] as [Org_Code]
into #project
from InvTrans_AccountPeriodLine as A
left join CBO_Project project on A.Project=project.ID
left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])
left join [InvTrans_AccountPeriodLineCost] as A2 on (A.[ID] = A2.[AccountPeriodLine])
left join [CBO_CostType] as A3 on (A2.[CostType] = A3.[ID])
left join [Base_UOM] as A5 on (A.[CostUOM] = A5.[ID])
left join Base_UOM_Trl A5S on A5S.ID=A5.ID and A5S.SysMLFlag='zh-CN'
left join [Base_Organization] as A6 on (A.[Org] = A6.[ID])
left join Base_Organization_Trl orgtr on A6.ID=orgtr.ID and orgtr.SysMLFlag='zh-CN'
left join [Base_SetofBooks] as A7 on (A2.[SOB] = A7.[ID])
left join CBO_Wh wh on wh.ID=A.Wh
left join CBO_Wh_Trl whtr on wh.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
left join Base_AccountingPeriod period on A.AccountPeriod=period.ID
left join [CBO_Category] as itemtype on (A1.[StockCategory] = itemtype.[ID])
left join [CBO_Category_Trl] as itemtypetr on (itemtypetr.SysMlFlag = 'zh-CN') and (itemtypetr.[ID] = itemtype.[ID])
where (((((A2.[IsMainSOB] = 1) and (A2.[IsAccCostType] = 1) and (A7.[Code] = @Org))
and A6.[Code]=@Org) and A.[QtyPriceDealFlg] in (0, 2, 4))) --and (A1.[Code] = N'15.5504.3206')
--and A1.[Code] in(select ItemInfo_ItemCode from #trans)
and LEFT(Convert(varchar(10),period.FromDate,120),7)=@Period
and wh.ID in (case when (@xianmu='板材数量' or @xianmu='板材金额') then (select wh.ID where wh.ID IN(1001611020221442,1001611020221602,1001611020221612
,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
,1001611020220535
,1001612200000271
) and wh.Org=A6.ID)
when @xianmu='铜排件金额' then (select wh.ID where wh.ID IN(1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
,1001611020221452,1001611020221462,1001611020221482
,1001610130496757,1001610130496758,1001611020221166,1001611020221226
,1001611020220546
,1001612200000271
) and wh.Org=A6.ID)
when @xianmu='元器件金额' then (select wh.ID where wh.ID IN(1001611020221432,1001611020221572,1001611020221582
,1001611011087574,1001611011087677
,1001610130496747,1001611020221186,1001610130496795,1001611020221196
,1001611020220546
,1001611020209909,1001611020209984
) and wh.Org=A6.ID)
when @xianmu='库存商品金额' then (select wh.ID where wh.ID IN(1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
1001611020220003,1001611020220058,1001611020220069,1001611020220080,1001611011087884,1001702073047087
) and wh.Org=A6.ID)
when @xianmu='售后金额' then (select wh.ID where wh.ID IN(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371
) and wh.Org=A6.ID)
when @xianmu='所有库存' then (select wh.ID where wh.ID IN(1001611020221156,
1001611020221166,
1001611020221176,
1001611020221186,
1001611020221196,
1001611020221206,
1001611020221216,
1001611020221226,
1001611020221236,
1001611020221246,
1001611020221256,
1001611020221266,
1001611020221276,
1001611020221286,
1001611020221296,
1001611020221306,
1001611020221320,
1001611020221330,
1001611020221340,
1001611020221350,
1001611020221360,
1001611020221370,
1001611020221380,
1001611020221390,
1001611020221400,
1001611020221410,
1001701140216358,
1001712153504084,
1001611011087575,
1001611011087657,
1001611011087667,
1001611011087717,
1001611011087738,
1001611011087739,
1001611011087818,
1001611011087847,
1001611011087867,
1001611011087884,
1001612290244422,
1001612290244438,
1001701041682704,
1001701041682906,
1001701041682945,
1001701041683096,
1001701041683135,
1001701041683171,
1001702073047087,
1001611020221432,
1001611020221442,
1001611020221452,
1001611020221462,
1001611020221472,
1001611020221482,
1001611020221492,
1001611020221502,
1001611020221512,
1001611020221532,
1001611020221572,
1001611020221582,
1001611020221592,
1001611020221602,
1001611020221612,
1001611020221632,
1001611020221642,
1001611020221652,
1001611020221672,
1001611020221682,
1001702073046092,
1001709181534949,
1001801300744738,
1001806231486379,
1001904280561252,
1001905245084482,
1001905251806348,
1001905251808792
) and wh.Org=A6.ID)
when @xianmu='其它金额' then (select wh.ID where wh.ID not in(1001611020221442,1001611020221602,1001611020221612
,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
,1001611020220535
,1001612200000271
,1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
,1001611020221452,1001611020221462,1001611020221482
,1001610130496757,1001610130496758,1001611020221166,1001611020221226
,1001611020220546
,1001612200000271
,1001611020221432,1001611020221572,1001611020221582
,1001611011087574,1001611011087677
,1001610130496747,1001611020221186,1001610130496795,1001611020221196
,1001611020220546
,1001611020209909,1001611020209984
,1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
1001611020220003,1001611020220058,1001611020220069,1001611020220080
,001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371
) and wh.Org=A6.ID)
ELSE '' END)
--and A1.[Code] in(select ItemInfo_ItemCode from #trans)
group by project.Code,orgtr.Name, LEFT(Convert(varchar(10),period.FromDate,120),7) ,wh.ID,whtr.Name,itemtype.Code,
itemtypetr.[Name],A1.[Code], A1.[Name], A1.SPECS,A5S.Name, A6.[Code] having sum(A.[CostSubBalQty])>0 and sum( case when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end )>0
---- --剔除以下单据类型2016.3.16
-- select * from InvDoc_CostAdjustDocType_Trl where Name='存货成本调整[默认]'
-- --售后增加单据类型
-- --TransIn001 调入单（组间调拨）
-- --TransIn002 调入单（仓库转储）
----推算三个月内收数据,以入库来推算库龄;
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #TThree
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-2,@Period+'-01'),120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,0,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #Three from #TThree group by Org,Wh,ItemCode,IttemName
--推算三到六个月收数据;
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #SSix
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-5,@Period+'-01'),120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-3,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #Six from #SSix group by Org,Wh,ItemCode,IttemName
--推算六到十二个月收数据,
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #tthreety
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-11,@Period+'-01'),120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-6,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #threety from #tthreety group by Org,Wh,ItemCode,IttemName
--推算一年到二年收数据,
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #onetwoyear
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-23,@Period+'-01'),120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-12,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #onetwoyearresult from #onetwoyear group by Org,Wh,ItemCode,IttemName
--推算二年到三年收数据,
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #twothreeyear
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-35,@Period+'-01'),120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-24,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #twothreeyearresult from #twothreeyear group by Org,Wh,ItemCode,IttemName
--推算十二月以上收数据,改为3年以上
select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType'))
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #tthreetys
from InvTrans_TransLine as A
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
where (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
--and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
--and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
and A.DocType_EntityID not in(select A.DocType_EntityID where A.DocType_EntityID in (case when A6.ID in( 1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
,1001611020220611
,1001611011087576
,1001611020221552
,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11) then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),'2010-01-01',120),7)
and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-36,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #threetys from #tthreetys group by Org,Wh,ItemCode,IttemName
--————————————————————————————————————————————————————————————————————————————————————————————————————————————
--以上往后退月得出的收金额与数量
select orgtr.Name OrgName,LEFT(Convert(varchar(10),period.FromDate,120),7) Period,wh.ID WhID,whtr.Name WhName,
itemtype.Code itemtypeCode,itemtypetr.[Name] as itemtypeName,A1.SPECS,A5S.Name CU_Name,
A1.[Code] as [ItemID_Code], A1.[Name] as [ItemID_Name],
sum(A.[CostSubBalQty]) as [INVTotal_CostSubBalQty],
sum( case when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end ) as [MF_BalMoney],
A6.[Code] as [Org_Code]
into #ItemMan
from InvTrans_AccountPeriodLine as A
left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])
left join [InvTrans_AccountPeriodLineCost] as A2 on (A.[ID] = A2.[AccountPeriodLine])
left join [CBO_CostType] as A3 on (A2.[CostType] = A3.[ID])
left join [Base_UOM] as A5 on (A.[CostUOM] = A5.[ID])
left join Base_UOM_Trl A5S on A5S.ID=A5.ID and A5S.SysMLFlag='zh-CN'
left join [Base_Organization] as A6 on (A.[Org] = A6.[ID])
left join Base_Organization_Trl orgtr on A6.ID=orgtr.ID and orgtr.SysMLFlag='zh-CN'
left join [Base_SetofBooks] as A7 on (A2.[SOB] = A7.[ID])
left join CBO_Wh wh on wh.ID=A.Wh
left join CBO_Wh_Trl whtr on wh.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
left join Base_AccountingPeriod period on A.AccountPeriod=period.ID
left join [CBO_Category] as itemtype on (A1.[StockCategory] = itemtype.[ID])
left join [CBO_Category_Trl] as itemtypetr on (itemtypetr.SysMlFlag = 'zh-CN') and (itemtypetr.[ID] = itemtype.[ID])
where (((((A2.[IsMainSOB] = 1) and (A2.[IsAccCostType] = 1) and (A7.[Code] = @Org))
and A6.[Code]=@Org) and A.[QtyPriceDealFlg] in (0, 2, 4))) --and (A1.[Code] = N'15.5504.3206')
--and A1.[Code] in(select ItemInfo_ItemCode from #trans)
and LEFT(Convert(varchar(10),period.FromDate,120),7)=@Period
and wh.ID in (case when (@xianmu='板材数量' or @xianmu='板材金额') then (select wh.ID where wh.ID IN(1001611020221442,1001611020221602,1001611020221612
,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
,1001611020220535
,1001612200000271
) and wh.Org=A6.ID)
when @xianmu='铜排件金额' then (select wh.ID where wh.ID IN(1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
,1001611020221452,1001611020221462,1001611020221482
,1001610130496757,1001610130496758,1001611020221166,1001611020221226
,1001611020220546
,1001612200000271
) and wh.Org=A6.ID)
when @xianmu='元器件金额' then (select wh.ID where wh.ID IN(1001611020221432,1001611020221572,1001611020221582
,1001611011087574,1001611011087677
,1001610130496747,1001611020221186,1001610130496795,1001611020221196
,1001611020220546
,1001611020209909,1001611020209984
) and wh.Org=A6.ID)
when @xianmu='库存商品金额' then (select wh.ID where wh.ID IN(1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
1001611020220003,1001611020220058,1001611020220069,1001611020220080,1001611011087884,1001702073047087
) and wh.Org=A6.ID)
when @xianmu='售后金额' then (select wh.ID where wh.ID IN(1001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371
) and wh.Org=A6.ID)
when @xianmu='所有库存' then (select wh.ID where wh.ID IN(1001611020221156,
1001611020221166,
1001611020221176,
1001611020221186,
1001611020221196,
1001611020221206,
1001611020221216,
1001611020221226,
1001611020221236,
1001611020221246,
1001611020221256,
1001611020221266,
1001611020221276,
1001611020221286,
1001611020221296,
1001611020221306,
1001611020221320,
1001611020221330,
1001611020221340,
1001611020221350,
1001611020221360,
1001611020221370,
1001611020221380,
1001611020221390,
1001611020221400,
1001611020221410,
1001701140216358,
1001712153504084,
1001611011087575,
1001611011087657,
1001611011087667,
1001611011087717,
1001611011087738,
1001611011087739,
1001611011087818,
1001611011087847,
1001611011087867,
1001611011087884,
1001612290244422,
1001612290244438,
1001701041682704,
1001701041682906,
1001701041682945,
1001701041683096,
1001701041683135,
1001701041683171,
1001702073047087,
1001611020221432,
1001611020221442,
1001611020221452,
1001611020221462,
1001611020221472,
1001611020221482,
1001611020221492,
1001611020221502,
1001611020221512,
1001611020221532,
1001611020221572,
1001611020221582,
1001611020221592,
1001611020221602,
1001611020221612,
1001611020221632,
1001611020221642,
1001611020221652,
1001611020221672,
1001611020221682,
1001702073046092,
1001709181534949,
1001801300744738,
1001806231486379,
1001904280561252,
1001905245084482,
1001905251806348,
1001905251808792
) and wh.Org=A6.ID)
when @xianmu='其它金额' then (select wh.ID where wh.ID not in(1001611020221442,1001611020221602,1001611020221612
,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
,1001611020220535
,1001612200000271
,1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
,1001611020221452,1001611020221462,1001611020221482
,1001610130496757,1001610130496758,1001611020221166,1001611020221226
,1001611020220546
,1001612200000271
,1001611020221432,1001611020221572,1001611020221582
,1001611011087574,1001611011087677
,1001610130496747,1001611020221186,1001610130496795,1001611020221196
,1001611020220546
,1001611020209909,1001611020209984
,1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
1001611020220003,1001611020220058,1001611020220069,1001611020220080
,001611011087777,1001611011087727,1001611011087727
,1001610130496922,1001611020221296,1001610130497043,1001611020221330
,1001611020221632
,1001612200000371
) and wh.Org=A6.ID)
ELSE '' END)
and A1.[Code] in(select [ItemID_Code] from #project)
group by orgtr.Name, LEFT(Convert(varchar(10),period.FromDate,120),7) ,wh.ID,whtr.Name,itemtype.Code,
itemtypetr.[Name],A1.[Code], A1.[Name], A1.SPECS,A5S.Name, A6.[Code] having sum(A.[CostSubBalQty])>0 and sum( case when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end )>0
--select *
-- ,isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreeMoney,
--isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) SixMoney,
--isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetyMoney,
--isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetysMoney,
--isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreeQty,
--isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) SixQty,
--isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetyQty,
--isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetysQty
--into #TuiMain
--from #ItemMan a
-- select *
-- ,isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code),0) ThreeMoney,
--isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code),0) SixMoney,
--isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code),0) ThreetyMoney,
--isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code),0) ThreetysMoney,
--isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code),0) ThreeQty,
--isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code),0) SixQty,
--isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code ),0) ThreetyQty,
--isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code ),0) ThreetysQty
--into #TuiMain
--from #ItemMan a
select *,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreeMoney,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) SixMoney,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreetyMoney,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) onetwoyearresultMoney,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) twothreeyearresultMoney,
(case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreetysMoney,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreeQty,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)SixQty,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreetyQty,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) onetwoyearresultQty,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) twothreeyearresultQty,
(case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreetysQty
into #TuiMain
from #ItemMan a
--推算三个月内与三至六个月数据
select OrgName,Period,WhID,WhName,itemtypeCode,itemtypeName,ItemID_Code,ItemID_Name,SPECS,CU_Name,INVTotal_CostSubBalQty,MF_BalMoney,
Org_Code, ThreeMoney,SixMoney,ThreetyMoney,onetwoyearresultMoney,twothreeyearresultMoney,ThreetysMoney,ThreeQty,SixQty,ThreetyQty,onetwoyearresultQty,twothreeyearresultQty,ThreetysQty,MF_BalMoney BalMoney,
--三个月内算法：如果结存数大于三个月内则为结存数，如果三个月内少于结存数的则为三个月内数据；when INVTotal_CostSubBalQty=ThreeQty then MF_BalMoney 当数量相等时金额等于当前金额
(case when ThreeMoney>0 then
(case when INVTotal_CostSubBalQty=ThreeQty then MF_BalMoney when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)
else 0
end) TMoney
,
(case when ThreeQty>0 then
(case when INVTotal_CostSubBalQty>ThreeQty then ThreeQty when INVTotal_CostSubBalQty<=ThreeQty then INVTotal_CostSubBalQty else 0 end) else 0 end) TQyt
into #STA
from #TuiMain
--三到六个月算法：如果结存数据减去三个月内数大于零，则为结存数据减去三个月内数之和（前提三到六个月数大于零：如果结存数减去三个月之和大于六个月数据则为六个月内数据，如果少于六个月数据则为结存减去三个月数据），
--(case when MF_BalMoney-(case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)>0 then
----当六个月数大于零
--(case when ThreeMoney>0 then
----如果结存减去三个月数小于等于零怎则为零，如果结存减去三个月内数据大于零（如果结存减去三个月数据大于等于六个月数据则为六个月数据，如果结存加减去三个月数小于六个月数据则为结存减去三个月数
--(case when MF_BalMoney- (case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)>=SixMoney
-- then SixMoney when MF_BalMoney- (case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)<SixMoney then MF_BalMoney-(case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end) else 0 end)
-- else 0 end) else 0 end) as SMoney
SELECT *,
(case when SixQty>0 and
(case when SixMoney>0 then
(case when (MF_BalMoney-TMoney)<=SixMoney
then (MF_BalMoney-TMoney) when (MF_BalMoney-TMoney)>SixMoney then SixMoney else 0 end) else 0 end)=0 then (MF_BalMoney-TMoney) else (case when SixMoney>0 then
(case when (INVTotal_CostSubBalQty-(case when SixQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt)<=SixQty
then (INVTotal_CostSubBalQty-TQyt) when (INVTotal_CostSubBalQty-TQyt)>SixQty then SixQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney)<=SixMoney
then (MF_BalMoney-TMoney) when (MF_BalMoney-TMoney)>SixMoney then SixMoney else 0 end) else 0 end) end) SMoney
,(case when SixQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt)<=SixQty
then (INVTotal_CostSubBalQty-TQyt) when (INVTotal_CostSubBalQty-TQyt)>SixQty then SixQty else 0 end) else 0 end) SQyt
into #ST
FROM #STA
--推算六个月到十二个月
select *,
--如果结存减去三个月内减去六个月内数小于等于零则为零；如果结存减去三个月减去六个月数据大于零则（如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和，反之则等于六到十二月数），
(case when ThreetyQty>0 and
(case when ThreetyMoney>0 then
(case when (MF_BalMoney-TMoney-SMoney)<=ThreetyMoney
then (MF_BalMoney-TMoney-SMoney)
when (MF_BalMoney-TMoney-SMoney)>ThreetyMoney then ThreetyMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney) else (case when ThreetyMoney>0 then
(case when (INVTotal_CostSubBalQty-(case when ThreetyQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt)<=ThreetyQty
then (INVTotal_CostSubBalQty-TQyt-SQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt)>ThreetyQty then ThreetyQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney)<=ThreetyMoney
then (MF_BalMoney-TMoney-SMoney)
when (MF_BalMoney-TMoney-SMoney)>ThreetyMoney then ThreetyMoney else 0 end) else 0 end) end) ThMoney
,(case when ThreetyQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt)<=ThreetyQty
then (INVTotal_CostSubBalQty-TQyt-SQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt)>ThreetyQty then ThreetyQty else 0 end) else 0 end) ThQyt
into #Ttys
from #ST
--这里要写 1到2年
select *,
--如果结存减去三个月内减去六个月内数小于等于零则为零；如果结存减去三个月减去六个月数据大于零则（如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和，反之则等于六到十二月数），
(case when onetwoyearresultQty>0 and
(case when onetwoyearresultMoney>0 then
(case when (MF_BalMoney-TMoney-SMoney-ThMoney)<=onetwoyearresultMoney
then (MF_BalMoney-TMoney-SMoney-ThMoney)
when (MF_BalMoney-TMoney-SMoney-ThMoney)>onetwoyearresultMoney then onetwoyearresultMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney-ThMoney) else (case when onetwoyearresultMoney>0 then
(case when (INVTotal_CostSubBalQty-(case when onetwoyearresultQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)<=onetwoyearresultQty
then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)>onetwoyearresultQty then onetwoyearresultQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney-ThMoney)<=onetwoyearresultMoney
then (MF_BalMoney-TMoney-SMoney-ThMoney)
when (MF_BalMoney-TMoney-SMoney-ThMoney)>onetwoyearresultMoney then onetwoyearresultMoney else 0 end) else 0 end) end) onetwoYsMoney
,(case when onetwoyearresultQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)<=onetwoyearresultQty
then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)>onetwoyearresultQty then onetwoyearresultQty else 0 end) else 0 end) onetwoYsQyt
into #onetwoyearTyy
from #Ttys
--这里写2到3年
--twothreeyearresultMoney
--twothreeyearresultQty
select *,
--如果结存减去三个月内减去六个月内数小于等于零则为零；如果结存减去三个月减去六个月数据大于零则（如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和，反之则等于六到十二月数），
(case when twothreeyearresultQty>0 and
(case when twothreeyearresultMoney>0 then
(case when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)<=twothreeyearresultMoney
then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)
when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)>twothreeyearresultMoney then twothreeyearresultMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney) else (case when twothreeyearresultMoney>0 then
(case when (INVTotal_CostSubBalQty-(case when twothreeyearresultQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)<=twothreeyearresultQty
then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)>twothreeyearresultQty then twothreeyearresultQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)<=twothreeyearresultMoney
then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)
when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)>twothreeyearresultMoney then twothreeyearresultMoney else 0 end) else 0 end) end) twothreeYsMoney
,(case when twothreeyearresultQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)<=twothreeyearresultQty
then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)
when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)>twothreeyearresultQty then twothreeyearresultQty else 0 end) else 0 end) twothreeYsQyt
into #Ttys_cout
from #onetwoyearTyy
--推算十二个月以上数据 改为36个月以上
select *,
(case when ThreetysMoney>0 then
(case when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney)<=ThreetysMoney then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney) when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney)>ThreetysMoney then ThreetysMoney else 0 end) else 0 end) ThMoneyss
,(case when ThreetysQty>0 then
(case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt)<=ThreetysQty then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt) when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt)>ThreetysQty then ThreetysQty else 0 end) else 0 end) ThQytss
into #endsum
from #Ttys_cout
select *,(case when (case when (TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney+ThMoneyss)!=BalMoney then BalMoney-(TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney) else ThMoneyss end)>0 then (case when (TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney+ThMoneyss)!=BalMoney then BalMoney-(TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney) else ThMoneyss end) else 0 end) ThMoneys
,(case when (TQyt+SQyt+ThQyt+onetwoYsQyt+twothreeYsQyt+ThQytss)!=INVTotal_CostSubBalQty then INVTotal_CostSubBalQty-(TQyt+SQyt+ThQyt+onetwoYsQyt+twothreeYsQyt) else ThQytss end) ThQyts
from #endsum
drop table #project
drop table #TThree
drop table #Three
drop table #Six
drop table #SSix
drop table #threety
drop table #tthreety
drop table #onetwoyear
drop table #onetwoyearresult
drop table #twothreeyear
drop table #twothreeyearresult
drop table #threetys
drop table #tthreetys
drop table #ItemMan
drop table #TuiMain
drop table #ST
drop table #STA
drop table #Ttys
drop table #onetwoyearTyy
drop table #Ttys_cout
drop table #endsum
end
