usp_inserttimesheet.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE OR REPLACE PROCEDURE usp_InsertTimeSheet
(
   inTimeSheetID      CHAR,
   inUserID           CHAR,
   inWeekEndingDate   DATE
)
AS

varGroupID CHAR(36);
CURSOR Project_Cursor IS SELECT ProjectID 
   FROM GroupProjects WHERE GroupID = varGroupID;
varProjectID CHAR(36);
varGuid CHAR(32);
varTimeSheetItemID CHAR(36);
varTimeSheetDate DATE;

BEGIN
   -- Get the GroupID that the user belongs to
   SELECT GroupID INTO varGroupID FROM Users WHERE UserID = inUserID;

   BEGIN
      -- Insert the time sheet
     INSERT INTO TimeSheets
        (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
        ManagerID, LastUpdateDate)
        VALUES(inTimeSheetID, inUserID, inWeekEndingDate, 0, NULL,
        NULL, SYSDATE);
      EXCEPTION
         WHEN OTHERS THEN
         ROLLBACK;
         RAISE_APPLICATION_ERROR( -20999,'Insert into TimeSheets failed.');
         RETURN;
   END;

   BEGIN
      -- Set the initial time sheet date to the beginning of the week
      varTimeSheetDate := inWeekEndingDate - 4;

      -- Set up a loop to insert time sheet items for 5 days
      FOR i IN 1 .. 5

         LOOP
         -- Open the cursor
         OPEN Project_Cursor;

            LOOP
            -- Fetch a row of data from the cursor into our variable
            FETCH Project_Cursor INTO varProjectID;
            -- Exit when no more data
            EXIT WHEN Project_Cursor%NOTFOUND;
            -- Get a Guid
            SELECT SYS_GUID() INTO varGuid FROM DUAL;
            -- Format the Guid with dashes in our variable
            varTimeSheetItemID := SUBSTR(varGuid,1,8) || '-' ||
               SUBSTR(varGuid,9,4) || '-' ||
               SUBSTR(varGuid,13,4) || '-' ||
               SUBSTR(varGuid,17,4) || '-' ||
               SUBSTR(varGuid,21,12);
            BEGIN
               -- Insert the time sheet item
               INSERT INTO TimeSheetItems
                  (TimeSheetItemID, TimeSheetID, ProjectID, Hours, 
                  TimeSheetDate)
                  VALUES(varTimeSheetItemID, inTimeSheetID, varProjectID, 0, 
                  varTimeSheetDate);
               EXCEPTION
                  WHEN OTHERS THEN
                  ROLLBACK;
                  RAISE_APPLICATION_ERROR( -20999,
                     'Insert into TimeSheetItems failed.');
                  RETURN;
                  END;
            END LOOP;

         CLOSE Project_Cursor;

         -- Increment the date by one day
         varTimeSheetDate := varTimeSheetDate + 1;
         END LOOP;
      END;

   -- Commit all inserts
   COMMIT;
END;