May 31, 2011 at 12:02 pm
I took your code, formatted it with SQL Prompt Pro, and then eliminated three of your cursors. Look it over, see if it works correctly, and then see if you can get rid of the 4th. You should be able to end up with an INSERT INTO ... SELECT statement.
BEGIN TRAN
DECLARE @col1 VARCHAR(1000)
DECLARE @TotalInvoiceAmount DECIMAL(18, 6)
DECLARE @termsdiscountamount DECIMAL(18, 6)
DECLARE @InvoiceSubTotal DECIMAL(18, 6)
DECLARE @supplierkey VARCHAR(1000)
DECLARE @UnitsShipped DECIMAL(18, 6)
DECLARE @GrossAmount DECIMAL(18, 6)
DECLARE @NetAmount DECIMAL(18, 6)
DECLARE @ChargeType VARCHAR(20)
DECLARE @ShipToKey VARCHAR(20)
DECLARE @RebateUnits DECIMAL(18, 6)
DECLARE @TotalGross DECIMAL(18, 6)
DECLARE @TotalNet DECIMAL(18, 6)
DECLARE @TotalUnits DECIMAL(18, 6)
DECLARE @RuleId DECIMAL(18, 6)
DECLARE @RuleUnitType INTEGER
DECLARE @TotalChargeUnits DECIMAL(18, 6)
DECLARE @TotalChargeGross DECIMAL(18, 6)
DECLARE @TotalChargeNet DECIMAL(18, 6)
DECLARE @UnitsToDeductFromTerms DECIMAL(18, 6)
DECLARE @TermsRuleType AS INTEGER
DECLARE @TermsPercent AS DECIMAL(18, 6)
SET @RuleUnitType = 1
SET @TermsRuleType = 1
TRUNCATE TABLE rb_ruleinvoice
DECLARE cur CURSOR STATIC LOCAL
FOR
SELECT invoicekey,
rb_ruleid
FROM rb_invoicestoprocess
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @col1, @RuleId
IF @@fetch_status <> 0
BREAK
-- This cursor is just about useless - it takes one row at random
-- (since there isn't an ORDER BY clause) and assigns its values
-- to a few variables.
-- "Just About" = Unless there will only ever be one row...
-- In which case, use these changes.
/*
DECLARE curMain CURSOR STATIC LOCAL
FOR
SELECT TotalInvoiceAmount,
InvoiceSubTotal,
termsdiscountamount,
supplierkey,
shiptokey
FROM invoicetotal a,
invoice b
WHERE a.invoicekey = b.invoicekey
AND a.invoicekey = @col1
*/
SELECT @TotalInvoiceAmount = TotalInvoiceAmount,
@InvoiceSubTotal = InvoiceSubTotal,
@termsdiscountamount = termsdiscountamount,
@supplierkey = supplierkey,
@shiptokey = shiptokey
FROM invoicetotal a,
JOIN invoice b
ON a.invoicekey = b.invoicekey
WHERE a.invoicekey = @col1
/*
OPEN curMain
WHILE 1 = 1
BEGIN
FETCH curMain INTO @TotalInvoiceAmount, @InvoiceSubTotal, @termsdiscountamount, @supplierkey,
@ShipToKey
IF @@fetch_status <> 0
BREAK
END
DEALLOCATE curMain
*/
SET @UnitsToDeductFromTerms = 0
SET @RebateUnits = 0
SET @TotalGross = 0
SET @TotalNet = 0
SET @TotalUnits = 0
SET @TotalChargeUnits = 0
SET @TotalChargeGross = 0
SET @TotalChargeNet = 0
--print cast(@col1 as nvarchar(30))
/*
DECLARE curLineItems CURSOR STATIC LOCAL
FOR
SELECT SUM(unitsshipped),
SUM(grossamount),
SUM(netamount)
*/
SELECT @TotalUnits = SUM(unitsshipped),
@TotalGross = SUM(grossamount),
@TotalNet = SUM(netamount)
FROM (SELECT UnitsShipped,
GrossAmount,
NetAmount
FROM InvoiceLineItem a,
Invoice b
WHERE a.invoicekey = @col1
AND a.invoicekey = b.invoicekey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM RB_RuleCategory c
WHERE c.rb_ruleid = 8251
AND c.SupplierId = B.supplierkey)
UNION
SELECT 1,
a.TaxAmount,
a.TaxAmount
FROM invoicetotaltax a,
Invoice b
WHERE a.invoicekey = @col1
AND a.invoicekey = b.invoicekey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM RB_RuleCategory c
WHERE c.rb_ruleid = 8251
AND c.SupplierId = B.supplierkey)) AS tftest
/*
OPEN curLineItems
WHILE 1 = 1
BEGIN
FETCH curLineItems INTO @UnitsShipped, @GrossAmount, @NetAmount
IF @@fetch_status <> 0
BREAK
SET @TotalGross = @TotalGross + @GrossAmount
SET @TotalNet = @TotalNet + @NetAmount
SET @TotalUnits = @TotalUnits + @UnitsShipped
--print cast(@col1 as nvarchar(30)) + ' ' + cast(@UnitsShipped as nvarchar(30))
END
DEALLOCATE curLineItems
*/
--declare curTax CURSOR STATIC LOCAL FOR
--select 1,a.TaxAmount,a.TaxAmount as AmountType from invoicetotaltax a,Invoice b where a.invoicekey = @col1 and a.invoicekey = b.invoicekey and a.ProductRebateCategory in (select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = 8251 and c.SupplierId = B.supplierkey)
--open curTax
--while 1=1
--BEGIN
-- FETCH curTax INTO @UnitsShipped,@GrossAmount,@NetAmount
-- IF @@fetch_status <> 0
-- break
-- set @TotalGross = @TotalGross + @GrossAmount
-- set @TotalNet = @TotalNet + @NetAmount
-- set @TotalUnits = @TotalUnits + @UnitsShipped
--end
--Deallocate curTax
/*
DECLARE curCharges CURSOR STATIC LOCAL
FOR
SELECT 1,
Amount,
Amount,
Type AS AmountType
FROM InvoiceTotalAdditionalCosts a,
Invoice b
WHERE a.invoicekey = @col1
AND a.invoicekey = b.invoicekey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM RB_RuleCategory c
WHERE c.rb_ruleid = 8251
AND c.SupplierId = B.supplierkey)
OPEN curCharges
WHILE 1 = 1
BEGIN
FETCH curCharges INTO @UnitsShipped, @GrossAmount, @NetAmount, @ChargeType
IF @@fetch_status <> 0
BREAK
IF UPPER(@ChargeType) = 'C'
BEGIN
IF @GrossAmount < 0
BEGIN
SET @GrossAmount = @GrossAmount * -1
END
IF @NetAmount < 0
BEGIN
SET @NetAmount = @NetAmount * -1
END
END
SET @TotalGross = @TotalGross + @GrossAmount
SET @TotalNet = @TotalNet + @NetAmount
SET @TotalUnits = @TotalUnits + @UnitsShipped
-- set the charge units
SET @TotalChargeUnits = @TotalChargeUnits + @UnitsShipped
SET @TotalChargeGross = @TotalChargeGross + @GrossAmount
SET @TotalChargeNet = @TotalChargeNet + @NetAmount
END
DEALLOCATE curCharges
*/
SELECT @TotalChargeUnits = SUM(1),
@TotalChargeGross = SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END),
@TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END),
-- Following columns were being added to from the previous query...
@TotalGross = @TotalGross + SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END),
@TotalNet = @TotalNet + SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END),
@TotalUnits = @TotalUnits + SUM(1)
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN RM_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE c.RM_RuleID = 8251;
-- now calculate the final totals
SELECT @RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN @TotalGross
WHEN 2 THEN @TotalNet
WHEN 3 THEN @TotalUnits
END
SELECT @UnitsToDeductFromTerms = CASE (@RuleUnitType)
WHEN 1 THEN @TotalChargeGross
WHEN 2 THEN @TotalChargeNet
WHEN 3 THEN @TotalChargeUnits
END
IF @termsdiscountamount <> 0
AND @TermsRuleType <> 0
BEGIN
IF @TermsRuleType = 1
BEGIN
SET @TermsPercent = @termsdiscountamount / @TotalInvoiceAmount
END
ELSE
BEGIN
SET @TermsPercent = @termsdiscountamount / @InvoiceSubTotal
END
IF @TermsPercent <> 0
BEGIN
SET @RebateUnits = @RebateUnits - @UnitsToDeductFromTerms
SET @RebateUnits = @RebateUnits - ((@UnitsToDeductFromTerms) * @TermsPercent)
SET @RebateUnits = @RebateUnits + @UnitsToDeductFromTerms
END
END
INSERT INTO [RB_RuleInvoice]
([RB_RuleId],
[RB_RulePeriodId],
[InvoiceKey],
[IsAssociated],
[ForcastData],
[RebateUnits],
[GrossAmount],
[NetAmount],
[Units],
[DealerId],
[RebateAmount],
[RebateAmountPaid],
[RebateAmountActual],
[ReallocationAmount],
[ReallocationAmountPaid],
[ReallocationPaidFlag],
[PostPeriodAdjustAmount],
[TotalInvoiceNotPaid],
[TotalReallocation],
[PercentPayable])
VALUES (@RuleId,
-1,
@col1,
1,
0,
@RebateUnits,
@TotalGross,
@TotalNet,
@TotalUnits,
@ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0)
END
SELECT *
FROM rb_ruleinvoice
DEALLOCATE cur
ROLLBACK
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 31, 2011 at 12:49 pm
GSquared (5/31/2011)
...GSquared, OEC, FEBC, HDA, RSVP, OODA, MAP, NMVP, ARCSW, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ETC
Gus, you didn't take the JOAT exam yet? :-P:-D
June 1, 2011 at 8:52 am
LutzM (5/31/2011)
GSquared (5/31/2011)
...GSquared, OEC, FEBC, HDA, RSVP, OODA, MAP, NMVP, ARCSW, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ETCGus, you didn't take the JOAT exam yet? :-P:-D
I can't. Would violate the rules for unethical exam taking. After all, I actually know the material, wouldn't have to brain dump or anything. What's the point of taking an exam you actually understand the material for? :w00t:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2011 at 11:59 am
Wayne,
I have taken you code and I am having some sucess with it -- can you give me an example of how to eliminate the last cursor? (you make reference to eliminating the last cursor by doing a select into)
I am at the point where I have all my variables filled with the correct data but I cannot seem to get them to become part of an insert statement....
I have been trying something along this line -- but I think I am barking up the wrong tree....
insert INTO [RB_RuleInvoice]
([RB_RuleId]
,[RB_RulePeriodId]
,[InvoiceKey]
,[IsAssociated]
,[ForcastData]
,[RebateUnits]
,[GrossAmount]
,[NetAmount]
,[Units]
,[DealerId]
,[RebateAmount]
,[RebateAmountPaid]
,[RebateAmountActual]
,[ReallocationAmount]
,[ReallocationAmountPaid]
,[ReallocationPaidFlag]
,[PostPeriodAdjustAmount]
,[TotalInvoiceNotPaid]
,[TotalReallocation]
,[PercentPayable])
select @RuleId,@RulePeriodId,@InvoiceKey,@IsAssociated,0,@RebateUnits,@TotalGross,@TotalNet,@TotalUnits,@ShipToKey,0,0,0,0,0,0,0,0,0,0
from
(
---- this is the rest of the fixed query you gave me.......
)
June 1, 2011 at 12:41 pm
tfeuz (6/1/2011)
Wayne,I have taken you code and I am having some sucess with it -- can you give me an example of how to eliminate the last cursor? (you make reference to eliminating the last cursor by doing a select into)
I am at the point where I have all my variables filled with the correct data but I cannot seem to get them to become part of an insert statement....
I have been trying something along this line -- but I think I am barking up the wrong tree....
First, can you post what the code that you're having success with?
Secondly, you have been asked several times to provide DDL and sample data for us to work with:
LutzM (5/30/2011)
We'd need table def and sample data for all tables involved in a ready to use format as decribed in the first link referenced in my signature.
Sean Lange (5/31/2011)
As Lutz suggested post some consumable ddl and sample data and there will most likely be multiple versions for you to try and see how the performance changes.
Sean Lange (5/31/2011)
Yes I know it is a lot of work but if you want some real help with this we have to have something to work with. Given the complexity there is little chance that somebody is going to dedicate the couple hours it will take to create your tables with some possible sample data for you.
LutzM (5/31/2011)
It might be possible to do it all set based. But I'd need to see the table and data to be sure. There are so many loops it makes me feel being in the gift packing industry... 😛
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
So, to be blunt, I'm not willing to invest more time in helping you out if you're not going to bother to help us help you. Post the requested information, and we'll see what we can do. There looks to be 4 folks willing to help you out... so, help us help you!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 1, 2011 at 1:00 pm
Thanks Wayne...
I have started to complied all the information -- it will be 9 tables and a small set of data -- including 2 stored procedures....
Unfortunately I accidently deleted all the test data -- it may take me a bit to resurrect....
I will post again when I have the required information...
Thanks..
June 2, 2011 at 7:41 am
All,
Here is the information requested to assist with this problem. I tried to follow your advice in the links at the bottom of your signatures as best I could, if I have missed anything then please let me know. In addition, I created a brand new DB and ran all the scripts in order to ensure that they ran and gave me the desired output.
That being said, the problem is really this -- everything actually works and does what it should do correctly (the math is correct) -- my issue is ensuring it is running as fast as it possibly can.
Currently on a dataset of approx 100,000 rows this process takes about 2 minutes. I keep reading about others processing far more rows, with more complexity in far less time - clearly I am missing something.
Question #1
can this be optimized further to process higher volumes of 100K rows in seconds instead of minutes?
Question #2
You will note that I have a procedure that is responsible for calculating the units on an invoice and retunring the value. This is because I have some client side code that requires me to calculate the units on an invoice but does not require me to do the extra work that the second procedure is doing. This is simply an effort to have only one set of code that is responsible for calculating the units -- not sure if this is the righht approach from a preformance perspective but it is from a maintenance perspective (in my eyes)
Thank you
Tony
Table Creation SQL
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Invoice_Status997Flag]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF_Invoice_Status997Flag]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Invoice_PrintCount]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF_Invoice_PrintCount]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__Deleted__44CA3770]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__Deleted__44CA3770]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__RebateC__0D44F85C]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__RebateC__0D44F85C]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Invoice_LBMXDocumentId]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF_Invoice_LBMXDocumentId]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Invoice_MBXStatus]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF_Invoice_MBXStatus]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__DataSou__00AA174D]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__DataSou__00AA174D]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__Product__22FF2F51]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__Product__22FF2F51]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__Billing__361203C5]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__Billing__361203C5]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__invoice__AP_EFTS__740F363E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__invoice__AP_EFTS__740F363E]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__invoice__AR_EFTS__75035A77]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__invoice__AR_EFTS__75035A77]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__APSumma__5A1A5A11]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__APSumma__5A1A5A11]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__MatchCr__125EB334]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__MatchCr__125EB334]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__Promoti__1EC48A19]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__Promoti__1EC48A19]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__Rebate___636EBA21]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__Rebate___636EBA21]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__TermsDi__290D0E62]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__TermsDi__290D0E62]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Invoice__TermsDi__4F3DA442]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Invoice] DROP CONSTRAINT [DF__Invoice__TermsDi__4F3DA442]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceLi__Retai__3C54ED00]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceLineItem] DROP CONSTRAINT [DF__InvoiceLi__Retai__3C54ED00]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceLi__Suppl__3D491139]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceLineItem] DROP CONSTRAINT [DF__InvoiceLi__Suppl__3D491139]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceLi__Trade__48BAC3E5]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceLineItem] DROP CONSTRAINT [DF__InvoiceLi__Trade__48BAC3E5]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceTo__Tot_R__3E3D3572]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceTotal] DROP CONSTRAINT [DF__InvoiceTo__Tot_R__3E3D3572]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceTo__Tot_S__3F3159AB]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceTotal] DROP CONSTRAINT [DF__InvoiceTo__Tot_S__3F3159AB]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceTo__Tot_B__40257DE4]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceTotal] DROP CONSTRAINT [DF__InvoiceTo__Tot_B__40257DE4]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__InvoiceTo__Tot_B__4119A21D]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceTotal] DROP CONSTRAINT [DF__InvoiceTo__Tot_B__4119A21D]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__RuleTyp__0E391C95]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__RuleTyp__0E391C95]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__EntityD__0F2D40CE]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__EntityD__0F2D40CE]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__PaidOut__11158940]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__PaidOut__11158940]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__GSTCalc__382F5661]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__GSTCalc__382F5661]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__ManualR__603D47BB]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__ManualR__603D47BB]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__Individ__4277DAAA]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__Individ__4277DAAA]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__Anticip__4B0D20AB]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__Anticip__4B0D20AB]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__LockedU__190BB0C3]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__LockedU__190BB0C3]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__CalcDat__31A25463]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__CalcDat__31A25463]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_Rule__Collect__3943762B]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_Rule] DROP CONSTRAINT [DF__RB_Rule__Collect__3943762B]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Reall__74444068]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Reall__74444068]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Reall__753864A1]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Reall__753864A1]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Reall__762C88DA]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Reall__762C88DA]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__PostP__4183B671]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__PostP__4183B671]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Total__2EC5E7B8]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Total__2EC5E7B8]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Total__2FBA0BF1]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Total__2FBA0BF1]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RuleIn__Perce__30AE302A]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RuleInvoice] DROP CONSTRAINT [DF__RB_RuleIn__Perce__30AE302A]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RulePe__Suppl__7FEAFD3E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RulePeriod] DROP CONSTRAINT [DF__RB_RulePe__Suppl__7FEAFD3E]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RulePe__PaidO__61316BF4]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RulePeriod] DROP CONSTRAINT [DF__RB_RulePe__PaidO__61316BF4]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RulePe__Expor__4D7F7902]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RulePeriod] DROP CONSTRAINT [DF__RB_RulePe__Expor__4D7F7902]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__RB_RulePe__UseSu__7187CF4E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[RB_RulePeriod] DROP CONSTRAINT [DF__RB_RulePe__UseSu__7187CF4E]
END
GO
GO
/****** Object: Table [dbo].[Invoice] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoice]') AND type in (N'U'))
DROP TABLE [dbo].[Invoice]
GO
/****** Object: Table [dbo].[InvoiceLineItem] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceLineItem]') AND type in (N'U'))
DROP TABLE [dbo].[InvoiceLineItem]
GO
/****** Object: Table [dbo].[InvoiceTotal] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceTotal]') AND type in (N'U'))
DROP TABLE [dbo].[InvoiceTotal]
GO
/****** Object: Table [dbo].[InvoiceTotalTax] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceTotalTax]') AND type in (N'U'))
DROP TABLE [dbo].[InvoiceTotalTax]
GO
/****** Object: Table [dbo].[RB_InvoicesToProcess] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_InvoicesToProcess]') AND type in (N'U'))
DROP TABLE [dbo].[RB_InvoicesToProcess]
GO
/****** Object: Table [dbo].[RB_InvoicesToProcess_Moved] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_InvoicesToProcess_Moved]') AND type in (N'U'))
DROP TABLE [dbo].[RB_InvoicesToProcess_Moved]
GO
/****** Object: Table [dbo].[RB_Rule] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_Rule]') AND type in (N'U'))
DROP TABLE [dbo].[RB_Rule]
GO
/****** Object: Table [dbo].[RB_RuleInvoice] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_RuleInvoice]') AND type in (N'U'))
DROP TABLE [dbo].[RB_RuleInvoice]
GO
/****** Object: Table [dbo].[RB_RulePeriod] Script Date: 06/01/2011 14:51:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_RulePeriod]') AND type in (N'U'))
DROP TABLE [dbo].[RB_RulePeriod]
GO
GO
/****** Object: Table [dbo].[Invoice] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoice](
[InvoiceKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[InvoiceDate] [smalldatetime] NULL,
[InvoiceNumber] [nvarchar](30) NULL,
[PurchaseOrderDate] [smalldatetime] NULL,
[PurchaseOrderNumber] [nvarchar](30) NULL,
[InvoiceType] [nvarchar](10) NULL,
[Currency] [nvarchar](10) NULL,
[GSTNumber] [nvarchar](30) NULL,
[SoldToKey] [decimal](18, 0) NULL,
[SoldToNumber] [nvarchar](20) NULL,
[SoldToName] [nvarchar](60) NULL,
[SoldToAddress1] [nvarchar](55) NULL,
[SoldToAddress2] [nvarchar](55) NULL,
[SoldToCity] [nvarchar](30) NULL,
[SoldToProvince] [nvarchar](10) NULL,
[SoldToPostalCode] [nvarchar](15) NULL,
[ShipToKey] [decimal](18, 0) NULL,
[ShipToNumber] [decimal](18, 5) NULL,
[ShipToName] [nvarchar](60) NULL,
[ShipToAddress1] [nvarchar](55) NULL,
[ShipToAddress2] [nvarchar](55) NULL,
[ShipToCity] [nvarchar](30) NULL,
[ShipToProvince] [nvarchar](10) NULL,
[ShipToPostalCode] [nvarchar](15) NULL,
[RemitToKey] [decimal](18, 0) NULL,
[RemitToNumber] [nvarchar](20) NULL,
[RemitToName] [nvarchar](60) NULL,
[RemitToAddress1] [nvarchar](55) NULL,
[RemitToAddress2] [nvarchar](55) NULL,
[RemitToCity] [nvarchar](30) NULL,
[RemitToProvince] [nvarchar](10) NULL,
[RemitToPostalCode] [nvarchar](15) NULL,
[OrderByNumber] [nvarchar](20) NULL,
[OrderByName] [nvarchar](60) NULL,
[OrderByAddress1] [nvarchar](55) NULL,
[OrderByAddress2] [nvarchar](55) NULL,
[OrderByCity] [nvarchar](30) NULL,
[OrderByProvince] [nvarchar](10) NULL,
[OrderByPostalCode] [nvarchar](15) NULL,
[ContactName] [nvarchar](60) NULL,
[ContactPhoneNumber] [nvarchar](80) NULL,
[SupplierKey] [decimal](18, 0) NULL,
[SupplierName] [nvarchar](60) NULL,
[SupplierNumber] [nvarchar](80) NULL,
[SupplierAddress1] [nvarchar](55) NULL,
[SupplierAddress2] [nvarchar](55) NULL,
[SupplierCity] [nvarchar](30) NULL,
[SupplierProvince] [nvarchar](10) NULL,
[SupplierPostalCode] [nvarchar](15) NULL,
[TermsType] [nvarchar](10) NULL,
[TermsDiscountPercent] [decimal](18, 6) NULL,
[TermsDiscountDueDate] [smalldatetime] NULL,
[TermsDiscountDaysDue] [decimal](18, 6) NULL,
[TermsNetDueDate] [smalldatetime] NULL,
[TermsNetDays] [decimal](18, 6) NULL,
[TermsDiscountAmount] [decimal](18, 6) NULL,
[TermsDescription] [nvarchar](80) NULL,
[ShipDate] [smalldatetime] NULL,
[ShippingMethodOfPayment] [nvarchar](35) NULL,
[LocationQualifier] [nvarchar](10) NULL,
[DeliveryTermsDescription] [nvarchar](80) NULL,
[ShipViaMethod] [nvarchar](35) NULL,
[InternalOrderNumber] [nvarchar](30) NULL,
[MerchandiseTypeCode] [nvarchar](20) NULL,
[PackingSlipNumber] [nvarchar](30) NULL,
[BillOfLadingNumber] [nvarchar](30) NULL,
[WaybillNumber] [nvarchar](30) NULL,
[PromotionNumber] [nvarchar](30) NULL,
[JobNumber] [nvarchar](30) NULL,
[LotNumber] [nvarchar](30) NULL,
[ModelNumber] [nvarchar](30) NULL,
[LotElevation] [nvarchar](30) NULL,
[JobSequenceNumber] [nvarchar](30) NULL,
[RMANumber] [nvarchar](30) NULL,
[QSTNumber] [nvarchar](30) NULL,
[SellerCreditMemo] [nvarchar](30) NULL,
[OriginalInvoiceNumber] [nvarchar](30) NULL,
[StandardCarrierCode] [nvarchar](10) NULL,
[Status] [int] NOT NULL,
[InvoiceDateAdded] [smalldatetime] NOT NULL,
[ExportedFlag] [int] NOT NULL,
[PrintFlag] [int] NOT NULL,
[TestFlag] [int] NOT NULL,
[ActiveWarnings] [int] NOT NULL,
[DataCheckerStatus] [int] NOT NULL,
[Status997Flag] [int] NOT NULL,
[UserStatus] [numeric](18, 0) NOT NULL,
[UserKey] [decimal](18, 0) NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[SourceFileName] [nchar](500) NOT NULL,
[PrintCount] [decimal](18, 0) NOT NULL,
[DeletedFlag] [int] NOT NULL,
[ExportDate] [datetime] NULL,
[RebateCategoriesAdded] [int] NOT NULL,
[LBMXDocumentId] [decimal](18, 0) NOT NULL,
[MBXStatus] [int] NOT NULL,
[BuyingGroupInvoiceNumber] [nvarchar](50) NOT NULL,
[InternalInvoiceType] [nvarchar](6) NULL,
[ShipToNetDueDate] [smalldatetime] NULL,
[ShipToDiscountDueDate] [smalldatetime] NULL,
[ShipToTermsDescription] [nvarchar](80) NULL,
[OriginalNetDueDate] [smalldatetime] NULL,
[OriginalDiscountDueDate] [smalldatetime] NULL,
[OriginalTermsDescription] [nvarchar](80) NULL,
[OriginalTermsDiscountPercent] [decimal](18, 6) NULL,
[OriginalTermsDiscountAmount] [decimal](18, 6) NULL,
[DataSource] [nvarchar](200) NOT NULL,
[DistributionCode] [nvarchar](50) NULL,
[PostAR] [int] NULL,
[PostAP] [int] NULL,
[PostType] [int] NULL,
[TermsCalculationDate] [datetime] NOT NULL,
[ProductCategoryAddedFlag] [int] NOT NULL,
[TransportationMethod] [nvarchar](50) NULL,
[EquipmentInitial] [nvarchar](50) NULL,
[EquipmentNumber] [nvarchar](50) NULL,
[BillingType] [int] NOT NULL,
[AP_EFTStatus] [int] NOT NULL,
[AR_EFTStatus] [int] NOT NULL,
[APSummaryStatus] [int] NOT NULL,
[GroupPO] [nvarchar](50) NULL,
[SubPO] [nvarchar](50) NULL,
[SalesOrderNumber] [nvarchar](50) NULL,
[DeliverToName] [nvarchar](50) NULL,
[DeliverToAddress] [nvarchar](50) NULL,
[DeliverToCity] [nvarchar](50) NULL,
[DeliverToProvince] [nvarchar](50) NULL,
[DeliverToPostalCode] [nvarchar](50) NULL,
[TransportationContact] [nvarchar](50) NULL,
[TransportationPhone] [nvarchar](50) NULL,
[AccountNumber] [nvarchar](20) NULL,
[VesselCode] [nvarchar](10) NULL,
[VesselName] [nvarchar](50) NULL,
[OriginalShipToNumber] [nvarchar](50) NULL,
[ShipToDiscountAmount] [decimal](18, 6) NULL,
[MatchCreditNote] [decimal](18, 0) NOT NULL,
[PromotionStatus] [int] NOT NULL,
[ShipToDiscountPercent] [decimal](18, 6) NULL,
[PostingPeriod] [int] NULL,
[AccountingExportDate] [datetime] NULL,
[AccountingBatchID] [nvarchar](300) NULL,
[FederatedTermsDate] [datetime] NULL,
[Rebate_LockedUserKey] [int] NOT NULL,
[TermsDiscountAddedToBalance] [int] NOT NULL,
[PostingDate] [datetime] NULL,
[TermsDiscountAddedToBalanceAP] [int] NOT NULL,
CONSTRAINT [PK_InvoiceHeader] PRIMARY KEY CLUSTERED
(
[InvoiceKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[InvoiceLineItem] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvoiceLineItem](
[InvoiceLineKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[InvoiceKey] [decimal](18, 0) NOT NULL,
[InvoiceLineNumber] [decimal](18, 0) NOT NULL,
[SupplierProductNumber] [nvarchar](50) NULL,
[ManufacturerProductNumber] [nvarchar](50) NULL,
[UniversalProductCode] [nvarchar](50) NULL,
[BuyerProductNumber] [nvarchar](50) NULL,
[ProductDescription] [text] NULL,
[PiecesShipped] [decimal](18, 6) NULL,
[UnitsShipped] [decimal](18, 6) NULL,
[UnitOfMeasureDescription] [nvarchar](10) NULL,
[OriginalOrderQuantity] [decimal](18, 6) NULL,
[BackOrderedQuantity] [decimal](18, 6) NULL,
[PriceIdentifierCode] [nvarchar](10) NULL,
[UnitPrice] [decimal](18, 9) NULL,
[BasisOfUnitPriceCode] [nvarchar](10) NULL,
[GrossAmount] [decimal](18, 6) NULL,
[Multiplier] [nvarchar](50) NULL,
[DiscountPercent] [decimal](18, 6) NULL,
[ItemDiscountAmount] [decimal](18, 6) NULL,
[NetAmount] [decimal](18, 6) NULL,
[ProductLength] [nvarchar](50) NULL,
[ProductWidth] [nvarchar](50) NULL,
[ProductHeight] [nvarchar](50) NULL,
[ProductWeight] [nvarchar](50) NULL,
[ProductGroup] [nvarchar](30) NULL,
[Inches] [nvarchar](20) NULL,
[ProductRebateCategory] [nvarchar](30) NULL,
[DiscountGrouping] [nvarchar](50) NULL,
[LotNumber] [nvarchar](30) NULL,
[ProductDescription2] [nvarchar](200) NULL,
[InternalProductCategory] [nvarchar](50) NULL,
[OriginalProductRebateCategory] [nvarchar](30) NULL,
[OutOfStockReason] [nvarchar](20) NULL,
[OutOfStockETA] [nvarchar](20) NULL,
[PrimaryPromotionKey] [decimal](18, 0) NULL,
[RetailDiscount] [decimal](18, 5) NOT NULL,
[SupplierDiscount] [decimal](18, 5) NOT NULL,
[TradeDiscountApplied] [int] NOT NULL,
CONSTRAINT [PK_InvoiceLineItem] PRIMARY KEY CLUSTERED
(
[InvoiceLineKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[InvoiceTotal] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvoiceTotal](
[InvoiceTotalKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[InvoiceKey] [decimal](18, 0) NOT NULL,
[TotalInvoiceAmount] [decimal](18, 6) NULL,
[AmountSubjectToTermsDiscount] [decimal](18, 6) NULL,
[AmountPayableAfterTermsDiscount] [decimal](18, 6) NULL,
[TotalNumberDetailLines] [decimal](18, 0) NOT NULL,
[InvoiceSubTotal] [decimal](18, 6) NULL,
[ShipToAmountPayableAfterTermsDiscount] [decimal](18, 6) NULL,
[Tot_RetailDiscount] [decimal](18, 5) NOT NULL,
[Tot_SupplierDiscount] [decimal](18, 5) NOT NULL,
[Tot_BuyingGroupRevisedTotal] [decimal](18, 5) NOT NULL,
[Tot_BuyingGroupMarkup] [decimal](18, 5) NOT NULL,
CONSTRAINT [PK_InvoiceTotal] PRIMARY KEY CLUSTERED
(
[InvoiceTotalKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[InvoiceTotalTax] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvoiceTotalTax](
[InvoiceTotalTaxKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[InvoiceTotalKey] [decimal](18, 0) NOT NULL,
[InvoiceKey] [decimal](18, 0) NOT NULL,
[TaxType] [nvarchar](10) NULL,
[TaxAmount] [decimal](18, 6) NULL,
[TaxPercent] [decimal](18, 6) NULL,
[TaxExemptCode] [nvarchar](10) NULL,
[ProductRebateCategory] [nvarchar](50) NULL,
CONSTRAINT [PK_InvoiceTotalTax] PRIMARY KEY CLUSTERED
(
[InvoiceTotalTaxKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[RB_InvoicesToProcess] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_InvoicesToProcess](
[RB_RuleId] [decimal](18, 0) NOT NULL,
[InvoiceKey] [nvarchar](50) NOT NULL,
[DateAdded] [datetime] NOT NULL,
[RulePeriodId] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[RB_InvoicesToProcess_Moved] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_InvoicesToProcess_Moved](
[RB_RuleId] [decimal](18, 0) NOT NULL,
[InvoiceKey] [nvarchar](50) NOT NULL,
[DateAdded] [datetime] NOT NULL,
[RulePeriodId] [decimal](18, 0) NULL,
[OldDealerKey] [nvarchar](50) NULL
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[RB_Rule] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_Rule](
[RB_RuleId] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[Priority] [int] NOT NULL,
[Status] [int] NOT NULL,
[Description] [nvarchar](200) NOT NULL,
[StartDate] [smalldatetime] NOT NULL,
[EndDate] [smalldatetime] NOT NULL,
[PaymentType] [int] NOT NULL,
[PaymentFrequency] [int] NOT NULL,
[ReportOptions] [int] NOT NULL,
[Payor] [decimal](18, 0) NOT NULL,
[MaximumAmount] [decimal](18, 0) NULL,
[EarnedAccountNumber] [nvarchar](50) NULL,
[ReceivableAccount] [nvarchar](50) NULL,
[GSTAccount] [nvarchar](50) NULL,
[DistributionType] [int] NOT NULL,
[TermsDiscountRebateable] [int] NOT NULL,
[GroupRebateUnits] [decimal](18, 6) NULL,
[IsPartOfSuperRebate] [int] NOT NULL,
[RebatesPaidOut] [nvarchar](50) NULL,
[RebatesPayable] [nvarchar](50) NULL,
[GSTOut] [nvarchar](50) NULL,
[RuleType] [int] NOT NULL,
[EntityDistType] [int] NOT NULL,
[PaidOut] [int] NOT NULL,
[HoldBackAccount] [nvarchar](50) NULL,
[HoldBackPercent] [decimal](18, 4) NULL,
[GSTCalcStatus] [int] NOT NULL,
[ManualRebate] [int] NOT NULL,
[ShortDescription] [nvarchar](1000) NULL,
[ParentRuleId] [decimal](18, 0) NULL,
[UserType] [int] NULL,
[IndividualEntities] [int] NOT NULL,
[AnticipatedRebatePercent] [decimal](18, 6) NOT NULL,
[LockedUserKey] [decimal](18, 0) NOT NULL,
[CustomerProgramId] [nvarchar](50) NULL,
[CalcDateType] [int] NOT NULL,
[CollectTaxFromPayor] [int] NOT NULL
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[RB_RuleInvoice] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_RuleInvoice](
[RuleToInvoiceKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[RB_RuleId] [decimal](18, 0) NOT NULL,
[RB_RulePeriodId] [decimal](18, 0) NOT NULL,
[InvoiceKey] [nvarchar](50) NOT NULL,
[IsAssociated] [int] NOT NULL,
[ForcastData] [bit] NOT NULL,
[RebateUnits] [decimal](18, 6) NOT NULL,
[GrossAmount] [decimal](18, 6) NOT NULL,
[NetAmount] [decimal](18, 6) NOT NULL,
[Units] [decimal](18, 6) NOT NULL,
[DealerId] [nvarchar](50) NOT NULL,
[RebateAmount] [decimal](18, 6) NULL,
[RebateAmountPaid] [decimal](18, 6) NULL,
[RebateAmountActual] [decimal](18, 6) NULL,
[ReallocationAmount] [decimal](18, 6) NOT NULL,
[ReallocationAmountPaid] [decimal](18, 6) NOT NULL,
[ReallocationPaidFlag] [int] NOT NULL,
[PostPeriodAdjustAmount] [decimal](18, 6) NOT NULL,
[TotalInvoiceNotPaid] [decimal](18, 6) NOT NULL,
[TotalReallocation] [decimal](18, 6) NOT NULL,
[PercentPayable] [decimal](18, 6) NOT NULL
) ON [PRIMARY]
GO
GO
/****** Object: Table [dbo].[RB_RulePeriod] Script Date: 06/01/2011 14:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_RulePeriod](
[RB_RulePeriodId] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[RB_RuleId] [decimal](18, 0) NOT NULL,
[GeneratedDate] [datetime] NOT NULL,
[PeriodNumber] [int] NOT NULL,
[PeriodStarting] [datetime] NOT NULL,
[PeriodEnding] [datetime] NULL,
[CalculatedAmount] [decimal](18, 5) NULL,
[ActualAmount] [decimal](18, 5) NULL,
[RebateDate] [datetime] NULL,
[FudgeFactor] [decimal](18, 5) NULL,
[Holdback] [decimal](18, 5) NULL,
[Reconciled] [bit] NOT NULL,
[AmountPaidOut] [decimal](18, 6) NULL,
[TotalUnits] [decimal](18, 6) NOT NULL,
[Notes] [text] NULL,
[RecalculateStatus] [int] NOT NULL,
[SupplierTax] [decimal](18, 6) NOT NULL,
[GLAccount] [nvarchar](50) NULL,
[PaidOut] [int] NOT NULL,
[ExportFlag] [decimal](18, 0) NOT NULL,
[UseSupplierCalculation] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF_Invoice_Status997Flag] DEFAULT (0) FOR [Status997Flag]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF_Invoice_PrintCount] DEFAULT (0) FOR [PrintCount]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF__Invoice__Deleted__44CA3770] DEFAULT (0) FOR [DeletedFlag]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF__Invoice__RebateC__0D44F85C] DEFAULT (0) FOR [RebateCategoriesAdded]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF_Invoice_LBMXDocumentId] DEFAULT (1) FOR [LBMXDocumentId]
GO
ALTER TABLE [dbo].[Invoice] ADD CONSTRAINT [DF_Invoice_MBXStatus] DEFAULT (0) FOR [MBXStatus]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT ('LBMX') FOR [DataSource]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [ProductCategoryAddedFlag]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (3) FOR [BillingType]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [AP_EFTStatus]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [AR_EFTStatus]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [APSummaryStatus]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [MatchCreditNote]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT (0) FOR [PromotionStatus]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT ((0)) FOR [Rebate_LockedUserKey]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT ((0)) FOR [TermsDiscountAddedToBalance]
GO
ALTER TABLE [dbo].[Invoice] ADD DEFAULT ((0)) FOR [TermsDiscountAddedToBalanceAP]
GO
ALTER TABLE [dbo].[InvoiceLineItem] ADD DEFAULT ((0)) FOR [RetailDiscount]
GO
ALTER TABLE [dbo].[InvoiceLineItem] ADD DEFAULT ((0)) FOR [SupplierDiscount]
GO
ALTER TABLE [dbo].[InvoiceLineItem] ADD DEFAULT ((0)) FOR [TradeDiscountApplied]
GO
ALTER TABLE [dbo].[InvoiceTotal] ADD DEFAULT ((0)) FOR [Tot_RetailDiscount]
GO
ALTER TABLE [dbo].[InvoiceTotal] ADD DEFAULT ((0)) FOR [Tot_SupplierDiscount]
GO
ALTER TABLE [dbo].[InvoiceTotal] ADD DEFAULT ((0)) FOR [Tot_BuyingGroupRevisedTotal]
GO
ALTER TABLE [dbo].[InvoiceTotal] ADD DEFAULT ((0)) FOR [Tot_BuyingGroupMarkup]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (2) FOR [RuleType]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (1) FOR [EntityDistType]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (0) FOR [PaidOut]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (1) FOR [GSTCalcStatus]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (0) FOR [ManualRebate]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (0) FOR [IndividualEntities]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (0) FOR [AnticipatedRebatePercent]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT (0) FOR [LockedUserKey]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT ((0)) FOR [CalcDateType]
GO
ALTER TABLE [dbo].[RB_Rule] ADD DEFAULT ((0)) FOR [CollectTaxFromPayor]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT (0) FOR [ReallocationAmount]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT (0) FOR [ReallocationAmountPaid]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT (0) FOR [ReallocationPaidFlag]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT (0) FOR [PostPeriodAdjustAmount]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT ((0)) FOR [TotalInvoiceNotPaid]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT ((0)) FOR [TotalReallocation]
GO
ALTER TABLE [dbo].[RB_RuleInvoice] ADD DEFAULT ((100)) FOR [PercentPayable]
GO
ALTER TABLE [dbo].[RB_RulePeriod] ADD DEFAULT (0) FOR [SupplierTax]
GO
ALTER TABLE [dbo].[RB_RulePeriod] ADD DEFAULT (0) FOR [PaidOut]
GO
ALTER TABLE [dbo].[RB_RulePeriod] ADD DEFAULT ((0)) FOR [ExportFlag]
GO
ALTER TABLE [dbo].[RB_RulePeriod] ADD DEFAULT ((0)) FOR [UseSupplierCalculation]
GO
GO
/****** Object: Table [dbo].[RB_RuleCategory] Script Date: 06/02/2011 08:40:14 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RB_RuleCategory]') AND type in (N'U'))
DROP TABLE [dbo].[RB_RuleCategory]
GO
GO
/****** Object: Table [dbo].[RB_RuleCategory] Script Date: 06/02/2011 08:40:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RB_RuleCategory](
[RB_RuleCategoryId] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[RB_RuleId] [decimal](18, 0) NOT NULL,
[SupplierId] [nvarchar](50) NOT NULL,
[CategoryNumber] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceTotalAdditionalCosts]') AND type in (N'U'))
DROP TABLE [dbo].[RB_RuleCategory]
CREATE TABLE [dbo].[InvoiceTotalAdditionalCosts](
[InvoiceTotalAdditionalChargeKey] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[InvoiceTotalKey] [decimal](18, 0) NOT NULL,
[InvoiceKey] [decimal](18, 0) NOT NULL,
[Type] [nvarchar](10) NULL,
[Code] [nvarchar](10) NULL,
[Amount] [nvarchar](50) NULL,
[Description] [nvarchar](300) NULL,
[ItemIndex] [int] NULL,
[ProductRebateCategory] [nvarchar](50) NULL,
[RetailDiscount] [decimal](18, 5) NOT NULL,
[SupplierDiscount] [decimal](18, 5) NOT NULL,
[TradeDiscountApplied] [int] NOT NULL,
[InternalType] [int] NOT NULL,
CONSTRAINT [PK_InvoiceTotalAdditionalCosts] PRIMARY KEY CLUSTERED
(
[InvoiceTotalAdditionalChargeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InvoiceTotalAdditionalCosts] ADD DEFAULT ((0)) FOR [RetailDiscount]
GO
ALTER TABLE [dbo].[InvoiceTotalAdditionalCosts] ADD DEFAULT ((0)) FOR [SupplierDiscount]
GO
ALTER TABLE [dbo].[InvoiceTotalAdditionalCosts] ADD DEFAULT ((0)) FOR [TradeDiscountApplied]
GO
ALTER TABLE [dbo].[InvoiceTotalAdditionalCosts] ADD DEFAULT ((0)) FOR [InternalType]
GO
Data Import SQL
-- insert into the rules to be procrssed quue
insert into rb_invoicestoprocess (RB_Ruleid,InvoiceKey,DateAdded)
SELECT '1','1','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','10','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','2','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','3','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','4','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','5','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','6','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','7','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','8','Jun 1 2011 9:08PM' UNION ALL
SELECT '1','9','Jun 1 2011 9:08PM'
-- inser the rule data
SET IDENTITY_INSERT rb_rule ON
insert into rb_rule
(RB_Ruleid,Priority,Status,Description,StartDate,EndDate,PaymentType,PaymentFrequency,ReportOptions,Payor,MaximumAmount,EarnedAccountNumber,ReceivableAccount
,GSTAccount,DistributionType,TermsDiscountRebateable,IsPartOfSuperRebate,RebatesPaidOut,RebatesPayable,RuleType,EntityDistType
,PaidOut,GSTCalcStatus,ManualRebate,ShortDescription,IndividualEntities,AnticipatedRebatePercent,LockedUserKey,CalcDateType,CollectTaxFromPayor)
SELECT '1','1','1','Tony Test Program','Jan 1 2011 12:00AM','Dec 31 2011 12:00AM','1','1','1','9988','100000','3000','1206','2191','1','0','0','8005','2020','2','1','0','0','0','Test','0','0.020000','0','0','0'
SET IDENTITY_INSERT rb_rule OFF
-- rule period data
SET IDENTITY_INSERT rb_ruleperiod ON
insert into rb_ruleperiod
(RB_RulePeriodid,RB_ruleId,GeneratedDate,PeriodNumber,PeriodStarting,PeriodEnding,CalculatedAmount,ActualAmount,FudgeFactor,Holdback,Reconciled,AmountPaidOut,TotalUnits,RecalculateStatus
,SupplierTax,PaidOut,ExportFlag,UseSupplierCalculation)
SELECT '1','1','Jun 1 2011 4:03PM','1','Jan 1 2011 12:00AM','Jan 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '2','1','Jun 1 2011 4:03PM','2','Feb 1 2011 12:00AM','Feb 28 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '3','1','Jun 1 2011 4:03PM','3','Mar 1 2011 12:00AM','Mar 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '4','1','Jun 1 2011 4:03PM','4','Apr 1 2011 12:00AM','Apr 30 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '5','1','Jun 1 2011 4:03PM','5','May 1 2011 12:00AM','May 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '6','1','Jun 1 2011 4:03PM','6','Jun 1 2011 12:00AM','Jun 30 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '7','1','Jun 1 2011 4:03PM','7','Jul 1 2011 12:00AM','Jul 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '8','1','Jun 1 2011 4:03PM','8','Aug 1 2011 12:00AM','Aug 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '9','1','Jun 1 2011 4:03PM','9','Sep 1 2011 12:00AM','Sep 30 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '10','1','Jun 1 2011 4:03PM','10','Oct 1 2011 12:00AM','Oct 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '11','1','Jun 1 2011 4:03PM','11','Nov 1 2011 12:00AM','Nov 30 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0' UNION ALL
SELECT '12','1','Jun 1 2011 4:03PM','12','Dec 1 2011 12:00AM','Dec 31 2011 12:00AM','0.00000','0.00000','0.00000','0.00000','0','0.000000','0.000000','0','0.000000','0','0','0'
SET IDENTITY_INSERT rb_ruleperiod OFF
-- rule category
SET IDENTITY_INSERT RB_RuleCategory on
insert into RB_RuleCategory
(RB_RuleCategoryid,RB_ruleId,SupplierId,CategoryNumber)
SELECT '64225','1','9988','100'
SET IDENTITY_INSERT RB_RuleCategory OFF
-- InvoiceHeader
SET IDENTITY_INSERT Invoice ON
insert into Invoice
(
InvoiceKey,InvoiceDate,InvoiceNumber,PurchaseOrderNumber,
SoldToKey, SoldToNumber, SoldToName, SoldToAddress1,
SoldToCity, SoldToProvince, SoldToPostalCode, ShipToKey,
ShipToNumber, ShipToName, ShipToAddress1, ShipToCity,
ShipToProvince, ShipToPostalCode, SupplierKey, SupplierNumber,
SupplierAddress1, SupplierCity, SupplierProvince, SupplierPostalCode,
TermsDiscountAmount, Status, InvoiceDateAdded, ExportedFlag,
PrintFlag, TestFlag, ActiveWarnings, DataCheckerstatus,
status997flag, userstatus, userkey, lastupdate,
sourcefilename,
PrintCount, DeletedFlag, RebateCategoriesAdded,
LBMXDocumentId, mbxstatus, buyinggroupinvoicenumber, InternalInvoiceType,
OriginalTermsDiscountAmount, DataSource, TermsCalculationDate,
ProductCategoryAddedFlag, BillingType, AP_EFTStatus, AR_EFTStatus,
APSummaryStatus, OriginalShipToNumber, ShipToDiscountAmount, MatchCreditNote,
PromotionStatus, AccountingExportDate, Rebate_LockedUserKey, TermsDiscountAddedToBalance,
TermsDiscountAddedToBalanceAP
)
SELECT '1','Jan 24 2011 12:00AM','7461936301','RA6515324287','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461936301','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '2','Jan 24 2011 12:00AM','7461936905','RA6515323892','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461936905','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '3','Jan 24 2011 12:00AM','7461937804','101460930','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461937804','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '4','Jan 24 2011 12:00AM','7461937979','1211101','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461937979','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '5','Jan 24 2011 12:00AM','7461938023','106208806FAX','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461938023','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '6','Jan 24 2011 12:00AM','7461938130','11911.1','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461938130','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '7','Jan 24 2011 12:00AM','7461938131','11911','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461938131','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '8','Jan 24 2011 12:00AM','7461938361','101359336','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461938361','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '9','Jan 22 2011 12:00AM','7461939243','11X000013 00','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461939243','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0' UNION ALL
SELECT '10','Jan 24 2011 12:00AM','7461939423','106208809FAX','4165','1','Tim-Br Marts Limited GST # 105287304','Suite 705 - 1601 Airport Road N.E.','Calgary','AB','T2E 6Z8','17878','6650.00000','DORSET TIM-BR MART','#0178 59 KAWAGAMA LAKE RD, PO BX 10','DORSET','ON','P0A1E0','9988','123456','123 Test Street','Test','ON','N0L1G9','0.000000','0','Jun 1 2011 3:55PM','0','0','0','0','0','0','0','3','Jun 1 2011 3:54PM','LBMX','0','0','2','1','0','7461939423','CAR','0.000000','LBMX','Jun 1 2011 3:54PM','0','1','0','0','0','6650','0.000000','0','0','Jun 1 2011 3:54PM','0','0','0'
SET IDENTITY_INSERT invoice OFF
-- invoicetotal record
SET IDENTITY_INSERT InvoiceTotal ON
insert into InvoiceTotal
(
InvoiceTotalKey,InvoiceKey,totalInvoiceAmount,AmountSubjectToTermsDiscount,
AmountPayableAfterTermsDiscount,TotalNumberDetailLines, InvoiceSubTotal,ShipToAmountPayableAfterTermsDiscount,
Tot_RetailDiscount,Tot_SupplierDiscount,Tot_BuyingGroupRevisedTotal,Tot_BuyingGroupMarkup
)
SELECT '1','1','0.000000','0.000000','0.000000','0','0.000000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '2','2','0.000000','0.000000','0.000000','0','0.000000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '3','3','28.520000','0.000000','0.000000','0','28.520000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '4','4','75.030000','0.000000','0.000000','0','75.030000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '5','5','640.410000','0.000000','0.000000','0','640.410000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '6','6','788.540000','0.000000','0.000000','0','788.540000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '7','7','822.240000','0.000000','0.000000','0','822.240000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '8','8','366.760000','0.000000','0.000000','0','366.760000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '9','9','97.040000','0.000000','0.000000','0','97.040000','0.000000','0.00000','0.00000','0.00000','0.00000' UNION ALL
SELECT '10','10','959.700000','0.000000','0.000000','0','959.700000','0.000000','0.00000','0.00000','0.00000','0.00000'
SET IDENTITY_INSERT InvoiceTotal OFF
-- line item data
SET IDENTITY_INSERT InvoiceLineItem ON
insert into InvoiceLineItem
(
InvoiceLineKey, InvoiceKey, InvoiceLineNumber, SupplierProductNumber,
ProductDescription, PiecesShipped, UnitsShipped, OriginalOrderQuantity,
BackOrderedQuantity, UnitPrice, GrossAmount, ItemDiscountAmount,
NetAmount, ProductRebateCategory, RetailDiscount, SupplierDiscount,
TradeDiscountApplied
)
SELECT '1','1','1','0','DELIVERY SURCHARGE','0.000000','0.000000','0.000000','0.000000','1.000000000','0.000000','0.000000','0.000000','100','0.00000','0.00000','0' UNION ALL
SELECT '2','1','1','9091414291','RETURN KIT-DEA','1.000000','1.000000','0.000000','0.000000','0.000000000','0.000000','0.000000','0.000000','100','0.00000','0.00000','0' UNION ALL
SELECT '3','2','1','9091414291','RETURN KIT-DEA','1.000000','1.000000','0.000000','0.000000','0.000000000','0.000000','0.000000','0.000000','100','0.00000','0.00000','0' UNION ALL
SELECT '4','2','1','0','DELIVERY SURCHARGE','0.000000','0.000000','0.000000','0.000000','1.000000000','0.000000','0.000000','0.000000','100','0.00000','0.00000','0' UNION ALL
SELECT '5','3','1','54457125','METHADONE TAB 10MG ROX 100@','4.000000','4.000000','0.000000','0.000000','7.130000000','28.520000','0.000000','28.520000','100','0.00000','0.00000','0' UNION ALL
SELECT '7','4','1','555097402','AMPHETAM SALT TB 30MG TEV 100@','0.000000','0.000000','0.000000','0.000000','16.240000000','0.000000','0.000000','0.000000','100','0.00000','0.00000','0' UNION ALL
SELECT '8','4','1','60951071270','ENDOCET TB 10 325MG ENDO 100@','1.000000','1.000000','0.000000','0.000000','35.990000000','35.990000','0.000000','35.990000','100','0.00000','0.00000','0' UNION ALL
SELECT '6','4','1','60951079770','ENDOCET TAB 10 650MG ENDO 100@','1.000000','1.000000','0.000000','0.000000','39.040000000','39.040000','0.000000','39.040000','100','0.00000','0.00000','0' UNION ALL
SELECT '9','5','1','54092038701','ADDERALL XR CAP 20MG 100','1.000000','1.000000','0.000000','0.000000','640.410000000','640.410000','0.000000','640.410000','100','0.00000','0.00000','0' UNION ALL
SELECT '10','6','1','406853001','OXYCOD HCL TAB 30MG MALL 100@','6.000000','6.000000','0.000000','0.000000','31.990000000','191.940000','0.000000','191.940000','100','0.00000','0.00000','0' UNION ALL
SELECT '11','6','1','59011044010','OXYCONTIN CR TAB 40MG (REF)100','1.000000','1.000000','0.000000','0.000000','596.600000000','596.600000','0.000000','596.600000','100','0.00000','0.00000','0' UNION ALL
SELECT '13','7','1','406853001','OXYCOD HCL TAB 30MG MALL 100@','6.000000','6.000000','0.000000','0.000000','31.990000000','191.940000','0.000000','191.940000','100','0.00000','0.00000','0' UNION ALL
SELECT '14','7','1','60951070070','ENDOCET TB 7.5 325MG ENDO 100@','6.000000','6.000000','0.000000','0.000000','33.070000000','198.420000','0.000000','198.420000','100','0.00000','0.00000','0' UNION ALL
SELECT '12','7','1','60951071270','ENDOCET TB 10 325MG ENDO 100@','12.000000','12.000000','0.000000','0.000000','35.990000000','431.880000','0.000000','431.880000','100','0.00000','0.00000','0' UNION ALL
SELECT '16','8','1','54023525','MORPHINE IR TAB 15MG ROX 100@','2.000000','2.000000','0.000000','0.000000','6.850000000','13.700000','0.000000','13.700000','100','0.00000','0.00000','0' UNION ALL
SELECT '15','8','1','555097202','AMPHETAM SALT TB 10MG TEV 100@','2.000000','2.000000','0.000000','0.000000','15.880000000','31.760000','0.000000','31.760000','100','0.00000','0.00000','0' UNION ALL
SELECT '17','8','1','378710301','OXYCOD ACET 2.5 325 MYLN 100@','2.000000','2.000000','0.000000','0.000000','160.650000000','321.300000','0.000000','321.300000','100','0.00000','0.00000','0' UNION ALL
SELECT '18','9','1','54035250','MORPHINE SU OS 20MG ROX 120ML@','2.000000','2.000000','0.000000','0.000000','48.520000000','97.040000','0.000000','97.040000','100','0.00000','0.00000','0' UNION ALL
SELECT '19','10','1','406853001','OXYCOD HCL TAB 30MG MALL 100@','30.000000','30.000000','0.000000','0.000000','31.990000000','959.700000','0.000000','959.700000','100','0.00000','0.00000','0'
SET IDENTITY_INSERT InvoiceLineItem OFF
Stored Procedure that calculates the units on 1 invoice and returns the results of the calculation
if object_id('dbo.sp_CalculateRuleInvoiceUnits_Single') is not null drop procedure dbo.sp_CalculateRuleInvoiceUnits_Single
CREATE PROCEDURE sp_CalculateRuleInvoiceUnits_Single @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6),@InvoiceKey VARCHAR(1000),@RebateUnits decimal(18,6) OUTPUT,@TotalGross decimal(18,6) OUTPUT,@TotalNet decimal(18,6) OUTPUT,@TotalUnits decimal(18,6) OUTPUT
as
DECLARE @InvoiceDate as date
DECLARE @IsAssociated integer
DECLARE @TotalInvoiceAmount decimal(18,6)
DECLARE @termsdiscountamount decimal(18,6)
DECLARE @InvoiceSubTotal decimal(18,6)
DECLARE @supplierkey VARCHAR(1000)
DECLARE @UnitsShipped decimal(18,6)
DECLARE @GrossAmount decimal(18,6)
DECLARE @NetAmount decimal(18,6)
DECLARE @ChargeType varchar(20)
DECLARE @ShipToKey varchar(20)
DECLARE @RulePeriodId nvarchar(30)
DECLARE @TotalChargeUnits decimal(18,6)
DECLARE @TotalChargeGross decimal(18,6)
DECLARE @TotalChargeNet decimal(18,6)
DECLARE @UnitsToDeductFromTerms decimal(18,6)
DECLARE @TermsPercent as decimal(18,6)
set @RebateUnits = 0
select @TotalInvoiceAmount=TotalInvoiceAmount,@InvoiceSubTotal= InvoiceSubTotal,@termsdiscountamount=termsdiscountamount
from invoicetotal a,invoice b
where a.invoicekey = b.invoicekey
and a.invoicekey = @InvoiceKey
set @UnitsToDeductFromTerms = 0
set @RebateUnits = 0
set @TotalGross = 0
set @TotalNet = 0
set @TotalUnits = 0
set @TotalChargeUnits = 0
set @TotalChargeGross = 0
set @TotalChargeNet =0
SELECT @TotalUnits = SUM(unitsshipped),
@TotalGross = SUM(grossamount),
@TotalNet = SUM(netamount)
from
(
select UnitsShipped as unitsshipped,GrossAmount as grossamount,NetAmount as netamount from InvoiceLineItem a,Invoice b where a.invoicekey = @InvoiceKey and a.invoicekey = b.invoicekey and a.ProductRebateCategory in (select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = @RuleId and c.SupplierId = B.supplierkey)
union
select 1 as unitsshipped,a.TaxAmount as grossamount,a.TaxAmount as netamount from invoicetotaltax a,Invoice b where a.invoicekey = @InvoiceKey and a.invoicekey = b.invoicekey and a.ProductRebateCategory in (select c.categorynumber from RB_RuleCategory c where c.rb_ruleid = @RuleId and c.SupplierId = B.supplierkey)
) as tftest
--print @RuleUnitType
--print @TotalGross
--print @TotalNet
--print @TotalUnits
set @TotalChargeUnits = 0
set @TotalChargeGross = 0
set @TotalChargeNet = 0
SELECT @TotalChargeUnits = SUM(1),
@TotalChargeGross = SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END),
@TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(Amount) ELSE Amount END)
-- Following columns were being added to from the previous query...
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE c.RB_RuleID = @RuleId and b.InvoiceKey = @InvoiceKey;
if ISNUMERIC(@TotalChargeGross) =1
begin
set @TotalGross = @TotalGross + @TotalChargeGross
end
if ISNUMERIC(@TotalChargeNet) = 1
begin
set @TotalNet = @TotalNet + @TotalChargeNet
end
if ISNUMERIC(@TotalChargeUnits) =1
begin
set @TotalUnits = @TotalUnits + @TotalChargeUnits
end
--print @RuleUnitType
--print @TotalGross
--print @TotalNet
--print @TotalUnits
-- now calculate the final totals
select @RebateUnits = case (@RuleUnitType)
WHEN 1 THEN @TotalGross
WHEN 2 THEN @TotalNet
WHEN 3 THEN @TotalUnits
END
--print 'test'
--print @RebateUnits
--print 'test'
select @UnitsToDeductFromTerms= case (@RuleUnitType)
WHEN 1 THEN @TotalChargeGross
WHEN 2 THEN @TotalChargeNet
WHEN 3 THEN @TotalChargeUnits
END
if @termsdiscountamount <> 0 and @TermsRuleType <> 0
begin
if @TermsRuleType = 1
begin
set @TermsPercent = @termsdiscountamount / @TotalInvoiceAmount
end
else
begin
set @TermsPercent = @termsdiscountamount / @InvoiceSubTotal
end
if @TermsPercent <> 0
begin
set @RebateUnits = @RebateUnits - @UnitsToDeductFromTerms
set @RebateUnits = @RebateUnits - ((@UnitsToDeductFromTerms) * @TermsPercent)
set @RebateUnits = @RebateUnits + @UnitsToDeductFromTerms
end
end
--print @InvoiceKey
--print @RebateUnits
Stored Procedure that loops through all invoices in a queue , calculates the units and stores the result in a new table
--DROP PROCEDURE sp_CalculateRuleInvoiceUnits
CREATE PROCEDURE sp_CalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)
as
DECLARE @InvoiceDate as date
DECLARE @shiptodiscountduedate as date
DECLARE @shiptonetduedate as date
DECLARE @termsdiscountduedate as date
DECLARE @termsnetduedate as date
DECLARE @IsAssociated integer
DECLARE @InvoiceKey VARCHAR(1000)
DECLARE @TotalInvoiceAmount decimal(18,6)
DECLARE @termsdiscountamount decimal(18,6)
DECLARE @InvoiceSubTotal decimal(18,6)
DECLARE @supplierkey VARCHAR(1000)
DECLARE @UnitsShipped decimal(18,6)
DECLARE @GrossAmount decimal(18,6)
DECLARE @NetAmount decimal(18,6)
DECLARE @ChargeType varchar(20)
DECLARE @ShipToKey varchar(20)
DECLARE @RebateUnits decimal(18,6)
DECLARE @TotalGross decimal(18,6)
DECLARE @TotalNet decimal(18,6)
DECLARE @TotalUnits decimal(18,6)
DECLARE @RulePeriodId nvarchar(30)
DECLARE @TotalChargeUnits decimal(18,6)
DECLARE @TotalChargeGross decimal(18,6)
DECLARE @TotalChargeNet decimal(18,6)
DECLARE @UnitsToDeductFromTerms decimal(18,6)
DECLARE @DateCalculationFieldIdentifier integer
DECLARE @DateCalculationFieldName nvarchar(100)
DECLARE @TermsPercent as decimal(18,6)
select @DateCalculationFieldIdentifier = calcdatetype from RB_Rule where rb_ruleid = @RuleId
DECLARE curMain CURSOR STATIC LOCAL FOR
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate from invoicetotal a,invoice b,rb_invoicestoprocess c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
union
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate from invoicetotal a,invoice b,RB_InvoicesToProcess_Moved c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
open curMain
while 1=1
BEGIN
FETCH curMain INTO @InvoiceKey,@InvoiceDate,@ShipToKey,@shiptodiscountduedate,@shiptonetduedate,@termsdiscountduedate,@termsnetduedate
IF @@fetch_status <> 0
break
select @InvoiceDate= case (@DateCalculationFieldIdentifier)
WHEN 0 THEN @InvoiceDate
WHEN 1 THEN @shiptodiscountduedate
WHEN 2 THEN @shiptonetduedate
WHEN 3 THEN @termsdiscountduedate
WHEN 4 THEN @termsnetduedate
else @InvoiceDate
END
--print @InvoiceDate
exec sp_CalculateRuleInvoiceUnits_Single @RuleUnitType,@TermsRuleType,@RuleId,@InvoiceKey,@RebateUnits OUTPUT,@TotalGross OUTPUT,@TotalNet OUTPUT,@TotalUnits OUTPUT
set @IsAssociated = 1
select @IsAssociated= case (@RulePeriodId)
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
else 1
END
if @RulePeriodId is null or @RulePeriodId = -1 or @RulePeriodId = -3
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting <= @InvoiceDate
and periodending >= @InvoiceDate
and reconciled = 0
if isnumeric(@RulePeriodId) = 0
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting >= @InvoiceDate
and reconciled = 0
order by rb_ruleperiodid asc
end
end
INSERT INTO [RB_RuleInvoice]
([RB_RuleId]
,[RB_RulePeriodId]
,[InvoiceKey]
,[IsAssociated]
,[ForcastData]
,[RebateUnits]
,[GrossAmount]
,[NetAmount]
,[Units]
,[DealerId]
,[RebateAmount]
,[RebateAmountPaid]
,[RebateAmountActual]
,[ReallocationAmount]
,[ReallocationAmountPaid]
,[ReallocationPaidFlag]
,[PostPeriodAdjustAmount]
,[TotalInvoiceNotPaid]
,[TotalReallocation]
,[PercentPayable])
values
(@RuleId,@RulePeriodId,@InvoiceKey,@IsAssociated,0,@RebateUnits,@TotalGross,@TotalNet,@TotalUnits,@ShipToKey,0,0,0,0,0,0,0,0,0,0)
end
Deallocate curMain
Index Creation Code -- these indexes were generated simply by using the built in tools of SQL server
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceLineItem_43_800057936__K2_K28_K17_K21_K1_10] ON [dbo].[InvoiceLineItem]
(
[InvoiceKey] ASC,
[ProductRebateCategory] ASC,
[GrossAmount] ASC,
[NetAmount] ASC,
[InvoiceLineKey] ASC
)
INCLUDE ( [UnitsShipped]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_800057936_17_21_1_2] ON [dbo].[InvoiceLineItem]([GrossAmount], [NetAmount], [InvoiceLineKey], [InvoiceKey])
go
CREATE STATISTICS [_dta_stat_800057936_28_2_17_21_1] ON [dbo].[InvoiceLineItem]([ProductRebateCategory], [InvoiceKey], [GrossAmount], [NetAmount], [InvoiceLineKey])
go
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceTotal_43_832058050__K2_3_7] ON [dbo].[InvoiceTotal]
(
[InvoiceKey] ASC
)
INCLUDE ( [TotalInvoiceAmount],
[InvoiceSubTotal]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_InvoiceTotal_43_832058050__K2] ON [dbo].[InvoiceTotal]
(
[InvoiceKey] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_768057822_1_42] ON [dbo].[Invoice]([InvoiceKey], [SupplierKey])
go
CREATE STATISTICS [_dta_stat_768057822_1_2_17_100_99_52] ON [dbo].[Invoice]([InvoiceKey], [InvoiceDate], [ShipToKey], [ShipToDiscountDueDate], [ShipToNetDueDate], [TermsDiscountDueDate])
go
CREATE STATISTICS [_dta_stat_768057822_2_17_100_99_52_54_1] ON [dbo].[Invoice]([InvoiceDate], [ShipToKey], [ShipToDiscountDueDate], [ShipToNetDueDate], [TermsDiscountDueDate], [TermsNetDueDate], [InvoiceKey])
go
CREATE STATISTICS [_dta_stat_896058278_1_2] ON [dbo].[RB_InvoicesToProcess]([RB_RuleId], [InvoiceKey])
go
Testing Code to execute
truncate table rb_ruleinvoice
sp_CalculateRuleInvoiceUnits 1,1,1
select * from rb_ruleInvoice
June 2, 2011 at 9:42 am
Excellent... this will help tremendously.
I've noticed two things in the sp_CalculateRuleInvoiceUnits procedure:
1. These variables are no longer used - probably as a result of eliminating cursors:
-- variables not currently being used
DECLARE @TotalInvoiceAmount DECIMAL(18, 6),
@termsdiscountamount DECIMAL(18, 6),
@InvoiceSubTotal DECIMAL(18, 6),
@supplierkey VARCHAR(1000),
@UnitsShipped DECIMAL(18, 6),
@GrossAmount DECIMAL(18, 6),
@NetAmount DECIMAL(18, 6),
@ChargeType VARCHAR(20),
@TotalChargeUnits DECIMAL(18, 6),
@TotalChargeGross DECIMAL(18, 6),
@TotalChargeNet DECIMAL(18, 6),
@UnitsToDeductFromTerms DECIMAL(18, 6),
@DateCalculationFieldName NVARCHAR(100),
@TermsPercent AS DECIMAL(18, 6) ;
2. This variable is used before a value has been assigned to it. Can you investigate the original code to see if this was the case then, and if not where a value was assigned to it at?
-- variables used before value is assigned to it
DECLARE @RulePeriodId NVARCHAR(30);
Question: is the sp_CalculateRuleInvoiceUnits_Single procedure ever called outside of the sp_CalculateRuleInvoiceUnits procedure?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 10:59 am
Wayne,
Item #1
- I removed the variables you mentioned. Does having these extra variables "hanging around" cause preformance issues?
Item#2
I missed something in the conversion -- and that varibale was not set....
Here is the updated procedure with the extra variables removed and the fix to item #2
CREATE PROCEDURE sp_CalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)
as
DECLARE @InvoiceDate as date
DECLARE @shiptodiscountduedate as date
DECLARE @shiptonetduedate as date
DECLARE @termsdiscountduedate as date
DECLARE @termsnetduedate as date
DECLARE @IsAssociated integer
DECLARE @InvoiceKey VARCHAR(1000)
DECLARE @ShipToKey varchar(20)
DECLARE @RebateUnits decimal(18,6)
DECLARE @TotalGross decimal(18,6)
DECLARE @TotalNet decimal(18,6)
DECLARE @TotalUnits decimal(18,6)
DECLARE @RulePeriodId nvarchar(30)
DECLARE @DateCalculationFieldIdentifier integer
select @DateCalculationFieldIdentifier = calcdatetype from RB_Rule where rb_ruleid = @RuleId
DECLARE curMain CURSOR STATIC LOCAL FOR
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate,ruleperiodid from invoicetotal a,invoice b,rb_invoicestoprocess c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
union
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate,ruleperiodid from invoicetotal a,invoice b,RB_InvoicesToProcess_Moved c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
open curMain
while 1=1
BEGIN
FETCH curMain INTO @InvoiceKey,@InvoiceDate,@ShipToKey,@shiptodiscountduedate,@shiptonetduedate,@termsdiscountduedate,@termsnetduedate,@RulePeriodId
IF @@fetch_status <> 0
break
select @InvoiceDate= case (@DateCalculationFieldIdentifier)
WHEN 0 THEN @InvoiceDate
WHEN 1 THEN @shiptodiscountduedate
WHEN 2 THEN @shiptonetduedate
WHEN 3 THEN @termsdiscountduedate
WHEN 4 THEN @termsnetduedate
else @InvoiceDate
END
--print @InvoiceDate
exec sp_CalculateRuleInvoiceUnits_Single @RuleUnitType,@TermsRuleType,@RuleId,@InvoiceKey,@RebateUnits OUTPUT,@TotalGross OUTPUT,@TotalNet OUTPUT,@TotalUnits OUTPUT
set @IsAssociated = 1
select @IsAssociated= case (@RulePeriodId)
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
else 1
END
if @RulePeriodId is null or @RulePeriodId = -1 or @RulePeriodId = -3
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting <= @InvoiceDate
and periodending >= @InvoiceDate
and reconciled = 0
if isnumeric(@RulePeriodId) = 0
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting >= @InvoiceDate
and reconciled = 0
order by rb_ruleperiodid asc
end
end
INSERT INTO [RB_RuleInvoice]
([RB_RuleId]
,[RB_RulePeriodId]
,[InvoiceKey]
,[IsAssociated]
,[ForcastData]
,[RebateUnits]
,[GrossAmount]
,[NetAmount]
,[Units]
,[DealerId]
,[RebateAmount]
,[RebateAmountPaid]
,[RebateAmountActual]
,[ReallocationAmount]
,[ReallocationAmountPaid]
,[ReallocationPaidFlag]
,[PostPeriodAdjustAmount]
,[TotalInvoiceNotPaid]
,[TotalReallocation]
,[PercentPayable])
values
(@RuleId,@RulePeriodId,@InvoiceKey,@IsAssociated,0,@RebateUnits,@TotalGross,@TotalNet,@TotalUnits,@ShipToKey,0,0,0,0,0,0,0,0,0,0)
end
Deallocate curMain
Item#3
Yes I do call the other stored procedure directrly from my client application.
In this case I simply want to calculate the units and not do the "other" stuff in the second procedure. Now, if you tell me that I will cut the preformance down from minutes to seconds by combining them -- you will not have to convince me very hard to do that and create a second (copy) of the procedure that can only be called directly
June 2, 2011 at 12:15 pm
First of all, change those stored procedure names. Do NOT prefix them with sp_ - this makes SQL look in the master database first.
Okay, here is step 1. We're going to put all of the logic of the spCalculateRuleInvoiceUnits_Single procedure into an in-line table-valued function. Since the spCalculateRuleInvoiceUnits_Single procedure is directly used by the application, it will be reduced to just selecting from this function. Step 2 (coming later) will also use the iTVF.
Oh... the @InvoiceKey parameter has been changed from a varchar(1000) to a decimal(18,0) to match the actual data type of the column in the tables.
IF OBJECT_ID('dbo.fnCalculateRuleInvoiceUnits_Single') IS NOT NULL
DROP FUNCTION dbo.fnCalculateRuleInvoiceUnits_Single
GO
CREATE FUNCTION dbo.fnCalculateRuleInvoiceUnits_Single (
@RuleUnitType numeric(9,2),
@TermsRuleType numeric(9,2),
@RuleId decimal(18,6),
@InvoiceKey DECIMAL(18,0))
RETURNS TABLE
AS
RETURN
WITH cte AS
(
SELECT b.TermsDiscountAmount,
TermsPercent = b.termsdiscountamount / CASE WHEN @TermsRuleType = 1 THEN a.TotalInvoiceAmount ELSE a.InvoiceSubTotal END
FROM dbo.InvoiceTotal a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.InvoiceKey = @InvoiceKey
), cteTotals AS
(
SELECT TotalUnits = SUM(unitsshipped),
TotalGross = SUM(grossamount),
TotalNet = SUM(netamount)
FROM (SELECT UnitsShipped AS unitsshipped,
GrossAmount AS grossamount,
NetAmount AS netamount
FROM dbo.InvoiceLineItem a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.invoicekey = @InvoiceKey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM dbo.RB_RuleCategory c
WHERE c.rb_ruleid = @RuleId
AND c.SupplierId = B.supplierkey)
UNION
SELECT 1 AS unitsshipped,
a.TaxAmount AS grossamount,
a.TaxAmount AS netamount
FROM dbo.InvoiceTotalTax a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.invoicekey = @InvoiceKey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM dbo.RB_RuleCategory c
WHERE c.rb_ruleid = @RuleId
AND c.SupplierId = B.supplierkey)) AS tftest
), cteTotalCharge AS
(
SELECT TotalChargeUnits = SUM(1),
TotalChargeGross = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END),
TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END)
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE c.RB_RuleID = @RuleId
AND b.InvoiceKey = @InvoiceKey
)
SELECT RebateUnits = CONVERT(DECIMAL(18,6),
ISNULL(CASE WHEN cte.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND cte.TermsPercent <> 0
THEN caTotals.RebateUnits - caCharge.UnitsToDeductFromTerms
- ((caCharge.UnitsToDeductFromTerms) * cte.TermsPercent)
+ caCharge.UnitsToDeductFromTerms
ELSE caTotals.RebateUnits
END
, 0)),
TotalUnits = CONVERT(DECIMAL(18,6), caTotals.TotalUnits + ISNULL(caCharge.TotalChargeUnits,0)),
TotalGross = CONVERT(DECIMAL(18,6), caTotals.TotalGross + ISNULL(caCharge.TotalChargeGross,0)),
TotalNet = CONVERT(DECIMAL(18,6), caTotals.TotalNet + ISNULL(caCharge.TotalChargeNet,0))
FROM cte
CROSS APPLY (SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN TotalGross
WHEN 2 THEN TotalNet
WHEN 3 THEN TotalUnits
END
FROM cteTotals) caTotals
CROSS APPLY (SELECT *, UnitsToDeductFromTerms =
CASE (@RuleUnitType)
WHEN 1 THEN TotalChargeGross
WHEN 2 THEN TotalChargeNet
WHEN 3 THEN TotalChargeUnits
END
FROM cteTotalCharge) caCharge;
GO
if object_id('dbo.spCalculateRuleInvoiceUnits_Single') is not null drop procedure dbo.spCalculateRuleInvoiceUnits_Single
GO
CREATE PROCEDURE spCalculateRuleInvoiceUnits_Single @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6),@InvoiceKey DECIMAL(18,0),@RebateUnits decimal(18,6) OUTPUT,@TotalGross decimal(18,6) OUTPUT,@TotalNet decimal(18,6) OUTPUT,@TotalUnits decimal(18,6) OUTPUT
AS
SELECT @RebateUnits = RebateUnits,
@TotalUnits = TotalUnits,
@TotalGross = TotalGross,
@TotalNet = TotalNet
FROM dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType,@TermsRuleType,@RuleId,@InvoiceKey);
GO
Please start testing this new procedure out to ensure that it is returning the same data as the previous procedure.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 12:36 pm
Wayne,
I dropped all the old procedures and ran the script you sent....
the numbers all look correct....
I did fix the procedure names -- so just so we are on the same page - here is the main "looping" sp that you asked me to change the name in...
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)
as
DECLARE @InvoiceDate as date
DECLARE @shiptodiscountduedate as date
DECLARE @shiptonetduedate as date
DECLARE @termsdiscountduedate as date
DECLARE @termsnetduedate as date
DECLARE @IsAssociated integer
DECLARE @InvoiceKey VARCHAR(1000)
DECLARE @ShipToKey varchar(20)
DECLARE @RebateUnits decimal(18,6)
DECLARE @TotalGross decimal(18,6)
DECLARE @TotalNet decimal(18,6)
DECLARE @TotalUnits decimal(18,6)
DECLARE @RulePeriodId nvarchar(30)
DECLARE @DateCalculationFieldIdentifier integer
select @DateCalculationFieldIdentifier = calcdatetype from RB_Rule where rb_ruleid = @RuleId
DECLARE curMain CURSOR STATIC LOCAL FOR
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate,ruleperiodid from invoicetotal a,invoice b,rb_invoicestoprocess c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
union
select a.invoicekey,InvoiceDate,shiptokey,shiptodiscountduedate,shiptonetduedate,termsdiscountduedate,termsnetduedate,ruleperiodid from invoicetotal a,invoice b,RB_InvoicesToProcess_Moved c
where a.invoicekey = b.invoicekey
and a.invoicekey = c.invoicekey
and rb_ruleid = @RuleId
open curMain
while 1=1
BEGIN
FETCH curMain INTO @InvoiceKey,@InvoiceDate,@ShipToKey,@shiptodiscountduedate,@shiptonetduedate,@termsdiscountduedate,@termsnetduedate,@RulePeriodId
IF @@fetch_status <> 0
break
select @InvoiceDate= case (@DateCalculationFieldIdentifier)
WHEN 0 THEN @InvoiceDate
WHEN 1 THEN @shiptodiscountduedate
WHEN 2 THEN @shiptonetduedate
WHEN 3 THEN @termsdiscountduedate
WHEN 4 THEN @termsnetduedate
else @InvoiceDate
END
--print @InvoiceDate
exec spCalculateRuleInvoiceUnits_Single @RuleUnitType,@TermsRuleType,@RuleId,@InvoiceKey,@RebateUnits OUTPUT,@TotalGross OUTPUT,@TotalNet OUTPUT,@TotalUnits OUTPUT
set @IsAssociated = 1
select @IsAssociated= case (@RulePeriodId)
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
else 1
END
if @RulePeriodId is null or @RulePeriodId = -1 or @RulePeriodId = -3
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting <= @InvoiceDate
and periodending >= @InvoiceDate
and reconciled = 0
if isnumeric(@RulePeriodId) = 0
begin
select @RulePeriodId = rb_ruleperiodid from rb_ruleperiod where rb_ruleid = @RuleId
and periodstarting >= @InvoiceDate
and reconciled = 0
order by rb_ruleperiodid asc
end
end
INSERT INTO [RB_RuleInvoice]
([RB_RuleId]
,[RB_RulePeriodId]
,[InvoiceKey]
,[IsAssociated]
,[ForcastData]
,[RebateUnits]
,[GrossAmount]
,[NetAmount]
,[Units]
,[DealerId]
,[RebateAmount]
,[RebateAmountPaid]
,[RebateAmountActual]
,[ReallocationAmount]
,[ReallocationAmountPaid]
,[ReallocationPaidFlag]
,[PostPeriodAdjustAmount]
,[TotalInvoiceNotPaid]
,[TotalReallocation]
,[PercentPayable])
values
(@RuleId,@RulePeriodId,@InvoiceKey,@IsAssociated,0,@RebateUnits,@TotalGross,@TotalNet,@TotalUnits,@ShipToKey,0,0,0,0,0,0,0,0,0,0)
end
Deallocate curMain
June 2, 2011 at 1:34 pm
... and, Part 2...
CREATE PROCEDURE spCalculateRuleInvoiceUnits @RuleUnitType numeric(9,2),@TermsRuleType numeric(9,2),@RuleId decimal(18,6)
AS
WITH cteMain AS
(
SELECT a.InvoiceKey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
FROM dbo.InvoiceTotal a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN dbo.rb_invoicestoprocess c
ON a.InvoiceKey = c.InvoiceKey
WHERE c.rb_ruleid = @RuleId
UNION
SELECT a.invoicekey,
b.InvoiceDate,
b.ShipToKey,
b.ShipToDiscountDueDate,
b.ShipToNetDueDate,
b.TermsDiscountDueDate,
b.TermsNetDueDate,
c.RulePeriodId
FROM dbo.InvoiceTotal a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN dbo.RB_InvoicesToProcess_Moved c
ON a.InvoiceKey = c.InvoiceKey
WHERE c.rb_ruleid = @RuleId
), cteMain2 AS
(
SELECT cteMain.*,
fn.*,
InvoiceDate2 = CASE (cte.CalcDateType)
WHEN 1 THEN cteMain.shiptodiscountduedate
WHEN 2 THEN cteMain.shiptonetduedate
WHEN 3 THEN cteMain.termsdiscountduedate
WHEN 4 THEN cteMain.termsnetduedate
ELSE cteMain.InvoiceDate
END,
IsAssociated = CASE cteMain.RulePeriodId
WHEN -1 THEN 0
WHEN -2 THEN 0
WHEN -3 THEN -3
ELSE 1
END
FROM cteMain
CROSS APPLY dbo.fnCalculateRuleInvoiceUnits_Single(@RuleUnitType, @TermsRuleType, @RuleId, cteMain.InvoiceKey) fn
CROSS APPLY (SELECT CalcDateType FROM dbo.RB_Rule WHERE RB_RuleId = @RuleId) cte
)
INSERT INTO dbo.[RB_RuleInvoice]
([RB_RuleId],
[RB_RulePeriodId],
[InvoiceKey],
[IsAssociated],
[ForcastData],
[RebateUnits],
[GrossAmount],
[NetAmount],
[Units],
[DealerId],
[RebateAmount],
[RebateAmountPaid],
[RebateAmountActual],
[ReallocationAmount],
[ReallocationAmountPaid],
[ReallocationPaidFlag],
[PostPeriodAdjustAmount],
[TotalInvoiceNotPaid],
[TotalReallocation],
[PercentPayable])
SELECT @RuleId,
CASE WHEN t.RulePeriodId IS NULL OR t.RulePeriodId IN (-1,-3) THEN ISNULL(caAll.rb_RulePeriodId, caNext.rb_RulePeriodId)
ELSE t.RulePeriodId
END,
t.InvoiceKey,
t.IsAssociated,
0,
t.RebateUnits,
t.TotalGross,
t.TotalNet,
t.TotalUnits,
t.ShipToKey,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM cteMain2 t
CROSS APPLY (SELECT rb_ruleperiodid
FROM rb_ruleperiod
WHERE rb_ruleid = @RuleId
AND periodstarting <= t.InvoiceDate2
AND periodending >= t.InvoiceDate2
AND reconciled = 0 ) caAll
CROSS APPLY (SELECT TOP (1)
rb_ruleperiodid
FROM rb_ruleperiod
WHERE rb_ruleid = @RuleId
AND periodstarting >= t.InvoiceDate2
AND reconciled = 0
ORDER BY rb_ruleperiodid ASC
) caNext;
GO
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 1:57 pm
Hi Wayne,
First off -- thank you som uch for your help!
I am just in the process of reloading my test data to get the same volume (100,000 lines) -- I will restart the testing in the morning with the bigger volumes.
I did do the test several times with 20,000 invoices and it looks like it is taking about 10 seconds....I am not sure if the progression is linear but 50 seconds to do 100K is certainly much better than 2 minutes...
I will let you know the results of my test and if we are done tweaking I will ned to spend some time examining the code and asking you what\why you did certain things.....
Thanks very much!
Tony
June 2, 2011 at 2:29 pm
tfeuz (6/2/2011)
Hi Wayne,First off -- thank you som uch for your help!
You're welcome.
I am just in the process of reloading my test data to get the same volume (100,000 lines) -- I will restart the testing in the morning with the bigger volumes.
I did do the test several times with 20,000 invoices and it looks like it is taking about 10 seconds....I am not sure if the progression is linear but 50 seconds to do 100K is certainly much better than 2 minutes...
There are a couple of opportunities for more performance enhancements. One specifically is the UNION operators... if there will not be any duplicate values between the two parts of the UNION, change it to a UNION ALL (with a union, both sides need to be sorted, and duplicates eliminated).
I will let you know the results of my test and if we are done tweaking I will ned to spend some time examining the code and asking you what\why you did certain things.....
Thanks very much!
Tony
For the next stage of performance tuning, how about running your test with retrieving the actual execution plan, save is as a .sqlplan file, and then attach it to your post. I did not do any extra indexes, so it should be interesting to see what we can do to improve things!
Edit: feel free to ask away!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2011 at 6:32 am
Wayne,
I examined the 2 procedures and managed to change 1 of the "UNIONS" to a "UNION ALL" -- the second cannot be changed as there may in fact be duplicates...
Here is the new procedure:
CREATE FUNCTION dbo.fnCalculateRuleInvoiceUnits_Single (
@RuleUnitType numeric(9,2),
@TermsRuleType numeric(9,2),
@RuleId decimal(18,6),
@InvoiceKey DECIMAL(18,0))
RETURNS TABLE
AS
RETURN
WITH cte AS
(
SELECT b.TermsDiscountAmount,
TermsPercent = b.termsdiscountamount / CASE WHEN @TermsRuleType = 1 THEN a.TotalInvoiceAmount ELSE a.InvoiceSubTotal END
FROM dbo.InvoiceTotal a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.InvoiceKey = @InvoiceKey
), cteTotals AS
(
SELECT TotalUnits = SUM(unitsshipped),
TotalGross = SUM(grossamount),
TotalNet = SUM(netamount)
FROM (SELECT UnitsShipped AS unitsshipped,
GrossAmount AS grossamount,
NetAmount AS netamount
FROM dbo.InvoiceLineItem a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.invoicekey = @InvoiceKey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM dbo.RB_RuleCategory c
WHERE c.rb_ruleid = @RuleId
AND c.SupplierId = B.supplierkey)
UNION ALL
SELECT 1 AS unitsshipped,
a.TaxAmount AS grossamount,
a.TaxAmount AS netamount
FROM dbo.InvoiceTotalTax a
JOIN dbo.Invoice b
ON a.InvoiceKey = b.InvoiceKey
WHERE a.invoicekey = @InvoiceKey
AND a.ProductRebateCategory IN (SELECT c.categorynumber
FROM dbo.RB_RuleCategory c
WHERE c.rb_ruleid = @RuleId
AND c.SupplierId = B.supplierkey)) AS tftest
), cteTotalCharge AS
(
SELECT TotalChargeUnits = SUM(1),
TotalChargeGross = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END),
TotalChargeNet = SUM(CASE WHEN [Type] = 'C' THEN ABS(a.Amount)
ELSE a.Amount
END)
FROM InvoiceTotalAdditionalCosts a
JOIN Invoice b
ON a.InvoiceKey = b.InvoiceKey
JOIN RB_RuleCategory c
ON c.SupplierId = b.SupplierKey
AND a.ProductRebateCategory = c.CategoryNumber
WHERE c.RB_RuleID = @RuleId
AND b.InvoiceKey = @InvoiceKey
)
SELECT RebateUnits = CONVERT(DECIMAL(18,6),
ISNULL(CASE WHEN cte.TermsDiscountAmount <> 0
AND @TermsRuleType <> 0
AND cte.TermsPercent <> 0
THEN caTotals.RebateUnits - caCharge.UnitsToDeductFromTerms
- ((caCharge.UnitsToDeductFromTerms) * cte.TermsPercent)
+ caCharge.UnitsToDeductFromTerms
ELSE caTotals.RebateUnits
END
, 0)),
TotalUnits = CONVERT(DECIMAL(18,6), caTotals.TotalUnits + ISNULL(caCharge.TotalChargeUnits,0)),
TotalGross = CONVERT(DECIMAL(18,6), caTotals.TotalGross + ISNULL(caCharge.TotalChargeGross,0)),
TotalNet = CONVERT(DECIMAL(18,6), caTotals.TotalNet + ISNULL(caCharge.TotalChargeNet,0))
FROM cte
CROSS APPLY (SELECT *,
RebateUnits = CASE (@RuleUnitType)
WHEN 1 THEN TotalGross
WHEN 2 THEN TotalNet
WHEN 3 THEN TotalUnits
END
FROM cteTotals) caTotals
CROSS APPLY (SELECT *, UnitsToDeductFromTerms =
CASE (@RuleUnitType)
WHEN 1 THEN TotalChargeGross
WHEN 2 THEN TotalChargeNet
WHEN 3 THEN TotalChargeUnits
END
FROM cteTotalCharge) caCharge;
GO
I ran a few tests and it looks like we are at 28 seconds to reculate 80,000 invoice records which have 1.2 million line item records attached to them.
I have attached the .sqlplan file
Tony
Viewing 15 posts - 16 through 30 (of 117 total)
You must be logged in to reply to this topic. Login to reply