IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_PRECO_TBL]')
          AND type IN(N'P', N'PC')
)
    DROP PROCEDURE [dbo].[P_EST_PROD_PRECO_TBL];
GO

CREATE PROCEDURE P_EST_PROD_PRECO_TBL(@CD_EMP       INT,
                                      @CD_CTR_GRP   INT,
                                      @CD_PROD      INT,
                                      @VLR_FORN     MONEY,
                                      @VLR_VD       MONEY,
                                      @CD_USU       INT,
                                      @CD_ALT_PRECO INT   = 0)
AS
     BEGIN
         IF EXISTS
         (
             SELECT CD_PROD
             FROM EST_PROD_PRECO_TBL
             WHERE CD_EMP = @CD_EMP
                   AND CD_PROD = @CD_PROD
                   AND CD_CTR_GRP = @CD_CTR_GRP
         )
             BEGIN
                 UPDATE EST_PROD_PRECO_TBL
                   SET
                       VLR_FORN = @VLR_FORN,
                       VLR_VD = @VLR_VD,
                       CD_USU = @CD_USU,
                       DT_ULT_ALT = CONVERT( CHAR(10), GETDATE(), 112)
                 WHERE CD_EMP = @CD_EMP
                       AND CD_CTR_GRP = @CD_CTR_GRP
                       AND CD_PROD = @CD_PROD;
             END;
         ELSE
             BEGIN
                 INSERT INTO EST_PROD_PRECO_TBL
                 (CD_EMP
                , CD_PROD
                , VLR_FORN
                , VLR_VD
                , CD_USU
                , DT_CAD
                , DT_ULT_ALT
                , CD_CTR_GRP
                 )
                 VALUES
                 (@CD_EMP,
                  @CD_PROD,
                  @VLR_FORN,
                  @VLR_VD,
                  @CD_USU,
                  CONVERT( CHAR(10), GETDATE(), 112),
                  NULL,
                  @CD_CTR_GRP
                 );
             END;
         IF @CD_ALT_PRECO = 0
             BEGIN
                 SET @CD_ALT_PRECO = ISNULL(
                                           (
                                               SELECT MAX(CD_ALT_PRECO)
                                               FROM EST_PROD_PRECO_ALT
                                           ), 0) + 1;
             END;
         INSERT INTO EST_PROD_PRECO_ALT
         (CD_EMP
        , CD_ALT_PRECO
        , TP_ALT_PRECO
        , ZERO_OFERTA
        , DT_CAD
        , CD_USU
        , DS_ALT_PRECO
        , APLICADO
        , DT_APLICACAO
        , DT_AGENDADA
         )
                SELECT @CD_EMP
                     , @CD_ALT_PRECO
                     , 0
                     , 0
                     , CONVERT(                       CHAR(10), GETDATE(), 112)
                     , @CD_USU
                     , 'ALTERACAO DE PRECOS PMC, GRUPO DE PREÇOS: '+CONVERT(VARCHAR(10),@CD_CTR_GRP)+ ', ÀS '+CONVERT( CHAR(20), GETDATE(), 120)
                     , 0
                     , GETDATE()
                     , GETDATE();
         INSERT INTO EST_PROD_PRECO_ALT_CPL
         (CD_EMP
        , CD_ALT_PRECO
        , CD_PROD
        , VLR_TABELA
        , VLR_OFERTA
         )
                SELECT @CD_EMP
                     , @CD_ALT_PRECO
                     , @CD_PROD
                     , @VLR_VD
                     , 0;
         INSERT INTO EST_PROD_PRECO_ALT_PRC_FILIAL
         (CD_EMP
        , CD_ALT_PRECO
        , CD_FILIAL
         )
                SELECT @CD_EMP
                     , @CD_ALT_PRECO
                     , CD_FILIAL
                FROM EST_PROD_PRECO_TBL_GRUPO_PRC_FILIAL
                WHERE CD_CTR_GRP = @CD_CTR_GRP;
     END;