SQL for Check for Special Pricing

Discussions relating to plugin development, and the Jiwa API.

SQL for Check for Special Pricing

Postby SBarnes » Wed Jan 15, 2020 5:35 pm

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.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: SQL for Check for Special Pricing

Postby Mike.Sheen » Fri Jan 17, 2020 5:21 pm

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
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: SQL for Check for Special Pricing

Postby SBarnes » Fri Jan 17, 2020 5:46 pm

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.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: SQL for Check for Special Pricing

Postby pricerc » Mon Jan 20, 2020 1:21 pm

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.
Last edited by pricerc on Mon Jan 20, 2020 1:35 pm, edited 1 time in total.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: SQL for Check for Special Pricing

Postby pricerc » Mon Jan 20, 2020 1:28 pm

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 3 guests

cron