
IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE
           O.NAME = 'ESTCONSOLI' AND
	   TYPE = 'V' 		  AND
	   U.UID = O.UID          AND
	   U.NAME = USER)

   DROP VIEW ESTCONSOLI
GO    

CREATE VIEW ESTCONSOLI AS                 
SELECT POSESTCONS.CDFILIAL,
       POSESTCONS.CDPRODUTO,
       POSESTCONS.DTPOSESTCON,
       POSESTCONS.DTPOSESTCON DTULTAUTFOR,
       POSESTCONS.DTPOSESTCON DTULTLISTPRE,
       PRODFILI.DTULTCOMPR,
       POSESTCONS.QTESTDIACON,
       POSESTCONS.QTESTANTCON,
       POSESTCONS.VRESTDIACON,
       POSESTCONS.VRESTANTCON,
       POSESTCONS.VRESTBRUTCON,
       POSESTCONS.VRBRUTANTCON,
       POSESTCONS.IDPOSZEROCON,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTANTCON/POSESTCONS.QTESTANTCON)
                        END
            ELSE
               (POSESTCONS.VRESTDIACON/POSESTCONS.QTESTDIACON) END AS VRCUST1,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE(POSESTCONS.VRBRUTANTCON/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCON/POSESTCONS.QTESTDIACON) END AS VRCUST2,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTANTCON/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTDIACON/POSESTCONS.QTESTDIACON) END AS VRCUST3,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                            WHEN 0 THEN 0
                            ELSE (POSESTCONS.VRBRUTANTCON/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCON/POSESTCONS.QTESTDIACON) END AS VRCUST4,
       PRODFILI.VRULTCOMPR   AS VRCUST5,
       POSESTCONS.VRCUSPRLISTP AS VRCUST6,
       POSESTCONS.VRPRCUSTOAF AS VRCUST7,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTANTCOM1/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTDIACOM1/POSESTCONS.QTESTDIACON) END AS VRCUST20,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTANTCOM2/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTDIACOM2/POSESTCONS.QTESTDIACON) END AS VRCUST21,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRBRUTANTCM1/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCM1/POSESTCONS.QTESTDIACON) END AS VRCUST22,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRBRUTANTCM2/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCM2/POSESTCONS.QTESTDIACON) END AS VRCUST23,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTDIACOM1/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTDIACOM1/POSESTCONS.QTESTDIACON) END AS VRCUST24,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE (POSESTCONS.VRESTDIACOM2/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTDIACOM2/POSESTCONS.QTESTDIACON) END AS VRCUST25,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                           WHEN 0 THEN 0
                           ELSE(POSESTCONS.VRESTBRUTCM1/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCM1/POSESTCONS.QTESTDIACON) END AS VRCUST26,
       CASE ISNULL(POSESTCONS.QTESTDIACON, 0)
            WHEN 0 THEN CASE ISNULL(POSESTCONS.QTESTANTCON, 0)
                            WHEN 0 THEN 0
                            ELSE (POSESTCONS.VRESTBRUTCM2/POSESTCONS.QTESTANTCON)
                        END
            ELSE (POSESTCONS.VRESTBRUTCM2/POSESTCONS.QTESTDIACON) END AS VRCUST27,
       PRODFILI.VRULTCOMPRM1 AS VRCUST28,
       PRODFILI.VRULTCOMPRM2 AS VRCUST29,
       POSESTCONS.VRCUSPRLISM1 AS VRCUST30,
       POSESTCONS.VRCUSPRLISM2 AS VRCUST31,
       POSESTCONS.VRPRCUSAFM1 AS VRCUST32,
       POSESTCONS.VRPRCUSAFM2 AS VRCUST33
FROM POSESTCONS, PRODFILI
WHERE (POSESTCONS.CDFILIAL    = PRODFILI.CDFILIAL)  AND
      (POSESTCONS.CDPRODUTO   = PRODFILI.CDPRODUTO)
GO

