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_AGRUPAMENTO        FCT_AGRUPAMENTO.NRAGRUPAMENTO%TYPE;
    V_NRFATURACONTAGRUP  FCT_FATURACONT.NRFATURACONT%TYPE;
    V_DTINICOMPET        FCT_FATURACONT.DTINICOMPET%TYPE;
    V_DTFINCOMPET        FCT_FATURACONT.DTFINCOMPET%TYPE;
    V_VRVENCIMENTO       FCT_FATURACONT.VRVENCIMENTO%TYPE;
    V_TOTALPARCGERADAS   INTEGER;
    V_TOTALPARCELA       INTEGER;
    V_NRAGRUPCONTFAT     FCT_RELACAGRUPCONTFAT.NRAGRUPCONTFAT%TYPE;
    V_IDPARCAGRUP        FCT_RELACAGRUPCONTFAT.IDPARCAGRUP%TYPE;
    V_NRSEQCONTRATO      FCT_CONTRATO.NRSEQCONTRATO%TYPE;
    V_QTDIASCOMPETATUAL  NUMBER;
    V_QTDIASCOMPET       NUMBER;
    V_PERIODOINCOMP      CHAR(1);
    V_VRITEMMEDICAO      FLOAT(126);

    -- RATEIO
    V_NRRATEIOITEMFAT   FCT_RATEIOITFATURACONT.NRRATEIOITEMFAT%TYPE;
    V_COUNTITENSRATEIO  NUMBER;
    V_IDGERARATEIO      VARCHAR2(1);

    V_GEROU_PARCELA NUMBER;
BEGIN

    FOR C_APUR IN (SELECT NRMEDICAOFATURA, NRSEQCONTRATO, CDFILIALFATURA, NRPARCFATURA,
                       CDTIPOOPERFATURA, CDSERIENF, CDEMPRESA, CDINSCESTA, CDINSCMUNI,
                       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

        V_DTINICOMPET   := C_APUR.DTINICOMPET;
        V_DTFINCOMPET   := C_APUR.DTFINCOMPET;
        V_VRVENCIMENTO  := C_APUR.VRMEDICAO;
        V_IDPARCAGRUP   := 'N';
        V_NRSEQCONTRATO := NULL;
        V_PERIODOINCOMP := 'N';

        SELECT COUNT(*)
          INTO V_GEROU_PARCELA
          FROM FCT_RELACAGRUPCONTFAT
         WHERE NRMEDICAOFATURA = C_APUR.NRMEDICAOFATURA
           AND NRORG           = P_NRORG;

        IF (V_GEROU_PARCELA = 0) THEN

            IF(C_APUR.DTINICOMPET <> ('01/' || TO_CHAR(C_APUR.DTINICOMPET, 'MM/YYYY')) OR C_APUR.DTFINCOMPET <> LAST_DAY(C_APUR.DTFINCOMPET)) THEN
                FOR C_INFOPERIODOINC IN (
                    SELECT (COMPET.NRDIACOMPETFIN - COMPET.NRDIACOMPETINI) + 1 AS QTDIASCOMPET, 
                           COMPET.NRDIACOMPETFIN,
                           COMPET.NRDIACOMPETINI,
                           ESCOPO.NRTIPOORCAMENTO,
                           PARAM.IDTPCALCPARCINC
                     FROM FCT_COMPETENCIANEGOCONT COMPET
                     JOIN FCT_NEGOCIACAOCONTRATO NEGO
                       ON COMPET.NRNEGOCIACAOCONTRATO = NEGO.NRNEGOCIACAOCONTRATO
                      AND COMPET.NRORG                = NEGO.NRORG
                     JOIN FCT_TIPOORCAMENTO ESCOPO
                       ON NEGO.NRTIPOORCAMENTO = ESCOPO.NRTIPOORCAMENTO
                      AND NEGO.NRORG           = ESCOPO.NRORG
                     JOIN FCT_CAPAPARAMFATESC PARAM
                       ON ESCOPO.NRTIPOORCAMENTO = PARAM.NRTIPOORCAMENTO
                      AND ESCOPO.NRORG           = PARAM.NRORG
                    WHERE TO_CHAR(C_APUR.DTINICOMPET, 'DD') BETWEEN NRDIACOMPETINI AND NRDIACOMPETFIN
                      AND NEGO.NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
                      AND NEGO.IDTPFATURA    = 'X'
                ) LOOP
                    IF(TO_CHAR(C_APUR.DTINICOMPET, 'DD') <> (LPAD(C_INFOPERIODOINC.NRDIACOMPETINI, 2, '0')) 
                        OR C_APUR.DTFINCOMPET <> LAST_DAY(C_APUR.DTFINCOMPET)) THEN
                        V_QTDIASCOMPETATUAL := (TRUNC(C_APUR.DTFINCOMPET) - TRUNC(C_APUR.DTINICOMPET)) + 1;
                        V_QTDIASCOMPET      := C_INFOPERIODOINC.QTDIASCOMPET;

                        IF(V_QTDIASCOMPETATUAL <> V_QTDIASCOMPET AND C_INFOPERIODOINC.IDTPCALCPARCINC <> '2') THEN
                            V_PERIODOINCOMP := 'S';
                            V_VRVENCIMENTO  := (V_VRVENCIMENTO / V_QTDIASCOMPET) * V_QTDIASCOMPETATUAL;
                        END IF;
                    END IF;
                END LOOP;
            END IF;

            -- VERIFICA SE O CONTRATO EST LIGADO A UM AGRUPAMENTO
            BEGIN
              SELECT NRAGRUPAMENTO
              INTO V_AGRUPAMENTO
              FROM FCT_RELACAGRUPAMENTOCONT
              WHERE NRSEQCONTRATO = C_APUR.NRSEQCONTRATO;
            EXCEPTION WHEN NO_DATA_FOUND THEN
              V_AGRUPAMENTO := NULL;
            END;

            IF(V_AGRUPAMENTO IS NOT NULL) THEN
                V_IDPARCAGRUP  := 'S';

                -- VERIFICA SE EXISTEM PARCELAS CRIADAS PARA ELE
                BEGIN
                    SELECT DISTINCT PARCELA.NRFATURACONT
                      INTO V_NRFATURACONTAGRUP
                      FROM FCT_FATURACONT PARCELA
                      JOIN FCT_RELACAGRUPCONTFAT RELACFAT
                        ON PARCELA.NRFATURACONT = RELACFAT.NRFATURACONT
                       AND PARCELA.NRORG        = RELACFAT.NRORG
                      JOIN FCT_RELACAGRUPAMENTOCONT RELACCONT
                        ON RELACFAT.NRSEQCONTRATO = RELACCONT.NRSEQCONTRATO
                       AND RELACFAT.NRORG         = RELACCONT.NRORG
                      JOIN FCT_AGRUPAMENTO AGRUP
                        ON RELACCONT.NRAGRUPAMENTO = AGRUP.NRAGRUPAMENTO
                       AND RELACCONT.NRORG         = AGRUP.NRORG
                     WHERE AGRUP.NRAGRUPAMENTO        = V_AGRUPAMENTO
                       AND TRUNC(PARCELA.DTINICOMPET) = TRUNC(V_DTINICOMPET)
                       AND TRUNC(PARCELA.DTFINCOMPET) = TRUNC(V_DTFINCOMPET)
                       AND PARCELA.IDTPFATURA         = 'X'
                       AND PARCELA.IDSITUACAO         = '1'
                       AND RELACFAT.IDPARCAGRUP       = 'S';

                EXCEPTION WHEN NO_DATA_FOUND THEN
                  V_NRFATURACONTAGRUP := NULL;
                END;

                V_NRFATURACONT := V_NRFATURACONTAGRUP;
                IF (V_NRFATURACONTAGRUP IS NOT NULL) THEN
                    V_IDPARCAGRUP  := 'S';
                END IF;

            ELSE
                BEGIN
                    SELECT NRFATURACONT
                    INTO V_NRFATURACONT
                    FROM FCT_RELACAGRUPCONTFAT RELACFAT
                    WHERE RELACFAT.NRMEDICAOFATURA = C_APUR.NRMEDICAOFATURA
                      AND RELACFAT.NRSEQCONTRATO   = C_APUR.NRSEQCONTRATO
                      AND RELACFAT.NRORG           = P_NRORG;
                EXCEPTION WHEN NO_DATA_FOUND THEN
                    V_NRFATURACONT := NULL;
                END;
            END IF;

            IF (V_NRFATURACONT IS NULL) THEN

                NOVO_CODIGO_SC('FCT_FATURACONT', V_NRFATURACONT, 1, P_NRORG);
                INSERT INTO FCT_FATURACONT (
                    NRFATURACONT,
                    NRSEQCONTRATO,
                    CDFILIALFATURA,
                    NRPARCFATURA,
                    CDTIPOOPERFATURA,
                    CDSERIENF,
                    CDEMPRESA,
                    CDINSCESTA,
                    CDINSCMUNI,
                    IDTPFATURA,
                    IDSITUACAO,
                    DTINICOMPET,
                    DTFINCOMPET,
                    DTLIMITEAPU,
                    DTVENCIMENTO,
                    NRDIASVENCIMENTO,
                    QTDIASFATUVENC,
                    VRVENCIMENTO,
                    VRFATURADO,
                    VRFALTAFATURAR,
                    IDFORMAFATURA,
                    DSJUSTIFICATIVA,
                    NRAPURACAO,
                    NRMEDICAOFATURA,
                    IDALTUSUARIO,
                    IDATIVO,
                    NRORG,
                    DTINCLUSAO,
                    NRORGINCLUSAO,
                    CDOPERINCLUSAO
                ) VALUES (
                    V_NRFATURACONT,
                    NULL,
                    C_APUR.CDFILIALFATURA,
                    C_APUR.NRPARCFATURA,
                    C_APUR.CDTIPOOPERFATURA,
                    C_APUR.CDSERIENF,
                    C_APUR.CDEMPRESA,
                    C_APUR.CDINSCESTA,
                    C_APUR.CDINSCMUNI,
                    'X',
                    '1',
                    V_DTINICOMPET,
                    V_DTFINCOMPET,
                    C_APUR.DTLIMITEAPU,
                    C_APUR.DTVENCIMENTO,
                    C_APUR.NRDIASVENCIMENTO,
                    C_APUR.QTDIASFATUVENC,
                    ROUND(V_VRVENCIMENTO, 2),
                    0,
                    ROUND(V_VRVENCIMENTO, 2),
                    NULL,
                    NULL,
                    NULL,
                    NULL,
                    'N',
                    'S',
                    P_NRORG,
                    SYSDATE,
                    P_NRORG,
                    P_CDOPERADOR
                );

            ELSE
                --IF(V_VALIDAAPUR = 1) THEN
                UPDATE FCT_FATURACONT
                   SET VRVENCIMENTO   = ROUND(VRVENCIMENTO + V_VRVENCIMENTO, 2),
                       VRFALTAFATURAR = ROUND(VRFALTAFATURAR + V_VRVENCIMENTO, 2),
                       DTINICOMPET    = V_DTINICOMPET,
                       DTFINCOMPET    = V_DTFINCOMPET
                 WHERE NRFATURACONT = V_NRFATURACONT
                   AND NRORG        = P_NRORG;
                --END IF;
            END IF;

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

            NOVO_CODIGO_SC('FCT_RELACAGRUPCONTFAT', V_NRAGRUPCONTFAT, 1, P_NRORG);
            INSERT INTO FCT_RELACAGRUPCONTFAT (
                NRAGRUPCONTFAT,     NRFATURACONT, IDPARCAGRUP,  NRSEQCONTRATO,
                NRORG,              IDATIVO,      DTINCLUSAO,   DTULTATU,
                CDOPERINCLUSAO,     NRORGULTATU,  CDOPERULTATU, NRMEDICAOFATURA,
                NRORGINCLUSAO
            ) VALUES (
                V_NRAGRUPCONTFAT,     V_NRFATURACONT, V_IDPARCAGRUP,       C_APUR.NRSEQCONTRATO,
                C_APUR.NRORG,         'S',            SYSDATE,             NULL,
                P_CDOPERADOR,          NULL,          NULL,                C_APUR.NRMEDICAOFATURA,
                C_APUR.NRORG
            );

            IF (V_IDPARCAGRUP = 'S') THEN

              UPDATE FCT_RELACAGRUPCONTFAT
              SET IDPARCAGRUP = 'S'
              WHERE NRFATURACONT = V_NRFATURACONT
                  AND NRORG      = P_NRORG;

            END IF;

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

                V_VRITEMMEDICAO := NVL(C_ITENS.VRITEMCONT, C_ITENS.VRTOTITEMCONT);
                IF V_PERIODOINCOMP = 'S' THEN 
                    V_VRITEMMEDICAO := (V_VRITEMMEDICAO / V_QTDIASCOMPET) * V_QTDIASCOMPETATUAL;
                END IF;

                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',
                    ROUND(V_VRITEMMEDICAO, 2), 0, ROUND(V_VRITEMMEDICAO, 2), NULL, NULL, NULL,
                    'S', P_NRORG, SYSDATE, P_NRORG, P_CDOPERADOR
                );

            END LOOP;

            /** GERAR RATEIO ITENS **/
            BEGIN
                SELECT IDGERARATEIO
                INTO V_IDGERARATEIO
                FROM FCT_CAPAPARAMFATESC ESCOPO
                JOIN FCT_CONTRATO CONT
                    ON ESCOPO.NRTIPOORCAMENTO = CONT.NRTIPOORCAMENTO
                    AND ESCOPO.NRORG          = CONT.NRORG
                WHERE CONT.NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
                    AND ESCOPO.NRORG     = P_NRORG;
            EXCEPTION WHEN NO_DATA_FOUND THEN
                V_IDGERARATEIO := 'N';
            END;

            IF (V_IDGERARATEIO = 'S') THEN
                BEGIN
                    SELECT COUNT(NRESTRUTURAM)
                    INTO V_COUNTITENSRATEIO
                    FROM (SELECT DISTINCT  NRESTRUTURAM
                          FROM FCT_RELACESTRUTURACONT ESTCONT
                          WHERE ESTCONT.NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
                            AND ESTCONT.NRORG         = P_NRORG
                            AND ESTCONT.NRESTRUTURAM IS NOT NULL
                            AND ESTCONT.IDRELACESTRUTURA = 'RATEIO'
                            AND (SELECT COUNT(*)
                                 FROM FCT_ITEMCONTRATO ITEM
                                 LEFT JOIN FCT_RELACESTRUTURACONT EST
                                    ON ITEM.NRITEMOCONTRATO = EST.NRITEMOCONTRATO
                                    AND ITEM.NRORG          = EST.NRORG
                                 WHERE ITEM.NRSEQCONTRATO  = C_APUR.NRSEQCONTRATO
                                    AND ITEM.NRORG         = P_NRORG
                                    AND EST.NRESTRUTURAM IS NULL) = 0);
                END;

                IF V_COUNTITENSRATEIO > 1 THEN
                    FOR C_ITENSRATEIO IN (
                      SELECT ESTCONT.NRESTRUTURAM,
                        LOCALM.CDCENTCUST,
                        ESTCONT.NRITEMOCONTRATO,
                        ITFATURA.VRVENCIMENTO,
                        ITFATURA.NRFATURACONT,
                        ITFATURA.NRITFATURACONT,
                        ESTCONT.VRPERCRATEIO,
                        ITFATURA.IDSITUACAO
                      FROM FCT_ITFATURACONT ITFATURA
                      JOIN FCT_RELACESTRUTURACONT ESTCONT
                        ON ITFATURA.NRITEMOCONTRATO  = ESTCONT.NRITEMOCONTRATO
                        AND ITFATURA.NRORG           = ESTCONT.NRORG
                        AND ESTCONT.IDRELACESTRUTURA = 'RATEIO'
                      LEFT JOIN ESTRUTURAM LOCALM
                        ON ESTCONT.NRESTRUTURAM = LOCALM.NRESTRUTURAM
                        AND ESTCONT.NRORG       = LOCALM.NRORG
                      WHERE ITFATURA.NRSEQCONTRATO = C_APUR.NRSEQCONTRATO
                        AND ITFATURA.NRORG         = P_NRORG
                  ) LOOP

                      NOVO_CODIGO_NT('FCT_RATEIOITFATURACONT', V_NRRATEIOITEMFAT, 1);
                      INSERT INTO FCT_RATEIOITFATURACONT (
                          NRRATEIOITEMFAT, NRITFATURACONT,  NRFATURACONT,
                          NRSEQCONTRATO,   NRITEMOCONTRATO, NRESTRUTURAM,
                          CDCENTCUST,      VRPERCENTRATEIO, VRRATEADO,
                          NRORG,           IDATIVO,         DTINCLUSAO,
                          DTULTATU,        NRORGINCLUSAO,   CDOPERINCLUSAO,
                          NRORGULTATU,     CDOPERULTATU,    IDSITUVENCRATEIO,
                          NRAPURACAO
                      ) VALUES (
                          V_NRRATEIOITEMFAT,          C_ITENSRATEIO.NRITFATURACONT,         C_ITENSRATEIO.NRFATURACONT,
                          C_APUR.NRSEQCONTRATO,       C_ITENSRATEIO.NRITEMOCONTRATO,        C_ITENSRATEIO.NRESTRUTURAM,
                          C_ITENSRATEIO.CDCENTCUST,   ROUND(C_ITENSRATEIO.VRPERCRATEIO, 2), ROUND(C_ITENSRATEIO.VRVENCIMENTO * (C_ITENSRATEIO.VRPERCRATEIO/100), 2),
                          P_NRORG,                    'S',                                  SYSDATE,
                          NULL,                       P_NRORG,                              P_CDOPERADOR,
                          NULL,                       NULL,                                 C_ITENSRATEIO.IDSITUACAO,
                          NULL
                      );
                  END LOOP; --C_ITENSRATEIO
                END IF;
            END IF;

        END IF; --V_GEROU_PARCELA = 0

    END LOOP; --C_APUR
END;
/