Index: openacs.org-dev/packages/ecommerce/tcl/ecommerce-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs.org-dev/packages/ecommerce/tcl/ecommerce-procs-oracle.xql,v
diff -u -r1.1.1.1 -r1.1.1.2
--- openacs.org-dev/packages/ecommerce/tcl/ecommerce-procs-oracle.xql	9 Jul 2002 17:35:05 -0000	1.1.1.1
+++ openacs.org-dev/packages/ecommerce/tcl/ecommerce-procs-oracle.xql	8 Oct 2002 15:47:05 -0000	1.1.1.2
@@ -60,15 +60,14 @@
   <fullquery name="ec_order_summary_for_customer.order_info_select">      
     <querytext>
       select eco.confirmed_date, eco.creditcard_id, eco.shipping_method,
-      u.email,
-      eca.line1, eca.line2, eca.city, eca.usps_abbrev, eca.zip_code, eca.country_code,
-      eca.full_state_name, eca.attn, eca.phone, eca.phone_time
+      u.email, eco.shipping_address as shipping_address_id, 
+      ecc.billing_address as billing_address_id
       from ec_orders eco,
       cc_users u,
-      ec_addresses eca
+      ec_creditcards ecc
       where eco.order_id = :order_id
       and eco.user_id = u.user_id(+)
-      and eco.shipping_address = eca.address_id(+)
+      and eco.creditcard_id = ecc.creditcard_id(+)
     </querytext>
   </fullquery>
 
@@ -77,8 +76,46 @@
       insert into ec_user_sessions
       (user_session_id, ip_address, start_time, http_user_agent)
       values
-      (:user_session_id, :ip_address , sysdate, :http_user_agent)
+      (:user_session_id, :ip_address, sysdate, :http_user_agent)
     </querytext>
   </fullquery>
 
+<fullquery name="ec_price_line.get_regular_approvalrequired_price">      
+    <querytext>
+      select min(decode(ucp.price,null,p.price,
+      decode(sign(ucp.price-p.price),1,p.price,ucp.price))) as regular_price,
+      ucp.user_class_name
+      from ec_products p, (select * from (select uc.product_id, uc.price, c.user_class_name 
+      from ec_product_user_class_prices uc, ec_user_classes c, 
+      ec_user_class_user_map m 
+      where uc.user_class_id = c.user_class_id 
+      and uc.product_id = :product_id
+      and uc.user_class_id = m.user_class_id
+      and m.user_id = :user_id 
+      and m.user_class_approved_p = 't' order by uc.price)
+      where rownum=1) ucp
+      where p.product_id = :product_id and ucp.product_id(+)=p.product_id
+      group by p.product_id, p.price, ucp.user_class_name, ucp.price
+    </querytext>
+  </fullquery>
+
+  <fullquery name="ec_price_line.get_regular_no_approval_required_price">      
+    <querytext>
+      select min(decode(ucp.price,null,p.price,
+      decode(sign(ucp.price-p.price),1,p.price,ucp.price))) as regular_price,
+      ucp.user_class_name
+      from ec_products p, (select * from (select uc.product_id, uc.price, c.user_class_name 
+      from ec_product_user_class_prices uc, ec_user_classes c, 
+      ec_user_class_user_map m 
+      where uc.user_class_id = c.user_class_id 
+      and uc.product_id = :product_id
+      and uc.user_class_id = m.user_class_id
+      and m.user_id = :user_id 
+      and (m.user_class_approved_p is null or m.user_class_approved_p = 't') order by uc.price)
+      where rownum=1) ucp
+      where p.product_id = :product_id and ucp.product_id(+)=p.product_id
+      group by p.product_id, p.price, ucp.user_class_name, ucp.price
+    </querytext>
+  </fullquery>
+
 </queryset>