/*Funcao que considera a utilizacao de uma receita em menu */
CREATE OR REPLACE FUNCTION REC_EM_USO_MENUGRID
(
P_CDRECEITA IN VARCHAR2,
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRORG IN NUMBER,
P_SOMENTE_VIG_APROV IN VARCHAR2 DEFAULT 'N',
P_DTREF IN DATE DEFAULT NULL
) RETURN VARCHAR2
AS
  V_DTREF DATE;
  V_ESTA_EM_USO NUMBER;
  V_PAI_ESTA_EM_USO VARCHAR2(1);
BEGIN
   V_DTREF := P_DTREF;
   IF P_DTREF IS NOT NULL THEN
      V_DTREF := TRUNC(SYSDATE);
   END IF;

   SELECT COUNT(1)
     INTO V_ESTA_EM_USO
     FROM RECSUBSEMAM R
          INNER JOIN MANUALCIAM M ON R.CDFILIAL = M.CDFILIAL AND
                                      R.CDCIA = M.CDCIA AND
                                      R.CDMANCIA = M.CDMANCIA AND
                                      R.NRORG = M.NRORG                                      
          INNER JOIN SUBGRUSEMAM S ON S.CDFILIAL = R.CDFILIAL AND
                                      S.CDCIA = R.CDCIA AND
                                      S.CDMANCIA = R.CDMANCIA AND
                                      S.NRSERVICOCIAFILI = R.NRSERVICOCIAFILI AND
                                      S.CDCLASSE = R.CDCLASSE AND
                                      S.CDSUBGRU = R.CDSUBGRU AND
                                      S.NRSEQSUBGRUSEMAM = R.NRSEQSUBGRUSEMAM AND
                                      S.NRORG = R.NRORG          
    WHERE R.CDRECEITA = P_CDRECEITA
      AND VERIF_VALOR_LISTA(R.CDFILIAL,P_CDFILIAL) IS NOT NULL      
      AND VERIF_VALOR_LISTA(R.CDFILIAL||'|'||R.CDCIA,P_CDCIA) IS NOT NULL
      AND R.NRORG = P_NRORG
      AND M.IDSITUAPROPCIA NOT IN ('E', 'R')
      AND (
          (P_SOMENTE_VIG_APROV <> 'S') OR
          (M.IDSITUAPROPCIA = 'A' AND (TRUNC(P_DTREF) BETWEEN M.DTINIVGMA AND M.DTFIMVGMA) 
           AND (TRUNC(P_DTREF) BETWEEN S.DTINIVGSUMA AND S.DTFIMVGSUMA))
          )
      AND ROWNUM = 1;
   
   IF V_ESTA_EM_USO > 0 THEN
      RETURN 'S';
   END IF;

   FOR CUR_RECPAIS IN
   (
   SELECT S.CDRECEITA
     FROM SUBRECEITAM S
          INNER JOIN RECEITAM RECPAI ON RECPAI.CDRECEITA = S.CDRECEITA AND
                                        ( (RECPAI.IDRECFLEXIVEL <> 'P') OR (S.IDPREFERENCIA = 'S') )
          INNER JOIN FILIRECEITA F ON F.CDRECEITA = S.CDRECEITA AND F.NRVARIACAOREC = S.NRVARIACAOREC
    WHERE S.CDSUBRECEITA = P_CDRECEITA
      AND VERIF_VALOR_LISTA(F.CDFILIAL,P_CDFILIAL) IS NOT NULL
   ) LOOP
      V_PAI_ESTA_EM_USO := REC_EM_USO_MENUGRID(CUR_RECPAIS.CDRECEITA, P_CDFILIAL, P_CDCIA, P_NRORG, P_SOMENTE_VIG_APROV, P_DTREF);
      IF V_PAI_ESTA_EM_USO = 'S' THEN
        RETURN 'S';
      END IF;
   END LOOP;

   RETURN 'N';
END;
/