Wednesday 21 December 2016

AP Supplier Banks Payments Query

SELECT   pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code,
         hou.NAME org_name, pv.vendor_id supplier_id,
         pv.segment1 supplier_number, plc.description supplier_type,
         pv.vendor_name supplier_name,
         (CASE
             WHEN TRUNC (NVL (pv.end_date_active, SYSDATE + 1)) >=
                                                               TRUNC (SYSDATE)
                THEN 'Active'
             ELSE 'Inactive'
          END
         ) status,
         pvs.vendor_site_code site_name,
         (SELECT TO_CHAR (MAX (check_date), 'DD-MON-RRRR')
            FROM apps.ap_checks
           WHERE vendor_id = pv.vendor_id
             AND vendor_site_id = pvs.vendor_site_id) last_payment_date,
         pv.vat_registration_num tax_registration_number,
         fu.user_name updated_by,
         TO_CHAR (pvs.last_update_date, 'DD-MON-RRRR') update_date,
         pvs.address_line1 site_address1, pvs.address_line2 site_address2,
         pvs.address_line3 site_address3,
         pvs.address_lines_alt alternate_address, pvs.city city,
         pvs.state state, pvs.zip postal_code,
         pvs.area_code || ' ' || pvs.phone telephone,
         pvs.fax_area_code || ' ' || pvs.fax fax,
         pvs.email_address site_email, pvs.remittance_email remittance_email,
         pvs.supplier_notif_method notify_method, pvs.attribute13 remit_type,
         apt.NAME payment_terms, pvs.payment_currency_code currency,
         ieb.bank_account_name, ieb.bank_account_number bank_account_num,
         ieb.branch_number bank_branch_name,
         TO_CHAR (ipi.start_date, 'DD-MON-RRRR') effective_date,
         pvs.hold_unmatched_invoices_flag hold_unmatched_invoices,
         pvs.hold_future_payments_flag hold_unvalidated_invoices,
         pvs.hold_all_payments_flag hold_all_payments
    FROM apps.ap_suppliers pv,
         apps.ap_supplier_sites_all pvs,
         apps.hz_parties party_supp,
         apps.hz_party_sites site_supp,
         applsys.fnd_user fu,
         apps.hr_operating_units hou,
         apps.po_lookup_codes plc,
         apps.ap_lookup_codes alc,
         apps.ap_terms apt,
         iby.iby_external_payees_all iep,
         iby.iby_pmt_instr_uses_all ipi,
         apps.iby_ext_bank_accounts_v ieb
   WHERE pv.vendor_id = pvs.vendor_id
     AND party_supp.party_id = pv.party_id
     AND party_supp.party_id = site_supp.party_id
     AND site_supp.party_site_id = pvs.party_site_id
     AND pvs.last_updated_by = fu.user_id(+)
     AND pvs.org_id = hou.organization_id
     AND pv.vendor_type_lookup_code = plc.lookup_code(+)
     AND plc.lookup_type(+) = 'VENDOR TYPE'
     AND pvs.terms_id = apt.term_id
     AND iep.supplier_site_id = pvs.vendor_site_id
     AND iep.payee_party_id = pv.party_id
     AND iep.default_payment_method_code = alc.lookup_code
     AND alc.lookup_type = 'PAYMENT METHOD'
     AND iep.payee_party_id = party_supp.party_id
     AND iep.party_site_id = site_supp.party_site_id
     AND iep.ext_payee_id = ipi.ext_pmt_party_id(+)
     AND ipi.instrument_id = ieb.ext_bank_account_id(+)
ORDER BY pv.vendor_name, pvs.vendor_site_code;

1 comment:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete