multiple 'discount programs' per debtor

[pricerc]

I have multiple vendors making gadgets.

These vendors sell these gadgets to a distributor (running Jiwa), who on-sells them to resellers, who on-sell them to consumers.

To keep it interesting, these vendors each offer a range of discount programs for resellers, with the distributor expected to honour the discounts those resellers have signed up for (with the vendor).

Thus, any given reseller may be participating in many discount programs. Possibly even more than one per vendor (a vendor might have multiple product lines, with different lines having different discount programs).

I need to be able to sign up a single debtor to multiple different 'discount programs'.

I'm leaning towards adapting the "List Maintenance" sample plugin to add a "reseller discount programs" list onto the Debtor Maintenance 'Financials" tab - where the 'widgets' being added to a debtor are Debtor Pricing Groups (i.e. the lookup table for things you can put into the list).

I will then adapt the price group matrix stored procedure to incorporate the new custom table (augment the "-- Debtor Group is linked to @DebtorID" bits).

And just in case I wasn't entertained enough - the discount programs I'm immediately dealing with have different discount rates for different product lines within the vendor's suite of products, so I will be building up the (currently unused) group matrix pricing table.

[SBarnes]

Was there a question in all that somewhere?

In short if I was trying to do this I would introduce a custom pricing rule which is simply a stored procedure that follows the same input pattern and output pattern as Jiwa's pricing rules and add that to the pricing schemes.

As for a custom way of putting the debtor into different pricing programs yes adapting list maintenance to create a list of programs would work, but then you would probably need another table to link the list and discounts for this I would usually add a tab under debtor maintenance with a grid on it that went to a second table with the discount in it, the debtor id and the primary key of the program table and go from there.

[pricerc]

Was there a question in all that somewhere?

No.

But I thought it would be a good place to record my thoughts lest someone else has a similar problem...

I'll also post the plugin when I'm done.

The existing group matrix (debtor pricing group vs inventory pricing group) has the exact structure we need, except that a debtor can only be in one debtor pricing group. And our customer is not currently using it, so there is no risk of conflicting with existing data.

So I'm 'just' changing it so that a debtor can be in multiple pricing groups.

The way this company operates they do a single vendor per sales order, and then do a back-to-back P/O. So, the group matrix will likely end up in its own price scheme, which will be used for these kinds of vendor/reseller agreements, other sales will not use the group matrix, and will use their existing price schemes.

I've already set up a new table:

Code: Select all

   RecID uniqueidentifier not null default newid(),
   DebtorID char(20) not null,
   DebtorPricingGroupID varchar(36) NOT NULL,
   LastSavedDateTime datetime NOT NULL DEFAULT sysdatetime(),
   ItemNo int NOT NULL,
   EffectiveDate datetime NULL,
   ExpiryDate datetime NULL,
   RowHash rowversion

Right now, I'm just working on getting the DebtorID, DebtorPricingGroupID bit working, which is the minimum I need to test my adapted stored procedure.

There's already a unique index on DebtorID, DebtorPricingGroupID, I'll add additional sanity checking along the way.

For now, I've gone with adding a tab to the "Financials" tab in debtor maintenance (next to 'Pricing'), I considered putting the grid on to the Pricing tab, but thought a whole new tab would be cleaner, code-wise.

I expect the stored procedure to be the easy part - there is already one for debtor pricing group vs inventory pricing group - I just need to adapt it to also look up my new table and not just db_main to get debtor pricing groups.