USP_REPORT_BILLINGGENERATETRANSACTIONSCHARGEPROCCESSREPORT_SUMMARY

                create procedure dbo.USP_REPORT_BILLINGGENERATETRANSACTIONSCHARGEPROCCESSREPORT_SUMMARY
                (
                    @ID nvarchar(36) -- Process ID

                )
                as
                begin
                    declare @SUCCESS_TABLENAME nvarchar(255);
                    select @SUCCESS_TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
                            from dbo.BUSINESSPROCESSSTATUS 
                            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
                            where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'SUCCESS';

                    declare @EXCEPTION_TABLENAME nvarchar(255);
                    select @EXCEPTION_TABLENAME = BUSINESSPROCESSOUTPUT.TABLENAME 
                            from dbo.BUSINESSPROCESSSTATUS 
                            inner join dbo.BUSINESSPROCESSOUTPUT on BUSINESSPROCESSSTATUS.ID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID
                            where BUSINESSPROCESSSTATUS.ID = @ID AND BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION';

                    begin try
                        declare @SQL nvarchar(4000);
                        set @SQL = 'select BILLINGGENERATETRANSACTIONSPROCESS.TRANSACTIONDATE as CHARGEDATE,
                                            case when BILLINGGENERATETRANSACTIONSPROCESS.IDSETREGISTERID is null then ''<All Students>'' else IDSETREGISTER.NAME end as STUDENTSNAME,
                                            case when (exists (select ID from dbo.BILLINGGENERATETRANSACTIONSCHARGEPROCESSBILLINGITEMS where BILLINGGENERATETRANSACTIONSCHARGEPROCESSBILLINGITEMS.BILLINGGENERATETRANSACTIONSPROCESSID = BILLINGGENERATETRANSACTIONSPROCESS.ID)) then 
                                                (select dbo.UDA_BUILDLIST(BILLINGITEMS.NAME) from dbo.UFN_BILLINGGENERATETRANSACTIONSPROCESS_GETBILLINGITEMS(BILLINGGENERATETRANSACTIONSPROCESS.ID) as BILLINGITEMS) 
                                            else 
                                                ''<All Billing Items>'' 
                                            end as BILLINGITEMSTEXT,
                                            case when (exists (select ID from dbo.BILLINGGENERATETRANSACTIONSPROCESSSCHOOL where BILLINGGENERATETRANSACTIONSPROCESSSCHOOL.BILLINGGENERATETRANSACTIONSPROCESSID = BILLINGGENERATETRANSACTIONSPROCESS.ID)) then 
                                                (select dbo.UDA_BUILDLIST(SCHOOLS.NAME) from dbo.UFN_BILLINGGENERATETRANSACTIONSPROCESS_GETSCHOOLS(BILLINGGENERATETRANSACTIONSPROCESS.ID) as SCHOOLS) 
                                            else 
                                                ''<All Schools>'' 
                                            end as SCHOOLSTEXT,
                                            (select count(SUCCESS_DATA.STUDENTID) from dbo.' + @SUCCESS_TABLENAME + ' as SUCCESS_DATA) as SUCCESSCOUNT,
                                            (select sum(SUCCESS_DATA.AMOUNT) from dbo.' + @SUCCESS_TABLENAME + ' as SUCCESS_DATA) as SUCCESSAMOUNT,
                                            (select count(EXCEPTION_DATA.STUDENTID) from dbo.' + @EXCEPTION_TABLENAME + ' as EXCEPTION_DATA) as EXCEPTIONCOUNT
                                    from dbo.BILLINGGENERATETRANSACTIONSPROCESS
                                        inner join dbo.BUSINESSPROCESSSTATUS
                                            on BUSINESSPROCESSSTATUS.BUSINESSPROCESSPARAMETERSETID = BILLINGGENERATETRANSACTIONSPROCESS.ID
                                        left outer join dbo.IDSETREGISTER
                                            on BILLINGGENERATETRANSACTIONSPROCESS.IDSETREGISTERID = IDSETREGISTER.ID
                                    where BUSINESSPROCESSSTATUS.ID = ''' + @ID + ''';';

                        exec sp_executesql @SQL;
          end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch 
                end