Sage 300

hi all - What is the best way to reconcile OE prepayments? You can have prepayments on orders, shipments and invoices in o/e. Have a customer that does all three and now would like a report showing open document prepayments in summary. I can see the details in OEPPRE and AROBP, but getting the 'stack' to summarize is proving to be a challenge. Any ideas?

I would create a view that consists of a union query of the fields you need from AROBP & OEPPRE with just the fields you need with matching aliases. something like: SELECT IDCUST AS Cust, IDINVC AS Doc, IDRMIT AS [Check], DATEBUS AS PayDate, AMTPAYMHC AS Amount FROM dbo.AROBP UNION SELECT dbo.OEORDH.CUSTOMER AS Cust, dbo.OEPPRE.DOCNUMBER AS Doc, dbo.OEPPRE.CHECKNUM AS [Check], dbo.OEPPRE.CHECKDATE AS PayDate, dbo.OEPPRE.PAYMENT AS Amount FROM dbo.OEPPRE INNER JOIN dbo.OEORDH ON dbo.OEPPRE.DOCNUMBER = dbo.OEORDH.ORDNUMBER WHERE (dbo.OEPPRE.APPLYTO = 4) -- for orders With more UNIONS for shipments starting with OEPPRE but using the appropriate tables instead of OEORDH

Thank you Phil i'll give this a whirl! Was hoping I was missing something simple but this confirms its a few layers deep.