CREATE OR REPLACE PROCEDURE USISTEMA.ACERTO
(P_CDG_ORGAO IN NUMBER,
 P_REF_ANOMES IN NUMBER) AS


L_CDG_ORDEM NUMBER;
L_CDG_FUNCIONAL NUMBER;
L_CDG_SITFUNC NUMBER;

L_CDG_PROVDESC NUMBER;
L_VLR_PROVDESC FLOAT;

L_21181 FLOAT;
L_21182 FLOAT;
L_52311 FLOAT;

L_VLR_AUX FLOAT;



CURSOR PEGA_MATRICULAS IS
SELECT
        DISTINCT
        FIN.CDG_ORDEM,
        FIN.CDG_FUNCIONAL,
        FUN.CDG_SITFUNC

  FROM USISTEMA.TBFINANCEIRO FIN,
       USISTEMA.TBCADFUNCIONAL FUN
 WHERE FIN.REF_ANOMES = 201001
   AND FIN.CDG_ORGAO = 303
   AND FIN.ID_AUX = 0
   AND FIN.REF_ANOMES = FUN.REF_ANOMES
   AND FIN.CDG_ORGAO = FUN.CDG_ORGAO
   AND FIN.ID_AUX = FUN.ID_AUX
   AND FIN.CDG_ORDEM = FUN.CDG_ORDEM
   AND FIN.CDG_FUNCIONAL = FUN.CDG_FUNCIONAL
   AND FUN.CDG_SITFUNC IN (4,11);


CURSOR PEGA_PROVENTOS IS
SELECT SUM(VALOR)
  FROM USISTEMA.TBFINANCEIRO
 WHERE REF_ANOMES = 201001
   AND ID_AUX = 0
   AND CDG_PROVDESC IN (21180,21184)
   AND CDG_ORDEM = L_CDG_ORDEM
   AND CDG_FUNCIONAL = L_CDG_FUNCIONAL;

CURSOR PEGA_52310 IS
SELECT SUM(VALOR)
  FROM USISTEMA.TBFINANCEIRO
 WHERE REF_ANOMES = 201001
   AND ID_AUX = 0
   AND CDG_PROVDESC IN (52310)
   AND CDG_ORDEM = L_CDG_ORDEM
   AND CDG_FUNCIONAL = L_CDG_FUNCIONAL;



BEGIN


   OPEN  PEGA_MATRICULAS;
   LOOP

       FETCH PEGA_MATRICULAS INTO L_CDG_ORDEM, L_CDG_FUNCIONAL, L_CDG_SITFUNC;
       EXIT WHEN PEGA_MATRICULAS%NOTFOUND;

       L_21181 := 0;
       L_21182 := 0;
       L_52311 := 0;
       L_VLR_AUX := 0;

        OPEN PEGA_PROVENTOS;
       FETCH PEGA_PROVENTOS INTO L_VLR_AUX;
       CLOSE PEGA_PROVENTOS;

       L_VLR_AUX := NVL(L_VLR_AUX,0);

       IF L_CDG_SITFUNC = 4 THEN

          L_21181 := L_VLR_AUX;

       ELSE

          L_21182 := L_VLR_AUX;

       END IF;

       L_VLR_AUX := 0;

        OPEN PEGA_52310;
       FETCH PEGA_52310 INTO L_VLR_AUX;
       CLOSE PEGA_52310;

       L_VLR_AUX := NVL(L_VLR_AUX,0);

       L_52311 := L_VLR_AUX;

       DELETE FROM TBFINANCEIRO
        WHERE REF_ANOMES = 201001
          AND CDG_PROVDESC IN (21180,21184)
          AND ID_AUX = 0
          AND CDG_ORDEM = L_CDG_ORDEM
          AND CDG_FUNCIONAL = L_CDG_FUNCIONAL;

       DELETE FROM TBFINANCEIRO
        WHERE REF_ANOMES = 201001
          AND CDG_PROVDESC IN (52310)
          AND ID_AUX = 0
          AND CDG_ORDEM = L_CDG_ORDEM
          AND CDG_FUNCIONAL = L_CDG_FUNCIONAL;


       IF L_21181 > 0 THEN

           INSERT INTO
               TBFINANCEIRO
               (REF_ANOMES,
                ID_AUX,
                CDG_ORGAO,
                CDG_ORDEM,
                CDG_FUNCIONAL,
                CDG_PROVDESC,
                FORMULA,
                PRAZO_INI,
                PRAZO_FIM,
                QUANTIDADE,
                VALOR,
                OPERACAO,
                CALCULADO,
                DTA_ATUALIZACAO,
                ODM_ATUALIZACAO,
                OBSERVACAO)
               VALUES
               (P_REF_ANOMES,
                0,
                P_CDG_ORGAO,
                L_CDG_ORDEM,
                L_CDG_FUNCIONAL,
                21181,
                0,
                1,
                1,
                0,
                L_21181,
                2,
                0,
                SYSDATE,
                77777,
                'DIF FINAL-2 2009');

       END IF;

       IF L_21182> 0 THEN

           INSERT INTO
               TBFINANCEIRO
               (REF_ANOMES,
                ID_AUX,
                CDG_ORGAO,
                CDG_ORDEM,
                CDG_FUNCIONAL,
                CDG_PROVDESC,
                FORMULA,
                PRAZO_INI,
                PRAZO_FIM,
                QUANTIDADE,
                VALOR,
                OPERACAO,
                CALCULADO,
                DTA_ATUALIZACAO,
                ODM_ATUALIZACAO,
                OBSERVACAO)
               VALUES
               (P_REF_ANOMES,
                0,
                P_CDG_ORGAO,
                L_CDG_ORDEM,
                L_CDG_FUNCIONAL,
                21182,
                0,
                1,
                1,
                0,
                L_21182,
                2,
                0,
                SYSDATE,
                77777,
                'DIF FINAL-2 2009');

       END IF;

       IF L_52311 > 0 THEN

           INSERT INTO
               TBFINANCEIRO
               (REF_ANOMES,
                ID_AUX,
                CDG_ORGAO,
                CDG_ORDEM,
                CDG_FUNCIONAL,
                CDG_PROVDESC,
                FORMULA,


CREATE OR REPLACE TYPE BODY USISTEMA.TP_ACERTO AS
    MEMBER FUNCTION IS_VALID RETURN BOOLEAN IS
    BEGIN
          IF REF_ANOMES < 200001 OR REF_ANOMES > 209912 THEN
            RETURN FALSE;
        END IF;
        IF CDG_ORDEM <= 0 OR CDG_FUNCIONAL <= 0 THEN
            RETURN FALSE;
        END IF;

        IF VALOR < 0 THEN
            RETURN FALSE;
        END IF;

        RETURN TRUE;
    END;
END;