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,
- GenericEvent.VEHICLE_ID,
- GenericEvent.WBS_ID,
- Person.PERSON_ID,
- (Person.LAST_NAME || ' ' || Person.FIRST_NAME) person_name,
- P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
- (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_name,
- GenericEvent.REF_NUMBER,
- GenericEvent.GE_TYPE_STATUS_ID,
- (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) vehicle_name,
- vAdfFirstAttachDocEO.file_path first_attach_doc,
- GenericEvent.OBJECT_ID,
- NVL2(Object.vehicle_id, '['|| Object.COMPANY_NR || '].[' || Object.NUMBERPLATE || ']', NULL) object_name,
- GeTypeStatus.ICON_NAME status_icon,
- GeTypeStatus.GE_TYPE_STATUS_ID,
- vAdfFirstAttachDocEO.content_Type_id first_attach_doc_content_type,
- vehicle.numberplate vehicle_numberplate,
- vehicle.company_nr vehicle_company_nr,
- GenericEvent.IS_PAID,
- -- GenericEvent.EQUIPMENT_STORE_PLACE_ID,
- -- EquipmentStorePlace.NAME storeplace_name,
- VoTin.VEHICLE_OWNER_ID,
- VoTin.Tin vo_tin_tin,
- VoTin.Real_Employer_Name vo_tin_real_employer_name
- FROM
- GENERIC_EVENT GenericEvent,
- PERSON Person,
- GUI_USERS GuiUsers,
- VEHICLE Vehicle,
- VEHICLE Object,
- VO_VEHICLE_CATEGORY VoVehicleCategory,
- GE_TYPE_STATUS GeTypeStatus,
- EQUIPMENT_STORE_PLACE EquipmentStorePlace,
- VO_TIN VoTin,
- VO_PERSON_CATEGORY VoPersonCategory,
- V_ADF_FIRST_ATTACH_DOC vAdfFirstAttachDocEO
- WHERE
- GenericEvent.person_id = Person.person_id (+)
- AND person.vo_person_category_id =VoPersonCategory.Vo_person_category_id(+)
- AND GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id (+)
- AND GenericEvent.GUI_USER_ID = GuiUsers.GUI_USER_ID
- AND GenericEvent.vehicle_id = Vehicle.vehicle_id (+)
- AND GenericEvent.OBJECT_ID = Object.vehicle_id (+)
- AND Object.VO_VEHICLE_CATEGORY_ID = VoVehicleCategory.VO_VEHICLE_CATEGORY_ID (+)
- AND GenericEvent.EQUIPMENT_STORE_PLACE_ID = EquipmentStorePlace.EQUIPMENT_STORE_PLACE_ID(+)
- AND Person.REAL_EMPLOYER_TIN = VoTin.TIN (+)
- AND VoPersonCategory.vehicle_owner_id = VoTin.vehicle_owner_id (+)
- AND GenericEvent.generic_event_id = vAdfFirstAttachDocEO.generic_event_id(+)
- AND (vAdfFirstAttachDocEO.row_num = 1 OR vAdfFirstAttachDocEO.row_num IS NULL)
- AND (GenericEvent.PERSON_ID IN
- (SELECT PERSON_ID FROM GU_PERSON WHERE GUI_USER_ID = :gui_user_id)
- OR GenericEvent.VEHICLE_ID IN
- (SELECT VEHICLE_ID FROM GU_VEHICLE WHERE GUI_USER_ID = :gui_user_id)
- OR GenericEvent.object_id IN
- (SELECT VEHICLE_ID FROM GU_VEHICLE WHERE GUI_USER_ID = :gui_user_id)
- OR GenericEvent.poi_id IN
- (SELECT poi_id FROM v_gui_user_oc_poi WHERE gui_user_id = :gui_user_id)
- OR GenericEvent.gui_user_id = :gui_user_id)
- AND (NULL IS NULL OR GenericEvent.PERSON_ID = NULL) -- person filter
- AND (NULL IS NULL OR GenericEvent.VEHICLE_ID = NULL) -- vehicle filter
- AND (NULL IS NULL OR GenericEvent.POI_ID = NULL) -- poi filter
- AND (NULL IS NULL OR GenericEvent.OBJECT_ID = NULL) -- object filter
- AND (NULL IS NULL OR GenericEvent.WBS_ID = NULL) -- wbs filter
- AND GenericEvent.GE_DATE >= toutcdate(:fromDate, 'Europe/Brussels')
- AND GenericEvent.GE_DATE <= toutcdate(:toDate, 'Europe/Brussels')
- AND NVL (GenericEvent.IS_WORK_ORDER,'N') <> 'Y'
- ORDER BY GenericEvent.CREATION_DATE DESC;
Advertisement
Add Comment
Please, Sign In to add comment