1 PACKAGE BODY AHL_FMP_MR_INTERVAL_PVT AS
2 /* $Header: AHLVMITB.pls 120.6.12020000.2 2012/12/10 14:05:46 shnatu 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 -- bachandr added following logic for ADAT ER # 7415856
513 -- If start date or start value is present and user has not entered due date rule,
514 -- then due date rule is defaulted to 'At Start'.
515 -- ADAT ER # 7415856 -- start
516
517 IF
518 (
519 p_x_interval_rec.duedate_rule_code IS NULL
520 OR
521 p_x_interval_rec.duedate_rule_code = FND_API.G_MISS_CHAR
522 )
523 AND
524 (
525 (
526 p_x_interval_rec.start_value IS NOT NULL
527 AND
528 p_x_interval_rec.start_value <> FND_API.G_MISS_NUM
529 )
530 OR
531 (
532 p_x_interval_rec.start_date IS NOT NULL
533 AND
534 p_x_interval_rec.start_date <> FND_API.G_MISS_DATE
535 )
536 )
537 THEN
538 p_x_interval_rec.duedate_rule_code := 'START';
539 END IF;
540 -- ADAT ER # 7415856 -- end
541
542 END default_attributes;
543
544 -- Procedure to add Default values for missing attributes (CREATE)
545 PROCEDURE default_missing_attributes
546 (
547 p_x_interval_rec IN OUT NOCOPY interval_rec_type
548 )
549 IS
550
551 BEGIN
552
553 -- Convert G_MISS values to NULL
554 IF ( p_x_interval_rec.earliest_due_value = FND_API.G_MISS_NUM ) THEN
555 p_x_interval_rec.earliest_due_value := null;
556 END IF;
557
558 IF ( p_x_interval_rec.start_value = FND_API.G_MISS_NUM ) THEN
559 p_x_interval_rec.start_value := null;
560 END IF;
561
562 IF ( p_x_interval_rec.stop_value = FND_API.G_MISS_NUM ) THEN
563 p_x_interval_rec.stop_value := null;
564 END IF;
565
566 IF ( p_x_interval_rec.tolerance_before = FND_API.G_MISS_NUM ) THEN
567 p_x_interval_rec.tolerance_before := null;
568 END IF;
569
570 IF ( p_x_interval_rec.tolerance_after = FND_API.G_MISS_NUM ) THEN
571 p_x_interval_rec.tolerance_after := null;
572 END IF;
573
574 IF ( p_x_interval_rec.reset_value = FND_API.G_MISS_NUM ) THEN
575 p_x_interval_rec.reset_value := null;
576 END IF;
577
578 IF ( p_x_interval_rec.start_date = FND_API.G_MISS_DATE ) THEN
579 p_x_interval_rec.start_date := null;
580 END IF;
581
582 IF ( p_x_interval_rec.stop_date = FND_API.G_MISS_DATE ) THEN
583 p_x_interval_rec.stop_date := null;
584 END IF;
585
586 IF ( p_x_interval_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
587 p_x_interval_rec.attribute_category := null;
588 END IF;
589
590 IF ( p_x_interval_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
591 p_x_interval_rec.attribute1 := null;
592 END IF;
593
594 IF ( p_x_interval_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
595 p_x_interval_rec.attribute2 := null;
596 END IF;
597
598 IF ( p_x_interval_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
599 p_x_interval_rec.attribute3 := null;
600 END IF;
601
602 IF ( p_x_interval_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
603 p_x_interval_rec.attribute4 := null;
604 END IF;
605
606 IF ( p_x_interval_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
607 p_x_interval_rec.attribute5 := null;
608 END IF;
609
610 IF ( p_x_interval_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
611 p_x_interval_rec.attribute6 := null;
612 END IF;
613
614 IF ( p_x_interval_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
615 p_x_interval_rec.attribute7 := null;
616 END IF;
617
618 IF ( p_x_interval_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
619 p_x_interval_rec.attribute8 := null;
620 END IF;
621
622 IF ( p_x_interval_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
623 p_x_interval_rec.attribute9 := null;
624 END IF;
625
626 IF ( p_x_interval_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
627 p_x_interval_rec.attribute10 := null;
628 END IF;
629
630 IF ( p_x_interval_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
631 p_x_interval_rec.attribute11 := null;
632 END IF;
633
634 IF ( p_x_interval_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
635 p_x_interval_rec.attribute12 := null;
636 END IF;
637
638 IF ( p_x_interval_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
639 p_x_interval_rec.attribute13 := null;
640 END IF;
641
642 IF ( p_x_interval_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
643 p_x_interval_rec.attribute14 := null;
644 END IF;
645
646 IF ( p_x_interval_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
647 p_x_interval_rec.attribute15 := null;
648 END IF;
649
650 --pdoki added for ADAT ER
651 IF ( p_x_interval_rec.duedate_rule_code = FND_API.G_MISS_CHAR ) THEN
652 p_x_interval_rec.duedate_rule_code := null;
653 END IF;
654
655 END default_missing_attributes;
656
657 -- Procedure to add Default values for unchanged attributes (UPDATE)
658 PROCEDURE default_unchanged_attributes
659 (
660 p_x_interval_rec IN OUT NOCOPY interval_rec_type
661 )
662 IS
663
664 l_old_interval_rec interval_rec_type;
665
666 CURSOR get_old_rec ( c_mr_interval_id NUMBER )
667 IS
668 SELECT counter_id,
669 interval_value,
670 earliest_due_value,
671 start_value,
672 stop_value,
673 tolerance_before,
674 tolerance_after,
675 reset_value,
676 start_date,
677 stop_date,
678 attribute_category,
679 attribute1,
680 attribute2,
681 attribute3,
682 attribute4,
683 attribute5,
684 attribute6,
685 attribute7,
686 attribute8,
687 attribute9,
688 attribute10,
689 attribute11,
690 attribute12,
691 attribute13,
692 attribute14,
693 attribute15,
694 calc_duedate_rule_code --pdoki added for ADAT ER
695 FROM AHL_MR_INTERVALS_APP_V
696 WHERE mr_interval_id = c_mr_interval_id;
697
698 BEGIN
699
700 -- Get the old record from AHL_MR_INTERVALS.
701 OPEN get_old_rec( p_x_interval_rec.mr_interval_id );
702
703 FETCH get_old_rec INTO
704 l_old_interval_rec.counter_id,
705 l_old_interval_rec.interval_value,
706 l_old_interval_rec.earliest_due_value,
707 l_old_interval_rec.start_value,
708 l_old_interval_rec.stop_value,
709 l_old_interval_rec.tolerance_before,
710 l_old_interval_rec.tolerance_after,
711 l_old_interval_rec.reset_value,
712 l_old_interval_rec.start_date,
713 l_old_interval_rec.stop_date,
714 l_old_interval_rec.attribute_category,
715 l_old_interval_rec.attribute1,
716 l_old_interval_rec.attribute2,
717 l_old_interval_rec.attribute3,
718 l_old_interval_rec.attribute4,
719 l_old_interval_rec.attribute5,
720 l_old_interval_rec.attribute6,
721 l_old_interval_rec.attribute7,
722 l_old_interval_rec.attribute8,
723 l_old_interval_rec.attribute9,
724 l_old_interval_rec.attribute10,
725 l_old_interval_rec.attribute11,
726 l_old_interval_rec.attribute12,
727 l_old_interval_rec.attribute13,
728 l_old_interval_rec.attribute14,
729 l_old_interval_rec.attribute15,
730 l_old_interval_rec.duedate_rule_code; --pdoki added for ADAT ER
731
732 IF get_old_rec%NOTFOUND THEN
733 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_INTERVAL_REC' );
734 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_interval_rec ) );
735 FND_MSG_PUB.add;
736 CLOSE get_old_rec;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739
740 CLOSE get_old_rec;
741
742 -- Convert G_MISS values to NULL and NULL values to Old values
743 IF ( p_x_interval_rec.counter_id IS NULL ) THEN
744 p_x_interval_rec.counter_id := l_old_interval_rec.counter_id;
745 END IF;
746
747 IF ( p_x_interval_rec.interval_value = FND_API.G_MISS_NUM ) THEN
748 p_x_interval_rec.interval_value := null;
749 ELSIF ( p_x_interval_rec.interval_value IS NULL ) THEN
750 p_x_interval_rec.interval_value := l_old_interval_rec.interval_value;
751 END IF;
752
753 IF ( p_x_interval_rec.earliest_due_value = FND_API.G_MISS_NUM ) THEN
754 p_x_interval_rec.earliest_due_value := null;
755 ELSIF ( p_x_interval_rec.earliest_due_value IS NULL ) THEN
756 p_x_interval_rec.earliest_due_value := l_old_interval_rec.earliest_due_value;
757 END IF;
758
759 IF ( p_x_interval_rec.start_value = FND_API.G_MISS_NUM ) THEN
760 p_x_interval_rec.start_value := null;
761 ELSIF ( p_x_interval_rec.start_value IS NULL ) THEN
762 p_x_interval_rec.start_value := l_old_interval_rec.start_value;
763 END IF;
764
765 IF ( p_x_interval_rec.stop_value = FND_API.G_MISS_NUM ) THEN
766 p_x_interval_rec.stop_value := null;
767 ELSIF ( p_x_interval_rec.stop_value IS NULL ) THEN
768 p_x_interval_rec.stop_value := l_old_interval_rec.stop_value;
769 END IF;
770
771 IF ( p_x_interval_rec.tolerance_before = FND_API.G_MISS_NUM ) THEN
772 p_x_interval_rec.tolerance_before := null;
773 ELSIF ( p_x_interval_rec.tolerance_before IS NULL ) THEN
774 p_x_interval_rec.tolerance_before := l_old_interval_rec.tolerance_before;
775 END IF;
776
777 IF ( p_x_interval_rec.tolerance_after = FND_API.G_MISS_NUM ) THEN
778 p_x_interval_rec.tolerance_after := null;
779 ELSIF ( p_x_interval_rec.tolerance_after IS NULL ) THEN
780 p_x_interval_rec.tolerance_after := l_old_interval_rec.tolerance_after;
781 END IF;
782
783 IF ( p_x_interval_rec.reset_value = FND_API.G_MISS_NUM ) THEN
784 p_x_interval_rec.reset_value := null;
785 ELSIF ( p_x_interval_rec.reset_value IS NULL ) THEN
786 p_x_interval_rec.reset_value := l_old_interval_rec.reset_value;
787 END IF;
788
789
790 IF ( p_x_interval_rec.start_date = FND_API.G_MISS_DATE ) THEN
791 p_x_interval_rec.start_date := null;
792 ELSIF ( p_x_interval_rec.start_date IS NULL ) THEN
793 p_x_interval_rec.start_date := l_old_interval_rec.start_date;
794 END IF;
795
796 IF ( p_x_interval_rec.stop_date = FND_API.G_MISS_DATE ) THEN
797 p_x_interval_rec.stop_date := null;
798 ELSIF ( p_x_interval_rec.stop_date IS NULL ) THEN
799 p_x_interval_rec.stop_date := l_old_interval_rec.stop_date;
800 END IF;
801
802 IF ( p_x_interval_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
803 p_x_interval_rec.attribute_category := null;
804 ELSIF ( p_x_interval_rec.attribute_category IS NULL ) THEN
805 p_x_interval_rec.attribute_category := l_old_interval_rec.attribute_category;
806 END IF;
807
808 IF ( p_x_interval_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
809 p_x_interval_rec.attribute1 := null;
810 ELSIF ( p_x_interval_rec.attribute1 IS NULL ) THEN
811 p_x_interval_rec.attribute1 := l_old_interval_rec.attribute1;
812 END IF;
813
814 IF ( p_x_interval_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
815 p_x_interval_rec.attribute2 := null;
816 ELSIF ( p_x_interval_rec.attribute2 IS NULL ) THEN
817 p_x_interval_rec.attribute2 := l_old_interval_rec.attribute2;
818 END IF;
819
820 IF ( p_x_interval_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
821 p_x_interval_rec.attribute3 := null;
822 ELSIF ( p_x_interval_rec.attribute3 IS NULL ) THEN
823 p_x_interval_rec.attribute3 := l_old_interval_rec.attribute3;
824 END IF;
825
826 IF ( p_x_interval_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
827 p_x_interval_rec.attribute4 := null;
828 ELSIF ( p_x_interval_rec.attribute4 IS NULL ) THEN
829 p_x_interval_rec.attribute4 := l_old_interval_rec.attribute4;
830 END IF;
831
832 IF ( p_x_interval_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
833 p_x_interval_rec.attribute5 := null;
834 ELSIF ( p_x_interval_rec.attribute5 IS NULL ) THEN
835 p_x_interval_rec.attribute5 := l_old_interval_rec.attribute5;
836 END IF;
837
838 IF ( p_x_interval_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
839 p_x_interval_rec.attribute6 := null;
840 ELSIF ( p_x_interval_rec.attribute6 IS NULL ) THEN
841 p_x_interval_rec.attribute6 := l_old_interval_rec.attribute6;
842 END IF;
843
844 IF ( p_x_interval_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
845 p_x_interval_rec.attribute7 := null;
846 ELSIF ( p_x_interval_rec.attribute7 IS NULL ) THEN
847 p_x_interval_rec.attribute7 := l_old_interval_rec.attribute7;
848 END IF;
849
850 IF ( p_x_interval_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
851 p_x_interval_rec.attribute8 := null;
852 ELSIF ( p_x_interval_rec.attribute8 IS NULL ) THEN
853 p_x_interval_rec.attribute8 := l_old_interval_rec.attribute8;
854 END IF;
855
856 IF ( p_x_interval_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
857 p_x_interval_rec.attribute9 := null;
858 ELSIF ( p_x_interval_rec.attribute9 IS NULL ) THEN
859 p_x_interval_rec.attribute9 := l_old_interval_rec.attribute9;
860 END IF;
861
862 IF ( p_x_interval_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
863 p_x_interval_rec.attribute10 := null;
864 ELSIF ( p_x_interval_rec.attribute10 IS NULL ) THEN
865 p_x_interval_rec.attribute10 := l_old_interval_rec.attribute10;
866 END IF;
867
868 IF ( p_x_interval_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
869 p_x_interval_rec.attribute11 := null;
870 ELSIF ( p_x_interval_rec.attribute11 IS NULL ) THEN
871 p_x_interval_rec.attribute11 := l_old_interval_rec.attribute11;
872 END IF;
873
874 IF ( p_x_interval_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
875 p_x_interval_rec.attribute12 := null;
876 ELSIF ( p_x_interval_rec.attribute12 IS NULL ) THEN
877 p_x_interval_rec.attribute12 := l_old_interval_rec.attribute12;
878 END IF;
879
880 IF ( p_x_interval_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
881 p_x_interval_rec.attribute13 := null;
882 ELSIF ( p_x_interval_rec.attribute13 IS NULL ) THEN
883 p_x_interval_rec.attribute13 := l_old_interval_rec.attribute13;
884 END IF;
885
886 IF ( p_x_interval_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
887 p_x_interval_rec.attribute14 := null;
888 ELSIF ( p_x_interval_rec.attribute14 IS NULL ) THEN
889 p_x_interval_rec.attribute14 := l_old_interval_rec.attribute14;
890 END IF;
891
892 IF ( p_x_interval_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
893 p_x_interval_rec.attribute15 := null;
894 ELSIF ( p_x_interval_rec.attribute15 IS NULL ) THEN
895 p_x_interval_rec.attribute15 := l_old_interval_rec.attribute15;
896 END IF;
897
898 --pdoki added for ADAT ER
899 IF ( p_x_interval_rec.duedate_rule_code = FND_API.G_MISS_CHAR ) THEN
900 p_x_interval_rec.duedate_rule_code := null;
901 ELSIF ( p_x_interval_rec.duedate_rule_code IS NULL ) THEN
902 p_x_interval_rec.duedate_rule_code := l_old_interval_rec.duedate_rule_code;
903 END IF;
904
905 END default_unchanged_attributes;
906
907 -- Procedure to validate individual interval attributes
908 PROCEDURE validate_attributes
909 (
910 p_interval_rec IN interval_rec_type,
911 x_return_status OUT NOCOPY VARCHAR2
912 )
913 IS
914
915 BEGIN
916 x_return_status := FND_API.G_RET_STS_SUCCESS;
917
918 -- Check if the Counter ID does not contain a null value.
919 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
920 IF ( ( p_interval_rec.dml_operation = 'C' AND
921 p_interval_rec.counter_id IS NULL AND
922 p_interval_rec.counter_name IS NULL ) OR
923 ( p_interval_rec.counter_id = FND_API.G_MISS_NUM AND
924 p_interval_rec.counter_name = FND_API.G_MISS_CHAR ) ) THEN
925 FND_MESSAGE.set_name( 'AHL','AHL_FMP_COUNTER_NULL' );
926 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
927 FND_MSG_PUB.add;
928 END IF;
929 END IF;
930
931 -- Check if the Interval Value does not contain an invalid value
932 IF ( p_interval_rec.interval_value IS NOT NULL AND
933 p_interval_rec.interval_value <= 0 ) THEN
934 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL' );
935 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.interval_value ) );
936 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
937 FND_MSG_PUB.add;
938 END IF;
939
940 -- Check if the earliest due Value does not contain an invalid value.
941 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
942 IF ( p_interval_rec.earliest_due_value IS NOT NULL AND
943 p_interval_rec.earliest_due_value <> FND_API.G_MISS_NUM AND
944 p_interval_rec.earliest_due_value < 0 ) THEN
945 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EARL_DUE' );
946 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.earliest_due_value ) );
947 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
948 FND_MSG_PUB.add;
949 END IF;
950 END IF;
951
952 -- Check if the Start Value does not contain an invalid value.
953 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
954 IF ( p_interval_rec.start_value IS NOT NULL AND
955 p_interval_rec.start_value <> FND_API.G_MISS_NUM AND
956 p_interval_rec.start_value < 0 ) THEN
957 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_START' );
958 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.start_value ) );
959 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
960 FND_MSG_PUB.add;
961 END IF;
962 END IF;
963
964 -- Check if the Stop Value does not contain an invalid value.
965 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
966 IF ( p_interval_rec.stop_value IS NOT NULL AND
967 p_interval_rec.stop_value <> FND_API.G_MISS_NUM AND
968 p_interval_rec.stop_value <= 0 ) THEN
969 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_STOP' );
970 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.stop_value ) );
971 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
972 FND_MSG_PUB.add;
973 END IF;
974 END IF;
975
976 -- Check if the Tolerance Before does not contain an invalid value.
977 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
978 IF ( p_interval_rec.tolerance_before IS NOT NULL AND
979 p_interval_rec.tolerance_before <> FND_API.G_MISS_NUM AND
980 p_interval_rec.tolerance_before < 0 ) THEN
981 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOLERANCE_BF' );
982 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.tolerance_before ) );
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 -- Check if the Tolerance After does not contain an invalid value.
989 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
990 IF ( p_interval_rec.tolerance_after IS NOT NULL AND
991 p_interval_rec.tolerance_after <> FND_API.G_MISS_NUM AND
992 p_interval_rec.tolerance_after < 0 ) THEN
993 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOLERANCE_AF' );
994 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.tolerance_after ) );
995 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
996 FND_MSG_PUB.add;
997 END IF;
998 END IF;
999
1000 -- Check if the Reset After does not contain an invalid value.
1001 IF ( p_interval_rec.dml_operation <> 'D' ) THEN
1002 IF ( p_interval_rec.reset_value IS NOT NULL AND
1003 p_interval_rec.reset_value <> FND_API.G_MISS_NUM AND
1004 p_interval_rec.reset_value < 0 ) THEN
1005 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_RESET_VALUE' );
1006 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_interval_rec.reset_value ) );
1007 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1008 FND_MSG_PUB.add;
1009 END IF;
1010 END IF;
1011
1012 -- Check if the mandatory Interval ID column contains a null value.
1013 IF ( p_interval_rec.dml_operation <> 'C' ) THEN
1014 IF ( p_interval_rec.mr_interval_id IS NULL OR
1015 p_interval_rec.mr_interval_id = FND_API.G_MISS_NUM ) THEN
1016 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MR_INTERVAL_ID_NULL' );
1017 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1018 FND_MSG_PUB.add;
1019 END IF;
1020 END IF;
1021
1022 -- Check if the mandatory Object Version Number column contains a null value.
1023 IF ( p_interval_rec.dml_operation <> 'C' ) THEN
1024 IF ( p_interval_rec.object_version_number IS NULL OR
1025 p_interval_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1026 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INT_OBJ_VERSION_NULL' );
1027 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1028 FND_MSG_PUB.add;
1029 END IF;
1030 END IF;
1031
1032 END validate_attributes;
1033
1034 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
1035 PROCEDURE validate_record
1036 (
1037 p_interval_rec IN interval_rec_type,
1038 p_repetitive_flag IN VARCHAR2,
1039 p_appln_code IN VARCHAR2,
1040 p_mr_type_code IN VARCHAR2,
1041 x_return_status OUT NOCOPY VARCHAR2
1042 )
1043 IS
1044
1045 l_return_status VARCHAR2(1);
1046 l_msg_data VARCHAR2(2000);
1047 l_initial_reading NUMBER;
1048
1049 BEGIN
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 -- Check if the appropriate attributes are passed for PM Programs
1053 IF ( p_mr_type_code = 'PROGRAM' AND
1054 ( p_interval_rec.tolerance_before IS NOT NULL OR
1055 p_interval_rec.tolerance_after IS NOT NULL OR
1056 p_interval_rec.reset_value IS NOT NULL OR
1057 p_interval_rec.interval_value IS NOT NULL OR
1058 p_interval_rec.start_date IS NOT NULL OR
1059 p_interval_rec.stop_date IS NOT NULL ) ) THEN
1060 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_INPUTS' );
1061 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1062 FND_MSG_PUB.add;
1063 END IF;
1064
1065 -- Check if the Interval Value is always passed for non-PM Programs
1066 IF ( ( p_mr_type_code IS NULL OR
1067 p_mr_type_code <> 'PROGRAM' ) AND
1068 ( p_interval_rec.dml_operation = 'C' AND
1069 p_interval_rec.interval_value IS NULL ) OR
1070 ( p_interval_rec.interval_value = FND_API.G_MISS_NUM ) ) THEN
1071 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INTERVAL_NULL' );
1072 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1073 FND_MSG_PUB.add;
1074 END IF;
1075
1076 -- Check if Start is passed for PM Programs
1077 IF ( p_mr_type_code = 'PROGRAM' AND
1078 p_interval_rec.start_value IS NULL ) THEN
1079 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_START_NULL' );
1080 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1081 FND_MSG_PUB.add;
1082 RETURN;
1083 END IF;
1084
1085 -- Check if Stop is passed for PM Programs
1086 IF ( p_mr_type_code = 'PROGRAM' AND
1087 p_interval_rec.stop_value IS NULL ) THEN
1088 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_STOP_NULL' );
1089 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1090 FND_MSG_PUB.add;
1091 RETURN;
1092 END IF;
1093
1094 -- Check if Range is not passed for One Time MRs.
1095 IF ( ( p_mr_type_code IS NULL OR
1096 p_mr_type_code <> 'PROGRAM' ) AND
1097 p_repetitive_flag = 'N' AND
1098 ( p_interval_rec.start_value IS NOT NULL OR
1099 p_interval_rec.stop_value IS NOT NULL OR
1100 p_interval_rec.start_date IS NOT NULL OR
1101 p_interval_rec.stop_date IS NOT NULL ) ) THEN
1102 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RANGE_NOTNULL' );
1103 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1104 FND_MSG_PUB.add;
1105 RETURN;
1106 END IF;
1107
1108 -- Check if the Tolerance Before is less than the Interval Value
1109 IF ( p_interval_rec.tolerance_before IS NOT NULL AND
1110 p_interval_rec.tolerance_before >= p_interval_rec.interval_value ) THEN
1111 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOL_BF_INT' );
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 Tolerance After is less than the Interval Value
1117 IF ( p_interval_rec.tolerance_after IS NOT NULL AND
1118 p_interval_rec.tolerance_after >= p_interval_rec.interval_value ) THEN
1119 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_TOL_AF_INT' );
1120 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1121 FND_MSG_PUB.add;
1122 END IF;
1123
1124 IF ( p_interval_rec.counter_id IS NOT NULL AND
1125 p_interval_rec.reset_value IS NOT NULL AND
1126 p_interval_rec.reset_value >= 0 ) THEN
1127
1128 SELECT NVL( initial_reading, 0 )
1129 INTO l_initial_reading
1130 FROM csi_counter_template_vl --amsriniv
1131 WHERE counter_id = p_interval_rec.counter_id;
1132
1133 -- Check if the Reset Value is less than the initial reading for the counter
1134 IF ( p_interval_rec.reset_value < l_initial_reading ) THEN
1135 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RESET_LESS_INIT_VALUE' );
1136 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( l_initial_reading ) );
1137 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1138 FND_MSG_PUB.add;
1139 END IF;
1140
1141 END IF;
1142
1143 -- Check if the Start Value is passed if Stop Value is passed
1144 IF ( p_interval_rec.start_value IS NULL AND
1145 p_interval_rec.stop_value IS NOT NULL ) THEN
1146 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MISSING_VALUE_RANGE' );
1147 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1148 FND_MSG_PUB.add;
1149 END IF;
1150
1151 -- Check if the Start Value is passed if Stop Value is passed
1152 IF ( p_interval_rec.start_value IS NULL AND
1153 p_interval_rec.start_date IS NULL AND
1154 p_interval_rec.earliest_due_value IS NOT NULL ) THEN
1155 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1156 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1157 FND_MSG_PUB.add;
1158 END IF;
1159
1160 -- Check if the Start Date is passed if Stop Date is passed
1161 IF ( p_interval_rec.start_date IS NULL AND
1162 p_interval_rec.stop_date IS NOT NULL ) THEN
1163 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MISSING_DATE_RANGE' );
1164 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1165 FND_MSG_PUB.add;
1166 END IF;
1167
1168 -- Check if the Stop Value is not greater than Start Value
1169 IF ( p_interval_rec.start_value IS NOT NULL AND
1170 p_interval_rec.stop_value IS NOT NULL AND
1171 p_interval_rec.stop_value < p_interval_rec.start_value ) THEN
1172 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_VALUE_RANGE' );
1173 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1174 FND_MSG_PUB.add;
1175 END IF;
1176
1177 -- Check if the Stop date is not greater than Start date
1178 IF ( p_interval_rec.start_date IS NOT NULL AND
1179 p_interval_rec.stop_date IS NOT NULL AND
1180 p_interval_rec.stop_date < p_interval_rec.start_date ) THEN
1181 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_DATE_RANGE' );
1182 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1183 FND_MSG_PUB.add;
1184 END IF;
1185
1186 -- Check if both Date based and Value based interval ranges are not passed
1187 IF ( ( p_interval_rec.start_date IS NOT NULL OR
1188 p_interval_rec.stop_date IS NOT NULL ) AND
1189 ( p_interval_rec.start_value IS NOT NULL OR
1190 p_interval_rec.stop_value IS NOT NULL ) ) THEN
1191 FND_MESSAGE.set_name( 'AHL','AHL_FMP_BOTH_DATE_VALUE_RANGE' );
1192 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1193 FND_MSG_PUB.add;
1194 END IF;
1195
1196 -- Check if the Interval Value is between Start Value and Stop Value
1197 IF ( p_interval_rec.start_value IS NOT NULL AND
1198 p_interval_rec.stop_value IS NOT NULL AND
1199 p_interval_rec.interval_value >
1200 --( p_interval_rec.stop_value - p_interval_rec.start_value ) ) THEN
1201 -- Fix for bug# 3482307.
1202 ( p_interval_rec.stop_value - p_interval_rec.start_value + 1) ) THEN
1203 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1204 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1205 FND_MSG_PUB.add;
1206 END IF;
1207
1208 -- Check if the earliest due value is less than Start Value
1209 IF ( p_interval_rec.start_value IS NOT NULL AND
1210 p_interval_rec.earliest_due_value > p_interval_rec.start_value ) THEN
1211 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_INTERVAL_RANGE' );
1212 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_interval_rec ) );
1213 FND_MSG_PUB.add;
1214 END IF;
1215
1216 END validate_record;
1217
1218 -- Procedure to Perform cross records validation and duplicate checks
1219 PROCEDURE validate_records
1220 (
1221 p_mr_effectivity_id IN NUMBER,
1222 p_repetitive_flag IN VARCHAR2,
1223 p_appln_code IN VARCHAR2,
1224 p_mr_type_code IN VARCHAR2,
1225 x_return_status OUT NOCOPY VARCHAR2
1226 )
1227 IS
1228
1229 l_interval_rec interval_rec_type;
1230 l_prev_counter_id NUMBER := NULL;
1231 l_prev_stop_date DATE := NULL;
1232 l_prev_stop_value NUMBER := NULL;
1233 l_counter_id NUMBER := NULL;
1234 l_counter_name VARCHAR2(30) := NULL;
1235 l_start_date DATE;
1236 l_stop_date DATE;
1237 l_start_value NUMBER;
1238 l_stop_value NUMBER;
1239 l_earliest_due_value NUMBER;
1240
1241 CURSOR get_pm_pgm_dup_rec( c_mr_effectivity_id NUMBER )
1242 IS
1243 SELECT counter_id,
1244 counter_name
1245 FROM AHL_MR_INTERVALS_V
1246 WHERE mr_effectivity_id = c_mr_effectivity_id
1247 GROUP BY counter_id,
1248 counter_name
1249 HAVING count(*) > 1;
1250
1251 CURSOR get_dup_rec( c_mr_effectivity_id NUMBER )
1252 IS
1253 SELECT counter_id,
1254 counter_name,
1255 start_value,
1256 stop_value,
1257 start_date,
1258 stop_date
1259 FROM AHL_MR_INTERVALS_V
1260 WHERE mr_effectivity_id = c_mr_effectivity_id
1261 GROUP BY counter_id,
1262 counter_name,
1263 start_value,
1264 stop_value,
1265 start_date,
1266 stop_date
1267 HAVING count(*) > 1;
1268
1269 CURSOR get_recs_for_date_range( c_mr_effectivity_id NUMBER )
1270 IS
1271 SELECT counter_id,
1272 counter_name,
1273 start_date,
1274 stop_date
1275 FROM AHL_MR_INTERVALS_V
1276 WHERE mr_effectivity_id = c_mr_effectivity_id
1277 ORDER BY counter_id,
1278 start_date,
1279 stop_date;
1280
1281 CURSOR get_recs_for_value_range( c_mr_effectivity_id NUMBER )
1282 IS
1283 SELECT counter_id,
1284 counter_name,
1285 earliest_due_value,
1286 start_value,
1287 stop_value
1288 FROM AHL_MR_INTERVALS_V
1289 WHERE mr_effectivity_id = c_mr_effectivity_id
1290 ORDER BY counter_id,
1291 start_value,
1292 stop_value;
1293
1294 CURSOR check_unique_reset_value( c_mr_effectivity_id NUMBER )
1295 IS
1296 SELECT counter_name
1297 FROM AHL_MR_INTERVALS_V
1298 WHERE mr_effectivity_id = c_mr_effectivity_id
1299 AND reset_value IS NOT NULL
1300 GROUP BY counter_name
1301 HAVING count(*) > 1;
1302
1303 BEGIN
1304 x_return_status := FND_API.G_RET_STS_SUCCESS;
1305
1306 -- Check whether any duplicate Interval record (based on Counter) exist
1307 -- For Preventive Maintenance Programs
1308
1309 IF ( p_appln_code = 'PM' AND
1310 p_mr_type_code = 'PROGRAM' ) THEN
1311
1312 OPEN get_pm_pgm_dup_rec( p_mr_effectivity_id );
1313
1314 LOOP
1315 FETCH get_pm_pgm_dup_rec INTO
1316 l_interval_rec.counter_id,
1317 l_interval_rec.counter_name;
1318
1319 EXIT WHEN get_pm_pgm_dup_rec%NOTFOUND;
1320
1321 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_PGM_DUP_INT_REC' );
1322 FND_MESSAGE.set_token( 'FIELD', l_interval_rec.counter_name );
1323 FND_MSG_PUB.add;
1324 END LOOP;
1325
1326 IF ( get_pm_pgm_dup_rec%ROWCOUNT > 0 ) THEN
1327 x_return_status := FND_API.G_RET_STS_ERROR;
1328 END IF;
1329
1330 CLOSE get_pm_pgm_dup_rec;
1331 RETURN;
1332
1333 END IF;
1334
1335 -- Check whether any duplicate Interval record (based on Counter and Range) exist
1336 OPEN get_dup_rec( p_mr_effectivity_id );
1337
1338 LOOP
1339 FETCH get_dup_rec INTO
1340 l_interval_rec.counter_id,
1341 l_interval_rec.counter_name,
1342 l_interval_rec.start_value,
1343 l_interval_rec.stop_value,
1344 l_interval_rec.start_date,
1345 l_interval_rec.stop_date;
1346
1347 EXIT WHEN get_dup_rec%NOTFOUND;
1348
1349 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUPLICATE_INT_REC' );
1350 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1351 FND_MSG_PUB.add;
1352 END LOOP;
1353
1354 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1355 CLOSE get_dup_rec;
1356 x_return_status := FND_API.G_RET_STS_ERROR;
1357 RETURN;
1358 END IF;
1359
1360 CLOSE get_dup_rec;
1361
1362 IF ( p_repetitive_flag = 'N' ) THEN
1363 RETURN;
1364 END IF;
1365
1366 OPEN get_recs_for_date_range( p_mr_effectivity_id );
1367
1368 LOOP
1369 FETCH get_recs_for_date_range INTO
1370 l_interval_rec.counter_id,
1371 l_interval_rec.counter_name,
1372 l_interval_rec.start_date,
1373 l_interval_rec.stop_date;
1374
1375 EXIT WHEN get_recs_for_date_range%NOTFOUND;
1376
1377 IF ( l_prev_counter_id IS NOT NULL AND
1378 l_prev_stop_date IS NOT NULL AND
1379 l_interval_rec.counter_id = l_prev_counter_id AND
1380 --l_interval_rec.start_date < l_prev_stop_date ) THEN
1381 -- Fix for bug# 3482307.
1382 l_interval_rec.start_date <= l_prev_stop_date ) THEN
1383 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_DATE_RANGE' );
1384 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1385 FND_MSG_PUB.add;
1386 CLOSE get_recs_for_date_range;
1387 x_return_status := FND_API.G_RET_STS_ERROR;
1388 RETURN;
1389 END IF;
1390
1391 l_prev_counter_id := l_interval_rec.counter_id;
1392 l_prev_stop_date := l_interval_rec.stop_date;
1393
1394 END LOOP;
1395
1396 CLOSE get_recs_for_date_range;
1397 l_prev_counter_id := null;
1398
1399 OPEN get_recs_for_value_range( p_mr_effectivity_id );
1400
1401 LOOP
1402 FETCH get_recs_for_value_range INTO
1403 l_interval_rec.counter_id,
1404 l_interval_rec.counter_name,
1405 l_interval_rec.earliest_due_value,
1406 l_interval_rec.start_value,
1407 l_interval_rec.stop_value;
1408
1409 EXIT WHEN get_recs_for_value_range%NOTFOUND;
1410
1411 IF ( l_prev_counter_id IS NOT NULL AND
1412 l_prev_stop_value IS NOT NULL AND
1413 l_interval_rec.counter_id = l_prev_counter_id AND
1414 --l_interval_rec.start_value < l_prev_stop_value ) THEN
1415 -- Fix for bug# 3482307.
1416 l_interval_rec.start_value <= l_prev_stop_value ) THEN
1417 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_VALUE_RANGE' );
1418 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1419 FND_MSG_PUB.add;
1420 CLOSE get_recs_for_value_range;
1421 x_return_status := FND_API.G_RET_STS_ERROR;
1422 RETURN;
1423 END IF;
1424
1425 IF ( l_prev_counter_id IS NOT NULL AND
1426 l_prev_stop_value IS NOT NULL AND
1427 l_interval_rec.counter_id = l_prev_counter_id AND
1428 l_interval_rec.earliest_due_value <= l_prev_stop_value ) THEN
1429 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_VALUE_RANGE' );
1430 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_interval_rec ) );
1431 FND_MSG_PUB.add;
1432 CLOSE get_recs_for_value_range;
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 RETURN;
1435 END IF;
1436
1437 l_prev_counter_id := l_interval_rec.counter_id;
1438 l_prev_stop_value := l_interval_rec.stop_value;
1439
1440 END LOOP;
1441
1442 CLOSE get_recs_for_value_range;
1443
1444 OPEN check_unique_reset_value( p_mr_effectivity_id );
1445 LOOP
1446 FETCH check_unique_reset_value
1447 INTO l_counter_name;
1448
1449 EXIT WHEN check_unique_reset_value%NOTFOUND;
1450
1451 FND_MESSAGE.set_name( 'AHL','AHL_FMP_RESET_VALUE_NONUNIQUE' );
1452 FND_MESSAGE.set_token( 'FIELD', l_counter_name );
1453 FND_MSG_PUB.add;
1454 END LOOP;
1455
1456 IF ( check_unique_reset_value%ROWCOUNT > 0 ) THEN
1457 CLOSE check_unique_reset_value;
1458 x_return_status := FND_API.G_RET_STS_ERROR;
1459 RETURN;
1460 END IF;
1461
1462 CLOSE check_unique_reset_value;
1463
1464 END validate_records;
1465
1466 -- Procedure to get the Repetitive Flag and Type for a MR
1467 PROCEDURE get_mr_header_details
1468 (
1469 p_mr_header_id IN NUMBER,
1470 x_repetitive_flag OUT NOCOPY VARCHAR2,
1471 x_mr_type_code OUT NOCOPY VARCHAR2
1472 )
1473 IS
1474
1475 CURSOR get_mr_details( c_mr_header_id NUMBER )
1476 IS
1477 SELECT repetitive_flag,
1478 type_code
1479 FROM AHL_MR_HEADERS_APP_V
1480 WHERE mr_header_id = c_mr_header_id;
1481
1482 BEGIN
1483
1484 -- Get the Repetitive Flag and Type of the MR.
1485 OPEN get_mr_details( p_mr_header_id );
1486
1487 FETCH get_mr_details INTO
1488 x_repetitive_flag,
1489 x_mr_type_code;
1490
1491 CLOSE get_mr_details;
1492
1493 END get_mr_header_details;
1494
1495 -- Procedure to default the old value of the Threshold Date
1496 PROCEDURE default_threshold_attributes
1497 (
1498 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type
1499 )
1500 IS
1501
1502 CURSOR get_old_rec( c_mr_effectivity_id NUMBER )
1503 IS
1504 SELECT threshold_date,
1505 program_duration,
1506 program_duration_uom_code
1507 FROM AHL_MR_EFFECTIVITIES_APP_V
1508 WHERE mr_effectivity_id = c_mr_effectivity_id;
1509
1510 l_old_rec threshold_rec_type;
1511
1512 BEGIN
1513
1514 OPEN get_old_rec( p_x_threshold_rec.mr_effectivity_id );
1515
1516 FETCH get_old_rec INTO
1517 l_old_rec.threshold_date,
1518 l_old_rec.program_duration,
1519 l_old_rec.program_duration_uom_code;
1520
1521 CLOSE get_old_rec;
1522
1523 IF ( p_x_threshold_rec.threshold_date = FND_API.G_MISS_DATE ) THEN
1524 p_x_threshold_rec.threshold_date := null;
1525 ELSIF ( p_x_threshold_rec.threshold_date IS NULL ) THEN
1526 p_x_threshold_rec.threshold_date := l_old_rec.threshold_date;
1527 END IF;
1528
1529 IF ( p_x_threshold_rec.program_duration = FND_API.G_MISS_NUM ) THEN
1530 p_x_threshold_rec.program_duration := null;
1531 ELSIF ( p_x_threshold_rec.program_duration IS NULL ) THEN
1532 p_x_threshold_rec.program_duration := l_old_rec.program_duration;
1533 END IF;
1534
1535 IF ( p_x_threshold_rec.program_duration_uom_code = FND_API.G_MISS_CHAR ) THEN
1536 p_x_threshold_rec.program_duration_uom_code := null;
1537 ELSIF ( p_x_threshold_rec.program_duration_uom_code IS NULL ) THEN
1538 p_x_threshold_rec.program_duration_uom_code := l_old_rec.program_duration_uom_code;
1539 END IF;
1540
1541 END default_threshold_attributes;
1542
1543 -- Procedure to validate and update the Threshold information
1544 PROCEDURE update_threshold
1545 (
1546 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type,
1547 p_repetitive_flag IN VARCHAR2,
1548 p_appln_code IN VARCHAR2,
1549 p_mr_type_code IN VARCHAR2,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 p_super_user IN VARCHAR2,
1552 p_mr_header_id IN NUMBER
1553 )
1554 IS
1555
1556 l_return_status VARCHAR2(1);
1557 l_msg_data VARCHAR2(2000);
1558 l_old_threshold_rec threshold_rec_type;
1559 l_mr_status_code VARCHAR2(30);
1560 l_pm_install_flag VARCHAR2(1);
1561
1562 BEGIN
1563 x_return_status := FND_API.G_RET_STS_SUCCESS;
1564
1565 -- Default unchanged value of Threshold attributes
1566 default_threshold_attributes
1567 (
1568 p_x_threshold_rec -- IN OUT
1569 );
1570
1571 -- Check if the Threshold date is not set for repeating MRs.
1572 IF ( p_x_threshold_rec.threshold_date IS NOT NULL AND
1573 p_repetitive_flag = 'Y' ) THEN
1574 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_THRESHOLD' );
1575 FND_MSG_PUB.add;
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 RETURN;
1578 END IF;
1579
1580 -- Check if the Threshold date is not set for PM Programs
1581 IF ( p_x_threshold_rec.threshold_date IS NOT NULL AND
1582 p_appln_code= 'PM' AND
1583 p_mr_type_code = 'PROGRAM' ) THEN
1584 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_THRESHOLD' );
1585 FND_MSG_PUB.add;
1586 x_return_status := FND_API.G_RET_STS_ERROR;
1587 RETURN;
1588 END IF;
1589
1590 -- Check if the Program Duration is not specified for PM Activities.
1591 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1592 p_appln_code = 'PM' AND
1593 p_mr_type_code = 'ACTIVITY' ) THEN
1594 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_ACT_PGM_DURATION' );
1595 FND_MSG_PUB.add;
1596 x_return_status := FND_API.G_RET_STS_ERROR;
1597 RETURN;
1598 END IF;
1599
1600 -- Check if the Program Duration is a positive number.
1601 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1602 p_x_threshold_rec.program_duration <= 0 ) THEN
1603 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_DUR_INVALID' );
1604 FND_MSG_PUB.add;
1605 x_return_status := FND_API.G_RET_STS_ERROR;
1606 RETURN;
1607 END IF;
1608
1609 -- Check if the Program Duration is specified then the UOM should be specified.
1610 IF ( p_x_threshold_rec.program_duration IS NOT NULL AND
1611 p_x_threshold_rec.program_duration_uom_code IS NULL ) THEN
1612 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_PGM_DUR_UOM_NULL' );
1613 FND_MSG_PUB.add;
1614 x_return_status := FND_API.G_RET_STS_ERROR;
1615 RETURN;
1616 END IF;
1617
1618 -- Check if the Program Duration UOM is a valid value.
1619 IF ( p_x_threshold_rec.program_duration_uom_code IS NOT NULL ) THEN
1620
1621 AHL_FMP_COMMON_PVT.validate_lookup
1622 (
1623 x_return_status => l_return_status,
1624 x_msg_data => l_msg_data,
1625 p_lookup_type => 'AHL_UMP_TIME_UOM',
1626 p_lookup_meaning => NULL,
1627 p_x_lookup_code => p_x_threshold_rec.program_duration_uom_code
1628 );
1629
1630 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1631 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_INV_PGM_DUR_UOM' );
1632 FND_MSG_PUB.add;
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634 RETURN;
1635 END IF;
1636 END IF;
1637
1638 -- Check Profile value
1639
1640 IF G_APPLN_USAGE IS NULL
1641 THEN
1642 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
1643 FND_MSG_PUB.ADD;
1644 x_return_status := FND_API.G_RET_STS_ERROR;
1645 IF G_DEBUG = 'Y' THEN
1646 AHL_DEBUG_PUB.debug('APPLN USAGE CODE IS NULL IN VALIDATE_API_INPUTS' );
1647 END IF;
1648 END IF;
1649
1650
1651 IF ( G_APPLN_USAGE = 'PM' ) THEN
1652 l_pm_install_flag:= 'Y';
1653 ELSE
1654 l_pm_install_flag:= 'N';
1655 END IF;
1656
1657 -- Update effectivity only if changed attributes.
1658 IF ((p_x_threshold_rec.program_duration IS NULL AND
1659 l_old_threshold_rec.program_duration IS NOT NULL) OR
1660 (p_x_threshold_rec.program_duration IS NOT NULL AND
1661 l_old_threshold_rec.program_duration IS NOT NULL AND
1662 l_old_threshold_rec.program_duration <> p_x_threshold_rec.program_duration) OR
1663 (p_x_threshold_rec.program_duration IS NOT NULL AND
1664 l_old_threshold_rec.program_duration IS NULL) OR
1665
1666 (p_x_threshold_rec.program_duration_uom_code IS NULL AND
1667 l_old_threshold_rec.program_duration_uom_code IS NOT NULL) OR
1668 (p_x_threshold_rec.program_duration_uom_code IS NOT NULL AND
1669 l_old_threshold_rec.program_duration_uom_code IS NOT NULL AND
1670 l_old_threshold_rec.program_duration_uom_code <> p_x_threshold_rec.program_duration_uom_code) OR
1671 (p_x_threshold_rec.program_duration_uom_code IS NOT NULL AND
1672 l_old_threshold_rec.program_duration_uom_code IS NULL) OR
1673
1674 (p_x_threshold_rec.threshold_date IS NULL AND
1675 l_old_threshold_rec.threshold_date IS NOT NULL) OR
1676 (p_x_threshold_rec.threshold_date IS NOT NULL AND
1677 l_old_threshold_rec.threshold_date IS NOT NULL AND
1678 l_old_threshold_rec.threshold_date <> p_x_threshold_rec.threshold_date) OR
1679 (p_x_threshold_rec.threshold_date IS NOT NULL AND
1680 l_old_threshold_rec.threshold_date IS NULL) ) THEN
1681
1682 l_mr_status_code :=AHL_FMP_COMMON_PVT.check_mr_status(p_mr_header_id);
1683 IF ( l_pm_install_flag = 'Y' AND
1684 p_super_user = 'Y' AND
1685 l_mr_status_code = 'COMPLETE' AND
1686 p_mr_type_code ='PROGRAM' ) THEN
1687 AHL_FMP_COMMON_PVT.validate_mr_type_program
1688 (
1689 x_return_status => l_return_status,
1690 x_msg_data => l_msg_data,
1691 p_mr_header_id => p_mr_header_id,
1692 p_effectivity_id => p_x_threshold_rec.MR_EFFECTIVITY_ID,
1693 p_eff_obj_version => p_x_threshold_rec.OBJECT_VERSION_NUMBER
1694 );
1695 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1696 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PROGRAM_DURATION' );
1697 FND_MSG_PUB.add;
1698 x_return_status := FND_API.G_RET_STS_ERROR;
1699 RETURN;
1700 END IF;
1701 END IF;
1702
1703 IF ( l_pm_install_flag = 'Y' AND
1704 p_super_user = 'Y' AND
1705 l_mr_status_code = 'COMPLETE' AND
1706 p_mr_type_code ='ACTIVITY' ) THEN
1707 AHL_FMP_COMMON_PVT.validate_mr_type_activity
1708 (
1709 x_return_status => l_return_status,
1710 x_msg_data => l_msg_data,
1711 p_effectivity_id => p_x_threshold_rec.MR_EFFECTIVITY_ID,
1712 p_eff_obj_version => p_x_threshold_rec.OBJECT_VERSION_NUMBER
1713 );
1714 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1715 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PROGRAM_DURATION' );
1716 FND_MSG_PUB.add;
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 RETURN;
1719 END IF;
1720 END IF;
1721
1722
1723 -- Update the record
1724 UPDATE AHL_MR_EFFECTIVITIES SET
1725 object_version_number = object_version_number + 1,
1726 threshold_date = p_x_threshold_rec.threshold_date,
1727 program_duration = p_x_threshold_rec.program_duration,
1728 program_duration_uom_code = p_x_threshold_rec.program_duration_uom_code,
1729 last_update_date = SYSDATE,
1730 last_updated_by = FND_GLOBAL.user_id,
1731 last_update_login = FND_GLOBAL.login_id
1732 WHERE mr_effectivity_id = p_x_threshold_rec.mr_effectivity_id
1733 AND object_version_number = p_x_threshold_rec.object_version_number;
1734
1735 -- If the record does not exist, then, abort API.
1736 IF ( SQL%ROWCOUNT = 0 ) THEN
1737 FND_MESSAGE.set_name('AHL','AHL_FMP_EFF_RECORD_CHANGED');
1738 FND_MSG_PUB.add;
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 RETURN;
1741 END IF;
1742
1743 END IF;
1744
1745 END update_threshold;
1746
1747 PROCEDURE process_interval
1748 (
1749 p_api_version IN NUMBER := '1.0',
1750 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1751 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1752 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1753 p_default IN VARCHAR2 := FND_API.G_FALSE,
1754 p_module_type IN VARCHAR2 := NULL,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2,
1758 p_x_interval_tbl IN OUT NOCOPY interval_tbl_type,
1759 p_x_threshold_rec IN OUT NOCOPY threshold_rec_type,
1760 p_mr_header_id IN NUMBER,
1761 p_super_user IN VARCHAR2
1762 )
1763 IS
1764 l_api_version CONSTANT NUMBER := 1.0;
1765 l_return_status VARCHAR2(1);
1766 l_msg_count NUMBER;
1767 l_mr_interval_id NUMBER;
1768
1769 l_fractional_trunc_flag BOOLEAN := FALSE;
1770
1771 BEGIN
1772 -- Initialize API return status to success
1773 x_return_status := FND_API.G_RET_STS_SUCCESS;
1774
1775 -- Standard Start of API savepoint
1776 SAVEPOINT process_interval_PVT;
1777
1778 -- Standard call to check for call compatibility.
1779 IF NOT FND_API.compatible_api_call
1780 (
1781 l_api_version,
1782 p_api_version,
1783 G_API_NAME,
1784 G_PKG_NAME
1785 )
1786 THEN
1787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1788 END IF;
1789
1790 -- Initialize message list if p_init_msg_list is set to TRUE.
1791 IF FND_API.to_boolean( p_init_msg_list ) THEN
1792 FND_MSG_PUB.initialize;
1793 END IF;
1794
1795 -- Enable Debug (optional)
1796 IF ( G_DEBUG = 'Y' ) THEN
1797 AHL_DEBUG_PUB.enable_debug;
1798 END IF;
1799
1800 IF G_DEBUG = 'Y' THEN
1801 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
1802 END IF;
1803
1804 -- Get the PM Installation Flag
1805
1806 IF G_DEBUG = 'Y' THEN
1807 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Application Installed : ' || G_APPLN_USAGE );
1808 END IF;
1809
1810 -- Validate all the inputs of the API
1811 validate_api_inputs
1812 (
1813 p_x_interval_tbl, -- IN
1814 p_mr_header_id, -- IN
1815 p_x_threshold_rec, -- IN
1816 l_return_status, -- OUT
1817 p_super_user
1818 );
1819
1820 -- If any severe error occurs, then, abort API.
1821 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1822 RAISE FND_API.G_EXC_ERROR;
1823 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825 END IF;
1826
1827 -- Get the Details for the given MR
1828 get_mr_header_details
1829 (
1830 p_mr_header_id, -- IN
1831 g_repetitive_flag, -- OUT
1832 g_mr_type_code -- OUT
1833 );
1834
1835 -- Validate and Update the Threshold information
1836 update_threshold
1837 (
1838 p_x_threshold_rec, -- IN
1839 g_repetitive_flag, -- IN
1840 G_APPLN_USAGE, -- IN
1841 g_mr_type_code,
1842 l_return_status, -- OUT
1843 p_super_user,
1844 p_mr_header_id
1845
1846 );
1847
1848 -- If any severe error occurs, then, abort API.
1849 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1850 RAISE FND_API.G_EXC_ERROR;
1851 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1853 END IF;
1854
1855 -- If the module type is JSP, then default values for ID columns of LOV attributes
1856 IF ( p_module_type = 'JSP' ) THEN
1857 FOR i IN 1..p_x_interval_tbl.count LOOP
1858 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1859 clear_lov_attribute_ids
1860 (
1861 p_x_interval_tbl(i) -- IN OUT Record with Values and Ids
1862 );
1863 END IF;
1864 END LOOP;
1865 END IF;
1866
1867 -- Convert Values into Ids.
1868 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1869 FOR i IN 1..p_x_interval_tbl.count LOOP
1870 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1871 convert_values_to_ids
1872 (
1873 p_x_interval_tbl(i), -- IN OUT Record with Values and Ids
1874 p_x_threshold_rec.mr_effectivity_id, -- IN
1875 l_return_status -- OUT
1876 );
1877
1878 -- If any severe error occurs, then, abort API.
1879 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1880 RAISE FND_API.G_EXC_ERROR;
1881 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1883 END IF;
1884 END IF;
1885 END LOOP;
1886 END IF;
1887
1888 IF G_DEBUG = 'Y' THEN
1889 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1890 END IF;
1891
1892 -- Default interval attributes.
1893 FOR i IN 1..p_x_interval_tbl.count LOOP
1894 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1895 default_attributes
1896 (
1897 p_x_interval_tbl(i), -- IN OUT
1898 l_fractional_trunc_flag -- OUT
1899 );
1900 END IF;
1901 END LOOP;
1902
1903 IF G_DEBUG = 'Y' THEN
1904 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1905 END IF;
1906
1907 -- Validate all attributes (Item level validation)
1908 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1909 FOR i IN 1..p_x_interval_tbl.count LOOP
1910 validate_attributes
1911 (
1912 p_x_interval_tbl(i), -- IN
1913 l_return_status -- OUT
1914 );
1915
1916 -- If any severe error occurs, then, abort API.
1917 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1918 RAISE FND_API.G_EXC_ERROR;
1919 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END IF;
1922 END LOOP;
1923 END IF;
1924
1925 IF G_DEBUG = 'Y' THEN
1926 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1927 END IF;
1928
1929 -- Default missing and unchanged attributes.
1930 FOR i IN 1..p_x_interval_tbl.count LOOP
1931 IF ( p_x_interval_tbl(i).dml_operation = 'U' ) THEN
1932 default_unchanged_attributes
1933 (
1934 p_x_interval_tbl(i) -- IN OUT
1935 );
1936 ELSIF ( p_x_interval_tbl(i).dml_operation = 'C' ) THEN
1937 default_missing_attributes
1938 (
1939 p_x_interval_tbl(i) -- IN OUT
1940 );
1941 END IF;
1942 END LOOP;
1943
1944 IF G_DEBUG = 'Y' THEN
1945 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1946 END IF;
1947
1948 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1949 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1950 FOR i IN 1..p_x_interval_tbl.count LOOP
1951 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1952 validate_record
1953 (
1954 p_x_interval_tbl(i), -- IN
1955 g_repetitive_flag, -- IN
1956 G_APPLN_USAGE, -- IN
1957 g_mr_type_code, -- IN
1958 l_return_status -- OUT
1959 );
1960
1961 -- If any severe error occurs, then, abort API.
1962 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1963 RAISE FND_API.G_EXC_ERROR;
1964 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1966 END IF;
1967 END IF;
1968 END LOOP;
1969 END IF;
1970
1971 IF G_DEBUG = 'Y' THEN
1972 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
1973 END IF;
1974
1975 --pdoki added for ADAT ER start.
1976 FOR i IN 1..p_x_interval_tbl.count LOOP
1977 IF ( p_x_interval_tbl(i).dml_operation <> 'D' ) THEN
1978 IF (p_x_interval_tbl(i).start_date IS NOT NULL OR p_x_interval_tbl(i).start_value IS NOT NULL) THEN
1979 IF (p_x_interval_tbl(i).duedate_rule_code IS NOT NULL) THEN
1980 AHL_RM_ROUTE_UTIL.validate_lookup(
1981 x_return_status => x_return_status,
1982 x_msg_data => x_msg_data,
1983 p_lookup_type => 'AHL_DUEDATE_RULE',
1984 p_lookup_meaning => p_x_interval_tbl(i).duedate_rule_meaning,
1985 p_x_lookup_code => p_x_interval_tbl(i).duedate_rule_code
1986 );
1987 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1988 RAISE FND_API.G_EXC_ERROR;
1989 END IF;
1990 END IF;
1991 ELSE
1992 p_x_interval_tbl(i).duedate_rule_code := NULL;
1993 END IF;
1994 END IF;
1995 END LOOP;
1996 --pdoki added for ADAT ER end.
1997
1998 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1999 l_msg_count := FND_MSG_PUB.count_msg;
2000 IF l_msg_count > 0 THEN
2001 x_msg_count := l_msg_count;
2002 RAISE FND_API.G_EXC_ERROR;
2003 END IF;
2004
2005 -- Perform the DML statement directly.
2006 FOR i IN 1..p_x_interval_tbl.count LOOP
2007 IF ( p_x_interval_tbl(i).dml_operation = 'C' ) THEN
2008
2009 -- Insert the record
2010 INSERT INTO AHL_MR_INTERVALS
2011 (
2012 MR_INTERVAL_ID,
2013 OBJECT_VERSION_NUMBER,
2014 MR_EFFECTIVITY_ID,
2015 COUNTER_ID,
2016 INTERVAL_VALUE,
2017 EARLIEST_DUE_VALUE,
2018 START_VALUE,
2019 STOP_VALUE,
2020 START_DATE,
2021 STOP_DATE,
2022 TOLERANCE_BEFORE,
2023 TOLERANCE_AFTER,
2024 RESET_VALUE,
2025 ATTRIBUTE_CATEGORY,
2026 ATTRIBUTE1,
2027 ATTRIBUTE2,
2028 ATTRIBUTE3,
2029 ATTRIBUTE4,
2030 ATTRIBUTE5,
2031 ATTRIBUTE6,
2032 ATTRIBUTE7,
2033 ATTRIBUTE8,
2034 ATTRIBUTE9,
2035 ATTRIBUTE10,
2036 ATTRIBUTE11,
2037 ATTRIBUTE12,
2038 ATTRIBUTE13,
2039 ATTRIBUTE14,
2040 ATTRIBUTE15,
2041 LAST_UPDATE_DATE,
2042 LAST_UPDATED_BY,
2043 CREATION_DATE,
2044 CREATED_BY,
2045 LAST_UPDATE_LOGIN,
2046 CALC_DUEDATE_RULE_CODE --pdoki added for ADAT ER
2047 ) VALUES
2048 (
2049 AHL_MR_INTERVALS_S.NEXTVAL,
2050 p_x_interval_tbl(i).object_version_number,
2051 p_x_threshold_rec.mr_effectivity_id,
2052 p_x_interval_tbl(i).counter_id,
2053 p_x_interval_tbl(i).interval_value,
2054 p_x_interval_tbl(i).earliest_due_value,
2055 p_x_interval_tbl(i).start_value,
2056 p_x_interval_tbl(i).stop_value,
2057 p_x_interval_tbl(i).start_date,
2058 p_x_interval_tbl(i).stop_date,
2059 p_x_interval_tbl(i).tolerance_before,
2060 p_x_interval_tbl(i).tolerance_after,
2061 p_x_interval_tbl(i).reset_value,
2062 p_x_interval_tbl(i).attribute_category,
2063 p_x_interval_tbl(i).attribute1,
2064 p_x_interval_tbl(i).attribute2,
2065 p_x_interval_tbl(i).attribute3,
2066 p_x_interval_tbl(i).attribute4,
2067 p_x_interval_tbl(i).attribute5,
2068 p_x_interval_tbl(i).attribute6,
2069 p_x_interval_tbl(i).attribute7,
2070 p_x_interval_tbl(i).attribute8,
2071 p_x_interval_tbl(i).attribute9,
2072 p_x_interval_tbl(i).attribute10,
2073 p_x_interval_tbl(i).attribute11,
2074 p_x_interval_tbl(i).attribute12,
2075 p_x_interval_tbl(i).attribute13,
2076 p_x_interval_tbl(i).attribute14,
2077 p_x_interval_tbl(i).attribute15,
2078 p_x_interval_tbl(i).last_update_date,
2079 p_x_interval_tbl(i).last_updated_by,
2080 p_x_interval_tbl(i).creation_date,
2081 p_x_interval_tbl(i).created_by,
2082 p_x_interval_tbl(i).last_update_login,
2083 p_x_interval_tbl(i).duedate_rule_code --pdoki added for ADAT ER
2084 ) RETURNING mr_interval_id INTO l_mr_interval_id;
2085
2086 -- Set OUT values
2087 p_x_interval_tbl(i).mr_interval_id := l_mr_interval_id;
2088
2089 ELSIF ( p_x_interval_tbl(i).dml_operation = 'U' ) THEN
2090
2091 -- Update the record
2092 UPDATE AHL_MR_INTERVALS SET
2093 object_version_number = object_version_number + 1,
2094 counter_id = p_x_interval_tbl(i).counter_id,
2095 interval_value = p_x_interval_tbl(i).interval_value,
2096 earliest_due_value = p_x_interval_tbl(i).earliest_due_value,
2097 start_value = p_x_interval_tbl(i).start_value,
2098 stop_value = p_x_interval_tbl(i).stop_value,
2099 start_date = p_x_interval_tbl(i).start_date,
2100 stop_date = p_x_interval_tbl(i).stop_date,
2101 tolerance_before = p_x_interval_tbl(i).tolerance_before,
2102 tolerance_after = p_x_interval_tbl(i).tolerance_after,
2103 reset_value = p_x_interval_tbl(i).reset_value,
2104 attribute_category = p_x_interval_tbl(i).attribute_category,
2105 attribute1 = p_x_interval_tbl(i).attribute1,
2106 attribute2 = p_x_interval_tbl(i).attribute2,
2107 attribute3 = p_x_interval_tbl(i).attribute3,
2108 attribute4 = p_x_interval_tbl(i).attribute4,
2109 attribute5 = p_x_interval_tbl(i).attribute5,
2110 attribute6 = p_x_interval_tbl(i).attribute6,
2111 attribute7 = p_x_interval_tbl(i).attribute7,
2112 attribute8 = p_x_interval_tbl(i).attribute8,
2113 attribute9 = p_x_interval_tbl(i).attribute9,
2114 attribute10 = p_x_interval_tbl(i).attribute10,
2115 attribute11 = p_x_interval_tbl(i).attribute11,
2116 attribute12 = p_x_interval_tbl(i).attribute12,
2117 attribute13 = p_x_interval_tbl(i).attribute13,
2118 attribute14 = p_x_interval_tbl(i).attribute14,
2119 attribute15 = p_x_interval_tbl(i).attribute15,
2120 last_update_date = p_x_interval_tbl(i).last_update_date,
2121 last_updated_by = p_x_interval_tbl(i).last_updated_by,
2122 last_update_login = p_x_interval_tbl(i).last_update_login,
2123 calc_duedate_rule_code= p_x_interval_tbl(i).duedate_rule_code --pdoki added for ADAT ER
2124 WHERE mr_interval_id = p_x_interval_tbl(i).mr_interval_id
2125 AND object_version_number = p_x_interval_tbl(i).object_version_number;
2126
2127 -- If the record does not exist, then, abort API.
2128 IF ( SQL%ROWCOUNT = 0 ) THEN
2129 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2130 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_interval_tbl(i) ) );
2131 FND_MSG_PUB.add;
2132 RAISE FND_API.G_EXC_ERROR;
2133 END IF;
2134
2135 -- Set OUT values
2136 p_x_interval_tbl(i).object_version_number := p_x_interval_tbl(i).object_version_number + 1;
2137
2138 ELSIF ( p_x_interval_tbl(i).dml_operation = 'D' ) THEN
2139
2140 -- Delete the record
2141 DELETE AHL_MR_INTERVALS
2142 WHERE mr_interval_id = p_x_interval_tbl(i).mr_interval_id
2143 AND object_version_number = p_x_interval_tbl(i).object_version_number;
2144
2145 -- If the record does not exist, then, abort API.
2146 IF ( SQL%ROWCOUNT = 0 ) THEN
2147 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2148 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
2149 FND_MSG_PUB.add;
2150 RAISE FND_API.G_EXC_ERROR;
2151 END IF;
2152
2153 END IF;
2154 END LOOP;
2155
2156 IF G_DEBUG = 'Y' THEN
2157 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
2158 END IF;
2159
2160 -- Perform cross records validations and duplicate records check
2161 validate_records
2162 (
2163 p_x_threshold_rec.mr_effectivity_id, -- IN
2164 g_repetitive_flag, -- IN
2165 G_APPLN_USAGE, -- IN
2166 g_mr_type_code, -- IN
2167 l_return_status -- OUT
2168 );
2169
2170 -- If any severe error occurs, then, abort API.
2171 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2172 RAISE FND_API.G_EXC_ERROR;
2173 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2175 END IF;
2176
2177 IF G_DEBUG = 'Y' THEN
2178 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
2179 END IF;
2180
2181 -- Perform the Commit (if requested)
2182 IF FND_API.to_boolean( p_commit ) THEN
2183 COMMIT WORK;
2184 END IF;
2185
2186 -- Add informational message if any truncation of interval values occured.
2187 -- Fix for bug#3482307
2188 IF (l_fractional_trunc_flag) THEN
2189 FND_MESSAGE.Set_Name('AHL','AHL_FMP_FRACTION_TRUNC');
2190 FND_MSG_PUB.ADD;
2191 END IF;
2192
2193 -- Count and Get messages (optional)
2194 FND_MSG_PUB.count_and_get
2195 (
2196 p_encoded => FND_API.G_FALSE,
2197 p_count => x_msg_count,
2198 p_data => x_msg_data
2199 );
2200
2201 -- Disable debug (if enabled)
2202 IF ( G_DEBUG = 'Y' ) THEN
2203 AHL_DEBUG_PUB.disable_debug;
2204 END IF;
2205
2206 EXCEPTION
2207
2208 WHEN FND_API.G_EXC_ERROR THEN
2209 ROLLBACK TO process_interval_PVT;
2210 x_return_status := FND_API.G_RET_STS_ERROR ;
2211 FND_MSG_PUB.count_and_get
2212 (
2213 p_encoded => FND_API.G_FALSE,
2214 p_count => x_msg_count,
2215 p_data => x_msg_data
2216 );
2217
2218 -- Disable debug (if enabled)
2219 IF ( G_DEBUG = 'Y' ) THEN
2220 AHL_DEBUG_PUB.disable_debug;
2221 END IF;
2222
2223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2224 ROLLBACK TO process_interval_PVT;
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2226 FND_MSG_PUB.count_and_get
2227 (
2228 p_encoded => FND_API.G_FALSE,
2229 p_count => x_msg_count,
2230 p_data => x_msg_data
2231 );
2232
2233 -- Disable debug (if enabled)
2234 IF ( G_DEBUG = 'Y' ) THEN
2235 AHL_DEBUG_PUB.disable_debug;
2236 END IF;
2237
2238 WHEN OTHERS THEN
2239 ROLLBACK TO process_interval_PVT;
2240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2241 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2242 THEN
2243 FND_MSG_PUB.add_exc_msg
2244 (
2245 p_pkg_name => G_PKG_NAME,
2246 p_procedure_name => G_API_NAME,
2247 p_error_text => SUBSTRB(SQLERRM,1,240)
2248 );
2249 END IF;
2250 FND_MSG_PUB.count_and_get
2251 (
2252 p_encoded => FND_API.G_FALSE,
2253 p_count => x_msg_count,
2254 p_data => x_msg_data
2255 );
2256
2257 -- Disable debug (if enabled)
2258 IF ( G_DEBUG = 'Y' ) THEN
2259 AHL_DEBUG_PUB.disable_debug;
2260 END IF;
2261
2262 END process_interval;
2263
2264 END AHL_FMP_MR_INTERVAL_PVT;
[Home] [Help]