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