Help! Problem with empty Comm_Cost & 3% calc.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help! Problem with empty Comm_Cost & 3% calc.

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2003-07-08 : 10:52:19

Good morning Ladies & Gentlemen

I have a ratther weird problem. The following stored proceedure
deletes data from the COMM_REG & QS_REG tables in order to update with the next month's sales data. But for some reason there are lots of entries in which there were no comm-cost update. There are updates for some but not for others. Also where is the calculation for 3% commission because it needs to be removed.

The person who originally wrote the sp is no longer with us and I am trying to find the source of the comm-cost data so that I can correct ant blank entries and also the 3% commission calculation so that I can correct it. I am new to tsql lanuage and stored proceedures so any help would be greatly appreciated. Could you brilliant people help please... Here is the stored proceedure
----------------------------------------------------------------------
Alter Procedure sp_comm_reg_query
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
As

DELETE FROM COMM_REG;
DELETE FROM QS_REG;

--1

--ADD NEW RECORDS TO QT_INV_CROSS

INSERT INTO QT_INV_CROSS

SELECT [HISTRY INV-HEADER].INVOICE#,
[HISTRY INV-HEADER].[INVOICE-DATE] AS [INV-DATE],
[HISTRY INV-HEADER].[ORIG-INV#],
MIN(SUBSTRING([HISTRY SPEC-DESC].[DESC], 3, 10))
AS QUOTE
FROM [HISTRY INV-LINE] INNER JOIN
[HISTRY SPEC-DESC] ON
[HISTRY INV-LINE].INVOICE# + [HISTRY INV-LINE].SEQ# = [HISTRY SPEC-DESC].[INVOICE-SEQ#]
INNER JOIN
[HISTRY INV-HEADER] ON
[HISTRY INV-LINE].INVOICE# = [HISTRY INV-HEADER].INVOICE# LEFT
OUTER JOIN
QT_INV_CROSS ON
[HISTRY INV-HEADER].INVOICE# = QT_INV_CROSS.INVOICE#
WHERE ([HISTRY SPEC-DESC].[DESC] LIKE 'qt%') AND
(NOT ([HISTRY SPEC-DESC].[DESC] LIKE 'qty%')) AND
(QT_INV_CROSS.QUOTE IS NULL)
GROUP BY [HISTRY INV-HEADER].[ORIG-INV#],
[HISTRY INV-HEADER].INVOICE#,
[HISTRY INV-HEADER].[INVOICE-DATE];

--2

--ADD NEW RECORDS TO COMM_REG

--OMIT
--759926 = CREDIT CARD SERVICE CHARGE
--733692 = SERVICE PART-SEE WORKORDER
--140128 = TRAVEL TIME
--222222 = SHIPPING CHARGES
--666631 = RMA RESTOCK FEE
--158911 = SHIPPING CHARGE

INSERT INTO COMM_REG

SELECT [HISTRY INV-LINE].INVOICE#, [HISTRY INV-LINE].DP#,
[HISTRY INV-LINE].[CUST-DP#], [HISTRY INV-LINE].[QTY-ORD],
[HISTRY INV-LINE].[QTY-SHP],
[HISTRY INV-LINE].[AVER-COST],
[HISTRY INV-LINE].[LANDED-COST], [HISTRY INV-LINE].COST,
[HISTRY INV-LINE].CONTRACT#,
[HISTRY INV-LINE].[LAST-RCOST],
[HISTRY INV-LINE].[DLR-PRICE], [HISTRY INV-LINE].[RET-PRC],
[HISTRY INV-LINE].[COMM%(1)],
[HISTRY INV-LINE].[COMMD(1)], [HISTRY INV-LINE].FREE,
[HISTRY INV-LINE].[DISC%], [HISTRY INV-LINE].DISCOUNTD,
[HISTRY INV-LINE].TICKETS, [HISTRY INV-LINE].FLAGS,
[HISTRY INV-LINE].[FLAGS3(1)],
[HISTRY INV-LINE].[SETUP-SEQ(1)], [HISTRY INV-LINE].GL#,
[HISTRY INV-LINE].SEQ#, NULL AS QUOTE#,
[IDEAS INVENTORY].BRAND,
[IDEAS INVENTORY].[VNDR-ITEM#] AS [VEND ID],
[IDEAS INVENTORY].[DESC] AS DESCRIPTION,
[SALES GROUPS].TERR#,
[SALES GROUPS].NAME AS [TERR NAME],
[SALES GROUPS].[GROUP] AS [SALES GROUP],
[HISTRY INV-HEADER].CUST#,
[HISTRY INV-HEADER].[CUST-PO#], NULL
AS COMM_COST, NULL AS COMM_COST_SOURCE,
NULL AS PROFIT, NULL AS GP_PERCENT,
NULL AS CUSTOMER_NAME, NULL AS [INV-DATE],
NULL AS [TN-PO#], NULL AS [RECV-DATE], NULL AS [RECV-COST],
[HISTRY INV-HEADER].[ORIG-INV#], NULL AS COMM_COST_ADJ,
NULL AS COMM_COST_ADJ_NOTE

FROM [HISTRY INV-HEADER] INNER JOIN
[HISTRY INV-LINE] ON
[HISTRY INV-HEADER].INVOICE# = [HISTRY INV-LINE].INVOICE# INNER
JOIN
[IDEAS INVENTORY] ON
[HISTRY INV-LINE].DP# = [IDEAS INVENTORY].DP# INNER JOIN
[SALES GROUPS] ON
[HISTRY INV-HEADER].TERR# = [SALES GROUPS].TERR# LEFT OUTER
JOIN
comm_reg ON
[HISTRY INV-HEADER].INVOICE# = comm_reg.INVOICE# AND
[HISTRY INV-LINE].INVOICE# = comm_reg.INVOICE# AND
[HISTRY INV-LINE].SEQ# = comm_reg.SEQ#
WHERE ({ fn YEAR([INVOICE-DATE]) } = { fn YEAR({ fn NOW() } - 30)
}) AND ({ fn MONTH([INVOICE-DATE])
} = { fn MONTH({ fn NOW() } - 30) }) AND
([HISTRY INV-LINE].DP# < '888888') AND
(comm_reg.INVOICE# IS NULL) AND
(NOT ([HISTRY INV-LINE].DP# = '759926')) AND
(NOT ([HISTRY INV-LINE].DP# = '733692')) AND
(NOT ([HISTRY INV-LINE].DP# = '140128')) AND
(NOT ([HISTRY INV-LINE].DP# = '222222')) AND
(NOT ([HISTRY INV-LINE].DP# = '666631')) AND
(NOT ([HISTRY INV-LINE].DP# = '158911')) AND
[HISTRY INV-LINE].[QTY-SHP] <> 0;

--UPDATE QUOTE#

UPDATE [COMM_REG]

SET [QUOTE#] = [QT_INV_CROSS].[QUOTE]

FROM [QT_INV_CROSS]

WHERE [COMM_REG].[INVOICE#] = [QT_INV_CROSS].[INVOICE#];

--3

--INSERT NEW RECORDS INTO QS_REG

INSERT INTO QS_REG

select
QuoteSys.dbo.tblItems.ID,
QuoteSys.dbo.tblItems.Quote#,
QuoteSys.dbo.tblItems.Qty,
QuoteSys.dbo.tblItems.List,
QuoteSys.dbo.tblItems.Sell,
QuoteSys.dbo.tblItems.DP,
QuoteSys.dbo.tblItems.Description,
QuoteSys.dbo.tblItems.[Raw Cost],
QuoteSys.dbo.tblItems.Landed,
QuoteSys.dbo.tblItems.[Final Customer Price],
QuoteSys.dbo.tblItems.[Line Margin],
QuoteSys.dbo.tblItems.[Vend ID],
QuoteSys.dbo.tblItems.Source,
QuoteSys.dbo.tblItems.Brand,
QuoteSys.dbo.tblItems.Found,
QuoteSys.dbo.tblItems.IngramDate,
QuoteSys.dbo.tblItems.SourcePrice,
QuoteSys.dbo.tblItems.Cat,
QuoteSys.dbo.tblItems.MarkUp,
QuoteSys.dbo.tblItems.NewQuote#,
QuoteSys.dbo.tblItems.TRNT_Desc1,
QuoteSys.dbo.tblItems.TRNT_Desc2,
QuoteSys.dbo.tblItems.TRNT_Cat,
QuoteSys.dbo.tblItems.TRNT_Landed,
QuoteSys.dbo.tblItems.TRNT_List,
QuoteSys.dbo.tblItems.TRNT_Brand,
QuoteSys.dbo.tblItems.TRNT_Found,
QuoteSys.dbo.tblItems.ING_Brand,
QuoteSys.dbo.tblItems.ING_CatNo,
QuoteSys.dbo.tblItems.ING_Desc1,
QuoteSys.dbo.tblItems.ING_Desc2,
QuoteSys.dbo.tblItems.ING_Cat1,
QuoteSys.dbo.tblItems.ING_Cat2,
QuoteSys.dbo.tblItems.ING_TNLanded,
QuoteSys.dbo.tblItems.ING_Acquisition,
QuoteSys.dbo.tblItems.ING_List,
QuoteSys.dbo.tblItems.ING_AuthtoBuy,
QuoteSys.dbo.tblItems.ING_InstantRebate,
QuoteSys.dbo.tblItems.ING_DownloadDate,
QuoteSys.dbo.tblItems.ING_RawCost,
QuoteSys.dbo.tblItems.ING_Found,
QuoteSys.dbo.tblItems.SPEC_ProgID,
QuoteSys.dbo.tblItems.SPEC_Customer,
QuoteSys.dbo.tblItems.SPEC_Vendor,
QuoteSys.dbo.tblItems.SPEC_EffectDate,
QuoteSys.dbo.tblItems.SPEC_EndDate,
QuoteSys.dbo.tblItems.SPEC_Fixed,
QuoteSys.dbo.tblItems.SPEC_Rebate,
QuoteSys.dbo.tblItems.SPEC_DiscPer,
QuoteSys.dbo.tblItems.SPEC_List,
QuoteSys.dbo.tblItems.SPEC_Discount,
QuoteSys.dbo.tblItems.SPEC_Found,
QuoteSys.dbo.tblItems.BSPEC_ProgID,
QuoteSys.dbo.tblItems.BSPEC_DisPer,
QuoteSys.dbo.tblItems.BSPEC_Found,
QuoteSys.dbo.tblItems.ProdFamily,
QuoteSys.dbo.tblItems.Dell_Brand,
QuoteSys.dbo.tblItems.Dell_Description,
QuoteSys.dbo.tblItems.Dell_Raw_Cost,
QuoteSys.dbo.tblItems.Dell_Shipping,
QuoteSys.dbo.tblItems.Dell_Found,
QuoteSys.dbo.tblItems.Purch_Override_Live,
QuoteSys.dbo.tblItems.Purch_Override_Date,
QuoteSys.dbo.tblItems.Purch_Override_User,
QuoteSys.dbo.tblItems.Purch_Override_Private,
QuoteSys.dbo.tblItems.Purch_Override_Public,
QuoteSys.dbo.tblItems.Commision_Cost,
QuoteSys.dbo.tblItems.Ing_Rebate_Exp,
QuoteSys.dbo.tblItems.Ing_Rebate,
QuoteSys.dbo.tblItems.Purch_Override_Reference,
QuoteSys.dbo.tblItems.Purch_Override_PO,
QuoteSys.dbo.tblItems.Purch_Override,
QuoteSys.dbo.tblItems.Rebate_Amount,
QuoteSys.dbo.tblItems.Program_Active,
QuoteSys.dbo.tblItems.hp_list ,
QuoteSys.dbo.tblItems.ibm_list,
QuoteSys.dbo.tblItems.ibm_bi,
NULL AS TERR,
NULL AS COMM_COST_SOURCE

FROM QuoteSys.dbo.tblItems INNER JOIN
dbo.vw_comm_reg_quotes ON CONVERT(varchar,
QuoteSys.dbo.tblItems.Quote#)
= dbo.vw_comm_reg_quotes.QUOTE# LEFT OUTER JOIN
dbo.qs_reg ON
QuoteSys.dbo.tblItems.ID = dbo.qs_reg.ID
WHERE (dbo.qs_reg.ID IS NULL);

--CHANGE INGRAM MARKUP ON IBM TO 2.75%

UPDATE [QS_REG]

SET
[ING_Acquisition] = [ING_RawCost]* 1.0275

FROM qs_reg
WHERE (Brand LIKE 'IBM%') AND
(ING_RawCost > 0) AND (ING_Acquisition > 0) OR
(Brand LIKE 'IBM%') AND (TERR = '1011') AND
(ING_RawCost > 0) AND (ING_Acquisition > 0);

--UPDATE BLANK VEND ID

UPDATE [QS_REG]

SET
[VEND ID] = [IDEAS INVENTORY].[VNDR-ITEM#]

FROM [IDEAS INVENTORY]

WHERE CONVERT(VARCHAR,[QS_REG].[DP]) = CONVERT(VARCHAR,[IDEAS INVENTORY].[DP#]) AND
[VEND ID] IS NULL;

--UPDATE BLANK DP

UPDATE [QS_REG]

SET
[DP] = CONVERT(VARCHAR,[IDEAS INVENTORY].[DP#])

FROM [IDEAS INVENTORY]

WHERE [VEND ID] = [IDEAS INVENTORY].[VNDR-ITEM#]AND
[DP] IS NULL;

--DELETE NON-COMMISSION AND SERVICES

DELETE FROM [QS_REG]

WHERE ([Final Customer Price] IS NULL) OR ([DP]='888888')
OR ([DP]='999999')
OR ([DP]='999');

DELETE FROM [QS_REG]

WHERE [BRAND] = 'SUPPORT' OR
[BRAND] = 'TRNT MAINT' OR
[BRAND] = 'TRNT SVC FEE' OR
[BRAND] = 'TRNT FEE' OR
[BRAND] = 'TRNT CONFIG';

--UPDATE TERR

UPDATE [QS_REG]

SET
[TERR] = QuoteSys.dbo.tblHeader.Terr#

FROM QuoteSys.dbo.tblHeader

WHERE [QS_REG].[QUOTE#] = QuoteSys.dbo.tblHeader.[QUOTE#] AND
[QS_REG].[TERR] IS NULL

--UPDATE PROGRAM_ACTIVE

UPDATE [QS_REG]

SET
[BSPEC_ProgID] = NULL

WHERE

[BSPEC_ProgID] = '1016';

UPDATE [QS_REG]

SET
Program_Active = NULL;

UPDATE [QS_REG]

SET
Program_Active = SPEC_ProgID

WHERE SPEC_ProgID > 0;

UPDATE [QS_REG]

SET
Program_Active = BSPEC_ProgID

WHERE BSPEC_ProgID > 0 AND Program_Active IS NULL;

--UPDATE COMM_COST, MERCK, NO PROGRAM, HAS INGRAM ACQUISITION

UPDATE [QS_REG]

SET
[Commision_Cost] = [ING_Acquisition] * 1.01,

[COMM_COST_SOURCE] = 'INGRAM ACQUISITION + 1% ' +
convert(varchar,DATEPART(m,[QS_REG].ING_DownloadDate)) + '/' +
convert(varchar,DATEPART(d,[QS_REG].ING_DownloadDate)) + '/' +
convert(varchar,DATEPART(yy,[QS_REG].ING_DownloadDate))

WHERE (TERR = '1010') AND (SPEC_ProgID IS NULL) AND
(BSPEC_ProgID IS NULL) AND
(ING_Acquisition > 0) OR
(TERR = '1011') AND (SPEC_ProgID IS NULL) AND
(BSPEC_ProgID IS NULL) AND
(ING_Acquisition > 0)

--UPDATE COMM_COST, NOT MERCK, NO PROGRAM, HAS INGRAM ACQUISITION

UPDATE [QS_REG]

SET
[Commision_Cost] = [ING_Acquisition] * 1.03,

[COMM_COST_SOURCE] = 'INGRAM ACQUISITION + 3% ' +
convert(varchar,DATEPART(m,[QS_REG].ING_DownloadDate)) + '/' +
convert(varchar,DATEPART(d,[QS_REG].ING_DownloadDate)) + '/' +
convert(varchar,DATEPART(yy,[QS_REG].ING_DownloadDate))

WHERE (NOT (TERR = '1010')) AND (SPEC_ProgID IS NULL) AND
(BSPEC_ProgID IS NULL) AND
(ING_Acquisition > 0) AND (NOT (TERR = '1011'));

--UPDATE COMM_COST, MERCK, PURCHASE OVERRIDE

UPDATE [QS_REG]

SET
[Commision_Cost] = [PURCH_OVERRIDE] * 1.01,

[COMM_COST_SOURCE] = 'PURCHASING OVERRIDE + 1% ('+ [QS_REG].[PURCH_OVERRIDE_PUBLIC] + ')'

WHERE (TERR = '1010') AND

([PURCH_OVERRIDE] > 0) OR (TERR = '1011') AND

([PURCH_OVERRIDE] > 0)

--UPDATE COMM_COST, NOT MERCK, PURCHASE OVERRIDE

UPDATE [QS_REG]

SET
[Commision_Cost] = [PURCH_OVERRIDE] * 1.03,

[COMM_COST_SOURCE] = 'PURCHASING OVERRIDE + 3% ('+ [QS_REG].[PURCH_OVERRIDE_PUBLIC] + ')'

WHERE (NOT (TERR = '1010')) AND

([PURCH_OVERRIDE] > 0) AND

(NOT (TERR = '1011'));

--4

--PROGRAM 1003 WITH SPEC_FIXED (MERCK/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '1003') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '1003') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.01,
[COMM_COST_SOURCE] = 'PROGRAM 1003 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '1003') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 1003 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 1003) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 1003 WITH FAMILY DISCOUNT (MERCK/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] * [QS_REG].[BSPEC_DisPer]
FROM qs_reg
WHERE (Program_Active = '1003') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '1003') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.01,
[COMM_COST_SOURCE] = 'PROGRAM 1003 (FAMILY DISCOUNT)'
FROM qs_reg
WHERE (Program_Active = '1003') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 1003 (FAMILY DISCOUNT) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (Program_Active = '1003') AND
(ING_RawCost IS NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

--PROGRAM 21042 WITH SPEC_FIXED (COLGATE/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21042') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21042') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21042 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21042') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21042 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21042) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21042 WITH FAMILY DISCOUNT (COLGATE/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] * [QS_REG].[BSPEC_DisPer]
FROM qs_reg
WHERE (Program_Active = '21042') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21042') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21042 (FAMILY DISCOUNT)'
FROM qs_reg
WHERE (Program_Active = '21042') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21042 (FAMILY DISCOUNT) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (Program_Active = '21042') AND
(ING_RawCost IS NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

--PROGRAM 21046 WITH SPEC_FIXED (LONZA/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21046') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21046') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21046 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21046') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21046 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21046) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21047 WITH SPEC_FIXED (COLGATE/IBM)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21047') AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21047') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = ([ING_Acquisition]-[Rebate_Amount])*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21047 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21047') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21047 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21047) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21048 WITH SPEC_FIXED (MERCK/IBM)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21048') AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21048') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = ([ING_Acquisition]-[Rebate_Amount])*1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21048 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21048') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21048 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21048) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21050 WITH SPEC_FIXED (MERCK/HP)

UPDATE [QS_REG]
SET
[Rebate_Amount] = ([HP_LIST] * .8)-[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21050') AND
([HP_LIST] IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21050') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = ([ING_Acquisition]-[Rebate_Amount])*1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21050 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21050') AND
(ING_Acquisition IS NOT NULL) AND
( [HP_LIST] IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21050 (FIXED PRICE) HP LIST N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21050) AND (SPEC_Fixed > 0) AND
([HP_LIST] IS NULL);

--PROGRAM 21050 WITH % OF LIST DISCOUNT (MERCK/HP)

UPDATE [QS_REG]
SET
[REBATE_AMOUNT] = SPEC_LIST * SPEC_DISCPER
FROM qs_reg
WHERE (Program_Active = '21050') AND
SPEC_Fixed IS NULL AND
SPEC_LIST > 0 AND
SPEC_DISCPER > 0;

UPDATE [QS_REG]
SET
[REBATE_AMOUNT] = HP_LIST * SPEC_DISCPER
FROM qs_reg
WHERE (Program_Active = '21050') AND
SPEC_Fixed IS NULL AND
SPEC_LIST IS NULL AND
SPEC_DISCPER > 0 AND
HP_LIST >0;

UPDATE [QS_REG]
SET
[COMMISION_COST] = (ING_ACQUISITION-REBATE_AMOUNT) * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21050 (% OF LIST)'
FROM qs_reg
WHERE (Program_Active = '21050') AND
SPEC_Fixed IS NULL AND
REBATE_AMOUNT >0;

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21050 (% OF LIST ) HP LIST N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21050) AND SPEC_Fixed IS NULL AND (SPEC_LIST IS NULL) AND
([HP_LIST] IS NULL) AND SPEC_DISCPER > 0;

--PROGRAM 21050 WITH FAMILY % OF LIST DISCOUNT (MERCK/HP)

UPDATE [QS_REG]
SET
[REBATE_AMOUNT] = HP_LIST * BSPEC_DISPER
FROM qs_reg
WHERE (Program_Active = '21050') AND
SPEC_Fixed IS NULL AND
SPEC_DISCPER IS NULL AND HP_LIST > 0 AND BSPEC_DISPER > 0;

UPDATE [QS_REG]
SET
[COMMISION_COST] = (ING_ACQUISITION-REBATE_AMOUNT) * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21050 (FAMILY % OF LIST)'
FROM qs_reg
WHERE (Program_Active = '21050') AND
SPEC_Fixed IS NULL AND
REBATE_AMOUNT >0 AND
SPEC_DISCPER IS NULL AND HP_LIST > 0 AND BSPEC_DISPER > 0;

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21050 (FAMILY % OF LIST ) HP LIST N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21050) AND SPEC_Fixed IS NULL AND
([HP_LIST] IS NULL) AND SPEC_DISCPER IS NULL AND BSPEC_DISPER > 0;

--PROGRAM 21051 WITH SPEC_FIXED (THOMSON/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21051') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21051') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21051 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21051') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21051 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21051) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21051 WITH FAMILY DISCOUNT (THOMSON/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] * [QS_REG].[BSPEC_DisPer]
FROM qs_reg
WHERE (Program_Active = '21051') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21051') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21051 (FAMILY DISCOUNT)'
FROM qs_reg
WHERE (Program_Active = '21051') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21051 (FAMILY DISCOUNT) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (Program_Active = '21051') AND
(ING_RawCost IS NULL) AND
(SPEC_Fixed IS NULL) AND
(BSPEC_DisPer > 0);

--PROGRAM 21059 WITH FIXED PRICE (MERCK/NEC)

UPDATE [QS_REG]
SET
[Commision_Cost] = 2256.41 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD1810-1';

UPDATE [QS_REG]
SET
[Commision_Cost] = 226.67 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE700';

UPDATE [QS_REG]
SET
[Commision_Cost] = 1568.21 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD1810X';

UPDATE [QS_REG]
SET
[Commision_Cost] = 3487.18 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD2010-1';

UPDATE [QS_REG]
SET
[Commision_Cost] = 2769.23 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD2010X';

UPDATE [QS_REG]
SET
[Commision_Cost] = 3517.95 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD2010X-P';

UPDATE [QS_REG]
SET
[Commision_Cost] = 2769.23 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD2010X-BK';

UPDATE [QS_REG]
SET
[Commision_Cost] = 202.05 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE700+';

UPDATE [QS_REG]
SET
[Commision_Cost] = 330.26 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE950+BK';

UPDATE [QS_REG]
SET
[Commision_Cost] = 202.05 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE700+BK';

UPDATE [QS_REG]
SET
[Commision_Cost] = 957.95 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD1830';

UPDATE [QS_REG]
SET
[Commision_Cost] = 957.95 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'LCD1830-BK';

UPDATE [QS_REG]
SET
[Commision_Cost] = 130.26 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21059 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'AS50';

--PROGRAM 21064 WITH FIXED PRICE (MERCK/NEC)

UPDATE [QS_REG]
SET
[Commision_Cost] = 264.62 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21064 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'DP710S'

UPDATE [QS_REG]
SET
[Commision_Cost] = 769.23 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21064 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'DPLUS200';

UPDATE [QS_REG]
SET
[Commision_Cost] = 202.05 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21064 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE700+';

UPDATE [QS_REG]
SET
[Commision_Cost] = 223.59 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21064 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE750+';

UPDATE [QS_REG]
SET
[Commision_Cost] = 644.10 * 1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21064 (FIXED PRICE)'
WHERE (Program_Active = '21064') AND [VEND ID] = 'FE1250+';

--PROGRAM 21069 WITH SPEC_FIXED (COVANCE/COMPAQ)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21069') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21069') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = [ING_Acquisition]-[Rebate_Amount]*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21069 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21069') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21069 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21069) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21071 WITH SPEC_FIXED (LONZA/IBM)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21071') AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21012') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = ([ING_Acquisition]-[Rebate_Amount])*1.03,
[COMM_COST_SOURCE] = 'PROGRAM 21071 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21071') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21071 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21071) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--PROGRAM 21072 WITH SPEC_FIXED (MERCK/IBM)

UPDATE [QS_REG]
SET
[Rebate_Amount] = [QS_REG].[ING_RawCost] - [QS_REG].[SPEC_Fixed]
FROM qs_reg
WHERE (Program_Active = '21072') AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[Rebate_Amount] = 0
FROM qs_reg
WHERE (Program_Active = '21072') AND
([Rebate_Amount]<.01);

UPDATE [QS_REG]
SET
[Commision_Cost] = ([ING_Acquisition]-[Rebate_Amount])*1.01,
[COMM_COST_SOURCE] = 'PROGRAM 21072 (FIXED PRICE)'
FROM qs_reg
WHERE (Program_Active = '21072') AND
(ING_Acquisition IS NOT NULL) AND
(ING_RawCost IS NOT NULL) AND (SPEC_Fixed IS NOT NULL);

UPDATE [QS_REG]
SET
[COMM_COST_SOURCE] = 'PROGRAM 21072 (FIXED PRICE) US1 N/A ',
[Commision_Cost] = NULL
FROM qs_reg
WHERE (SPEC_ProgID = 21072) AND (SPEC_Fixed > 0) AND
(ING_RawCost IS NULL);

--5 UPDATE COMM_COST AND COMM_COST_SOURCE FROM QS_REG

UPDATE [COMM_REG]

SET
[COMM_COST] = [qs_reg].[COMMISION_COST],
[COMM_COST_SOURCE] = [qs_reg].[COMM_COST_SOURCE]

FROM comm_reg INNER JOIN
qs_reg ON SUBSTRING(CONVERT(VARCHAR,
comm_reg.QUOTE#), 1, 5)
+ SUBSTRING(CONVERT(VARCHAR, comm_reg.DP#), 1, 6)
= SUBSTRING(CONVERT(VARCHAR, qs_reg.Quote#), 1,
5) + SUBSTRING(CONVERT(VARCHAR, qs_reg.DP), 1,
6);

--UPDATE TN-PO# PRICING FOR DROPSHIPMENTS

UPDATE COMM_REG

SET
[TN-PO#] = vw_po_inv_pricing_dropship.[PO#],
[RECV-DATE] = vw_po_inv_pricing_dropship.[RECV-DATE],
[RECV-COST] = vw_po_inv_pricing_dropship.[RECV-COST]

FROM comm_reg INNER JOIN
vw_po_inv_pricing_dropship ON
comm_reg.INVOICE# = vw_po_inv_pricing_dropship.INVOICE# AND
comm_reg.DP# = vw_po_inv_pricing_dropship.DP#;

-- MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR DROPSHIPMENTS

UPDATE COMM_REG

SET

COMM_COST = CONVERT(MONEY,[RECV-COST]*1.01),
COMM_COST_SOURCE = 'TN-PO# '+[TN-PO#]+'PLUS 1%'

FROM comm_reg
WHERE (NOT (COMM_COST_SOURCE LIKE 'PROGRAM%')) AND (NOT ([TN-PO#] = '')) AND ([SALES GROUP] = 'MERCK') OR COMM_COST_SOURCE IS NULL AND (NOT ([TN-PO#] = '')) AND ([SALES GROUP] = 'MERCK');

-- NOT MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR DROPSHIP
UPDATE COMM_REG

SET
COMM_COST = CONVERT(MONEY,[RECV-COST]*1.03),
COMM_COST_SOURCE = 'TN-PO# '+[TN-PO#]+'PLUS 3%'

FROM comm_reg
WHERE (NOT (COMM_COST_SOURCE LIKE 'PROGRAM%')) AND (NOT ([TN-PO#] = '')) AND ([SALES GROUP] <> 'MERCK') OR COMM_COST_SOURCE IS NULL AND (NOT ([TN-PO#] = '')) AND ([SALES GROUP] <> 'MERCK');

-- UPDATE TN-PO# PRICING FOR NON-DROPSHIPMENTS (PASS 1)

UPDATE COMM_REG

SET

[RECV-COST] = vw_po_inv_pricing_not_dropship.[RECV-COST],
COMM_COST = NULL,
COMM_COST_SOURCE = NULL

FROM comm_reg INNER JOIN
vw_po_inv_pricing_not_dropship ON
comm_reg.DP# = vw_po_inv_pricing_not_dropship.DP#
WHERE (comm_reg.[TN-PO#] IS NULL) AND
(NOT (comm_reg.COMM_COST_SOURCE LIKE 'PROGRAM%')) OR
(comm_reg.[TN-PO#] IS NULL) AND
(comm_reg.COMM_COST_SOURCE IS NULL)

-- MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR NON-DROPSHIPMENTS (PASS 1)

UPDATE COMM_REG

SET

COMM_COST = CONVERT(MONEY,[RECV-COST]*1.01),
COMM_COST_SOURCE = 'TN-PO THIS MONTH +1%'

FROM comm_reg
WHERE ([RECV-COST] > 0) AND COMM_COST_SOURCE IS NULL AND ([SALES GROUP] = 'MERCK') OR [RECV-COST] > 0 AND COMM_COST_SOURCE = '' AND ([SALES GROUP] = 'MERCK');

-- NOT MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR NON-DROPSHIPMENTS (PASS 1)

UPDATE COMM_REG

SET

COMM_COST = CONVERT(MONEY,[RECV-COST]*1.03),
COMM_COST_SOURCE = 'TN-PO THIS MONTH +3%'

FROM comm_reg
WHERE [RECV-COST] > 0 AND COMM_COST_SOURCE IS NULL AND ([SALES GROUP] <> 'MERCK') OR [RECV-COST] > 0 AND COMM_COST_SOURCE = '' AND ([SALES GROUP] <> 'MERCK');

--UPDATE TN-PO# PRICING FOR NON-DROPSHIPMENTS (PASS 2)

UPDATE COMM_REG

SET

[RECV-COST] = vw_po_inv_pricing_last_3mo.[RECV-COST],
COMM_COST = NULL,
COMM_COST_SOURCE = NULL

FROM comm_reg INNER JOIN
vw_po_inv_pricing_last_3mo ON
comm_reg.DP# = vw_po_inv_pricing_last_3mo.DP#

WHERE (comm_reg.COMM_COST_SOURCE IS NULL)

-- MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR NON-DROPSHIPMENTS (PASS 2)

UPDATE COMM_REG

SET

COMM_COST = CONVERT(MONEY,[RECV-COST]*1.01),
COMM_COST_SOURCE = 'TN-PO OTHER MONTH +1%'

FROM comm_reg
WHERE [RECV-COST] > 0 AND [COMM_COST_SOURCE] IS NULL AND ([SALES GROUP] = 'MERCK');

-- NOT MERCK - UPDATE COMM_COST AND COMM_COST_SOURCE FOR NON-DROPSHIPMENTS (PASS 2)

UPDATE COMM_REG

SET

COMM_COST = CONVERT(MONEY,[RECV-COST]*1.03),
COMM_COST_SOURCE = 'TN-PO THIS MONTH +3%'

FROM comm_reg
WHERE [RECV-COST] > 0 AND [COMM_COST_SOURCE] IS NULL AND ([SALES GROUP] <> 'MERCK');

-- DELETE UNASSIGNED SALES GROUP

DELETE FROM COMM_REG WHERE [SALES GROUP] = 'UNASSIGNED';

--CCEPFEES

UPDATE [COMM_REG]

SET [COMM_COST] = 0,
[COMM_COST_SOURCE] = 'CEP FEE 100% PROFIT'

WHERE [DP#] = '631457';

--SERVICES

UPDATE [COMM_REG]

SET [COMM_COST] = ([DLR-PRICE]/100) * .8,
[COMM_COST_SOURCE] = 'SERVICE 20% PROFIT'

WHERE [BRAND] = 'SUPPORT' OR
[BRAND] = 'TRNT MAINT' OR
[BRAND] = 'TRNT SVC FEE' OR
[BRAND] = 'TRNT FEE' OR
[BRAND] = 'TRNT CONFIG' ;

--SPECTRUM

UPDATE [COMM_REG]

SET [COMM_COST] = [LANDED-COST]/100,
[COMM_COST_SOURCE] = 'LANDED - SPECTRUM'

WHERE [BRAND] = 'SPECTRUM';

--COMPAQ-ED

DELETE FROM [COMM_REG]
WHERE [BRAND] = 'COMPAQ-ED'

DELETE FROM [COMM_REG]
WHERE [BRAND] = 'COMPAQ ED'

--UPDATE TRAINING

UPDATE [COMM_REG]

set [COMM_COST_SOURCE] = 'LANDED - TRAINING',
[COMM_COST] = [LANDED-COST]/100

WHERE [BRAND] = 'TRAINING';

--MOUSE PADS

UPDATE [COMM_REG]

SET
[COMM_COST] = [LANDED-COST]/100,
[COMM_COST_SOURCE] = 'LANDED COST - MOUSE PADS'

WHERE [DP#] = '738710' OR [DP#] = '100297';

--PROGRAM 21048 TECH DATA SPECIAL 26484AU ONLY (MERCK/IBM)

UPDATE [COMM_REG]
SET
[Comm_Cost] = 2348.25,
[COMM_COST_SOURCE] = 'PROGRAM 21048 (TECH DATA SPECIAL)'

WHERE [DP#] = '761434' AND ([SALES GROUP] = 'MERCK');

--UPDATE PROFIT

UPDATE [COMM_REG]

SET
[PROFIT] = ([DLR-PRICE]/100)-[COMM_COST]

WHERE [COMM_COST_SOURCE] IS NOT NULL;

--UPDATE GP_PERCENT (DLR-PRICE <> 0)

UPDATE [COMM_REG]

SET
[GP_PERCENT] = [PROFIT]/([DLR-PRICE]/100)

WHERE [COMM_COST_SOURCE] IS NOT NULL
AND [DLR-PRICE]<> 0;

--UPDATE GP_PERCENT (DLR-PRICE = 0)

UPDATE [COMM_REG]

SET
[GP_PERCENT] = '-1'

WHERE [COMM_COST_SOURCE] IS NOT NULL
AND [DLR-PRICE] = 0 AND
[COMM_COST] > 0;

--UPDATE GP_PERCENT (PROFIT = 0)

UPDATE [COMM_REG]

SET
[GP_PERCENT] = '0'

WHERE [PROFIT] = 0;

--UPDATE CUSTOMER NAME AND INVOICE DATE

UPDATE [COMM_REG]
SET
[CUSTOMER_NAME] = [IDEAS CUSTOMERS].[NAME]
FROM comm_reg INNER JOIN
[IDEAS CUSTOMERS] ON
comm_reg.CUST# = [IDEAS CUSTOMERS].CUST#;

UPDATE [COMM_REG]
SET
[INV-DATE] = [HISTRY INV-HEADER].[INVOICE-DATE]
FROM comm_reg INNER JOIN
[HISTRY INV-HEADER] ON
comm_reg.INVOICE# = [HISTRY INV-HEADER].INVOICE#;

--CREATE SEPARATE EXXON REPORT

UPDATE COMM_REG

SET
TERR# = '7000',
[TERR NAME] = 'EXXON SALES',
[SALES GROUP] = 'EXXON SALES'

WHERE (CUSTOMER_NAME LIKE 'EXXON%')
----------------------------------------------------------------------
Thanks so much in advance.
pharoah35

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-08 : 10:58:51

Hi there....

quote:
Also where is the calculation for 3% commission because it needs to be removed.


UPDATE [QS_REG] 
SET 
[Commision_Cost] = [ING_Acquisition] * 1.03,

quote:

But for some reason there are lots of entries in which there were no comm-cost update. There are updates for some but not for others.

It is impossible to answer the main part of your question as I have no idea what 1) the data that you're working with, and 2) the criteria for your updates.

I suggest that you separate all the updates on each table and test on the records that didn’t get an update in order to establish the reasons and make changes accordingly.

Edited by - Amethystium on 07/08/2003 11:08:23

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-08 : 11:24:38

Wow!

That's a helluva a transaction...how long does that take to execute?

There's so much to mention

1. It's too much work, can't it be broken up so you can shorten the transaction
2. INSERT's with no column lists
3. Lots of Updates and No Error checking, what if an update failed or didn't find anything to update.

Seriously, I'd break that bad boy up...does it really all have to be 1 big transaction?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources