Index: openacs-4/packages/invoices/tcl/invoice-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/invoices/tcl/invoice-procs.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/invoices/tcl/invoice-procs.xql	16 Aug 2005 20:04:32 -0000	1.1
+++ openacs-4/packages/invoices/tcl/invoice-procs.xql	11 Oct 2005 15:23:28 -0000	1.2
@@ -1,6 +1,18 @@
 <?xml version="1.0"?>
 <queryset>
 
+<fullquery name="iv::invoice::set_status.set_status">
+      <querytext>
+
+	update iv_invoices
+	set status = :status
+	where offer_id = (select latest_revision
+                          from cr_items
+                          where item_id = :invoice_id)
+
+      </querytext>
+</fullquery>
+
 <fullquery name="iv::invoice::data.get_data">
       <querytext>
 
@@ -24,16 +36,25 @@
 <fullquery name="iv::invoice::data.invoice_items">
       <querytext>
 
-    select r.title, r.description, r.item_id, ii.offer_item_id,
+    select cr.title, cr.description, cr.item_id, ii.offer_item_id,
            ii.item_units, ii.price_per_unit, ii.item_nr,
-           ii.rebate, ii.vat, m.category_id, ofi.file_count, ofi.page_count
-    from iv_offer_items ofi, cr_items oi, cr_revisions r,
-         category_object_map m, iv_invoice_items ii
-    where oi.latest_revision = ii.invoice_id
-    and r.revision_id = ii.iv_item_id
-    and oi.item_id = :invoice_id
+           ii.rebate, ii.vat, m.category_id, ofi.file_count,
+           ofi.page_count, pr.title as project_title, p.project_code,
+           pi.item_id as project_id
+    from iv_offer_items ofi, cr_items ci, cr_revisions cr,
+         category_object_map m, iv_invoice_items ii, cr_revisions oor,
+         acs_rels r, cr_items pi, cr_revisions pr, pm_projects p
+    where ci.latest_revision = ii.invoice_id
+    and cr.revision_id = ii.iv_item_id
+    and ci.item_id = :invoice_id
     and m.object_id = ofi.offer_item_id
     and ofi.offer_item_id = ii.offer_item_id
+    and oor.revision_id = ofi.offer_id
+    and r.object_id_two = oor.item_id
+    and r.object_id_one = pi.item_id
+    and r.rel_type = 'application_data_link'
+    and pi.latest_revision = pr.revision_id
+    and pr.revision_id = p.project_id
     order by ii.sort_order
 
       </querytext>
@@ -45,8 +66,8 @@
 	select t.invoice_id as invoice_rev_id, r.title, r.description,
 	       t.invoice_nr, t.total_amount, t.vat, t.vat_percent,
 	       o.creation_user, p.first_names, p.last_name, t.amount_sum,
-	       to_char(o.creation_date, :timestamp_format) as creation_date,
-	       to_char(t.due_date, :timestamp_format) as due_date,
+	       to_char(o.creation_date, 'YYYY-MM-DD HH24:MI:SS') as creation_date,
+	       to_char(t.due_date, 'YYYY-MM-DD HH24:MI:SS') as due_date,
 	       t.payment_days, t.currency, t.organization_id, t.recipient_id
 	from iv_invoices t, cr_revisions r, cr_items i, acs_objects o,
 	     persons p
@@ -62,16 +83,25 @@
 <fullquery name="iv::invoice::parse_data.invoice_items">
       <querytext>
 
-    select r.title, r.description, r.item_id, ii.offer_item_id,
+    select cr.title, cr.description, cr.item_id, ii.offer_item_id,
            ii.item_units, ii.price_per_unit, ii.item_nr,
-           ii.rebate, ii.vat, m.category_id, ofi.file_count, ofi.page_count
-    from iv_offer_items ofi, cr_items oi, cr_revisions r,
-         category_object_map m, iv_invoice_items ii
-    where oi.latest_revision = ii.invoice_id
-    and r.revision_id = ii.iv_item_id
-    and oi.item_id = :invoice_id
+           ii.rebate, ii.vat, m.category_id, ofi.file_count,
+           ofi.page_count, pr.title as project_title, p.project_code,
+           pi.item_id as project_id
+    from iv_offer_items ofi, cr_items ci, cr_revisions cr,
+         category_object_map m, iv_invoice_items ii, cr_revisions oor,
+         acs_rels r, cr_items pi, cr_revisions pr, pm_projects p
+    where ci.latest_revision = ii.invoice_id
+    and cr.revision_id = ii.iv_item_id
+    and ci.item_id = :invoice_id
     and m.object_id = ofi.offer_item_id
     and ofi.offer_item_id = ii.offer_item_id
+    and oor.revision_id = ofi.offer_id
+    and r.object_id_two = oor.item_id
+    and r.object_id_one = pi.item_id
+    and r.rel_type = 'application_data_link'
+    and pi.latest_revision = pr.revision_id
+    and pr.revision_id = p.project_id
     order by ii.sort_order
 
       </querytext>