1 PACKAGE BODY AHL_FMP_MR_INTERVAL_PVT AS
2 /* $Header: AHLVMITB.pls 120.4 2007/11/07 22:50:23 sikumar ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_FMP_MR_INTERVAL_PVT';
5 G_API_NAME VARCHAR2(30) := 'PROCESS_INTERVAL';
6 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
7
8 G_APPLN_USAGE VARCHAR2(30) := FND_PROFILE.value( 'AHL_APPLN_USAGE' );
9
10
11 g_repetitive_flag VARCHAR2(1) := NULL;
12 g_mr_type_code VARCHAR2(30) := NULL;
13
14 -- Function to get the Record Identifier for Error Messages
15 FUNCTION get_record_identifier
16 (
17 p_interval_rec IN interval_rec_type
18 ) RETURN VARCHAR2
19 IS
20
21 l_record_identifier VARCHAR2(2000) := '';
22
23 BEGIN
24
25 -- For PM Programs
26 IF ( G_APPLN_USAGE = 'PM' AND
27 g_mr_type_code = 'PROGRAM' ) THEN
28
29 IF ( p_interval_rec.earliest_due_value IS NOT NULL AND
30 p_interval_rec.earliest_due_value <> FND_API.G_MISS_NUM ) THEN
31 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.earliest_due_value );
32 END IF;
33
34 l_record_identifier := l_record_identifier || ' - ';
35
36 IF ( p_interval_rec.start_value IS NOT NULL AND
37 p_interval_rec.start_value <> FND_API.G_MISS_NUM ) THEN
38 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.start_value );
39 END IF;
40
41 l_record_identifier := l_record_identifier || ' - ';
42
43 IF ( p_interval_rec.stop_value IS NOT NULL AND
44 p_interval_rec.stop_value <> FND_API.G_MISS_NUM ) THEN
45 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.stop_value );
46 END IF;
47
48 l_record_identifier := l_record_identifier || ' - ';
49
50 -- For One-Time Maintenencce Requirements
51 ELSIF ( g_repetitive_flag = 'N' ) THEN
52 IF ( p_interval_rec.interval_value IS NOT NULL AND
53 p_interval_rec.interval_value <> FND_API.G_MISS_NUM ) THEN
54 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.interval_value );
55 END IF;
56
57 l_record_identifier := l_record_identifier || ' - ';
58
59 -- For All other Maintenance Requirements
60 ELSE
61
62 IF ( p_interval_rec.start_date IS NOT NULL AND
63 p_interval_rec.start_date <> FND_API.G_MISS_DATE ) THEN
64 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.start_date, 'DD-MON_YYYY' );
65 END IF;
66
67 l_record_identifier := l_record_identifier || ' - ';
68
69 IF ( p_interval_rec.stop_date IS NOT NULL AND
70 p_interval_rec.stop_date <> FND_API.G_MISS_DATE ) THEN
71 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.stop_date, 'DD-MON-YYYY' );
72 END IF;
73
74 l_record_identifier := l_record_identifier || ' - ';
75
76 IF ( p_interval_rec.earliest_due_value IS NOT NULL AND
77 p_interval_rec.earliest_due_value <> FND_API.G_MISS_NUM ) THEN
78 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.earliest_due_value );
79 END IF;
80
81 l_record_identifier := l_record_identifier || ' - ';
82
83 IF ( p_interval_rec.start_value IS NOT NULL AND
84 p_interval_rec.start_value <> FND_API.G_MISS_NUM ) THEN
85 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.start_value );
86 END IF;
87
88 l_record_identifier := l_record_identifier || ' - ';
89
90 IF ( p_interval_rec.interval_value IS NOT NULL AND
91 p_interval_rec.interval_value <> FND_API.G_MISS_NUM ) THEN
92 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.interval_value );
93 END IF;
94
95 l_record_identifier := l_record_identifier || ' - ';
96
97 IF ( p_interval_rec.stop_value IS NOT NULL AND
98 p_interval_rec.stop_value <> FND_API.G_MISS_NUM ) THEN
99 l_record_identifier := l_record_identifier || TO_CHAR( p_interval_rec.stop_value );
100 END IF;
101
102 l_record_identifier := l_record_identifier || ' - ';
103
104 END IF;
105
106 -- Common Identifiers
107 IF ( p_interval_rec.counter_name IS NOT NULL AND
108 p_interval_rec.counter_name <> FND_API.G_MISS_CHAR ) THEN
109 l_record_identifier := l_record_identifier || p_interval_rec.counter_name;
110 END IF;
111
112 RETURN l_record_identifier;
113
114 END get_record_identifier;
115
116 -- Procedure to validate the Inputs of the API
117 PROCEDURE validate_api_inputs
118 (
119 p_interval_tbl IN interval_tbl_type,
120 p_mr_header_id IN NUMBER,
121 p_threshold_rec IN threshold_rec_type,
122 x_return_status OUT NOCOPY VARCHAR2,
123 p_super_user IN VARCHAR2
124 )
125 IS
126
127 l_return_status VARCHAR2(1);
128 l_msg_data VARCHAR2(2000);
129 l_mr_status_code VARCHAR2(30);
130 l_mr_type_code VARCHAR2(30);
131 l_pm_install_flag VARCHAR2(1);
132
133 BEGIN
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- Check if a valid value is passed in p_mr_header_id
137 IF ( p_mr_header_id = FND_API.G_MISS_NUM OR
138 p_mr_header_id IS NULL ) THEN
139 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MR_HEADER_ID_NULL' );
140 FND_MSG_PUB.add;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 RETURN;
143 END IF;
144
145 -- Check Profile value
146
147 IF G_APPLN_USAGE IS NULL
148 THEN
149 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
150 FND_MSG_PUB.ADD;
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 RETURN;
153 IF G_DEBUG = 'Y' THEN
154 AHL_DEBUG_PUB.debug('APPLN USAGE CODE IS NULL IN VALIDATE_API_INPUTS' );
155 END IF;
156 END IF;
157
158
159 IF ( G_APPLN_USAGE = 'PM' ) THEN
160 l_pm_install_flag:= 'Y';
161 ELSE
162 l_pm_install_flag:= 'N';
163 END IF;
164
165 --check if mr is terminated and get the mr status.
166 l_mr_status_code :=AHL_FMP_COMMON_PVT.check_mr_status(p_mr_header_id);
167 IF l_mr_status_code IS NULL THEN
168 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_MR' );
169 FND_MSG_PUB.add;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 RETURN;
172 END IF;
173
174 -- Check if the Maintenance Requirement is in Updatable status based on the pm,super user,mrstatus
175 IF ( l_pm_install_flag = 'Y' AND
176 p_super_user ='Y' AND
177 l_mr_status_code = 'COMPLETE') THEN
178 AHL_FMP_COMMON_PVT.validate_mr_pm_status
179 (
180 x_return_status => l_return_status,
181 x_msg_data => l_msg_data,
182 p_mr_header_id => p_mr_header_id
183 );
184 ELSE
185 AHL_FMP_COMMON_PVT.validate_mr_status
186 (
187 x_return_status => l_return_status,
188 x_msg_data => l_msg_data,
189 p_mr_header_id => p_mr_header_id
190 );
191 END IF;
192
193 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
194 FND_MESSAGE.set_name( 'AHL', l_msg_data );
195 FND_MSG_PUB.add;
196 x_return_status := l_return_status;
197 RETURN;
198 END IF;
199
200
201 -- Check if a valid value is passed in p_mr_effectivity_id
202 IF ( p_threshold_rec.mr_effectivity_id = FND_API.G_MISS_NUM OR
203 p_threshold_rec.mr_effectivity_id IS NULL ) THEN
204 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MRE_ID_NULL' );
205 FND_MSG_PUB.add;
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 RETURN;
208 END IF;
209
210 -- Check if the Maintenance Requirement Effectivity exists
211 AHL_FMP_COMMON_PVT.validate_mr_effectivity
212 (
213 x_return_status => l_return_status,
214 x_msg_data => l_msg_data,
215 p_mr_effectivity_id => p_threshold_rec.mr_effectivity_id,
216 p_object_version_number => p_threshold_rec.object_version_number
217 );
218
219 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
220 FND_MESSAGE.set_name( 'AHL', l_msg_data );
221 FND_MSG_PUB.add;
222 x_return_status := l_return_status;
223 RETURN;
224 END IF;
225
226 l_mr_type_code :=AHL_FMP_COMMON_PVT.check_mr_type(p_mr_header_id);
227
228 -- Check if atleast one record is passed in p_interval_tbl or p_threshold_rec is passed
229 IF ( p_interval_tbl.count < 1 AND
230 ( p_threshold_rec.threshold_date IS NULL OR
231 p_threshold_rec.threshold_date = FND_API.G_MISS_DATE ) AND
232 ( p_threshold_rec.program_duration IS NULL OR
233 p_threshold_rec.program_duration = FND_API.G_MISS_NUM ) ) THEN
234 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
235 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
236 FND_MSG_PUB.add;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 RETURN;
239 END IF;
240
241 -- Validate DML Operation
242 FOR i IN 1..p_interval_tbl.count LOOP
243 IF ( p_interval_tbl(i).dml_operation <> 'D' AND
244 p_interval_tbl(i).dml_operation <> 'U' AND
245 p_interval_tbl(i).dml_operation <> 'C' ) THEN
246 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DML_INVALID' );
247 FND_MESSAGE.set_token( 'FIELD', p_interval_tbl(i).dml_operation );
248 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_tbl(i) ) );
249 FND_MSG_PUB.add;
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 RETURN;
252 END IF;
253
254 --BASED on the discussion with sracha on Nov02 2004 to show the error msg.
255 IF ( l_pm_install_flag = 'Y' AND
256 p_interval_tbl(i).dml_operation = 'U' AND
257 p_super_user ='Y' AND
258 l_mr_status_code = 'COMPLETE') THEN
259 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_DML' );
260 FND_MSG_PUB.add;
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 RETURN;
263 END IF;
264
265
266 IF ( l_pm_install_flag = 'Y' AND
267 p_super_user ='Y' AND
268 p_interval_tbl(i).dml_operation = 'D' AND
269 l_mr_status_code = 'COMPLETE' AND
270 l_mr_type_code ='ACTIVITY')THEN
271 AHL_FMP_COMMON_PVT.validate_mr_type_activity
272 (
273 x_return_status => l_return_status,
274 x_msg_data => l_msg_data,
275 p_effectivity_id => p_threshold_rec.MR_EFFECTIVITY_ID,
276 p_eff_obj_version => p_threshold_rec.OBJECT_VERSION_NUMBER
277 );
278 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
279 FND_MESSAGE.set_name( 'AHL', l_msg_data );
280 FND_MSG_PUB.add;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 RETURN;
283 END IF;
284 END IF;
285
286
287 IF ( l_pm_install_flag = 'Y' AND
288 p_super_user ='Y' AND
289 p_interval_tbl(i).dml_operation = 'D' AND
290 l_mr_status_code = 'COMPLETE' AND
291 l_mr_type_code ='PROGRAM') THEN
292 AHL_FMP_COMMON_PVT.validate_mr_type_program
293 (
294 x_return_status => l_return_status,
295 x_msg_data => l_msg_data,
296 p_mr_header_id => p_mr_header_id,
297 p_effectivity_id => p_threshold_rec.MR_EFFECTIVITY_ID,
298 p_eff_obj_version => p_threshold_rec.OBJECT_VERSION_NUMBER
299 );
300 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
301 FND_MESSAGE.set_name( 'AHL', l_msg_data );
302 FND_MSG_PUB.add;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 RETURN;
305 END IF;
306 END IF;
307 END LOOP;
308
309
310 -- Check if a valid value is passed in p_threshold_rec.object_version_number if any other threshold information is passed
311 IF ( ( p_threshold_rec.threshold_date <> FND_API.G_MISS_DATE AND
312 p_threshold_rec.program_duration <> FND_API.G_MISS_NUM AND
313 p_threshold_rec.mr_effectivity_id IS NOT NULL ) AND
314 ( p_threshold_rec.object_version_number IS NULL OR
315 p_threshold_rec.object_version_number = FND_API.G_MISS_NUM ) ) THEN
316 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MRE_OBJ_VERSION_NULL' );
317 FND_MSG_PUB.add;
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 RETURN;
320 END IF;
321
322 END validate_api_inputs;
323
324 -- Procedure to Default NULL / G_MISS Values for LOV attributes
325 PROCEDURE clear_lov_attribute_ids
326 (
327 p_x_interval_rec IN OUT NOCOPY interval_rec_type
328 )
329 IS
330
331 BEGIN
332
333 IF ( p_x_interval_rec.counter_name IS NULL ) THEN
334 p_x_interval_rec.counter_id := NULL;
335 ELSIF ( p_x_interval_rec.counter_name = FND_API.G_MISS_CHAR ) THEN
336 p_x_interval_rec.counter_id := FND_API.G_MISS_NUM;
337 END IF;
338
339 END clear_lov_attribute_ids;
340
341 -- Procedure to perform Value to ID conversion for appropriate attributes
342 PROCEDURE convert_values_to_ids
343 (
344 p_x_interval_rec IN OUT NOCOPY interval_rec_type,
345 p_mr_effectivity_id IN NUMBER,
346 x_return_status OUT NOCOPY VARCHAR2
347 )
348 IS
349
350 l_return_status VARCHAR2(1);
351 l_msg_data VARCHAR2(2000);
352 l_inventory_item_id NUMBER;
353 l_item_number VARCHAR2(40);
354 l_relationship_id NUMBER;
355 l_position_ref_meaning VARCHAR2(80);
356
357 CURSOR get_item_effectivity ( c_mr_effectivity_id NUMBER )
358 IS
359 SELECT DECODE( relationship_id, null,
360 inventory_item_id,
361 position_inventory_item_id ),
362 DECODE( relationship_id, null,
363 item_number,
364 position_item_number ),
365 relationship_id,
366 position_ref_meaning
367 FROM AHL_MR_EFFECTIVITIES_V
368 WHERE mr_effectivity_id = c_mr_effectivity_id;
369
370 BEGIN
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 -- Convert / Validate Counter
374 IF ( ( p_x_interval_rec.counter_id IS NOT NULL AND
375 p_x_interval_rec.counter_id <> FND_API.G_MISS_NUM ) OR
376 ( p_x_interval_rec.counter_name IS NOT NULL AND
377 p_x_interval_rec.counter_name <> FND_API.G_MISS_CHAR ) ) THEN
378
379 OPEN get_item_effectivity( p_mr_effectivity_id );
380
381 FETCH get_item_effectivity INTO
382 l_inventory_item_id,
383 l_item_number,
384 l_relationship_id,
385 l_position_ref_meaning;
386
387 CLOSE get_item_effectivity;
388
389 AHL_FMP_COMMON_PVT.validate_counter_template
390 (
391 x_return_status => l_return_status,
392 x_msg_data => l_msg_data,
393 p_inventory_item_id => l_inventory_item_id,
394 p_relationship_id => l_relationship_id,
395 p_counter_name => p_x_interval_rec.counter_name,
396 p_x_counter_id => p_x_interval_rec.counter_id
397 );
398
399 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
400 FND_MESSAGE.set_name( 'AHL', l_msg_data );
401
402 IF ( l_msg_data = 'AHL_FMP_INVALID_COUNTER' OR
403 l_msg_data = 'AHL_FMP_TOO_MANY_COUNTERS' ) THEN
404 IF ( p_x_interval_rec.counter_name IS NULL OR
405 p_x_interval_rec.counter_name = FND_API.G_MISS_CHAR ) THEN
406 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_interval_rec.counter_id ));
407 ELSE
408 FND_MESSAGE.set_token( 'FIELD', p_x_interval_rec.counter_name );
409 END IF;
410 ELSE
411 IF ( p_x_interval_rec.counter_name IS NULL OR
412 p_x_interval_rec.counter_name = FND_API.G_MISS_CHAR ) THEN
413 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_x_interval_rec.counter_id ) );
414 ELSE
415 FND_MESSAGE.set_token( 'FIELD1', p_x_interval_rec.counter_name );
416 END IF;
417
418 IF ( l_position_ref_meaning IS NOT NULL ) THEN
419 FND_MESSAGE.set_token( 'FIELD2', l_position_ref_meaning );
420 ELSE
421 FND_MESSAGE.set_token( 'FIELD2', l_item_number );
422 END IF;
423 END IF;
424
425 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_interval_rec ) );
426 FND_MSG_PUB.add;
427 END IF;
428
429 END IF;
430
431 END convert_values_to_ids;
432
433 -- Procedure to add Default values for interval attributes
434 PROCEDURE default_attributes
435 (
436 p_x_interval_rec IN OUT NOCOPY interval_rec_type,
437 x_fractional_trunc_flag OUT NOCOPY BOOLEAN
438 )
439 IS
440
441 l_value NUMBER;
442
443 BEGIN
444
445 x_fractional_trunc_flag := FALSE;
446
447 p_x_interval_rec.last_update_date := SYSDATE;
448 p_x_interval_rec.last_updated_by := FND_GLOBAL.user_id;
449 p_x_interval_rec.last_update_login := FND_GLOBAL.login_id;
450
451 IF ( p_x_interval_rec.dml_operation = 'C' ) THEN
452 p_x_interval_rec.object_version_number := 1;
453 p_x_interval_rec.creation_date := SYSDATE;
454 p_x_interval_rec.created_by := FND_GLOBAL.user_id;
455 END IF;
456
457 -- Fix for bug# 3482307.
458 IF (p_x_interval_rec.earliest_due_value IS NOT NULL AND
459 p_x_interval_rec.earliest_due_value <> FND_API.G_MISS_NUM) THEN
460 l_value := p_x_interval_rec.earliest_due_value;
461 p_x_interval_rec.earliest_due_value := trunc(p_x_interval_rec.earliest_due_value);
462 IF (l_value <> p_x_interval_rec.earliest_due_value) THEN
463 x_fractional_trunc_flag := TRUE;
464 END IF;
465 END IF;
466
467 IF (p_x_interval_rec.start_value IS NOT NULL AND
468 p_x_interval_rec.start_value <> FND_API.G_MISS_NUM) THEN
469 l_value := p_x_interval_rec.start_value;
470 p_x_interval_rec.start_value := trunc(p_x_interval_rec.start_value);
471 IF (l_value <> p_x_interval_rec.start_value) THEN
472 x_fractional_trunc_flag := TRUE;
473 END IF;
474 END IF;
475
476 IF (p_x_interval_rec.stop_value IS NOT NULL AND
477 p_x_interval_rec.stop_value <> FND_API.G_MISS_NUM) THEN
478 l_value := p_x_interval_rec.stop_value;
479 p_x_interval_rec.stop_value := trunc(p_x_interval_rec.stop_value);
480 IF (l_value <> p_x_interval_rec.stop_value) THEN
481 x_fractional_trunc_flag := TRUE;
482 END IF;
483 END IF;
484
485 IF (p_x_interval_rec.interval_value IS NOT NULL AND
486 p_x_interval_rec.interval_value <> FND_API.G_MISS_NUM) THEN
487 l_value := p_x_interval_rec.interval_value;
488 p_x_interval_rec.interval_value := trunc(p_x_interval_rec.interval_value);
489 IF (l_value <> p_x_interval_rec.interval_value) THEN
490 x_fractional_trunc_flag := TRUE;
491 END IF;
492 END IF;
493
494 IF (p_x_interval_rec.tolerance_before IS NOT NULL AND
495 p_x_interval_rec.tolerance_before <> FND_API.G_MISS_NUM) THEN
496 l_value := p_x_interval_rec.tolerance_before;
497 p_x_interval_rec.tolerance_before := trunc(p_x_interval_rec.tolerance_before);
498 IF (l_value <> p_x_interval_rec.tolerance_before) THEN
499 x_fractional_trunc_flag := TRUE;
500 END IF;
501 END IF;
502
503 IF (p_x_interval_rec.tolerance_after IS NOT NULL AND
504 p_x_interval_rec.tolerance_after <> FND_API.G_MISS_NUM) THEN
505 l_value := p_x_interval_rec.tolerance_after;
506 p_x_interval_rec.tolerance_after := trunc(p_x_interval_rec.tolerance_after);
507 IF (l_value <> p_x_interval_rec.tolerance_after) THEN
508 x_fractional_trunc_flag := TRUE;
509 END IF;
510 END IF;
511
512 END default_attributes;
513
514 -- Procedure to add Default values for missing attributes (CREATE)
515 PROCEDURE default_missing_attributes
516 (
517 p_x_interval_rec IN OUT NOCOPY interval_rec_type
518 )
519 IS
520
521 BEGIN
522
523 -- Convert G_MISS values to NULL
524 IF ( p_x_interval_rec.earliest_due_value = FND_API.G_MISS_NUM ) THEN
525 p_x_interval_rec.earliest_due_value := null;
526 END IF;
527
528 IF ( p_x_interval_rec.start_value = FND_API.G_MISS_NUM ) THEN
529 p_x_interval_rec.start_value := null;
530 END IF;
531
532 IF ( p_x_interval_rec.stop_value = FND_API.G_MISS_NUM ) THEN
533 p_x_interval_rec.stop_value := null;
534 END IF;
535
536 IF ( p_x_interval_rec.tolerance_before = FND_API.G_MISS_NUM ) THEN
537 p_x_interval_rec.tolerance_before := null;
538 END IF;
539
540 IF ( p_x_interval_rec.tolerance_after = FND_API.G_MISS_NUM ) THEN
541 p_x_interval_rec.tolerance_after := null;
542 END IF;
543
544 IF ( p_x_interval_rec.reset_value = FND_API.G_MISS_NUM ) THEN
545 p_x_interval_rec.reset_value := null;
546 END IF;
547
548 IF ( p_x_interval_rec.start_date = FND_API.G_MISS_DATE ) THEN
549 p_x_interval_rec.start_date := null;
550 END IF;
551
552 IF ( p_x_interval_rec.stop_date = FND_API.G_MISS_DATE ) THEN
553 p_x_interval_rec.stop_date := null;
554 END IF;
555
556 IF ( p_x_interval_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
557 p_x_interval_rec.attribute_category := null;
558 END IF;
559
560 IF ( p_x_interval_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
561 p_x_interval_rec.attribute1 := null;
562 END IF;
563
564 IF ( p_x_interval_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
565 p_x_interval_rec.attribute2 := null;
566 END IF;
567
568 IF ( p_x_interval_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
569 p_x_interval_rec.attribute3 := null;
570 END IF;
571
572 IF ( p_x_interval_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
573 p_x_interval_rec.attribute4 := null;
574 END IF;
575
576 IF ( p_x_interval_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
577 p_x_interval_rec.attribute5 := null;
578 END IF;
579
580 IF ( p_x_interval_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
581 p_x_interval_rec.attribute6 := null;
582 END IF;
583
584 IF ( p_x_interval_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
585 p_x_interval_rec.attribute7 := null;
586 END IF;
587
588 IF ( p_x_interval_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
589 p_x_interval_rec.attribute8 := null;
590 END IF;
591
592 IF ( p_x_interval_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
593 p_x_interval_rec.attribute9 := null;
594 END IF;
595
596 IF ( p_x_interval_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
597 p_x_interval_rec.attribute10 := null;
598 END IF;
599
600 IF ( p_x_interval_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
601 p_x_interval_rec.attribute11 := null;
602 END IF;
603
604 IF ( p_x_interval_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
605 p_x_interval_rec.attribute12 := null;
606 END IF;
607
608 IF ( p_x_interval_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
609 p_x_interval_rec.attribute13 := null;
610 END IF;
611
612 IF ( p_x_interval_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
613 p_x_interval_rec.attribute14 := null;
614 END IF;
615
616 IF ( p_x_interval_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
617 p_x_interval_rec.attribute15 := null;
618 END IF;
619
620 END default_missing_attributes;
621
622 -- Procedure to add Default values for unchanged attributes (UPDATE)
623 PROCEDURE default_unchanged_attributes
624 (
625 p_x_interval_rec IN OUT NOCOPY interval_rec_type
626 )
627 IS
628
629 l_old_interval_rec interval_rec_type;
630
631 CURSOR get_old_rec ( c_mr_interval_id NUMBER )
632 IS
633 SELECT counter_id,
634 interval_value,
635 earliest_due_value,
636 start_value,
637 stop_value,
638 tolerance_before,
639 tolerance_after,
640 reset_value,
641 start_date,
642 stop_date,
643 attribute_category,
644 attribute1,
645 attribute2,
646 attribute3,
647 attribute4,
648 attribute5,
649 attribute6,
650 attribute7,
651 attribute8,
652 attribute9,
653 attribute10,
654 attribute11,
655 attribute12,
656 attribute13,
657 attribute14,
658 attribute15
659 FROM AHL_MR_INTERVALS_APP_V
660 WHERE mr_interval_id = c_mr_interval_id;
661
662 BEGIN
663
664 -- Get the old record from AHL_MR_INTERVALS.
665 OPEN get_old_rec( p_x_interval_rec.mr_interval_id );
666
667 FETCH get_old_rec INTO
668 l_old_interval_rec.counter_id,
669 l_old_interval_rec.interval_value,
670 l_old_interval_rec.earliest_due_value,
671 l_old_interval_rec.start_value,
672 l_old_interval_rec.stop_value,
673 l_old_interval_rec.tolerance_before,
674 l_old_interval_rec.tolerance_after,
675 l_old_interval_rec.reset_value,
676 l_old_interval_rec.start_date,
677 l_old_interval_rec.stop_date,
678 l_old_interval_rec.attribute_category,
679 l_old_interval_rec.attribute1,
680 l_old_interval_rec.attribute2,
681 l_old_interval_rec.attribute3,
682 l_old_interval_rec.attribute4,
683 l_old_interval_rec.attribute5,
684 l_old_interval_rec.attribute6,
685 l_old_interval_rec.attribute7,
686 l_old_interval_rec.attribute8,
687 l_old_interval_rec.attribute9,
688 l_old_interval_rec.attribute10,
689 l_old_interval_rec.attribute11,
690 l_old_interval_rec.attribute12,
691 l_old_interval_rec.attribute13,
692 l_old_interval_rec.attribute14,
693 l_old_interval_rec.attribute15;
694
695 IF get_old_rec%NOTFOUND THEN
696 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_INTERVAL_REC' );
697 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_interval_rec ) );
698 FND_MSG_PUB.add;
699 CLOSE get_old_rec;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 CLOSE get_old_rec;
704
705 -- Convert G_MISS values to NULL and NULL values to Old values
706 IF ( p_x_interval_rec.counter_id IS NULL ) THEN
707 p_x_interval_rec.counter_id := l_old_interval_rec.counter_id;
708 END IF;
709
710 IF ( p_x_interval_rec.interval_value = FND_API.G_MISS_NUM ) THEN
711 p_x_interval_rec.interval_value := null;
712 ELSIF ( p_x_interval_rec.interval_value IS NULL ) THEN
713 p_x_interval_rec.interval_value := l_old_interval_rec.interval_value;
714 END IF;
715
716 IF ( p_x_interval_rec.earliest_due_value = FND_API.G_MISS_NUM ) THEN
717 p_x_interval_rec.earliest_due_value := null;
718 ELSIF ( p_x_interval_rec.earliest_due_value IS NULL ) THEN
719 p_x_interval_rec.earliest_due_value := l_old_interval_rec.earliest_due_value;
720 END IF;
721
722 IF ( p_x_interval_rec.start_value = FND_API.G_MISS_NUM ) THEN
723 p_x_interval_rec.start_value := null;
724 ELSIF ( p_x_interval_rec.start_value IS NULL ) THEN
725 p_x_interval_rec.start_value := l_old_interval_rec.start_value;
726 END IF;
727
728 IF ( p_x_interval_rec.stop_value = FND_API.G_MISS_NUM ) THEN
729 p_x_interval_rec.stop_value := null;
730 ELSIF ( p_x_interval_rec.stop_value IS NULL ) THEN
731 p_x_interval_rec.stop_value := l_old_interval_rec.stop_value;
732 END IF;
733
734 IF ( p_x_interval_rec.tolerance_before = FND_API.G_MISS_NUM ) THEN
735 p_x_interval_rec.tolerance_before := null;
736 ELSIF ( p_x_interval_rec.tolerance_before IS NULL ) THEN
737 p_x_interval_rec.tolerance_before := l_old_interval_rec.tolerance_before;
738 END IF;
739
740 IF ( p_x_interval_rec.tolerance_after = FND_API.G_MISS_NUM ) THEN
741 p_x_interval_rec.tolerance_after := null;
742 ELSIF ( p_x_interval_rec.tolerance_after IS NULL ) THEN
743 p_x_interval_rec.tolerance_after := l_old_interval_rec.tolerance_after;
744 END IF;
745
746 IF ( p_x_interval_rec.reset_value = FND_API.G_MISS_NUM ) THEN
747 p_x_interval_rec.reset_value := null;
748 ELSIF ( p_x_interval_rec.reset_value IS NULL ) THEN
749 p_x_interval_rec.reset_value := l_old_interval_rec.reset_value;
750 END IF;
751
752
753 IF ( p_x_interval_rec.start_date = FND_API.G_MISS_DATE ) THEN
754 p_x_interval_rec.start_date := null;
755 ELSIF ( p_x_interval_rec.start_date IS NULL ) THEN
756 p_x_interval_rec.start_date := l_old_interval_rec.start_date;
757 END IF;
758
759 IF ( p_x_interval_rec.stop_date = FND_API.G_MISS_DATE ) THEN
760 p_x_interval_rec.stop_date := null;
761 ELSIF ( p_x_interval_rec.stop_date IS NULL ) THEN
762 p_x_interval_rec.stop_date := l_old_interval_rec.stop_date;
763 END IF;
764
765 IF ( p_x_interval_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
766 p_x_interval_rec.attribute_category := null;
767 ELSIF ( p_x_interval_rec.attribute_category IS NULL ) THEN
768 p_x_interval_rec.attribute_category := l_old_interval_rec.attribute_category;
769 END IF;
770
771 IF ( p_x_interval_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
772 p_x_interval_rec.attribute1 := null;
773 ELSIF ( p_x_interval_rec.attribute1 IS NULL ) THEN
774 p_x_interval_rec.attribute1 := l_old_interval_rec.attribute1;
775 END IF;
776
777 IF ( p_x_interval_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
778 p_x_interval_rec.attribute2 := null;
779 ELSIF ( p_x_interval_rec.attribute2 IS NULL ) THEN
780 p_x_interval_rec.attribute2 := l_old_interval_rec.attribute2;
781 END IF;
782
783 IF ( p_x_interval_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
784 p_x_interval_rec.attribute3 := null;
785 ELSIF ( p_x_interval_rec.attribute3 IS NULL ) THEN
786 p_x_interval_rec.attribute3 := l_old_interval_rec.attribute3;
787 END IF;
788
789 IF ( p_x_interval_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
790 p_x_interval_rec.attribute4 := null;
791 ELSIF ( p_x_interval_rec.attribute4 IS NULL ) THEN
792 p_x_interval_rec.attribute4 := l_old_interval_rec.attribute4;
793 END IF;
794
795 IF ( p_x_interval_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
796 p_x_interval_rec.attribute5 := null;
797 ELSIF ( p_x_interval_rec.attribute5 IS NULL ) THEN
798 p_x_interval_rec.attribute5 := l_old_interval_rec.attribute5;
799 END IF;
800
801 IF ( p_x_interval_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
802 p_x_interval_rec.attribute6 := null;
803 ELSIF ( p_x_interval_rec.attribute6 IS NULL ) THEN
804 p_x_interval_rec.attribute6 := l_old_interval_rec.attribute6;
805 END IF;
806
807 IF ( p_x_interval_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
808 p_x_interval_rec.attribute7 := null;
809 ELSIF ( p_x_interval_rec.attribute7 IS NULL ) THEN
810 p_x_interval_rec.attribute7 := l_old_interval_rec.attribute7;
811 END IF;
812
813 IF ( p_x_interval_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
814 p_x_interval_rec.attribute8 := null;
815 ELSIF ( p_x_interval_rec.attribute8 IS NULL ) THEN
816 p_x_interval_rec.attribute8 := l_old_interval_rec.attribute8;
817 END IF;
818
819 IF ( p_x_interval_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
820 p_x_interval_rec.attribute9 := null;
821 ELSIF ( p_x_interval_rec.attribute9 IS NULL ) THEN
822 p_x_interval_rec.attribute9 := l_old_interval_rec.attribute9;
823 END IF;
824
825 IF ( p_x_interval_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
826 p_x_interval_rec.attribute10 := null;
827 ELSIF ( p_x_interval_rec.attribute10 IS NULL ) THEN
828 p_x_interval_rec.attribute10 := l_old_interval_rec.attribute10;
829 END IF;
830
831 IF ( p_x_interval_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
832 p_x_interval_rec.attribute11 := null;
833 ELSIF ( p_x_interval_rec.attribute11 IS NULL ) THEN
834 p_x_interval_rec.attribute11 := l_old_interval_rec.attribute11;
835 END IF;
836
837 IF ( p_x_interval_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
838 p_x_interval_rec.attribute12 := null;
839 ELSIF ( p_x_interval_rec.attribute12 IS NULL ) THEN
840 p_x_interval_rec.attribute12 := l_old_interval_rec.attribute12;
841 END IF;
842
843 IF ( p_x_interval_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
844 p_x_interval_rec.attribute13 := null;
845 ELSIF ( p_x_interval_rec.attribute13 IS NULL ) THEN
846 p_x_interval_rec.attribute13 := l_old_interval_rec.attribute13;
847 END IF;
848
849 IF ( p_x_interval_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
850 p_x_interval_rec.attribute14 := null;
851 ELSIF ( p_x_interval_rec.attribute14 IS NULL ) THEN
852 p_x_interval_rec.attribute14 := l_old_interval_rec.attribute14;
853 END IF;
854
855 IF ( p_x_interval_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
856 p_x_interval_rec.attribute15 := null;
857 ELSIF ( p_x_interval_rec.attribute15 IS NULL ) THEN
858 p_x_interval_rec.attribute15 := l_old_interval_rec.attribute15;
859 END IF;
860
861 END default_unchanged_attributes;
862
863 -- Procedure to validate individual interval attributes
864 PROCEDURE validate_attributes
865 (
866 p_interval_rec IN interval_rec_type,
867 x_return_status OUT NOCOPY VARCHAR2
868 )
869 IS
870
871 BEGIN
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 -- Check if the Counter ID does not contain a null value.
875 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
876 IF ( ( p_interval_rec.dml_operation = 'C' AND
877 p_interval_rec.counter_id IS NULL AND
878 p_interval_rec.counter_name IS NULL ) OR
879 ( p_interval_rec.counter_id = FND_API.G_MISS_NUM AND
880 p_interval_rec.counter_name = FND_API.G_MISS_CHAR ) ) THEN
881 FND_MESSAGE.set_name( 'AHL','AHL_FMP_COUNTER_NULL' );
882 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
883 FND_MSG_PUB.add;
884 END IF;
885 END IF;
886
887 -- Check if the Interval Value does not contain an invalid value
888 IF ( p_interval_rec.interval_value IS NOT NULL AND
889 p_interval_rec.interval_value <= 0 ) THEN
890 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL' );
891 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.interval_value ) );
892 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
893 FND_MSG_PUB.add;
894 END IF;
895
896 -- Check if the earliest due Value does not contain an invalid value.
897 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
898 IF ( p_interval_rec.earliest_due_value IS NOT NULL AND
899 p_interval_rec.earliest_due_value <> FND_API.G_MISS_NUM AND
900 p_interval_rec.earliest_due_value < 0 ) THEN
901 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EARL_DUE' );
902 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.earliest_due_value ) );
903 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
904 FND_MSG_PUB.add;
905 END IF;
906 END IF;
907
908 -- Check if the Start Value does not contain an invalid value.
909 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
910 IF ( p_interval_rec.start_value IS NOT NULL AND
911 p_interval_rec.start_value <> FND_API.G_MISS_NUM AND
912 p_interval_rec.start_value < 0 ) THEN
913 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_START' );
914 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.start_value ) );
915 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
916 FND_MSG_PUB.add;
917 END IF;
918 END IF;
919
920 -- Check if the Stop Value does not contain an invalid value.
921 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
922 IF ( p_interval_rec.stop_value IS NOT NULL AND
923 p_interval_rec.stop_value <> FND_API.G_MISS_NUM AND
924 p_interval_rec.stop_value <= 0 ) THEN
925 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_STOP' );
926 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.stop_value ) );
927 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
928 FND_MSG_PUB.add;
929 END IF;
930 END IF;
931
932 -- Check if the Tolerance Before does not contain an invalid value.
933 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
934 IF ( p_interval_rec.tolerance_before IS NOT NULL AND
935 p_interval_rec.tolerance_before <> FND_API.G_MISS_NUM AND
936 p_interval_rec.tolerance_before < 0 ) THEN
937 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOLERANCE_BF' );
938 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.tolerance_before ) );
939 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
940 FND_MSG_PUB.add;
941 END IF;
942 END IF;
943
944 -- Check if the Tolerance After does not contain an invalid value.
945 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
946 IF ( p_interval_rec.tolerance_after IS NOT NULL AND
947 p_interval_rec.tolerance_after <> FND_API.G_MISS_NUM AND
948 p_interval_rec.tolerance_after < 0 ) THEN
949 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOLERANCE_AF' );
950 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.tolerance_after ) );
951 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
952 FND_MSG_PUB.add;
953 END IF;
954 END IF;
955
956 -- Check if the Reset After does not contain an invalid value.
957 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
958 IF ( p_interval_rec.reset_value IS NOT NULL AND
959 p_interval_rec.reset_value <> FND_API.G_MISS_NUM AND
960 p_interval_rec.reset_value < 0 ) THEN
961 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_RESET_VALUE' );
962 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.reset_value ) );
963 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
964 FND_MSG_PUB.add;
965 END IF;
966 END IF;
967
968 -- Check if the mandatory Interval ID column contains a null value.
969 IF ( p_interval_rec.dml_operation <> 'C' ) THEN
970 IF ( p_interval_rec.mr_interval_id IS NULL OR
971 p_interval_rec.mr_interval_id = FND_API.G_MISS_NUM ) THEN
972 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MR_INTERVAL_ID_NULL' );
973 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
974 FND_MSG_PUB.add;
975 END IF;
976 END IF;
977
978 -- Check if the mandatory Object Version Number column contains a null value.
979 IF ( p_interval_rec.dml_operation <> 'C' ) THEN
980 IF ( p_interval_rec.object_version_number IS NULL OR
981 p_interval_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
982 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INT_OBJ_VERSION_NULL' );
983 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
984 FND_MSG_PUB.add;
985 END IF;
986 END IF;
987
988 END validate_attributes;
989
990 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
991 PROCEDURE validate_record
992 (
993 p_interval_rec IN interval_rec_type,
994 p_repetitive_flag IN VARCHAR2,
995 p_appln_code IN VARCHAR2,
996 p_mr_type_code IN VARCHAR2,
997 x_return_status OUT NOCOPY VARCHAR2
998 )
999 IS
1000
1001 l_return_status VARCHAR2(1);
1002 l_msg_data VARCHAR2(2000);
1003 l_initial_reading NUMBER;
1004
1005 BEGIN
1006 x_return_status := FND_API.G_RET_STS_SUCCESS;
1007
1008 -- Check if the appropriate attributes are passed for PM Programs
1009 IF ( p_mr_type_code = 'PROGRAM' AND
1010 ( p_interval_rec.tolerance_before IS NOT NULL OR
1011 p_interval_rec.tolerance_after IS NOT NULL OR
1012 p_interval_rec.reset_value IS NOT NULL OR
1013 p_interval_rec.interval_value IS NOT NULL OR
1014 p_interval_rec.start_date IS NOT NULL OR
1015 p_interval_rec.stop_date IS NOT NULL ) ) THEN
1016 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_INPUTS' );
1017 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1018 FND_MSG_PUB.add;
1019 END IF;
1020
1021 -- Check if the Interval Value is always passed for non-PM Programs
1022 IF ( ( p_mr_type_code IS NULL OR
1023 p_mr_type_code <> 'PROGRAM' ) AND
1024 ( p_interval_rec.dml_operation = 'C' AND
1025 p_interval_rec.interval_value IS NULL ) OR
1026 ( p_interval_rec.interval_value = FND_API.G_MISS_NUM ) ) THEN
1027 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INTERVAL_NULL' );
1028 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1029 FND_MSG_PUB.add;
1030 END IF;
1031
1032 -- Check if Start is passed for PM Programs
1033 IF ( p_mr_type_code = 'PROGRAM' AND
1034 p_interval_rec.start_value IS NULL ) THEN
1035 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_START_NULL' );
1036 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1037 FND_MSG_PUB.add;
1038 RETURN;
1039 END IF;
1040
1041 -- Check if Stop is passed for PM Programs
1042 IF ( p_mr_type_code = 'PROGRAM' AND
1043 p_interval_rec.stop_value IS NULL ) THEN
1044 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_STOP_NULL' );
1045 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1046 FND_MSG_PUB.add;
1047 RETURN;
1048 END IF;
1049
1050 -- Check if Range is not passed for One Time MRs.
1051 IF ( ( p_mr_type_code IS NULL OR
1052 p_mr_type_code <> 'PROGRAM' ) AND
1053 p_repetitive_flag = 'N' AND
1054 ( p_interval_rec.start_value IS NOT NULL OR
1055 p_interval_rec.stop_value IS NOT NULL OR
1056 p_interval_rec.start_date IS NOT NULL OR
1057 p_interval_rec.stop_date IS NOT NULL ) ) THEN
1058 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RANGE_NOTNULL' );
1059 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1060 FND_MSG_PUB.add;
1061 RETURN;
1062 END IF;
1063
1064 -- Check if the Tolerance Before is less than the Interval Value
1065 IF ( p_interval_rec.tolerance_before IS NOT NULL AND
1066 p_interval_rec.tolerance_before >= p_interval_rec.interval_value ) THEN
1067 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOL_BF_INT' );
1068 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1069 FND_MSG_PUB.add;
1070 END IF;
1071
1072 -- Check if the Tolerance After is less than the Interval Value
1073 IF ( p_interval_rec.tolerance_after IS NOT NULL AND
1074 p_interval_rec.tolerance_after >= p_interval_rec.interval_value ) THEN
1075 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOL_AF_INT' );
1076 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1077 FND_MSG_PUB.add;
1078 END IF;
1079
1080 IF ( p_interval_rec.counter_id IS NOT NULL AND
1081 p_interval_rec.reset_value IS NOT NULL AND
1082 p_interval_rec.reset_value >= 0 ) THEN
1083
1084 SELECT NVL( initial_reading, 0 )
1085 INTO l_initial_reading
1086 FROM csi_counter_template_vl --amsriniv
1087 WHERE counter_id = p_interval_rec.counter_id;
1088
1089 -- Check if the Reset Value is less than the initial reading for the counter
1090 IF ( p_interval_rec.reset_value < l_initial_reading ) THEN
1091 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RESET_LESS_INIT_VALUE' );
1092 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( l_initial_reading ) );
1093 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1094 FND_MSG_PUB.add;
1095 END IF;
1096
1097 END IF;
1098
1099 -- Check if the Start Value is passed if Stop Value is passed
1100 IF ( p_interval_rec.start_value IS NULL AND
1101 p_interval_rec.stop_value IS NOT NULL ) THEN
1102 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MISSING_VALUE_RANGE' );
1103 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1104 FND_MSG_PUB.add;
1105 END IF;
1106
1107 -- Check if the Start Value is passed if Stop Value is passed
1108 IF ( p_interval_rec.start_value IS NULL AND
1109 p_interval_rec.start_date IS NULL AND
1110 p_interval_rec.earliest_due_value IS NOT NULL ) THEN
1111 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1112 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1113 FND_MSG_PUB.add;
1114 END IF;
1115
1116 -- Check if the Start Date is passed if Stop Date is passed
1117 IF ( p_interval_rec.start_date IS NULL AND
1118 p_interval_rec.stop_date IS NOT NULL ) THEN
1119 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MISSING_DATE_RANGE' );
1120 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1121 FND_MSG_PUB.add;
1122 END IF;
1123
1124 -- Check if the Stop Value is not greater than Start Value
1125 IF ( p_interval_rec.start_value IS NOT NULL AND
1126 p_interval_rec.stop_value IS NOT NULL AND
1127 p_interval_rec.stop_value < p_interval_rec.start_value ) THEN
1128 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_VALUE_RANGE' );
1129 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1130 FND_MSG_PUB.add;
1131 END IF;
1132
1133 -- Check if the Stop date is not greater than Start date
1134 IF ( p_interval_rec.start_date IS NOT NULL AND
1135 p_interval_rec.stop_date IS NOT NULL AND
1136 p_interval_rec.stop_date < p_interval_rec.start_date ) THEN
1137 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_DATE_RANGE' );
1138 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1139 FND_MSG_PUB.add;
1140 END IF;
1141
1142 -- Check if both Date based and Value based interval ranges are not passed
1143 IF ( ( p_interval_rec.start_date IS NOT NULL OR
1144 p_interval_rec.stop_date IS NOT NULL ) AND
1145 ( p_interval_rec.start_value IS NOT NULL OR
1146 p_interval_rec.stop_value IS NOT NULL ) ) THEN
1147 FND_MESSAGE.set_name( 'AHL','AHL_FMP_BOTH_DATE_VALUE_RANGE' );
1148 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1149 FND_MSG_PUB.add;
1150 END IF;
1151
1152 -- Check if the Interval Value is between Start Value and Stop Value
1153 IF ( p_interval_rec.start_value IS NOT NULL AND
1154 p_interval_rec.stop_value IS NOT NULL AND
1155 p_interval_rec.interval_value >
1156 --( p_interval_rec.stop_value - p_interval_rec.start_value ) ) THEN
1157 -- Fix for bug# 3482307.
1158 ( p_interval_rec.stop_value - p_interval_rec.start_value + 1) ) THEN
1159 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1160 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1161 FND_MSG_PUB.add;
1162 END IF;
1163
1164 -- Check if the earliest due value is less than Start Value
1165 IF ( p_interval_rec.start_value IS NOT NULL AND
1166 p_interval_rec.earliest_due_value > p_interval_rec.start_value ) THEN
1167 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1168 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1169 FND_MSG_PUB.add;
1170 END IF;
1171
1172 END validate_record;
1173
1174 -- Procedure to Perform cross records validation and duplicate checks
1175 PROCEDURE validate_records
1176 (
1177 p_mr_effectivity_id IN NUMBER,
1178 p_repetitive_flag IN VARCHAR2,
1179 p_appln_code IN VARCHAR2,
1180 p_mr_type_code IN VARCHAR2,
1181 x_return_status OUT NOCOPY VARCHAR2
1182 )
1183 IS
1184
1185 l_interval_rec interval_rec_type;
1186 l_prev_counter_id NUMBER := NULL;
1187 l_prev_stop_date DATE := NULL;
1188 l_prev_stop_value NUMBER := NULL;
1189 l_counter_id NUMBER := NULL;
1190 l_counter_name VARCHAR2(30) := NULL;
1191 l_start_date DATE;
1192 l_stop_date DATE;
1193 l_start_value NUMBER;
1194 l_stop_value NUMBER;
1195 l_earliest_due_value NUMBER;
1196
1197 CURSOR get_pm_pgm_dup_rec( c_mr_effectivity_id NUMBER )
1198 IS
1199 SELECT counter_id,
1200 counter_name
1201 FROM AHL_MR_INTERVALS_V
1202 WHERE mr_effectivity_id = c_mr_effectivity_id
1203 GROUP BY counter_id,
1204 counter_name
1205 HAVING count(*) > 1;
1206
1207 CURSOR get_dup_rec( c_mr_effectivity_id NUMBER )
1208 IS
1209 SELECT counter_id,
1210 counter_name,
1211 start_value,
1212 stop_value,
1213 start_date,
1214 stop_date
1215 FROM AHL_MR_INTERVALS_V
1216 WHERE mr_effectivity_id = c_mr_effectivity_id
1217 GROUP BY counter_id,
1218 counter_name,
1219 start_value,
1220 stop_value,
1221 start_date,
1222 stop_date
1223 HAVING count(*) > 1;
1224
1225 CURSOR get_recs_for_date_range( c_mr_effectivity_id NUMBER )
1226 IS
1227 SELECT counter_id,
1228 counter_name,
1229 start_date,
1230 stop_date
1231 FROM AHL_MR_INTERVALS_V
1232 WHERE mr_effectivity_id = c_mr_effectivity_id
1233 ORDER BY counter_id,
1234 start_date,
1235 stop_date;
1236
1237 CURSOR get_recs_for_value_range( c_mr_effectivity_id NUMBER )
1238 IS
1239 SELECT counter_id,
1240 counter_name,
1241 earliest_due_value,
1242 start_value,
1243 stop_value
1244 FROM AHL_MR_INTERVALS_V
1245 WHERE mr_effectivity_id = c_mr_effectivity_id
1246 ORDER BY counter_id,
1247 start_value,
1248 stop_value;
1249
1250 CURSOR check_unique_reset_value( c_mr_effectivity_id NUMBER )
1251 IS
1252 SELECT counter_name
1253 FROM AHL_MR_INTERVALS_V
1254 WHERE mr_effectivity_id = c_mr_effectivity_id
1255 AND reset_value IS NOT NULL
1256 GROUP BY counter_name
1257 HAVING count(*) > 1;
1258
1259 BEGIN
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261
1262 -- Check whether any duplicate Interval record (based on Counter) exist
1263 -- For Preventive Maintenance Programs
1264
1265 IF ( p_appln_code = 'PM' AND
1266 p_mr_type_code = 'PROGRAM' ) THEN
1267
1268 OPEN get_pm_pgm_dup_rec( p_mr_effectivity_id );
1269
1270 LOOP
1271 FETCH get_pm_pgm_dup_rec INTO
1272 l_interval_rec.counter_id,
1273 l_interval_rec.counter_name;
1274
1275 EXIT WHEN get_pm_pgm_dup_rec%NOTFOUND;
1276
1277 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_PGM_DUP_INT_REC' );
1278 FND_MESSAGE.set_token( 'FIELD', l_interval_rec.counter_name );
1279 FND_MSG_PUB.add;
1280 END LOOP;
1281
1282 IF ( get_pm_pgm_dup_rec%ROWCOUNT > 0 ) THEN
1283 x_return_status := FND_API.G_RET_STS_ERROR;
1284 END IF;
1285
1286 CLOSE get_pm_pgm_dup_rec;
1287 RETURN;
1288
1289 END IF;
1290
1291 -- Check whether any duplicate Interval record (based on Counter and Range) exist
1292 OPEN get_dup_rec( p_mr_effectivity_id );
1293
1294 LOOP
1295 FETCH get_dup_rec INTO
1296 l_interval_rec.counter_id,
1297 l_interval_rec.counter_name,
1298 l_interval_rec.start_value,
1299 l_interval_rec.stop_value,
1300 l_interval_rec.start_date,
1301 l_interval_rec.stop_date;
1302
1303 EXIT WHEN get_dup_rec%NOTFOUND;
1304
1305 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUPLICATE_INT_REC' );
1306 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1307 FND_MSG_PUB.add;
1308 END LOOP;
1309
1310 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1311 CLOSE get_dup_rec;
1312 x_return_status := FND_API.G_RET_STS_ERROR;
1313 RETURN;
1314 END IF;
1315
1316 CLOSE get_dup_rec;
1317
1318 IF ( p_repetitive_flag = 'N' ) THEN
1319 RETURN;
1320 END IF;
1321
1322 OPEN get_recs_for_date_range( p_mr_effectivity_id );
1323
1324 LOOP
1325 FETCH get_recs_for_date_range INTO
1326 l_interval_rec.counter_id,
1327 l_interval_rec.counter_name,
1328 l_interval_rec.start_date,
1329 l_interval_rec.stop_date;
1330
1331 EXIT WHEN get_recs_for_date_range%NOTFOUND;
1332
1333 IF ( l_prev_counter_id IS NOT NULL AND
1334 l_prev_stop_date IS NOT NULL AND
1335 l_interval_rec.counter_id = l_prev_counter_id AND
1336 --l_interval_rec.start_date < l_prev_stop_date ) THEN
1337 -- Fix for bug# 3482307.
1338 l_interval_rec.start_date <= l_prev_stop_date ) THEN
1339 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_DATE_RANGE' );
1340 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1341 FND_MSG_PUB.add;
1342 CLOSE get_recs_for_date_range;
1343 x_return_status := FND_API.G_RET_STS_ERROR;
1344 RETURN;
1345 END IF;
1346
1347 l_prev_counter_id := l_interval_rec.counter_id;
1348 l_prev_stop_date := l_interval_rec.stop_date;
1349
1350 END LOOP;
1351
1352 CLOSE get_recs_for_date_range;
1353 l_prev_counter_id := null;
1354
1355 OPEN get_recs_for_value_range( p_mr_effectivity_id );
1356
1357 LOOP
1358 FETCH get_recs_for_value_range INTO
1359 l_interval_rec.counter_id,
1360 l_interval_rec.counter_name,
1361 l_interval_rec.earliest_due_value,
1362 l_interval_rec.start_value,
1363 l_interval_rec.stop_value;
1364
1365 EXIT WHEN get_recs_for_value_range%NOTFOUND;
1366
1367 IF ( l_prev_counter_id IS NOT NULL AND
1368 l_prev_stop_value IS NOT NULL AND
1369 l_interval_rec.counter_id = l_prev_counter_id AND
1370 --l_interval_rec.start_value < l_prev_stop_value ) THEN
1371 -- Fix for bug# 3482307.
1372 l_interval_rec.start_value <= l_prev_stop_value ) THEN
1373 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_VALUE_RANGE' );
1374 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1375 FND_MSG_PUB.add;
1376 CLOSE get_recs_for_value_range;
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378 RETURN;
1379 END IF;
1380
1381 IF ( l_prev_counter_id IS NOT NULL AND
1382 l_prev_stop_value IS NOT NULL AND
1383 l_interval_rec.counter_id = l_prev_counter_id AND
1384 l_interval_rec.earliest_due_value <= l_prev_stop_value ) THEN
1385 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_VALUE_RANGE' );
1386 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1387 FND_MSG_PUB.add;
1388 CLOSE get_recs_for_value_range;
1389 x_return_status := FND_API.G_RET_STS_ERROR;
1390 RETURN;
1391 END IF;
1392
1393 l_prev_counter_id := l_interval_rec.counter_id;
1394 l_prev_stop_value := l_interval_rec.stop_value;
1395
1396 END LOOP;
1397
1398 CLOSE get_recs_for_value_range;
1399
1400 OPEN check_unique_reset_value( p_mr_effectivity_id );
1401 LOOP
1402 FETCH check_unique_reset_value
1403 INTO l_counter_name;
1404
1405 EXIT WHEN check_unique_reset_value%NOTFOUND;
1406
1407 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RESET_VALUE_NONUNIQUE' );
1408 FND_MESSAGE.set_token( 'FIELD', l_counter_name );
1409 FND_MSG_PUB.add;
1410 END LOOP;
1411
1412 IF ( check_unique_reset_value%ROWCOUNT > 0 ) THEN
1413 CLOSE check_unique_reset_value;
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415 RETURN;
1416 END IF;
1417
1418 CLOSE check_unique_reset_value;
1419
1420 END validate_records;
1421
1422 -- Procedure to get the Repetitive Flag and Type for a MR
1423 PROCEDURE get_mr_header_details
1424 (
1425 p_mr_header_id IN NUMBER,
1426 x_repetitive_flag OUT NOCOPY VARCHAR2,
1427 x_mr_type_code OUT NOCOPY VARCHAR2
1428 )
1429 IS
1430
1431 CURSOR get_mr_details( c_mr_header_id NUMBER )
1432 IS
1433 SELECT repetitive_flag,
1434 type_code
1435 FROM AHL_MR_HEADERS_APP_V
1436 WHERE mr_header_id = c_mr_header_id;
1437
1438 BEGIN
1439
1440 -- Get the Repetitive Flag and Type of the MR.
1441 OPEN get_mr_details( p_mr_header_id );
1442
1443 FETCH get_mr_details INTO
1444 x_repetitive_flag,
1445 x_mr_type_code;
1446
1447 CLOSE get_mr_details;
1448
1449 END get_mr_header_details;
1450
1451 -- Procedure to default the old value of the Threshold Date
1452 PROCEDURE default_threshold_attributes
1453 (
1454 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type
1455 )
1456 IS
1457
1458 CURSOR get_old_rec( c_mr_effectivity_id NUMBER )
1459 IS
1460 SELECT threshold_date,
1461 program_duration,
1462 program_duration_uom_code
1463 FROM AHL_MR_EFFECTIVITIES_APP_V
1464 WHERE mr_effectivity_id = c_mr_effectivity_id;
1465
1466 l_old_rec threshold_rec_type;
1467
1468 BEGIN
1469
1470 OPEN get_old_rec( p_x_threshold_rec.mr_effectivity_id );
1471
1472 FETCH get_old_rec INTO
1473 l_old_rec.threshold_date,
1474 l_old_rec.program_duration,
1475 l_old_rec.program_duration_uom_code;
1476
1477 CLOSE get_old_rec;
1478
1479 IF ( p_x_threshold_rec.threshold_date = FND_API.G_MISS_DATE ) THEN
1480 p_x_threshold_rec.threshold_date := null;
1481 ELSIF ( p_x_threshold_rec.threshold_date IS NULL ) THEN
1482 p_x_threshold_rec.threshold_date := l_old_rec.threshold_date;
1483 END IF;
1484
1485 IF ( p_x_threshold_rec.program_duration = FND_API.G_MISS_NUM ) THEN
1486 p_x_threshold_rec.program_duration := null;
1487 ELSIF ( p_x_threshold_rec.program_duration IS NULL ) THEN
1488 p_x_threshold_rec.program_duration := l_old_rec.program_duration;
1489 END IF;
1490
1491 IF ( p_x_threshold_rec.program_duration_uom_code = FND_API.G_MISS_CHAR ) THEN
1492 p_x_threshold_rec.program_duration_uom_code := null;
1493 ELSIF ( p_x_threshold_rec.program_duration_uom_code IS NULL ) THEN
1494 p_x_threshold_rec.program_duration_uom_code := l_old_rec.program_duration_uom_code;
1495 END IF;
1496
1497 END default_threshold_attributes;
1498
1499 -- Procedure to validate and update the Threshold information
1500 PROCEDURE update_threshold
1501 (
1502 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type,
1503 p_repetitive_flag IN VARCHAR2,
1504 p_appln_code IN VARCHAR2,
1505 p_mr_type_code IN VARCHAR2,
1506 x_return_status OUT NOCOPY VARCHAR2,
1507 p_super_user IN VARCHAR2,
1508 p_mr_header_id IN NUMBER
1509 )
1510 IS
1511
1512 l_return_status VARCHAR2(1);
1513 l_msg_data VARCHAR2(2000);
1514 l_old_threshold_rec threshold_rec_type;
1515 l_mr_status_code VARCHAR2(30);
1516 l_pm_install_flag VARCHAR2(1);
1517
1518 BEGIN
1519 x_return_status := FND_API.G_RET_STS_SUCCESS;
1520
1521 -- Default unchanged value of Threshold attributes
1522 default_threshold_attributes
1523 (
1524 p_x_threshold_rec -- IN OUT
1525 );
1526
1527 -- Check if the Threshold date is not set for repeating MRs.
1528 IF ( p_x_threshold_rec.threshold_date IS NOT NULL AND
1529 p_repetitive_flag = 'Y' ) THEN
1530 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_THRESHOLD' );
1531 FND_MSG_PUB.add;
1532 x_return_status := FND_API.G_RET_STS_ERROR;
1533 RETURN;
1534 END IF;
1535
1536 -- Check if the Threshold date is not set for PM Programs
1537 IF ( p_x_threshold_rec.threshold_date IS NOT NULL AND
1538 p_appln_code= 'PM' AND
1539 p_mr_type_code = 'PROGRAM' ) THEN
1540 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_THRESHOLD' );
1541 FND_MSG_PUB.add;
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 RETURN;
1544 END IF;
1545
1546 -- Check if the Program Duration is not specified for PM Activities.
1547 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1548 p_appln_code = 'PM' AND
1549 p_mr_type_code = 'ACTIVITY' ) THEN
1550 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_ACT_PGM_DURATION' );
1551 FND_MSG_PUB.add;
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 RETURN;
1554 END IF;
1555
1556 -- Check if the Program Duration is a positive number.
1557 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1558 p_x_threshold_rec.program_duration <= 0 ) THEN
1559 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_DUR_INVALID' );
1560 FND_MSG_PUB.add;
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 RETURN;
1563 END IF;
1564
1565 -- Check if the Program Duration is specified then the UOM should be specified.
1566 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1567 p_x_threshold_rec.program_duration_uom_code IS NULL ) THEN
1568 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_DUR_UOM_NULL' );
1569 FND_MSG_PUB.add;
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 RETURN;
1572 END IF;
1573
1574 -- Check if the Program Duration UOM is a valid value.
1575 IF ( p_x_threshold_rec.program_duration_uom_code IS NOT NULL ) THEN
1576
1577 AHL_FMP_COMMON_PVT.validate_lookup
1578 (
1579 x_return_status => l_return_status,
1580 x_msg_data => l_msg_data,
1581 p_lookup_type => 'AHL_UMP_TIME_UOM',
1582 p_lookup_meaning => NULL,
1583 p_x_lookup_code => p_x_threshold_rec.program_duration_uom_code
1584 );
1585
1586 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1587 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_INV_PGM_DUR_UOM' );
1588 FND_MSG_PUB.add;
1589 x_return_status := FND_API.G_RET_STS_ERROR;
1590 RETURN;
1591 END IF;
1592 END IF;
1593
1594 -- Check Profile value
1595
1596 IF G_APPLN_USAGE IS NULL
1597 THEN
1598 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
1599 FND_MSG_PUB.ADD;
1600 x_return_status := FND_API.G_RET_STS_ERROR;
1601 IF G_DEBUG = 'Y' THEN
1602 AHL_DEBUG_PUB.debug('APPLN USAGE CODE IS NULL IN VALIDATE_API_INPUTS' );
1603 END IF;
1604 END IF;
1605
1606
1607 IF ( G_APPLN_USAGE = 'PM' ) THEN
1608 l_pm_install_flag:= 'Y';
1609 ELSE
1610 l_pm_install_flag:= 'N';
1611 END IF;
1612
1613 -- Update effectivity only if changed attributes.
1614 IF ((p_x_threshold_rec.program_duration IS NULL AND
1615 l_old_threshold_rec.program_duration IS NOT NULL) OR
1616 (p_x_threshold_rec.program_duration IS NOT NULL AND
1617 l_old_threshold_rec.program_duration IS NOT NULL AND
1618 l_old_threshold_rec.program_duration <> p_x_threshold_rec.program_duration) OR
1619 (p_x_threshold_rec.program_duration IS NOT NULL AND
1620 l_old_threshold_rec.program_duration IS NULL) OR
1621
1622 (p_x_threshold_rec.program_duration_uom_code IS NULL AND
1623 l_old_threshold_rec.program_duration_uom_code IS NOT NULL) OR
1624 (p_x_threshold_rec.program_duration_uom_code IS NOT NULL AND
1625 l_old_threshold_rec.program_duration_uom_code IS NOT NULL AND
1626 l_old_threshold_rec.program_duration_uom_code <> p_x_threshold_rec.program_duration_uom_code) OR
1627 (p_x_threshold_rec.program_duration_uom_code IS NOT NULL AND
1628 l_old_threshold_rec.program_duration_uom_code IS NULL) OR
1629
1630 (p_x_threshold_rec.threshold_date IS NULL AND
1631 l_old_threshold_rec.threshold_date IS NOT NULL) OR
1632 (p_x_threshold_rec.threshold_date IS NOT NULL AND
1633 l_old_threshold_rec.threshold_date IS NOT NULL AND
1634 l_old_threshold_rec.threshold_date <> p_x_threshold_rec.threshold_date) OR
1635 (p_x_threshold_rec.threshold_date IS NOT NULL AND
1636 l_old_threshold_rec.threshold_date IS NULL) ) THEN
1637
1638 l_mr_status_code :=AHL_FMP_COMMON_PVT.check_mr_status(p_mr_header_id);
1639 IF ( l_pm_install_flag = 'Y' AND
1640 p_super_user = 'Y' AND
1641 l_mr_status_code = 'COMPLETE' AND
1642 p_mr_type_code ='PROGRAM' ) THEN
1643 AHL_FMP_COMMON_PVT.validate_mr_type_program
1644 (
1645 x_return_status => l_return_status,
1646 x_msg_data => l_msg_data,
1647 p_mr_header_id => p_mr_header_id,
1648 p_effectivity_id => p_x_threshold_rec.MR_EFFECTIVITY_ID,
1649 p_eff_obj_version => p_x_threshold_rec.OBJECT_VERSION_NUMBER
1650 );
1651 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1652 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PROGRAM_DURATION' );
1653 FND_MSG_PUB.add;
1654 x_return_status := FND_API.G_RET_STS_ERROR;
1655 RETURN;
1656 END IF;
1657 END IF;
1658
1659 IF ( l_pm_install_flag = 'Y' AND
1660 p_super_user = 'Y' AND
1661 l_mr_status_code = 'COMPLETE' AND
1662 p_mr_type_code ='ACTIVITY' ) THEN
1663 AHL_FMP_COMMON_PVT.validate_mr_type_activity
1664 (
1665 x_return_status => l_return_status,
1666 x_msg_data => l_msg_data,
1667 p_effectivity_id => p_x_threshold_rec.MR_EFFECTIVITY_ID,
1668 p_eff_obj_version => p_x_threshold_rec.OBJECT_VERSION_NUMBER
1669 );
1670 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1671 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PROGRAM_DURATION' );
1672 FND_MSG_PUB.add;
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 RETURN;
1675 END IF;
1676 END IF;
1677
1678
1679 -- Update the record
1680 UPDATE AHL_MR_EFFECTIVITIES SET
1681 object_version_number = object_version_number + 1,
1682 threshold_date = p_x_threshold_rec.threshold_date,
1683 program_duration = p_x_threshold_rec.program_duration,
1684 program_duration_uom_code = p_x_threshold_rec.program_duration_uom_code,
1685 last_update_date = SYSDATE,
1686 last_updated_by = FND_GLOBAL.user_id,
1687 last_update_login = FND_GLOBAL.login_id
1688 WHERE mr_effectivity_id = p_x_threshold_rec.mr_effectivity_id
1689 AND object_version_number = p_x_threshold_rec.object_version_number;
1690
1691 -- If the record does not exist, then, abort API.
1692 IF ( SQL%ROWCOUNT = 0 ) THEN
1693 FND_MESSAGE.set_name('AHL','AHL_FMP_EFF_RECORD_CHANGED');
1694 FND_MSG_PUB.add;
1695 x_return_status := FND_API.G_RET_STS_ERROR;
1696 RETURN;
1697 END IF;
1698
1699 END IF;
1700
1701 END update_threshold;
1702
1703 PROCEDURE process_interval
1704 (
1705 p_api_version IN NUMBER := '1.0',
1706 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1707 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1708 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1709 p_default IN VARCHAR2 := FND_API.G_FALSE,
1710 p_module_type IN VARCHAR2 := NULL,
1711 x_return_status OUT NOCOPY VARCHAR2,
1712 x_msg_count OUT NOCOPY NUMBER,
1713 x_msg_data OUT NOCOPY VARCHAR2,
1714 p_x_interval_tbl IN OUT NOCOPY interval_tbl_type,
1715 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type,
1716 p_mr_header_id IN NUMBER,
1717 p_super_user IN VARCHAR2
1718 )
1719 IS
1720 l_api_version CONSTANT NUMBER := 1.0;
1721 l_return_status VARCHAR2(1);
1722 l_msg_count NUMBER;
1723 l_mr_interval_id NUMBER;
1724
1725 l_fractional_trunc_flag BOOLEAN := FALSE;
1726
1727 BEGIN
1728 -- Initialize API return status to success
1729 x_return_status := FND_API.G_RET_STS_SUCCESS;
1730
1731 -- Standard Start of API savepoint
1732 SAVEPOINT process_interval_PVT;
1733
1734 -- Standard call to check for call compatibility.
1735 IF NOT FND_API.compatible_api_call
1736 (
1737 l_api_version,
1738 p_api_version,
1739 G_API_NAME,
1740 G_PKG_NAME
1741 )
1742 THEN
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END IF;
1745
1746 -- Initialize message list if p_init_msg_list is set to TRUE.
1747 IF FND_API.to_boolean( p_init_msg_list ) THEN
1748 FND_MSG_PUB.initialize;
1749 END IF;
1750
1751 -- Enable Debug (optional)
1752 IF ( G_DEBUG = 'Y' ) THEN
1753 AHL_DEBUG_PUB.enable_debug;
1754 END IF;
1755
1756 IF G_DEBUG = 'Y' THEN
1757 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
1758 END IF;
1759
1760 -- Get the PM Installation Flag
1761
1762 IF G_DEBUG = 'Y' THEN
1763 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Application Installed : ' || G_APPLN_USAGE );
1764 END IF;
1765
1766 -- Validate all the inputs of the API
1767 validate_api_inputs
1768 (
1769 p_x_interval_tbl, -- IN
1770 p_mr_header_id, -- IN
1771 p_x_threshold_rec, -- IN
1772 l_return_status, -- OUT
1773 p_super_user
1774 );
1775
1776 -- If any severe error occurs, then, abort API.
1777 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1778 RAISE FND_API.G_EXC_ERROR;
1779 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1781 END IF;
1782
1783 -- Get the Details for the given MR
1784 get_mr_header_details
1785 (
1786 p_mr_header_id, -- IN
1787 g_repetitive_flag, -- OUT
1788 g_mr_type_code -- OUT
1789 );
1790
1791 -- Validate and Update the Threshold information
1792 update_threshold
1793 (
1794 p_x_threshold_rec, -- IN
1795 g_repetitive_flag, -- IN
1796 G_APPLN_USAGE, -- IN
1797 g_mr_type_code,
1798 l_return_status, -- OUT
1799 p_super_user,
1800 p_mr_header_id
1801
1802 );
1803
1804 -- If any severe error occurs, then, abort API.
1805 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1806 RAISE FND_API.G_EXC_ERROR;
1807 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 END IF;
1810
1811 -- If the module type is JSP, then default values for ID columns of LOV attributes
1812 IF ( p_module_type = 'JSP' ) THEN
1813 FOR i IN 1..p_x_interval_tbl.count LOOP
1814 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1815 clear_lov_attribute_ids
1816 (
1817 p_x_interval_tbl(i) -- IN OUT Record with Values and Ids
1818 );
1819 END IF;
1820 END LOOP;
1821 END IF;
1822
1823 -- Convert Values into Ids.
1824 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1825 FOR i IN 1..p_x_interval_tbl.count LOOP
1826 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1827 convert_values_to_ids
1828 (
1829 p_x_interval_tbl(i), -- IN OUT Record with Values and Ids
1830 p_x_threshold_rec.mr_effectivity_id, -- IN
1831 l_return_status -- OUT
1832 );
1833
1834 -- If any severe error occurs, then, abort API.
1835 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1836 RAISE FND_API.G_EXC_ERROR;
1837 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 END IF;
1840 END IF;
1841 END LOOP;
1842 END IF;
1843
1844 IF G_DEBUG = 'Y' THEN
1845 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1846 END IF;
1847
1848 -- Default interval attributes.
1849 FOR i IN 1..p_x_interval_tbl.count LOOP
1850 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1851 default_attributes
1852 (
1853 p_x_interval_tbl(i), -- IN OUT
1854 l_fractional_trunc_flag -- OUT
1855 );
1856 END IF;
1857 END LOOP;
1858
1859 IF G_DEBUG = 'Y' THEN
1860 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1861 END IF;
1862
1863 -- Validate all attributes (Item level validation)
1864 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1865 FOR i IN 1..p_x_interval_tbl.count LOOP
1866 validate_attributes
1867 (
1868 p_x_interval_tbl(i), -- IN
1869 l_return_status -- OUT
1870 );
1871
1872 -- If any severe error occurs, then, abort API.
1873 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1874 RAISE FND_API.G_EXC_ERROR;
1875 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1877 END IF;
1878 END LOOP;
1879 END IF;
1880
1881 IF G_DEBUG = 'Y' THEN
1882 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1883 END IF;
1884
1885 -- Default missing and unchanged attributes.
1886 FOR i IN 1..p_x_interval_tbl.count LOOP
1887 IF ( p_x_interval_tbl(i).dml_operation = 'U' ) THEN
1888 default_unchanged_attributes
1889 (
1890 p_x_interval_tbl(i) -- IN OUT
1891 );
1892 ELSIF ( p_x_interval_tbl(i).dml_operation = 'C' ) THEN
1893 default_missing_attributes
1894 (
1895 p_x_interval_tbl(i) -- IN OUT
1896 );
1897 END IF;
1898 END LOOP;
1899
1900 IF G_DEBUG = 'Y' THEN
1901 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1902 END IF;
1903
1904 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1905 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1906 FOR i IN 1..p_x_interval_tbl.count LOOP
1907 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1908 validate_record
1909 (
1910 p_x_interval_tbl(i), -- IN
1911 g_repetitive_flag, -- IN
1912 G_APPLN_USAGE, -- IN
1913 g_mr_type_code, -- IN
1914 l_return_status -- OUT
1915 );
1916
1917 -- If any severe error occurs, then, abort API.
1918 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1919 RAISE FND_API.G_EXC_ERROR;
1920 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1922 END IF;
1923 END IF;
1924 END LOOP;
1925 END IF;
1926
1927 IF G_DEBUG = 'Y' THEN
1928 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
1929 END IF;
1930
1931 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1932 l_msg_count := FND_MSG_PUB.count_msg;
1933 IF l_msg_count > 0 THEN
1934 x_msg_count := l_msg_count;
1935 RAISE FND_API.G_EXC_ERROR;
1936 END IF;
1937
1938 -- Perform the DML statement directly.
1939 FOR i IN 1..p_x_interval_tbl.count LOOP
1940 IF ( p_x_interval_tbl(i).dml_operation = 'C' ) THEN
1941
1942 -- Insert the record
1943 INSERT INTO AHL_MR_INTERVALS
1944 (
1945 MR_INTERVAL_ID,
1946 OBJECT_VERSION_NUMBER,
1947 MR_EFFECTIVITY_ID,
1948 COUNTER_ID,
1949 INTERVAL_VALUE,
1950 EARLIEST_DUE_VALUE,
1951 START_VALUE,
1952 STOP_VALUE,
1953 START_DATE,
1954 STOP_DATE,
1955 TOLERANCE_BEFORE,
1956 TOLERANCE_AFTER,
1957 RESET_VALUE,
1958 ATTRIBUTE_CATEGORY,
1959 ATTRIBUTE1,
1960 ATTRIBUTE2,
1961 ATTRIBUTE3,
1962 ATTRIBUTE4,
1963 ATTRIBUTE5,
1964 ATTRIBUTE6,
1965 ATTRIBUTE7,
1966 ATTRIBUTE8,
1967 ATTRIBUTE9,
1968 ATTRIBUTE10,
1969 ATTRIBUTE11,
1970 ATTRIBUTE12,
1971 ATTRIBUTE13,
1972 ATTRIBUTE14,
1973 ATTRIBUTE15,
1974 LAST_UPDATE_DATE,
1975 LAST_UPDATED_BY,
1976 CREATION_DATE,
1977 CREATED_BY,
1978 LAST_UPDATE_LOGIN
1979 ) VALUES
1980 (
1981 AHL_MR_INTERVALS_S.NEXTVAL,
1982 p_x_interval_tbl(i).object_version_number,
1983 p_x_threshold_rec.mr_effectivity_id,
1984 p_x_interval_tbl(i).counter_id,
1985 p_x_interval_tbl(i).interval_value,
1986 p_x_interval_tbl(i).earliest_due_value,
1987 p_x_interval_tbl(i).start_value,
1988 p_x_interval_tbl(i).stop_value,
1989 p_x_interval_tbl(i).start_date,
1990 p_x_interval_tbl(i).stop_date,
1991 p_x_interval_tbl(i).tolerance_before,
1992 p_x_interval_tbl(i).tolerance_after,
1993 p_x_interval_tbl(i).reset_value,
1994 p_x_interval_tbl(i).attribute_category,
1995 p_x_interval_tbl(i).attribute1,
1996 p_x_interval_tbl(i).attribute2,
1997 p_x_interval_tbl(i).attribute3,
1998 p_x_interval_tbl(i).attribute4,
1999 p_x_interval_tbl(i).attribute5,
2000 p_x_interval_tbl(i).attribute6,
2001 p_x_interval_tbl(i).attribute7,
2002 p_x_interval_tbl(i).attribute8,
2003 p_x_interval_tbl(i).attribute9,
2004 p_x_interval_tbl(i).attribute10,
2005 p_x_interval_tbl(i).attribute11,
2006 p_x_interval_tbl(i).attribute12,
2007 p_x_interval_tbl(i).attribute13,
2008 p_x_interval_tbl(i).attribute14,
2009 p_x_interval_tbl(i).attribute15,
2010 p_x_interval_tbl(i).last_update_date,
2011 p_x_interval_tbl(i).last_updated_by,
2012 p_x_interval_tbl(i).creation_date,
2013 p_x_interval_tbl(i).created_by,
2014 p_x_interval_tbl(i).last_update_login
2015 ) RETURNING mr_interval_id INTO l_mr_interval_id;
2016
2017 -- Set OUT values
2018 p_x_interval_tbl(i).mr_interval_id := l_mr_interval_id;
2019
2020 ELSIF ( p_x_interval_tbl(i).dml_operation = 'U' ) THEN
2021
2022 -- Update the record
2023 UPDATE AHL_MR_INTERVALS SET
2024 object_version_number = object_version_number + 1,
2025 counter_id = p_x_interval_tbl(i).counter_id,
2026 interval_value = p_x_interval_tbl(i).interval_value,
2027 earliest_due_value = p_x_interval_tbl(i).earliest_due_value,
2028 start_value = p_x_interval_tbl(i).start_value,
2029 stop_value = p_x_interval_tbl(i).stop_value,
2030 start_date = p_x_interval_tbl(i).start_date,
2031 stop_date = p_x_interval_tbl(i).stop_date,
2032 tolerance_before = p_x_interval_tbl(i).tolerance_before,
2033 tolerance_after = p_x_interval_tbl(i).tolerance_after,
2034 reset_value = p_x_interval_tbl(i).reset_value,
2035 attribute_category = p_x_interval_tbl(i).attribute_category,
2036 attribute1 = p_x_interval_tbl(i).attribute1,
2037 attribute2 = p_x_interval_tbl(i).attribute2,
2038 attribute3 = p_x_interval_tbl(i).attribute3,
2039 attribute4 = p_x_interval_tbl(i).attribute4,
2040 attribute5 = p_x_interval_tbl(i).attribute5,
2041 attribute6 = p_x_interval_tbl(i).attribute6,
2042 attribute7 = p_x_interval_tbl(i).attribute7,
2043 attribute8 = p_x_interval_tbl(i).attribute8,
2044 attribute9 = p_x_interval_tbl(i).attribute9,
2045 attribute10 = p_x_interval_tbl(i).attribute10,
2046 attribute11 = p_x_interval_tbl(i).attribute11,
2047 attribute12 = p_x_interval_tbl(i).attribute12,
2048 attribute13 = p_x_interval_tbl(i).attribute13,
2049 attribute14 = p_x_interval_tbl(i).attribute14,
2050 attribute15 = p_x_interval_tbl(i).attribute15,
2051 last_update_date = p_x_interval_tbl(i).last_update_date,
2052 last_updated_by = p_x_interval_tbl(i).last_updated_by,
2053 last_update_login = p_x_interval_tbl(i).last_update_login
2054 WHERE mr_interval_id = p_x_interval_tbl(i).mr_interval_id
2055 AND object_version_number = p_x_interval_tbl(i).object_version_number;
2056
2057 -- If the record does not exist, then, abort API.
2058 IF ( SQL%ROWCOUNT = 0 ) THEN
2059 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2060 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_interval_tbl(i) ) );
2061 FND_MSG_PUB.add;
2062 RAISE FND_API.G_EXC_ERROR;
2063 END IF;
2064
2065 -- Set OUT values
2066 p_x_interval_tbl(i).object_version_number := p_x_interval_tbl(i).object_version_number + 1;
2067
2068 ELSIF ( p_x_interval_tbl(i).dml_operation = 'D' ) THEN
2069
2070 -- Delete the record
2071 DELETE AHL_MR_INTERVALS
2072 WHERE mr_interval_id = p_x_interval_tbl(i).mr_interval_id
2073 AND object_version_number = p_x_interval_tbl(i).object_version_number;
2074
2075 -- If the record does not exist, then, abort API.
2076 IF ( SQL%ROWCOUNT = 0 ) THEN
2077 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2078 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
2079 FND_MSG_PUB.add;
2080 RAISE FND_API.G_EXC_ERROR;
2081 END IF;
2082
2083 END IF;
2084 END LOOP;
2085
2086 IF G_DEBUG = 'Y' THEN
2087 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
2088 END IF;
2089
2090 -- Perform cross records validations and duplicate records check
2091 validate_records
2092 (
2093 p_x_threshold_rec.mr_effectivity_id, -- IN
2094 g_repetitive_flag, -- IN
2095 G_APPLN_USAGE, -- IN
2096 g_mr_type_code, -- IN
2097 l_return_status -- OUT
2098 );
2099
2100 -- If any severe error occurs, then, abort API.
2101 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2102 RAISE FND_API.G_EXC_ERROR;
2103 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105 END IF;
2106
2107 IF G_DEBUG = 'Y' THEN
2108 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
2109 END IF;
2110
2111 -- Perform the Commit (if requested)
2112 IF FND_API.to_boolean( p_commit ) THEN
2113 COMMIT WORK;
2114 END IF;
2115
2116 -- Add informational message if any truncation of interval values occured.
2117 -- Fix for bug#3482307
2118 IF (l_fractional_trunc_flag) THEN
2119 FND_MESSAGE.Set_Name('AHL','AHL_FMP_FRACTION_TRUNC');
2120 FND_MSG_PUB.ADD;
2121 END IF;
2122
2123 -- Count and Get messages (optional)
2124 FND_MSG_PUB.count_and_get
2125 (
2126 p_encoded => FND_API.G_FALSE,
2127 p_count => x_msg_count,
2128 p_data => x_msg_data
2129 );
2130
2131 -- Disable debug (if enabled)
2132 IF ( G_DEBUG = 'Y' ) THEN
2133 AHL_DEBUG_PUB.disable_debug;
2134 END IF;
2135
2136 EXCEPTION
2137
2138 WHEN FND_API.G_EXC_ERROR THEN
2139 ROLLBACK TO process_interval_PVT;
2140 x_return_status := FND_API.G_RET_STS_ERROR ;
2141 FND_MSG_PUB.count_and_get
2142 (
2143 p_encoded => FND_API.G_FALSE,
2144 p_count => x_msg_count,
2145 p_data => x_msg_data
2146 );
2147
2148 -- Disable debug (if enabled)
2149 IF ( G_DEBUG = 'Y' ) THEN
2150 AHL_DEBUG_PUB.disable_debug;
2151 END IF;
2152
2153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2154 ROLLBACK TO process_interval_PVT;
2155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2156 FND_MSG_PUB.count_and_get
2157 (
2158 p_encoded => FND_API.G_FALSE,
2159 p_count => x_msg_count,
2160 p_data => x_msg_data
2161 );
2162
2163 -- Disable debug (if enabled)
2164 IF ( G_DEBUG = 'Y' ) THEN
2165 AHL_DEBUG_PUB.disable_debug;
2166 END IF;
2167
2168 WHEN OTHERS THEN
2169 ROLLBACK TO process_interval_PVT;
2170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2171 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2172 THEN
2173 FND_MSG_PUB.add_exc_msg
2174 (
2175 p_pkg_name => G_PKG_NAME,
2176 p_procedure_name => G_API_NAME,
2177 p_error_text => SUBSTRB(SQLERRM,1,240)
2178 );
2179 END IF;
2180 FND_MSG_PUB.count_and_get
2181 (
2182 p_encoded => FND_API.G_FALSE,
2183 p_count => x_msg_count,
2184 p_data => x_msg_data
2185 );
2186
2187 -- Disable debug (if enabled)
2188 IF ( G_DEBUG = 'Y' ) THEN
2189 AHL_DEBUG_PUB.disable_debug;
2190 END IF;
2191
2192 END process_interval;
2193
2194 END AHL_FMP_MR_INTERVAL_PVT;
[Home] [Help]