[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
program_update_date DATE,
start_date_active DATE,
end_date_active DATE,
location_postal_code_id number
);
SELECT location_hierarchy_id
FROM jtf_loc_hierarchies_b
WHERE location_type_code = p_hier_rec.location_type_code
AND DECODE(p_hier_rec.location_type_code,
'AREA1', area1_id,
'AREA2', area2_id,
'COUNTRY', country_id,
'CREGION', country_region_id,
'STATE', state_id,
'SREGION', state_region_id,
'CITY', city_id,
'POSTAL_CODE', postal_code_id
) =
DECODE(p_hier_rec.location_type_code,
'AREA1', p_hier_rec.area1_id,
'AREA2', p_hier_rec.area2_id,
'COUNTRY', p_hier_rec.country_id,
'CREGION', p_hier_rec.country_region_id,
'STATE', p_hier_rec.state_id,
'SREGION', p_hier_rec.state_region_id,
'CITY', p_hier_rec.city_id,
'POSTAL_CODE', p_hier_rec.postal_code_id
);
SELECT jtf_loc_hierarchies_b_s.NEXTVAL
FROM DUAL;
SELECT count(*)
FROM jtf_loc_hierarchies_b
WHERE location_hierarchy_id = l_hier_id;
UPDATE jtf_loc_hierarchies_b
SET location_hierarchy_id = l_hier_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = object_version_number + 1,
request_id = p_area_rec.request_id,
program_application_id = p_area_rec.program_application_id,
program_id = p_area_rec.program_id,
program_update_date = p_area_rec.program_update_date,
created_by_application_id = 530,
start_date_active = p_area_rec.start_date_active,
end_date_active = p_area_rec.end_date_active,
location_type_code = p_hier_rec.location_type_code,
area1_id = p_hier_rec.area1_id,
area1_code = p_hier_rec.area1_code,
area2_id = p_hier_rec.area2_id,
area2_code = p_hier_rec.area2_code,
country_id = p_hier_rec.country_id,
country_code = p_hier_rec.country_code,
country_region_id = p_hier_rec.country_region_id,
country_region_code = p_hier_rec.country_region_code,
state_id = p_hier_rec.state_id,
state_code = p_hier_rec.state_code,
state_region_id = p_hier_rec.state_region_id,
state_region_code = p_hier_rec.state_region_code,
city_id = p_hier_rec.city_id,
city_code = p_hier_rec.city_code,
postal_code_id = p_hier_rec.postal_code_id
WHERE location_hierarchy_id = l_hier_id;
INSERT INTO jtf_loc_hierarchies_b(
location_hierarchy_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
request_id,
program_application_id,
program_id,
program_update_date,
created_by_application_id,
start_date_active,
end_date_active,
location_type_code,
area1_id,
area1_code,
area2_id,
area2_code,
country_id,
country_code,
country_region_id,
country_region_code,
state_id,
state_code,
state_region_id,
state_region_code,
city_id,
city_code,
postal_code_id
)
VALUES(
l_hier_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1,
p_area_rec.request_id,
p_area_rec.program_application_id,
p_area_rec.program_id,
p_area_rec.program_update_date,
530,
p_area_rec.start_date_active,
p_area_rec.end_date_active,
p_hier_rec.location_type_code,
p_hier_rec.area1_id,
p_hier_rec.area1_code,
p_hier_rec.area2_id,
p_hier_rec.area2_code,
p_hier_rec.country_id,
p_hier_rec.country_code,
p_hier_rec.country_region_id,
p_hier_rec.country_region_code,
p_hier_rec.state_id,
p_hier_rec.state_code,
p_hier_rec.state_region_id,
p_hier_rec.state_region_code,
p_hier_rec.city_id,
p_hier_rec.city_code,
p_hier_rec.postal_code_id
);
SELECT area.location_area_id,
area.location_area_code,
area.location_type_code,
area.parent_location_area_id,
area.request_id,
area.program_application_id,
area.program_id,
area.program_update_date,
area.start_date_active,
area.end_date_active,
postal.location_postal_code_id
FROM jtf_loc_areas_vl area, jtf_loc_postal_codes postal
WHERE area.location_area_id = postal.location_area_id (+);
SELECT location_area_id,
location_area_code,
location_type_code,
parent_location_area_id
FROM jtf_loc_areas_vl
WHERE location_area_id = l_parent_id;
SELECT location_postal_code_id
FROM jtf_loc_postal_codes
WHERE location_area_id = l_area_id;