Index: openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 22 Oct 2001 06:48:49 -0000 1.5 +++ openacs-4/packages/ecommerce/sql/oracle/ecommerce-create.sql 26 May 2002 04:36:49 -0000 1.6 @@ -24,7 +24,6 @@ -- gilbertw: acs-references is not complete yet. use old acs-geo-tables until -- acs-references is done -@@ acs-geo-tables-create.sql -- product display templates create sequence ec_template_id_sequence start with 2; @@ -546,18 +545,17 @@ 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 + -- Some credit card gateways do 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, + -- depending on admin settings, after we get success from the credit card gateway, -- 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')) @@ -1265,7 +1263,7 @@ as select * from ec_orders -where order_state in ('authorized_plus_avs','authorized_minus_avs','partially_fulfilled'); +where order_state in ('authorized','partially_fulfilled'); -- this is needed because orders might be only partially shipped @@ -1278,7 +1276,7 @@ -- 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, + address_id references ec_addresses, shipment_date date not null, expected_arrival_date date, carrier varchar(50), -- e.g., 'fedex' @@ -1995,13 +1993,13 @@ as select * from ec_gift_certificates -where gift_certificate_state in ('authorized','authorized_plus_avs','authorized_minus_avs'); +where gift_certificate_state in ('authorized'); 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'); +where gift_certificate_state in ('authorized'); create or replace view ec_gift_certificates_issued as @@ -2393,7 +2391,9 @@ create sequence ec_transaction_id_sequence start with 4000000; create table ec_financial_transactions ( - transaction_id integer not null primary key, + transaction_id varchar(20) not null primary key, + -- The charge transaction that a refund transaction refunded. + refunded_transaction_id varchar(20) references ec_financial_transactions, -- 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 @@ -2409,6 +2409,7 @@ -- a trigger fills the creditcard_id in if it's not specified creditcard_id not null references ec_creditcards, transaction_amount number not null, + refunded_amount number, -- 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 @@ -2424,15 +2425,7 @@ -- 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')), @@ -2470,42 +2463,6 @@ / 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 ---------------------------- ----------------------------------------------------- @@ -2678,7 +2635,12 @@ min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN SELECT order_id into v_order_id FROM ec_shipments where shipment_id=v_shipment_id; - SELECT min(shipment_id) into min_shipment_id FROM ec_shipments where order_id=v_order_id; + SELECT min(s.shipment_id) into min_shipment_id + FROM ec_shipments s, ec_items i, ec_products p + WHERE s.order_id=v_order_id + AND s.shipment_id = i.shipment_id + AND i.product_id = p.product_id + AND p.no_shipping_avail_p = 'f'; IF v_shipment_id=min_shipment_id THEN SELECT nvl(shipping_charged,0) - nvl(shipping_refunded,0) into base_shipping FROM ec_orders where order_id=v_order_id; ELSE @@ -2703,7 +2665,12 @@ min_shipment_id ec_shipments.shipment_id%TYPE; BEGIN SELECT order_id into v_order_id FROM ec_shipments where shipment_id=v_shipment_id; - SELECT min(shipment_id) into min_shipment_id FROM ec_shipments where order_id=v_order_id; + SELECT min(s.shipment_id) into min_shipment_id + FROM ec_shipments s, ec_items i, ec_products p + WHERE s.order_id=v_order_id + AND s.shipment_id = i.shipment_id + AND i.product_id = p.product_id + AND p.no_shipping_avail_p = 'f'; IF v_shipment_id=min_shipment_id THEN SELECT nvl(shipping_tax_charged,0) - nvl(shipping_tax_refunded,0) into base_shipping_tax FROM ec_orders where order_id=v_order_id; ELSE