USP_ACADEMICCATALOGPROGRAM_DELETE

Executes the "Academic Catalog Program: Delete" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.
Definition

Copy


CREATE procedure dbo.USP_ACADEMICCATALOGPROGRAM_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    --cache current context information

    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();

    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    --remove from Educational history update batch

    update
        dbo.BATCHEDUCATIONALHISTORYUPDATE
    set
        ACADEMICCATALOGPROGRAMID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where
        ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEGREETYPE
    from dbo.ACADEMICCATALOGDEGREETYPE
    inner join ACADEMICCATALOGSUBDEPARTMENT 
        on ACADEMICCATALOGSUBDEPARTMENT.ID = ACADEMICCATALOGDEGREETYPE.ACADEMICCATALOGSUBDEPARTMENTID
    inner join ACADEMICCATALOGDEPARTMENT 
        on ACADEMICCATALOGDEPARTMENT.ID = ACADEMICCATALOGSUBDEPARTMENT.ACADEMICCATALOGDEPARTMENTID
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEGREETYPE
    from dbo.ACADEMICCATALOGDEGREETYPE
    inner join ACADEMICCATALOGDEPARTMENT 
        on ACADEMICCATALOGDEPARTMENT.ID = ACADEMICCATALOGDEGREETYPE.ACADEMICCATALOGDEPARTMENTID
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEGREETYPE
    from dbo.ACADEMICCATALOGDEGREETYPE
    inner join ACADEMICCATALOGSUBDEPARTMENT 
        on ACADEMICCATALOGSUBDEPARTMENT.ID = ACADEMICCATALOGDEGREETYPE.ACADEMICCATALOGSUBDEPARTMENTID
    inner join ACADEMICCATALOGDEPARTMENT 
        on ACADEMICCATALOGDEPARTMENT.ID = ACADEMICCATALOGSUBDEPARTMENT.ACADEMICCATALOGDEPARTMENTID
    inner join ACADEMICCATALOGDIVISION
        on ACADEMICCATALOGDIVISION.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDIVISIONID
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDIVISION.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEGREETYPE
    from dbo.ACADEMICCATALOGDEGREETYPE
    inner join ACADEMICCATALOGDEPARTMENT 
        on ACADEMICCATALOGDEPARTMENT.ID = ACADEMICCATALOGDEGREETYPE.ACADEMICCATALOGDEPARTMENTID
    inner join ACADEMICCATALOGDIVISION
        on ACADEMICCATALOGDIVISION.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDIVISIONID
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDIVISION.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEPARTMENT
    from dbo.ACADEMICCATALOGDEPARTMENT
    inner join ACADEMICCATALOGDIVISION
        on ACADEMICCATALOGDIVISION.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDIVISIONID
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDIVISION.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    delete from dbo.ACADEMICCATALOGDEPARTMENT
    from dbo.ACADEMICCATALOGDEPARTMENT
    inner join dbo.ACADEMICCATALOGCOLLEGE
        on ACADEMICCATALOGCOLLEGE.ID = ACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGCOLLEGEID
    inner join dbo.ACADEMICCATALOGDEGREE
        on ACADEMICCATALOGDEGREE.ID = ACADEMICCATALOGCOLLEGE.ACADEMICCATALOGDEGREEID
    where ACADEMICCATALOGDEGREE.ACADEMICCATALOGPROGRAMID = @ID;

    exec dbo.USP_ACADEMICCATALOGPROGRAM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    -- reset CONTEXT_INFO to previous value

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    return 0;