Automating changing the inventory status

[DannyC]

I have a client with lots and lots of partnos which over time are discontinued, deleted or otherwise not in use.
They don't change the status in Jiwa. They just indicate as such with a special leading character on the partno.

After donkeys years of using Jiwa they now are looking into using the status field properly.
The problem is, when the status is changed in Jiwa it does all those checks for backorders, SOH, active purchase orders, unactivated stock transfers.

If they were to provide a list of all the partnos and the desired status, is there a way, maybe via a plugin I guess, to somehow automate it such that Jiwa tries to change the status and if it succeeds without throwing an error then just change the status but if it throws an error it will warn the user what the issue is maybe via a grid or other way that they could address the issue(s) and try again.
Or even more fancy if there are issues, then get rid of the backorder records, or close purchase orders etc.

[Mike.Sheen]

They just indicate as such with a special leading character on the partno.

I've seen that so many times. All sorts of data gets embedded into the Part No. field - statuses, preferred suppliers, colours, sizes, it goes on and on.

IMHO this is a crime.

If they were to provide a list of all the partnos and the desired status, is there a way, maybe via a plugin I guess, to somehow automate it such that Jiwa tries to change the status and if it succeeds without throwing an error then just change the status but if it throws an error it will warn the user what the issue is maybe via a grid or other way that they could address the issue(s) and try again.

A CSV containing Part No's could easily be imported via plugin, and for each part, read the inventory item, and in a try catch block set the status and save.

Or even more fancy if there are issues, then get rid of the backorder records, or close purchase orders etc.

As above - but in the catch block deal with the obstructions and then retry the save.

[SBarnes]

Are you able to provide the code that does the checks when the status is changed as we may need to automate the clean up?

[Mike.Sheen]

If any of the following queries returns a row where the 1st column is a value > 0, then an exception is thrown when setting the Status.

Code: Select all

SELECT COUNT(OrdersOnBackID) FROM IN_OnBackOrder WHERE InventoryID = @InventoryID

Code: Select all

SELECT coalesce(SUM(SO_Lines.QuantityBackOrd),0) FROM SO_Lines JOIN SO_History ON (SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID) JOIN SO_Main ON (SO_Main.InvoiceID = SO_History.InvoiceID) WHERE SO_Lines.InventoryID = @InventoryID AND SO_History.HistoryNo = SO_Main.CurrentHistoryNo

Code: Select all

SELECT SUM(QuantityLeft) FROM IN_WarehouseSOH WHERE InventoryID = @InventoryID

Code: Select all

SELECT SUM(Quantity) FROM PO_Lines LEFT JOIN PO_Main ON (PO_Lines.OrderID = PO_Main.OrderID) WHERE InventoryID = @InventoryID AND PO_Main.Status <> 10

Code: Select all

SELECT COUNT(TransferLineID) FROM IN_TransferLines LEFT JOIN IN_Transfer ON (IN_TransferLines.TransferID = IN_Transfer.TransferID) WHERE (FromPartInventoryID = @InventoryID OR ToPartInventoryID = @InventoryID) AND IN_Transfer.Status = 0

[SBarnes]

Hi Mike,

Unless I am missing something aren't the first two SQL statements looking at the same thing?

[Mike.Sheen]

Unless I am missing something aren't the first two SQL statements looking at the same thing?

Yes they are, in that they both check for backorders. The second query specifically targets backorders associated with sales orders, however.

The second one should probably be removed from our checks as it doesn't do anything special - perhaps once upon a time it did, but the original purpose is now lost to the ages...

[SBarnes]

OK so I have most of this working except when I try and write off stock with the following function

Code: Select all

private void WriteOffStock(JiwaFinancials.Jiwa.JiwaServiceModel.Tables.IN_WarehouseSOH whSOH,  JiwaFinancials.Jiwa.JiwaInventory.Inventory Inventory)
   {
      JiwaFinancials.Jiwa.JiwaApplication.Manager manager = Inventory.Manager;
      JiwaFinancials.Jiwa.JiwaStockTransfer.StockTransfer jtransfer = manager.BusinessLogicFactory.CreateBusinessLogic<JiwaFinancials.Jiwa.JiwaStockTransfer.StockTransfer>(null);
      jtransfer.CreateNew();
      jtransfer.TransferDate = manager.Database.SysDateTime;
      jtransfer.LogicalWarehouseResidingIn.ReadRecord(whSOH.IN_LogicalID);
      JiwaFinancials.Jiwa.JiwaStockTransfer.Line line = manager.CollectionItemFactory.CreateCollectionItem<JiwaFinancials.Jiwa.JiwaStockTransfer.Line>();
      line.FromInventory.ReadRecord(whSOH.InventoryID);
      line.TransferQuantity = whSOH.QuantityLeft == null ? 0 : (decimal) whSOH.QuantityLeft;
      //line.FromPartExpiryDate = DateTime.Now;
      jtransfer.Lines.Add(line);
      jtransfer.Save();   
      jtransfer.Read(jtransfer.RecID);
      jtransfer.Status = JiwaFinancials.Jiwa.JiwaStockTransfer.StockTransfer.Statuses.Activated;
      jtransfer.Save();      
   }
   


It crashes at the first call to save with the following stack trace, obviously there is some date property that needs setting but I can't figure out what

Code: Select all

System.Exception occurred
  HResult=0x80131500
  Message=One or more errors occurred.

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

  Source=<Cannot evaluate the exception source>
  StackTrace:
   at JiwaFinancials.Jiwa.JiwaApplication.JiwaCollection`1.iSave()
   at JiwaFinancials.Jiwa.JiwaApplication.JiwaCollection`1.Save()
   at JiwaFinancials.Jiwa.JiwaStockTransfer.StockTransfer.iSave()
   at JiwaFinancials.Jiwa.JiwaApplication.BusinessLogic.Maintenance.Save()
   at BusinessLogicPlugin.WriteOffStock(IN_WarehouseSOH whSOH, Inventory Inventory) in c:\ProgramData\Jiwa Financials\Jiwa 7\7.2.1\SBarnes\DS9MSSQLSERVER2012\JiwaDemo7\Plugins\Admin\Compile\Attkey Inventory Import Status\Attkey Inventory Import Status.cs:line 297
   at BusinessLogicPlugin.SetStatusInfo(Inventory& Inventory, String Value, String RowData, String[] Row, Int32 RowNo, Mapping Mapping) in c:\ProgramData\Jiwa Financials\Jiwa 7\7.2.1\SBarnes\DS9MSSQLSERVER2012\JiwaDemo7\Plugins\Admin\Compile\Attkey Inventory Import Status\Attkey Inventory Import Status.cs:line 372
   at BusinessLogicPlugin.<AppendProperties>b__0(Inventory inventory, String value, String rowData, String[] row, Int32 rowNo, Mapping mapping) in c:\ProgramData\Jiwa Financials\Jiwa 7\7.2.1\SBarnes\DS9MSSQLSERVER2012\JiwaDemo7\Plugins\Admin\Compile\Attkey Inventory Import Status\Attkey Inventory Import Status.cs:line 141
   at JiwaFinancials.Jiwa.JiwaInventory.Import.DestinationProperty.SetProperty(Inventory Inventory, String Value, String RowData, String[] Row, Int32 RowNo, Mapping Mapping)
   at JiwaFinancials.Jiwa.JiwaInventory.Import.InventoryImport.SetDestinationProperty(Inventory& Inventory, Mapping Mapping, String Value, String RowData, String[] Row, Int32 RowNo)
   at JiwaFinancials.Jiwa.JiwaInventory.Import.InventoryImport.SetDestinationProperties(Inventory& Inventory, String RowData, String[] Row, Int32 RowNo)
   at JiwaFinancials.Jiwa.JiwaInventory.Import.InventoryImport.Process()


[SBarnes]

Ignore the above I worked out the issue on the transfer lines you have to set the expiry dates even if the products don't use them.

And I got the whole thing to work through the inventory import screen, I just have to test it on the customer database as it works on the demo one.