[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_MAINT_PVT
Source
1 Package Body AMS_LIST_MAINT_PVT AS
2 /* $Header: amsvlmgb.pls 115.38 2002/11/20 23:00:16 jieli ship $ */
3
4 /*==========================================================================+
5 | PROCEDURES. |
6 | Schedule_List. |
7 | Submit_List_For_Generation. |
8 | Check_List_Association. |
9 | Create_Source_View. |
10 | Copy_List. |
11 | Create_Discoverer_Url. |
12 +==========================================================================*/
13
14
15 --global package variables.
16 g_sqlerrm varchar2(500);
17 g_sqlcode varchar2(500);
18
19 ----------------------------------------------------------------------------
20 --This Variable stores a record from the AMS_LIST_HEADERS_ALL table. --
21 ----------------------------------------------------------------------------
22 g_listheader_rec AMS_LISTHEADER_PVT.list_header_rec_type;
23
24 ----------------------------------------------------------------------------
25 --This Variable stores a record from the AMS_LIST_SELECT_ACTIONS table. --
26 ----------------------------------------------------------------------------
27 g_listaction_rec AMS_LISTACTION_PVT.action_rec_type;
28
29 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_List_Maint_PVT';
30 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvlmgb.pls';
31
32 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
33 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
34 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
35
36
37 -----------------------------------------------------------------------------
38 -- Procedure
39 -- Create_Discoverer_Url
40
41 -- PURPOSE
42 -- Creates a URL which will launch Web Discoverer.
43 --
44 -- PARAMETERS
45
46 -- NOTES
47 -- created tdonohoe 09-May-2000
48 -----------------------------------------------------------------------------
49 PROCEDURE Create_Discoverer_Url(p_text IN VARCHAR2,
50 p_application_id IN NUMBER,
51 p_responsibility_id IN NUMBER,
52 p_security_group_id IN NUMBER,
53 p_function_id IN NUMBER,
54 p_target IN VARCHAR2,
55 p_session_id IN NUMBER,
56 x_discoverer_url OUT NOCOPY VARCHAR2
57 )
58 IS
59
60 BEGIN
61
62 x_discoverer_url := ORACLEAPPS.CREATERFLINK( p_text => p_text
63 ,p_application_id => p_application_id
64 ,p_responsibility_id => p_responsibility_id
65 ,p_security_group_id => p_security_group_id
66 ,p_function_id => p_function_id
67 ,p_target => p_target
68 ,p_session_id => p_session_id);
69
70
71
72 END;
73
74
75 ----------------------------------------------------------------------------------------------------------
76 -- Procedure
77 -- Schedule_List
78
79 -- PURPOSE
80 -- Called by Concurrent Manager to Schedule The AMS_LISTGENERATION_PKG.GENERATE_LIST procedure.
81 --
82 --
83 -- PARAMETERS
84
85 -- NOTES
86 -- created tdonohoe 11/23/99
87 ---------------------------------------------------------------------------------------------------------
88
89 Procedure SCHEDULE_LIST(errbuf OUT NOCOPY varchar2,
90 retcode OUT NOCOPY number,
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
93 p_commit IN VARCHAR2 := FND_API.G_FALSE,
94 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
95 p_list_header_id IN NUMBER,
96 p_list_used_by_id IN VARCHAR2 := NULL,
97 p_arc_list_used_by IN VARCHAR2 := NULL,
98 p_new_list_name IN VARCHAR2 := NULL,
99 p_copy_entries IN VARCHAR2 := 'Y') IS
100
101 l_return_status varchar2(100);
102 l_msg_count number;
103 l_msg_data varchar2(2000);
104
105 Begin
106
107 null;
108 /*
109 -----------------------------------------
110 --The Result returned by SCHEDULE_LIST.--
111 -----------------------------------------
112 retcode :=0;
113
114
115 AMS_Utility_PVT.Create_Log (
116 x_return_status => l_return_status,
117 p_arc_log_used_by => 'LIST',
118 p_log_used_by_id => p_list_header_id,
119 p_msg_data => G_PKG_NAME || '.' || 'SCHEDULE_LIST: ' || TO_CHAR (p_validation_level));
120
121 AMS_LISTGENERATION_PKG.Generate_List( p_api_version => p_api_version,
122 p_init_msg_list => p_init_msg_list,
123 p_commit => p_commit,
124 p_validation_level => p_validation_level,
125 p_list_header_id => p_list_header_id,
126 p_list_used_by_id => p_list_used_by_id,
127 p_arc_list_used_by => p_arc_list_used_by,
128 p_new_list_name => p_new_list_name,
129 p_copy_entries => p_copy_entries,
130 x_return_status => l_return_status,
131 x_msg_count => l_msg_count,
132 x_msg_data => l_msg_data);
133
134
135 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)THEN
136 retcode :=0;
137 ELSE
138 retcode :=1;
139 END IF;
140 */
141
142
143 END SCHEDULE_LIST;
144
145 ----------------------------------------------------------------------------------------------------------
146 -- Procedure
147 -- Submit_List_For_Generation
148
149 -- PURPOSE
150 -- Submit List for Generation to Concurrent Manager at the specified_time.
151 --
152 -- PARAMETERS
153
154 -- NOTES
155 -- created tdonohoe 11/22/99
156 -- modified sugupta 04/24/2000 added timezone id..
157 ---------------------------------------------------------------------------------------------------------
158 Procedure Submit_List_For_Generation(p_list_header_id in number,
159 p_user_id IN NUMBER,
160 p_resp_id IN NUMBER,
161 p_list_used_by_id in number := NULL,
162 p_arc_list_used_by in varchar2 := NULL,
163 p_timezone_id in NUMBER := NULL,
164 p_time in DATE := NULL,
165 p_name in varchar2 := NULL,
166 p_copy_entries in varchar2 := 'Y',
167 x_schedule_id OUT NOCOPY number ) IS
168
169 PRAGMA AUTONOMOUS_TRANSACTION;
170
171 l_return_number NUMBER := NULL;
172
173 l_return_status VARCHAR2(1) := NULL;
174 l_msg_count NUMBER := NULL;
175 l_msg_data VARCHAR2(2000) := NULL;
176 l_start_time DATE := NULL;
177
178 Begin
179
180 null;
181 /*
182 -- sugupta 04/24/2000 timezone_id and USER_ENTERED_START_TIME has been added in the table
183 -- Screen passes timezone id and user entered date.. api should convert user entered date
184 -- into date for system which will be passed as start time in call to submit_request
185 -- p_user_tz_id can be null... take value in profile option for User Timezone
186
187 IF (p_time IS NOT NULL)
188 THEN
189 AMS_UTILITY_PVT.Convert_Timezone(
190 p_init_msg_list => FND_API.G_TRUE,
191 x_return_status => l_return_status,
192 x_msg_count => l_msg_count,
193 x_msg_data => l_msg_data,
194
195 p_user_tz_id => p_timezone_id,
196 p_in_time => p_time,
197 p_convert_type => 'SYS',
198
199 x_out_time => l_start_time
200 );
201
202 -- If any errors happen let start time be sysdate
203 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
204 l_start_time := sysdate;
205 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
206 l_start_time := sysdate;
207 END IF;
208 END IF;
209
210 --tdonohoe 12-15-1999
211 --setting up oracle applications profile values;
212
213 fnd_global.apps_initialize(p_user_id, p_resp_id, 530);
214
215 x_schedule_id := FND_REQUEST.SUBMIT_REQUEST(application => 'AMS',
216 program => 'AMSLISTGEN',
217 start_time => to_char(l_start_time,'DD-MON-YYYY HH24:MI'),
218 argument1 => 1.0,
219 argument2 => FND_API.G_TRUE,
220 argument3 => FND_API.G_FALSE,
221 argument4 => FND_API.G_VALID_LEVEL_FULL,
222
223 argument5 => p_list_header_id,
224 argument6 => p_list_used_by_id,
225 argument7 => p_arc_list_used_by,
226 argument8 => p_name,
227 argument9 => p_copy_entries );
228 if(x_schedule_id <>0)then
229 update ams_list_headers_all
230 set status_code = 'PENDING', status_date = sysdate
231 where list_header_id = p_list_header_id;
232
233 end if;
234
235 commit;
236
237 */
238 End;
239
240 ----------------------------------------------------------------------------------------------------------
241 -- Procedure
242 -- Check_List_Association
243 -- PURPOSE
244 -- A List may optionally be associated with a valid marketing activity.
245 -- This Procedure checks that the specified activity and type exist.
246
247 -- PARAMETERS
248 -- P_ARC_LIST_USED_BY , valid values are CAMP,CSCH,EVEH,EVEO.
249 -- P_LIST_USED_BY_ID , the foreign key to the marketing entity table.
250 -- NOTES
251 -- created tdonohoe 11/16/99
252 ---------------------------------------------------------------------------------------------------------
253 Function Check_List_Association(p_arc_list_used_by varchar2,p_list_used_by_id number)
254 Return Varchar2 IS
255
256 l_return_status VARCHAR2(1);
257
258 Begin
259
260 null;
261 /*
262 l_return_status := FND_API.G_TRUE;
263
264 IF(p_arc_list_used_by = 'CSCH')THEN
265 IF AMS_Utility_PVT.check_fk_exists(
266 'ams_campaign_schedules',
267 'campaign_schedule_id',
268 p_list_used_by_id) = FND_API.g_false
269 THEN
270 l_return_status := FND_API.G_FALSE;
271 RETURN l_return_status;
272
273 END IF;
274 ELSIF(p_arc_list_used_by = 'CAMP')THEN
275 IF AMS_Utility_PVT.check_fk_exists(
276 'ams_campaigns_all_b',
277 'campaign_id',
278 p_list_used_by_id) = FND_API.g_false
279 THEN
280 l_return_status := FND_API.G_FALSE;
281 RETURN l_return_status;
282 END IF;
283 ELSIF(p_arc_list_used_by = 'EVEO')THEN
284 IF AMS_Utility_PVT.check_fk_exists(
285 'ams_event_offers_all_b',
286 'event_offer_id',
287 p_list_used_by_id) = FND_API.g_false
288 THEN
289 l_return_status := FND_API.G_FALSE;
290 RETURN l_return_status;
291 END IF;
292 ELSIF(p_arc_list_used_by = 'EVEH')THEN
293 IF AMS_Utility_PVT.check_fk_exists(
294 'ams_event_headers_all_b',
295 'event_header_id',
296 p_list_used_by_id) = FND_API.g_false
297 THEN
298 l_return_status := FND_API.G_FALSE;
299 RETURN l_return_status ;
300 END IF;
301 ELSE
302 l_return_status := FND_API.G_FALSE;
303 RETURN l_return_status ;
304 END IF;
305
306 */
307
308
309 End Check_List_Association;
310
311 -----------------------------------------------------------------------------------------------
312 -- Procedure
313 -- Create_Source_View
314 --
315 -- PURPOSE
316 -- 1. Creates a view based on the mapping information specified in the AMS_LIST_SRC_TYPES
317 -- and AMS_LIST_SRC_FIELDS tables.
318
319 -- 2. The view will select only the columns which have been mapped to in the FIELD_TABLE_NAME
320 -- column in the AMS_LIST_SRC_FIELDS table.
321
322 -- 3. Each column in the view will be given an alias which corresponds to
323 -- the SOURCE_COLUMN_MEANING column in the AMS_LIST_SRC_FIELDS table.
324
325 -- PARAMETERS
326 -- 1. p_list_source_type specifies the type of mapping which the view is being created for.
327 -- IMPORT or TARGET.
328
329 -- 2. p_source_type_code specifies the mapping code which the view is being created for.
330 --
331
332 -- NOTES
333 -- 1. The view name is constucted as follows AMS_||P_LIST_SOURCE_TYPE||P_SOURCE_CODE||_V.
334
335 -- HISTORY
336 -- 07/26/1999 tdonohoe created
337 -- 03/06/2000 tdonohoe modified 1) If the name of the source type starts with "AMS_" then
338 -- this is added to the name of the view.
339 -- 2) The view will include all columns from any sub source types
340 -- which are associated with the the master source type.
341 -- 05/07/2000 tdonohoe modified 1) remove any occurances of '-' from the cursors
342 -- C_Source_Fields and C_Sub_Source_Fields
343 -- 07/13/2000 vbhandar modified 1)fixed problem with view not being generated in target
344 -- sql string not constructed properly
345 -----------------------------------------------------------------------------------------------
346 -- End of Comments
347
348 Procedure Create_Source_View(p_api_version IN NUMBER,
349 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
350 p_commit IN VARCHAR2 := FND_API.g_false,
351 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
352
353 p_user_id IN NUMBER,
354 p_resp_id IN NUMBER,
355 p_resp_appl_id IN NUMBER,
356
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2,
360
361 p_list_source_type VARCHAR2,
362 p_source_type_code VARCHAR2) IS
363
364
365 l_api_name CONSTANT VARCHAR2(30) := 'Create_Source_View';
366 l_api_version CONSTANT NUMBER := 1.0;
367
368 --selects the "mapped to" fields for the specified list_source_type and source_type_code.
369
370 Cursor C_Source_Fields Is Select f.Field_Column_Name,replace(replace(substr(f.SOURCE_COLUMN_MEANING,1,30),' ','_'),'-','')
371 From Ams_List_Src_Fields f,
372 Ams_List_Src_Types t
373 Where upper(t.Source_Type_Code) = upper(P_Source_Type_Code)
374 And upper(t.list_source_type) = upper(P_List_Source_Type)
375 And f.list_source_type_id = t.list_source_type_id
376 order by 2;
377
378 --selects the "mapped to" fields for the specified list_source_type and source_type_code.
379 Cursor C_Sub_Source_Fields
380 Is Select f.Field_Column_Name,replace(replace(substr(f.SOURCE_COLUMN_MEANING,1,30),' ','_'),'-','')
381 From Ams_List_Src_Fields f,
382 Ams_List_Src_Types t,
383 Ams_List_Src_Type_Assocs a
384 Where upper(t.Source_Type_Code) = upper(P_Source_Type_Code)
385 And upper(t.list_source_type) = upper(P_List_Source_Type)
386 And a.master_source_type_id = t.list_source_type_id
387 And a.sub_source_type_id = f.list_source_type_id
388 order by 2;
389
390
391 l_column_name varchar2(50);
392 l_column_meaning varchar2(50);
393
394 l_view_name varchar2(40);
395 l_sql_str varchar2(10000);
396
397 l_source_code varchar2(50);
398 l_type varchar2(50);
399
400
401 l_result boolean;
402 l_stmt varchar2(8000);
403 l_status varchar2(10);
404 l_industry varchar2(10);
405 l_applsys_schema varchar2(30);
406 l_counter number;
407
408 Begin
409
410
411 -- Standard Start of API savepoint
412 SAVEPOINT Create_Source_View;
413
414 -- Standard call to check for call compatibility.
415 IF NOT FND_API.Compatible_API_Call ( l_api_version,
416 p_api_version,
417 l_api_name,
418 G_PKG_NAME)
419 THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422
423
424 -- Initialize message list IF p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list ) THEN
426 FND_MSG_PUB.initialize;
427 END IF;
428
429 -- Debug Message
430 IF (AMS_DEBUG_HIGH_ON) THEN
431 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
432 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Maint_PVT.Create_Source_View: Start', TRUE);
433 FND_MSG_PUB.Add;
434 END IF;
435
436 -- Initialize API return status to success
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439
440 fnd_global.apps_initialize(p_user_id,p_resp_id,p_resp_appl_id);
441
442
443 --replacing any spaces in the p_source_type_code variable with "_".
444 --replacing any spaces in the p_list_source_type variable with "_".
445 l_source_code := replace(p_source_type_code,' ','_');
446 l_type := replace(p_list_source_type,' ','_');
447 l_type := substr(l_type,1,3);
448
449 --if the source code starts with "AMS_" remove from string.
450 if(substr(l_source_code,1,4) = 'AMS_')then
451 l_source_code := substr(l_source_code,5);
452 end if;
453
454 --getting a 20 character substring to ensure that the view name is not
455 --longer than 30 characters when we add the strings "AMS_"+ 3 character list type + "_V"
456 l_source_code := substr(l_source_code,1,20);
457
458
459 --constructing the full view name.
460 l_view_name := 'AMS_'||upper(l_type)||'_'||upper(l_source_code)||'_V';
461 l_sql_str := ' create or replace view '||l_view_name||' as Select ';
462
463
464 open c_source_fields;
465 loop
466 fetch c_source_fields INTO l_column_name,l_column_meaning;
467 exit when c_source_fields%NOTFOUND;
468 l_sql_str := l_sql_str||'ale.'||l_column_name||' '||l_column_meaning||',';
469
470 end loop;
471 close c_source_fields;
472
473 open c_sub_source_fields;
474 loop
475 fetch c_sub_source_fields INTO l_column_name,l_column_meaning;
476 exit when c_sub_source_fields%NOTFOUND;
477 l_sql_str := l_sql_str||l_column_name||' '||l_column_meaning||',';
478
479 end loop;
480 close c_sub_source_fields;
481
482
483 l_result := fnd_installation.get_app_info('FND',
484 l_status,
485 l_industry,
486 l_applsys_schema);
487
488
489
490
491 if(p_list_source_type = 'TARGET')then
492
493 l_sql_str := l_sql_str
494 ||' ale.LIST_ENTRY_ID,ale.LIST_HEADER_ID,ale.OBJECT_VERSION_NUMBER,ale.LIST_SELECT_ACTION_ID,ale.ARC_LIST_SELECT_ACTION_FROM,ale.LIST_SELECT_ACTION_FROM_NAME,'
495 ||' ale.SOURCE_CODE,ale.ARC_LIST_USED_BY_SOURCE,ale.PIN_CODE,ale.LIST_ENTRY_SOURCE_SYSTEM_ID,ale.LIST_ENTRY_SOURCE_SYSTEM_TYPE,'
496 ||'ale.VIEW_APPLICATION_ID,ale.MANUALLY_ENTERED_FLAG,ale.MARKED_AS_DUPLICATE_FLAG,ale.MARKED_AS_RANDOM_FLAG,ale.PART_OF_CONTROL_GROUP_FLAG,'
497 ||' ale.ENABLED_FLAG,ale.CELL_CODE,ale.CAMPAIGN_ID,ale.MEDIA_ID,ale.CHANNEL_ID,ale.CHANNEL_SCHEDULE_ID,ale.EVENT_OFFER_ID,ale.LAST_UPDATE_DATE,ale.LAST_UPDATED_BY,ale.CREATION_DATE,ale.CREATED_BY FROM ams_list_entries ale '
498 ||' WHERE ale.LIST_ENTRY_SOURCE_SYSTEM_TYPE ='||''''||P_SOURCE_TYPE_CODE||'''';
499
500 elsif(p_list_source_type = 'IMPORT')then
501
502 l_sql_str := l_sql_str||' ale.IMPORT_SOURCE_LINE_ID, '
503 || ' ale.OBJECT_VERSION_NUMBER,ale.LAST_UPDATE_DATE, '
504 || ' ale.LAST_UPDATED_BY,ale.CREATION_DATE,ale.CREATED_BY,'
505 || ' ale.LAST_UPDATE_LOGIN,ale.IMPORT_LIST_HEADER_ID, '
506 || ' ale.IMPORT_SUCCESSFUL_FLAG,ale.ENABLED_FLAG, '
507 || ' ale.IMPORT_FAILURE_REASON,'
508 || ' ale.RE_IMPORT_LAST_DONE_DATE,ale.DEDUPE_KEY '
509 || ' FROM AMS_IMP_SOURCE_LINES ale , '
510 || ' ams_imp_list_headers_all ail, '
511 || ' ams_list_src_types alt '
512 ||' WHERE alt.list_source_type = ' || ''''|| 'IMPORT' ||''''
513 ||' and ail.list_source_type_id = alt.list_source_type_id '
514 ||' and alt.source_type_code = '|| ''''||
515 upper(p_source_type_code) ||''''
516 ||' and ale.import_list_header_id = ail.import_list_header_id ';
517 else
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519
520 end if;
521
522
523
524 ad_ddl.do_ddl(l_applsys_schema,FND_GLOBAL.APPLICATION_SHORT_NAME,ad_ddl.create_view,l_sql_str, l_view_name);
525
526
527
528
529
530 -- Standard check of p_commit.
531 IF FND_API.To_Boolean ( p_commit )
532 THEN
533 COMMIT WORK;
534 END IF;
535
536 -- Success Message
537 -- MMSG
538 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
539 THEN
540 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
541 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Maint_PVT.Create_Source_View', TRUE);
542 FND_MSG_PUB.Add;
543 END IF;
544
545
546 IF (AMS_DEBUG_HIGH_ON) THEN
547 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
548 FND_MESSAGE.Set_Token('ROW','AMS_List_Maint_PVT.Create_Source_View: END', TRUE);
549 FND_MSG_PUB.Add;
550 END IF;
551
552
553 -- Standard call to get message count AND IF count is 1, get message info.
554 FND_MSG_PUB.Count_AND_Get
555 ( p_count => x_msg_count,
556 p_data => x_msg_data,
557 p_encoded => FND_API.G_FALSE
558 );
559
560
561
562 EXCEPTION
563
564 WHEN FND_API.G_EXC_ERROR THEN
565
566 ROLLBACK TO Create_Source_View;
567 x_return_status := FND_API.G_RET_STS_ERROR ;
568 g_sqlerrm := SQLERRM;
569 g_sqlcode := SQLCODE;
570 --dbms_output.put_line('AMS_List_Maint_PVT.Create_Source_View:'||g_sqlerrm||g_sqlcode);
571
572 FND_MSG_PUB.Count_AND_Get
573 ( p_count => x_msg_count,
574 p_data => x_msg_data,
575 p_encoded => FND_API.G_FALSE
576 );
577
578
579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
580
581 ROLLBACK TO Create_Source_View;
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
583
584 g_sqlerrm := SQLERRM;
585 g_sqlcode := SQLCODE;
586 --dbms_output.put_line('AMS_List_Maint_PVT.Create_Source_View:'||g_sqlerrm||g_sqlcode);
587
588 FND_MSG_PUB.Count_AND_Get
589 ( p_count => x_msg_count,
590 p_data => x_msg_data,
591 p_encoded => FND_API.G_FALSE
592 );
593
594 WHEN OTHERS THEN
595
596 ROLLBACK TO Create_Source_View;
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
598
599 g_sqlerrm := SQLERRM;
600 g_sqlcode := SQLCODE;
601 --dbms_output.put_line('AMS_List_Maint_PVT.Create_Source_View:'||g_sqlerrm||g_sqlcode);
602
603 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
604 THEN
605 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
606 END IF;
607
608 FND_MSG_PUB.Count_AND_Get
609 ( p_count => x_msg_count,
610 p_data => x_msg_data,
611 p_encoded => FND_API.G_FALSE
612 );
613
614 End Create_Source_View;
615
616
617
618 ----------------------------------------------------------------------------------
619 -- Procedure
620 -- Copy_List
621 --
622 -- PURPOSE
623 -- 1. Copies a List Header and optionally its actions or existing entries.
624 -- 2. If the list is REPEATING then all existing REPEATED lists are created
625 -- as EXCLUDE actions in the new list.
626
627 -- PARAMETERS
628 -- P_List_Id The List_Header_Id of The List to be Copied.
629 -- P_List_Used_By_ID The Foreign Key to the Entity using the list.
630 -- p_arc_list_used_by The Qualifier code which identifies the type of entity
631 -- which is using the list.
632 -- P_Copy_Option 'A' Create New List Header and Copy Actions.
633 -- 'E' Create New List Header and create one INCLUDE action
634 -- of the copied list name.
635 -- P_Repeat_Option If this is a repeating list then this option specifies
636 -- how to copy the list.
637 -- 'R' create a new list with a generation type of 'REPEAT'.
638 -- 'I' create a new list with a generation type of 'INCREMENTAL',
639 -- additionally create 'EXCLUDE' actions for all previously
640 -- generated children lists of the parent list.
641 --
642 -- NOTES
643 -- 10/27/1999 tdonohoe created
644 -- 06/22/2000 tdonohoe modified cursors to explicitly name columns
645 ---------------------------------------------------------------------
646 Procedure Copy_List (p_api_version IN NUMBER,
647 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
648 p_commit IN VARCHAR2 := FND_API.g_false,
649 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
650
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2,
654
655
656 p_list_id IN NUMBER,
657 p_list_used_by_id IN NUMBER := NULL,
658 p_arc_list_used_by IN VARCHAR2 := NULL,
659 p_list_type IN VARCHAR2 := NULL,
660 p_copy_option IN VARCHAR2 :='A',
661 p_repeat_option IN VARCHAR2 :='R',
662 x_list_id OUT NOCOPY NUMBER,
663 x_listheader_rec OUT NOCOPY AMS_LISTHEADER_PVT.list_header_rec_type) IS
664
665 l_api_name CONSTANT VARCHAR2(30) := 'COPY_LIST';
666 l_api_version CONSTANT NUMBER := 1.0;
667
668 ------------------------------------------------------------------------------------
669 --This Cursor retreves the list header details from the AMS_LIST_HEADERS_ALL table.-
670 ------------------------------------------------------------------------------------
671 Cursor C_ListHeader_Dets(p_list_header_id NUMBER) IS
672 SELECT
673 list_header_id
674 ,last_update_date
675 ,last_updated_by
676 ,creation_date
677 ,created_by
678 ,last_update_login
679 ,object_version_number
680 ,request_id
681 ,program_id
682 ,program_application_id
683 ,program_update_date
684 ,view_application_id
685 ,list_name
686 ,list_used_by_id
687 ,arc_list_used_by
688 ,list_type
689 ,status_code
690 ,status_date
691 ,generation_type
692 ,repeat_exclude_type
693 ,row_selection_type
694 ,owner_user_id
695 ,access_level
696 ,enable_log_flag
697 ,enable_word_replacement_flag
698 ,enable_parallel_dml_flag
699 ,dedupe_during_generation_flag
700 ,generate_control_group_flag
701 ,last_generation_success_flag
702 ,forecasted_start_date
703 ,forecasted_end_date
704 ,actual_end_date
705 ,sent_out_date
706 ,dedupe_start_date
707 ,last_dedupe_date
708 ,last_deduped_by_user_id
709 ,workflow_item_key
710 ,no_of_rows_duplicates
711 ,no_of_rows_min_requested
712 ,no_of_rows_max_requested
713 ,no_of_rows_in_list
714 ,no_of_rows_in_ctrl_group
715 ,no_of_rows_active
716 ,no_of_rows_inactive
717 ,no_of_rows_manually_entered
718 ,no_of_rows_do_not_call
719 ,no_of_rows_do_not_mail
720 ,no_of_rows_random
721 ,org_id
722 ,main_gen_start_time
723 ,main_gen_end_time
724 ,main_random_nth_row_selection
725 ,main_random_pct_row_selection
726 ,ctrl_random_nth_row_selection
727 ,ctrl_random_pct_row_selection
728 ,repeat_source_list_header_id
729 ,result_text
730 ,keywords
731 ,description
732 ,list_priority
733 ,assign_person_id
734 ,list_source
735 ,list_source_type
736 ,list_online_flag
737 ,random_list_id
738 ,enabled_flag
739 ,assigned_to
740 ,query_id
741 ,owner_person_id
742 ,archived_by
743 ,archived_date
744 ,attribute_category
745 ,attribute1
746 ,attribute2
747 ,attribute3
748 ,attribute4
749 ,attribute5
750 ,attribute6
751 ,attribute7
752 ,attribute8
753 ,attribute9
754 ,attribute10
755 ,attribute11
756 ,attribute12
757 ,attribute13
758 ,attribute14
759 ,attribute15
760 ,timezone_id
761 ,user_entered_start_time
762 FROM ams_list_headers_all
763 WHERE list_header_id = p_list_header_id;
764
765
766 ------------------------------------------------------------------------------------
767 --This Cursor retreves all list criteraias from the AMS_LIST_SELECT_ACTIONS table.--
768 ------------------------------------------------------------------------------------
769 Cursor C_ListAction_Dets(p_list_header_id NUMBER)
770 IS SELECT
771 list_select_action_id
772 ,last_update_date
773 ,last_updated_by
774 ,creation_date
775 ,created_by
776 ,last_update_login
777 ,object_version_number
778 ,list_header_id
779 ,order_number
780 ,list_action_type
781 ,incl_object_name
782 ,arc_incl_object_from
783 ,incl_object_id
784 ,incl_object_wb_sheet
785 ,incl_object_wb_owner
786 ,incl_object_cell_code
787 ,rank
788 ,no_of_rows_available
789 ,no_of_rows_requested
790 ,no_of_rows_used
791 ,distribution_pct
792 ,result_text
793 ,description
794 FROM ams_list_select_actions
795 WHERE list_header_id = p_list_header_id
796 ORDER BY order_number;
797
798
799 ---------------------------------------------------------------
800 --used to select all previously generated repeatible lists. --
801 --these lists must be created as "EXCLUDE" actions from the --
802 --currently generated list. this guarantees uniqueness of --
803 --list entries across lists. --
804 ---------------------------------------------------------------
805 CURSOR C_Repeat_Lists(p_source_list_id number,p_current_list_id number)IS
806 SELECT list_header_Id,
807 list_name
808 FROM ams_list_headers_all
809 WHERE repeat_source_list_header_id = p_source_list_id
810 AND list_header_id <> p_current_list_id
811 ORDER BY list_header_id;
812
813 ----------------------------------------------------------------------------
814 --These table records will store a set of list header and list name fields--
815 ----------------------------------------------------------------------------
816 TYPE t_list_header_id is TABLE OF ams_list_headers_all.list_header_id%type;
817 TYPE t_list_name is TABLE OF ams_list_headers_all.list_name%type;
818
819 ----------------------------------------------------------------------------
820 --These variables will store the results of cursor c_repeat_lists. --
821 ----------------------------------------------------------------------------
822 l_repeat_list_header_id t_list_header_id;
823 l_repeat_list_name t_list_name;
824
825 l_list_name ams_list_headers_all.list_name%type;
826 l_list_header_id NUMBER;
827 l_action_id NUMBER;
828
829 ----------------------------------------------------------------------------
830 --The count of generated lists for this REPEAT_LIST_SOURCE_TYPE. --
831 ----------------------------------------------------------------------------
832 l_repeat_list_count NUMBER;
833
834
835 ----------------------------------------------------------------------------------
836 --These Variables store the result status of the call to the create_list_header --
837 --and create_list_actions API procedures --
838 ----------------------------------------------------------------------------------
839 l_return_status VARCHAR2(1);
840 l_msg_count NUMBER;
841 l_msg_data VARCHAR2(2000);
842
843 Begin
844 null;
845 /*
846 -- Standard Start of API savepoint
847 SAVEPOINT Copy_List;
848
849 -- Standard call to check for call compatibility.
850 IF NOT FND_API.Compatible_API_Call ( l_api_version,
851 p_api_version,
852 l_api_name,
853 G_PKG_NAME)
854 THEN
855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
856 END IF;
857
858
859 -- Initialize message list IF p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean( p_init_msg_list ) THEN
861 FND_MSG_PUB.initialize;
862 END IF;
863
864 -- Debug Message
865 IF (AMS_DEBUG_HIGH_ON) THEN
866 THEN
867 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
868 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Maint_PVT.Copy_List: Start', TRUE);
869 FND_MSG_PUB.Add;
870 END IF;
871
872 -- Initialize API return status to success
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 --------------------------------------------------------------------------------
876 --Check that(if specified) the entity to associate the list with is valid. --
877 --------------------------------------------------------------------------------
878 IF(p_list_used_by_id IS NOT NULL and p_arc_list_used_by IS NOT NULL)THEN
879 IF ( Check_List_Association(p_arc_list_used_by,p_list_used_by_id) = FND_API.g_false )THEN
880 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
881 THEN
882 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_USED_BY_ID');
883 FND_MSG_PUB.add;
884 END IF;
885 x_return_status := FND_API.g_ret_sts_error;
886 RETURN;
887 END IF;
888 END IF;--P_ARC_LIST_USED_BY AND P_LIST_USED_BY_ID CHECK
889
890 --------------------------------------------------------------------------------
891 --Check that(if specified) the list type is valid. --
892 --------------------------------------------------------------------------------
893 IF(p_list_type IS NOT NULL)THEN
894 IF AMS_Utility_PVT.check_lookup_exists(
895 p_lookup_type => 'AMS_LIST_TYPE',
896 p_lookup_code => p_list_type) = FND_API.g_false THEN
897
898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
899 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_TYPE');
900 FND_MSG_PUB.add;
901 END IF;
902
903 x_return_status := FND_API.g_ret_sts_error;
904 RETURN;
905 END IF;
906 END IF;
907
908 ------------------------------------
909 --getting the list header details.--
910 ------------------------------------
911 OPEN C_ListHeader_Dets(p_list_id);
912 FETCH C_ListHeader_Dets INTO g_listheader_rec;
913
914 IF(C_ListHeader_Dets%NOTFOUND)THEN
915 CLOSE C_ListHeader_Dets;
916 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
917 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ID_NOT_EXIST');
918 FND_MSG_PUB.Add;
919 END IF;
920 x_return_status := FND_API.G_RET_STS_ERROR;
921 RETURN;
922 END IF;
923 CLOSE C_ListHeader_Dets;
924
925 IF(g_listheader_rec.generation_type = 'INCREMENTAL')THEN
926 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)) THEN
927 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_BAD_COPY_INC');
928 FND_MSG_PUB.Add;
929 END IF;
930 x_return_status := FND_API.G_RET_STS_ERROR;
931 RETURN;
932 END IF;
933
934 -------------------------------------------------------------------------
935 --copying the original list name, as it may be needed if a criteria is --
936 --created to INCLUDE the original list entries. --
937 -------------------------------------------------------------------------
938 l_list_name := g_listheader_rec.list_name;
939
940 g_listheader_rec.list_name := g_listheader_rec.list_name||' '||to_char(sysdate,'DD-MON-YY HH24:MI:SS');
941 g_listheader_rec.status_code :='NEW';
942 g_listheader_rec.status_date := SYSDATE;
943
944 ------------------------------------------------------------------------
945 --copying the source code values into the record type to be created. --
946 ------------------------------------------------------------------------
947 IF(p_list_used_by_id IS NOT NULL and p_arc_list_used_by IS NOT NULL)THEN
948 g_listheader_rec.list_used_by_id := p_list_used_by_id;
949 g_listheader_rec.arc_list_used_by := p_arc_list_used_by;
950 END IF;
951
952 ------------------------------------------------------------------------
953 --copying the list type value into the record type to be created. --
954 ------------------------------------------------------------------------
955 IF(p_list_type IS NOT NULL)THEN
956 g_listheader_rec.list_type := p_list_type;
957 END IF;
958
959 ------------------------------------------------------------------------
960 --If a REPEATIBLE list is copied then the new list must have --
961 --a generation type of INCREMENTAL. --
962 ------------------------------------------------------------------------
963 IF(g_listheader_rec.generation_type='REPEAT')THEN
964 IF(p_repeat_option = 'R')THEN
965 g_listheader_rec.generation_type := 'REPEAT';
966 ELSE
967 g_listheader_rec.generation_type := 'INCREMENTAL';
968 END IF;
969 END IF;
970
971 ------------------------------------------------------------------------
972 --setting the list_header_id to NULL. --
973 ------------------------------------------------------------------------
974 g_listheader_rec.list_header_id := NULL;
975
976
977 ------------------------------------------------------------------------
978 --Creating a new List Header. --
979 ------------------------------------------------------------------------
980 AMS_ListHeader_PVT.Create_ListHeader(
981 p_api_version => 1.0,
982 x_return_status => l_return_status,
983 x_msg_count => l_msg_count,
984 x_msg_data => l_msg_data,
985 p_listheader_rec => g_listheader_rec,
986 x_listheader_id => l_list_header_id);
987
988 IF(l_return_status = FND_API.G_RET_STS_ERROR)THEN
989 Raise FND_API.G_EXC_ERROR;
990 ELSIF(l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
992 END IF;
993
994 x_list_id := l_list_header_id;
995
996 ------------------------------------------------------------------------
997 --Fetching The new list header details into the OUT NOCOPY variable. --
998 ------------------------------------------------------------------------
999 OPEN c_listheader_dets(x_list_id);
1000 FETCH c_listheader_dets INTO x_listheader_rec;
1001 CLOSE c_listheader_dets;
1002
1003 ------------------------------------------------------------------------
1004 --Copy all Actions from the parent list into the new list. --
1005 ------------------------------------------------------------------------
1006 IF(p_copy_option = 'A')THEN
1007 OPEN c_listaction_dets(p_list_id);
1008 LOOP
1009 FETCH c_listaction_dets into g_listaction_rec;
1010 EXIT when c_listaction_dets%NOTFOUND;
1011
1012 g_listaction_rec.list_header_id := l_list_header_id;
1013 g_listaction_rec.list_select_action_id := NULL;
1014
1015 AMS_ListAction_Pvt.Create_ListAction(p_api_version => 1.0,
1016 x_return_status => l_return_status,
1017 x_msg_count => l_msg_count,
1018 x_msg_data => l_msg_data,
1019 p_action_rec => g_listaction_rec,
1020 x_action_id => l_action_id);
1021
1022 IF(l_return_status = FND_API.G_RET_STS_ERROR)THEN
1023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1024 END IF;
1025
1026 END LOOP;
1027 CLOSE c_listaction_dets;
1028 ------------------------------------------------------------------------
1029 --INCLUDE The parent list as a criteria in the new list. --
1030 ------------------------------------------------------------------------
1031 ELSIF(p_copy_option = 'E')THEN
1032 g_listaction_rec.incl_object_name := l_list_name;
1033 g_listaction_rec.arc_incl_object_from := 'LIST';
1034 g_listaction_rec.list_action_type := 'INCLUDE';
1035 g_listaction_rec.incl_object_id := p_list_id;
1036 g_listaction_rec.list_header_id := l_list_header_id;
1037 g_listaction_rec.order_number := 1;
1038 g_listaction_rec.rank := 1;
1039
1040
1041 AMS_ListAction_Pvt.Create_ListAction(p_api_version => 1.0,
1042 x_return_status => l_return_status,
1043 x_msg_count => l_msg_count,
1044 x_msg_data => l_msg_data,
1045 p_action_rec => g_listaction_rec,
1046 x_action_id => l_action_id);
1047
1048 IF(l_return_status = FND_API.G_RET_STS_ERROR)THEN
1049 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1050 END IF;
1051 END IF;--P_COPY_OPTION CHECK.
1052
1053 -----------------------------------------------------------------------------------
1054 --If this is an INCREMENTAL list then we must create EXCLUDE actions for all the --
1055 --previously generated REPEATING\INCREMENTAL lists for the parent list. --
1056 -----------------------------------------------------------------------------------
1057 IF(g_listheader_rec.generation_type = 'INCREMENTAL')THEN
1058 ------------------------------------------------------------------------
1059 --getting the number of times a repeated list header has been created.--
1060 ------------------------------------------------------------------------
1061 SELECT COUNT(*)
1062 INTO l_repeat_list_count
1063 FROM ams_list_headers_all
1064 WHERE repeat_source_list_Header_id = p_list_id;
1065
1066 IF(l_repeat_list_count >0)THEN
1067 ------------------------------------------------------------------------
1068 --Fetch the existing set of already generated Repeated Lists. --
1069 ------------------------------------------------------------------------
1070 OPEN c_repeat_lists(p_list_id,l_list_header_id);
1071 FETCH c_repeat_lists BULK COLLECT INTO l_repeat_list_header_id,l_repeat_list_name;
1072 CLOSE c_repeat_lists;
1073
1074 -----------------------------------------------------------------------------------
1075 --Creating Exclude Actions for the set of repeated lists generated so far. --
1076 --this guarantees the latest list never has entries which have been targeted --
1077 --in a previous list. --
1078 -----------------------------------------------------------------------------------
1079 FOR I in l_repeat_list_header_id.first .. l_repeat_list_header_id.last LOOP
1080 g_listaction_rec.list_header_id := l_list_header_id;
1081 g_listaction_rec.incl_object_name := l_repeat_list_name(i);
1082 g_listaction_rec.arc_incl_object_from := 'LIST';
1083 g_listaction_rec.list_action_type := 'EXCLUDE';
1084 g_listaction_rec.incl_object_id := l_repeat_list_header_id(i);
1085 g_listaction_rec.order_number := g_listaction_rec.order_number + 1;
1086 g_listaction_rec.rank := g_listaction_rec.rank + 1;
1087
1088 AMS_ListAction_Pvt.Create_ListAction( p_api_version => 1.0,
1089 x_return_status => l_return_status,
1090 x_msg_count => l_msg_count,
1091 x_msg_data => l_msg_data,
1092 p_action_rec => g_listaction_rec,
1093 x_action_id => l_action_id);
1094
1095 IF(l_return_status = FND_API.G_RET_STS_ERROR)THEN
1096 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098 END LOOP;
1099 END IF; --REPEAT LIST COUNT CHECK;
1100 END IF; --REPEAT LIST CHECK.
1101
1102 -- Standard check of p_commit.
1103 IF FND_API.To_Boolean ( p_commit )
1104 THEN
1105 COMMIT WORK;
1106 END IF;
1107
1108 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1109 THEN
1110 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1111 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Maint_PVT.Copy_List', TRUE);
1112 FND_MSG_PUB.Add;
1113 END IF;
1114
1115
1116 IF (AMS_DEBUG_HIGH_ON) THEN
1117 THEN
1118 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1119 FND_MESSAGE.Set_Token('ROW','AMS_List_Maint_PVT.Copy_List: END', TRUE);
1120 FND_MSG_PUB.Add;
1121 END IF;
1122
1123 -- Standard call to get message count AND IF count is 1, get message info.
1124 FND_MSG_PUB.Count_AND_Get
1125 ( p_count => x_msg_count,
1126 p_data => x_msg_data,
1127 p_encoded => FND_API.G_FALSE);
1128
1129
1130 EXCEPTION
1131 WHEN FND_API.G_EXC_ERROR THEN
1132
1133 ROLLBACK TO Copy_List;
1134 x_return_status := FND_API.G_RET_STS_ERROR ;
1135 g_sqlerrm := SQLERRM;
1136 g_sqlcode := SQLCODE;
1137 --dbms_output.put_line('AMS_List_Maint_PVT.Copy_List:'||g_sqlerrm||g_sqlcode);
1138
1139 FND_MSG_PUB.Count_AND_Get
1140 ( p_count => x_msg_count,
1141 p_data => x_msg_data,
1142 p_encoded => FND_API.G_FALSE
1143 );
1144
1145
1146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1147
1148 ROLLBACK TO Copy_List;
1149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1150
1151 g_sqlerrm := SQLERRM;
1152 g_sqlcode := SQLCODE;
1153 --dbms_output.put_line('AMS_List_Maint_PVT.Copy_List:'||g_sqlerrm||g_sqlcode);
1154
1155 FND_MSG_PUB.Count_AND_Get
1156 ( p_count => x_msg_count,
1157 p_data => x_msg_data,
1158 p_encoded => FND_API.G_FALSE
1159 );
1160
1161 WHEN OTHERS THEN
1162
1163 ROLLBACK TO Copy_List;
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1165
1166 g_sqlerrm := SQLERRM;
1167 g_sqlcode := SQLCODE;
1168 --dbms_output.put_line('AMS_List_Maint_PVT.Copy_List:'||g_sqlerrm||g_sqlcode);
1169
1170 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1171 THEN
1172 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1173 END IF;
1174
1175 FND_MSG_PUB.Count_AND_Get
1176 ( p_count => x_msg_count,
1177 p_data => x_msg_data,
1178 p_encoded => FND_API.G_FALSE
1179 );
1180
1181
1182 */
1183 End Copy_List;
1184
1185
1186 ---------------------------------------------------------------------
1187 -- PROCEDURE
1188 -- Get_PinCodeData_Rec
1189 --
1190 -- PURPOSE
1191 -- Gets customer record for a given pincode.
1192 --
1193 -- PARAMETERS
1194 -- x_listentry_rec: the record representing AMS_LIST_ENTRIES.
1195 -- p_pincode: the pincode.
1196 --
1197 -- NOTES
1198 -- 1. since pincode is unique only one record will be returned.
1199 -- 2. Raise exception incase of invalid pincode.
1200 -- created vbhandar 2/16/2000
1201 ---------------------------------------------------------------------
1202 PROCEDURE Get_PinCodeData_Rec (
1203 p_api_version IN NUMBER,
1204 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1205 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1206
1207 x_return_status OUT NOCOPY VARCHAR2,
1208 x_msg_count OUT NOCOPY NUMBER,
1209 x_msg_data OUT NOCOPY VARCHAR2,
1210
1211 p_pincode IN VARCHAR2,
1212 x_listentry_rec OUT NOCOPY ListEntryType_Rec_Type
1213 )
1214 IS
1215 l_api_version CONSTANT NUMBER := 1.0;
1216 l_api_name CONSTANT VARCHAR2(30) := 'Get_PinCodeData_Rec';
1217 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'||l_api_name;
1218
1219 --Retrieve all necessary List entry parameters .
1220 Cursor C_ListEntry_Dets IS Select LIST_ENTRY_ID,LIST_HEADER_ID,
1221 SOURCE_CODE,ARC_LIST_USED_BY_SOURCE,
1222 SOURCE_CODE_FOR_ID,PIN_CODE,
1223 LIST_ENTRY_SOURCE_SYSTEM_ID,
1224 LIST_ENTRY_SOURCE_SYSTEM_TYPE,
1225 VIEW_APPLICATION_ID,
1226 CELL_CODE,CAMPAIGN_ID,
1227 CHANNEL_SCHEDULE_ID,
1228 EVENT_OFFER_ID,SUFFIX,
1229 FIRST_NAME,LAST_NAME,
1230 CUSTOMER_NAME,TITLE,
1231 ADDRESS_LINE1,ADDRESS_LINE2,
1232 CITY,STATE,ZIPCODE,COUNTRY,FAX,PHONE,EMAIL_ADDRESS
1233 From ams_list_entries
1234 Where ams_list_entries.pin_code = p_pincode;
1235
1236 BEGIN
1237 --------------------- initialize -----------------------
1238 SAVEPOINT Get_PinCodeData_Rec;
1239
1240 IF (AMS_DEBUG_HIGH_ON) THEN
1241
1242
1243
1244 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1245
1246 END IF;
1247
1248 IF FND_API.to_boolean (p_init_msg_list) THEN
1249 FND_MSG_PUB.initialize;
1250 END IF;
1251
1252 IF NOT FND_API.compatible_api_call (
1253 l_api_version,
1254 p_api_version,
1255 l_api_name,
1256 g_pkg_name
1257 ) THEN
1258 RAISE FND_API.g_exc_unexpected_error;
1259 END IF;
1260
1261 x_return_status := FND_API.g_ret_sts_success;
1262
1263 ------------------------ select ------------------------
1264 OPEN C_ListEntry_Dets;
1265 FETCH C_ListEntry_Dets INTO x_listentry_rec;
1266 IF C_ListEntry_Dets%NOTFOUND THEN
1267 CLOSE C_ListEntry_Dets;
1268 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1269 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1270 FND_MSG_PUB.add;
1271 END IF;
1272 RAISE FND_API.g_exc_error;
1273 END IF;
1274 CLOSE C_ListEntry_Dets;
1275
1276 END Get_PinCodeData_Rec;
1277
1278
1279 END AMS_LIST_MAINT_PVT;