Home » Developer & Programmer » Reports & Discoverer » Code to compute Price INCL of VAT
Code to compute Price INCL of VAT [message #541236] Mon, 30 January 2012 03:07 Go to next message
Maverick27
Messages: 84
Registered: October 2008
Member
HI - i have a Field item on my report which i would like to show "Price INCL of VAT". Can somebody pls give me the PL/SQL code or trigger to do this? VAT is 16%

I'm a Forms programmer, not so familiar with Reports.
I suspect, it should be something like this:

F_1 := F_1 * 1.16

Thanks

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541237 is a reply to message #541236] Mon, 30 January 2012 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Assuming that a field your report returns is named PRICE, then you have (at least) two options: the first one is to compute required value in report's query:
select 
  id,
  name,
  price,
  price * 1.16  price_incl_vat
from some_table
where ...

Another option is to create a formula column that returns NUMBER; its code would be
return (:price * 1.16);


Just a remark: what will you do when VAT changes? Will you modify all code you wrote and hard-coded 16% VAT? I suppose that you should store that value somewhere in the database and use it when required.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541238 is a reply to message #541236] Mon, 30 January 2012 03:18 Go to previous messageGo to next message
azamkhan
Messages: 557
Registered: August 2005
Senior Member
use a place hoder column column for this purpose

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541242 is a reply to message #541237] Mon, 30 January 2012 03:38 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Hi littlefoot - thanks for quick response.
We are not storing VAT in the database, so it's OK to hardcode.
Do i simply right click the field, goto PL/SQL editor; copy & paste the code return (:price * 1.16); btwn Begin & End ?

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541243 is a reply to message #541242] Mon, 30 January 2012 03:40 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541244 is a reply to message #541242] Mon, 30 January 2012 03:44 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
PS: what is a place holder column ?

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541252 is a reply to message #541244] Mon, 30 January 2012 03:50 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
My Field is F_1

:F_1 is not defined, when i try to compile.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541256 is a reply to message #541252] Mon, 30 January 2012 03:52 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What field is F_1? Please, post your query (SELECT ... statement) as well as formula you used.

P.S. What is a placeholder column? Go to Reports Help and search for it.

[Updated on: Mon, 30 January 2012 03:53]

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541260 is a reply to message #541256] Mon, 30 January 2012 04:04 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
function F_1FormatTrigger return boolean is
begin
return (:F_1 * 1.16);
--return (TRUE);
end;
  • Attachment: F_1.JPG
    (Size: 36.20KB, Downloaded 1206 times)

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541266 is a reply to message #541260] Mon, 30 January 2012 04:10 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A formula column (NOT "format trigger" you used) - as I said previously - should return NUMBER (not Boolean).

F_1 contains "price excl. VAT". You need to create a new field which will contain "price incl. VAT".

What you did is a mess. Please, forget everything you did so far, delete it and start over. This time, THINK and read what I already told you.

Also, when I ask "post your query (SELECT ... statement)", it is not because I don't have anything else to do, but when someone tries to assist, you should answer his/her questions. This time, mine.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541269 is a reply to message #541260] Mon, 30 January 2012 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
F_1 is the name of an item in the layout. You can't use those in formulaes. You need to use the name of an item in the data model. Probably price.
Also you can't use a format trigger for this. Format triggers make items appear or disappear from the layout, they have no effect on the actual values of items.
As littlefoot said, create a formula column (in the data model), then put the correct calculation in it's formula property in the property palette.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541283 is a reply to message #541269] Mon, 30 January 2012 04:44 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Folks - thanks for you patience. it seems now that everything is falling in perspective.
Qtn -1 , when trying to create Formula Col. > it (+) button is'nt being activated ? Why is that ?
See attached ,image.
  • Attachment: Formula.JPG
    (Size: 43.42KB, Downloaded 1045 times)

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541284 is a reply to message #541283] Mon, 30 January 2012 04:46 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create it in Data Model Editor; you'll find it in the toolbar.

[Updated on: Mon, 30 January 2012 04:46]

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541286 is a reply to message #541283] Mon, 30 January 2012 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because you create them in the data model view. There's a tool bar top left of the data model with some buttons. Have a look at the tool tips to see what's available.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541297 is a reply to message #541286] Mon, 30 January 2012 05:41 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Thanks Folks - finally accomplished.
Last Qtn:
How do i "ROUND OFF" the Price INCL VAT to show KES 995.00 ??
Is it done from Format Mask or done programmitically ?
I have also shown Price EXCL VAT, when multipled by 16% to give Price INCL VAT.
See attachment.
  • Attachment: final.JPG
    (Size: 11.36KB, Downloaded 1175 times)

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541299 is a reply to message #541297] Mon, 30 January 2012 05:44 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option is
return (round(:price * 1.16, 2))

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541300 is a reply to message #541299] Mon, 30 January 2012 05:51 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Hi - not working. Still showing .99 cents @ the end.
I even removed the Format Mask to show NULL.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541302 is a reply to message #541300] Mon, 30 January 2012 05:53 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Even with Format mask of NNN,NNN,NNN,NNN.00
still shows th cents @ end.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541303 is a reply to message #541300] Mon, 30 January 2012 05:54 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Even with Format mask of NNN,NNN,NNN,NNN.00
still shows th cents @ end.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541304 is a reply to message #541302] Mon, 30 January 2012 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you mean you want the two decimal places to always be zero?

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541305 is a reply to message #541302] Mon, 30 January 2012 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah, sorry, I thought you need to display 2 decimals ...

It seems that you want to ROUND to 0 places; would this help? 
return (to_char(round(:price * 1.16, 0), '999G999G990D00'))

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541307 is a reply to message #541305] Mon, 30 January 2012 06:08 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
Fatal error has occurred - report doesn't run.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541309 is a reply to message #541307] Mon, 30 January 2012 06:10 Go to previous messageGo to next message
Maverick27
Messages: 84
Registered: October 2008
Member
ORA 06502

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541312 is a reply to message #541309] Mon, 30 January 2012 06:18 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; if you used that piece of code, function doesn't return NUMBER any more, but CHAR - fix it in PL/SQL ("function its_name return CHAR ...") and, possibly, its property palette (don't have Reports here, can't check it myself).

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541314 is a reply to message #541312] Mon, 30 January 2012 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or just have it return:
return (round(:price * 1.16))

And set the format mask in the property palette to still display 2 decimal places.

Report message to a moderator

Re: Code to compute Price INCL of VAT [message #541326 is a reply to message #541314] Mon, 30 January 2012 07:18 Go to previous message
Maverick27
Messages: 84
Registered: October 2008
Member
thanks Cookie, it works, & i'm sure Littlefoot your suggestion also works, just have to test it out.
Thanks for your patience.

Report message to a moderator

Previous Topic: error in running report
Next Topic: Error message when passing the report parameter from command line
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Nov 27 01:41:24 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.