Page 1 of 1
SQL for Check for Special Pricing
Posted:
Wed Jan 15, 2020 5:35 pm
by SBarnes
What SQL would reveal for a debtor id and inventory inventory combination that the default price would not apply, in every case the check would be done with a quantity of one and today's date?
I don't need the price just that it won't be the default.
Re: SQL for Check for Special Pricing
Posted:
Fri Jan 17, 2020 5:21 pm
by Mike.Sheen
SBarnes wrote:What SQL would reveal for a debtor id and inventory inventory combination that the default price would not apply, in every case the check would be done with a quantity of one and today's date?
I don't need the price just that it won't be the default.
If I had a few spare hours I reckon I could come up with a chunk of SQL which could do this.
To get you started:
- Code: Select all
CREATE PROC NotDefaultPrice(@InventoryID CHAR(20), @DebtorID CHAR(20))
AS
DECLARE @HasPrice BIT = 0
IF EXISTS(SELECT * FROM IN_DebtorSpecificPrice WHERE IN_DebtorSpecificPrice.DebtorID = @DebtorID AND IN_DebtorSpecificPrice.InventoryID = @InventoryID)
SET @HasPrice = 1
IF (@HasPrice = 0)
IF EXISTS(SELECT * FROM IN_DebtorClassificationSpecificPrice WHERE IN_DebtorClassificationSpecificPrice.DebtorClassificationID = (SELECT DB_Main.ClassificationID FROM DB_Main WHERE DebtorID = @DebtorID)
AND IN_DebtorClassificationSpecificPrice.InventoryID = @InventoryID)
SET @HasPrice = 1
-- Repeat for each source of a price
SELECT @HasPrice
GO
Re: SQL for Check for Special Pricing
Posted:
Fri Jan 17, 2020 5:46 pm
by SBarnes
Thanks Mike
I am trying to produce a file through the web api that can be run for a specific debtor and give a price per item for everything that is in their preset list, problem is a customer could have thousands to products and to get the price for each when it may be the default means the process can take a couple of minutes to complete, serious grunt, so I am trying to create a situation where I only need to get the price for those where the default doesn't apply to process less.
Of course given that a customer is going to need a web api key to access this another thought process will be on a scheduled basis create a listing of all debtors and inventory combinations where the debtor has an api key in a permanent working table where the default doesn't apply and use this at file generation time to speed things up.
You have been working with the database I am referring to on a support ticket today so you realise the size we are working with.
Re: SQL for Check for Special Pricing
Posted:
Mon Jan 20, 2020 1:21 pm
by pricerc
a table-valued function like this might help.
you can used DebtorID or AccountNumber and InventoryID or PartNo.
- Code: Select all
SELECT p.AccountNo, p.AccountName, i.PartNo, i.Description, p.PriceUsed, p.EffectivePrice FROM
IN_Main i
cross apply [dbo].[AERP_GetDebtorPricing] ('1000', i.PartNo) p
order by i.PartNo
- Code: Select all
CREATE FUNCTION [dbo].[AERP_GetDebtorPricing] (@debtor VARCHAR(20), @inventory VARCHAR(20))
RETURNS TABLE
AS
RETURN (
WITH DebtorPart AS (
SELECT DebtorID
, AccountNo
, AccountName = Name
, DB_Main.ClassificationID
, Classification = DB_Classification.Description
, DB_Main.PricingGroupID
, GroupDescription = DB_PricingGroups.Description
, IN_Main.InventoryID
, PartNo
, PartDescription = IN_Main.Description
, DefaultPrice = nullif(DefaultPrice, 0)
, LastCost = nullif(LCost, 0)
, RRP = nullif(RRPPrice, 0)
, SellingPrice1 = nullif(Price1, 0)
FROM DB_Main
LEFT JOIN DB_Classification
ON DB_Classification.DebtorClassificationID = DB_Main.ClassificationID
LEFT JOIN DB_PricingGroups
ON DB_PricingGroups.RecID = DB_Main.PricingGroupID
CROSS JOIN IN_Main
LEFT JOIN IN_SellingPrices
ON IN_SellingPrices.InventoryID = IN_Main.InventoryID
WHERE (IN_Main.InventoryID = @inventory OR IN_Main.PartNo = @inventory)
AND (DB_Main.DebtorID = @debtor OR DB_Main.AccountNo = @debtor)
)
, DebtorPrice AS (
SELECT DebtorPrice = CASE OPMode
WHEN 0
THEN CASE Source
WHEN 0
THEN DefaultPrice * (1 + Amount / 100)
WHEN 1
THEN LastCost * (1 + Amount / 100)
WHEN 2
THEN RRP * (1 + Amount / 100)
WHEN 3
THEN SellingPrice1 * (1 + Amount / 100)
END
ELSE CASE Source
WHEN 0
THEN DefaultPrice + Amount
WHEN 1
THEN LastCost + Amount
WHEN 2
THEN RRP + Amount
WHEN 3
THEN SellingPrice1 + Amount
END
END
FROM DebtorPart
LEFT JOIN IN_DebtorSpecificPrice DP
ON DP.DebtorID = DebtorPart.DebtorID
AND DP.InventoryID = DebtorPart.InventoryID
AND DP.UseQuantityBreak = 0
AND DP.QuantityBreak = 0
)
, ClassPrice AS (
SELECT ClassPrice = CASE OPMode
WHEN 0
THEN CASE Source
WHEN 0
THEN DefaultPrice * (1 + Amount / 100)
WHEN 1
THEN LastCost * (1 + Amount / 100)
WHEN 2
THEN RRP * (1 + Amount / 100)
WHEN 3
THEN SellingPrice1 * (1 + Amount / 100)
END
ELSE CASE Source
WHEN 0
THEN DefaultPrice + Amount
WHEN 1
THEN LastCost + Amount
WHEN 2
THEN RRP + Amount
WHEN 3
THEN SellingPrice1 + Amount
END
END
FROM DebtorPart
LEFT JOIN IN_DebtorClassificationSpecificPrice CP
ON CP.DebtorClassificationID = DebtorPart.ClassificationID
AND CP.InventoryID = DebtorPart.InventoryID
AND CP.UseQuantityBreak = 0
AND CP.QuantityBreak = 0
)
, GroupPrice AS (
SELECT GroupPrice = CASE OPMode
WHEN 0
THEN CASE Source
WHEN 0
THEN DefaultPrice * (1 + Amount / 100)
WHEN 1
THEN LastCost * (1 + Amount / 100)
WHEN 2
THEN RRP * (1 + Amount / 100)
WHEN 3
THEN SellingPrice1 * (1 + Amount / 100)
END
ELSE CASE Source
WHEN 0
THEN DefaultPrice + Amount
WHEN 1
THEN LastCost + Amount
WHEN 2
THEN RRP + Amount
WHEN 3
THEN SellingPrice1 + Amount
END
END
FROM DebtorPart
LEFT JOIN IN_DebtorPricingGroupSpecificPrice GP
ON GP.RecID = DebtorPart.PricingGroupID
AND GP.InventoryID = DebtorPart.InventoryID
AND GP.UseQuantityBreak = 0
AND GP.QuantityBreak = 0
)
SELECT DebtorID
, AccountNo
, AccountName
, ClassificationID
, Classification
, PricingGroupID
, GroupDescription
, InventoryID
, PartNo
, PartDescription
, PriceUsed =
case
when DebtorPrice is not null then 'DebtorPrice'
when GroupPrice is not null then 'GroupPrice'
when ClassPrice is not null then 'ClassPrice'
when DefaultPrice is not null then 'DefaultPrice'
when SellingPrice1 is not null then 'SellingPrice1'
else 'RRP'
end
, EffectivePrice = COALESCE(DebtorPrice, GroupPrice, ClassPrice, DefaultPrice, SellingPrice1, RRP)
, DefaultPrice = convert(decimal(10,2), DefaultPrice)
, RRP = convert(decimal(10,2), Coalesce(RRP, 0))
, SellingPrice1 = convert(decimal(10,2), Coalesce(SellingPrice1,0))
, DebtorPrice = convert(decimal(10,2), Coalesce(DebtorPrice,0))
, GroupPrice = convert(decimal(10,2), Coalesce(GroupPrice,0))
, ClassPrice = convert(decimal(10,2), Coalesce(ClassPrice,0))
FROM DebtorPart
, DebtorPrice
, GroupPrice
, ClassPrice
);
GO
ALTER AUTHORIZATION ON [dbo].[AERP_GetDebtorPricing] TO SCHEMA OWNER
GO
*edit - more relevant sample query - a single debtor.
Re: SQL for Check for Special Pricing
Posted:
Mon Jan 20, 2020 1:28 pm
by pricerc
That TVF can be modified further. For our customer, they wanted all the detail that's in there. But any unnecessary parts can be removed.
e.g. if you don't do debtor group pricing, then take that bit out.
and if you have a separate customer/part list somewhere else, those values can be used for input to the TVF instead of IN_Main.