DROP VIEW rv_transaction;

CREATE OR REPLACE VIEW rv_transaction
AS
  SELECT t.m_transaction_id,
         t.ad_client_id,
         t.ad_org_id,
         t.movementtype,
         t.movementdate,
         t.movementqty,
         t.m_attributesetinstance_id,
         asi.m_attributeset_id,
         asi.serno,
         asi.lot,
         asi.m_lot_id,
         asi.guaranteedate,
         t.m_product_id,
         p.value,
         p.name,
         p.description,
         p.upc,
         p.sku,
         p.c_uom_id,
         p.m_product_category_id,
         p.classification,
         p.weight,
         p.volume,
         p.versionno,
         t.m_locator_id,
         l.m_warehouse_id,
         l.x,
         l.y,
         l.z,
         t.m_inventoryline_id,
         il.m_inventory_id,
         t.m_movementline_id,
         ml.m_movement_id,
         t.m_inoutline_id,
         iol.m_inout_id,
         t.m_productionline_id,
         prdl.m_productionplan_id,
         prdp.m_production_id,
         t.c_projectissue_id,
         pjl.c_project_id,
         COALESCE(il.line, ml.line, iol.line, prdl.line, pjl.line) AS line,
         t.created                                                 AS m_transaction_created,
         t.createdby                                               AS m_transaction_createdby,
         t.isactive                                                AS m_transaction_isactive,
         t.pp_cost_collector_id,
         t.updated                                                 AS m_transaction_updated,
         t.updatedby                                               AS m_transction_updatedby,
         l.ad_org_id                                               AS m_locator_ad_org_id,
         l.isactive                                                AS m_locator_isactive,
         l.isdefault,
         l.priorityno,
         l.value                                                   AS m_locator_value,
         p.ad_org_id                                               AS m_product_ad_org_id,
         p.copyfrom                                                AS m_product_copyfrom,
         p.created                                                 AS m_product_created,
         p.createdby                                               AS m_product_createdby,
         p.c_revenuerecognition_id,
         p.c_subscriptiontype_id,
         p.c_taxcategory_id,
         p.descriptionurl,
         p.discontinued                                            AS m_product_discontinued,
         p.discontinuedat                                          AS m_product_discontinuedat,
         p.documentnote,
         p.group1,
         p.group2,
         p.guaranteedays,
         p.guaranteedaysmin,
         p.help,
         p.imageurl,
         p.isactive                                                AS m_product_isactive,
         p.isbom,
         p.isdropship,
         p.isexcludeautodelivery,
         p.isinvoiceprintdetails,
         p.ispicklistprintdetails,
         p.ispurchased,
         p.isselfservice,
         p.issold,
         p.isstocked,
         p.issummary                                               AS m_product_issummary,
         p.isverified,
         p.iswebstorefeatured,
         p.lowlevel,
         p.m_attributeset_id                                       AS m_product_m_attributeset_id,
         p.m_attributesetinstance_id                               AS m_product_m_asi_id,
         p.m_freightcategory_id,
         p.m_locator_id                                            AS m_product_m_locator_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,
         il.ad_org_id                                              AS m_inventoryline_ad_org_id,
         il.c_charge_id                                            AS m_inventoryline_c_charge_id,
         il.created                                                AS m_inventoryline_created,
         il.createdby                                              AS m_inventoryline_createdby,
         il.description                                            AS m_inventoryline_description,
         il.inventorytype,
         il.isactive                                               AS m_inventoryline_isactive,
         il.m_attributesetinstance_id                              AS m_inventoryline_m_asi_id,
         il.m_locator_id                                           AS m_inventoryline_m_locator_id,
         il.m_product_id                                           AS m_inventoryline_m_product_id,
         il.processed                                              AS m_inventoryline_processed,
         il.qtybook,
         il.qtycount,
         il.qtycsv,
         il.qtyinternaluse,
         il.reversalline_id                                        AS m_inventoryline_revline_id,
         il.updated                                                AS m_inventoryline_updated,
         il.updatedby                                              AS m_inventoryline_updatedby,
         ml.ad_org_id                                              AS m_movementline_ad_org_id,
         ml.confirmedqty                                           AS m_movementline_confirmedqty,
         ml.created                                                AS m_movementline_created,
         ml.createdby                                              AS m_movementline_createdby,
         ml.dd_orderline_id,
         ml.description                                            AS m_movementline_description,
         ml.isactive                                               AS m_movementline_isactive,
         ml.m_attributesetinstance_id                              AS m_movementline_m_asi_id,
         ml.m_attributesetinstanceto_id,
         ml.m_locator_id                                           AS m_movementline_m_locator_id,
         ml.m_locatorto_id,
         ml.movementqty                                            AS m_movementline_movementqty,
         ml.m_product_id                                           AS m_movementline_m_product_id,
         ml.processed                                              AS m_movementline_processed,
         ml.reversalline_id                                        AS m_movementline_reversalln_id,
         ml.scrappedqty                                            AS m_movementline_scrappedqty,
         ml.targetqty                                              AS m_movementline_targetqty,
         ml.updated                                                AS m_movementline_updated,
         ml.updatedby                                              AS m_movementline_updatedby,
         iol.ad_org_id                                             AS m_inoutline_ad_org_id,
         iol.ad_orgtrx_id                                          AS m_inoutline_ad_orgtrx_id,
         iol.c_activity_id                                         AS m_inoutline_c_activity_id,
         iol.c_campaign_id                                         AS m_inoutline_c_campaign_id,
         iol.c_charge_id                                           AS m_inoutline_c_charge_id,
         iol.confirmedqty                                          AS m_inoutline_confirmedqty,
         iol.c_orderline_id                                        AS m_inoutline_c_orderline_id,
         iol.c_project_id                                          AS m_inoutline_c_project_id,
         iol.c_projectphase_id,
         iol.c_projecttask_id,
         iol.created                                               AS m_inoutline_created,
         iol.createdby                                             AS m_inoutline_createdby,
         iol.c_uom_id                                              AS m_inoutline_c_uom_id,
         iol.description                                           AS m_inoutline_description,
         iol.isactive                                              AS m_inoutline_isactive,
         iol.isdescription,
         iol.isinvoiced,
         iol.line                                                  AS m_inoutline_line,
         iol.m_attributesetinstance_id                             AS m_inoutline_m_asi_id,
         iol.m_inout_id                                            AS m_inoutline_m_inout_id,
         iol.movementqty                                           AS m_inoutline_movementqty,
         iol.m_product_id                                          AS m_inoutline_m_product_id,
         iol.m_rmaline_id                                          AS m_inoutline_m_rmaline_id,
         iol.pickedqty                                             AS m_inoutline_pickedqty,
         iol.processed                                             AS m_inoutline_processed,
         iol.qtyentered                                            AS m_inoutline_qtyentered,
         iol.ref_inoutline_id,
         iol.reversalline_id                                       AS m_inoutline_reversalline_id,
         iol.scrappedqty                                           AS m_inoutline_scrappedqty,
         iol.targetqty                                             AS m_inoutline_targetqty,
         iol.updated                                               AS m_inoutline_updated,
         iol.updatedby                                             AS m_inoutline_updatedby,
         iol.user1_id                                              AS m_inoutline_user1_id,
         iol.user2_id                                              AS m_inoutline_user2_id,
         prdl.ad_org_id                                            AS m_productionline_ad_org_id,
         prdl.created                                              AS m_productionline_created,
         prdl.createdby                                            AS m_productionline_createdby,
         prdl.description                                          AS m_productionline_description,
         prdl.isactive                                             AS m_productionline_isactive,
         prdl.m_attributesetinstance_id                            AS m_productionline_m_asi_id,
         prdl.m_locator_id                                         AS m_productionline_m_locator_id,
         prdl.movementqty                                          AS m_productionline_movementqty,
         prdl.m_product_id                                         AS m_productionline_m_product_id,
         prdl.m_productionline_id                                  AS m_productionl_m_productionl_id,
         prdl.processed                                            AS m_productionline_processed,
         prdl.updated                                              AS m_productionline_updated,
         prdl.updatedby                                            AS m_productionline_updatedby,
         prdp.ad_org_id                                            AS m_productionplan_ad_org_id,
         prdp.created                                              AS m_productionplan_created,
         prdp.createdby                                            AS m_productionplan_createdby,
         prdp.description                                          AS m_productionplan_description,
         prdp.isactive                                             AS m_productionplan_isactive,
         prdp.line                                                 AS m_productionplan_line,
         prdp.m_locator_id                                         AS m_productionplan_m_locator_id,
         prdp.m_product_id                                         AS m_productionplan_m_product_id,
         prdp.processed                                            AS m_productionplan_processed,
         prdp.productionqty                                        AS m_productionplan_productionqt,
         prdp.updated                                              AS m_productionplan_updated,
         prdp.updatedby                                            AS m_productionplan_updatedby,
         pjl.ad_org_id                                             AS c_prjctissue_ad_org_id,
         pjl.c_projectissue_id                                     AS c_prjctissue_c_projectissue_id,
         pjl.created                                               AS c_prjctissue_created,
         pjl.createdby                                             AS c_prjctissue_createdby,
         pjl.description                                           AS c_prjctissue_description,
         pjl.isactive                                              AS c_prjctissue_isactive,
         pjl.m_attributesetinstance_id                             AS c_prjctissue_m_asi_id,
         pjl.m_inoutline_id                                        AS c_prjctissue_m_inoutline_id,
         pjl.m_locator_id                                          AS c_prjctissue_m_locator_id,
         pjl.movementdate                                          AS c_prjctissue_movementdate,
         pjl.movementqty                                           AS c_prjctissue_movementqty,
         pjl.m_product_id                                          AS c_prjctissue_m_product_id,
         pjl.posted                                                AS c_prjctissue_posted,
         pjl.processed                                             AS c_prjctissue_processed,
         pjl.processedon                                           AS c_prjctissue_processedon,
         pjl.processing                                            AS c_prjctissue_processing,
         pjl.s_timeexpenseline_id                                  AS c_prjctissue_s_timeexpline_id,
         pjl.updated                                               AS c_prjctissue_updated,
         pjl.updatedby                                             AS c_prjctissue_updatedby
  FROM   m_transaction t
         JOIN m_locator l
           ON t.m_locator_id = l.m_locator_id
         JOIN m_product p
           ON t.m_product_id = p.m_product_id
         LEFT JOIN m_attributesetinstance asi
                ON t.m_attributesetinstance_id = asi.m_attributesetinstance_id
         LEFT JOIN m_inventoryline il
                ON t.m_inventoryline_id = il.m_inventoryline_id
         LEFT JOIN m_movementline ml
                ON t.m_movementline_id = ml.m_movementline_id
         LEFT JOIN m_inoutline iol
                ON t.m_inoutline_id = iol.m_inoutline_id
         LEFT JOIN m_productionline prdl
                ON t.m_productionline_id = prdl.m_productionline_id
         LEFT JOIN m_productionplan prdp
                ON prdl.m_productionplan_id = prdp.m_productionplan_id
         LEFT JOIN c_projectissue pjl
                ON t.c_projectissue_id = pjl.c_projectissue_id
;

