CREATE OR REPLACE FUNCTION FC_TUSS_EMISSAO_SERVICO_GUIA (vNRAUTORIZACAO IN NUMBER) -- Table AUTORIZACAO
RETURN NUMBER
IS vNRAUTORIZACAO AUTORIZACAO.NRAUTORIZACAO%TYPE;
CURSOR cPROCURA IS SELECT CDSERVICO FROM SERVICO_DA_AUTORIZACAO
WHERE NRAUTORIZACAO = vNRAUTORIZACAO AND NRSEQ_SERV_AUTORIZACAO
IN (SELECT NRSEQ_SERV_AUTORIZACAO FROM SERVICO_DA_AUTORIZACAO);
/*
****************************************************************************************************************************
Criagco : Daniel Rodrigo Marques Vitellozzi
Data : 14/09/2010
Msdulo Principal : Produgco Midica - Autorizagco
Msdulos : PM60FMAut
Objetivo : Validar o campo CDSERVICO da tabela SERVICO_DA_AUTORIZACAO de uma determinada guia da tabela AUTORIZACAO.
Alteragues :
+--------------------------------------------------------------------------------------------------------------------------+
| DATA | RESPONSAVEL | VERSAO | PENDJNCIA | ALTERACAO |
|------------+------------------+--------+-----------+---------------------------------------------------------------------|
| 14/09/2010 | Evandro Mizobuti | | | |
+--------------------------------------------------------------------------------------------------------------------------+
Scripts : SELECT * FROM AUTORIZACAO WHERE NRAUTORIZACAO = '385227'
SELECT * FROM SERVICO where CDSERVICO = '10106014'
SELECT * FROM SERVICO_TAB_REFER WHERE CDSERVICO = '10106014' -- TUSS
SELECT CDSERVICO FROM SERVICO_DA_AUTORIZACAO WHERE NRAUTORIZACAO LIKE '%385227%'
AND NRSEQ_SERV_AUTORIZACAO IN ( SELECT NRSEQ_SERV_AUTORIZACAO FROM SERVICO_DA_AUTORIZACAO )
****************************************************************************************************************************
*/
BEGIN
OPEN cPROCURA;
IF NOT CDSERVICO IN ('40201171','40202690','40201180','40202720') AND (CDSERVICO = '40201082') AND (CDSERVICO <> '40202666')
OR NOT CDSERVICO IN ('40201082','40202690','40201180','40202720') AND (CDSERVICO = '40201171')
OR NOT CDSERVICO IN ('40201171','40201082','40201180','40202720') AND (CDSERVICO = '40202690')
OR NOT CDSERVICO IN ('40201171','40202690','40201082','40202720') AND (CDSERVICO = '40201180')
OR NOT CDSERVICO IN ('40201171','40202690','40201180','40201082') AND (CDSERVICO = '40202720')
OR (CDSERVICO = '40202666' AND CDSERVICO <> '40201082') THEN
IF (CDSERVICO = '20103263' AND CDSERVICO <> '20103271') OR (CDSERVICO = '20103271' AND CDSERVICO <> '20103263') THEN
IF (CDSERVICO = '40301354' AND CDSERVICO <> '40301362') OR (CDSERVICO = '40301362' AND CDSERVICO <> '40301354')
OR (CDSERVICO = '40701042' AND CDSERVICO <> '40701050') OR (CDSERVICO = '40701050' AND CDSERVICO <> '40701042')
OR (CDSERVICO = '40809099' AND CDSERVICO <> '41002024') OR (CDSERVICO = '41002024' AND CDSERVICO <> '40809099')
OR (CDSERVICO = '41001036' AND CDSERVICO <> '41001044') OR (CDSERVICO = '41001044' AND CDSERVICO <> '41001036') THEN
IF (CDSERVICO = '41203100' AND CDSERVICO <> '41203119') OR (CDSERVICO = '41203119' AND CDSERVICO <> '41203100') THEN
IF (CDSERVICO = '41203020' AND NOT CDSERVICO IN ('41203038','41203046'))
OR (CDSERVICO = '41203038' AND NOT CDSERVICO IN ('41203020','41203046'))
OR (CDSERVICO = '41203046' AND NOT CDSERVICO IN ('41203038','41203020')) THEN
IF (CDSERVICO = '41101014' AND NOT CDSERVICO IN ('41101022','41101030','41101073','41101081','41101090'))
OR (CDSERVICO = '41101022' AND NOT CDSERVICO IN ('41101014','41101030','41101073','41101081','41101090'))
OR (CDSERVICO = '41101030' AND NOT CDSERVICO IN ('41101022','41101014','41101073','41101081','41101090'))
OR (CDSERVICO = '41101073' AND NOT CDSERVICO IN ('41101022','41101030','41101014','41101081','41101090'))
OR (CDSERVICO = '41101081' AND NOT CDSERVICO IN ('41101022','41101030','41101073','41101014','41101090'))
OR (CDSERVICO = '41101090' AND NOT CDSERVICO IN ('41101022','41101030','41101073','41101081','41101014')) THEN
IF (CDSERVICO = '41101138' AND CDSERVICO <> '41101154') OR (CDSERVICO = '41101154' AND CDSERVICO <> '41101138') THEN
IF (CDSERVICO = '41101189' AND NOT CDSERVICO IN ('41101200','41101219','41101278'))
OR (CDSERVICO = '41101200' AND NOT CDSERVICO IN ('41101189','41101219','41101278'))
OR (CDSERVICO = '41101219' AND NOT CDSERVICO IN ('41101200','41101189','41101278'))
OR (CDSERVICO = '41101278' AND NOT CDSERVICO IN ('41101200','41101219','41101189')) THEN
IF (CDSERVICO = '41101251' AND NOT CDSERVICO IN ('41101260','41101286','41101294'))
OR (CDSERVICO = '41101260' AND NOT CDSERVICO IN ('41101251','41101286','41101294'))
OR (CDSERVICO = '41101286' AND NOT CDSERVICO IN ('41101260','41101251','41101294'))
OR (CDSERVICO = '41101294' AND NOT CDSERVICO IN ('41101260','41101286','41101251')) THEN
IF (CDSERVICO = '30912059' AND CDSERVICO <> '30912067') OR (CDSERVICO = '30912067' AND CDSERVICO <> '30912059') THEN
IF (CDSERVICO = '30101492' AND CDSERVICO <> '30210119') OR (CDSERVICO = '30210119' AND CDSERVICO <> '30101492') THEN
IF (CDSERVICO = '31004113' AND CDSERVICO <> '31004130') OR (CDSERVICO = '31004130' AND CDSERVICO <> '31004113') THEN
IF (CDSERVICO = '30214041' AND CDSERVICO <> '30214050') OR (CDSERVICO = '30214050' AND CDSERVICO <> '30214041') THEN
IF (CDSERVICO = '31309020' AND CDSERVICO <> '31309062') OR (CDSERVICO = '31309062' AND CDSERVICO <> '31309020') THEN
IF (CDSERVICO = '20104081' AND CDSERVICO <> '20104103') OR (CDSERVICO = '20104103' AND CDSERVICO <> '20104081') THEN
IF (CDSERVICO = '40103749' AND CDSERVICO <> '41401492') OR (CDSERVICO = '41401492' AND CDSERVICO <> '40103749') THEN
IF (CDSERVICO = '40201210' AND CDSERVICO <> '40201228') OR (CDSERVICO = '40201228' AND CDSERVICO <> '40201210') THEN
IF (CDSERVICO = '40201198' AND CDSERVICO <> '40201201') OR (CDSERVICO = '40201201' AND CDSERVICO <> '40201198') THEN
IF (CDSERVICO = '40201252' AND CDSERVICO <> '40201260') OR (CDSERVICO = '40201260' AND CDSERVICO <> '40201252') THEN
IF (CDSERVICO = '40201236' AND CDSERVICO <> '40201244') OR (CDSERVICO = '40201244' AND CDSERVICO <> '40201236') THEN
IF (CDSERVICO = '30205085' AND CDSERVICO <> '30403014') OR (CDSERVICO = '30403014' AND CDSERVICO <> '30205085') THEN
IF (CDSERVICO = '30201063' AND CDSERVICO <> '30203015') OR (CDSERVICO = '30203015' AND CDSERVICO <> '30201063') THEN
IF (CDSERVICO = '30724066' AND CDSERVICO <> '30724279') OR (CDSERVICO = '30724279' AND CDSERVICO <> '30724066') THEN
IF (CDSERVICO = '30724040' AND CDSERVICO <> '30724139') OR (CDSERVICO = '30724139' AND CDSERVICO <> '30724040') THEN
IF (CDSERVICO = '30725020' AND CDSERVICO <> '30725054') OR (CDSERVICO = '30725054' AND CDSERVICO <> '30725020') THEN
IF (CDSERVICO = '30725011' AND CDSERVICO <> '30725135') OR (CDSERVICO = '30725135' AND CDSERVICO <> '30725011') THEN
IF (CDSERVICO = '30101220' AND CDSERVICO <> '30101239') OR (CDSERVICO = '30101239' AND CDSERVICO <> '30101220') THEN
IF (CDSERVICO = '30601037' AND CDSERVICO <> '30601045') OR (CDSERVICO = '30601045' AND CDSERVICO <> '30601037') THEN
IF (CDSERVICO = '30601096' AND CDSERVICO <> '30601150') OR (CDSERVICO = '30601150' AND CDSERVICO <> '30601096') THEN
IF (CDSERVICO = '31103235' AND CDSERVICO <> '31103472') OR (CDSERVICO = '31103472' AND CDSERVICO <> '31103235') THEN
Raise_Application_Error(-20101,'Guia Liberada !!!');
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
CLOSE cPROCURA;
EXCEPTION WHEN OTHERS THEN
IF cPROCURA%ISOPEN THEN
RAISE_APPLICATION_ERROR(-20101,'Guia Liberada !!!');
CLOSE cPROCURA;
END IF;
RETURN(AcdErro = ('V049')); -- Retorna Erro de Guia Recusada !!!
END;
END FC_TUSS_EMISSAO_SERVICO_GUIA;Por gentileza, estou elaborando uma FUNCTION para verificar se um determinado guia pode passar. Mas, está ocorrendo o erro PLS-00103
)