1 PACKAGE BODY EAM_DEPT_APPROVERS_PVT as
2 /* $Header: EAMVDAPB.pls 115.8 2003/08/19 06:51:40 yjhabak ship $ */
3 -- Start of comments
4 -- API name : EAM_DEPT_APPROVERS_PVT
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN P_API_VERSION IN NUMBER REQUIRED
10 -- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
11 -- DEFAULT = FND_API.G_FALSE
12 -- P_COMMIT IN VARCHAR2 OPTIONAL
13 -- DEFAULT = FND_API.G_FALSE
14 -- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
15 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
16 -- P_DEPT_ID IN NUMBER REQUIRED
17 -- P_LAST_UPDATE_DATE IN DATE REQUIRED
18 -- P_LAST_UPDATED_BY IN NUMBER REQUIRED
19 -- P_CREATION_DATE IN DATE REQUIRED
20 -- P_CREATED_BY IN NUMBER REQUIRED
21 -- P_LAST_UPDATE_LOGIN IN NUMBER OPTIONAL
22 -- P_RESPONSIBILITY_ID IN NUMBER REQUIRED
23 -- P_RESPONSIBILITY_APPLICATN_ID IN NUMBER REQUIRED
24 -- OUT X_RETURN_STATUS OUT VARCHAR2(1)
25 -- X_MSG_COUNT OUT NUMBER
26 -- X_MSG_DATA OUT VARCHAR2(2000)
27 --
28 -- Version Current version 115.0
29 --
30 -- Notes : Note text
31 --
32 -- End of comments
33 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_DEPT_APPROVERS_PVT';
34
35 PROCEDURE INSERT_ROW(
36 P_API_VERSION IN NUMBER,
37 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
38 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
39 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 P_ROWID IN OUT NOCOPY VARCHAR2,
41 P_DEPT_ID NUMBER,
42 P_ORGANIZATION_ID NUMBER,
43 P_LAST_UPDATE_DATE DATE,
44 P_LAST_UPDATED_BY NUMBER,
45 P_CREATION_DATE DATE,
46 P_CREATED_BY NUMBER,
47 P_LAST_UPDATE_LOGIN NUMBER,
48 P_RESPONSIBILITY_ID NUMBER,
49 P_RESPONSIBILITY_APPLICATN_ID NUMBER,
50 P_PRIMARY_APPROVER NUMBER,
51 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
52 X_MSG_COUNT OUT NOCOPY NUMBER,
53 X_MSG_DATA OUT NOCOPY VARCHAR2)
54 IS
55 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
58
59 CURSOR C IS SELECT rowid FROM BOM_EAM_DEPT_APPROVERS
60 WHERE DEPT_ID = P_DEPT_ID AND RESPONSIBILITY_ID=P_RESPONSIBILITY_ID;
61
62 BEGIN
63
64 -- Standard Start of API savepoint
65 SAVEPOINT apiname_apitype;
66
67 -- Standard call to check for call compatibility.
68 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
69 RAISE fnd_api.g_exc_unexpected_error;
70 END IF;
71
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF fnd_api.to_boolean(p_init_msg_list) THEN
74 fnd_msg_pub.initialize;
75 END IF;
76
77 -- Initialize API return status to success
78 x_return_status := fnd_api.g_ret_sts_success;
79
80 -- API body
81 INSERT INTO BOM_EAM_DEPT_APPROVERS(
82 DEPT_ID,
83 ORGANIZATION_ID,
84 LAST_UPDATE_DATE,
85 LAST_UPDATED_BY,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATE_LOGIN,
89 RESPONSIBILITY_ID,
90 RESPONSIBILITY_APPLICATION_ID,
91 PRIMARY_APPROVER_ID
92 ) SELECT
93 P_DEPT_ID,
94 P_ORGANIZATION_ID,
95 P_LAST_UPDATE_DATE,
96 P_LAST_UPDATED_BY,
97 P_CREATION_DATE,
98 P_CREATED_BY,
99 P_LAST_UPDATE_LOGIN,
100 P_RESPONSIBILITY_ID,
101 P_RESPONSIBILITY_APPLICATN_ID,
102 P_PRIMARY_APPROVER
103 FROM DUAL
104 WHERE P_DEPT_ID = P_DEPT_ID
105 AND
106 NOT EXISTS (
107 SELECT null
108 FROM BOM_EAM_DEPT_APPROVERS BDA
109 WHERE BDA.DEPT_ID = P_DEPT_ID And
110 BDA.RESPONSIBILITY_ID = P_RESPONSIBILITY_ID
111 );
112
113 OPEN C;
114 FETCH C INTO P_ROWID;
115 if (C%NOTFOUND) then
116 CLOSE C;
117 Raise NO_DATA_FOUND;
118 end if;
119 CLOSE C;
120
121 -- End of API body.
122 -- Standard check of p_commit.
123 IF fnd_api.to_boolean(p_commit) THEN
124 COMMIT WORK;
125 END IF;
126
127 -- Standard call to get message count and if count is 1, get message info.
128 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
129 EXCEPTION
130 WHEN fnd_api.g_exc_error THEN
131 ROLLBACK TO apiname_apitype;
132 x_return_status := fnd_api.g_ret_sts_error;
133 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
134 WHEN fnd_api.g_exc_unexpected_error THEN
135 ROLLBACK TO apiname_apitype;
136 x_return_status := fnd_api.g_ret_sts_unexp_error;
137 fnd_msg_pub.count_and_get(
138 p_count => x_msg_count
139 ,p_data => x_msg_data);
140 WHEN OTHERS THEN
141 ROLLBACK TO apiname_apitype;
142 x_return_status := fnd_api.g_ret_sts_unexp_error;
143
144 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
145 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
146 END IF;
147
148 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
149 END Insert_Row;
150
151
152 PROCEDURE LOCK_ROW(
153 P_API_VERSION IN NUMBER,
154 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
155 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
156 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
157 P_ROWID IN OUT NOCOPY VARCHAR2,
158 P_DEPT_ID NUMBER,
159 P_ORGANIZATION_ID NUMBER,
160 P_LAST_UPDATE_DATE DATE,
161 P_LAST_UPDATED_BY NUMBER,
162 P_CREATION_DATE DATE,
163 P_CREATED_BY NUMBER,
164 P_LAST_UPDATE_LOGIN NUMBER,
165 P_RESPONSIBILITY_ID NUMBER,
166 P_RESPONSIBILITY_APPLICATN_ID NUMBER,
167 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
168 X_MSG_COUNT OUT NOCOPY NUMBER,
169 X_MSG_DATA OUT NOCOPY VARCHAR2)
170 IS
171 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
172 l_api_version CONSTANT NUMBER := 1.0;
173 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
174
175 CURSOR C IS
176 SELECT * FROM BOM_EAM_DEPT_APPROVERS
177 WHERE rowid = P_ROWID
178 FOR UPDATE Of RESPONSIBILITY_ID NOWAIT;
179 Recinfo C%ROWTYPE;
180
181 BEGIN
182
183 -- Standard Start of API savepoint
184 SAVEPOINT apiname_apitype;
185
186 -- Standard call to check for call compatibility.
187 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
188 RAISE fnd_api.g_exc_unexpected_error;
189 END IF;
190
191 -- Initialize message list if p_init_msg_list is set to TRUE.
192 IF fnd_api.to_boolean(p_init_msg_list) THEN
193 fnd_msg_pub.initialize;
194 END IF;
195
196 -- Initialize API return status to success
197 x_return_status := fnd_api.g_ret_sts_success;
198
199 -- API body
200
201 OPEN C;
202 FETCH C INTO Recinfo;
203 if (C%NOTFOUND) then
204 CLOSE C;
205 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
206 APP_EXCEPTION.Raise_Exception;
207 end if;
208 CLOSE C;
209 if (
210 (Recinfo.DEPT_ID = P_DEPT_ID)
211 AND (Recinfo.ORGANIZATION_ID = P_ORGANIZATION_ID)
212 AND (Recinfo.RESPONSIBILITY_ID = P_RESPONSIBILITY_ID)
213 AND (Recinfo.RESPONSIBILITY_APPLICATION_ID = P_RESPONSIBILITY_APPLICATN_ID)
214 ) then
215 return;
216 else
217 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
218 APP_EXCEPTION.Raise_Exception;
219 end if;
220
221 -- End of API body.
222 -- Standard check of p_commit.
223 IF fnd_api.to_boolean(p_commit) THEN
224 COMMIT WORK;
225 END IF;
226
227 -- Standard call to get message count and if count is 1, get message info.
228 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
229 EXCEPTION
230 WHEN fnd_api.g_exc_error THEN
231 ROLLBACK TO apiname_apitype;
232 x_return_status := fnd_api.g_ret_sts_error;
233 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
234 WHEN fnd_api.g_exc_unexpected_error THEN
235 ROLLBACK TO apiname_apitype;
236 x_return_status := fnd_api.g_ret_sts_unexp_error;
237 fnd_msg_pub.count_and_get(
238 p_count => x_msg_count
239 ,p_data => x_msg_data);
240 WHEN OTHERS THEN
241 ROLLBACK TO apiname_apitype;
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243
244 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
245 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
246 END IF;
247
248 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
249
250 END Lock_Row;
251
252
253 PROCEDURE UPDATE_ROW
254 (
255 P_API_VERSION IN NUMBER,
256 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
257 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
258 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
259 P_ROWID IN OUT NOCOPY VARCHAR2,
260 P_DEPT_ID NUMBER,
261 P_ORGANIZATION_ID NUMBER,
262 P_LAST_UPDATE_DATE DATE,
263 P_LAST_UPDATED_BY NUMBER,
264 P_CREATION_DATE DATE,
265 P_CREATED_BY NUMBER,
266 P_LAST_UPDATE_LOGIN NUMBER,
267 P_RESPONSIBILITY_ID NUMBER,
268 P_RESPONSIBILITY_APPLICATN_ID NUMBER,
269 P_PRIMARY_APPROVER NUMBER,
270 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
271 X_MSG_COUNT OUT NOCOPY NUMBER,
272 X_MSG_DATA OUT NOCOPY VARCHAR2)
273 IS
274 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
275 l_api_version CONSTANT NUMBER := 1.0;
276 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
277
278 BEGIN
279 -- Standard Start of API savepoint
280 SAVEPOINT apiname_apitype;
281
282 -- Standard call to check for call compatibility.
283 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
284 RAISE fnd_api.g_exc_unexpected_error;
285 END IF;
286
287 -- Initialize message list if p_init_msg_list is set to TRUE.
288 IF fnd_api.to_boolean(p_init_msg_list) THEN
289 fnd_msg_pub.initialize;
290 END IF;
291
292 -- Initialize API return status to success
293 x_return_status := fnd_api.g_ret_sts_success;
294
295 -- API body
296
297 UPDATE BOM_EAM_DEPT_APPROVERS
298 SET
299 DEPT_ID = P_DEPT_ID,
300 ORGANIZATION_ID = P_ORGANIZATION_ID,
301 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
302 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
303 CREATION_DATE = P_CREATION_DATE,
304 CREATED_BY = P_CREATED_BY,
305 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
306 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID,
307 RESPONSIBILITY_APPLICATION_ID = P_RESPONSIBILITY_APPLICATN_ID,
308 PRIMARY_APPROVER_ID = P_PRIMARY_APPROVER
309 WHERE ROWID = P_ROWID;
310
311 if (SQL%NOTFOUND) then
312 Raise NO_DATA_FOUND;
313 end if;
314
315 -- End of API body.
316 -- Standard check of p_commit.
317 IF fnd_api.to_boolean(p_commit) THEN
318 COMMIT WORK;
319 END IF;
320
321 -- Standard call to get message count and if count is 1, get message info.
322 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
323 EXCEPTION
324 WHEN fnd_api.g_exc_error THEN
325 ROLLBACK TO apiname_apitype;
326 x_return_status := fnd_api.g_ret_sts_error;
327 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
328 WHEN fnd_api.g_exc_unexpected_error THEN
329 ROLLBACK TO apiname_apitype;
330 x_return_status := fnd_api.g_ret_sts_unexp_error;
331 fnd_msg_pub.count_and_get(
332 p_count => x_msg_count
333 ,p_data => x_msg_data);
334 WHEN OTHERS THEN
335 ROLLBACK TO apiname_apitype;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337
338 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
339 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
340 END IF;
341
342 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
343
344 END Update_Row;
345
346
347
348 PROCEDURE DELETE_ROW (
349 P_DEPT_ID NUMBER,
350 P_ORGANIZATION_ID NUMBER,
351 P_RESPONSIBILITY_ID NUMBER,
352 P_RESPONSIBILITY_APPLICATN_ID NUMBER)
353 is
354 begin
355
356 delete from BOM_EAM_DEPT_APPROVERS
357 where DEPT_ID = P_DEPT_ID
358 and ORGANIZATION_ID = P_ORGANIZATION_ID
359 and RESPONSIBILITY_ID = P_RESPONSIBILITY_ID
360 and RESPONSIBILITY_APPLICATION_ID = P_RESPONSIBILITY_APPLICATN_ID;
361
362 if (sql%notfound) then
363 raise no_data_found;
364 end if;
365
366 end DELETE_ROW;
367
368 END EAM_DEPT_APPROVERS_PVT;
369
[Home] [Help]