[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
FROM csf_gps_vendors_b WHERE enabled = 'Y';
SELECT device_tag
FROM csf_gps_devices
WHERE device_id = p_device_id;
SELECT device_tag
FROM csf_gps_device_assignments a
, csf_gps_devices d
WHERE a.resource_id = p_resource_id
AND a.resource_type = p_resource_type
AND NVL(p_date, SYSDATE) BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE + 1);
SELECT vendor_name
FROM csf_gps_vendors_vl
WHERE vendor_id = p_vendor_id;
SELECT NVL(meaning, 'UNKNOWN')
FROM fnd_lookups
WHERE lookup_type = 'CSF_GPS_DEVICE_STATUSES'
AND lookup_code = 'UNKNOWN';
SELECT l.vendor_feed_time
, l.status
, trunc(l.latitude, 3)
, trunc(l.longitude, 3)
, l.speed
, l.direction
, l.parked_time
, l.address
, d.device_tag
, (
SELECT NVL(fl.meaning, stmap.internal_status_code) FROM fnd_lookups fl, csf_gps_vendor_status_maps stmap
WHERE fl.lookup_type = 'CSF_GPS_DEVICE_STATUSES' AND fl.lookup_code = stmap.internal_status_code AND stmap.vendor_status_code = l.status
) status_code_meaning
, l.creation_date
FROM csf_gps_location_feeds l
, csf_gps_devices d
WHERE d.device_id = p_device_id
AND l.device_id = d.device_id
-- AND (
-- p_date IS NULL AND l.creation_date > SYSDATE - 1
-- OR p_date BETWEEN (l.creation_date - 1) AND (l.creation_date + 1)
-- )
ORDER BY ABS(NVL(p_date, l.creation_date) - l.creation_date) ASC, l.creation_date DESC;
SELECT l.vendor_feed_time
, l.status
, trunc(l.latitude, 3)
, trunc(l.longitude, 3)
, l.speed
, l.direction
, l.parked_time
, l.address
, d.device_tag
, (
SELECT NVL(fl.meaning, stmap.internal_status_code) FROM fnd_lookups fl, csf_gps_vendor_status_maps stmap
WHERE fl.lookup_type = 'CSF_GPS_DEVICE_STATUSES' AND fl.lookup_code = stmap.internal_status_code AND stmap.vendor_status_code = l.status
) status_code_meaning
, l.creation_date
FROM csf_gps_location_feeds l
, csf_gps_device_assignments a
, csf_gps_devices d
WHERE a.resource_id = p_resource_id
AND a.resource_type = p_resource_type
AND a.device_id = l.device_id
AND d.device_id = a.device_id
-- AND (
-- p_date IS NULL AND l.creation_date > SYSDATE - 1
-- OR p_date BETWEEN (l.creation_date - 1) AND (l.creation_date + 1)
-- AND p_date BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE+1)
-- )
ORDER BY ABS(NVL(p_date, l.creation_date) - l.creation_date) ASC, l.creation_date DESC;
INSERT INTO csf_gps_location_feeds(
location_feed_id
, device_id
, creation_date
, vendor_feed_time
, status
, latitude
, longitude
, speed
, direction
, parked_time
, address
)
VALUES (
csf_gps_location_feeds_s.NEXTVAL
, p_device_id_tbl(i)
, SYSDATE
, p_feed_time_tbl(i)
, p_status_tbl(i)
, p_lat_tbl(i)
, p_lng_tbl(i)
, p_speed_tbl(i)
, p_dir_tbl(i)
, p_parked_time_tbl(i)
, p_address_tbl(i)
);
DELETE csf_gps_location_feeds
WHERE device_id = p_device_id
AND creation_date BETWEEN p_start_date AND p_end_date;
DELETE csf_gps_location_feeds
WHERE device_id IN (
SELECT device_id
FROM csf_gps_device_assignments
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type
AND start_date_active < p_end_date
AND NVL(end_date_active, SYSDATE) > p_start_date
)
AND creation_date BETWEEN p_start_date AND p_end_date;
SELECT vendor_id, vendor_name
FROM csf_gps_vendors_vl;
SELECT device_id, device_tag
FROM csf_gps_devices
WHERE vendor_id = v_vendor_id;
SELECT resource_id, resource_type
FROM jtf_terr_rsc_all
WHERE terr_id = p_territory_id;
DELETE csf_gps_location_feeds
WHERE device_id IN (
SELECT device_id
FROM csf_gps_device_assignments
WHERE device_assignment_id = p_device_assignment_id
AND start_date_active < l_end_date
AND NVL(end_date_active, SYSDATE) > l_start_date
)
AND creation_date BETWEEN l_start_date AND l_end_date;
DELETE FROM csf_gps_vendors_tl t
WHERE NOT EXISTS (SELECT NULL FROM csf_gps_vendors_b b WHERE b.vendor_id = t.vendor_id);
UPDATE csf_gps_vendors_tl cgvt
SET (cgvt.vendor_name, cgvt.description) = (
SELECT cgvtl.vendor_name, cgvtl.description
FROM csf_gps_vendors_tl cgvtl
WHERE cgvtl.vendor_id = cgvt.vendor_id
AND cgvtl.language = cgvt.source_lang
)
WHERE (cgvt.vendor_id, cgvt.language) IN (
SELECT subt.vendor_id, subt.language
FROM csf_gps_vendors_tl subb, csf_gps_vendors_tl subt
WHERE subb.vendor_id = subt.vendor_id
AND subb.language = subt.source_lang
AND (
subb.vendor_name <> subt.vendor_name
OR subb.description <> subt.description
OR (subb.description IS NULL AND subt.description IS NOT NULL)
OR (subb.description IS NOT NULL AND subt.description IS NULL)
)
);
INSERT INTO csf_gps_vendors_tl (
vendor_id
, vendor_name
, description
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, language
, source_lang
)
SELECT cgvt.vendor_id
, cgvt.vendor_name
, cgvt.description
, cgvt.created_by
, cgvt.creation_date
, cgvt.last_updated_by
, cgvt.last_update_date
, cgvt.last_update_login
, l.language_code
, cgvt.source_lang
FROM csf_gps_vendors_tl cgvt
, fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND cgvt.language = userenv('LANG')
AND NOT EXISTS (
SELECT NULL
FROM csf_gps_vendors_tl t
WHERE t.vendor_id = cgvt.vendor_id
AND t.language = l.language_code
);