Distributor Sku Processor

SET NOCOUNT ON UPDATE I SET FseInclude = 'N', Note = 'ZZ PrnCode or CstCode encountered' FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND (I.PrnCode LIKE 'ZZ%' OR I.CstCode LIKE 'ZZ%') AND EXISTS (SELECT * FROM tbl_IMPORT_Exclusions WHERE OwnerId = 0 AND OwnerType = 'BRO' AND ExclusionId = 'ZZ') SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON UPDATE I SET FseInclude = 'N', Note = 'Missing CustomerSku or MfrSku value' FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND (I.CustomerSku IS NULL OR I.CustomerSku = '' OR I.MfrSku IS NULL OR I.CstCode IS NULL ) SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON UPDATE I SET CustomerKey = CS.CustomerKey, MfrId = A.ObjectId, CustomerId = D.Cdr_RecordId FROM tbl_IMPORT_DistributorSkus I INNER JOIN dbo.tbl_CDR_DstCodes DC ON DC.OwnerID = I.ownerId AND DC.ownerType = I.ownerType AND DC.cdr_dstcode = I.CstCode INNER JOIN tbl_CDR_Distributors D ON D.OwnerID = I.ownerId AND D.FSL_TableCode = I.ownerType AND D.cdr_recordId = DC.cdr_recordId INNER JOIN tbl_IMPORT_Aliases A ON A.ownerId = D.OwnerID AND A.ownerType = I.ownerType AND A.objectType = 'MFRID' AND A.Alias = I.PrnCode INNER JOIN tblManufacturers M ON M.Mfr_Id = A.ObjectId INNER JOIN tbl_PRD_CustomerSkus CS ON CS.OwnerID = I.ownerId AND CS.FSL_TableCode = I.ownerType AND CS.MfrId = A.ObjectId AND CS.LinkType = 'SKU' AND CS.Sku = I.MfrSku AND CS.CustomerId = D.Cdr_RecordId WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND NOT I.CustomerSku IS NULL AND NOT I.CustomerSku = '' AND I.FseInclude = 'Y' AND NOT A.ObjectId IS NULL SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SELECT I.PrnCode, COUNT(*) Cnt FROM tbl_IMPORT_DistributorSkus I LEFT JOIN tbl_IMPORT_Aliases A ON A.ownerId = I.OwnerID AND A.ownerType = I.ownerType AND A.objectType = 'MFRID' AND A.Alias = I.PrnCode WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND A.Alias IS NULL AND NOT I.PrnCode LIKE 'ZZ%' AND NOT I.PrnCode LIKE 'Z-%' GROUP BY PrnCode ORDER BY COUNT(*) DESC, I.PrnCode SET NOCOUNT ON UPDATE X SET FseInclude = 'N', Note = 'Duplicate Distributor/Mfr/MfrSku records encountered' FROM tbl_IMPORT_DistributorSkus X WHERE X.eai_batchId = AND X.ownerId = AND X.ownerType = AND CAST(X.CustomerId As Varchar(20)) + '/' + CAST(X.MfrId As Varchar(20)) + '/' + X.MfrSku IN (SELECT CAST(I.CustomerId As Varchar(20)) + '/' + CAST(I.MfrId As Varchar(20)) + '/' + I.MfrSku FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND I.FseInclude = 'Y' AND I.MfrId IS NOT NULL AND I.MfrSku IS NOT NULL AND I.CustomerId IS NOT NULL GROUP BY I.CustomerId, I.MfrId, I.MfrSku HAVING COUNT(*) > 1 ) SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON UPDATE CS SET CustomerSku = I.CustomerSku FROM tbl_PRD_CustomerSkus CS INNER JOIN tbl_IMPORT_DistributorSkus I ON I.OwnerID = CS.ownerId AND I.OwnerType = CS.FSL_TableCode AND I.MfrSku = CS.Sku AND I.MfrId = CS.MfrId AND I.CustomerId = CS.customerID AND I.CustomerKey = CS.CustomerKey WHERE I.eai_batchId = AND CS.ownerId = AND CS.FSL_TableCode = AND CS.LinkType = 'SKU' AND I.FseInclude = 'Y' AND NOT I.CustomerSku IS NULL AND NOT I.CustomerSku = '' AND NOT I.MfrId IS NULL AND NOT CS.CustomerSku = I.CustomerSku SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON INSERT INTO tbl_PRD_CustomerSkus (OwnerID, FSL_TableCode, sku, LinkType, CustomerID, CustomerType, CustomerSku, MfrId) SELECT #qmd_batch.ownerId#, '#qmd_batch.ownerType#', MfrSku, 'SKU' As LinkType, D.cdr_recordid As CustomerID, 'DST' As 'CustomerType', I.CustomerSku, A.ObjectId As MfrId FROM tbl_IMPORT_DistributorSkus I INNER JOIN tbl_CDR_Distributors D ON D.OwnerID = I.ownerId AND D.FSL_TableCode = I.ownerType AND D.cdr_dstcode = I.CstCode INNER JOIN tbl_IMPORT_Aliases A ON A.ownerId = I.OwnerID AND A.ownerType = I.ownerType AND A.objectType = 'MFRID' AND A.Alias = I.PrnCode WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND I.CustomerSku IS NOT NULL AND NOT I.CustomerSku = '' AND I.FseInclude = 'Y' AND A.ObjectId IS NOT NULL AND NOT EXISTS (SELECT * FROM tbl_PRD_CustomerSkus CS WHERE CS.OwnerID = AND CS.FSL_TableCode = AND CS.MfrId = A.ObjectId AND CS.LinkType = 'SKU' AND CS.Sku = I.MfrSku AND CS.CustomerID = D.cdr_recordid) ORDER BY D.Cdr_DstName, I.CstCode, I.PrnCode, I.MfrSku, I.CustomerSku SELECT rowsEffected=@@ROWCOUNT SET NOCOUNT OFF SELECT CustSkuCnt = COUNT(*) FROM tbl_PRD_CustomerSkus WHERE OwnerId = AND FSL_TableCode = SET NOCOUNT ON SELECT ExclusionCnt = COUNT(*) FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND FseInclude = 'N' SET NOCOUNT OFF SET NOCOUNT ON SELECT NewOrderCnt = COUNT(*) FROM dbo.tbl_CDR_Orders WHERE Source = AND SourceType = 'BatchId' AND OwnerId = AND OwnerType = AND CreateDate = #_InsertDate# SET NOCOUNT OFF
Total Distributor SKU Record Count:   #qmd_CustomerSku.CustSkuCnt#
Existing Records Updated: #qmd_UpdCustSku.rowsEffected#
New Records Inserted: #qmd_InsertRecords.rowsEffected#
Excluded Record Count: #qmd_Exclusions.ExclusionCnt#
Unmapped PrnCodes Count: #qmd_UnmappedPrnCodes.RecordCount#
New Orders Inserted: #qmd_NewOrders.NewOrderCnt#


SET NOCOUNT ON SELECT Note, COUNT(*) Cnt FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND FseInclude = 'N' GROUP BY Note SET NOCOUNT OFF SET NOCOUNT ON SELECT CstCode, CustomerId, MfrId, MfrSku, PrnCode, CustomerSku FROM tbl_IMPORT_DistributorSkus X WHERE X.eai_batchId = AND X.ownerId = AND X.ownerType = AND CAST(X.CustomerId As Varchar(20)) + '/' + CAST(X.MfrId As Varchar(20)) + '/' + X.MfrSku IN (SELECT CAST(I.CustomerId As Varchar(20)) + '/' + CAST(I.MfrId As Varchar(20)) + '/' + I.MfrSku FROM tbl_IMPORT_DistributorSkus I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND I.FseInclude = 'N' AND I.MfrId IS NOT NULL AND I.MfrSku IS NOT NULL AND I.CustomerId IS NOT NULL GROUP BY I.CustomerId, I.MfrId, I.MfrSku HAVING COUNT(*) > 1 ) ORDER BY CstCode, CustomerId, MfrId, MfrSku, PrnCode SET NOCOUNT OFF
Excluded Record Message Count
#Note#   #Cnt#


Duplicate Distributor/Mfr/MfrSku Records

CstCode CustomerId MfrId MfrSku PrnCode CustomerSku
#CstCode#   #CustomerId# #MfrId# #MfrSku# #PrnCode#   #CustomerSku#


Unmapped Principal Codes

PrnCode Count
#PrnCode#   #Cnt#