Learn Oracle ebs R12: February 2022

how to Clear the Oracle Applications cache

Clear the Oracle Applications cache

Every time you change user assignments to a user group, clear the Oracle Applications cache so that users logging into Oracle eBusiness Suite R12 see only the appropriate options. (Note that if you do not clear the cache, users may be able to see the wrong options but they cannot actually do them.)

Log in as an Oracle ebs R12 user with the Functional Administrator responsibility.
The Security tab's Grants screen appears.
Note:
You can also log in as the sysadmin user if you do not want to assign the Functional Administrator responsibility only for the purpose of clearing cache.
Click the Core Services tab from the Grants screen. The Lookup Types screen appears.
Click Caching Framework from the list of secondary tabs. The Overview screen opens.
Click Global Configuration from the left panel. The Global Cache Configuration screen opens.
Click Clear All Cache. A warning appears.
Click Yes.

Tables Used by Concurrent Request Concurrent Program

FND_CONCURRENT_REQUESTS

Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).

FND_RUN_REQUESTS

Stores information about the reports in a report set that a user submits including the report set’s parameter values.

FND_CONC_REQUEST_ARGUMENTS

Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.

FND_DUAL

Records when a request does not update any database tables.

FND_CONCURRENT_PROCESSES

Records information about Oracle Applications processes and OS processes.

FND_CONC_STAT_LIST

Collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics।

Query to find Concurrent Request Details from Concurrent Program Name

SELECT

distinct user_concurrent_program_name,

responsibility_name,

request_date,

argument_text,

request_id,

phase_code,

status_code,

logfile_name,

outfile_name,

output_file_type,

hold_flag,

user_name

FROM

fnd_concurrent_requests fcr,

fnd_concurrent_programs_tl fcp,

fnd_responsibility_tl fr,

fnd_user fu

WHERE

fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id

and fcr.responsibility_id = fr.responsibility_id

and fcr.requested_by = fu.user_id

--and user_name = upper('SYSADMIN')

and user_concurrent_program_name in

('Active Users')

and Phase_code='P'

ORDER BY REQUEST_DATE DESC;

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;

Oracle API to insert supplier bank account information

API used:

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

iby_disbursement_setup_pub.create_external_payee

iby_disbursement_setup_pub.set_payee_instr_assignment

IBY_EXT_BANKACCT_PUB.check_bank_acct_owner

IBY_EXT_BANKACCT_PUB.add_joint_account_owner

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_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;

Search This Blog

how to Clear the Oracle Applications cache

Tables Used by Concurrent Request Concurrent Program

Query to find Concurrent Request Details from Concurrent Program Name

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

Oracle API to insert supplier bank account information

Oracle API to insert supplier bank account information

API used:

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

iby_disbursement_setup_pub.create_external_payee

iby_disbursement_setup_pub.set_payee_instr_assignment

IBY_EXT_BANKACCT_PUB.check_bank_acct_owner

IBY_EXT_BANKACCT_PUB.add_joint_account_owner