postgresql7.1
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, current_timestamp, :customer_service_rep,:reason_for_return)
select coalesce(price_tax_charged,0) as price_tax_charged,
coalesce(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)
select coalesce(shipping_tax_charged,0)
from ec_orders
where order_id=:order_id
select ec_tax(0,:shipping_bind_variable,:order_id)
select ec_tax(0,:base_shipping_to_refund,:order_id)
select transaction_id as charged_transaction_id, marked_date
from ec_financial_transactions
where order_id = :order_id
and transaction_type = 'charge'
and (transaction_amount - :refund_amount) < 0.01::numeric
and (transaction_amount - :refund_amount) > 0::numeric
and refunded_amount is null
and marked_date is not null
and failed_p = 'f'
order by transaction_id
limit 1
insert into ec_financial_transactions
(transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
values
(:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', current_timestamp, :scheduled_hour)
update ec_financial_transactions
set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
where transaction_id = :charged_transaction_id
select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date
from ec_financial_transactions
where order_id = :order_id
and transaction_type = 'charge'
and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric
and marked_date is not null
and failed_p = 'f'
order by (transaction_amount - coalesce(refunded_amount, 0)) desc
limit 1
insert into ec_financial_transactions
(transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
values
(:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', current_timestamp, :scheduled_hour)
update ec_financial_transactions
set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
where transaction_id = :charged_transaction_id
select ec_order_gift_cert_amount(:order_id)
insert into ec_problems_log
(problem_id, problem_date, problem_details, order_id)
values
(ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id)
select u.gift_certificate_id, coalesce(sum(u.amount_used),0) - coalesce(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
(:gift_certificate_id, :order_id, least(:certificate_amount_to_reinstate, :reinstateable_amount), current_timestamp)
insert into ec_problems_log
(problem_id, problem_date, problem_details, order_id)
values
(ec_problem_id_sequence.nextval, current_timestamp, :errorstring, :order_id)
update ec_financial_transactions
set refunded_date = current_timestamp
where transaction_id=:pgw_transaction_id
update ec_financial_transactions
set to_be_captured_date = current_timestamp
where transaction_id = :transaction_id
select transaction_id as charged_transaction_id, marked_date
from ec_financial_transactions
where order_id = :order_id
and transaction_type = 'charge'
and (transaction_amount - :refund_amount) < 0.01::numeric
and (transaction_amount - :refund_amount) > 0::numeric
and refunded_amount is null
and marked_date is not null
and failed_p = 'f'
order by transaction_id
limit 1
select transaction_id as charged_transaction_id, (transaction_amount - coalesce(refunded_amount, 0)) as unrefunded_amount, marked_date
from ec_financial_transactions
where order_id = :order_id
and transaction_type = 'charge'
and (transaction_amount - coalesce(refunded_amount, 0)) > 0.01::numeric
and marked_date is not null
and failed_p = 'f'
order by (transaction_amount - coalesce(refunded_amount, 0)) desc
limit 1
update ec_financial_transactions
set refunded_amount = coalesce(refunded_amount, 0) + :refund_amount
where transaction_id = :charged_transaction_id
update ec_financial_transactions
set refunded_amount = coalesce(refunded_amount, 0) + :unrefunded_amount
where transaction_id = :charged_transaction_id
insert into ec_financial_transactions
(transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
values
(:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :refund_amount, 'refund', sysdate, :scheduled_hour)
insert into ec_financial_transactions
(transaction_id, refunded_transaction_id, order_id, refund_id, creditcard_id, transaction_amount, transaction_type, inserted_date, to_be_captured_date)
values
(:refund_transaction_id, :charged_transaction_id, :order_id, :refund_id, :creditcard_id, :unrefunded_amount, 'refund', sysdate, :scheduled_hour)