[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' SELECT p.party_id
, s.party_site_id
, l.location_id
, SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
, l.address1 street
, l.postal_code
, l.city
, l.state
, l.country
, t.territory_short_name
, l.geometry
, s.start_date_active
, s.end_date_active
FROM jtf_rs_resource_extns_vl r
, per_people_f pf
, hz_parties p
, hz_party_sites s
, hz_locations l
, fnd_territories_vl t
WHERE r.resource_id = :resource_id
AND pf.person_id = r.source_id
AND pf.party_id = p.party_id (+)
AND p.party_id = s.party_id (+)
AND NVL(s.status, ''A'') = ''A''
AND s.location_id = l.location_id (+)
AND l.country = t.territory_code(+)
ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
' SELECT r.source_id party_id
, s.party_site_id
, l.location_id
, SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
, l.address1 street
, l.postal_code
, l.city
, l.state
, l.country
, t.territory_short_name
, l.geometry
, s.start_date_active
, s.end_date_active
FROM jtf_rs_resource_extns_vl r
, hz_party_sites s
, hz_locations l
, fnd_territories_vl t
WHERE r.resource_id = :resource_id
AND r.source_id = s.party_id (+)
AND NVL(s.status, ''A'') = ''A''
AND s.location_id = l.location_id (+)
AND l.country = t.territory_code(+)
ORDER BY s.party_site_id NULLS LAST, s.last_update_date DESC';
' SELECT p.party_id
, s.party_site_id
, l.location_id
, SUBSTR(l.short_description, INSTR(l.short_description, '' '', -1) + 1) address_id
, l.address1 street
, l.postal_code
, l.city
, l.state
, l.country
, t.territory_short_name
, l.geometry
, s.start_date_active
, s.end_date_active
FROM hz_parties p
, hz_party_sites s
, hz_locations l
, fnd_territories_vl t
WHERE p.person_last_name = :res_type_id_string
AND p.person_first_name = :dep_arr_party_name
AND s.party_id = p.party_id
AND l.location_id = s.location_id
AND l.country = t.territory_code
ORDER BY s.last_update_date DESC';
select hp.party_id
, hps.party_site_id
, hzl.location_id
, SUBSTR(hzl.short_description, INSTR(hzl.short_description, '' '',-1) + 1) address_id
, hzl.address1 street
, hzl.postal_code
, hzl.city
, hzl.state
, hzl.country
, t.territory_short_name
, hzl.geometry
, hps.start_date_active
, hps.end_date_active
from hz_locations hzl,
hz_party_sites hps,
hz_cust_acct_sites hzacs,
hz_cust_site_uses hzacus,
hz_parties hp,
hz_cust_accounts hzca,
csp_rs_cust_relations ccr
,fnd_territories_vl t
where hzl.location_id =hps.location_id
and hzl.country = t.territory_code(+)
and hps.party_id=hp.party_id
and hzacs.party_site_id=hps.party_site_id
and hzacs.cust_acct_site_id= hzacus.cust_acct_site_id
and hzacus.site_use_code = ''SHIP_TO''
and hzacus.PRIMARY_FLAG=''Y''
and hp.party_id=hzca.party_id
and hzca.cust_account_id=ccr.customer_id
and ccr.resource_id=:resource_id
ORDER BY hps.party_site_id NULLS LAST, hps.last_update_date DESC';
PROCEDURE update_location (
p_location_rec IN hz_location_v2pub.LOCATION_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
pragma autonomous_transaction;
hz_location_v2pub.update_location(
p_location_rec => p_location_rec
, p_object_version_number => p_object_version_number
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
, p_update_address IN VARCHAR2 DEFAULT 'F'
, x_geometry OUT NOCOPY mdsys.sdo_geometry
) IS
l_api_name CONSTANT VARCHAR2(50) := 'RESOLVE_ADDRESS';
l_update_addr BOOLEAN;
l_update_geo BOOLEAN := FALSE;
SELECT object_version_number, geometry, geometry_status_code
FROM HZ_LOCATIONS
WHERE LOCATION_ID = p_location_id;
debug(' --> Update Addr = ' || p_update_address, l_api_name, fnd_log.level_statement);
l_update_addr := NVL(fnd_api.to_boolean(p_update_address), FALSE);
l_update_geo := TRUE;
l_update_addr:= TRUE;
IF l_update_addr THEN
IF p_address1 = l_roadname THEN
l_location_rec.address1 := l_road;
l_update_geo := TRUE;
IF l_update_geo THEN
IF l_debug THEN
debug('LF is installed..Assigning resolved geometry for updation', l_api_name, fnd_log.level_procedure);
debug('Geometry should not be updated..Need to retain existing geometry', l_api_name, fnd_log.level_procedure);
IF l_update_addr OR l_update_geo THEN
IF l_debug THEN
debug('Updating Address ', l_api_name, fnd_log.level_statement);
update_location(
p_location_rec => l_location_rec
, p_object_version_number => l_location_ovn
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => l_msg_data );
SELECT a.address_id
, a.address_line1 street
, a.postal_code
, a.town_or_city city
, a.region_2 state
, a.country
, t.territory_short_name
, a.date_from start_date_active
, a.date_to end_date_active
FROM per_addresses a
, jtf_rs_resource_extns r
, fnd_territories_vl t
WHERE r.resource_id = b_resource_id
AND a.person_id = r.source_id
AND a.country = t.territory_code
AND TRUNC(a.date_from) <= TRUNC(b_date)
AND TRUNC(NVL(a.date_to, b_date + 1)) >= TRUNC(b_date)
ORDER BY a.primary_flag DESC, a.date_from DESC;
SELECT territory_code FROM fnd_territories WHERE ROWNUM = 1;
SELECT hz_party_number_s.NEXTVAL INTO l_person_rec.party_rec.party_number
FROM dual;
SELECT hz_party_site_number_s.NEXTVAL INTO l_party_site_rec.party_site_number
FROM dual;
, p_update_address => l_change_address
, x_geometry => x_address_rec.geometry
);
SELECT territory_code FROM fnd_territories WHERE ROWNUM = 1;
SELECT hz_party_number_s.NEXTVAL INTO l_person_rec.party_rec.party_number
FROM dual;
SELECT hz_party_site_number_s.NEXTVAL INTO l_party_site_rec.party_site_number
FROM dual;
select hl.location_id,
hps.party_site_id,
hps.party_id,
hl.address1,
hl.postal_code,
hl.city,
hl.state,
hl.country,
t.territory_short_name,
HPS.START_DATE_ACTIVE,
HPS.END_DATE_ACTIVE
FROM csp_rs_cust_relations csc
, hz_locations hl
, fnd_territories_vl t
, hz_party_sites hps
WHERE csc.resource_id=p_resource_id
AND csc.resource_type = p_resource_type
AND csc.default_trip_start = hl.location_id(+)
AND hl.country = t.territory_code(+)
AND hps.location_id = HL.location_id
AND csc.default_trip_start is not null;