CREATE OR REPLACE VIEW C_ORDER_LINETAX_V
AS 
SELECT ol.AD_Client_ID AS AD_Client_ID, ol.AD_Org_ID AS AD_Org_ID, ol.IsActive AS IsActive, 
  ol.Created AS Created, ol.CreatedBy AS CreatedBy, ol.Updated AS Updated, ol.UpdatedBy AS UpdatedBy,  
  CAST('en_US' AS VARCHAR(5)) AS AD_Language,  
  ol.C_Order_ID AS C_Order_ID, ol.C_OrderLine_ID AS C_OrderLine_ID, 
  ol.C_Tax_ID AS C_Tax_ID, t.TaxIndicator AS TaxIndicator,     
  ol.C_BPartner_ID AS C_BPartner_ID, ol.C_BPartner_Location_ID AS C_BPartner_Location_ID, 
  bp.Name AS BPName, bpl.C_Location_ID AS C_Location_ID,  
  ol.Line AS Line, 
  p.M_Product_ID AS M_Product_ID,  
  CASE WHEN ol.QtyOrdered<>0 OR ol.M_Product_ID IS NOT NULL THEN ol.QtyOrdered END AS QtyOrdered,      
  CASE WHEN ol.QtyEntered<>0 OR ol.M_Product_ID IS NOT NULL THEN ol.QtyEntered END AS QtyEntered,      
  CASE WHEN ol.QtyEntered<>0 OR ol.M_Product_ID IS NOT NULL THEN uom.UOMSymbol END AS UOMSymbol,   
  COALESCE(c.Name,p.Name|| COALESCE(productAttribute(ol.M_AttributeSetInstance_ID),N''),ol.Description) AS Name,   
  CASE WHEN COALESCE(c.Name,p.Name) IS NOT NULL THEN ol.Description END AS Description,   
  p.DocumentNote AS DocumentNote,      
  p.UPC AS UPC, p.SKU AS SKU, 
  COALESCE(pp.VendorProductNo,p.Value) AS ProductValue,  
  ra.Description AS ResourceDescription,
  CASE WHEN i.IsDiscountPrinted='Y' AND ol.PriceList<>0 THEN ol.PriceList END AS PriceList,  
  CASE WHEN i.IsDiscountPrinted='Y' AND ol.PriceList<>0 AND ol.QtyEntered<>0 
               THEN ol.PriceList*ol.QtyOrdered/ol.QtyEntered END AS PriceEnteredList,  
  CASE WHEN i.IsDiscountPrinted='Y' AND ol.PriceList>ol.PriceActual AND ol.PriceList<>0
               THEN (ol.PriceList-ol.PriceActual)/ol.PriceList*100 END AS Discount,  
  CASE WHEN ol.PriceActual<>0 OR ol.M_Product_ID IS NOT NULL 
               THEN ol.PriceActual END AS PriceActual,   
  CASE WHEN ol.PriceEntered<>0 OR ol.M_Product_ID IS NOT NULL 
               THEN ol.PriceEntered END AS PriceEntered,   
  CASE WHEN ol.LineNetAmt<>0 OR ol.M_Product_ID IS NOT NULL 
               THEN ol.LineNetAmt END AS LineNetAmt,     
  p.Description AS ProductDescription, p.ImageURL AS ImageURL,     
  ol.C_Campaign_ID AS C_Campaign_ID, 
  ol.C_Project_ID AS C_Project_ID, 
  ol.C_Activity_ID AS C_Activity_ID, 
  ol.C_ProjectPhase_ID AS C_ProjectPhase_ID, 
  ol.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  C_OrderLine ol  
    INNER JOIN C_UOM uom ON (ol.C_UOM_ID=uom.C_UOM_ID)  
    INNER JOIN C_Order i ON (ol.C_Order_ID=i.C_Order_ID)  
    LEFT OUTER JOIN M_Product p ON (ol.M_Product_ID=p.M_Product_ID)  
    LEFT OUTER JOIN S_ResourceAssignment ra ON (ol.S_ResourceAssignment_ID=ra.S_ResourceAssignment_ID)  
    LEFT OUTER JOIN C_Charge c ON (ol.C_Charge_ID=c.C_Charge_ID)     
    LEFT OUTER JOIN C_BPartner_Product pp ON (ol.M_Product_ID=pp.M_Product_ID AND i.C_BPartner_ID=pp.C_BPartner_ID)  
    INNER JOIN C_BPartner bp ON (ol.C_BPartner_ID=bp.C_BPartner_ID)  
    INNER JOIN C_BPartner_Location bpl ON (ol.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)  
    LEFT OUTER JOIN C_Tax t ON (ol.C_Tax_ID=t.C_Tax_ID)  
UNION 
SELECT ol.AD_Client_ID AS AD_Client_ID, ol.AD_Org_ID AS AD_Org_ID, ol.IsActive AS IsActive, 
  ol.Created AS Created, ol.CreatedBy AS CreatedBy, ol.Updated AS Updated, ol.UpdatedBy AS UpdatedBy,  
  CAST('en_US' AS VARCHAR(5)) AS AD_Language,  
  ol.C_Order_ID AS C_Order_ID, ol.C_OrderLine_ID AS C_OrderLine_ID, 
  ol.C_Tax_ID AS C_Tax_ID, NULL AS TaxIndicator,
  nullif(1,1) AS C_BPartner_ID, NULLIF(1,1) AS C_BPartner_Location_ID, 
  NULL AS BPName, NULLIF(1,1) AS C_Location_ID,
  ol.Line+(b.Line/100) AS Line, 
  p.M_Product_ID AS M_Product_ID,
  ol.QtyOrdered*b.BOMQty AS QtyOrdered, 
  ol.QtyEntered*b.BOMQty AS QtyEntered,
  uom.UOMSymbol AS UOMSymbol,
  p.Name AS Name,
  b.Description AS Description,
  p.DocumentNote AS DocumentNote,
  p.UPC AS UPC, p.SKU AS SKU, 
  p.Value AS ProductValue,
  NULL AS ResourceDescription,   
  nullif(1,1) AS PriceList, 
  NULLIF(1,1) AS PriceEnteredList,
  NULLIF(1,1) AS Discount, 
  NULLIF(1,1) AS PriceActual, 
  NULLIF(1,1) AS PriceEntered, 
  NULLIF(1,1) AS LineNetAmt,
  p.Description AS ProductDescription, p.ImageURL AS ImageURL,
  ol.C_Campaign_ID AS C_Campaign_ID, 
  ol.C_Project_ID AS C_Project_ID,
  ol.C_Activity_ID AS C_Activity_ID, 
  ol.C_ProjectPhase_ID AS C_ProjectPhase_ID, 
  ol.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  M_Product_BOM b   
  INNER JOIN C_OrderLine ol ON (b.M_Product_ID=ol.M_Product_ID)  
  INNER JOIN M_Product bp ON (bp.M_Product_ID=ol.M_Product_ID AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsInvoicePrintDetails='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)
UNION 
SELECT AD_Client_ID AS AD_Client_ID, AD_Org_ID AS AD_Org_ID, IsActive AS IsActive, 
  Created AS Created, CreatedBy AS CreatedBy, Updated AS Updated, UpdatedBy AS UpdatedBy,  
  CAST('en_US' AS VARCHAR(5)) AS AD_Language, 
  C_Order_ID AS C_Order_ID, NULLIF(1,1) AS C_OrderLine_ID, 
  NULLIF(1,1) AS C_Tax_ID, NULL AS TaxIndicator,
  nullif(1,1) AS C_BPartner_ID, NULLIF(1,1) AS C_BPartner_Location_ID, 
  NULL AS BPName, NULLIF(1,1) AS C_Location_ID,
  nullif(1,1) AS Line,
  NULLIF(1,1) AS M_Product_ID, 
  nullif(1,1) AS QtyOrdered, 
  NULLIF(1,1) AS QtyEntered,
  NULL AS UOMSymbol,
  NULL AS Name,
  NULL AS Description, 
  NULL AS DocumentNote,
  NULL AS UPC, NULL AS SKU,
  NULL AS ProductValue,
  NULL AS ResourceDescription,
  nullif(1,1) AS PriceList,
  NULLIF(1,1) AS PriceEnteredList,
  NULLIF(1,1) AS Discount,
  NULLIF(1,1) AS PriceActual,
  NULLIF(1,1) AS PriceEntered,
  NULLIF(1,1) AS LineNetAmt,
  NULL AS ProductDescription,  NULL AS ImageURL,  
  nullif(1,1) AS C_Campaign_ID, 
  NULLIF(1,1) AS C_Project_ID,
  NULLIF(1,1) AS C_Activity_ID, 
  NULLIF(1,1) AS C_ProjectPhase_ID,
  NULLIF(1,1) AS C_ProjectTask_ID 
FROM  C_Order  
UNION 
SELECT ot.AD_Client_ID AS AD_Client_ID, ot.AD_Org_ID AS AD_Org_ID, ot.IsActive AS IsActive,
  ot.Created AS Created, ot.CreatedBy AS CreatedBy, ot.Updated AS Updated, ot.UpdatedBy AS UpdatedBy,  
  CAST('en_US' AS VARCHAR(5)) AS AD_Language,   
  ot.C_Order_ID AS C_Order_ID, NULLIF(1,1) AS C_OrderLine_ID,
  ot.C_Tax_ID AS C_Tax_ID, t.TaxIndicator AS TaxIndicator,
  nullif(1,1) AS C_BPartner_ID, NULLIF(1,1) AS C_BPartner_Location_ID,
  NULL AS BPName, NULLIF(1,1) AS C_Location_ID,
  nullif(1,1) AS Line, NULLIF(1,1) AS M_Product_ID,
  nullif(1,1) AS QtyOrdered,
  NULLIF(1,1) AS QtyEntered,
  NULL AS UOMSymbol,
  t.Name AS Name,
  NULL AS Description,
  NULL AS DocumentNote,
  NULL AS UPC, NULL AS SKU,
  NULL AS ProductValue,
  NULL AS ResourceDescription,
  nullif(1,1) AS PriceList,
  NULLIF(1,1) AS PriceEnteredList,
  NULLIF(1,1) AS Discount,
  CASE WHEN ot.IsTaxIncluded='Y' THEN ot.TaxAmt ELSE ot.TaxBaseAmt END AS PriceActual,
  CASE WHEN ot.IsTaxIncluded='Y' THEN ot.TaxAmt ELSE ot.TaxBaseAmt END AS PriceEntered,
  CASE WHEN ot.IsTaxIncluded='Y' THEN 0 ELSE ot.TaxAmt END AS LineNetAmt,
  NULL AS ProductDescription, NULL AS ImageURL,
  nullif(1,1) AS C_Campaign_ID,
  NULLIF(1,1) AS C_Project_ID,
  NULLIF(1,1) AS C_Activity_ID, 
  NULLIF(1,1) AS C_ProjectPhase_ID,
  NULLIF(1,1) AS C_ProjectTask_ID 
FROM  C_OrderTax ot  
  INNER JOIN C_Tax t ON (ot.C_Tax_ID=t.C_Tax_ID)
/
