[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pos_bank_account_bo(eb.ext_bank_account_id,
eb.bank_id,
eb.country_code,
bp.party_name,
bapr.bank_or_branch_number,
eb.branch_id,
br.party_name,
brpr.bank_or_branch_number,
branchca.class_code,
s.location_id,
branchcp.eft_swift_code,
eb.ext_bank_account_id,
eb.bank_account_name,
eb.masked_bank_account_num,
eb.currency_code,
eb.description,
eb.check_digits,
decode(eb.currency_code, NULL, 'Y', 'N'),
eb.bank_account_name_alt,
eb.short_acct_name,
eb.account_suffix,
eb.masked_iban,
ow.account_owner_party_id,
op.party_name,
eb.account_classification,
eb.bank_account_type,
eb.agency_location_code,
eb.start_date,
eb.end_date,
eb.payment_factor_flag,
eb.foreign_payment_use_flag,
eb.exchange_rate_agreement_num,
eb.exchange_rate_agreement_type,
eb.exchange_rate,
eb.hedging_contract_reference,
eb.secondary_account_reference,
eb.attribute_category,
eb.attribute1,
eb.attribute2,
eb.attribute3,
eb.attribute4,
eb.attribute5,
eb.attribute6,
eb.attribute7,
eb.attribute8,
eb.attribute9,
eb.attribute10,
eb.attribute11,
eb.attribute12,
eb.attribute13,
eb.attribute14,
eb.attribute15,
eb.object_version_number,
eb.bank_account_num_electronic,
NULL,
NULL,
NULL
/*,
brpr.bank_code,
eb.encrypted*/)
BULK COLLECT
INTO l_pos_bank_account_bo_tbl
FROM hz_organization_profiles bapr,
hz_organization_profiles brpr,
hz_parties bp,
hz_party_sites s,
iby_account_owners ow,
hz_parties br,
hz_parties op,
iby_ext_bank_accounts eb,
hz_code_assignments branchca,
hz_contact_points branchcp,
-- Bug 13096283/13586778: Publish bank account info for non-primary owners of factor account
-- Part 1: Get all suppliers that own same bank account(s) with the current supplier (l_party_id)
(SELECT DISTINCT a1.account_owner_party_id,
a1.ext_bank_account_id
FROM iby_account_owners a1,
iby_external_payees_all payee,
iby_pmt_instr_uses_all instr
WHERE payee.ext_payee_id = instr.ext_pmt_party_id
AND payee_party_id = l_party_id
AND instr.instrument_id = a1.ext_bank_account_id
AND instr.instrument_type = 'BANKACCOUNT'
AND instr.payment_function = 'PAYABLES_DISB'
) supp
-- End Bug 13096283 Part 1
WHERE eb.bank_id = bp.party_id(+)
AND eb.bank_id = bapr.party_id(+)
AND eb.branch_id = br.party_id(+)
AND eb.branch_id = brpr.party_id(+)
AND eb.ext_bank_account_id = ow.ext_bank_account_id(+)
AND ow.primary_flag(+) = 'Y'
AND nvl(ow.end_date, SYSDATE + 10) > SYSDATE
AND ow.account_owner_party_id = op.party_id(+)
AND (br.party_id = s.party_id(+))
AND (s.identifying_address_flag(+) = 'Y')
AND (branchcp.owner_table_name(+) = 'HZ_PARTIES')
AND (branchcp.owner_table_id(+) = eb.branch_id)
AND (branchcp.contact_point_type(+) = 'EFT')
AND (nvl(branchcp.status(+), 'A') = 'A')
AND (branchca.class_category(+) = 'BANK_BRANCH_TYPE') -- Bug 13586778: Publish Branch Type
AND (branchca.owner_table_name(+) = 'HZ_PARTIES')
AND (branchca.owner_table_id(+) = eb.branch_id)
AND SYSDATE BETWEEN -- Bug 14621927: Check effect end date to prevent publishing ineffective bank record
trunc(bapr.effective_start_date) AND
nvl(trunc(bapr.effective_end_date), SYSDATE + 1)
AND SYSDATE BETWEEN
trunc(brpr.effective_start_date) AND
nvl(trunc(brpr.effective_end_date), SYSDATE + 1)
AND op.party_id = supp.account_owner_party_id -- Bug 13096283 Part 2: Join to get primary owner info for the bank account
AND eb.ext_bank_account_id = supp.ext_bank_account_id -- Bug 13586778: only select account that belongs to the current supplier.
AND branchca.primary_flag = 'Y'; -- Bug 16205262: Check primary_flag to prevent publishing duplicated bank account info.
p_create_update_flag IN VARCHAR2,
p_pos_bank_account_bo IN pos_bank_account_bo_tbl,
x_acct_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
v_row_exists NUMBER := 0;
IF p_create_update_flag = 'C' THEN
iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version,
p_init_msg_list,
l_ext_bank_acct_rec,
x_acct_id,
x_return_status,
x_msg_count,
x_msg_data,
l_response);
ELSIF p_create_update_flag = 'U' THEN
iby_ext_bankacct_pub.update_ext_bank_acct(p_api_version,
p_init_msg_list,
l_ext_bank_acct_rec,
x_return_status,
x_msg_count,
x_msg_data,
l_response);