APPS.IEU_TASKS_WR_MIG_PVT SQL Statements

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

Line: 11

l_delete_flag          VARCHAR2(1);

Line: 23

  l_date_selected   varchar2(1);

Line: 75

    SELECT resource_id, task_id, resource_type_code
    from (SELECT  /*+ parallel(TASKS) parallel(ASG) pq_distribute(ASG hash,hash) */
                  tasks.task_id, TASKS.owner_id,
                  tasks.owner_type_code, asg.resource_id, asg.resource_type_code, asg.task_assignment_id,
                  max(asg.last_update_date) over (partition by asg.task_id) max_update_date, asg.last_update_date
                 FROM JTF_TASKS_B TASKS , JTF_TASK_ASSIGNMENTS ASG
                 WHERE TASKS.TASK_ID = ASG.TASK_ID
                 AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
                 AND TASKS.OPEN_FLAG = 'Y'
                 AND TASKS.entity = 'TASK'
                 and tasks.owner_type_code = 'RS_GROUP'
                 and asg.resource_type_code not in ('RS_GROUP', 'RS_TEAM')
                 and asg.assignee_role = 'ASSIGNEE'
                 and exists
                 (SELECT /*+ index(a,JTF_RS_GROUP_MEMBERS_N1) */ null
                    FROM JTF_RS_GROUP_MEMBERS a
                   WHERE a.group_id=tasks.owner_id
                   and a.RESOURCE_ID = asg.resource_id
                   AND NVL(DELETE_FLAG,'N') <> 'Y' )
                 and exists
                 (select  1
                  from jtf_task_statuses_b sts
                  where sts.task_status_id = asg.assignment_status_id
                  and (nvl(sts.closed_flag, 'N') = 'N'
                  and nvl(sts.completed_flag, 'N') = 'N'
                  and nvl(sts.cancelled_flag, 'N') = 'N'
                  and nvl(sts.rejected_flag, 'N') = 'N'))) a
      where a.last_update_date = a.max_update_date;

Line: 105

 SELECT booking_end_date, task_id
 FROM   JTF_TASK_ALL_ASSIGNMENTS
 WHERE  assignee_role = 'OWNER';

Line: 110

 SELECT TASK_ID,
        DECODE(DELETED_FLAG, 'Y', 4, 3) "STATUS_ID"
 FROM JTF_TASKS_B
 WHERE ((OPEN_FLAG = 'N' AND DELETED_FLAG = 'N') OR (DELETED_FLAG = 'Y'))
 AND ENTITY = 'TASK';

Line: 116

/**      select resource_id, task_id, resource_type_code
      from
	(
	SELECT  tasks.task_id, TASKS.owner_id, tasks.owner_type_code, asg.resource_id, asg.resource_type_code, asg.task_assignment_id,
			  max(asg.last_update_date) over (partition by asg.task_id) max_update_date, asg.last_update_date
			  FROM JTF_TASK_ASSIGNMENTS ASG, JTF_TASKS_B TASKS
			  WHERE TASKS.TASK_ID = ASG.TASK_ID
			  AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
			  AND TASKS.OPEN_FLAG = 'Y'
			  AND TASKS.entity = 'TASK'
			  and tasks.owner_type_code = 'RS_GROUP'
			  and asg.resource_type_code not in ('RS_GROUP', 'RS_TEAM')
			  and asg.assignee_role = 'ASSIGNEE'
			  and exists
			  (SELECT null
			     FROM JTF_RS_GROUP_MEMBERS
			    WHERE group_id=tasks.owner_id
			    and RESOURCE_ID = asg.resource_id
			    AND NVL(DELETE_FLAG,'N') <> 'Y' )
			  and exists
			  (select 1
			   from jtf_task_statuses_b sts
			   where sts.task_status_id = asg.assignment_status_id
			   and (nvl(sts.closed_flag, 'N') = 'N'
			   and nvl(sts.completed_flag, 'N') = 'N'
			   and nvl(sts.cancelled_flag, 'N') = 'N'
			   and nvl(sts.rejected_flag, 'N') = 'N') )
	--and tasks.task_id = 17234
	) a
      where a.last_update_date = a.max_update_date;

Line: 219

  l_deleted_flag       VARCHAR2(1);

Line: 225

  select ''''||object_code||'''' object_code
  from ieu_uwqm_work_sources_b
  where ws_id in ( select assct_props.parent_ws_id
                   from   ieu_uwqm_work_sources_b ws, ieu_uwqm_ws_assct_props assct_props
                   where  ws.ws_id = assct_props.ws_id
                   and    assct_props.child_ws_id = p_child_ws_id
                   and    nvl(ws.not_valid_flag,'N') = 'N');

Line: 258

          v_task_details_1 :=   'select  /*+ ordered parallel(tb) parallel(tt) use_nl(tp,ip,sts_b)*/
        tb.task_id
      , tb.task_number work_item_number
      , tb.customer_id
      , tb.owner_id
      , decode(tb.owner_type_code, '||''''||'RS_GROUP'||''''||','||''''||'RS_GROUP'||''''||','||''''||'RS_TEAM'||''''||','||
        ''''||'RS_TEAM'||''''||','||''''|| 'RS_INDIVIDUAL'||''''||')'||' owner_type_code
      , tb.owner_type_code owner_type_actual
      , tb.source_object_id
      , tb.source_object_type_code
--      , decode(tb.date_selected,'||''''||'P'||''''||', tb.planned_end_date, '||''''||'A'||''''||', tb.actual_end_date, '||''''||'S'||''''||', tb.scheduled_end_date, null, tb.scheduled_end_date) due_date
      , tb.planned_start_date
      , tb.planned_end_date
      , tb.actual_start_date
      , tb.actual_end_date
      , tb.scheduled_start_date
      , tb.scheduled_end_date
      , tb.task_type_id
      , substr(tt.task_name,1,1990) TITLE
      , tp.importance_level
      , ip.priority_code
      , ip.priority_id
      , ip.priority_level
      , decode(NVL(tb.owner_type_code,'||''''||'NULL'||''''||'), '||''''||'RS_GROUP'||''''||', 1, 0) distribution_status_id
      , decode(nvl(sts_b.on_hold_flag, '||''''||'N'||''''||'),'||''''||'Y'||''''||', 5, 0) uwq_status_id
      , 1 ins_flag
   from  jtf_tasks_b tb
     , jtf_tasks_tl tt
     , jtf_task_priorities_b tp
     , ieu_uwqm_priorities_b ip
     , jtf_task_statuses_b sts_b
   where tb.entity = '||''''||'TASK'||''''||'
	and   nvl(tb.deleted_flag, '||''''||'N'||''''||') = '||''''||'N'||''''||'
	and   tb.task_id = tt.task_id
	and   tt.language =  userenv('||''''||'LANG'||''''||')
	and   tp.task_priority_id = nvl(tb.task_priority_id, 4)
	and   least(tp.importance_level, 4) = ip.priority_level
	and   open_flag = '||''''||'Y'||''''||'
	and   tb.task_status_id = sts_b.task_status_id';

Line: 302

	      select ws_id
	      into l_ws_id
	      from ieu_uwqm_work_sources_b
	      where object_code = l_object_code
	      and nvl(not_valid_flag, 'N') = l_not_valid_flag;

Line: 321

		 select count(*)
		 into   l_assct_ws_cnt
		 from   ieu_uwqm_work_sources_b ws, ieu_uwqm_ws_assct_props assct_props
		 where  ws.ws_id = assct_props.ws_id
		 and    assct_props.child_ws_id = l_ws_id
		 and    nvl(ws.not_valid_flag,'N') = 'N';

Line: 406

		 insert into ieu_uwqm_items
			( WORK_ITEM_ID,
			 OBJECT_VERSION_NUMBER,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATED_BY,
			 LAST_UPDATE_DATE,
			 LAST_UPDATE_LOGIN,
			 SECURITY_GROUP_ID,
			 WORKITEM_OBJ_CODE,
			 WORKITEM_PK_ID,
			 STATUS_ID,
			 PRIORITY_ID,
			 PRIORITY_LEVEL,
			-- DUE_DATE,
			 TITLE,
			 PARTY_ID,
			 OWNER_TYPE,
			 OWNER_ID,
			 OWNER_TYPE_ACTUAL,
			 SOURCE_OBJECT_ID,
			 SOURCE_OBJECT_TYPE_CODE,
			 APPLICATION_ID,
			 IEU_ENUM_TYPE_UUID,
			 STATUS_UPDATE_USER_ID,
			 WORK_ITEM_NUMBER,
			 RESCHEDULE_TIME,
			 WS_ID,
			 DISTRIBUTION_STATUS_ID )
		values  (
			IEU_UWQM_ITEMS_S1.NEXTVAL,
			0,
			l_user_id,
			SYSDATE,
			l_user_id,
			SYSDATE,
			l_login_id,
			0,
			'TASK',
			l_task_det_rec.l_task_id_list(i),
			l_task_det_rec.l_task_status_id_list(i),
			l_task_det_rec.l_pty_id_list(i),
			l_task_det_rec.l_pty_level_list(i),
			--l_task_det_rec.l_due_date_list(i),
			l_task_det_rec.l_task_name_list(i),
			l_task_det_rec.l_customer_id_list(i),
			l_task_det_rec.l_owner_type_code_list(i),
			l_task_det_rec.l_owner_id_list(i),
			l_task_det_rec.l_owner_type_actual_list(i),
			l_task_det_rec.l_source_object_id_list(i),
			l_task_det_rec.l_source_object_type_code_list(i),
			690,
			'TASKS',
			l_user_id,
			l_task_det_rec.l_task_number_list(i),
			sysdate,
			l_ws_id,
			l_task_det_rec.l_dist_sts_id(i));

Line: 468

		   --fnd_file.put_line(FND_FILE.LOG,'insert failed..');

Line: 529

	     -- fnd_file.put_line(FND_FILE.LOG,'begin update');

Line: 532

		    UPDATE IEU_UWQM_ITEMS
		    set
			OBJECT_VERSION_NUMBER  = OBJECT_VERSION_NUMBER + 1,
--			CREATED_BY             = l_user_id,
--			CREATION_DATE          = SYSDATE,
			LAST_UPDATED_BY        = l_user_id,
			LAST_UPDATE_DATE       = SYSDATE,
			LAST_UPDATE_LOGIN      = l_login_id,
			STATUS_ID              = l_task_det_rec.l_task_status_id_list(i),
			PRIORITY_ID            = l_task_det_rec.l_pty_id_list(i),
			PRIORITY_LEVEL         = l_task_det_rec.l_pty_level_list(i),
			--DUE_DATE               = l_task_det_rec.l_due_date_list(i),
			TITLE                  = l_task_det_rec.l_task_name_list(i),
			PARTY_ID               = l_task_det_rec.l_customer_id_list(i),
			OWNER_TYPE             = l_task_det_rec.l_owner_type_code_list(i),
			OWNER_ID               = l_task_det_rec.l_owner_id_list(i),
			SOURCE_OBJECT_ID       = l_task_det_rec.l_source_object_id_list(i),
			SOURCE_OBJECT_TYPE_CODE = l_task_det_rec.l_source_object_type_code_list(i),
			OWNER_TYPE_ACTUAL      = l_task_det_rec.l_owner_type_actual_list(i),
			APPLICATION_ID         = 690,
			IEU_ENUM_TYPE_UUID     = 'TASKS',
			STATUS_UPDATE_USER_ID  = l_user_id,
			WORK_ITEM_NUMBER       = l_task_det_rec.l_task_number_list(i),
			RESCHEDULE_TIME        = sysdate,
			WS_ID                  = l_ws_id,
			DISTRIBUTION_STATUS_ID = l_task_det_rec.l_dist_sts_id(i)
		     where workitem_obj_code = 'TASK'
			 and workitem_pk_id = l_task_det_rec.l_task_id_list(i)
			 and l_task_det_rec.l_ins_flag(i) = 0;

Line: 588

                       FND_MESSAGE.SET_NAME('IEU', 'IEU_UPDATE_UWQM_ITEM_FAILED');

Line: 622

		l_task_det_rec.l_task_id_list.DELETE;

Line: 623

		l_task_det_rec.l_task_number_list.DELETE;

Line: 624

		l_task_det_rec.l_customer_id_list.DELETE;

Line: 625

		l_task_det_rec.l_owner_id_list.DELETE;

Line: 626

		l_task_det_rec.l_owner_type_code_list.DELETE;

Line: 627

		l_task_det_rec.l_owner_type_actual_list.DELETE;

Line: 628

		l_task_det_rec.l_source_object_id_list.DELETE;

Line: 629

		l_task_det_rec.l_source_object_type_code_list.DELETE;

Line: 630

		--l_task_det_rec.l_due_date_list.DELETE;

Line: 631

		l_task_det_rec.l_planned_start_date_list.DELETE;

Line: 632

		l_task_det_rec.l_planned_end_date_list.DELETE;

Line: 633

		l_task_det_rec.l_actual_start_date_list.DELETE;

Line: 634

		l_task_det_rec.l_actual_end_date_list.DELETE;

Line: 635

		l_task_det_rec.l_scheduled_start_date_list.DELETE;

Line: 636

		l_task_det_rec.l_scheduled_end_date_list.DELETE;

Line: 637

		l_task_det_rec.l_task_type_id_list.DELETE;

Line: 638

		l_task_det_rec.l_task_name_list.DELETE;

Line: 639

		l_task_det_rec.l_importance_level_list.DELETE;

Line: 640

		l_task_det_rec.l_priority_code_list.DELETE;

Line: 641

		l_task_det_rec.l_pty_id_list.DELETE;

Line: 642

		l_task_det_rec.l_pty_level_list.DELETE;

Line: 643

		l_task_det_rec.l_dist_sts_id.DELETE;

Line: 644

		l_task_det_rec.l_task_status_id_list.DELETE;

Line: 645

		l_task_det_rec.l_ins_flag.DELETE;

Line: 652

	    DBMS_STATS.DELETE_TABLE_STATS (
		   ownname         => 'IEU',
		   tabname         => 'IEU_UWQM_ITEMS');

Line: 678

	--	fnd_file.put_line(FND_FILE.LOG,'Begin update');

Line: 680

			update IEU_UWQM_ITEMS
			set	due_date = l_task_duedate_rec.l_due_date_list(i)
			where   workitem_pk_id = l_task_duedate_rec.l_task_id_list(i)
			and	workitem_obj_code = 'TASK'
			and     ws_id = l_ws_id;

Line: 690

			 'assignee update for workitem_pk_id ' || l_task_asg_rec.l_asg_task_id_list(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX) ||
		         'Oracle error is ' ||
			 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); */

Line: 713

                       FND_MESSAGE.SET_NAME('IEU', 'IEU_UPDATE_UWQM_ITEM_FAILED');

Line: 743

             l_task_duedate_rec.l_due_date_list.DELETE;

Line: 744

             l_task_duedate_rec.l_task_id_list.DELETE;

Line: 768

	--	fnd_file.put_line(FND_FILE.LOG,'Begin update');

Line: 770

			update IEU_UWQM_ITEMS
			set	status_id = l_task_status_rec.l_status_id_list(i),
         			LAST_UPDATED_BY        = l_user_id,
	        		LAST_UPDATE_DATE       = SYSDATE,
		        	LAST_UPDATE_LOGIN      = l_login_id
			where   workitem_obj_code = 'TASK'
                        and     workitem_pk_id = l_task_status_rec.l_task_id_list(i)
			and     ws_id = l_ws_id;

Line: 802

                       FND_MESSAGE.SET_NAME('IEU', 'IEU_UPDATE_UWQM_ITEM_FAILED');

Line: 831

             l_task_status_rec.l_task_id_list.DELETE;

Line: 832

             l_task_status_rec.l_status_id_list.DELETE;

Line: 857

	--	fnd_file.put_line(FND_FILE.LOG,'Begin update');

Line: 859

			update IEU_UWQM_ITEMS
			set	assignee_id = l_task_asg_rec.l_asg_id_list(i),
				assignee_type = 'RS_INDIVIDUAL',
				assignee_type_actual = l_task_asg_rec.l_asg_type_act_list(i),
				DISTRIBUTION_STATUS_ID = 3
			where   workitem_pk_id = l_task_asg_rec.l_asg_task_id_list(i)
			and	workitem_obj_code = 'TASK'
			and	ws_id = l_ws_id;

Line: 872

			 'assignee update for workitem_pk_id ' || l_task_asg_rec.l_asg_task_id_list(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX) ||
		         'Oracle error is ' ||
			 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); */

Line: 896

                       FND_MESSAGE.SET_NAME('IEU', 'IEU_UPDATE_UWQM_ITEM_FAILED');

Line: 926

	     l_task_asg_rec.l_asg_id_list.DELETE;

Line: 927

	     l_task_asg_rec.l_asg_task_id_list.DELETE;

Line: 928

	     l_task_asg_rec.l_asg_type_act_list.DELETE;

Line: 998

	    DBMS_STATS.DELETE_TABLE_STATS (
		   ownname         => 'IEU',
		   tabname         => 'IEU_UWQM_ITEMS');