CREATE OR REPLACE PROCEDURE FCT_GERAR_PARCELAS_APURACAO (
    P_NRSEQCONTRATO   IN VARCHAR2,
    P_NRMEDICAOFATURA IN VARCHAR2,
    P_NRORG           IN NUMBER,
    P_CDOPERADOR      IN VARCHAR2
)
AS

    V_NRFATURACONT    NOVOCODIGO.NRSEQUENCIAL%TYPE;
    V_NRITFATURACONT  NOVOCODIGO.NRSEQUENCIAL%TYPE;
    V_VALIDAAPUR      NUMBER;

BEGIN

   FOR C_APUR IN (SELECT NRMEDICAOFATURA, NRSEQCONTRATO, NRGRUPOFATURA, CDFILIALFATURA, NRPARCFATURA,
                         IDSITUACAO, DTINICOMPET, DTFINCOMPET, DTLIMITEAPU, DTVENCIMENTO, NRDIASVENCIMENTO,
                         QTDIASFATUVENC, VRMEDICAO, IDATIVO, NRORG
                    FROM FCT_MEDICAOFATURA
                   WHERE ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(FCT_MEDICAOFATURA.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                     AND ((INSTR(P_NRMEDICAOFATURA, '_' || TO_CHAR(FCT_MEDICAOFATURA.NRMEDICAOFATURA) || '_') > 0) OR (P_NRMEDICAOFATURA IS NULL))
                     AND NRORG = P_NRORG
                     AND IDSITUACAO = '3') LOOP

       BEGIN
         SELECT COUNT(*)
           INTO V_VALIDAAPUR
           FROM FCT_FATURACONT
          WHERE NRMEDICAOFATURA = C_APUR.NRMEDICAOFATURA
            AND NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
            AND NRORG = P_NRORG;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
           V_VALIDAAPUR := 0;
     END;

     IF (V_VALIDAAPUR = 0) THEN

        NOVO_CODIGO_SC('FCT_FATURACONT', V_NRFATURACONT, 1, P_NRORG);

        INSERT INTO  FCT_FATURACONT (NRFATURACONT, NRSEQCONTRATO, NRGRUPOFATURA, CDFILIALFATURA, NRPARCFATURA, IDTPFATURA, IDSITUACAO,
                      DTINICOMPET, DTFINCOMPET, DTLIMITEAPU, DTVENCIMENTO, NRDIASVENCIMENTO, QTDIASFATUVENC,
                      VRVENCIMENTO, VRFATURADO, VRFALTAFATURAR, IDFORMAFATURA, DSJUSTIFICATIVA, NRAPURACAO, NRMEDICAOFATURA,
                      IDATIVO, NRORG,  DTINCLUSAO,  NRORGINCLUSAO,  CDOPERINCLUSAO)
            VALUES (V_NRFATURACONT, C_APUR.NRSEQCONTRATO, C_APUR.NRGRUPOFATURA, C_APUR.CDFILIALFATURA, C_APUR.NRPARCFATURA, 'X', '1',
                    C_APUR.DTINICOMPET, C_APUR.DTFINCOMPET, C_APUR.DTLIMITEAPU, C_APUR.DTVENCIMENTO, C_APUR.NRDIASVENCIMENTO, C_APUR.QTDIASFATUVENC,
                    C_APUR.VRMEDICAO, 0, C_APUR.VRMEDICAO, NULL, NULL, NULL, C_APUR.NRMEDICAOFATURA,
                    'S', P_NRORG, SYSDATE, P_NRORG, P_CDOPERADOR);

         FOR C_ITENS IN (SELECT NRITMEDICAOFATURA, NRMEDICAOFATURA, NRSEQCONTRATO, NRITEMOCONTRATO, CDSERVORCA,
                                CDPRODFATURA, IDSITUACAO, VRTOTITEMCONT, IDATIVO, NRORG
                           FROM FCT_ITMEDICAOFATURA
                          WHERE NRMEDICAOFATURA = C_APUR.NRMEDICAOFATURA
                            AND NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
                            AND NRORG = P_NRORG) LOOP

             NOVO_CODIGO_SC('FCT_ITFATURACONT', V_NRITFATURACONT, 1, P_NRORG);

             INSERT INTO FCT_ITFATURACONT (NRITFATURACONT, NRFATURACONT, NRSEQCONTRATO, NRITEMOCONTRATO, CDPRODFATURA, IDSITUACAO,
                                           VRVENCIMENTO, VRFATURADO, VRFALTAFATURAR, IDFORMAFATURA, DSJUSTIFICATIVA, NRAPURACAO,
                                           IDATIVO, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
                     VALUES (V_NRITFATURACONT, V_NRFATURACONT, C_ITENS.NRSEQCONTRATO, C_ITENS.NRITEMOCONTRATO, C_ITENS.CDPRODFATURA, '1',
                             C_ITENS.VRTOTITEMCONT, 0, C_ITENS.VRTOTITEMCONT, NULL, NULL, NULL,
                             'S', P_NRORG, SYSDATE, P_NRORG, P_CDOPERADOR);

         END LOOP;

         UPDATE FCT_MEDICAOFATURA
            SET IDSITUACAO = '4',
                NRORGULTATU = P_NRORG,
                DTULTATU = SYSDATE,
                CDOPERULTATU = P_CDOPERADOR
          WHERE NRMEDICAOFATURA = C_APUR.NRMEDICAOFATURA
            AND NRORG = P_NRORG;

     END IF;
   END LOOP;
END;