create or replace PROCEDURE CALC_CAPACIDADE_PROD_PLA ( 
    P_CDFILIAL IN CARDADIA.CDFILIAL%TYPE, 
    P_CDSERVFILI IN CARDADIA.CDSERVFILI%TYPE,
    P_DTCARDAPIO IN CARDADIA.DTCARDAPIO%TYPE,
    P_NRORG VARCHAR2 DEFAULT 1,
    P_RECALCULAR VARCHAR2 DEFAULT 'S',
    P_RETORNO OUT VARCHAR2
) AS  
    V_SEQPROD NUMBER;
    V_SEQEQPROD NUMBER;
    V_QTPRODUTO FLOAT;

    V_INIPREP FLOAT;
    V_FIMPREP FLOAT;
    V_TEMPOFINANT FLOAT;

    V_INIPREPROD FLOAT;
    V_FIMPREPROD FLOAT;

    V_EXCEDETEMPINI VARCHAR(1);

    V_CDPRAT_ANT VARCHAR2(10);
    V_AGRUP_ANT NUMBER;

    V_HRINICSERV VARCHAR(4);
    V_HRINICATEN VARCHAR(4);
    V_EXISTE_CARD VARCHAR(1);

    V_MEDIATEMPOPREP FLOAT;
    V_PERCENTTEMPOPREP FLOAT;

    V_PERIODODATA DATE;

    V_QTPRATINICIADO FLOAT;
    V_QTTOTAL FLOAT;
    V_QTTOTALPRAT FLOAT;
    V_QTPADRAO FLOAT;
    
    V_HISTORICO VARCHAR2(1);
    --procedure de historico
    PROCEDURE HISTORICO_CALC_EQUIP
      (
       P_DTCARDAPIO IN DATE,
       P_CDFILIAL IN EQUIPAMCOZ.CDCENTRAL%TYPE,
       P_CDSERVFILI IN SERVICO.CDSERVFILI%TYPE,
       P_CDPRATO IN PRATO.CDPRATO%TYPE,
       P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
       P_QTDPROD IN NUMBER,
       P_NRCOMENSAIS IN NUMBER,
       P_NRORG VARCHAR2 DEFAULT 1,
       P_HISTORICO OUT VARCHAR2
       )
    AS  

    V_QTMEDIA FLOAT;
    V_QTDAPRODUZIR FLOAT;
    V_PERCENTGRUP FLOAT;
    V_PERIODODATA DATE;
    V_TEMPTOTALEQUIP FLOAT;
    V_PERCENTTEMPEQUIP FLOAT;
    V_TEMPOPREPEQUIP FLOAT;
    V_SEQ NUMBER;
    V_SEQEQPROD NUMBER;
    V_GRUPANT NUMBER;

    BEGIN

        P_HISTORICO := 'N';
        V_PERIODODATA := TO_CHAR(P_DTCARDAPIO, 'DD/MM/YYYY');
        V_PERIODODATA := V_PERIODODATA - 90;

        -- RETORNA QUANTIDADE MEDIA DE PRODUCAO
        BEGIN
            SELECT NUMQUEBRAS
            INTO V_QTMEDIA 
            FROM (
                SELECT MAX(DTCARDAPIO) DTCARDAPIO, COUNT(NUMQUEBRAS) REPETICOES, NUMQUEBRAS
                FROM(
                    SELECT DTCARDAPIO, COUNT(NRPRODCADENCI) NUMQUEBRAS
                    FROM PRODCADENCI
                    WHERE CDFILIAL = P_CDFILIAL
                        AND CDSERVFILI = P_CDSERVFILI
                        AND CDPRATO = P_CDPRATO
                        AND NRORG = P_NRORG
                        AND DTCARDAPIO > V_PERIODODATA
                        AND TO_CHAR(DTCARDAPIO, 'D') = TO_CHAR(TO_DATE(P_DTCARDAPIO), 'D')
                        AND INIPREPREAL IS NOT NULL
                    GROUP BY DTCARDAPIO
                    ORDER BY DTCARDAPIO DESC
                )
                GROUP BY NUMQUEBRAS
                ORDER BY REPETICOES DESC, DTCARDAPIO DESC
            )
            WHERE ROWNUM = 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
                 RETURN;
            END;
        END;

        -- RETORNA TEMPO TOTAL UTILIZADO NOS EQUIPAMENTOS
        BEGIN
            SELECT SUM(EQPRAT.TEMPOPREP)
            INTO V_TEMPTOTALEQUIP
            FROM EQUIPRATOCOZ EQPRAT
            INNER JOIN EQUIPAMCOZ EQ ON EQ.NREQUIPAMCOZ = EQPRAT.NREQUIPAMENTO AND EQ.NRORG = EQPRAT.NRORG 
            WHERE EQPRAT.CDPRATO = P_CDPRATO
                 AND EQPRAT.CDFILAUXPRAT = P_CDFILAUXPRAT 
                 AND EQ.CDCENTRAL = P_CDFILIAL
                 AND EQ.NRORG = P_NRORG;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN BEGIN
                 RETURN;
            END;
        END;

        FOR C_PRATO IN (
            SELECT P.CDPRATO, P.CDFILAUXPRAT, ROUND(AVG(P.FIMPREPREAL), 4) FIMPREPREAL, 
                  ROUND(AVG(P.FIMPREPREAL - P.INIPREPREAL),4) TEMPOPREPTOTAL, (SUM(QUANTIDADE) / QT.QTPRODMAX) QTMEDIAGRUPO, 
                  P.NRGRUPO
            FROM PRODCADENCI P 
            INNER JOIN (
                SELECT CDPRATO, CDFILAUXPRAT , MAX(NRGRUPO) QTPRODMAX 
                FROM PRODCADENCI
                WHERE CDPRATO = P_CDPRATO 
                        AND CDFILAUXPRAT = P_CDFILAUXPRAT 
                        AND CDFILIAL = P_CDFILIAL
                        AND CDSERVFILI = P_CDSERVFILI
                        AND NRORG = P_NRORG
                        AND DTCARDAPIO <> P_DTCARDAPIO
                        AND DTCARDAPIO > V_PERIODODATA
                        AND TO_CHAR(DTCARDAPIO, 'D') = TO_CHAR(TO_DATE(P_DTCARDAPIO), 'D')
                        AND FIMPREPREAL IS NOT NULL
                GROUP BY CDPRATO, CDFILAUXPRAT 
            ) QT ON QT.CDPRATO = P.CDPRATO AND QT.CDFILAUXPRAT = P.CDFILAUXPRAT 
            WHERE P.CDPRATO = P_CDPRATO 
                AND P.CDFILAUXPRAT = P_CDFILAUXPRAT 
                AND P.CDFILIAL = P_CDFILIAL
                AND P.CDSERVFILI = P_CDSERVFILI
                AND P.NRORG = P_NRORG
                AND P.DTCARDAPIO <> P_DTCARDAPIO
                AND P.DTCARDAPIO > V_PERIODODATA
                AND TO_CHAR(P.DTCARDAPIO, 'D') = TO_CHAR(TO_DATE(P_DTCARDAPIO), 'D')
                AND P.FIMPREPREAL IS NOT NULL
            GROUP BY P.CDPRATO, P.CDFILAUXPRAT, P.NRGRUPO, QT.QTPRODMAX 
            ORDER BY P.NRGRUPO
        )LOOP
            V_SEQEQPROD := 0;
            V_TEMPOFINANT := NULL;
            V_PERCENTGRUP := C_PRATO.QTMEDIAGRUPO/V_QTMEDIA;
            V_QTDAPRODUZIR := ROUND(V_PERCENTGRUP * P_QTDPROD, 4);

            NOVO_CODIGO_NT('PRODCADENCI', V_SEQ);            
            INSERT INTO PRODCADENCI 
                (NRPRODCADENCI, CDFILIAL, CDSERVFILI, DTCARDAPIO, 
                 CDPRATO, CDFILAUXPRAT, QUANTIDADE,
                 IDSTATUS, NRCOMENSAIS, INIPREPPLA, FIMPREPPLA, 
                 INIPREPREAL, FIMPREPREAL, IDCONFIRMPLAN, IDBLOQPROD, NRORG, NRGRUPO)
            VALUES 
                (V_SEQ, P_CDFILIAL, P_CDSERVFILI, P_DTCARDAPIO, 
                 P_CDPRATO, P_CDFILAUXPRAT, V_QTDAPRODUZIR,
                 'P', P_NRCOMENSAIS, (C_PRATO.FIMPREPREAL - C_PRATO.TEMPOPREPTOTAL), C_PRATO.FIMPREPREAL, 
                 NULL, NULL, 'S', 'N', P_NRORG, C_PRATO.NRGRUPO);

            FOR C_DADOS IN (

                SELECT DISTINCT EQ.NREQUIPAMENTO, EQ.QTDEQUIPUTIL, EQPRAT.TEMPOPREP
                FROM EQPRODCADENCI EQ
                INNER JOIN PRODCADENCI P ON P.NRPRODCADENCI = EQ.NRPRODCADENCI AND P.NRORG = EQ.NRORG 
                INNER JOIN EQUIPRATOCOZ EQPRAT ON EQPRAT.NREQUIPAMENTO = EQ.NREQUIPAMENTO AND EQPRAT.CDPRATO = P.CDPRATO
                    AND EQPRAT.CDFILAUXPRAT = P.CDFILAUXPRAT AND EQPRAT.NRORG = P.NRORG  
                WHERE P.CDPRATO = P_CDPRATO 
                            AND P.CDFILAUXPRAT = P_CDFILAUXPRAT 
                            AND P.CDFILIAL = P_CDFILIAL
                            AND P.CDSERVFILI = P_CDSERVFILI
                            AND P.NRORG = P_NRORG
                            AND P.DTCARDAPIO > V_PERIODODATA
                            AND TO_CHAR(P.DTCARDAPIO, 'D') = TO_CHAR(TO_DATE(P_DTCARDAPIO), 'D')
                            AND P.FIMPREPREAL IS NOT NULL
                            AND P.NRGRUPO = C_PRATO.NRGRUPO
            )LOOP
                V_PERCENTTEMPEQUIP := C_DADOS.TEMPOPREP/V_TEMPTOTALEQUIP;
                V_TEMPOPREPEQUIP := ROUND( V_PERCENTTEMPEQUIP * C_PRATO.TEMPOPREPTOTAL,4);

                IF(V_TEMPOFINANT IS NULL) THEN
                    V_TEMPOFINANT := ROUND(C_PRATO.FIMPREPREAL,4);
                END IF;

                V_SEQEQPROD := V_SEQEQPROD + 1;
                INSERT INTO EQPRODCADENCI 
                    (NREQPRODCADENCI , NRPRODCADENCI , NREQUIPAMENTO , 
                    QTDEQUIPUTIL, TEMPOINI, TEMPOFIN, NRORG)
                VALUES 
                    (V_SEQEQPROD, V_SEQ, C_DADOS.NREQUIPAMENTO, 
                     C_DADOS.QTDEQUIPUTIL, ROUND(V_TEMPOFINANT - V_TEMPOPREPEQUIP, 4), ROUND(V_TEMPOFINANT, 4), P_NRORG);

                V_TEMPOFINANT := V_TEMPOFINANT - V_TEMPOPREPEQUIP;
                P_HISTORICO := 'S';

            END LOOP;
        END LOOP;
    END;

BEGIN

    V_EXISTE_CARD := 'N';
    BEGIN
        SELECT HRINICSERV, HRINICATEN
        INTO V_HRINICSERV, V_HRINICATEN
        FROM SERVICO
        WHERE CDFILIAL = P_CDFILIAL
            AND CDSERVFILI = P_CDSERVFILI
            AND NRORG = P_NRORG; 
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            P_RETORNO := 'SERV_NOT_FOUND';
         END;
         RETURN;
    END;
    

    IF V_HRINICSERV IS NULL OR V_HRINICATEN IS NULL THEN
        IF V_HRINICSERV IS NULL THEN 
            P_RETORNO := 'HRSERV_NOT_FOUND';
        ELSE
            P_RETORNO := 'HRATEN_NOT_FOUND';
        END IF;
        RETURN;
    ELSE
        V_SEQEQPROD := 0;
        V_EXCEDETEMPINI := 'N';
        V_AGRUP_ANT := NULL;

        DBMS_OUTPUT.PUT_LINE('DELETA INFORMACOES DA PRODUCAO PARA O PERIODO');
        IF P_RECALCULAR = 'S' THEN

            DELETE FROM EQPRODCADENCI
            WHERE NRPRODCADENCI IN (
                SELECT NRPRODCADENCI
                FROM PRODCADENCI
                WHERE CDFILIAL = P_CDFILIAL
                    AND CDSERVFILI = P_CDSERVFILI
                    AND DTCARDAPIO = P_DTCARDAPIO
                    AND NRORG = P_NRORG
                    AND INIPREPREAL IS NULL
            );

            DELETE FROM PRODCADENCI
            WHERE CDFILIAL = P_CDFILIAL
                AND CDSERVFILI = P_CDSERVFILI
                AND DTCARDAPIO = P_DTCARDAPIO
                AND NRORG = P_NRORG
                AND INIPREPREAL IS NULL;
        ELSE
            DELETE FROM PRODCADENCI
            WHERE CDFILIAL = P_CDFILIAL
                AND CDSERVFILI = P_CDSERVFILI
                AND DTCARDAPIO = P_DTCARDAPIO
                AND NRORG = P_NRORG
                AND INIPREPREAL IS NULL
                AND INIPREPPLA IS NULL;
        END IF;

        FOR C_CARD IN (
            SELECT C.NRCARDAPIO, C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, 
                   C.QTCOMESTICAR, C.QTCLIESTICAR, C.QTFUNESTICAR, C.QTDOAESTICAR,
                   CC.NRSEQPRATCAR, CC.CDPRATO, CC.CDFILAUXPRAT, (C.QTCOMESTICAR * CC.VRPECONSCARD) / 100 AS QTCOMENSAIS,
                   --MAX(R.QTPADRAO) QTPORPESSOA, ((C.QTCOMESTICAR * CC.VRPECONSCARD) / 100) * MAX(R.QTPADRAO) QTTOTAL,
                   HORACHAR_HORADEC(S.HRINICATEN) HRINICATEN, HORACHAR_HORADEC(S.HRINICSERV) HRINICSERV, 
                   C.NRORG
                  FROM CARDADIA C 
                 INNER JOIN SERVICO S ON S.CDFILIAL = C.CDFILIAL AND S.CDSERVFILI = C.CDSERVFILI AND S.NRORG = C.NRORG
                 INNER JOIN COMPCARD CC ON CC.NRORG = C.NRORG AND CC.CDFILIAL = C.CDFILIAL AND CC.NRCARDAPIO = C.NRCARDAPIO
                    AND ((P_RECALCULAR = 'S') OR
                            CC.CDPRATO || CC.CDFILAUXPRAT NOT IN (SELECT DISTINCT P.CDPRATO || P.CDFILAUXPRAT
                                              FROM PRODCADENCI P
                                              WHERE  P.NRORG = P_NRORG
                                                       AND P.CDFILIAL = P_CDFILIAL
                                                       AND P.CDSERVFILI = P_CDSERVFILI
                                                       AND P.DTCARDAPIO = P_DTCARDAPIO
                                                       AND P.INIPREPPLA IS NOT NULL))
                 --INNER JOIN TABLE(RECEITA_UTIL(C.CDFILIAL, C.CDSERVFILI, CC.CDPRATO, CC.CDFILAUXPRAT, '0', 'Z', CC.CDPRATO,0,0,'N')) R 
                 --   ON R.CDPRATO = CC.CDPRATO AND R.CDFILAUXPRAT = CC.CDFILAUXPRAT AND R.QTPADRAO > 0
                 WHERE C.NRORG = P_NRORG
                   AND C.CDFILIAL = P_CDFILIAL
                   AND C.CDSERVFILI = P_CDSERVFILI
                   AND C.DTCARDAPIO = P_DTCARDAPIO
                 GROUP BY C.NRCARDAPIO, C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, 
                       C.QTCOMESTICAR, C.QTCLIESTICAR, C.QTFUNESTICAR, C.QTDOAESTICAR,
                       CC.NRSEQPRATCAR, CC.CDPRATO, CC.CDFILAUXPRAT, CC.VRPECONSCARD,
                       S.HRINICATEN, S.HRINICSERV, C.NRORG
                 ORDER BY C.DTCARDAPIO, CC.NRSEQPRATCAR      
        )LOOP
            
            BEGIN 
                SELECT QTPADRAO INTO V_QTPADRAO
                FROM( SELECT NVL(QTPADRAOSERV,0) AS QTPADRAO FROM PRATSERV WHERE CDFILIAL = C_CARD.CDFILIAL AND CDSERVFILI = C_CARD.CDSERVFILI
                        AND CDPRATO = C_CARD.CDPRATO AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT AND NVL(QTPADRAOSERV,0) > 0
                      UNION ALL
                      SELECT NVL(QTPADRAOFILI, 0) AS QTPADRAO FROM PRATFILI WHERE CDFILIAL = C_CARD.CDFILIAL AND CDPRATO = C_CARD.CDPRATO
                         AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT AND NVL(QTPADRAOFILI, 0) > 0 AND NOT EXISTS (SELECT CDPRATO FROM PRATSERV WHERE CDFILIAL = C_CARD.CDFILIAL AND CDSERVFILI = C_CARD.CDSERVFILI
                                                                            AND CDPRATO = C_CARD.CDPRATO AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT
                                                                            AND NVL(QTPADRAOSERV,0) > 0)
                      UNION ALL
                      SELECT NVL(QTPADRAOPRAT,0) QTPADRAO FROM PRATPADR WHERE CDPRATO = C_CARD.CDPRATO AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT
                         AND NOT EXISTS (SELECT CDPRATO FROM PRATSERV WHERE CDFILIAL = C_CARD.CDFILIAL AND CDSERVFILI = C_CARD.CDSERVFILI
                                            AND CDPRATO = C_CARD.CDPRATO AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT
                                            AND NVL(QTPADRAOSERV, 0) > 0)
                         AND NOT EXISTS (SELECT CDPRATO FROM PRATFILI WHERE CDFILIAL = C_CARD.CDFILIAL AND CDPRATO = C_CARD.CDPRATO AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT AND NVL(QTPADRAOFILI, 0) > 0));
            EXCEPTION
                WHEN NO_DATA_FOUND THEN BEGIN
                    V_QTPADRAO := 0;
                END;
            END;
            
            IF V_QTPADRAO > 0 THEN
                
                V_QTTOTAL := C_CARD.QTCOMENSAIS * V_QTPADRAO;
                
                BEGIN
                    SELECT NVL(SUM(QUANTIDADE), 0)
                        INTO V_QTPRATINICIADO
                    FROM PRODCADENCI
                    WHERE CDFILIAL = C_CARD.CDFILIAL
                        AND CDSERVFILI = C_CARD.CDSERVFILI
                        AND DTCARDAPIO = C_CARD.DTCARDAPIO
                        AND CDPRATO = C_CARD.CDPRATO
                        AND CDFILAUXPRAT = C_CARD.CDFILAUXPRAT
                        AND NRORG = C_CARD.NRORG
                        AND INIPREPREAL IS NOT NULL;
                 EXCEPTION
                     WHEN NO_DATA_FOUND THEN BEGIN
                        V_QTPRATINICIADO := 0;
                     END;
                END;
                V_QTTOTALPRAT := V_QTTOTAL - V_QTPRATINICIADO;
            
                HISTORICO_CALC_EQUIP(C_CARD.DTCARDAPIO, C_CARD.CDFILIAL, C_CARD.CDSERVFILI, C_CARD.CDPRATO, C_CARD.CDFILAUXPRAT, V_QTTOTALPRAT, C_CARD.QTCOMENSAIS, C_CARD.NRORG, V_HISTORICO);
                    
                IF V_HISTORICO = 'N' THEN
                    FOR C_DADOS IN(
                        SELECT 
                               CARD.*,
                               CALC.NREQUIPAMENTO, CALC.DESCRICAO, CALC.QTDISPTOTAL, CALC.PESO,
                               CALC.QTEQUIP, CALC.QTPRODUTO, CALC.TEMPOPREP, CALC.HORAINI, CALC.HORAFIN, CALC.TEMPTOTALEQUIP, CALC.AGRUPAMENTO 
                        FROM ( SELECT C_CARD.NRCARDAPIO, C_CARD.CDFILIAL, C_CARD.CDSERVFILI, C_CARD.DTCARDAPIO, 
                               C_CARD.QTCOMESTICAR, C_CARD.QTCLIESTICAR, C_CARD.QTFUNESTICAR, C_CARD.QTDOAESTICAR,
                               C_CARD.NRSEQPRATCAR, C_CARD.CDPRATO, C_CARD.CDFILAUXPRAT, C_CARD.QTCOMENSAIS, V_QTPADRAO AS QTPORPESSOA, V_QTTOTALPRAT AS QTTOTAL,
                               C_CARD.HRINICATEN, C_CARD.HRINICSERV, C_CARD.NRORG 
                               FROM DUAL) CARD
                        LEFT JOIN (TABLE(CALCULO_EQUIP(C_CARD.CDFILIAL, C_CARD.CDPRATO, C_CARD.CDFILAUXPRAT, V_QTTOTALPRAT, C_CARD.HRINICSERV, 'FIN', C_CARD.NRORG))) CALC 
                            ON CALC.CDPRATO = CARD.CDPRATO AND CALC.CDFILAUXPRAT = CARD.CDFILAUXPRAT 
                        ORDER BY CARD.CDPRATO, CALC.AGRUPAMENTO, CALC.NREQUIPAMENTO
                    ) LOOP
                        --RESETA VALORES
                        IF C_DADOS.CDPRATO <> V_CDPRAT_ANT OR V_CDPRAT_ANT IS NULL THEN
                            IF V_TEMPOFINANT IS NOT NULL THEN
                                V_EXCEDETEMPINI := 'N';
                            END IF;
    
                            --VERIFICA HISTÃ“RICO DO PRATO (TEMPO DE PREPARO, E TEMPO DE FIM DE PREPARO)
                            --HISTÃ“RICO CONSIDERANDO 3 MESES(90 DIAS) ANTERIORES A DATA DO CARDÃPIO
                            V_PERIODODATA := TO_CHAR(P_DTCARDAPIO, 'DD/MM/YYYY');
                            V_PERIODODATA := V_PERIODODATA - 90;
                            BEGIN
    
                                SELECT AVG(TEMPOPREP), AVG(FIMPREPREAL)
                                INTO V_MEDIATEMPOPREP, V_TEMPOFINANT
                                FROM (
                                    SELECT P.DTCARDAPIO, AVG(P.FIMPREPREAL - P.INIPREPREAL) TEMPOPREP, TEMP.FIMPREPREAL
                                    FROM PRODCADENCI P 
                                    INNER JOIN (
                                        SELECT DTCARDAPIO, MAX(FIMPREPREAL) FIMPREPREAL
                                        FROM PRODCADENCI
                                        WHERE CDPRATO = C_DADOS.CDPRATO 
                                        AND CDFILAUXPRAT = C_DADOS.CDFILAUXPRAT 
                                        AND CDFILIAL = C_DADOS.CDFILIAL
                                        AND CDSERVFILI = C_DADOS.CDSERVFILI
                                        AND NRORG = C_DADOS.NRORG
                                        GROUP BY DTCARDAPIO
                                    ) TEMP ON TEMP.DTCARDAPIO = P.DTCARDAPIO 
                                    WHERE P.CDPRATO = C_DADOS.CDPRATO 
                                        AND P.CDFILAUXPRAT = C_DADOS.CDFILAUXPRAT 
                                        AND P.CDFILIAL = C_DADOS.CDFILIAL
                                        AND P.CDSERVFILI = C_DADOS.CDSERVFILI
                                        AND P.NRORG = C_DADOS.NRORG
                                        AND P.DTCARDAPIO > V_PERIODODATA
                                        AND TO_CHAR(P.DTCARDAPIO, 'D') = TO_CHAR(TO_DATE(C_DADOS.DTCARDAPIO), 'D')
                                        GROUP BY P.DTCARDAPIO, TEMP.FIMPREPREAL
                                    ORDER BY P.DTCARDAPIO
                                );
    
                                IF V_MEDIATEMPOPREP IS NULL THEN
                                    V_MEDIATEMPOPREP := C_DADOS.TEMPTOTALEQUIP;
                                    V_PERCENTTEMPOPREP := 1;
                                ELSE 
                                    V_PERCENTTEMPOPREP := C_DADOS.TEMPOPREP/C_DADOS.TEMPTOTALEQUIP;
                                END IF;
    
                                IF V_TEMPOFINANT IS NULL THEN
                                    V_TEMPOFINANT := C_DADOS.HRINICSERV;
                                END IF;
                            EXCEPTION
                                 WHEN NO_DATA_FOUND THEN BEGIN
                                    V_MEDIATEMPOPREP := C_DADOS.TEMPTOTALEQUIP;
                                    V_PERCENTTEMPOPREP := 1;
                                    V_TEMPOFINANT := C_DADOS.HRINICSERV;
                                 END;
                            END;
                        END IF;
    
                        --CALCULO DA QUANTIDADE DO PRODUTO
                        IF C_DADOS.QTPRODUTO IS NULL THEN
                            V_QTPRODUTO := C_DADOS.QTTOTAL;
                        ELSE 
                            V_QTPRODUTO := C_DADOS.QTPRODUTO;
                        END IF;
    
                        --CALCULO DE TEMPO
                        IF V_MEDIATEMPOPREP IS NULL THEN
                            V_INIPREP := NULL;
                            V_FIMPREP := NULL;
                        ELSE 
    
                            IF( V_AGRUP_ANT <> C_DADOS.AGRUPAMENTO AND (V_TEMPOFINANT - C_DADOS.TEMPTOTALEQUIP) < C_DADOS.HRINICATEN) THEN
                                V_EXCEDETEMPINI := 'S';
                                V_TEMPOFINANT := C_DADOS.HRINICSERV;
                            END IF;
    
                            --CASO TEMPO DE PRODUCAO ULTRAPASSE O TEMPO INICIAL DO SERVICO, INSERE APÃ“S O TEMPO DE SERVIMENTO
                            IF V_EXCEDETEMPINI = 'S' THEN
                                V_INIPREP := V_TEMPOFINANT;
                                V_FIMPREP := V_TEMPOFINANT + (V_MEDIATEMPOPREP * V_PERCENTTEMPOPREP);
                                V_TEMPOFINANT := V_FIMPREP;
                            ELSE
                                V_INIPREP := V_TEMPOFINANT - (V_MEDIATEMPOPREP * V_PERCENTTEMPOPREP);
                                V_FIMPREP := V_TEMPOFINANT;
                                V_TEMPOFINANT := V_INIPREP;
                            END IF;            
    
                        END IF;
    
                        --INSERE PRODCADENCI 
                        IF( V_CDPRAT_ANT IS NULL OR C_DADOS.CDPRATO <> V_CDPRAT_ANT 
                            OR ( C_DADOS.CDPRATO = V_CDPRAT_ANT AND C_DADOS.AGRUPAMENTO <> V_AGRUP_ANT)) THEN 
    
                            IF V_EXCEDETEMPINI = 'N' THEN
                                V_INIPREPROD := (V_FIMPREP - V_MEDIATEMPOPREP);
                                V_FIMPREPROD := V_FIMPREP;
                            ELSE 
                                V_INIPREPROD := V_INIPREP;
                                V_FIMPREPROD := (V_INIPREP + V_MEDIATEMPOPREP);
                            END IF;
    
                            NOVO_CODIGO_NT('PRODCADENCI', V_SEQPROD);            
                            INSERT INTO PRODCADENCI 
                                (NRPRODCADENCI, CDFILIAL, CDSERVFILI, DTCARDAPIO, 
                                 CDPRATO, CDFILAUXPRAT, QUANTIDADE,
                                 IDSTATUS, NRCOMENSAIS, INIPREPPLA, FIMPREPPLA, 
                                 INIPREPREAL, FIMPREPREAL, IDCONFIRMPLAN, IDBLOQPROD, NRORG, NRGRUPO)
                            VALUES 
                                (V_SEQPROD, C_DADOS.CDFILIAL, C_DADOS.CDSERVFILI, C_DADOS.DTCARDAPIO, 
                                 C_DADOS.CDPRATO, C_DADOS.CDFILAUXPRAT, V_QTPRODUTO,
                                 'P', C_DADOS.QTCOMENSAIS, V_INIPREPROD, V_FIMPREPROD, 
                                 NULL, NULL, 'S', 'N', P_NRORG, NVL(C_DADOS.AGRUPAMENTO, 1));
    
                         END IF;
    
                        --INSERE EQPRODCADENCI CASO POSSUA EQUIPAMENTO
                        IF C_DADOS.NREQUIPAMENTO IS NOT NULL THEN
                            V_SEQEQPROD := V_SEQEQPROD + 1;
                            INSERT INTO EQPRODCADENCI 
                                (NREQPRODCADENCI , NRPRODCADENCI , NREQUIPAMENTO , 
                                QTDEQUIPUTIL, TEMPOINI, TEMPOFIN, NRORG)
                            VALUES 
                                (V_SEQEQPROD, V_SEQPROD, C_DADOS.NREQUIPAMENTO, 
                                 C_DADOS.QTEQUIP, ROUND(V_INIPREP, 4), ROUND(V_FIMPREP, 4), P_NRORG);
                        END IF;
    
                        V_CDPRAT_ANT := C_DADOS.CDPRATO;
                        V_AGRUP_ANT := C_DADOS.AGRUPAMENTO;
                        V_EXISTE_CARD := 'S';
                    END LOOP;
    
                END IF;
                
            END IF;
            
        END LOOP;
        
        --VERIFICA PRATOS  EXCEDENTES
        CALC_EXCEDENTES(P_CDFILIAL, P_CDSERVFILI, P_DTCARDAPIO, P_NRORG);
       
        IF V_EXISTE_CARD = 'N' THEN
            P_RETORNO := 'CARD_NOT_FOUND';
        END IF;
    END IF;

END;
/


-- VERSAO --
BEGIN ATUVEROBJ('CALC_CAPACIDADE_PROD_PLA','X.XX.XXX'); END;
/
