--- creating table to add section create table dotlrn_ecommerce_section ( section_id integer primary key, course_id integer references cr_items(item_id), community_id integer references dotlrn_communities_all(community_id), product_id integer references ec_products, section_name varchar(2000), age varchar(2000), date_time_start date, date_time_end date, daily_p char(1) check (daily_p in ('t','f')), weekly_p char(1) check (weekly_p in ('t','f')), qualified_age_low integer, qualified_age_high integer, account_code_revenue varchar(100), account_code_expense varchar(100), max_participants integer, waiting_list_p char(1) check (waiting_list_p in ('t','f')), notify_waiting_number integer, member_price_number numeric, non_member_price numeric, description text ); create index de_community_id_idx ON dotlrn_ecommerce_section (community_id); create index de_product_id_idx ON dotlrn_ecommerce_section (product_id); create index de_course_id_idx ON dotlrn_ecommerce_section (course_id); -- Term - comes from site-wide categories -- instructors - come from from instructors group -- asssistants - come from assistants group -- actual dates - comes from the calendar items in the community declare begin acs_rel_type.create_role('as_session_role', 'Assessment Sessions Role', 'Assessment Sessions Role'); acs_rel_type.create_role('ec_product_role', 'Ecommerce Product Role', 'Ecommerce Product Role'); acs_rel_type.create_role('member_rel_role', 'Member Relationship Role', 'Member Relationship Role'); commit; end; / show errors create table person_info ( person_id integer not null, grade clob, allergies clob, age integer, special_needs clob, CONSTRAINT dotlrn_ecommerce_persons_fk FOREIGN KEY (person_id) REFERENCES persons(person_id) ); create table dotlrn_ecommerce_orders ( item_id integer references ec_items on delete cascade not null, -- Can be a user or group patron_id integer references users(user_id) on delete cascade not null, participant_id integer references parties(party_id) on delete cascade not null ); create table dotlrn_ecommerce_transactions ( order_id integer references ec_orders on delete cascade not null unique, method char(1) check (method in ('cc', 'internal_account', 'check', 'cash', 'invoice', 'scholarship', 'lockbox')) not null, internal_account clob ); -- Create custom ecommerce field insert into ec_custom_product_fields (field_identifier, field_name, default_value, column_type, last_modified,last_modifying_user, modified_ip_address) values ('maxparticipants', 'Max Participants', '', 'integer', sysdate, '0', '0.0.0.0'); alter table ec_custom_product_field_values add maxparticipants integer; alter table ec_custom_p_field_values_audit add maxparticipants integer; insert into ec_custom_product_fields (field_identifier, field_name, default_value, column_type, last_modified,last_modifying_user, modified_ip_address) values ('show_description_p', 'Show Section Description', '', 'char(1)', now(), '0', '0.0.0.0'); alter table ec_custom_product_field_values add show_description_p char(1) constraint show_description_p_constraint check (show_description_p in ('t', 'f')); alter table ec_custom_p_field_values_audit add show_description_p char(1); @@ dotlrn-ecommerce-memberships-create.sql @@ dotlrn-ecommerce-admin-portlet-create.sql