Issue with pallet buys

[JuiceyBrucey]

Hi,
7.2.1.0
SR 16
We got an error in JIWA when converting a quote into an order.
I got some info on what the issue is, but I need someone to elaborate a bit more so I know exactly what is happening.
Here is the support info:
"When you load a Sales Order with a Pallet purchase, you are not linking the Single Unit Line to the Kit Line
So when they try and convert a Quote to a Sales Order, they are getting an Error"

I am not sure what this means and need more info.
Thank you.
Cheers

[Mike.Sheen]

Rule #1 when you get an error in Jiwa and you want it fixed:

CITE THE BLOODY ERROR MESSAGE

Rule #2 when you get an error in Jiwa and you want it fixed:
Reproduce the error in demo data, and then provide complete and accurate steps of what you did in demo data to get the error, so we can reproduce the issue and provide a fix / work-around / explanation.

If that's too much effort, then you don't really need a solution.

[JuiceyBrucey]

Thank you for your bloody reply.
Now where do I find the f****** error message?
Is there a bloody error log???
Sorry, that information was not bloody supplied to me.
Thank you.

[JuiceyBrucey]

So still no info on this?
So you know:
Staff got an error and told me in a phone call that an error stated object not found. Staff did not screen shot or give me any more information. So I am completely in the dark.
It was obviously something more to do with JIWA, than the website, so I asked Gary if he knew what the issue was and this was his reply:
"When you load a Sales Order with a Palle purchase, you are not linking the Single Unit Line to the Kit Line
So when they try and convert a Quote to a Sales Order, they are getting an Error"
So he knew what the problem was, but I haven't got a clue. I am not a JIWA developer so this means very little to me.
I asked for more information so I can understand what his reply meant, I have not got any more information as yet.
Is there an error log????
Is there ANYWHERE I can see ANY type of error message stored????
Can I get ANY more information from ANYONE????
No, no and no.
So I posted it on here in the hope that someone would assist because I could not do anything else.
If there was an error log, I could have posted the bloody error message because I did not see it myself and it was not saved by anyone.
Maybe consider building an inbuilt error logging system like most programs have. Then I could go and get the bloody thing.
So, does anyone have ANY information on what this problem is????

[Scott.Pearce]

Are you able to set this scenario up in a demo database? If so, do that, and provide the steps you followed to make the error happen. Then we can replicate the issue here and tell you more about what's going on.

[JuiceyBrucey]

Hi Scott,
I will see what I can do.
Thank you for your reply.
Cheers

[JuiceyBrucey]

Ok, so now I have more information. This is the problem:
"Hi Bruce,

Where an Inventory Item is a Pallet Buy, they are setup as a Kitset.
This is flagged by ‘IN_Main.BOMObject’ = 3
e.g. ATHP

This then pulls the SOH from a Component Product Code
The Link can be found in the ‘IN_Components’ table
e.g. ATH

The Sales Order must have both the Master Sales Line and The Component Line(s) – There may be more than one component to a Kit.
Example Script of the link
SELECT IN_Main.InventoryID, IN_Main.PartNo, IN_Main.Description, IN_Components.Quantity, IN_Components.LineNumber, IN_Main_1.InventoryID AS Component_INID, IN_Main_1.PartNo AS Component_INPartNo,
IN_Main_1.Description AS Component_INDescription
FROM IN_Components INNER JOIN
IN_Main ON IN_Components.InventoryID = IN_Main.InventoryID INNER JOIN
IN_Main AS IN_Main_1 ON IN_Components.ComponentID = IN_Main_1.InventoryID
Where IN_Main.PartNo = 'ATHP'

Example of the issue…..
SELECT QO_Lines.InvoiceLineID, QO_Lines.GroupNo, QO_Lines.LineNum, QO_Lines.CommentLine, QO_Lines.PartNo, QO_Lines.Description, QO_Lines.KittingStatus, QO_Lines.KittingUnits, QO_Lines.KitStyle, QO_Lines.KitLineID,
QO_Main.InvoiceID, QO_Main.DebtorID, QO_Main.StaffID, QO_Main.InvoiceNo
FROM QO_Lines INNER JOIN
QO_History ON QO_Lines.InvoiceHistoryID = QO_History.InvoiceHistoryID INNER JOIN
QO_Main ON QO_History.InvoiceID = QO_Main.InvoiceID
Where KitStyle <> 0 and InvoiceNo = '0000000132'
Order by QO_Main.InvoiceNo

InvoiceLineID GroupNo LineNum CommentLine PartNo Description KittingStatus KittingUnits KitStyle KitLineID InvoiceID DebtorID StaffID InvoiceNo
5e2876f6d47b44df9909 1 45 0 SHA PINE WOODSHAVINGS BALES (SMALL) 14 KG 2 1 3 6ce52a2672aa4c0ea4d0 57ef0f687e704c9ea202 8d3c8d0d1e474f73bc25 fb7cca6ee4494c069fdc 132
6ce52a2672aa4c0ea4d0 1 44 0 SHAP PINE WOODSHAVINGS SMALL PALLET BUY 1 0 3 57ef0f687e704c9ea202 8d3c8d0d1e474f73bc25 fb7cca6ee4494c069fdc 132
7f74864a815e42b3b4c9 1 33 0 SHAP PINE WOODSHAVINGS BALES (SMALL) 1 BALE 1 0 3 57ef0f687e704c9ea202 8d3c8d0d1e474f73bc25 fb7cca6ee4494c069fdc 132

In the Above example the Kit Line ID is missing……. And the Product “SHAP” is entered twice
As you have added the line 33 when importing….
But Salce Staff then have to manually add the SHAP – Line44, which then adds the Line SHA – Line45, to get the product to transfer to the Sales Order."

So my question now is:
I want to extract an inventory item. I want to ensure that ALL information is available including components or anything else that I might need to send through in a quote. I want to take this extracted inventory data, store it on the website database so it can be used to formulate future quote orders so that there is nothing left out and I dont end up getting errors.
This is the SQL from one of my current inventory extraction plugins:

Code: Select all

var query = @"
                SELECT
                main.LastSavedDateTime,
                main.InventoryID,
                main.PartNo,
                main.Description,
                main.Status,
                main.DefaultPrice,
                main.RRPPrice,
                main.Aux2,
                main.BackOrderable,

               price.ForwardPriceDate,
               price.CurrentPriceDate,
 
                price.Price1, price.ForwardPrice1,
                price.Price2, price.ForwardPrice2,
                price.Price3, price.ForwardPrice3,
                price.Price4, price.ForwardPrice4,
                price.Price5, price.ForwardPrice5,
                price.Price6, price.ForwardPrice6,
                price.Price7, price.ForwardPrice7,
                price.Price8, price.ForwardPrice8,
                price.Price9, price.ForwardPrice9,

                uom.Name AS UOM_Name,
                uom.QuantityInnersPerUnitOfMeasure AS UOM_QuantityInners,
                uom.IsSell AS UOM_IsSell,
                uom.IsPurchase AS UOM_IsPurchase,
                uom.Barcode AS UOM_Barcode,
                uom.IsEnabled AS UOM_IsEnabled,
               uom.ItemNo AS UOM_ItemNo

            FROM IN_Main main
            LEFT JOIN IN_SellingPrices price
                ON price.InventoryID = main.InventoryID

            OUTER APPLY (
                SELECT TOP 1 *
                FROM IN_UnitOfMeasure u
                WHERE u.IN_Main_InventoryID = main.InventoryID
                  AND u.IsEnabled = 1
                  AND u.Name = 'PALLET'
                ORDER BY u.Name 
            ) uom

            WHERE main.InventoryID = @InventoryID;
            ;";

            var result = Db.SqlList<MyCustomModel>(query, new { InventoryID = request.InventoryID });
            return result.ConvertTo<GetInventoryByIdLimitFieldsResponse>();

This is the model:

Code: Select all

public class MyCustomModel
    {
        public DateTime LastSavedDateTime { get; set; }
        public string InventoryID { get; set; }
        public string PartNo { get; set; }
        public string Description { get; set; }
        public int Status { get; set; }
        public decimal DefaultPrice { get; set; }
        public decimal RRPPrice { get; set; }
        public string Aux2 { get; set; }
        public bool BackOrderable { get; set; }

      public string    UOM_Name { get; set; }
      public int       UOM_QuantityInners { get; set; }
      public bool    UOM_IsSell { get; set; }
      public bool    UOM_IsPurchase { get; set; }
      public string    UOM_Barcode { get; set; }
      public bool    UOM_IsEnabled { get; set; }
      public int       UOM_ItemNo {get; set;}
      
      public DateTime ForwardPriceDate { get; set; }
      public DateTime CurrentPriceDate { get; set; }
        public decimal Price1 { get; set; }
        public decimal ForwardPrice1 { get; set; }
        public decimal Price2 { get; set; }
        public decimal ForwardPrice2 { get; set; }
        public decimal Price3 { get; set; }
        public decimal ForwardPrice3 { get; set; }
        public decimal Price4 { get; set; }
        public decimal ForwardPrice4 { get; set; }
        public decimal Price5 { get; set; }
        public decimal ForwardPrice5 { get; set; }
        public decimal Price6 { get; set; }
        public decimal ForwardPrice6 { get; set; }
        public decimal Price7 { get; set; }
        public decimal ForwardPrice7 { get; set; }
        public decimal Price8 { get; set; }
        public decimal ForwardPrice8 { get; set; }
        public decimal Price9 { get; set; }
        public decimal ForwardPrice9 { get; set; }
         
    }

Instead of me spending hours working out and testing, could someone tell me what SQL and model will get ALL of the information I need to capture everything I need so that I can store this info and use it in future quotes?
Also, is there an example of all of the JSON that needs to be sent so that nothing is left out?
Thank you.

[JuiceyBrucey]

can someone tell me if there is any intention of answering this support query?
Even just say "work it out yourself" or "not our job".
At least then I will know what course of action to take and will start working on it myself.
I have not a clue if I am asking too much or if I am over stepping the mark by asking this. Either way, just let me know so I can decide what to do.
Thank you.
Cheers

[Scott.Pearce]

Pretty busy here, but I'll endeavor to take a look tomorrow morning.

[JuiceyBrucey]

Thank you very much Scott.
I appreciate it.
Cheers