[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_SRC_TYPES_PVT
Source
1 PACKAGE BODY AMS_LIST_SRC_TYPES_PVT AS
2 /* $Header: amsvstvb.pls 120.3 2005/06/07 11:53:44 appldev $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_LIST_SRC_TYPES_PVT
7 --
8 -- PURPOSE
9 -- The purpose of this package is to creat and update the
10 -- views for Master list source type.
11 -- The following cases are handled.
12 -- 1. Create Master view for new source type.
13 -- 2. Update the Master view for new source type.
14 -- 3. Create/Update the Master view in case a new
15 -- Sub source type is added or deleted.
16 -- 4. Create/Update ALL the Master view in case a new
17 -- item is added/deleted from the Sub source type.
18 --
19 --
20 -- PROCEDURES
21 --
22 --
23 -- PARAMETERS
24 -- INPUT
25 --
26 --
27 -- OUTPUT
28 --
29 -- HISTORY
30 -- 19-Apr-2001 usingh Created.
31 -- ---------------------------------------------------------
32
33
34 -- This procedure create or updates the Master source type view.
35
36 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
37 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
38 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
39
40 AMS_LOG_PROCEDURE_ON boolean := AMS_UTILITY_PVT.logging_enabled(FND_LOG.LEVEL_PROCEDURE);
41 AMS_LOG_EXCEPTION_ON boolean := AMS_UTILITY_PVT.logging_enabled(FND_LOG.LEVEL_exception);
42 AMS_LOG_STATEMENT_ON boolean := AMS_UTILITY_PVT.logging_enabled(FND_LOG.LEVEL_STATEMENT);
43
44 AMS_LOG_PROCEDURE constant number := FND_LOG.LEVEL_PROCEDURE;
45 AMS_LOG_EXCEPTION constant Number := FND_LOG.LEVEL_EXCEPTION;
46 AMS_LOG_STATEMENT constant Number := FND_LOG.LEVEL_STATEMENT;
47
48 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_LIST_SRC_TYPES_PVT';
49 G_module_name constant varchar2(100):='oracle.apps.ams.plsql.'||g_pkg_name;
50
51 PROCEDURE master_source_type_view(
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
54 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 p_list_source_type_id IN NUMBER
59 ) IS
60
61 TYPE I_LIST_SOURCE_FIELD_IDLIST IS TABLE OF AMS_LIST_SRC_FIELDS.LIST_SOURCE_FIELD_ID%TYPE
62 INDEX BY BINARY_INTEGER;
63
64 TYPE I_COLUMN_NAME_LIST IS TABLE OF VARCHAR2(30)
65 INDEX BY BINARY_INTEGER;
66
67 TYPE I_COLUMN_HEADING_LIST IS TABLE OF VARCHAR2(120)
68 INDEX BY BINARY_INTEGER;
69
70 I_COLUMN_NAME I_COLUMN_NAME_LIST;
71 I_COLUMN_HEADING I_COLUMN_HEADING_LIST;
72 I_LIST_SOURCE_FIELD_ID I_LIST_SOURCE_FIELD_IDLIST;
73 i_sql_string VARCHAR2(32767);
74 i_view_name VARCHAR2(200);
75 i_list_source_name VARCHAR2(120);
76 i_dup_source_name VARCHAR2(120) := 'A';
77 i_rows NUMBER := 1000;
78 i_counter NUMBER := 0;
79 i_comma VARCHAR2(1) := ',';
80 i_exists VARCHAR2(1);
81 i_number NUMBER := 0;
82 i_mst_source VARCHAR2(120);
83 i_st_source VARCHAR2(120);
84 i_mst_exists VARCHAR2(1);
85 i_st_counter NUMBER := 0;
86 i_le_ss_type VARCHAR2(120);
87 L_SUB_SOURCE_TYPE_ID NUMBER;
88 L_FAX_SUB_SOURCE_TYPE_ID NUMBER;
89 l_total_sub_types NUMBER;
90 l_count_sub_type NUMBER;
91 l_no_of_chunks NUMBER;
92
93
94 cursor c_list_src_type_name is
95 SELECT SUBSTR(REPLACE(source_type_code,' ','_'),1,20), source_type_code
96 FROM ams_list_src_types --ams_list_src_types_vl
97 WHERE list_source_type_id = p_list_source_type_id;
98
99
100 cursor c_master is
101 SELECT list_source_field_id, field_column_name, substr(replace(source_column_name,' ','_'),1,26)
102 FROM ams_list_src_fields --ams_list_src_fields_vl
103 WHERE list_source_type_id = p_list_source_type_id
104 AND field_column_name <> 'CUSTOMER_NAME'
105 AND upper(source_column_name) not in ('PARTY_ID','PARTY_NAME')
106 AND nvl(enabled_flag,'Y') = 'Y';
107
108 cursor c_all_sub_types is
109 SELECT distinct asa.sub_source_type_id
110 FROM ams_list_src_fields asf,
111 ams_list_src_type_assocs asa
112 WHERE asa.master_source_type_id = p_list_source_type_id
113 AND asa.sub_source_type_id =asf.list_source_type_id
114 AND asf.field_column_name <> 'CUSTOMER_NAME'
115 AND nvl(asf.enabled_flag,'Y') = 'Y'
116 AND nvl(asa.enabled_flag,'Y') = 'Y'
117 ORDER BY asa.SUB_SOURCE_TYPE_ID;
118 /* --SQL ID:11755972 Fix -- musman
119 SELECT aso.SUB_SOURCE_TYPE_ID
120 FROM ams_list_src_type_assocs aso
121 WHERE aso.master_source_type_id = p_list_source_type_id
122 AND nvl(aso.enabled_flag,'Y') = 'Y' -- bug:4055791:musman checking for the enabled flag of the associations
123 and aso.SUB_SOURCE_TYPE_ID in (
124 SELECT distinct asa.sub_source_type_id
125 FROM ams_list_src_fields_vl asf,
126 ams_list_src_type_assocs asa,
127 ams_list_src_types_vl ast
128 WHERE asa.master_source_type_id = aso.master_source_type_id
129 AND asa.sub_source_type_id = aso.SUB_SOURCE_TYPE_ID
130 AND asa.sub_source_type_id = asf.list_source_type_id
131 AND asa.sub_source_type_id = ast.list_source_type_id
132 AND asf.field_column_name <> 'CUSTOMER_NAME'
133 AND nvl(asa.enabled_flag,'Y') = 'Y' -- bug:4055791:musman checking for the enabled flag of the associations
134 AND nvl(asf.enabled_flag,'Y') = 'Y')
135 ORDER BY aso.SUB_SOURCE_TYPE_ID;
136 */
137
138
139 cursor c_total_sub_types is
140 SELECT count(distinct asa.sub_source_type_id)
141 FROM ams_list_src_fields asf
142 ,ams_list_src_type_assocs asa
143 WHERE asa.master_source_type_id = p_list_source_type_id
144 AND asa.sub_source_type_id = asf.list_source_type_id
145 AND asf.field_column_name <> 'CUSTOMER_NAME'
146 AND nvl(asf.enabled_flag,'Y') = 'Y'
147 AND nvl(asa.enabled_flag,'Y') = 'Y';
148
149 /* SQL ID: 11755988 Fix --musman
150 SELECT count(*) FROM ams_list_src_type_assocs aso
151 WHERE aso.master_source_type_id = p_list_source_type_id
152 AND nvl(aso.enabled_flag,'Y') = 'Y' -- bug:4055791:musman checking for the enabled flag of the associations
153 and aso.SUB_SOURCE_TYPE_ID in (
154 SELECT distinct asa.sub_source_type_id
155 FROM ams_list_src_fields_vl asf,
156 ams_list_src_type_assocs asa,
157 ams_list_src_types_vl ast
158 WHERE asa.master_source_type_id = aso.master_source_type_id
159 AND asa.sub_source_type_id = aso.SUB_SOURCE_TYPE_ID
160 AND asa.sub_source_type_id = asf.list_source_type_id
161 AND asa.sub_source_type_id = ast.list_source_type_id
162 AND nvl(asa.enabled_flag,'Y') = 'Y' -- bug:4055791:musman checking for the enabled flag of the associations
163 AND asf.field_column_name <> 'CUSTOMER_NAME'
164 AND nvl(asf.enabled_flag,'Y') = 'Y');
165 */
166
167
168 cursor c_fax_sub_types is
169 SELECT list_source_type_id FROM ams_list_src_types_vl WHERE source_type_code = 'FAX';
170
171 cursor c_sub_type is
172 SELECT asf.list_source_field_id, asf.field_column_name, substr(replace(asf.source_column_name,' ','_'),1,26)||'_S'||to_char(i_number)
173 FROM ams_list_src_fields asf,
174 ams_list_src_type_assocs asa
175 WHERE asa.master_source_type_id = p_list_source_type_id
176 AND asa.sub_source_type_id = L_SUB_SOURCE_TYPE_ID
177 AND asa.sub_source_type_id = asf.list_source_type_id
178 AND asf.field_column_name <> 'CUSTOMER_NAME'
179 AND nvl(asa.enabled_flag,'Y') = 'Y'
180 AND nvl(asf.enabled_flag,'Y') = 'Y';
181 /* SQL ID: 11756019 Fix
182 SELECT asf.list_source_field_id, asf.field_column_name, substr(replace(asf.source_column_name,' ','_'),1,26)||'_S'||to_char(i_number)
183 FROM ams_list_src_fields_vl asf,
184 ams_list_src_type_assocs asa,
185 ams_list_src_types_vl ast
186 WHERE asa.master_source_type_id = p_list_source_type_id
187 AND asa.sub_source_type_id = L_SUB_SOURCE_TYPE_ID
188 AND asa.sub_source_type_id = asf.list_source_type_id
189 AND asa.sub_source_type_id = ast.list_source_type_id
190 AND asf.field_column_name <> 'CUSTOMER_NAME'
191 AND nvl(asa.enabled_flag,'Y') = 'Y' --bug:4055791:musman checking for the enabled flag of the associations
192 AND nvl(asf.enabled_flag,'Y') = 'Y';
193 -- ORDER BY asf.source_column_name;
194 */
195
196 cursor c_count_sub_type is
197 SELECT count(*)
198 FROM ams_list_src_fields asf,
199 ams_list_src_type_assocs asa
200 WHERE asa.master_source_type_id = p_list_source_type_id
201 AND asa.sub_source_type_id = L_SUB_SOURCE_TYPE_ID
202 AND asa.sub_source_type_id = asf.list_source_type_id
203 AND asf.field_column_name <> 'CUSTOMER_NAME'
204 AND nvl(asf.enabled_flag,'Y') = 'Y'
205 AND nvl(asa.enabled_flag,'Y') = 'Y';
206 /* SQL ID: 11756034 fix: musman
207 SELECT count(*)
208 FROM ams_list_src_fields_vl asf,
209 ams_list_src_type_assocs asa,
210 ams_list_src_types_vl ast
211 WHERE asa.master_source_type_id = p_list_source_type_id
212 AND asa.sub_source_type_id = L_SUB_SOURCE_TYPE_ID
213 AND asa.sub_source_type_id = asf.list_source_type_id
214 AND asa.sub_source_type_id = ast.list_source_type_id
215 AND asf.field_column_name <> 'CUSTOMER_NAME'
216 AND nvl(asf.enabled_flag,'Y') = 'Y'
217 AND nvl(asa.enabled_flag,'Y') = 'Y'; --bug:4055791:musman checking for the enabled flag of the associations
218 */
219
220 /* -- looks like this cursor has not been used : musman
221 cursor c_dup_st is
222 SELECT COUNT(*)
223 FROM ams_list_src_fields_vl asf,
224 ams_list_src_type_assocs asa,
225 ams_list_src_types_vl ast
226 WHERE asa.master_source_type_id = p_list_source_type_id
227 AND asa.sub_source_type_id = asf.list_source_type_id
228 AND asa.sub_source_type_id = ast.list_source_type_id
229 AND NVL(asf.enabled_flag,'Y') = 'Y'
230 AND SUBSTR(asf.source_column_name,1,28) = i_st_source;
231 */
232
233 cursor c_master_exists is
234 SELECT 'Y'
235 FROM ams_list_src_fields --ams_list_src_fields_vl
236 WHERE list_source_type_id = p_list_source_type_id
237 AND field_column_name <> 'CUSTOMER_NAME'
238 AND upper(source_column_name) not in ('PARTY_ID','PARTY_NAME')
239 AND nvl(enabled_flag,'Y') = 'Y'
240 AND ROWNUM < 2;
241
242 cursor c_sub_type_exists is
243 SELECT 'Y'
244 FROM ams_list_src_fields asf, --ams_list_src_fields_vl asf,
245 ams_list_src_type_assocs asa
246 WHERE asa.master_source_type_id = p_list_source_type_id
247 AND asa.sub_source_type_id = asf.list_source_type_id
248 AND NVL(asf.enabled_flag,'Y') = 'Y'
249 AND nvl(asa.enabled_flag,'Y') = 'Y' --bug:4055791:musman checking for the enabled flag of the associations
250 AND ROWNUM < 2;
251
252 cursor c_mst_exist is
253 SELECT 'Y'
254 FROM ams_list_src_fields_vl
255 WHERE list_source_type_id = p_list_source_type_id
256 AND nvl(enabled_flag,'Y') = 'Y'
257 AND substr(source_column_name,1,28) = i_mst_source ;
258
259
260 l_api_name CONSTANT VARCHAR2(30) := 'master_source_type_view';
261 l_api_version CONSTANT NUMBER := 1.0;
262 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
263
264 -- Status Local Variables
265 l_return_status VARCHAR2(1); -- Return value from procedures
266 l_msg_count NUMBER ;
267 l_msg_data VARCHAR2(2000);
268
269
270 BEGIN
271
272 IF NOT FND_API.Compatible_API_Call ( l_api_version,
273 p_api_version,
274 l_api_name,
275 G_PKG_NAME)
276 THEN
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END IF;
279
280
281 -- Initialize message list IF p_init_msg_list is set to TRUE.
282 IF FND_API.to_Boolean( p_init_msg_list ) THEN
283 FND_MSG_PUB.initialize;
284 END IF;
285
286
287 -- Debug Message
288
289 IF (AMS_LOG_PROCEDURE_ON) THEN
290 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
291 END IF;
292
293 IF (AMS_DEBUG_HIGH_ON) THEN
294 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
295 FND_MESSAGE.Set_Token('ROW', l_api_name||': Start', TRUE);
296 FND_MSG_PUB.Add;
297 END IF;
298
299 -- Initialize API return status to success
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 IF (AMS_LOG_STATEMENT_ON) THEN
303 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name,'Generating the tar views for :'||p_list_source_type_id);
304 END IF;
305
306 OPEN c_list_src_type_name;
307 FETCH c_list_src_type_name into i_list_source_name, i_le_ss_type;
308 CLOSE c_list_src_type_name;
309
310 i_view_name := 'AMS_TAR_'||i_list_source_name||'_V';
311 i_sql_string := ' CREATE OR REPLACE VIEW '||i_view_name||' AS SELECT ';
312
313 i_sql_string := i_sql_string||'LIST_ENTRY_ID LIST_ENTRY_ID , ';
314 i_sql_string := i_sql_string||'LIST_HEADER_ID LIST_HEADER_ID , ';
315 i_sql_string := i_sql_string||'LIST_SELECT_ACTION_ID LIST_SELECT_ACTION_ID , ';
316 i_sql_string := i_sql_string||'SOURCE_CODE_FOR_ID SOURCE_CODE_FOR_ID , ';
317 i_sql_string := i_sql_string||'LIST_ENTRY_SOURCE_SYSTEM_ID LIST_ENTRY_SOURCE_SYSTEM_ID , ';
318 i_sql_string := i_sql_string||'PARTY_ID PARTY_ID , ';
319 i_sql_string := i_sql_string||'CUSTOMER_NAME PARTY_NAME , ';
320 i_sql_string := i_sql_string||'CURR_CP_TIME_ZONE CURR_CP_TIME_ZONE , ';
321 i_sql_string := i_sql_string||'CURR_CP_ID CURR_CP_ID , ';
322 i_sql_string := i_sql_string||'CURR_CP_COUNTRY_CODE CURR_CP_COUNTRY_CODE , ';
323 i_sql_string := i_sql_string||'CURR_CP_INDEX CURR_CP_INDEX , ';
324 i_sql_string := i_sql_string||'CURR_CP_AREA_CODE CURR_CP_AREA_CODE , ';
325 i_sql_string := i_sql_string||'CURR_CP_PHONE_NUMBER CURR_CP_PHONE_NUMBER , ';
326 i_sql_string := i_sql_string||'CURR_CP_RAW_PHONE_NUMBER CURR_CP_RAW_PHONE_NUMBER , ';
327 i_sql_string := i_sql_string||'NEXT_CALL_TIME NEXT_CALL_TIME , ';
328 i_sql_string := i_sql_string||'DO_NOT_USE_FLAG DO_NOT_USE_FLAG , ';
329 i_sql_string := i_sql_string||'CALLBACK_FLAG CALLBACK_FLAG , ';
330 i_sql_string := i_sql_string||'RECORD_OUT_FLAG RECORD_OUT_FLAG , ';
331 i_sql_string := i_sql_string||'ENABLED_FLAG ENABLED_FLAG , ';
332 i_sql_string := i_sql_string||'NEWLY_UPDATED_flag NEWLY_UPDATED_flag , ';
333 i_sql_string := i_sql_string||'PIN_CODE PIN_CODE , ';
334 i_sql_string := i_sql_string||'CURR_CP_TIME_ZONE_AUX CURR_CP_TIME_ZONE_AUX , ';
335 i_sql_string := i_sql_string||'DO_NOT_USE_REASON DO_NOT_USE_REASON , ';
336 i_sql_string := i_sql_string||'RECORD_RELEASE_TIME RECORD_RELEASE_TIME , ';
337 i_sql_string := i_sql_string||'SOURCE_CODE SOURCE_CODE ';
338
339 i_exists := null;
343 if i_exists = 'Y' then
340 OPEN c_master_exists;
341 FETCH c_master_exists into i_exists;
342 CLOSE c_master_exists;
344 i_sql_string := i_sql_string||' , ';
345 OPEN c_master;
346 LOOP
347 FETCH c_master BULK COLLECT into I_LIST_SOURCE_FIELD_ID, I_COLUMN_NAME, I_COLUMN_HEADING LIMIT i_rows;
348 EXIT when c_master%notfound;
349 END LOOP;
350 CLOSE c_master;
351
352 FOR i IN I_LIST_SOURCE_FIELD_ID.FIRST..I_LIST_SOURCE_FIELD_ID.LAST
353 LOOP
354 i_counter := i_counter + 1;
355 if I_LIST_SOURCE_FIELD_ID.LAST = i_counter then
356 i_comma := ' ';
357 end if;
358 if I_COLUMN_NAME(i) <> 'PARTY_ID' then
359 i_sql_string := i_sql_string||I_COLUMN_NAME(i)||' '||I_COLUMN_HEADING(i)||i_comma;
360 end if;
361 END LOOP;
362 end if;
363
364
365 -- ********************************88
366 i_exists := null;
367 OPEN c_sub_type_exists;
368 FETCH c_sub_type_exists into i_exists;
369 CLOSE c_sub_type_exists;
370 if i_exists = 'Y' then
371 i_sql_string := i_sql_string||' ,';
372 I_LIST_SOURCE_FIELD_ID.DELETE;
373 I_COLUMN_NAME.DELETE;
374 I_COLUMN_HEADING.DELETE;
375 i_counter := 0;
376 i_comma := ',';
377
378 open c_fax_sub_types;
379 fetch c_fax_sub_types into L_FAX_SUB_SOURCE_TYPE_ID;
380 close c_fax_sub_types;
381
382 open c_total_sub_types;
383 fetch c_total_sub_types into l_total_sub_types;
384 close c_total_sub_types;
385
386 open c_all_sub_types;
387 LOOP
388 fetch c_all_sub_types into L_SUB_SOURCE_TYPE_ID;
389 exit when c_all_sub_types%notfound;
390 i_number := i_number + 1;
391
392 IF (AMS_LOG_STATEMENT_ON) THEN
393 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name,'L_SUB_SOURCE_TYPE_ID:'||L_SUB_SOURCE_TYPE_ID);
394 END IF;
395
396
397 OPEN c_sub_type;
398 LOOP
399 FETCH c_sub_type BULK COLLECT into I_LIST_SOURCE_FIELD_ID, I_COLUMN_NAME, I_COLUMN_HEADING LIMIT i_rows;
400 EXIT when c_sub_type%notfound;
401 END LOOP;
402 CLOSE c_sub_type;
403
404 i_counter := 0;
405 FOR i IN I_LIST_SOURCE_FIELD_ID.FIRST..I_LIST_SOURCE_FIELD_ID.LAST
406 LOOP
407 i_counter := i_counter + 1;
408 if i_number = l_total_sub_types then
409 open c_count_sub_type;
410 fetch c_count_sub_type into l_count_sub_type;
411 close c_count_sub_type;
412 if l_count_sub_type = i_counter then
413 i_comma := ' ';
414 end if;
415 end if;
416 if L_FAX_SUB_SOURCE_TYPE_ID = L_SUB_SOURCE_TYPE_ID then
417 if I_COLUMN_HEADING(i) = 'PHONE_NUMBER'||'_S'||to_char(i_number) then
418 I_COLUMN_HEADING(i) := 'FAX_'||I_COLUMN_HEADING(i);
419 end if;
420 if I_COLUMN_HEADING(i) = 'PHONE_AREA_CODE'||'_S'||to_char(i_number) then
421 I_COLUMN_HEADING(i) := 'FAX_'||I_COLUMN_HEADING(i);
422 end if;
423 if I_COLUMN_HEADING(i) = 'PHONE_COUNTRY_CODE'||'_S'||to_char(i_number) then
424 I_COLUMN_HEADING(i) := 'FAX_'||I_COLUMN_HEADING(i);
425 end if;
426 end if;
427 i_sql_string := i_sql_string||I_COLUMN_NAME(i)||' '||I_COLUMN_HEADING(i)||i_comma;
428 END LOOP;
429
430 END LOOP; -- for c_all_sub_types cursor
431 close c_all_sub_types;
432 end if; -- if i_exists = 'Y' then
433
434 i_sql_string := i_sql_string||' FROM AMS_LIST_ENTRIES WHERE LIST_ENTRY_SOURCE_SYSTEM_TYPE = '||''''||i_le_ss_type||'''';
435 IF (AMS_LOG_STATEMENT_ON) THEN
436 l_no_of_chunks := ceil(length(i_sql_string)/2000 );
437 for i in 1 ..l_no_of_chunks
438 loop
439 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name||'.'||l_api_name,substr(i_sql_string,(2000*i) - 1999,2000));
440 end loop;
441 END IF;
442 EXECUTE IMMEDIATE i_sql_string;
443 /*
444 exception
445 when others then
446 raise;
447 */
448 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
449 THEN
450 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
451 FND_MESSAGE.Set_Token('ROW', l_api_name, TRUE);
452 FND_MSG_PUB.Add;
453 END IF;
454
455
456 IF (AMS_DEBUG_HIGH_ON) THEN
457 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
458 FND_MESSAGE.Set_Token('ROW', l_api_name||': END', TRUE);
459 FND_MSG_PUB.Add;
460 END IF;
461
462 IF (AMS_LOG_PROCEDURE_ON) THEN
463 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
464 END IF;
465
466 -- Standard call to get message count AND IF count is 1, get message info.
467 FND_MSG_PUB.Count_AND_Get
468 ( p_count => x_msg_count,
469 p_data => x_msg_data,
470 p_encoded => FND_API.G_FALSE
471 );
472
473 IF (AMS_LOG_PROCEDURE_ON) THEN
474 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,'$$$$$$$$$: END :$$$$$$');
475 END IF;
476
477 EXCEPTION
478
479 WHEN FND_API.G_EXC_ERROR THEN
480 IF (AMS_LOG_PROCEDURE_ON) THEN
481 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' execution ERROR has been raised ');
482 END IF;
483
484 x_return_status := FND_API.G_RET_STS_ERROR ;
485 FND_MSG_PUB.Count_AND_Get
486 ( p_count => x_msg_count,
490
487 p_data => x_msg_data,
488 p_encoded => FND_API.G_FALSE
489 );
491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
492 IF (AMS_LOG_PROCEDURE_ON) THEN
493 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' Unexpected error has been raised ');
494 END IF;
495
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
497 FND_MSG_PUB.Count_AND_Get
498 ( p_count => x_msg_count,
499 p_data => x_msg_data,
500 p_encoded => FND_API.G_FALSE
501 );
502
503
504 WHEN OTHERS THEN
505
506
507 IF (AMS_LOG_PROCEDURE_ON) THEN
508 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' In Others exception handling ');
509 END IF;
510
511 IF (AMS_LOG_EXCEPTION_ON) THEN
512 AMS_UTILITY_PVT.debug_message(AMS_LOG_EXCEPTION,g_module_name||'.'||l_api_name,'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
513 END IF;
514
515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
516 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
517 THEN
518 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
519 END IF;
520
521 FND_MSG_PUB.Count_AND_Get
522 ( p_count => x_msg_count,
523 p_data => x_msg_data,
524 p_encoded => FND_API.G_FALSE
525 );
526
527 END master_source_type_view;
528
529 PROCEDURE update_all_master_views(
530 p_api_version IN NUMBER,
531 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
532 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
533 x_return_status OUT NOCOPY VARCHAR2,
534 x_msg_count OUT NOCOPY NUMBER,
535 x_msg_data OUT NOCOPY VARCHAR2,
536 p_list_source_type_id IN NUMBER
537 ) IS
538
539 i_master_source_type_id NUMBER;
540
541 cursor c_all_master is
542 SELECT master_source_type_id
543 FROM ams_list_src_type_assocs
544 WHERE sub_source_type_id = p_list_source_type_id
545 -- AND enabled_flag = 'Y' --we have to re-generate the parent after assoc is disabled
546 AND master_source_type_id in (14,30) ; --- only if the master is person or organization contact
547
548 l_api_name CONSTANT VARCHAR2(30) := 'master_source_type_view';
549 l_api_version CONSTANT NUMBER := 1.0;
550 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
551
552 -- Status Local Variables
553 l_return_status VARCHAR2(1); -- Return value from procedures
554 l_msg_count NUMBER ;
555 l_msg_data VARCHAR2(2000);
556
557
558 begin
559 IF NOT FND_API.Compatible_API_Call ( l_api_version,
560 p_api_version,
561 l_api_name,
562 G_PKG_NAME)
563 THEN
564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
565 END IF;
566
567
568 -- Initialize message list IF p_init_msg_list is set to TRUE.
569 IF FND_API.to_Boolean( p_init_msg_list ) THEN
570 FND_MSG_PUB.initialize;
571 END IF;
572
573 -- Debug Message
574 IF (AMS_DEBUG_HIGH_ON) THEN
575 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
576 FND_MESSAGE.Set_Token('ROW', l_api_name||': Start', TRUE);
577 FND_MSG_PUB.Add;
578 END IF;
579
580 IF (AMS_LOG_PROCEDURE_ON) THEN
581 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
582 END IF;
583
584 -- Initialize API return status to success
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 OPEN c_all_master;
587 LOOP
588 FETCH c_all_master into i_master_source_type_id;
589 EXIT when c_all_master%notfound;
590 master_source_type_view(l_api_version,
591 FND_API.G_FALSE,
592 FND_API.G_VALID_LEVEL_FULL,
593 x_return_status,
594 x_msg_count,
595 x_msg_data,
596 i_master_source_type_id);
597
598 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
599 RAISE FND_API.g_exc_unexpected_error;
600 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
601 RAISE FND_API.g_exc_error;
602 END IF;
603 END LOOP;
604 CLOSE c_all_master;
605
606 /*
607 exception
608 when others then
609 raise;
610 */
611 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
612 THEN
613 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
614 FND_MESSAGE.Set_Token('ROW', l_Api_name, TRUE);
615 FND_MSG_PUB.Add;
616 END IF;
617
618
619 IF (AMS_DEBUG_HIGH_ON) THEN
620 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
621 FND_MESSAGE.Set_Token('ROW', l_api_name||': END', TRUE);
622 FND_MSG_PUB.Add;
623 END IF;
624
625 IF (AMS_LOG_PROCEDURE_ON) THEN
626 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
627 END IF;
628
629 -- Standard call to get message count AND IF count is 1, get message info.
630 FND_MSG_PUB.Count_AND_Get
631 ( p_count => x_msg_count,
632 p_data => x_msg_data,
633 p_encoded => FND_API.G_FALSE
634 );
638 WHEN FND_API.G_EXC_ERROR THEN
635
636 EXCEPTION
637
639 x_return_status := FND_API.G_RET_STS_ERROR ;
640
641 IF (AMS_LOG_PROCEDURE_ON) THEN
642 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' execution ERROR has been raised ');
643 END IF;
644
645 FND_MSG_PUB.Count_AND_Get
646 ( p_count => x_msg_count,
647 p_data => x_msg_data,
648 p_encoded => FND_API.G_FALSE
649 );
650 IF c_all_master%ISOPEN THEN
651 CLOSE c_all_master;
652 END IF;
653
654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
655 IF (AMS_LOG_PROCEDURE_ON) THEN
656 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' Unexpected error has been raised ');
657 END IF;
658
659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
660 FND_MSG_PUB.Count_AND_Get
661 ( p_count => x_msg_count,
662 p_data => x_msg_data,
663 p_encoded => FND_API.G_FALSE
664 );
665 IF c_all_master%ISOPEN THEN
666 CLOSE c_all_master;
667 END IF;
668
669
670 WHEN OTHERS THEN
671
672 IF (AMS_LOG_PROCEDURE_ON) THEN
673 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' In Others exception handling ');
674 END IF;
675
676 IF (AMS_LOG_EXCEPTION_ON) THEN
677 AMS_UTILITY_PVT.debug_message(AMS_LOG_EXCEPTION,g_module_name||'.'||l_api_name,'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
678 END IF;
679
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
681 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
682 THEN
683 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
684 END IF;
685 IF c_all_master%ISOPEN THEN
686 CLOSE c_all_master;
687 END IF;
688
689
690 FND_MSG_PUB.Count_AND_Get
691 ( p_count => x_msg_count,
692 p_data => x_msg_data,
693 p_encoded => FND_API.G_FALSE
694 );
695
696 end update_all_master_views;
697
698
699
700 PROCEDURE process_views(
701 p_api_version_number IN NUMBER,
702 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
703 p_commit IN VARCHAR2 := FND_API.G_FALSE,
704 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
705
706 x_return_status OUT NOCOPY VARCHAR2,
707 x_msg_count OUT NOCOPY NUMBER,
708 x_msg_data OUT NOCOPY VARCHAR2,
709 p_list_source_type_id IN NUMBER
710 ) IS
711
712 i_list_source_type VARCHAR2(30);
713 i_master_flag VARCHAR2(1);
714
715 l_api_name CONSTANT VARCHAR2(30) := 'process_views';
716 l_api_version CONSTANT NUMBER := 1.0;
717 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
718 -- Status Local Variables
719 l_return_status VARCHAR2(1); -- Return value from procedures
720
721 cursor c_source_type is
722 SELECT list_source_type, master_source_type_flag FROM ams_list_src_types_vl
723 WHERE list_source_type_id = p_list_source_type_id;
724
725
726 begin
727
728
729 IF NOT FND_API.Compatible_API_Call ( l_api_version,
730 p_api_version_number,
731 l_api_name,
732 G_PKG_NAME)
733 THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END IF;
736
737
738 -- Initialize message list IF p_init_msg_list is set to TRUE.
739 IF FND_API.to_Boolean( p_init_msg_list ) THEN
740 FND_MSG_PUB.initialize;
741 END IF;
742
743 -- Debug Message
744 IF (AMS_DEBUG_HIGH_ON) THEN
745 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
746 FND_MESSAGE.Set_Token('ROW', l_api_name||': Start', TRUE);
747 FND_MSG_PUB.Add;
748 END IF;
749
750
751 IF (AMS_LOG_PROCEDURE_ON) THEN
752 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||':Start');
753 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,'Passed in List Source Type Id is:'||p_list_source_type_id);
754 END IF;
755
756 -- Initialize API return status to success
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 OPEN c_source_type;
760 FETCH c_source_type into i_list_source_type, i_master_flag;
761 CLOSE c_source_type;
762
763 if i_list_source_type = 'TARGET' then
764 IF i_master_flag = 'Y'
765 AND (p_list_source_type_id = 14 OR p_list_source_type_id = 30) ---person or organization contact
766 THEN
767 IF (AMS_LOG_STATEMENT_ON) THEN
768 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name,'calling the master_source_type_view');
769 END IF;
770
771 master_source_type_view(
772 l_api_version,
773 FND_API.G_FALSE,
774 FND_API.G_VALID_LEVEL_FULL,
775 x_return_status,
776 x_msg_count,
777 x_msg_data,
778 p_list_source_type_id);
779
780 else
781 IF (AMS_LOG_STATEMENT_ON) THEN
782 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name,'calling the update_all_master_views');
783 END IF;
787 FND_API.G_FALSE,
784
785 update_all_master_views(
786 l_api_version,
788 FND_API.G_VALID_LEVEL_FULL,
789 x_return_status,
790 x_msg_count,
791 x_msg_data,
792 p_list_source_type_id);
793
794 end if;
795
796 IF (AMS_LOG_STATEMENT_ON) THEN
797 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,g_module_name||'.'||l_api_name,'Return Status is:'||x_return_status);
798 END IF;
799
800 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
801 THEN
802 AMS_UTILITY_PVT.debug_message('Error Generating the tar views');
803 FND_MESSAGE.set_name('AMS', 'AMS_ERR_LIST_GEN_TAR_VIEW');
804 FND_MSG_PUB.Add;
805 RAISE FND_API.g_exc_error;
806 END IF;
807
808 END IF;
809
810
811 /* -- commented out old code.
812 if i_master_flag = 'Y' then
813 master_source_type_view(p_list_source_type_id);
814 else
815 update_all_master_views(p_list_source_type_id);
816 end if;
817 end if;
818
819
820 exception
821 when others then
822 raise;
823 */
824 -- Success Message
825 -- MMSG
826 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
827 THEN
828 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
829 FND_MESSAGE.Set_Token('ROW', l_api_name, TRUE);
830 FND_MSG_PUB.Add;
831 END IF;
832
833
834 IF (AMS_DEBUG_HIGH_ON) THEN
835 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
836 FND_MESSAGE.Set_Token('ROW', l_api_name||': END', TRUE);
837 FND_MSG_PUB.Add;
838 END IF;
839
840 IF (AMS_LOG_PROCEDURE_ON) THEN
841 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,l_full_name||': END ');
842 END IF;
843
844
845 -- Standard call to get message count AND IF count is 1, get message info.
846 FND_MSG_PUB.Count_AND_Get
847 ( p_count => x_msg_count,
848 p_data => x_msg_data,
849 p_encoded => FND_API.G_FALSE
850 );
851
852 EXCEPTION
853
854 WHEN FND_API.G_EXC_ERROR THEN
855 IF (AMS_LOG_PROCEDURE_ON) THEN
856 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' execution ERROR has been raised ');
857 END IF;
858
859 x_return_status := FND_API.G_RET_STS_ERROR ;
860 FND_MSG_PUB.Count_AND_Get
861 ( p_count => x_msg_count,
862 p_data => x_msg_data,
863 p_encoded => FND_API.G_FALSE
864 );
865
866
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868
869 IF (AMS_LOG_PROCEDURE_ON) THEN
870 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' Unexpected error has been raised ');
871 END IF;
872
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
874 FND_MSG_PUB.Count_AND_Get
875 ( p_count => x_msg_count,
876 p_data => x_msg_data,
877 p_encoded => FND_API.G_FALSE
878 );
879
880 WHEN OTHERS THEN
881
882 IF (AMS_LOG_PROCEDURE_ON) THEN
883 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,g_module_name||'.'||l_api_name,' In Others exception handling ');
884 END IF;
885
886 IF (AMS_LOG_EXCEPTION_ON) THEN
887 AMS_UTILITY_PVT.debug_message(AMS_LOG_EXCEPTION,g_module_name||'.'||l_api_name,'SQLCODE:' || SQLCODE || ' SQLERRM: ' || SQLERRM);
888 END IF;
889
890
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
892 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
893 THEN
894 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
895 END IF;
896
897 FND_MSG_PUB.Count_AND_Get
898 ( p_count => x_msg_count,
899 p_data => x_msg_data,
900 p_encoded => FND_API.G_FALSE
901 );
902 end process_views;
903
904 end AMS_LIST_SRC_TYPES_PVT;