x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 119 subscribers
  • Views 1479 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
757
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
All lades

How to match Inventory (trial balance) amount with Inventory Valuation by Category - SQL statement

Posted By

For the past weeks, I have been working on generating a report that will provide the inventory amount per Category and should match it with the Inventory balance per Trial Balance or G/L or Financial reporter.

Does anybody built a report on this using SQL or Financial Reporter or Crystal Report?

If I sum up the TRANSCOST from the ICIVAL table, it matches the Actual Cost from I/C Item Valuation (ICVALU05) report which I believe came with Sage 300. However, if i generate a report based on Category from ICIVAL table, the 'Actual Cost' doesn't match with my G/L balance. Possibly I am pulling numbers from the wrong.

Can anyone enlighten me how to calculate the inventory per Category and match it with the inventory in the balance sheet?  A SQL statement would be really helpful so I can track easily which tables are linked to come up with the amount.

Thanks for the help in advance.

  • 0

    Hi, to get inventory by category, use table ICILOC (Location Details) instead of ICIVAL. ICILOC will give you current stock valuation. Then compare with the trial balance. ICILOC and ICIVAL should match, but I have seen cases where it does not. ICIVAL has al historical transactions, so if you want to get stock valuation for a past date, you need to use it.

  • 0 in reply to Mariano Catalano

    Hi Mariano.

    My result still does not match with Trial Balance or G/L or Financial reporter amounts.

    Below is my SQL statement.

    SELECT A.CATEGORY, B.[DESC] AS [DESCRIPTION], SUM(A.TRANSCOST) AS [ACTUAL]
    FROM ICIVAL AS A INNER JOIN ICCATG AS B ON A.CATEGORY = B.CATEGORY
    WHERE TRANSDATE <= @ENDATE AND
    B.CATEGORY IN ( 'BANDSW', 'SAWMLL', 'TERKN')
    GROUP BY A.CATEGORY, B.[DESC]
    ORDER BY A.CATEGORY

    Would greatly appreciate if someone could help me fix my SQL statement.

    Thanks.

*Community Hub is the new name for Sage City