select t.organization_id, t.currency, t.paid_currency, t.vat_percent as cur_vat_percent, t.cancelled_p, t.invoice_id as invoice_rev_id, t.parent_invoice_id, t.total_amount as cur_total_amount, t.status, t.amount_sum as cur_amount_sum from iv_invoices t, cr_items i where i.latest_revision = t.invoice_id and i.item_id = :invoice_id select cp.item_id from cr_items ci, cr_items co, cr_items cp, iv_invoice_items ii, iv_offer_items oi, pm_projects p, acs_data_links r where ci.item_id = :invoice_id and ii.invoice_id = ci.latest_revision and oi.offer_item_id = ii.offer_item_id and oi.offer_id = co.latest_revision and r.object_id_one = co.item_id and r.object_id_two = cp.item_id and p.project_id = cp.latest_revision select p.first_names || ' ' || p.last_name, p.person_id from persons p, iv_invoices i where i.invoice_id = :parent_invoice_id and p.person_id = i.contact_id order by lower(p.last_name), lower(p.first_names) select i.recipient_id as rec_id from iv_invoices i where i.invoice_id = :parent_invoice_id select p.first_names || ' ' || p.last_name, p.person_id from persons p, acs_rels r where r.object_id_one = p.person_id and r.object_id_two = :organization_id and r.rel_type = 'contact_rels_ir' order by lower(p.last_name), lower(p.first_names) select p.person_id from persons p, pm_projects pj, cr_items i where i.item_id in ([join $project_id ,]) and i.latest_revision = pj.project_id and pj.contact_id = p.person_id order by lower(p.last_name), lower(p.first_names) select pj.recipient_id as rec_id from pm_projects pj, cr_items i where i.item_id in ([join $project_id ,]) and i.latest_revision = pj.project_id select r.object_id_one from acs_rels r where r.object_id_two = :organization_id and r.rel_type = 'contact_rels_ir' select cri.item_id as cancel_id, r.title as cancel_title from iv_invoices ci, cr_items cri, cr_revisions r where cri.latest_revision = ci.invoice_id and ci.invoice_id = :parent_invoice_id and r.revision_id = ci.invoice_id select ii.item_id as cancel_id, r.title as cancel_title from iv_invoices ci, cr_items cri, iv_invoices i, cr_items ii, cr_revisions r where cri.latest_revision = ci.invoice_id and ci.invoice_id = i.parent_invoice_id and i.invoice_id = ii.latest_revision and cri.item_id = :invoice_id and r.revision_id = i.invoice_id select io.item_id as offer_id, ro.title as offer_title from iv_offers o, cr_items io, cr_revisions ro, acs_data_links r, cr_items ip, pm_projects p where o.offer_id = ro.revision_id and ro.revision_id = io.latest_revision and r.object_id_one = ip.item_id and r.object_id_two = io.item_id and ip.item_id in ([join $subprojects ,]) and p.project_id = ip.latest_revision and p.invoice_p = false select cr.title, cr.description, ofi.offer_item_id, ofi.item_units, ofi.offer_id, ofi.price_per_unit, ofi.item_nr, pi.item_id as project_id, of.credit_percent, pr.title as project_title, ofi.vat, ofi.rebate, m.category_id, oi.item_id as offer_cr_item_id from cr_items oi, cr_revisions cr, cr_items pi, cr_revisions pr, acs_objects o, acs_data_links r, iv_offers of, iv_offer_items ofi left outer join category_object_map m on (m.object_id = ofi.offer_item_id) where o.object_id = ofi.offer_id and o.package_id = :package_id and oi.latest_revision = ofi.offer_id and r.object_id_one = pi.item_id and r.object_id_two = oi.item_id and pr.revision_id = pi.latest_revision and pi.item_id in ([join $project_id ,]) and cr.revision_id = ofi.offer_item_id and of.offer_id = ofi.offer_id and not exists (select 1 from iv_invoice_items ii, iv_invoices i, cr_items ci where ii.offer_item_id = ofi.offer_item_id and i.invoice_id = ii.invoice_id and ci.latest_revision = i.invoice_id and i.cancelled_p = 'f') order by pi.item_id, ofi.item_nr select ir.title, ir.description, ir.item_id as iv_item_id, i.item_units, i.price_per_unit, i.item_nr, ofi.offer_id, pi.item_id as project_id, pr.title as project_title, i.vat as old_vat, i.rebate, m.category_id, i.offer_item_id, of.credit_percent, oi.item_id as offer_cr_item_id from cr_items oi, iv_invoice_items i, cr_revisions ir, cr_items pi, cr_revisions pr, iv_offers of, cr_items vi, cr_items ii, acs_data_links r, pm_projects p, iv_offer_items ofi left outer join category_object_map m on (m.object_id = ofi.offer_item_id) where oi.latest_revision = ofi.offer_id and i.offer_item_id = ofi.offer_item_id and i.iv_item_id = ir.revision_id and ir.revision_id = ii.latest_revision and i.invoice_id = vi.latest_revision and vi.item_id = :invoice_id and r.object_id_one = pi.item_id and r.object_id_two = oi.item_id and pr.revision_id = pi.latest_revision and p.project_id = pr.revision_id and of.offer_id = ofi.offer_id order by pi.item_id, i.item_nr select r.title from cr_revisions r, cr_items i where r.revision_id = i.latest_revision and i.item_id in ([join $project_id ,]) select min(o.payment_days) as payment_days, max(o.vat_percent) as vat_percent, sum(o.vat) as vat, sum(o.amount_total) as amount_total, sum(o.amount_sum) as amount_sum from iv_offers o, cr_items i, acs_data_links r where o.offer_id = i.latest_revision and r.object_id_one in ([join $project_id ,]) and r.object_id_two = i.item_id select t.invoice_id as invoice_rev_id, r.title, r.description, t.invoice_nr, t.parent_invoice_id, t.total_amount, t.paid_amount, t.payment_days, t.vat, t.vat_percent, to_char(t.due_date, :date_format) as due_date, t.amount_sum, o.creation_user, p.first_names, p.last_name, t.recipient_id, to_char(o.creation_date, :timestamp_format) as creation_date, t.contact_id from iv_invoices t, cr_revisions r, cr_items i, acs_objects o, persons p where r.revision_id = t.invoice_id and i.latest_revision = r.revision_id and i.item_id = :invoice_id and o.object_id = t.invoice_id and p.person_id = o.creation_user select count(*) as unbilled_items from iv_offers o, iv_offer_items ofi where o.offer_id = :offer_id and ofi.offer_id = o.offer_id and not exists (select 1 from iv_invoice_items ii where ii.offer_item_id = ofi.offer_item_id) group by o.offer_id select of.offer_id as credit_offer_rev_id, of.amount_total from iv_offers of, cr_items oi, acs_data_links r, acs_objects o, pm_projects p, cr_items pi where r.object_id_one = pi.item_id and r.object_id_two = oi.item_id and oi.latest_revision = of.offer_id and of.status = 'credit' and o.object_id = of.offer_id and o.package_id = :package_id and pi.latest_revision = p.project_id and p.status_id = 2 and p.customer_id = :organization_id select ofi.offer_id as credit_offer_rev_id, oi.item_id as credit_offer_item_id from iv_offers of, cr_items i, acs_objects o, acs_data_links r, iv_offer_items ofi, cr_items oi, pm_projects p, cr_items pi where r.object_id_one = pi.item_id and r.object_id_two = i.item_id and o.object_id = of.offer_id and o.package_id = :package_id and of.offer_id = ofi.offer_id and of.status = 'credit' and i.latest_revision = of.offer_id and oi.latest_revision = ofi.offer_item_id and ofi.item_nr = :invoice_id and pi.latest_revision = p.project_id and p.status_id = 2 and p.customer_id = :organization_id update iv_offers set status = :status where offer_id = :offer_id and status <> 'credit' select distinct customer_id from pm_projectsx where item_id in ([join $project_id ","])