select ii.iv_item_id, ii.title as item_title, iv.title as invoice_title, ii.rebate, ii.amount_total as final_amount, com.category_id as cat_id, iv.organization_id, to_char(ii.creation_date,'yy-mm-dd') as creation_date, to_char(ii.creation_date,'mm') as month, ii.offer_item_id, iv.organization_id, org.name as org_name, ob.title as cat_name, i.item_id from iv_invoice_itemsx ii, iv_invoicesx iv, category_object_map com, cr_items i, organizations org, acs_objects ob where iv.item_id = i.item_id and ii.invoice_id = i.latest_revision and iv.invoice_id = ii.invoice_id and com.object_id = ii.offer_item_id and org.organization_id = iv.organization_id and ob.object_id = com.category_id $category_filter_clause and [template::list::page_where_clause -name "iv_items"] [template::list::filter_where_clauses -and -name "iv_items"] [template::list::orderby_clause -orderby -name "iv_items"] select ii.iv_item_id from iv_invoice_itemsx ii, iv_invoicesx iv, category_object_map com, cr_items i, organizations org, acs_objects ob where iv.item_id = i.item_id and ii.invoice_id = i.latest_revision and iv.invoice_id = ii.invoice_id and com.object_id = ii.offer_item_id and org.organization_id = iv.organization_id and ob.object_id = com.category_id $category_filter_clause [template::list::filter_where_clauses -and -name "iv_items"] [template::list::orderby_clause -orderby -name "iv_items"] select distinct o.title, c.tree_id from category_object_map com, iv_offer_items io, acs_objects o, categories c where com.object_id = io.offer_item_id and com.category_id = c.category_id and c.tree_id = o.object_id order by o.title asc select o.title, c.category_id from categories c, acs_objects o where o.object_id = c.category_id and c.tree_id in ([template::util::tcl_to_sql_list $tree_ids]) select o.item_id from iv_offersx o, iv_offer_items oi where oi.offer_item_id = :offer_item_id and oi.offer_id = o.offer_id select sum(ii.amount_total) from iv_invoicesx iv, iv_invoice_itemsx ii, cr_items i, category_object_map com where iv.item_id = i.item_id and ii.invoice_id = i.latest_revision and iv.invoice_id = ii.invoice_id and ii.offer_item_id = com.object_id and com.category_id = :c_id [template::list::filter_where_clauses -and -name "iv_items"]