SELECT pd.PromotionID, pd.Descr,
		pd.SchemeID,pd.EffectiveDate, pd.EndDate,
		 cd.DistributorID,
		 dl.DistributorName,
		  cde.InventoryID AS FocID,
		  ii.Descr AS FocName,
		  COUNT( cde.InventoryID) AS Quanity,

           CASE WHEN cde.Rate IS NOT NULL
		  THEN cde.ChangingInventoryID
		  WHEN cde.Rate IS NULL
		  THEN cde.InventoryID
		  END
		  SKU,
		     CASE WHEN cde.ChangingInventoryID IS NOT NULL
		  THEN (SELECT i1.Descr FROM MRCDMS..InventoryItem i1 WHERE i1.InventoryID = cde.ChangingInventoryID)
		  WHEN cde.ChangingInventoryID IS NULL
		  THEN ii.Descr
		  END
		  SkuName,
		    ---Rate
		  CASE WHEN cde.Rate IS NOT NULL
		  THEN cde.Rate
		  WHEN cde.Rate IS NULL
		  THEN 1
		  END
		  Rate,
		    CASE WHEN cde.Rate IS NOT NULL
		  THEN (cde.Rate *(COUNT( cde.InventoryID)))
		  WHEN cde.Rate IS NULL
		  THEN (1 *(COUNT( cde.InventoryID)))
		  END
		  SkuQuantity

		 FROM MRCDMS..DMSPRODiscount pd ----Define program Discount
		INNER JOIN  MRCDMS..DMSClaimDistributors --Get Distributor
		 cd ON cd.CompanyID = pd.CompanyID
		AND pd.PromotionID = cd.ProgramID INNER JOIN
		MRCDMS..DMSClaimDefine cld -- Claim Define Type P : Promotion , L:Loyalty, V:Visibility
		ON cld.CompanyID = cd.CompanyID AND cld.RefNbr = cd.RefNbr
		INNER JOIN DMSBLDistributorLocation dl -- Get distributor location detail
		 ON dl.CompanyID = cd.CompanyID AND cd.DistributorID = dl.DistributorID
		INNER JOIN  (
		 --Get top DMSClaimDetails by RefNbr, LineNBR following ProgramID and Inventory
			SELECT * FROM (	SELECT  RANK() OVER (PARTITION BY cds.ProgramID,
		 cds.InventoryID ORDER BY cds.RefNbr DESC, cds.LineNbr DESC) AS RankRef,
		 cds.CompanyID, cds.RefNbr,cds.ProgramID,
		  cds.InventoryID, cds.Rate, cds.LineNbr, cds.ChangingInventoryID FROM MRCDMS..DMSClaimDetails cds)
		   temp WHERE temp.RankRef = 1) cde --Detail program detail
		ON cde.CompanyID = cd.CompanyID
		AND cde.ProgramID = pd.PromotionID
		INNER JOIN MRCDMS..InventoryItem ii -- Inventory Item
		 ON ii.CompanyID = cde.CompanyID
		 AND cde.InventoryID = ii.InventoryID
		 WHERE cd.Status = 'A' --Acknown
		 AND cld.Type = 'P' --Type promotion
		 GROUP BY pd.PromotionID, pd.Descr,
		 pd.SchemeID,pd.EffectiveDate, pd.EndDate,
		 cd.DistributorID,
		  dl.DistributorName,
		  cde.InventoryID,
		   ii.Descr,
		   cde.Rate,
		    cde.ChangingInventoryID