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