timesheetsytdpackagebody.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE OR REPLACE PACKAGE BODY TimeSheetsYTDPackage 
AS
   PROCEDURE usp_SelectTimeSheetsYTD (inManagerID IN CHAR, 
      results_cursor OUT CURSOR_TYPE)
   AS
   -- Declare local variables
   varFirstDayOfYear DATE;
   varWeekEndingDate DATE;
   BEGIN
      -- Calculate the first day of the year date and current week ending date
      SELECT TRUNC(SYSDATE, 'YEAR')  INTO varFirstDayOfYear FROM DUAL;
      SELECT (SYSDATE + (6 - TO_CHAR(SYSDATE, 'D'))) INTO varWeekEndingDate 
      FROM DUAL;

      OPEN results_cursor FOR
      -- Select year-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 varFirstDayOfYear AND varWeekEndingDate
      AND Submitted = 1
      GROUP BY WeekEndingDate, FirstName, LastName, ApprovalDate;
   END;
END;