[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT iby.status, iby.temp_ext_bank_acct_id, req.object_version_number
FROM iby_temp_ext_bank_accts iby, pos_acnt_gen_req req
WHERE req.account_request_id = p_account_request_id
AND iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id for update nowait;
SELECT req.assignment_request_id, summ.assignment_id, summ.priority,
req.request_type, req.mapping_id, req.party_site_id,
req.address_request_id, req.object_version_number
FROM pos_acnt_addr_req req, pos_acnt_addr_summ_req summ
where req.assignment_request_id = summ.assignment_request_id
AND summ.account_request_id = p_account_request_id for update nowait;
l_needToDelete boolean;
SELECT assignment_id, priority, start_date, end_date,
ext_bank_account_id, account_request_id, assignment_status
FROM pos_acnt_addr_summ_req
where assignment_request_id = l_assignment_request_id
and priority > l_priority for update nowait;
SELECT assignment_id, priority
FROM pos_acnt_addr_summ_req
where assignment_request_id = l_assignment_request_id
and priority < l_priority;
select summ.assignment_id from pos_acnt_addr_summ_req summ
where assignment_request_id = l_assignment_request_id
and assignment_status = 'CURRENT'
and not exists(select 1 from pos_acnt_addr_summ_req
where assignment_request_id = l_assignment_request_id
and assignment_status <> 'CURRENT');
-- Concurrency Issue: Verify no one else has updated the account
if l_object_version_number <> p_object_version_number then
x_exception_msg := 'The bank account has been updated.';
x_exception_msg := 'The bank account cannot be deleted. Account Status is ' || l_status;
-- Delete the row in the IBY temp table
l_step := 4;
-- Update the priorities in POS_ACNT_ADDR_SUMM_REQ
-- Delete the row in the POS_ACNT_ADDR_SUMM_REQ
l_step := 5;
l_needToDelete := true;
-- Update the priority in POS_ACNT_ADDR_SUMM_REQ
POS_SBD_PKG.supplier_update_assignment(
p_assignment_id => l_assign_req_det_below_rec.assignment_id
, p_assignment_request_id => l_assign_req_rec.assignment_request_id
, p_object_version_number => l_assign_req_rec.object_version_number
, p_account_request_id => l_assign_req_det_below_rec.account_request_id
, p_ext_bank_account_id => l_assign_req_det_below_rec.ext_bank_account_id
, p_request_type => l_assign_req_rec.request_type
, p_mapping_id => l_assign_req_rec.mapping_id
, p_party_site_id => l_assign_req_rec.party_site_id
, p_address_request_id => l_assign_req_rec.address_request_id
, p_priority => l_assign_req_det_below_rec.priority - 1
, p_start_date => l_assign_req_det_below_rec.start_date
, p_end_date => l_assign_req_det_below_rec.end_date
, x_status => x_status
, x_exception_msg => x_exception_msg
);
l_needToDelete := false;
-- Delete the row in POS_ACNT_ADDR_SUMM_REQ
l_assignment_id := l_assign_req_rec.assignment_id;
-- Delete the row in POS_ACNT_ADDR_REQ if needed
if l_needToDelete = true then
-- Check if there are any other rows left.
l_step := 10;
l_needToDelete := false;
l_needToDelete := true;
if l_needToDelete = true then
POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
p_assignment_request_id => l_assignment_request_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
select account_request_id from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
WHERE iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id
AND req.mapping_id = p_mapping_id
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)
)
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)
)
AND (iby.bank_account_num = p_bank_account_number OR iby.bank_account_name = p_bank_account_name)
AND iby.currency_code = p_currency_code
AND iby.country_code = p_country_code;
select party_id, vendor_id from pos_supplier_mappings
where mapping_id = p_mapping_id;
POS_SBD_TBL_PKG.insert_row_pos_acnt_gen_req (
p_mapping_id => p_mapping_id
, p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
, p_ext_bank_account_id => p_ext_bank_account_id
, x_account_request_id => x_account_request_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_SBD_PKG.supplier_update_assignment(
p_assignment_id => null
, p_assignment_request_id => null
, p_object_version_number => null
, p_account_request_id => x_account_request_id
, p_ext_bank_account_id => p_ext_bank_account_id
, p_request_type => p_request_type
, p_mapping_id => p_mapping_id
, p_party_site_id => p_party_site_id
, p_address_request_id => p_address_request_id
, p_priority => null
, p_start_date => sysdate
, p_end_date => null
, x_status => x_status
, x_exception_msg => x_exception_msg
);
procedure supplier_update_account (
p_mapping_id in NUMBER
, p_account_request_id in number
, p_object_version_number in number
, 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 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 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_FROM_SUPPLIER in VARCHAR2
, x_account_request_id out nocopy NUMBER
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step number;
select party_id, vendor_id from pos_supplier_mappings
where mapping_id = p_mapping_id;
select req.temp_ext_bank_acct_id, iby.note_alt, req.object_version_number, iby.status
from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
where req.account_request_id = p_account_request_id
and req.temp_ext_bank_acct_id = iby.temp_ext_bank_acct_id for update nowait;
' Begin supplier_update_account ');
-- Update the existing request.
l_step := 2;
x_exception_msg := 'The bank account has been updated.';
-- Update the row in IBY_TEMP_EXT_BANK_ACCTS
POS_SBD_IBY_PKG.update_iby_temp_account (
p_temp_ext_bank_acct_id => l_account_request_id_rec.temp_ext_bank_acct_id
, p_party_id => l_party_id
, p_status => l_status
, p_owner_primary_flag => 'Y'
, p_payment_factor_flag => 'N'
, p_BANK_ID => p_bank_id
, p_BANK_NAME => p_bank_name
, p_BANK_NAME_ALT => p_bank_name_alt
, p_BANK_NUMBER => p_bank_number
, p_BANK_INSTITUTION => p_bank_institution
, p_BANK_ADDRESS1 => p_bank_address1
, p_BANK_ADDRESS2 => p_bank_address2
, p_BANK_ADDRESS3 => p_bank_address3
, p_BANK_ADDRESS4 => p_bank_address4
, p_BANK_CITY => p_bank_city
, p_BANK_COUNTY => p_bank_county
, p_BANK_STATE => p_bank_state
, p_BANK_ZIP => p_bank_zip
, p_BANK_PROVINCE => p_bank_province
, p_BANK_COUNTRY => p_country_code
, p_BRANCH_ID => p_branch_id
, p_BRANCH_NAME => p_branch_name
, p_BRANCH_NAME_ALT => p_branch_name_alt
, p_BRANCH_NUMBER => p_branch_number
, p_BRANCH_TYPE => p_branch_type
, p_RFC_IDENTIFIER => p_rfc_identifier
, p_BIC => p_bic
, p_BRANCH_ADDRESS1 => p_branch_address1
, p_BRANCH_ADDRESS2 => p_branch_address2
, p_BRANCH_ADDRESS3 => p_branch_address3
, p_BRANCH_ADDRESS4 => p_branch_address4
, p_BRANCH_CITY => p_branch_city
, p_BRANCH_COUNTY => p_branch_county
, p_BRANCH_STATE => p_branch_state
, p_BRANCH_ZIP => p_branch_zip
, p_BRANCH_PROVINCE => p_branch_province
, p_BRANCH_COUNTRY => p_country_code
, p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
, p_bank_account_number => p_bank_account_number
, p_bank_account_name => p_bank_account_name
, p_bank_account_name_alt => p_bank_account_name_alt
, p_check_digits => p_check_digits
, p_iban => p_iban
, p_currency_code => p_currency_code
, p_country_code => p_country_code
, p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
, p_bank_account_type => p_bank_account_type
, p_account_description => p_account_description
, p_end_date => p_end_date
, p_start_date => p_start_date
, p_agency_location_code => p_agency_location_code
, p_account_suffix => p_account_suffix
, p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
, P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
, p_EXCHANGE_RATE => p_EXCHANGE_RATE
, p_NOTES => p_NOTES_FROM_SUPPLIER
, p_NOTE_ALT => l_account_request_id_rec.note_alt
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_SBD_TBL_PKG.insert_row_pos_acnt_gen_req (
p_mapping_id => p_mapping_id
, p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
, p_ext_bank_account_id => p_ext_bank_account_id
, x_account_request_id => x_account_request_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
-- update all the records in POS_ACNT_ADDR_SUMM_REQ with the account id
update pos_acnt_addr_summ_req
set account_request_id = x_account_request_id
where ext_bank_account_id = p_ext_bank_account_id;
pos_spm_wf_pkg1.notify_account_update
(p_vendor_id => l_vendor_id,
p_bank_name => p_bank_name,
p_bank_account_number => p_bank_account_number,
p_currency_code => p_currency_code,
p_bank_account_name => p_bank_account_name,
x_itemtype => l_itemtype,
x_itemkey => l_itemkey);
' End supplier_update_account ');
END supplier_update_account;
select req.temp_ext_bank_acct_id, req.object_version_number, iby.note, iby.status,
iby.ext_bank_account_id, iby.bank_id, iby.branch_id
from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
where account_request_id = p_account_request_id
and iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id;
select vendor_id from pos_supplier_mappings where party_id = p_party_id;
-- Update the iby request tables.
open l_account_request_cur;
-- Update the row in IBY_TEMP_EXT_BANK_ACCTS
POS_SBD_IBY_PKG.update_iby_temp_account (
p_temp_ext_bank_acct_id => l_temp_ext_bank_account_id
, p_party_id => p_party_id
, p_status => 'IN_VERIFICATION'
, p_owner_primary_flag => 'Y'
, p_payment_factor_flag => 'N'
, p_BANK_ID => l_bank_id
, p_BANK_NAME => p_bank_name
, p_BANK_NAME_ALT => p_bank_name_alt
, p_BANK_NUMBER => p_bank_number
, p_BANK_INSTITUTION => p_bank_institution
, p_BANK_ADDRESS1 => p_bank_address1
, p_BANK_ADDRESS2 => p_bank_address2
, p_BANK_ADDRESS3 => p_bank_address3
, p_BANK_ADDRESS4 => p_bank_address4
, p_BANK_CITY => p_bank_city
, p_BANK_COUNTY => p_bank_county
, p_BANK_STATE => p_bank_state
, p_BANK_ZIP => p_bank_zip
, p_BANK_PROVINCE => p_bank_province
, p_BANK_COUNTRY => p_country_code
, p_BRANCH_ID => l_branch_id
, p_BRANCH_NAME => p_branch_name
, p_BRANCH_NAME_ALT => p_branch_name_alt
, p_BRANCH_NUMBER => p_branch_number
, p_BRANCH_TYPE => p_branch_type
, p_RFC_IDENTIFIER => p_rfc_identifier
, p_BIC => p_bic
, p_BRANCH_ADDRESS1 => p_branch_address1
, p_BRANCH_ADDRESS2 => p_branch_address2
, p_BRANCH_ADDRESS3 => p_branch_address3
, p_BRANCH_ADDRESS4 => p_branch_address4
, p_BRANCH_CITY => p_branch_city
, p_BRANCH_COUNTY => p_branch_county
, p_BRANCH_STATE => p_branch_state
, p_BRANCH_ZIP => p_branch_zip
, p_BRANCH_PROVINCE => p_branch_province
, p_BRANCH_COUNTRY => p_country_code
, p_EXT_BANK_ACCOUNT_ID => l_ext_bank_account_id
, p_bank_account_number => p_bank_account_number
, p_bank_account_name => p_bank_account_name
, p_bank_account_name_alt => p_bank_account_name_alt
, p_check_digits => p_check_digits
, p_iban => p_iban
, p_currency_code => p_currency_code
, p_country_code => p_country_code
, p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
, p_bank_account_type => p_bank_account_type
, p_account_description => p_account_description
, p_end_date => p_end_date
, p_start_date => p_start_date
, p_agency_location_code => p_agency_location_code
, p_account_suffix => p_account_suffix
, p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
, P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
, p_EXCHANGE_RATE => p_EXCHANGE_RATE
, p_NOTES => l_NOTES_FROM_SUPPLIER
, p_NOTE_ALT => p_NOTES_FROM_BUYER -- Note from buyer
, x_status => x_status
, x_exception_msg => x_exception_msg
);
select req.temp_ext_bank_acct_id, req.object_version_number, iby.note
from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
where account_request_id = p_account_request_id
and iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id for update nowait;
select vendor_id from pos_supplier_mappings where party_id = p_party_id;
-- Update the iby request tables.
open l_account_request_id_cur;
-- Update the row in IBY_TEMP_EXT_BANK_ACCTS
POS_SBD_IBY_PKG.update_iby_temp_account (
p_temp_ext_bank_acct_id => l_temp_ext_bank_account_id
, p_party_id => p_party_id
, p_status => 'APPROVED'
, p_owner_primary_flag => 'Y'
, p_payment_factor_flag => 'N'
, p_BANK_ID => p_bank_id
, p_BANK_NAME => p_bank_name
, p_BANK_NAME_ALT => p_bank_name_alt
, p_BANK_NUMBER => p_bank_number
, p_BANK_INSTITUTION => p_bank_institution
, p_BANK_ADDRESS1 => p_bank_address1
, p_BANK_ADDRESS2 => p_bank_address2
, p_BANK_ADDRESS3 => p_bank_address3
, p_BANK_ADDRESS4 => p_bank_address4
, p_BANK_CITY => p_bank_city
, p_BANK_COUNTY => p_bank_county
, p_BANK_STATE => p_bank_state
, p_BANK_ZIP => p_bank_zip
, p_BANK_PROVINCE => p_bank_province
, p_BANK_COUNTRY => p_country_code
, p_BRANCH_ID => p_branch_id
, p_BRANCH_NAME => p_branch_name
, p_BRANCH_NAME_ALT => p_branch_name_alt
, p_BRANCH_NUMBER => p_branch_number
, p_BRANCH_TYPE => p_branch_type
, p_RFC_IDENTIFIER => p_rfc_identifier
, p_BIC => p_bic
, p_BRANCH_ADDRESS1 => p_branch_address1
, p_BRANCH_ADDRESS2 => p_branch_address2
, p_BRANCH_ADDRESS3 => p_branch_address3
, p_BRANCH_ADDRESS4 => p_branch_address4
, p_BRANCH_CITY => p_branch_city
, p_BRANCH_COUNTY => p_branch_county
, p_BRANCH_STATE => p_branch_state
, p_BRANCH_ZIP => p_branch_zip
, p_BRANCH_PROVINCE => p_branch_province
, p_BRANCH_COUNTRY => p_country_code
, p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
, p_bank_account_number => p_bank_account_number
, p_bank_account_name => p_bank_account_name
, p_bank_account_name_alt => p_bank_account_name_alt
, p_check_digits => p_check_digits
, p_iban => p_iban
, p_currency_code => p_currency_code
, p_country_code => p_country_code
, p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
, p_bank_account_type => p_bank_account_type
, p_account_description => p_account_description
, p_end_date => p_end_date
, p_start_date => p_start_date
, p_agency_location_code => p_agency_location_code
, p_account_suffix => p_account_suffix
, p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
, P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
, p_EXCHANGE_RATE => p_EXCHANGE_RATE
, p_NOTES => l_NOTES_FROM_SUPPLIER
, p_NOTE_ALT => p_NOTES_FROM_BUYER -- Note from buyer
, x_status => x_status
, x_exception_msg => x_exception_msg
);
update pos_acnt_addr_summ_req
set account_request_id = null,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where account_request_id = p_account_request_id;
select assignment_id from pos_acnt_addr_summ_req
where assignment_request_id = p_assignment_request_id for update nowait;
select * from pos_acnt_addr_req
where assignment_request_id = p_assignment_request_id
and object_version_number = p_object_version_number for update nowait;
select * from pos_acnt_addr_summ_req
where assignment_request_id = p_assignment_request_id
order by priority for update nowait;
-- Delete the row in POS_ACNT_ADDR_SUMM_REQ
POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
p_assignment_id => l_assignment_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
-- update the record in POS_ACNT_ADDR_REQ to Approved.
l_step := 6;
select 1 from pos_acnt_addr_summ_req poss, pos_acnt_addr_req req
where poss.assignment_request_id = req.assignment_request_id
and req.mapping_id = p_mapping_id
and ( (poss.account_request_id = p_account_request_id
and poss.account_request_id is not null and p_account_request_id is not null) OR
(ext_bank_account_id = p_ext_bank_account_id
and ext_bank_account_id is not null and p_ext_bank_account_id is not null))
and req.request_status = 'PENDING'
and ( (req.party_site_id is null and req.address_request_id is null and p_party_site_id is null
and p_address_request_id is null) OR
(req.party_site_id = p_party_site_id
and req.party_site_id is not null and p_party_site_id is not null) OR
(req.address_request_id = p_address_request_id
and req.address_request_id is not null and p_address_request_id is not null)
)
and rownum = 1
UNION ALL
select 1 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee, pos_supplier_mappings pmap
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.org_id is null
and payee.supplier_site_id is null
and ((payee.party_site_id = p_party_site_id
and payee.party_site_id is not null and p_party_site_id is not null) OR
(payee.party_site_id is null and p_party_site_id is null and p_address_request_id is null))
and payee.payment_function = 'PAYABLES_DISB'
and (uses.instrument_id = p_ext_bank_account_id
and uses.instrument_id is not null and p_ext_bank_account_id is not null)
and payee.payee_party_id = pmap.party_id
and pmap.mapping_id = p_mapping_id
and rownum = 1;
POS_SBD_PKG.supplier_update_assignment(
p_assignment_id => null
, p_assignment_request_id => null
, p_object_version_number => null
, p_account_request_id => p_account_request_id
, p_ext_bank_account_id => p_ext_bank_account_id
, p_request_type => l_request_type
, p_mapping_id => p_mapping_id
, p_party_site_id => p_party_site_id
, p_address_request_id => p_address_request_id
, p_priority => null
, p_start_date => sysdate
, p_end_date => null
, x_status => x_status
, x_exception_msg => x_exception_msg
);
/* This procedure creates/update the account assignment on supplier's request.
*
*/
PROCEDURE supplier_update_assignment (
p_assignment_id IN NUMBER
, p_assignment_request_id IN NUMBER
, p_object_version_number IN NUMBER
, p_account_request_id IN NUMBER
, p_ext_bank_account_id IN NUMBER
, p_request_type IN VARCHAR2
, p_mapping_id IN NUMBER
, p_party_site_id IN NUMBER
, p_address_request_id IN NUMBER
, p_priority IN NUMBER
, p_start_date IN DATE
, p_end_date IN DATE
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;
select uses.order_of_preference, uses.start_date, uses.end_date
from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
iby_ext_bank_accounts act, pos_supplier_mappings pmap
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.org_id is null
and payee.supplier_site_id is null
and payee.party_site_id is null
and payee.payment_function = 'PAYABLES_DISB'
and uses.instrument_id = act.ext_bank_account_id
and payee.payee_party_id = pmap.party_id
and pmap.mapping_id = p_mapping_id
and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
and act.ext_bank_account_id = p_ext_bank_account_id;
select assignment_id from pos_acnt_addr_summ_req req
where assignment_request_id = l_assignment_request_id
and ((ext_bank_account_id = p_ext_bank_account_id and ext_bank_account_id is not null and p_ext_bank_account_id is not null) OR
(account_request_id = p_account_request_id and account_request_id is not null and p_account_request_id is not null and p_ext_bank_account_id is null));
select uses.order_of_preference, uses.start_date, uses.end_date
from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
iby_ext_bank_accounts act, pos_supplier_mappings pmap
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.org_id is null
and payee.supplier_site_id is null
and payee.party_site_id = p_party_site_id
and payee.payment_function = 'PAYABLES_DISB'
and uses.instrument_id = act.ext_bank_account_id
and payee.payee_party_id = pmap.party_id
and pmap.mapping_id = p_mapping_id
and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
and act.ext_bank_account_id = p_ext_bank_account_id;
select assignment_request_id from pos_acnt_addr_req
where request_type = 'SUPPLIER'
and mapping_id = p_mapping_id
and request_status = 'PENDING'
and party_site_id is null
and address_request_id is null;
select assignment_request_id from pos_acnt_addr_req
where request_type = 'ADDRESS'
and mapping_id = p_mapping_id
and request_status = 'PENDING'
and ( (address_request_id = p_address_request_id and address_request_id is not null and p_address_request_id is not null)
OR (party_site_id = p_party_site_id and party_site_id is not null and p_party_site_id is not null));
select uses.order_of_preference, uses.start_date, uses.end_date, uses.instrument_id, req.account_request_id
from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
iby_ext_bank_accounts act, pos_supplier_mappings pmap, pos_acnt_gen_req req
where uses.instrument_type = 'BANKACCOUNT'
and payee.ext_payee_id = uses.ext_pmt_party_id
and payee.org_id is null
and ((payee.party_site_id = p_party_site_id
and p_party_site_id is not null and payee.party_site_id is not null) OR
(p_party_site_id is null and payee.party_site_id is null))
and uses.instrument_id = act.ext_bank_account_id
and payee.payee_party_id = pmap.party_id
and payee.payment_function = 'PAYABLES_DISB'
and pmap.mapping_id = p_mapping_id
and payee.supplier_site_id is null
and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
and req.ext_bank_account_id (+) = act.ext_bank_account_id
and req.mapping_id(+) = p_mapping_id;
select max(summ.priority)
from pos_acnt_addr_summ_req summ, pos_acnt_addr_req req
where req.mapping_id = p_mapping_id
and req.assignment_request_id = summ.assignment_request_id
and ((req.party_site_id = p_party_site_id and req.party_site_id is
not null and p_party_site_id is not null)
OR (req.party_site_id is null and p_party_site_id is null))
and ((req.address_request_id = p_address_request_id
and req.address_request_id is not null and p_address_request_id is not null)
OR (req.address_request_id is null and p_address_request_id is null))
and req.request_status = 'PENDING';
' Begin supplier_update_assignment ');
POS_SBD_TBL_PKG.insert_row_pos_acnt_addr_req (
p_mapping_id => p_mapping_id
, p_request_type => p_request_type
, p_party_site_id => p_party_site_id
, p_address_request_id => p_address_request_id
, x_assignment_request_id => l_assignment_request_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_SBD_TBL_PKG.insert_row_pos_acnt_summ_req (
p_assignment_request_id => l_assignment_request_id
, p_ext_bank_account_id => l_assign_iby_rec.instrument_id
, p_account_request_id => l_assign_iby_rec.account_request_id
, p_start_date => l_assign_iby_rec.start_date
, p_end_date => l_assign_iby_rec.end_date
, p_priority => l_assign_iby_rec.order_of_preference
, p_assignment_status => 'CURRENT'
, x_assignment_id => l_c_assignment_id
, x_status => x_status
, x_exception_msg => x_exception_msg);
l_assignment_status := 'UPDATE';
l_assignment_status := 'UPDATE';
POS_SBD_TBL_PKG.update_row_pos_acnt_summ_req (
p_assignment_id => l_assignment_id
, p_assignment_request_id => l_assignment_request_id
, p_ext_bank_account_id => p_ext_bank_account_id
, p_account_request_id => p_account_request_id
, p_start_date => p_start_date
, p_end_date => p_end_date
, p_priority => l_f_priority
, p_assignment_status => l_assignment_status
, x_status => x_status
, x_exception_msg => x_exception_msg
);
POS_SBD_TBL_PKG.insert_row_pos_acnt_summ_req (
p_assignment_request_id => l_assignment_request_id
, p_ext_bank_account_id => p_ext_bank_account_id
, p_account_request_id => p_account_request_id
, p_start_date => p_start_date
, p_end_date => p_end_date
, p_priority => l_f_priority
, p_assignment_status => l_assignment_status
, x_assignment_id => l_assignment_id
, x_status => x_status
, x_exception_msg => x_exception_msg
);
' End supplier_update_assignment ');
END supplier_update_assignment;
select temp.status, req.object_version_number, req.temp_ext_bank_acct_id, pmap.vendor_id,
temp.bank_account_num, temp.bank_name
from pos_acnt_gen_req req, iby_temp_ext_bank_accts temp, pos_supplier_mappings pmap
where req.account_request_id = p_account_request_id
and temp.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id
and pmap.mapping_id = req.mapping_id;
update iby_temp_ext_bank_accts
set status = 'VERIFICATION_FAILED', note_alt = p_note_from_buyer
where temp_ext_bank_acct_id = l_temp_ext_bank_acct_id;
update pos_acnt_addr_summ_req
set account_request_id = null
where account_request_id = p_account_request_id;
PROCEDURE update_payment_pref(
p_payment_preference_id IN NUMBER
, p_party_id IN NUMBER
, p_party_site_id IN NUMBER
, p_payment_currency_code IN VARCHAR2
, p_invoice_currency_code IN VARCHAR2
, p_payment_method IN VARCHAR2
, p_notification_method IN VARCHAR2
, p_object_version_number IN NUMBER
, x_status out nocopy VARCHAR2
, x_exception_msg out nocopy VARCHAR2
)
IS
l_step NUMBER;
select payment_preference_id from pos_acnt_pay_pref
where party_id = p_party_id
and party_site_id = p_party_site_id;
select POS_ACNT_PAY_PREF_S.nextval into l_payment_preference_id from dual;
insert into POS_ACNT_PAY_PREF (
payment_preference_id
, party_id
, party_site_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, object_version_number
, payment_method
, notification_method
, payment_currency_code
, invoice_currency_code
)
values
(
l_payment_preference_id
, p_party_id
, p_party_site_id
, sysdate -- creation_date
, fnd_global.user_id -- created_by
, sysdate -- last_update_date
, fnd_global.user_id -- last_updated_by
, fnd_global.login_id -- last_update_login
, 1
, p_payment_method
, l_notification_method
, p_payment_currency_code
, p_invoice_currency_code
);
update pos_acnt_pay_pref set
last_update_date = sysdate
, last_updated_by = fnd_global.user_id
, last_update_login = fnd_global.login_id
, payment_currency_code = p_payment_currency_code
, invoice_currency_code = p_invoice_currency_code
, notification_method = p_notification_method
, payment_method = p_payment_method
where payment_preference_id = l_payment_preference_id;
END update_payment_pref;
select addr.assignment_request_id from pos_acnt_addr_req addr
where addr.mapping_id = p_mapping_id
and (
(addr.request_type = 'SUPPLIER' and p_address_request_id is null and p_address_request_id is null) OR
(addr.request_type = 'ADDRESS' and addr.party_site_id = p_party_site_id and p_party_site_id is not null and addr.party_site_id is not null) OR
(addr.request_type = 'ADDRESS' and addr.address_request_id = p_address_request_id and p_address_request_id is not null and addr.address_request_id is not null)
)
and addr.REQUEST_STATUS = 'PENDING'
and not exists (select 1 from pos_acnt_addr_summ_req summ
where addr.assignment_request_id = summ.assignment_request_id
and summ.assignment_status <> ('CURRENT')
and rownum = 1);
select assignment_id from pos_acnt_addr_summ_req
where assignment_request_id = l_assignment_request_id;
update pos_acnt_addr_req
set party_site_id = p_party_site_id, address_request_id = null
where address_request_id = p_address_request_id;
SELECT owners.account_owner_party_id
FROM iby_pmt_instr_uses_all instrument,
IBY_ACCOUNT_OWNERS owners,
iby_external_payees_all payees
WHERE
owners.primary_flag = 'Y' AND
owners.ext_bank_account_id = p_acct_id AND
owners.ext_bank_account_id = instrument.instrument_id AND
payees.ext_payee_id = instrument.ext_pmt_party_id AND
payees.payee_party_id = owners.account_owner_party_id;
SELECT vendor_name, segment1
INTO l_supplier_name, l_supplier_number
FROM ap_suppliers
WHERE party_id = l_party_id;