[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| create_bank/update_bank API. |
| |
| Validate: |
| 1. Combination of Country and Bank Name is unique |
| 2. Combination of Country and Short Bank Name is unique |
| -- remove this 2nd validation due to upgrade changes |
| 3. Combination of Country and Bank Number is unique |
| |
| Bug 6642215/6742860: Validation changed to check combination of |
| bank name, number and country is unique. removed other validations |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_temp_id in HZ_ORG_PROFILE_VAL_GT table |
+=======================================================================*/
PROCEDURE validate_bank (p_temp_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2) IS
l_bank_name_rowid VARCHAR2(1000);
SELECT country
FROM HZ_BANK_VAL_GT
WHERE temp_id = p_temp_id;
SELECT bank_or_branch_number
FROM HZ_BANK_VAL_GT
WHERE temp_id = p_temp_id;
SELECT organization_name
FROM hz_org_profile_val_gt
WHERE temp_id = p_temp_id;
SELECT 'Y'
FROM hz_parties BankParty,
hz_organization_profiles BankOrgProfile,
hz_code_assignments BankCA
WHERE BankParty.PARTY_TYPE = 'ORGANIZATION'
AND BankParty.status = 'A'
AND BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
AND SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BankCA.CLASS_CODE = 'BANK'
AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND NVL(BankCA.STATUS, 'A') = 'A'
AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
AND BankOrgProfile.home_country = l_country
AND nvl(BankOrgProfile.bank_or_branch_number,'--NULL--') = nvl(l_bank_number,'--NULL--')
AND upper(BankParty.party_name) = upper(l_bank_name)
AND BankParty.party_id <> p_temp_id;
SELECT known_as
FROM hz_org_profile_val_gt
WHERE temp_id = p_temp_id;
SELECT 'Y'
FROM hz_parties BankParty,
hz_code_assignments BankCA,
hz_organization_profiles BankOrgProfile
WHERE BankParty.PARTY_TYPE = 'ORGANIZATION'
AND BankParty.status = 'A'
AND BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
AND SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BankCA.CLASS_CODE = 'BANK'
AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND NVL(BankCA.STATUS, 'A') = 'A'
AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
AND BankOrgProfile.home_country = l_country
AND upper(BankParty.party_name) = upper(l_bank_name)
AND BankParty.party_id <> p_temp_id;*/
SELECT 'Y'
FROM hz_parties BankParty,
hz_code_assignments BankCA,
hz_organization_profiles BankOrgProfile
WHERE BankParty.PARTY_TYPE = 'ORGANIZATION'
AND BankParty.status = 'A'
AND BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
AND SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BankCA.CLASS_CODE = 'BANK'
AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND NVL(BankCA.STATUS, 'A') = 'A'
AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
AND BankOrgProfile.home_country = l_country
AND BankParty.known_as = l_short_name
AND BankParty.party_id <> p_temp_id;
SELECT 'Y'
FROM hz_parties BankParty,
hz_organization_profiles BankOrgProfile,
hz_code_assignments BankCA
WHERE BankParty.PARTY_TYPE = 'ORGANIZATION'
AND BankParty.status = 'A'
AND BankParty.PARTY_ID = BankOrgProfile.PARTY_ID
AND SYSDATE between TRUNC(BankOrgProfile.effective_start_date)
and NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BankCA.CLASS_CODE = 'BANK'
AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND NVL(BankCA.STATUS, 'A') = 'A'
AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
AND BankOrgProfile.home_country = l_country
AND BankOrgProfile.bank_or_branch_number = l_bank_number
AND BankParty.party_id <> p_temp_id;
| update_bank_branch API |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_temp_org_profile_id temp_id in HZ_ORG_PROFILE_VAL_GT table |
+=======================================================================*/
PROCEDURE validate_branch (p_temp_org_profile_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2) IS
BEGIN
NULL;
SELECT 1
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code;
SELECT rowid
FROM ce_bank_accounts
WHERE bank_account_name = p_account_name
AND bank_branch_id = p_branch_id;
SELECT 'X'
FROM ce_bank_accounts
WHERE bank_branch_id = p_branch_id
AND bank_account_name = p_account_name
AND bank_account_id <> NVL(p_account_id, -1);
| Validate that at least one account use is selected for the |
| bank account |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_ap, p_ar, p_pay, p_xtr |
+=======================================================================*/
PROCEDURE validate_account_use(p_ap IN VARCHAR2,
p_ar IN VARCHAR2,
p_pay IN VARCHAR2,
p_xtr IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2 ) IS
BEGIN
cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use (+)');
| Short Account Name is required when Xtr use is selected for the |
| bank account |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| p_short_account_name, p_xtr |
+=======================================================================*/
PROCEDURE validate_short_account_name(p_short_account_name IN VARCHAR2,
p_xtr IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2 ) IS
BEGIN
cep_standard.debug('CE_BANK_AND_ACCOUNT_VALIDATION.validate_account_use (+)');
SELECT full_name
INTO l_return
FROM per_employees_x
WHERE employee_id = p_emp_id;
SELECT hl.meaning || ', '
FROM hr_organization_information oi,
hr_lookups hl
WHERE oi.org_information_context = 'CLASS'
AND oi.org_information1 = 'HR_BG'
and hl.lookup_type = 'ORG_CLASS'
AND hl.lookup_code = oi.org_information1
and oi.organization_id = p_org_id;
SELECT hl.meaning || ', '
FROM hr_organization_information oi,
hr_lookups hl
WHERE oi.org_information_context = 'CLASS'
AND oi.org_information1 = 'HR_LEGAL'
and hl.lookup_type = 'ORG_CLASS'
AND hl.lookup_code = oi.org_information1
and oi.organization_id = p_org_id;
SELECT hl.meaning || ', '
FROM hr_organization_information oi,
hr_lookups hl
WHERE oi.org_information_context = 'CLASS'
AND oi.org_information1 = 'OPERATING_UNIT'
and hl.lookup_type = 'ORG_CLASS'
AND hl.lookup_code = oi.org_information1
and oi.organization_id = p_org_id;
SELECT 'BG'
FROM ce_security_profiles_v
WHERE organization_type = 'BUSINESS_GROUP'
AND organization_id = p_org_id;
SELECT 'OU'
FROM ce_security_profiles_v
WHERE organization_type = 'OPERATING_UNIT'
AND organization_id = p_org_id;
SELECT 1
FROM fnd_territories ft
WHERE ft.territory_code = p_country_code;
select currency_code into p_cur
from ce_bank_accounts
where bank_account_id = p_bank_account_id;
select count(*) into p_ap_def
from ce_bank_accounts ba, ce_bank_acct_uses_all bau
where ba.bank_account_id = bau.bank_account_id
and ba.currency_code = p_cur
and nvl(bau.org_id, bau.LEGAL_ENTITY_ID) = p_org_id
and nvl(bau.AP_DEFAULT_SETTLEMENT_FLAG,'N') = 'Y'
and bau.bank_acct_use_id <> nvl(p_bank_acct_use_id, bau.bank_acct_use_id);
select count(*) into p_xtr_def
from ce_bank_accounts ba, ce_bank_acct_uses_all bau
where ba.bank_account_id = bau.bank_account_id
and ba.currency_code = p_cur
and nvl(bau.org_id, bau.LEGAL_ENTITY_ID) = p_org_id
and nvl(bau.XTR_DEFAULT_SETTLEMENT_FLAG,'N') = 'Y'
and bau.bank_acct_use_id <> nvl(p_bank_acct_use_id, bau.bank_acct_use_id) ;
SELECT 'X'
FROM ce_bank_acct_uses_all
WHERE bank_account_id = p_bank_account_id
AND NVL(org_id, legal_entity_id) = p_org_le_id
AND bank_acct_use_id <> NVL(p_acct_use_id, -1);
select count(*) into p_pay_doc_count
from ce_payment_documents
where PAYMENT_DOCUMENT_ID = P_PAYMENT_DOCUMENT_ID;
select nvl(PAYMENT_DOC_CATEGORY, '-1')
into P_PAYMENT_DOC_CATEGORY_CODE
from ce_payment_documents
where PAYMENT_DOCUMENT_ID = P_PAYMENT_DOCUMENT_ID;
select count(*) into p_ap_doc_cat_count
from ce_ap_pm_doc_categories
where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID
and PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE;
select nvl(PAYMENT_DOC_CATEGORY, '-1')
into P_PAYMENT_DOC_CATEGORY_CODE
from ce_ap_pm_doc_categories
where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID
and PAYMENT_METHOD_CODE = P_PAYMENT_METHOD_CODE;
select nvl(PAYMENT_DOC_CATEGORY, '-1')
into P_PAYMENT_DOC_CATEGORY_CODE
from ce_bank_acct_uses_all
where BANK_ACCT_USE_ID = P_BANK_ACCT_USE_ID;
SELECT 'BG'
FROM hr_organization_information oi,
hr_lookups hl
WHERE oi.org_information_context = 'CLASS'
AND oi.org_information1 = 'HR_BG'
and hl.lookup_type = 'ORG_CLASS'
AND hl.lookup_code = oi.org_information1
and oi.organization_id = p_org_id;
SELECT 'OU'
FROM hr_organization_information oi,
hr_lookups hl
WHERE oi.org_information_context = 'CLASS'
AND oi.org_information1 = 'OPERATING_UNIT'
and hl.lookup_type = 'ORG_CLASS'
AND hl.lookup_code = oi.org_information1
and oi.organization_id = p_org_id;