APPS.WIP_SCHEDULING SQL Statements

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

Line: 16

		INSERT INTO WIP_SCHEDULING_INTERFACE
		(
		interface_id,
		group_id,
		wip_entity_id,
		organization_id,
		operation_seq_num,
		resource_seq_num,
		scheduling_level,
		operation_start_date,
		operation_completion_date,
		resource_start_date,
		resource_completion_date,
		process_phase,
		process_status,
		last_update_date,
		last_updated_by,
		last_update_login,
		creation_date,
		created_by,
		request_id,
		program_application_id,
		program_id,
		program_update_date
		)
		(SELECT WIP_INTERFACE_S.NEXTVAL,
			P_GROUP_ID,
			P_WIP_ENTITY_ID,
			P_ORGANIZATION_ID,
			OPERATION_SEQ_NUM,
			NULL,
			P_SCHEDULING_LEVEL,
			FIRST_UNIT_START_DATE,
			LAST_UNIT_COMPLETION_DATE,
			NULL,
			NULL,
			1,
			1,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.LOGIN_ID,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_REQUEST_ID,
			FND_GLOBAL.PROG_APPL_ID,
			FND_GLOBAL.CONC_PROGRAM_ID,
			SYSDATE
		FROM WIP_OPERATIONS
		WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
		AND   ORGANIZATION_ID = P_ORGANIZATION_ID);

Line: 68

		INSERT INTO WIP_SCHEDULING_INTERFACE
		(
		interface_id,
		group_id,
		wip_entity_id,
		organization_id,
		operation_seq_num,
		resource_seq_num,
		scheduling_level,
		operation_start_date,
		operation_completion_date,
		resource_start_date,
		resource_completion_date,
		usage_rate_or_amount,
		process_phase,
		process_status,
		last_update_date,
		last_updated_by,
		last_update_login,
		creation_date,
		created_by,
		request_id,
		program_application_id,
		program_id,
		program_update_date
		)
		(SELECT WIP_INTERFACE_S.NEXTVAL,
			P_GROUP_ID,
			P_WIP_ENTITY_ID,
			P_ORGANIZATION_ID,
			OPERATION_SEQ_NUM,
			RESOURCE_SEQ_NUM,
			P_SCHEDULING_LEVEL,
			NULL,
			NULL,
			START_DATE,
			COMPLETION_DATE,
			usage_rate_or_amount,
			1,
			1,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.LOGIN_ID,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_REQUEST_ID,
			FND_GLOBAL.PROG_APPL_ID,
			FND_GLOBAL.CONC_PROGRAM_ID,
			SYSDATE
		FROM WIP_OPERATION_RESOURCES
		WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID
		AND   ORGANIZATION_ID = P_ORGANIZATION_ID);

Line: 125

		INSERT INTO WIP_SCHEDULING_INTERFACE
		(
		interface_id,
		group_id,
		wip_entity_id,
		organization_id,
		operation_seq_num,
		resource_seq_num,
		scheduling_level,
		operation_start_date,
		operation_completion_date,
		resource_start_date,
		resource_completion_date,
		process_phase,
		process_status,
		last_update_date,
		last_updated_by,
		last_update_login,
		creation_date,
		created_by,
		request_id,
		program_application_id,
		program_id,
		program_update_date
		)
		(SELECT WIP_INTERFACE_S.NEXTVAL,
			P_GROUP_ID,
			WIP_ENTITY_ID,
			ORGANIZATION_ID,
			OPERATION_SEQ_NUM,
			NULL,
			P_SCHEDULING_LEVEL,
			FIRST_UNIT_START_DATE,
			LAST_UNIT_COMPLETION_DATE,
			NULL,
			NULL,
			1,
			1,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.LOGIN_ID,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_REQUEST_ID,
			FND_GLOBAL.PROG_APPL_ID,
			FND_GLOBAL.CONC_PROGRAM_ID,
			SYSDATE
		FROM WIP_OPERATIONS
		WHERE (WIP_ENTITY_ID, ORGANIZATION_ID) IN
		(SELECT WIP_ENTITY_ID,
			ORGANIZATION_ID
		 FROM   WIP_JOB_SCHEDULE_INTERFACE WJSI
		 WHERE  WJSI.GROUP_ID = P_JOB_INTERFACE_GROUP_ID
		 AND    WJSI.PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
		 AND    WJSI.PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE));

Line: 182

		INSERT INTO WIP_SCHEDULING_INTERFACE
		(
		interface_id,
		group_id,
		wip_entity_id,
		organization_id,
		operation_seq_num,
		resource_seq_num,
		scheduling_level,
		operation_start_date,
		operation_completion_date,
		resource_start_date,
		resource_completion_date,
		usage_rate_or_amount,
		process_phase,
		process_status,
		last_update_date,
		last_updated_by,
		last_update_login,
		creation_date,
		created_by,
		request_id,
		program_application_id,
		program_id,
		program_update_date
		)
		(SELECT WIP_INTERFACE_S.NEXTVAL,
			P_GROUP_ID,
			WIP_ENTITY_ID,
			ORGANIZATION_ID,
			OPERATION_SEQ_NUM,
			RESOURCE_SEQ_NUM,
			P_SCHEDULING_LEVEL,
			NULL,
			NULL,
			START_DATE,
			COMPLETION_DATE,
			USAGE_RATE_OR_AMOUNT,
			1,
			1,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.LOGIN_ID,
			SYSDATE,
			FND_GLOBAL.USER_ID,
			FND_GLOBAL.CONC_REQUEST_ID,
			FND_GLOBAL.PROG_APPL_ID,
			FND_GLOBAL.CONC_PROGRAM_ID,
			SYSDATE
		FROM WIP_OPERATION_RESOURCES
		WHERE (WIP_ENTITY_ID, ORGANIZATION_ID) IN
		(SELECT WIP_ENTITY_ID,
			ORGANIZATION_ID
		 FROM   WIP_JOB_SCHEDULE_INTERFACE WJSI
		 WHERE  WJSI.GROUP_ID = P_JOB_INTERFACE_GROUP_ID
		 AND    WJSI.PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
		 AND    WJSI.PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE));

Line: 250

	SELECT DISTINCT WIP_ENTITY_ID, ORGANIZATION_ID, SCHEDULING_LEVEL
	  FROM WIP_SCHEDULING_INTERFACE
	 WHERE GROUP_ID = P_GROUP_ID
           AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
           AND PROCESS_STATUS = WIP_CONSTANTS.RUNNING;

Line: 257

	SELECT OPERATION_SEQ_NUM
	FROM   WIP_OPERATIONS
	WHERE  WIP_ENTITY_ID = we_id
	AND    ORGANIZATION_ID = org_id;

Line: 264

	UPDATE WIP_SCHEDULING_INTERFACE
	SET PROCESS_STATUS = WIP_CONSTANTS.RUNNING
	WHERE GROUP_ID = P_GROUP_ID
	AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
	AND PROCESS_STATUS = WIP_CONSTANTS.PENDING;

Line: 283

			UPDATE WIP_OPERATIONS WO
			SET (WO.FIRST_UNIT_START_DATE,
			     WO.FIRST_UNIT_COMPLETION_DATE,
			     WO.LAST_UNIT_START_DATE,
			     WO.LAST_UNIT_COMPLETION_DATE,
			     WO.LAST_UPDATED_BY) =
			(SELECT OPERATION_START_DATE,
				OPERATION_COMPLETION_DATE,
				OPERATION_START_DATE,
				OPERATION_COMPLETION_DATE,
				LAST_UPDATED_BY
			   FROM WIP_SCHEDULING_INTERFACE WSI
			  WHERE WSI.WIP_ENTITY_ID = C.WIP_ENTITY_ID
			    AND WSI.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
			    AND WSI.GROUP_ID = P_GROUP_ID),
			     LAST_UPDATE_DATE = SYSDATE
			WHERE WO.WIP_ENTITY_ID = C.WIP_ENTITY_ID
			AND   WO.ORGANIZATION_ID = C.ORGANIZATION_ID;

Line: 302

			UPDATE WIP_OPERATION_RESOURCES WOR
			SET    (START_DATE,
				COMPLETION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY) =
			(SELECT FIRST_UNIT_START_DATE,
				LAST_UNIT_COMPLETION_DATE,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY
			   FROM WIP_OPERATIONS WO
			  WHERE WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
			    AND WO.ORGANIZATION_ID = WOR.ORGANIZATION_ID
			    AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM)
			WHERE   WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
                        AND     WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;

Line: 320

			UPDATE WIP_OPERATION_RESOURCES WOR
			SET (WOR.START_DATE,
			     WOR.COMPLETION_DATE,
			     WOR.USAGE_RATE_OR_AMOUNT,
			     WOR.LAST_UPDATED_BY) =
			(SELECT RESOURCE_START_DATE,
				RESOURCE_COMPLETION_DATE,
				USAGE_RATE_OR_AMOUNT,
				LAST_UPDATED_BY
			   FROM WIP_SCHEDULING_INTERFACE WSI
			  WHERE WSI.WIP_ENTITY_ID = C.WIP_ENTITY_ID
			    AND WSI.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
			    AND WSI.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM
			    AND WSI.GROUP_ID = P_GROUP_ID),
			     LAST_UPDATE_DATE = SYSDATE
			WHERE WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
			AND   WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;

Line: 340

				SELECT MIN(START_DATE), MAX(COMPLETION_DATE)
				INTO   x_min_date, x_max_date
				FROM   WIP_OPERATION_RESOURCES WOR
				WHERE  WOR.WIP_ENTITY_ID = C.WIP_ENTITY_ID
				AND    WOR.OPERATION_SEQ_NUM = D.OPERATION_SEQ_NUM
				AND    WOR.ORGANIZATION_ID = C.ORGANIZATION_ID;

Line: 347

				UPDATE 	WIP_OPERATIONS WO
				SET    	FIRST_UNIT_START_DATE = x_min_date,
					 LAST_UNIT_START_DATE = x_min_date,
					 FIRST_UNIT_COMPLETION_DATE = x_max_date,
					 LAST_UNIT_COMPLETION_DATE = x_max_date,
					 LAST_UPDATE_DATE = SYSDATE,
					 LAST_UPDATED_BY = 1
				WHERE   WO.WIP_ENTITY_ID = C.WIP_ENTITY_ID
				AND     WO.ORGANIZATION_ID = C.ORGANIZATION_ID
				AND     WO.OPERATION_SEQ_NUM = D.OPERATION_SEQ_NUM;

Line: 362

		UPDATE_REQ_DATES(C.WIP_ENTITY_ID,
				 C.ORGANIZATION_ID);

Line: 365

		UPDATE_JOB_DATES(C.WIP_ENTITY_ID,
				 C.ORGANIZATION_ID);

Line: 370

	UPDATE WIP_SCHEDULING_INTERFACE
	SET PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE,
	    PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE
	WHERE GROUP_ID = P_GROUP_ID
	AND PROCESS_PHASE = WIP_CONSTANTS.ML_VALIDATION
	AND PROCESS_STATUS = WIP_CONSTANTS.RUNNING;

Line: 377

	DELETE FROM WIP_SCHEDULING_INTERFACE
	WHERE  GROUP_ID = P_GROUP_ID
	AND    PROCESS_STATUS = WIP_CONSTANTS.ML_COMPLETE
        AND    PROCESS_PHASE = WIP_CONSTANTS.ML_COMPLETE;

Line: 393

	SELECT	count(*)
	INTO	recCount
	FROM	wip_scheduling_interface
	WHERE	group_id = p_group_id
		and process_phase = WIP_CONSTANTS.ML_VALIDATION
		and process_status = WIP_CONSTANTS.PENDING;

Line: 415

	SELECT	count(*)
	INTO	recCount
	FROM	wip_scheduling_interface
	WHERE	group_id = p_group_id
		and process_status = WIP_CONSTANTS.ERROR;

Line: 464

                (SELECT 1
                   FROM WIP_DISCRETE_JOBS WDJ
                  WHERE WDJ.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
                    AND WDJ.ORGANIZATION_ID = WSI.ORGANIZATION_ID
                    AND WDJ.STATUS_TYPE IN (1,3,4,6)) ',
		WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
		'WIP_SI_INVALID_JOB');

Line: 539

			SELECT OPERATION_SEQ_NUM
			FROM WIP_SCHEDULING_INTERFACE SI2
			WHERE SI2.GROUP_ID = WSI.GROUP_ID
			AND   SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			AND   SI2.PROCESS_PHASE = 2
			AND   SI2.PROCESS_STATUS = 2
			MINUS
			SELECT OPERATION_SEQ_NUM
			FROM WIP_OPERATIONS WO
			WHERE WO.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   WO.ORGANIZATION_ID = WSI.ORGANIZATION_ID) ',
		WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
		'WIP_SI_INVALID_OPS');

Line: 560

			SELECT OPERATION_SEQ_NUM
			FROM WIP_OPERATIONS WO
			WHERE WO.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   WO.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			MINUS
			SELECT OPERATION_SEQ_NUM
			FROM WIP_SCHEDULING_INTERFACE SI2
			WHERE GROUP_ID = WSI.GROUP_ID
			AND   SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			AND   SI2.PROCESS_PHASE = 2
			AND   SI2.PROCESS_STATUS = 2) ',
		WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
		'WIP_SI_INVALID_OPS');

Line: 581

			SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
			FROM WIP_SCHEDULING_INTERFACE SI2
			WHERE GROUP_ID = WSI.GROUP_ID
			AND   SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			AND   SI2.PROCESS_PHASE = 2
			AND   SI2.PROCESS_STATUS = 2
			MINUS
			SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
			FROM WIP_OPERATION_RESOURCES WOR
			WHERE WOR.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   WOR.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			) ',
		WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
		'WIP_SI_INVALID_RES');

Line: 603

			SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
			FROM WIP_OPERATION_RESOURCES WOR
			WHERE WOR.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   WOR.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			MINUS
			SELECT OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM
			FROM WIP_SCHEDULING_INTERFACE SI2
			WHERE GROUP_ID = WSI.GROUP_ID
			AND   SI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
			AND   SI2.ORGANIZATION_ID = WSI.ORGANIZATION_ID
			AND   SI2.PROCESS_PHASE = 2
			AND   SI2.PROCESS_STATUS = 2
			 )',
		WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
		'WIP_SI_INVALID_RES');

Line: 629

                ' EXISTS (SELECT 1
                          FROM WIP_SCHEDULING_INTERFACE WSI2
                          WHERE WSI2.GROUP_ID = WSI.GROUP_ID
                          AND   WSI2.WIP_ENTITY_ID = WSI.WIP_ENTITY_ID
                          AND   WSI2.PROCESS_STATUS = 3) ',
                WIP_MASS_LOAD_UTILITIES.MSG_ERROR,
                'WIP_SI_OTHERS_FAILED');

Line: 638

PROCEDURE UPDATE_REQ_DATES(P_WIP_ENTITY_ID NUMBER,
			   P_ORGANIZATION_ID NUMBER) IS
BEGIN

	UPDATE WIP_REQUIREMENT_OPERATIONS WRO
	SET    (DATE_REQUIRED,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY) =
	(SELECT FIRST_UNIT_START_DATE,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY
	   FROM WIP_OPERATIONS WO
	  WHERE WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
	    AND WO.ORGANIZATION_ID = WRO.ORGANIZATION_ID
	    AND WO.OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM))
	WHERE   WRO.WIP_ENTITY_ID = P_WIP_ENTITY_ID
	AND     WRO.ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 656

END UPDATE_REQ_DATES;

Line: 658

PROCEDURE UPDATE_JOB_DATES(P_WIP_ENTITY_ID NUMBER,
			   P_ORGANIZATION_ID NUMBER) IS
BEGIN
	UPDATE 	WIP_DISCRETE_JOBS WDJ
	SET    	SCHEDULED_START_DATE =
	(SELECT MIN(FIRST_UNIT_START_DATE)
	   FROM	WIP_OPERATIONS WO
	  WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
	    AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID),
		SCHEDULED_COMPLETION_DATE =
	(SELECT MAX(LAST_UNIT_COMPLETION_DATE)
	   FROM	WIP_OPERATIONS WO
	  WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
	    AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID),
		(LAST_UPDATE_DATE,
		LAST_UPDATED_BY) =
	(SELECT LAST_UPDATE_DATE,
		LAST_UPDATED_BY
	   FROM WIP_OPERATIONS WO
	  WHERE WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
	    AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
	    AND WO.PREVIOUS_OPERATION_SEQ_NUM IS NULL)
	WHERE   WDJ.WIP_ENTITY_ID = P_WIP_ENTITY_ID
	AND     WDJ.ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 683

END UPDATE_JOB_DATES;