[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(
SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10)
)
INTO l_ret_value
FROM dual;
PROCEDURE insert_for_report
(
p_duns IN VARCHAR2,
p_record_type IN NUMBER,
p_reference1 IN VARCHAR2,
p_reference2 IN VARCHAR2,
p_reference3 IN VARCHAR2,
p_reference4 IN VARCHAR2,
p_reference5 IN VARCHAR2
)
IS
l_module_name VARCHAR2(60);
l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.insert_for_report';
INSERT INTO fv_ccr_process_report
(duns_info,record_type,reference1,reference2,reference3,reference4,reference5)
VALUES
(p_duns,p_record_type,p_reference1,p_reference2,p_reference3,p_reference4,
p_reference5);
SELECT ext_payee_id
FROM iby_external_payees_all payee
WHERE payee.PAYEE_PARTY_ID = ci_party_id
AND payee.PAYMENT_FUNCTION = ci_pmt_function
AND ((ci_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
(payee.PARTY_SITE_ID = ci_party_site_id))
AND ((ci_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
(payee.SUPPLIER_SITE_ID = ci_supplier_site_id))
AND ((ci_org_id is NULL and payee.ORG_ID is NULL) OR
(payee.ORG_ID = ci_org_id AND payee.ORG_TYPE = ci_org_type));
SELECT hzp.party_name, NVL(pav.num_1099, pav.individual_1099),
pav.organization_type_lookup_code
INTO x_vendor_name,x_num_1099, x_org_type_lkup
FROM hz_parties hzp, ap_suppliers pav
WHERE hzp.party_id = pav.party_id
AND pav.vendor_id = p_vendor_id;
PROCEDURE update_vendor_org_type
(
p_vendor_id IN NUMBER,
p_vend_org_type IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2
)
IS
BEGIN
fnd_file.put_line(fnd_file.log, 'p_vendor_id: '||p_vendor_id);
UPDATE ap_suppliers
SET organization_type_lookup_code = p_vend_org_type
WHERE vendor_id = p_vendor_id;
END update_vendor_org_type;
SELECT pavs.vendor_site_id, hps.duns_number_c
FROM ap_supplier_sites_all pavs, hz_party_sites hps
WHERE pavs.vendor_id = p_supp_id
AND pavs.vendor_site_code = p_site_name
AND pavs.org_id = p_org
AND pavs.party_site_id = hps.party_site_id;
SELECT vendor_site_code INTO x_site_code
--FROM po_vendor_sites_all
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id;
SELECT pav.vendor_id, hzp.party_name
FROM hz_parties hzp, ap_suppliers pav
WHERE (pav.num_1099 = p_taxpayer OR
pav.individual_1099 = p_taxpayer)
AND hzp.party_id = pav.party_id;
SELECT pav.vendor_id, hzp.party_name
FROM hz_parties hzp, ap_suppliers pav
WHERE hzp.party_name = p_legal_bus
AND hzp.party_id = pav.party_id;
SELECT segment1
--FROM po_vendors
FROM ap_suppliers
where segment1 = p_supp_num;
SELECT branch_party_id, bank_name, bank_branch_name
FROM ce_bank_branches_v
WHERE branch_number = p_routing
AND branch_number IS NOT NULL;
SELECT bank_name,bank_branch_name,branch_number
INTO l_bank_name,l_bank_branch_name,
l_routing_num FROM ce_bank_branches_v
WHERE branch_party_id = p_bank_branch_id;
x_update_account OUT NOCOPY VARCHAR2
)
IS
CURSOR bank_acct_csr(p_branch_id NUMBER,p_acct_number VARCHAR2,
p_currency VARCHAR2) IS
--Bug8405987
SELECT ext_bank_account_id,
DECODE(UPPer(bank_account_type),'SAVINGS','S','CHECKING','C',bank_account_type)
FROM iby_ext_bank_accounts
WHERE branch_id = p_branch_id
AND bank_account_num = p_acct_number
AND currency_code = p_currency
AND country_code = p_country_code;
x_update_account:='N';
SELECT bank_account_num,branch_id,
DECODE(UPPER(bank_account_type),'SAVINGS','S','CHECKING','C',bank_account_type)
INTO l_bank_acct_num,l_bank_branch_id,
l_bank_account_type
FROM iby_ext_bank_accounts
WHERE ext_bank_account_id=p_bank_account_id;
x_update_account:='Y';
x_update_account:='Y';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'x_update_account->'||x_update_account);
/* Procedure used to create or update vendor
create or update vendor site and update bank_acct_uses table*/
PROCEDURE fv_process_vendor
(
p_ccr_id IN NUMBER ,
p_prev_ccr_id IN VARCHAR2 ,
p_update_type IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_bank_branch_id IN NUMBER ,
p_vendor_id IN NUMBER,
p_pay_site_id IN NUMBER,
p_main_add_site_id IN NUMBER,
p_enabled_flag IN VARCHAR2,
p_main_address_flag IN VARCHAR2,
p_taxpayer_number IN VARCHAR2,
p_legal_bus_name IN VARCHAR2,
p_duns IN VARCHAR2,
p_plus4 IN VARCHAR2,
p_main_address_line1 IN VARCHAR2,
p_main_address_line2 IN VARCHAR2,
p_main_address_city IN VARCHAR2,
p_main_address_state IN VARCHAR2,
p_main_address_zip IN VARCHAR2,
p_main_address_country IN VARCHAR2,
p_pay_address_line1 IN VARCHAR2,
p_pay_address_line2 IN VARCHAR2,
p_pay_address_line3 IN VARCHAR2,
p_pay_address_city IN VARCHAR2,
p_pay_address_state IN VARCHAR2,
p_pay_address_zip IN VARCHAR2,
p_pay_address_country IN VARCHAR2,
p_old_bank_account_id IN NUMBER,
p_new_bank_account_id IN NUMBER,
p_bank_name IN VARCHAR2,
p_bank_branch_name IN VARCHAR2,
p_bank_num IN VARCHAR2,
p_bank_account_num IN VARCHAR2,
p_org_id IN NUMBER,
p_update_vendor_flag IN VARCHAR2,
p_org_name IN varchar2,
p_ccr_status IN varchar2,
p_insert_vendor_flag IN VARCHAR2,
p_prev_vendor_id IN NUMBER,
p_file_date IN DATE,
p_bank_conc_req_status IN VARCHAR2,
p_header_conc_req_status IN VARCHAR2,
p_assgn_conc_req_status IN VARCHAR2,
p_base_currency IN VARCHAR2,
p_valid_bank_info IN VARCHAR2,
p_federal_vendor IN VARCHAR2,
p_created_bank_branch_id IN NUMBER,
p_created_bank_account_id IN NUMBER,
x_vendor_id OUT NOCOPY NUMBER,
x_output OUT NOCOPY VARCHAR2,
x_react_pay_site_code OUT NOCOPY VARCHAR2,
x_react_main_site_code OUT NOCOPY VARCHAR2,
x_tp_changed OUT NOCOPY VARCHAR2,
x_vendor_name OUT NOCOPY VARCHAR2,
p_org_type_lookup IN VARCHAR2,
p_remit_poc IN VARCHAR2,
p_mail_poc IN VARCHAR2,
p_ar_us_phone IN VARCHAR2,
p_ar_fax IN VARCHAR2,
p_ar_email IN VARCHAR2,
p_ar_non_us_phone IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'FV_PROCESS_VENDORS';
l_update_bank_flag varchar2(1);
l_account_uses_insert_flag varchar2(1);
SELECT vendor_site_id,
address_line1,
address_line2 ,
address_line3 ,
address_lines_alt ,
city ,
state ,
country ,
zip
FROM ap_supplier_sites_all
WHERE vendor_id =p_exist_vendor_id
AND vendor_site_code=p_existing_vendor_site_code
AND org_id =p_existing_org_id;
l_update_tin_prf varchar2(3) := 'No';
l_update_tin_flg varchar2(3) := 'No';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'p_update_type: '|| p_update_type);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'p_update_vendor_flag: '||p_update_vendor_flag);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'p_insert_vendor_flag: '||p_insert_vendor_flag);
SELECT 1
INTO l_count
FROM ap_suppliers
WHERE vendor_name = l_legal_bus_name
AND num_1099 <> p_taxpayer_number;
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
l_account_uses_insert_flag := 'N'; -- Do not insert bank acct uses
l_account_uses_upd_flag := 'N'; -- Do not update bank acct uses
l_account_uses_insert_flag := 'I';
insert_for_report(l_duns_for_report,15,null,'CCR SUPPLIER BANK '
|| l_duns_for_report,p_bank_branch_name,null,null);
l_account_uses_insert_flag := 'U'; -- Update bank acct uses if relation already exist
insert_for_report(l_duns_for_report,14,null,p_bank_branch_name,
p_bank_num,null,null);
UPDATE fv_ccr_vendors
SET bank_branch_id = l_bank_branch_id
,enabled ='Y',last_update_date=sysdate,
last_updated_by=l_user_id,last_update_login=l_login_id
WHERE ccr_id = p_ccr_id;
IF (p_update_vendor_flag = 'Y') THEN
-- Get the vendor id if it already exists
IF(p_vendor_id <> 0) THEN
/* Changed the reference to US from USA */
IF(p_main_address_country = 'US' AND
(p_taxpayer_number IS NULL or length(p_taxpayer_number)<>9)) THEN
get_vendor_name(p_vendor_id,l_vendor_name,l_num_1099,l_org_type_lookup);
insert_for_report(p_duns,18,l_msg_text,null,null,null,null);
insert_for_report(p_duns,16,l_msg_text,null,
null,null,null);
l_update_tin_prf := get_profile_option('FV_CCR_UPDATE_TIN');
select count(*) into l_vendor_cnt
from fv_ccr_vendors fcv, fv_ccr_orgs fco
where fcv.ccr_id = fco.ccr_id
and fcv.DUNS <> p_duns and fcv.vendor_id = p_vendor_id
and (fco.pay_site_id is not null or fco.MAIN_ADDRESS_SITE_ID is not null);
if ((nvl(l_update_tin_prf, 'No') <> 'Yes') or (l_vendor_cnt > 0))then
l_update_tin_flg := 'No';
elsif nvl(l_update_tin_prf, 'No') = 'Yes' then
l_update_tin_flg := 'Yes';
insert_for_report(p_duns,16,l_msg_text,null,
null,null,null);
insert_for_report(p_duns,16,l_msg_text,null,
null,null,null);
update_vendor_org_type(
p_vendor_id => l_vendor_id,
p_vend_org_type => p_org_type_lookup,
x_status => l_status,
x_exception_msg => l_msg);
IF l_update_tin_flg = 'Yes' then
BEGIN
l_msg := null;
FV_CCR_UTIL_PVT.update_vendor(
p_vendor_id=>l_vendor_id,
p_taxpayer_id=>p_taxpayer_number,
x_status =>l_status,
x_exception_msg=>l_msg);
FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_UPDATE');
insert_for_report(p_duns,19,l_msg_text||nvl(l_excp_msg,''),null,null
,null,null);
UPDATE fv_ccr_vendors
SET vendor_id = p_vendor_id,
last_update_date=sysdate,
last_updated_by =l_user_id,
last_update_login=l_login_id
WHERE DUNS=p_duns;
IF(p_insert_vendor_flag = 'N' AND p_prev_vendor_id <> 0) THEN
l_vendor_id := p_prev_vendor_id;
insert_for_report(p_duns,16,l_msg_text,null,null,null,null);
insert_for_report(p_duns,18,l_msg_text,null,
null,null,null);
insert_for_report(p_duns,16,l_msg_text,null,null
,null,null);
/*** If vendor id does not exist create the vendor else update the vendor ***/
if(l_vendor_id IS NULL OR l_vendor_id = 0 ) THEN
BEGIN
l_msg := null;
fv_utility.log_mesg('before calling insert_vendor');
FV_CCR_UTIL_PVT.insert_vendor
(
--p_vendor_name => p_legal_bus_name,
p_vendor_name => l_legal_bus_name,
p_taxpayer_id => p_taxpayer_number,
p_supplier_number=>l_supplier_number,
p_org_type_lookup_code => p_org_type_lookup,
x_vendor_id =>l_vendor_id,
x_status=>l_status,
x_exception_msg=>l_msg);
fv_utility.log_mesg('after calling insert_vendor');
--insert_for_report(p_duns,12,null,p_legal_bus_name,
insert_for_report(p_duns,12,null,l_legal_bus_name,
p_taxpayer_number,null,null);
UPDATE fv_ccr_vendors
SET vendor_id =l_vendor_id,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE DUNS = p_duns;
FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_INSERT');
insert_for_report(p_duns,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
UPDATE fv_ccr_orgs
SET bank_account_id = l_new_bank_account_id,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id = p_ccr_id
AND org_id=p_org_id;
UPDATE fv_ccr_vendors
SET conc_request_status=l_header_conc_req_status,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id=p_ccr_id;
UPDATE fv_ccr_vendors
SET vendor_id =l_vendor_id,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE DUNS = p_duns;
--This is the case where DUNS4 is newly inserted and
--does not have vendor_id update it with DUNS/DUNS4
--vendor id
IF(nvl(p_vendor_id,0)<>0) THEN
l_vendor_id:=p_vendor_id;
insert_for_report(p_duns,16,l_msg_text,null,null
,null,null);
FV_CCR_UTIL_PVT.insert_vendor_site(
p_vendor_site_code=>l_plus4,
p_vendor_id=>l_vendor_id ,
p_org_id =>p_org_id,
p_address_line1=>p_main_address_line1,
p_address_line2=>p_main_address_line2,
p_address_line3=>null,
p_address_line4=>null,
p_city=>p_main_address_city,
p_state=>l_state,
p_zip=>p_main_address_zip,
p_province=>l_province,
p_country=>p_main_address_country,
p_duns_number=>p_duns,
p_pay_site_flag=> NULL,
p_hold_unvalidated_inv_flag=>'N',
p_hold_all_payments_flag=>'N',
p_us_phone => NULL,
p_fax => NULL,
p_email => NULL,
p_non_us_phone => NULL,
p_purchasing_site_flag => 'Y',
x_vendor_site_id=>l_main_add_site_id,
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
UPDATE hz_party_sites
SET addressee = p_mail_poc
WHERE party_site_id = l_party_site_id;
insert_for_report(l_duns_for_report,13,null,l_plus4,l_vendor_name,'M',null);
FV_CCR_UTIL_PVT.update_vendor_site(
p_vendor_site_code =>null,
p_vendor_site_id=>l_main_add_site_id,
p_org_id => p_org_id,
p_address_line1=>p_main_address_line1,
p_address_line2=>p_main_address_line2,
p_address_line3=>null,
p_address_line4=>null,
p_city=>p_main_address_city,
p_state=>l_state,
p_zip=>p_main_address_zip,
p_province=>l_province, --To be populated for canadian vendors.
p_country=>p_main_address_country,
p_duns_number=>p_duns,
p_pay_site_flag => NULL,
p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
p_hold_all_payments_flag=>l_hold_all_payments_flag,
p_us_phone => NULL,
p_fax => NULL,
p_email => NULL,
p_non_us_phone => NULL,
p_purchasing_site_flag => 'Y',
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
UPDATE hz_party_sites
SET addressee = p_mail_poc
WHERE party_site_id = l_party_site_id;
FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_INSERT');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_UPDATE');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
SELECT COUNT(*)
INTO l_supplier_sites_count
FROM ap_supplier_sites_all
WHERE vendor_id=l_vendor_id
AND org_id =p_org_id;
update iby_external_payees_all
set inactive_date=sysdate
where supplier_site_id=l_ss_vendor_site_id
AND org_id = p_org_id;
update iby_pmt_instr_uses_all
set end_date=sysdate
where ext_pmt_party_id in
(select distinct ext_payee_id from iby_external_payees_all
where supplier_site_id=l_ss_vendor_site_id
and org_id = p_org_id);
UPDATE ap_supplier_sites_all
SET inactive_date = sysdate,
vendor_site_code=substr('old_'||VENDOR_SITE_ID||'_'||p_duns,0,15)
WHERE duns_number=p_duns
AND vendor_id =l_vendor_id
AND org_id = p_org_id;
insert_for_report(p_duns,16,l_msg_text,null,null
,null,null);
l_account_uses_insert_flag := 'N';
FV_CCR_UTIL_PVT.insert_vendor_site(
p_vendor_site_code=>l_plus4,
p_vendor_id=>l_vendor_id ,
p_org_id =>p_org_id,
p_address_line1=>p_pay_address_line1,
p_address_line2=>p_pay_address_line2,
p_address_line3=>p_pay_address_line3,
p_address_line4=>null,
p_city=>p_pay_address_city,
p_state=>l_state,
p_zip=>p_pay_address_zip,
p_province=>l_province,
p_country=>p_pay_address_country,
p_duns_number=>p_duns,
p_pay_site_flag=>l_pay_site_flag,
p_hold_unvalidated_inv_flag=>'N',
p_hold_all_payments_flag=>'N',
p_us_phone => p_ar_us_phone,
p_fax => p_ar_fax,
p_email => p_ar_email,
p_non_us_phone => p_ar_non_us_phone,
--p_purchasing_site_flag => NULL,
p_purchasing_site_flag => 'Y',
x_vendor_site_id=>l_pay_site_id,
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
UPDATE hz_party_sites
SET addressee = p_remit_poc
WHERE party_site_id = l_party_site_id;
l_account_uses_insert_flag := 'I';
insert_for_report(l_duns_for_report,13,null,l_plus4,l_vendor_name,'P',null);
FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_INSERT');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
l_account_uses_insert_flag := 'N';
IF(l_account_uses_insert_flag <> 'I' AND l_account_uses_insert_flag<>'N') THEN
l_account_uses_insert_flag := 'U';
FV_CCR_UTIL_PVT.update_vendor_site(
p_vendor_site_code =>null,
p_vendor_site_id=>p_pay_site_id,
p_org_id => p_org_id,
p_address_line1=>p_pay_address_line1,
p_address_line2=>p_pay_address_line2,
p_address_line3=>p_pay_address_line3,
p_address_line4=>null,
p_city=>p_pay_address_city,
p_state=>l_state,
p_zip=>p_pay_address_zip,
p_province=>l_province,
p_country=>p_pay_address_country,
p_duns_number=>p_duns,
--p_pay_site_flag => NULL, mod for bug 6348043
p_pay_site_flag => 'Y',
p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
p_hold_all_payments_flag=>l_hold_all_payments_flag,
p_us_phone => p_ar_us_phone,
p_fax => p_ar_fax,
p_email => p_ar_email,
p_non_us_phone => p_ar_non_us_phone,
--p_purchasing_site_flag => NULL,
p_purchasing_site_flag => 'Y',
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
UPDATE hz_party_sites
SET addressee = p_remit_poc
WHERE party_site_id = l_party_site_id;
FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_UPDATE');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
l_account_uses_insert_flag,
p_org_id );
UPDATE ap_supplier_sites_all
SET duns_number=0,
party_site_id=0
WHERE vendor_site_code='old_'||p_duns
AND vendor_id =l_vendor_id
AND org_id = p_org_id;
UPDATE fv_ccr_vendors
SET conc_request_status=l_header_conc_req_status,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id=p_ccr_id;
UPDATE FV_CCR_ORGS
SET pay_site_id = l_pay_site_id,
main_address_site_id = l_main_add_site_id,
bank_account_id = l_new_bank_account_id,
conc_request_status=l_assgn_conc_req_status,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id = p_ccr_id
AND org_id = p_org_id;
SELECT party_id
INTO l_party_id
FROM ap_suppliers
WHERE vendor_id = l_vendor_id;
AP_AUTOMATIC_PROPAGATION_PKG.Update_Payment_Schedules (
p_from_bank_account_id => p_old_bank_account_id,
p_to_bank_account_id => l_new_bank_account_id,
p_vendor_id => l_vendor_id,
p_vendor_site_id =>l_pay_site_id,
p_party_Site_Id => null,
p_org_id => p_org_id,
p_party_id => l_party_id);
FV_CCR_UTIL_PVT.update_vendor_site(
p_vendor_site_code =>null,
p_vendor_site_id=>p_pay_site_id,
p_org_id => p_org_id,
p_address_line1=>p_pay_address_line1,
p_address_line2=>p_pay_address_line2,
p_address_line3=>p_pay_address_line3,
p_address_line4=>null,
p_city=>p_pay_address_city,
p_state=>l_state,
p_zip=>p_pay_address_zip,
p_province=>l_province,
p_country=>p_pay_address_country,
p_duns_number=>p_duns,
--p_pay_site_flag => NULL, mod for bug 6348043
p_pay_site_flag => NULL,
--p_hold_unvalidated_inv_flag=>'Y',Commented and below for bug 9442110
--p_hold_all_payments_flag=>'Y',Commented and below for bug 9442110
p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
p_hold_all_payments_flag=>l_hold_all_payments_flag,
p_us_phone => p_ar_us_phone,
p_fax => p_ar_fax,
p_email => p_ar_email,
p_non_us_phone => p_ar_non_us_phone,
p_purchasing_site_flag => NULL,
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_UPDATE');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
FV_CCR_UTIL_PVT.update_vendor_site(
p_vendor_site_code =>null,
p_vendor_site_id=>p_main_add_site_id,
p_org_id => p_org_id,
p_address_line1=>p_main_address_line1,
p_address_line2=>p_main_address_line2,
p_address_line3=>null,
p_address_line4=>null,
p_city=>p_main_address_city,
p_state=>l_state,
p_zip=>p_main_address_zip,
p_province=>l_province,
p_country=>p_main_address_country,
p_duns_number=>p_duns,
p_pay_site_flag => NULL,
--p_hold_unvalidated_inv_flag=>'Y',Commented and below for bug 9442110
--p_hold_all_payments_flag=>'Y',Commented and below for bug 9442110
p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
p_hold_all_payments_flag=>l_hold_all_payments_flag,
p_us_phone => NULL,
p_fax => NULL,
p_email => NULL,
p_non_us_phone => NULL,
p_purchasing_site_flag => NULL,
x_party_site_id => l_party_site_id,
x_status=>l_status,
x_exception_msg=>l_msg);
FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_UPDATE');
insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
UPDATE fv_ccr_vendors
SET conc_request_status=l_header_conc_req_status,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id=p_ccr_id;
UPDATE fv_ccr_orgs
SET conc_request_status=l_assgn_conc_req_status,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE ccr_id=p_ccr_id
AND org_id=p_org_id;
PROCEDURE delete_plusfour_assignments(p_ccrid number)
as
v_plus_four varchar2(100);
l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.delete_plusfour_assignments';
update fv_ccr_vendors
set vendor_id = null
where duns = (select duns from fv_ccr_vendors where ccr_id = p_ccrid)
and plus_four is not null;
select plus_four into v_plus_four
from fv_ccr_vendors
where ccr_id = p_ccrid;
delete from fv_ccr_orgs
where ccr_id in (
select ccr_id
from fv_ccr_vendors
where duns = (select duns from fv_ccr_vendors where ccr_id =
p_ccrid)
and plus_four is not null);
END delete_plusfour_assignments;
for crec in (select pay_site_id
from fv_ccr_orgs o, hr_organization_units ou, ap_supplier_sites_all vs
where o.pay_site_id is not null
and o.pay_site_id = vs.VENDOR_SITE_ID
and vs.ORG_ID = ou.organization_id
and ou.organization_id = nvl(p_org_id,ou.organization_id)
and ccr_id=p_ccrid)
loop
v_count := v_count + 1;
for crec in (select main_address_site_id
from fv_ccr_orgs o, hr_organization_units ou, po_vendor_sites_all vs
where o.main_address_site_id is not null
and o.main_address_site_flag = 'Y'
and o.main_address_site_id = vs.VENDOR_SITE_ID
and vs.ORG_ID = ou.organization_id
and ou.organization_id = nvl(v_org_id,ou.organization_id)
and ccr_id=p_ccrid)
loop
v_count := v_count + 1;
SELECT p_lookup_code||' - '||meaning meaning
INTO l_lookup_meaning
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND language = userenv('LANG');
SELECT COUNT(*)
INTO l_count
FROM fv_ccr_orgs fco
WHERE fco.ccr_id = p_ccr_id
AND mo_global.check_access(fco.org_id)<>'Y';
SELECT COUNT(*)
INTO l_count
FROM ap_duplicate_vendors_all
WHERE duplicate_vendor_id = p_vendor_id
AND process_flag <> 'Y';
PROCEDURE insert_vendor
(
p_vendor_name IN varchar2,
p_taxpayer_id IN varchar2,
p_supplier_number IN varchar2,
p_org_type_lookup_code IN VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
fv_utility.log_mesg('Exception in fv_ccr_util_pvt.insert_vendor.');
END insert_vendor;
PROCEDURE update_vendor
(
p_vendor_id IN NUMBER,
p_taxpayer_id IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_party_id number;
SELECT party_id INTO l_party_id
FROM ap_suppliers
WHERE vendor_id = p_vendor_id;
UPDATE hz_parties
SET JGZZ_FISCAL_CODE = p_taxpayer_id
where party_id = l_party_id;
update hz_organization_profiles
set JGZZ_FISCAL_CODE = p_taxpayer_id
where party_id = l_party_id;
UPDATE ap_suppliers
SET num_1099 = p_taxpayer_id
WHERE vendor_id = p_vendor_id;
END update_vendor;
PROCEDURE insert_vendor_site
(
p_vendor_site_code IN VARCHAR2,
p_vendor_id IN NUMBER,
p_org_id IN NUMBER,
p_address_line1 IN VARCHAR2,
p_address_line2 IN VARCHAR2,
p_address_line3 IN VARCHAR2,
p_address_line4 IN VARCHAR2,
p_city IN VARCHAR2,
p_state IN VARCHAR2,
p_zip IN VARCHAR2,
p_province IN VARCHAR2,
p_country IN VARCHAR2,
p_duns_number IN VARCHAR2,
p_pay_site_flag IN VARCHAR2,
p_hold_unvalidated_inv_flag IN VARCHAR2,
p_hold_all_payments_flag IN VARCHAR2,
p_us_phone IN VARCHAR2,
p_fax IN VARCHAR2,
p_email IN VARCHAR2,
p_non_us_phone IN VARCHAR2,
p_purchasing_site_flag IN VARCHAR2,
x_vendor_site_id OUT NOCOPY NUMBER,
x_party_site_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
Select cage_code, legal_bus_name, dba_name, division_name
into l_cage_code, l_legal_bus_name, l_dba_name, l_division_name
from fv_ccr_vendors where duns = p_duns_number and rownum<=1;
END insert_vendor_site;
PROCEDURE update_vendor_site
(
p_vendor_site_code IN VARCHAR2,
p_vendor_site_id IN NUMBER,
p_org_id IN NUMBER,
p_address_line1 IN VARCHAR2,
p_address_line2 IN VARCHAR2,
p_address_line3 IN VARCHAR2,
p_address_line4 IN VARCHAR2,
p_city IN VARCHAR2,
p_state IN VARCHAR2,
p_zip IN VARCHAR2,
p_province IN VARCHAR2,
p_country IN VARCHAR2,
p_duns_number IN VARCHAR2,
p_pay_site_flag IN VARCHAR2,
p_hold_unvalidated_inv_flag IN VARCHAR2,
p_hold_all_payments_flag IN VARCHAR2,
p_us_phone IN VARCHAR2,
p_fax IN VARCHAR2,
p_email IN VARCHAR2,
p_non_us_phone IN VARCHAR2,
p_purchasing_site_flag IN VARCHAR2,
x_party_site_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2
)
IS
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
Select cage_code, legal_bus_name, dba_name, division_name
into l_cage_code, l_legal_bus_name, l_dba_name, l_division_name
from fv_ccr_vendors where duns = p_duns_number and rownum<=1;
AP_VENDOR_PUB_PKG.update_vendor_site(
p_api_version => l_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit=> FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_ret_stat,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
p_vendor_site_rec=>l_vendor_site_rec,
p_vendor_site_id=>p_vendor_site_id,
p_calling_prog=>'CCRImport'); --Bug 6519638
SELECT party_site_id,location_id
INTO l_party_site_id,l_location_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id;
-- Update the location information
BEGIN
select object_version_number
into l_object_version_number
from hz_locations
where location_id = l_location_id;
hz_location_v2pub.update_location(
FND_API.G_TRUE,
l_location_rec,
l_object_version_number,
l_ret_stat,
l_msg_count,
l_msg_data);
-- Update party site information
BEGIN
l_party_site_rec.party_site_id := l_party_site_id;
select object_version_number
into l_object_version_number
from hz_party_sites
where party_site_id = l_party_site_id;
hz_party_site_v2pub.update_party_site(
FND_API.G_TRUE,
l_party_site_rec,
l_object_version_number,
l_ret_stat,
l_msg_count,
l_msg_data);
END update_vendor_site;
select ach_us_phone, ach_non_us_phone, ach_email, ach_fax
into l_ach_us_phone, l_ach_non_us_phone, l_ach_email, l_ach_fax from fv_ccr_vendors
where duns=p_duns_number and rownum<=1;
PROCEDURE update_bank_account
(
p_bank_account_id NUMBER,
p_bank_account_type VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_bank_account_type iby_ext_bank_accounts.bank_account_type%TYPE;
UPDATE iby_ext_bank_accounts
set BANK_ACCOUNT_TYPE = l_bank_account_type
where EXT_BANK_ACCOUNT_ID = p_bank_account_id;
fv_utility.log_mesg('When others error in update_bank_account.');
END update_bank_account;
p_account_uses_insert_flag IN VARCHAR2,
p_org_id IN NUMBER
)
IS
l_api_version CONSTANT NUMBER:= 1.0;
SELECT party_id,vendor_type_lookup_code
INTO l_party_id,l_vendor_type_lookup_code
FROM ap_suppliers
WHERE vendor_id=p_vendor_id;
SELECT party_site_id INTO l_party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_pay_site_id;
UPDATE iby_pmt_instr_uses_all
SET END_DATE = sysdate-1,
last_update_date=sysdate,
last_updated_by=l_user_id,
last_update_login=l_login_id
WHERE instrument_id <> p_new_bank_account_id
AND EXT_PMT_PARTY_ID = l_payee_id
AND END_DATE IS NULL;
(p_account_uses_insert_flag = 'I' OR p_account_uses_insert_flag = 'U') AND
(p_valid_bank_info='Y' OR p_federal_vendor='N')) THEN
l_uses_reln_exists := 'N';
IF(p_account_uses_insert_flag = 'U') THEN
IBY_DISBURSEMENT_SETUP_PUB.Get_Payee_Instr_Assignments(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_payee => l_payee,
x_assignments => l_assignment_tab,
x_response => l_response);
UPDATE iby_pmt_instr_uses_all
SET end_date=null,
start_date=p_file_date,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login=l_user_id
WHERE (l_assign_id IS NOT NULL AND INSTRUMENT_PAYMENT_USE_ID=l_assign_id);
IF (l_uses_reln_exists = 'N' OR p_account_uses_insert_flag = 'I') THEN
l_assignment.Instrument.instrument_Type := 'BANKACCOUNT';
select nvl(taxpayer_id, null) into l_ccr_tin from fv_ccr_vendors where duns = p_duns;
select nvl(num_1099, null) into l_supplier_tin from ap_suppliers where vendor_id = p_vendor_id;
select meaning into l_ext_cert_val
from fnd_lookup_values
where lookup_type like 'FV_EXTERNAL_CERTIFICATION'
and lookup_code =(select code from fv_ccr_class_codes where duns = p_duns
and codetype like 'External Certification' AND code like 'DFD%'
AND ROWNUM <=1)
and language = Userenv('LANG');
select meaning into l_ext_cert_val
from fnd_lookup_values
where lookup_type like 'FV_EXTERNAL_CERTIFICATION'
and lookup_code =(select code from fv_ccr_class_codes where duns = p_duns
and codetype like 'External Certification' AND code like 'EPL%'
AND ROWNUM <=1)
and language = Userenv('LANG');
select flagtype, flagval into l_flagtype, l_flagval
from fv_ccr_flags where duns = p_duns and rownum<=1;
select meaning into l_flag_code
from fnd_lookup_values
where lookup_type like 'FV_CCR_FLAGS'
and lookup_code = l_flagtype||l_flagval
and language = Userenv('LANG');
select meaning into l_flag_code
from fnd_lookup_values
where lookup_type like 'FV_CCR_FLAGS'
and lookup_code = l_flagtype
and language = Userenv('LANG');
select substr(code, (instr(code, '^', 1,1) + 1), ((instr(code, '^', 1,2)- instr(code, '^', 1,1))-1) ) ,
substr(code, (instr(code, '^', 1,2) + 1), ((instr(code, '^', 1,3)- instr(code, '^', 1,2))-1) )
into l_lse, l_lsr
from fv_ccr_class_codes where duns = p_duns and codetype like 'CCR Numerics' and code like 'LS%'
and rownum<=1;
select substr(code, (instr(code, '^', 1,1) + 1), ((instr(code, '^', 1,2)- instr(code, '^', 1,1))-1) ) ,
substr(code, (instr(code, '^', 1,2) + 1), ((instr(code, '^', 1,3)- instr(code, '^', 1,2))-1) ) ,
substr(code, (instr(code, '^', 1,3) + 1), ((instr(code, '^', 1,4)- instr(code, '^', 1,3))-1) ) ,
substr(code, (instr(code, '^', 1,4) + 1), ((length(code)- instr(code, '^', 1,4))) )
into l_cblc, l_cbla, l_sblc, l_sbla
from fv_ccr_class_codes where duns = p_duns and codetype like 'CCR Numerics' and code like 'BL%'
and rownum<=1;
select substr(code, (instr(code, '^', 1,1) + 1), ((instr(code, '^', 1,2)- instr(code, '^', 1,1))-1) )
into l_bk
from fv_ccr_class_codes where duns = p_duns and codetype like 'CCR Numerics' and code like 'BK%'
and rownum<=1;
select substr(code, (instr(code, '^', 1,1) + 1), ((instr(code, '^', 1,2)- instr(code, '^', 1,1))-1) )
into l_pg
from fv_ccr_class_codes where duns = p_duns and codetype like 'CCR Numerics' and code like 'PG%'
and rownum<=1;
select substr(code, (instr(code, '^', 1,1) + 1), ((instr(code, '^', 1,2)- instr(code, '^', 1,1))-1) )
into l_pt
from fv_ccr_class_codes where duns = p_duns and codetype like 'CCR Numerics' and code like 'PT%'
and rownum<=1;
select lpad('State :', 33)||substr(p_code,4)||' - '||meaning
into l_dis_code_val
from fnd_lookup_values flv
where flv.lookup_type = 'FV_DIS_CODE_STATE'
and flv.lookup_code = substr(p_code, 4)
and flv.language = userenv('LANG')
and rownum<=1;
select lpad('County :', 33)||substr(p_code,4)||' - '||meaning
into l_dis_code_val
from fnd_lookup_values flv
where flv.lookup_type = 'FV_DIS_CODE_COUNTY'
and flv.lookup_code = substr(p_code, 4)
and flv.language = userenv('LANG')
and rownum<=1;
select lpad('Metropolitan Statistical Area :', 33)||substr(p_code,4)||' - '||meaning
into l_dis_code_val
from fnd_lookup_values flv
where flv.lookup_type = 'FV_DIS_CODE_MSA'
and flv.lookup_code = substr(p_code, 4)
and flv.language = userenv('LANG')
and rownum<=1;
select substr(p_code,4)||' - '||meaning
into l_dis_code_val
from fnd_lookup_values flv
where flv.lookup_type = 'FV_DIS_CODE_ANY'
and flv.lookup_code = substr(p_code, 4)
and flv.language = userenv('LANG')
and rownum<=1;
select fcc.code into l_debar_code
from fv_ccr_vendors fcv, fv_ccr_class_codes fcc, fv_ccr_orgs fco
where fcv.duns = fcc.duns(+)
and fcv.ccr_id = fco.ccr_id(+)
and fcv.vendor_id = p_supplier_id
and fco.pay_site_id = p_supplier_site_id;