[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 2. update_ext_bank
* 3. set_ext_bank_end_date
* 4. check_ext_bank_exist
* 5. create_bank_branch
* 6. update_bank_branch
* 7. set_bank_branch_end_date
* 8. check_ext_bank_branch_exist
* 9. create_ext_bank_acct
* 10. update_ext_bank_acct
* 11. set_ext_bank_acct_dates
* 12. check_ext_acct_exist
* 13. get_ext_bank_acct
* 14. get_ext_bank_acct
* 15. create_intermediary_acct
* 16. update_intermediary_acct
* 17. add_joint_acct_owner
* 18. set_joint_acct_owner_end_date
* 19. change_primary_acct_owner
+====================================================================*/
-- 1. create_ext_bank
--
-- API name : create_ext_bank
-- Type : Public
-- Pre-reqs : None
-- Function : Creates an external bank
-- Current version : 1.0
-- Previous version: 1.0
-- Initial version : 1.0
--
PROCEDURE create_ext_bank (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_ext_bank_rec IN ExtBank_rec_type,
x_bank_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_ext_bank';
PROCEDURE update_ext_bank (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_ext_bank_rec IN ExtBank_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'update_bank';
SAVEPOINT update_bank_pub;
print_debuginfo('Calling CE API to update bank');
ce_bank_pub.update_bank(p_init_msg_list,
p_ext_bank_rec.bank_id,
p_ext_bank_rec.bank_name,
p_ext_bank_rec.bank_number,
p_ext_bank_rec.bank_alt_name,
p_ext_bank_rec.bank_short_name, -- p_short_bank_name
p_ext_bank_rec.description,
p_ext_bank_rec.tax_payer_id, -- p_tax_payer_id
p_ext_bank_rec.tax_registration_number, -- p_tax_registration_number
p_ext_bank_rec.attribute_category, -- p_attribute_category
p_ext_bank_rec.attribute1, -- p_attribute1
p_ext_bank_rec.attribute2, -- p_attribute2
p_ext_bank_rec.attribute3, -- p_attribute3
p_ext_bank_rec.attribute4, -- p_attribute4
p_ext_bank_rec.attribute5, -- p_attribute5
p_ext_bank_rec.attribute6, -- p_attribute6
p_ext_bank_rec.attribute7, -- p_attribute7
p_ext_bank_rec.attribute8, -- p_attribute8
p_ext_bank_rec.attribute9, -- p_attribute9
p_ext_bank_rec.attribute10, -- p_attribute10
p_ext_bank_rec.attribute11, -- p_attribute11
p_ext_bank_rec.attribute12, -- p_attribute12
p_ext_bank_rec.attribute13, -- p_attribute13
p_ext_bank_rec.attribute14, -- p_attribute14
p_ext_bank_rec.attribute15, -- p_attribute15
p_ext_bank_rec.attribute16, -- p_attribute16
p_ext_bank_rec.attribute17, -- p_attribute17
p_ext_bank_rec.attribute18, -- p_attribute18
p_ext_bank_rec.attribute19, -- p_attribute19
p_ext_bank_rec.attribute20, -- p_attribute20
p_ext_bank_rec.attribute21, -- p_attribute21
p_ext_bank_rec.attribute22, -- p_attribute22
p_ext_bank_rec.attribute23, -- p_attribute23
p_ext_bank_rec.attribute24, -- p_attribute24
x_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
ROLLBACK TO update_bank_pub;
ROLLBACK TO update_bank_pub;
ROLLBACK TO update_bank_pub;
END update_ext_bank;
SELECT object_version_number
FROM hz_parties
WHERE party_id = p_bank_id;
PROCEDURE update_ext_bank_branch (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_ext_bank_branch_rec IN OUT NOCOPY ExtBankBranch_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_ext_bank_branch';
SELECT object_version_number
FROM HZ_CODE_ASSIGNMENTS
WHERE class_category = 'RFC_IDENTIFIER'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_branch_id;
SELECT object_version_number
FROM HZ_CODE_ASSIGNMENTS
WHERE class_category = 'EFT'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_branch_id;
SAVEPOINT update_bank_branch_pub;
print_debuginfo('Calling CE API to update bank branch');
ce_bank_pub.update_bank_branch(p_init_msg_list,
p_ext_bank_branch_rec.branch_party_id,
p_ext_bank_branch_rec.branch_name,
p_ext_bank_branch_rec.branch_number,
p_ext_bank_branch_rec.branch_type,
p_ext_bank_branch_rec.alternate_branch_name,
p_ext_bank_branch_rec.description,
p_ext_bank_branch_rec.bic,
p_ext_bank_branch_rec.eft_number, -- p_eft_number
p_ext_bank_branch_rec.rfc_identifier,
p_ext_bank_branch_rec.attribute_category, -- p_attribute_category
p_ext_bank_branch_rec.attribute1, -- p_attribute1
p_ext_bank_branch_rec.attribute2, -- p_attribute2
p_ext_bank_branch_rec.attribute3, -- p_attribute3
p_ext_bank_branch_rec.attribute4, -- p_attribute4
p_ext_bank_branch_rec.attribute5, -- p_attribute5
p_ext_bank_branch_rec.attribute6, -- p_attribute6
p_ext_bank_branch_rec.attribute7, -- p_attribute7
p_ext_bank_branch_rec.attribute8, -- p_attribute8
p_ext_bank_branch_rec.attribute9, -- p_attribute9
p_ext_bank_branch_rec.attribute10, -- p_attribute10
p_ext_bank_branch_rec.attribute11, -- p_attribute11
p_ext_bank_branch_rec.attribute12, -- p_attribute12
p_ext_bank_branch_rec.attribute13, -- p_attribute13
p_ext_bank_branch_rec.attribute14, -- p_attribute14
p_ext_bank_branch_rec.attribute15, -- p_attribute15
p_ext_bank_branch_rec.attribute16, -- p_attribute16
p_ext_bank_branch_rec.attribute17, -- p_attribute17
p_ext_bank_branch_rec.attribute18, -- p_attribute18
p_ext_bank_branch_rec.attribute19, -- p_attribute19
p_ext_bank_branch_rec.attribute20, -- p_attribute20
p_ext_bank_branch_rec.attribute21, -- p_attribute21
p_ext_bank_branch_rec.attribute22, -- p_attribute22
p_ext_bank_branch_rec.attribute23, -- p_attribute23
p_ext_bank_branch_rec.attribute24, -- p_attribute24
p_ext_bank_branch_rec.bch_object_version_number,
p_ext_bank_branch_rec.typ_object_version_number,
p_ext_bank_branch_rec.rfc_object_version_number,
p_ext_bank_branch_rec.eft_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
ROLLBACK TO update_bank_branch_pub;
ROLLBACK TO update_bank_branch_pub;
ROLLBACK TO update_bank_branch_pub;
END update_ext_bank_branch;
SELECT object_version_number
FROM hz_parties
WHERE party_id = p_branch_id;
SELECT bank_or_branch_number
FROM hz_organization_profiles
WHERE SYSDATE between TRUNC(effective_start_date)
and NVL(TRUNC(effective_end_date), SYSDATE+1)
AND party_id = p_branch_id;
SELECT owners.account_owner_party_id
FROM iby_pmt_instr_uses_all instrument,
IBY_ACCOUNT_OWNERS owners,
iby_external_payees_all payees
WHERE
owners.primary_flag = 'Y' AND
owners.ext_bank_account_id = p_acct_id AND
owners.ext_bank_account_id = instrument.instrument_id AND
payees.ext_payee_id = instrument.ext_pmt_party_id AND
payees.payee_party_id = owners.account_owner_party_id;
SELECT vendor_name, segment1 INTO l_supplier_name, l_supplier_number FROM ap_suppliers WHERE party_id = l_party_id;
fnd_msg_pub.delete_msg;
INSERT INTO IBY_EXT_BANK_ACCOUNTS
(
EXT_BANK_ACCOUNT_ID,
COUNTRY_CODE,
BRANCH_ID,
BANK_ID,
BANK_ACCOUNT_NUM,
BANK_ACCOUNT_NUM_HASH1,
BANK_ACCOUNT_NUM_HASH2,
MASKED_BANK_ACCOUNT_NUM,
BA_MASK_SETTING,
BA_UNMASK_LENGTH,
CURRENCY_CODE,
IBAN,
IBAN_HASH1,
IBAN_HASH2,
MASKED_IBAN,
CHECK_DIGITS,
BANK_ACCOUNT_TYPE,
ACCOUNT_CLASSIFICATION,
ACCOUNT_SUFFIX,
AGENCY_LOCATION_CODE,
-- MULTI_CURRENCY_ALLOWED_FLAG,
PAYMENT_FACTOR_FLAG,
FOREIGN_PAYMENT_USE_FLAG,
EXCHANGE_RATE_AGREEMENT_NUM,
EXCHANGE_RATE_AGREEMENT_TYPE,
EXCHANGE_RATE,
HEDGING_CONTRACT_REFERENCE,
-- STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
--REQUEST_ID,
--PROGRAM_APPLICATION_ID,
--PROGRAM_ID,
--PROGRAM_UPDATE_DATE,
START_DATE,
END_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
BANK_ACCOUNT_NAME,
BANK_ACCOUNT_NAME_ALT,
SHORT_ACCT_NAME,
DESCRIPTION,
ENCRYPTED,
BANK_ACCOUNT_NUM_ELECTRONIC,
SALT_VERSION,
SECONDARY_ACCOUNT_REFERENCE -- Bug 7408747
)
VALUES
(
IBY_EXT_BANK_ACCOUNTS_S.nextval, --EXT_BANK_ACCOUNT_ID,
p_ext_bank_acct_rec.country_code, --COUNTRY_CODE,
p_ext_bank_acct_rec.branch_id, --BRANCH_ID,
p_ext_bank_acct_rec.bank_id, --BANK_ID,
p_ext_bank_acct_rec.bank_account_num, --BANK_ACCOUNT_NUM,
l_ba_num_hash1,
l_ba_num_hash2,
l_masked_ba_num,
lx_mask_option,
lx_unmask_len,
p_ext_bank_acct_rec.currency, --CURRENCY_CODE,
p_ext_bank_acct_rec.iban, --IBAN,
l_iban_hash1,
l_iban_hash2,
l_masked_iban,
p_ext_bank_acct_rec.check_digits, --CHECK_DIGITS,
p_ext_bank_acct_rec.acct_type, --BANK_ACCOUNT_TYPE,
'EXTERNAL', --ACCOUNT_CLASSIFICATION,
p_ext_bank_acct_rec.acct_suffix, --ACCOUNT_SUFFIX,
p_ext_bank_acct_rec.agency_location_code, --AGENCY_LOCATION_CODE,
-- p_ext_bank_acct_rec.multi_currency_allowed_flag, --MULTI_CURRENCY_ALLOWED_FLAG,
p_ext_bank_acct_rec.payment_factor_flag, --PAYMENT_FACTOR_FLAG,
p_ext_bank_acct_rec.foreign_payment_use_flag, --FOREIGN_PAYMENT_USE_FLAG,
p_ext_bank_acct_rec.exchange_rate_agreement_num, --EXCHANGE_RATE_AGREEMENT_NUM,
p_ext_bank_acct_rec.exchange_rate_agreement_type, --EXCHANGE_RATE_AGREEMENT_TYPE,
p_ext_bank_acct_rec.exchange_rate, --EXCHANGE_RATE,
p_ext_bank_acct_rec.hedging_contract_reference, --HEDGING_CONTRACT_REFERENCE,
-- p_ext_bank_acct_rec.status, --STATUS,
p_ext_bank_acct_rec.attribute_category, --ATTRIBUTE_CATEGORY,
p_ext_bank_acct_rec.attribute1, --ATTRIBUTE1,
p_ext_bank_acct_rec.attribute2, --ATTRIBUTE2,
p_ext_bank_acct_rec.attribute3, --ATTRIBUTE3
p_ext_bank_acct_rec.attribute4, --ATTRIBUTE4,
p_ext_bank_acct_rec.attribute5, --ATTRIBUTE5,
p_ext_bank_acct_rec.attribute6, --ATTRIBUTE6,
p_ext_bank_acct_rec.attribute7, --ATTRIBUTE7,
p_ext_bank_acct_rec.attribute8, --ATTRIBUTE8,
p_ext_bank_acct_rec.attribute9, --ATTRIBUTE9,
p_ext_bank_acct_rec.attribute10, --ATTRIBUTE10,
p_ext_bank_acct_rec.attribute11, --ATTRIBUTE11,
p_ext_bank_acct_rec.attribute12, --ATTRIBUTE12,
p_ext_bank_acct_rec.attribute13, --ATTRIBUTE13,
p_ext_bank_acct_rec.attribute14, --ATTRIBUTE14,
p_ext_bank_acct_rec.attribute15, --ATTRIBUTE15,
--REQUEST_ID,
--PROGRAM_APPLICATION_ID,
--PROGRAM_ID,
--PROGRAM_UPDATE_DATE,
NVL(p_ext_bank_acct_rec.start_date, sysdate), --START_DATE,
p_ext_bank_acct_rec.end_date, --END_DATE,
fnd_global.user_id, --CREATED_BY,
sysdate, --CREATION_DATE,
fnd_global.user_id, --LAST_UPDATED_BY,
sysdate, --LAST_UPDATE_DATE,
fnd_global.login_id, --LAST_UPDATE_LOGIN,
1.0, --OBJECT_VERSION_NUMBER,
p_ext_bank_acct_rec.bank_account_name, --BANK_ACCOUNT_NAME
p_ext_bank_acct_rec.alternate_acct_name, --BANK_ACCOUNT_NAME_ALT
p_ext_bank_acct_rec.short_acct_name, --SHORT_ACCT_NAME
p_ext_bank_acct_rec.description, --DESCRIPTION
'N', -- encrypted
l_bank_account_num_electronic,
iby_security_pkg.get_salt_version,
p_ext_bank_acct_rec.secondary_account_reference -- Bug 7408747
) RETURNING EXT_BANK_ACCOUNT_ID INTO x_acct_id;
print_debuginfo('New Row inserted in IBY_EXT_BANK_ACCOUNTS');
print_debuginfo('Failed to insert in IBY_EXT_BANK_ACCOUNTS');
l_insert_status BOOLEAN;
select org_id, vendor_site_id
INTO l_org_id, l_supp_site_id
from ap_supplier_sites_all
where vendor_site_id = p_supplier_site_id AND
org_id = p_org_id;
SELECT status
INTO l_party_site_status
FROM HZ_PARTY_SITES
WHERE party_site_id = p_party_site_id;
SELECT status
INTO l_party_site_status
FROM HZ_PARTY_SITES
WHERE party_site_id = p_party_site_id;
select org_type
INTO l_org_type
from iby_external_payees_all
where payee_party_id=p_ext_bank_acct_rec.acct_owner_party_id AND
PAYMENT_FUNCTION='PAYABLES_DISB' AND
PARTY_SITE_ID =p_party_site_id AND
ORG_ID = p_org_id AND
SUPPLIER_SITE_ID IS NOT NULL;
PROCEDURE update_ext_bank_acct (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_ext_bank_acct_rec IN OUT NOCOPY ExtBankAcct_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_ext_bank_acct';
SELECT object_version_number,
bank_account_num_hash1,
bank_account_num_hash2,
iban_hash1,
iban_hash2,
ba_mask_setting,
ba_unmask_length,
ba_num_sec_segment_id,
iban_sec_segment_id,
encrypted,
iban,
masked_iban
FROM IBY_EXT_BANK_ACCOUNTS
WHERE EXT_BANK_ACCOUNT_ID = p_acct_id;
SELECT count(*)
FROM IBY_EXT_BANK_ACCOUNTS_V
WHERE
(bank_acct_num_hash1 = p_account_num_hash1)
AND (bank_acct_num_hash2 = p_account_num_hash2)
-- AND (p_currency IS NULL OR CURRENCY_CODE = p_currency)
AND (p_bank_id IS NULL and BANK_PARTY_ID is NULL) OR (BANK_PARTY_ID = p_bank_id)
AND (p_branch_id IS NULL and BRANCH_PARTY_ID is NULL) OR (BRANCH_PARTY_ID = p_branch_id)
AND EXT_BANK_ACCOUNT_ID<>p_bank_acct_id;
SELECT BANK_NUMBER, BRANCH_NUMBER
FROM CE_BANK_BRANCHES_V
WHERE BANK_PARTY_ID = p_bank_id
AND BRANCH_PARTY_ID = p_branch_id;
SELECT owners.account_owner_party_id
FROM iby_pmt_instr_uses_all instrument,
IBY_ACCOUNT_OWNERS owners,
iby_external_payees_all payees
WHERE
owners.primary_flag = 'Y' AND
owners.ext_bank_account_id = p_acct_id AND
owners.ext_bank_account_id = instrument.instrument_id AND
payees.ext_payee_id = instrument.ext_pmt_party_id AND
payees.payee_party_id = owners.account_owner_party_id;
SAVEPOINT update_ext_bank_acct_pub;
DELETE FROM iby_security_segments
WHERE sec_segment_id = l_ba_segment_id;
DELETE FROM iby_security_segments
WHERE sec_segment_id = l_iban_segment_id;
print_debuginfo('before delete');
DELETE FROM iby_security_segments
WHERE sec_segment_id = l_iban_segment_id;
print_debuginfo('After delete');
SELECT vendor_name, segment1 INTO l_supplier_name, l_supplier_number FROM ap_suppliers WHERE party_id = l_party_id;
fnd_msg_pub.delete_msg;
print_debuginfo('Before Update: using bank account');
UPDATE IBY_EXT_BANK_ACCOUNTS
SET BANK_ACCOUNT_NUM = nvl(l_acct_number, BANK_ACCOUNT_NUM),
COUNTRY_CODE=p_ext_bank_acct_rec.country_code,
CURRENCY_CODE = p_ext_bank_acct_rec.currency,
IBAN = l_iban,
CHECK_DIGITS = p_ext_bank_acct_rec.check_digits,
-- MULTI_CURRENCY_ALLOWED_FLAG = p_ext_bank_acct_rec.multi_currency_allowed_flag,
BANK_ACCOUNT_TYPE = p_ext_bank_acct_rec.acct_type,
ACCOUNT_SUFFIX = p_ext_bank_acct_rec.acct_suffix,
AGENCY_LOCATION_CODE = p_ext_bank_acct_rec.agency_location_code,
BANK_ID = p_ext_bank_acct_rec.bank_id,
BRANCH_ID = p_ext_bank_acct_rec.branch_id,
FOREIGN_PAYMENT_USE_FLAG=p_ext_bank_acct_rec.foreign_payment_use_flag,
PAYMENT_FACTOR_FLAG=p_ext_bank_acct_rec.payment_factor_flag,
EXCHANGE_RATE_AGREEMENT_TYPE=p_ext_bank_acct_rec.exchange_rate_agreement_type,
EXCHANGE_RATE_AGREEMENT_NUM=p_ext_bank_acct_rec.exchange_rate_agreement_num,
EXCHANGE_RATE=p_ext_bank_acct_rec.exchange_rate,
START_DATE=p_ext_bank_acct_rec.start_date,
END_DATE=p_ext_bank_acct_rec.end_date,
HEDGING_CONTRACT_REFERENCE=p_ext_bank_acct_rec.hedging_contract_reference,
MASKED_BANK_ACCOUNT_NUM=nvl(l_masked_ba_num,MASKED_BANK_ACCOUNT_NUM),
MASKED_IBAN=l_masked_iban,
IBAN_HASH1=l_iban_hash1,
IBAN_HASH2=l_iban_hash2,
BANK_ACCOUNT_NUM_HASH1=nvl(l_ba_num_hash1,BANK_ACCOUNT_NUM_HASH1),
-- bug 7635964
BANK_ACCOUNT_NUM_HASH2=nvl(l_ba_num_hash2,BANK_ACCOUNT_NUM_HASH2),
ENCRYPTED = l_encrypted,
BANK_ACCOUNT_NUM_ELECTRONIC = nvl(l_bank_account_num_electronic,
BANK_ACCOUNT_NUM_ELECTRONIC),
ATTRIBUTE_CATEGORY = p_ext_bank_acct_rec.attribute_category,
ATTRIBUTE1 = p_ext_bank_acct_rec.attribute1,
ATTRIBUTE2 = p_ext_bank_acct_rec.attribute2,
ATTRIBUTE3 = p_ext_bank_acct_rec.attribute3,
ATTRIBUTE4 = p_ext_bank_acct_rec.attribute4,
ATTRIBUTE5 = p_ext_bank_acct_rec.attribute5,
ATTRIBUTE6 = p_ext_bank_acct_rec.attribute6,
ATTRIBUTE7 = p_ext_bank_acct_rec.attribute7,
ATTRIBUTE8 = p_ext_bank_acct_rec.attribute8,
ATTRIBUTE9 = p_ext_bank_acct_rec.attribute9,
ATTRIBUTE10 = p_ext_bank_acct_rec.attribute10,
ATTRIBUTE11 = p_ext_bank_acct_rec.attribute11,
ATTRIBUTE12 = p_ext_bank_acct_rec.attribute12,
ATTRIBUTE13 = p_ext_bank_acct_rec.attribute13,
ATTRIBUTE14 = p_ext_bank_acct_rec.attribute14,
ATTRIBUTE15 = p_ext_bank_acct_rec.attribute15,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = fnd_global.login_id,
BANK_ACCOUNT_NAME = p_ext_bank_acct_rec.bank_account_name,
BANK_ACCOUNT_NAME_ALT = p_ext_bank_acct_rec.alternate_acct_name,
SHORT_ACCT_NAME = p_ext_bank_acct_rec.short_acct_name,
DESCRIPTION = p_ext_bank_acct_rec.description,
OBJECT_VERSION_NUMBER = p_ext_bank_acct_rec.object_version_number+1,
SECONDARY_ACCOUNT_REFERENCE = p_ext_bank_acct_rec.secondary_account_reference -- Bug 7408747
WHERE EXT_BANK_ACCOUNT_ID = p_ext_bank_acct_rec.bank_account_id
RETURNING OBJECT_VERSION_NUMBER INTO p_ext_bank_acct_rec.object_version_number;
ROLLBACK TO update_ext_bank_acct_pub;
ROLLBACK TO update_ext_bank_acct_pub;
ROLLBACK TO update_ext_bank_acct_pub;
END update_ext_bank_acct;
SELECT b.EXT_BANK_ACCOUNT_ID,
b.COUNTRY_CODE,
b.BRANCH_ID,
b.BANK_ID,
b.BANK_ACCOUNT_NUM,
b.CURRENCY_CODE,
b.IBAN,
b.CHECK_DIGITS,
b.BANK_ACCOUNT_TYPE,
b.ACCOUNT_CLASSIFICATION,
b.ACCOUNT_SUFFIX,
b.AGENCY_LOCATION_CODE,
-- b.MULTI_CURRENCY_ALLOWED_FLAG,
b.PAYMENT_FACTOR_FLAG,
b.FOREIGN_PAYMENT_USE_FLAG,
b.EXCHANGE_RATE_AGREEMENT_NUM,
b.EXCHANGE_RATE_AGREEMENT_TYPE,
b.EXCHANGE_RATE,
b.HEDGING_CONTRACT_REFERENCE,
-- b.STATUS,
b.ATTRIBUTE_CATEGORY,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
b.ATTRIBUTE5,
b.ATTRIBUTE6,
b.ATTRIBUTE7,
b.ATTRIBUTE8,
b.ATTRIBUTE9,
b.ATTRIBUTE10,
b.ATTRIBUTE11,
b.ATTRIBUTE12,
b.ATTRIBUTE13,
b.ATTRIBUTE14,
b.ATTRIBUTE15,
b.REQUEST_ID,
b.PROGRAM_APPLICATION_ID,
b.PROGRAM_ID,
b.PROGRAM_UPDATE_DATE,
b.START_DATE,
b.END_DATE,
b.CREATED_BY,
b.CREATION_DATE,
b.LAST_UPDATED_BY,
b.LAST_UPDATE_DATE,
b.LAST_UPDATE_LOGIN,
b.OBJECT_VERSION_NUMBER,
null,
b.BANK_ACCOUNT_NUM_HASH2,
b.BANK_ACCOUNT_NUM_HASH1,
b.MASKED_BANK_ACCOUNT_NUM,
b.IBAN_HASH1,
b.IBAN_HASH2,
b.MASKED_IBAN,
b.BA_MASK_SETTING,
b.BA_UNMASK_LENGTH,
b.ENCRYPTED,
b.BANK_ACCOUNT_NAME,
b.BANK_ACCOUNT_NAME_ALT,
b.SHORT_ACCT_NAME,
b.DESCRIPTION,
b.SECONDARY_ACCOUNT_REFERENCE -- Bug 7408747
FROM IBY_EXT_BANK_ACCOUNTS b
WHERE b.EXT_BANK_ACCOUNT_ID = p_bankacct_id;
SELECT ACCOUNT_OWNER_PARTY_ID
FROM IBY_ACCOUNT_OWNERS
WHERE EXT_BANK_ACCOUNT_ID = p_bankacct_id
AND PRIMARY_FLAG = 'Y';
UPDATE IBY_EXT_BANK_ACCOUNTS
SET START_DATE = p_start_date, END_DATE = NVL(p_end_date,END_DATE)
WHERE EXT_BANK_ACCOUNT_ID = p_acct_id;
SELECT EXT_BANK_ACCOUNT_ID,
START_DATE,
END_DATE
FROM IBY_EXT_BANK_ACCOUNTS
WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
AND p_country_code=COUNTRY_CODE
AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id));
SELECT EXT_BANK_ACCOUNT_ID,
START_DATE,
END_DATE
FROM IBY_EXT_BANK_ACCOUNTS
WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
AND p_country_code=COUNTRY_CODE
AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id))
AND ((p_acct_type is NULL and BANK_ACCOUNT_TYPE is NULL)OR (p_acct_type=BANK_ACCOUNT_TYPE));
SELECT EXT_BANK_ACCOUNT_ID,
START_DATE,
END_DATE
FROM IBY_EXT_BANK_ACCOUNTS
WHERE (BANK_ACCOUNT_NUM_HASH1= p_account_num_hash1)
AND (BANK_ACCOUNT_NUM_HASH2= p_account_num_hash2)
AND ((p_currency IS NULL and CURRENCY_CODE is NULL) OR (CURRENCY_CODE = p_currency))
AND ((p_bank_id IS NULL AND BANK_ID is NULL) OR (BANK_ID = p_bank_id))
AND ((p_branch_id IS NULL AND BRANCH_ID is NULL) OR (BRANCH_ID = p_branch_id))
AND p_country_code=COUNTRY_CODE
AND ((p_acct_id IS NULL) OR (EXT_BANK_ACCOUNT_ID <> p_acct_id))
AND ((p_acct_suffix is NULL and ACCOUNT_SUFFIX is NULL)OR (p_acct_suffix=ACCOUNT_SUFFIX));
INSERT INTO IBY_INTERMEDIARY_ACCTS(
INTERMEDIARY_ACCT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
BANK_ACCT_ID,
COUNTRY_CODE,
BANK_NAME,
CITY,
BANK_CODE,
BRANCH_NUMBER,
BIC,
ACCOUNT_NUMBER,
CHECK_DIGITS,
IBAN,
COMMENTS,
OBJECT_VERSION_NUMBER)
VALUES (
IBY_INTERMEDIARY_ACCTS_S.nextval,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.login_id, -1),
sysdate,
NVL(FND_GLOBAL.user_id,-1),
p_intermed_acct_rec.bank_account_id,
p_intermed_acct_rec.country_code,
p_intermed_acct_rec.bank_name,
p_intermed_acct_rec.city,
p_intermed_acct_rec.bank_code,
p_intermed_acct_rec.branch_number,
p_intermed_acct_rec.bic,
p_intermed_acct_rec.account_number,
p_intermed_acct_rec.check_digits,
p_intermed_acct_rec.iban,
p_intermed_acct_rec.comments,
1)
RETURNING INTERMEDIARY_ACCT_ID INTO x_intermediary_acct_id;
PROCEDURE update_intermediary_acct (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_intermed_acct_rec IN OUT NOCOPY IntermediaryAcct_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_bank';
SELECT object_version_number
FROM IBY_INTERMEDIARY_ACCTS
WHERE intermediary_acct_id = p_intermed_acct_rec.intermediary_acct_id;
SAVEPOINT update_intermediary_acct;
UPDATE IBY_INTERMEDIARY_ACCTS
SET country_code = p_intermed_acct_rec.country_code,
bank_name = p_intermed_acct_rec.bank_name,
city = p_intermed_acct_rec.city,
bank_code = p_intermed_acct_rec.bank_code,
branch_number = p_intermed_acct_rec.branch_number,
bic = p_intermed_acct_rec.bic,
account_number = p_intermed_acct_rec.account_number,
check_digits = p_intermed_acct_rec.check_digits,
iban = p_intermed_acct_rec.iban,
comments = p_intermed_acct_rec.comments,
last_update_date = sysdate,
last_update_login = NVL(FND_GLOBAL.login_id,-1),
last_updated_by = NVL(FND_GLOBAL.user_id,-1),
object_version_number = l_old_ovn + 1
WHERE intermediary_acct_id = p_intermed_acct_rec.intermediary_acct_id
RETURNING object_version_number INTO p_intermed_acct_rec.object_version_number;
ROLLBACK TO update_intermediary_bank_acct;
ROLLBACK TO update_intermediary_bank_acct;
ROLLBACK TO update_intermediary_bank_acct;
END update_intermediary_acct;
SELECT COUNT(*)
FROM IBY_ACCOUNT_OWNERS
WHERE ACCOUNT_OWNER_PARTY_ID = p_party_id
AND EXT_BANK_ACCOUNT_ID = bank_account_id;
INSERT INTO IBY_ACCOUNT_OWNERS
(
ACCOUNT_OWNER_ID,
EXT_BANK_ACCOUNT_ID,
ACCOUNT_OWNER_PARTY_ID,
END_DATE,
PRIMARY_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES
(
IBY_ACCOUNT_OWNERS_S.NEXTVAL,
p_bank_account_id,
p_acct_owner_party_id,
NULL,
'N',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
1.0
) RETURNING ACCOUNT_OWNER_ID INTO x_joint_acct_owner_id;
UPDATE IBY_ACCOUNT_OWNERS
SET END_DATE = p_end_date,
OBJECT_VERSION_NUMBER = p_object_version_number + 1
WHERE ACCOUNT_OWNER_ID = p_acct_owner_id
AND OBJECT_VERSION_NUMBER = p_object_version_number
RETURNING OBJECT_VERSION_NUMBER INTO p_object_version_number;
print_debuginfo('Updated Object Version Number : ' || p_object_version_number);
SELECT account_owner_id
FROM IBY_ACCOUNT_OWNERS
WHERE EXT_BANK_ACCOUNT_ID = bank_account_id
AND primary_flag = 'Y';
UPDATE IBY_ACCOUNT_OWNERS
SET PRIMARY_FLAG = 'N'
WHERE EXT_BANK_ACCOUNT_ID = p_bank_acct_id;
UPDATE IBY_ACCOUNT_OWNERS
SET PRIMARY_FLAG = 'Y'
WHERE ACCOUNT_OWNER_PARTY_ID = p_acct_owner_party_id
AND EXT_BANK_ACCOUNT_ID = p_bank_acct_id;
SELECT hp.country
FROM hz_parties hp
WHERE hp.party_id = p_bank_id
AND hp.status = 'A';
select ce_bank.bank_party_id as bank_id,
ce_bank.bank_home_country as country,
ce_bank.bank_name,
ce_bank.bank_number
from ce_bank_branches_v ce_bank
where ce_bank.branch_party_id=p_branch_id;
SELECT hz_bank.party_id AS bank_id,
hz_bank.country,
hz_bank.party_name,
hz_bankorg.bank_or_branch_number
FROM hz_parties hz_bank,
hz_organization_profiles hz_bankorg,
hz_parties hz_branch,
hz_relationships hz_rel,
hz_code_assignments hz_bankCA,
hz_code_assignments hz_branchCA
WHERE hz_branchCA.owner_table_name = 'HZ_PARTIES'
AND hz_branchCA.owner_table_id = hz_branch.party_id
AND hz_branchCA.class_category = 'BANK_INSTITUTION_TYPE'
AND hz_branchCA.class_code = 'BANK_BRANCH'
AND NVL(hz_branchCA.STATUS, 'A') = 'A'
AND hz_bankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND hz_bankCA.CLASS_CODE = 'BANK'
AND hz_bankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND hz_bankCA.OWNER_TABLE_ID = hz_bank.PARTY_ID
AND NVL(hz_bankCA.STATUS, 'A') = 'A'
AND hz_rel.OBJECT_ID = hz_bank.PARTY_ID
And hz_branch.PARTY_ID = hz_rel.SUBJECT_ID
And hz_rel.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
And hz_rel.RELATIONSHIP_CODE = 'BRANCH_OF'
And hz_rel.STATUS = 'A'
And hz_rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
And hz_rel.SUBJECT_TYPE = 'ORGANIZATION'
And hz_rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
And hz_rel.OBJECT_TYPE = 'ORGANIZATION'
AND hz_bank.party_id = hz_bankorg.party_id
AND SYSDATE between TRUNC(hz_bankorg.effective_start_date)
and NVL(TRUNC(hz_bankorg.effective_end_date), SYSDATE+1)
AND hz_branch.party_id = p_branch_id;
SELECT CURRENCY_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = p_currency_code;
SELECT ext_ba_encryption_mode
FROM iby_sys_security_options;
SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
b.ba_num_sec_segment_id, bak.subkey_cipher_text ba_subkey_cipher,
bas.segment_cipher_text ba_segment_cipher,
bas.encoding_scheme ba_encoding,
b.iban_sec_segment_id, ibk.subkey_cipher_text iban_subkey_cipher,
ibs.segment_cipher_text iban_segment_cipher,
ibs.encoding_scheme iban_encoding,
b.ba_mask_setting, b.ba_unmask_length
FROM iby_ext_bank_accounts b, iby_sys_security_subkeys bak,
iby_sys_security_subkeys ibk, iby_security_segments bas,
iby_security_segments ibs
WHERE
( (NVL(ba_unmask_length,-1) <> ci_unmask_len) OR
(NVL(ba_mask_setting,' ') <> ci_mask_option) )
AND (b.ba_num_sec_segment_id = bas.sec_segment_id(+))
AND (bas.sec_subkey_id = bak.sec_subkey_id(+))
AND (b.iban_sec_segment_id = ibs.sec_segment_id(+))
AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+));
IBY_SECURITY_PKG.Update_Segment
(FND_API.G_FALSE,lx_ba_segment_id,l_ba_segment,
iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,
ext_ba_rec.ba_subkey_cipher);
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
IBY_SECURITY_PKG.Update_Segment
(FND_API.G_FALSE,lx_iban_segment_id,l_iban_segment,
iby_security_pkg.G_ENCODING_UTF8_AL32,p_sys_key,
ext_ba_rec.iban_subkey_cipher);
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
UPDATE iby_ext_bank_accounts
SET
bank_account_num = lx_ba_unmask_digits,
ba_num_sec_segment_id = lx_ba_segment_id,
iban = lx_iban_unmask_digits,
iban_sec_segment_id = lx_iban_segment_id,
masked_bank_account_num =
Mask_Bank_Number(l_ba_num,lx_mask_option,lx_unmask_len),
masked_iban = Mask_Bank_Number(l_iban,lx_mask_option,lx_unmask_len),
ba_mask_setting = lx_mask_option,
ba_unmask_length = lx_unmask_len,
encrypted = DECODE(l_mode, iby_security_pkg.G_ENCRYPT_MODE_NONE,'N',
'Y'
),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
b.ba_num_sec_segment_id, b.iban_sec_segment_id,
b.bank_account_num_electronic, b.ba_num_elec_sec_segment_id,
b.ba_mask_setting, b.ba_unmask_length
FROM iby_ext_bank_accounts b
WHERE (NVL(b.encrypted,'N') = 'N');
UPDATE iby_ext_bank_accounts
SET
bank_account_num = lx_ba_unmask_digits,
iban = lx_iban_unmask_digits,
ba_num_sec_segment_id = NVL(lx_ba_segment_id,ba_num_sec_segment_id),
iban_sec_segment_id = NVL(lx_iban_segment_id,iban_sec_segment_id),
bank_account_num_electronic = lx_e_ba_unmask_digits,
ba_num_elec_sec_segment_id =
NVL(lx_e_ba_segment_id,ba_num_elec_sec_segment_id),
encrypted = 'Y'
WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
SELECT b.ext_bank_account_id, b.bank_account_num, b.iban,
b.bank_account_num_electronic,
b.ba_num_sec_segment_id, bak.subkey_cipher_text ba_subkey_cipher,
bas.segment_cipher_text ba_segment_cipher,
bas.encoding_scheme ba_encoding,
b.iban_sec_segment_id, ibk.subkey_cipher_text iban_subkey_cipher,
ibs.segment_cipher_text iban_segment_cipher,
ibs.encoding_scheme iban_encoding,
b.ba_num_elec_sec_segment_id,
ebk.subkey_cipher_text e_ba_subkey_cipher,
ebs.segment_cipher_text e_ba_segment_cipher,
ebs.encoding_scheme e_ba_encoding,
b.ba_mask_setting, b.ba_unmask_length
FROM iby_ext_bank_accounts b, iby_sys_security_subkeys bak,
iby_sys_security_subkeys ebk, iby_sys_security_subkeys ibk,
iby_security_segments bas, iby_security_segments ibs,
iby_security_segments ebs
WHERE
((NOT ba_num_sec_segment_id IS NULL) OR (NOT iban_sec_segment_id IS NULL))
AND (b.ba_num_sec_segment_id = bas.sec_segment_id(+))
AND (bas.sec_subkey_id = bak.sec_subkey_id(+))
AND (b.iban_sec_segment_id = ibs.sec_segment_id(+))
AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+))
AND (b.ba_num_elec_sec_segment_id = ebs.sec_segment_id(+))
AND (ebs.sec_subkey_id = ebk.sec_subkey_id(+));
UPDATE iby_ext_bank_accounts
SET
bank_account_num = NVL(l_ba_num,bank_account_num),
iban = NVL(l_iban,iban),
bank_account_num_electronic = NVL(l_e_ba,bank_account_num_electronic),
ba_num_sec_segment_id = NULL,
iban_sec_segment_id = NULL,
ba_num_elec_sec_segment_id = NULL,
encrypted = 'N',
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (ext_bank_account_id = ext_ba_rec.ext_bank_account_id);
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.ba_num_sec_segment_id;
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.iban_sec_segment_id;
DELETE FROM iby_security_segments
WHERE sec_segment_id = ext_ba_rec.ba_num_elec_sec_segment_id;
SELECT ext_ba_mask_setting, ext_ba_unmask_len
FROM iby_sys_security_options;
SELECT vendor_id
INTO x_vendor_id
FROM ap_suppliers
WHERE party_id = p_party_id
AND rownum = 1;