[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_create_or_update IN VARCHAR2,
px_rowid IN OUT NOCOPY ROWID,
px_adapter_log_id IN OUT NOCOPY NUMBER,
p_created_by_module IN VARCHAR2,
p_created_by_module_id IN NUMBER,
p_http_status_code IN VARCHAR2,
p_request_id IN NUMBER,
p_object_version_number IN NUMBER,
p_inout_doc IN CLOB );
SELECT username, encrypted_password
FROM HZ_ADAPTERS
WHERE adapter_id = l_adapter_id;
p_last_update_date_op IN VARCHAR2,
p_last_update_date IN VARCHAR2,
p_country IN VARCHAR2,
p_adapter_content_source IN VARCHAR2,
p_overwrite_threshold IN VARCHAR2 )
IS
l_return_status VARCHAR2(30);
SELECT max(to_number(lookup_code)), min(to_number(lookup_code))
FROM ar_lookups
WHERE lookup_type = 'HZ_ADDR_VAL_STATUS'
AND enabled_flag = 'Y';
SELECT adapter_id
FROM hz_adapters
WHERE ltrim(rtrim(adapter_content_source)) = ltrim(rtrim(l_adapter_content_source));
SELECT maximum_batch_size
FROM hz_adapters
WHERE adapter_id = l_adapter_id;
SELECT to_char(min(creation_date), 'DD-MON-YYYY')
FROM hz_locations;
SELECT COUNT(1)
FROM FND_CONCURRENT_REQUESTS
WHERE priority_request_id = l_request_id
AND request_id <> l_request_id
AND phase_code = 'C'
AND status_code = 'E';
log('p_last_update_date_op :'||p_last_update_date_op);
log('p_last_update_date :'||p_last_update_date);
IF(p_last_update_date IS NOT NULL) THEN
l_in_lud := to_char(fnd_date.canonical_to_date(p_last_update_date),'DD-MON-YYYY');
IF(p_last_update_date_op = '>') THEN
l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date)+1,'DD-MON-YYYY');
ELSIF(p_last_update_date_op = '<') THEN
l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date)-1,'DD-MON-YYYY');
l_tmp_lud := to_char(fnd_date.canonical_to_date(p_last_update_date),'DD-MON-YYYY');
log('temp last_update_date: '||l_tmp_lud);
l_where_clause := 'select count(1) from HZ_LOCATIONS '||
'where actual_content_source = ''USER_ENTERED'' and nvl(do_not_validate_flag,''N'') = ''N''';
IF((p_last_update_date_op IS NOT NULL) AND (p_last_update_date IS NOT NULL)) THEN
l_where_clause := l_where_clause||' and trunc(last_update_date) '
||p_last_update_date_op
||' to_date(:p_last_upd_date,''DD-MON-YYYY'')';
p_op => p_last_update_date_op,
--p_in => p_last_update_date,
p_in => l_tmp_lud,
p_nvl_out => l_nvl_lud,
p_from_out => l_from_lud,
p_to_out => l_to_lud );
l_parameter_list.DELETE;
l_allow_update_std VARCHAR2(1);
select max(to_number(lookup_code))
from AR_LOOKUPS
where lookup_type = 'HZ_ADDR_VAL_STATUS';
select object_version_number, state, province, country,
county, postal_code, city, rowid
from HZ_LOCATIONS
where location_id = l_location_id;
select adapter_content_source
from HZ_ADAPTERS
where adapter_id = l_adapter_id;
select 'X'
from HZ_LOCATION_PROFILES
where sysdate between effective_start_date and nvl(effective_end_date, sysdate)
and actual_content_source = l_adapter_content_source
and location_id = l_location_id;
SELECT 'X'
FROM hz_locations
WHERE location_id = l_location_id
AND date_validated IS NOT NULL
AND validation_status_code IS NOT NULL;
SELECT hps.party_id
FROM hz_party_sites hps
WHERE hps.location_id = l_location_id
AND hps.identifying_address_flag = 'Y';
log('Expected error at update_location_profile. Mandatory column checking failed.');
log('Unexpected error at update_location_profile. Mandatory column checking failed.');
p_create_update_flag => 'U',
x_return_status => l_return_status );
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
log('Expected error at update_location_profile');
log('Unexpected error at update_location_profile');
log('Location profile updated.');
l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
IF((l_allow_update_std = 'N') AND (l_dummy IS NOT NULL)) THEN
l_validation_sst_flag := 'N';
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
log('Expected error at update_location_profile. Rollback changes.');
log('Unexpected error at update_location_profile. Rollback changes.');
log('Update location.');
UPDATE hz_locations
SET address1 = l_location_rec.address1,
address2 = l_location_rec.address2,
address3 = l_location_rec.address3,
address4 = l_location_rec.address4,
city = l_location_rec.city,
country = l_location_rec.country,
county = l_location_rec.county,
state = l_location_rec.state,
province = l_location_rec.province,
postal_code = l_location_rec.postal_code,
address_key = l_key,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
validation_status_code = l_location_profile_rec.validation_status_code,
date_validated = l_location_profile_rec.date_validated,
object_version_number = nvl(object_version_number,1)+1
WHERE location_id = l_location_rec.location_id;
UPDATE hz_cust_acct_sites_all cas
SET cas.address_text = null
WHERE cas.address_text IS NOT NULL
AND EXISTS
( SELECT 1
FROM HZ_PARTY_SITES ps
WHERE ps.location_id = l_location_rec.location_id
AND cas.party_site_id = ps.party_site_id );
SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE party_id = l_party_id
FOR UPDATE NOWAIT;
UPDATE HZ_PARTIES
SET country = l_location_rec.country,
address1 = l_location_rec.address1,
address2 = l_location_rec.address2,
address3 = l_location_rec.address3,
address4 = l_location_rec.address4,
city = l_location_rec.city,
county = l_location_rec.county,
postal_code = l_location_rec.postal_code,
state = l_location_rec.state,
province = l_location_rec.province,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE party_id = l_party_id;
log('Cannot update party due to record change.');
log('Error happens at update location. Rollback changes.');
END IF; -- check allow update standardized address
END IF; -- update location profiles
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
select distinct org_id
from hz_loc_assignments
where location_id = l_loc_id;
select 1
from hz_geo_name_reference_log
where location_table_name = 'HZ_LOCATIONS'
and location_id = l_loc_id
and rownum = 1;
SELECT 'Y'
INTO l_loc_assignment_exist
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM HZ_LOC_ASSIGNMENTS la
WHERE la.location_id = l_location_rec.location_id );
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM HZ_PARTY_SITES PS
WHERE PS.LOCATION_ID = l_location_rec.location_id
AND PS.PARTY_ID = -1);
SELECT default_country
INTO l_default_country
FROM ar_system_parameters_all
WHERE org_id = l_org_id;
select to_nclob(out_doc)
from HZ_ADAPTER_LOGS
where adapter_log_id = l_adapter_log_id;
p_create_or_update => 'C',
px_rowid => l_rowid,
px_adapter_log_id => l_adapter_log_id,
p_created_by_module => p_module,
p_created_by_module_id => p_module_id,
p_http_status_code => NULL,
p_request_id => hz_utility_v2pub.request_id,
p_object_version_number => 1,
p_inout_doc => to_clob(p_addrval_doc) );
SELECT rowid INTO l_rowid
FROM HZ_ADAPTER_LOGS
WHERE adapter_log_id = l_adapter_log_id;
p_create_or_update => 'U',
px_rowid => l_rowid,
px_adapter_log_id => l_adapter_log_id,
p_created_by_module => NULL,
p_created_by_module_id => NULL,
p_http_status_code => l_resp_status,
p_request_id => NULL,
p_object_version_number => 2,
p_inout_doc => in_xml );
select la.adapter_id
from HZ_ADAPTERS la, HZ_ADAPTER_TERRITORIES t
where la.adapter_id = t.adapter_id
and t.territory_code = l_country_code
and t.enabled_flag = 'Y'
and t.default_flag = 'Y'
and la.enabled_flag = 'Y'
and rownum = 1;
select 'X'
from HZ_ADAPTERS
where adapter_id = l_adapter_id
and enabled_flag = 'Y';
p_create_or_update IN VARCHAR2,
px_rowid IN OUT NOCOPY ROWID,
px_adapter_log_id IN OUT NOCOPY NUMBER,
p_created_by_module IN VARCHAR2,
p_created_by_module_id IN NUMBER,
p_http_status_code IN VARCHAR2,
p_request_id IN NUMBER,
p_object_version_number IN NUMBER,
p_inout_doc IN CLOB ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
IF(p_create_or_update = 'C') THEN
HZ_ADAPTER_LOGS_PKG.Insert_Row(
x_adapter_log_id => px_adapter_log_id,
x_created_by_module => p_created_by_module,
x_created_by_module_id => p_created_by_module_id,
x_http_status_code => p_http_status_code,
x_request_id => p_request_id,
x_object_version_number => p_object_version_number );
UPDATE HZ_ADAPTER_LOGS
SET out_doc = p_inout_doc
WHERE adapter_log_id = px_adapter_log_id;
HZ_ADAPTER_LOGS_PKG.Update_Row(
x_rowid => px_rowid,
x_adapter_log_id => px_adapter_log_id,
x_created_by_module => NULL,
x_created_by_module_id => NULL,
x_http_status_code => p_http_status_code,
x_request_id => NULL,
x_OBJECT_VERSION_NUMBER => p_object_version_number );
UPDATE HZ_ADAPTER_LOGS
SET in_doc = p_inout_doc
WHERE adapter_log_id = px_adapter_log_id;
SELECT host_address
INTO l_adapter_url
FROM HZ_ADAPTERS
WHERE ADAPTER_ID = p_adapter_id;
l_parameter_list.DELETE;