create or replace PROCEDURE CALC_EXCEDENTES ( 
    P_CDFILIAL IN CARDADIA.CDFILIAL%TYPE, 
    P_CDSERVFILI IN CARDADIA.CDSERVFILI%TYPE,
    P_DTCARDAPIO IN CARDADIA.DTCARDAPIO%TYPE,
    P_NRORG VARCHAR2 DEFAULT 1
) AS
BEGIN
    
    UPDATE PRODCADENCI
        SET IDBLOQPROD = 'N'
    WHERE CDFILIAL = P_CDFILIAL
            AND CDSERVFILI = P_CDSERVFILI
            AND DTCARDAPIO = P_DTCARDAPIO
            AND NRORG = P_NRORG;
        
    /* 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, (EQ.TEMPOFIN - EQ.TEMPOINI) DURACAO
            FROM EQPRODCADENCI EQ
            INNER JOIN PRODCADENCI P ON P.NRPRODCADENCI = EQ.NRPRODCADENCI AND P.NRORG = EQ.NRORG 
            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
                AND P.INIPREPREAL IS NULL
            ORDER BY DURACAO, EQ.NREQUIPAMENTO)
    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 AND E.NRORG = P.NRORG 
                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
                        AND P.INIPREPREAL IS NULL
                        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 AND EQ.NRORG = P.NRORG 
                    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
                        AND P.INIPREPREAL IS NULL
                        AND EQ.TEMPOINI BETWEEN CALCULO.TEMPOINI AND CALCULO.TEMPOFIN
                        AND EQ.NREQUIPAMENTO = CALCULO.NREQUIPAMENTO);
        END LOOP;
        
    END LOOP;

END;
/



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