[Home] [Help]
PACKAGE BODY: APPS.GME_PROCESS_PARAMETERS_PVT
Source
1 PACKAGE BODY gme_process_parameters_pvt AS
2 /* $Header: GMEVPPRB.pls 120.3 2006/03/17 11:12:54 pxkumar noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_PROCESS_PARAMETERS_PVT';
5
6 /*===========================================================================================
7 Procedure
8 insert_process_parameter
9 Description
10 This particular procedure is used to insert parameter for an resource
11 Parameters
12 p_process_param_rec.batchstep_resource_id OR
13 (p_plant_code,p_batch_no,p_batchstep_no,p_activity,p_resource,p_parameter ) to uniquely
14 identify an process parameter
15 p_process_param_rec gme_process_parameters%ROWTYPE - details of the process parameter
16 x_return_status reflects return status of the API
17 =============================================================================================*/
18 PROCEDURE insert_process_parameter (
19 p_batch_no IN VARCHAR2
20 ,p_org_code IN VARCHAR2
21 ,p_validate_flexfields IN VARCHAR2
22 ,p_batchstep_no IN NUMBER
23 ,p_activity IN VARCHAR2
24 ,p_parameter IN VARCHAR2
25 ,p_process_param_rec IN gme_process_parameters%ROWTYPE
26 ,x_process_param_rec OUT NOCOPY gme_process_parameters%ROWTYPE
27 ,x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 CURSOR cur_validate_batch_type (v_resource_id NUMBER)
30 IS
31 SELECT 1
32 FROM gme_batch_header b, gme_batch_step_resources r
33 WHERE b.batch_id = r.batch_id
34 AND r.batchstep_resource_id = v_resource_id
35 AND b.batch_type = 10;
36
37 CURSOR cur_process_parameter (v_parameter VARCHAR2)
38 IS
39 SELECT parameter_id, parameter_type, minimum_value, maximum_value
40 ,units
41 FROM gmp_process_parameters
42 WHERE parameter_name = v_parameter AND delete_mark = 0;
43
44 CURSOR cur_process_value (v_parameter_id NUMBER, v_value VARCHAR2)
45 IS
46 SELECT 1
47 FROM gmp_parameter_values
48 WHERE parameter_id = v_parameter_id AND parameter_value = v_value;
49
50 resource_fetch_error EXCEPTION;
51 input_param_missing EXCEPTION;
52 validate_param_failed EXCEPTION;
53 val_pro_param_failed EXCEPTION;
54 param_details_not_found EXCEPTION;
55 wrong_target_value EXCEPTION;
56 invalid_step_status EXCEPTION;
57 wrong_actual_value EXCEPTION;
58 validation_failure EXCEPTION;
59 l_api_name CONSTANT VARCHAR2 (30) := 'INSERT_PROCESS_PARAMETER';
60 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
61 l_process_param_rec gme_process_parameters%ROWTYPE;
62 l_resource_id NUMBER;
63 l_activity_id NUMBER;
64 l_batchstep_id NUMBER;
65 l_dummy NUMBER;
66 l_batch_id NUMBER;
67 l_step_status NUMBER;
68 l_parameter_id NUMBER;
69 l_parameter_type NUMBER;
70 l_minimum_value NUMBER;
71 l_maximum_value NUMBER;
72 l_units VARCHAR2 (4);
73 v_temp NUMBER;
74 l_value NUMBER;
75 l_proc_param_id NUMBER;
76 l_resources VARCHAR2 (16);
77 l_batch_header gme_batch_header%ROWTYPE;
78 l_resource_row gme_batch_step_resources%ROWTYPE;
79 BEGIN
80 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
81 gme_debug.put_line ('Calling public Insert process parameters Row');
82 END IF;
83
84 x_return_status := fnd_api.g_ret_sts_success;
85
86 IF p_parameter IS NULL THEN
87 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
88 ,'FIELD_NAME'
89 ,'PROCESS PARAMETER');
90 RAISE input_param_missing;
91 END IF;
92
93 IF p_process_param_rec.batchstep_resource_id IS NOT NULL THEN
94 -- validate the resource provided
95 l_resource_row.batchstep_resource_id :=
96 p_process_param_rec.batchstep_resource_id;
97
98 IF NOT (gme_batch_step_resources_dbl.fetch_row (l_resource_row
99 ,l_resource_row) ) THEN
100 gme_common_pvt.log_message ('GME_RSRCID_NOT_FOUND'
101 ,'BATCHSTEP_RSRC_ID'
102 ,l_resource_row.batchstep_resource_id);
103 RAISE resource_fetch_error;
104 END IF;
105
106 l_resource_id := l_resource_row.batchstep_resource_id;
107 l_activity_id := l_resource_row.batchstep_activity_id;
108 l_batchstep_id := l_resource_row.batchstep_id;
109 l_batch_id := l_resource_row.batch_id;
110 l_resources := l_resource_row.resources;
111
112 -- make sure resource id does not belong to an FPO
113 OPEN cur_validate_batch_type (l_resource_id);
114
115 FETCH cur_validate_batch_type
116 INTO l_dummy;
117
118 IF cur_validate_batch_type%FOUND THEN
119 CLOSE cur_validate_batch_type;
120
121 gme_common_pvt.log_message ('GME_FPO_PARAM_NO_EDIT');
122 RAISE validate_param_failed;
123 END IF;
124
125 CLOSE cur_validate_batch_type;
126 ELSE /* p_process_param_rec.batchstep_resource_id IS NOT NULL */
127 l_resources := p_process_param_rec.resources;
128 validate_process_param (p_org_code => p_org_code
129 ,p_batch_no => p_batch_no
130 ,p_batchstep_no => p_batchstep_no
131 ,p_activity => p_activity
132 ,p_resource => l_resources
133 ,x_batch_id => l_batch_id
134 ,x_batchstep_id => l_batchstep_id
135 ,x_activity_id => l_activity_id
136 ,x_resource_id => l_resource_id
137 ,x_parameter_id => l_parameter_id
138 ,x_proc_param_id => l_proc_param_id
139 ,x_step_status => l_step_status
140 ,x_return_status => l_return_status);
141
142 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
143 gme_debug.put_line ( 'after validate_process_param '
144 || l_return_status);
145 END IF;
146
147 IF l_return_status <> 'S' THEN
148 RAISE val_pro_param_failed;
149 END IF;
150 END IF; /* p_process_param_rec.batchstep_resource_id IS NOT NULL */
151
152 -- Check Step Status
153 IF l_step_status IN (4, 5) THEN
154 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
155 RAISE invalid_step_status;
156 END IF;
157
158 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
159 gme_debug.put_line ('after step status');
160 END IF;
161
162 -- Get process parameters details from GMP tables
163 OPEN cur_process_parameter (p_parameter);
164
165 FETCH cur_process_parameter
166 INTO l_parameter_id, l_parameter_type, l_minimum_value
167 ,l_maximum_value, l_units;
168
169 IF cur_process_parameter%NOTFOUND THEN
170 CLOSE cur_process_parameter;
171
172 gme_common_pvt.log_message ('GME_PARAM_NOT_FOUND'
173 ,'PARAMETER'
174 ,p_parameter);
175 RAISE param_details_not_found;
176 END IF;
177
178 CLOSE cur_process_parameter;
179
180 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
181 gme_debug.put_line ('after getting param details ');
182 END IF;
183
184 -- Check for target value
185 IF p_process_param_rec.target_value IS NOT NULL THEN
186 IF l_parameter_type = 2 THEN
187 BEGIN
188 v_temp := p_process_param_rec.target_value;
189 EXCEPTION
190 WHEN VALUE_ERROR THEN
191 x_return_status := fnd_api.g_ret_sts_error;
192 gme_common_pvt.log_message
193 ('GME_INVALID_FIELD'
194 ,'FIELD'
195 ,'p_process_param_rec.target_value');
196 RETURN;
197 END;
198 ELSE
199 IF l_parameter_type = 3 THEN
200 OPEN cur_process_value (l_parameter_id
201 ,p_process_param_rec.target_value);
202
203 FETCH cur_process_value
204 INTO l_value;
205
206 IF cur_process_value%NOTFOUND THEN
207 CLOSE cur_process_value;
208
209 gme_common_pvt.log_message
210 ('GME_INVALID_FIELD'
211 ,'FIELD'
212 ,'p_process_param_rec.target_value');
213 RAISE wrong_target_value;
214 END IF;
215
216 CLOSE cur_process_value;
217 END IF;
218 END IF; /* l_parameter_type = 2*/
219
220 --check for target value with minimum value
221 IF l_minimum_value IS NOT NULL THEN
222 IF (p_process_param_rec.target_value < l_minimum_value) THEN
223 fnd_message.set_name ('GMD', 'LM_OUTOFRANGE');
224 END IF;
225 END IF; /* l_minimum_value*/
226
227 --check for target value with maximum value
228 IF l_maximum_value IS NOT NULL THEN
229 IF (p_process_param_rec.target_value > l_maximum_value) THEN
230 fnd_message.set_name ('GMD', 'LM_OUTOFRANGE');
231 END IF;
232 END IF; /* l_maximum_value*/
233 END IF; /* p_process_param_rec.target_value IS NOT NULL*/
234
235 --check for actual value
236 IF l_step_status IN (2, 3) THEN
237 IF p_process_param_rec.actual_value IS NOT NULL THEN
238 IF l_parameter_type = 2 THEN
239 BEGIN
240 v_temp := p_process_param_rec.actual_value;
241 EXCEPTION
242 WHEN VALUE_ERROR THEN
243 gme_common_pvt.log_message
244 ('GME_INVALID_FIELD'
245 ,'FIELD'
246 ,'p_process_param_rec.actual_value');
247 x_return_status := fnd_api.g_ret_sts_error;
248 RETURN;
249 END;
250
251 -- check for actual value with minimum value
252 IF l_minimum_value IS NOT NULL THEN
253 IF (p_process_param_rec.actual_value < l_minimum_value) THEN
254 fnd_message.set_name ('GMD', 'LM_OUTOFRANGE');
255 END IF;
256 END IF; /* l_minimum_value*/
257
258 --check for actual value with maximum value
259 IF l_maximum_value IS NOT NULL THEN
260 IF (p_process_param_rec.actual_value > l_maximum_value) THEN
261 fnd_message.set_name ('GMD', 'LM_OUTOFRANGE');
262 END IF;
263 END IF; /* l_maximum_value*/
264 END IF; /* l_parameter_type = 2*/
265 END IF; /* p_process_param_rec.actual_value IS NOT NULL*/
266 END IF; /* l_step_status IN (2,3)*/
267
268 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
269 gme_debug.put_line ('after validate actual value');
270 END IF;
271
272 --Validate flex fields
273 /* Nsinha as part of GME_Process_Parameter_APIs_TD
274 Modify the existing logic and directly call gme_api_validate_flex_fld_pvt.validate_flex_process_param API
275 along with newly added IN parameter p_validate_flexfields. */
276 gme_validate_flex_fld_pvt.validate_flex_process_param
277 (p_process_param_rec => p_process_param_rec
278 ,p_validate_flexfields => p_validate_flexfields
279 ,x_process_param_rec => l_process_param_rec
280 ,x_return_status => l_return_status);
281
282 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
283 gme_debug.put_line ('validate flex return ' || l_return_status);
284 END IF;
285
286 IF l_return_status <> fnd_api.g_ret_sts_success THEN
287 RAISE validation_failure;
288 END IF;
289
290 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
291 gme_debug.put_line ('after validate flex fields');
292 END IF;
293
294 l_process_param_rec.batch_id := l_batch_id;
295 l_process_param_rec.batchstep_id := l_batchstep_id;
296 l_process_param_rec.batchstep_activity_id := l_activity_id;
297 l_process_param_rec.batchstep_resource_id := l_resource_id;
298 l_process_param_rec.resources := l_resources;
299 l_process_param_rec.parameter_id := l_parameter_id;
300 l_process_param_rec.minimum_value := l_minimum_value;
301 l_process_param_rec.maximum_value := l_maximum_value;
302 l_process_param_rec.parameter_uom := l_units;
303
304 -- null out values of actual fields for pending step
305 IF l_step_status = 1 THEN
306 l_process_param_rec.target_value := p_process_param_rec.target_value;
307 l_process_param_rec.actual_value := NULL;
308 ELSE
309 l_process_param_rec.target_value := p_process_param_rec.target_value;
310 l_process_param_rec.actual_value := p_process_param_rec.actual_value;
311 END IF;
312
313 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
314 gme_debug.put_line ('call insert ' || x_return_status);
315 END IF;
316
317 /* Nsinha as part of GME_Process_Parameter_APIs_TD:
318 Replaced the call to gme_api_process_parameters.insert_process_param
319 with gme_process_parameters_dbl.insert_row
320 */
321 IF NOT gme_process_parameters_dbl.insert_row
322 (p_process_parameters => l_process_param_rec
323 ,x_process_parameters => x_process_param_rec) THEN
324 RAISE fnd_api.g_exc_error;
325 END IF;
326
327 IF g_debug <= gme_debug.g_log_procedure THEN
328 gme_debug.put_line ( g_pkg_name
329 || '.'
330 || l_api_name
331 || ':'
332 || 'Exiting with '
333 || x_return_status
334 || ' at '
335 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
336 END IF;
337 EXCEPTION
338 WHEN input_param_missing OR validate_param_failed OR invalid_step_status OR param_details_not_found OR wrong_target_value THEN
339 x_return_status := fnd_api.g_ret_sts_error;
340 WHEN resource_fetch_error THEN
341 x_return_status := fnd_api.g_ret_sts_error;
342 WHEN val_pro_param_failed THEN
343 x_return_status := l_return_status;
344 WHEN validation_failure THEN
345 x_return_status := fnd_api.g_ret_sts_error;
346 WHEN OTHERS THEN
347 x_return_status := fnd_api.g_ret_sts_unexp_error;
348 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
349 END insert_process_parameter;
350
351 /*===========================================================================================
352 Procedure
353 update_process_parameter
354 Description
355 This particular procedure is used to insert parameter for an resource
356 Parameters
357 p_process_param_rec.batchstep_resource_id OR
358 (p_plant_code,p_batch_no,p_batchstep_no,p_activity,p_parameter ) to uniquely
359 identify an process parameter
360 p_process_param_rec gme_process_parameters%ROWTYPE - details of the process parameter
361 x_return_status reflects return status of the API
362 =============================================================================================*/
363 PROCEDURE update_process_parameter (
364 p_batch_no IN VARCHAR2 := NULL
365 ,p_org_code IN VARCHAR2 := NULL
366 ,p_validate_flexfields IN VARCHAR2 := fnd_api.g_false
367 ,p_batchstep_no IN NUMBER := NULL
368 ,p_activity IN VARCHAR2 := NULL
369 ,p_parameter IN VARCHAR2 := NULL
370 ,p_process_param_rec IN gme_process_parameters%ROWTYPE
371 ,x_process_param_rec OUT NOCOPY gme_process_parameters%ROWTYPE
372 ,x_return_status OUT NOCOPY VARCHAR2)
373 IS
374 CURSOR cur_validate_parameter (v_proc_param_id NUMBER)
375 IS
376 SELECT batchstep_id, batch_id
377 FROM gme_process_parameters
378 WHERE process_param_id = v_proc_param_id;
379
380 CURSOR cur_validate_batch_type (v_proc_param_id NUMBER)
381 IS
382 SELECT 1
383 FROM gme_batch_header b, gme_process_parameters p
384 WHERE b.batch_id = p.batch_id
385 AND p.process_param_id = v_proc_param_id
386 AND b.batch_type = 10;
387
388 CURSOR cur_process_parameter (v_parameter_id NUMBER)
389 IS
390 SELECT parameter_type
391 FROM gmp_process_parameters
392 WHERE parameter_id = v_parameter_id AND delete_mark = 0;
393
394 CURSOR cur_process_value (v_parameter_id NUMBER)
395 IS
396 SELECT parameter_value
397 FROM gmp_parameter_values
398 WHERE parameter_id = v_parameter_id;
399
400 input_param_missing EXCEPTION;
401 validate_param_failed EXCEPTION;
402 val_pro_param_failed EXCEPTION;
403 wrong_target_value EXCEPTION;
404 invalid_step_status EXCEPTION;
405 wrong_actual_value EXCEPTION;
406 validation_failure EXCEPTION;
407 param_not_found EXCEPTION;
408 update_param_failed EXCEPTION;
409 no_change EXCEPTION;
410 param_details_not_found EXCEPTION;
411 batch_steps_fetch_error EXCEPTION;
412 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_PROCESS_PARAMETER';
413 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
414 l_process_param_rec gme_process_parameters%ROWTYPE;
415 l_batch_steps gme_batch_steps%ROWTYPE;
416 l_proc_param_id NUMBER;
417 l_resource_id NUMBER;
418 l_activity_id NUMBER;
419 l_batchstep_id NUMBER;
420 l_dummy NUMBER;
421 l_batch_id NUMBER;
422 l_step_status NUMBER;
423 l_parameter_id NUMBER;
424 l_parameter_type NUMBER;
425 l_minimum_value NUMBER;
426 l_maximum_value NUMBER;
427 l_units VARCHAR2 (4);
428 v_temp NUMBER;
429 l_value NUMBER;
430 l_flex_validate BOOLEAN := FALSE;
431 l_field_updated BOOLEAN := FALSE;
432 BEGIN
433 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
434 gme_debug.put_line ('Calling public update process parameters Row');
435 END IF;
436
437 /* Initially let us assign the return status to success */
438 x_return_status := fnd_api.g_ret_sts_success;
439
440 IF (p_parameter IS NULL AND p_process_param_rec.process_param_id IS NULL) THEN
441 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
442 ,'FIELD_NAME'
443 ,'PROCESS PARAMETERS');
444 RAISE input_param_missing;
445 END IF;
446
447 IF p_process_param_rec.process_param_id IS NOT NULL THEN
448 -- validate the process parameter provided
449 l_proc_param_id := p_process_param_rec.process_param_id;
450
451 OPEN cur_validate_parameter (l_proc_param_id);
452
453 FETCH cur_validate_parameter
454 INTO l_batchstep_id, l_batch_id;
455
456 IF cur_validate_parameter%NOTFOUND THEN
457 CLOSE cur_validate_parameter;
458
459 gme_common_pvt.log_message ('GME_PARAM_ID_NOT_FOUND'
460 ,'PROCESS_PARAM_ID'
461 ,l_proc_param_id);
462 RAISE param_not_found;
463 END IF;
464
465 CLOSE cur_validate_parameter;
466
467 /* Fetch the step status*/
468 l_batch_steps.batch_id := l_batch_id;
469 l_batch_steps.batchstep_id := l_batchstep_id;
470
471 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_steps, l_batch_steps) ) THEN
472 RAISE batch_steps_fetch_error;
473 END IF;
474
475 l_step_status := l_batch_steps.step_status;
476
477 -- make sure process parameter id does not belong to an FPO
478 OPEN cur_validate_batch_type (l_proc_param_id);
479
480 FETCH cur_validate_batch_type
481 INTO l_dummy;
482
483 IF cur_validate_batch_type%FOUND THEN
484 CLOSE cur_validate_batch_type;
485
486 gme_common_pvt.log_message ('GME_FPO_PARAM_NO_EDIT');
487 RAISE validate_param_failed;
488 END IF;
489
490 CLOSE cur_validate_batch_type;
491 ELSE
492 validate_process_param (p_org_code => p_org_code
493 ,p_batch_no => p_batch_no
494 ,p_batchstep_no => p_batchstep_no
495 ,p_activity => p_activity
496 ,p_resource => p_process_param_rec.resources
497 ,p_parameter => p_parameter
498 ,x_batch_id => l_batch_id
499 ,x_batchstep_id => l_batchstep_id
500 ,x_activity_id => l_activity_id
501 ,x_resource_id => l_resource_id
502 ,x_parameter_id => l_parameter_id
503 ,x_proc_param_id => l_proc_param_id
504 ,x_step_status => l_step_status
505 ,x_return_status => l_return_status);
506
507 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
508 gme_debug.put_line ( 'after validate_process_param '
509 || l_return_status);
510 END IF;
511
512 IF l_return_status <> 'S' THEN
513 RAISE val_pro_param_failed;
514 END IF;
515 END IF;
516
517 IF l_step_status IN (4, 5) THEN
518 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
519 RAISE invalid_step_status;
520 END IF;
521
522 l_process_param_rec.process_param_id := l_proc_param_id;
523
524 IF NOT gme_process_parameters_dbl.fetch_row (l_process_param_rec
525 ,l_process_param_rec) THEN
526 RAISE fnd_api.g_exc_error;
527 END IF;
528
529 -- Get process parameters details from GMP tables
530 OPEN cur_process_parameter (l_process_param_rec.parameter_id);
531
532 FETCH cur_process_parameter
533 INTO l_parameter_type;
534
535 IF cur_process_parameter%NOTFOUND THEN
536 CLOSE cur_process_parameter;
537
538 RAISE param_details_not_found;
539 END IF;
540
541 CLOSE cur_process_parameter;
542
543 IF l_step_status IN (2, 3) THEN
544 IF p_process_param_rec.actual_value = fnd_api.g_miss_char THEN
545 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
546 ,'FIELD_NAME'
547 ,'ACTUAL_VALUE');
548 RAISE input_param_missing;
549 ELSIF ( p_process_param_rec.actual_value IS NOT NULL
550 AND ( (NVL (l_process_param_rec.actual_value, '0') <>
551 p_process_param_rec.actual_value) )
552 OR l_process_param_rec.actual_value IS NULL) THEN
553 IF l_parameter_type = 2 THEN
554 BEGIN
555 v_temp := p_process_param_rec.actual_value;
556 EXCEPTION
557 WHEN VALUE_ERROR THEN
558 gme_common_pvt.log_message
559 ('GME_INVALID_FIELD'
560 ,'FIELD'
561 ,'p_process_param_rec.actual_value');
562 x_return_status := fnd_api.g_ret_sts_error;
563 RETURN;
564 END;
565 END IF;
566
567 l_field_updated := TRUE;
568 l_process_param_rec.actual_value :=
569 p_process_param_rec.actual_value;
570 END IF;
571 END IF; /*l_step_status*/
572
573 IF p_validate_flexfields = fnd_api.g_true THEN
574 l_field_updated := TRUE;
575 END IF;
576
577 /* Nsinha as part of GME_Process_Parameter_APIs_TD
578 Modify the existing logic and directly call gme_api_validate_flex_fld_pvt.validate_flex_process_param API
579 along with newly added IN parameter p_validate_flexfields. */
580 gme_validate_flex_fld_pvt.validate_flex_process_param
581 (p_process_param_rec => p_process_param_rec
582 ,p_validate_flexfields => p_validate_flexfields
583 ,x_process_param_rec => l_process_param_rec
584 ,x_return_status => l_return_status);
585
586 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
587 gme_debug.put_line ('validate flex return ' || l_return_status);
588 END IF;
589
590 IF l_return_status <> fnd_api.g_ret_sts_success THEN
591 RAISE validation_failure;
592 END IF;
593
594 IF NOT (l_field_updated) THEN
595 gme_common_pvt.log_message ('GME_NO_CHANGE_TO_UPD');
596 RAISE no_change;
597 END IF;
598
599 /* Replace the call to gme_api_process_parameters.update_process_param
600 with gme_process_parameters_dbl.update_row
601 */
602 IF NOT gme_process_parameters_dbl.update_row
603 (p_process_parameters => l_process_param_rec) THEN
604 RAISE fnd_api.g_exc_error;
605 END IF;
606
607 x_process_param_rec := l_process_param_rec;
608
609 IF g_debug <= gme_debug.g_log_procedure THEN
610 gme_debug.put_line ( g_pkg_name
611 || '.'
612 || l_api_name
613 || ':'
614 || 'Exiting with '
615 || x_return_status
616 || ' at '
617 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
618 END IF;
619 EXCEPTION
620 WHEN input_param_missing OR validate_param_failed OR param_not_found OR invalid_step_status OR param_details_not_found OR batch_steps_fetch_error OR wrong_target_value OR no_change THEN
621 x_return_status := fnd_api.g_ret_sts_error;
622 WHEN VALUE_ERROR THEN
623 x_return_status := fnd_api.g_ret_sts_error;
624 WHEN fnd_api.g_exc_error THEN
625 x_return_status := fnd_api.g_ret_sts_error;
626 WHEN validation_failure THEN
627 x_return_status := fnd_api.g_ret_sts_error;
628 WHEN val_pro_param_failed OR update_param_failed THEN
629 x_return_status := l_return_status;
630 WHEN OTHERS THEN
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
633 END update_process_parameter;
634
635 /*===========================================================================================
636 Procedure
637 delete_process_parameter
638 Description
639 This particular procedure is used to insert parameter for an resource
640 Parameters
641 p_process_param_rec.batchstep_resource_id OR
642 (p_plant_code,p_batch_no,p_batchstep_no,p_activity,p_parameter ) to uniquely
643 identify an process parameter
644 p_process_param_rec gme_process_parameters%ROWTYPE - details of the process parameter
645 x_return_status reflects return status of the API
646 =============================================================================================*/
647 PROCEDURE delete_process_parameter (
648 p_batch_no IN VARCHAR2
649 ,p_org_code IN VARCHAR2
650 ,p_batchstep_no IN NUMBER
651 ,p_activity IN VARCHAR2
652 ,p_parameter IN VARCHAR2
653 ,p_process_param_rec IN gme_process_parameters%ROWTYPE
654 ,x_return_status OUT NOCOPY VARCHAR2)
655 IS
656 CURSOR cur_validate_parameter (v_proc_param_id NUMBER)
657 IS
658 SELECT batchstep_id, batch_id
659 FROM gme_process_parameters
660 WHERE process_param_id = v_proc_param_id;
661
662 CURSOR cur_validate_batch_type (v_proc_param_id NUMBER)
663 IS
664 SELECT 1
665 FROM gme_batch_header b, gme_process_parameters p
666 WHERE b.batch_id = p.batch_id
667 AND p.process_param_id = v_proc_param_id
668 AND b.batch_type = 10;
669
670 /*siva added following cursor */
671 CURSOR cur_validate_batch_status(v_batch_id NUMBER)
672 IS
673 SELECT batch_status
674 FROM gme_batch_header
675 WHERE batch_id = v_batch_id;
676
677 input_param_missing EXCEPTION;
678 validate_param_failed EXCEPTION;
679 val_pro_param_failed EXCEPTION;
680 wrong_target_value EXCEPTION;
681 invalid_batch_status EXCEPTION;
682 param_not_found EXCEPTION;
683 delete_param_failed EXCEPTION;
684 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_PROCESS_PARAMETER';
685 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
686 l_process_param_rec gme_process_parameters%ROWTYPE;
687 l_proc_param_id NUMBER;
688 l_resource_id NUMBER;
689 l_activity_id NUMBER;
690 l_batchstep_id NUMBER;
691 l_dummy NUMBER;
692 l_batch_id NUMBER;
693 l_step_status NUMBER;
694 l_parameter_id NUMBER;
695 l_batch_status NUMBER;
696 BEGIN
697 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
698 gme_debug.put_line ('Calling public delete process parameters Row');
699 END IF;
700
701 IF (p_parameter IS NULL AND p_process_param_rec.process_param_id IS NULL) THEN
702 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
703 ,'FIELD_NAME'
704 ,'PROCESS PARAMETERS');
705 RAISE input_param_missing;
706 END IF;
707
708 IF p_process_param_rec.process_param_id IS NOT NULL THEN
709 -- validate the process parameter provided
710 l_proc_param_id := p_process_param_rec.process_param_id;
711
712 OPEN cur_validate_parameter (l_proc_param_id);
713
714 FETCH cur_validate_parameter
715 INTO l_batchstep_id, l_batch_id;
716
717 IF cur_validate_parameter%NOTFOUND THEN
718 CLOSE cur_validate_parameter;
719
720 gme_common_pvt.log_message ('GME_PARAM_ID_NOT_FOUND'
721 ,'PROCESS_PARAM_ID'
722 ,l_proc_param_id);
723 RAISE param_not_found;
724 END IF;
725
726 CLOSE cur_validate_parameter;
727
728 -- make sure process parameter id does not belong to an FPO
729 OPEN cur_validate_batch_type (l_proc_param_id);
730
731 FETCH cur_validate_batch_type
732 INTO l_dummy;
733
734 IF cur_validate_batch_type%FOUND THEN
735 CLOSE cur_validate_batch_type;
736
737 gme_common_pvt.log_message ('GME_FPO_PARAM_NO_EDIT');
738 RAISE validate_param_failed;
739 END IF;
740
741 CLOSE cur_validate_batch_type;
742 ELSE
743 validate_process_param (p_org_code => p_org_code
744 ,p_batch_no => p_batch_no
745 ,p_batchstep_no => p_batchstep_no
746 ,p_activity => p_activity
747 ,p_resource => p_process_param_rec.resources
748 ,p_parameter => p_parameter
749 ,x_batch_id => l_batch_id
750 ,x_batchstep_id => l_batchstep_id
751 ,x_activity_id => l_activity_id
752 ,x_resource_id => l_resource_id
753 ,x_parameter_id => l_parameter_id
754 ,x_proc_param_id => l_proc_param_id
755 ,x_step_status => l_step_status
756 ,x_return_status => l_return_status);
757
758 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
759 gme_debug.put_line ( 'after validate_process_param '
760 || l_return_status);
761 END IF;
762
763 IF l_return_status <> 'S' THEN
764 RAISE val_pro_param_failed;
765 END IF;
766 END IF;
767
768 /* siva begin */
769 OPEN cur_validate_batch_status(l_batch_id);
770 FETCH cur_validate_batch_status INTO l_batch_status;
771 IF cur_validate_batch_status%FOUND THEN
772 IF l_batch_status <> 1 THEN
773 CLOSE cur_validate_batch_status;
774 gme_common_pvt.log_message('PM_WRONG_STATUS');
775 RAISE invalid_batch_status;
776 END IF;
777 END IF;
778 CLOSE cur_validate_batch_status;
779
780 /* IF l_step_status <> 1 THEN
781 gme_common_pvt.log_message ('PC_STEP_STATUS_ERR');
782 RAISE invalid_step_status;
783 END IF;*/
784 /* siva end */
785
786 l_process_param_rec.process_param_id := l_proc_param_id;
787 x_return_status := l_return_status;
788
789 /* Replace the call to gme_api_process_parameters.delete_process_param
790 with gme_process_parameters_dbl.delete_row
791 */
792 IF NOT gme_process_parameters_dbl.delete_row
793 (p_process_parameters => l_process_param_rec) THEN
794 RAISE fnd_api.g_exc_error;
795 END IF;
796 EXCEPTION
797 WHEN input_param_missing OR validate_param_failed OR param_not_found OR invalid_batch_status THEN
798 x_return_status := fnd_api.g_ret_sts_error;
799 WHEN val_pro_param_failed OR delete_param_failed THEN
800 x_return_status := l_return_status;
801 WHEN OTHERS THEN
802 x_return_status := fnd_api.g_ret_sts_unexp_error;
803 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
804 END delete_process_parameter;
805
806 /*===========================================================================================
807 Procedure
808 validate_process_param
809 Description
810 This particular procedure is used to validate parameter combo provided to identify an activity
811 passed to rsrc APIs
812 Parameters
813 (p_plant_code,p_batch_no,step_no and activity ) to uniquely identify an activity
814 x_return_status reflects return status of the API
815 =============================================================================================*/
816 PROCEDURE validate_process_param (
817 p_org_code IN VARCHAR2
818 ,p_batch_no IN VARCHAR2
819 ,p_batchstep_no IN NUMBER
820 ,p_activity IN VARCHAR2
821 ,p_resource IN VARCHAR2
822 ,p_parameter IN VARCHAR2
823 ,x_batch_id OUT NOCOPY NUMBER
824 ,x_batchstep_id OUT NOCOPY NUMBER
825 ,x_activity_id OUT NOCOPY NUMBER
826 ,x_resource_id OUT NOCOPY NUMBER
827 ,x_parameter_id OUT NOCOPY NUMBER
828 ,x_proc_param_id OUT NOCOPY NUMBER
829 ,x_step_status OUT NOCOPY NUMBER
830 ,x_return_status OUT NOCOPY VARCHAR2)
831 IS
832 l_api_name CONSTANT VARCHAR2 (30) := 'validate_process_param';
833 l_activity_id NUMBER;
834 l_parameter_id NUMBER;
835 l_resource VARCHAR2 (16);
836 l_step_status NUMBER;
837 l_resource_id NUMBER;
838 l_batch_type NUMBER;
839 l_rsrc_not_found BOOLEAN;
840 l_act_found BOOLEAN;
841 l_count NUMBER := 0;
842 l_act_count NUMBER := 0;
843 l_param_no NUMBER := 0;
844 l_param_id NUMBER;
845 l_batch_header gme_batch_header%ROWTYPE;
846 l_batch_steps gme_batch_steps%ROWTYPE;
847
848 CURSOR cur_check_act_resource (
849 v_step_id NUMBER
850 ,v_activity VARCHAR2
851 ,v_batch_id NUMBER
852 ,v_resource VARCHAR2)
853 IS
854 SELECT COUNT (*)
855 FROM gme_batch_step_activities a, gme_batch_step_resources r
856 WHERE a.batch_id = r.batch_id
857 AND a.batchstep_id = r.batchstep_id
858 AND a.batchstep_activity_id = r.batchstep_activity_id
859 AND a.batch_id = v_batch_id
860 AND a.batchstep_id = v_step_id
861 AND a.activity = v_activity
862 AND r.resources = v_resource;
863
864 CURSOR cur_get_activity_id (
865 v_step_id NUMBER
866 ,v_activity VARCHAR2
867 ,v_batch_id NUMBER)
868 IS
869 SELECT batchstep_activity_id
870 FROM gme_batch_step_activities
871 WHERE batchstep_id = v_step_id
872 AND batch_id = v_batch_id
873 AND activity = v_activity;
874
875 CURSOR cur_fetch_resource_dtl (v_activity_id NUMBER, v_resource VARCHAR2)
876 IS
877 SELECT batchstep_resource_id
878 FROM gme_batch_step_resources
879 WHERE batchstep_activity_id = v_activity_id
880 AND resources = v_resource;
881
882 CURSOR cur_check_parameter (v_parameter VARCHAR2)
883 IS
884 SELECT parameter_id
885 FROM gmp_process_parameters
886 WHERE parameter_name = v_parameter AND delete_mark = 0;
887
888 CURSOR cur_get_parameter (v_resource_id NUMBER, v_parameter VARCHAR2)
889 IS
890 SELECT process_param_id
891 FROM gme_process_parameters p, gmp_process_parameters l
892 WHERE p.parameter_id = l.parameter_id
893 AND p.batchstep_resource_id = v_resource_id
894 AND l.parameter_name = v_parameter
895 AND l.delete_mark = 0;
896
897 batch_header_fetch_error EXCEPTION;
898 batch_steps_fetch_error EXCEPTION;
899 stepactivity_not_found EXCEPTION;
900 resource_not_found EXCEPTION;
901 dup_act_rsrc_found EXCEPTION;
902 input_param_missing EXCEPTION;
903 dup_param_found EXCEPTION;
904 param_not_found EXCEPTION;
905 setup_failure EXCEPTION;
906 BEGIN
907 /* Initially let us assign the return status to success */
908 x_return_status := fnd_api.g_ret_sts_success;
909
910 IF p_activity IS NULL THEN
911 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
912 ,'FIELD_NAME'
913 ,'ACTIVITY');
914 RAISE input_param_missing;
915 ELSIF p_resource IS NULL THEN
916 gme_common_pvt.log_message ('GME_FIELD_VALUE_REQUIRED'
917 ,'FIELD_NAME'
918 ,'RESOURCE');
919 RAISE input_param_missing;
920 END IF;
921
922 -- Check setup is done. For all profile/parameter values based on orgn_code/organization_id.
923 gme_common_pvt.g_setup_done :=
924 gme_common_pvt.setup (p_org_code => p_org_code);
925
926 IF NOT gme_common_pvt.g_setup_done THEN
927 RAISE setup_failure;
928 END IF;
929
930 l_batch_header.organization_id := gme_common_pvt.g_organization_id;
931 -- Validate input param one by one to see if it identifies a resource/process parameter correctly
932 l_batch_header.organization_id := gme_common_pvt.g_organization_id;
933 l_batch_header.batch_no := p_batch_no;
934 l_batch_header.batch_type := 0;
935
936 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
937 RAISE batch_header_fetch_error;
938 END IF;
939
940 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
941 gme_debug.put_line ('validation- after batch fetch');
942 END IF;
943
944 x_batch_id := l_batch_header.batch_id;
945 -- use batch_id to fetch batchstep_id
946 l_batch_steps.batch_id := x_batch_id;
947 l_batch_steps.batchstep_no := p_batchstep_no;
948
949 IF NOT (gme_batch_steps_dbl.fetch_row (l_batch_steps, l_batch_steps) ) THEN
950 RAISE batch_steps_fetch_error;
951 END IF;
952
953 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
954 gme_debug.put_line ('validation- after step fetch');
955 END IF;
956
957 x_step_status := l_batch_steps.step_status;
958 x_batchstep_id := l_batch_steps.batchstep_id;
959
960 FOR step_activity IN cur_get_activity_id (x_batchstep_id
961 ,p_activity
962 ,x_batch_id) LOOP
963 x_activity_id := step_activity.batchstep_activity_id;
964 l_act_found := TRUE;
965 l_activity_id := x_activity_id;
966
967 OPEN cur_fetch_resource_dtl (l_activity_id, p_resource);
968
969 FETCH cur_fetch_resource_dtl
970 INTO l_resource_id;
971
972 IF cur_fetch_resource_dtl%NOTFOUND THEN
973 CLOSE cur_fetch_resource_dtl;
974
975 l_rsrc_not_found := TRUE;
976 ELSE
977 CLOSE cur_fetch_resource_dtl;
978
979 l_rsrc_not_found := FALSE;
980 x_resource_id := l_resource_id;
981 EXIT;
982 END IF;
983 END LOOP;
984
985 IF NOT l_act_found THEN
986 gme_common_pvt.log_message ('GME_STEP_ACTIVITY_NOT_FOUND'
987 ,'ACTIVITY'
988 ,p_activity
989 ,'STEP_NO'
990 ,p_batchstep_no);
991 RAISE stepactivity_not_found;
992 END IF;
993
994 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
995 gme_debug.put_line ('validation- after activity check');
996 END IF;
997
998 -- If resource was not found in any activity then report error
999 IF l_rsrc_not_found THEN
1000 gme_common_pvt.log_message ('GME_RSRC_NOT_FOUND'
1001 ,'RESOURCE'
1002 ,p_resource
1003 ,'ACTIVITY'
1004 ,p_activity);
1005 RAISE resource_not_found;
1006 END IF;
1007
1008 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1009 gme_debug.put_line ('validation- after duplicate resource');
1010 END IF;
1011
1012 -- if same activity and resource combination exists then raise error
1013 OPEN cur_check_act_resource (x_batchstep_id
1014 ,p_activity
1015 ,x_batch_id
1016 ,p_resource);
1017
1018 FETCH cur_check_act_resource
1019 INTO l_act_count;
1020
1021 IF l_act_count > 1 THEN
1022 CLOSE cur_check_act_resource;
1023
1024 gme_common_pvt.log_message ('GME_BATCH_SAME_ACT_RSRC_FOUND'
1025 ,'ACTIVITY'
1026 ,p_activity
1027 ,'RESOURCE'
1028 ,p_resource);
1029 RAISE dup_act_rsrc_found;
1030 END IF;
1031
1032 CLOSE cur_check_act_resource;
1033
1034 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1035 gme_debug.put_line ('validation- after act-resource check');
1036 END IF;
1037
1038 -- check teh parameter exists in gmp
1039 IF p_parameter IS NOT NULL THEN
1040 OPEN cur_check_parameter (p_parameter);
1041
1042 FETCH cur_check_parameter
1043 INTO l_parameter_id;
1044
1045 IF cur_check_parameter%NOTFOUND THEN
1046 CLOSE cur_check_parameter;
1047
1048 gme_common_pvt.log_message ('GME_PARAM_NOT_FOUND'
1049 ,'PARAMETER'
1050 ,p_parameter);
1051 RAISE param_not_found;
1052 END IF;
1053
1054 CLOSE cur_check_parameter;
1055 END IF;
1056
1057 x_parameter_id := l_parameter_id;
1058
1059 -- check for process parameter for update and delete only
1060 IF p_parameter IS NOT NULL THEN
1061 gme_debug.put_line ( 'validation- after act-resource check'
1062 || x_resource_id
1063 || 'p_parameter');
1064
1065 FOR process_param IN cur_get_parameter (x_resource_id, p_parameter) LOOP
1066 l_count := l_count + 1;
1067 x_proc_param_id := process_param.process_param_id;
1068 END LOOP;
1069
1070 IF l_count > 1 THEN
1071 gme_common_pvt.log_message ('GME_BATCH_SAME_PARAM_FOUND'
1072 ,'PARAMETER'
1073 ,p_parameter);
1074 RAISE dup_param_found;
1075 ELSIF l_count = 0 THEN
1076 gme_common_pvt.log_message ('GME_PARAM_NOT_FOUND'
1077 ,'PARAMETER'
1078 ,p_parameter);
1079 RAISE param_not_found;
1080 END IF;
1081 END IF;
1082
1083 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1084 gme_debug.put_line ('validation- after process parameter');
1085 END IF;
1086
1087 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1088 gme_debug.put_line ('validation-batch_id' || x_batch_id);
1089 gme_debug.put_line ('validation-x_batchstep_id' || x_batchstep_id);
1090 gme_debug.put_line ('validation-x_activity_id' || x_activity_id);
1091 gme_debug.put_line ('validation-x_resource_id ' || x_resource_id);
1092 gme_debug.put_line ('validation-x_parameter_id ' || x_parameter_id);
1093 gme_debug.put_line ('validation-x_step_status ' || x_step_status);
1094 END IF;
1095 EXCEPTION
1096 WHEN setup_failure THEN
1097 x_return_status := fnd_api.g_ret_sts_error;
1098 WHEN batch_header_fetch_error OR batch_steps_fetch_error OR input_param_missing THEN
1099 x_return_status := fnd_api.g_ret_sts_error;
1100 WHEN stepactivity_not_found OR resource_not_found OR dup_act_rsrc_found OR dup_param_found OR param_not_found THEN
1101 x_return_status := fnd_api.g_ret_sts_error;
1102 WHEN OTHERS THEN
1103 x_return_status := fnd_api.g_ret_sts_unexp_error;
1104 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1105 END validate_process_param;
1106 END gme_process_parameters_pvt;