CREATE OR REPLACE FUNCTION GET_PROPORCAO_ITEMFAT
(P_CDFILIAL IN VARCHAR2,
 P_CDCIA IN VARCHAR2,
 P_NRTABPRECO IN VARCHAR2,
 P_NRSERVICOCIAFILI IN NUMBER,
 P_CDCLASSE IN VARCHAR2,
 P_CDCICLO IN VARCHAR2,
 P_NRSEQPRECO IN NUMBER,
 P_DATAREF IN DATE,   
 P_NRORG IN NUMBER,
 P_RETORNA_PROP IN VARCHAR2,
 P_VALIDAERROS IN VARCHAR2 DEFAULT 'N') RETURN NUMBER
AS
   V_IDPROPORCAO_ATUAL VARCHAR2(1);
   V_QTPROPORCAO_ATUAL FLOAT;
   V_DSSERVICOCIAFILI VARCHAR2(500);
   V_IDVINCULOMANUAL VARCHAR2(1);
   V_IDPROPORCAO VARCHAR2(1);
   V_QTPROPORCAO FLOAT;
   V_IDSERVICO VARCHAR2(1);
   V_EXISTE_SUBGRU NUMBER;
   V_CDMANCIA VARCHAR2(4);
   V_QUANTIDADE_SUBGRUPOS NUMBER;
   V_IDPROPSUBSEMAM VARCHAR2(1);
   V_QTPROPSUBSEMAM FLOAT;
   V_QTPROPSUBSEMAM_DIF VARCHAR2(1);
   V_SERVICO VARCHAR2(2000);
   V_SGCLASSE VARCHAR2(20);
   V_IDCICLO VARCHAR2(20);
   V_IDUTILLOADIGUAL VARCHAR2(1);
BEGIN  
   /*Busca a proporcao gravada no preco*/
   SELECT MAX(S.DSSERVICOCIAFILI),MAX(CL.SGCLASSE), MAX(CI.IDCICLO), MAX(P.IDPROPORCAO), MAX(QTPROPORCAO), MAX(C.IDVINCULOMANUAL), MAX(G.IDSERVICO), MAX(IDUTILLOADIGUAL)
     INTO V_DSSERVICOCIAFILI,  V_SGCLASSE, V_IDCICLO, V_IDPROPORCAO, V_QTPROPORCAO, V_IDVINCULOMANUAL, V_IDSERVICO, V_IDUTILLOADIGUAL
     FROM PRECOSECIAM P
          INNER JOIN CIAFILI C ON C.CDFILIAL = P.CDFILIAL AND
                                  C.CDCIA = P.CDCIA AND
                                  C.NRORG = P.NRORG
          INNER JOIN SERVICOCIAFILI S ON S.CDFILIAL = P.CDFILIAL AND
                                         S.CDCIA = P.CDCIA AND
                                         S.NRSERVICOCIAFILI = P.NRSERVICOCIAFILI AND
                                         S.NRORG = P.NRORG
          INNER JOIN CLASSE CL ON CL.CDCLASSE = P.CDCLASSE
          INNER JOIN CICLO CI ON CI.CDCICLO = P.CDCICLO                                         
          INNER JOIN SERVICOGEN G ON G.NRSERVICOGEN = S.NRSERVICOGEN AND
                                     G.NRORG = S.NRORG          
    WHERE P.CDFILIAL = P_CDFILIAL
      AND P.CDCIA = P_CDCIA
      AND P.NRTABPRECO = P_NRTABPRECO
      AND P.NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
      AND P.CDCLASSE = P_CDCLASSE
      AND P.CDCICLO = P_CDCICLO
      AND P.NRSEQPRECO = P_NRSEQPRECO
      AND P.NRORG = P_NRORG;

   /*Se o servico for handling ou a companhia nao obrigar vinculo com menu grid, retorna as informacoes gravadas pelo sistema*/
 	IF NVL(V_IDVINCULOMANUAL, 'N') = 'N' OR V_IDSERVICO IN ('3', '5') THEN
       IF P_RETORNA_PROP = 'S' THEN
 	       RETURN TO_NUMBER(V_IDPROPORCAO);
       ELSE
          RETURN V_QTPROPORCAO;
       END IF;
   ELSE
       V_SERVICO := V_DSSERVICOCIAFILI||'$$'||V_SGCLASSE||'$$'||V_IDCICLO;
       V_QUANTIDADE_SUBGRUPOS := 0;
       FOR CUR_SUBGRU IN
       (
       SELECT S.IDPROPSUBSEMAM, CASE WHEN S.IDPROPSUBSEMAM IN ('1', '3') THEN NULL ELSE  S.QTPROPSUBSEMAM END AS QTPROPSUBSEMAM
         FROM SUBGRUTABPRECOM P
              INNER JOIN MANUALCIAM M ON M.CDFILIAL = P.CDFILIAL AND
                                         M.CDCIA = P.CDCIA AND
                                         M.CDMANCIA = P.CDMANCIA
              INNER JOIN SUBGRUSEMAM S ON S.CDFILIAL = P.CDFILIAL AND
                                          S.CDCIA = P.CDCIA AND
                                          S.CDMANCIA = P.CDMANCIA AND
                                          S.NRSERVICOCIAFILI = P.NRSERVICOCIAFILI AND
                                          S.CDCLASSE = P.CDCLASSE AND
                                          S.CDSUBGRU = P.CDSUBGRU AND
                                          S.NRSEQSUBGRUSEMAM = P.NRSEQSUBGRUSEMAM
        WHERE P.CDFILIAL = P_CDFILIAL
          AND P.CDCIA = P_CDCIA
          AND P.NRTABPRECO = P_NRTABPRECO         
          AND P.NRSERVICOCIAFILIPRECO = P_NRSERVICOCIAFILI
          AND P.CDCLASSE = P_CDCLASSE
          AND P.CDCICLO = P_CDCICLO
          AND P.NRSEQPRECO = P_NRSEQPRECO
          --Quando a validacao de erros estiver desabilitada, o menu nao precisa estar aprovado
          AND (P_DATAREF BETWEEN S.DTINIVGSUMA AND S.DTFIMVGSUMA OR 
               (M.IDSITUAPROPCIA = 'P' AND P_VALIDAERROS = 'N'))
       ) LOOP
          V_QUANTIDADE_SUBGRUPOS := V_QUANTIDADE_SUBGRUPOS + 1;
          IF CUR_SUBGRU.IDPROPSUBSEMAM IS NULL OR 
           (CUR_SUBGRU.QTPROPSUBSEMAM IS NULL AND CUR_SUBGRU.IDPROPSUBSEMAM NOT IN ('1', '3') ) THEN
             IF P_VALIDAERROS = 'S' THEN
                /*Operacao bloqueada. Servico <<NOME>> da classe <<CLASSE>> e ciclo <<CICLO>> esta vinculado a subgrupos vigentes cujo loading se encontra vazio.*/
                RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00090' || '$$' || V_SERVICO || 'APPERRMSGEND', TRUE);
             ELSE
                RETURN NULL;
             END IF;
          END IF;
          IF V_QUANTIDADE_SUBGRUPOS = 1 THEN
             V_IDPROPSUBSEMAM := CUR_SUBGRU.IDPROPSUBSEMAM;                    
             V_QTPROPSUBSEMAM := CUR_SUBGRU.QTPROPSUBSEMAM;
             V_QTPROPSUBSEMAM_DIF := 'N';
          ELSE
             IF (V_IDPROPSUBSEMAM <> CUR_SUBGRU.IDPROPSUBSEMAM) THEN
                V_IDPROPSUBSEMAM := 'D';
                V_QTPROPSUBSEMAM_DIF := 'S';
             END IF;
             IF V_IDPROPSUBSEMAM <> 'D' THEN
                IF (V_QTPROPSUBSEMAM <> CUR_SUBGRU.QTPROPSUBSEMAM OR
                    (CUR_SUBGRU.QTPROPSUBSEMAM IS NULL AND V_QTPROPSUBSEMAM IS NOT NULL) OR
                    (CUR_SUBGRU.QTPROPSUBSEMAM IS NOT NULL AND V_QTPROPSUBSEMAM IS NULL)
                   ) THEN
                   V_QTPROPSUBSEMAM_DIF := 'S';
                END IF;
             END IF;
          END IF;
       END LOOP;

       IF V_QUANTIDADE_SUBGRUPOS = 0 THEN
          IF P_VALIDAERROS = 'S' THEN
             /* Operacao bloqueada. O servico $param[0] da classe: $param[1] e ciclo: $param[2] do voo nao esta vinculado a um subgrupo vigente de menu grid. */
             RAISE_APPLICATION_ERROR(-20000,'APPERRMSGSTR' || 'APP-00063'|| '$$' || V_SERVICO || 'APPERRMSGEND');
          ELSE
             RETURN NULL;
          END IF; 
       ELSIF V_QUANTIDADE_SUBGRUPOS = 1 THEN         
          V_IDPROPORCAO_ATUAL := V_IDPROPSUBSEMAM;
          V_QTPROPORCAO_ATUAL := V_QTPROPSUBSEMAM;
       ELSIF V_QUANTIDADE_SUBGRUPOS > 1 THEN
          IF V_IDPROPSUBSEMAM = 'D' THEN
             V_IDPROPORCAO_ATUAL := '1';
             V_QTPROPORCAO_ATUAL := NULL;
          ELSIF V_IDPROPSUBSEMAM = '2' THEN
             IF V_QTPROPSUBSEMAM_DIF = 'S' THEN
                V_IDPROPORCAO_ATUAL := '1';
                V_QTPROPORCAO_ATUAL := NULL;
             ELSE
                V_IDPROPORCAO_ATUAL := '2';
                V_QTPROPORCAO_ATUAL := V_QTPROPSUBSEMAM;
             END IF;
          ELSIF V_IDPROPSUBSEMAM = '3' THEN
             V_IDPROPORCAO_ATUAL := '1';
             V_QTPROPORCAO_ATUAL := NULL;
          ELSIF V_IDPROPSUBSEMAM = '4' THEN            
             IF V_IDUTILLOADIGUAL = 'S' AND V_QTPROPSUBSEMAM_DIF = 'N' THEN
                V_IDPROPORCAO_ATUAL := '4';
                V_QTPROPORCAO_ATUAL := V_QTPROPSUBSEMAM;
             ELSE            
                V_IDPROPORCAO_ATUAL := '1';
                V_QTPROPORCAO_ATUAL := NULL;
             END IF;
          ELSIF V_IDPROPSUBSEMAM = '5' THEN
             V_IDPROPORCAO_ATUAL := '5';
             V_QTPROPORCAO_ATUAL := '1';
          END IF;
       END IF;

       IF P_RETORNA_PROP = 'S' THEN      
          RETURN TO_NUMBER(V_IDPROPORCAO_ATUAL);
       ELSE
          RETURN V_QTPROPORCAO_ATUAL;
       END IF;

    END IF;

    RETURN NULL;

END;
/