[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION Insert_Rejections (
p_parent_table IN VARCHAR2,
p_parent_id IN NUMBER,
p_reject_code IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
l_current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(100) := 'INSERT_REJECTIONS';
l_current_calling_sequence := 'AP_VENDOR_PUB_PKG.Insert_rejections<-'
||P_calling_sequence;
l_debug_info := '(Insert Rejections 1) Insert into AP_SUPPLIER_INT_REJECTIONS, '||
'REJECT CODE:'||p_reject_code;
INSERT INTO AP_SUPPLIER_INT_REJECTIONS(
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
VALUES (
p_parent_table,
p_parent_id,
p_reject_code,
p_last_updated_by,
SYSDATE,
p_last_update_login,
p_last_updated_by,
SYSDATE);
END Insert_Rejections;
SELECT COUNT(*)
INTO l_count
FROM fnd_currencies_vl
WHERE currency_code = p_currency_code
AND enabled_flag = 'Y'
AND currency_flag = 'Y'
AND TRUNC(NVL(start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(end_date_active, SYSDATE))>= TRUNC(SYSDATE);
SELECT lookup_code
INTO l_dummy_lookup
FROM ap_lookup_codes
WHERE lookup_type = p_lookup_type
AND lookup_code = p_column_value
AND enabled_flag = 'Y'
AND nvl(inactive_date,sysdate+1) > sysdate;
SELECT lookup_code
INTO l_dummy_lookup
FROM po_lookup_codes
WHERE lookup_type = p_lookup_type
AND lookup_code = p_column_value
AND enabled_flag = 'Y'
AND nvl(inactive_date,sysdate+1) > sysdate;
SELECT lookup_code
INTO l_dummy_lookup
FROM fnd_lookups
--FROM fnd_lookup_vlaues
--modified by abhsaxen from fnd_lookup_vlaues to fnd_lookups
--as fnd_lookups is respecting user language.
WHERE lookup_type = p_lookup_type
AND lookup_code = p_column_value
AND enabled_flag = 'Y'
AND nvl(end_date_active,sysdate+1) > sysdate;
SELECT count(*)
INTO l_dup_count_org_id
-- bug 7430783 Changing validation table to ap_supplier_sites_all
--FROM po_vendor_sites_all SITE
FROM ap_supplier_sites_all SITE
WHERE SITE.vendor_id = x_vendor_id
AND SITE.org_id = x_org_id
AND UPPER(SITE.vendor_site_code) = UPPER(x_vendor_site_code);
SELECT count(*)
INTO l_dup_count_org_name
-- bug 7430783 Changing validation table to ap_supplier_sites_all
--FROM po_vendor_sites_all SITE, HR_OPERATING_UNITS ORG
FROM ap_supplier_sites_all SITE, HR_OPERATING_UNITS ORG
WHERE SITE.vendor_id = x_vendor_id
AND ORG.name = x_org_name
AND UPPER(vendor_site_code) = UPPER(x_vendor_site_code)
AND SITE.org_id = ORG.organization_id;
SELECT COUNT(*)
INTO l_count
FROM hz_party_sites hps, ap_suppliers aps
WHERE aps.vendor_id = p_vendor_id
AND hps.party_id = aps.party_id
AND UPPER(hps.party_site_name) = UPPER(p_vendor_site_code);
SELECT count(*)
INTO l_dup_count_org_id
FROM ap_supplier_sites_all SITE
WHERE SITE.vendor_id = x_vendor_id
AND SITE.org_id = x_org_id
AND ((x_vendor_site_ID is not null and SITE.vendor_site_ID <> x_vendor_site_ID) OR
(x_vendor_site_code is not null and UPPER(SITE.vendor_site_code) <> UPPER(x_vendor_site_code)))
AND UPPER(SITE.ece_tp_location_code) = UPPER(x_ece_tp_location_code) ;
SELECT count(*)
INTO l_dup_count_org_name
FROM ap_supplier_sites_all SITE, HR_OPERATING_UNITS ORG
WHERE SITE.vendor_id = x_vendor_id
AND ORG.name = x_org_name
AND SITE.org_id = ORG.organization_id
AND ((x_vendor_site_ID is not null and SITE.vendor_site_ID <> x_vendor_site_ID) OR
(x_vendor_site_code is not null and UPPER(SITE.vendor_site_code) <> UPPER(x_vendor_site_code)))
AND UPPER(SITE.ece_tp_location_code) = UPPER(x_ece_tp_location_code);
/* SELECT employee_id
INTO l_dummy
FROM hr_employees_current_v
WHERE employee_id = p_employee_id;*/
SELECT employee_id
INTO l_dummy
FROM HR_CURRENT_EMP hre
WHERE hre.employee_id = p_employee_id
AND hre.BUSINESS_GROUP_ID IN ( SELECT fsp.BUSINESS_GROUP_ID
FROM FINANCIALS_SYSTEM_PARAMETERS fsp);
SELECT nvl(count(freight_code),0)
INTO l_dummy
FROM org_freight
WHERE organization_id = p_inventory_org_id
AND nvl(disable_date, sysdate +1 ) > sysdate
AND freight_code = p_ship_via_lookup_code;
SELECT set_of_books_id
INTO l_dummy
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id = p_sob_id;
SELECT COUNT(*)
INTO l_count
FROM po_vendors
WHERE (p_vendor_id IS NULL OR p_vendor_id = ap_null_num OR vendor_id <> p_vendor_id) --bug7023543
AND employee_id = p_employee_id;
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers
WHERE segment1 = p_segment1;
/* Bug 6939863 - Made Chk_Dup_Vendor_Name_new with same logic as update.
* Bug 5606948 added a call to Chk_Dup_Vendor_Name_update in
* PROCEDURE Validate_Vendor even in Insert mode.
* Chk_Dup_Vendor_Name_new is not called from anywhere, so made this similar
* to Chk_Dup_Vendor_Name_update and also corrected employee_id logic
* in both calls.
PROCEDURE Chk_Dup_Vendor_Name_new(p_vendor_name IN VARCHAR2,
x_valid OUT NOCOPY BOOLEAN
) IS
l_count NUMBER := 0;
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers_int
WHERE UPPER(vendor_name) = UPPER(p_vendor_name);
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id); --bug 5606948
SELECT 1
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 13629613
AND nvl(VENDOR_TYPE_LOOKUP_CODE,'nonemp') <> 'EMPLOYEE'
AND employee_id is NULL
AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 6939863 - changed <> to = for employee_id
AND (p_employee_id IS NULL OR employee_id = p_employee_id);
SELECT 1
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 6939863 - changed <> to = for employee_id
AND (p_employee_id IS NULL OR employee_id = p_employee_id)
AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_count
FROM po_vendors
WHERE (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
AND segment1 = p_segment1;
SELECT count(*)
INTO l_count
FROM po_history_vendors
WHERE segment1 = p_segment1;
SELECT routing_header_id
INTO l_dummy
FROM RCV_ROUTING_HEADERS
WHERE routing_header_id = p_rcv_rtg_id;
SELECT count(*)
INTO l_count
FROM fnd_currencies_vl
WHERE currency_code = p_payment_currency_code
AND (gl_currency_api.is_fixed_rate(p_invoice_currency_code
,currency_code
,sysdate)= 'Y'
AND enabled_flag = 'Y'
AND trunc(nvl(start_date_active,sysdate)) <= trunc(sysdate)
AND trunc(nvl(end_date_active,sysdate)) >= trunc(sysdate)
);
SELECT COUNT(*)
INTO l_count
FROM ap_income_tax_types
WHERE income_tax_type = p_type_1099
AND TRUNC(SYSDATE) < TRUNC(NVL(inactive_date, SYSDATE+1));
SELECT GCC.code_combination_id
INTO l_ccid
FROM GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS GSOB
WHERE GCC.code_combination_id = p_ccid
AND GCC.account_type = 'L'
AND GCC.enabled_flag = 'Y'
AND GCC.detail_posting_allowed_flag = 'Y'
AND GSOB.set_of_books_id = p_sob_id
AND GSOB.chart_of_accounts_id = GCC.chart_of_accounts_id
AND nvl(GCC.end_date_active,sysdate+1) > sysdate;
SELECT GCC.code_combination_id
INTO l_ccid
FROM GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS GSOB
WHERE GCC.code_combination_id = p_ccid
AND GCC.enabled_flag = 'Y'
AND GCC.detail_posting_allowed_flag = 'Y'
AND GCC.chart_of_accounts_id = GSOB.chart_of_accounts_id
AND GSOB.set_of_books_id = p_sob_id
AND nvl(GCC.end_date_active,sysdate+1) > sysdate;
PROCEDURE Chk_Dup_Vendor_Name_update(p_vendor_name IN VARCHAR2,
p_vendor_id IN NUMBER,
p_vendor_type_lookup_code IN VARCHAR2,
p_employee_id IN NUMBER,
x_valid OUT NOCOPY BOOLEAN
) IS
l_count NUMBER := 0;
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id); --bug 5606948
SELECT 1
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 13629613
AND nvl(VENDOR_TYPE_LOOKUP_CODE,'nonemp') <> 'EMPLOYEE'
AND employee_id is NULL
AND ROWNUM = 1; --bug 5606948
SELECT COUNT(*)
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 6939863 - changed <> to = for employee_id
AND (p_employee_id IS NULL OR employee_id = p_employee_id);
SELECT 1
INTO l_count
FROM ap_suppliers
WHERE UPPER(vendor_name) = UPPER(p_vendor_name)
AND (p_vendor_id IS NULL OR vendor_id <> p_vendor_id)
--bug 6939863 - changed <> to = for employee_id
AND (p_employee_id IS NULL OR employee_id = p_employee_id)
AND ROWNUM = 1;
END Chk_Dup_Vendor_Name_update;
SELECT supplier_numbering_method
INTO l_ven_num_code
FROM ap_product_setup;
/*Open Issue 11 -- This select needs to be adjusted for MOAC*/
SELECT nvl(supplier_numbering_method, 'MANUAL')
INTO l_ven_num_code
FROM ap_product_setup;
SELECT allow_awt_flag
INTO l_asp_awt_flag
FROM ap_system_parameters
WHERE org_id = p_org_id;
SELECT name
INTO l_dummy_name
FROM AP_AWT_GROUPS
WHERE group_id = p_awt_id
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT group_id
INTO l_dummy_id
FROM AP_AWT_GROUPS
WHERE group_id = p_awt_id
AND name = p_awt_name
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT group_id
INTO p_awt_id
FROM AP_AWT_GROUPS
WHERE name = p_awt_name
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT name
INTO l_dummy_name
FROM AP_AWT_GROUPS
WHERE group_id = p_pay_awt_id
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT group_id
INTO l_dummy_id
FROM AP_AWT_GROUPS
WHERE group_id = p_pay_awt_id
AND name = p_pay_awt_name
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT group_id
INTO p_pay_awt_id
FROM AP_AWT_GROUPS
WHERE name = p_pay_awt_name
AND sysdate < nvl(inactive_date, sysdate + 1);
SELECT employee_id
INTO l_dummy
FROM hr_employees_current_v
WHERE employee_id = p_hold_by;
SELECT name
INTO l_terms_name
FROM AP_TERMS_TL
WHERE term_id = p_terms_id
AND language = userenv('LANG')
AND sysdate < nvl(end_date_active, sysdate+1);
SELECT term_id
INTO l_terms_id
FROM AP_TERMS_TL
WHERE term_id = p_terms_id
AND name = p_terms_name
AND language = userenv('LANG')
AND sysdate < nvl(end_date_active, sysdate+1);
SELECT term_id
INTO p_terms_id
FROM AP_TERMS_TL
WHERE name = p_terms_name
AND language = userenv('LANG')
AND sysdate < nvl(end_date_active, sysdate+1);
SELECT distribution_set_id
INTO l_dist_id
FROM AP_DISTRIBUTION_SETS_ALL
WHERE distribution_set_id = p_dist_id
AND distribution_set_name = p_dist_name
AND sysdate < nvl(inactive_date, sysdate+1);
SELECT distribution_set_id
INTO p_dist_id
FROM AP_DISTRIBUTION_SETS_ALL
WHERE distribution_set_name = p_dist_name
AND sysdate < nvl(inactive_date, sysdate+1);
SELECT distribution_set_name
INTO l_dist_name
FROM AP_DISTRIBUTION_SETS_ALL
WHERE distribution_set_id = p_dist_id
AND sysdate < nvl(inactive_date, sysdate+1);
SELECT lot.location_code
INTO l_ship_locn_code
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE loc.location_id = p_ship_location_id
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.ship_to_site_flag = 'Y';
SELECT loc.location_id
INTO l_ship_locn_id
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE lot.location_code = p_ship_location_code
AND loc.location_id = p_ship_location_id
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.ship_to_site_flag = 'Y';
SELECT loc.location_id
INTO p_ship_location_id
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE lot.location_code = p_ship_location_code
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.ship_to_site_flag = 'Y';
SELECT lot.location_code
INTO l_bill_locn_code
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE loc.location_id = p_bill_location_id
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.bill_to_site_flag = 'Y';
SELECT loc.location_id
INTO l_bill_locn_id
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE loc.location_id = p_bill_location_id
AND lot.location_code = p_bill_location_code
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.bill_to_site_flag = 'Y';
SELECT loc.location_id
INTO p_bill_location_id
FROM HR_LOCATIONS_ALL loc, HR_LOCATIONS_ALL_TL lot
WHERE lot.location_code = p_bill_location_code
AND sysdate < nvl(loc.inactive_date, sysdate + 1)
AND loc.location_id = lot.location_id
AND lot.language = userenv('LANG')
AND (nvl(loc.business_group_id,nvl(hr_general.get_business_group_id,-99))=
nvl(hr_general.get_business_group_id,-99))
AND loc.bill_to_site_flag = 'Y';
SELECT vendor_site_id
INTO l_dummy
FROM po_vendor_sites_all
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_default_pay_site_id
AND org_id = p_org_id
AND nvl(inactive_date, sysdate +1 ) > sysdate
AND pay_site_flag = 'Y';
SELECT count(*)
INTO l_count
FROM ap_income_tax_regions
WHERE region_short_name = p_state
AND sysdate < nvl(inactive_date,sysdate+1);
SELECT organization_id
INTO l_org_id
FROM HR_OPERATING_UNITS
WHERE organization_id = p_org_id
AND name = p_org_name
AND sysdate < nvl(date_to, sysdate + 1);
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INCONSISTENT_ORG_INFO',
g_user_id,
g_login_id,
'Check_Org_Id_Name') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '|| p_org_id
||', P_Org_Name: '||p_org_name
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT organization_id
INTO p_org_id
FROM HR_OPERATING_UNITS
WHERE name = p_org_name
AND sysdate < nvl(date_to, sysdate + 1);
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_ORG_INFO',
g_user_id,
g_login_id,
'Check_Org_Id_Name') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '|| p_org_id
||', P_Org_Name: '||p_org_name
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT name
INTO l_org_name
FROM HR_OPERATING_UNITS
WHERE organization_id = p_org_id
AND sysdate < nvl(date_to, sysdate + 1);
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_ORG_INFO',
g_user_id,
g_login_id,
'Check_Org_Id_Name') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '|| p_org_id
||', P_Org_Name: '||p_org_name
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT count(*)
INTO l_count
FROM po_lookup_codes
WHERE lookup_code = p_pay_on_receipt_summary_code
AND lookup_type = 'ERS INVOICE_SUMMARY_CONSIGNED' -- bug 8429005 'ERS INVOICE_SUMMARY'
AND sysdate < nvl(inactive_date,sysdate+1)
AND (lookup_code = 'PAY_SITE'
OR (p_pay_on_code = 'USE' AND lookup_code = 'CONSUMPTION_ADVICE')
OR (p_pay_on_code = 'RECEIPT' and lookup_code IN ('RECEIPT','PACKING_SLIP'))
);
SELECT hg.GEOGRAPHY_NAME
INTO primary_state
FROM HZ_GEOGRAPHIES hg
WHERE hg.COUNTRY_CODE='US' AND hg.GEOGRAPHY_ID IN
(SELECT hgi.GEOGRAPHY_ID
FROM HZ_GEOGRAPHY_IDENTIFIERS hgi
WHERE hgi.GEOGRAPHY_TYPE ='STATE' AND UPPER(hgi.IDENTIFIER_VALUE) = UPPER(p_state));
SELECT hl.location_id
INTO x_location_id
FROM HZ_Locations hl, HZ_Party_Sites hps,
fnd_languages fl
WHERE hl.language = fl.language_code(+) AND
nvl(upper(hl.country), 'dummy') =
nvl(upper(p_country), 'dummy') AND
nvl(upper(hl.address1), 'dummy') =
nvl(upper(p_address_line1), 'dummy') AND
nvl(upper(hl.address2), 'dummy') =
nvl(upper(p_address_line2), 'dummy') AND
nvl(upper(hl.address3), 'dummy') =
nvl(upper(p_address_line3), 'dummy') AND
nvl(upper(hl.address4), 'dummy') =
nvl(upper(p_address_line4), 'dummy') AND
nvl(upper(hl.city), 'dummy') = nvl(upper(p_city), 'dummy') AND
(nvl(upper(hl.state), 'dummy') = nvl(upper(primary_state), 'dummy') OR nvl(upper(hl.state), 'dummy') = nvl(upper(p_state), 'dummy'))AND --Bug 14493264
--nvl(upper(hl.state), 'dummy') = nvl(upper(p_state), 'dummy') AND
nvl(upper(hl.postal_code), 'dummy') = nvl(upper(p_zip), 'dummy') AND
nvl(upper(hl.province), 'dummy') =
nvl(upper(p_province), 'dummy') AND
nvl(upper(hl.county), 'dummy') = nvl(upper(p_county), 'dummy') AND
nvl(upper(fl.nls_language), 'dummy') =
nvl(upper(p_language), 'dummy') AND
nvl(upper(hl.address_style), 'dummy') =
nvl(upper(p_address_style), 'dummy') AND
hl.location_id = hps.location_id AND
hps.party_site_id = p_party_site_id ;
SELECT hl.location_id, hps.party_site_id
INTO x_location_id, p_party_site_id
FROM HZ_Locations hl,
HZ_Party_Sites hps,
po_vendors pv,
fnd_languages fl
WHERE nvl(upper(hl.country), 'dummy') =
nvl(upper(p_country), 'dummy') AND
nvl(upper(hl.address1), 'dummy') =
nvl(upper(p_address_line1), 'dummy') AND
nvl(upper(hl.address2), 'dummy') =
nvl(upper(p_address_line2), 'dummy') AND
nvl(upper(hl.address3), 'dummy') =
nvl(upper(p_address_line3), 'dummy') AND
nvl(upper(hl.address4), 'dummy') =
nvl(upper(p_address_line4), 'dummy') AND
nvl(upper(hl.city), 'dummy') = nvl(upper(p_city), 'dummy') AND
(nvl(upper(hl.state), 'dummy') = nvl(upper(primary_state), 'dummy') OR nvl(upper(hl.state), 'dummy') = nvl(upper(p_state), 'dummy'))AND --Bug 14493264
-- nvl(upper(hl.state), 'dummy') = nvl(upper(p_state), 'dummy') AND
nvl(upper(hl.postal_code), 'dummy') = nvl(upper(p_zip), 'dummy') AND
nvl(upper(hl.province), 'dummy') =
nvl(upper(p_province), 'dummy') AND
nvl(upper(hl.county), 'dummy') = nvl(upper(p_county), 'dummy') AND
nvl(upper(fl.nls_language), 'dummy') =
nvl(upper(p_language), 'dummy') AND
nvl(upper(hl.address_style), 'dummy') =
nvl(upper(p_address_style), 'dummy') AND
hl.location_id = hps.location_id AND
hps.party_id = pv.party_id AND
pv.vendor_id = p_vendor_id AND
hl.language = fl.language_code(+);
SELECT FIN.rfq_only_site_flag,
FIN.accts_pay_code_combination_id,
FIN.prepay_code_combination_id,
FIN.future_dated_payment_ccid,
fin.vat_country_code,
fin.ship_via_lookup_code
INTO l_rfq_only_site,
l_accts_pay_ccid,
l_prepay_ccid,
l_future_pay_ccid,
l_home_country_code,
p_ship_via_lookup_code
FROM FINANCIALS_SYSTEM_PARAMS_ALL FIN,
HR_OPERATING_UNITS HR
WHERE HR.organization_id = FIN.org_id
AND ( HR.name = p_org_name OR
HR.organization_id = p_org_id);
SELECT nvl(count(primary_pay_site_flag),0)
INTO l_dummy_1
FROM ap_vendor_sites_v
WHERE nvl(primary_pay_site_flag,'N') = 'Y'
AND vendor_id = p_vendor_id
AND nvl(inactive_date, sysdate + 1) > sysdate
AND nvl(vendor_site_id, -99) <> nvl(p_vendor_site_id, -99);
SELECT count(*)
INTO l_dummy_2
FROM AP_SUPPLIER_SITES_INT
WHERE vendor_id = p_vendor_id
AND nvl(inactive_date ,sysdate+1) > sysdate
AND primary_pay_site_flag = 'Y'
AND import_request_id is not null -- Bug 11817579
AND nvl(status,'NEW') <> 'PROCESSED'
AND (org_id = p_org_id OR
operating_unit_name = p_org_name);
SELECT nvl(count(tax_reporting_site_flag),0)
INTO l_dummy_1
FROM ap_vendor_sites_v
WHERE nvl(tax_reporting_site_flag,'N') = 'Y'
AND vendor_id = p_vendor_id
AND nvl(inactive_date, sysdate + 1) > sysdate
AND nvl(vendor_site_id, -99) <> nvl(p_vendor_site_id, -99);
SELECT count(*)
INTO l_dummy_2
FROM AP_SUPPLIER_SITES_INT
WHERE vendor_id = p_vendor_id
AND nvl(inactive_date ,sysdate+1) > sysdate
AND tax_reporting_site_flag = 'Y'
AND (org_id = p_org_id OR
operating_unit_name = p_org_name);
SELECT count(*)
INTO l_count
FROM fnd_territories_vl
WHERE territory_code = p_country_of_origin_code;
SELECT COUNT(vendor_id)
INTO l_vendor_count
FROM po_vendor_sites_all
WHERE selling_company_identifier = p_selling_company_identifier
AND vendor_id <> p_vendor_id;
SELECT tolerance_id
INTO l_default_tolerance
FROM ap_system_parameters;
SELECT services_tolerance_id
INTO l_default_tolerance
FROM ap_system_parameters;
SELECT ASP.tolerance_id
INTO l_default_tolerance
FROM ap_system_parameters_all ASP, HR_OPERATING_UNITS ORG
WHERE ASP.org_id = ORG.organization_id
AND (ORG.organization_id = p_org_id OR
ORG.name = p_org_name);
SELECT ASP.services_tolerance_id
INTO l_default_tolerance
FROM ap_system_parameters_all ASP, HR_OPERATING_UNITS ORG
WHERE ASP.org_id = ORG.organization_id
AND (ORG.organization_id = p_org_id OR
ORG.name = p_org_name);
SELECT tolerance_name
INTO l_tolerance_name
FROM AP_TOLERANCE_TEMPLATES
WHERE tolerance_id = p_tolerance_id;
SELECT tolerance_id
INTO l_tolerance_id
FROM AP_TOLERANCE_TEMPLATES
WHERE tolerance_id = p_tolerance_id
AND tolerance_name = p_tolerance_name;
SELECT tolerance_id
INTO p_tolerance_id
FROM AP_TOLERANCE_TEMPLATES
WHERE tolerance_name = p_tolerance_name;
SELECT FRT.freight_code
INTO l_dummy
FROM org_freight FRT, financials_system_params_all FIN
WHERE FRT.organization_id = FIN.inventory_organization_id
AND FIN.org_id = p_org_id
AND nvl(FRT.disable_date, sysdate +1 ) > sysdate
AND FRT.freight_code = p_ship_via_lookup_code;
SELECT party_id
INTO l_dummy
FROM hz_parties
WHERE party_id = p_party_id;
SELECT count(party_id)
INTO l_count
FROM HZ_PARTY_USG_ASSIGNMENTS HPUA
WHERE HPUA.PARTY_USAGE_CODE in
('SUPPLIER','SUPPLIER_CONTACT', 'ORG_CONTACT')
AND HPUA.PARTY_ID = p_party_id;
SELECT Count(*)
INTO l_dummy
FROM hz_locations hl, hz_party_sites hps
WHERE hl.location_id = p_location_id
AND hl.location_id = hps.location_id
AND hps.party_site_id = nvl(p_party_site_id, hps.party_site_id);
SELECT Count(*)
INTO l_dummy
FROM hz_party_sites hps,
ap_suppliers aps /* Bug 12590128 */
WHERE ( hps.location_id = nvl(p_location_id, hps.location_id)
AND hps.party_site_id = p_party_site_id)
AND aps.party_id = hps.party_id
AND aps.vendor_id = p_vendor_id;
SELECT relationship_id
INTO l_dummy
FROM hz_relationships
WHERE relationship_id = p_relationship_id
AND rownum < 2;
SELECT org_contact_id
INTO l_dummy
FROM hz_org_contacts
WHERE org_contact_id = p_org_contact_id;
SELECT vendor_site_id
INTO l_dummy
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_site_id
AND nvl(inactive_date, sysdate +1 ) > sysdate;
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_VENDOR_SITE_ID',
g_user_id,
g_login_id,
'Check_Vendor_Site_Id') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Vendor_Site_Id: '|| p_vendor_site_id
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT vendor_site_code, vendor_site_id
INTO l_vendor_site_code,p_vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_site_code = p_vendor_site_code
AND vendor_site_id=p_vendor_site_id;
SELECT count(1) into l_match_count
FROM po_vendor_sites_all
WHERE vendor_site_code = p_vendor_site_code
AND vendor_id=p_vendor_id;
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_VENDOR_SITE_CODE',
g_user_id,
g_login_id,
'Check_Org_Id_Name_Site_Code') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '||p_org_id
||', P_Org_Name: '||p_org_name
||', P_Vendor_Site_Code: '||p_vendor_site_code
||', P_Vendor_Site_Id: '|| p_vendor_site_id
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT vendor_site_code, vendor_site_id
INTO l_vendor_site_code,p_vendor_site_id
FROM PO_VENDOR_SITES_ALL
WHERE vendor_site_code = p_vendor_site_code
AND vendor_id = p_vendor_id;
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_VENDOR_SITE_CODE',
g_user_id,
g_login_id,
'Check_Org_Id_Name_Site_Code') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '||p_org_id
||', P_Org_Name: '||p_org_name
||', P_Vendor_Site_Code: '||p_vendor_site_code
||', P_Vendor_Site_Id: '|| p_vendor_site_id
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT vendor_site_code, vendor_site_id
INTO l_vendor_site_code,p_vendor_site_id
FROM PO_VENDOR_SITES_ALL
WHERE org_id = p_org_id
AND vendor_site_code = p_vendor_site_code
AND vendor_site_id=p_vendor_site_id;
SELECT vendor_site_code, vendor_site_id
INTO l_vendor_site_code,p_vendor_site_id
FROM PO_VENDOR_SITES_ALL
WHERE org_id = p_org_id
AND vendor_site_code=p_vendor_site_code
AND vendor_id=p_vendor_id; /* Bug 9844445 */
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_VENDOR_SITE_CODE',
g_user_id,
g_login_id,
'Check_Org_Id_Name_Site_Code') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '||p_org_id
||', P_Org_Name: '||p_org_name
||', P_Vendor_Site_Code: '||p_vendor_site_code
||', P_Vendor_Site_Id: '|| p_vendor_site_id
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
SELECT SITE.vendor_site_code,vendor_site_id
INTO l_vendor_site_code,p_vendor_site_id
FROM PO_VENDOR_SITES_ALL SITE, HR_OPERATING_UNITS ORG
WHERE ORG.name = p_org_name
AND SITE.org_id = ORG.organization_id
AND SITE.vendor_site_code = p_vendor_site_code;
IF (Insert_Rejections(
p_int_table,
p_int_key,
'AP_INVALID_VENDOR_SITE_CODE',
g_user_id,
g_login_id,
'Check_Org_Id_Name_Site_Code') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' P_Org_Id: '||p_org_id
||', P_Org_Name: '||p_org_name
||', P_Vendor_Site_Code: '||p_vendor_site_code
||', P_Vendor_Site_Id: '|| p_vendor_site_id
||', P_Int_Table: '||p_int_table
||', P_Int_Key: '||p_int_key);
select count(party_site_id) into l_match_sitename_cnt
from hz_party_sites hzps,
ap_suppliers aps
where hzps.party_id = aps.party_id
and aps.vendor_id = p_vendor_id
AND hzps.party_site_id = p_party_site_id
and hzps.party_site_name = p_party_site_name;
select hzps.party_site_name, hzps.party_site_id
INTO l_party_site_name,p_party_site_id
from hz_party_sites hzps,
ap_suppliers aps
where hzps.party_id = aps.party_id
and aps.vendor_id = p_vendor_id
AND hzps.party_site_id = p_party_site_id
and hzps.party_site_name = p_party_site_name;
select count(party_site_id) into l_match_sitename_cnt
from hz_party_sites hps,
ap_suppliers aps
where hps.party_id = aps.party_id
and aps.vendor_id = p_vendor_id
and hps.party_site_name = p_party_site_name;
select hps.party_site_name, hps.party_site_id
INTO l_party_site_name,p_party_site_id
from hz_party_sites hps,
ap_suppliers aps
where hps.party_id = aps.party_id
and aps.vendor_id = p_vendor_id
and hps.party_site_name = p_party_site_name;
SELECT hzps.party_site_name, hzps.party_site_id
INTO l_party_site_name,p_party_site_id
FROM hz_party_sites hzps, ap_suppliers aps
WHERE hzps.party_site_name = p_party_site_name
AND hzps.party_site_id = p_party_site_id
AND aps.vendor_id = p_vendor_id;
SELECT hzps.party_site_name, hzps.party_site_id
INTO l_party_site_name,p_party_site_id
FROM hz_party_sites hzps, ap_supplier_sites_all aps
WHERE aps.org_id = p_org_id
AND hzps.party_site_name = p_party_site_name
AND hzps.party_site_id = aps.party_site_id
AND aps.vendor_id = p_vendor_id;
SELECT hzps.party_site_name, hzps.party_site_id
INTO l_party_site_name,p_party_site_id
FROM hz_party_sites hzps, ap_supplier_sites_all aps,
HR_OPERATING_UNITS ORG
WHERE ORG.name = p_org_name
AND aps.org_id = ORG.organization_id
AND hzps.party_site_name = p_party_site_name
AND hzps.party_site_id = aps.party_site_id
AND aps.vendor_id = p_vendor_id;
PROCEDURE update_supplier_JFMIP_checks(p_vendor_rec IN r_vendor_rec_type,
p_calling_prog IN VARCHAR2,
x_valid OUT NOCOPY BOOLEAN
) IS
e_apps_exception EXCEPTION;
FND_MESSAGE.SET_NAME('SQLAP','AP_CCR_NO_UPDATE');
END update_supplier_JFMIP_checks;
PROCEDURE Chk_update_site_ccr_values(p_vendor_site_rec IN r_vendor_site_rec_type,
p_calling_prog IN VARCHAR2,
x_valid OUT NOCOPY BOOLEAN
) IS
e_apps_exception EXCEPTION;
FND_MESSAGE.SET_NAME('SQLAP','AP_CCR_NO_UPDATE');
END Chk_update_site_ccr_values;
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
l_last_update_login := fnd_global.login_id;
SELECT HZ_PARTY_NUMBER_S.Nextval
INTO l_party_rec.party_number
FROM DUAL;
SELECT PROCESS_FOR_APPLICABILITY_FLAG, ALLOW_OFFSET_TAX_FLAG, TAX_CLASSIFICATION_CODE,
PARTY_TAX_PROFILE_ID
INTO l_auto_tax_calc_flag,l_offset_tax_flag, l_tax_classification_code,
L_PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_ID = l_party_id
AND PARTY_TYPE_CODE = 'THIRD_PARTY'
AND ROWNUM = 1;
ZX_PARTY_TAX_PROFILE_PKG.update_row (
P_PARTY_TAX_PROFILE_ID => L_PARTY_TAX_PROFILE_ID,
P_COLLECTING_AUTHORITY_FLAG => null,
P_PROVIDER_TYPE_CODE => null,
P_CREATE_AWT_DISTS_TYPE_CODE => null,
P_CREATE_AWT_INVOICES_TYPE_COD => null,
P_TAX_CLASSIFICATION_CODE => l_tax_classification_code, -- Bug#7506443 zrehman
P_SELF_ASSESS_FLAG => null,
P_ALLOW_OFFSET_TAX_FLAG => l_offset_tax_flag,-- Bug#7371143 zrehman
P_REP_REGISTRATION_NUMBER => l_vendor_rec.vat_registration_num,
P_EFFECTIVE_FROM_USE_LE => null,
P_RECORD_TYPE_CODE => null,
P_REQUEST_ID => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_PARTY_ID => null,
P_PROGRAM_LOGIN_ID => null,
P_PARTY_TYPE_CODE => null,
P_SUPPLIER_FLAG => null,
P_CUSTOMER_FLAG => null,
P_SITE_FLAG => null,
P_PROCESS_FOR_APPLICABILITY_FL => l_auto_tax_calc_flag,-- Bug#7371143 zrehman
P_ROUNDING_LEVEL_CODE => null,
P_ROUNDING_RULE_CODE => null,
P_WITHHOLDING_START_DATE => null,
P_INCLUSIVE_TAX_FLAG => null,
P_ALLOW_AWT_FLAG => null,
P_USE_LE_AS_SUBSCRIBER_FLAG => null,
P_LEGAL_ESTABLISHMENT_FLAG => null,
P_FIRST_PARTY_LE_FLAG => null,
P_REPORTING_AUTHORITY_FLAG => null,
X_RETURN_STATUS => l_return_status,
P_REGISTRATION_TYPE_CODE => null,
P_COUNTRY_CODE => null
);
l_debug_info := 'ZX_PARTY_TAX_PROFILE_PKG.update_row';
ap_vendors_pkg.insert_row(
p_vendor_rec => l_vendor_rec
,p_last_update_date => sysdate
,p_last_updated_by => nvl(l_user_id,-1)
,p_last_update_login => nvl(l_last_update_login,-1)
,p_creation_date => sysdate
,p_created_by => nvl(l_user_id,-1)
,p_request_id => l_request_id
,p_program_application_id => l_program_application_id
,p_program_id => l_program_id
,p_program_update_date => sysdate
,x_rowid => l_row_id
,x_vendor_id => l_vendor_id);
l_debug_info := 'After call to ap_vendors_pkg.insert_row';
PROCEDURE Update_Vendor
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_vendor_rec IN r_vendor_rec_type,
p_vendor_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Vendor';
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
SAVEPOINT Update_Vendor_PUB;
-- Select all the values needed to pass to update_row
-- from PO_VENDORS
SELECT
decode(l_vendor_rec.allow_awt_flag,
ap_null_char, NULL,
nvl(l_vendor_rec.allow_awt_flag, allow_awt_flag))
,decode(l_vendor_rec.allow_substitute_receipts_flag,
ap_null_char, NULL,
nvl(l_vendor_rec.allow_substitute_receipts_flag,
allow_substitute_receipts_flag))
,decode(l_vendor_rec.allow_unordered_receipts_flag,
ap_null_char, NULL,
nvl(l_vendor_rec.allow_unordered_receipts_flag,
allow_unordered_receipts_flag))
,decode(l_vendor_rec.always_take_disc_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.always_take_disc_flag,
always_take_disc_flag))
,decode(l_vendor_rec.attribute_category,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute_category,
attribute_category))
,decode(l_vendor_rec.attribute1,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute1, attribute1))
,decode(l_vendor_rec.attribute10,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute10, attribute10))
,decode(l_vendor_rec.attribute11,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute11, attribute11))
,decode(l_vendor_rec.attribute12,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute12, attribute12))
,decode(l_vendor_rec.attribute13,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute13, attribute13))
,decode(l_vendor_rec.attribute14,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute14, attribute14))
,decode(l_vendor_rec.attribute15,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute15, attribute15))
,decode(l_vendor_rec.attribute2,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute2, attribute2))
,decode(l_vendor_rec.attribute3,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute3, attribute3))
,decode(l_vendor_rec.attribute4,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute4, attribute4))
,decode(l_vendor_rec.attribute5,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute5, attribute5))
,decode(l_vendor_rec.attribute6,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute6, attribute6))
,decode(l_vendor_rec.attribute7,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute7, attribute7))
,decode(l_vendor_rec.attribute8,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute8, attribute8))
,decode(l_vendor_rec.attribute9,
ap_null_char, NULL,
nvl(l_vendor_rec.attribute9, attribute9))
,decode(l_vendor_rec.auto_calculate_interest_flag,
ap_null_char, NULL,
nvl(l_vendor_rec.auto_calculate_interest_flag,
auto_calculate_interest_flag))
,decode(l_vendor_rec.awt_group_id,
ap_null_num,NULL,
nvl(l_vendor_rec.awt_group_id,
awt_group_id))
,decode(l_vendor_rec.bank_charge_bearer,
ap_null_char,NULL,
nvl(l_vendor_rec.bank_charge_bearer,
bank_charge_bearer))
,decode(l_vendor_rec.check_digits,
ap_null_char, NULL,
nvl(l_vendor_rec.check_digits,
check_digits))
,decode(l_vendor_rec.create_debit_memo_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.create_debit_memo_flag,
create_debit_memo_flag))
,decode(l_vendor_rec.customer_num,
ap_null_char,NULL,
nvl(l_vendor_rec.customer_num,
customer_num))
,decode(l_vendor_rec.days_early_receipt_allowed,
ap_null_num,NULL,
nvl(l_vendor_rec.days_early_receipt_allowed,
days_early_receipt_allowed))
,decode(l_vendor_rec.days_late_receipt_allowed,
ap_null_num,NULL,
nvl(l_vendor_rec.days_late_receipt_allowed,
days_late_receipt_allowed))
,decode(l_vendor_rec.employee_id,
ap_null_num,NULL,
nvl(l_vendor_rec.employee_id,
employee_id))
,decode(l_vendor_rec.enabled_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.enabled_flag,
enabled_flag))
,decode(l_vendor_rec.end_date_active,
ap_null_date,NULL,
nvl(l_vendor_rec.end_date_active,
end_date_active))
,decode(l_vendor_rec.enforce_ship_to_location_code,
ap_null_char, NULL,
nvl(l_vendor_rec.enforce_ship_to_location_code,
enforce_ship_to_location_code ))
,decode(l_vendor_rec.exclude_freight_from_discount,
ap_null_char,NULL,
nvl(l_vendor_rec.exclude_freight_from_discount,
exclude_freight_from_discount))
,decode(l_vendor_rec.federal_reportable_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.federal_reportable_flag,
federal_reportable_flag))
,decode(l_vendor_rec.global_attribute_category,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute_category,
global_attribute_category))
,decode(l_vendor_rec.global_attribute1,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute1,
global_attribute1))
,decode(l_vendor_rec.global_attribute2,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute2,
global_attribute2))
,decode(l_vendor_rec.global_attribute3,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute3,
global_attribute3))
,decode(l_vendor_rec.global_attribute4,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute4,
global_attribute4))
,decode(l_vendor_rec.global_attribute5,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute5,
global_attribute5))
,decode(l_vendor_rec.global_attribute6,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute6,
global_attribute6))
,decode(l_vendor_rec.global_attribute7,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute7,
global_attribute7))
,decode(l_vendor_rec.global_attribute8,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute8,
global_attribute8))
,decode(l_vendor_rec.global_attribute9,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute9,
global_attribute9))
,decode(l_vendor_rec.global_attribute10,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute10,
global_attribute10))
,decode(l_vendor_rec.global_attribute11,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute11,
global_attribute11))
,decode(l_vendor_rec.global_attribute12,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute12,
global_attribute12))
,decode(l_vendor_rec.global_attribute13,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute13,
global_attribute13))
,decode(l_vendor_rec.global_attribute14,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute14,
global_attribute14))
,decode(l_vendor_rec.global_attribute15,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute15,
global_attribute15))
,decode(l_vendor_rec.global_attribute16,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute16,
global_attribute16))
,decode(l_vendor_rec.global_attribute17,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute17,
global_attribute17))
,decode(l_vendor_rec.global_attribute18,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute18,
global_attribute18))
,decode(l_vendor_rec.global_attribute19,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute19,
global_attribute19))
,decode(l_vendor_rec.global_attribute20,
ap_null_char,NULL,
nvl(l_vendor_rec.global_attribute20,
global_attribute20))
,decode(l_vendor_rec.hold_all_payments_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.hold_all_payments_flag,
hold_all_payments_flag))
,decode(l_vendor_rec.hold_by,
ap_null_num, NULL,
nvl(l_vendor_rec.hold_by, hold_by))
,decode(l_vendor_rec.hold_date,
ap_null_date,NULL,
nvl(l_vendor_rec.hold_date, hold_date))
,decode(l_vendor_rec.hold_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.hold_flag, hold_flag))
,decode(l_vendor_rec.hold_future_payments_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.hold_future_payments_flag,
hold_future_payments_flag))
,decode(l_vendor_rec.hold_reason,
ap_null_char,NULL,
nvl(l_vendor_rec.hold_reason, hold_reason))
,decode(l_vendor_rec.hold_unmatched_invoices_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.hold_unmatched_invoices_flag,
hold_unmatched_invoices_flag))
,decode(l_vendor_rec.inspection_required_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.inspection_required_flag,
inspection_required_flag ))
,decode(l_vendor_rec.invoice_amount_limit,
ap_null_num, NULL,
nvl(l_vendor_rec.invoice_amount_limit,
invoice_amount_limit))
,decode(l_vendor_rec.invoice_currency_code,
ap_null_char,NULL,
nvl(l_vendor_rec.invoice_currency_code,
invoice_currency_code))
,decode(l_vendor_rec.match_option,
ap_null_char,NULL,
nvl(l_vendor_rec.match_option, match_option))
,decode(l_vendor_rec.min_order_amount,
ap_null_num,NULL,
nvl(l_vendor_rec.min_order_amount,
min_order_amount))
,decode(l_vendor_rec.minority_group_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.minority_group_lookup_code,
minority_group_lookup_code))
,decode(l_vendor_rec.name_control,
ap_null_char,NULL,
nvl(l_vendor_rec.name_control, name_control))
,decode(l_vendor_rec.one_time_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.one_time_flag, one_time_flag ))
,decode(l_vendor_rec.organization_type_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.organization_type_lookup_code,
organization_type_lookup_code))
,decode(l_vendor_rec.parent_vendor_id,
ap_null_num,NULL,
nvl(l_vendor_rec.parent_vendor_id,
parent_vendor_id))
,decode(l_vendor_rec.parent_party_id,
ap_null_num,NULL,
nvl(l_vendor_rec.parent_party_id,
parent_party_id))
,decode(l_vendor_rec.party_id,
ap_null_num,NULL,
nvl(l_vendor_rec.party_id, party_id))
,decode(l_vendor_rec.pay_date_basis_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.pay_date_basis_lookup_code,
pay_date_basis_lookup_code))
,decode(l_vendor_rec.pay_group_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.pay_group_lookup_code,
pay_group_lookup_code))
,decode(l_vendor_rec.payment_currency_code,
ap_null_char,NULL,
nvl(l_vendor_rec.payment_currency_code,
payment_currency_code))
,decode(l_vendor_rec.payment_priority,
ap_null_num,NULL,
nvl(l_vendor_rec.payment_priority,
payment_priority))
,decode(l_vendor_rec.purchasing_hold_reason,
ap_null_char,NULL,
nvl(l_vendor_rec.purchasing_hold_reason,
purchasing_hold_reason))
,decode(l_vendor_rec.qty_rcv_exception_code,
ap_null_char,NULL,
nvl(l_vendor_rec.qty_rcv_exception_code,
qty_rcv_exception_code))
,decode(l_vendor_rec.qty_rcv_tolerance,
ap_null_num,NULL,
nvl(l_vendor_rec.qty_rcv_tolerance,
qty_rcv_tolerance))
,decode(l_vendor_rec.receipt_days_exception_code,
ap_null_char,NULL,
nvl(l_vendor_rec.receipt_days_exception_code,
receipt_days_exception_code))
,decode(l_vendor_rec.receipt_required_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.receipt_required_flag,
receipt_required_flag))
,decode(l_vendor_rec.receiving_routing_id,
ap_null_num,NULL,
nvl(l_vendor_rec.receiving_routing_id,
receiving_routing_id))
,decode(l_vendor_rec.segment1,
ap_null_char,NULL,
nvl(l_vendor_rec.segment1, segment1 ))
,decode(l_vendor_rec.segment2,
ap_null_char,NULL,
nvl(l_vendor_rec.segment2, segment2 ))
,decode(l_vendor_rec.segment3,
ap_null_char,NULL,
nvl(l_vendor_rec.segment3, segment3 ))
,decode(l_vendor_rec.segment4,
ap_null_char,NULL,
nvl(l_vendor_rec.segment4, segment4 ))
,decode(l_vendor_rec.segment5,
ap_null_char,NULL,
nvl(l_vendor_rec.segment5, segment5 ))
,decode(l_vendor_rec.set_of_books_id,
ap_null_num,NULL,
nvl(l_vendor_rec.set_of_books_id,
set_of_books_id))
,decode(l_vendor_rec.small_business_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.small_business_flag,
small_business_flag ))
,decode(l_vendor_rec.start_date_active,
ap_null_date,NULL,
nvl(l_vendor_rec.start_date_active,
start_date_active))
,decode(l_vendor_rec.state_reportable_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.state_reportable_flag,
state_reportable_flag))
,decode(l_vendor_rec.summary_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.summary_flag, summary_flag))
,decode(l_vendor_rec.tax_reporting_name,
ap_null_char,NULL,
nvl(l_vendor_rec.tax_reporting_name,
tax_reporting_name))
,decode(l_vendor_rec.tax_verification_date,
ap_null_date,NULL,
nvl(l_vendor_rec.tax_verification_date,
tax_verification_date))
,decode(l_vendor_rec.terms_date_basis,
ap_null_char,NULL,
nvl(l_vendor_rec.terms_date_basis,
terms_date_basis))
,decode(l_vendor_rec.terms_id,
ap_null_num,NULL,
nvl(l_vendor_rec.terms_id, terms_id ))
--bug6050423 starts.system inserts taxpayer id
--of individual contractors into ap_suppliers
,decode(l_vendor_rec.jgzz_fiscal_code,
ap_null_char,NULL,
nvl(l_vendor_rec.jgzz_fiscal_code,nvl(individual_1099,num_1099)))
--bug6050423 ends
,decode(l_vendor_rec.type_1099,
ap_null_char,NULL,
nvl(l_vendor_rec.type_1099, type_1099))
,decode(l_vendor_rec.validation_number,
ap_null_num,NULL,
nvl(l_vendor_rec.validation_number,
validation_number))
,decode(l_vendor_rec.vendor_type_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.vendor_type_lookup_code,
vendor_type_lookup_code))
,decode(l_vendor_rec.withholding_start_date,
ap_null_date,NULL,
nvl(l_vendor_rec.withholding_start_date,
withholding_start_date))
,decode(l_vendor_rec.withholding_status_lookup_code,
ap_null_char,NULL,
nvl(l_vendor_rec.withholding_status_lookup_code,
withholding_status_lookup_code))
,decode(l_vendor_rec.women_owned_flag,
ap_null_char,NULL,
nvl(l_vendor_rec.women_owned_flag,women_owned_flag))
-- bug7561758
,decode(l_vendor_rec.pay_awt_group_id,
ap_null_num,NULL,
nvl(l_vendor_rec.pay_awt_group_id,
pay_awt_group_id))
,decode(l_vendor_rec.ni_number,
ap_null_char,NULL,l_vendor_rec.ni_number)/*Bug9716861 */
,rowid
INTO
l_vendor_rec.allow_awt_flag
,l_vendor_rec.allow_substitute_receipts_flag
,l_vendor_rec.allow_unordered_receipts_flag
,l_vendor_rec.always_take_disc_flag
,l_vendor_rec.attribute_category
,l_vendor_rec.attribute1
,l_vendor_rec.attribute10
,l_vendor_rec.attribute11
,l_vendor_rec.attribute12
,l_vendor_rec.attribute13
,l_vendor_rec.attribute14
,l_vendor_rec.attribute15
,l_vendor_rec.attribute2
,l_vendor_rec.attribute3
,l_vendor_rec.attribute4
,l_vendor_rec.attribute5
,l_vendor_rec.attribute6
,l_vendor_rec.attribute7
,l_vendor_rec.attribute8
,l_vendor_rec.attribute9
,l_vendor_rec.auto_calculate_interest_flag
,l_vendor_rec.awt_group_id
,l_vendor_rec.bank_charge_bearer
,l_vendor_rec.check_digits
,l_vendor_rec.create_debit_memo_flag
,l_vendor_rec.customer_num
,l_vendor_rec.days_early_receipt_allowed
,l_vendor_rec.days_late_receipt_allowed
,l_vendor_rec.employee_id
,l_vendor_rec.enabled_flag
,l_vendor_rec.end_date_active
,l_vendor_rec.enforce_ship_to_location_code
,l_vendor_rec.exclude_freight_from_discount
,l_vendor_rec.federal_reportable_flag
,l_vendor_rec.global_attribute_category
,l_vendor_rec.global_attribute1
,l_vendor_rec.global_attribute2
,l_vendor_rec.global_attribute3
,l_vendor_rec.global_attribute4
,l_vendor_rec.global_attribute5
,l_vendor_rec.global_attribute6
,l_vendor_rec.global_attribute7
,l_vendor_rec.global_attribute8
,l_vendor_rec.global_attribute9
,l_vendor_rec.global_attribute10
,l_vendor_rec.global_attribute11
,l_vendor_rec.global_attribute12
,l_vendor_rec.global_attribute13
,l_vendor_rec.global_attribute14
,l_vendor_rec.global_attribute15
,l_vendor_rec.global_attribute16
,l_vendor_rec.global_attribute17
,l_vendor_rec.global_attribute18
,l_vendor_rec.global_attribute19
,l_vendor_rec.global_attribute20
,l_vendor_rec.hold_all_payments_flag
,l_vendor_rec.hold_by
,l_vendor_rec.hold_date
,l_vendor_rec.hold_flag
,l_vendor_rec.hold_future_payments_flag
,l_vendor_rec.hold_reason
,l_vendor_rec.hold_unmatched_invoices_flag
,l_vendor_rec.inspection_required_flag
,l_vendor_rec.invoice_amount_limit
,l_vendor_rec.invoice_currency_code
,l_vendor_rec.match_option
,l_vendor_rec.min_order_amount
,l_vendor_rec.minority_group_lookup_code
,l_vendor_rec.name_control
,l_vendor_rec.one_time_flag
,l_vendor_rec.organization_type_lookup_code
,l_vendor_rec.parent_vendor_id
,l_vendor_rec.parent_party_id
,l_vendor_rec.party_id
,l_vendor_rec.pay_date_basis_lookup_code
,l_vendor_rec.pay_group_lookup_code
,l_vendor_rec.payment_currency_code
,l_vendor_rec.payment_priority
,l_vendor_rec.purchasing_hold_reason
,l_vendor_rec.qty_rcv_exception_code
,l_vendor_rec.qty_rcv_tolerance
,l_vendor_rec.receipt_days_exception_code
,l_vendor_rec.receipt_required_flag
,l_vendor_rec.receiving_routing_id
,l_vendor_rec.segment1
,l_vendor_rec.segment2
,l_vendor_rec.segment3
,l_vendor_rec.segment4
,l_vendor_rec.segment5
,l_vendor_rec.set_of_books_id
,l_vendor_rec.small_business_flag
,l_vendor_rec.start_date_active
,l_vendor_rec.state_reportable_flag
,l_vendor_rec.summary_flag
,l_vendor_rec.tax_reporting_name
,l_vendor_rec.tax_verification_date
,l_vendor_rec.terms_date_basis
,l_vendor_rec.terms_id
,l_vendor_rec.jgzz_fiscal_code --bug6050423
,l_vendor_rec.type_1099
,l_vendor_rec.validation_number
,l_vendor_rec.vendor_type_lookup_code
,l_vendor_rec.withholding_start_date
,l_vendor_rec.withholding_status_lookup_code
,l_vendor_rec.women_owned_flag
,l_vendor_rec.pay_awt_group_id -- bug7561758
,l_vendor_rec.ni_number /*Bug9716861 */
,l_rowid
FROM po_vendors
WHERE vendor_id = p_vendor_id;
ap_vendors_pkg.update_row(
p_vendor_rec => l_vendor_rec,
p_last_update_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_login => l_last_update_login,
p_request_id => l_request_id ,
p_program_application_id => l_program_application_id,
p_program_id => l_program_id,
p_program_update_date => sysdate,
p_rowid => l_rowid,
p_vendor_id => p_vendor_id);
ROLLBACK TO Update_Vendor_PUB;
ROLLBACK TO Update_Vendor_PUB;
ROLLBACK TO Update_Vendor_PUB;
END Update_Vendor;
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_INSP_REQ_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Inspection_Required_Flag: '||p_vendor_rec.inspection_required_flag);
SELECT COUNT(*) INTO l_status FROM DUAL WHERE EXISTS (
SELECT 'Y'
FROM zx_input_classifications_v
WHERE lookup_type in ('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS')
AND org_id = -99
AND enabled_flag = 'Y'
AND LOOKUP_CODE = p_vendor_rec.VAT_CODE );
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_VAT_CODE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Vat_Code: '
||p_vendor_rec.vat_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_REC_REQ_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Receipt_Required_Flag: '||p_vendor_rec.receipt_required_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PAYMENT_PRIORITY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Payment_Priority: '||p_vendor_rec.payment_priority);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_1099_TYPE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Type_1099: '||p_vendor_rec.type_1099
||' ,Federal_Reportable_Flag: '||p_vendor_rec.federal_reportable_flag);
SELECT FIN.vat_country_code
INTO l_default_country_code
FROM FINANCIALS_SYSTEM_PARAMS_ALL FIN,
AP_SYSTEM_PARAMETERS_ALL ASP
WHERE ASP.set_of_books_id=p_vendor_rec.set_of_books_id
AND FIN.org_id = ASP.org_id;
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_DEFAULT_COUNTRY_CODE_NULL',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Default Country is Null');
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_TAXPAYER_ID',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,NUM_1099: '||p_vendor_rec.jgzz_fiscal_code
||' ,Federal_Reportable_Flag: '||p_vendor_rec.federal_reportable_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_EMPLOYEE_ID',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Vendor_Type_Lookup_Code: '||p_vendor_rec.vendor_type_lookup_code
||' ,Employee_Id: NULL');
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_EMPLOYEE_ID',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Employee_Id: '||p_vendor_rec.employee_id);
SELECT PPF.PARTY_ID
INTO P_VENDOR_REC.PARTY_ID
FROM PER_PEOPLE_F PPF
WHERE PPF.PERSON_ID = P_VENDOR_REC.EMPLOYEE_ID
AND TRUNC(SYSDATE) BETWEEN
TRUNC(ppf.effective_start_date) AND
TRUNC(ppf.effective_end_date);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_EMPLOYEE_ID',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Vendor_Type_Lookup_Code: '||p_vendor_rec.vendor_type_lookup_code
||' ,Not able to get Party Id From Employee Id.');
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_INSPEC_RECEIPT',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Inspection_Required_Flag: '||p_vendor_rec.inspection_required_flag
||' ,Receipt_Required_Flag: '||p_vendor_rec.receipt_required_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_NAME_CONTROL',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Name_Control: '||p_vendor_rec.name_control);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_SHIP_INVENTORY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Ship_Via_Lookup_Code: '||p_vendor_rec.ship_via_lookup_code
||' ,Inventory_Organization_Id: '||p_vendor_rec.inventory_organization_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_SOB',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Set_Of_Books_Id: '||p_vendor_rec.set_of_books_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
--'DUPLICATE SEGMENT1 INT',
'AP_INVALID_SEGMENT1_INT',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Segment1: '||p_vendor_rec.segment1);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_VEN_DUPLICATE_VEN_NUM',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Segment1: '||p_vendor_rec.segment1);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_RCV_ROUTING',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Receiving_Routing_Id: '||p_vendor_rec.receiving_routing_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_VENDOR_TYPE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Vendor_Type_Lookup_Code: '||p_vendor_rec.vendor_type_lookup_code
||' ,Employee_Id: '||p_vendor_rec.employee_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_INV_CURRENCY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Invoice_Currency_Code: '||p_vendor_rec.invoice_currency_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PAY_CURRENCY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Invoice_Currency_Code: '||p_vendor_rec.invoice_currency_code
||' ,Payment_Currency_Code: '||p_vendor_rec.payment_currency_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_TYPE_1099',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Type_1099: '||p_vendor_rec.type_1099);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCORRECT_GTAS_INFO',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id);
SELECT Global_Attribute4,
Global_Attribute5,
Global_Attribute_Category /*Bug#15977829*/
INTO p_vendor_rec.Global_Attribute4,
p_vendor_rec.Global_Attribute5,
p_vendor_rec.Global_Attribute_Category
FROM AP_SUPPLIERS_INT
WHERE vendor_interface_id =
p_vendor_rec.vendor_interface_id;
--update validations
/* open issue 1 -- no longer need to check for name duplicates
------------------------------------------------------------------------
l_debug_info := 'Call to Validate vendor name';
Chk_Dup_Vendor_Name_update(p_vendor_rec.vendor_name,
p_vendor_id,
x_valid);
l_debug_info := 'Call for prohibiting update of CCR vendor';
update_supplier_JFMIP_checks(p_vendor_rec,
p_calling_prog,
x_valid);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_CCR_NO_UPDATE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||'Cannot Update CCR vendor, Vendor_id'||p_vendor_rec.vendor_id);
FND_MESSAGE.SET_NAME('SQLAP','AP_CANT_UPDATE_CCR_VENDOR');
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_VEN_DUPLICATE_NAME',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Vendor_name: '||p_vendor_rec.vendor_name);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
--'AP_INVALID_PAYEE',
'AP_INVALID_PAYEE_INFO',/*bug 7572325*/
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:' ||
p_vendor_rec.vendor_interface_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PARTY_ID',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Party_Id: '||p_vendor_rec.party_id);
--insert validations
------------------------------------------------------------------------
l_debug_info := 'Call to Validate vendor number';
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_NULL_VENDOR_NUMBER', --bug 5568861
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '||p_vendor_rec.vendor_interface_id
||' ,Segment1: '||p_vendor_rec.segment1);
--addl insert validations
---------------------------------------------------------------
l_debug_info := 'Call to Validate one_time_flag';
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_ONE_TIME_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,One_Time_Flag: '||p_vendor_rec.one_time_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_SUMMARY_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Summary_Flag: '||p_vendor_rec.summary_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_ENABLED_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Enabled_Flag: '||p_vendor_rec.enabled_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_VENDOR_TYPE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Vendor_Type_Lookup_Code: '
||p_vendor_rec.vendor_type_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PAY_DATE_BASIS',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Pay_Date_Basis_Lookup_Code: '
|| p_vendor_rec.pay_date_basis_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PAY_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Pay_Group_Lookup_Code:'
||p_vendor_rec.pay_group_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_ORG_TYPE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Organization_Type_Lookup_Code:'
||p_vendor_rec.organization_type_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_MINORITY_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Minority_Group_Lookup_Code:'
||p_vendor_rec.minority_group_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_TERMS_DATE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Terms_Date_Basis:'
||p_vendor_rec.terms_date_basis);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_QTY_RCV_OPTION',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Qty_Rcv_Execption_Code:'
||p_vendor_rec.Qty_Rcv_Exception_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_SHIP_RCV_OPTION',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Enforce_Ship_To_Location_Code:'
||p_vendor_rec.enforce_ship_to_location_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_REC_RCV_OPTION',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Receipt_Days_Exception_Code:'
||p_vendor_rec.receipt_days_exception_code);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_DEBIT_MEMO',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Create_Debit_Memo_Flag:'
||p_vendor_rec.create_debit_memo_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_FED_REPORTABLE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Federal_Reportable_Flag:'
||p_vendor_rec.federal_reportable_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_STATE_REPORTABLE',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,State_Reportable_Flag:'
||p_vendor_rec.state_reportable_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_CALC_INT',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' , Auto_Calculate_Interest_Flag'
||p_vendor_rec.auto_calculate_interest_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_EXC_FR_DISC',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Exclude_Freight_From_Discount:'
||p_vendor_rec.exclude_freight_from_discount);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_HOLD_UNMAT_INV',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Hold_Unmatched_Invoices_Flag:'
||p_vendor_rec.hold_unmatched_invoices_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_UNORD_RCV',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Allow_Unordered_Receipts_Flag:'
||p_vendor_rec.allow_unordered_receipts_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_SUBS_RCV',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Allow_Substitute_Receipts_Flag:'
||p_vendor_rec.allow_substitute_receipts_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_HOLD',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Hold_Flag:'
||p_vendor_rec.hold_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_SMALL_BUSINESS',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Small_Business_Flag:'
||p_vendor_rec.small_business_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_WOMEN_OWNED',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Women_Owned_Flag:'
||p_vendor_rec.women_owned_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_HOLD_FUT_PAY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Hold_Future_Payments_Flag:'
||p_vendor_rec.hold_future_payments_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_HOLD_ALL_PAY',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Hold_All_Payments_Flag:'
||p_vendor_rec.hold_all_payments_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_TAKE_DISC',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Always_Take_Disc_Flag:'
||p_vendor_rec.always_take_disc_flag);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_NULL_VENDOR_NUMBER', --bug5568861
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_MATCH_OPTION',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Match_Option:'
||p_vendor_rec.match_option);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_AWT_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Awt_Group_Id: '||p_vendor_rec.awt_group_id
||' ,Awt_Group_Name:'
||p_vendor_rec.awt_group_name);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_AWT_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id:'
||p_vendor_rec.vendor_interface_id
||' ,Awt_Group_Id: '||p_vendor_rec.pay_awt_group_id
||' ,Awt_Group_Name:'
||p_vendor_rec.pay_awt_group_name);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INCONSISTENT_TERM',
g_user_id,
g_login_id,
'Validate_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Interface_Id: '
||p_vendor_rec.vendor_interface_id
||' ,Terms_Id: '||p_vendor_rec.terms_id
||' ,Terms_Name: '||p_vendor_rec.terms_name);
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
l_last_updated_by NUMBER;
SELECT Iep.Exclusive_Payment_Flag,
/* Bug9955876 */
/* NVL(iep.exclusive_payment_flag,'N') exclusive_payment_flag, -- B 8900634/
8889211 */
iep.Bank_instruction1_code, -- Bug 8769088 start
iep.Bank_instruction2_code,
iep.Delivery_channel_code,
iep.bank_instruction_details,
iep.settlement_priority,
iep.payment_text_message1,
iep.payment_text_message2,
iep.payment_text_message3,
iep.bank_charge_bearer,
iep.payment_reason_code,
iep.payment_reason_comments,
iep.payment_format_code,
iep.remit_advice_delivery_method, -- separate remittance advice delivery
iep.remit_advice_email, -- separate remittance advice delivery
iep.remit_advice_fax, -- separate remittance advice delivery
-- Bug 8769088 end
iep.ext_payee_id
FROM iBy_External_Payees_All iep
WHERE iep.Payee_Party_Id = p_prty_id
AND Party_Site_Id IS NULL
AND Supplier_Site_Id IS NULL;
SELECT pmtmthdAssignmentseo.Payment_Method_Code,
pmtmthdAssignmentseo.InActive_Date
FROM iBy_Payment_Methods_vl pmthds,
iBy_ext_Party_pmt_mthds pmtmthdAssignmentseo
WHERE pmthds.Payment_Method_Code = pmtmthdAssignmentseo.Payment_Method_Code (+)
AND pmtmthdAssignmentseo.Payment_Flow = 'DISBURSEMENTS'
AND Nvl(pmthds.InActive_Date,Trunc(SYSDATE + 1)) > Trunc(SYSDATE)
AND Nvl(pmtmthdAssignmentseo.InActive_Date,Trunc(SYSDATE + 1)) > Trunc(SYSDATE) /* Bug9763027 */
AND pmtmthdAssignmentseo.Payment_Function = 'PAYABLES_DISB'
AND pmtmthdAssignmentseo.ext_pmt_Party_Id = p_ext_payee_id
AND pmtmthdAssignmentseo.primary_flag = 'Y';
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_ORG_INFO_NULL', --bug 5568861
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Site_Code: '||p_vendor_site_rec.vendor_site_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_ORG_INFO_NULL', --bug 5568861
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Site_Code: '||p_vendor_site_rec.vendor_site_code);
SELECT nvl(multi_org_flag,'N')
INTO l_multi_org_flag
FROM FND_PRODUCT_GROUPS;
select employee_id
into l_employee_id
from ap_suppliers
where vendor_id = l_vendor_site_rec.vendor_id;
SELECT --8892266 pv.terms_date_basis -- Bug#7506443 --bug 8200984 (changed to aps)--Bug8280106
--8892266, pv.pay_group_lookup_code
pv.payment_priority
-- , asp.always_take_disc_flag .. B# 8260603
, pv.always_take_disc_flag -- B# 8260603
, pv.hold_all_payments_flag
, pv.hold_future_payments_flag
, pv.hold_reason
--Bug6317600 Commenting awt_group_id. AWT should be defaulted from Payables options and not Supplier
--, pv.awt_group_id
, asp.bank_charge_bearer
, pv.match_option -- bug 8200984 (changed to aps)-Bug 8280106(changed to default from Supplier only)
--8892266 , pv.pay_date_basis_lookup_code -- bug 8200984 (changed to aps)-Bug 8280106(changed to default from Supplier only)
, pv.invoice_amount_limit
--8892266 , NVL(pv.invoice_currency_code,asp.invoice_currency_code) --bug:7282105
--8892266 , NVL(pv.payment_currency_code,asp.payment_currency_code) --bug:7282105
--8892266 , pv.terms_id -- Bug#7506443--8200984 (changed to aps)-Bug 8280106(changed to default from Supplier only)
, pv.federal_reportable_flag
, pv.organization_type_lookup_code
, asp.set_of_books_id
--Bug 7651872, asp.hold_unmatched_invoices_flag
, decode(pv.vendor_type_lookup_code
,'EMPLOYEE'
,asp.hold_unmatched_invoices_flag
,pv.hold_unmatched_invoices_flag) /* Added for bug#9193468 */
/* ,l_hold_unmatched_invoices_flag) -- Bug 8614887 Commented for bug#9193468 */
, pv.exclude_freight_from_discount
, pv.party_id
, pv.vendor_type_lookup_code
, nvl(pv.allow_awt_flag, 'N')
, pv.CREATE_DEBIT_MEMO_FLAG --Bug8373166
INTO
--8892266 l_sup_terms_date_basis
--8892266 , l_sup_pay_group_lookup_code
l_payment_priority
, l_sup_always_take_disc_flag
, l_hold_all_payments_flag
, l_hold_future_payments_flag
, l_hold_reason
--Bug6317600
--, l_awt_group_id
, l_sup_bank_charge_bearer
, l_match_option
--8892266 , l_sup_pay_date_basis_lk_code
, l_invoice_amount_limit
--8892266 , l_sup_invoice_currency_code
--8892266 , l_sup_payment_currency_code
--8892266 , l_sup_terms_id
, l_federal_reportable_flag
, l_org_type_lookup_code
, l_set_of_books_id
--Bug 7651872, l_hold_unmatched_invoices_flag
, l_hold_unmatched_invoices_flag --Bug 8614887
, l_exclude_freight_from_disc
, l_party_id
, l_vendor_type_lookup_code
, l_sup_awt_flag
,l_create_debit_memo_flag --Bug8373166
FROM po_vendors pv,
ap_system_parameters_all asp,
ap_product_setup aps -- Bug 8200984
WHERE pv.vendor_id = p_vendor_site_rec.vendor_id
AND asp.org_id=l_def_org_id;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT NVL(asp.invoice_currency_code, pv.invoice_currency_code) invoice_currency_code,
NVL(asp.payment_currency_code, pv.payment_currency_code) payment_currency_code,
NVL(asp.vendor_pay_group_lookup_code, pv.pay_group_lookup_code) pay_group_lookup_code,
NVL(asp.terms_date_basis, pv.terms_date_basis) terms_date_basis,
NVL(asp.pay_date_basis_lookup_code, pv.pay_date_basis_lookup_code) pay_date_basis_lookup_code,
NVL(asp.terms_id, pv.terms_id) terms_id
INTO l_sup_invoice_currency_code,
l_sup_payment_currency_code,
l_sup_pay_group_lookup_code,
l_sup_terms_date_basis,
l_sup_pay_date_basis_lk_code,
l_sup_terms_id
FROM po_vendors pv,
ap_system_parameters_all asp
WHERE pv.vendor_id = p_vendor_site_rec.vendor_id
AND asp.org_id=l_def_org_id;
SELECT language_code
INTO l_location_rec.language
FROM fnd_languages
WHERE nls_language = l_vendor_site_rec.language;
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_TCA_ERROR',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
SELECT HZ_PARTY_SITE_NUMBER_S.Nextval
INTO l_party_site_rec.party_site_number
FROM DUAL;
SELECT PROCESS_FOR_APPLICABILITY_FLAG, ALLOW_OFFSET_TAX_FLAG
, TAX_CLASSIFICATION_CODE -- Bug#7506443
,ROUNDING_LEVEL_CODE ,ROUNDING_RULE_CODE ,INCLUSIVE_TAX_FLAG /* B 9530837 */
INTO l_auto_tax_calc_flag,l_offset_tax_flag,
l_tax_classification_code -- Bug#7506443
,L_ROUNDING_LEVEL_CODE ,L_ROUNDING_RULE_CODE ,L_INCLUSIVE_TAX_FLAG /* B 9530837 */
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_ID = l_party_id
AND PARTY_TYPE_CODE = 'THIRD_PARTY'
AND ROWNUM = 1;
SELECT Zic.Lookup_Code, Zic.End_Date_Active, zic.start_date_active
INTO L_Zic_tax_classification_code, Zic_end_date_Active, Zic_start_date_active
FROM ZX_INPUT_CLASSIFICATIONS_V Zic
WHERE Zic.Lookup_Code = l_tax_classification_code
AND ZIC.enabled_flag ='Y'
AND ZIC.lookup_type='ZX_INPUT_CLASSIFICATIONS'
AND Zic.Org_Id= L_Def_Org_Id;
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_VAT_CODE',
g_user_id,
g_login_id,
'AP_VENDOR_PUB_PKG.Create_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vat_Code: '
||p_vendor_site_rec.vat_code);-- END_DATE_ACTIVE, INVALID VAT CODE
SELECT PARTY_TAX_PROFILE_ID INTO L_PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_ID = l_party_site_id
AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND ROWNUM = 1;
ZX_PARTY_TAX_PROFILE_PKG.update_row (
P_PARTY_TAX_PROFILE_ID => L_PARTY_TAX_PROFILE_ID,
P_COLLECTING_AUTHORITY_FLAG => null,
P_PROVIDER_TYPE_CODE => null,
P_CREATE_AWT_DISTS_TYPE_CODE => null,
P_CREATE_AWT_INVOICES_TYPE_COD => null,
P_TAX_CLASSIFICATION_CODE => l_tax_classification_code, -- Bug#7506443 zrehman
P_SELF_ASSESS_FLAG => null,
P_ALLOW_OFFSET_TAX_FLAG => l_offset_tax_flag,-- Bug#7371143 zrehman
P_REP_REGISTRATION_NUMBER => l_vendor_site_rec.vat_registration_num,
P_EFFECTIVE_FROM_USE_LE => null,
P_RECORD_TYPE_CODE => null,
P_REQUEST_ID => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_PARTY_ID => null,
P_PROGRAM_LOGIN_ID => null,
P_PARTY_TYPE_CODE => null,
P_SUPPLIER_FLAG => null,
P_CUSTOMER_FLAG => null,
P_SITE_FLAG => null,
P_PROCESS_FOR_APPLICABILITY_FL => l_auto_tax_calc_flag,-- Bug#7371143 zrehman
/*P_ROUNDING_LEVEL_CODE => null, B 9530837 */
P_ROUNDING_LEVEL_CODE => L_ROUNDING_LEVEL_CODE , /* B 9530837 */
/*P_ROUNDING_RULE_CODE => null, B 9530837 */
P_ROUNDING_RULE_CODE => L_ROUNDING_RULE_CODE , /* B 9530837 */
P_WITHHOLDING_START_DATE => null,
/*P_INCLUSIVE_TAX_FLAG => null, B 9530837 */
P_INCLUSIVE_TAX_FLAG => L_INCLUSIVE_TAX_FLAG , /* B 9530837 */
P_ALLOW_AWT_FLAG => null,
P_USE_LE_AS_SUBSCRIBER_FLAG => null,
P_LEGAL_ESTABLISHMENT_FLAG => null,
P_FIRST_PARTY_LE_FLAG => null,
P_REPORTING_AUTHORITY_FLAG => null,
X_RETURN_STATUS => l_return_status,
P_REGISTRATION_TYPE_CODE => null,
P_COUNTRY_CODE => null
);
l_debug_info := 'ZX_PARTY_TAX_PROFILE_PKG.update_row';
ap_vendor_sites_pkg.insert_row(
p_vendor_site_rec => l_vendor_site_rec,
p_last_update_date => sysdate,
p_last_updated_by => nvl(l_user_id,-1),
p_last_update_login => nvl(l_last_update_login, -1),
p_creation_date => sysdate,
p_created_by => nvl(l_user_id, -1) ,
p_request_id => l_request_id ,
p_program_application_id => l_program_application_id,
p_program_id => l_program_id,
p_program_update_date => sysdate,
p_AP_Tax_Rounding_Rule => SUBSTR(L_ROUNDING_RULE_CODE,1,1), /* 9530837 */
p_Amount_Includes_Tax_Flag => L_INCLUSIVE_TAX_FLAG, /* 9530837 */
x_rowid => l_rowid,
x_vendor_site_id => l_vendor_site_id);
l_debug_info := 'After call to ap_vendor_sites_pkg.insert_row';
PROCEDURE Update_Vendor_Site
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER :=
FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_vendor_site_rec IN r_vendor_site_rec_type,
p_vendor_site_id IN NUMBER,
p_calling_prog IN VARCHAR2 DEFAULT 'NOT ISETUP'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Vendor_Site';
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
SAVEPOINT Update_Vendor_Site_PUB;
SELECT org_id
INTO l_org_id
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_vendor_site_id;
SELECT
decode(l_vendor_site_rec.AREA_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.AREA_CODE, AREA_CODE))
,decode(l_vendor_site_rec.PHONE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PHONE, PHONE))
,decode(l_vendor_site_rec.CUSTOMER_NUM,
ap_null_char,NULL,
nvl(l_vendor_site_rec.CUSTOMER_NUM, CUSTOMER_NUM))
,decode(l_vendor_site_rec.SHIP_TO_LOCATION_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.SHIP_TO_LOCATION_ID, SHIP_TO_LOCATION_ID))
,decode(l_vendor_site_rec.BILL_TO_LOCATION_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.BILL_TO_LOCATION_ID, BILL_TO_LOCATION_ID))
,decode(l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE, SHIP_VIA_LOOKUP_CODE))
,decode(l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE))
,decode(l_vendor_site_rec.FOB_LOOKUP_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.FOB_LOOKUP_CODE,
FOB_LOOKUP_CODE))
,decode(l_vendor_site_rec.INACTIVE_DATE,
ap_null_date,NULL,
nvl(l_vendor_site_rec.INACTIVE_DATE,
INACTIVE_DATE))
,decode(l_vendor_site_rec.FAX,
ap_null_char,NULL,
nvl(l_vendor_site_rec.FAX, FAX))
,decode(l_vendor_site_rec.FAX_AREA_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.FAX_AREA_CODE,
FAX_AREA_CODE))
,decode(l_vendor_site_rec.TELEX,
ap_null_char,NULL,
nvl(l_vendor_site_rec.TELEX, TELEX))
,decode(l_vendor_site_rec.TERMS_DATE_BASIS,
ap_null_char,NULL,
nvl(l_vendor_site_rec.TERMS_DATE_BASIS,
TERMS_DATE_BASIS))
,decode(l_vendor_site_rec.DISTRIBUTION_SET_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.DISTRIBUTION_SET_ID, DISTRIBUTION_SET_ID))
,decode(l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID,
ACCTS_PAY_CODE_COMBINATION_ID))
,decode(l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID))
,decode(l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE, PAY_GROUP_LOOKUP_CODE))
,decode(l_vendor_site_rec.PAYMENT_PRIORITY,
ap_null_num,NULL,
nvl(l_vendor_site_rec.PAYMENT_PRIORITY, PAYMENT_PRIORITY))
,decode(l_vendor_site_rec.TERMS_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.TERMS_ID, TERMS_ID))
,decode(l_vendor_site_rec.INVOICE_AMOUNT_LIMIT,
ap_null_num,NULL,
nvl(l_vendor_site_rec.INVOICE_AMOUNT_LIMIT, INVOICE_AMOUNT_LIMIT))
,decode(l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE, PAY_DATE_BASIS_LOOKUP_CODE))
,decode(l_vendor_site_rec.ALWAYS_TAKE_DISC_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ALWAYS_TAKE_DISC_FLAG, ALWAYS_TAKE_DISC_FLAG))
,decode(l_vendor_site_rec.INVOICE_CURRENCY_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.INVOICE_CURRENCY_CODE, INVOICE_CURRENCY_CODE))
,decode(l_vendor_site_rec.PAYMENT_CURRENCY_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAYMENT_CURRENCY_CODE, PAYMENT_CURRENCY_CODE))
,decode(l_vendor_site_rec.VENDOR_SITE_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.VENDOR_SITE_ID, VENDOR_SITE_ID))
,decode(l_vendor_site_rec.VENDOR_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.VENDOR_ID, VENDOR_ID))
,decode(l_vendor_site_rec.VENDOR_SITE_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.VENDOR_SITE_CODE, VENDOR_SITE_CODE))
,decode(l_vendor_site_rec.VENDOR_SITE_CODE_ALT,
ap_null_char,NULL,
nvl(l_vendor_site_rec.VENDOR_SITE_CODE_ALT, VENDOR_SITE_CODE_ALT))
,decode(l_vendor_site_rec.PURCHASING_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PURCHASING_SITE_FLAG, PURCHASING_SITE_FLAG))
,decode(l_vendor_site_rec.RFQ_ONLY_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.RFQ_ONLY_SITE_FLAG, RFQ_ONLY_SITE_FLAG))
,decode(l_vendor_site_rec.PAY_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAY_SITE_FLAG, PAY_SITE_FLAG))
,decode(l_vendor_site_rec.ATTENTION_AR_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTENTION_AR_FLAG, ATTENTION_AR_FLAG))
,decode(l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG, HOLD_ALL_PAYMENTS_FLAG))
,decode(l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG))
,decode(l_vendor_site_rec.HOLD_REASON,
ap_null_char,NULL,
nvl(l_vendor_site_rec.HOLD_REASON, HOLD_REASON))
,decode(l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG, HOLD_UNMATCHED_INVOICES_FLAG))
,decode(l_vendor_site_rec.TAX_REPORTING_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.TAX_REPORTING_SITE_FLAG, TAX_REPORTING_SITE_FLAG))
,decode(l_vendor_site_rec.ATTRIBUTE_CATEGORY,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE_CATEGORY, ATTRIBUTE_CATEGORY))
,decode(l_vendor_site_rec.ATTRIBUTE1,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE1, ATTRIBUTE1))
,decode(l_vendor_site_rec.ATTRIBUTE2,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE2, ATTRIBUTE2))
,decode(l_vendor_site_rec.ATTRIBUTE3,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE3, ATTRIBUTE3))
,decode(l_vendor_site_rec.ATTRIBUTE4,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE4, ATTRIBUTE4))
,decode(l_vendor_site_rec.ATTRIBUTE5,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE5, ATTRIBUTE5))
,decode(l_vendor_site_rec.ATTRIBUTE6,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE6, ATTRIBUTE6))
,decode(l_vendor_site_rec.ATTRIBUTE7,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE7, ATTRIBUTE7))
,decode(l_vendor_site_rec.ATTRIBUTE8,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE8, ATTRIBUTE8))
,decode(l_vendor_site_rec.ATTRIBUTE9,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE9, ATTRIBUTE9))
,decode(l_vendor_site_rec.ATTRIBUTE10,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE10, ATTRIBUTE10))
,decode(l_vendor_site_rec.ATTRIBUTE11,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE11, ATTRIBUTE11))
,decode(l_vendor_site_rec.ATTRIBUTE12,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE12, ATTRIBUTE12))
,decode(l_vendor_site_rec.ATTRIBUTE13,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE13, ATTRIBUTE13))
,decode(l_vendor_site_rec.ATTRIBUTE14,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE14, ATTRIBUTE14))
,decode(l_vendor_site_rec.ATTRIBUTE15,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ATTRIBUTE15, ATTRIBUTE15))
,decode(l_vendor_site_rec.VALIDATION_NUMBER,
ap_null_num,NULL,
nvl(l_vendor_site_rec.VALIDATION_NUMBER, VALIDATION_NUMBER))
,decode(l_vendor_site_rec.EXCLUDE_FREIGHT_FROM_DISCOUNT,
ap_null_char,NULL,
nvl(l_vendor_site_rec.EXCLUDE_FREIGHT_FROM_DISCOUNT,
EXCLUDE_FREIGHT_FROM_DISCOUNT))
,decode(l_vendor_site_rec.BANK_CHARGE_BEARER,
ap_null_char,NULL,
nvl(l_vendor_site_rec.BANK_CHARGE_BEARER, BANK_CHARGE_BEARER))
,decode(l_vendor_site_rec.ORG_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.ORG_ID, ORG_ID))
,decode(l_vendor_site_rec.CHECK_DIGITS,
ap_null_char,NULL,
nvl(l_vendor_site_rec.CHECK_DIGITS, CHECK_DIGITS))
,decode(l_vendor_site_rec.ALLOW_AWT_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ALLOW_AWT_FLAG, ALLOW_AWT_FLAG))
,decode(l_vendor_site_rec.AWT_GROUP_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.AWT_GROUP_ID, AWT_GROUP_ID))
,decode(l_vendor_site_rec.DEFAULT_PAY_SITE_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.DEFAULT_PAY_SITE_ID, DEFAULT_PAY_SITE_ID))
,decode(l_vendor_site_rec.PAY_ON_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAY_ON_CODE, PAY_ON_CODE))
,decode(l_vendor_site_rec.PAY_ON_RECEIPT_SUMMARY_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PAY_ON_RECEIPT_SUMMARY_CODE, PAY_ON_RECEIPT_SUMMARY_CODE))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE_CATEGORY,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE_CATEGORY))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE1,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE1))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE2,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE2))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE3,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE3))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE4,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE4))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE5,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE5))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE6,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE6))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE7,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE7))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE8,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE8))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE9,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE9))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE10,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE10))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE11,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE11))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE12,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE12))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE13,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE13))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE14,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE14))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE15,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE15))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE16,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE16))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE17,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE17))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE18,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE18))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE19,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE19))
,decode(l_vendor_site_rec.GLOBAL_ATTRIBUTE20,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE20))
,decode(l_vendor_site_rec.TP_HEADER_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.TP_HEADER_ID, TP_HEADER_ID))
,decode(l_vendor_site_rec.ECE_TP_LOCATION_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.ECE_TP_LOCATION_CODE, ECE_TP_LOCATION_CODE))
,decode(l_vendor_site_rec.PCARD_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PCARD_SITE_FLAG, PCARD_SITE_FLAG))
,decode(l_vendor_site_rec.MATCH_OPTION,
ap_null_char,NULL,
nvl(l_vendor_site_rec.MATCH_OPTION, MATCH_OPTION))
,decode(l_vendor_site_rec.COUNTRY_OF_ORIGIN_CODE,
ap_null_char,NULL,
nvl(l_vendor_site_rec.COUNTRY_OF_ORIGIN_CODE, COUNTRY_OF_ORIGIN_CODE))
,decode(l_vendor_site_rec.FUTURE_DATED_PAYMENT_CCID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.FUTURE_DATED_PAYMENT_CCID, FUTURE_DATED_PAYMENT_CCID))
,decode(l_vendor_site_rec.CREATE_DEBIT_MEMO_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.CREATE_DEBIT_MEMO_FLAG, CREATE_DEBIT_MEMO_FLAG))
,decode(l_vendor_site_rec.SUPPLIER_NOTIF_METHOD,
ap_null_char,NULL,
nvl(l_vendor_site_rec.SUPPLIER_NOTIF_METHOD, SUPPLIER_NOTIF_METHOD))
,decode(l_vendor_site_rec.EMAIL_ADDRESS,
ap_null_char,NULL,
nvl(l_vendor_site_rec.EMAIL_ADDRESS, EMAIL_ADDRESS))
,decode(l_vendor_site_rec.PRIMARY_PAY_SITE_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.PRIMARY_PAY_SITE_FLAG, PRIMARY_PAY_SITE_FLAG))
,decode(l_vendor_site_rec.SHIPPING_CONTROL,
ap_null_char,NULL,
nvl(l_vendor_site_rec.SHIPPING_CONTROL, SHIPPING_CONTROL))
,decode(l_vendor_site_rec.SELLING_COMPANY_IDENTIFIER,
ap_null_char,NULL,
nvl(l_vendor_site_rec.SELLING_COMPANY_IDENTIFIER, SELLING_COMPANY_IDENTIFIER))
,decode(l_vendor_site_rec.GAPLESS_INV_NUM_FLAG,
ap_null_char,NULL,
nvl(l_vendor_site_rec.GAPLESS_INV_NUM_FLAG, GAPLESS_INV_NUM_FLAG))
,decode(l_vendor_site_rec.LOCATION_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.LOCATION_ID, LOCATION_ID))
,decode(l_vendor_site_rec.PARTY_SITE_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.PARTY_SITE_ID, PARTY_SITE_ID))
,decode(l_vendor_site_rec.TOLERANCE_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.TOLERANCE_ID, TOLERANCE_ID))
,decode(l_vendor_site_rec.services_tolerance_id,
ap_null_num,NULL,
nvl(l_vendor_site_rec.services_tolerance_id,services_tolerance_id))
,decode(l_vendor_site_rec.retainage_rate,
ap_null_num,NULL,
nvl(l_vendor_site_rec.retainage_rate,retainage_rate))
--bug6388041
,decode(l_vendor_site_rec.duns_number,
ap_null_char, NULL,
nvl(l_vendor_site_rec.duns_number,duns_number))
-- bug 7437549
,decode(l_vendor_site_rec.EDI_ID_NUMBER,
ap_null_char,NULL,
nvl(l_vendor_site_rec.EDI_ID_NUMBER, EDI_ID_NUMBER))
--bug7561758
,decode(l_vendor_site_rec.PAY_AWT_GROUP_ID,
ap_null_num,NULL,
nvl(l_vendor_site_rec.PAY_AWT_GROUP_ID, PAY_AWT_GROUP_ID))
-- bug 7673494 start
/*Bug9290488 start*/
,decode(l_vendor_site_rec.address_line1,
ap_null_char, NULL,
l_vendor_site_rec.address_line1)
,decode(l_vendor_site_rec.address_lines_alt,
ap_null_char, NULL,
l_vendor_site_rec.address_lines_alt)
,decode(l_vendor_site_rec.address_line2,
ap_null_char, NULL,
l_vendor_site_rec.address_line2)
,decode(l_vendor_site_rec.address_line3,
ap_null_char, NULL,
l_vendor_site_rec.address_line3)
,decode(l_vendor_site_rec.city,
ap_null_char, NULL,
l_vendor_site_rec.city)
,decode(l_vendor_site_rec.state,
ap_null_char, NULL,
l_vendor_site_rec.state)
,decode(l_vendor_site_rec.zip,
ap_null_char, NULL,
l_vendor_site_rec.zip)
,decode(l_vendor_site_rec.province,
ap_null_char, NULL,
l_vendor_site_rec.province)
,decode(l_vendor_site_rec.country,
ap_null_char, NULL,
l_vendor_site_rec.country)
,decode(l_vendor_site_rec.address_line4,
ap_null_char, NULL,
l_vendor_site_rec.address_line4)
,decode(l_vendor_site_rec.county,
ap_null_char, NULL,
l_vendor_site_rec.county)
,decode(l_vendor_site_rec.address_style,
ap_null_char, NULL,
l_vendor_site_rec.address_style)
,decode(l_vendor_site_rec.language,
ap_null_char, NULL,
l_vendor_site_rec.language)
/*Bug9290488 end*/
-- bug 7673494 end
-- starting the Changes for CLM reference data management bug#9499174
/*Bug 10236833 for cage_code value, replaced ap_null_num with ap_null_char*/
,decode(l_vendor_site_rec.cage_code,
ap_null_char,NULL,
nvl(l_vendor_site_rec.CAGE_CODE, CAGE_CODE))
,decode(l_vendor_site_rec.legal_business_name,
ap_null_char,NULL,
nvl(l_vendor_site_rec.LEGAL_BUSINESS_NAME, LEGAL_BUSINESS_NAME))
,decode(l_vendor_site_rec.doing_bus_as_name,
ap_null_char,NULL,
nvl(l_vendor_site_rec.DOING_BUS_AS_NAME, DOING_BUS_AS_NAME))
,decode(l_vendor_site_rec.division_name,
ap_null_char,NULL,
nvl(l_vendor_site_rec.DIVISION_NAME, DIVISION_NAME))
,decode(l_vendor_site_rec.small_business_code,
ap_null_char,NULL,
nvl(l_vendor_site_rec.SMALL_BUSINESS_CODE, SMALL_BUSINESS_CODE))
,decode(l_vendor_site_rec.CCR_COMMENTS ,
ap_null_char,NULL,
nvl(l_vendor_site_rec.CCR_COMMENTS , CCR_COMMENTS ))
,decode(l_vendor_site_rec.DEBARMENT_START_DATE,
ap_null_date,NULL,
nvl(l_vendor_site_rec.DEBARMENT_START_DATE,DEBARMENT_START_DATE) )
,decode(l_vendor_site_rec.DEBARMENT_END_DATE ,
ap_null_date,NULL,
nvl(l_vendor_site_rec.DEBARMENT_END_DATE,DEBARMENT_END_DATE) )
-- Ending the Changes for CLM reference data management bug#9499174
INTO
l_vendor_site_rec.AREA_CODE
,l_vendor_site_rec.PHONE
,l_vendor_site_rec.CUSTOMER_NUM
,l_vendor_site_rec.SHIP_TO_LOCATION_ID
,l_vendor_site_rec.BILL_TO_LOCATION_ID
,l_vendor_site_rec.SHIP_VIA_LOOKUP_CODE
,l_vendor_site_rec.FREIGHT_TERMS_LOOKUP_CODE
,l_vendor_site_rec.FOB_LOOKUP_CODE
,l_vendor_site_rec.INACTIVE_DATE
,l_vendor_site_rec.FAX
,l_vendor_site_rec.FAX_AREA_CODE
,l_vendor_site_rec.TELEX
,l_vendor_site_rec.TERMS_DATE_BASIS
,l_vendor_site_rec.DISTRIBUTION_SET_ID
,l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID
,l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID
,l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE
,l_vendor_site_rec.PAYMENT_PRIORITY
,l_vendor_site_rec.TERMS_ID
,l_vendor_site_rec.INVOICE_AMOUNT_LIMIT
,l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE
,l_vendor_site_rec.ALWAYS_TAKE_DISC_FLAG
,l_vendor_site_rec.INVOICE_CURRENCY_CODE
,l_vendor_site_rec.PAYMENT_CURRENCY_CODE
,l_vendor_site_rec.VENDOR_SITE_ID
,l_vendor_site_rec.VENDOR_ID
,l_vendor_site_rec.VENDOR_SITE_CODE
,l_vendor_site_rec.VENDOR_SITE_CODE_ALT
,l_vendor_site_rec.PURCHASING_SITE_FLAG
,l_vendor_site_rec.RFQ_ONLY_SITE_FLAG
,l_vendor_site_rec.PAY_SITE_FLAG
,l_vendor_site_rec.ATTENTION_AR_FLAG
,l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG
,l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG
,l_vendor_site_rec.HOLD_REASON
,l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG
,l_vendor_site_rec.TAX_REPORTING_SITE_FLAG
,l_vendor_site_rec.ATTRIBUTE_CATEGORY
,l_vendor_site_rec.ATTRIBUTE1
,l_vendor_site_rec.ATTRIBUTE2
,l_vendor_site_rec.ATTRIBUTE3
,l_vendor_site_rec.ATTRIBUTE4
,l_vendor_site_rec.ATTRIBUTE5
,l_vendor_site_rec.ATTRIBUTE6
,l_vendor_site_rec.ATTRIBUTE7
,l_vendor_site_rec.ATTRIBUTE8
,l_vendor_site_rec.ATTRIBUTE9
,l_vendor_site_rec.ATTRIBUTE10
,l_vendor_site_rec.ATTRIBUTE11
,l_vendor_site_rec.ATTRIBUTE12
,l_vendor_site_rec.ATTRIBUTE13
,l_vendor_site_rec.ATTRIBUTE14
,l_vendor_site_rec.ATTRIBUTE15
,l_vendor_site_rec.VALIDATION_NUMBER
,l_vendor_site_rec.EXCLUDE_FREIGHT_FROM_DISCOUNT
,l_vendor_site_rec.BANK_CHARGE_BEARER
,l_vendor_site_rec.ORG_ID
,l_vendor_site_rec.CHECK_DIGITS
,l_vendor_site_rec.ALLOW_AWT_FLAG
,l_vendor_site_rec.AWT_GROUP_ID
,l_vendor_site_rec.DEFAULT_PAY_SITE_ID
,l_vendor_site_rec.PAY_ON_CODE
,l_vendor_site_rec.PAY_ON_RECEIPT_SUMMARY_CODE
,l_vendor_site_rec.GLOBAL_ATTRIBUTE_CATEGORY
,l_vendor_site_rec.GLOBAL_ATTRIBUTE1
,l_vendor_site_rec.GLOBAL_ATTRIBUTE2
,l_vendor_site_rec.GLOBAL_ATTRIBUTE3
,l_vendor_site_rec.GLOBAL_ATTRIBUTE4
,l_vendor_site_rec.GLOBAL_ATTRIBUTE5
,l_vendor_site_rec.GLOBAL_ATTRIBUTE6
,l_vendor_site_rec.GLOBAL_ATTRIBUTE7
,l_vendor_site_rec.GLOBAL_ATTRIBUTE8
,l_vendor_site_rec.GLOBAL_ATTRIBUTE9
,l_vendor_site_rec.GLOBAL_ATTRIBUTE10
,l_vendor_site_rec.GLOBAL_ATTRIBUTE11
,l_vendor_site_rec.GLOBAL_ATTRIBUTE12
,l_vendor_site_rec.GLOBAL_ATTRIBUTE13
,l_vendor_site_rec.GLOBAL_ATTRIBUTE14
,l_vendor_site_rec.GLOBAL_ATTRIBUTE15
,l_vendor_site_rec.GLOBAL_ATTRIBUTE16
,l_vendor_site_rec.GLOBAL_ATTRIBUTE17
,l_vendor_site_rec.GLOBAL_ATTRIBUTE18
,l_vendor_site_rec.GLOBAL_ATTRIBUTE19
,l_vendor_site_rec.GLOBAL_ATTRIBUTE20
,l_vendor_site_rec.TP_HEADER_ID
,l_vendor_site_rec.ECE_TP_LOCATION_CODE
,l_vendor_site_rec.PCARD_SITE_FLAG
,l_vendor_site_rec.MATCH_OPTION
,l_vendor_site_rec.COUNTRY_OF_ORIGIN_CODE
,l_vendor_site_rec.FUTURE_DATED_PAYMENT_CCID
,l_vendor_site_rec.CREATE_DEBIT_MEMO_FLAG
,l_vendor_site_rec.SUPPLIER_NOTIF_METHOD
,l_vendor_site_rec.EMAIL_ADDRESS
,l_vendor_site_rec.PRIMARY_PAY_SITE_FLAG
,l_vendor_site_rec.SHIPPING_CONTROL
,l_vendor_site_rec.SELLING_COMPANY_IDENTIFIER
,l_vendor_site_rec.GAPLESS_INV_NUM_FLAG
,l_vendor_site_rec.LOCATION_ID
,l_vendor_site_rec.PARTY_SITE_ID
,l_vendor_site_rec.TOLERANCE_ID
,l_vendor_site_rec.services_tolerance_id
,l_vendor_site_rec.retainage_rate
,l_vendor_site_rec.duns_number --bug6388041
,l_vendor_site_rec.EDI_ID_NUMBER --bug7437549
,l_vendor_site_rec.PAY_AWT_GROUP_ID --bug7561758
-- bug 7673494 start
,l_vendor_site_rec.address_line1
,l_vendor_site_rec.address_lines_alt
,l_vendor_site_rec.address_line2
,l_vendor_site_rec.address_line3
,l_vendor_site_rec.city
,l_vendor_site_rec.state
,l_vendor_site_rec.zip
,l_vendor_site_rec.province
,l_vendor_site_rec.country
,l_vendor_site_rec.address_line4
,l_vendor_site_rec.county
,l_vendor_site_rec.address_style
,l_vendor_site_rec.language
-- bug 7673494 end
-- starting the Changes for CLM reference data management bug#9499174
,l_vendor_site_rec.CAGE_CODE
,l_vendor_site_rec.LEGAL_BUSINESS_NAME
,l_vendor_site_rec.DOING_BUS_AS_NAME
,l_vendor_site_rec.DIVISION_NAME
,l_vendor_site_rec.SMALL_BUSINESS_CODE
,l_vendor_site_rec.CCR_COMMENTS
,l_vendor_site_rec.DEBARMENT_START_DATE
,l_vendor_site_rec.DEBARMENT_END_DATE
-- Ending the Changes for CLM reference data management bug#9499174
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_vendor_site_id;
ap_vendor_sites_pkg.update_row(
p_vendor_site_rec => l_vendor_site_rec,
p_last_update_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_login => l_last_update_login,
p_request_id => l_request_id ,
p_program_application_id => l_program_application_id,
p_program_id => l_program_id,
p_program_update_date => sysdate,
p_vendor_site_id => p_vendor_site_id);
ROLLBACK TO Update_Vendor_Site_PUB;
ROLLBACK TO Update_Vendor_Site_PUB;
ROLLBACK TO Update_Vendor_Site_PUB;
END Update_Vendor_Site;
SELECT language_code,installed_flag
FROM fnd_languages
WHERE nls_language = p_vendor_site_rec.language
AND nvl(Installed_flag,'I') in ('I','B','D');
SELECT Set_Of_Books_Id
INTO l_sob
FROM ap_system_parameters
WHERE org_id = p_vendor_site_rec.org_id;
SELECT vendor_type_lookup_code
INTO G_vendor_type_lookup_code
FROM ap_suppliers
WHERE vendor_id = p_vendor_site_rec.vendor_id;
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_HOLD_UNMAT_INV',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Hold_Unmatched_Invoices_Flag: '
||p_vendor_site_rec.hold_unmatched_invoices_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_VENDOR_SITE_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Site_Code: '||p_vendor_site_rec.vendor_site_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_TAX_RS_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Tax_Reporting_Site_Flag: '
||p_vendor_site_rec.tax_reporting_site_flag);
SELECT COUNT(*) INTO l_status FROM DUAL WHERE EXISTS (
SELECT 'Y'
FROM zx_input_classifications_v
WHERE lookup_type in ('ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS')
AND org_id in ( p_vendor_site_rec.org_id, -99)
AND enabled_flag = 'Y'
AND LOOKUP_CODE = p_vendor_site_rec.VAT_CODE );
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_VAT_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vat_Code: '
||p_vendor_site_rec.vat_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_DEF_PAY_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_Id: '||p_vendor_site_rec.org_id
||' ,Deafult_Pay_Site_Id: '
||p_vendor_site_rec.default_pay_site_id);
--AND p_mode = 'I' -- should skip this check for Update, xili, 12/18/2006 --commenting for Bug 14032240 to validate during UPDATE aswell
AND p_vendor_site_rec.vendor_site_code <> ap_null_char THEN
Check_dup_vendor_site ( p_vendor_site_rec.vendor_id,
p_vendor_site_rec.vendor_site_code,
p_vendor_site_rec.org_name,
p_vendor_site_rec.org_id,
x_valid);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_DUPLICATE_VENDOR_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_Id: '||p_vendor_site_rec.org_id
||' ,Vendor_Site_Code: '
||p_vendor_site_rec.vendor_site_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_VEN_DUPLICATE_ECE_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_Id: '||p_vendor_site_rec.org_id
||' ,Vendor_Site_Code: '
||p_vendor_site_rec.vendor_site_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_ON_RCE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_On_Code: '||p_vendor_site_rec.pay_on_code
||' ,Pay_On_Receipt_Summary_Code: '
||p_vendor_site_rec.pay_on_receipt_summary_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_SHIPPING_CONTROL',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Shipping_Control: '||p_vendor_site_rec.shipping_control);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_TERM',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Terms_Id: '||p_vendor_site_rec.terms_id
||' ,Terms_Name: '||p_vendor_site_rec.terms_name
||' ,Default_Terms_Id: '||p_vendor_site_rec.default_terms_Id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_ON_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_On_Code: '||p_vendor_site_rec.pay_on_code
||' ,Purchasing_Site_Flag: '||p_vendor_site_rec.purchasing_site_flag
||' ,Pay_Site_Flag: '||p_vendor_site_rec.pay_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_ON_RCE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_On_Code: '||p_vendor_site_rec.pay_on_code
||' ,Pay_On_Receipt_Summary_Code: '
||p_vendor_site_rec.pay_on_receipt_summary_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_PRIORITY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Payment_Priority: '||p_vendor_site_rec.payment_priority);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_INV_CURRENCY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Invoice_Currency_Code: '||p_vendor_site_rec.invoice_currency_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_CURRENCY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Invoice_Currency_Code: '||p_vendor_site_rec.invoice_currency_code
||' ,Payment_Currency_Code: '||p_vendor_site_rec.payment_currency_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_ACCOUNTS_PAY_CCID',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Accts_Pay_Code_Comb_Id: '
||p_vendor_site_rec.accts_pay_code_combination_id
||' ,Set_Of_Books_Id: '||l_sob);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_SUPP_INVALID_CCID',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Prepay_Code_Combination_Id: '
||p_vendor_site_rec.prepay_code_combination_id
||' ,Set_Of_Books_Id: '||l_sob);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_SUPP_INVALID_CCID',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Future_Dated_Payment_Ccid: '
||p_vendor_site_rec.future_dated_payment_ccid
||' ,Set_Of_Books_Id: '||l_sob);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_DUNS_NUMBER',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Invalid_Duns_number: '
||p_vendor_site_rec.duns_number);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_ISP_NOT_AVAILABLE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME
||l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id:'
||p_vendor_site_rec.vendor_site_interface_id
||' ISP page not available and CCR data available');
IF (Insert_Rejections( 'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_SMALL_NSMALL_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME
||l_api_name,'Parameters: '
||' Vendor site interface id '
||p_vendor_site_rec.vendor_site_interface_id
||', Small business code '
||p_vendor_site_rec.small_business_code);
--update validations
null;
l_debug_info := 'check for prohibiting the update of CCR vendor site';
Chk_update_site_ccr_values(p_vendor_site_rec,
p_calling_prog,
x_valid);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_CCR_NO_UPDATE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,attempting to update non-updatablee elements on CCR site : '
||p_vendor_site_rec.vendor_site_id);
FND_MESSAGE.SET_NAME('SQLAP','AP_CCR_NO_UPDATE');
l_api_name,'Call after Chk_update_site_ccr_values... Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,attempting to update non-updatablee elements on CCR site : '
||p_vendor_site_rec.vendor_site_id);
--insert validations
------------------------------------------------------------------------
l_debug_info := 'Call to Validate payee';
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
--'AP_INVALID_PAYEE',
'AP_INVALID_PAYEE_INFO',/*bug 7572325*/
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id:' ||
p_vendor_site_rec.vendor_interface_id);
SELECT null INTO l_dummy
FROM FND_TERRITORIES
WHERE TERRITORY_CODE = p_vendor_site_rec.country
AND OBSOLETE_FLAG = 'N';
IF (Insert_Rejections
(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_API_INVALID_COUNTRY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Country code validation '
||p_vendor_site_rec.country);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PARTY_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||' Org_Id: '||p_vendor_site_rec.org_id
||', Org_Name: '||p_vendor_site_rec.ORG_NAME
||', Party_site_name: '||p_vendor_site_rec.party_site_name
||', Party_site_id: '|| p_vendor_site_rec.party_site_id
||', Vendor_id: '|| p_vendor_site_rec.vendor_id
||', Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'INVALID_NLS_LANGUAGE',
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE)
THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING (G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||', P_Int_Table: '||'AP_SUPPLIER_SITES_INT'
||', P_Int_Key: '||p_vendor_site_rec.vendor_site_interface_id
||', P_Language: '||'LANGUAGE');
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'DISABLED_NLS_LANGUAGE',
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE)
THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING (G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Parameters: '
||', P_Int_Table: '||'AP_SUPPLIER_SITES_INT'
||', P_Int_Key: '||p_vendor_site_rec.vendor_site_interface_id
||',P_Language: '||'LANGUAGE');
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_MULTIPLE_ADDRESS',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Party_site_id: '||p_vendor_site_rec.party_site_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_ADDRESS',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Party_site_id: '||p_vendor_site_rec.party_site_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_NULL_COUNTRY_NAME',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_id: '||p_vendor_site_rec.org_id
||' ,Org_Name: '||p_vendor_site_rec.org_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_NULL_ADDRESS_LINE1',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_id: '||p_vendor_site_rec.org_id
||' ,Org_Name: '||p_vendor_site_rec.org_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_DUP_PARTY_SITE_NAME',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_id: '||p_vendor_site_rec.org_id
||' ,Org_Name: '||p_vendor_site_rec.org_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PRIM_PAY_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Vendor_Id: '||p_vendor_site_rec.vendor_id
||' ,Org_id: '||p_vendor_site_rec.org_id
||' ,Org_Name: '||p_vendor_site_rec.org_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_COUNTRY_ORIGIN',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '||p_vendor_site_rec.vendor_site_interface_id
||' ,Country_Of_Origin_Code: '||p_vendor_site_rec.country_of_origin_code);
--addl insert validations
------------------------------------------------------------------------
l_debug_info := 'Call to Validate pcard_site_flag';
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PCARD_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Pcard_Site_Flag: '
||p_vendor_site_rec.pcard_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PURCHASING_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Purchasing_Site_Flag: '
||p_vendor_site_rec.purchasing_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_RFQ_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Rfq_Only_Site_Flag: '
||p_vendor_site_rec.rfq_only_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_SITE_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_Site_Flag: '
||p_vendor_site_rec.pay_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_ATTN_AR_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Attention_Ar_Flag: '
||p_vendor_site_rec.attention_ar_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PRIMPAY_SITE_FLAG',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Primary_Pay_Site_Flag: '
||p_vendor_site_rec.primary_pay_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_FREIGHT_TERMS',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Freight_Terms_Lookup_Code '
||p_vendor_site_rec.freight_terms_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_FOB',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Fob_Lookup_Code '
||p_vendor_site_rec.fob_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_DATE_BASIS',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_Date_Basis_Lookup_Code '
||p_vendor_site_rec.pay_date_basis_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_PAY_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Pay_Group_Lookup_Code '
||p_vendor_site_rec.pay_group_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_TERMS_DATE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Terms_Date_Basis '
||p_vendor_site_rec.terms_date_basis);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_DEBIT_MEMO',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Create_Debit_Memo_Flag '
||p_vendor_site_rec.create_debit_memo_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_EXC_FR_DISC',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Exclude_Freight_From_Discount '
||p_vendor_site_rec.exclude_freight_from_discount);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_HOLD_FUT_PAY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Hold_Future_Payments_Flag '
||p_vendor_site_rec.hold_future_payments_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_HOLD_ALL_PAY',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Hold_All_Payments_Flag '
||p_vendor_site_rec.hold_all_payments_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_TAKE_DISC',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Always_Take_Disc_Flag '
||p_vendor_site_rec.always_take_disc_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_MATCH_OPTION',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Match_Option '
||p_vendor_site_rec.match_option);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_ALLOW_AWT',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Allow_Awt_Flag '
||p_vendor_site_rec.allow_awt_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_AWT_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Allow_Awt_Flag '
||p_vendor_site_rec.allow_awt_flag
||' , Awt_Group_Id: '||p_vendor_site_rec.awt_group_id
||' ,Awt_Group_Name: '||p_vendor_site_rec.awt_group_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_AWT_GROUP',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Allow_Awt_Flag '
||p_vendor_site_rec.allow_awt_flag
||' , Awt_Group_Id: '||p_vendor_site_rec.pay_awt_group_id
||' ,Awt_Group_Name: '||p_vendor_site_rec.pay_awt_group_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_DIST_SET',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Distribution_Set_Id '
||p_vendor_site_rec.distribution_set_id
||' , Distribution_Set_Name: '
||p_vendor_site_rec.distribution_set_name
||' ,Default_Dist_Set_Id: '
||p_vendor_site_rec.default_dist_set_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_SHIP_LOC',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Ship_To_Location_Id '
||p_vendor_site_rec.ship_to_location_id
||' , Ship_To_Location_Code '
||p_vendor_site_rec.ship_to_location_code
||' ,Default_ship_to_loc_Id: '
||p_vendor_site_rec.default_ship_to_loc_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_BILL_LOC',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Bill_To_Location_Id '
||p_vendor_site_rec.bill_to_location_id
||' , Bill_To_Location_Code '
||p_vendor_site_rec.bill_to_location_code
||' ,Default_bill_to_loc_Id: '
||p_vendor_site_rec.default_bill_to_loc_id);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_NOTIF_METHOD',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Supplier_Notif_Method: '
||p_vendor_site_rec.supplier_notif_method);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_REMIT_ADVICE_FLAG_INVALID',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Remit_Advice_Delivery_Method: '
||p_vendor_site_rec.remit_advice_delivery_method);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSISTENT_TOLERANCE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Toleranace_Id: '
||p_vendor_site_rec.tolerance_id
||' ,Tolerance_Name: '||p_vendor_site_rec.tolerance_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INCONSIS_SERVICE_TOL', /*Bug11884093 */
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Toleranace_Id: '
||p_vendor_site_rec.services_tolerance_id
||' ,Tolerance_Name: '||p_vendor_site_rec.services_tolerance_name);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_RETAINAGE_RATE',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Retainage_Rate '
||p_vendor_site_rec.retainage_rate);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_SHIP_VIA',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' ,Ship_Via_Lookup_Code: '
||p_vendor_site_rec.ship_via_lookup_code);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_DUPLICATE_TAX_RS',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' Vendor_Id: '||p_vendor_site_rec.vendor_id
||' Vendor_Site_Id: '||p_vendor_site_rec.vendor_site_id
||' Org_Id: '||p_vendor_site_rec.org_id
||' Org_Name: '||p_vendor_site_rec.org_name
||' ,Tax_Reporting_Site_Flag: '
||p_vendor_site_rec.tax_reporting_site_flag);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
p_vendor_site_rec.vendor_site_interface_id,
'AP_INVALID_SHIPPING_CONTROL',
g_user_id,
g_login_id,
'Validate_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL)
THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Site_Interface_Id: '
||p_vendor_site_rec.vendor_site_interface_id
||' Gapless Invoice Num Flag: '
||p_vendor_site_rec.gapless_inv_num_flag
||' Selling Company Identifier: '
||p_vendor_site_rec.selling_company_identifier);
l_last_update_login number := FND_GLOBAL.LOGIN_ID;
SELECT HZ_PARTY_NUMBER_S.Nextval
INTO l_party_rec.party_number
FROM DUAL;
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_PERSON_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_person'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
SELECT HZ_PARTY_NUMBER_S.Nextval
INTO l_party_rec.party_number
FROM DUAL;
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_CONTACT_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_org_contact'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
SELECT HZ_PARTY_SITE_NUMBER_S.Nextval
INTO l_party_site_rec.party_site_number
FROM DUAL;
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_ASSIGN_SITE_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_party_site'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_PRIM_PHONE_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_phone_contact_point'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_ALT_PHONE_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_phone_contact_point'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_FAX_PHONE_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_phone_contact_point'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_EMAIL_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_email_contact_point'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
--delete the message stack for the index
--already fetched
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
IF (Insert_Rejections(
p_parent_table => 'AP_SUP_SITE_CONTACT_INT',
p_parent_id => p_vendor_contact_rec.vendor_contact_interface_id,
p_reject_code => 'AP_CREATE_WEB_ERROR',
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => 'hz_party_v2pub.create_web_contact_point'
) <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Error logging into Rejections table');
SELECT po_vendor_contacts_s.nextval
INTO l_vendor_contact_rec.vendor_contact_id
FROM dual;
INSERT INTO ap_supplier_contacts(
per_party_id,
relationship_id,
rel_party_id,
party_site_id,
org_contact_id,
org_party_site_id,
--vendor_site_id, Bug 7013954 Vendor Site info no longer used
vendor_contact_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inactive_date, --Bug 4994974
attribute_category, --bug 6745669 -- added dff columns
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
--bug 6745669
)VALUES(
l_vendor_contact_rec.per_party_id,
l_vendor_contact_rec.relationship_id,
l_vendor_contact_rec.rel_party_id,
l_vendor_contact_rec.party_site_id,
l_vendor_contact_rec.org_contact_id,
l_vendor_contact_rec.org_party_site_id,
--l_vendor_contact_rec.vendor_site_id, Bug 7013954 Vendor Site info no longer used
l_vendor_contact_rec.vendor_contact_id,
SYSDATE,
nvl(fnd_global.user_id,-1),
SYSDATE,
nvl(fnd_global.user_id,-1),
nvl(fnd_global.login_id,-1),
nvl(FND_GLOBAL.conc_request_id,-1),
nvl(FND_GLOBAL.prog_appl_id,-1),
nvl(FND_GLOBAL.conc_program_id,-1),
sysdate,
l_vendor_contact_rec.inactive_date, --Bug 4994974
l_vendor_contact_rec.attribute_category, --bug 6745669 -- added dff columns
l_vendor_contact_rec.attribute1,
l_vendor_contact_rec.attribute2,
l_vendor_contact_rec.attribute3,
l_vendor_contact_rec.attribute4,
l_vendor_contact_rec.attribute5,
l_vendor_contact_rec.attribute6,
l_vendor_contact_rec.attribute7,
l_vendor_contact_rec.attribute8,
l_vendor_contact_rec.attribute9,
l_vendor_contact_rec.attribute10,
l_vendor_contact_rec.attribute11,
l_vendor_contact_rec.attribute12,
l_vendor_contact_rec.attribute13,
l_vendor_contact_rec.attribute14,
l_vendor_contact_rec.attribute15
);
FND_MSG_PUB.DELETE_MSG(p_msg_index => i);
FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
PROCEDURE Update_Vendor_Contact
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_vendor_contact_rec IN r_vendor_contact_rec_type,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Vendor_Contact';
SAVEPOINT Update_Vendor_Contact_PUB;
UPDATE ap_supplier_contacts set
last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_login_id,
inactive_date =p_vendor_contact_rec.inactive_date
WHERE per_party_id = p_vendor_contact_rec.per_party_id AND
relationship_id =p_vendor_contact_rec.relationship_id AND
rel_party_id= p_vendor_contact_rec.rel_party_id AND
party_site_id = p_vendor_contact_rec.party_site_id AND
org_contact_id =p_vendor_contact_rec.org_contact_id AND
vendor_contact_id =p_vendor_contact_rec.vendor_contact_id;
ROLLBACK TO Update_Vendor_Contact_PUB;
ROLLBACK TO Update_Vendor_Contact_PUB;
END Update_Vendor_Contact;
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_PARTY_SITE_FOR_CONT',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
||' Party_site_name: '||p_vendor_contact_rec.party_site_name
||', Vendor_id: '|| p_vendor_contact_rec.vendor_id
||', Party_Site_Id: '||p_vendor_contact_rec.party_site_id);
SELECT hps.party_id,
hps.location_id
INTO x_org_party_id,
x_location_id
FROM HZ_Party_Sites hps, po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_vendor_contact_rec.vendor_site_id
AND pvs.party_site_id = hps.party_site_id;
SELECT party_site_id
INTO p_vendor_contact_rec.org_party_site_id
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_contact_rec.vendor_site_id;
SELECT hps.party_id,
hps.location_id
INTO x_org_party_id,
x_location_id
FROM HZ_Party_Sites hps, po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_vendor_contact_rec.vendor_site_id
AND pvs.party_site_id = hps.party_site_id;
SELECT party_site_id
INTO p_vendor_contact_rec.org_party_site_id
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_contact_rec.vendor_site_id;
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_PARTY_SITE_FOR_CONT',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
--||' Vendor_Site_Id: '||p_vendor_contact_rec.vendor_site_id
||', Party_Site_Id: '||p_vendor_contact_rec.party_site_id);
SELECT hps.party_id,
hps.location_id
INTO x_org_party_id,
x_location_id
FROM HZ_Party_Sites hps
WHERE hps.party_site_id =
p_vendor_contact_rec.org_party_site_id;
SELECT aps.party_id
INTO x_org_party_id
FROM AP_SUPPLIERS aps
WHERE aps.vendor_id = p_vendor_contact_rec.vendor_id;
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INCONSISTENT_PARTY_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
||',Vendor_Site_Id: '||p_vendor_contact_rec.vendor_site_id
||', Org_Party_Site_Id: '||p_vendor_contact_rec.org_party_site_id);
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_PARTY_SITE_FOR_CONT',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
--||' Vendor_Site_Id: '||p_vendor_contact_rec.vendor_site_id
||', Org_Party_Site_Id: '||p_vendor_contact_rec.org_party_site_id);
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_PARTY_SITE',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
--||' Vendor_Site_Id: '||p_vendor_contact_rec.vendor_site_id
||', Party_Site_Id: '||p_vendor_contact_rec.party_site_id);
Select party_id into l_vend_party_id
from ap_suppliers
where vendor_id = p_vendor_contact_rec.vendor_id ;
select hpc.party_id,
hr.relationship_id,
hr.party_id
into p_vendor_contact_rec.per_party_id,
p_vendor_contact_rec.relationship_id,
p_vendor_contact_rec.rel_party_id
from hz_parties hpc,
hz_contact_points hcpp,
hz_contact_points hcpe,
hz_relationships hr
where hr.subject_id = l_vend_party_id --
And hcpp.owner_table_name(+) = 'HZ_PARTIES'
And hcpp.owner_table_id(+) = hr.PARTY_ID
And hcpp.phone_line_type(+) = 'GEN'
And hcpp.contact_point_type(+) = 'PHONE'
And hcpe.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and hcpe.OWNER_TABLE_ID(+) = hr.PARTY_ID
And hcpe.CONTACT_POINT_TYPE(+) = 'EMAIL'
and hr.object_id = hpc.party_id
and hr.subject_type = 'ORGANIZATION'
and hr.subject_table_name = 'HZ_PARTIES'
and hr.object_table_name = 'HZ_PARTIES'
and hr.object_type = 'PERSON'
and hr.relationship_code = 'CONTACT'
and hr.directional_flag = 'B'
and hr.relationship_type = 'CONTACT'
and hpc.PARTY_TYPE = 'PERSON'
and nvl(upper(hpc.person_first_name),'DUMMY')
= nvl(upper(p_vendor_contact_rec.person_first_name),'DUMMY')
and nvl(upper(hpc.person_middle_name),'DUMMY')
= nvl(upper(p_vendor_contact_rec.person_middle_name),'DUMMY')
and nvl(upper(hpc.person_last_name),'DUMMY')
= nvl(upper(p_vendor_contact_rec.person_last_name),'DUMMY')
and nvl(upper(hcpp.phone_area_code),'DUMMY')
= nvl(upper(p_vendor_contact_rec.area_code),'DUMMY')
and nvl(upper(hcpp.PHONE_NUMBER),'DUMMY')
= nvl(upper(p_vendor_contact_rec.phone),'DUMMY')
and nvl(upper(hcpe.email_address),'DUMMY')
= nvl(upper(p_vendor_contact_rec.email_address),'DUMMY')
and rownum < 2 ;
Select org_contact_id into p_vendor_contact_rec.org_contact_id
from ap_supplier_contacts
where per_party_id = p_vendor_contact_rec.per_party_id
and relationship_id = p_vendor_contact_rec.relationship_id
and rel_party_id = p_vendor_contact_rec.rel_party_id
and rownum < 2 ;
Select count(*) into l_combo_ct from ap_supplier_contacts
where per_party_id = p_vendor_contact_rec.per_party_id
and relationship_id = p_vendor_contact_rec.relationship_id
and rel_party_id = p_vendor_contact_rec.rel_party_id
and NVL(org_party_site_id, -1) = NVL(p_vendor_contact_rec.org_party_site_id, -1) ;
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_VEN_CONTACT_DUP_NAME',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
-- put true message to conc log file
l_debug_info := '***** ERROR : Contact information you are trying to import is already associated ';
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_REL_PARTY',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
||' Party_Site_Id: '||p_vendor_contact_rec.org_party_site_id
||', rel_party_id: '||p_vendor_contact_rec.rel_party_id);
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_RELATIONSHIP',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
||' Vendor_Interface_Id: '||p_vendor_contact_rec.vendor_interface_id
||', Relationship_Id: '||p_vendor_contact_rec.relationship_id);
IF (Insert_Rejections(
'AP_SUP_SITE_CONTACT_INT',
p_vendor_contact_rec.vendor_contact_interface_id,
'AP_INVALID_ORG_CONTACT',
g_user_id,
g_login_id,
'Validate_Vendor_Contact') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
l_api_name,'Parameters: '
||' Vendor_Contact_Interface_Id: '||
p_vendor_contact_rec.vendor_contact_interface_id
||' Vendor_Interface_Id: '||p_vendor_contact_rec.vendor_id
||', org_contact_id: '||p_vendor_contact_rec.org_contact_id);
SELECT *
FROM Ap_Suppliers_Int
WHERE import_request_id = l_request_id
AND vendor_interface_id IS NOT NULL
ORDER BY segment1;
SELECT temp_ext_bank_acct_id
FROM IBY_TEMP_EXT_BANK_ACCTS
WHERE calling_app_unique_ref1 = p_unique_ref
--Bug 7412849 (Base Bug 7387700) As status can be NULL, this where condition always resolves to FALSE.
--Added NVL around 'status'.
--AND status <> 'PROCESSED';
UPDATE Ap_Suppliers_Int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND NVL(status,'NEW') IN ('NEW', 'REJECTED')
AND EXISTS
( SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C' );
DELETE AP_SUPPLIER_INT_REJECTIONS
WHERE PARENT_TABLE='AP_SUPPLIERS_INT';
UPDATE Ap_Suppliers_Int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL AND
((p_what_to_import = 'ALL' AND nvl(status,'NEW') in ('NEW', 'REJECTED')) OR
(p_what_to_import = 'NEW' AND nvl(status,'NEW') = 'NEW') OR
(p_what_to_import = 'REJECTED' AND nvl(status,'NEW') = 'REJECTED'));
UPDATE Ap_Suppliers_Int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_int_rec.vendor_interface_id;
IF ( Insert_Rejections(
'AP_SUPPLIERS_INT',
vendor_int_rec.vendor_interface_id,
'AP_INVALID_NUM_1099',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE
)
THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
UPDATE Ap_Suppliers_Int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_int_rec.vendor_interface_id;
IF ( Insert_Rejections(
'AP_SUPPLIERS_INT',
vendor_int_rec.vendor_interface_id,
'POS_SPM_CREATE_SUPP_ERR2',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE
)
THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
UPDATE Ap_Suppliers_Int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_int_rec.vendor_interface_id;
IF ( Insert_Rejections(
'AP_SUPPLIERS_INT',
vendor_int_rec.vendor_interface_id,
'POS_SPM_CREATE_SUPP_ERR1',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE
)
THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
fails validation we shall rollback to this savepoint and update
the supplier record as "REJECTED" and insert a record into Rejection
table and thereafter commit the work.*/
SAVEPOINT Import_Vendor_PUB2;
UPDATE Ap_Suppliers_Int
SET status = 'PROCESSED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id;
UPDATE Ap_Supplier_Sites_Int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = vendor_rec.vendor_interface_id;
UPDATE Ap_Sup_Site_Contact_Int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = vendor_rec.vendor_interface_id;
UPDATE IBY_TEMP_EXT_BANK_ACCTS
SET ext_payee_id = l_ext_payee_id
,account_owner_party_id = l_party_id -- bug 6753331
WHERE calling_app_unique_ref1 = vendor_rec.vendor_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'PROCESSED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
UPDATE Ap_Suppliers_Int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (Insert_Rejections(
--'IBY_TEMP_EXT_BANK_ACCTS',
'AP_SUPPLIERS_INT', --bug 8275512
vendor_rec.vendor_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
vendor_rec.vendor_interface_id,
'AP_PAYEE_CREATION',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
IF (Insert_Rejections(
'AP_SUPPLIERS_INT',
vendor_rec.vendor_interface_id,
--'AP_INVALID_PAYEE',
'AP_INVALID_PAYEE_INFO',/*bug 7572325*/
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
UPDATE Ap_Suppliers_Int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id;
SELECT *
FROM Ap_Supplier_Sites_Int
WHERE import_request_id = l_request_id
AND (org_id IS NOT NULL OR operating_unit_name IS NOT NULL)
AND vendor_id IS NOT NULL
AND org_id = nvl(p_org_id, org_id) --bug13801271
--AND org_id IN (SELECT fsp.org_id FROM financials_system_parameters fsp) --bug13801271
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
SELECT temp_ext_bank_acct_id
FROM IBY_TEMP_EXT_BANK_ACCTS
WHERE calling_app_unique_ref2 = p_unique_ref
--Bug 7412849 (Base Bug 7387700) As status can be NULL, this where condition always resolves to FALSE.
--Added NVL around 'status'.
--AND status <> 'PROCESSED';
UPDATE Ap_Supplier_Sites_Int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND NVL(status,'NEW') IN ('NEW', 'REJECTED')
AND EXISTS
( SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C' )
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
DELETE AP_SUPPLIER_INT_REJECTIONS
WHERE PARENT_TABLE='AP_SUPPLIER_SITES_INT';
UPDATE Ap_Supplier_Sites_Int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL AND
((p_what_to_import = 'ALL' AND nvl(status,'NEW') in ('NEW', 'REJECTED')) OR
(p_what_to_import = 'NEW' AND nvl(status,'NEW') = 'NEW') OR
(p_what_to_import = 'REJECTED' AND nvl(status,'NEW') = 'REJECTED'))
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
UPDATE Ap_Supplier_Sites_Int
SET status = 'REJECTED',
import_request_id = l_request_id
WHERE (operating_unit_name IS NULL AND org_id IS NULL) OR
vendor_id IS NULL
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
INSERT INTO Ap_Supplier_Int_Rejections
(parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT 'AP_SUPPLIER_SITES_INT',vendor_site_interface_id,'AP_ORG_INFO_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
FROM Ap_Supplier_Sites_Int
WHERE STATUS='REJECTED'
AND import_request_id=l_request_id
AND (operating_unit_name IS NULL and org_id IS NULL)
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
UNION
select 'AP_SUPPLIER_SITES_INT',vendor_site_interface_id,'AP_VENDOR_ID_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from Ap_Supplier_Sites_Int
where status='REJECTED'
AND import_request_id=l_request_id
AND vendor_id IS NULL
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
fails validation we shall rollback to this savepoint and update
the supplier site record as "REJECTED" and insert a record into Rejection
table and thereafter commit the work.*/
SAVEPOINT Import_Vendor_Sites_PUB2;
UPDATE Ap_Supplier_Sites_Int
SET status = 'PROCESSED'
WHERE vendor_site_interface_id =
site_rec.vendor_site_interface_id
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
UPDATE AP_Sup_Site_Contact_Int
SET vendor_site_id = l_vendor_site_id
WHERE vendor_id = site_rec.vendor_id
AND vendor_site_code = site_rec.vendor_site_code
AND (org_id = site_rec.org_id OR
operating_unit_name = site_rec.org_name);
SELECT org_id
INTO ext_payee_rec.payer_org_id
FROM Po_Vendor_Sites_All
WHERE vendor_site_id = l_vendor_site_id;
SELECT party_id, 'PAYABLES_DISB'
INTO ext_payee_rec.payee_party_id,
ext_payee_rec.payment_function
FROM Po_Vendors
WHERE vendor_id = site_rec.vendor_id;
UPDATE IBY_TEMP_EXT_BANK_ACCTS
SET ext_payee_id = l_ext_payee_id
,account_owner_party_id = ext_payee_rec.payee_party_id --bug 6753331
WHERE calling_app_unique_ref2 = site_rec.vendor_site_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'PROCESSED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
/*UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (Insert_Rejections(
'IBY_TEMP_EXT_BANK_ACCTS',
site_rec.vendor_site_interface_id,
'AP_BANK_ACCT_CREATION',
g_user_id,
g_login_id,
'Import_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
/*UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (Insert_Rejections(
--'IBY_TEMP_EXT_BANK_ACCTS',
'AP_SUPPLIER_SITES_INT', --bug 8275512
site_rec.vendor_site_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'Import_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
UPDATE Ap_Supplier_Sites_Int
SET status = 'REJECTED'
WHERE vendor_site_interface_id = site_rec.vendor_site_interface_id
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (Insert_Rejections(
--'IBY_TEMP_EXT_BANK_ACCTS',
'AP_SUPPLIER_SITES_INT', --bug 8275512
site_rec.vendor_site_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'Import_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
site_rec.vendor_site_interface_id,
'AP_PAYEE_CREATION',
g_user_id,
g_login_id,
'Import_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
IF (Insert_Rejections(
'AP_SUPPLIER_SITES_INT',
site_rec.vendor_site_interface_id,
'AP_INVALID_PAYEE_INFO',
g_user_id,
g_login_id,
'Import_Vendor_Site') <> TRUE) THEN
--
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_MSG_PUB.Count_And_Get(
p_count => l_msg_count,
p_data => l_msg_data);
UPDATE Ap_Supplier_Sites_Int
SET status = 'REJECTED'
WHERE vendor_site_interface_id =
site_rec.vendor_site_interface_id
AND org_id = nvl(p_org_id, org_id) --bug13989575
AND mo_global.check_access(org_id)= 'Y' --bug13989575
;
SELECT *
FROM Ap_Sup_Site_Contact_Int
WHERE import_request_id = l_request_id
AND vendor_id IS NOT NULL
--AND (org_id IS NOT NULL OR operating_unit_name IS NOT NULL ) /* Bug 12590128 */
AND vendor_id not in(select vendor_id from ap_suppliers
where vendor_type_lookup_code = 'EMPLOYEE') --Bug6648405
-- Bug 7013954 Contacts can be created at Supplier level. So cannot mandate
-- the site information to be present.
-- AND (vendor_site_code IS NOT NULL OR vendor_site_id IS NOT NULL)
AND last_name IS NOT NULL
FOR UPDATE OF status; --Bug6413297
UPDATE Ap_Sup_Site_Contact_Int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND NVL(status,'NEW') IN ('NEW', 'REJECTED')
AND EXISTS
( SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C' );
DELETE AP_SUPPLIER_INT_REJECTIONS
WHERE PARENT_TABLE='AP_SUP_SITE_CONTACT_INT';
UPDATE Ap_Sup_Site_Contact_Int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL AND
((p_what_to_import = 'ALL' AND nvl(status,'NEW') in ('NEW', 'REJECTED')) OR
(p_what_to_import = 'NEW' AND nvl(status,'NEW') = 'NEW') OR
(p_what_to_import = 'REJECTED' AND nvl(status,'NEW') = 'REJECTED'));
UPDATE Ap_Sup_Site_Contact_Int
SET status = 'REJECTED',
import_request_id = l_request_id
WHERE ((vendor_id IS NULL) OR
/* (operating_unit_name IS NULL AND org_id IS NULL) OR */ /* Bug 12590128 */
-- Bug 7013954 Vendor site info no longer used for validation
-- (vendor_site_code IS NULL and vendor_site_id IS NULL) OR
(last_name IS NULL) OR
--Bug7390094 - rejecting contacts for Employee type suppliers.
(vendor_id in(select vendor_id from ap_suppliers
where vendor_type_lookup_code = 'EMPLOYEE')));
insert into ap_supplier_int_rejections
/* Bug 12590128 start */
/*(select 'AP_SUP_SITE_CONTACT_INT',vendor_contact_interface_id,'AP_ORG_INFO_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from ap_sup_site_contact_int
where status='REJECTED'
AND import_request_id=l_request_id
AND (operating_unit_name IS NULL and org_id IS NULL))*/
/* Bug 12590128 end */
-- Bug 7013954 Conditions related to supplier site needs to be commented.
/*UNION
(select 'AP_SUP_SITE_CONTACT_INT',vendor_contact_interface_id,'AP_VENDOR_SITE_INFO_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from ap_sup_site_contact_int
where status='REJECTED'
AND import_request_id=l_request_id
AND (vendor_site_code IS NULL and vendor_site_id IS NULL))*/
/*UNION*/
(select 'AP_SUP_SITE_CONTACT_INT',vendor_contact_interface_id,'AP_LAST_NAME_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from ap_sup_site_contact_int
where status='REJECTED'
AND import_request_id=l_request_id
AND last_name IS NULL)
UNION
(select 'AP_SUP_SITE_CONTACT_INT',vendor_contact_interface_id,'AP_VENDOR_ID_NULL',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from ap_sup_site_contact_int
where status='REJECTED'
AND import_request_id=l_request_id
AND vendor_id IS NULL)
-- Bug 7390094 Prevent contacts import for employee type supplier.
UNION
(select 'AP_SUP_SITE_CONTACT_INT',vendor_contact_interface_id,'AP_EMPLOYEE_CONTACTS',
g_user_id,SYSDATE,g_login_id,g_user_id,SYSDATE
from ap_sup_site_contact_int
where status='REJECTED'
AND import_request_id=l_request_id
AND vendor_id in(select vendor_id from ap_suppliers
where vendor_type_lookup_code = 'EMPLOYEE'));
UPDATE Ap_Sup_Site_Contact_Int
SET status = 'PROCESSED'
WHERE CURRENT OF contact_int_cur; --Bug6413297
UPDATE Ap_Sup_Site_Contact_Int
SET status = 'REJECTED'
WHERE CURRENT OF contact_int_cur; --Bug6413297
PROCEDURE Update_Address_Assignments_DFF(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_contact_party_id IN NUMBER,
p_org_party_site_id IN NUMBER,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Address_Assignments_DFF';
SAVEPOINT Update_Address_Assign_DFF_PUB;
UPDATE ap_supplier_contacts
SET attribute_category=p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15
WHERE per_party_id=p_contact_party_id
AND org_party_site_id=p_org_party_site_id
AND NVL(inactive_date, SYSDATE+1 ) > SYSDATE
;
' Updated '
||l_count
||' rows.');
SELECT vendor_contact_id
INTO l_event_vendor_contact_id
FROM ap_supplier_contacts
WHERE per_party_id=p_contact_party_id
AND org_party_site_id=p_org_party_site_id
AND NVL(inactive_date, SYSDATE+1 ) > SYSDATE ;
ROLLBACK TO Update_Address_Assign_DFF_PUB;
ROLLBACK TO Update_Address_Assign_DFF_PUB;
END Update_Address_Assignments_DFF;
SELECT vendor_id
INTO l_vendor_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = i_vendor_site_id;
SELECT vendor_id
INTO l_vendor_id
FROM po_vendor_contacts
WHERE vendor_contact_id = i_vendor_contact_id
AND ROWNUM = 1;
SELECT to_char(ap_supplier_event_s.nextval)
INTO l_event_key
FROM dual;
SELECT COUNT( * )
INTO l_vndr_site_merged_cnt
FROM ap_duplicate_vendors_all adv
WHERE adv.duplicate_vendor_site_id = p_vendor_site_id
AND NVL(adv.process_flag,'N') = 'Y'
AND ROWNUM=1;