Home » Developer & Programmer » Reports & Discoverer » ORA-06502 (report builder 6i)
icon5.gif  ORA-06502 [message #312079] Mon, 07 April 2008 23:04 Go to next message
fadhzone
Messages: 61
Registered: April 2008
Member
THERE IS ERROR FOR THIS SCRIPT (BOLD)..BUT I CAN'T FIND THE ANSWER.

function BeforeReport return boolean is
S NUMBER(4);
C NUMBER(4);
ln_Count NUMBER;
STR1 VARCHAR2(200);
STR2 VARCHAR2(200);
STR3 VARCHAR2(200);
STR4 VARCHAR2(200);
STR5 VARCHAR2(200);
STR5_1 VARCHAR2(200);
STR6 VARCHAR2(200);
STR7 VARCHAR2(200);
STR7_1 VARCHAR2(200);
STR7_2 NUMBER;
l_store VARCHAR2(500);

begin

:P_PROG_ID := nvl(:P_PROG_ID,'AP040R');
:P_USER_ID := 'HQAPP002';


IF :P_USER_ID IS NOT NULL THEN
SELECT USER_STORE.STORE_CD
INTO S
FROM USER_STORE
WHERE USER_STORE.USR_ID = :P_USER_ID
AND DEFAULT_STORE= 'Y';
END IF;



IF S IS NOT NULL THEN
SELECT C.CTRL_CO_NAME
INTO :P_CO_NAME
FROM COMPANY_CONTROL C, STORE
WHERE C.CTRL_COMPANY_NO = STORE.CTRL_COMPANY_NO
AND STORE.STORE_CD = S;
END IF;

-- Check Branch Access Right
l_store:='(0';
--For i In nvl(:P_BRANCH_FR,0)..nvl(:P_BRANCH_TO,0)
FOR I IN (select distinct store_cd from user_store where USR_ID = :P_USER_ID
and store_cd between nvl(:P_BRANCH_FR,0) and nvl(:P_BRANCH_TO,0) )
LOOP

l_store:=l_store||','||TO_CHAR(I.STORE_CD);

END LOOP;


IF (:P_BRANCH_FR IS NULL and :P_BRANCH_TO IS NULL) or (:P_BRANCH_FR IS not NULL and :P_BRANCH_TO IS NULL) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD =0';
ELSIF (nvl(:P_BRANCH_FR,0)=0 and nvl(:P_BRANCH_to,0)>0 ) OR (nvl(:P_BRANCH_FR,0)>0 and nvl(:P_BRANCH_to,0)>0 ) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD IN (select distinct store_cd from user_store where USR_ID = '''
||:P_USER_ID||''' and store_cd between '||nvl(:P_BRANCH_FR,0)||' and '||nvl(:P_BRANCH_TO,0) ||')';
END IF;

IF :P_SUP_MAINCD_FR IS NOT NULL THEN
STR3 := ' AND SUPPLIER_HIST.SUP_MAINCD >= ''' || :P_SUP_MAINCD_FR || '''';
END IF;

IF :P_SUP_MAINCD_TO IS NOT NULL THEN
STR4 := ' AND SUPPLIER_HIST.SUP_MAINCD <= ''' || :P_SUP_MAINCD_TO || '''';
END IF;


IF :P_SUP_TYPE_FR IS NOT NULL AND :P_SUP_TYPE_FR<> 'ALL' THEN
IF :P_SUP_TYPE_TO IS NOT NULL AND :P_SUP_TYPE_TO<> 'ALL' THEN
STR5 := ' AND SUPD_TYPE >= ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
STR5_1 := ' AND SUPD_TYPE <= ''' || SUBSTR(:P_SUP_TYPE_TO,1,1) || '''';
ELSE
STR5 := ' AND SUPD_TYPE = ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
END IF;
END IF;

IF :P_SECTION IS NOT NULL THEN
STR6 := ' AND SECT_CD = ''' || :P_SECTION || '''';
END IF;

IF :P_STATUS IS NOT NULL AND :P_STATUS <>'ALL' THEN
IF :P_STATUS IS NOT NULL AND :P_STATUS= 'DEBIT BALANCE' THEN
STR7 := 'AND nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) -
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) < ''' || :P_STATUS || '''';
ELSIF :P_STATUS IS NOT NULL AND :P_STATUS = 'CREDIT BALANCE' THEN
STR7_1 := ' AND (nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) -
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) >= )''' || :P_STATUS ||'''';
END IF;
END IF;


:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1;

return (true);
end;
Re: ORA-06502 [message #312081 is a reply to message #312079] Mon, 07 April 2008 23:12 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Looks like it is a Oracle Report issue.

You can debug on this by adding srw.message call if it is called from Oracle Report.
Re: ORA-06502 [message #312082 is a reply to message #312081] Mon, 07 April 2008 23:19 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
HOW TO ADD IT?
Re: ORA-06502 [message #312084 is a reply to message #312079] Mon, 07 April 2008 23:25 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Check the correct syntax of srw.message in the Oracle Report Help.I have put some lines (check the syntax as I donot remember exactly) but you have to add till the end to debug to see which statement is erroring.
To me it looks like all required variables are not passed to it.


function BeforeReport return boolean is
S NUMBER(4);
C NUMBER(4);
ln_Count NUMBER;
STR1 VARCHAR2(200);
STR2 VARCHAR2(200);
STR3 VARCHAR2(200);
STR4 VARCHAR2(200);
STR5 VARCHAR2(200);
STR5_1 VARCHAR2(200);
STR6 VARCHAR2(200);
STR7 VARCHAR2(200);
STR7_1 VARCHAR2(200);
STR7_2 NUMBER;
l_store VARCHAR2(500);

begin

srw.message('1001','Before Prog Id');

:P_PROG_ID := nvl(:P_PROG_ID,'AP040R');
:P_USER_ID := 'HQAPP002';

srw.message('1002','After Prog Id');

IF :P_USER_ID IS NOT NULL THEN
SELECT USER_STORE.STORE_CD
INTO S
FROM USER_STORE
WHERE USER_STORE.USR_ID = :P_USER_ID
AND DEFAULT_STORE= 'Y';
END IF;

srw.message('1003','After First IF');

IF S IS NOT NULL THEN
SELECT C.CTRL_CO_NAME
INTO :P_CO_NAME
FROM COMPANY_CONTROL C, STORE 
WHERE C.CTRL_COMPANY_NO = STORE.CTRL_COMPANY_NO
AND STORE.STORE_CD = S;
END IF; 

-- Check Branch Access Right 
l_store:='(0';
--For i In nvl(:P_BRANCH_FR,0)..nvl(:P_BRANCH_TO,0) 
FOR I IN (select distinct store_cd from user_store where USR_ID = :P_USER_ID
and store_cd between nvl(:P_BRANCH_FR,0) and nvl(:P_BRANCH_TO,0) )
LOOP

l_store:=l_store||','||TO_CHAR(I.STORE_CD);

END LOOP; 


IF (:P_BRANCH_FR IS NULL and :P_BRANCH_TO IS NULL) or (:P_BRANCH_FR IS not NULL and :P_BRANCH_TO IS NULL) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD =0';
ELSIF (nvl(:P_BRANCH_FR,0)=0 and nvl(:P_BRANCH_to,0)>0 ) OR (nvl(:P_BRANCH_FR,0)>0 and nvl(:P_BRANCH_to,0)>0 ) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD IN (select distinct store_cd from user_store where USR_ID = '''
||:P_USER_ID||''' and store_cd between '||nvl(:P_BRANCH_FR,0)||' and '||nvl(:P_BRANCH_TO,0) ||')';
END IF; 

IF :P_SUP_MAINCD_FR IS NOT NULL THEN
STR3 := ' AND SUPPLIER_HIST.SUP_MAINCD >= ''' || :P_SUP_MAINCD_FR || '''';
END IF; 

IF :P_SUP_MAINCD_TO IS NOT NULL THEN
STR4 := ' AND SUPPLIER_HIST.SUP_MAINCD <= ''' || :P_SUP_MAINCD_TO || '''';
END IF; 


IF :P_SUP_TYPE_FR IS NOT NULL AND :P_SUP_TYPE_FR<> 'ALL' THEN 
IF :P_SUP_TYPE_TO IS NOT NULL AND :P_SUP_TYPE_TO<> 'ALL' THEN
STR5 := ' AND SUPD_TYPE >= ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
STR5_1 := ' AND SUPD_TYPE <= ''' || SUBSTR(:P_SUP_TYPE_TO,1,1) || '''';
ELSE
STR5 := ' AND SUPD_TYPE = ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
END IF; 
END IF; 

IF :P_SECTION IS NOT NULL THEN
STR6 := ' AND SECT_CD = ''' || :P_SECTION || '''';
END IF;

IF :P_STATUS IS NOT NULL AND :P_STATUS <>'ALL' THEN
IF :P_STATUS IS NOT NULL AND :P_STATUS= 'DEBIT BALANCE' THEN
STR7 := 'AND nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) - 
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) < ''' || :P_STATUS || '''';
ELSIF :P_STATUS IS NOT NULL AND :P_STATUS = 'CREDIT BALANCE' THEN
STR7_1 := ' AND (nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) - 
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) >= )''' || :P_STATUS ||'''';
END IF;
END IF; 

:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1;

return (true);
end; 

[Updated on: Mon, 07 April 2008 23:27]

Report message to a moderator

Re: ORA-06502 [message #312086 is a reply to message #312084] Mon, 07 April 2008 23:32 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
why i can't upload file with the format *.rdf
Re: ORA-06502 [message #312088 is a reply to message #312079] Mon, 07 April 2008 23:35 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Where are you trying to upload?
To the server or this forum?
If you want to upload to this forum then anacedent may help you.

[Updated on: Mon, 07 April 2008 23:35]

Report message to a moderator

Re: ORA-06502 [message #312095 is a reply to message #312088] Mon, 07 April 2008 23:45 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
i try to upload the file to this forum
so u know wut the problem is..
i must enhance the module.i must add one more parameter to the report where if user select 1-debit note the report will displays the negative values,2-credit note displays positive values and 3-all will displays all the values.
the status parameter is a drop down list contains 3 of the choice as i mentioned above.

[Updated on: Mon, 07 April 2008 23:46]

Report message to a moderator

Re: ORA-06502 [message #312096 is a reply to message #312079] Mon, 07 April 2008 23:49 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Step 1:
Create a user parameter on the Report builder.
On the properties of the user parameter mention all the values you want to display.

Step 2:
Then go to the Parameter Form Layout and select a text field.Give it any name for example 'Note' and then on the properties select the paremeter you created in step 1.

[Updated on: Mon, 07 April 2008 23:50]

Report message to a moderator

Re: ORA-06502 [message #312099 is a reply to message #312096] Mon, 07 April 2008 23:56 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
the step done.but how to put the script at the beforereport (program unit).
Re: ORA-06502 [message #312104 is a reply to message #312079] Tue, 08 April 2008 00:06 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
Then add your logic like this if your new parameter name is P_NOTE_TYPE.

Add STR8 as varchar2(200) to the declare section.

function BeforeReport return boolean is
S NUMBER(4);
C NUMBER(4);
ln_Count NUMBER;
STR1 VARCHAR2(200);
STR2 VARCHAR2(200);
STR3 VARCHAR2(200);
STR4 VARCHAR2(200);
STR5 VARCHAR2(200);
STR5_1 VARCHAR2(200);
STR6 VARCHAR2(200);
STR7 VARCHAR2(200);
STR7_1 VARCHAR2(200);
STR7_2 NUMBER;
STR8 VARCHAR2(200);
l_store VARCHAR2(500);


Then add the following within BEGIN


IF :P_NOTE_TYPE IS NOT NULL THEN
STR8 :=' AND NOTE_TYPE = ''' ||:P_NOTE_TYPE|| ''''
END IF; 




Also modify where clause
:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1;

to accomodate str8
:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1||STR8;


[Updated on: Tue, 08 April 2008 00:18]

Report message to a moderator

Re: ORA-06502 [message #312106 is a reply to message #312079] Tue, 08 April 2008 00:20 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
If you cannot manage the changes then please upload the rdf.
Though I am also a newbie here for which I cannot guide you how to upload the rdf.

anacedent is my guiding angel on this forum, anacedent can help you on this.
Re: ORA-06502 [message #312107 is a reply to message #312079] Tue, 08 April 2008 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Though I am also a newbie here for which I cannot guide you how to upload the rdf.
But apparently have not yet found the Private Messages.
Re: ORA-06502 [message #312123 is a reply to message #312107] Tue, 08 April 2008 00:57 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
i'm really new here n new in developing..

i'm seeking the guide..that's y i keep ask n ask
Re: ORA-06502 [message #312125 is a reply to message #312123] Tue, 08 April 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
fadhzone wrote on Tue, 08 April 2008 07:57
i'm really new here n new in developing..

i'm seeking the guide..that's y i keep ask n ask

First read OraFAQ Forum Guide and don't use IM/SMS speak.

Regards
Michel

Re: ORA-06502 [message #312127 is a reply to message #312125] Tue, 08 April 2008 01:08 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
ok sorry..

can i upload a file with rdf format?
Re: ORA-06502 [message #312143 is a reply to message #312127] Tue, 08 April 2008 01:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes (at least, at this moment Allowed Files Extensions is set to "unrestricted").
Re: ORA-06502 [message #312179 is a reply to message #312143] Tue, 08 April 2008 02:46 Go to previous message
fadhzone
Messages: 61
Registered: April 2008
Member
thanks to those who were helping me.now i get the idea to solve the problem.this forum helped me much!thanks
Previous Topic: Report parameter
Next Topic: Exporting Discoverer Report to Common Directory
Goto Forum:
  


Current Time: Sat Dec 10 18:30:53 CST 2016

Total time taken to generate the page: 0.04435 seconds