Re: Invoice with Reports

From: Steve Davis <spd_at_patrol.i-way.co.uk>
Date: Mon, 27 Apr 1998 18:39:33 GMT
Message-ID: <3544d226.0_at_news.i-way.co.uk>

Terenzio,

try the following:

I presume you already have a query which lists the rows for a given invoice, and that the "rows" table has some kind of invoice indicator (say invoice_id)

Create a new query:
select r.invoice_id

,          sum(r.qty*r.un_value) extended_price
,          v.vat_value
,          v.vat_code
,          sum(r.qty*r.un_value*v.vat_value/100) vat_amount
from vat_table v,
         rows_table r

where r.vat_code = v.vat
group by r.invoice_id

,               v.vat_value
,               v.vat_code

Link the invoice_id from the original query to the invoice_id of the new query

(You can use the alternative method of linking the groups - if you do this, don't include invoice_id in the 'select' or 'group by' clauses of the new query, but include 'and r.invoice_id = :invoice_id' in the where clause - then link the groups)

I presume you want to show this information in the bottom margin, not the footer of the report.
You CAN put it in the margin, but it's a lot easier to put it in the body of the report. If it needs to be in a particular position on the page, just make sure everything above it is in a vertical fixed frame.

To get the totals, use summary columns.

Hope this helps,
Steve

"Terenzio (Datasyst)" <mailds_at_tin.it> wrote:

>Please I need some help regarding Oracle Reports from Developer 2000 rel. 2.0
>I have to create an invoice
>I have two tables:
>- Rows (with columns Row No.,Quantity, Unitary Value, Vat Code)
>- VAT (with columns Vat Code, Vat Value)

>My problem is that I want to calculate and print, in invoice footer, the total amount of Values and
>VATs grouped by VAT value.

>Example

>Table VAT
>Row No. Q.ty Un.Value VAT Code VAT VAT Value
>-------------------------------------------------------
>---------------------------------
>1 5 500 AA
>AA 20
>2 10 250 AA
>BB 10
>3 20 300 BB
>CC 5
>4 10 300 CC
>5 10 500 AA
>6 30 100 BB
>and so on
>(Note that the rows printing is not order by VAT code but by Row No.)

>The total I want is following:

>Q.ty * Un.Value VAT Value % VAT
>--------------------------------------------------------------------
> 10.000 20 (AA) 2.000
> 9.000 10 (BB) 900
> 3.000 5 (CC) 150
>--------------------------------------------------------------------
> 22.000 3.050 = £ 25.050
>(Total amount of the invoice)
>Thanks for Help!!!!
>Franco
Received on Mon Apr 27 1998 - 20:39:33 CEST