USE [befri_Goldhoo]
GO

/****** Object:  View [shop_order].[V_PurchaseOrderLine]    Script Date: 2020/12/8 11:22:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [shop_order].[V_PurchaseOrderLine]
AS
    SELECT  pol.PurchaseOrderLineId ,
            pol.PurchaseOrderId ,
            pol.InventoryType ,
            pol.ImageItemId ,
            pol.CombtItemId ,
            pol.CombtItemNumber ,
            pol.CombtItemName ,
            pol.InventoryId ,
            pol.InventoryNumber ,
            pol.InventoryName ,
            pol.Spec ,
            pol.Length ,
            pol.Width ,
            pol.Height ,
            pol.Uom ,
            pol.Quantity ,
            ISNULL(cl.ClosedQuantity, 0) ClosedQuantity ,
            ISNULL(cl.ClosedTime, 0) ClosedTime ,
            pol.RemainQty ,
            pol.Price ,
            pol.Freight ,
            pol.IsUrgent ,
            pol.IsAllotype ,
            pol.IsSpecial ,
            pol.SpecialSpec ,
            pol.SpecialFare ,
            pol.ApprovedIdea ,
            pol.Closed ,
            pol.ClosedBy ,
            pol.ClosedOn ,
            pol.ClosedReason ,
            pol.LineStatus ,
            vtm.Text AS LineStatusText ,
            pol.VersionNumber ,
            pol.Description ,
            vimi.ThumbnailUrl AS MasterImageUrl ,
            vimi.HandledUrl ,
            cAcc.DiscountCoef AS ClearanceDiscountCoef ,
            cAcc.Quantity AS ClearanceWhQty ,
            dAcc.DiscountCoef AS DiscountDiscountCoef ,
            dAcc.Quantity AS DiscountWhQty ,
            item.PriceType
    FROM    shop_order.PurchaseOrderLine AS pol
            INNER JOIN basedata.Inventory item ON item.InventoryId = pol.InventoryId
            LEFT OUTER JOIN basedata.V_InventoryMasterImage AS vimi ON pol.ImageItemId = vimi.InventoryId
            LEFT OUTER JOIN dbo.ValueTextMap AS vtm ON vtm.Value = pol.LineStatus
                                                       AND vtm.TableName = 'shop_order.PurchaseOrderLine'
                                                       AND vtm.FieldName = 'LineStatus'
            LEFT JOIN clearance.ClearanceAcc cAcc ON pol.InventoryId = cAcc.InventoryId
			LEFT JOIN Discount.DiscountAcc dAcc ON pol.InventoryId = dAcc.InventoryId
            LEFT JOIN ( SELECT  COUNT(1) ClosedTime ,
                                VoucherId PurchaseOrderLineId ,
                                SUM(Quantity) ClosedQuantity
                        FROM    [dbo].[VoucherProcess]
                        WHERE   tableName = 'shop_order.PurchaseOrderLine'
                        GROUP BY VoucherId
                      ) cl ON cl.PurchaseOrderLineId = pol.PurchaseOrderLineId

GO