timesheetpackagebody.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE OR REPLACE PACKAGE BODY TimeSheetPackage 
AS
   PROCEDURE usp_SelectTimeSheet (inUserID IN CHAR, 
      inWeekEndingDate IN DATE, results_cursor OUT CURSOR_TYPE)
  AS
  BEGIN
    OPEN results_cursor FOR
   -- Select the TimeSheetID, Submitted, ProjectID and ProjectName
   SELECT TimeSheets.TimeSheetID, Submitted, Projects.ProjectID, ProjectName, 
      -- Select the TimeSheetItemID and Hours for Monday
      Monday.TimeSheetItemID AS MondayTimeSheetItemID, 
      Monday.Hours AS MondayHours,
      -- Select the TimeSheetItemID and Hours for Tuesday
      Tuesday.TimeSheetItemID AS TuesdayTimeSheetItemID, 
      Tuesday.Hours AS TuesdayHours, 
      -- Select the TimeSheetItemID and Hours for Wednesday
      Wednesday.TimeSheetItemID AS WednesdayTimeSheetItemID, 
      Wednesday.Hours AS WednesdayHours, 
      -- Select the TimeSheetItemID and Hours for Thursday
      Thursday.TimeSheetItemID AS ThursdayTimeSheetItemID, 
      Thursday.Hours AS ThursdayHours,
      -- Select the TimeSheetItemID and Hours for Friday
      Friday.TimeSheetItemID AS FridayTimeSheetItemID, 
      Friday.Hours AS FridayHours
   -- TimeSheets is the main table
   FROM TimeSheets
   -- Join TimeSheetItems table for Monday's data
   LEFT OUTER JOIN TimeSheetItems Monday ON 
      TimeSheets.TimeSheetID = Monday.TimeSheetID 
      AND Monday.TimeSheetDate = inWeekEndingDate - 4
   -- Join Projects table for Project names
   LEFT OUTER JOIN Projects ON 
      Monday.ProjectID = Projects.ProjectID
   -- Join TimeSheetItems table for Tuesday's data
   LEFT OUTER JOIN TimeSheetItems Tuesday ON 
      TimeSheets.TimeSheetID = Tuesday.TimeSheetID 
      AND Tuesday.ProjectID = Monday.ProjectID 
      AND Tuesday.TimeSheetDate = inWeekEndingDate - 3
   -- Join TimeSheetItems table for Wednesday's data
   LEFT OUTER JOIN TimeSheetItems Wednesday ON 
      TimeSheets.TimeSheetID = Wednesday.TimeSheetID 
      AND Wednesday.ProjectID = Monday.ProjectID 
      AND Wednesday.TimeSheetDate = inWeekEndingDate - 2
   -- Join TimeSheetItems table for Thursday's data
   LEFT OUTER JOIN TimeSheetItems Thursday 
      ON TimeSheets.TimeSheetID = Thursday.TimeSheetID 
      AND Thursday.ProjectID = Monday.ProjectID 
      AND Thursday.TimeSheetDate = inWeekEndingDate - 1
   -- Join TimeSheetItems table for Friday's data
   LEFT OUTER JOIN TimeSheetItems Friday ON 
      TimeSheets.TimeSheetID = Friday.TimeSheetID 
      AND Friday.ProjectID = Monday.ProjectID 
      AND Friday.TimeSheetDate = inWeekEndingDate
   -- Search criteria is the WeekEndingDate and UserID
   WHERE WeekEndingDate = inWeekEndingDate
      AND UserID = inUserID
   -- Order the results by the SequenceNumber of the projects
   ORDER BY SequenceNumber;
   END;
END;