SELECT
   GNSTMTP.CDPRODOBJECT,
   GNTP.IDGENTYPE,
   GNSTM_STOREROOM.IDSTOREROOM,
   GNSTM_STOREROOM.NMSTOREROOM,
   GNSTM_TEAMRESP.NMTEAM,
   GNSTM_DEPTOWNER.IDDEPARTMENT IDAREA,
   GNSTM_CSTOWNER.IDCOMMERCIAL IDCUSTOMER,
   GNSTM_SUPOWNER.IDCOMMERCIAL IDSUPPLIER,
   OBTY.IDOBJECTTYPE,
   OBOBJ.IDOBJECT,
   OBOBJ.NMOBJECT,
   CASE
      WHEN ( GNSTMOBJ.FGCONTROLREPLACEM = 1 AND STOCKVIEW.VLSTOCK < GNSTMOBJ.VLREPLACEMENT ) OR STOCKVIEW.VLSTOCK IS NULL THEN 1
      WHEN GNSTMOBJ.FGCONTROLOPTIMUM = 1 AND STOCKVIEW.VLSTOCK < GNSTMOBJ.VLOPTIMUM THEN 2
      ELSE 3
   END AS FGTYPESTOCK,
   COALESCE(STOCKVIEW.VLSTOCK, 0) QTSTOCK,
   GNSTMOBJ.VLREPLACEMENT PONTOREP,
   GNSTMOBJ.VLOPTIMUM QTIDEAL,
   COALESCE(STOCKVIEW.VLUNITCOST, 0) VLUNITCOST,
   STOCKVIEW.VLSTOCKCOST
FROM GNSTOREROOMOBJECT GNSTMOBJ
   INNER JOIN OBSTOREROOMITSUP GNSTMITSUP ON ( GNSTMOBJ.CDSTOREROOMOBJECT = GNSTMITSUP.CDSTOREROOMOBJECT )
   INNER JOIN GNSTOREROOM GNSTM_PLACE ON ( GNSTMOBJ.CDSTOREROOM = GNSTM_PLACE.CDSTOREROOM )
   INNER JOIN GNSTOREROOM GNSTM_STOREROOM ON (
      GNSTM_PLACE.CDSTOREROOM = GNSTM_STOREROOM.CDSTOREROOM
      AND GNSTM_PLACE.FGTYPE = 1
      OR GNSTM_PLACE.CDSTOREROOMREF = GNSTM_STOREROOM.CDSTOREROOM
   )
   INNER JOIN GNSTOREROOMTYPE GNSTMTP ON ( GNSTM_STOREROOM.CDSTOREROOMTYPE = GNSTMTP.CDSTOREROOMTYPE )
   INNER JOIN GNGENTYPE GNTP ON ( GNSTMTP.CDSTOREROOMTYPE = GNTP.CDGENTYPE )
   LEFT JOIN ADDEPARTMENT GNSTM_DEPTOWNER ON ( GNSTM_STOREROOM.CDDEPARTMENT = GNSTM_DEPTOWNER.CDDEPARTMENT )
   LEFT JOIN ADCOMPANY GNSTM_CSTOWNER ON ( GNSTM_STOREROOM.CDCUSTOMER = GNSTM_CSTOWNER.CDCOMPANY )
   LEFT JOIN ADCOMPANY GNSTM_SUPOWNER ON ( GNSTM_STOREROOM.CDSUPPLIER = GNSTM_SUPOWNER.CDCOMPANY )
   LEFT JOIN ADTEAM GNSTM_TEAMRESP ON ( GNSTM_STOREROOM.CDTEAMRESP = GNSTM_TEAMRESP.CDTEAM )
   INNER JOIN OBOBJECT OBOBJ ON ( GNSTMITSUP.CDOBJECT = OBOBJ.CDOBJECT AND OBOBJ.FGCURRENT = 1 )
   INNER JOIN OBOBJECTGROUP OBOBJGRP ON ( OBOBJ.CDOBJECT = OBOBJGRP.CDOBJECTGROUP )
   INNER JOIN OBOBJECTTYPE OBTY ON ( OBOBJGRP.CDOBJECTTYPE = OBTY.CDOBJECTTYPE )
   LEFT JOIN (
      SELECT
         GNSTM_STOREROOM2.CDSTOREROOM,
         GNSTM_PLACE_STOCK.CDOBJECT,
         SUM( GNSTM_PLACE_STOCK.VLQUANTTOTAL ) AS VLSTOCK,
         SUM( GNSTM_PLACE_STOCK.VLQUANTTOTAL * GNSTM_PLACE_STOCK.VLUNITCOST ) AS VLSTOCKCOST,
         CASE
            WHEN SUM( GNSTM_PLACE_STOCK.VLQUANTTOTAL ) = 0 THEN 0
            ELSE SUM( GNSTM_PLACE_STOCK.VLQUANTTOTAL * GNSTM_PLACE_STOCK.VLUNITCOST ) / SUM( GNSTM_PLACE_STOCK.VLQUANTTOTAL )
         END AS VLUNITCOST
      FROM GNSTOREROOM GNSTM_PLACE
         INNER JOIN GNSTOREROOM GNSTM_STOREROOM2 ON (
            GNSTM_PLACE.CDSTOREROOM = GNSTM_STOREROOM2.CDSTOREROOM
            AND GNSTM_PLACE.FGTYPE = 1
            OR GNSTM_PLACE.CDSTOREROOMREF = GNSTM_STOREROOM2.CDSTOREROOM
         )
         INNER JOIN GNSTOREROOMOBJECTCOST GNSTM_PLACE_STOCK ON (
            GNSTM_PLACE.CDSTOREROOM = GNSTM_PLACE_STOCK.CDSTMPLACE
         )
      GROUP BY
         GNSTM_STOREROOM2.CDSTOREROOM,
         GNSTM_PLACE_STOCK.CDOBJECT
   ) STOCKVIEW ON ( GNSTMITSUP.CDOBJECT = STOCKVIEW.CDOBJECT AND GNSTMOBJ.CDSTOREROOM = STOCKVIEW.CDSTOREROOM )
WHERE (
   EXISTS(
      SELECT 1
      FROM
         ITITEM CHKIT
      WHERE
         CHKIT.CDITEM = OBOBJ.CDOBJECT
      UNION
      SELECT 1
      FROM
         WMNEWWASTE CHKWST
      WHERE
         CHKWST.CDWASTE = OBOBJ.CDOBJECT
      UNION
      SELECT 1
      FROM
         OBMATERIAL OBMAT
      WHERE
         OBMAT.CDMATERIAL = OBOBJ.CDOBJECT
   )
   AND STOCKVIEW.VLSTOCK <> 0 /*NOTE 1*/
)