Tuesday, May 13, 2014

Query to Display Suppliers and Sites Banks Details

SELECT asp.vendor_name
      ,assa.vendor_site_code
      ,asp.pay_group_lookup_code vendor_pay_group
      ,assa.pay_group_lookup_code site_pay_group
      , asp.vat_code vendor_vat_code
      ,assa.vat_code site_vat_code
      ,asp.vat_registration_num vendor_reg_no
      ,assa.vat_registration_num site_reg_no
      ,assa.org_id
      ,assa.inactive_date
      ,at.name vendor_terms
      ,at2.name site_terms
      ,asp.payment_method_lookup_code supplier_pay_method
      ,assa.payment_method_lookup_code site_pay_method
      ,ieba.bank_account_name vendor_bank_account_name
      ,ieba.masked_iban AS vendor_iban
      ,ieba.currency_code vendor_currency_code
      ,ipiua.order_of_preference vendor_order_of_preference
      ,ipiua.start_date vendor_start_date
      ,ipiua.end_date vendor_end_date
      ,fc.name AS currency_name
      ,hp.party_name AS vendor_bank_name
      ,ieba.masked_bank_account_num AS vendor_bank_account_number
      ,cbbv.bank_branch_name vendor_bank_branch_name
      ,cbbv.branch_number AS vendor_branch_name
      ,hop.bank_or_branch_number AS vendor_bank_number
      ,cbbv.eft_swift_code AS vendor_eft_swift_code
      ,ieba.bank_account_type as vendor_bank_account_type
  FROM ap_suppliers asp
      ,ap_supplier_sites_all assa
      ,ap_terms at
      ,ap_terms at2
      ,iby_external_payees_all iepa
      ,iby_pmt_instr_uses_all ipiua
      ,iby_ext_bank_accounts ieba
      ,fnd_currencies_vl fc
      ,hz_parties hp
      ,ce_bank_branches_v cbbv
      ,hz_organization_profiles hop
 WHERE 1=1
   AND asp.vendor_id=assa.vendor_id
   AND at.term_id=asp.terms_id
   AND at2.term_id(+)=assa.terms_id
   AND asp.vendor_name=nvl(:p_vend_name,asp.vendor_name)
   AND iepa.payee_party_id=asp.party_id
   AND iepa.party_site_id=assa.party_site_id
   AND iepa.org_id=assa.org_id
   AND iepa.payment_function = 'PAYABLES_DISB'
   AND iepa.supplier_site_id=assa.vendor_site_id
   AND iepa.ext_payee_id = ipiua.ext_pmt_party_id(+)
   AND ipiua.instrument_type (+)= 'BANKACCOUNT'
   AND ieba.ext_bank_account_id(+)=ipiua.instrument_id
   AND fc.currency_code(+) = ieba.currency_code
   AND hp.party_id(+)=ieba.bank_id
   AND ieba.branch_id=cbbv.branch_party_id(+)
   AND ieba.bank_id=hop.party_id(+)
   AND SYSDATE BETWEEN NVL (ieba.start_date, SYSDATE)
                                 AND NVL (ieba.end_date, SYSDATE)
                 AND SYSDATE BETWEEN NVL (ipiua.start_date, SYSDATE)
                                 AND NVL (ipiua.end_date, SYSDATE)
  AND SYSDATE BETWEEN TRUNC(hop.effective_start_date(+))
  AND NVL(TRUNC(hop.effective_end_date(+)),SYSDATE + 1)
;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect