ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Overdue COD Invoices
http://www.istechforum.com/YaBB.pl?num=1351881200

Message started by BethanyW on 11/02/12 at 11:33:20

Title: Overdue COD Invoices
Post by BethanyW on 11/02/12 at 11:33:20

The Print Aging screen (AR-F) doesn't quite do what we would like it to (show all past due COD invoices), so I've been trying to create a Crystal Report to fill in the gap. Unfortunately I can't figure out how in the world to connect the correct tables to get what I want. I've spent hours examining the tables I think I need for a way to link them and searching the source code (bkarf.src) for a hint, but keep coming up empty-handed.

These are the tables I think I need, though perhaps not all of them:
- BKARHINV
- BKARCHKF
- BKARINVT
- BKART

I can link the last 2 together, but they don't contain enough information. It looked like I could link the first 2 together, but the invoice number is a number field in one and a string field in the other.

I know this must be possible given what AR-F can do, but need guidance to link the necessary tables. Can anyone help?

Title: Re: Overdue COD Invoices
Post by Kelloggs on 11/02/12 at 12:07:36

If you don't mind please clarify something for me.

If "COD" stands for Cash On Delivery, how come an invoice can be past due?

:-[

Kelloggs

Title: Re: Overdue COD Invoices
Post by BethanyW on 11/02/12 at 12:19:51

Kelloggs,
Sorry for the confusion. We set up our COD terms to be due 10 days after shipment.

This issue came up because we recently found out we never got paid for a shipment we sent out over a year ago. So now we would like to check once in awhile to make sure that there are no outstanding (>10 days) COD invoices. Does that help?

Title: Re: Overdue COD Invoices
Post by Kelloggs on 11/02/12 at 12:26:12

Ohh I see. We have our own AR Aging Report (MS Access)
I hope it helps. It can be change to show > 10

Here is the script

SELECT
BKAR_INVT_CODE, BKAR_INVT_DATE, BKAR_INVT_NUM,
BKAR_INVT_AMT, BKAR_INVT_TYPE, BKAR_INVT_AMTRM, BKAR_INVT_MCCOD,
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) <= 30, BKAR_INVT_AMTRM, 0 ), 0) 'Days_Current',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 30 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 60, BKAR_INVT_AMTRM, 0 ),0 ) 'Days_30',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 60 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 90, BKAR_INVT_AMTRM, 0 ),0 ) 'Days_60',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 90 AND (CURDATE ( ) - BKAR_INVT_DATE) <= 120, BKAR_INVT_AMTRM, 0 ),0 ) 'Days_90',
IF(BKAR_INVT_TYPE = 'I',IF ((CURDATE ( ) - BKAR_INVT_DATE) > 120, BKAR_INVT_AMTRM, 0 ), 0 ) 'Days_120',
(CURDATE ( ) - BKAR_INVT_DATE) AS DD,
BKARHINV.BKAR_INV_CUSNME as CUSNME, BKARHINV.BKAR_INV_CUSORD as CUSORD
FROM BKARINVT LEFT JOIN BKARHINV ON BKARINVT.BKAR_INVT_NUM = BKARHINV.BKAR_INV_NUM
WHERE BKAR_INVT_AMTRM <> 0;

;D

Kelloggs

Title: Re: Overdue COD Invoices
Post by BethanyW on 11/02/12 at 12:34:37

Oh my, I have no idea how I missed that those 2 fields are the same! Thank you so much!!!