USP_REPORT_UNMAPPEDGL

            create procedure dbo.USP_REPORT_UNMAPPEDGL

            as
                with GL_ERRORS_CTE as (    
                -- Missing revenue types

                select
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null as PAYMENTMETHODNAME,
                    null as ITEMTYPE,
                    1 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT
                left outer join dbo.PDACCOUNTCODEMAPPING CM on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                where CM.ID is null
                and O.OFFICEID not in (16,17)

                union all

                -- Missing payment methods

                -- excludes Treasury-Adjustments,Fixed Assets,and Treasury-Miscellaneous payments

                select 
                    O.OFFICEID,
                    O.NAME OFFICENAME,  
                    coalesce(CMRT.TYPENAME, O.NAME) ITEMNAME, 
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    null as ITEMTYPE,
                    2 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT
                full outer join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on CMRT.OFFICEID = CMPT.OFFICEID
                left outer join dbo.PDACCOUNTCODEMAPPING CM on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                where CM.ID is null
                and O.OFFICEID not in (7,8,9)
                -- excluding items that cannot be mapped with Altru prod flags

                and (O.OFFICEID <> 3 or CMPT.NAMEID <> 256)
                and (O.OFFICEID <> 15 or CMPT.NAMEID <> 512)
                and O.OFFICEID not in (16,17)

                union all

                -- Missing payment methods

                -- Treasury-Adjustments,Fixed Assets,and Treasury-Miscellaneous payments

                -- Their payment methods are not associated with a revenue type

                select 
                    O.OFFICEID,
                    O.NAME OFFICENAME,  
                    O.NAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    null as ITEMTYPE,
                    2 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT
                left outer join dbo.PDACCOUNTCODEMAPPING CM on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                where CM.ID is null 
                and O.OFFICEID in (7,8,9)
                -- excluding items that cannot be mapped with Altru prod flags

                and (O.OFFICEID <> 9 or CMPT.NAMEID not in (128, 256, 512))

                union all

                -- Missing application types

                select
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMAT.TYPENAME ITEMNAME,
                    null as PAYMENTMETHODNAME,
                    null as ITEMTYPE,
                    3 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPAPPLICATIONTYPE CMAT
                left outer join dbo.PDACCOUNTCODEMAPPING CM on (CM.APPLICATIONTYPE & CMAT.NAMEID) > 0 and CM.OFFICEID = CMAT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMAT.OFFICEID
                where CM.ID is null
                -- excluding items that cannot be mapped with Altru prod flags

                and O.OFFICEID not in (1,2)
                and (O.OFFICEID <> 15 or CMAT.NAMEID not in (8,16,64,256,1024,2048,4096,16384,32768))
                and (O.OFFICEID <> 3 or CMAT.NAMEID not in (16,256,1024,2048,4096,16384,65536,131072))

                union all

                -- Missing segment mappings

                select 
                    -1 as OFFICEID,
                    null as OFFICENAME,
                    SMV.LONGDESCRIPTION ITEMNAME,
                    null as PAYMENTMETHODNAME,
                    TAS.FRIENDLYTABLENAME ITEMTYPE,
                    4 as ERRORCODE
                from dbo.PDACCOUNTSEGMENTMAPPINGVIEW SMV
                left outer join dbo.PDACCOUNTSEGMENTMAPPING SM on SM.LONGDESCRIPTIONID = SMV.LONGDESCRIPTIONID
                inner join dbo.PDACCOUNTSTRUCTURE PAS on PAS.ID = SMV.PDACCOUNTSTRUCTUREID
                inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT TAS on TAS.ID = PAS.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                where SM.PDACCOUNTSEGMENTVALUEID is null

                union all

                -- Missing segment values with '<Not Used>' description

                select
                    -1 as OFFICEID,
                    null as OFFICENAME,
                    SM.LONGDESCRIPTION ITEMNAME,
                    null as PAYMENTMETHODNAME,
                    TAS.FRIENDLYTABLENAME ITEMTYPE,
                    4 as ERRORCODE
                from dbo.PDACCOUNTSEGMENTMAPPING SM
                left outer join dbo.PDACCOUNTSTRUCTURE PAS on PAS.ID = SM.PDACCOUNTSTRUCTUREID
                left outer join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT TAS on TAS.ID = PAS.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                where SM.PDACCOUNTSEGMENTVALUEID is null
                and not exists(select 1 from dbo.PDACCOUNTSEGMENTMAPPINGVIEW where LONGDESCRIPTIONID = SM.LONGDESCRIPTIONID)

                union all

                -- Missing composite segment mappings

                select 
                    -1 as OFFICEID,
                    null as OFFICENAME,
                    SMV.LONGDESCRIPTION ITEMNAME,
                    null as PAYMENTMETHODNAME,
                    TAS.FRIENDLYTABLENAME ITEMTYPE,
                    5 as ERRORCODE
                from dbo.PDCOMPOSITESEGMENTMAPPINGVIEW SMV
                left outer join dbo.PDCOMPOSITESEGMENTMAPPINGENTRY SME on SME.LONGDESCRIPTIONID = SMV.LONGDESCRIPTIONID
                left outer join dbo.PDCOMPOSITESEGMENTTABLELIST TL on TL.ID = SME.PDCOMPOSITESEGMENTTABLELISTID
                left outer join dbo.PDCOMPOSITESEGMENT S on S.ID = TL.PDCOMPOSITESEGMENTID
                left outer join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT TAS on SMV.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = TAS.ID

                union all

                -- Gift in kind card subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (select GIK.[DESCRIPTION] from dbo.GIFTINKINDSUBTYPECODE GIK where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = GIK.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMPT.NAMEID = 128
                and CMS.ADDITIONALSUBTYPE = 1

                union all

                -- Credit card subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (
                    select CTC.[DESCRIPTION] from dbo.CREDITTYPECODE CTC 
                    where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = CTC.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMPT.NAMEID = 4
                and CMS.ADDITIONALSUBTYPE = 2

                union all

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (
                    select CTC.[DESCRIPTION] from dbo.CREDITTYPECODE CTC 
                    where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = CTC.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 4
                and O.OFFICEID = 10
                and CMS.ADDITIONALSUBTYPE = 2

                union all

                -- Property subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (select PST.[DESCRIPTION] from dbo.PROPERTYSUBTYPECODE PST where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = PST.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMPT.NAMEID = 32
                and CMS.ADDITIONALSUBTYPE = 3

                union all

                -- Other payment method subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (select OPM.[DESCRIPTION] from dbo.OTHERPAYMENTMETHODCODE OPM where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = OPM.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMPT.NAMEID = 16
                and CMS.ADDITIONALSUBTYPE = 4

                union all

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (select OPM.[DESCRIPTION] from dbo.OTHERPAYMENTMETHODCODE OPM where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = OPM.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 16
                and O.OFFICEID = 10
                and CMS.ADDITIONALSUBTYPE = 4

                union all

                -- Pledge subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID
                cross apply (select PST.[DESCRIPTION] from dbo.PROPERTYSUBTYPECODE PST where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = PST.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID in (1,2,4)
                and CMS.ADDITIONALSUBTYPE = 5

                union all

                -- Benefit subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    CMPT.TYPENAME PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                left outer join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                left outer join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE CMPT on (CM.PAYMENTMETHOD & CMPT.NAMEID) > 0 and CM.OFFICEID = CMPT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMPT.OFFICEID or O.OFFICEID = CMRT.OFFICEID
                cross apply (select PST.[DESCRIPTION] from dbo.BENEFITCATEGORYCODE PST where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = PST.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID in (1,8)
                and CMS.ADDITIONALSUBTYPE = 6

                union all

                -- Resource subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.[DESCRIPTION] ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select PST.[DESCRIPTION] from dbo.RESOURCECATEGORYCODE PST where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = PST.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 16
                and CMS.ADDITIONALSUBTYPE in (7,8)

                union all

                -- Program subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.NAME ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select PROGRAM.NAME from dbo.PROGRAM where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = PROGRAM.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 1
                and CMS.ADDITIONALSUBTYPE = 10

                union all

                -- Fee subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.NAME ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select FEE.NAME from dbo.FEE where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = FEE.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 2
                and CMS.ADDITIONALSUBTYPE = 11

                union all

                -- Tax subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.NAME ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select TAX.NAME from dbo.TAX where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = TAX.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 4
                and CMS.ADDITIONALSUBTYPE = 12

                union all

                -- Facility subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.NAME ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select EVENTLOCATION.NAME from dbo.EVENTLOCATION where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = EVENTLOCATION.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 1024
                and CMS.ADDITIONALSUBTYPE = 13

                union all

                -- Merchandise subtypes

                select distinct
                    O.OFFICEID,
                    O.NAME OFFICENAME,
                    CMRT.TYPENAME ITEMNAME,
                    null PAYMENTMETHODNAME,
                    A.NAME ITEMTYPE,
                    6 as ERRORCODE
                from dbo.PDACCOUNTCODEMAPPING CM
                inner join dbo.PDACCOUNTCODEMAPREVENUETYPE CMRT on (CM.REVENUETYPE & CMRT.NAMEID) > 0 and CM.OFFICEID = CMRT.OFFICEID
                inner join dbo.PDACCOUNTCODEMAPPINGSUBTYPE CMS on CMS.PDACCOUNTCODEMAPPINGID = CM.ID
                inner join dbo.PDACCOUNTCODEMAPOFFICE O on O.OFFICEID = CMRT.OFFICEID
                cross apply (select MERCHANDISEDEPARTMENT.NAME from dbo.MERCHANDISEDEPARTMENT where not exists(select 1 from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where SUBTYPEID = MERCHANDISEDEPARTMENT.ID)) A
                where CMS.SUBTYPEID <> '99999999-9999-9999-9999-999999999999'
                and CMRT.NAMEID = 2048
                and CMS.ADDITIONALSUBTYPE = 14
            )
            select
                OFFICENAME,
                ITEMNAME,
                PAYMENTMETHODNAME,
                ITEMTYPE,
                ERRORCODE
            from GL_ERRORS_CTE
            -- excluding items that cannot be mapped with Altru prod flags

            where OFFICEID not in (6,11,12,13,14)
            order by ERRORCODE,OFFICENAME,ITEMNAME,PAYMENTMETHODNAME