Re: Package, Package Body and Function declarations

The package/procedure/function worked properly when I added the following line to the package declaration...

          PRAGMA RESTRICT_REFERENCES (FUNC_CUMULATIVE, WNDS,RNDS); I also had to "switch around" my comparisons in the IF statement in order to correctly reset the "cumulative counter".

Original...

             IF avc_datetype <> lvc_datetype THEN... Required...

             IF lvc_datetype <> avc_datetype THEN...

Hope this helps someone. I spent a decent amount of time looking at the Oracle Documentation (I'm thankful for the Adobe Acrobat format).

Brian

In article <6rkgua$5a6$1_at_nnrp1.dejanews.com>,   vick9935_at_my-dejanews.com wrote:
> I am having difficulty in defining a function within a package body, and then
> using that function within a procedure SQL statement in that same package
> body.
>
> I also question if you can use aliases, defined within the same SQL statement
> that calls a function, as the function arguments.
>
> Any help is greatly appreciated.
>
> Here is the entire definition...
>
> rem SQL statement for cash flow
> rem brian vickery
> rem 21-aug-1998
> rem
>
> create or replace package PACK_CASHFLOW as
> type PROC_CASHFLOW_DEF_TYPE is record (
> monthdollar number,
> datevalue date,
> cumdollar number,
> typeofdate varchar2(10)) ;
> type rc_type is ref cursor return PROC_CASHFLOW_DEF_TYPE ;
> procedure PROC_CASHFLOW (avc_user IN VARCHAR2,avc_startdate IN
> VARCHAR2,avc_enddate IN VARCHAR2, rc in out rc_type );
> end ;
> /
> create or replace package body PACK_CASHFLOW as
> ln_cumulative number := 0 ;
> lvc_datetype varchar2(10) := '';
> function FUNC_CUMULATIVE (avc_datetype varchar2, an_increment number )
> return number is
> BEGIN
> IF avc_datetype <> lvc_datetype THEN
> ln_cumulative := 0;
> lvc_datetype := avc_datetype;
> END IF;
> ln_cumulative := ln_cumulative + an_increment ;
> Return ln_cumulative ;
> END FUNC_CUMULATIVE;
> procedure PROC_CASHFLOW (avc_user IN VARCHAR2,avc_startdate IN
> VARCHAR2,avc_enddate IN VARCHAR2, rc in out rc_type ) as
> begin
> OPEN rc FOR SELECT
> ROUND((SUM(NVL((CP_COST_TASK.CURRENT_BUDGET_PROJ *
> CP_PROJ_TASK_MLSTN.PCT_TASK_BUDGET)/100,0)))/1000,0) MonthDollar,
> TRUNC(CP_PROJ_SCHED_ACTIVITY.PLAN_DATE,'MM')
> DATEVALUE,
>
> FUNC_CUMULATIVE('BUDGET',ROUND((SUM(NVL((CP_COST_TASK.CURRENT_BUDGET_PROJ *
> CP_PROJ_TASK_MLSTN.PCT_TASK_BUDGET)/100,0)))/1000,0)) CumDollar,
> 'BUDGET' TYPEOFDATE
> FROM I_USER_PROJ_FILTER_PROJS,
> CP_COST_TASK,
> CP_PROJ_TASK_MLSTN,
> CP_PROJ_SCHED_ACTIVITY
> WHERE I_USER_PROJ_FILTER_PROJS.USERID = avc_user
> AND I_USER_PROJ_FILTER_PROJS.PROJ_NO =
> CP_COST_TASK.PROJ_NO
> AND CP_COST_TASK.PROJ_NO =
> CP_PROJ_TASK_MLSTN.PROJ_NO
> AND CP_COST_TASK.COST_TASK =
> CP_PROJ_TASK_MLSTN.COST_TASK
> AND CP_PROJ_TASK_MLSTN.PROJ_NO =
> CP_PROJ_SCHED_ACTIVITY.PROJ_NO
> AND CP_PROJ_TASK_MLSTN.SCHED_ACTIVITY_CODE =
> CP_PROJ_SCHED_ACTIVITY.SCHED_ACTIVITY_CODE
> AND TRUNC(CP_PROJ_SCHED_ACTIVITY.PLAN_DATE,'MM')
> BETWEEN TO_DATE(avc_startdate,'DD-MON-YY')
>
> AND TO_DATE(avc_enddate,'DD-MON-YY')
> GROUP BY
> TRUNC(CP_PROJ_SCHED_ACTIVITY.PLAN_DATE,'MM'),
> 'PLAN'
> ORDER BY 4,2;
> end PROC_CASHFLOW;
> end PACK_CASHFLOW;
> /
>
> --
> Brian in Seattle
>
> A person who smiles in the face of adversity...probably has a scapegoat.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

--
Brian in Seattle

A person who smiles in the face of adversity...probably has a scapegoat.

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum