/* Sistemas Integrados TECNISA
   Compras

Nome: INSERE_PRODFORNCMP_MARCFORNCMP

Descriiio:
Inseriio nas tabelas PRODFORNCOMP e MARCPRODCOMP

P_CDFORNECED - Fornecedor
P_CDMARCA    - Marca
P_CDPRODUTO  - Produto

Para aplicar e debugar
 @C:\WIN_V4\SQL\END_CONV\ORACLE\8770_CMPSTPO.SQL;

Criaiio:
24/08/2004 - Marcelo Alexsandro Souza Silva
*/

CREATE OR REPLACE
PROCEDURE INSERE_PRODFORNCMP_MARCFORNCMP
  (P_CDFORNECED VARCHAR2,
   P_CDMARCA    VARCHAR2,
   P_CDPRODUTO  VARCHAR2,
   P_CDARVPROD  VARCHAR2,
   P_ARRED      IN VARCHAR2 DEFAULT NULL,
   P_CDFORNORIG IN VARCHAR2 DEFAULT NULL,
   P_VRARREDEMPE IN FLOAT DEFAULT NULL,
   P_VRARREDCMPFO IN FLOAT DEFAULT NULL,
   P_VRPERPIPRFO IN FLOAT DEFAULT NULL, 
   P_VRPERVARPREPRFO IN FLOAT DEFAULT NULL,
   P_NRDIASVALPRFORN IN FLOAT DEFAULT NULL)
IS
BEGIN
   FOR CUR_CONTPROD IN
      (SELECT
          CDPRODUTO AS CDPRODCOMPL
       FROM
          PRODUTO
       WHERE
          (P_CDARVPROD  = CDARVPROD OR
           CDARVPROD LIKE P_CDARVPROD || '%') AND
           CDPRODESTO IS NOT NULL
       )
   LOOP
      IF P_CDFORNECED IS NOT NULL THEN
         IF (P_ARRED IS NOT NULL) THEN
            IF (P_ARRED = 'E') THEN
               IF (P_CDFORNORIG IS NOT NULL) THEN
                  INSERT INTO PRODFORNCOMP
                    (CDFORNECED, CDPRODUTO, CDPRODCOMPL, VRARREDEMPE)
                 (SELECT
                     P_CDFORNECED,
                     P_CDPRODUTO,
                     CUR_CONTPROD.CDPRODCOMPL,
                     P.VRARREDEMPE
                  FROM
                     PRODFORNCOMP P
                  WHERE
                     P.CDPRODCOMPL = CUR_CONTPROD.CDPRODCOMPL AND
                     P.CDFORNECED  = P_CDFORNORIG             AND
                     P.CDPRODUTO   = P_CDPRODUTO              AND
                     NOT EXISTS(SELECT
                                   CDFORNECED,
                                   CDPRODUTO,
                                   CDPRODCOMPL
                                FROM
                                   PRODFORNCOMP
                                WHERE
                                   CDFORNECED   = P_CDFORNECED AND
                                   CDPRODCOMPL  = CUR_CONTPROD.CDPRODCOMPL));
               ELSE
                  INSERT INTO PRODFORNCOMP
                     (CDFORNECED, CDPRODUTO, CDPRODCOMPL)
                  (SELECT
                      P_CDFORNECED,
                      P_CDPRODUTO,
                      CUR_CONTPROD.CDPRODCOMPL
                   FROM
                      PARAMGERAL
                   WHERE
                      NOT EXISTS(SELECT
                                    CDFORNECED,
                                    CDPRODUTO,
                                    CDPRODCOMPL
                                 FROM
                                    PRODFORNCOMP
                                 WHERE
                                    CDFORNECED   = P_CDFORNECED AND
                                    CDPRODCOMPL  = CUR_CONTPROD.CDPRODCOMPL));
               END IF;
            ELSE
               INSERT INTO PRODFORNCOMP
                 (CDFORNECED, CDPRODUTO, CDPRODCOMPL, VRARREDEMPE, 
                  VRARREDCMPFO, VRPERPIPRFO, VRPERVARPREPRFO, NRDIASVALPRFORN)
              (SELECT
                  P_CDFORNECED,
                  P_CDPRODUTO,
                  CUR_CONTPROD.CDPRODCOMPL,
                  NVL(P_VRARREDEMPE, P.VRARREDEMPPR),
                  P_VRARREDCMPFO, P_VRPERPIPRFO, P_VRPERVARPREPRFO, P_NRDIASVALPRFORN
               FROM
                  PRODUTO P
               WHERE
                  P.CDPRODUTO   = CUR_CONTPROD.CDPRODCOMPL AND
                  NOT EXISTS(SELECT
                                CDFORNECED,
                                CDPRODUTO,
                                CDPRODCOMPL
                             FROM
                                PRODFORNCOMP
                             WHERE
                                CDFORNECED   = P_CDFORNECED AND
                                CDPRODCOMPL  = CUR_CONTPROD.CDPRODCOMPL));
            END IF;
         ELSE
            INSERT INTO PRODFORNCOMP
               (CDFORNECED, CDPRODUTO, CDPRODCOMPL)
            (SELECT
                P_CDFORNECED,
                P_CDPRODUTO,
                CUR_CONTPROD.CDPRODCOMPL
             FROM
                PARAMGERAL
             WHERE
                NOT EXISTS(SELECT
                              CDFORNECED,
                              CDPRODUTO,
                              CDPRODCOMPL
                           FROM
                              PRODFORNCOMP
                           WHERE
                              CDFORNECED   = P_CDFORNECED AND
                              CDPRODCOMPL  = CUR_CONTPROD.CDPRODCOMPL));
         END IF;
      END IF;
      IF P_CDMARCA IS NOT NULL THEN
         INSERT INTO MARCPRODCOMP
            (CDMARCA, CDPRODUTO, CDPRMARCOMPL)
         (SELECT
             P_CDMARCA,
             P_CDPRODUTO,
             CUR_CONTPROD.CDPRODCOMPL
         FROM
             PARAMGERAL
         WHERE
             NOT EXISTS(SELECT
                           CDMARCA,
                           CDPRODUTO,
                           CDPRMARCOMPL
                        FROM
                           MARCPRODCOMP
                        WHERE
                           CDMARCA      = P_CDMARCA    AND
                           CDPRMARCOMPL = CUR_CONTPROD.CDPRODCOMPL));
      END IF;
      COMMIT;
   END LOOP;
END;
/