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
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:
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:
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
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,
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;
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,