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_lookup_values
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 employee_id
INTO l_dummy
FROM hr_employees_current_v
WHERE employee_id = p_employee_id;
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 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 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 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);
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 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'
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 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(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(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 (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
WHERE ( hps.location_id = nvl(p_location_id, hps.location_id)
AND hps.party_site_id = p_party_site_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 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;
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 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;
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_char,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))
,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_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);
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);
--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',
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_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 pmtmthdAssignmentseo.Payment_Method_Code,
pmtmthdAssignmentseo.InActive_Date,
pmtmthdAssignmentseo.Primary_Flag,
iep.exclusive_payment_flag
FROM iBy_Payment_Methods_vl pmthds,
iBy_ext_Party_pmt_mthds pmtmthdAssignmentseo,
iBy_External_Payees_All iep
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 pmtmthdAssignmentseo.Payment_Function = 'PAYABLES_DISB'
AND pmtmthdAssignmentseo.ext_pmt_Party_Id = iep.ext_payee_id
AND iep.Payee_Party_Id = p_prty_id
AND Party_Site_Id IS NULL
AND Supplier_Site_Id IS NULL;
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;
/* Bug 7155121 Modified the below select to get values from both ap_system_parameters_all and po_vendors*/
SELECT nvl(pv.terms_date_basis, asp.terms_date_basis) -- Bug#7506443
, pv.pay_group_lookup_code
, pv.payment_priority
, asp.always_take_disc_flag
, 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
, asp.match_option
, asp.pay_date_basis_lookup_code
, pv.invoice_amount_limit
, NVL(pv.invoice_currency_code,asp.invoice_currency_code) --bug:7282105
, NVL(pv.payment_currency_code,asp.payment_currency_code) --bug:7282105
, NVL(pv.terms_id, asp.terms_id) -- Bug#7506443
, pv.federal_reportable_flag
, pv.organization_type_lookup_code
, asp.set_of_books_id
--Bug 7651872, asp.hold_unmatched_invoices_flag
, pv.exclude_freight_from_discount
, pv.party_id
, pv.vendor_type_lookup_code
, nvl(pv.allow_awt_flag, 'N')
INTO
l_sup_terms_date_basis
, 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
, l_sup_pay_date_basis_lk_code
, l_invoice_amount_limit
, l_sup_invoice_currency_code
, l_sup_payment_currency_code
, 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_exclude_freight_from_disc
, l_party_id
, l_vendor_type_lookup_code
, l_sup_awt_flag
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;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
SELECT language_code
INTO l_location_rec.language
FROM fnd_languages
WHERE nls_language = l_vendor_site_rec.language;
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
INTO l_auto_tax_calc_flag,l_offset_tax_flag,
l_tax_classification_code -- Bug#7506443
FROM ZX_PARTY_TAX_PROFILE
WHERE PARTY_ID = l_party_id
AND PARTY_TYPE_CODE = 'THIRD_PARTY'
AND ROWNUM = 1;
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,
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_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,
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_char,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
,decode(l_vendor_site_rec.address_line1,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_line1, address_line1))
,decode(l_vendor_site_rec.address_lines_alt,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_lines_alt, address_lines_alt))
,decode(l_vendor_site_rec.address_line2,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_line2, address_line2))
,decode(l_vendor_site_rec.address_line3,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_line3, address_line3))
,decode(l_vendor_site_rec.city,
ap_null_char, NULL,
nvl(l_vendor_site_rec.city, city))
,decode(l_vendor_site_rec.state,
ap_null_char, NULL,
nvl(l_vendor_site_rec.state, state))
,decode(l_vendor_site_rec.zip,
ap_null_char, NULL,
nvl(l_vendor_site_rec.zip, zip))
,decode(l_vendor_site_rec.province,
ap_null_char, NULL,
nvl(l_vendor_site_rec.province, province))
,decode(l_vendor_site_rec.country,
ap_null_char, NULL,
nvl(l_vendor_site_rec.country, country))
,decode(l_vendor_site_rec.address_line4,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_line4, address_line4))
,decode(l_vendor_site_rec.county,
ap_null_char, NULL,
nvl(l_vendor_site_rec.county, county))
,decode(l_vendor_site_rec.address_style,
ap_null_char, NULL,
nvl(l_vendor_site_rec.address_style, address_style))
,decode(l_vendor_site_rec.language,
ap_null_char, NULL,
nvl(l_vendor_site_rec.language, language))
-- bug 7673494 end
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
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 Set_Of_Books_Id
INTO l_sob
FROM ap_system_parameters
WHERE org_id = p_vendor_site_rec.org_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_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
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_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);
--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');
--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',
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,
'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_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_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_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_INCONSISTENT_SERVICE_TOL',
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;
SELECT HZ_PARTY_NUMBER_S.Nextval
INTO l_party_rec.party_number
FROM DUAL;
SELECT HZ_PARTY_SITE_NUMBER_S.Nextval
INTO l_party_site_rec.party_site_number
FROM DUAL;
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
);
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;
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;
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 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',
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);
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 = '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 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',
vendor_rec.vendor_interface_id,
'AP_BANK_ACCT_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);
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',
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',
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;
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' );
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'));
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 ;
INSERT INTO Ap_Supplier_Int_Rejections
(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))
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;
UPDATE Ap_Supplier_Sites_Int
SET status = 'PROCESSED'
WHERE vendor_site_interface_id =
site_rec.vendor_site_interface_id;
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',
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;
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 )
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 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
(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 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;