Index: openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql 29 Jan 2002 00:11:46 -0000 1.2 +++ openacs-4/packages/ecommerce/www/admin/orders/items-return-4-oracle.xql 26 May 2002 04:36:50 -0000 1.3 @@ -1,117 +1,126 @@ - oracle8.1.6 + + oracle + 8.1.6 + - - + + insert into ec_refunds - (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons) - values - (:refund_id, :order_id, :cash_amount_to_refund, sysdate, :customer_service_rep,:reason_for_return) - - - + (refund_id, order_id, refund_amount, refund_date, refunded_by, refund_reasons) + values + (:refund_id, :order_id, :cash_amount_to_refund, sysdate, :customer_service_rep,:reason_for_return) + + - - - - select nvl(price_tax_charged,0) as price_tax_charged, nvl(shipping_tax_charged,0) as shipping_tax_charged from ec_items where item_id=:item_id - - + + + select nvl(price_tax_charged,0) as price_tax_charged, + nvl(shipping_tax_charged,0) as shipping_tax_charged + from ec_items + where item_id=:item_id + + - - - - select ec_tax(:price_bind_variable,0,:order_id) from dual - - + + + select ec_tax(:price_bind_variable,0,:order_id) + from dual + + - - - - select ec_tax(0,:shipping_bind_variable,:order_id) from dual - - + + + select ec_tax(0,:shipping_bind_variable,:order_id) + from dual + + - - - - select nvl(shipping_tax_charged,0) from ec_orders where order_id=:order_id - - + + + select nvl(shipping_tax_charged,0) + from ec_orders + where order_id=:order_id + + - - - - select ec_tax(0,:base_shipping_to_refund,:order_id) from dual - - + + + select ec_tax(0,:base_shipping_to_refund,:order_id) + from dual + + - - - + + insert into ec_financial_transactions - (transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date) - values - (:transaction_id, :order_id, :refund_id, :creditcard_id, :cash_amount_to_refund, 'refund', sysdate) - - - + (transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date) + values + (:transaction_id, :order_id, :refund_id, :creditcard_id, :cash_amount_to_refund, 'refund', sysdate) + + - - - - select ec_order_gift_cert_amount(:order_id) from dual - - + + + select ec_order_gift_cert_amount(:order_id) + from dual + + - - - + + insert into ec_problems_log - (problem_id, problem_date, problem_details, order_id) - values - (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) - - - + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) + + - - - - select gift_certificate_amount_left(:cert) from dual - - + + + select u.gift_certificate_id, nvl(sum(u.amount_used),0) - nvl(sum(u.amount_reinstated),0) as reinstateable_amount + from ec_gift_certificate_usage u, ec_gift_certificates c + where u.gift_certificate_id = c.gift_certificate_id + and u.order_id = :order_id + group by u.gift_certificate_id, c.expires + order by expires desc, gift_certificate_id desc + + - - - + + insert into ec_gift_certificate_usage - (gift_certificate_id, order_id, amount_reinstated, reinstated_date) - values - (:cert, :order_id, :iteration_reinstate_amount, sysdate) - - - + (gift_certificate_id, order_id, amount_reinstated, reinstated_date) + values + (:gift_certificate_id, :order_id, least(to_number(:certificate_amount_to_reinstate), to_number(:reinstateable_amount)), sysdate) + + - - - + + insert into ec_problems_log - (problem_id, problem_date, problem_details, order_id) - values - (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) - - - + (problem_id, problem_date, problem_details, order_id) + values + (ec_problem_id_sequence.nextval, sysdate, :errorstring, :order_id) + + - - - - update ec_financial_transactions set refunded_date=sysdate where transaction_id=:transaction_id - - + + + update ec_financial_transactions + set refunded_date=sysdate + where transaction_id=:pgw_transaction_id + + - + + + update ec_financial_transactions + set to_be_captured_date = sysdate + where transaction_id = :transaction_id + + +