Report of Payments and AR by Sales GL Account

[kenaruj]

Accpac ERP 5.3A
Crystal 10

I need to create the following two reports...

First Report: A report of payments by Sales GL Account. This report is used to assess Credit Card bank charges to each area within our organization. Each GL account identifies a specific area. My plan is to associate each payment (PP, PY, UC, CN and applied CN) to the invoice it was applied to and the items within the invoice that the payment paid for (items "paid" from lowest, usually tax, to highest) to the items category, to the categories Sales GL account. I'm thinking I'll need to create an application (.NET) to process the payments before reporting the results because I need to take partial payments, overpayments and multiple payments into account and that may make for an extremely complicated Crystal Report. Thoughts? Ideas?

Second Report: A report of Aged AR by Customer by Sales GL Account. We need to report our total aging by area which again is represented within the GL account. I'm thinking that if I can process all open invoices and payments applied to those invoices (again, applying payments to items from low to high), I'll be left with what hasn't been paid for and to what Category -> Sales GL Account it belongs to. Thoughts? Idea?

As I understand it, when an invoice is posted, the total amount is posted to AR then when a payment is posted, it affects AR. So what I need to do in both instances is skip the general AR account and associate payments (or lack thereof in the case of Aged AR) directly with the items on the invoices.

Currently, I'm working within the following tables trying to figure this out:

AROBL (Open Documents)
AROBP (Document Payments)
ARIBH (Invoices)
ARIBD (Invoice Detail)

Advice?

Thanks.

 

[Rajender123]

Yes, using these table that report can be made, I also made these type of report from ACCPAC

 

[kenaruj]

How did you handle a Credit Note to a partially paid invoice? Or credit notes in general? If the item being credited has been previously paid for then the amount of the credit has to be backed out of the G/L account and possibly applied to another G/L account in the case of a partially paid invoice.

Advice?

 

[Jay Converse]

Use ARPJH and ARPJD for your source, those are the posting journals, and the last tables to be updated before GL batches are created