Supplier Bank and Branches creation API in Oracle Apps R12

 

Supplier Bank and Branches creation API in Oracle Apps R12

API Used:

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

HZ_LOCATION_V2PUB.CREATE_LOCATION

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

IBY_EXT_BANKACCT_PUB.create_ext_bank

 

1 2 3 4 5 6 7 8 9 10 11 12

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;