SELECT 
   "M_ATTRIBUTESETINSTANCE"."DESCRIPTION",
   "C_UOM"."NAME",
   "DocStatus"."NAME",
   "M_WAREHOUSE"."NAME",
   "M_PRODUCT"."NAME",
   "M_PRODUCT_CATEGORY"."NAME",
   "C_BPARTNER"."NAME",
   "OpenTransaction"."DOCUMENTNO",
   "OpenTransaction"."M_PRODUCT_ID",
   "OpenTransaction"."M_ATTRIBUTESETINSTANCE_ID",
   "OpenTransaction"."C_UOM_ID",
   "OpenTransaction"."C_BPARTNER_ID",
   "OpenTransaction"."M_WAREHOUSE_ID",
   "OpenTransaction"."MOVEMENTTYPE",
   "OpenTransaction"."AD_ORG_ID",
   "OpenTransaction"."AD_CLIENT_ID",
   "OpenTransaction"."QTY",
   "OpenTransaction"."DOCSTATUS",
   "AD_ORG"."NAME",
   "AD_CLIENT"."VALUE"
FROM (
   SELECT ord.ad_org_id ,
  ord.ad_client_id ,
  ord.m_warehouse_id ,
  ordln.m_product_id ,
  Ordln.M_Attributesetinstance_Id ,
  Ordln.C_Uom_Id,
  ord.c_bpartner_id,
  ordln.qtyordered AS Qty ,
  'Sales Order'    AS MovementType ,
  ord.documentno ,
  ord.docstatus
FROM c_order ord
INNER JOIN c_orderline ordln
ON (ord.c_order_id=ordln.c_order_id)
LEFT JOIN m_attributesetinstance asi
ON (ordln.m_attributesetinstance_id=asi.m_attributesetinstance_id)
LEFT JOIN m_product pro
ON (ordln.m_product_id=pro.m_product_id)
INNER JOIN m_warehouse wh
ON (ord.m_warehouse_id=wh.m_warehouse_id)
WHERE ord.issotrx     ='Y'
AND ord.isreturntrx   ='N'
AND Ord.Docstatus    IN ('IP','DR')
UNION ALL
SELECT ord.ad_org_id ,
  ord.ad_client_id ,
  ord.m_warehouse_id ,
  ordln.m_product_id ,
  Ordln.M_Attributesetinstance_Id ,
  Ordln.C_Uom_Id,
  ord.c_bpartner_id,
  ordln.qtyentered AS Qty ,
  'Purchase Order' AS MovementType ,
  ord.documentno ,
  ord.docstatus
FROM c_order ord
INNER JOIN c_orderline ordln
ON (ord.c_order_id  =ordln.c_order_id)
WHERE ord.issotrx   ='N'
AND ord.isreturntrx ='N'
AND Ord.Docstatus  IN ('IP','DR')
UNION ALL
SELECT inout.ad_org_id ,
  inout.ad_client_id ,
  inout.m_warehouse_id ,
  inoutln.m_product_id ,
  Inoutln.M_Attributesetinstance_Id ,
  Inoutln.C_Uom_Id,
  inout.c_bpartner_id,
  inoutln.movementqty AS Qty ,
  'Material Receipt'  AS MovementType ,
  inout.documentno ,
  inout.docstatus
FROM m_inout INOUT
INNER JOIN m_inoutline inoutln
ON (inout.m_inout_id   =inoutln.m_inout_id)
WHERE inout.docstatus IN ('IP','DR')
AND Inout.Movementtype ='V+'
UNION ALL
SELECT req.ad_org_id ,
  req.ad_client_id ,
  Req.M_Warehouse_Id ,
  reqln.m_product_id ,
  Reqln.M_Attributesetinstance_Id ,
  NULL AS c_uom_id,
  req.c_bpartner_id,
  reqln.qty     AS Qty ,
  'Requisition' AS MovementType ,
  req.documentno ,
  req.docstatus
FROM m_requisition req
INNER JOIN m_requisitionline reqln
ON (req.m_requisition_id=reqln.m_requisition_id)
WHERE Req.Docstatus    IN ('IP','DR')
UNION ALL
SELECT mov.ad_org_id ,
  Mov.Ad_Client_Id ,
  mov.dtd001_mwarehousesource_id AS m_warehouse_id ,
  movln.m_product_id ,
  Movln.M_Attributesetinstance_Id ,
  Movln.C_Uom_Id,
  NULL                      AS c_bpartner_id,
  Movln.Movementqty         AS Qty ,
  'Inventory Movement From' AS MovementType ,
  mov.documentno ,
  mov.docstatus
FROM m_movement mov
INNER JOIN m_movementline movln
ON (mov.m_movement_id=movln.m_movement_id)
WHERE Mov.Docstatus IN ('IP','DR')
UNION ALL
SELECT
  (SELECT Ad_Org_id FROM M_warehouse WHERE m_warehouse_id=mov.m_warehouse_id
  ) AS ad_org_id ,
  mov.ad_client_id ,
  mov.m_warehouse_id ,
  Movln.M_Product_Id ,
  movln.m_attributesetinstance_id ,
  Movln.C_Uom_Id,
  NULL                    AS c_bpartner_id,
  Movln.Movementqty       AS Qty ,
  'Inventory Movement To' AS MovementType ,
  mov.documentno ,
  mov.docstatus
FROM m_movement mov
INNER JOIN m_movementline movln
ON (mov.m_movement_id=movln.m_movement_id)
WHERE Mov.Docstatus IN ('IP','DR')
UNION ALL
SELECT inv.ad_org_id ,
  inv.ad_client_id ,
  inv.m_warehouse_id ,
  invln.m_product_id ,
  Invln.M_Attributesetinstance_Id ,
  NULL                     AS C_Uom_Id,
  NULL                     AS c_bpartner_id,
  invln.qtyinternaluse     AS Qty ,
  'Internal Use Inventory' AS MovementType ,
  inv.documentno ,
  inv.docstatus
FROM m_inventory inv
INNER JOIN m_inventoryline invln
ON (inv.m_inventory_id  =invln.m_inventory_id)
WHERE inv.docstatus    IN ('IP','DR')
AND Invln.Isinternaluse ='Y'
UNION ALL
SELECT inv.ad_org_id ,
  inv.ad_client_id ,
  inv.m_warehouse_id ,
  invln.m_product_id ,
  Invln.M_Attributesetinstance_Id ,
  NULL                 AS C_Uom_Id,
  NULL                 AS c_bpartner_id,
  invln.differenceqty  AS Qty ,
  'Physical Inventory' AS MovementType ,
  inv.documentno ,
  inv.docstatus
FROM m_inventory inv
INNER JOIN m_inventoryline invln
ON (inv.m_inventory_id  =invln.m_inventory_id)
WHERE inv.docstatus    IN ('IP','DR')
AND Invln.Isinternaluse ='N'
UNION ALL
SELECT inout.ad_org_id ,
  inout.ad_client_id ,
  inout.m_warehouse_id ,
  inoutln.m_product_id ,
  Inoutln.M_Attributesetinstance_Id ,
  Inoutln.C_Uom_Id,
  inout.c_bpartner_id,
  inoutln.movementqty AS Qty ,
  'Shipment'          AS MovementType ,
  inout.documentno ,
  inout.docstatus
FROM m_inout INOUT
INNER JOIN m_inoutline inoutln
ON (inout.m_inout_id   =inoutln.m_inout_id)
WHERE inout.docstatus IN ('IP','DR')
AND inout.movementtype ='C-'
UNION
SELECT inout.ad_org_id ,
  inout.ad_client_id ,
  inout.m_warehouse_id ,
  inoutln.m_product_id ,
  Inoutln.M_Attributesetinstance_Id ,
  Inoutln.C_Uom_Id,
  inout.c_bpartner_id,
  inoutln.movementqty AS Qty ,
  'Customer Return'   AS MovementType ,
  inout.documentno ,
  inout.docstatus
FROM m_inout INOUT
INNER JOIN m_inoutline inoutln
ON (inout.m_inout_id   =inoutln.m_inout_id)
WHERE inout.docstatus IN ('IP','DR')
AND Inout.Movementtype ='C+'
UNION ALL
SELECT inout.ad_org_id ,
  inout.ad_client_id ,
  inout.m_warehouse_id ,
  inoutln.m_product_id ,
  Inoutln.M_Attributesetinstance_Id ,
  Inoutln.C_Uom_Id,
  inout.c_bpartner_id,
  inoutln.movementqty AS Qty ,
  'Return To Vendor'  AS MovementType ,
  inout.documentno ,
  inout.docstatus
FROM m_inout INOUT
INNER JOIN m_inoutline inoutln
ON (inout.m_inout_id   =inoutln.m_inout_id)
WHERE inout.docstatus IN ('IP','DR')
AND Inout.Movementtype ='V-'
UNION ALL
SELECT ord.ad_org_id ,
  ord.ad_client_id ,
  ord.m_warehouse_id ,
  ordln.m_product_id ,
  Ordln.M_Attributesetinstance_Id ,
  Ordln.C_Uom_Id,
  ord.c_bpartner_id,
  ordln.qtyordered AS Qty ,
  'Customer RMA'   AS MovementType ,
  ord.documentno ,
  ord.docstatus
FROM c_order ord
INNER JOIN c_orderline ordln
ON (ord.c_order_id  =ordln.c_order_id)
WHERE ord.issotrx   ='Y'
AND ord.isreturntrx ='Y'
AND ord.docstatus  IN ('IP','DR')
UNION ALL
SELECT ord.ad_org_id ,
  ord.ad_client_id ,
  ord.m_warehouse_id ,
  ordln.m_product_id ,
  Ordln.M_Attributesetinstance_Id ,
  Ordln.C_Uom_Id,
  ord.c_bpartner_id,
  ordln.qtyordered AS Qty ,
  'Vendor RMA'     AS MovementType ,
  ord.documentno ,
  ord.docstatus
FROM c_order ord
INNER JOIN c_orderline ordln
ON (Ord.C_Order_Id  =Ordln.C_Order_Id)
WHERE ord.issotrx   ='N'
AND ord.isreturntrx ='Y'
AND Ord.Docstatus  IN ('IP','DR')

) "OpenTransaction"
INNER JOIN "DB17NOV2017"."AD_ORG"
ON (
   "OpenTransaction"."AD_ORG_ID" = "AD_ORG"."AD_ORG_ID"
)
INNER JOIN "DB17NOV2017"."AD_CLIENT"
ON (
   "OpenTransaction"."AD_CLIENT_ID" = "AD_CLIENT"."AD_CLIENT_ID"
)
INNER JOIN "DB17NOV2017"."M_PRODUCT"
ON (
   "OpenTransaction"."M_PRODUCT_ID" = "M_PRODUCT"."M_PRODUCT_ID"
)
LEFT OUTER JOIN (
   SELECT Ad_Ref_List.value,
    Ad_Ref_List.name
  FROM Ad_Reference
  INNER JOIN Ad_Ref_List
  ON Ad_Reference.Ad_Reference_Id=Ad_Ref_List.Ad_Reference_Id
  WHERE Ad_Reference.Name        ='_Document Status'
  AND Ad_Reference.Isactive      ='Y'
  AND Ad_Ref_List.isactive       ='Y'

) "DocStatus"
ON (
   "OpenTransaction"."DOCSTATUS" = "DocStatus"."VALUE"
)
LEFT OUTER JOIN "DB17NOV2017"."M_WAREHOUSE"
ON (
   "OpenTransaction"."M_WAREHOUSE_ID" = "M_WAREHOUSE"."M_WAREHOUSE_ID"
)
LEFT OUTER JOIN "DB17NOV2017"."M_PRODUCT_CATEGORY"
ON (
   "M_PRODUCT"."M_PRODUCT_CATEGORY_ID" = "M_PRODUCT_CATEGORY"."M_PRODUCT_CATEGORY_ID"
)
LEFT OUTER JOIN "DB17NOV2017"."C_UOM"
ON (
   "OpenTransaction"."C_UOM_ID" = "C_UOM"."C_UOM_ID"
)
LEFT OUTER JOIN "DB17NOV2017"."C_BPARTNER"
ON (
   "OpenTransaction"."C_BPARTNER_ID" = "C_BPARTNER"."C_BPARTNER_ID"
)
LEFT OUTER JOIN "DB17NOV2017"."M_ATTRIBUTESETINSTANCE"
ON (
   "OpenTransaction"."M_ATTRIBUTESETINSTANCE_ID" = "M_ATTRIBUTESETINSTANCE"."M_ATTRIBUTESETINSTANCE_ID"
)