2 Replies Latest reply on Dec 9, 2014 6:28 AM by Sanjay Desai EBS

    SQL of suppliers and supplier sites by last check date

    user6928506

      Dear All,

       

      I have a query to retrieve inactive suppliers by parameters and have a bad performance on below query. 

       

      Can  anyone advise to tune this query?  Thank in advance!

       

       

      select distinct v.vendor_id,

                 vs.vendor_site_id,

                 v.vendor_name as Supplier_Name,

                 v.segment1 as Supplier_Number,

                 vs.Vendor_site_code as Supplier_Site_Code,

                 ac.payment_method_code  as Payment_Method_Code,

                 max(ac.check_date) as last_pay_date

          FROM AP_SUPPLIERS v

              ,AP_SUPPLIER_SITES_ALL vs

              ,AP_CHECKS_ALL ac

          WHERE v.vendor_id = vs.vendor_id

            and ac.vendor_id = v.vendor_id

            and ac.vendor_site_id = vs.vendor_site_id

            and ac.status_lookup_code <> 'VOIDED'

            and v.vendor_type_lookup_code = 'VENDOR'

            and ac.amount <> 0

            and v.enabled_flag = 'Y' --Only Active Vendors

            and (v.end_date_active IS NULL OR v.end_date_active > trunc(SYSDATE))

            and (select max(ac1.check_date)

                from AP_CHECKS_ALL ac1

                where ac1.vendor_id = v.vendor_id

                and ac1.vendor_site_id = vs.vendor_site_id

                and ac1.status_lookup_code <> 'VOIDED'

                and ac1.amount <> 0) between '01-feb-2010' and '01-feb-2010'

           group by v.vendor_id,

                    vs.vendor_site_id,

                     v.vendor_name,

                     v.segment1,

                     vs.vendor_site_code,

                     ac.payment_method_code;

        • 1. Re: SQL of suppliers and supplier sites by last check date
          PranitSaha

          Try replacing AP_SUPPLIERS_V with PO_VENDORS or HZ_PARTIES

           

          Sent from my iPhone

          • 2. Re: SQL of suppliers and supplier sites by last check date
            Sanjay Desai EBS

            Pl. try the following SQL :

             

            select distinct v.vendor_id,

                       vs.vendor_site_id,

                       v.vendor_name as Supplier_Name,

                       v.segment1 as Supplier_Number,

                       vs.Vendor_site_code as Supplier_Site_Code,

                       ac.payment_method_code  as Payment_Method_Code,

                       max(ac.check_date) as last_pay_date

                FROM AP_SUPPLIERS v

                    ,AP_SUPPLIER_SITES_ALL vs

                    ,AP_CHECKS_ALL ac

                WHERE v.vendor_id = vs.vendor_id

                  and v.vendor_type_lookup_code = 'VENDOR'

                  and v.enabled_flag = 'Y' --Only Active Vendors

                  AND NVL(V.END_DATE_ACTIVE, SYSDATE + 1 ) > SYSDATE

            --      and (v.end_date_active IS NULL OR v.end_date_active > trunc(SYSDATE))

                 and ac.vendor_id = v.vendor_id

                  and ac.vendor_site_id = vs.vendor_site_id

                  and ac.status_lookup_code <> 'VOIDED'

                  and ac.amount <> 0

                  and (select max(ac1.check_date)

                      from AP_CHECKS_ALL ac1

                      where ac1.vendor_id = v.vendor_id

                      and ac1.vendor_site_id = vs.vendor_site_id

                      and ac1.status_lookup_code <> 'VOIDED'

                      and ac1.amount <> 0) between '01-feb-2010' and '01-feb-2010'

                 group by v.vendor_id,

                          vs.vendor_site_id,

                           v.vendor_name,

                           v.segment1,

                           vs.vendor_site_code,

                           ac.payment_method_code;

             

            HTH

            sd