Supplier and Supplier Site Level External Bank Queries in R12

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

-- Supplier Level External Bank
SELECT
    hzp.party_name "VENDOR NAME",
    aps.segment1 "VENDOR NUMBER",
    ieb.bank_account_num "ACCOUNT NUMBER",
    ieb.bank_account_name "ACCOUNT NAME",
    ieb.bank_account_type "Bank Account Type",
    ieb.ext_bank_account_id,
    ieb.bank_account_num,
    ieb.iban ext_bank_account_iban_number,
    ieb.bank_account_name,
    ieb.bank_account_name_alt,
    ieb.bank_account_num_electronic,
    ieb.end_date bank_account_end_date,
    aps.party_id,
    aps.vendor_name,
    NULL,
    NULL,
    NULL,
    ext_branch.bank_branch_name,
    ext_branch.bank_branch_name_alt,
    ext_branch.branch_number,
    ext_branch.branch_party_id,
    NULL,
    NULL,
    NULL,
    ext_bank.bank_name_alt,
    ext_bank.bank_name,
    ext_bank.bank_number,
    ipi.start_date supp_bank_ass_start_date,
    ipi.end_date supp_bank_ass_end_date
FROM
    apps.hz_parties hzp,
    apps.ap_suppliers aps,
    apps.iby_external_payees_all iep,
    apps.iby_pmt_instr_uses_all ipi,
    apps.iby_ext_bank_accounts ieb,
    iby_ext_banks_v ext_bank,
    iby_ext_bank_branches_v ext_branch
WHERE
        hzp.party_id = aps.party_id
    AND        iep.payee_party_id = hzp.party_id
    AND        ieb.bank_id = ext_bank.bank_party_id (+)
    AND        ieb.branch_id = ext_branch.branch_party_id (+)
    AND        ipi.instrument_id = ieb.ext_bank_account_id
    AND        iep.ext_payee_id = ipi.ext_pmt_party_id                                  
  --AND aps.segment1 = '3811'                                  
                                  --


-- Supplier Site Level External Bank
 SELECT
    hzp.party_name "VENDOR NAME",
    aps.segment1 "VENDOR NUMBER",
    assa.vendor_site_code "SITE CODE",
    ieb.bank_account_num "ACCOUNT NUMBER",
    ieb.bank_account_name "ACCOUNT NAME",
    ieb.bank_account_type "Bank Account Type",
    ieb.ext_bank_account_id,
    ieb.bank_account_num,
    ieb.iban ext_bank_account_iban_number,
    ieb.bank_account_name,
    ieb.bank_account_name_alt,
    ieb.bank_account_num_electronic,
    ieb.end_date bank_account_end_date,
    aps.party_id,
    aps.vendor_name,
    NULL,
    NULL,
    NULL,
    ext_branch.bank_branch_name,
    ext_branch.bank_branch_name_alt,
    ext_branch.branch_number,
    ext_branch.branch_party_id,
    NULL,
    NULL,
    NULL,
    ext_bank.bank_name_alt,
    ext_bank.bank_name,
    ext_bank.bank_number,
    ipi.start_date supp_bank_ass_start_date,
    ipi.end_date supp_bank_ass_end_date
FROM
    apps.hz_parties hzp,
    apps.ap_suppliers aps,
    apps.hz_party_sites site_supp,
    apps.ap_supplier_sites_all assa,
    apps.iby_external_payees_all iep,
    apps.iby_pmt_instr_uses_all ipi,
    apps.iby_ext_bank_accounts ieb,
    iby_ext_banks_v ext_bank,
    iby_ext_bank_branches_v ext_branch
WHERE
        hzp.party_id = aps.party_id
    AND        hzp.party_id = site_supp.party_id
    AND        site_supp.party_site_id = assa.party_site_id
    AND        assa.vendor_id = aps.vendor_id
    AND        iep.payee_party_id = hzp.party_id
    AND        iep.party_site_id = site_supp.party_site_id
    AND        iep.supplier_site_id = assa.vendor_site_id
    AND        ieb.bank_id = ext_bank.bank_party_id
    AND        ieb.branch_id = ext_branch.branch_party_id
    AND        ipi.instrument_id = ieb.ext_bank_account_id
    AND        iep.ext_payee_id = ipi.ext_pmt_party_id                                  
 -- AND aps.segment1 = '1311'                                  
    AND        assa.org_id = 101

--in case of supplier site level bank iby_external_payees_all will have link through supplier site id.

select * from apps.iby_pmt_instr_uses_all
where ext_pmt_party_id = 11402

select * from iby_external_payees_all
where ext_payee_id = 11402

select * from hz_parties
where party_id = 448346

select * from ap_suppliers
where party_id = 448346