1 PACKAGE BODY EGO_LIFECYCLE_ADMIN_PUB AS
2 /* $Header: EGOPLCAB.pls 115.5 2004/05/26 09:55:19 srajapar noship $ */
3
4 g_pkg_name VARCHAR2(30) := 'EGO_LIFECYCLE_ADMIN_PUB';
5 g_current_user_id NUMBER := EGO_SCTX.Get_User_Id();
6 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
7 g_app_name VARCHAR2(3) := 'EGO';
8 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
9 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
10 g_api_name_token VARCHAR2(8) := 'API_NAME';
11 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
12
13 PROCEDURE Check_Delete_Lifecycle_OK
14 (
15 p_api_version IN NUMBER
16 , p_lifecycle_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
17 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
18 , x_delete_ok OUT NOCOPY VARCHAR2
19 , x_return_status OUT NOCOPY VARCHAR2
20 , x_errorcode OUT NOCOPY NUMBER
21 , x_msg_count OUT NOCOPY NUMBER
22 , x_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_count NUMBER;
26 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Lifecycle_OK';
27 l_message VARCHAR2(4000) := NULL;
28 BEGIN
29
30 IF FND_API.To_Boolean(p_init_msg_list) THEN
31 FND_MSG_PUB.Initialize;
32 END IF;
33
34 x_delete_ok := FND_API.G_TRUE;
35 --Check if there are any entries for it in MTL_SYSTEM_ITEMS
36 BEGIN
37 SELECT 1
38 INTO l_count
39 FROM DUAL
40 WHERE EXISTS
41 (SELECT 'X'
42 FROM MTL_SYSTEM_ITEMS_B
43 WHERE LIFECYCLE_ID = p_lifecycle_id);
44 x_delete_ok := FND_API.G_FALSE;
45 l_message := 'EGO_ITEM_ASSOCIATED_LC';
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 NULL;
49 -- no rows found
50 -- check item revisions
51 END;
52
53 IF (l_message IS NULL) THEN
54 BEGIN
55 SELECT 1
56 INTO l_count
57 FROM DUAL
58 WHERE EXISTS
59 (SELECT 'X'
60 FROM MTL_ITEM_REVISIONS_B
61 WHERE LIFECYCLE_ID = p_lifecycle_id);
62 x_delete_ok := FND_API.G_FALSE;
63 l_message := 'EGO_REVISION_ASSOCIATED_LC';
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 NULL;
67 -- no rows found
68 -- check for pending item status
69 END;
70 END IF;
71
72 IF (l_message IS NULL) THEN
73 BEGIN
74 SELECT 1
75 INTO l_count
76 FROM DUAL
77 WHERE EXISTS
78 (SELECT 'X'
79 FROM MTL_PENDING_ITEM_STATUS
80 WHERE LIFECYCLE_ID = p_lifecycle_id);
81 x_delete_ok := FND_API.G_FALSE;
82 l_message := 'EGO_PENDING_ITEM_LC';
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 NULL;
86 -- no rows found
87 -- every thing is fine
88 END;
89 END IF;
90
91 IF (l_message IS NOT NULL) THEN
92 FND_MESSAGE.Set_Name(g_app_name
93 ,l_message);
94 FND_MSG_PUB.Add;
95 END IF;
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 x_delete_ok := FND_API.G_FALSE;
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 FND_MESSAGE.Set_Name(g_app_name
103 ,g_plsql_err);
104 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
105 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
106 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
107 FND_MSG_PUB.Add;
108
109 END Check_Delete_Lifecycle_OK;
110
111 PROCEDURE Check_Delete_Phase_OK
112 (
113 p_api_version IN NUMBER
114 , p_phase_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
115 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
116 , x_delete_ok OUT NOCOPY VARCHAR2
117 , x_return_status OUT NOCOPY VARCHAR2
118 , x_errorcode OUT NOCOPY NUMBER
119 , x_msg_count OUT NOCOPY NUMBER
120 , x_msg_data OUT NOCOPY VARCHAR2
121 )
122 IS
123 l_count NUMBER;
124 l_message VARCHAR2(4000);
125 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Phase_OK';
126 BEGIN
127
128 IF FND_API.To_Boolean(p_init_msg_list) THEN
129 FND_MSG_PUB.Initialize;
130 END IF;
131 x_delete_ok := FND_API.G_TRUE;
132
133 BEGIN
134 SELECT 1
135 INTO l_count
136 FROM DUAL
137 WHERE EXISTS
138 (SELECT 'X'
139 FROM MTL_SYSTEM_ITEMS_B
140 WHERE CURRENT_PHASE_ID = p_phase_id);
141 x_delete_ok := FND_API.G_FALSE;
142 l_message := 'EGO_ITEM_ASSOCIATED_PH';
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 NULL;
146 -- no rows found
147 -- check item revisions
148 END;
149
150 IF (l_message IS NULL) THEN
151 BEGIN
152 SELECT 1
153 INTO l_count
154 FROM DUAL
155 WHERE EXISTS
156 (SELECT 'X'
157 FROM MTL_ITEM_REVISIONS_B
158 WHERE CURRENT_PHASE_ID = p_phase_id);
159 x_delete_ok := FND_API.G_FALSE;
160 l_message := 'EGO_REVISION_ASSOCIATED_PH';
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 NULL;
164 -- no rows found
165 -- check if there are any pending item status
166 END;
167 END IF;
168
169 IF (l_message IS NULL) THEN
170 BEGIN
171 SELECT 1
172 INTO l_count
173 FROM DUAL
174 WHERE EXISTS
175 (SELECT 'X'
176 FROM MTL_PENDING_ITEM_STATUS
177 WHERE PHASE_ID = p_phase_id);
178 x_delete_ok := FND_API.G_FALSE;
179 l_message := 'EGO_PENDING_ITEM_PH';
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 NULL;
183 -- no rows found
184 -- every thing is fine
185 END;
186 END IF;
187
188 IF (l_message IS NOT NULL) THEN
189 FND_MESSAGE.Set_Name(g_app_name
190 ,l_message
191 );
192 FND_MSG_PUB.Add;
193 END IF;
194
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_delete_ok := FND_API.G_FALSE;
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 FND_MESSAGE.Set_Name(g_app_name
201 ,g_plsql_err);
202 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
203 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
204 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
205 FND_MSG_PUB.Add;
206
207 END Check_Delete_Phase_OK;
208
209
210 PROCEDURE Process_Phase_Delete
211 (
212 p_api_version IN NUMBER
213 , p_phase_id IN NUMBER
214 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
215 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
216 , x_return_status OUT NOCOPY VARCHAR2
217 , x_errorcode OUT NOCOPY NUMBER
218 , x_msg_count OUT NOCOPY NUMBER
219 , x_msg_data OUT NOCOPY VARCHAR2
220 )
221 IS
222 l_api_name CONSTANT VARCHAR2(30) := 'Process_Phase_Delete';
223 l_api_version CONSTANT NUMBER := 1.0;
224 BEGIN
225 IF FND_API.To_Boolean(p_commit) THEN
226 SAVEPOINT Process_Phase_Delete_PUB;
227 END IF;
228 --Standard checks
229 IF NOT FND_API.Compatible_API_Call (l_api_version
230 ,p_api_version
231 ,l_api_name
232 ,g_pkg_name)
233 THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END IF;
236 IF FND_API.To_Boolean(p_init_msg_list) THEN
237 FND_MSG_PUB.Initialize;
238 END IF;
239
240 --Delete the stale data from ego_lcphase_policy
241 DELETE
242 FROM
243 EGO_LCPHASE_POLICY
244 WHERE
245 PHASE_ID = p_phase_id;
246
247 -- Standard check of p_commit.
248 IF FND_API.To_Boolean(p_commit)
249 THEN
250 COMMIT WORK;
251 END IF;
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253 EXCEPTION
254 WHEN OTHERS THEN
255 IF FND_API.To_Boolean(p_commit) THEN
256 ROLLBACK TO Process_Phase_Delete_PUB;
257 END IF;
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 FND_MESSAGE.Set_Name(g_app_name
260 ,g_plsql_err);
261 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
262 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
263 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
264 FND_MSG_PUB.Add;
265
266 END Process_Phase_Delete;
267
268 PROCEDURE Process_Phase_Code_Delete
269 (
270 p_api_version IN NUMBER
271 , p_phase_code IN VARCHAR2
272 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
273 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
274 , x_return_status OUT NOCOPY VARCHAR2
275 , x_errorcode OUT NOCOPY NUMBER
276 , x_msg_count OUT NOCOPY NUMBER
277 , x_msg_data OUT NOCOPY VARCHAR2
278 )
279 IS
280 l_api_name CONSTANT VARCHAR2(30) := 'Process_Phase_Code_Delete';
281 l_api_version CONSTANT NUMBER := 1.0;
282 BEGIN
283 IF FND_API.To_Boolean(p_commit) THEN
284 SAVEPOINT Process_Phase_Code_Delete_PUB;
285 END IF;
286 --Standard checks
287 IF NOT FND_API.Compatible_API_Call (l_api_version
288 ,p_api_version
289 ,l_api_name
290 ,g_pkg_name)
291 THEN
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293 END IF;
294 IF FND_API.To_Boolean(p_init_msg_list) THEN
295 FND_MSG_PUB.Initialize;
296 END IF;
297
298 --Delete the stale data from ego_lcphase_item_status
299 DELETE
300 FROM
301 EGO_LCPHASE_ITEM_STATUS
302 WHERE
303 PHASE_CODE = p_phase_code;
304
305 -- Standard check of p_commit.
306 IF FND_API.To_Boolean(p_commit)
307 THEN
308 COMMIT WORK;
309 END IF;
310 x_return_status := FND_API.G_RET_STS_SUCCESS;
311 EXCEPTION
312 WHEN OTHERS THEN
313 IF FND_API.To_Boolean(p_commit) THEN
314 ROLLBACK TO Process_Phase_Code_Delete_PUB;
315 END IF;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 FND_MESSAGE.Set_Name(g_app_name
318 ,g_plsql_err);
319 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
320 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
321 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
322 FND_MSG_PUB.Add;
323
324 END Process_Phase_Code_Delete;
325
326 PROCEDURE Delete_Stale_Data_For_Lc
327 (
328 p_api_version IN NUMBER
329 , p_lifecycle_id IN NUMBER
330 , p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
331 , p_commit IN VARCHAR2 := fnd_api.g_FALSE
332 , x_return_status OUT NOCOPY VARCHAR2
333 , x_errorcode OUT NOCOPY NUMBER
334 , x_msg_count OUT NOCOPY NUMBER
335 , x_msg_data OUT NOCOPY VARCHAR2
336 )
337 IS
338 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Stale_Data_For_Lc';
339 l_api_version CONSTANT NUMBER := 1.0;
340 BEGIN
341 IF FND_API.To_Boolean(p_commit) THEN
342 SAVEPOINT Delete_Stale_Data_Lc_PUB;
343 END IF;
344 --Standard checks
345 IF NOT FND_API.Compatible_API_Call (l_api_version
346 ,p_api_version
347 ,l_api_name
348 ,g_pkg_name)
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352 IF FND_API.To_Boolean(p_init_msg_list) THEN
353 FND_MSG_PUB.Initialize;
354 END IF;
355 --Delete the stale data from ego_obj_type_lifecycles
356 DELETE
357 FROM
358 EGO_OBJ_TYPE_LIFECYCLES
359 WHERE
360 LIFECYCLE_ID = p_lifecycle_id;
361 -- Standard check of p_commit.
362 IF FND_API.To_Boolean(p_commit)
363 THEN
364 COMMIT WORK;
365 END IF;
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367 -- Make a standard call to get message count and if count is 1,
368 -- get message info.
369 -- The client will directly display the x_msg_data (which is already
370 -- translated) if the x_msg_count = 1;
371 -- Else, i.e. if x_msg_count > 1, client will call the FND_MSG_PUB.Get
372 -- Server-side procedure to access the messages, and consolidate them
373 -- and display them all at once or display one message after another.
374 FND_MSG_PUB.Count_And_Get
375 (
376 p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379 EXCEPTION
380 WHEN OTHERS THEN
381 IF FND_API.To_Boolean(p_commit) THEN
382 ROLLBACK TO Delete_Stale_Data_Lc_PUB;
383 END IF;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 FND_MESSAGE.Set_Name(g_app_name
386 ,g_plsql_err);
387 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
388 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
389 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
390 FND_MSG_PUB.Add;
391
392 END Delete_Stale_Data_For_Lc;
393 END EGO_LIFECYCLE_ADMIN_PUB;
394
[Home] [Help]