TOP

AdmarcMactive_2_2K11_12_28

USE [Web_HearstSalesForce]
GO
/****** Object: StoredProcedure [dbo].[AdmarcMactive_2] Script Date: 12/28/2011 14:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Ernest W. Lessenger
– Create date: 3/25/2010
– Description: Prepares the Admarc/Mactive import tables for import
– =============================================
ALTER PROCEDURE [dbo].[AdmarcMactive_2]
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

EXEC [Clear_Deleted];

— Set the unique identifiers used for performing upserts
— This is being done statically rather than using a view in order to permit indexing of the unique
— ID columns if required
UPDATE Accounts
SET [Account_Id__c] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([AccountNumber]));

UPDATE Accounts
SET [ParentAccountId] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([ParentAccountNumber]))
WHERE ([ParentAccountNumber] IS NOT NULL);

UPDATE Orders
SET [Account_Id__c] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([AccountNumber])),
[OrderID__c] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([OrderNumber]));

UPDATE Appearances
SET [OrderId__c] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([OrderNumber])),
[ScheduleID__c] = LTRIM(RTRIM([Business_Unit__c])) + ‘.’ + LTRIM(RTRIM([OrderNumber])) + ‘.’ + LTRIM(RTRIM(convert(nvarchar(40),[Sequence__c])));

UPDATE Accounts
SET RecordTypeId = ’012A00000009A6tIAE’
WHERE (RecordTypeId IS NULL);

UPDATE Appearances
SET Amount = 0.0
WHERE (Amount IS NULL);

UPDATE Orders
SET Amount = 0.0
WHERE (Amount IS NULL);

– Create indexes

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Accounts]‘) AND name = N’IDX_Accounts_AccountId’)
CREATE NONCLUSTERED INDEX [IDX_Accounts_AccountId] ON [dbo].[Accounts]
( [Account_Id__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Accounts]‘) AND name = N’IDX_Accounts_DUNS’)
CREATE NONCLUSTERED INDEX [IDX_Accounts_DUNS] ON [dbo].[Accounts]
( [DUNS_Number__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Appearances]‘) AND name = N’IDX_Appearances_ScheduleId’)
CREATE NONCLUSTERED INDEX [IDX_Appearances_ScheduleId] ON [dbo].[Appearances]
( [ScheduleID__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Appearances]‘) AND name = N’IDX_Appearances_OrderId’)
CREATE CLUSTERED INDEX [IDX_Appearances_OrderId] ON [dbo].[Appearances]
( [OrderId__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Orders]‘) AND name = N’IDX_Orders_OrderId’)
CREATE NONCLUSTERED INDEX [IDX_Orders_OrderId] ON [dbo].[Orders]
( [OrderID__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Orders]‘) AND name = N’IDX_Orders_AccountId’)
CREATE NONCLUSTERED INDEX [IDX_Orders_AccountId] ON [dbo].[Orders]
( [Account_Id__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N’[dbo].[Appearances]‘) AND name = N’IDX_Appearances_OrderId’)
CREATE NONCLUSTERED INDEX [IDX_Appearances_RepNbr] ON [dbo].[Appearances]
( [REP NBR] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

– Set the SFDC Id for the appearances that already exist in Salesforce
–UPDATE [Appearances]
– SET [Id] = o.[Id]
–FROM [Appearances] AS a INNER JOIN [OpportunityLineItem] AS o
– ON (a.[ScheduleID__c] = o.[ScheduleId__c])
–WHERE (o.IsDeleted = 0);

– Set the SFDC Id for Parent Accounts

– Set the ParentId on Accounts for all accounts that have parents in the 999 business unit
UPDATE Accounts
SET ParentId = ca.ParentId
FROM Accounts a INNER JOIN Account ca ON (a.Account_Id__c = ca.Account_Id__c)
INNER JOIN Account pa ON (ca.ParentId = pa.Id)
WHERE (ca.IsDeleted = 0) AND (pa.IsDeleted = 0)
AND (pa.Business_Unit__c = ’999′)
AND (a.ParentId IS NULL);

UPDATE Accounts
SET ParentId = pa.Id
FROM Accounts a INNER JOIN Account pa ON (a.ParentAccountId = pa.Account_Id__c)
WHERE (a.ParentAccountNumber IS NOT NULL)
AND (pa.IsDeleted = 0)
AND (a.ParentId IS NULL);

– Drop the exception tables so they can be recreated later

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Accounts_Exception1]‘) AND type in (N’U'))
DROP TABLE [dbo].[Accounts_Exception1];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Orders_Exception1]‘) AND type in (N’U'))
DROP TABLE [dbo].[Orders_Exception1];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Appearances_Exception1]‘) AND type in (N’U'))
DROP TABLE [dbo].[Appearances_Exception1];

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Accounts_Exception2]‘) AND type in (N’U'))
DROP TABLE [dbo].[Accounts_Exception2];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Orders_Exception2]‘) AND type in (N’U'))
DROP TABLE [dbo].[Orders_Exception2];
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Appearances_Exception2]‘) AND type in (N’U'))
DROP TABLE [dbo].[Appearances_Exception2];

– Create the exceptions files

SELECT *
INTO Accounts_Exception1
FROM [Accounts]
WHERE (Account_Id__c in (
SELECT Account_Id__c
FROM [Accounts]
GROUP BY Account_Id__c
HAVING COUNT(*) > 1));

DELETE FROM [Accounts]
WHERE (Account_Id__c in (
SELECT Account_Id__c
FROM [Accounts]
GROUP BY Account_Id__c
HAVING COUNT(*) > 1));

SELECT *
INTO [Orders_Exception1]
FROM [Orders]
WHERE (OrderID__c in (
SELECT OrderID__c
FROM [Orders]
GROUP BY OrderID__c
HAVING COUNT(*) > 1));

DELETE
FROM [Orders]
WHERE (OrderID__c in (
SELECT OrderID__c
FROM [Orders]
GROUP BY OrderID__c
HAVING COUNT(*) > 1));

SELECT *
INTO [Appearances_Exception1]
FROM [Appearances]
WHERE (ScheduleID__c in (
SELECT ScheduleID__c
FROM [Appearances]
GROUP BY ScheduleID__c
HAVING COUNT(*) > 1));

DELETE
FROM [Appearances]
WHERE (ScheduleID__c in (
SELECT ScheduleID__c
FROM [Appearances]
GROUP BY ScheduleID__c
HAVING COUNT(*) > 1));

SELECT a.*
INTO Orders_Exception2
FROM Orders a
LEFT JOIN Accounts o on a.Account_Id__c = o.Account_Id__c
LEFT JOIN Account opp on a.Account_Id__c = opp.Account_Id__c
WHERE ((o.Account_Id__c is null AND opp.Account_Id__c is null) OR (a.CloseDate IS NULL))
AND IsNull(opp.IsDeleted,0) = 0;

CREATE NONCLUSTERED INDEX [IDX_Orders_Exception2_OrderId] ON [dbo].[Orders_Exception2]
( [OrderID__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

DELETE
FROM [Orders]
WHERE ([OrderID__c] in (SELECT [OrderID__c] FROM [Orders_Exception2]));

SELECT a.*
INTO Appearances_Exception2
FROM Appearances a
LEFT JOIN Orders o on a.OrderId__c = o.OrderID__c
LEFT JOIN Opportunity opp on a.OrderId__c = opp.OrderID__c
WHERE ((o.OrderID__c is null AND opp.OrderID__c is null ) OR (a.[Date] IS NULL))
AND IsNull(opp.IsDeleted,0) = 0;

CREATE NONCLUSTERED INDEX [IDX_Appearances_Exception2_ScheduleId] ON [dbo].[Appearances_Exception2]
( [ScheduleID__c] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

DELETE
FROM [Appearances]
WHERE (ScheduleID__c in (SELECT ScheduleID__c FROM [Appearances_Exception2]));

– If a SRID was not provided, but a SalesRep name was, then look up the user by name in the users table
UPDATE Accounts
SET SLS_1__c = s.Name
FROM Accounts a inner join [User] u ON (a.SLS_1_Name__c = u.Name)
inner join SRID__c s ON (s.User__c = u.Id) AND (a.Business_Unit__c = s.Business_Unit__c)
WHERE a.SLS_1__c IS NULL;

UPDATE Accounts
SET SLS_2__c = s.Name
FROM Accounts a inner join [User] u ON (a.SLS_2_Name__c = u.Name)
inner join SRID__c s ON (s.User__c = u.Id) AND (a.Business_Unit__c = s.Business_Unit__c)
WHERE a.SLS_2__c IS NULL;

– If a SRID was not provided, but a SalesRep name was, then look up the user by name in the users table
UPDATE Orders
SET SLS_1__c = s.Name
FROM Orders o inner join [User] u ON (o.SLS_1_Name = u.Name)
inner join SRID__c s ON (s.User__c = u.Id) AND (o.Business_Unit__c = s.Business_Unit__c)
WHERE o.SLS_1__c IS NULL;

– If we still don’t have an owner on the Account, then assign the market SRID
UPDATE Accounts
SET SLS_1__c = ‘M’ + Business_Unit__c
WHERE (SLS_1__c IS NULL);

– If no opportunity owner was specified, then try to get the owner name from the appearance or from the Account otherwise
UPDATE Orders
SET SLS_1__c = app.[REP NBR]
–SELECT o.OrderID__c,o.SLS_1__c, app.[REP NBR]
FROM Orders o INNER JOIN Appearances app ON o.OrderID__c = app.OrderId__c
INNER JOIN (
SELECT OrderId__c, Count(DISTINCT [REP NBR]) as NumReps
FROM Appearances app
GROUP BY OrderId__c
HAVING COUNT(DISTINCT [REP NBR]) = 1
) qo ON o.OrderID__c = qo.OrderId__c
WHERE o.SLS_1__c IS NULL;

UPDATE Orders
SET SLS_1__c = a.SLS_1__c
FROM Accounts a inner join Orders o ON a.Account_Id__c = o.Account_Id__c
WHERE o.SLS_1__c IS NULL;

– Populate AccountAgency IDs from the cached Account table
UPDATE AccountAgency
SET AccountId = b.Id, AgencyId = c.Id
FROM AccountAgency a INNER JOIN Account b ON (a.AccountNbr = b.Account_Id__c)
inner join Account c ON (a.AgencyNbr = c.Account_Id__c);

DELETE FROM AccountAgency
WHERE (AccountId IS NULL) OR (AgencyId IS NULL);

UPDATE AccountAgency
SET PartnerId = b.Id
FROM AccountAgency a INNER JOIN [Partner] b
ON (a.AccountId = b.AccountFromId) AND (a.AgencyId = b.AccountToId)

DELETE FROM AccountAgency
WHERE (PartnerId IS NOT NULL);
END

Read More
TOP

SFDC: Answers for Salesforce Data Geeks

http://ezrakenigsberg.com/

Great resource for SFDC data management

Read More
TOP

SalesForce: Job lead

  • http://www.edlconsulting.com/
  • http://www.danteconsulting.com/

http://itunes.apple.com/podcast/salesforce-com-training-certification/id325668840

Read More
TOP

Data Integration — Open Source

http://apatar.com/

Read More

Switch to our mobile site