SHARE
TWEET

Untitled

a guest Nov 14th, 2017 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET QUOTED_IDENTIFIER OFF
  2. GO
  3. SET ANSI_NULLS OFF
  4. GO
  5.  
  6. CREATE PROCEDURE [dbo].[PayCalcServiceUpgradeUpdate] (
  7.     @CompanyID int,
  8.     @SetupID int,
  9.     @UpgradeDate datetime,
  10.     @UpgradeOrigSubtotal decimal(9,2),
  11.     @UpgradeOrigScheduleID int,
  12.     @UpgradeOrigService varchar(15),
  13.     @UpgradeTechID1 int,
  14.     @UpgradeTechID2 int,
  15.     @UpgradeTechID3 int,
  16.     @UpgradeTechID4 int,
  17.     @UpgradeTechID5 int,
  18.     @UpgradeTechShare1 Percentage,
  19.     @UpgradeTechShare2 Percentage,
  20.     @UpgradeTechShare3 Percentage,
  21.     @UpgradeTechShare4 Percentage,
  22.     @UpgradeTechShare5 Percentage,
  23.     @UpgradeComment varchar(500),
  24.     @UpgradeSkipMonths char(12),
  25.     @UpgradeUserDef23 varchar(10),
  26.     @UpgradeTechBonus1 Percentage,
  27.     @UpgradeTechBonus2 Percentage,
  28.     @UpgradeTechBonus3 Percentage,
  29.     @UpgradeTechBonus4 Percentage,
  30.     @UpgradeTechBonus5 Percentage
  31. )
  32. AS
  33.  
  34. SET NOCOUNT ON
  35.  
  36. DELETE  FROM PayCalcServiceUpgrade WHERE SetupID = @SetupID
  37.  
  38. DECLARE @UpgradeStartDate datetime,
  39.     @UpgradeEndDate datetime,
  40.     @OriginalStartDate datetime,
  41.     @OriginalEndDate datetime,
  42.     @RemainingOriginalMonths int,
  43.     @UpgradedAnnualValue decimal(9,2),
  44.     @UpgradedMonthlyValue decimal(9,2),
  45.     @UpgradedMonthlyValueDiff decimal(9,2),
  46.     @OriginalMonthlyValue decimal(9,2),
  47.     @OriginalSubtotal decimal(9,2),
  48.     @OriginalRemainingServiceCount int,
  49.     @NewScheduleID int,
  50.     @NewSubtotal decimal(9,2),
  51.     @IsSplit bit
  52.  
  53. SET @OriginalSubtotal = @UpgradeOrigSubtotal
  54.  
  55. --Set start date to first of the upgrade month, to end of previous month 1 year away
  56. SET @UpgradeStartDate = dbo.FirstOfMonth(@UpgradeDate)
  57. SET @UpgradeEndDate = DATEADD(dd, -1, DATEADD(yyyy,1,@UpgradeStartDate))
  58.  
  59. SELECT
  60. @OriginalStartDate = StartDate,
  61. @NewScheduleID = ScheduleID,
  62. @NewSubtotal = SubTotal,
  63. @IsSplit = CASE WHEN SetupType = 'SP' THEN 1 ELSE 0 END
  64. FROM ServiceSetups
  65. WHERE SetupID = @SetupID
  66.  
  67. SET @OriginalEndDate = DATEADD(yyyy, 1, @OriginalStartDate)
  68.  
  69. SET @RemainingOriginalMonths = DATEDIFF(mm, @UpgradeStartDate, @OriginalEndDate)
  70. IF @RemainingOriginalMonths < 0
  71.     SET @RemainingOriginalMonths = 0
  72.  
  73. IF @IsSplit = 0 BEGIN --Regular service
  74.     SET @UpgradedAnnualValue = @NewSubtotal * (dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate)  - 1)
  75.  
  76.     --Get new annual values amount each time it would generate (new annual value / times setup is going to generate)
  77.     SET @UpgradedMonthlyValue = @UpgradedAnnualValue / (dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate) - 1)
  78.  
  79.     SET @OriginalRemainingServiceCount = dbo.ScheduleCount(@UpgradeOrigScheduleID, @UpgradeStartDate, @UpgradeEndDate)
  80.  
  81.     --Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
  82.     SET @OriginalMonthlyValue = (dbo.ScheduleCount(@UpgradeOrigScheduleID, '01/01/2005', CONVERT(datetime, '2005-12-31 11:59 pm', 120)) * @OriginalSubtotal) / dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate)
  83.  
  84.     --Poor naming convention - but this would be the amount each time the new invoice generates, not always monthly
  85.     SET @UpgradedMonthlyValueDiff = @UpgradedMonthlyValue - @OriginalMonthlyValue
  86.     IF @UpgradedMonthlyValueDiff < 0
  87.         SET @UpgradedMonthlyValueDiff = 0
  88. END
  89. ELSE BEGIN --Is a split
  90.  
  91.     DECLARE @StartOfMonth datetime,
  92.             @EndOfMonth datetime
  93.  
  94.     SET @StartOfMonth = DATEADD(month, DATEDIFF(month, 0, @UpgradeDate), 0)
  95.  
  96.     SET @EndOfMonth = DATEADD(mi,1439,CONVERT(datetime,EOMONTH(@UpgradeDate)))
  97.     --New Subtotal * 12 because amount on setup is split up into the PR invoices, per month
  98.     SET @UpgradedAnnualValue = @NewSubtotal * 12
  99.  
  100.     --Monthly Value / number of times PR will generate in a month
  101.  
  102.    
  103.     IF dbo.ScheduleCount(@UpgradeOrigScheduleID, @StartOfMonth, @EndOfMonth) > dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth ) BEGIN
  104.  
  105.         SET @UpgradedMonthlyValue = @NewSubtotal / dbo.ScheduleCount(@UpgradeOrigScheduleID,  @StartOfMonth, @EndOfMonth)
  106.  
  107.         --Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
  108.         SET @OriginalMonthlyValue = @OriginalSubtotal / dbo.ScheduleCount(@UpgradeOrigScheduleID,  @StartOfMonth, @EndOfMonth)
  109.     END
  110.     ELSE BEGIN
  111.  
  112.         SET @UpgradedMonthlyValue = @NewSubtotal / dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth)
  113.  
  114.         --Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
  115.         SET @OriginalMonthlyValue = @OriginalSubtotal / dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth)
  116.     END
  117.  
  118.     SET @OriginalRemainingServiceCount = dbo.ScheduleCountSkip(@UpgradeOrigScheduleID, @UpgradeStartDate, @UpgradeEndDate)
  119.  
  120.     --Poor naming convention - but this would be the amount each time the new invoice generates, not always monthly
  121.     SET @UpgradedMonthlyValueDiff = @UpgradedMonthlyValue - @OriginalMonthlyValue
  122.     IF @UpgradedMonthlyValueDiff < 0
  123.         SET @UpgradedMonthlyValueDiff = 0
  124. END
  125.  
  126. INSERT INTO PayCalcServiceUpgrade
  127. (SetupID,UpgradeDate,UpgradeOrigSubtotal,UpgradeOrigScheduleID,UpgradeOrigService, UpgradeTechID1,
  128. UpgradeTechID2,UpgradeTechID3,UpgradeTechID4,UpgradeTechID5,UpgradeTechShare1,UpgradeTechShare2,
  129. UpgradeTechShare3,UpgradeTechShare4,UpgradeTechShare5,UpgradeComment,UpgradeSkipMonths, UpgradeUserDef23,
  130. UpgradeStartDate, UpgradeEndDate, UpgradedAnnualValue, UpgradedMonthlyValue, UpgradedMonthlyValueDiff,
  131. OriginalStartDate, OriginalEndDate, OriginalRemainingServiceCount, OriginalSubtotal, RemainingOriginalMonths,  OriginalMonthlyValue,
  132. UpgradeTechBonus1,UpgradeTechBonus2,UpgradeTechBonus3,UpgradeTechBonus4,UpgradeTechBonus5)
  133.  
  134. VALUES
  135. (@SetupID,@UpgradeDate,@UpgradeOrigSubtotal,@UpgradeOrigScheduleID,@UpgradeOrigService, @UpgradeTechID1,
  136. @UpgradeTechID2,@UpgradeTechID3,    @UpgradeTechID4,@UpgradeTechID5,    @UpgradeTechShare1, @UpgradeTechShare2,
  137. @UpgradeTechShare3, @UpgradeTechShare4, @UpgradeTechShare5, @UpgradeComment, @UpgradeSkipMonths, @UpgradeUserDef23,
  138. @UpgradeStartDate, @UpgradeEndDate, @UpgradedAnnualValue, @UpgradedMonthlyValue, @UpgradedMonthlyValueDiff,
  139. @OriginalStartDate, @OriginalEndDate, @OriginalRemainingServiceCount, @OriginalSubtotal, @RemainingOriginalMonths, @OriginalMonthlyValue,
  140. @UpgradeTechBonus1, @UpgradeTechBonus2, @UpgradeTechBonus3, @UpgradeTechBonus4, @UpgradeTechBonus5)
  141.  
  142. SET NOCOUNT OFF
  143.  
  144.  
  145.  
  146. GO
RAW Paste Data
Top