CREATE OR REPLACE PROCEDURE AJUSTA_PIS_COFINS_SPED(
  P_CDFILIAL      IN  VENDA.CDFILIAL%TYPE,
  P_CDCAIXA       IN  VENDA.CDCAIXA%TYPE,
  P_DTENTRVENDA   IN  VENDA.DTENTRVENDA%TYPE)
IS
  V_VRIMPPISCALC     NUMBER;
  V_VRIMPPIS         NUMBER; 
  V_VRIMPCOFINSCALC  NUMBER;
  V_VRIMPCOFINS      NUMBER; 
  V_DIFERENCA        NUMBER;
  V_NRSEQUITVEND     VARCHAR2(3);
BEGIN
  FOR C_VRPIS IN (SELECT CDFILIAL, CDCAIXA, NRSEQVENDA,
                            SUM(VRIMPPIS) AS VRIMPPIS, CDCSTPRODPC, VRPERPIS, SUM(VRBCPISCOFINS) AS VRBCPISCOFINS                            
                       FROM (SELECT P.CDARVPROD, P.NMPRODUTO,
                                    IP.CDCSTPRODPC, IP.VRPERPIS,                                   
                                    ROUND_ABNT(IP.VRBCPISCOFINS*(IP.VRPERPIS/100),2) AS VRIMPPIS, IP.VRBCPISCOFINS,                                    
                                    IP.NRSEQVENDA, VD.CDCAIXA, VD.CDFILIAL
                               FROM VENDA VD, ITEMVENDA IT, PRODUTO P,
                                    (SELECT IV.CDFILIAL, IV.CDCAIXA, IV.NRSEQVENDA, IV.NRSEQUITVEND,
                                            IV.VRPERPIS, I.CDIMPOSTONFE, IV.CDCSTPRODPC,                                      
                                            IV.VRBCPISCOFINS, IV.VRIMPPIS
                                       FROM ITVENDAIMPOS IV, IMPOSTO I
                                      WHERE IV.CDIMPOSTO   = I.CDIMPOSTO
                                        AND I.IDTPIMPOSFIS <> 'S') IP
                              WHERE VD.CDFILIAL      = P_CDFILIAL
                                AND VD.CDCAIXA       = P_CDCAIXA
                                AND VD.IDSITUVENDA   = 'O'
                                AND VD.IDTPAMBNFCE   = '1'
                                AND VD.IDSTATUSNFCE  = 'A'
                                AND VD.IDTPEMISVEND  IN ('N','S')
                                AND VD.DTENTRVENDA   = P_DTENTRVENDA
                                AND VD.CDFILIAL      = IT.CDFILIAL
                                AND VD.CDCAIXA       = IT.CDCAIXA
                                AND VD.NRSEQVENDA    = IT.NRSEQVENDA
                                AND IT.CDPRODUTO     = P.CDPRODUTO
                                AND IT.CDFILIAL      = IP.CDFILIAL
                                AND IT.CDCAIXA       = IP.CDCAIXA
                                AND IT.NRSEQVENDA    = IP.NRSEQVENDA
                                AND IT.NRSEQUITVEND  = IP.NRSEQUITVEND
                                AND VD.IDAPROVACAONFCE = 'P'
                                AND IP.CDIMPOSTONFE  IN ('00','80'))
                      WHERE VRIMPPIS > 0
                   GROUP BY CDCSTPRODPC, VRPERPIS, CDFILIAL, CDCAIXA, NRSEQVENDA) LOOP

      V_VRIMPPISCALC := ROUND_ABNT(C_VRPIS.VRBCPISCOFINS*(C_VRPIS.VRPERPIS/100),2);
      V_DIFERENCA    := (C_VRPIS.VRIMPPIS-V_VRIMPPISCALC);

      IF V_DIFERENCA <> 0 THEN           
          
          SELECT NRSEQUITVEND
            INTO V_NRSEQUITVEND
            FROM (SELECT MAX(VRBCPISCOFINS) AS VRBCPISCOFINS, NRSEQUITVEND 
                    FROM ITVENDAIMPOS
                   WHERE CDFILIAL    = C_VRPIS.CDFILIAL
                     AND CDCAIXA     = C_VRPIS.CDCAIXA
                     AND NRSEQVENDA  = C_VRPIS.NRSEQVENDA
                     AND CDCSTPRODPC = C_VRPIS.CDCSTPRODPC
                     AND VRPERPIS    = C_VRPIS.VRPERPIS
                GROUP BY NRSEQUITVEND
                ORDER BY VRBCPISCOFINS DESC, NRSEQUITVEND ASC)      
            WHERE ROWNUM = 1;
            
          SELECT ROUND_ABNT(VRBCPISCOFINS*(VRPERPIS/100),2) 
            INTO V_VRIMPPIS
            FROM ITVENDAIMPOS
           WHERE CDFILIAL     = C_VRPIS.CDFILIAL
             AND CDCAIXA      = C_VRPIS.CDCAIXA
             AND NRSEQVENDA   = C_VRPIS.NRSEQVENDA
             AND NRSEQUITVEND = V_NRSEQUITVEND;
          
          IF V_DIFERENCA > 0 THEN    
            
            IF (V_VRIMPPIS-V_DIFERENCA) >= 0 THEN
              UPDATE ITVENDAIMPOS
                 SET VRIMPPIS     = V_VRIMPPIS-V_DIFERENCA
               WHERE CDFILIAL     = C_VRPIS.CDFILIAL
                 AND CDCAIXA      = C_VRPIS.CDCAIXA
                 AND NRSEQVENDA   = C_VRPIS.NRSEQVENDA
                 AND NRSEQUITVEND = V_NRSEQUITVEND;
            END IF;
          ELSE
            UPDATE ITVENDAIMPOS
                 SET VRIMPPIS = VRIMPPIS+ABS(V_DIFERENCA)
               WHERE CDFILIAL     = C_VRPIS.CDFILIAL
                 AND CDCAIXA      = C_VRPIS.CDCAIXA
                 AND NRSEQVENDA   = C_VRPIS.NRSEQVENDA
                 AND NRSEQUITVEND = V_NRSEQUITVEND;  
          END IF;
      END IF; 
      COMMIT;
  END LOOP;
  
  FOR C_VRCOFINS IN (SELECT CDFILIAL, CDCAIXA, NRSEQVENDA,
                            SUM(VRIMPCOFINS) AS VRIMPCOFINS, CDCSTPRODPC, VRPERCOFINS, SUM(VRBCPISCOFINS) AS VRBCPISCOFINS
                       FROM (SELECT P.CDARVPROD, P.NMPRODUTO,
                                    IP.CDCSTPRODPC, IP.VRPERCOFINS,                                    
                                    ROUND_ABNT(IP.VRBCPISCOFINS*(IP.VRPERCOFINS/100),2) AS VRIMPCOFINS, IP.VRBCPISCOFINS,                                    
                                    IP.NRSEQVENDA, VD.CDCAIXA, VD.CDFILIAL
                               FROM VENDA VD, ITEMVENDA IT, PRODUTO P,
                                    (SELECT IV.CDFILIAL, IV.CDCAIXA, IV.NRSEQVENDA, IV.NRSEQUITVEND,
                                            IV.VRPERCOFINS, I.CDIMPOSTONFE, IV.CDCSTPRODPC,                                      
                                            IV.VRBCPISCOFINS, IV.VRIMPCOFINS
                                       FROM ITVENDAIMPOS IV, IMPOSTO I
                                      WHERE IV.CDIMPOSTO   = I.CDIMPOSTO
                                        AND I.IDTPIMPOSFIS <> 'S') IP
                              WHERE VD.CDFILIAL      = P_CDFILIAL
                                AND VD.CDCAIXA       = P_CDCAIXA
                                AND VD.IDSITUVENDA   = 'O'
                                AND VD.IDTPAMBNFCE   = '1'
                                AND VD.IDSTATUSNFCE  = 'A'
                                AND VD.IDTPEMISVEND  IN ('N','S')
                                AND VD.DTENTRVENDA   = P_DTENTRVENDA
                                AND VD.CDFILIAL      = IT.CDFILIAL
                                AND VD.CDCAIXA       = IT.CDCAIXA
                                AND VD.NRSEQVENDA    = IT.NRSEQVENDA
                                AND IT.CDPRODUTO     = P.CDPRODUTO
                                AND IT.CDFILIAL      = IP.CDFILIAL
                                AND IT.CDCAIXA       = IP.CDCAIXA
                                AND IT.NRSEQVENDA    = IP.NRSEQVENDA
                                AND IT.NRSEQUITVEND  = IP.NRSEQUITVEND
                                AND VD.IDAPROVACAONFCE = 'P'
                                AND IP.CDIMPOSTONFE  IN ('00','80'))
                      WHERE VRIMPCOFINS > 0
                   GROUP BY CDCSTPRODPC, VRPERCOFINS, CDFILIAL, CDCAIXA, NRSEQVENDA) LOOP

      V_VRIMPCOFINSCALC := ROUND_ABNT(C_VRCOFINS.VRBCPISCOFINS*(C_VRCOFINS.VRPERCOFINS/100),2);
      V_DIFERENCA       := (C_VRCOFINS.VRIMPCOFINS-V_VRIMPCOFINSCALC);
  
      IF V_DIFERENCA <> 0 THEN  
          
          SELECT NRSEQUITVEND
            INTO V_NRSEQUITVEND
            FROM (SELECT MAX(VRBCPISCOFINS) AS VRBCPISCOFINS, NRSEQUITVEND 
                    FROM ITVENDAIMPOS
                   WHERE CDFILIAL    = C_VRCOFINS.CDFILIAL
                     AND CDCAIXA     = C_VRCOFINS.CDCAIXA
                     AND NRSEQVENDA  = C_VRCOFINS.NRSEQVENDA
                     AND CDCSTPRODPC = C_VRCOFINS.CDCSTPRODPC
                     AND VRPERCOFINS = C_VRCOFINS.VRPERCOFINS
                GROUP BY NRSEQUITVEND
                ORDER BY VRBCPISCOFINS DESC, NRSEQUITVEND ASC)      
            WHERE ROWNUM = 1; 
            
            SELECT ROUND_ABNT(VRBCPISCOFINS*(VRPERCOFINS/100),2) 
              INTO V_VRIMPCOFINS
              FROM ITVENDAIMPOS
             WHERE CDFILIAL     = C_VRCOFINS.CDFILIAL
               AND CDCAIXA      = C_VRCOFINS.CDCAIXA
               AND NRSEQVENDA   = C_VRCOFINS.NRSEQVENDA
               AND NRSEQUITVEND = V_NRSEQUITVEND;
          
          IF V_DIFERENCA > 0 THEN 
            
            IF (V_VRIMPCOFINS-V_DIFERENCA) >= 0 THEN
              
              UPDATE ITVENDAIMPOS
                 SET VRIMPCOFINS  = V_VRIMPCOFINS-V_DIFERENCA
               WHERE CDFILIAL     = C_VRCOFINS.CDFILIAL
                 AND CDCAIXA      = C_VRCOFINS.CDCAIXA
                 AND NRSEQVENDA   = C_VRCOFINS.NRSEQVENDA
                 AND NRSEQUITVEND = V_NRSEQUITVEND;
                
            END IF;
          ELSE
            UPDATE ITVENDAIMPOS
                 SET VRIMPCOFINS  = V_VRIMPCOFINS+ABS(V_DIFERENCA)
               WHERE CDFILIAL     = C_VRCOFINS.CDFILIAL
                 AND CDCAIXA      = C_VRCOFINS.CDCAIXA
                 AND NRSEQVENDA   = C_VRCOFINS.NRSEQVENDA
                 AND NRSEQUITVEND = V_NRSEQUITVEND;        
          END IF;
      END IF; 
      COMMIT;
  END LOOP;    
END;

/

