How to Force-Logout Idle or Stuck Oracle EBS Users With One PL/SQL Package (Free Script Inside)

  How to Force-Logout Idle or Stuck Oracle EBS Users With One PL/SQL Package (Free Script Inside) If you've ever administered Oracle E-...

Showing posts with label How to find Query that retrieves bank account details at the Supplier Level for Release R12. Show all posts
Showing posts with label How to find Query that retrieves bank account details at the Supplier Level for Release R12. Show all posts

Tuesday, 14 July 2015

How to find Query that retrieves bank account details at the Supplier Level for Release R12

SELECT ieba.ext_bank_account_id
     , ieba.country_code
     , ieba.branch_id
     , ieba.bank_id
     , ieba.bank_account_num
     , ieba.masked_bank_account_num
     , ieba.ba_mask_setting
     , ieba.ba_unmask_length
     , ieba.currency_code
     , ieba.iban
     , ieba.iban_hash1
     , ieba.iban_hash2
     , ieba.masked_iban
     , ieba.check_digits
     , ieba.bank_account_type
     , ieba.account_classification
     , ieba.account_suffix
     , ieba.agency_location_code
     , ieba.payment_factor_flag
     , ieba.foreign_payment_use_flag
     , ieba.exchange_rate_agreement_num
     , ieba.exchange_rate_agreement_type
     , ieba.exchange_rate
     , ieba.hedging_contract_reference
     , ieba.secondary_account_reference
     , ieba.ba_num_sec_segment_id
     , ieba.encrypted
     , ieba.iban_sec_segment_id
     , ieba.attribute_category
     , ieba.attribute1
     , ieba.attribute2
     , ieba.attribute3
     , ieba.attribute4
     , ieba.attribute5
     , ieba.attribute6
     , ieba.attribute7
     , ieba.attribute8
     , ieba.attribute9
     , ieba.attribute10
     , ieba.attribute11
     , ieba.attribute12
     , ieba.attribute13
     , ieba.attribute14
     , ieba.attribute15
     , ieba.request_id
     , ieba.program_application_id
     , ieba.program_id
     , ieba.program_update_date
     , ieba.start_date
     , ieba.end_date
     , ieba.created_by
     , ieba.creation_date
     , ieba.last_updated_by
     , ieba.last_update_date
     , ieba.last_update_login
     , ieba.object_version_number
     , ieba.bank_account_name
     , ieba.bank_account_name_alt
     , ieba.short_acct_name
     , ieba.description
     , ieba.bank_account_num_electronic
     , ieba.ba_num_elec_sec_segment_id
     , ieba.salt_version
     , ieba.contact_name
     , ieba.contact_phone
     , ieba.contact_fax
     , ieba.contact_email
     , aps.vendor_id
     , hopbank.bank_or_branch_number "BANK_NUMBER"
     , hopbranch.bank_or_branch_number "BRANCH_NUMBER"
     , hzpbank.party_name "BANK_NAME"
     , hzpbranch.party_name "BRANCH_NAME"
     , hopbank.home_country
     , ipi.order_of_preference
     , ipi.start_date assign_date
     , ipi.end_date assign_end_date
     , ipi.last_update_date last_date
     , aps.segment1
     , aps.vendor_name
  FROM hz_parties hzp
     , ap_suppliers aps
     , iby_external_payees_all iep
     , iby_pmt_instr_uses_all ipi
     , iby_ext_bank_accounts ieba
     , hz_parties hzpbank
     , hz_parties hzpbranch
     , hz_organization_profiles hopbranch
     , hz_organization_profiles hopbank
     , iby_account_owners iao
 WHERE hzp.party_id = aps.party_id
   AND iep.payee_party_id = hzp.party_id
   AND hzp.party_id = iao.account_owner_party_id
   AND ieba.ext_bank_account_id = iao.ext_bank_account_id
   AND iep.ext_payee_id = ipi.ext_pmt_party_id
   AND ipi.instrument_id = ieba.ext_bank_account_id
   AND ieba.bank_id = hzpbank.party_id
   AND ieba.branch_id = hzpbranch.party_id
   AND hzpbranch.party_id = hopbranch.party_id
   AND hzpbank.party_id = hopbank.party_id
  -- AND aps.segment1 = " <Supplier Number>"
   AND NVL (TO_CHAR (aps.end_date_active, 'DD-MON-YYYY'), SYSDATE + 1) > SYSDATE
   AND iep.inactive_date IS NULL
   AND iep.org_type IS NULL
   AND iep.supplier_site_id IS NULL;