USP_CHARGE_ADD

A generic stored procedure for adding charges.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@STUDENTID	uniqueidentifier	IN
@DUEDATE	date	IN
@AMOUNT	money	IN
@CHARGEDATE	date	IN
@POSTDATE	datetime	IN
@POSTSTATUSCODE	tinyint	IN
@BILLINGITEMID	uniqueidentifier	IN
@STUDENTPROGRESSIONID	uniqueidentifier	IN
@CHARGEID	nvarchar(60)	INOUT
@DESCRIPTION	nvarchar(100)	IN
@STUDENTENROLLMENTID	uniqueidentifier	IN
@AUTOAPPLY	bit	IN
Definition

Copy


CREATE procedure dbo.USP_CHARGE_ADD
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @STUDENTID uniqueidentifier = null,    
    @DUEDATE date = null,  
    @AMOUNT money = 0,
    @CHARGEDATE date = null,
    @POSTDATE datetime = null,
    @POSTSTATUSCODE tinyint = 1,
      @BILLINGITEMID uniqueidentifier = null,
    @STUDENTPROGRESSIONID uniqueidentifier = null,
    @CHARGEID nvarchar(60) = null output,
    @DESCRIPTION nvarchar(100) = null,
    @STUDENTENROLLMENTID uniqueidentifier = null,
    @AUTOAPPLY bit = 0
)
as
begin
    set nocount on;

    if @ID is null
        set @ID = newid();

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

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

    begin try
        -- Transaction type 104 = Charge


        if @POSTSTATUSCODE = 3 and @POSTDATE is not null
            set @POSTDATE = null;

        if (@CHARGEID is null)
        begin
            set @CHARGEID = dbo.UFN_CHARGE_GETNEXTCHARGEID();
        end

        if (@DESCRIPTION is null)
        begin
            select @DESCRIPTION = PRODUCT.NAME
            from dbo.PRODUCT
            where PRODUCT.ID = @BILLINGITEMID;
        end

        exec dbo.USP_FINANCIALTRANSACTION_ADD @ID=@ID, @CHANGEAGENTID=@CHANGEAGENTID, @CONSTITUENTID=@STUDENTID, @TYPECODE=104, @AMOUNT=@AMOUNT, @DATE=@CHARGEDATE, @POSTDATE=@POSTDATE, @POSTSTATUSCODE=@POSTSTATUSCODE, @USERDEFINEDID=@CHARGEID;

        if @AMOUNT <= 0 
            raiserror('ERR_NOT_ALLOW_ZEROAMOUNT', 13, 1);

        if @STUDENTPROGRESSIONID is not null
        begin 
            set @STUDENTENROLLMENTID=null
        end

        -- handle inserting the basic charge data

        insert into dbo.CHARGE (
            ID, 
            DUEDATE, 
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            @ID, 
            @DUEDATE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);


        -- Handling student charge data.


        -- Check the price type first

        declare @PRICETYPECODE tinyint
        select @PRICETYPECODE=PRICETYPECODE from dbo.BILLINGITEM where dbo.BILLINGITEM.ID=@BILLINGITEMID

        if (@PRICETYPECODE=2 or @PRICETYPECODE = 3)
        begin
            insert into dbo.STUDENTCHARGE (
                ID,
                STUDENTPROGRESSIONID,
                EDUCATIONALHISTORYID,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (
                @ID,
                @STUDENTPROGRESSIONID,
                @STUDENTENROLLMENTID,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end

        -- update the line items to handle the billing item

        insert into dbo.CHARGELINEITEM
            (ID,
            BILLINGITEMID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select FINANCIALTRANSACTIONLINEITEM.ID,
                @BILLINGITEMID,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.FINANCIALTRANSACTIONLINEITEM
        where (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID);

        -- Update the basic line items with the new description

        update dbo.FINANCIALTRANSACTIONLINEITEM
            set DESCRIPTION = @DESCRIPTION,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        from dbo.FINANCIALTRANSACTIONLINEITEM
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID;

        -- We can assume that the charge owner owns 100% of this charge so set that up here.

        insert into dbo.CHARGELINEITEMRESPONSIBILITY
            (ID, 
            FINANCIALTRANSACTIONLINEITEMID, 
            CONSTITUENTID, 
            PERCENTAGE, 
            DATEADDED, 
            ADDEDBYID, 
            DATECHANGED, 
            CHANGEDBYID)
        select newid(), 
                FINANCIALTRANSACTIONLINEITEM.ID,
                @STUDENTID,
                100,
                DATECHANGED,
                CHANGEDBYID,
                DATECHANGED,
                CHANGEDBYID
        from dbo.FINANCIALTRANSACTIONLINEITEM
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

        if @AUTOAPPLY=1 
          exec dbo.USP_CHARGE_AUTOAPPLY @ID,@CHANGEAGENTID

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

return 0;
end