create or replace PROCEDURE P_PER_COSTSAVING_COMP1
                                                        (   
                                                           IP_STDT DATE, 
                                                            IP_ENDDT DATE, 
                                                            IP_ENCTYPE VARCHAR2 ,
                                                            OP_COSTSAVINGCOMPDATASET OUT SYS_REFCURSOR
                                                        )   
AS 
        V_ERRCODE NUMBER;  
        V_ERRMSG VARCHAR2(4000);  
        V_USERNAME VARCHAR2(150);
        TYPE V_TYPE_PER_COST_COMP_RECORD IS RECORD (
                                            id NUMBER(10),
                                            UserName VARCHAR2(250),  
                                            user_idn NUMBER(10),  
                                            EpisodeType VARCHAR2(100),  
                                            FixedCost NUMBER(18,0),  
                                            CalcCost NUMBER(18,0),  
                                            TotalTime NUMBER(18,0),  
                                            DirectCost NUMBER(18,0),  
                                            InDirectCost NUMBER(18,0)
                                          ); 
        TYPE V_TABLE_PER_COST_COMP_TAB IS TABLE OF V_TYPE_PER_COST_COMP_RECORD;
        V_TABLE_PER_COST_COMP V_TABLE_PER_COST_COMP_TAB;
        
BEGIN
	select rownum as id,	
		UserName,  
		user_idn,  
		EpisodeType,  
		FixedCost, 
		CalcCost, 
		TotalTime, 
		DirectCost, 
		InDirectCost BULK COLLECT INTO V_TABLE_PER_COST_COMP
	from (
			Select  
				UserName,  
				user_idn,  
				EpisodeType,  
				Sum(FixedCost)as FixedCost, 
				Sum(CalcCost) as CalcCost, 
				Sum(TotalTime) as TotalTime, 
				Sum(DirectCost)as DirectCost, 
				Sum(InDirectCost)as InDirectCost 
			from
			(
				Select null as  username,user_idn,EpisodeType,FixedCost,CalcCost,
                                       TotalTime,0 as DirectCost,0 as InDirectCost 
				From 
			)tab1
			group by UserName,user_idn,    EpisodeType 
	) finaldataset;  

  IF V_TABLE_PER_COST_COMP.first IS NOT NULL
	THEN
		FOR i in V_TABLE_PER_COST_COMP.first..V_TABLE_PER_COST_COMP.last
		LOOP
			V_TABLE_PER_COST_COMP(i).id := V_TABLE_PER_COST_COMP(i).id;
    
			select NVL(e.last_name,'')||','||NVL(e.first_name,'') into V_USERNAME
			from sys_user  su    
			left outer join entity e on su.entity_idn=e.entity_idn 
			WHERE  su.user_idn = V_TABLE_PER_COST_COMP(i).user_idn;
		    
			V_TABLE_PER_COST_COMP(i).UserName := V_USERNAME;
              
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||'A '||V_TABLE_PER_COST_COMP(i).UserName);
		END LOOP;
  END IF;
     -- OPEN OP_COSTSAVINGCOMPDATASET FOR V_TABLE_PER_COST_COMP;
		
EXCEPTION
    WHEN OTHERS THEN
        V_ERRCODE :=SQLCODE;
        V_ERRMSG := SQLERRM;
        RAISE_APPLICATION_ERROR(-20001,TO_CHAR(V_ERRCODE)||V_ERRMSG);
END P_PER_COSTSAVING_COMP1;
/

SQL> create or replace PROCEDURE P_PER_COSTSAVING_COMP1
                                                        (   
                                                           IP_STDT DATE, 
                                                            IP_ENDDT DATE, 
                                                            IP_ENCTYPE VARCHAR2 ,
                                                            OP_COSTSAVINGCOMPDATASET OUT SYS_REFCURSOR
                                                        )   
AS 
        V_ERRCODE NUMBER;  
        V_ERRMSG VARCHAR2(4000);  
        V_USERNAME VARCHAR2(150);
        TYPE V_TYPE_PER_COST_COMP_RECORD IS RECORD (
                                            id NUMBER(10),
                                            UserName VARCHAR2(250),  
                                            user_idn NUMBER(10),  
                                            EpisodeType VARCHAR2(100),  
                                            FixedCost NUMBER(18,0),  
                                            CalcCost NUMBER(18,0),  
                                            TotalTime NUMBER(18,0),  
                                            DirectCost NUMBER(18,0),  
                                            InDirectCost NUMBER(18,0)
                                          ); 
        TYPE V_TABLE_PER_COST_COMP_TAB IS TABLE OF V_TYPE_PER_COST_COMP_RECORD;
        V_TABLE_PER_COST_COMP V_TABLE_PER_COST_COMP_TAB;
        
BEGIN
	select rownum as id,	
		UserName,  
		user_idn,  
		EpisodeType,  
		FixedCost, 
		CalcCost, 
		TotalTime, 
		DirectCost, 
		InDirectCost BULK COLLECT INTO V_TABLE_PER_COST_COMP
	from (
			Select  
				UserName,  
				user_idn,  
				EpisodeType,  
				Sum(FixedCost)as FixedCost, 
				Sum(CalcCost) as CalcCost, 
				Sum(TotalTime) as TotalTime, 
				Sum(DirectCost)as DirectCost, 
				Sum(InDirectCost)as InDirectCost 
			from
			(
				Select null as  username,user_idn,EpisodeType,FixedCost,CalcCost,
                                       TotalTime,0 as DirectCost,0 as InDirectCost 
				From 
			)tab1
			group by UserName,user_idn,    EpisodeType 
	) finaldataset;  

  IF V_TABLE_PER_COST_COMP.first IS NOT NULL
	THEN
		FOR i in V_TABLE_PER_COST_COMP.first..V_TABLE_PER_COST_COMP.last
		LOOP
			V_TABLE_PER_COST_COMP(i).id := V_TABLE_PER_COST_COMP(i).id;
    
			select NVL(e.last_name,'')||','||NVL(e.first_name,'') into V_USERNAME
			from sys_user  su    
			left outer join entity e on su.entity_idn=e.entity_idn 
			WHERE  su.user_idn = V_TABLE_PER_COST_COMP(i).user_idn;
		    
			V_TABLE_PER_COST_COMP(i).UserName := V_USERNAME;
              
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||'A '||V_TABLE_PER_COST_COMP(i).UserName);
		END LOOP;
  END IF;
     -- OPEN OP_COSTSAVINGCOMPDATASET FOR V_TABLE_PER_COST_COMP;
END P_PER_COSTSAVING_COMP1;
/