APPS.IEU_DIAG_AUDIT_TRACK_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 31

   Select a.workitem_pk_id,
          a.title,
          DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
          a.priority_id,
          a.due_date,
          a.reschedule_time,
          a.OWNER_ID,
		a.owner_type,
          a.ASSIGNEE_ID,
		a.assignee_type,
		a.ws_id
   from ieu_uwqm_items a
	 Where a.DISTRIBUTION_STATUS_ID = 0
   And nvl(a.owner_type, 'NULL') <> 'RS_GROUP'
   AND nvl(a.assignee_type, 'NULL') <> 'RS_INDIVIDUAL'
   and a.creation_date  BETWEEN p_from_date AND  p_to_date
   ORDER BY a.title;

Line: 64

    fnd_msg_pub.delete_msg();

Line: 86

              select group_name into owner_name
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 94

              select resource_name into owner_name
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id;

Line: 106

            select resource_name into assignee_name
            from JTF_RS_RESOURCE_EXTNS_vl
            where resource_id = cur_rec.assignee_id;

Line: 114

                  select group_name into assignee_name
            from jtf_rs_groups_tl
            where group_id = cur_rec.assignee_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 126

                select name into priority
                from ieu_uwqm_priorities_tl
                where  priority_id = cur_rec.priority_id
                and language = FND_GLOBAL.CURRENT_LANGUAGE;

Line: 137

        select ws_name into l_ws_name
	   from ieu_uwqm_work_sources_tl
	   where ws_id = cur_rec.ws_id
	   and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 230

   Select a.workitem_pk_id,
          a.title,
          DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
          a.priority_id,
          a.due_date,
          a.reschedule_time,
          a.OWNER_ID,
		a.owner_type,
          a.ASSIGNEE_ID,
		a.assignee_type,
		a.ws_id
	from ieu_uwqm_items a
	 Where a.DISTRIBUTION_STATUS_ID = 3
   And a.assignee_type = 'RS_INDIVIDUAL'
   And a.assignee_id IN ( select resource_id  from JTF_RS_RESOURCE_EXTNS where lower(user_name) = lower(p_user_name))
   and a.creation_date  BETWEEN p_from_date AND  p_to_date
   ORDER BY a.title;

Line: 262

    fnd_msg_pub.delete_msg();

Line: 275

      l_sql := ' select user_id from fnd_user where upper(user_name) like upper( :p_user_name)';

Line: 311

              select group_name into owner_name
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 319

              select resource_name into owner_name
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id;

Line: 331

            select resource_name into assignee_name
            from JTF_RS_RESOURCE_EXTNS_vl
            where resource_id = cur_rec.assignee_id;

Line: 339

                  select group_name into assignee_name
            from jtf_rs_groups_tl
            where group_id = cur_rec.assignee_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 352

                select name into priority
                from ieu_uwqm_priorities_tl
                where  priority_id = cur_rec.priority_id
                and language = FND_GLOBAL.CURRENT_LANGUAGE;

Line: 363

		select ws_name into l_ws_name
	     from ieu_uwqm_work_sources_tl
	     where ws_id = cur_rec.ws_id
	     and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 453

   Select a.workitem_pk_id, a.workitem_obj_code,
          DECODE(a.workitem_STATUS_ID_curr,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
          a.owner_id_prev,a.owner_id_curr,a.owner_type_prev,a.owner_type_curr,
          a.assignee_id_prev,a.assignee_id_curr,a.assignee_type_prev,a.assignee_type_curr,
          ws_code
   from ieu_uwqm_audit_log a
	 Where a.creation_date between p_from_date and p_to_date
   And (a.assignee_type_prev ='RS_INDIVIDUAL' )
   and (a.assignee_type_curr ='RS_INDIVIDUAL')
 	 and (a.assignee_id_prev <> a.assignee_id_curr)
   AND a.owner_id_curr in ( select group_id from jtf_rs_groups_vl where lower(group_name) = lower(p_group_name))
    ORDER BY a.creation_date;

Line: 482

    fnd_msg_pub.delete_msg();

Line: 504

              select title into title
              from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
              and workitem_obj_code=cur_rec.workitem_obj_code;

Line: 513

              select group_name into owner_name_prev
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id_prev and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 521

              select resource_name into owner_name_prev
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id_prev;

Line: 533

              select resource_name into assignee_name_prev
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.assignee_id_prev;

Line: 541

              select group_name into assignee_name_prev
              from jtf_rs_groups_tl
              where group_id = cur_rec.assignee_id_prev and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 552

              select group_name into owner_name_curr
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id_curr and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 560

              select resource_name into owner_name_curr
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id_curr;

Line: 572

              select resource_name into assignee_name_curr
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.assignee_id_curr;

Line: 580

              select group_name into assignee_name_curr
              from jtf_rs_groups_tl
              where group_id = cur_rec.assignee_id_curr and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 591

	     select ws_name into l_ws_name
		from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
	     where b.ws_id = tl.ws_id
	     and tl.language =  FND_GLOBAL.CURRENT_LANGUAGE
	     and b.ws_code=cur_rec.ws_code;

Line: 681

   Select a.workitem_pk_id, a.workitem_obj_code,
          DECODE(a.workitem_STATUS_ID_curr,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
          a.owner_id_prev,a.owner_id_curr,a.owner_type_prev,a.owner_type_curr,
          a.assignee_id_prev,a.assignee_id_curr,a.assignee_type_prev,a.assignee_type_curr,
		ws_code
   from ieu_uwqm_audit_log a
	 Where a.creation_date between p_from_date and p_to_date
   And (   (a.owner_type_prev <> 'RS_GROUP')
            and  (a.owner_type_curr = 'RS_GROUP')
            and  (a.assignee_type_curr is null)
       )
   OR
       (   (a.owner_type_prev = 'RS_GROUP')
            and (a.owner_type_curr = 'RS_GROUP')
            and  (a.assignee_type_curr is null)
            and  (a.owner_id_prev <> a.owner_id_curr)
       )
    OR
			 (   (a.assignee_type_prev is not null)
            and (a.assignee_type_curr is null)
 			      and (a.owner_type_curr = 'RS_GROUP')
       )
    ORDER BY a.creation_date;

Line: 721

    fnd_msg_pub.delete_msg();

Line: 743

              select title into title
              from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
              and workitem_obj_code=cur_rec.workitem_obj_code;

Line: 752

              select group_name into owner_name_prev
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id_prev and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 760

              select resource_name into owner_name_prev
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id_prev;

Line: 772

              select resource_name into assignee_name_prev
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.assignee_id_prev;

Line: 780

              select group_name into assignee_name_prev
              from jtf_rs_groups_tl
              where group_id = cur_rec.assignee_id_prev and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 791

              select group_name into owner_name_curr
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id_curr and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 799

              select resource_name into owner_name_curr
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id_curr;

Line: 811

              select resource_name into assignee_name_curr
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.assignee_id_curr;

Line: 819

              select group_name into assignee_name_curr
              from jtf_rs_groups_tl
              where group_id = cur_rec.assignee_id_curr and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 830

	     select ws_name into l_ws_name
		from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
	     where b.ws_id = tl.ws_id
	     and tl.language =  FND_GLOBAL.CURRENT_LANGUAGE
	    and b.ws_code=cur_rec.ws_code;

Line: 921

   Select a.workitem_pk_id,
          a.title,
          DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
          a.priority_id,
          a.due_date,
          a.reschedule_time,
          a.OWNER_ID,
		a.owner_type,
          a.ASSIGNEE_ID,
          a.assignee_type,
		a.ws_id
		from ieu_uwqm_items a
	 Where a.DISTRIBUTION_STATUS_ID = 2
   And a.owner_type = 'RS_GROUP'
   And a.owner_id IN (select group_id FROM jtf_rs_groups_vl WHERE lower(GROUP_name) = lower(p_group_name))
   and a.creation_date  BETWEEN p_from_date AND  p_to_date
   ORDER BY a.title;

Line: 952

    fnd_msg_pub.delete_msg();

Line: 973

              select group_name into owner_name
              from jtf_rs_groups_tl
              where group_id = cur_rec.owner_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 981

              select resource_name into owner_name
              from JTF_RS_RESOURCE_EXTNS_vl
              where resource_id = cur_rec.owner_id;

Line: 993

            select resource_name into assignee_name
            from JTF_RS_RESOURCE_EXTNS_vl
            where resource_id = cur_rec.assignee_id;

Line: 1001

                  select group_name into assignee_name
            from jtf_rs_groups_tl
            where group_id = cur_rec.assignee_id and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 1014

                select name into priority
                from ieu_uwqm_priorities_tl
                where  priority_id = cur_rec.priority_id
                and language = FND_GLOBAL.CURRENT_LANGUAGE;

Line: 1026

	    select ws_name into l_ws_name
	    from ieu_uwqm_work_sources_tl
	    where ws_id = cur_rec.ws_id
	    and language =  FND_GLOBAL.CURRENT_LANGUAGE;

Line: 1112

   select  distinct a.workitem_pk_id, a.MODULE ,
           DECODE(a.WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') WORKITEM_STATUS_ID_CURR,
           DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'On Hold', '1', 'Distributable',
                  '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR ,
           a.workitem_obj_code,a.OWNER_ID_CURR, rs1.group_name owner_name,
           a.ASSIGNEE_ID_CURR, rs2.resource_name assignee_name, a.ws_code, a.work_item_number, b.ws_id, tl.ws_name ws_name
	 FROM ieu_uwqm_audit_log a, ieu_uwqm_work_sources_b b, ieu_uwqm_work_sources_tl tl,
     jtf_rs_groups_vl rs1, JTF_RS_RESOURCE_EXTNS_vl rs2
    WHERE a.owner_type_curr = 'RS_GROUP'
     and a.assignee_type_curr = 'RS_INDIVIDUAL'
     AND a.owner_id_curr = rs1.group_id(+)
     AND a.assignee_id_curr = rs2.resource_id(+)
	and a.workitem_obj_code = b.object_code
	and b.ws_id = tl.ws_id
	and tl.language =  FND_GLOBAL.CURRENT_LANGUAGE
     AND not exists
        (select 1 from jtf_rs_group_members
        where group_id = a.owner_id_curr
        and resource_id = a.assignee_id_curr
        and nvl(delete_flag, 'N') = 'N')
      and a.creation_date BETWEEN p_from_date AND  p_to_date
     ORDER BY a.workitem_obj_code;

Line: 1148

    fnd_msg_pub.delete_msg();

Line: 1154

    SELECT count(*) INTO l_tmp FROM (   select  distinct a.workitem_pk_id, a.MODULE ,
           DECODE(a.WORKITEM_STATUS_ID_CURR,'0', 'Not Distributable', '1', 'Distributable',
                  '2', 'Distributing', '3', 'Distributed') WORKITEM_STATUS_ID_CURR,
           DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'Not Distributable', '1', 'Distributable',
                  '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR ,
           a.workitem_obj_code,a.OWNER_ID_CURR, rs1.group_name owner_name,
           a.ASSIGNEE_ID_CURR, rs2.resource_name assignee_name, b.ws_id, tl.ws_name ws_name,
		 a.work_item_number
     FROM ieu_uwqm_audit_log a, ieu_uwqm_work_sources_b b, ieu_uwqm_work_sources_tl tl,
     jtf_rs_groups_tl rs1, JTF_RS_RESOURCE_EXTNS_vl rs2
    WHERE a.owner_type_curr = 'RS_GROUP'
     and a.assignee_type_curr = 'RS_INDIVIDUAL'
     and a.workitem_obj_code = b.object_code
     and b.ws_id = tl.ws_id
     AND a.owner_id_curr = rs1.group_id(+)
     AND a.assignee_id_curr = rs2.resource_id(+)
	and rs1.language=FND_GLOBAL.CURRENT_LANGUAGE
     AND not exists
        (select 1 from jtf_rs_group_members
        where group_id = a.owner_id_curr
        and resource_id = a.assignee_id_curr
        and nvl(delete_flag, 'N') = 'N')
      and a.creation_date BETWEEN p_from_date AND  p_to_date
     ORDER BY a.workitem_obj_code);

Line: 1190

	/*   select ws_name into l_ws_name
	   from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
	   where b.ws_id = tl.ws_id
	   and tl.language =  FND_GLOBAL.CURRENT_LANGUAGE
	   and b.ws_code=cur_rec.ws_code;

Line: 1336

    select a.action_key,a.EVENT_KEY, a.MODULE ,
           DECODE(a.WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') WORKITEM_STATUS_ID_CURR,
           a.owner_id_curr, a.OWNER_TYPE_CURR,
           a.assignee_id_curr,a.ASSIGNEE_TYPE_CURR,
           DECODE(a.PARENT_WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close',
                  '4', 'Delete', '5', 'Sleep') PARENT_WORKITEM_STATUS_ID_CURR,
           DECODE(a.PARENT_DIST_STATUS_ID_CURR ,'0', 'On Hold', '1', 'Distributable',
                  '2', 'Distributing', '3', 'Distributed') PARENT_DIST_STATUS_ID_CURR,
           DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'On Hold', '1', 'Distributable',
                  '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR,
            priority_id_curr ,a.DUE_DATE_CURR ,a.RESCHEDULE_TIME_CURR,
            a.IEU_COMMENT_CODE1 m1,
            a.IEU_COMMENT_CODE2 m2,
            a.IEU_COMMENT_CODE3 m3,
            a.IEU_COMMENT_CODE4 m4,
            a.IEU_COMMENT_CODE5 m5,
            a.WORKITEM_COMMENT_CODE1 m6,
            a.WORKITEM_COMMENT_CODE2 m7,
            a.WORKITEM_COMMENT_CODE3 m8,
            a.WORKITEM_COMMENT_CODE4 m9,
            a.WORKITEM_COMMENT_CODE5 m10, a.LAST_UPDATE_DATE, a.workitem_pk_id,
		  a.return_status, a.error_code, a.ws_code, a.source_object_id_curr, a.source_object_type_code_curr
     FROM ieu_uwqm_audit_log a
	where (a.work_item_number = p_item_number
     AND a.workitem_obj_code = p_object_code)
     or (a.SOURCE_OBJECT_ID_CURR = p_item_number
     and a.SOURCE_OBJECT_TYPE_CODE_CURR = p_object_code)
     order by a.audit_log_id,a.creation_date;

Line: 1367

    fnd_msg_pub.delete_msg();

Line: 1410

              select group_name into owner_name
		    from jtf_rs_groups_tl
		    where group_id = cur_rec.owner_id_curr and language = l_language;

Line: 1418

		    select resource_name into owner_name
		    from JTF_RS_RESOURCE_EXTNS_vl
		    where resource_id = cur_rec.owner_id_curr;

Line: 1430

		    select resource_name into assignee_name
		    from JTF_RS_RESOURCE_EXTNS_vl
		    where resource_id = cur_rec.assignee_id_curr;

Line: 1438

              select group_name into assignee_name
		    from jtf_rs_groups_tl
		    where group_id = cur_rec.assignee_id_curr and language = l_language;

Line: 1449

		  select meaning into action
		  from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = cur_rec.action_key;

Line: 1457

		           select ws_type into ws_type
		           from ieu_uwqm_work_sources_b where ws_code = cur_rec.ws_code;

Line: 1470

		  select meaning into event
		  from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = cur_rec.event_key;

Line: 1479

		  select title into title
		  from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
		  and workitem_obj_code=p_object_code;

Line: 1488

		  select name into priority
		  from ieu_uwqm_priorities_tl
		  where  priority_id = cur_rec.priority_id_curr
		  and language = l_language;

Line: 1519

            select meaning into current_meaning
            from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = code;

Line: 1562

		     --insert into p_temp(msg) values(l_index || ' = ' || my_message); commit;

Line: 1565

		  l_del_msg := fnd_msg_pub.delete_msg;

Line: 1571

                                                               cur_rec.LAST_UPDATE_DATE,
                                                               cur_rec.MODULE,
                                                               cur_rec.WORKITEM_STATUS_ID_CURR ,
                                                               owner_name,
                                                               cur_rec.OWNER_ID_CURR,
                                                               ASSIGNEE_name,
                                                               cur_rec.ASSIGNEE_ID_CURR,
                                                               cur_rec.PARENT_WORKITEM_STATUS_ID_CURR,
                                                               cur_rec.PARENT_DIST_STATUS_ID_CURR,
                                                               cur_rec.WORKITEM_DIST_STATUS_ID_CURR,
                                                               priority,
                                                               cur_rec.DUE_DATE_CURR,
                                                               cur_rec.RESCHEDULE_TIME_CURR,
												   action,
												   title,
												   cur_rec.workitem_pk_id,
												   meaning1,
												   meaning2,
												   meaning3,
												   meaning4,
												   meaning5,
												   meaning6,
												   meaning7,
												   meaning8,
												   meaning9,
												   meaning10,
												   return_status
												   );