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
;
