psi_mmobile

Untitled

Jul 7th, 2026
64
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.90 KB | None | 0 0
  1. SELECT
  2. GenericEvent.COST,
  3. GenericEvent.CREATION_DATE,
  4. GenericEvent.GE_COMMENT,
  5. GenericEvent.GE_DATE,
  6. GenericEvent.GE_HR,
  7. GenericEvent.GE_KM,
  8. GenericEvent.GE_TYPE_ID,
  9. GenericEvent.GENERIC_EVENT_ID,
  10. GenericEvent.GUI_USER_ID,
  11. GenericEvent.LM_COMMENT,
  12. GenericEvent.LM_DATE,
  13. GenericEvent.LM_NAME,
  14. GenericEvent.NAME,
  15. GenericEvent.poi_id,
  16. GenericEvent.SHORT_NAME,
  17. null vehicle_id,
  18. GenericEvent.wbs_id,
  19. Person.person_id,
  20. (Person.LAST_NAME || ' ' || Person.FIRST_NAME) person_name,
  21. vopc.name category_name,
  22. vopc.graphical_symbol,
  23. P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
  24. (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
  25. GenericEvent.REF_NUMBER,
  26. GenericEvent.GE_TYPE_STATUS_ID,
  27. null vehicle_name,
  28. null object_id,
  29. null object_name,
  30. GeTypeStatus.ICON_NAME status_icon,
  31. GenericEvent.IS_PAID,
  32. VoTin.Tin vo_tin_tin,
  33. VoTin.Real_Employer_Name vo_tin_real_employer_name,
  34. poi.short_name poi_short_name,
  35. poi.name poi_name
  36. from
  37. generic_event GenericEvent,
  38. person Person,
  39. gu_person GuPerson,
  40. ge_type_status GeTypeStatus,
  41. vo_tin VoTin,
  42. vo_person_category vopc,
  43. gui_users GuiUsers,
  44. oc_poi poi
  45. where
  46. GenericEvent.person_id = GuPerson.person_id
  47. and GuPerson.gui_user_id = GuiUsers.gui_user_id
  48. and GuiUsers.gui_user_id = :gui_user_id
  49. and GuPerson.person_id = Person.person_id
  50. and Person.vo_person_category_id = vopc.vo_person_category_id
  51. and Person.REAL_EMPLOYER_TIN = VoTin.TIN (+)
  52. AND vopc.vehicle_owner_id = VoTin.vehicle_owner_id(+)
  53. AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
  54. and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
  55. and nvl(vopc.is_active, 'Y') = 'Y'
  56. and GenericEvent.poi_id = poi.poi_id(+)
  57. and Person.person_status_id = 0
  58. AND NVL (GenericEvent.IS_WORK_ORDER,'N') = 'N'
  59. and (:person_ids is null or GenericEvent.person_id in (SELECT * FROM TABLE(in_list(:person_ids)))) -- left filter
  60. union all
  61. SELECT
  62. GenericEvent.COST,
  63. GenericEvent.CREATION_DATE,
  64. GenericEvent.GE_COMMENT,
  65. GenericEvent.GE_DATE,
  66. GenericEvent.GE_HR,
  67. GenericEvent.GE_KM,
  68. GenericEvent.GE_TYPE_ID,
  69. GenericEvent.GENERIC_EVENT_ID,
  70. GenericEvent.GUI_USER_ID,
  71. GenericEvent.LM_COMMENT,
  72. GenericEvent.LM_DATE,
  73. GenericEvent.LM_NAME,
  74. GenericEvent.NAME,
  75. GenericEvent.poi_id,
  76. GenericEvent.SHORT_NAME,
  77. Vehicle.vehicle_id,
  78. GenericEvent.wbs_id,
  79. null person_id,
  80. null person_name,
  81. vovc.name category_name,
  82. vovc.graphical_symbol,
  83. P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
  84. (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
  85. GenericEvent.REF_NUMBER,
  86. GenericEvent.GE_TYPE_STATUS_ID,
  87. (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) vehicle_name,
  88. null object_id,
  89. null object_name,
  90. GeTypeStatus.ICON_NAME status_icon,
  91. GenericEvent.IS_PAID,
  92. null vo_tin_tin,
  93. null vo_tin_real_employer_name,
  94. poi.short_name poi_short_name,
  95. poi.name poi_name
  96. from
  97. generic_event GenericEvent,
  98. vehicle Vehicle,
  99. gu_vehicle GuVehicle,
  100. ge_type_status GeTypeStatus,
  101. vo_vehicle_category vovc,
  102. gui_users GuiUsers,
  103. oc_poi poi
  104. where
  105. GenericEvent.vehicle_id = GuVehicle.vehicle_id
  106. and GuVehicle.gui_user_id = GuiUsers.gui_user_id
  107. and GuiUsers.gui_user_id = :gui_user_id
  108. and GuVehicle.vehicle_id = Vehicle.vehicle_id
  109. and Vehicle.vo_vehicle_category_id = vovc.vo_vehicle_category_id
  110. AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
  111. and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
  112. and nvl(vovc.is_active, 'Y') = 'Y'
  113. and nvl(vovc.is_object, 'N') = 'N'
  114. and GenericEvent.poi_id = poi.poi_id(+)
  115. AND NVL (GenericEvent.IS_WORK_ORDER,'N') = 'N'
  116. and (:vehicle_ids is null or GenericEvent.vehicle_id in (SELECT * FROM TABLE(in_list(:vehicle_ids)))) -- left filter
  117. union all
  118. SELECT
  119. GenericEvent.COST,
  120. GenericEvent.CREATION_DATE,
  121. GenericEvent.GE_COMMENT,
  122. GenericEvent.GE_DATE,
  123. GenericEvent.GE_HR,
  124. GenericEvent.GE_KM,
  125. GenericEvent.GE_TYPE_ID,
  126. GenericEvent.GENERIC_EVENT_ID,
  127. GenericEvent.GUI_USER_ID,
  128. GenericEvent.LM_COMMENT,
  129. GenericEvent.LM_DATE,
  130. GenericEvent.LM_NAME,
  131. GenericEvent.NAME,
  132. GenericEvent.poi_id,
  133. GenericEvent.SHORT_NAME,
  134. null vehicle_id,
  135. GenericEvent.wbs_id,
  136. null person_id,
  137. null person_name,
  138. vooc.name category_name,
  139. vooc.graphical_symbol,
  140. P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
  141. (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
  142. GenericEvent.REF_NUMBER,
  143. GenericEvent.GE_TYPE_STATUS_ID,
  144. null vehicle_name,
  145. GenericEvent.object_id,
  146. NVL2(Object.vehicle_id, '['|| Object.COMPANY_NR || '].[' || Object.NUMBERPLATE || ']', NULL) object_name,
  147. GeTypeStatus.ICON_NAME status_icon,
  148. GenericEvent.IS_PAID,
  149. null vo_tin_tin,
  150. null vo_tin_real_employer_name,
  151. poi.short_name poi_short_name,
  152. poi.name poi_name
  153. from
  154. generic_event GenericEvent,
  155. vehicle Object,
  156. gu_vehicle GuObject,
  157. ge_type_status GeTypeStatus,
  158. vo_vehicle_category vooc,
  159. gui_users GuiUsers,
  160. oc_poi poi
  161. where
  162. GenericEvent.vehicle_id = GuObject.vehicle_id
  163. and GuObject.gui_user_id = GuiUsers.gui_user_id
  164. and GuiUsers.gui_user_id = :gui_user_id
  165. and GuObject.vehicle_id = Object.vehicle_id
  166. and Object.vo_vehicle_category_id = vooc.vo_vehicle_category_id
  167. AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
  168. and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
  169. and nvl(vooc.is_active, 'Y') = 'Y'
  170. and nvl(vooc.is_object, 'N') = 'Y'
  171. and GenericEvent.poi_id = poi.poi_id(+)
  172. AND NVL (GenericEvent.IS_WORK_ORDER,'N') = 'N'
  173. and (:object_ids is null or GenericEvent.object_id in (SELECT * FROM TABLE(in_list(:object_ids)))) -- left filter
  174. union all
  175. SELECT
  176. GenericEvent.COST,
  177. GenericEvent.CREATION_DATE,
  178. GenericEvent.GE_COMMENT,
  179. GenericEvent.GE_DATE,
  180. GenericEvent.GE_HR,
  181. GenericEvent.GE_KM,
  182. GenericEvent.GE_TYPE_ID,
  183. GenericEvent.GENERIC_EVENT_ID,
  184. GenericEvent.GUI_USER_ID,
  185. GenericEvent.LM_COMMENT,
  186. GenericEvent.LM_DATE,
  187. GenericEvent.LM_NAME,
  188. GenericEvent.NAME,
  189. GenericEvent.poi_id,
  190. GenericEvent.SHORT_NAME,
  191. null vehicle_id,
  192. GenericEvent.wbs_id,
  193. null person_id,
  194. null person_name,
  195. vowc.name category_name,
  196. vowc.graphical_symbol,
  197. P_WBS.getWbsName(GenericEvent.wbs_id, GenericEvent.POI_ID) wbs_name,
  198. (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) user_full_name,
  199. GenericEvent.REF_NUMBER,
  200. GenericEvent.GE_TYPE_STATUS_ID,
  201. null vehicle_name,
  202. null object_id,
  203. null object_name,
  204. GeTypeStatus.ICON_NAME status_icon,
  205. GenericEvent.IS_PAID,
  206. null vo_tin_tin,
  207. null vo_tin_real_employer_name,
  208. poi.short_name poi_short_name,
  209. poi.name poi_name
  210. from
  211. generic_event GenericEvent,
  212. vehicle Object,
  213. v_gui_user_oc_poi vguop,
  214. ge_type_status GeTypeStatus,
  215. vo_wbs_category vowc,
  216. gui_users GuiUsers,
  217. oc_poi poi,
  218. wbs Wbs,
  219. gu_wbs GuWbs
  220. where
  221. GenericEvent.poi_id = vguop.poi_id
  222. and vguop.gui_user_id = GuiUsers.gui_user_id
  223. and GuiUsers.gui_user_id = :gui_user_id
  224. and vguop.poi_id = poi.poi_id
  225. and GenericEvent.wbs_id = GuWbs.wbs_id
  226. and GuWbs.gui_user_id = GuiUsers.gui_user_id
  227. and GuWbs.wbs_id = Wbs.wbs_id
  228. and Wbs.vo_wbs_category_id = vowc.vo_wbs_category_id
  229. AND GenericEvent.GE_DATE between trunc(sysdate - 1) AND trunc(sysdate + 1)
  230. and GenericEvent.ge_type_status_id = GeTypeStatus.ge_type_status_id(+)
  231. AND NVL (GenericEvent.IS_WORK_ORDER,'N') = 'N'
  232. and (:poi_ids is null or GenericEvent.poi_id in (SELECT * FROM TABLE(in_list(:poi_ids)))) -- left filter;
Advertisement
Add Comment
Please, Sign In to add comment