CREATE OR REPLACE FUNCTION COMPOSICAO_EM_PRODUCAO(
  P_CDFILIAL          IN UTILRECFLEXOPCOES.CDFILIAL%TYPE,
  P_CDRECEITA         IN UTILRECFLEXOPCOES.CDRECEITA%TYPE,
  P_NRORG             IN UTILRECFLEXOPCOES.NRORG%TYPE,
  P_NRSEQUTILRECFLEX  IN UTILRECFLEXOPCOES.NRSEQUTILRECFLEX%TYPE,
  P_CODIGOCOMP        VARCHAR2,
  P_IDCOMP            VARCHAR2
) RETURN VARCHAR2 
AS 
  V_IDPRODUCAO VARCHAR2(1);
BEGIN
    
    V_IDPRODUCAO := 'N';
  
    IF P_IDCOMP = 'S' THEN
    
      BEGIN  
          SELECT 'S'
            INTO V_IDPRODUCAO 
          FROM UTILRECFLEXOPCOES
            WHERE CDFILIAL          = P_CDFILIAL
            AND   CDRECEITA         = P_CDRECEITA
            AND   NRORG             = P_NRORG
            AND   NRSEQUTILRECFLEX  = P_NRSEQUTILRECFLEX
            AND   CDRECEITAOP       = P_CODIGOCOMP
            AND   CDPRODUTO           IS NULL;
      EXCEPTION WHEN OTHERS THEN
          V_IDPRODUCAO := 'N';
      END; 
    ELSIF P_IDCOMP = 'P' THEN
     
       BEGIN 
          SELECT 'S'
            INTO V_IDPRODUCAO 
          FROM UTILRECFLEXOPCOES
            WHERE CDFILIAL          = P_CDFILIAL
            AND   CDRECEITA         = P_CDRECEITA
            AND   NRORG             = P_NRORG
            AND   NRSEQUTILRECFLEX  = P_NRSEQUTILRECFLEX
            AND   CDPRODUTO         = P_CODIGOCOMP
            AND   CDRECEITAOP         IS NULL;
      EXCEPTION WHEN OTHERS THEN
          V_IDPRODUCAO := 'N';
      END; 
    END IF;
  
  IF V_IDPRODUCAO IS NULL THEN
    V_IDPRODUCAO := 'N';
  END IF;
  
  RETURN V_IDPRODUCAO;
END;
/