I have been trying to figure out the best way to get a report that would describe something like Item “Usage” by Customer.  The trick with the report is that not only do we have to recognize items that are SOLD to specific customers, but we must contemplate that the same item may also be a component in one or more bill of materials.  When these bills are produced they consume the component item.  I need some way to show this usage as well by customer. 

The IM_ItemTransactionHistory file does contain all transactions.  For all sales transactions it also contains the customer number for the customer that purchased the item.  For items consumed in production however, no customer number is recorded.  How can I tie this IM_ItemTransactionHistory record to something like the Work Order Number or the Finished Good that was produced.  If I can get to what item was produced in connection with the work issue transaction I can determine what customer it was for.  We have an enhancement in Sage 100 that allows us to tie all of our bill of materials to the customer who uses that bill.  If I can get the Work Order Number or the Bill Number of the item being made I would have enough.

I see that I have these field available in IM_ItemTransactionHistory: 

   5  EntryNo . . . . . . . . . . .    10 SYY  Entry Number. . . . . . . . . .

                                                Notes: (IM5$(27,10)}

   6  SequenceNo. . . . . . . . . .     6 SNY  Sequence Number . . . . . . . .

                                                FmtType:ZEROFILL Notes:

                                                (IM5$(37,3)}

  20  SourceJournal . . . . . . . .     2 SNN  Source Journal. . . . . . . . .SOURCEJOURNAL

  21  JournalNoGLBatchNo. . . . . .     6 SNN  Journal Number/GL Batch Number.

Is there a way using one or more of these fields above to tie to a work order number that was being used for the material issue or the SKU for the Bill of Material that was being produced on the work order associated with the bill of material? 

I see that in WOO we have these files.  Is there a way to tie this to IM_ItemTransactionHistory?

Can anyone suggest a way that I might be able to do this?

Does anyone know when Work Order finally moves up the new framework if the IM_ItemTransactionHistory file will contain a link to the work order number?

Thanks,

Tyler Christensen