1 PACKAGE BODY GMD_OPERATIONS_PUB AS
2 /* $Header: GMDPOPSB.pls 120.3 2006/09/20 14:53:46 kmotupal noship $
3 ************************************************************************************
4 * *
5 * Package GMD_OPERATIONS_PUB *
6 * *
7 * Contents: INSERT_OPERATION *
8 * UPDATE_OPERATION *
9 * DELETE_OPERATION *
10 * *
11 * Use This is the public layer of the GMD Operation 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 * 21-OCT-2003 Shyam S Commented section in update_operation *
21 * procedure that check if p_value is *
22 * passes or not *
23 * 20-FEB-2004 NSRIVAST Bug# 3222090,Removed call to *
24 * FND_PROFILE.VALUE('AFLOG_ENABLED') *
25 * Shyam S 09-14-04 Added validations in insert, update and delete *
26 * that chceks for user access to owner orgn code *
27 ***********************************************************************************
28 */
29
30
31 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
32 --Forward declaration.
33 FUNCTION set_debug_flag RETURN VARCHAR2;
34 l_debug VARCHAR2(1) := set_debug_flag;
35
36 FUNCTION set_debug_flag RETURN VARCHAR2 IS
37 l_debug VARCHAR2(1):= 'N';
38 BEGIN
39 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
40 l_debug := 'Y';
41 END IF;
42 RETURN l_debug;
43 END set_debug_flag;
44 --Bug 3222090, NSRIVAST 20-FEB-2004, END
45
46 /*===========================================================================================
47 Procedure
48 insert_operation
49 Description
50 This particular procedure is used to insert an operation
51 Parameters
52 WHO WHEN WHAT
53 kkillams 10-MAR-2004 New p_oprn_rsrc_tbl input paramter is added to proceudre to pass the
54 resource details for activities. Added validation to check if default
55 status is 400/700 for the context organization then activity should have the
56 resources w.r.t. bug# 3408799
57 ==================================================================================================== */
58 PROCEDURE insert_operation (
59 p_api_version IN NUMBER
60 ,p_init_msg_list IN BOOLEAN
61 ,p_commit IN BOOLEAN
62 ,p_operations IN OUT NOCOPY gmd_operations%ROWTYPE
63 ,p_oprn_actv_tbl IN OUT NOCOPY gmd_operations_pub.gmd_oprn_activities_tbl_type
64 ,x_message_count OUT NOCOPY NUMBER
65 ,x_message_list OUT NOCOPY VARCHAR2
66 ,x_return_status OUT NOCOPY VARCHAR2
67 ,p_oprn_rsrc_tbl IN gmd_operation_resources_pub.gmd_oprn_resources_tbl_type) IS
68
69 v_oprn_id gmd_operations.oprn_id%TYPE;
70 v_oprn_no gmd_operations.oprn_no%TYPE;
71 v_oprn_vers gmd_operations.oprn_vers%TYPE;
72 p_ret NUMBER;
73 v_oprn_class gmd_operations.oprn_class%TYPE;
74 l_retn_status VARCHAR2(1);
75 my_rsrc_table_type gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
76 l_api_version NUMBER := 1.0;
77 l_entity_status gmd_api_grp.status_rec_type; ---bug# 3408799
78 default_status_err EXCEPTION;
79 l_resource_count NUMBER;
80
81 invalid_version EXCEPTION;
82 setup_failure EXCEPTION;
83 ins_operation_err EXCEPTION;
84
85 CURSOR Cur_gen_oprn_id IS
86 SELECT GEM5_OPRN_ID_S.NEXTVAL
87 FROM FND_DUAL;
88
89 BEGIN
90 SAVEPOINT insert_oprn;
91
92 /* Make sure we are call compatible */
93 IF NOT FND_API.compatible_api_call(l_api_version
94 ,p_api_version
95 ,'insert_operation'
96 ,'gmd_operations_pub') THEN
97 RAISE invalid_version;
98 END IF;
99
100 /* Initialize message list and count if needed */
101 IF p_init_msg_list THEN
102 fnd_msg_pub.initialize;
103 END IF;
104
105 /* Intializes the setup fields */
106 IF NOT gmd_api_grp.setup_done THEN
107 gmd_api_grp.setup_done := gmd_api_grp.setup;
108 END IF;
109 IF NOT gmd_api_grp.setup_done THEN
110 RAISE setup_failure;
111 END IF;
112
113 /* Initially let us assign the return status to success */
114 x_return_status := FND_API.g_ret_sts_success;
115
116 /* Operation number must be passed, otherwise give error */
117 IF p_operations.oprn_no IS NULL THEN
118 IF (l_debug = 'Y') THEN
119 gmd_debug.put_line('operation number required');
120 END IF;
121
122 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
123 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_NO');
124 FND_MSG_PUB.ADD;
125 RAISE ins_operation_err;
126 END IF;
127
128 /* Operation Version must be passed, otherwise give error */
129 IF p_operations.oprn_vers IS NULL THEN
130 IF (l_debug = 'Y') THEN
131 gmd_debug.put_line('operation version required');
132 END IF;
133
134 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
135 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_VERS');
136 FND_MSG_PUB.ADD;
137 RAISE ins_operation_err;
138 ELSIF p_operations.oprn_vers < 0 THEN
139 gmd_api_grp.log_message ('GMD_NEGATIVE_FIELDS',
140 'FIELD', 'OPRN_VERS');
141 RAISE ins_operation_err;
142 END IF;
143
144 /* Check for duplicate oprn_no/vers */
145 v_oprn_no := p_operations.oprn_no;
146 v_oprn_vers := p_operations.oprn_vers;
147 /* call common function which checks for duplicate operation no and vers */
148 P_ret := gmdopval_pub.check_duplicate_oprn(v_oprn_no, v_oprn_vers, 'F');
149 IF p_ret <> 0 THEN
150 gmd_api_grp.log_message('FM_OPER_CODE_EXISTS');
151 RAISE ins_operation_err;
152 END IF;
153
154 /* Description must be passed, otherwise give error */
155 IF p_operations.oprn_desc IS NULL THEN
156 IF (l_debug = 'Y') THEN
157 gmd_debug.put_line('operation desc required');
158 END IF;
159
160 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
161 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_DESC');
162 FND_MSG_PUB.ADD;
163 RAISE ins_operation_err;
164 END IF;
165
166 /* PROCESS_QTY_UOM must be passed, otherwise give error */
167 IF p_operations.PROCESS_QTY_UOM IS NULL THEN
168 IF (l_debug = 'Y') THEN
169 gmd_debug.put_line('process qty uom required');
170 END IF;
171
172 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
173 FND_MESSAGE.SET_TOKEN ('MISSING', 'PROCESS_QTY_UOM');
174 FND_MSG_PUB.ADD;
175 RAISE ins_operation_err;
176 /* call common function to check if um passed is valid */
177 ELSIF (NOT(gmd_api_grp.validate_um(p_operations.PROCESS_QTY_UOM))) THEN
178 IF (l_debug = 'Y') THEN
179 gmd_debug.put_line('process qty uom invalid');
180 END IF;
181
182 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
183 FND_MSG_PUB.ADD;
184 RAISE ins_operation_err;
185 END IF;
186
187 /*
188 * Convergence related fix - Shyam S
189 *
190 */
191 --Check that organization id is not null if raise an error message
192 IF (p_operations.owner_organization_id IS NULL) THEN
193 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
194 FND_MSG_PUB.Add;
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 -- Check if organization is accessible to the responsibility
199 IF NOT (GMD_API_GRP.OrgnAccessible (powner_orgn_id => p_operations.owner_organization_id) ) THEN
200 RAISE ins_operation_err;
201 END IF;
202
203 --Check the organization id passed is process enabled if not raise an error message
204 IF NOT (gmd_api_grp.check_orgn_status(p_operations.owner_organization_id)) THEN
205 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
206 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_operations.owner_organization_id);
207 FND_MSG_PUB.Add;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF;
210
211 /* Effective start date must be passed, otherwise give error */
212 IF p_operations.effective_start_date IS NULL THEN
213 p_operations.effective_start_date := TRUNC(SYSDATE);
214 ELSE
215 p_operations.effective_start_date := TRUNC(p_operations.effective_start_date);
216 END IF;
217
218 IF p_operations.effective_end_date IS NOT NULL THEN
219 p_operations.effective_end_date := TRUNC(p_operations.effective_end_date);
220 /* Effective end date must be greater than start date, otherwise give error */
221 IF p_operations.effective_start_date > p_operations.effective_end_date THEN
222 IF (l_debug = 'Y') THEN
223 gmd_debug.put_line('effective start date must be less then end date');
224 END IF;
225 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
226 FND_MSG_PUB.ADD;
227 RAISE ins_operation_err;
228 END IF;
229 END IF;
230
231 IF p_operations.minimum_transfer_qty < 0 THEN
232 gmd_api_grp.log_message ('GMD_NEGATIVE_FIELDS',
233 'FIELD', 'MINIMUM_TRANSFER_QTY');
234 RAISE ins_operation_err;
235 END IF;
236
237 /* Operation Class Validation - valid operation class must be passed */
238 IF p_operations.oprn_class IS NOT NULL THEN
239 /* calls common funciton which checks if class is valid */
240 v_oprn_class := p_operations.oprn_class;
241 P_ret := gmdopval_pub.check_oprn_class(v_oprn_class, 'F');
242 IF P_ret <> 0 THEN
243 gmd_api_grp.log_message('FM_INV_OPRN_CLASS');
244 END IF;
245 END IF;
246
247 OPEN Cur_gen_oprn_id;
248 FETCH cur_gen_oprn_id into p_operations.oprn_id;
249 CLOSE cur_gen_oprn_id;
250
251 /* At least one activity must be passed to add operation */
252 IF (p_oprn_actv_tbl.count = 0) THEN
253 gmd_api_grp.log_message ('GMD_DETAILS_REQUIRED');
254 RAISE ins_operation_err;
255 END IF;
256
257 IF x_return_status = 'S' THEN
258 IF (l_debug = 'Y') THEN
259 gmd_debug.put_line('before PVT routine called');
260 END IF;
261
262 /* insert operation */
263 GMD_OPERATIONS_PVT.insert_operation(p_operations => p_operations,
264 x_message_count => x_message_count,
265 x_message_list => x_message_list,
266 x_return_status => l_retn_status);
267 IF l_retn_status <> FND_API.g_ret_sts_success THEN
268 RAISE ins_operation_err;
269 END IF;
270
271
272 /* validate oprn activity info and insert oprn activity */
273 FOR i in 1.. p_oprn_actv_tbl.count LOOP
274 p_oprn_actv_tbl(i).oprn_id := p_operations.oprn_id;
275 --Getting the default status for the owner orgn code fo operation from parameters table w.r.t. bug#3408799
276 gmd_api_grp.get_status_details(v_entity_type => 'OPERATION'
277 ,v_orgn_id => p_operations.owner_organization_id -- w.r.t. 4004501
278 ,x_entity_status => l_entity_status);
279
280 --Copy the all related resources for the context activity w.r.t. bug#3408799
281 l_resource_count :=0;
282 FOR j in 1 ..p_oprn_rsrc_tbl.count
283 LOOP
284 IF (p_oprn_actv_tbl(i).oprn_line_id = p_oprn_rsrc_tbl(j).oprn_line_id) OR
285 (p_oprn_actv_tbl(i).activity = p_oprn_rsrc_tbl(j).activity) THEN
286 l_resource_count :=l_resource_count+1;
287 my_rsrc_table_type(l_resource_count) :=p_oprn_rsrc_tbl(j);
288 END IF; --p_oprn_actv_tbl(i).oprn_line_id = p_oprn_rsrc_tbl(j).oprn_line_id
289 END LOOP; --j in 1 ..p_oprn_rsrc_tbl.count
290
291 --Raise error if default status is 400/700 and no activites are attached w.r.t. bug#3408799
292 IF l_entity_status.status_type IN (400,700) AND l_resource_count = 0 THEN
293 gmd_api_grp.log_message('GMD_RESOURCE_NOT_ATTACH');
294 RAISE ins_operation_err;
295 END IF; --l_entity_status.status_type IN (400,700) AND l_resource_count = 0
296
297 GMD_OPERATION_ACTIVITIES_PUB.insert_operation_activity(p_init_msg_list => FALSE,
298 p_oprn_activity => p_oprn_actv_tbl(i),
299 p_oprn_rsrc_tbl => my_rsrc_table_type,
300 x_message_count => x_message_count,
301 x_message_list => x_message_list,
302 x_return_status => l_retn_status);
303
304 IF l_retn_status <> FND_API.g_ret_sts_success THEN
305 RAISE ins_operation_err;
306 END IF;
307 my_rsrc_table_type.delete;
308 END LOOP; --i in 1.. p_oprn_actv_tbl.count
309
310 IF p_commit THEN
311 COMMIT;
312 SAVEPOINT default_status_sp;
313
314 /* -- Why call this again
315 --Getting the default status for the owner orgn code fo operation from parameters table w.r.t. bug#3408799
316 gmd_api_grp.get_status_details(v_entity_type => 'OPERATION'
317 ,v_orgn_code => p_operations.owner_orgn_code
318 ,x_entity_status => l_entity_status);
319
320 */
321
322 --Add this code after the call to gmd_recipes_mls.insert_row.
323 IF (l_entity_status.entity_status <> 100) THEN
324 Gmd_status_pub.modify_status ( p_api_version => 1
325 , p_init_msg_list => TRUE
326 , p_entity_name => 'OPERATION'
327 , p_entity_id => p_operations.oprn_id
328 , p_entity_no => NULL
329 , p_entity_version => NULL
330 , p_to_status => l_entity_status.entity_status
331 , p_ignore_flag => FALSE
332 , x_message_count => x_message_count
333 , x_message_list => x_message_list
334 , x_return_status => l_retn_status);
335 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
336 RAISE default_status_err;
337 END IF; --x_return_status
338 END IF; --l_entity_status.entity_status <> 100
339 COMMIT;
340 END IF; ---p_commit
341 END IF;
342
343 FND_MSG_PUB.count_and_get(p_count => x_message_count
344 ,p_data => x_message_list);
345
346 EXCEPTION
347 WHEN setup_failure OR invalid_version THEN
348 ROLLBACK TO SAVEPOINT insert_oprn;
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
351 P_data => x_message_list);
352 WHEN ins_operation_err THEN
353 ROLLBACK TO SAVEPOINT insert_oprn;
354 x_return_status := FND_API.g_ret_sts_error;
355 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
356 P_data => x_message_list);
357 WHEN default_status_err THEN
358 ROLLBACK TO default_status_sp;
359 x_return_status := FND_API.g_ret_sts_error;
360 FND_MSG_PUB.Count_And_Get (
361 p_count => x_message_count,
362 p_data => x_message_list );
363 WHEN OTHERS THEN
364 ROLLBACK TO SAVEPOINT insert_oprn;
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
367 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
368 FND_MSG_PUB.ADD;
369 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
370 P_data => x_message_list);
371 END Insert_Operation;
372
373
374 /*===========================================================================================
375 Procedure
376 update_operation
377 Description
378 This particular procedure is used to update an operation
379 Parameters
380 ================================================ */
381 PROCEDURE update_operation
382 ( p_api_version IN NUMBER
383 , p_init_msg_list IN BOOLEAN
384 , p_commit IN BOOLEAN
385 , p_oprn_id IN gmd_operations.oprn_id%TYPE
386 , p_oprn_no IN gmd_operations.oprn_no%TYPE
387 , p_oprn_vers IN gmd_operations.oprn_vers%TYPE
388 , p_update_table IN gmd_operations_pub.update_tbl_type
389 , x_message_count OUT NOCOPY NUMBER
390 , x_message_list OUT NOCOPY VARCHAR2
391 , x_return_status OUT NOCOPY VARCHAR2) IS
392
393 v_oprn_id gmd_operations.oprn_id%TYPE;
394 l_retn_status VARCHAR2(1);
395 l_api_version NUMBER := 1.0;
396
397 l_start_date VARCHAR2(30);
398 l_end_date VARCHAR2(30);
399 l_owner_orgn_id NUMBER;
400
401 invalid_version EXCEPTION;
402 setup_failure EXCEPTION;
403 upd_oprn_err EXCEPTION;
404
405 CURSOR get_oprn_id (v_oprn_no VARCHAR2, v_oprn_vers NUMBER) IS
406 SELECT oprn_id
407 FROM gmd_operations
408 where oprn_no = v_oprn_no
409 and oprn_vers = v_oprn_vers;
410
411 CURSOR check_oprn_id (v_oprn_id NUMBER) IS
412 SELECT oprn_id
413 FROM gmd_operations
414 where oprn_id = v_oprn_id;
415
416 CURSOR get_orgn_id (v_oprn_id NUMBER) IS
417 SELECT OWNER_ORGANIZATION_ID
418 FROM gmd_operations
419 where oprn_id = v_oprn_id;
420
421 l_orgn_id NUMBER;
422
423 BEGIN
424 SAVEPOINT update_oprn;
425
426 /* Initialize message list and count if needed */
427 IF p_init_msg_list THEN
428 fnd_msg_pub.initialize;
429 END IF;
430
431 /* Make sure we are call compatible */
432 IF NOT FND_API.compatible_api_call(l_api_version
433 ,p_api_version
434 ,'update_operation'
435 ,'gmd_operations_pub') THEN
436 RAISE invalid_version;
437 END IF;
438
439 /* Intializes the setup fields */
440 IF NOT gmd_api_grp.setup_done THEN
441 gmd_api_grp.setup_done := gmd_api_grp.setup;
442 END IF;
443 IF NOT gmd_api_grp.setup_done THEN
444 RAISE setup_failure;
445 END IF;
446
447 /* Initially let us assign the return status to success */
448 x_return_status := FND_API.g_ret_sts_success;
449
450 IF (l_debug = 'Y') THEN
451 gmd_debug.put_line('Start of update_operation PUB');
452 END IF;
453
454 /* Oprn_id or oprn_no and vers must be passed, otherwise give error */
455 IF (p_oprn_id IS NULL AND (p_oprn_no IS NULL OR p_oprn_vers IS NULL ))THEN
456 IF (l_debug = 'Y') THEN
457 gmd_debug.put_line('operation id or operation number and version are required');
458 END IF;
459
460 IF (p_oprn_id IS NULL) THEN
461 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
462 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_ID');
463 FND_MSG_PUB.ADD;
464 ELSE
465 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
466 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_NO, OPRN_VERS');
467 FND_MSG_PUB.ADD;
468 END IF;
469 RAISE upd_oprn_err;
470 ELSIF p_oprn_id IS NOT NULL THEN
471 OPEN check_oprn_id(p_oprn_id);
472 FETCH check_oprn_id INTO v_oprn_id;
473 IF check_oprn_id%NOTFOUND THEN
474 gmd_api_grp.log_message ('FM_INVOPRN');
475 RAISE upd_oprn_err;
476 END IF;
477 CLOSE check_oprn_id;
478 ELSIF ((p_oprn_no IS NOT NULL) AND (p_oprn_vers IS NOT NULL)) THEN
479 OPEN get_oprn_id(p_oprn_no, p_oprn_vers);
480 FETCH get_oprn_id INTO v_oprn_id;
481 IF get_oprn_id%NOTFOUND THEN
482 gmd_api_grp.log_message ('FM_INVOPRN');
483 RAISE upd_oprn_err;
484 END IF;
485 CLOSE get_oprn_id;
486 END IF;
487
488 -- Bug# 5552324 Kapil M
489 -- Added the check for Org Responsibility access.
490 OPEN get_orgn_id(p_oprn_id);
491 FETCH get_orgn_id INTO l_orgn_id;
492 CLOSE get_orgn_id;
493 IF NOT (GMD_API_GRP.OrgnAccessible (powner_orgn_id => l_orgn_id ) ) THEN
494 RAISE upd_oprn_err;
495 END IF;
496
497 /* Loop thru cols to be updated - verify col and value are present */
498 FOR i in 1 .. p_update_table.count LOOP
499 /* Col_to_update and value must be passed, otherwise give error */
500 IF p_update_table(i).p_col_to_update IS NULL THEN
501 IF (l_debug = 'Y') THEN
502 gmd_debug.put_line('col_to_update required');
503 END IF;
504
505 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
506 FND_MESSAGE.SET_TOKEN ('MISSING', 'COL_TO_UPDATE');
507 FND_MSG_PUB.ADD;
508 RAISE upd_oprn_err;
509 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'PROCESS_QTY_UOM' THEN
510 IF (NOT(gmd_api_grp.validate_um(p_update_table(i).p_value))) THEN
511 IF (l_debug = 'Y') THEN
512 gmd_debug.put_line('process qty uom invalid');
513 END IF;
514 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
515 FND_MSG_PUB.ADD;
516 RAISE upd_oprn_err;
517 END IF;
518 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'MINIMUM_TRANSFER_QTY' THEN
519 IF p_update_table(i).p_value < 0 THEN
520 gmd_api_grp.log_message ('GMD_NEGATIVE_FIELDS',
521 'FIELD', 'MINIMUM_TRANSFER_QTY');
522 RAISE upd_oprn_err;
523 END IF;
524 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OPRN_CLASS' THEN
525 IF gmdopval_pub.check_oprn_class(p_update_table(i).p_value, 'F') <> 0 THEN
526 gmd_api_grp.log_message('FM_INV_OPRN_CLASS');
527 RAISE upd_oprn_err;
528 END IF;
529 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OPRN_DESC' THEN
530 IF p_update_table(i).p_value IS NULL THEN
531 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
532 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_DESC');
533 FND_MSG_PUB.ADD;
534 RAISE upd_oprn_err;
535 END IF;
536 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK' THEN
537 GMDRTVAL_PUB.check_delete_mark ( Pdelete_mark => p_update_table(i).p_value,
538 x_return_status => l_retn_status);
539 IF l_retn_status <> 'S' THEN /* it indicates that invalid value has been passed */
540 FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
541 FND_MSG_PUB.ADD;
542 RAISE upd_oprn_err;
543 END IF;
544 ELSIF UPPER(p_update_table(i).p_col_to_update) = 'OWNER_ORGANIZATION_ID' THEN
545 /* Validation : Check if owner_orgn_idis valid */
546 l_owner_orgn_id := p_update_table(i).p_value;
547 IF NOT GMD_API_GRP.OrgnAccessible(l_owner_orgn_id) THEN
548 RAISE upd_oprn_err;
549 END IF;
550 END IF;
551
552 /* Validation : Verify Operation status is not On Hold nor Obsolete/Archived
553 and Operation is not logically deleted */
554 IF v_oprn_id IS NOT NULL THEN
555 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED
556 (Entity => 'OPERATION',
557 Entity_id => v_oprn_id,
558 Update_Column_name => p_update_table(i).p_col_to_update) THEN
559 FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
560 FND_MSG_PUB.ADD;
561 RAISE upd_oprn_err;
562 END IF;
563 END IF;
564 END LOOP;
565
566 IF x_return_status = 'S' THEN
567 IF (l_debug = 'Y') THEN
568 gmd_debug.put_line('before PVT routine called');
569 END IF;
570
571 GMD_OPERATIONS_PVT.update_operation(p_oprn_id => v_oprn_id
572 , p_update_table => p_update_table
573 , x_message_count => x_message_count
574 , x_message_list => x_message_list
575 , x_return_status => l_retn_status);
576 IF l_retn_status <> FND_API.g_ret_sts_success THEN
577 RAISE upd_oprn_err;
578 END IF;
579 IF p_commit THEN
580 COMMIT;
581 END IF;
582
583 /* Adding message to stack indicating the success of the routine */
584 gmd_api_grp.log_message ('GMD_SAVED_CHANGES');
585 END IF;
586
587 FND_MSG_PUB.count_and_get(p_count => x_message_count
588 ,p_data => x_message_list);
589
590 EXCEPTION
591 WHEN invalid_version or setup_failure THEN
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 ROLLBACK TO SAVEPOINT update_oprn;
594 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
595 P_data => x_message_list);
596 WHEN upd_oprn_err THEN
597 x_return_status := FND_API.G_RET_STS_ERROR;
598 ROLLBACK TO SAVEPOINT update_oprn;
599 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
600 P_data => x_message_list);
601 WHEN OTHERS THEN
602 ROLLBACK TO SAVEPOINT update_oprn;
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
605 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
606 FND_MSG_PUB.ADD;
607 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
608 P_data => x_message_list);
609
610 END update_operation;
611
612 /*===========================================================================================
613 Procedure
614 delete_operation
615 Description
616 This particular procedure is used to set delete_mark = 1 for an operation
617 Parameters
618 ================================================ */
619 PROCEDURE delete_operation (
620 p_api_version IN NUMBER
621 , p_init_msg_list IN BOOLEAN
622 , p_commit IN BOOLEAN
623 , p_oprn_id IN gmd_operations.oprn_id%TYPE
624 , p_oprn_no IN gmd_operations.oprn_no%TYPE
625 , p_oprn_vers IN gmd_operations.oprn_vers%TYPE
626 , x_message_count OUT NOCOPY NUMBER
627 , x_message_list OUT NOCOPY VARCHAR2
628 , x_return_status OUT NOCOPY VARCHAR2 ) IS
629
630 v_update_table gmd_operations_pub.update_tbl_type;
631 l_retn_status VARCHAR2(1);
632 l_api_version NUMBER := 1.0;
633
634 upd_oprn_err EXCEPTION;
635 BEGIN
636 SAVEPOINT delete_oprn;
637
638 /* Initialize message list and count if needed */
639 IF p_init_msg_list THEN
640 fnd_msg_pub.initialize;
641 END IF;
642
643 /* Initially let us assign the return status to success */
644 x_return_status := FND_API.g_ret_sts_success;
645
646 IF (l_debug = 'Y') THEN
647 gmd_debug.put_line('START of delete_operation PUB');
648 END IF;
649
650 /* Call update_operation and set delete mark for given activity to 1*/
651 v_update_table(1).p_col_to_update := 'DELETE_MARK';
652 v_update_table(1).p_value := '1';
653
654 /* call update with oprn id if that is what is passed */
655 update_operation(p_api_version => p_api_version
656 ,p_init_msg_list => FALSE
657 ,p_oprn_id => p_oprn_id
658 ,p_oprn_no => p_oprn_no
659 ,p_oprn_vers => p_oprn_vers
660 ,p_update_table => v_update_table
661 ,x_message_count => x_message_count
662 ,x_message_list => x_message_list
663 ,x_return_status => l_retn_status);
664
665 IF l_retn_status <> FND_API.g_ret_sts_success THEN
666 RAISE upd_oprn_err;
667 END IF;
668
669 IF p_commit THEN
670 COMMIT;
671 END IF;
672
673 /* Adding message to stack indicating the success of the routine */
674 gmd_api_grp.log_message ('GMD_SAVED_CHANGES');
675 FND_MSG_PUB.count_and_get(p_count => x_message_count
676 ,p_data => x_message_list);
677
678 IF (l_debug = 'Y') THEN
679 gmd_debug.put_line('END of delete_operation PUB');
680 END IF;
681
682 EXCEPTION
683 WHEN upd_oprn_err THEN
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 ROLLBACK TO SAVEPOINT delete_oprn;
686 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
687 P_data => x_message_list);
688 WHEN OTHERS THEN
689 ROLLBACK TO SAVEPOINT delete_oprn;
690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
692 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
693 FND_MSG_PUB.ADD;
694 FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
695 P_data => x_message_list);
696
697 END delete_operation;
698
699 END GMD_OPERATIONS_PUB;
[Home] [Help]