SELECT ba.bank_account_id, --银行账户key
ftv.territory_short_name, --国家
ftv.territory_code, --国家简称
cb.bank_name, ---银行名称
cbb.bank_branch_name, ---分行名称
hp.party_name, --公司法人
ba.ap_use_allowed_flag, --ap是否可用
ba.ar_use_allowed_flag, --ar是否可用
ba.bank_account_name, ---账户名称
ba.bank_account_num, --银行帐号
ba.currency_code, --币种
gcc1.segment1 asset_segment1, ---现金
gcc1.segment2 asset_segment2,
gcc1.segment3 asset_segment3,
gcc1.segment4 asset_segment4,
gcc1.segment5 asset_segment5,
gcc1.segment6 asset_segment6,
gcc1.segment7 asset_segment7,
gcc1.segment8 asset_segment8,
gcc2.segment1 cash_c_segment1, ---现金结算
gcc2.segment2 cash_c_segment2,
gcc2.segment3 cash_c_segment3,
gcc2.segment4 cash_c_segment4,
gcc2.segment5 cash_c_segment5,
gcc2.segment6 cash_c_segment6,
gcc2.segment7 cash_c_segment7,
gcc2.segment8 cash_c_segment8,
gcc3.segment1 bank_c_segment1, ---银行手续费
gcc3.segment2 bank_c_segment2,
gcc3.segment3 bank_c_segment3,
gcc3.segment4 bank_c_segment4,
gcc3.segment5 bank_c_segment5,
gcc3.segment6 bank_c_segment6,
gcc3.segment7 bank_c_segment7,
gcc3.segment8 bank_c_segment8,
ba.netting_acct_flag, --净值计算帐户
hou.name org_name,
hou.organization_id,
cbau.ap_use_enable_flag ou_ap_use_enable_flag,
cbau.ar_use_enable_flag ou_ar_use_enable_flag,
gcc4.segment1 ou_asset_segment1, ---公司银行科目
gcc4.segment2 ou_asset_segment2,
gcc4.segment3 ou_asset_segment3,
gcc4.segment4 ou_asset_segment4,
gcc4.segment5 ou_asset_segment5,
gcc4.segment6 ou_asset_segment6,
gcc4.segment7 ou_asset_segment7,
gcc4.segment8 ou_asset_segment8,
gcc5.segment1 ou_cash_c_segment1, ---公司现金结算
gcc5.segment2 ou_cash_c_segment2,
gcc5.segment3 ou_cash_c_segment3,
gcc5.segment4 ou_cash_c_segment4,
gcc5.segment5 ou_cash_c_segment5,
gcc5.segment6 ou_cash_c_segment6,
gcc5.segment7 ou_cash_c_segment7,
gcc5.segment8 ou_cash_c_segment8,
gcc6.segment1 ou_bank_c_segment1, ---公司银行手续费
gcc6.segment2 ou_bank_c_segment2,
gcc6.segment3 ou_bank_c_segment3,
gcc6.segment4 ou_bank_c_segment4,
gcc6.segment5 ou_bank_c_segment5,
gcc6.segment6 ou_bank_c_segment6,
gcc6.segment7 ou_bank_c_segment7,
gcc6.segment8 ou_bank_c_segment8,
gcc7.segment1 ou_future_segment1, ---公司远期付款
gcc7.segment2 ou_future_segment2,
gcc7.segment3 ou_future_segment3,
gcc7.segment4 ou_future_segment4,
gcc7.segment5 ou_future_segment5,
gcc7.segment6 ou_future_segment6,
gcc7.segment7 ou_future_segment7,
gcc7.segment8 ou_future_segment8,
ba.description,
substr(ba.description, 1, instr(ba.description, '+', 1) - 1) desc1, ---银行联行号
substr(ba.description,
instr(ba.description, '+', 1) + 1,
instr(ba.description, '+', 1, 2) -
instr(ba.description, '+', 1) - 1) desc2, ---银行所在省
substr(ba.description,
instr(ba.description, '+', 1, 2) + 1,
length(ba.description) - instr(ba.description, '+', 1, 2)) desc3 ---银行所在市
FROM ce_banks_v cb,
ce_bank_accounts ba,
ce_bank_branches_v cbb,
ce.ce_bank_acct_uses_all cbau,
fnd_territories_vl ftv,
hz_parties hp,
gl_code_combinations gcc1,
gl_code_combinations gcc2,
gl_code_combinations gcc3,
hr_operating_units hou,
ce_gl_accounts_ccid cgc,
gl_code_combinations gcc4,
gl_code_combinations gcc5,
gl_code_combinations gcc6,
gl_code_combinations gcc7
WHERE 1 = 1
AND cbau.bank_account_id = ba.bank_account_id
AND cbb.branch_party_id = ba.bank_branch_id
AND SYSDATE < nvl(ba.end_date, SYSDATE + 1)
AND ba.account_classification = 'INTERNAL'
AND cbau.ap_use_enable_flag = 'Y'
AND cb.pk_id = ba.bank_id
AND ftv.territory_code = cb.home_country
AND hp.party_id = ba.account_owner_party_id
AND gcc1.code_combination_id = ba.asset_code_combination_id
AND gcc2.code_combination_id = ba.cash_clearing_ccid
AND gcc3.code_combination_id = ba.bank_charges_ccid
AND hou.organization_id = cbau.org_id
AND cgc.bank_acct_use_id = cbau.bank_acct_use_id
AND cgc.ap_asset_ccid = gcc4.code_combination_id
AND cgc.cash_clearing_ccid = gcc5.code_combination_id
AND cgc.bank_charges_ccid = gcc6.code_combination_id
AND cgc.future_dated_payment_ccid = gcc7.code_combination_id
;
select hz3.party_name,
hr.name,
hz1.party_name,
hz2.party_name,
ce.bank_account_name,
ce.bank_account_num,
ce.attribute1,
ce.attribute2,
ce.attribute3,
ce.attribute4,
ce.attribute5,
ce.attribute6,
ce.attribute7,
ce.attribute8,
gcc.concatenated_segments
from ce_bank_accounts ce,
gl_code_combinations_kfv gcc,
hz_parties hz1,
hz_parties hz2,
hz_parties hz3,
hr_operating_units hr
where ce.asset_code_combination_id = gcc.code_combination_id
and ce.bank_id = hz1.party_id
and ce.bank_branch_id = hz2.party_id
and ce.account_owner_party_id = hz3.party_id
and hr.default_legal_context_id = ce.account_owner_org_id
and hz1.status = 'A'
and hz2.status = 'A'
and hz3.status = 'A'
