API tools faq
paste
Advertisement
psi_mmobile

Untitled

psi_mmobile
Mar 17th, 2020
310
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.17 KB | None | 0 0
raw download clone embed print report
  1. SELECT SocialOfficeTransaction.WORK_DATE,
  2. SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID,
  3. SocialOfficeTransaction.POI_TIN,
  4. Wbs.WBS_ID,
  5. SoTransactionStatus.NAME AS SO_TRANSACTION_STATUS_NAME,
  6. SoTransactionStatus.SO_TRANSACTION_STATUS_ID,
  7. SocialOfficeTransaction.CREATION_DATE,
  8. SocialOfficeTransaction.CREATION_USER_NAME,
  9. SocialOfficeTransaction.SO_CREATION_NUMBER,
  10. SocialOfficeTransaction.CANCEL_DATE,
  11. SocialOfficeTransaction.CANCELLATION_USER_NAME,
  12. SocialOfficeTransaction.SO_CANCELLATION_NUMBER,
  13. Person.COMPANY_NR,
  14. Person.PERSON_ID,
  15. Person.FIRST_NAME,
  16. Person.LAST_NAME,
  17. SocialOfficeTransaction.CREATION_GUI_USER_ID,
  18. SocialOfficeTransaction.CANCELLATION_GUI_USER_ID,
  19. SocialOfficeTransaction.PERSON_ID AS PERSON_ID1,
  20. SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID AS SO_TRANSACTION_STATUS_ID1,
  21. SocialOfficeTransaction.WBS_ID AS WBS_ID1,
  22. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'Dy') AS VIEW_ATTR,
  23. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'dd') AS VIEW_ATTR,
  24. Wbs.REF_NUMBER,
  25. TOUSERDATE (SocialOfficeTransaction.CREATION_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  26. TOUSERDATE (SocialOfficeTransaction.CANCEL_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  27. OperationCenter.TIMEZONE,
  28. OperationCenter.OPERATION_CENTER_ID,
  29. VoPersonCategory.NAME AS NAME1,
  30. VoPersonCategory.VO_PERSON_CATEGORY_ID,
  31. VoPersonCategory.GRAPHICAL_SYMBOL,
  32. P_PERSON.list_person_group_name(Person.PERSON_ID) AS VIEW_ATTR,
  33. decode (SocialOfficeTransaction.is_start_time_set, 'N' , '', to_char (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone), 'HH24:mi')) AS VIEW_ATTR,
  34. SocialOfficeTransaction.DURATION,
  35. decode (SocialOfficeTransaction.is_start_time_set, 'N', '', DECODE(SIGN(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) + SocialOfficeTransaction.duration /24 - trunc (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone)+ 1)), 1, '00:00', to_char (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) + SocialOfficeTransaction.duration /24, 'HH24:mi'))) AS VIEW_ATTR,
  36. SocialOfficeTransaction.IS_START_TIME_SET,
  37. TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) AS VIEW_ATTR,
  38. SocialOfficeTransaction.SO_COMMENT,
  39. SocialOfficeTransaction.PLAN_MODE,
  40. p_person.getCompanyName(Person.person_id) AS VIEW_ATTR,
  41. OcPoi.NAME,
  42. OcPoi.POI_ID,
  43. GuPerson.GUI_USER_ID,
  44. GuPerson.PERSON_ID AS PERSON_ID2,
  45. GuiUsers.GUI_USER_ID AS GUI_USER_ID1,
  46. to_char(SocialOfficeTransaction.WORK_DATE,'dd/MM/yyyy') AS VIEW_ATTR,
  47. Person.IS_ADMIN_STATUS_OK,
  48. NVL(Str.STR_VALUE,AdminStatusCategory.NAME) AS VIEW_ATTR,
  49. SoTransactionStatus.DESCRIPTION,
  50. P_PERSON.IsDocumentValid(Person.PERSON_ID, 100, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  51. P_PERSON.IsDocumentValid(Person.PERSON_ID, 101, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  52. P_PERSON.IsDocumentValid(Person.PERSON_ID, 102, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  53. P_PERSON.IsDocumentValid(Person.PERSON_ID, 103, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  54. P_PERSON.IsDocumentValid(Person.PERSON_ID, 104, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  55. P_PERSON.IsDocumentValid(Person.PERSON_ID, 105, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  56. P_PERSON.IsDocumentValid(Person.PERSON_ID, 106, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  57. NVL(Str1.STR_VALUE,SoTransactionStatus.DESCRIPTION) AS VIEW_ATTR,
  58. P_PERSON.IsDocumentValid(Person.PERSON_ID, 107, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  59. P_PERSON.IsDocumentValid(Person.PERSON_ID, 108, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
  60. P_PERSON.IsDocumentValid(Person.PERSON_ID, 109, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR
  61. FROM OF_OWNER.SOCIAL_OFFICE_TRANSACTION SocialOfficeTransaction, OF_OWNER.SO_TRANSACTION_STATUS SoTransactionStatus, WBS Wbs, PERSON Person, GUI_USERS GuiUsers, OPERATION_CENTER OperationCenter, GU_PERSON GuPerson, VO_PERSON_CATEGORY VoPersonCategory, OC_POI OcPoi, OF_OWNER.ADMIN_STATUS_CATEGORY AdminStatusCategory, OF_OWNER.STR Str, OF_OWNER.STR Str1
  62. WHERE SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID = SoTransactionStatus.SO_TRANSACTION_STATUS_ID
  63. AND SoTransactionStatus.STR_ID = Str1.STR_ID(+)
  64. AND GuiUsers.LANGUAGE = Str1.LANGUAGE(+)
  65. AND SocialOfficeTransaction.PERSON_ID = Person.PERSON_ID
  66. and Person.ADMIN_STATUS_CATEGORY_ID = AdminStatusCategory.ADMIN_STATUS_CATEGORY_ID(+)
  67. and AdminStatusCategory.STR_ID = Str.STR_ID(+)
  68. and GuiUsers.LANGUAGE = Str.LANGUAGE(+)
  69. and SocialOfficeTransaction.PERSON_ID = GuPerson.person_id
  70. and GuPerson.gui_user_id = 101494
  71. and GuiUsers.gui_user_id = GuPerson.gui_user_id
  72. and GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
  73. and Person.vo_person_category_id = VoPersonCategory.vo_person_category_id
  74. and SocialOfficeTransaction.wbs_id = Wbs.wbs_id (+)
  75. --AND (EXISTS
  76. -- (SELECT pd_poi_id
  77. -- FROM pd_poi
  78. -- WHERE pd_poi.poi_id = 1051274
  79. -- AND pd_poi.social_office_transaction_id=SocialOfficeTransaction.social_office_transaction_id)
  80. -- OR EXISTS
  81. -- (SELECT w.wbs_id
  82. -- FROM wbs w
  83. -- WHERE w.poi_id = 1051274
  84. -- AND w.wbs_id = SocialOfficeTransaction.wbs_id)
  85. -- )
  86.  
  87. and OcPoi.poi_id = 1051274
  88. AND SocialOfficeTransaction.work_date BETWEEN toutcdate( trunc(sysdate) + 1 ,timezone) AND toutcdate( trunc(sysdate) + 44 ,timezone)
  89. and ((NVL(null,'N') = 'N' AND NVL(null,'N') = 'N') OR INSTR(DECODE(NVL(null,'N'),'N','','6') || DECODE(NVL(null,'N'),'N','','25'),SoTransactionStatus.SO_TRANSACTION_STATUS_ID) > 0)
  90. and ( -1 = -1 or Person.VO_PERSON_CATEGORY_ID = -1)
  91. and upper(NVL(Str.STR_VALUE,AdminStatusCategory.NAME) || ' ' ||
  92. SocialOfficeTransaction.CANCELLATION_USER_NAME || ' ' ||
  93. P_PERSON.getCompanyName(Person.PERSON_ID) || ' ' ||
  94. Person.COMPANY_NR || ' ' ||
  95. SocialOfficeTransaction.CREATION_USER_NAME || ' ' ||
  96. NVL(Str1.STR_VALUE,SoTransactionStatus.DESCRIPTION) || ' ' ||
  97. SocialOfficeTransaction.DURATION || ' ' ||
  98. Person.FIRST_NAME || ' ' ||
  99. Person.LAST_NAME || ' ' ||
  100. VoPersonCategory.NAME || ' ' ||
  101. P_PERSON.list_person_group_name(Person.PERSON_ID) || ' ' ||
  102. SocialOfficeTransaction.PLAN_MODE || ' ' ||
  103. OcPoi.NAME || ' ' ||
  104. SocialOfficeTransaction.POI_TIN || ' ' ||
  105. Wbs.REF_NUMBER || ' ' ||
  106. SocialOfficeTransaction.SO_CANCELLATION_NUMBER || ' ' ||
  107. SocialOfficeTransaction.SO_COMMENT || ' ' ||
  108. SocialOfficeTransaction.SO_CREATION_NUMBER || ' ' ||
  109. SoTransactionStatus.NAME) like '%' || upper('BE FLOBECQ 1200178') || '%'
  110. and (NVL(null,'N') = 'N' or NVL(Person.IS_ADMIN_STATUS_OK,'N') = 'N')
  111. and SoTransactionStatus.SO_TRANSACTION_STATUS_ID = 7
  112. ORDER BY SocialOfficeTransaction.WORK_DATE, Person.COMPANY_NR, Person.LAST_NAME, Person.FIRST_NAME;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!