CREATE OR REPLACE PROCEDURE GERA_RETROBILL
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_DATAINI IN DATE,
P_DATAFIN IN DATE,
P_CDOPERADOR IN VARCHAR2,
P_NRRETROBILL OUT NUMBER,
P_NRORG IN NUMBER DEFAULT 1
)
AS
   V_SEQUENCIAL VARCHAR2(20);
   V_NRITRETROBILL NUMBER;
   V_IDCALCPROPDLCOM VARCHAR2(1);
   V_VRALTXPORT FLOAT;   

   V_QTSERVICO FLOAT;
   V_QTSERVICO_SBY FLOAT;
   V_QTSERVICOFAT FLOAT;
   V_QTSERVICOTOT FLOAT;

   V_QTSERVTOT_JAEMIT FLOAT;
   V_QTSERVFAT_JAEMIT FLOAT;
   V_QTSERV_JAEMIT FLOAT;
   V_VRREAL_JAEMIT FLOAT;
   V_VRDOLAR_JAEMIT FLOAT;
   V_VALOR_JAEMIT FLOAT;
   V_VRPRECO_COM_SBYNUT FLOAT;
   V_VRPRECOUNPROP FLOAT;

   V_VRPRECOANT FLOAT; 
   V_VRPRECOATU FLOAT;
   V_VRPERDESC FLOAT;
   V_VRPERSBYNUT FLOAT;
   V_VALORITEMANT FLOAT;
   V_VALORITEMATU FLOAT;
   V_TOTALITEMANT FLOAT;
   V_TOTALITEMATU FLOAT;
   V_VRPERDESCCIA FLOAT;
   V_VRPEDESCFIXO FLOAT;
   V_VRPRECOUNANT FLOAT;
   V_VRDESCONDANT FLOAT;
   V_VRDESFIXOANT FLOAT;
   V_VRDESDESVANT FLOAT;
   V_VRPRECOUNATU FLOAT;
   V_VRDESCONDATU FLOAT;
   V_VRDESFIXOATU FLOAT;
   V_VRDESDESVATU FLOAT;
   V_VRDIFERENCA FLOAT;
   V_VRTOTSBYNUTATU FLOAT;

   V_IDDESCDESV VARCHAR2(1);
   V_IDDESCCOND VARCHAR2(1);
   V_IDDESCFIXO VARCHAR2(1);

   V_TOT_ITENS NUMBER;
   V_EXISTE_EM_RETROBILL NUMBER;
BEGIN
   NOVO_CODIGO('RETROBILL' || P_CDFILIAL, V_SEQUENCIAL);
   P_NRRETROBILL := TO_NUMBER(V_SEQUENCIAL);

   SELECT MAX(NVL(VRALTXPORT, 0.00))
     INTO V_VRALTXPORT
     FROM CIAFILI CF
    WHERE CDFILIAL = P_CDFILIAL
      AND CDCIA = P_CDCIA;
   
   /* Insere retrobill com os parametros informados */
   INSERT INTO RETROBILL
   (NRRETROBILL, CDFILIAL, CDCIA, DTRETROBILL,
    DTINICIAL, DTFINAL, IDANALIZEATIV, VRRETROBILL, VRCREDITO, VRTAXAAERO,
    NRORG, IDATIVO, DTINCLUSAO, DTULTATU, NRORGINCLUSAO,
    CDOPERINCLUSAO, NRORGULTATU, CDOPERULTATU)
   VALUES
   (P_NRRETROBILL, P_CDFILIAL, P_CDCIA, TRUNC(SYSDATE),
    P_DATAINI, P_DATAFIN, 'I', 0.00, 0.00, NVL(V_VRALTXPORT,0.00),
    P_NRORG, 'S', SYSDATE, SYSDATE, P_NRORG,
    P_CDOPERADOR, P_NRORG, P_CDOPERADOR);

   V_TOT_ITENS := 0;

   FOR CUR_ITENS IN 
   (
   SELECT D.CDFILIAL, D.CDCIA, D.NRVOO, D.CDTRECHO, D.DTHRPLAVOOPV, D.NRDELIVERY, D.IDDELIVCOMPL,
          S.NRSERVICOCIAFILI, S.CDCLASSE, S.CDCICLO,
          S.IDTPPRECOSP, S.IDTIPMANUALSP,
          MAX(NVL(D.VRPERSBYUT, 0.00)) VRPERSBYUT, MAX(NVL(D.VRPERSBYNUT,0.00)) VRPERSBYNUT, MAX(NVL(D.VRALTXPORT,0.00)) VRALTXPORT,
          MAX(NVL(D.VRPERDESCFIXO, 0.00)) VRPERDESCFIXO, MAX(NVL(D.VRPERDESCDESV, 0.00)) VRPERDESCDESV, MAX(NVL(D.VRPERDESCCOND, 0.00)) VRPERDESCCOND,
          MAX(D.IDCALCPROPDLCOM) IDCALCPROPDLCOM, MAX(D.IDDESCDESV) IDDESCDESV, MAX(D.IDDESCCOND) IDDESCCOND, MAX(D.IDDESCFIXO) IDDESCFIXO,
          SUM(NVL(S.QTATUAPRODSP, 0.0)-(NVL(S.QTSESBYPRSP, 0.0)- NVL(S.QTSESBYUTSP, 0.0))) AS QTSERVICOPRECOTOTAL,
          SUM(NVL(S.QTSERFATSP, 0.0) + NVL(S.QTSESBYUTSP, 0.0)) AS QTSERVICOPRECOUNIT,
          SUM(NVL(S.QTSERFATSP, 0.0)) AS QTSERVICOFAT,
          SUM(NVL(S.QTSESBYUTSP, 0.0)) AS QTSERVICOSBYUT,
          SUM(NVL(S.QTSESBYPRSP, 0.0) - NVL(S.QTSESBYUTSP, 0.0)) AS QTSERVICOSBYNUT,
          MAX(VRPRURESESP) AS VRPRECOREAL,
          MAX(VRPRUDOLSESP) AS VRPRECODOLAR,
          MAX(VRPRUSBYSP) AS VRPRUSBYSP,       
          SUM(NVL(S.VRNUSADO, 0.0)) AS VRNUSADO,
          SUM(NVL(VRDESCONDRSP, 0.0)) AS VRDESCONDREAL,
          SUM(NVL(VRDESCONDDSP, 0.0)) AS VRDESCONDDOLAR,
          SUM(NVL(VRDESFIXORSP, 0.0)) AS VRDESFIXOREAL,
          SUM(NVL(VRDESFIXODSP, 0.0)) AS VRDESFIXODOLAR,
          SUM(NVL(VRDESDESVRSP, 0.0)) AS VRDESDESVREAL,
          SUM(NVL(VRDESDESVDSP, 0.0)) AS VRDESDESVDOLAR
    FROM DELIVERYM D
         INNER JOIN SERVPLANOM S ON S.CDFILIAL = D.CDFILIAL AND
                                    S.NRDELIVERY = D.NRDELIVERY
         INNER JOIN VOO V ON V.CDFILIAL = D.CDFILIAL AND
                             V.CDCIA = D.CDCIA AND
                             V.NRVOO = D.NRVOO                                    
   WHERE D.CDFILIAL = P_CDFILIAL
     AND D.CDCIA = P_CDCIA
     AND D.DTEMISDEL BETWEEN P_DATAINI AND P_DATAFIN
     AND S.IDSITSERSP = '2'
   GROUP BY D.CDFILIAL, D.CDCIA, D.NRVOO, D.CDTRECHO, D.DTHRPLAVOOPV, D.NRDELIVERY, D.IDDELIVCOMPL,
            S.NRSERVICOCIAFILI, S.CDCLASSE, S.CDCICLO,
            S.IDTPPRECOSP, S.IDTIPMANUALSP
   ORDER BY D.CDFILIAL, D.CDCIA, D.NRVOO, D.CDTRECHO, D.DTHRPLAVOOPV, D.IDDELIVCOMPL, D.NRDELIVERY
   ) LOOP
     /*Se o item ja foi cobrado em um retrobill passado, ignora-o e passa para o proximo*/
     SELECT COUNT(1)
       INTO V_EXISTE_EM_RETROBILL
       FROM ITRETROBILL
      WHERE CDFILIAL = P_CDFILIAL
        AND CDCIA = P_CDCIA
        AND NRSERVICOCIAFILI = CUR_ITENS.NRSERVICOCIAFILI
        AND CDCLASSE = CUR_ITENS.CDCLASSE
        AND CDCICLO = CUR_ITENS.CDCICLO
        AND NRDELIVERY  = CUR_ITENS.NRDELIVERY;
             
     IF V_EXISTE_EM_RETROBILL > 0 THEN
        CONTINUE;
     END IF;        

     V_TOT_ITENS := V_TOT_ITENS + 1;

     NOVO_CODIGO_NT('ITRETROBILL'||P_CDFILIAL||'|'||TO_CHAR(P_NRRETROBILL), V_SEQUENCIAL);
     V_NRITRETROBILL := TO_NUMBER(V_SEQUENCIAL);

     /*Se item cobrado como preco total*/
     IF CUR_ITENS.IDTPPRECOSP = '2' THEN
        V_QTSERVICO := CUR_ITENS.QTSERVICOPRECOTOTAL;
        V_QTSERVICOFAT := 1.0;
     ELSE /*Se item cobrado como preco unitario*/
        V_QTSERVICO := CUR_ITENS.QTSERVICOPRECOUNIT;
        V_QTSERVICOFAT := CUR_ITENS.QTSERVICOFAT;
     END IF;

     V_QTSERVICO_SBY := V_QTSERVICOFAT + CUR_ITENS.QTSERVICOSBYUT;

     /*Se voo nacional...*/                    
     IF CUR_ITENS.IDTIPMANUALSP = '1' THEN
        V_VRPRECOUNANT := ROUND(CUR_ITENS.VRPRECOREAL, 2);
        V_VRDESCONDANT := ROUND(CUR_ITENS.VRDESCONDREAL, 2);
        V_VRDESFIXOANT := ROUND(CUR_ITENS.VRDESFIXOREAL, 2);
        V_VRDESDESVANT := ROUND(CUR_ITENS.VRDESDESVREAL, 2);
     ELSE
        V_VRPRECOUNANT := ROUND(CUR_ITENS.VRPRECODOLAR, 2);
        V_VRDESCONDANT := ROUND(CUR_ITENS.VRDESCONDDOLAR, 2);                    
        V_VRDESFIXOANT := ROUND(CUR_ITENS.VRDESFIXODOLAR, 2);
        V_VRDESDESVANT := ROUND(CUR_ITENS.VRDESDESVDOLAR, 2);
     END IF;

     IF CUR_ITENS.IDDESCCOND = '1' THEN
        V_IDDESCCOND := 'S';
     ELSE
        V_IDDESCCOND := 'N';
     END IF;

     IF CUR_ITENS.IDDESCFIXO = '1' THEN
        V_IDDESCFIXO := 'S'; 
     ELSE
        V_IDDESCFIXO := 'N';
     END IF;

     IF CUR_ITENS.IDDESCDESV = '1' THEN
        V_IDDESCDESV := 'S';
     ELSE
        V_IDDESCDESV := 'N';
     END IF;

     /*Valor do item antes dos descontos*/     
     V_VALORITEMANT := ROUND((V_QTSERVICOFAT * V_VRPRECOUNANT), 2) + 
                       ROUND((CUR_ITENS.QTSERVICOSBYUT * V_VRPRECOUNANT), 2) + 
                       ROUND((CUR_ITENS.VRNUSADO), 2);
     
     /*>>> Calcula o preco total do item, considerando os descontos  <<<*/
     V_TOTALITEMANT := V_VALORITEMANT - V_VRDESDESVANT - V_VRDESFIXOANT - V_VRDESCONDANT;

     /*Se o calculo proporcional em delivery complementar estiver habilitado  */
     IF CUR_ITENS.IDDELIVCOMPL = 'S' AND CUR_ITENS.IDCALCPROPDLCOM = 'S' THEN
        SELECT SUM(NVL(S.QTATUAPRODSP, 0.0)-(NVL(S.QTSESBYPRSP, 0.0)- NVL(S.QTSESBYUTSP, 0.0))) AS QTSERVTOT_JAEMIT,
               SUM(NVL(S.QTSERFATSP, 0.0) + NVL(S.QTSESBYUTSP, 0.0)) AS QTSERVFAT_JAEMIT,
               SUM(ROUND(NVL(S.QTSERFATSP, 0.0) * NVL(S.VRPRURESESP, 0.00), 2) + 
                   ROUND(NVL(S.QTSESBYUTSP, 0.00) * NVL(S.VRPRUSBYSP, 0.00), 2)) AS VRREAL,
               SUM(ROUND(NVL(S.QTSERFATSP, 0.0) * NVL(S.VRPRUDOLSESP, 0.00), 2) + 
                   ROUND(NVL(S.QTSESBYUTSP, 0.00) * NVL(S.VRPRUSBYSP, 0.00), 2)) AS VRDOLAR
          INTO V_QTSERVTOT_JAEMIT, V_QTSERVFAT_JAEMIT, V_VRREAL_JAEMIT, V_VRDOLAR_JAEMIT 
          FROM SERVPLANOM S          
         WHERE CDFILIAL = CUR_ITENS.CDFILIAL
           AND CDCIA = CUR_ITENS.CDCIA
           AND NRVOO = CUR_ITENS.NRVOO
           AND CDTRECHO = CUR_ITENS.CDTRECHO
           AND DTHRPLAVOOPV = CUR_ITENS.DTHRPLAVOOPV
           AND NRSERVICOCIAFILI = CUR_ITENS.NRSERVICOCIAFILI
           AND CDCLASSE = CUR_ITENS.CDCLASSE
           AND CDCICLO = CUR_ITENS.CDCICLO
           AND IDSITSERSP = '2'
           AND NRDELIVERY < CUR_ITENS.NRDELIVERY;

        /*Se preco unitario*/
        IF CUR_ITENS.IDTPPRECOSP = '1' THEN
           V_QTSERV_JAEMIT := V_QTSERVFAT_JAEMIT;           
        ELSE
           V_QTSERV_JAEMIT := V_QTSERVTOT_JAEMIT;
        END IF;

        /*Se servico nacional*/
        IF CUR_ITENS.IDTIPMANUALSP = '1' THEN
           V_VALOR_JAEMIT := V_VRREAL_JAEMIT;
        ELSE
           V_VALOR_JAEMIT := V_VRDOLAR_JAEMIT;
        END IF;
     ELSE
        V_QTSERV_JAEMIT := 0.0;
        V_VALOR_JAEMIT := 0.0;
     END IF;
     V_QTSERVICOTOT := V_QTSERVICO + V_QTSERV_JAEMIT;

     /*Obtem na nova tabela de precos o preco atual para o servico*/
     V_VRPRECOUNATU := F_CALCULA_PRECOM('N', CUR_ITENS.CDFILIAL, CUR_ITENS.CDCIA, CUR_ITENS.NRSERVICOCIAFILI, CUR_ITENS.CDCLASSE, CUR_ITENS.CDCICLO, 
                                        V_QTSERVICOTOT, TRUNC(SYSDATE), CUR_ITENS.IDTIPMANUALSP);     

     /*Se o calculo proporcional em delivery complementar estiver habilitado  */
     IF CUR_ITENS.IDDELIVCOMPL = 'S' AND CUR_ITENS.IDCALCPROPDLCOM = 'S' THEN                

        IF V_QTSERVICOFAT > 0 THEN
           /*Total de servicos (inclusive os ja emitidos) vezes o preco proporcional, menos o total ja cobrado em deliveries. 
             O resultado e dividido pelo total do item a ser faturado*/
           V_VRPRECOUNPROP := ROUND(((V_QTSERVICOFAT * V_VRPRECOUNATU) - V_VALOR_JAEMIT)/V_QTSERVICOFAT, 2);
        ELSE
           V_VRPRECOUNPROP := 0;
        END IF;

        /*Se preco unitario*/
        IF CUR_ITENS.IDTPPRECOSP = '1' THEN           

           /*Valor total do stand by nao utilizado*/
           V_VRTOTSBYNUTATU := ROUND((CUR_ITENS.QTSERVICOSBYNUT * V_VRPRECOUNATU * (CUR_ITENS.VRPERSBYNUT/100)), 2);

           V_VRPRECOUNATU := V_VRPRECOUNPROP;
        ELSE
           /*Se preco total, busca qual seria o preco COM os stand-by nao utilizados*/
           V_VRPRECO_COM_SBYNUT := F_CALCULA_PRECOM('N', CUR_ITENS.CDFILIAL, CUR_ITENS.CDCIA, CUR_ITENS.NRSERVICOCIAFILI, CUR_ITENS.CDCLASSE, CUR_ITENS.CDCICLO, 
                                                    V_QTSERVICOTOT + CUR_ITENS.QTSERVICOSBYNUT, TRUNC(SYSDATE), CUR_ITENS.IDTIPMANUALSP);

           /*Valor total do stand by nao utilizado (diferenca vezes o percentual cobrado sobre nao utilizados)*/
           V_VRTOTSBYNUTATU := ROUND((V_VRPRECO_COM_SBYNUT - V_VRPRECOUNATU) * (CUR_ITENS.VRPERSBYNUT/100), 2);           
        END IF;

        V_VRPRECOUNATU := V_VRPRECOUNPROP;
     ELSE 
        /*Valor total do stand by nao utilizado*/
        V_VRTOTSBYNUTATU := ROUND((CUR_ITENS.QTSERVICOSBYNUT * V_VRPRECOUNATU * (CUR_ITENS.VRPERSBYNUT/100)), 2);
     END IF;
     
     /*Valor do item antes dos descontos*/
     V_VALORITEMATU := ROUND((V_QTSERVICOFAT * V_VRPRECOUNATU), 2) + 
                       ROUND((CUR_ITENS.QTSERVICOSBYUT * V_VRPRECOUNATU),2) + 
                       V_VRTOTSBYNUTATU;     

     /* Calcula o desconto de desvalorizacao correto */
     IF V_IDDESCDESV = 'S' THEN
        V_VRDESDESVATU := ROUND(V_VALORITEMATU * CUR_ITENS.VRPERDESCDESV/100, 2);
     ELSE        
        V_VRDESDESVATU := 0.00;
     END IF;

     /* Calcula o desconto fixo correto */
     IF V_IDDESCFIXO = 'S' THEN 
        V_VRDESFIXOATU := ROUND((V_VALORITEMATU - V_VRDESDESVATU)  * CUR_ITENS.VRPERDESCFIXO/100, 2);
     ELSE
        V_VRDESFIXOATU := 0.00;
     END IF;   
     
     /* Calcula o desconto condicional correto */
     IF V_IDDESCCOND = 'S' THEN
        V_VRDESCONDATU := ROUND((V_VALORITEMATU - V_VRDESDESVATU - V_VRDESFIXOATU)  * CUR_ITENS.VRPERDESCCOND/100, 2);
     ELSE
        V_VRDESCONDATU := 0.00;
     END IF;
     
     /*>>> Calcula o preco total do item na nova tabela, considerando os descontos  <<<*/
     V_TOTALITEMATU := V_VALORITEMATU - V_VRDESDESVATU - V_VRDESFIXOATU - V_VRDESCONDATU;

     IF (V_QTSERVICO_SBY > 0) THEN
       /*Calcula o preco (medio) anterior e atual para o servico*/
       V_VRPRECOANT := ROUND(V_TOTALITEMANT/V_QTSERVICO_SBY, 2);
       V_VRPRECOATU := ROUND(V_TOTALITEMATU/V_QTSERVICO_SBY, 2);
     ELSE
        V_VRPRECOANT := V_TOTALITEMANT;
        V_VRPRECOATU := V_TOTALITEMATU;
        V_QTSERVICO_SBY := 1;
     END IF;

     /*Calcula a diferenca entre o valor cobrado e o valor devido*/
     V_VRDIFERENCA := ROUND(V_QTSERVICO_SBY * V_VRPRECOATU, 2) - ROUND(V_QTSERVICO_SBY * V_VRPRECOANT, 2);

     INSERT INTO ITRETROBILL
     (NRRETROBILL, NRITRETROBILL, 
      CDFILIAL, CDCIA,NRDELIVERY,
      NRSERVICOCIAFILI, CDCLASSE, CDCICLO,
      QTSERVICO, QTSERVICOFAT, QTSERVICOSBYUT, QTSERVICOSBYNUT,
      VRPRECOANT, VRPRECOATU,
      VRPRECOUNANTERIOR, VRPRECOUNATUAL,
      VRTOTSBYNUTANT, VRTOTSBYNUTATU,
      VRDESCCONDANT, VRDESCCONDATU,
      VRDESCFIXOANT, VRDESCFIXOATU,
      VRDESCDESVANT, VRDESCDESVATU,
      VRTOTALANTERIOR, VRTOTALATUAL, VRDIFERENCA,
      NRORG, IDATIVO, DTINCLUSAO, DTULTATU, NRORGINCLUSAO,
      CDOPERINCLUSAO, NRORGULTATU, CDOPERULTATU)
     VALUES
     (P_NRRETROBILL, V_NRITRETROBILL,
      P_CDFILIAL, P_CDCIA, CUR_ITENS.NRDELIVERY,
      CUR_ITENS.NRSERVICOCIAFILI, CUR_ITENS.CDCLASSE, CUR_ITENS.CDCICLO,
      V_QTSERVICO_SBY, V_QTSERVICOFAT, CUR_ITENS.QTSERVICOSBYUT, CUR_ITENS.QTSERVICOSBYNUT,
      V_VRPRECOANT, V_VRPRECOATU,
      V_VRPRECOUNANT, V_VRPRECOUNATU,
      CUR_ITENS.VRNUSADO, V_VRTOTSBYNUTATU,
      V_VRDESCONDANT, V_VRDESCONDATU,
      V_VRDESFIXOANT, V_VRDESFIXOATU,
      V_VRDESDESVANT, V_VRDESDESVATU,
      V_TOTALITEMANT, V_TOTALITEMATU, V_VRDIFERENCA,
      P_NRORG, 'S', SYSDATE, SYSDATE, P_NRORG,
      P_CDOPERADOR, P_NRORG, P_CDOPERADOR);
   
   END LOOP;   
   
   /* Se nao ha servicos no periodo, exclui o retrobill incluido */
   IF V_TOT_ITENS = 0 THEN
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' ||  'APP-00021' || 'APPERRMSGEND', TRUE);
   END IF;
   
   /*Atualiza os valores de credito (vrdiferenca < 0) e retrobill(vrdiferenca > 0) na tabela RETROBILL*/
   UPDATE RETROBILL
      SET (VRRETROBILL, VRCREDITO) = (SELECT SUM(CASE WHEN VRDIFERENCA < 0 THEN 0.00 ELSE VRDIFERENCA END) AS VRRETROBILL,
                                             SUM(CASE WHEN VRDIFERENCA > 0 THEN 0.00 ELSE VRDIFERENCA END) AS VRCREDITO
                                        FROM ITRETROBILL
                                       WHERE NRRETROBILL = P_NRRETROBILL)
    WHERE CDFILIAL = P_CDFILIAL AND NRRETROBILL = P_NRRETROBILL;
   
   /* Consolida os dados que gerarao retrobill (diferenca positiva) */
   INSERT INTO ITRETROBILLCONS
   (NRRETROBILL, CDFILIAL, CDCIA, 
    NRSERVICOCIAFILI, CDCLASSE, CDCICLO,
    QTSERVICO, VRRETROBILL, VRDESCONTO, VRFINALRETROBILL,
    NRORG, IDATIVO, DTINCLUSAO, DTULTATU, NRORGINCLUSAO,
    CDOPERINCLUSAO, NRORGULTATU, CDOPERULTATU)
   SELECT NRRETROBILL, CDFILIAL, CDCIA, NRSERVICOCIAFILI, CDCLASSE, CDCICLO,
          SUM(QTSERVICO), SUM(VRDIFERENCA), 0.00, SUM(VRDIFERENCA),
          P_NRORG,  'S', SYSDATE, SYSDATE, P_NRORG,
          P_CDOPERADOR AS CDOPERINCLUSAO, P_NRORG, P_CDOPERADOR AS CDOPERULTATU
     FROM ITRETROBILL
    WHERE CDFILIAL = P_CDFILIAL AND NRRETROBILL = P_NRRETROBILL
      AND VRDIFERENCA > 0
    GROUP BY NRRETROBILL, CDFILIAL, CDCIA, NRSERVICOCIAFILI, CDCLASSE, CDCICLO;
END;
/