Supplier bank interface in oracle apps r12
In this post , We will be discuss about Supplier bank interface in oracle apps r12. supplier Bank interface helps to import the supplier bank account details from external system into the oracle apps. We can upload the supplier banking informations from other sources into the oracle apps. Supplier bank interface helps to bulk import the supplier banking informations from external source into the oracle apps r12. Here below is the detail script of Supplier bank interface in oracle apps r12.
Details Script of Supplier bank interface in oracle apps r12
DECLARE
x_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
x_bank_id NUMBER;
x_return_status VARCHAR2(10);
x_msg_count number;
x_msg_data varchar2(256);
x_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
x_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
x_branch_id number;
x_acct_id number;
x_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
p_ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type ;
x_ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type ;
x_ext_payee_status_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type ;
l_ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type ;
l_ext_payee_id_rec_type IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Rec_Type ;
p_payee IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type ;
p_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type ;
p_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type ;
x_assign_id number;
V_BANK_PARTY_ID NUMBER;
V_BANK_CREATION_FLAG VARCHAR2(40);
p_BANK_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_BRANCH_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_BANK_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
p_BRANCH_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2(2000);
x_location_id NUMBER;
V_COUNT NUMBER;
V_ERROR_FLAG VARCHAR2(40);
V_ERROR_MESSAGE VARCHAR2(4000);
v_TERRITORY_CODE VARCHAR2(40);
CURSOR SUPPLIER_BANK IS
SELECT ROWID, xrs.* FROM XXAP__SUPPLIER_BANK_STG XRS
WHERE PROCESSED_FLAG = 'N'
AND ACTION_TYPE='INSERT';
begin
dbms_output.put_line('Start of procedure.');
fnd_global.apps_initialize (fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id
);
dbms_output.put_line('Procedure initializad.');
FOR I IN SUPPLIER_BANK LOOP
V_BANK_CREATION_FLAG:='N';
V_ERROR_FLAG:='N';
V_ERROR_MESSAGE:=NULL;
v_TERRITORY_CODE :=NULL;
x_branch_id:=0;
x_bANK_id:=0;
x_LOCATION_id:=0;
V_BANK_PARTY_ID:=0;
begin
select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories
where UPPER(TERRITORY_CODE)=UPPER(i.BANK_COUNTRY);
exception when others then
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist';
end;
BEGIN
SELECT BANK_PARTY_ID INTO V_BANK_PARTY_ID FROM CE_BANKS_V
WHERE UPPER(BANK_NAME)=UPPER(I.BANK_NAME)
AND UPPER(HOME_COUNTRY)=UPPER(v_TERRITORY_CODE)
AND ROWNUM=1;
EXCEPTION WHEN NO_DATA_FOUND THEN
x_bank_rec.bank_name := I.BANK_NAME;
x_bank_rec.bank_alt_name:=I.BANK_NAME_ALT;
x_bank_rec.bank_number := I.BANK_NUM;
x_bank_rec.institution_type := 'BANK'; -- hz_code_assignments .CLASS_CODE
x_bank_rec.country_code :=I.BANK_COUNTRY ;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
dbms_output.put_line('before External bank creation.');
IBY_EXT_BANKACCT_PUB.create_ext_bank
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_rec => x_bank_rec
,x_bank_id => x_bank_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('External bank created.');
else
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:='Creation of BANK failed:'||x_msg_data;
END IF;
IF V_ERROR_FLAG='N' THEN
BEGIN
p_bank_location_rec.country := I.BANK_COUNTRY;
p_bank_location_rec.address1 := I.BANK_ADDRESS_1;
p_bank_location_rec.address2 := I.BANK_ADDRESS_2;
p_bank_location_rec.address3 := I.BANK_ADDRESS_3;
p_bank_location_rec.city := I.BANK_CITY;
p_bank_location_rec.postal_code := I.BANK_POSTAL_CODE;
p_bank_location_rec.state := I.BANK_STATE;
p_bank_location_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.create_location');
HZ_LOCATION_V2PUB.CREATE_LOCATION
(
p_init_msg_list => FND_API.G_TRUE,
p_location_rec => p_bank_location_rec,
x_location_id => x_location_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_id);
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);
ELSE
DBMS_OUTPUT.put_line ('Creation of Location failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK ADDRESS FAIL'||x_msg_data;
V_ERROR_FLAG:='Y';
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
-- V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK ADDRESS FAIL'||x_msg_data;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
IF V_ERROR_FLAG='N' THEN
IF x_bank_id>0 AND x_location_id>0 THEN
BEGIN
-- Setting the Context --
--mo_global.init('AR');
--d_global.set_nls_context('AMERICAN');
-- Initializing the Mandatory API parameters
p_BANK_party_site_rec.party_id := x_bank_id;
p_BANK_party_site_rec.location_id := x_location_id;
p_BANK_party_site_rec.identifying_address_flag := 'Y';
p_BANK_party_site_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
(
p_init_msg_list => FND_API.G_TRUE,
p_party_site_rec => p_BANK_party_site_rec,
x_party_site_id => x_party_site_id,
x_party_site_number => x_party_site_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
-- COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Party Site Id = '||x_party_site_id);
DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number);
ELSE
DBMS_OUTPUT.put_line ('Creation of Party Site failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK ADDRESS FAIL'||x_msg_data;
ROLLBACK;
V_ERROR_FLAG:='Y';
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
END IF;
V_BANK_PARTY_ID:=x_bank_id;
IF NVL(x_bank_id,0)>0 THEN
V_BANK_CREATION_FLAG:='Y';
END IF;
END;
IF V_ERROR_FLAG='N' THEN
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM CE_BANK_BRANCHES_V
where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME)
AND UPPER(BANK_NAME)=UPPER(I.BANK_NAME)
AND UPPER(BANK_HOME_COUNTRY)=UPPER(v_TERRITORY_CODE)
AND ROWNUM=1;
EXCEPTION WHEN OTHERS THEN
V_COUNT:=0;
END;
IF V_COUNT>0 AND V_BANK_CREATION_FLAG='N' THEN
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'BANK AND BRANCH IS ALREADY EXSIST';
END IF;
IF V_COUNT=0 THEN
x_bank_branch_rec.bank_party_id := V_BANK_PARTY_ID ;
x_bank_branch_rec.branch_name := I.BRANCH_NAME ;
x_bank_branch_rec.alternate_branch_name:=i.BANK_BRANCH_NAME_ALT;
x_bank_branch_rec.branch_number := I.BRANCH_NUM ;
x_bank_branch_rec.bic:=I.BIC;
x_bank_branch_rec.branch_type := 'OTHER' ; --defined in lookup as BANK BRANCH TYPE
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_branch_rec => x_bank_branch_rec
,x_branch_id => x_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);
dbms_output.put_line('External bank Branch created.');
dbms_output.put_line('x_branch_id: '||x_branch_id);
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 = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('External bank BRANCH created.');
else
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK BRANCH failed:'||x_msg_data;
END IF;
END IF;
END IF;
IF V_ERROR_FLAG='N' THEN
BEGIN
p_BRANCH_location_rec.country := I.BRANCH_COUNTRY;
p_BRANCH_location_rec.address1 := I.BRANCH_ADDRESS_1;
p_BRANCH_location_rec.address2 := I.BRANCH_ADDRESS_2;
p_BRANCH_location_rec.address3 := I.BRANCH_ADDRESS_3;
p_BRANCH_location_rec.city := I.BRANCH_CITY;
p_BRANCH_location_rec.postal_code := I.BRANCH_POSTAL_CODE;
p_BRANCH_location_rec.state := I.BRANCH_STATE;
p_BRANCH_location_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.create_location');
HZ_LOCATION_V2PUB.CREATE_LOCATION
(
p_init_msg_list => FND_API.G_TRUE,
p_location_rec => p_BRANCH_location_rec,
x_location_id => x_location_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_id);
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);
ELSE
DBMS_OUTPUT.put_line ('Creation of Location failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BRANCH ADDRESS FAIL'||x_msg_data;
V_ERROR_FLAG:='Y';
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
IF V_ERROR_FLAG='N' THEN
IF x_branch_id>0 AND x_location_id>0 THEN
BEGIN
-- Setting the Context --
--mo_global.init('AR');
--d_global.set_nls_context('AMERICAN');
-- Initializing the Mandatory API parameters
p_BRANCH_party_site_rec.party_id := x_branch_id;
p_BRANCH_party_site_rec.location_id := x_location_id;
p_BRANCH_party_site_rec.identifying_address_flag := 'Y';
p_BRANCH_party_site_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
(
p_init_msg_list => FND_API.G_TRUE,
p_party_site_rec => p_BRANCH_party_site_rec,
x_party_site_id => x_party_site_id,
x_party_site_number => x_party_site_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
-- COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Party Site Id = '||x_party_site_id);
DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number);
ELSE
DBMS_OUTPUT.put_line ('Creation of Party Site failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BRANCH ADDRESS FAIL'||x_msg_data;
ROLLBACK;
V_ERROR_FLAG:='Y';
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
END IF;
IF V_ERROR_FLAG='Y' THEN
ROLLBACK;
UPDATE XXAP__SUPPLIER_BANK_STG
SET PROCESSED_FLAG = 'N',
ERROR_DESCRIPTION = V_ERROR_MESSAGE
WHERE ROWID = I.ROWID;
COMMIT;
ELSE
UPDATE XXAP_SUPPLIER_BANK_STG
SET PROCESSED_FLAG = 'Y'
WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
END IF;
Script to Create Supplier Bank Accounts
DECLARE
x_bank_rec iby_ext_bankacct_pub.extbank_rec_type;
x_bank_id NUMBER;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (256);
x_response_rec iby_fndcpt_common_pub.result_rec_type;
x_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;
x_branch_id NUMBER;
x_acct_id NUMBER;
x_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
p_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type;
x_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type;
x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_create_tab_type;
l_ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;
l_ext_payee_id_rec_type iby_disbursement_setup_pub.ext_payee_id_rec_type;
p_payee iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type;
x_assign_id NUMBER;
v_bank_party_id NUMBER;
v_branch_party_id NUMBER;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
v_party_site_id NUMBER;
V_EXT_BANK_ACCOUNT_ID NUMBER;
v_bank_creation_flag VARCHAR2 (40);
v_error_flag VARCHAR2 (40);
v_error_message VARCHAR2 (4000);
v_party_id number;
v_TERRITORY_CODE VARCHAR2(400);
x_joint_owner_id number;
CURSOR supplier_bank
IS
SELECT ROWID, xrs.*
FROM XXAP_SUPP_BANK_ACCOUNT_STG xrs
WHERE processed_flag = 'N';
BEGIN
DBMS_OUTPUT.put_line ('Start of procedure.');
fnd_global.apps_initialize (fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id
);
DBMS_OUTPUT.put_line ('Procedure initializad.');
FOR i IN supplier_bank
LOOP
v_bank_creation_flag := 'N';
v_error_flag := 'N';
v_error_message := NULL;
v_bank_party_id:=NULL;
V_branch_party_id:=NULL;
v_ext_bank_account_id:=NULL;
v_vendor_id:=NULL;
v_party_id:=NULL;
v_vendor_site_id:=NULL;
v_party_site_id:=NULL;
v_TERRITORY_CODE:=NULL;
begin
select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories
where UPPER(TERRITORY_CODE)=UPPER(i.COUNTRY);
exception when others then
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist';
end;
BEGIN
SELECT bank_party_id
INTO v_bank_party_id
FROM ce_banks_v
WHERE UPPER(bank_name)= UPPER(i.bank_name)
and upper(HOME_COUNTRY)=UPPER(I.COUNTRY)
AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_flag := 'Y';
v_error_message := 'BANK IS NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT branch_party_id
INTO v_branch_party_id
FROM ce_bank_branches_v
where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME)
AND UPPER(bank_name)=UPPER(I.BANK_NAME)
AND UPPER(BANK_HOME_COUNTRY)=UPPER(I.COUNTRY)
AND ROWNUM=1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'BRANCH IS NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT ext_bank_account_id
INTO v_ext_bank_account_id
FROM iby_ext_bank_accounts
WHERE bank_id = v_bank_party_id
AND branch_id = v_branch_party_id
AND bank_account_num = i.bank_account_number
AND UPPER(bank_account_name) = UPPER(i.bank_account_name);
EXCEPTION
WHEN no_data_found
THEN
v_ext_bank_account_id:=null;
when others then
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'ERROR IN THE SYSTEM BANK ACCOUNT NUMBER FETCH';
END;
BEGIN
SELECT vendor_id
INTO v_vendor_id
FROM po_vendors
WHERE upper(vendor_name) = upper(i.vendor_name)
and segment1=I.ATTRIBUTE1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT party_id
INTO v_party_id
FROM po_vendors
WHERE upper(vendor_name) =upper(i.vendor_name)
and segment1=I.ATTRIBUTE1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
END;
IF i.vendor_site_code IS NOT NULL THEN
BEGIN
SELECT vendor_site_id,party_site_id
INTO v_vendor_site_id,v_party_site_id
FROM ap_supplier_sites_all
WHERE UPPER(vendor_site_code) = UPPER(i.vendor_site_code)
AND ORG_ID=p_operating_unit
AND vendor_id = v_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR SITE NOT EXIST IN THE SYSTEM';
END;
END IF;
IF v_error_flag = 'N' AND v_ext_bank_account_id IS NULL
THEN
x_bank_acct_rec.country_code := I.COUNTRY ;
x_bank_acct_rec.branch_id := v_branch_party_id;
x_bank_acct_rec.bank_id := v_bank_party_id;
x_bank_acct_rec.acct_owner_party_id := v_party_id;
--supplier party id
x_bank_acct_rec.iban := i.iban;
x_bank_acct_rec.currency := i.currency;
x_bank_acct_rec.bank_account_name := i.bank_account_name;
x_bank_acct_rec.bank_account_num := i.bank_account_number;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
iby_ext_bankacct_pub.create_ext_bank_acct
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => x_bank_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,
x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('External bank account created.');
DBMS_OUTPUT.put_line ('x_acct_id' || x_acct_id);
DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count);
x_bank_rec iby_ext_bankacct_pub.extbank_rec_type;
x_bank_id NUMBER;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (256);
x_response_rec iby_fndcpt_common_pub.result_rec_type;
x_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;
x_branch_id NUMBER;
x_acct_id NUMBER;
x_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
p_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type;
x_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type;
x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_create_tab_type;
l_ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type;
l_ext_payee_id_rec_type iby_disbursement_setup_pub.ext_payee_id_rec_type;
p_payee iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type;
x_assign_id NUMBER;
v_bank_party_id NUMBER;
v_branch_party_id NUMBER;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
v_party_site_id NUMBER;
V_EXT_BANK_ACCOUNT_ID NUMBER;
v_bank_creation_flag VARCHAR2 (40);
v_error_flag VARCHAR2 (40);
v_error_message VARCHAR2 (4000);
v_party_id number;
v_TERRITORY_CODE VARCHAR2(400);
x_joint_owner_id number;
CURSOR supplier_bank
IS
SELECT ROWID, xrs.*
FROM XXAP_SUPP_BANK_ACCOUNT_STG xrs
WHERE processed_flag = 'N';
BEGIN
DBMS_OUTPUT.put_line ('Start of procedure.');
fnd_global.apps_initialize (fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id
);
DBMS_OUTPUT.put_line ('Procedure initializad.');
FOR i IN supplier_bank
LOOP
v_bank_creation_flag := 'N';
v_error_flag := 'N';
v_error_message := NULL;
v_bank_party_id:=NULL;
V_branch_party_id:=NULL;
v_ext_bank_account_id:=NULL;
v_vendor_id:=NULL;
v_party_id:=NULL;
v_vendor_site_id:=NULL;
v_party_site_id:=NULL;
v_TERRITORY_CODE:=NULL;
begin
select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories
where UPPER(TERRITORY_CODE)=UPPER(i.COUNTRY);
exception when others then
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist';
end;
BEGIN
SELECT bank_party_id
INTO v_bank_party_id
FROM ce_banks_v
WHERE UPPER(bank_name)= UPPER(i.bank_name)
and upper(HOME_COUNTRY)=UPPER(I.COUNTRY)
AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error_flag := 'Y';
v_error_message := 'BANK IS NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT branch_party_id
INTO v_branch_party_id
FROM ce_bank_branches_v
where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME)
AND UPPER(bank_name)=UPPER(I.BANK_NAME)
AND UPPER(BANK_HOME_COUNTRY)=UPPER(I.COUNTRY)
AND ROWNUM=1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'BRANCH IS NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT ext_bank_account_id
INTO v_ext_bank_account_id
FROM iby_ext_bank_accounts
WHERE bank_id = v_bank_party_id
AND branch_id = v_branch_party_id
AND bank_account_num = i.bank_account_number
AND UPPER(bank_account_name) = UPPER(i.bank_account_name);
EXCEPTION
WHEN no_data_found
THEN
v_ext_bank_account_id:=null;
when others then
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'ERROR IN THE SYSTEM BANK ACCOUNT NUMBER FETCH';
END;
BEGIN
SELECT vendor_id
INTO v_vendor_id
FROM po_vendors
WHERE upper(vendor_name) = upper(i.vendor_name)
and segment1=I.ATTRIBUTE1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
END;
BEGIN
SELECT party_id
INTO v_party_id
FROM po_vendors
WHERE upper(vendor_name) =upper(i.vendor_name)
and segment1=I.ATTRIBUTE1;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM';
END;
IF i.vendor_site_code IS NOT NULL THEN
BEGIN
SELECT vendor_site_id,party_site_id
INTO v_vendor_site_id,v_party_site_id
FROM ap_supplier_sites_all
WHERE UPPER(vendor_site_code) = UPPER(i.vendor_site_code)
AND ORG_ID=p_operating_unit
AND vendor_id = v_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_error_flag := 'Y';
v_error_message :=
v_error_message || 'VENDOR SITE NOT EXIST IN THE SYSTEM';
END;
END IF;
IF v_error_flag = 'N' AND v_ext_bank_account_id IS NULL
THEN
x_bank_acct_rec.country_code := I.COUNTRY ;
x_bank_acct_rec.branch_id := v_branch_party_id;
x_bank_acct_rec.bank_id := v_bank_party_id;
x_bank_acct_rec.acct_owner_party_id := v_party_id;
--supplier party id
x_bank_acct_rec.iban := i.iban;
x_bank_acct_rec.currency := i.currency;
x_bank_acct_rec.bank_account_name := i.bank_account_name;
x_bank_acct_rec.bank_account_num := i.bank_account_number;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
iby_ext_bankacct_pub.create_ext_bank_acct
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => x_bank_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,
x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('External bank account created.');
DBMS_OUTPUT.put_line ('x_acct_id' || x_acct_id);
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);
ELSE
DBMS_OUTPUT.put_line
( 'Creation of BANK ACCCOUNT failed:'
|| x_msg_data
);
ROLLBACK;
v_error_flag := 'Y';
v_error_message :=
v_error_message
|| 'Creation of BANK ACCCOUNT failed:'
|| x_msg_data;
END IF;
l_ext_payee_rec.payee_party_site_id := v_party_site_id;
l_ext_payee_rec.payee_party_id := v_party_id;
l_ext_payee_rec.payment_function := 'PAYABLES_DISB';
IF i.vendor_site_code IS NOT NULL THEN
l_ext_payee_rec.payer_org_id := p_operating_unit;
l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';
ELSE
l_ext_payee_rec.payer_org_id := NULL;
l_ext_payee_rec.payer_org_type := NULL;
END IF;
l_ext_payee_rec.exclusive_pay_flag := 'N';
l_ext_payee_rec.default_pmt_method :=
NVL ('CHECK', i.payment_method);
l_ext_payee_rec.supplier_site_id := v_vendor_site_id;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
p_ext_payee_tab (0) := l_ext_payee_rec;
iby_disbursement_setup_pub.create_external_payee
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_payee_tab => p_ext_payee_tab,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_id_tab => x_ext_payee_id_tab,
x_ext_payee_status_tab => x_ext_payee_status_tab
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('External Payee created.');
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);
ELSE
DBMS_OUTPUT.put_line ( 'Creation of payee failed:'
|| x_msg_data
);
v_error_message :=
v_error_message
|| 'Creation of payee failed:'
|| x_msg_data;
ROLLBACK;
v_error_flag := 'Y';
END IF;
p_payee.supplier_site_id := v_vendor_site_id;
p_payee.party_id :=v_party_id;
p_payee.party_site_id := v_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
IF i.vendor_site_code IS NOT NULL THEN
p_payee.org_id := p_operating_unit;
p_payee.org_type := 'OPERATING_UNIT';
ELSE
p_payee.org_id := NULL;
p_payee.org_type := NULL;
END IF;
l_ext_payee_id_rec_type := x_ext_payee_id_tab (0);
p_instrument.instrument_id := x_acct_id;
p_instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.priority := 1;
p_assignment_attribs.instrument := p_instrument;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
x_response_rec := NULL;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');
DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);
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);
ELSE
DBMS_OUTPUT.put_line
( 'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data
);
v_error_message:=
v_error_message||'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data;
ROLLBACK;
v_error_flag := 'Y';
END IF;
END IF;
IF v_error_flag = 'N' AND v_ext_bank_account_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('CHECKING THAT VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status);
BEGIN
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
x_response_rec := NULL;
IBY_EXT_BANKACCT_PUB.check_bank_acct_owner
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_bank_acct_id => v_ext_bank_account_id,
p_acct_owner_party_id =>v_party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response_rec
);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('ERROR IN CHECKING VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status);
END;
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('already owner');
p_payee.supplier_site_id := v_vendor_site_id;
p_payee.party_id :=v_party_id;
p_payee.party_site_id := v_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
IF i.vendor_site_code IS NOT NULL THEN
p_payee.org_id := p_operating_unit;
p_payee.org_type := 'OPERATING_UNIT';
ELSE
p_payee.org_id := NULL;
p_payee.org_type := NULL;
END IF;
p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id;
p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.priority := 1;
-- p_assignment_attribs.instrument := p_instrument;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
x_response_rec := NULL;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');
DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);
DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);
else
DBMS_OUTPUT.put_line
( 'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data
);
v_error_message:=
v_error_message||'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data;
ROLLBACK;
v_error_flag := 'Y';
end if;
ELSE
DBMS_OUTPUT.put_line
( 'Creation of joint bank acct owner'
);
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
x_response_rec := NULL;
IBY_EXT_BANKACCT_PUB.add_joint_account_owner
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_bank_account_id => v_ext_bank_account_id,
p_acct_owner_party_id => v_party_id,
x_joint_acct_owner_id =>x_joint_owner_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data =>x_msg_data,
x_response=> x_response_rec);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('joint acct created');
p_payee.supplier_site_id := v_vendor_site_id;
p_payee.party_id :=v_party_id;
p_payee.party_site_id := v_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
IF i.vendor_site_code IS NOT NULL THEN
p_payee.org_id := p_operating_unit;
p_payee.org_type := 'OPERATING_UNIT';
ELSE
p_payee.org_id := NULL;
p_payee.org_type := NULL;
END IF;
p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id;
p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.priority := 1;
x_msg_count := 0;
x_msg_data := NULL;
x_return_status := NULL;
x_response_rec := NULL;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response_rec
);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.');
DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id);
DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);
else
DBMS_OUTPUT.put_line
( 'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data
);
v_error_message:=
v_error_message||'Creation of Payee_Instr_Assignment failed:'
|| x_msg_data;
ROLLBACK;
v_error_flag := 'Y';
end if;
else
DBMS_OUTPUT.put_line
( 'Creation of joint account owner failed'
|| x_msg_data
);
v_error_message:=
v_error_message||'Creation of joint account owner failed'
|| x_msg_data;
ROLLBACK;
v_error_flag := 'Y';
end if;
end if;
end if;
IF v_error_flag = 'Y'
THEN
ROLLBACK;
UPDATE XXAP_SUPP_BANK_ACCOUNT_STG
SET processed_flag = 'N',
error_description = v_error_message
WHERE ROWID = i.ROWID;
COMMIT;
ELSE
UPDATE XXAP_SUPP_BANK_ACCOUNT_STG
SET processed_flag = 'Y'
WHERE ROWID = i.ROWID;
COMMIT;
END IF;
END LOOP;
END;
0 comments:
Post a Comment