create or replace procedure insert_into_grouping
(
 p_group_id IN OUT number,
 p_group_name  IN varchar2,
 p_group_type_code  IN varchar2,
 p_access_group_flag  IN number,
 p_live_flag  IN number,
 p_choke_point_flag  IN number,
 p_private_name_flag  IN number,
 p_private_membership_flag  IN number,
 p_private_resources_flag  IN number,
 p_hidden_name_flag  IN number,
 p_hidden_membership_flag  IN number,
 p_hidden_resources_flag  IN number,
 p_group_editable_flag  IN number,
 p_group_deletable_flag  IN number,
 p_auto_deletion_flag  IN number,
 p_self_add_flag  IN number,
 p_generate_alias_group_flag  IN number,
 p_resources_to_children_flag  IN number,
 p_resources_from_parent_flag  IN number,
 p_entity_id  IN number,
 p_entity_type_code  IN varchar2,
 p_alias_group_prefix  IN varchar2,
 p_alias_group_suffix  IN varchar2,
 p_parent_group_id  IN number,
 p_owner_entity_id  IN number,
 p_viewer_entity_type_code  IN number,  -- this needs changed to varchar2 when database gets fixed
 p_owner_entity_type_code  IN varchar2,
 p_viewer_entity_id  IN number,
 p_effective_date  IN date,
 p_expiration_date  IN date,
 p_last_gen_date  IN date,
 p_created_by  IN number,
 p_created_date  IN date,
 p_last_modified_by  IN number,
 p_last_modified_date  IN date,
 p_marked_for_deletion  IN number,
 p_process_change_flag  IN number,
 p_process_insert_flag  IN number,
 p_source_institution_id  IN number,
 p_data_source_key   IN varchar2,
 p_generation_sequence IN number              
)
AS
type t_entity is ref cursor;
c_newid t_entity;
BEGIN
        if (p_group_id is null) then
                open c_newid for select 
		            group_id_seq.nextval 
		          from 
		           dual;             
                fetch c_newid into p_group_id;
                insert into grouping 
                  (
                   group_id ,
                   group_name,
                   group_type_code,
                   access_group_flag,
                   live_flag,
                   choke_point_flag,
                   private_name_flag,
                   private_membership_flag,
                   private_resources_flag,
                   hidden_name_flag,
                   hidden_membership_flag,
                   hidden_resources_flag,
                   group_editable_flag,
                   group_deletable_flag,
                   auto_deletion_flag,
                   self_add_flag,
                   generate_alias_group_flag,
                   resources_to_children_flag,
                   resources_from_parent_flag,
                   entity_id,
                   entity_type_code,
                   alias_group_prefix,
                   alias_group_suffix,
                   parent_group_id,
                   owner_entity_id,
                   viewer_entity_type_code,
                   owner_entity_type_code,
                   viewer_entity_id,
                   effective_date,
                   expiration_date,
                   last_gen_date,
                   created_by,
                   created_date,
                   last_modified_by,
                   last_modified_date,
                   marked_for_deletion,
                   process_change_flag,
                   process_insert_flag,
                   source_institution_id,
                   data_source_key,
                   generation_sequence                
                  )
                 values 
                  (
                   p_group_id ,
                   p_group_name,  
                   p_group_type_code,
                   p_access_group_flag,
                   p_live_flag,
                   p_choke_point_flag,
                   p_private_name_flag,
                   p_private_membership_flag,
                   p_private_resources_flag,
                   p_hidden_name_flag,
                   p_hidden_membership_flag,
                   p_hidden_resources_flag,
                   p_group_editable_flag,
                   p_group_deletable_flag,
                   p_auto_deletion_flag,
                   p_self_add_flag,
                   p_generate_alias_group_flag,
                   p_resources_to_children_flag,
                   p_resources_from_parent_flag,
                   p_entity_id,
                   p_entity_type_code,
                   p_alias_group_prefix,
                   p_alias_group_suffix,
                   p_parent_group_id,
                   p_owner_entity_id,
                   p_viewer_entity_type_code,
                   p_owner_entity_type_code,
                   p_viewer_entity_id,
                   p_effective_date,
                   p_expiration_date,
                   p_last_gen_date,
                   p_created_by,
                   p_created_date,
                   p_last_modified_by,
                   p_last_modified_date,
                   p_marked_for_deletion,
                   p_process_change_flag,
                   p_process_insert_flag,
                   p_source_institution_id,
                   p_data_source_key,
                   p_generation_sequence                                   
                  );
                commit; 
               else
                 update grouping set
                     last_modified_date = p_last_modified_date,
                     last_modified_by = p_last_modified_by
                    where group_id = p_group_id;
                 commit;      
             end if; -----keep processing
END insert_into_grouping;
/
show errors;