create or replace TYPE              "ZMART_DEMONSTRATIVO_CTB_TYPE" FORCE AS OBJECT
    (NRORG NUMBER,
    CDEMPRESA VARCHAR2(2 BYTE),
	DTMES DATE, /*COMPETENCIA*/
    CDTIPODEMO VARCHAR2(2 BYTE), /*TIPO DO DEMONSTRATIVO*/
    NMTIPODEMO VARCHAR2(64 BYTE), /*NOME DO DEMONSTRATIVO*/
    CDITEMSTRU VARCHAR2(4 BYTE), /*ITEM DE ESTRUTURA DO DEMONSTRATIVO*/
    NMITEMSTRU VARCHAR(84 BYTE), /*NOME DO ITEM DE ESTRUTURA*/
    IDORIGVALOR VARCHAR2(1 BYTE), /*T=TITULO, A=APURACAO,I=INFORMATIVO*/
	VRSALDOITEM NUMBER)
/
create or replace TYPE              "ZMART_DEMONSTRATIVO_CTB_TABLE" AS TABLE OF ZMART_DEMONSTRATIVO_CTB_TYPE
/
create or replace FUNCTION ZMART_DEMONSTRATIVO_CTBO(
    P_CDTIPODEMO IN ITEMDEMOF.CDTIPODEMO%TYPE, -- CODIGO DO DEMONSTRATIVO
    P_IDDESCENCER IN CHAR, -- DESCONSIDERA ENCERRAMENTO DE EXERC(N = CONSIDERA ENCERRAMENTO S = DESCONSIDERA ENCERRAMENTO)
    P_DATAINI IN DATE, -- DATA INICIAL
    P_DATAFIN IN DATE, -- DATA FINAL
    P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE DEFAULT NULL, -- CODIGO DA EMPRESA
    P_NRORG IN ORGANIZACAO.NRORG%TYPE DEFAULT NULL --NUMERO ORAGANIZACAO
    ) RETURN ZMART_DEMONSTRATIVO_CTB_TABLE PIPELINED
IS
    OUT_RECORD ZMART_DEMONSTRATIVO_CTB_TYPE := ZMART_DEMONSTRATIVO_CTB_TYPE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
    CURSOR C1 IS
        SELECT I.NRORG, L.CDEMPRESA, I.CDTIPODEMO, TP.NMTIPODEMO, I.CDITEMSTRU, IT.NMITEMSTRU, L.DTLANCCONT AS DTMES, L.VRLANCAMENT
        FROM ITEMAPUCTB I
        JOIN ITEMDEMOF IT
            ON I.CDITEMSTRU = IT.CDITEMSTRU
            AND I.CDTIPODEMO = IT.CDTIPODEMO
            AND I.NRORG = IT.NRORG
        JOIN TIPODEMO TP
            ON TP.NRORG = IT.NRORG
            AND TP.CDTIPODEMO = IT.CDTIPODEMO
        CROSS JOIN LANCCONT L
        WHERE ROWNUM = 1;
    TYPE ASSOC_ARRAY IS TABLE OF C1%ROWTYPE INDEX BY VARCHAR2(30);
    V_TABLE ASSOC_ARRAY;
    V_MESREFERENCIA DATE := TRUNC(P_DATAINI);
BEGIN
    /*IDTIPOOTIG(S=SALDO,V=REFERENCIA)*/
    IF P_CDEMPRESA IS NOT NULL THEN
        IF P_IDDESCENCER = 'N' THEN
                FOR C2 IN (SELECT NVL(S.NRORG, I.NRORG) AS NRORG, NVL(S.CDEMPRESA, P_CDEMPRESA) AS CDEMPRESA, NVL(S.DTMES, I.DTMES) AS DTMES,
                NVL(S.CDTIPODEMO, I.CDTIPODEMO) AS CDTIPODEMO, I.NMTIPODEMO, NVL(S.CDITEMSTRU, I.CDITEMSTRU) AS CDITEMSTRU, I.NMITEMSTRU,
                I.IDORIGVALOR,
                NVL(S.VRSALDOITEM, 0) AS VRSALDOITEM
                FROM (SELECT L.NRORG, L.CDEMPRESA, TRUNC(L.DTLANCCONT, 'MM') AS DTMES, C.CDTIPODEMO, C.CDITEMSTRU, NVL(SUM(ROUND(L.VRLANCAMENT, 2)),0) AS VRSALDOITEM
                            FROM LANCCONT L, (SELECT C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO, COUNT(C.CDCONTCTBL) AS CONT
                                            FROM CONTCTBL C, ITEMAPUCTB I
                                            WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO || '%'
                                            AND C.IDTIPOCONT = 'A'
                                            AND C.IDCONTCCUS <> 'R'
                                            AND I.CDTIPODEMO = P_CDTIPODEMO
                                            AND I.IDTIPOORIG = 'S'
                                            AND C.NRORG = I.NRORG
                                            GROUP BY C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO
                                            HAVING COUNT(C.CDCONTCTBL) < 2) C
                            WHERE TRUNC(L.DTLANCCONT) BETWEEN P_DATAINI AND P_DATAFIN
                            AND L.CDEMPRESA = P_CDEMPRESA
                            AND L.CDCONTCTBL = C.CDCONTCTBL
                            AND C.NRORG = L.NRORG
                            GROUP BY L.NRORG, L.CDEMPRESA, TRUNC(L.DTLANCCONT, 'MM'), C.CDTIPODEMO, C.CDITEMSTRU) S
                        RIGHT JOIN (SELECT ITEMDEMOF.NRORG, DT.DTMES, ITEMDEMOF.CDTIPODEMO,
                                            TIPODEMO.NMTIPODEMO, ITEMDEMOF.CDITEMSTRU, ITEMDEMOF.NMITEMSTRU, ITEMDEMOF.IDORIGVALOR
                                    FROM ITEMDEMOF
                                    JOIN TIPODEMO
                                        ON ITEMDEMOF.NRORG = TIPODEMO.NRORG
                                        AND ITEMDEMOF.CDTIPODEMO = TIPODEMO.CDTIPODEMO
                                        AND ITEMDEMOF.CDTIPODEMO = P_CDTIPODEMO
                                    JOIN (SELECT NRORG, DTMES
                                                FROM DATA
                                                WHERE DTDATA BETWEEN P_DATAINI AND P_DATAFIN
                                                GROUP BY NRORG, DTMES) DT
                                        ON DT.NRORG = ITEMDEMOF.NRORG) I
                            ON I.NRORG = S.NRORG
                            AND I.CDTIPODEMO = S.CDTIPODEMO
                            AND I.CDITEMSTRU = S.CDITEMSTRU
                            AND I.DTMES = S.DTMES
                        WHERE (I.NRORG = P_NRORG OR P_NRORG IS NULL)
                        ORDER BY NVL(S.NRORG, I.NRORG), NVL(S.DTMES, I.DTMES), NVL(S.CDEMPRESA, P_CDEMPRESA),
                                NVL(S.CDTIPODEMO, I.CDTIPODEMO), NVL(S.CDITEMSTRU, I.CDITEMSTRU)) LOOP
                IF V_MESREFERENCIA <> C2.DTMES THEN
                        FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
                        END LOOP;
                    FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                                    FROM ITEMDEMOF
                                    WHERE CDTIPODEMO = P_CDTIPODEMO
                                    ORDER BY CDITEMSTRU) LOOP
                        OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                        OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                        OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                        OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                        OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                        OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                        OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                        OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                        OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                        PIPE ROW(OUT_RECORD);
                    END LOOP;
                    V_MESREFERENCIA := C2.DTMES;
                    END IF;
                    V_TABLE(C2.CDITEMSTRU).NRORG := C2.NRORG;
                    V_TABLE(C2.CDITEMSTRU).CDEMPRESA := C2.CDEMPRESA;
                    V_TABLE(C2.CDITEMSTRU).CDTIPODEMO := C2.CDTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).NMTIPODEMO := C2.NMTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).CDITEMSTRU := C2.CDITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).NMITEMSTRU := C2.NMITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).DTMES := C2.DTMES;
                    V_TABLE(C2.CDITEMSTRU).VRLANCAMENT := C2.VRSALDOITEM;
            END LOOP;
            FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
            END LOOP;
            FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                            FROM ITEMDEMOF
                            WHERE CDTIPODEMO = P_CDTIPODEMO
                            ORDER BY CDITEMSTRU) LOOP
                OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                PIPE ROW(OUT_RECORD);
            END LOOP;
        ELSE
            FOR C2 IN (SELECT NVL(S.NRORG, I.NRORG) AS NRORG, NVL(S.CDEMPRESA, P_CDEMPRESA) AS CDEMPRESA, NVL(S.DTMES, I.DTMES) AS DTMES,
                NVL(S.CDTIPODEMO, I.CDTIPODEMO) AS CDTIPODEMO, I.NMTIPODEMO, NVL(S.CDITEMSTRU, I.CDITEMSTRU) AS CDITEMSTRU, I.NMITEMSTRU,
                I.IDORIGVALOR,
                NVL(S.VRSALDOITEM, 0) AS VRSALDOITEM
                FROM (SELECT L.NRORG, L.CDEMPRESA, TRUNC(L.DTLANCCONT, 'MM') AS DTMES, C.CDTIPODEMO, C.CDITEMSTRU, NVL(SUM(ROUND(L.VRLANCAMENT, 2)),0) AS VRSALDOITEM
                            FROM LANCCONT L, (SELECT C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO, COUNT(C.CDCONTCTBL) AS CONT
                                            FROM CONTCTBL C, ITEMAPUCTB I
                                            WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO || '%'
                                            AND C.IDTIPOCONT = 'A'
                                            AND C.IDCONTCCUS <> 'R'
                                            AND I.CDTIPODEMO = P_CDTIPODEMO
                                            AND I.IDTIPOORIG = 'S'
                                            AND C.NRORG = I.NRORG
                                            GROUP BY C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO
                                            HAVING COUNT(C.CDCONTCTBL) < 2) C
                            WHERE TRUNC(L.DTLANCCONT) BETWEEN P_DATAINI AND P_DATAFIN
                            AND L.CDEMPRESA = P_CDEMPRESA
                            AND L.CDCONTCTBL = C.CDCONTCTBL
                            AND C.NRORG = L.NRORG
                            AND L.IDLANCINTE <> 'F'
                            GROUP BY L.NRORG, L.CDEMPRESA, TRUNC(L.DTLANCCONT, 'MM'), C.CDTIPODEMO, C.CDITEMSTRU) S
                        RIGHT JOIN (SELECT ITEMDEMOF.NRORG, DT.DTMES, ITEMDEMOF.CDTIPODEMO,
                                            TIPODEMO.NMTIPODEMO, ITEMDEMOF.CDITEMSTRU, ITEMDEMOF.NMITEMSTRU, ITEMDEMOF.IDORIGVALOR
                                    FROM ITEMDEMOF
                                    JOIN TIPODEMO
                                        ON ITEMDEMOF.NRORG = TIPODEMO.NRORG
                                        AND ITEMDEMOF.CDTIPODEMO = TIPODEMO.CDTIPODEMO
                                        AND ITEMDEMOF.CDTIPODEMO = P_CDTIPODEMO
                                    JOIN (SELECT NRORG, DTMES
                                                FROM DATA
                                                WHERE DTDATA BETWEEN P_DATAINI AND P_DATAFIN
                                                GROUP BY NRORG, DTMES) DT
                                        ON DT.NRORG = ITEMDEMOF.NRORG) I
                            ON I.NRORG = S.NRORG
                            AND I.CDTIPODEMO = S.CDTIPODEMO
                            AND I.CDITEMSTRU = S.CDITEMSTRU
                            AND I.DTMES = S.DTMES
                        WHERE (I.NRORG = P_NRORG OR P_NRORG IS NULL)
                        ORDER BY NVL(S.NRORG, I.NRORG), NVL(S.DTMES, I.DTMES), NVL(S.CDEMPRESA, P_CDEMPRESA),
                                NVL(S.CDTIPODEMO, I.CDTIPODEMO), NVL(S.CDITEMSTRU, I.CDITEMSTRU)) LOOP
                IF V_MESREFERENCIA <> C2.DTMES THEN
                        FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
                        END LOOP;
                    FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                                    FROM ITEMDEMOF
                                    WHERE CDTIPODEMO = P_CDTIPODEMO
                                    ORDER BY CDITEMSTRU) LOOP
                        OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                        OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                        OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                        OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                        OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                        OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                        OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                        OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                        OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                        PIPE ROW(OUT_RECORD);
                    END LOOP;
                    V_MESREFERENCIA := C2.DTMES;
                    END IF;
                    V_TABLE(C2.CDITEMSTRU).NRORG := C2.NRORG;
                    V_TABLE(C2.CDITEMSTRU).CDEMPRESA := C2.CDEMPRESA;
                    V_TABLE(C2.CDITEMSTRU).CDTIPODEMO := C2.CDTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).NMTIPODEMO := C2.NMTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).CDITEMSTRU := C2.CDITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).NMITEMSTRU := C2.NMITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).DTMES := C2.DTMES;
                    V_TABLE(C2.CDITEMSTRU).VRLANCAMENT := C2.VRSALDOITEM;
            END LOOP;
            FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
            END LOOP;
            FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                            FROM ITEMDEMOF
                            WHERE CDTIPODEMO = P_CDTIPODEMO
                            ORDER BY CDITEMSTRU) LOOP
                OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                PIPE ROW(OUT_RECORD);
            END LOOP;
        END IF;
    ELSE
        IF P_IDDESCENCER = 'N' THEN
                FOR C2 IN (SELECT NVL(S.NRORG, I.NRORG) AS NRORG, NVL(P_CDEMPRESA, 'T') AS CDEMPRESA, NVL(S.DTMES, I.DTMES) AS DTMES,
                NVL(S.CDTIPODEMO, I.CDTIPODEMO) AS CDTIPODEMO, I.NMTIPODEMO, NVL(S.CDITEMSTRU, I.CDITEMSTRU) AS CDITEMSTRU, I.NMITEMSTRU,
                I.IDORIGVALOR,
                NVL(S.VRSALDOITEM, 0) AS VRSALDOITEM
                FROM (SELECT L.NRORG, TRUNC(L.DTLANCCONT, 'MM') AS DTMES, C.CDTIPODEMO, C.CDITEMSTRU, NVL(SUM(ROUND(L.VRLANCAMENT, 2)),0) AS VRSALDOITEM
                            FROM LANCCONT L, (SELECT C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO, COUNT(C.CDCONTCTBL) AS CONT
                                            FROM CONTCTBL C, ITEMAPUCTB I
                                            WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO || '%'
                                            AND C.IDTIPOCONT = 'A'
                                            AND C.IDCONTCCUS <> 'R'
                                            AND I.CDTIPODEMO = P_CDTIPODEMO
                                            AND I.IDTIPOORIG = 'S'
                                            AND C.NRORG = I.NRORG
                                            GROUP BY C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO
                                            HAVING COUNT(C.CDCONTCTBL) < 2) C
                            WHERE TRUNC(DTLANCCONT) BETWEEN P_DATAINI AND P_DATAFIN
                            AND L.CDCONTCTBL = C.CDCONTCTBL
                            AND C.NRORG = L.NRORG
                            GROUP BY L.NRORG, TRUNC(L.DTLANCCONT, 'MM'), C.CDTIPODEMO, C.CDITEMSTRU) S
                        RIGHT JOIN (SELECT ITEMDEMOF.NRORG, DT.DTMES, ITEMDEMOF.CDTIPODEMO,
                                            TIPODEMO.NMTIPODEMO, ITEMDEMOF.CDITEMSTRU, ITEMDEMOF.NMITEMSTRU, ITEMDEMOF.IDORIGVALOR
                                    FROM ITEMDEMOF
                                    JOIN TIPODEMO
                                        ON ITEMDEMOF.NRORG = TIPODEMO.NRORG
                                        AND ITEMDEMOF.CDTIPODEMO = TIPODEMO.CDTIPODEMO
                                        AND ITEMDEMOF.CDTIPODEMO = P_CDTIPODEMO
                                    JOIN (SELECT NRORG, DTMES
                                                FROM DATA
                                                WHERE DTDATA BETWEEN P_DATAINI AND P_DATAFIN
                                                GROUP BY NRORG, DTMES) DT
                                        ON DT.NRORG = ITEMDEMOF.NRORG) I
                            ON I.NRORG = S.NRORG
                            AND I.CDTIPODEMO = S.CDTIPODEMO
                            AND I.CDITEMSTRU = S.CDITEMSTRU
                            AND I.DTMES = S.DTMES
                        WHERE (I.NRORG = P_NRORG OR P_NRORG IS NULL)
                        ORDER BY NVL(S.NRORG, I.NRORG), NVL(S.DTMES, I.DTMES), NVL(P_CDEMPRESA, 'T'),
                                NVL(S.CDTIPODEMO, I.CDTIPODEMO), NVL(S.CDITEMSTRU, I.CDITEMSTRU)) LOOP
                IF V_MESREFERENCIA <> C2.DTMES THEN
                        FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
                        END LOOP;
                    FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                                    FROM ITEMDEMOF
                                    WHERE CDTIPODEMO = P_CDTIPODEMO
                                    ORDER BY CDITEMSTRU) LOOP
                        OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                        OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                        OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                        OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                        OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                        OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                        OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                        OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                        OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                        PIPE ROW(OUT_RECORD);
                    END LOOP;
                    V_MESREFERENCIA := C2.DTMES;
                    END IF;
                    V_TABLE(C2.CDITEMSTRU).NRORG := C2.NRORG;
                    V_TABLE(C2.CDITEMSTRU).CDEMPRESA := C2.CDEMPRESA;
                    V_TABLE(C2.CDITEMSTRU).CDTIPODEMO := C2.CDTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).NMTIPODEMO := C2.NMTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).CDITEMSTRU := C2.CDITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).NMITEMSTRU := C2.NMITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).DTMES := C2.DTMES;
                    V_TABLE(C2.CDITEMSTRU).VRLANCAMENT := C2.VRSALDOITEM;
            END LOOP;
            FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
            END LOOP;
            FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                            FROM ITEMDEMOF
                            WHERE CDTIPODEMO = P_CDTIPODEMO
                            ORDER BY CDITEMSTRU) LOOP
                OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                PIPE ROW(OUT_RECORD);
            END LOOP;
        ELSE
            FOR C2 IN (SELECT NVL(S.NRORG, I.NRORG) AS NRORG, NVL(P_CDEMPRESA, 'T') AS CDEMPRESA, NVL(S.DTMES, I.DTMES) AS DTMES,
                NVL(S.CDTIPODEMO, I.CDTIPODEMO) AS CDTIPODEMO, I.NMTIPODEMO, NVL(S.CDITEMSTRU, I.CDITEMSTRU) AS CDITEMSTRU, I.NMITEMSTRU,
                I.IDORIGVALOR,
                NVL(S.VRSALDOITEM, 0) AS VRSALDOITEM
                FROM (SELECT L.NRORG, TRUNC(L.DTLANCCONT, 'MM') AS DTMES, C.CDTIPODEMO, C.CDITEMSTRU, NVL(SUM(ROUND(L.VRLANCAMENT, 2)),0) AS VRSALDOITEM
                            FROM LANCCONT L, (SELECT C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO, COUNT(C.CDCONTCTBL) AS CONT
                                            FROM CONTCTBL C, ITEMAPUCTB I
                                            WHERE C.CDCONTCTBL LIKE I.CDORIGDEMO || '%'
                                            AND C.IDTIPOCONT = 'A'
                                            AND C.IDCONTCCUS <> 'R'
                                            AND I.CDTIPODEMO = P_CDTIPODEMO
                                            AND I.IDTIPOORIG = 'S'
                                            AND C.NRORG = I.NRORG
                                            GROUP BY C.NRORG, C.CDCONTCTBL, I.CDITEMSTRU, I.CDTIPODEMO
                                            HAVING COUNT(C.CDCONTCTBL) < 2) C
                            WHERE TRUNC(DTLANCCONT) BETWEEN P_DATAINI AND P_DATAFIN
                            AND L.CDCONTCTBL = C.CDCONTCTBL
                            AND C.NRORG = L.NRORG
                            AND L.IDLANCINTE <> 'F'
                            GROUP BY L.NRORG, TRUNC(L.DTLANCCONT, 'MM'), C.CDTIPODEMO, C.CDITEMSTRU) S
                        RIGHT JOIN (SELECT ITEMDEMOF.NRORG, DT.DTMES, ITEMDEMOF.CDTIPODEMO,
                                            TIPODEMO.NMTIPODEMO, ITEMDEMOF.CDITEMSTRU, ITEMDEMOF.NMITEMSTRU, ITEMDEMOF.IDORIGVALOR
                                    FROM ITEMDEMOF
                                    JOIN TIPODEMO
                                        ON ITEMDEMOF.NRORG = TIPODEMO.NRORG
                                        AND ITEMDEMOF.CDTIPODEMO = TIPODEMO.CDTIPODEMO
                                        AND ITEMDEMOF.CDTIPODEMO = P_CDTIPODEMO
                                    JOIN (SELECT NRORG, DTMES
                                                FROM DATA
                                                WHERE DTDATA BETWEEN P_DATAINI AND P_DATAFIN
                                                GROUP BY NRORG, DTMES) DT
                                        ON DT.NRORG = ITEMDEMOF.NRORG) I
                            ON I.NRORG = S.NRORG
                            AND I.CDTIPODEMO = S.CDTIPODEMO
                            AND I.CDITEMSTRU = S.CDITEMSTRU
                            AND I.DTMES = S.DTMES
                        WHERE (I.NRORG = P_NRORG OR P_NRORG IS NULL)
                        ORDER BY NVL(S.NRORG, I.NRORG), NVL(S.DTMES, I.DTMES), NVL(P_CDEMPRESA, 'T'),
                                NVL(S.CDTIPODEMO, I.CDTIPODEMO), NVL(S.CDITEMSTRU, I.CDITEMSTRU)) LOOP
                    IF V_MESREFERENCIA <> C2.DTMES THEN
                        FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
                        END LOOP;
                    FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                                    FROM ITEMDEMOF
                                    WHERE CDTIPODEMO = P_CDTIPODEMO
                                    ORDER BY CDITEMSTRU) LOOP
                        OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                        OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                        OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                        OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                        OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                        OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                        OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                        OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                        OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                        PIPE ROW(OUT_RECORD);
                    END LOOP;
                    V_MESREFERENCIA := C2.DTMES;
                    END IF;
                    V_TABLE(C2.CDITEMSTRU).NRORG := C2.NRORG;
                    V_TABLE(C2.CDITEMSTRU).CDEMPRESA := C2.CDEMPRESA;
                    V_TABLE(C2.CDITEMSTRU).CDTIPODEMO := C2.CDTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).NMTIPODEMO := C2.NMTIPODEMO;
                    V_TABLE(C2.CDITEMSTRU).CDITEMSTRU := C2.CDITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).NMITEMSTRU := C2.NMITEMSTRU;
                    V_TABLE(C2.CDITEMSTRU).DTMES := C2.DTMES;
                    V_TABLE(C2.CDITEMSTRU).VRLANCAMENT := C2.VRSALDOITEM;
            END LOOP;
            FOR C_AUX IN (SELECT I.NRORG, IT.CDTIPODEMO, IT.CDITEMSTRU, I.IDORIGVALOR, IT.IDTIPOORIG, IT.CDORIGDEMO
                                    FROM ITEMDEMOF I
                                    JOIN ITEMAPUCTB IT
                                        ON IT.CDTIPODEMO = I.CDTIPODEMO
                                        AND IT.CDITEMSTRU = I.CDITEMSTRU
                                    WHERE I.CDTIPODEMO = P_CDTIPODEMO
                                    AND IT.IDTIPOORIG = 'V'
                                    AND I.NRORG = IT.NRORG
                                    ORDER BY IT.CDTIPODEMO, IT.CDITEMSTRU) LOOP
                            IF V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT IS NULL THEN V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT := 0; END IF;
                            IF C_AUX.IDORIGVALOR <> 'I' THEN
                                        V_TABLE(C_AUX.CDITEMSTRU).NRORG := C_AUX.NRORG;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDEMPRESA := NVL(P_CDEMPRESA, 'T');
                                        V_TABLE(C_AUX.CDITEMSTRU).CDTIPODEMO := C_AUX.CDTIPODEMO;
                                        V_TABLE(C_AUX.CDITEMSTRU).CDITEMSTRU := C_AUX.CDITEMSTRU;
                                        V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT :=  V_TABLE(SUBSTR(C_AUX.CDORIGDEMO, 3, 4)).VRLANCAMENT +
                                                                                    V_TABLE(C_AUX.CDITEMSTRU).VRLANCAMENT;
                            END IF;
            END LOOP;
            FOR C_RETURN IN (SELECT CDITEMSTRU, IDORIGVALOR
                            FROM ITEMDEMOF
                            WHERE CDTIPODEMO = P_CDTIPODEMO
                            ORDER BY CDITEMSTRU) LOOP
                OUT_RECORD.NRORG := V_TABLE(C_RETURN.CDITEMSTRU).NRORG;
                OUT_RECORD.CDEMPRESA := V_TABLE(C_RETURN.CDITEMSTRU).CDEMPRESA;
                OUT_RECORD.DTMES := V_TABLE(C_RETURN.CDITEMSTRU).DTMES;
                OUT_RECORD.CDTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).CDTIPODEMO;
                OUT_RECORD.NMTIPODEMO := V_TABLE(C_RETURN.CDITEMSTRU).NMTIPODEMO;
                OUT_RECORD.CDITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).CDITEMSTRU;
                OUT_RECORD.NMITEMSTRU := V_TABLE(C_RETURN.CDITEMSTRU).NMITEMSTRU;
                OUT_RECORD.IDORIGVALOR := C_RETURN.IDORIGVALOR;
                OUT_RECORD.VRSALDOITEM := V_TABLE(C_RETURN.CDITEMSTRU).VRLANCAMENT;
                PIPE ROW(OUT_RECORD);
            END LOOP;
        END IF;
    END IF;
    RETURN;
END;