[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor verify_imp_adapter is select count(ADAPTER_CONTENT_SOURCE)
from hz_imp_adapters
where batch_id = p_batch_id
and send_flag = 'Y';
cursor imp_adapter_cur is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
DEFAULT_REPLACE_STATUS_LEVEL
from hz_adapters
where enabled_flag = 'Y'
and adapter_content_source in
(select distinct ADAPTER_CONTENT_SOURCE
from hz_imp_adapters
where batch_id = p_batch_id
and send_flag = 'Y') ;
cursor imp_addresss_cur(v_adapter varchar2) is select rowid
from hz_imp_addresses_int
where batch_id = p_batch_id
and country in
(select country_code from hz_imp_adapters
where adapter_content_source = v_adapter
and batch_id = p_batch_id
and send_flag = 'Y');
not exists (select 'X' from fnd_territories
where TERRITORY_CODE = country)
);
(select territory_code from hz_adapter_territories
where adapter_id = v_adapter_id
and default_flag = 'Y');*/
cursor adapter_cur is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
DEFAULT_REPLACE_STATUS_LEVEL
from hz_adapters
where enabled_flag = 'Y'
and adapter_id in
(select distinct HZ_LOCATION_SERVICES_PUB.get_adapter_id(null,country)
from hz_imp_addresses_int
where batch_id = p_batch_id
--Bug No:3347996.Added conditions to overcome unnecessary calls to
--HZ_LOCATION_SERVICES_PUB.get_adapter_id.
and country in (select distinct territory_code
from hz_adapter_territories t,
hz_adapters ad
where ad.adapter_id =t.adapter_id
and ad.enabled_flag='Y'
and t.enabled_flag='Y'
and t.default_flag = 'Y')
and exists (select 'X' from fnd_territories
where TERRITORY_CODE = country)
----End of Bug No:3347996
);
cursor addresss_cur(v_adapter_id number) is select rowid
from hz_imp_addresses_int
where batch_id = p_batch_id
and country in
(select territory_code from hz_adapter_territories t,
hz_adapters ad
where t.adapter_id = v_adapter_id
and ad.adapter_id =t.adapter_id
and ad.enabled_flag='Y'
and t.enabled_flag='Y' --Bug No:3347996
and t.default_flag = 'Y')
and exists (select 'X' from fnd_territories
where TERRITORY_CODE = country);
cursor addresss_cur_default is select rowid
from hz_imp_addresses_int
where batch_id = p_batch_id
--Bug No:3347996
and country not in ((select distinct territory_code
from hz_adapter_territories t,
hz_adapters ad
where ad.adapter_id=t.adapter_id
and ad.enabled_flag='Y'
and t.enabled_flag='Y'
and t.default_flag = 'Y'
)UNION
(select distinct country_code
from hz_imp_adapters
where batch_id = p_batch_id
and send_flag= 'Y')
);
--Bug No:3347996--and not exists (select 'X' from fnd_territories
--Bug No:3347996 where TERRITORY_CODE = country);
cursor default_adapter is select adapter_id,adapter_content_source,nvl(MAXIMUM_BATCH_SIZE,DEFAULT_BATCH_SIZE),
DEFAULT_REPLACE_STATUS_LEVEL
from hz_adapters
where enabled_flag = 'Y'
and adapter_id = to_number(fnd_profile.value('HZ_DEFAULT_LOC_ADAPTER'));
select country,count(decode(ACCEPT_STANDARDIZED_FLAG,'Y','Y',null))Validated_rec,
count(decode(ACCEPT_STANDARDIZED_FLAG,'N','N',null))Failed_rec,
count(ACCEPT_STANDARDIZED_FLAG)Total_rec
from hz_imp_addresses_int
where batch_id = p_batch_id
and ACCEPT_STANDARDIZED_FLAG is not null
group by country;
cursor find_imp_country(p_country_code varchar2) is select 'X' from hz_imp_adapters
where batch_id = p_batch_id
and country_code = p_country_code;
update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
where batch_id = l_batch_id;
update hz_imp_adapters
set RECORDS_PASSED_VALIDATION = rec.Validated_rec,
RECORDS_FAILED_VALIDATION = rec.Failed_rec,
TOTAL_RECORDS_VALIDATED = rec.Total_rec
where batch_id = p_batch_id
and COUNTRY_CODE = rec.country;
insert into hz_imp_adapters
(batch_id,country_code,RECORDS_PASSED_VALIDATION,
RECORDS_FAILED_VALIDATION,TOTAL_RECORDS_VALIDATED,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE)
values(p_batch_id,rec.country,rec.Validated_rec,
rec.Failed_rec,rec.Total_rec,
-1,sysdate,-1,sysdate);
update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'COMPLETED'
where batch_id = l_batch_id;
update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'PROCESSING'
where batch_id = l_batch_id returning ORIGINAL_SYSTEM into l_orig_system;
update hz_imp_addresses_int
set validation_subset_id = l_counter,
adapter_content_source = l_adapter_content_source(i)
where rowid = l_row_id(j);
update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
where batch_id = l_batch_id;
update HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
where batch_id = l_batch_id
returning ORIGINAL_SYSTEM into l_orig_system;
UPDATE hz_imp_addresses_int
SET validation_subset_id = l_counter,
adapter_content_source = l_adapter_content_source(i)
WHERE rowid = l_row_id(j);
UPDATE HZ_IMP_BATCH_SUMMARY set ADDR_VAL_STATUS = 'ERROR'
WHERE batch_id = l_batch_id;
Procedure update_validated_address(
p_SITE_ORIG_SYSTEM_REFERENCE in VARCHAR2 ,
p_SITE_ORIG_SYSTEM in VARCHAR2 ,
p_batch_id in NUMBER,
p_Address1 in VARCHAR2 DEFAULT NULL,
p_Address2 in VARCHAR2 DEFAULT NULL,
p_Address3 in VARCHAR2 DEFAULT NULL,
p_Address4 in VARCHAR2 DEFAULT NULL,
p_city in VARCHAR2 DEFAULT NULL,
p_county in VARCHAR2 DEFAULT NULL,
p_CountrySubEntity in VARCHAR2 DEFAULT NULL,
p_country in VARCHAR2 DEFAULT NULL,
p_postal_code in VARCHAR2 DEFAULT NULL,
p_status in VARCHAR2 DEFAULT NULL,
P_OVERWRITE_THRESHOLD in VARCHAR2 DEFAULT NULL )is
begin
UPDATE HZ_IMP_ADDRESSES_INT SET
Address1_std = p_Address1,
Address2_std = p_Address2,
Address3_std = p_Address3,
Address4_std = p_Address4,
city_std = p_city,
county_std = p_county,
PROV_STATE_ADMIN_CODE_STD = p_CountrySubEntity,
country_std = nvl(upper(p_country), upper(country)),
postal_code_std = p_postal_code,
ACCEPT_STANDARDIZED_FLAG = compare_treshhold(p_status,P_OVERWRITE_THRESHOLD),
DATE_VALIDATED = trunc(sysdate),
ADDR_VALID_STATUS_CODE = p_status
WHERE SITE_ORIG_SYSTEM_REFERENCE = P_SITE_ORIG_SYSTEM_REFERENCE
AND SITE_ORIG_SYSTEM = P_SITE_ORIG_SYSTEM
AND BATCH_ID = p_batch_id;
FND_FILE.put_line(fnd_file.log,'Can not find record for update HZ_IMP_ADDRESSES_INT: ');
FND_FILE.put_line(fnd_file.log,'update_validated_address: Aborting processing inboundxml for this batch');
l_parameter_list.DELETE;