1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Vendor ID, Last Update Date, Invoice Details - Query

Discussion in 'Oracle Apps Technical' started by Apps Developer, Mar 14, 2012.

  1. Apps Developer

    Apps Developer Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    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?
     
  2. Apps Developer

    Apps Developer Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    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
     
  3. Apps Developer

    Apps Developer Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Query to find vendor name, create date, number of invoices .

    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
     
  4. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Hello,

    I remember seeing your post in another forum a while ago? Are you testing the waters here? :)
    cheers
    Ramji