Forum OpenACS Q&A: Looking for someone familiar with ecommerce module

On www/admin/ecommerce/orders/revenue.tcl there's a query that calls
ec_items_money_view. I fixed the nvl's and decode's in that page, but
I have no idea what this view is supposed to do and don't have time to
dig it up.

Someone familiar with ecommerce could dig into that query, guess what
it's supposed to do (that file has some good comments) or look at
ecommerce aD incarnations post-3.2 to see if they added the view to
ecommerce.sql.

Any takers?

I guess I could have made it clear that the view definition is missing from the ecommerce.sql file. It's simply not there. We need someone familiar with ecommerce to find what it's supposed to do.

BTW, there are only 2 bugs left on the ecommerce module on SDM now (and I am working through them).

Here's the view, including some comments about what it does:

-- This view displays:
-- order_id
-- shipment_date
-- bal_price_charged sum(price_charged - price_refunded) for all items in the shipment
-- bal_shipping_charged
-- bal_tax_charged
-- The purpose: payment is recognized when an item ships so this sums the various
-- parts of payment (price, shipping, tax) for all the items in each shipment

create or replace view ec_items_money_view
as
select i.shipment_id, i.order_id, s.shipment_date, nvl(sum(i.price_charged),0) - nvl(sum(i.price_refunded),0) as bal_price_charg
ed,
nvl(sum(i.shipping_charged),0) - nvl(sum(i.shipping_refunded),0) as bal_shipping_charged,
nvl(sum(i.price_tax_charged),0) - nvl(sum(i.price_tax_refunded),0) + nvl(sum(i.shipping_tax_charged),0)
  - nvl(sum(i.shipping_tax_refunded),0) as bal_tax_charged
from ec_items i, ec_shipments s
where i.shipment_id=s.shipment_id
and i.item_state <> 'void'
group by i.order_id, i.shipment_id, s.shipment_date;

Do you need more info about it?

That's great Eve. Thanks a lot! I'll go port and commit that.