DECLARE
p_init_msg_list VARCHAR2 (200);
p_acct_rec apps.ce_bank_pub.bankacct_rec_type;
x_acct_id NUMBER;
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_count NUMBER;
p_segments APPS.FND_FLEX_EXT.SEGMENTARRAY;
p_segments2 APPS.FND_FLEX_EXT.SEGMENTARRAY;
p_code_combination_id NUMBER;
p_code_combination_id2 NUMBER;
l_msg_index number;
CURSOR l_data IS
select
bank_account_id,
bank_account_name,
bank_account_num ,
bank_branch_id,
cba.branch_number ,
cba.bank_number,
( select bank_branch_name from ce_bank_branches_v xbb where xbb.branch_number=cba.branch_number and xbb.bank_number=cba.bank_number) new_bank_branch_name,
( select branch_party_id from ce_bank_branches_v xbb where xbb.branch_number=cba.branch_number and xbb.bank_number=cba.bank_number) new_bank_branch_id,
bank_id,
(select bank_name from ce_banks_v xxb where xxb.bank_number=cba.bank_number) new_bank_name,
(select bank_party_id from ce_banks_v xxb where xxb.bank_number=cba.bank_number) new_bank_id,
currency_code,
iban_number,
short_account_name,
account_owner_party_id,
cba.ACCOUNT_OWNER_PARTY_NAME,
(SELECT party_id
-- INTO ln_acct_owner_party_id
FROM hz_parties
WHERE party_name = cba.ACCOUNT_OWNER_PARTY_NAME and rownum=1) NEW_ACCOUNT_OWNER_PARTY_ID,
account_owner_org_id,
cba.account_owner_org_name,
(select DEFAULT_LEGAL_CONTEXT_ID from hr_operating_units where name=cba.account_owner_org_name) new_account_owner_org_id,
account_classification,
ap_use_allowed_flag,
ar_use_allowed_flag,
XTR_USE_ALLOWED_FLAG,
PAY_USE_ALLOWED_FLAG,
multi_currency_allowed_flag,
PAYMENT_MULTI_CURRENCY_FLAG,
ZERO_AMOUNT_ALLOWED,
AP_AMOUNT_TOLERANCE,
AR_AMOUNT_TOLERANCE,
PAY_AMOUNT_TOLERANCE,
AP_PERCENT_TOLERANCE,
AR_PERCENT_TOLERANCE,
BANK_ACCOUNT_TYPE,
start_date,
POOLED_FLAG,
MASKED_ACCOUNT_NUM,
MASKED_IBAN,
CE_AMOUNT_TOLERANCE,
CE_PERCENT_TOLERANCE,
ASSET_CODE_COMBINATION_ID,
segment1,
'99999999999' segment2,
segment3,
DECODE(segment4,'00000000','0')segment4,
segment5,
segment6,
segment7,
CASH_CLEARING_CCID,
segment8,
segment9,
segment10,
DECODE(segment11,'00000000','0')segment11,
segment12,
segment13,
segment14,
object_version_number,
NETTING_ACCT_FLAG,
BANK_ACCOUNT_NUM_ELECTRONIC,
STMT_LINE_FLOAT_HANDLING_FLAG,
AUTORECON_AP_MATCHING_ORDER,
AUTORECON_AR_MATCHING_ORDER,
RECON_FOREIGN_BANK_XRATE_TYPE,
RECON_FOR_BANK_XRATE_DATE_TYPE,
RECON_ENABLE_OI_FLAG,
RECON_OI_MATCHING_CODE,
RECON_OI_AMOUNT_TOLERANCE,
RECON_OI_PERCENT_TOLERANCE,
XTR_BANK_ACCOUNT_REFERENCE,
AUTORECON_AP_MATCHING_ORDER2,
status
from XX.XX_CE_BANK_ACCOUNTS_ALL cba
where 1=1
and status is not null;
l_concat varchar2(500);
l_code_comb_count number;
BEGIN
fnd_global.apps_initialize(
user_id=>1110,
resp_id=>50779,
resp_appl_id=>200);
FOR n_data IN l_data LOOP
x_acct_id :=null;
l_code_comb_count :=null;
IF n_data.ASSET_CODE_COMBINATION_ID is not null THEN
p_segments (1) := n_data.segment1;
p_segments (2) := n_data.segment2;
p_segments (3) := n_data.segment3;
p_segments (4) := n_data.segment4;
p_segments (5) := n_data.segment5;
p_segments (6) := n_data.segment6;
p_segments (7) := n_data.segment7;
p_segments (8) := '00000';
p_segments (9) := '00000';
p_segments (10) := '00000';
p_segments (11) := '0';
p_segments (12) := '0';
p_code_combination_id :=
xx_get_ccid (50388,
SYSDATE,
12,
p_segments);
SELECT COUNT (1) INTO l_code_comb_count
FROM XTR_GL_REFERENCES
WHERE CODE_COMBINATION_ID = p_code_combination_id;
DBMS_OUTPUT.put_line ('l_code_comb_count: ' || l_code_comb_count ||'p_code_combination_id= ' || p_code_combination_id || 'segment2= '|| n_data.segment2);
IF l_code_comb_count =0 THEN
SELECT CONCATENATED_SEGMENTS
INTO l_concat
FROM gl_code_combinations_kfv
WHERE code_combination_id = p_code_combination_id;
INSERT INTO XTR_GL_REFERENCES (AUDIT_INDICATOR,
COMPANY_CODE,
CODE_COMBINATION_ID,
CREATED_BY,
CREATED_ON,
UPDATED_BY,
UPDATED_ON,
TREASURY_REFERENCE)
VALUES (NULL,
'AVIVASA',
p_code_combination_id,
'NATICA',
TO_DATE ('01-01-2020', 'DD-MM-YYYY'),
NULL,
NULL,
l_concat);
COMMIT;
END IF;
DBMS_OUTPUT.put_line ('1-' || fnd_flex_ext.GET_MESSAGE || ' -- ccid= '||p_code_combination_id || ' bank_account_id=' || n_data.bank_account_id || ' BANK_ACCOUNT_NAME=' || n_data.BANK_ACCOUNT_NAME);
--DBMS_OUTPUT.PUT_LINE ('v_requistion_rec.segment10:' || v_requistion_rec.segment10 );
-- ELSE
-- p_code_combination_id :=NULL;
END IF;
p_init_msg_list := fnd_api.g_true;
p_acct_rec.branch_id := n_data.new_bank_branch_id ;
p_acct_rec.bank_id := n_data.new_bank_id;
p_acct_rec.account_owner_org_id := n_data.new_account_owner_org_id ;
p_acct_rec.account_owner_party_id := n_data.new_account_owner_party_id;
p_acct_rec.account_classification := n_data.account_classification;
p_acct_rec.bank_account_name := n_data.bank_account_name;
p_acct_rec.bank_account_num := n_data.bank_account_num ;
p_acct_rec.currency := n_data.currency_code;
p_acct_rec.start_date := n_data.start_date;
p_acct_rec.iban := n_data.iban_number;
p_acct_rec.short_account_name := n_data.SHORT_ACCOUNT_NAME;
p_acct_rec.ap_use_allowed_flag := n_data.ap_use_allowed_flag;
p_acct_rec.AR_USE_ALLOWED_FLAG := n_data.AR_USE_ALLOWED_FLAG;
p_acct_rec.XTR_USE_ALLOWED_FLAG := n_data.XTR_USE_ALLOWED_FLAG;
p_acct_rec.PAY_USE_ALLOWED_FLAG := n_data.PAY_USE_ALLOWED_FLAG;
p_acct_rec.multi_currency_allowed_flag := n_data.multi_currency_allowed_flag;
p_acct_rec.payment_multi_currency_flag := n_data.payment_multi_currency_flag;
p_acct_rec.zero_amount_allowed := n_data.zero_amount_allowed;
p_acct_rec.ap_amount_tolerance := n_data.ap_amount_tolerance;
p_acct_rec.ar_amount_tolerance := n_data.ar_amount_tolerance;
p_acct_rec.pay_amount_tolerance := n_data.pay_amount_tolerance;
p_acct_rec.ap_percent_tolerance := n_data.ap_percent_tolerance;
p_acct_rec.ar_percent_tolerance := n_data.ar_percent_tolerance;
--bank_account_type apide yok
p_acct_rec.pooled_flag := n_data.pooled_flag;
p_acct_rec.masked_account_num := n_data.masked_account_num;
p_acct_rec.masked_iban := n_data.masked_iban;
p_acct_rec.ce_amount_tolerance := n_data.ce_amount_tolerance;
p_acct_rec.ce_percent_tolerance := n_data.ce_percent_tolerance;
p_acct_rec.asset_code_combination_id := p_code_combination_id;
p_acct_rec.cash_clearing_ccid := p_code_combination_id;
p_acct_rec.netting_acct_flag := n_data.netting_acct_flag;
--bank_account_num_electronic apide yok
p_acct_rec.stmt_line_float_handling_flag := n_data.stmt_line_float_handling_flag;
p_acct_rec.autorecon_ap_matching_order := n_data.autorecon_ap_matching_order;
p_acct_rec.autorecon_ar_matching_order := n_data.autorecon_ar_matching_order;
p_acct_rec.recon_foreign_bank_xrate_type := n_data.recon_foreign_bank_xrate_type;
p_acct_rec.recon_for_bank_xrate_date_type := n_data.recon_for_bank_xrate_date_type;
p_acct_rec.recon_enable_oi_flag := n_data.recon_enable_oi_flag;
p_acct_rec.recon_oi_matching_code := n_data.recon_oi_matching_code;
p_acct_rec.recon_oi_amount_tolerance := n_data.recon_oi_amount_tolerance;
p_acct_rec.recon_oi_percent_tolerance := n_data.recon_oi_percent_tolerance;
p_acct_rec.xtr_bank_account_reference := n_data.xtr_bank_account_reference;
p_acct_rec.autorecon_ap_matching_order2 := n_data.autorecon_ap_matching_order2;
CE_BANK_PUB.CREATE_BANK_ACCT (p_init_msg_list => p_init_msg_list,
p_acct_rec => p_acct_rec,
x_acct_id => x_acct_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF x_return_status IN ('S') THEN
update XX.XX_CE_BANK_ACCOUNTS_ALL set status='UPDATE', new_bank_account_id=x_acct_id where bank_account_id= n_data.bank_account_id;
COMMIT;
END IF;
DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
IF x_return_status IN ('E')
THEN
DBMS_OUTPUT.put_line ('x_msg_count:' || x_msg_count);
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
x_msg_data,
l_msg_index);
x_msg_data := x_msg_data || 'Bank Account API Error ';
DBMS_OUTPUT.PUT_LINE ('Error :- ' || x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.put_line ('x_msg_data:' || x_msg_data);
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('x_msg_count:' || SQLERRM);
END;
No comments:
Post a Comment