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;

5 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Good blog, Thanks for sharing this informative article. It would be helpful to all if you write a full article.
Oracle Fusion HCM Online Training"

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

yamuna said...
This comment has been removed by the author.
Goutham Raj said...

Good Article, Alots of information provided, Thanks for sharing this information.
Spark Scala Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *