create or replace PROCEDURE TRABRELCARDPER_PME(
    P_NRSEQTRABCARD IN VARCHAR2,
    P_CDFILCENTPME IN VARCHAR2,
    P_CDFILIAL IN VARCHAR2, -- RECEBE UNIDADE AGREGADA, SENAO CENTRAL
    P_CDTIPOCARD IN VARCHAR2,
    P_NRCARDPME IN VARCHAR2,
    P_DTINICIAL IN DATE,
    P_DTFINAL IN DATE,
    P_CDSERVFILI IN VARCHAR2, -- SERVICOS CONCATENADOS MESMO SE REPETIDOS 10001_10001_10002_10006_10006
    P_CDTIPOPRAT IN VARCHAR2 -- CONCATENADOS COM A ORDENACAO (CDTIPOPRAT:NRORDCNFCPME) E SEPARANDO POR SERVICO UTILIZANDO |  (15:1_15:2_20:3_25:4_|01:5_15:6_20:7_25:8_|05:9_)
) IS

    V_ROW NUMBER;
    V_NRSEQ NUMBER;
    V_CONT NUMBER;
    V_NRPAGINA NUMBER;
    V_NOVO VARCHAR2(1);
    
    V_CDFILIAL VARCHAR2(4);
    V_CDSERVFILI VARCHAR2(5);
    V_DTCARDDIAPME DATE;
    
    V_TP_1 VARCHAR2(2);
    V_TP_2 VARCHAR2(2);
    V_TP_3 VARCHAR2(2);
    V_TP_4 VARCHAR2(2);
    
    V_NRSEQ_1 VARCHAR2(2);
    V_NRSEQ_2 VARCHAR2(2);
    V_NRSEQ_3 VARCHAR2(2);
    V_NRSEQ_4 VARCHAR2(2);
    
    V_LAST_NRSEQ VARCHAR2(2);
    
BEGIN

    V_ROW := 1;
    V_NRSEQ := 0;
    V_CONT := 0;
    V_NRPAGINA := 1;
    V_NOVO := 'S';
    
    V_CDFILIAL := NULL;
    V_CDSERVFILI := NULL;
    V_DTCARDDIAPME := NULL;
    
    FOR C_SERVICOS IN (
        SELECT L.CDSERVFILI, ROWNUM AS NUM_SERV
          FROM ( SELECT REGEXP_SUBSTR(P_CDSERVFILI,'[^_]+', 1, LEVEL) AS CDSERVFILI
                   FROM DUAL 
                CONNECT BY REGEXP_SUBSTR(P_CDSERVFILI,'[^_]+', 1, LEVEL) IS NOT NULL
               ) L
         INNER JOIN SERVICO ON SERVICO.IDTIPOSERV = 'M'
                           AND SERVICO.CDFILIAL = P_CDFILIAL
                           AND SERVICO.CDSERVFILI = L.CDSERVFILI
    ) LOOP
                V_NRSEQ := V_NRSEQ + 1; 
        
        V_TP_1 := NULL;
        V_TP_2 := NULL;
        V_TP_3 := NULL;
        V_TP_4 := NULL;
                              
        V_CONT := V_CONT + 1; 
        IF ( V_CONT > 5 ) THEN 
            V_NRPAGINA := V_NRPAGINA + 1; 
            V_CONT := 0;
            V_NRSEQ := 1;
        END IF;

        
        FOR C_TIPOPRAT IN (
            SELECT CDTIPOPRAT, NRSEQCNFCPME
              FROM (SELECT CDTIPOPRAT, NRORDCNFCPME, NRSEQCNFCPME
                      FROM CONFCARDPME 
                     WHERE CDFILCENTPME = P_CDFILCENTPME 
                       AND NRCARDPME = P_NRCARDPME 
                       AND CDSERVFILI = C_SERVICOS.CDSERVFILI
                       AND INSTR(REGEXP_SUBSTR(P_CDTIPOPRAT,'[^ | ]+',1,C_SERVICOS.NUM_SERV), (CDTIPOPRAT || ':' || NRORDCNFCPME)) > 0
                   ) A 
             ORDER BY NRORDCNFCPME
        ) LOOP
            IF (V_ROW = 1) THEN 
                V_TP_1 := C_TIPOPRAT.CDTIPOPRAT;
                V_NRSEQ_1 := C_TIPOPRAT.NRSEQCNFCPME;
            ELSIF (V_ROW = 2) THEN 
                V_TP_2 := C_TIPOPRAT.CDTIPOPRAT;
                V_NRSEQ_2 := C_TIPOPRAT.NRSEQCNFCPME;
            ELSIF (V_ROW = 3) THEN
                V_TP_3 := C_TIPOPRAT.CDTIPOPRAT;
                V_NRSEQ_3 := C_TIPOPRAT.NRSEQCNFCPME;
            ELSE 
                V_TP_4 := C_TIPOPRAT.CDTIPOPRAT;
                V_NRSEQ_4 := C_TIPOPRAT.NRSEQCNFCPME;
            END IF;
            
            V_ROW := V_ROW + 1;
        END LOOP;
        
        V_ROW := 1;
        
        FOR C_PRATOS IN (
                SELECT CP.CDFILCENTPME, CP.CDFILIAL, CP.DTCARDIAPME, CP.IDCARDESPEC, 
                   CP.CDSERVFILI, SUBSTR(CP.NMTIPOPRAT, 0, 15) AS NMTIPOPRAT,
                   CP.CDTIPOPRAT, CP.CDPRATO, CP.CDFILAUXPRAT, CP.DTINICARDPME,
                   CP.DTFINCARDPME, CP.NRORDCNFCPME, SUBSTR( DECODE(CP.IDCARDESPEC, 'S', 'CARDESPEC' || NVL(PA.NMFANT, CP.NMPRATO), NVL(PA.NMFANT, CP.NMPRATO)), 0, 40) AS NMPRATO,
                   CP.NRSEQCNFCPME, CP.IDOPCCNFCPME
              FROM (
                SELECT CP.CDFILCENTPME, CP.CDFILIAL, CP.DTCARDIAPME, CP.CDSERVFILI, TP.NMTIPOPRAT,
                           TP.CDTIPOPRAT, PR.CDPRATO, C.DTINICARDPME,
                           C.DTFINCARDPME, NRORDCNFCPME, PR.CDFILAUXPRAT,
                           PR.NMPRATO AS NMPRATO, CF.NRSEQCNFCPME, CF.IDOPCCNFCPME, CP.IDCARDESPEC
                      FROM CARDAPIOPME C
                     INNER JOIN (SELECT CDFILCENTPME, NRCARDPME, NRCARDIAPME, CDFILIAL, DTCARDIAPME, CDSERVFILI, QTCOMESTCPME, 
                                   QTCOMEFECPME, IDCARDESPEC
                              FROM CARDIAPME P
                             WHERE (P.CDFILCENTPME = P_CDFILCENTPME) AND
                                   (P.NRCARDPME = P_NRCARDPME ) AND
                                   (P.CDFILIAL = P.CDFILCENTPME) AND                          
                                   (P.DTCARDIAPME BETWEEN P_DTINICIAL AND P_DTFINAL) AND
                                   (CDSERVFILI = C_SERVICOS.CDSERVFILI ) AND
                                   NOT EXISTS(SELECT CDFILIAL
                                                FROM  CARDIAPME C
                                               WHERE (C.CDFILCENTPME = P.CDFILCENTPME) AND      
                                                     (C.NRCARDPME    = P.NRCARDPME) AND
                                                     (C.CDSERVFILI   = P.CDSERVFILI)  AND
                                                     (C.CDFILIAL     = P_CDFILIAL ) AND
                                                     (C.DTCARDIAPME  = P.DTCARDIAPME) AND
                                                     (C.IDCARDESPEC  = 'S') )
                             UNION ALL
                            SELECT CDFILCENTPME, NRCARDPME, NRCARDIAPME, CDFILIAL,DTCARDIAPME, CDSERVFILI, QTCOMESTCPME, QTCOMEFECPME, IDCARDESPEC
                              FROM CARDIAPME
                             WHERE (CDFILCENTPME = P_CDFILCENTPME  ) AND
                                   (NRCARDPME    = P_NRCARDPME  )    AND
                                   (CDFILIAL     = P_CDFILIAL  )     AND
                                   (DTCARDIAPME BETWEEN P_DTINICIAL  AND P_DTFINAL ) AND
                                   (CDSERVFILI = C_SERVICOS.CDSERVFILI ) AND
                                   (IDCARDESPEC  = 'S')) CP ON CP.CDSERVFILI  = C_SERVICOS.CDSERVFILI AND C.CDFILCENTPME = CP.CDFILCENTPME AND C.NRCARDPME = CP.NRCARDPME 
                     INNER JOIN CONFCARDPME CF ON CP.CDFILCENTPME = CF.CDFILCENTPME
                                                    AND CP.CDSERVFILI   = CF.CDSERVFILI
                                                    AND CP.NRCARDPME    = CF.NRCARDPME
                                                    AND INSTR(REGEXP_SUBSTR(P_CDTIPOPRAT,'[^ | ]+',1,C_SERVICOS.NUM_SERV), (CF.CDTIPOPRAT || ':' || CF.NRORDCNFCPME)) > 0 
                     INNER JOIN TIPOPRAT TP ON CF.CDTIPOPRAT  = TP.CDTIPOPRAT
                     LEFT JOIN COMPCARDPME CC ON CP.NRCARDIAPME = CC.NRCARDIAPME
                        AND CP.CDFILCENTPME = CC.CDFILCENTPME
                        AND CP.NRCARDPME    = CC.NRCARDPME
                        AND CC.CDTIPOPRAT   = CF.CDTIPOPRAT
                        AND CC.NRSEQPRACPME = CF.NRSEQCNFCPME
                        AND CC.NRORDPRACPME = CF.NRORDCNFCPME         
                     LEFT JOIN PRATO PR ON CC.CDPRATO = PR.CDPRATO AND CC.CDFILAUXPRAT = PR.CDFILAUXPRAT 
                      WHERE C.CDTIPOCARD   = P_CDTIPOCARD
                        AND C.CDFILCENTPME = P_CDFILCENTPME
                        AND C.DTINICARDPME = P_DTINICIAL
                        AND C.DTFINCARDPME = P_DTFINAL
                )CP
              LEFT JOIN (SELECT NMFANTASIASE AS NMFANT, CDPRATO, CDFILAUXPRAT, CDFILIAL, CDSERVFILI
                           FROM PRATSERV
                          WHERE CDFILIAL = P_CDFILIAL
                            AND CDSERVFILI = C_SERVICOS.CDSERVFILI
                            AND NMFANTASIASE IS NOT NULL
                          UNION ALL
                         SELECT P1.NMFANTASIAPF AS NMFANT, P1.CDPRATO, P1.CDFILAUXPRAT, P1.CDFILIAL, C_SERVICOS.CDSERVFILI AS CDSERVFILI
                           FROM PRATFILI P1
                          WHERE P1.CDFILIAL = P_CDFILIAL
                            AND P1.NMFANTASIAPF IS NOT NULL
                          UNION ALL
                         SELECT P2.NMFANTASIAPR AS NMFANT, P2.CDPRATO, P2.CDFILAUXPRAT, P_CDFILIAL AS CDFILIAL, C_SERVICOS.CDSERVFILI AS CDSERVFILI
                           FROM PRATPADR P2
                          WHERE P2.NMFANTASIAPR IS NOT NULL
                          UNION ALL
                         SELECT P3.NMFANTASIAPRTP AS NMFANT, P3.CDPRATO, P3.CDFILAUXPRAT, P_CDFILIAL AS CDFILIAL, C_SERVICOS.CDSERVFILI AS CDSERVFILI
                           FROM PRATTPCARD P3
                          WHERE P3.CDFILCENTPME = P_CDFILCENTPME
                            AND P3.CDTIPOCARD   = P_CDTIPOCARD
                            AND P3.NMFANTASIAPRTP IS NOT NULL
                   ) PA ON PA.CDPRATO = CP.CDPRATO
                       AND PA.CDFILAUXPRAT = CP.CDFILAUXPRAT
                     ORDER BY CP.DTCARDIAPME, CP.NRORDCNFCPME
        ) LOOP
            IF ((V_CDFILIAL IS NULL OR V_CDFILIAL <> C_PRATOS.CDFILIAL) OR
                 (V_CDSERVFILI IS NULL OR V_CDSERVFILI <> C_PRATOS.CDSERVFILI) OR
                 (V_DTCARDDIAPME IS NULL OR V_DTCARDDIAPME <> C_PRATOS.DTCARDIAPME) OR 
                 (V_LAST_NRSEQ IS NULL OR V_LAST_NRSEQ <> V_NRSEQ)
            ) THEN
                V_NOVO := 'S'; 
                                                    
                V_CDFILIAL := C_PRATOS.CDFILIAL;
                V_CDSERVFILI := C_PRATOS.CDSERVFILI;
                V_DTCARDDIAPME := C_PRATOS.DTCARDIAPME;
                V_LAST_NRSEQ := V_NRSEQ;
            END IF;
            
            IF ( V_NOVO = 'S' ) THEN
                V_NOVO := 'N'; 
            
                IF (C_PRATOS.CDTIPOPRAT = V_TP_1 AND C_PRATOS.NRSEQCNFCPME = V_NRSEQ_1) THEN  
                     INSERT INTO TRABCARDMES(NRSEQTRABCARD, NRSEQUITEMTRAB, CDFILIAL, CDSERVFILI, NRSEQCARDMES, NRORDCARDMES,
                                               DTCARDAPIO, DTMESCARD, IDOPCCNFMES1, CDTIPOPRAT1, NRPAGINA,
                                               CDPRATO1, CDFILAUXPRAT1, NMTIPOPRAT1, NMPRATO1, DTINCLUSAO)
                        VALUES(P_NRSEQTRABCARD, V_NRSEQ, C_PRATOS.CDFILIAL, C_PRATOS.CDSERVFILI, C_PRATOS.NRSEQCNFCPME, C_PRATOS.NRORDCNFCPME,
                              C_PRATOS.DTCARDIAPME, C_PRATOS.DTINICARDPME, C_PRATOS.IDOPCCNFCPME, C_PRATOS.CDTIPOPRAT, V_NRPAGINA,
                              C_PRATOS.CDPRATO, C_PRATOS.CDFILAUXPRAT, C_PRATOS.NMTIPOPRAT, C_PRATOS.NMPRATO, SYSDATE);
                ELSIF (C_PRATOS.CDTIPOPRAT = V_TP_2 AND C_PRATOS.NRSEQCNFCPME = V_NRSEQ_2) THEN  
                     INSERT INTO TRABCARDMES(NRSEQTRABCARD, NRSEQUITEMTRAB, CDFILIAL, CDSERVFILI, NRSEQCARDMES, NRORDCARDMES,
                                               DTCARDAPIO, DTMESCARD, IDOPCCNFMES2, CDTIPOPRAT2, NRPAGINA,
                                               CDPRATO2, CDFILAUXPRAT2, NMTIPOPRAT2, NMPRATO2, DTINCLUSAO)
                        VALUES(P_NRSEQTRABCARD, V_NRSEQ, C_PRATOS.CDFILIAL, C_PRATOS.CDSERVFILI, C_PRATOS.NRSEQCNFCPME, C_PRATOS.NRORDCNFCPME,
                              C_PRATOS.DTCARDIAPME, C_PRATOS.DTINICARDPME, C_PRATOS.IDOPCCNFCPME, C_PRATOS.CDTIPOPRAT, V_NRPAGINA,
                              C_PRATOS.CDPRATO, C_PRATOS.CDFILAUXPRAT, C_PRATOS.NMTIPOPRAT, C_PRATOS.NMPRATO, SYSDATE);
                ELSIF (C_PRATOS.CDTIPOPRAT = V_TP_3 AND C_PRATOS.NRSEQCNFCPME = V_NRSEQ_3) THEN  
                     INSERT INTO TRABCARDMES(NRSEQTRABCARD, NRSEQUITEMTRAB, CDFILIAL, CDSERVFILI, NRSEQCARDMES, NRORDCARDMES,
                                               DTCARDAPIO, DTMESCARD, IDOPCCNFMES3, CDTIPOPRAT3, NRPAGINA,
                                               CDPRATO3, CDFILAUXPRAT3, NMTIPOPRAT3, NMPRATO3, DTINCLUSAO)
                        VALUES(P_NRSEQTRABCARD, V_NRSEQ, C_PRATOS.CDFILIAL, C_PRATOS.CDSERVFILI, C_PRATOS.NRSEQCNFCPME, C_PRATOS.NRORDCNFCPME,
                              C_PRATOS.DTCARDIAPME, C_PRATOS.DTINICARDPME, C_PRATOS.IDOPCCNFCPME, C_PRATOS.CDTIPOPRAT, V_NRPAGINA,
                              C_PRATOS.CDPRATO, C_PRATOS.CDFILAUXPRAT, C_PRATOS.NMTIPOPRAT, C_PRATOS.NMPRATO, SYSDATE);
                ELSE  
                     INSERT INTO TRABCARDMES(NRSEQTRABCARD, NRSEQUITEMTRAB, CDFILIAL, CDSERVFILI, NRSEQCARDMES, NRORDCARDMES,
                                               DTCARDAPIO, DTMESCARD, IDOPCCNFMES4, CDTIPOPRAT4, NRPAGINA,
                                               CDPRATO4, CDFILAUXPRAT4, NMTIPOPRAT4, NMPRATO4, DTINCLUSAO)
                        VALUES(P_NRSEQTRABCARD, V_NRSEQ, C_PRATOS.CDFILIAL, C_PRATOS.CDSERVFILI, C_PRATOS.NRSEQCNFCPME, C_PRATOS.NRORDCNFCPME,
                              C_PRATOS.DTCARDIAPME, C_PRATOS.DTINICARDPME, C_PRATOS.IDOPCCNFCPME, C_PRATOS.CDTIPOPRAT, V_NRPAGINA,
                              C_PRATOS.CDPRATO, C_PRATOS.CDFILAUXPRAT, C_PRATOS.NMTIPOPRAT, C_PRATOS.NMPRATO, SYSDATE);
                END IF;              
            ELSE 
                
                UPDATE TRABCARDMES
                   SET IDOPCCNFMES1  = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.IDOPCCNFCPME, IDOPCCNFMES1), IDOPCCNFMES1),
                       CDTIPOPRAT1   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.CDTIPOPRAT, CDTIPOPRAT1), CDTIPOPRAT1),
                       CDPRATO1      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.CDPRATO, CDPRATO1), CDPRATO1),
                       CDFILAUXPRAT1 = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.CDFILAUXPRAT, CDFILAUXPRAT1), CDFILAUXPRAT1),
                       NMTIPOPRAT1   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.NMTIPOPRAT, NMTIPOPRAT1), NMTIPOPRAT1),
                       NMPRATO1      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_1, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_1, C_PRATOS.NMPRATO, NMPRATO1), NMPRATO1),
                       IDOPCCNFMES2  = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.IDOPCCNFCPME, IDOPCCNFMES2), IDOPCCNFMES2),
                       CDTIPOPRAT2   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.CDTIPOPRAT, CDTIPOPRAT2), CDTIPOPRAT2),
                       CDPRATO2      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.CDPRATO, CDPRATO2), CDPRATO2),
                       CDFILAUXPRAT2 = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.CDFILAUXPRAT, CDFILAUXPRAT2), CDFILAUXPRAT2),
                       NMTIPOPRAT2   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.NMTIPOPRAT, NMTIPOPRAT2), NMTIPOPRAT2),
                       NMPRATO2      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_2, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_2, C_PRATOS.NMPRATO, NMPRATO2), NMPRATO2),
                       IDOPCCNFMES3  = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.IDOPCCNFCPME, IDOPCCNFMES3), IDOPCCNFMES3),
                       CDTIPOPRAT3   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.CDTIPOPRAT, CDTIPOPRAT3), CDTIPOPRAT3),
                       CDPRATO3      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.CDPRATO, CDPRATO3), CDPRATO3),
                       CDFILAUXPRAT3 = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.CDFILAUXPRAT, CDFILAUXPRAT3), CDFILAUXPRAT3),
                       NMTIPOPRAT3   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.NMTIPOPRAT, NMTIPOPRAT3), NMTIPOPRAT3),
                       NMPRATO3      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_3, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_3, C_PRATOS.NMPRATO, NMPRATO3), NMPRATO3),
                       IDOPCCNFMES4  = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.IDOPCCNFCPME, IDOPCCNFMES4), IDOPCCNFMES4),
                       CDTIPOPRAT4   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.CDTIPOPRAT, CDTIPOPRAT4), CDTIPOPRAT4),
                       CDPRATO4      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.CDPRATO, CDPRATO4), CDPRATO4),
                       CDFILAUXPRAT4 = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.CDFILAUXPRAT, CDFILAUXPRAT4), CDFILAUXPRAT4),
                       NMTIPOPRAT4   = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.NMTIPOPRAT, NMTIPOPRAT4), NMTIPOPRAT4),
                       NMPRATO4      = DECODE(C_PRATOS.NRSEQCNFCPME, V_NRSEQ_4, DECODE(C_PRATOS.CDTIPOPRAT, V_TP_4, C_PRATOS.NMPRATO, NMPRATO4), NMPRATO4)
                   WHERE CDFILIAL = C_PRATOS.CDFILIAL
                     AND CDSERVFILI = C_PRATOS.CDSERVFILI
                     AND DTCARDAPIO = C_PRATOS.DTCARDIAPME
                     AND NRSEQTRABCARD = P_NRSEQTRABCARD
                     AND NRSEQUITEMTRAB = V_NRSEQ
                     AND NRPAGINA = V_NRPAGINA;                     
            END IF;  
        END LOOP;
    END LOOP;

   COMMIT;
END;
/