CREATE OR REPLACE PACKAGE BODY CK_ASSOC AS

procedure ck_assoc( unique_id number, typeid number) is
--  this procedure uses the cursors defined in jmquery.jps
--  first query association details that have FKs to our elemnet

  begin
--  all assoc details that have FKs to our element
dbms_output.put_line('at start of proc');
    	for each_row in jrmquery.associations_to_cur(typeid) 
    	loop
      	begin

	
	    for each_inner_row in jrmquery.associations_from_cur(each_row.element_id)
	    loop 
	    begin  
		if(each_inner_row.arc_no is not null) then 
			if(each_inner_row.arc_no != each_row.arc_no) then
		       		ck_assoc_table( unique_id , 
				typeid, 
				each_row.element_id , 
                        	each_inner_row.other_element_id,
				each_row.assoc_name,
				each_inner_row.assoc_name);
			end if;
		else 
		       		ck_assoc_table( unique_id , 
				typeid, 
				each_row.element_id , 
                        	each_inner_row.other_element_id,
				each_row.assoc_name,
				each_inner_row.assoc_name);
		end if;
            end;
            end loop;
 
     	end;
    	end loop;
  end;

procedure ck_assoc_table (P_SEQ      number,
			P_THIS_TYPE_ID   number,
			P_ASSOC_TYPE_ID  number,
			P_OTHER_ELEMENT_ID  number,
			P_THIS_COL_NAME  varchar2,
			P_OTHER_COL_NAME varchar2) is
-- P_SEQ      is the unique sequence id for the report session
-- P_THIS TYPE_ID   is the element type id we start with
-- P_ASSOC_TYPE_ID   is the id of the intersection type 
-- P_OTHER_ELEMENT_ID is the element type id at the other end of the assoc
-- P_THIS_COL_NAME name of the column in the intersection view that points to this elem type
-- P_OTHER_COL_NAME name of the column in the intersection view that points to the other element

begin
-- The following SQL insertS rows into cdi_temp_atomic_elements

	for fm in  jrmquery.element_type_cur(P_THIS_TYPE_ID) loop   
		tmp_this_view :=fm.product||'_'||fm.plural_name; 
	end loop;
	for fm in  jrmquery.element_type_cur(P_ASSOC_TYPE_ID) loop   
		tmp_intersection_view :=fm.product||'_'||fm.plural_name; 
	end loop;
	for fm in  jrmquery.element_type_cur(P_OTHER_ELEMENT_ID) loop   
		tmp_other_view :=fm.product||'_'||fm.plural_name; 
	end loop;

   insert into CDI_TEMP_ASSOC_ELEMENTS (SEQ,     
					 THIS_ELEM_TYPE_ID,
                                         INTERSECT_TYPE_ID,    
                                         OTHER_ELEM_TYPE_ID,
                                         THIS_VIEW,
                                         INTERSECT_VIEW  ,
                                         OTHER_VIEW ,
                                         THIS_COL,
					 OTHER_COL)
        VALUES
	(P_SEQ,
	P_THIS_TYPE_ID,
        P_ASSOC_TYPE_ID,
        P_OTHER_ELEMENT_ID,
        tmp_this_view,
        tmp_intersection_view,
        tmp_other_view,
        P_THIS_COL_NAME,
	P_OTHER_COL_NAME);
        commit;

 

end ;
END ck_assoc;
/