Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- GenericEvent.COST,
- GenericEvent.CREATION_DATE,
- GenericEvent.GE_COMMENT,
- GenericEvent.GE_DATE,
- GenericEvent.GE_HR,
- GenericEvent.GE_KM,
- GenericEvent.GE_TYPE_ID,
- GenericEvent.GENERIC_EVENT_ID,
- GenericEvent.GUI_USER_ID,
- GenericEvent.LM_COMMENT,
- GenericEvent.LM_DATE,
- GenericEvent.LM_NAME,
- GenericEvent.NAME,
- GenericEvent.poi_id,
- GenericEvent.SHORT_NAME,
- null vehicle_id,
- GenericEvent.wbs_id,
- Person.person_id,
- (Person.LAST_NAME || ' ' || Person.FIRST_NAME) person_name,
- vopc.name category_name,
- vopc.graphical_symbol,
- P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
- (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
- GenericEvent.REF_NUMBER,
- GenericEvent.GE_TYPE_STATUS_ID,
- null vehicle_name,
- null object_id,
- null object_name,
- GeTypeStatus.ICON_NAME status_icon,
- GenericEvent.IS_PAID,
- VoTin.Tin vo_tin_tin,
- VoTin.Real_Employer_Name vo_tin_real_employer_name,
- poi.short_name poi_short_name,
- poi.name poi_name
- from
- generic_event GenericEvent,
- person Person,
- gu_person GuPerson,
- ge_type_status GeTypeStatus,
- vo_tin VoTin,
- vo_person_category vopc,
- gui_users GuiUsers,
- oc_poi poi
- where
- GenericEvent.person_id = GuPerson.person_id
- and GuPerson.gui_user_id = GuiUsers.gui_user_id
- and GuiUsers.gui_user_id = :gui_user_id
- and GuPerson.person_id = Person.person_id
- and Person.vo_person_category_id = vopc.vo_person_category_id
- and Person.REAL_EMPLOYER_TIN = VoTin.TIN (+)
- AND vopc.vehicle_owner_id = VoTin.vehicle_owner_id(+)
- AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
- and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
- and nvl(vopc.is_active, 'Y') = 'Y'
- and GenericEvent.poi_id = poi.poi_id(+)
- and Person.person_status_id = 0
- union all
- SELECT
- GenericEvent.COST,
- GenericEvent.CREATION_DATE,
- GenericEvent.GE_COMMENT,
- GenericEvent.GE_DATE,
- GenericEvent.GE_HR,
- GenericEvent.GE_KM,
- GenericEvent.GE_TYPE_ID,
- GenericEvent.GENERIC_EVENT_ID,
- GenericEvent.GUI_USER_ID,
- GenericEvent.LM_COMMENT,
- GenericEvent.LM_DATE,
- GenericEvent.LM_NAME,
- GenericEvent.NAME,
- GenericEvent.poi_id,
- GenericEvent.SHORT_NAME,
- Vehicle.vehicle_id,
- GenericEvent.wbs_id,
- null person_id,
- null person_name,
- vovc.name category_name,
- vovc.graphical_symbol,
- P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
- (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
- GenericEvent.REF_NUMBER,
- GenericEvent.GE_TYPE_STATUS_ID,
- (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) vehicle_name,
- null object_id,
- null object_name,
- GeTypeStatus.ICON_NAME status_icon,
- GenericEvent.IS_PAID,
- null vo_tin_tin,
- null vo_tin_real_employer_name,
- poi.short_name poi_short_name,
- poi.name poi_name
- from
- generic_event GenericEvent,
- vehicle Vehicle,
- gu_vehicle GuVehicle,
- ge_type_status GeTypeStatus,
- vo_vehicle_category vovc,
- gui_users GuiUsers,
- oc_poi poi
- where
- GenericEvent.vehicle_id = GuVehicle.vehicle_id
- and GuVehicle.gui_user_id = GuiUsers.gui_user_id
- and GuiUsers.gui_user_id = :gui_user_id
- and GuVehicle.vehicle_id = Vehicle.vehicle_id
- and Vehicle.vo_vehicle_category_id = vovc.vo_vehicle_category_id
- AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
- and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
- and nvl(vovc.is_active, 'Y') = 'Y'
- and nvl(vovc.is_object, 'N') = 'N'
- and GenericEvent.poi_id = poi.poi_id(+)
- union all
- SELECT
- GenericEvent.COST,
- GenericEvent.CREATION_DATE,
- GenericEvent.GE_COMMENT,
- GenericEvent.GE_DATE,
- GenericEvent.GE_HR,
- GenericEvent.GE_KM,
- GenericEvent.GE_TYPE_ID,
- GenericEvent.GENERIC_EVENT_ID,
- GenericEvent.GUI_USER_ID,
- GenericEvent.LM_COMMENT,
- GenericEvent.LM_DATE,
- GenericEvent.LM_NAME,
- GenericEvent.NAME,
- GenericEvent.poi_id,
- GenericEvent.SHORT_NAME,
- null vehicle_id,
- GenericEvent.wbs_id,
- null person_id,
- null person_name,
- vooc.name category_name,
- vooc.graphical_symbol,
- P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
- (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
- GenericEvent.REF_NUMBER,
- GenericEvent.GE_TYPE_STATUS_ID,
- null vehicle_name,
- GenericEvent.object_id,
- NVL2(Object.vehicle_id, '['|| Object.COMPANY_NR || '].[' || Object.NUMBERPLATE || ']', NULL) object_name,
- GeTypeStatus.ICON_NAME status_icon,
- GenericEvent.IS_PAID,
- null vo_tin_tin,
- null vo_tin_real_employer_name,
- poi.short_name poi_short_name,
- poi.name poi_name
- from
- generic_event GenericEvent,
- vehicle Object,
- gu_vehicle GuObject,
- ge_type_status GeTypeStatus,
- vo_vehicle_category vooc,
- gui_users GuiUsers,
- oc_poi poi
- where
- GenericEvent.vehicle_id = GuObject.vehicle_id
- and GuObject.gui_user_id = GuiUsers.gui_user_id
- and GuiUsers.gui_user_id = :gui_user_id
- and GuObject.vehicle_id = Object.vehicle_id
- and Object.vo_vehicle_category_id = vooc.vo_vehicle_category_id
- AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
- and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
- and nvl(vooc.is_active, 'Y') = 'Y'
- and nvl(vooc.is_object, 'N') = 'Y'
- and GenericEvent.poi_id = poi.poi_id(+)
- union all
- SELECT
- GenericEvent.COST,
- GenericEvent.CREATION_DATE,
- GenericEvent.GE_COMMENT,
- GenericEvent.GE_DATE,
- GenericEvent.GE_HR,
- GenericEvent.GE_KM,
- GenericEvent.GE_TYPE_ID,
- GenericEvent.GENERIC_EVENT_ID,
- GenericEvent.GUI_USER_ID,
- GenericEvent.LM_COMMENT,
- GenericEvent.LM_DATE,
- GenericEvent.LM_NAME,
- GenericEvent.NAME,
- GenericEvent.poi_id,
- GenericEvent.SHORT_NAME,
- null vehicle_id,
- GenericEvent.wbs_id,
- null person_id,
- null person_name,
- vowc.name category_name,
- vowc.graphical_symbol,
- P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
- (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
- GenericEvent.REF_NUMBER,
- GenericEvent.GE_TYPE_STATUS_ID,
- null vehicle_name,
- null object_id,
- null object_name,
- GeTypeStatus.ICON_NAME status_icon,
- GenericEvent.IS_PAID,
- null vo_tin_tin,
- null vo_tin_real_employer_name,
- poi.short_name poi_short_name,
- poi.name poi_name
- from
- generic_event GenericEvent,
- vehicle Object,
- v_gui_user_oc_poi vguop,
- ge_type_status GeTypeStatus,
- vo_wbs_category vowc,
- gui_users GuiUsers,
- oc_poi poi,
- wbs Wbs,
- gu_wbs GuWbs
- where
- GenericEvent.poi_id = vguop.poi_id
- and vguop.gui_user_id = GuiUsers.gui_user_id
- and GuiUsers.gui_user_id = :gui_user_id
- and vguop.poi_id = poi.poi_id
- and GenericEvent.wbs_id = GuWbs.wbs_id
- and GuWbs.gui_user_id = GuiUsers.gui_user_id
- and GuWbs.wbs_id = Wbs.wbs_id
- and Wbs.vo_wbs_category_id = vowc.vo_wbs_category_id
- AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
- and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+);
Advertisement
Add Comment
Please, Sign In to add comment