CREATE OR REPLACE
PACKAGE UTIL_APURAC AS 
    
   TYPE MINEFETMES_RECTYPE IS RECORD( 
      CDFILIAL     FILIAL.CDFILIAL%TYPE,
      NMTURNO      TURNOSERV.NMTURNO%TYPE,
      CDSERVFILI   SERVICO.CDSERVFILI%TYPE, 
      QTEFETIVOS   MINEFETMES.QTMINEFET%TYPE, 
      DSMINEFET    MINEFETMES.DSMINEFET%TYPE, 
      CDPRODUTO    PRODUTO.CDPRODUTO%TYPE, 
      DTBASE       DATE, 
      VRSERVICO    NUMBER); 
       
   TYPE MINEFETMES_CURTYPE IS REF CURSOR RETURN MINEFETMES_RECTYPE;    
    
   PROCEDURE OPENEFETMINCUR (C_MINEFETMES  IN OUT MINEFETMES_CURTYPE, 
                             P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE, 
                             P_DATAINI     IN DATE, 
                             P_DATAFIM     IN DATE, 
                             P_IDFATUPLAN  IN VARCHAR, 
                             P_SERVMINEFETMES IN VARCHAR); 
    
   FUNCTION CalcValrServ (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                          P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                          P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                          P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE, 
                          P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE,
                          P_DTREF      IN DATE DEFAULT NULL) 
 
            RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (CalcValrServ, WNDS, WNPS); 
 
   FUNCTION CalcValrServTur (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                             P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                             P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                             P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE, 
                             P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE,
                             P_DTREF      IN DATE DEFAULT NULL) 
 
            RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (CalcValrServTur, WNDS, WNPS); 
 
 
   FUNCTION CalcEfetMini (P_CDFILIAL IN FILIAL.CDFILIAL%TYPE, 
                          P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE, 
                          P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                          P_CDGRSERVFAT IN SERVCLIE.CDGRSERVFAT%TYPE, 
                          P_DTFATURAM IN EFECLIFAT.DTFATURAM%TYPE) 
            RETURN NUMBER; 
   PRAGMA RESTRICT_REFERENCES (CalcEfetMini, WNDS, WNPS); 
 
   PROCEDURE PrecoCorr (P_CDFILIAL IN FILIAL.CDFILIAL%TYPE, 
                        P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE, 
                        P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                        P_CDGRSERVFAT IN SERVCLIE.CDGRSERVFAT%TYPE, 
                        P_QTEFETIVOS IN NUMBER, 
                        P_QTEFETMINI IN NUMBER, 
                        P_IDSERVGRUP IN VARCHAR2, 
                        P_VRSERVICO IN OUT NUMBER, 
                        P_VRPERCCORR IN OUT NUMBER, 
                        P_IDCONSTABCORR IN VARCHAR2 DEFAULT 'N',
                        P_DATAINI IN DATE DEFAULT NULL,
                        P_DATAFIM IN DATE DEFAULT NULL,
                        P_UTEFEPLA IN VARCHAR DEFAULT 'N'); 
   PRAGMA RESTRICT_REFERENCES (PrecoCorr, WNDS, WNPS); 
 
 
   FUNCTION DsNmServico (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                         P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                         P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                         P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE) 
 
            RETURN VARCHAR; 
   PRAGMA RESTRICT_REFERENCES (DsNmServico, WNDS, WNPS); 
 
   FUNCTION DsNmServicoTur (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                            P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                            P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                            P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE, 
                            P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE) 
 
            RETURN VARCHAR; 
   PRAGMA RESTRICT_REFERENCES (DsNmServicoTur, WNDS, WNPS); 
    
   FUNCTION CalculaDtBase (P_NRDIABASE   IN NUMBER, 
                           P_DATAINI     IN DATE, 
                           P_DATAFIM     IN DATE)  
            RETURN DATE; 
   PRAGMA RESTRICT_REFERENCES (CalculaDtBase, WNDS, WNPS);        
   
   PROCEDURE INSERE_COMPENSACAO (P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                 P_CDFILIAPUR  IN FILIAL.CDFILIAL%TYPE,
                                 P_CDCLIENTE    IN CLIENTE.CDCLIENTE%TYPE,
                                 P_DATAINI      IN DATE,
                                 P_DATAFIM      IN DATE,
                                 P_NRAPURACFAT  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                 P_NRAPURACENTRAL  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                 P_CDFILIALCENTRAL IN FILIAL.CDFILIAL%TYPE);

   PROCEDURE INSERE_MINIMO_EFET_MES (P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                     P_CDFILIAPUR  IN FILIAL.CDFILIAL%TYPE,
                                     P_CDCLIENTE    IN CLIENTE.CDCLIENTE%TYPE,
                                     P_DATAINI      IN DATE,
                                     P_DATAFIM      IN DATE,
                                     P_NRAPURACFAT  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                     P_IDFATUPLAN   IN VARCHAR2,
                                     P_NRAPURACENTRAL  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                     P_CDFILIALCENTRAL IN FILIAL.CDFILIAL%TYPE);                                  
END UTIL_APURAC;
/

CREATE OR REPLACE
PACKAGE BODY UTIL_APURAC AS
   
   FUNCTION CalculaDtBase (P_NRDIABASE   IN NUMBER,
                           P_DATAINI     IN DATE, 
                           P_DATAFIM     IN DATE)  
            RETURN DATE AS 
   V_NRDIABASE VARCHAR2(2); 
   V_DATABASE  DATE;                         
   BEGIN 
      V_NRDIABASE := LPAD(TO_CHAR(P_NRDIABASE),2,'0'); 
      IF (TO_CHAR(P_DATAINI, 'MM') <> TO_CHAR(P_DATAFIM, 'MM')) THEN  
         IF TO_CHAR(P_DATAINI, 'DD') <= V_NRDIABASE THEN  
            IF V_NRDIABASE >= TO_CHAR(LAST_DAY(P_DATAINI),'DD') THEN  
               V_DATABASE := LAST_DAY(P_DATAINI); 
            ELSE  
                V_DATABASE := TO_DATE(P_NRDIABASE || '/' || TO_CHAR(P_DATAINI,'MM') || '/' || TO_CHAR(P_DATAINI, 'YYYY')); 
            END IF; 
         ELSE  
            V_DATABASE := TO_DATE(V_NRDIABASE || '/' || TO_CHAR(P_DATAFIM,'MM') || '/' || TO_CHAR(P_DATAFIM, 'YYYY'));  
         END IF; 
      ELSE 
         IF V_NRDIABASE >= TO_CHAR(LAST_DAY(P_DATAFIM),'DD') THEN  
            V_NRDIABASE := TO_CHAR(LAST_DAY(P_DATAFIM),'DD'); 
         END IF; 
         IF (V_NRDIABASE >= TO_CHAR(P_DATAINI,'DD')) AND (V_NRDIABASE <= TO_CHAR(P_DATAFIM,'DD')) THEN 
            V_DATABASE := TO_DATE(V_NRDIABASE || '/' || TO_CHAR(P_DATAFIM,'MM') || '/' || TO_CHAR(P_DATAFIM, 'YYYY'));     
         END IF; 
      END IF; 
    
      RETURN (V_DATABASE); 
   END;

   PROCEDURE INSERE_MINIMO_EFET_MES (P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                     P_CDFILIAPUR  IN FILIAL.CDFILIAL%TYPE,
                                     P_CDCLIENTE    IN CLIENTE.CDCLIENTE%TYPE,
                                     P_DATAINI      IN DATE,
                                     P_DATAFIM      IN DATE,
                                     P_NRAPURACFAT  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                     P_IDFATUPLAN   IN VARCHAR2,
                                     P_NRAPURACENTRAL  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                     P_CDFILIALCENTRAL IN FILIAL.CDFILIAL%TYPE)
   AS
   V_IDPRINCIPAL VARCHAR2(1);
   V_SEQ VARCHAR2(20);
   V_NRHISTCTRLAPURFAT NUMBER;
   BEGIN

      /*VERIFICA SE CLIENTE E PRINCIPAL PARA CONTINUAR ROTINA*/
      SELECT NVL(IDCLIENPRIN,'N') 
        INTO V_IDPRINCIPAL 
        FROM CLIENFILIAL
       WHERE CDFILIAL = P_CDFILIAPUR AND
             CDCLIENTE = P_CDCLIENTE;
       
      IF V_IDPRINCIPAL = 'S' THEN
         /*RETORNA OS ITENS QUE NAO ATINGIRAM O MINIMO DE EFETIVOS POR MES PARA INCLUIR NA NF*/
         FOR C_MIN IN (SELECT T.CDFILIAL, T.CDTURNO, T.NMTURNO, T.CDSERVFILI, T.NMSERVICO, (T.QTMINEFET - T.QTEFETIVOS) QTEFETIVOS,  
                              T.DSMINEFET, T.CDPRODUTO, T.DTBASE, T.VRSERVICO, NULL AS CDGRSERVFAT, 'TU' AS TABELA 
                         FROM (SELECT MI.CDFILIAL, MAX(SM.CDTURNO) AS CDTURNO, MI.NMTURNO, MAX(TS.CDSERVFILI) AS CDSERVFILI, SS.NMSERVICO, MI.QTMINEFET , SUM(SM.QTEFETIVOS)QTEFETIVOS,  
                                      MI.DSMINEFET, MAX(UTIL_APURAC.CALCVALRSERVTUR (P_CDFILIAPUR,P_CDCLIENTE,SM.CDSERVFILI,
                                      UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), SM.CDTURNO)) VRSERVICO, 
                                      MI.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) DTBASE 
                                 FROM SUM_EFETIVOS SM, CLIENFILIAL CF, MINEFETMESTU MI, TURNOSERV TS, SERVICO SS
                                WHERE SM.CDFILIAL       = P_CDFILIAPUR  
                                  AND SM.CDFILIAL       = MI.CDFILIAL 
                                  AND SM.CDFILIAL       = TS.CDFILIAL 
                                  AND UPPER(MI.NMTURNO) = UPPER(TS.NMTURNO) 
                                  AND SM.CDTURNO        = TS.CDTURNO 
                                  AND SM.CDFILIAL       = CF.CDFILIAL  
                                  AND SM.CDSERVFILI     = TS.CDSERVFILI
								  AND SS.CDFILIAL       = SM.CDFILIAL
                                  AND SS.CDSERVFILI     = SM.CDSERVFILI
                                  AND (SM.DTFATURAM BETWEEN ADD_MONTHS(UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM),-1)+1 AND 
                                                            UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) ) 
                                  AND SM.CDTURNO IS NOT NULL 
                                  AND SM.CDFILIAL     = CF.CDFILIAL 
                                  AND SM.CDCLIENTE    = CF.CDCLIENTE 
                                  AND ((MI.IDTERCEIROS = 'N' AND CF.IDCLIENPRIN = 'S') OR (MI.IDTERCEIROS = 'S')) 
                                  AND ((P_IDFATUPLAN  = 'N' AND SM.IDFATUPLAN = 'F') OR (P_IDFATUPLAN = 'S' ))  
                                 GROUP BY MI.CDFILIAL, MI.NMTURNO, SS.NMSERVICO, MI.QTMINEFET, MI.DSMINEFET, 
                                          MI.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM)) T  
   
                       UNION ALL 
                  
                       SELECT T.CDFILIAL, T.CDTURNO, T.NMTURNO, T.CDSERVFILI, T.NMSERVICO, (T.QTMINEFET - T.QTEFETIVOS) QTEFETIVOS, T.DSMINEFET,  
                               T.CDPRODUTO, T.DTBASE, T.VRSERVICO, T.CDGRSERVFAT, 'GD' AS TABELA 
                         FROM (SELECT MI.CDFILIAL, NULL AS CDTURNO, NULL NMTURNO, MI.CDSERVFILI, SS.NMSERVICO, MI.QTMINEFET, SUM(SM.QTEFETIVOS)QTEFETIVOS, MI.DSMINEFET,  
                                      SM.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) DTBASE, 
                                      UTIL_APURAC.CALCVALRSERV (P_CDFILIAPUR,P_CDCLIENTE,SM.CDSERVFILI,
                                      UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), NULL) VRSERVICO,
                                      SM.CDGRSERVFAT
                                 FROM SUM_EFETIVOS SM, CLIENFILIAL CF , MINEFETMES MI, SERVICO SS  
                                WHERE SM.CDFILIAL   = P_CDFILIAPUR  
                                  AND SM.CDFILIAL   = MI.CDFILIAL 
                                  AND SM.CDSERVFILI = MI.CDSERVFILI 
								  AND SS.CDFILIAL   = SM.CDFILIAL
                                  AND SS.CDSERVFILI = SM.CDSERVFILI
                                  AND (SM.DTFATURAM BETWEEN ADD_MONTHS(UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM),-1)+1 AND 
                                                            UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) ) 
                                  AND SM.CDFILIAL   = CF.CDFILIAL 
                                  AND SM.CDCLIENTE  = CF.CDCLIENTE 
                                  AND ((MI.IDTERCEIROS = 'N' AND CF.IDCLIENPRIN = 'S') OR (MI.IDTERCEIROS = 'S')) 
                                  AND ((P_IDFATUPLAN  = 'N' AND SM.IDFATUPLAN = 'F') OR (P_IDFATUPLAN = 'S' ))  
                                  AND SM.CDTURNO IS NULL 
                               GROUP BY MI.CDFILIAL, NULL, MI.CDSERVFILI, SS.NMSERVICO, MI.QTMINEFET, MI.DSMINEFET,  
                                       SM.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), 
                                       UTIL_APURAC.CALCVALRSERV (P_CDFILIAPUR,P_CDCLIENTE,SM.CDSERVFILI,UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), NULL),
                                       SM.CDGRSERVFAT) T
         )LOOP
            IF C_MIN.TABELA = 'TU' THEN
               INSERT INTO TRAB_APURAC_TU
                  (CDFILIALAPTU,   NRAPURACTU, CDCLIENTEAPTU,
                   CDSERVFILIAPTU, DTAPURACFATTU, CDPRODUTOAPTU,
                   NREFETIVOSAPTU, VRPRECUNITAPTU,DSPRODEMISAPTU,
                   IDAJUSEFETAPTU, IDPRODSUBSTU, CDTURNOTU, IDUTILEQUITU, NMSERVICOTU, CDGRSERVFAT, IDTABELA, NRAPURACENTRAL, CDFILIALCENTRAL, CDFILIAGREGADA )
               VALUES (P_CDFILIAL, P_NRAPURACFAT, P_CDCLIENTE,
                       C_MIN.CDSERVFILI, C_MIN.DTBASE, C_MIN.CDPRODUTO,
                       C_MIN.QTEFETIVOS, C_MIN.VRSERVICO,C_MIN.DSMINEFET,
                       'N', 'N', C_MIN.CDTURNO, 'N', C_MIN.NMSERVICO, C_MIN.CDGRSERVFAT, 'TU', P_NRAPURACENTRAL, P_CDFILIALCENTRAL, P_CDFILIAPUR);     
            
            ELSE
               INSERT INTO TRAB_APURAC_GD
                  (CDFILIALAPGD, NRAPURACGD, CDCLIENTEAPGD,
                   CDSERVFILIAPGD, DTAPURACFATGD, CDPRODUTOAPGD,
                   NREFETIVOSAPGD, VRPRECUNITAPGD,DSPRODEMISAPGD,
                   IDAJUSEFETAPGD, IDPRODSUBSGD, IDUTILEQUIGD, NMSERVICOGD, CDGRSERVFAT, IDTABELA, NRAPURACENTRAL, CDFILIALCENTRAL, CDFILIAGREGADA)
               VALUES (P_CDFILIAL, P_NRAPURACFAT, P_CDCLIENTE,
                       C_MIN.CDSERVFILI, C_MIN.DTBASE, C_MIN.CDPRODUTO,
                       C_MIN.QTEFETIVOS, C_MIN.VRSERVICO,C_MIN.DSMINEFET,
                       'N', 'N', 'N', C_MIN.NMSERVICO, C_MIN.CDGRSERVFAT, 'GD', P_NRAPURACENTRAL, P_CDFILIALCENTRAL, P_CDFILIAPUR);
            
            END IF;
         END LOOP;
      END IF;
   END;

   PROCEDURE INSERE_COMPENSACAO (P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
                                 P_CDFILIAPUR  IN FILIAL.CDFILIAL%TYPE,
                                 P_CDCLIENTE   IN CLIENTE.CDCLIENTE%TYPE,
                                 P_DATAINI     IN DATE,
                                 P_DATAFIM     IN DATE,
                                 P_NRAPURACFAT IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                 P_NRAPURACENTRAL  IN NOTAFISCAL.NRAPURACFAT%TYPE,
                                 P_CDFILIALCENTRAL IN FILIAL.CDFILIAL%TYPE)
   AS 
   V_IDPRINCIPAL VARCHAR2(1);
   V_DTBASE DATE;
   V_DTABASEANT DATE; /*DATA BASE DO MES ANTERIOR PARA VERIFICAR REINCIDENCIA DE REGISTROS DE COMPENSACAO*/
   V_NRCTRLCOMPENSACAO VARCHAR2(20);
   V_QTEFETIVOS NUMBER;
   V_QTEFETIVOS_DIA NUMBER;
   V_QTEFETIVOS_PERIODO NUMBER;
   V_EXISTECOMP NUMBER; /*SE EXISTE COMPENSACAO NO ULTIMO PERIODO*/
   V_VRSERVICO FLOAT;
   V_PERCCORRECAO FLOAT;
   V_PERCCORECAOPRXMES FLOAT;
   V_QTDEMINIMO NUMBER; /*QUANTIDADE TOTAL DO MINIMO DE EFETIVOS NO PERIODO*/
   V_EXISTECOMPCONSSEC NUMBER; /*SE EXISTE COMPENSACAO CONSECUTIVA*/ 
   V_PERCENTUAL FLOAT;
   V_SEQ VARCHAR2(20);
   V_NRHISTCTRLAPURFAT NUMBER;
   BEGIN
      /*VERIFICA SE CLIENTE E PRINCIPAL PARA CONTINUAR ROTINA*/
      SELECT IDCLIENPRIN 
        INTO V_IDPRINCIPAL 
        FROM CLIENFILIAL
       WHERE CDFILIAL = P_CDFILIAPUR AND
             CDCLIENTE = P_CDCLIENTE;
       
      IF V_IDPRINCIPAL = 'S' THEN
      
      
      
         /*RETORNA SERVICOS ASSOCIADOS AO CLIENTE PARA SEREM APURADOS DE ACORDO COM A MAIOR DATA DE VIGENCIA MENOR OU IGUAL AO PERIODO INICIAL DA APURACAO*/
         FOR C_SERV IN (SELECT P.NRPARAMCOMPENSACAO, SC.CDSERVFILI, S.NMSERVICO, S.CDPRODUTO, SC.CDGRSERVFAT, S.CDSERVPREQUI,
                               P.DTVIGENCIA, P.IDAPLICPERCSOBRE, P.IDCORRIGEMINPOR, SUBSTR(P.DSEFETMINPERIOD,0,400) AS DSEFETMINPERIOD, P.IDCONSTERCEIROS, P.IDATIVATABCOR, 
                               P.VRPERCPADRAO, P.VRPERCPROXMES, P.NRDIABASE, P.NRDIASPERIODO, P.IDGERANFSEPARADA,
                               P.QTEFETMINIPERIOD, P.QTEFETMINDIASEG, P.QTEFETMINDIATER, P.QTEFETMINDIAQUA, P.QTEFETMINDIAQUI, P.QTEFETMINDIASEX,
                               P.QTEFETMINDIASAB, P.QTEFETMINDIADOM, P.QTEFETMINDIANUTIL 
                          FROM SERVCLIE SC INNER JOIN PARAMCOMPENSACAO P ON P.CDFILIAL = SC.CDFILIAL AND 
                                                                            P.CDSERVFILI = SC.CDSERVFILI AND 
                                                                            SC.CDCLIENTE = P_CDCLIENTE AND
                                                                            SC.CDFILIAL = P_CDFILIAPUR
                                           INNER JOIN SERVICO S ON S.CDFILIAL = P.CDFILIAL AND
                                                                   S.CDSERVFILI = P.CDSERVFILI        
                         WHERE P.DTVIGENCIA = (SELECT MAX(PP.DTVIGENCIA)
                                                 FROM PARAMCOMPENSACAO PP
                                                WHERE PP.CDFILIAL = P.CDFILIAL AND
                                                      PP.CDSERVFILI = P.CDSERVFILI AND
                                                      PP.DTVIGENCIA <= P_DATAINI)
                                AND UTIL_APURAC.CalculaDtBase (P.NRDIABASE, P_DATAINI, P_DATAFIM) IS NOT NULL
         )LOOP

            /*VERIFICA O DIA BASE PARA CADA SERVICO PARA PESQUISAR EFETIVOS*/
            V_DTBASE := UTIL_APURAC.CalculaDtBase (C_SERV.NRDIABASE, P_DATAINI, P_DATAFIM);
            /*O DIA BASE ANTERIOR (PARA VERIFICAR SE EXISTEM LANCAMENTOS CONSECUTIVOS SERA CALCULADO DE ACORDO COM O NRO DE DIAS DE DIAS DO PERIODO DE APURACAO*/
            V_DTABASEANT := ADD_MONTHS(UTIL_APURAC.CalculaDtBase (C_SERV.NRDIABASE, P_DATAINI, P_DATAFIM), - TRUNC(C_SERV.NRDIASPERIODO/30))+1;

            /*VERIFICA SE HOUVE COMPENSACAO DENTRO DO ULTIMO PERIODO, SE SIM, N�O GERA COMPENSACAO NOVAMENTE*/
            IF (C_SERV.NRDIASPERIODO = 30) THEN /*SE O PERIODO E DE 30 DIAS NAO VAI HAVER DUAS COMPENSACOES DENTRO DO MESMO PERIODO*/
               V_EXISTECOMP := 0;
            ELSE /*SE O PERIODO E > 30 DIAS ENTAO TEM QUE VERIFICAR SE TEM COMPENSACAO GERADA DENTRO DO ULTIMO PERIODO, SE SIM, NAO GERA COMPENSACAO*/
               BEGIN
                  SELECT COUNT(NRCTRLCOMPENSACAO)
                    INTO V_EXISTECOMP
                    FROM CTRLCOMPENSACAO CP
                   WHERE (CP.CDFILIAL = P_CDFILIAL) AND
                         (CP.CDFILIAPUR = P_CDFILIAPUR) AND
                         (CP.CDCLIENTE = P_CDCLIENTE) AND
                         (CP.CDSERVFILI = C_SERV.CDSERVFILI) AND
                         (CP.DTBASE BETWEEN V_DTABASEANT AND V_DTBASE);
               EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_EXISTECOMP := 0;
               END;
               IF V_EXISTECOMP IS NULL THEN
                  V_EXISTECOMP := 0;
               END IF;
            END IF;
            /*SE NAO HOUVE COMPENSACAO DENTRO DO ULTIMO PERIODO ENTAO PODE GERAR COMPENSACAO*/
            IF (V_EXISTECOMP = 0) THEN

               V_QTEFETIVOS := 0;
               V_QTDEMINIMO := 0;
               /*SE CORRIGE EFETIVOS POR PERIODO SOMA A QUANTIDADE DE EFETIVOS DO PERIODO E COMPARA COM O M�NIMO PARA O PER�ODO*/
               IF C_SERV.IDCORRIGEMINPOR = 'PERIODO' THEN
                  BEGIN
                     SELECT SUM(NVL(SM.QTEFETIVOS,0))QTEFETIVOS
                       INTO V_QTEFETIVOS_PERIODO
                       FROM SUM_EFETIVOS SM INNER JOIN (SELECT SC.CDFILIAL, SC.CDCLIENTE, SC.CDSERVFILI
                                                          FROM SERVCLIE SC INNER JOIN SERVICOCOMPENSACAO SP
                                                            ON (SC.CDFILIAL = SP.CDFILIAL AND
                                                                SC.CDSERVFILI = SP.CDSERVFILI AND
                                                                SP.NRPARAMCOMPENSACAO = C_SERV.NRPARAMCOMPENSACAO)
                                                         WHERE C_SERV.IDCONSTERCEIROS = 'S' AND
                                                               SC.IDCONSTERCCOMP = 'S' AND
                                                               SC.CDFILIAL = P_CDFILIAPUR AND
                                                               SC.CDCLIENTE <> P_CDCLIENTE
                                                         UNION ALL
                                                         SELECT P_CDFILIAPUR, P_CDCLIENTE, C_SERV.CDSERVFILI
                                                           FROM DUAL)SC
                         ON (SM.CDFILIAL = SC.CDFILIAL) AND
                            (SM.CDCLIENTE  = SC.CDCLIENTE) AND
                            (SM.CDSERVFILI = SC.CDSERVFILI) AND
                            (SM.DTFATURAM BETWEEN V_DTABASEANT AND V_DTBASE) AND
                            (SM.CDTURNO IS NULL);
                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_QTEFETIVOS_PERIODO := 0;
                  END;
                  IF V_QTEFETIVOS_PERIODO IS NULL THEN
                     V_QTEFETIVOS_PERIODO := 0;
                  END IF;
                  IF (C_SERV.IDCORRIGEMINPOR = 'PERIODO') AND (V_QTEFETIVOS_PERIODO <  C_SERV.QTEFETMINIPERIOD) THEN
                     V_QTEFETIVOS := CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_PERIODO ELSE C_SERV.QTEFETMINIPERIOD - V_QTEFETIVOS_PERIODO END;
                  END IF;
                  V_QTDEMINIMO := C_SERV.QTEFETMINIPERIOD;
               ELSE
                  /*SE CORRIGE EFETIVOS POR DIA SOMA A QUANTIDADE DE EFETIVOS PARA CADA DIA E COMPARA COM O M�NIMO PARA CADA DIA DO PER�ODO*/
                  FOR C_DATAS IN (SELECT DT.DTDATA, CASE WHEN FI.DTFERIFILI IS NOT NULL THEN 8
                                                         WHEN FD.DTFERICARD IS NOT NULL THEN 8
                                                         ELSE TO_NUMBER(TO_CHAR(DT.DTDATA,'D')) END AS DTMINIMO
                                    FROM DATA DT LEFT JOIN FERIFILI FI ON FI.CDFILIAL = P_CDFILIAPUR AND FI.DTFERIFILI = DT.DTDATA
                                                 LEFT JOIN FERICARD FD ON FD.CDFILIAL = P_CDFILIAPUR AND FD.DTFERICARD = DT.DTDATA
                                   WHERE DT.DTDATA BETWEEN V_DTABASEANT AND V_DTBASE
                                  ORDER BY DT.DTDATA
                  ) LOOP
                     BEGIN
                        SELECT SUM(NVL(SM.QTEFETIVOS,0))QTEFETIVOS
                          INTO V_QTEFETIVOS_DIA
                          FROM SUM_EFETIVOS SM INNER JOIN (SELECT SC.CDFILIAL, SC.CDCLIENTE, SC.CDSERVFILI
                                                             FROM SERVCLIE SC INNER JOIN SERVICOCOMPENSACAO SP
                                                               ON (SC.CDFILIAL = SP.CDFILIAL AND
                                                                   SC.CDSERVFILI = SP.CDSERVFILI AND
                                                                   SP.NRPARAMCOMPENSACAO = C_SERV.NRPARAMCOMPENSACAO)
                                                            WHERE C_SERV.IDCONSTERCEIROS = 'S' AND
                                                                  SC.IDCONSTERCCOMP = 'S' AND
                                                                  SC.CDFILIAL = P_CDFILIAPUR AND
                                                                  SC.CDCLIENTE <> P_CDCLIENTE
                                                            UNION ALL
                                                            SELECT P_CDFILIAPUR, P_CDCLIENTE, C_SERV.CDSERVFILI
                                                              FROM DUAL)SC
                            ON (SM.CDFILIAL = SC.CDFILIAL) AND
                               (SM.CDCLIENTE  = SC.CDCLIENTE) AND
                               (SM.CDSERVFILI = SC.CDSERVFILI) AND
                               (SM.DTFATURAM = C_DATAS.DTDATA) AND
                               (SM.CDTURNO IS NULL);
                     EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_QTEFETIVOS_DIA := 0;
                     END;
                     IF V_QTEFETIVOS_DIA IS NULL THEN
                        V_QTEFETIVOS_DIA := 0;
                     END IF;
                     /*SE A APURACAO E POR DIA, ENTAO COMPARA A SOMA DE EFETIVOS APURADOS POR DIA DA SEMANA COM O MINIMO POR DIA E VAI SOMANDO A QTDE A COMPENSAR                                                                                  POR EFETIVOS APURADOS OU PELA DIFERENCA ENTRE MINIMO E APURADO*/
                     IF C_DATAS.DTMINIMO = 1 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIADOM;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIADOM THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIADOM - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 2 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIASEG;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIASEG THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIASEG - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 3 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIATER;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIATER THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIATER - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 4 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIAQUA;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIAQUA THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIAQUA - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 5 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIAQUI;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIAQUI THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIAQUI - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 6 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIASEX;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIASEX THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIASEX - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 7 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIASAB;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIASAB THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIASAB - V_QTEFETIVOS_DIA END;
                        END IF;
                     ELSIF C_DATAS.DTMINIMO = 8 THEN
                        V_QTDEMINIMO := V_QTDEMINIMO + C_SERV.QTEFETMINDIANUTIL;
                        IF V_QTEFETIVOS_DIA < C_SERV.QTEFETMINDIANUTIL THEN
                           V_QTEFETIVOS := V_QTEFETIVOS + CASE WHEN C_SERV.IDAPLICPERCSOBRE = 'EFETIVOS_APURADOS' THEN V_QTEFETIVOS_DIA ELSE C_SERV.QTEFETMINDIANUTIL - V_QTEFETIVOS_DIA END;
                        END IF;
                     END IF;
                  END LOOP;
               END IF;

               /*SE A QUANTIDADE DE EFETIVOS A COMPENSAR E MAIOR QUE ZERO, SERA INSERIDO O REGISTRO DE COMPENSACAO*/
               IF V_QTEFETIVOS > 0 THEN
                  /*VERIFICA SE EXISTE LAN�AMENTO CONSECUTIVO PARA APLICAR VRPROXIMOMES*/
                  BEGIN
                     SELECT COUNT(NRCTRLCOMPENSACAO)
                       INTO V_EXISTECOMPCONSSEC
                       FROM CTRLCOMPENSACAO
                      WHERE CDFILIAL = P_CDFILIAL AND 
                            CDSERVFILI = C_SERV.CDSERVFILI AND
                            CDFILIAPUR = P_CDFILIAPUR AND
                            CDCLIENTE = P_CDCLIENTE AND
                            DTBASE BETWEEN V_DTABASEANT - C_SERV.NRDIASPERIODO AND V_DTABASEANT;
                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_EXISTECOMPCONSSEC := 0;
                  END;
                     
                  /*VERIFICA SE UTILIZA TABELA DE CORRECAO*/
                  IF C_SERV.IDATIVATABCOR = 'S' THEN
                     /*CALCULA PERCENTUAL ENTRE O MINIMO PROPOSTO E A QUANTIDADE DE EFETIVOS APURADOS, SE � POR DIA, SOMA O MINIMO DE CADA DIA PARA ACHAR O MINIMO DO PERIODO*/
                     V_PERCENTUAL := ((V_QTEFETIVOS / V_QTDEMINIMO) * 100);
                     /*SE EXISTE, VERIFICA A FAIXA DE CORRECAO*/
                     BEGIN
                        SELECT VRPERCCORRECAO, VRPERCPROXMES
                         INTO V_PERCCORRECAO, V_PERCCORECAOPRXMES
                         FROM COMPENSACAO 
                        WHERE NRPARAMCOMPENSACAO = C_SERV.NRPARAMCOMPENSACAO 
                          AND V_PERCENTUAL >= VRPERCINICIAL
                          AND V_PERCENTUAL <= VRPERCFINAL;
                     EXCEPTION 
                        WHEN NO_DATA_FOUND THEN
                           V_PERCCORRECAO := 1;
                           V_PERCCORECAOPRXMES := 1;
                     END;
                     
                     V_VRSERVICO := (UTIL_APURAC.CalcValrServ(P_CDFILIAPUR, P_CDCLIENTE, C_SERV.CDSERVFILI, V_DTBASE, NULL) 
                                      * CASE  V_EXISTECOMPCONSSEC WHEN  0 THEN V_PERCCORRECAO ELSE V_PERCCORECAOPRXMES END)/100;  
                     
                  ELSE
                     /*SE NAO EXISTE, APLICA O PERCENTUAL DO VALOR SOBRE A PARAMETRIZACAO PADARAO*/
                     V_VRSERVICO := (UTIL_APURAC.CalcValrServ(P_CDFILIAPUR, P_CDCLIENTE, C_SERV.CDSERVFILI, V_DTBASE, NULL) 
                                     * CASE V_EXISTECOMPCONSSEC WHEN 0 THEN C_SERV.VRPERCPADRAO ELSE C_SERV.VRPERCPROXMES END)/100;
                  END IF;
                                
                  /*INSERE NA TABELA O REGISTRO DE COMPENSACAO PENDENTE PARA A APURACAO*/
                  NOVO_CODIGO('CTRLCOMPENSACAO',V_NRCTRLCOMPENSACAO);
                  V_NRCTRLCOMPENSACAO := SUBSTR(V_NRCTRLCOMPENSACAO,11,10);
                  INSERT INTO CTRLCOMPENSACAO (NRCTRLCOMPENSACAO, CDFILIAL, CDFILIAPUR, CDCLIENTE, CDSERVFILI, DTAPURACINI, DTAPURACFIM, QTEFETDIF, NRAPURACCOMP,
                                               DTBASE, VRSERVICO, IDREINCIDENTE, NRPARAMCOMPENSACAO)
                  VALUES (V_NRCTRLCOMPENSACAO, P_CDFILIAL, P_CDFILIAPUR, P_CDCLIENTE,C_SERV.CDSERVFILI, P_DATAINI, P_DATAFIM, 
                          V_QTEFETIVOS, P_NRAPURACFAT,
                          V_DTBASE, V_VRSERVICO, CASE V_EXISTECOMPCONSSEC WHEN 0 THEN 'N' ELSE 'S' END, C_SERV.NRPARAMCOMPENSACAO ); 
                          
                  /*SE NAO GERA NF SEPARADA ENTAO VAI INSERIR NA TRAB_APURAC_GD O ITEM DA COMPENSACAO*/
                  IF (C_SERV.IDGERANFSEPARADA = 'N') THEN

                     INSERT INTO TRAB_APURAC_GD
                        (CDFILIALAPGD, NRAPURACGD, CDCLIENTEAPGD,
                         CDSERVFILIAPGD, DTAPURACFATGD, CDPRODUTOAPGD,
                         NREFETIVOSAPGD, VRPRECUNITAPGD,DSPRODEMISAPGD,
                         IDAJUSEFETAPGD, IDPRODSUBSGD, IDUTILEQUIGD, NMSERVICOGD, CDPTOVNDFATURTAP, CDGRSERVFAT, IDTABELA, NRAPURACENTRAL, CDFILIALCENTRAL, CDFILIAGREGADA)
                     VALUES (P_CDFILIAL, P_NRAPURACFAT, P_CDCLIENTE,
                             C_SERV.CDSERVFILI, V_DTBASE, C_SERV.CDPRODUTO,
                             V_QTEFETIVOS, V_VRSERVICO,C_SERV.DSEFETMINPERIOD,
                             'N', 'N', CASE WHEN C_SERV.CDSERVPREQUI IS NULL THEN 'N' ELSE 'S' END, C_SERV.NMSERVICO, NULL, C_SERV.CDGRSERVFAT, 'CP', P_NRAPURACENTRAL, P_CDFILIALCENTRAL, P_CDFILIAPUR);
                     
                  END IF;
               END IF;
            END IF;
         END LOOP;
      END IF;
   END;
 
   PROCEDURE OPENEFETMINCUR (C_MINEFETMES  IN OUT MINEFETMES_CURTYPE, 
                             P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE, 
                             P_DATAINI     IN DATE, 
                             P_DATAFIM     IN DATE, 
                             P_IDFATUPLAN  IN VARCHAR, 
                             P_SERVMINEFETMES IN VARCHAR) IS  
                              
   V_CDCLIENTE VARCHAR2(21); 
   BEGIN 
      BEGIN 
        SELECT CDCLIENTE 
          INTO V_CDCLIENTE 
          FROM CLIENFILIAL 
         WHERE CDFILIAL = P_CDFILIAL 
           AND IDCLIENPRIN = 'S'; 
      EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
          V_CDCLIENTE := NULL; 
      END; 
          
      OPEN C_MINEFETMES FOR 
         SELECT DISTINCT T.CDFILIAL, T.NMTURNO, NULL CDSERVFILI, (T.QTMINEFET - T.QTEFETIVOS) QTEFETIVOS,  
                T.DSMINEFET, T.CDPRODUTO, T.DTBASE, T.VRSERVICO 
           FROM  
              (SELECT MI.CDFILIAL, MI.NMTURNO,MI.QTMINEFET , SUM(SM.QTEFETIVOS)QTEFETIVOS,  
                      MI.DSMINEFET, UTIL_APURAC.CALCVALRSERVTUR (P_CDFILIAL,V_CDCLIENTE,SM.CDSERVFILI,UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), SM.CDTURNO) VRSERVICO, 
                      MI.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) DTBASE 
                 FROM SUM_EFETIVOS SM, CLIENFILIAL CF, MINEFETMESTU MI, TURNOSERV TS 
                WHERE SM.CDFILIAL     = P_CDFILIAL  
                  AND SM.CDFILIAL     = MI.CDFILIAL 
                  AND SM.CDFILIAL     = TS.CDFILIAL 
                  AND MI.NMTURNO      = TS.NMTURNO 
                  AND SM.CDTURNO      = TS.CDTURNO 
                  AND SM.CDFILIAL     = CF.CDFILIAL  
                  AND SM.CDSERVFILI   = TS.CDSERVFILI 
                  AND (SM.DTFATURAM BETWEEN ADD_MONTHS(UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM),-1)+1 AND UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) ) 
                  AND SM.CDTURNO IS NOT NULL 
                  AND SM.CDFILIAL     = CF.CDFILIAL 
                  AND SM.CDCLIENTE    = CF.CDCLIENTE 
                  AND ((MI.IDTERCEIROS = 'N' AND CF.IDCLIENPRIN = 'S') OR (MI.IDTERCEIROS = 'S')) 
                  AND ((P_IDFATUPLAN  = 'N' AND SM.IDFATUPLAN = 'F') OR (P_IDFATUPLAN = 'S' ))  
               GROUP BY MI.CDFILIAL, MI.NMTURNO, MI.QTMINEFET, MI.DSMINEFET,  
               UTIL_APURAC.CALCVALRSERVTUR (P_CDFILIAL,V_CDCLIENTE,SM.CDSERVFILI,UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), SM.CDTURNO), 
                        MI.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM)) T  
 
               
         UNION ALL 
               
         SELECT T.CDFILIAL, T.NMTURNO, T.CDSERVFILI, (T.QTMINEFET - T.QTEFETIVOS) QTEFETIVOS, T.DSMINEFET,  
                T.CDPRODUTO, T.DTBASE, T.VRSERVICO 
           FROM 
              (SELECT MI.CDFILIAL, NULL NMTURNO, MI.CDSERVFILI,MI.QTMINEFET, SUM(SM.QTEFETIVOS)QTEFETIVOS, MI.DSMINEFET,  
                      SM.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) DTBASE, 
                      UTIL_APURAC.CALCVALRSERV (P_CDFILIAL,V_CDCLIENTE,SM.CDSERVFILI,UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), NULL) VRSERVICO 
                 FROM SUM_EFETIVOS SM, CLIENFILIAL CF , MINEFETMES MI  
                WHERE SM.CDFILIAL   = P_CDFILIAL  
                  AND SM.CDFILIAL   = MI.CDFILIAL 
                  AND SM.CDSERVFILI = MI.CDSERVFILI 
                  AND (SM.DTFATURAM BETWEEN ADD_MONTHS(UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM),-1)+1 AND UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM) ) 
                  AND SM.CDFILIAL   = CF.CDFILIAL 
                  AND SM.CDCLIENTE  = CF.CDCLIENTE 
                  AND ((MI.IDTERCEIROS = 'N' AND CF.IDCLIENPRIN = 'S') OR (MI.IDTERCEIROS = 'S')) 
                  AND ((P_IDFATUPLAN  = 'N' AND SM.IDFATUPLAN = 'F') OR (P_IDFATUPLAN = 'S' ))  
                  AND SM.CDTURNO IS NULL 
                  AND (INSTR(P_SERVMINEFETMES, MI.CDSERVFILI ) = 0) 
               GROUP BY MI.CDFILIAL, NULL, MI.CDSERVFILI, MI.QTMINEFET, MI.DSMINEFET,  
                        SM.CDPRODUTO, UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), 
                        UTIL_APURAC.CALCVALRSERV (P_CDFILIAL,V_CDCLIENTE,SM.CDSERVFILI,UTIL_APURAC.CalculaDtBase(MI.NRDIABASE,P_DATAINI,P_DATAFIM), NULL)) T; 
   END;     
    
   FUNCTION CalcValrServ (P_CDFILIAL IN FILIAL.CDFILIAL%TYPE, 
                          P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE, 
                          P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                          P_DTFATURAM IN EFECLIFAT.DTFATURAM%TYPE, 
                          P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE,
                          P_DTREF      IN DATE DEFAULT NULL)
            RETURN NUMBER AS 
      VrServico      SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoFer   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoDom   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSeg   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoTer   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoQua   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoQui   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSex   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSab   SERVCLIE.VRSERVFATU%TYPE; 
      VrCount        NUMBER(3); 
      V_DTREF DATE;
   BEGIN 
      V_DTREF := NVL(P_DTREF, P_DTFATURAM);
      BEGIN
 
         SELECT MAX(VRSERVFATHIS) VRSERVFATHIS, MAX(VRFATUFERHIS) VRFATUFERHIS, MAX(VRFATUDOMHIS), 
                MAX(VRFATUSEGHIS) VRFATUSEGHIS, MAX(VRFATUTERHIS) VRFATUTERHIS, 
                MAX(VRFATUQUAHIS) VRFATUQUAHIS, MAX(VRFATUQUIHIS) VRFATUQUIHIS, 
                MAX(VRFATUSEXHIS) VRFATUSEXHIS, MAX(VRFATUSABHIS) VRFATUSABHIS 
         INTO VrServico, VrServicoFer, VrServicoDom, VrServicoSeg, VrServicoTer, 
              VrServicoQua, VrServicoQui, VrServicoSex, VrServicoSab 
         FROM HISTSERVFATU 
         WHERE CDFILIAL     = P_CDFILIAL   AND 
               CDCLIENTE    = P_CDCLIENTE  AND 
               CDSERVFILI   = P_CDSERVFILI AND 
               DTALTEFATHIS = (SELECT MAX(DTALTEFATHIS) 
                               FROM HISTSERVFATU H, (SELECT MAX(DTSRVFATUHIS) AS DTSRVFATUHIS 
                                                     FROM HISTSERVFATU 
                                                     WHERE CDFILIAL = P_CDFILIAL    
                                                       AND CDCLIENTE = P_CDCLIENTE   
                                                       AND CDSERVFILI = P_CDSERVFILI  
                                                       AND DTSRVFATUHIS <= V_DTREF) X 
                               WHERE H.CDFILIAL     = P_CDFILIAL    
                                 AND H.CDCLIENTE    = P_CDCLIENTE   
                                 AND H.CDSERVFILI   = P_CDSERVFILI  
                                 AND H.DTSRVFATUHIS = X.DTSRVFATUHIS) AND 
               DTSRVFATUHIS = (SELECT MAX(H.DTSRVFATUHIS) 
                               FROM HISTSERVFATU H, (SELECT MAX(DTSRVFATUHIS) AS DTSRVFATUHIS 
                                                     FROM HISTSERVFATU 
                                                     WHERE CDFILIAL = P_CDFILIAL    
                                                       AND CDCLIENTE = P_CDCLIENTE   
                                                       AND CDSERVFILI = P_CDSERVFILI  
                                                       AND DTSRVFATUHIS <= V_DTREF) X 
                               WHERE H.CDFILIAL     = P_CDFILIAL    
                                 AND H.CDCLIENTE    = P_CDCLIENTE   
                                 AND H.CDSERVFILI   = P_CDSERVFILI  
                                 AND H.DTSRVFATUHIS = X.DTSRVFATUHIS); 
      EXCEPTION 
         WHEN NO_DATA_FOUND THEN 
            NULL; 
      END; 
      IF (VrServico IS NULL)  THEN 
         BEGIN 
            SELECT MAX(VRSERVFATU) VRSERVFATU, MAX(VRSERVFATFER) VRSERVFATFER, 
                   MAX(VRSERVFATDOM) VRSERVFATDOM, 
                   MAX(VRSERVFATSEG) VRSERVFATSEG, MAX(VRSERVFATTER) VRSERVFATTER, 
                   MAX(VRSERVFATQUA) VRSERVFATQUA, MAX(VRSERVFATQUI) VRSERVFATQUI, 
                   MAX(VRSERVFATSEX) VRSERVFATSEX, MAX(VRSERVFATSAB) VRSERVFATSAB 
            INTO VrServico, VrServicoFer, VrServicoDom, VrServicoSeg, VrServicoTer, 
                 VrServicoQua, VrServicoQui, VrServicoSex, VrServicoSab 
            FROM SERVCLIE 
            WHERE CDFILIAL   = P_CDFILIAL   AND 
                  CDCLIENTE  = P_CDCLIENTE  AND 
                  CDSERVFILI = P_CDSERVFILI; 
         EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
               NULL; 
         END; 
 
         IF (VrServico IS NULL) THEN 
            VrServico := 0; 
         ELSE                               
            SELECT COUNT(*) 
            INTO VrCount 
            FROM FERIFILI 
            WHERE CDFILIAL = P_CDFILIAL AND 
                  DTFERIFILI = P_DTFATURAM; 
 
            IF (VrCount = 0) THEN
               SELECT COUNT(1)
                  INTO VrCount
               FROM FERICARD
               WHERE CDFILIAL = P_CDFILIAL AND
                     DTFERICARD = P_DTFATURAM;
            END IF;
 
 
            IF (VrCount > 0) THEN 
               IF (VrServicoFer IS NOT NULL) AND (VrServicoFer <> 0) THEN 
                  VrServico := VrServicoFer; 
               END IF; 
            ELSE 
               IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
                  (VrServicoDom IS NOT NULL) AND 
                  (VrServicoDom <> 0) THEN 
                  VrServico := VrServicoDom; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
                  (VrServicoSeg IS NOT NULL) AND 
                  (VrServicoSeg <> 0) THEN 
                  VrServico := VrServicoSeg; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
                  (VrServicoTer IS NOT NULL) AND 
                  (VrServicoTer <> 0) THEN 
                  VrServico := VrServicoTer; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
                  (VrServicoQua IS NOT NULL) AND 
                  (VrServicoQua <> 0) THEN 
                  VrServico := VrServicoQua; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
                  (VrServicoQui IS NOT NULL) AND 
                  (VrServicoQui <> 0) THEN 
                  VrServico := VrServicoQui; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
                  (VrServicoSex IS NOT NULL) AND 
                  (VrServicoSex <> 0) THEN 
                  VrServico := VrServicoSex; 
               END IF; 
               IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
                  (VrServicoSab IS NOT NULL) AND 
                  (VrServicoSab <> 0) THEN 
                  VrServico := VrServicoSab; 
               END IF; 
            END IF; 
         END IF; 
      ELSE 
         SELECT COUNT(*) 
         INTO VrCount 
         FROM FERIFILI 
         WHERE CDFILIAL = P_CDFILIAL AND 
               DTFERIFILI = P_DTFATURAM; 
 
          IF (VrCount = 0) THEN
             SELECT COUNT(1)
                INTO VrCount
             FROM FERICARD
             WHERE CDFILIAL = P_CDFILIAL AND
                   DTFERICARD = P_DTFATURAM;
          END IF;
 
 
         IF (VrCount > 0) THEN 
            IF (VrServicoFer IS NOT NULL) AND (VrServicoFer <> 0) THEN 
               VrServico := VrServicoFer; 
            END IF; 
         ELSE 
            IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
               (VrServicoDom IS NOT NULL) AND 
               (VrServicoDom <> 0) THEN 
               VrServico := VrServicoDom; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
               (VrServicoSeg IS NOT NULL) AND 
               (VrServicoSeg <> 0) THEN 
               VrServico := VrServicoSeg; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
               (VrServicoTer IS NOT NULL) AND 
               (VrServicoTer <> 0) THEN 
               VrServico := VrServicoTer; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
               (VrServicoQua IS NOT NULL) AND 
               (VrServicoQua <> 0) THEN 
               VrServico := VrServicoQua; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
               (VrServicoQui IS NOT NULL) AND 
               (VrServicoQui <> 0) THEN 
               VrServico := VrServicoQui; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
               (VrServicoSex IS NOT NULL) AND 
               (VrServicoSex <> 0) THEN 
               VrServico := VrServicoSex; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
               (VrServicoSab IS NOT NULL) AND 
               (VrServicoSab <> 0) THEN 
               VrServico := VrServicoSab; 
            END IF; 
         END IF; 
      END IF; 
      RETURN(VrServico); 
   END; 
 
   FUNCTION CalcValrServTur (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                             P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                             P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                             P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE, 
                             P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE,
                             P_DTREF      IN DATE DEFAULT NULL) 
             RETURN NUMBER AS 
 
      VrServico      SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoFer   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoDom   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSeg   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoTer   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoQua   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoQui   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSex   SERVCLIE.VRSERVFATU%TYPE; 
      VrServicoSab   SERVCLIE.VRSERVFATU%TYPE; 
      VrCount        NUMBER(3);
      V_DTREF DATE; 
 
   BEGIN 
      V_DTREF := NVL(P_DTREF, P_DTFATURAM);
      BEGIN 
         SELECT MAX(S.VRSERVFATTUR) VRSERVFATTUR, MAX(S.VRSVFATTUFER) VRSVFATTUFER, 
                MAX(S.VRSVFATTUDOM) VRSVFATTUDOM, 
                MAX(S.VRSVFATTUSEG) VRSVFATTUSEG, MAX(S.VRSVFATTUTER) VRSVFATTUTER, 
                MAX(S.VRSVFATTUQUA) VRSVFATTUQUA, MAX(S.VRSVFATTUQUI) VRSVFATTUQUI, 
                MAX(S.VRSVFATTUSEX) VRSVFATTUSEX, MAX(S.VRSVFATTUSAB) VRSVFATTUSAB 
         INTO VrServico, VrServicoFer, VrServicoDom, VrServicoSeg, VrServicoTer, 
              VrServicoQua, VrServicoQui, VrServicoSex, VrServicoSab 
         FROM SERVCLIETURN S 
               
              INNER JOIN (SELECT CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO,  
                                 MAX(DTSERVCLIETURN) AS DTSERVCLIETURN 
                           FROM SERVCLIETURN 
                           WHERE DTSERVCLIETURN <= V_DTREF AND 
                                 CDFILIAL   = P_CDFILIAL   AND 
                                 CDCLIENTE  = P_CDCLIENTE  AND 
                                 CDSERVFILI = P_CDSERVFILI AND 
                                 CDTURNO    = P_CDTURNO 
                          GROUP BY CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO) MV 
                 ON MV.CDFILIAL = S.CDFILIAL AND 
                    MV.CDCLIENTE = S.CDCLIENTE AND 
                    MV.CDSERVFILI = S.CDSERVFILI AND 
                    MV.CDTURNO = S.CDTURNO AND 
                    MV.DTSERVCLIETURN = S.DTSERVCLIETURN 
               
              INNER JOIN (SELECT CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO,  
                                 DTSERVCLIETURN, MAX(DTALTSERVCLIETURN) AS DTALTSERVCLIETURN 
                           FROM SERVCLIETURN 
                           WHERE DTSERVCLIETURN <= V_DTREF AND 
                                 CDFILIAL   = P_CDFILIAL   AND 
                                 CDCLIENTE  = P_CDCLIENTE  AND 
                                 CDSERVFILI = P_CDSERVFILI AND 
                                 CDTURNO    = P_CDTURNO 
                          GROUP BY CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO, DTSERVCLIETURN) MA 
                 ON MA.CDFILIAL = S.CDFILIAL AND 
                    MA.CDCLIENTE = S.CDCLIENTE AND 
                    MA.CDSERVFILI = S.CDSERVFILI AND 
                    MA.CDTURNO = S.CDTURNO AND 
                    MA.DTSERVCLIETURN = S.DTSERVCLIETURN AND 
                    MA.DTALTSERVCLIETURN = S.DTALTSERVCLIETURN 
         WHERE S.CDFILIAL   = P_CDFILIAL   AND 
               S.CDCLIENTE  = P_CDCLIENTE  AND 
               S.CDSERVFILI = P_CDSERVFILI AND 
               S.CDTURNO    = P_CDTURNO; 
      EXCEPTION 
         WHEN NO_DATA_FOUND THEN 
            NULL; 
      END; 
 
      SELECT COUNT(*) 
      INTO VrCount 
      FROM FERIFILI 
      WHERE CDFILIAL = P_CDFILIAL AND 
            DTFERIFILI = P_DTFATURAM; 
 
        IF (VrCount = 0) THEN
           SELECT COUNT(1)
              INTO VrCount
           FROM FERICARD
           WHERE CDFILIAL = P_CDFILIAL AND
                 DTFERICARD = P_DTFATURAM;
        END IF;
 
 
      IF (VrCount > 0) THEN 
         IF (VrServicoFer IS NOT NULL) AND (VrServicoFer <> 0) THEN 
            VrServico := VrServicoFer; 
         END IF; 
      ELSE 
         IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
            (VrServicoDom IS NOT NULL) AND 
            (VrServicoDom <> 0) THEN 
            VrServico := VrServicoDom; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
            (VrServicoSeg IS NOT NULL) AND 
            (VrServicoSeg <> 0) THEN 
            VrServico := VrServicoSeg; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
            (VrServicoTer IS NOT NULL) AND 
            (VrServicoTer <> 0) THEN 
            VrServico := VrServicoTer; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
            (VrServicoQua IS NOT NULL) AND 
            (VrServicoQua <> 0) THEN 
            VrServico := VrServicoQua; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
            (VrServicoQui IS NOT NULL) AND 
            (VrServicoQui <> 0) THEN 
            VrServico := VrServicoQui; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
            (VrServicoSex IS NOT NULL) AND 
            (VrServicoSex <> 0) THEN 
            VrServico := VrServicoSex; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
            (VrServicoSab IS NOT NULL) AND 
            (VrServicoSab <> 0) THEN 
            VrServico := VrServicoSab; 
         END IF; 
      END IF; 
 
      IF (VrServico IS NULL) THEN 
         VrServico := 0; 
      END IF; 
       
      IF (VrServico = 0) THEN 
         VrServico := UTIL_APURAC.CalcValrServ(P_CDFILIAL, P_CDCLIENTE, P_CDSERVFILI, P_DTFATURAM, P_CDTURNO, V_DTREF);
      END IF; 
      RETURN(VrServico); 
   END; 
 
   FUNCTION CalcEfetMini (P_CDFILIAL IN FILIAL.CDFILIAL%TYPE, 
                          P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE, 
                          P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                          P_CDGRSERVFAT IN SERVCLIE.CDGRSERVFAT%TYPE, 
                          P_DTFATURAM IN EFECLIFAT.DTFATURAM%TYPE) 
            RETURN NUMBER AS 
      QtEfetMini      SERVCLIE.QTEFETMINI%TYPE; 
      QtEfetMiniFer   SERVCLIE.QTEFETMINFER%TYPE; 
      QtEfetMiniDom   SERVCLIE.QTEFETMINDOM%TYPE; 
      QtEfetMiniSeg   SERVCLIE.QTEFETMINSEG%TYPE; 
      QtEfetMiniTer   SERVCLIE.QTEFETMINTER%TYPE; 
      QtEfetMiniQua   SERVCLIE.QTEFETMINQUA%TYPE; 
      QtEfetMiniQui   SERVCLIE.QTEFETMINQUI%TYPE; 
      QtEfetMiniSex   SERVCLIE.QTEFETMINSEX%TYPE; 
      QtEfetMiniSab   SERVCLIE.QTEFETMINSAB%TYPE; 
      VrCount         NUMBER(3); 
   BEGIN 
      IF (P_CDGRSERVFAT IS NULL) THEN 
         Begin 
            SELECT QTEFETMINI, QTEFETMINFER, QTEFETMINDOM, QTEFETMINSEG, QTEFETMINTER, 
                   QTEFETMINQUA, QTEFETMINQUI, QTEFETMINSEX, QTEFETMINSAB 
            INTO QtEfetMini, QtEfetMiniFer, QtEfetMiniDom, QtEfetMiniSeg, QtEfetMiniTer, 
                 QtEfetMiniQua, QtEfetMiniQui, QtEfetMiniSex, QtEfetMiniSab 
            FROM SERVCLIE 
            WHERE CDFILIAL = P_CDFILIAL AND 
                  CDCLIENTE = P_CDCLIENTE AND 
                  CDSERVFILI = P_CDSERVFILI; 
         Exception 
           When No_Data_Found Then 
              QtEfetMini := NULL; QtEfetMiniFer := NULL; QtEfetMiniDom := NULL; QtEfetMiniSeg := NULL; 
              QtEfetMiniTer := NULL; QtEfetMiniQua := NULL; QtEfetMiniQui := NULL; QtEfetMiniSex := NULL; 
              QtEfetMiniSab := NULL; 
         End;   
      ELSE 
         SELECT QTEFETMINIGSF, QTEFMINFERGSF, QTEFMINDOMGSF, QTEFMINSEGGSF, 
                QTEFMINTERGSF, QTEFMINQUAGSF, QTEFMINQUIGSF, QTEFMINSEXGSF, 
                QTEFMINSABGSF 
         INTO QtEfetMini, QtEfetMiniFer, QtEfetMiniDom, QtEfetMiniSeg, 
              QtEfetMiniTer, QtEfetMiniQua, QtEfetMiniQui, QtEfetMiniSex, 
              QtEfetMiniSab 
         FROM GRUPSERVFAT 
         WHERE CDFILIAL = P_CDFILIAL AND 
               CDCLIENTE = P_CDCLIENTE AND 
               CDGRSERVFAT = P_CDGRSERVFAT; 
      END IF; 
 
      IF (QtEfetMini IS NULL) THEN 
         QtEfetMini := 0; 
      ELSE 
         SELECT COUNT(*) 
         INTO VrCount 
         FROM FERIFILI 
         WHERE CDFILIAL = P_CDFILIAL AND 
               DTFERIFILI = P_DTFATURAM; 
 
          IF (VrCount = 0) THEN
             SELECT COUNT(1)
                INTO VrCount
             FROM FERICARD
             WHERE CDFILIAL = P_CDFILIAL AND
                   DTFERICARD = P_DTFATURAM;
          END IF;
 
 
         IF (VrCount > 0) THEN 
            IF (QtEfetMiniFer IS NOT NULL) AND (QtEfetMiniFer <> 0) THEN 
               QtEfetMini := QtEfetMiniFer; 
            END IF; 
         ELSE 
            IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
               (QtEfetMiniDom IS NOT NULL) AND 
               (QtEfetMiniDom <> 0) THEN 
               QtEfetMini := QtEfetMiniDom; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
               (QtEfetMiniSeg IS NOT NULL) AND 
               (QtEfetMiniSeg <> 0) THEN 
               QtEfetMini := QtEfetMiniSeg; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
               (QtEfetMiniTer IS NOT NULL) AND 
               (QtEfetMiniTer <> 0) THEN 
               QtEfetMini := QtEfetMiniTer; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
               (QtEfetMiniQua IS NOT NULL) AND 
               (QtEfetMiniQua <> 0) THEN 
               QtEfetMini := QtEfetMiniQua; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
               (QtEfetMiniQui IS NOT NULL) AND 
               (QtEfetMiniQui <> 0) THEN 
               QtEfetMini := QtEfetMiniQui; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
               (QtEfetMiniSex IS NOT NULL) AND 
               (QtEfetMiniSex <> 0) THEN 
               QtEfetMini := QtEfetMiniSex; 
            END IF; 
            IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
               (QtEfetMiniSab IS NOT NULL) AND 
               (QtEfetMiniSab <> 0) THEN 
               QtEfetMini := QtEfetMiniSab; 
            END IF; 
         END IF; 
      END IF; 
 
      RETURN(QtEfetMini); 
   END; 
 
   PROCEDURE PrecoCorr (P_CDFILIAL IN FILIAL.CDFILIAL%TYPE, 
                        P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE, 
                        P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                        P_CDGRSERVFAT IN SERVCLIE.CDGRSERVFAT%TYPE, 
                        P_QTEFETIVOS IN NUMBER, 
                        P_QTEFETMINI IN NUMBER, 
                        P_IDSERVGRUP IN VARCHAR2, 
                        P_VRSERVICO IN OUT NUMBER, 
                        P_VRPERCCORR IN OUT NUMBER, 
                        P_IDCONSTABCORR IN VARCHAR2 DEFAULT 'N',
                        P_DATAINI IN DATE DEFAULT NULL,
                        P_DATAFIM IN DATE DEFAULT NULL,
                        P_UTEFEPLA IN VARCHAR DEFAULT 'N') AS 
 
      V_PERC NUMBER; 
      V_IDATIVATABCOR VARCHAR2(1); 
      V_QTEFETVOL NUMBER; 
      V_PRINCIPAL VARCHAR2(1);
      V_EFETIVOS_TER NUMBER;
   BEGIN 
      /*VERIFICA SE O CLIENTE E PRINCIPAL PARA TRAZER OS EFETIVOS DOS TERCEIROS, CASO O SERVICO CONSIDERE*/
      SELECT NVL(IDCLIENPRIN,'N')
        INTO V_PRINCIPAL
        FROM CLIENFILIAL
       WHERE CDFILIAL = P_CDFILIAL AND
             CDCLIENTE = P_CDCLIENTE;
             
      V_EFETIVOS_TER := 0;
      IF (V_PRINCIPAL = 'S') THEN
         /*RETORNA OS EFETIVOS DOS SERVICOS DOS CLIENTES TERCEIROS QUE NO CLIENTE PRINCIPAL CONSIDERAM TERCEIROS - POR SERVICO OU GRUPO DE SERVICO*/
         BEGIN 
            SELECT NVL(SUM(SM.QTEFETIVOS),0) 
              INTO V_EFETIVOS_TER
              FROM SUM_EFETIVOS SM INNER JOIN (SELECT CDFILIAL, CDSERVFILI 
                                                 FROM SERVCLIE SC
                                                 WHERE CDCLIENTE = P_CDCLIENTE AND
                                                       CDFILIAL = P_CDFILIAL AND
                                                       IDCONSTERCTABCOR = 'S') SV ON (SM.CDSERVFILI = SV.CDSERVFILI AND 
                                                                                      SM.CDFILIAL = SV.CDFILIAL)
             WHERE DTFATURAM BETWEEN P_DATAINI AND P_DATAFIM   AND
                   ((SM.IDFATUPLAN = 'F') OR (P_UTEFEPLA = 'S'))  AND
                   (SM.CDCLIENTE <> P_CDCLIENTE) AND
                   (SM.CDFILIAL   = P_CDFILIAL) AND
                   (((SM.CDGRSERVFAT IS NULL) AND (P_IDSERVGRUP <> 'G')) OR ((P_IDSERVGRUP = 'G') AND (SM.CDGRSERVFAT = P_CDGRSERVFAT)));
         EXCEPTION 
            WHEN NO_DATA_FOUND THEN
               V_EFETIVOS_TER := 0;
         END;
      END IF;
      IF V_EFETIVOS_TER IS NULL THEN
         V_EFETIVOS_TER := 0;
      END IF;
      SELECT NVL(MAX(QTVOLPROSERVCLI), 0) INTO V_QTEFETVOL 
        FROM SERVCLIE 
       WHERE CDFILIAL = P_CDFILIAL 
         AND CDSERVFILI = P_CDSERVFILI 
         AND CDCLIENTE = P_CDCLIENTE; 
 
      IF V_QTEFETVOL = 0 THEN 
         V_QTEFETVOL := P_QTEFETMINI; 
      END IF; 
 
      IF (V_QTEFETVOL <> 0) THEN 
         V_PERC := (((P_QTEFETIVOS + V_EFETIVOS_TER)/ V_QTEFETVOL) * 100); 
      END IF; 
 
      P_VRPERCCORR := 1; 
 
      IF (P_VRSERVICO IS NULL) THEN 
         P_VRSERVICO := 0; 
      END IF; 
 
      IF (P_IDSERVGRUP <> 'G') THEN 
          
          
         SELECT NVL(MAX(IDATIVATABCOR), 'N') 
           INTO V_IDATIVATABCOR 
           FROM SERVCLIE    
          WHERE CDFILIAL = P_CDFILIAL 
            AND CDCLIENTE = P_CDCLIENTE 
            AND CDSERVFILI = P_CDSERVFILI; 
         IF (V_IDATIVATABCOR = 'S') OR (P_IDCONSTABCORR = 'S') THEN                                      
            SELECT MAX((VRPECORRSERV/100)) 
              INTO P_VRPERCCORR 
              FROM CORRPRECSERV 
             WHERE CDFILIAL = P_CDFILIAL  
               AND CDCLIENTE = P_CDCLIENTE  
               AND CDSERVFILI = P_CDSERVFILI  
               AND V_PERC > VRPEINICSERV 
               AND V_PERC <= VRPEFIMSERV; 
         END IF;    
      ELSE 
          
         SELECT MAX((VRPECORRGRUP/100)) 
           INTO P_VRPERCCORR 
           FROM CORRPRECGRUP 
          WHERE CDFILIAL = P_CDFILIAL  
            AND CDCLIENTE = P_CDCLIENTE  
            AND CDGRSERVFAT = P_CDGRSERVFAT  
            AND V_PERC > VRPEINICGRUP 
            AND V_PERC <= VRPEFIMGRUP; 
      END IF; 
 
      IF (P_VRPERCCORR IS NULL) THEN 
         P_VRPERCCORR := 1; 
      END IF; 
 
      P_VRSERVICO := P_VRSERVICO * P_VRPERCCORR; 
       
   END; 
 
   FUNCTION DsNmServico (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                         P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                         P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                         P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE) 
 
            RETURN VARCHAR AS 
      DsServico      SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoFer   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoDom   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSeg   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoTer   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoQua   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoQui   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSex   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSab   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoExc   SERVCLIE.DSEMISSERCLI%TYPE; 
      VrCount        NUMBER(3); 
   BEGIN 
      BEGIN 
         SELECT NVL(DSEMISSERCLI, NULL), NVL(DSPRODEMISDOM,NULL), NVL(DSPRODEMISSEG,NULL), NVL(DSPRODEMISTER,NULL), 
                NVL(DSPRODEMISQUA,NULL), NVL(DSPRODEMISQUI,NULL), NVL(DSPRODEMISSEX,NULL), NVL(DSPRODEMISSAB,NULL), 
                NVL(DSPRODEMISFER, NULL) 
         INTO DsServico, DsServicoDom, DsServicoSeg, DsServicoTer, DsServicoQua, 
              DsServicoQui, DsServicoSex, DsServicoSab, DsServicoFer 
         FROM SERVCLIE 
         WHERE CDCLIENTE  = P_CDCLIENTE  AND 
               CDFILIAL   = P_CDFILIAL   AND 
               CDSERVFILI = P_CDSERVFILI; 
      EXCEPTION 
         WHEN NO_DATA_FOUND THEN 
            NULL; 
      END; 
 
      SELECT COUNT(*) 
      INTO VrCount 
      FROM FERIFILI 
      WHERE CDFILIAL   = P_CDFILIAL  AND 
            DTFERIFILI = P_DTFATURAM; 
 
      IF (VrCount = 0) THEN
         SELECT COUNT(1)
            INTO VrCount
         FROM FERICARD
         WHERE CDFILIAL = P_CDFILIAL AND
               DTFERICARD = P_DTFATURAM;
      END IF;
 
 
      IF (VrCount > 0) THEN 
         IF (DsServicoFer IS NOT NULL) THEN 
            DsServicoExc := DsServicoFer; 
         END IF; 
      ELSE 
         IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
            (DsServicoDom IS NOT NULL) THEN 
            DsServicoExc := DsServicoDom; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
            (DsServicoSeg IS NOT NULL) THEN 
            DsServicoExc := DsServicoSeg; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
            (DsServicoTer IS NOT NULL) THEN 
            DsServicoExc := DsServicoTer; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
            (DsServicoQua IS NOT NULL) THEN 
            DsServicoExc := DsServicoQua; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
            (DsServicoQui IS NOT NULL) THEN 
            DsServicoExc := DsServicoQui; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
            (DsServicoSex IS NOT NULL) THEN 
            DsServicoExc := DsServicoSex; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
            (DsServicoSab IS NOT NULL) THEN 
            DsServicoExc := DsServicoSab; 
         END IF; 
      END IF; 
 
      IF (dsServicoExc IS NULL) THEN 
         DsServicoExc := DsServico; 
      END IF; 
      RETURN(DsServicoExc); 
   END; 
 
   FUNCTION DsNmServicoTur (P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE, 
                            P_CDCLIENTE  IN CLIENTE.CDCLIENTE%TYPE, 
                            P_CDSERVFILI IN EFECLIFAT.CDSERVFILI%TYPE, 
                            P_DTFATURAM  IN EFECLIFAT.DTFATURAM%TYPE, 
                            P_CDTURNO    IN EFECLIFATTUR.CDTURNO%TYPE) 
 
            RETURN VARCHAR AS 
 
      DsServico      SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoFer   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoDom   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSeg   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoTer   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoQua   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoQui   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSex   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoSab   SERVCLIE.DSEMISSERCLI%TYPE; 
      DsServicoExc   SERVCLIE.DSEMISSERCLI%TYPE; 
      VrCount        NUMBER(3); 
   BEGIN 
 
     BEGIN 
        SELECT NVL(S.DSEMISTURCLI, NULL), NVL(S.DSPREMISTUDOM, NULL), NVL(S.DSPREMISTUSEG,NULL), NVL(S.DSPREMISTUTER, NULL), 
               NVL(S.DSPREMISTUQUA, NULL), NVL(S.DSPREMISTUQUI,NULL), NVL(S.DSPREMISTUSEX,NULL), NVL(S.DSPREMISTUSAB,NULL), 
               NVL(S.DSPREMISTUFER, NULL) 
         INTO DsServico, DsServicoDom, DsServicoSeg, DsServicoTer, DsServicoQua, 
              DsServicoQui, DsServicoSex, DsServicoSab, DsServicoFer 
         FROM SERVCLIETURN S 
               
              INNER JOIN (SELECT CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO,  
                                 MAX(DTSERVCLIETURN) AS DTSERVCLIETURN 
                           FROM SERVCLIETURN 
                           WHERE DTSERVCLIETURN <= P_DTFATURAM AND 
                                 CDFILIAL   = P_CDFILIAL   AND 
                                 CDCLIENTE  = P_CDCLIENTE  AND 
                                 CDSERVFILI = P_CDSERVFILI AND 
                                 CDTURNO    = P_CDTURNO 
                          GROUP BY CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO) MV 
                 ON MV.CDFILIAL = S.CDFILIAL AND 
                    MV.CDCLIENTE = S.CDCLIENTE AND 
                    MV.CDSERVFILI = S.CDSERVFILI AND 
                    MV.CDTURNO = S.CDTURNO AND 
                    MV.DTSERVCLIETURN = S.DTSERVCLIETURN 
               
              INNER JOIN (SELECT CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO,  
                                 DTSERVCLIETURN, MAX(DTALTSERVCLIETURN) AS DTALTSERVCLIETURN 
                           FROM SERVCLIETURN 
                           WHERE DTSERVCLIETURN <= P_DTFATURAM AND 
                                 CDFILIAL   = P_CDFILIAL   AND 
                                 CDCLIENTE  = P_CDCLIENTE  AND 
                                 CDSERVFILI = P_CDSERVFILI AND 
                                 CDTURNO    = P_CDTURNO 
                          GROUP BY CDFILIAL, CDCLIENTE, CDSERVFILI, CDTURNO, DTSERVCLIETURN) MA 
                 ON MA.CDFILIAL = S.CDFILIAL AND 
                    MA.CDCLIENTE = S.CDCLIENTE AND 
                    MA.CDSERVFILI = S.CDSERVFILI AND 
                    MA.CDTURNO = S.CDTURNO AND 
                    MA.DTSERVCLIETURN = S.DTSERVCLIETURN AND 
                    MA.DTALTSERVCLIETURN = S.DTALTSERVCLIETURN 
         WHERE S.CDCLIENTE  = P_CDCLIENTE  AND 
               S.CDFILIAL   = P_CDFILIAL   AND 
               S.CDSERVFILI = P_CDSERVFILI AND 
               S.CDTURNO    = P_CDTURNO; 
      EXCEPTION 
         WHEN NO_DATA_FOUND THEN 
            NULL; 
      END; 
 
      SELECT COUNT(*) 
      INTO VrCount 
      FROM FERIFILI 
      WHERE CDFILIAL   = P_CDFILIAL  AND 
            DTFERIFILI = P_DTFATURAM; 
 
      IF (VrCount = 0) THEN
         SELECT COUNT(1)
            INTO VrCount
         FROM FERICARD
         WHERE CDFILIAL = P_CDFILIAL AND
               DTFERICARD = P_DTFATURAM;
      END IF;
 
      IF (VrCount > 0) THEN 
         IF (DsServicoFer IS NOT NULL) THEN 
            DsServicoExc := DsServicoFer; 
         END IF; 
      ELSE 
         IF (TO_CHAR(P_DTFATURAM,'D') = '1') AND 
            (DsServicoDom IS NOT NULL) THEN 
            DsServicoExc := DsServicoDom; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '2') AND 
            (DsServicoSeg IS NOT NULL) THEN 
            DsServicoExc := DsServicoSeg; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '3') AND 
            (DsServicoTer IS NOT NULL) THEN 
            DsServicoExc := DsServicoTer; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '4') AND 
            (DsServicoQua IS NOT NULL) THEN 
            DsServicoExc := DsServicoQua; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '5') AND 
            (DsServicoQui IS NOT NULL) THEN 
            DsServicoExc := DsServicoQui; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '6') AND 
            (DsServicoSex IS NOT NULL) THEN 
            DsServicoExc := DsServicoSex; 
         END IF; 
         IF (TO_CHAR(P_DTFATURAM,'D') = '7') AND 
            (DsServicoSab IS NOT NULL) THEN 
            DsServicoExc := DsServicoSab; 
         END IF; 
      END IF; 
      IF (dsServicoExc IS NULL) THEN 
         DsServicoExc := DsServico; 
         IF (dsServicoExc IS NULL) THEN 
            DsServicoExc := UTIL_APURAC.DsNmServico(P_CDFILIAL, P_CDCLIENTE, P_CDSERVFILI, P_DTFATURAM); 
            IF (dsServicoExc IS NULL) THEN 
               SELECT NMSERVICO 
               INTO dsServicoExc 
               FROM SERVICO 
               WHERE CDFILIAL   = P_CDFILIAL AND 
                     CDSERVFILI = P_CDSERVFILI; 
            END IF; 
         END IF; 
      END IF;    
      RETURN(dsServicoExc); 
   END; 
 
END UTIL_APURAC;
/