[Home] [Help]
PACKAGE BODY: APPS.PA_OBJECT_DIST_LISTS_PVT
Source
1 PACKAGE BODY PA_OBJECT_DIST_LISTS_PVT AS
2 /* $Header: PATODLVB.pls 120.1 2005/08/19 17:04:37 mwasowic noship $ */
3 procedure CREATE_OBJECT_DIST_LIST (
4 p_api_version IN NUMBER := 1.0,
5 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
6 p_commit IN VARCHAR2 := FND_API.g_false,
7 p_validate_only IN VARCHAR2 := FND_API.g_true,
8 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
9 P_LIST_ID in NUMBER,
10 P_OBJECT_TYPE in VARCHAR2,
11 P_OBJECT_ID in VARCHAR2,
12 P_RECORD_VERSION_NUMBER in NUMBER := 1,
13 P_CREATED_BY in NUMBER default fnd_global.user_id,
14 P_CREATION_DATE in DATE default sysdate,
15 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
16 P_LAST_UPDATE_DATE in DATE default sysdate,
17 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
18 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
20 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21 )
22 IS
23 l_error_msg_code varchar2(30);
24 BEGIN
25 IF p_commit = FND_API.G_TRUE
26 THEN
27 SAVEPOINT CREATE_OBJECT_DIST_LIST;
28 END IF;
29
30 IF p_init_msg_list = FND_API.G_TRUE THEN
31 fnd_msg_pub.initialize;
32 END IF;
33
34 x_return_status := 'S';
35 x_msg_count := 0;
36
37 -- Validate the Input Values
38 If (p_list_id is null OR
39 NOT PA_DISTRIBUTION_LIST_UTILS.Check_valid_dist_list_id(
40 p_list_id =>p_list_id) )
41 then
42 l_error_msg_code := 'PA_DL_LIST_ID_INV';
43 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
44 p_msg_name => l_error_msg_code);
45
46 x_msg_data := l_error_msg_code;
47 x_msg_count := x_msg_count +1;
48 x_return_status := 'E';
49 RAISE FND_API.G_EXC_ERROR;
50 END IF;
51 -- Check valid Object Type
52
53 -- Check Valid Object Id for the Object type
54
55 -- Insert a row if no validation failure
56 If (x_return_status = fnd_api.g_ret_sts_success
57 AND p_validate_only <> fnd_api.g_true) then
58 PA_OBJECT_DIST_LISTS_PKG.INSERT_ROW (
59 P_LIST_ID => P_LIST_ID,
60 P_OBJECT_TYPE => P_OBJECT_TYPE,
61 P_OBJECT_ID => P_OBJECT_ID,
62 P_RECORD_VERSION_NUMBER => 1,
63 P_CREATED_BY => P_CREATED_BY,
64 P_CREATION_DATE => P_CREATION_DATE,
65 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
66 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
67 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
68 End if;
69 -- Commit the changes if requested
70 if (p_commit = FND_API.G_TRUE
71 AND x_return_status = fnd_api.g_ret_sts_success) then
72 commit;
73 end if;
74
75 fnd_msg_pub.count_and_get(p_count => x_msg_count,
76 p_data => x_msg_data);
77
78
79 EXCEPTION
80 WHEN FND_API.G_EXC_ERROR THEN
81 IF p_commit = FND_API.G_TRUE
82 THEN
83 ROLLBACK TO CREATE_OBJECT_DIST_LIST;
84 END IF;
85 x_return_status := 'E';
86
87 WHEN OTHERS THEN
88 IF p_commit = FND_API.G_TRUE
89 THEN
90 ROLLBACK TO CREATE_OBJECT_DIST_LIST;
91 END IF;
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_PVT',
94 p_procedure_name => 'CREATE_OBJECT_DIST_LIST',
95 p_error_text => SUBSTRB(SQLERRM,1,240));
96 RAISE;
97
98 End CREATE_OBJECT_DIST_LIST;
99
100 procedure UPDATE_OBJECT_DIST_LIST (
101 p_api_version IN NUMBER := 1.0,
102 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
103 p_commit IN VARCHAR2 := FND_API.g_false,
104 p_validate_only IN VARCHAR2 := FND_API.g_true,
105 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
106 P_LIST_ID in NUMBER,
107 P_OBJECT_TYPE in VARCHAR2,
108 P_OBJECT_ID in VARCHAR2,
109 P_RECORD_VERSION_NUMBER in NUMBER,
110 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
111 P_LAST_UPDATE_DATE in DATE default sysdate,
112 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
113 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
114 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
115 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
116 )
117 IS
118 Cursor check_record_changed IS
119 select rowid
120 from pa_object_dist_lists
121 where list_id = p_list_id
122 and object_type = p_object_type
123 and object_id = p_object_id
124 and record_version_number = p_record_version_number
125 for update of list_id;
126
127 l_error_msg_code varchar2(30);
128 l_rowid rowid;
129
130 Begin
131 IF p_commit = FND_API.G_TRUE
132 THEN
133 SAVEPOINT UPDATE_OBJECT_DIST_LIST;
134 END IF;
135
136 IF p_init_msg_list = FND_API.G_TRUE THEN
137 fnd_msg_pub.initialize;
138 END IF;
139
140 x_return_status := fnd_api.g_ret_sts_success;
141 x_msg_count := 0;
142
143 -- Validate the Input Values
144 If (p_list_id is null OR
145 NOT PA_DISTRIBUTION_LIST_UTILS.Check_valid_dist_list_id(
146 p_list_id =>p_list_id) )
147 then
148 l_error_msg_code := 'PA_DL_LIST_ID_INV';
149 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
150 p_msg_name => l_error_msg_code);
151
152 x_msg_data := l_error_msg_code;
153 x_msg_count := x_msg_count +1;
154 x_return_status := 'E';
155 RAISE FND_API.G_EXC_ERROR;
156 END IF;
157 -- Check valid Object Type
158
159 -- Check Valid Object Id for the Object type
160 -- Lock the Row
161 OPEN check_record_changed;
162 FETCH check_record_changed INTO l_rowid;
163 IF check_record_changed%NOTFOUND THEN
164 PA_UTILS.Add_Message( p_app_short_name => 'PA'
165 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169 CLOSE check_record_changed;
170
171 -- Update row
172 If (x_return_status = FND_API.G_RET_STS_SUCCESS
173 AND p_validate_only <> fnd_api.g_true) then
174 PA_OBJECT_DIST_LISTS_PKG.UPDATE_ROW (
175 P_LIST_ID => P_LIST_ID,
176 P_OBJECT_TYPE => P_OBJECT_TYPE,
177 P_OBJECT_ID => P_OBJECT_ID,
178 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER + 1,
179 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
180 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
181 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
182 End if;
183 -- Commit the changes if requested
184 if (p_commit = FND_API.G_TRUE
185 AND x_return_status = fnd_api.g_ret_sts_success) then
186 commit;
187 end if;
188
189 fnd_msg_pub.count_and_get(p_count => x_msg_count,
190 p_data => x_msg_data);
191
192 EXCEPTION
193 WHEN FND_API.G_EXC_ERROR THEN
194 IF p_commit = FND_API.G_TRUE
195 THEN
196 ROLLBACK TO UPDATE_OBJECT_DIST_LIST;
197 END IF;
198 x_return_status := 'E';
199
200 WHEN OTHERS THEN
201 IF p_commit = FND_API.G_TRUE
202 THEN
203 ROLLBACK TO UPDATE_OBJECT_DIST_LIST;
204 END IF;
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_PVT',
207 p_procedure_name => 'UPDATE_OBJECT_DIST_LIST',
208 p_error_text => SUBSTRB(SQLERRM,1,240));
209 RAISE;
210
211
212 End UPDATE_OBJECT_DIST_LIST;
213
214 procedure DELETE_OBJECT_DIST_LIST (
215 p_api_version IN NUMBER := 1.0,
216 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
217 p_commit IN VARCHAR2 := FND_API.g_false,
218 p_validate_only IN VARCHAR2 := FND_API.g_true,
219 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
220 P_LIST_ID in NUMBER,
221 P_OBJECT_TYPE in VARCHAR2,
222 P_OBJECT_ID in VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
224 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
225 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
226 )
227 IS
228 Begin
229
230 IF p_commit = FND_API.G_TRUE
231 THEN
232 SAVEPOINT DELETE_OBJECT_DIST_LIST;
233 END IF;
234
235 IF p_init_msg_list = FND_API.G_TRUE THEN
236 fnd_msg_pub.initialize;
237 END IF;
238 x_return_status := fnd_api.g_ret_sts_success;
239 x_msg_count := 0;
240
241 -- Delete row
242 If (x_return_status = FND_API.G_RET_STS_SUCCESS
243 AND p_validate_only <> fnd_api.g_true) then
244 PA_OBJECT_DIST_LISTS_PKG.DELETE_ROW (
245 P_LIST_ID => P_LIST_ID
246 ,P_OBJECT_TYPE => P_OBJECT_TYPE
247 ,P_OBJECT_ID => P_OBJECT_ID ) ;
248 End if;
249 -- Commit the changes if requested
250 if (p_commit = FND_API.G_TRUE
251 AND x_return_status = fnd_api.g_ret_sts_success) then
252 commit;
253 end if;
254
255 fnd_msg_pub.count_and_get(p_count => x_msg_count,
256 p_data => x_msg_data);
257
258 EXCEPTION
259 WHEN FND_API.G_EXC_ERROR THEN
260 IF p_commit = FND_API.G_TRUE
261 THEN
262 ROLLBACK TO DELETE_OBJECT_DIST_LIST;
263 END IF;
264 x_return_status := 'E';
265
266 WHEN OTHERS THEN
267 IF p_commit = FND_API.G_TRUE
268 THEN
269 ROLLBACK TO DELETE_OBJECT_DIST_LIST;
270 END IF;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_PVT',
273 p_procedure_name => 'DELETE_OBJECT_DIST_LIST',
274 p_error_text => SUBSTRB(SQLERRM,1,240));
275 RAISE;
276 End DELETE_OBJECT_DIST_LIST;
277
278 procedure DELETE_ASSOC_DIST_LISTS (
279 p_api_version IN NUMBER := 1.0,
280 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
281 p_commit IN VARCHAR2 := FND_API.g_false,
282 p_validate_only IN VARCHAR2 := FND_API.g_true,
283 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
284 P_OBJECT_TYPE in VARCHAR2,
285 P_OBJECT_ID in VARCHAR2,
286 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
287 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
288 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
289 )
290 IS
291 CURSOR c_list_ids IS
292 SELECT list_id list_id
293 FROM pa_object_dist_lists
294 WHERE object_type = p_object_type
295 AND object_id = p_object_id;
296
297 CURSOR c_list_other_usage(cp_list_id NUMBER) IS
298 SELECT 'Y'
299 FROM pa_object_dist_lists
300 WHERE list_id = cp_list_id
301 AND (object_type <> p_object_type
302 OR object_id <> p_object_id)
303 AND ROWNUM = 1;
304
305 l_dummy VARCHAR2(1);
306 Begin
307
308 IF p_commit = FND_API.G_TRUE
309 THEN
310 SAVEPOINT DELETE_ASSOC_DIST_LISTS;
311 END IF;
312
313 IF p_init_msg_list = FND_API.G_TRUE THEN
314 fnd_msg_pub.initialize;
315 END IF;
316
317 x_return_status := fnd_api.g_ret_sts_success;
318 x_msg_count := 0;
319
320 IF (x_return_status = FND_API.G_RET_STS_SUCCESS
321 AND p_validate_only <> fnd_api.g_true) THEN
322 FOR rec IN c_list_ids LOOP
323 -- Delete row
324 PA_OBJECT_DIST_LISTS_PKG.DELETE_ROW (
325 P_LIST_ID => rec.list_id
326 ,P_OBJECT_TYPE => P_OBJECT_TYPE
327 ,P_OBJECT_ID => P_OBJECT_ID );
328
329 OPEN c_list_other_usage(rec.list_id);
330 FETCH c_list_other_usage INTO l_dummy;
331 IF c_list_other_usage%NOTFOUND THEN
332 pa_distribution_lists_pvt.delete_dist_list (
333 p_validate_only => fnd_api.g_false,
334 p_list_id => rec.list_id,
335 p_delete_list_item_flag => 'Y',
336 x_return_status => x_return_status,
337 x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data);
339 END IF;
340 CLOSE c_list_other_usage;
341 EXIT WHEN x_return_status <> 'S';
342 END LOOP;
343 End if;
344 -- Commit the changes if requested
345 if (p_commit = FND_API.G_TRUE
346 AND x_return_status = fnd_api.g_ret_sts_success) then
347 commit;
348 end if;
349
350 fnd_msg_pub.count_and_get(p_count => x_msg_count,
351 p_data => x_msg_data);
352
353 EXCEPTION
354 WHEN FND_API.G_EXC_ERROR THEN
355 IF p_commit = FND_API.G_TRUE
356 THEN
357 ROLLBACK TO DELETE_ASSOC_DIST_LISTS;
358 END IF;
359 x_return_status := 'E';
360
361 WHEN OTHERS THEN
362 IF p_commit = FND_API.G_TRUE
363 THEN
364 ROLLBACK TO DELETE_ASSOC_DIST_LISTS;
365 END IF;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_OBJECT_DIST_LISTS_PVT',
368 p_procedure_name => 'DELETE_ASSOC_DIST_LISTS',
369 p_error_text => SUBSTRB(SQLERRM,1,240));
370 RAISE;
371 End DELETE_ASSOC_DIST_LISTS;
372
373 END PA_OBJECT_DIST_LISTS_PVT;