CREATE OR REPLACE PACKAGE BODY CK_FDTREE AS
procedure ck_folderfd_tree( unique_id number, containerid varchar2, recursechar varchar2, funname varchar2) is
--  cursor to walk down contained folders
    cursor get_folders(container_id varchar2, recursec varchar2) is	
	Select folders.irid folderid,folders.name name
	from sdd_folders folders
	where 
                 folders.irid like container_id
                 and    ( folders.root_flag = 'Y' or container_id <> '%')
    union
	select child_folders.irid, child_folders.name
	from sdd_folders child_folders,
    ( select distinct member_object
   	from i$sdd_folder_members
   	connect by prior member_object = folder_reference
   	start with folder_reference in
        (Select folders.irid folderid
	from sdd_folders folders
	where 
             folders.irid like container_id
             and    ( folders.root_flag = 'Y' or container_id <> '%')
	)
    ) h
    where h.member_object = child_folders.irid
    and   recursec = 'Y'
    order by 2;
--  cursor to get functions in given folder
    cursor get_functions(container_id number, function_name varchar2) is
	SELECT   FUN.ID funid 
	FROM CI_FUNCTIONS FUN,
        CI_folder_members foldmem
	WHERE 
		foldmem.member_object = fun.id
		and foldmem.folder_reference = container_id 
		AND FUN.FUNCTION_LABEL like  UPPER(RTRIM(function_name ))
		ORDER BY FUN.FUNCTION_LABEL;


  begin
    	for each_folder in get_folders(containerid, recursechar) 
    	loop
      	begin
	    for each_function in get_functions(each_folder.folderid, funname)
	    loop 
	    begin  
	       ck_fdtree( unique_id , each_folder.folderid , each_function.funid);
            end;
            end loop;
	    ck_fwalk.ck_fwalk(unique_id, each_folder.folderid);
	    ck_bwalk.ck_bwalk(unique_id, each_folder.folderid);
     	end;
    	end loop;
  end;

procedure CK_FDTREE(P_SEQ      number,
		     P_FOLD_ID   number,
	             P_FUN_ID   number) is
-- P_SEQ      is the unique sequence id for the report session
-- P_FOLD_ID   is the irid of the containing folder
-- P_FUN_ID   is the irid of the function to decompose
begin
-- The following SQL insertS Atomic Function of P_FUN_ID into the Temporay 
-- table this is achieved by tree walking down the Hierarachy from P_FUN_ID
-- Where a there is a common function reference the hierarchy below is replaced 
-- by the hierarchary below the master function
-- The insertion is only done if a row corresponding to this atomic function (for the given
-- folder id) has not already been inserted.
-- skhwaja : Replace the view with base table name to support 817 database
   insert into CDI_TEMP_ATOMIC_ELEMENTS (TMP_SEQ,     
					 TMP_FOLDER_ID,
                                         TMP_ID,    
                                         TMP_CONTEXT_TYPE,
                                         TMP_START_END_FLAG,
                                         TMP_DIRECTION_FLAG,
                                         TMP_FLOW_TYPE_FLAG,
                                         TMP_PROCESSED_FLAG)
  select P_SEQ, 
	  P_FOLD_ID, 
          FUN.IRID, 
          'F', 
          NULL, 
          NULL, 
          NULL, 
          NULL 
   from   i$sdd_fun FUN 
    where    jr_vn.context(ivid) > 0
    and      FUN.COMMON_FUNCTION_REF IS NULL 
  -- and fun.id not in 
  -- (select tmp.tmp_id from CDI_TEMP_ATOMIC_ELEMENTS where tmp.TMP_SEQ = P_SEQ
--	AND tmp.TMP_FOLDER_ID = P_FOLD_ID )
    AND NOT EXISTS  
    (select NULL from CDI_TEMP_ATOMIC_ELEMENTS where TMP_SEQ = P_SEQ 
	AND TMP_FOLDER_ID = P_FOLD_ID AND TMP_ID = FUN.IRID) 
    and not exists 
    (select null from ci_functions where parent_reference = fun.irid) 
  start  with FUN.IRID = P_FUN_ID 
  connect by prior NVL(FUN.COMMON_FUNCTION_REF,FUN.IRID)= 
                        	FUN.PARENT_REF;

commit;

-- The following SQL take each of the atomic functions inserted into the 
-- temporay table by the previous sql and inserts distinct dataflows comming
-- in and out of that function  
-- The SQL prior to the UNION looks for outcomming flows
-- The SQL after the UNION looks for incomming flows
-- if the dataflow is to a DATASTORE or EXTERNAL then it is marked as a 
-- START or END object (Depending on direction) and is deemed to be processed
-- Otherwise the flows to/from other functions are inserted and not marked as 
-- PROCESSED


   insert into CDI_TEMP_ATOMIC_ELEMENTS (TMP_SEQ,
 					 TMP_FOLDER_ID,     
                                         TMP_ID,    
                                         TMP_CONTEXT_TYPE,
                                         TMP_START_END_FLAG,
                                         TMP_DIRECTION_FLAG,
                                         TMP_FLOW_TYPE_FLAG,
                                         TMP_PROCESSED_FLAG)
   select distinct
          P_SEQ,
	  P_FOLD_ID,
          DF.ID,
          'D',
          DECODE(DF.DESTINATION_TYPE,'FUNCTION',null,'E'),
          'O',
          DECODE(DF.DESTINATION_TYPE,'DATASTORE','D','FUNCTION','F','E'),
          DECODE(DF.DESTINATION_TYPE,'FUNCTION',null,'Y') 
   from   CI_DATAFLOWS DF
   where  DF.FUNCTION_SOURCE_REFERENCE IN (select TMP.TMP_ID
                                           from   CDI_TEMP_ATOMIC_ELEMENTS TMP
                                           where  TMP.TMP_SEQ = P_SEQ
					 AND TMP.TMP_FOLDER_ID = P_FOLD_ID
                                           and    TMP.TMP_CONTEXT_TYPE = 'F' 
                                            and    TMP.TMP_PROCESSED_FLAG IS NULL) 
   union
   select distinct
          P_SEQ,
	  P_FOLD_ID,
          DF.ID,
          'D',
          DECODE(DF.SOURCE_TYPE,'FUNCTION',null,'S'),
          'I',
          DECODE(DF.SOURCE_TYPE,'DATASTORE','D','FUNCTION','F','E'),
          DECODE(DF.SOURCE_TYPE,'FUNCTION',null,'Y')
   from   CI_DATAFLOWS DF
   where  DF.FUNCTION_DEST_REFERENCE IN (select TMP.TMP_ID
                                         from   CDI_TEMP_ATOMIC_ELEMENTS TMP
                                         where  TMP.TMP_SEQ = P_SEQ
					 AND TMP.TMP_FOLDER_ID = P_FOLD_ID
                                         and    TMP.TMP_CONTEXT_TYPE = 'F'
                                            and  TMP.TMP_PROCESSED_FLAG IS NULL); 
  
   commit;
end ;
END CK_FDTREE;
/