UFN_RELATIONSHIPCONFIGURATION_CONFIGUREFORREVENUEBATCH

            create function dbo.UFN_RELATIONSHIPCONFIGURATION_CONFIGUREFORREVENUEBATCH
            (
                @CONSTITUENTID uniqueidentifier = null
            )
            returns table
            as
            return(
                select
                    R.RELATIONSHIPTYPECODEID as ID,
                    0 as GENDERCODE,
                    case 
                        when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                        when convert(int, RELATIONSHIPCONSTITUENT.ISGROUP) + convert(int,RELATIONSHIPCONSTITUENT.ISORGANIZATION) = 0 then 1 
                        else 0 end as APPLIESTOINDIVIDUALS,
                    convert(int, RELATIONSHIPCONSTITUENT.ISORGANIZATION) as APPLIESTOORGANIZATIONS,
                    convert(int, RELATIONSHIPCONSTITUENT.ISGROUP) as APPLIESTOGROUPS,
                    case 
                        when convert(int, coalesce(RECIPROCALCONSTITUENT.ISORGANIZATION, RECIPROCALBATCHCONSTITUENT.ISORGANIZATION)) + convert(int, coalesce(RECIPROCALCONSTITUENT.ISGROUP, RECIPROCALBATCHCONSTITUENT.ISGROUP)) = 0 then 1 
                        else 0 end as RELATESTOINDIVIDUALS,
                    case 
                        when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                        else convert(int, coalesce(RECIPROCALCONSTITUENT.ISORGANIZATION, RECIPROCALBATCHCONSTITUENT.ISORGANIZATION)) end as RELATESTOORGANIZATIONS,
                    convert(int, coalesce(RECIPROCALCONSTITUENT.ISGROUP, RECIPROCALBATCHCONSTITUENT.ISGROUP)) as RELATESTOGROUPS
                from dbo.BATCHREVENUECONSTITUENTRELATION R
                inner join dbo.BATCHREVENUECONSTITUENT RELATIONSHIPCONSTITUENT 
                    on R.CONSTITUENTID = RELATIONSHIPCONSTITUENT.ID
                left join dbo.CONSTITUENT RECIPROCALCONSTITUENT 
                    on R.RELATIONID = RECIPROCALCONSTITUENT.ID
                left join dbo.BATCHREVENUECONSTITUENT RECIPROCALBATCHCONSTITUENT 
                    on R.RELATIONID = RECIPROCALBATCHCONSTITUENT.ID
                left join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE 
                    on EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID = R.RELATIONSHIPTYPECODEID
                where R.CONSTITUENTID = @CONSTITUENTID

                union all

                select
                    R.RECIPROCALTYPECODEID as ID,
                    0 as GENDERCODE,
                    case 
                        when convert(int, coalesce(RECIPROCALCONSTITUENT.ISORGANIZATION, RECIPROCALBATCHCONSTITUENT.ISORGANIZATION)) + convert(int, coalesce(RECIPROCALCONSTITUENT.ISGROUP, RECIPROCALBATCHCONSTITUENT.ISGROUP)) = 0 then 1 
                        else 0 end as APPLIESTOINDIVIDUALS,
                    case 
                        when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                        else convert(int, coalesce(RECIPROCALCONSTITUENT.ISORGANIZATION, RECIPROCALBATCHCONSTITUENT.ISORGANIZATION)) end as APPLIESTOORGANIZATIONS,
                    convert(int, coalesce(RECIPROCALCONSTITUENT.ISGROUP, RECIPROCALBATCHCONSTITUENT.ISGROUP)) as APPLIESTOGROUPS,
                    case 
                        when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
                        when convert(int, RELATIONSHIPCONSTITUENT.ISGROUP) + convert(int,RELATIONSHIPCONSTITUENT.ISORGANIZATION) = 0 then 1 
                        else 0 end as RELATESTOINDIVIDUALS,
                    convert(int, RELATIONSHIPCONSTITUENT.ISORGANIZATION) as RELATESTOORGANIZATIONS,
                    convert(int, RELATIONSHIPCONSTITUENT.ISGROUP) as RELATESTOGROUPS
                from dbo.BATCHREVENUECONSTITUENTRELATION R
                inner join dbo.BATCHREVENUECONSTITUENT RELATIONSHIPCONSTITUENT 
                    on R.CONSTITUENTID = RELATIONSHIPCONSTITUENT.ID
                left join dbo.CONSTITUENT RECIPROCALCONSTITUENT 
                    on R.RELATIONID = RECIPROCALCONSTITUENT.ID
                left join dbo.BATCHREVENUECONSTITUENT RECIPROCALBATCHCONSTITUENT 
                    on R.RELATIONID = RECIPROCALBATCHCONSTITUENT.ID
                left join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE 
                    on EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID = R.RELATIONSHIPTYPECODEID
                where R.CONSTITUENTID = @CONSTITUENTID
                )