Vendor ID, Last Update Date, Invoice Details - Query

[AppsDeveloper]

Could someone help me in writing the query to find the vendor ID, vendor number, vendor name, vendor site code, Created Date, Last Update Date, last invoice date, last payment date, total # of invoices and total $ amount of invoices for each supplier. All these should be group by the vendor ID. Means all the vendor ids in the report should be unique. Please help.

1) How do I find the last payment date

2) How do I find supplier_catagory?

3) As I have to use sum(invoice_amount), count(invoice_amount), max(payment_date) which point to 3 different tables, I am not sure how to use "group by" based on the Vendor_id alone while I select lot of fields from different table. Could you please help me with this?

Query I tried:
select
pv.vendor_id


,pv.segment1 "Vendor Number",
,pv.vendor_name "Vendor Name"
,pv.payment_method_lookup_code "Payment Method"
,pv.CREATION_DATE "CREATED DATE"
,pvs.vendor_site_code "Site Name"
,pvs.vendor_site_code_alt "Alternate Vendor Site Name"
,(SELECT TO_CHAR (TRUNC (a.INVOICE_DATE)) FROM from ap.ap_invoices_all a where a.vendor_id=pv.vendor_id) "INVOICE DATE"
,MAX(TO_CHAR (TRUNC (aipa.ACCOUNTING_DATE))) "LAST PAYMENT DATE"
,(select count(a.invoice_amount) from ap.ap_invoices_all a where a.vendor_id=pv.vendor_id) "TOTAL NUMBER OF INVOICES"
,(SELECT SUM(A.INVOICE_AMOUNT) from ap.ap_invoices_all a where a.vendor_id=pv.vendor_id GROUP BY A.VENDOR_ID) "TOTAL INVOICE AMOUNT"

from po_vendors pv
,po_vendor_sites_all pvs
,po_vendor_contacts pvc
,AP.AP_INVOICE_PAYMENTS_ALL aipa
,ap.ap_invoices_all aia


where pv.vendor_id = pvs.vendor_id(+)
and pvs.vendor_site_id = pvc.vendor_site_id(+)
and aipa.INVOICE_ID = aia.INVOICE_ID
and pv.vendor_id=aia.vendor_id

[June7]

Might have more success building a report and using report Grouping & Sorting with aggregate calcs instead of trying to accomplish all within one query, which is most likely not possible. A report will allow display of detail records and summary calculations in footer/header sections.