ALTER PROCEDURE "GP_GET_ILLEGAL_REPORT_TOTALS"
(
  "PA" VARCHAR(200),
  "START_DATE" TIMESTAMP,
  "END_DATE" TIMESTAMP,
  "BY_MANAGEMENT_SECTOR" INTEGER
)
RETURNS
(
  "PA_ID" VARCHAR(6),
  "MANAGEMENT_SECTOR" VARCHAR(20),
  "OBSERVATION" VARCHAR(30),
  "OBSERVATION_CODE" VARCHAR(20),
  "KEY_OBSERVATION" INTEGER,
  "TOTAL_COUNT" INTEGER,
  "TOTAL_DISTANCE" NUMERIC(15, 5),
  "TOTAL_PER_KM" NUMERIC(15, 5),
  "ADULT_MALES" NUMERIC(15, 5),
  "ADULT_FEMALES" NUMERIC(15, 5),
  "TOTAL_YOUNG" NUMERIC(15, 5),
  "MALES_TO_FEMALES" NUMERIC(15, 5)
)
AS
BEGIN
  EXECUTE PROCEDURE GP_GET_TOTAL_DISTANCE PA, START_DATE, END_DATE RETURNING_VALUES :TOTAL_DISTANCE;
  IF (BY_MANAGEMENT_SECTOR=0) THEN
    BEGIN
      FOR
        SELECT a.PA_ID, c.OBSERVATION, b.OBS_CODE, c.KEY_OBSERVATION, SUM(a.TOTAL)
        FROM GROUND_PATROL_OBSERVATIONS a, LK_OBS_CODES b, LK_OBSERVATIONS c
        WHERE :PA CONTAINING a.PA_ID
              AND b.OBS_CODE_RELATION=c.OBS_CODE_RELATION AND a.OBSERVATION_RELATION=b.OBSERVATION_RELATION
              AND c.OBSERVATION_GROUP='Illegal Activities'
              AND a.DATE_TIME>=:START_DATE AND a.DATE_TIME<:END_DATE
        GROUP BY a.PA_ID, c.OBSERVATION, b.OBS_CODE, c.KEY_OBSERVATION
        ORDER BY a.PA_ID, c.OBSERVATION, b.OBS_CODE
        INTO :PA_ID, :OBSERVATION, :OBSERVATION_CODE, :KEY_OBSERVATION, :TOTAL_COUNT
      DO BEGIN
        TOTAL_PER_KM=TOTAL_COUNT/TOTAL_DISTANCE;
        SUSPEND;
      END
    END
  ELSE
    BEGIN
      FOR
        SELECT a.PA_ID, a.MGTSECTOR, c.OBSERVATION, b.OBS_CODE, c.KEY_OBSERVATION, SUM(a.TOTAL)
        FROM GROUND_PATROL_OBSERVATIONS a, LK_OBS_CODES b, LK_OBSERVATIONS c
        WHERE :PA CONTAINING a.PA_ID
              AND b.OBS_CODE_RELATION=c.OBS_CODE_RELATION AND a.OBSERVATION_RELATION=b.OBSERVATION_RELATION
              AND c.OBSERVATION_GROUP='Illegal Activities'
              AND a.DATE_TIME>=:START_DATE AND a.DATE_TIME<:END_DATE
        GROUP BY a.PA_ID, a.MGTSECTOR, c.OBSERVATION, b.OBS_CODE, c.KEY_OBSERVATION
        ORDER BY a.PA_ID, c.OBSERVATION, b.OBS_CODE
        INTO :PA_ID, :MANAGEMENT_SECTOR, :OBSERVATION, :OBSERVATION_CODE, :KEY_OBSERVATION, :TOTAL_COUNT
      DO BEGIN
        TOTAL_PER_KM=TOTAL_COUNT/TOTAL_DISTANCE;
        SUSPEND;
      END
    END
END