[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_EFFECTIVITY_PVT
Source
1 PACKAGE BODY ahl_fmp_mr_effectivity_pvt AS
2 /* $Header: AHLVMREB.pls 120.3 2008/03/24 08:10:51 pdoki ship $ */
3
4 G_PKG_NAME VARCHAR2(30) :='AHL_FMP_MR_EFFECTIVITY_PVT';
5 G_API_NAME VARCHAR2(30) :='PROCESS_EFFECTIVITY';
6 G_DEBUG VARCHAR2(30) :=AHL_DEBUG_PUB.is_log_enabled;
7 G_APPLN_USAGE VARCHAR2(30) :=ltrim(rtrim(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
8
9 -- Function to get the Record Identifier for Error Messages
10 FUNCTION get_record_identifier
11 (
12 p_effectivity_rec IN effectivity_rec_type
13 ) RETURN VARCHAR2
14 IS
15
16 l_record_identifier VARCHAR2(2000) := '';
17
18 BEGIN
19 IF ( p_effectivity_rec.name IS NOT NULL AND
20 p_effectivity_rec.name <> FND_API.G_MISS_CHAR ) THEN
21 l_record_identifier := p_effectivity_rec.name;
22 END IF;
23
24 l_record_identifier := l_record_identifier || ' - ';
25
26 IF ( p_effectivity_rec.item_number IS NOT NULL AND
27 p_effectivity_rec.item_number <> FND_API.G_MISS_CHAR ) THEN
28 l_record_identifier := l_record_identifier || p_effectivity_rec.item_number;
29 END IF;
30
31 IF ( G_APPLN_USAGE = 'PM' ) THEN
32 RETURN l_record_identifier;
33 END IF;
34
35 l_record_identifier := l_record_identifier || ' - ';
36
37 IF ( p_effectivity_rec.position_ref_meaning IS NOT NULL AND
38 p_effectivity_rec.position_ref_meaning <> FND_API.G_MISS_CHAR ) THEN
39 l_record_identifier := l_record_identifier || p_effectivity_rec.position_ref_meaning;
40 END IF;
41
42 l_record_identifier := l_record_identifier || ' - ';
43
44 IF ( p_effectivity_rec.position_item_number IS NOT NULL AND
45 p_effectivity_rec.position_item_number <> FND_API.G_MISS_CHAR ) THEN
46 l_record_identifier := l_record_identifier || p_effectivity_rec.position_item_number;
47 END IF;
48
49 l_record_identifier := l_record_identifier || ' - ';
50
51 IF ( p_effectivity_rec.pc_node_name IS NOT NULL AND
52 p_effectivity_rec.pc_node_name <> FND_API.G_MISS_CHAR ) THEN
53 l_record_identifier := l_record_identifier || p_effectivity_rec.pc_node_name;
54 END IF;
55
56 RETURN l_record_identifier;
57
58 END get_record_identifier;
59
60 -- Procedure to validate the Inputs of the API
61 PROCEDURE validate_api_inputs
62 (
63 p_effectivity_tbl IN effectivity_tbl_type,
64 p_mr_header_id IN NUMBER,
65 P_APPLN_USAGE IN VARCHAR2,
66 x_return_status OUT NOCOPY VARCHAR2,
67 p_super_user IN VARCHAR2
68 )
69 IS
70 l_error_code VARCHAR2(30);
71 l_return_status VARCHAR2(30);
72 l_mr_status_code VARCHAR2(30);
73 l_mr_type_code VARCHAR2(30);
74 l_pm_install_flag VARCHAR2(1);
75
76 BEGIN
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Check if a valid value is passed in p_mr_header_id
80
81 IF ( p_mr_header_id = FND_API.G_MISS_NUM OR
82 p_mr_header_id IS NULL ) THEN
83 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_MR_HEADER_ID_NULL' );
84 FND_MSG_PUB.add;
85 x_return_status := FND_API.G_RET_STS_ERROR;
86 RETURN;
87 IF G_DEBUG = 'Y' THEN
88 AHL_DEBUG_PUB.debug('MR_HEADER_ID is null to validate_api_inputs' );
89 END IF;
90 END IF;
91 -- Check Profile value
92
93 IF G_APPLN_USAGE IS NULL
94 THEN
95 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
96 FND_MSG_PUB.ADD;
97 IF G_DEBUG = 'Y' THEN
98 AHL_DEBUG_PUB.debug('APPLN USAGE CODE IS NULL IN VALIDATE_API_INPUTS' );
99 END IF;
100 END IF;
101
102 IF ( G_APPLN_USAGE = 'PM' ) THEN
103 l_pm_install_flag:= 'Y';
104 ELSE
105 l_pm_install_flag:= 'N';
106 END IF;
107
108 --check if mr is terminated and get the mr status.
109 l_mr_status_code :=AHL_FMP_COMMON_PVT.check_mr_status(p_mr_header_id);
110 IF l_mr_status_code IS NULL THEN
111 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_MR' );
112 FND_MSG_PUB.add;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 RETURN;
115 END IF;
116
117 --check if mr type.
118 l_mr_type_code :=AHL_FMP_COMMON_PVT.check_mr_type(p_mr_header_id);
119
120 -- Check if the Maintenance Requirement is in Updatable status
121 IF ( l_pm_install_flag = 'Y' AND
122 p_super_user ='Y' AND
123 l_mr_status_code = 'COMPLETE') THEN
124 AHL_FMP_COMMON_PVT.validate_mr_pm_status
125 (
126 x_return_status => l_return_status,
127 x_msg_data => l_error_code,
128 p_mr_header_id => p_mr_header_id
129 );
130 ELSE
131 AHL_FMP_COMMON_PVT.validate_mr_status
132 (
133 x_return_status => l_return_status,
134 x_msg_data => l_error_code,
135 p_mr_header_id => p_mr_header_id
136 );
137 END IF;
138
139 IF l_error_code is not null THEN
140 AHL_DEBUG_PUB.debug('Error here.....'||L_ERROR_CODE);
141 FND_MESSAGE.set_name( 'AHL', l_error_code );
142 FND_MSG_PUB.add;
143 RETURN;
144 END IF;
145
146
147
148
149 -- Check if atleast one record is passed in p_effectivity_tbl
150 IF ( p_effectivity_tbl.count < 1 ) THEN
151 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
152 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
153 FND_MSG_PUB.add;
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 RETURN;
156 END IF;
157
158 FOR i IN 1..p_effectivity_tbl.count LOOP
159
160 -- Validate DML Operation
161 IF ( p_effectivity_tbl(i).dml_operation <> 'D' AND
162 p_effectivity_tbl(i).dml_operation <> 'U' AND
163 p_effectivity_tbl(i).dml_operation <> 'C' ) THEN
164 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
165 FND_MESSAGE.set_token( 'FIELD', p_effectivity_tbl(i).dml_operation );
166 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_tbl(i) ) );
167 FND_MSG_PUB.add;
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 END IF;
170
171 IF ( l_pm_install_flag = 'Y' AND
172 p_effectivity_tbl(i).dml_operation = 'D' AND
173 l_mr_status_code = 'COMPLETE' AND
174 l_mr_type_code ='ACTIVITY')
175 THEN
176 AHL_FMP_COMMON_PVT.validate_mr_type_activity
177 (
178 x_return_status => l_return_status,
179 x_msg_data => l_error_code,
180 p_effectivity_id => p_effectivity_tbl(i).MR_EFFECTIVITY_ID,
181 p_eff_obj_version => p_effectivity_tbl(i).OBJECT_VERSION_NUMBER
182 );
183 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
184 FND_MESSAGE.set_name( 'AHL', l_error_code );
185 FND_MSG_PUB.add;
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 RETURN;
188 END IF;
189 END IF;
190
191 IF ( l_pm_install_flag = 'Y' AND
192 p_effectivity_tbl(i).dml_operation = 'D'AND
193 l_mr_status_code = 'COMPLETE' AND
194 l_mr_type_code ='PROGRAM')
195 THEN
196 AHL_FMP_COMMON_PVT.validate_mr_type_program
197 (
198 x_return_status => l_return_status,
199 x_msg_data => l_error_code,
200 p_mr_header_id => p_mr_header_id,
201 p_effectivity_id => p_effectivity_tbl(i).MR_EFFECTIVITY_ID,
202 p_eff_obj_version => p_effectivity_tbl(i).OBJECT_VERSION_NUMBER
203 );
204
205 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
206 FND_MESSAGE.set_name( 'AHL', l_error_code );
207 FND_MSG_PUB.add;
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 RETURN;
210 END IF;
211 END IF;
212
213
214 IF ( l_pm_install_flag = 'Y' AND
215 p_effectivity_tbl(i).dml_operation = 'U' AND
216 p_super_user ='Y' AND
217 l_mr_status_code = 'COMPLETE') THEN
218 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_UPDATE' );
219 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_tbl(i) ) );
220 FND_MSG_PUB.add;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 RETURN;
223 END IF;
224
225
226 -- Validate to ensure that MC Info and PC Info are not passed for PM
227 IF ( P_APPLN_USAGE = 'PM' AND
228 p_effectivity_tbl(i).dml_operation <> 'D' ) THEN
229
230 IF ( ( p_effectivity_tbl(i).relationship_id IS NOT NULL AND
231 p_effectivity_tbl(i).relationship_id <> FND_API.G_MISS_NUM ) OR
232 ( p_effectivity_tbl(i).position_ref_meaning IS NOT NULL AND
233 p_effectivity_tbl(i).position_ref_meaning <> FND_API.G_MISS_CHAR ) ) THEN
234 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_INPUT_MC_POS' );
235 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_tbl(i) ) );
236 FND_MSG_PUB.add;
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 END IF;
239
240 IF ( ( p_effectivity_tbl(i).position_inventory_item_id IS NOT NULL AND
241 p_effectivity_tbl(i).position_inventory_item_id <> FND_API.G_MISS_NUM ) OR
242 ( p_effectivity_tbl(i).position_item_number IS NOT NULL AND
243 p_effectivity_tbl(i).position_item_number <> FND_API.G_MISS_CHAR ) ) THEN
244 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_INPUT_MC_POS_ITEM' );
245 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_tbl(i) ) );
246 FND_MSG_PUB.add;
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 END IF;
249
250 IF ( ( p_effectivity_tbl(i).pc_node_id IS NOT NULL AND
251 p_effectivity_tbl(i).pc_node_id <> FND_API.G_MISS_NUM ) OR
252 ( p_effectivity_tbl(i).pc_node_name IS NOT NULL AND
253 p_effectivity_tbl(i).pc_node_name <> FND_API.G_MISS_CHAR ) ) THEN
254 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_INPUT_PC_NODE' );
255 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_tbl(i) ) );
256 FND_MSG_PUB.add;
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 END IF;
259
260 END IF;
261
262 END LOOP;
263
264 END validate_api_inputs;
265
266 -- Procedure to Default NULL / G_MISS Values for LOV attributes
267 PROCEDURE clear_lov_attribute_ids
268 (
269 p_x_effectivity_rec IN OUT NOCOPY effectivity_rec_type
270 )
271 IS
272
273 BEGIN
274 /*
275 IF ( p_x_effectivity_rec.item_number IS NULL ) THEN
276 p_x_effectivity_rec.inventory_item_id := NULL;
277 ELSIF ( p_x_effectivity_rec.item_number = FND_API.G_MISS_CHAR ) THEN
278 p_x_effectivity_rec.inventory_item_id := FND_API.G_MISS_NUM;
279 END IF;
280 */
281 IF ( p_x_effectivity_rec.position_ref_meaning IS NULL ) THEN
282 p_x_effectivity_rec.relationship_id := NULL;
283 ELSIF ( p_x_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
284 p_x_effectivity_rec.relationship_id := FND_API.G_MISS_NUM;
285 END IF;
286
287 IF ( p_x_effectivity_rec.position_item_number IS NULL ) THEN
288 p_x_effectivity_rec.position_inventory_item_id := NULL;
289 ELSIF ( p_x_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
290 p_x_effectivity_rec.position_inventory_item_id := FND_API.G_MISS_NUM;
291 END IF;
292
293 IF ( p_x_effectivity_rec.pc_node_name IS NULL ) THEN
294 p_x_effectivity_rec.pc_node_id := NULL;
295 ELSIF ( p_x_effectivity_rec.pc_node_name = FND_API.G_MISS_CHAR ) THEN
296 p_x_effectivity_rec.pc_node_id := FND_API.G_MISS_NUM;
297 END IF;
298 END clear_lov_attribute_ids;
299
300 -- Procedure to perform Value to ID conversion for appropriate attributes
301 PROCEDURE convert_values_to_ids
302 (
303 p_x_effectivity_rec IN OUT NOCOPY effectivity_rec_type,
304 x_return_status OUT NOCOPY VARCHAR2
305 )
306 IS
307
308 l_return_status VARCHAR2(1);
309 l_msg_data VARCHAR2(2000);
310
311 BEGIN
312 x_return_status := FND_API.G_RET_STS_SUCCESS;
313
314 -- Convert / Validate Item
315 IF ( ( p_x_effectivity_rec.inventory_item_id IS NOT NULL AND
316 p_x_effectivity_rec.inventory_item_id <> FND_API.G_MISS_NUM ) OR
317 ( p_x_effectivity_rec.item_number IS NOT NULL AND
318 p_x_effectivity_rec.item_number <> FND_API.G_MISS_CHAR ) ) THEN
319
320 AHL_FMP_COMMON_PVT.validate_item
321 (
322 x_return_status => l_return_status,
323 x_msg_data => l_msg_data,
324 p_item_number => p_x_effectivity_rec.item_number,
325 p_x_inventory_item_id => p_x_effectivity_rec.inventory_item_id
326 );
327
328 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
329 FND_MESSAGE.set_name( 'AHL', l_msg_data );
330
331 IF ( p_x_effectivity_rec.item_number IS NULL OR
332 p_x_effectivity_rec.item_number = FND_API.G_MISS_CHAR ) THEN
333 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_rec.inventory_item_id ) );
334 ELSE
335 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_rec.item_number );
336 END IF;
337
338 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_rec ) );
339 FND_MSG_PUB.add;
340 END IF;
341
342 END IF;
343
344 -- Convert / Validate Master Configuration Position
345 IF ( ( p_x_effectivity_rec.relationship_id IS NOT NULL AND
346 p_x_effectivity_rec.relationship_id <> FND_API.G_MISS_NUM ) OR
347 ( p_x_effectivity_rec.position_ref_meaning IS NOT NULL AND
348 p_x_effectivity_rec.position_ref_meaning <> FND_API.G_MISS_CHAR ) )
349 THEN
350
351 AHL_FMP_COMMON_PVT.validate_position
352 (
353 x_return_status => l_return_status,
354 x_msg_data => l_msg_data,
355 p_position_ref_meaning => p_x_effectivity_rec.position_ref_meaning,
356 p_x_relationship_id => p_x_effectivity_rec.relationship_id
357 );
358
359 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
360 FND_MESSAGE.set_name( 'AHL', l_msg_data );
361
362 IF ( p_x_effectivity_rec.position_ref_meaning IS NULL OR
363 p_x_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
364 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_rec.relationship_id ) );
365 ELSE
366 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_rec.position_ref_meaning );
367 END IF;
368
369 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_rec ) );
370 FND_MSG_PUB.add;
371 END IF;
372
373 END IF;
374
375 -- Convert / Validate Master Configuration Item
376 IF ( ( p_x_effectivity_rec.position_inventory_item_id IS NOT NULL AND
377 p_x_effectivity_rec.position_inventory_item_id <> FND_API.G_MISS_NUM )
378 OR
379 ( p_x_effectivity_rec.position_item_number IS NOT NULL AND
380 p_x_effectivity_rec.position_item_number <> FND_API.G_MISS_CHAR ) )
381 THEN
382
383 AHL_FMP_COMMON_PVT.validate_item
384 (
385 x_return_status => l_return_status,
386 x_msg_data => l_msg_data,
387 p_item_number => p_x_effectivity_rec.position_item_number,
388 p_x_inventory_item_id => p_x_effectivity_rec.position_inventory_item_id
389 );
390
391 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
392 FND_MESSAGE.set_name( 'AHL', l_msg_data );
393
394 IF ( p_x_effectivity_rec.position_item_number IS NULL OR
395 p_x_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
396 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_rec.position_inventory_item_id ) );
397 ELSE
398 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_rec.position_item_number );
399 END IF;
400
401 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_rec ) );
402 FND_MSG_PUB.add;
403 END IF;
404
405 END IF;
406
407 -- Convert / Validate Product Classification Node
408 IF ( ( p_x_effectivity_rec.pc_node_id IS NOT NULL AND
409 p_x_effectivity_rec.pc_node_id <> FND_API.G_MISS_NUM ) OR
410 ( p_x_effectivity_rec.pc_node_name IS NOT NULL AND
411 p_x_effectivity_rec.pc_node_name <> FND_API.G_MISS_CHAR ) ) THEN
412
413 AHL_FMP_COMMON_PVT.validate_pc_node
414 (
415 x_return_status => l_return_status,
416 x_msg_data => l_msg_data,
417 p_pc_node_name => p_x_effectivity_rec.pc_node_name,
418 p_x_pc_node_id => p_x_effectivity_rec.pc_node_id
419 );
420
421 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
422 FND_MESSAGE.set_name( 'AHL', l_msg_data );
423
424 IF ( p_x_effectivity_rec.pc_node_name IS NULL OR
425 p_x_effectivity_rec.pc_node_name = FND_API.G_MISS_CHAR ) THEN
426 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_effectivity_rec.pc_node_id ) );
427 ELSE
428 FND_MESSAGE.set_token( 'FIELD', p_x_effectivity_rec.pc_node_name );
429 END IF;
430
431 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_rec ) );
432 FND_MSG_PUB.add;
433 END IF;
434
435 END IF;
436
437 END convert_values_to_ids;
438
439 -- Procedure to add Default values for effectivity attributes
440 PROCEDURE default_attributes
441 (
442 p_x_effectivity_rec IN OUT NOCOPY effectivity_rec_type
443 )
444 IS
445
446 BEGIN
447
448 p_x_effectivity_rec.last_update_date := SYSDATE;
449 p_x_effectivity_rec.last_updated_by := FND_GLOBAL.user_id;
450 p_x_effectivity_rec.last_update_login := FND_GLOBAL.login_id;
451
452 IF ( p_x_effectivity_rec.dml_operation = 'C' ) THEN
453 p_x_effectivity_rec.object_version_number := 1;
454 p_x_effectivity_rec.creation_date := SYSDATE;
455 p_x_effectivity_rec.created_by := FND_GLOBAL.user_id;
456 END IF;
457
458 END default_attributes;
459
460 -- Procedure to add Default values for missing attributes (CREATE)
461 PROCEDURE default_missing_attributes
462 (
463 p_x_effectivity_rec IN OUT NOCOPY effectivity_rec_type
464 )
465 IS
466
467 BEGIN
468
469 -- Convert G_MISS values to NULL
470 IF ( p_x_effectivity_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
471 p_x_effectivity_rec.inventory_item_id := null;
472 END IF;
473
474 IF ( p_x_effectivity_rec.item_number = FND_API.G_MISS_CHAR ) THEN
475 p_x_effectivity_rec.item_number := null;
476 END IF;
477
478 IF ( p_x_effectivity_rec.relationship_id = FND_API.G_MISS_NUM ) THEN
479 p_x_effectivity_rec.relationship_id := null;
480 END IF;
481
482 IF ( p_x_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
483 p_x_effectivity_rec.position_ref_meaning := null;
484 END IF;
485
486 IF ( p_x_effectivity_rec.position_inventory_item_id = FND_API.G_MISS_NUM ) THEN
487 p_x_effectivity_rec.position_inventory_item_id := null;
488 END IF;
489
490 IF ( p_x_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
491 p_x_effectivity_rec.position_item_number := null;
492 END IF;
493
494 IF ( p_x_effectivity_rec.pc_node_id = FND_API.G_MISS_NUM ) THEN
495 p_x_effectivity_rec.pc_node_id := null;
496 END IF;
497
498 IF ( p_x_effectivity_rec.pc_node_name = FND_API.G_MISS_CHAR ) THEN
499 p_x_effectivity_rec.pc_node_name := null;
500 END IF;
501
502 IF ( p_x_effectivity_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
503 p_x_effectivity_rec.attribute_category := null;
504 END IF;
505
506 IF ( p_x_effectivity_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
507 p_x_effectivity_rec.attribute1 := null;
508 END IF;
509
510 IF ( p_x_effectivity_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
511 p_x_effectivity_rec.attribute2 := null;
512 END IF;
513
514 IF ( p_x_effectivity_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
515 p_x_effectivity_rec.attribute3 := null;
516 END IF;
517
518 IF ( p_x_effectivity_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
519 p_x_effectivity_rec.attribute4 := null;
520 END IF;
521
522 IF ( p_x_effectivity_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
523 p_x_effectivity_rec.attribute5 := null;
524 END IF;
525
526 IF ( p_x_effectivity_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
527 p_x_effectivity_rec.attribute6 := null;
528 END IF;
529
530 IF ( p_x_effectivity_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
531 p_x_effectivity_rec.attribute7 := null;
532 END IF;
533
534 IF ( p_x_effectivity_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
535 p_x_effectivity_rec.attribute8 := null;
536 END IF;
537
538 IF ( p_x_effectivity_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
539 p_x_effectivity_rec.attribute9 := null;
540 END IF;
541
542 IF ( p_x_effectivity_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
543 p_x_effectivity_rec.attribute10 := null;
544 END IF;
545
546 IF ( p_x_effectivity_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
547 p_x_effectivity_rec.attribute11 := null;
548 END IF;
549
550 IF ( p_x_effectivity_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
551 p_x_effectivity_rec.attribute12 := null;
552 END IF;
553
554 IF ( p_x_effectivity_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
555 p_x_effectivity_rec.attribute13 := null;
556 END IF;
557
558 IF ( p_x_effectivity_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
559 p_x_effectivity_rec.attribute14 := null;
560 END IF;
561
562 IF ( p_x_effectivity_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
563 p_x_effectivity_rec.attribute15 := null;
564 END IF;
565
566 END default_missing_attributes;
567
568 -- Procedure to add Default values for unchanged attributes (UPDATE)
569 PROCEDURE default_unchanged_attributes
570 (
571 p_x_effectivity_rec IN OUT NOCOPY effectivity_rec_type
572 )
573 IS
574
575 l_old_effectivity_rec effectivity_rec_type;
576
577 CURSOR get_old_rec ( c_mr_effectivity_id NUMBER )
578 IS
579 SELECT name,
580 inventory_item_id,
581 item_number,
582 relationship_id,
583 position_ref_meaning,
584 position_inventory_item_id,
585 position_item_number,
586 pc_node_id,
587 pc_node_name,
588 attribute_category,
589 attribute1,
590 attribute2,
591 attribute3,
592 attribute4,
593 attribute5,
594 attribute6,
595 attribute7,
596 attribute8,
597 attribute9,
598 attribute10,
599 attribute11,
600 attribute12,
601 attribute13,
602 attribute14,
603 attribute15
604 FROM AHL_MR_EFFECTIVITIES_V
605 WHERE mr_effectivity_id = c_mr_effectivity_id;
606
607 BEGIN
608
609 -- Get the old record from AHL_MR_EFFECTIVITIES.
610 OPEN get_old_rec( p_x_effectivity_rec.mr_effectivity_id );
611
612 FETCH get_old_rec INTO
613 l_old_effectivity_rec.name,
614 l_old_effectivity_rec.inventory_item_id,
615 l_old_effectivity_rec.item_number,
616 l_old_effectivity_rec.relationship_id,
617 l_old_effectivity_rec.position_ref_meaning,
618 l_old_effectivity_rec.position_inventory_item_id,
619 l_old_effectivity_rec.position_item_number,
620 l_old_effectivity_rec.pc_node_id,
621 l_old_effectivity_rec.pc_node_name,
622 l_old_effectivity_rec.attribute_category,
623 l_old_effectivity_rec.attribute1,
624 l_old_effectivity_rec.attribute2,
625 l_old_effectivity_rec.attribute3,
626 l_old_effectivity_rec.attribute4,
627 l_old_effectivity_rec.attribute5,
628 l_old_effectivity_rec.attribute6,
629 l_old_effectivity_rec.attribute7,
630 l_old_effectivity_rec.attribute8,
631 l_old_effectivity_rec.attribute9,
632 l_old_effectivity_rec.attribute10,
633 l_old_effectivity_rec.attribute11,
634 l_old_effectivity_rec.attribute12,
635 l_old_effectivity_rec.attribute13,
636 l_old_effectivity_rec.attribute14,
637 l_old_effectivity_rec.attribute15;
638
639 IF get_old_rec%NOTFOUND THEN
640 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INVALID_MR_EFF_REC' );
641 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_rec ) );
642 FND_MSG_PUB.add;
643 CLOSE get_old_rec;
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646
647 CLOSE get_old_rec;
648
649 -- Convert G_MISS values to NULL and NULL values to Old values
650 IF ( p_x_effectivity_rec.name IS NULL ) THEN
651 p_x_effectivity_rec.name := l_old_effectivity_rec.name;
652 END IF;
653
654 IF ( p_x_effectivity_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
655 p_x_effectivity_rec.inventory_item_id := null;
656 ELSIF ( p_x_effectivity_rec.inventory_item_id IS NULL ) THEN
657 p_x_effectivity_rec.inventory_item_id := l_old_effectivity_rec.inventory_item_id;
658 END IF;
659
660 IF ( p_x_effectivity_rec.item_number = FND_API.G_MISS_CHAR ) THEN
661 p_x_effectivity_rec.item_number := null;
662 ELSIF ( p_x_effectivity_rec.item_number IS NULL ) THEN
663 p_x_effectivity_rec.item_number := l_old_effectivity_rec.item_number;
664 END IF;
665
666 IF ( p_x_effectivity_rec.relationship_id = FND_API.G_MISS_NUM ) THEN
667 p_x_effectivity_rec.relationship_id := null;
668 ELSIF ( p_x_effectivity_rec.relationship_id IS NULL ) THEN
669 p_x_effectivity_rec.relationship_id := l_old_effectivity_rec.relationship_id;
670 END IF;
671
672 IF ( p_x_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
673 p_x_effectivity_rec.position_ref_meaning := null;
674 ELSIF ( p_x_effectivity_rec.position_ref_meaning IS NULL ) THEN
675 p_x_effectivity_rec.position_ref_meaning := l_old_effectivity_rec.position_ref_meaning;
676 END IF;
677
678 IF ( p_x_effectivity_rec.position_inventory_item_id = FND_API.G_MISS_NUM ) THEN
679 p_x_effectivity_rec.position_inventory_item_id := null;
680 ELSIF ( p_x_effectivity_rec.position_inventory_item_id IS NULL ) THEN
681 p_x_effectivity_rec.position_inventory_item_id := l_old_effectivity_rec.position_inventory_item_id;
682 END IF;
683
684 IF ( p_x_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
685 p_x_effectivity_rec.position_item_number := null;
686 ELSIF ( p_x_effectivity_rec.position_item_number IS NULL ) THEN
687 p_x_effectivity_rec.position_item_number := l_old_effectivity_rec.position_item_number;
688 END IF;
689
690 IF ( p_x_effectivity_rec.pc_node_id = FND_API.G_MISS_NUM ) THEN
691 p_x_effectivity_rec.pc_node_id := null;
692 ELSIF ( p_x_effectivity_rec.pc_node_id IS NULL ) THEN
693 p_x_effectivity_rec.pc_node_id := l_old_effectivity_rec.pc_node_id;
694 END IF;
695
696 IF ( p_x_effectivity_rec.pc_node_name = FND_API.G_MISS_CHAR ) THEN
697 p_x_effectivity_rec.pc_node_name := null;
698 ELSIF ( p_x_effectivity_rec.pc_node_name IS NULL ) THEN
699 p_x_effectivity_rec.pc_node_name := l_old_effectivity_rec.pc_node_name;
700 END IF;
701
702 IF ( p_x_effectivity_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
703 p_x_effectivity_rec.attribute_category := null;
704 ELSIF ( p_x_effectivity_rec.attribute_category IS NULL ) THEN
705 p_x_effectivity_rec.attribute_category := l_old_effectivity_rec.attribute_category;
706 END IF;
707
708 IF ( p_x_effectivity_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
709 p_x_effectivity_rec.attribute1 := null;
710 ELSIF ( p_x_effectivity_rec.attribute1 IS NULL ) THEN
711 p_x_effectivity_rec.attribute1 := l_old_effectivity_rec.attribute1;
712 END IF;
713
714 IF ( p_x_effectivity_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
715 p_x_effectivity_rec.attribute2 := null;
716 ELSIF ( p_x_effectivity_rec.attribute2 IS NULL ) THEN
717 p_x_effectivity_rec.attribute2 := l_old_effectivity_rec.attribute2;
718 END IF;
719
720 IF ( p_x_effectivity_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
721 p_x_effectivity_rec.attribute3 := null;
722 ELSIF ( p_x_effectivity_rec.attribute3 IS NULL ) THEN
723 p_x_effectivity_rec.attribute3 := l_old_effectivity_rec.attribute3;
724 END IF;
725
726 IF ( p_x_effectivity_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
727 p_x_effectivity_rec.attribute4 := null;
728 ELSIF ( p_x_effectivity_rec.attribute4 IS NULL ) THEN
729 p_x_effectivity_rec.attribute4 := l_old_effectivity_rec.attribute4;
730 END IF;
731
732 IF ( p_x_effectivity_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
733 p_x_effectivity_rec.attribute5 := null;
734 ELSIF ( p_x_effectivity_rec.attribute5 IS NULL ) THEN
735 p_x_effectivity_rec.attribute5 := l_old_effectivity_rec.attribute5;
736 END IF;
737
738 IF ( p_x_effectivity_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
739 p_x_effectivity_rec.attribute6 := null;
740 ELSIF ( p_x_effectivity_rec.attribute6 IS NULL ) THEN
741 p_x_effectivity_rec.attribute6 := l_old_effectivity_rec.attribute6;
742 END IF;
743
744 IF ( p_x_effectivity_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
745 p_x_effectivity_rec.attribute7 := null;
746 ELSIF ( p_x_effectivity_rec.attribute7 IS NULL ) THEN
747 p_x_effectivity_rec.attribute7 := l_old_effectivity_rec.attribute7;
748 END IF;
749
750 IF ( p_x_effectivity_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
751 p_x_effectivity_rec.attribute8 := null;
752 ELSIF ( p_x_effectivity_rec.attribute8 IS NULL ) THEN
753 p_x_effectivity_rec.attribute8 := l_old_effectivity_rec.attribute8;
754 END IF;
755
756 IF ( p_x_effectivity_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
757 p_x_effectivity_rec.attribute9 := null;
758 ELSIF ( p_x_effectivity_rec.attribute9 IS NULL ) THEN
759 p_x_effectivity_rec.attribute9 := l_old_effectivity_rec.attribute9;
760 END IF;
761
762 IF ( p_x_effectivity_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
763 p_x_effectivity_rec.attribute10 := null;
764 ELSIF ( p_x_effectivity_rec.attribute10 IS NULL ) THEN
765 p_x_effectivity_rec.attribute10 := l_old_effectivity_rec.attribute10;
766 END IF;
767
768 IF ( p_x_effectivity_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
769 p_x_effectivity_rec.attribute11 := null;
770 ELSIF ( p_x_effectivity_rec.attribute11 IS NULL ) THEN
771 p_x_effectivity_rec.attribute11 := l_old_effectivity_rec.attribute11;
772 END IF;
773
774 IF ( p_x_effectivity_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
775 p_x_effectivity_rec.attribute12 := null;
776 ELSIF ( p_x_effectivity_rec.attribute12 IS NULL ) THEN
777 p_x_effectivity_rec.attribute12 := l_old_effectivity_rec.attribute12;
778 END IF;
779
780 IF ( p_x_effectivity_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
781 p_x_effectivity_rec.attribute13 := null;
782 ELSIF ( p_x_effectivity_rec.attribute13 IS NULL ) THEN
783 p_x_effectivity_rec.attribute13 := l_old_effectivity_rec.attribute13;
784 END IF;
785
786 IF ( p_x_effectivity_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
787 p_x_effectivity_rec.attribute14 := null;
788 ELSIF ( p_x_effectivity_rec.attribute14 IS NULL ) THEN
789 p_x_effectivity_rec.attribute14 := l_old_effectivity_rec.attribute14;
790 END IF;
791
792 IF ( p_x_effectivity_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
793 p_x_effectivity_rec.attribute15 := null;
794 ELSIF ( p_x_effectivity_rec.attribute15 IS NULL ) THEN
795 p_x_effectivity_rec.attribute15 := l_old_effectivity_rec.attribute15;
796 END IF;
797
798 END default_unchanged_attributes;
799
800 -- Procedure to validate individual effectivity attributes
801 PROCEDURE validate_attributes
802 (
803 p_effectivity_rec IN effectivity_rec_type,
804 x_return_status OUT NOCOPY VARCHAR2
805 )
806 IS
807
808 BEGIN
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 IF ( p_effectivity_rec.dml_operation = 'C' ) THEN
812 -- Check if the Effectivity Name does not column contains a null value.
813 IF ( p_effectivity_rec.name IS NULL OR
814 p_effectivity_rec.name = FND_API.G_MISS_CHAR ) THEN
815 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EFFECTIVITY_NAME_NULL' );
816 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
817 FND_MSG_PUB.add;
818 END IF;
819 RETURN;
820 END IF;
821
822 IF ( p_effectivity_rec.dml_operation = 'U' ) THEN
823 -- Check if the Effectivity Name column does not contains a null value.
824 IF ( p_effectivity_rec.name = FND_API.G_MISS_CHAR ) THEN
825 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EFFECTIVITY_NAME_NULL' );
826 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
827 FND_MSG_PUB.add;
828 END IF;
829 END IF;
830
831 -- Check if the mandatory Effectivity ID column contains a null value.
832 IF ( p_effectivity_rec.mr_effectivity_id IS NULL OR
833 p_effectivity_rec.mr_effectivity_id = FND_API.G_MISS_NUM ) THEN
834 FND_MESSAGE.set_name( 'AHL','AHL_FMP_EFFECTIVITY_ID_NULL' );
835 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
836 FND_MSG_PUB.add;
837 RETURN;
838 END IF;
839
840 -- Check if the mandatory Object Version Number column contains a null value.
841 IF ( p_effectivity_rec.object_version_number IS NULL OR
842 p_effectivity_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
843 FND_MESSAGE.set_name( 'AHL','AHL_FMP_MRE_OBJ_VERSION_NULL' );
844 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
845 FND_MSG_PUB.add;
846 RETURN;
847 END IF;
848
849 END validate_attributes;
850
851 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
852 PROCEDURE validate_record
853 (
854 p_effectivity_rec IN effectivity_rec_type,
855 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
856 p_mr_header_id IN NUMBER,
857 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
858 P_APPLN_USAGE IN VARCHAR2,
859 x_return_status OUT NOCOPY VARCHAR2
860 )
861 IS
862
863 l_return_status VARCHAR2(1);
864 l_msg_data VARCHAR2(2000);
865 l_manufacturer_id NUMBER;
866 l_counter_id NUMBER;
867 l_dummy VARCHAR2(1); --pdoki added for Bug 6719371
868
869 --pdoki added for Bug 6719371
870 CURSOR check_alternate( c_relationship_id NUMBER, c_inventory_item_id NUMBER )
871 IS
872 SELECT 'X'
873 FROM MTL_SYSTEM_ITEMS_KFV MTL,
874 FND_LOOKUP_VALUES_VL IT,
875 AHL_POSITION_ALTERNATES_V PA
876 WHERE MTL.SERVICE_ITEM_FLAG = 'N'
877 AND IT.LOOKUP_CODE (+) = MTL.ITEM_TYPE
878 AND IT.LOOKUP_TYPE (+) = 'ITEM_TYPE'
879 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(IT.START_DATE_ACTIVE,SYSDATE))
880 AND TRUNC(NVL(IT.END_DATE_ACTIVE,SYSDATE + 1))
881 AND MTL.INVENTORY_ITEM_ID = PA.INVENTORY_ITEM_ID
882 AND PA.INVENTORY_ORG_ID = MTL.ORGANIZATION_ID
883 AND PA.RELATIONSHIP_ID = c_relationship_id
884 AND MTL.INVENTORY_ITEM_ID = c_inventory_item_id ;
885
886
887 CURSOR get_eff_dtls_rec ( c_mr_effectivity_id NUMBER )
888 IS
889 SELECT DISTINCT EFD.manufacturer_id
890 FROM AHL_MR_EFFECTIVITY_DTLS_APP_V EFD,
891 AHL_MR_EFFECTIVITIES_APP_V EF
892 WHERE EFD.manufacturer_id IS NOT NULL
893 AND EFD.mr_effectivity_id = EF.mr_effectivity_id
894 AND EF.mr_effectivity_id = c_mr_effectivity_id;
895
896 CURSOR get_intervals_rec ( c_mr_effectivity_id NUMBER )
897 IS
898 SELECT DISTINCT INT.counter_id
899 FROM AHL_MR_INTERVALS_APP_V INT,
900 AHL_MR_EFFECTIVITIES_APP_V EF
901 WHERE INT.mr_effectivity_id = EF.mr_effectivity_id
902 AND EF.mr_effectivity_id = c_mr_effectivity_id;
903
904 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
905 CURSOR get_mr_details
906 IS
907 SELECT program_type_code
908 FROM ahl_mr_headers_b
909 WHERE mr_header_id = p_mr_header_id;
910
911 l_prog_type VARCHAR2(30);
912 -- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
913
914 BEGIN
915 x_return_status := FND_API.G_RET_STS_SUCCESS;
916
917 IF ( P_APPLN_USAGE = 'PM' ) THEN
918
919 -- Check if Item is NULL
920 IF ( p_effectivity_rec.inventory_item_id IS NULL AND
921 p_effectivity_rec.item_number IS NULL ) THEN
922 FND_MESSAGE.set_name( 'AHL','AHL_FMP_PM_ITEM_NULL' );
923 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
924 FND_MSG_PUB.add;
925 END IF;
926
927 ELSE
928
929 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
930 OPEN get_mr_details;
931 FETCH get_mr_details INTO l_prog_type;
932 CLOSE get_mr_details;
933 -- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
934
935 -- Check if both Item and Master Configuration Position are NULL
936 IF ( p_effectivity_rec.relationship_id IS NULL AND
937 p_effectivity_rec.position_ref_meaning IS NULL AND
938 p_effectivity_rec.inventory_item_id IS NULL AND
939 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
940 p_effectivity_rec.item_number IS NULL AND
941 nvl(l_prog_type, 'X') <> 'MO_PROC') THEN
942 -- Tamal [MEL/CDL RM-FMP Enhancements] Ends here...
943 FND_MESSAGE.set_name( 'AHL','AHL_FMP_ITEM_POS_NULL' );
944 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
945 FND_MSG_PUB.add;
946 END IF;
947
948 -- Check if Master Configuration Item contains a value but, the Position is NULL
949 IF ( p_effectivity_rec.relationship_id IS NULL AND
950 p_effectivity_rec.position_ref_meaning IS NULL AND
951 ( p_effectivity_rec.position_inventory_item_id IS NOT NULL OR
952 p_effectivity_rec.position_item_number IS NOT NULL ) ) THEN
953 FND_MESSAGE.set_name( 'AHL','AHL_FMP_POS_NULL_ITEM_NOTNULL' );
954 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
955 FND_MSG_PUB.add;
956 END IF;
957
958 -- Check if both Item and Master Configuration Position contain values
959 IF ( ( p_effectivity_rec.inventory_item_id IS NOT NULL OR
960 p_effectivity_rec.item_number IS NOT NULL ) AND
961 ( p_effectivity_rec.relationship_id IS NOT NULL OR
962 p_effectivity_rec.position_ref_meaning IS NOT NULL ) ) THEN
963 FND_MESSAGE.set_name( 'AHL','AHL_FMP_BOTH_ITEM_POS_NOTNULL' );
964 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
965 FND_MSG_PUB.add;
966 END IF;
967
968 -- Check if both Item and Master Configuration Item contain values
969 IF ( ( p_effectivity_rec.inventory_item_id IS NOT NULL OR
970 p_effectivity_rec.item_number IS NOT NULL ) AND
971 ( p_effectivity_rec.position_inventory_item_id IS NOT NULL OR
972 p_effectivity_rec.position_item_number IS NOT NULL ) ) THEN
973 FND_MESSAGE.set_name( 'AHL','AHL_FMP_BOTH_ITEM_POS_ITEM' );
974 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
975 FND_MSG_PUB.add;
976 END IF;
977
978 -- Check if the given Item can be installed in the given Master Configuration Position
979 IF ( p_effectivity_rec.relationship_id IS NOT NULL AND
980 p_effectivity_rec.position_inventory_item_id IS NOT NULL ) THEN
981
982 -- Check if the given Item can be installed in the given Position.
983 /* AHL_FMP_COMMON_PVT.validate_position_item
984 (
985 x_return_status => l_return_status,
986 x_msg_data => l_msg_data,
987 p_inventory_item_id => p_effectivity_rec.position_inventory_item_id,
988 p_relationship_id => p_effectivity_rec.relationship_id
989 );
990
991 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
992 FND_MESSAGE.set_name( 'AHL', l_msg_data );
993 IF ( p_effectivity_rec.position_item_number IS NULL OR
994 p_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
995 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_effectivity_rec.position_inventory_item_id ) );
996 ELSE
997 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_rec.position_item_number );
998 END IF;
999
1000 IF ( p_effectivity_rec.position_ref_meaning IS NULL OR
1001 p_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
1002 FND_MESSAGE.set_token( 'FIELD2', TO_CHAR( p_effectivity_rec.relationship_id ) );
1003 ELSE
1004 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_rec.position_ref_meaning );
1005 END IF;
1006
1007 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
1008 FND_MSG_PUB.add;
1009 END IF; */
1010
1011 --pdoki added for Bug 6719371
1012 OPEN check_alternate( p_effectivity_rec.relationship_id , p_effectivity_rec.position_inventory_item_id );
1013
1014 FETCH check_alternate INTO
1015 l_dummy;
1016
1017 IF check_alternate%NOTFOUND THEN
1018 l_msg_data := 'AHL_FMP_INVALID_POSITION_ITEM';
1019
1020 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1021 IF ( p_effectivity_rec.position_item_number IS NULL OR
1022 p_effectivity_rec.position_item_number = FND_API.G_MISS_CHAR ) THEN
1023 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_effectivity_rec.position_inventory_item_id ) );
1024 ELSE
1025 FND_MESSAGE.set_token( 'FIELD1', p_effectivity_rec.position_item_number );
1026 END IF;
1027
1028 IF ( p_effectivity_rec.position_ref_meaning IS NULL OR
1029 p_effectivity_rec.position_ref_meaning = FND_API.G_MISS_CHAR ) THEN
1030 FND_MESSAGE.set_token( 'FIELD2', TO_CHAR( p_effectivity_rec.relationship_id ) );
1031 ELSE
1032 FND_MESSAGE.set_token( 'FIELD2', p_effectivity_rec.position_ref_meaning );
1033 END IF;
1034
1035 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
1036 FND_MSG_PUB.add;
1037 END IF;
1038 CLOSE check_alternate;
1039 END IF;
1040
1041 -- Check if there are Effectivity details defined based on the Position / Item for this Effectivity Record
1042 IF ( p_effectivity_rec.inventory_item_id IS NOT NULL OR
1043 p_effectivity_rec.relationship_id IS NOT NULL ) THEN
1044 OPEN get_eff_dtls_rec( p_effectivity_rec.mr_effectivity_id );
1045
1046 FETCH get_eff_dtls_rec INTO
1047 l_manufacturer_id;
1048
1049 IF get_eff_dtls_rec%FOUND THEN
1050
1051 IF ( p_effectivity_rec.inventory_item_id IS NOT NULL ) THEN
1052 AHL_FMP_COMMON_PVT.validate_manufacturer
1053 (
1054 x_return_status => l_return_status,
1055 x_msg_data => l_msg_data,
1056 p_inventory_item_id => p_effectivity_rec.inventory_item_id,
1057 p_x_manufacturer_id => l_manufacturer_id
1058 );
1059 ELSIF ( p_effectivity_rec.position_inventory_item_id IS NOT NULL ) THEN
1060 AHL_FMP_COMMON_PVT.validate_manufacturer
1061 (
1062 x_return_status => l_return_status,
1063 x_msg_data => l_msg_data,
1064 p_inventory_item_id => p_effectivity_rec.position_inventory_item_id,
1065 p_x_manufacturer_id => l_manufacturer_id
1066 );
1067 ELSIF ( p_effectivity_rec.relationship_id IS NOT NULL ) THEN
1068 AHL_FMP_COMMON_PVT.validate_manufacturer
1069 (
1070 x_return_status => l_return_status,
1071 x_msg_data => l_msg_data,
1072 p_relationship_id => p_effectivity_rec.relationship_id,
1073 p_x_manufacturer_id => l_manufacturer_id
1074 );
1075 END IF;
1076
1077 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1078 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_EFFECTIVITY_DTLS_EXIST' );
1079 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
1080 FND_MSG_PUB.add;
1081 END IF;
1082
1083 END IF;
1084
1085 CLOSE get_eff_dtls_rec;
1086
1087 END IF;
1088
1089 END IF;
1090
1091 -- Check if there are Intervals defined based on the Position / Item for this Effectivity Record
1092 IF ( p_effectivity_rec.inventory_item_id IS NOT NULL OR
1093 p_effectivity_rec.relationship_id IS NOT NULL ) THEN
1094 OPEN get_intervals_rec( p_effectivity_rec.mr_effectivity_id );
1095
1096 FETCH get_intervals_rec INTO
1097 l_counter_id;
1098
1099 IF get_intervals_rec%FOUND THEN
1100
1101 IF ( p_effectivity_rec.inventory_item_id IS NOT NULL ) THEN
1102 AHL_FMP_COMMON_PVT.validate_counter_template
1103 (
1104 x_return_status => l_return_status,
1105 x_msg_data => l_msg_data,
1106 p_inventory_item_id => p_effectivity_rec.inventory_item_id,
1107 p_x_counter_id => l_counter_id
1108 );
1109 ELSIF ( p_effectivity_rec.position_inventory_item_id IS NOT NULL ) THEN
1110 AHL_FMP_COMMON_PVT.validate_counter_template
1111 (
1112 x_return_status => l_return_status,
1113 x_msg_data => l_msg_data,
1114 p_inventory_item_id => p_effectivity_rec.position_inventory_item_id,
1115 p_x_counter_id => l_counter_id
1116 );
1117 ELSIF ( p_effectivity_rec.relationship_id IS NOT NULL ) THEN
1118 AHL_FMP_COMMON_PVT.validate_counter_template
1119 (
1120 x_return_status => l_return_status,
1121 x_msg_data => l_msg_data,
1122 p_relationship_id => p_effectivity_rec.relationship_id,
1123 p_x_counter_id => l_counter_id
1124 );
1125 END IF;
1126
1127 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1128 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_INTERVALS_EXIST' );
1129 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_effectivity_rec ) );
1130 FND_MSG_PUB.add;
1131 END IF;
1132
1133 END IF;
1134
1135 CLOSE get_intervals_rec;
1136
1137 END IF;
1138
1139 END validate_record;
1140
1141 -- Procedure to Perform cross records validation and duplicate checks
1142 PROCEDURE validate_records
1143 (
1144 p_mr_header_id IN NUMBER,
1145 P_APPLN_USAGE IN VARCHAR2,
1146 x_return_status OUT NOCOPY VARCHAR2
1147 )
1148 IS
1149
1150 l_effectivity_name VARCHAR2(80) := NULL;
1151 l_item_number VARCHAR2(40) := NULL;
1152 l_inventory_item_id NUMBER := NULL;
1153
1154 CURSOR get_dup_name ( c_mr_header_id NUMBER )
1155 IS
1156 SELECT name
1157 FROM AHL_MR_EFFECTIVITIES_APP_V
1158 WHERE mr_header_id = c_mr_header_id
1159 GROUP BY name
1160 HAVING count(*) > 1;
1161
1162 CURSOR get_dup_item ( c_mr_header_id NUMBER )
1163 IS
1164 SELECT inventory_item_id
1165 FROM AHL_MR_EFFECTIVITIES_APP_V
1166 WHERE mr_header_id = c_mr_header_id
1167 GROUP BY inventory_item_id
1168 HAVING count(*) > 1;
1169
1170 /* The above query has to use the table instead of the view, because the view
1171 contains more records than the table and if using view the above query doesn't
1172 work. That is the reason why the following cursor is added. This bug is raised
1173 by Michael Payne. */
1174
1175 cursor get_item_number (c_inventory_item_id number) is
1176 select item_number
1177 from ahl_mr_effectivities_v
1178 where inventory_item_id = c_inventory_item_id;
1179
1180 BEGIN
1181
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 -- Check whether any duplicate effectivity records (based on Name) for the given MR_HEADER_ID
1185 OPEN get_dup_name( p_mr_header_id );
1186
1187 LOOP
1188
1189 FETCH get_dup_name INTO
1190 l_effectivity_name;
1191
1192 EXIT WHEN get_dup_name%NOTFOUND;
1193
1194 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_EFFECTIVITY_NAME_DUP' );
1195 FND_MESSAGE.set_token( 'RECORD', l_effectivity_name );
1196 FND_MSG_PUB.add;
1197
1198 END LOOP;
1199
1200 IF ( get_dup_name%ROWCOUNT > 0 ) THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 END IF;
1203
1204 CLOSE get_dup_name;
1205
1206 -- Check whether any duplicate effectivity records (based on Item) for the given MR_HEADER_ID for PM
1207 IF ( P_APPLN_USAGE = 'PM' ) THEN
1208
1209 OPEN get_dup_item( p_mr_header_id );
1210
1211 LOOP
1212
1213 FETCH get_dup_item INTO
1214 l_inventory_item_id;
1215
1216 EXIT WHEN get_dup_item%NOTFOUND;
1217
1218 if (get_dup_item%FOUND and l_inventory_item_id is not null) then
1219 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_ITEM_DUP' );
1220 open get_item_number(l_inventory_item_id);
1221 fetch get_item_number into l_item_number;
1222 if get_item_number%notfound then
1223 l_item_number := to_char(l_inventory_item_id);
1224 end if;
1225 close get_item_number;
1226 FND_MESSAGE.set_token( 'RECORD', l_item_number );
1227 FND_MSG_PUB.add;
1228 end if;
1229
1230 END LOOP;
1231
1232 IF ( get_dup_item%ROWCOUNT > 0 ) THEN
1233 x_return_status := FND_API.G_RET_STS_ERROR;
1234 END IF;
1235
1236 CLOSE get_dup_item;
1237
1238 END IF;
1239
1240 END validate_records;
1241
1242
1243 PROCEDURE process_effectivity
1244 (
1245 p_api_version IN NUMBER := '1.0',
1246 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1247 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1248 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1249 p_default IN VARCHAR2 := FND_API.G_FALSE,
1250 p_module_type IN VARCHAR2 := NULL,
1251 x_return_status OUT NOCOPY VARCHAR2,
1252 x_msg_count OUT NOCOPY NUMBER,
1253 x_msg_data OUT NOCOPY VARCHAR2,
1254 p_x_effectivity_tbl IN OUT NOCOPY effectivity_tbl_type,
1255 p_mr_header_id IN NUMBER,
1256 p_super_user IN VARCHAR2
1257 )
1258 IS
1259 l_api_version CONSTANT NUMBER := 1.0;
1260 l_return_status VARCHAR2(1);
1261 l_msg_count NUMBER;
1262 l_mr_effectivity_id NUMBER;
1263 BEGIN
1264 -- Initialize API return status to success
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266
1267 -- Standard Start of API savepoint
1268 SAVEPOINT process_effectivity_PVT;
1269
1270 -- Standard call to check for call compatibility.
1271 IF NOT FND_API.compatible_api_call
1272 (
1273 l_api_version,
1274 p_api_version,
1275 G_API_NAME,
1276 G_PKG_NAME
1277 )
1278 THEN
1279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1280 END IF;
1281
1282 -- Initialize message list if p_init_msg_list is set to TRUE.
1283 IF FND_API.to_boolean( p_init_msg_list ) THEN
1284 FND_MSG_PUB.initialize;
1285 END IF;
1286
1287 -- Enable Debug (optional)
1288 IF ( G_DEBUG = 'Y' ) THEN
1289 AHL_DEBUG_PUB.enable_debug;
1290 END IF;
1291
1292 IF G_DEBUG = 'Y' THEN
1293 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
1294 END IF;
1295
1296 -- Get the Application Code
1297
1298
1299 IF G_DEBUG = 'Y' THEN
1300 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_appln_usage );
1301 END IF;
1302
1303 -- Validate all the inputs of the API
1304 AHL_DEBUG_PUB.debug('Before Validate inputs ' );
1305 validate_api_inputs
1306 (
1307 p_x_effectivity_tbl, -- IN
1308 p_mr_header_id, -- IN
1309 RTRIM(LTRIM(g_appln_usage)), -- IN
1310 x_return_status, -- OUT
1311 p_super_user
1312 );
1313 l_msg_count := FND_MSG_PUB.count_msg;
1314 IF l_msg_count > 0 THEN
1315 AHL_DEBUG_PUB.debug('After validate with error ' );
1316 x_msg_count := l_msg_count;
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319
1320
1321 AHL_DEBUG_PUB.debug('After validate with no error ' );
1322
1323 -- If any severe error occurs, then, abort API.
1324
1325 IF G_DEBUG = 'Y' THEN
1326 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_api_inputs' );
1327 END IF;
1328
1329 -- If the module type is JSP, then default values for ID columns of LOV attributes
1330 IF ( p_module_type = 'JSP' ) THEN
1331 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1332 IF ( p_x_effectivity_tbl(i).dml_operation <> 'D' ) THEN
1333 clear_lov_attribute_ids
1334 (
1335 p_x_effectivity_tbl(i) -- IN OUT Record with Values and Ids
1336 );
1337 END IF;
1338 END LOOP;
1339 END IF;
1340
1341 -- Convert Values into Ids.
1342 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1343 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1344 IF ( p_x_effectivity_tbl(i).dml_operation <> 'D' ) THEN
1345 convert_values_to_ids
1346 (
1347 p_x_effectivity_tbl(i) , -- IN OUT Record with Values and Ids
1348 l_return_status -- OUT
1349 );
1350
1351 -- If any severe error occurs, then, abort API.
1352 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1353 RAISE FND_API.G_EXC_ERROR;
1354 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 END IF;
1357 END IF;
1358 END LOOP;
1359 END IF;
1360
1361 IF G_DEBUG = 'Y' THEN
1362 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1363 END IF;
1364
1365 -- Default effectivity attributes.
1366 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1367 IF ( p_x_effectivity_tbl(i).dml_operation <> 'D' ) THEN
1368 default_attributes
1369 (
1370 p_x_effectivity_tbl(i) -- IN OUT
1371 );
1372 END IF;
1373 END LOOP;
1374
1375 IF G_DEBUG = 'Y' THEN
1376 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1377 END IF;
1378
1379 -- Validate all attributes (Item level validation)
1380 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1381 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1382 validate_attributes
1383 (
1384 p_x_effectivity_tbl(i), -- IN
1385 l_return_status -- OUT
1386 );
1387
1388 -- If any severe error occurs, then, abort API.
1389 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1390 RAISE FND_API.G_EXC_ERROR;
1391 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1393 END IF;
1394 END LOOP;
1395 END IF;
1396
1397 IF G_DEBUG = 'Y' THEN
1398 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1399 END IF;
1400
1401 -- Default missing and unchanged attributes.
1402 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1403 IF ( p_x_effectivity_tbl(i).dml_operation = 'U' ) THEN
1404 default_unchanged_attributes
1405 (
1406 p_x_effectivity_tbl(i) -- IN OUT
1407 );
1408 ELSIF ( p_x_effectivity_tbl(i).dml_operation = 'C' ) THEN
1409 default_missing_attributes
1410 (
1411 p_x_effectivity_tbl(i) -- IN OUT
1412 );
1413 END IF;
1414 END LOOP;
1415
1416 IF G_DEBUG = 'Y' THEN
1417 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1418 END IF;
1419
1420 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1421 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1422 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1423 IF ( p_x_effectivity_tbl(i).dml_operation <> 'D' ) THEN
1424 validate_record
1425 (
1426 p_x_effectivity_tbl(i), -- IN
1427 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
1428 p_mr_header_id, -- IN
1429 -- Tamal [MEL/CDL RM-FMP Enhancements] Begins here...
1430 g_appln_usage, -- IN
1431 l_return_status -- OUT
1432 );
1433
1434 -- If any severe error occurs, then, abort API.
1435 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1436 RAISE FND_API.G_EXC_ERROR;
1437 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1439 END IF;
1440 END IF;
1441 END LOOP;
1442 END IF;
1443
1444 IF G_DEBUG = 'Y' THEN
1445 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
1446 END IF;
1447
1448 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1449 l_msg_count := FND_MSG_PUB.count_msg;
1450 IF l_msg_count > 0 THEN
1451 x_msg_count := l_msg_count;
1452 RAISE FND_API.G_EXC_ERROR;
1453 END IF;
1454
1455 -- Perform the DML statement directly.
1456 FOR i IN 1..p_x_effectivity_tbl.count LOOP
1457 IF ( p_x_effectivity_tbl(i).dml_operation = 'C' ) THEN
1458
1459 BEGIN
1460 -- Insert the record
1461 INSERT INTO AHL_MR_EFFECTIVITIES
1462 (
1463 MR_EFFECTIVITY_ID,
1464 OBJECT_VERSION_NUMBER,
1465 MR_HEADER_ID,
1466 NAME,
1467 INVENTORY_ITEM_ID,
1468 RELATIONSHIP_ID,
1469 PC_NODE_ID,
1470 ATTRIBUTE_CATEGORY,
1471 ATTRIBUTE1,
1472 ATTRIBUTE2,
1473 ATTRIBUTE3,
1474 ATTRIBUTE4,
1475 ATTRIBUTE5,
1476 ATTRIBUTE6,
1477 ATTRIBUTE7,
1478 ATTRIBUTE8,
1479 ATTRIBUTE9,
1480 ATTRIBUTE10,
1481 ATTRIBUTE11,
1482 ATTRIBUTE12,
1483 ATTRIBUTE13,
1484 ATTRIBUTE14,
1485 ATTRIBUTE15,
1486 LAST_UPDATE_DATE,
1487 LAST_UPDATED_BY,
1488 CREATION_DATE,
1489 CREATED_BY,
1490 LAST_UPDATE_LOGIN
1491 ) VALUES
1492 (
1493 AHL_MR_EFFECTIVITIES_S.NEXTVAL,
1494 p_x_effectivity_tbl(i).object_version_number,
1495 p_mr_header_id,
1496 p_x_effectivity_tbl(i).name,
1497 DECODE( p_x_effectivity_tbl(i).relationship_id, NULL,
1498 p_x_effectivity_tbl(i).inventory_item_id,
1499 p_x_effectivity_tbl(i).position_inventory_item_id ),
1500 p_x_effectivity_tbl(i).relationship_id,
1501 p_x_effectivity_tbl(i).pc_node_id,
1502 p_x_effectivity_tbl(i).attribute_category,
1503 p_x_effectivity_tbl(i).attribute1,
1504 p_x_effectivity_tbl(i).attribute2,
1505 p_x_effectivity_tbl(i).attribute3,
1506 p_x_effectivity_tbl(i).attribute4,
1507 p_x_effectivity_tbl(i).attribute5,
1508 p_x_effectivity_tbl(i).attribute6,
1509 p_x_effectivity_tbl(i).attribute7,
1510 p_x_effectivity_tbl(i).attribute8,
1511 p_x_effectivity_tbl(i).attribute9,
1512 p_x_effectivity_tbl(i).attribute10,
1513 p_x_effectivity_tbl(i).attribute11,
1514 p_x_effectivity_tbl(i).attribute12,
1515 p_x_effectivity_tbl(i).attribute13,
1516 p_x_effectivity_tbl(i).attribute14,
1517 p_x_effectivity_tbl(i).attribute15,
1518 p_x_effectivity_tbl(i).last_update_date,
1519 p_x_effectivity_tbl(i).last_updated_by,
1520 p_x_effectivity_tbl(i).creation_date,
1521 p_x_effectivity_tbl(i).created_by,
1522 p_x_effectivity_tbl(i).last_update_login
1523 ) RETURNING mr_effectivity_id INTO l_mr_effectivity_id;
1524
1525 -- Set OUT values
1526 p_x_effectivity_tbl(i).mr_effectivity_id := l_mr_effectivity_id;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF ( SQLCODE = -1 ) THEN
1531 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_EFFECTIVITY_NAME_DUP' );
1532 FND_MESSAGE.set_token( 'RECORD', p_x_effectivity_tbl(i).name );
1533 FND_MSG_PUB.add;
1534 END IF;
1535 END;
1536
1537 ELSIF ( p_x_effectivity_tbl(i).dml_operation = 'U' ) THEN
1538
1539 BEGIN
1540 -- Update the record
1541 UPDATE AHL_MR_EFFECTIVITIES SET
1542 object_version_number = object_version_number + 1,
1543 name = p_x_effectivity_tbl(i).name,
1544 inventory_item_id = DECODE(
1545 p_x_effectivity_tbl(i).relationship_id,
1546 NULL,
1547 p_x_effectivity_tbl(i).inventory_item_id,
1548 p_x_effectivity_tbl(i).position_inventory_item_id ),
1549 relationship_id = p_x_effectivity_tbl(i).relationship_id,
1550 pc_node_id = p_x_effectivity_tbl(i).pc_node_id,
1551 attribute_category = p_x_effectivity_tbl(i).attribute_category,
1552 attribute1 = p_x_effectivity_tbl(i).attribute1,
1553 attribute2 = p_x_effectivity_tbl(i).attribute2,
1554 attribute3 = p_x_effectivity_tbl(i).attribute3,
1555 attribute4 = p_x_effectivity_tbl(i).attribute4,
1556 attribute5 = p_x_effectivity_tbl(i).attribute5,
1557 attribute6 = p_x_effectivity_tbl(i).attribute6,
1558 attribute7 = p_x_effectivity_tbl(i).attribute7,
1559 attribute8 = p_x_effectivity_tbl(i).attribute8,
1560 attribute9 = p_x_effectivity_tbl(i).attribute9,
1561 attribute10 = p_x_effectivity_tbl(i).attribute10,
1562 attribute11 = p_x_effectivity_tbl(i).attribute11,
1563 attribute12 = p_x_effectivity_tbl(i).attribute12,
1564 attribute13 = p_x_effectivity_tbl(i).attribute13,
1565 attribute14 = p_x_effectivity_tbl(i).attribute14,
1566 attribute15 = p_x_effectivity_tbl(i).attribute15,
1567 last_update_date = p_x_effectivity_tbl(i).last_update_date,
1568 last_updated_by = p_x_effectivity_tbl(i).last_updated_by,
1569 last_update_login = p_x_effectivity_tbl(i).last_update_login
1570 WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id
1571 AND object_version_number = p_x_effectivity_tbl(i).object_version_number;
1572
1573 -- If the record does not exist, then, abort API.
1574 IF ( SQL%ROWCOUNT = 0 ) THEN
1575 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
1576 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_effectivity_tbl(i) ) );
1577 FND_MSG_PUB.add;
1578 END IF;
1579
1580 -- Set OUT values
1581 p_x_effectivity_tbl(i).object_version_number := p_x_effectivity_tbl(i).object_version_number + 1;
1582
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 IF ( SQLCODE = -1 ) THEN
1586 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_EFFECTIVITY_NAME_DUP' );
1587 FND_MESSAGE.set_token( 'RECORD', p_x_effectivity_tbl(i).name );
1588 FND_MSG_PUB.add;
1589 END IF;
1590 END;
1591
1592 ELSIF ( p_x_effectivity_tbl(i).dml_operation = 'D' ) THEN
1593
1594 -- Delete the record
1595 DELETE AHL_MR_EFFECTIVITIES
1596 WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id
1597 AND object_version_number = p_x_effectivity_tbl(i).object_version_number;
1598
1599 -- If the record does not exist, then, abort API.
1600 IF ( SQL%ROWCOUNT = 0 ) THEN
1601 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
1602 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1603 FND_MSG_PUB.add;
1604 END IF;
1605
1606 -- Delete the record in related Tables
1607 DELETE AHL_MR_EFFECTIVITY_DTLS
1608 WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id;
1609
1610 -- If the record does not exist, then, Continue.
1611 IF ( SQL%ROWCOUNT = 0 ) THEN
1612 -- Ignore the Exception
1613 NULL;
1614 END IF;
1615
1616 -- Delete the record in related Tables
1617 DELETE AHL_MR_INTERVALS
1618 WHERE mr_effectivity_id = p_x_effectivity_tbl(i).mr_effectivity_id;
1619
1620 -- If the record does not exist, then, Continue.
1621 IF ( SQL%ROWCOUNT = 0 ) THEN
1622 -- Ignore the Exception
1623 NULL;
1624 END IF;
1625
1626 END IF;
1627 END LOOP;
1628
1629
1630 IF G_DEBUG = 'Y' THEN
1631 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1632 END IF;
1633
1634 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1635 l_msg_count := FND_MSG_PUB.count_msg;
1636 IF l_msg_count > 0 THEN
1637 x_msg_count := l_msg_count;
1638 RAISE FND_API.G_EXC_ERROR;
1639 END IF;
1640
1641 -- Perform cross records validations and duplicate records check
1642
1643 IF G_DEBUG = 'Y' THEN
1644 AHL_DEBUG_PUB.debug( 'Before Validate Records ' );
1645 END IF;
1646
1647 validate_records
1648 (
1649 p_mr_header_id, -- IN
1650 g_appln_usage, -- IN
1651 l_return_status -- OUT
1652 );
1653
1654
1655 IF G_DEBUG = 'Y' THEN
1656 AHL_DEBUG_PUB.debug( 'After Validate Records ' );
1657 END IF;
1658
1659
1660 -- If any severe error occurs, then, abort API.
1661 l_msg_count := FND_MSG_PUB.count_msg;
1662 IF l_msg_count > 0 THEN
1663 x_msg_count := l_msg_count;
1664 RAISE FND_API.G_EXC_ERROR;
1665 END IF;
1666
1667 IF G_DEBUG = 'Y' THEN
1668 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
1669 END IF;
1670
1671 -- Perform the Commit (if requested)
1672 IF FND_API.to_boolean( p_commit ) THEN
1673 COMMIT WORK;
1674 END IF;
1675
1676 -- Count and Get messages (optional)
1677 FND_MSG_PUB.count_and_get
1678 (
1679 p_encoded => FND_API.G_FALSE,
1680 p_count => x_msg_count,
1681 p_data => x_msg_data
1682 );
1683
1684 -- Disable debug (if enabled)
1685 IF ( G_DEBUG = 'Y' ) THEN
1686 AHL_DEBUG_PUB.disable_debug;
1687 END IF;
1688
1689 EXCEPTION
1690
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 ROLLBACK TO process_effectivity_PVT;
1693 x_return_status := FND_API.G_RET_STS_ERROR ;
1694 FND_MSG_PUB.count_and_get
1695 (
1696 p_encoded => FND_API.G_FALSE,
1697 p_count => x_msg_count,
1698 p_data => x_msg_data
1699 );
1700
1701 -- Disable debug (if enabled)
1702 IF ( G_DEBUG = 'Y' ) THEN
1703 AHL_DEBUG_PUB.disable_debug;
1704 END IF;
1705
1706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1707 ROLLBACK TO process_effectivity_PVT;
1708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1709 FND_MSG_PUB.count_and_get
1710 (
1711 p_encoded => FND_API.G_FALSE,
1712 p_count => x_msg_count,
1713 p_data => x_msg_data
1714 );
1715
1716 -- Disable debug (if enabled)
1717 IF ( G_DEBUG = 'Y' ) THEN
1718 AHL_DEBUG_PUB.disable_debug;
1719 END IF;
1720
1721 WHEN OTHERS THEN
1722 ROLLBACK TO process_effectivity_PVT;
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1724 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1725 THEN
1726 FND_MSG_PUB.add_exc_msg
1727 (
1728 p_pkg_name => G_PKG_NAME,
1729 p_procedure_name => G_API_NAME,
1730 p_error_text => SUBSTRB(SQLERRM,1,240)
1731 );
1732 END IF;
1733 FND_MSG_PUB.count_and_get
1734 (
1735 p_encoded => FND_API.G_FALSE,
1736 p_count => x_msg_count,
1737 p_data => x_msg_data
1738 );
1739 -- Disable debug (if enabled)
1740 IF ( G_DEBUG = 'Y' ) THEN
1741 AHL_DEBUG_PUB.disable_debug;
1742 END IF;
1743
1744 END process_effectivity;
1745
1746 END AHL_FMP_MR_EFFECTIVITY_PVT;