CREATE OR REPLACE PROCEDURE DETERMINA_CKLISTPRODINF_COMPL
(
P_CDFILIAL IN VARCHAR2,
P_NRLISTA_CKLISTS IN VARCHAR2,   
P_DTPRODUCAO IN DATE,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER
) 
AS
   V_EXISTE_PREVISTO NUMBER;
   V_IDTIPOPLAN VARCHAR2(100);
   V_NRCKLISTPRODINF NUMBER;
   V_QTRECEITA_JA_PLANEJADA FLOAT;
BEGIN
   V_IDTIPOPLAN := 'PREVISTO';
   /*Identifica se e complementar*/   
   SELECT COUNT(1)
     INTO V_EXISTE_PREVISTO
     FROM CKLISTPRODINF c
          LEFT JOIN( SELECT CDLISTA FROM LISTA L WHERE L.NRLISTA = P_NRLISTA_CKLISTS ) L ON TO_NUMBER(L.CDLISTA) = C.NRCKLISTPRODINF
    WHERE C.CDFILIAL = P_CDFILIAL
      AND C.DTPRODUCAOCALC = P_DTPRODUCAO
      AND C.IDTIPOPLAN = 'PREVISTO'
      AND L.CDLISTA IS NULL;

   IF V_EXISTE_PREVISTO > 0 THEN
      V_IDTIPOPLAN := 'COMPLEMENTAR';
   END IF;

   IF V_IDTIPOPLAN = 'COMPLEMENTAR' THEN
      
      UPDATE CKLISTPRODINF C
         SET IDTIPOPLAN = 'COMPLEMENTAR'
       WHERE EXISTS (SELECT 1
                       FROM LISTA L
                      WHERE L.NRLISTA = P_NRLISTA_CKLISTS
                        AND TO_NUMBER(L.CDLISTA) = C.NRCKLISTPRODINF);

      FOR CUR_RECEITAS IN
      (
      SELECT D.CDFILIAL, D.NRDESTCKLISTPRODINF, R.CDRECEITA, D.DTATENDIMENTO, D.CDCIA, D.NRVOO, D.CDTRECHO, D.CDCLASSE, D.CDCICLO, D.NRSERVICOCIAFILI,
             C.NRTIPOPROD, D.CDRECEITADEST
        FROM CKLISTPRODINF C
             INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                   C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
             INNER JOIN RECCKLISTPRODINF R ON R.CDFILIAL = C.CDFILIAL AND
                                              R.NRCKLISTPRODINF = C.NRCKLISTPRODINF
             INNER JOIN DESTCKLISTPRODINF D ON D.CDFILIAL = R.CDFILIAL AND
                                               D.NRRECCKLISTPRODINF = R.NRRECCKLISTPRODINF
       WHERE C.CDFILIAL = P_CDFILIAL
         AND L.NRLISTA = P_NRLISTA_CKLISTS
      ) LOOP
         /*Encontrar a quantidade de receita ja planejada para a data de producao*/
         SELECT SUM(D.QTRECEITACALC) AS QTRECEITA
           INTO V_QTRECEITA_JA_PLANEJADA 
           FROM DESTCKLISTPRODINF D
                INNER JOIN RECCKLISTPRODINF R ON R.CDFILIAL = D.CDFILIAL AND
                                                 R.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF
                INNER JOIN CKLISTPRODINF C ON C.CDFILIAL = R.CDFILIAL AND
                                              C.NRCKLISTPRODINF = R.NRCKLISTPRODINF
          WHERE C.CDFILIAL = P_CDFILIAL
            AND C.DTPRODUCAOCALC = P_DTPRODUCAO
            AND R.CDRECEITA = CUR_RECEITAS.CDRECEITA
            AND D.DTATENDIMENTO = CUR_RECEITAS.DTATENDIMENTO
            AND NVL(D.CDRECEITADEST, '<<<SEM RECEITA DESTINO>>>>') = NVL(CUR_RECEITAS.CDRECEITADEST, '<<<SEM RECEITA DESTINO>>>>')
            AND D.CDCIA = CUR_RECEITAS.CDCIA
            AND D.NRVOO = CUR_RECEITAS.NRVOO
            AND D.CDTRECHO = CUR_RECEITAS.CDTRECHO
            AND D.CDCLASSE = CUR_RECEITAS.CDCLASSE
            AND D.CDCICLO = CUR_RECEITAS.CDCICLO
            AND D.NRSERVICOCIAFILI = CUR_RECEITAS.NRSERVICOCIAFILI
            AND NVL(C.NRTIPOPROD, -77777) = NVL(CUR_RECEITAS.NRTIPOPROD, -77777)
            AND C.IDTIPOPLAN IN ('PREVISTO', 'COMPLEMENTAR')
            AND NOT EXISTS(SELECT 1
                             FROM LISTA L
                            WHERE L.NRLISTA = P_NRLISTA_CKLISTS
                              AND TO_NUMBER(L.CDLISTA) = C.NRCKLISTPRODINF);

         IF V_QTRECEITA_JA_PLANEJADA > 0 THEN
            UPDATE DESTCKLISTPRODINF
               SET QTRECEITACALC = QTRECEITACALC - V_QTRECEITA_JA_PLANEJADA,
                   QTRECEITAINF = QTRECEITAINF - V_QTRECEITA_JA_PLANEJADA
             WHERE CDFILIAL = P_CDFILIAL
               AND NRDESTCKLISTPRODINF = CUR_RECEITAS.NRDESTCKLISTPRODINF;               
         END IF;
      END LOOP;

      /*Apaga destinos <= 0*/
      DELETE FROM DESTCKLISTPRODINF D
       WHERE QTRECEITACALC <= 0
         AND EXISTS (SELECT 1
                       FROM RECCKLISTPRODINF R
                            INNER JOIN CKLISTPRODINF C ON C.CDFILIAL = R.CDFILIAL AND
                                                          C.NRCKLISTPRODINF = R.NRCKLISTPRODINF
                            INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                                  C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
                      WHERE R.CDFILIAL = D.CDFILIAL
                        AND R.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF);

      /*Atualiza receitas*/
      UPDATE RECCKLISTPRODINF R
         SET QTRECEITACALC = NVL((SELECT SUM(D.QTRECEITACALC)
                                FROM DESTCKLISTPRODINF D
                                     INNER JOIN RECCKLISTPRODINF RR ON RR.CDFILIAL = D.CDFILIAL AND
                                                           RR.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF
                                     INNER JOIN CKLISTPRODINF C ON C.CDFILIAL = RR.CDFILIAL AND
                                                                   C.NRCKLISTPRODINF = RR.NRCKLISTPRODINF
                                     INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                                           C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
                               WHERE R.CDFILIAL = D.CDFILIAL
                                 AND R.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF), 0),
             QTRECEITAINF = NVL((SELECT SUM(D.QTRECEITAINF)
                                FROM DESTCKLISTPRODINF D
                                     INNER JOIN RECCKLISTPRODINF RR ON RR.CDFILIAL = D.CDFILIAL AND
                                                           RR.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF
                                     INNER JOIN CKLISTPRODINF C ON C.CDFILIAL = RR.CDFILIAL AND
                                                                   C.NRCKLISTPRODINF = RR.NRCKLISTPRODINF
                                     INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                                           C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
                               WHERE R.CDFILIAL = D.CDFILIAL
                                 AND R.NRRECCKLISTPRODINF = D.NRRECCKLISTPRODINF), 0)
       WHERE EXISTS (SELECT 1
                       FROM CKLISTPRODINF C
                            INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                                  C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
                      WHERE C.CDFILIAL = R.CDFILIAL
                        AND C.NRCKLISTPRODINF = R.NRCKLISTPRODINF
                    );

      /*Apaga as receitas que nao possuem destinos*/
      DELETE FROM RECCKLISTPRODINF R
       WHERE QTRECEITACALC <= 0
         AND EXISTS (SELECT 1
                       FROM CKLISTPRODINF C
                            INNER JOIN LISTA L ON L.NRLISTA = P_NRLISTA_CKLISTS AND
                                                  C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA)
                      WHERE C.CDFILIAL = R.CDFILIAL
                        AND C.NRCKLISTPRODINF = R.NRCKLISTPRODINF);

      /*Apaga os checklists que nao possuem receitas*/
      DELETE FROM CKLISTPRODINF C
       WHERE EXISTS (SELECT 1
                       FROM LISTA L                                                  
                      WHERE L.NRLISTA = P_NRLISTA_CKLISTS
                        AND C.NRCKLISTPRODINF = TO_NUMBER(L.CDLISTA))
         AND NOT EXISTS (SELECT 1
                           FROM RECCKLISTPRODINF
                          WHERE CDFILIAL = C.CDFILIAL
                            AND NRCKLISTPRODINF = C.NRCKLISTPRODINF); 
   END IF;
END;
/