DROP VIEW c_order_linetax_v;

CREATE OR REPLACE VIEW c_order_linetax_v AS 
    (
        (   SELECT ol.ad_client_id,
                ol.ad_org_id,
                ol.isactive,
                ol.created,
                ol.createdby,
                ol.updated,
                ol.updatedby,
                'en_US'                               AS ad_language,
                ol.c_order_id,
                ol.c_orderline_id,
                ol.c_tax_id,
                t.taxindicator,
                ol.c_bpartner_id,
                ol.c_bpartner_location_id,
                bp.name                               AS bpname,
                bpl.c_location_id,
                ol.line,
                p.m_product_id,
                po.vendorproductno,
                CASE WHEN ol.qtyordered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.qtyordered ELSE NULL END                               AS qtyordered,
                CASE WHEN ol.qtyentered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.qtyentered ELSE NULL END                               AS qtyentered,
                CASE WHEN ol.qtyentered <> 0 OR ol.m_product_id IS NOT NULL THEN uom.uomsymbol ELSE NULL END                               AS uomsymbol,
                COALESCE(c.name, (p.name || productattribute(ol.m_attributesetinstance_id)), ol.description)  AS name,
                CASE WHEN COALESCE(c.name, p.name) IS NOT NULL THEN ol.description ELSE NULL END                               AS description,
                p.documentnote,
                p.upc,
                p.sku,
                COALESCE(pp.vendorproductno, p.value) AS productvalue,
                ra.description                        AS resourcedescription,
                CASE WHEN i.isdiscountprinted = 'Y' AND ol.pricelist <> 0 THEN ol.pricelist ELSE NULL END                               AS pricelist,
                CASE WHEN i.isdiscountprinted = 'Y' AND ol.pricelist <> 0 AND ol.qtyentered <> 0 THEN ol.pricelist * ol.qtyordered / ol.qtyentered ELSE NULL 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 ELSE NULL END                               
                AS discount,
                CASE WHEN ol.priceactual <> 0 OR ol.m_product_id IS NOT NULL THEN ol.priceactual ELSE NULL END                               AS priceactual,
                CASE WHEN ol.priceentered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.priceentered ELSE NULL END                               AS priceentered,
                CASE WHEN ol.linenetamt <> 0 OR ol.m_product_id IS NOT NULL THEN ol.linenetamt ELSE NULL END                               AS linenetamt,
                p.description                         AS productdescription,
                p.imageurl,
                ol.c_campaign_id,
                ol.c_project_id,
                ol.c_activity_id,
                ol.c_projectphase_id,
                ol.c_projecttask_id,
                ol.ad_orgtrx_id                       AS c_orderline_ad_orgtrx_id,
                ol.c_charge_id                        AS c_orderline_c_charge_id,
                ol.c_currency_id                      AS c_orderline_c_currency_id,
                ol.c_uom_id,
                ol.datedelivered,
                ol.dateinvoiced,
                ol.dateordered,
                ol.datepromised                       AS c_orderline_datepromised,
                ol.freightamt                         AS c_orderline_freightamt,
                ol.isdescription,
                ol.link_orderline_id,
                ol.m_promotion_id,
                ol.m_shipper_id                       AS c_orderline_m_shipper_id,
                ol.m_warehouse_id                     AS c_orderline_m_warehouse_id,
                ol.pricecost,
                ol.pricelimit,
                ol.processed                          AS c_orderline_processed,
                ol.qtydelivered,
                ol.qtyinvoiced,
                ol.qtylostsales,
                ol.qtyreserved,
                ol.ref_orderline_id,
                ol.rramt,
                ol.rrstartdate,
                ol.s_resourceassignment_id,
                ol.user1_id                           AS c_orderline_user1_id,
                ol.user2_id                           AS c_orderline_user2_id,
                uom.ad_org_id                         AS c_uom_ad_org_id,
                uom.costingprecision,
                uom.description                       AS c_uom_description,
                uom.isactive                          AS c_uom_isactive,
                uom.isdefault,
                uom.name                              AS c_uom_name,
                uom.stdprecision,
                uom.uomtype,
                uom.x12de355,
                i.ad_org_id                           AS c_order_ad_org_id,
                i.ad_orgtrx_id                        AS c_order_ad_orgtrx_id,
                i.ad_user_id,
                i.amountrefunded,
                i.amounttendered,
                i.bill_bpartner_id,
                i.bill_location_id,
                i.bill_user_id,
                i.c_activity_id                       AS c_order_c_activity_id,
                i.c_bpartner_id                       AS c_order_c_bpartner_id,
                i.c_campaign_id                       AS c_order_c_campaign_id,
                i.c_cashline_id,
                i.c_cashplanline_id,
                i.c_charge_id                         AS c_order_c_charge_id,
                i.c_conversiontype_id,
                i.c_currency_id                       AS c_order_c_currency_id,
                i.c_doctype_id,
                i.c_doctypetarget_id,
                i.chargeamt,
                i.copyfrom,
                i.c_payment_id,
                i.c_paymentterm_id,
                i.c_pos_id,
                i.c_project_id                        AS c_order_c_project_id,
                i.created                             AS c_order_created,
                i.createdby                           AS c_order_createdby,
                i.dateacct,
                i.dateprinted,
                i.datepromised                        AS c_order_datepromised,
                i.deliveryrule,
                i.deliveryviarule,
                i.description                         AS c_order_description,
                i.docaction,
                i.docstatus,
                i.documentno,
                i.dropship_bpartner_id,
                i.dropship_location_id,
                i.dropship_user_id,
                i.freightamt                          AS c_order_freightamt,
                i.freightcostrule,
                i.grandtotal,
                i.invoicerule,
                i.isactive                            AS c_order_isactive,
                i.isapproved,
                i.iscreditapproved,
                i.isdelivered,
                i.isdiscountprinted                   AS c_order_isdiscountprinted,
                i.isdropship                          AS c_order_isdropship,
                i.isinvoiced,
                i.ispayschedulevalid,
                i.isprinted,
                i.isselected,
                i.isselfservice                       AS c_order_isselfservice,
                i.issotrx,
                i.istaxincluded,
                i.istransferred,
                i.link_order_id,
                i.m_freightcategory_id                AS c_order_m_freightcategory_id,
                i.m_pricelist_id                      AS c_order_m_pricelist_id,
                i.m_shipper_id                        AS c_order_m_shipper_id,
                i.m_warehouse_id                      AS c_order_m_warehouse_id,
                i.ordertype,
                i.pay_bpartner_id,
                i.pay_location_id,
                i.paymentrule                         AS c_order_paymentrule,
                i.poreference,
                i.posted,
                i.priorityrule,
                i.processed                           AS c_order_processed,
                i.processedon,
                i.promotioncode,
                i.ref_order_id,
                i.salesrep_id,
                i.sendemail                           AS c_order_sendemail,
                i.totallines,
                i.updated                             AS c_order_updated,
                i.updatedby                           AS c_order_updatedby,
                i.user1_id                            AS c_order_user1_id,
                i.user2_id                            AS c_order_user2_id,
                i.volume                              AS c_order_volume,
                i.weight                              AS c_order_weight,
                p.ad_org_id                           AS m_product_ad_org_id,
                p.classification,
                p.copyfrom                            AS m_product_copyfrom,
                p.created                             AS m_product_created,
                p.createdby                           AS m_product_createdby,
                p.c_revenuerecognition_id,
                p.c_subscriptiontype_id,
                p.c_taxcategory_id,
                p.c_uom_id                            AS m_product_c_uom_id,
                p.descriptionurl,
                p.discontinued                        AS m_product_discontinued,
                p.discontinuedat                      AS m_product_discontinuedat,
                p.group1,
                p.group2,
                p.guaranteedays,
                p.guaranteedaysmin,
                p.help,
                p.isactive                            AS m_product_isactive,
                p.isbom,
                p.isdropship                          AS m_product_isdropship,
                p.isexcludeautodelivery,
                p.isinvoiceprintdetails,
                p.ispicklistprintdetails,
                p.ispurchased,
                p.isselfservice                       AS m_product_isselfservice,
                p.issold,
                p.isstocked,
                p.issummary                           AS m_product_issummary,
                p.isverified,
                p.iswebstorefeatured,
                p.lowlevel,
                p.m_attributeset_id,
                p.m_attributesetinstance_id,
                p.m_freightcategory_id                AS m_product_m_freightcategory_id,
                p.m_locator_id,
                p.m_product_category_id               AS m_product_product_category_id,
                p.processing                          AS m_product_processing,
                p.producttype,
                p.r_mailtext_id,
                p.salesrep_id                         AS m_product_salesrep_id,
                p.s_expensetype_id,
                p.shelfdepth,
                p.shelfheight,
                p.shelfwidth,
                p.s_resource_id,
                p.unitsperpack,
                p.unitsperpallet,
                p.updated                             AS m_product_updated,
                p.updatedby                           AS m_product_updatedby,
                p.versionno,
                p.volume                              AS m_product_volume,
                p.weight                              AS m_product_weight,
                po.ad_org_id                          AS m_product_po_ad_org_id,
                po.c_bpartner_id                      AS m_product_po_c_bpartner_id,
                po.c_currency_id                      AS m_product_po_c_currency_id,
                po.costperorder,
                po.created                            AS m_product_po_created,
                po.createdby                          AS m_product_po_createdby,
                po.c_uom_id                           AS m_product_po_c_uom_id,
                po.deliverytime_actual,
                po.deliverytime_promised,
                po.discontinued                       AS m_product_po_discontinued,
                po.discontinuedat                     AS m_product_po_discontinuedat,
                po.isactive                           AS m_product_po_isactive,
                po.iscurrentvendor,
                po.manufacturer                       AS m_product_po_manufacturer,
                po.m_product_id                       AS m_product_po_m_product_id,
                po.order_min,
                po.order_pack,
                po.priceeffective,
                po.pricelastinv,
                po.pricelastpo,
                po.pricelist                          AS m_product_po_pricelist,
                po.pricepo,
                po.qualityrating                      AS m_product_po_qualityrating,
                po.royaltyamt,
                po.upc                                AS m_product_po_upc,
                po.updated                            AS m_product_po_updated,
                po.updatedby                          AS m_product_po_updatedby,
                po.vendorcategory                     AS m_product_po_vendorcategory,
                ra.ad_org_id                          AS s_resourceassignment_ad_org_id,
                ra.assigndatefrom,
                ra.assigndateto,
                ra.created                            AS s_resourceassignment_created,
                ra.createdby                          AS s_resourceassignment_createby,
                ra.isactive                           AS s_resourceassignment_isactive,
                ra.isconfirmed,
                ra.name                               AS s_resourceassignment_name,
                ra.qty                                AS s_resourceassignment_qty,
                ra.s_resource_id                      AS s_resourceassignment_s_resour,
                ra.updated                            AS s_resourceassignment_updated,
                ra.updatedby                          AS s_resourceassignment_updatedby,
                c.ad_org_id                           AS c_charge_c_org_id,
                c.c_bpartner_id                       AS c_charge_c_bpartner_id,
                c.c_chargetype_id,
                c.chargeamt                           AS c_charge_chargeamt,
                c.c_taxcategory_id                    AS c_charge_c_taxcategory_id,
                c.description                         AS c_charge_description,
                c.isactive                            AS c_charge_isactive,
                c.issamecurrency,
                c.issametax,
                c.istaxincluded                       AS c_charge_istaxincluded,
                pp.ad_org_id                          AS c_bpartner_product_ad_org_id,
                pp.c_bpartner_id                      AS c_bp_product_c_bpartner_id,
                pp.created                            AS c_bpartner_product_created,
                pp.createdby                          AS c_bpartner_product_createdby,
                pp.description                        AS c_bpartner_product_description,
                pp.isactive                           AS c_bpartner_product_isactive,
                pp.ismanufacturer,
                pp.manufacturer                       AS c_bpartner_product_manufactur,
                pp.qualityrating                      AS c_bpartner_product_qualityrat,
                pp.shelflifemindays,
                pp.shelflifeminpct                    AS c_bpartner_product_shelflifem,
                pp.updated                            AS c_bpartner_product_updated,
                pp.updatedby                          AS c_bpartner_product_updatedby,
                pp.vendorcategory                     AS c_bpartner_product_vendorcate,
                bp.acqusitioncost,
                bp.actuallifetimevalue,
                bp.ad_language                        AS c_bpartner_ad_language,
                bp.ad_orgbp_id,
                bp.ad_org_id                          AS c_bpartner_ad_org_id,
                bp.bpartner_parent_id,
                bp.c_bp_group_id,
                bp.c_dunning_id,
                bp.c_greeting_id,
                bp.c_invoiceschedule_id,
                bp.c_paymentterm_id                   AS c_bpartner_c_paymentterm_id,
                bp.created                            AS c_bpartner_created,
                bp.createdby                          AS c_bpartner_createdby,
                bp.c_taxgroup_id                      AS c_bpartner_c_taxgroup_id,
                bp.deliveryrule                       AS c_bpartner_deliveryrule,
                bp.deliveryviarule                    AS c_bpartner_deliveryviarule,
                bp.description                        AS c_bpartner_description,
                bp.dunninggrace                       AS c_bpartner_dunninggrace,
                bp.duns,
                bp.firstsale                          AS c_bpartner_firstsale,
                bp.flatdiscount                       AS c_bpartner_flatdiscount,
                bp.freightcostrule                    AS c_bpartner_freightcostrule,
                bp.invoicerule                        AS c_bpartner_invoicerule,
                bp.isactive                           AS c_bpartner_isactive,
                bp.iscustomer,
                bp.isdiscountprinted                  AS c_bpartner_isdiscountprinted,
                bp.isemployee                         AS c_bpartner_isemployee,
                bp.ismanufacturer                     AS c_bpartner_ismanufacturer,
                bp.isonetime,
                bp.ispotaxexempt,
                bp.isprospect,
                bp.issalesrep,
                bp.issummary                          AS c_bpartner_issummary,
                bp.istaxexempt                        AS c_bpartner_istaxexempt,
                bp.isvendor,
                bp.logo_id,
                bp.m_discountschema_id,
                bp.m_pricelist_id                     AS c_bpartner_m_pricelist_id,
                bp.naics,
                bp.name                               AS c_bpartner_name,
                bp.name2                              AS c_bpartner_name2,
                bp.numberemployees,
                bp.paymentrule                        AS c_bpartner_paymentrule,
                bp.paymentrulepo,
                bp.po_discountschema_id,
                bp.po_paymentterm_id,
                bp.po_pricelist_id,
                bp.poreference                        AS c_bpartner_poreference,
                bp.potentiallifetimevalue,
                bp.rating                             AS c_bpartner_rating,
                bp.referenceno                        AS c_bpartner_referenceno,
                bp.salesrep_id                        AS c_bpartner_salesrep_id,
                bp.salesvolume,
                bp.sendemail                          AS c_bpartner_sendemail,
                bp.shareofcustomer,
                bp.shelflifeminpct                    AS c_bpartner_shelflifeminpct,
                bp.so_creditlimit,
                bp.socreditstatus,
                bp.so_creditused,
                bp.so_description,
                bp.taxid                              AS c_bpartner_taxid,
                bp.totalopenbalance,
                bp.updated                            AS c_bpartner_updated,
                bp.updatedby                          AS c_bpartner_updatedby,
                bp.url                                AS c_bpartner_url,
                bp.value                              AS c_bpartner_value,
                bpl.ad_org_id                         AS c_bpartner_location_ad_org_id,
                bpl.c_bpartner_id                     AS c_bpartner_location_c_bpartne,
                bpl.created                           AS c_bpartner_location_created,
                bpl.createdby                         AS c_bpartner_location_createdby,
                bpl.c_salesregion_id,
                bpl.fax                               AS c_bpartner_location_fax,
                bpl.isactive                          AS c_bpartner_location_isactive,
                bpl.isbillto,
                bpl.isdn,
                bpl.ispayfrom,
                bpl.isremitto,
                bpl.isshipto,
                bpl.name                              AS c_bpartner_location_name,
                bpl.phone                             AS c_bpartner_location_phone,
                bpl.phone2                            AS c_bpartner_location_phone2,
                bpl.updated                           AS c_bpartner_location_updated,
                bpl.updatedby                         AS c_bpartner_location_updatedby,
                t.ad_org_id                           AS c_tax_ad_org_id,
                t.ad_rule_id,
                t.c_country_id,
                t.c_region_id,
                t.c_taxcategory_id                    AS c_tax_c_taxcategory_id,
                t.description                         AS c_tax_description,
                t.isactive                            AS c_tax_isactive,
                t.isdefault                           AS c_tax_isdefault,
                t.isdocumentlevel,
                t.issalestax,
                t.issummary                           AS c_tax_issummary,
                t.istaxexempt                         AS c_tax_istaxexempt,
                t.name                                AS c_tax_name,
                t.parent_tax_id,
                t.rate,
                t.requirestaxcertificate,
                t.sopotype,
                t.to_country_id,
                t.to_region_id                        AS c_tax_to_region_id,
                t.validfrom 
            FROM c_orderline ol 
                JOIN c_uom uom 
                ON ol.c_uom_id = uom.c_uom_id 
                JOIN c_order i 
                ON ol.c_order_id = i.c_order_id 
                LEFT JOIN m_product p 
                ON ol.m_product_id = p.m_product_id 
                LEFT JOIN m_product_po po 
                ON p.m_product_id = po.m_product_id AND i.c_bpartner_id = po.c_bpartner_id 
                LEFT JOIN s_resourceassignment ra 
                ON ol.s_resourceassignment_id = ra.s_resourceassignment_id 
                LEFT JOIN c_charge c 
                ON ol.c_charge_id = c.c_charge_id 
                LEFT JOIN c_bpartner_product pp 
                ON ol.m_product_id = pp.m_product_id AND i.c_bpartner_id = pp.c_bpartner_id 
                JOIN c_bpartner bp 
                ON ol.c_bpartner_id = bp.c_bpartner_id 
                JOIN c_bpartner_location bpl 
                ON ol.c_bpartner_location_id = bpl.c_bpartner_location_id 
                LEFT JOIN c_tax t 
                ON ol.c_tax_id = t.c_tax_id 
                UNION /* BOM lines */ SELECT ol.ad_client_id,
                        ol.ad_org_id,
                        ol.isactive,
                        ol.created,
                        ol.createdby,
                        ol.updated,
                        ol.updatedby,
                        'en_US'                 AS ad_language,
                        ol.c_order_id,
                        ol.c_orderline_id,
                        ol.c_tax_id,
                        NULL                    AS taxindicator,
                        NULL                    AS c_bpartner_id,
                        NULL                    AS c_bpartner_location_id,
                        NULL                    AS bpname,
                        NULL                    AS c_location_id,
                        ol.line + bl.line / 100 AS line,
                        p.m_product_id,
                        po.vendorproductno,
                        CASE WHEN bl.isqtypercentage = 'N' THEN ol.qtyordered * bl.qtybom ELSE ol.qtyordered * (bl.qtybatch / 100) END                 AS qtyordered,
                        CASE WHEN bl.isqtypercentage = 'N' THEN ol.qtyentered * bl.qtybom ELSE ol.qtyentered * (bl.qtybatch / 100) END                 AS qtyentered,
                        uom.uomsymbol,
                        p.name,
                        bl.description,
                        p.documentnote,
                        p.upc,
                        p.sku,
                        p.value                 AS productvalue,
                        NULL                    AS resourcedescription,
                        NULL                    AS pricelist,
                        NULL                    AS priceenteredlist,
                        NULL                    AS discount,
                        NULL                    AS priceactual,
                        NULL                    AS priceentered,
                        NULL                    AS linenetamt,
                        p.description           AS productdescription,
                        p.imageurl,
                        ol.c_campaign_id,
                        ol.c_project_id,
                        ol.c_activity_id,
                        ol.c_projectphase_id,
                        ol.c_projecttask_id,
                        ol.ad_orgtrx_id         AS c_orderline_ad_orgtrx_id,
                        ol.c_charge_id          AS c_orderline_c_charge_id,
                        ol.c_currency_id        AS c_orderline_c_currency_id,
                        ol.c_uom_id,
                        ol.datedelivered,
                        ol.dateinvoiced,
                        ol.dateordered,
                        ol.datepromised         AS c_orderline_datepromised,
                        ol.freightamt           AS c_orderline_freightamt,
                        ol.isdescription,
                        ol.link_orderline_id,
                        ol.m_promotion_id,
                        ol.m_shipper_id         AS c_orderline_m_shipper_id,
                        ol.m_warehouse_id       AS c_orderline_m_warehouse_id,
                        ol.pricecost,
                        ol.pricelimit,
                        ol.processed            AS c_orderline_processed,
                        ol.qtydelivered,
                        ol.qtyinvoiced,
                        ol.qtylostsales,
                        ol.qtyreserved,
                        ol.ref_orderline_id,
                        ol.rramt,
                        ol.rrstartdate,
                        ol.s_resourceassignment_id,
                        ol.user1_id             AS c_orderline_user1_id,
                        ol.user2_id             AS c_orderline_user2_id,
                        uom.ad_org_id           AS c_uom_ad_org_id,
                        uom.costingprecision,
                        uom.description         AS c_uom_description,
                        uom.isactive            AS c_uom_isactive,
                        uom.isdefault,
                        uom.name                AS c_uom_name,
                        uom.stdprecision,
                        uom.uomtype,
                        uom.x12de355,
                        i.ad_org_id             AS c_order_ad_org_id,
                        i.ad_orgtrx_id          AS c_order_ad_orgtrx_id,
                        i.ad_user_id,
                        i.amountrefunded,
                        i.amounttendered,
                        i.bill_bpartner_id,
                        i.bill_location_id,
                        i.bill_user_id,
                        i.c_activity_id         AS c_order_c_activity_id,
                        i.c_bpartner_id         AS c_order_c_bpartner_id,
                        i.c_campaign_id         AS c_order_c_campaign_id,
                        i.c_cashline_id,
                        i.c_cashplanline_id,
                        i.c_charge_id           AS c_order_c_charge_id,
                        i.c_conversiontype_id,
                        i.c_currency_id         AS c_order_c_currency_id,
                        i.c_doctype_id,
                        i.c_doctypetarget_id,
                        i.chargeamt,
                        i.copyfrom,
                        i.c_payment_id,
                        i.c_paymentterm_id,
                        i.c_pos_id,
                        i.c_project_id          AS c_order_c_project_id,
                        i.created               AS c_order_created,
                        i.createdby             AS c_order_createdby,
                        i.dateacct,
                        i.dateprinted,
                        i.datepromised          AS c_order_datepromised,
                        i.deliveryrule,
                        i.deliveryviarule,
                        i.description           AS c_order_description,
                        i.docaction,
                        i.docstatus,
                        i.documentno,
                        i.dropship_bpartner_id,
                        i.dropship_location_id,
                        i.dropship_user_id,
                        i.freightamt            AS c_order_freightamt,
                        i.freightcostrule,
                        i.grandtotal,
                        i.invoicerule,
                        i.isactive              AS c_order_isactive,
                        i.isapproved,
                        i.iscreditapproved,
                        i.isdelivered,
                        i.isdiscountprinted     AS c_order_isdiscountprinted,
                        i.isdropship            AS c_order_isdropship,
                        i.isinvoiced,
                        i.ispayschedulevalid,
                        i.isprinted,
                        i.isselected,
                        i.isselfservice         AS c_order_isselfservice,
                        i.issotrx,
                        i.istaxincluded,
                        i.istransferred,
                        i.link_order_id,
                        i.m_freightcategory_id  AS c_order_m_freightcategory_id,
                        i.m_pricelist_id        AS c_order_m_pricelist_id,
                        i.m_shipper_id          AS c_order_m_shipper_id,
                        i.m_warehouse_id        AS c_order_m_warehouse_id,
                        i.ordertype,
                        i.pay_bpartner_id,
                        i.pay_location_id,
                        i.paymentrule           AS c_order_paymentrule,
                        i.poreference,
                        i.posted,
                        i.priorityrule,
                        i.processed             AS c_order_processed,
                        i.processedon,
                        i.promotioncode,
                        i.ref_order_id,
                        i.salesrep_id,
                        i.sendemail             AS c_order_sendemail,
                        i.totallines,
                        i.updated               AS c_order_updated,
                        i.updatedby             AS c_order_updatedby,
                        i.user1_id              AS c_order_user1_id,
                        i.user2_id              AS c_order_user2_id,
                        i.volume                AS c_order_volume,
                        i.weight                AS c_order_weight,
                        p.ad_org_id             AS m_product_ad_org_id,
                        p.classification,
                        p.copyfrom              AS m_product_copyfrom,
                        p.created               AS m_product_created,
                        p.createdby             AS m_product_createdby,
                        p.c_revenuerecognition_id,
                        p.c_subscriptiontype_id,
                        p.c_taxcategory_id,
                        p.c_uom_id              AS m_product_c_uom_id,
                        p.descriptionurl,
                        p.discontinued          AS m_product_discontinued,
                        p.discontinuedat        AS m_product_discontinuedat,
                        p.group1,
                        p.group2,
                        p.guaranteedays,
                        p.guaranteedaysmin,
                        p.help,
                        p.isactive              AS m_product_isactive,
                        p.isbom,
                        p.isdropship            AS m_product_isdropship,
                        p.isexcludeautodelivery,
                        p.isinvoiceprintdetails,
                        p.ispicklistprintdetails,
                        p.ispurchased,
                        p.isselfservice         AS m_product_isselfservice,
                        p.issold,
                        p.isstocked,
                        p.issummary             AS m_product_issummary,
                        p.isverified,
                        p.iswebstorefeatured,
                        p.lowlevel,
                        p.m_attributeset_id,
                        p.m_attributesetinstance_id,
                        p.m_freightcategory_id  AS m_product_m_freightcategory_id,
                        p.m_locator_id,
                        p.m_product_category_id AS m_product_product_category_id,
                        p.processing            AS m_product_processing,
                        p.producttype,
                        p.r_mailtext_id,
                        p.salesrep_id           AS m_product_salesrep_id,
                        p.s_expensetype_id,
                        p.shelfdepth,
                        p.shelfheight,
                        p.shelfwidth,
                        p.s_resource_id,
                        p.unitsperpack,
                        p.unitsperpallet,
                        p.updated               AS m_product_updated,
                        p.updatedby             AS m_product_updatedby,
                        p.versionno,
                        p.volume                AS m_product_volume,
                        p.weight                AS m_product_weight,
                        po.ad_org_id            AS m_product_po_ad_org_id,
                        po.c_bpartner_id        AS m_product_po_c_bpartner_id,
                        po.c_currency_id        AS m_product_po_c_currency_id,
                        po.costperorder,
                        po.created              AS m_product_po_created,
                        po.createdby            AS m_product_po_createdby,
                        po.c_uom_id             AS m_product_po_c_uom_id,
                        po.deliverytime_actual,
                        po.deliverytime_promised,
                        po.discontinued         AS m_product_po_discontinued,
                        po.discontinuedat       AS m_product_po_discontinuedat,
                        po.isactive             AS m_product_po_isactive,
                        po.iscurrentvendor,
                        po.manufacturer         AS m_product_po_manufacturer,
                        po.m_product_id         AS m_product_po_m_product_id,
                        po.order_min,
                        po.order_pack,
                        po.priceeffective,
                        po.pricelastinv,
                        po.pricelastpo,
                        po.pricelist            AS m_product_po_pricelist,
                        po.pricepo,
                        po.qualityrating        AS m_product_po_qualityrating,
                        po.royaltyamt,
                        po.upc                  AS m_product_po_upc,
                        po.updated              AS m_product_po_updated,
                        po.updatedby            AS m_product_po_updatedby,
                        po.vendorcategory       AS m_product_po_vendorcategory,
                        NULL                    AS s_resourceassignment_ad_org_id,
                        NULL                    AS assigndatefrom,
                        NULL                    AS assigndateto,
                        NULL                    AS s_resourceassignment_created,
                        NULL                    AS s_resourceassignment_createby,
                        NULL                    AS s_resourceassignment_isactive,
                        NULL                    AS isconfirmed,
                        NULL                    AS s_resourceassignment_name,
                        NULL                    AS s_resourceassignment_qty,
                        NULL                    AS s_resourceassignment_s_resour,
                        NULL                    AS s_resourceassignment_updated,
                        NULL                    AS s_resourceassignment_updatedby,
                        NULL                    AS c_charge_c_org_id,
                        NULL                    AS c_charge_c_bpartner_id,
                        NULL                    AS c_chargetype_id,
                        NULL                    AS c_charge_chargeamt,
                        NULL                    AS c_charge_c_taxcategory_id,
                        NULL                    AS c_charge_description,
                        NULL                    AS c_charge_isactive,
                        NULL                    AS issamecurrency,
                        NULL                    AS issametax,
                        NULL                    AS c_charge_istaxincluded,
                        NULL                    AS c_bpartner_product_ad_org_id,
                        NULL                    AS c_bp_product_c_bpartner_id,
                        NULL                    AS c_bpartner_product_created,
                        NULL                    AS c_bpartner_product_createdby,
                        NULL                    AS c_bpartner_product_description,
                        NULL                    AS c_bpartner_product_isactive,
                        NULL                    AS ismanufacturer,
                        NULL                    AS c_bpartner_product_manufactur,
                        NULL                    AS c_bpartner_product_qualityrat,
                        NULL                    AS shelflifemindays,
                        NULL                    AS c_bpartner_product_shelflifem,
                        NULL                    AS c_bpartner_product_updated,
                        NULL                    AS c_bpartner_product_updatedby,
                        NULL                    AS c_bpartner_product_vendorcate,
                        NULL                    AS acqusitioncost,
                        NULL                    AS actuallifetimevalue,
                        NULL                    AS c_bpartner_ad_language,
                        NULL                    AS ad_orgbp_id,
                        NULL                    AS c_bpartner_ad_org_id,
                        NULL                    AS bpartner_parent_id,
                        NULL                    AS c_bp_group_id,
                        NULL                    AS c_dunning_id,
                        NULL                    AS c_greeting_id,
                        NULL                    AS c_invoiceschedule_id,
                        NULL                    AS c_bpartner_c_paymentterm_id,
                        NULL                    AS c_bpartner_created,
                        NULL                    AS c_bpartner_createdby,
                        NULL                    AS c_bpartner_c_taxgroup_id,
                        NULL                    AS c_bpartner_deliveryrule,
                        NULL                    AS c_bpartner_deliveryviarule,
                        NULL                    AS c_bpartner_description,
                        NULL                    AS c_bpartner_dunninggrace,
                        NULL                    AS duns,
                        NULL                    AS c_bpartner_firstsale,
                        NULL                    AS c_bpartner_flatdiscount,
                        NULL                    AS c_bpartner_freightcostrule,
                        NULL                    AS c_bpartner_invoicerule,
                        NULL                    AS c_bpartner_isactive,
                        NULL                    AS iscustomer,
                        NULL                    AS c_bpartner_isdiscountprinted,
                        NULL                    AS c_bpartner_isemployee,
                        NULL                    AS c_bpartner_ismanufacturer,
                        NULL                    AS isonetime,
                        NULL                    AS ispotaxexempt,
                        NULL                    AS isprospect,
                        NULL                    AS issalesrep,
                        NULL                    AS c_bpartner_issummary,
                        NULL                    AS c_bpartner_istaxexempt,
                        NULL                    AS isvendor,
                        NULL                    AS logo_id,
                        NULL                    AS m_discountschema_id,
                        NULL                    AS c_bpartner_m_pricelist_id,
                        NULL                    AS naics,
                        NULL                    AS c_bpartner_name,
                        NULL                    AS c_bpartner_name2,
                        NULL                    AS numberemployees,
                        NULL                    AS c_bpartner_paymentrule,
                        NULL                    AS paymentrulepo,
                        NULL                    AS po_discountschema_id,
                        NULL                    AS po_paymentterm_id,
                        NULL                    AS po_pricelist_id,
                        NULL                    AS c_bpartner_poreference,
                        NULL                    AS potentiallifetimevalue,
                        NULL                    AS c_bpartner_rating,
                        NULL                    AS c_bpartner_referenceno,
                        NULL                    AS c_bpartner_salesrep_id,
                        NULL                    AS salesvolume,
                        NULL                    AS c_bpartner_sendemail,
                        NULL                    AS shareofcustomer,
                        NULL                    AS c_bpartner_shelflifeminpct,
                        NULL                    AS so_creditlimit,
                        NULL                    AS socreditstatus,
                        NULL                    AS so_creditused,
                        NULL                    AS so_description,
                        NULL                    AS c_bpartner_taxid,
                        NULL                    AS totalopenbalance,
                        NULL                    AS c_bpartner_updated,
                        NULL                    AS c_bpartner_updatedby,
                        NULL                    AS c_bpartner_url,
                        NULL                    AS c_bpartner_value,
                        NULL                    AS c_bpartner_location_ad_org_id,
                        NULL                    AS c_bpartner_location_c_bpartne,
                        NULL                    AS c_bpartner_location_created,
                        NULL                    AS c_bpartner_location_createdby,
                        NULL                    AS c_salesregion_id,
                        NULL                    AS c_bpartner_location_fax,
                        NULL                    AS c_bpartner_location_isactive,
                        NULL                    AS isbillto,
                        NULL                    AS isdn,
                        NULL                    AS ispayfrom,
                        NULL                    AS isremitto,
                        NULL                    AS isshipto,
                        NULL                    AS c_bpartner_location_name,
                        NULL                    AS c_bpartner_location_phone,
                        NULL                    AS c_bpartner_location_phone2,
                        NULL                    AS c_bpartner_location_updated,
                        NULL                    AS c_bpartner_location_updatedby,
                        NULL                    AS c_tax_ad_org_id,
                        NULL                    AS ad_rule_id,
                        NULL                    AS c_country_id,
                        NULL                    AS c_region_id,
                        NULL                    AS c_tax_c_taxcategory_id,
                        NULL                    AS c_tax_description,
                        NULL                    AS c_tax_isactive,
                        NULL                    AS c_tax_isdefault,
                        NULL                    AS isdocumentlevel,
                        NULL                    AS issalestax,
                        NULL                    AS c_tax_issummary,
                        NULL                    AS c_tax_istaxexempt,
                        NULL                    AS c_tax_name,
                        NULL                    AS parent_tax_id,
                        NULL                    AS rate,
                        NULL                    AS requirestaxcertificate,
                        NULL                    AS sopotype,
                        NULL                    AS to_country_id,
                        NULL                    AS c_tax_to_region_id,
                        NULL                    AS validfrom 
                    FROM pp_product_bom b 
                        JOIN c_orderline ol 
                        ON b.m_product_id = ol.m_product_id 
                        JOIN c_order i 
                        ON ol.c_order_id = i.c_order_id 
                        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' 
                        JOIN pp_product_bomline bl 
                        ON bl.pp_product_bom_id = b.pp_product_bom_id 
                        JOIN m_product p 
                        ON p.m_product_id = bl.m_product_id 
                        LEFT JOIN m_product_po po 
                        ON p.m_product_id = po.m_product_id AND i.c_bpartner_id = po.c_bpartner_id 
                        JOIN c_uom uom 
                        ON p.c_uom_id = uom.c_uom_id) 
    UNION   /* empty line */ SELECT i.ad_client_id,
                i.ad_org_id,
                i.isactive,
                i.created,
                i.createdby,
                i.updated,
                i.updatedby,
                'en_US'                AS ad_language,
                i.c_order_id,
                NULL                   AS c_orderline_id,
                NULL                   AS c_tax_id,
                NULL                   AS taxindicator,
                NULL                   AS c_bpartner_id,
                NULL                   AS c_bpartner_location_id,
                NULL                   AS bpname,
                NULL                   AS c_location_id,
                NULL                   AS line,
                NULL                   AS m_product_id,
                NULL                   AS vendorproductno,
                NULL                   AS qtyordered,
                NULL                   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,
                NULL                   AS pricelist,
                NULL                   AS priceenteredlist,
                NULL                   AS discount,
                NULL                   AS priceactual,
                NULL                   AS priceentered,
                NULL                   AS linenetamt,
                NULL                   AS productdescription,
                NULL                   AS imageurl,
                NULL                   AS c_campaign_id,
                NULL                   AS c_project_id,
                NULL                   AS c_activity_id,
                NULL                   AS c_projectphase_id,
                NULL                   AS c_projecttask_id,
                NULL                   AS c_orderline_ad_orgtrx_id,
                NULL                   AS c_orderline_c_charge_id,
                NULL                   AS c_orderline_c_currency_id,
                NULL                   AS c_uom_id,
                NULL                   AS datedelivered,
                NULL                   AS dateinvoiced,
                NULL                   AS dateordered,
                NULL                   AS c_orderline_datepromised,
                NULL                   AS c_orderline_freightamt,
                NULL                   AS isdescription,
                NULL                   AS link_orderline_id,
                NULL                   AS m_promotion_id,
                NULL                   AS c_orderline_m_shipper_id,
                NULL                   AS c_orderline_m_warehouse_id,
                NULL                   AS pricecost,
                NULL                   AS pricelimit,
                NULL                   AS c_orderline_processed,
                NULL                   AS qtydelivered,
                NULL                   AS qtyinvoiced,
                NULL                   AS qtylostsales,
                NULL                   AS qtyreserved,
                NULL                   AS ref_orderline_id,
                NULL                   AS rramt,
                NULL                   AS rrstartdate,
                NULL                   AS s_resourceassignment_id,
                NULL                   AS c_orderline_user1_id,
                NULL                   AS c_orderline_user2_id,
                NULL                   AS c_uom_ad_org_id,
                NULL                   AS costingprecision,
                NULL                   AS c_uom_description,
                NULL                   AS c_uom_isactive,
                NULL                   AS isdefault,
                NULL                   AS c_uom_name,
                NULL                   AS stdprecision,
                NULL                   AS uomtype,
                NULL                   AS x12de355,
                NULL                   AS c_order_ad_org_id,
                NULL                   AS c_order_ad_orgtrx_id,
                NULL                   AS ad_user_id,
                NULL                   AS amountrefunded,
                NULL                   AS amounttendered,
                NULL                   AS bill_bpartner_id,
                NULL                   AS bill_location_id,
                NULL                   AS bill_user_id,
                NULL                   AS c_order_c_activity_id,
                NULL                   AS c_order_c_bpartner_id,
                NULL                   AS c_order_c_campaign_id,
                NULL                   AS c_cashline_id,
                NULL                   AS c_cashplanline_id,
                NULL                   AS c_order_c_charge_id,
                NULL                   AS c_conversiontype_id,
                NULL                   AS c_order_c_currency_id,
                NULL                   AS c_doctype_id,
                NULL                   AS c_doctypetarget_id,
                NULL                   AS chargeamt,
                NULL                   AS copyfrom,
                NULL                   AS c_payment_id,
                NULL                   AS c_paymentterm_id,
                NULL                   AS c_pos_id,
                NULL                   AS c_order_c_project_id,
                NULL                   AS c_order_created,
                NULL                   AS c_order_createdby,
                NULL                   AS dateacct,
                NULL                   AS dateprinted,
                NULL                   AS c_order_datepromised,
                NULL                   AS deliveryrule,
                NULL                   AS deliveryviarule,
                NULL                   AS c_order_description,
                NULL                   AS docaction,
                NULL                   AS docstatus,
                NULL                   AS documentno,
                NULL                   AS dropship_bpartner_id,
                NULL                   AS dropship_location_id,
                NULL                   AS dropship_user_id,
                NULL                   AS c_order_freightamt,
                NULL                   AS freightcostrule,
                NULL                   AS grandtotal,
                NULL                   AS invoicerule,
                NULL                   AS c_order_isactive,
                NULL                   AS isapproved,
                NULL                   AS iscreditapproved,
                NULL                   AS isdelivered,
                NULL                   AS c_order_isdiscountprinted,
                NULL                   AS c_order_isdropship,
                NULL                   AS isinvoiced,
                NULL                   AS ispayschedulevalid,
                NULL                   AS isprinted,
                NULL                   AS isselected,
                NULL                   AS c_order_isselfservice,
                NULL                   AS issotrx,
                NULL                   AS istaxincluded,
                NULL                   AS istransferred,
                NULL                   AS link_order_id,
                NULL                   AS c_order_m_freightcategory_id,
                NULL                   AS c_order_m_pricelist_id,
                NULL                   AS c_order_m_shipper_id,
                NULL                   AS c_order_m_warehouse_id,
                NULL                   AS ordertype,
                NULL                   AS pay_bpartner_id,
                NULL                   AS pay_location_id,
                NULL                   AS c_order_paymentrule,
                NULL                   AS poreference,
                NULL                   AS posted,
                NULL                   AS priorityrule,
                NULL                   AS c_order_processed,
                NULL                   AS processedon,
                NULL                   AS promotioncode,
                NULL                   AS ref_order_id,
                NULL                   AS salesrep_id,
                NULL                   AS c_order_sendemail,
                NULL                   AS totallines,
                NULL                   AS c_order_updated,
                NULL                   AS c_order_updatedby,
                NULL                   AS c_order_user1_id,
                NULL                   AS c_order_user2_id,
                NULL                   AS c_order_volume,
                NULL                   AS c_order_weight,
                NULL                   AS m_product_ad_org_id,
                NULL                   AS classification,
                NULL                   AS m_product_copyfrom,
                NULL                   AS m_product_created,
                NULL                   AS m_product_createdby,
                NULL                   AS c_revenuerecognition_id,
                NULL                   AS c_subscriptiontype_id,
                NULL                   AS c_taxcategory_id,
                NULL                   AS m_product_c_uom_id,
                NULL                   AS descriptionurl,
                NULL                   AS m_product_discontinued,
                NULL                   AS m_product_discontinuedat,
                NULL                   AS group1,
                NULL                   AS group2,
                NULL                   AS guaranteedays,
                NULL                   AS guaranteedaysmin,
                NULL                   AS help,
                NULL                   AS m_product_isactive,
                NULL                   AS isbom,
                NULL                   AS m_product_isdropship,
                NULL                   AS isexcludeautodelivery,
                NULL                   AS isinvoiceprintdetails,
                NULL                   AS ispicklistprintdetails,
                NULL                   AS ispurchased,
                NULL                   AS m_product_isselfservice,
                NULL                   AS issold,
                NULL                   AS isstocked,
                NULL                   AS m_product_issummary,
                NULL                   AS isverified,
                NULL                   AS iswebstorefeatured,
                NULL                   AS lowlevel,
                NULL                   AS m_attributeset_id,
                NULL                   AS m_attributesetinstance_id,
                NULL                   AS m_product_m_freightcategory_id,
                NULL                   AS m_locator_id,
                NULL                   AS m_product_product_category_id,
                NULL                   AS m_product_processing,
                NULL                   AS producttype,
                NULL                   AS r_mailtext_id,
                NULL                   AS m_product_salesrep_id,
                NULL                   AS s_expensetype_id,
                NULL                   AS shelfdepth,
                NULL                   AS shelfheight,
                NULL                   AS shelfwidth,
                NULL                   AS s_resource_id,
                NULL                   AS unitsperpack,
                NULL                   AS unitsperpallet,
                NULL                   AS m_product_updated,
                NULL                   AS m_product_updatedby,
                NULL                   AS versionno,
                NULL                   AS m_product_volume,
                NULL                   AS m_product_weight,
                NULL                   AS m_product_po_ad_org_id,
                NULL                   AS m_product_po_c_bpartner_id,
                NULL                   AS m_product_po_c_currency_id,
                NULL                   AS costperorder,
                NULL                   AS m_product_po_created,
                NULL                   AS m_product_po_createdby,
                NULL                   AS m_product_po_c_uom_id,
                NULL                   AS deliverytime_actual,
                NULL                   AS deliverytime_promised,
                NULL                   AS m_product_po_discontinued,
                NULL                   AS m_product_po_discontinuedat,
                NULL                   AS m_product_po_isactive,
                NULL                   AS iscurrentvendor,
                NULL                   AS m_product_po_manufacturer,
                NULL                   AS m_product_po_m_product_id,
                NULL                   AS order_min,
                NULL                   AS order_pack,
                NULL                   AS priceeffective,
                NULL                   AS pricelastinv,
                NULL                   AS pricelastpo,
                NULL                   AS m_product_po_pricelist,
                NULL                   AS pricepo,
                NULL                   AS m_product_po_qualityrating,
                NULL                   AS royaltyamt,
                NULL                   AS m_product_po_upc,
                NULL                   AS m_product_po_updated,
                NULL                   AS m_product_po_updatedby,
                NULL                   AS m_product_po_vendorcategory,
                NULL                   AS s_resourceassignment_ad_org_id,
                NULL                   AS assigndatefrom,
                NULL                   AS assigndateto,
                NULL                   AS s_resourceassignment_created,
                NULL                   AS s_resourceassignment_createby,
                NULL                   AS s_resourceassignment_isactive,
                NULL                   AS isconfirmed,
                NULL                   AS s_resourceassignment_name,
                NULL                   AS s_resourceassignment_qty,
                NULL                   AS s_resourceassignment_s_resour,
                NULL                   AS s_resourceassignment_updated,
                NULL                   AS s_resourceassignment_updatedby,
                NULL                   AS c_charge_c_org_id,
                NULL                   AS c_charge_c_bpartner_id,
                NULL                   AS c_chargetype_id,
                NULL                   AS c_charge_chargeamt,
                NULL                   AS c_charge_c_taxcategory_id,
                NULL                   AS c_charge_description,
                NULL                   AS c_charge_isactive,
                NULL                   AS issamecurrency,
                NULL                   AS issametax,
                NULL                   AS c_charge_istaxincluded,
                NULL                   AS c_bpartner_product_ad_org_id,
                NULL                   AS c_bp_product_c_bpartner_id,
                NULL                   AS c_bpartner_product_created,
                NULL                   AS c_bpartner_product_createdby,
                NULL                   AS c_bpartner_product_description,
                NULL                   AS c_bpartner_product_isactive,
                NULL                   AS ismanufacturer,
                NULL                   AS c_bpartner_product_manufactur,
                NULL                   AS c_bpartner_product_qualityrat,
                NULL                   AS shelflifemindays,
                NULL                   AS c_bpartner_product_shelflifem,
                NULL                   AS c_bpartner_product_updated,
                NULL                   AS c_bpartner_product_updatedby,
                NULL                   AS c_bpartner_product_vendorcate,
                NULL                   AS acqusitioncost,
                NULL                   AS actuallifetimevalue,
                NULL                   AS c_bpartner_ad_language,
                NULL                   AS ad_orgbp_id,
                NULL                   AS c_bpartner_ad_org_id,
                NULL                   AS bpartner_parent_id,
                NULL                   AS c_bp_group_id,
                NULL                   AS c_dunning_id,
                NULL                   AS c_greeting_id,
                NULL                   AS c_invoiceschedule_id,
                NULL                   AS c_bpartner_c_paymentterm_id,
                NULL                   AS c_bpartner_created,
                NULL                   AS c_bpartner_createdby,
                NULL                   AS c_bpartner_c_taxgroup_id,
                NULL                   AS c_bpartner_deliveryrule,
                NULL                   AS c_bpartner_deliveryviarule,
                NULL                   AS c_bpartner_description,
                NULL                   AS c_bpartner_dunninggrace,
                NULL                   AS duns,
                NULL                   AS c_bpartner_firstsale,
                NULL                   AS c_bpartner_flatdiscount,
                NULL                   AS c_bpartner_freightcostrule,
                NULL                   AS c_bpartner_invoicerule,
                NULL                   AS c_bpartner_isactive,
                NULL                   AS iscustomer,
                NULL                   AS c_bpartner_isdiscountprinted,
                NULL                   AS c_bpartner_isemployee,
                NULL                   AS c_bpartner_ismanufacturer,
                NULL                   AS isonetime,
                NULL                   AS ispotaxexempt,
                NULL                   AS isprospect,
                NULL                   AS issalesrep,
                NULL                   AS c_bpartner_issummary,
                NULL                   AS c_bpartner_istaxexempt,
                NULL                   AS isvendor,
                NULL                   AS logo_id,
                NULL                   AS m_discountschema_id,
                NULL                   AS c_bpartner_m_pricelist_id,
                NULL                   AS naics,
                NULL                   AS c_bpartner_name,
                NULL                   AS c_bpartner_name2,
                NULL                   AS numberemployees,
                NULL                   AS c_bpartner_paymentrule,
                NULL                   AS paymentrulepo,
                NULL                   AS po_discountschema_id,
                NULL                   AS po_paymentterm_id,
                NULL                   AS po_pricelist_id,
                NULL                   AS c_bpartner_poreference,
                NULL                   AS potentiallifetimevalue,
                NULL                   AS c_bpartner_rating,
                NULL                   AS c_bpartner_referenceno,
                NULL                   AS c_bpartner_salesrep_id,
                NULL                   AS salesvolume,
                NULL                   AS c_bpartner_sendemail,
                NULL                   AS shareofcustomer,
                NULL                   AS c_bpartner_shelflifeminpct,
                NULL                   AS so_creditlimit,
                NULL                   AS socreditstatus,
                NULL                   AS so_creditused,
                NULL                   AS so_description,
                NULL                   AS c_bpartner_taxid,
                NULL                   AS totalopenbalance,
                NULL                   AS c_bpartner_updated,
                NULL                   AS c_bpartner_updatedby,
                NULL                   AS c_bpartner_url,
                NULL                   AS c_bpartner_value,
                NULL                   AS c_bpartner_location_ad_org_id,
                NULL                   AS c_bpartner_location_c_bpartne,
                NULL                   AS c_bpartner_location_created,
                NULL                   AS c_bpartner_location_createdby,
                NULL                   AS c_salesregion_id,
                NULL                   AS c_bpartner_location_fax,
                NULL                   AS c_bpartner_location_isactive,
                NULL                   AS isbillto,
                NULL                   AS isdn,
                NULL                   AS ispayfrom,
                NULL                   AS isremitto,
                NULL                   AS isshipto,
                NULL                   AS c_bpartner_location_name,
                NULL                   AS c_bpartner_location_phone,
                NULL                   AS c_bpartner_location_phone2,
                NULL                   AS c_bpartner_location_updated,
                NULL                   AS c_bpartner_location_updatedby,
                NULL                   AS c_tax_ad_org_id,
                NULL                   AS ad_rule_id,
                NULL                   AS c_country_id,
                NULL                   AS c_region_id,
                NULL                   AS c_tax_c_taxcategory_id,
                NULL                   AS c_tax_description,
                NULL                   AS c_tax_isactive,
                NULL                   AS c_tax_isdefault,
                NULL                   AS isdocumentlevel,
                NULL                   AS issalestax,
                NULL                   AS c_tax_issummary,
                NULL                   AS c_tax_istaxexempt,
                NULL                   AS c_tax_name,
                NULL                   AS parent_tax_id,
                NULL                   AS rate,
                NULL                   AS requirestaxcertificate,
                NULL                   AS sopotype,
                NULL                   AS to_country_id,
                NULL                   AS c_tax_to_region_id,
                NULL                   AS validfrom 
            FROM c_order i
            )
UNION 
SELECT /* tax lines */ ot.ad_client_id,
    ot.ad_org_id,
    ot.isactive,
    ot.created,
    ot.createdby,
    ot.updated,
    ot.updatedby,
    'en_US' AS ad_language,
    ot.c_order_id,
    NULL    AS c_orderline_id,
    ot.c_tax_id,
    t.taxindicator,
    NULL    AS c_bpartner_id,
    NULL    AS c_bpartner_location_id,
    NULL    AS bpname,
    NULL    AS c_location_id,
    NULL    AS line,
    NULL    AS m_product_id,
    NULL    AS vendorproductno,
    NULL    AS qtyordered,
    NULL    AS qtyentered,
    NULL    AS uomsymbol,
    t.name,
    NULL    AS description,
    NULL    AS documentnote,
    NULL    AS upc,
    NULL    AS sku,
    NULL    AS productvalue,
    NULL    AS resourcedescription,
    NULL    AS pricelist,
    NULL    AS priceenteredlist,
    NULL    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 NULL ELSE ot.taxamt END AS linenetamt,
    NULL    AS productdescription,
    NULL    AS imageurl,
    NULL    AS c_campaign_id,
    NULL    AS c_project_id,
    NULL    AS c_activity_id,
    NULL    AS c_projectphase_id,
    NULL    AS c_projecttask_id,
    NULL    AS c_orderline_ad_orgtrx_id,
    NULL    AS c_orderline_c_charge_id,
    NULL    AS c_orderline_c_currency_id,
    NULL    AS c_uom_id,
    NULL    AS datedelivered,
    NULL    AS dateinvoiced,
    NULL    AS dateordered,
    NULL    AS c_orderline_datepromised,
    NULL    AS c_orderline_freightamt,
    NULL    AS isdescription,
    NULL    AS link_orderline_id,
    NULL    AS m_promotion_id,
    NULL    AS c_orderline_m_shipper_id,
    NULL    AS c_orderline_m_warehouse_id,
    NULL    AS pricecost,
    NULL    AS pricelimit,
    NULL    AS c_orderline_processed,
    NULL    AS qtydelivered,
    NULL    AS qtyinvoiced,
    NULL    AS qtylostsales,
    NULL    AS qtyreserved,
    NULL    AS ref_orderline_id,
    NULL    AS rramt,
    NULL    AS rrstartdate,
    NULL    AS s_resourceassignment_id,
    NULL    AS c_orderline_user1_id,
    NULL    AS c_orderline_user2_id,
    NULL    AS c_uom_ad_org_id,
    NULL    AS costingprecision,
    NULL    AS c_uom_description,
    NULL    AS c_uom_isactive,
    NULL    AS isdefault,
    NULL    AS c_uom_name,
    NULL    AS stdprecision,
    NULL    AS uomtype,
    NULL    AS x12de355,
    NULL    AS c_order_ad_org_id,
    NULL    AS c_order_ad_orgtrx_id,
    NULL    AS ad_user_id,
    NULL    AS amountrefunded,
    NULL    AS amounttendered,
    NULL    AS bill_bpartner_id,
    NULL    AS bill_location_id,
    NULL    AS bill_user_id,
    NULL    AS c_order_c_activity_id,
    NULL    AS c_order_c_bpartner_id,
    NULL    AS c_order_c_campaign_id,
    NULL    AS c_cashline_id,
    NULL    AS c_cashplanline_id,
    NULL    AS c_order_c_charge_id,
    NULL    AS c_conversiontype_id,
    NULL    AS c_order_c_currency_id,
    NULL    AS c_doctype_id,
    NULL    AS c_doctypetarget_id,
    NULL    AS chargeamt,
    NULL    AS copyfrom,
    NULL    AS c_payment_id,
    NULL    AS c_paymentterm_id,
    NULL    AS c_pos_id,
    NULL    AS c_order_c_project_id,
    NULL    AS c_order_created,
    NULL    AS c_order_createdby,
    NULL    AS dateacct,
    NULL    AS dateprinted,
    NULL    AS c_order_datepromised,
    NULL    AS deliveryrule,
    NULL    AS deliveryviarule,
    NULL    AS c_order_description,
    NULL    AS docaction,
    NULL    AS docstatus,
    NULL    AS documentno,
    NULL    AS dropship_bpartner_id,
    NULL    AS dropship_location_id,
    NULL    AS dropship_user_id,
    NULL    AS c_order_freightamt,
    NULL    AS freightcostrule,
    NULL    AS grandtotal,
    NULL    AS invoicerule,
    NULL    AS c_order_isactive,
    NULL    AS isapproved,
    NULL    AS iscreditapproved,
    NULL    AS isdelivered,
    NULL    AS c_order_isdiscountprinted,
    NULL    AS c_order_isdropship,
    NULL    AS isinvoiced,
    NULL    AS ispayschedulevalid,
    NULL    AS isprinted,
    NULL    AS isselected,
    NULL    AS c_order_isselfservice,
    NULL    AS issotrx,
    NULL    AS istaxincluded,
    NULL    AS istransferred,
    NULL    AS link_order_id,
    NULL    AS c_order_m_freightcategory_id,
    NULL    AS c_order_m_pricelist_id,
    NULL    AS c_order_m_shipper_id,
    NULL    AS c_order_m_warehouse_id,
    NULL    AS ordertype,
    NULL    AS pay_bpartner_id,
    NULL    AS pay_location_id,
    NULL    AS c_order_paymentrule,
    NULL    AS poreference,
    NULL    AS posted,
    NULL    AS priorityrule,
    NULL    AS c_order_processed,
    NULL    AS processedon,
    NULL    AS promotioncode,
    NULL    AS ref_order_id,
    NULL    AS salesrep_id,
    NULL    AS c_order_sendemail,
    NULL    AS totallines,
    NULL    AS c_order_updated,
    NULL    AS c_order_updatedby,
    NULL    AS c_order_user1_id,
    NULL    AS c_order_user2_id,
    NULL    AS c_order_volume,
    NULL    AS c_order_weight,
    NULL    AS m_product_ad_org_id,
    NULL    AS classification,
    NULL    AS m_product_copyfrom,
    NULL    AS m_product_created,
    NULL    AS m_product_createdby,
    NULL    AS c_revenuerecognition_id,
    NULL    AS c_subscriptiontype_id,
    NULL    AS c_taxcategory_id,
    NULL    AS m_product_c_uom_id,
    NULL    AS descriptionurl,
    NULL    AS m_product_discontinued,
    NULL    AS m_product_discontinuedat,
    NULL    AS group1,
    NULL    AS group2,
    NULL    AS guaranteedays,
    NULL    AS guaranteedaysmin,
    NULL    AS help,
    NULL    AS m_product_isactive,
    NULL    AS isbom,
    NULL    AS m_product_isdropship,
    NULL    AS isexcludeautodelivery,
    NULL    AS isinvoiceprintdetails,
    NULL    AS ispicklistprintdetails,
    NULL    AS ispurchased,
    NULL    AS m_product_isselfservice,
    NULL    AS issold,
    NULL    AS isstocked,
    NULL    AS m_product_issummary,
    NULL    AS isverified,
    NULL    AS iswebstorefeatured,
    NULL    AS lowlevel,
    NULL    AS m_attributeset_id,
    NULL    AS m_attributesetinstance_id,
    NULL    AS m_product_m_freightcategory_id,
    NULL    AS m_locator_id,
    NULL    AS m_product_product_category_id,
    NULL    AS m_product_processing,
    NULL    AS producttype,
    NULL    AS r_mailtext_id,
    NULL    AS m_product_salesrep_id,
    NULL    AS s_expensetype_id,
    NULL    AS shelfdepth,
    NULL    AS shelfheight,
    NULL    AS shelfwidth,
    NULL    AS s_resource_id,
    NULL    AS unitsperpack,
    NULL    AS unitsperpallet,
    NULL    AS m_product_updated,
    NULL    AS m_product_updatedby,
    NULL    AS versionno,
    NULL    AS m_product_volume,
    NULL    AS m_product_weight,
    NULL    AS m_product_po_ad_org_id,
    NULL    AS m_product_po_c_bpartner_id,
    NULL    AS m_product_po_c_currency_id,
    NULL    AS costperorder,
    NULL    AS m_product_po_created,
    NULL    AS m_product_po_createdby,
    NULL    AS m_product_po_c_uom_id,
    NULL    AS deliverytime_actual,
    NULL    AS deliverytime_promised,
    NULL    AS m_product_po_discontinued,
    NULL    AS m_product_po_discontinuedat,
    NULL    AS m_product_po_isactive,
    NULL    AS iscurrentvendor,
    NULL    AS m_product_po_manufacturer,
    NULL    AS m_product_po_m_product_id,
    NULL    AS order_min,
    NULL    AS order_pack,
    NULL    AS priceeffective,
    NULL    AS pricelastinv,
    NULL    AS pricelastpo,
    NULL    AS m_product_po_pricelist,
    NULL    AS pricepo,
    NULL    AS m_product_po_qualityrating,
    NULL    AS royaltyamt,
    NULL    AS m_product_po_upc,
    NULL    AS m_product_po_updated,
    NULL    AS m_product_po_updatedby,
    NULL    AS m_product_po_vendorcategory,
    NULL    AS s_resourceassignment_ad_org_id,
    NULL    AS assigndatefrom,
    NULL    AS assigndateto,
    NULL    AS s_resourceassignment_created,
    NULL    AS s_resourceassignment_createby,
    NULL    AS s_resourceassignment_isactive,
    NULL    AS isconfirmed,
    NULL    AS s_resourceassignment_name,
    NULL    AS s_resourceassignment_qty,
    NULL    AS s_resourceassignment_s_resour,
    NULL    AS s_resourceassignment_updated,
    NULL    AS s_resourceassignment_updatedby,
    NULL    AS c_charge_c_org_id,
    NULL    AS c_charge_c_bpartner_id,
    NULL    AS c_chargetype_id,
    NULL    AS c_charge_chargeamt,
    NULL    AS c_charge_c_taxcategory_id,
    NULL    AS c_charge_description,
    NULL    AS c_charge_isactive,
    NULL    AS issamecurrency,
    NULL    AS issametax,
    NULL    AS c_charge_istaxincluded,
    NULL    AS c_bpartner_product_ad_org_id,
    NULL    AS c_bp_product_c_bpartner_id,
    NULL    AS c_bpartner_product_created,
    NULL    AS c_bpartner_product_createdby,
    NULL    AS c_bpartner_product_description,
    NULL    AS c_bpartner_product_isactive,
    NULL    AS ismanufacturer,
    NULL    AS c_bpartner_product_manufactur,
    NULL    AS c_bpartner_product_qualityrat,
    NULL    AS shelflifemindays,
    NULL    AS c_bpartner_product_shelflifem,
    NULL    AS c_bpartner_product_updated,
    NULL    AS c_bpartner_product_updatedby,
    NULL    AS c_bpartner_product_vendorcate,
    NULL    AS acqusitioncost,
    NULL    AS actuallifetimevalue,
    NULL    AS c_bpartner_ad_language,
    NULL    AS ad_orgbp_id,
    NULL    AS c_bpartner_ad_org_id,
    NULL    AS bpartner_parent_id,
    NULL    AS c_bp_group_id,
    NULL    AS c_dunning_id,
    NULL    AS c_greeting_id,
    NULL    AS c_invoiceschedule_id,
    NULL    AS c_bpartner_c_paymentterm_id,
    NULL    AS c_bpartner_created,
    NULL    AS c_bpartner_createdby,
    NULL    AS c_bpartner_c_taxgroup_id,
    NULL    AS c_bpartner_deliveryrule,
    NULL    AS c_bpartner_deliveryviarule,
    NULL    AS c_bpartner_description,
    NULL    AS c_bpartner_dunninggrace,
    NULL    AS duns,
    NULL    AS c_bpartner_firstsale,
    NULL    AS c_bpartner_flatdiscount,
    NULL    AS c_bpartner_freightcostrule,
    NULL    AS c_bpartner_invoicerule,
    NULL    AS c_bpartner_isactive,
    NULL    AS iscustomer,
    NULL    AS c_bpartner_isdiscountprinted,
    NULL    AS c_bpartner_isemployee,
    NULL    AS c_bpartner_ismanufacturer,
    NULL    AS isonetime,
    NULL    AS ispotaxexempt,
    NULL    AS isprospect,
    NULL    AS issalesrep,
    NULL    AS c_bpartner_issummary,
    NULL    AS c_bpartner_istaxexempt,
    NULL    AS isvendor,
    NULL    AS logo_id,
    NULL    AS m_discountschema_id,
    NULL    AS c_bpartner_m_pricelist_id,
    NULL    AS naics,
    NULL    AS c_bpartner_name,
    NULL    AS c_bpartner_name2,
    NULL    AS numberemployees,
    NULL    AS c_bpartner_paymentrule,
    NULL    AS paymentrulepo,
    NULL    AS po_discountschema_id,
    NULL    AS po_paymentterm_id,
    NULL    AS po_pricelist_id,
    NULL    AS c_bpartner_poreference,
    NULL    AS potentiallifetimevalue,
    NULL    AS c_bpartner_rating,
    NULL    AS c_bpartner_referenceno,
    NULL    AS c_bpartner_salesrep_id,
    NULL    AS salesvolume,
    NULL    AS c_bpartner_sendemail,
    NULL    AS shareofcustomer,
    NULL    AS c_bpartner_shelflifeminpct,
    NULL    AS so_creditlimit,
    NULL    AS socreditstatus,
    NULL    AS so_creditused,
    NULL    AS so_description,
    NULL    AS c_bpartner_taxid,
    NULL    AS totalopenbalance,
    NULL    AS c_bpartner_updated,
    NULL    AS c_bpartner_updatedby,
    NULL    AS c_bpartner_url,
    NULL    AS c_bpartner_value,
    NULL    AS c_bpartner_location_ad_org_id,
    NULL    AS c_bpartner_location_c_bpartne,
    NULL    AS c_bpartner_location_created,
    NULL    AS c_bpartner_location_createdby,
    NULL    AS c_salesregion_id,
    NULL    AS c_bpartner_location_fax,
    NULL    AS c_bpartner_location_isactive,
    NULL    AS isbillto,
    NULL    AS isdn,
    NULL    AS ispayfrom,
    NULL    AS isremitto,
    NULL    AS isshipto,
    NULL    AS c_bpartner_location_name,
    NULL    AS c_bpartner_location_phone,
    NULL    AS c_bpartner_location_phone2,
    NULL    AS c_bpartner_location_updated,
    NULL    AS c_bpartner_location_updatedby,
    NULL    AS c_tax_ad_org_id,
    NULL    AS ad_rule_id,
    NULL    AS c_country_id,
    NULL    AS c_region_id,
    NULL    AS c_tax_c_taxcategory_id,
    NULL    AS c_tax_description,
    NULL    AS c_tax_isactive,
    NULL    AS c_tax_isdefault,
    NULL    AS isdocumentlevel,
    NULL    AS issalestax,
    NULL    AS c_tax_issummary,
    NULL    AS c_tax_istaxexempt,
    NULL    AS c_tax_name,
    NULL    AS parent_tax_id,
    NULL    AS rate,
    NULL    AS requirestaxcertificate,
    NULL    AS sopotype,
    NULL    AS to_country_id,
    NULL    AS c_tax_to_region_id,
    NULL    AS validfrom 
FROM c_ordertax ot 
        JOIN c_tax t 
        ON ot.c_tax_id = t.c_tax_id
;
DROP VIEW c_order_linetax_vt;

CREATE OR REPLACE VIEW c_order_linetax_vt AS 
    (
        (   SELECT ol.ad_client_id,
                ol.ad_org_id,
                ol.isactive,
                ol.created,
                ol.createdby,
                ol.updated,
                ol.updatedby,
                uomt.ad_language,
                ol.c_order_id,
                ol.c_orderline_id,
                ol.c_tax_id,
                tt.taxindicator,
                ol.c_bpartner_id,
                ol.c_bpartner_location_id,
                bp.name                                   AS bpname,
                bpl.c_location_id,
                ol.line,
                pt.m_product_id,
                po.vendorproductno,
                CASE WHEN ol.qtyordered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.qtyordered ELSE NULL END                                   AS qtyordered,
                CASE WHEN ol.qtyentered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.qtyentered ELSE NULL END                                   AS qtyentered,
                CASE WHEN ol.qtyentered <> 0 OR ol.m_product_id IS NOT NULL THEN uomt.uomsymbol ELSE NULL END                                   AS uomsymbol,
                COALESCE(ct.name, (COALESCE(pt.name, p.name) || productattribute(ol.m_attributesetinstance_id)), ol.description)     AS name,
                CASE WHEN COALESCE(ct.name, pt.name, p.name) IS NOT NULL THEN ol.description ELSE NULL END                                   AS description,
                COALESCE(pt.documentnote, p.documentnote) AS documentnote,
                p.upc,
                p.sku,
                COALESCE(pp.vendorproductno, p.value)     AS productvalue,
                ra.description                            AS resourcedescription,
                CASE WHEN i.isdiscountprinted = 'Y' AND ol.pricelist <> 0 THEN ol.pricelist ELSE NULL END                                   AS pricelist,
                CASE WHEN i.isdiscountprinted = 'Y' AND ol.pricelist <> 0 AND ol.qtyentered <> 0 THEN ol.pricelist * ol.qtyordered / ol.qtyentered ELSE NULL 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 ELSE NULL END                                   
                AS discount,
                CASE WHEN ol.priceactual <> 0 OR ol.m_product_id IS NOT NULL THEN ol.priceactual ELSE NULL END                                   AS priceactual,
                CASE WHEN ol.priceentered <> 0 OR ol.m_product_id IS NOT NULL THEN ol.priceentered ELSE NULL END                                   AS priceentered,
                CASE WHEN ol.linenetamt <> 0 OR ol.m_product_id IS NOT NULL THEN ol.linenetamt ELSE NULL END                                   AS linenetamt,
                pt.description                            AS productdescription,
                p.imageurl,
                ol.c_campaign_id,
                ol.c_project_id,
                ol.c_activity_id,
                ol.c_projectphase_id,
                ol.c_projecttask_id,
                ol.ad_orgtrx_id                           AS c_orderline_ad_orgtrx_id,
                ol.c_charge_id                            AS c_orderline_c_charge_id,
                ol.c_currency_id                          AS c_orderline_c_currency_id,
                ol.c_uom_id,
                ol.datedelivered,
                ol.dateinvoiced,
                ol.dateordered,
                ol.datepromised                           AS c_orderline_datepromised,
                ol.freightamt                             AS c_orderline_freightamt,
                ol.isdescription,
                ol.link_orderline_id,
                ol.m_promotion_id,
                ol.m_shipper_id                           AS c_orderline_m_shipper_id,
                ol.m_warehouse_id                         AS c_orderline_m_warehouse_id,
                ol.pricecost,
                ol.pricelimit,
                ol.processed                              AS c_orderline_processed,
                ol.qtydelivered,
                ol.qtyinvoiced,
                ol.qtylostsales,
                ol.qtyreserved,
                ol.ref_orderline_id,
                ol.rramt,
                ol.rrstartdate,
                ol.s_resourceassignment_id,
                ol.user1_id                               AS c_orderline_user1_id,
                ol.user2_id                               AS c_orderline_user2_id,
                uomt.ad_org_id                            AS c_uom_ad_org_id,
                uom.costingprecision,
                uomt.description                          AS c_uom_description,
                uomt.isactive                             AS c_uom_isactive,
                uom.isdefault,
                uomt.name                                 AS c_uom_name,
                uom.stdprecision,
                uom.uomtype,
                uom.x12de355,
                i.ad_org_id                               AS c_order_ad_org_id,
                i.ad_orgtrx_id                            AS c_order_ad_orgtrx_id,
                i.ad_user_id,
                i.amountrefunded,
                i.amounttendered,
                i.bill_bpartner_id,
                i.bill_location_id,
                i.bill_user_id,
                i.c_activity_id                           AS c_order_c_activity_id,
                i.c_bpartner_id                           AS c_order_c_bpartner_id,
                i.c_campaign_id                           AS c_order_c_campaign_id,
                i.c_cashline_id,
                i.c_cashplanline_id,
                i.c_charge_id                             AS c_order_c_charge_id,
                i.c_conversiontype_id,
                i.c_currency_id                           AS c_order_c_currency_id,
                i.c_doctype_id,
                i.c_doctypetarget_id,
                i.chargeamt,
                i.copyfrom,
                i.c_payment_id,
                i.c_paymentterm_id,
                i.c_pos_id,
                i.c_project_id                            AS c_order_c_project_id,
                i.created                                 AS c_order_created,
                i.createdby                               AS c_order_createdby,
                i.dateacct,
                i.dateprinted,
                i.datepromised                            AS c_order_datepromised,
                i.deliveryrule,
                i.deliveryviarule,
                i.description                             AS c_order_description,
                i.docaction,
                i.docstatus,
                i.documentno,
                i.dropship_bpartner_id,
                i.dropship_location_id,
                i.dropship_user_id,
                i.freightamt                              AS c_order_freightamt,
                i.freightcostrule,
                i.grandtotal,
                i.invoicerule,
                i.isactive                                AS c_order_isactive,
                i.isapproved,
                i.iscreditapproved,
                i.isdelivered,
                i.isdiscountprinted                       AS c_order_isdiscountprinted,
                i.isdropship                              AS c_order_isdropship,
                i.isinvoiced,
                i.ispayschedulevalid,
                i.isprinted,
                i.isselected,
                i.isselfservice                           AS c_order_isselfservice,
                i.issotrx,
                i.istaxincluded,
                i.istransferred,
                i.link_order_id,
                i.m_freightcategory_id                    AS c_order_m_freightcategory_id,
                i.m_pricelist_id                          AS c_order_m_pricelist_id,
                i.m_shipper_id                            AS c_order_m_shipper_id,
                i.m_warehouse_id                          AS c_order_m_warehouse_id,
                i.ordertype,
                i.pay_bpartner_id,
                i.pay_location_id,
                i.paymentrule                             AS c_order_paymentrule,
                i.poreference,
                i.posted,
                i.priorityrule,
                i.processed                               AS c_order_processed,
                i.processedon,
                i.promotioncode,
                i.ref_order_id,
                i.salesrep_id,
                i.sendemail                               AS c_order_sendemail,
                i.totallines,
                i.updated                                 AS c_order_updated,
                i.updatedby                               AS c_order_updatedby,
                i.user1_id                                AS c_order_user1_id,
                i.user2_id                                AS c_order_user2_id,
                i.volume                                  AS c_order_volume,
                i.weight                                  AS c_order_weight,
                pt.ad_org_id                              AS m_product_ad_org_id,
                p.classification,
                p.copyfrom                                AS m_product_copyfrom,
                pt.created                                AS m_product_created,
                pt.createdby                              AS m_product_createdby,
                p.c_revenuerecognition_id,
                p.c_subscriptiontype_id,
                p.c_taxcategory_id,
                p.c_uom_id                                AS m_product_c_uom_id,
                p.descriptionurl,
                p.discontinued                            AS m_product_discontinued,
                p.discontinuedat                          AS m_product_discontinuedat,
                p.group1,
                p.group2,
                p.guaranteedays,
                p.guaranteedaysmin,
                p.help,
                pt.isactive                               AS m_product_isactive,
                p.isbom,
                p.isdropship                              AS m_product_isdropship,
                p.isexcludeautodelivery,
                p.isinvoiceprintdetails,
                p.ispicklistprintdetails,
                p.ispurchased,
                p.isselfservice                           AS m_product_isselfservice,
                p.issold,
                p.isstocked,
                p.issummary                               AS m_product_issummary,
                p.isverified,
                p.iswebstorefeatured,
                p.lowlevel,
                p.m_attributeset_id,
                p.m_attributesetinstance_id,
                p.m_freightcategory_id                    AS m_product_m_freightcategory_id,
                p.m_locator_id,
                p.m_product_category_id                   AS m_product_product_category_id,
                p.processing                              AS m_product_processing,
                p.producttype,
                p.r_mailtext_id,
                p.salesrep_id                             AS m_product_salesrep_id,
                p.s_expensetype_id,
                p.shelfdepth,
                p.shelfheight,
                p.shelfwidth,
                p.s_resource_id,
                p.unitsperpack,
                p.unitsperpallet,
                pt.updated                                AS m_product_updated,
                pt.updatedby                              AS m_product_updatedby,
                p.versionno,
                p.volume                                  AS m_product_volume,
                p.weight                                  AS m_product_weight,
                po.ad_org_id                              AS m_product_po_ad_org_id,
                po.c_bpartner_id                          AS m_product_po_c_bpartner_id,
                po.c_currency_id                          AS m_product_po_c_currency_id,
                po.costperorder,
                po.created                                AS m_product_po_created,
                po.createdby                              AS m_product_po_createdby,
                po.c_uom_id                               AS m_product_po_c_uom_id,
                po.deliverytime_actual,
                po.deliverytime_promised,
                po.discontinued                           AS m_product_po_discontinued,
                po.discontinuedat                         AS m_product_po_discontinuedat,
                po.isactive                               AS m_product_po_isactive,
                po.iscurrentvendor,
                po.manufacturer                           AS m_product_po_manufacturer,
                po.m_product_id                           AS m_product_po_m_product_id,
                po.order_min,
                po.order_pack,
                po.priceeffective,
                po.pricelastinv,
                po.pricelastpo,
                po.pricelist                              AS m_product_po_pricelist,
                po.pricepo,
                po.qualityrating                          AS m_product_po_qualityrating,
                po.royaltyamt,
                po.upc                                    AS m_product_po_upc,
                po.updated                                AS m_product_po_updated,
                po.updatedby                              AS m_product_po_updatedby,
                po.vendorcategory                         AS m_product_po_vendorcategory,
                ra.ad_org_id                              AS s_resourceassignment_ad_org_id,
                ra.assigndatefrom,
                ra.assigndateto,
                ra.created                                AS s_resourceassignment_created,
                ra.createdby                              AS s_resourceassignment_createby,
                ra.isactive                               AS s_resourceassignment_isactive,
                ra.isconfirmed,
                ra.name                                   AS s_resourceassignment_name,
                ra.qty                                    AS s_resourceassignment_qty,
                ra.s_resource_id                          AS s_resourceassignment_s_resour,
                ra.updated                                AS s_resourceassignment_updated,
                ra.updatedby                              AS s_resourceassignment_updatedby,
                ct.ad_org_id                              AS c_charge_c_org_id,
                c.c_bpartner_id                           AS c_charge_c_bpartner_id,
                c.c_chargetype_id,
                c.chargeamt                               AS c_charge_chargeamt,
                c.c_taxcategory_id                        AS c_charge_c_taxcategory_id,
                c.description                             AS c_charge_description,
                ct.isactive                               AS c_charge_isactive,
                c.issamecurrency,
                c.issametax,
                c.istaxincluded                           AS c_charge_istaxincluded,
                pp.ad_org_id                              AS c_bpartner_product_ad_org_id,
                pp.c_bpartner_id                          AS c_bp_product_c_bpartner_id,
                pp.created                                AS c_bpartner_product_created,
                pp.createdby                              AS c_bpartner_product_createdby,
                pp.description                            AS c_bpartner_product_descriptio,
                pp.isactive                               AS c_bpartner_product_isactive,
                pp.ismanufacturer,
                pp.manufacturer                           AS c_bpartner_product_manufactur,
                pp.qualityrating                          AS c_bpartner_product_qualityrat,
                pp.shelflifemindays,
                pp.shelflifeminpct                        AS c_bpartner_product_shelflifem,
                pp.updated                                AS c_bpartner_product_updated,
                pp.updatedby                              AS c_bpartner_product_updatedby,
                pp.vendorcategory                         AS c_bpartner_product_vendorcate,
                bp.acqusitioncost,
                bp.actuallifetimevalue,
                bp.ad_language                            AS c_bpartner_ad_language,
                bp.ad_orgbp_id,
                bp.ad_org_id                              AS c_bpartner_ad_org_id,
                bp.bpartner_parent_id,
                bp.c_bp_group_id,
                bp.c_dunning_id,
                bp.c_greeting_id,
                bp.c_invoiceschedule_id,
                bp.c_paymentterm_id                       AS c_bpartner_c_paymentterm_id,
                bp.created                                AS c_bpartner_created,
                bp.createdby                              AS c_bpartner_createdby,
                bp.c_taxgroup_id                          AS c_bpartner_c_taxgroup_id,
                bp.deliveryrule                           AS c_bpartner_deliveryrule,
                bp.deliveryviarule                        AS c_bpartner_deliveryviarule,
                bp.description                            AS c_bpartner_description,
                bp.dunninggrace                           AS c_bpartner_dunninggrace,
                bp.duns,
                bp.firstsale                              AS c_bpartner_firstsale,
                bp.flatdiscount                           AS c_bpartner_flatdiscount,
                bp.freightcostrule                        AS c_bpartner_freightcostrule,
                bp.invoicerule                            AS c_bpartner_invoicerule,
                bp.isactive                               AS c_bpartner_isactive,
                bp.iscustomer,
                bp.isdiscountprinted                      AS c_bpartner_isdiscountprinted,
                bp.isemployee                             AS c_bpartner_isemployee,
                bp.ismanufacturer                         AS c_bpartner_ismanufacturer,
                bp.isonetime,
                bp.ispotaxexempt,
                bp.isprospect,
                bp.issalesrep,
                bp.issummary                              AS c_bpartner_issummary,
                bp.istaxexempt                            AS c_bpartner_istaxexempt,
                bp.isvendor,
                bp.logo_id,
                bp.m_discountschema_id,
                bp.m_pricelist_id                         AS c_bpartner_m_pricelist_id,
                bp.naics,
                bp.name                                   AS c_bpartner_name,
                bp.name2                                  AS c_bpartner_name2,
                bp.numberemployees,
                bp.paymentrule                            AS c_bpartner_paymentrule,
                bp.paymentrulepo,
                bp.po_discountschema_id,
                bp.po_paymentterm_id,
                bp.po_pricelist_id,
                bp.poreference                            AS c_bpartner_poreference,
                bp.potentiallifetimevalue,
                bp.rating                                 AS c_bpartner_rating,
                bp.referenceno                            AS c_bpartner_referenceno,
                bp.salesrep_id                            AS c_bpartner_salesrep_id,
                bp.salesvolume,
                bp.sendemail                              AS c_bpartner_sendemail,
                bp.shareofcustomer,
                bp.shelflifeminpct                        AS c_bpartner_shelflifeminpct,
                bp.so_creditlimit,
                bp.socreditstatus,
                bp.so_creditused,
                bp.so_description,
                bp.taxid                                  AS c_bpartner_taxid,
                bp.totalopenbalance,
                bp.updated                                AS c_bpartner_updated,
                bp.updatedby                              AS c_bpartner_updatedby,
                bp.url                                    AS c_bpartner_url,
                bp.value                                  AS c_bpartner_value,
                bpl.ad_org_id                             AS c_bpartner_location_ad_org_id,
                bpl.c_bpartner_id                         AS c_bpartner_location_c_bpartne,
                bpl.created                               AS c_bpartner_location_created,
                bpl.createdby                             AS c_bpartner_location_createdby,
                bpl.c_salesregion_id,
                bpl.fax                                   AS c_bpartner_location_fax,
                bpl.isactive                              AS c_bpartner_location_isactive,
                bpl.isbillto,
                bpl.isdn,
                bpl.ispayfrom,
                bpl.isremitto,
                bpl.isshipto,
                bpl.name                                  AS c_bpartner_location_name,
                bpl.phone                                 AS c_bpartner_location_phone,
                bpl.phone2                                AS c_bpartner_location_phone2,
                bpl.updated                               AS c_bpartner_location_updated,
                bpl.updatedby                             AS c_bpartner_location_updatedby,
                tt.ad_org_id                              AS c_tax_ad_org_id,
                t.ad_rule_id,
                t.c_country_id,
                t.c_region_id,
                t.c_taxcategory_id                        AS c_tax_c_taxcategory_id,
                tt.description                            AS c_tax_description,
                tt.isactive                               AS c_tax_isactive,
                t.isdefault                               AS c_tax_isdefault,
                t.isdocumentlevel,
                t.issalestax,
                t.issummary                               AS c_tax_issummary,
                t.istaxexempt                             AS c_tax_istaxexempt,
                tt.name                                   AS c_tax_name,
                t.parent_tax_id,
                t.rate,
                t.requirestaxcertificate,
                t.sopotype,
                t.to_country_id,
                t.to_region_id                            AS c_tax_to_region_id,
                t.validfrom 
            FROM c_orderline ol 
                JOIN c_uom uom 
                ON ol.c_uom_id = uom.c_uom_id 
                JOIN c_uom_trl uomt 
                ON ol.c_uom_id = uomt.c_uom_id 
                JOIN c_order i 
                ON ol.c_order_id = i.c_order_id 
                LEFT JOIN m_product p 
                ON ol.m_product_id = p.m_product_id 
                LEFT JOIN m_product_trl pt 
                ON ol.m_product_id = pt.m_product_id AND uomt.ad_language = pt.ad_language 
                LEFT JOIN m_product_po po 
                ON p.m_product_id = po.m_product_id AND i.c_bpartner_id = po.c_bpartner_id 
                LEFT JOIN s_resourceassignment ra 
                ON ol.s_resourceassignment_id = ra.s_resourceassignment_id 
                LEFT JOIN c_charge c 
                ON ol.c_charge_id = c.c_charge_id 
                LEFT JOIN c_charge_trl ct 
                ON ol.c_charge_id = ct.c_charge_id AND uomt.ad_language = ct.ad_language 
                LEFT JOIN c_bpartner_product pp 
                ON ol.m_product_id = pp.m_product_id AND i.c_bpartner_id = pp.c_bpartner_id 
                JOIN c_bpartner bp 
                ON ol.c_bpartner_id = bp.c_bpartner_id 
                JOIN c_bpartner_location bpl 
                ON ol.c_bpartner_location_id = bpl.c_bpartner_location_id 
                LEFT JOIN c_tax t 
                ON ol.c_tax_id = t.c_tax_id 
                LEFT JOIN c_tax_trl tt 
                ON ol.c_tax_id = tt.c_tax_id AND uomt.ad_language = tt.ad_language 
                UNION /* BOM lines */ SELECT ol.ad_client_id,
                        ol.ad_org_id,
                        ol.isactive,
                        ol.created,
                        ol.createdby,
                        ol.updated,
                        ol.updatedby,
                        uomt.ad_language,
                        ol.c_order_id,
                        ol.c_orderline_id,
                        ol.c_tax_id,
                        NULL                    AS taxindicator,
                        NULL                    AS c_bpartner_id,
                        NULL                    AS c_bpartner_location_id,
                        NULL                    AS bpname,
                        NULL                    AS c_location_id,
                        ol.line + bl.line / 100 AS line,
                        pt.m_product_id,
                        po.vendorproductno,
                        CASE WHEN bl.isqtypercentage = 'N' THEN ol.qtyordered * bl.qtybom ELSE ol.qtyordered * (bl.qtybatch / 100) END                 AS qtyordered,
                        CASE WHEN bl.isqtypercentage = 'N' THEN ol.qtyentered * bl.qtybom ELSE ol.qtyentered * (bl.qtybatch / 100) END                 AS qtyentered,
                        uomt.uomsymbol,
                        pt.name,
                        bl.description,
                        pt.documentnote,
                        p.upc,
                        p.sku,
                        p.value                 AS productvalue,
                        NULL                    AS resourcedescription,
                        NULL                    AS pricelist,
                        NULL                    AS priceenteredlist,
                        NULL                    AS discount,
                        NULL                    AS priceactual,
                        NULL                    AS priceentered,
                        NULL                    AS linenetamt,
                        pt.description          AS productdescription,
                        p.imageurl,
                        ol.c_campaign_id,
                        ol.c_project_id,
                        ol.c_activity_id,
                        ol.c_projectphase_id,
                        ol.c_projecttask_id,
                        ol.ad_orgtrx_id         AS c_orderline_ad_orgtrx_id,
                        ol.c_charge_id          AS c_orderline_c_charge_id,
                        ol.c_currency_id        AS c_orderline_c_currency_id,
                        ol.c_uom_id,
                        ol.datedelivered,
                        ol.dateinvoiced,
                        ol.dateordered,
                        ol.datepromised         AS c_orderline_datepromised,
                        ol.freightamt           AS c_orderline_freightamt,
                        ol.isdescription,
                        ol.link_orderline_id,
                        ol.m_promotion_id,
                        ol.m_shipper_id         AS c_orderline_m_shipper_id,
                        ol.m_warehouse_id       AS c_orderline_m_warehouse_id,
                        ol.pricecost,
                        ol.pricelimit,
                        ol.processed            AS c_orderline_processed,
                        ol.qtydelivered,
                        ol.qtyinvoiced,
                        ol.qtylostsales,
                        ol.qtyreserved,
                        ol.ref_orderline_id,
                        ol.rramt,
                        ol.rrstartdate,
                        ol.s_resourceassignment_id,
                        ol.user1_id             AS c_orderline_user1_id,
                        ol.user2_id             AS c_orderline_user2_id,
                        uomt.ad_org_id          AS c_uom_ad_org_id,
                        uom.costingprecision,
                        uom.description         AS c_uom_description,
                        uomt.isactive           AS c_uom_isactive,
                        uom.isdefault,
                        uomt.name               AS c_uom_name,
                        uom.stdprecision,
                        uom.uomtype,
                        uom.x12de355,
                        i.ad_org_id             AS c_order_ad_org_id,
                        i.ad_orgtrx_id          AS c_order_ad_orgtrx_id,
                        i.ad_user_id,
                        i.amountrefunded,
                        i.amounttendered,
                        i.bill_bpartner_id,
                        i.bill_location_id,
                        i.bill_user_id,
                        i.c_activity_id         AS c_order_c_activity_id,
                        i.c_bpartner_id         AS c_order_c_bpartner_id,
                        i.c_campaign_id         AS c_order_c_campaign_id,
                        i.c_cashline_id,
                        i.c_cashplanline_id,
                        i.c_charge_id           AS c_order_c_charge_id,
                        i.c_conversiontype_id,
                        i.c_currency_id         AS c_order_c_currency_id,
                        i.c_doctype_id,
                        i.c_doctypetarget_id,
                        i.chargeamt,
                        i.copyfrom,
                        i.c_payment_id,
                        i.c_paymentterm_id,
                        i.c_pos_id,
                        i.c_project_id          AS c_order_c_project_id,
                        i.created               AS c_order_created,
                        i.createdby             AS c_order_createdby,
                        i.dateacct,
                        i.dateprinted,
                        i.datepromised          AS c_order_datepromised,
                        i.deliveryrule,
                        i.deliveryviarule,
                        i.description           AS c_order_description,
                        i.docaction,
                        i.docstatus,
                        i.documentno,
                        i.dropship_bpartner_id,
                        i.dropship_location_id,
                        i.dropship_user_id,
                        i.freightamt            AS c_order_freightamt,
                        i.freightcostrule,
                        i.grandtotal,
                        i.invoicerule,
                        i.isactive              AS c_order_isactive,
                        i.isapproved,
                        i.iscreditapproved,
                        i.isdelivered,
                        i.isdiscountprinted     AS c_order_isdiscountprinted,
                        i.isdropship            AS c_order_isdropship,
                        i.isinvoiced,
                        i.ispayschedulevalid,
                        i.isprinted,
                        i.isselected,
                        i.isselfservice         AS c_order_isselfservice,
                        i.issotrx,
                        i.istaxincluded,
                        i.istransferred,
                        i.link_order_id,
                        i.m_freightcategory_id  AS c_order_m_freightcategory_id,
                        i.m_pricelist_id        AS c_order_m_pricelist_id,
                        i.m_shipper_id          AS c_order_m_shipper_id,
                        i.m_warehouse_id        AS c_order_m_warehouse_id,
                        i.ordertype,
                        i.pay_bpartner_id,
                        i.pay_location_id,
                        i.paymentrule           AS c_order_paymentrule,
                        i.poreference,
                        i.posted,
                        i.priorityrule,
                        i.processed             AS c_order_processed,
                        i.processedon,
                        i.promotioncode,
                        i.ref_order_id,
                        i.salesrep_id,
                        i.sendemail             AS c_order_sendemail,
                        i.totallines,
                        i.updated               AS c_order_updated,
                        i.updatedby             AS c_order_updatedby,
                        i.user1_id              AS c_order_user1_id,
                        i.user2_id              AS c_order_user2_id,
                        i.volume                AS c_order_volume,
                        i.weight                AS c_order_weight,
                        pt.ad_org_id            AS m_product_ad_org_id,
                        p.classification,
                        p.copyfrom              AS m_product_copyfrom,
                        pt.created              AS m_product_created,
                        pt.createdby            AS m_product_createdby,
                        p.c_revenuerecognition_id,
                        p.c_subscriptiontype_id,
                        p.c_taxcategory_id,
                        p.c_uom_id              AS m_product_c_uom_id,
                        p.descriptionurl,
                        p.discontinued          AS m_product_discontinued,
                        p.discontinuedat        AS m_product_discontinuedat,
                        p.group1,
                        p.group2,
                        p.guaranteedays,
                        p.guaranteedaysmin,
                        p.help,
                        pt.isactive             AS m_product_isactive,
                        p.isbom,
                        p.isdropship            AS m_product_isdropship,
                        p.isexcludeautodelivery,
                        p.isinvoiceprintdetails,
                        p.ispicklistprintdetails,
                        p.ispurchased,
                        p.isselfservice         AS m_product_isselfservice,
                        p.issold,
                        p.isstocked,
                        p.issummary             AS m_product_issummary,
                        p.isverified,
                        p.iswebstorefeatured,
                        p.lowlevel,
                        p.m_attributeset_id,
                        p.m_attributesetinstance_id,
                        p.m_freightcategory_id  AS m_product_m_freightcategory_id,
                        p.m_locator_id,
                        p.m_product_category_id AS m_product_product_category_id,
                        p.processing            AS m_product_processing,
                        p.producttype,
                        p.r_mailtext_id,
                        p.salesrep_id           AS m_product_salesrep_id,
                        p.s_expensetype_id,
                        p.shelfdepth,
                        p.shelfheight,
                        p.shelfwidth,
                        p.s_resource_id,
                        p.unitsperpack,
                        p.unitsperpallet,
                        p.updated               AS m_product_updated,
                        p.updatedby             AS m_product_updatedby,
                        p.versionno,
                        p.volume                AS m_product_volume,
                        p.weight                AS m_product_weight,
                        po.ad_org_id            AS m_product_po_ad_org_id,
                        po.c_bpartner_id        AS m_product_po_c_bpartner_id,
                        po.c_currency_id        AS m_product_po_c_currency_id,
                        po.costperorder,
                        po.created              AS m_product_po_created,
                        po.createdby            AS m_product_po_createdby,
                        po.c_uom_id             AS m_product_po_c_uom_id,
                        po.deliverytime_actual,
                        po.deliverytime_promised,
                        po.discontinued         AS m_product_po_discontinued,
                        po.discontinuedat       AS m_product_po_discontinuedat,
                        po.isactive             AS m_product_po_isactive,
                        po.iscurrentvendor,
                        po.manufacturer         AS m_product_po_manufacturer,
                        po.m_product_id         AS m_product_po_m_product_id,
                        po.order_min,
                        po.order_pack,
                        po.priceeffective,
                        po.pricelastinv,
                        po.pricelastpo,
                        po.pricelist            AS m_product_po_pricelist,
                        po.pricepo,
                        po.qualityrating        AS m_product_po_qualityrating,
                        po.royaltyamt,
                        po.upc                  AS m_product_po_upc,
                        po.updated              AS m_product_po_updated,
                        po.updatedby            AS m_product_po_updatedby,
                        po.vendorcategory       AS m_product_po_vendorcategory,
                        NULL                    AS s_resourceassignment_ad_org_id,
                        NULL                    AS assigndatefrom,
                        NULL                    AS assigndateto,
                        NULL                    AS s_resourceassignment_created,
                        NULL                    AS s_resourceassignment_createby,
                        NULL                    AS s_resourceassignment_isactive,
                        NULL                    AS isconfirmed,
                        NULL                    AS s_resourceassignment_name,
                        NULL                    AS s_resourceassignment_qty,
                        NULL                    AS s_resourceassignment_s_resour,
                        NULL                    AS s_resourceassignment_updated,
                        NULL                    AS s_resourceassignment_updatedby,
                        NULL                    AS c_charge_c_org_id,
                        NULL                    AS c_charge_c_bpartner_id,
                        NULL                    AS c_chargetype_id,
                        NULL                    AS c_charge_chargeamt,
                        NULL                    AS c_charge_c_taxcategory_id,
                        NULL                    AS c_charge_description,
                        NULL                    AS c_charge_isactive,
                        NULL                    AS issamecurrency,
                        NULL                    AS issametax,
                        NULL                    AS c_charge_istaxincluded,
                        NULL                    AS c_bpartner_product_ad_org_id,
                        NULL                    AS c_bp_product_c_bpartner_id,
                        NULL                    AS c_bpartner_product_created,
                        NULL                    AS c_bpartner_product_createdby,
                        NULL                    AS c_bpartner_product_descriptio,
                        NULL                    AS c_bpartner_product_isactive,
                        NULL                    AS ismanufacturer,
                        NULL                    AS c_bpartner_product_manufactur,
                        NULL                    AS c_bpartner_product_qualityrat,
                        NULL                    AS shelflifemindays,
                        NULL                    AS c_bpartner_product_shelflifem,
                        NULL                    AS c_bpartner_product_updated,
                        NULL                    AS c_bpartner_product_updatedby,
                        NULL                    AS c_bpartner_product_vendorcate,
                        NULL                    AS acqusitioncost,
                        NULL                    AS actuallifetimevalue,
                        NULL                    AS c_bpartner_ad_language,
                        NULL                    AS ad_orgbp_id,
                        NULL                    AS c_bpartner_ad_org_id,
                        NULL                    AS bpartner_parent_id,
                        NULL                    AS c_bp_group_id,
                        NULL                    AS c_dunning_id,
                        NULL                    AS c_greeting_id,
                        NULL                    AS c_invoiceschedule_id,
                        NULL                    AS c_bpartner_c_paymentterm_id,
                        NULL                    AS c_bpartner_created,
                        NULL                    AS c_bpartner_createdby,
                        NULL                    AS c_bpartner_c_taxgroup_id,
                        NULL                    AS c_bpartner_deliveryrule,
                        NULL                    AS c_bpartner_deliveryviarule,
                        NULL                    AS c_bpartner_description,
                        NULL                    AS c_bpartner_dunninggrace,
                        NULL                    AS duns,
                        NULL                    AS c_bpartner_firstsale,
                        NULL                    AS c_bpartner_flatdiscount,
                        NULL                    AS c_bpartner_freightcostrule,
                        NULL                    AS c_bpartner_invoicerule,
                        NULL                    AS c_bpartner_isactive,
                        NULL                    AS iscustomer,
                        NULL                    AS c_bpartner_isdiscountprinted,
                        NULL                    AS c_bpartner_isemployee,
                        NULL                    AS c_bpartner_ismanufacturer,
                        NULL                    AS isonetime,
                        NULL                    AS ispotaxexempt,
                        NULL                    AS isprospect,
                        NULL                    AS issalesrep,
                        NULL                    AS c_bpartner_issummary,
                        NULL                    AS c_bpartner_istaxexempt,
                        NULL                    AS isvendor,
                        NULL                    AS logo_id,
                        NULL                    AS m_discountschema_id,
                        NULL                    AS c_bpartner_m_pricelist_id,
                        NULL                    AS naics,
                        NULL                    AS c_bpartner_name,
                        NULL                    AS c_bpartner_name2,
                        NULL                    AS numberemployees,
                        NULL                    AS c_bpartner_paymentrule,
                        NULL                    AS paymentrulepo,
                        NULL                    AS po_discountschema_id,
                        NULL                    AS po_paymentterm_id,
                        NULL                    AS po_pricelist_id,
                        NULL                    AS c_bpartner_poreference,
                        NULL                    AS potentiallifetimevalue,
                        NULL                    AS c_bpartner_rating,
                        NULL                    AS c_bpartner_referenceno,
                        NULL                    AS c_bpartner_salesrep_id,
                        NULL                    AS salesvolume,
                        NULL                    AS c_bpartner_sendemail,
                        NULL                    AS shareofcustomer,
                        NULL                    AS c_bpartner_shelflifeminpct,
                        NULL                    AS so_creditlimit,
                        NULL                    AS socreditstatus,
                        NULL                    AS so_creditused,
                        NULL                    AS so_description,
                        NULL                    AS c_bpartner_taxid,
                        NULL                    AS totalopenbalance,
                        NULL                    AS c_bpartner_updated,
                        NULL                    AS c_bpartner_updatedby,
                        NULL                    AS c_bpartner_url,
                        NULL                    AS c_bpartner_value,
                        NULL                    AS c_bpartner_location_ad_org_id,
                        NULL                    AS c_bpartner_location_c_bpartne,
                        NULL                    AS c_bpartner_location_created,
                        NULL                    AS c_bpartner_location_createdby,
                        NULL                    AS c_salesregion_id,
                        NULL                    AS c_bpartner_location_fax,
                        NULL                    AS c_bpartner_location_isactive,
                        NULL                    AS isbillto,
                        NULL                    AS isdn,
                        NULL                    AS ispayfrom,
                        NULL                    AS isremitto,
                        NULL                    AS isshipto,
                        NULL                    AS c_bpartner_location_name,
                        NULL                    AS c_bpartner_location_phone,
                        NULL                    AS c_bpartner_location_phone2,
                        NULL                    AS c_bpartner_location_updated,
                        NULL                    AS c_bpartner_location_updatedby,
                        NULL                    AS c_tax_ad_org_id,
                        NULL                    AS ad_rule_id,
                        NULL                    AS c_country_id,
                        NULL                    AS c_region_id,
                        NULL                    AS c_tax_c_taxcategory_id,
                        NULL                    AS c_tax_description,
                        NULL                    AS c_tax_isactive,
                        NULL                    AS c_tax_isdefault,
                        NULL                    AS isdocumentlevel,
                        NULL                    AS issalestax,
                        NULL                    AS c_tax_issummary,
                        NULL                    AS c_tax_istaxexempt,
                        NULL                    AS c_tax_name,
                        NULL                    AS parent_tax_id,
                        NULL                    AS rate,
                        NULL                    AS requirestaxcertificate,
                        NULL                    AS sopotype,
                        NULL                    AS to_country_id,
                        NULL                    AS c_tax_to_region_id,
                        NULL                    AS validfrom 
                    FROM pp_product_bom b 
                        JOIN c_orderline ol 
                        ON b.m_product_id = ol.m_product_id 
                        JOIN c_order i 
                        ON ol.c_order_id = i.c_order_id 
                        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' 
                        JOIN pp_product_bomline bl 
                        ON bl.pp_product_bom_id = b.pp_product_bom_id 
                        JOIN m_product p 
                        ON p.m_product_id = bl.m_product_id 
                        LEFT JOIN m_product_po po 
                        ON p.m_product_id = po.m_product_id AND i.c_bpartner_id = po.c_bpartner_id 
                        JOIN c_uom uom 
                        ON ol.c_uom_id = uom.c_uom_id 
                        JOIN c_uom_trl uomt 
                        ON p.c_uom_id = uomt.c_uom_id 
                        JOIN m_product_trl pt 
                        ON pt.m_product_id = bl.m_product_id AND uomt.ad_language = pt.ad_language) 
    UNION   /* empty line */ SELECT i.ad_client_id,
                i.ad_org_id,
                i.isactive,
                i.created,
                i.createdby,
                i.updated,
                i.updatedby,
                l.ad_language,
                i.c_order_id,
                NULL                          AS c_orderline_id,
                NULL                          AS c_tax_id,
                NULL                          AS taxindicator,
                NULL                          AS c_bpartner_id,
                NULL                          AS c_bpartner_location_id,
                NULL                          AS bpname,
                NULL                          AS c_location_id,
                NULL                          AS line,
                NULL                          AS m_product_id,
                NULL                          AS vendorproductno,
                NULL                          AS qtyordered,
                NULL                          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,
                NULL                          AS pricelist,
                NULL                          AS priceenteredlist,
                NULL                          AS discount,
                NULL                          AS priceactual,
                NULL                          AS priceentered,
                NULL                          AS linenetamt,
                NULL                          AS productdescription,
                NULL                          AS imageurl,
                NULL                          AS c_campaign_id,
                NULL                          AS c_project_id,
                NULL                          AS c_activity_id,
                NULL                          AS c_projectphase_id,
                NULL                          AS c_projecttask_id,
                NULL                          AS c_orderline_ad_orgtrx_id,
                NULL                          AS c_orderline_c_charge_id,
                NULL                          AS c_orderline_c_currency_id,
                NULL                          AS c_uom_id,
                NULL                          AS datedelivered,
                NULL                          AS dateinvoiced,
                NULL                          AS dateordered,
                NULL                          AS c_orderline_datepromised,
                NULL                          AS c_orderline_freightamt,
                NULL                          AS isdescription,
                NULL                          AS link_orderline_id,
                NULL                          AS m_promotion_id,
                NULL                          AS c_orderline_m_shipper_id,
                NULL                          AS c_orderline_m_warehouse_id,
                NULL                          AS pricecost,
                NULL                          AS pricelimit,
                NULL                          AS c_orderline_processed,
                NULL                          AS qtydelivered,
                NULL                          AS qtyinvoiced,
                NULL                          AS qtylostsales,
                NULL                          AS qtyreserved,
                NULL                          AS ref_orderline_id,
                NULL                          AS rramt,
                NULL                          AS rrstartdate,
                NULL                          AS s_resourceassignment_id,
                NULL                          AS c_orderline_user1_id,
                NULL                          AS c_orderline_user2_id,
                NULL                          AS c_uom_ad_org_id,
                NULL                          AS costingprecision,
                NULL                          AS c_uom_description,
                NULL                          AS c_uom_isactive,
                NULL                          AS isdefault,
                NULL                          AS c_uom_name,
                NULL                          AS stdprecision,
                NULL                          AS uomtype,
                NULL                          AS x12de355,
                NULL                          AS c_order_ad_org_id,
                NULL                          AS c_order_ad_orgtrx_id,
                NULL                          AS ad_user_id,
                NULL                          AS amountrefunded,
                NULL                          AS amounttendered,
                NULL                          AS bill_bpartner_id,
                NULL                          AS bill_location_id,
                NULL                          AS bill_user_id,
                NULL                          AS c_order_c_activity_id,
                NULL                          AS c_order_c_bpartner_id,
                NULL                          AS c_order_c_campaign_id,
                NULL                          AS c_cashline_id,
                NULL                          AS c_cashplanline_id,
                NULL                          AS c_order_c_charge_id,
                NULL                          AS c_conversiontype_id,
                NULL                          AS c_order_c_currency_id,
                NULL                          AS c_doctype_id,
                NULL                          AS c_doctypetarget_id,
                NULL                          AS chargeamt,
                NULL                          AS copyfrom,
                NULL                          AS c_payment_id,
                NULL                          AS c_paymentterm_id,
                NULL                          AS c_pos_id,
                NULL                          AS c_order_c_project_id,
                NULL                          AS c_order_created,
                NULL                          AS c_order_createdby,
                NULL                          AS dateacct,
                NULL                          AS dateprinted,
                NULL                          AS c_order_datepromised,
                NULL                          AS deliveryrule,
                NULL                          AS deliveryviarule,
                NULL                          AS c_order_description,
                NULL                          AS docaction,
                NULL                          AS docstatus,
                NULL                          AS documentno,
                NULL                          AS dropship_bpartner_id,
                NULL                          AS dropship_location_id,
                NULL                          AS dropship_user_id,
                NULL                          AS c_order_freightamt,
                NULL                          AS freightcostrule,
                NULL                          AS grandtotal,
                NULL                          AS invoicerule,
                NULL                          AS c_order_isactive,
                NULL                          AS isapproved,
                NULL                          AS iscreditapproved,
                NULL                          AS isdelivered,
                NULL                          AS c_order_isdiscountprinted,
                NULL                          AS c_order_isdropship,
                NULL                          AS isinvoiced,
                NULL                          AS ispayschedulevalid,
                NULL                          AS isprinted,
                NULL                          AS isselected,
                NULL                          AS c_order_isselfservice,
                NULL                          AS issotrx,
                NULL                          AS istaxincluded,
                NULL                          AS istransferred,
                NULL                          AS link_order_id,
                NULL                          AS c_order_m_freightcategory_id,
                NULL                          AS c_order_m_pricelist_id,
                NULL                          AS c_order_m_shipper_id,
                NULL                          AS c_order_m_warehouse_id,
                NULL                          AS ordertype,
                NULL                          AS pay_bpartner_id,
                NULL                          AS pay_location_id,
                NULL                          AS c_order_paymentrule,
                NULL                          AS poreference,
                NULL                          AS posted,
                NULL                          AS priorityrule,
                NULL                          AS c_order_processed,
                NULL                          AS processedon,
                NULL                          AS promotioncode,
                NULL                          AS ref_order_id,
                NULL                          AS salesrep_id,
                NULL                          AS c_order_sendemail,
                NULL                          AS totallines,
                NULL                          AS c_order_updated,
                NULL                          AS c_order_updatedby,
                NULL                          AS c_order_user1_id,
                NULL                          AS c_order_user2_id,
                NULL                          AS c_order_volume,
                NULL                          AS c_order_weight,
                NULL                          AS m_product_ad_org_id,
                NULL                          AS classification,
                NULL                          AS m_product_copyfrom,
                NULL                          AS m_product_created,
                NULL                          AS m_product_createdby,
                NULL                          AS c_revenuerecognition_id,
                NULL                          AS c_subscriptiontype_id,
                NULL                          AS c_taxcategory_id,
                NULL                          AS m_product_c_uom_id,
                NULL                          AS descriptionurl,
                NULL                          AS m_product_discontinued,
                NULL                          AS m_product_discontinuedat,
                NULL                          AS group1,
                NULL                          AS group2,
                NULL                          AS guaranteedays,
                NULL                          AS guaranteedaysmin,
                NULL                          AS help,
                NULL                          AS m_product_isactive,
                NULL                          AS isbom,
                NULL                          AS m_product_isdropship,
                NULL                          AS isexcludeautodelivery,
                NULL                          AS isinvoiceprintdetails,
                NULL                          AS ispicklistprintdetails,
                NULL                          AS ispurchased,
                NULL                          AS m_product_isselfservice,
                NULL                          AS issold,
                NULL                          AS isstocked,
                NULL                          AS m_product_issummary,
                NULL                          AS isverified,
                NULL                          AS iswebstorefeatured,
                NULL                          AS lowlevel,
                NULL                          AS m_attributeset_id,
                NULL                          AS m_attributesetinstance_id,
                NULL                          AS m_product_m_freightcategory_id,
                NULL                          AS m_locator_id,
                NULL                          AS m_product_product_category_id,
                NULL                          AS m_product_processing,
                NULL                          AS producttype,
                NULL                          AS r_mailtext_id,
                NULL                          AS m_product_salesrep_id,
                NULL                          AS s_expensetype_id,
                NULL                          AS shelfdepth,
                NULL                          AS shelfheight,
                NULL                          AS shelfwidth,
                NULL                          AS s_resource_id,
                NULL                          AS unitsperpack,
                NULL                          AS unitsperpallet,
                NULL                          AS m_product_updated,
                NULL                          AS m_product_updatedby,
                NULL                          AS versionno,
                NULL                          AS m_product_volume,
                NULL                          AS m_product_weight,
                NULL                          AS m_product_po_ad_org_id,
                NULL                          AS m_product_po_c_bpartner_id,
                NULL                          AS m_product_po_c_currency_id,
                NULL                          AS costperorder,
                NULL                          AS m_product_po_created,
                NULL                          AS m_product_po_createdby,
                NULL                          AS m_product_po_c_uom_id,
                NULL                          AS deliverytime_actual,
                NULL                          AS deliverytime_promised,
                NULL                          AS m_product_po_discontinued,
                NULL                          AS m_product_po_discontinuedat,
                NULL                          AS m_product_po_isactive,
                NULL                          AS iscurrentvendor,
                NULL                          AS m_product_po_manufacturer,
                NULL                          AS m_product_po_m_product_id,
                NULL                          AS order_min,
                NULL                          AS order_pack,
                NULL                          AS priceeffective,
                NULL                          AS pricelastinv,
                NULL                          AS pricelastpo,
                NULL                          AS m_product_po_pricelist,
                NULL                          AS pricepo,
                NULL                          AS m_product_po_qualityrating,
                NULL                          AS royaltyamt,
                NULL                          AS m_product_po_upc,
                NULL                          AS m_product_po_updated,
                NULL                          AS m_product_po_updatedby,
                NULL                          AS m_product_po_vendorcategory,
                NULL                          AS s_resourceassignment_ad_org_id,
                NULL                          AS assigndatefrom,
                NULL                          AS assigndateto,
                NULL                          AS s_resourceassignment_created,
                NULL                          AS s_resourceassignment_createby,
                NULL                          AS s_resourceassignment_isactive,
                NULL                          AS isconfirmed,
                NULL                          AS s_resourceassignment_name,
                NULL                          AS s_resourceassignment_qty,
                NULL                          AS s_resourceassignment_s_resour,
                NULL                          AS s_resourceassignment_updated,
                NULL                          AS s_resourceassignment_updatedby,
                NULL                          AS c_charge_c_org_id,
                NULL                          AS c_charge_c_bpartner_id,
                NULL                          AS c_chargetype_id,
                NULL                          AS c_charge_chargeamt,
                NULL                          AS c_charge_c_taxcategory_id,
                NULL                          AS c_charge_description,
                NULL                          AS c_charge_isactive,
                NULL                          AS issamecurrency,
                NULL                          AS issametax,
                NULL                          AS c_charge_istaxincluded,
                NULL                          AS c_bpartner_product_ad_org_id,
                NULL                          AS c_bp_product_c_bpartner_id,
                NULL                          AS c_bpartner_product_created,
                NULL                          AS c_bpartner_product_createdby,
                NULL                          AS c_bpartner_product_descriptio,
                NULL                          AS c_bpartner_product_isactive,
                NULL                          AS ismanufacturer,
                NULL                          AS c_bpartner_product_manufactur,
                NULL                          AS c_bpartner_product_qualityrat,
                NULL                          AS shelflifemindays,
                NULL                          AS c_bpartner_product_shelflifem,
                NULL                          AS c_bpartner_product_updated,
                NULL                          AS c_bpartner_product_updatedby,
                NULL                          AS c_bpartner_product_vendorcate,
                NULL                          AS acqusitioncost,
                NULL                          AS actuallifetimevalue,
                NULL                          AS c_bpartner_ad_language,
                NULL                          AS ad_orgbp_id,
                NULL                          AS c_bpartner_ad_org_id,
                NULL                          AS bpartner_parent_id,
                NULL                          AS c_bp_group_id,
                NULL                          AS c_dunning_id,
                NULL                          AS c_greeting_id,
                NULL                          AS c_invoiceschedule_id,
                NULL                          AS c_bpartner_c_paymentterm_id,
                NULL                          AS c_bpartner_created,
                NULL                          AS c_bpartner_createdby,
                NULL                          AS c_bpartner_c_taxgroup_id,
                NULL                          AS c_bpartner_deliveryrule,
                NULL                          AS c_bpartner_deliveryviarule,
                NULL                          AS c_bpartner_description,
                NULL                          AS c_bpartner_dunninggrace,
                NULL                          AS duns,
                NULL                          AS c_bpartner_firstsale,
                NULL                          AS c_bpartner_flatdiscount,
                NULL                          AS c_bpartner_freightcostrule,
                NULL                          AS c_bpartner_invoicerule,
                NULL                          AS c_bpartner_isactive,
                NULL                          AS iscustomer,
                NULL                          AS c_bpartner_isdiscountprinted,
                NULL                          AS c_bpartner_isemployee,
                NULL                          AS c_bpartner_ismanufacturer,
                NULL                          AS isonetime,
                NULL                          AS ispotaxexempt,
                NULL                          AS isprospect,
                NULL                          AS issalesrep,
                NULL                          AS c_bpartner_issummary,
                NULL                          AS c_bpartner_istaxexempt,
                NULL                          AS isvendor,
                NULL                          AS logo_id,
                NULL                          AS m_discountschema_id,
                NULL                          AS c_bpartner_m_pricelist_id,
                NULL                          AS naics,
                NULL                          AS c_bpartner_name,
                NULL                          AS c_bpartner_name2,
                NULL                          AS numberemployees,
                NULL                          AS c_bpartner_paymentrule,
                NULL                          AS paymentrulepo,
                NULL                          AS po_discountschema_id,
                NULL                          AS po_paymentterm_id,
                NULL                          AS po_pricelist_id,
                NULL                          AS c_bpartner_poreference,
                NULL                          AS potentiallifetimevalue,
                NULL                          AS c_bpartner_rating,
                NULL                          AS c_bpartner_referenceno,
                NULL                          AS c_bpartner_salesrep_id,
                NULL                          AS salesvolume,
                NULL                          AS c_bpartner_sendemail,
                NULL                          AS shareofcustomer,
                NULL                          AS c_bpartner_shelflifeminpct,
                NULL                          AS so_creditlimit,
                NULL                          AS socreditstatus,
                NULL                          AS so_creditused,
                NULL                          AS so_description,
                NULL                          AS c_bpartner_taxid,
                NULL                          AS totalopenbalance,
                NULL                          AS c_bpartner_updated,
                NULL                          AS c_bpartner_updatedby,
                NULL                          AS c_bpartner_url,
                NULL                          AS c_bpartner_value,
                NULL                          AS c_bpartner_location_ad_org_id,
                NULL                          AS c_bpartner_location_c_bpartne,
                NULL                          AS c_bpartner_location_created,
                NULL                          AS c_bpartner_location_createdby,
                NULL                          AS c_salesregion_id,
                NULL                          AS c_bpartner_location_fax,
                NULL                          AS c_bpartner_location_isactive,
                NULL                          AS isbillto,
                NULL                          AS isdn,
                NULL                          AS ispayfrom,
                NULL                          AS isremitto,
                NULL                          AS isshipto,
                NULL                          AS c_bpartner_location_name,
                NULL                          AS c_bpartner_location_phone,
                NULL                          AS c_bpartner_location_phone2,
                NULL                          AS c_bpartner_location_updated,
                NULL                          AS c_bpartner_location_updatedby,
                NULL                          AS c_tax_ad_org_id,
                NULL                          AS ad_rule_id,
                NULL                          AS c_country_id,
                NULL                          AS c_region_id,
                NULL                          AS c_tax_c_taxcategory_id,
                NULL                          AS c_tax_description,
                NULL                          AS c_tax_isactive,
                NULL                          AS c_tax_isdefault,
                NULL                          AS isdocumentlevel,
                NULL                          AS issalestax,
                NULL                          AS c_tax_issummary,
                NULL                          AS c_tax_istaxexempt,
                NULL                          AS c_tax_name,
                NULL                          AS parent_tax_id,
                NULL                          AS rate,
                NULL                          AS requirestaxcertificate,
                NULL                          AS sopotype,
                NULL                          AS to_country_id,
                NULL                          AS c_tax_to_region_id,
                NULL                          AS validfrom 
	    FROM c_order i, ad_language l
	    WHERE l.isbaselanguage='N' AND l.issystemlanguage='Y'
            )
UNION 
SELECT /* tax lines */ ot.ad_client_id,
    ot.ad_org_id,
    ot.isactive,
    ot.created,
    ot.createdby,
    ot.updated,
    ot.updatedby,
    tt.ad_language,
    ot.c_order_id,
    NULL                          AS c_orderline_id,
    ot.c_tax_id,
    tt.taxindicator,
    NULL                          AS c_bpartner_id,
    NULL                          AS c_bpartner_location_id,
    NULL                          AS bpname,
    NULL                          AS c_location_id,
    NULL                          AS line,
    NULL                          AS m_product_id,
    NULL                          AS vendorproductno,
    NULL                          AS qtyordered,
    NULL                          AS qtyentered,
    NULL                          AS uomsymbol,
    tt.name,
    NULL                          AS description,
    NULL                          AS documentnote,
    NULL                          AS upc,
    NULL                          AS sku,
    NULL                          AS productvalue,
    NULL                          AS resourcedescription,
    NULL                          AS pricelist,
    NULL                          AS priceenteredlist,
    NULL                          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 NULL ELSE ot.taxamt END                       AS linenetamt,
    NULL                          AS productdescription,
    NULL                          AS imageurl,
    NULL                          AS c_campaign_id,
    NULL                          AS c_project_id,
    NULL                          AS c_activity_id,
    NULL                          AS c_projectphase_id,
    NULL                          AS c_projecttask_id,
    NULL                          AS c_orderline_ad_orgtrx_id,
    NULL                          AS c_orderline_c_charge_id,
    NULL                          AS c_orderline_c_currency_id,
    NULL                          AS c_uom_id,
    NULL                          AS datedelivered,
    NULL                          AS dateinvoiced,
    NULL                          AS dateordered,
    NULL                          AS c_orderline_datepromised,
    NULL                          AS c_orderline_freightamt,
    NULL                          AS isdescription,
    NULL                          AS link_orderline_id,
    NULL                          AS m_promotion_id,
    NULL                          AS c_orderline_m_shipper_id,
    NULL                          AS c_orderline_m_warehouse_id,
    NULL                          AS pricecost,
    NULL                          AS pricelimit,
    NULL                          AS c_orderline_processed,
    NULL                          AS qtydelivered,
    NULL                          AS qtyinvoiced,
    NULL                          AS qtylostsales,
    NULL                          AS qtyreserved,
    NULL                          AS ref_orderline_id,
    NULL                          AS rramt,
    NULL                          AS rrstartdate,
    NULL                          AS s_resourceassignment_id,
    NULL                          AS c_orderline_user1_id,
    NULL                          AS c_orderline_user2_id,
    NULL                          AS c_uom_ad_org_id,
    NULL                          AS costingprecision,
    NULL                          AS c_uom_description,
    NULL                          AS c_uom_isactive,
    NULL                          AS isdefault,
    NULL                          AS c_uom_name,
    NULL                          AS stdprecision,
    NULL                          AS uomtype,
    NULL                          AS x12de355,
    NULL                          AS c_order_ad_org_id,
    NULL                          AS c_order_ad_orgtrx_id,
    NULL                          AS ad_user_id,
    NULL                          AS amountrefunded,
    NULL                          AS amounttendered,
    NULL                          AS bill_bpartner_id,
    NULL                          AS bill_location_id,
    NULL                          AS bill_user_id,
    NULL                          AS c_order_c_activity_id,
    NULL                          AS c_order_c_bpartner_id,
    NULL                          AS c_order_c_campaign_id,
    NULL                          AS c_cashline_id,
    NULL                          AS c_cashplanline_id,
    NULL                          AS c_order_c_charge_id,
    NULL                          AS c_conversiontype_id,
    NULL                          AS c_order_c_currency_id,
    NULL                          AS c_doctype_id,
    NULL                          AS c_doctypetarget_id,
    NULL                          AS chargeamt,
    NULL                          AS copyfrom,
    NULL                          AS c_payment_id,
    NULL                          AS c_paymentterm_id,
    NULL                          AS c_pos_id,
    NULL                          AS c_order_c_project_id,
    NULL                          AS c_order_created,
    NULL                          AS c_order_createdby,
    NULL                          AS dateacct,
    NULL                          AS dateprinted,
    NULL                          AS c_order_datepromised,
    NULL                          AS deliveryrule,
    NULL                          AS deliveryviarule,
    NULL                          AS c_order_description,
    NULL                          AS docaction,
    NULL                          AS docstatus,
    NULL                          AS documentno,
    NULL                          AS dropship_bpartner_id,
    NULL                          AS dropship_location_id,
    NULL                          AS dropship_user_id,
    NULL                          AS c_order_freightamt,
    NULL                          AS freightcostrule,
    NULL                          AS grandtotal,
    NULL                          AS invoicerule,
    NULL                          AS c_order_isactive,
    NULL                          AS isapproved,
    NULL                          AS iscreditapproved,
    NULL                          AS isdelivered,
    NULL                          AS c_order_isdiscountprinted,
    NULL                          AS c_order_isdropship,
    NULL                          AS isinvoiced,
    NULL                          AS ispayschedulevalid,
    NULL                          AS isprinted,
    NULL                          AS isselected,
    NULL                          AS c_order_isselfservice,
    NULL                          AS issotrx,
    NULL                          AS istaxincluded,
    NULL                          AS istransferred,
    NULL                          AS link_order_id,
    NULL                          AS c_order_m_freightcategory_id,
    NULL                          AS c_order_m_pricelist_id,
    NULL                          AS c_order_m_shipper_id,
    NULL                          AS c_order_m_warehouse_id,
    NULL                          AS ordertype,
    NULL                          AS pay_bpartner_id,
    NULL                          AS pay_location_id,
    NULL                          AS c_order_paymentrule,
    NULL                          AS poreference,
    NULL                          AS posted,
    NULL                          AS priorityrule,
    NULL                          AS c_order_processed,
    NULL                          AS processedon,
    NULL                          AS promotioncode,
    NULL                          AS ref_order_id,
    NULL                          AS salesrep_id,
    NULL                          AS c_order_sendemail,
    NULL                          AS totallines,
    NULL                          AS c_order_updated,
    NULL                          AS c_order_updatedby,
    NULL                          AS c_order_user1_id,
    NULL                          AS c_order_user2_id,
    NULL                          AS c_order_volume,
    NULL                          AS c_order_weight,
    NULL                          AS m_product_ad_org_id,
    NULL                          AS classification,
    NULL                          AS m_product_copyfrom,
    NULL                          AS m_product_created,
    NULL                          AS m_product_createdby,
    NULL                          AS c_revenuerecognition_id,
    NULL                          AS c_subscriptiontype_id,
    NULL                          AS c_taxcategory_id,
    NULL                          AS m_product_c_uom_id,
    NULL                          AS descriptionurl,
    NULL                          AS m_product_discontinued,
    NULL                          AS m_product_discontinuedat,
    NULL                          AS group1,
    NULL                          AS group2,
    NULL                          AS guaranteedays,
    NULL                          AS guaranteedaysmin,
    NULL                          AS help,
    NULL                          AS m_product_isactive,
    NULL                          AS isbom,
    NULL                          AS m_product_isdropship,
    NULL                          AS isexcludeautodelivery,
    NULL                          AS isinvoiceprintdetails,
    NULL                          AS ispicklistprintdetails,
    NULL                          AS ispurchased,
    NULL                          AS m_product_isselfservice,
    NULL                          AS issold,
    NULL                          AS isstocked,
    NULL                          AS m_product_issummary,
    NULL                          AS isverified,
    NULL                          AS iswebstorefeatured,
    NULL                          AS lowlevel,
    NULL                          AS m_attributeset_id,
    NULL                          AS m_attributesetinstance_id,
    NULL                          AS m_product_m_freightcategory_id,
    NULL                          AS m_locator_id,
    NULL                          AS m_product_product_category_id,
    NULL                          AS m_product_processing,
    NULL                          AS producttype,
    NULL                          AS r_mailtext_id,
    NULL                          AS m_product_salesrep_id,
    NULL                          AS s_expensetype_id,
    NULL                          AS shelfdepth,
    NULL                          AS shelfheight,
    NULL                          AS shelfwidth,
    NULL                          AS s_resource_id,
    NULL                          AS unitsperpack,
    NULL                          AS unitsperpallet,
    NULL                          AS m_product_updated,
    NULL                          AS m_product_updatedby,
    NULL                          AS versionno,
    NULL                          AS m_product_volume,
    NULL                          AS m_product_weight,
    NULL                          AS m_product_po_ad_org_id,
    NULL                          AS m_product_po_c_bpartner_id,
    NULL                          AS m_product_po_c_currency_id,
    NULL                          AS costperorder,
    NULL                          AS m_product_po_created,
    NULL                          AS m_product_po_createdby,
    NULL                          AS m_product_po_c_uom_id,
    NULL                          AS deliverytime_actual,
    NULL                          AS deliverytime_promised,
    NULL                          AS m_product_po_discontinued,
    NULL                          AS m_product_po_discontinuedat,
    NULL                          AS m_product_po_isactive,
    NULL                          AS iscurrentvendor,
    NULL                          AS m_product_po_manufacturer,
    NULL                          AS m_product_po_m_product_id,
    NULL                          AS order_min,
    NULL                          AS order_pack,
    NULL                          AS priceeffective,
    NULL                          AS pricelastinv,
    NULL                          AS pricelastpo,
    NULL                          AS m_product_po_pricelist,
    NULL                          AS pricepo,
    NULL                          AS m_product_po_qualityrating,
    NULL                          AS royaltyamt,
    NULL                          AS m_product_po_upc,
    NULL                          AS m_product_po_updated,
    NULL                          AS m_product_po_updatedby,
    NULL                          AS m_product_po_vendorcategory,
    NULL                          AS s_resourceassignment_ad_org_id,
    NULL                          AS assigndatefrom,
    NULL                          AS assigndateto,
    NULL                          AS s_resourceassignment_created,
    NULL                          AS s_resourceassignment_createby,
    NULL                          AS s_resourceassignment_isactive,
    NULL                          AS isconfirmed,
    NULL                          AS s_resourceassignment_name,
    NULL                          AS s_resourceassignment_qty,
    NULL                          AS s_resourceassignment_s_resour,
    NULL                          AS s_resourceassignment_updated,
    NULL                          AS s_resourceassignment_updatedby,
    NULL                          AS c_charge_c_org_id,
    NULL                          AS c_charge_c_bpartner_id,
    NULL                          AS c_chargetype_id,
    NULL                          AS c_charge_chargeamt,
    NULL                          AS c_charge_c_taxcategory_id,
    NULL                          AS c_charge_description,
    NULL                          AS c_charge_isactive,
    NULL                          AS issamecurrency,
    NULL                          AS issametax,
    NULL                          AS c_charge_istaxincluded,
    NULL                          AS c_bpartner_product_ad_org_id,
    NULL                          AS c_bp_product_c_bpartner_id,
    NULL                          AS c_bpartner_product_created,
    NULL                          AS c_bpartner_product_createdby,
    NULL                          AS c_bpartner_product_descriptio,
    NULL                          AS c_bpartner_product_isactive,
    NULL                          AS ismanufacturer,
    NULL                          AS c_bpartner_product_manufactur,
    NULL                          AS c_bpartner_product_qualityrat,
    NULL                          AS shelflifemindays,
    NULL                          AS c_bpartner_product_shelflifem,
    NULL                          AS c_bpartner_product_updated,
    NULL                          AS c_bpartner_product_updatedby,
    NULL                          AS c_bpartner_product_vendorcate,
    NULL                          AS acqusitioncost,
    NULL                          AS actuallifetimevalue,
    NULL                          AS c_bpartner_ad_language,
    NULL                          AS ad_orgbp_id,
    NULL                          AS c_bpartner_ad_org_id,
    NULL                          AS bpartner_parent_id,
    NULL                          AS c_bp_group_id,
    NULL                          AS c_dunning_id,
    NULL                          AS c_greeting_id,
    NULL                          AS c_invoiceschedule_id,
    NULL                          AS c_bpartner_c_paymentterm_id,
    NULL                          AS c_bpartner_created,
    NULL                          AS c_bpartner_createdby,
    NULL                          AS c_bpartner_c_taxgroup_id,
    NULL                          AS c_bpartner_deliveryrule,
    NULL                          AS c_bpartner_deliveryviarule,
    NULL                          AS c_bpartner_description,
    NULL                          AS c_bpartner_dunninggrace,
    NULL                          AS duns,
    NULL                          AS c_bpartner_firstsale,
    NULL                          AS c_bpartner_flatdiscount,
    NULL                          AS c_bpartner_freightcostrule,
    NULL                          AS c_bpartner_invoicerule,
    NULL                          AS c_bpartner_isactive,
    NULL                          AS iscustomer,
    NULL                          AS c_bpartner_isdiscountprinted,
    NULL                          AS c_bpartner_isemployee,
    NULL                          AS c_bpartner_ismanufacturer,
    NULL                          AS isonetime,
    NULL                          AS ispotaxexempt,
    NULL                          AS isprospect,
    NULL                          AS issalesrep,
    NULL                          AS c_bpartner_issummary,
    NULL                          AS c_bpartner_istaxexempt,
    NULL                          AS isvendor,
    NULL                          AS logo_id,
    NULL                          AS m_discountschema_id,
    NULL                          AS c_bpartner_m_pricelist_id,
    NULL                          AS naics,
    NULL                          AS c_bpartner_name,
    NULL                          AS c_bpartner_name2,
    NULL                          AS numberemployees,
    NULL                          AS c_bpartner_paymentrule,
    NULL                          AS paymentrulepo,
    NULL                          AS po_discountschema_id,
    NULL                          AS po_paymentterm_id,
    NULL                          AS po_pricelist_id,
    NULL                          AS c_bpartner_poreference,
    NULL                          AS potentiallifetimevalue,
    NULL                          AS c_bpartner_rating,
    NULL                          AS c_bpartner_referenceno,
    NULL                          AS c_bpartner_salesrep_id,
    NULL                          AS salesvolume,
    NULL                          AS c_bpartner_sendemail,
    NULL                          AS shareofcustomer,
    NULL                          AS c_bpartner_shelflifeminpct,
    NULL                          AS so_creditlimit,
    NULL                          AS socreditstatus,
    NULL                          AS so_creditused,
    NULL                          AS so_description,
    NULL                          AS c_bpartner_taxid,
    NULL                          AS totalopenbalance,
    NULL                          AS c_bpartner_updated,
    NULL                          AS c_bpartner_updatedby,
    NULL                          AS c_bpartner_url,
    NULL                          AS c_bpartner_value,
    NULL                          AS c_bpartner_location_ad_org_id,
    NULL                          AS c_bpartner_location_c_bpartne,
    NULL                          AS c_bpartner_location_created,
    NULL                          AS c_bpartner_location_createdby,
    NULL                          AS c_salesregion_id,
    NULL                          AS c_bpartner_location_fax,
    NULL                          AS c_bpartner_location_isactive,
    NULL                          AS isbillto,
    NULL                          AS isdn,
    NULL                          AS ispayfrom,
    NULL                          AS isremitto,
    NULL                          AS isshipto,
    NULL                          AS c_bpartner_location_name,
    NULL                          AS c_bpartner_location_phone,
    NULL                          AS c_bpartner_location_phone2,
    NULL                          AS c_bpartner_location_updated,
    NULL                          AS c_bpartner_location_updatedby,
    NULL                          AS c_tax_ad_org_id,
    NULL                          AS ad_rule_id,
    NULL                          AS c_country_id,
    NULL                          AS c_region_id,
    NULL                          AS c_tax_c_taxcategory_id,
    NULL                          AS c_tax_description,
    NULL                          AS c_tax_isactive,
    NULL                          AS c_tax_isdefault,
    NULL                          AS isdocumentlevel,
    NULL                          AS issalestax,
    NULL                          AS c_tax_issummary,
    NULL                          AS c_tax_istaxexempt,
    NULL                          AS c_tax_name,
    NULL                          AS parent_tax_id,
    NULL                          AS rate,
    NULL                          AS requirestaxcertificate,
    NULL                          AS sopotype,
    NULL                          AS to_country_id,
    NULL                          AS c_tax_to_region_id,
    NULL                          AS validfrom 
FROM c_ordertax ot 
        JOIN c_tax_trl tt 
        ON ot.c_tax_id = tt.c_tax_id
;
DROP VIEW c_invoice_linetax_v;

CREATE OR REPLACE VIEW c_invoice_linetax_v AS 
    (
        ( ( SELECT il.ad_client_id,
                il.ad_org_id,
                il.isactive,
                il.created,
                il.createdby,
                il.updated,
                il.updatedby,
                'en_US'                               AS ad_language,
                il.c_invoice_id,
                il.c_invoiceline_id,
                il.c_tax_id,
                il.taxamt,
                il.linetotalamt,
                t.taxindicator,
                il.line,
                p.m_product_id,
                CASE WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN il.qtyinvoiced ELSE NULL END                               AS qtyinvoiced,
                CASE WHEN il.qtyentered <> 0 OR il.m_product_id IS NOT NULL THEN il.qtyentered ELSE NULL END                               AS qtyentered,
                CASE WHEN il.qtyentered <> 0 OR il.m_product_id IS NOT NULL THEN uom.uomsymbol ELSE NULL END                               AS uomsymbol,
                COALESCE(c.name, (p.name || COALESCE(productattribute(il.m_attributesetinstance_id), '')), il.description)  AS name,
                CASE WHEN COALESCE(c.name, p.name) IS NOT NULL THEN il.description ELSE NULL END                               AS description,
                p.documentnote,
                p.upc,
                p.sku,
                COALESCE(pp.vendorproductno, p.value) AS productvalue,
                ra.description                        AS resourcedescription,
                CASE WHEN i.isdiscountprinted = 'Y' AND il.pricelist <> 0 THEN il.pricelist ELSE NULL END                               AS pricelist,
                CASE WHEN i.isdiscountprinted = 'Y' AND il.pricelist <> 0 AND il.qtyentered <> 0 THEN il.pricelist * il.qtyinvoiced / il.qtyentered ELSE NULL 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 ELSE NULL END                               
                AS discount,
                CASE WHEN il.priceactual <> 0 OR il.m_product_id IS NOT NULL THEN il.priceactual ELSE NULL END                               AS priceactual,
                CASE WHEN il.priceentered <> 0 OR il.m_product_id IS NOT NULL THEN il.priceentered ELSE NULL END                               AS priceentered,
                CASE WHEN il.linenetamt <> 0 OR il.m_product_id IS NOT NULL THEN il.linenetamt ELSE NULL END                               AS linenetamt,
                il.m_attributesetinstance_id,
                asi.m_attributeset_id,
                asi.serno,
                asi.lot,
                asi.m_lot_id,
                asi.guaranteedate,
                p.description                         AS productdescription,
                p.imageurl,
                il.c_campaign_id,
                il.c_project_id,
                il.c_activity_id,
                il.c_projectphase_id,
                il.c_projecttask_id,
                il.ad_orgtrx_id                       AS c_invoiceline_ad_orgtrx_id,
                il.a_processed,
                il.c_charge_id,
                il.c_orderline_id,
                il.c_uom_id,
                il.isdescription,
                il.isprinted                          AS c_invoiceline_isprinted,
                il.m_inoutline_id,
                il.m_rmaline_id,
                il.pricelimit,
                il.processed                          AS c_invoiceline_processed,
                il.ref_invoiceline_id,
                il.rramt,
                il.rrstartdate,
                il.s_resourceassignment_id,
                il.user1_id                           AS c_invoiceline_user1_id,
                il.user2_id                           AS c_invoiceline_user2_id,
                uom.ad_org_id                         AS c_uom_ad_org_id,
                uom.costingprecision,
                uom.description                       AS c_uom_description,
                uom.isactive                          AS c_uom_isactive,
                uom.isdefault                         AS c_uom_isdefault,
                uom.name                              AS c_uom_name,
                uom.stdprecision,
                uom.uomtype,
                uom.x12de355,
                i.ad_org_id                           AS c_invoice_ad_org_id,
                i.ad_orgtrx_id                        AS c_invoice_ad_orgtrx_id,
                i.ad_user_id                          AS c_invoice_ad_user_id,
                i.c_activity_id                       AS c_invoice_c_activity_id,
                i.c_bpartner_id                       AS c_invoice_c_bpartner_id,
                i.c_bpartner_location_id,
                i.c_campaign_id                       AS c_invoice_c_campaign_id,
                i.c_charge_id                         AS c_invoice_c_charge_id,
                i.c_conversiontype_id,
                i.c_currency_id,
                i.c_doctype_id,
                i.c_doctypetarget_id,
                i.c_dunninglevel_id,
                i.chargeamt                           AS c_invoice_chargeamt,
                i.c_order_id,
                i.c_payment_id,
                i.c_paymentterm_id,
                i.c_project_id                        AS c_invoice_c_project_id,
                i.created                             AS c_invoice_created,
                i.createdby                           AS c_invoice_createdby,
                i.dateacct,
                i.dateinvoiced,
                i.dateordered,
                i.dateprinted,
                i.description                         AS c_invoice_description,
                i.docaction,
                i.docstatus,
                i.documentno,
                i.dunninggrace,
                i.generateto,
                i.grandtotal,
                i.invoicecollectiontype,
                i.isactive                            AS c_invoice_isactive,
                i.isapproved,
                i.isdiscountprinted,
                i.isindispute,
                i.ispaid,
                i.ispayschedulevalid,
                i.isprinted                           AS c_invoice_isprinted,
                i.isselfservice                       AS c_invoice_isselfservice,
                i.issotrx,
                i.istaxincluded                       AS c_invoice_istaxincluded,
                i.istransferred,
                i.m_pricelist_id,
                i.m_rma_id,
                i.paymentrule,
                i.poreference,
                i.posted,
                i.processedon,
                i.processing,
                i.ref_invoice_id,
                i.reversal_id,
                i.salesrep_id,
                i.sendemail,
                i.totallines,
                i.updated                             AS c_invoice_updated,
                i.updatedby                           AS c_invoice_updatedby,
                i.user1_id                            AS c_invoice_user1_id,
                i.user2_id                            AS c_invoice_user2_id,
                t.ad_org_id                           AS c_tax_ad_org_id,
                t.ad_rule_id,
                t.c_country_id,
                t.c_region_id,
                t.c_taxcategory_id                    AS c_tax_c_taxcategory_id,
                t.description                         AS c_tax_description,
                t.isactive                            AS c_tax_isactive,
                t.isdefault                           AS c_tax_isdefault,
                t.isdocumentlevel,
                t.issalestax,
                t.issummary                           AS c_tax_issummary,
                t.istaxexempt,
                t.name                                AS c_tax_name,
                t.parent_tax_id,
                t.rate,
                t.requirestaxcertificate,
                t.sopotype,
                t.to_country_id,
                t.to_region_id,
                t.validfrom,
                p.ad_org_id                           AS m_product_ad_org_id,
                p.classification,
                p.copyfrom                            AS m_product_copyfrom,
                p.created                             AS m_product_created,
                p.createdby                           AS m_product_createdby,
                p.c_revenuerecognition_id,
                p.c_subscriptiontype_id,
                p.c_taxcategory_id                    AS m_product_c_taxcategory_id,
                p.descriptionurl,
                p.discontinued,
                p.discontinuedat,
                p.group1,
                p.group2,
                p.guaranteedays,
                p.guaranteedaysmin,
                p.help,
                p.isactive                            AS m_product_isactive,
                p.isbom,
                p.isdropship,
                p.isexcludeautodelivery,
                p.isinvoiceprintdetails,
                p.ispicklistprintdetails,
                p.ispurchased,
                p.isselfservice                       AS m_product_isselfservice,
                p.issold,
                p.isstocked,
                p.issummary                           AS m_product_issummary,
                p.isverified,
                p.iswebstorefeatured,
                p.lowlevel,
                p.m_attributeset_id                   AS m_product_m_attributeset_id,
                p.m_attributesetinstance_id           AS m_product_m_asi_id,
                p.m_freightcategory_id,
                p.m_locator_id,
                p.m_product_category_id               AS m_product_m_prod_category_id,
                p.processing                          AS m_product_processing,
                p.producttype,
                p.r_mailtext_id,
                p.salesrep_id                         AS m_product_salesrep_id,
                p.s_expensetype_id,
                p.shelfdepth,
                p.shelfheight,
                p.shelfwidth,
                p.s_resource_id                       AS m_product_s_resource_id,
                p.unitsperpack,
                p.unitsperpallet,
                p.updated                             AS m_product_updated,
                p.updatedby                           AS m_product_updatedby,
                p.versionno,
                p.volume,
                p.weight,
                c.ad_org_id                           AS c_charge_ad_org_id,
                c.c_bpartner_id                       AS c_charge_c_bpartner_id,
                c.c_chargetype_id,
                c.chargeamt                           AS c_charge_chargeamt,
                c.c_taxcategory_id                    AS c_charge_c_taxcategory_id,
                c.description                         AS c_charge_description,
                c.isactive                            AS c_charge_isactive,
                c.issamecurrency,
                c.issametax,
                c.istaxincluded                       AS c_charge_istaxincluded,
                pp.ad_org_id                          AS c_bp_product_ad_org_id,
                pp.c_bpartner_id                      AS c_bp_product_c_bpartner_id,
                pp.created                            AS c_bp_product_created,
                pp.createdby                          AS c_bp_product_createdby,
                pp.description                        AS c_bp_product_description,
                pp.isactive                           AS c_bp_product_isactive,
                pp.ismanufacturer,
                pp.manufacturer,
                pp.qualityrating,
                pp.shelflifemindays,
                pp.shelflifeminpct,
                pp.updated                            AS c_bp_product_updated,
                pp.updatedby                          AS c_bp_product_updatedby,
                pp.vendorcategory,
                ra.ad_org_id                          AS s_rassignment_ad_org_id,
                ra.assigndatefrom,
                ra.assigndateto,
                ra.created                            AS s_rassignment_created,
                ra.createdby                          AS s_rassignment_createby,
                ra.isactive                           AS s_rassignment_isactive,
                ra.isconfirmed,
                ra.name                               AS s_rassignment_name,
                ra.qty                                AS s_rassignment_qty,
                ra.s_resource_id                      AS s_rassignment_s_resource_id,
                ra.updated                            AS s_rassignment_updated,
                ra.updatedby                          AS s_rassignment_updatedby,
                asi.ad_org_id                         AS m_asi_ad_org_id,
                asi.created                           AS m_asi_created,
                asi.createdby                         AS m_asi_createdby,
                asi.description                       AS m_asi_description,
                asi.isactive                          AS m_asi_isactive,
                asi.updated                           AS m_asi_updated,
                asi.updatedby                         AS m_asi_updatedby 
            FROM c_invoiceline il 
                JOIN c_uom uom 
                ON il.c_uom_id = uom.c_uom_id 
                JOIN c_invoice i 
                ON il.c_invoice_id = i.c_invoice_id 
                LEFT JOIN c_tax t 
                ON il.c_tax_id = t.c_tax_id 
                LEFT JOIN m_product p 
                ON il.m_product_id = p.m_product_id 
                LEFT JOIN c_charge c 
                ON il.c_charge_id = c.c_charge_id 
                LEFT JOIN c_bpartner_product pp 
                ON il.m_product_id = pp.m_product_id AND i.c_bpartner_id = pp.c_bpartner_id 
                LEFT JOIN s_resourceassignment ra 
                ON il.s_resourceassignment_id = ra.s_resourceassignment_id 
                LEFT JOIN m_attributesetinstance asi 
                ON il.m_attributesetinstance_id = asi.m_attributesetinstance_id 
                UNION /* BOM lines */ SELECT il.ad_client_id,
                        il.ad_org_id,
                        il.isactive,
                        il.created,
                        il.createdby,
                        il.updated,
                        il.updatedby,
                        'en_US'                     AS ad_language,
                        il.c_invoice_id,
                        il.c_invoiceline_id,
                        il.c_tax_id,
                        il.taxamt,
                        il.linetotalamt,
                        t.taxindicator,
                        il.line + bl.line / 100     AS line,
                        p.m_product_id,
                        CASE WHEN bl.isqtypercentage = 'N' THEN il.qtyinvoiced * bl.qtybom ELSE il.qtyinvoiced * (bl.qtybatch / 100) END                     AS qtyinvoiced,
                        CASE WHEN bl.isqtypercentage = 'N' THEN il.qtyentered * bl.qtybom ELSE il.qtyentered * (bl.qtybatch / 100) END                     AS qtyentered,
                        uom.uomsymbol,
                        p.name,
                        b.description,
                        p.documentnote,
                        p.upc,
                        p.sku,
                        p.value                     AS productvalue,
                        NULL                        AS resourcedescription,
                        NULL                        AS pricelist,
                        NULL                        AS priceenteredlist,
                        NULL                        AS discount,
                        NULL                        AS priceactual,
                        NULL                        AS priceentered,
                        NULL                        AS linenetamt,
                        il.m_attributesetinstance_id,
                        asi.m_attributeset_id,
                        asi.serno,
                        asi.lot,
                        asi.m_lot_id,
                        asi.guaranteedate,
                        p.description               AS productdescription,
                        p.imageurl,
                        il.c_campaign_id,
                        il.c_project_id,
                        il.c_activity_id,
                        il.c_projectphase_id,
                        il.c_projecttask_id,
                        il.ad_orgtrx_id             AS c_invoiceline_ad_orgtrx_id,
                        il.a_processed,
                        il.c_charge_id,
                        il.c_orderline_id,
                        il.c_uom_id,
                        il.isdescription,
                        il.isprinted                AS c_invoiceline_isprinted,
                        il.m_inoutline_id,
                        il.m_rmaline_id,
                        il.pricelimit,
                        il.processed                AS c_invoiceline_processed,
                        il.ref_invoiceline_id,
                        il.rramt,
                        il.rrstartdate,
                        il.s_resourceassignment_id,
                        il.user1_id                 AS c_invoiceline_user1_id,
                        il.user2_id                 AS c_invoiceline_user2_id,
                        uom.ad_org_id               AS c_uom_ad_org_id,
                        uom.costingprecision,
                        uom.description             AS c_uom_description,
                        uom.isactive                AS c_uom_isactive,
                        uom.isdefault               AS c_uom_isdefault,
                        uom.name                    AS c_uom_name,
                        uom.stdprecision,
                        uom.uomtype,
                        uom.x12de355,
                        NULL                        AS c_invoice_ad_org_id,
                        NULL                        AS c_invoice_ad_orgtrx_id,
                        NULL                        AS c_invoice_ad_user_id,
                        NULL                        AS c_invoice_c_activity_id,
                        NULL                        AS c_invoice_c_bpartner_id,
                        NULL                        AS c_bpartner_location_id,
                        NULL                        AS c_invoice_c_campaign_id,
                        NULL                        AS c_invoice_c_charge_id,
                        NULL                        AS c_conversiontype_id,
                        NULL                        AS c_currency_id,
                        NULL                        AS c_doctype_id,
                        NULL                        AS c_doctypetarget_id,
                        NULL                        AS c_dunninglevel_id,
                        NULL                        AS c_invoice_chargeamt,
                        NULL                        AS c_order_id,
                        NULL                        AS c_payment_id,
                        NULL                        AS c_paymentterm_id,
                        NULL                        AS c_invoice_c_project_id,
                        NULL                        AS c_invoice_created,
                        NULL                        AS c_invoice_createdby,
                        NULL                        AS dateacct,
                        NULL                        AS dateinvoiced,
                        NULL                        AS dateordered,
                        NULL                        AS dateprinted,
                        NULL                        AS c_invoice_description,
                        NULL                        AS docaction,
                        NULL                        AS docstatus,
                        NULL                        AS documentno,
                        NULL                        AS dunninggrace,
                        NULL                        AS generateto,
                        NULL                        AS grandtotal,
                        NULL                        AS invoicecollectiontype,
                        NULL                        AS c_invoice_isactive,
                        NULL                        AS isapproved,
                        NULL                        AS isdiscountprinted,
                        NULL                        AS isindispute,
                        NULL                        AS ispaid,
                        NULL                        AS ispayschedulevalid,
                        NULL                        AS c_invoice_isprinted,
                        NULL                        AS c_invoice_isselfservice,
                        NULL                        AS issotrx,
                        NULL                        AS c_invoice_istaxincluded,
                        NULL                        AS istransferred,
                        NULL                        AS m_pricelist_id,
                        NULL                        AS m_rma_id,
                        NULL                        AS paymentrule,
                        NULL                        AS poreference,
                        NULL                        AS posted,
                        NULL                        AS processedon,
                        NULL                        AS processing,
                        NULL                        AS ref_invoice_id,
                        NULL                        AS reversal_id,
                        NULL                        AS salesrep_id,
                        NULL                        AS sendemail,
                        NULL                        AS totallines,
                        NULL                        AS c_invoice_updated,
                        NULL                        AS c_invoice_updatedby,
                        NULL                        AS c_invoice_user1_id,
                        NULL                        AS c_invoice_user2_id,
                        t.ad_org_id                 AS c_tax_ad_org_id,
                        t.ad_rule_id,
                        t.c_country_id,
                        t.c_region_id,
                        t.c_taxcategory_id          AS c_tax_c_taxcategory_id,
                        t.description               AS c_tax_description,
                        t.isactive                  AS c_tax_isactive,
                        t.isdefault                 AS c_tax_isdefault,
                        t.isdocumentlevel,
                        t.issalestax,
                        t.issummary                 AS c_tax_issummary,
                        t.istaxexempt,
                        t.name                      AS c_tax_name,
                        t.parent_tax_id,
                        t.rate,
                        t.requirestaxcertificate,
                        t.sopotype,
                        t.to_country_id,
                        t.to_region_id,
                        t.validfrom,
                        p.ad_org_id                 AS m_product_ad_org_id,
                        p.classification,
                        p.copyfrom                  AS m_product_copyfrom,
                        p.created                   AS m_product_created,
                        p.createdby                 AS m_product_createdby,
                        p.c_revenuerecognition_id,
                        p.c_subscriptiontype_id,
                        p.c_taxcategory_id          AS m_product_c_taxcategory_id,
                        p.descriptionurl,
                        p.discontinued,
                        p.discontinuedat,
                        p.group1,
                        p.group2,
                        p.guaranteedays,
                        p.guaranteedaysmin,
                        p.help,
                        p.isactive                  AS m_product_isactive,
                        p.isbom,
                        p.isdropship,
                        p.isexcludeautodelivery,
                        p.isinvoiceprintdetails,
                        p.ispicklistprintdetails,
                        p.ispurchased,
                        p.isselfservice             AS m_product_isselfservice,
                        p.issold,
                        p.isstocked,
                        p.issummary                 AS m_product_issummary,
                        p.isverified,
                        p.iswebstorefeatured,
                        p.lowlevel,
                        p.m_attributeset_id         AS m_product_m_attributeset_id,
                        p.m_attributesetinstance_id AS m_product_m_asi_id,
                        p.m_freightcategory_id,
                        p.m_locator_id,
                        p.m_product_category_id     AS m_product_m_prod_category_id,
                        p.processing                AS m_product_processing,
                        p.producttype,
                        p.r_mailtext_id,
                        p.salesrep_id               AS m_product_salesrep_id,
                        p.s_expensetype_id,
                        p.shelfdepth,
                        p.shelfheight,
                        p.shelfwidth,
                        p.s_resource_id             AS m_product_s_resource_id,
                        p.unitsperpack,
                        p.unitsperpallet,
                        p.updated                   AS m_product_updated,
                        p.updatedby                 AS m_product_updatedby,
                        p.versionno,
                        p.volume,
                        p.weight,
                        NULL                        AS c_charge_ad_org_id,
                        NULL                        AS c_charge_c_bpartner_id,
                        NULL                        AS c_chargetype_id,
                        NULL                        AS c_charge_chargeamt,
                        NULL                        AS c_charge_c_taxcategory_id,
                        NULL                        AS c_charge_description,
                        NULL                        AS c_charge_isactive,
                        NULL                        AS issamecurrency,
                        NULL                        AS issametax,
                        NULL                        AS c_charge_istaxincluded,
                        NULL                        AS c_bp_product_ad_org_id,
                        NULL                        AS c_bp_product_c_bpartner_id,
                        NULL                        AS c_bp_product_created,
                        NULL                        AS c_bp_product_createdby,
                        NULL                        AS c_bp_product_description,
                        NULL                        AS c_bp_product_isactive,
                        NULL                        AS ismanufacturer,
                        NULL                        AS manufacturer,
                        NULL                        AS qualityrating,
                        NULL                        AS shelflifemindays,
                        NULL                        AS shelflifeminpct,
                        NULL                        AS c_bp_product_updated,
                        NULL                        AS c_bp_product_updatedby,
                        NULL                        AS vendorcategory,
                        NULL                        AS s_rassignment_ad_org_id,
                        NULL                        AS assigndatefrom,
                        NULL                        AS assigndateto,
                        NULL                        AS s_rassignment_created,
                        NULL                        AS s_rassignment_createby,
                        NULL                        AS s_rassignment_isactive,
                        NULL                        AS isconfirmed,
                        NULL                        AS s_rassignment_name,
                        NULL                        AS s_rassignment_qty,
                        NULL                        AS s_rassignment_s_resource_id,
                        NULL                        AS s_rassignment_updated,
                        NULL                        AS s_rassignment_updatedby,
                        asi.ad_org_id               AS m_asi_ad_org_id,
                        asi.created                 AS m_asi_created,
                        asi.createdby               AS m_asi_createdby,
                        asi.description             AS m_asi_description,
                        asi.isactive                AS m_asi_isactive,
                        asi.updated                 AS m_asi_updated,
                        asi.updatedby               AS m_asi_updatedby 
                    FROM pp_product_bom b 
                        JOIN c_invoiceline il 
                        ON b.m_product_id = il.m_product_id 
                        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' 
                        JOIN pp_product_bomline bl 
                        ON bl.pp_product_bom_id = b.pp_product_bom_id 
                        JOIN m_product p 
                        ON bl.m_product_id = p.m_product_id 
                        JOIN c_uom uom 
                        ON p.c_uom_id = uom.c_uom_id 
                        LEFT JOIN c_tax t 
                        ON il.c_tax_id = t.c_tax_id 
                        LEFT JOIN m_attributesetinstance asi 
                        ON il.m_attributesetinstance_id = asi.m_attributesetinstance_id) 
        UNION   /* comment lines */ SELECT il.ad_client_id,
                    il.ad_org_id,
                    il.isactive,
                    il.created,
                    il.createdby,
                    il.updated,
                    il.updatedby,
                    'en_US'         AS ad_language,
                    il.c_invoice_id,
                    il.c_invoiceline_id,
                    NULL            AS c_tax_id,
                    NULL            AS taxamt,
                    NULL            AS linetotalamt,
                    NULL            AS taxindicator,
                    il.line,
                    NULL            AS m_product_id,
                    NULL            AS qtyinvoiced,
                    NULL            AS qtyentered,
                    NULL            AS uomsymbol,
                    il.description  AS name,
                    NULL            AS description,
                    NULL            AS documentnote,
                    NULL            AS upc,
                    NULL            AS sku,
                    NULL            AS productvalue,
                    NULL            AS resourcedescription,
                    NULL            AS pricelist,
                    NULL            AS priceenteredlist,
                    NULL            AS discount,
                    NULL            AS priceactual,
                    NULL            AS priceentered,
                    NULL            AS linenetamt,
                    NULL            AS m_attributesetinstance_id,
                    NULL            AS m_attributeset_id,
                    NULL            AS serno,
                    NULL            AS lot,
                    NULL            AS m_lot_id,
                    NULL            AS guaranteedate,
                    NULL            AS productdescription,
                    NULL            AS imageurl,
                    NULL            AS c_campaign_id,
                    NULL            AS c_project_id,
                    NULL            AS c_activity_id,
                    NULL            AS c_projectphase_id,
                    NULL            AS c_projecttask_id,
                    NULL            AS c_invoiceline_ad_orgtrx_id,
                    NULL            AS a_processed,
                    NULL            AS c_charge_id,
                    NULL            AS c_orderline_id,
                    NULL            AS c_uom_id,
                    NULL            AS isdescription,
                    NULL            AS c_invoiceline_isprinted,
                    NULL            AS m_inoutline_id,
                    NULL            AS m_rmaline_id,
                    NULL            AS pricelimit,
                    NULL            AS c_invoiceline_processed,
                    NULL            AS ref_invoiceline_id,
                    NULL            AS rramt,
                    NULL            AS rrstartdate,
                    NULL            AS s_resourceassignment_id,
                    NULL            AS c_invoiceline_user1_id,
                    NULL            AS c_invoiceline_user2_id,
                    NULL            AS c_uom_ad_org_id,
                    NULL            AS costingprecision,
                    NULL            AS c_uom_description,
                    NULL            AS c_uom_isactive,
                    NULL            AS c_uom_isdefault,
                    NULL            AS c_uom_name,
                    NULL            AS stdprecision,
                    NULL            AS uomtype,
                    NULL            AS x12de355,
                    NULL            AS c_invoice_ad_org_id,
                    NULL            AS c_invoice_ad_orgtrx_id,
                    NULL            AS c_invoice_ad_user_id,
                    NULL            AS c_invoice_c_activity_id,
                    NULL            AS c_invoice_c_bpartner_id,
                    NULL            AS c_bpartner_location_id,
                    NULL            AS c_invoice_c_campaign_id,
                    NULL            AS c_invoice_c_charge_id,
                    NULL            AS c_conversiontype_id,
                    NULL            AS c_currency_id,
                    NULL            AS c_doctype_id,
                    NULL            AS c_doctypetarget_id,
                    NULL            AS c_dunninglevel_id,
                    NULL            AS c_invoice_chargeamt,
                    NULL            AS c_order_id,
                    NULL            AS c_payment_id,
                    NULL            AS c_paymentterm_id,
                    NULL            AS c_invoice_c_project_id,
                    NULL            AS c_invoice_created,
                    NULL            AS c_invoice_createdby,
                    NULL            AS dateacct,
                    NULL            AS dateinvoiced,
                    NULL            AS dateordered,
                    NULL            AS dateprinted,
                    NULL            AS c_invoice_description,
                    NULL            AS docaction,
                    NULL            AS docstatus,
                    NULL            AS documentno,
                    NULL            AS dunninggrace,
                    NULL            AS generateto,
                    NULL            AS grandtotal,
                    NULL            AS invoicecollectiontype,
                    NULL            AS c_invoice_isactive,
                    NULL            AS isapproved,
                    NULL            AS isdiscountprinted,
                    NULL            AS isindispute,
                    NULL            AS ispaid,
                    NULL            AS ispayschedulevalid,
                    NULL            AS c_invoice_isprinted,
                    NULL            AS c_invoice_isselfservice,
                    NULL            AS issotrx,
                    NULL            AS c_invoice_istaxincluded,
                    NULL            AS istransferred,
                    NULL            AS m_pricelist_id,
                    NULL            AS m_rma_id,
                    NULL            AS paymentrule,
                    NULL            AS poreference,
                    NULL            AS posted,
                    NULL            AS processedon,
                    NULL            AS processing,
                    NULL            AS ref_invoice_id,
                    NULL            AS reversal_id,
                    NULL            AS salesrep_id,
                    NULL            AS sendemail,
                    NULL            AS totallines,
                    NULL            AS c_invoice_updated,
                    NULL            AS c_invoice_updatedby,
                    NULL            AS c_invoice_user1_id,
                    NULL            AS c_invoice_user2_id,
                    NULL            AS c_tax_ad_org_id,
                    NULL            AS ad_rule_id,
                    NULL            AS c_country_id,
                    NULL            AS c_region_id,
                    NULL            AS c_tax_c_taxcategory_id,
                    NULL            AS c_tax_description,
                    NULL            AS c_tax_isactive,
                    NULL            AS c_tax_isdefault,
                    NULL            AS isdocumentlevel,
                    NULL            AS issalestax,
                    NULL            AS c_tax_issummary,
                    NULL            AS istaxexempt,
                    NULL            AS c_tax_name,
                    NULL            AS parent_tax_id,
                    NULL            AS rate,
                    NULL            AS requirestaxcertificate,
                    NULL            AS sopotype,
                    NULL            AS to_country_id,
                    NULL            AS to_region_id,
                    NULL            AS validfrom,
                    NULL            AS m_product_ad_org_id,
                    NULL            AS classification,
                    NULL            AS m_product_copyfrom,
                    NULL            AS m_product_created,
                    NULL            AS m_product_createdby,
                    NULL            AS c_revenuerecognition_id,
                    NULL            AS c_subscriptiontype_id,
                    NULL            AS m_product_c_taxcategory_id,
                    NULL            AS descriptionurl,
                    NULL            AS discontinued,
                    NULL            AS discontinuedat,
                    NULL            AS group1,
                    NULL            AS group2,
                    NULL            AS guaranteedays,
                    NULL            AS guaranteedaysmin,
                    NULL            AS help,
                    NULL            AS m_product_isactive,
                    NULL            AS isbom,
                    NULL            AS isdropship,
                    NULL            AS isexcludeautodelivery,
                    NULL            AS isinvoiceprintdetails,
                    NULL            AS ispicklistprintdetails,
                    NULL            AS ispurchased,
                    NULL            AS m_product_isselfservice,
                    NULL            AS issold,
                    NULL            AS isstocked,
                    NULL            AS m_product_issummary,
                    NULL            AS isverified,
                    NULL            AS iswebstorefeatured,
                    NULL            AS lowlevel,
                    NULL            AS m_product_m_attributeset_id,
                    NULL            AS m_product_m_asi_id,
                    NULL            AS m_freightcategory_id,
                    NULL            AS m_locator_id,
                    NULL            AS m_product_m_prod_category_id,
                    NULL            AS m_product_processing,
                    NULL            AS producttype,
                    NULL            AS r_mailtext_id,
                    NULL            AS m_product_salesrep_id,
                    NULL            AS s_expensetype_id,
                    NULL            AS shelfdepth,
                    NULL            AS shelfheight,
                    NULL            AS shelfwidth,
                    NULL            AS m_product_s_resource_id,
                    NULL            AS unitsperpack,
                    NULL            AS unitsperpallet,
                    NULL            AS m_product_updated,
                    NULL            AS m_product_updatedby,
                    NULL            AS versionno,
                    NULL            AS volume,
                    NULL            AS weight,
                    NULL            AS c_charge_ad_org_id,
                    NULL            AS c_charge_c_bpartner_id,
                    NULL            AS c_chargetype_id,
                    NULL            AS c_charge_chargeamt,
                    NULL            AS c_charge_c_taxcategory_id,
                    NULL            AS c_charge_description,
                    NULL            AS c_charge_isactive,
                    NULL            AS issamecurrency,
                    NULL            AS issametax,
                    NULL            AS c_charge_istaxincluded,
                    NULL            AS c_bp_product_ad_org_id,
                    NULL            AS c_bp_product_c_bpartner_id,
                    NULL            AS c_bp_product_created,
                    NULL            AS c_bp_product_createdby,
                    NULL            AS c_bp_product_description,
                    NULL            AS c_bp_product_isactive,
                    NULL            AS ismanufacturer,
                    NULL            AS manufacturer,
                    NULL            AS qualityrating,
                    NULL            AS shelflifemindays,
                    NULL            AS shelflifeminpct,
                    NULL            AS c_bp_product_updated,
                    NULL            AS c_bp_product_updatedby,
                    NULL            AS vendorcategory,
                    NULL            AS s_rassignment_ad_org_id,
                    NULL            AS assigndatefrom,
                    NULL            AS assigndateto,
                    NULL            AS s_rassignment_created,
                    NULL            AS s_rassignment_createby,
                    NULL            AS s_rassignment_isactive,
                    NULL            AS isconfirmed,
                    NULL            AS s_rassignment_name,
                    NULL            AS s_rassignment_qty,
                    NULL            AS s_rassignment_s_resource_id,
                    NULL            AS s_rassignment_updated,
                    NULL            AS s_rassignment_updatedby,
                    NULL            AS m_asi_ad_org_id,
                    NULL            AS m_asi_created,
                    NULL            AS m_asi_createdby,
                    NULL            AS m_asi_description,
                    NULL            AS m_asi_isactive,
                    NULL            AS m_asi_updated,
                    NULL            AS m_asi_updatedby 
                FROM c_invoiceline il 
                WHERE il.c_uom_id IS NULL) 
    UNION   /* empty line */ SELECT i.ad_client_id,
                i.ad_org_id,
                i.isactive,
                i.created,
                i.createdby,
                i.updated,
                i.updatedby,
                'en_US'         AS ad_language,
                i.c_invoice_id,
                NULL            AS c_invoiceline_id,
                NULL            AS c_tax_id,
                NULL            AS taxamt,
                NULL            AS linetotalamt,
                NULL            AS taxindicator,
                999998          AS line,
                NULL            AS m_product_id,
                NULL            AS qtyinvoiced,
                NULL            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,
                NULL            AS pricelist,
                NULL            AS priceenteredlist,
                NULL            AS discount,
                NULL            AS priceactual,
                NULL            AS priceentered,
                NULL            AS linenetamt,
                NULL            AS m_attributesetinstance_id,
                NULL            AS m_attributeset_id,
                NULL            AS serno,
                NULL            AS lot,
                NULL            AS m_lot_id,
                NULL            AS guaranteedate,
                NULL            AS productdescription,
                NULL            AS imageurl,
                NULL            AS c_campaign_id,
                NULL            AS c_project_id,
                NULL            AS c_activity_id,
                NULL            AS c_projectphase_id,
                NULL            AS c_projecttask_id,
                NULL            AS c_invoiceline_ad_orgtrx_id,
                NULL            AS a_processed,
                NULL            AS c_charge_id,
                NULL            AS c_orderline_id,
                NULL            AS c_uom_id,
                NULL            AS isdescription,
                NULL            AS c_invoiceline_isprinted,
                NULL            AS m_inoutline_id,
                NULL            AS m_rmaline_id,
                NULL            AS pricelimit,
                NULL            AS c_invoiceline_processed,
                NULL            AS ref_invoiceline_id,
                NULL            AS rramt,
                NULL            AS rrstartdate,
                NULL            AS s_resourceassignment_id,
                NULL            AS c_invoiceline_user1_id,
                NULL            AS c_invoiceline_user2_id,
                NULL            AS c_uom_ad_org_id,
                NULL            AS costingprecision,
                NULL            AS c_uom_description,
                NULL            AS c_uom_isactive,
                NULL            AS c_uom_isdefault,
                NULL            AS c_uom_name,
                NULL            AS stdprecision,
                NULL            AS uomtype,
                NULL            AS x12de355,
                NULL            AS c_invoice_ad_org_id,
                NULL            AS c_invoice_ad_orgtrx_id,
                NULL            AS c_invoice_ad_user_id,
                NULL            AS c_invoice_c_activity_id,
                NULL            AS c_invoice_c_bpartner_id,
                NULL            AS c_bpartner_location_id,
                NULL            AS c_invoice_c_campaign_id,
                NULL            AS c_invoice_c_charge_id,
                NULL            AS c_conversiontype_id,
                NULL            AS c_currency_id,
                NULL            AS c_doctype_id,
                NULL            AS c_doctypetarget_id,
                NULL            AS c_dunninglevel_id,
                NULL            AS c_invoice_chargeamt,
                NULL            AS c_order_id,
                NULL            AS c_payment_id,
                NULL            AS c_paymentterm_id,
                NULL            AS c_invoice_c_project_id,
                NULL            AS c_invoice_created,
                NULL            AS c_invoice_createdby,
                NULL            AS dateacct,
                NULL            AS dateinvoiced,
                NULL            AS dateordered,
                NULL            AS dateprinted,
                NULL            AS c_invoice_description,
                NULL            AS docaction,
                NULL            AS docstatus,
                NULL            AS documentno,
                NULL            AS dunninggrace,
                NULL            AS generateto,
                NULL            AS grandtotal,
                NULL            AS invoicecollectiontype,
                NULL            AS c_invoice_isactive,
                NULL            AS isapproved,
                NULL            AS isdiscountprinted,
                NULL            AS isindispute,
                NULL            AS ispaid,
                NULL            AS ispayschedulevalid,
                NULL            AS c_invoice_isprinted,
                NULL            AS c_invoice_isselfservice,
                NULL            AS issotrx,
                NULL            AS c_invoice_istaxincluded,
                NULL            AS istransferred,
                NULL            AS m_pricelist_id,
                NULL            AS m_rma_id,
                NULL            AS paymentrule,
                NULL            AS poreference,
                NULL            AS posted,
                NULL            AS processedon,
                NULL            AS processing,
                NULL            AS ref_invoice_id,
                NULL            AS reversal_id,
                NULL            AS salesrep_id,
                NULL            AS sendemail,
                NULL            AS totallines,
                NULL            AS c_invoice_updated,
                NULL            AS c_invoice_updatedby,
                NULL            AS c_invoice_user1_id,
                NULL            AS c_invoice_user2_id,
                NULL            AS c_tax_ad_org_id,
                NULL            AS ad_rule_id,
                NULL            AS c_country_id,
                NULL            AS c_region_id,
                NULL            AS c_tax_c_taxcategory_id,
                NULL            AS c_tax_description,
                NULL            AS c_tax_isactive,
                NULL            AS c_tax_isdefault,
                NULL            AS isdocumentlevel,
                NULL            AS issalestax,
                NULL            AS c_tax_issummary,
                NULL            AS istaxexempt,
                NULL            AS c_tax_name,
                NULL            AS parent_tax_id,
                NULL            AS rate,
                NULL            AS requirestaxcertificate,
                NULL            AS sopotype,
                NULL            AS to_country_id,
                NULL            AS to_region_id,
                NULL            AS validfrom,
                NULL            AS m_product_ad_org_id,
                NULL            AS classification,
                NULL            AS m_product_copyfrom,
                NULL            AS m_product_created,
                NULL            AS m_product_createdby,
                NULL            AS c_revenuerecognition_id,
                NULL            AS c_subscriptiontype_id,
                NULL            AS m_product_c_taxcategory_id,
                NULL            AS descriptionurl,
                NULL            AS discontinued,
                NULL            AS discontinuedat,
                NULL            AS group1,
                NULL            AS group2,
                NULL            AS guaranteedays,
                NULL            AS guaranteedaysmin,
                NULL            AS help,
                NULL            AS m_product_isactive,
                NULL            AS isbom,
                NULL            AS isdropship,
                NULL            AS isexcludeautodelivery,
                NULL            AS isinvoiceprintdetails,
                NULL            AS ispicklistprintdetails,
                NULL            AS ispurchased,
                NULL            AS m_product_isselfservice,
                NULL            AS issold,
                NULL            AS isstocked,
                NULL            AS m_product_issummary,
                NULL            AS isverified,
                NULL            AS iswebstorefeatured,
                NULL            AS lowlevel,
                NULL            AS m_product_m_attributeset_id,
                NULL            AS m_product_m_asi_id,
                NULL            AS m_freightcategory_id,
                NULL            AS m_locator_id,
                NULL            AS m_product_m_prod_category_id,
                NULL            AS m_product_processing,
                NULL            AS producttype,
                NULL            AS r_mailtext_id,
                NULL            AS m_product_salesrep_id,
                NULL            AS s_expensetype_id,
                NULL            AS shelfdepth,
                NULL            AS shelfheight,
                NULL            AS shelfwidth,
                NULL            AS m_product_s_resource_id,
                NULL            AS unitsperpack,
                NULL            AS unitsperpallet,
                NULL            AS m_product_updated,
                NULL            AS m_product_updatedby,
                NULL            AS versionno,
                NULL            AS volume,
                NULL            AS weight,
                NULL            AS c_charge_ad_org_id,
                NULL            AS c_charge_c_bpartner_id,
                NULL            AS c_chargetype_id,
                NULL            AS c_charge_chargeamt,
                NULL            AS c_charge_c_taxcategory_id,
                NULL            AS c_charge_description,
                NULL            AS c_charge_isactive,
                NULL            AS issamecurrency,
                NULL            AS issametax,
                NULL            AS c_charge_istaxincluded,
                NULL            AS c_bp_product_ad_org_id,
                NULL            AS c_bp_product_c_bpartner_id,
                NULL            AS c_bp_product_created,
                NULL            AS c_bp_product_createdby,
                NULL            AS c_bp_product_description,
                NULL            AS c_bp_product_isactive,
                NULL            AS ismanufacturer,
                NULL            AS manufacturer,
                NULL            AS qualityrating,
                NULL            AS shelflifemindays,
                NULL            AS shelflifeminpct,
                NULL            AS c_bp_product_updated,
                NULL            AS c_bp_product_updatedby,
                NULL            AS vendorcategory,
                NULL            AS s_rassignment_ad_org_id,
                NULL            AS assigndatefrom,
                NULL            AS assigndateto,
                NULL            AS s_rassignment_created,
                NULL            AS s_rassignment_createby,
                NULL            AS s_rassignment_isactive,
                NULL            AS isconfirmed,
                NULL            AS s_rassignment_name,
                NULL            AS s_rassignment_qty,
                NULL            AS s_rassignment_s_resource_id,
                NULL            AS s_rassignment_updated,
                NULL            AS s_rassignment_updatedby,
                NULL            AS m_asi_ad_org_id,
                NULL            AS m_asi_created,
                NULL            AS m_asi_createdby,
                NULL            AS m_asi_description,
                NULL            AS m_asi_isactive,
                NULL            AS m_asi_updated,
                NULL            AS m_asi_updatedby 
            FROM c_invoice i
            )
UNION 
SELECT /* tax lines */ it.ad_client_id,
    it.ad_org_id,
    it.isactive,
    it.created,
    it.createdby,
    it.updated,
    it.updatedby,
    'en_US' AS ad_language,
    it.c_invoice_id,
    NULL    AS c_invoiceline_id,
    it.c_tax_id,
    NULL    AS taxamt,
    NULL    AS linetotalamt,
    t.taxindicator,
    999999  AS line,
    NULL    AS m_product_id,
    NULL    AS qtyinvoiced,
    NULL    AS qtyentered,
    NULL    AS uomsymbol,
    t.name,
    NULL    AS description,
    NULL    AS documentnote,
    NULL    AS upc,
    NULL    AS sku,
    NULL    AS productvalue,
    NULL    AS resourcedescription,
    NULL    AS pricelist,
    NULL    AS priceenteredlist,
    NULL    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 NULL ELSE it.taxamt END AS linenetamt,
    NULL    AS m_attributesetinstance_id,
    NULL    AS m_attributeset_id,
    NULL    AS serno,
    NULL    AS lot,
    NULL    AS m_lot_id,
    NULL    AS guaranteedate,
    NULL    AS productdescription,
    NULL    AS imageurl,
    NULL    AS c_campaign_id,
    NULL    AS c_project_id,
    NULL    AS c_activity_id,
    NULL    AS c_projectphase_id,
    NULL    AS c_projecttask_id,
    NULL    AS c_invoiceline_ad_orgtrx_id,
    NULL    AS a_processed,
    NULL    AS c_charge_id,
    NULL    AS c_orderline_id,
    NULL    AS c_uom_id,
    NULL    AS isdescription,
    NULL    AS c_invoiceline_isprinted,
    NULL    AS m_inoutline_id,
    NULL    AS m_rmaline_id,
    NULL    AS pricelimit,
    NULL    AS c_invoiceline_processed,
    NULL    AS ref_invoiceline_id,
    NULL    AS rramt,
    NULL    AS rrstartdate,
    NULL    AS s_resourceassignment_id,
    NULL    AS c_invoiceline_user1_id,
    NULL    AS c_invoiceline_user2_id,
    NULL    AS c_uom_ad_org_id,
    NULL    AS costingprecision,
    NULL    AS c_uom_description,
    NULL    AS c_uom_isactive,
    NULL    AS c_uom_isdefault,
    NULL    AS c_uom_name,
    NULL    AS stdprecision,
    NULL    AS uomtype,
    NULL    AS x12de355,
    NULL    AS c_invoice_ad_org_id,
    NULL    AS c_invoice_ad_orgtrx_id,
    NULL    AS c_invoice_ad_user_id,
    NULL    AS c_invoice_c_activity_id,
    NULL    AS c_invoice_c_bpartner_id,
    NULL    AS c_bpartner_location_id,
    NULL    AS c_invoice_c_campaign_id,
    NULL    AS c_invoice_c_charge_id,
    NULL    AS c_conversiontype_id,
    NULL    AS c_currency_id,
    NULL    AS c_doctype_id,
    NULL    AS c_doctypetarget_id,
    NULL    AS c_dunninglevel_id,
    NULL    AS c_invoice_chargeamt,
    NULL    AS c_order_id,
    NULL    AS c_payment_id,
    NULL    AS c_paymentterm_id,
    NULL    AS c_invoice_c_project_id,
    NULL    AS c_invoice_created,
    NULL    AS c_invoice_createdby,
    NULL    AS dateacct,
    NULL    AS dateinvoiced,
    NULL    AS dateordered,
    NULL    AS dateprinted,
    NULL    AS c_invoice_description,
    NULL    AS docaction,
    NULL    AS docstatus,
    NULL    AS documentno,
    NULL    AS dunninggrace,
    NULL    AS generateto,
    NULL    AS grandtotal,
    NULL    AS invoicecollectiontype,
    NULL    AS c_invoice_isactive,
    NULL    AS isapproved,
    NULL    AS isdiscountprinted,
    NULL    AS isindispute,
    NULL    AS ispaid,
    NULL    AS ispayschedulevalid,
    NULL    AS c_invoice_isprinted,
    NULL    AS c_invoice_isselfservice,
    NULL    AS issotrx,
    NULL    AS c_invoice_istaxincluded,
    NULL    AS istransferred,
    NULL    AS m_pricelist_id,
    NULL    AS m_rma_id,
    NULL    AS paymentrule,
    NULL    AS poreference,
    NULL    AS posted,
    NULL    AS processedon,
    NULL    AS processing,
    NULL    AS ref_invoice_id,
    NULL    AS reversal_id,
    NULL    AS salesrep_id,
    NULL    AS sendemail,
    NULL    AS totallines,
    NULL    AS c_invoice_updated,
    NULL    AS c_invoice_updatedby,
    NULL    AS c_invoice_user1_id,
    NULL    AS c_invoice_user2_id,
    NULL    AS c_tax_ad_org_id,
    NULL    AS ad_rule_id,
    NULL    AS c_country_id,
    NULL    AS c_region_id,
    NULL    AS c_tax_c_taxcategory_id,
    NULL    AS c_tax_description,
    NULL    AS c_tax_isactive,
    NULL    AS c_tax_isdefault,
    NULL    AS isdocumentlevel,
    NULL    AS issalestax,
    NULL    AS c_tax_issummary,
    NULL    AS istaxexempt,
    NULL    AS c_tax_name,
    NULL    AS parent_tax_id,
    NULL    AS rate,
    NULL    AS requirestaxcertificate,
    NULL    AS sopotype,
    NULL    AS to_country_id,
    NULL    AS to_region_id,
    NULL    AS validfrom,
    NULL    AS m_product_ad_org_id,
    NULL    AS classification,
    NULL    AS m_product_copyfrom,
    NULL    AS m_product_created,
    NULL    AS m_product_createdby,
    NULL    AS c_revenuerecognition_id,
    NULL    AS c_subscriptiontype_id,
    NULL    AS m_product_c_taxcategory_id,
    NULL    AS descriptionurl,
    NULL    AS discontinued,
    NULL    AS discontinuedat,
    NULL    AS group1,
    NULL    AS group2,
    NULL    AS guaranteedays,
    NULL    AS guaranteedaysmin,
    NULL    AS help,
    NULL    AS m_product_isactive,
    NULL    AS isbom,
    NULL    AS isdropship,
    NULL    AS isexcludeautodelivery,
    NULL    AS isinvoiceprintdetails,
    NULL    AS ispicklistprintdetails,
    NULL    AS ispurchased,
    NULL    AS m_product_isselfservice,
    NULL    AS issold,
    NULL    AS isstocked,
    NULL    AS m_product_issummary,
    NULL    AS isverified,
    NULL    AS iswebstorefeatured,
    NULL    AS lowlevel,
    NULL    AS m_product_m_attributeset_id,
    NULL    AS m_product_m_asi_id,
    NULL    AS m_freightcategory_id,
    NULL    AS m_locator_id,
    NULL    AS m_product_m_prod_category_id,
    NULL    AS m_product_processing,
    NULL    AS producttype,
    NULL    AS r_mailtext_id,
    NULL    AS m_product_salesrep_id,
    NULL    AS s_expensetype_id,
    NULL    AS shelfdepth,
    NULL    AS shelfheight,
    NULL    AS shelfwidth,
    NULL    AS m_product_s_resource_id,
    NULL    AS unitsperpack,
    NULL    AS unitsperpallet,
    NULL    AS m_product_updated,
    NULL    AS m_product_updatedby,
    NULL    AS versionno,
    NULL    AS volume,
    NULL    AS weight,
    NULL    AS c_charge_ad_org_id,
    NULL    AS c_charge_c_bpartner_id,
    NULL    AS c_chargetype_id,
    NULL    AS c_charge_chargeamt,
    NULL    AS c_charge_c_taxcategory_id,
    NULL    AS c_charge_description,
    NULL    AS c_charge_isactive,
    NULL    AS issamecurrency,
    NULL    AS issametax,
    NULL    AS c_charge_istaxincluded,
    NULL    AS c_bp_product_ad_org_id,
    NULL    AS c_bp_product_c_bpartner_id,
    NULL    AS c_bp_product_created,
    NULL    AS c_bp_product_createdby,
    NULL    AS c_bp_product_description,
    NULL    AS c_bp_product_isactive,
    NULL    AS ismanufacturer,
    NULL    AS manufacturer,
    NULL    AS qualityrating,
    NULL    AS shelflifemindays,
    NULL    AS shelflifeminpct,
    NULL    AS c_bp_product_updated,
    NULL    AS c_bp_product_updatedby,
    NULL    AS vendorcategory,
    NULL    AS s_rassignment_ad_org_id,
    NULL    AS assigndatefrom,
    NULL    AS assigndateto,
    NULL    AS s_rassignment_created,
    NULL    AS s_rassignment_createby,
    NULL    AS s_rassignment_isactive,
    NULL    AS isconfirmed,
    NULL    AS s_rassignment_name,
    NULL    AS s_rassignment_qty,
    NULL    AS s_rassignment_s_resource_id,
    NULL    AS s_rassignment_updated,
    NULL    AS s_rassignment_updatedby,
    NULL    AS m_asi_ad_org_id,
    NULL    AS m_asi_created,
    NULL    AS m_asi_createdby,
    NULL    AS m_asi_description,
    NULL    AS m_asi_isactive,
    NULL    AS m_asi_updated,
    NULL    AS m_asi_updatedby 
FROM c_invoicetax it 
        JOIN c_tax t 
        ON it.c_tax_id = t.c_tax_id
;
DROP VIEW c_invoice_linetax_vt;

CREATE OR REPLACE VIEW c_invoice_linetax_vt AS 
    (
        ( ( SELECT il.ad_client_id,
                il.ad_org_id,
                il.isactive,
                il.created,
                il.createdby,
                il.updated,
                il.updatedby,
                uomt.ad_language,
                il.c_invoice_id,
                il.c_invoiceline_id,
                il.c_tax_id,
                il.taxamt,
                il.linetotalamt,
                tt.taxindicator,
                il.line,
                pt.m_product_id,
                CASE WHEN il.qtyinvoiced <> 0 OR il.m_product_id IS NOT NULL THEN il.qtyinvoiced ELSE NULL END                               AS qtyinvoiced,
                CASE WHEN il.qtyentered <> 0 OR il.m_product_id IS NOT NULL THEN il.qtyentered ELSE NULL END                               AS qtyentered,
                CASE WHEN il.qtyentered <> 0 OR il.m_product_id IS NOT NULL THEN uomt.uomsymbol ELSE NULL END                               AS uomsymbol,
                COALESCE(ct.name, (pt.name || COALESCE(productattribute(il.m_attributesetinstance_id), '')), il.description) AS name,
                CASE WHEN COALESCE(ct.name, pt.name) IS NOT NULL THEN il.description ELSE NULL END                               AS description,
                pt.documentnote,
                p.upc,
                p.sku,
                COALESCE(pp.vendorproductno, p.value) AS productvalue,
                ra.description                        AS resourcedescription,
                CASE WHEN i.isdiscountprinted = 'Y' AND il.pricelist <> 0 THEN il.pricelist ELSE NULL END                               AS pricelist,
                CASE WHEN i.isdiscountprinted = 'Y' AND il.pricelist <> 0 AND il.qtyentered <> 0 THEN il.pricelist * il.qtyinvoiced / il.qtyentered ELSE NULL 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 ELSE NULL END                               
                AS discount,
                CASE WHEN il.priceactual <> 0 OR il.m_product_id IS NOT NULL THEN il.priceactual ELSE NULL END                               AS priceactual,
                CASE WHEN il.priceentered <> 0 OR il.m_product_id IS NOT NULL THEN il.priceentered ELSE NULL END                               AS priceentered,
                CASE WHEN il.linenetamt <> 0 OR il.m_product_id IS NOT NULL THEN il.linenetamt ELSE NULL END                               AS linenetamt,
                il.m_attributesetinstance_id,
                asi.m_attributeset_id,
                asi.serno,
                asi.lot,
                asi.m_lot_id,
                asi.guaranteedate,
                pt.description                        AS productdescription,
                p.imageurl,
                il.c_campaign_id,
                il.c_project_id,
                il.c_activity_id,
                il.c_projectphase_id,
                il.c_projecttask_id,
                il.ad_orgtrx_id                       AS c_invoiceline_ad_orgtrx_id,
                il.a_processed,
                il.c_charge_id,
                il.c_orderline_id,
                il.c_uom_id,
                il.isdescription,
                il.isprinted                          AS c_invoiceline_isprinted,
                il.m_inoutline_id,
                il.m_rmaline_id,
                il.pricelimit,
                il.processed                          AS c_invoiceline_processed,
                il.ref_invoiceline_id,
                il.rramt,
                il.rrstartdate,
                il.s_resourceassignment_id,
                il.user1_id                           AS c_invoiceline_user1_id,
                il.user2_id                           AS c_invoiceline_user2_id,
                uomt.ad_org_id                        AS c_uom_ad_org_id,
                uom.costingprecision,
                uomt.description                      AS c_uom_description,
                uomt.isactive                         AS c_uom_isactive,
                uom.isdefault                         AS c_uom_isdefault,
                uomt.name                             AS c_uom_name,
                uom.stdprecision,
                uom.uomtype,
                uom.x12de355,
                i.ad_org_id                           AS c_invoice_ad_org_id,
                i.ad_orgtrx_id                        AS c_invoice_ad_orgtrx_id,
                i.ad_user_id                          AS c_invoice_ad_user_id,
                i.c_activity_id                       AS c_invoice_c_activity_id,
                i.c_bpartner_id                       AS c_invoice_c_bpartner_id,
                i.c_bpartner_location_id,
                i.c_campaign_id                       AS c_invoice_c_campaign_id,
                i.c_charge_id                         AS c_invoice_c_charge_id,
                i.c_conversiontype_id,
                i.c_currency_id,
                i.c_doctype_id,
                i.c_doctypetarget_id,
                i.c_dunninglevel_id,
                i.chargeamt                           AS c_invoice_chargeamt,
                i.c_order_id,
                i.c_payment_id,
                i.c_paymentterm_id,
                i.c_project_id                        AS c_invoice_c_project_id,
                i.created                             AS c_invoice_created,
                i.createdby                           AS c_invoice_createdby,
                i.dateacct,
                i.dateinvoiced,
                i.dateordered,
                i.dateprinted,
                i.description                         AS c_invoice_description,
                i.docaction,
                i.docstatus,
                i.documentno,
                i.dunninggrace,
                i.generateto,
                i.grandtotal,
                i.invoicecollectiontype,
                i.isactive                            AS c_invoice_isactive,
                i.isapproved,
                i.isdiscountprinted,
                i.isindispute,
                i.ispaid,
                i.ispayschedulevalid,
                i.isprinted                           AS c_invoice_isprinted,
                i.isselfservice                       AS c_invoice_isselfservice,
                i.issotrx,
                i.istaxincluded                       AS c_invoice_istaxincluded,
                i.istransferred,
                i.m_pricelist_id,
                i.m_rma_id,
                i.paymentrule,
                i.poreference,
                i.posted,
                i.processedon,
                i.processing,
                i.ref_invoice_id,
                i.reversal_id,
                i.salesrep_id,
                i.sendemail,
                i.totallines,
                i.updated                             AS c_invoice_updated,
                i.updatedby                           AS c_invoice_updatedby,
                i.user1_id                            AS c_invoice_user1_id,
                i.user2_id                            AS c_invoice_user2_id,
                tt.ad_org_id                          AS c_tax_ad_org_id,
                t.ad_rule_id,
                t.c_country_id,
                t.c_region_id,
                t.c_taxcategory_id                    AS c_tax_c_taxcategory_id,
                tt.description                        AS c_tax_description,
                tt.isactive                           AS c_tax_isactive,
                t.isdefault                           AS c_tax_isdefault,
                t.isdocumentlevel,
                t.issalestax,
                t.issummary                           AS c_tax_issummary,
                t.istaxexempt,
                tt.name                               AS c_tax_name,
                t.parent_tax_id,
                t.rate,
                t.requirestaxcertificate,
                t.sopotype,
                t.to_country_id,
                t.to_region_id,
                t.validfrom,
                pt.ad_org_id                          AS m_product_ad_org_id,
                p.classification,
                p.copyfrom                            AS m_product_copyfrom,
                pt.created                            AS m_product_created,
                pt.createdby                          AS m_product_createdby,
                p.c_revenuerecognition_id,
                p.c_subscriptiontype_id,
                p.c_taxcategory_id                    AS m_product_c_taxcategory_id,
                p.descriptionurl,
                p.discontinued,
                p.discontinuedat,
                p.group1,
                p.group2,
                p.guaranteedays,
                p.guaranteedaysmin,
                p.help,
                pt.isactive                           AS m_product_isactive,
                p.isbom,
                p.isdropship,
                p.isexcludeautodelivery,
                p.isinvoiceprintdetails,
                p.ispicklistprintdetails,
                p.ispurchased,
                p.isselfservice                       AS m_product_isselfservice,
                p.issold,
                p.isstocked,
                p.issummary                           AS m_product_issummary,
                p.isverified,
                p.iswebstorefeatured,
                p.lowlevel,
                p.m_attributeset_id                   AS m_product_m_attributeset_id,
                p.m_attributesetinstance_id           AS m_product_m_asi_id,
                p.m_freightcategory_id,
                p.m_locator_id,
                p.m_product_category_id               AS m_product_m_prod_category_id,
                p.processing                          AS m_product_processing,
                p.producttype,
                p.r_mailtext_id,
                p.salesrep_id                         AS m_product_salesrep_id,
                p.s_expensetype_id,
                p.shelfdepth,
                p.shelfheight,
                p.shelfwidth,
                p.s_resource_id                       AS m_product_s_resource_id,
                p.unitsperpack,
                p.unitsperpallet,
                pt.updated                            AS m_product_updated,
                pt.updatedby                          AS m_product_updatedby,
                p.versionno,
                p.volume,
                p.weight,
                ct.ad_org_id                          AS c_charge_ad_org_id,
                c.c_bpartner_id                       AS c_charge_c_bpartner_id,
                c.c_chargetype_id,
                c.chargeamt                           AS c_charge_chargeamt,
                c.c_taxcategory_id                    AS c_charge_c_taxcategory_id,
                ct.description                        AS c_charge_description,
                ct.isactive                           AS c_charge_isactive,
                c.issamecurrency,
                c.issametax,
                c.istaxincluded                       AS c_charge_istaxincluded,
                pp.ad_org_id                          AS c_bp_product_ad_org_id,
                pp.c_bpartner_id                      AS c_bp_product_c_bpartner_id,
                pp.created                            AS c_bp_product_created,
                pp.createdby                          AS c_bp_product_createdby,
                pp.description                        AS c_bp_product_description,
                pp.isactive                           AS c_bp_product_isactive,
                pp.ismanufacturer,
                pp.manufacturer,
                pp.qualityrating,
                pp.shelflifemindays,
                pp.shelflifeminpct,
                pp.updated                            AS c_bp_product_updated,
                pp.updatedby                          AS c_bp_product_updatedby,
                pp.vendorcategory,
                ra.ad_org_id                          AS s_rassignment_ad_org_id,
                ra.assigndatefrom,
                ra.assigndateto,
                ra.created                            AS s_rassignment_created,
                ra.createdby                          AS s_rassignment_createby,
                ra.isactive                           AS s_rassignment_isactive,
                ra.isconfirmed,
                ra.name                               AS s_rassignment_name,
                ra.qty                                AS s_rassignment_qty,
                ra.s_resource_id                      AS s_rassignment_s_resource_id,
                ra.updated                            AS s_rassignment_updated,
                ra.updatedby                          AS s_rassignment_updatedby,
                asi.ad_org_id                         AS m_asi_ad_org_id,
                asi.created                           AS m_asi_created,
                asi.createdby                         AS m_asi_createdby,
                asi.description                       AS m_asi_description,
                asi.isactive                          AS m_asi_isactive,
                asi.updated                           AS m_asi_updated,
                asi.updatedby                         AS m_asi_updatedby 
            FROM c_invoiceline il 
                JOIN c_uom uom 
                ON il.c_uom_id = uom.c_uom_id 
                JOIN c_uom_trl uomt 
                ON il.c_uom_id = uomt.c_uom_id 
                JOIN c_invoice i 
                ON il.c_invoice_id = i.c_invoice_id 
                LEFT JOIN c_tax t 
                ON il.c_tax_id = t.c_tax_id 
                LEFT JOIN c_tax_trl tt 
                ON il.c_tax_id = tt.c_tax_id AND uomt.ad_language = tt.ad_language 
                LEFT JOIN m_product p 
                ON il.m_product_id = p.m_product_id 
                LEFT JOIN c_charge c 
                ON il.c_charge_id = c.c_charge_id 
                LEFT JOIN c_charge_trl ct 
                ON il.c_charge_id = ct.c_charge_id 
                LEFT JOIN c_bpartner_product pp 
                ON il.m_product_id = pp.m_product_id AND i.c_bpartner_id = pp.c_bpartner_id 
                LEFT JOIN m_product_trl pt 
                ON il.m_product_id = pt.m_product_id AND uomt.ad_language = pt.ad_language 
                LEFT JOIN s_resourceassignment ra 
                ON il.s_resourceassignment_id = ra.s_resourceassignment_id 
                LEFT JOIN m_attributesetinstance asi 
                ON il.m_attributesetinstance_id = asi.m_attributesetinstance_id 
                UNION /* BOM lines */ SELECT il.ad_client_id,
                        il.ad_org_id,
                        il.isactive,
                        il.created,
                        il.createdby,
                        il.updated,
                        il.updatedby,
                        uomt.ad_language,
                        il.c_invoice_id,
                        il.c_invoiceline_id,
                        il.c_tax_id,
                        il.taxamt,
                        il.linetotalamt,
                        tt.taxindicator,
                        il.line + bl.line / 100                   AS line,
                        pt.m_product_id,
                        CASE WHEN bl.isqtypercentage = 'N' THEN il.qtyinvoiced * bl.qtybom ELSE il.qtyinvoiced * (bl.qtybatch / 100) END                                   AS qtyinvoiced,
                        CASE WHEN bl.isqtypercentage = 'N' THEN il.qtyentered * bl.qtybom ELSE il.qtyentered * (bl.qtybatch / 100) END                                   AS qtyentered,
                        uomt.uomsymbol,
                        COALESCE(pt.name, p.name)                 AS name,
                        b.description,
                        COALESCE(pt.documentnote, p.documentnote) AS documentnote,
                        p.upc,
                        p.sku,
                        p.value                                   AS productvalue,
                        NULL                                      AS resourcedescription,
                        NULL                                      AS pricelist,
                        NULL                                      AS priceenteredlist,
                        NULL                                      AS discount,
                        NULL                                      AS priceactual,
                        NULL                                      AS priceentered,
                        NULL                                      AS linenetamt,
                        il.m_attributesetinstance_id,
                        asi.m_attributeset_id,
                        asi.serno,
                        asi.lot,
                        asi.m_lot_id,
                        asi.guaranteedate,
                        pt.description                            AS productdescription,
                        p.imageurl,
                        il.c_campaign_id,
                        il.c_project_id,
                        il.c_activity_id,
                        il.c_projectphase_id,
                        il.c_projecttask_id,
                        il.ad_orgtrx_id                           AS c_invoiceline_ad_orgtrx_id,
                        il.a_processed,
                        il.c_charge_id,
                        il.c_orderline_id,
                        il.c_uom_id,
                        il.isdescription,
                        il.isprinted                              AS c_invoiceline_isprinted,
                        il.m_inoutline_id,
                        il.m_rmaline_id,
                        il.pricelimit,
                        il.processed                              AS c_invoiceline_processed,
                        il.ref_invoiceline_id,
                        il.rramt,
                        il.rrstartdate,
                        il.s_resourceassignment_id,
                        il.user1_id                               AS c_invoiceline_user1_id,
                        il.user2_id                               AS c_invoiceline_user2_id,
                        uomt.ad_org_id                            AS c_uom_ad_org_id,
                        uom.costingprecision,
                        uomt.description                          AS c_uom_description,
                        uomt.isactive                             AS c_uom_isactive,
                        uom.isdefault                             AS c_uom_isdefault,
                        uomt.name                                 AS c_uom_name,
                        uom.stdprecision,
                        uom.uomtype,
                        uom.x12de355,
                        NULL                                      AS c_invoice_ad_org_id,
                        NULL                                      AS c_invoice_ad_orgtrx_id,
                        NULL                                      AS c_invoice_ad_user_id,
                        NULL                                      AS c_invoice_c_activity_id,
                        NULL                                      AS c_invoice_c_bpartner_id,
                        NULL                                      AS c_bpartner_location_id,
                        NULL                                      AS c_invoice_c_campaign_id,
                        NULL                                      AS c_invoice_c_charge_id,
                        NULL                                      AS c_conversiontype_id,
                        NULL                                      AS c_currency_id,
                        NULL                                      AS c_doctype_id,
                        NULL                                      AS c_doctypetarget_id,
                        NULL                                      AS c_dunninglevel_id,
                        NULL                                      AS c_invoice_chargeamt,
                        NULL                                      AS c_order_id,
                        NULL                                      AS c_payment_id,
                        NULL                                      AS c_paymentterm_id,
                        NULL                                      AS c_invoice_c_project_id,
                        NULL                                      AS c_invoice_created,
                        NULL                                      AS c_invoice_createdby,
                        NULL                                      AS dateacct,
                        NULL                                      AS dateinvoiced,
                        NULL                                      AS dateordered,
                        NULL                                      AS dateprinted,
                        NULL                                      AS c_invoice_description,
                        NULL                                      AS docaction,
                        NULL                                      AS docstatus,
                        NULL                                      AS documentno,
                        NULL                                      AS dunninggrace,
                        NULL                                      AS generateto,
                        NULL                                      AS grandtotal,
                        NULL                                      AS invoicecollectiontype,
                        NULL                                      AS c_invoice_isactive,
                        NULL                                      AS isapproved,
                        NULL                                      AS isdiscountprinted,
                        NULL                                      AS isindispute,
                        NULL                                      AS ispaid,
                        NULL                                      AS ispayschedulevalid,
                        NULL                                      AS c_invoice_isprinted,
                        NULL                                      AS c_invoice_isselfservice,
                        NULL                                      AS issotrx,
                        NULL                                      AS c_invoice_istaxincluded,
                        NULL                                      AS istransferred,
                        NULL                                      AS m_pricelist_id,
                        NULL                                      AS m_rma_id,
                        NULL                                      AS paymentrule,
                        NULL                                      AS poreference,
                        NULL                                      AS posted,
                        NULL                                      AS processedon,
                        NULL                                      AS processing,
                        NULL                                      AS ref_invoice_id,
                        NULL                                      AS reversal_id,
                        NULL                                      AS salesrep_id,
                        NULL                                      AS sendemail,
                        NULL                                      AS totallines,
                        NULL                                      AS c_invoice_updated,
                        NULL                                      AS c_invoice_updatedby,
                        NULL                                      AS c_invoice_user1_id,
                        NULL                                      AS c_invoice_user2_id,
                        tt.ad_org_id                              AS c_tax_ad_org_id,
                        t.ad_rule_id,
                        t.c_country_id,
                        t.c_region_id,
                        t.c_taxcategory_id                        AS c_tax_c_taxcategory_id,
                        tt.description                            AS c_tax_description,
                        tt.isactive                               AS c_tax_isactive,
                        t.isdefault                               AS c_tax_isdefault,
                        t.isdocumentlevel,
                        t.issalestax,
                        t.issummary                               AS c_tax_issummary,
                        t.istaxexempt,
                        tt.name                                   AS c_tax_name,
                        t.parent_tax_id,
                        t.rate,
                        t.requirestaxcertificate,
                        t.sopotype,
                        t.to_country_id,
                        t.to_region_id,
                        t.validfrom,
                        pt.ad_org_id                              AS m_product_ad_org_id,
                        p.classification,
                        p.copyfrom                                AS m_product_copyfrom,
                        pt.created                                AS m_product_created,
                        pt.createdby                              AS m_product_createdby,
                        p.c_revenuerecognition_id,
                        p.c_subscriptiontype_id,
                        p.c_taxcategory_id                        AS m_product_c_taxcategory_id,
                        p.descriptionurl,
                        p.discontinued,
                        p.discontinuedat,
                        p.group1,
                        p.group2,
                        p.guaranteedays,
                        p.guaranteedaysmin,
                        p.help,
                        pt.isactive                               AS m_product_isactive,
                        p.isbom,
                        p.isdropship,
                        p.isexcludeautodelivery,
                        p.isinvoiceprintdetails,
                        p.ispicklistprintdetails,
                        p.ispurchased,
                        p.isselfservice                           AS m_product_isselfservice,
                        p.issold,
                        p.isstocked,
                        p.issummary                               AS m_product_issummary,
                        p.isverified,
                        p.iswebstorefeatured,
                        p.lowlevel,
                        p.m_attributeset_id                       AS m_product_m_attributeset_id,
                        p.m_attributesetinstance_id               AS m_product_m_asi_id,
                        p.m_freightcategory_id,
                        p.m_locator_id,
                        p.m_product_category_id                   AS m_product_m_prod_category_id,
                        p.processing                              AS m_product_processing,
                        p.producttype,
                        p.r_mailtext_id,
                        p.salesrep_id                             AS m_product_salesrep_id,
                        p.s_expensetype_id,
                        p.shelfdepth,
                        p.shelfheight,
                        p.shelfwidth,
                        p.s_resource_id                           AS m_product_s_resource_id,
                        p.unitsperpack,
                        p.unitsperpallet,
                        p.updated                                 AS m_product_updated,
                        p.updatedby                               AS m_product_updatedby,
                        p.versionno,
                        p.volume,
                        p.weight,
                        NULL                                      AS c_charge_ad_org_id,
                        NULL                                      AS c_charge_c_bpartner_id,
                        NULL                                      AS c_chargetype_id,
                        NULL                                      AS c_charge_chargeamt,
                        NULL                                      AS c_charge_c_taxcategory_id,
                        NULL                                      AS c_charge_description,
                        NULL                                      AS c_charge_isactive,
                        NULL                                      AS issamecurrency,
                        NULL                                      AS issametax,
                        NULL                                      AS c_charge_istaxincluded,
                        NULL                                      AS c_bp_product_ad_org_id,
                        NULL                                      AS c_bp_product_c_bpartner_id,
                        NULL                                      AS c_bp_product_created,
                        NULL                                      AS c_bp_product_createdby,
                        NULL                                      AS c_bp_product_description,
                        NULL                                      AS c_bp_product_isactive,
                        NULL                                      AS ismanufacturer,
                        NULL                                      AS manufacturer,
                        NULL                                      AS qualityrating,
                        NULL                                      AS shelflifemindays,
                        NULL                                      AS shelflifeminpct,
                        NULL                                      AS c_bp_product_updated,
                        NULL                                      AS c_bp_product_updatedby,
                        NULL                                      AS vendorcategory,
                        NULL                                      AS s_rassignment_ad_org_id,
                        NULL                                      AS assigndatefrom,
                        NULL                                      AS assigndateto,
                        NULL                                      AS s_rassignment_created,
                        NULL                                      AS s_rassignment_createby,
                        NULL                                      AS s_rassignment_isactive,
                        NULL                                      AS isconfirmed,
                        NULL                                      AS s_rassignment_name,
                        NULL                                      AS s_rassignment_qty,
                        NULL                                      AS s_rassignment_s_resource_id,
                        NULL                                      AS s_rassignment_updated,
                        NULL                                      AS s_rassignment_updatedby,
                        asi.ad_org_id                             AS m_asi_ad_org_id,
                        asi.created                               AS m_asi_created,
                        asi.createdby                             AS m_asi_createdby,
                        asi.description                           AS m_asi_description,
                        asi.isactive                              AS m_asi_isactive,
                        asi.updated                               AS m_asi_updated,
                        asi.updatedby                             AS m_asi_updatedby 
                    FROM pp_product_bom b 
                        JOIN c_invoiceline il 
                        ON b.m_product_id = il.m_product_id 
                        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' 
                        JOIN pp_product_bomline bl 
                        ON bl.pp_product_bom_id = b.pp_product_bom_id 
                        JOIN m_product p 
                        ON bl.m_product_id = p.m_product_id 
                        JOIN c_uom uom 
                        ON p.c_uom_id = uom.c_uom_id 
                        JOIN c_uom_trl uomt 
                        ON p.c_uom_id = uomt.c_uom_id 
                        JOIN m_product_trl pt 
                        ON bl.m_product_id = pt.m_product_id AND uomt.ad_language = pt.ad_language 
                        LEFT JOIN c_tax t 
                        ON il.c_tax_id = t.c_tax_id 
                        LEFT JOIN c_tax_trl tt 
                        ON il.c_tax_id = tt.c_tax_id AND uomt.ad_language = tt.ad_language 
                        LEFT JOIN m_attributesetinstance asi 
                        ON il.m_attributesetinstance_id = asi.m_attributesetinstance_id) 
        UNION   /* comment lines */ SELECT il.ad_client_id,
                    il.ad_org_id,
                    il.isactive,
                    il.created,
                    il.createdby,
                    il.updated,
                    il.updatedby,
                    l.ad_language,
                    il.c_invoice_id,
                    il.c_invoiceline_id,
                    NULL            AS c_tax_id,
                    NULL            AS taxamt,
                    NULL            AS linetotalamt,
                    NULL            AS taxindicator,
                    il.line,
                    NULL            AS m_product_id,
                    NULL            AS qtyinvoiced,
                    NULL            AS qtyentered,
                    NULL            AS uomsymbol,
                    il.description  AS name,
                    NULL            AS description,
                    NULL            AS documentnote,
                    NULL            AS upc,
                    NULL            AS sku,
                    NULL            AS productvalue,
                    NULL            AS resourcedescription,
                    NULL            AS pricelist,
                    NULL            AS priceenteredlist,
                    NULL            AS discount,
                    NULL            AS priceactual,
                    NULL            AS priceentered,
                    NULL            AS linenetamt,
                    NULL            AS m_attributesetinstance_id,
                    NULL            AS m_attributeset_id,
                    NULL            AS serno,
                    NULL            AS lot,
                    NULL            AS m_lot_id,
                    NULL            AS guaranteedate,
                    NULL            AS productdescription,
                    NULL            AS imageurl,
                    NULL            AS c_campaign_id,
                    NULL            AS c_project_id,
                    NULL            AS c_activity_id,
                    NULL            AS c_projectphase_id,
                    NULL            AS c_projecttask_id,
                    NULL            AS c_invoiceline_ad_orgtrx_id,
                    NULL            AS a_processed,
                    NULL            AS c_charge_id,
                    NULL            AS c_orderline_id,
                    NULL            AS c_uom_id,
                    NULL            AS isdescription,
                    NULL            AS c_invoiceline_isprinted,
                    NULL            AS m_inoutline_id,
                    NULL            AS m_rmaline_id,
                    NULL            AS pricelimit,
                    NULL            AS c_invoiceline_processed,
                    NULL            AS ref_invoiceline_id,
                    NULL            AS rramt,
                    NULL            AS rrstartdate,
                    NULL            AS s_resourceassignment_id,
                    NULL            AS c_invoiceline_user1_id,
                    NULL            AS c_invoiceline_user2_id,
                    NULL            AS c_uom_ad_org_id,
                    NULL            AS costingprecision,
                    NULL            AS c_uom_description,
                    NULL            AS c_uom_isactive,
                    NULL            AS c_uom_isdefault,
                    NULL            AS c_uom_name,
                    NULL            AS stdprecision,
                    NULL            AS uomtype,
                    NULL            AS x12de355,
                    NULL            AS c_invoice_ad_org_id,
                    NULL            AS c_invoice_ad_orgtrx_id,
                    NULL            AS c_invoice_ad_user_id,
                    NULL            AS c_invoice_c_activity_id,
                    NULL            AS c_invoice_c_bpartner_id,
                    NULL            AS c_bpartner_location_id,
                    NULL            AS c_invoice_c_campaign_id,
                    NULL            AS c_invoice_c_charge_id,
                    NULL            AS c_conversiontype_id,
                    NULL            AS c_currency_id,
                    NULL            AS c_doctype_id,
                    NULL            AS c_doctypetarget_id,
                    NULL            AS c_dunninglevel_id,
                    NULL            AS c_invoice_chargeamt,
                    NULL            AS c_order_id,
                    NULL            AS c_payment_id,
                    NULL            AS c_paymentterm_id,
                    NULL            AS c_invoice_c_project_id,
                    NULL            AS c_invoice_created,
                    NULL            AS c_invoice_createdby,
                    NULL            AS dateacct,
                    NULL            AS dateinvoiced,
                    NULL            AS dateordered,
                    NULL            AS dateprinted,
                    NULL            AS c_invoice_description,
                    NULL            AS docaction,
                    NULL            AS docstatus,
                    NULL            AS documentno,
                    NULL            AS dunninggrace,
                    NULL            AS generateto,
                    NULL            AS grandtotal,
                    NULL            AS invoicecollectiontype,
                    NULL            AS c_invoice_isactive,
                    NULL            AS isapproved,
                    NULL            AS isdiscountprinted,
                    NULL            AS isindispute,
                    NULL            AS ispaid,
                    NULL            AS ispayschedulevalid,
                    NULL            AS c_invoice_isprinted,
                    NULL            AS c_invoice_isselfservice,
                    NULL            AS issotrx,
                    NULL            AS c_invoice_istaxincluded,
                    NULL            AS istransferred,
                    NULL            AS m_pricelist_id,
                    NULL            AS m_rma_id,
                    NULL            AS paymentrule,
                    NULL            AS poreference,
                    NULL            AS posted,
                    NULL            AS processedon,
                    NULL            AS processing,
                    NULL            AS ref_invoice_id,
                    NULL            AS reversal_id,
                    NULL            AS salesrep_id,
                    NULL            AS sendemail,
                    NULL            AS totallines,
                    NULL            AS c_invoice_updated,
                    NULL            AS c_invoice_updatedby,
                    NULL            AS c_invoice_user1_id,
                    NULL            AS c_invoice_user2_id,
                    NULL            AS c_tax_ad_org_id,
                    NULL            AS ad_rule_id,
                    NULL            AS c_country_id,
                    NULL            AS c_region_id,
                    NULL            AS c_tax_c_taxcategory_id,
                    NULL            AS c_tax_description,
                    NULL            AS c_tax_isactive,
                    NULL            AS c_tax_isdefault,
                    NULL            AS isdocumentlevel,
                    NULL            AS issalestax,
                    NULL            AS c_tax_issummary,
                    NULL            AS istaxexempt,
                    NULL            AS c_tax_name,
                    NULL            AS parent_tax_id,
                    NULL            AS rate,
                    NULL            AS requirestaxcertificate,
                    NULL            AS sopotype,
                    NULL            AS to_country_id,
                    NULL            AS to_region_id,
                    NULL            AS validfrom,
                    NULL            AS m_product_ad_org_id,
                    NULL            AS classification,
                    NULL            AS m_product_copyfrom,
                    NULL            AS m_product_created,
                    NULL            AS m_product_createdby,
                    NULL            AS c_revenuerecognition_id,
                    NULL            AS c_subscriptiontype_id,
                    NULL            AS m_product_c_taxcategory_id,
                    NULL            AS descriptionurl,
                    NULL            AS discontinued,
                    NULL            AS discontinuedat,
                    NULL            AS group1,
                    NULL            AS group2,
                    NULL            AS guaranteedays,
                    NULL            AS guaranteedaysmin,
                    NULL            AS help,
                    NULL            AS m_product_isactive,
                    NULL            AS isbom,
                    NULL            AS isdropship,
                    NULL            AS isexcludeautodelivery,
                    NULL            AS isinvoiceprintdetails,
                    NULL            AS ispicklistprintdetails,
                    NULL            AS ispurchased,
                    NULL            AS m_product_isselfservice,
                    NULL            AS issold,
                    NULL            AS isstocked,
                    NULL            AS m_product_issummary,
                    NULL            AS isverified,
                    NULL            AS iswebstorefeatured,
                    NULL            AS lowlevel,
                    NULL            AS m_product_m_attributeset_id,
                    NULL            AS m_product_m_asi_id,
                    NULL            AS m_freightcategory_id,
                    NULL            AS m_locator_id,
                    NULL            AS m_product_m_prod_category_id,
                    NULL            AS m_product_processing,
                    NULL            AS producttype,
                    NULL            AS r_mailtext_id,
                    NULL            AS m_product_salesrep_id,
                    NULL            AS s_expensetype_id,
                    NULL            AS shelfdepth,
                    NULL            AS shelfheight,
                    NULL            AS shelfwidth,
                    NULL            AS m_product_s_resource_id,
                    NULL            AS unitsperpack,
                    NULL            AS unitsperpallet,
                    NULL            AS m_product_updated,
                    NULL            AS m_product_updatedby,
                    NULL            AS versionno,
                    NULL            AS volume,
                    NULL            AS weight,
                    NULL            AS c_charge_ad_org_id,
                    NULL            AS c_charge_c_bpartner_id,
                    NULL            AS c_chargetype_id,
                    NULL            AS c_charge_chargeamt,
                    NULL            AS c_charge_c_taxcategory_id,
                    NULL            AS c_charge_description,
                    NULL            AS c_charge_isactive,
                    NULL            AS issamecurrency,
                    NULL            AS issametax,
                    NULL            AS c_charge_istaxincluded,
                    NULL            AS c_bp_product_ad_org_id,
                    NULL            AS c_bp_product_c_bpartner_id,
                    NULL            AS c_bp_product_created,
                    NULL            AS c_bp_product_createdby,
                    NULL            AS c_bp_product_description,
                    NULL            AS c_bp_product_isactive,
                    NULL            AS ismanufacturer,
                    NULL            AS manufacturer,
                    NULL            AS qualityrating,
                    NULL            AS shelflifemindays,
                    NULL            AS shelflifeminpct,
                    NULL            AS c_bp_product_updated,
                    NULL            AS c_bp_product_updatedby,
                    NULL            AS vendorcategory,
                    NULL            AS s_rassignment_ad_org_id,
                    NULL            AS assigndatefrom,
                    NULL            AS assigndateto,
                    NULL            AS s_rassignment_created,
                    NULL            AS s_rassignment_createby,
                    NULL            AS s_rassignment_isactive,
                    NULL            AS isconfirmed,
                    NULL            AS s_rassignment_name,
                    NULL            AS s_rassignment_qty,
                    NULL            AS s_rassignment_s_resource_id,
                    NULL            AS s_rassignment_updated,
                    NULL            AS s_rassignment_updatedby,
                    NULL            AS m_asi_ad_org_id,
                    NULL            AS m_asi_created,
                    NULL            AS m_asi_createdby,
                    NULL            AS m_asi_description,
                    NULL            AS m_asi_isactive,
                    NULL            AS m_asi_updated,
                    NULL            AS m_asi_updatedby 
                FROM c_invoiceline il,
                    ad_language l 
                WHERE il.c_uom_id IS NULL AND l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y') 
    UNION   /* empty line */ SELECT i.ad_client_id,
                i.ad_org_id,
                i.isactive,
                i.created,
                i.createdby,
                i.updated,
                i.updatedby,
                l.ad_language,
                i.c_invoice_id,
                NULL            AS c_invoiceline_id,
                NULL            AS c_tax_id,
                NULL            AS taxamt,
                NULL            AS linetotalamt,
                NULL            AS taxindicator,
                999998          AS line,
                NULL            AS m_product_id,
                NULL            AS qtyinvoiced,
                NULL            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,
                NULL            AS pricelist,
                NULL            AS priceenteredlist,
                NULL            AS discount,
                NULL            AS priceactual,
                NULL            AS priceentered,
                NULL            AS linenetamt,
                NULL            AS m_attributesetinstance_id,
                NULL            AS m_attributeset_id,
                NULL            AS serno,
                NULL            AS lot,
                NULL            AS m_lot_id,
                NULL            AS guaranteedate,
                NULL            AS productdescription,
                NULL            AS imageurl,
                NULL            AS c_campaign_id,
                NULL            AS c_project_id,
                NULL            AS c_activity_id,
                NULL            AS c_projectphase_id,
                NULL            AS c_projecttask_id,
                NULL            AS c_invoiceline_ad_orgtrx_id,
                NULL            AS a_processed,
                NULL            AS c_charge_id,
                NULL            AS c_orderline_id,
                NULL            AS c_uom_id,
                NULL            AS isdescription,
                NULL            AS c_invoiceline_isprinted,
                NULL            AS m_inoutline_id,
                NULL            AS m_rmaline_id,
                NULL            AS pricelimit,
                NULL            AS c_invoiceline_processed,
                NULL            AS ref_invoiceline_id,
                NULL            AS rramt,
                NULL            AS rrstartdate,
                NULL            AS s_resourceassignment_id,
                NULL            AS c_invoiceline_user1_id,
                NULL            AS c_invoiceline_user2_id,
                NULL            AS c_uom_ad_org_id,
                NULL            AS costingprecision,
                NULL            AS c_uom_description,
                NULL            AS c_uom_isactive,
                NULL            AS c_uom_isdefault,
                NULL            AS c_uom_name,
                NULL            AS stdprecision,
                NULL            AS uomtype,
                NULL            AS x12de355,
                NULL            AS c_invoice_ad_org_id,
                NULL            AS c_invoice_ad_orgtrx_id,
                NULL            AS c_invoice_ad_user_id,
                NULL            AS c_invoice_c_activity_id,
                NULL            AS c_invoice_c_bpartner_id,
                NULL            AS c_bpartner_location_id,
                NULL            AS c_invoice_c_campaign_id,
                NULL            AS c_invoice_c_charge_id,
                NULL            AS c_conversiontype_id,
                NULL            AS c_currency_id,
                NULL            AS c_doctype_id,
                NULL            AS c_doctypetarget_id,
                NULL            AS c_dunninglevel_id,
                NULL            AS c_invoice_chargeamt,
                NULL            AS c_order_id,
                NULL            AS c_payment_id,
                NULL            AS c_paymentterm_id,
                NULL            AS c_invoice_c_project_id,
                NULL            AS c_invoice_created,
                NULL            AS c_invoice_createdby,
                NULL            AS dateacct,
                NULL            AS dateinvoiced,
                NULL            AS dateordered,
                NULL            AS dateprinted,
                NULL            AS c_invoice_description,
                NULL            AS docaction,
                NULL            AS docstatus,
                NULL            AS documentno,
                NULL            AS dunninggrace,
                NULL            AS generateto,
                NULL            AS grandtotal,
                NULL            AS invoicecollectiontype,
                NULL            AS c_invoice_isactive,
                NULL            AS isapproved,
                NULL            AS isdiscountprinted,
                NULL            AS isindispute,
                NULL            AS ispaid,
                NULL            AS ispayschedulevalid,
                NULL            AS c_invoice_isprinted,
                NULL            AS c_invoice_isselfservice,
                NULL            AS issotrx,
                NULL            AS c_invoice_istaxincluded,
                NULL            AS istransferred,
                NULL            AS m_pricelist_id,
                NULL            AS m_rma_id,
                NULL            AS paymentrule,
                NULL            AS poreference,
                NULL            AS posted,
                NULL            AS processedon,
                NULL            AS processing,
                NULL            AS ref_invoice_id,
                NULL            AS reversal_id,
                NULL            AS salesrep_id,
                NULL            AS sendemail,
                NULL            AS totallines,
                NULL            AS c_invoice_updated,
                NULL            AS c_invoice_updatedby,
                NULL            AS c_invoice_user1_id,
                NULL            AS c_invoice_user2_id,
                NULL            AS c_tax_ad_org_id,
                NULL            AS ad_rule_id,
                NULL            AS c_country_id,
                NULL            AS c_region_id,
                NULL            AS c_tax_c_taxcategory_id,
                NULL            AS c_tax_description,
                NULL            AS c_tax_isactive,
                NULL            AS c_tax_isdefault,
                NULL            AS isdocumentlevel,
                NULL            AS issalestax,
                NULL            AS c_tax_issummary,
                NULL            AS istaxexempt,
                NULL            AS c_tax_name,
                NULL            AS parent_tax_id,
                NULL            AS rate,
                NULL            AS requirestaxcertificate,
                NULL            AS sopotype,
                NULL            AS to_country_id,
                NULL            AS to_region_id,
                NULL            AS validfrom,
                NULL            AS m_product_ad_org_id,
                NULL            AS classification,
                NULL            AS m_product_copyfrom,
                NULL            AS m_product_created,
                NULL            AS m_product_createdby,
                NULL            AS c_revenuerecognition_id,
                NULL            AS c_subscriptiontype_id,
                NULL            AS m_product_c_taxcategory_id,
                NULL            AS descriptionurl,
                NULL            AS discontinued,
                NULL            AS discontinuedat,
                NULL            AS group1,
                NULL            AS group2,
                NULL            AS guaranteedays,
                NULL            AS guaranteedaysmin,
                NULL            AS help,
                NULL            AS m_product_isactive,
                NULL            AS isbom,
                NULL            AS isdropship,
                NULL            AS isexcludeautodelivery,
                NULL            AS isinvoiceprintdetails,
                NULL            AS ispicklistprintdetails,
                NULL            AS ispurchased,
                NULL            AS m_product_isselfservice,
                NULL            AS issold,
                NULL            AS isstocked,
                NULL            AS m_product_issummary,
                NULL            AS isverified,
                NULL            AS iswebstorefeatured,
                NULL            AS lowlevel,
                NULL            AS m_product_m_attributeset_id,
                NULL            AS m_product_m_asi_id,
                NULL            AS m_freightcategory_id,
                NULL            AS m_locator_id,
                NULL            AS m_product_m_prod_category_id,
                NULL            AS m_product_processing,
                NULL            AS producttype,
                NULL            AS r_mailtext_id,
                NULL            AS m_product_salesrep_id,
                NULL            AS s_expensetype_id,
                NULL            AS shelfdepth,
                NULL            AS shelfheight,
                NULL            AS shelfwidth,
                NULL            AS m_product_s_resource_id,
                NULL            AS unitsperpack,
                NULL            AS unitsperpallet,
                NULL            AS m_product_updated,
                NULL            AS m_product_updatedby,
                NULL            AS versionno,
                NULL            AS volume,
                NULL            AS weight,
                NULL            AS c_charge_ad_org_id,
                NULL            AS c_charge_c_bpartner_id,
                NULL            AS c_chargetype_id,
                NULL            AS c_charge_chargeamt,
                NULL            AS c_charge_c_taxcategory_id,
                NULL            AS c_charge_description,
                NULL            AS c_charge_isactive,
                NULL            AS issamecurrency,
                NULL            AS issametax,
                NULL            AS c_charge_istaxincluded,
                NULL            AS c_bp_product_ad_org_id,
                NULL            AS c_bp_product_c_bpartner_id,
                NULL            AS c_bp_product_created,
                NULL            AS c_bp_product_createdby,
                NULL            AS c_bp_product_description,
                NULL            AS c_bp_product_isactive,
                NULL            AS ismanufacturer,
                NULL            AS manufacturer,
                NULL            AS qualityrating,
                NULL            AS shelflifemindays,
                NULL            AS shelflifeminpct,
                NULL            AS c_bp_product_updated,
                NULL            AS c_bp_product_updatedby,
                NULL            AS vendorcategory,
                NULL            AS s_rassignment_ad_org_id,
                NULL            AS assigndatefrom,
                NULL            AS assigndateto,
                NULL            AS s_rassignment_created,
                NULL            AS s_rassignment_createby,
                NULL            AS s_rassignment_isactive,
                NULL            AS isconfirmed,
                NULL            AS s_rassignment_name,
                NULL            AS s_rassignment_qty,
                NULL            AS s_rassignment_s_resource_id,
                NULL            AS s_rassignment_updated,
                NULL            AS s_rassignment_updatedby,
                NULL            AS m_asi_ad_org_id,
                NULL            AS m_asi_created,
                NULL            AS m_asi_createdby,
                NULL            AS m_asi_description,
                NULL            AS m_asi_isactive,
                NULL            AS m_asi_updated,
                NULL            AS m_asi_updatedby 
            FROM c_invoice i,
                ad_language l 
            WHERE l.isbaselanguage = 'N' AND l.issystemlanguage = 'Y'
            )
UNION 
SELECT /* tax lines */ it.ad_client_id,
    it.ad_org_id,
    it.isactive,
    it.created,
    it.createdby,
    it.updated,
    it.updatedby,
    tt.ad_language,
    it.c_invoice_id,
    NULL    AS c_invoiceline_id,
    it.c_tax_id,
    NULL    AS taxamt,
    NULL    AS linetotalamt,
    tt.taxindicator,
    999999  AS line,
    NULL    AS m_product_id,
    NULL    AS qtyinvoiced,
    NULL    AS qtyentered,
    NULL    AS uomsymbol,
    tt.name,
    NULL    AS description,
    NULL    AS documentnote,
    NULL    AS upc,
    NULL    AS sku,
    NULL    AS productvalue,
    NULL    AS resourcedescription,
    NULL    AS pricelist,
    NULL    AS priceenteredlist,
    NULL    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 NULL ELSE it.taxamt END AS linenetamt,
    NULL    AS m_attributesetinstance_id,
    NULL    AS m_attributeset_id,
    NULL    AS serno,
    NULL    AS lot,
    NULL    AS m_lot_id,
    NULL    AS guaranteedate,
    NULL    AS productdescription,
    NULL    AS imageurl,
    NULL    AS c_campaign_id,
    NULL    AS c_project_id,
    NULL    AS c_activity_id,
    NULL    AS c_projectphase_id,
    NULL    AS c_projecttask_id,
    NULL    AS c_invoiceline_ad_orgtrx_id,
    NULL    AS a_processed,
    NULL    AS c_charge_id,
    NULL    AS c_orderline_id,
    NULL    AS c_uom_id,
    NULL    AS isdescription,
    NULL    AS c_invoiceline_isprinted,
    NULL    AS m_inoutline_id,
    NULL    AS m_rmaline_id,
    NULL    AS pricelimit,
    NULL    AS c_invoiceline_processed,
    NULL    AS ref_invoiceline_id,
    NULL    AS rramt,
    NULL    AS rrstartdate,
    NULL    AS s_resourceassignment_id,
    NULL    AS c_invoiceline_user1_id,
    NULL    AS c_invoiceline_user2_id,
    NULL    AS c_uom_ad_org_id,
    NULL    AS costingprecision,
    NULL    AS c_uom_description,
    NULL    AS c_uom_isactive,
    NULL    AS c_uom_isdefault,
    NULL    AS c_uom_name,
    NULL    AS stdprecision,
    NULL    AS uomtype,
    NULL    AS x12de355,
    NULL    AS c_invoice_ad_org_id,
    NULL    AS c_invoice_ad_orgtrx_id,
    NULL    AS c_invoice_ad_user_id,
    NULL    AS c_invoice_c_activity_id,
    NULL    AS c_invoice_c_bpartner_id,
    NULL    AS c_bpartner_location_id,
    NULL    AS c_invoice_c_campaign_id,
    NULL    AS c_invoice_c_charge_id,
    NULL    AS c_conversiontype_id,
    NULL    AS c_currency_id,
    NULL    AS c_doctype_id,
    NULL    AS c_doctypetarget_id,
    NULL    AS c_dunninglevel_id,
    NULL    AS c_invoice_chargeamt,
    NULL    AS c_order_id,
    NULL    AS c_payment_id,
    NULL    AS c_paymentterm_id,
    NULL    AS c_invoice_c_project_id,
    NULL    AS c_invoice_created,
    NULL    AS c_invoice_createdby,
    NULL    AS dateacct,
    NULL    AS dateinvoiced,
    NULL    AS dateordered,
    NULL    AS dateprinted,
    NULL    AS c_invoice_description,
    NULL    AS docaction,
    NULL    AS docstatus,
    NULL    AS documentno,
    NULL    AS dunninggrace,
    NULL    AS generateto,
    NULL    AS grandtotal,
    NULL    AS invoicecollectiontype,
    NULL    AS c_invoice_isactive,
    NULL    AS isapproved,
    NULL    AS isdiscountprinted,
    NULL    AS isindispute,
    NULL    AS ispaid,
    NULL    AS ispayschedulevalid,
    NULL    AS c_invoice_isprinted,
    NULL    AS c_invoice_isselfservice,
    NULL    AS issotrx,
    NULL    AS c_invoice_istaxincluded,
    NULL    AS istransferred,
    NULL    AS m_pricelist_id,
    NULL    AS m_rma_id,
    NULL    AS paymentrule,
    NULL    AS poreference,
    NULL    AS posted,
    NULL    AS processedon,
    NULL    AS processing,
    NULL    AS ref_invoice_id,
    NULL    AS reversal_id,
    NULL    AS salesrep_id,
    NULL    AS sendemail,
    NULL    AS totallines,
    NULL    AS c_invoice_updated,
    NULL    AS c_invoice_updatedby,
    NULL    AS c_invoice_user1_id,
    NULL    AS c_invoice_user2_id,
    NULL    AS c_tax_ad_org_id,
    NULL    AS ad_rule_id,
    NULL    AS c_country_id,
    NULL    AS c_region_id,
    NULL    AS c_tax_c_taxcategory_id,
    NULL    AS c_tax_description,
    NULL    AS c_tax_isactive,
    NULL    AS c_tax_isdefault,
    NULL    AS isdocumentlevel,
    NULL    AS issalestax,
    NULL    AS c_tax_issummary,
    NULL    AS istaxexempt,
    NULL    AS c_tax_name,
    NULL    AS parent_tax_id,
    NULL    AS rate,
    NULL    AS requirestaxcertificate,
    NULL    AS sopotype,
    NULL    AS to_country_id,
    NULL    AS to_region_id,
    NULL    AS validfrom,
    NULL    AS m_product_ad_org_id,
    NULL    AS classification,
    NULL    AS m_product_copyfrom,
    NULL    AS m_product_created,
    NULL    AS m_product_createdby,
    NULL    AS c_revenuerecognition_id,
    NULL    AS c_subscriptiontype_id,
    NULL    AS m_product_c_taxcategory_id,
    NULL    AS descriptionurl,
    NULL    AS discontinued,
    NULL    AS discontinuedat,
    NULL    AS group1,
    NULL    AS group2,
    NULL    AS guaranteedays,
    NULL    AS guaranteedaysmin,
    NULL    AS help,
    NULL    AS m_product_isactive,
    NULL    AS isbom,
    NULL    AS isdropship,
    NULL    AS isexcludeautodelivery,
    NULL    AS isinvoiceprintdetails,
    NULL    AS ispicklistprintdetails,
    NULL    AS ispurchased,
    NULL    AS m_product_isselfservice,
    NULL    AS issold,
    NULL    AS isstocked,
    NULL    AS m_product_issummary,
    NULL    AS isverified,
    NULL    AS iswebstorefeatured,
    NULL    AS lowlevel,
    NULL    AS m_product_m_attributeset_id,
    NULL    AS m_product_m_asi_id,
    NULL    AS m_freightcategory_id,
    NULL    AS m_locator_id,
    NULL    AS m_product_m_prod_category_id,
    NULL    AS m_product_processing,
    NULL    AS producttype,
    NULL    AS r_mailtext_id,
    NULL    AS m_product_salesrep_id,
    NULL    AS s_expensetype_id,
    NULL    AS shelfdepth,
    NULL    AS shelfheight,
    NULL    AS shelfwidth,
    NULL    AS m_product_s_resource_id,
    NULL    AS unitsperpack,
    NULL    AS unitsperpallet,
    NULL    AS m_product_updated,
    NULL    AS m_product_updatedby,
    NULL    AS versionno,
    NULL    AS volume,
    NULL    AS weight,
    NULL    AS c_charge_ad_org_id,
    NULL    AS c_charge_c_bpartner_id,
    NULL    AS c_chargetype_id,
    NULL    AS c_charge_chargeamt,
    NULL    AS c_charge_c_taxcategory_id,
    NULL    AS c_charge_description,
    NULL    AS c_charge_isactive,
    NULL    AS issamecurrency,
    NULL    AS issametax,
    NULL    AS c_charge_istaxincluded,
    NULL    AS c_bp_product_ad_org_id,
    NULL    AS c_bp_product_c_bpartner_id,
    NULL    AS c_bp_product_created,
    NULL    AS c_bp_product_createdby,
    NULL    AS c_bp_product_description,
    NULL    AS c_bp_product_isactive,
    NULL    AS ismanufacturer,
    NULL    AS manufacturer,
    NULL    AS qualityrating,
    NULL    AS shelflifemindays,
    NULL    AS shelflifeminpct,
    NULL    AS c_bp_product_updated,
    NULL    AS c_bp_product_updatedby,
    NULL    AS vendorcategory,
    NULL    AS s_rassignment_ad_org_id,
    NULL    AS assigndatefrom,
    NULL    AS assigndateto,
    NULL    AS s_rassignment_created,
    NULL    AS s_rassignment_createby,
    NULL    AS s_rassignment_isactive,
    NULL    AS isconfirmed,
    NULL    AS s_rassignment_name,
    NULL    AS s_rassignment_qty,
    NULL    AS s_rassignment_s_resource_id,
    NULL    AS s_rassignment_updated,
    NULL    AS s_rassignment_updatedby,
    NULL    AS m_asi_ad_org_id,
    NULL    AS m_asi_created,
    NULL    AS m_asi_createdby,
    NULL    AS m_asi_description,
    NULL    AS m_asi_isactive,
    NULL    AS m_asi_updated,
    NULL    AS m_asi_updatedby 
FROM c_invoicetax it 
        JOIN c_tax_trl tt 
        ON it.c_tax_id = tt.c_tax_id
;
SELECT register_migration_script('201303271041_IDEMPIERE-794.sql') FROM dual
;

