Prev
Next
Forums Financial Management Lawson S3 Financials

AP vendors report

 4 Replies
 1 Subscribed to this topic
 43 Subscribed to this forum
Sort:
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member
6/30/2015 6:52 PM
    I am trying to create a list of vendors including name, tax id, diverse code, phone number, class, and address. I joined the APVENMAST AND APVENADDR but I am getting multiple address per vendor. I think I'd like to get the maximum date or most current vendor's address from the file and I don't know how to. Any help would be greatly appreciated.
    Example:
    Vendor 1 MED PO box 1235 1/18/11
    Vendor 1 MED PO box 3953 3/26/12
    TracyO
    Veteran Member
    Posts: 97
    Veteran Member
    6/30/2015 7:26 PM
      I have a similar report but also included the APAUDIT table. I then added a group on APVENMAST.VENDOR and did a secondary sort on APAUDIT.TRANS_DATE. Then suppress the detail section and print the Group header or footer whichever on you put your fields on.
      Chesca
      Veteran Member
      Posts: 490
      Veteran Member
      6/30/2015 8:25 PM
        Would you be willing to share your SQL or report via email? We have to provide the state with this report by end of day tomorrow and my users did not let me know they needed this until this morning.
        TracyO
        Veteran Member
        Posts: 97
        Veteran Member
        7/1/2015 11:23 AM

          Here is the sql

          SELECT "apvenmast"."VENDOR", "apvenmast"."VEN_CLASS", "apvenmast"."VENDOR_VNAME", "apvenaddr"."ADDR1", "apvenaddr"."ADDR2", "apvenaddr"."CITY_ADDR5", "apvenaddr"."STATE_PROV", "apvenaddr"."POSTAL_CODE", "apvenmast"."TAX_ID", "APAUDIT"."FIELD_ID", "APAUDIT"."TRANS_DATE", "apvenmast"."VENDOR_STATUS"
          FROM ("LSFPROD_PROD9"."APVENMAST" "apvenmast" INNER JOIN "LSFPROD_PROD9"."APVENADDR" "apvenaddr" ON ("apvenmast"."VENDOR_GROUP"="apvenaddr"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="apvenaddr"."VENDOR")) LEFT OUTER JOIN "LSFPROD_PROD9"."APAUDIT" "APAUDIT" ON ("apvenmast"."VENDOR_GROUP"="APAUDIT"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="APAUDIT"."VENDOR")
          WHERE "apvenmast"."VENDOR_STATUS"='A'
          ORDER BY "apvenmast"."VENDOR", "APAUDIT"."TRANS_DATE"


          Chesca
          Veteran Member
          Posts: 490
          Veteran Member
          7/3/2015 5:45 PM
            Thank you soooo much TracyO!!