[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.12020000.2 2012/12/13 05:06:27 shnatu ship $ */
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 -- pdoki added for SBE Project
10 PROCEDURE CHECK_LOOKUP_CODE
11 (
12 x_return_status OUT NOCOPY VARCHAR2,
13 p_lookup_code IN VARCHAR2,
14 p_lookup_TYPE IN VARCHAR2
15 )
16 as
17 CURSOR get_lookup_type_code(c_lookup_code VARCHAR2,c_lookup_type VARCHAR2)
18 IS
19 SELECT lookup_code
20 FROM FND_LOOKUP_VALUES_VL
21 WHERE lookup_code = c_lookup_code
22 AND lookup_type = c_lookup_type
23 AND sysdate between nvl(start_date_active,sysdate)
24 AND nvl(end_date_active,sysdate);
25
26 l_lookup_code VARCHAR2(30):=null;
27 begin
28 OPEN get_lookup_type_code(p_lookup_code,p_lookup_type);
29 FETCH get_lookup_type_code INTO l_lookup_code;
30 IF get_lookup_type_code%NOTFOUND
31 THEN
32 x_return_Status:= FND_API.G_RET_STS_UNEXP_ERROR;
33 END IF;
34 close GET_LOOKUP_TYPE_CODE;
35 end;
36
37 PROCEDURE DEFAULT_MISSING_ATTRIBS
38 (p_x_mr_relation_tbl IN OUT NOCOPY AHL_FMP_MR_relation_PVT.MR_relation_TBL)
39 AS
40
41 BEGIN
42 IF P_X_MR_relation_TBL.COUNT>0
43 THEN
44 FOR i IN P_X_MR_relation_TBL.FIRST.. P_X_MR_relation_TBL.LAST
45 LOOP
46 IF p_x_mr_relation_tbl(I).RELATIONSHIP_CODE= FND_API.G_MISS_CHAR
47 THEN
48 p_x_mr_relation_tbl(I).RELATIONSHIP_CODE:=NULL;
49 END IF;
50
51 IF p_x_mr_relation_tbl(I).MR_HEADER_ID= FND_API.G_MISS_NUM
52 THEN
53 p_x_mr_relation_tbl(I).MR_HEADER_ID:=NULL;
54 END IF;
55
56 IF p_x_mr_relation_tbl(I).RELATED_MR_HEADER_ID= FND_API.G_MISS_NUM
57 THEN
58 p_x_mr_relation_tbl(I).RELATED_MR_HEADER_ID:=NULL;
59 END IF;
60
61 IF p_x_mr_relation_tbl(I).MR_RELATIONSHIP_ID= FND_API.G_MISS_NUM
62 THEN
63 p_x_mr_relation_tbl(I).MR_RELATIONSHIP_ID:=NULL;
64 END IF;
65
66 IF p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
67 THEN
68 IF p_x_mr_relation_tbl(i).dml_operation='C'
69 THEN
70 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=1;
71 ELSE
72 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=NULL;
73 END IF;
74 END IF;
75
76 IF p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
77 THEN
78 p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY:=NULL;
79 END IF;
80
81 IF p_x_mr_relation_tbl(i).ATTRIBUTE1=FND_API.G_MISS_CHAR
82 THEN
83 p_x_mr_relation_tbl(i).ATTRIBUTE1:=NULL;
84 END IF;
85
86 IF p_x_mr_relation_tbl(i).ATTRIBUTE2=FND_API.G_MISS_CHAR
87 THEN
88 p_x_mr_relation_tbl(i).ATTRIBUTE2:=NULL;
89 END IF;
90
91 IF p_x_mr_relation_tbl(i).ATTRIBUTE3=FND_API.G_MISS_CHAR
92 THEN
93 p_x_mr_relation_tbl(i).ATTRIBUTE3:=NULL;
94 END IF;
95
96 IF p_x_mr_relation_tbl(i).ATTRIBUTE4 IS NULL OR p_x_mr_relation_tbl(i).ATTRIBUTE4=FND_API.G_MISS_CHAR
97 THEN
98 p_x_mr_relation_tbl(i).ATTRIBUTE4:=NULL;
99 END IF;
100
101 IF p_x_mr_relation_tbl(i).ATTRIBUTE5=FND_API.G_MISS_CHAR
102 THEN
103 p_x_mr_relation_tbl(i).ATTRIBUTE5:=NULL;
104 END IF;
105
106 IF p_x_mr_relation_tbl(i).ATTRIBUTE6=FND_API.G_MISS_CHAR
107 THEN
108 p_x_mr_relation_tbl(i).ATTRIBUTE6:=NULL;
109 END IF;
110
111 IF p_x_mr_relation_tbl(i).ATTRIBUTE7=FND_API.G_MISS_CHAR
112 THEN
113 p_x_mr_relation_tbl(i).ATTRIBUTE7:=NULL;
114 END IF;
115
116 IF p_x_mr_relation_tbl(i).ATTRIBUTE8=FND_API.G_MISS_CHAR
117 THEN
118 p_x_mr_relation_tbl(i).ATTRIBUTE8:=NULL;
119 END IF;
120
121 IF p_x_mr_relation_tbl(i).ATTRIBUTE9=FND_API.G_MISS_CHAR
122 THEN
123 p_x_mr_relation_tbl(i).ATTRIBUTE9:=NULL;
124 END IF;
125
126 IF p_x_mr_relation_tbl(i).ATTRIBUTE10=FND_API.G_MISS_CHAR
127 THEN
128 p_x_mr_relation_tbl(i).ATTRIBUTE10:=NULL;
129 END IF;
130
131 IF p_x_mr_relation_tbl(i).ATTRIBUTE11=FND_API.G_MISS_CHAR
132 THEN
133 p_x_mr_relation_tbl(i).ATTRIBUTE11:=NULL;
134 END IF;
135
136 IF p_x_mr_relation_tbl(i).ATTRIBUTE12=FND_API.G_MISS_CHAR
137 THEN
138 p_x_mr_relation_tbl(i).ATTRIBUTE12:=NULL;
139 END IF;
140
141 IF p_x_mr_relation_tbl(i).ATTRIBUTE13=FND_API.G_MISS_CHAR
142 THEN
143 p_x_mr_relation_tbl(i).ATTRIBUTE13:=NULL;
144 END IF;
145
146 IF p_x_mr_relation_tbl(i).ATTRIBUTE14=FND_API.G_MISS_CHAR
147 THEN
148 p_x_mr_relation_tbl(i).ATTRIBUTE14:=NULL;
149 END IF;
150
151 IF p_x_mr_relation_tbl(i).ATTRIBUTE15=FND_API.G_MISS_CHAR
152 THEN
153 p_x_mr_relation_tbl(i).ATTRIBUTE15:=NULL;
154 END IF;
155 END LOOP;
156 END IF;
157 END;
158
159 PROCEDURE TRANS_VALUE_ID
160 (
161 x_return_status OUT NOCOPY VARCHAR2,
162 p_x_mr_relation_rec IN OUT NOCOPY MR_RELATION_REC
163 )
164 as
165 CURSOR title_to_relmr_header_id(C_TITLE IN VARCHAR2,C_TYPE_CODE VARCHAR2)
166 IS
167 SELECT MR_HEADER_ID
168 FROM AHL_MR_HEADERS_APP_V A
169 WHERE UPPER(TITLE)=(C_TITLE)
170 AND MR_STATUS_CODE<>'TERMINATED'
171 AND trunc(NVL(EFFECTIVE_TO,SYSDATE+1))>SYSDATE
172 AND NVL(TYPE_CODE,'X')=DECODE(C_TYPE_CODE,'ACTIVITY','PROGRAM', 'PROGRAM','ACTIVITY','X');
173
174 CURSOR c_mr_header_id_to_relmr_title(C_MR_HEADER_ID IN NUMBER,C_TYPE_CODE VARCHAR2)
175 IS
176 SELECT TITLE
177 FROM AHL_MR_HEADERS_APP_V
178 WHERE MR_HEADER_ID=C_MR_HEADER_ID
179 AND MR_STATUS_CODE<>'TERMINATED'
180 AND trunc(NVL(EFFECTIVE_TO,SYSDATE+1))>SYSDATE
181 AND NVL(TYPE_CODE,'X')=DECODE(NVL(C_TYPE_CODE,'X'),'ACTIVITY','PROGRAM','PROGRAM','ACTIVITY','X');
182
183
184 check_flag VARCHAR2(1):='N';
185 l_rel_mr_header_id AHL_MR_HEADERS_B.MR_HEADER_ID%TYPE;
186 l_api_name VARCHAR2(30):='TRANS_VALUE_ID';
187 l_type_code AHL_MR_HEADERS_B.TYPE_CODE%TYPE;
188 l_title AHL_MR_HEADERS_B.TITLE%TYPE;
189 BEGIN
190 x_return_status:=FND_API.G_RET_STS_SUCCESS;
191
192 IF G_DEBUG='Y' THEN
193 AHL_DEBUG_PUB.enable_debug;
194 AHL_DEBUG_PUB.debug( 'p_x_mr_relation_rec.related_mr_header_id'||p_x_mr_relation_rec.related_mr_header_id,'+DEBUG_RELATIONS+');
195 END IF;
196
197 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
198 THEN
199 SELECT TYPE_CODE INTO L_TYPE_CODE
200 FROM AHL_MR_HEADERS_B
201 WHERE MR_HEADER_ID=p_x_mr_relation_rec.MR_HEADER_ID;
202 END IF;
203
204 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
205 THEN
206 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_NULL');
207 FND_MSG_PUB.ADD;
208 check_flag:='N';
209 ELSE
210 check_flag:='Y';
211 END IF;
212
213 IF check_flag='Y'
214 THEN
215 IF p_x_mr_relation_rec.related_mr_header_id is not null and
216 p_x_mr_relation_rec.related_mr_header_id<>fnd_api.g_miss_num
217 THEN
218 open c_mr_header_id_to_relmr_title(p_x_mr_relation_rec.related_mr_header_id,l_type_code);
219 fetch c_mr_header_id_to_relmr_title into l_title;
220 close c_mr_header_id_to_relmr_title;
221 END IF;
222
223 IF NVL(l_title,'X')<>p_x_mr_relation_rec.RELATED_MR_TITLE
224 THEN
225
226 OPEN title_to_relmr_header_id(p_x_mr_relation_rec.RELATED_MR_TITLE,nvl(l_type_code,'X'));
227 FETCH title_to_relmr_header_id INTO l_rel_mr_header_id;
228
229 IF title_to_relmr_header_id%NOTFOUND
230 THEN
231 SELECT TITLE INTO l_title
232 FROM AHL_MR_HEADERS_B
233 WHERE MR_HEADER_ID=p_x_mr_relation_rec.MR_HEADER_ID;
234
235 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATED_TITLE_INVAL');
236 FND_MESSAGE.SET_TOKEN('FIELD',l_title,false);
237 FND_MESSAGE.SET_TOKEN('RECORD', p_x_mr_relation_rec.RELATED_MR_TITLE, false);
238 FND_MSG_PUB.ADD;
239 ELSE
240 p_x_mr_relation_rec.related_mr_header_id:=l_rel_mr_header_id;
241 END IF;
242 CLOSE title_to_relmr_header_id;
243 END IF;
244 END IF;
245 AHL_DEBUG_PUB.debug( 'p_x_mr_relation_rec.related_mr_header_id::'||p_x_mr_relation_rec.related_mr_header_id);
246
247 END;
248
249 PROCEDURE NON_CYCLIC_ENF
250 (
251 p_api_version IN NUMBER:=1.0,
252 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
253 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 P_MR_HEADER_ID IN NUMBER,
258 P_RELATED_MR_HEADER_ID IN NUMBER,
259 P_RELATED_MR_TITLE IN VARCHAR2,
260 -- pdoki added for SBE Project
261 P_RELATIONSHIP_CODE IN VARCHAR2
262 )
263 AS
264 l_cyclic_loop EXCEPTION;
265 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
266 l_counter NUMBER;
267 BEGIN
268 x_return_status:=FND_API.G_RET_STS_SUCCESS;
269
270 SELECT COUNT(*) INTO l_counter
271 FROM AHL_MR_RELATIONSHIPS
272 WHERE RELATIONSHIP_CODE = P_RELATIONSHIP_CODE -- pdoki added for SBE Project
273 START WITH RELATED_MR_HEADER_ID=P_RELATED_MR_HEADER_ID
274 CONNECT BY PRIOR RELATED_MR_HEADER_ID=MR_HEADER_ID;
275 EXCEPTION
276 WHEN l_cyclic_loop THEN
277 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_CYCLIC');
278 FND_MESSAGE.SET_TOKEN('RECORD', P_RELATED_MR_TITLE ,false);
279 FND_MSG_PUB.ADD;
280 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
282 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
284 p_count => x_msg_count,
285 p_data => x_msg_data);
286
287 WHEN FND_API.G_EXC_ERROR THEN
288 X_return_status := FND_API.G_RET_STS_ERROR;
289 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
290 p_count => x_msg_count,
291 p_data => X_msg_data);
292 WHEN OTHERS THEN
293 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 fnd_msg_pub.add_exc_msg(p_pkg_name =>'AHL_FMP_MR_RELATION_PVT',
297 p_procedure_name =>'NON_CYCLIC_ENF',
298 p_error_text =>SUBSTR(SQLERRM,1,240));
299 END IF;
300 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
301 p_count => x_msg_count,
302 p_data => X_msg_data);
303 END;
304
305 PROCEDURE VALIDATE_MR_RELATION
306 (
307 x_return_status OUT NOCOPY VARCHAR2,
308 p_mr_relation_rec IN MR_RELATION_REC
309 )
310 as
311 CURSOR Check_mr_header_stat(C_MR_HEADER_ID IN NUMBER)
312 IS
313 SELECT MR_STATUS_CODE,TITLE,TYPE_CODE
314 FROM AHL_MR_HEADERS_B
315 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
316
317 l_head_rec Check_mr_header_stat%rowtype;
318 l_rel_head_rec Check_mr_header_stat%rowtype;
319 L_TITLE AHL_MR_HEADERS_B.TITLE%type;
320 L_MR_TITLE AHL_MR_HEADERS_B.TITLE%type;
321 l_counter number;
322
323 CURSOR CHECK_UNIQ(c_mr_header_id NUMBER,c_related_mr_header_id NUMBER,c_relationship_code VARCHAR2)
324 IS
325 select *
326 from AHL_MR_RELATIONSHIPS
327 where MR_HEADER_ID=C_MR_HEADER_ID
328 and RELATED_MR_HEADER_ID=C_RELATED_MR_HEADER_ID
329 and relationship_code=c_relationship_code;
330
331 -- pdoki added for SBE Project, Start.
332 CURSOR CHECK_UNIQ_RELN(c_mr_header_id NUMBER,c_related_mr_header_id NUMBER,c_relationship_code VARCHAR2)
333 IS
334 select *
335 FROM AHL_MR_RELATIONSHIPS
336 WHERE RELATIONSHIP_CODE NOT IN (C_RELATIONSHIP_CODE, 'PARENT')
337 AND ((MR_HEADER_ID=C_MR_HEADER_ID AND RELATED_MR_HEADER_ID=C_RELATED_MR_HEADER_ID)
338 OR (MR_HEADER_ID = C_RELATED_MR_HEADER_ID AND RELATED_MR_HEADER_ID = C_MR_HEADER_ID));
339
340
341 CURSOR Check_Unplanned_MR(c_related_mr_header_id NUMBER)
342 IS
343 SELECT TITLE
344 FROM AHL_MR_HEADERS_B
345 WHERE MR_HEADER_ID=C_RELATED_MR_HEADER_ID
346 AND IMPLEMENT_STATUS_CODE = 'OPTIONAL_DO_NOT_IMPLEMENT';
347
348
349 CURSOR CHECK_GROUP_MR(c_related_mr_header_id NUMBER)
350 IS
351 select *
352 from AHL_MR_RELATIONSHIPS
353 where relationship_code = 'PARENT'
354 and (MR_HEADER_ID=C_RELATED_MR_HEADER_ID or RELATED_MR_HEADER_ID=C_RELATED_MR_HEADER_ID);
355
356
357
358 CURSOR CHECK_ACCOM_TRIGGER(c_related_mr_header_id NUMBER)
359 IS
360 select *
361 from AHL_MR_RELATIONSHIPS
362 where relationship_code = 'TERMINATES'
363 and (MR_HEADER_ID=C_RELATED_MR_HEADER_ID or RELATED_MR_HEADER_ID=C_RELATED_MR_HEADER_ID);
364
365
366
367 CURSOR CHK_START_MR_IN_CHAIN (c_related_mr_header_id NUMBER)
368 IS
369 select 'x'
370 from AHL_MR_LOOP_CHAIN_RELNS
371 where MR_HEADER_ID= c_related_mr_header_id
372 and MR_RELATIONSHIP_ID = START_MR_RELATIONSHIP_ID
373 and relationship_code = 'CHAIN';
374
375
376 l_result VARCHAR2(10);
377 l_result1 VARCHAR2(10);
378 -- pdoki added for SBE Project, End.
379 l_rel_rec CHECK_UNIQ%ROWTYPE;
380
381 -- Tamal [MEL/CDL] -- Begin changes
382 CURSOR check_mo_proc
383 (
384 c_mr_header_id number
385 )
386 IS
387 SELECT 'x'
388 FROM ahl_mr_headers_b
389 WHERE mr_header_id = c_mr_header_id AND
390 program_type_code = 'MO_PROC';
391
392 l_dummy_char VARCHAR2(1);
393 -- Tamal [MEL/CDL] -- End changes
394
395 --sukhwsin::Complex Assembly Maintenance Changes - starts
396 --Cursor to get Implementation status code for MR
397 CURSOR get_mr_impl_status(c_mr_header_id NUMBER) IS
398 SELECT implement_status_code, title, version_number
399 FROM ahl_mr_headers_b
400 WHERE mr_header_id = c_mr_header_id;
401 --Local Variable Declation for Complex Assembly Changes
402 l_mr_version_number ahl_mr_headers_b.version_number%TYPE;
403 l_implement_sts_code ahl_mr_headers_b.implement_status_code%TYPE;
404 --sukhwsin::Complex Assembly Maintenance Changes - ends
405 l_object_version_number number;
406 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MR_RELATION';
407 l_mr_header_id NUMBER:=0;
408 l_mr_check_flag VARCHAR2(1):='N';
409 l_return_status VARCHAR2(1);
410 BEGIN
411 x_return_status:=FND_API.G_RET_STS_SUCCESS;
412 IF G_DEBUG='Y' THEN
413 AHL_DEBUG_PUB.enable_debug;
414 END IF;
415 IF p_mr_relation_rec.MR_HEADER_ID IS NULL or
416 p_mr_relation_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
417 THEN
418 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
419 FND_MSG_PUB.ADD;
420 ELSE
421 OPEN Check_mr_header_stat(p_mr_relation_rec.mr_header_id);
422 FETCH Check_mr_header_stat INTO l_head_rec;
423 IF Check_mr_header_stat%FOUND
424 THEN
425 IF l_head_rec.mr_status_code='DRAFT' OR
426 l_head_rec.mr_status_code='APPROVAL_REJECTED'
427 THEN
428 IF G_DEBUG='Y' THEN
429 AHL_DEBUG_PUB.debug( 'mr_Status_code'||l_head_rec.mr_status_code,'+DEBUG_RELATIONS+');
430 END IF;
431 l_mr_check_flag:='Y';
432 ELSE
433 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR_STATUS');
434 FND_MSG_PUB.ADD;
435 IF G_DEBUG='Y' THEN
436 AHL_DEBUG_PUB.debug( 'mr_Status_code'||l_head_rec.mr_status_code,'+DEBUG_RELATIONS+');
437 END IF;
438 l_mr_check_flag:='N';
439 END IF;
440
441 IF ltrim(rtrim(l_head_rec.title))=rtrim(ltrim(p_mr_relation_rec.RELATED_MR_TITLE))
442 THEN
443 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_CYCLIC');
444 FND_MESSAGE.SET_TOKEN('RECORD',rtrim(ltrim(p_mr_relation_rec.RELATED_MR_TITLE)),false);
445 FND_MSG_PUB.ADD;
446 l_mr_check_flag:='N';
447 END IF;
448
449 ELSE
450 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
451 FND_MSG_PUB.ADD;
452 l_mr_check_flag:='N';
453 END IF;
454 CLOSE Check_mr_header_stat;
455 END IF;
456
457 IF l_mr_check_flag='Y'
458 THEN
459 IF p_mr_relation_rec.dml_operation<>'D'
460 THEN
461 IF p_mr_relation_rec.RELATED_MR_HEADER_ID IS NULL OR
462 p_mr_relation_rec.RELATED_MR_HEADER_ID=FND_API.G_MISS_NUM
463 THEN
464 FND_MESSAGE.SET_NAME('AHL','AHL_MR_HEADER_ID_NULL');
465 FND_MSG_PUB.ADD;
466 ELSE
467 OPEN Check_mr_header_stat(p_mr_relation_rec.RELATED_MR_HEADER_ID);
468 FETCH Check_mr_header_stat INTO l_rel_head_rec;
469 IF Check_mr_header_stat%FOUND
470 THEN
471 IF G_PM_INSTALL='Y'
472 THEN
473
474 IF l_head_rec.TYPE_CODE='PROGRAM' and
475 p_mr_relation_rec.relationship_code='PARENT'
476 THEN
477 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_PROGRM_RELCODE_INV');
478 FND_MESSAGE.SET_TOKEN('FIELD', l_head_rec.TITLE,false);
479 FND_MSG_PUB.ADD;
480 ELSIF l_head_rec.TYPE_CODE='ACTIVITY' and p_mr_relation_rec.relationship_code='CHILD'
481 THEN
482 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_ACTVTY_RELCODE_INV');
483 FND_MESSAGE.SET_TOKEN('FIELD', l_head_rec.TITLE,false);
484 FND_MSG_PUB.ADD;
485 END IF;
486
487 IF l_head_rec.TYPE_CODE='PROGRAM' and l_rel_head_rec.TYPE_CODE='PROGRAM'
488 THEN
489 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_PROG_2_PROG_INV');
490 FND_MESSAGE.SET_TOKEN('FIELD', p_mr_relation_rec.RELATED_MR_TITLE,false);
491 FND_MSG_PUB.ADD;
492 ELSIF l_head_rec.TYPE_CODE='ACTIVITY' and l_rel_head_rec.TYPE_CODE='ACTIVITY'
493 THEN
494 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_ACTV_2_ACTV_INV');
495 FND_MESSAGE.SET_TOKEN('FIELD', p_mr_relation_rec.RELATED_MR_TITLE,false);
496 FND_MSG_PUB.ADD;
497 END IF;
498 END IF;
499 END IF;
500 CLOSE Check_mr_header_stat;
501 END IF;
502 END IF;
503
504 IF p_mr_relation_rec.dml_operation<>'C'
505 THEN
506 IF (p_mr_relation_rec.MR_RELATIONSHIP_ID IS NULL OR
507 p_mr_relation_rec.MR_RELATIONSHIP_ID=FND_API.G_MISS_NUM)
508 THEN
509 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATIONSHIPID_NULL');
510 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE,false);
511 FND_MSG_PUB.ADD;
512 END IF;
513
514 IF (p_mr_relation_rec.OBJECT_VERSION_NUMBER IS NULL OR
515 p_mr_relation_rec.OBJECT_vERSION_NUMBER=FND_API.G_MISS_NUM)
516 THEN
517 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_REL_OBJ_VERSION_NULL');
518 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE,false);
519 FND_MSG_PUB.ADD;
520 END IF;
521 END IF;
522
523
524 IF p_mr_relation_rec.dml_operation<>'D'
525 THEN
526 -- pdoki added for SBE Project, Start.
527 IF p_mr_relation_rec.relationship_code IS NOT NULL OR p_mr_relation_rec.relationship_code<>FND_API.G_MISS_CHAR
528 THEN
529
530 CHECK_LOOKUP_CODE
531 (
532 x_return_status =>l_return_status,
533 p_lookup_code =>p_mr_relation_rec.relationship_code,
534 p_lookup_TYPE =>'AHL_FMP_MR_RELATIONSHIP'
535 );
536 IF l_return_status<>'S'
537 THEN
538 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_INVALID');
539 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_relation_rec.relationship_code,false);
540 FND_MSG_PUB.ADD;
541 END IF;
542 END IF;
543
544 IF p_mr_relation_rec.relationship_code = 'CHILD' OR p_mr_relation_rec.relationship_code ='PARENT' THEN
545
546 l_result := AHL_MR_LOOP_CHAIN_RELNS_PVT.IS_MR_IN_LOOP_OR_CHAIN(p_mr_relation_rec.mr_header_id);
547 l_result1 := AHL_MR_LOOP_CHAIN_RELNS_PVT.IS_MR_IN_LOOP_OR_CHAIN(p_mr_relation_rec.related_mr_header_id);
548 IF l_result IN ('LOOP','CHAIN') OR l_result1 IN ('LOOP','CHAIN') THEN
549 IF l_result IN ('LOOP','CHAIN') THEN
550 select TITLE into l_mr_title from AHL_MR_HEADERS_B where mr_header_id = p_mr_relation_rec.mr_header_id;
551 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_LOOP_OR_CHAIN');
552 FND_MESSAGE.SET_TOKEN('RECORD', l_mr_title, false);
553 FND_MSG_PUB.ADD;
554
555 END IF;
556 IF l_result1 IN ('LOOP','CHAIN') THEN
557 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_LOOP_OR_CHAIN');
558 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
559 FND_MSG_PUB.ADD;
560
561 END IF;
562
563 END IF;
564
565
566 OPEN CHECK_ACCOM_TRIGGER(p_mr_relation_rec.related_mr_header_id);
567 FETCH CHECK_ACCOM_TRIGGER into l_rel_rec;
568
569 IF CHECK_ACCOM_TRIGGER%FOUND THEN
570 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ACCOM_TRIGGER');
571 FND_MESSAGE.SET_TOKEN('TITLE', p_mr_relation_rec.RELATED_MR_TITLE, false);
572 FND_MSG_PUB.ADD;
573 END IF;
574 CLOSE CHECK_ACCOM_TRIGGER;
575
576
577 OPEN CHECK_ACCOM_TRIGGER(p_mr_relation_rec.mr_header_id);
578 FETCH CHECK_ACCOM_TRIGGER into l_rel_rec;
579
580 IF CHECK_ACCOM_TRIGGER%FOUND THEN
581 select title into l_mr_title from AHL_MR_HEADERS_B where MR_HEADER_ID = P_MR_RELATION_REC.mr_header_id;
582 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ACCOM_TRIGGER');
583 FND_MESSAGE.SET_TOKEN('TITLE', l_mr_title, false);
584 FND_MSG_PUB.ADD;
585 END IF;
586 CLOSE CHECK_ACCOM_TRIGGER;
587
588 END IF;
589
590
591
592 IF p_mr_relation_rec.relationship_code = 'CHILD' OR
593 p_mr_relation_rec.relationship_code ='PARENT' OR
594 p_mr_relation_rec.relationship_code = 'INITIATES' OR
595 p_mr_relation_rec.relationship_code ='INITIATEDBY' OR
596 p_mr_relation_rec.relationship_code ='TERMINATES' OR
597 p_mr_relation_rec.relationship_code ='TERMINATEDBY' THEN
598
599
600 IF G_DEBUG='Y' THEN
601 AHL_DEBUG_PUB.debug( 'Check Uniq Record');
602 END IF;
603
604 IF p_mr_relation_rec.relationship_code='CHILD' THEN
605 OPEN check_uniq(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'PARENT');
606 ELSIF p_mr_relation_rec.relationship_code='PARENT' THEN
607 OPEN check_uniq(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'PARENT');
608 ELSIF p_mr_relation_rec.relationship_code='INITIATES' THEN
609 OPEN check_uniq(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'INITIATES');
610 ELSIF p_mr_relation_rec.relationship_code='INITIATEDBY' THEN
611 OPEN check_uniq(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'INITIATES');
612 ELSIF p_mr_relation_rec.relationship_code='TERMINATES' THEN
613 OPEN check_uniq(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'TERMINATES');
614 ELSIF p_mr_relation_rec.relationship_code='TERMINATEDBY' THEN
615 OPEN check_uniq(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'TERMINATES');
616 END IF;
617
618
619 FETCH check_uniq INTO l_rel_rec;
620
621 IF check_uniq%found
622 THEN
623 IF p_mr_relation_rec.dml_operation='C'
624 THEN
625 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_DUPLICATE');
626 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
627 FND_MSG_PUB.ADD;
628 ELSIF P_mr_relation_rec.dml_operation='U'
629 THEN
630 IF l_rel_rec.MR_RELATIONSHIP_ID<>p_mr_relation_rec.MR_RELATIONSHIP_ID
631 THEN
632 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_RELATION_DUPLICATE');
633 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
634 FND_MSG_PUB.ADD;
635 END IF;
636 END IF;
637 END IF;
638 CLOSE check_UNIQ;
639
640 END IF;
641
642
643
644 IF p_mr_relation_rec.relationship_code = 'INITIATES' OR
645 p_mr_relation_rec.relationship_code ='INITIATEDBY' OR
646 p_mr_relation_rec.relationship_code ='TERMINATES' OR
647 p_mr_relation_rec.relationship_code ='TERMINATEDBY' THEN
648
649
650
651 IF p_mr_relation_rec.relationship_code='INITIATES' THEN
652 OPEN CHECK_UNIQ_RELN(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'INITIATES');
653 ELSIF p_mr_relation_rec.relationship_code='TERMINATES' THEN
654 OPEN CHECK_UNIQ_RELN(p_mr_relation_rec.mr_header_id,p_mr_relation_rec.related_mr_header_id,'TERMINATES');
655 ELSIF p_mr_relation_rec.relationship_code='INITIATEDBY' THEN
656 OPEN CHECK_UNIQ_RELN(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'INITIATES');
657 ELSIF p_mr_relation_rec.relationship_code='TERMINATEDBY' THEN
658 OPEN CHECK_UNIQ_RELN(p_mr_relation_rec.related_mr_header_id,p_mr_relation_rec.mr_header_id,'TERMINATES');
659 END IF;
660
661
662 FETCH CHECK_UNIQ_RELN INTO l_rel_rec;
663
664 IF CHECK_UNIQ_RELN%FOUND THEN
665
666 IF p_mr_relation_rec.dml_operation='C'
667 THEN
668 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_MANY_RELNS');
669 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
670 FND_MSG_PUB.ADD;
671 ELSIF P_mr_relation_rec.dml_operation='U'
672 THEN
673 IF l_rel_rec.MR_RELATIONSHIP_ID<>p_mr_relation_rec.MR_RELATIONSHIP_ID
674 THEN
675 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_MANY_RELNS');
676 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_relation_rec.RELATED_MR_TITLE, false);
677 FND_MSG_PUB.ADD;
678 END IF;
679 END IF;
680
681 END IF;
682 CLOSE CHECK_UNIQ_RELN;
683
684
685 OPEN CHECK_UNPLANNED_MR(P_MR_RELATION_REC.RELATED_MR_HEADER_ID);
686 FETCH Check_Unplanned_MR into l_title;
687
688 IF Check_Unplanned_MR%FOUND THEN
689 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_UNPLANNED');
690 FND_MESSAGE.SET_TOKEN('TITLE', l_title, false);
691 FND_MSG_PUB.ADD;
692 END IF;
693 close CHECK_UNPLANNED_MR;
694
695 l_result := AHL_MR_LOOP_CHAIN_RELNS_PVT.IS_MR_IN_LOOP_OR_CHAIN(p_mr_relation_rec.mr_header_id);
696 l_result1 := AHL_MR_LOOP_CHAIN_RELNS_PVT.IS_MR_IN_LOOP_OR_CHAIN(p_mr_relation_rec.related_mr_header_id);
697
698 select title into l_mr_title from AHL_MR_HEADERS_B where MR_HEADER_ID = P_MR_RELATION_REC.mr_header_id;
699 l_title := p_mr_relation_rec.RELATED_MR_TITLE;
700
701
702 IF l_result = 'LOOP' OR l_result1 ='LOOP' THEN
703 IF l_result1 = 'LOOP' THEN
704 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_LOOP');
705 FND_MESSAGE.SET_TOKEN('TITLE', l_title, false);
706 FND_MSG_PUB.ADD;
707
708 ELSIF l_result = 'LOOP' THEN
709 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_LOOP');
710 FND_MESSAGE.SET_TOKEN('TITLE', l_mr_title, false);
711 FND_MSG_PUB.ADD;
712
713 END IF;
714 ELSIF l_result = 'CHAIN' OR l_result1 ='CHAIN' THEN
715 IF l_result1 ='CHAIN' THEN
716 IF P_MR_RELATION_REC.RELATIONSHIP_CODE = 'INITIATES' THEN
717
718 OPEN CHK_START_MR_IN_CHAIN(p_mr_relation_rec.related_mr_header_id);
719 FETCH CHK_START_MR_IN_CHAIN into l_dummy_char;
720
721 IF CHK_START_MR_IN_CHAIN%NOTFOUND THEN
722
723 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_CHAIN');
724 FND_MESSAGE.SET_TOKEN('TITLE', l_title, false);
725 FND_MSG_PUB.ADD;
726
727 END IF;
728 CLOSE CHK_START_MR_IN_CHAIN;
729
730 ELSE
731
732 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_CHAIN');
733 FND_MESSAGE.SET_TOKEN('TITLE', l_title, false);
734 FND_MSG_PUB.ADD;
735
736 END IF;
737 ELSIF l_result ='CHAIN' THEN
738 IF P_MR_RELATION_REC.RELATIONSHIP_CODE = 'INITIATEDBY' THEN
739
740 OPEN CHK_START_MR_IN_CHAIN(p_mr_relation_rec.mr_header_id);
741 FETCH CHK_START_MR_IN_CHAIN into l_dummy_char;
742
743 IF CHK_START_MR_IN_CHAIN%NOTFOUND THEN
744
745 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_CHAIN');
746 FND_MESSAGE.SET_TOKEN('TITLE', l_mr_title, false);
747 FND_MSG_PUB.ADD;
748
749 END IF;
750 CLOSE CHK_START_MR_IN_CHAIN;
751
752 ELSE
753
754 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IN_CHAIN');
755 FND_MESSAGE.SET_TOKEN('TITLE', l_mr_title, false);
756 FND_MSG_PUB.ADD;
757
758 END IF;
759
760 END IF;
761 END IF;
762
763 END IF;
764
765 END IF;
766
767 IF p_mr_relation_rec.relationship_code ='TERMINATES' OR p_mr_relation_rec.relationship_code ='TERMINATEDBY' THEN
768 OPEN CHECK_GROUP_MR(p_mr_relation_rec.related_mr_header_id);
769 FETCH CHECK_GROUP_MR into l_rel_rec;
770
771 IF CHECK_GROUP_MR%FOUND THEN
772 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_GROUP_MR');
773 FND_MESSAGE.SET_TOKEN('TITLE', p_mr_relation_rec.RELATED_MR_TITLE, false);
774 FND_MSG_PUB.ADD;
775 END IF;
776 CLOSE CHECK_GROUP_MR;
777
778 OPEN CHECK_GROUP_MR(p_mr_relation_rec.mr_header_id);
779 FETCH CHECK_GROUP_MR into l_rel_rec;
780
781 IF CHECK_GROUP_MR%FOUND THEN
782 select title into l_mr_title from AHL_MR_HEADERS_B where MR_HEADER_ID = P_MR_RELATION_REC.mr_header_id;
783 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_GROUP_MR');
784 FND_MESSAGE.SET_TOKEN('TITLE', l_mr_title, false);
785 FND_MSG_PUB.ADD;
786 END IF;
787 CLOSE CHECK_GROUP_MR;
788 END IF;
789 -- pdoki added for SBE Project, End.
790
791 -- Tamal [MEL/CDL] -- Begin changes
792 OPEN check_mo_proc(p_mr_relation_rec.MR_HEADER_ID);
793 FETCH check_mo_proc INTO l_dummy_char;
794 IF (check_mo_proc%FOUND)
795 THEN
796 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MRL_MO_PROC');
797 -- Relationships are not available for a Maintenance Requirement of (M) and (0) procedure program type.
798 FND_MSG_PUB.ADD;
799 END IF;
800 -- Tamal [MEL/CDL] -- End changes
801 --sukhwsin::Complex Assembly Changes - starts
802 IF (p_mr_relation_rec.dml_operation<>'D') THEN
803 --If mr_header_id OR related_mr_header_id has implement status as Soft Limit, then raise error.
804 l_implement_sts_code := NULL;
805 l_title := NULL;
806 l_mr_version_number := NULL;
807 OPEN get_mr_impl_status(p_mr_relation_rec.mr_header_id);
808 FETCH get_mr_impl_status INTO l_implement_sts_code, l_title, l_mr_version_number;
809 CLOSE get_mr_impl_status;
810 IF (l_implement_sts_code IS NOT NULL AND l_implement_sts_code = 'SOFT_LIMIT') THEN
811 FND_MESSAGE.SET_NAME('AHL','AHL_CAM_MR_IMP_STS_ERR2');
812 FND_MESSAGE.SET_TOKEN('TITLE', l_title);
813 FND_MESSAGE.SET_TOKEN('VRSN', l_mr_version_number);
814 FND_MSG_PUB.ADD;
815 END IF;
816 --Perform this validation on related_mr_header_id
817 l_implement_sts_code := NULL;
818 l_title := NULL;
819 l_mr_version_number := NULL;
820 OPEN get_mr_impl_status(p_mr_relation_rec.related_mr_header_id);
821 FETCH get_mr_impl_status INTO l_implement_sts_code, l_title, l_mr_version_number;
822 CLOSE get_mr_impl_status;
823 IF (l_implement_sts_code IS NOT NULL AND l_implement_sts_code = 'SOFT_LIMIT') THEN
824 FND_MESSAGE.SET_NAME('AHL','AHL_CAM_MR_IMP_STS_ERR2');
825 FND_MESSAGE.SET_TOKEN('TITLE', l_title);
826 FND_MESSAGE.SET_TOKEN('VRSN', l_mr_version_number);
827 FND_MSG_PUB.ADD;
828 END IF;
829 END IF;
830 --sukhwsin::Complex Assembly Changes - ends
831 END IF;
832 END;
833
834 PROCEDURE PROCESS_MR_RELATION
835 (
836 p_api_version IN NUMBER:= 1.0,
837 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
838 p_commit IN VARCHAR2 := FND_API.G_FALSE,
839 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
840 p_default IN VARCHAR2:= FND_API.G_FALSE,
841 p_module_type IN VARCHAR2 := NULL,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_x_mr_relation_tbl IN OUT NOCOPY MR_RELATION_TBL
846 )
847 as
848 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MR_RELATION';
849 l_api_version CONSTANT NUMBER := 1.0;
850 l_msg_count NUMBER;
851 l_msg_data VARCHAR2(2000);
852 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
853 l_mr_header_id NUMBER:=0;
854 l_mr_relation_rec MR_RELATION_REC;
855 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE;
856 BEGIN
857
858
859 SAVEPOINT PROCESS_MR_RELATION;
860
861 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
862 p_api_version,
863 l_api_name,G_PKG_NAME) THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 END IF;
866
867
868 IF FND_API.to_boolean(p_init_msg_list) THEN
869 FND_MSG_PUB.initialize;
870 END IF;
871
872
873 x_return_status:=FND_API.G_RET_STS_SUCCESS;
874
875 IF G_DEBUG='Y' THEN
876 AHL_DEBUG_PUB.enable_debug;
877 END IF;
878 /*
879 IF FND_API.to_boolean(p_default)
880 THEN
881 DEFAULT_MISSING_ATTRIBS
882 (
883 p_x_mr_relation_tbl =>p_x_mr_relation_tbl
884 );
885 END IF;
886
887 --IF p_module_type = 'JSP'
888 --THEN
889 -- FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
890 -- LOOP
891 -- p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=NULL;
892 -- END LOOP;
893 --END IF;
894 */
895
896 -- code for Value_To_ID conversion for parent MR.
897 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
898 LOOP
899 IF (
900 p_x_mr_relation_tbl(i).mr_header_id IS NULL OR
901 p_x_mr_relation_tbl(i).mr_header_id = FND_API.G_MISS_NUM
902 )
903 THEN
904 -- Function to convert mr_title,mr_version_number to id
905 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
906 p_mr_title => p_x_mr_relation_tbl(i).mr_title,
907 p_mr_version_number => p_x_mr_relation_tbl(i).mr_version_number,
908 x_mr_header_id => p_x_mr_relation_tbl(i).mr_header_id,
909 x_return_status => x_return_status
910 );
911 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
912 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
913 fnd_log.string
914 (
915 fnd_log.level_statement,
916 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
917 'Invalid MR Title, Version Number provided'
918 );
919 END IF;
920 RAISE FND_API.G_EXC_ERROR;
921 END IF;
922 END IF;
923 END LOOP;
924
925 --Start of API Body
926 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
927 THEN
928
929 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
930 LOOP
931
932 l_mr_relation_rec:=p_x_mr_relation_tbl(i);
933
934 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
935 THEN
936 TRANS_VALUE_ID
937 (
938 x_return_status =>x_return_Status,
939 p_x_mr_relation_rec =>l_mr_relation_rec);
940 END IF;
941 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_relation_rec.RELATED_MR_HEADER_ID;
942
943 END LOOP;
944
945 l_msg_count := FND_MSG_PUB.count_msg;
946 IF l_msg_count > 0
947 THEN
948 X_msg_count := l_msg_count;
949 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 RAISE FND_API.G_EXC_ERROR;
951 END IF;
952 END IF;
953
954 FOR i IN P_X_MR_RELATION_TBL.FIRST.. P_X_MR_RELATION_TBL.LAST
955 LOOP
956
957 x_return_status:=FND_API.G_RET_STS_SUCCESS;
958
959 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
960 THEN
961
962 VALIDATE_MR_RELATION
963 (
964 x_return_status =>x_return_Status,
965 p_mr_relation_rec =>p_x_mr_relation_tbl(i));
966
967 END IF;
968 l_msg_count := FND_MSG_PUB.count_msg;
969 IF l_msg_count > 0
970 THEN
971 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 END IF;
973
974 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
975 THEN
976 IF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='PARENT'
977 THEN
978 l_mr_header_id:=p_x_mr_relation_tbl(i).MR_HEADER_ID;
979 p_x_mr_relation_tbl(i).MR_HEADER_ID:=p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID;
980 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_header_id;
981 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='PARENT';
982 ELSIF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='CHILD'
983 THEN
984 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='PARENT';
985 END IF;
986 -- pdoki added for SBE Project, Start.
987 IF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='INITIATEDBY'
988 THEN
989 l_mr_header_id:=p_x_mr_relation_tbl(i).MR_HEADER_ID;
990 p_x_mr_relation_tbl(i).MR_HEADER_ID:=p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID;
991 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_header_id;
992 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='INITIATES';
993 ELSIF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='INITIATES'
994 THEN
995 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='INITIATES';
996 end if;
997
998
999 IF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE='TERMINATEDBY'
1000 THEN
1001 l_mr_header_id:=p_x_mr_relation_tbl(i).MR_HEADER_ID;
1002 p_x_mr_relation_tbl(i).MR_HEADER_ID:=p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID;
1003 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID:=l_mr_header_id;
1004 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='TERMINATES';
1005 ELSIF p_x_mr_relation_tbl(i).RELATIONSHIP_CODE= 'TERMINATES'
1006 THEN
1007 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE:='TERMINATES';
1008 END IF;
1009 -- pdoki added for SBE Project, End.
1010 END IF;
1011
1012 IF nvl(x_return_status,'X')='S'
1013 THEN
1014 IF p_x_mr_relation_tbl(i).DML_OPERATION='D' then
1015 delete AHL_MR_RELATIONSHIPS
1016 where MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
1017 and OBJECT_VERSION_NUMBER=p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER;
1018
1019 if sql%rowcount=0 then
1020 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_RECORD_CHANGED');
1021 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_relation_tbl(i).related_mr_title,false);
1022 FND_MSG_PUB.ADD;
1023 end if;
1024 ELSIF p_x_mr_relation_tbl(i).DML_operation='U'
1025 then
1026
1027 IF x_return_status=FND_API.G_RET_STS_SUCCESS
1028 THEN
1029
1030 update AHL_mr_RELATIONSHIPS
1031 set MR_HEADER_ID = p_x_mr_relation_tbl(i).MR_HEADER_ID,
1032 RELATED_MR_HEADER_ID = p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
1033 RELATIONSHIP_CODE = p_x_mr_relation_tbl(i).RELATIONSHIP_CODE,
1034 MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID,
1035 OBJECT_VERSION_NUMBER = p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER + 1,
1036 ATTRIBUTE_CATEGORY = p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY,
1037 ATTRIBUTE1 = p_x_mr_relation_tbl(i).ATTRIBUTE1,
1038 ATTRIBUTE2 = p_x_mr_relation_tbl(i).ATTRIBUTE2,
1039 ATTRIBUTE3 = p_x_mr_relation_tbl(i).ATTRIBUTE3,
1040 ATTRIBUTE4 = p_x_mr_relation_tbl(i).ATTRIBUTE4,
1041 ATTRIBUTE5 = p_x_mr_relation_tbl(i).ATTRIBUTE5,
1042 ATTRIBUTE6 = p_x_mr_relation_tbl(i).ATTRIBUTE6,
1043 ATTRIBUTE7 = p_x_mr_relation_tbl(i).ATTRIBUTE7,
1044 ATTRIBUTE8 = p_x_mr_relation_tbl(i).ATTRIBUTE8,
1045 ATTRIBUTE9 = p_x_mr_relation_tbl(i).ATTRIBUTE9,
1046 ATTRIBUTE10 = p_x_mr_relation_tbl(i).ATTRIBUTE10,
1047 ATTRIBUTE11 = p_x_mr_relation_tbl(i).ATTRIBUTE11,
1048 ATTRIBUTE12 = p_x_mr_relation_tbl(i).ATTRIBUTE12,
1049 ATTRIBUTE13 = p_x_mr_relation_tbl(i).ATTRIBUTE13,
1050 ATTRIBUTE14 = p_x_mr_relation_tbl(i).ATTRIBUTE14,
1051 ATTRIBUTE15 = p_x_mr_relation_tbl(i).ATTRIBUTE15,
1052 LAST_UPDATE_DATE = sysdate,
1053 LAST_UPDATED_BY = fnd_global.user_id,
1054 LAST_UPDATE_LOGIN = fnd_global.user_id
1055 where MR_RELATIONSHIP_ID = p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
1056 and OBJECT_VERSION_NUMBER=p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER;
1057
1058 if sql%rowcount=0 then
1059 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1060 FND_MSG_PUB.ADD;
1061 end if;
1062 END IF;
1063
1064 ELSIF p_x_mr_relation_tbl(i).DML_operation='C'
1065 then
1066
1067 SELECT AHL_MR_RELATIONSHIPS_S.NEXTVAL
1068 INTO p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID
1069 FROM DUAL;
1070
1071 p_x_mr_relation_tbl(i).OBJECT_VERSION_NUMBER:=1;
1072
1073 IF x_return_status=FND_API.G_RET_STS_SUCCESS
1074 THEN
1075 insert into AHl_mr_RELATIONSHIPS(
1076 MR_RELATIONSHIP_ID,
1077 OBJECT_VERSION_NUMBER,
1078 LAST_UPDATE_DATE,
1079 LAST_UPDATED_BY,
1080 CREATION_DATE,
1081 CREATED_BY,
1082 LAST_UPDATE_LOGIN,
1083 MR_HEADER_ID,
1084 RELATED_MR_HEADER_ID,
1085 RELATIONSHIP_CODE,
1086 ATTRIBUTE_CATEGORY,
1087 ATTRIBUTE1,
1088 ATTRIBUTE2,
1089 ATTRIBUTE3,
1090 ATTRIBUTE4,
1091 ATTRIBUTE5,
1092 ATTRIBUTE6,
1093 ATTRIBUTE7,
1094 ATTRIBUTE8,
1095 ATTRIBUTE9,
1096 ATTRIBUTE10,
1097 ATTRIBUTE11,
1098 ATTRIBUTE12,
1099 ATTRIBUTE13,
1100 ATTRIBUTE14,
1101 ATTRIBUTE15)
1102 values(
1103 p_x_mr_relation_tbl(i).MR_RELATIONSHIP_ID,
1104 1,
1105 sysdate,
1106 fnd_global.user_id,
1107 SYSDATE,
1108 fnd_global.user_id,
1109 fnd_global.user_id,
1110 p_x_mr_relation_tbl(i).MR_HEADER_ID,
1111 p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
1112 p_x_mr_relation_tbl(i).RELATIONSHIP_CODE,
1113 p_x_mr_relation_tbl(i).ATTRIBUTE_CATEGORY,
1114 p_x_mr_relation_tbl(i).ATTRIBUTE1,
1115 p_x_mr_relation_tbl(i).ATTRIBUTE2,
1116 p_x_mr_relation_tbl(i).ATTRIBUTE3,
1117 p_x_mr_relation_tbl(i).ATTRIBUTE4,
1118 p_x_mr_relation_tbl(i).ATTRIBUTE5,
1119 p_x_mr_relation_tbl(i).ATTRIBUTE6,
1120 p_x_mr_relation_tbl(i).ATTRIBUTE7,
1121 p_x_mr_relation_tbl(i).ATTRIBUTE8,
1122 p_x_mr_relation_tbl(i).ATTRIBUTE9,
1123 p_x_mr_relation_tbl(i).ATTRIBUTE10,
1124 p_x_mr_relation_tbl(i).ATTRIBUTE11,
1125 p_x_mr_relation_tbl(i).ATTRIBUTE12,
1126 p_x_mr_relation_tbl(i).ATTRIBUTE13,
1127 p_x_mr_relation_tbl(i).ATTRIBUTE14,
1128 p_x_mr_relation_tbl(i).ATTRIBUTE15);
1129 END IF;
1130 END IF;
1131 END IF;
1132
1133 IF p_x_mr_relation_tbl(i).DML_operation<>'D'
1134 THEN
1135 NON_CYCLIC_ENF
1136 (
1137 p_api_version =>l_api_version,
1138 p_init_msg_list =>l_init_msg_list,
1139 p_validation_level =>p_validation_level ,
1140 x_return_status =>x_return_Status,
1141 x_msg_count =>l_msg_count,
1142 x_msg_data =>l_msg_data,
1143 p_mr_header_id =>p_x_mr_relation_tbl(i).MR_HEADER_ID,
1144 p_related_mr_header_id =>p_x_mr_relation_tbl(i).RELATED_MR_HEADER_ID,
1145 p_related_mr_title =>p_x_mr_relation_tbl(i).RELATED_MR_TITLE,
1146 -- pdoki added for SBE Project
1147 p_relationship_code =>p_x_mr_relation_tbl(i).RELATIONSHIP_CODE);
1148 END IF;
1149 END LOOP;
1150
1151 l_msg_count := FND_MSG_PUB.count_msg;
1152 IF l_msg_count > 0 THEN
1153 X_msg_count := l_msg_count;
1154 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157
1158 IF FND_API.TO_BOOLEAN(p_commit) THEN
1159 COMMIT;
1160 END IF;
1161
1162 IF G_DEBUG='Y' THEN
1163 AHL_DEBUG_PUB.disable_debug;
1164 END IF;
1165
1166 EXCEPTION
1167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1168 ROLLBACK TO PROCESS_MR_RELATION;
1169 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1170 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1171 p_count => x_msg_count,
1172 p_data => x_msg_data);
1173
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 ROLLBACK TO PROCESS_MR_RELATION;
1176 X_return_status := FND_API.G_RET_STS_ERROR;
1177 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1178 p_count => x_msg_count,
1179 p_data => X_msg_data);
1180 WHEN OTHERS THEN
1181 ROLLBACK TO PROCESS_MR_RELATION;
1182 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1183 IF G_DEBUG='Y' THEN
1184 AHL_DEBUG_PUB.debug( 'PROCESS-->'||sqlerrm,'DEBUG RELATIONS');
1185 END IF;
1186 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1187 THEN
1188 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1189 p_procedure_name =>L_API_NAME,
1190 p_error_text =>SUBSTR(SQLERRM,1,240));
1191 END IF;
1192 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1193 p_count => x_msg_count,
1194 p_data => X_msg_data);
1195
1196 END;
1197 END AHL_FMP_MR_RELATION_PVT;