OraFAQ Forum: Forms » From Date and to Date in Query Find Form in Custom Oracle Apps Form

Home » Developer & Programmer » Forms » From Date and to Date in Query Find Form in Custom Oracle Apps Form (Oracle Forms 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603693]

Wed, 18 December 2013 08:56

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi,

I am trying to develop a query find form based.
There are two data blocks - Master block and detail block for a particular invoice.
But I have to limit the data based on start_date and end_date that the user enters , and clicks find button.

Control_blk:
So I created another block called control block that has start_date , end_date and find button. Start date and end date are non database columns
On the find button , I put a when _button_pressed trigger.

Code: [Select all] [Show/ hide]

go_block('INVOICE_BLK');
do_key('execute_query');

Invoice Block : ( master block )
created a master block that has the items invoice_id , first_name,last_name , account_type and agency_name.

Then created a pre-query trigger that has the code to populate data.

The query that populates the data is

Code: [Select all] [Show/ hide]

select i.invoice_id,
       i.first_name,
       i.last_name,
       cust.account_type,
       a.agency_name
into :invoice_blk.invoice_id,
  :invoice_blk.first_name,
       :invoice_blk.last_name,
:invoice_blk.account_type,
      :invoice_blk..agency_name    
from  invoice i , customer cust , agency a 
where i.invoice_id = cust.invoice_id 
and   i.agency_id  = a.agency_id 
and     cust.add_date between  :control_blk.start_date  and :control_blk.end_date;

But I get an error saying Unable to perform query.
Please let me know how can I fix this issue.

Thanks
Megha

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603694 is a reply to message #603693]

Wed, 18 December 2013 09:11

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Instead of writing code to fetch the matching data you need to write code to set the default where clause on the invoice_blk and let form builder query functionality take care of the rest.

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603695 is a reply to message #603694]

Wed, 18 December 2013 09:23

deepakmannazhi
Messages: 137
Registered: February 2010
Location: Dubai, UAE

Senior Member

Hai , with reference to above comment, please try this in PRE-QUERY trigger

Set_Block_Property('INVOICE_BLK',DEFAULT_WHERE,'add_date between :control_blk.start_date and :control_blk.end_date');

[Updated on: Wed, 18 December 2013 09:23]

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603696 is a reply to message #603695]

Wed, 18 December 2013 09:27

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Deepak ,

Thank you for the reply. But where should I write the below select query ?

Code: [Select all] [Show/ hide]

select i.invoice_id,
       i.first_name,
       i.last_name,
       cust.account_type,
       a.agency_name
into :invoice_blk.invoice_id,
  :invoice_blk.first_name,
       :invoice_blk.last_name,
:invoice_blk.account_type,
      :invoice_blk..agency_name    
from  invoice i , customer cust , agency a 
where i.invoice_id = cust.invoice_id 
and   i.agency_id  = a.agency_id

Thanks
Megha

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603697 is a reply to message #603696]

Wed, 18 December 2013 09:30

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

You shouldn't write it anywhere, it's the wrong approach.

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603699 is a reply to message #603696]

Wed, 18 December 2013 09:33

deepakmannazhi
Messages: 137
Registered: February 2010
Location: Dubai, UAE

Senior Member

Sorry Please ignore above comment

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603700 is a reply to message #603697]

Wed, 18 December 2013 09:35

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

HI Cookiemonster,

For master block (invoice_blk) that I created the 'query data source type' is Table and 'Query data source name' is the Invoice table.
Then how will the query I have be executed ?
I am a beginner in Oracle forms and trying to understand how this works.
Please help.

Report message to a moderator

Re: From Date and to Date in Query Find Form in Custom Oracle Apps Form [message #603702 is a reply to message #603700]

Wed, 18 December 2013 09:43

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

execute_query does it all for you. You just need to set the correct where clause

Report message to a moderator

Previous Topic:	Records not display when i selected
Next Topic:	Forms runtime quits when Alt key button is pressed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 06:27:30 CDT 2025