Advertisement
psi_mmobile

Untitled

Feb 12th, 2021
1,146
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 3.10 KB | None | 0 0
  1. -- #1
  2. SELECT TRUNC(touserdate(sot.work_date,oc.timezone)) AS DAY_OF_WEEK,COUNT(*) AS NUMBER_OF_SOT
  3. FROM social_office_transaction sot, gu_person gp, person p, gui_users gu, operation_center oc, vo_person_category vopc
  4. WHERE gu.gui_user_id = gp.gui_user_id
  5. AND gp.person_id = p.person_id
  6. AND p.person_id = sot.person_id
  7. AND gu.operation_center_id = oc.operation_center_id
  8. AND touserdate(sot.work_date,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
  9. AND gu.gui_user_id = 101114
  10. AND p.person_status_id = 0
  11. AND p.vo_person_category_id = vopc.vo_person_category_id
  12. AND NVL(vopc.is_active,'Y') = 'Y'
  13. GROUP BY TRUNC(touserdate(sot.work_date,oc.timezone))
  14. ORDER BY 1;
  15.  
  16. -- #2
  17. SELECT TO_CHAR(TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH'),'dd/mm/yyyy HH:MI') AS REPORTED_BY_HOUR, COUNT(vd.vehicle_data_id) AS NUMBER_OF_BADGES
  18. FROM gui_users gu,operation_center oc, gu_vehicle guv, vehicle v, vo_vehicle_category vovc, vehicle_data vd
  19. WHERE gu.operation_center_id = oc.operation_center_id
  20. AND gu.gui_user_id = guv.gui_user_id
  21. AND guv.vehicle_id = v.vehicle_id
  22. AND v.vehicle_id = vd.vehicle_id
  23. AND v.vo_vehicle_category_id = vovc.vo_vehicle_category_id
  24. AND NVL(vovc.is_active,'Y') = 'Y'
  25. AND NVL(v.vehicle_status_id,0) = 0
  26. AND vd.event_type_id = 103
  27. AND gu.gui_user_id = 101114
  28. AND TRUNC(touserdate(vd.gps_full_date,oc.timezone)) = TRUNC(SYSDATE)
  29. GROUP BY TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH')
  30. ORDER BY 1;
  31.  
  32. -- #3
  33. SELECT touserdate(ptd.date_day,oc.timezone) AS WORK_DATE, SUM(ptd.trf_dhr) AS WORKED_HOURS
  34. FROM gui_users gu,operation_center oc, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
  35. WHERE gu.operation_center_id = oc.operation_center_id
  36. AND gu.gui_user_id = gp.gui_user_id
  37. AND gp.person_id = p.person_id
  38. AND p.person_id = ptd.person_id
  39. AND ptd.work_code_id = wc.work_code_id
  40. AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
  41. AND p.vo_person_category_id = vopc.vo_person_category_id
  42. AND gu.gui_user_id = 101042
  43. AND p.person_status_id = 0
  44. AND NVL(vopc.is_active,'Y') = 'Y'
  45. AND NVL(wc.is_active,'Y') = 'Y'
  46. AND touserdate(ptd.date_day,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
  47. GROUP BY touserdate(ptd.date_day,oc.timezone)
  48. ORDER BY 1;
  49.  
  50. -- #4
  51. SELECT touserdate(ptd.date_day,oc.timezone) AS WORK_DATE,wc.work_code_name AS WORK_CODE, SUM(ptd.trf_dhr) AS WORKED_HOURS
  52. FROM gui_users gu,operation_center oc, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
  53. WHERE gu.operation_center_id = oc.operation_center_id
  54. AND gu.gui_user_id = gp.gui_user_id
  55. AND gp.person_id = p.person_id
  56. AND p.person_id = ptd.person_id
  57. AND ptd.work_code_id = wc.work_code_id
  58. AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
  59. AND p.vo_person_category_id = vopc.vo_person_category_id
  60. AND gu.gui_user_id = 101042
  61. AND p.person_status_id = 0
  62. AND NVL(vopc.is_active,'Y') = 'Y'
  63. AND NVL(wc.is_active,'Y') = 'Y'
  64. AND touserdate(ptd.date_day,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
  65. GROUP BY touserdate(ptd.date_day,oc.timezone), wc.work_code_name
  66. ORDER BY 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement