eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.POS_SBD_IBY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 22


  	delete from iby_temp_ext_bank_accts
	where temp_ext_bank_acct_id = p_iby_temp_ext_bank_account_id;

Line: 171


    	select IBY_TEMP_EXT_BANK_ACCTS_S.nextval into x_temp_ext_bank_account_id from dual;

Line: 173


    	insert into iby_temp_ext_bank_accts
    	(
	     temp_ext_bank_acct_id
	   , status
	   , account_owner_party_id
	   , owner_primary_flag
	   , payment_factor_flag
	   , request_id
	   , program_application_id
	   , program_id
	   , program_update_date
	   , object_version_number
	   , creation_date
	   , created_by
	   , last_update_date
	   , last_updated_by
	   , last_update_login
	   , BANK_ID
	   , BANK_NAME
	   , BANK_NAME_ALT
	   , BANK_NUMBER
	   , BANK_INSTITUTION_TYPE
	   , BANK_ADDRESS_ID
	   , BRANCH_ID
  	   , BRANCH_NAME
	   , BRANCH_NAME_ALT
	   , BRANCH_NUMBER
	   , BRANCH_TYPE
	   , RFC_IDENTIFIER
	   , BIC
	   , BRANCH_ADDRESS_ID
	   , EXT_BANK_ACCOUNT_ID
	   , bank_account_num
	   , bank_account_name
	   , bank_account_name_alt
	   , check_digits
	   , iban
	   , currency_code
	   , FOREIGN_PAYMENT_USE_FLAG
	   , bank_account_type
	   , country_code
	   , description
	   , end_date
	   , start_date
	   , agency_location_code
	   , account_suffix
	   , EXCHANGE_RATE_AGREEMENT_NUM
	   , EXCHANGE_RATE_AGREEMENT_TYPE
	   , EXCHANGE_RATE
	   , NOTE
	   , NOTE_ALT
	  )
	  values
	  (
	     x_temp_ext_bank_account_id
	   , p_status
	   , p_party_id
	   , p_owner_primary_flag
	   , p_payment_factor_flag
	   , null
	   , 177
	   , 177
	   , sysdate
	   , 1
	   , sysdate
	   , fnd_global.user_id
	   , sysdate
	   , fnd_global.user_id
	   , fnd_global.login_id
	   , p_BANK_ID
	   , p_BANK_NAME
	   , p_BANK_NAME_ALT
	   , p_BANK_NUMBER
	   , p_BANK_INSTITUTION
	   , l_bank_location_id
	   , p_BRANCH_ID
	   , p_BRANCH_NAME
	   , p_BRANCH_NAME_ALT
	   , p_BRANCH_NUMBER
	   , p_BRANCH_TYPE
	   , p_RFC_IDENTIFIER
	   , p_BIC
	   , l_branch_location_id
	   , p_EXT_BANK_ACCOUNT_ID
	   , p_bank_account_number
	   , p_bank_account_name
	   , p_bank_account_name_alt
	   , p_check_digits
	   , p_iban
	   , p_currency_code
	   , p_FOREIGN_PAYMENT_USE_FLAG
	   , p_bank_account_type
	   , p_country_code
	   , p_account_description
	   , p_end_date
	   , p_start_date
	   , p_agency_location_code
	   , p_account_suffix
	   , p_EXCHANGE_RATE_AGREEMENT_NUM
	   , p_EXCHANGE_RATE_AGREEMENT_TYPE
	   , p_EXCHANGE_RATE
	   , p_NOTES
	   , p_NOTE_ALT
	  );

Line: 295


/* This procedure updates the iby temp account on buyer's request.
 *
 */
PROCEDURE update_iby_temp_account (
  p_temp_ext_bank_acct_id in number
, p_party_id in NUMBER
, p_status in varchar2
, p_owner_primary_flag in varchar2
, p_payment_factor_flag in varchar2
, p_BANK_ID in NUMBER
, p_BANK_NAME in VARCHAR2
, p_BANK_NAME_ALT in varchar2
, p_BANK_NUMBER in VARCHAR2
, p_BANK_INSTITUTION in varchar2
, p_BANK_ADDRESS1 in VARCHAR2
, p_BANK_ADDRESS2 in VARCHAR2
, p_BANK_ADDRESS3 in VARCHAR2
, p_BANK_ADDRESS4 in VARCHAR2
, p_BANK_CITY in VARCHAR2
, p_BANK_COUNTY in VARCHAR2
, p_BANK_STATE in VARCHAR2
, p_BANK_ZIP in VARCHAR2
, p_BANK_PROVINCE in VARCHAR2
, p_BANK_COUNTRY in VARCHAR2
, p_BRANCH_ID in NUMBER
, p_BRANCH_NAME in VARCHAR2
, p_BRANCH_NAME_ALT in varchar2
, p_BRANCH_NUMBER in VARCHAR2
, p_BRANCH_TYPE in varchar2
, p_RFC_IDENTIFIER in varchar2
, p_BIC in varchar2
, p_BRANCH_ADDRESS1 in VARCHAR2
, p_BRANCH_ADDRESS2 in VARCHAR2
, p_BRANCH_ADDRESS3 in VARCHAR2
, p_BRANCH_ADDRESS4 in VARCHAR2
, p_BRANCH_CITY in VARCHAR2
, p_BRANCH_COUNTY in VARCHAR2
, p_BRANCH_STATE in VARCHAR2
, p_BRANCH_ZIP in VARCHAR2
, p_BRANCH_PROVINCE in VARCHAR2
, p_BRANCH_COUNTRY in VARCHAR2
, p_EXT_BANK_ACCOUNT_ID in number
, p_bank_account_number in varchar2
, p_bank_account_name in varchar2
, p_bank_account_name_alt in varchar2
, p_check_digits in varchar2
, p_iban in varchar2
, p_currency_code in varchar2
, p_country_code in varchar2
, p_FOREIGN_PAYMENT_USE_FLAG in varchar2
, p_bank_account_type in varchar2
, p_account_description in varchar2
, p_end_date in date
, p_start_date in date
, p_agency_location_code in varchar2
, p_account_suffix in varchar2
, p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
, p_exchange_rate_agreement_type in VARCHAR2
, p_EXCHANGE_RATE in NUMBER
, p_NOTES in VARCHAR2
, p_NOTE_ALT in varchar2
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
 	l_step NUMBER;

Line: 366


 	select iby.bank_address_id, iby.branch_address_id
  	into l_bank_location_id, l_branch_location_id
  	from iby_temp_ext_bank_accts iby
  	where temp_ext_bank_acct_id = p_temp_ext_bank_acct_id;

Line: 376


			' Begin update_iby_temp_account ');

Line: 407


			    	POS_SBD_IBY_PKG.update_location (
	  			  p_location_id => l_bank_location_id
				, p_ADDRESS1 => p_bank_address1
				, p_ADDRESS2 => p_bank_address2
				, p_ADDRESS3 => p_bank_address3
				, p_ADDRESS4 => p_bank_address4
				, p_CITY     => p_bank_city
				, p_COUNTY   => p_bank_county
				, p_STATE    => p_bank_state
				, p_ZIP      => p_bank_zip
				, p_PROVINCE => p_bank_province
				, p_COUNTRY  => p_country_code
				, x_status   => x_status
				, x_exception_msg => x_exception_msg);

Line: 459


    				POS_SBD_IBY_PKG.update_location (
				  p_location_id => l_branch_location_id
				, p_ADDRESS1 => p_branch_address1
				, p_ADDRESS2 => p_branch_address2
				, p_ADDRESS3 => p_branch_address3
				, p_ADDRESS4 => p_branch_address4
				, p_CITY     => p_branch_city
				, p_COUNTY   => p_branch_county
				, p_STATE    => p_branch_state
				, p_ZIP      => p_branch_zip
				, p_PROVINCE => p_branch_province
				, p_COUNTRY  => p_country_code
				, x_status   => x_status
				, x_exception_msg => x_exception_msg);

Line: 504


	update iby_temp_ext_bank_accts set
	     status = p_status
	   , account_owner_party_id = p_party_id
	   , owner_primary_flag = p_owner_primary_flag
	   , payment_factor_flag = p_payment_factor_flag
	   , last_update_date = sysdate
	   , last_updated_by = fnd_global.user_id
	   , last_update_login = fnd_global.login_id
	   , BANK_ID = p_bank_id
	   , BANK_NAME = p_bank_name
	   , BANK_NAME_ALT = p_bank_name_alt
	   , BANK_NUMBER = p_bank_number
	   , BANK_INSTITUTION_TYPE = p_bank_institution
	   , BANK_ADDRESS_ID = l_bank_location_id
	   , BRANCH_ID = p_branch_id
	   , BRANCH_NAME = p_branch_name
	   , BRANCH_NAME_ALT = p_branch_name_alt
	   , BRANCH_NUMBER = p_branch_number
	   , BRANCH_TYPE = p_branch_type
	   , RFC_IDENTIFIER = p_rfc_identifier
	   , BIC = p_bic
	   , BRANCH_ADDRESS_ID = l_branch_location_id
	   , EXT_BANK_ACCOUNT_ID = nvl(p_ext_bank_account_id, ext_bank_account_id)
	   , bank_account_num = p_bank_account_number
	   , bank_account_name = p_bank_account_name
	   , bank_account_name_alt = p_bank_account_name_alt
	   , check_digits = p_check_digits
	   , iban = p_iban
	   , currency_code = p_currency_code
	   , FOREIGN_PAYMENT_USE_FLAG = p_foreign_payment_use_flag
	   , bank_account_type = p_bank_account_type
	   , country_code = p_country_code
	   , description = p_account_description
	   , end_date = p_end_date
	   , start_date = p_start_date
	   , agency_location_code = p_agency_location_code
	   , account_suffix = p_account_suffix
	   , EXCHANGE_RATE_AGREEMENT_NUM = p_exchange_rate_agreement_num
	   , EXCHANGE_RATE_AGREEMENT_TYPE = p_exchange_rate_agreement_type
	   , EXCHANGE_RATE = p_exchange_rate
	   , NOTE = p_notes
	   , NOTE_ALT = p_note_alt
	   where temp_ext_bank_acct_id = p_temp_ext_bank_acct_id;

Line: 552


			' End update_iby_temp_account ');

Line: 561


END update_iby_temp_account;

Line: 634


/* This procedure updates the location.
 *
 */
PROCEDURE update_location (
  p_location_id in NUMBER
, p_ADDRESS1 in VARCHAR2
, p_ADDRESS2 in VARCHAR2
, p_ADDRESS3 in VARCHAR2
, p_ADDRESS4 in VARCHAR2
, p_CITY in VARCHAR2
, p_COUNTY in VARCHAR2
, p_STATE in VARCHAR2
, p_ZIP in VARCHAR2
, p_PROVINCE in VARCHAR2
, p_COUNTRY in VARCHAR2
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS

	l_step number;

Line: 666


			' Begin update_location ');

Line: 675


	select object_version_number, created_by_module
    	into l_obj_ver, l_created_by_module from hz_locations
    	where location_id = p_location_id;

Line: 714


    	hz_location_v2pub.update_location (
        	p_init_msg_list => fnd_api.g_true,
        	p_location_rec  => l_location_rec,
        	p_object_version_number   => l_obj_ver,
        	x_return_status => x_status,
        	x_msg_count => l_msg_count,
        	x_msg_data => x_exception_msg
    	);

Line: 727


			' End update_location ');

Line: 734


END update_location;

Line: 807


 	select party_id from pos_supplier_mappings where mapping_id = p_mapping_id;

Line: 987


	select temp.ext_bank_account_id, temp.account_owner_party_id
	from IBY_TEMP_EXT_BANK_ACCTS temp
	where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id;

Line: 1001


	select org_id, party_site_id from ap_supplier_sites_all where
	vendor_site_id = p_vendor_site_id;

Line: 1007


	select max(uses.order_of_preference)
	from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
	iby_ext_bank_accounts act, ap_supplier_sites_all pvsa
	where uses.instrument_type = 'BANKACCOUNT'
	AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
	and payee.ext_payee_id = uses.ext_pmt_party_id
	and payee.org_id = pvsa.org_id
	and payee.party_site_id = pvsa.party_site_id
	and org_type = 'OPERATING_UNIT'
	and pvsa.vendor_site_id = payee.supplier_site_id
	and payee.supplier_site_id  = p_vendor_site_id
	and uses.instrument_id = act.ext_bank_account_id
	and payee.payee_party_id = l_party_id
	and payee.party_site_id is null;

Line: 1025


	select payee.object_version_number, payee.ext_payee_id from iby_external_payees_all payee
	where payee.ext_payee_id = l_party_id
	and payee.org_id is null
	and payee.party_site_id is null
	and payee.supplier_site_id  = p_vendor_site_id;

Line: 1033


	select uses.object_version_number
	from iby_external_payees_all payee, iby_pmt_instr_uses_all uses
	where payee.ext_payee_id = l_party_id
	and payee.org_id is null
	and payee.party_site_id is null
	and payee.supplier_site_id  = p_vendor_site_id
	and payee.ext_payee_id = uses.ext_pmt_party_id
	and uses.instrument_id = l_ext_bank_account_id
	and uses.instrument_type = 'BANKACCOUNT';

Line: 1190


	-- Create/Update the account
	POS_SBD_IBY_PKG.approve_iby_temp_account (
	  p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
	, x_status => x_status
	, x_exception_msg => x_exception_msg
	);

Line: 1236


	select req.account_request_id,
	temp.bank_id, temp.bank_name, temp.bank_number,
	temp.bank_institution_type, temp.bank_name_alt,
	temp.bank_address_id,
	temp.branch_id, temp.branch_name, temp.branch_number, temp.bic,
	temp.branch_type, temp.branch_name_alt, temp.rfc_identifier,
	temp.branch_address_id,
	temp.ext_bank_account_id, temp.account_owner_party_id,
	temp.country_code, temp.FOREIGN_PAYMENT_USE_FLAG,
	temp.bank_account_name, temp.bank_account_num, temp.check_digits,
	temp.iban, temp.currency_code,
	temp.bank_account_name_alt, temp.bank_account_type,
	temp.description, temp.end_date, temp.start_date, temp.agency_location_code,
	temp.status, temp.note, temp.note_alt, temp.account_suffix, temp.exchange_rate,
	temp.exchange_rate_agreement_num, temp.exchange_rate_agreement_type, temp.payment_factor_flag
	from IBY_TEMP_EXT_BANK_ACCTS temp, pos_acnt_gen_req req
	where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id
	and req.temp_ext_bank_acct_id = temp.temp_ext_bank_acct_id;

Line: 1277


	select act.object_version_number, ow.account_owner_party_id
	from iby_ext_bank_accounts act, iby_account_owners ow
	where ow.ext_bank_account_id = act.ext_bank_account_id
	and act.ext_bank_account_id = l_ext_bank_account_id
	and ow.primary_flag = 'Y'
	and NVL(ow.end_date,SYSDATE+10)>SYSDATE
	AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);

Line: 1535


			' Calling IBY_EXT_BANKACCT_PUB.create/update_ext_bank_acct');

Line: 1631


			' Now Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');

Line: 1640


	 IBY_EXT_BANKACCT_PUB.update_ext_bank_acct (
          p_api_version                => 1.0,
          p_init_msg_list              => FND_API.G_TRUE,
          p_ext_bank_acct_rec          => l_ext_bank_acct_rec,
          x_return_status              => x_status,
          x_msg_count                  => l_msg_count,
          x_msg_data                   => x_exception_msg,
          x_response                   => l_result_rec
         );

Line: 1652


			' After Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');

Line: 1667


	POS_SBD_IBY_PKG.update_req_with_account (
	  p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
	, p_ext_bank_account_id =>  l_ext_bank_account_id
	, p_account_request_id => l_account_request_id
	, p_bank_id => l_bank_id
	, p_branch_id => l_branch_id
	, x_status        => x_status
	, x_exception_msg => x_exception_msg
	);

Line: 1689


PROCEDURE update_req_with_account (
  p_temp_ext_bank_account_id in number
, p_ext_bank_account_id in number
, p_account_request_id in number
, p_bank_id in number
, p_branch_id in number
, x_status        out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS

l_step number;

Line: 1705


	update pos_acnt_addr_summ_req
	set ext_bank_account_id = p_ext_bank_account_id,
	last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id,
	object_version_number = object_version_number + 1
	where account_request_id = p_account_request_id;

Line: 1714


	update pos_acnt_gen_req
	set ext_bank_account_id = p_ext_bank_account_id,
	last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id,
	object_version_number = object_version_number + 1
	where account_request_id = p_account_request_id;

Line: 1723


	update iby_temp_ext_bank_accts
	set bank_id = p_bank_id,
	branch_id = p_branch_id,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id,
 	object_version_number = object_version_number + 1,
	ext_bank_account_id = p_ext_bank_account_id
	where temp_ext_bank_acct_id = p_temp_ext_bank_account_id;

Line: 1739


END update_req_with_account;

Line: 1775


	select count(*) from iby_temp_ext_bank_accts iby, pos_acnt_gen_req pos
	where pos.mapping_id = p_mapping_id
	and pos.temp_ext_bank_acct_id = iby.temp_ext_bank_acct_id
	and (
	     (iby.currency_code = p_currency_code
		and p_currency_code is not null and iby.currency_code is not null) OR
	     (p_currency_code is null and iby.currency_code is null)
	    )

	and iby.country_code = p_country_code
	and iby.status in ('NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CORRECTED', 'CHANGE_PENDING')
	AND ((iby.bank_id = p_bank_id and p_bank_id is not null and iby.bank_id is not null) OR
	     (iby.bank_number = p_bank_number and p_bank_number is not null and iby.bank_number is not null) OR
	     (p_bank_id is null and iby.bank_id is null and p_bank_number is null and iby.bank_number is null)
	    )
	AND (
	      (iby.branch_id = p_branch_id and p_branch_id is not null and iby.branch_id is not null) OR
	      (iby.branch_number = p_branch_number and p_branch_number is not null
	       and iby.branch_number is not null) OR
	      (p_branch_id is null and iby.branch_id is null
	       and p_branch_number is null and iby.branch_number is null)
	    )

	AND (
	     (iby.bank_account_num = p_bank_account_number and p_bank_account_number is not null
	       and iby.bank_account_num is not null) OR
	     (iby.bank_account_name = p_bank_account_name and p_bank_account_name is not null
	       and iby.bank_account_name is not null)
	    )
	AND ((pos.account_request_id <> p_account_request_id and p_account_request_id is not null and
		pos.account_request_id is not null) OR (p_account_request_id is null));

Line: 1808


	select count(*) from iby_ext_bank_accounts act, iby_account_owners o, pos_supplier_mappings pmap
	where o.ext_bank_account_id  = act.ext_bank_account_id
	and (
		(act.currency_code = p_currency_code
		 and act.currency_code is not null and p_currency_code is not null) OR
		(act.currency_code is null and p_currency_code is null)
	    )
	and o.account_owner_party_id = pmap.party_id
	and pmap.mapping_id = p_mapping_id
	and ((act.bank_id = l_bank_id and act.bank_id is not null and l_bank_id is not null) OR
	     (act.bank_id is null and l_bank_id is null))
	and ((act.branch_id = l_branch_id and act.branch_id is not null
		and l_branch_id is not null) OR
	     (act.branch_id is null and l_branch_id is null))
	and (
		act.bank_account_name = p_bank_account_name
		 and act.bank_account_name is not null and p_bank_account_name is not null
	    )
	and ((act.ext_bank_account_id <> p_EXT_BANK_ACCOUNT_ID and p_EXT_BANK_ACCOUNT_ID is not null)
		OR p_EXT_BANK_ACCOUNT_ID is null)
	and act.country_code = p_country_code
	and not exists
	(
	select 1 from IBY_TEMP_EXT_BANK_ACCTS temp
	where temp.EXT_BANK_ACCOUNT_ID = act.ext_bank_account_id
	and temp.status in ('CORRECTED', 'NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CHANGE_PENDING')
	and temp.account_owner_party_id = o.account_owner_party_id
	)
	and ((act.ext_bank_account_id <> p_ext_bank_account_id and p_ext_bank_account_id is not null and
	act.ext_bank_account_id is not null) OR (p_ext_bank_account_id is null))
	AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);