I have a customer, v2018 Advanced, who reached out to us requesting a report to see the last sold date and last receipt date for all item codes (including miscellaneous). These fields already exist in the CI_Item Table which they could find and use, but there was 1 issue. They use Job Cost and there's a good number of items that they purchase against a Job. Therefore, it never hits inventory and the Last Receipt Date does not get updated, nor does it hit the IM_ItemTransactionHistory table either.
I tried several different ways to link the CI_Item table to the PO_ReceiptHistoryDetail and Header tables either within the same report or through a subreport to pull the following fields:
CI_Item.ItemCode
CI_Item.ItemCodeDesc
CI_Item.LastSoldDate
CI_Item.LastReceiptDate
PO_ReceiptHistHeader.ReceiptDate
Both instances run slower than slow and likely aren't great options IMO. It was also even suggested to me to try the vReceiptHistory view (that business insights explorer uses maybe?), but same results.
Other options suggested to me thus far is creating a UDF and importing the ReceiptDate into the UDF, custom programming, or create a script.
Does anyone else have any other thoughts on how this can be done more efficiently?
Thanks
------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------