Sage 100

Trying to create a Crystal report for a client that includes the GL account hit during Inventory Transactions and PO Receipts. However, the GL acct is not retained in the Trans Detail file. Any suggestions? 4.3 SU6

Is the account key there? If so, you have to pull in the GL_account table linking the account key. then pull the GL account over.

Nope, I don't see any GL related data in the IM5_TransactionDetail file.

Wow, that's going to be a tough one. You can reference the product line file for the DEFAULT accounts but those can be changed in some cases (e.g. IM issues). Good luck with this!

Why do they want it? What do they need?

New CFO wants to dissect Inventory receipt & transaction information. Included in that is a breakdown by the GL acct impacted by the transaction. And yes, the problem is that they can select different GL accts than the defaults from the Prod Ln.

Maybe you can join the IMA ProductLine and retrieve the GL Acct Key from the product line for the item number. My very 1st scripting project was for someone who noticed AR Invoice History Detail doesn't store the Inventory acct key except for misc charges and items. I created 2 UDFs in SO Invc DE one for Inventory Acct Key the other for Inventory Acct No and flowed it thru to history. The script's purpose was to retrieve the Acct Key from the product line and Acct No from the GL and stuff them into the UDFs.

Oh I just noticed you said they select different GL accts than the defaults from the product line.

Won't help for existing records, but... can you create a UDF in IM5 to capture the account key?

I think that's about all I can do Mark. Depends on how desperately he needs the account information.

Is a UDF even possible for this table in v4.3? You may be recommending an upgrade in the not too distant future for them.............

Good point @JeffSchwenk . That entity is unavailable in 4.3. Will need to upgrade.

I am trying something similar in 4.4 but not having any luck. Cost variances on items received into inventory so we can then cross-check if those variances have been flowing through COGS and what that looks like this year. The IM_TransactionHistory does not store PV transactions, I thought of using the GL_DetailPosting and link by receipt number back to PO_HistoryDetail but if I select source journal of PV, the receipt number is blank. I thought of using PO_HistoryDetail and CI_Item standard cost but they constantly change the standard cost. Are the PV transactions stored anywhere?

Can we assume only sales and PO receipt transactions? With that limitation, you could create a sub report for each IM5 that looks up the AR_InvoiceDetailHistory line for the transaction. The actual GL accounts used are saved there. Would involve one subreport for sales and another for purchases....

To get the variances @LisaDion I'm guessing you'd have to use both the AP_InvoiceHistoryDetail and the PO_ReceiptHistoryDetail tables to compare receipt values vs. invoiced values.

@RandyMarion has a good point. A possible limitation of connecting IM5 to AR_InvoiceHistoryDetail is that if there are multiple instances of an item on the invoice, there's no line key to reference the correct line.

@MarkKotyla raises an interesting point... especially if the duplicate items on the invoice in history happen to have different GL accounts. If not, could probably get away with with just summing the multiple items up

I thought in AR_InvoiceDetailHistory the GL accounts were only populated for misc charges, misc items, and special items but not regular inventory items.

It definitely stores the accounts in the AR_InvoiceHistoryDetail for regular items and misc items/charges and special items. The trick is, as Randy said, if they change the account numbers on the same item on the same invoice.

Sorry I was thinking of the Inventory Acct Key stored in there, as opposed to the Sales Acct Key and COGS Acct Key.

@Therese Logeais thanks for the suggestion. The client uses standard cost and I am also trying to caputre by item number the variance when the item is received.