1 PACKAGE BODY GMD_OPERATION_RESOURCES_PUB AS
2 /* $Header: GMDPOPRB.pls 120.1.12010000.2 2008/11/06 21:48:32 rpatangy ship $
3 +=========================================================================+
4 | FILENAME |
5 | GMDPOPRB.pls |
6 | |
7 | DESCRIPTION |
8 | This package contains public definitions for |
9 | creating, modifying, deleting operation resources |
10 | |
11 | HISTORY |
12 | 22-AUG-2002 Sandra Dulyk Created |
13 | 25-NOV-2002 Thomas Daniel Bug# 2679110 |
14 | Rewrote the procedures to handle the |
15 | errors properly and also to handle |
16 | further validations |
17 | 20-FEB-2004 NSRIVAST Bug# 3222090, Removed call to |
18 | FND_PROFILE.VALUE('AFLOG_ENABLED') |
19 | 20-APR-2006 KMOTUPAL Bug# 5172254 Closed the cursors before|
20 | raising the exception if a validation |
21 | fails. |
22 | 26-MAY-2008 KBANDDYO Bug#7118558: column um_code is replaced |
23 | with uom_code and table sy_uoms_mst is |
24 | replaced with mtl_units_of_measure |
25 +=========================================================================+
26 API Name : GMD_OPERATION_RESOURCES_PUB
27 Type : Public
28 Function : This package contains public procedures used to create, modify, and delete operation resources
29 Pre-reqs : N/A
30 Parameters: Per function
31
32 Current Vers : 1.0
33
34 Previous Vers : 1.0
35
36 Initial Vers : 1.0
37 Notes
38 */
39
40 /* Global Cursors */
41 CURSOR check_oprn_line_id(V_oprn_line_id NUMBER) IS
42 SELECT 1
43 FROM gmd_operation_activities
44 WHERE oprn_line_id = v_oprn_line_id;
45
46
47 --Bug#7118558
48 CURSOR check_uom(v_uom VARCHAR2) IS
49 SELECT 1
50 FROM mtl_units_of_measure
51 WHERE uom_Code = v_uom
52 AND (disable_date IS NULL or disable_date > SYSDATE); /* pku */
53
54
55 CURSOR check_cost_cmpntcls_id(v_cost_cmpntcls_id NUMBER) IS
56 SELECT 1
57 FROM cm_cmpt_mst
58 WHERE COST_CMPNTCLS_id = v_cost_cmpntcls_id
59 AND delete_mark = 0;
60
61 CURSOR check_cost_analysis_code(v_cost_analysis_code VARCHAR2) IS
62 SELECT 1
63 FROM cm_alys_mst
64 WHERE cost_analysis_code = v_cost_analysis_code
65 AND delete_mark = 0;
66
67 CURSOR check_Resource(p_oprn_line_id NUMBER, p_resources VARCHAR2) IS
68 SELECT 1
69 FROM gmd_operation_resources
70 WHERE oprn_line_id = p_oprn_line_id
71 AND resources = p_resources;
72
73 CURSOR check_one_prim_rsrc (V_oprn_line_id NUMBER) IS
74 SELECT COUNT(1)
75 FROM gmd_operation_resources
76 WHERE oprn_line_id = V_oprn_line_id
77 AND prim_rsrc_ind = 1;
78
79 CURSOR check_atleast_one (V_oprn_line_id NUMBER, V_resources VARCHAR2) IS
80 SELECT COUNT(1)
81 FROM gmd_operation_resources
82 WHERE oprn_line_id = V_oprn_line_id
83 AND (V_resources IS NULL OR resources <> V_resources)
84 AND prim_rsrc_ind = 1;
85
86
87 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
88 --Forward declaration.
89 FUNCTION set_debug_flag RETURN VARCHAR2;
90 l_debug VARCHAR2(1) := set_debug_flag;
91
92 FUNCTION set_debug_flag RETURN VARCHAR2 IS
93 l_debug VARCHAR2(1):= 'N';
94 BEGIN
95 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
96 l_debug := 'Y';
97 END IF;
98 RETURN l_debug;
99 END set_debug_flag;
100 --Bug 3222090, NSRIVAST 20-FEB-2004, END
101
102 /*================================================
103 Procedure
104 insert_operation_resources
105 Description
106 This particular procedure is used to insert an
107 operation resources Parameters
108 ================================================ */
109 PROCEDURE insert_operation_resources
110 ( p_api_version IN NUMBER
111 , p_init_msg_list IN BOOLEAN
112 , p_commit IN BOOLEAN
113 , p_oprn_line_id IN gmd_operation_activities.oprn_line_id%TYPE
114 , p_oprn_rsrc_tbl IN gmd_operation_resources_pub.gmd_oprn_resources_tbl_type
115 , x_message_count OUT NOCOPY NUMBER
116 , x_message_list OUT NOCOPY VARCHAR2
117 , x_return_status OUT NOCOPY VARCHAR2) IS
118
119 v_resources gmd_operation_resources.resources%TYPE;
120 v_uom gmd_operation_resources.resource_usage_uom%TYPE;
121 v_cost_cmpntcls_id gmd_operation_resources.cost_cmpntcls_id%TYPE;
122 v_cost_analysis_code gmd_operation_resources.cost_analysis_code%TYPE;
123 v_count NUMBER DEFAULT 0;
124 l_return_status VARCHAR2(1);
125 l_api_version NUMBER := 1.0;
126
127 setup_failure EXCEPTION;
128 invalid_version EXCEPTION;
129 ins_oprn_rsrc_err EXCEPTION;
130 inv_resource_ind EXCEPTION;
131
132 CURSOR get_oprn_id(V_oprn_line_id NUMBER) IS
133 SELECT oprn_id
134 FROM gmd_operation_activities
135 WHERE oprn_line_id = V_oprn_line_id;
136
137 CURSOR fetch_proc_uom (V_oprn_line_id NUMBER) IS
138 SELECT process_qty_uom
139 FROM gmd_operations a, gmd_operation_activities b
140 WHERE a.oprn_id = b.oprn_id
141 AND b.oprn_line_id = V_oprn_line_id;
142
143 CURSOR get_resource_det (V_resources VARCHAR2) IS
144 SELECT cr.cost_cmpntcls_id
145 FROM cr_rsrc_mst cr, cm_cmpt_mst cm
146 WHERE cr.cost_cmpntcls_id = cm.cost_cmpntcls_id
147 AND cr.resources = V_resources
148 AND cr.delete_mark = 0
149 AND cm.delete_mark = 0
150 AND cm.usage_ind = 3;
151
152 CURSOR get_cost_analysis_code (V_oprn_line_id NUMBER) IS
153 SELECT cost_analysis_code
154 FROM fm_actv_mst a, gmd_operation_activities o
155 WHERE o.activity = a.activity
156 AND o.oprn_line_id = V_oprn_line_id;
157
158 --Bug#7118558
159 CURSOR Cur_get_resource_usage_uom(V_resources VARCHAR2) IS
160 SELECT std_usage_uom
161 FROM cr_rsrc_mst
162 WHERE resources = V_resources;
163
164 l_cost_cmpntcls_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
165 l_oprn_id NUMBER;
166 l_oprn_rsrc_tbl gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
167 l_rsrc_count NUMBER(5);
168 l_usage_uom VARCHAR2(4);
169 BEGIN
170 SAVEPOINT insert_oprn_rsrc;
171
172 /* Initially let us assign the return status to success */
173 x_return_status := FND_API.g_ret_sts_success;
174
175 IF NOT gmd_api_grp.setup_done THEN
176 gmd_api_grp.setup_done := gmd_api_grp.setup;
177 END IF;
178 IF NOT gmd_api_grp.setup_done THEN
179 RAISE setup_failure;
180 END IF;
181
182 /* Make sure we are call compatible */
183 IF NOT FND_API.compatible_api_call(l_api_version
184 ,p_api_version
185 ,'insert_operation_resources'
186 ,'gmd_operation_resources_pub') THEN
187 RAISE invalid_version;
188 END IF;
189
190 /* Initialize message list and count if needed */
191 IF p_init_msg_list THEN
192 fnd_msg_pub.initialize;
193 END IF;
194
195 IF (l_debug = 'Y') THEN
196 gmd_debug.put_line('In insert_operation_resources public.');
197 END IF;
198
199 /* Operation Line ID must be passed, otherwise give error, also check operation line id exists */
200 IF p_oprn_line_id IS NULL THEN
201 IF (l_debug = 'Y') THEN
202 gmd_debug.put_line('operation line ID is required');
203 END IF;
204 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
205 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_LINE_ID');
206 FND_MSG_PUB.ADD;
207 RAISE ins_oprn_rsrc_err;
208 ELSIF p_oprn_line_id IS NOT NULL THEN
209 /* check to see that it is valid id */
210 OPEN check_oprn_line_id(p_oprn_line_id);
211 FETCH check_oprn_line_id INTO v_count;
212 IF check_oprn_line_id%NOTFOUND THEN
213 /* must pass existing operation line id */
214 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_OPRNLINE_ID');
215 FND_MSG_PUB.ADD;
216 CLOSE check_oprn_line_id;
217 RAISE ins_oprn_rsrc_err;
218 END IF;
219 CLOSE check_oprn_line_id;
220 END IF;
221
222 /* Operation Security Validation */
223 OPEN get_oprn_id(p_oprn_line_id);
224 FETCH get_oprn_id INTO l_oprn_id;
225 CLOSE get_oprn_id;
226
227 /* Operation Security Validation */
228 /* Validation: Chcek if this users performing update has access to this
229 operation owner orgn code */
230 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
231 ,Entity_id => l_oprn_id) THEN
232 RAISE ins_oprn_rsrc_err;
233 END IF;
234
235 FOR i in 1 .. p_oprn_rsrc_tbl.count LOOP
236 /* Lets initialize the local structure which will be passed to private layer */
237 l_oprn_rsrc_tbl(i) := p_oprn_rsrc_tbl(i);
238
239 /* Resource must be passed, otherwise give error */
240 IF l_oprn_rsrc_tbl(i).resources IS NULL THEN
241 IF (l_debug = 'Y') THEN
242 gmd_debug.put_line('operation resource required');
243 END IF;
244 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
245 FND_MESSAGE.SET_TOKEN ('MISSING', 'RESOURCES');
246 FND_MSG_PUB.ADD;
247 RAISE ins_oprn_rsrc_err;
248 ELSE /* check resource exists */
249 v_resources := l_oprn_rsrc_tbl(i).resources;
250 OPEN get_resource_det(v_resources);
251 FETCH get_resource_det INTO l_cost_cmpntcls_id;
252 IF get_resource_det%NOTFOUND THEN
253 /* must pass valid resource */
254 FND_MESSAGE.SET_NAME('GMD','FM_BAD_RESOURCE');
255 FND_MSG_PUB.ADD;
256 CLOSE get_resource_det;
257 RAISE ins_oprn_rsrc_err;
258 END IF;
259 CLOSE get_resource_det;
260 END IF;
261
262 /* Process_Qty must be passed, otherwise give error */
263 IF l_oprn_rsrc_tbl(i).process_qty IS NULL THEN
264 IF (l_debug = 'Y') THEN
265 gmd_debug.put_line('resource process qty required');
266 END IF;
267 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
268 FND_MESSAGE.SET_TOKEN ('MISSING', 'PROCESS_QTY');
269 FND_MSG_PUB.ADD;
270 RAISE ins_oprn_rsrc_err;
271 ELSIF l_oprn_rsrc_tbl(i).process_qty < 0 THEN
272 FND_MESSAGE.SET_TOKEN('GMD', 'FM_PROCQTYERR');
273 FND_MSG_PUB.ADD;
274 RAISE ins_oprn_rsrc_err;
275 END IF;
276
277 /* resource usagemust be passed, otherwise give error */
278 IF l_oprn_rsrc_tbl(i).resource_usage IS NULL THEN
279 IF (l_debug = 'Y') THEN
280 gmd_debug.put_line('resource usage required');
281 END IF;
282
283 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
284 FND_MESSAGE.SET_TOKEN ('MISSING', 'RESOURCE_USAGE');
285 FND_MSG_PUB.ADD;
286 RAISE ins_oprn_rsrc_err;
287 END IF;
288
289 -- Negative usage
290 IF l_oprn_rsrc_tbl(i).resource_usage < 0 THEN
291 FND_MESSAGE.SET_NAME('GMD', 'FM_RESUSGERR');
292 FND_MSG_PUB.ADD;
293 RAISE ins_oprn_rsrc_err;
294 END IF;
295
296 /* If Usage is not passed, derive the default from resource */
297 IF l_oprn_rsrc_tbl(i).resource_usage_uom IS NULL THEN
298 IF (l_debug = 'Y') THEN
299 gmd_debug.put_line('resource usage uom required');
300 END IF;
301 OPEN Cur_get_resource_usage_uom(l_oprn_rsrc_tbl(i).resources);
302 FETCH Cur_get_resource_usage_uom INTO l_oprn_rsrc_tbl(i).resource_usage_uom;
303 IF Cur_get_resource_usage_uom%NOTFOUND THEN
304 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
305 FND_MESSAGE.SET_TOKEN ('MISSING', 'resource_usage_uom');
306 FND_MSG_PUB.ADD;
307 CLOSE Cur_get_resource_usage_uom;
308 RAISE ins_oprn_rsrc_err;
309 END IF;
310 CLOSE Cur_get_resource_usage_uom;
311 ELSE /* check uom exists */
312 v_uom := l_oprn_rsrc_tbl(i).resource_usage_uom;
313 OPEN check_uom(v_uom);
314 FETCH check_uom INTO v_count;
315 IF check_uom%NOTFOUND THEN
316 /* must pass existing uom */
317 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
318 FND_MSG_PUB.ADD;
319 CLOSE check_uom;
320 RAISE ins_oprn_rsrc_err;
321 END IF;
322 CLOSE check_uom;
323 END IF;
324
325 /* cost component id, otherwise give error */
326 IF l_oprn_rsrc_tbl(i).cost_cmpntcls_id IS NULL THEN
327 l_oprn_rsrc_tbl(i).cost_cmpntcls_id := l_cost_cmpntcls_id;
328 ELSE /* check cost_cmpntcls_id exists */
329 v_cost_cmpntcls_id := l_oprn_rsrc_tbl(i).cost_cmpntcls_id;
330 OPEN check_cost_cmpntcls_id(v_cost_cmpntcls_id);
331 FETCH check_cost_cmpntcls_id INTO v_count;
332 IF check_cost_cmpntcls_id%NOTFOUND THEN
333 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_CMPNTCLS_ID');
334 FND_MSG_PUB.ADD;
335 CLOSE check_cost_cmpntcls_id;
336 RAISE ins_oprn_rsrc_err;
337 END IF;
338 CLOSE check_cost_cmpntcls_id;
339 END IF;
340
341 /* Cost Analysis code must be passed, otherwise give error */
342 IF l_oprn_rsrc_tbl(i).cost_analysis_code IS NULL THEN
343 OPEN get_cost_analysis_code (P_oprn_line_id);
344 FETCH get_cost_analysis_code INTO l_oprn_rsrc_tbl(i).cost_analysis_code;
345 CLOSE get_cost_analysis_code;
346 ELSE /* check cost_analysis_code exists */
347 v_cost_analysis_code := l_oprn_rsrc_tbl(i).cost_analysis_code;
348 OPEN check_cost_analysis_code(v_cost_analysis_code);
349 FETCH check_cost_analysis_code INTO v_count;
350 IF check_cost_analysis_code%NOTFOUND THEN
351 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
352 FND_MSG_PUB.ADD;
353 CLOSE check_cost_analysis_code;
354 RAISE ins_oprn_rsrc_err;
355 END IF;
356 CLOSE check_cost_analysis_code;
357 END IF;
358
359 IF l_oprn_rsrc_tbl(i).prim_rsrc_ind IS NULL THEN
360 l_oprn_rsrc_tbl(i).prim_rsrc_ind := 0;
361 END IF;
362 /* Plan Type/Resource Indicator should be valid */
363 IF l_oprn_rsrc_tbl(i).prim_rsrc_ind NOT IN (0,1,2) THEN
364 gmd_api_grp.log_message ('FM_RSRCINDERR');
365 RAISE ins_oprn_rsrc_err;
366 END IF;
367
368 /* Resource Count must be passed, otherwise give error */
369 IF l_oprn_rsrc_tbl(i).resource_count IS NULL THEN
370 IF (l_debug = 'Y') THEN
371 gmd_debug.put_line('Resource Count required');
372 END IF;
373 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
374 FND_MESSAGE.SET_TOKEN ('MISSING', 'RESOURCE_COUNT');
375 FND_MSG_PUB.ADD;
376 RAISE ins_oprn_rsrc_err;
377 ELSIF l_oprn_rsrc_tbl(i).resource_count < 0 THEN
378 gmd_api_grp.log_message ('FM_RESCOUNTERR');
379 RAISE ins_oprn_rsrc_err;
380 END IF;
381
382 /* Offset Interval must be passed, otherwise give error */
383 IF l_oprn_rsrc_tbl(i).offset_interval IS NULL THEN
384 IF (l_debug = 'Y') THEN
385 gmd_debug.put_line('Offset interval required');
386 END IF;
387 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
388 FND_MESSAGE.SET_TOKEN ('MISSING', 'OFFSET_INTERVAL');
389 FND_MSG_PUB.ADD;
390 RAISE ins_oprn_rsrc_err;
391 END IF;
392
393 /* Scale Type must be passed, otherwise give error */
394 IF l_oprn_rsrc_tbl(i).scale_type IS NULL THEN
395 IF (l_debug = 'Y') THEN
396 gmd_debug.put_line('Scale Type is null assigning default 1');
397 END IF;
398 l_oprn_rsrc_tbl(i).scale_type := 1;
399 ELSIF l_oprn_rsrc_tbl(i).scale_type NOT IN (0, 1, 2) THEN
400 gmd_api_grp.log_message ('FM_SCALETYPERR');
401 RAISE ins_oprn_rsrc_err;
402 END IF;
403
404 /* Lets initialialize the default values */
405 IF x_return_status = FND_API.g_ret_sts_success THEN
406 OPEN fetch_proc_uom (p_oprn_line_id);
407 FETCH fetch_proc_uom INTO l_oprn_rsrc_tbl(i).resource_process_uom;
408 CLOSE fetch_proc_uom;
409 END IF;
410 END LOOP;
411
412 IF x_return_status = 'S' THEN
413 IF (l_debug = 'Y') THEN
414 gmd_debug.put_line('before PVT routine called');
415 END IF;
416
417 GMD_OPERATION_RESOURCES_PVT.insert_operation_resources(p_oprn_line_id => p_oprn_line_id,
418 p_oprn_rsrc_tbl => l_oprn_rsrc_tbl,
419 x_message_count => x_message_count,
420 x_message_list => x_message_list,
421 x_return_status => l_return_status);
422
423 IF l_return_status <> FND_API.g_ret_sts_success THEN
424 RAISE ins_oprn_rsrc_err;
425 END IF;
426
427 /* Let us check if their are more than one resource marked as primary */
428 OPEN check_one_prim_rsrc (p_oprn_line_id);
429 FETCH check_one_prim_rsrc INTO v_count;
430 CLOSE check_one_prim_rsrc;
431 IF v_count > 1 THEN
432 gmd_api_grp.log_message ('GMD_ONE_PRIMARY_RESOURCE');
433 RAISE inv_resource_ind;
434 END IF;
435
436 /* This implies that we are setting this current resource as secondary or auxillary */
437 /* so let us check if their exists atleast one primary resource */
438 OPEN check_atleast_one (p_oprn_line_id, NULL);
439 FETCH check_atleast_one INTO v_count;
440 CLOSE check_atleast_one;
441 IF v_count = 0 THEN
442 gmd_api_grp.log_message ('GMD_MIN_ONE_PRIMARY_RESOURCE');
443 RAISE inv_resource_ind;
444 END IF;
445
446 IF p_commit THEN
447 COMMIT;
448 END IF;
449 END IF;
450
451 FND_MSG_PUB.count_and_get(p_count => x_message_count
452 ,p_data => x_message_list);
453
454 IF (l_debug = 'Y') THEN
455 gmd_debug.put_line('End of insert_operation_resource PUB');
456 END IF;
457 EXCEPTION
458 WHEN setup_failure OR invalid_version OR inv_resource_ind THEN
459 ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
462 P_data => x_message_list);
463 WHEN ins_oprn_rsrc_err THEN
464 ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
467 P_data => x_message_list);
468 WHEN OTHERS THEN
469 ROLLBACK TO SAVEPOINT insert_oprn_rsrc;
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
471 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
472 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
473 FND_MSG_PUB.ADD;
474 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
475 P_data => x_message_list);
476 END insert_operation_resources;
477
478 /*========================================================
479 Procedure
480 update_operation_resources
481 Description
482 This particular procedure is used to update operation resources
483 Parameters
484 ================================================ */
485 PROCEDURE update_operation_resources
486 ( p_api_version IN NUMBER
487 , p_init_msg_list IN BOOLEAN
488 , p_commit IN BOOLEAN
489 , p_oprn_line_id IN gmd_operation_resources.oprn_line_id%TYPE
490 , p_resources IN gmd_operation_resources.resources%TYPE
491 , p_update_table IN gmd_operation_resources_pub.update_tbl_type
492 , x_message_count OUT NOCOPY NUMBER
493 , x_message_list OUT NOCOPY VARCHAR2
494 , x_return_status OUT NOCOPY VARCHAR2) IS
495
496 v_oprn_id gmd_operations.oprn_id%TYPE;
497 l_return_status VARCHAR2(1);
498 l_api_version NUMBER := 1.0;
499 l_exist NUMBER(5);
500
501 invalid_version EXCEPTION;
502 setup_failure EXCEPTION;
503
504 CURSOR get_oprn_id (p_oprN_line_id gmd_operation_resources.oprn_line_id%TYPE) IS
505 SELECT oprn_id
506 FROM gmd_operation_activities
507 WHERE oprn_line_id = p_oprn_line_id;
508
509 upd_oprn_rsrc_err EXCEPTION;
510 BEGIN
511 SAVEPOINT update_oprn_rsrc;
512
513 /* Initially let us assign the return status to success */
514 x_return_status := FND_API.g_ret_sts_success;
515
516 /* Make sure we are call compatible */
517 IF NOT FND_API.compatible_api_call(l_api_version
518 ,p_api_version
519 ,'update_operation_resources'
520 ,'gmd_operation_resources_pub') THEN
521 RAISE invalid_version;
522 END IF;
523
524 /* Initialize message list and count if needed */
525 IF p_init_msg_list THEN
526 fnd_msg_pub.initialize;
527 END IF;
528
529 IF NOT gmd_api_grp.setup_done THEN
530 gmd_api_grp.setup_done := gmd_api_grp.setup;
531 END IF;
532 IF NOT gmd_api_grp.setup_done THEN
533 RAISE setup_failure;
534 END IF;
535
536 IF (l_debug = 'Y') THEN
537 gmd_debug.put_line('Start of update_operation_activity PUB');
538 END IF;
539
540 /* Oprn_line_id must be passed, otherwise give error */
541 IF p_oprn_line_id IS NULL THEN
542 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
543 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_LINE_ID');
544 FND_MSG_PUB.ADD;
545 RAISE upd_oprn_rsrc_err;
546 ELSE /* check oprn_line_id exists */
547 OPEN check_oprn_line_id(p_oprn_line_id);
548 FETCH check_oprn_line_id INTO l_exist;
549 IF check_oprn_line_id%NOTFOUND THEN
550 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_OPRNLINE_ID');
551 FND_MSG_PUB.ADD;
552 CLOSE check_oprn_line_id;
553 RAISE upd_oprn_rsrc_err;
554 END IF;
555 CLOSE check_oprn_line_id;
556 END IF;
557
558 /* Resources must be passed, otherwise give error */
559 IF p_resources IS NULL THEN
560 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
561 FND_MESSAGE.SET_TOKEN ('MISSING', 'P_RESOURCES');
562 FND_MSG_PUB.ADD;
563 RAISE upd_oprn_rsrc_err;
564 ELSE /* check resource exists */
565 OPEN check_resource(p_oprn_line_id, p_resources);
566 FETCH check_resource INTO l_exist;
567 IF check_resource%NOTFOUND THEN
568 /* must pass valid resource */
569 FND_MESSAGE.SET_NAME('GMD','FM_BAD_RESOURCE');
570 FND_MSG_PUB.ADD;
571 CLOSE check_resource;
572 RAISE upd_oprn_rsrc_err;
573 END IF;
574 CLOSE check_resource;
575 END IF;
576
577 /* Loop thru cols to be updated - verify col and value are present */
578 FOR i in 1 .. p_update_table.count LOOP
579 /* Col_to_update and value must be passed, otherwise give error */
580 IF p_update_table(i).p_col_to_update IS NULL THEN
581 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
582 FND_MESSAGE.SET_TOKEN ('MISSING', 'P_COL_TO_UPDATE');
583 FND_MSG_PUB.ADD;
584 RAISE upd_oprn_rsrc_err;
585 ELSIF p_update_table(i).p_value IS NULL THEN
586 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
587 FND_MESSAGE.SET_TOKEN ('MISSING', 'P_VALUE');
588 FND_MSG_PUB.ADD;
589 RAISE upd_oprn_rsrc_err;
590 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'COST_ANALYSIS_CODE' THEN
591 OPEN check_cost_analysis_code(p_update_table(i).p_value);
592 FETCH check_cost_analysis_code INTO l_exist;
593 IF check_cost_analysis_code%NOTFOUND THEN
594 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_ANLYS_CODE');
595 FND_MSG_PUB.ADD;
596 CLOSE check_cost_analysis_code;
597 RAISE upd_oprn_rsrc_err;
598 END IF;
599 CLOSE check_cost_analysis_code;
600 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'RESOURCE_USAGE' THEN
601 IF p_update_table(i).p_value < 0 THEN
602 gmd_api_grp.log_message('FM_RESUSGERR');
603 RAISE upd_oprn_rsrc_err;
604 END IF;
605 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PROCESS_QTY' THEN
606 IF p_update_table(i).p_value < 0 THEN
607 gmd_api_grp.log_message('FM_PROCQTYERR');
608 RAISE upd_oprn_rsrc_err;
609 END IF;
610 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'resource_usage_uom' THEN
611 OPEN check_uom(p_update_table(i).p_value);
612 FETCH check_uom INTO l_exist;
613 IF check_uom%NOTFOUND THEN
614 /* must pass existing uom */
615 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
616 FND_MSG_PUB.ADD;
617 CLOSE check_uom;
618 RAISE upd_oprn_rsrc_err;
619 END IF;
620 CLOSE check_uom;
621 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'COST_CMPNTCLS_ID' THEN
622 OPEN check_cost_cmpntcls_id(p_update_table(i).p_value);
623 FETCH check_cost_cmpntcls_id INTO l_exist;
624 IF check_cost_cmpntcls_id%NOTFOUND THEN
625 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_COST_CMPNTCLS_ID');
626 FND_MSG_PUB.ADD;
627 CLOSE check_cost_cmpntcls_id;
628 RAISE upd_oprn_rsrc_err;
629 END IF;
630 CLOSE check_cost_cmpntcls_id;
631 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PRIM_RSRC_IND' THEN
632 IF p_update_table(i).p_value NOT IN (0, 1, 2) THEN
633 gmd_api_grp.log_message ('FM_RSRCINDERR');
634 RAISE upd_oprn_rsrc_err;
635 ELSIF p_update_table(i).p_value = 1 THEN
636 /* This implies that we are setting this current resource as primary */
637 /* so let us check if their are any other primaries already existing */
638 OPEN check_one_prim_rsrc (p_oprn_line_id);
639 FETCH check_one_prim_rsrc INTO l_exist;
640 CLOSE check_one_prim_rsrc;
641 IF l_exist > 0 THEN
642 gmd_api_grp.log_message ('GMD_ONE_PRIMARY_RESOURCE');
643 RAISE upd_oprn_rsrc_err;
644 END IF;
645 ELSIF p_update_table(i).p_value IN (0,2) THEN
646 /* This implies that we are setting this current resource as secondary or auxillary */
647 /* so let us check if their exists atleast one primary resource */
648 OPEN check_atleast_one (p_oprn_line_id, p_resources);
649 FETCH check_atleast_one INTO l_exist;
650 CLOSE check_atleast_one;
651 IF l_exist = 0 THEN
652 gmd_api_grp.log_message ('GMD_MIN_ONE_PRIMARY_RESOURCE');
653 RAISE upd_oprn_rsrc_err;
654 END IF;
655 END IF;
656 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'RESOURCE_COUNT' THEN
657 IF p_update_table(i).p_value < 0 THEN
658 gmd_api_grp.log_message ('FM_RESCOUNTERR');
659 RAISE upd_oprn_rsrc_err;
660 END IF;
661 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OFFSET_INTERVAL' THEN
662 IF p_update_table(i).p_value < 0 THEN
663 gmd_api_grp.log_message('GMD_INVALID_OFFSET');
664 RAISE upd_oprn_rsrc_err;
665 END IF;
666 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'SCALE_TYPE' THEN
667 IF p_update_table(i).p_value NOT IN (0, 1, 2) THEN
668 gmd_api_grp.log_message ('FM_SCALETYPERR');
669 RAISE upd_oprn_rsrc_err;
670 END IF;
671 END IF;
672 END LOOP;
673
674 OPEN get_oprn_id(p_oprn_line_id);
675 FETCH get_oprn_id INTO v_oprn_id;
676 CLOSE get_oprn_id;
677
678 /* Operation Security Validation */
679 /* Validation: Chcek if this users performing update has access to this
680 operation owner orgn code */
681 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
682 ,Entity_id => v_oprn_id) THEN
683 RAISE upd_oprn_rsrc_err;
684 END IF;
685
686 /* Validation : Verify Operation status is not On Hold nor Obsolete/Archived
687 and Operation is not logically deleted */
688 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
689 Entity_id => v_oprn_id ) THEN
690 FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
691 FND_MSG_PUB.ADD;
692 RAISE upd_oprn_rsrc_err;
693 END IF;
694
695 IF x_return_status = 'S' THEN
696 IF (l_debug = 'Y') THEN
697 gmd_debug.put_line('before PVT routine called');
698 END IF;
699
700 GMD_OPERATION_RESOURCES_PVT.update_operation_resources(p_oprn_line_id => p_oprn_line_id
701 , p_resources => p_resources
702 , p_update_table => p_update_table
703 , x_message_count => x_message_count
704 , x_message_list => x_message_list
705 , x_return_status => l_return_status);
706 IF l_return_status <> FND_API.g_ret_sts_success THEN
707 RAISE upd_oprn_rsrc_err;
708 END IF;
709 IF p_commit THEN
710 COMMIT;
711 END IF;
712 END IF;
713
714 FND_MSG_PUB.count_and_get(p_count => x_message_count
715 ,p_data => x_message_list);
716
717 IF (l_debug = 'Y') THEN
718 gmd_debug.put_line('END of update_operation_resource PUB');
719 END IF;
720
721 EXCEPTION
722 WHEN invalid_version OR setup_failure THEN
723 ROLLBACK TO SAVEPOINT update_oprn_rsrc;
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
726 P_data => x_message_list);
727 WHEN upd_oprn_rsrc_err THEN
728 ROLLBACK TO SAVEPOINT update_oprn_rsrc;
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
731 P_data => x_message_list);
732 WHEN OTHERS THEN
733 ROLLBACK TO SAVEPOINT update_oprn_rsrc;
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
736 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
737 FND_MSG_PUB.ADD;
738 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
739 P_data => x_message_list);
740 END update_operation_resources;
741
742 /*=============================================================
743 Procedure
744 delete_operation_resources
745 Description
746 This particular procedure is used to delete operation resources
747 Parameters
748 ================================================ */
749 PROCEDURE delete_operation_resources
750 ( p_api_version IN NUMBER
751 , p_init_msg_list IN BOOLEAN
752 , p_commit IN BOOLEAN
753 , p_oprn_line_id IN gmd_operation_resources.oprn_line_id%TYPE
754 , p_resources IN gmd_operation_resources.resources%TYPE
755 , x_message_count OUT NOCOPY NUMBER
756 , x_message_list OUT NOCOPY VARCHAR2
757 , x_return_status OUT NOCOPY VARCHAR2) IS
758
759 v_update_table gmd_operation_resources_pub.update_tbl_type;
760 v_count NUMBER;
761 l_return_status VARCHAR2(1);
762 l_api_version NUMBER := 1.0;
763 v_oprn_id NUMBER;
764
765 invalid_version EXCEPTION;
766 setup_failure EXCEPTION;
767 del_oprn_rsrc_err EXCEPTION;
768 inv_resource_ind EXCEPTION;
769
770 CURSOR check_oprn_line_id(p_oprn_line_id NUMBER) IS
771 SELECT 1
772 FROM gmd_operation_activities
773 WHERE oprn_line_id = p_oprn_line_id;
774
775 CURSOR check_Resource(p_oprn_line_id NUMBER, p_resources VARCHAR2) IS
776 SELECT 1
777 FROM gmd_operation_resources
778 WHERE oprn_line_id = p_oprn_line_id
779 AND resources = p_resources;
780
781 CURSOR get_oprn_id (p_oprN_line_id gmd_operation_resources.oprn_line_id%TYPE) IS
782 SELECT oprn_id
783 FROM gmd_operation_activities
784 WHERE oprn_line_id = p_oprn_line_id;
785
786 BEGIN
787 SAVEPOINT delete_oprn_rsrc;
788
789 /* Initially let us assign the return status to success */
790 x_return_status := FND_API.g_ret_sts_success;
791
792 /* Initialize message list and count if needed */
793 IF p_init_msg_list THEN
794 fnd_msg_pub.initialize;
795 END IF;
796
797 /* Make sure we are call compatible */
798 IF NOT FND_API.compatible_api_call(l_api_version
799 ,p_api_version
800 ,'delete_operation_resources'
801 ,'gmd_operation_resources_pub') THEN
802 RAISE invalid_version;
803 END IF;
804
805 IF NOT gmd_api_grp.setup_done THEN
806 gmd_api_grp.setup_done := gmd_api_grp.setup;
807 END IF;
808 IF NOT gmd_api_grp.setup_done THEN
809 RAISE setup_failure;
810 END IF;
811
812 IF (l_debug = 'Y') THEN
813 gmd_debug.put_line('START of delete_operation_resources PUB');
814 END IF;
815
816 /* Operation Line ID must be passed, otherwise give error */
817 IF (p_oprn_line_id IS NULL OR p_resources IS NULL) THEN
818 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
819 FND_MESSAGE.SET_TOKEN ('MISSING', 'P_OPRN_LINE_ID');
820 FND_MSG_PUB.ADD;
821 RAISE del_oprn_rsrc_err;
822 ELSE /* check oprn_line_id and resource exist */
823 OPEN check_oprn_line_id(p_oprn_line_id);
824 FETCH check_oprn_line_id INTO v_count;
825 IF check_oprn_line_id%NOTFOUND THEN
826 /* must pass valid oprn_line_id */
827 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_OPRNLINE_ID');
828 FND_MSG_PUB.ADD;
829 CLOSE check_oprn_line_id; /* pku */
830 RAISE del_oprn_rsrc_err;
831 ELSE
832 OPEN check_resource(p_oprn_line_id, p_resources);
833 FETCH check_resource INTO v_count;
834 IF check_resource%NOTFOUND THEN
835 /* must pass valid resource */
836 FND_MESSAGE.SET_NAME('GMD','FM_BAD_RESOURCE');
837 FND_MSG_PUB.ADD;
838 CLOSE check_resource;
839 CLOSE check_oprn_line_id; /* pku */
840 RAISE del_oprn_rsrc_err;
841 END IF;
842 CLOSE check_resource;
843 END IF;
844 CLOSE check_oprn_line_id;
845 END IF;
846
847 /* get the oprn_id */
848 OPEN get_oprn_id(p_oprn_line_id);
849 FETCH get_oprn_id INTO v_oprn_id;
850 CLOSE get_oprn_id;
851
852 /* Operation Security Validation */
853 /* Validation: Chcek if this users performing update has access to this
854 operation owner orgn code */
855 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'OPERATION'
856 ,Entity_id => v_oprn_id) THEN
857 RAISE del_oprn_rsrc_err;
858 END IF;
859
860
861 IF x_return_status = FND_API.g_ret_sts_success THEN
862 /* Call PVT delete_operation_resources */
863 IF (l_debug = 'Y') THEN
864 gmd_debug.put_line('before call to delete_operation_resource PVT');
865 END IF;
866 gmd_operation_resources_pvt.delete_operation_resource(p_oprn_line_id => p_oprn_line_id
867 ,p_resources => p_resources
868 , x_message_count => x_message_count
869 , x_message_list => x_message_list
870 , x_return_status => l_return_status);
871 IF l_return_status <> FND_API.g_ret_sts_success THEN
872 RAISE del_oprn_rsrc_err;
873 END IF;
874
875 /* This implies that we are setting this current resource as secondary or auxillary */
876 /* so let us check if their exists atleast one primary resource */
877 OPEN check_atleast_one (p_oprn_line_id, NULL);
878 FETCH check_atleast_one INTO v_count;
879 CLOSE check_atleast_one;
880 IF v_count = 0 THEN
881 gmd_api_grp.log_message ('GMD_MIN_ONE_PRIMARY_RESOURCE');
882 RAISE inv_resource_ind;
883 END IF;
884
885 IF p_commit THEN
886 COMMIT;
887 END IF;
888 END IF;
889
890 FND_MSG_PUB.count_and_get(p_count => x_message_count
891 ,p_data => x_message_list);
892
893 IF (l_debug = 'Y') THEN
894 gmd_debug.put_line('END of delete_operation_resources PUB');
895 END IF;
896
897 EXCEPTION
898 WHEN invalid_version OR setup_failure OR inv_resource_ind THEN
899 ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
902 P_data => x_message_list);
903 WHEN del_oprn_rsrc_err THEN
904 ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
907 P_data => x_message_list);
908 WHEN OTHERS THEN
909 ROLLBACK TO SAVEPOINT delete_oprn_rsrc;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
912 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
913 FND_MSG_PUB.ADD;
914 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
915 P_data => x_message_list);
916 END delete_operation_resources;
917
918 END GMD_OPERATION_RESOURCES_PUB;
[Home] [Help]