[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_RELATION_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_RELATION_PVT AS
2 /* $Header: AHLVMRLB.pls 120.1 2006/02/01 10:57:09 tamdas noship $ */
3 G_PKG_NAME VARCHAR2(50):= 'AHL_FMP_MR_RELATION_PVT';
4 G_PM_INSTALL VARCHAR2(30):=ahl_util_pkg.is_pm_installed;
5
6 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
7 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
8
9 PROCEDURE DEFAULT_MISSING_ATTRIBS
10 (p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_relation_PVT.MR_relation_TBL)
11 AS
12
13 BEGIN
14 IF P_X_MR_relation_TBL.COUNT>0
15 THEN
16 FOR i IN P_X_MR_relation_TBL.FIRST.. P_X_MR_relation_TBL.LAST
17 LOOP
18 IF p_x_mr_relation_tbl(I).RELATIONSHIP_CODE= FND_API.G_MISS_CHAR
19 THEN
20 p_x_mr_relation_tbl(I).RELATIONSHIP_CODE:=NULL;
21 END IF;
22
23 IF p_x_mr_relation_tbl(I).MR_HEADER_ID= FND_API.G_MISS_NUM
24 THEN
25 p_x_mr_relation_tbl(I).MR_HEADER_ID:=NULL;
26 END IF;
27
28 IF p_x_mr_relation_tbl(I).RELATED_MR_HEADER_ID= FND_API.G_MISS_NUM
29 THEN
30 p_x_mr_relation_tbl(I).RELATED_MR_HEADER_ID:=NULL;
31 END IF;
32
33 IF p_x_mr_relation_tbl(I).MR_RELATIONSHIP_ID= FND_API.G_MISS_NUM
34 THEN
35 p_x_mr_relation_tbl(I).MR_RELATIONSHIP_ID:=NULL;
36 END IF;
37
38 IF p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
39 THEN
40 IF p_x_mr_relation_tbl(i).dml_operation='C'
41 THEN
42 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=1;
43 ELSE
44 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=NULL;
45 END IF;
46 END IF;
47
48 IF p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
49 THEN
50 p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY:=NULL;
51 END IF;
52
53 IF p_x_mr_relation_tbl(i).ATTRIBUTE1=FND_API.G_MISS_CHAR
54 THEN
55 p_x_mr_relation_tbl(i).ATTRIBUTE1:=NULL;
56 END IF;
57
58 IF p_x_mr_relation_tbl(i).ATTRIBUTE2=FND_API.G_MISS_CHAR
59 THEN
60 p_x_mr_relation_tbl(i).ATTRIBUTE2:=NULL;
61 END IF;
62
63 IF p_x_mr_relation_tbl(i).ATTRIBUTE3=FND_API.G_MISS_CHAR
64 THEN
65 p_x_mr_relation_tbl(i).ATTRIBUTE3:=NULL;
66 END IF;
67
68 IF p_x_mr_relation_tbl(i).ATTRIBUTE4 IS NULL OR p_x_mr_relation_tbl(i).ATTRIBUTE4=FND_API.G_MISS_CHAR
69 THEN
70 p_x_mr_relation_tbl(i).ATTRIBUTE4:=NULL;
71 END IF;
72
73 IF p_x_mr_relation_tbl(i).ATTRIBUTE5=FND_API.G_MISS_CHAR
74 THEN
75 p_x_mr_relation_tbl(i).ATTRIBUTE5:=NULL;
76 END IF;
77
78 IF p_x_mr_relation_tbl(i).ATTRIBUTE6=FND_API.G_MISS_CHAR
79 THEN
80 p_x_mr_relation_tbl(i).ATTRIBUTE6:=NULL;
81 END IF;
82
83 IF p_x_mr_relation_tbl(i).ATTRIBUTE7=FND_API.G_MISS_CHAR
84 THEN
85 p_x_mr_relation_tbl(i).ATTRIBUTE7:=NULL;
86 END IF;
87
88 IF p_x_mr_relation_tbl(i).ATTRIBUTE8=FND_API.G_MISS_CHAR
89 THEN
90 p_x_mr_relation_tbl(i).ATTRIBUTE8:=NULL;
91 END IF;
92
93 IF p_x_mr_relation_tbl(i).ATTRIBUTE9=FND_API.G_MISS_CHAR
94 THEN
95 p_x_mr_relation_tbl(i).ATTRIBUTE9:=NULL;
96 END IF;
97
98 IF p_x_mr_relation_tbl(i).ATTRIBUTE10=FND_API.G_MISS_CHAR
99 THEN
100 p_x_mr_relation_tbl(i).ATTRIBUTE10:=NULL;
101 END IF;
102
103 IF p_x_mr_relation_tbl(i).ATTRIBUTE11=FND_API.G_MISS_CHAR
104 THEN
105 p_x_mr_relation_tbl(i).ATTRIBUTE11:=NULL;
106 END IF;
107
108 IF p_x_mr_relation_tbl(i).ATTRIBUTE12=FND_API.G_MISS_CHAR
109 THEN
110 p_x_mr_relation_tbl(i).ATTRIBUTE12:=NULL;
111 END IF;
112
113 IF p_x_mr_relation_tbl(i).ATTRIBUTE13=FND_API.G_MISS_CHAR
114 THEN
115 p_x_mr_relation_tbl(i).ATTRIBUTE13:=NULL;
116 END IF;
117
118 IF p_x_mr_relation_tbl(i).ATTRIBUTE14=FND_API.G_MISS_CHAR
119 THEN
120 p_x_mr_relation_tbl(i).ATTRIBUTE14:=NULL;
121 END IF;
122
123 IF p_x_mr_relation_tbl(i).ATTRIBUTE15=FND_API.G_MISS_CHAR
124 THEN
125 p_x_mr_relation_tbl(i).ATTRIBUTE15:=NULL;
126 END IF;
127 END LOOP;
128 END IF;
129 END;
130
131 PROCEDURE TRANS_VALUE_ID
132 (
133 x_return_status OUT NOCOPY VARCHAR2,
134 p_x_mr_relation_rec IN OUT NOCOPY MR_RELATION_REC
135 )
136 as
137 CURSOR title_to_relmr_header_id(C_TITLE IN VARCHAR2,C_TYPE_CODE VARCHAR2)
138 IS
139 SELECT MR_HEADER_ID
140 FROM AHL_MR_HEADERS_APP_V A
141 WHERE UPPER(TITLE)=(C_TITLE)
142 AND MR_STATUS_CODE<>'TERMINATED'
143 AND trunc(NVL(EFFECTIVE_TO,SYSDATE+1))>SYSDATE
144 AND NVL(TYPE_CODE,'X')=DECODE(C_TYPE_CODE,'ACTIVITY','PROGRAM', 'PROGRAM','ACTIVITY','X');
145
146 CURSOR c_mr_header_id_to_relmr_title(C_MR_HEADER_ID IN NUMBER,C_TYPE_CODE VARCHAR2)
147 IS
148 SELECT TITLE
149 FROM AHL_MR_HEADERS_APP_V
150 WHERE MR_HEADER_ID=C_MR_HEADER_ID
151 AND MR_STATUS_CODE<>'TERMINATED'
152 AND trunc(NVL(EFFECTIVE_TO,SYSDATE+1))>SYSDATE
153 AND NVL(TYPE_CODE,'X')=DECODE(NVL(C_TYPE_CODE,'X'),'ACTIVITY','PROGRAM','PROGRAM','ACTIVITY','X');
154
155
156 check_flag VARCHAR2(1):='N';
157 l_rel_mr_header_id AHL_MR_HEADERS_B.MR_HEADER_ID%TYPE;
158 l_api_name VARCHAR2(30):='TRANS_VALUE_ID';
159 l_type_code AHL_MR_HEADERS_B.TYPE_CODE%TYPE;
160 l_title AHL_MR_HEADERS_B.TITLE%TYPE;
161 BEGIN
162 x_return_status:=FND_API.G_RET_STS_SUCCESS;
163
164 IF G_DEBUG='Y' THEN
165 AHL_DEBUG_PUB.enable_debug;
166 AHL_DEBUG_PUB.debug( 'p_x_mr_relation_rec.related_mr_header_id'||p_x_mr_relation_rec.related_mr_header_id,'+DEBUG_RELATIONS+');
167 END IF;
168
169 IF p_x_mr_relation_rec.MR_HEADER_ID IS NOT NULL OR p_x_mr_relation_rec.MR_HEADER_ID<>FND_API.G_MISS_NUM
170 THEN
171 SELECT TYPE_CODE INTO L_TYPE_CODE
172 FROM AHL_MR_HEADERS_B
173 WHERE MR_HEADER_ID=p_x_mr_relation_rec.MR_HEADER_ID;
174 END IF;
175
176 IF p_x_mr_relation_rec.RELATED_MR_TITLE IS NULL OR p_x_mr_relation_rec.RELATED_MR_TITLE=FND_API.G_MISS_CHAR
177 THEN
178 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_NULL');
179 FND_MSG_PUB.ADD;
180 check_flag:='N';
181 ELSE
182 check_flag:='Y';
183 END IF;
184
185 IF check_flag='Y'
186 THEN
187 IF p_x_mr_relation_rec.related_mr_header_id is not null and
188 p_x_mr_relation_rec.related_mr_header_id<>fnd_api.g_miss_num
189 THEN
190 open c_mr_header_id_to_relmr_title(p_x_mr_relation_rec.related_mr_header_id,l_type_code);
191 fetch c_mr_header_id_to_relmr_title into l_title;
192 close c_mr_header_id_to_relmr_title;
193 END IF;
194
195 IF NVL(l_title,'X')<>p_x_mr_relation_rec.RELATED_MR_TITLE
196 THEN
197
198 OPEN title_to_relmr_header_id(p_x_mr_relation_rec.RELATED_MR_TITLE,nvl(l_type_code,'X'));
199 FETCH title_to_relmr_header_id INTO l_rel_mr_header_id;
200
201 IF title_to_relmr_header_id%NOTFOUND
202 THEN
203 SELECT TITLE INTO l_title
204 FROM AHL_MR_HEADERS_B
205 WHERE MR_HEADER_ID=p_x_mr_relation_rec.MR_HEADER_ID;
206
207 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATED_TITLE_INVAL');
208 FND_MESSAGE.SET_TOKEN('FIELD',l_title,false);
209 FND_MESSAGE.SET_TOKEN('RECORD', p_x_mr_relation_rec.RELATED_MR_TITLE, false);
210 FND_MSG_PUB.ADD;
211 ELSE
212 p_x_mr_relation_rec.related_mr_header_id:=l_rel_mr_header_id;
213 END IF;
214 CLOSE title_to_relmr_header_id;
215 END IF;
216 END IF;
217 AHL_DEBUG_PUB.debug( 'p_x_mr_relation_rec.related_mr_header_id'||p_x_mr_relation_rec.related_mr_header_id,'+DEBUG_RELATIONS+');
218
219 END;
220
221 PROCEDURE NON_CYCLIC_ENF
222 (
223 p_api_version IN NUMBER:=1.0,
224 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
225 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 P_MR_HEADER_ID IN NUMBER,
230 P_RELATED_MR_HEADER_ID IN NUMBER,
231 P_RELATED_MR_TITLE IN VARCHAR2
232 )
233 AS
234 l_cyclic_loop EXCEPTION;
235 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
236 l_counter NUMBER;
237 BEGIN
238 x_return_status:=FND_API.G_RET_STS_SUCCESS;
239
240 SELECT COUNT(*) INTO l_counter
241 FROM AHL_MR_RELATIONSHIPS
242 START WITH RELATED_MR_HEADER_ID=P_RELATED_MR_HEADER_ID
243 CONNECT BY PRIOR RELATED_MR_HEADER_ID=MR_HEADER_ID;
244 EXCEPTION
245 WHEN l_cyclic_loop THEN
246 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_CYCLIC');
247 FND_MESSAGE.SET_TOKEN('RECORD', P_RELATED_MR_TITLE ,false);
248 FND_MSG_PUB.ADD;
249 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
253 p_count => x_msg_count,
254 p_data => x_msg_data);
255
256 WHEN FND_API.G_EXC_ERROR THEN
257 X_return_status := FND_API.G_RET_STS_ERROR;
258 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
259 p_count => x_msg_count,
260 p_data => X_msg_data);
261 WHEN OTHERS THEN
262 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
264 THEN
265 fnd_msg_pub.add_exc_msg(p_pkg_name =>'AHL_FMP_MR_RELATION_PVT',
266 p_procedure_name =>'NON_CYCLIC_ENF',
267 p_error_text =>SUBSTR(SQLERRM,1,240));
268 END IF;
269 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
270 p_count => x_msg_count,
271 p_data => X_msg_data);
272 END;
273
274 PROCEDURE VALIDATE_MR_RELATION
275 (
276 x_return_status OUT NOCOPY VARCHAR2,
277 p_mr_relation_rec IN MR_RELATION_REC
278 )
279 as
280 CURSOR Check_mr_header_stat(C_MR_HEADER_ID IN NUMBER)
281 IS
282 SELECT MR_STATUS_CODE,TITLE,TYPE_CODE
283 FROM AHL_MR_HEADERS_B
284 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
285
286 l_head_rec Check_mr_header_stat%rowtype;
287 l_rel_head_rec Check_mr_header_stat%rowtype;
288
289 CURSOR CHECK_UNIQ(c_mr_header_id NUMBER,c_related_mr_header_id NUMBER,c_relationship_code VARCHAR2)
290 IS
291 select *
292 from AHL_MR_RELATIONSHIPS
293 where MR_HEADER_ID=C_MR_HEADER_ID
294 and RELATED_MR_HEADER_ID=C_RELATED_MR_HEADER_ID
295 and relationship_code=c_relationship_code;
296
297 l_rel_rec CHECK_UNIQ%ROWTYPE;
298
299 -- Tamal [MEL/CDL] -- Begin changes
300 CURSOR check_mo_proc
301 (
302 c_mr_header_id number
303 )
304 IS
305 SELECT 'x'
306 FROM ahl_mr_headers_b
307 WHERE mr_header_id = c_mr_header_id AND
308 program_type_code = 'MO_PROC';
309
310 l_dummy_char VARCHAR2(1);
311 -- Tamal [MEL/CDL] -- End changes
312
313 l_object_version_number number;
314 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MR_RELATION';
315 l_mr_header_id NUMBER:=0;
316 l_mr_check_flag VARCHAR2(1):='N';
317 BEGIN
318 x_return_status:=FND_API.G_RET_STS_SUCCESS;
319 IF G_DEBUG='Y' THEN
320 AHL_DEBUG_PUB.enable_debug;
321 END IF;
322 IF p_mr_relation_rec.MR_HEADER_ID IS NULL or
323 p_mr_relation_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
324 THEN
325 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
326 FND_MSG_PUB.ADD;
327 ELSE
328 OPEN Check_mr_header_stat(p_mr_relation_rec.mr_header_id);
329 FETCH Check_mr_header_stat INTO l_head_rec;
330 IF Check_mr_header_stat%FOUND
331 THEN
332 IF l_head_rec.mr_status_code='DRAFT' OR
333 l_head_rec.mr_status_code='APPROVAL_REJECTED'
334 THEN
335 IF G_DEBUG='Y' THEN
336 AHL_DEBUG_PUB.debug( 'mr_Status_code'||l_head_rec.mr_status_code,'+DEBUG_RELATIONS+');
337 END IF;
338 l_mr_check_flag:='Y';
339 ELSE
340 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR_STATUS');
341 FND_MSG_PUB.ADD;
342 IF G_DEBUG='Y' THEN
343 AHL_DEBUG_PUB.debug( 'mr_Status_code'||l_head_rec.mr_status_code,'+DEBUG_RELATIONS+');
344 END IF;
345 l_mr_check_flag:='N';
346 END IF;
347
348 IF ltrim(rtrim(l_head_rec.title))=rtrim(ltrim(p_mr_relation_rec.RELATED_MR_TITLE))
349 THEN
350 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_CYCLIC');
351 FND_MESSAGE.SET_TOKEN('RECORD',rtrim(ltrim(p_mr_relation_rec.RELATED_MR_TITLE)),false);
352 FND_MSG_PUB.ADD;
353 l_mr_check_flag:='N';
354 END IF;
355
356 ELSE
357 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
358 FND_MSG_PUB.ADD;
359 l_mr_check_flag:='N';
360 END IF;
361 CLOSE Check_mr_header_stat;
362 END IF;
363
364 IF l_mr_check_flag='Y'
365 THEN
366 IF p_mr_relation_rec.dml_operation<>'D'
367 THEN
368 IF p_mr_relation_rec.RELATED_MR_HEADER_ID IS NULL OR
369 p_mr_relation_rec.RELATED_MR_HEADER_ID=FND_API.G_MISS_NUM
370 THEN
371 FND_MESSAGE.SET_NAME('AHL','AHL_MR_HEADER_ID_NULL');
372 FND_MSG_PUB.ADD;
373 ELSE
374 OPEN Check_mr_header_stat(p_mr_relation_rec.RELATED_MR_HEADER_ID);
375 FETCH Check_mr_header_stat INTO l_rel_head_rec;
376 IF Check_mr_header_stat%FOUND
377 THEN
378 IF G_PM_INSTALL='Y'
379 THEN
380
381 IF l_head_rec.TYPE_CODE='PROGRAM' and
382 p_mr_relation_rec.relationship_code='PARENT'
383 THEN
384 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_PROGRM_RELCODE_INV');
385 FND_MESSAGE.SET_TOKEN('FIELD', l_head_rec.TITLE,false);
386 FND_MSG_PUB.ADD;
387 ELSIF l_head_rec.TYPE_CODE='ACTIVITY' and p_mr_relation_rec.relationship_code='CHILD'
388 THEN
389 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_ACTVTY_RELCODE_INV');
390 FND_MESSAGE.SET_TOKEN('FIELD', l_head_rec.TITLE,false);
391 FND_MSG_PUB.ADD;
392 END IF;
393
394 IF l_head_rec.TYPE_CODE='PROGRAM' and l_rel_head_rec.TYPE_CODE='PROGRAM'
395 THEN
396 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_PROG_2_PROG_INV');
397 FND_MESSAGE.SET_TOKEN('FIELD', p_mr_relation_rec.RELATED_MR_TITLE,false);
398 FND_MSG_PUB.ADD;
399 ELSIF l_head_rec.TYPE_CODE='ACTIVITY' and l_rel_head_rec.TYPE_CODE='ACTIVITY'
400 THEN
401 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_ACTV_2_ACTV_INV');
402 FND_MESSAGE.SET_TOKEN('FIELD', p_mr_relation_rec.RELATED_MR_TITLE,false);
403 FND_MSG_PUB.ADD;
404 END IF;
405 END IF;
406 END IF;
407 CLOSE Check_mr_header_stat;
408 END IF;
409 END IF;
410
411 IF p_mr_relation_rec.dml_operation<>'C'
412 THEN
413 IF (p_mr_relation_rec.MR_RELATIONSHIP_ID IS NULL OR
414 p_mr_relation_rec.MR_RELATIONSHIP_ID=FND_API.G_MISS_NUM)
415 THEN
416 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATIONSHIPID_NULL');
417 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE,false);
418 FND_MSG_PUB.ADD;
419 END IF;
420
421 IF (p_mr_relation_rec.OBJECT_VERSION_NUMBER IS NULL OR
422 p_mr_relation_rec.OBJECT_vERSION_NUMBER=FND_API.G_MISS_NUM)
423 THEN
424 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_REL_OBJ_VERSION_NULL');
425 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE,false);
426 FND_MSG_PUB.ADD;
427 END IF;
428 END IF;
429
430
431 IF p_mr_relation_rec.dml_operation<>'D'
432 THEN
433 IF G_DEBUG='Y' THEN
434 AHL_DEBUG_PUB.debug( 'Check Uniq Record','+DEBUG_RELATIONS+');
435 END IF;
436 IF p_mr_relation_rec.relationship_code='CHILD'
437 THEN
438 OPEN check_uniq(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'PARENT');
439 ELSE
440 OPEN check_uniq(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'PARENT');
441 END IF;
442
443 FETCH check_uniq INTO l_rel_rec;
444
445 IF check_uniq%found
446 THEN
447 IF p_mr_relation_rec.dml_operation='C'
448 THEN
449 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_DUPLICATE');
450 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
451 FND_MSG_PUB.ADD;
452 ELSIF P_mr_relation_rec.dml_operation='U'
453 THEN
454 IF l_rel_rec.MR_RELATIONSHIP_ID<>p_mr_relation_rec.MR_RELATIONSHIP_ID
455 THEN
456 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_DUPLICATE');
457 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
458 FND_MSG_PUB.ADD;
459 END IF;
460 END IF;
461 END IF;
462 CLOSE check_UNIQ;
463 END IF;
464
465 -- Tamal [MEL/CDL] -- Begin changes
466 OPEN check_mo_proc(p_mr_relation_rec.MR_HEADER_ID);
467 FETCH check_mo_proc INTO l_dummy_char;
468 IF (check_mo_proc%FOUND)
469 THEN
470 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MRL_MO_PROC');
471 -- Relationships are not available for a Maintenance Requirement of (M) and (0) procedure program type.
472 FND_MSG_PUB.ADD;
473 END IF;
474 -- Tamal [MEL/CDL] -- End changes
475 END IF;
476 END;
477
478 PROCEDURE PROCESS_MR_RELATION
479 (
480 p_api_version IN NUMBER:= 1.0,
481 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
482 p_commit IN VARCHAR2 := FND_API.G_FALSE,
483 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
484 p_default IN VARCHAR2:= FND_API.G_FALSE,
485 p_module_type IN VARCHAR2 := NULL,
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2,
489 p_x_mr_relation_tbl IN OUT NOCOPY MR_RELATION_TBL
490 )
491 as
492 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MR_RELATION';
493 l_api_version CONSTANT NUMBER := 1.0;
494 l_msg_count NUMBER;
495 l_msg_data VARCHAR2(2000);
496 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
497 l_mr_header_id NUMBER:=0;
498 l_mr_relation_rec MR_RELATION_REC;
499 BEGIN
500
501
502 SAVEPOINT PROCESS_MR_RELATION;
503
504 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
505 p_api_version,
506 l_api_name,G_PKG_NAME) THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 END IF;
509
510
511 IF FND_API.to_boolean(p_init_msg_list) THEN
512 FND_MSG_PUB.initialize;
513 END IF;
514
515
516 x_return_status:=FND_API.G_RET_STS_SUCCESS;
517
518 IF G_DEBUG='Y' THEN
519 AHL_DEBUG_PUB.enable_debug;
520 END IF;
521 /*
522 IF FND_API.to_boolean(p_default)
523 THEN
524 DEFAULT_MISSING_ATTRIBS
525 (
526 p_x_mr_relation_tbl =>p_x_mr_relation_tbl
527 );
528 END IF;
529
530 --IF p_module_type = 'JSP'
531 --THEN
532 -- FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
533 -- LOOP
534 -- p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=NULL;
535 -- END LOOP;
536 --END IF;
537 */
538
539 -- code for Value_To_ID conversion for parent MR.
540 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
541 LOOP
542 IF (
543 p_x_mr_relation_tbl(i).mr_header_id IS NULL OR
544 p_x_mr_relation_tbl(i).mr_header_id = FND_API.G_MISS_NUM
545 )
546 THEN
547 -- Function to convert mr_title,mr_version_number to id
548 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
549 p_mr_title => p_x_mr_relation_tbl(i).mr_title,
550 p_mr_version_number => p_x_mr_relation_tbl(i).mr_version_number,
551 x_mr_header_id => p_x_mr_relation_tbl(i).mr_header_id,
552 x_return_status => x_return_status
553 );
554 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
555 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
556 fnd_log.string
557 (
558 fnd_log.level_statement,
559 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
560 'Invalid MR Title, Version Number provided'
561 );
562 END IF;
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565 END IF;
566 END LOOP;
567
568 --Start of API Body
569 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
570 THEN
571
572 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
573 LOOP
574
575 l_mr_relation_rec:=p_x_mr_relation_tbl(i);
576
577 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
578 THEN
579 TRANS_VALUE_ID
580 (
581 x_return_status =>x_return_Status,
582 p_x_mr_relation_rec =>l_mr_relation_rec);
583 END IF;
584 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_relation_rec.RELATED_MR_HEADER_ID;
585
586 END LOOP;
587
588 l_msg_count := FND_MSG_PUB.count_msg;
589 IF l_msg_count > 0
590 THEN
591 X_msg_count := l_msg_count;
592 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595 END IF;
596
597 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
598 LOOP
599
600 x_return_status:=FND_API.G_RET_STS_SUCCESS;
601
602 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
603 THEN
604
605 VALIDATE_MR_RELATION
606 (
607 x_return_status =>x_return_Status,
608 p_mr_relation_rec =>p_x_mr_relation_tbl(i));
609
610 END IF;
611 l_msg_count := FND_MSG_PUB.count_msg;
612 IF l_msg_count > 0
613 THEN
614 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 END IF;
616
617 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
618 THEN
619 IF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='PARENT'
620 THEN
621 l_mr_header_id:=p_x_mr_relation_tbl(i).MR_HEADER_ID;
622 p_x_mr_relation_tbl(i).MR_HEADER_ID:=p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID;
623 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_header_id;
624 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='PARENT';
625 ELSIF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='CHILD'
626 THEN
627 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='PARENT';
628 END IF;
629 END IF;
630
631 IF nvl(x_return_status,'X')='S'
632 THEN
633 IF p_x_mr_relation_tbl(i).DML_OPERATION='D' then
634 delete AHL_MR_RELATIONSHIPS
635 where MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
636 and OBJECT_VERSION_NUMBER=p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER;
637
638 if sql%rowcount=0 then
639 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_RECORD_CHANGED');
640 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_relation_tbl(i).related_mr_title,false);
641 FND_MSG_PUB.ADD;
642 end if;
643 ELSIF p_x_mr_relation_tbl(i).DML_operation='U'
644 then
645
646 IF x_return_status=FND_API.G_RET_STS_SUCCESS
647 THEN
648
649 update AHL_mr_RELATIONSHIPS
650 set MR_HEADER_ID = p_x_mr_relation_tbl(i).MR_HEADER_ID,
651 RELATED_MR_HEADER_ID = p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
652 RELATIONSHIP_CODE = p_x_mr_relation_tbl(i).RELATIONSHIP_CODE,
653 MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID,
654 OBJECT_VERSION_NUMBER = p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER + 1,
655 ATTRIBUTE_CATEGORY = p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY,
656 ATTRIBUTE1 = p_x_mr_relation_tbl(i).ATTRIBUTE1,
657 ATTRIBUTE2 = p_x_mr_relation_tbl(i).ATTRIBUTE2,
658 ATTRIBUTE3 = p_x_mr_relation_tbl(i).ATTRIBUTE3,
659 ATTRIBUTE4 = p_x_mr_relation_tbl(i).ATTRIBUTE4,
660 ATTRIBUTE5 = p_x_mr_relation_tbl(i).ATTRIBUTE5,
661 ATTRIBUTE6 = p_x_mr_relation_tbl(i).ATTRIBUTE6,
662 ATTRIBUTE7 = p_x_mr_relation_tbl(i).ATTRIBUTE7,
663 ATTRIBUTE8 = p_x_mr_relation_tbl(i).ATTRIBUTE8,
664 ATTRIBUTE9 = p_x_mr_relation_tbl(i).ATTRIBUTE9,
665 ATTRIBUTE10 = p_x_mr_relation_tbl(i).ATTRIBUTE10,
666 ATTRIBUTE11 = p_x_mr_relation_tbl(i).ATTRIBUTE11,
667 ATTRIBUTE12 = p_x_mr_relation_tbl(i).ATTRIBUTE12,
668 ATTRIBUTE13 = p_x_mr_relation_tbl(i).ATTRIBUTE13,
669 ATTRIBUTE14 = p_x_mr_relation_tbl(i).ATTRIBUTE14,
670 ATTRIBUTE15 = p_x_mr_relation_tbl(i).ATTRIBUTE15,
671 LAST_UPDATE_DATE = sysdate,
672 LAST_UPDATED_BY = fnd_global.user_id,
673 LAST_UPDATE_LOGIN = fnd_global.user_id
674 where MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
675 and OBJECT_VERSION_NUMBER=p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER;
676
677 if sql%rowcount=0 then
678 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
679 FND_MSG_PUB.ADD;
680 end if;
681 END IF;
682
683 ELSIF p_x_mr_relation_tbl(i).DML_operation='C'
684 then
685
686 SELECT AHL_MR_RELATIONSHIPS_S.NEXTVAL
687 INTO p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
688 FROM DUAL;
689
690 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=1;
691
692 IF x_return_status=FND_API.G_RET_STS_SUCCESS
693 THEN
694 insert into AHl_mr_RELATIONSHIPS(
695 MR_RELATIONSHIP_ID,
696 OBJECT_VERSION_NUMBER,
697 LAST_UPDATE_DATE,
698 LAST_UPDATED_BY,
699 CREATION_DATE,
700 CREATED_BY,
701 LAST_UPDATE_LOGIN,
702 MR_HEADER_ID,
703 RELATED_MR_HEADER_ID,
704 RELATIONSHIP_CODE,
705 ATTRIBUTE_CATEGORY,
706 ATTRIBUTE1,
707 ATTRIBUTE2,
708 ATTRIBUTE3,
709 ATTRIBUTE4,
710 ATTRIBUTE5,
711 ATTRIBUTE6,
712 ATTRIBUTE7,
713 ATTRIBUTE8,
714 ATTRIBUTE9,
715 ATTRIBUTE10,
716 ATTRIBUTE11,
717 ATTRIBUTE12,
718 ATTRIBUTE13,
719 ATTRIBUTE14,
720 ATTRIBUTE15)
721 values(
722 p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID,
723 1,
724 sysdate,
725 fnd_global.user_id,
726 SYSDATE,
727 fnd_global.user_id,
728 fnd_global.user_id,
729 p_x_mr_relation_tbl(i).MR_HEADER_ID,
730 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
731 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE,
732 p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY,
733 p_x_mr_relation_tbl(i).ATTRIBUTE1,
734 p_x_mr_relation_tbl(i).ATTRIBUTE2,
735 p_x_mr_relation_tbl(i).ATTRIBUTE3,
736 p_x_mr_relation_tbl(i).ATTRIBUTE4,
737 p_x_mr_relation_tbl(i).ATTRIBUTE5,
738 p_x_mr_relation_tbl(i).ATTRIBUTE6,
739 p_x_mr_relation_tbl(i).ATTRIBUTE7,
740 p_x_mr_relation_tbl(i).ATTRIBUTE8,
741 p_x_mr_relation_tbl(i).ATTRIBUTE9,
742 p_x_mr_relation_tbl(i).ATTRIBUTE10,
743 p_x_mr_relation_tbl(i).ATTRIBUTE11,
744 p_x_mr_relation_tbl(i).ATTRIBUTE12,
745 p_x_mr_relation_tbl(i).ATTRIBUTE13,
746 p_x_mr_relation_tbl(i).ATTRIBUTE14,
747 p_x_mr_relation_tbl(i).ATTRIBUTE15);
748 END IF;
749 END IF;
750 END IF;
751
752 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
753 THEN
754 NON_CYCLIC_ENF
755 (
756 p_api_version =>l_api_version,
757 p_init_msg_list =>l_init_msg_list,
758 p_validation_level =>p_validation_level ,
759 x_return_status =>x_return_Status,
760 x_msg_count =>l_msg_count,
761 x_msg_data =>l_msg_data,
762 p_mr_header_id =>p_x_mr_relation_tbl(i).MR_HEADER_ID,
763 p_related_mr_header_id =>p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
764 p_related_mr_title=>p_x_mr_relation_tbl(i).RELATED_MR_TITLE);
765 END IF;
766 END LOOP;
767
768 l_msg_count := FND_MSG_PUB.count_msg;
769 IF l_msg_count > 0 THEN
770 X_msg_count := l_msg_count;
771 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774
775 IF FND_API.TO_BOOLEAN(p_commit) THEN
776 COMMIT;
777 END IF;
778
779 IF G_DEBUG='Y' THEN
780 AHL_DEBUG_PUB.disable_debug;
781 END IF;
782
783 EXCEPTION
784 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
785 ROLLBACK TO PROCESS_MR_RELATION;
786 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
788 p_count => x_msg_count,
789 p_data => x_msg_data);
790
791 WHEN FND_API.G_EXC_ERROR THEN
792 ROLLBACK TO PROCESS_MR_RELATION;
793 X_return_status := FND_API.G_RET_STS_ERROR;
794 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
795 p_count => x_msg_count,
796 p_data => X_msg_data);
797 WHEN OTHERS THEN
798 ROLLBACK TO PROCESS_MR_RELATION;
799 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 IF G_DEBUG='Y' THEN
801 AHL_DEBUG_PUB.debug( 'PROCESS-->'||sqlerrm,'DEBUG RELATIONS');
802 END IF;
803 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
804 THEN
805 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
806 p_procedure_name =>L_API_NAME,
807 p_error_text =>SUBSTR(SQLERRM,1,240));
808 END IF;
809 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
810 p_count => x_msg_count,
811 p_data => X_msg_data);
812
813 END;
814 END AHL_FMP_MR_RELATION_PVT;