-- -- ecommerce.sql -- -- by eveander@arsdigita.com, April 1999 -- -- Besides the tables defined here, you also need to import -- zip_codes, which contains the following columns: -- ZIP_CODE NOT NULL VARCHAR2(10) -- STATE_CODE CHAR(2) -- CITY_NAME VARCHAR2(32) -- COUNTY_NAME VARCHAR2(32) -- LONGITUDE NUMBER(9,6) -- LATITUDE NUMBER(9,6) -- Each table in ecommerce has a column for user_id, ip_address, -- creation_date and last_modified to assist in auditing user -- inserts, updates, and deletes. -- Audit tables store information about entries in the main ec_ tables. -- They have an column for each column of the main table, plus -- a column for marking when a logged entry was for a deleted row. -- product display templates create sequence ec_template_id_sequence start with 2; -- I should have named this product_templates because now we -- have other kinds of templates. create table ec_templates ( template_id integer not null primary key, template_name varchar(200), template varchar(4000), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_templates_audit ( template_id integer, template_name varchar(200), template varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- A trigger is used to move information from the main table to -- the audit table create or replace trigger ec_templates_audit_tr before update or delete on ec_templates for each row begin insert into ec_templates_audit ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( :old.template_id, :old.template_name, :old.template, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- This inserts the default template into the ec_templates table insert into ec_templates ( template_id, template_name, template, last_modified, last_modifying_user, modified_ip_address ) values ( 1,'Default', '' || CHR(10) || '<%= $product_name %>' || CHR(10) || '' || CHR(10) || '' || CHR(10) || CHR(10) || '

<%= $product_name %>

' || CHR(10) || CHR(10) || '' || CHR(10) || '' || CHR(10) || '' || CHR(10) || '' || CHR(10) || '' || CHR(10) || '
' || CHR(10) || ' ' || CHR(10) || ' ' || CHR(10) || ' ' || CHR(10) || ' ' || CHR(10) || ' ' || CHR(10) || '
<%= [ec_linked_thumbnail_if_it_exists $dirname] %>' || CHR(10) || ' <%= $one_line_description %>' || CHR(10) || '
' || CHR(10) || ' <%= [ec_price_line $db $product_id $user_id $offer_code] %>' || CHR(10) || '
' || CHR(10) || '		' || CHR(10) || '<%= [ec_add_to_cart_link $db $product_id] %>' || CHR(10) || '
' || CHR(10) || CHR(10) || '

' || CHR(10) || '<%= $detailed_description %>' || CHR(10) || CHR(10) || '<%= [ec_display_product_purchase_combinations $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_product_links_if_they_exist $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_professional_reviews_if_they_exist $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_customer_comments $db $product_id $comments_sort_by] %>' || CHR(10) || CHR(10) || '

' || CHR(10) || CHR(10) || '<%= [ec_mailing_list_link_for_a_product $db $product_id] %>' || CHR(10) || CHR(10) || '<%= [ec_footer $db] %>' || CHR(10) || '' || CHR(10) || '', sysdate, 1, 'none'); -- product categories and subcategories and subsubcategories create sequence ec_category_id_sequence; create table ec_categories ( category_id integer not null primary key, -- pretty, human-readable category_name varchar(100), sort_key number, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_categories_sort_idx on ec_categories (sort_key); create table ec_categories_audit ( category_id integer, category_name varchar(100), sort_key number, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_categories_audit_tr before update or delete on ec_categories for each row begin insert into ec_categories_audit ( category_id, category_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( :old.category_id, :old.category_name, :old.sort_key, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_subcategory_id_sequence; create table ec_subcategories ( subcategory_id integer not null primary key, category_id not null references ec_categories, -- pretty, human-readable subcategory_name varchar(100), sort_key number, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_subcategories_idx on ec_subcategories (category_id); create index ec_subcategories_idx2 on ec_subcategories (sort_key); create table ec_subcategories_audit ( subcategory_id integer, category_id integer, subcategory_name varchar(100), sort_key number, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_subcategories_audit_tr before update or delete on ec_subcategories for each row begin insert into ec_subcategories_audit ( subcategory_id, category_id, subcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( :old.subcategory_id, :old.category_id, :old.subcategory_name, :old.sort_key, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- a view with category_name also create or replace view ec_subcategories_augmented as select subs.*, cats.category_name from ec_subcategories subs, ec_categories cats where subs.category_id = cats.category_id; create sequence ec_subsubcategory_id_sequence; create table ec_subsubcategories ( subsubcategory_id integer not null primary key, subcategory_id not null references ec_subcategories, -- pretty, human-readable subsubcategory_name varchar(100), sort_key number, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_subsubcategories_idx on ec_subsubcategories (subcategory_id); create index ec_subsubcategories_idx2 on ec_subsubcategories (sort_key); create table ec_subsubcategories_audit ( subsubcategory_id integer, subcategory_id integer, subsubcategory_name varchar(100), sort_key number, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_subsubcategories_audit_tr before update or delete on ec_subsubcategories for each row begin insert into ec_subsubcategories_audit ( subsubcategory_id, subcategory_id, subsubcategory_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( :old.subsubcategory_id, :old.subcategory_id, :old.subsubcategory_name, :old.sort_key, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- a view with full subcategory and category info as well create or replace view ec_subsubcategories_augmented as select subsubs.*, subs.subcategory_name, cats.category_id, cats.category_name from ec_subsubcategories subsubs, ec_subcategories subs, ec_categories cats where subsubs.subcategory_id = subs.subcategory_id and subs.category_id = cats.category_id; create sequence ec_product_id_sequence start with 1; -- This table contains the products and also the product series. -- A product series has the same fields as a product (it actually -- *is* a product, since it's for sale, has its own price, etc.). -- The only difference is that it has other products associated -- with it (that are part of it). So information about the -- whole series is kept in this table and the product_series_map -- table below keeps track of which products are inside each -- series. create table ec_products ( product_id integer not null primary key, sku varchar(100), product_name varchar(200), creation_date date default sysdate not null, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), -- this is the regular price for the product. If user -- classes are charged a different price, it should be -- specified in ec_product_user_class_prices price number, -- for stuff that can't be shipped like services no_shipping_avail_p char(1) default 'f' check(no_shipping_avail_p in ('t', 'f')), -- leave this blank if shipping is calculated using -- one of the more complicated methods available shipping number, -- fill this in if shipping is calculated by: above price -- for first item (with this product_id), and the below -- price for additional items (with this product_id) shipping_additional number, -- fill this in if shipping is calculated using weight -- use whatever units you want (lbs/kg), just be consistent -- and make your shipping algorithm take the units into -- account weight number, -- holds pictures, sample chapters, etc. dirname varchar(200), -- whether this item should show up in searches (e.g., if it's -- a volume of a series, you might not want it to) present_p char(1) default 't' check (present_p in ('t','f')), -- whether the item should show up at all in the user pages active_p char(1) default 't' check (active_p in ('t','f')), -- the date the product becomes available for sale (it can be listed -- before then, it's just not buyable) available_date date default sysdate not null, announcements varchar(4000), announcements_expire date, -- if there's a web site with more info about the product url varchar(300), template_id references ec_templates, -- o = out of stock, q = ships quickly, m = ships -- moderately quickly, s = ships slowly, i = in stock -- with no message about the speed of the shipment (shipping -- messages are in parameters .ini file) stock_status char(1) check (stock_status in ('o','q','m','s','i')), -- comma-separated lists of available colors, sizes, and styles for the user -- to choose upon ordering color_list varchar(4000), size_list varchar(4000), style_list varchar(4000), -- email this list on purchase email_on_purchase_list varchar(4000), -- the user ID and IP address of the creator of the product last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create or replace view ec_products_displayable as select * from ec_products where active_p='t'; create or replace view ec_products_searchable as select * from ec_products where active_p='t' and present_p='t'; create table ec_products_audit ( product_id integer, product_name varchar(200), creation_date date, one_line_description varchar(400), detailed_description varchar(4000), search_keywords varchar(4000), price number, shipping number, shipping_additional number, weight number, dirname varchar(200), present_p char(1) default 't' check (present_p in ('t','f')), active_p char(1) default 't' check (active_p in ('t','f')), available_date date, announcements varchar(4000), announcements_expire date, url varchar(300), template_id integer, stock_status char(1) check (stock_status in ('o','q','m','s','i')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_products_audit_tr before update or delete on ec_products for each row begin insert into ec_products_audit ( product_id, product_name, creation_date, one_line_description, detailed_description, search_keywords, shipping, shipping_additional, weight, dirname, present_p, active_p, available_date, announcements, announcements_expire, url, template_id, stock_status, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.product_name, :old.creation_date, :old.one_line_description, :old.detailed_description, :old.search_keywords, :old.shipping, :old.shipping_additional, :old.weight, :old.dirname, :old.present_p, :old.active_p, :old.available_date, :old.announcements, :old.announcements_expire, :old.url, :old.template_id, :old.stock_status, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- people who bought product_id also bought products 0 through -- 4, where product_0 is the most frequently purchased, 1 is next, -- etc. create table ec_product_purchase_comb ( product_id integer not null primary key references ec_products, product_0 references ec_products, product_1 references ec_products, product_2 references ec_products, product_3 references ec_products, product_4 references ec_products ); create index ec_product_purchase_comb_idx0 on ec_product_purchase_comb(product_0); create index ec_product_purchase_comb_idx1 on ec_product_purchase_comb(product_1); create index ec_product_purchase_comb_idx2 on ec_product_purchase_comb(product_2); create index ec_product_purchase_comb_idx3 on ec_product_purchase_comb(product_3); create index ec_product_purchase_comb_idx4 on ec_product_purchase_comb(product_4); create sequence ec_sale_price_id_sequence start with 1; create table ec_sale_prices ( sale_price_id integer not null primary key, product_id not null references ec_products, sale_price number, sale_begins date not null, sale_ends date not null, -- like Introductory Price or Sale Price or Special Offer sale_name varchar(30), -- if non-null, the user has to know this code to get the sale price offer_code varchar(20), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_sale_prices_by_product_idx on ec_sale_prices(product_id); create or replace view ec_sale_prices_current as select * from ec_sale_prices where sysdate-sale_begins>=0 and sysdate-sale_ends<=0; create table ec_sale_prices_audit ( sale_price_id integer, product_id integer, sale_price number, sale_begins date, sale_ends date, sale_name varchar(30), offer_code varchar(20), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_sale_prices_audit_tr before update or delete on ec_sale_prices for each row begin insert into ec_sale_prices_audit ( sale_price_id, product_id, sale_price, sale_begins, sale_ends, sale_name, offer_code, last_modified, last_modifying_user, modified_ip_address ) values ( :old.sale_price_id, :old.product_id, :old.sale_price, :old.sale_begins, :old.sale_ends, :old.sale_name, :old.offer_code, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create table ec_product_series_map ( -- this is the product_id of a product that happens to be -- a series series_id not null references ec_products, -- this is the product_id of a product that is one of the -- components of the above series component_id not null references ec_products, primary key (series_id, component_id), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_product_series_map_idx2 on ec_product_series_map(component_id); create table ec_product_series_map_audit ( series_id integer, component_id integer, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_series_map_audit_tr before update or delete on ec_product_series_map for each row begin insert into ec_product_series_map_audit ( series_id, component_id, last_modified, last_modifying_user, modified_ip_address ) values ( :old.series_id, :old.component_id, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_address_id_sequence start with 1; create table ec_addresses ( address_id integer not null primary key, user_id not null references users, address_type varchar(20) not null, -- e.g., billing attn varchar(100), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), -- for international addresses country_code char(2) references country_codes(iso), -- this can be the province or region for an international address full_state_name varchar(30), -- D for day, E for evening phone_time varchar(10) ); create index ec_addresses_by_user_idx on ec_addresses (user_id); create sequence ec_creditcard_id_sequence start with 1; create table ec_creditcards ( creditcard_id integer not null primary key, user_id not null references users, -- Cybercash does not ask for this but we'll store it anyway creditcard_type char(1), -- no spaces; always 16 digits (oops; except for AMEX, which is 15) -- depending on admin settings, after we get success from Cybercash, -- we may bash this to NULL -- we make this a VARCHAR so it doesn't get padded by Oracle creditcard_number varchar(16), -- just the last four digits for subsequent UI creditcard_last_four char(4), -- ##/## creditcard_expire char(5), billing_zip_code varchar(80), -- if it ever failed (conclusively), set this to 't' so we -- won't give them the option of using it again failed_p char(1) default 'f' check (failed_p in ('t','f')) ); create index ec_creditcards_by_user_idx on ec_creditcards (user_id); create sequence ec_user_class_id_sequence start with 1; create table ec_user_classes ( user_class_id integer not null primary key, -- human-readable user_class_name varchar(200), -- e.g., student last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_user_classes_audit ( user_class_id integer, user_class_name varchar(200), -- e.g., student last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_user_classes_audit_tr before update or delete on ec_user_classes for each row begin insert into ec_user_classes_audit ( user_class_id, user_class_name, last_modified, last_modifying_user, modified_ip_address ) values ( :old.user_class_id, :old.user_class_name, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create table ec_product_user_class_prices ( product_id not null references ec_products, user_class_id not null references ec_user_classes, price number, primary key (product_id, user_class_id), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_product_user_class_idx on ec_product_user_class_prices(user_class_id); -- ec_product_user_class_prices_audit abbreviated as -- ec_product_u_c_prices_audit create table ec_product_u_c_prices_audit ( product_id integer, user_class_id integer, price number, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_u_c_prices_audit_tr before update or delete on ec_product_user_class_prices for each row begin insert into ec_product_u_c_prices_audit ( product_id, user_class_id, price, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.user_class_id, :old.price, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_recommendation_id_sequence start with 1; create table ec_product_recommendations ( recommendation_id integer not null primary key, product_id not null references ec_products, -- might be null if the product is recommended for everyone user_class_id references ec_user_classes, -- html format recommendation_text varchar(4000), active_p char(1) default 't' check (active_p in ('t','f')), -- where it's displayed (top level if all three are blank): category_id references ec_categories, subcategory_id references ec_subcategories, subsubcategory_id references ec_subsubcategories, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_product_recommendation_idx on ec_product_recommendations(category_id); create index ec_product_recommendation_idx2 on ec_product_recommendations(subcategory_id); create index ec_product_recommendation_idx3 on ec_product_recommendations(subsubcategory_id); create index ec_product_recommendation_idx4 on ec_product_recommendations(user_class_id); create index ec_product_recommendation_idx5 on ec_product_recommendations(active_p); create table ec_product_recommend_audit ( recommendation_id integer, product_id integer, user_class_id integer, recommendation_text varchar(4000), active_p char(1) default 't' check (active_p in ('t','f')), category_id integer, subcategory_id integer, subsubcategory_id integer, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_recommend_audit_tr before update or delete on ec_product_recommendations for each row begin insert into ec_product_recommend_audit ( recommendation_id, product_id, user_class_id, recommendation_text, active_p, category_id, subcategory_id, subsubcategory_id, last_modified, last_modifying_user, modified_ip_address ) values ( :old.recommendation_id, :old.product_id, :old.user_class_id, :old.recommendation_text, :old.active_p, :old.category_id, :old.subcategory_id, :old.subsubcategory_id, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create or replace view ec_recommendations_cats_view as select recs.*, nvl(cats.category_id,nvl(subs.category_id,subsubs.category_id)) as the_category_id, nvl(cats.category_name,nvl(subs.category_name,subsubs.category_name)) as the_category_name, nvl(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, nvl(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, subsubs.subsubcategory_id as the_subsubcategory_id, subsubs.subsubcategory_name as the_subsubcategory_name from ec_product_recommendations recs, ec_categories cats, ec_subcategories_augmented subs, ec_subsubcategories_augmented subsubs where recs.category_id = cats.category_id(+) and recs.subcategory_id = subs.subcategory_id(+) and recs.subsubcategory_id = subsubs.subcategory_id(+); -- one row per customer-user; all the extra info that the ecommerce -- system needs create table ec_user_class_user_map ( user_id not null references users, user_class_id not null references ec_user_classes, primary key (user_id, user_class_id), user_class_approved_p char(1) check (user_class_approved_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_user_class_user_map_idx on ec_user_class_user_map (user_class_id); create index ec_user_class_user_map_idx2 on ec_user_class_user_map (user_class_approved_p); create table ec_user_class_user_map_audit ( user_id integer, user_class_id integer, user_class_approved_p char(1) check (user_class_approved_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_user_class_user_audit_tr before update or delete on ec_user_class_user_map for each row begin insert into ec_user_class_user_map_audit ( user_id, user_class_id, user_class_approved_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.user_id, :old.user_class_id, :old.user_class_approved_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- this specifies that product_a links to product_b on the display page for product_a create table ec_product_links ( product_a not null references ec_products, product_b not null references ec_products, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null, primary key (product_a, product_b) ); create index ec_product_links_idx on ec_product_links (product_b); create table ec_product_links_audit ( product_a integer, product_b integer, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_links_audit_tr before update or delete on ec_product_links for each row begin insert into ec_product_links_audit ( product_a, product_b, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_a, :old.product_b, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_product_comment_id_sequence start with 1; -- comments made by users on the products create table ec_product_comments ( comment_id integer not null primary key, product_id not null references ec_products, user_id not null references users, user_comment varchar(4000), one_line_summary varchar(300), rating number, -- in some systems, the administrator will have to approve comments first approved_p char(1) check (approved_p in ('t','f')), comment_date date, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_product_comments_idx on ec_product_comments(product_id); create index ec_product_comments_idx2 on ec_product_comments(user_id); create index ec_product_comments_idx3 on ec_product_comments(approved_p); create table ec_product_comments_audit ( comment_id integer, product_id integer, user_id integer, user_comment varchar(4000), one_line_summary varchar(300), rating number, approved_p char(1), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_comments_audit_tr before update or delete on ec_product_comments for each row begin insert into ec_product_comments_audit ( comment_id, product_id, user_id, user_comment, one_line_summary, rating, approved_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.comment_id, :old.product_id, :old.user_id, :old.user_comment, :old.one_line_summary, :old.rating, :old.approved_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_product_review_id_sequence start with 1; -- reviews made by professionals of the products create table ec_product_reviews ( review_id integer not null primary key, product_id not null references ec_products, author_name varchar(100), publication varchar(100), review_date date, -- in HTML format review clob, display_p char(1) check (display_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create index ec_product_reviews_idx on ec_product_reviews (product_id); create index ec_product_reviews_idx2 on ec_product_reviews (display_p); create table ec_product_reviews_audit ( review_id integer, product_id integer, author_name varchar(100), publication varchar(100), review_date date, -- in HTML format review clob, display_p char(1) check (display_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_product_reviews_audit_tr before update or delete on ec_product_reviews for each row begin insert into ec_product_reviews_audit ( review_id, product_id, author_name, publication, review_date, review, display_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.review_id, :old.product_id, :old.author_name, :old.publication, :old.review_date, :old.review, :old.display_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- a product can be in more than one category create table ec_category_product_map ( product_id not null references ec_products, category_id not null references ec_categories, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null, primary key (product_id, category_id) ); create index ec_category_product_map_idx on ec_category_product_map (category_id); create index ec_category_product_map_idx2 on ec_category_product_map (publisher_favorite_p); create table ec_category_product_map_audit ( product_id integer, category_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- ec_category_product_map_audit_tr abbreviated as -- ec_cat_prod_map_audit_tr create or replace trigger ec_cat_prod_map_audit_tr before update or delete on ec_category_product_map for each row begin insert into ec_category_product_map_audit ( product_id, category_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.category_id, :old.publisher_favorite_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create table ec_subcategory_product_map ( product_id not null references ec_products, subcategory_id not null references ec_subcategories, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subcategory_id) ); create index ec_subcat_product_map_idx on ec_subcategory_product_map (subcategory_id); create index ec_subcat_product_map_idx2 on ec_subcategory_product_map (publisher_favorite_p); -- ec_subcategory_product_map_audit abbreviated as create table ec_subcat_prod_map_audit ( product_id integer, subcategory_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); -- ec_subcat_prod_map_audit_tr create or replace trigger ec_subcat_prod_map_audit_tr before update or delete on ec_subcategory_product_map for each row begin insert into ec_subcat_prod_map_audit ( product_id, subcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.subcategory_id, :old.publisher_favorite_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create table ec_subsubcategory_product_map ( product_id not null references ec_products, subsubcategory_id not null references ec_subsubcategories, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null, primary key (product_id, subsubcategory_id) ); create index ec_subsubcat_prod_map_idx on ec_subsubcategory_product_map (subsubcategory_id); create index ec_subsubcat_prod_map_idx2 on ec_subsubcategory_product_map (publisher_favorite_p); create table ec_subsubcat_prod_map_audit ( product_id integer, subsubcategory_id integer, publisher_favorite_p char(1) check (publisher_favorite_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_subsubcat_prod_map_audit_tr before update or delete on ec_subsubcategory_product_map for each row begin insert into ec_subsubcat_prod_map_audit ( product_id, subsubcategory_id, publisher_favorite_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.subsubcategory_id, :old.publisher_favorite_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- A template can have more than 1 category associated -- with it, but a category can have at most one template. -- When a product is added in a given category, its template -- will default to the one associated with its category (if -- the product is in more than 1 category, it'll get the -- template associated with one of its categories), although -- the admin can always associate a product with any category -- they want. create table ec_category_template_map ( category_id not null primary key references ec_categories, template_id not null references ec_templates ); create index ec_category_template_map_idx on ec_category_template_map (template_id); -- I could in theory make some hairy system that lets them specify -- what kind of form element each field will have, does -- error checking, etc., but I don't think it's necessary since it's -- just the site administrator using it. So here's a very simple -- table to store the custom product fields: create table ec_custom_product_fields ( field_identifier varchar(100) not null primary key, field_name varchar(100), default_value varchar(100), -- column type for oracle (i.e. text, varchar(50), integer, ...) column_type varchar(100), creation_date date, active_p char(1) default 't' check (active_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_product_fields_audit ( field_identifier varchar(100), field_name varchar(100), default_value varchar(100), column_type varchar(100), creation_date date, active_p char(1) default 't' check (active_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_custom_prod_fields_audit_tr before update or delete on ec_custom_product_fields for each row begin insert into ec_custom_product_fields_audit ( field_identifier, field_name, default_value, column_type, creation_date, active_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.field_identifier, :old.field_name, :old.default_value, :old.column_type, :old.creation_date, :old.active_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- more columns are added to this table (by Tcl scripts) when the -- administrator adds custom product fields -- the columns in this table have the name of the field_identifiers -- in ec_custom_product_fields -- this table stores the values create table ec_custom_product_field_values ( product_id not null primary key references ec_products, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_custom_p_field_values_audit ( product_id integer, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_custom_p_f_values_audit_tr before update or delete on ec_custom_product_field_values for each row begin insert into ec_custom_p_field_values_audit ( product_id, last_modified, last_modifying_user, modified_ip_address ) values ( :old.product_id, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_user_session_sequence; create table ec_user_sessions ( user_session_id integer not null primary key, -- often will not be known user_id references users, ip_address varchar(20) not null, start_time date, http_user_agent varchar(4000) ); create index ec_user_sessions_idx on ec_user_sessions(user_id); create table ec_user_session_info ( user_session_id not null references ec_user_sessions, product_id references ec_products, category_id references ec_categories, search_text varchar(200) ); create index ec_user_session_info_idx on ec_user_session_info (user_session_id); create index ec_user_session_info_idx2 on ec_user_session_info (product_id); create index ec_user_session_info_idx3 on ec_user_session_info (category_id); -- If a user comes to product.tcl with an offer_code in the url, -- I'm going to shove it into this table and then check this -- table each time I try to determine the price for the users' -- products. The alternative is to store the offer_codes in a -- cookie and look at that each time I try to determine the price -- for a product. But I think this will be a little faster. create table ec_user_session_offer_codes ( user_session_id not null references ec_user_sessions, product_id not null references ec_products, offer_code varchar(20) not null, primary key (user_session_id, product_id) ); -- create some indices create index ec_u_s_offer_codes_by_u_s_id on ec_user_session_offer_codes(user_session_id); create index ec_u_s_offer_codes_by_p_id on ec_user_session_offer_codes(product_id); create sequence ec_order_id_sequence start with 3000000; create table ec_orders ( order_id integer not null primary key, -- can be null, until they've checked out or saved their basket user_id references users, user_session_id references ec_user_sessions, order_state varchar(50) default 'in_basket' not null, tax_exempt_p char(1) default 'f' check(tax_exempt_p in ('t', 'f')), shipping_method varchar(20), -- express or standard or pickup or 'no shipping' shipping_address integer references ec_addresses(address_id), -- store credit card info in a different table creditcard_id integer references ec_creditcards(creditcard_id), -- information recorded upon FSM state changes -- we need this to figure out if order is stale -- and should be offered up for removal in_basket_date date, confirmed_date date, authorized_date date, voided_date date, expired_date date, -- base shipping, which is added to the amount charged for each item shipping_charged number, shipping_refunded number, shipping_tax_charged number, shipping_tax_refunded number, -- entered by customer service cs_comments varchar(4000), reason_for_void varchar(4000), voided_by references users, -- if the user chooses to save their shopping cart saved_p char(1) check (saved_p in ('t','f')), check (user_id is not null or user_session_id is not null) ); create index ec_orders_by_user_idx on ec_orders (user_id); create index ec_orders_by_user_sess_idx on ec_orders (user_session_id); create index ec_orders_by_credit_idx on ec_orders (creditcard_id); create index ec_orders_by_addr_idx on ec_orders (shipping_address); create index ec_orders_by_conf_idx on ec_orders (confirmed_date); create index ec_orders_by_state_idx on ec_orders (order_state); -- note that an order could essentially become uninteresting for financial -- accounting if all the items underneath it are individually voided or returned create or replace view ec_orders_reportable as select * from ec_orders where order_state <> 'in_basket' and order_state <> 'void'; -- orders that have items which still need to be shipped create or replace view ec_orders_shippable as select * from ec_orders where order_state in ('authorized_plus_avs','authorized_minus_avs','partially_fulfilled'); -- this is needed because orders might be only partially shipped create sequence ec_shipment_id_sequence; create table ec_shipments ( shipment_id integer not null primary key, order_id not null references ec_orders, -- usually, but not necessarily, the same as the shipping_address -- in ec_orders because a customer may change their address between -- shipments. -- a trigger fills address_id in automatically if it's null address_id not null references ec_addresses, shipment_date date not null, expected_arrival_date date, carrier varchar(50), -- e.g., 'fedex' tracking_number varchar(24), -- only if we get confirmation from carrier that the goods -- arrived on a specific date actual_arrival_date date, -- arbitrary info from carrier, e.g., 'Joe Smith signed for it' actual_arrival_detail varchar(4000), -- for things that aren't really shipped like services shippable_p char(1) default 't' check(shippable_p in ('t', 'f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20) ); create index ec_shipments_by_order_id on ec_shipments(order_id); create index ec_shipments_by_shipment_date on ec_shipments(shipment_date); -- fills address_id into ec_shipments if it's missing -- (using the shipping_address associated with the order) create or replace trigger ec_shipment_address_update_tr before insert on ec_shipments for each row declare v_address_id ec_addresses.address_id%TYPE; begin select shipping_address into v_address_id from ec_orders where order_id=:new.order_id; IF :new.address_id is null THEN :new.address_id := v_address_id; END IF; end; / show errors create table ec_shipments_audit ( shipment_id integer, order_id integer, address_id integer, shipment_date date, expected_arrival_date date, carrier varchar(50), tracking_number varchar(24), actual_arrival_date date, actual_arrival_detail varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_shipments_audit_tr before update or delete on ec_shipments for each row begin insert into ec_shipments_audit ( shipment_id, order_id, address_id, shipment_date, expected_arrival_date, carrier, tracking_number, actual_arrival_date, actual_arrival_detail, last_modified, last_modifying_user, modified_ip_address ) values ( :old.shipment_id, :old.order_id, :old.address_id, :old.shipment_date, :old.expected_arrival_date, :old.carrier, :old.tracking_number, :old.actual_arrival_date, :old.actual_arrival_detail, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence refund_id_sequence; create table ec_refunds ( refund_id integer not null primary key, order_id not null references ec_orders, -- not really necessary because it's in ec_financial_transactions refund_amount number not null, refund_date date not null, refunded_by not null references users, refund_reasons varchar(4000) ); create index ec_refunds_by_order_idx on ec_refunds (order_id); -- these are the items that make up each order create sequence ec_item_id_sequence start with 1; create table ec_items ( item_id integer not null primary key, order_id not null references ec_orders, product_id not null references ec_products, color_choice varchar(4000), size_choice varchar(4000), style_choice varchar(4000), shipment_id references ec_shipments, -- this is the date that user put this item into their shopping basket in_cart_date date, voided_date date, voided_by references users, expired_date date, item_state varchar(50) default 'in_basket', -- NULL if not received back received_back_date date, -- columns for reporting (e.g., what was done, what was made) price_charged number, price_refunded number, shipping_charged number, shipping_refunded number, price_tax_charged number, price_tax_refunded number, shipping_tax_charged number, shipping_tax_refunded number, -- like Our Price or Sale Price or Introductory Price price_name varchar(30), -- did we go through a merchant-initiated refund? refund_id references ec_refunds, -- comments entered by customer service (CS) cs_comments varchar(4000) ); create index ec_items_by_product on ec_items(product_id); create index ec_items_by_order on ec_items(order_id); create index ec_items_by_shipment on ec_items(shipment_id); create or replace view ec_items_reportable as select * from ec_items where item_state in ('to_be_shipped', 'shipped', 'arrived'); create or replace view ec_items_refundable as select * from ec_items where item_state in ('shipped','arrived') and refund_id is null; create or replace view ec_items_shippable as select * from ec_items where item_state in ('to_be_shipped'); -- This view displays: -- order_id -- shipment_date -- bal_price_charged sum(price_charged - price_refunded) for all items in the shipment -- bal_shipping_charged -- bal_tax_charged -- The purpose: payment is recognized when an item ships so this sums the various -- parts of payment (price, shipping, tax) for all the items in each shipment create or replace view ec_items_money_view as select i.shipment_id, i.order_id, s.shipment_date, nvl(sum(i.price_charged),0) - nvl(sum(i.price_refunded),0) as bal_price_charged, nvl(sum(i.shipping_charged),0) - nvl(sum(i.shipping_refunded),0) as bal_shipping_charged, nvl(sum(i.price_tax_charged),0) - nvl(sum(i.price_tax_refunded),0) + nvl(sum(i.shipping_tax_charged),0) - nvl(sum(i.shipping_tax_refunded),0) as bal_tax_charged from ec_items i, ec_shipments s where i.shipment_id=s.shipment_id and i.item_state <> 'void' group by i.order_id, i.shipment_id, s.shipment_date; -- a set of triggers to update order_state based on what happens -- to the items in the order -- partially_fulfilled: some but not all non-void items have shipped -- fulfilled: all non-void items have shipped -- returned: all non-void items received_back -- void: all items void -- We're not interested in partial returns. -- this is hellish because you can't select a count of the items -- in a given item_state from ec_items when you're updating ec_items, -- so we have to do a horrid "trio" (temporary table, row level trigger, -- system level trigger) as discussed in -- http://photo.net/doc/site-wide-search.html (we use a temporary -- table instead of a package because they're better) -- I. temporary table to hold the order_ids that have to have their -- state updated as a result of the item_state changes create global temporary table ec_state_change_order_ids ( order_id integer ); -- II. row-level trigger which updates ec_state_change_order_ids -- so we know which rows to update in ec_orders create or replace trigger ec_order_state_before_tr before update on ec_items for each row begin insert into ec_state_change_order_ids (order_id) values (:new.order_id); end; / show errors -- III. System level trigger to update all the rows that were changed -- in the before trigger. create or replace trigger ec_order_state_after_tr after update on ec_items declare CURSOR orders_cursor IS SELECT unique order_id FROM ec_state_change_order_ids; v_order_id ec_orders.order_id%TYPE; n_items integer; n_shipped_items integer; n_received_back_items integer; n_void_items integer; n_nonvoid_items integer; begin FOR orders_cursor_rec IN orders_cursor LOOP v_order_id := orders_cursor_rec.order_id; select count(*) into n_items from ec_items where order_id=v_order_id; select count(*) into n_shipped_items from ec_items where order_id=v_order_id and item_state='shipped' or item_state='arrived'; select count(*) into n_received_back_items from ec_items where order_id=v_order_id and item_state='received_back'; select count(*) into n_void_items from ec_items where order_id=v_order_id and item_state='void'; IF n_items = n_void_items THEN update ec_orders set order_state='void', voided_date=sysdate where order_id=v_order_id; ELSE n_nonvoid_items := n_items - n_void_items; IF n_nonvoid_items = n_received_back_items THEN update ec_orders set order_state='returned' where order_id=v_order_id; ELSIF n_nonvoid_items = n_received_back_items + n_shipped_items THEN update ec_orders set order_state='fulfilled' where order_id=v_order_id; ELSIF n_shipped_items >= 1 or n_received_back_items >=1 THEN update ec_orders set order_state='partially_fulfilled' where order_id=v_order_id; END IF; END IF; END LOOP; delete from ec_state_change_order_ids; end; / show errors -- this is a 1-row table -- it contains all settings that the admin can change from the admin pages -- most of the configuration is done using the parameters .ini file create table ec_admin_settings ( -- this is here just so that the insert statement (a page or -- so down) can't be executed twice admin_setting_id integer not null primary key, -- the following columns are related to shipping costs base_shipping_cost number, default_shipping_per_item number, weight_shipping_cost number, add_exp_base_shipping_cost number, add_exp_amount_per_item number, add_exp_amount_by_weight number, -- default template to use if the product isn't assigned to one -- (until the admin changes it, it will be 1, which will be -- the preloaded template) default_template integer default 1 not null references ec_templates, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_admin_settings_audit ( admin_setting_id integer, base_shipping_cost number, default_shipping_per_item number, weight_shipping_cost number, add_exp_base_shipping_cost number, add_exp_amount_per_item number, add_exp_amount_by_weight number, default_template integer, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_admin_settings_audit_tr before update or delete on ec_admin_settings for each row begin insert into ec_admin_settings_audit ( admin_setting_id, base_shipping_cost, default_shipping_per_item, weight_shipping_cost, add_exp_base_shipping_cost, add_exp_amount_per_item, add_exp_amount_by_weight, default_template, last_modified, last_modifying_user, modified_ip_address ) values ( :old.admin_setting_id, :old.base_shipping_cost, :old.default_shipping_per_item, :old.weight_shipping_cost, :old.add_exp_base_shipping_cost, :old.add_exp_amount_per_item, :old.add_exp_amount_by_weight, :old.default_template, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- put one row into ec_admin_settings so that I don't have to use 0or1row insert into ec_admin_settings ( admin_setting_id, default_template, last_modified, last_modifying_user, modified_ip_address ) values ( 1, 1, sysdate, 1, 'none'); -- this is populated by the rules the administrator sets in /admin/ecommerce/sales-tax.tcl create table ec_sales_tax_by_state ( usps_abbrev not null primary key references states, -- this a decimal number equal to the percentage tax divided by 100 tax_rate number not null, -- charge tax on shipping? shipping_p char(1) not null check (shipping_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_sales_tax_by_state_audit ( usps_abbrev char(2), tax_rate number, shipping_p char(1) check (shipping_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_sales_tax_by_state_audit_tr before update or delete on ec_sales_tax_by_state for each row begin insert into ec_sales_tax_by_state_audit ( usps_abbrev, tax_rate, shipping_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.usps_abbrev, :old.tax_rate, :old.shipping_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- these tables are used if MultipleRetailersPerProductP is 1 in the -- parameters .ini file create sequence ec_retailer_sequence start with 1; create table ec_retailers ( retailer_id integer not null primary key, retailer_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), fax varchar(30), -- for international addresses country_code char(2) references country_codes(iso), --national, local, international reach varchar(15) check (reach in ('national','local','international','regional','web')), url varchar(200), -- space-separated list of states in which tax must be collected nexus_states varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_retailers_audit ( retailer_id integer, retailer_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), usps_abbrev char(2), zip_code varchar(10), phone varchar(30), fax varchar(30), country_code char(2), reach varchar(15) check (reach in ('national','local','international','regional','web')), url varchar(200), nexus_states varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_retailers_audit_tr before update or delete on ec_retailers for each row begin insert into ec_retailers_audit ( retailer_id, retailer_name, primary_contact_name, secondary_contact_name, primary_contact_info, secondary_contact_info, line1, line2, city, usps_abbrev, zip_code, phone, fax, country_code, reach, url, nexus_states, financing_policy, return_policy, price_guarantee_policy, delivery_policy, installation_policy, last_modified, last_modifying_user, modified_ip_address ) values ( :old.retailer_id, :old.retailer_name, :old.primary_contact_name, :old.secondary_contact_name, :old.primary_contact_info, :old.secondary_contact_info, :old.line1, :old.line2, :old.city, :old.usps_abbrev, :old.zip_code, :old.phone, :old.fax, :old.country_code, :old.reach, :old.url, :old.nexus_states, :old.financing_policy, :old.return_policy, :old.price_guarantee_policy, :old.delivery_policy, :old.installation_policy, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_retailer_location_sequence start with 1; create table ec_retailer_locations ( retailer_location_id integer not null primary key, retailer_id not null references ec_retailers, location_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), -- state usps_abbrev char(2) references states, -- big enough to hold zip+4 with dash zip_code varchar(10), phone varchar(30), fax varchar(30), -- for international addresses country_code char(2) references country_codes(iso), url varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_retailer_locations_audit ( retailer_location_id integer, retailer_id integer, location_name varchar(300), primary_contact_name varchar(100), secondary_contact_name varchar(100), primary_contact_info varchar(4000), secondary_contact_info varchar(4000), line1 varchar(100), line2 varchar(100), city varchar(100), usps_abbrev char(2), zip_code varchar(10), phone varchar(30), fax varchar(30), country_code char(2), url varchar(200), financing_policy varchar(4000), return_policy varchar(4000), price_guarantee_policy varchar(4000), delivery_policy varchar(4000), installation_policy varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_retailer_locations_audit_tr before update or delete on ec_retailer_locations for each row begin insert into ec_retailer_locations_audit ( retailer_location_id, retailer_id, location_name, primary_contact_name, secondary_contact_name, primary_contact_info, secondary_contact_info, line1, line2, city, usps_abbrev, zip_code, phone, fax, country_code, url, financing_policy, return_policy, price_guarantee_policy, delivery_policy, installation_policy, last_modified, last_modifying_user, modified_ip_address ) values ( :old.retailer_location_id, :old.retailer_id, :old.location_name, :old.primary_contact_name, :old.secondary_contact_name, :old.primary_contact_info, :old.secondary_contact_info, :old.line1, :old.line2, :old.city, :old.usps_abbrev, :old.zip_code, :old.phone, :old.fax, :old.country_code, :old.url, :old.financing_policy, :old.return_policy, :old.price_guarantee_policy, :old.delivery_policy, :old.installation_policy, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create sequence ec_offer_sequence start with 1; create table ec_offers ( offer_id integer not null primary key, product_id not null references ec_products, retailer_location_id not null references ec_retailer_locations, store_sku integer, retailer_premiums varchar(500), price number not null, shipping number, shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), -- o = out of stock, q = ships quickly, m = ships -- moderately quickly, s = ships slowly, i = in stock -- with no message about the speed of the shipment (shipping -- messages are in parameters .ini file) stock_status char(1) check (stock_status in ('o','q','m','s','i')), special_offer_p char(1) check (special_offer_p in ('t','f')), special_offer_html varchar(500), offer_begins date not null, offer_ends date not null, deleted_p char(1) default 'f' check (deleted_p in ('t','f')), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create or replace view ec_offers_current as select * from ec_offers where deleted_p='f' and sysdate-offer_begins>=0 and sysdate-offer_ends<=0; create table ec_offers_audit ( offer_id integer, product_id integer, retailer_location_id integer, store_sku integer, retailer_premiums varchar(500), price number, shipping number, shipping_unavailable_p char(1) check (shipping_unavailable_p in ('t','f')), stock_status char(1) check (stock_status in ('o','q','m','s','i')), special_offer_p char(1) check (special_offer_p in ('t','f')), special_offer_html varchar(500), offer_begins date, offer_ends date, deleted_p char(1) default 'f' check (deleted_p in ('t','f')), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), -- This differs from the deleted_p column! -- deleted_p refers to the user request to stop offering -- delete_p indicates the row has been deleted from the main offers table delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_offers_audit_tr before update or delete on ec_offers for each row begin insert into ec_offers_audit ( offer_id, product_id, retailer_location_id, store_sku, retailer_premiums, price, shipping, shipping_unavailable_p, stock_status, special_offer_p, special_offer_html, offer_begins, offer_ends, deleted_p, last_modified, last_modifying_user, modified_ip_address ) values ( :old.offer_id, :old.product_id, :old.retailer_location_id, :old.store_sku, :old.retailer_premiums, :old.price, :old.shipping, :old.shipping_unavailable_p, :old.stock_status, :old.special_offer_p, :old.special_offer_html, :old.offer_begins, :old.offer_ends, :old.deleted_p, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- Gift certificate stuff ---- ------------------------------ create sequence ec_gift_cert_id_sequence start with 1000000; create table ec_gift_certificates ( gift_certificate_id integer primary key, gift_certificate_state varchar(50) not null, amount number not null, -- a trigger will update this to f if the -- entire amount is used up (to speed up -- queries) amount_remaining_p char(1) default 't' check (amount_remaining_p in ('t','f')), issue_date date, authorized_date date, claimed_date date, -- customer service rep who issued it issued_by references users, -- customer who purchased it purchased_by references users, expires date, user_id references users, -- if it's unclaimed, claim_check will be filled in, -- and user_id won't be filled in -- claim check should be unique (one way to do this -- is to always begin it with "$gift_certificate_id-") claim_check varchar(50), certificate_message varchar(200), certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), voided_date date, voided_by references users, reason_for_void varchar(4000), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null, check (user_id is not null or claim_check is not null) ); create index ec_gc_by_state on ec_gift_certificates(gift_certificate_state); create index ec_gc_by_amount_remaining on ec_gift_certificates(amount_remaining_p); create index ec_gc_by_user on ec_gift_certificates(user_id); create index ec_gc_by_claim_check on ec_gift_certificates(claim_check); -- note: there's a trigger in ecommerce-plsql.sql which updates amount_remaining_p -- when a gift certificate is used -- note2: there's a 1-1 correspondence between user-purchased gift certificates -- and financial transactions. ec_financial_transactions stores the corresponding -- gift_certificate_id. create or replace view ec_gift_certificates_approved as select * from ec_gift_certificates where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); create or replace view ec_gift_certificates_purchased as select * from ec_gift_certificates where gift_certificate_state in ('authorized_plus_avs','authorized_minus_avs'); create or replace view ec_gift_certificates_issued as select * from ec_gift_certificates where gift_certificate_state in ('authorized') and issued_by is not null; create table ec_gift_certificates_audit ( gift_certificate_id integer, gift_certificate_state varchar(50), amount number, issue_date date, authorized_date date, issued_by integer, purchased_by integer, expires date, user_id integer, claim_check varchar(50), certificate_message varchar(200), certificate_to varchar(100), certificate_from varchar(100), recipient_email varchar(100), voided_date date, voided_by integer, reason_for_void varchar(4000), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_gift_certificates_audit_tr before update or delete on ec_gift_certificates for each row begin insert into ec_gift_certificates_audit ( gift_certificate_id, amount, issue_date, authorized_date, issued_by, purchased_by, expires, user_id, claim_check, certificate_message, certificate_to, certificate_from, recipient_email, voided_date, voided_by, reason_for_void, last_modified, last_modifying_user, modified_ip_address ) values ( :old.gift_certificate_id, :old.amount, :old.issue_date, :old.authorized_date, :old.issued_by, :old.purchased_by, :old.expires, :old.user_id, :old.claim_check, :old.certificate_message, :old.certificate_to, :old.certificate_from, :old.recipient_email, :old.voided_date, :old.voided_by, :old.reason_for_void, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors create table ec_gift_certificate_usage ( gift_certificate_id not null references ec_gift_certificates, order_id references ec_orders, amount_used number, used_date date, amount_reinstated number, reinstated_date date ); create index ec_gift_cert_by_id on ec_gift_certificate_usage (gift_certificate_id); --------- customer service -------------------- create sequence ec_issue_id_sequence; create sequence ec_action_id_sequence; create sequence ec_interaction_id_sequence; create sequence ec_user_ident_id_sequence; -- this contains the bits of info a cs rep uses to identify -- a user -- often user_id is not known and the customer service rep -- will have to get other info in order to identify the user create table ec_user_identification ( user_identification_id integer not null primary key, date_added date, user_id references users, email varchar(100), first_names varchar(100), last_name varchar(100), -- this is varchar(80) in community-core.sql, so I'll be consistent postal_code varchar(80), other_id_info varchar(2000) ); -- should index everything because this all columns may potentially -- be searched through every time a new interaction is recorded create index ec_user_ident_by_user_id on ec_user_identification(user_id); create index ec_user_ident_by_email on ec_user_identification(email); create index ec_user_ident_by_first_names on ec_user_identification(first_names); create index ec_user_ident_by_last_name on ec_user_identification(last_name); create index ec_user_ident_by_postal_code on ec_user_identification(postal_code); -- puts date_added into ec_user_identification if it's missing create or replace trigger ec_user_identificate_date_tr before insert on ec_user_identification for each row begin IF :new.date_added is null THEN :new.date_added := sysdate; END IF; end; / show errors create table ec_customer_serv_interactions ( interaction_id integer not null primary key, customer_service_rep references users, user_identification_id not null references ec_user_identification, interaction_date date, interaction_originator varchar(20) not null, -- e.g. customer, customer-service-rep, automatic interaction_type varchar(30) not null, -- e.g. email, phone_call -- will be filled in if the customer-originated interaction is -- an email interaction_headers varchar(4000) ); create index ec_csin_by_user_ident_id on ec_customer_serv_interactions(user_identification_id); create or replace trigger ec_cs_interaction_inserts before insert on ec_customer_serv_interactions for each row begin IF INSERTING AND :new.interaction_date is null THEN :new.interaction_date := sysdate; END IF; end; / show errors create or replace view ec_customer_service_reps as select * from users where user_id in (select customer_service_rep from ec_customer_serv_interactions) or user_id in (select issued_by from ec_gift_certificates_issued); create table ec_customer_service_issues ( issue_id integer not null primary key, user_identification_id not null references ec_user_identification, -- may be null if this issue isn't associated with an order order_id integer references ec_orders, -- may be null if this issue isn't associated with a gift certificate gift_certificate_id integer references ec_gift_certificates, open_date date not null, close_date date, -- customer service reps who closed the issue closed_by references users, -- we never really delete issues deleted_p char(1) default 'f' check (deleted_p in ('t','f')) ); create index ec_csi_by_user_ident_id on ec_customer_service_issues(user_identification_id); create index ec_csi_by_open_date on ec_customer_service_issues(open_date); -- because an issue can have more than one issue_type create table ec_cs_issue_type_map ( issue_id not null references ec_customer_service_issues, issue_type varchar(40) not null -- e.g. billing, web site ); create index ec_csitm_by_issue_id on ec_cs_issue_type_map(issue_id); create index ec_csitm_by_issue_type on ec_cs_issue_type_map(issue_type); create or replace trigger ec_cs_issue_inserts before insert on ec_customer_service_issues for each row begin IF INSERTING AND :new.open_date is null THEN :new.open_date := sysdate; END IF; end; / show errors create table ec_customer_service_actions ( action_id integer not null primary key, issue_id integer not null references ec_customer_service_issues, interaction_id integer not null references ec_customer_serv_interactions, action_details varchar(4000), follow_up_required varchar(4000) ); create index ec_csa_by_issue on ec_customer_service_actions(issue_id); create table ec_cs_action_info_used_map ( action_id not null references ec_customer_service_actions, info_used varchar(100) not null ); create index ec_csaium_by_action_id on ec_cs_action_info_used_map(action_id); create index ec_csaium_by_info_used on ec_cs_action_info_used_map(info_used); -- this table contains picklist choices for the customer service data -- entry people create sequence ec_picklist_item_id_sequence; create table ec_picklist_items ( picklist_item_id integer not null primary key, -- pretty, human-readable picklist_item varchar(100), -- which picklist this item is in picklist_name varchar(100), sort_key number, last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_picklist_items_audit ( picklist_item_id integer, picklist_item varchar(100), picklist_name varchar(100), sort_key number, last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_picklist_items_audit_tr before update or delete on ec_picklist_items for each row begin insert into ec_picklist_items_audit ( picklist_item_id, picklist_item, picklist_name, sort_key, last_modified, last_modifying_user, modified_ip_address ) values ( :old.picklist_item_id, :old.picklist_item, :old.picklist_name, :old.sort_key, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- Canned responses for customer support create sequence ec_canned_response_id_sequence; create table ec_canned_responses ( response_id integer not null primary key, one_line varchar(100) not null, response_text varchar(4000) not null ); ----------------------------------------------- -- templates 1-6 are pre-defined (see the insert statements below the -- table definitions) -- the wording of each can be changed at /admin/ecommerce/email-templates/ create sequence ec_email_template_id_sequence start with 7; create table ec_email_templates ( email_template_id integer not null primary key, title varchar(100), subject varchar(200), message varchar(4000), -- this lists the variable names that customer service can -- use in this particular email -- for their info only variables varchar(1000), -- for informational purposes only, when the email is -- sent when_sent varchar(1000), -- for customer service issues, this is a tcl list of all -- the issue_types that should be inserted into -- ec_cs_issue_type_map for the issue that will be created -- when the message is sent issue_type_list varchar(100), last_modified date not null, last_modifying_user not null references users, modified_ip_address varchar(20) not null ); create table ec_email_templates_audit ( email_template_id integer, title varchar(100), subject varchar(200), message varchar(4000), variables varchar(1000), when_sent varchar(1000), issue_type_list varchar(100), last_modified date, last_modifying_user integer, modified_ip_address varchar(20), delete_p char(1) default 'f' check (delete_p in ('t','f')) ); create or replace trigger ec_email_templates_audit_tr before update or delete on ec_email_templates for each row begin insert into ec_email_templates_audit ( email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address ) values ( :old.email_template_id, :old.title, :old.subject, :old.message, :old.variables, :old.when_sent, :old.issue_type_list, :old.last_modified, :old.last_modifying_user, :old.modified_ip_address ); end; / show errors -- The following templates are predefined. The templates are -- used in procedures which send out the email, so the template_ids -- shouldn't be changed, although the text can be edited at -- /admin/ecommerce/email-templates/ -- -- email_template_id used for -- ----------------- --------- -- 1 new order -- 2 order shipped -- 3 delayed credit denied -- 4 new gift certificate order -- 5 gift certificate recipient -- 6 gift certificate order failure set scan off insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (1, 'New Order', 'Your Order', 'Thank you for your order. We received your order' || CHR(10) || 'on confirmed_date_here.' || CHR(10) || CHR(10) || 'The following is your order information:' || CHR(10) || CHR(10) || 'item_summary_here' || CHR(10) || CHR(10) || 'Shipping Address:' || CHR(10) || 'address_here' || CHR(10) || CHR(10) || 'price_summary_here' || CHR(10) || CHR(10) || 'Thank you.' || CHR(10) || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'confirmed_date_here, address_here, item_summary_here, price_here, shipping_here, tax_here, total_here, customer_service_signature_here', 'This email will automatically be sent out after an order has been authorized.', '{new order}', sysdate, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (2, 'Order Shipped', 'Your Order Has Shipped', 'We shipped the following items on shipped_date_here:' || CHR(10) || CHR(10) || 'item_summary_here' || CHR(10) || CHR(10) || 'Your items were shipped to:' || CHR(10) || CHR(10) || 'address_here' || CHR(10) || CHR(10) || 'sentence_about_whether_this_completes_the_order_here' || CHR(10) || CHR(10) || 'You can track your package by accessing' || CHR(10) || '"Your Account" at system_url_here' || CHR(10) || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'shipped_date_here, item_summary_here, address_here, sentence_about_whether_this_completes_the_order_here, system_url_here, customer_service_signature_here', 'This email will automatically be sent out after an order or partial order has shipped.', '{order shipped}', sysdate, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (3, 'Delayed Credit Denied', 'Your Order', 'At this time we are not able to receive' || CHR(10) || 'authorization to charge your account. We' || CHR(10) || 'have saved your order so that you can come' || CHR(10) || 'back to system_url_here' || CHR(10) || 'and resubmit it.' || CHR(10) || CHR(10) || 'Please go to your shopping cart and' || CHR(10) || 'click on "Retrieve Saved Cart".' || CHR(10) || CHR(10) || 'Thank you.' || CHR(10) || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'system_url_here, customer_service_signature_here', 'This email will automatically be sent out after a credit card authorization fails if it didn''t fail at the time the user initially submitted their order.', 'billing', sysdate, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (4, 'New Gift Certificate Order', 'Your Order', 'Thank you for your gift certificate order at system_name_here!' || CHR(10) || CHR(10) || 'The gift certificate will be sent to:' || CHR(10) || CHR(10) || 'recipient_email_here' || CHR(10) || CHR(10) || 'Your order details:' || CHR(10) || CHR(10) || 'Gift Certificate certificate_amount_here' || CHR(10) || 'Shipping 0.00' || CHR(10) || 'Tax 0.00' || CHR(10) || '------------ ------------' || CHR(10) || 'TOTAL certificate_amount_here' || CHR(10) || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'system_name_here, recipient_email_here, certificate_amount_here, customer_service_signature_here', 'This email will be sent after a customer orders a gift certificate.', '{gift certificate}', sysdate, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (5, 'Gift Certificate Recipient', 'Gift Certificate', 'It''s our pleasure to inform you that someone' || CHR(10) || 'has purchased a gift certificate for you at' || CHR(10) || 'system_name_here!' || CHR(10) || CHR(10) || 'Use the claim check below to retrieve your gift' || CHR(10) || 'certificate at system_url_here' || CHR(10) || CHR(10) || 'amount_and_message_summary_here' || CHR(10) || CHR(10) || 'Claim Check: claim_check_here' || CHR(10) || CHR(10) || 'To redeem it, just go to' || CHR(10) || 'system_url_here' || CHR(10) || 'choose the items you wish to purchase,' || CHR(10) || 'and proceed to Checkout. You''ll then have' || CHR(10) || 'the opportunity to type in your claim code' || CHR(10) || 'and redeem your certificate! Any remaining' || CHR(10) || 'balance must be paid for by credit card.' || CHR(10) || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'system_name_here, system_url_here, amount_and_message_summary_here, claim_check_here, customer_service_signature_here', 'This is sent to recipients of gift certificates.', '{gift certificate}', sysdate, 1, 'none'); insert into ec_email_templates (email_template_id, title, subject, message, variables, when_sent, issue_type_list, last_modified, last_modifying_user, modified_ip_address) values (6, 'Gift Certificate Order Failure', 'Your Gift Certificate Order', 'We are sorry to report that the authorization' || CHR(10) || 'for the gift certificate order you placed' || CHR(10) || 'at system_name_here could not be made.' || CHR(10) || 'Your order has been canceled. Please' || CHR(10) || 'come back and try your order again at:' || CHR(10) || CHR(10) || 'system_url_here' || CHR(10) || 'For your records, here is the order' || CHR(10) || 'that you attempted to place:' || CHR(10) || CHR(10) || 'Would have been sent to: recipient_email_here' || CHR(10) || 'amount_and_message_summary_here' || CHR(10) || 'We apologize for the inconvenience.' || CHR(10) || 'Sincerely,' || CHR(10) || 'customer_service_signature_here', 'system_name_here, system_url_here, recipient_email_here, amount_and_message_summary_here, customer_service_signature_here', 'This is sent to customers who tried to purchase a gift certificate but got no immediate response from CyberCash and we found out later the auth failed.', '{gift certificate}', sysdate, 1, 'none'); set scan on -- users can sign up for mailing lists based on category, subcategory, -- or subsubcategory (the appropriate level of categorization on which -- to base mailing lists depends on how the site administrator has -- set up their system) -- when the user is signed up for a subsubcategory list, the subcategory_id -- and category_id are also filled in (which makes it easier to refer -- to the mailing list later). -- "cat" stands for "categorization" create table ec_cat_mailing_lists ( user_id not null references users, category_id references ec_categories, subcategory_id references ec_subcategories, subsubcategory_id references ec_subsubcategories ); create index ec_cat_mailing_list_idx on ec_cat_mailing_lists(user_id); create index ec_cat_mailing_list_idx2 on ec_cat_mailing_lists(category_id); create index ec_cat_mailing_list_idx3 on ec_cat_mailing_lists(subcategory_id); create index ec_cat_mailing_list_idx4 on ec_cat_mailing_lists(subsubcategory_id); create sequence ec_spam_id_sequence; create table ec_spam_log ( spam_id integer not null primary key, spam_date date, spam_text varchar(4000), -- the following are all criteria used in choosing the users to be spammed mailing_list_category_id references ec_categories, mailing_list_subcategory_id references ec_subcategories, mailing_list_subsubcategory_id references ec_subsubcategories, user_class_id references ec_user_classes, product_id references ec_products, last_visit_start_date date, last_visit_end_date date ); create index ec_spam_log_by_cat_mail_idx on ec_spam_log (mailing_list_category_id); create index ec_spam_log_by_cat_mail_idx2 on ec_spam_log (mailing_list_subcategory_id); create index ec_spam_log_by_cat_mail_idx3 on ec_spam_log (mailing_list_subsubcategory_id); create index ec_spam_log_by_user_cls_idx on ec_spam_log (user_class_id); create index ec_spam_log_by_product_idx on ec_spam_log (product_id); -- CREDIT CARD STUFF ------------------------ --------------------------------------------- create sequence ec_transaction_id_sequence start with 4000000; create table ec_financial_transactions ( transaction_id integer not null primary key, -- order_id or gift_certificate_id must be filled in order_id references ec_orders, -- The following two rows were added 1999-08-11. They're -- not actually needed by the system right now, but -- they might be useful in the future (I can envision them -- being useful as factory functions are automated). shipment_id references ec_shipments, refund_id references ec_refunds, -- this refers to the purchase of a gift certificate, not the use of one gift_certificate_id references ec_gift_certificates, -- creditcard_id is in here even though order_id has a creditcard_id associated with -- it in case a different credit card is used for a refund or a partial shipment. -- a trigger fills the creditcard_id in if it's not specified creditcard_id not null references ec_creditcards, transaction_amount number not null, -- charge doesn't imply that a charge will actually occur; it's just -- an authorization to charge -- in the case of a refund, there's no such thing as an authorization -- to refund, so the refund really will occur transaction_type varchar(6) not null check (transaction_type in ('charge','refund')), -- it starts out null, becomes 't' when we want to capture it, or becomes -- 'f' it is known that we don't want to capture the transaction (although -- the 'f' is mainly just for reassurance; we only capture ones with 't') -- There's no need to set this for refunds. Refunds are always to be captured. to_be_captured_p char(1) check (to_be_captured_p in (null,'t','f')), inserted_date date not null, authorized_date date, -- set when to_be_captured_p becomes 't'; used in cron jobs to_be_captured_date date, marked_date date, settled_date date, refunded_date date, refund_settled_date date, -- generated by us talking to Cybercash disputed_p char(1) check (disputed_p in ('t','f')), -- date on which we discovered the dispute dispute_discovery_date date, -- if the consumer's bank got his money back from us forcibly charged_back_p char(1) check (charged_back_p in ('t','f')), -- if the transaction failed, this will keep the cron jobs from continuing -- to retry it failed_p char(1) default 'f' check (failed_p in ('t','f')), check (order_id is not null or gift_certificate_id is not null) ); create index ec_finan_trans_by_order_idx on ec_financial_transactions (order_id); create index ec_finan_trans_by_cc_idx on ec_financial_transactions (creditcard_id); create index ec_finan_trans_by_gc_idx on ec_financial_transactions (gift_certificate_id); -- reportable transactions: those which have not failed which are to -- be captured (note: refunds are always to be captured) create or replace view ec_fin_transactions_reportable as select * from ec_financial_transactions where (transaction_type='charge' and to_be_captured_p='t' and failed_p='f') or (transaction_type='refund' and failed_p='f'); -- fills creditcard_id into ec_financial_transactions if it's missing -- (using the credit card associated with the order) create or replace trigger fin_trans_ccard_update_tr before insert on ec_financial_transactions for each row declare v_creditcard_id ec_creditcards.creditcard_id%TYPE; begin IF :new.order_id is not null THEN select creditcard_id into v_creditcard_id from ec_orders where order_id=:new.order_id; IF :new.creditcard_id is null THEN :new.creditcard_id := v_creditcard_id; END IF; END IF; end; / show errors create table ec_cybercash_log ( transaction_id not null references ec_financial_transactions, -- The types of transactions that will be logged here are -- mauthonly, postauth, return, void, retry, query txn_attempted_type varchar(25), txn_attempted_time date, -- Everything below this line is returned by CyberCash. Note -- that not all columns will have values (for instance, cc_time -- is only returned when doing a query, aux_msg is not returned -- when doing a query, ref-code never seems to be returned, -- batch-id is only returned when querying for settled or setlret). -- Note: when doing a non-query, there is no txn_type returned -- by CyberCash, so this value will be inserted by talk_to_cybercash -- procedure. The reason for doing this is consistency; for -- example the attempted transaction type may be mauthonly, but later -- when querying for this authorization, the txn_type is auth. So, -- auth will be inserted into txn_type when doing an mauthonly. txn_type varchar(25), -- we take the time returned by CyberCash and chop off the -- sub-second precision cc_time date, merch_txn varchar(25), cust_txn varchar(25), origin char(1), txn_status varchar(25), errloc varchar(25), errmsg varchar(200), aux_msg varchar(200), auth_code varchar(25), action_code varchar(25), avs_code varchar(3), ref_code varchar(25), batch_id varchar(25), amount number ); -- END CREDIT CARD STUFF ---------------------------- ----------------------------------------------------- -- this is to record any problems that may have occurred so that the site administrator -- can be alerted on the admin pages create sequence ec_problem_id_sequence; create table ec_problems_log ( problem_id integer not null primary key, problem_date date, problem_details varchar(4000), -- if it's related to an order order_id references ec_orders, -- if it's related to a gift certificate gift_certificate_id references ec_gift_certificates, resolved_date date, resolved_by references users ); -- keeps track of automatic emails (based on templates) that are sent out create table ec_automatic_email_log ( user_identification_id not null references ec_user_identification, email_template_id not null references ec_email_templates, order_id references ec_orders, shipment_id references ec_shipments, gift_certificate_id references ec_gift_certificates, date_sent date ); create index ec_auto_email_by_usr_id_idx on ec_automatic_email_log (user_identification_id); create index ec_auto_email_by_temp_idx on ec_automatic_email_log (email_template_id); create index ec_auto_email_by_order_idx on ec_automatic_email_log (order_id); create index ec_auto_email_by_shipment_idx on ec_automatic_email_log (shipment_id); create index ec_auto_email_by_gc_idx on ec_automatic_email_log (gift_certificate_id);