CREATE OR REPLACE FUNCTION CONCLUSAO_CRONO_INFO
   (P_CDFILIAL     IN PROJETO.CDFILIAL%TYPE,
    P_CDPROJETO    IN PROJETO.CDPROJETO%TYPE,
    P_CDVERSAO     IN CRONOPROJETO.CDVERCRONPRJ%TYPE,
    P_SOLICITACAO  IN SOLISERV.NRSOLISERV%TYPE,
    P_TAREFA       IN PLANATIVTARE.NRTAREFA%TYPE,
    P_ATIVIDADE    IN PLANATIVTARE.NRPLATIVTARE%TYPE) RETURN NUMBER
AS
  V_CONCLATIV     FLOAT;
  V_CONCLSOLI     FLOAT;
BEGIN

   SELECT
      ROUND(
            SUM(
                DECODE(A.IDSITATIVTAR,'C',A.QTTEMPLANEJA,
                       DECODE(CR.VRPERCONCPRJ,NULL,0,(CR.VRPERCONCPRJ*A.QTTEMPLANEJA)/100)
                       )
                )/SUM(A.QTTEMPLANEJA),2)*100,
      NVL(SUM(CR.VRPERCONCPRJ),ROUND(SUM(DECODE(A.IDSITATIVTAR,'C',A.QTTEMPLANEJA,0))/SUM(A.QTTEMPLANEJA),2)*100)
      INTO V_CONCLSOLI, V_CONCLATIV

   FROM CRONOPROJETO CR,
     (SELECT DECODE(SUM(NVL(QTTEMPLAITAR,0)),0,100,SUM(NVL(QTTEMPLAITAR,0))) AS QTTEMPLANEJA, PL.CDFILIAL, PL.NRTAREFA, PL.NRPLATIVTARE, PL.IDSITATIVTAR
      FROM BASELINEPROJ BS, SOLIBASELINE SB, SOLISERV SS, TAREFA TF, PLANATIVTARE PL
      WHERE BS.CDFILIAL     = P_CDFILIAL                        AND
            BS.CDPROJETO    = P_CDPROJETO                       AND
            SB.CDFILIAL     = BS.CDFILIAL                       AND
            SB.CDPROJETO    = BS.CDPROJETO                      AND
            SB.NRBASELINEPR = BS.NRBASELINEPR                    AND
            SB.NRSOLISERV   = NVL(P_SOLICITACAO, SB.NRSOLISERV) AND
            SB.CDFILIAL     = SS.CDFILIAL                       AND
            SB.NRSOLISERV   = SS.NRSOLISERV                     AND
            SB.CDFILIAL     = TF.CDFILIAL                       AND
            SB.NRSOLISERV   = TF.NRSOLISERV                     AND
            TF.CDFILIAL     = PL.CDFILIAL                       AND
            TF.NRTAREFA     = PL.NRTAREFA                       AND
            PL.NRTAREFA     = NVL(P_TAREFA,PL.NRTAREFA)         AND
            PL.NRPLATIVTARE = NVL(P_ATIVIDADE, PL.NRPLATIVTARE)
        GROUP BY PL.CDFILIAL, PL.NRTAREFA, PL.NRPLATIVTARE, PL.IDSITATIVTAR) A,

     (SELECT ROUND(SUM(DTHFAPATIVTA - DTHIAPATIVTA)*24,2) AS QTTEMPREAL, AP.CDFILIAL, AP.NRTAREFA, AP.NRPLATIVTARE
      FROM BASELINEPROJ BS, SOLIBASELINE SB, SOLISERV SS, TAREFA TF, APROATIVTARE AP
      WHERE BS.CDFILIAL      = P_CDFILIAL                       AND
            BS.CDPROJETO     = P_CDPROJETO                      AND
            SB.CDFILIAL     = BS.CDFILIAL                       AND
            SB.CDPROJETO    = BS.CDPROJETO                      AND
            SB.NRBASELINEPR = BS.NRBASELINEPR                   AND
            SB.NRSOLISERV   = NVL(P_SOLICITACAO, SB.NRSOLISERV) AND
            SB.CDFILIAL     = SS.CDFILIAL                       AND
            SB.NRSOLISERV   = SS.NRSOLISERV                     AND
            SB.CDFILIAL     = TF.CDFILIAL                       AND
            SB.NRSOLISERV   = TF.NRSOLISERV                     AND
            TF.CDFILIAL     = AP.CDFILIAL                       AND
            TF.NRTAREFA     = AP.NRTAREFA                       AND
            AP.NRTAREFA     = NVL(P_TAREFA,AP.NRTAREFA)         AND
            AP.NRPLATIVTARE = NVL(P_ATIVIDADE, AP.NRPLATIVTARE)
       GROUP BY AP.CDFILIAL, AP.NRTAREFA, AP.NRPLATIVTARE)B
   WHERE
       CR.CDFILIAL     = A.CDFILIAL
   AND CR.NRTAREFA     = A.NRTAREFA
   AND CR.NRPLATIVTARE = A.NRPLATIVTARE
   AND CR.CDFILIAL     = B.CDFILIAL    (+)
   AND CR.NRTAREFA     = B.NRTAREFA    (+)
   AND CR.NRPLATIVTARE = B.NRPLATIVTARE(+)
   AND CR.CDFILIAL     = P_CDFILIAL
   AND CR.CDPROJETO    = P_CDPROJETO
   AND CR.CDVERCRONPRJ = (SELECT MAX(C.CDVERCRONPRJ)
                          FROM CRONOPROJETO C
                           WHERE C.CDFILIAL     = CR.CDFILIAL   AND
                                 C.CDPROJETO    = CR.CDPROJETO  AND
                                 C.CDVERCRONPRJ = NVL(P_CDVERSAO, C.CDVERCRONPRJ));

   IF P_ATIVIDADE IS NULL THEN
      RETURN(V_CONCLSOLI);
   ELSE
      RETURN(V_CONCLATIV);
   END IF;
END;

/

-- VERSAO --
BEGIN ATUVEROBJ('CONCLUSAO_CRONO_INFO','5.01.033'); END;
/

