eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AMS_LISTGENERATION_UTIL_PKG

Source


1 PACKAGE BODY AMS_LISTGENERATION_UTIL_PKG AS
2 /* $Header: amsvlgub.pls 120.6 2006/02/23 01:36:26 bmuthukr noship $*/
3 
4 G_PKG_NAME    CONSTANT VARCHAR2(30):='AMS_LIST_UTIL_PKG';
5 G_FILE_NAME   CONSTANT VARCHAR2(12):='amsvlgub.pls';
6 
7 g_remote_gen       VARCHAR2(1) := 'N';
8 g_remote_gen_list   VARCHAR2(1) := 'N';
9 g_database_link     VARCHAR2(128);
10 
11 cancelexcep exception;
12 
13 /* This is the only procedure called in this package.
14  * Which in turn calls the rest. However, the function getWFItemStatus is called
15  * elsewhere.
16  *
17  *
18  */
19 
20 PROCEDURE cancel_list_gen(p_list_header_id in NUMBER,
21 			  p_remote_gen in VARCHAR2,
22 			  p_remote_gen_list in VARCHAR2,
23 			  p_database_link in VARCHAR2,
24 			  x_msg_count OUT NOCOPY NUMBER,
25 			  x_msg_data OUT NOCOPY VARCHAR2,
26 			  x_return_status OUT NOCOPY VARCHAR2)
27 IS
28   l_msg_count   NUMBER ;
29   l_msg_data    VARCHAR2(2000);
30   l_return_status    VARCHAR2(10);
31   l_error_position   varchar2(100);
32 --  l_listheader_rec   ams_listheader_pvt.list_header_rec_type;
33   l_total_recs    number;
34 
35   l_status_code   varchar2(100);
36 --  cancel_list_gen Exception ;
37 
38 Begin
39 
40   l_error_position := '<- Start Cancel List Generation ->';
41 
42   x_return_status := FND_API.G_RET_STS_SUCCESS;
43 
44   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Canceling List Generation',
45             p_arc_log_used_by => 'LIST',
46             p_log_used_by_id  => p_list_header_id,
47             p_level => 'HIGH');
48 
49   g_remote_gen := p_remote_gen; -- 'Y' ==> remote data source
50   g_remote_gen_list := p_remote_gen_list; -- 'Y' ==> generate remotely
51   g_database_link := p_database_link;
52 
53 --  Get status for list = Listheaderid;
54   open get_status_code(p_list_header_id);
55   fetch get_status_code into l_status_code;
56   close get_status_code;
57 
58   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'In cancel List gen, Status code: '|| l_status_code,
59             p_arc_log_used_by => 'LIST',
60             p_log_used_by_id  => p_list_header_id,
61             p_level => 'HIGH');
62 
63 --  l_status_code := 'FAILED';
64 
65   If (l_status_code = 'FAILED') -- This is changing in R12
66   then
67 
68   /* for now calling delete procedure in the Generation package because this
69 is what is needed here. Later that Procedure can be migrated here. */
70 
71   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Before delete in Cancel List Gen'|| l_status_code,
72          p_arc_log_used_by => 'LIST',
73          p_log_used_by_id  => p_list_header_id,
74          p_level => 'LOW');
75 
76     delete_list_entries(p_list_header_id, x_msg_count, x_msg_data, x_return_status);
77 
78   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'After delete in Cancel List Gen'|| l_status_code,
79          p_arc_log_used_by => 'LIST',
80          p_log_used_by_id  => p_list_header_id,
81          p_level => 'HIGH');
82 
83     if x_return_status <> FND_API.g_ret_sts_success then
84        AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing delete_list_entries. Unable to delete entries.', 'LIST', p_list_header_id,'HIGH');
85 --       raise FND_API.g_exc_unexpected_error;
86     end if;
87 
88   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Updating list header info '|| l_status_code,
89          p_arc_log_used_by => 'LIST',
90          p_log_used_by_id  => p_list_header_id,
91          p_level => 'LOW');
92 
93 	Update_List_Header (p_list_header_id, x_return_status);
94 
95   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'List header info updated '|| l_status_code,
96          p_arc_log_used_by => 'LIST',
97          p_log_used_by_id  => p_list_header_id,
98          p_level => 'LOW');
99 
100 	if x_return_status <> FND_API.g_ret_sts_success then
101 	  AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing UpdateListHeader. Unable to update List header.', 'LIST', p_list_header_id,'HIGH');
102         end if;
103 
104 	Commit;
105 
106 	Raise cancelexcep;
107 
108   else
109 
110   AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Cancel List Gen: Status code is not FAILED so exiting. Status code is '||x_return_Status,
111          p_arc_log_used_by => 'LIST',
112          p_log_used_by_id  => p_list_header_id,
113          p_level => 'LOW');
114 
115 	return;
116   end if;
117 
118 Exception
119 /*
120     WHEN FND_API.g_exc_unexpected_error THEN
121      AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing procedure cancel_list_gen '||sqlcode||'  '||sqlerrm, 'LIST', p_list_header_id,'HIGH');
122       x_return_status := FND_API.g_ret_sts_unexp_error ;
123       FND_MSG_PUB.count_and_get(
124             p_encoded => FND_API.g_false,
125             p_count   => x_msg_count,
126             p_data    => x_msg_data
127       );
128 */
129 
130   --WHEN cancelListGen then
131   WHEN cancelexcep then
132 
133      AMS_LISTGENERATION_PKG.write_to_act_log('In procedure cancel_list_gen: User termination detected '||sqlcode||'  '||sqlerrm, 'LIST', p_list_header_id, 'HIGH');
134      x_return_status := FND_API.g_ret_sts_unexp_error ;
135 	Raise cancelListGen;
136 
137     WHEN OTHERS THEN
138      AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing procedure cancel_list_gen '||sqlcode||'  '||sqlerrm, 'LIST', p_list_header_id, 'HIGH');
139      x_return_status := FND_API.g_ret_sts_unexp_error ;
140 
141      IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
142 	FND_MSG_PUB.add_exc_msg(g_pkg_name, g_file_name);
143      END IF;
144 
145      FND_MSG_PUB.count_and_get(
146             p_encoded => FND_API.g_false,
147             p_count   => x_msg_count,
148             p_data    => x_msg_data
149 	);
150 
151 End cancel_list_gen;
152 
153 Procedure Delete_List_entries(p_list_header_id in NUMBER,
154 		x_msg_count OUT NOCOPY NUMBER,
155 		x_msg_data OUT NOCOPY VARCHAR2,
156 		x_return_status out nocopy VARCHAR2)
157 is
158  -- l_gen_type	VARCHAR2(200);
159 l_delete_action varchar2(80);
160 l_total_recs	number;
161 l_null		varchar2(30) := null;
162 l_gen_type	VARCHAR2(20);
163 
164 Begin
165 
166    AMS_LISTGENERATION_PKG.write_to_act_log('Executing delete_list_entries in listcancelgen.', 'LIST', p_list_header_id,'LOW');
167 
168    x_return_status := FND_API.G_RET_STS_SUCCESS;
169 
170 --    If p_listheader_rec.list_type = 'TARGET'
171    select generation_type into l_gen_type
172    from ams_list_headers_all
173    where list_header_id = p_list_header_id;
174 
175 --   If p_listheader_rec.generation_type = 'STANDARD' then
176    If l_gen_type = 'STANDARD' then
177 
178 	DELETE FROM ams_list_entries
179 	WHERE list_header_id = p_list_header_id;
180 
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');
182 
183 	If g_remote_gen = 'Y' -- based on remote DS so delete remotely
184 	then
185 	   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');
186 	   execute immediate
187            'BEGIN AMS_Remote_ListGen_PKG.remote_list_gen'||'@'||g_database_link||'(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12)'||';'||
188       ' END;'
189             using  '1',
190              l_null,
191              'T',
192              l_null,
193              OUT x_return_status,
194              OUT x_msg_count,
195              OUT x_msg_data,
196              p_list_header_id,
197              l_null,
198              l_null,
199              OUT l_total_recs,
200              'DELETE_LIST_ENTRIES';
201 	  if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
202              AMS_LISTGENERATION_PKG.write_to_act_log('Error in executing remote procedure', 'LIST', p_list_header_id,'HIGH');
203              AMS_LISTGENERATION_PKG.write_to_act_log('Error '||x_msg_data , 'LIST', p_list_header_id,'HIGH');
204           else
205              AMS_LISTGENERATION_PKG.write_to_act_log('Entries deleted succesfully in remote instance','LIST', p_list_header_id,'LOW');
206           end if;
207 	end if; --g_remote_gen = 'Y'
208     End if; -- STANDARD
209 
210     AMS_LISTGENERATION_PKG.write_to_act_log('Deleting entries from list src type usages tables.', 'LIST', p_list_header_id,'LOW');
211 
212     DELETE FROM ams_list_src_type_usages
213     WHERE list_header_id = p_list_header_id;
214 
215     AMS_LISTGENERATION_PKG.write_to_act_log('Procedure delete_list_entries executed successfully.', 'LIST', p_list_header_id,'LOW');
216 
217 EXCEPTION
218    WHEN OTHERS THEN
219     x_return_status := FND_API.G_RET_STS_ERROR;
220     AMS_LISTGENERATION_PKG.write_to_act_log(p_msg_data => 'Error Deleting in CancelListGen',
221             p_arc_log_used_by => 'LIST',
222             p_log_used_by_id  => p_list_header_id,
223             p_level => 'HIGH');
224     FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
225     FND_MESSAGE.Set_Token('TEXT', 'Delete List Entries ' || l_delete_action || ' '|| SQLERRM||' '||SQLCODE);
226     FND_MSG_PUB.Add;
227 
228 End Delete_list_entries;
229 
230 Procedure Update_list_header(p_list_header_id in Number,
231 --			     x_msg_count IN NUMBER,
232 --                             x_msg_data IN VARCHAR2,
233                              x_return_status OUT NOCOPY VARCHAR2)
234 AS
235 Begin
236 
237   x_return_status := FND_API.G_RET_STS_SUCCESS;
238 
239 /*
240   If g_remote_gen = 'Y'
241    Currently, I don't see the remote list header table getting updated.
242   Endif
243 */
244 
245  update ams_list_headers_all
246      set --WORKFLOW_ITEM_KEY  = NULL,
247 	status_code      = 'DRAFT',
248 	ctrl_status_code = 'DRAFT',
249 	user_status_id   = 311,
250 	last_update_date = sysdate,
251 	status_date      = sysdate,
252 	NO_OF_ROWS_DUPLICATES = null,
253 	NO_OF_ROWS_MIN_REQUESTED = null,
254 	NO_OF_ROWS_MAX_REQUESTED = null,
255 	NO_OF_ROWS_IN_LIST = null,
256 	NO_OF_ROWS_IN_CTRL_GROUP = null,
257 	NO_OF_ROWS_ACTIVE = null,
258 	NO_OF_ROWS_INACTIVE = null,
259 	NO_OF_ROWS_MANUALLY_ENTERED = null,
260 	NO_OF_ROWS_DO_NOT_CALL  = null,
261 	NO_OF_ROWS_DO_NOT_MAIL  = null,
262 	NO_OF_ROWS_RANDOM = null
263     where list_header_id = p_list_header_id;
264 
265 Exception
266     When Others then
267 
268      AMS_LISTGENERATION_PKG.write_to_act_log('Error while executing procedure Update_list_header '||sqlcode||'  '||sqlerrm, 'LIST', p_list_header_id,'HIGH');
269 
270 	x_return_status := FND_API.g_ret_sts_unexp_error ;
271 
272      IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
273         FND_MSG_PUB.add_exc_msg(g_pkg_name, g_file_name);
274      END IF;
275 /*
276      FND_MSG_PUB.count_and_get(
277             p_encoded => FND_API.g_false,
278             p_count   => x_msg_count,
279             p_data    => x_msg_data
280       );
281 */
282 End Update_list_header;
283 
284 Function getWFItemStatus(p_list_header_id in Number) return VARCHAR2
285 IS
286 
287  l_item_type	CONSTANT VARCHAR2(20) :='AMSLISTG';
288  l_item_key	NUMBER;
289  l_status 	VARCHAR2(20);
290  l_result 	VARCHAR2(1000);
291 
292 cursor wf_item_key(p_list_header_id NUMBER) IS
293 select workflow_item_key
294 from ams_list_headers_all
295 where list_header_id = p_list_header_id;
296 
297 Begin
298   -- Get the work flow item key
299   open wf_item_key(p_list_header_id);
300   fetch wf_item_key into l_item_key;
301   close wf_item_key;
302 
303   if wf_item_key%Notfound then
304     return null;
305   elsif l_item_key = null then
306     return null;
307   end if;
308 
309   WF_Engine.ItemStatus(l_item_type, l_item_key, l_status, l_result);
310   return l_status;
311 
312 Exception
313   When others then
314     AMS_LISTGENERATION_PKG.write_to_act_log('Error getting ItemStatus. ItemKey = '||l_item_key||' and Status = '|| l_status,'LIST', p_list_header_id, 'HIGH');
315 
316 End getWFItemStatus;
317 
318 Function isListCancelling(p_list_header_id in Number) return VARCHAR2
319 IS
320   l_status_code   varchar2(100);
321 Begin
322 
323   open get_status_code(p_list_header_id);
324   fetch get_status_code into l_status_code;
325   close get_status_code;
326 
327   IF (l_status_code='FAILED') AND (getWFItemStatus(p_list_header_id) = 'ACTIVE')
328   THEN
329     return 'Y';
330   ELSE
331     return 'N';
332   END IF;
333 
334 Exception
335 
336  When others then
337     AMS_LISTGENERATION_PKG.write_to_act_log('Error getting status code = '|| l_status_code, 'LIST', p_list_header_id, 'HIGH');
338     return 'N';
339 End isListCancelling;
340 
341 PROCEDURE START_CTRL_GRP_PROCESS
342              (p_list_header_id  in  number) is
343 
344 l_request_id    number :=NULL;
345 X_CTRL_STATUS VARCHAR2(200);
346 l_log_level varchar2(200);
347 
348 cursor c_count_entries is
349 select sum(decode(enabled_flag,'N',0,1)),
350        sum(decode(enabled_flag,'Y',0,1)),
351        sum(1),
352        sum(decode(part_of_control_group_flag,'Y',1,0))
353 from ams_list_entries
354 where list_header_id = p_list_header_id ;
355 
356 cursor c_list_header_info is
357 select list_type, ctrl_gen_mode
358   from ams_list_headers_all
359  where list_header_id = p_list_header_id;
360 
361 l_no_of_rows_in_list            number;
362 l_no_of_rows_active             number;
363 l_no_of_rows_inactive           number;
364 l_no_of_rows_in_ctrl_group      number;
365 
366 l_list_header_info_rec  c_list_header_info%rowtype;
367 
368 X_RETURN_STATUS VARCHAR2(1);
369 X_MSG_COUNT NUMBER;
370 X_MSG_DATA VARCHAR2(200);
371 x_msg_data1 varchar2(200);
372 
373 BEGIN
374    -- Initialize API return status to SUCCESS
375    x_return_status := FND_API.G_RET_STS_SUCCESS;
376    open c_list_header_info;
377    fetch c_list_header_info into l_list_header_info_rec;
378    close c_list_header_info;
379 
380    if l_list_header_info_rec.list_type <> 'TARGET' then
381         return;
382    end if;
383 
384    --select decode(p_log_flag,'Y','HIGH','LOW') into l_log_level from dual;
385 
386    l_request_id := FND_REQUEST.SUBMIT_REQUEST(
387 			application => 'AMS',
388 			program     => 'AMSCGSP',
389 			argument1   => p_LIST_HEADER_id);
390 
391    update ams_list_headers_all
392       set ctrl_conc_job_id = l_request_id,
393           last_update_date = sysdate
394     where list_header_id = p_list_header_id;
395    commit;
396 
397 
398 exception
399    when others then
400      update ams_list_headers_all
401         set ctrl_status_code = 'FAILED',
402             status_code = status_code_old,
403 	    status_code_old = null,
404             last_update_date = sysdate,
405    	    user_status_id = 303
406       where list_header_id = p_list_header_id;
407      commit;
408 END START_CTRL_GRP_PROCESS;
409 
410 PROCEDURE CANCEL_CTRL_GRP_PROCESS
411              (p_list_header_id  in  number) IS
412 
413 cursor c1 is
414 select ctrl_conc_job_id,status_code_old,status_code
415   from ams_list_headers_all
416  where list_header_id = p_list_header_id;
417 
418 cursor c_user_status(p_status_code in varchar2) is
419 select user_status_id
420   from ams_user_statuses_vl
421  where system_status_code = p_status_code
422   and system_status_type = 'AMS_LIST_STATUS';
423 
424 l_request_id      number := 0;
425 l_status_code_old varchar2(100);
426 l_old_status_id   number;
427 l_msg_text        varchar2(1000);
428 l_cancel_status   boolean;
429 l_status_code     varchar2(100);
430 
431 BEGIN
432    -- Initialize API return status to SUCCESS
433    --x_return_status := FND_API.G_RET_STS_SUCCESS;
434 
435    open c1;
436    fetch c1 into l_request_id,l_status_code_old,l_status_code;
437    close c1;
438 
439    if l_status_code_old is not null then
440       open c_user_status(l_status_code_old);
441       fetch c_user_status into l_old_status_id;
442       close c_user_status;
443    else
444       open c_user_status(l_status_code);
445       fetch c_user_status into l_old_status_id;
446       close c_user_status;
447    end if;
448 
449    l_cancel_status := fnd_concurrent.cancel_request(l_request_id,l_msg_text);
450 
451    update ams_list_headers_all
452       set ctrl_status_code = 'DRAFT',
453           status_code = nvl(status_code_old,status_code),
454           last_update_date = sysdate,
455 	  user_status_id = l_old_status_id
456     where list_header_id = p_list_header_id;
457 
458     commit;
459 
460 
461 exception
462    when others then
463         null;
464 END CANCEL_CTRL_GRP_PROCESS;
465 
466 --Procedure added by bmuthukr for CR#4886329
467 procedure get_split_preview_count(p_split_preview_count_tbl IN OUT NOCOPY AMS_LISTGENERATION_UTIL_PKG.split_preview_count_tbl%type,
468                                   p_list_header_id          IN NUMBER,
469                                   x_return_status           OUT NOCOPY VARCHAR2,
470                                   x_msg_count               OUT NOCOPY NUMBER,
471                                   x_msg_data                OUT NOCOPY VARCHAR2) is
472 
473 
474 
475 cursor c_remote_list is
476 select nvl(stypes.remote_flag,'N') ,database_link
477   from ams_list_src_types stypes, ams_list_headers_all list
478  where list.list_source_type = stypes.source_type_code
479    and list_header_id  =  p_list_header_id;
480 
481 l_remote_flag  varchar2(1) := 'N';
482 l_db_link      varchar2(100) := null;
483 l_cnt          number;
484 remote_exp     exception;
485 
486 begin
487 
488    open c_remote_list;
489    fetch c_remote_list into l_remote_flag, l_db_link;
490    close c_remote_list;
491 
492    for i in 1..p_split_preview_count_tbl.count
493    loop
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 '));
495       if nvl(l_remote_flag,'N') = 'N' then
496          execute immediate p_split_preview_count_tbl(i).sp_query INTO l_cnt;
497       else --need to execute the sql in remote instance.
498          execute immediate
499             'begin
500                ams_remote_listgen_pkg.remote_get_count'||'@'||l_db_link||'(:1,:2,:3,:4,:5)'||';'||
501             ' end;'
502             using p_split_preview_count_tbl(i).sp_query,
503             out l_cnt,
504             out x_msg_count,
505             out x_msg_data,
506             out x_return_status;
507          if nvl(x_return_status,'S') in ('E','U') then -- resulted in error.
508             raise remote_exp;
509          end if;
510       end if;
511       p_split_preview_count_tbl(i).prv_count := l_cnt;
512    end loop;
513 exception
514    when remote_exp then
515       x_msg_count := 1;
516       x_return_status := 'E';
517       x_msg_data := 'Error while executing the sql in remote schema '||x_msg_data;
518    when others then
519       x_msg_count := 1;
520       x_return_status := 'E';
521       x_msg_data := sqlcode||'   '||sqlerrm;
522 end get_split_preview_count;
523 
524 END AMS_LISTGENERATION_UTIL_PKG;