Running procedures through BI Publisher

 Hi,

Have a procedure that reads as follows, it runs perfectly fine through Oracle sqlplus. Want to run it through BI Publisher.

Recommended solutions were to convert the procedure to a function and then run it through Data template of BI Publisher.Currently, not aware of the exact steps. Kindly let know .

Regards.

CREATE OR REPLACE PROCEDURE BAL_PRO

(P_YEAR IN NUMBER)

IS

 

V_TOT                   NUMBER(30,2):=0;

V_BAL                   NUMBER(30,2):=0;

V_DATE                DATE;

 

CURSOR C1 IS

                                SELECT CBLDATE,ROUND(SUM(BALANCE4/100000),2) BAL

                                FROM                    FT_D010014_ODS

                                HAVING                TO_CHAR(CBLDATE,'YYYY')=P_YEAR

                                GROUP BY            CBLDATE

                                ORDER BY            CBLDATE;

 

C1_REC