CREATE OR REPLACE FUNCTION DES_ISS_SITU_ESP(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCMUNI  IN INSCRMUNICIP.CDINSCMUNI%TYPE,
   P_CDARVPROD   IN PRODUTO.CDARVPROD%TYPE,
   P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
   P_NRLANCTONF  IN NOTAFISCAL.NRLANCTONF%TYPE) RETURN VARCHAR2
AS 
   V_SITU_ESP     VARCHAR2(50);
   V_VRNOTAFISC   FLOAT;
   V_VRBASEISS    FLOAT;
   V_CDSUBITEMISS VARCHAR2(5);
BEGIN
   BEGIN
     SELECT SUM(ROUND(DECODE(II.CDFISCALIT,'1',II.VRBASECALCIT,0),2)),
            SUM(ROUND(VRNOTAFISC,2)),
            NVL(CLASSE_CODTRIBPROD(P_CDARVPROD, P_CDEMPRESA, P_CDINSCMUNI),'')
       INTO V_VRBASEISS,
            V_VRNOTAFISC,
            V_CDSUBITEMISS
       FROM ITEMPROIMPOS II, ITEMPRODUTO IP, PRODUTO PO, NOTAFISCAL NF
      WHERE II.CDFILIAL    = NF.CDFILIAL
        AND II.NRLANCTONF  = NF.NRLANCTONF
        AND NF.CDFILIAL    = P_CDFILIAL
        AND NF.NRLANCTONF  = P_NRLANCTONF
        AND II.CDFILIAL    = IP.CDFILIAL
        AND II.NRLANCTONF  = IP.NRLANCTONF
        AND II.NRSEQITPROD = IP.NRSEQITPROD
        AND PO.CDPRODUTO   = IP.CDPRODUTO
        AND PO.CDARVPROD   = P_CDARVPROD
      GROUP BY NVL(CLASSE_CODTRIBPROD(P_CDARVPROD, P_CDEMPRESA, P_CDINSCMUNI),'');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           V_VRBASEISS    := 0;
           V_VRNOTAFISC   := 0;
           V_CDSUBITEMISS := NULL;
   END;
   IF V_CDSUBITEMISS = '702' THEN
      V_SITU_ESP := '3';
   ELSIF V_CDSUBITEMISS IN ('902','903','1501') THEN
      V_SITU_ESP := '4';
   ELSIF (V_CDSUBITEMISS IN ('1706','1008','1001','1002','1003','1004','1005') AND NVL(V_VRBASEISS,0) = NVL(V_VRNOTAFISC,0)) THEN
      V_SITU_ESP := '5';
   ELSIF (NVL(V_VRBASEISS,0) = 0 AND V_CDSUBITEMISS IN ('1706','1008','1001','1002','1003','1004','1005')) THEN
      V_SITU_ESP := '6';
   ELSE
      V_SITU_ESP := '1';
   END IF;
   RETURN V_SITU_ESP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('DES_ISS_SITU_ESP','5.30.000'); END;
/
