Who's On?

	Membership:
	Latest: Pat
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5296

	People Online:
	Visitors: 520
	Members: 0
	Total: 520

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244

Relate arapplied to oeoinvoice

4 Replies

0 Subscribed to this topic

22 Subscribed to this forum

Sort:

Author

Messages

TracyO

Veteran Member

Posts: 97

3/11/2009 6:43 PM

I have a Crystal report using OeInvoice (this report also includes oeinvcline & oeinvcmisc) I need to add the date this was paid in AR and the due date. I think I need to get from the arapplied table to the oeinvoice table, but I need some common link in between the two.

The oeinvoice file has the invoice nubmer in two seperate fileds and the arapplied has them comgined in one field so I can not link on that common field.

Has anyone linked these tables or know if I am not looking at the correct AR table to get the date applied & due date?

THanks

John Henley

Posts: 3355

3/11/2009 7:18 PM

You will probably want to get this from AROIHDR rather than ARAPPLIED. Here's a query I use (this is for SQL Server):
SELECT
OEI.BATCH_NBR,
OEI.CUSTOMER,
OEI.INVC_PREFIX,
OEI.INVC_NUMBER,
OEI.STATUS,
OEI.INVOICE_DATE,
ARO.DUE_DATE,
ARO.TRANS_TYPE,
ARO.INVOICE,
SUM(CASE WHEN ARO.TRANS_TYPE = 'C' THEN -1 ELSE 1 END * ARO.TRAN_AMT - ARO.APPLD_AMT - ARO.ADJ_AMT) AS OPEN_AMT
FROM OEINVOICE OEI
LEFT OUTER JOIN AROIHDR ARH
ON (ARH.COMPANY = OEI.COMPANY)
AND (ARH.CUSTOMER = OEI.CUSTOMER)
AND (ARH.TRANS_TYPE IN ('C','I'))
AND (ARH.INVOICE = OEI.INVC_PREFIX + REPLICATE('0',8-LEN(RTRIM(OEI.INVC_NUMBER))) + RTRIM(OEI.INVC_NUMBER))
LEFT OUTER JOIN AROITEMS ARO
ON (ARO.COMPANY = ARH.COMPANY)
AND (ARO.TRANS_TYPE = ARH.ALT_TYPE)
AND (ARO.INVOICE = ARH.INVOICE)
WHERE (OEI.COMPANY = 4321)
AND (OEI.STATUS = 9)
AND (ARH.STATUS > 0)
AND (ARO.STATUS > 0)
GROUP BY OEI.BATCH_NBR, OEI.CUSTOMER, OEI.INVC_PREFIX, OEI.INVC_NUMBER, OEI.STATUS,OEI.INVOICE_DATE,ARO.DUE_DATE,ARO.TRANS_TYPE,ARO.INVOICE
ORDER BY OEI.CUSTOMER, OEI.INVC_PREFIX, OEI.INVC_NUMBER

TracyO

Veteran Member

Posts: 97

3/11/2009 7:47 PM

Thanks John
I will try that.

TracyO

Veteran Member

Posts: 97

3/12/2009 1:33 PM

John
Are you running agains 9.0? We are currently still on 8.1 and I dont' see the two fields that I really need Due Date & Appld amt on the AROIHDR table, but I do see them on the ARAPPLIED table.

John Henley

Posts: 3355

3/12/2009 1:39 PM

They are on AROITEMS, not AROIHDR. No need to go to ARAPPLIED