CREATE OR REPLACE FUNCTION CUSTO_PERC_COL_CARDTRAB
(
   P_NRCARDAPIOTRAB IN NUMBER,
   P_NRCONFIGCARDTRAB IN NUMBER
) RETURN FLOAT
AS
   V_VRCUSTO FLOAT := 0;
   V_QTTOTCOMENSAIS FLOAT := 0;
BEGIN
   FOR C_PRATOS IN
   (
      SELECT S.QTCOMENSAIS, S.VRPECONSCARDTRAB/100 * S.QTCOMENSAIS AS QTCOMENSAIS_ESTIMADOS,
             CUSTO_PRAT_CARDTRAB(S.NRCARDAPIOTRAB, S.CDFILAUXPRAT, S.CDPRATO) AS VRCUSTO        
        FROM
        (SELECT D.QTCOMENSAIS, C.VRPECONSCARDTRAB, C.NRCARDAPIOTRAB, C.CDFILAUXPRAT, C.CDPRATO
        FROM DIACARDTRAB D        
             LEFT JOIN COMPCARDTRAB C ON C.NRCARDAPIOTRAB = D.NRCARDAPIOTRAB AND
                                         C.NRDIACARDTRAB = D.NRDIACARDTRAB AND
                                         C.NRCONFIGCARDTRAB = P_NRCONFIGCARDTRAB
       WHERE D.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
         AND D.QTCOMENSAIS > 0
        ) S
   ) LOOP
      V_VRCUSTO := V_VRCUSTO + NVL(C_PRATOS.QTCOMENSAIS_ESTIMADOS, 0) * NVL(C_PRATOS.VRCUSTO, 0);
      V_QTTOTCOMENSAIS := V_QTTOTCOMENSAIS + C_PRATOS.QTCOMENSAIS;
   END LOOP;
   IF V_QTTOTCOMENSAIS > 0 THEN
      RETURN ROUND(V_VRCUSTO/V_QTTOTCOMENSAIS, 5);
   END IF;

   RETURN 0;
END;
/