[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_log_msg := 'Select sync attrributes from TCA for '|| x_party_id;
SELECT jgzz_fiscal_code,
substrb(tax_reference,1,20),
organization_name_phonetic,
substrb(party_name,1,240),
party_name,
tax_reference
INTO l_num_1099,
l_vat_registration_num,
l_vendor_name_alt,
l_vendor_name,
l_tca_vendor_name,
l_tca_vat_registration_num
FROM hz_parties
WHERE party_id = x_party_id;
l_log_msg := 'After Selecting Attributes for '|| x_party_id;
l_log_msg := 'Select to check if supplier exists for '|| x_party_id;
SELECT party_id,
num_1099,
tca_sync_num_1099
INTO l_check_party_id,
l_upgraded_num_1099,
l_check_num_1099
FROM ap_suppliers
WHERE party_id = x_party_id;
--not contractor individual.For contractor individuals,if we update the
--the jgzz_fisacl_code in tca,the same thing will not be reflected in
--num_1099 of the ap_suppliers.We store the TIN numbers of the contractors
--in the field individual_1099 of ap_suppliers and not in TCA.
UPDATE ap_suppliers
SET
--bug6691916.commented the below assignment statement and added
--the one below that.As per analysis,only organization type lookup
--code of individual or foreign individual are considered
/*num_1099 = decode(UPPER(vendor_type_lookup_code),'CONTRACTOR',
decode(UPPER(organization_type_lookup_code),
'INDIVIDUAL',NULL,
'FOREIGN INDIVIDUAL',NULL,
'PARTNERSHIP',NULL,
'FOREIGN PARTNERSHIP',NULL,
l_num_1099),
l_num_1099),*/
num_1099 = decode(vendor_type_lookup_code,
'EMPLOYEE', NULL, /* bug11067238 start */
decode(UPPER(organization_type_lookup_code),
'INDIVIDUAL',NULL,
'FOREIGN INDIVIDUAL',NULL,
l_num_1099
)
),
vat_registration_num = l_vat_registration_num,
vendor_name_alt = l_vendor_name_alt,
/* vendor_name = l_vendor_name), commented for Bug9328048 */
vendor_name = decode(vendor_type_lookup_code, 'EMPLOYEE',nvl(vendor_name,l_vendor_name),l_vendor_name), --Bug9328048
tca_sync_vendor_name = l_tca_vendor_name,
tca_sync_vat_reg_num = l_tca_vat_registration_num,
tca_sync_num_1099 = nvl(l_check_num_1099,
l_upgraded_num_1099)
WHERE party_id = x_party_id;
l_last_update_date ap_supplier_sites_all.last_update_date%type; -- B# 7646333
l_log_msg := 'Selecting Attributes for : '|| x_location_id;
SELECT hl.state,
hl.province,
hl.county,
hl.city,
hl.postal_code,
hl.country,
substrb(hl.city,1,60), --6708281
substrb(hl.postal_code,1,60), --6708281
substrb(hl.country,1,60), --6708281
hl.address_style,
fl.nls_language,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.address_lines_phonetic
,hl.last_update_date -- B# 7646333
INTO l_state,
l_province,
l_county,
l_tca_sync_city,
l_tca_sync_zip,
l_tca_sync_country,
l_city,
l_zip,
l_country,
l_address_style,
l_language,
l_address1,
l_address2,
l_address3,
l_address4,
l_address_line_alt
,l_last_update_date -- B# 7646333
FROM hz_locations hl,
fnd_languages fl
WHERE hl.language = fl.language_code (+)
AND hl.location_id = x_location_id;
SELECT vendor_site_id
INTO l_check_vendor_site_id
FROM ap_supplier_sites_all
WHERE location_id = x_location_id
AND vendor_site_id = x_vendor_site_id
AND rownum = 1;
SELECT vendor_site_id
INTO l_check_vendor_site_id
FROM ap_supplier_sites_all
WHERE location_id = x_location_id
AND rownum = 1;
l_log_msg := 'Update Supplier Sites Upgrade Cases: '
|| x_location_id;
UPDATE ap_supplier_sites_all
SET tca_sync_state = nvl(tca_sync_state,state),
tca_sync_county = nvl(tca_sync_county,county),
tca_sync_province = nvl(tca_sync_province,province)
WHERE location_id = x_location_id;
l_log_msg := 'Update Supplier Sites for : '|| x_location_id;
UPDATE ap_supplier_sites_all
SET state = l_state,
province = l_province,
county = l_county,
tca_sync_city = l_tca_sync_city,
tca_sync_zip = l_tca_sync_zip,
tca_sync_country = l_tca_sync_country,
city = l_city,
zip = l_zip,
country = l_country,
address_style = l_address_style,
language = l_language,
address_line1 = l_address1,
address_line2 = l_address2,
address_line3 = l_address3,
address_line4 = l_address4,
address_lines_alt = l_address_line_alt
WHERE location_id = x_location_id;
UPDATE ap_supplier_sites_all
SET last_update_date = SYSDATE -- B# 7646333
,LAST_UPDATED_BY = FND_GLOBAL.user_id -- B# 7646333
,last_update_login = FND_GLOBAL.LOGIN_ID -- B# 7646333
WHERE location_id = x_location_id
AND vendor_site_id = l_check_vendor_site_id;
UPDATE ap_supplier_sites_all
SET last_update_date = SYSDATE -- B# 7646333
,LAST_UPDATED_BY = FND_GLOBAL.user_id -- B# 7646333
,last_update_login = FND_GLOBAL.LOGIN_ID -- B# 7646333
WHERE location_id = x_location_id;
l_log_msg := 'After Update of Site Attributes for : '
|| x_location_id;
l_log_msg := 'Selecting Attributes for : '|| x_party_site_id;
SELECT duns_number_c
INTO l_duns_number
FROM hz_party_sites
WHERE party_site_id = x_party_site_id;
l_log_msg := 'Update Supplier Sites for : '|| x_party_site_id;
UPDATE ap_supplier_sites_all
SET duns_number = l_duns_number
WHERE party_site_id = x_party_site_id
AND EXISTS (SELECT 'Site Exists'
FROM ap_supplier_sites_all a
WHERE a.party_site_id = x_party_site_id);
l_log_msg := 'After Update of Site Attributes for : '|| x_party_site_id;