i am passing dynamic where clause to a detail block from the main control block by pressing a button, the problem is different types of criterias are not comming together, like i may choose any value of machine or if its null it should bring all the machines combined with status which can be either opened,closed,inspected and i added them as elements of list and if the selected item has status with value All it shoud bring all the status for all the machines.but the problem is its not satisfying the criteria properly and when i choose all nothing is displayed in the detail block.
{code}
declare
cnt number;
var varchar2(32000);
VAAL VARCHAR2(32500);
begin
cnt := Get_List_Element_Count('IP_REP_INFO.T_LIST_IN');
if cnt >= 1
then
var := null;
for i in 1..cnt loop
var := var||','||''''||Get_List_Element_Value('IP_REP_INFO.T_LIST_IN',i)||'''';
end loop;
Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,
'WHERE (1=1 AND JOB_MACH IS NULL) OR (1=0 OR JOB_MACH in ('||substr(var, 2)||')) AND (1 = 1 and :ORDER_STATUS IN ('||'''ALL'''||') AND
STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') ) or ( 1=0 OR STATUS = :ORDER_STATUS)');
GO_BLOCK('OV_JOB_MAINT');
EXECUTE_QUERY;
GO_ITEM('JOB_DT');
end;
{\code}