[Home] [Help]
318: select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
319: workitem_obj_code
320: from ieu_uwqm_items
321: where owner_type = l_owner_type_grp
322: and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
323: and status_id in (0,1,2)
324: and reschedule_time <= sysdate
325: order by priority_level, due_date;
326:
384: select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
385: workitem_obj_code
386: from ieu_uwqm_items
387: where assignee_type = l_owner_type_grp
388: and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
389: and status_id in (0,1,2)
390: and reschedule_time <= sysdate
391: order by priority_level, due_date;
392:
868: union
869: select work_item_id
870: from ieu_uwqm_items
871: where owner_type = l_owner_type_grp
872: and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
873: and status_id = p_status_id
874: and (p_node_type = 1 or p_node_type = 3)
875: and reschedule_time <= sysdate
876: union
884: union
885: select work_item_id
886: from ieu_uwqm_items
887: where assignee_type = l_owner_type_grp
888: and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
889: and status_id = p_status_id
890: and (p_node_type = 1 or p_node_type = 5)
891: and reschedule_time <= sysdate;
892: begin
4328:
4329: l_delete_flag_yes varchar2(1);
4330:
4331: cursor c_grp_id(p_resource_id in number) is
4332: select group_id from jtf_rs_group_members
4333: where resource_id = p_resource_id
4334: and nvl(delete_flag, 'N') <> l_delete_flag_yes;
4335:
4336: l_not_valid_flag VARCHAR2(1);
4354: l_bindvar_fm_ctr := 0;
4355: l_bindvar_to_ctr := 0;
4356:
4357: /* performance issues with the query and try three different approach and using the one that is giving better performance
4358: 1. owner_id in (select group_id from jtf_rs_group_members
4359: where resource_id = :resource_id
4360: and nvl(delete_flag,'N') <> 'Y');
4361: 2. exists (select 1 from jtf_rs_group_members
4362: where resource_id = :resource_id
4357: /* performance issues with the query and try three different approach and using the one that is giving better performance
4358: 1. owner_id in (select group_id from jtf_rs_group_members
4359: where resource_id = :resource_id
4360: and nvl(delete_flag,'N') <> 'Y');
4361: 2. exists (select 1 from jtf_rs_group_members
4362: where resource_id = :resource_id
4363: and nvl(delete_flag,'N') <> 'Y');
4364: 3. owner_id in (group_id1, group_id2, group_id3); - Explicitly passing the string.
4365:
4519:
4520: -- Build the complete Grp Asg Where clause
4521: l_df_asg_where_clause := ' assignee_type = '||':assignee_type'||(l_bindvar_fm_ctr-2)||
4522: ' and assignee_id in
4523: (select group_id from jtf_rs_group_members
4524: where resource_id = :resource_id'||
4525: ' and nvl(delete_flag,'||':delete_flag'||(l_bindvar_fm_ctr-1)||') <> '||':delete_flag'||l_bindvar_fm_ctr||')';
4526:
4527: l_fm_group_assigned_flag := 'T';
4828:
4829: l_delete_flag_no varchar2(1);
4830:
4831: cursor c_grp_id(p_resource_id in number) is
4832: select group_id from jtf_rs_group_members
4833: where resource_id = p_resource_id
4834: and nvl(delete_flag, 'N') = l_delete_flag_no;
4835:
4836: BEGIN