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
