-- #1

  SELECT gu.gui_user_id,TRUNC(touserdate(sot.work_date,oc.timezone)) AS DAY_OF_WEEK,COUNT(*) AS NUMBER_OF_SOT
  FROM social_office_transaction sot, gu_person gp, person p, gui_users gu, operation_center oc, vo_person_category vopc
  WHERE gu.gui_user_id = gp.gui_user_id
  AND gp.person_id = p.person_id
  AND p.person_id = sot.person_id
  AND gu.operation_center_id = oc.operation_center_id
  AND sot.work_date BETWEEN TRUNC(toutcdate(SYSDATE,'iw'),oc.timezone) AND TRUNC(toutcdate(SYSDATE,'iw'),oc.timezone) + 6
  AND p.person_status_id = 0
  AND p.vo_person_category_id = vopc.vo_person_category_id
  AND NVL(vopc.is_active,'Y') = 'Y'
  AND gu.user_admin_right != 'MF'
  GROUP BY gu.gui_user_id,TRUNC(touserdate(sot.work_date,oc.timezone));

-- #2
  SELECT gu.gui_user_id,TO_CHAR(TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH'),'dd/mm/yyyy HH24:MI') AS REPORTED_BY_HOUR, COUNT(vd.vehicle_data_id) AS NUMBER_OF_BADGES
  FROM gui_users gu,operation_center oc, gu_vehicle guv, vehicle v, vo_vehicle_category vovc, vehicle_data vd
  WHERE gu.operation_center_id = oc.operation_center_id
  AND gu.gui_user_id = guv.gui_user_id
  AND guv.vehicle_id = v.vehicle_id
  AND v.vehicle_id = vd.vehicle_id
  AND v.vo_vehicle_category_id = vovc.vo_vehicle_category_id
  AND NVL(vovc.is_active,'Y') = 'Y'
  AND NVL(v.vehicle_status_id,0) = 0
  AND vd.event_type_id = 103
  AND gu.user_admin_right != 'MF'
  AND vd.date_day = TRUNC(toutcdate(SYSDATE,oc.timezone))
  GROUP BY gu.gui_user_id,TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH');

-- #3

SELECT gu.gui_user_id, ptd.date_day AS WORK_DATE, TRIM(TO_CHAR(NVL(SUM(ptd.TRF_DHR),0), '999990.99')) AS WORKED_HOURS
FROM gui_users gu, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
WHERE gu.gui_user_id = gp.gui_user_id
AND gp.person_id = p.person_id
AND p.person_id = ptd.person_id
AND ptd.work_code_id = wc.work_code_id
AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
AND p.vo_person_category_id = vopc.vo_person_category_id
AND p.person_status_id = 0
AND NVL(vopc.is_active,'Y') = 'Y'
AND NVL(wc.is_active,'Y') = 'Y'
AND gu.user_admin_right != 'MF'
AND ptd.date_day BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 6
GROUP BY gu.gui_user_id,ptd.date_day;


-- #4
  SELECT gu.gui_user_id,wc.short_code,wc.work_code_name AS WORK_CODE, SUM(ptd.trf_dhr) AS WORKED_HOURS
  FROM gui_users gu, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
  WHERE gu.gui_user_id = gp.gui_user_id
  AND gp.person_id = p.person_id
  AND p.person_id = ptd.person_id
  AND ptd.work_code_id = wc.work_code_id
  AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
  AND p.vo_person_category_id = vopc.vo_person_category_id
  AND p.person_status_id = 0
  AND NVL(vopc.is_active,'Y') = 'Y'
  AND NVL(wc.is_active,'Y') = 'Y'
  AND gu.user_admin_right != 'MF'
  AND TRUNC(ptd.date_day,'mm') = TRUNC(SYSDATE,'mm')
  GROUP BY gu.gui_user_id,wc.short_code,wc.work_code_name;