gistfile1.sql

SELECT  i.ItemNo 
	 ,	ic.InventoryCountingId
	 ,  ict.TypeCode InventoryCountingType
	 ,  ru.RetailUnitNo RetailUnitNo
	 ,  ru.RetailUnitId RetailUnitId
	 ,	MAX(ic.InventoryCountingDate) InventoryCountingDate
     ,	MAX(ccmInventoryCountingCauseCode.Code) InventoryCountingCauseCode
	 ,	MAX(ccmInventoryCountingDailyStatusCode.Code) InventoryCountingDailyStatusCode
	 ,	MAX(ccmItemType.Code) ItemType
	 ,	SUM(icd.CountedQty) CountedQty
	 ,	MAX(il.ObtainedTransferPriceAmt) InventoryTransferPriceAmt 
	 ,	MAX(hdCategory.DetailNo) ItemCategory
	 ,	MAX(dbo.GetInventoryTransferPriceMethod(il.ItemLocalId,ic.InventoryCountingDate)) InventoryTransferPriceMethodId
	 ,	MAX(SalesPriceAmt.PriceAmt) SalesPriceAmt
	 ,  MAX(CASE ccmItemType.Code WHEN '2' THEN 'K' ELSE 'S' END) MeasurementType
  FROM InventoryCounting ic
    INNER JOIN InventoryCountingDaily icd ON icd.InventoryCountingId=ic.InventoryCountingId
    INNER JOIN InventoryCountingGroup icg ON ic.InventoryCountingGroupId=icg.InventoryCountingGroupId
    INNER JOIN InventoryCountingInventoryCountingSchedule icics ON icics.InventoryCountingId=ic.InventoryCountingId
    INNER JOIN InventoryCountingSchedule ics ON ics.InventoryCountingScheduleId = icics.InventoryCountingScheduleId
    INNER JOIN InventoryCountingType ict ON ics.InventoryCountingTypeId=ict.InventoryCountingTypeId
    INNER JOIN InventoryCountingScheduleLine icsl ON icsl.InventoryCountingScheduleId=ics.InventoryCountingScheduleId AND icsl.ItemLocalId = icd.ItemLocalId
    INNER JOIN InventoryCountingCause icc ON icc.InventoryCountingScheduleLineId = icsl.InventoryCountingScheduleLineId
    INNER JOIN RetailUnit ru ON ru.RetailUnitId=ic.RetailUnitId
  	INNER JOIN ItemLocal il ON il.ItemLocalId = icd.ItemLocalId AND il.RetailUnitId = ru.RetailUnitId
	INNER JOIN Item i ON il.ItemId = i.ItemId
	INNER JOIN ItemOrderingUnit iou ON i.ItemId = iou.ItemId
	INNER JOIN RetailUnitItemOrderingUnit ruiou ON ruiou.ItemOrderingUnitId = iou.ItemOrderingUnitId AND ruiou.IsActive = '1' AND ruiou.IsItemOrderingUnitPrimary = 1
	INNER JOIN dbo.HierarchyItem hi ON hi.ItemId = i.ItemId
    INNER JOIN dbo.HierarchyDetail hdCategory ON hi.HierarchyDetailId = hdCategory.HierarchyDetailId
    INNER JOIN CommonCodeMaster ccm ON ccm.CommonCodeMasterId = ic.InventoryCountingStatusCodeId
	INNER JOIN CommonCodeMaster ccmInventoryCountingCauseCode ON ccmInventoryCountingCauseCode.CommonCodeMasterId = icc.InventoryCountingCauseCodeId
	LEFT OUTER JOIN CommonCodeMaster ccmInventoryCountingDailyStatusCode ON ccmInventoryCountingDailyStatusCode.CommonCodeMasterId = icd.InventoryCountingDailyStatusCodeId
	INNER JOIN CommonCodeMaster ccmItemType ON ccmItemType.CommonCodeMasterId = i.ItemTypeId
	OUTER APPLY dbo.GetSalesPriceAmt_TV(il.ItemLocalId,ic.InventoryCountingDate) SalesPriceAmt
 WHERE ic.CollectedDateTime IS NULL
   AND icg.GroupNo='01'
   AND ccm.Code ='G'
 GROUP	BY i.ItemNo, ic.InventoryCountingId, ict.TypeCode,ru.RetailUnitNo,ru.RetailUnitId
  UNION
 SELECT	v.ItemNo 
	 ,	ic.InventoryCountingId
	 ,  ict.TypeCode InventoryCountingType
	 ,  ru.RetailUnitNo RetailUnitNo
	 ,  ru.RetailUnitId RetailUnitId
	 ,	MAX(ic.InventoryCountingDate) InventoryCountingDate
	 ,	ict.TypeCode
	 ,	'G' InventoryCountingDailyStatusCode 
	 ,	MAX(v.itemType) ItemType
	 ,	SUM(v.counted) CountedQty
	 ,	MAX(v.StockTransferPrice) InventoryTransferPriceAmt 
	 ,	MAX(v.DetailNo) ItemCategory
	 ,	MAX(v.StockTransferPriceMethod) InventoryTransferPriceMethodId
	 ,	MAX(v.SalesPrice) SalesPriceAmt
	 ,  MAX(CASE v.WeightedQty WHEN NULL THEN 'S' ELSE 'K' END) MeasurementType
  FROM RetailUnit ru
    
    inner join InventoryCounting ic on ic.RetailUnitId = ru.RetailUnitId
    INNER JOIN CommonCodeMaster ccm ON ccm.CommonCodeMasterId = ic.InventoryCountingStatusCodeId
    INNER JOIN InventoryCountingGroup icg ON ic.InventoryCountingGroupId=icg.InventoryCountingGroupId
    INNER JOIN InventoryCountingInventoryCountingSchedule icics ON icics.InventoryCountingId=ic.InventoryCountingId
    INNER JOIN InventoryCountingSchedule ics ON ics.InventoryCountingScheduleId = icics.InventoryCountingScheduleId
    INNER JOIN InventoryCountingType ict ON ics.InventoryCountingTypeId=ict.InventoryCountingTypeId
    
    left join InventoryCountingToInventoryCounting ic2ic on ic.InventoryCountingId = ic2ic.ParentInventoryCountingId
    left join InventoryCounting ic2 on ic2ic.ChildInventoryCountingId = ic2.InventoryCountingId
    cross apply (select i.ItemNo
    , ccmItemType.Code as itemType
    , ISNULL(icpl.WeightedQty, icpl.CountedQty) as counted
    , i.ItemId
    , prices.StockPrice as StockTransferPrice
    , case ccmItemType.Code
      when '1' then '3' 
      else icil.StockTransferPriceMethod
      end as StockTransferPriceMethod
    , prices.SalesPrice as SalesPrice
    , icpl.WeightedQty
    , icc.DetailNo    
     from InventoryCountingPlanned icp
       inner JOIN InventoryCountingPlannedLine icpl on icpl.InventoryCountingPlannedId=icp.InventoryCountingPlannedId
	   inner JOIN InventoryCountingItemLocal icil ON icil.InventoryCountingItemLocalId = icp.InventoryCountingItemLocalId
	   INNER JOIN ItemLocal il ON il.ItemLocalId = icil.ItemLocalId AND il.RetailUnitId = ru.RetailUnitId
	   INNER JOIN Item i ON il.ItemId = i.ItemId
	   INNER JOIN ItemVatRate ivr ON ivr.ItemId = i.ItemId 
	   INNER JOIN VATRate vr ON vr.VATRateId = ivr.VATRateId AND vr.CountryId = ru.CountryId
	   INNER JOIN CommonCodeMaster ccmItemType ON ccmItemType.CommonCodeMasterId = i.ItemTypeId
	   INNER JOIN InventoryCountingCategory icc ON icc.InventoryCountingCategoryId = icil.InventoryCountingCategoryId
	   CROSS APPLY dbo.GetPriceAndValueForInventoryCountingPlannedLine_TV(ccmItemType.Code, 		
		icpl.CountedQty,
		icpl.WeightedQty,
		icpl.ManuallyEnteredSalesPriceAmt,
		icil.SalesPrice,
		icil.StockTransferPrice,
		dbo.GetStockCountEstimatedGrossProfitPct(icc.EstimatedGrossProfitPct),
		vr.VATRatePct) as prices
	   where icp.InventoryCountingId in (ic.InventoryCountingId, ic2.InventoryCountingId)
	   and ccmItemType.Code <> '1'
	   union 
	select i.ItemNo
    , ccmItemType.Code as itemType
    , 1 as counted
    , i.ItemId
    , sum( icpl.CountedQty * prices.StockPrice) as StockTransferPrice
    , '3' as  StockTransferPriceMethod
    , sum(icpl.CountedQty * prices.SalesPrice) as SalesPrice
    , null WeightedQty
    , icc.DetailNo
    
     from InventoryCountingPlanned icp
       inner JOIN InventoryCountingPlannedLine icpl on icpl.InventoryCountingPlannedId=icp.InventoryCountingPlannedId
	   inner JOIN InventoryCountingItemLocal icil ON icil.InventoryCountingItemLocalId = icp.InventoryCountingItemLocalId
	   INNER JOIN ItemLocal il ON il.ItemLocalId = icil.ItemLocalId AND il.RetailUnitId = ru.RetailUnitId
	   INNER JOIN Item i ON il.ItemId = i.ItemId
	   INNER JOIN ItemVatRate ivr ON ivr.ItemId = i.ItemId 
	   INNER JOIN VATRate vr ON vr.VATRateId = ivr.VATRateId AND vr.CountryId = ru.CountryId
	   INNER JOIN CommonCodeMaster ccmItemType ON ccmItemType.CommonCodeMasterId = i.ItemTypeId
	   INNER JOIN InventoryCountingCategory icc ON icc.InventoryCountingCategoryId = icil.InventoryCountingCategoryId
	   CROSS APPLY dbo.GetPriceAndValueForInventoryCountingPlannedLine_TV(ccmItemType.Code, 		
		icpl.CountedQty,
		icpl.WeightedQty,
		icpl.ManuallyEnteredSalesPriceAmt,
		icil.SalesPrice,
		icil.StockTransferPrice,
		dbo.GetStockCountEstimatedGrossProfitPct(icc.EstimatedGrossProfitPct),
		vr.VATRatePct) as prices
	   where icp.InventoryCountingId in (ic.InventoryCountingId, ic2.InventoryCountingId)
	   and ccmItemType.Code = '1'
	   group by i.ItemId, ccmItemType.Code, icc.DetailNo, i.ItemNo
    ) v
 WHERE ic.CollectedDateTime IS NULL
   AND icg.GroupNo='02'
   AND ccm.Code = 'G'
 GROUP	BY v.ItemNo, ic.InventoryCountingId, ict.TypeCode,ru.RetailUnitNo,ru.RetailUnitId