API tools faq
paste
Guest User

Untitled

a guest
Oct 26th, 2016
68
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.52 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. Wbs.POI_ID,
  23. Wbs.POI_TIN AS POI_TIN1,
  24. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'Dy') AS VIEW_ATTR,
  25. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'dd') AS VIEW_ATTR,
  26. Wbs.REF_NUMBER,
  27. TOUSERDATE (SocialOfficeTransaction.CREATION_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  28. TOUSERDATE (SocialOfficeTransaction.CANCEL_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  29. OperationCenter.TIMEZONE,
  30. OperationCenter.OPERATION_CENTER_ID,
  31. VoPersonCategory.NAME AS NAME1,
  32. VoPersonCategory.VO_PERSON_CATEGORY_ID,
  33. VoPersonCategory.GRAPHICAL_SYMBOL,
  34. P_PERSON.list_person_group_name(Person.PERSON_ID) AS VIEW_ATTR,
  35. DECODE (SocialOfficeTransaction.is_start_time_set, 'N' , '', TO_CHAR (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone), 'HH24:mi')) AS VIEW_ATTR,
  36. SocialOfficeTransaction.DURATION,
  37. 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,
  38. SocialOfficeTransaction.IS_START_TIME_SET,
  39. TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) AS VIEW_ATTR,
  40. SocialOfficeTransaction.SO_COMMENT,
  41. SocialOfficeTransaction.PLAN_MODE,
  42. p_person.getCompanyName(Person.person_id) AS VIEW_ATTR,
  43. NVL(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id) AS VIEW_ATTR,
  44. DECODE (p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), NULL, OcPoi.name, p_checkinatwork.getUserCawPoiName(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID)) AS VIEW_ATTR,
  45. OcPoi.POI_ID AS POI_ID2,
  46. DECODE (
  47. (SELECT v.poi_id FROM v_gui_user_oc_poi v WHERE v.gui_user_id = GuPerson.gui_user_id
  48. AND v.poi_id = NVL(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id)
  49. ), NULL, 'true', 'false') AS VIEW_ATTR
  50. FROM OF_OWNER.SOCIAL_OFFICE_TRANSACTION SocialOfficeTransaction,
  51. OF_OWNER.SO_TRANSACTION_STATUS SoTransactionStatus,
  52. WBS Wbs,
  53. PERSON Person,
  54. OC_POI OcPoi,
  55. GUI_USERS GuiUsers,
  56. OPERATION_CENTER OperationCenter,
  57. GU_PERSON GuPerson,
  58. VO_PERSON_CATEGORY VoPersonCategory
  59. WHERE ((((SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID = SoTransactionStatus.SO_TRANSACTION_STATUS_ID)
  60. AND (SocialOfficeTransaction.WBS_ID = Wbs.WBS_ID)))
  61. AND (SocialOfficeTransaction.PERSON_ID = Person.PERSON_ID))
  62. AND (Wbs.POI_ID = OcPoi.POI_ID(+))
  63. AND SocialOfficeTransaction.PERSON_ID = GuPerson.person_id
  64. AND GuPerson.gui_user_id = 101708
  65. AND SocialOfficeTransaction.PERSON_ID = 110777
  66. AND GuiUsers.gui_user_id = GuPerson.gui_user_id
  67. AND GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
  68. AND Person.vo_person_category_id = VoPersonCategory.vo_person_category_id
  69. --AND SocialOfficeTransaction.Wbs_Id = NVL(?, SocialOfficeTransaction.Wbs_Id)
  70. AND SocialOfficeTransaction.person_id = 110777
  71. and OcPoi.poi_id = 603334
  72. and ( ( (( ( (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) <= sysdate ) ) AND ( (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) >= sysdate - 6 ) ) )AND (nvl(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id) = 603334) ) ) )
  73. ORDER BY work_date DESC;
  74.  
  75.  
  76. SELECT SocialOfficeTransaction.WORK_DATE,
  77. SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID,
  78. SocialOfficeTransaction.POI_TIN,
  79. Wbs.WBS_ID,
  80. SoTransactionStatus.NAME AS SO_TRANSACTION_STATUS_NAME,
  81. SoTransactionStatus.SO_TRANSACTION_STATUS_ID,
  82. SocialOfficeTransaction.CREATION_DATE,
  83. SocialOfficeTransaction.CREATION_USER_NAME,
  84. SocialOfficeTransaction.SO_CREATION_NUMBER,
  85. SocialOfficeTransaction.CANCEL_DATE,
  86. SocialOfficeTransaction.CANCELLATION_USER_NAME,
  87. SocialOfficeTransaction.SO_CANCELLATION_NUMBER,
  88. Person.COMPANY_NR,
  89. Person.PERSON_ID,
  90. Person.FIRST_NAME,
  91. Person.LAST_NAME,
  92. SocialOfficeTransaction.CREATION_GUI_USER_ID,
  93. SocialOfficeTransaction.CANCELLATION_GUI_USER_ID,
  94. SocialOfficeTransaction.PERSON_ID AS PERSON_ID1,
  95. SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID AS SO_TRANSACTION_STATUS_ID1,
  96. SocialOfficeTransaction.WBS_ID AS WBS_ID1,
  97. Wbs.POI_ID,
  98. Wbs.POI_TIN AS POI_TIN1,
  99. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'Dy') AS VIEW_ATTR,
  100. TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone),'dd') AS VIEW_ATTR,
  101. Wbs.REF_NUMBER,
  102. TOUSERDATE (SocialOfficeTransaction.CREATION_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  103. TOUSERDATE (SocialOfficeTransaction.CANCEL_DATE, OPERATIONCENTER.timezone) AS VIEW_ATTR,
  104. OperationCenter.TIMEZONE,
  105. OperationCenter.OPERATION_CENTER_ID,
  106. VoPersonCategory.NAME AS NAME1,
  107. VoPersonCategory.VO_PERSON_CATEGORY_ID,
  108. VoPersonCategory.GRAPHICAL_SYMBOL,
  109. P_PERSON.list_person_group_name(Person.PERSON_ID) AS VIEW_ATTR,
  110. decode (SocialOfficeTransaction.is_start_time_set, 'N' , '', to_char (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone), 'HH24:mi')) AS VIEW_ATTR,
  111. SocialOfficeTransaction.DURATION,
  112. 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,
  113. SocialOfficeTransaction.IS_START_TIME_SET,
  114. TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) AS VIEW_ATTR,
  115. SocialOfficeTransaction.SO_COMMENT,
  116. SocialOfficeTransaction.PLAN_MODE,
  117. p_person.getCompanyName(Person.person_id) AS VIEW_ATTR,
  118. nvl(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id) AS VIEW_ATTR,
  119. decode (p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), NULL, OcPoi.name, p_checkinatwork.getUserCawPoiName(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID)) AS VIEW_ATTR,
  120. OcPoi.POI_ID AS POI_ID2,
  121. decode ((SELECT v.poi_id FROM v_gui_user_oc_poi v
  122. WHERE v.gui_user_id = GuPerson.gui_user_id AND v.poi_id = nvl(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id)), NULL, 'true', 'false') AS VIEW_ATTR
  123. FROM OF_OWNER.SOCIAL_OFFICE_TRANSACTION SocialOfficeTransaction, OF_OWNER.SO_TRANSACTION_STATUS SoTransactionStatus, WBS Wbs, PERSON Person, OC_POI OcPoi, GUI_USERS GuiUsers, OPERATION_CENTER OperationCenter, GU_PERSON GuPerson, VO_PERSON_CATEGORY VoPersonCategory
  124. WHERE (SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID = SoTransactionStatus.SO_TRANSACTION_STATUS_ID)
  125. AND (SocialOfficeTransaction.WBS_ID = Wbs.WBS_ID)
  126. AND (SocialOfficeTransaction.PERSON_ID = Person.PERSON_ID)
  127. AND (Wbs.POI_ID = OcPoi.POI_ID(+))
  128. AND SocialOfficeTransaction.PERSON_ID = GuPerson.person_id
  129. AND GuPerson.gui_user_id = 101708
  130. AND GuiUsers.gui_user_id = GuPerson.gui_user_id
  131. AND GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
  132. AND Person.vo_person_category_id = VoPersonCategory.vo_person_category_id
  133. AND (Nvl('N', 'N') = 'N' OR SoTransactionStatus.SO_TRANSACTION_STATUS_ID = 6)
  134. AND (Nvl('N', 'N') = 'N' OR (SoTransactionStatus.SO_TRANSACTION_STATUS_ID = 5 OR SoTransactionStatus.SO_TRANSACTION_STATUS_ID = 2 ))
  135. and SocialOfficeTransaction.Wbs_Id = Nvl(129631, SocialOfficeTransaction.Wbs_Id)
  136. AND Person.VO_PERSON_CATEGORY_ID = Nvl(NULL, Person.VO_PERSON_CATEGORY_ID)
  137. and SocialOfficeTransaction.person_id = 110777
  138. AND ( ( (( ( (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) <= sysdate ) ) AND ( (TOUSERDATE (SocialOfficeTransaction.work_date , OperationCenter.timezone) >= sysdate - 6 ) ) )AND (nvl(p_checkinatwork.getUserCawPoiId(GuPerson.gui_user_id, SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID), Wbs.poi_id) = 603334) ) ) )
  139. ORDER BY SocialOfficeTransaction.WORK_DATE, Person.COMPANY_NR, Person.LAST_NAME, Person.FIRST_NAME, OcPoi.NAME
  140. ;
  141.  
  142.  
  143. SELECT DISTINCT SOCIALOFFICETRANSACTION.*
  144. FROM
  145. (SELECT SOCIALOFFICETRANSACTION.*,
  146. OCPOI.NAME NAME,
  147. WBS.REF_NUMBER,
  148. WBS.NAME AS WBS_NAME,
  149. WBS.POI_TIN WBS_POI_TIN,
  150. WBS.POI_ID,
  151. SOTRANSACTIONSTATUS.NAME AS SO_TRANSACTION_STATUS_NAME
  152. FROM SOCIAL_OFFICE_TRANSACTION SOCIALOFFICETRANSACTION,
  153. GUI_USERS GUIUSERS,
  154. SO_TRANSACTION_STATUS SOTRANSACTIONSTATUS,
  155. WBS WBS,
  156. OC_POI OCPOI,
  157. PERSON PERSON,
  158. GU_PERSON GUPERSON
  159. WHERE GUIUSERS.GUI_USER_ID = 101708
  160. AND SOCIALOFFICETRANSACTION.PERSON_ID = PERSON.PERSON_ID
  161. AND PERSON.PERSON_ID = GUPERSON.PERSON_ID
  162. AND GUPERSON.GUI_USER_ID = GUIUSERS.GUI_USER_ID
  163. AND SocialOfficeTransaction.person_id = 110777 -- TUKA NQQ FILTER BRAAAT *THUG LIFE'S AWAY* B)
  164. AND SOCIALOFFICETRANSACTION.SO_TRANSACTION_STATUS_ID = SOTRANSACTIONSTATUS.SO_TRANSACTION_STATUS_ID
  165. AND SOCIALOFFICETRANSACTION.WBS_ID = WBS.WBS_ID
  166. AND WBS.POI_ID = OCPOI.POI_ID
  167. and ((OcPoi.OC_POI_STATUS_ID = 0 and SocialOfficeTransaction.so_transaction_status_id = 7) or (SocialOfficeTransaction.so_transaction_status_id <> 7))
  168. AND SOCIALOFFICETRANSACTION.WORK_DATE >= sysdate - 6
  169. AND SOCIALOFFICETRANSACTION.WORK_DATE < sysdate
  170.  
  171. and nvl( SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID, 2) = nvl( decode(-1, -1, SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID, -1), 2)
  172. AND nvl(Wbs.poi_id, -2) = nvl (decode(603334, -1, nvl(Wbs.poi_id, -2), 603334), -2)
  173. and nvl( SocialOfficeTransaction.POI_TIN, -2) = nvl( decode('-1', '-1', SocialOfficeTransaction.POI_TIN, '-1') , -2)
  174. ) SOCIALOFFICETRANSACTION
  175. order by SOCIALOFFICETRANSACTION.creation_date;
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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!