[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.12020000.3 2013/02/14 13:41:38 rsandepo ship $*/
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_work_order_type IN NUMBER := NULL
79 ,p_planner IN NUMBER := NULL
80 ,p_firm IN NUMBER := NULL
81 ,p_planned IN VARCHAR2 := NULL
82 ,p_notification_required IN VARCHAR2 := NULL
83 ,p_shutdown_type_code IN VARCHAR2 := NULL
84 ,p_tagging_required_flag IN VARCHAR2 := NULL
85 ,x_return_status OUT NOCOPY VARCHAR2
86 ,x_msg_count OUT NOCOPY NUMBER
87 ,x_msg_data OUT NOCOPY VARCHAR2
88 ) IS
89
90 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
91 l_api_version CONSTANT NUMBER := 1.0;
92 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
93
94 BEGIN
95 -- Standard Start of API savepoint
96 SAVEPOINT eam_org_maint_defaults_pvt;
97
98 -- Standard call to check for call compatibility.
99 IF NOT fnd_api.compatible_api_call(
100 l_api_version
101 ,p_api_version
102 ,l_api_name
103 ,g_pkg_name) THEN
104 RAISE fnd_api.g_exc_unexpected_error;
105 END IF;
106
107 -- Initialize message list if p_init_msg_list is set to TRUE.
108 IF fnd_api.to_boolean(p_init_msg_list) THEN
109 fnd_msg_pub.initialize;
110 END IF;
111
112 -- Initialize API return status to success
113 x_return_status := fnd_api.g_ret_sts_success;
114
115 -- API body
116
117 -- All validation will done by the calling API
118
119 BEGIN
120
121 -- Insert row into EOMD
122 INSERT INTO eam_org_maint_defaults
123 (
124 object_type
125 ,object_id
126 ,organization_id
127 ,owning_department_id
128 ,accounting_class_code
129 ,area_id
130 ,activity_cause_code
131 ,activity_type_code
132 ,activity_source_code
133 ,work_order_type
134 ,planner_maintenance
135 ,firm_planned_flag
136 ,plan_maintenance
137 ,notification_required
138 ,shutdown_type_code
139 ,tagging_required_flag
140 ,created_by
141 ,creation_date
142 ,last_updated_by
143 ,last_update_date
144 ,last_update_login
145 )
146 VALUES
147 (
148 p_object_type
149 ,p_object_id
150 ,p_organization_id
151 ,p_owning_department_id
152 ,p_accounting_class_code
153 ,p_area_id
154 ,p_activity_cause_code
155 ,p_activity_type_code
156 ,p_activity_source_code
157 ,p_work_order_type
158 ,p_planner
159 ,p_firm
160 ,p_planned
161 ,p_notification_required
162 ,p_shutdown_type_code
163 ,p_tagging_required_flag
164 ,fnd_global.user_id
165 ,sysdate
166 ,fnd_global.user_id
167 ,sysdate
168 ,fnd_global.login_id
169 );
170
171 EXCEPTION
172 WHEN DUP_VAL_ON_INDEX THEN
173 fnd_message.set_name('EAM', 'EAM_EOMD_RECORD_EXISTS');
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_error;
176 END;
177
178 -- End of API body.
179
180 -- Standard check of p_commit.
181 IF fnd_api.to_boolean(p_commit) THEN
182 COMMIT WORK;
183 END IF;
184
185 -- Standard call to get message count and if count is 1, get message info.
186 fnd_msg_pub.count_and_get(p_count => x_msg_count
187 ,p_data => x_msg_data);
188 EXCEPTION
189 WHEN fnd_api.g_exc_error THEN
190 ROLLBACK TO eam_org_maint_defaults_pvt;
191 x_return_status := fnd_api.g_ret_sts_error;
192 fnd_msg_pub.count_and_get(p_count => x_msg_count
193 ,p_data => x_msg_data);
194 WHEN fnd_api.g_exc_unexpected_error THEN
195 ROLLBACK TO eam_org_maint_defaults_pvt;
196 x_return_status := fnd_api.g_ret_sts_unexp_error;
197 fnd_msg_pub.count_and_get(p_count => x_msg_count
198 ,p_data => x_msg_data);
199 WHEN OTHERS THEN
200 ROLLBACK TO eam_org_maint_defaults_pvt;
201 x_return_status := fnd_api.g_ret_sts_unexp_error;
202 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
203 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
204 END IF;
205 fnd_msg_pub.count_and_get(p_count => x_msg_count
206 ,p_data => x_msg_data);
207 END insert_row;
208
209
210 PROCEDURE update_row
211 (
212 p_api_version IN NUMBER
213 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
214 ,p_commit IN VARCHAR2 := fnd_api.g_false
215 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
216 ,p_object_type IN NUMBER
217 ,p_object_id IN NUMBER
218 ,p_organization_id IN NUMBER
219 ,p_owning_department_id IN NUMBER := NULL
220 ,p_accounting_class_code IN VARCHAR2 := NULL
221 ,p_area_id IN NUMBER := NULL
222 ,p_activity_cause_code IN VARCHAR2 := NULL
223 ,p_activity_type_code IN VARCHAR2 := NULL
224 ,p_activity_source_code IN VARCHAR2 := NULL
225 ,p_work_order_type IN NUMBER := NULL
226 ,p_planner IN NUMBER := NULL
227 ,p_firm IN NUMBER := NULL
228 ,p_planned IN VARCHAR2 := NULL
229 ,p_notification_required IN VARCHAR2 := NULL
230 ,p_shutdown_type_code IN VARCHAR2 := NULL
231 ,p_tagging_required_flag IN VARCHAR2 := NULL
232 ,x_return_status OUT NOCOPY VARCHAR2
233 ,x_msg_count OUT NOCOPY NUMBER
234 ,x_msg_data OUT NOCOPY VARCHAR2
235 ) IS
236
237 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
238 l_api_version CONSTANT NUMBER := 1.0;
239 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
240
241 BEGIN
242 -- Standard Start of API savepoint
243 SAVEPOINT eam_org_maint_defaults_pvt;
244
245 -- Standard call to check for call compatibility.
246 IF NOT fnd_api.compatible_api_call(
247 l_api_version
248 ,p_api_version
249 ,l_api_name
250 ,g_pkg_name) THEN
251 RAISE fnd_api.g_exc_unexpected_error;
252 END IF;
253
254 -- Initialize message list if p_init_msg_list is set to TRUE.
255 IF fnd_api.to_boolean(p_init_msg_list) THEN
256 fnd_msg_pub.initialize;
257 END IF;
258
259 -- Initialize API return status to success
260 x_return_status := fnd_api.g_ret_sts_success;
261
262 -- API body
263
264 -- All validation will done by the calling API
265
266 BEGIN
267
268 -- Insert row into EOMD
269 UPDATE eam_org_maint_defaults SET
270 owning_department_id = decode(p_owning_department_id, fnd_api.g_miss_num, null, null, owning_department_id, p_owning_department_id)
271 ,accounting_class_code = decode(p_accounting_class_code, fnd_api.g_miss_char, null, null, accounting_class_code, p_accounting_class_code)
272 ,area_id = decode(p_area_id, fnd_api.g_miss_num, null, null, area_id, p_area_id)
273 ,activity_cause_code = decode(p_activity_cause_code, fnd_api.g_miss_char, null, null, activity_cause_code, p_activity_cause_code)
274 ,activity_type_code = decode(p_activity_type_code, fnd_api.g_miss_char, null, null, activity_type_code, p_activity_type_code)
275 ,activity_source_code = decode(p_activity_source_code, fnd_api.g_miss_char, null, null, activity_source_code, p_activity_source_code)
276 ,work_order_type = decode(p_work_order_type, fnd_api.g_miss_num, null, null, work_order_type, p_work_order_type)
277 ,planner_maintenance = decode(p_planner, fnd_api.g_miss_num, NULL, NULL, planner_maintenance, p_planner)
278 ,firm_planned_flag = decode(p_firm, fnd_api.g_miss_num, NULL, NULL, firm_planned_flag, p_firm)
279 ,plan_maintenance = decode(p_planned, fnd_api.g_miss_char, NULL, NULL, plan_maintenance, p_planned)
280 ,notification_required = decode(p_notification_required, fnd_api.g_miss_char, NULL, NULL, notification_required, p_notification_required)
281 ,shutdown_type_code = decode(p_shutdown_type_code, fnd_api.g_miss_char, null, null, shutdown_type_code, p_shutdown_type_code)
282 ,tagging_required_flag = decode(p_tagging_required_flag, fnd_api.g_miss_char, null, null, tagging_required_flag, p_tagging_required_flag)
283 ,created_by = fnd_global.user_id
284 ,creation_date = sysdate
285 ,last_updated_by = fnd_global.user_id
286 ,last_update_date = sysdate
287 ,last_update_login = fnd_global.login_id
288 WHERE object_type = p_object_type AND object_id = p_object_id
289 AND organization_id = p_organization_id;
290
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 fnd_message.set_name('EAM', 'EAM_EOMD_RECORD_NOT_FOUND');
294 fnd_msg_pub.add;
295 RAISE fnd_api.g_exc_error;
296 END;
297
298 -- End of API body.
299
300 -- Standard check of p_commit.
301 IF fnd_api.to_boolean(p_commit) THEN
302 COMMIT WORK;
303 END IF;
304
305 -- Standard call to get message count and if count is 1, get message info.
306 fnd_msg_pub.count_and_get(p_count => x_msg_count
307 ,p_data => x_msg_data);
308 EXCEPTION
309 WHEN fnd_api.g_exc_error THEN
310 ROLLBACK TO eam_org_maint_defaults_pvt;
311 x_return_status := fnd_api.g_ret_sts_error;
312 fnd_msg_pub.count_and_get(p_count => x_msg_count
313 ,p_data => x_msg_data);
314 WHEN fnd_api.g_exc_unexpected_error THEN
315 ROLLBACK TO eam_org_maint_defaults_pvt;
316 x_return_status := fnd_api.g_ret_sts_unexp_error;
317 fnd_msg_pub.count_and_get(p_count => x_msg_count
318 ,p_data => x_msg_data);
319 WHEN OTHERS THEN
320 ROLLBACK TO eam_org_maint_defaults_pvt;
321 x_return_status := fnd_api.g_ret_sts_unexp_error;
322 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
323 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
324 END IF;
325 fnd_msg_pub.count_and_get(p_count => x_msg_count
326 ,p_data => x_msg_data);
327 END update_row;
328
329
330 -- Update if row exists else insert a new row
331 PROCEDURE update_insert_row
332 (
333 p_api_version IN NUMBER
334 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
335 ,p_commit IN VARCHAR2 := fnd_api.g_false
336 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
337 ,p_object_type IN NUMBER
338 ,p_object_id IN NUMBER
339 ,p_organization_id IN NUMBER
340 ,p_owning_department_id IN NUMBER := NULL
341 ,p_accounting_class_code IN VARCHAR2 := NULL
342 ,p_area_id IN NUMBER := NULL
343 ,p_activity_cause_code IN VARCHAR2 := NULL
344 ,p_activity_type_code IN VARCHAR2 := NULL
345 ,p_activity_source_code IN VARCHAR2 := NULL
346 ,p_work_order_type IN NUMBER := NULL
347 ,p_planner IN NUMBER := NULL
348 ,p_firm IN NUMBER := NULL
349 ,p_planned IN VARCHAR2 := NULL
350 ,p_notification_required IN VARCHAR2 := NULL
351 ,p_shutdown_type_code IN VARCHAR2 := NULL
352 ,p_tagging_required_flag IN VARCHAR2 := NULL
353 ,x_return_status OUT NOCOPY VARCHAR2
354 ,x_msg_count OUT NOCOPY NUMBER
355 ,x_msg_data OUT NOCOPY VARCHAR2
356 ) IS
357
358 l_api_name CONSTANT VARCHAR2(30) := 'update_insert_row';
359 l_api_version CONSTANT NUMBER := 1.0;
360 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
361 l_count NUMBER;
362
363 BEGIN
364 -- Standard Start of API savepoint
365 SAVEPOINT eam_org_maint_defaults_pvt;
366
367 -- Standard call to check for call compatibility.
368 IF NOT fnd_api.compatible_api_call(
369 l_api_version
370 ,p_api_version
371 ,l_api_name
372 ,g_pkg_name) THEN
373 RAISE fnd_api.g_exc_unexpected_error;
374 END IF;
375
376 -- Initialize message list if p_init_msg_list is set to TRUE.
377 IF fnd_api.to_boolean(p_init_msg_list) THEN
378 fnd_msg_pub.initialize;
379 END IF;
380
381 -- Initialize API return status to success
382 x_return_status := fnd_api.g_ret_sts_success;
383
384 -- API body
385
386 -- All validation will done by the calling API
387
388 SELECT count(*) INTO l_count
389 FROM eam_org_maint_defaults
390 WHERE object_type = p_object_type AND object_id = p_object_id
391 AND organization_id = p_organization_id;
392
393 IF l_count = 0 THEN
394 insert_row
395 (
396 p_api_version => 1.0
397 ,p_object_type => p_object_type
398 ,p_object_id => p_object_id
399 ,p_organization_id => p_organization_id
400 ,p_owning_department_id => from_fnd_std_num(p_owning_department_id)
401 ,p_accounting_class_code => from_fnd_std_char(p_accounting_class_code)
402 ,p_area_id => from_fnd_std_num(p_area_id)
403 ,p_activity_cause_code => from_fnd_std_char(p_activity_cause_code)
404 ,p_activity_type_code => from_fnd_std_char(p_activity_type_code)
405 ,p_activity_source_code => from_fnd_std_char(p_activity_source_code)
406 ,p_work_order_type => from_fnd_std_num(p_work_order_type)
407 ,p_planner => from_fnd_std_num(p_planner)
408 ,p_firm => from_fnd_std_num(p_firm)
409 ,p_planned => from_fnd_std_char(p_planned)
410 ,p_notification_required => from_fnd_std_char(p_notification_required)
411 ,p_shutdown_type_code => from_fnd_std_char(p_shutdown_type_code)
412 ,p_tagging_required_flag => from_fnd_std_char(p_tagging_required_flag)
413 ,x_return_status => x_return_status
414 ,x_msg_count => x_msg_count
415 ,x_msg_data => x_msg_data
416 );
417 ELSE
418 update_row
419 (
420 p_api_version => 1.0
421 ,p_object_type => p_object_type
422 ,p_object_id => p_object_id
423 ,p_organization_id => p_organization_id
424 ,p_owning_department_id => p_owning_department_id
425 ,p_accounting_class_code => p_accounting_class_code
426 ,p_area_id => p_area_id
427 ,p_activity_cause_code => p_activity_cause_code
428 ,p_activity_type_code => p_activity_type_code
429 ,p_activity_source_code => p_activity_source_code
430 ,p_work_order_type => p_work_order_type
431 ,p_planner => p_planner
432 ,p_firm => p_firm
433 ,p_planned => p_planned
434 ,p_notification_required => p_notification_required
435 ,p_shutdown_type_code => p_shutdown_type_code
436 ,p_tagging_required_flag => p_tagging_required_flag
437 ,x_return_status => x_return_status
438 ,x_msg_count => x_msg_count
439 ,x_msg_data => x_msg_data
440 );
441 END IF;
442
443 IF x_return_status <> fnd_api.g_ret_sts_success THEN
444 RAISE fnd_api.g_exc_error;
445 END IF;
446
447 -- End of API body.
448
449 -- Standard check of p_commit.
450 IF fnd_api.to_boolean(p_commit) THEN
451 COMMIT WORK;
452 END IF;
453
454 -- Standard call to get message count and if count is 1, get message info.
455 fnd_msg_pub.count_and_get(p_count => x_msg_count
456 ,p_data => x_msg_data);
457 EXCEPTION
458 WHEN fnd_api.g_exc_error THEN
459 ROLLBACK TO eam_org_maint_defaults_pvt;
460 x_return_status := fnd_api.g_ret_sts_error;
461 fnd_msg_pub.count_and_get(p_count => x_msg_count
462 ,p_data => x_msg_data);
463 WHEN fnd_api.g_exc_unexpected_error THEN
464 ROLLBACK TO eam_org_maint_defaults_pvt;
465 x_return_status := fnd_api.g_ret_sts_unexp_error;
466 fnd_msg_pub.count_and_get(p_count => x_msg_count
467 ,p_data => x_msg_data);
468 WHEN OTHERS THEN
469 ROLLBACK TO eam_org_maint_defaults_pvt;
470 x_return_status := fnd_api.g_ret_sts_unexp_error;
471 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
472 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
473 END IF;
474 fnd_msg_pub.count_and_get(p_count => x_msg_count
475 ,p_data => x_msg_data);
476 END update_insert_row;
477
478
479 END eam_org_maint_defaults_pvt;
480