/* Sistemas Integrados TECNISA
   Planejamento Alimentação dustrial

Nome: 7861_PLAFUNO_TIPO_RECEITA_UTILIZADA.SQL

Descrição:
Função ara retornar a receita utiliza do prato.

'S' - Receita por Serviço
'F' - Receita Local
'P' - Receita Padrão

Criação:
16/11/2011 - Samuel Anderson
*/

CREATE OR REPLACE FUNCTION RECEITA_UTILIZADA(
   P_CDPRATO      IN PRATO.CDPRATO%TYPE,
   P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
   P_CDSERVFILI   IN SERVICO.CDSERVFILI%TYPE,
   P_CDFILCENTPME IN FILIAL.CDFILIAL%TYPE DEFAULT NULL,
   P_CDTIPOCARD   IN TIPOCARD.CDTIPOCARD%TYPE DEFAULT NULL) RETURN VARCHAR2 IS

   V_IDTIPO     VARCHAR2(1);
BEGIN
    BEGIN
       SELECT DISTINCT IDTIPO
     INTO V_IDTIPO
     FROM (SELECT 'S' AS IDTIPO
             FROM RECESERV
            WHERE (RECESERV.CDFILAUXPRAT = P_CDFILAUXPRAT)
              AND (RECESERV.CDPRATO      = P_CDPRATO)
              AND (RECESERV.CDFILIAL     = P_CDFILIAL)
              AND (RECESERV.CDSERVFILI   = P_CDSERVFILI)
          UNION ALL
           SELECT 'F' AS IDTIPO
             FROM RECEFILI
            WHERE (RECEFILI.CDFILAUXPRAT = P_CDFILAUXPRAT)
              AND (RECEFILI.CDPRATO      = P_CDPRATO)
              AND (RECEFILI.CDFILIAL     = P_CDFILIAL)
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECESERV
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILIAL     = P_CDFILIAL)
                                 AND (CDSERVFILI   = P_CDSERVFILI))
          UNION ALL
           SELECT 'T' AS IDTIPO
             FROM RECETPCARD
            WHERE (RECETPCARD.CDFILCENTPME = P_CDFILCENTPME)
              AND (RECETPCARD.CDTIPOCARD   = P_CDTIPOCARD)
              AND (RECETPCARD.CDFILAUXPRAT = P_CDFILAUXPRAT)
              AND (RECETPCARD.CDPRATO      = P_CDPRATO)
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECEFILI
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILIAL     = P_CDFILIAL))
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECESERV
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILIAL     = P_CDFILIAL)
                                 AND (CDSERVFILI   = P_CDSERVFILI))
          UNION ALL 
           SELECT 'P' AS IDTIPO
             FROM RECEPADR
            WHERE (RECEPADR.CDFILAUXPRAT = P_CDFILAUXPRAT)
              AND (RECEPADR.CDPRATO      = P_CDPRATO)
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECETPCARD
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILCENTPME = P_CDFILCENTPME)
                                 AND (CDTIPOCARD   = P_CDTIPOCARD))
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECEFILI
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILIAL     = P_CDFILIAL))
              AND NOT EXISTS (SELECT CDPRATO
                                FROM RECESERV
                               WHERE (CDFILAUXPRAT = P_CDFILAUXPRAT)
                                 AND (CDPRATO      = P_CDPRATO)
                                 AND (CDFILIAL     = P_CDFILIAL)
                                 AND (CDSERVFILI   = P_CDSERVFILI)));
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
    END;

   RETURN(V_IDTIPO);
END;
/