[Home] [Help]
PACKAGE BODY: APPS.GMD_ACTIVITIES_PUB
Source
1 PACKAGE BODY GMD_ACTIVITIES_PUB AS
2 /* $Header: GMDPACTB.pls 115.4 2004/02/25 17:42:27 nsrivast noship $
3 **************************************************************************
4 * *
5 * Package GMD_ACTIVITY_PUB *
6 * *
7 * Contents: INSERT_ACTIVITY *
8 * UPDATE_ACTIVITY *
9 * DELETE_ACTIVITY *
10 * *
11 * Use This is the public layer of the GMD Activity API *
12 * *
13 * *
14 * History *
15 * Written by Sandra Dulyk, OPM Development *
16 * 25-NOV-2002 Thomas Daniel Bug# 2679110 *
17 * Rewrote the procedures to handle the *
18 * errors properly and also to handle *
19 * further validations *
20 * 20-FEB-2004 Bug 3222090, NSRIVAST *
21 * Removed call to FND_PROFILE.VALUE('AFLOG_ENABLED') *
22 **************************************************************************
23 */
24
25 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
26 --Forward declaration.
27 FUNCTION set_debug_flag RETURN VARCHAR2;
28 l_debug VARCHAR2(1) := set_debug_flag;
29
30 FUNCTION set_debug_flag RETURN VARCHAR2 IS
31 l_debug VARCHAR2(1):= 'N';
32 BEGIN
33 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
34 l_debug := 'Y';
35 END IF;
36 RETURN l_debug;
37 END set_debug_flag;
38 -- Bug 3222090, NSRIVAST, END
39
40
41
42 /*===========================================================================================
43 Procedure
44 insert_activity
45 Description
46 This particular procedure is used to insert an activity
47 Parameters
48 ================================================ */
49 PROCEDURE insert_activity (
50 p_api_version IN NUMBER
51 , p_init_msg_list IN BOOLEAN
52 , p_commit IN BOOLEAN
53 , p_activity_tbl IN gmd_activities_pub.gmd_activities_tbl_type
54 , x_message_count OUT NOCOPY NUMBER
55 , x_message_list OUT NOCOPY VARCHAR2
56 , x_return_status OUT NOCOPY VARCHAR2) IS
57
58 l_retn_status VARCHAR2(1);
59 l_api_version NUMBER := 1.0;
60 l_exist NUMBER(5);
61
62 setup_failure EXCEPTION;
63 invalid_version EXCEPTION;
64 ins_activity_err EXCEPTION;
65
66 BEGIN
67 SAVEPOINT insert_activity;
68
69 IF (l_debug = 'Y') THEN
70 gmd_debug.log_initialize('InsActv');
71 END IF;
72
73 /* Initialize message list and count if needed */
74 IF p_init_msg_list THEN
75 fnd_msg_pub.initialize;
76 END IF;
77
78 /* Initially let us assign the return status to success */
79 x_return_status := FND_API.g_ret_sts_success;
80
81 IF NOT gmd_api_grp.setup_done THEN
82 gmd_api_grp.setup_done := gmd_api_grp.setup;
83 END IF;
84 IF NOT gmd_api_grp.setup_done THEN
85 RAISE setup_failure;
86 END IF;
87
88 /* Make sure we are call compatible */
89 IF NOT FND_API.compatible_api_call(l_api_version
90 ,p_api_version
91 ,'insert_activity'
92 ,'gmd_activities_pub') THEN
93 RAISE invalid_version;
94 END IF;
95
96 /* Loop through records in activity table and perform validations for each record*/
97 FOR i IN 1 .. p_activity_tbl.count LOOP
98
99 IF (l_debug = 'Y') THEN
100 gmd_debug.put_line(' Start of LOOP. Activity is ' || p_activity_tbl(i).activity);
101 END IF;
102
103 /* Activity must be passed, otherwise give error */
104 IF p_activity_tbl(i).activity IS NULL THEN
105 IF (l_debug = 'Y') THEN
106 gmd_debug.put_line('activity required');
107 END IF;
108 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
109 FND_MESSAGE.SET_TOKEN ('MISSING', 'ACTIVITY');
110 FND_MSG_PUB.ADD;
111 x_return_status := FND_API.g_ret_sts_error;
112 END IF;
113
114 /* Cost Analysis Code Validations - Must be passed, otherwise give error */
115 /* Also, cost analysis code must be defined in cm_alys_mst, else give error */
116 IF p_activity_tbl(i).cost_analysis_code IS NULL THEN
117 IF (l_debug = 'Y') THEN
118 gmd_debug.put_line('cost analysis required');
119 END IF;
120
121 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
122 FND_MESSAGE.SET_TOKEN ('MISSING', 'COST_ANALYSIS_CODE');
123 FND_MSG_PUB.ADD;
124 x_return_status := FND_API.g_ret_sts_error;
125 ELSE
126 IF GMDOPVAL_PUB.check_cost_analysis (pcost_analysis_code => p_activity_tbl(i).cost_analysis_code) <> 0 THEN
127 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
128 FND_MSG_PUB.ADD;
129 x_return_status := FND_API.g_ret_sts_error;
130 END IF;
131 END IF;
132
133 /* Description must be passed, otherwise give error */
134 IF p_activity_tbl(i).activity_desc IS NULL THEN
135 IF (l_debug = 'Y') THEN
136 gmd_debug.put_line('activity desc required');
137 END IF;
138 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
139 FND_MESSAGE.SET_TOKEN ('MISSING', 'ACTIVITY_DESC');
140 FND_MSG_PUB.ADD;
141 x_return_status := FND_API.g_ret_sts_error;
142 END IF;
143
144 END LOOP;
145
146 IF x_return_status = 'S' THEN
147 GMD_ACTIVITIES_PVT.insert_activity(p_activity_tbl => p_activity_tbl,
148 x_message_count => x_message_count,
149 x_message_list => x_message_list,
150 x_return_status => l_retn_status);
151 IF l_retn_status <> FND_API.g_ret_sts_success THEN
152 RAISE ins_activity_err;
153 END IF;
154
155 IF p_commit THEN
156 COMMIT;
157 END IF;
158
159 /* Adding message to stack indicating the success of the routine */
160 gmd_api_grp.log_message ('GMD_SAVED_CHANGES');
161 END IF;
162
163 FND_MSG_PUB.count_and_get(p_count => x_message_count
164 ,p_data => x_message_list);
165
166 EXCEPTION
167 WHEN setup_failure OR invalid_version THEN
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 ROLLBACK TO SAVEPOINT insert_activity;
170 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
171 P_data => x_message_list);
172 WHEN ins_activity_err THEN
173 x_return_status := l_retn_status;
174 ROLLBACK TO SAVEPOINT insert_activity;
175 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
176 P_data => x_message_list);
177 WHEN OTHERS THEN
178 ROLLBACK TO SAVEPOINT insert_activity;
179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
180 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
181 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
182 FND_MSG_PUB.ADD;
183 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
184 P_data => x_message_list);
185 END Insert_Activity;
186
187
188 /*==========================================================
189 Procedure
190 update_activity
191 Description
192 This particular procedure is used to update an activity
193 Parameters
194 ================================================ */
195 PROCEDURE update_activity (
196 p_api_version IN NUMBER
197 ,p_init_msg_list IN BOOLEAN
198 ,p_commit IN BOOLEAN
199 ,p_activity IN gmd_activities.activity%TYPE
200 ,p_update_table IN gmd_activities_pub.update_tbl_type
201 ,x_message_count OUT NOCOPY NUMBER
202 ,x_message_list OUT NOCOPY VARCHAR2
203 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
204
205 l_retn_status VARCHAR2(1);
206 l_api_version NUMBER := 1.0;
207
208 setup_failure EXCEPTION;
209 invalid_version EXCEPTION;
210 upd_activity_err EXCEPTION;
211
212 CURSOR Cur_check_activity (v_activity VARCHAR2) IS
213 SELECT 1
214 FROM gmd_activities_b
215 WHERE activity = v_activity;
216
217 l_exist NUMBER(5);
218
219 BEGIN
220 SAVEPOINT update_activity;
221
222 IF (l_debug = 'Y') THEN
223 gmd_debug.log_initialize('UpdActv');
224 END IF;
225
226 /* Initialize message list and count if needed */
227 IF p_init_msg_list THEN
228 fnd_msg_pub.initialize;
229 END IF;
230
231 IF NOT gmd_api_grp.setup_done THEN
232 gmd_api_grp.setup_done := gmd_api_grp.setup;
233 END IF;
234 IF NOT gmd_api_grp.setup_done THEN
235 RAISE setup_failure;
236 END IF;
237
238 /* Make sure we are call compatible */
239 IF NOT FND_API.compatible_api_call(l_api_version
240 ,p_api_version
241 ,'update_activity'
242 ,'gmd_activities_pub') THEN
243 RAISE invalid_version;
244 END IF;
245
246 /* Initially let us assign the return status to success */
247 x_return_status := FND_API.g_ret_sts_success;
248
249 IF (l_debug = 'Y') THEN
250 gmd_debug.put_line('Start of update_activity PUB');
251 END IF;
252
253 /* Activity must be passed, otherwise give error */
254 IF p_activity IS NULL THEN
255 IF (l_debug = 'Y') THEN
256 gmd_debug.put_line('activity required');
257 END IF;
258 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
259 FND_MESSAGE.SET_TOKEN ('MISSING', 'ACTIVITY');
260 FND_MSG_PUB.ADD;
261 x_return_status := FND_API.g_ret_sts_error;
262 ELSE
263 /* Check for the existense of activity */
264 OPEN Cur_check_activity(p_activity);
265 FETCH Cur_check_activity INTO l_exist;
266 IF (Cur_check_activity%NOTFOUND) THEN
267 gmd_api_grp.log_message ('FM_INVACTIVITY');
268 x_return_status := FND_API.g_ret_sts_error;
269 END IF;
270 CLOSE Cur_check_activity;
271 END IF;
272
273 /* Loop thru cols to be updated - verify col and value are present */
274 FOR i in 1 .. p_update_table.count LOOP
275 /* Col_to_update and value must be passed, otherwise give error */
276 IF p_update_table(i).p_col_to_update IS NULL THEN
277 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
278 FND_MESSAGE.SET_TOKEN ('MISSING', 'COL_TO_UPDATE');
279 FND_MSG_PUB.ADD;
280 x_return_status := FND_API.g_ret_sts_error;
281 ELSIF p_update_table(i).p_value IS NULL THEN
282 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
283 FND_MESSAGE.SET_TOKEN ('MISSING', 'P_VALUE');
284 FND_MSG_PUB.ADD;
285 x_return_status := FND_API.g_ret_sts_error;
286 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK' THEN
287 GMDRTVAL_PUB.check_delete_mark ( Pdelete_mark => p_update_table(i).p_value,
288 x_return_status => l_retn_status);
289 IF l_retn_status <> 'S' THEN /* it indicates that invalid value has been passed */
290 FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
291 FND_MSG_PUB.ADD;
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 END IF;
294 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'ACTIVITY_DESC' THEN
295 IF p_update_table(i).p_value IS NULL THEN
296 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
297 FND_MESSAGE.SET_TOKEN ('MISSING', 'ACTIVITY_DESC');
298 FND_MSG_PUB.ADD;
299 x_return_status := FND_API.g_ret_sts_error;
300 END IF;
301 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'COST_ANALYSIS_CODE' THEN
302 IF p_update_table(i).p_value IS NULL THEN
303 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
304 FND_MESSAGE.SET_TOKEN ('MISSING', 'COST_ANALYSIS_CODE');
305 FND_MSG_PUB.ADD;
306 x_return_status := FND_API.g_ret_sts_error;
307 ELSE
308 IF GMDOPVAL_PUB.check_cost_analysis (pcost_analysis_code => p_update_table(i).p_value) <> 0 THEN
309 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
310 FND_MSG_PUB.ADD;
311 x_return_status := FND_API.g_ret_sts_error;
312 END IF;
313 END IF;
314 END IF;
315 END LOOP;
316
317 IF x_return_status = 'S' THEN
318 GMD_ACTIVITIES_PVT.update_activity(p_activity => p_activity
319 , p_update_table => p_update_table
320 , x_message_count => x_message_count
321 , x_message_list => x_message_list
322 , x_return_status => l_retn_status);
323 IF l_retn_status <> FND_API.g_ret_sts_success THEN
324 RAISE upd_activity_err;
325 END IF;
326
327 IF p_commit THEN
328 COMMIT;
329 END IF;
330
331 /* Adding message to stack indicating the success of the routine */
332 gmd_api_grp.log_message ('GMD_SAVED_CHANGES');
333 END IF;
334
335 FND_MSG_PUB.count_and_get(p_count => x_message_count
336 ,p_data => x_message_list);
337
338 EXCEPTION
339 WHEN setup_failure OR invalid_version THEN
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 ROLLBACK to SAVEPOINT update_activity;
342 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
343 P_data => x_message_list);
344 WHEN upd_activity_err THEN
345 x_return_status := l_retn_status;
346 ROLLBACK to SAVEPOINT update_activity;
347 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
348 P_data => x_message_list);
349 WHEN OTHERS THEN
350 ROLLBACK to SAVEPOINT update_activity;
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
353 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
354 FND_MSG_PUB.ADD;
355 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
356 P_data => x_message_list);
357
358 END update_activity;
359
360 /*===========================================================================================
361 Procedure
362 delete_activity
363 Description
364 This particular procedure is used to set delete_mark = 1 for an activity
365 Parameters
366 ================================================ */
367 PROCEDURE delete_activity (
368 p_api_version IN NUMBER
369 ,p_init_msg_list IN BOOLEAN
370 ,p_commit IN BOOLEAN
371 ,p_activity IN gmd_activities.activity%TYPE
372 ,x_message_count OUT NOCOPY NUMBER
373 ,x_message_list OUT NOCOPY VARCHAR2
374 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
375
376 v_update_table gmd_activities_pub.update_tbl_type;
377 l_retn_status VARCHAR2(1);
378 del_activity_err EXCEPTION;
379 BEGIN
380 SAVEPOINT delete_activity;
381
382 IF (l_debug = 'Y') THEN
383 gmd_debug.log_initialize('DelActv');
384 END IF;
385
386 /* Initially let us assign the return status to success */
387 x_return_status := FND_API.g_ret_sts_success;
388
389 v_update_table(1).p_col_to_update := 'DELETE_MARK';
390 v_update_table(1).p_value := '1';
391
392 update_activity(p_api_version => p_api_version
393 ,p_init_msg_list => p_init_msg_list
394 ,p_activity => p_activity
395 ,p_update_table => v_update_table
396 , x_message_count => x_message_count
397 , x_message_list => x_message_list
398 , x_return_status => l_retn_status);
399 IF l_retn_status <> FND_API.g_ret_sts_success THEN
400 RAISE del_activity_err;
401 END IF;
402
403 IF p_commit THEN
404 COMMIT;
405 END IF;
406
407 FND_MSG_PUB.count_and_get(p_count => x_message_count
408 ,p_data => x_message_list);
409
410 EXCEPTION
411 WHEN del_activity_err THEN
412 x_return_status := l_retn_status;
413 ROLLBACK to SAVEPOINT delete_activity;
414 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
415 P_data => x_message_list);
416 WHEN OTHERS THEN
417 ROLLBACK to SAVEPOINT delete_activity;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
420 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
421 FND_MSG_PUB.ADD;
422 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
423 P_data => x_message_list);
424
425 END delete_activity;
426
427 END GMD_ACTIVITIES_PUB;