USP_DATAFORMTEMPLATE_ADD_RECEIVABLECREDIT

The save procedure used by the add dataform template "Credit Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@STUDENTID	uniqueidentifier	IN	Name
@AMOUNT	money	IN	Credit amount
@CREDITDATE	date	IN	Credit date
@POSTDATE	datetime	IN	Post date
@POSTSTATUSCODE	tinyint	IN	Post status
@BILLINGITEMID	uniqueidentifier	IN	Crediting for
@DESCRIPTION	nvarchar(100)	IN	Description
@SCHOOLID	uniqueidentifier	IN	School
@GRADELEVELID	uniqueidentifier	IN	Grade level
@BILLINGITEMTYPE	tinyint	IN	Type
@APPLICATIONS	xml	IN	Applications

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECEIVABLECREDIT
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @STUDENTID uniqueidentifier = null,    
    @AMOUNT money = 0,
    @CREDITDATE date = null,
    @POSTDATE datetime = null,
    @POSTSTATUSCODE tinyint = 1,
  @BILLINGITEMID uniqueidentifier = null,
  @DESCRIPTION nvarchar(100) = null,
    @SCHOOLID uniqueidentifier = null,
    @GRADELEVELID uniqueidentifier = null,
    @BILLINGITEMTYPE tinyint = 0,
  @APPLICATIONS xml = null
)
as

set nocount on;

begin try

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

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

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

    declare @CREDITID nvarchar(60);
    declare @STUDENTPROGRESSIONID uniqueidentifier;
      declare @STUDENTENROLLMENTID uniqueidentifier;

    -- Transaction type 106 = Credit


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

      set @CREDITID = dbo.UFN_FINANCIALTRANSACTION_GETNEXTFINANCIALTRANSACTIONID(106);

      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=106, @AMOUNT=@AMOUNT, @DATE=@CREDITDATE, @POSTDATE=@POSTDATE, @POSTSTATUSCODE=@POSTSTATUSCODE, @USERDEFINEDID=@CREDITID;

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

      -- Check the price type first

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

      if @SCHOOLID is not null
      begin    
          if ((@PRICETYPECODE=2) or (@PRICETYPECODE = 3))
              select @STUDENTPROGRESSIONID =dbo.UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE(@CREDITDATE, @STUDENTID, @SCHOOLID)

          if (@STUDENTPROGRESSIONID is null and @PRICETYPECODE = 3)
                set @STUDENTENROLLMENTID = dbo.UFN_STUDENT_GETENROLLMENTBYDATE(@STUDENTID, @SCHOOLID, @CREDITDATE)
      end

        insert into dbo.RECEIVABLECREDIT (
            ID,
            STUDENTPROGRESSIONID,
            EDUCATIONALHISTORYID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
            @ID,
            @STUDENTPROGRESSIONID,
            @STUDENTENROLLMENTID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        -- update the line items to handle the billing item

        insert into dbo.RECEIVABLECREDITLINEITEM
            (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;  

        declare @RECEIVABLECREDITLINEITEMID uniqueidentifier;
        select top 1 @RECEIVABLECREDITLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        from FINANCIALTRANSACTION
            inner join FINANCIALTRANSACTIONLINEITEM
                on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        where (FINANCIALTRANSACTION.ID = @ID) and
                (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0);

        set @APPLICATIONS = 
            (select ID uniqueidentifier,
                    @RECEIVABLECREDITLINEITEMID as SOURCELINEITEMID,
                    TARGETLINEITEMID,
                    AMOUNT,
                    POSTDATE,
                    POSTSTATUSCODE
            from dbo.UFN_RECEIVABLEPAYMENTCREDITAPPLICATIONS_FROMITEMLISTXML(@APPLICATIONS)
            for xml raw('ITEM'),type,elements,root('APPLICATIONS'),BINARY BASE64);

        exec dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT @RECEIVABLECREDITLINEITEMID, null, @CHANGEAGENTID, @APPLICATIONS

  end try

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

return 0;