Premium Retail Services

This query shows stores mapped from the context of 360. It does not show 360 stores that are not mapped nor does it show MDM stores that are not mapped.

SELECT A.[SiteID] AS "360SiteId"
    ,A.[masterSiteID] AS "360MasterSiteID"
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SName], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360Name"
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SStoreNo], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360StoreNumber"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SAddress], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360Address"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SCity], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360City"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SState], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360State"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[SZip], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360PostalCode"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.[CountryCode], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "360CountryCode"
    ,C.[Id] AS "MDMSiteId"
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[TdlinxCode], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMTDLinxCode"
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[Name], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMName"
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[StoreNumber], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMStoreNumber"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[Address], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMAddress"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[City], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMCity"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[State], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMState"
      ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.[PostalCode], '"', ''), ',', ''), CHAR(160), ' '), CHAR(9), ''), CHAR(10), ''), CHAR(13), ''))) AS "MDMPostalCode"
      ,C.[CountryId] AS "MDMCountryCode"
  FROM [QTrax].[dbo].[Sites] A
  JOIN [Integrations].[acostamdm].[MasterDataMapping] B ON A.[SiteID] = B.[EntityId] AND B.[EntityType] = 'Site' AND B.[MappedType] = 'Acosta Store' AND B.[IsActive] = 1
  JOIN [Integrations].[acostamdm].[Store] C ON C.[Id] = B.[MappedId]
  WHERE (A.[masterSiteID] IS NULL OR A.[masterSiteID] = 0) AND A.[CountryCode] = 'US'