CREATE OR REPLACE PROCEDURE CRIA_MNEMONICO (
  P_NRORG              IN NUMBER, 
  P_IDORCACONTR        IN VARCHAR2,
  P_NRSEQ              IN NUMBER,
  P_MNEMONICO          OUT VARCHAR2)
AS
  V_CDSEQORCAMENTO_NEW VARCHAR2(20);
  V_CDSEQORCAMENTO     VARCHAR2(20);
  V_CDMINEMORCA_NEW    VARCHAR2(50);
  V_CDMINEMORCA        VARCHAR2(50);
  V_DTREFORCAMENTO     FCT_ORCAMENTO.DTREFORCAMENTO%TYPE;
  V_NRVERSAOORCAMENTO  FCT_ORCAMENTO.NRVERSAOORCAMENTO%TYPE;
  V_NRSEQORCAMENTOPAI  FCT_ORCAMENTO.NRSEQORCAMENTOPAI%TYPE;
  V_CDFILIAL           FCT_ORCAMENTO.CDFILIAL%TYPE;
  V_ANO                VARCHAR2(4);
  V_FILIAL             VARCHAR2(4);
BEGIN
  IF P_IDORCACONTR = 'O' THEN /* ORCAMENTO */
    
    SELECT MO.CDMINEMORCA, O.DTREFORCAMENTO, O.NRVERSAOORCAMENTO, O.NRSEQORCAMENTOPAI, O.CDFILIAL, O.CDSEQORCAMENTO
      INTO V_CDMINEMORCA, V_DTREFORCAMENTO, V_NRVERSAOORCAMENTO, V_NRSEQORCAMENTOPAI, V_CDFILIAL, V_CDSEQORCAMENTO
      FROM FCT_ORCAMENTO O
      LEFT JOIN FCT_ORCAMENTOMODELO MO
        ON O.NRORCAMENTOMODELO = MO.NRORCAMENTOMODELO
       AND O.NRORG             = MO.NRORG
     WHERE O.NRSEQORCAMENTO = P_NRSEQ
       AND O.NRORG          = P_NRORG;
    
    IF V_CDMINEMORCA IS NULL THEN
      V_CDMINEMORCA := 'ORC[ANO][SEQ][VER]';
    END IF;

      V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA,'[ANO]',TO_CHAR(V_DTREFORCAMENTO,'YYYY'));
      V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[MES]',TO_CHAR(V_DTREFORCAMENTO,'MM'));
      V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[VER]',LPAD(TO_CHAR(V_NRVERSAOORCAMENTO),'2','0'));
      V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[FIL]',V_CDFILIAL);

      IF ((V_NRSEQORCAMENTOPAI IS NULL) AND (V_NRVERSAOORCAMENTO = 1)) THEN

        SELECT DECODE(INSTR('[ANO]',V_CDMINEMORCA),0,NULL,TO_CHAR(V_DTREFORCAMENTO,'YYYY')),
               DECODE(INSTR('[FIL]',V_CDMINEMORCA),0,NULL,V_CDFILIAL)
          INTO V_ANO, V_FILIAL
          FROM DUAL;

         NOVO_CODIGO_NT('FCT_ORCAMENTOCD'||V_ANO||V_FILIAL,V_CDSEQORCAMENTO_NEW,1, P_NRORG);

         V_CDMINEMORCA_NEW :=  REPLACE(V_CDMINEMORCA_NEW,'[SEQ]',SUBSTR(V_CDSEQORCAMENTO_NEW,15,20));

         UPDATE FCT_ORCAMENTO
            SET CDSEQORCAMENTO = SUBSTR(V_CDSEQORCAMENTO_NEW,15,20),
                NRORCAMENTO    = V_CDMINEMORCA_NEW
          WHERE NRSEQORCAMENTO = P_NRSEQ
            AND NRORG          = P_NRORG;
      ELSE
         FOR C_ORCA IN
           (SELECT O.NRSEQORCAMENTO, O.CDSEQORCAMENTO, O.NRORCAMENTO, O.NRVERSAOORCAMENTO
              FROM FCT_ORCAMENTO O
             WHERE O.NRSEQORCAMENTO = V_NRSEQORCAMENTOPAI
               AND O.NRORG          = P_NRORG) LOOP
               
           IF ((C_ORCA.CDSEQORCAMENTO = V_CDSEQORCAMENTO) AND (C_ORCA.NRVERSAOORCAMENTO = V_NRVERSAOORCAMENTO)) THEN
             
             SELECT DECODE(INSTR('[ANO]',V_CDMINEMORCA),0,NULL,TO_CHAR(V_DTREFORCAMENTO,'YYYY')),
                   DECODE(INSTR('[FIL]',V_CDMINEMORCA),0,NULL,V_CDFILIAL)
              INTO V_ANO, V_FILIAL
              FROM DUAL;
             
             NOVO_CODIGO_NT('FCT_ORCAMENTOCD'||V_ANO||V_FILIAL, V_CDSEQORCAMENTO_NEW, 1, P_NRORG);
             V_CDMINEMORCA_NEW :=  REPLACE(V_CDMINEMORCA_NEW,'[SEQ]',SUBSTR(V_CDSEQORCAMENTO_NEW,15,20));
             
           ELSE
             V_CDMINEMORCA_NEW :=  REPLACE(V_CDMINEMORCA_NEW,'[SEQ]',LPAD(TO_CHAR(C_ORCA.CDSEQORCAMENTO),6,0));
           END IF;

           UPDATE FCT_ORCAMENTO
              SET CDSEQORCAMENTO = C_ORCA.CDSEQORCAMENTO,
                  NRORCAMENTO    = V_CDMINEMORCA_NEW
            WHERE NRSEQORCAMENTO = P_NRSEQ
              AND NRORG          = P_NRORG;
              
         END LOOP;
      END IF;
        
  ELSE
    FOR C_CONTRATO IN (
        SELECT MD.CDMODCONTRATO, O.DTINICIOCONTRATO, O.NRVERSAOCONTRATO,
               O.NRSEQCONTRATOPAI, O.NRSEQCONTRATO, O.CDFILIALORCA AS CDFILIAL
          FROM FCT_CONTRATO O, FCT_MODELOCONTRATO MD
         WHERE O.NRORG             = P_NRORG
           AND O.NRSEQCONTRATO     = P_NRSEQ
           AND O.NRSEQMODCONTRA    = MD.NRSEQMODCONTRA(+)
           AND O.NRORG             = MD.NRORG) LOOP

        IF C_CONTRATO.CDMODCONTRATO IS NULL THEN
           V_CDMINEMORCA := 'CONT[ANO][SEQ][VER]';
        ELSE
           V_CDMINEMORCA := C_CONTRATO.CDMODCONTRATO;
        END IF;

        V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA,'[ANO]',TO_CHAR(C_CONTRATO.DTINICIOCONTRATO,'YYYY'));
        V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[MES]',TO_CHAR(C_CONTRATO.DTINICIOCONTRATO,'MM'));
        V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[VER]',LPAD(TO_CHAR(C_CONTRATO.NRVERSAOCONTRATO),'2','0'));
        V_CDMINEMORCA_NEW := REPLACE(V_CDMINEMORCA_NEW,'[FIL]',LPAD(TO_CHAR(C_CONTRATO.CDFILIAL),'2','0'));

        SELECT DECODE(INSTR('[ANO]',V_CDMINEMORCA),0,NULL,TO_CHAR(C_CONTRATO.DTINICIOCONTRATO,'YYYY')),
               DECODE(INSTR('[FIL]',V_CDMINEMORCA),0,NULL,C_CONTRATO.CDFILIAL)
          INTO V_ANO, V_FILIAL
          FROM DUAL;

        IF ((C_CONTRATO.NRSEQCONTRATOPAI IS NULL) AND (C_CONTRATO.NRVERSAOCONTRATO = 1)) THEN
            NOVO_CODIGO_NT('FCT_CONTRATOCD'||V_ANO||V_FILIAL,V_CDSEQORCAMENTO_NEW,1, P_NRORG);

            V_CDMINEMORCA_NEW :=  REPLACE(V_CDMINEMORCA_NEW,'[SEQ]',SUBSTR(V_CDSEQORCAMENTO_NEW,15,20));

           UPDATE FCT_CONTRATO
              SET CDSEQCONTRATO  = SUBSTR(V_CDSEQORCAMENTO_NEW,15,20),
                  NRCONTRATOFCT  = V_CDMINEMORCA_NEW
            WHERE NRSEQCONTRATO  = P_NRSEQ
              AND NRORG          = P_NRORG;
        ELSE
           FOR C_CONTR IN
             (SELECT O.NRSEQCONTRATO, O.CDSEQCONTRATO,O.NRCONTRATOFCT
                FROM FCT_CONTRATO O
               WHERE O.NRSEQCONTRATO  = C_CONTRATO.NRSEQCONTRATOPAI
                 AND O.NRORG          = P_NRORG) LOOP

              V_CDMINEMORCA_NEW :=  REPLACE(V_CDMINEMORCA_NEW,'[SEQ]',LPAD(TO_CHAR(C_CONTR.CDSEQCONTRATO),6,0));

              UPDATE FCT_CONTRATO
                 SET CDSEQCONTRATO  = C_CONTR.CDSEQCONTRATO,
                     NRCONTRATOFCT  = V_CDMINEMORCA_NEW
               WHERE NRSEQCONTRATO  = P_NRSEQ
                 AND NRORG          = P_NRORG;
           END LOOP;
        END IF;
    END LOOP;
  END IF;
  P_MNEMONICO := V_CDMINEMORCA_NEW;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CRIA_MNEMONICO','6.10.xxx'); END;
/