daily pastebin goal
48%
SHARE
TWEET

Untitled

a guest Nov 14th, 2017 55 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top