CREATE OR REPLACE PROCEDURE FCT_COPIA_FORM_PRECO_ITPROP (
 P_NRFORMPRECOSERVICO IN NUMBER,
 P_NRORG IN NUMBER,
 P_OPERADOR IN VARCHAR2,
 P_NRSEQORCAMENTO IN FCT_ORCAMENTO.NRSEQORCAMENTO%TYPE,
 P_NRITEMORCAMENTO IN FCT_ITEMORCAMENTO.NRITEMORCAMENTO%TYPE,
 P_NRCALCFORMPRECSERV IN VARCHAR2 DEFAULT NULL,
 P_IDFORMACAO IN NUMBER DEFAULT 0)
AS

V_NRCALCFORMPRECSERV FCT_CALCFORMPRECSERV.NRCALCFORMPRECSERV%TYPE;
V_IDITEMCALCULOPAI VARCHAR2(20);
V_REFERENCIA VARCHAR2(500);
V_PAI NUMBER;

BEGIN
    FOR C_CALC IN (SELECT NRCALCFORMPRECSERV, NRFORMPRECOSERVICO, IDORDENACAO, IDITEMCALCULO, IDELEMENTO,
                           NRCALCFORMPRECSERVPAI, DSREFECALCULO, CDHABILITSERV, IDTIPOCALCULO,
                           DSEXPRESSAOCALC, VRPERCCALCULO, VRUNITARIO, QTCALCULO, IDUSAMEDIDA,
                           CDTPMEDIDASERV, QTMEDIDACALC, VRTOTAL
                      FROM FCT_CALCFORMPRECSERV
                      WHERE NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
                       AND (P_NRCALCFORMPRECSERV IS NULL OR
                           (NRCALCFORMPRECSERV IN(SELECT NRCALCFORMPRECSERV
                                                  FROM FCT_CALCFORMPRECSERV
                                                  WHERE NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
                                                  CONNECT BY PRIOR NRCALCFORMPRECSERV = NRCALCFORMPRECSERVPAI
                                                    START WITH INSTR(P_NRCALCFORMPRECSERV, '_'||TO_CHAR(NRCALCFORMPRECSERV)||'_') > 0)))
                       AND NRORG = P_NRORG
                       ORDER BY IDORDENACAO) LOOP
            BEGIN
                SELECT IDITEMCALCULO
                  INTO V_IDITEMCALCULOPAI
                FROM FCT_CALCFORMPRECSERV
                WHERE NRCALCFORMPRECSERV = C_CALC.NRCALCFORMPRECSERVPAI
                   AND NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
                   AND NRORG = P_NRORG;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                  V_IDITEMCALCULOPAI := NULL;
            END;

            IF V_IDITEMCALCULOPAI IS NOT NULL THEN
                V_REFERENCIA := C_CALC.DSREFECALCULO||'_PAI_'||V_IDITEMCALCULOPAI;
            ELSE
                V_REFERENCIA := C_CALC.DSREFECALCULO;
            END IF;

            NOVO_CODIGO('FCT_CALCFORMPRECSERV_ITPRO', V_NRCALCFORMPRECSERV, 1, P_NRORG);
            INSERT INTO FCT_CALCFORMPRECSERV_ITPRO (NRCALCFORMPRECSERV, NRFORMPRECOSERVICO, IDORDENACAO, IDITEMCALCULO,
                                                    IDELEMENTO, NRCALCFORMPRECSERVPAI, DSREFECALCULO, CDHABILITSERV,
                                                    IDTIPOCALCULO, DSEXPRESSAOCALC, VRPERCCALCULO, VRUNITARIO, QTCALCULO,
                                                    IDUSAMEDIDA, CDTPMEDIDASERV, QTMEDIDACALC, VRTOTAL, NRORG, DTINCLUSAO,
                                                    NRORGINCLUSAO, CDOPERINCLUSAO, IDATIVO, NRSEQORCAMENTO, NRITEMORCAMENTO,
                                                    NRRELACFORMACAOPROP)
            VALUES (V_NRCALCFORMPRECSERV, P_NRFORMPRECOSERVICO, C_CALC.IDORDENACAO, C_CALC.IDITEMCALCULO,
                    C_CALC.IDELEMENTO, C_CALC.NRCALCFORMPRECSERVPAI, V_REFERENCIA, C_CALC.CDHABILITSERV,
                    C_CALC.IDTIPOCALCULO, C_CALC.DSEXPRESSAOCALC, C_CALC.VRPERCCALCULO, C_CALC.VRUNITARIO,
                    C_CALC.QTCALCULO,  C_CALC.IDUSAMEDIDA,  C_CALC.CDTPMEDIDASERV, C_CALC.QTMEDIDACALC,
                    C_CALC.VRTOTAL, P_NRORG, SYSDATE, P_NRORG, P_OPERADOR, 'S', P_NRSEQORCAMENTO, P_NRITEMORCAMENTO,
                    P_IDFORMACAO);
        END LOOP;

        FOR C_CALCPAI IN (SELECT NRCALCFORMPRECSERV, NRFORMPRECOSERVICO,
                                 SUBSTR(DSREFECALCULO, INSTR(DSREFECALCULO, '_PAI_')+5) AS IDPAI,
                                 SUBSTR(DSREFECALCULO, 0, INSTR(DSREFECALCULO, '_PAI_')-1) AS REFERENCIA
                              FROM FCT_CALCFORMPRECSERV_ITPRO
                             WHERE NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
                               AND NRORG = P_NRORG
                               AND (NRSEQORCAMENTO = P_NRSEQORCAMENTO OR (P_NRSEQORCAMENTO IS NULL AND NRSEQORCAMENTO IS NULL))
                               AND (NRITEMORCAMENTO IS NULL OR NRITEMORCAMENTO = P_NRITEMORCAMENTO)
                               AND (P_IDFORMACAO IS NULL OR NRRELACFORMACAOPROP = P_IDFORMACAO)
                               AND NRCALCFORMPRECSERVPAI IS NOT NULL
                               ORDER BY IDORDENACAO) LOOP

            SELECT NRCALCFORMPRECSERV
              INTO V_PAI
              FROM FCT_CALCFORMPRECSERV_ITPRO
             WHERE IDITEMCALCULO = C_CALCPAI.IDPAI
               AND (NRSEQORCAMENTO = P_NRSEQORCAMENTO
                    OR (P_NRSEQORCAMENTO IS NULL AND NRSEQORCAMENTO IS NULL))
               AND (NRITEMORCAMENTO IS NULL
                        OR NRITEMORCAMENTO = P_NRITEMORCAMENTO)
               AND (P_IDFORMACAO IS NULL OR NRRELACFORMACAOPROP = P_IDFORMACAO)
               AND NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
               AND NRORG = P_NRORG;

            UPDATE FCT_CALCFORMPRECSERV_ITPRO
               SET NRCALCFORMPRECSERVPAI  =  V_PAI,
                   DSREFECALCULO = C_CALCPAI.REFERENCIA
             WHERE NRCALCFORMPRECSERV = C_CALCPAI.NRCALCFORMPRECSERV
               AND NRFORMPRECOSERVICO = P_NRFORMPRECOSERVICO
               AND NRORG = P_NRORG;
        END LOOP;
    COMMIT;
END;