Hi all,
I’m trying to write a generic inquiry to calculate the total purchase $ for an item by warehouse by period. My first thoughts were to go at it using INTran and just check the Tran Type.
Is this the correct route or is there a better source? Also, what transaction types do I need to check for in order to ensure i’m aggregating ONLY vendor purchase $?
thank you!
Joseph
Best answer by jhalling52
Hi
I believe the INTran table should meet your requirements here.
If you’re looking to isolate only IN Receipts that originate from a Purchase Receipt, you can add a condition where POReceiptNbr is not empty.
To properly account for Purchase Returns, consider adding a calculated field that multiplies Tran Cost by InvtMult. This will ensure returns are reflected as negative values. Be sure to apply the SUM aggregate function to this calculation.
You’ll also want to include groupings by FinPeriod and SiteID to organize the results appropriately.
This example will include landed costs. If you don’t want landed costs included, you could add a condition so the Reason Code does not equal the Landed Cost Reason Code.
Hi
I believe the INTran table should meet your requirements here.
If you’re looking to isolate only IN Receipts that originate from a Purchase Receipt, you can add a condition where POReceiptNbr is not empty.
To properly account for Purchase Returns, consider adding a calculated field that multiplies Tran Cost by InvtMult. This will ensure returns are reflected as negative values. Be sure to apply the SUM aggregate function to this calculation.
You’ll also want to include groupings by FinPeriod and SiteID to organize the results appropriately.
This example will include landed costs. If you don’t want landed costs included, you could add a condition so the Reason Code does not equal the Landed Cost Reason Code.
The Landed Cost Reason Code is defined on the Landed Cost Codes screen (IN202000). Other Reason Codes are configured on the Purchase Order and Inventory Preferences screens.
Thinking about it further, an alternative to filtering by Reason Code would be to add a condition where the Tran Type is not equal to “Adjustment.” This approach is more flexible, as it allows the GI to be used across any site without needing to identify and maintain specific Reason Codes for each one.
I believe the Receipt Cost field on INItemCostHist represents the total cost of inventory coming into stock, regardless of the source. This includes both Purchase Receipts (from POs) and Direct Inventory Receipts (entered in IN301000).
If you are fine referencing all incoming cost, regardless of whether created through a PO, then this field works.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
