ReceiptItemLineExportDto_VW.sql

Use TrioReceipt
GO
--
-- S2H: Trans036
-- ReceiptItemLine (Section 3)
--
ALTER VIEW dbo.ReceiptItemLineExportDto_VW AS 
SELECT rh.ReceiptHeaderId ReceiptHeaderId
	 , ru.RetailUnitNo RetailUnitNo
	 , ril.ItemLineNo ItemLineNo
	 , ril.ItemCodeNo ItemCodeNo
	 , CAST(il.ItemNo AS VARCHAR(13))  TransformedItemCodeValue
	 , il.ReceiptText ReceiptText
	 , ril.IsItemCodeTyped IsItemCodeTyped
	 , CASE WHEN rpm.ReceiptPriceModificationId IS NULL THEN 0 ELSE 1 END IsPriceTyped
	 , CASE WHEN ril.PriceType = '1' THEN 1 ELSE 0 END IsPriceLocal
	 , ril.IsCancelled IsCancelled
	 , il.VATRatePct VATRatePct
	 , il.ItemType ItemType
	 , ril.ItemQty ItemQty
	 , ril.ItemAmt ItemAmt
     , ISNULL(ril.WeightQty, 0) as WeightItemSummaryQty
	 , ISNULL(ril.ObtainedTransferPriceAmt,0) ObtainedTransferPriceAmt
	 , ISNULL(ABS(ril.GrossItemAmt - ril.ItemAmt),0) DiscountAmt
	 , ril.ItemTransactionType TransactionType
	 , il.ItemCategory ItemCategory
	 , 0 PseudoCode
	 , CASE WHEN il.HasUniqueItemType = 0 THEN '1' ELSE '3' END UniqueItemType
  FROM ReceiptHeader rh
	   INNER JOIN RetailUnitInformation ru ON ru.RetailUnitId = rh.RetailUnitId
	   INNER JOIN ReceiptItemLine ril ON ril.ReceiptHeaderId = rh.ReceiptHeaderId
	   LEFT OUTER JOIN ReceiptPriceModification rpm ON (rpm.ReceiptHeaderId = ril.ReceiptHeaderId AND rpm.ItemLineNo = ril.ItemLineNo AND rpm.ModificationMethod = 'PriceOverride')
       CROSS APPLY (
          SELECT TOP 1 *
          FROM ItemLocalInformation ili
          WHERE ili.ItemLocalId = ril.ItemLocalId
       ) il