create table xx_bank_conversion_tmp
(
row_id number,
gl_org_segment varchar2(3),
Bank_Name varchar2(300),
--Bank_country_code varchar2(10),
Branch_name varchar2(300),
--Branch_type varchar2(15),
Branch_routing_num varchar2(30),
Bank_account_name varchar2(300),
bank_Account_num varchar2(30),
--currency varchar2(3),
--multi_currency_allowed_flag varchar2(1),
--ap_use_allowed_flag varchar2(1),
--ar_use_allowed_flag varchar2(1),
process_flag varchar2(1)
);
CREATE OR REPLACE PROCEDURE xx_bank_conversion IS
g_init_msg_list VARCHAR2(300) := fnd_api.g_true; -- IN
g_bank_id NUMBER;
g_branch_id NUMBER;
g_acct_id NUMBER;
g_log_level integer;
CURSOR c1 is select * from xx_bank_conversion_tmp where process_flag = 'Y';
BEGIN
/*Author: Faraz Anwar*/
/*Dated: 03/25/2014 */
/*FND: Debug Log Enabled
Set this profile to Yes
FND: Debug Log Module
Set this to %
FND: Debug Log Level
Set this to Statement
*/
/*
LEVEL_UNEXPECTED : Internal Level Id is 6
LEVEL_ERROR : Internal Level Id is 5
LEVE_EXCEPTION : Internal Level Id is 4
LEVEL_EVENT : Internal Level Id is 3
LEVEL_PROCEDURE : Internal Level Id is 2
LEVEL_STATEMENT : Internal Level Id is 1
*/
DECLARE
l_resp_id NUMBER;
l_user_id NUMBER;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name LIKE 'FANWAR';
SELECT responsibility_id
INTO l_resp_id
FROM fnd_responsibility_vl
WHERE responsibility_name LIKE 'AR Inquiry Reports';
fnd_global.apps_initialize(l_user_id, l_resp_id, 222); --AR=222
mo_global.init('AR');
FND_LOG.MESSAGE(FND_LOG.LEVEL_STATEMENT, 'apps.plsql.xx_bank_conversion');
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'l_user_id '||l_user_id);
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'l_resp_id '||l_resp_id);
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.INIT', 'G_CURRENT_RUNTIME_LEVEL '||FND_LOG.G_CURRENT_RUNTIME_LEVEL);
END INIT;
FOR r1 in c1 LOOP
g_bank_id := null;
g_branch_id := null;
g_acct_id := null;
--
-- Creating Bank
--
DECLARE
l_country_code VARCHAR2(300); -- IN
l_bank_name VARCHAR2(300); -- IN
l_bank_number VARCHAR2(300); -- IN
l_alternate_bank_name VARCHAR2(300); -- IN
l_short_bank_name VARCHAR2(300); -- IN
l_description VARCHAR2(300); -- IN
l_tax_payer_id VARCHAR2(300); -- IN
l_tax_registration_number VARCHAR2(300); -- IN
l_bank_id NUMBER; -- OUT
l_acct_id NUMBER;
l_return_status VARCHAR2(300); -- OUT
l_msg_count NUMBER; -- OUT
l_msg_data VARCHAR2(300); -- OUT
BEGIN
l_country_code := 'US';
l_bank_name := r1.bank_name;
DECLARE
l_end_date DATE;
BEGIN
ce_bank_pub.check_bank_exist(p_country_code => l_country_code,
p_bank_name => l_bank_name,
p_bank_number => l_bank_number,
x_bank_id => l_bank_id,
x_end_date => l_end_date);
END;
IF l_bank_id is not null then
FND_LOG.STRING(fnd_log.level_exception,'apps.plsql.xx_bank_conversion.create_bank', 'Bank Exists l_bank_name ='||l_bank_name);
FND_LOG.STRING(FND_LOG.level_exception,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_id '||l_bank_id);
END IF;
IF l_bank_id IS NULL THEN
ce_bank_pub.create_bank(p_init_msg_list => g_init_msg_list, -- IN
p_country_code => l_country_code, -- IN
p_bank_name => l_bank_name, -- IN
p_bank_number => l_bank_number, -- IN
p_alternate_bank_name => l_alternate_bank_name, -- IN
p_short_bank_name => l_short_bank_name, -- IN
p_description => l_description, -- IN
p_tax_payer_id => l_tax_payer_id, -- IN
p_tax_registration_number => l_tax_registration_number, -- IN
x_bank_id => l_bank_id, -- OUT
x_return_status => l_return_status, -- OUT
x_msg_count => l_msg_count, -- OUT
x_msg_data => l_msg_data -- OUT
);
IF l_return_status = 'S' then
g_log_level := FND_LOG.LEVEL_EVENT;
ELSE
g_log_level := FND_LOG.LEVEL_EXCEPTION;
END IF;
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_id '||L_BANK_ID);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'l_bank_name '||l_bank_name);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_return_status '||l_return_status);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_msg_count '||L_MSG_COUNT);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank', 'x_msg_data '||L_MSG_DATA);
END IF;
g_bank_id := l_bank_id;
END bank;
--
-- Creating Branch
--
DECLARE
l_branch_name VARCHAR2(300); -- IN
l_branch_number VARCHAR2(300); -- IN
l_branch_type VARCHAR2(300); -- IN
l_alternate_branch_name VARCHAR2(300); -- IN
l_description VARCHAR2(300); -- IN
l_bic VARCHAR2(300); -- IN
l_eft_number VARCHAR2(300); -- IN
l_rfc_identifier VARCHAR2(300); -- IN
l_branch_id NUMBER; -- OUT
l_return_status VARCHAR2(300); -- OUT
l_msg_count NUMBER; -- OUT
l_msg_data VARCHAR2(300); -- OUT
BEGIN
l_branch_name := r1.branch_name;
l_branch_number := r1.branch_routing_num;
l_branch_type := 'ABA';
DECLARE
l_end_date DATE;
BEGIN
ce_bank_pub.check_branch_exist(p_bank_id => g_bank_id,
p_branch_name => l_branch_name,
p_branch_number => l_branch_number,
x_branch_id => l_branch_id,
x_end_date => l_end_date);
END;
IF l_branch_id is not null then
FND_LOG.STRING(fnd_log.level_exception,'apps.plsql.xx_bank_conversion.create_bank_branch', 'Branch Already Exists l_branch_name ='||l_branch_name);
FND_LOG.STRING(FND_LOG.level_exception,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_id '||L_BRANCH_ID);
END IF;
IF l_branch_id IS NULL THEN
ce_bank_pub.create_bank_branch(p_init_msg_list => g_init_msg_list, -- IN
p_bank_id => g_bank_id, -- IN
p_branch_name => l_branch_name, -- IN
p_branch_number => l_branch_number, -- IN
p_branch_type => l_branch_type, -- IN
p_alternate_branch_name => l_alternate_branch_name, -- IN
p_description => l_description, -- IN
p_bic => l_bic, -- IN
p_eft_number => l_eft_number, -- IN
p_rfc_identifier => l_rfc_identifier, -- IN
x_branch_id => l_branch_id, -- OUT
x_return_status => l_return_status, -- OUT
x_msg_count => l_msg_count, -- OUT
x_msg_data => l_msg_data -- OUT
);
IF l_return_status = 'S' then
g_log_level := FND_LOG.LEVEL_EVENT;
ELSE
g_log_level := FND_LOG.LEVEL_EXCEPTION;
END IF;
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_id '||L_BRANCH_ID);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'l_branch_name '||l_branch_name);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_msg_count '||L_MSG_COUNT);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_msg_data '||L_MSG_DATA);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_branch', 'x_return_status '||l_return_status);
END IF;
g_branch_id := l_branch_id;
--
-- Creating Bank Account
--
END branch;
DECLARE
l_acct_rec apps.ce_bank_pub.bankacct_rec_type;
l_return_status VARCHAR2(300); -- OUT
l_msg_count NUMBER; -- OUT
l_msg_data VARCHAR2(300); -- OUT
l_acct_id number;
BEGIN
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.create_bank_acct', 'Calling create_bank_acct');
l_acct_rec.bank_id := g_bank_id;
l_acct_rec.branch_id := g_branch_id;
l_acct_rec.account_owner_org_id := 102; --OU
l_acct_rec.account_owner_party_id := 1460411; --OU party id, xle_entity_profiles
l_acct_rec.account_classification := 'INTERNAL';
l_acct_rec.bank_account_name := r1.bank_account_name; --'';
l_acct_rec.bank_account_num := r1.bank_account_num; --'222222222';
l_acct_rec.currency := 'USD';
l_acct_rec.multi_currency_allowed_flag := 'N';
l_acct_rec.ap_use_allowed_flag := 'Y';
l_acct_rec.ar_use_allowed_flag := 'Y';
l_acct_rec.start_date := SYSDATE;
l_acct_rec.asset_code_combination_id := null;
l_acct_rec.masked_account_num := r1.bank_account_num;
l_acct_rec.zero_amount_allowed := 'N';
l_acct_rec.multi_currency_allowed_flag := 'N';
l_acct_rec.pooled_flag := 'N';
l_acct_rec.ap_amount_tolerance := 0;
l_acct_rec.ar_amount_tolerance := 0;
l_acct_rec.xtr_amount_tolerance := 0;
l_acct_rec.pay_amount_tolerance := 0;
l_acct_rec.ce_amount_tolerance := 0;
l_acct_rec.ap_percent_tolerance := 0;
l_acct_rec.ar_percent_tolerance := 0;
l_acct_rec.xtr_percent_tolerance := 0;
l_acct_rec.pay_percent_tolerance := 0;
l_acct_rec.ce_percent_tolerance := 0;
BEGIN
SELECT code_combination_id
INTO l_acct_rec.asset_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments =
'865-00-1111-00-000-X-00-0-00-00000';
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
ce_bank_pub.create_bank_acct(p_init_msg_list => g_init_msg_list,
p_acct_rec => l_acct_rec,
x_acct_id => l_acct_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
g_acct_id := l_acct_id;
IF l_return_status = 'S' then
g_log_level := FND_LOG.LEVEL_EVENT;
ELSE
g_log_level := FND_LOG.LEVEL_EXCEPTION;
END IF;
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'l_acct_id '||l_acct_id);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'l_account_name '||r1.bank_account_name);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_return_status '||l_return_status);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_msg_count '||L_MSG_COUNT);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct', 'x_msg_data '||L_MSG_DATA);
if l_return_status = 'E' then
CONTINUE;
END IF;
END bank_acct;
--
-- Assigning Bank Account Uses
--
DECLARE
l_acct_use_rec apps.ce_bank_pub.bankacct_use_rec_type;
l_acct_use_id NUMBER;
l_return_status VARCHAR2(300); -- OUT
l_msg_count NUMBER; -- OUT
l_msg_data VARCHAR2(300); -- OUT
BEGIN
l_acct_use_rec.bank_account_id := g_acct_id;
l_acct_use_rec.org_type := 'OU';
l_acct_use_rec.org_id := 102;
--l_acct_use_rec.legal_entity_id := 102;
l_acct_use_rec.ar_use_enable_flag := 'Y';
l_acct_use_rec.ap_use_enable_flag := 'Y';
--l_acct_use_rec.org_party_id := 1460411; --xla_entity_profiles
l_acct_use_rec.authorized_flag := 'N';
l_acct_use_rec.default_account_flag := 'N';
BEGIN
SELECT code_combination_id
INTO l_acct_use_rec.asset_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments =
'199-00-1111-00-000-X-00-0-00-00000';
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
l_acct_use_rec.ap_asset_ccid := l_acct_use_rec.asset_code_combination_id;
l_acct_use_rec.ar_asset_ccid := l_acct_use_rec.asset_code_combination_id;
FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'Calling create_bank_acct_use');
ce_bank_pub.create_bank_acct_use(p_init_msg_list => g_init_msg_list,
p_acct_use_rec => l_acct_use_rec,
x_acct_use_id => l_acct_use_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF l_return_status = 'S' then
g_log_level := FND_LOG.LEVEL_EVENT;
ELSE
g_log_level := FND_LOG.LEVEL_EXCEPTION;
END IF;
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'l_acct_use_id '||l_acct_use_id);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'l_acct_use_acct_name '||r1.bank_account_name);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_return_status '||l_return_status);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_msg_count '||L_MSG_COUNT);
FND_LOG.STRING(g_log_level,'apps.plsql.xx_bank_conversion.create_bank_acct_use', 'x_msg_data '||L_MSG_DATA);
IF l_return_status = 'E' then
CONTINUE;
END IF;
END bank_acct_use;
end loop;
END;
Run following SQL query to verify if any of the lines were not loaded successfully.
SELECT * FROM FND_LOG_MESSAGES WHERE MODULE LIKE 'apps.plsql.xx_bank_conversion%'
--and module not like 'apps.plsql.xx_bank_conversion.create_bank_branch'
--and module not like 'apps.plsql.xx_bank_conversion.create_bank'
and log_level = 4 order by log_sequence asc;
