Hello there
As someone who's come across many stock reconciliations in the past, there are many things you have to be aware of.
For a start, it depends on what inventory valuation method you have and how the system handles revaluations (IB movements) - i.e. whether or not they are one per item / branch, or it has cascaded the changes across each lot/location.
Firstly, reconcile F41021 to F4111. R41530 gives you a valuation of your stock on-hand (F41021.LIPQOH) multiplied by the inventory costs at whatever cost level (F4101.IMCLEV) your stock is priced at in F4105 (F4105.COCSIN = 'I').
Then, reconcile F4111 with F0911. The standard reports assume that you are using F41112 (accumulated F4111, known as the As Of file), written to by R41542 and R41548. If you don't want to use the As Of facility, then there are some things that may help you:
1. Select only where F4111.ILIPCD <> 'X'. (This indicates an item ledger movement that has no financial impact, like a lot status change.)
2. Don't assume that you can match F4111 on-to-one with F0911, for the following reasons:
a) Sales orders that have been shipped but not yet processed by R42800 (Sales Update) will have a document number equal to the order number and a G/L date of zero. Exclude these by not selecting where F4111.ILIPCD = 'S'.
b) Your movements may be summarised in the F0911, in which case individual journal entry line numbers can't be matched; you may still be able to match at document number level, so your report should compare at that level.
c) If you run cycle billing, you cannot reconcile inventory using document numbers - because the financial inventory is relieved on the day of the shipment against document type JE, and this is never written back to the F4111.
d) If you run manufacturing, you have two major problems: the IC (completion) and IM (issue) movements are also marked ILIPCD = 'S' until you run R31802 (Manufacturing Accounting). And once you do, the program assigns a new document number for the F0911 and doesn't update the F4111 with this new number! The only way you can then reconcile is by using the sub-ledger number (F0911.GLSBL, with sub-ledger type 'W'), which contains a work order number.
e) Select only ledger type 'AA' from the F0911.
My main approach to reconciliation is therefore to:
a) Summarise to document type ONLY using SQL, e.g.
SELECT ILDCT, SUM(ILPAID) FROM F4111 WHERE ILDGL BETWEEN...
AND ILIPCD NOT IN ('X', 'S')
ORDER BY ILDCT
GROUP BY ILDCT
SELECT GLDCT, SUM(GLAA) FROM F0911 WHERE GLDGJ BETWEEN...
AND GLLT = 'AA' AND GLOBJ = xxxx
ORDER BY GLDCT
GROUP BY GLDCT
b) Rule out those document types that match exactly
c) For the remaining, summarise to document number and type, unless a work order
b) For work orders, summarise subledger
d) For JE movements in F0911, work out if they are cycle-billed or manual; if manual, exclude; if cycle-billed, match these against F4111.ILIPCD='S' and ILDCTO <> 'WO'
You gradually focus in on the problem areas this way.
I hope this is not too much information. I have written reports that do similar things to all this, it is possible, but not always at the detailed level you are looking for.
