Dynamic Report parameter

[shreya alle]

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_CHA R(SYSDATE+1-((rownum)*7) ,'ww-YYYY')),TO_CHAR (ADD_MONTHS(SYS DATE,-rownum+1),'Mont h-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.

[amitpatel66]

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

[shreya alle]

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!!!

[amitpatel66]

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:
[code=oracle]

SELECT DECODE(:$FLEX$. first_value_set ,'week',(TO_CHA R(SYS DATE+1-((x.rn)*7) ,'ww-YYYY')),TO_CHAR (ADD_MONTHS(SYS DATE,-x.rn+1),'Month-YYYY') week_month FROM (SELECT rownum rn FROM all_objects) x
[/code]

In Where clause,you add this:

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

[shreya alle]

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:
[code=oracle]

SELECT DECODE(:$FLEX$. first_value_set ,'week',(TO_CHA R(SYS DATE+1-((x.rn)*7) ,'ww-YYYY')),TO_CHAR (ADD_MONTHS(SYS DATE,-x.rn+1),'Month-YYYY') week_month FROM (SELECT rownum rn FROM all_objects) x
[/code]

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. :(

[amitpatel66]

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?

[shreya alle]

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 :
[code=oracle]

SELECT DECODE (:p1,'week', TO_CHAR (case.creation_ date, 'IW-YYYY' ),
'month',TO_CHAR (case.creation_ date, 'MON-YYYY')) "Week_inflo w",
case.type "type",
case.dept "dept",
COUNT (case.instance_ id) "inflow",
SUM (DECODE (case.ola_got , '', 0, 1)) "met_OLA",
SUM (DECODE (case.dismissed , '', 0, 1)) "dismissed" ,
( SUM (DECODE (case.ola_got , '', 0, 1))
- SUM (DECODE (case.dismissed , '', 0, 1))
) "actual_inflow" ,
test1.percentag e_settled
(DECODE (:p1,'week', TO_CHAR (case.creation_ date, 'IW-YYYY'),
'month',TO_CHAR (case.creation_ date, 'MON-YYYY')),
case.type,
case.dept,
( SUM (DECODE (case.ola_got , '', 0, 1))
- SUM (DECODE (case.dismissed , '', 0, 1))
)
) "Percentage_set tled"

FROM case_v case,
casecomp_v casecomponent,
executive_v executive
WHERE
case.instance_i d = casecomponent.c ase_instance_id
AND casecomponent.c asecomp_instanc e_id = executive.casec omponent_id
AND (DECODE(:p1,'we ek' ,TO_CHAR (case.creation_ date, 'IW-YYYY'),
'month',TO_CHAR (case.creation_ date, 'MM-YYYY')))
BETWEEN
DECODE(:p1,'wee k',:p2,'month', TO_CHAR(to_date (:p2,'MM-YYYY'),'MM-YYYY'))
AND DECODE(:p1,'wee k',:p3,'month', TO_CHAR(to_date (:p3,'MM-YYYY'),'MM-YYYY'))
AND ((case.type IS NULL)
OR
(case.type IS NOT NULL AND case.type = :p4)
)
GROUP BY TO_CHAR (case.creation_ date, 'IW-YYYY'),
TO_CHAR (case.creation_ date, 'MON-YYYY'),
case.type,
case.dept
ORDER BY 1 DESC



--and the function test1 code is :


CREATE OR REPLACE PACKAGE BODY APPS.test1
IS

FUNCTION percentage_sett led (
p_week_inflow VARCHAR2,
p_type case_v.type%TYP E,
p_delivery_dept case_v.dept%TYP E,
p_actual_inflow NUMBER
)
RETURN NUMBER
IS
v_percentage_in flow NUMBER (10);
v_count NUMBER (10);
BEGIN
SELECT COUNT (*)
INTO v_count
FROM case_v ,
casecomp_v casecomponent,
executive_v executive
WHERE case_v.instance _id = casecomponent.c ase_instance_id
AND casecomponent.c asecomp_instanc e_id = executive.casec omponent_id
AND (TO_CHAR (case_v.creatio n_date, 'iw-yyyy') = p_week_inflow or
TO_CHAR (case_v.creatio n_date, 'mon-yyyy')= p_week_inflow)
AND case_v.type = p_type
AND case_v.dept = p_delivery_dept
AND executive.statu s = 'Gesloten';

IF p_actual_inflow = 0
THEN
v_percentage_in flow := null;
ELSE
v_percentage_in flow := ((v_count / p_actual_inflow ) * 100);
END IF;

RETURN round(v_percent age_settled,0);
END percentage_sett led;
END test1;

[/code]

[amitpatel66]

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.

[shreya alle]

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.

[shreya alle]

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.

[amitpatel66]

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?

[shreya alle]

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,'wee k',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

[amitpatel66]

Hi Amit.

I wrote this query in the Table name field

(SELECT DECODE(:p1,'wee k',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.

[shreya alle]

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,N ULL) week_month FROM all_objects)


Throwing an error "invalid arguments are passed" in the value set itself.

[amitpatel66]

(SELECT DECODE(:$FLEX$. xx_period_type, 'week',rownum,N ULL) 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:
[code=oracle]

select <calculation for week> AS data,'Week' AS week_month from all_objects
UNION ALL
select <calculation of month>,'Month' from all_objects
[/code]

And in WHERE clause add where condition:

WHERE data<= .....
AND week_month = :$FLEX$.firstva lueset