Description
To get OMD export for which ever client or outlets according to outlet import template
Query :
To get OMD export for which ever client or outlets according to outlet import template
Query :
Select LocationID,ClientID,Country as [Country],CountryID,SubClientID,ISNULL ([SubClientCode],'') as [SubClientCode],ISNULL ([SubClientName],'') as [SubClient],
Code as [CustomerCode],Barcode,
Name as [CustomerName],ISNULL (Street,'') as [Street],ISNULL (Street2,'') as [Address2],ISNULL (Street3,'') as [Address3],ISNULL (Address4,'') as [Address4],
City as [City],State as [State],
ISNULL (PostalCode,'') as [PostalCode],ISNULL (RegionCode,'') as [RegionCode],ISNULL (RegionName,'') as [RegionName],
ISNULL (Latitude,'') as [Latitude],ISNULL (Longitude,'') as [Longitude],
ISNULL (TimeZone,'') as [TimeZone],ISNULL (PrimaryPhone,'') as [PrimaryPhone],ISNULL (PrimaryMobile,'') as [MobilePhone],PrimaryEmail as [Email],/*SalesHierarchyID,*/
ISNULL (SalesOrganizationCode,'') as [SalesOrganizationCode],ISNULL (SalesOrganizationName,'') as [SalesOrganizationName],
ISNULL (SalesOfficeCode,'') as [SalesOfficeCode],ISNULL (SalesOfficeName,'') as [SalesOfficeName],
ISNULL (SalesGroupCode,'')as [SalesGroupCode],ISNULL (SalesGroupName,'') as [SalesGroupName],
ISNULL (SalesTerritoryCode,'') as [SalesTerritoryCode],ISNULL (SalesTerritoryName,'') as SalesTerritory,
ISNULL (TeleSellingTerritoryCode,'') as [TeleSellingTerritoryCode],ISNULL (TeleSellingTerritoryName,'') as [TeleSellingTerritoryName],
ISNULL (CA_TerritoryCode,'') as [CreditApproverCode],ISNULL (CA_TerritoryName,'') as [CreditApproverName],
ISNULL (MC_TerritoryCode,'') as [MerchandizerCode],ISNULL (MC_TerritoryName,'') as [MerchandizerName],
ISNULL (P1_TerritoryCode,'') as [P1_TerritoryCode],ISNULL (P1_TerritoryName,'') as [P1_TerritoryName],
ISNULL (P2_TerritoryCode,'') as [P2_TerritoryCode],ISNULL (P2_TerritoryName,'') as [P2_TerritoryName],
ISNULL (P3_TerritoryCode,'') as [P3_TerritoryCode],ISNULL (P3_TerritoryName,'') as [P3_TerritoryName],ISNULL (P4_TerritoryCode,'') as [P4_TerritoryCode],
ISNULL (P4_TerritoryName,'') as [P4_TerritoryName],ISNULL (P5_TerritoryCode,'') as [P5_TerritoryCode],ISNULL (P5_TerritoryName,'') as [P5_TerritoryName],
ISNULL (NCB_TerritoryCode,'') as [ReserveRouteCode],ISNULL (NCB_TerritoryName,'') as [ReserveRouteName],
ISNULL (LocationType,'') as [TradeChannelName],ISNULL (ChannelCode,'') as [TradeChannelCode],
ISNULL (SubTradeChannelName,'') as [SubTradeChannel],ISNULL (SubTradeChannelTypeCode,'') as [SubTradeChannelCode],
ISNULL (LocationLocalTradeChannelCode,'') as [LocalTradeChannelCode],ISNULL (LocationLocalTradeChannel,'') as [LocalTradeChannelName],
ISNULL (LocalSubtradeChannelCode,'') as [LocalSubTradeChannelCode],ISNULL (LocalSubtradeChannel,'') as [LocalSubTradeChannelName],
ISNULL (TradeGroupCode,'') as [TradeGroupCode],ISNULL (TradeGroup,'') as [TradeGroupName],
ISNULL (LocationChainCode,'') as [ChainCode],ISNULL (LocationChain,'') as [ChainName],
ISNULL (CustomerTierCode,'') as [CustomerTierCode],ISNULL (Classification,'') as [CustomerTierName],
ISNULL (CPLCode,'') as [CPLCode],ISNULL (CPLName,'') as [CPLName],
ISNULL (ClusterCode,'') as [ClusterCode],ISNULL (LocationCluster,'') as [ClusterName],
ISNULL (LocationLocalClusterCode,'') as [LocalClusterCode],ISNULL (LocationLocalCluster,'') as [LocalClusterName],
ISNULL (MarketSegmentCode,'') as [MarketSegmentCode],ISNULL (MarketSegment,'') as [MarketSegmentName],
ISNULL (SegmentCode,'') as [SegmentCode],ISNULL (Segment,'') as [SegmentName],
ISNULL (EnvironmentCode,'') as [EnvironmentCode],ISNULL (Environment,'') as [EnvironmentName],
ISNULL (Assortment1,'') as [Assortment1],ISNULL (Assortment2,'') as [Assortment2],ISNULL (Assortment3,'') as [Assortment3],
ISNULL (Assortment4,'') as [Assortment4],ISNULL (Assortment5,'') as [Assortment5],
ISNULL (ExtraFieldJson,'') as [ExtraFields],
CASE
when IsActive = '1' then 'Active'
when IsActive = '0' then 'InActive'
end as IsActive,
convert(char,convert(datetime,CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)CreatedOn,CreatedByUser,
convert(char,convert(datetime,ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ModifiedOn,ModifiedByUser/*
COUNT(case when IsActiveStatus = 'Yes' then 'Active' end) as Active_count,
COUNT(case when IsActiveStatus = 'No' then 'InActive' end) as InActive_count*/
FROM vwLocationList where
/*[CountryId] = '29' and */[ClientId] IN (/*71*/460/*,237,*//*443*/)/*and ClusterCode = 'Modern Trade'*//*and LocationID IN (
2651911
)*//*
and LocationType IN (
'SS IC LEISURE'
)*//*
and Code IN (
'600599010','600622596','600641788','600695865','500360811','600601853','501029476'
)*/
and LocationID IN (
6253106,26007548,17970528,20301384,20312178,20313331,20499141,21732072,23012161,23012297,23044334,23044402,24074324,24078583,24671411,24860376,24860395,24911567,25528961,25939753,25528828
)
--and SubClientName NOT IN ('StoreCheck')
--and ltrim(rtrim(SubClientCode)) IS NULL
--and P1_TerritoryCode IN ('ON')-- and SalesOfficeName = 'Central'
--and IsActive = '1'
/*and SalesHierarchyID IN (
536071
)*/
and IsActive = '1'
--and SalesGroupCode IN ('9005382-PARRAL','9005382-DELICIAS') and SalesOfficeCode IS NULL
--and convert(char,convert(datetime,ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120) >= '2024-12-31'
--and Assortment5 = 'Consolidated'
--and SubClientID = 1150587
--and SalesOrganizationName IN ('North','South')
--and SalesOfficeName IN ('Fresno-Iztacalco')
/*and SalesTerritoryCode IN (
'EUP910','EIP222','EUP205','EUP202','EUP208','EUP206','EUP209','EUP207','EUP907','EUP109','EUP105','EUP104','EUP906','EUP905','EUP504','EIP221','EUP204','EUP506','EUP501','EUP908','EUP108','EUP203','EUP102','EUP902','EUP909','EUP904','EUP107','EUP106','EUP101','EUP201','EUP103','EUP901','EIP223','EUP903','EUCZ01','EICZ01','EICZ02'
)*/
--and LocationCluster NOT IN ('On-premise','Modern Trade','Traditional')
--and (P1_TerritoryCode = '1094636' or P2_TerritoryCode = '1094636' or P3_TerritoryCode = '1094636' or P4_TerritoryCode = '1094636' or P5_TerritoryCode = '1094636' )
--and P5_TerritoryCode = '1096067'
--and ModifiedOn >= '2025-08-26'
--AND Assortment4 = 'KROGER'
--and /*(State = 'GA' or */City In ('Atlanta','Georgia')--)
/*group by /*LocationID,*//*SubClientCode,SubClientName,*/ClientID,/*SubClientID,*/Country,CountryID,/*Assortment3,Code,Name,*/
/*SalesHierarchyID,*//*SalesOrganizationName,SalesOrganizationCode,
SalesOfficeName,SalesOfficeCode,SalesGroupName,SalesGroupCode,SalesTerritoryName,SalesTerritoryCode,/*P3_TerritoryName,P3_TerritoryCode*//*,ChannelCode,LocationType,*/
/*SubTradeChannelTypeCode,SubTradeChannelName,*/*/LocationCluster,ClusterCode
/*Assortment1,Assortment2,Assortment3,*//*Assortment4,*/--,Assortment5*/
--convert(char,convert(datetime,ModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120),ModifiedByUser*/
ORDER BY --ClientID,SubClientId--,State--,P3_TerritoryCode--,createdon--[LocationId] OPTION (RECOMPILE)}
SalesOrganizationCode,SalesOrganizationName,SalesOfficeName,SalesOfficeCode,SalesGroupName,SalesGroupCode,SalesTerritoryName,SalesTerritoryCode
--------------------------------------------------