Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Relate arapplied to oeoinvoice

 4 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
TracyO
Veteran Member
Posts: 97
Veteran Member
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: 3353
    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
      Thanks for using the LawsonGuru.com forums!
      John
      TracyO
      Veteran Member
      Posts: 97
      Veteran Member
      3/11/2009 7:47 PM
        Thanks John
        I will try that.
        TracyO
        Veteran Member
        Posts: 97
        Veteran Member
        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: 3353
          3/12/2009 1:39 PM
            They are on AROITEMS, not AROIHDR. No need to go to ARAPPLIED
            Thanks for using the LawsonGuru.com forums!
            John