CREATE OR REPLACE PROCEDURE DASHBOARD_FIN_CLI_INAD AS
BEGIN
    DELETE FROM PDCCLIEINADIPLE;

    FOR CLIENTES IN (
        SELECT
            CL.NRORG,
            BR.CDEMPRESA,
            E.NMEMPRESA,
            CL.NRINSJURCLIE,
            CL.NMRAZSOCCLIE,
            BR.DTVENBOLREC,
            MIN(BR.DTVENBOLREC) AS DTVENBOLRECMIN,
            MAX(BR.DTVENBOLREC) AS DTVENBOLRECMAX,
            SUM(BR.VRFINALBREC) VRTOTALBOLREC
        FROM
            BOLETRECEBER BR,
            CLIENTE CL,
            EMPRESA E
        WHERE
            BR.CDCLIENTE = CL.CDCLIENTE
            AND BR.DTVENBOLREC < ( SYSDATE - 1 )
            AND BR.IDSTATBREC <> 'B'
            AND BR.CDEMPRESA = E.CDEMPRESA
        GROUP BY
            CL.NRORG,
            BR.CDEMPRESA,
            E.NMEMPRESA,
            CL.NRINSJURCLIE,
            CL.NMRAZSOCCLIE,
            BR.DTVENBOLREC
        ORDER BY
            VRTOTALBOLREC DESC
    ) LOOP
        INSERT INTO PDCCLIEINADIPLE (
            NRORG,
            CDEMPRESA,
            NMEMPRESA,
            NRINSJURCLIE,
            NMRAZSOCCLIE,
            DTVENBOLREC,
            DTVENBOLRECMIN,
            DTVENBOLRECMAX,
            VRTOTALBOLREC
        ) VALUES (
            CLIENTES.NRORG,
            CLIENTES.CDEMPRESA,
            CLIENTES.NMEMPRESA,
            CLIENTES.NRINSJURCLIE,
            CLIENTES.NMRAZSOCCLIE,
            CLIENTES.DTVENBOLREC,
            CLIENTES.DTVENBOLRECMIN,
            CLIENTES.DTVENBOLRECMAX,
            CLIENTES.VRTOTALBOLREC
        );

    END LOOP;

END;
/
