OraFAQ Forum: Forms » Print all records retrieved by the query find form

Home » Developer & Programmer » Forms » Print all records retrieved by the query find form (Oracle forms 10g)

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

Print all records retrieved by the query find form [message #608221]

Mon, 17 February 2014 12:39

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi All,

I am working on a query find form which has a start date , end date which are mandatory columns and find button on the control block. In addition to this there are Process ID , Process Name and Dept No items on the same control block which are optional columns. User need not enter the optional columns.
Based on the start date and end date , when the user clicks the Find button - all records are retrieved.
But the client requirement is that there must be a Print button which would enable the user to print all the records that are retrieved.
How can I copy them to an excel file or a PDF file ?

Please let me know.

Thanks
Megha

-------------------------------

Update : After further research I found that I could use the Reports option available to the Object Navigator. See the attached screenshot. So, I checked Base report on block and the Master Data block as the block name.
Also, changed the follwing on the property palette for that report
execution mode: Runtime
Report Destination type : screen
Communication mode: Synchronous
Data Source Data block : Name of the Master Data block

At this point , I am not sure how can I invoke this report when the form retrieves records.
Please let me know.

Thanks
Megha

Attachment: reprots.JPG
(Size: 22.90KB, Downloaded 776 times)

[Updated on: Mon, 17 February 2014 14:07]

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608227 is a reply to message #608221]

Mon, 17 February 2014 14:12

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In a POST-QUERY block-level trigger get the last query executed - in order to do that, use GET_BLOCK_PROPERTY and its LAST_QUERY property. Then find the first WHERE (using the INSTR function) and select the rest of the query into a variable (use SUBSTR to do that). Once you have the whole WHERE clause, pass it to a report. Report will use that WHERE clause as a lexical parameter. The result would have exactly the same record set as form's data block.

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608229 is a reply to message #608227]

Mon, 17 February 2014 15:12

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Littlefoot,

I am not clear on this part.
"Then find the first WHERE (using the INSTR function) and select the rest of the query into a variable (use SUBSTR to do that). Once you have the whole WHERE clause, pass it to a report. Report will use that WHERE clause as a lexical parameter."
I was able to get the last query that was executed.
what is the next step?

Thanks
Megha.

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608239 is a reply to message #608229]

Tue, 18 February 2014 01:09

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Last query looks like this:

select ename, job, sal from emp where deptno = 10 and sal > 2000

You have to extract the WHERE part, so: find where the "WHERE" is (with INSTR function), and then extract it out of the "last query" with the SUBSTR function:

SQL> WITH test
  2          AS (SELECT 'select ename, job, sal from emp where deptno = 10 and sal > 2000'
  3                        last_query
  4                FROM DUAL)
  5  SELECT INSTR (last_query, 'where') l_where,
  6         SUBSTR (last_query, INSTR (last_query, 'where')) l_lex
  7    FROM test;

   L_WHERE L_LEX
---------- --------------------------------
        33 where deptno = 10 and sal > 2000

SQL>

L_LEX is what you pass to the report and use as a lexical variable in report's query:

select ename, job, sal
from emp
&l_lex

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608315 is a reply to message #608239]

Tue, 18 February 2014 10:07

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Thank you little foot.

I tried to  fnd_request.submit_request ('FND',                -- application
                                        'COCN_PGM_SHORT_NAME',-- program short name
                                        '',                   -- description
                                        '',                   -- start time
                                        FALSE,                -- sub request
                                        'Argument1',          -- argument1
                                        'Argument2',          -- argument2
                                        'N',                  -- argument3
                                        NULL,                 -- argument4
                                        NULL,                 -- argument5
                                       'Argument6',          -- argument6
                                       CHR (0)               -- represents end of arguments
                                 );

The program short name is for the report process.
I guess this is the other way which did not work.
----------------------------------------------------------------------------------
But the one that you suggested, I want to get this right.
First I defined a report under the reports section in the object navigator of the forms builder tool.
Next, I try to get the last query that was executed on the form.
Next, how do I pass the lexical parameter to the report?
Do I use something like

DECLARE 
 repid REPORT_OBJECT;
 v_rep VARCHAR2(100); 
rep_status VARCHAR2(20);
BEGIN repid := FIND_REPORT_OBJECT('REPORT195'); 
v_rep := RUN_REPORT_OBJECT(repid); 
END;

I am getting an error when I write the above code on a block level trigger. REPORT_OBJECT is not recognised.
All I am trying to do is get the records retrieved by the screen.
Under File -> Export , export is greyed out. How can I enable it ?

Thanks
Megha

[Updated on: Tue, 18 February 2014 10:09]

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608329 is a reply to message #608315]

Tue, 18 February 2014 14:46

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not familiar with "fnd_request.submit_request".

Report I was talking about is created with Reports Builder.

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608330 is a reply to message #608329]

Tue, 18 February 2014 14:47

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Littlefoot,

Thank you for the reply.
Please let me know how do I pass the lexical parameter from the oracle form to the oracle report?

Thanks
Megha

[Updated on: Tue, 18 February 2014 14:49]

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608331 is a reply to message #608330]

Tue, 18 February 2014 14:49

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You don't pass a lexical parameter. You just pass a parameter. "Lexical parameter" is a Reports term.

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608403 is a reply to message #608331]

Wed, 19 February 2014 07:40

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Hi Littlefoot,

I have developed a report that can accept parameters .
How do I pass a parameter from the form to the report ?

Thanks
Megha

Report message to a moderator

Re: Print all records retrieved by the query find form [message #608458 is a reply to message #608403]

Thu, 20 February 2014 00:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How to pass parameter from oracle forms to reports

Report message to a moderator

Previous Topic:	webutil !
Next Topic:	Multi records block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:34:25 CDT 2024