You are extracting data from Jiwa and using it on a website for things like building quotes. The quote built on the website can then be pushed back into Jiwa as a quote, which in turn may be turned into a sales order in Jiwa.
The problem comes when you are dealing with kits. A "kit" in Jiwa is an inventory item with IN_Main.BOMObject = 3. For such an inventory item, when you add it to a quote in Jiwa, the code sees that a kit inventory item is being added, so it adds it (known as the "kit header"), then looks for "component" inventory items associated with it. These components can be found in a table called IN_Components where IN_Components.InventoryID = the InventoryID/RecID of the kit inventory item. The IN_Components.ComponentID points to the InventoryID of the component inventory item. These component items are then also added to the quote underneath the kit header and a relationship established via QO_Lines.KitLineID. This is probably better illustrated by actually doing one in a Jiwa demo database:
1. Create a fresh Jiwa demo database and log in (admin/password).
2. Go to Inventory->Maintenance.
3. Create a new inventory item (big "New" button in the ribbon menu, top left).
4. Give it a part no. of Component1 and save.
5. Create a new inventory item.
6. Give it a part no. of Component2 and save.
7. Create a new inventory item.
8. Give it a part no. of KitHeader.
9. On the Main tab, change the "Kit Style" drop-down to "Kit".
10. Go to the Alternate->Components tab, and use the lookup button in the grid on that tab to add part nos. "Component1" and "Component2" (you may have to resize the search screen and its panes as it's default sizing is screwy).
11. Set the quantity of "Component1" and "Component2" to be 1 and 2, respectively.
12. Save.
Right. Now we have created a kit. The kit header is really an abstract part and does not have SOH (stock on hand) as such. It is the component items that are "real" inventory items and can have SOH. The kit header dictates the pricing of the kit, but it is the components that actually exist as stock. If we wanted to be able to sell this kit, we would first have to transfer in some stock of Component1 and Component2. More specifically, to sell qty 1 of KitHeader, we would have to transfer in quantity 1 of Component1, and quantity 2 of Component2. But we are not going to sell this kit, we just want to create a quote - so let's do that below.
1. Go to Sales->Quoting.
2. Create a new quote.
3. Choose debtor 1001.
4. In the lines grid, use the look up button to choose the part no. "KitHeader".
5. Save.
Now look at the quote. See the colours? Blue for the kit header, and pink for its components. This colourisation occurs because when we added the kit header, its components were also pulled onto the quote and a relationship established between them and the kit header they belong to. Let's go look at the QO_Lines table for our quote:
- Code: Select all
SELECT QO_Lines.*
FROM QO_Main
JOIN QO_History ON QO_Main.InvoiceID = QO_History.InvoiceID
JOIN QO_Lines ON QO_History.InvoiceHistoryID = QO_Lines.InvoiceHistoryID
WHERE QO_Main.InvoiceNo = '100009'
AND QO_History.HistoryNo = QO_Main.CurrentHistoryNo
ORDER BY QO_Lines.LineNum
Of particular interest in the result set are the fields KittingStatus and KitLineID, so I've dumb-ed down the query a little to focus on those:
- Code: Select all
SELECT QO_Lines.InvoiceLineID, PartNo, KittingStatus, KitLineID
FROM QO_Main
JOIN QO_History ON QO_Main.InvoiceID = QO_History.InvoiceID
JOIN QO_Lines ON QO_History.InvoiceHistoryID = QO_Lines.InvoiceHistoryID
WHERE QO_Main.InvoiceNo = '100009'
AND QO_History.HistoryNo = QO_Main.CurrentHistoryNo
ORDER BY QO_Lines.LineNum
here is the result set:
- Code: Select all
InvoiceLineID,PartNo,KittingStatus,KitLineID
73e23e5a721948a995cd,KitHeader,1,
984409b237ad4c3f8584,Component1,2,73e23e5a721948a995cd
c9fe5df3a5a449a29c3f,Component2,2,73e23e5a721948a995cd
d85a61d5f33b49618134,Round,0,73e23e5a721948a995cd
You can see that the KitHeader part has a KittingStatus of 1 which denotes a kit header, whilst the Component1 and Component2 lines have a kitting status of 2 denoting a kit component. Here is the enumeration from the code:
- Code: Select all
Public Enum SalesOrderKitLineTypes
e_SalesOrderNormalLine = 0
e_SalesOrderKitHeader = 1
e_SalesOrderKitComponent = 2
End Enum
Critically, and I suspect the piece you are missing, are the values in the KitLineID column. You can see that the KitHeader part has a blank value (because it is the kit header, it needs point to nothing else), but the component lines "Component1" and "Component2" contain a value which points to the InvoiceLineID of the KitHeader line.
So, I think the answer to your question is, when adding an IN_Main.BOMObject = 3 type inventory item to your web-based quote, you need to go get its component lines from IN_Components and add those after it AND place the InvoiceLineID of the header into the KitLineID of the components. Of course, your system may have different IDs, etc. but what is important is that you establish the relationship between header and its components, and if pushing the quote into Jiwa ensuring this relation remains intact.
Let me know if this helps, if I'm on the right track, and then we can get into specifics as required.
