SET TRANSACTION ISOLATION LEVEL read uncommitted

SET NOCOUNT ON
--SELECT SUM (p) FROM
--(
Select
 vw_ce_dms_transactions.TransactionDateTime,
 vw_ce_dms_transactions.UID,
 vw_ce_dms_transactions.Units,
 vw_ce_dms_transactions.NetWeight,
 oms_wo_header.WONo,
 TraderDF.Text1,
 vw_df_plu_file.PLUCode,
 vw_df_plu_file.PLUDescription,
 vw_df_plu_file.ChargeQty,

 CASE
  When ce_oms_wo_lines.WOLineDFID Is NOT  Null THEN ce_oms_wo_lines.AValue1
  ELSE vw_df_plu_file.UOM
 End As UOM,

 ce_ims_items.NValue6 As Pieces,
 ce_ims_items.AValue1 As ProdBatchNo


 -----
 ,ISNULL((
CASE when
    vw_df_plu_file.ChargeQty = 1 AND IsNull(CASE when ce_oms_wo_lines.WOLineDFID Is Null THEN vw_df_plu_file.UOM ELSE ce_oms_wo_lines.AValue1 End, 'Box')  = 'Box' THEN dbo.vw_ce_dms_transactions.Units
when vw_df_plu_file.ChargeQty = 1 AND IsNull(CASE when ce_oms_wo_lines.WOLineDFID Is Null THEN vw_df_plu_file.UOM ELSE ce_oms_wo_lines.AValue1 End, 'Each')  = 'Each' THEN ce_ims_items.NValue6
when vw_df_plu_file.ChargeQty = 1 AND IsNull(CASE when ce_oms_wo_lines.WOLineDFID Is Null THEN vw_df_plu_file.UOM ELSE ce_oms_wo_lines.AValue1 End, 'Unit')  = 'Unit' THEN ce_ims_items.NValue6
ELSE dbo.vw_ce_dms_transactions.NetWeight END
),0)


*

IsNull(CASE
   --When ce_oms_wo_lines.WOLineDFID Is Null THEN IsNull(PL.Value,0 )
   When ce_oms_wo_lines.WOLineDFID Is Null THEN IsNull([dbo].[fn_ce_GetCustomerProductPrice](Cust.Code + '|' + vw_df_plu_file.PLUCode , oms_wo_header.WOValidFromDate, 0), 0)
   -- swapped FOR ticket 6919


   ELSE oms_wo_lines.WOLinePrice
  End,'0') AS Price

 -----

From vw_ce_dms_transactions
Inner Join oms_order_header
 On oms_order_header.OrderType = 'SO'
 AND oms_order_header.OrderNo = vw_ce_dms_transactions.WONo
 AND oms_order_header.OrderVersion = 0
Inner Join oms_wo_header
 On oms_wo_header.WOType = 'SO'
 AND oms_wo_header.WONo = oms_order_header.OrderNo
 AND oms_wo_header.WOVersion = 0
Inner Join datafiles_data As TraderDF
 On TraderDF.DatafileDataID = oms_order_header.TraderID
Inner Join vw_df_plu_file
 On vw_ce_dms_transactions.PLUID = vw_df_plu_file.PLUID
Inner Join ce_ims_items
 On ce_ims_items.ItemTypeCode = 'BOX'
 AND ce_ims_items.ItemID = vw_ce_dms_transactions.UID
Left join ce_oms_wo_lines
 On ce_oms_wo_lines.WOType = 'SO'
 AND ce_oms_wo_lines.WONo = vw_ce_dms_transactions.WONo
 AND ce_oms_wo_lines.WOVersion = 0
 AND ce_oms_wo_lines.WOLineNo = vw_ce_dms_transactions.WOLineNo
 AND ce_oms_wo_lines.WOLineDFID = vw_ce_dms_transactions.PLUID
Left join oms_wo_lines
 On oms_wo_lines.WOType = 'SO'
 AND oms_wo_lines.WONo = vw_ce_dms_transactions.WONo
 AND oms_wo_lines.WOVersion = 0
 AND oms_wo_lines.WOLineNo = vw_ce_dms_transactions.WOLineNo
 AND oms_wo_lines.WOLineDFID = vw_ce_dms_transactions.PLUID
---
Inner Join datafiles_data As Cust
   On Cust.DatafileDataID = oms_order_header.TraderID

--Left Join kp_settlement_price_tracker As PL
--  On PriceTypeCode = 'CUSTOMER_PRODUCT_PRICE'
--  AND [Key] LIKE Cust.Code + '|' + vw_df_plu_file.PLUCode
--  AND ValidFromDatetime <= oms_wo_header.WOValidFromDate
--      removed FOR ticket 6919

Where
 TransactionType = 'PALLET_DISPATCH_PICKING'
 AND (TransactionDateTime between '20150117' AND '20150218 23:59:59')
 AND Cancelled = 0
Order BY DMSTransactionID desc