CREATE OR REPLACE FUNCTION GET_SBG_MAX_HEIGHT(
  P_CDFILIAL IN VARCHAR2,
  P_CDCIA IN VARCHAR2,
  P_CDMANCIA IN VARCHAR2,
  P_NRSERVICOCIAFILI IN NUMBER,
  P_CDCLASSE IN VARCHAR2,
  P_CDSUBGRU IN VARCHAR2,
  P_NRSEQSUBGRUSEMAM IN NUMBER
) RETURN NUMBER IS
  QTDTOTAL INT;
BEGIN
SELECT MAX(QTD) INTO QTDTOTAL FROM (
  SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, SUM(QTD) AS QTD FROM (
    SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, CDCICLO, ID, COUNT(*)+1 AS QTD FROM (
      SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, 'PROCIA' AS CDCICLO, '0' AS ID FROM PROCIASUBSEMAM
      UNION ALL
      SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, CDCICLO, '1' AS ID FROM RECSUBSEMAM
      UNION ALL
      SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, CDCICLO, '2' AS ID FROM PRODSUBSEMAM
      UNION ALL
      SELECT CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRUPRIN AS CDSUBGRU, NRSEQSUBGRUSEMAMPRIN AS NRSEQSUBGRUSEMAM, CDCICLO, '3' AS ID FROM SUBGRUPRINCIPALMENU
    )
    WHERE CDFILIAL = P_CDFILIAL AND CDCIA = P_CDCIA AND CDMANCIA = P_CDMANCIA AND NRSERVICOCIAFILI = P_NRSERVICOCIAFILI AND CDCLASSE = P_CDCLASSE AND CDSUBGRU = P_CDSUBGRU AND NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
    GROUP BY CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, CDCICLO, ID
  )
  GROUP BY CDFILIAL, CDCIA, CDMANCIA, NRSERVICOCIAFILI, CDCLASSE, CDSUBGRU, NRSEQSUBGRUSEMAM, CDCICLO
);
RETURN QTDTOTAL;
END;
/