CREATE OR REPLACE FUNCTION GET_SBG_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,
  P_CDCICLO IN VARCHAR2
) RETURN NUMBER IS
  QTD INT;
  QTDTOTAL INT;
BEGIN
IF P_CDCICLO = 'PROCIA' THEN
  SELECT COUNT(*) INTO QTD FROM PROCIASUBSEMAM 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;
  RETURN NVL(QTD+1, 0);
ELSE
  SELECT SUM(QTD) INTO QTDTOTAL FROM (
    SELECT ID, COUNT(*)+1 AS QTD FROM (
      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 AND CDCICLO = P_CDCICLO
    GROUP BY ID
  );
  RETURN NVL(QTDTOTAL, 0);
END IF;
END;
/