• Search Forums
    Advanced Search
Prev
Next
Forums Financial Management Lawson S3 Financials

AP vendors report

 4 Replies
 1 Subscribed to this topic
 45 Subscribed to this forum
Sort:
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member
New Poster
New Poster
Congrats on posting!
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
Deleted User
New Member
Posts: 0
New 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
New Poster
New Poster
Congrats on posting!
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.
Deleted User
New Member
Posts: 0
New 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
New Poster
New Poster
Congrats on posting!
7/3/2015 5:45 PM
Thank you soooo much TracyO!!