APPS.IEM_ARCH_PVT SQL Statements

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

Line: 45

   select iem_arch_requests_s1.nextval into l_seq_id from dual;

Line: 55

          	p_LAST_UPDATED_BY    =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
               p_LAST_UPDATE_DATE    =>sysdate,
               p_LAST_UPDATE_LOGIN    =>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')),
               x_return_status=>l_ret_status,
               x_msg_count=>l_msg_count,
               x_msg_data=>l_msg_data
                );

Line: 80

          	p_LAST_UPDATED_BY    =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
               p_LAST_UPDATE_DATE    =>sysdate,
               p_LAST_UPDATE_LOGIN    =>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')),
               x_return_status=>l_ret_status,
               x_msg_count=>l_msg_count,
               x_msg_data=>l_msg_data
                );

Line: 192

	select count(*) into l_arch_count
	from iem_archived_dtls
	where request_id=p_request_id
	and nvl(media_id,1)>0;			--	ignoring data with no media id info where media id=0

Line: 197

								-- some processing error like move or delete message error
	IF l_arch_count=0 THEN
		update iem_Arch_Requests
		set status='C'
		where Request_id=p_request_id;

Line: 202

		update iem_archived_folders
		set arch_folder_status='O'
		where arch_folder_id=l_arch_Folder_id;

Line: 206

		update iem_Arch_Requests
		set status='E',
		arch_comment=nvl(l_out_text,' ')||nvl(m_out_text,' ')
		where Request_id=p_request_id;

Line: 253

	l_api_name        		VARCHAR2(255):='delete_request';

Line: 275

   delete from IEM_ARCH_REQUESTS
   where request_id=p_request_id;

Line: 277

   delete from IEM_ARCHIVED_DTLS
   where request_id=p_request_id;

Line: 290

       FND_MESSAGE.SET_NAME('IEM','IEM_ARCH_OES_FLD_DELETE_ERROR');

Line: 362

 select message_id
 from iem_arch_msgdtls where
 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
 and received_date

 select message_id
 from iem_arch_msgdtls where
 email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
 and creation_date

	select to_date(p_date,p_date_format) into l_date from dual;

	select to_char(max(creation_date),p_date_format)
	into x_arch_date
	from iem_arch_requests
	where email_account_id=p_email_account_id
	and folder_name=p_folder and status ='C';

	select count(*) into x_count
	from iem_arch_msgdtls where
	email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1);

	select count(*) into l_count
 	from iem_arch_msgdtls where
 	email_account_id=p_email_account_id and mailproc_Status=substr(p_folder,1,1)
 	and creation_date

		select count(*) into l_proc_count
		from iem_arch_requests
		where email_account_id=p_email_account_id
		and folder_name=p_folder
		and status in ('S','E','P');

			select meaning into x_action_desc
			from fnd_lookups
			where lookup_type='IEM_ARCH_STATUS'
			and lookup_code=l_action_flg;

	select request_id,request_type from iem_arch_Requests
	where status='S'
	order by creation_date for update;

		update iem_arch_Requests
		set status='I'
		where request_id=v1.request_id;

 cursor c1 is select a.ih_media_item_id,b.source_message_id from iem_arch_msgdtls a,iem_archived_dtls b
 where b.request_id=p_request_id
 and a.message_id=b.source_message_id;

						update iem_archived_dtls
						set error_summary='Error While Creating MLCS'
						where request_id=p_request_id  and source_message_id=v1.source_message_id;

						delete from iem_archived_dtls where request_id=p_request_id
						and source_message_id=v1.source_message_id;

						delete from iem_arch_msgs where message_id=v1.source_message_id;

						delete from iem_arch_msgdtls where message_id=v1.source_message_id;

					-- Update the MLCS with ENDDATE TIME
					l_media_lc_rec.milcs_id:=l_milcs_id;

  			JTF_IH_PUB.Update_MediaLifeCycle( 1.0,
						'T',
						'F',
						TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
						TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
						nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
						TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
						l_ret_status,
						l_msg_count,
						l_msg_data,
						l_media_lc_rec);

						update iem_archived_dtls
						set error_summary='Error While Updating MLCS'
						where request_id=p_request_id  and source_message_id=v1.source_message_id;