APPS.IES_SVY_DEPLOYMENT_PVT SQL Statements

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

Line: 76

	SELECT NVL(SVR.NO_OF_REMINDERS,0) no_of_reminders
		,NVL(SVR.REMINDER_INTERVAL,0) reminder_interval
		,SVR.SURVEY_REMINDER_ID survey_reminder_id
		,SDP.RESPONSE_END_DATE response_end_date
		,SDP.REMINDER_TEMPLATE_ID reminder_template_id
	FROM  IES_SVY_REMINDERS_V  SVR
		,IES_SVY_DEPLYMENTS_V SDP
	WHERE  SDP.SURVEY_DEPLOYMENT_ID = SVR.SURVEY_DEPLOYMENT_ID
	AND    SDP.SURVEY_DEPLOYMENT_ID = p_deployment_id;

Line: 177

			Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'ERROR',
					   p_reminder_type => p_reminder_type,
					   p_update_flag   => 'Y');

Line: 182

			Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'PENDING',
					   p_reminder_type => p_reminder_type,
					   p_update_flag => 'Y');

Line: 186

			UPDATE ies_svy_deplyments_all
			SET concurrent_req_id = l_schedule_id
			WHERE survey_deployment_id =  p_deployment_id;

Line: 195

		SELECT response_end_date
		INTO l_response_end_date
		FROM ies_svy_deplyments_v
		WHERE survey_deployment_id = p_deployment_id;

Line: 212

				SELECT IES_SVY_REMINDER_HST_S.nextval INTO l_reminder_hst_id FROM DUAL;

Line: 214

					INSERT INTO ies_svy_reminder_hst_v
							(survey_reminder_hst_id
							,object_version_number
							,created_by
							,creation_date
							,last_updated_by
							,last_update_date
							,last_update_login
							,survey_reminder_id
							,reminder_date)
					VALUES
							(l_reminder_hst_id
							,1
							,p_user_id
							,sysdate
							,p_user_id
							,sysdate
							,p_user_id
							,l_survey_reminder_id
							,sysdate
							);

Line: 252

						UPDATE ies_svy_reminder_hst_v
						SET CONCURRENT_REQ_ID = l_schedule_id
						WHERE SURVEY_REMINDER_HST_ID = l_reminder_hst_id;

Line: 354

SELECT  CONTENT_NUMBER, CONTENT_TYPE_ID
FROM JTF_FM_TEMPLATE_CONTENTS, JTF_FM_AMV_ITEMS_VL
WHERE JTF_FM_TEMPLATE_CONTENTS.TEMPLATE_ID = p_template_id
AND JTF_FM_TEMPLATE_CONTENTS.CONTENT_NUMBER = JTF_FM_AMV_ITEMS_VL.ITEM_ID
AND JTF_FM_TEMPLATE_CONTENTS.F_DELETEDFLAG IS NULL;

Line: 361

  SELECT EMAIL_ADDRESS
  FROM AMS_LIST_ENTRIES
  WHERE LIST_ENTRY_ID = p_list_entry_id;

Line: 366

   SELECT EMAIL_SUBJECT_HEADING
   FROM IES_SVY_DEPLYMENTS_ALL
    WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;

Line: 562

	--get the survey_list_entry_id to be inserted into ies_svy_entrs_remind_hst

	SELECT survey_list_entry_id
	INTO l_survey_list_entry_id
	FROM ies_svy_list_entries
	WHERE list_entry_id = p_list_entry_id
	AND survey_deployment_id = p_deployment_id;

Line: 571

	INSERT into ies_svy_entrs_remind_hst
		(entry_reminder_hst_id
		,object_version_number
		,created_by
		,creation_date
		,last_updated_by
		,last_update_date
		,last_update_login
		,survey_list_entry_id
		,reminder_date
		,concurrent_req_id
		,fulfillment_req_id)
	VALUES
		(ies_svy_entrs_remind_hst_s.nextval
		,1
		,l_user_id
		,sysdate
		,l_user_id
		,sysdate
		,l_user_id
		,l_survey_list_entry_id
		,sysdate
		,null
		,l_request_id);

Line: 596

     x_message := x_message || ' ,Entry inserted in ies_svy_entrs_remind_hst';

Line: 760

SELECT  CONTENT_NUMBER, CONTENT_TYPE_ID
FROM JTF_FM_TEMPLATE_CONTENTS, JTF_FM_AMV_ITEMS_VL
WHERE JTF_FM_TEMPLATE_CONTENTS.TEMPLATE_ID = p_template_id
AND JTF_FM_TEMPLATE_CONTENTS.CONTENT_NUMBER = JTF_FM_AMV_ITEMS_VL.ITEM_ID
AND JTF_FM_TEMPLATE_CONTENTS.F_DELETEDFLAG IS NULL;

Line: 767

   SELECT EMAIL_SUBJECT_HEADING
   FROM IES_SVY_DEPLYMENTS_ALL
    WHERE SURVEY_DEPLOYMENT_ID = p_deployment_id;

Line: 818

		SELECT REMINDER_EMAIL_SUBJECT
		INTO l_subject
		FROM ies_svy_reminders_v
		WHERE survey_deployment_id = p_deployment_id;

Line: 929

 	SELECT QUERY_ID
 	INTO l_query_id
 	FROM JTF_FM_QUERY
 	WHERE QUERY_NAME = 'SURVEY_LIST_QUERY';

Line: 978

	 -- a record should be inserted in ies_svy_reminder_hst

	IF(p_reminder_type is null) THEN
		-- Set Deployment Status to be Active
		Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'ACTIVE',
					   p_reminder_type => p_reminder_type,
					   p_update_flag => 'Y');

Line: 988

          UPDATE ies_svy_list_entries
		SET survey_sent_date = sysdate
          WHERE survey_deployment_id = p_deployment_id;

Line: 992

		-- Update FM Request ID
		UPDATE ies_svy_deplyments_v
		SET fulfillment_req_id = l_request_id
		WHERE survey_deployment_id = p_deployment_id;

Line: 998

    	--else a record should be inserted in ies_svy_reminder_hst if its a reminder

         UPDATE ies_svy_reminder_hst_v
         SET fulfillment_req_id = l_request_id
         WHERE SURVEY_REMINDER_HST_ID = p_reminder_hst_id;

Line: 1036

		Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'ERROR',
					   p_reminder_type => p_reminder_type,
					   p_update_flag => 'Y');

Line: 1065

		Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'ERROR',
					   p_reminder_type => p_reminder_type,
					   p_update_flag => 'Y');

Line: 1097

		Update_Dep_Status(p_dep_id => p_deployment_id,
					   p_status => 'ERROR',
					   p_reminder_type => p_reminder_type,
					   p_update_flag => 'Y');

Line: 1149

  SELECT LIST_ENTRY_ID
  FROM  AMS_LIST_ENTRIES
  WHERE LIST_HEADER_ID =  l_list_header_id
  AND ENABLED_FLAG = 'Y';

Line: 1191

     SELECT  LIST_HEADER_ID INTO l_list_header_id
         FROM IES_SVY_DEPLYMENTS_ALL
       WHERE SURVEY_DEPLOYMENT_ID = p_survey_deployment_id;

Line: 1207

     SELECT COUNT(*) INTO l_total_list_entries
        FROM AMS_LIST_ENTRIES
      WHERE LIST_HEADER_ID = l_list_header_id;

Line: 1229

    SELECT count(*) INTO l_count_value
      FROM IES_SVY_LIST_ENTRIES
    WHERE SURVEY_DEPLOYMENT_ID = p_survey_deployment_id;

Line: 1242

        insert into ies_svy_list_entries
		(
		SURVEY_LIST_ENTRY_ID
		,OBJECT_VERSION_NUMBER
		,CREATED_BY
		,CREATION_DATE
		,LAST_UPDATED_BY
		,LAST_UPDATE_DATE
		,LAST_UPDATE_LOGIN
		,SURVEY_DEPLOYMENT_ID
		,LIST_ENTRY_ID
		,RESPONDENT_ID
		)
	   values
		( ies_svy_list_entries_s.nextval
		,1
		,-1
		,sysdate
		,-1
		,sysdate
		,-1
		,p_survey_deployment_id
		,l_list_entry_id
		,l_random_number
		);

Line: 1317

Procedure Update_Dep_Status (p_dep_id NUMBER,
			    p_status VARCHAR2,
			    p_reminder_type VARCHAR2,
			    p_update_flag VARCHAR2) IS

l_deployment_status_code       VARCHAR2(30);

Line: 1330

		SELECT  DEPLOYMENT_STATUS_CODE, SURVEY_CYCLE_ID
		INTO    l_deployment_status_code, l_survey_cycle_id
		FROM IES_SVY_DEPLYMENTS_V
		WHERE SURVEY_DEPLOYMENT_ID = p_dep_id;

Line: 1335

		SELECT count(*)
		INTO l_dep_count_active
		FROM IES_SVY_DEPLYMENTS_V
		WHERE SURVEY_CYCLE_ID = l_survey_cycle_id
		AND DEPLOYMENT_STATUS_CODE  = 'ACTIVE';

Line: 1341

		if (p_update_flag = 'Y') then
			UPDATE IES_SVY_DEPLYMENTS_V
			SET DEPLOYMENT_STATUS_CODE = p_status
			where survey_deployment_id =  p_dep_id;

Line: 1348

			SELECT SURVEY_ID
			INTO l_survey_id
			FROM IES_SVY_CYCLES_V
			WHERE SURVEY_CYCLE_ID = l_survey_cycle_id;

Line: 1354

				UPDATE IES_SVY_SURVEYS_V
				SET SURVEY_STATUS_CODE = 'ACTIVE'
				WHERE SURVEY_ID = l_survey_id;