DBA Data[Home] [Help]

APPS.AMS_LISTGENERATION_UTIL_PKG SQL Statements

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

Line: 68

  /* for now calling delete procedure in the Generation package because this
is what is needed here. Later that Procedure can be migrated here. */

  AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Before delete in Cancel List Gen'|| l_status_code,
         p_arc_log_used_by => 'LIST',
         p_log_used_by_id  => p_list_header_id,
         p_level => 'LOW');
Line: 76

    delete_list_entries(p_list_header_id, x_msg_count, x_msg_data, x_return_status);
Line: 78

  AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'After delete in Cancel List Gen'|| l_status_code,
         p_arc_log_used_by => 'LIST',
         p_log_used_by_id  => p_list_header_id,
         p_level => 'HIGH');
Line: 84

       AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', p_list_header_id,'HIGH');
Line: 93

	Update_List_Header (p_list_header_id, x_return_status);
Line: 95

  AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'List header info updated '|| l_status_code,
         p_arc_log_used_by => 'LIST',
         p_log_used_by_id  => p_list_header_id,
         p_level => 'LOW');
Line: 101

	  AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing UpdateListHeader. Unable to update List header.', 'LIST', p_list_header_id,'HIGH');
Line: 153

Procedure Delete_List_entries(p_list_header_id in NUMBER,
		x_msg_count OUT NOCOPY NUMBER,
		x_msg_data OUT NOCOPY VARCHAR2,
		x_return_status out nocopy VARCHAR2)
is
 -- l_gen_type	VARCHAR2(200);
Line: 159

l_delete_action varchar2(80);
Line: 166

   AMS_LISTGENERATION_PKG.write_to_act_log('Executing delete_list_entries in listcancelgen.', 'LIST', p_list_header_id,'LOW');
Line: 171

   select generation_type into l_gen_type
   from ams_list_headers_all
   where list_header_id = p_list_header_id;
Line: 178

	DELETE FROM ams_list_entries
	WHERE list_header_id = p_list_header_id;
Line: 181

	AMS_LISTGENERATION_PKG.write_to_act_log(sql%rowcount||' entries deleted from ams_list_entries in local instance.', 'LIST', p_list_header_id,'LOW');
Line: 183

	If g_remote_gen = 'Y' -- based on remote DS so delete remotely
	then
	   AMS_LISTGENERATION_PKG.write_to_act_log('Calling remote procedure with process type as DELETE_LIST_ENTRIES to delete entries in remote instance', 'LIST', p_list_header_id,'LOW');
Line: 200

             'DELETE_LIST_ENTRIES';
Line: 205

             AMS_LISTGENERATION_PKG.write_to_act_log('Entries deleted succesfully in remote instance','LIST', p_list_header_id,'LOW');
Line: 212

    DELETE FROM ams_list_src_type_usages
    WHERE list_header_id = p_list_header_id;
Line: 215

    AMS_LISTGENERATION_PKG.write_to_act_log('Procedure delete_list_entries executed successfully.', 'LIST', p_list_header_id,'LOW');
Line: 225

    FND_MESSAGE.Set_Token('TEXT', 'Delete List Entries ' || l_delete_action || ' '|| SQLERRM||' '||SQLCODE);
Line: 228

End Delete_list_entries;
Line: 230

Procedure Update_list_header(p_list_header_id in Number,
--			     x_msg_count IN NUMBER,
--                             x_msg_data IN VARCHAR2,
                             x_return_status OUT NOCOPY VARCHAR2)
AS
Begin

  x_return_status := FND_API.G_RET_STS_SUCCESS;
Line: 241

   Currently, I don't see the remote list header table getting updated.
  Endif
*/

 update ams_list_headers_all
     set --WORKFLOW_ITEM_KEY  = NULL,
	status_code      = 'DRAFT',
	ctrl_status_code = 'DRAFT',
	user_status_id   = 311,
	last_update_date = sysdate,
	status_date      = sysdate,
	NO_OF_ROWS_DUPLICATES = null,
	NO_OF_ROWS_MIN_REQUESTED = null,
	NO_OF_ROWS_MAX_REQUESTED = null,
	NO_OF_ROWS_IN_LIST = null,
	NO_OF_ROWS_IN_CTRL_GROUP = null,
	NO_OF_ROWS_ACTIVE = null,
	NO_OF_ROWS_INACTIVE = null,
	NO_OF_ROWS_MANUALLY_ENTERED = null,
	NO_OF_ROWS_DO_NOT_CALL  = null,
	NO_OF_ROWS_DO_NOT_MAIL  = null,
	NO_OF_ROWS_RANDOM = null
    where list_header_id = p_list_header_id;
Line: 268

     AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing procedure Update_list_header '||sqlcode||'  '||sqlerrm, 'LIST', p_list_header_id,'HIGH');
Line: 282

End Update_list_header;
Line: 293

select workflow_item_key
from ams_list_headers_all
where list_header_id = p_list_header_id;
Line: 349

select sum(decode(enabled_flag,'N',0,1)),
       sum(decode(enabled_flag,'Y',0,1)),
       sum(1),
       sum(decode(part_of_control_group_flag,'Y',1,0))
from ams_list_entries
where list_header_id = p_list_header_id ;
Line: 357

select list_type, ctrl_gen_mode
  from ams_list_headers_all
 where list_header_id = p_list_header_id;
Line: 391

   update ams_list_headers_all
      set ctrl_conc_job_id = l_request_id,
          last_update_date = sysdate
    where list_header_id = p_list_header_id;
Line: 400

     update ams_list_headers_all
        set ctrl_status_code = 'FAILED',
            status_code = status_code_old,
	    status_code_old = null,
            last_update_date = sysdate,
   	    user_status_id = 303
      where list_header_id = p_list_header_id;
Line: 414

select ctrl_conc_job_id,status_code_old,status_code
  from ams_list_headers_all
 where list_header_id = p_list_header_id;
Line: 419

select user_status_id
  from ams_user_statuses_vl
 where system_status_code = p_status_code
  and system_status_type = 'AMS_LIST_STATUS';
Line: 451

   update ams_list_headers_all
      set ctrl_status_code = 'DRAFT',
          status_code = nvl(status_code_old,status_code),
          last_update_date = sysdate,
	  user_status_id = l_old_status_id
    where list_header_id = p_list_header_id;
Line: 476

select nvl(stypes.remote_flag,'N') ,database_link
  from ams_list_src_types stypes, ams_list_headers_all list
 where list.list_source_type = stypes.source_type_code
   and list_header_id  =  p_list_header_id;
Line: 494

      p_split_preview_count_tbl(i).sp_query := 'SELECT count(1) '||substr(p_split_preview_count_tbl(i).sp_query,instr(upper(p_split_preview_count_tbl(i).sp_query), ' FROM '));