Creating Pricing Rules in Debtor Pricing Schemes Bug  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Creating Pricing Rules in Debtor Pricing Schemes Bug  Topic is solved

Postby SBarnes » Thu Jan 30, 2020 11:43 am

There is a pricing rules bug when you try to add pricing rules to a pricing scheme see https://service.jiwa.com.au/servicedesk ... /JIWA-5724.

Basically its because the VB Script property is null.

The attached plugin will get around the issue for the time being until it gets fixed.
Attachments
Plugin Fix Pricing Scheme Maintenance.xml
Plugin fixes bug
(31.73 KiB) Downloaded 49 times
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby Mike.Sheen » Sat Feb 01, 2020 1:04 pm

Thanks, Stuart - for reporting this and especially thanks for being generous and sharing a workaround!

One issue with the plugin you have provided is multiple instance interference when using a private variable in the plugin classes - as discussed in this post.

It's only a potential issue where you have the same form (in this case the Price Schemes form) open multiple times - and I know you are probably aware of the issue, and I, myself, are guilty of using the same flawed strategy.

I know it's highly unlikely in this case for it to pose a problem, but It's a good habit to avoid using private variables. I'm not so much pointing this out for your benefit, as I know you already know all this - but for those following along or stumbling across this post in the future and adapting the code for their own purposes.

In this case, you stash away a reference to the form in a private variable, pform within the SetupBeforeHandlers method - so you can access that within the UltraToolbarsManager1.ToolClick handler of the form.

You could instead of gotten back to the form instance within the handler using the DockWithinContainer property - which you yourself demonstrated here.

An alternative to this, which is useful for other handlers where you don't have a useful property like the DockWithinContainer property to get a reference back to the form (or anything else) is to inject properties into the handler using a delegate...

Code: Select all
public void SetupBeforeHandlers(JiwaFinancials.Jiwa.JiwaApplication.IJiwaForm JiwaForm, JiwaFinancials.Jiwa.JiwaApplication.Plugin.Plugin Plugin)
{
      JiwaFinancials.Jiwa.JiwaPriceSchemesUI.frmPriceScheme pform = (JiwaFinancials.Jiwa.JiwaPriceSchemesUI.frmPriceScheme) JiwaForm;
      pform.UltraToolbarsManager1.ToolClick += delegate(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e) { UltraToolbarsManager_ToolClick(sender, e, pform); };
}
...
public virtual void UltraToolbarsManager_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e, JiwaFinancials.Jiwa.JiwaPriceSchemesUI.frmPriceScheme pform)
{
   // in here we have access to the pform parameter, which was injected at the time of adding the handler - no need for a private variable and avoiding interference when the same form is open multiple times.
}


Again, I know you already are familiar with the above technique - just pointing this out for the benefit of others :)
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: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby SBarnes » Sat Feb 01, 2020 4:20 pm

Hi Mike,

yes I am aware of the private variable idea being flawed but kept it simple in the fact as you say the chances of opening multiple copies of the form are remote especially given the bug has taken this long to show up.

One question I do however have is why does the vbscript property exists as unless I've missed it there is nowhere in the interface to set it anyhow?
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby Mike.Sheen » Sat Feb 01, 2020 6:07 pm

SBarnes wrote:One question I do however have is why does the vbscript property exists as unless I've missed it there is nowhere in the interface to set it anyhow?


Purely legacy reasons. We didn't want to delete or remove the legacy script customers had from version 6. We don't use that property now in version 7, but kept it in the table in case after upgrade customers needed to refer back to it to re-discover what the script was doing.

We're fairly cautious about removing data on upgrade, and there are a few other examples around where we kept the data but don't use it.

To date nobody has approached us relating to custom pricing which used VB Script in version 6 and how to make it work in version 7. But if they did, we'd have a fighting chance of offering a plugin to replicate that behaviour seeing as we did keep the old data in that column.
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: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby SBarnes » Sat Feb 01, 2020 6:47 pm

Hi Mike,

Thanks for the explanation, I suppose if you ever had to do it you could always introduce a plugin interface similar to what happened for custom fields where what used to have a vbscript then became a plugin which would definitely open up what could be done including data from other sources but there is no point in answering a question no one is asking and given this bug has only just surfaced you'd have to believe this is the first time some one has tried adding a new custom pricing rule since the launch of version 7.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby Mike.Sheen » Sun Feb 02, 2020 1:14 pm

SBarnes wrote:you could always introduce a plugin interface similar to what happened for custom fields where what used to have a vbscript then became a plugin which would definitely open up what could be done including data from other sources


We could, but that would mean our fancy stored procedure usp_JIWA_Price_GetPrice which iterates through the prices in a price scheme and returns the prices would no longer have parity with the price scheme business logic. Unless we opened up the stored proc to invoke managed code...

SBarnes wrote:but there is no point in answering a question no one is asking and given this bug has only just surfaced you'd have to believe this is the first time some one has tried adding a new custom pricing rule since the launch of version 7.


Exactly. Whilst I would like to do this, as it "feels" right - it doesn't make much business sense investing the time and risking introducing problems arising from making such changes unless there is demand for it.

FWIW this issue is logged as DEV-8038 to be fixed in 7.2.1 SR3.
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: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby SBarnes » Sun Feb 02, 2020 1:29 pm

I don't believe you can call managed code under Azure SQL so that would rule out that side of it anyway.

More importantly does usp_JIWA_Price_GetPrice handle custom rules i.e. does it do exactly the same functionality as the PriceScheme object or will it need modifying?

It may interest you to know that I am only aware out of our customers of one who has a custom rule and possibly a second after we talk to them this week, nice to know such an elegant solution is under appreciated :lol:

If it's any consolation I followed your lead in a file extraction I built into the web api where it looks at a master stored procedure but you can override it at the debtor level as I hate seeing things hard coded.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Creating Pricing Rules in Debtor Pricing Schemes Bug

Postby Mike.Sheen » Sun Feb 02, 2020 2:36 pm

SBarnes wrote:More importantly does usp_JIWA_Price_GetPrice handle custom rules i.e. does it do exactly the same functionality as the PriceScheme object or will it need modifying?


It does handle custom rules - it inspects the price scheme, and for each price "element", aka rule, it executes the stored procedure associated with that and stores the results into temp tables and then operates on that.

It's very simple:
Code: Select all
ALTER PROCEDURE [dbo].[usp_JIWA_Price_GetPrice] @SP_PriceSchemeID
CHAR(20),@SP_DebtorID CHAR(20), @SP_OrderDate DATETIME, @SP_InventoryID
CHAR(20), @SP_LogicalID CHAR(20), @SP_Quantity FLOAT AS
SET NOCOUNT ON

DECLARE @MoneyDecimalPlaces TINYINT
DECLARE @PricestoredProcName VARCHAR(255)
DECLARE @FindTheCheapestPrice BIT
DECLARE @UseZeroPrices BIT

CREATE TABLE #TempPrices
(
   PriceElementNo INT IDENTITY NOT NULL,   
   Price DECIMAL(19,6),
   Description varchar(100) COLLATE Database_Default,
   HasQuantityBreak Bit,
   QuantityBreak DECIMAL(19,6),
    HasDateRange TINYINT,
   StartDate datetime NULL,
   EndDate datetime NULL,
   HasSOHID TINYINT,
   SOHID CHAR(20) NULL,
   IncTax BIT,
   RecID CHAR(36)
)

CREATE TABLE #Prices
(
   PriceElementNo INT IDENTITY NOT NULL,       
   Price DECIMAL(19,6),
   Description varchar(100) COLLATE Database_Default,
   HasQuantityBreak Bit,
   QuantityBreak DECIMAL(19,6),
    HasDateRange TINYINT,
   StartDate datetime NULL,
   EndDate datetime NULL,
   HasSOHID TINYINT,
   SOHID CHAR(20) NULL,
   IncTax BIT,
   RecID CHAR(36)   
)

DECLARE @IncTax BIT

SELECT TOP 1 @MoneyDecimalPlaces = DecimalPlaces FROM FX_Currency WHERE IsLocal = 1
SET @MoneyDecimalPlaces = ISNULL(@MoneyDecimalPlaces,0)

SET @FindTheCheapestPrice = (SELECT TOP 1 COALESCE(FindTheCheapest,0) FROM IN_PriceSchemes WHERE PriceSchemeID=@SP_PriceSchemeID)
SET @FindTheCheapestPrice = ISNULL(@FindTheCheapestPrice,0)

--Read in the price scheme USED prices (only the store procedure based ones)
DECLARE PriceElements CURSOR FOR
SELECT COALESCE(PriceStoredProcName, ''), COALESCE(UseZeroPrice, 0)
FROM IN_PriceSchemePrice
INNER JOIN IN_PriceSchemePriority ON IN_PriceSchemePriority.PriceSchemePriceID=IN_PriceSchemePrice.PriceSchemePriceID
WHERE PriceIsStoredProc=1
AND PriceActive=1
AND PriceSchemeID=@SP_PriceSchemeID
ORDER BY SchemePriority,PriceDescription

OPEN PriceElements

FETCH NEXT FROM PriceElements INTO @PricestoredProcName, @UseZeroPrices
WHILE (@@FETCH_STATUS <> -1)
   BEGIN           
      IF LTRIM(RTRIM(@PricestoredProcName)) <> ''
         BEGIN
                DELETE FROM #TempPrices

            INSERT INTO #TempPrices
            EXEC @PricestoredProcName @SP_DebtorID, @SP_OrderDate, @SP_InventoryID, @SP_LogicalID, @SP_Quantity            

                IF @UseZeroPrices = 0
                    DELETE FROM #TempPrices WHERE Price = 0

            INSERT INTO #Prices
                SELECT    Price, Description,   HasQuantityBreak, QuantityBreak, HasDateRange, StartDate,
                        EndDate, HasSOHID, SOHID, IncTax, RecID
                FROM #TempPrices
         END
      FETCH NEXT FROM PriceElements INTO @PricestoredProcName, @UseZeroPrices
   END

CLOSE PriceElements
DEALLOCATE PriceElements

IF (SELECT COUNT(*) FROM #Prices) = 0
BEGIN
   INSERT INTO #Prices
   SELECT DefaultPrice, 'Default Sell Price', 0, 0, 0, '', '', 0, '', SellPriceIncTax, InventoryID
   FROM IN_Main
   WHERE InventoryID = @SP_InventoryID
END

SELECT *
FROM #Prices
WHERE ((@SP_Quantity > QuantityBreak) AND HasQuantityBreak = 1) OR (HasQuantityBreak = 0)
ORDER BY PriceElementNo

SET NOCOUNT OFF
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


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron