CREATE OR REPLACE VIEW M_INOUT_LINE_V
AS 
SELECT iol.AD_Client_ID AS AD_Client_ID, iol.AD_Org_ID AS AD_Org_ID, iol.IsActive AS IsActive, 
    iol.Created AS Created, iol.CreatedBy AS CreatedBy, iol.Updated AS Updated, iol.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    iol.M_InOut_ID AS M_InOut_ID, iol.M_InOutLine_ID AS M_InOutLine_ID,
    iol.Line AS Line, p.M_Product_ID AS M_Product_ID,
    CASE WHEN iol.MovementQty<>0 OR iol.M_Product_ID IS NOT NULL THEN iol.MovementQty END AS MovementQty,
    CASE WHEN iol.QtyEntered<>0 OR iol.M_Product_ID IS NOT NULL THEN iol.QtyEntered END AS QtyEntered,
    CASE WHEN iol.MovementQty<>0 OR iol.M_Product_ID IS NOT NULL THEN uom.UOMSymbol END AS UOMSymbol,
    ol.QtyOrdered AS QtyOrdered, ol.QtyDelivered AS QtyDelivered,
    CASE WHEN iol.MovementQty<>0 OR iol.M_Product_ID IS NOT NULL THEN ol.QtyOrdered-ol.QtyDelivered END AS QtyBackOrdered,
    COALESCE(p.Name|| COALESCE(productAttribute(iol.M_AttributeSetInstance_ID),N''),c.Name,iol.Description) AS Name,
    CASE WHEN COALESCE(c.Name,p.Name) IS NOT NULL THEN iol.Description END AS Description,
    p.DocumentNote AS DocumentNote,
    p.UPC AS UPC, p.SKU AS SKU, p.Value AS ProductValue,
    iol.M_Locator_ID AS M_Locator_ID, l.M_Warehouse_ID AS M_Warehouse_ID, l.X AS X, l.Y AS Y, l.Z AS Z,
    iol.M_AttributeSetInstance_ID AS M_AttributeSetInstance_ID, asi.M_AttributeSet_ID AS M_AttributeSet_ID,
    asi.SerNo AS SerNo, asi.Lot AS Lot, asi.M_Lot_ID AS M_Lot_ID, asi.GuaranteeDate AS GuaranteeDate,
    p.Description AS ProductDescription, p.ImageURL AS ImageURL,
    iol.C_Campaign_ID AS C_Campaign_ID, iol.C_Project_ID AS C_Project_ID,
    iol.C_Activity_ID AS C_Activity_ID, iol.C_ProjectPhase_ID AS C_ProjectPhase_ID,
    iol.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  M_InOutLine iol
    INNER JOIN C_UOM uom ON (iol.C_UOM_ID=uom.C_UOM_ID)
    LEFT OUTER JOIN M_Product p ON (iol.M_Product_ID=p.M_Product_ID)
    LEFT OUTER JOIN M_AttributeSetInstance asi ON (iol.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID)
    LEFT OUTER JOIN M_Locator l ON (iol.M_Locator_ID=l.M_Locator_ID)
    LEFT OUTER JOIN C_OrderLine ol ON (iol.C_OrderLine_ID=ol.C_OrderLine_ID)
    LEFT OUTER JOIN C_Charge c ON (iol.C_Charge_ID=c.C_Charge_ID)
UNION 
SELECT iol.AD_Client_ID AS AD_Client_ID, iol.AD_Org_ID AS AD_Org_ID, iol.IsActive AS IsActive,
    iol.Created AS Created, iol.CreatedBy AS CreatedBy, iol.Updated AS Updated, iol.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    iol.M_InOut_ID AS M_InOut_ID, iol.M_InOutLine_ID AS M_InOutLine_ID,
    iol.Line+(b.Line/100) AS Line, p.M_Product_ID AS M_Product_ID,
    iol.MovementQty*b.BOMQty AS MovementQty,
    iol.QtyEntered*b.BOMQty AS QtyEntered,
    uom.UOMSymbol AS UOMSymbol,
    nullif(1,1) AS QtyOrdered,
    NULLIF(1,1) AS QtyDelivered,
    NULLIF(1,1) AS QtyBackOrdered,
    p.Name AS Name,
    b.Description AS Description,
    p.DocumentNote AS DocumentNote,
    p.UPC AS UPC, p.SKU AS SKU,
    p.Value AS ProductValue,
    iol.M_Locator_ID AS M_Locator_ID, l.M_Warehouse_ID AS M_Warehouse_ID, l.X AS X, l.Y AS Y, l.Z AS Z,
    iol.M_AttributeSetInstance_ID AS M_AttributeSetInstance_ID, asi.M_AttributeSet_ID AS M_AttributeSet_ID,
    asi.SerNo AS SerNo, asi.Lot AS Lot, asi.M_Lot_ID AS M_Lot_ID, asi.GuaranteeDate AS GuaranteeDate,
    p.Description AS ProductDescription, p.ImageURL AS ImageURL,
    iol.C_Campaign_ID AS C_Campaign_ID, iol.C_Project_ID AS C_Project_ID,
    iol.C_Activity_ID AS C_Activity_ID, iol.C_ProjectPhase_ID AS C_ProjectPhase_ID,
    iol.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  M_Product_BOM b
    INNER JOIN M_InOutLine iol ON (b.M_Product_ID=iol.M_Product_ID)
    INNER JOIN M_Product bp ON (bp.M_Product_ID=iol.M_Product_ID
          AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsPickListPrintDetails='Y')
    INNER JOIN M_Product p ON (b.M_ProductBOM_ID=p.M_Product_ID)
    INNER JOIN C_UOM uom ON (p.C_UOM_ID=uom.C_UOM_ID)
    LEFT OUTER JOIN M_AttributeSetInstance asi ON (iol.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID)
    LEFT OUTER JOIN M_Locator l ON (iol.M_Locator_ID=l.M_Locator_ID)
/
