create or replace FUNCTION BUSCA_CUSTOTOTAL(P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                            P_NRORDPROD    IN PRODACABADO.NRORDPROD%TYPE,
                                            P_NRSUBORDPROD IN PRODACABADO.NRSUBORDPROD%TYPE) RETURN FLOAT


AS

V_CUSTOTOTAL FLOAT := 0;

BEGIN
   BEGIN 
      SELECT SUM(DECODE(APRCUSTOP.CDFUNCUSTO,'002', APRCUSTOP.VRAPRCUSTOP * APRCUSTOP.QTHRAPRCUST * QTAPRCUSTOP,'003', APRCUSTOP.VRAPRCUSTOP * APRCUSTOP.QTHRAPRCUST * QTAPRCUSTOP, APRCUSTOP.QTAPRCUSTOP * APRCUSTOP.VRAPRCUSTOP)) +
                    NVL(SUM(DECODE(APRCUSTOP.CDFUNCUSTO,'099',(
                    SELECT NVL(SUM(VRCUSTERCOP),0) TOTALVRCUSTERCOP
                              FROM CUSTERCOP
                             WHERE CDFILIAL      = P_CDFILIAL
                               AND NRORDPROD     = P_NRORDPROD 
                               AND NRSUBORDPROD  = P_NRSUBORDPROD) )),0)
                       INTO V_CUSTOTOTAL
                       FROM CUSTOINDI, APRCUSTOP
                      WHERE APRCUSTOP.CDFILIAL     = P_CDFILIAL
                        AND APRCUSTOP.NRORDPROD    = P_NRORDPROD
                        AND APRCUSTOP.NRSUBORDPROD = P_NRSUBORDPROD
                        AND APRCUSTOP.CDFUNCUSTO   = CUSTOINDI.CDFUNCUSTO
                        AND APRCUSTOP.CDCUSTOINDI  = CUSTOINDI.CDCUSTOINDI
                        AND CUSTOINDI.IDCOMPCUSTPR = 'S';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CUSTOTOTAL := 0;
   END;

 RETURN(V_CUSTOTOTAL);
END;
/
