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
) IS  
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_EXISTE_CARD VARCHAR(1);
BEGIN

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

    IF V_HRINICSERV IS NULL THEN
        P_RETORNO := 'HRSERV_NOT_FOUND';
        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
            );

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

        DBMS_OUTPUT.PUT_LINE('INSERCAO DOS PRATOS NA PRODUCAO');
        FOR C_DADOS IN(
            SELECT 
                   A.NRCARDAPIO, A.CDFILIAL, A.CDSERVFILI, A.DTCARDAPIO, 
                   A.QTCOMESTICAR, A.QTCLIESTICAR, A.QTFUNESTICAR, A.QTDOAESTICAR,
                   A.NRSEQPRATCAR, A.CDPRATO, A.CDFILAUXPRAT, A.QTCOMENSAIS,
                   A.QTPORPESSOA, A.QTTOTAL,
                   A.HRINICATEN, A.HRINICSERV, A.NRORG,
                   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.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 NOT IN (SELECT DISTINCT P.CDPRATO
                                              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       
            ) A
            LEFT JOIN TABLE(CALCULO_EQUIP(A.CDFILIAL, A.CDPRATO, A.CDFILAUXPRAT, A.QTTOTAL, A.HRINICSERV, 'FIN', A.NRORG)) CALC
                ON CALC.CDPRATO = A.CDPRATO AND CALC.CDFILAUXPRAT = A.CDFILAUXPRAT
            ORDER BY A.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;
                V_TEMPOFINANT := C_DADOS.HRINICSERV;
            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 C_DADOS.TEMPOPREP 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 APOS O TEMPO DE SERVIMENTO
                IF V_EXCEDETEMPINI = 'S' THEN
                    V_INIPREP := V_TEMPOFINANT;
                    V_FIMPREP := V_TEMPOFINANT + C_DADOS.TEMPOPREP;
                    V_TEMPOFINANT := V_FIMPREP;
                ELSE
                    V_INIPREP := V_TEMPOFINANT - C_DADOS.TEMPOPREP;
                    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 - C_DADOS.TEMPTOTALEQUIP);
                    V_FIMPREPROD := V_FIMPREP;
                ELSE 
                    V_INIPREPROD := V_INIPREP;
                    V_FIMPREPROD := (V_INIPREP + C_DADOS.TEMPTOTALEQUIP);
                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)
                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);

             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, V_INIPREP, V_FIMPREP, P_NRORG);
            END IF;

            V_CDPRAT_ANT := C_DADOS.CDPRATO;
            V_AGRUP_ANT := C_DADOS.AGRUPAMENTO;
            V_EXISTE_CARD := 'S';
        END LOOP;

        /* CALCULA O HORARIO DA PRODUCAO CONSIDERANDO OS EQUIPAMENTOS PARA NAO SEREM UTILIZADOS AO MESMO TEMPO */
        FOR CALCULO IN 
            -- CARREGA OS HORARIOS DE PREPARACAO
            (SELECT DISTINCT EQ.NREQUIPAMENTO, EQ.TEMPOINI, (EQ.TEMPOFIN - 0.01) TEMPOFIN
                FROM EQPRODCADENCI EQ
                INNER JOIN PRODCADENCI P ON P.NRPRODCADENCI = EQ.NRPRODCADENCI
                WHERE P.CDFILIAL = P_CDFILIAL
                    AND P.CDSERVFILI = P_CDSERVFILI
                    AND P.DTCARDAPIO = P_DTCARDAPIO
                    AND P.NRORG = P_NRORG
                    AND P.INIPREPPLA IS NOT NULL
                ORDER BY EQ.NREQUIPAMENTO, EQ.TEMPOINI)
        LOOP
            -- CARREGA OS EQUIPAMENTOS UTILIZADOS NO MESMO HORARIO
            FOR EXCEDE IN
                (SELECT A.NREQUIPAMENTO, A.EQUIP_NEC, EQ.QTDISPTOTAL 
                 FROM (
                    SELECT E.NREQUIPAMENTO, SUM(E.QTDEQUIPUTIL) EQUIP_NEC
                    FROM PRODCADENCI P
                    INNER JOIN EQPRODCADENCI E ON E.NRPRODCADENCI = P.NRPRODCADENCI
                    WHERE P.CDFILIAL = P_CDFILIAL
                            AND P.CDSERVFILI = P_CDSERVFILI
                            AND P.DTCARDAPIO = P_DTCARDAPIO
                            AND P.NRORG = P_NRORG
                            AND E.TEMPOINI BETWEEN CALCULO.TEMPOINI AND CALCULO.TEMPOFIN 
                            AND E.NREQUIPAMENTO = CALCULO.NREQUIPAMENTO
                    GROUP BY E.NREQUIPAMENTO
                    ORDER BY E.NREQUIPAMENTO
                ) A
                INNER JOIN EQUIPAMCOZ EQ ON EQ.NREQUIPAMCOZ = A.NREQUIPAMENTO
                WHERE A.EQUIP_NEC > EQ.QTDISPTOTAL)
            LOOP        
                UPDATE PRODCADENCI
                   SET IDBLOQPROD = 'S'
                 WHERE NRPRODCADENCI IN (
                        SELECT DISTINCT P.NRPRODCADENCI
                        FROM EQPRODCADENCI EQ 
                        INNER JOIN PRODCADENCI P ON P.NRPRODCADENCI = EQ.NRPRODCADENCI
                        WHERE P.CDFILIAL = P_CDFILIAL
                            AND P.CDSERVFILI = P_CDSERVFILI
                            AND P.DTCARDAPIO = P_DTCARDAPIO
                            AND P.NRORG = P_NRORG
                            AND EQ.TEMPOINI BETWEEN CALCULO.TEMPOINI AND CALCULO.TEMPOFIN);
            END LOOP;
        END LOOP;

        IF V_EXISTE_CARD = 'N' THEN
            P_RETORNO := 'CARD_NOT_FOUND';
        END IF;
    END IF;

END;
/