CREATE OR REPLACE PROCEDURE CALC_DESC_PROP_DELIVERYM(
  P_CDFILIAL     IN VARCHAR2, 
  P_CDCIA        IN VARCHAR2,
  P_NRVOO        IN VARCHAR2,
  P_CDTRECHO     IN VARCHAR2,
  P_DTHRPLAVOOPV IN DATE,
  P_CDOPERADOR IN VARCHAR2,  
  P_NRORG IN NUMBER DEFAULT 1)
AS
   V_EXISTE_DELIVERY VARCHAR2(4);
   V_EXISTE_SERV_A_EMITIR VARCHAR2(4);
   V_VRTOTPAX_SBY FLOAT;
   V_IDCALCPROPDLCOM VARCHAR2(1);
   V_VRCORRENTE FLOAT;
   V_VRDOLAR FLOAT;
   V_VRDESCONTO FLOAT;
   V_CDAGRODECO VARCHAR2(4);
   V_TOTAL_RATEADO FLOAT;
   V_MAIOR_QUANTIDADE FLOAT;
   V_NRITEMPLANSP NUMBER;
   V_VRPRECO FLOAT;
   V_VRTOTPRECO_ANTERIOR FLOAT;
BEGIN
   /* ************************************************************************/
   /* VERIFICA SE HA DELIVERY COMPLEMENTAR E SE UTILIZA DESCONTO PROPORCIONAL*/      
   /* ************************************************************************/
   SELECT MAX(SPD.CDFILIAL), MAX(SP.CDFILIAL), MAX(PC.IDCALCPROPDLCOM)
     INTO V_EXISTE_DELIVERY, V_EXISTE_SERV_A_EMITIR, V_IDCALCPROPDLCOM
     FROM PLANOVOOM P
          LEFT JOIN PARAMCOMIS PC ON PC.CDFILIAL = P.CDFILIAL
          LEFT JOIN SERVPLANOM SPD ON SPD.CDFILIAL = P.CDFILIAL AND
                                      SPD.CDCIA = P.CDCIA AND
                                      SPD.NRVOO = P.NRVOO AND
                                      SPD.CDTRECHO = P.CDTRECHO AND
                                      SPD.DTHRPLAVOOPV = P.DTHRPLAVOOPV AND
                                      SPD.NRDELIVERY IS NULL AND
                                      SPD.IDSITSERSP = '1'
          LEFT JOIN SERVPLANOM SP ON SP.CDFILIAL = P.CDFILIAL AND
                                     SP.CDCIA = P.CDCIA AND
                                     SP.NRVOO = P.NRVOO AND
                                     SP.CDTRECHO = P.CDTRECHO AND
                                     SP.DTHRPLAVOOPV = P.DTHRPLAVOOPV AND
                                     SP.NRDELIVERY IS NOT NULL AND
                                     SP.IDSITSERSP <> '3'
    WHERE SP.CDFILIAL = P_CDFILIAL
      AND SP.CDCIA = P_CDCIA 
      AND SP.NRVOO = P_NRVOO
      AND SP.CDTRECHO = P_CDTRECHO
      AND SP.DTHRPLAVOOPV = P_DTHRPLAVOOPV;
    /* ************************************************************************/
    /* VERIFICA SE HA DELIVERY COMPLEMENTAR E SE UTILIZA DESCONTO PROPORCIONAL*/      
    /* ************************************************************************/
    IF V_EXISTE_DELIVERY IS NOT NULL AND
       V_EXISTE_SERV_A_EMITIR IS NOT NULL AND
       V_IDCALCPROPDLCOM = 'S' THEN
       
       /* ************************************************************************/
       /* PERCORRE OS SERVICOS DA DELIVERY COMPLEMENTAR (E SEUS CORRESPONDENTES EM DELIVERIES EMITIDAS)*/      
       /* ************************************************************************/
       FOR C_SERV IN 
       (SELECT SP.CDFILIAL,SP.CDCIA,SP.NRVOO,SP.CDTRECHO, SP.DTHRPLAVOOPV, SP.CDAGRDECO,
               SP.NRSERVICOCIAFILI, SP.CDCLASSE, SP.CDCICLO, SP.IDTIPMANUALSP, SP.IDTPPRECOSP,
               MAX(NVL(CF.VRPERSBYNUT, 0)) AS VRPERSBYNUT,
               MAX(NVL(P.QTTOTPAXAT, 0) + NVL(P.QTPAXSBY, 0)) AS QTPAX,
               SUM(QTSERFATSP)+SUM(QTSESBYUTSP) AS QTREALPROD,
               SUM(QTSERFATSP)QTSERFATSP,
               SUM(QTATUAPRODSP-(SP.QTSESBYPRSP-SP.QTSESBYUTSP)) QTREALTOT,               
               /* VALOR TOTAL DE SERVICOS EM DELIVERIES (REAL) */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN 
                          NVL(DECODE (SP.QTSERFATSP, 0, 1, SP.QTSERFATSP) * SP.VRPRURESESP, 0.00)
                       ELSE 0.00
                    END
                  ) AS VRSERVDELREAL,
               /* VALOR TOTAL DE STAND BY UTILIZADOS EM DELIVERIES (REAL) */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN 
                          NVL(SP.VRPRUSBYSP * SP.QTSESBYUTSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS VRSBYDELREAL,
               
               /* VALOR TOTAL DE STAND BY UTILIZADOS EM DELIVERIES (DOLAR) */
               SUM( 
                    CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN
                          NVL(DECODE (SP.QTSERFATSP, 0, 1, SP.QTSERFATSP) * SP.VRPRUDOLSESP, 0.00)
                       ELSE 0.00
                    END
                       
                  ) AS VRSERVDELDOLAR,
               
               /* VALOR TOTAL DE STAND BY UTILIZADOS EM DELIVERIES (DOLAR) */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN
                          NVL(DECODE(SP.VRPRUDOLSESP, 0,0, VRPRUSBYSP) * SP.QTSESBYUTSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS VRSBYDELDOLAR,
                  
               /* VALOR TOTAL DE STAND BY NAO UTILIZADOS COBRADO EM DELIVERIES */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN
                          NVL(VRNUSADO, 0.00)
                       ELSE 0.00
                    END
                  ) AS VRNUSADO,
                  
               /* VALOR TOTAL DE STAND BY NAO UTILIZADOS COBRADO EM DELIVERIES */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN
                          NVL(SP.QTSESBYPRSP - SP.QTSESBYUTSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS QTNAOUSADOS,
                  
              /* VALOR TOTAL DE SERVICOS DA DELIVERY COMPLEMENTAR */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NULL THEN
                          NVL(SP.QTSERFATSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS QTSERFATSP_COMPLEMENTAR,
                  
               /* VALOR TOTAL DE SERVICOS DA DELIVERY COMPLEMENTAR */
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NULL THEN
                          NVL(SP.QTSESBYUTSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS QTSESBYUTSP_COMPLEMENTAR,               
               SUM( CASE 
                       WHEN SP.NRDELIVERY IS NOT NULL THEN
                          NVL(SP.QTSERFATSP, 0.00)
                       ELSE 0.00
                    END
                  ) AS QTSERFATSP_EMITIDA
          FROM SERVPLANOM SP
               LEFT JOIN CIAFILI CF ON CF.CDFILIAL = SP.CDFILIAL AND
                                       CF.CDCIA = SP.CDCIA
               LEFT JOIN PREVISAOPAXM P ON P.CDFILIAL = SP.CDFILIAL AND
                                           P.CDCIA = SP.CDCIA AND
                                           P.NRVOO = SP.NRVOO AND
                                           P.CDTRECHO = SP.CDTRECHO AND
                                           P.DTHRPLAVOOPV = SP.DTHRPLAVOOPV AND
                                           P.CDCLASSE = SP.CDCLASSE
         WHERE SP.CDFILIAL = P_CDFILIAL
           AND SP.CDCIA = P_CDCIA 
           AND SP.NRVOO = P_NRVOO
           AND SP.CDTRECHO = P_CDTRECHO
           AND SP.DTHRPLAVOOPV = P_DTHRPLAVOOPV           
           AND SP.IDSITSERSP  <> 3
           AND EXISTS (SELECT 1
                         FROM SERVPLANOM S
                        WHERE S.CDFILIAL = SP.CDFILIAL 
                          AND S.CDCIA = SP.CDCIA 
                          AND S.NRVOO = SP.NRVOO
                          AND S.CDTRECHO = SP.CDTRECHO
                          AND S.DTHRPLAVOOPV = SP.DTHRPLAVOOPV
                          AND S.NRSERVICOCIAFILI = SP.NRSERVICOCIAFILI
                          AND S.CDCLASSE = SP.CDCLASSE
                          AND S.CDCICLO = SP.CDCICLO
                          AND S.IDSITSERSP = '1'
                          AND S.NRDELIVERY IS NULL)
         GROUP BY SP.CDFILIAL,SP.CDCIA,SP.NRVOO,SP.CDTRECHO, SP.DTHRPLAVOOPV, SP.CDAGRDECO,
                  SP.NRSERVICOCIAFILI, SP.CDCLASSE, SP.CDCICLO, SP.IDTIPMANUALSP, SP.IDTPPRECOSP
       ) LOOP
         /* ************************************************************************/
         /* DETERMINA QUANTIDADE UTILIZADA NA TABELA DE PROPORCAO                  */      
         /* ************************************************************************/
         V_VRTOTPAX_SBY := CASE WHEN C_SERV.IDTPPRECOSP = '2' THEN C_SERV.QTREALTOT ELSE C_SERV.QTREALPROD END;
         
         /* ************************************************************************/
         /* DETERMINA PRECO DO SERVICO A PARTIR DA QUANTIDADE TOTAL (EM DELIVERIES E DELIVERIES COMPLEMENTARES) */      
         /* ************************************************************************/
         CALCULA_PRECOM('N',
                        C_SERV.CDFILIAL,
                        C_SERV.CDCIA,
                        C_SERV.NRVOO,
                        C_SERV.CDTRECHO,
                        C_SERV.NRSERVICOCIAFILI,
                        C_SERV.CDCLASSE,
                        C_SERV.CDCICLO,
                        C_SERV.DTHRPLAVOOPV,
                        C_SERV.DTHRPLAVOOPV,
                        CASE WHEN C_SERV.IDTPPRECOSP = '2' THEN C_SERV.QTPAX ELSE V_VRTOTPAX_SBY END,
                        V_VRTOTPAX_SBY,
                        C_SERV.IDTIPMANUALSP,
                        V_VRCORRENTE,
                        V_VRDOLAR);

         /*IF C_SERV.IDTIPMANUALSP = '2' THEN         
            V_VRDESCONTO := (C_SERV.VRSERVDELDOLAR + C_SERV.VRSBYDELDOLAR) - (C_SERV.QTSERFATSP_EMITIDA * V_VRDOLAR);
         ELSE*/
            /* PRECO UNITARIO * QUANTIDADE - VALOR COBRADO EM DELIVERIES ANTERIORES, SOMADO A  
            V_VRDESCONTO := (C_SERV.VRSERVDELREAL + C_SERV.VRSBYDELREAL) -  (C_SERV.QTSERFATSP_EMITIDA * V_VRCORRENTE);            
         END IF;*/

         V_VRPRECO := CASE WHEN C_SERV.IDTIPMANUALSP = '2' THEN V_VRDOLAR ELSE V_VRCORRENTE END;
         V_VRTOTPRECO_ANTERIOR := CASE WHEN C_SERV.IDTIPMANUALSP = '2' THEN C_SERV.VRSERVDELDOLAR ELSE C_SERV.VRSERVDELREAL END;

         IF C_SERV.IDTPPRECOSP = '2' THEN
            V_VRDESCONTO := V_VRPRECO - V_VRTOTPRECO_ANTERIOR;
         ELSE
            /* PRECO UNITARIO * QUANTIDADE - VALOR COBRADO EM DELIVERIES ANTERIORES*/
            V_VRDESCONTO := (C_SERV.QTREALPROD * V_VRPRECO) - V_VRTOTPRECO_ANTERIOR;
         END IF;
         
         V_VRDESCONTO := ROUND(V_VRDESCONTO, 2);
         
         /* ************************************************************************/
         /* ATUALIZA SERVICOS DE DELIVERIES COMPLEMENTARES, JOGANDO O DESCONTO RATEADO DE ACORDO COM A PARTICIPACAO NO TOTAL*/      
         /* ************************************************************************/
         UPDATE SERVPLANOM
            /* JOGA VALOR PROPORCIONAL DO DESCONTO  EM CADA ITEM COMPLEMENTAR, RATEIO*/
            SET VRDESCPROP  = ROUND(V_VRDESCONTO * 
                              CASE WHEN (C_SERV.QTSERFATSP_COMPLEMENTAR + C_SERV.QTSESBYUTSP_COMPLEMENTAR) = 0.00 THEN 0.00
                                   ELSE (NVL(QTSERFATSP,0) + NVL(QTSESBYUTSP, 0))/(C_SERV.QTSERFATSP_COMPLEMENTAR + C_SERV.QTSESBYUTSP_COMPLEMENTAR)
                              END, 2),
                /* AUDITORIA */
                NRORGULTATU = P_NRORG,
                CDOPERULTATU = P_CDOPERADOR,
                DTULTATU = SYSDATE
          WHERE CDFILIAL = C_SERV.CDFILIAL AND
                CDCIA = C_SERV.CDCIA AND
                NRVOO = C_SERV.NRVOO AND
                CDTRECHO = C_SERV.CDTRECHO AND
                DTHRPLAVOOPV = C_SERV.DTHRPLAVOOPV AND
                NRSERVICOCIAFILI = C_SERV.NRSERVICOCIAFILI AND              
                CDCLASSE = C_SERV.CDCLASSE AND
                CDCICLO = C_SERV.CDCICLO AND
                NRDELIVERY IS NULL;
         
         /* VERIFICA O TOTAL DE DESCONTO RATEADO */
         SELECT SUM(VRDESCPROP), MAX(NVL(QTSERFATSP,0) + NVL(QTSESBYUTSP, 0))
           INTO V_TOTAL_RATEADO, V_MAIOR_QUANTIDADE
           FROM SERVPLANOM
          WHERE CDFILIAL = C_SERV.CDFILIAL AND
                CDCIA = C_SERV.CDCIA AND
                NRVOO = C_SERV.NRVOO AND
                CDTRECHO = C_SERV.CDTRECHO AND
                DTHRPLAVOOPV = C_SERV.DTHRPLAVOOPV AND
                NRSERVICOCIAFILI = C_SERV.NRSERVICOCIAFILI AND              
                CDCLASSE = C_SERV.CDCLASSE AND
                CDCICLO = C_SERV.CDCICLO AND
                NRDELIVERY IS NULL;
                
         /* PEGA O NUMERO DE ITEM DAQUELE QUE RECEBEU A MAIOR PARTE DO DESCONTO */
         SELECT MAX(NRITEMPLANSP)
           INTO V_NRITEMPLANSP
           FROM SERVPLANOM
          WHERE CDFILIAL = C_SERV.CDFILIAL AND
                CDCIA = C_SERV.CDCIA AND
                NRVOO = C_SERV.NRVOO AND
                CDTRECHO = C_SERV.CDTRECHO AND
                DTHRPLAVOOPV = C_SERV.DTHRPLAVOOPV AND
                NRSERVICOCIAFILI = C_SERV.NRSERVICOCIAFILI AND              
                CDCLASSE = C_SERV.CDCLASSE AND
                CDCICLO = C_SERV.CDCICLO AND
                NRDELIVERY IS NULL AND
                NVL(QTSERFATSP,0) + NVL(QTSESBYUTSP, 0) = V_MAIOR_QUANTIDADE;
         
         /* ************************************************************************/
         /* CORRIGE DIFERENCA DO RATEIO*/      
         /* ************************************************************************/
         UPDATE SERVPLANOM
            /* JOGA, SE EXISTIR, A DIFERENCA DO VALOR RATEADO*/
            SET VRDESCPROP  = VRDESCPROP + (V_VRDESCONTO - V_TOTAL_RATEADO),
                /* AUDITORIA */
                NRORGULTATU = P_NRORG,
                CDOPERULTATU = P_CDOPERADOR,
                DTULTATU = SYSDATE            
          WHERE CDFILIAL = C_SERV.CDFILIAL AND
                CDCIA = C_SERV.CDCIA AND
                NRVOO = C_SERV.NRVOO AND
                CDTRECHO = C_SERV.CDTRECHO AND
                DTHRPLAVOOPV = C_SERV.DTHRPLAVOOPV AND
                NRSERVICOCIAFILI = C_SERV.NRSERVICOCIAFILI AND              
                CDCLASSE = C_SERV.CDCLASSE AND
                CDCICLO = C_SERV.CDCICLO AND
                NRITEMPLANSP = V_NRITEMPLANSP;
       END LOOP;
       
       /* ATUALIZA O CAMPO DE DESCONTO DAS DELIVERIES COMPLEMENTARES */
       UPDATE OBSDELIVTMPM
          SET VRDESCONTOPROP = (SELECT SUM(VRDESCPROP)
                                FROM SERVPLANOM
                               WHERE CDFILIAL = OBSDELIVTMPM.CDFILIAL
                                 AND CDCIA = OBSDELIVTMPM.CDCIATMP
                                 AND NRVOO = OBSDELIVTMPM.NRVOOTMP
                                 AND CDTRECHO = OBSDELIVTMPM.CDTRECHOTMP
                                 AND DTHRPLAVOOPV = OBSDELIVTMPM.DTHRPLAVOTMP
                                 AND CDAGRDECO = OBSDELIVTMPM.CDAGRDECOTMP),
              /* AUDITORIA */
              NRORGULTATU = P_NRORG,
              CDOPERULTATU = P_CDOPERADOR,
              DTULTATU = SYSDATE
        WHERE CDFILIAL = P_CDFILIAL AND
              CDCIATMP = P_CDCIA AND
              NRVOOTMP = P_NRVOO AND
              CDTRECHOTMP = P_CDTRECHO AND
              DTHRPLAVOTMP = P_DTHRPLAVOOPV;
        
        /* SE AINDA NAO EXISTE, INCLUI UM NOVO REGISTRO COM O DESCONTO */
       INSERT INTO OBSDELIVTMPM          
          (CDFILIAL, CDCIATMP, NRVOOTMP, 
           CDTRECHOTMP, DTHRPLAVOTMP, CDAGRDECOTMP,
           VRDESCONTOPROP,
           NRORG, DTINCLUSAO, CDOPERINCLUSAO, DTULTATU, 
           NRORGINCLUSAO, NRORGULTATU, CDOPERULTATU)
          SELECT CDFILIAL, CDCIA, NRVOO, 
                 CDTRECHO, DTHRPLAVOOPV, CDAGRDECO, 
                 SUM(VRDESCPROP),
                 P_NRORG, SYSDATE, P_CDOPERADOR, SYSDATE,
                 P_NRORG, P_NRORG, P_CDOPERADOR                 
            FROM SERVPLANOM
           WHERE CDFILIAL = P_CDFILIAL           
             AND CDCIA = P_CDCIA
             AND NRVOO = P_NRVOO
             AND CDTRECHO = P_CDTRECHO
             AND DTHRPLAVOOPV = P_DTHRPLAVOOPV
             AND NOT EXISTS (SELECT 1
                               FROM OBSDELIVTMPM
                              WHERE CDFILIAL = SERVPLANOM.CDFILIAL
                                AND CDCIATMP = SERVPLANOM.CDCIA
                                AND NRVOOTMP = SERVPLANOM.NRVOO
                                AND CDTRECHOTMP = SERVPLANOM.CDTRECHO
                                AND DTHRPLAVOTMP = SERVPLANOM.DTHRPLAVOOPV
                                AND CDAGRDECOTMP = SERVPLANOM.CDAGRDECO)
           GROUP BY CDFILIAL, CDCIA, NRVOO, 
                    CDTRECHO, DTHRPLAVOOPV, CDAGRDECO;
        
    END IF;
END;
/