Index: openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql 29 Jan 2002 00:11:46 -0000 1.4 +++ openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql 26 May 2002 04:36:50 -0000 1.5 @@ -1,118 +1,93 @@ - oracle8.1.6 + + oracle + 8.1.6 + - - - declare begin ec_reinst_gift_cert_on_order (:order_id); end; - - + + + declare + begin + ec_reinst_gift_cert_on_order (:order_id); + end; + + + + + + select ec_order_cost(:order_id) + from dual + + - - - - select ec_order_cost(:order_id) from dual - - + + + update ec_orders + set order_state='authorized', authorized_date=sysdate + where order_id=:order_id + + + + + + update ec_financial_transactions + set authorized_date=sysdate + where transaction_id=:transaction_id + + - - - - update ec_orders set order_state=:new_state, authorized_date=sysdate where order_id=:order_id - - - - - - update ec_financial_transactions set authorized_date=sysdate where transaction_id=:transaction_id - - - - - - - update ec_orders set order_state='confirmed', confirmed_date=sysdate where order_id=:order_id - - - - - - - select ec_order_cost(:order_id) from dual - - - - - - + + + update ec_orders + set order_state='confirmed', confirmed_date=sysdate + where order_id=:order_id + + + + + + select ec_order_cost(:order_id) + from dual + + + + + insert into ec_financial_transactions - (transaction_id, order_id, transaction_amount, transaction_type, inserted_date) - values - (:transaction_id, :order_id, :total_amount, 'charge', sysdate) - - - - - - - - select gift_certificate_id - from ec_gift_certificates_approved - where user_id=:user_id - and sysdate - expires < 0 - and amount_remaining_p = 't' - order by expires + (transaction_id, order_id, transaction_amount, transaction_type, inserted_date) + values + (:transaction_id, :order_id, :total_amount, 'charge', sysdate) + + - - - - - - - select ec_gift_certificate_balance(:user_id) from dual - - - - - - - select ec_order_amount_owed(:order_id) from dual - - - - - - - select gift_certificate_amount_left(:gift_certificate_id) from dual - - - - - - + + + select ec_order_amount_owed(:order_id) + from dual + + + + + + select gift_certificate_id, gift_certificate_amount_left(gift_certificate_id) as amount_available + from ec_gift_certificates_approved + where user_id = :user_id + and sysdate - expires < 0 + and amount_remaining_p = 't' + and gift_certificate_amount_left(gift_certificate_id) > 0 + order by expires + + + + + insert into ec_gift_certificate_usage (gift_certificate_id, order_id, amount_used, used_date) - VALUES - (:gift_certificate_id, :order_id, least(to_number(:gift_certificate_amount_left), - to_number(:amount_owed)), sysdate) - - - - - - - - select ec_gift_certificate_balance(:user_id) from dual - - - - - - - select ec_order_amount_owed(:order_id) from dual - - - - + values + (:gift_certificate_id, :order_id, least(to_number(:amount_available), to_number(:amount_owed)), sysdate) + + +