Luke的在线笔记

--变量定义
DECLARE @LevelThreeValue AS VARCHAR(50)
DECLARE @Levels AS VARCHAR(10)
DECLARE @Year AS VARCHAR(10)
DECLARE @LevelTwoId AS VARCHAR(50)
DECLARE @BudgetOwnerId AS VARCHAR(50)
DECLARE @BrandId AS VARCHAR(50)
DECLARE @ActivityId AS VARCHAR(50)
DECLARE @ActivityCode AS VARCHAR(50)
DECLARE @ExpenseTypeId AS VARCHAR(50)
DECLARE @FYAmountDifference AS DECIMAL(18,3)
DECLARE @Q1Difference AS DECIMAL(18,3)
DECLARE @Q2Difference AS DECIMAL(18,3)
DECLARE @Q3Difference AS DECIMAL(18,3)
DECLARE @Q4Difference AS DECIMAL(18,3)
DECLARE @AuditCount AS INT
DECLARE @Guid AS VARCHAR(50)
DECLARE @PreviousAmount AS DECIMAL(18,3)
DECLARE @UpdatedAmount AS DECIMAL(18,3)
DECLARE @ApprovalStatus AS VARCHAR(50)
DECLARE @QuarterCount AS INT
DECLARE @QuarterDraftCount AS INT
DECLARE @QuarterRejectedCount AS INT
DECLARE @QuarterlyBPId AS VARCHAR(50)
DECLARE @Timestr AS VARCHAR(50)
--获取Activity相关信息
SELECT @LevelThreeValue = LevelThreeValue,
@Levels = Levels,
@Year = Year,
@BudgetOwnerId = BudgetOwnerId,
@BrandId = BrandId,
@ActivityId = ActivityId,
@ActivityCode = ActivityCode,
@LevelTwoId = LevelTwoId,
@ExpenseTypeId = ExpenseTypeId
FROM PP_China_AnM_BP.VIEWActivityExpenseType
WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND (Levels = '3' OR (Levels = '2' AND LevelThreeValue = 'False'))
--查询插入数据是否存在Draft
SELECT @QuarterDraftCount= COUNT(*),@Timestr = Timestr,@QuarterlyBPId = QuarterlyBPId FROM FIN.PP_AnM_QuarterlyBPDetail WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' ApprovalStatus = 'Draft' GROUP BY ApprovalStatus,Timestr
--查询插入数据是否存在Rejected
SELECT @QuarterRejectedCount = COUNT(*),@Timestr = Timestr,@QuarterlyBPId = QuarterlyBPId FROM FIN.PP_AnM_QuarterlyBPDetail WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' AND ApprovalStatus = 'Rejected' GROUP BY ApprovalStatus,Timestr
--查询总条数
SELECT @QuarterCount = COUNT(*),@Timestr = Timestr FROM FIN.PP_AnM_QuarterlyBPDetail WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' GROUP BY ApprovalStatus,Timestr
--如果数据存在Draft,优先更新Draft数据
IF @QuarterRejectedCount >0
BEGIN
SET @QuarterCount = @QuarterRejectedCount
SET @ApprovalStatus = 'Rejected'
END

IF @QuarterDraftCount>0
BEGIN
SET @QuarterCount = @QuarterDraftCount
SET @ApprovalStatus = 'Draft'
END

IF @QuarterCount>0 AND @ApprovalStatus = 'Draft'
BEGIN
--更新二级
IF @Levels = '2' AND ISNULL(@LevelThreeValue,'') <> 'True'
BEGIN
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET Q1Value = CAST(ISNULL('1000',0) AS FLOAT),Q2Value = CAST(ISNULL('0',0) AS FLOAT),Q3Value = CAST(ISNULL('0',0) AS FLOAT),Q4Value = CAST(ISNULL('1000',0) AS FLOAT),FYTotal = CAST(ISNULL('1000',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT) + CAST(ISNULL('1000',0) AS FLOAT),UpdatedBy ='Weiyang',UpdatedDate=GETDATE(),Comments = N'12345678909uyit' WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' AND QuarterlyBPId= @QuarterlyBPId

UPDATE FIN.PP_AnM_QuarterlyBPDetail SET WTValue = ISNULL(B.Q1Value,0) + ISNULL(B.Q2Value,0) + ISNULL(B.Q3Value,0)+ ISNULL(B.Q4Value,0) FROM FIN.PP_AnM_ActivityExpenseType B WHERE FIN.PP_AnM_QuarterlyBPDetail.WBSCode = B.WBSCode AND FIN.PP_AnM_QuarterlyBPDetail.WBSCode = 'AM-CN-26110-AB000-P0' AND FIN.PP_AnM_QuarterlyBPDetail.CurrentQuarter = 'Q2' AND B.ExpenseTypeId = @ExpenseTypeId AND QuarterlyBPId= @QuarterlyBPId
END
ELSE IF @Levels = '3'
BEGIN
SELECT @Q1Difference =CASE WHEN ISNUMERIC('1000') = 1 THEN CAST('1000' AS FLOAT) - CAST(ISNULL(Q1Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q1Value,0) AS FLOAT) END,
@Q2Difference = CASE WHEN ISNUMERIC('0') = 1 THEN CAST('0' AS FLOAT) - CAST(ISNULL(Q2Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q2Value,0) AS FLOAT) END,
@Q3Difference =CASE WHEN ISNUMERIC('0') = 1 THEN CAST('0' AS FLOAT) - CAST(ISNULL(Q3Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q3Value,0) AS FLOAT) END,
@Q4Difference = CASE WHEN ISNUMERIC('1000') = 1 THEN CAST('1000' AS FLOAT) - CAST(ISNULL(Q4Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q4Value,0) AS FLOAT) END
FROM FIN.PP_AnM_QuarterlyBPDetail WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' AND QuarterlyBPId= @QuarterlyBPId
--更新三级值
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET Q1Value = CAST(ISNULL('1000',0) AS FLOAT),Q2Value = CAST(ISNULL('0',0) AS FLOAT),Q3Value = CAST(ISNULL('0',0) AS FLOAT),Q4Value = CAST(ISNULL('1000',0) AS FLOAT),FYTotal = CAST(ISNULL('1000',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT) + CAST(ISNULL('1000',0) AS FLOAT),UpdatedBy ='Weiyang',UpdatedDate=GETDATE(),Comments =N'12345678909uyit' WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' AND QuarterlyBPId= @QuarterlyBPId
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET WTValue = ISNULL(B.Q1Value,0) + ISNULL(B.Q2Value,0) + ISNULL(B.Q3Value,0)+ ISNULL(B.Q4Value,0) FROM FIN.PP_AnM_ActivityExpenseType B WHERE FIN.PP_AnM_QuarterlyBPDetail.WBSCode = B.WBSCode AND FIN.PP_AnM_QuarterlyBPDetail.WBSCode = 'AM-CN-26110-AB000-P0' AND FIN.PP_AnM_QuarterlyBPDetail.CurrentQuarter = 'Q2' AND B.ExpenseTypeId = @ExpenseTypeId AND FIN.PP_AnM_QuarterlyBPDetail.QuarterlyBPId= @QuarterlyBPId
--更新二级值
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET
Q1Value= ISNULL(@Q1Difference,0) + CAST(ISNULL(Q1Value,0) AS FLOAT),
Q2Value = ISNULL(@Q2Difference,0) + CAST(ISNULL(Q1Value,0) AS FLOAT) ,
Q3Value = ISNULL(@Q3Difference,0) + CAST(ISNULL(Q3Value,0) AS FLOAT) ,
Q4Value = ISNULL(@Q4Difference,0) + CAST(ISNULL(Q4Value,0) AS FLOAT) ,
FYTotal = ISNULL( @FYAmountDifference,0) + CAST(ISNULL(FYTotal ,0) AS FLOAT) ,UpdatedBy ='Weiyang',UpdatedDate=GETDATE(),Comments=N'12345678909uyit'
WHERE Year = @Year AND BudgetOwnerId = @BudgetOwnerId AND BrandId = @BrandId AND ActivityId = @ActivityId AND ExpenseTypeId = @LevelTwoId AND CurrentQuarter = 'Q2' AND QuarterlyBPId= @QuarterlyBPId
END
--更新时间戳
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET Timestr = '1675149099379' WHERE Year = @Year AND BudgetOwnerId = @BudgetOwnerId AND CurrentQuarter = 'Q2' AND QuarterlyBPId= @QuarterlyBPId

DELETE FROM FIN.PP_AnM_BPWarning WHERE Timestr = @Timestr
END
ELSE IF ISNULL(@QuarterCount,0) = 0 OR (ISNULL(@QuarterCount,0) > 0
AND @ApprovalStatus = 'Rejected' )
BEGIN
--插入对应的所有ExpenseType数据
INSERT INTO FIN.PP_AnM_QuarterlyBPDetail(Id,QuarterlyBPId,Year,BudgetOwnerId,BrandId,ActivityId,ExpenseTypeId,ImportType,Q1Value,Q2Value,Q3Value,Q4Value,FYTotal,CurrentQuarter,CreatedDate,CreatedBy,ApprovalStatus,WBSCode,Timestr,Comments) SELECT NEWID(),'96778b25-10fd-42f6-a6f4-77b6595656dc',Year,BudgetOwnerId,BrandId,ActivityId,ExpenseTypeId,'Quarter',0,0,0,0,0,'Q2',GETDATE(),'Weiyang','Draft','AM-CN-'+SUBSTRING(ISNULL(Year,''),3,2)+'1'+ISNULL(BudgetOwnerCode,'')+'-'+ISNULL(BrandCode,'')+ISNULL(ActivityCode,'') + '-' + ISNULL(ExpenseTypeCode,''),'1675149099379',N'12345678909uyit'
FROM PP_China_AnM_BP.VIEWActivityExpenseType WHERE Year = @Year AND BudgetOwnerId = @BudgetOwnerId AND BrandId = @BrandId AND ActivityId = @ActivityId
--更新值
--更新二级
IF @Levels = '2' AND ISNULL(@LevelThreeValue,'') <> 'True'
BEGIN
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET Q1Value = CAST(ISNULL('1000',0) AS FLOAT),Q2Value = CAST(ISNULL('0',0) AS FLOAT),Q3Value = CAST(ISNULL('0',0) AS FLOAT),Q4Value = CAST(ISNULL('1000',0) AS FLOAT),FYTotal = CAST(ISNULL('1000',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT) + CAST(ISNULL('1000',0) AS FLOAT),UpdatedBy ='Weiyang',UpdatedDate=GETDATE(),Comments =N'12345678909uyit' WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2'

UPDATE FIN.PP_AnM_QuarterlyBPDetail SET WTValue = ISNULL(B.Q1Value,0) + ISNULL(B.Q2Value,0) + ISNULL(B.Q3Value,0)+ ISNULL(B.Q4Value,0) FROM FIN.PP_AnM_ActivityExpenseType B WHERE FIN.PP_AnM_QuarterlyBPDetail.WBSCode = B.WBSCode AND FIN.PP_AnM_QuarterlyBPDetail.WBSCode = 'AM-CN-26110-AB000-P0' AND FIN.PP_AnM_QuarterlyBPDetail.CurrentQuarter = 'Q2' AND B.ExpenseTypeId = @ExpenseTypeId
END
ELSE IF @Levels = '3'
BEGIN
SELECT @Q1Difference =CASE WHEN ISNUMERIC('1000') = 1 THEN CAST('1000' AS FLOAT) - CAST(ISNULL(Q1Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q1Value,0) AS FLOAT) END,
@Q2Difference = CASE WHEN ISNUMERIC('0') = 1 THEN CAST('0' AS FLOAT) - CAST(ISNULL(Q2Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q2Value,0) AS FLOAT) END,
@Q3Difference =CASE WHEN ISNUMERIC('0') = 1 THEN CAST('0' AS FLOAT) - CAST(ISNULL(Q3Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q3Value,0) AS FLOAT) END,
@Q4Difference = CASE WHEN ISNUMERIC('1000') = 1 THEN CAST('1000' AS FLOAT) - CAST(ISNULL(Q4Value,0) AS FLOAT) ELSE 0-CAST(ISNULL(Q4Value,0) AS FLOAT) END
FROM FIN.PP_AnM_QuarterlyBPDetail WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2'

--更新三级值
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET Q1Value = CAST(ISNULL('1000',0) AS FLOAT),Q2Value = CAST(ISNULL('0',0) AS FLOAT),Q3Value = CAST(ISNULL('0',0) AS FLOAT),Q4Value = CAST(ISNULL('1000',0) AS FLOAT),FYTotal = CAST(ISNULL('1000',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT)+CAST(ISNULL('0',0) AS FLOAT) + CAST(ISNULL('1000',0) AS FLOAT),UpdatedBy ='Weiyang',UpdatedDate=GETDATE(),Comments =N'12345678909uyit' WHERE WBSCode = 'AM-CN-26110-AB000-P0' AND CurrentQuarter = 'Q2' AND Timestr = '1675149099379'
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET WTValue = ISNULL(B.Q1Value,0) + ISNULL(B.Q2Value,0) + ISNULL(B.Q3Value,0)+ ISNULL(B.Q4Value,0) FROM FIN.PP_AnM_ActivityExpenseType B WHERE FIN.PP_AnM_QuarterlyBPDetail.WBSCode = B.WBSCode AND FIN.PP_AnM_QuarterlyBPDetail.WBSCode = 'AM-CN-26110-AB000-P0' AND FIN.PP_AnM_QuarterlyBPDetail.CurrentQuarter = 'Q2'
--更新二级值
UPDATE FIN.PP_AnM_QuarterlyBPDetail SET
Q1Value= ISNULL(@Q1Difference,0) + CAST(ISNULL(Q1Value,0) AS FLOAT),
Q2Value = ISNULL(@Q2Difference,0) + CAST(ISNULL(Q1Value,0) AS FLOAT) ,
Q3Value = ISNULL(@Q3Difference,0) + CAST(ISNULL(Q3Value,0) AS FLOAT) ,
Q4Value = ISNULL(@Q4Difference,0) + CAST(ISNULL(Q4Value,0) AS FLOAT) ,
FYTotal = ISNULL( @FYAmountDifference,0) + CAST(ISNULL(FYTotal ,0) AS FLOAT),UpdatedBy ='Weiyang',UpdatedDate=GETDATE() ,Comments =N'12345678909uyit'
WHERE Year = @Year AND BudgetOwnerId = @BudgetOwnerId AND BrandId = @BrandId AND ActivityId = @ActivityId AND ExpenseTypeId = @LevelTwoId AND CurrentQuarter = 'Q2'
END
END

posted @   夜月之光  阅读(93)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 本地部署 DeepSeek:小白也能轻松搞定!
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 在缓慢中沉淀,在挑战中重生!2024个人总结!
· 从 Windows Forms 到微服务的经验教训
· 李飞飞的50美金比肩DeepSeek把CEO忽悠瘸了,倒霉的却是程序员
点击右上角即可分享
微信分享提示