AR Invoice & AR CreditMemo Report

[Former Member]

Hi,

I would to generate a report by using Crystal Report the data is is from AR Invoice (OINV, INV1)with a condition i need to plus back stock/item Qty which after did AR CreditMemo (ORIN, RIN)....

What is the step i need to do?

Thanks

Danny

[Former Member]

I using Crystal Report, First i create a view (AR Invoice & AR CreditMemo) then using formula if objtype is 15 then *-1 so that my amount will minus out the AR CreditMemo.

[former_member217067]

Hi Danny,

The OMTH table does not have to be filled there is only information about reconciliation. Please note that documents can be linked with functionalities (base u2013 target documents or internally reconciled). In a reporting tool you need to get correct data from a SQL query therefore I suggest to first creating a SQL query with correct results.

I suggest something like this:

SELECT sum(required quantity)

(

SELECT all invoices

UNION ALL

SELECT all linked credit memos based on invoices

UNION ALL

SELECT all linked credit memos internally reconciled

)

Regards,

Pavol

[Former Member]

Hi Experts,

In order to get proper stock qty and amount need to plus back AR creditMemo figure.

thanks

Danny

[former_member217067]

Hi Danny,

If you need to know relation between base and target documents then in marketing line tables are columns named Base with document number, line and type. Please note that the documents can be reconciled also with internal reconciliation and then the relation is kept in reconciliation tables OITR.

Best regards,

Pavol

[Former Member]

Hi Pavol,

I can't find the table "OITR", it is not in db.

Danny

[former_member217067]

Hi Danny,

OITR table is in 2007 version. You can see the data tables in status bar by activating View -> System Information. In version 2005 the internal reconciliations are stored in OMTH table.

Regards,

Pavol

[Former Member]

Hi Pavol,

I checked already the OMTH table, it's empty.

Actually, i created a view with OINV, INV1, ORIN, RIN1, but i don't know to show a proper way in crystal report. (Don't how to plus back the stock qty)