Monday, 28 January 2013

Bank Account information for Release R12

/* Formatted on 1/28/2013 1:57:03 PM (QP5 v5.114.809.3010) */
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;

No comments:

Post a Comment