[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.3.12020000.2 2012/12/10 14:03:13 shnatu ship $ */
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 -- Function to get the Record Identifier for Error Messages
66 FUNCTION get_ext_record_identifier
67 (
68 p_effty_ext_detail_rec IN effty_ext_detail_rec_type
69 ) RETURN VARCHAR2
70 IS
71
72 l_record_identifier VARCHAR2(2000) := '';
73
74 BEGIN
75 IF ( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'OWNER' )THEN
76 IF ( p_effty_ext_detail_rec.owner IS NOT NULL AND
77 p_effty_ext_detail_rec.owner <> FND_API.G_MISS_CHAR ) THEN
78 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.owner;
79 END IF;
80 ELSIF( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'LOCATION' ) THEN
81 IF ( p_effty_ext_detail_rec.location IS NOT NULL AND
82 p_effty_ext_detail_rec.location <> FND_API.G_MISS_CHAR ) THEN
83 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.location;
84 END IF;
85 ELSIF( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR' ) THEN
86 IF ( p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NOT NULL AND
87 p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE <> FND_API.G_MISS_CHAR ) THEN
88 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE;
89 END IF;
90 l_record_identifier := l_record_identifier || ' - ';
91 /*IF ( p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NOT NULL AND
92 p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME <> FND_API.G_MISS_CHAR ) THEN
93 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME;
94 END IF;
95 l_record_identifier := l_record_identifier || ' - ';*/
96 IF ( p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE IS NOT NULL AND
97 p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE <> FND_API.G_MISS_CHAR ) THEN
98 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE;
99 END IF;
100 ELSE
101 IF ( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE IS NOT NULL AND
102 p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE <> FND_API.G_MISS_CHAR ) THEN
103 l_record_identifier := l_record_identifier || p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE;
104 END IF;
105 END IF;
106
107 RETURN l_record_identifier;
108
109 END get_ext_record_identifier;
110
111 -- Procedure to validate the Inputs of the API
112 PROCEDURE validate_api_inputs
113 (
114 p_effectivity_detail_tbl IN effectivity_detail_tbl_type,
115 p_effty_ext_detail_tbl IN effty_ext_detail_tbl_type,
116 p_mr_header_id IN NUMBER,
117 p_mr_effectivity_id IN NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2
119 )
120 IS
121 l_return_status VARCHAR2(1);
122 l_msg_data VARCHAR2(2000);
123 l_appln_code VARCHAR2(30);
124 BEGIN
125 x_return_status := FND_API.G_RET_STS_SUCCESS;
126
127 -- Check Profile value
128
129 IF G_APPLN_USAGE IS NULL THEN
130 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
131 FND_MSG_PUB.ADD;
132 RETURN;
133 END IF;
134
135
136 IF ( G_APPLN_USAGE = 'PM' ) THEN
137 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_MED_PM_INSTALL' );
138 FND_MSG_PUB.add;
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 RETURN;
141 END IF;
142
143 -- Check if a valid value is passed in p_mr_header_id
144
145 IF ( p_mr_header_id = FND_API.G_MISS_NUM OR
146 p_mr_header_id IS NULL ) THEN
147 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MR_HEADER_ID_NULL' );
148 FND_MSG_PUB.add;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 RETURN;
151 END IF;
152
153 -- Check if the Maintenance Requirement is in Updatable status
154 AHL_FMP_COMMON_PVT.validate_mr_status
155 (
156 x_return_status => l_return_status,
157 x_msg_data => l_msg_data,
158 p_mr_header_id => p_mr_header_id
159 );
160
161 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
162 FND_MESSAGE.set_name( 'AHL', l_msg_data );
163 FND_MSG_PUB.add;
164 x_return_status := l_return_status;
165 RETURN;
166 END IF;
167
168 -- Check if a valid value is passed in p_mr_effectivity_id
169 IF ( p_mr_effectivity_id = FND_API.G_MISS_NUM OR
170 p_mr_effectivity_id IS NULL ) THEN
171 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MRE_ID_NULL' );
172 FND_MSG_PUB.add;
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 RETURN;
175 END IF;
176
177 -- Check if the Maintenance Requirement Effectivity exists
178 AHL_FMP_COMMON_PVT.validate_mr_effectivity
179 (
180 x_return_status => l_return_status,
181 x_msg_data => l_msg_data,
182 p_mr_effectivity_id => p_mr_effectivity_id
183 );
184
185 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
186 FND_MESSAGE.set_name( 'AHL', l_msg_data );
187 FND_MSG_PUB.add;
188 x_return_status := l_return_status;
189 RETURN;
190 END IF;
191
192 -- Check if atleast one record is passed in p_effectivity_detail_tbl
193 IF ( p_effectivity_detail_tbl.count < 1 AND p_effty_ext_detail_tbl.count < 1 ) THEN
194 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
195 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
196 FND_MSG_PUB.add;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 RETURN;
199 END IF;
200
201 -- Validate DML Operation
202 FOR i IN 1..p_effectivity_detail_tbl.count LOOP
203 IF ( p_effectivity_detail_tbl(i).dml_operation <> 'D' AND
204 p_effectivity_detail_tbl(i).dml_operation <> 'U' AND
205 p_effectivity_detail_tbl(i).dml_operation <> 'C' ) THEN
206 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DML_INVALID' );
207 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_tbl(i).dml_operation );
208 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_tbl(i) ) );
209 FND_MSG_PUB.add;
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 RETURN;
212 END IF;
213 END LOOP;
214
215 -- Validate DML Operation for extended details
216 FOR i IN 1..p_effty_ext_detail_tbl.count LOOP
217 IF ( p_effty_ext_detail_tbl(i).dml_operation <> 'D' AND
218 p_effty_ext_detail_tbl(i).dml_operation <> 'U' AND
219 p_effty_ext_detail_tbl(i).dml_operation <> 'C' ) THEN
220 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DML_INVALID' );
221 FND_MESSAGE.set_token( 'FIELD', p_effty_ext_detail_tbl(i).dml_operation );
222 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_tbl(i) ) );
223 FND_MSG_PUB.add;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 RETURN;
226 END IF;
227 IF ( p_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE <> 'OWNER' AND
228 p_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE <> 'LOCATION' AND
229 p_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE <> 'CSIEXTATTR' ) THEN
230 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DML_INVALID' );
231 FND_MESSAGE.set_token( 'FIELD', p_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE );
232 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_tbl(i) ) );
233 FND_MSG_PUB.add;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 RETURN;
236 END IF;
237 END LOOP;
238
239 END validate_api_inputs;
240
241 -- Procedure to Default NULL / G_MISS Values for LOV attributes
242 PROCEDURE clear_lov_attribute_ids
243 (
244 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
245 )
246 IS
247
248 BEGIN
249
250 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL ) THEN
251 p_x_effectivity_detail_rec.manufacturer_id := NULL;
252 ELSIF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
253 p_x_effectivity_detail_rec.manufacturer_id := FND_API.G_MISS_NUM;
254 END IF;
255
256 IF ( p_x_effectivity_detail_rec.country IS NULL ) THEN
257 p_x_effectivity_detail_rec.country_code := NULL;
258 ELSIF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
259 p_x_effectivity_detail_rec.country_code := FND_API.G_MISS_CHAR;
260 END IF;
261
262 END clear_lov_attribute_ids;
263
264 -- Procedure to Default NULL / G_MISS Values for LOV attributes
265 PROCEDURE clear_ext_lov_attribute_ids
266 (
267 p_x_effty_ext_detail_rec IN OUT NOCOPY effty_ext_detail_rec_type
268 )
269 IS
270
271 BEGIN
272
273 IF ( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'OWNER' )THEN
274 IF ( p_x_effty_ext_detail_rec.owner IS NULL ) THEN
275 p_x_effty_ext_detail_rec.owner_id := NULL;
276 ELSIF ( p_x_effty_ext_detail_rec.owner = FND_API.G_MISS_CHAR ) THEN
277 p_x_effty_ext_detail_rec.owner_id := FND_API.G_MISS_NUM;
278 END IF;
279 ELSIF( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'LOCATION' ) THEN
280 IF ( p_x_effty_ext_detail_rec.location IS NULL ) THEN
281 p_x_effty_ext_detail_rec.location_type_code := NULL;
282 ELSIF ( p_x_effty_ext_detail_rec.location = FND_API.G_MISS_CHAR ) THEN
283 p_x_effty_ext_detail_rec.location_type_code := FND_API.G_MISS_CHAR;
284 END IF;
285 ELSIF( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR' ) THEN
286 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NULL )THEN
287 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME := NULL;
288 ELSIF (p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE = FND_API.G_MISS_CHAR)THEN
289 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME := FND_API.G_MISS_CHAR;
290 END IF;
291 END IF;
292
293
294 END clear_ext_lov_attribute_ids;
295
296 -- Procedure to perform Value to ID conversion for appropriate attributes
297 PROCEDURE convert_values_to_ids
298 (
299 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type,
300 p_mr_effectivity_id IN NUMBER,
301 x_return_status OUT NOCOPY VARCHAR2
302 )
303 IS
304
305 l_return_status VARCHAR2(1);
306 l_msg_data VARCHAR2(2000);
307 l_inventory_item_id NUMBER;
308 l_item_number VARCHAR2(40);
309 l_relationship_id NUMBER;
310 l_position_ref_meaning VARCHAR2(80);
311
312 CURSOR get_item_effectivity ( c_mr_effectivity_id NUMBER )
313 IS
314 SELECT DECODE( relationship_id, null,
315 inventory_item_id,
316 position_inventory_item_id ),
317 DECODE( relationship_id, null,
318 item_number,
319 position_item_number ),
320 relationship_id,
321 position_ref_meaning
322 FROM AHL_MR_EFFECTIVITIES_V
323 WHERE mr_effectivity_id = c_mr_effectivity_id;
324
325 BEGIN
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 -- Convert / Validate Manufacturer
329 IF ( ( p_x_effectivity_detail_rec.manufacturer_id IS NOT NULL AND
330 p_x_effectivity_detail_rec.manufacturer_id <> FND_API.G_MISS_NUM ) OR
331 ( p_x_effectivity_detail_rec.manufacturer IS NOT NULL AND
332 p_x_effectivity_detail_rec.manufacturer <> FND_API.G_MISS_CHAR ) ) THEN
333
334 OPEN get_item_effectivity( p_mr_effectivity_id );
335
336 FETCH get_item_effectivity INTO
337 l_inventory_item_id,
338 l_item_number,
339 l_relationship_id,
340 l_position_ref_meaning;
341
342 CLOSE get_item_effectivity;
343
344 AHL_FMP_COMMON_PVT.validate_manufacturer
345 (
346 x_return_status => l_return_status,
347 x_msg_data => l_msg_data,
348 p_inventory_item_id => l_inventory_item_id,
349 p_relationship_id => l_relationship_id,
350 p_manufacturer_name => p_x_effectivity_detail_rec.manufacturer,
351 p_x_manufacturer_id => p_x_effectivity_detail_rec.manufacturer_id
352 );
353
354 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
355 FND_MESSAGE.set_name( 'AHL', l_msg_data );
356
357 IF ( l_msg_data = 'AHL_FMP_INVALID_MF' OR
358 l_msg_data = 'AHL_FMP_TOO_MANY_MFS' ) THEN
359 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL OR
360 p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
361 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_detail_rec.manufacturer_id ));
362 ELSE
363 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.manufacturer );
364 END IF;
365 ELSE
366 IF ( p_x_effectivity_detail_rec.manufacturer IS NULL OR
367 p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
368 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_x_effectivity_detail_rec.manufacturer_id ));
369 ELSE
370 FND_MESSAGE.set_token( 'FIELD1', p_x_effectivity_detail_rec.manufacturer );
371 END IF;
372
373 IF ( l_position_ref_meaning IS NOT NULL ) THEN
374 FND_MESSAGE.set_token( 'FIELD2', l_position_ref_meaning );
375 ELSE
376 FND_MESSAGE.set_token( 'FIELD2', l_item_number );
377 END IF;
378 END IF;
379
380 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
381 FND_MSG_PUB.add;
382 END IF;
383
384 END IF;
385
386 -- Convert / Validate Country
387 IF ( ( p_x_effectivity_detail_rec.country_code IS NOT NULL AND
388 p_x_effectivity_detail_rec.country_code <> FND_API.G_MISS_CHAR )
389 OR
390 ( p_x_effectivity_detail_rec.country IS NOT NULL AND
391 p_x_effectivity_detail_rec.country <> FND_API.G_MISS_CHAR ) )
392 THEN
393
394 AHL_FMP_COMMON_PVT.validate_country
395 (
396 x_return_status => l_return_status,
397 x_msg_data => l_msg_data,
398 p_country_name => p_x_effectivity_detail_rec.country,
399 p_x_country_code => p_x_effectivity_detail_rec.country_code
400 );
401
402 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
403 FND_MESSAGE.set_name( 'AHL', l_msg_data );
404 IF ( p_x_effectivity_detail_rec.country IS NULL OR
405 p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
406 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.country_code );
407 ELSE
408 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_detail_rec.country );
409 END IF;
410 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
411 FND_MSG_PUB.add;
412 END IF;
413
414 END IF;
415
416 END convert_values_to_ids;
417
418 -- Procedure to perform Value to ID conversion for appropriate attributes
419 PROCEDURE convert_ext_values_to_ids
420 (
421 p_x_effty_ext_detail_rec IN OUT NOCOPY effty_ext_detail_rec_type,
422 p_mr_effectivity_id IN NUMBER,
423 x_return_status OUT NOCOPY VARCHAR2
424 )
425 IS
426 l_return_status VARCHAR2(1);
427 l_msg_data VARCHAR2(2000);
428 l_inventory_item_id NUMBER;
429 l_item_number VARCHAR2(40);
430 l_relationship_id NUMBER;
431 l_position_ref_meaning VARCHAR2(80);
432
433 CURSOR get_item_effectivity ( c_mr_effectivity_id NUMBER )
434 IS
435 SELECT DECODE( relationship_id, null,
436 inventory_item_id,
437 position_inventory_item_id ),
438 DECODE( relationship_id, null,
439 item_number,
440 position_item_number ),
441 relationship_id,
442 position_ref_meaning
443 FROM AHL_MR_EFFECTIVITIES_V
444 WHERE mr_effectivity_id = c_mr_effectivity_id;
445
446 BEGIN
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 -- Convert / Validate Manufacturer
450 IF ( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'OWNER' )THEN
451 IF ( ( p_x_effty_ext_detail_rec.owner_id IS NOT NULL AND
452 p_x_effty_ext_detail_rec.owner_id <> FND_API.G_MISS_NUM ) OR
453 ( p_x_effty_ext_detail_rec.owner IS NOT NULL AND
454 p_x_effty_ext_detail_rec.owner <> FND_API.G_MISS_CHAR ) ) THEN
455
456 OPEN get_item_effectivity( p_mr_effectivity_id );
457
458 FETCH get_item_effectivity INTO
459 l_inventory_item_id,
460 l_item_number,
461 l_relationship_id,
462 l_position_ref_meaning;
463
464 CLOSE get_item_effectivity;
465
466 AHL_FMP_COMMON_PVT.validate_owner
467 (
468 x_return_status => l_return_status,
469 x_msg_data => l_msg_data,
470 p_owner => p_x_effty_ext_detail_rec.owner,
471 p_x_owner_id => p_x_effty_ext_detail_rec.owner_id
472 );
473
474 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
475 FND_MESSAGE.set_name( 'AHL', l_msg_data );
476
477 IF ( l_msg_data = 'AHL_FMP_INVALID_OWNER' OR
478 l_msg_data = 'AHL_FMP_INV_TOO_MANY_OWNERS' ) THEN
479 IF ( p_x_effty_ext_detail_rec.owner IS NULL OR
480 p_x_effty_ext_detail_rec.owner = FND_API.G_MISS_CHAR ) THEN
481 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effty_ext_detail_rec.owner_id ));
482 ELSE
483 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.owner );
484 END IF;
485 ELSE
486 IF ( p_x_effty_ext_detail_rec.owner IS NULL OR
487 p_x_effty_ext_detail_rec.owner = FND_API.G_MISS_CHAR ) THEN
488 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effty_ext_detail_rec.owner_id ));
489 ELSE
490 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.owner );
491 END IF;
492 END IF;
493
494 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_x_effty_ext_detail_rec ) );
495 FND_MSG_PUB.add;
496 END IF;
497 END IF;
498 END IF;
499
500 -- Convert / Validate Location
501 IF( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'LOCATION' ) THEN
502 IF ( ( p_x_effty_ext_detail_rec.location_type_code IS NOT NULL AND
503 p_x_effty_ext_detail_rec.location_type_code <> FND_API.G_MISS_CHAR )
504 OR
505 ( p_x_effty_ext_detail_rec.location IS NOT NULL AND
506 p_x_effty_ext_detail_rec.location <> FND_API.G_MISS_CHAR ) )
507 THEN
508
509 AHL_FMP_COMMON_PVT.validate_location
510 (
511 x_return_status => l_return_status,
512 x_msg_data => l_msg_data,
513 p_location => p_x_effty_ext_detail_rec.location,
514 p_x_location_type_code => p_x_effty_ext_detail_rec.location_type_code
515 );
516
517 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
518 FND_MESSAGE.set_name( 'AHL', l_msg_data );
519 IF ( p_x_effty_ext_detail_rec.location IS NULL OR
520 p_x_effty_ext_detail_rec.location = FND_API.G_MISS_CHAR ) THEN
521 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.location_type_code );
522 ELSE
523 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.location );
524 END IF;
525 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_x_effty_ext_detail_rec ) );
526 FND_MSG_PUB.add;
527 END IF;
528 END IF;
529 END IF;
530
531 -- Convert / Validate Location
532 IF( p_x_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR' ) THEN
533 IF ( ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NOT NULL AND
534 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE <> FND_API.G_MISS_CHAR )
535 OR
536 ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NOT NULL AND
537 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME <> FND_API.G_MISS_CHAR ) )
538 THEN
539
540 AHL_FMP_COMMON_PVT.validate_csi_ext_attribute
541 (
542 x_return_status => l_return_status,
543 x_msg_data => l_msg_data,
544 p_csi_attribute_name => p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME,
545 p_x_csi_attribute_code => p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE
546 );
547
548 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
549 FND_MESSAGE.set_name( 'AHL', l_msg_data );
550 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NULL OR
551 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME = FND_API.G_MISS_CHAR ) THEN
552 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE );
553 ELSE
554 FND_MESSAGE.set_token( 'FIELD', p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME );
555 END IF;
556 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_x_effty_ext_detail_rec ) );
557 FND_MSG_PUB.add;
558 END IF;
559 END IF;
560 END IF;
561
562 END convert_ext_values_to_ids;
563
564 -- Procedure to add Default values for effectivity_detail attributes
565 PROCEDURE default_attributes
566 (
567 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
568 )
569 IS
570
571 BEGIN
572
573 p_x_effectivity_detail_rec.last_update_date := SYSDATE;
574 p_x_effectivity_detail_rec.last_updated_by := FND_GLOBAL.user_id;
575 p_x_effectivity_detail_rec.last_update_login := FND_GLOBAL.login_id;
576
577 IF ( p_x_effectivity_detail_rec.dml_operation = 'C' ) THEN
578 p_x_effectivity_detail_rec.object_version_number := 1;
579 p_x_effectivity_detail_rec.creation_date := SYSDATE;
580 p_x_effectivity_detail_rec.created_by := FND_GLOBAL.user_id;
581 END IF;
582
583 END default_attributes;
584
585 -- Procedure to add Default values for effectivity_detail attributes
586 PROCEDURE default_ext_attributes
587 (
588 p_x_effty_ext_detail_rec IN OUT NOCOPY effty_ext_detail_rec_type
589 )
590 IS
591
592 BEGIN
593
594 p_x_effty_ext_detail_rec.last_update_date := SYSDATE;
595 p_x_effty_ext_detail_rec.last_updated_by := FND_GLOBAL.user_id;
596 p_x_effty_ext_detail_rec.last_update_login := FND_GLOBAL.login_id;
597
598 IF ( p_x_effty_ext_detail_rec.dml_operation = 'C' ) THEN
599 p_x_effty_ext_detail_rec.object_version_number := 1;
600 p_x_effty_ext_detail_rec.creation_date := SYSDATE;
601 p_x_effty_ext_detail_rec.created_by := FND_GLOBAL.user_id;
602 END IF;
603
604 END default_ext_attributes;
605
606 -- Procedure to add Default values for missing attributes (CREATE)
607 PROCEDURE default_missing_attributes
608 (
609 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
610 )
611 IS
612
613 BEGIN
614
615 -- Convert G_MISS values to NULL
616 IF ( p_x_effectivity_detail_rec.serial_number_from = FND_API.G_MISS_CHAR ) THEN
617 p_x_effectivity_detail_rec.serial_number_from := null;
618 END IF;
619
620 IF ( p_x_effectivity_detail_rec.serial_number_to = FND_API.G_MISS_CHAR ) THEN
621 p_x_effectivity_detail_rec.serial_number_to := null;
622 END IF;
623
624 IF ( p_x_effectivity_detail_rec.manufacturer_id = FND_API.G_MISS_NUM ) THEN
625 p_x_effectivity_detail_rec.manufacturer_id := null;
626 END IF;
627
628 IF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
629 p_x_effectivity_detail_rec.manufacturer := null;
630 END IF;
631
632 IF ( p_x_effectivity_detail_rec.manufacture_date_from = FND_API.G_MISS_DATE ) THEN
633 p_x_effectivity_detail_rec.manufacture_date_from := null;
634 END IF;
635
636 IF ( p_x_effectivity_detail_rec.manufacture_date_to = FND_API.G_MISS_DATE ) THEN
637 p_x_effectivity_detail_rec.manufacture_date_to := null;
638 END IF;
639
640 IF ( p_x_effectivity_detail_rec.country_code = FND_API.G_MISS_CHAR ) THEN
641 p_x_effectivity_detail_rec.country_code := null;
642 END IF;
643
644 IF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
645 p_x_effectivity_detail_rec.country := null;
646 END IF;
647
648 IF ( p_x_effectivity_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
649 p_x_effectivity_detail_rec.attribute_category := null;
650 END IF;
651
652 IF ( p_x_effectivity_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
653 p_x_effectivity_detail_rec.attribute1 := null;
654 END IF;
655
656 IF ( p_x_effectivity_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
657 p_x_effectivity_detail_rec.attribute2 := null;
658 END IF;
659
660 IF ( p_x_effectivity_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
661 p_x_effectivity_detail_rec.attribute3 := null;
662 END IF;
663
664 IF ( p_x_effectivity_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
665 p_x_effectivity_detail_rec.attribute4 := null;
666 END IF;
667
668 IF ( p_x_effectivity_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
669 p_x_effectivity_detail_rec.attribute5 := null;
670 END IF;
671
672 IF ( p_x_effectivity_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
673 p_x_effectivity_detail_rec.attribute6 := null;
674 END IF;
675
676 IF ( p_x_effectivity_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
677 p_x_effectivity_detail_rec.attribute7 := null;
678 END IF;
679
680 IF ( p_x_effectivity_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
681 p_x_effectivity_detail_rec.attribute8 := null;
682 END IF;
683
684 IF ( p_x_effectivity_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
685 p_x_effectivity_detail_rec.attribute9 := null;
686 END IF;
687
688 IF ( p_x_effectivity_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
689 p_x_effectivity_detail_rec.attribute10 := null;
690 END IF;
691
692 IF ( p_x_effectivity_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
693 p_x_effectivity_detail_rec.attribute11 := null;
694 END IF;
695
696 IF ( p_x_effectivity_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
697 p_x_effectivity_detail_rec.attribute12 := null;
698 END IF;
699
700 IF ( p_x_effectivity_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
701 p_x_effectivity_detail_rec.attribute13 := null;
702 END IF;
703
704 IF ( p_x_effectivity_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
705 p_x_effectivity_detail_rec.attribute14 := null;
706 END IF;
707
708 IF ( p_x_effectivity_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
709 p_x_effectivity_detail_rec.attribute15 := null;
710 END IF;
711
712 END default_missing_attributes;
713
714 -- Procedure to add Default values for missing attributes (CREATE)
715 PROCEDURE default_ext_missing_attributes
716 (
717 p_x_effty_ext_detail_rec IN OUT NOCOPY effty_ext_detail_rec_type
718 )
719 IS
720
721 BEGIN
722
723 -- Convert G_MISS values to NULL
724 IF ( p_x_effty_ext_detail_rec.owner = FND_API.G_MISS_CHAR ) THEN
725 p_x_effty_ext_detail_rec.owner := null;
726 END IF;
727
728 IF ( p_x_effty_ext_detail_rec.owner_id = FND_API.G_MISS_NUM ) THEN
729 p_x_effty_ext_detail_rec.owner_id := null;
730 END IF;
731
732 IF ( p_x_effty_ext_detail_rec.LOCATION = FND_API.G_MISS_CHAR ) THEN
733 p_x_effty_ext_detail_rec.LOCATION := null;
734 END IF;
735
736 IF ( p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE = FND_API.G_MISS_CHAR ) THEN
737 p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE := null;
738 END IF;
739
740 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE = FND_API.G_MISS_CHAR ) THEN
741 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE := null;
742 END IF;
743
744 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME = FND_API.G_MISS_CHAR ) THEN
745 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME := null;
746 END IF;
747
748 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE = FND_API.G_MISS_CHAR ) THEN
749 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE := null;
750 END IF;
751
752 IF ( p_x_effty_ext_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
753 p_x_effty_ext_detail_rec.attribute_category := null;
754 END IF;
755
756 IF ( p_x_effty_ext_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
757 p_x_effty_ext_detail_rec.attribute1 := null;
758 END IF;
759
760 IF ( p_x_effty_ext_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
761 p_x_effty_ext_detail_rec.attribute2 := null;
762 END IF;
763
764 IF ( p_x_effty_ext_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
765 p_x_effty_ext_detail_rec.attribute3 := null;
766 END IF;
767
768 IF ( p_x_effty_ext_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
769 p_x_effty_ext_detail_rec.attribute4 := null;
770 END IF;
771
772 IF ( p_x_effty_ext_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
773 p_x_effty_ext_detail_rec.attribute5 := null;
774 END IF;
775
776 IF ( p_x_effty_ext_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
777 p_x_effty_ext_detail_rec.attribute6 := null;
778 END IF;
779
780 IF ( p_x_effty_ext_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
781 p_x_effty_ext_detail_rec.attribute7 := null;
782 END IF;
783
784 IF ( p_x_effty_ext_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
785 p_x_effty_ext_detail_rec.attribute8 := null;
786 END IF;
787
788 IF ( p_x_effty_ext_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
789 p_x_effty_ext_detail_rec.attribute9 := null;
790 END IF;
791
792 IF ( p_x_effty_ext_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
793 p_x_effty_ext_detail_rec.attribute10 := null;
794 END IF;
795
796 IF ( p_x_effty_ext_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
797 p_x_effty_ext_detail_rec.attribute11 := null;
798 END IF;
799
800 IF ( p_x_effty_ext_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
801 p_x_effty_ext_detail_rec.attribute12 := null;
802 END IF;
803
804 IF ( p_x_effty_ext_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
805 p_x_effty_ext_detail_rec.attribute13 := null;
806 END IF;
807
808 IF ( p_x_effty_ext_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
809 p_x_effty_ext_detail_rec.attribute14 := null;
810 END IF;
811
812 IF ( p_x_effty_ext_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
813 p_x_effty_ext_detail_rec.attribute15 := null;
814 END IF;
815
816 END default_ext_missing_attributes;
817
818 -- Procedure to add Default values for unchanged attributes (UPDATE)
819 PROCEDURE default_unchanged_attributes
820 (
821 p_x_effectivity_detail_rec IN OUT NOCOPY effectivity_detail_rec_type
822 )
823 IS
824
825 l_old_effectivity_detail_rec effectivity_detail_rec_type;
826
827 CURSOR get_old_rec ( c_mr_effectivity_detail_id NUMBER )
828 IS
829 SELECT exclude_flag,
830 serial_number_from,
831 serial_number_to,
832 manufacturer_id,
833 manufacturer,
834 manufacture_date_from,
835 manufacture_date_to,
836 country_code,
837 country,
838 attribute_category,
839 attribute1,
840 attribute2,
841 attribute3,
842 attribute4,
843 attribute5,
844 attribute6,
845 attribute7,
846 attribute8,
847 attribute9,
848 attribute10,
849 attribute11,
850 attribute12,
851 attribute13,
852 attribute14,
853 attribute15
854 FROM AHL_MR_EFFECTIVITY_DTLS_V
855 WHERE mr_effectivity_detail_id = c_mr_effectivity_detail_id;
856
857 BEGIN
858
859 -- Get the old record from AHL_MR_EFFECTIVITY_DTLS.
860 OPEN get_old_rec( p_x_effectivity_detail_rec.mr_effectivity_detail_id );
861
862 FETCH get_old_rec INTO
863 l_old_effectivity_detail_rec.exclude_flag,
864 l_old_effectivity_detail_rec.serial_number_from,
865 l_old_effectivity_detail_rec.serial_number_to,
866 l_old_effectivity_detail_rec.manufacturer_id,
867 l_old_effectivity_detail_rec.manufacturer,
868 l_old_effectivity_detail_rec.manufacture_date_from,
869 l_old_effectivity_detail_rec.manufacture_date_to,
870 l_old_effectivity_detail_rec.country_code,
871 l_old_effectivity_detail_rec.country,
872 l_old_effectivity_detail_rec.attribute_category,
873 l_old_effectivity_detail_rec.attribute1,
874 l_old_effectivity_detail_rec.attribute2,
875 l_old_effectivity_detail_rec.attribute3,
876 l_old_effectivity_detail_rec.attribute4,
877 l_old_effectivity_detail_rec.attribute5,
878 l_old_effectivity_detail_rec.attribute6,
879 l_old_effectivity_detail_rec.attribute7,
880 l_old_effectivity_detail_rec.attribute8,
881 l_old_effectivity_detail_rec.attribute9,
882 l_old_effectivity_detail_rec.attribute10,
883 l_old_effectivity_detail_rec.attribute11,
884 l_old_effectivity_detail_rec.attribute12,
885 l_old_effectivity_detail_rec.attribute13,
886 l_old_effectivity_detail_rec.attribute14,
887 l_old_effectivity_detail_rec.attribute15;
888
889 IF get_old_rec%NOTFOUND THEN
890 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_EFF_DTL_REC' );
891 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_rec ) );
892 FND_MSG_PUB.add;
893 CLOSE get_old_rec;
894 RAISE FND_API.G_EXC_ERROR;
895 END IF;
896
897 CLOSE get_old_rec;
898
899 -- Convert G_MISS values to NULL and NULL values to Old values
900 IF ( p_x_effectivity_detail_rec.exclude_flag IS NULL ) THEN
901 p_x_effectivity_detail_rec.exclude_flag := l_old_effectivity_detail_rec.exclude_flag;
902 END IF;
903
904 IF ( p_x_effectivity_detail_rec.serial_number_from = FND_API.G_MISS_CHAR ) THEN
905 p_x_effectivity_detail_rec.serial_number_from := null;
906 ELSIF ( p_x_effectivity_detail_rec.serial_number_from IS NULL ) THEN
907 p_x_effectivity_detail_rec.serial_number_from := l_old_effectivity_detail_rec.serial_number_from;
908 END IF;
909
910 IF ( p_x_effectivity_detail_rec.serial_number_to = FND_API.G_MISS_CHAR ) THEN
911 p_x_effectivity_detail_rec.serial_number_to := null;
912 ELSIF ( p_x_effectivity_detail_rec.serial_number_to IS NULL ) THEN
913 p_x_effectivity_detail_rec.serial_number_to := l_old_effectivity_detail_rec.serial_number_to;
914 END IF;
915
916 IF ( p_x_effectivity_detail_rec.manufacturer_id = FND_API.G_MISS_NUM ) THEN
917 p_x_effectivity_detail_rec.manufacturer_id := null;
918 ELSIF ( p_x_effectivity_detail_rec.manufacturer_id IS NULL ) THEN
919 p_x_effectivity_detail_rec.manufacturer_id := l_old_effectivity_detail_rec.manufacturer_id;
920 END IF;
921
922 IF ( p_x_effectivity_detail_rec.manufacturer = FND_API.G_MISS_CHAR ) THEN
923 p_x_effectivity_detail_rec.manufacturer := null;
924 ELSIF ( p_x_effectivity_detail_rec.manufacturer IS NULL ) THEN
925 p_x_effectivity_detail_rec.manufacturer := l_old_effectivity_detail_rec.manufacturer;
926 END IF;
927
928 IF ( p_x_effectivity_detail_rec.manufacture_date_from = FND_API.G_MISS_DATE ) THEN
929 p_x_effectivity_detail_rec.manufacture_date_from := null;
930 ELSIF ( p_x_effectivity_detail_rec.manufacture_date_from IS NULL ) THEN
931 p_x_effectivity_detail_rec.manufacture_date_from := l_old_effectivity_detail_rec.manufacture_date_from;
932 END IF;
933
934 IF ( p_x_effectivity_detail_rec.manufacture_date_to = FND_API.G_MISS_DATE ) THEN
935 p_x_effectivity_detail_rec.manufacture_date_to := null;
936 ELSIF ( p_x_effectivity_detail_rec.manufacture_date_to IS NULL ) THEN
937 p_x_effectivity_detail_rec.manufacture_date_to := l_old_effectivity_detail_rec.manufacture_date_to;
938 END IF;
939
940 IF ( p_x_effectivity_detail_rec.country_code = FND_API.G_MISS_CHAR ) THEN
941 p_x_effectivity_detail_rec.country_code := null;
942 ELSIF ( p_x_effectivity_detail_rec.country_code IS NULL ) THEN
943 p_x_effectivity_detail_rec.country_code := l_old_effectivity_detail_rec.country_code;
944 END IF;
945
946 IF ( p_x_effectivity_detail_rec.country = FND_API.G_MISS_CHAR ) THEN
947 p_x_effectivity_detail_rec.country := null;
948 ELSIF ( p_x_effectivity_detail_rec.country IS NULL ) THEN
949 p_x_effectivity_detail_rec.country := l_old_effectivity_detail_rec.country;
950 END IF;
951
952 IF ( p_x_effectivity_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
953 p_x_effectivity_detail_rec.attribute_category := null;
954 ELSIF ( p_x_effectivity_detail_rec.attribute_category IS NULL ) THEN
955 p_x_effectivity_detail_rec.attribute_category := l_old_effectivity_detail_rec.attribute_category;
956 END IF;
957
958 IF ( p_x_effectivity_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
959 p_x_effectivity_detail_rec.attribute1 := null;
960 ELSIF ( p_x_effectivity_detail_rec.attribute1 IS NULL ) THEN
961 p_x_effectivity_detail_rec.attribute1 := l_old_effectivity_detail_rec.attribute1;
962 END IF;
963
964 IF ( p_x_effectivity_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
965 p_x_effectivity_detail_rec.attribute2 := null;
966 ELSIF ( p_x_effectivity_detail_rec.attribute2 IS NULL ) THEN
967 p_x_effectivity_detail_rec.attribute2 := l_old_effectivity_detail_rec.attribute2;
968 END IF;
969
970 IF ( p_x_effectivity_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
971 p_x_effectivity_detail_rec.attribute3 := null;
972 ELSIF ( p_x_effectivity_detail_rec.attribute3 IS NULL ) THEN
973 p_x_effectivity_detail_rec.attribute3 := l_old_effectivity_detail_rec.attribute3;
974 END IF;
975
976 IF ( p_x_effectivity_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
977 p_x_effectivity_detail_rec.attribute4 := null;
978 ELSIF ( p_x_effectivity_detail_rec.attribute4 IS NULL ) THEN
979 p_x_effectivity_detail_rec.attribute4 := l_old_effectivity_detail_rec.attribute4;
980 END IF;
981
982 IF ( p_x_effectivity_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
983 p_x_effectivity_detail_rec.attribute5 := null;
984 ELSIF ( p_x_effectivity_detail_rec.attribute5 IS NULL ) THEN
985 p_x_effectivity_detail_rec.attribute5 := l_old_effectivity_detail_rec.attribute5;
986 END IF;
987
988 IF ( p_x_effectivity_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
989 p_x_effectivity_detail_rec.attribute6 := null;
990 ELSIF ( p_x_effectivity_detail_rec.attribute6 IS NULL ) THEN
991 p_x_effectivity_detail_rec.attribute6 := l_old_effectivity_detail_rec.attribute6;
992 END IF;
993
994 IF ( p_x_effectivity_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
995 p_x_effectivity_detail_rec.attribute7 := null;
996 ELSIF ( p_x_effectivity_detail_rec.attribute7 IS NULL ) THEN
997 p_x_effectivity_detail_rec.attribute7 := l_old_effectivity_detail_rec.attribute7;
998 END IF;
999
1000 IF ( p_x_effectivity_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
1001 p_x_effectivity_detail_rec.attribute8 := null;
1002 ELSIF ( p_x_effectivity_detail_rec.attribute8 IS NULL ) THEN
1003 p_x_effectivity_detail_rec.attribute8 := l_old_effectivity_detail_rec.attribute8;
1004 END IF;
1005
1006 IF ( p_x_effectivity_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
1007 p_x_effectivity_detail_rec.attribute9 := null;
1008 ELSIF ( p_x_effectivity_detail_rec.attribute9 IS NULL ) THEN
1009 p_x_effectivity_detail_rec.attribute9 := l_old_effectivity_detail_rec.attribute9;
1010 END IF;
1011
1012 IF ( p_x_effectivity_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
1013 p_x_effectivity_detail_rec.attribute10 := null;
1014 ELSIF ( p_x_effectivity_detail_rec.attribute10 IS NULL ) THEN
1015 p_x_effectivity_detail_rec.attribute10 := l_old_effectivity_detail_rec.attribute10;
1016 END IF;
1017
1018 IF ( p_x_effectivity_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
1019 p_x_effectivity_detail_rec.attribute11 := null;
1020 ELSIF ( p_x_effectivity_detail_rec.attribute11 IS NULL ) THEN
1021 p_x_effectivity_detail_rec.attribute11 := l_old_effectivity_detail_rec.attribute11;
1022 END IF;
1023
1024 IF ( p_x_effectivity_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
1025 p_x_effectivity_detail_rec.attribute12 := null;
1026 ELSIF ( p_x_effectivity_detail_rec.attribute12 IS NULL ) THEN
1027 p_x_effectivity_detail_rec.attribute12 := l_old_effectivity_detail_rec.attribute12;
1028 END IF;
1029
1030 IF ( p_x_effectivity_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
1031 p_x_effectivity_detail_rec.attribute13 := null;
1032 ELSIF ( p_x_effectivity_detail_rec.attribute13 IS NULL ) THEN
1033 p_x_effectivity_detail_rec.attribute13 := l_old_effectivity_detail_rec.attribute13;
1034 END IF;
1035
1036 IF ( p_x_effectivity_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
1037 p_x_effectivity_detail_rec.attribute14 := null;
1038 ELSIF ( p_x_effectivity_detail_rec.attribute14 IS NULL ) THEN
1039 p_x_effectivity_detail_rec.attribute14 := l_old_effectivity_detail_rec.attribute14;
1040 END IF;
1041
1042 IF ( p_x_effectivity_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
1043 p_x_effectivity_detail_rec.attribute15 := null;
1044 ELSIF ( p_x_effectivity_detail_rec.attribute15 IS NULL ) THEN
1045 p_x_effectivity_detail_rec.attribute15 := l_old_effectivity_detail_rec.attribute15;
1046 END IF;
1047
1048 END default_unchanged_attributes;
1049
1050 -- Procedure to add Default values for unchanged attributes (UPDATE)
1051 PROCEDURE default_ext_unchg_attributes
1052 (
1053 p_x_effty_ext_detail_rec IN OUT NOCOPY effty_ext_detail_rec_type
1054 )
1055 IS
1056
1057 l_old_effty_ext_detail_rec effty_ext_detail_rec_type;
1058
1059 CURSOR get_old_rec ( c_mr_effectivity_ext_dtl_id NUMBER )
1060 IS
1061 SELECT
1062 EFFECT_EXT_DTL_REC_TYPE,
1063 EXCLUDE_FLAG,
1064 OWNER_ID,
1065 LOCATION_TYPE_CODE,
1066 CSI_EXT_ATTRIBUTE_CODE,
1067 CSI_EXT_ATTRIBUTE_VALUE,
1068 attribute_category,
1069 attribute1,
1070 attribute2,
1071 attribute3,
1072 attribute4,
1073 attribute5,
1074 attribute6,
1075 attribute7,
1076 attribute8,
1077 attribute9,
1078 attribute10,
1079 attribute11,
1080 attribute12,
1081 attribute13,
1082 attribute14,
1083 attribute15
1084 FROM AHL_MR_EFFECTIVITY_EXT_DTLS
1085 WHERE MR_EFFECTIVITY_EXT_DTL_ID = c_mr_effectivity_ext_dtl_id;
1086
1087 BEGIN
1088
1089 -- Get the old record from AHL_MR_EFFECTIVITY_DTLS.
1090 OPEN get_old_rec( p_x_effty_ext_detail_rec.mr_effectivity_ext_dtl_id );
1091
1092 FETCH get_old_rec INTO
1093 l_old_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE,
1094 l_old_effty_ext_detail_rec.EXCLUDE_FLAG,
1095 l_old_effty_ext_detail_rec.OWNER_ID,
1096 l_old_effty_ext_detail_rec.LOCATION_TYPE_CODE,
1097 l_old_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE,
1098 l_old_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE,
1099 l_old_effty_ext_detail_rec.attribute_category,
1100 l_old_effty_ext_detail_rec.attribute1,
1101 l_old_effty_ext_detail_rec.attribute2,
1102 l_old_effty_ext_detail_rec.attribute3,
1103 l_old_effty_ext_detail_rec.attribute4,
1104 l_old_effty_ext_detail_rec.attribute5,
1105 l_old_effty_ext_detail_rec.attribute6,
1106 l_old_effty_ext_detail_rec.attribute7,
1107 l_old_effty_ext_detail_rec.attribute8,
1108 l_old_effty_ext_detail_rec.attribute9,
1109 l_old_effty_ext_detail_rec.attribute10,
1110 l_old_effty_ext_detail_rec.attribute11,
1111 l_old_effty_ext_detail_rec.attribute12,
1112 l_old_effty_ext_detail_rec.attribute13,
1113 l_old_effty_ext_detail_rec.attribute14,
1114 l_old_effty_ext_detail_rec.attribute15;
1115
1116 IF get_old_rec%NOTFOUND THEN
1117 IF(l_old_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'OWNER')THEN
1118 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVEFF_EXT_OWN_REC' );
1119 ELSIF (l_old_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'LOCATION')THEN
1120 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVEFF_EXT_LOC_REC' );
1121 ELSIF (l_old_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR')THEN
1122 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVEFF_EXT_ATTR_REC' );
1123 END IF;
1124 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_x_effty_ext_detail_rec ) );
1125 FND_MSG_PUB.add;
1126 CLOSE get_old_rec;
1127 RAISE FND_API.G_EXC_ERROR;
1128 END IF;
1129
1130 CLOSE get_old_rec;
1131
1132 -- Convert G_MISS values to NULL and NULL values to Old values
1133 IF ( p_x_effty_ext_detail_rec.exclude_flag IS NULL ) THEN
1134 p_x_effty_ext_detail_rec.exclude_flag := l_old_effty_ext_detail_rec.exclude_flag;
1135 END IF;
1136
1137 IF ( p_x_effty_ext_detail_rec.OWNER_ID = FND_API.G_MISS_NUM ) THEN
1138 p_x_effty_ext_detail_rec.OWNER_ID := null;
1139 ELSIF ( p_x_effty_ext_detail_rec.OWNER_ID IS NULL ) THEN
1140 p_x_effty_ext_detail_rec.OWNER_ID := l_old_effty_ext_detail_rec.OWNER_ID;
1141 END IF;
1142
1143 IF ( p_x_effty_ext_detail_rec.OWNER = FND_API.G_MISS_CHAR ) THEN
1144 p_x_effty_ext_detail_rec.OWNER := null;
1145 ELSIF ( p_x_effty_ext_detail_rec.OWNER IS NULL ) THEN
1146 p_x_effty_ext_detail_rec.OWNER := l_old_effty_ext_detail_rec.OWNER;
1147 END IF;
1148
1149 IF ( p_x_effty_ext_detail_rec.LOCATION = FND_API.G_MISS_CHAR ) THEN
1150 p_x_effty_ext_detail_rec.LOCATION := null;
1151 ELSIF ( p_x_effty_ext_detail_rec.LOCATION IS NULL ) THEN
1152 p_x_effty_ext_detail_rec.LOCATION := l_old_effty_ext_detail_rec.LOCATION;
1153 END IF;
1154
1155 IF ( p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE = FND_API.G_MISS_CHAR ) THEN
1156 p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE := null;
1157 ELSIF ( p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE IS NULL ) THEN
1158 p_x_effty_ext_detail_rec.LOCATION_TYPE_CODE := l_old_effty_ext_detail_rec.LOCATION_TYPE_CODE;
1159 END IF;
1160
1161 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE = FND_API.G_MISS_CHAR ) THEN
1162 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE := null;
1163 ELSIF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NULL ) THEN
1164 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE := l_old_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE;
1165 END IF;
1166
1167 IF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE = FND_API.G_MISS_CHAR ) THEN
1168 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE := null;
1169 ELSIF ( p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE IS NULL ) THEN
1170 p_x_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE := l_old_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE;
1171 END IF;
1172
1173 IF ( p_x_effty_ext_detail_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
1174 p_x_effty_ext_detail_rec.attribute_category := null;
1175 ELSIF ( p_x_effty_ext_detail_rec.attribute_category IS NULL ) THEN
1176 p_x_effty_ext_detail_rec.attribute_category := l_old_effty_ext_detail_rec.attribute_category;
1177 END IF;
1178
1179 IF ( p_x_effty_ext_detail_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
1180 p_x_effty_ext_detail_rec.attribute1 := null;
1181 ELSIF ( p_x_effty_ext_detail_rec.attribute1 IS NULL ) THEN
1182 p_x_effty_ext_detail_rec.attribute1 := l_old_effty_ext_detail_rec.attribute1;
1183 END IF;
1184
1185 IF ( p_x_effty_ext_detail_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
1186 p_x_effty_ext_detail_rec.attribute2 := null;
1187 ELSIF ( p_x_effty_ext_detail_rec.attribute2 IS NULL ) THEN
1188 p_x_effty_ext_detail_rec.attribute2 := l_old_effty_ext_detail_rec.attribute2;
1189 END IF;
1190
1191 IF ( p_x_effty_ext_detail_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
1192 p_x_effty_ext_detail_rec.attribute3 := null;
1193 ELSIF ( p_x_effty_ext_detail_rec.attribute3 IS NULL ) THEN
1194 p_x_effty_ext_detail_rec.attribute3 := l_old_effty_ext_detail_rec.attribute3;
1195 END IF;
1196
1197 IF ( p_x_effty_ext_detail_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
1198 p_x_effty_ext_detail_rec.attribute4 := null;
1199 ELSIF ( p_x_effty_ext_detail_rec.attribute4 IS NULL ) THEN
1200 p_x_effty_ext_detail_rec.attribute4 := l_old_effty_ext_detail_rec.attribute4;
1201 END IF;
1202
1203 IF ( p_x_effty_ext_detail_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
1204 p_x_effty_ext_detail_rec.attribute5 := null;
1205 ELSIF ( p_x_effty_ext_detail_rec.attribute5 IS NULL ) THEN
1206 p_x_effty_ext_detail_rec.attribute5 := l_old_effty_ext_detail_rec.attribute5;
1207 END IF;
1208
1209 IF ( p_x_effty_ext_detail_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
1210 p_x_effty_ext_detail_rec.attribute6 := null;
1211 ELSIF ( p_x_effty_ext_detail_rec.attribute6 IS NULL ) THEN
1212 p_x_effty_ext_detail_rec.attribute6 := l_old_effty_ext_detail_rec.attribute6;
1213 END IF;
1214
1215 IF ( p_x_effty_ext_detail_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
1216 p_x_effty_ext_detail_rec.attribute7 := null;
1217 ELSIF ( p_x_effty_ext_detail_rec.attribute7 IS NULL ) THEN
1218 p_x_effty_ext_detail_rec.attribute7 := l_old_effty_ext_detail_rec.attribute7;
1219 END IF;
1220
1221 IF ( p_x_effty_ext_detail_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
1222 p_x_effty_ext_detail_rec.attribute8 := null;
1223 ELSIF ( p_x_effty_ext_detail_rec.attribute8 IS NULL ) THEN
1224 p_x_effty_ext_detail_rec.attribute8 := l_old_effty_ext_detail_rec.attribute8;
1225 END IF;
1226
1227 IF ( p_x_effty_ext_detail_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
1228 p_x_effty_ext_detail_rec.attribute9 := null;
1229 ELSIF ( p_x_effty_ext_detail_rec.attribute9 IS NULL ) THEN
1230 p_x_effty_ext_detail_rec.attribute9 := l_old_effty_ext_detail_rec.attribute9;
1231 END IF;
1232
1233 IF ( p_x_effty_ext_detail_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
1234 p_x_effty_ext_detail_rec.attribute10 := null;
1235 ELSIF ( p_x_effty_ext_detail_rec.attribute10 IS NULL ) THEN
1236 p_x_effty_ext_detail_rec.attribute10 := l_old_effty_ext_detail_rec.attribute10;
1237 END IF;
1238
1239 IF ( p_x_effty_ext_detail_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
1240 p_x_effty_ext_detail_rec.attribute11 := null;
1241 ELSIF ( p_x_effty_ext_detail_rec.attribute11 IS NULL ) THEN
1242 p_x_effty_ext_detail_rec.attribute11 := l_old_effty_ext_detail_rec.attribute11;
1243 END IF;
1244
1245 IF ( p_x_effty_ext_detail_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
1246 p_x_effty_ext_detail_rec.attribute12 := null;
1247 ELSIF ( p_x_effty_ext_detail_rec.attribute12 IS NULL ) THEN
1248 p_x_effty_ext_detail_rec.attribute12 := l_old_effty_ext_detail_rec.attribute12;
1249 END IF;
1250
1251 IF ( p_x_effty_ext_detail_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
1252 p_x_effty_ext_detail_rec.attribute13 := null;
1253 ELSIF ( p_x_effty_ext_detail_rec.attribute13 IS NULL ) THEN
1254 p_x_effty_ext_detail_rec.attribute13 := l_old_effty_ext_detail_rec.attribute13;
1255 END IF;
1256
1257 IF ( p_x_effty_ext_detail_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
1258 p_x_effty_ext_detail_rec.attribute14 := null;
1259 ELSIF ( p_x_effty_ext_detail_rec.attribute14 IS NULL ) THEN
1260 p_x_effty_ext_detail_rec.attribute14 := l_old_effty_ext_detail_rec.attribute14;
1261 END IF;
1262
1263 IF ( p_x_effty_ext_detail_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
1264 p_x_effty_ext_detail_rec.attribute15 := null;
1265 ELSIF ( p_x_effty_ext_detail_rec.attribute15 IS NULL ) THEN
1266 p_x_effty_ext_detail_rec.attribute15 := l_old_effty_ext_detail_rec.attribute15;
1267 END IF;
1268
1269 END default_ext_unchg_attributes;
1270
1271 -- Procedure to validate individual effectivity_detail attributes
1272 PROCEDURE validate_attributes
1273 (
1274 p_effectivity_detail_rec IN effectivity_detail_rec_type,
1275 x_return_status OUT NOCOPY VARCHAR2
1276 )
1277 IS
1278
1279 BEGIN
1280 x_return_status := FND_API.G_RET_STS_SUCCESS;
1281
1282 IF ( p_effectivity_detail_rec.dml_operation = 'C' ) THEN
1283 -- Check if the Exclude Flag does not contain a null value.
1284 IF ( p_effectivity_detail_rec.exclude_flag IS NULL OR
1285 p_effectivity_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
1286 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
1287 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1288 FND_MSG_PUB.add;
1289 ELSE
1290 -- Check if the Exclude Flag does not contain an invalid value.
1291 IF ( p_effectivity_detail_rec.exclude_flag <> 'Y' AND
1292 p_effectivity_detail_rec.exclude_flag <> 'N' ) THEN
1293 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
1294 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_rec.exclude_flag );
1295 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1296 FND_MSG_PUB.add;
1297 END IF;
1298 END IF;
1299 RETURN;
1300 END IF;
1301
1302 IF ( p_effectivity_detail_rec.dml_operation = 'U' ) THEN
1303 -- Check if the Exclude Flag does not contain a null value.
1304 IF ( p_effectivity_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
1305 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
1306 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1307 FND_MSG_PUB.add;
1308 ELSIF ( p_effectivity_detail_rec.exclude_flag IS NOT NULL ) THEN
1309 -- Check if the Exclude Flag does not contain an invalid value.
1310 IF ( p_effectivity_detail_rec.exclude_flag <> 'Y' AND
1311 p_effectivity_detail_rec.exclude_flag <> 'N' ) THEN
1312 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
1313 FND_MESSAGE.set_token( 'FIELD', p_effectivity_detail_rec.exclude_flag );
1314 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1315 FND_MSG_PUB.add;
1316 END IF;
1317 END IF;
1318 END IF;
1319
1320 -- Check if the mandatory Effectivity Detail ID column contains a null value.
1321 IF ( p_effectivity_detail_rec.mr_effectivity_detail_id IS NULL OR
1322 p_effectivity_detail_rec.mr_effectivity_detail_id = FND_API.G_MISS_NUM ) THEN
1323 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MR_EFF_DTL_ID_NULL' );
1324 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1325 FND_MSG_PUB.add;
1326 END IF;
1327
1328 -- Check if the mandatory Object Version Number column contains a null value.
1329 IF ( p_effectivity_detail_rec.object_version_number IS NULL OR
1330 p_effectivity_detail_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1331 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MED_OBJ_VERSION_NULL' );
1332 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1333 FND_MSG_PUB.add;
1334 END IF;
1335
1336 END validate_attributes;
1337
1338 -- Procedure to validate individual effectivity_detail attributes
1339 PROCEDURE validate_ext_attributes
1340 (
1341 p_effty_ext_detail_rec IN effty_ext_detail_rec_type,
1342 x_return_status OUT NOCOPY VARCHAR2
1343 )
1344 IS
1345
1346 BEGIN
1347 x_return_status := FND_API.G_RET_STS_SUCCESS;
1348
1349 IF ( p_effty_ext_detail_rec.dml_operation = 'C' ) THEN
1350 -- Check if the Exclude Flag does not contain a null value.
1351 IF ( p_effty_ext_detail_rec.exclude_flag IS NULL OR
1352 p_effty_ext_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
1353 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
1354 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1355 FND_MSG_PUB.add;
1356 ELSE
1357 -- Check if the Exclude Flag does not contain an invalid value.
1358 IF ( p_effty_ext_detail_rec.exclude_flag <> 'Y' AND
1359 p_effty_ext_detail_rec.exclude_flag <> 'N' ) THEN
1360 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
1361 FND_MESSAGE.set_token( 'FIELD', p_effty_ext_detail_rec.exclude_flag );
1362 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1363 FND_MSG_PUB.add;
1364 END IF;
1365 END IF;
1366 RETURN;
1367 END IF;
1368
1369 IF ( p_effty_ext_detail_rec.dml_operation = 'U' ) THEN
1370 -- Check if the Exclude Flag does not contain a null value.
1371 IF ( p_effty_ext_detail_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
1372 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EX_FLAG_NULL' );
1373 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1374 FND_MSG_PUB.add;
1375 ELSIF ( p_effty_ext_detail_rec.exclude_flag IS NOT NULL ) THEN
1376 -- Check if the Exclude Flag does not contain an invalid value.
1377 IF ( p_effty_ext_detail_rec.exclude_flag <> 'Y' AND
1378 p_effty_ext_detail_rec.exclude_flag <> 'N' ) THEN
1379 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_EX_FLAG' );
1380 FND_MESSAGE.set_token( 'FIELD', p_effty_ext_detail_rec.exclude_flag );
1381 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1382 FND_MSG_PUB.add;
1383 END IF;
1384 END IF;
1385 END IF;
1386
1387 -- Check if the mandatory Effectivity Detail ID column contains a null value.
1388 IF ( p_effty_ext_detail_rec.MR_EFFECTIVITY_EXT_DTL_ID IS NULL OR
1389 p_effty_ext_detail_rec.MR_EFFECTIVITY_EXT_DTL_ID = FND_API.G_MISS_NUM ) THEN
1390 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MR_EFF_EXT_DTL_ID_NULL' );
1391 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1392 FND_MSG_PUB.add;
1393 END IF;
1394
1395 -- Check if the mandatory Object Version Number column contains a null value.
1396 IF ( p_effty_ext_detail_rec.object_version_number IS NULL OR
1397 p_effty_ext_detail_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1398 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MED_EXT_OBJ_VER_NULL' );
1399 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_effty_ext_detail_rec ) );
1400 FND_MSG_PUB.add;
1401 END IF;
1402
1403 END validate_ext_attributes;
1404
1405 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
1406 PROCEDURE validate_record
1407 (
1408 p_effectivity_detail_rec IN effectivity_detail_rec_type,
1409 x_return_status OUT NOCOPY VARCHAR2
1410 )
1411 IS
1412
1413 l_return_status VARCHAR2(1);
1414 l_msg_data VARCHAR2(2000);
1415
1416 BEGIN
1417 x_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 -- Check if Serial Number Range is valid
1420 IF ( p_effectivity_detail_rec.serial_number_from IS NOT NULL AND
1421 p_effectivity_detail_rec.serial_number_to IS NOT NULL ) THEN
1422
1423 AHL_FMP_COMMON_PVT.validate_serial_numbers_range
1424 (
1425 x_return_status => l_return_status,
1426 x_msg_data => l_msg_data,
1427 p_serial_number_from => p_effectivity_detail_rec.serial_number_from,
1428 p_serial_number_to => p_effectivity_detail_rec.serial_number_to
1429 );
1430
1431 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1432 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1433 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_detail_rec.serial_number_from );
1434 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_detail_rec.serial_number_to );
1435 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1436 FND_MSG_PUB.add;
1437 END IF;
1438 END IF;
1439
1440 -- Check if Manufacture date range is valid
1441 IF ( p_effectivity_detail_rec.manufacture_date_from IS NOT NULL AND
1442 p_effectivity_detail_rec.manufacture_date_to IS NOT NULL ) THEN
1443 IF ( p_effectivity_detail_rec.manufacture_date_from >
1444 p_effectivity_detail_rec.manufacture_date_to ) THEN
1445 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_MFG_DT_RANGE' );
1446 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_detail_rec.manufacture_date_from );
1447 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_detail_rec.manufacture_date_to );
1448 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_detail_rec ) );
1449 FND_MSG_PUB.add;
1450 END IF;
1451 END IF;
1452
1453 -- Check if atleast one value is passed in the record
1454 IF ( p_effectivity_detail_rec.serial_number_from IS NULL AND
1455 p_effectivity_detail_rec.serial_number_to IS NULL AND
1456 p_effectivity_detail_rec.manufacturer_id IS NULL AND
1457 p_effectivity_detail_rec.manufacturer IS NULL AND
1458 p_effectivity_detail_rec.manufacture_date_from IS NULL AND
1459 p_effectivity_detail_rec.manufacture_date_to IS NULL AND
1460 p_effectivity_detail_rec.country IS NULL AND
1461 p_effectivity_detail_rec.country_code IS NULL ) THEN
1462 FND_MESSAGE.set_name( 'AHL','AHL_FMP_ONE_VALUE_REQD' );
1463 FND_MSG_PUB.add;
1464 END IF;
1465
1466 END validate_record;
1467
1468 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
1469 PROCEDURE validate_ext_record
1470 (
1471 p_effty_ext_detail_rec IN effty_ext_detail_rec_type,
1472 x_return_status OUT NOCOPY VARCHAR2
1473 )
1474 IS
1475
1476 l_return_status VARCHAR2(1);
1477 l_msg_data VARCHAR2(2000);
1478
1479 BEGIN
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481
1482 IF ( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'OWNER' )THEN
1483 IF ( (p_effty_ext_detail_rec.owner IS NULL OR p_effty_ext_detail_rec.owner = FND_API.G_MISS_CHAR) AND
1484 (p_effty_ext_detail_rec.owner_id IS NULL OR p_effty_ext_detail_rec.owner_id = FND_API.G_MISS_NUM )) THEN
1485 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_OWNER_NLL' );
1486 FND_MSG_PUB.add;
1487 ELSIF((p_effty_ext_detail_rec.location IS NOT NULL OR p_effty_ext_detail_rec.location <> FND_API.G_MISS_CHAR) AND
1488 (p_effty_ext_detail_rec.location_type_code IS NOT NULL OR p_effty_ext_detail_rec.location_type_code <> FND_API.G_MISS_CHAR )) THEN
1489 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_OWNER_REC' );
1490 FND_MSG_PUB.add;
1491 ELSIF((p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NOT NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME <> FND_API.G_MISS_CHAR) AND
1492 (p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NOT NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE <> FND_API.G_MISS_CHAR ))THEN
1493 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_OWNER_REC' );
1494 FND_MSG_PUB.add;
1495 END IF;
1496 ELSIF( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'LOCATION' ) THEN
1497 IF((p_effty_ext_detail_rec.location IS NULL OR p_effty_ext_detail_rec.location = FND_API.G_MISS_CHAR) AND
1498 (p_effty_ext_detail_rec.location_type_code IS NULL OR p_effty_ext_detail_rec.location_type_code = FND_API.G_MISS_CHAR )) THEN
1499 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_LOC_NLL' );
1500 FND_MSG_PUB.add;
1501 ELSIF ( (p_effty_ext_detail_rec.owner IS NOT NULL OR p_effty_ext_detail_rec.owner <> FND_API.G_MISS_CHAR) AND
1502 (p_effty_ext_detail_rec.owner_id IS NOT NULL OR p_effty_ext_detail_rec.owner_id <> FND_API.G_MISS_NUM )) THEN
1503 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_LOC_REC' );
1504 FND_MSG_PUB.add;
1505 ELSIF((p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NOT NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME <> FND_API.G_MISS_CHAR) AND
1506 (p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NOT NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE <> FND_API.G_MISS_CHAR ))THEN
1507 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_LOC_REC' );
1508 FND_MSG_PUB.add;
1509 END IF;
1510 ELSIF( p_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR' ) THEN
1511 IF((p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME IS NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME = FND_API.G_MISS_CHAR) AND
1512 (p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE IS NULL OR p_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE = FND_API.G_MISS_CHAR ))THEN
1513 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_CSIATTR_NLL' );
1514 FND_MSG_PUB.add;
1515 ELSIF ( (p_effty_ext_detail_rec.owner IS NOT NULL OR p_effty_ext_detail_rec.owner <> FND_API.G_MISS_CHAR) AND
1516 (p_effty_ext_detail_rec.owner_id IS NOT NULL OR p_effty_ext_detail_rec.owner_id <> FND_API.G_MISS_NUM )) THEN
1517 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_CSIATTR_REC' );
1518 FND_MSG_PUB.add;
1519 ELSIF((p_effty_ext_detail_rec.location IS NOT NULL OR p_effty_ext_detail_rec.location <> FND_API.G_MISS_CHAR) AND
1520 (p_effty_ext_detail_rec.location_type_code IS NOT NULL OR p_effty_ext_detail_rec.location_type_code <> FND_API.G_MISS_CHAR )) THEN
1521 FND_MESSAGE.set_name( 'AHL','AHL_FMP_INVALID_CSIATTR_REC' );
1522 FND_MSG_PUB.add;
1523 END IF;
1524 END IF;
1525
1526
1527 END validate_ext_record;
1528
1529 -- Procedure to Perform cross records validation and duplicate checks
1530 PROCEDURE validate_records
1531 (
1532 p_mr_effectivity_id IN NUMBER,
1533 x_return_status OUT NOCOPY VARCHAR2
1534 )
1535 IS
1536
1537 l_effectivity_detail_rec effectivity_detail_rec_type;
1538
1539 CURSOR get_dup_rec ( c_mr_effectivity_id NUMBER )
1540 IS
1541 SELECT serial_number_from,
1542 serial_number_to,
1543 manufacturer,
1544 manufacture_date_from,
1545 manufacture_date_to,
1546 country
1547 FROM AHL_MR_EFFECTIVITY_DTLS_V
1548 WHERE mr_effectivity_id = c_mr_effectivity_id
1549 GROUP BY serial_number_from,
1550 serial_number_to,
1551 manufacturer,
1552 manufacture_date_from,
1553 manufacture_date_to,
1554 country
1555 HAVING count(*) > 1;
1556
1557 CURSOR get_dup_owner_rec ( c_mr_effectivity_id NUMBER )
1558 IS
1559 SELECT EFFEXT.OWNER_ID,
1560 OWN.owner_number
1561 ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1562 FROM AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT, ahl_owner_details_v OWN
1563 WHERE EFFEXT.mr_effectivity_id = c_mr_effectivity_id
1564 AND EFFEXT.OWNER_ID = OWN.owner_id
1565 AND EFFECT_EXT_DTL_REC_TYPE = 'OWNER'
1566 GROUP BY EFFEXT.OWNER_ID,
1567 OWN.owner_number,
1568 EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1569 HAVING count(*) > 1;
1570
1571 CURSOR get_dup_location_rec ( c_mr_effectivity_id NUMBER )
1572 IS
1573 SELECT EFFEXT.LOCATION_TYPE_CODE,
1574 CS.meaning
1575 ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1576 FROM AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT, csi_lookups CS
1577 WHERE EFFEXT.mr_effectivity_id = c_mr_effectivity_id
1578 and CS.lookup_type='CSI_INST_LOCATION_SOURCE_CODE' and CS.lookup_code = EFFEXT.LOCATION_TYPE_CODE
1579 AND EFFECT_EXT_DTL_REC_TYPE = 'LOCATION'
1580 GROUP BY EFFEXT.LOCATION_TYPE_CODE,
1581 CS.meaning,
1582 EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1583 HAVING count(*) > 1;
1584
1585
1586 CURSOR get_dup_csi_attrib_rec ( c_mr_effectivity_id NUMBER )
1587 IS
1588 SELECT EFFEXT.CSI_EXT_ATTRIBUTE_CODE
1589 , EFFEXT.CSI_EXT_ATTRIBUTE_VALUE
1590 ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1591 ,(Select CIEA.ATTRIBUTE_NAME from CSI_I_EXTENDED_ATTRIBS CIEA
1592 WHERE CIEA.ATTRIBUTE_CODE = EFFEXT.CSI_EXT_ATTRIBUTE_CODE AND rownum < 2) CSI_EXT_ATTRIBUTE_NAME
1593 FROM AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT
1594 WHERE EFFEXT.mr_effectivity_id = c_mr_effectivity_id
1595 AND EFFEXT.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR'
1596 GROUP BY EFFEXT.CSI_EXT_ATTRIBUTE_CODE,
1597 EFFEXT.CSI_EXT_ATTRIBUTE_VALUE,
1598 EFFEXT.EFFECT_EXT_DTL_REC_TYPE
1599 HAVING count(*) > 1;
1600
1601 l_effty_ext_detail_rec effty_ext_detail_rec_type;
1602
1603 BEGIN
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605
1606 -- Check whether any duplicate effectivity_detail records exist
1607 OPEN get_dup_rec( p_mr_effectivity_id );
1608
1609 LOOP
1610 FETCH get_dup_rec INTO
1611 l_effectivity_detail_rec.serial_number_from,
1612 l_effectivity_detail_rec.serial_number_to,
1613 l_effectivity_detail_rec.manufacturer,
1614 l_effectivity_detail_rec.manufacture_date_from,
1615 l_effectivity_detail_rec.manufacture_date_to,
1616 l_effectivity_detail_rec.country;
1617
1618 EXIT WHEN get_dup_rec%NOTFOUND;
1619
1620 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUPLICATE_MED_REC' );
1621 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_effectivity_detail_rec ) );
1622 FND_MSG_PUB.add;
1623 END LOOP;
1624
1625 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1626 CLOSE get_dup_rec;
1627 x_return_status := FND_API.G_RET_STS_ERROR;
1628 RETURN;
1629 END IF;
1630
1631 CLOSE get_dup_rec;
1632
1633 -- Check whether any duplicate effectivity_detail owner records exist
1634 OPEN get_dup_owner_rec( p_mr_effectivity_id );
1635
1636 LOOP
1637 FETCH get_dup_owner_rec INTO
1638 l_effty_ext_detail_rec.owner_id,
1639 l_effty_ext_detail_rec.owner,
1640 l_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE;
1641
1642 EXIT WHEN get_dup_owner_rec%NOTFOUND;
1643
1644 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUP_MED_OWNER_REC' );
1645 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( l_effty_ext_detail_rec ) );
1646 FND_MSG_PUB.add;
1647 END LOOP;
1648
1649 IF ( get_dup_owner_rec%ROWCOUNT > 0 ) THEN
1650 CLOSE get_dup_owner_rec;
1651 x_return_status := FND_API.G_RET_STS_ERROR;
1652 RETURN;
1653 END IF;
1654
1655 CLOSE get_dup_owner_rec;
1656
1657 -- Check whether any duplicate effectivity_detail location records exist
1658 OPEN get_dup_location_rec( p_mr_effectivity_id );
1659
1660 LOOP
1661 FETCH get_dup_location_rec INTO
1662 l_effty_ext_detail_rec.LOCATION_TYPE_CODE,
1663 l_effty_ext_detail_rec.location,
1664 l_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE;
1665
1666 EXIT WHEN get_dup_location_rec%NOTFOUND;
1667
1668 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUP_MED_LOC_REC' );
1669 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( l_effty_ext_detail_rec ) );
1670 FND_MSG_PUB.add;
1671 END LOOP;
1672
1673 IF ( get_dup_location_rec%ROWCOUNT > 0 ) THEN
1674 CLOSE get_dup_location_rec;
1675 x_return_status := FND_API.G_RET_STS_ERROR;
1676 RETURN;
1677 END IF;
1678
1679 CLOSE get_dup_location_rec;
1680
1681 -- Check whether any duplicate effectivity_detail location records exist
1682 OPEN get_dup_csi_attrib_rec( p_mr_effectivity_id );
1683
1684 LOOP
1685 FETCH get_dup_csi_attrib_rec INTO
1686 l_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_CODE,
1687 l_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_VALUE,
1688 l_effty_ext_detail_rec.EFFECT_EXT_DTL_REC_TYPE
1689 ,l_effty_ext_detail_rec.CSI_EXT_ATTRIBUTE_NAME;
1690
1691 EXIT WHEN get_dup_csi_attrib_rec%NOTFOUND;
1692
1693 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_DUP_MED_ATTRIB_REC' );
1694 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( l_effty_ext_detail_rec ) );
1695 FND_MSG_PUB.add;
1696 END LOOP;
1697
1698 IF ( get_dup_csi_attrib_rec%ROWCOUNT > 0 ) THEN
1699 CLOSE get_dup_csi_attrib_rec;
1700 x_return_status := FND_API.G_RET_STS_ERROR;
1701 RETURN;
1702 END IF;
1703
1704 CLOSE get_dup_csi_attrib_rec;
1705
1706
1707
1708 END validate_records;
1709
1710 PROCEDURE process_effectivity_detail
1711 (
1712 p_api_version IN NUMBER := '1.0',
1713 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1714 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1715 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1716 p_default IN VARCHAR2 := FND_API.G_FALSE,
1717 p_module_type IN VARCHAR2 := NULL,
1718 x_return_status OUT NOCOPY VARCHAR2,
1719 x_msg_count OUT NOCOPY NUMBER,
1720 x_msg_data OUT NOCOPY VARCHAR2,
1721 p_x_effectivity_detail_tbl IN OUT NOCOPY effectivity_detail_tbl_type,
1722 p_x_effty_ext_detail_tbl IN OUT NOCOPY effty_ext_detail_tbl_type,
1723 p_mr_header_id IN NUMBER,
1724 p_mr_effectivity_id IN NUMBER
1725 )
1726
1727 IS
1728
1729 CURSOR get_all_effc_info ( c_mr_effectivity_id NUMBER )
1730 IS
1731 SELECT serial_number_from,
1732 serial_number_to,
1733 MR_EFFECTIVITY_DETAIL_ID
1734 FROM AHL_MR_EFFECTIVITY_DTLS_V
1735 WHERE mr_effectivity_id = c_mr_effectivity_id;
1736
1737
1738 Cursor find_min_max_serials ( c_mr_effectivity_id NUMBER )
1739 IS
1740 select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
1741 from csi_item_instances CSI,
1742 AHL_MR_EFFECTIVITIES EFF
1743 where
1744 EFF.MR_EFFECTIVITY_ID = c_mr_effectivity_id and
1745 CSI.inventory_item_id = EFF.inventory_item_id
1746 UNION
1747 select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
1748 from csi_item_instances CSI,
1749 ahl_position_alternates_v PA,
1750 AHL_MR_EFFECTIVITIES EFF
1751 where
1752 EFF.MR_EFFECTIVITY_ID = c_mr_effectivity_id and
1753 EFF.RELATIONSHIP_ID = PA.relationship_id and
1754 CSI.inventory_item_id = PA.inventory_item_id;
1755
1756
1757 l_get_eff_info get_all_effc_info%ROWTYPE;
1758
1759 l_api_version CONSTANT NUMBER := 1.0;
1760 l_return_status VARCHAR2(1);
1761 l_msg_count NUMBER;
1762 l_mr_effectivity_detail_id NUMBER;
1763
1764 l_min_serial VARCHAR2(30);
1765 l_max_serial VARCHAR2(30);
1766
1767 x VARCHAR2(30);
1768 y VARCHAR2(30);
1769 xi VARCHAR2(30);
1770 yi VARCHAR2(30);
1771
1772 l_MR_EFFECTIVITY_EXT_DTL_ID NUMBER;
1773
1774 BEGIN
1775 -- Initialize API return status to success
1776 x_return_status := FND_API.G_RET_STS_SUCCESS;
1777
1778 -- Standard Start of API savepoint
1779 SAVEPOINT process_effectivity_detail_PVT;
1780
1781 -- Standard call to check for call compatibility.
1782 IF NOT FND_API.compatible_api_call
1783 (
1784 l_api_version,
1785 p_api_version,
1786 G_API_NAME,
1787 G_PKG_NAME
1788 )
1789 THEN
1790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1791 END IF;
1792
1793 -- Initialize message list if p_init_msg_list is set to TRUE.
1794 IF FND_API.to_boolean( p_init_msg_list ) THEN
1795 FND_MSG_PUB.initialize;
1796 END IF;
1797
1798 -- Enable Debug (optional)
1799 IF ( G_DEBUG = 'Y' ) THEN
1800 AHL_DEBUG_PUB.enable_debug;
1801 END IF;
1802
1803 IF G_DEBUG = 'Y' THEN
1804 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
1805 END IF;
1806
1807 -- Validate all the inputs of the API
1808 validate_api_inputs
1809 (
1810 p_x_effectivity_detail_tbl, -- IN
1811 p_x_effty_ext_detail_tbl,
1812 p_mr_header_id, -- IN
1813 p_mr_effectivity_id, -- IN
1814 l_return_status -- OUT
1815 );
1816
1817 -- If any severe error occurs, then, abort API.
1818 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1819 RAISE FND_API.G_EXC_ERROR;
1820 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1822 END IF;
1823
1824 IF G_DEBUG = 'Y' THEN
1825 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Done validate_api_inputs' );
1826 END IF;
1827 -- If the module type is JSP, then default values for ID columns of LOV attributes
1828 IF ( p_module_type = 'JSP' ) THEN
1829 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1830 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1831 clear_lov_attribute_ids
1832 (
1833 p_x_effectivity_detail_tbl(i) -- IN OUT Record with Values and Ids
1834 );
1835 END IF;
1836 END LOOP;
1837 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
1838 IF ( p_x_effty_ext_detail_tbl(i).dml_operation <> 'D' ) THEN
1839 clear_ext_lov_attribute_ids
1840 (
1841 p_x_effty_ext_detail_tbl(i) -- IN OUT Record with Values and Ids
1842 );
1843 END IF;
1844 END LOOP;
1845 END IF;
1846
1847 IF G_DEBUG = 'Y' THEN
1848 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Done clear_lov_attribute_ids and clear_ext_lov_attribute_ids' );
1849 END IF;
1850
1851 -- Convert Values into Ids.
1852 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1853 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1854 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1855 convert_values_to_ids
1856 (
1857 p_x_effectivity_detail_tbl(i), -- IN OUT Record with Values and Ids
1858 p_mr_effectivity_id, -- IN
1859 l_return_status -- OUT
1860 );
1861
1862 -- If any severe error occurs, then, abort API.
1863 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1864 RAISE FND_API.G_EXC_ERROR;
1865 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1867 END IF;
1868 END IF;
1869 END LOOP;
1870 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
1871 IF ( p_x_effty_ext_detail_tbl(i).dml_operation <> 'D' ) THEN
1872 convert_ext_values_to_ids
1873 (
1874 p_x_effty_ext_detail_tbl(i), -- IN OUT Record with Values and Ids
1875 p_mr_effectivity_id, -- IN
1876 l_return_status -- OUT
1877 );
1878
1879 -- If any severe error occurs, then, abort API.
1880 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1881 RAISE FND_API.G_EXC_ERROR;
1882 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1884 END IF;
1885 END IF;
1886 END LOOP;
1887 END IF;
1888
1889 IF G_DEBUG = 'Y' THEN
1890 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1891 END IF;
1892
1893 -- Default effectivity_detail attributes.
1894 IF FND_API.to_boolean( p_default ) THEN
1895 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1896 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1897 default_attributes
1898 (
1899 p_x_effectivity_detail_tbl(i) -- IN OUT
1900 );
1901 END IF;
1902 END LOOP;
1903 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
1904 IF ( p_x_effty_ext_detail_tbl(i).dml_operation <> 'D' ) THEN
1905 default_ext_attributes
1906 (
1907 p_x_effty_ext_detail_tbl(i) -- IN OUT
1908 );
1909 END IF;
1910 END LOOP;
1911 END IF;
1912
1913 IF G_DEBUG = 'Y' THEN
1914 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1915 END IF;
1916
1917 -- Validate all attributes (Item level validation)
1918 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1919 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1920 validate_attributes
1921 (
1922 p_x_effectivity_detail_tbl(i), -- IN
1923 l_return_status -- OUT
1924 );
1925
1926 -- If any severe error occurs, then, abort API.
1927 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1928 RAISE FND_API.G_EXC_ERROR;
1929 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 END IF;
1932 END LOOP;
1933 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
1934 validate_ext_attributes
1935 (
1936 p_x_effty_ext_detail_tbl(i), -- IN
1937 l_return_status -- OUT
1938 );
1939
1940 -- If any severe error occurs, then, abort API.
1941 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1942 RAISE FND_API.G_EXC_ERROR;
1943 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1945 END IF;
1946 END LOOP;
1947 END IF;
1948
1949 IF G_DEBUG = 'Y' THEN
1950 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1951 END IF;
1952
1953 -- Default missing and unchanged attributes.
1954 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1955 IF ( p_x_effectivity_detail_tbl(i).dml_operation = 'U' ) THEN
1956 default_unchanged_attributes
1957 (
1958 p_x_effectivity_detail_tbl(i) -- IN OUT
1959 );
1960 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'C' ) THEN
1961 default_missing_attributes
1962 (
1963 p_x_effectivity_detail_tbl(i) -- IN OUT
1964 );
1965 END IF;
1966 END LOOP;
1967
1968 -- Default missing and unchanged attributes.
1969 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
1970 IF ( p_x_effty_ext_detail_tbl(i).dml_operation = 'U' ) THEN
1971 default_ext_unchg_attributes
1972 (
1973 p_x_effty_ext_detail_tbl(i) -- IN OUT
1974 );
1975 ELSIF ( p_x_effty_ext_detail_tbl(i).dml_operation = 'C' ) THEN
1976 default_ext_missing_attributes
1977 (
1978 p_x_effty_ext_detail_tbl(i) -- IN OUT
1979 );
1980 END IF;
1981 END LOOP;
1982
1983 IF G_DEBUG = 'Y' THEN
1984 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1985 END IF;
1986
1987 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1988 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1989 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
1990 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
1991 validate_record
1992 (
1993 p_x_effectivity_detail_tbl(i), -- IN
1994 l_return_status -- OUT
1995 );
1996
1997 -- If any severe error occurs, then, abort API.
1998 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1999 RAISE FND_API.G_EXC_ERROR;
2000 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2002 END IF;
2003 END IF;
2004 END LOOP;
2005 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
2006 IF ( p_x_effty_ext_detail_tbl(i).dml_operation <> 'D' ) THEN
2007 validate_ext_record
2008 (
2009 p_x_effty_ext_detail_tbl(i), -- IN
2010 l_return_status -- OUT
2011 );
2012
2013 -- If any severe error occurs, then, abort API.
2014 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2015 RAISE FND_API.G_EXC_ERROR;
2016 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2018 END IF;
2019 END IF;
2020 END LOOP;
2021 END IF;
2022
2023 IF G_DEBUG = 'Y' THEN
2024 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
2025 END IF;
2026
2027 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2028 l_msg_count := FND_MSG_PUB.count_msg;
2029 IF l_msg_count > 0 THEN
2030 x_msg_count := l_msg_count;
2031 RAISE FND_API.G_EXC_ERROR;
2032 END IF;
2033
2034 -- Perform the DML statement directly.
2035 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
2036 IF ( p_x_effectivity_detail_tbl(i).dml_operation = 'C' ) THEN
2037
2038 -- Insert the record
2039 INSERT INTO AHL_MR_EFFECTIVITY_DTLS
2040 (
2041 MR_EFFECTIVITY_DETAIL_ID,
2042 OBJECT_VERSION_NUMBER,
2043 MR_EFFECTIVITY_ID,
2044 EXCLUDE_FLAG,
2045 SERIAL_NUMBER_FROM,
2046 SERIAL_NUMBER_TO,
2047 MANUFACTURER_ID,
2048 MANUFACTURE_DATE_FROM,
2049 MANUFACTURE_DATE_TO,
2050 COUNTRY_CODE,
2051 ATTRIBUTE_CATEGORY,
2052 ATTRIBUTE1,
2053 ATTRIBUTE2,
2054 ATTRIBUTE3,
2055 ATTRIBUTE4,
2056 ATTRIBUTE5,
2057 ATTRIBUTE6,
2058 ATTRIBUTE7,
2059 ATTRIBUTE8,
2060 ATTRIBUTE9,
2061 ATTRIBUTE10,
2062 ATTRIBUTE11,
2063 ATTRIBUTE12,
2064 ATTRIBUTE13,
2065 ATTRIBUTE14,
2066 ATTRIBUTE15,
2067 LAST_UPDATE_DATE,
2068 LAST_UPDATED_BY,
2069 CREATION_DATE,
2070 CREATED_BY,
2071 LAST_UPDATE_LOGIN
2072 ) VALUES
2073 (
2074 AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
2075 p_x_effectivity_detail_tbl(i).object_version_number,
2076 p_mr_effectivity_id,
2077 p_x_effectivity_detail_tbl(i).exclude_flag,
2078 p_x_effectivity_detail_tbl(i).serial_number_from,
2079 p_x_effectivity_detail_tbl(i).serial_number_to,
2080 p_x_effectivity_detail_tbl(i).manufacturer_id,
2081 p_x_effectivity_detail_tbl(i).manufacture_date_from,
2082 p_x_effectivity_detail_tbl(i).manufacture_date_to,
2083 p_x_effectivity_detail_tbl(i).country_code,
2084 p_x_effectivity_detail_tbl(i).attribute_category,
2085 p_x_effectivity_detail_tbl(i).attribute1,
2086 p_x_effectivity_detail_tbl(i).attribute2,
2087 p_x_effectivity_detail_tbl(i).attribute3,
2088 p_x_effectivity_detail_tbl(i).attribute4,
2089 p_x_effectivity_detail_tbl(i).attribute5,
2090 p_x_effectivity_detail_tbl(i).attribute6,
2091 p_x_effectivity_detail_tbl(i).attribute7,
2092 p_x_effectivity_detail_tbl(i).attribute8,
2093 p_x_effectivity_detail_tbl(i).attribute9,
2094 p_x_effectivity_detail_tbl(i).attribute10,
2095 p_x_effectivity_detail_tbl(i).attribute11,
2096 p_x_effectivity_detail_tbl(i).attribute12,
2097 p_x_effectivity_detail_tbl(i).attribute13,
2098 p_x_effectivity_detail_tbl(i).attribute14,
2099 p_x_effectivity_detail_tbl(i).attribute15,
2100 p_x_effectivity_detail_tbl(i).last_update_date,
2101 p_x_effectivity_detail_tbl(i).last_updated_by,
2102 p_x_effectivity_detail_tbl(i).creation_date,
2103 p_x_effectivity_detail_tbl(i).created_by,
2104 p_x_effectivity_detail_tbl(i).last_update_login
2105 ) RETURNING mr_effectivity_detail_id INTO l_mr_effectivity_detail_id;
2106
2107 -- Set OUT values
2108 p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id := l_mr_effectivity_detail_id;
2109
2110 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'U' ) THEN
2111
2112 -- Update the record
2113 UPDATE AHL_MR_EFFECTIVITY_DTLS SET
2114 object_version_number = object_version_number + 1,
2115 exclude_flag = p_x_effectivity_detail_tbl(i).exclude_flag,
2116 serial_number_from = p_x_effectivity_detail_tbl(i).serial_number_from,
2117 serial_number_to = p_x_effectivity_detail_tbl(i).serial_number_to,
2118 manufacturer_id = p_x_effectivity_detail_tbl(i).manufacturer_id,
2119 manufacture_date_from = p_x_effectivity_detail_tbl(i).manufacture_date_from,
2120 manufacture_date_to = p_x_effectivity_detail_tbl(i).manufacture_date_to,
2121 country_code = p_x_effectivity_detail_tbl(i).country_code,
2122 attribute_category = p_x_effectivity_detail_tbl(i).attribute_category,
2123 attribute1 = p_x_effectivity_detail_tbl(i).attribute1,
2124 attribute2 = p_x_effectivity_detail_tbl(i).attribute2,
2125 attribute3 = p_x_effectivity_detail_tbl(i).attribute3,
2126 attribute4 = p_x_effectivity_detail_tbl(i).attribute4,
2127 attribute5 = p_x_effectivity_detail_tbl(i).attribute5,
2128 attribute6 = p_x_effectivity_detail_tbl(i).attribute6,
2129 attribute7 = p_x_effectivity_detail_tbl(i).attribute7,
2130 attribute8 = p_x_effectivity_detail_tbl(i).attribute8,
2131 attribute9 = p_x_effectivity_detail_tbl(i).attribute9,
2132 attribute10 = p_x_effectivity_detail_tbl(i).attribute10,
2133 attribute11 = p_x_effectivity_detail_tbl(i).attribute11,
2134 attribute12 = p_x_effectivity_detail_tbl(i).attribute12,
2135 attribute13 = p_x_effectivity_detail_tbl(i).attribute13,
2136 attribute14 = p_x_effectivity_detail_tbl(i).attribute14,
2137 attribute15 = p_x_effectivity_detail_tbl(i).attribute15,
2138 last_update_date = p_x_effectivity_detail_tbl(i).last_update_date,
2139 last_updated_by = p_x_effectivity_detail_tbl(i).last_updated_by,
2140 last_update_login = p_x_effectivity_detail_tbl(i).last_update_login
2141 WHERE mr_effectivity_detail_id = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
2142 AND object_version_number = p_x_effectivity_detail_tbl(i).object_version_number;
2143
2144 -- If the record does not exist, then, abort API.
2145 IF ( SQL%ROWCOUNT = 0 ) THEN
2146 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2147 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_tbl(i) ) );
2148 FND_MSG_PUB.add;
2149 RAISE FND_API.G_EXC_ERROR;
2150 END IF;
2151
2152 -- Set OUT values
2153 p_x_effectivity_detail_tbl(i).object_version_number := p_x_effectivity_detail_tbl(i).object_version_number + 1;
2154
2155 ELSIF ( p_x_effectivity_detail_tbl(i).dml_operation = 'D' ) THEN
2156
2157 -- Delete the record
2158 DELETE AHL_MR_EFFECTIVITY_DTLS
2159 WHERE mr_effectivity_detail_id = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
2160 AND object_version_number = p_x_effectivity_detail_tbl(i).object_version_number;
2161
2162 -- If the record does not exist, then, abort API.
2163 IF ( SQL%ROWCOUNT = 0 ) THEN
2164 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2165 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
2166 FND_MSG_PUB.add;
2167 RAISE FND_API.G_EXC_ERROR;
2168 END IF;
2169 END IF;
2170
2171
2172
2173 END LOOP;
2174
2175 IF G_DEBUG = 'Y' THEN
2176 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
2177 END IF;
2178
2179
2180 -- this check can only be done once all records are created/updated/deleted.
2181
2182 FOR i IN 1..p_x_effectivity_detail_tbl.count LOOP
2183 IF ( p_x_effectivity_detail_tbl(i).dml_operation <> 'D' ) THEN
2184
2185
2186 OPEN find_min_max_serials ( p_mr_effectivity_id );
2187 FETCH find_min_max_serials INTO l_min_serial , l_max_serial;
2188 CLOSE find_min_max_serials;
2189
2190 OPEN get_all_effc_info( p_mr_effectivity_id );
2191
2192 LOOP
2193 FETCH get_all_effc_info INTO
2194 l_get_eff_info.serial_number_from,
2195 l_get_eff_info.serial_number_to,
2196 l_get_eff_info.MR_EFFECTIVITY_DETAIL_ID ;
2197
2198 EXIT WHEN get_all_effc_info%NOTFOUND;
2199
2200
2201 xi := NVL( p_x_effectivity_detail_tbl(i).serial_number_from , l_min_serial);
2202 yi := NVL( p_x_effectivity_detail_tbl(i).serial_number_to , l_max_serial);
2203 x := NVL( l_get_eff_info.serial_number_from , l_min_serial);
2204 y := NVL( l_get_eff_info.serial_number_to , l_max_serial);
2205
2206
2207 IF(l_get_eff_info.MR_EFFECTIVITY_DETAIL_ID <> p_x_effectivity_detail_tbl(i).MR_EFFECTIVITY_DETAIL_ID) THEN
2208
2209 IF(
2210 (
2211 ( xi >= x ) AND
2212 ( xi <= y )
2213 )
2214 OR
2215 (
2216 ( yi >= x ) AND
2217 ( yi <= y )
2218 )
2219 OR
2220 (
2221 ( xi < x ) AND
2222 ( yi > y )
2223 )
2224 )
2225 THEN
2226 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_OVERLAP_MED_REC' );
2227 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_detail_tbl(i) ) );
2228 FND_MSG_PUB.add;
2229 RAISE FND_API.G_EXC_ERROR;
2230 END IF;
2231
2232 END IF;
2233
2234 END LOOP;
2235
2236 CLOSE get_all_effc_info;
2237
2238 END IF;
2239
2240 END LOOP;
2241
2242 -- Perform the DML statement directly.
2243 FOR i IN 1..p_x_effty_ext_detail_tbl.count LOOP
2244 IF ( p_x_effty_ext_detail_tbl(i).dml_operation = 'C' ) THEN
2245
2246 -- Insert the record
2247 INSERT INTO AHL_MR_EFFECTIVITY_EXT_DTLS
2248 (
2249 MR_EFFECTIVITY_EXT_DTL_ID,
2250 OBJECT_VERSION_NUMBER,
2251 MR_EFFECTIVITY_ID,
2252 EXCLUDE_FLAG,
2253 EFFECT_EXT_DTL_REC_TYPE,
2254 OWNER_ID,
2255 LOCATION_TYPE_CODE,
2256 CSI_EXT_ATTRIBUTE_CODE,
2257 CSI_EXT_ATTRIBUTE_VALUE,
2258 ATTRIBUTE_CATEGORY,
2259 ATTRIBUTE1,
2260 ATTRIBUTE2,
2261 ATTRIBUTE3,
2262 ATTRIBUTE4,
2263 ATTRIBUTE5,
2264 ATTRIBUTE6,
2265 ATTRIBUTE7,
2266 ATTRIBUTE8,
2267 ATTRIBUTE9,
2268 ATTRIBUTE10,
2269 ATTRIBUTE11,
2270 ATTRIBUTE12,
2271 ATTRIBUTE13,
2272 ATTRIBUTE14,
2273 ATTRIBUTE15,
2274 LAST_UPDATE_DATE,
2275 LAST_UPDATED_BY,
2276 CREATION_DATE,
2277 CREATED_BY,
2278 LAST_UPDATE_LOGIN
2279 ) VALUES
2280 (
2281 AHL_MR_EFFECTIVITY_EXT_DTLS_S.NEXTVAL,
2282 p_x_effty_ext_detail_tbl(i).object_version_number,
2283 p_mr_effectivity_id,
2284 p_x_effty_ext_detail_tbl(i).exclude_flag,
2285 p_x_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE,
2286 p_x_effty_ext_detail_tbl(i).OWNER_ID,
2287 p_x_effty_ext_detail_tbl(i).LOCATION_TYPE_CODE,
2288 p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_CODE,
2289 p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_VALUE,
2290 p_x_effty_ext_detail_tbl(i).attribute_category,
2291 p_x_effty_ext_detail_tbl(i).attribute1,
2292 p_x_effty_ext_detail_tbl(i).attribute2,
2293 p_x_effty_ext_detail_tbl(i).attribute3,
2294 p_x_effty_ext_detail_tbl(i).attribute4,
2295 p_x_effty_ext_detail_tbl(i).attribute5,
2296 p_x_effty_ext_detail_tbl(i).attribute6,
2297 p_x_effty_ext_detail_tbl(i).attribute7,
2298 p_x_effty_ext_detail_tbl(i).attribute8,
2299 p_x_effty_ext_detail_tbl(i).attribute9,
2300 p_x_effty_ext_detail_tbl(i).attribute10,
2301 p_x_effty_ext_detail_tbl(i).attribute11,
2302 p_x_effty_ext_detail_tbl(i).attribute12,
2303 p_x_effty_ext_detail_tbl(i).attribute13,
2304 p_x_effty_ext_detail_tbl(i).attribute14,
2305 p_x_effty_ext_detail_tbl(i).attribute15,
2306 p_x_effty_ext_detail_tbl(i).last_update_date,
2307 p_x_effty_ext_detail_tbl(i).last_updated_by,
2308 p_x_effty_ext_detail_tbl(i).creation_date,
2309 p_x_effty_ext_detail_tbl(i).created_by,
2310 p_x_effty_ext_detail_tbl(i).last_update_login
2311 ) RETURNING MR_EFFECTIVITY_EXT_DTL_ID INTO l_MR_EFFECTIVITY_EXT_DTL_ID;
2312
2313 -- Set OUT values
2314 p_x_effty_ext_detail_tbl(i).MR_EFFECTIVITY_EXT_DTL_ID := l_MR_EFFECTIVITY_EXT_DTL_ID;
2315
2316 ELSIF ( p_x_effty_ext_detail_tbl(i).dml_operation = 'U' ) THEN
2317
2318 -- Update the record
2319 UPDATE AHL_MR_EFFECTIVITY_EXT_DTLS SET
2320 object_version_number = object_version_number + 1,
2321 exclude_flag = p_x_effty_ext_detail_tbl(i).exclude_flag,
2322 EFFECT_EXT_DTL_REC_TYPE = p_x_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE,
2323 OWNER_ID = p_x_effty_ext_detail_tbl(i).OWNER_ID,
2324 LOCATION_TYPE_CODE = p_x_effty_ext_detail_tbl(i).LOCATION_TYPE_CODE,
2325 CSI_EXT_ATTRIBUTE_CODE = p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_CODE,
2326 CSI_EXT_ATTRIBUTE_VALUE = p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_VALUE,
2327 attribute_category = p_x_effty_ext_detail_tbl(i).attribute_category,
2328 attribute1 = p_x_effty_ext_detail_tbl(i).attribute1,
2329 attribute2 = p_x_effty_ext_detail_tbl(i).attribute2,
2330 attribute3 = p_x_effty_ext_detail_tbl(i).attribute3,
2331 attribute4 = p_x_effty_ext_detail_tbl(i).attribute4,
2332 attribute5 = p_x_effty_ext_detail_tbl(i).attribute5,
2333 attribute6 = p_x_effty_ext_detail_tbl(i).attribute6,
2334 attribute7 = p_x_effty_ext_detail_tbl(i).attribute7,
2335 attribute8 = p_x_effty_ext_detail_tbl(i).attribute8,
2336 attribute9 = p_x_effty_ext_detail_tbl(i).attribute9,
2337 attribute10 = p_x_effty_ext_detail_tbl(i).attribute10,
2338 attribute11 = p_x_effty_ext_detail_tbl(i).attribute11,
2339 attribute12 = p_x_effty_ext_detail_tbl(i).attribute12,
2340 attribute13 = p_x_effty_ext_detail_tbl(i).attribute13,
2341 attribute14 = p_x_effty_ext_detail_tbl(i).attribute14,
2342 attribute15 = p_x_effty_ext_detail_tbl(i).attribute15,
2343 last_update_date = p_x_effty_ext_detail_tbl(i).last_update_date,
2344 last_updated_by = p_x_effty_ext_detail_tbl(i).last_updated_by,
2345 last_update_login = p_x_effty_ext_detail_tbl(i).last_update_login
2346 WHERE MR_EFFECTIVITY_EXT_DTL_ID = p_x_effty_ext_detail_tbl(i).MR_EFFECTIVITY_EXT_DTL_ID
2347 AND object_version_number = p_x_effty_ext_detail_tbl(i).object_version_number;
2348
2349 -- If the record does not exist, then, abort API.
2350 IF ( SQL%ROWCOUNT = 0 ) THEN
2351 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2352 FND_MESSAGE.set_token( 'RECORD', get_ext_record_identifier( p_x_effty_ext_detail_tbl(i) ) );
2353 FND_MSG_PUB.add;
2354 RAISE FND_API.G_EXC_ERROR;
2355 END IF;
2356
2357 -- Set OUT values
2358 p_x_effty_ext_detail_tbl(i).object_version_number := p_x_effty_ext_detail_tbl(i).object_version_number + 1;
2359
2360 ELSIF ( p_x_effty_ext_detail_tbl(i).dml_operation = 'D' ) THEN
2361
2362 -- Delete the record
2363 DELETE AHL_MR_EFFECTIVITY_EXT_DTLS
2364 WHERE MR_EFFECTIVITY_EXT_DTL_ID = p_x_effty_ext_detail_tbl(i).MR_EFFECTIVITY_EXT_DTL_ID
2365 AND object_version_number = p_x_effty_ext_detail_tbl(i).object_version_number;
2366
2367 -- If the record does not exist, then, abort API.
2368 IF ( SQL%ROWCOUNT = 0 ) THEN
2369 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
2370 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
2371 FND_MSG_PUB.add;
2372 RAISE FND_API.G_EXC_ERROR;
2373 END IF;
2374 END IF;
2375
2376
2377
2378 END LOOP;
2379
2380 -- Perform cross records validations and duplicate records check
2381 validate_records
2382 (
2383 p_mr_effectivity_id, -- IN
2384 l_return_status -- OUT
2385 );
2386
2387 -- If any severe error occurs, then, abort API.
2388 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2389 RAISE FND_API.G_EXC_ERROR;
2390 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2392 END IF;
2393
2394 IF G_DEBUG = 'Y' THEN
2395 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
2396 END IF;
2397
2398 -- Perform the Commit (if requested)
2399 IF FND_API.to_boolean( p_commit ) THEN
2400 COMMIT WORK;
2401 END IF;
2402
2403 -- Count and Get messages (optional)
2404 FND_MSG_PUB.count_and_get
2405 (
2406 p_encoded => FND_API.G_FALSE,
2407 p_count => x_msg_count,
2408 p_data => x_msg_data
2409 );
2410
2411 -- Disable debug (if enabled)
2412 IF ( G_DEBUG = 'Y' ) THEN
2413 AHL_DEBUG_PUB.disable_debug;
2414 END IF;
2415
2416 EXCEPTION
2417
2418 WHEN FND_API.G_EXC_ERROR THEN
2419 ROLLBACK TO process_effectivity_detail_PVT;
2420 x_return_status := FND_API.G_RET_STS_ERROR ;
2421 FND_MSG_PUB.count_and_get
2422 (
2423 p_encoded => FND_API.G_FALSE,
2424 p_count => x_msg_count,
2425 p_data => x_msg_data
2426 );
2427
2428 -- Disable debug (if enabled)
2429 IF ( G_DEBUG = 'Y' ) THEN
2430 AHL_DEBUG_PUB.disable_debug;
2431 END IF;
2432
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 ROLLBACK TO process_effectivity_detail_PVT;
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2436 FND_MSG_PUB.count_and_get
2437 (
2438 p_encoded => FND_API.G_FALSE,
2439 p_count => x_msg_count,
2440 p_data => x_msg_data
2441 );
2442
2443 -- Disable debug (if enabled)
2444 IF ( G_DEBUG = 'Y' ) THEN
2445 AHL_DEBUG_PUB.disable_debug;
2446 END IF;
2447
2448 WHEN OTHERS THEN
2449 ROLLBACK TO process_effectivity_detail_PVT;
2450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2451 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2452 THEN
2453 FND_MSG_PUB.add_exc_msg
2454 (
2455 p_pkg_name => G_PKG_NAME,
2456 p_procedure_name => G_API_NAME,
2457 p_error_text => SUBSTRB(SQLERRM,1,240)
2458 );
2459 END IF;
2460 FND_MSG_PUB.count_and_get
2461 (
2462 p_encoded => FND_API.G_FALSE,
2463 p_count => x_msg_count,
2464 p_data => x_msg_data
2465 );
2466
2467 -- Disable debug (if enabled)
2468 IF ( G_DEBUG = 'Y' ) THEN
2469 AHL_DEBUG_PUB.disable_debug;
2470 END IF;
2471
2472 END process_effectivity_detail;
2473
2474 END AHL_FMP_EFFECTIVITY_DTL_PVT;