USP_DATAFORMTEMPLATE_ADD_TRANSACTIONTOGROUP

The save procedure used by the add dataform template "Add a Transaction to a Group Add Data 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.
@IDSETREGISTERID	uniqueidentifier	IN	Selection
@CHARGEDATE	date	IN	Charge date
@DUEDATE	date	IN	Due date
@POSTDATE	date	IN	Post date
@POSTSTATUSCODE	tinyint	IN	Post status
@BILLINGITEMID	uniqueidentifier	IN	Charging for
@DESCRIPTION	nvarchar(100)	IN	Description
@SCHOOLS	xml	IN	Add charges for
@AMOUNT	money	IN	Charge amount
@CREATEOUTPUTIDSET	bit	IN	Create output selection from results
@OUTPUTSELECTIONTYPEID	uniqueidentifier	IN	Selection type
@OUTPUTSELECTIONNAME	nvarchar(100)	IN	Selection name
@OVERWRITEOUTPUTSELECTION	bit	IN	Overwrite existing selection
@CREATEEXCEPTIONIDSET	bit	IN	Create exception selection from results
@EXCEPTIONSELECTIONTYPEID	uniqueidentifier	IN	Selection type
@EXCEPTIONSELECTIONNAME	nvarchar(100)	IN	Selection name
@OVERWRITEEXCEPTIONSELECTION	bit	IN	Overwrite existing selection
@STUDENTS	xml	IN
@BILLINGITEMTYPE	tinyint	IN	Type
Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_TRANSACTIONTOGROUP
        (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,            
            @IDSETREGISTERID uniqueidentifier = null,
            @CHARGEDATE date = null,
            @DUEDATE date = null,
            @POSTDATE date = null,
            @POSTSTATUSCODE tinyint = 1,
            @BILLINGITEMID uniqueidentifier = null,
            @DESCRIPTION nvarchar(100) = null,
            @SCHOOLS xml= null,            
            @AMOUNT money = 0,
            @CREATEOUTPUTIDSET bit = null,
            @OUTPUTSELECTIONTYPEID uniqueidentifier = null,
            @OUTPUTSELECTIONNAME nvarchar(100) = null,
            @OVERWRITEOUTPUTSELECTION bit = null,
            @CREATEEXCEPTIONIDSET bit = null,
            @EXCEPTIONSELECTIONTYPEID uniqueidentifier = null,
            @EXCEPTIONSELECTIONNAME nvarchar(100) = null,
            @OVERWRITEEXCEPTIONSELECTION bit = null,
            @STUDENTS xml = null,
            @BILLINGITEMTYPE tinyint = 0
        )
        as

        set nocount on;

        begin try
            declare @pricetypecode integer

            select @pricetypecode=dbo.UFN_BILLINGITEM_GETPRICETYPECODE(@BILLINGITEMID)

            if @pricetypecode=1
            begin
                if @AMOUNT<=0
                    raiserror('ERR_AMOUNT_GREATER_THAN_ZERO', 13, 1);
            end

            declare @SCHOOL table
            (
            SCHOOLID uniqueidentifier not null            
            )

            if @pricetypecode=2 or @pricetypecode=3
            begin
                insert into @SCHOOL (SCHOOLID)    
                SELECT T.C.value('(SCHOOLID)[1]','uniqueidentifier') AS 'SCHOOLID'                
                FROM @SCHOOLS.nodes('/SCHOOLS/ITEM') T(C)

                if (select count(SCHOOLID) from @SCHOOL)=0
                raiserror('ERR_SCHOOL_REQUIRED', 13, 1);            
            end            

            if @CREATEOUTPUTIDSET = 1 AND LEN(@OUTPUTSELECTIONNAME) = 0
                raiserror('ERR_OUTPUTSELECTIONNAME_REQUIRED', 13, 1);

            if @CREATEEXCEPTIONIDSET = 1 AND LEN(@EXCEPTIONSELECTIONNAME) = 0
                raiserror('ERR_EXCEPTIONSELECTIONNAME_REQUIRED', 13, 1);

            If @CREATEOUTPUTIDSET = 1 AND (select dbo.UFN_BUSINESSPROCESS_IDSETCANBECREATED(@OUTPUTSELECTIONNAME))=0
                raiserror('ERR_OUTPUTSELECTIONNAME_CANNOTOVERWRITEOUTPUTSELECTION', 13, 1);

            if @CREATEOUTPUTIDSET = 1 AND @OVERWRITEOUTPUTSELECTION =0 AND exists(select ID from dbo.IDSETREGISTER where NAME = @OUTPUTSELECTIONNAME)
                raiserror('ERR_OUTPUTSELECTIONNAME_OUTPUTSELECTIONEXISTS', 13, 1);

            If @CREATEEXCEPTIONIDSET = 1 AND (select dbo.UFN_BUSINESSPROCESS_IDSETCANBECREATED(@EXCEPTIONSELECTIONNAME))=0
                raiserror('ERR_EXCEPTIONSELECTIONNAME_CANNOTOVERWRITEEXCEPTIONSELECTION', 13, 1);

            if @CREATEEXCEPTIONIDSET = 1 AND @OVERWRITEEXCEPTIONSELECTION =0 AND exists(select ID from dbo.IDSETREGISTER where NAME = @EXCEPTIONSELECTIONNAME)
                raiserror('ERR_EXCEPTIONSELECTIONNAME_EXCEPTIONSELECTIONEXISTS', 13, 1);

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

            declare @CURRENTDATE datetime
            set @CURRENTDATE = getdate()

            set @OUTPUTSELECTIONTYPEID = case @CREATEOUTPUTIDSET when 1 then @OUTPUTSELECTIONTYPEID else null end
            set @EXCEPTIONSELECTIONTYPEID = case @CREATEEXCEPTIONIDSET when 1 then @EXCEPTIONSELECTIONTYPEID else null end


            declare @STUDENTRECORDS table
            (    
                ID uniqueidentifier not null,
                INCLUDE bit null,
                STUDENTID uniqueidentifier null, 
                STUDENTNAME nvarchar(210) null,
                SCHOOLID uniqueidentifier null,
                SCHOOL nvarchar(100) null,
                GRADELEVELID uniqueidentifier null,
                GRADELEVEL nvarchar(10) null,
                STUDENTPROGRESSIONID uniqueidentifier null,
                ENROLLMENTID uniqueidentifier null,
                AMOUNT money null,
                MESSAGE nvarchar(100) null,
                TRANSACTIONID uniqueidentifier,
                CHARGEID nvarchar(60)
            )

            declare @handle int;

            exec sp_xml_preparedocument @handle output, @STUDENTS
            insert into @STUDENTRECORDS (ID, INCLUDE, STUDENTNAME, STUDENTID, SCHOOL, GRADELEVEL, AMOUNT, MESSAGE)    
            select newid(), INCLUDE, STUDENTNAME, STUDENTID, SCHOOL, GRADELEVEL, TRANSACTIONAMOUNT, MESSAGE FROM OPENXML (@handle, '/STUDENTS/ITEM', 2) WITH 
              (INCLUDE bit,
                STUDENTNAME nvarchar(210),
                STUDENTID uniqueidentifier,
                SCHOOL nvarchar(100),                
                GRADELEVEL nvarchar(100),
                TRANSACTIONAMOUNT money,
                MESSAGE nvarchar(100)
              )
            EXEC sp_xml_removedocument @handle

            update s set message='Charge amount must be greater than zero.' from @STUDENTRECORDS s where AMOUNT<=0

            declare @TOTALTRANSACTIONS integer
            select @TOTALTRANSACTIONS =count(ID) from @STUDENTRECORDS s where INCLUDE=1 and AMOUNT>0

            if @TOTALTRANSACTIONS =0
                raiserror('ERR_ATLEASTONERECORD_REQUIRED', 13, 1);

      if @pricetypecode=2 or @pricetypecode=3
            begin

      update @STUDENTRECORDS set STUDENTPROGRESSIONID=STUDENTPROGRESSION.ID
            from @STUDENTRECORDS S inner join dbo.CONSTITUENT C on S.STUDENTID=C.ID
            inner join dbo.EDUCATIONALHISTORY on S.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
            inner join dbo.STUDENTPROGRESSION on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]
            inner join dbo.SCHOOLGRADELEVEL on dbo.STUDENTPROGRESSION.[SCHOOLGRADELEVELID] = dbo.SCHOOLGRADELEVEL.[ID]        
            inner join dbo.CONSTITUENT on dbo.CONSTITUENT.ID=SCHOOLGRADELEVEL.SCHOOLID
            inner join dbo.GRADELEVEL on dbo.SCHOOLGRADELEVEL.GRADELEVELID=dbo.GRADELEVEL.ID
            inner join @SCHOOL sch on sch.SCHOOLID = dbo.SCHOOLGRADELEVEL.SCHOOLID
            where dbo.STUDENTPROGRESSION.ID=dbo.UFN_STUDENT_GETSTUDENTPROGRESSIONBYDATE(@CHARGEDATE, S.STUDENTID, sch.SCHOOLID)
      and dbo.GRADELEVEL.ABBREVIATION=s.GRADELEVEL and dbo.CONSTITUENT.KEYNAME=s.SCHOOL
      and s.INCLUDE=1 AND s.AMOUNT>0

      update @STUDENTRECORDS set ENROLLMENTID=EDUCATIONALHISTORY.ID
          from @STUDENTRECORDS S inner join dbo.EDUCATIONALHISTORY on S.STUDENTID = EDUCATIONALHISTORY.CONSTITUENTID
          inner join dbo.CONSTITUENT C on S.STUDENTID=C.ID
          inner join dbo.CONSTITUENT on dbo.EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = CONSTITUENT.ID
          inner join @SCHOOL sch on sch.SCHOOLID = CONSTITUENT.ID
          where S.STUDENTPROGRESSIONID is null AND dbo.EDUCATIONALHISTORY.ID=dbo.UFN_STUDENT_GETENROLLMENTBYDATE(S.STUDENTID, sch.SCHOOLID, @CHARGEDATE)
          and s.INCLUDE=1 AND s.AMOUNT>0 and dbo.CONSTITUENT.KEYNAME=s.SCHOOL
    end


            declare @BILLINGITEMNAME nvarchar(100);
            select @BILLINGITEMNAME = NAME from dbo.PRODUCT where ID=@BILLINGITEMID

            declare @ROWID uniqueidentifier
            declare @STUDENTID uniqueidentifier
            declare @STUDENTPROGRESSIONID uniqueidentifier
            declare @ENROLLMENTID uniqueidentifier        
            declare @TRANSACTIONID uniqueidentifier
            declare @TRANSACTIONAMOUNT money
            declare @CHARGEID nvarchar(60)

          declare @AUTOAPPLY bit
          select @AUTOAPPLY=CHARGE from dbo.APPLICATIONRULES 

            declare STUDENTTRANSACTIONCURSOR cursor local for
                select ID,
                    STUDENTID,
                    STUDENTPROGRESSIONID,
                    ENROLLMENTID,
                    AMOUNT,
                    TRANSACTIONID
                from @STUDENTRECORDS
                where INCLUDE=1 AND AMOUNT>0
                for update of TRANSACTIONID

            open STUDENTTRANSACTIONCURSOR
            fetch next from STUDENTTRANSACTIONCURSOR
            into @ROWID, @STUDENTID, @STUDENTPROGRESSIONID, @ENROLLMENTID, @TRANSACTIONAMOUNT, @TRANSACTIONID ;

            while @@fetch_status = 0
            begin
                set @CHARGEID = null;

                exec dbo.USP_CHARGE_ADD @ID=@TRANSACTIONID output, @CHANGEAGENTID=@CHANGEAGENTID, @STUDENTID=@STUDENTID, @DUEDATE=@DUEDATE, @AMOUNT=@TRANSACTIONAMOUNT, @POSTDATE=@POSTDATE, @POSTSTATUSCODE=@POSTSTATUSCODE, @CHARGEDATE=@CHARGEDATE, @BILLINGITEMID=@BILLINGITEMID, @STUDENTPROGRESSIONID=@STUDENTPROGRESSIONID, @CHARGEID=@CHARGEID output, @DESCRIPTION=@DESCRIPTION, @STUDENTENROLLMENTID=@ENROLLMENTID, @AUTOAPPLY=@AUTOAPPLY;

                update S
                    set CHARGEID = @CHARGEID,
                        TRANSACTIONID = @TRANSACTIONID
                    from @STUDENTRECORDS as S
                    where (S.ID = @ROWID);

                fetch next from STUDENTTRANSACTIONCURSOR
                into @ROWID, @STUDENTID, @STUDENTPROGRESSIONID, @ENROLLMENTID, @TRANSACTIONAMOUNT, @TRANSACTIONID;
            end

            close STUDENTTRANSACTIONCURSOR
            deallocate STUDENTTRANSACTIONCURSOR

            -- Create success and exception output tables.    

            if @CREATEOUTPUTIDSET =1
            begin                            
                delete from dbo.BILLING_ADDATRANSACTIONTOAGROUP_OUTPUT                
                insert into dbo.BILLING_ADDATRANSACTIONTOAGROUP_OUTPUT (STUDENTID) select STUDENTID from @STUDENTRECORDS where INCLUDE=1 and AMOUNT>0 and ((MESSAGE is null) or LEN(MESSAGE)=0)                
            end

            if @CREATEEXCEPTIONIDSET =1
            begin            
                delete from dbo.BILLING_ADDATRANSACTIONTOAGROUP_EXCEPTION                            
                insert into dbo.BILLING_ADDATRANSACTIONTOAGROUP_EXCEPTION (STUDENTID) select STUDENTID from @STUDENTRECORDS where LEN(MESSAGE)>0                
            end

        end try

        begin catch
            exec dbo.USP_RAISE_ERROR
            return 1
        end catch

        return 0