Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- poi.poi_id,
- poi.name,
- poi.location,
- poi.post_code,
- poi.street,
- poi.country,
- poi.street_nr,
- poi.oc_poi_status_id,
- pcat.name AS cat_name,
- poi.short_name,
- poi.mobility_zone_id,
- poi.street || ' ' || poi.street_nr || ', ' || poi.post_code || ' ' || poi.location || ' ' || poi.country address
- FROM oc_poi poi, oc_poi_category pcat, gui_users gu, wbs w
- WHERE gu.operation_center_id = pcat.operation_center_id
- AND pcat.oc_poi_category_id = poi.oc_poi_category_id
- AND pcat.oc_poi_category_id NOT IN (SELECT pc.home_poi_category_id FROM vo_person_category pc)
- AND NVL(poi.oc_poi_status_id, 0) = 0
- AND gu.gui_user_id = :gu_id
- AND poi.poi_id = w.poi_id(+)
- AND EXISTS (SELECT wpr.wbs_person_role_id
- FROM wbs_person wp, wbs_person_role wpr, role r, vo_role vr
- WHERE SYSDATE BETWEEN NVL(wp.from_date, TRUNC(SYSDATE)) AND NVL(wp.TO_DATE, TRUNC(SYSDATE+1))
- AND gu.profiled_person_id = wp.person_id
- AND wpr.wbs_person_id = wp.wbs_person_id
- AND wpr.vo_role_id = vr.vo_role_id
- AND vr.role_id = r.role_id
- AND NVL(vr.validation_status_id, r.validation_status_id) >= 80
- AND w.wbs_id = wp.wbs_id);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement