Description
Validates sales hierarchy structure, levels, relationships, and associated locations.
Query :
select
A.Country,A.ClientId,A.Client,A.SubClientId,A.SubClientName SubClient,
convert(char,convert(datetime,A.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)CreatedOn,/*A.CreatedByUser,
convert(char,convert(datetime,A.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ModifiedOn,A.ModifiedByUser,*/
A.SalesHierarchyId,/*E.Code,*/A.LevelName,A.SalesHierarchyType,/*A.SalesHierarchyLevel SalesHierarchyLevelID,*/A.Name,A.Code,/*E.LocationID,E.Code,E.IsDeleted,*/
/*'Active_count' = (select COUNT(case when IsActive = '1' then 'Active' end) as Active_count
from location
where ClientId = 408 and SalesHierarchyId IN (),
'InActive_count' = (select COUNT(case when IsActive = '0' then 'InActive' end) as InActive_count
from location where ClientId = 408),*/
COUNT(case when IsActive = '1' then 'Active' end) as Active_count,
COUNT(case when IsActive = '0' then 'InActive' end) as InActive_count,
CONCAT(A.LevelName,A.Name,A.code)'Key1',A.ParentSalesHierarchyId,B.LevelName ParentLevel,B.SalesHierarchyType ParentType, A.ParentSalesHierarchy ParentName,A.ParentCode,
B.ParentSalesHierarchyId GrandParentSalesHierarchyId,C.LevelName GrandParentLevel,C.SalesHierarchyType GrandParentType,B.ParentSalesHierarchy GrandParentName,
B.ParentCode GrandParentCode,
/*CASE
WHEN convert(nvarchar,C.ParentSalesHierarchyId)=0 then 'Top SalesHierarchy'
ELSE C.ParentSalesHierarchyId
END as GreatGrandParentSalesHierarchyId,*/
C.ParentSalesHierarchyId GreatGrandParentSalesHierarchyId,D.LevelName GreatGrandParentLevel,D.SalesHierarchyType GreatGrandParentType,C.ParentSalesHierarchy GreatGrandParentName,C.ParentCode GreatGrandParentCode
from vwSalesHierarchyList A WITH (NoLock)
full outer join vwSalesHierarchyList B WITH (NoLock) on A.ParentSalesHierarchyId=B.SalesHierarchyId --Level2 of hierarchy
full outer join vwSalesHierarchyList C WITH (NoLock) on B.ParentSalesHierarchyId=C.SalesHierarchyId --Level3 of hierarchy
full outer join vwSalesHierarchyList D WITH (NoLock) on C.ParentSalesHierarchyId=D.SalesHierarchyId --Level4 of hierarchy
full outer join LocationSalesHierarchy F WITH (NoLock) on A.SalesHierarchyId=F.SalesHierarchyID and F.IsDeleted = 0
full outer join Location E with (nolock) on F.LocationId=E.LocationId and E.IsDeleted = 0
where A.ClientId IN (408)
and (A.Code IN (
'MR0067','MR0040','MR0314','MR0043','MR0064','MR0044','MR0032','MR0045','MR0307','MR0313','MR0311','MR0308','MR0310','MR0309','MR0041','MR0042','MR0007','MR0005','MR0002','MR0004','MR0028','MR0003','MR0318','MR0315','MR0319','MR0085','MR0330','MR0331','MR0332','MR0333','MR0320','MR0317','MR0321','MR0322','MR0323','MR0086','MR0101','MR0103','MR0327','MR0100','MR0080','MR0031','MR0083','MR0084','MR0024','MR0022','MR0020','MR0023','MR0025','MR0021','MR0006','MR0265','MR0270','MR0260','MR0263','MR0281','MR0274','MR0277','MR0123','MR0125','MR0126','MR0129','MR0275','MR0280','MR0130','MR0120','MR0121','MR0124','MR0301','MR0299','MR0300','MR0069','MR0068','MR0293','MR0294','MR0286','MR0291','MR0285','MR0297','MR0070','MR0071','MR0072','MR0324','MR0062','MR0063','MR0061','MR0288','MR0203','MR0202','MR0201','MR0240','MR0221','MR0220','MR0222','MR0141','MR0142','MR0140','MR0182','MR0181','MR0160','MR0241','MR0243','MR0245','MR0244'
))-- or A.ParentCode = 'Zacatecas' or A.ParentCode = '9005382-Zacatecas')
/*,'Douglasville')*/
/*and A.ParentCode
IN ('Aguascalientes','Ameca','Belenes','Cd. Juárez','Cd. Obregón','Chihuahua','Comarca Lagunera','Culiacán','Delicias - Parral','Delicias','Durango','Estadio','Fresnillo','Guadalajara ZM','Hermosillo','Jalisco Foráneo 2','Jalisco Foráneo','Juárez','La Paz','Laredo','Los Altos','Matamoros','Matehuala','Mazatlán','Mexicali','Monclova','Monterrey','NL Foráneo','Nuevo Laredo','Obregón','Parral','Piedras Negras','Reynosa','Rioverde - Matehuala','Rioverde','Saltillo','San Luis','Santa Rita','Tecomán','Trojes','Zacatecas'
)*/
--and A.LevelName IN ('Sales Office','Sales Group','Sales Territory') --and A.SalesHierarchyType = 'NCB'/*
/*A.SalesHierarchyID IN (
224779
)*/
/*and A.Name IN ('Tuxpan','TÚXPAM')
'1143-082',
'Actopan'
)*//*
or A.Saleshierarchyid In (
152059,
152058))*/
--and A.LevelName IN ('Sales Territory')
--and B.LevelName IS NULL
--and A.Code IN ('AC Región Noreste','AC Región Norte','AC Región Occidente','AC Región Pacífico')
/*and A.CountryId = 57*/
--AND A.ParentSalesHierarchyId IN (434218,526517)
--0
--)
--and A.SubClientName = 'Grupo Arca Continental'
--and A.SalesHierarchyType like 'Preseller%'
group by
A.country,A.ClientId,A.Client,A.SubClientId,A.SubClientName,
convert(char,convert(datetime,A.CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120),/*A.CreatedByUser,
convert(char,convert(datetime,A.ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120),A.ModifiedByUser,*/
A.SalesHierarchyId,A.LevelName,A.SalesHierarchyType,/*A.SalesHierarchyLevel,*/A.name,A.code,/*E.LocationID,E.Code,E.IsDeleted,*/
CONCAT(A.LevelName,A.Name,A.code),/*
A.SalesHierarchyTypeId,*/A.ParentSalesHierarchyId,B.LevelName,B.SalesHierarchyType, A.ParentSalesHierarchy,A.ParentCode,
B.ParentSalesHierarchyId,C.LevelName,C.SalesHierarchyType,B.ParentSalesHierarchy,
B.ParentCode,
C.ParentSalesHierarchyId,D.LevelName,D.SalesHierarchyType,C.ParentSalesHierarchy,C.ParentCode
--AND COUNT(case when IsActive = '1' then 'Active' end) = 0
--AND count(case when IsActive = '0' then 'InActive' end) = 0
order by Key1--levelName desc,A.SalesHierarchyType, A.name,A.code,CreatedOn asc