Welcome, Guest. Please Login.
03/28/24 at 11:26:59
News:
Home Help Search Login


Pages: 1
Send Topic Print
Overdue COD Invoices (Read 2026 times)
BethanyW
Browser
*




Posts: 46
Overdue COD Invoices
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?
Back to top
 
 

Bethany Waldmann
Vermont Hardwoods
Chester, VT

Evo-Erp 2013.1, 5 user
IST Update: 7/26/13 SP1
Pervasive 11
Win2003 SBSR2 / XP Pro SP3 / Win7 x64 SP1
Crystal Reports v11
WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Overdue COD Invoices
Reply #1 - 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?
 
 embarrassed
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
BethanyW
Browser
*




Posts: 46
Re: Overdue COD Invoices
Reply #2 - 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?
Back to top
 
 

Bethany Waldmann
Vermont Hardwoods
Chester, VT

Evo-Erp 2013.1, 5 user
IST Update: 7/26/13 SP1
Pervasive 11
Win2003 SBSR2 / XP Pro SP3 / Win7 x64 SP1
Crystal Reports v11
WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: Overdue COD Invoices
Reply #3 - 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;  
 
 Grin
 
Kelloggs
 
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
BethanyW
Browser
*




Posts: 46
Re: Overdue COD Invoices
Reply #4 - 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!!!
Back to top
 
 

Bethany Waldmann
Vermont Hardwoods
Chester, VT

Evo-Erp 2013.1, 5 user
IST Update: 7/26/13 SP1
Pervasive 11
Win2003 SBSR2 / XP Pro SP3 / Win7 x64 SP1
Crystal Reports v11
WWW   IP Logged
Pages: 1
Send Topic Print