CREATE OR REPLACE PROCEDURE INICIALIZA_SALDO_CONSUMIDOR
   (P_CDFILIAL  IN FILIAL.CDFILIAL%TYPE,
    P_CDCLIENTE IN CLIENTE.CDCLIENTE%TYPE,
    P_DATA      IN VENDA.DTVENDA%TYPE,
    P_CDOPERADOR  IN OPERADOR.CDOPERADOR%TYPE) AS

   /* DECLARACAO DO TIPO (RECORD) QUE CONTERA OS DADOS DO CURSOR */
   TYPE CONSRECTYPE IS RECORD (
      CDCLIENTE      CONSUMIDOR.CDCLIENTE%TYPE,
      CDCONSUMIDOR   CONSUMIDOR.CDCONSUMIDOR%TYPE,
      VRMESUBCONS    CONSUMIDOR.VRMESUBCONS%TYPE,
      IDZERSALDCON   CLIENTE.IDZERSALDCON%TYPE
   );

   /* DECLARACAO DO TIPO DO CURSOR */
   TYPE CONSCURTYPE IS REF CURSOR RETURN CONSRECTYPE;

   /* VARIAVEIS QUE CONTERAO O CURSOR E O REGISTRO */
   REC_CONS    CONSRECTYPE;
   CUR_CONS    CONSCURTYPE;

   V_VRRESTOSALDO  NUMBER;
   V_VRSALDOPESS   NUMBER;
   V_SEQUENCIAL    VARCHAR2(20);
BEGIN
   COMMIT;

   IF (P_CDCLIENTE = 'T') THEN
      OPEN CUR_CONS FOR
         SELECT C.CDCLIENTE, C.CDCONSUMIDOR, NVL(C.VRMESUBCONS,0), CL.IDZERSALDCON
         FROM CONSUMIDOR C, CLIENFILIAL CF, CLIENTE CL
         WHERE CF.CDFILIAL = P_CDFILIAL
           AND CL.CDCLIENTE = CF.CDCLIENTE
           AND C.CDCLIENTE = CF.CDCLIENTE
           AND (((C.IDSITCONSUMI = '1') AND (CF.IDAJUSALDCONINA = 'N') AND (CF.IDAJUSALDCONMEN = 'S')) OR ((CF.IDAJUSALDCONINA = 'S') AND (CF.IDAJUSALDCONMEN = 'S')));
   ELSE
      OPEN CUR_CONS FOR
         SELECT C.CDCLIENTE, C.CDCONSUMIDOR, NVL(C.VRMESUBCONS,0), CL.IDZERSALDCON
         FROM CONSUMIDOR C, CLIENFILIAL CF, CLIENTE CL
         WHERE CF.CDFILIAL  = P_CDFILIAL
           AND (INSTR(P_CDCLIENTE,'_'||CF.CDCLIENTE||'_') <> 0)
           AND CL.CDCLIENTE = CF.CDCLIENTE
           AND C.CDCLIENTE  = CF.CDCLIENTE
           AND (((C.IDSITCONSUMI = '1') AND (CF.IDAJUSALDCONINA = 'N') AND (CF.IDAJUSALDCONMEN = 'S')) OR ((CF.IDAJUSALDCONINA = 'S') AND (CF.IDAJUSALDCONMEN = 'S')));
   END IF;

   LOOP
      FETCH CUR_CONS INTO REC_CONS;
      EXIT WHEN CUR_CONS%NOTFOUND;

      SELECT NVL(MAX(VRSALDOCONS),0), NVL(MAX(VRSALDPECONS),0)
      INTO V_VRRESTOSALDO, V_VRSALDOPESS
      FROM MOVICLIE
      WHERE CDCLIENTE    = REC_CONS.CDCLIENTE
        AND CDCONSUMIDOR = REC_CONS.CDCONSUMIDOR
        AND DTMOVCLI     = (SELECT MAX(DTMOVCLI)
                            FROM   MOVICLIE
                            WHERE  CDCLIENTE    = REC_CONS.CDCLIENTE
                              AND  CDCONSUMIDOR = REC_CONS.CDCONSUMIDOR
                              AND  DTMOVCLI     < P_DATA);

      IF (V_VRRESTOSALDO <> 0) AND (REC_CONS.IDZERSALDCON = 'S') THEN
         NOVO_CODIGO('MOVCLIE'||REC_CONS.CDCLIENTE, V_SEQUENCIAL);
         V_SEQUENCIAL := SUBSTR(V_SEQUENCIAL,11,10);

         INSERT INTO MOVICLIE
         (CDCLIENTE, NRSEQMOVCLI, CDCONSUMIDOR, VRMOVCLI,
          DTMOVCLI, DSHISTMOV, IDTIPMOCVLI, CDFILIAL, CDCAIXA, NRSEQVENDA, DTABERCAIX,
          NRSEQUMOVI, CDTIPORECE, VRSALDOCONS, VRMOVPESCONS, VRSALDPECONS,DTATUMOVCLIE,CDOPERADOR,DSATUMOVCLIE,
          DTMOVIMCLIE, DTULTATUMCLI)
         VALUES
         (REC_CONS.CDCLIENTE, V_SEQUENCIAL, REC_CONS.CDCONSUMIDOR, V_VRRESTOSALDO,
          P_DATA, 'Ajuste de saldo', 'A', P_CDFILIAL, NULL, NULL, NULL,
          NULL, NULL, 0, 0, V_VRSALDOPESS,P_DATA,P_CDOPERADOR,'Ajuste de Saldo Mensal do Consumidor',
          TRUNC(P_DATA), SYSDATE);

         V_VRRESTOSALDO := 0;
      END IF;

      NOVO_CODIGO('MOVCLIE'||REC_CONS.CDCLIENTE, V_SEQUENCIAL);
      V_SEQUENCIAL := SUBSTR(V_SEQUENCIAL,11,10);

      INSERT INTO MOVICLIE
      (CDCLIENTE, NRSEQMOVCLI, CDCONSUMIDOR, VRMOVCLI,
       DTMOVCLI, DSHISTMOV, IDTIPMOCVLI, CDFILIAL, CDCAIXA, NRSEQVENDA, DTABERCAIX,
       NRSEQUMOVI, CDTIPORECE, VRSALDOCONS, VRMOVPESCONS, VRSALDPECONS,
       DTATUMOVCLIE,CDOPERADOR,DSATUMOVCLIE, DTMOVIMCLIE,
       DTULTATUMCLI)
      VALUES
      (REC_CONS.CDCLIENTE, V_SEQUENCIAL, REC_CONS.CDCONSUMIDOR, REC_CONS.VRMESUBCONS,
       P_DATA+0.000016, 'Credito de subsidio', 'C', P_CDFILIAL, NULL, NULL, NULL,
       NULL, NULL, REC_CONS.VRMESUBCONS + V_VRRESTOSALDO, 0, V_VRSALDOPESS,
       P_DATA+0.000016,P_CDOPERADOR,'Ajuste de Saldo Mensal do Consumidor', TRUNC(P_DATA+0.000016),
       SYSDATE);
   END LOOP;
   CLOSE CUR_CONS;
   COMMIT;
END;
/