[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_EFFECTIVITY_DTL_PVT
Source
1 PACKAGE BODY AHL_FMP_EFFECTIVITY_DTL_PVT AS
2 /* $Header: AHLVMEDB.pls 120.0 2005/05/26 00:56:05 appldev noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_FMP_EFFECTIVITY_DTL_PVT';
5 G_API_NAME VARCHAR2(30) := 'PROCESS_EFFECTIVITY_DETAIL';
6 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
7 G_APPLN_USAGE VARCHAR2(30) :=LTRIM(RTRIM(FND_PROFILE.value('AHL_APPLN_USAGE')));
8
9
10 -- Function to get the Record Identifier for Error Messages
11 FUNCTION get_record_identifier
12 (
13 p_effectivity_detail_rec IN effectivity_detail_rec_type
14 ) RETURN VARCHAR2
15 IS
16
17 l_record_identifier VARCHAR2(2000) := '';
18
19 BEGIN
20
21 IF ( p_effectivity_detail_rec.serial_number_from IS NOT NULL AND
22 p_effectivity_detail_rec.serial_number_from <> FND_API.G_MISS_CHAR ) THEN
23 l_record_identifier := l_record_identifier || p_effectivity_detail_rec.serial_number_from;
24 END IF;
25
26 l_record_identifier := l_record_identifier || ' - ';
27
28 IF ( p_effectivity_detail_rec.serial_number_to IS NOT NULL AND
29 p_effectivity_detail_rec.serial_number_to <> FND_API.G_MISS_CHAR ) THEN
30 l_record_identifier := l_record_identifier || p_effectivity_detail_rec.serial_number_to;
31 END IF;
32
33 l_record_identifier := l_record_identifier || ' - ';
34
35 IF ( p_effectivity_detail_rec.manufacturer IS NOT NULL AND
36 p_effectivity_detail_rec.manufacturer <> FND_API.G_MISS_CHAR ) THEN
37 l_record_identifier := l_record_identifier || p_effectivity_detail_rec.manufacturer;
38 END IF;
39
40 l_record_identifier := l_record_identifier || ' - ';
41
42 IF ( p_effectivity_detail_rec.manufacture_date_from IS NOT NULL AND
43 p_effectivity_detail_rec.manufacture_date_from <> FND_API.G_MISS_DATE ) THEN
44 l_record_identifier := l_record_identifier || TO_CHAR( p_effectivity_detail_rec.manufacture_date_from, 'DD-MON-YYYY' );
45 END IF;
46
47 l_record_identifier := l_record_identifier || ' - ';
48
49 IF ( p_effectivity_detail_rec.manufacture_date_to IS NOT NULL AND
50 p_effectivity_detail_rec.manufacture_date_to <> FND_API.G_MISS_DATE ) THEN
51 l_record_identifier := l_record_identifier || TO_CHAR( p_effectivity_detail_rec.manufacture_date_to, 'DD-MON-YYYY' );
52 END IF;
53
54 l_record_identifier := l_record_identifier || ' - ';
55
56 IF ( p_effectivity_detail_rec.country IS NOT NULL AND
57 p_effectivity_detail_rec.country <> FND_API.G_MISS_CHAR ) THEN
58 l_record_identifier := l_record_identifier || p_effectivity_detail_rec.country;
59 END IF;
60
61 RETURN l_record_identifier;
62
63 END get_record_identifier;
64
65 -- Procedure to validate the Inputs of the API
66 PROCEDURE validate_api_inputs
67 (
68 p_effectivity_detail_tbl IN effectivity_detail_tbl_type,
69 p_mr_header_id IN NUMBER,
70 p_mr_effectivity_id IN NUMBER,
71 x_return_status OUT NOCOPY VARCHAR2
72 )
73 IS
74 l_return_status VARCHAR2(1);
75 l_msg_data VARCHAR2(2000);
76 l_appln_code VARCHAR2(30);
77 BEGIN
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 -- Check Profile value
81
82 IF G_APPLN_USAGE IS NULL
83 THEN
84 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
85 FND_MSG_PUB.ADD;
86 RETURN;
87 END IF;
88
89
90 IF ( G_APPLN_USAGE = 'PM' ) THEN
91 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_MED_PM_INSTALL' );
92 FND_MSG_PUB.add;
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 RETURN;
95 END IF;
96
97 -- Check if a valid value is passed in p_mr_header_id
98
99 IF ( p_mr_header_id = FND_API.G_MISS_NUM OR
100 p_mr_header_id IS NULL ) THEN
101 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MR_HEADER_ID_NULL' );
102 FND_MSG_PUB.add;
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 RETURN;
105 END IF;
106
107 -- Check if the Maintenance Requirement is in Updatable status
108 AHL_FMP_COMMON_PVT.validate_mr_status
109 (
110 x_return_status => l_return_status,
111 x_msg_data => l_msg_data,
112 p_mr_header_id => p_mr_header_id
113 );
114
115 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
116 FND_MESSAGE.set_name( 'AHL', l_msg_data );
117 FND_MSG_PUB.add;
118 x_return_status := l_return_status;
119 RETURN;
120 END IF;
121
122 -- Check if a valid value is passed in p_mr_effectivity_id
123 IF ( p_mr_effectivity_id = FND_API.G_MISS_NUM OR
124 p_mr_effectivity_id IS NULL ) THEN
125 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MRE_ID_NULL' );
126 FND_MSG_PUB.add;
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 RETURN;
129 END IF;
130
131 -- Check if the Maintenance Requirement Effectivity exists
132 AHL_FMP_COMMON_PVT.validate_mr_effectivity
133 (
134 x_return_status => l_return_status,
135 x_msg_data => l_msg_data,
136 p_mr_effectivity_id => p_mr_effectivity_id
137 );
138
139 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
140 FND_MESSAGE.set_name( 'AHL', l_msg_data );
141 FND_MSG_PUB.add;
142 x_return_status := l_return_status;
143 RETURN;
144 END IF;
145
146 -- Check if atleast one record is passed in p_effectivity_detail_tbl
147 IF ( p_effectivity_detail_tbl.count < 1 ) THEN
148 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
149 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
150 FND_MSG_PUB.add;
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 RETURN;
153 END IF;
154
155 -- Validate DML Operation
156 FOR i IN 1..p_effectivity_detail_tbl.count LOOP
157 IF ( p_effectivity_detail_tbl(i).dml_operation <> 'D' AND
158 p_effectivity_detail_tbl(i).dml_operation <> 'U' AND
159 p_effectivity_detail_tbl(i).dml_operation <> 'C' ) THEN
160 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DML_INVALID' );
161 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_tbl(i).dml_operation );
162 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_tbl(i) ) );
163 FND_MSG_PUB.add;
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 RETURN;
166 END IF;
167 END LOOP;
168
169 END validate_api_inputs;
170
171 -- Procedure to Default NULL / G_MISS Values for LOV attributes
172 PROCEDURE clear_lov_attribute_ids
173 (
174 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
175 )
176 IS
177
178 BEGIN
179
180 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL ) THEN
181 p_x_effectivity_detail_rec.manufacturer_id := NULL;
182 ELSIF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
183 p_x_effectivity_detail_rec.manufacturer_id := FND_API.G_MISS_NUM;
184 END IF;
185
186 IF ( p_x_effectivity_detail_rec.country IS NULL ) THEN
187 p_x_effectivity_detail_rec.country_code := NULL;
188 ELSIF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
189 p_x_effectivity_detail_rec.country_code := FND_API.G_MISS_CHAR;
190 END IF;
191
192 END clear_lov_attribute_ids;
193
194 -- Procedure to perform Value to ID conversion for appropriate attributes
195 PROCEDURE convert_values_to_ids
196 (
197 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type,
198 p_mr_effectivity_id IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2
200 )
201 IS
202
203 l_return_status VARCHAR2(1);
204 l_msg_data VARCHAR2(2000);
205 l_inventory_item_id NUMBER;
206 l_item_number VARCHAR2(40);
207 l_relationship_id NUMBER;
208 l_position_ref_meaning VARCHAR2(80);
209
210 CURSOR get_item_effectivity ( c_mr_effectivity_id NUMBER )
211 IS
212 SELECT DECODE( relationship_id, null,
213 inventory_item_id,
214 position_inventory_item_id ),
215 DECODE( relationship_id, null,
216 item_number,
217 position_item_number ),
218 relationship_id,
219 position_ref_meaning
220 FROM AHL_MR_EFFECTIVITIES_V
221 WHERE mr_effectivity_id = c_mr_effectivity_id;
222
223 BEGIN
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 -- Convert / Validate Manufacturer
227 IF ( ( p_x_effectivity_detail_rec.manufacturer_id IS NOT NULL AND
228 p_x_effectivity_detail_rec.manufacturer_id <> FND_API.G_MISS_NUM ) OR
229 ( p_x_effectivity_detail_rec.manufacturer IS NOT NULL AND
230 p_x_effectivity_detail_rec.manufacturer <> FND_API.G_MISS_CHAR ) ) THEN
231
232 OPEN get_item_effectivity( p_mr_effectivity_id );
233
234 FETCH get_item_effectivity INTO
235 l_inventory_item_id,
236 l_item_number,
237 l_relationship_id,
238 l_position_ref_meaning;
239
240 CLOSE get_item_effectivity;
241
242 AHL_FMP_COMMON_PVT.validate_manufacturer
243 (
244 x_return_status => l_return_status,
245 x_msg_data => l_msg_data,
246 p_inventory_item_id => l_inventory_item_id,
247 p_relationship_id => l_relationship_id,
248 p_manufacturer_name => p_x_effectivity_detail_rec.manufacturer,
249 p_x_manufacturer_id => p_x_effectivity_detail_rec.manufacturer_id
250 );
251
252 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
253 FND_MESSAGE.set_name( 'AHL', l_msg_data );
254
255 IF ( l_msg_data = 'AHL_FMP_INVALID_MF' OR
256 l_msg_data = 'AHL_FMP_TOO_MANY_MFS' ) THEN
257 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL OR
258 p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
259 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_detail_rec.manufacturer_id ));
260 ELSE
261 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.manufacturer );
262 END IF;
263 ELSE
264 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL OR
265 p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
266 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_x_effectivity_detail_rec.manufacturer_id ));
267 ELSE
268 FND_MESSAGE.set_token( 'FIELD1', p_x_effectivity_detail_rec.manufacturer );
269 END IF;
270
271 IF ( l_position_ref_meaning IS NOT NULL ) THEN
272 FND_MESSAGE.set_token( 'FIELD2', l_position_ref_meaning );
273 ELSE
274 FND_MESSAGE.set_token( 'FIELD2', l_item_number );
275 END IF;
276 END IF;
277
278 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
279 FND_MSG_PUB.add;
280 END IF;
281
282 END IF;
283
284 -- Convert / Validate Country
285 IF ( ( p_x_effectivity_detail_rec.country_code IS NOT NULL AND
286 p_x_effectivity_detail_rec.country_code <> FND_API.G_MISS_CHAR )
287 OR
288 ( p_x_effectivity_detail_rec.country IS NOT NULL AND
289 p_x_effectivity_detail_rec.country <> FND_API.G_MISS_CHAR ) )
290 THEN
291
292 AHL_FMP_COMMON_PVT.validate_country
293 (
294 x_return_status => l_return_status,
295 x_msg_data => l_msg_data,
296 p_country_name => p_x_effectivity_detail_rec.country,
297 p_x_country_code => p_x_effectivity_detail_rec.country_code
298 );
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 IF ( p_x_effectivity_detail_rec.country IS NULL OR
303 p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
304 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.country_code );
305 ELSE
306 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.country );
307 END IF;
308 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
309 FND_MSG_PUB.add;
310 END IF;
311
312 END IF;
313
314 END convert_values_to_ids;
315
316 -- Procedure to add Default values for effectivity_detail attributes
317 PROCEDURE default_attributes
318 (
319 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
320 )
321 IS
322
323 BEGIN
324
325 p_x_effectivity_detail_rec.last_update_date := SYSDATE;
326 p_x_effectivity_detail_rec.last_updated_by := FND_GLOBAL.user_id;
327 p_x_effectivity_detail_rec.last_update_login := FND_GLOBAL.login_id;
328
329 IF ( p_x_effectivity_detail_rec.dml_operation = 'C' ) THEN
330 p_x_effectivity_detail_rec.object_version_number := 1;
331 p_x_effectivity_detail_rec.creation_date := SYSDATE;
332 p_x_effectivity_detail_rec.created_by := FND_GLOBAL.user_id;
333 END IF;
334
335 END default_attributes;
336
337 -- Procedure to add Default values for missing attributes (CREATE)
338 PROCEDURE default_missing_attributes
339 (
340 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
341 )
342 IS
343
344 BEGIN
345
346 -- Convert G_MISS values to NULL
347 IF ( p_x_effectivity_detail_rec.serial_number_from = FND_API.G_MISS_CHAR ) THEN
348 p_x_effectivity_detail_rec.serial_number_from := null;
349 END IF;
350
351 IF ( p_x_effectivity_detail_rec.serial_number_to = FND_API.G_MISS_CHAR ) THEN
352 p_x_effectivity_detail_rec.serial_number_to := null;
353 END IF;
354
355 IF ( p_x_effectivity_detail_rec.manufacturer_id = FND_API.G_MISS_NUM ) THEN
356 p_x_effectivity_detail_rec.manufacturer_id := null;
357 END IF;
358
359 IF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
360 p_x_effectivity_detail_rec.manufacturer := null;
361 END IF;
362
363 IF ( p_x_effectivity_detail_rec.manufacture_date_from = FND_API.G_MISS_DATE ) THEN
364 p_x_effectivity_detail_rec.manufacture_date_from := null;
365 END IF;
366
367 IF ( p_x_effectivity_detail_rec.manufacture_date_to = FND_API.G_MISS_DATE ) THEN
368 p_x_effectivity_detail_rec.manufacture_date_to := null;
369 END IF;
370
371 IF ( p_x_effectivity_detail_rec.country_code = FND_API.G_MISS_CHAR ) THEN
372 p_x_effectivity_detail_rec.country_code := null;
373 END IF;
374
375 IF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
376 p_x_effectivity_detail_rec.country := null;
377 END IF;
378
379 IF ( p_x_effectivity_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
380 p_x_effectivity_detail_rec.attribute_category := null;
381 END IF;
382
383 IF ( p_x_effectivity_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
384 p_x_effectivity_detail_rec.attribute1 := null;
385 END IF;
386
387 IF ( p_x_effectivity_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
388 p_x_effectivity_detail_rec.attribute2 := null;
389 END IF;
390
391 IF ( p_x_effectivity_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
392 p_x_effectivity_detail_rec.attribute3 := null;
393 END IF;
394
395 IF ( p_x_effectivity_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
396 p_x_effectivity_detail_rec.attribute4 := null;
397 END IF;
398
399 IF ( p_x_effectivity_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
400 p_x_effectivity_detail_rec.attribute5 := null;
401 END IF;
402
403 IF ( p_x_effectivity_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
404 p_x_effectivity_detail_rec.attribute6 := null;
405 END IF;
406
407 IF ( p_x_effectivity_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
408 p_x_effectivity_detail_rec.attribute7 := null;
409 END IF;
410
411 IF ( p_x_effectivity_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
412 p_x_effectivity_detail_rec.attribute8 := null;
413 END IF;
414
415 IF ( p_x_effectivity_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
416 p_x_effectivity_detail_rec.attribute9 := null;
417 END IF;
418
419 IF ( p_x_effectivity_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
420 p_x_effectivity_detail_rec.attribute10 := null;
421 END IF;
422
423 IF ( p_x_effectivity_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
424 p_x_effectivity_detail_rec.attribute11 := null;
425 END IF;
426
427 IF ( p_x_effectivity_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
428 p_x_effectivity_detail_rec.attribute12 := null;
429 END IF;
430
431 IF ( p_x_effectivity_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
432 p_x_effectivity_detail_rec.attribute13 := null;
433 END IF;
434
435 IF ( p_x_effectivity_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
436 p_x_effectivity_detail_rec.attribute14 := null;
437 END IF;
438
439 IF ( p_x_effectivity_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
440 p_x_effectivity_detail_rec.attribute15 := null;
441 END IF;
442
443 END default_missing_attributes;
444
445 -- Procedure to add Default values for unchanged attributes (UPDATE)
446 PROCEDURE default_unchanged_attributes
447 (
448 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
449 )
450 IS
451
452 l_old_effectivity_detail_rec effectivity_detail_rec_type;
453
454 CURSOR get_old_rec ( c_mr_effectivity_detail_id NUMBER )
455 IS
456 SELECT exclude_flag,
457 serial_number_from,
458 serial_number_to,
459 manufacturer_id,
460 manufacturer,
461 manufacture_date_from,
462 manufacture_date_to,
463 country_code,
464 country,
465 attribute_category,
466 attribute1,
467 attribute2,
468 attribute3,
469 attribute4,
470 attribute5,
471 attribute6,
472 attribute7,
473 attribute8,
474 attribute9,
475 attribute10,
476 attribute11,
477 attribute12,
478 attribute13,
479 attribute14,
480 attribute15
481 FROM AHL_MR_EFFECTIVITY_DTLS_V
482 WHERE mr_effectivity_detail_id = c_mr_effectivity_detail_id;
483
484 BEGIN
485
486 -- Get the old record from AHL_MR_EFFECTIVITY_DTLS.
487 OPEN get_old_rec( p_x_effectivity_detail_rec.mr_effectivity_detail_id );
488
489 FETCH get_old_rec INTO
490 l_old_effectivity_detail_rec.exclude_flag,
491 l_old_effectivity_detail_rec.serial_number_from,
492 l_old_effectivity_detail_rec.serial_number_to,
493 l_old_effectivity_detail_rec.manufacturer_id,
494 l_old_effectivity_detail_rec.manufacturer,
495 l_old_effectivity_detail_rec.manufacture_date_from,
496 l_old_effectivity_detail_rec.manufacture_date_to,
497 l_old_effectivity_detail_rec.country_code,
498 l_old_effectivity_detail_rec.country,
499 l_old_effectivity_detail_rec.attribute_category,
500 l_old_effectivity_detail_rec.attribute1,
501 l_old_effectivity_detail_rec.attribute2,
502 l_old_effectivity_detail_rec.attribute3,
503 l_old_effectivity_detail_rec.attribute4,
504 l_old_effectivity_detail_rec.attribute5,
505 l_old_effectivity_detail_rec.attribute6,
506 l_old_effectivity_detail_rec.attribute7,
507 l_old_effectivity_detail_rec.attribute8,
508 l_old_effectivity_detail_rec.attribute9,
509 l_old_effectivity_detail_rec.attribute10,
510 l_old_effectivity_detail_rec.attribute11,
511 l_old_effectivity_detail_rec.attribute12,
512 l_old_effectivity_detail_rec.attribute13,
513 l_old_effectivity_detail_rec.attribute14,
514 l_old_effectivity_detail_rec.attribute15;
515
516 IF get_old_rec%NOTFOUND THEN
517 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_EFF_DTL_REC' );
518 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
519 FND_MSG_PUB.add;
520 CLOSE get_old_rec;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 CLOSE get_old_rec;
525
526 -- Convert G_MISS values to NULL and NULL values to Old values
527 IF ( p_x_effectivity_detail_rec.exclude_flag IS NULL ) THEN
528 p_x_effectivity_detail_rec.exclude_flag := l_old_effectivity_detail_rec.exclude_flag;
529 END IF;
530
531 IF ( p_x_effectivity_detail_rec.serial_number_from = FND_API.G_MISS_CHAR ) THEN
532 p_x_effectivity_detail_rec.serial_number_from := null;
533 ELSIF ( p_x_effectivity_detail_rec.serial_number_from IS NULL ) THEN
534 p_x_effectivity_detail_rec.serial_number_from := l_old_effectivity_detail_rec.serial_number_from;
535 END IF;
536
537 IF ( p_x_effectivity_detail_rec.serial_number_to = FND_API.G_MISS_CHAR ) THEN
538 p_x_effectivity_detail_rec.serial_number_to := null;
539 ELSIF ( p_x_effectivity_detail_rec.serial_number_to IS NULL ) THEN
540 p_x_effectivity_detail_rec.serial_number_to := l_old_effectivity_detail_rec.serial_number_to;
541 END IF;
542
543 IF ( p_x_effectivity_detail_rec.manufacturer_id = FND_API.G_MISS_NUM ) THEN
544 p_x_effectivity_detail_rec.manufacturer_id := null;
545 ELSIF ( p_x_effectivity_detail_rec.manufacturer_id IS NULL ) THEN
546 p_x_effectivity_detail_rec.manufacturer_id := l_old_effectivity_detail_rec.manufacturer_id;
547 END IF;
548
549 IF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
550 p_x_effectivity_detail_rec.manufacturer := null;
551 ELSIF ( p_x_effectivity_detail_rec.manufacturer IS NULL ) THEN
552 p_x_effectivity_detail_rec.manufacturer := l_old_effectivity_detail_rec.manufacturer;
553 END IF;
554
555 IF ( p_x_effectivity_detail_rec.manufacture_date_from = FND_API.G_MISS_DATE ) THEN
556 p_x_effectivity_detail_rec.manufacture_date_from := null;
557 ELSIF ( p_x_effectivity_detail_rec.manufacture_date_from IS NULL ) THEN
558 p_x_effectivity_detail_rec.manufacture_date_from := l_old_effectivity_detail_rec.manufacture_date_from;
559 END IF;
560
561 IF ( p_x_effectivity_detail_rec.manufacture_date_to = FND_API.G_MISS_DATE ) THEN
562 p_x_effectivity_detail_rec.manufacture_date_to := null;
563 ELSIF ( p_x_effectivity_detail_rec.manufacture_date_to IS NULL ) THEN
564 p_x_effectivity_detail_rec.manufacture_date_to := l_old_effectivity_detail_rec.manufacture_date_to;
565 END IF;
566
567 IF ( p_x_effectivity_detail_rec.country_code = FND_API.G_MISS_CHAR ) THEN
568 p_x_effectivity_detail_rec.country_code := null;
569 ELSIF ( p_x_effectivity_detail_rec.country_code IS NULL ) THEN
570 p_x_effectivity_detail_rec.country_code := l_old_effectivity_detail_rec.country_code;
571 END IF;
572
573 IF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
574 p_x_effectivity_detail_rec.country := null;
575 ELSIF ( p_x_effectivity_detail_rec.country IS NULL ) THEN
576 p_x_effectivity_detail_rec.country := l_old_effectivity_detail_rec.country;
577 END IF;
578
579 IF ( p_x_effectivity_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
580 p_x_effectivity_detail_rec.attribute_category := null;
581 ELSIF ( p_x_effectivity_detail_rec.attribute_category IS NULL ) THEN
582 p_x_effectivity_detail_rec.attribute_category := l_old_effectivity_detail_rec.attribute_category;
583 END IF;
584
585 IF ( p_x_effectivity_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
586 p_x_effectivity_detail_rec.attribute1 := null;
587 ELSIF ( p_x_effectivity_detail_rec.attribute1 IS NULL ) THEN
588 p_x_effectivity_detail_rec.attribute1 := l_old_effectivity_detail_rec.attribute1;
589 END IF;
590
591 IF ( p_x_effectivity_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
592 p_x_effectivity_detail_rec.attribute2 := null;
593 ELSIF ( p_x_effectivity_detail_rec.attribute2 IS NULL ) THEN
594 p_x_effectivity_detail_rec.attribute2 := l_old_effectivity_detail_rec.attribute2;
595 END IF;
596
597 IF ( p_x_effectivity_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
598 p_x_effectivity_detail_rec.attribute3 := null;
599 ELSIF ( p_x_effectivity_detail_rec.attribute3 IS NULL ) THEN
600 p_x_effectivity_detail_rec.attribute3 := l_old_effectivity_detail_rec.attribute3;
601 END IF;
602
603 IF ( p_x_effectivity_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
604 p_x_effectivity_detail_rec.attribute4 := null;
605 ELSIF ( p_x_effectivity_detail_rec.attribute4 IS NULL ) THEN
606 p_x_effectivity_detail_rec.attribute4 := l_old_effectivity_detail_rec.attribute4;
607 END IF;
608
609 IF ( p_x_effectivity_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
610 p_x_effectivity_detail_rec.attribute5 := null;
611 ELSIF ( p_x_effectivity_detail_rec.attribute5 IS NULL ) THEN
612 p_x_effectivity_detail_rec.attribute5 := l_old_effectivity_detail_rec.attribute5;
613 END IF;
614
615 IF ( p_x_effectivity_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
616 p_x_effectivity_detail_rec.attribute6 := null;
617 ELSIF ( p_x_effectivity_detail_rec.attribute6 IS NULL ) THEN
618 p_x_effectivity_detail_rec.attribute6 := l_old_effectivity_detail_rec.attribute6;
619 END IF;
620
621 IF ( p_x_effectivity_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
622 p_x_effectivity_detail_rec.attribute7 := null;
623 ELSIF ( p_x_effectivity_detail_rec.attribute7 IS NULL ) THEN
624 p_x_effectivity_detail_rec.attribute7 := l_old_effectivity_detail_rec.attribute7;
625 END IF;
626
627 IF ( p_x_effectivity_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
628 p_x_effectivity_detail_rec.attribute8 := null;
629 ELSIF ( p_x_effectivity_detail_rec.attribute8 IS NULL ) THEN
630 p_x_effectivity_detail_rec.attribute8 := l_old_effectivity_detail_rec.attribute8;
631 END IF;
632
633 IF ( p_x_effectivity_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
634 p_x_effectivity_detail_rec.attribute9 := null;
635 ELSIF ( p_x_effectivity_detail_rec.attribute9 IS NULL ) THEN
636 p_x_effectivity_detail_rec.attribute9 := l_old_effectivity_detail_rec.attribute9;
637 END IF;
638
639 IF ( p_x_effectivity_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
640 p_x_effectivity_detail_rec.attribute10 := null;
641 ELSIF ( p_x_effectivity_detail_rec.attribute10 IS NULL ) THEN
642 p_x_effectivity_detail_rec.attribute10 := l_old_effectivity_detail_rec.attribute10;
643 END IF;
644
645 IF ( p_x_effectivity_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
646 p_x_effectivity_detail_rec.attribute11 := null;
647 ELSIF ( p_x_effectivity_detail_rec.attribute11 IS NULL ) THEN
648 p_x_effectivity_detail_rec.attribute11 := l_old_effectivity_detail_rec.attribute11;
649 END IF;
650
651 IF ( p_x_effectivity_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
652 p_x_effectivity_detail_rec.attribute12 := null;
653 ELSIF ( p_x_effectivity_detail_rec.attribute12 IS NULL ) THEN
654 p_x_effectivity_detail_rec.attribute12 := l_old_effectivity_detail_rec.attribute12;
655 END IF;
656
657 IF ( p_x_effectivity_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
658 p_x_effectivity_detail_rec.attribute13 := null;
659 ELSIF ( p_x_effectivity_detail_rec.attribute13 IS NULL ) THEN
660 p_x_effectivity_detail_rec.attribute13 := l_old_effectivity_detail_rec.attribute13;
661 END IF;
662
663 IF ( p_x_effectivity_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
664 p_x_effectivity_detail_rec.attribute14 := null;
665 ELSIF ( p_x_effectivity_detail_rec.attribute14 IS NULL ) THEN
666 p_x_effectivity_detail_rec.attribute14 := l_old_effectivity_detail_rec.attribute14;
667 END IF;
668
669 IF ( p_x_effectivity_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
670 p_x_effectivity_detail_rec.attribute15 := null;
671 ELSIF ( p_x_effectivity_detail_rec.attribute15 IS NULL ) THEN
672 p_x_effectivity_detail_rec.attribute15 := l_old_effectivity_detail_rec.attribute15;
673 END IF;
674
675 END default_unchanged_attributes;
676
677 -- Procedure to validate individual effectivity_detail attributes
678 PROCEDURE validate_attributes
679 (
680 p_effectivity_detail_rec IN effectivity_detail_rec_type,
681 x_return_status OUT NOCOPY VARCHAR2
682 )
683 IS
684
685 BEGIN
686 x_return_status := FND_API.G_RET_STS_SUCCESS;
687
688 IF ( p_effectivity_detail_rec.dml_operation = 'C' ) THEN
689 -- Check if the Exclude Flag does not contain a null value.
690 IF ( p_effectivity_detail_rec.exclude_flag IS NULL OR
691 p_effectivity_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
692 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
693 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
694 FND_MSG_PUB.add;
695 ELSE
696 -- Check if the Exclude Flag does not contain an invalid value.
697 IF ( p_effectivity_detail_rec.exclude_flag <> 'Y' AND
698 p_effectivity_detail_rec.exclude_flag <> 'N' ) THEN
699 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
700 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_rec.exclude_flag );
701 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
702 FND_MSG_PUB.add;
703 END IF;
704 END IF;
705 RETURN;
706 END IF;
707
708 IF ( p_effectivity_detail_rec.dml_operation = 'U' ) THEN
709 -- Check if the Exclude Flag does not contain a null value.
710 IF ( p_effectivity_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
711 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
712 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
713 FND_MSG_PUB.add;
714 ELSIF ( p_effectivity_detail_rec.exclude_flag IS NOT NULL ) THEN
715 -- Check if the Exclude Flag does not contain an invalid value.
716 IF ( p_effectivity_detail_rec.exclude_flag <> 'Y' AND
717 p_effectivity_detail_rec.exclude_flag <> 'N' ) THEN
718 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
719 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_rec.exclude_flag );
720 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
721 FND_MSG_PUB.add;
722 END IF;
723 END IF;
724 END IF;
725
726 -- Check if the mandatory Effectivity Detail ID column contains a null value.
727 IF ( p_effectivity_detail_rec.mr_effectivity_detail_id IS NULL OR
728 p_effectivity_detail_rec.mr_effectivity_detail_id = FND_API.G_MISS_NUM ) THEN
729 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MR_EFF_DTL_ID_NULL' );
730 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
731 FND_MSG_PUB.add;
732 END IF;
733
734 -- Check if the mandatory Object Version Number column contains a null value.
735 IF ( p_effectivity_detail_rec.object_version_number IS NULL OR
736 p_effectivity_detail_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
737 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MED_OBJ_VERSION_NULL' );
738 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
739 FND_MSG_PUB.add;
740 END IF;
741
742 END validate_attributes;
743
744 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
745 PROCEDURE validate_record
746 (
747 p_effectivity_detail_rec IN effectivity_detail_rec_type,
748 x_return_status OUT NOCOPY VARCHAR2
749 )
750 IS
751
752 l_return_status VARCHAR2(1);
753 l_msg_data VARCHAR2(2000);
754
755 BEGIN
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 -- Check if Serial Number Range is valid
759 IF ( p_effectivity_detail_rec.serial_number_from IS NOT NULL AND
760 p_effectivity_detail_rec.serial_number_to IS NOT NULL ) THEN
761
762 AHL_FMP_COMMON_PVT.validate_serial_numbers_range
763 (
764 x_return_status => l_return_status,
765 x_msg_data => l_msg_data,
766 p_serial_number_from => p_effectivity_detail_rec.serial_number_from,
767 p_serial_number_to => p_effectivity_detail_rec.serial_number_to
768 );
769
770 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
771 FND_MESSAGE.set_name( 'AHL', l_msg_data );
772 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_detail_rec.serial_number_from );
773 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_detail_rec.serial_number_to );
774 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
775 FND_MSG_PUB.add;
776 END IF;
777 END IF;
778
779 -- Check if Manufacture date range is valid
780 IF ( p_effectivity_detail_rec.manufacture_date_from IS NOT NULL AND
781 p_effectivity_detail_rec.manufacture_date_to IS NOT NULL ) THEN
782 IF ( p_effectivity_detail_rec.manufacture_date_from >
783 p_effectivity_detail_rec.manufacture_date_to ) THEN
784 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_MFG_DT_RANGE' );
785 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_detail_rec.manufacture_date_from );
786 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_detail_rec.manufacture_date_to );
787 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
788 FND_MSG_PUB.add;
789 END IF;
790 END IF;
791
792 -- Check if atleast one value is passed in the record
793 IF ( p_effectivity_detail_rec.serial_number_from IS NULL AND
794 p_effectivity_detail_rec.serial_number_to IS NULL AND
795 p_effectivity_detail_rec.manufacturer_id IS NULL AND
796 p_effectivity_detail_rec.manufacturer IS NULL AND
797 p_effectivity_detail_rec.manufacture_date_from IS NULL AND
798 p_effectivity_detail_rec.manufacture_date_to IS NULL AND
799 p_effectivity_detail_rec.country IS NULL AND
800 p_effectivity_detail_rec.country_code IS NULL ) THEN
801 FND_MESSAGE.set_name( 'AHL','AHL_FMP_ONE_VALUE_REQD' );
802 FND_MSG_PUB.add;
803 END IF;
804
805 END validate_record;
806
807 -- Procedure to Perform cross records validation and duplicate checks
808 PROCEDURE validate_records
809 (
810 p_mr_effectivity_id IN NUMBER,
811 x_return_status OUT NOCOPY VARCHAR2
812 )
813 IS
814
815 l_effectivity_detail_rec effectivity_detail_rec_type;
816
817 CURSOR get_dup_rec ( c_mr_effectivity_id NUMBER )
818 IS
819 SELECT serial_number_from,
820 serial_number_to,
821 manufacturer,
822 manufacture_date_from,
823 manufacture_date_to,
824 country
825 FROM AHL_MR_EFFECTIVITY_DTLS_V
826 WHERE mr_effectivity_id = c_mr_effectivity_id
827 GROUP BY serial_number_from,
828 serial_number_to,
829 manufacturer,
830 manufacture_date_from,
831 manufacture_date_to,
832 country
833 HAVING count(*) > 1;
834
835 BEGIN
836 x_return_status := FND_API.G_RET_STS_SUCCESS;
837
838 -- Check whether any duplicate effectivity_detail records exist
839 OPEN get_dup_rec( p_mr_effectivity_id );
840
841 LOOP
842 FETCH get_dup_rec INTO
843 l_effectivity_detail_rec.serial_number_from,
844 l_effectivity_detail_rec.serial_number_to,
845 l_effectivity_detail_rec.manufacturer,
846 l_effectivity_detail_rec.manufacture_date_from,
847 l_effectivity_detail_rec.manufacture_date_to,
848 l_effectivity_detail_rec.country;
849
850 EXIT WHEN get_dup_rec%NOTFOUND;
851
852 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUPLICATE_MED_REC' );
853 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_effectivity_detail_rec ) );
854 FND_MSG_PUB.add;
855 END LOOP;
856
857 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
858 CLOSE get_dup_rec;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 RETURN;
861 END IF;
862
863 CLOSE get_dup_rec;
864
865 END validate_records;
866
867 PROCEDURE process_effectivity_detail
868 (
869 p_api_version IN NUMBER := '1.0',
870 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
871 p_commit IN VARCHAR2 := FND_API.G_FALSE,
872 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
873 p_default IN VARCHAR2 := FND_API.G_FALSE,
874 p_module_type IN VARCHAR2 := NULL,
875 x_return_status OUT NOCOPY VARCHAR2,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2,
878 p_x_effectivity_detail_tbl IN OUT NOCOPY effectivity_detail_tbl_type,
879 p_mr_header_id IN NUMBER,
880 p_mr_effectivity_id IN NUMBER
881 )
882
883 IS
884
885 CURSOR get_all_effc_info ( c_mr_effectivity_id NUMBER )
886 IS
887 SELECT serial_number_from,
888 serial_number_to,
889 MR_EFFECTIVITY_DETAIL_ID
890 FROM AHL_MR_EFFECTIVITY_DTLS_V
891 WHERE mr_effectivity_id = c_mr_effectivity_id;
892
893
894 Cursor find_min_max_serials ( c_mr_effectivity_id NUMBER )
895 IS
896 select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
897 from csi_item_instances CSI,
898 AHL_MR_EFFECTIVITIES EFF
899 where
900 EFF.MR_EFFECTIVITY_ID = c_mr_effectivity_id and
901 CSI.inventory_item_id = EFF.inventory_item_id
902 UNION
903 select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
904 from csi_item_instances CSI,
905 ahl_position_alternates_v PA,
906 AHL_MR_EFFECTIVITIES EFF
907 where
908 EFF.MR_EFFECTIVITY_ID = c_mr_effectivity_id and
909 EFF.RELATIONSHIP_ID = PA.relationship_id and
910 CSI.inventory_item_id = PA.inventory_item_id;
911
912
913 l_get_eff_info get_all_effc_info%ROWTYPE;
914
915 l_api_version CONSTANT NUMBER := 1.0;
916 l_return_status VARCHAR2(1);
917 l_msg_count NUMBER;
918 l_mr_effectivity_detail_id NUMBER;
919
920 l_min_serial VARCHAR2(30);
921 l_max_serial VARCHAR2(30);
922
923 x VARCHAR2(30);
924 y VARCHAR2(30);
925 xi VARCHAR2(30);
926 yi VARCHAR2(30);
927
928
929 BEGIN
930 -- Initialize API return status to success
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932
933 -- Standard Start of API savepoint
934 SAVEPOINT process_effectivity_detail_PVT;
935
936 -- Standard call to check for call compatibility.
937 IF NOT FND_API.compatible_api_call
938 (
939 l_api_version,
940 p_api_version,
941 G_API_NAME,
942 G_PKG_NAME
943 )
944 THEN
945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
946 END IF;
947
948 -- Initialize message list if p_init_msg_list is set to TRUE.
949 IF FND_API.to_boolean( p_init_msg_list ) THEN
950 FND_MSG_PUB.initialize;
951 END IF;
952
953 -- Enable Debug (optional)
954 IF ( G_DEBUG = 'Y' ) THEN
955 AHL_DEBUG_PUB.enable_debug;
956 END IF;
957
958 IF G_DEBUG = 'Y' THEN
959 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
960 END IF;
961
962 -- Validate all the inputs of the API
963 validate_api_inputs
964 (
965 p_x_effectivity_detail_tbl, -- IN
966 p_mr_header_id, -- IN
967 p_mr_effectivity_id, -- IN
968 l_return_status -- OUT
969 );
970
971 -- If any severe error occurs, then, abort API.
972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
973 RAISE FND_API.G_EXC_ERROR;
974 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 -- If the module type is JSP, then default values for ID columns of LOV attributes
979 IF ( p_module_type = 'JSP' ) THEN
980 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
981 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
982 clear_lov_attribute_ids
983 (
984 p_x_effectivity_detail_tbl(i) -- IN OUT Record with Values and Ids
985 );
986 END IF;
987 END LOOP;
988 END IF;
989
990 -- Convert Values into Ids.
991 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
992 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
993 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
994 convert_values_to_ids
995 (
996 p_x_effectivity_detail_tbl(i), -- IN OUT Record with Values and Ids
997 p_mr_effectivity_id, -- IN
998 l_return_status -- OUT
999 );
1000
1001 -- If any severe error occurs, then, abort API.
1002 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1003 RAISE FND_API.G_EXC_ERROR;
1004 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006 END IF;
1007 END IF;
1008 END LOOP;
1009 END IF;
1010
1011 IF G_DEBUG = 'Y' THEN
1012 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1013 END IF;
1014
1015 -- Default effectivity_detail attributes.
1016 IF FND_API.to_boolean( p_default ) THEN
1017 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1018 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1019 default_attributes
1020 (
1021 p_x_effectivity_detail_tbl(i) -- IN OUT
1022 );
1023 END IF;
1024 END LOOP;
1025 END IF;
1026
1027 IF G_DEBUG = 'Y' THEN
1028 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1029 END IF;
1030
1031 -- Validate all attributes (Item level validation)
1032 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1033 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1034 validate_attributes
1035 (
1036 p_x_effectivity_detail_tbl(i), -- IN
1037 l_return_status -- OUT
1038 );
1039
1040 -- If any severe error occurs, then, abort API.
1041 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1042 RAISE FND_API.G_EXC_ERROR;
1043 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1045 END IF;
1046 END LOOP;
1047 END IF;
1048
1049 IF G_DEBUG = 'Y' THEN
1050 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1051 END IF;
1052
1053 -- Default missing and unchanged attributes.
1054 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1055 IF ( p_x_effectivity_detail_tbl(i).dml_operation = 'U' ) THEN
1056 default_unchanged_attributes
1057 (
1058 p_x_effectivity_detail_tbl(i) -- IN OUT
1059 );
1060 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'C' ) THEN
1061 default_missing_attributes
1062 (
1063 p_x_effectivity_detail_tbl(i) -- IN OUT
1064 );
1065 END IF;
1066 END LOOP;
1067
1068 IF G_DEBUG = 'Y' THEN
1069 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1070 END IF;
1071
1072 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1073 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1074 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1075 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1076 validate_record
1077 (
1078 p_x_effectivity_detail_tbl(i), -- IN
1079 l_return_status -- OUT
1080 );
1081
1082 -- If any severe error occurs, then, abort API.
1083 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1084 RAISE FND_API.G_EXC_ERROR;
1085 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1087 END IF;
1088 END IF;
1089 END LOOP;
1090 END IF;
1091
1092 IF G_DEBUG = 'Y' THEN
1093 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
1094 END IF;
1095
1096 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1097 l_msg_count := FND_MSG_PUB.count_msg;
1098 IF l_msg_count > 0 THEN
1099 x_msg_count := l_msg_count;
1100 RAISE FND_API.G_EXC_ERROR;
1101 END IF;
1102
1103 -- Perform the DML statement directly.
1104 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1105 IF ( p_x_effectivity_detail_tbl(i).dml_operation = 'C' ) THEN
1106
1107 -- Insert the record
1108 INSERT INTO AHL_MR_EFFECTIVITY_DTLS
1109 (
1110 MR_EFFECTIVITY_DETAIL_ID,
1111 OBJECT_VERSION_NUMBER,
1112 MR_EFFECTIVITY_ID,
1113 EXCLUDE_FLAG,
1114 SERIAL_NUMBER_FROM,
1115 SERIAL_NUMBER_TO,
1116 MANUFACTURER_ID,
1117 MANUFACTURE_DATE_FROM,
1118 MANUFACTURE_DATE_TO,
1119 COUNTRY_CODE,
1120 ATTRIBUTE_CATEGORY,
1121 ATTRIBUTE1,
1122 ATTRIBUTE2,
1123 ATTRIBUTE3,
1124 ATTRIBUTE4,
1125 ATTRIBUTE5,
1126 ATTRIBUTE6,
1127 ATTRIBUTE7,
1128 ATTRIBUTE8,
1129 ATTRIBUTE9,
1130 ATTRIBUTE10,
1131 ATTRIBUTE11,
1132 ATTRIBUTE12,
1133 ATTRIBUTE13,
1134 ATTRIBUTE14,
1135 ATTRIBUTE15,
1136 LAST_UPDATE_DATE,
1137 LAST_UPDATED_BY,
1138 CREATION_DATE,
1139 CREATED_BY,
1140 LAST_UPDATE_LOGIN
1141 ) VALUES
1142 (
1143 AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
1144 p_x_effectivity_detail_tbl(i).object_version_number,
1145 p_mr_effectivity_id,
1146 p_x_effectivity_detail_tbl(i).exclude_flag,
1147 p_x_effectivity_detail_tbl(i).serial_number_from,
1148 p_x_effectivity_detail_tbl(i).serial_number_to,
1149 p_x_effectivity_detail_tbl(i).manufacturer_id,
1150 p_x_effectivity_detail_tbl(i).manufacture_date_from,
1151 p_x_effectivity_detail_tbl(i).manufacture_date_to,
1152 p_x_effectivity_detail_tbl(i).country_code,
1153 p_x_effectivity_detail_tbl(i).attribute_category,
1154 p_x_effectivity_detail_tbl(i).attribute1,
1155 p_x_effectivity_detail_tbl(i).attribute2,
1156 p_x_effectivity_detail_tbl(i).attribute3,
1157 p_x_effectivity_detail_tbl(i).attribute4,
1158 p_x_effectivity_detail_tbl(i).attribute5,
1159 p_x_effectivity_detail_tbl(i).attribute6,
1160 p_x_effectivity_detail_tbl(i).attribute7,
1161 p_x_effectivity_detail_tbl(i).attribute8,
1162 p_x_effectivity_detail_tbl(i).attribute9,
1163 p_x_effectivity_detail_tbl(i).attribute10,
1164 p_x_effectivity_detail_tbl(i).attribute11,
1165 p_x_effectivity_detail_tbl(i).attribute12,
1166 p_x_effectivity_detail_tbl(i).attribute13,
1167 p_x_effectivity_detail_tbl(i).attribute14,
1168 p_x_effectivity_detail_tbl(i).attribute15,
1169 p_x_effectivity_detail_tbl(i).last_update_date,
1170 p_x_effectivity_detail_tbl(i).last_updated_by,
1171 p_x_effectivity_detail_tbl(i).creation_date,
1172 p_x_effectivity_detail_tbl(i).created_by,
1173 p_x_effectivity_detail_tbl(i).last_update_login
1174 ) RETURNING mr_effectivity_detail_id INTO l_mr_effectivity_detail_id;
1175
1176 -- Set OUT values
1177 p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id := l_mr_effectivity_detail_id;
1178
1179 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'U' ) THEN
1180
1181 -- Update the record
1182 UPDATE AHL_MR_EFFECTIVITY_DTLS SET
1183 object_version_number = object_version_number + 1,
1184 exclude_flag = p_x_effectivity_detail_tbl(i).exclude_flag,
1185 serial_number_from = p_x_effectivity_detail_tbl(i).serial_number_from,
1186 serial_number_to = p_x_effectivity_detail_tbl(i).serial_number_to,
1187 manufacturer_id = p_x_effectivity_detail_tbl(i).manufacturer_id,
1188 manufacture_date_from = p_x_effectivity_detail_tbl(i).manufacture_date_from,
1189 manufacture_date_to = p_x_effectivity_detail_tbl(i).manufacture_date_to,
1190 country_code = p_x_effectivity_detail_tbl(i).country_code,
1191 attribute_category = p_x_effectivity_detail_tbl(i).attribute_category,
1192 attribute1 = p_x_effectivity_detail_tbl(i).attribute1,
1193 attribute2 = p_x_effectivity_detail_tbl(i).attribute2,
1194 attribute3 = p_x_effectivity_detail_tbl(i).attribute3,
1195 attribute4 = p_x_effectivity_detail_tbl(i).attribute4,
1196 attribute5 = p_x_effectivity_detail_tbl(i).attribute5,
1197 attribute6 = p_x_effectivity_detail_tbl(i).attribute6,
1198 attribute7 = p_x_effectivity_detail_tbl(i).attribute7,
1199 attribute8 = p_x_effectivity_detail_tbl(i).attribute8,
1200 attribute9 = p_x_effectivity_detail_tbl(i).attribute9,
1201 attribute10 = p_x_effectivity_detail_tbl(i).attribute10,
1202 attribute11 = p_x_effectivity_detail_tbl(i).attribute11,
1203 attribute12 = p_x_effectivity_detail_tbl(i).attribute12,
1204 attribute13 = p_x_effectivity_detail_tbl(i).attribute13,
1205 attribute14 = p_x_effectivity_detail_tbl(i).attribute14,
1206 attribute15 = p_x_effectivity_detail_tbl(i).attribute15,
1207 last_update_date = p_x_effectivity_detail_tbl(i).last_update_date,
1208 last_updated_by = p_x_effectivity_detail_tbl(i).last_updated_by,
1209 last_update_login = p_x_effectivity_detail_tbl(i).last_update_login
1210 WHERE mr_effectivity_detail_id = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
1211 AND object_version_number = p_x_effectivity_detail_tbl(i).object_version_number;
1212
1213 -- If the record does not exist, then, abort API.
1214 IF ( SQL%ROWCOUNT = 0 ) THEN
1215 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
1216 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_tbl(i) ) );
1217 FND_MSG_PUB.add;
1218 RAISE FND_API.G_EXC_ERROR;
1219 END IF;
1220
1221 -- Set OUT values
1222 p_x_effectivity_detail_tbl(i).object_version_number := p_x_effectivity_detail_tbl(i).object_version_number + 1;
1223
1224 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'D' ) THEN
1225
1226 -- Delete the record
1227 DELETE AHL_MR_EFFECTIVITY_DTLS
1228 WHERE mr_effectivity_detail_id = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
1229 AND object_version_number = p_x_effectivity_detail_tbl(i).object_version_number;
1230
1231 -- If the record does not exist, then, abort API.
1232 IF ( SQL%ROWCOUNT = 0 ) THEN
1233 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
1234 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1235 FND_MSG_PUB.add;
1236 RAISE FND_API.G_EXC_ERROR;
1237 END IF;
1238 END IF;
1239
1240
1241
1242 END LOOP;
1243
1244 IF G_DEBUG = 'Y' THEN
1245 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1246 END IF;
1247
1248
1249 -- this check can only be done once all records are created/updated/deleted.
1250
1251 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1252 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1253
1254
1255 OPEN find_min_max_serials ( p_mr_effectivity_id );
1256 FETCH find_min_max_serials INTO l_min_serial , l_max_serial;
1257 CLOSE find_min_max_serials;
1258
1259 OPEN get_all_effc_info( p_mr_effectivity_id );
1260
1261 LOOP
1262 FETCH get_all_effc_info INTO
1263 l_get_eff_info.serial_number_from,
1264 l_get_eff_info.serial_number_to,
1265 l_get_eff_info.MR_EFFECTIVITY_DETAIL_ID ;
1266
1267 EXIT WHEN get_all_effc_info%NOTFOUND;
1268
1269
1270 xi := NVL( p_x_effectivity_detail_tbl(i).serial_number_from , l_min_serial);
1271 yi := NVL( p_x_effectivity_detail_tbl(i).serial_number_to , l_max_serial);
1272 x := NVL( l_get_eff_info.serial_number_from , l_min_serial);
1273 y := NVL( l_get_eff_info.serial_number_to , l_max_serial);
1274
1275
1276 IF(l_get_eff_info.MR_EFFECTIVITY_DETAIL_ID <> p_x_effectivity_detail_tbl(i).MR_EFFECTIVITY_DETAIL_ID) THEN
1277
1278 IF(
1279 (
1280 ( xi >= x ) AND
1281 ( xi <= y )
1282 )
1283 OR
1284 (
1285 ( yi >= x ) AND
1286 ( yi <= y )
1287 )
1288 OR
1289 (
1290 ( xi < x ) AND
1291 ( yi > y )
1292 )
1293 )
1294 THEN
1295 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_MED_REC' );
1296 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_tbl(i) ) );
1297 FND_MSG_PUB.add;
1298 RAISE FND_API.G_EXC_ERROR;
1299 END IF;
1300
1301 END IF;
1302
1303 END LOOP;
1304
1305 CLOSE get_all_effc_info;
1306
1307 END IF;
1308
1309 END LOOP;
1310
1311
1312 -- Perform cross records validations and duplicate records check
1313 validate_records
1314 (
1315 p_mr_effectivity_id, -- IN
1316 l_return_status -- OUT
1317 );
1318
1319 -- If any severe error occurs, then, abort API.
1320 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1321 RAISE FND_API.G_EXC_ERROR;
1322 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1324 END IF;
1325
1326 IF G_DEBUG = 'Y' THEN
1327 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
1328 END IF;
1329
1330 -- Perform the Commit (if requested)
1331 IF FND_API.to_boolean( p_commit ) THEN
1332 COMMIT WORK;
1333 END IF;
1334
1335 -- Count and Get messages (optional)
1336 FND_MSG_PUB.count_and_get
1337 (
1338 p_encoded => FND_API.G_FALSE,
1339 p_count => x_msg_count,
1340 p_data => x_msg_data
1341 );
1342
1343 -- Disable debug (if enabled)
1344 IF ( G_DEBUG = 'Y' ) THEN
1345 AHL_DEBUG_PUB.disable_debug;
1346 END IF;
1347
1348 EXCEPTION
1349
1350 WHEN FND_API.G_EXC_ERROR THEN
1351 ROLLBACK TO process_effectivity_detail_PVT;
1352 x_return_status := FND_API.G_RET_STS_ERROR ;
1353 FND_MSG_PUB.count_and_get
1354 (
1355 p_encoded => FND_API.G_FALSE,
1356 p_count => x_msg_count,
1357 p_data => x_msg_data
1358 );
1359
1360 -- Disable debug (if enabled)
1361 IF ( G_DEBUG = 'Y' ) THEN
1362 AHL_DEBUG_PUB.disable_debug;
1363 END IF;
1364
1365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1366 ROLLBACK TO process_effectivity_detail_PVT;
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1368 FND_MSG_PUB.count_and_get
1369 (
1370 p_encoded => FND_API.G_FALSE,
1371 p_count => x_msg_count,
1372 p_data => x_msg_data
1373 );
1374
1375 -- Disable debug (if enabled)
1376 IF ( G_DEBUG = 'Y' ) THEN
1377 AHL_DEBUG_PUB.disable_debug;
1378 END IF;
1379
1380 WHEN OTHERS THEN
1381 ROLLBACK TO process_effectivity_detail_PVT;
1382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1383 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1384 THEN
1385 FND_MSG_PUB.add_exc_msg
1386 (
1387 p_pkg_name => G_PKG_NAME,
1388 p_procedure_name => G_API_NAME,
1389 p_error_text => SUBSTRB(SQLERRM,1,240)
1390 );
1391 END IF;
1392 FND_MSG_PUB.count_and_get
1393 (
1394 p_encoded => FND_API.G_FALSE,
1395 p_count => x_msg_count,
1396 p_data => x_msg_data
1397 );
1398
1399 -- Disable debug (if enabled)
1400 IF ( G_DEBUG = 'Y' ) THEN
1401 AHL_DEBUG_PUB.disable_debug;
1402 END IF;
1403
1404 END process_effectivity_detail;
1405
1406 END AHL_FMP_EFFECTIVITY_DTL_PVT;