CREATE OR REPLACE PROCEDURE GRAVA_CURVA_ABC_CLIENTE(
   P_EMPRESA          IN VARCHAR2,
   P_CLIENTE          IN VARCHAR2,
   P_TIPOCONTA        IN VARCHAR2,
   P_STATUS           IN VARCHAR2,
   P_DTINIVENC        IN CONTARECEBER.DTATUAVENREC%TYPE,
   P_DTFINVENC        IN CONTARECEBER.DTATUAVENREC%TYPE,
   P_DTINIBAIXA       IN BAIXARECEBIDA.DTBAIXAREC%TYPE,
   P_DTFINBAIXA       IN BAIXARECEBIDA.DTBAIXAREC%TYPE,
   P_TDTBAIXAREC      IN VARCHAR2,
   P_IDCURVAGERAL     IN VARCHAR2,
   P_NRSEQCURVAABCCLI OUT NUMBER)
AS
   V_NRSEQCURVAABCCLI NUMBER;
   V_NRSEQCURVAABC    NUMBER;
   V_VRPECURVAA       FLOAT;
   V_VRPECURVAB       FLOAT;
   V_VRPECURVAC       FLOAT;
   V_PERCANT          FLOAT;
   V_PERCCLIE         FLOAT;
   V_IDTPCURVAABC     VARCHAR2(01);
   V_TOTPERCA         FLOAT;
   V_DTINI      DATE;
   V_DTFIN      DATE;

BEGIN
   /*APAGA OS DAOS DE UMA GERACAO ANTERIOR DO RELATORIO*/
   DELETE FROM CURVAABCCLITEMP;

   /*GERA O SEQUENCIAL PRA EXECUCAO DO RELATORIO*/
   NOVO_CODIGO_NT('CURVAABCCLITEMP', V_NRSEQCURVAABCCLI);

   /*BUSCA OS VALORES DE REFERENCIA PARA CURVA A, B E C*/
   
   
   
   BEGIN
      SELECT VRPECURVAA, VRPECURVAB, VRPECURVAC
        INTO V_VRPECURVAA, V_VRPECURVAB, V_VRPECURVAC
        FROM PARAMGERAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRPECURVAA := 0;
         V_VRPECURVAB := 0;
         V_VRPECURVAC := 0;
   END;



   V_NRSEQCURVAABC := 0;
   
   
   V_DTINI := TO_DATE(P_DTINIVENC);
   V_DTFIN := TO_DATE(P_DTFINVENC);--agora vamos ver se essa merda nao traz
   

   /*BUSCA O TOTAL DO PERIODO POR EMPRESA*/
   FOR C_TOTEMPR IN (SELECT DECODE(P_IDCURVAGERAL,'G','G',EM.CDEMPRESA) AS CDEMPRESA, SUM(CR.VRATUAREC) VRTOTAL
                       FROM FILIAL FI, BAIXARECEBIDA BR, BOLETRECEBER  BO, NOTAFISCAL NF,
                           (SELECT CDCLIENTE
                              FROM CLIENTE
                             WHERE (P_CLIENTE   = 'T' OR INSTR(P_CLIENTE, CDCLIENTE) <> 0)) CL,
                           (SELECT CDFILIAL, NRLANCTONF, CDEMPRESA, CDFILIBOLREC, NRLANBOLREC, CDTPCTRECEB, VRATUAREC
                              FROM CONTARECEBER
                             WHERE VRATUAREC       <> 0
                               AND (P_STATUS = 'T' OR INSTR(P_STATUS, IDSTATREC) <> 0)
                               AND (P_TIPOCONTA = 'T' OR INSTR(P_TIPOCONTA, CDTPCTRECEB) <> 0)
                               AND (DTATUAVENREC BETWEEN TO_DATE(V_DTINI) AND TO_DATE(V_DTFIN)))  CR,
                           (SELECT CDEMPRESA
                              FROM EMPRESA
                             WHERE (P_EMPRESA = 'T' OR INSTR(P_EMPRESA, CDEMPRESA) <> 0)) EM
                      WHERE NF.CDFILIAL      = CR.CDFILIAL
                        AND NF.NRLANCTONF    = CR.NRLANCTONF
                        AND FI.CDFILIAL      = NF.CDFILIEMIT
                        AND CL.CDCLIENTE     = NF.CDCLIENTE
                        AND CR.CDEMPRESA     = EM.CDEMPRESA
                        AND BO.CDFILIAL      = CR.CDFILIBOLREC
                        AND BO.NRLANBOLREC   = CR.NRLANBOLREC
                        AND BR.CDFILIAL(+)   = BO.CDFILIBAIREC
                        AND BR.NRBAIXAREC(+) = BO.NRBAIXAREC
                        AND NF.IDSTATUS      IN ('A', 'E')
                        AND ((BR.DTBAIXAREC   BETWEEN NVL(RTRIM(P_DTINIBAIXA), DTBAIXAREC) AND NVL(RTRIM(P_DTFINBAIXA), DTBAIXAREC)) OR (P_TDTBAIXAREC = 'T'))
                      GROUP BY DECODE(P_IDCURVAGERAL,'G','G',EM.CDEMPRESA)) LOOP

      V_PERCANT  := 0;
      V_PERCCLIE := 0;
      V_TOTPERCA := 0;

      /*BUSCA O VALOR TOTAL DE CADA CLIENTE*/
      FOR C_TOTCLIE IN (SELECT CL.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMRAZSOCCLIE, COUNT(*) NRTOTCLIE, SUM(CR.VRATUAREC) VRTOTCLIE
                          FROM FILIAL FI, BAIXARECEBIDA BR, BOLETRECEBER BO, NOTAFISCAL NF,
                               (SELECT CDCLIENTE, NRINSJURCLIE, IDTPIJURCLIE, NMRAZSOCCLIE
                                  FROM CLIENTE
                                 WHERE (P_CLIENTE   = 'T' OR INSTR(P_CLIENTE, CDCLIENTE) <> 0)) CL,
                               (SELECT CDFILIAL, NRLANCTONF, CDEMPRESA, CDFILIBOLREC, NRLANBOLREC, CDTPCTRECEB, VRATUAREC
                                  FROM CONTARECEBER
                                 WHERE VRATUAREC       <> 0
                                   AND (P_STATUS = 'T' OR INSTR(P_STATUS, IDSTATREC) <> 0)
                                   AND (P_TIPOCONTA = 'T' OR INSTR(P_TIPOCONTA, CDTPCTRECEB) <> 0)
                                   AND (DTATUAVENREC BETWEEN TO_DATE(V_DTINI) AND TO_DATE(V_DTFIN)))  CR,
                               (SELECT CDEMPRESA
                                  FROM EMPRESA
                                 WHERE (C_TOTEMPR.CDEMPRESA = 'G' OR C_TOTEMPR.CDEMPRESA = CDEMPRESA)) EM
                         WHERE NF.CDFILIAL      = CR.CDFILIAL
                           AND NF.NRLANCTONF    = CR.NRLANCTONF
                           AND FI.CDFILIAL      = NF.CDFILIEMIT
                           AND CL.CDCLIENTE     = NF.CDCLIENTE
                           AND CR.CDEMPRESA     = EM.CDEMPRESA
                           AND BO.CDFILIAL      = CR.CDFILIBOLREC
                           AND BO.NRLANBOLREC   = CR.NRLANBOLREC
                           AND BR.CDFILIAL(+)   = BO.CDFILIBAIREC
                           AND BR.NRBAIXAREC(+) = BO.NRBAIXAREC
                           AND NF.IDSTATUS      IN ('A', 'E')
                           AND ((BR.DTBAIXAREC   BETWEEN NVL(RTRIM(P_DTINIBAIXA), DTBAIXAREC) AND NVL(RTRIM(P_DTFINBAIXA), DTBAIXAREC)) OR (P_TDTBAIXAREC = 'T'))
                         GROUP BY CL.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMRAZSOCCLIE
                         ORDER BY VRTOTCLIE DESC) LOOP

         V_PERCANT  := V_PERCANT + V_PERCCLIE;
         V_PERCCLIE := (C_TOTCLIE.VRTOTCLIE * 100)/ C_TOTEMPR.VRTOTAL;

         IF V_PERCANT <= V_VRPECURVAA THEN
            V_IDTPCURVAABC := 'A';
            V_TOTPERCA := V_TOTPERCA + V_PERCCLIE;
         ELSIF (V_PERCANT - V_TOTPERCA) <= V_VRPECURVAB THEN
            V_IDTPCURVAABC := 'B';
         ELSE
            V_IDTPCURVAABC := 'C';
         END IF;

         FOR C_DETCLIE IN (SELECT EM.CDEMPRESA, EM.NMEMPRESA,TR.CDTPCTRECEB, TR.DSTPCTRECEB, FI.NMFILIAL, NF.CDFILIEMIT, BR.DTBAIXAREC, CR.DSCONTAREC,
                                  CR.DTATUAVENREC, CR.VRATUAREC, BO.VRFINALBREC, CR.IDSTATREC, NF.NRNOTAFISC, NF.CDSERIE,
                                  NF.DTEMISSAO, (CR.VRATUAREC * 100)/ C_TOTEMPR.VRTOTAL AS VRPERC
                             FROM TIPOCTRECEBER TR, FILIAL FI, BAIXARECEBIDA BR, BOLETRECEBER  BO, NOTAFISCAL NF,
                                  (SELECT CDFILIAL, NRLANCTONF, CDEMPRESA, CDFILIBOLREC, NRLANBOLREC, CDTPCTRECEB, VRATUAREC,
                                          DSCONTAREC, DTATUAVENREC, IDSTATREC
                                     FROM CONTARECEBER
                                    WHERE VRATUAREC       <> 0
                                      AND (P_STATUS = 'T' OR INSTR(P_STATUS, IDSTATREC) <> 0)
                                      AND (P_TIPOCONTA = 'T' OR INSTR(P_TIPOCONTA, CDTPCTRECEB) <> 0)
                                      AND (DTATUAVENREC BETWEEN TO_DATE(V_DTINI) AND TO_DATE(V_DTFIN)))  CR,
                                  (SELECT CDEMPRESA, NMEMPRESA
                                     FROM EMPRESA
                                    WHERE (C_TOTEMPR.CDEMPRESA = 'G' OR C_TOTEMPR.CDEMPRESA = CDEMPRESA)) EM
                            WHERE NF.CDFILIAL      = CR.CDFILIAL
                              AND NF.NRLANCTONF    = CR.NRLANCTONF
                              AND FI.CDFILIAL      = NF.CDFILIEMIT
                              AND NF.CDCLIENTE     = C_TOTCLIE.CDCLIENTE
                              AND CR.CDEMPRESA     = EM.CDEMPRESA
                              AND BO.CDFILIAL      = CR.CDFILIBOLREC
                              AND BO.NRLANBOLREC   = CR.NRLANBOLREC
                              AND BR.CDFILIAL(+)   = BO.CDFILIBAIREC
                              AND BR.NRBAIXAREC(+) = BO.NRBAIXAREC
                              AND TR.CDTPCTRECEB   = CR.CDTPCTRECEB
                              AND NF.IDSTATUS      IN ('A', 'E')
                              AND ((BR.DTBAIXAREC   BETWEEN NVL(RTRIM(P_DTINIBAIXA), DTBAIXAREC) AND NVL(RTRIM(P_DTFINBAIXA), DTBAIXAREC)) OR (P_TDTBAIXAREC = 'T'))
                            ORDER BY EM.CDEMPRESA, CR.VRATUAREC DESC, CR.DTATUAVENREC) LOOP

             V_NRSEQCURVAABC := NVL(V_NRSEQCURVAABC,0) + 1;
             INSERT INTO CURVAABCCLITEMP
                (NRSEQCURVAABCCLI, NRSEQCURVAABC, CDEMPRESA, CDCLIENTE, IDCURVAGERAL, NRINSJURCLIE,
                 IDTPIJURCLIE, NMRAZSOCCLIE, NMEMPRESA, CDFILIEMIT, NMFILIAL,
                 DTBAIXAREC, DSCONTAREC, DTATUAVENREC, VRATUAREC, VRFINALBREC,
                 IDSTATREC, NRNOTAFISC, CDSERIE, DTEMISSAO, VRPERC, VRTOTAL,
                 VRTOTCLIE, NRTOTCLIE, VRPERCCLIE, VRPERCANT, IDTPCURVAABC, CDTPCTRECEB, DSTPCTRECEB)
            VALUES
                (V_NRSEQCURVAABCCLI, V_NRSEQCURVAABC, C_DETCLIE.CDEMPRESA, C_TOTCLIE.CDCLIENTE, P_IDCURVAGERAL, C_TOTCLIE.NRINSJURCLIE,
                 C_TOTCLIE.IDTPIJURCLIE, C_TOTCLIE.NMRAZSOCCLIE, C_DETCLIE.NMEMPRESA, C_DETCLIE.CDFILIEMIT, C_DETCLIE.NMFILIAL,
                 C_DETCLIE.DTBAIXAREC, C_DETCLIE.DSCONTAREC, C_DETCLIE.DTATUAVENREC, C_DETCLIE.VRATUAREC, C_DETCLIE.VRFINALBREC,
                 C_DETCLIE.IDSTATREC, C_DETCLIE.NRNOTAFISC, C_DETCLIE.CDSERIE, C_DETCLIE.DTEMISSAO, C_DETCLIE.VRPERC, C_TOTEMPR.VRTOTAL,
                 C_TOTCLIE.VRTOTCLIE, C_TOTCLIE.NRTOTCLIE, V_PERCCLIE, V_PERCANT, V_IDTPCURVAABC, C_DETCLIE.CDTPCTRECEB, C_DETCLIE.DSTPCTRECEB);
         END LOOP;
      END LOOP;
      
   END LOOP;

   P_NRSEQCURVAABCCLI := V_NRSEQCURVAABCCLI;
   COMMIT;
END;
/