SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SSS_StudentEnrollmentsDeleteFromWeb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SSS_StudentEnrollmentsDeleteFromWeb]
GO



--Gayathri J, 5th Dec, 2004
--Changed the proc to call Enrollments delete proc with appropriate parameters and removed the remaining code.
/* COMMENTS FOR API
Procedure Name: SSS_StudentEnrollmentsDeleteFromWeb
Function Description : Used to delete enrollment records for self service.
Parameters :
    @lSSS_StudentEnrollmentsID  - Specifies the SSS_StudentEnrollments identifier
    @UserGUID   - User GUID of the logged on user
    @RespGUID   - Responsibility GUID of the user who has logged on
    @lInstallationsID   - Specifies the installation in which the record has to be created
END COMMENTS FOR API */

CREATE PROCEDURE   [dbo].SSS_StudentEnrollmentsDeleteFromWeb
@lSSS_StudentEnrollmentsID  INT,
@UserGUID                   UNIQUEIDENTIFIER,
@RespGUID                   UNIQUEIDENTIFIER,
@lInstallationsID           INT

AS
BEGIN
DECLARE
@lPromotionOffStatusID      INT,
@sPromotionOffStatusName    ShortString,
@lWLHoldStatusID            INT,
@sWLHoldStatusName          ShortString,
@sIsWaitlisted              ShortString
    SET NOCOUNT ON

    SET CONTEXT_INFO @UserGUID
    SELECT
        @lPromotionOffStatusID = CAST(IP.ProfileValue AS INT),
        @sPromotionOffStatusName = S.Name
    FROM   [dbo].CMN_InstallationProfiles IP
        INNER JOIN   [dbo].CMN_Profiles P ON
            P.CMN_ProfilesID = IP.CMN_ProfilesID
        INNER JOIN   [dbo].CMN_Statuses S ON
            S.CMN_StatusesID = CAST(IP.ProfileValue AS INT)
    WHERE
        P.ProfileName = 'SSS_WL_Promotion_Off_Status'
        AND P.InstallationsID = @lInstallationsID

    SELECT
        @lWLHoldStatusID = CAST(IP.ProfileValue AS INT),
        @sWLHoldStatusName = S.Name
    FROM   [dbo].CMN_InstallationProfiles IP
        INNER JOIN   [dbo].CMN_Profiles P ON
            P.CMN_ProfilesID = IP.CMN_ProfilesID
        INNER JOIN   [dbo].CMN_Statuses S ON
            S.CMN_StatusesID = CAST(IP.ProfileValue AS INT)
    WHERE
        P.ProfileName = 'SSS_WL_Promotion_Off_Hold_Release_Status'
        AND P.InstallationsID = @lInstallationsID

     SELECT @sIsWaitlisted = IsWaitListed
     FROM   SSS_StudentEnrollments
     WHERE  SSS_StudentEnrollmentsID = @lSSS_StudentEnrollmentsID

    IF @sIsWaitlisted = 'Y'
    BEGIN
        EXEC   [dbo].SSS_StudentEnrollmentsDelete @lSSS_StudentEnrollmentsID = @lSSS_StudentEnrollmentsID,
                                        @TStamp = NULL,
                                        @UserGUID = @UserGUID,
                                        @RespGUID = @RespGUID,
                                        @lPromotionOffStatusID = @lPromotionOffStatusID,
                                        @sPromotionOffStatusName = @sPromotionOffStatusName,
                                        @lWLHoldStatusID = @lWLHoldStatusID,
                                        @sWLHoldStatusName = @sWLHoldStatusName,
                                        @TStampCheck = 'N',
                                        @sFireAutoPromoteOff = 'Y'
    END
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[SSS_StudentEnrollmentsDeleteFromWeb]  TO [db_ExecAll]
GO