[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15, SHIP_TO_LOCATION_ID, SHIP_TO_LOCATION_CODE,
BILL_TO_LOCATION_ID, BILL_TO_LOCATION_CODE, SHIP_VIA_LOOKUP_CODE,
ADDRESS_LINE1, ADDRESS_LINE2,
ADDRESS_LINE3, ADDRESS_LINE4, CITY,
COUNTY, STATE, ZIP, COUNTRY, PROVINCE, AREA_CODE, PHONE,
TELEX, FAX_AREA_CODE, FAX, LANGUAGE, INACTIVE_DATE,
PURCHASING_SITE_FLAG, PAY_SITE_FLAG, RFQ_ONLY_SITE_FLAG,
FOB_LOOKUP_CODE, FREIGHT_TERMS_LOOKUP_CODE, DISTRIBUTION_SET_ID, DISTRIBUTION_SET_NAME,
ACCTS_PAY_CCID, PREPAY_CCID, ALWAYS_TAKE_DISC_FLAG,
ATTENTION_AR_FLAG, PAY_DATE_BASIS_LOOKUP_CODE, PAY_GROUP_LOOKUP_CODE,
HOLD_FUTURE_PAYMENTS_FLAG, HOLD_ALL_PAYMENTS_FLAG,
HOLD_REASON, TERMS_DATE_BASIS, TAX_REPORTING_SITE_FLAG,
TERMS_ID, TERMS_NAME, EXCLUDE_FREIGHT_FROM_DISC,
HOLD_UNMATCHED_INV_FLAG, INVOICE_AMOUNT_LIMIT, CUSTOMER_NUM,
PAYMENT_CURRENCY_CODE, PAYMENT_PRIORITY, INVOICE_CURRENCY_CODE,
AWT_GROUP_ID, AWT_GROUP_NAME, ALLOW_AWT_FLAG,
VALIDATION_NUMBER, CHECK_DIGITS, ADDRESS_STYLE, PAY_ON_CODE,
DEFAULT_PAY_SITE_ID, PAY_ON_RECEIPT_SUMMARY_CODE, VENDOR_SITE_CODE_ALT, ADDRESS_LINES_ALT,
BANK_CHARGE_BEARER, TP_HEADER_ID, GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, ECE_TP_LOCATION_CODE,
PCARD_SITE_FLAG, MATCH_OPTION, FUTURE_DATED_PAYMENT_CCID,
CREATE_DEBIT_MEMO_FLAG, COUNTRY_OF_ORIGIN_CODE, SUPPLIER_NOTIF_METHOD, EMAIL_ADDRESS,
PRIMARY_PAY_SITE_FLAG, ORG_ID
INTO
x.VENDOR_SITE_ID, x.VENDOR_ID, x.VENDOR_SITE_CODE,
x.ATTRIBUTE_CATEGORY, x.ATTRIBUTE1, x.ATTRIBUTE2,
x.ATTRIBUTE3, x.ATTRIBUTE4, x.ATTRIBUTE5,
x.ATTRIBUTE6, x.ATTRIBUTE7, x.ATTRIBUTE8,
x.ATTRIBUTE9, x.ATTRIBUTE10, x.ATTRIBUTE11,
x.ATTRIBUTE12, x.ATTRIBUTE13, x.ATTRIBUTE14,
x.ATTRIBUTE15, x.SHIP_TO_LOCATION_ID, x.SHIP_TO_LOCATION_CODE,
x.BILL_TO_LOCATION_ID, x.BILL_TO_LOCATION_CODE, x.SHIP_VIA_LOOKUP_CODE,
x.ADDRESS_LINE1, x.ADDRESS_LINE2,
x.ADDRESS_LINE3, x.ADDRESS_LINE4, x.CITY,
x.COUNTY, x.STATE, x.ZIP, x.COUNTRY, x.PROVINCE, x.AREA_CODE, x.PHONE,
x.TELEX, x.FAX_AREA_CODE, x.FAX, x.LANGUAGE, x.INACTIVE_DATE,
x.PURCHASING_SITE_FLAG, x.PAY_SITE_FLAG, x.RFQ_ONLY_SITE_FLAG,
x.FOB_LOOKUP_CODE, x.FREIGHT_TERMS_LOOKUP_CODE,x.DISTRIBUTION_SET_ID, x.DISTRIBUTION_SET_NAME,
x.ACCTS_PAY_CODE_COMBINATION_ID, x.PREPAY_CODE_COMBINATION_ID, x.ALWAYS_TAKE_DISC_FLAG,
x.ATTENTION_AR_FLAG, x.PAY_DATE_BASIS_LOOKUP_CODE, x.PAY_GROUP_LOOKUP_CODE,
x.HOLD_FUTURE_PAYMENTS_FLAG, x.HOLD_ALL_PAYMENTS_FLAG,
x.HOLD_REASON, x.TERMS_DATE_BASIS, x.TAX_REPORTING_SITE_FLAG,
x.TERMS_ID, x.TERMS_NAME, x.EXCLUDE_FREIGHT_FROM_DISCOUNT,
x.HOLD_UNMATCHED_INVOICES_FLAG, x.INVOICE_AMOUNT_LIMIT, x.CUSTOMER_NUM,
x.PAYMENT_CURRENCY_CODE, x.PAYMENT_PRIORITY, x.INVOICE_CURRENCY_CODE,
x.AWT_GROUP_ID, x.AWT_GROUP_NAME, x.ALLOW_AWT_FLAG,
x.VALIDATION_NUMBER, x.CHECK_DIGITS, x.ADDRESS_STYLE, x.PAY_ON_CODE,
x.DEFAULT_PAY_SITE_ID, x.PAY_ON_RECEIPT_SUMMARY_CODE, x.VENDOR_SITE_CODE_ALT, x.ADDRESS_LINES_ALT,
x.BANK_CHARGE_BEARER, x.TP_HEADER_ID, x.GLOBAL_ATTRIBUTE_CATEGORY,
x.GLOBAL_ATTRIBUTE1, x.GLOBAL_ATTRIBUTE2, x.GLOBAL_ATTRIBUTE3,
x.GLOBAL_ATTRIBUTE4, x.GLOBAL_ATTRIBUTE5, x.GLOBAL_ATTRIBUTE6,
x.GLOBAL_ATTRIBUTE7, x.GLOBAL_ATTRIBUTE8, x.GLOBAL_ATTRIBUTE9,
x.GLOBAL_ATTRIBUTE10, x.GLOBAL_ATTRIBUTE11, x.GLOBAL_ATTRIBUTE12,
x.GLOBAL_ATTRIBUTE13, x.GLOBAL_ATTRIBUTE14, x.GLOBAL_ATTRIBUTE15,
x.GLOBAL_ATTRIBUTE16, x.GLOBAL_ATTRIBUTE17, x.GLOBAL_ATTRIBUTE18,
x.GLOBAL_ATTRIBUTE19, x.GLOBAL_ATTRIBUTE20, x.ECE_TP_LOCATION_CODE,
x.PCARD_SITE_FLAG, x.MATCH_OPTION, x.FUTURE_DATED_PAYMENT_CCID,
x.CREATE_DEBIT_MEMO_FLAG, x.COUNTRY_OF_ORIGIN_CODE, x.SUPPLIER_NOTIF_METHOD, x.EMAIL_ADDRESS,
x.PRIMARY_PAY_SITE_FLAG,x.ORG_ID
FROM AP_VENDOR_SITES_V
WHERE vendor_id = p_vendor_id
and vendor_site_id = p_vendorsite_id;
SELECT
VENDOR_ID, VENDOR_NAME, VENDOR_NAME_ALT, SUMMARY_FLAG, ENABLED_FLAG, EMPLOYEE_ID,
VENDOR_TYPE_LOOKUP_CODE, CUSTOMER_NUM, ONE_TIME_FLAG, PARENT_VENDOR_ID, MIN_ORDER_AMOUNT,
TERMS_ID, SET_OF_BOOKS_ID, ALWAYS_TAKE_DISC_FLAG, PAY_DATE_BASIS_LOOKUP_CODE,
PAY_GROUP_LOOKUP_CODE, PAYMENT_PRIORITY, INVOICE_CURRENCY_CODE, PAYMENT_CURRENCY_CODE,
INVOICE_AMOUNT_LIMIT, HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_REASON,
TYPE_1099, WITHHOLDING_STATUS_LOOKUP_CODE, WITHHOLDING_START_DATE,
ORGANIZATION_TYPE_LOOKUP_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
MINORITY_GROUP_LOOKUP_CODE, WOMEN_OWNED_FLAG, SMALL_BUSINESS_FLAG, HOLD_FLAG,
PURCHASING_HOLD_REASON, HOLD_BY, HOLD_DATE, TERMS_DATE_BASIS, INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG, QTY_RCV_TOLERANCE, QTY_RCV_EXCEPTION_CODE,
ENFORCE_SHIP_TO_LOCATION_CODE, DAYS_EARLY_RECEIPT_ALLOWED, DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE, RECEIVING_ROUTING_ID, ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG, TAX_VERIFICATION_DATE,
NAME_CONTROL, STATE_REPORTABLE_FLAG, FEDERAL_REPORTABLE_FLAG, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, AUTO_CALCULATE_INTEREST_FLAG,
VALIDATION_NUMBER, EXCLUDE_FREIGHT_FROM_DISCOUNT, TAX_REPORTING_NAME, CHECK_DIGITS,
ALLOW_AWT_FLAG, AWT_GROUP_ID, AWT_GROUP_NAME, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, BANK_CHARGE_BEARER,
MATCH_OPTION, CREATE_DEBIT_MEMO_FLAG, TERMS_NAME, NI_NUMBER
INTO
x.VENDOR_ID, x.VENDOR_NAME, x.VENDOR_NAME_ALT, x.SUMMARY_FLAG, x.ENABLED_FLAG, x.EMPLOYEE_ID,
x.VENDOR_TYPE_LOOKUP_CODE, x.CUSTOMER_NUM, x.ONE_TIME_FLAG, x.PARENT_VENDOR_ID, x.MIN_ORDER_AMOUNT,
x.TERMS_ID, x.SET_OF_BOOKS_ID, x.ALWAYS_TAKE_DISC_FLAG, x.PAY_DATE_BASIS_LOOKUP_CODE,
x.PAY_GROUP_LOOKUP_CODE, x.PAYMENT_PRIORITY, x.INVOICE_CURRENCY_CODE, x.PAYMENT_CURRENCY_CODE,
x.INVOICE_AMOUNT_LIMIT, x.HOLD_ALL_PAYMENTS_FLAG, x.HOLD_FUTURE_PAYMENTS_FLAG, x.HOLD_REASON,
x.TYPE_1099, x.WITHHOLDING_STATUS_LOOKUP_CODE, x.WITHHOLDING_START_DATE,
x.ORGANIZATION_TYPE_LOOKUP_CODE, x.START_DATE_ACTIVE, x.END_DATE_ACTIVE,
x.MINORITY_GROUP_LOOKUP_CODE, x.WOMEN_OWNED_FLAG, x.SMALL_BUSINESS_FLAG, x.HOLD_FLAG,
x.PURCHASING_HOLD_REASON, x.HOLD_BY, x.HOLD_DATE, x.TERMS_DATE_BASIS, x.INSPECTION_REQUIRED_FLAG,
x.RECEIPT_REQUIRED_FLAG, x.QTY_RCV_TOLERANCE, x.QTY_RCV_EXCEPTION_CODE,
x.ENFORCE_SHIP_TO_LOCATION_CODE, x.DAYS_EARLY_RECEIPT_ALLOWED, x.DAYS_LATE_RECEIPT_ALLOWED,
x.RECEIPT_DAYS_EXCEPTION_CODE, x.RECEIVING_ROUTING_ID, x.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
x.ALLOW_UNORDERED_RECEIPTS_FLAG, x.HOLD_UNMATCHED_INVOICES_FLAG, x.TAX_VERIFICATION_DATE,
x.NAME_CONTROL, x.STATE_REPORTABLE_FLAG, x.FEDERAL_REPORTABLE_FLAG, x.ATTRIBUTE_CATEGORY,
x.ATTRIBUTE1, x.ATTRIBUTE2, x.ATTRIBUTE3, x.ATTRIBUTE4, x.ATTRIBUTE5, x.ATTRIBUTE6,
x.ATTRIBUTE7, x.ATTRIBUTE8, x.ATTRIBUTE9, x.ATTRIBUTE10, x.ATTRIBUTE11, x.ATTRIBUTE12,
x.ATTRIBUTE13, x.ATTRIBUTE14, x.ATTRIBUTE15, x.AUTO_CALCULATE_INTEREST_FLAG,
x.VALIDATION_NUMBER, x.EXCLUDE_FREIGHT_FROM_DISCOUNT, x.TAX_REPORTING_NAME, x.CHECK_DIGITS,
x.ALLOW_AWT_FLAG, x.AWT_GROUP_ID, x.AWT_GROUP_NAME, x.GLOBAL_ATTRIBUTE1, x.GLOBAL_ATTRIBUTE2,
x.GLOBAL_ATTRIBUTE3, x.GLOBAL_ATTRIBUTE4, x.GLOBAL_ATTRIBUTE5, x.GLOBAL_ATTRIBUTE6,
x.GLOBAL_ATTRIBUTE7, x.GLOBAL_ATTRIBUTE8, x.GLOBAL_ATTRIBUTE9, x.GLOBAL_ATTRIBUTE10,
x.GLOBAL_ATTRIBUTE11, x.GLOBAL_ATTRIBUTE12, x.GLOBAL_ATTRIBUTE13, x.GLOBAL_ATTRIBUTE14,
x.GLOBAL_ATTRIBUTE15, x.GLOBAL_ATTRIBUTE16, x.GLOBAL_ATTRIBUTE17, x.GLOBAL_ATTRIBUTE18,
x.GLOBAL_ATTRIBUTE19, x.GLOBAL_ATTRIBUTE20, x.GLOBAL_ATTRIBUTE_CATEGORY, x.BANK_CHARGE_BEARER,
x.MATCH_OPTION, x.CREATE_DEBIT_MEMO_FLAG, x.TERMS_NAME, x.NI_NUMBER
FROM ap_vendors_v
WHERE vendor_id = p_vendor_id ;
SELECT 1
INTO l_found
FROM ap_lookup_codes
WHERE upper(lookup_code) = upper(p_paymethod)
AND lookup_type = 'PAYMENT METHOD';
SELECT term_id
INTO l_var
FROM ap_terms
WHERE upper(name) = upper(p_termid);
select count(*)
into l_var
from ap_vendors_v
Where upper(vendor_name) = upper(p_name);
select count(*)
into l_var
from fnd_currencies
where currency_code = nvl(p_currency,'USD');
select count(*)
into l_var
from HR_ALL_ORGANIZATION_UNITS
where organization_id = p_org;
select count(*)
into l_var
from ap_vendors_v
where nvl(vendor_number,'@@') = nvl(to_char(p_partnerid),'@@')
and nvl(upper(p_name),'@@') <> nvl(Upper(vendor_name),'@@');
SELECT term_id
INTO l_term_id
FROM ap_terms
WHERE upper(name) = upper(p_termid);
SELECT vendor_number
INTO l_override_vendornum
FROM ap_vendors_v
WHERE vendor_id = r_vendor_rec.vendor_id;
g_action := 'Vendor record update';
SELECT vendor_id
INTO r_vendor_rec.vendor_id
FROM ap_vendors_v
WHERE vendor_name = p_name;
g_action := 'Vendor record update';
ap_vendor_pub_pkg.update_vendor(
p_api_version => '1.0',
x_return_status => l_ret_status,
x_msg_count => l_ret_count,
x_msg_data => l_ret_msg,
p_vendor_rec => r_vendor_rec,
p_vendor_id => r_vendor_rec.vendor_id
);
itg_debug_pub.add('Update vendor returns - ' || l_ret_status || ' - ' || l_ret_msg ,1);
SELECT PARTY_ID
into l_party_id
FROM AP_SUPPLIERS
WHERE VENDOR_ID=r_vendor_rec.vendor_id;
select object_version_number,
party_number,
duns_number_c
into l_party_object_version_number,
l_party_number,
l_duns_number
from hz_parties
where party_id=l_party_id;
HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => FND_API.G_FALSE,
p_organization_rec => l_org_rec,
p_party_object_version_number => l_party_object_version_number,
x_profile_id => l_party_id,
x_return_status => l_ret_status,
x_msg_count => l_ret_count,
x_msg_data => l_ret_msg
);
itg_debug_pub.add('Error occured in update Vendor or update organization',6);
itg_msg.apicallret('ap_vendor_pub_pkg.Update_vendor or hz_party_v2pub.update_organization returns-',l_ret_status,substr(l_ret_msg,1,200));
SELECT count(*) INTO l_var
FROM FND_TERRITORIES
WHERE TERRITORY_CODE = p_country
AND OBSOLETE_FLAG = 'N';
select count(*)
into l_var
from HR_ALL_ORGANIZATION_UNITS
where organization_id = p_org;
SELECT set_of_books_id
FROM org_organization_definitions
WHERE organization_id = p_org;
SELECT ship_to_location_id,
bill_to_location_id,
ship_via_lookup_code,
freight_terms_lookup_code,
fob_lookup_code,
accts_pay_code_combination_id,
prepay_code_combination_id
FROM financials_system_params_all
WHERE set_of_books_id = p_sob_id;
SELECT *
INTO l_ven_rec
FROM ap_vendors_v
WHERE vendor_id = p_vinfo_rec.vendor_id;
SELECT vendor_site_id
INTO l_vendor_site_id
FROM ap_supplier_sites
WHERE vendor_id = p_vinfo_rec.vendor_id
AND upper(vendor_site_code) = UPPER(p_site_code);
g_action := 'vendor site update';
g_action := 'Vendor-site info update';
itg_debug_pub.add('Calling ap_vendors_pub_pkg.update_vendor_site');
l_api_name := 'ap_vendor_pub_pkg.update_vendor_site';
ap_vendor_pub_pkg.update_vendor_site
(
p_api_version => '1.0',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_ret_msg,
p_vendor_site_rec => l_vendorsite_rec,
p_vendor_site_id => l_vendor_site_id
);
itg_debug_pub.add('Return from ap_vendors_pub_pkg.update_vendor_site');
/* Adding following block to Fix Bug: 5258874 to update supplier site address locations*/
BEGIN
select location_id into p_location_rec.location_id
from ap_supplier_sites
where vendor_id = p_vinfo_rec.vendor_id and vendor_site_id = l_vendor_site_id;
select object_version_number
into p_object_version_number
from hz_locations
where location_id = p_location_rec.location_id;
itg_debug_pub.add('Calling hz_location_v2pub.update_location ');
l_api_name := 'hz_location_v2pub.update_location';
hz_location_v2pub.update_location
(
p_init_msg_list => FND_API.G_FALSE,
p_location_rec => p_location_rec,
p_object_version_number => p_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_ret_msg
);
itg_debug_pub.add('Return from hz_location_v2pub.update_location');
itg_debug_pub.add('Create/Update_vendor_site/update_location API returns - ' || l_return_status || ' - ' || l_ret_msg);
SELECT vendor_site_id
INTO l_vsite_id
FROM ap_supplier_sites
WHERE UPPER(vendor_site_code) = UPPER(p_site_code)
AND vendor_id = p_vinfo_rec.vendor_id;
select h.party_id, h.object_version_number
into l_party_id,l_obj_ver_num
from HZ_PARTIES h, ap_supplier_contacts a
where a.per_party_id = h.party_id
and a.vendor_site_id = l_vsite_id
AND NVL(upper(person_first_name), '1') = NVL(upper(p_first_name), '1')
AND NVL(upper(person_middle_name), '1') = NVL(upper(p_middle_name), '1')
AND NVL(upper(person_last_name), '1') = NVL(upper(p_last_name), '1')
AND ROWNUM = 1;
g_action := 'Vendor-contact record update';
HZ_PARTY_V2PUB.update_person (
p_person_rec => l_per_rec,
p_party_object_version_number => l_obj_ver_num,
x_profile_id => l_profile_id,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
itg_debug_pub.Add('HZ_PARTY_V2PUB.update_person - ' || l_ret_status || ' - ' || l_msg_data ,1);
itg_msg.apicallret('HZ_PARTY_V2PUB.update_person',l_ret_status,l_msg_data);