USP_DATALIST_MISSINGGLREPORT
Returns data about missing gl configuration.
Definition
Copy
CREATE procedure dbo.USP_DATALIST_MISSINGGLREPORT
as
set nocount on;
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)
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,256,1024,2048,4096,16384))
and (O.OFFICEID <> 3 or CMAT.NAMEID not in (16,256,1024,2048,4096,16384))
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