timesheetsqtdpackagebody.sql

来自「Beginning VB.NET DatabasesAll_Code.rar」· SQL 代码 · 共 29 行

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

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