[Home] [Help]
PACKAGE BODY: APPS.AMS_CELL_INTEGRATE_PVT
Source
1 PACKAGE BODY AMS_CELL_INTEGRATE_PVT AS
2 /* $Header: amsvceib.pls 115.6 2002/11/22 19:26:32 jieli ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_CELL_INTEGRATE_PVT';
5 ---------------------------------------------------------------------
6 -- PROCEDURE
7 -- create_segment_list
8 -- PURPOSE
9 -- This procedure will create a list header which will include the
10 -- segment id.
11 --
12 -- HISTORY
13 -- 09/06/01 yxliu Created.
14 -- 04/03/02 yxliu check if sql is empty before call list generation
15 -- API to avoid endless loop
16 ---------------------------------------------------------------------
17
18 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21
22 PROCEDURE create_segment_list
23 ( p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 p_owner_user_id IN NUMBER,
28 p_cell_id IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 x_list_header_id OUT NOCOPY NUMBER,
33 x_list_source_type OUT NOCOPY VARCHAR2,
34 p_list_name in VARCHAR2 --DEFAULT NULL
35 )
36 is
37 l_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
38 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
39 l_api_version NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'Create_segment_List';
41 l_action_rec AMS_ListAction_PVT.action_rec_type ;
42 l_action_id NUMBER;
43 l_cell_list_name VARCHAR2(200);
44
45 l_found VARCHAR2(1) := 'N';
46 l_master_type VARCHAR2(80);
47 l_master_type_id NUMBER;
48 l_source_object_name VARCHAR2(80);
49 l_source_object_pk_field VARCHAR2(80);
50 l_from_position NUMBER;
51 l_from_counter NUMBER;
52 l_end_position NUMBER;
53 l_end_counter NUMBER;
54 l_sql_string VARCHAR2(32767);
55 l_return_status VARCHAR2(1);
56
57 l_count NUMBER;
58 l_string_copy VARCHAR2(32767);
59 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
60 l_length NUMBER;
61
62 CURSOR c_chk_name IS
63 SELECT 'x'
64 FROM ams_list_headers_vl
65 WHERE list_name = p_list_name;
66
67 CURSOR c_get_cell_name IS
68 SELECT cell_name
69 FROM ams_cells_vl
70 WHERE cell_id = p_cell_id;
71
72 l_source_type varchar2(100);
73 l_var varchar2(1);
74
75 BEGIN
76
77 --------------------- get new list name -----------------------
78 IF (AMS_DEBUG_HIGH_ON) THEN
79
80 AMS_Utility_PVT.debug_message(l_api_name||': get list name');
81 END IF;
82 OPEN c_get_cell_name ;
83 FETCH c_get_cell_name into l_cell_list_name;
84 CLOSE c_get_cell_name;
85
86 IF p_list_name IS NOT NULL THEN
87 OPEN c_chk_name ;
88 FETCH c_chk_name INTO l_var ;
89 CLOSE c_chk_name ;
90 ELSE
91 l_var := 'x';
92 END IF;
93
94 IF l_var IS NOT NULL THEN
95 SELECT l_cell_list_name|| ' -:'|| to_char(sysdate,'DD-MON-YY HH:MM:SS')
96 INTO l_cell_list_name
97 FROM ams_cells_vl
98 WHERE cell_id = p_cell_id;
99 ELSE
100 l_cell_list_name := p_list_name ;
101 END IF;
102
103 IF NOT FND_API.Compatible_API_Call ( l_api_version,
104 p_api_version,
105 l_api_name,
106 G_PKG_NAME) THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 -- Initialize message list IF p_init_msg_list is set to TRUE.
111 IF FND_API.to_Boolean( p_init_msg_list ) THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114
115 -- Debug Message
116 IF (AMS_DEBUG_HIGH_ON) THEN
117 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
118 FND_MESSAGE.Set_Token('ROW',
119 'AMS_ListGeneration_PKG.create_segemnt_list: Start',
120 TRUE);
121 FND_MSG_PUB.Add;
122 END IF;
123
124 --------------------- find source type -----------------------
125 IF (AMS_DEBUG_HIGH_ON) THEN
126
127 AMS_Utility_PVT.debug_message(l_api_name ||': get sql string for cell');
128 END IF;
129 AMS_CELL_PVT.get_single_sql(
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 p_validation_level => p_validation_level,
133 x_return_status => l_return_status,
134 x_msg_count => x_msg_count,
135 x_msg_data => x_msg_data,
136 p_cell_id => p_cell_id,
137 x_sql_string => l_sql_string
138 );
139 IF l_return_status = FND_API.g_ret_sts_error THEN
140 RAISE FND_API.g_exc_error;
141 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
142 RAISE FND_API.g_exc_unexpected_error;
143 END IF;
144
145 IF l_sql_string IS NULL OR
146 l_sql_string = ''
147 THEN
148 IF (AMS_DEBUG_HIGH_ON) THEN
149
150 AMS_Utility_PVT.debug_message(l_api_name ||': empty sql string');
151 END IF;
152 RAISE FND_API.G_EXC_ERROR;
153 ELSE
154 --l_sql_string := UPPER(l_sql_string);
155 --dbms_output.put_line('sql_string: ' || l_sql_string);
156
157 IF (AMS_DEBUG_HIGH_ON) THEN
158
159
160
161 AMS_Utility_PVT.debug_message(l_api_name ||': put sql string into table');
162
163 END IF;
164 l_count := 0;
165 l_string_copy := l_sql_string;
166 l_length := length(l_string_copy);
167
168 LOOP
169 l_count := l_count + 1;
170 IF l_length < 1999 THEN
171 l_sql_string_tbl(l_count) := l_string_copy;
172 EXIT;
173 ELSE
174 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
175 l_string_copy := substr(l_string_copy, 2000);
176 END IF;
177 l_length := length(l_string_copy);
178 END LOOP;
179
180 IF (AMS_DEBUG_HIGH_ON) THEN
181
182
183
184 AMS_Utility_PVT.debug_message(l_api_name||': validate sql string');
185
186 END IF;
187 l_found := 'N';
188 AMS_ListGeneration_PKG.validate_sql_string(
189 p_sql_string => l_sql_string_tbl ,
190 p_search_string => 'FROM',
191 p_comma_valid => 'N',
192 x_found => l_found,
193 x_position => l_from_position,
194 x_counter => l_from_counter) ;
195
196 IF l_found = 'N' THEN
197 FND_MESSAGE.set_name('AMS', 'AMS_LIST_FROM_NOT_FOUND');
198 FND_MSG_PUB.Add;
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201
202 IF (AMS_DEBUG_HIGH_ON) THEN
203
204
205
206 AMS_Utility_PVT.debug_message(l_api_name||': get master type');
207
208 END IF;
209 l_found := 'N';
210
211 AMS_ListGeneration_PKG.get_master_types (
212 p_sql_string => l_sql_string_tbl,
213 p_start_length => 1,
214 p_start_counter => 1,
215 p_end_length => l_from_position,
216 p_end_counter => l_from_counter,
217 x_master_type_id=> l_master_type_id,
218 x_master_type=> l_master_type,
219 x_found=> l_found,
220 x_source_object_name => l_source_object_name,
221 x_source_object_pk_field => l_source_object_pk_field);
222
223 IF l_found = 'N' THEN
224 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_MASTER_TYPE');
225 FND_MSG_PUB.Add;
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228 IF (AMS_DEBUG_HIGH_ON) THEN
229
230 AMS_Utility_PVT.debug_message(l_api_name ||': master_type = '||l_master_type);
231 END IF;
232 --dbms_output.put_line('master_type :' || l_master_type);
233 x_list_source_type := l_master_type;
234
235 -------------------- create_listheader -----------------
236 -- Initialize API return status to success
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 -- Perform the database operation
240 IF (AMS_DEBUG_HIGH_ON) THEN
241
242 AMS_Utility_PVT.debug_message(l_api_name ||': create_listheader');
243 END IF;
244
245 -- ams_listheader_pvt.init_listheader_rec(l_list_header_rec);
246 l_list_header_rec.list_name := l_cell_list_name ;
247 l_list_header_rec.list_type := 'STANDARD';
248 l_list_header_rec.list_source_type := l_master_type;
249 l_list_header_rec.owner_user_id := p_owner_user_id;
250
251 AMS_ListHeader_PVT.Create_Listheader (
252 p_api_version => 1.0,
253 p_init_msg_list => l_init_msg_list,
254 p_commit => p_commit,
255 p_validation_level => p_validation_level ,
256 x_return_status => x_return_status,
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data,
259 p_listheader_rec => l_list_header_rec,
260 x_listheader_id => x_list_header_id
261 );
262
263 IF x_return_status <> FND_API.g_ret_sts_success THEN
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266 IF (AMS_DEBUG_HIGH_ON) THEN
267
268 AMS_Utility_PVT.debug_message(l_api_name ||'listheader_id = '||x_list_header_id);
269 END IF;
270
271 -------------------- Create_ListAction ----------------
272 IF (AMS_DEBUG_HIGH_ON) THEN
273
274 AMS_Utility_PVT.debug_message(l_api_name ||': create_listAction');
275 END IF;
276
277 l_action_rec.arc_action_used_by := 'LIST';
278 l_action_rec.action_used_by_id := x_list_header_id;
279 l_action_rec.order_number := 1 ;
280 l_action_rec.list_action_type := 'INCLUDE';
281 l_action_rec.arc_incl_object_from := 'CELL';
282 l_action_rec.incl_object_id := p_cell_id;
283 l_action_rec.rank := 1;
284
285 AMS_ListAction_PVT.Create_ListAction
286 ( p_api_version => 1.0,
287 p_init_msg_list => l_init_msg_list,
288 p_commit => p_commit,
289 p_validation_level => p_validation_level,
290 x_return_status => x_return_status,
291 x_msg_count => x_msg_count,
292 x_msg_data => x_msg_data,
293 p_action_rec => l_action_rec,
294 x_action_id => l_action_id
295 ) ;
296 FND_MESSAGE.set_name('AMS','after list action->'|| l_action_id|| '<-');
297 FND_MSG_PUB.Add;
298
299 IF x_return_status <> FND_API.g_ret_sts_success THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 -- Standard check of p_commit.
304
305 IF FND_API.To_Boolean(p_commit)
306 THEN
307 COMMIT WORK;
308 END IF;
309
310 -- Success Message
311 -- MMSG
312 --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
313 --THEN
314 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
315 FND_MESSAGE.Set_Token('ROW', 'AMS_CELL_PVT.create_segment_list: ');
316 FND_MSG_PUB.Add;
317 --END IF;
318
319
320 --IF (AMS_DEBUG_HIGH_ON) THEN
321 --THEN
322 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
323 FND_MESSAGE.Set_Token('ROW', 'AMS_ListGeneration_PKG.create_segment_list: END');
324 FND_MSG_PUB.Add;
325 --END IF;
326
327 FND_MSG_PUB.count_and_get(
328 p_encoded => FND_API.g_false,
329 p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 END IF;
333
334 EXCEPTION
335 WHEN FND_API.G_EXC_ERROR THEN
336 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
337 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
338 FND_MSG_PUB.Add;
339 -- Check if reset of the status is required
340 x_return_status := FND_API.G_RET_STS_ERROR ;
341 FND_MSG_PUB.count_and_get(
342 p_encoded => FND_API.g_false,
343 p_count => x_msg_count,
344 p_data => x_msg_data);
345
346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
347 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
348 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
349 FND_MSG_PUB.Add;
351 FND_MSG_PUB.count_and_get(
352 p_encoded => FND_API.g_false,
353 p_count => x_msg_count,
354 p_data => x_msg_data);
355
356 WHEN OTHERS THEN
357 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
358 FND_MESSAGE.Set_Token('ROW', sqlerrm||' '||sqlcode);
359 FND_MSG_PUB.Add;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
361 FND_MSG_PUB.count_and_get(
362 p_encoded => FND_API.g_false,
363 p_count => x_msg_count,
364 p_data => x_msg_data);
365 END CREATE_Segment_LIST;
366
367 END AMS_CEll_INTEGRATE_PVT;