-- ./contrib/packages/events/sql/oracle/events-activities-package-create.sql -- -- The Events Package -- -- @author Michael Steigman (michael@steigman.net) -- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ -- -- This package was orinally written by Bryan Che and Philip Greenspun -- -- GNU GPL v2 -- -- The Activity Package create or replace package events_activity as function new ( activity_id in events_activities.activity_id%TYPE default null, package_id in events_activities.package_id%TYPE, detail_url in events_activities.detail_url%TYPE default null, default_contact_user_id in events_activities.default_contact_user_id%TYPE default null, name in acs_activities.name%TYPE default null, description in acs_activities.description%TYPE default null, html_p in acs_activities.html_p%TYPE default 'f', status_summary in acs_activities.status_summary%TYPE default null, object_type in acs_object_types.object_type%TYPE default 'events_activity', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_activities.activity_id%TYPE; procedure del ( activity_id in events_activities.activity_id%TYPE ); end events_activity; / show errors; create or replace package body events_activity as function new ( activity_id in events_activities.activity_id%TYPE default null, package_id in events_activities.package_id%TYPE, detail_url in events_activities.detail_url%TYPE default null, default_contact_user_id in events_activities.default_contact_user_id%TYPE default null, name in acs_activities.name%TYPE default null, description in acs_activities.description%TYPE default null, html_p in acs_activities.html_p%TYPE default 'f', status_summary in acs_activities.status_summary%TYPE default null, object_type in acs_object_types.object_type%TYPE default 'events_activity', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_activities.activity_id%TYPE is v_activity_id events_activities.activity_id%TYPE; begin v_activity_id:= acs_activity.new ( activity_id => activity_id, name => name, description => description, html_p => html_p, status_summary => status_summary, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id ); insert into events_activities (activity_id, package_id, detail_url, default_contact_user_id) values (v_activity_id, package_id, detail_url, default_contact_user_id); return v_activity_id; end new; procedure del ( activity_id in events_activities.activity_id%TYPE ) is cursor v_activity_events is select event_id from acs_events where activity_id = del.activity_id; begin -- find and delete event instances for row in v_activity_events loop events_event.del(row.event_id); end loop; delete from events_def_actvty_attr_map where activity_id = del.activity_id; delete from events_org_role_activity_map where activity_id = del.activity_id; delete from events_activities where activity_id = del.activity_id; acs_object.delete(activity_id); end del; end events_activity; / show errors; -- ./contrib/packages/events/sql/oracle/events-events-package-create.sql -- -- The Events Package -- -- @author Michael Steigman (michael@steigman.net) -- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ -- -- This package was orinally written by Bryan Che and Philip Greenspun -- -- GNU GPL v2 -- -- The Event Package create or replace package events_event as procedure new ( event_id in events_events.event_id%TYPE default null, venue_id in events_venues.venue_id%TYPE, display_after in events_events.display_after%TYPE default null, max_people in events_events.max_people%TYPE default null, reg_deadline in events_events.reg_deadline%TYPE, available_p in events_events.available_p%TYPE default 't', deleted_p in events_events.deleted_p%TYPE default 'f', reg_cancellable_p in events_events.reg_cancellable_p%TYPE, reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, contact_user_id in events_events.contact_user_id%TYPE default null, refreshments_note in events_events.refreshments_note%TYPE default null, av_note in events_events.av_note%TYPE default null, additional_note in events_events.additional_note%TYPE default null, alternative_reg in events_events.alternative_reg%TYPE default null, activity_id in acs_events.activity_id%TYPE ); -- function new ( -- event_id in events_events.event_id%TYPE default null, -- venue_id in events_venues.venue_id%TYPE, -- display_after in events_events.display_after%TYPE default null, -- max_people in events_events.max_people%TYPE default null, -- reg_deadline in events_events.reg_deadline%TYPE, -- available_p in events_events.available_p%TYPE default 't', -- deleted_p in events_events.deleted_p%TYPE default 'f', -- reg_cancellable_p in events_events.reg_cancellable_p%TYPE, -- reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, -- refreshments_note in events_events.refreshments_note%TYPE default null, -- av_note in events_events.av_note%TYPE default null, -- additional_note in events_events.additional_note%TYPE default null, -- alternative_reg in events_events.alternative_reg%TYPE default null, -- activity_id in acs_events.activity_id%TYPE, -- name in acs_events.name%TYPE default null, -- description in acs_events.description%TYPE default null, -- html_p in acs_events.html_p%TYPE default 'f', -- status_summary in acs_events.status_summary%TYPE default null, -- timespan_id in acs_events.timespan_id%TYPE, -- recurrence_id in acs_events.recurrence_id%TYPE default null, -- object_type in acs_objects.object_type%TYPE default 'events_event', -- creation_date in acs_objects.creation_date%TYPE default sysdate, -- creation_user in acs_objects.creation_user%TYPE, -- creation_ip in acs_objects.creation_ip%TYPE, -- context_id in acs_objects.context_id%TYPE default null -- ) return events_events.event_id%TYPE; procedure del ( event_id in events_events.event_id%TYPE ); end events_event; / show errors; create or replace package body events_event as procedure new ( event_id in events_events.event_id%TYPE default null, venue_id in events_venues.venue_id%TYPE, display_after in events_events.display_after%TYPE default null, max_people in events_events.max_people%TYPE default null, reg_deadline in events_events.reg_deadline%TYPE, available_p in events_events.available_p%TYPE default 't', deleted_p in events_events.deleted_p%TYPE default 'f', reg_cancellable_p in events_events.reg_cancellable_p%TYPE, reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, contact_user_id in events_events.contact_user_id%TYPE default null, refreshments_note in events_events.refreshments_note%TYPE default null, av_note in events_events.av_note%TYPE default null, additional_note in events_events.additional_note%TYPE default null, alternative_reg in events_events.alternative_reg%TYPE default null, activity_id in acs_events.activity_id%TYPE ) is cursor v_activity_attrs is select attribute_id from events_def_actvty_attr_map edaam where edaam.activity_id = new.activity_id; cursor v_activity_org_roles is select role_id from events_org_role_activity_map eoram where eoram.activity_id = new.activity_id; begin insert into events_events (event_id, contact_user_id, venue_id, display_after, max_people, reg_deadline, available_p, deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, additional_note, alternative_reg) values (event_id, contact_user_id, venue_id, display_after, max_people, reg_deadline, available_p, deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, additional_note, alternative_reg); -- copy over pointers to custom fields (registration attributes) for row in v_activity_attrs loop insert into events_event_attr_map (event_id, attribute_id) values (event_id, row.attribute_id); end loop; for row in v_activity_org_roles loop insert into events_org_role_event_map (event_id, role_id) values (event_id, row.role_id); end loop; end new; -- function new ( -- event_id in events_events.event_id%TYPE default null, -- venue_id in events_venues.venue_id%TYPE, -- display_after in events_events.display_after%TYPE default null, -- max_people in events_events.max_people%TYPE default null, -- reg_deadline in events_events.reg_deadline%TYPE, -- available_p in events_events.available_p%TYPE default 't', -- deleted_p in events_events.deleted_p%TYPE default 'f', -- reg_cancellable_p in events_events.reg_cancellable_p%TYPE, -- reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, -- refreshments_note in events_events.refreshments_note%TYPE default null, -- av_note in events_events.av_note%TYPE default null, -- additional_note in events_events.additional_note%TYPE default null, -- alternative_reg in events_events.alternative_reg%TYPE default null, -- activity_id in acs_events.activity_id%TYPE, -- name in acs_events.name%TYPE default null, -- description in acs_events.description%TYPE default null, -- html_p in acs_events.html_p%TYPE default 'f', -- status_summary in acs_events.status_summary%TYPE default null, -- timespan_id in acs_events.timespan_id%TYPE, -- recurrence_id in acs_events.recurrence_id%TYPE default null, -- object_type in acs_objects.object_type%TYPE default 'events_event', -- creation_date in acs_objects.creation_date%TYPE default sysdate, -- creation_user in acs_objects.creation_user%TYPE, -- creation_ip in acs_objects.creation_ip%TYPE, -- context_id in acs_objects.context_id%TYPE default null -- ) return events_events.event_id%TYPE -- is -- v_event_id events_events.event_id%TYPE; -- cursor v_activity_attrs is -- select attribute_id -- from events_def_actvty_attr_map edaam -- where edaam.activity_id = new.activity_id; -- cursor v_activity_org_roles is -- select role_id -- from events_org_role_activity_map eoram -- where eoram.activity_id = new.activity_id; -- begin -- v_event_id := acs_event.new ( -- event_id => event_id, -- name => name, -- description => description, -- html_p => html_p, -- status_summary => status_summary, -- timespan_id => timespan_id, -- activity_id => activity_id, -- recurrence_id => recurrence_id, -- object_type => object_type, -- creation_date => creation_date, -- creation_user => creation_user, -- creation_ip => creation_ip, -- context_id => context_id -- ); -- insert into events_events -- (event_id, venue_id, display_after, max_people, reg_deadline, available_p, -- deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, -- additional_note, alternative_reg) -- values -- (v_event_id, venue_id, display_after, max_people, reg_deadline, available_p, -- deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, -- additional_note, alternative_reg); -- -- copy over pointers to custom fields (registration attributes) -- for row in v_activity_attrs loop -- insert into events_event_attr_map -- (event_id, attribute_id) -- values -- (v_event_id, row.attribute_id); -- end loop; -- for row in v_activity_org_roles loop -- insert into events_org_role_event_map -- (event_id, role_id) -- values -- (v_event_id, row.role_id); -- end loop; -- return v_event_id; -- end new; procedure del ( event_id in events_events.event_id%TYPE ) is begin delete from events_org_role_event_map where event_id = del.event_id; delete from events_event_attr_map where event_id = del.event_id; delete from events_events where event_id = del.event_id; acs_object.delete(event_id); end del; end events_event; / show errors; -- ./contrib/packages/events/sql/oracle/events-orders-package-create.sql -- -- The Events Package -- -- @author Michael Steigman (michael@steigman.net) -- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ -- -- This package was originally written by Bryan Che and Philip Greenspun -- -- GNU GPL v2 -- -- Order Package create or replace package events_order as function new ( order_id in events_orders.order_id%TYPE default null, user_id in users.user_id%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_orders.order_id%TYPE; procedure del ( order_id in events_orders.order_id%TYPE ); end events_order; / show errors; create or replace package body events_order as function new ( order_id in events_orders.order_id%TYPE default null, user_id in users.user_id%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_orders.order_id%TYPE is v_order_id acs_objects.object_id%TYPE; begin v_order_id:= acs_object.new ( object_id => order_id, object_type => 'events_order', creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id ); insert into events_orders (order_id, user_id) values (v_order_id, user_id); return v_order_id; end new; procedure del ( order_id in events_orders.order_id%TYPE ) is begin acs_object.delete(del.order_id); end del; end events_order; / show errors; -- ./contrib/packages/events/sql/oracle/events-registrations-package-create.sql -- -- The Events Package -- -- @author Michael Steigman (michael@steigman.net) -- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ -- -- This package was orinally written by Bryan Che and Philip Greenspun -- -- GNU GPL v2 -- -- The Registration Package create or replace package events_registration as function new ( reg_id in events_registrations.reg_id%TYPE default null, event_id in events_events.event_id%TYPE, user_id in users.user_id%TYPE, reg_state in events_registrations.reg_state%TYPE, comments in events_registrations.comments%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_registrations.reg_id%TYPE; procedure del ( reg_id in events_registrations.reg_id%TYPE ); end events_registration; / show errors; create or replace package body events_registration as function new ( reg_id in events_registrations.reg_id%TYPE default null, event_id in events_events.event_id%TYPE, user_id in users.user_id%TYPE, reg_state in events_registrations.reg_state%TYPE, comments in events_registrations.comments%TYPE, creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE, context_id in acs_objects.context_id%TYPE default null ) return events_registrations.reg_id%TYPE is v_reg_id acs_objects.object_id%TYPE; begin v_reg_id:= acs_object.new ( object_id => reg_id, object_type => 'events_registration', creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id ); insert into events_registrations (reg_id, event_id, user_id, reg_state, comments) values (v_reg_id, event_id, user_id, reg_state, comments); return v_reg_id; end new; procedure del ( reg_id in events_registrations.reg_id%TYPE ) is begin acs_object.delete(reg_id); end del; end events_registration; / show errors; -- ./contrib/packages/events/sql/oracle/events-venues-package-create.sql -- -- The Events Package -- -- @author Michael Steigman (michael@steigman.net) -- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ -- -- This package was orinally written by Bryan Che and Philip Greenspun -- -- GNU GPL v2 -- -- The Venue Package create or replace package events_venue as function new ( venue_id in events_venues.venue_id%TYPE default null, package_id in events_venues.package_id%TYPE default null, venue_name in events_venues.venue_name%TYPE, address1 in events_venues.address1%TYPE default null, address2 in events_venues.address2%TYPE default null, city in events_venues.city%TYPE, usps_abbrev in events_venues.usps_abbrev%TYPE default null, postal_code in events_venues.postal_code%TYPE default null, time_zone in events_venues.time_zone%TYPE default null, iso in events_venues.iso%TYPE default null, phone_number in events_venues.phone_number%TYPE default null, fax_number in events_venues.fax_number%TYPE default null, email in events_venues.email%TYPE default null, needs_reserve_p in events_venues.needs_reserve_p%TYPE default 'f', max_people in events_venues.max_people%TYPE default null, description in events_venues.description%TYPE default null ) return events_venues.venue_id%TYPE; procedure del ( venue_id in events_venues.venue_id%TYPE ); end events_venue; / show errors; create or replace package body events_venue as function new ( venue_id in events_venues.venue_id%TYPE default null, package_id in events_venues.package_id%TYPE default null, venue_name in events_venues.venue_name%TYPE, address1 in events_venues.address1%TYPE default null, address2 in events_venues.address2%TYPE default null, city in events_venues.city%TYPE, usps_abbrev in events_venues.usps_abbrev%TYPE default null, postal_code in events_venues.postal_code%TYPE default null, time_zone in events_venues.time_zone%TYPE default null, iso in events_venues.iso%TYPE default null, phone_number in events_venues.phone_number%TYPE default null, fax_number in events_venues.fax_number%TYPE default null, email in events_venues.email%TYPE default null, needs_reserve_p in events_venues.needs_reserve_p%TYPE default 'f', max_people in events_venues.max_people%TYPE default null, description in events_venues.description%TYPE default null ) return events_venues.venue_id%TYPE is v_venue_id events_venues.venue_id%TYPE; begin insert into events_venues (venue_id, venue_name, address1, address2, city, usps_abbrev, postal_code, iso, phone_number, time_zone, fax_number, email, needs_reserve_p, max_people, description, package_id) values (venue_id, venue_name, address1, address2, city, usps_abbrev, postal_code, iso, phone_number, time_zone, fax_number, email, needs_reserve_p, max_people, description, package_id); return v_venue_id; end new; procedure del ( venue_id in events_venues.venue_id%TYPE ) is begin delete from events_venues where venue_id = del.venue_id; acs_object.delete(del.venue_id); end del; end events_venue; / show errors;