DROP VIEW rv_orderdetail;

CREATE OR REPLACE VIEW rv_orderdetail
AS
  SELECT l.ad_client_id,
         l.ad_org_id,
         l.isactive,
         l.created,
         l.createdby,
         l.updated,
         l.updatedby,
         o.c_order_id,
         o.docstatus,
         o.docaction,
         o.c_doctype_id,
         o.isapproved,
         o.iscreditapproved,
         o.salesrep_id,
         o.bill_bpartner_id,
         o.bill_location_id,
         o.bill_user_id,
         o.isdropship,
         l.c_bpartner_id,
         l.c_bpartner_location_id,
         o.ad_user_id,
         o.poreference,
         o.c_currency_id,
         o.issotrx,
         l.c_campaign_id,
         l.c_project_id,
         l.c_activity_id,
         l.c_projectphase_id,
         l.c_projecttask_id,
         l.c_orderline_id,
         l.dateordered,
         l.datepromised,
         l.m_product_id,
         l.m_warehouse_id,
         l.m_attributesetinstance_id,
         productattribute(l.m_attributesetinstance_id)    AS productattribute,
         pasi.m_attributeset_id,
         pasi.m_lot_id,
         pasi.guaranteedate,
         pasi.lot,
         pasi.serno,
         l.c_uom_id,
         l.qtyentered,
         l.qtyordered,
         l.qtyreserved,
         l.qtydelivered,
         l.qtyinvoiced,
         l.priceactual,
         l.priceentered,
         l.qtyordered - l.qtydelivered                    AS qtytodeliver,
         l.qtyordered - l.qtyinvoiced                     AS qtytoinvoice,
         ( l.qtyordered - l.qtyinvoiced ) * l.priceactual AS netamttoinvoice,
         l.qtylostsales,
         l.qtylostsales * l.priceactual                   AS amtlostsales,
         CASE
           WHEN l.pricelist = 0 THEN 0
           ELSE round(( l.pricelist - l.priceactual ) / l.pricelist * 100, 2)
         END                                              AS discount,
         CASE
           WHEN l.pricelimit = 0 THEN 0
           ELSE round(( l.priceactual - l.pricelimit ) / l.pricelimit * 100, 2)
         END                                              AS margin,
         CASE
           WHEN l.pricelimit = 0 THEN 0
           ELSE ( l.priceactual - l.pricelimit ) * l.qtydelivered
         END                                              AS marginamt,
         o.ad_org_id                                      AS c_order_ad_org_id,
         o.ad_orgtrx_id                                   AS c_order_ad_orgtrx_id,
         o.amountrefunded,
         o.amounttendered,
         o.c_activity_id                                  AS c_order_c_activity_id,
         o.c_bpartner_id                                  AS c_order_c_bpartner_id,
         o.c_bpartner_location_id                         AS c_order_c_bpartner_location_i,
         o.c_campaign_id                                  AS c_order_c_compaign_id,
         o.c_cashline_id,
         o.c_cashplanline_id,
         o.c_charge_id                                    AS c_order_c_charge_id,
         o.c_conversiontype_id,
         o.c_doctypetarget_id,
         o.chargeamt,
         o.copyfrom,
         o.c_payment_id,
         o.c_paymentterm_id,
         o.c_pos_id,
         o.c_project_id                                   AS c_order_c_project_id,
         o.created                                        AS c_order_created,
         o.createdby                                      AS c_order_createdby,
         o.dateacct,
         o.dateordered                                    AS c_order_dateordered,
         o.dateprinted,
         o.datepromised                                   AS c_order_datepromised,
         o.deliveryrule,
         o.deliveryviarule,
         o.description                                    AS c_order_description,
         o.documentno,
         o.dropship_bpartner_id,
         o.dropship_location_id,
         o.dropship_user_id,
         o.freightamt                                     AS c_order_freightamt,
         o.freightcostrule,
         o.grandtotal,
         o.invoicerule,
         o.isactive                                       AS c_order_isactive,
         o.isdelivered,
         o.isdiscountprinted,
         o.isinvoiced,
         o.ispayschedulevalid,
         o.isprinted,
         o.isselected,
         o.isselfservice,
         o.istaxincluded,
         o.istransferred,
         o.link_order_id,
         o.m_freightcategory_id,
         o.m_pricelist_id,
         o.m_shipper_id                                   AS c_order_m_shipper_id,
         o.m_warehouse_id                                 AS c_order_m_warehouse_id,
         o.ordertype,
         o.pay_bpartner_id,
         o.pay_location_id,
         o.paymentrule,
         o.posted,
         o.priorityrule,
         o.processed                                      AS c_order_processed,
         o.processedon,
         o.promotioncode,
         o.ref_order_id,
         o.sendemail,
         o.totallines,
         o.updated                                        AS c_order_updated,
         o.updatedby                                      AS c_order_updatedby,
         o.user1_id                                       AS c_order_user1_id,
         o.user2_id                                       AS c_order_user2_id,
         o.volume,
         o.weight,
         l.ad_orgtrx_id                                   AS c_orderline_ad_orgtrx_id,
         l.c_charge_id                                    AS c_orderline_c_charge_id,
         l.c_currency_id                                  AS c_orderline_c_currency_id,
         l.c_tax_id,
         l.datedelivered,
         l.dateinvoiced,
         l.description                                    AS c_orderline_description,
         l.discount                                       AS c_orderline_discount,
         l.freightamt                                     AS c_orderline_freightamt,
         l.isdescription,
         l.line,
         l.linenetamt,
         l.link_orderline_id,
         l.m_promotion_id,
         l.m_shipper_id                                   AS c_orderline_m_shipper_id,
         l.pricecost,
         l.pricelimit,
         l.pricelist,
         l.processed                                      AS c_orderline_processed,
         l.ref_orderline_id,
         l.rramt,
         l.rrstartdate,
         l.s_resourceassignment_id,
         l.user1_id                                       AS c_orderline_user1_id,
         l.user2_id                                       AS c_orderline_user2_id,
         pasi.ad_org_id                                   AS m_asi_ad_org_id,
         pasi.created                                     AS m_asi_created,
         pasi.createdby                                   AS m_asi_createdby,
         pasi.description                                 AS m_asi_description,
         pasi.isactive                                    AS m_asi_isactive,
         pasi.updated                                     AS m_asi_updated,
         pasi.updatedby                                   AS m_asi_updatedby
  FROM   c_order o
         JOIN c_orderline l
           ON o.c_order_id = l.c_order_id
         LEFT JOIN m_attributesetinstance pasi
                ON l.m_attributesetinstance_id = pasi.m_attributesetinstance_id
;

