Home » Developer & Programmer » Forms » Running Oracle Report Through Form6i and validating between two dates (Developer 6)
icon9.gif  Running Oracle Report Through Form6i and validating between two dates [message #443556] Tue, 16 February 2010 04:45 Go to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
I want to call a report through forms 6i and i want to send a parameter from forms6i to report 6i. in which i want to take records between two dates. Please help

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443557 is a reply to message #443556] Tue, 16 February 2010 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what exactly is preventing you from doing that?

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443564 is a reply to message #443557] Tue, 16 February 2010 05:10 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
I developed a parametric form like:

Select Starting Date: =============(x.y.z)
Select Ending Date: =============(x.y.z)
Select Course Date: =============(x.y.z)

============
PRINT REPORT
============
Now I want to send parameters to report. 1 for course and 2nd for date, in which the report should be display between starting and ending dates.
Please Help
Thanks

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443566 is a reply to message #443564] Tue, 16 February 2010 05:12 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Pass parameters from form to report; use them in report (WHERE some_date BETWEEN first_parameter AND second_parameter).

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443567 is a reply to message #443557] Tue, 16 February 2010 05:15 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
I want to write a trigger on the above push button i.e "Print Report"

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443568 is a reply to message #443567] Tue, 16 February 2010 05:16 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Wait Please!
I am sending you my own created trigger. Please Read it

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #443570 is a reply to message #443566] Tue, 16 February 2010 05:18 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
DECLARE

PL_ID PARAMLIST;
P_EXP DATE;
V_ALT NUMBER;
STR VARCHAR2(1000):='';
BEGIN

STR := STR || ' WHERE CASH_MEMO_DETAIL.SUBMIT_DATE BETWEEN ' ||:MAIN_BLOCK.START_DATE ||' AND '|| :MAIN_BLOCK.END_DATE ;

PL_ID := GET_PARAMETER_LIST('PLIST');

IF NOT ID_NULL(PL_ID) THEN
DESTROY_PARAMETER_LIST('PLIST');
END IF;

PL_ID := CREATE_PARAMETER_LIST('PLIST');

IF NOT ID_NULL(PL_ID) THEN
ADD_PARAMETER(PL_ID,'P_EXP',TEXT_PARAMETER,str);
ADD_PARAMETER(PL_ID,'PARAMFORM',TEXT_PARAMETER,'YES');
ADD_PARAMETER(PL_ID,'MAXIMIZE',TEXT_PARAMETER,'YES');
END IF;

RUN_PRODUCT(REPORTS,'.\REPORTS\FEE_SLIP.REP',ASYNCHRONOUS, RUNTIME,FILESYSTEM, PL_ID,NULL);
END;

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #446937 is a reply to message #443570] Thu, 11 March 2010 00:45 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Sorry that I have been so long in replying. Have you solved your problem?

Okay, I have read your code. I recommend using 'web.show_document' but what I need to know is what is happening when you do run your code. Dates are a probem in that the format can be different between the different machines on which yor code is running. I suggest using a 'to_date(to_char(your_date,'yyyymmdd'),'yyyymmdd')' to push the date through to the other machine.

David

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #446972 is a reply to message #446937] Thu, 11 March 2010 03:21 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Hello Sir,
My problem is still unsolved. I have sent trigger to you,
Please check it and give me some more suggestions.
you can contact with me on "m_khaleeq2006@yahoo.com"
Thanks:
Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #446982 is a reply to message #446937] Thu, 11 March 2010 04:31 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Dear Sir!
I am sending you a text file as a attachment, please read it.
Thanks
Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #446987 is a reply to message #446937] Thu, 11 March 2010 04:43 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Dear Sir!
I was forgot to attach file.
I am again sending you a text file as a attachment, please read it.
Thanks
Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447561 is a reply to message #446987] Tue, 16 March 2010 01:43 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
As I said earlier use something like 'to_date(to_char(:MAIN_BLOCK.START_DATE,'dd-mmm-yyyy'),'dd-mmm-yyyy')'. This is a typical 'nls_date_format' problem. I suggest you use 'message;pause' pairs to display the date fields and the 'STR' variable so that you can SEE what the machine is doing.

David

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447602 is a reply to message #447561] Tue, 16 March 2010 05:56 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Hello Sir,
Many many thanks for helping me.
Can I discuss my other some problems when they will occur?

Thanks Again:
Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447604 is a reply to message #447602] Tue, 16 March 2010 06:00 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Can I discuss my other some problems when they will occur?

Of course; that's what Forum is for.

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447606 is a reply to message #447604] Tue, 16 March 2010 07:32 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
I used orafaq.com only on that time when I need some help.
Sometimes when I have a query on this Forum then there is no reply.
You have remembered me, then I want to join you as a professional friend.
My other account is m_khaleeq2006@yahoo.com

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447609 is a reply to message #447606] Tue, 16 March 2010 08:08 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Sometimes when I have a query on this Forum then there is no reply.

You have opened two topics so far; both of them have been discussed to a certain point.

In one of them you haven't replied so - how can anyone know whether you managed to solve the problem or not?

The second one is this very thread; your last message was unanswered until David saw it - I don't think that it was result of people not wanting to help, but something else - for example, no idea what might be wrong.
As you didn't raise it to the top, the topic somehow sank and waited for David to resurrect it.

As far as I can tell, vast majority of questions on this Forum is answered so - please, do come back, discuss your (and everyone else's) problems.

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447884 is a reply to message #447609] Thu, 18 March 2010 05:52 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Respectful Sir!
I have a table named "Student" having following attributes:
STD_ID
STD_NAME
STD_FNAME
...
...
...
...
and
POF_NPOF

The "POF_NPOF" attribute, I have set data type "CHAR(1)" in which I stores only "P" or "N".
NOTE:-(Remember that I want to display it on report as "P" for "POF Student" and "N" for "Non POF Student")

Now in report builder, I have created a formula function named "STATUS" and attached the following code:
=============================================================
function STATUSFormula return varchar2(20) is
STD_STATUS CHAR(20);
begin

IF :POF_NPOF = 'P' OR :POF_NPOF = 'p' THEN

STD_STATUS:='POF';

ELSE
STD_STATUS := 'Non POF';
END IF;

RETURN STD_STATUS;
end;
==============================================================
I have set the source property of my required report textbox to "STATUS" but,
The above code is not working properly and no value is returned by this function.
If you have an idea then please help.
Thanks:
Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447893 is a reply to message #447884] Thu, 18 March 2010 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does POF_NPOF column contain? Which value do you see on the screen (when you run the report)?

Try to rewrite the function as following:
function statusformula return char is
  std_status varchar2(20);
begin
  if upper(:pof_npof) = 'P' then
     std_status := 'POF';
  else
     std_status := 'Non POF';
  end if;
  return (std_status);
end;

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447910 is a reply to message #447893] Thu, 18 March 2010 07:28 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
The "POF_NPOF" column contains only "P" or "N" in database.
Before applying your code, No output was displayed.
Now, I have apply your suggested code but it only displays "Non POF" for all records.
Column contains "N" for "Non POF" and some records "P" for "POF".
I want to display if:
Column contains "P" then it should display "POF" and if column contains "N" then it should display "Non POF"

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447912 is a reply to message #447910] Thu, 18 March 2010 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the POF_NPOF column's datatype?

You said that it contains "P" and "N" only. What is the result of the following query:
select pof_npof, length(pof_npof), count(*) cnt
from this_table
group by pof_npof, length(pof_npof);

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447914 is a reply to message #447912] Thu, 18 March 2010 07:43 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
The data type of POF_NPOF is char(1)
and your query displays the following output.

POF_NPOF LENGTH(POF_NPOF) CNT
---------- ---------------- ----------
P 10 3

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447917 is a reply to message #447912] Thu, 18 March 2010 07:57 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
In POF_NPOF field I stored "P" for those who belonged to "POF Area" and "N" for those who are not belonged to "POF Area". I only stores "N" or "P" in this field because I set its data type char(1).

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447918 is a reply to message #447912] Thu, 18 March 2010 08:00 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Oo sorry, its data type is char(10), I forgot.

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447921 is a reply to message #447918] Thu, 18 March 2010 08:09 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens when you include TRIM function:
function statusformula return char is
  std_status varchar2(20);
begin
  if upper(trim(:pof_npof)) = 'P' then      --> here
     std_status := 'POF';
  else
     std_status := 'Non POF';
  end if;
  return (std_status);
end;

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447926 is a reply to message #447921] Thu, 18 March 2010 08:34 Go to previous messageGo to next message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Oooo yes sir,
I used trim( ) but an error message is generated i.e, "TRIM must be declare"
Then I used RTRIM ( ) then the output is displayed as I want, and now my problem is solved.
You are so genius.

Thanks for helping

Muhammad Khaleeq

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447927 is a reply to message #447926] Thu, 18 March 2010 08:56 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No, I'm not what you think that I am.

But you should consider NOT using CHAR datatype, but rather use VARCHAR2. If you did that in the first place, you wouldn't have that problem (and save 4 hours of your precious time).

Report message to a moderator

Re: Running Oracle Report Through Form6i and validating between two dates [message #447934 is a reply to message #447927] Thu, 18 March 2010 09:57 Go to previous message
m_khaleeq2006
Messages: 24
Registered: February 2010
Location: Pakistan
Junior Member
Okay sir, a bundle of thanks, you have solved my problem very quickly, thanks again for your time.
Regards,

Muhammad Khaleeq
(Software Developer)
(Welfare Computer Centre, Wah Cantt)

Report message to a moderator

Previous Topic: Maximising Forms window
Next Topic: Inserting into a table using data from a text box
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 00:30:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.