create or replace FUNCTION PRODUTO_ACABADO(
  P_CDFILAUXPRAT VARCHAR2,
  P_CDPRATO VARCHAR2,
  P_CDFILIAL VARCHAR2 DEFAULT NULL,
  P_CDSERVFILI VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
AS
  V_CDPRODACABADO VARCHAR2(10);
BEGIN
V_CDPRODACABADO := NULL;
  BEGIN
  SELECT DISTINCT CDPRODPRODUCAO INTO V_CDPRODACABADO
    FROM
          (SELECT PS.CDFILAUXPRAT, PS.CDPRATO, PS.CDPRODPRODUCAO, PS.CDFILIAL, PS.CDSERVFILI, 'S' AS IDTIPO
              FROM RECESERV RS, PRATSERV PS
              WHERE RS.IDTPREQPRODSERV <> 'E'
               AND RS.CDPRATO = PS.CDPRATO
               AND RS.CDFILAUXPRAT = PS.CDFILAUXPRAT
               AND RS.CDFILIAL = PS.CDFILIAL
               AND RS.CDSERVFILI = PS.CDSERVFILI
           UNION ALL
           SELECT PF.CDFILAUXPRAT, PF.CDPRATO, PF.CDPRODPRODUCAO, PF.CDFILIAL, NULL AS CDSERVFILI, 'F' AS IDTIPO
             FROM RECEFILI RF, PRATFILI PF
             WHERE RF.IDTPREQPRODFILI <> 'E'
               AND RF.CDPRATO = PF.CDPRATO
               AND RF.CDFILAUXPRAT = PF.CDFILAUXPRAT
               AND RF.CDFILIAL = PF.CDFILIAL
           UNION ALL
           SELECT PP.CDFILAUXPRAT, PP.CDPRATO, PP.CDPRODPRODUCAO, NULL AS CDFILIAL, NULL AS CDSERVFILI, 'P' AS IDTIPO
             FROM RECEPADR RP, PRATPADR PP
             WHERE RP.IDTPREQPRODPADR <> 'E'
               AND RP.CDPRATO = PP.CDPRATO
               AND RP.CDFILAUXPRAT = PP.CDFILAUXPRAT) PR
   WHERE    PR.CDPRATO = P_CDPRATO AND
              PR.CDFILAUXPRAT = P_CDFILAUXPRAT AND
              PR.IDTIPO = RECEITA_UTILIZADA(P_CDPRATO, P_CDFILAUXPRAT, P_CDFILIAL, P_CDSERVFILI) AND
              (NVL(PR.CDFILIAL, P_CDFILIAL) = P_CDFILIAL OR P_CDFILIAL IS NULL) AND
              (NVL(PR.CDSERVFILI, P_CDSERVFILI) = P_CDSERVFILI OR P_CDSERVFILI IS NULL);

  RETURN V_CDPRODACABADO;
END;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('PRODUTO_ACABADO','6.05.077'); END;
/
