Friday, 5 January 2018

Supplier Bank and Branches Creation API in Oracle Apps


In This post , I will share you the complete Script , which will use Oracle standard API to insert Supplier Bank and their Branches information in Oracle Apps.


Supplier Bank and Branches Creation Oracle API


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;

0 comments:

Post a Comment