psi_mmobile

Untitled

Sep 28th, 2021
661
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT AttachDoc.ATTACH_DOC_ID,
  2.        AttachDoc.CONTENT_TYPE_ID,
  3.        AttachDoc.CREATION_DATE,
  4.        AttachDoc.DESCRIPTION,
  5.        AttachDoc.DOCUMENT_DATE,
  6.        AttachDoc.DOCUMENT_TYPE_ID,
  7.        AttachDoc.EXPIRY_DATE,
  8.        AttachDoc.FILE_PATH,
  9.        AttachDoc.LM_COMMENT,
  10.        AttachDoc.LM_DATE,
  11.        AttachDoc.LM_NAME,
  12.        AttachDoc.NAME,
  13.        AttachDoc.ORDER_NUM,
  14.        AttachDoc.PERSON_ID,
  15.        AttachDoc.VEHICLE_ID,
  16.        AttachDoc.GUI_USER_ID,
  17.        AttachDoc.GENERIC_EVENT_ID,
  18.        AttachDoc.SHORT_NAME,
  19.        AttachDoc.POI_ID,
  20.        AttachDoc.OBJECT_ID,
  21.        Person.PERSON_ID AS PERSON_ID1,
  22.        (Person.LAST_NAME  || ' ' ||  Person.FIRST_NAME) AS VIEW_ATTR,
  23.        P_WBS.getWbsName(AttachDoc.wbs_id, AttachDoc.POI_ID) AS VIEW_ATTR,
  24.        (GuiUsers.LAST_NAME || ' ' || GuiUsers.FIRST_NAME) AS VIEW_ATTR,
  25.        (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) AS VIEW_ATTR,
  26.        NVL2  (Object.vehicle_id, '['|| Object.COMPANY_NR || '].[' || Object.numberplate || ']' , NULL) AS VIEW_ATTR,
  27.        ('[' || P_WBS.getWbsName(AttachDoc.wbs_id, AttachDoc.POI_ID) || '].[' || AttachDoc.NAME || '].[' || Person.LAST_NAME || ' ' || '].[' || Person.FIRST_NAME || ' ' || Person.LAST_NAME || '].[' || Vehicle.Numberplate  || '].[' || VoVehicleCategory.name || '].[' || Object.numberplate || ']') AS VIEW_ATTR,
  28.        AttachDoc.WBS_ID,
  29.        AttachDoc.REF_NUMBER,
  30.        (SELECT numberplate FROM vehicle v WHERE v.vehicle_id = AttachDoc.VEHICLE_ID) AS VIEW_ATTR,
  31.        (SELECT company_nr FROM vehicle v WHERE v.vehicle_id = AttachDoc.VEHICLE_ID) AS VIEW_ATTR,
  32.        DocumentType.DOCUMENT_TYPE_CATEGORY_ID,
  33.        DocumentType.DOCUMENT_TYPE_ID AS DOCUMENT_TYPE_ID1
  34. FROM OF_OWNER.ATTACH_DOC AttachDoc, V_UPDATABLE_PERSON Person, GUI_USERS GuiUsers, VEHICLE Vehicle, VEHICLE Object, VO_VEHICLE_CATEGORY VoVehicleCategory, OF_OWNER.DOCUMENT_TYPE DocumentType
  35. WHERE AttachDoc.person_id = Person.person_id (+)
  36. AND AttachDoc.DOCUMENT_TYPE_ID = DocumentType.DOCUMENT_TYPE_ID(+)
  37. AND AttachDoc.GUI_USER_ID = GuiUsers.GUI_USER_ID
  38. AND AttachDoc.vehicle_id = Vehicle.vehicle_id (+)
  39. AND AttachDoc.OBJECT_ID = Object.vehicle_id (+)
  40. AND Object.VO_VEHICLE_CATEGORY_ID = VoVehicleCategory.VO_VEHICLE_CATEGORY_ID (+)
  41. AND ( AttachDoc.PERSON_ID     IN
  42.   (SELECT PERSON_ID FROM GU_PERSON WHERE GUI_USER_ID = ?)
  43. OR AttachDoc.VEHICLE_ID IN
  44.   (SELECT VEHICLE_ID FROM GU_VEHICLE WHERE GUI_USER_ID = ?)
  45. OR AttachDoc.object_id IN
  46.   (SELECT VEHICLE_ID FROM GU_VEHICLE WHERE GUI_USER_ID = ?)
  47. OR AttachDoc.poi_id IN
  48.   (SELECT poi_id FROM v_gui_user_oc_poi WHERE gui_user_id = ?)
  49. OR AttachDoc.wbs_id IN
  50.   (SELECT wbs_id FROM wbs WHERE vehicle_owner_id IN (SELECT DISTINCT vehicle_owner_id FROM gu_vehicle_owner WHERE gui_user_id = ?))
  51. OR AttachDoc.gui_user_id = ?)
  52. AND (? IS NULL OR AttachDoc.PERSON_ID = ?)
  53. AND (? IS NULL OR AttachDoc.VEHICLE_ID = ?)
  54. AND (? IS NULL OR AttachDoc.POI_ID = ?)
  55. AND (? IS NULL OR AttachDoc.OBJECT_ID = ?)
  56. AND (? IS NULL OR AttachDoc.WBS_ID = ?)
  57. AND (DocumentType.DOCUMENT_TYPE_CATEGORY_ID = 101)
  58. ORDER BY CREATION_DATE DESC;
RAW Paste Data