CREATE OR REPLACE TRIGGER ATUALIZA_PRODUTO_PARAMPRODSUP
AFTER INSERT
ON PRSUBGRPROD FOR EACH ROW
DECLARE
   V_CODIGO           VARCHAR2(20);
   V_COUNT            NUMBER;
   V_CDFORNOPELOG     VARCHAR2(21);
   V_IDUTILCONSSOLIOL VARCHAR2(1);
   V_CONTAPART        FLOAT := 0;
BEGIN
   IF :NEW.CDFILISUBGR = 'P' THEN
      V_COUNT := 0;
      SELECT
         COUNT(1)
      INTO
         V_COUNT
      FROM
         PARASUPRI
      WHERE
         CDGRUPPROD   = :NEW.CDGRUPPROD AND
         CDSUBGRARVOR = :NEW.CDSUBGRPROD;
      
      IF V_COUNT <= 0 THEN
         FOR C_FILIALOL IN (SELECT CDFILIAL CDFILIOPELOG
                              FROM FILIAL
                             WHERE NVL(IDFUNCAOFILI, 'X') = 'O')
         LOOP
            FOR C_PARAMPRODSUP IN (SELECT CDFILIAL
                                     FROM FILIAL
                                    WHERE EXISTS (SELECT 1
                                                    FROM PARAMPRODSUP P
                                                   WHERE P.CDFILIOPELOG = C_FILIALOL.CDFILIOPELOG
                                                     AND P.CDFILIAL     = FILIAL.CDFILIAL
                                                     AND P.CDGRUPPROD   = :NEW.CDGRUPPROD
                                                     AND P.CDSUBGRPROD  = :NEW.CDSUBGRPROD
                                                     AND P.CDFILISUBGR  = 'P'))
            LOOP
               SELECT NVL(MAX(IDUTILCONSSOLIOL), 'N')
                 INTO V_IDUTILCONSSOLIOL
                 FROM PARASUPRI INNER JOIN FILIAL ON PARASUPRI.CDFILIAL = NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)
                WHERE FILIAL.CDFILIAL = C_PARAMPRODSUP.CDFILIAL;
               
               IF V_IDUTILCONSSOLIOL = 'N' THEN
                  SELECT NVL(MAX(CDFORNOPELOG), '&')
                    INTO V_CDFORNOPELOG
                    FROM FILIAL
                   WHERE CDFILIAL = C_FILIALOL.CDFILIOPELOG
                     AND NVL(IDFUNCAOFILI, 'S') = 'O';
                  
                  IF V_CDFORNOPELOG <> '&' THEN
                     DELETE
                       FROM PARTFILI
                      WHERE CDFILIAL   = C_PARAMPRODSUP.CDFILIAL
                        AND CDPRODUTO  = :NEW.CDPRODUTO
                        AND CDTIPPART  = '4';
                     INSERT INTO PARTFILI (CDPRODUTO, 
                                           CDFILIAL, 
                                           NRSEQPART, 
                                           CDTIPPART, 
                                           CDFORNECED)
                                   VALUES (:NEW.CDPRODUTO, 
                                           C_PARAMPRODSUP.CDFILIAL, 
                                           '1', 
                                           '4', 
                                           V_CDFORNOPELOG);
                     
                     DELETE FROM PARAMPRODSUP
                      WHERE CDFILIAL     =  C_PARAMPRODSUP.CDFILIAL
                        AND CDGRUPPROD   = :NEW.CDGRUPPROD
                        AND CDSUBGRPROD  = :NEW.CDSUBGRPROD
                        AND CDPRODUTO    = :NEW.CDPRODUTO
                        AND CDFILISUBGR  = 'P';
                     
                     NOVO_CODIGO_NT('PARAMPRODSUP', V_CODIGO);
                     INSERT INTO PARAMPRODSUP (NRSEQPARPROD, 
                                               CDFILIOPELOG, 
                                               CDFILIAL, 
                                               CDPRODUTO, 
                                               CDGRUPPROD, 
                                               CDSUBGRPROD, 
                                               CDFILISUBGR)
                                       VALUES (V_CODIGO, 
                                               C_FILIALOL.CDFILIOPELOG,
                                               C_PARAMPRODSUP.CDFILIAL,
                                               :NEW.CDPRODUTO,
                                               :NEW.CDGRUPPROD,
                                               :NEW.CDSUBGRPROD,
                                               'P');

                  END IF;
               ELSE
                  NOVO_CODIGO_NT('PARAMPRODSUP', V_CODIGO);
                  INSERT INTO PARAMPRODSUP (NRSEQPARPROD,
                                            CDFILIOPELOG,
                                            CDFILIAL,
                                            CDPRODUTO,
                                            CDGRUPPROD,
                                            CDSUBGRPROD,
                                            CDFILISUBGR)
                                    VALUES (V_CODIGO,
                                            C_FILIALOL.CDFILIOPELOG,
                                            C_PARAMPRODSUP.CDFILIAL,
                                            :NEW.CDPRODUTO,
                                            :NEW.CDGRUPPROD,
                                            :NEW.CDSUBGRPROD,
                                            'P');
               END IF;
            END LOOP;
         END LOOP;
      END IF;
   END IF;
END;
/