/* 8720_SUPSTP_ATUALIZA_PARTFILI */
IF EXISTS (SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'ATUALIZA_PARTFILI'  and
            type = 'TR'                       and
            u.uid = o.uid                    and
            u.name = USER)

   DROP TRIGGER ATUALIZA_PARTFILI;
GO

CREATE TRIGGER ATUALIZA_PARTFILI
ON PRSUBGRPROD
FOR INSERT

AS

DECLARE @V_NEW_CDFILISUBGR VARCHAR(4);
DECLARE @V_NEW_CDGRUPPROD  VARCHAR(10);
DECLARE @V_NEW_CDSUBGRPROD VARCHAR(10);
DECLARE @V_NEW_CDPRODUTO VARCHAR(10);

BEGIN
   DECLARE C_TRG_PRSUBGRPROD CURSOR FOR
    SELECT
      CDFILISUBGR,
      CDGRUPPROD,
      CDSUBGRPROD,
      CDPRODUTO
    FROM
      INSERTED
    WHERE
      CDFILISUBGR = 'P' AND
      0 = (SELECT COUNT(*)
           FROM DELETED)

   OPEN C_TRG_PRSUBGRPROD;
   FETCH NEXT FROM C_TRG_PRSUBGRPROD INTO @V_NEW_CDFILISUBGR,
                                          @V_NEW_CDGRUPPROD,
                                          @V_NEW_CDSUBGRPROD,
                                          @V_NEW_CDPRODUTO;

   WHILE (@@FETCH_STATUS = 0) BEGIN

     INSERT INTO PARTFILI
        (CDFILIAL, CDPRODUTO, CDTIPPART, NRSEQPART, CDFORNECED, CDFILISUBGR, CDGRUPPROD, CDSUBGRPROD, CDFILCOTA, NRCOLEPRECO)
     SELECT DISTINCT
        PARTFILI.CDFILIAL, @V_NEW_CDPRODUTO, '4', '1', PARTFILI.CDFORNECED, @V_NEW_CDFILISUBGR, @V_NEW_CDGRUPPROD, @V_NEW_CDSUBGRPROD, PARTFILI.CDFILCOTA, PARTFILI.NRCOLEPRECO
     FROM
        PARTFILI,
        FILIAL,
        PARASUPRI
     WHERE
        PARTFILI.CDFILIAL   = FILIAL.CDFILIAL AND
        PARASUPRI.CDFILIAL = ISNULL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL) AND
        PARASUPRI.CDGRUPPROD = @V_NEW_CDGRUPPROD AND
        PARTFILI.CDGRUPPROD = @V_NEW_CDGRUPPROD AND
        PARTFILI.CDSUBGRPROD = @V_NEW_CDSUBGRPROD AND
        PARTFILI.CDTIPPART = '4' AND
        NOT EXISTS(SELECT P.CDFILIAL
                   FROM PARTFILI P
                   WHERE
                      P.CDFILIAL = PARTFILI.CDFILIAL AND
                      P.CDPRODUTO = @V_NEW_CDPRODUTO AND
                      P.CDTIPPART = PARTFILI.CDTIPPART AND
                      P.NRSEQPART = PARTFILI.NRSEQPART);

     FETCH NEXT FROM C_TRG_PRSUBGRPROD INTO @V_NEW_CDFILISUBGR,
                                            @V_NEW_CDGRUPPROD,
                                            @V_NEW_CDSUBGRPROD,
                                            @V_NEW_CDPRODUTO;
   END;
   CLOSE C_TRG_PRSUBGRPROD;
   DEALLOCATE C_TRG_PRSUBGRPROD;

END;
GO


-- VERSAO --
EXEC ATUVEROBJ 'ATUALIZA_PARTFILI','4.11.016';
GO

