[Home] [Help]
PACKAGE BODY: APPS.GMP_RESOURCES_PUB
Source
1 PACKAGE BODY GMP_RESOURCES_PUB AS
2 /* $Header: GMPGRESB.pls 120.6 2012/01/17 21:39:34 rpatangy ship $ */
3
4 /* =============================================================== */
5 /* Procedure: */
6 /* insert_resources */
7 /* */
8 /* DESCRIPTION: */
9 /* */
10 /* API returns (x_return_code) = 'S' if the insert into resources */
11 /* header (cr_rsrc_mst ) table is successfully. */
12 /* */
13 /* History : */
14 /* Sridhar 03-SEP-2002 Initial implementation */
15 /* =============================================================== */
16 PROCEDURE insert_resources
17 ( p_api_version IN NUMBER := 1
18 , p_init_msg_list IN BOOLEAN := TRUE
19 , p_commit IN BOOLEAN := FALSE
20 , p_resources IN cr_rsrc_mst%ROWTYPE
21 , x_message_count OUT NOCOPY NUMBER
22 , x_message_list OUT NOCOPY VARCHAR2
23 , x_return_status IN OUT NOCOPY VARCHAR2
24 ) IS
25
26 /* Local variable section */
27 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCES';
28 l_row_id ROWID;
29 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
30
31
32 /* get a record type */
33 l_resources_rec cr_rsrc_mst%ROWTYPE;
34
35 /* Define Exceptions */
36 resource_creation_failure EXCEPTION;
37 RESOURCE_REQUIRED EXCEPTION;
38 invalid_version EXCEPTION;
39 X_msg varchar2(2000) := '';
40
41 BEGIN
42 SAVEPOINT create_resources;
43 gmd_debug.log_initialize('CreateResourcesPub');
44
45 /* Set the return status to success initially */
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 l_resources_rec := p_resources;
48
49 /* Initialize message list and count if needed */
50 IF p_init_msg_list THEN
51 fnd_msg_pub.initialize;
52 END IF;
53
54 /* Make sure we are call compatible */
55 IF NOT FND_API.compatible_api_call ( GMP_RESOURCES_PUB.m_api_version
56 ,p_api_version
57 ,'INSERT_RESOURCES'
58 ,GMP_RESOURCES_PUB.m_pkg_name) THEN
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 RAISE invalid_version;
61 END IF;
62
63 v_insert_flag := 'Y';
64
65
66
67 IF l_resources_rec.resources IS NOT NULL THEN
68 /* Validation 1. Check if this resources that is created does not exists
69 in the database.
70 */
71 check_data(l_resources_rec.resources,
72 l_resources_rec.resource_desc,
73 l_resources_rec.std_usage_uom,
74 l_resources_rec.resource_class,
75 l_resources_rec.cost_cmpntcls_id,
76 l_resources_rec.min_capacity,
77 l_resources_rec.max_capacity,
78 l_resources_rec.capacity_uom,
79 l_resources_rec.capacity_constraint,
80 l_resources_rec.capacity_tolerance,
81 x_message_count,
82 x_message_list,
83 l_return_status);
84 IF l_return_status = 'E' THEN
85 RAISE resource_creation_failure;
86 ELSE
87 /* Insert the Resource Data now */
88 /* Making the Capacity Tolerance field NULL if
89 Capacity Constraint field has value = 0
90 */
91 -- 13576218 rpatangy
92 IF l_resources_rec.capacity_constraint IS NULL THEN
93 -- valid values are 0 and 1, defualt is 0
94 l_resources_rec.capacity_constraint := 0 ;
95 END IF;
96
97 IF l_resources_rec.capacity_constraint = 0 THEN
98 l_resources_rec.capacity_tolerance := NULL;
99 END IF;
100 --
101 CR_RSRC_MST_PKG.insert_row
102 ( l_row_id,
103 l_resources_rec.resources,
104 l_resources_rec.resource_class,
105 l_resources_rec.trans_cnt,
106 NVL(l_resources_rec.delete_mark,0),
107 l_resources_rec.text_code,
108 l_resources_rec.min_capacity,
109 l_resources_rec.max_capacity,
110 l_resources_rec.capacity_constraint,
111 l_resources_rec.capacity_uom,
112 l_resources_rec.std_usage_uom,
113 l_resources_rec.cost_cmpntcls_id,
114 l_resources_rec.resource_desc,
115 --l_resources_rec.creation_date, Bug 13575854
116 sysdate,
117 --l_resources_rec.created_by, Bug 13575854
118 FND_GLOBAL.user_id,
119 --l_resources_rec.last_update_date, Bug 13575854
120 sysdate,
121 --l_resources_rec.last_updated_by, Bug 13575854
122 FND_GLOBAL.user_id,
123 l_resources_rec.last_update_login,
124 l_resources_rec.capacity_tolerance,
125 l_resources_rec.utilization,
126 l_resources_rec.efficiency
127 );
128 v_insert_flag := 'N';
129 END IF;
130 ELSE
131 x_return_status := 'E';
132 X_msg := 'Resources';
133 RAISE RESOURCE_REQUIRED;
134 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
135 FND_MSG_PUB.ADD;
136 END IF; /* p_resources.resources IS NOT NULL */
137
138 fnd_msg_pub.count_and_get (
139 p_count => x_message_count
140 ,p_encoded => FND_API.g_false
141 ,p_data => x_message_list);
142
143 IF x_message_count = 0 THEN
144 gmd_debug.put_line('Resource Header was created successfully');
145 END IF;
146
147 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
148 commit;
149
150 EXCEPTION
151 WHEN resource_creation_failure OR invalid_version THEN
152 ROLLBACK TO SAVEPOINT create_resources;
153 fnd_msg_pub.count_and_get (
154 p_count => x_message_count
155 ,p_encoded => FND_API.g_false
156 ,p_data => x_message_list);
157 x_return_status := FND_API.G_RET_STS_ERROR;
158
159 WHEN RESOURCE_REQUIRED THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
162 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
163 FND_MSG_PUB.ADD;
164 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
165
166 WHEN OTHERS THEN
167 ROLLBACK TO SAVEPOINT create_resources;
168 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
169 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
170 FND_MSG_PUB.ADD;
171 fnd_msg_pub.count_and_get (
172 p_count => x_message_count
173 ,p_encoded => FND_API.g_false
174 ,p_data => x_message_list);
175 x_return_status := FND_API.g_ret_sts_unexp_error;
176 END insert_resources;
177
178 /* =============================================================== */
179 /* Procedure: */
180 /* check_data */
181 /* */
182 /* DESCRIPTION: */
183 /* */
184 /* The following Procedure checks the Record and then Inserts */
185 /* the row into cr_rsrc_mst table and Returns S code if inserted */
186 /* Successfully */
187 /* */
188 /* History : */
189 /* Sgidugu 09/03/2002 Initial implementation */
190 /* =============================================================== */
191 PROCEDURE check_data(p_resources IN VARCHAR2,
192 p_resource_desc IN VARCHAR2,
193 p_std_usage_um IN VARCHAR2,
194 p_resource_class IN VARCHAR2,
195 p_cost_cmpntcls_id IN NUMBER,
196 p_min_capacity IN NUMBER,
197 p_max_capacity IN NUMBER,
198 p_capacity_uom IN VARCHAR2,
199 p_capacity_constraint IN NUMBER,
200 p_capacity_tolerance IN NUMBER,
201 x_message_count OUT NOCOPY NUMBER,
202 x_message_list OUT NOCOPY VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2) IS
204 CURSOR Cur_resources IS
205 SELECT COUNT(1)
206 FROM cr_rsrc_mst
207 where resources = p_resources
208 and delete_mark = 0;
209
210 CURSOR Cur_std_usage_um IS
211 SELECT COUNT(1)
212 --FROM sy_uoms_mst vkinduri BUG 13575854
213 FROM mtl_units_of_measure
214 WHERE uom_code = p_std_usage_um;
215
216
217 CURSOR Cur_resource_class IS
218 SELECT COUNT(1)
219 FROM cr_rsrc_cls
220 WHERE resource_class = p_resource_class
221 AND delete_mark = 0;
222
223 CURSOR Cur_cost_cmpntcls_code IS
224 SELECT COUNT(1)
225 FROM cm_cmpt_mst
226 WHERE cost_cmpntcls_id = p_cost_cmpntcls_id
227 AND delete_mark = 0;
228
229 l_return_val varchar2(16);
230 l_count1 number := 0;
231 l_count2 number := 0;
232 l_count3 number := 0;
233 l_count4 number := 0;
234
235 INVALID_MIN_MAX EXCEPTION;
236 INVALID_USAGE_UM EXCEPTION;
237 PS_DUP_REC EXCEPTION;
238 INVALID_RSRC_CLASS EXCEPTION;
239 INVALID_VALUE EXCEPTION;
240 RESOURCE_DESC_REQUIRED EXCEPTION;
241 MIN_MAX_CAPACITY_REQUIRED EXCEPTION;
242 STD_USAGE_UM_REQUIRED EXCEPTION; -- Vpedarla Bug: 7015717
243 COST_CMPTCLSID_REQUIRED EXCEPTION; -- vkinduri Bug 13575854
244 INVALID_CMPTCLS_ID EXCEPTION; -- vkinduri Bug 13575854
245 x_temp number;
246 X_field varchar2(2000) := '';
247 X_value varchar2(2000) := '';
248 X_msg varchar2(2000) := '';
249
250 BEGIN
251 /* Check Resources if they already exist */
252
253 IF v_insert_flag = 'Y' then
254 x_return_status := 'S';
255 OPEN Cur_resources;
256 FETCH Cur_resources INTO l_count1;
257 CLOSE Cur_resources;
258 IF l_count1 > 0 then
259 x_return_status := 'E';
260 RAISE PS_DUP_REC;
261 END IF; /* End if for Duplicate Record */
262 END IF; /* End if for Insert flag = 'Y' */
263
264 /* Check Usage_um if they already exist */
265
266 -- Vpedarla Bug: 7015717 Added below condition
267 IF p_std_usage_um is NULL
268 THEN
269 x_return_status := 'E';
270 X_msg := 'Standard Usage UOM';
271 RAISE STD_USAGE_UM_REQUIRED;
272 END IF;
273
274 IF p_std_usage_um is NOT NULL then
275 x_return_status := 'S';
276 OPEN Cur_std_usage_um;
277 FETCH Cur_std_usage_um INTO l_count2;
278 CLOSE Cur_std_usage_um;
279 --
280 IF l_count2 = 0 then
281 x_return_status := 'E';
282 RAISE INVALID_USAGE_UM;
283 END IF;
284 END IF; /* End if for std_usage_um */
285
286 /* Check Resource Class if they already exist and
287 if it is a valid entry */
288
289 IF p_resource_class is NOT NULL then
290 x_return_status := 'S';
291 OPEN Cur_resource_class;
292 FETCH Cur_resource_class INTO l_count3;
293 CLOSE Cur_resource_class;
294 --
295 IF l_count3 = 0 then
296 x_return_status := 'E';
297 RAISE INVALID_RSRC_CLASS;
298 END IF;
299 END IF; /* End if for resource_class */
300
301 /* Check Cost Component Id if they already exist
302 and if it is a valid entry */
303
304 IF p_cost_cmpntcls_id is NOT NULL then
305 x_return_status := 'S';
306 OPEN Cur_cost_cmpntcls_code;
307 FETCH Cur_cost_cmpntcls_code INTO l_count4;
308 CLOSE Cur_cost_cmpntcls_code;
309 --
310 IF l_count4 = 0 then
311 x_return_status := 'E';
312 -- vkinduri Bug 13575854
313 X_msg := 'Invalid Cost Component Class ID';
314 RAISE INVALID_CMPTCLS_ID ;
315 END IF;
316 ELSE
317 x_return_status := 'E';
318 -- vkinduri Bug 13575854
319 X_msg := 'Cost Component Class ID';
320 RAISE COST_CMPTCLSID_REQUIRED ;
321 END IF; /* End if for cost_cmpntcls_id */
322 --
323 IF p_resource_desc is NULL
324 THEN
325 x_return_status := 'E';
326 X_msg := 'Resource Description';
327 RAISE RESOURCE_DESC_REQUIRED;
328 END IF;
329 /* Check if Min Capacity is greater than Max Capacity */
330
331 x_return_status := 'S';
332 IF nvl(p_min_capacity,0) > nvl(p_max_capacity,999999.99) THEN
333 x_return_status := 'E';
334 RAISE INVALID_MIN_MAX;
335 END IF ;
336 --
337 /* Check if Max Capacity is lesser than Min Capacity */
338 x_return_status := 'S';
339 IF nvl(p_min_capacity,0) > nvl(p_max_capacity,999999.99) THEN
340 x_return_status := 'E';
341 RAISE INVALID_MIN_MAX;
342 END IF ;
343 --
344
345 IF p_capacity_constraint NOT IN (0,1)
346 THEN
347 x_return_status := 'E';
348 X_field := 'Capacity Constraint (Valid 0/1)';
349 X_value := p_capacity_constraint;
350 RAISE INVALID_VALUE;
351 END IF ;
352 --
353 IF (p_capacity_constraint = 1)
354 THEN
355 IF (p_min_capacity IS NULL) OR
356 (p_max_capacity IS NULL) OR (p_capacity_uom is NULL)
357 THEN
358 x_return_status := 'E';
359 X_msg := 'Min/Max/Capacity Uom';
360 RAISE MIN_MAX_CAPACITY_REQUIRED;
361 END IF ;
362 END IF ;
363 --
364 EXCEPTION
365 WHEN INVALID_MIN_MAX THEN
366 x_return_status := FND_API.G_RET_STS_ERROR;
367 FND_MESSAGE.SET_NAME('GMP','GMP_MIN_MAX_CAPACITY');
368 FND_MSG_PUB.ADD;
369 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
370 --
371 WHEN MIN_MAX_CAPACITY_REQUIRED THEN
372 x_return_status := FND_API.G_RET_STS_ERROR;
373 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
374 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
375 FND_MSG_PUB.ADD;
376 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
377 --
378 /* Bug: 7015717 Vpedarla */
379 WHEN STD_USAGE_UM_REQUIRED THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
382 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
383 FND_MSG_PUB.ADD;
384 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
385 --
386 WHEN PS_DUP_REC THEN
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 FND_MESSAGE.SET_NAME('GMP','PS_DUP_REC');
389 FND_MSG_PUB.ADD;
390 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
391 --
392 WHEN INVALID_VALUE THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MESSAGE.SET_NAME('GMP','GMP_INVALID_VALUE');
395 FND_MESSAGE.SET_TOKEN('FIELD',X_field);
396 FND_MESSAGE.SET_TOKEN('VALUE',X_value);
397 FND_MSG_PUB.ADD;
398 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
399 --
400 WHEN RESOURCE_DESC_REQUIRED THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
403 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
404 FND_MSG_PUB.ADD;
405 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
406 --
407 WHEN INVALID_USAGE_UM THEN
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 FND_MESSAGE.SET_NAME('GMA','SY_INVALID_UM_CODE');
410 FND_MSG_PUB.ADD;
411 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
412 --
413 WHEN INVALID_RSRC_CLASS THEN
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 FND_MESSAGE.SET_NAME('GMP','CR_INVALID_RSRC_CLASS');
416 FND_MSG_PUB.ADD;
417 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
418 -- vkinduri Bug 13575854
419 WHEN COST_CMPTCLSID_REQUIRED THEN
420 x_return_status := FND_API.G_RET_STS_ERROR;
421 FND_MESSAGE.SET_NAME('GMP','GMP_VALUE_REQUIRED');
422 FND_MESSAGE.SET_TOKEN('VALUE_REQUIRED',X_msg);
423 FND_MSG_PUB.ADD;
424 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
425 -- vkinduri Bug 13575854
426 WHEN INVALID_CMPTCLS_ID THEN
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 FND_MESSAGE.SET_NAME('GMP','INVALID_COST_CMPTCLSID');
429 FND_MSG_PUB.ADD;
430 FND_MSG_PUB.Count_And_Get(p_count=>x_message_count, p_data=>x_message_list);
431
432
433
434 END check_data; /* End of Procedure check_resources */
435
436 /* =============================================================== */
437 /* Procedure: */
438 /* update_resources */
439 /* */
440 /* DESCRIPTION: */
441 /* */
442 /* API returns (x_return_code) = 'S' if the update into Generic */
443 /* Resource Table */
444 /* */
445 /* History : */
446 /* Sgidugu 09/04/2002 Initial implementation */
447 /* =============================================================== */
448 PROCEDURE update_resources
449 ( p_api_version IN NUMBER := 1
450 , p_init_msg_list IN BOOLEAN := TRUE
451 , p_commit IN BOOLEAN := FALSE
452 , p_resources IN cr_rsrc_mst%ROWTYPE
453 , x_message_count OUT NOCOPY NUMBER
454 , x_message_list OUT NOCOPY VARCHAR2
455 , x_return_status OUT NOCOPY VARCHAR2
456 ) IS
457
458 /* Local variable section */
459 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCES';
460 l_row_id ROWID;
461 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
462
463 /* Define Exceptions */
464 resource_update_failure EXCEPTION;
465 invalid_version EXCEPTION;
466
467 BEGIN
468 SAVEPOINT update_resources;
469 gmd_debug.log_initialize('UpdateResourcePub');
470
471 /* Set the return status to success initially */
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 /* Initialize message list and count if needed */
475 IF p_init_msg_list THEN
476 fnd_msg_pub.initialize;
477 END IF;
478
479 /* Make sure we are call compatible */
480 IF NOT FND_API.compatible_api_call ( GMP_RESOURCES_PUB.m_api_version
481 ,p_api_version
482 ,l_api_name
483 ,GMP_RESOURCES_PUB.m_pkg_name) THEN
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 RAISE invalid_version;
486 END IF;
487
488 IF p_resources.resources IS NOT NULL THEN
489 /* Validation 1. Check if this resources that is created does not exists
490 in the the database.
491 */
492 check_data(p_resources.resources,
493 p_resources.resource_desc,
494 p_resources.std_usage_uom,
495 p_resources.resource_class,
496 p_resources.cost_cmpntcls_id,
497 p_resources.min_capacity,
498 p_resources.max_capacity,
499 p_resources.capacity_uom,
500 p_resources.capacity_constraint,
501 p_resources.capacity_tolerance,
502 x_message_count,
503 x_message_list,
504 l_return_status);
505
506 IF l_return_status = 'E' THEN
507 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_DUPLICATION');
508 FND_MSG_PUB.ADD;
509 RAISE resource_update_failure;
510 ELSE
511 /* Update the Resource Data now */
512 CR_RSRC_MST_PKG.update_row(
513 p_resources.resources,
514 p_resources.resource_class,
515 p_resources.trans_cnt,
516 NVL(p_resources.delete_mark,0),
517 p_resources.text_code,
518 p_resources.min_capacity,
519 p_resources.max_capacity,
520 p_resources.capacity_constraint,
521 p_resources.capacity_uom,
522 p_resources.std_usage_uom,
523 p_resources.cost_cmpntcls_id,
524 p_resources.resource_desc,
525 --p_resources.last_update_date,
526 sysdate, -- vkinduri Bug 13575854
527 --p_resources.last_updated_by,
528 FND_GLOBAL.user_id, -- vkinduri Bug 13575854
529 --p_resources.last_update_login,
530 FND_GLOBAL.user_id, -- vkinduri Bug 13575854
531 p_resources.capacity_tolerance,
532 p_resources.utilization,
533 p_resources.efficiency
534 );
535 END IF;
536 END IF;
537
538 /* Check if work was done */
539 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
540 RAISE resource_update_failure;
541 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
542
543 fnd_msg_pub.count_and_get (
544 p_count => x_message_count
545 ,p_encoded => FND_API.g_false
546 ,p_data => x_message_list);
547
548 IF x_message_count = 0 THEN
549 gmd_debug.put_line('Resource was Updated successfullly');
550 END IF;
551
552 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
553
554 EXCEPTION
555 WHEN resource_update_failure OR invalid_version THEN
556 ROLLBACK TO SAVEPOINT update_resources;
557 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
558 fnd_msg_pub.count_and_get (
559 p_count => x_message_count
560 ,p_encoded => FND_API.g_false
561 ,p_data => x_message_list);
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 WHEN OTHERS THEN
564 ROLLBACK TO SAVEPOINT update_resources;
565 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
566 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
567 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
568 FND_MSG_PUB.ADD;
569 fnd_msg_pub.count_and_get (
570 p_count => x_message_count
571 ,p_encoded => FND_API.g_false
572 ,p_data => x_message_list);
573 x_return_status := FND_API.g_ret_sts_unexp_error;
574 END update_resources;
575
576 /* =============================================================== */
577 /* Procedure: */
578 /* delete_resources */
579 /* */
580 /* DESCRIPTION: */
581 /* */
582 /* API returns (x_return_code) = 'S' if the delete Resources */
583 /* was Successful */
584 /* */
585 /* History : */
586 /* Sgidugu 09/04/2002 Initial implementation */
587 /* =============================================================== */
588 PROCEDURE delete_resources
589 ( p_api_version IN NUMBER := 1
590 , p_init_msg_list IN BOOLEAN := TRUE
591 , p_commit IN BOOLEAN := FALSE
592 , p_resources IN cr_rsrc_mst.resources%TYPE
593 , x_message_count OUT NOCOPY NUMBER
594 , x_message_list OUT NOCOPY VARCHAR2
595 , x_return_status OUT NOCOPY VARCHAR2
596 ) IS
597 CURSOR Cur_resources IS
598 SELECT count(1)
599 FROM cr_rsrc_mst
600 where resources = p_resources;
601
602 l_counter number;
603
604 /* Local variable section */
605 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCES';
606 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
607
608 /* Define Exceptions */
609 resource_delete_failure EXCEPTION;
610 invalid_version EXCEPTION;
611 BEGIN
612 SAVEPOINT delete_resources;
613 gmd_debug.log_initialize('DeleteResourcePub');
614
615 /* Set the return status to success initially */
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 /* Initialize message list and count if needed */
619 IF p_init_msg_list THEN
620 fnd_msg_pub.initialize;
621 END IF;
622
623 /* Make sure we are call compatible */
624 IF NOT FND_API.compatible_api_call ( GMP_RESOURCES_PUB.m_api_version
625 ,p_api_version
626 ,l_api_name
627 ,GMP_RESOURCES_PUB.m_pkg_name) THEN
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 RAISE invalid_version;
630 END IF;
631
632 OPEN Cur_resources;
633 FETCH Cur_resources INTO l_counter;
634 CLOSE Cur_resources;
635
636 IF (l_counter = 0 ) then
637 l_return_status := 'E';
638 GMD_DEBUG.PUT_LINE('Resource to be deleted Does Not Exist ');
639 FND_MSG_PUB.ADD;
640 RAISE resource_delete_failure;
641 ELSE
642 delete from cr_rsrc_mst_tl
643 where resources = p_resources;
644 --
645 delete from cr_rsrc_mst_b
646 where resources = p_resources;
647 l_return_status := 'S';
648 END IF;
649 --
650 fnd_msg_pub.count_and_get (
651 p_count => x_message_count
652 ,p_encoded => FND_API.g_false
653 ,p_data => x_message_list);
654
655 IF x_message_count = 0 THEN
656 gmd_debug.put_line('Resource was deleted successfully');
657 END IF;
658
659 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
660
661 EXCEPTION
662 WHEN resource_delete_failure OR invalid_version THEN
663 ROLLBACK TO SAVEPOINT delete_resources;
664 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
665 fnd_msg_pub.count_and_get (
666 p_count => x_message_count
667 ,p_encoded => FND_API.g_false
668 ,p_data => x_message_list);
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 WHEN OTHERS THEN
671 ROLLBACK TO SAVEPOINT delete_resources;
672 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
673 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
674 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
675 FND_MSG_PUB.ADD;
676 fnd_msg_pub.count_and_get (
677 p_count => x_message_count
678 ,p_encoded => FND_API.g_false
679 ,p_data => x_message_list);
680 x_return_status := FND_API.g_ret_sts_unexp_error;
681 END delete_resources;
682
683 END GMP_RESOURCES_PUB;