ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> RTM Reports & Editing >> Invoices not paid
http://www.istechforum.com/YaBB.pl?num=1451429960

Message started by Prier Products on 12/29/15 at 15:59:20

Title: Invoices not paid
Post by Prier Products on 12/29/15 at 15:59:20

Can anyone help me with the table name that would have the invoices that are still open? I am trying to get something like the AR Aging Report.

Thanks

Title: Re: Invoices not paid
Post by Lynn_Pantic on 12/29/15 at 20:42:08

BKARINVT where BKAR_INVT_AMTRM>0 would be all open AR invoices

Title: Re: Invoices not paid
Post by Prier Products on 12/30/15 at 06:20:03

Thank you Lynn.

Title: Re: Invoices not paid
Post by Kelloggs on 12/30/15 at 08:59:01

try this .....

SELECT
BKAR_INVT_CODE,
BKAR_INVT_DATE,
BKAR_INVT_NUM,
BKAR_INVT_AMT,
BKAR_INVT_TYPE,
BKAR_INVT_AMTRM,
BKAR_INVT_MCCOD,
BKARHINV.BKAR_INV_CUSORD,
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) <= 30, BKAR_INVT_AMTRM, 0 ), 0) 'D_Current',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 30 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 60, BKAR_INVT_AMTRM, 0 ),0 ) 'D_30',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 60 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 90, BKAR_INVT_AMTRM, 0 ),0 ) 'D_60',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 90 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 120, BKAR_INVT_AMTRM, 0 ),0 ) 'D_90',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 120, BKAR_INVT_AMTRM, 0 ), 0 ) 'D_120',
(CURDATE ( ) - BKAR_INVT_DATE) AS Total_Days_Late
FROM BKARINVT LEFT JOIN BKARHINV ON BKARINVT.BKAR_INVT_NUM = BKARHINV.BKAR_INV_NUM WHERE BKAR_INVT_AMTRM <> 0;

Have fun,

:P

Kelloggs

Title: Re: Invoices not paid
Post by Prier Products on 12/30/15 at 09:35:31

Thanks!