timesheetsmtdpackagebody.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE OR REPLACE PACKAGE BODY TimeSheetsMTDPackage 
AS
   PROCEDURE usp_SelectTimeSheetsMTD (inManagerID IN CHAR, 
      results_cursor OUT CURSOR_TYPE)
   AS
   -- Declare local variables
   varFirstDayOfMonth DATE;
   varWeekEndingDate DATE;
   BEGIN
      -- Calculate the first day of the month date and current week ending date
      SELECT ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1  INTO varFirstDayOfMonth 
      FROM DUAL;
      SELECT (SYSDATE + (6 - TO_CHAR(SYSDATE, 'D'))) INTO varWeekEndingDate 
      FROM DUAL;

      OPEN results_cursor FOR
      -- Select month-to-date submitted timesheets
      SELECT FirstName || ' ' || LastName AS UserName, SUM(Hours) AS TotalHours,
         WeekEndingDate, ApprovalDate
      FROM TimeSheets
      LEFT OUTER JOIN TimeSheetItems ON TimeSheets.TimeSheetID = 
         TimeSheetItems.TimeSheetID
      LEFT OUTER JOIN Users ON TimeSheets.UserID = Users.UserID
      WHERE Users.ManagerID = inManagerID
      AND WeekEndingDate BETWEEN varFirstDayOfMonth AND varWeekEndingDate
      AND Submitted = 1
      GROUP BY WeekEndingDate, FirstName, LastName, ApprovalDate;
   END;
END;