By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

Dynamic Report parameter

P: 14
I have 2 parameters.

1)1st parameter has independent value set and it will have only 2 possible values 'week' or 'month'.

2)2nd parameter has "table" value set which should display the
'weekno-year'of last 5 years if parameter1 value passed is 'week'.

3)2nd parameter value set should display the 'month-year' of last 5 years if parameter1 value passed is 'month'.

My code for 2nd value set "table name" is :-

(SELECT DECODE(:$FLEX$.first_value_set,'week',(TO_CHAR(SYS DATE+1-((rownum)*7) ,'ww-YYYY')),TO_CHAR(ADD_MONTHS(SYSDATE,-rownum+1),'Month-YYYY')) week_month
FROM all_objects

and in where/order by clause:

where rownum< DECODE(:$FLEX$.xx_period_type,'week',261,61))

This code is working fine from sqlplus on oracle client.
But in APPS it is throwing an error like....
APP-FND-00005:INCORRECT ARGUMENTS WERE PASSED TO USER EXIT #MESSAGE_TOKEN.

Cna any one help.
Thanks in advance.
Mar 13 '08 #1
Share this Question
Share on Google+
24 Replies


amitpatel66
Expert 100+
P: 2,367
Try once removing the WHERE/ORDER BY clause and check if it is working without any error
Mar 13 '08 #2

P: 14
Try once removing the WHERE/ORDER BY clause and check if it is working without any error

No Amit. It is still throwing the same error, even i removed the where clause!!!
Mar 13 '08 #3

amitpatel66
Expert 100+
P: 2,367
No Amit. It is still throwing the same error, even i removed the where clause!!!
Ok. I tried your query in TOAD and it works fine. I think the problem here is becuase of rownum if I am not wrong. Ok a small change to your query and lets see if it works:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DECODE(:$FLEX$.first_value_set,'week',(TO_CHAR(SYS DATE+1-((x.rn)*7) ,'ww-YYYY')),TO_CHAR(ADD_MONTHS(SYSDATE,-x.rn+1),'Month-YYYY') week_month FROM (SELECT rownum rn FROM all_objects) x
  3.  
In Where clause,you add this:

x.rn < DECODE(....);
Mar 13 '08 #4

P: 14
Ok. I tried your query in TOAD and it works fine. I think the problem here is becuase of rownum if I am not wrong. Ok a small change to your query and lets see if it works:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DECODE(:$FLEX$.first_value_set,'week',(TO_CHAR(SYS DATE+1-((x.rn)*7) ,'ww-YYYY')),TO_CHAR(ADD_MONTHS(SYSDATE,-x.rn+1),'Month-YYYY') week_month FROM (SELECT rownum rn FROM all_objects) x
  3.  
In Where clause,you add this:

x.rn < DECODE(....);

Hi Amit...

I tried with the code you sent... But the same old error repeated again. :(
Mar 13 '08 #5

amitpatel66
Expert 100+
P: 2,367
Hi Amit...

I tried with the code you sent... But the same old error repeated again. :(
Is your report multi org?

It shows problem with USER EXIT value which is incorrect?
can you post that code here for reference?
Mar 13 '08 #6

P: 14
Is your report multi org?

It shows problem with USER EXIT value which is incorrect?
can you post that code here for reference?

Here is my code :
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DECODE (:p1,'week', TO_CHAR (case.creation_date, 'IW-YYYY' ),
  3. 'month',TO_CHAR (case.creation_date, 'MON-YYYY')) "Week_inflow",
  4. case.type "type",
  5. case.dept "dept",
  6. COUNT (case.instance_id) "inflow",
  7. SUM (DECODE (case.ola_got , '', 0, 1)) "met_OLA",
  8. SUM (DECODE (case.dismissed, '', 0, 1)) "dismissed",
  9. ( SUM (DECODE (case.ola_got , '', 0, 1))
  10. - SUM (DECODE (case.dismissed, '', 0, 1))
  11. ) "actual_inflow",
  12. test1.percentage_settled
  13. (DECODE (:p1,'week', TO_CHAR (case.creation_date, 'IW-YYYY'),
  14. 'month',TO_CHAR (case.creation_date, 'MON-YYYY')),
  15. case.type,
  16. case.dept,
  17. ( SUM (DECODE (case.ola_got , '', 0, 1))
  18. - SUM (DECODE (case.dismissed, '', 0, 1))
  19. )
  20. ) "Percentage_settled"
  21.  
  22. FROM case_v case,
  23. casecomp_v casecomponent,
  24. executive_v executive
  25. WHERE 
  26. case.instance_id = casecomponent.case_instance_id
  27. AND casecomponent.casecomp_instance_id = executive.casecomponent_id
  28. AND (DECODE(:p1,'week' ,TO_CHAR (case.creation_date, 'IW-YYYY'),
  29. 'month',TO_CHAR (case.creation_date, 'MM-YYYY'))) 
  30. BETWEEN 
  31. DECODE(:p1,'week',:p2,'month',TO_CHAR(to_date(:p2,'MM-YYYY'),'MM-YYYY')) 
  32. AND DECODE(:p1,'week',:p3,'month',TO_CHAR(to_date(:p3,'MM-YYYY'),'MM-YYYY')) 
  33. AND ((case.type IS NULL)
  34. OR 
  35. (case.type IS NOT NULL AND case.type = :p4)
  36. )
  37. GROUP BY TO_CHAR (case.creation_date, 'IW-YYYY'),
  38. TO_CHAR (case.creation_date, 'MON-YYYY'),
  39. case.type,
  40. case.dept
  41. ORDER BY 1 DESC
  42.  
  43.  
  44.  
  45. --and the function test1 code is :
  46.  
  47.  
  48. CREATE OR REPLACE PACKAGE BODY APPS.test1
  49. IS
  50.  
  51. FUNCTION percentage_settled (
  52. p_week_inflow VARCHAR2,
  53. p_type case_v.type%TYPE,
  54. p_delivery_dept case_v.dept%TYPE,
  55. p_actual_inflow NUMBER
  56. )
  57. RETURN NUMBER
  58. IS
  59. v_percentage_inflow NUMBER (10);
  60. v_count NUMBER (10);
  61. BEGIN
  62. SELECT COUNT (*)
  63. INTO v_count
  64. FROM case_v ,
  65. casecomp_v casecomponent,
  66. executive_v executive
  67. WHERE case_v.instance_id = casecomponent.case_instance_id
  68. AND casecomponent.casecomp_instance_id = executive.casecomponent_id
  69. AND (TO_CHAR (case_v.creation_date, 'iw-yyyy') = p_week_inflow or 
  70. TO_CHAR (case_v.creation_date, 'mon-yyyy')= p_week_inflow)
  71. AND case_v.type = p_type
  72. AND case_v.dept = p_delivery_dept
  73. AND executive.status = 'Gesloten';
  74.  
  75. IF p_actual_inflow = 0
  76. THEN
  77. v_percentage_inflow := null;
  78. ELSE
  79. v_percentage_inflow := ((v_count / p_actual_inflow) * 100);
  80. END IF;
  81.  
  82. RETURN round(v_percentage_settled,0);
  83. END percentage_settled;
  84. END test1;
  85.  
  86.  
Mar 13 '08 #7

amitpatel66
Expert 100+
P: 2,367
What I am looking at is your source code that you have placed in before and after report trigger of the report.

Please clarify, Are you creating a report using reports builder or is this some other report?

I would like to have a look at the code SRW.USER_EXIT(..) that you would have placed in BEFORE and AFTER report trigger of a rdf file.
Mar 13 '08 #8

P: 14
I am not using report builder to generate a report.
My client has provided a tool which generates an XML output, and that is represented using RTF.
Mar 14 '08 #9

P: 14
What I am looking at is your source code that you have placed in before and after report trigger of the report.

Please clarify, Are you creating a report using reports builder or is this some other report?

I would like to have a look at the code SRW.USER_EXIT(..) that you would have placed in BEFORE and AFTER report trigger of a rdf file.

This error is displayed while creating the value set itself individually.
This has no link with the report or query.
Mar 14 '08 #10

amitpatel66
Expert 100+
P: 2,367
This error is displayed while creating the value set itself individually.
This has no link with the report or query.
Just one last test else will give you another suggestion. If the value of first valueset is week then just say :Select rownum from all_objects for second value set and test if this atleast works? And post back what happened?
Mar 14 '08 #11

P: 14
Just one last test else will give you another suggestion. If the value of first valueset is week then just say :Select rownum from all_objects for second value set and test if this atleast works? And post back what happened?

Hi Amit.

I wrote this query in the Table name field

(SELECT DECODE(:p1,'week',rownum,NULL) week_month FROM all_objects)

Now it is not giving any error in the value set . But when i submit a request to run, it is showing an error like

APP-FND-01242: can't read value from field P1


I think we cant write ":$FLEX$" in the select stmt....
It should be used only in the where clause right???

Thanks
Mar 14 '08 #12

amitpatel66
Expert 100+
P: 2,367
Hi Amit.

I wrote this query in the Table name field

(SELECT DECODE(:p1,'week',rownum,NULL) week_month FROM all_objects)

Now it is not giving any error in the value set . But when i submit a request to run, it is showing an error like

APP-FND-01242: can't read value from field P1


I think we cant write ":$FLEX$" in the select stmt....
It should be used only in the where clause right???

Thanks
No, you can make use of $FLEX$ in Table Name field. DO that because P1 is the internal parameter used in the rdf files and not in oracle applications. Use the first value set name in the Tablename field, and check if it works.
Mar 14 '08 #13

P: 14
No, you can make use of $FLEX$ in Table Name field. DO that because P1 is the internal parameter used in the rdf files and not in oracle applications. Use the first value set name in the Tablename field, and check if it works.


(SELECT DECODE(:$FLEX$.xx_period_type,'week',rownum,NULL) week_month FROM all_objects)


Throwing an error "invalid arguments are passed" in the value set itself.
Mar 14 '08 #14

amitpatel66
Expert 100+
P: 2,367
(SELECT DECODE(:$FLEX$.xx_period_type,'week',rownum,NULL) week_month FROM all_objects)


Throwing an error "invalid arguments are passed" in the value set itself.
Ok. Not sure why it is not allowing. anyways, what you can do is just say in table name field:
Expand|Select|Wrap|Line Numbers
  1.  
  2. select <calculation for week> AS data,'Week' AS week_month from all_objects
  3. UNION ALL
  4. select <calculation of month>,'Month' from all_objects
  5.  
And in WHERE clause add where condition:

WHERE data<= .....
AND week_month = :$FLEX$.firstvalueset
Mar 14 '08 #15

P: 14
Expand|Select|Wrap|Line Numbers
  1.  select <calculation for week> AS data,'Week' AS week_month from all_objects
  2. UNION ALL
  3. select <calculation of month>,'Month' from all_objects
  4.  
And in WHERE clause add where condition:
WHERE data<= .....
AND week_month = :$FLEX$.firstvalueset

We cannot use alias name "week_month " in the where clause like this.
"week_month = :$FLEX$.firstvalueset".


I found the following link expalining abt the value sets. In that it is given like we can not use $FLEX$ in table name, value and ID fields.

http://www.oracleappshub.com/aol/aol-valueset-a-beginner-guide/
Mar 14 '08 #16

amitpatel66
Expert 100+
P: 2,367
We cannot use alias name "week_month " in the where clause like this.
"week_month = :$FLEX$.firstvalueset".


I found the following link expalining abt the value sets. In that it is given like we can not use $FLEX$ in table name, value and ID fields.

http://www.oracleappshub.com/aol/aol...eginner-guide/
Did you try giving a SQL statement in the default value of the concurrent program parameters screen for this parameter?
Mar 14 '08 #17

P: 14
No. I didn't do that...
Mar 17 '08 #18

amitpatel66
Expert 100+
P: 2,367
No. I didn't do that...
Why dont you try that and check if the default value is correctly set for second parameter based on the value of the first parameter selected.
Mar 17 '08 #19

P: 14
Why dont you try that and check if the default value is correctly set for second parameter based on the value of the first parameter selected.
What about the valuset to be given there??
Mar 17 '08 #20

amitpatel66
Expert 100+
P: 2,367
What about the valuset to be given there??
Any value set that is of type table and can hold varchar2(50) as i do not think that the LOV for your query will be more than 10 characters....
Mar 17 '08 #21

P: 14
Any value set that is of type table and can hold varchar2(50) as i do not think that the LOV for your query will be more than 10 characters....
HI Amit ...

Thanks fro your guidence. Even by using default values, it was throwing some error. So i just used the followind code and its working fine.

Expand|Select|Wrap|Line Numbers
  1.  
  2. --Table name : all_objects
  3.  
  4. value : Decode (:$FLEX$.xx_period_type,'week', (TO_CHAR (sysdate+1- ((rownum)*7 ),'WW-YYYY') ),'month',to_char( add_months( sysdate,- rownum+1),'month-yyyy') )
  5. ID : Decode (:$FLEX$.xx_period_type,'week', (TO_CHAR (sysdate+1- ((rownum) *7 ),'WW-YYYY') ),'month',to_char( add_months (sysdate,-rownum+1),'month-yyyy') )
  6.  
  7. --and in where clause
  8.  
  9. where rownum < Decode (:$FLEX$.xx_period_type,'week',261,'month',61 )
  10. order by 1
  11.  
  12.  


This is working fine... :)

Thank u...

Thanks & Regards
Mar 19 '08 #22

amitpatel66
Expert 100+
P: 2,367
HI Amit ...

Thanks fro your guidence. Even by using default values, it was throwing some error. So i just used the followind code and its working fine.

Expand|Select|Wrap|Line Numbers
  1.  
  2. --Table name : all_objects
  3.  
  4. value : Decode (:$FLEX$.xx_period_type,'week', (TO_CHAR (sysdate+1- ((rownum)*7 ),'WW-YYYY') ),'month',to_char( add_months( sysdate,- rownum+1),'month-yyyy') )
  5. ID : Decode (:$FLEX$.xx_period_type,'week', (TO_CHAR (sysdate+1- ((rownum) *7 ),'WW-YYYY') ),'month',to_char( add_months (sysdate,-rownum+1),'month-yyyy') )
  6.  
  7. --and in where clause
  8.  
  9. where rownum < Decode (:$FLEX$.xx_period_type,'week',261,'month',61 )
  10. order by 1
  11.  
  12.  


This is working fine... :)

Thank u...

Thanks & Regards
That's Fantastic. So the problem was that ID value also needs to be specified with the same syntax that we use in table value field is it?
Mar 19 '08 #23

P: 14
That's Fantastic. So the problem was that ID value also needs to be specified with the same syntax that we use in table value field is it?
Yes and one more thing is , we can't use $FLEX$ in SELECT statement.
Mar 19 '08 #24

amitpatel66
Expert 100+
P: 2,367
Yes and one more thing is , we can't use $FLEX$ in SELECT statement.
Ok. So the problem is solved. Great!!

Shreya,

Please make sure you make use of [code] TAGS when ever you post any source code in this forum. CODE TAGS increases the readability and clarity of the source code for others to understand much better. When you click on POST REPLY or REPLY BUtton of the thread, it opens a new window where you can enter your reply. At the right hand side of this window you can check guidelines that will explain you about how to use [code] tags
Mar 19 '08 #25

Post your reply

Sign in to post your reply or Sign up for a free account.