USP_DATAFORMTEMPLATE_VIEW_GLSUMMARYINFO

The load procedure used by the view dataform template "GL Mapping Summary View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PROJECTCODEMISSING	bit	INOUT	PROJECTCODEMISSING
@ACCOUNTCODEMISSING	bit	INOUT	ACCOUNTCODEMISSING
@ACCOUNTNUMBERMISSING	bit	INOUT	ACCOUNTNUMBERMISSING
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GLSUMMARYINFO
                (
                    @DATALOADED bit = 0 output,
                    @PROJECTCODEMISSING bit = null output,
                    @ACCOUNTCODEMISSING bit = null output,
                    @ACCOUNTNUMBERMISSING bit = null output
                )
                as
                set nocount on;

                set @DATALOADED = 1;

                select    @PROJECTCODEMISSING = case when count(*) > 0 then 1 else 0 end 
                        from dbo.DESIGNATION where coalesce(PROJECTCODE, '') = '';

                if @PROJECTCODEMISSING = 0
                    select    @PROJECTCODEMISSING = case when count(*) > 0 then 1 else 0 end 
                            from dbo.EVENT
                            left join dbo.EVENTGLMAPPING on EVENT.ID = EVENTGLMAPPING.ID
                            where coalesce(PROJECTCODE, '') = '';

                if @PROJECTCODEMISSING = 0
                    select    @PROJECTCODEMISSING = case when count(*) > 0 then 1 else 0 end 
                            from dbo.MEMBERSHIPLEVEL
                            left join dbo.MEMBERSHIPGLMAPPING on MEMBERSHIPLEVEL.ID = MEMBERSHIPGLMAPPING.ID
                            where coalesce(PROJECTCODE, '') = '';

                select @ACCOUNTCODEMISSING = case when count(*) > 0 then 1 else 0 end
                        from 
                            dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP
                            left join dbo.GLACCOUNTTYPEMAPPING on MAP.GLACCOUNTTYPEMAPPINGID = GLACCOUNTTYPEMAPPING.ID
                        where 
                            coalesce(GLACCOUNTTYPEMAPPING.GLCODE, '') = ''
                            and (MAP.PAYMENTMETHODCODE <> 11 or dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1)  --Standing Order, UK Product Flag

                            and (MAP.REVENUETRANSACTIONTYPECODE <> 202 or dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1)  --Gift Aid, UK Product Flag

                            and (MAP.REVENUETRANSACTIONTYPECODE <> 7 or dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0321D454-B28B-4FAB-BD58-F2ECC7050594') = 1) -- Auction donation, Auction Product Flag

                            and (
                                --Product flag for BasicPrograms

                                dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
                                or
                                (
                                    MAP.REVENUETRANSACTIONTYPECODE <> 5            --Order

                                    and MAP.REVENUESPLITTYPECODE not in (5,7)   --Ticket,Tax

                                    and MAP.APPLICATIONCODE not in (9,10)       --Ticket,Order

                                )
                            );

                if @ACCOUNTCODEMISSING = 0 
                    select @ACCOUNTCODEMISSING = case when count(*) > 0 then 1 else 0 end
                    from dbo.GLREVENUECATEGORYMAPPING as MAP 
                    where coalesce(MAP.ACCOUNTCODE, '') = '' and MAP.ACTIVE = 1;

                if @ACCOUNTCODEMISSING = 0 
                    select @ACCOUNTCODEMISSING = case when count(*) > 0 then 1 else 0 end
                    from dbo.GLPREFERENCEINFO
                    where coalesce(GLPREFERENCEINFO.EMPTYREVENUECATEGORYACCOUNTCODE,'') = '';

                select    @ACCOUNTNUMBERMISSING = case when count(*) > 0 then 1 else 0 end 
                        from dbo.DESIGNATION where coalesce(ACCOUNTNUMBER, '') = '';

                if @ACCOUNTNUMBERMISSING = 0 
                    select    @ACCOUNTNUMBERMISSING = case when count(*) > 0 then 1 else 0 end 
                            from dbo.EVENT
                            left join DBO.EVENTGLMAPPING on EVENT.ID = EVENTGLMAPPING.ID
                            where coalesce(ACCOUNTNUMBER, '') = '';                

                if @ACCOUNTNUMBERMISSING = 0
                    select @ACCOUNTNUMBERMISSING = case when count(*) > 0 then 1 else 0 end
                        from dbo.MEMBERSHIPLEVEL
                        left join dbo.MEMBERSHIPGLMAPPING on MEMBERSHIPLEVEL.ID = MEMBERSHIPGLMAPPING.ID
                        where coalesce(ACCOUNTNUMBER, '') = '';
                return 0;