Package, Package Body and Function declarations

From: <vick9935_at_my-dejanews.com>
Date: 1998/08/21
Message-ID: <6rkgua$5a6$1@nnrp1.dejanews.com>#1/1

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

Received on Fri Aug 21 1998 - 00:00:00 CDT