[Home] [Help]
PACKAGE BODY: APPS.EAM_ORG_MAINT_DEFAULTS_PVT
Source
1 PACKAGE BODY eam_org_maint_defaults_pvt AS
2 /* $Header: EAMVOMDB.pls 120.1 2006/01/24 10:16:20 yjhabak noship $*/
3 -- Start of comments
4 -- API name : eam_org_maint_defaults_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 Default = FND_API.G_FALSE
11 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
13 -- parameter1
14 -- parameter2
15 -- .
16 -- .
17 -- OUT x_return_status OUT VARCHAR2(1)
18 -- x_msg_count OUT NUMBER
19 -- x_msg_data OUT VARCHAR2(2000)
20 -- parameter1
21 -- parameter2
22 -- .
23 -- .
24 -- Version Current version x.x
25 -- Changed....
26 -- previous version y.y
27 -- Changed....
28 -- .
29 -- .
30 -- previous version 2.0
31 -- Changed....
32 -- Initial version 1.0
33 --
34 -- Notes : Note text
35 --
36 -- End of comments
37
38 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_org_maint_defaults_pvt';
39
40
41 FUNCTION from_fnd_std_num(p_value NUMBER)
42 RETURN NUMBER IS
43 BEGIN
44 IF (p_value = fnd_api.g_miss_num) THEN
45 RETURN null;
46 ELSE
47 RETURN p_value;
48 END IF;
49 END from_fnd_std_num;
50
51
52 FUNCTION from_fnd_std_char(p_value VARCHAR2)
53 RETURN VARCHAR2 IS
54 BEGIN
55 IF (p_value = fnd_api.g_miss_char) THEN
56 RETURN null;
57 ELSE
58 RETURN p_value;
59 END IF;
60 END from_fnd_std_char;
61
62
63 PROCEDURE insert_row
64 (
65 p_api_version IN NUMBER
66 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
67 ,p_commit IN VARCHAR2 := fnd_api.g_false
68 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
69 ,p_object_type IN NUMBER
70 ,p_object_id IN NUMBER
71 ,p_organization_id IN NUMBER
72 ,p_owning_department_id IN NUMBER := NULL
73 ,p_accounting_class_code IN VARCHAR2 := NULL
74 ,p_area_id IN NUMBER := NULL
75 ,p_activity_cause_code IN VARCHAR2 := NULL
76 ,p_activity_type_code IN VARCHAR2 := NULL
77 ,p_activity_source_code IN VARCHAR2 := NULL
78 ,p_shutdown_type_code IN VARCHAR2 := NULL
79 ,p_tagging_required_flag IN VARCHAR2 := NULL
80 ,x_return_status OUT NOCOPY VARCHAR2
81 ,x_msg_count OUT NOCOPY NUMBER
82 ,x_msg_data OUT NOCOPY VARCHAR2
83 ) IS
84
85 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
86 l_api_version CONSTANT NUMBER := 1.0;
87 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
88
89 BEGIN
90 -- Standard Start of API savepoint
91 SAVEPOINT eam_org_maint_defaults_pvt;
92
93 -- Standard call to check for call compatibility.
94 IF NOT fnd_api.compatible_api_call(
95 l_api_version
96 ,p_api_version
97 ,l_api_name
98 ,g_pkg_name) THEN
99 RAISE fnd_api.g_exc_unexpected_error;
100 END IF;
101
102 -- Initialize message list if p_init_msg_list is set to TRUE.
103 IF fnd_api.to_boolean(p_init_msg_list) THEN
104 fnd_msg_pub.initialize;
105 END IF;
106
107 -- Initialize API return status to success
108 x_return_status := fnd_api.g_ret_sts_success;
109
110 -- API body
111
112 -- All validation will done by the calling API
113
114 BEGIN
115
116 -- Insert row into EOMD
117 INSERT INTO eam_org_maint_defaults
118 (
119 object_type
120 ,object_id
121 ,organization_id
122 ,owning_department_id
123 ,accounting_class_code
124 ,area_id
125 ,activity_cause_code
126 ,activity_type_code
127 ,activity_source_code
128 ,shutdown_type_code
129 ,tagging_required_flag
130 ,created_by
131 ,creation_date
132 ,last_updated_by
133 ,last_update_date
134 ,last_update_login
135 )
136 VALUES
137 (
138 p_object_type
139 ,p_object_id
140 ,p_organization_id
141 ,p_owning_department_id
142 ,p_accounting_class_code
143 ,p_area_id
144 ,p_activity_cause_code
145 ,p_activity_type_code
146 ,p_activity_source_code
147 ,p_shutdown_type_code
148 ,p_tagging_required_flag
149 ,fnd_global.user_id
150 ,sysdate
151 ,fnd_global.user_id
152 ,sysdate
153 ,fnd_global.login_id
154 );
155
156 EXCEPTION
157 WHEN DUP_VAL_ON_INDEX THEN
158 fnd_message.set_name('EAM', 'EAM_EOMD_RECORD_EXISTS');
159 fnd_msg_pub.add;
160 RAISE fnd_api.g_exc_error;
161 END;
162
163 -- End of API body.
164
165 -- Standard check of p_commit.
166 IF fnd_api.to_boolean(p_commit) THEN
167 COMMIT WORK;
168 END IF;
169
170 -- Standard call to get message count and if count is 1, get message info.
171 fnd_msg_pub.count_and_get(p_count => x_msg_count
172 ,p_data => x_msg_data);
173 EXCEPTION
174 WHEN fnd_api.g_exc_error THEN
175 ROLLBACK TO eam_org_maint_defaults_pvt;
176 x_return_status := fnd_api.g_ret_sts_error;
177 fnd_msg_pub.count_and_get(p_count => x_msg_count
178 ,p_data => x_msg_data);
179 WHEN fnd_api.g_exc_unexpected_error THEN
180 ROLLBACK TO eam_org_maint_defaults_pvt;
181 x_return_status := fnd_api.g_ret_sts_unexp_error;
182 fnd_msg_pub.count_and_get(p_count => x_msg_count
183 ,p_data => x_msg_data);
184 WHEN OTHERS THEN
185 ROLLBACK TO eam_org_maint_defaults_pvt;
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
188 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
189 END IF;
190 fnd_msg_pub.count_and_get(p_count => x_msg_count
191 ,p_data => x_msg_data);
192 END insert_row;
193
194
195 PROCEDURE update_row
196 (
197 p_api_version IN NUMBER
198 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
199 ,p_commit IN VARCHAR2 := fnd_api.g_false
200 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
201 ,p_object_type IN NUMBER
202 ,p_object_id IN NUMBER
203 ,p_organization_id IN NUMBER
204 ,p_owning_department_id IN NUMBER := NULL
205 ,p_accounting_class_code IN VARCHAR2 := NULL
206 ,p_area_id IN NUMBER := NULL
207 ,p_activity_cause_code IN VARCHAR2 := NULL
208 ,p_activity_type_code IN VARCHAR2 := NULL
209 ,p_activity_source_code IN VARCHAR2 := NULL
210 ,p_shutdown_type_code IN VARCHAR2 := NULL
211 ,p_tagging_required_flag IN VARCHAR2 := NULL
212 ,x_return_status OUT NOCOPY VARCHAR2
213 ,x_msg_count OUT NOCOPY NUMBER
214 ,x_msg_data OUT NOCOPY VARCHAR2
215 ) IS
216
217 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
218 l_api_version CONSTANT NUMBER := 1.0;
219 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
220
221 BEGIN
222 -- Standard Start of API savepoint
223 SAVEPOINT eam_org_maint_defaults_pvt;
224
225 -- Standard call to check for call compatibility.
226 IF NOT fnd_api.compatible_api_call(
227 l_api_version
228 ,p_api_version
229 ,l_api_name
230 ,g_pkg_name) THEN
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233
234 -- Initialize message list if p_init_msg_list is set to TRUE.
235 IF fnd_api.to_boolean(p_init_msg_list) THEN
236 fnd_msg_pub.initialize;
237 END IF;
238
239 -- Initialize API return status to success
240 x_return_status := fnd_api.g_ret_sts_success;
241
242 -- API body
243
244 -- All validation will done by the calling API
245
246 BEGIN
247
248 -- Insert row into EOMD
249 UPDATE eam_org_maint_defaults SET
250 owning_department_id = decode(p_owning_department_id, fnd_api.g_miss_num, null, null, owning_department_id, p_owning_department_id)
251 ,accounting_class_code = decode(p_accounting_class_code, fnd_api.g_miss_char, null, null, accounting_class_code, p_accounting_class_code)
252 ,area_id = decode(p_area_id, fnd_api.g_miss_num, null, null, area_id, p_area_id)
253 ,activity_cause_code = decode(p_activity_cause_code, fnd_api.g_miss_char, null, null, activity_cause_code, p_activity_cause_code)
254 ,activity_type_code = decode(p_activity_type_code, fnd_api.g_miss_char, null, null, activity_type_code, p_activity_type_code)
255 ,activity_source_code = decode(p_activity_source_code, fnd_api.g_miss_char, null, null, activity_source_code, p_activity_source_code)
256 ,shutdown_type_code = decode(p_shutdown_type_code, fnd_api.g_miss_char, null, null, shutdown_type_code, p_shutdown_type_code)
257 ,tagging_required_flag = decode(p_tagging_required_flag, fnd_api.g_miss_char, null, null, tagging_required_flag, p_tagging_required_flag)
258 ,created_by = fnd_global.user_id
259 ,creation_date = sysdate
260 ,last_updated_by = fnd_global.user_id
261 ,last_update_date = sysdate
262 ,last_update_login = fnd_global.login_id
263 WHERE object_type = p_object_type AND object_id = p_object_id
264 AND organization_id = p_organization_id;
265
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 fnd_message.set_name('EAM', 'EAM_EOMD_RECORD_NOT_FOUND');
269 fnd_msg_pub.add;
270 RAISE fnd_api.g_exc_error;
271 END;
272
273 -- End of API body.
274
275 -- Standard check of p_commit.
276 IF fnd_api.to_boolean(p_commit) THEN
277 COMMIT WORK;
278 END IF;
279
280 -- Standard call to get message count and if count is 1, get message info.
281 fnd_msg_pub.count_and_get(p_count => x_msg_count
282 ,p_data => x_msg_data);
283 EXCEPTION
284 WHEN fnd_api.g_exc_error THEN
285 ROLLBACK TO eam_org_maint_defaults_pvt;
286 x_return_status := fnd_api.g_ret_sts_error;
287 fnd_msg_pub.count_and_get(p_count => x_msg_count
288 ,p_data => x_msg_data);
289 WHEN fnd_api.g_exc_unexpected_error THEN
290 ROLLBACK TO eam_org_maint_defaults_pvt;
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 fnd_msg_pub.count_and_get(p_count => x_msg_count
293 ,p_data => x_msg_data);
294 WHEN OTHERS THEN
295 ROLLBACK TO eam_org_maint_defaults_pvt;
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
298 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
299 END IF;
300 fnd_msg_pub.count_and_get(p_count => x_msg_count
301 ,p_data => x_msg_data);
302 END update_row;
303
304
305 -- Update if row exists else insert a new row
306 PROCEDURE update_insert_row
307 (
308 p_api_version IN NUMBER
309 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
310 ,p_commit IN VARCHAR2 := fnd_api.g_false
311 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
312 ,p_object_type IN NUMBER
313 ,p_object_id IN NUMBER
314 ,p_organization_id IN NUMBER
315 ,p_owning_department_id IN NUMBER := NULL
316 ,p_accounting_class_code IN VARCHAR2 := NULL
317 ,p_area_id IN NUMBER := NULL
318 ,p_activity_cause_code IN VARCHAR2 := NULL
319 ,p_activity_type_code IN VARCHAR2 := NULL
320 ,p_activity_source_code IN VARCHAR2 := NULL
321 ,p_shutdown_type_code IN VARCHAR2 := NULL
322 ,p_tagging_required_flag IN VARCHAR2 := NULL
323 ,x_return_status OUT NOCOPY VARCHAR2
324 ,x_msg_count OUT NOCOPY NUMBER
325 ,x_msg_data OUT NOCOPY VARCHAR2
326 ) IS
327
328 l_api_name CONSTANT VARCHAR2(30) := 'update_insert_row';
329 l_api_version CONSTANT NUMBER := 1.0;
330 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
331 l_count NUMBER;
332
333 BEGIN
334 -- Standard Start of API savepoint
335 SAVEPOINT eam_org_maint_defaults_pvt;
336
337 -- Standard call to check for call compatibility.
338 IF NOT fnd_api.compatible_api_call(
339 l_api_version
340 ,p_api_version
341 ,l_api_name
342 ,g_pkg_name) THEN
343 RAISE fnd_api.g_exc_unexpected_error;
344 END IF;
345
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF fnd_api.to_boolean(p_init_msg_list) THEN
348 fnd_msg_pub.initialize;
349 END IF;
350
351 -- Initialize API return status to success
352 x_return_status := fnd_api.g_ret_sts_success;
353
354 -- API body
355
356 -- All validation will done by the calling API
357
358 SELECT count(*) INTO l_count
359 FROM eam_org_maint_defaults
360 WHERE object_type = p_object_type AND object_id = p_object_id
361 AND organization_id = p_organization_id;
362
363 IF l_count = 0 THEN
364 insert_row
365 (
366 p_api_version => 1.0
367 ,p_object_type => p_object_type
368 ,p_object_id => p_object_id
369 ,p_organization_id => p_organization_id
370 ,p_owning_department_id => from_fnd_std_num(p_owning_department_id)
371 ,p_accounting_class_code => from_fnd_std_char(p_accounting_class_code)
372 ,p_area_id => from_fnd_std_num(p_area_id)
373 ,p_activity_cause_code => from_fnd_std_char(p_activity_cause_code)
374 ,p_activity_type_code => from_fnd_std_char(p_activity_type_code)
375 ,p_activity_source_code => from_fnd_std_char(p_activity_source_code)
376 ,p_shutdown_type_code => from_fnd_std_char(p_shutdown_type_code)
377 ,p_tagging_required_flag => from_fnd_std_char(p_tagging_required_flag)
378 ,x_return_status => x_return_status
379 ,x_msg_count => x_msg_count
380 ,x_msg_data => x_msg_data
381 );
382 ELSE
383 update_row
384 (
385 p_api_version => 1.0
386 ,p_object_type => p_object_type
387 ,p_object_id => p_object_id
388 ,p_organization_id => p_organization_id
389 ,p_owning_department_id => p_owning_department_id
390 ,p_accounting_class_code => p_accounting_class_code
391 ,p_area_id => p_area_id
392 ,p_activity_cause_code => p_activity_cause_code
393 ,p_activity_type_code => p_activity_type_code
394 ,p_activity_source_code => p_activity_source_code
395 ,p_shutdown_type_code => p_shutdown_type_code
396 ,p_tagging_required_flag => p_tagging_required_flag
397 ,x_return_status => x_return_status
398 ,x_msg_count => x_msg_count
399 ,x_msg_data => x_msg_data
400 );
401 END IF;
402
403 IF x_return_status <> fnd_api.g_ret_sts_success THEN
404 RAISE fnd_api.g_exc_error;
405 END IF;
406
407 -- End of API body.
408
409 -- Standard check of p_commit.
410 IF fnd_api.to_boolean(p_commit) THEN
411 COMMIT WORK;
412 END IF;
413
414 -- Standard call to get message count and if count is 1, get message info.
415 fnd_msg_pub.count_and_get(p_count => x_msg_count
416 ,p_data => x_msg_data);
417 EXCEPTION
418 WHEN fnd_api.g_exc_error THEN
419 ROLLBACK TO eam_org_maint_defaults_pvt;
420 x_return_status := fnd_api.g_ret_sts_error;
421 fnd_msg_pub.count_and_get(p_count => x_msg_count
422 ,p_data => x_msg_data);
423 WHEN fnd_api.g_exc_unexpected_error THEN
424 ROLLBACK TO eam_org_maint_defaults_pvt;
425 x_return_status := fnd_api.g_ret_sts_unexp_error;
426 fnd_msg_pub.count_and_get(p_count => x_msg_count
427 ,p_data => x_msg_data);
428 WHEN OTHERS THEN
429 ROLLBACK TO eam_org_maint_defaults_pvt;
430 x_return_status := fnd_api.g_ret_sts_unexp_error;
431 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
432 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
433 END IF;
434 fnd_msg_pub.count_and_get(p_count => x_msg_count
435 ,p_data => x_msg_data);
436 END update_insert_row;
437
438
439 END eam_org_maint_defaults_pvt;
440