CREATE OR REPLACE PACKAGE BODY CK_FWALK AS

procedure CK_FWALK(P_SEQ number, P_FOLDER_ID number) is

-- P_SEQ      is the unique sequence id for the report session
-- P_FOLDER_ID is the folder irid of the container whose atomic functions are being processed
-- Cursor to get all unprocessed flows

   cursor GET_FLOW is
          select TMP_ID,
                 TMP_PROCESSED_FLAG,
                 TMP_START_END_FLAG 
          from   CDI_TEMP_ATOMIC_ELEMENTS
          where  TMP_CONTEXT_TYPE   = 'D'
          and    TMP_DIRECTION_FLAG = 'O'
          and    TMP_SEQ            = P_SEQ
	  and    TMP_FOLDER_ID      = P_FOLDER_ID
          and    TMP_PROCESSED_FLAG is null
          for update;

-- DONE is used to indicate when no unprocessed flows exists

   DONE boolean;

begin
   

   DONE := true;
  
while DONE loop 
-- FIRST LOOP

   DONE := false;

-- for each unprocess flow (Outcomming flows)

   for THIS_FLOW in GET_FLOW loop
-- SECOND LOOP
          DONE := true;

-- trace all other out commingflows
-- where a flow is to a DATASTORE, EXTERNAL mark it as END flow and 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 P_SEQ,
		 P_FOLDER_ID,
                 D.ID,
                 'D',
                 DECODE(D.DESTINATION_TYPE,'FUNCTION',null,'E'),
                 'O',
                 DECODE(D.DESTINATION_TYPE,'DATASTORE','D','FUNCTION','F','E'),
                 DECODE(D.DESTINATION_TYPE,'FUNCTION',null,'Y')
          from   CI_DATAFLOWS S,
                 CI_DATAFLOWS D 
          where  S.ID = THIS_FLOW.TMP_ID
          and    D.FUNCTION_SOURCE_REFERENCE = S.FUNCTION_DEST_REFERENCE 
          and not exists (select TMP_ID 
                          from   CDI_TEMP_ATOMIC_ELEMENTS
                          where  TMP_ID = D.ID
                          and    TMP_SEQ = P_SEQ
			  AND    TMP_FOLDER_ID = P_FOLDER_ID
	                  and    TMP_CONTEXT_TYPE = 'D');

          if sql%notfound then

-- If no outcomming flows were found then THIS_FLOW must be an END flow
-- therefore update it to reflect this and mark it as processed
         
             update CDI_TEMP_ATOMIC_ELEMENTS 
             set    TMP_START_END_FLAG = 'E',
                    TMP_PROCESSED_FLAG = 'Y'
             where current of GET_FLOW;
          else

-- otherwise mark THIS_FLOW as processed

             update CDI_TEMP_ATOMIC_ELEMENTS  
             set    TMP_PROCESSED_FLAG = 'Y'
             where current of GET_FLOW;
          end if;
 ---END SECOND LOOP
      end loop;
   
   commit;
 ---END FIRST LOOP 
   end loop;

   
  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_FOLDER_ID,
                 NVL(S.FUNCTION_DEST_REFERENCE,-1),
                 'F',
                 NULL,
                 'O',
                 NULL,
                 'T'
          from   CI_DATAFLOWS S
          where  S.ID IN  (select TMP_ID 
                          from   CDI_TEMP_ATOMIC_ELEMENTS
                          where  TMP_SEQ = P_SEQ
			  AND TMP_FOLDER_ID = P_FOLDER_ID
                          and    TMP_CONTEXT_TYPE = 'D'
                            AND TMP_DIRECTION_FLAG = 'O');

end;
END CK_FWALK;
/