/****** Object:  View [dbo].[V_EST_PROD_TRANSF_IT_PRE_FATURA_ITENS]    Script Date: 11/12/2013 12:05:49 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_TRANSF_IT_PRE_FATURA_ITENS]'))
DROP VIEW [dbo].[V_EST_PROD_TRANSF_IT_PRE_FATURA_ITENS]
GO
--SELECT * FROM V_EST_PROD_TRANSF_IT_PRE_FATURA_ITENS WHERE CD_CTR = 424472
CREATE VIEW V_EST_PROD_TRANSF_IT_PRE_FATURA_ITENS AS
SELECT
    CD_EMP,
    CD_FILIAL,
    CD_CTR AS CD_CTR,
    SUM(CD_PED) AS CD_PED,
    CD_PROD,
    SUM(QT_SOL) AS QT_SOL,
    SUM(QT_AT) AS QT_AT,
    SUM(QT_PRE) AS QT_PRE
FROM
(SELECT
    CD_EMP,
    CD_FILIAL,
    CD_CTR,
    0 AS CD_PED,
    CD_PROD,
    QT_SOL,
    QT_AT,
    0 AS QT_PRE
FROM
EST_PROD_TRANSF_IT 
UNION ALL
SELECT
    I.CD_EMP,
    I.CD_FILIAL,
    T.CD_CTR,
    I.CD_PED,
    I.CD_PROD,
    0 AS QT_SOL,
    0 AS QT_AT,
    I.QT_IT AS QT_PRE
FROM
    EST_PED_VD_IT I INNER JOIN  EST_PROD_TRANSF_PED_VD T ON
    I.CD_EMP = T.CD_EMP AND
    I.CD_FILIAL = T.CD_FILIAL AND
    I.CD_PED = T.CD_PED) rs
GROUP BY
    CD_EMP,
    CD_FILIAL,
    CD_CTR,
    CD_PROD