CREATE OR REPLACE VIEW C_INVOICE_LINETAX_V
AS 
SELECT il.AD_Client_ID AS AD_Client_ID, il.AD_Org_ID AS AD_Org_ID, il.IsActive AS IsActive,
    il.Created AS Created, il.CreatedBy AS CreatedBy, il.Updated AS Updated, il.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    il.C_Invoice_ID AS C_Invoice_ID, il.C_InvoiceLine_ID AS C_InvoiceLine_ID,
    il.C_Tax_ID AS C_Tax_ID, il.TaxAmt AS TaxAmt, il.LineTotalAmt AS LineTotalAmt, t.TaxIndicator AS TaxIndicator,
    il.Line AS Line,  p.M_Product_ID AS M_Product_ID,
    CASE WHEN il.QtyInvoiced<>0 OR il.M_Product_ID IS NOT NULL THEN il.QtyInvoiced END AS QtyInvoiced,
    CASE WHEN il.QtyEntered<>0 OR il.M_Product_ID IS NOT NULL THEN il.QtyEntered END AS QtyEntered,
    CASE WHEN il.QtyEntered<>0 OR il.M_Product_ID IS NOT NULL THEN uom.UOMSymbol END AS UOMSymbol,
    COALESCE(c.Name,p.Name||COALESCE(productAttribute(il.M_AttributeSetInstance_ID),N''),il.Description) AS Name,
    CASE WHEN COALESCE(c.Name,p.Name) IS NOT NULL THEN il.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 il.PriceList<>0 THEN il.PriceList END AS PriceList,
    CASE WHEN i.IsDiscountPrinted='Y' AND il.PriceList<>0 AND il.QtyEntered<>0 
              THEN il.PriceList*il.QtyInvoiced/il.QtyEntered END AS PriceEnteredList,
    CASE WHEN i.IsDiscountPrinted='Y' AND il.PriceList>il.PriceActual AND il.PriceList<>0 
              THEN (il.PriceList-il.PriceActual)/il.PriceList*100 END AS Discount,
    CASE WHEN il.PriceActual<>0 OR il.M_Product_ID IS NOT NULL THEN il.PriceActual END AS PriceActual,
    CASE WHEN il.PriceEntered<>0 OR il.M_Product_ID IS NOT NULL THEN il.PriceEntered END AS PriceEntered,
    CASE WHEN il.LineNetAmt<>0 OR il.M_Product_ID IS NOT NULL THEN il.LineNetAmt END AS LineNetAmt,
    il.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,
    il.C_Campaign_ID AS C_Campaign_ID,
    il.C_Project_ID AS C_Project_ID,
    il.C_Activity_ID AS C_Activity_ID,
    il.C_ProjectPhase_ID AS C_ProjectPhase_ID,
    il.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  C_InvoiceLine il
    INNER JOIN C_UOM uom ON (il.C_UOM_ID=uom.C_UOM_ID)
    INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID)
    LEFT OUTER JOIN C_Tax t ON (il.C_Tax_ID=t.C_Tax_ID)
    LEFT OUTER JOIN M_Product p ON (il.M_Product_ID=p.M_Product_ID)
    LEFT OUTER JOIN C_Charge c ON (il.C_Charge_ID=c.C_Charge_ID)
    LEFT OUTER JOIN C_BPartner_Product pp ON (il.M_Product_ID=pp.M_Product_ID AND i.C_BPartner_ID=pp.C_BPartner_ID)
    LEFT OUTER JOIN S_ResourceAssignment ra ON (il.S_ResourceAssignment_ID=ra.S_ResourceAssignment_ID)
    LEFT OUTER JOIN M_AttributeSetInstance asi ON (il.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID)  
UNION 
SELECT il.AD_Client_ID AS AD_Client_ID, il.AD_Org_ID AS AD_Org_ID, il.IsActive AS IsActive, 
    il.Created AS Created, il.CreatedBy AS CreatedBy, il.Updated AS Updated, il.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    il.C_Invoice_ID AS C_Invoice_ID, il.C_InvoiceLine_ID AS C_InvoiceLine_ID, 
    il.C_Tax_ID AS C_Tax_ID, il.TaxAmt AS TaxAmt, il.LineTotalAmt AS LineTotalAmt, t.TaxIndicator AS TaxIndicator,
    il.Line+(b.Line/100) AS Line, p.M_Product_ID AS M_Product_ID,  il.QtyInvoiced*b.BOMQty AS QtyInvoiced, 
    il.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,
    il.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,
    il.C_Campaign_ID AS C_Campaign_ID, il.C_Project_ID AS C_Project_ID,
    il.C_Activity_ID AS C_Activity_ID, il.C_ProjectPhase_ID AS C_ProjectPhase_ID,
    il.C_ProjectTask_ID AS C_ProjectTask_ID 
FROM  M_Product_BOM b
    INNER JOIN C_InvoiceLine il ON (b.M_Product_ID=il.M_Product_ID)
    INNER JOIN M_Product bp ON (bp.M_Product_ID=il.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)
    LEFT OUTER JOIN C_Tax t ON (il.C_Tax_ID=t.C_Tax_ID)
    LEFT OUTER JOIN M_AttributeSetInstance asi ON (il.M_AttributeSetInstance_ID=asi.M_AttributeSetInstance_ID)
UNION 
SELECT il.AD_Client_ID AS AD_Client_ID, il.AD_Org_ID AS AD_Org_ID, il.IsActive AS IsActive,
    il.Created AS Created, il.CreatedBy AS CreatedBy, il.Updated AS Updated, il.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    il.C_Invoice_ID AS C_Invoice_ID, il.C_InvoiceLine_ID AS C_InvoiceLine_ID,
    nullif(1,1) AS C_Tax_ID, NULLIF(1,1) AS TaxAmt, NULLIF(1,1) AS LineTotalAmt, NULL AS TaxIndicator,
    il.Line AS Line, NULLIF(1,1) AS M_Product_ID,  nullif(1,1) AS QtyInvoiced,   nullif(1,1) AS QtyEntered,
    NULL AS UOMSymbol,  NULL AS Name,
    il.Description 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,
    nullif(1,1) AS M_AttributeSetInstance_ID,
    NULLIF(1,1) AS M_AttributeSet_ID,
    NULL AS SerNo, NULL AS Lot, NULLIF(1,1) AS M_Lot_ID, NULL AS GuaranteeDate,
    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_InvoiceLine il 
WHERE il.C_UOM_ID IS NULL  
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_Invoice_ID AS C_Invoice_ID, NULLIF(1,1) AS C_InvoiceLine_ID,
    nullif(1,1) AS C_Tax_ID, NULLIF(1,1) AS TaxAmt, NULLIF(1,1) AS LineTotalAmt, NULL AS TaxIndicator,
    999998 AS Line, NULLIF(1,1) AS M_Product_ID,  nullif(1,1) AS QtyInvoiced, 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,
    nullif(1,1) AS M_AttributeSetInstance_ID,
    NULLIF(1,1) AS M_AttributeSet_ID,
    NULL AS SerNo, NULL AS Lot, NULLIF(1,1) AS M_Lot_ID, NULL AS GuaranteeDate,
    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_Invoice  
UNION 
SELECT it.AD_Client_ID AS AD_Client_ID, it.AD_Org_ID AS AD_Org_ID, it.IsActive AS IsActive,
    it.Created AS Created, it.CreatedBy AS CreatedBy, it.Updated AS Updated, it.UpdatedBy AS UpdatedBy,
    CAST('en_US' AS VARCHAR(5)) AS AD_Language,
    it.C_Invoice_ID AS C_Invoice_ID, NULLIF(1,1) AS C_InvoiceLine_ID,
    it.C_Tax_ID AS C_Tax_ID, NULLIF(1,1) AS TaxAmt, NULLIF(1,1) AS LineTotalAmt, t.TaxIndicator AS TaxIndicator,
    999999 AS Line, NULLIF(1,1) AS M_Product_ID,
    nullif(1,1) AS QtyInvoiced, 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 it.IsTaxIncluded='Y' THEN it.TaxAmt ELSE it.TaxBaseAmt END AS PriceActual,
    CASE WHEN it.IsTaxIncluded='Y' THEN it.TaxAmt ELSE it.TaxBaseAmt END AS PriceEntered,
    CASE WHEN it.IsTaxIncluded='Y' THEN 0 ELSE it.TaxAmt END AS LineNetAmt,
    nullif(1,1) AS M_AttributeSetInstance_ID, NULLIF(1,1) AS M_AttributeSet_ID,
    NULL AS SerNo, NULL AS Lot, NULLIF(1,1) AS M_Lot_ID, NULL AS GuaranteeDate,
    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_InvoiceTax it
    INNER JOIN C_Tax t ON (it.C_Tax_ID=t.C_Tax_ID)
/
