CREATE OR REPLACE PROCEDURE EXPORTA_MODO_PREPARO
(P_PRATOS IN VARCHAR2,
 P_ORIGEM IN VARCHAR2,
 P_CDFILIAL IN VARCHAR2,
 P_CDSERVFILI IN VARCHAR2, 
 P_DESTINO IN VARCHAR2,
 P_FILIAIS IN VARCHAR2,
 P_SERVICOS IN VARCHAR2,
 P_SOBREPOR IN VARCHAR)
AS
    V_MODOPREP_ORIGEM NUMBER;
    V_INDICE NUMBER;
    V_CDFILAUXPRAT VARCHAR2(4);
    V_CDPRATO VARCHAR2(10);
    V_CDFILIALDEST VARCHAR2(4);
    V_CDSERVFILIDEST VARCHAR2(5);
    V_MODOPREP_DEST NUMBER;
    V_NRPREPPRAT VARCHAR2(02);
    V_MODOPREPARO LONG RAW;
    V_NMRESPONSAVEL VARCHAR2(40);
BEGIN
    -- Origem e destino padrao, nao realiza a operacao
    IF P_ORIGEM = 'P' AND P_DESTINO = 'P' THEN 
        RETURN;
    END IF;
    FOR C_LISTAPRATOS IN 
    (SELECT CDLISTA
       FROM LISTA L
      WHERE L.NRLISTA = P_PRATOS) LOOP
        -- Separa os valores da chave do prato para melhor uso
        V_CDFILAUXPRAT := NULL;
        V_CDPRATO := NULL;
        V_INDICE := INSTR(C_LISTAPRATOS.CDLISTA, '-', 1);
        IF(V_INDICE > 0) THEN
            V_CDFILAUXPRAT := SUBSTR(C_LISTAPRATOS.CDLISTA, 1, V_INDICE-1);
            V_CDPRATO := SUBSTR(C_LISTAPRATOS.CDLISTA, V_INDICE+1, 10);
        END IF;
        -- Verifica se o modo de preparo de origem existe        
        IF P_ORIGEM = 'P' THEN
            BEGIN
                SELECT TXPREPPRAT, NMRPREPPRAT 
                INTO V_MODOPREPARO, V_NMRESPONSAVEL
                FROM PREPPRAPAD
                WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                AND CDPRATO = V_CDPRATO
                AND IDPREPPRAT = '1';
            EXCEPTION
                WHEN OTHERS THEN
                    V_MODOPREPARO := NULL;
                    V_NMRESPONSAVEL := NULL;
            END;
        ELSIF P_ORIGEM = 'L' THEN
            BEGIN
                SELECT TXPREPFILI, NMRPREPFILI
                INTO V_MODOPREPARO, V_NMRESPONSAVEL
                FROM PREPPRAFIL
                WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                AND CDPRATO = V_CDPRATO
                AND CDFILIAL = P_CDFILIAL
                AND IDPREPFILI = '1';
            EXCEPTION
                WHEN OTHERS THEN
                    V_MODOPREPARO := NULL;
                    V_NMRESPONSAVEL := NULL;
            END;
        ELSIF P_ORIGEM = 'S' THEN
            BEGIN
                SELECT TXPREPSERV, NMRPREPSERV
                INTO V_MODOPREPARO, V_NMRESPONSAVEL
                FROM PREPPRASER
                WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                AND CDPRATO = V_CDPRATO
                AND CDFILIAL = P_CDFILIAL
                AND CDSERVFILI = P_CDSERVFILI
                AND IDPREPSERV = '1';
            EXCEPTION
                WHEN OTHERS THEN
                    V_MODOPREPARO := NULL;
                    V_NMRESPONSAVEL := NULL;
            END;
        END IF;
        
        -- Se o modo de preparo de origem existir
        IF V_MODOPREPARO IS NOT NULL THEN
            -- PADRAO
            IF P_DESTINO = 'P' THEN
                -- Se sobrepoe, apaga o que ja esta no destino, se nao apenas altera se nao tiver no destino
                IF P_SOBREPOR = 'S' THEN                
                    DELETE FROM PREPPRAPAD
                    WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                      AND CDPRATO = V_CDPRATO
                      AND IDPREPPRAT = '1';
                    V_MODOPREP_DEST := 0;
                ELSE
                    SELECT COUNT(1)
                     INTO V_MODOPREP_DEST
                     FROM PREPPRAPAD
                    WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                      AND CDPRATO = V_CDPRATO
                      AND IDPREPPRAT = '1';
                END IF;

                -- Se o destino estiver vazio
                IF V_MODOPREP_DEST = 0 THEN  
                    -- Busca o proximo sequencial                  
                    SELECT MAX(NRPREPPRAT)
                      INTO V_NRPREPPRAT
                      FROM PREPPRAPAD
                     WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                       AND CDPRATO = V_CDPRATO;
                    IF V_NRPREPPRAT IS NULL THEN
                        V_NRPREPPRAT := '00';
                    END IF;
                    V_NRPREPPRAT := LPAD(TO_CHAR(TO_NUMBER(V_NRPREPPRAT) + 1), 2, '0');

                    -- Insere modo de preparo da origem no destino
                    INSERT INTO PREPPRAPAD
                    (CDFILAUXPRAT, CDPRATO, NRPREPPRAT, IDPREPPRAT, TXPREPPRAT, NMRPREPPRAT)
                    VALUES
                    (V_CDFILAUXPRAT, V_CDPRATO, V_NRPREPPRAT, '1', V_MODOPREPARO, V_NMRESPONSAVEL);
                END IF;
            -- LOCAL
            ELSIF P_DESTINO = 'L' THEN
                FOR C_FILIAIS IN
                (SELECT CDLISTA AS CDFILIAL
                   FROM LISTA L
                  WHERE L.NRLISTA = P_FILIAIS) LOOP
                    -- Se origem igual ao destino, passa para a proxima unidade
                    IF P_ORIGEM = 'L' AND C_FILIAIS.CDFILIAL = P_CDFILIAL THEN
                        CONTINUE;
                    END IF;
                    IF P_SOBREPOR = 'S' THEN                
                        DELETE FROM PREPPRAFIL
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = C_FILIAIS.CDFILIAL
                        AND IDPREPFILI = '1';
                        V_MODOPREP_DEST := 0;
                    ELSE
                        SELECT COUNT(1)
                        INTO V_MODOPREP_DEST
                        FROM PREPPRAFIL
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = C_FILIAIS.CDFILIAL
                        AND IDPREPFILI = '1';
                    END IF;
                    -- Se o destino estiver vazio
                    IF V_MODOPREP_DEST = 0 THEN  
                        -- Busca o proximo sequencial                  
                        SELECT MAX(NRPREPFILI)
                        INTO V_NRPREPPRAT
                        FROM PREPPRAFIL
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = C_FILIAIS.CDFILIAL;
                        IF V_NRPREPPRAT IS NULL THEN
                            V_NRPREPPRAT := '00';
                        END IF;
                        V_NRPREPPRAT := LPAD(TO_CHAR(TO_NUMBER(V_NRPREPPRAT) + 1), 2, '0');

                        -- Insere modo de preparo da origem no destino
                        INSERT INTO PREPPRAFIL
                        (CDFILAUXPRAT, CDPRATO, CDFILIAL, NRPREPFILI, IDPREPFILI, TXPREPFILI, NMRPREPFILI)
                        VALUES
                        (V_CDFILAUXPRAT, V_CDPRATO, C_FILIAIS.CDFILIAL, V_NRPREPPRAT, '1', V_MODOPREPARO, V_NMRESPONSAVEL);
                    END IF;
                END LOOP;
            -- SERVICO
            ELSIF P_DESTINO = 'S' THEN
                FOR C_SERVICOS IN
                (SELECT CDLISTA
                   FROM LISTA L
                  WHERE L.NRLISTA = P_SERVICOS) LOOP
                    -- Separa a chave do servico em filial e codigo do serviço
                    V_CDFILIALDEST := NULL;
                    V_CDSERVFILIDEST := NULL;
                    V_INDICE := INSTR(C_SERVICOS.CDLISTA, '_', 1);
                    IF(V_INDICE > 0) THEN
                        V_CDFILIALDEST := SUBSTR(C_SERVICOS.CDLISTA, 1, V_INDICE-1);
                        V_CDSERVFILIDEST := SUBSTR(C_SERVICOS.CDLISTA, V_INDICE+1, 5);
                    END IF;
                    -- Se o valor na lista nao pode ser separado em filial e servico
                    IF V_CDFILIALDEST IS NULL THEN
                        CONTINUE;
                    END IF;
                    -- Se origem igual ao destino, passa para o proximo servico
                    IF P_ORIGEM = 'S' AND V_CDFILIALDEST = P_CDFILIAL AND V_CDSERVFILIDEST = P_CDSERVFILI THEN
                        CONTINUE;
                    END IF;
                    IF P_SOBREPOR = 'S' THEN                
                        DELETE FROM PREPPRASER
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = V_CDFILIALDEST
                        AND CDSERVFILI = V_CDSERVFILIDEST
                        AND IDPREPSERV = '1';
                        V_MODOPREP_DEST := 0;
                    ELSE
                        SELECT COUNT(1)
                        INTO V_MODOPREP_DEST
                        FROM PREPPRASER
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = V_CDFILIALDEST
                        AND CDSERVFILI = V_CDSERVFILIDEST
                        AND IDPREPSERV = '1';
                    END IF;
                    -- Se o destino estiver vazio
                    IF V_MODOPREP_DEST = 0 THEN  
                        -- Busca o proximo sequencial                  
                        SELECT MAX(NRPREPSERV)
                        INTO V_NRPREPPRAT
                        FROM PREPPRASER
                        WHERE CDFILAUXPRAT = V_CDFILAUXPRAT
                        AND CDPRATO = V_CDPRATO
                        AND CDFILIAL = V_CDFILIALDEST
                        AND CDSERVFILI = V_CDSERVFILIDEST;
                        IF V_NRPREPPRAT IS NULL THEN
                            V_NRPREPPRAT := '00';
                        END IF;
                        V_NRPREPPRAT := LPAD(TO_CHAR(TO_NUMBER(V_NRPREPPRAT) + 1), 2, '0');

                        -- Insere modo de preparo da origem no destino
                        INSERT INTO PREPPRASER
                        (CDFILAUXPRAT, CDPRATO, CDFILIAL, CDSERVFILI, NRPREPSERV, IDPREPSERV, TXPREPSERV, NMRPREPSERV)
                        VALUES
                        (V_CDFILAUXPRAT, V_CDPRATO, V_CDFILIALDEST, V_CDSERVFILIDEST, V_NRPREPPRAT, '1', V_MODOPREPARO, V_NMRESPONSAVEL);
                    END IF;
                END LOOP;
            END IF;            
        END IF;
    END LOOP;
END;
/