Parameter filter is not working on end system

[pnavadhani]

Hi All,

 

I have a query below where i am trying to join 4 tables (oh,ol,lo,sk) .The query works perfectly in jasper with the given parameters. But when i deployed the same code in JDA WMS .some of the parameters are displaying wrong results .For example if i give the parameter value for WAVE as 2 ,it will display the result and for wave 5 the actual result will be empty but same result will be displayed forwave 2.

Ideally wave parameter is displaying wrong results. I am not understinading why the same query works fine in jasper but not in end system(jda wms)

SELECT OH.ORDER_ID,OH.WORK_GROUP,OH.CUSTOMER_ID,OH.USER_DEF_TYPE_1,to_char(OH.ORDER_DATE,'DD-MM-YYYY HH24:MI:SS'),

OL.SKU_ID,OL.USER_DEF_CHK_1,

LO.LOCATION_ID,LO.ZONE_1,

SK.DESCRIPTION,COUNT(SP.TEXT),

oh.from_site_id "Site Id",

to_char(sysdate,'DD-MM-YYYY') "PRINT_DATE"

FROM

ORDER_HEADER OH

INNER JOIN

ORDER_LINE OL ON OH.ORDER_ID=OL.ORDER_ID

INNER JOIN 

LOCATION LO ON OH.USER_DEF_TYPE_1=LO.SUBZONE_1

INNER JOIN

SKU SK ON OL.SKU_ID=SK.SKU_ID

LEFT JOIN

SPECIAL_INS SP ON OL.ORDER_ID=SP.REFERENCE_ID

WHERE OH.STATUS in ('Released','In Progress')  AND OL.USER_DEF_CHK_1='Y' and substr(OH.OWNER_ID,1,2) = substr(lo.zone_1,1,2)

AND( $P{GRID}  is null or  $P{GRID} = LO.ZONE_1)

AND ( $P{Location}  is null or  $P{Location} = LO.LOCATION_ID)

AND ( $P{Wave}  is null or  $P{Wave} = OH.WORK_GROUP)

AND ( $P{Store}  is null or  $P{Store} = OH.CUSTOMER_ID)

AND ( $P{Itemno}  is null or  $P{Itemno} = OL.SKU_ID)

and ( $P{from_date} is null or  to_date($P{from_date}, 'DD/MM/YYYY') <= to_date(to_char(OH.ORDER_DATE, 'DD/MM/YYYY'),'DD/MM/YYYY'))

and ( $P{to_date} is null or  to_date($P{to_date}, 'DD/MM/YYYY') >= to_date(to_char(OH.ORDER_DATE,'DD/MM/YYYY'),'DD/MM/YYYY'))

GROUP BY OH.ORDER_ID,OH.WORK_GROUP,OH.CUSTOMER_ID,OH.USER_DEF_TYPE_1,OH.ORDER_DATE,oh.from_site_id,

OL.SKU_ID,OL.USER_DEF_CHK_1,

LO.LOCATION_ID,LO.ZONE_1,

SK.DESCRIPTION

ORDER BY OL.SKU_ID