[Home] [Help]
880: -- This functionality is not available now. So allow always
881: l_MR_Initialization_flag := 'Y';
882: IF (l_MR_Initialization_flag <> 'Y') THEN
883: x_return_status := FND_API.G_RET_STS_ERROR;
884: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UNAUTHORIZED_USER');
885: FND_MSG_PUB.ADD;
886: --IF G_DEBUG='Y' THEN
887: --AHL_DEBUG_PUB.debug('Unauthorized User', 'UMP:Validate_For_Initialize');
888: --END IF;
890: END IF;
891:
892: IF (p_unit_effectivity_id IS NULL OR p_unit_effectivity_id = FND_API.G_MISS_NUM) THEN
893: x_return_status := FND_API.G_RET_STS_ERROR;
894: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_NULL');
895: FND_MSG_PUB.ADD;
896: --IF G_DEBUG='Y' THEN
897: --AHL_DEBUG_PUB.debug('Null Effectivity', 'UMP:Validate_For_Initialize');
898: --END IF;
903: OPEN l_ue_details_csr(p_unit_effectivity_id);
904: FETCH l_ue_details_csr INTO l_mr_id, l_item_instance_id, l_repetitive_mr_flag, l_status_code;
905: IF (l_ue_details_csr%NOTFOUND) THEN
906: x_return_status := FND_API.G_RET_STS_ERROR;
907: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
908: FND_MESSAGE.Set_Token('UEID', p_unit_effectivity_id);
909: FND_MSG_PUB.ADD;
910: CLOSE l_ue_details_csr;
911: --IF G_DEBUG='Y' THEN
904: FETCH l_ue_details_csr INTO l_mr_id, l_item_instance_id, l_repetitive_mr_flag, l_status_code;
905: IF (l_ue_details_csr%NOTFOUND) THEN
906: x_return_status := FND_API.G_RET_STS_ERROR;
907: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
908: FND_MESSAGE.Set_Token('UEID', p_unit_effectivity_id);
909: FND_MSG_PUB.ADD;
910: CLOSE l_ue_details_csr;
911: --IF G_DEBUG='Y' THEN
912: --AHL_DEBUG_PUB.debug('Invalid Effectivity Id', 'UMP:Validate_For_Initialize');
915: ELSE
916: --Ensure that unit is not locked
917: IF(AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED(p_unit_header_id => null, p_instance_id => l_item_instance_id) = FND_API.G_TRUE) THEN
918: x_return_status := FND_API.G_RET_STS_ERROR;
919: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INIT_UNIT_LOCKED');
920: FND_MSG_PUB.ADD;
921: CLOSE l_ue_details_csr;
922: RAISE FND_API.G_EXC_ERROR;
923: END IF;
922: RAISE FND_API.G_EXC_ERROR;
923: END IF;
924: IF (l_repetitive_mr_flag = 'Y') THEN
925: x_return_status := FND_API.G_RET_STS_ERROR;
926: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_MR_TYPE');
927: FND_MSG_PUB.ADD;
928: --IF G_DEBUG='Y' THEN
929: --AHL_DEBUG_PUB.debug('Repetitive Effectivity', 'UMP:Validate_For_Initialize');
930: --END IF;
938: -- Ensure that this is not a child MR
939: OPEN l_child_mr_csr(p_unit_effectivity_id);
940: FETCH l_child_mr_csr INTO l_junk;
941: IF (l_child_mr_csr%FOUND) THEN
942: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CHILD_MR');
943: FND_MSG_PUB.ADD;
944: CLOSE l_child_mr_csr;
945: --IF G_DEBUG='Y' THEN
946: --AHL_DEBUG_PUB.debug('Child MR', 'UMP:Validate_For_Initialize');
952:
953: -- Ensure that the Current status is null, init-due or init-accomplished only
954: IF ((l_status_code IS NOT NULL) AND (l_status_code <> 'INIT-DUE') AND (l_status_code <> 'INIT-ACCOMPLISHED')) THEN
955: x_return_status := FND_API.G_RET_STS_ERROR;
956: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STATUS');
957: FND_MESSAGE.Set_Token('STATUS', l_status_code);
958: FND_MSG_PUB.ADD;
959: --IF G_DEBUG='Y' THEN
960: --AHL_DEBUG_PUB.debug('Invalid Status: ' || 'l_status_code', 'UMP:Validate_For_Initialize');
953: -- Ensure that the Current status is null, init-due or init-accomplished only
954: IF ((l_status_code IS NOT NULL) AND (l_status_code <> 'INIT-DUE') AND (l_status_code <> 'INIT-ACCOMPLISHED')) THEN
955: x_return_status := FND_API.G_RET_STS_ERROR;
956: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STATUS');
957: FND_MESSAGE.Set_Token('STATUS', l_status_code);
958: FND_MSG_PUB.ADD;
959: --IF G_DEBUG='Y' THEN
960: --AHL_DEBUG_PUB.debug('Invalid Status: ' || 'l_status_code', 'UMP:Validate_For_Initialize');
961: --END IF;
967: IF (l_temp_status = FALSE) THEN
968: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969: END IF;
970: IF (l_temp_status_code <> 'INIT-ACCOMPLISHED' and l_last_accomplish_date IS NOT null) THEN
971: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_ACCMPLSHD');
972: FND_MSG_PUB.ADD;
973: --IF G_DEBUG='Y' THEN
974: --AHL_DEBUG_PUB.debug('Already Accomplished', 'UMP:Validate_For_Initialize');
975: --END IF;
981: FETCH l_prior_initializations_csr INTO l_prior_ue_status;
982: IF (l_prior_initializations_csr%FOUND) THEN
983: x_return_status := FND_API.G_RET_STS_ERROR;
984: IF (l_prior_ue_status = 'DEFERRED') THEN
985: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_DEFERRED');
986: FND_MSG_PUB.ADD;
987: ELSE
988: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_INITLZD');
989: FND_MSG_PUB.ADD;
984: IF (l_prior_ue_status = 'DEFERRED') THEN
985: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_DEFERRED');
986: FND_MSG_PUB.ADD;
987: ELSE
988: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_INITLZD');
989: FND_MSG_PUB.ADD;
990: END IF;
991: CLOSE l_prior_initializations_csr;
992: --IF G_DEBUG='Y' THEN
1414: END IF;
1415:
1416: -- Check if the unit effectivity id is not null
1417: IF (p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID IS NULL OR p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID = FND_API.G_MISS_NUM) THEN
1418: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_NULL');
1419: FND_MSG_PUB.ADD;
1420: x_return_status := FND_API.G_RET_STS_ERROR;
1421: END IF;
1422:
1424: --IF (p_unit_Effectivity_rec.STATUS_CODE IS NOT NULL AND p_unit_Effectivity_rec.STATUS_CODE <> FND_API.G_MISS_CHAR) THEN
1425: OPEN l_validate_status_csr(p_unit_Effectivity_rec.STATUS_CODE);
1426: FETCH l_validate_status_csr into l_junk;
1427: IF (l_validate_status_csr%NOTFOUND) THEN
1428: FND_MESSAGE.Set_Name('AHL','AHL_UMP_STATUS_INVALID');
1429: FND_MESSAGE.Set_Token('STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1430: FND_MSG_PUB.ADD;
1431: x_return_status := FND_API.G_RET_STS_ERROR;
1432: END IF;
1425: OPEN l_validate_status_csr(p_unit_Effectivity_rec.STATUS_CODE);
1426: FETCH l_validate_status_csr into l_junk;
1427: IF (l_validate_status_csr%NOTFOUND) THEN
1428: FND_MESSAGE.Set_Name('AHL','AHL_UMP_STATUS_INVALID');
1429: FND_MESSAGE.Set_Token('STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1430: FND_MSG_PUB.ADD;
1431: x_return_status := FND_API.G_RET_STS_ERROR;
1432: END IF;
1433: CLOSE l_validate_status_csr;
1435:
1436: -- If the status is INIT-ACCOMPLISHED or INIT-DUE, ensure that the user has permission
1437: IF ((p_unit_Effectivity_rec.STATUS_CODE = 'INIT-ACCOMPLISHED') OR (p_unit_Effectivity_rec.STATUS_CODE = 'INIT-DUE')) THEN
1438: IF (p_authorized_user_flag = 'N') THEN
1439: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UNAUTHORIZED_USER');
1440: FND_MSG_PUB.ADD;
1441: x_return_status := FND_API.G_RET_STS_ERROR;
1442: --RAISE USER_NOT_AUTHORIZED;
1443: END IF;
1445: -- validate that UE is not assigned to any visit.
1446: IF (p_unit_Effectivity_rec.STATUS_CODE = 'INIT-ACCOMPLISHED') THEN
1447: l_visit_status := AHL_UMP_UTIL_PKG.get_Visit_Status(p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1448: IF (l_visit_status IS NOT NULL) THEN
1449: FND_MESSAGE.Set_Name('AHL','AHL_UMP_VISIT_ASSIGNED');
1450: FND_MSG_PUB.ADD;
1451: x_return_status := FND_API.G_RET_STS_ERROR;
1452: END IF;
1453: END IF;
1455: -- validate that UE is not a child UE.
1456: OPEN l_child_mr_csr(p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1457: FETCH l_child_mr_csr INTO l_junk;
1458: IF (l_child_mr_csr%FOUND) THEN
1459: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CHILD_MR');
1460: FND_MSG_PUB.ADD;
1461: x_return_status := FND_API.G_RET_STS_ERROR;
1462: END IF;
1463: CLOSE l_child_mr_csr;
1475: FETCH l_ue_details_csr into
1476: l_prev_status, l_prev_object_version_no, l_mr_id, l_item_instance_id, l_preceding_ue_id,
1477: l_service_line_id;
1478: IF (l_ue_details_csr%NOTFOUND) THEN
1479: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
1480: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1481: FND_MSG_PUB.ADD;
1482: x_return_status := FND_API.G_RET_STS_ERROR;
1483: -- Cannot proceed further
1476: l_prev_status, l_prev_object_version_no, l_mr_id, l_item_instance_id, l_preceding_ue_id,
1477: l_service_line_id;
1478: IF (l_ue_details_csr%NOTFOUND) THEN
1479: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
1480: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1481: FND_MSG_PUB.ADD;
1482: x_return_status := FND_API.G_RET_STS_ERROR;
1483: -- Cannot proceed further
1484: RETURN;
1487:
1488: --Ensure that unit is not locked
1489: IF(AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED(p_unit_header_id => null, p_instance_id => l_item_instance_id) = FND_API.G_TRUE) THEN
1490: x_return_status := FND_API.G_RET_STS_ERROR;
1491: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INIT_UNIT_LOCKED');
1492: FND_MSG_PUB.ADD;
1493: RETURN; -- cannot proceed further
1494: END IF;
1495:
1496: -- Returning only after doing all of the following checks
1497:
1498: -- If object version no is different, write error message and skip to next unit effectivity
1499: IF(l_prev_object_version_no <> p_unit_Effectivity_rec.OBJECT_VERSION_NUMBER) THEN
1500: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_CHANGED');
1501: FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1502: FND_MSG_PUB.ADD;
1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: END IF;
1497:
1498: -- If object version no is different, write error message and skip to next unit effectivity
1499: IF(l_prev_object_version_no <> p_unit_Effectivity_rec.OBJECT_VERSION_NUMBER) THEN
1500: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_CHANGED');
1501: FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1502: FND_MSG_PUB.ADD;
1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: END IF;
1505:
1508: -- IF(p_unit_Effectivity_rec.STATUS_CODE = 'TERMINATED') THEN
1509: -- -- Call VWP API to ensure that this is not assigned to a visit
1510: -- -- and if the visit is not in progress
1511: -- IF(AHL_UMP_UTIL_PKG.Is_UE_In_Execution(p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID) = TRUE) THEN
1512: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_IN_EXEC');
1513: -- FND_MSG_PUB.ADD;
1514: -- x_return_status := FND_API.G_RET_STS_ERROR;
1515: -- END IF;
1516: -- END IF;
1524: -- Both the dates (due and accomplished) should not be set. Only one should be set.
1525: IF (p_unit_Effectivity_rec.STATUS_CODE IS NOT NULL) THEN
1526: IF ((p_unit_Effectivity_rec.SET_DUE_DATE IS NOT NULL AND p_unit_Effectivity_rec.SET_DUE_DATE <> FND_API.G_MISS_DATE) AND
1527: (p_unit_Effectivity_rec.ACCOMPLISHED_DATE IS NOT NULL AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE)) THEN
1528: FND_MESSAGE.Set_Name('AHL','AHL_UMP_BOTH_DATES_SET');
1529: FND_MSG_PUB.ADD;
1530: x_return_status := FND_API.G_RET_STS_ERROR;
1531: END IF;
1532: END IF;
1535: IF ((p_unit_Effectivity_rec.STATUS_CODE = 'INIT-ACCOMPLISHED') OR
1536: (p_unit_Effectivity_rec.STATUS_CODE = 'ACCOMPLISHED')) THEN
1537: -- validate ACCOMPLISHED_DATE = G_MISS_DATE for INIT-ACCOMPLISHED later in update_unit_effectivity proc.
1538: IF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE IS NULL OR (p_unit_Effectivity_rec.ACCOMPLISHED_DATE = FND_API.G_MISS_DATE AND p_unit_Effectivity_rec.STATUS_CODE = 'ACCOMPLISHED')) THEN
1539: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHD_DATE_NULL');
1540: FND_MSG_PUB.ADD;
1541: x_return_status := FND_API.G_RET_STS_ERROR;
1542: ELSIF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE
1543: AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE > sysdate) THEN
1540: FND_MSG_PUB.ADD;
1541: x_return_status := FND_API.G_RET_STS_ERROR;
1542: ELSIF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE
1543: AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE > sysdate) THEN
1544: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACC_DATE_IN_FUTR');
1545: FND_MESSAGE.Set_Token('ACCDATE',p_unit_Effectivity_rec.ACCOMPLISHED_DATE);
1546: FND_MSG_PUB.ADD;
1547: x_return_status := FND_API.G_RET_STS_ERROR;
1548: END IF;
1541: x_return_status := FND_API.G_RET_STS_ERROR;
1542: ELSIF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE
1543: AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE > sysdate) THEN
1544: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACC_DATE_IN_FUTR');
1545: FND_MESSAGE.Set_Token('ACCDATE',p_unit_Effectivity_rec.ACCOMPLISHED_DATE);
1546: FND_MSG_PUB.ADD;
1547: x_return_status := FND_API.G_RET_STS_ERROR;
1548: END IF;
1549: -- If this is a group MR, ensure that all descendents are accomplished
1550: /*
1551: OPEN l_invalid_descendents_csr(p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1552: FETCH l_invalid_descendents_csr INTO l_junk;
1553: IF (l_invalid_descendents_csr%FOUND) THEN
1554: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CHILD_UNACCMPLSHD');
1555: FND_MSG_PUB.ADD;
1556: x_return_status := FND_API.G_RET_STS_ERROR;
1557: END IF;
1558: CLOSE l_invalid_descendents_csr;
1562: -- Ensure that the previous status of the current effectivity is not MR-TERMINATE, DEFERRED or SR-CLOSED or CANCELLED.
1563: -- Allow updates to UE in status ACCOMPLISHED and TERMINATED. Only Counter values can be updated for this case.
1564: IF ((l_prev_status = 'MR-TERMINATE') OR (l_prev_status = 'DEFERRED') OR
1565: (l_prev_status = 'SR-CLOSED') OR (l_prev_status = 'CANCELLED') ) THEN
1566: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1567: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1568: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1569: FND_MSG_PUB.ADD;
1570: x_return_status := FND_API.G_RET_STS_ERROR;
1563: -- Allow updates to UE in status ACCOMPLISHED and TERMINATED. Only Counter values can be updated for this case.
1564: IF ((l_prev_status = 'MR-TERMINATE') OR (l_prev_status = 'DEFERRED') OR
1565: (l_prev_status = 'SR-CLOSED') OR (l_prev_status = 'CANCELLED') ) THEN
1566: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1567: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1568: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1569: FND_MSG_PUB.ADD;
1570: x_return_status := FND_API.G_RET_STS_ERROR;
1571: END IF;
1564: IF ((l_prev_status = 'MR-TERMINATE') OR (l_prev_status = 'DEFERRED') OR
1565: (l_prev_status = 'SR-CLOSED') OR (l_prev_status = 'CANCELLED') ) THEN
1566: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1567: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1568: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1569: FND_MSG_PUB.ADD;
1570: x_return_status := FND_API.G_RET_STS_ERROR;
1571: END IF;
1572:
1577: IF (l_temp_status = FALSE) THEN
1578: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1579: END IF;
1580: IF (l_temp_status_code <> 'INIT-ACCOMPLISHED' and l_last_accomplished_date IS NOT null) THEN
1581: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_ACCMPLSHD');
1582: FND_MSG_PUB.ADD;
1583: x_return_status := FND_API.G_RET_STS_ERROR;
1584: END IF;
1585: -- Ensure that there are no prior initializations
1586: OPEN l_prior_initializations_csr(l_mr_id, l_item_instance_id, p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1587: FETCH l_prior_initializations_csr INTO l_prior_ue_status;
1588: IF (l_prior_initializations_csr%FOUND) THEN
1589: IF (l_prior_ue_status = 'DEFERRED') THEN
1590: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_DEFERRED');
1591: FND_MSG_PUB.ADD;
1592: ELSE
1593: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_INITLZD');
1594: FND_MSG_PUB.ADD;
1589: IF (l_prior_ue_status = 'DEFERRED') THEN
1590: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_DEFERRED');
1591: FND_MSG_PUB.ADD;
1592: ELSE
1593: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ALRDY_INITLZD');
1594: FND_MSG_PUB.ADD;
1595: END IF;
1596: x_return_status := FND_API.G_RET_STS_ERROR;
1597: END IF;
1614: 'AHL_UMP_EFF','SR',6);
1615: FETCH l_servq_num_csr INTO l_err_serreq_num;
1616: CLOSE l_servq_num_csr;
1617:
1618: FND_MESSAGE.Set_Name('AHL','AHL_UMP_LTR_ACCMPLSH_EXSTS');
1619: FND_MESSAGE.Set_Token('UEID', p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1620: FND_MESSAGE.Set_Token('NEW_ACC_DATE',to_char(p_unit_Effectivity_rec.ACCOMPLISHED_DATE,fnd_date.outputDT_mask));
1621: FND_MESSAGE.Set_Token('ACC_DATE',to_char(l_last_accomplished_date,fnd_date.outputDT_mask));
1622: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1615: FETCH l_servq_num_csr INTO l_err_serreq_num;
1616: CLOSE l_servq_num_csr;
1617:
1618: FND_MESSAGE.Set_Name('AHL','AHL_UMP_LTR_ACCMPLSH_EXSTS');
1619: FND_MESSAGE.Set_Token('UEID', p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1620: FND_MESSAGE.Set_Token('NEW_ACC_DATE',to_char(p_unit_Effectivity_rec.ACCOMPLISHED_DATE,fnd_date.outputDT_mask));
1621: FND_MESSAGE.Set_Token('ACC_DATE',to_char(l_last_accomplished_date,fnd_date.outputDT_mask));
1622: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1623: FND_MSG_PUB.ADD;
1616: CLOSE l_servq_num_csr;
1617:
1618: FND_MESSAGE.Set_Name('AHL','AHL_UMP_LTR_ACCMPLSH_EXSTS');
1619: FND_MESSAGE.Set_Token('UEID', p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1620: FND_MESSAGE.Set_Token('NEW_ACC_DATE',to_char(p_unit_Effectivity_rec.ACCOMPLISHED_DATE,fnd_date.outputDT_mask));
1621: FND_MESSAGE.Set_Token('ACC_DATE',to_char(l_last_accomplished_date,fnd_date.outputDT_mask));
1622: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1623: FND_MSG_PUB.ADD;
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1617:
1618: FND_MESSAGE.Set_Name('AHL','AHL_UMP_LTR_ACCMPLSH_EXSTS');
1619: FND_MESSAGE.Set_Token('UEID', p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1620: FND_MESSAGE.Set_Token('NEW_ACC_DATE',to_char(p_unit_Effectivity_rec.ACCOMPLISHED_DATE,fnd_date.outputDT_mask));
1621: FND_MESSAGE.Set_Token('ACC_DATE',to_char(l_last_accomplished_date,fnd_date.outputDT_mask));
1622: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1623: FND_MSG_PUB.ADD;
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1625: END IF;
1618: FND_MESSAGE.Set_Name('AHL','AHL_UMP_LTR_ACCMPLSH_EXSTS');
1619: FND_MESSAGE.Set_Token('UEID', p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1620: FND_MESSAGE.Set_Token('NEW_ACC_DATE',to_char(p_unit_Effectivity_rec.ACCOMPLISHED_DATE,fnd_date.outputDT_mask));
1621: FND_MESSAGE.Set_Token('ACC_DATE',to_char(l_last_accomplished_date,fnd_date.outputDT_mask));
1622: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1623: FND_MSG_PUB.ADD;
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1625: END IF;
1626: END IF;
1650: 'AHL_UMP_EFF','SR',6);
1651: FETCH l_servq_num_csr INTO l_err_serreq_num;
1652: CLOSE l_servq_num_csr;
1653:
1654: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ERLR_EFF_EXISTS');
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1651: FETCH l_servq_num_csr INTO l_err_serreq_num;
1652: CLOSE l_servq_num_csr;
1653:
1654: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ERLR_EFF_EXISTS');
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1652: CLOSE l_servq_num_csr;
1653:
1654: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ERLR_EFF_EXISTS');
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1660: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1653:
1654: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ERLR_EFF_EXISTS');
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1660: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1661: FND_MSG_PUB.ADD;
1654: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ERLR_EFF_EXISTS');
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1660: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1661: FND_MSG_PUB.ADD;
1662: x_return_status := FND_API.G_RET_STS_ERROR;
1655: FND_MESSAGE.Set_Token('INST', l_err_instance_number);
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1660: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1661: FND_MSG_PUB.ADD;
1662: x_return_status := FND_API.G_RET_STS_ERROR;
1663: END IF;
1656: FND_MESSAGE.Set_Token('BEF_UEID', l_temp_ue_id);
1657: FND_MESSAGE.Set_Token('UEID',p_unit_Effectivity_rec.UNIT_EFFECTIVITY_ID);
1658: FND_MESSAGE.Set_Token('DUEDATE',l_err_due_date);
1659: FND_MESSAGE.Set_Token('TITLE',l_err_title);
1660: FND_MESSAGE.Set_Token('SERVQ_NUM',l_err_serreq_num);
1661: FND_MSG_PUB.ADD;
1662: x_return_status := FND_API.G_RET_STS_ERROR;
1663: END IF;
1664: END IF;
1680: x_return_status := FND_API.G_RET_STS_ERROR;
1681: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1682: END IF;
1683: IF (l_last_accomplished_date IS NULL) THEN
1684: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PRED_NOT_ACCMPLSHD');
1685: FND_MSG_PUB.ADD;
1686: x_return_status := FND_API.G_RET_STS_ERROR;
1687: END IF;
1688: END IF;
1692: -- If terminating, ensure that the Accomplished Date if given, is in the past
1693: IF (p_unit_Effectivity_rec.STATUS_CODE = 'TERMINATED' OR p_unit_Effectivity_rec.STATUS_CODE = 'MR-TERMINATE') THEN
1694: IF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE IS NOT NULL AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE) THEN
1695: IF (TRUNC(p_unit_Effectivity_rec.ACCOMPLISHED_DATE) > TRUNC(sysdate)) THEN
1696: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACC_DATE_IN_FUTR');
1697: FND_MESSAGE.Set_Token('ACCDATE', p_unit_Effectivity_rec.ACCOMPLISHED_DATE);
1698: FND_MSG_PUB.ADD;
1699: x_return_status := FND_API.G_RET_STS_ERROR;
1700: END IF;
1693: IF (p_unit_Effectivity_rec.STATUS_CODE = 'TERMINATED' OR p_unit_Effectivity_rec.STATUS_CODE = 'MR-TERMINATE') THEN
1694: IF (p_unit_Effectivity_rec.ACCOMPLISHED_DATE IS NOT NULL AND p_unit_Effectivity_rec.ACCOMPLISHED_DATE <> FND_API.G_MISS_DATE) THEN
1695: IF (TRUNC(p_unit_Effectivity_rec.ACCOMPLISHED_DATE) > TRUNC(sysdate)) THEN
1696: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACC_DATE_IN_FUTR');
1697: FND_MESSAGE.Set_Token('ACCDATE', p_unit_Effectivity_rec.ACCOMPLISHED_DATE);
1698: FND_MSG_PUB.ADD;
1699: x_return_status := FND_API.G_RET_STS_ERROR;
1700: END IF;
1701: END IF;
1704: -- If INIT-DUE, ensure that the Set Due Date if given, is in the future
1705: IF (p_unit_Effectivity_rec.STATUS_CODE = 'INIT-DUE') THEN
1706: IF (p_unit_Effectivity_rec.SET_DUE_DATE IS NOT NULL AND p_unit_Effectivity_rec.SET_DUE_DATE <> FND_API.G_MISS_DATE) THEN
1707: IF (TRUNC(p_unit_Effectivity_rec.SET_DUE_DATE) < TRUNC(sysdate)) THEN
1708: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUE_DATE_IN_PAST');
1709: FND_MESSAGE.Set_Token('DUEDATE', p_unit_Effectivity_rec.SET_DUE_DATE);
1710: FND_MSG_PUB.ADD;
1711: x_return_status := FND_API.G_RET_STS_ERROR;
1712: END IF;
1705: IF (p_unit_Effectivity_rec.STATUS_CODE = 'INIT-DUE') THEN
1706: IF (p_unit_Effectivity_rec.SET_DUE_DATE IS NOT NULL AND p_unit_Effectivity_rec.SET_DUE_DATE <> FND_API.G_MISS_DATE) THEN
1707: IF (TRUNC(p_unit_Effectivity_rec.SET_DUE_DATE) < TRUNC(sysdate)) THEN
1708: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUE_DATE_IN_PAST');
1709: FND_MESSAGE.Set_Token('DUEDATE', p_unit_Effectivity_rec.SET_DUE_DATE);
1710: FND_MSG_PUB.ADD;
1711: x_return_status := FND_API.G_RET_STS_ERROR;
1712: END IF;
1713: END IF;
1717: IF (p_unit_Effectivity_rec.qa_collection_id IS NOT NULL) AND
1718: (p_unit_Effectivity_rec.qa_collection_id <> FND_API.G_MISS_NUM) THEN
1719: OPEN l_qa_collection_csr (p_unit_Effectivity_rec.qa_collection_id);
1720: IF l_qa_collection_csr%NOTFOUND THEN
1721: FND_MESSAGE.Set_Name('AHL','AHL_UMP_QA_COLLECTION_INVALID');
1722: FND_MESSAGE.Set_Token('COLLECT_ID', p_unit_Effectivity_rec.qa_collection_id);
1723: FND_MSG_PUB.ADD;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: END IF;
1718: (p_unit_Effectivity_rec.qa_collection_id <> FND_API.G_MISS_NUM) THEN
1719: OPEN l_qa_collection_csr (p_unit_Effectivity_rec.qa_collection_id);
1720: IF l_qa_collection_csr%NOTFOUND THEN
1721: FND_MESSAGE.Set_Name('AHL','AHL_UMP_QA_COLLECTION_INVALID');
1722: FND_MESSAGE.Set_Token('COLLECT_ID', p_unit_Effectivity_rec.qa_collection_id);
1723: FND_MSG_PUB.ADD;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: END IF;
1726: CLOSE l_qa_collection_csr;
1729: -- Validate the current UE status is not ACCOMPLISHED.
1730: IF (l_prev_status = 'ACCOMPLISHED' OR l_prev_status = 'TERMINATED' OR
1731: l_prev_status = 'DEFERRED' OR l_prev_status = 'INIT-ACCOMPLISHED' OR
1732: l_prev_status = 'SR-CLOSED' OR l_prev_status = 'CANCELLED') THEN
1733: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1734: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1735: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1736: FND_MSG_PUB.ADD;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1730: IF (l_prev_status = 'ACCOMPLISHED' OR l_prev_status = 'TERMINATED' OR
1731: l_prev_status = 'DEFERRED' OR l_prev_status = 'INIT-ACCOMPLISHED' OR
1732: l_prev_status = 'SR-CLOSED' OR l_prev_status = 'CANCELLED') THEN
1733: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1734: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1735: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1736: FND_MSG_PUB.ADD;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1738: END IF;
1731: l_prev_status = 'DEFERRED' OR l_prev_status = 'INIT-ACCOMPLISHED' OR
1732: l_prev_status = 'SR-CLOSED' OR l_prev_status = 'CANCELLED') THEN
1733: FND_MESSAGE.Set_Name('AHL','AHL_UMP_INVALID_STTS_CHNG');
1734: FND_MESSAGE.Set_Token('FROM_STATUS', l_prev_status);
1735: FND_MESSAGE.Set_Token('TO_STATUS', p_unit_Effectivity_rec.STATUS_CODE);
1736: FND_MSG_PUB.ADD;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1738: END IF;
1739:
1744: (p_unit_Effectivity_rec.unit_deferral_ID <> FND_API.G_MISS_NUM) THEN
1745: OPEN l_unit_deferral_csr (p_unit_Effectivity_rec.unit_deferral_id);
1746: FETCH l_unit_deferral_csr INTO l_junk;
1747: IF (l_unit_deferral_csr%NOTFOUND) THEN
1748: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DEFERRAL_INVALID');
1749: FND_MESSAGE.Set_Token('DEFERRAL_ID', p_unit_Effectivity_rec.unit_deferral_id);
1750: FND_MSG_PUB.ADD;
1751: x_return_status := FND_API.G_RET_STS_ERROR;
1752: END IF;
1745: OPEN l_unit_deferral_csr (p_unit_Effectivity_rec.unit_deferral_id);
1746: FETCH l_unit_deferral_csr INTO l_junk;
1747: IF (l_unit_deferral_csr%NOTFOUND) THEN
1748: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DEFERRAL_INVALID');
1749: FND_MESSAGE.Set_Token('DEFERRAL_ID', p_unit_Effectivity_rec.unit_deferral_id);
1750: FND_MSG_PUB.ADD;
1751: x_return_status := FND_API.G_RET_STS_ERROR;
1752: END IF;
1753:
1835:
1836: -- Ensure that for Modify or delete operation, the threshold id is present
1837: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'M' OR p_unit_threshold_tbl(i).OPERATION_FLAG = 'D') THEN
1838: IF (p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID IS NULL OR p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID = FND_API.G_MISS_NUM) THEN
1839: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_ID_NULL');
1840: FND_MSG_PUB.ADD;
1841: x_return_status := FND_API.G_RET_STS_ERROR;
1842: END IF;
1843: END IF;
1847: OPEN l_ue_id_check_csr(p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID,
1848: p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID);
1849: FETCH l_ue_id_check_csr INTO l_junk;
1850: IF (l_ue_id_check_csr%NOTFOUND) THEN
1851: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
1852: FND_MESSAGE.Set_Token('UEID', p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID);
1853: FND_MSG_PUB.ADD;
1854: x_return_status := FND_API.G_RET_STS_ERROR;
1855: END IF;
1848: p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID);
1849: FETCH l_ue_id_check_csr INTO l_junk;
1850: IF (l_ue_id_check_csr%NOTFOUND) THEN
1851: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
1852: FND_MESSAGE.Set_Token('UEID', p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID);
1853: FND_MSG_PUB.ADD;
1854: x_return_status := FND_API.G_RET_STS_ERROR;
1855: END IF;
1856: CLOSE l_ue_id_check_csr;
1858:
1859: -- Ensure that the Counter ID is valid for a Create or Modify operation
1860: IF (p_unit_threshold_tbl(i).COUNTER_ID IS NULL OR p_unit_threshold_tbl(i).COUNTER_ID = FND_API.G_MISS_NUM) THEN
1861: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'C' OR p_unit_threshold_tbl(i).OPERATION_FLAG = 'M') THEN
1862: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
1863: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1864: FND_MSG_PUB.ADD;
1865: x_return_status := FND_API.G_RET_STS_ERROR;
1866: END IF;
1859: -- Ensure that the Counter ID is valid for a Create or Modify operation
1860: IF (p_unit_threshold_tbl(i).COUNTER_ID IS NULL OR p_unit_threshold_tbl(i).COUNTER_ID = FND_API.G_MISS_NUM) THEN
1861: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'C' OR p_unit_threshold_tbl(i).OPERATION_FLAG = 'M') THEN
1862: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
1863: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1864: FND_MSG_PUB.ADD;
1865: x_return_status := FND_API.G_RET_STS_ERROR;
1866: END IF;
1867: END IF;
1870: IF (p_unit_threshold_tbl(i).COUNTER_ID IS NOT NULL AND p_unit_threshold_tbl(i).COUNTER_ID <> FND_API.G_MISS_NUM) THEN
1871: OPEN l_validate_counter_csr(p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID, p_unit_threshold_tbl(i).COUNTER_ID);
1872: FETCH l_validate_counter_csr INTO l_junk;
1873: IF (l_validate_counter_csr%NOTFOUND) THEN
1874: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
1875: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1876: FND_MSG_PUB.ADD;
1877: x_return_status := FND_API.G_RET_STS_ERROR;
1878: ELSE
1871: OPEN l_validate_counter_csr(p_unit_threshold_tbl(i).UNIT_EFFECTIVITY_ID, p_unit_threshold_tbl(i).COUNTER_ID);
1872: FETCH l_validate_counter_csr INTO l_junk;
1873: IF (l_validate_counter_csr%NOTFOUND) THEN
1874: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
1875: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1876: FND_MSG_PUB.ADD;
1877: x_return_status := FND_API.G_RET_STS_ERROR;
1878: ELSE
1879: -- get counter reading.
1892: -- Retrieve object version no. for this threshold
1893: OPEN l_get_prev_ctr_csr(p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
1894: FETCH l_get_prev_ctr_csr into l_prev_object_version_no, l_prev_counter;
1895: IF (l_get_prev_ctr_csr%NOTFOUND) THEN
1896: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRSHLD_ID_INVALID');
1897: FND_MESSAGE.Set_Token('THRESHOLDID', p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
1898: FND_MSG_PUB.ADD;
1899: CLOSE l_get_prev_ctr_csr;
1900: x_return_status := FND_API.G_RET_STS_ERROR;
1893: OPEN l_get_prev_ctr_csr(p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
1894: FETCH l_get_prev_ctr_csr into l_prev_object_version_no, l_prev_counter;
1895: IF (l_get_prev_ctr_csr%NOTFOUND) THEN
1896: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRSHLD_ID_INVALID');
1897: FND_MESSAGE.Set_Token('THRESHOLDID', p_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
1898: FND_MSG_PUB.ADD;
1899: CLOSE l_get_prev_ctr_csr;
1900: x_return_status := FND_API.G_RET_STS_ERROR;
1901: RETURN;
1902: END IF;
1903: CLOSE l_get_prev_ctr_csr;
1904: -- Check if object version no is different
1905: IF(l_prev_object_version_no <> p_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
1906: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
1907: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1908: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
1909: FND_MSG_PUB.ADD;
1910: x_return_status := FND_API.G_RET_STS_ERROR;
1903: CLOSE l_get_prev_ctr_csr;
1904: -- Check if object version no is different
1905: IF(l_prev_object_version_no <> p_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
1906: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
1907: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1908: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
1909: FND_MSG_PUB.ADD;
1910: x_return_status := FND_API.G_RET_STS_ERROR;
1911: RETURN;
1904: -- Check if object version no is different
1905: IF(l_prev_object_version_no <> p_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
1906: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
1907: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1908: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
1909: FND_MSG_PUB.ADD;
1910: x_return_status := FND_API.G_RET_STS_ERROR;
1911: RETURN;
1912: END IF;
1912: END IF;
1913: -- For modify operation, ensure that the counter (id) has not changed
1914: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'M') THEN
1915: IF (p_unit_threshold_tbl(i).COUNTER_ID <> l_prev_counter) THEN
1916: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THR_COUNTER_CHANGED');
1917: FND_MSG_PUB.ADD;
1918: x_return_status := FND_API.G_RET_STS_ERROR;
1919: END IF;
1920: END IF;
1922:
1923: -- Ensure that the counter value is valid
1924: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'C' OR p_unit_threshold_tbl(i).OPERATION_FLAG = 'M') THEN
1925: IF (p_unit_threshold_tbl(i).COUNTER_VALUE IS NULL OR p_unit_threshold_tbl(i).COUNTER_VALUE = FND_API.G_MISS_NUM) THEN
1926: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CNTR_VALUE_MISSING');
1927: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1928: FND_MSG_PUB.ADD;
1929: x_return_status := FND_API.G_RET_STS_ERROR;
1930: ELSE
1923: -- Ensure that the counter value is valid
1924: IF (p_unit_threshold_tbl(i).OPERATION_FLAG = 'C' OR p_unit_threshold_tbl(i).OPERATION_FLAG = 'M') THEN
1925: IF (p_unit_threshold_tbl(i).COUNTER_VALUE IS NULL OR p_unit_threshold_tbl(i).COUNTER_VALUE = FND_API.G_MISS_NUM) THEN
1926: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CNTR_VALUE_MISSING');
1927: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1928: FND_MSG_PUB.ADD;
1929: x_return_status := FND_API.G_RET_STS_ERROR;
1930: ELSE
1931: -- Ensure that the entered value is not lesser than the Net Reading
1929: x_return_status := FND_API.G_RET_STS_ERROR;
1930: ELSE
1931: -- Ensure that the entered value is not lesser than the Net Reading
1932: IF (p_unit_threshold_tbl(i).COUNTER_VALUE < l_net_reading) THEN
1933: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_LESSER');
1934: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1935: FND_MESSAGE.Set_Token('ENTVAL', p_unit_threshold_tbl(i).COUNTER_VALUE);
1936: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
1937: FND_MSG_PUB.ADD;
1930: ELSE
1931: -- Ensure that the entered value is not lesser than the Net Reading
1932: IF (p_unit_threshold_tbl(i).COUNTER_VALUE < l_net_reading) THEN
1933: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_LESSER');
1934: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1935: FND_MESSAGE.Set_Token('ENTVAL', p_unit_threshold_tbl(i).COUNTER_VALUE);
1936: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
1937: FND_MSG_PUB.ADD;
1938: x_return_status := FND_API.G_RET_STS_ERROR;
1931: -- Ensure that the entered value is not lesser than the Net Reading
1932: IF (p_unit_threshold_tbl(i).COUNTER_VALUE < l_net_reading) THEN
1933: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_LESSER');
1934: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1935: FND_MESSAGE.Set_Token('ENTVAL', p_unit_threshold_tbl(i).COUNTER_VALUE);
1936: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
1937: FND_MSG_PUB.ADD;
1938: x_return_status := FND_API.G_RET_STS_ERROR;
1939: END IF;
1932: IF (p_unit_threshold_tbl(i).COUNTER_VALUE < l_net_reading) THEN
1933: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_LESSER');
1934: FND_MESSAGE.Set_Token('COUNTER', p_unit_threshold_tbl(i).COUNTER_NAME);
1935: FND_MESSAGE.Set_Token('ENTVAL', p_unit_threshold_tbl(i).COUNTER_VALUE);
1936: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
1937: FND_MSG_PUB.ADD;
1938: x_return_status := FND_API.G_RET_STS_ERROR;
1939: END IF;
1940: END IF;
2044:
2045: -- Ensure that for Modify or delete operation, the accomplishment id is present
2046: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M' OR p_unit_accomplish_tbl(i).OPERATION_FLAG = 'D') THEN
2047: IF (p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID IS NULL OR p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID = FND_API.G_MISS_NUM) THEN
2048: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCOMPLISH_ID_NULL');
2049: FND_MSG_PUB.ADD;
2050: x_return_status := FND_API.G_RET_STS_ERROR;
2051: END IF;
2052: END IF;
2056: OPEN l_ue_id_check_csr(p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID,
2057: p_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID);
2058: FETCH l_ue_id_check_csr INTO l_junk;
2059: IF (l_ue_id_check_csr%NOTFOUND) THEN
2060: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
2061: FND_MESSAGE.Set_Token('UEID', p_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID);
2062: FND_MSG_PUB.ADD;
2063: x_return_status := FND_API.G_RET_STS_ERROR;
2064: END IF;
2057: p_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID);
2058: FETCH l_ue_id_check_csr INTO l_junk;
2059: IF (l_ue_id_check_csr%NOTFOUND) THEN
2060: FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_ID_INVALID');
2061: FND_MESSAGE.Set_Token('UEID', p_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID);
2062: FND_MSG_PUB.ADD;
2063: x_return_status := FND_API.G_RET_STS_ERROR;
2064: END IF;
2065: CLOSE l_ue_id_check_csr;
2067:
2068: -- Ensure that the Counter ID is valid for a Create or Modify operation
2069: IF (p_unit_accomplish_tbl(i).COUNTER_ID IS NULL OR p_unit_accomplish_tbl(i).COUNTER_ID = FND_API.G_MISS_NUM) THEN
2070: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'C' OR p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M') THEN
2071: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
2072: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2073: FND_MSG_PUB.ADD;
2074: x_return_status := FND_API.G_RET_STS_ERROR;
2075: END IF;
2068: -- Ensure that the Counter ID is valid for a Create or Modify operation
2069: IF (p_unit_accomplish_tbl(i).COUNTER_ID IS NULL OR p_unit_accomplish_tbl(i).COUNTER_ID = FND_API.G_MISS_NUM) THEN
2070: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'C' OR p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M') THEN
2071: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
2072: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2073: FND_MSG_PUB.ADD;
2074: x_return_status := FND_API.G_RET_STS_ERROR;
2075: END IF;
2076: END IF;
2082: -- to allow for init-accomplishment deletion along with counter values; so there is a
2083: -- possibility for accomplishment date to be null or g-miss date.
2084: IF (p_accomplishment_date IS NULL OR p_accomplishment_date = FND_API.G_MISS_DATE)
2085: THEN
2086: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHD_DATE_NULL');
2087: FND_MSG_PUB.ADD;
2088: x_return_status := FND_API.G_RET_STS_ERROR;
2089: RETURN;
2090: END IF;
2092: OPEN l_validate_counter_csr(p_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID,
2093: p_unit_accomplish_tbl(i).COUNTER_ID);
2094: FETCH l_validate_counter_csr INTO l_junk;
2095: IF (l_validate_counter_csr%NOTFOUND) THEN
2096: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
2097: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2098: FND_MSG_PUB.ADD;
2099: x_return_status := FND_API.G_RET_STS_ERROR;
2100: ELSE
2093: p_unit_accomplish_tbl(i).COUNTER_ID);
2094: FETCH l_validate_counter_csr INTO l_junk;
2095: IF (l_validate_counter_csr%NOTFOUND) THEN
2096: FND_MESSAGE.Set_Name('AHL','AHL_UMP_COUNTER_INVALID');
2097: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2098: FND_MSG_PUB.ADD;
2099: x_return_status := FND_API.G_RET_STS_ERROR;
2100: ELSE
2101: IF (p_ue_status_code = 'INIT-ACCOMPLISHED') THEN
2132: -- Retrieve object version no. for this accomplishment
2133: OPEN l_get_prev_ctr_csr(p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
2134: FETCH l_get_prev_ctr_csr into l_prev_object_version_no, l_prev_counter;
2135: IF (l_get_prev_ctr_csr%NOTFOUND) THEN
2136: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSH_ID_INVALID');
2137: FND_MESSAGE.Set_Token('ACCOMPLISHMENTID', p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
2138: FND_MSG_PUB.ADD;
2139: CLOSE l_get_prev_ctr_csr;
2140: x_return_status := FND_API.G_RET_STS_ERROR;
2133: OPEN l_get_prev_ctr_csr(p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
2134: FETCH l_get_prev_ctr_csr into l_prev_object_version_no, l_prev_counter;
2135: IF (l_get_prev_ctr_csr%NOTFOUND) THEN
2136: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSH_ID_INVALID');
2137: FND_MESSAGE.Set_Token('ACCOMPLISHMENTID', p_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
2138: FND_MSG_PUB.ADD;
2139: CLOSE l_get_prev_ctr_csr;
2140: x_return_status := FND_API.G_RET_STS_ERROR;
2141: RETURN;
2142: END IF;
2143: CLOSE l_get_prev_ctr_csr;
2144: -- Check if object version no is different
2145: IF(l_prev_object_version_no <> p_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
2146: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
2147: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2148: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2149: FND_MSG_PUB.ADD;
2150: x_return_status := FND_API.G_RET_STS_ERROR;
2143: CLOSE l_get_prev_ctr_csr;
2144: -- Check if object version no is different
2145: IF(l_prev_object_version_no <> p_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
2146: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
2147: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2148: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2149: FND_MSG_PUB.ADD;
2150: x_return_status := FND_API.G_RET_STS_ERROR;
2151: RETURN;
2144: -- Check if object version no is different
2145: IF(l_prev_object_version_no <> p_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
2146: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
2147: -- FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2148: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2149: FND_MSG_PUB.ADD;
2150: x_return_status := FND_API.G_RET_STS_ERROR;
2151: RETURN;
2152: END IF;
2152: END IF;
2153: -- For modify operation, ensure that the counter (id) has not changed
2154: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M') THEN
2155: IF (p_unit_accomplish_tbl(i).COUNTER_ID <> l_prev_counter) THEN
2156: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACC_COUNTER_CHANGED');
2157: FND_MSG_PUB.ADD;
2158: x_return_status := FND_API.G_RET_STS_ERROR;
2159: END IF;
2160: END IF;
2162:
2163: -- Ensure that the counter value is valid
2164: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'C' OR p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M') THEN
2165: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE IS NULL OR p_unit_accomplish_tbl(i).COUNTER_VALUE = FND_API.G_MISS_NUM) THEN
2166: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CNTR_VALUE_MISSING');
2167: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2168: FND_MSG_PUB.ADD;
2169: x_return_status := FND_API.G_RET_STS_ERROR;
2170: ELSE
2163: -- Ensure that the counter value is valid
2164: IF (p_unit_accomplish_tbl(i).OPERATION_FLAG = 'C' OR p_unit_accomplish_tbl(i).OPERATION_FLAG = 'M') THEN
2165: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE IS NULL OR p_unit_accomplish_tbl(i).COUNTER_VALUE = FND_API.G_MISS_NUM) THEN
2166: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CNTR_VALUE_MISSING');
2167: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2168: FND_MSG_PUB.ADD;
2169: x_return_status := FND_API.G_RET_STS_ERROR;
2170: ELSE
2171: -- Ensure that the entered value is not greater than the Net Reading
2169: x_return_status := FND_API.G_RET_STS_ERROR;
2170: ELSE
2171: -- Ensure that the entered value is not greater than the Net Reading
2172: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE > l_net_reading) THEN
2173: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_GREATER');
2174: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2175: FND_MESSAGE.Set_Token('ENTVAL', p_unit_accomplish_tbl(i).COUNTER_VALUE);
2176: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
2177: FND_MSG_PUB.ADD;
2170: ELSE
2171: -- Ensure that the entered value is not greater than the Net Reading
2172: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE > l_net_reading) THEN
2173: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_GREATER');
2174: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2175: FND_MESSAGE.Set_Token('ENTVAL', p_unit_accomplish_tbl(i).COUNTER_VALUE);
2176: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
2177: FND_MSG_PUB.ADD;
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2171: -- Ensure that the entered value is not greater than the Net Reading
2172: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE > l_net_reading) THEN
2173: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_GREATER');
2174: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2175: FND_MESSAGE.Set_Token('ENTVAL', p_unit_accomplish_tbl(i).COUNTER_VALUE);
2176: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
2177: FND_MSG_PUB.ADD;
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2179: END IF;
2172: IF (p_unit_accomplish_tbl(i).COUNTER_VALUE > l_net_reading) THEN
2173: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CTR_VAL_GREATER');
2174: FND_MESSAGE.Set_Token('COUNTER', p_unit_accomplish_tbl(i).COUNTER_NAME);
2175: FND_MESSAGE.Set_Token('ENTVAL', p_unit_accomplish_tbl(i).COUNTER_VALUE);
2176: FND_MESSAGE.Set_Token('CURRVAL', l_net_reading);
2177: FND_MSG_PUB.ADD;
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2179: END IF;
2180: END IF;
2366: l_status_code := null;
2367: END IF;
2368: -- raise error if date is null or g_miss.
2369: ELSIF (l_accomplished_date IS NULL OR l_accomplished_date = FND_API.G_MISS_DATE) THEN
2370: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHD_DATE_NULL');
2371: FND_MSG_PUB.ADD;
2372: RETURN;
2373: END IF;
2374: CLOSE l_accomplish_exists_csr;
2385: END IF;
2386:
2387: -- If object version no is different, write error message and skip to next unit effectivity
2388: IF(l_ue_rec.OBJECT_VERSION_NUMBER <> p_unit_Effectivity_rec.OBJECT_VERSION_NUMBER) THEN
2389: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_CHANGED');
2390: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2391: FND_MSG_PUB.ADD;
2392: RETURN;
2393: END IF;
2386:
2387: -- If object version no is different, write error message and skip to next unit effectivity
2388: IF(l_ue_rec.OBJECT_VERSION_NUMBER <> p_unit_Effectivity_rec.OBJECT_VERSION_NUMBER) THEN
2389: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UE_CHANGED');
2390: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2391: FND_MSG_PUB.ADD;
2392: RETURN;
2393: END IF;
2394:
2405: p_mr_header_id => l_ue_rec.MR_HEADER_ID,
2406: x_counters => l_counters_msg,
2407: x_return_status => l_return_status);
2408: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2409: FND_MESSAGE.Set_Name('AHL','AHL_UMP_MISSING_COUNTERS');
2410: FND_MESSAGE.Set_Token('COUNTERS', l_counters_msg);
2411: FND_MSG_PUB.ADD;
2412: RETURN;
2413: END IF;
2406: x_counters => l_counters_msg,
2407: x_return_status => l_return_status);
2408: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2409: FND_MESSAGE.Set_Name('AHL','AHL_UMP_MISSING_COUNTERS');
2410: FND_MESSAGE.Set_Token('COUNTERS', l_counters_msg);
2411: FND_MSG_PUB.ADD;
2412: RETURN;
2413: END IF;
2414: END IF;
2583: IF (l_unit_deferral_operation = 'D') THEN
2584: -- Check Object version.
2585: IF (l_unit_def_rec.object_version_number <> p_unit_Effectivity_rec.unit_deferral_object_version)
2586: THEN
2587: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2588: FND_MSG_PUB.ADD;
2589: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2590: END IF;
2591:
2595:
2596: -- Check Object version.
2597: IF (l_unit_def_rec.object_version_number <> p_unit_Effectivity_rec.unit_deferral_object_version)
2598: THEN
2599: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2600: FND_MSG_PUB.ADD;
2601: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602: END IF;
2603:
2747: p_mr_header_id => l_ue_rec.MR_HEADER_ID,
2748: x_counters => l_counters_msg,
2749: x_return_status => l_return_status);
2750: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2751: FND_MESSAGE.Set_Name('AHL','AHL_UMP_MISSING_COUNTERS');
2752: FND_MESSAGE.Set_Token('COUNTERS', l_counters_msg);
2753: FND_MSG_PUB.ADD;
2754: --RETURN;
2755: END IF;
2748: x_counters => l_counters_msg,
2749: x_return_status => l_return_status);
2750: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2751: FND_MESSAGE.Set_Name('AHL','AHL_UMP_MISSING_COUNTERS');
2752: FND_MESSAGE.Set_Token('COUNTERS', l_counters_msg);
2753: FND_MSG_PUB.ADD;
2754: --RETURN;
2755: END IF;
2756: END IF;
2964: FETCH l_get_unit_threshold_csr INTO l_threshold_details;
2965: IF (l_get_unit_threshold_csr%FOUND) THEN
2966: -- If object version no is different, write error message and skip to next unit effectivity
2967: IF(l_threshold_details.OBJECT_VERSION_NUMBER <> p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
2968: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
2969: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_threshold_tbl(i).COUNTER_NAME);
2970: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2971: FND_MSG_PUB.ADD;
2972: RETURN;
2965: IF (l_get_unit_threshold_csr%FOUND) THEN
2966: -- If object version no is different, write error message and skip to next unit effectivity
2967: IF(l_threshold_details.OBJECT_VERSION_NUMBER <> p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
2968: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
2969: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_threshold_tbl(i).COUNTER_NAME);
2970: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2971: FND_MSG_PUB.ADD;
2972: RETURN;
2973: END IF;
2966: -- If object version no is different, write error message and skip to next unit effectivity
2967: IF(l_threshold_details.OBJECT_VERSION_NUMBER <> p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER) THEN
2968: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRESHOLD_CHANGED');
2969: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_threshold_tbl(i).COUNTER_NAME);
2970: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2971: FND_MSG_PUB.ADD;
2972: RETURN;
2973: END IF;
2974: p_x_unit_threshold_tbl(i).OBJECT_VERSION_NUMBER := l_threshold_details.OBJECT_VERSION_NUMBER + 1;
3082: P_LAST_UPDATE_DATE => TRUNC(sysdate),
3083: P_LAST_UPDATED_BY => fnd_global.user_id,
3084: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3085: ELSE
3086: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRSHLD_ID_INVALID');
3087: FND_MESSAGE.Set_Token('THRESHOLDID', p_x_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
3088: FND_MSG_PUB.ADD;
3089: END IF;
3090: CLOSE l_get_unit_threshold_csr;
3083: P_LAST_UPDATED_BY => fnd_global.user_id,
3084: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3085: ELSE
3086: FND_MESSAGE.Set_Name('AHL','AHL_UMP_THRSHLD_ID_INVALID');
3087: FND_MESSAGE.Set_Token('THRESHOLDID', p_x_unit_threshold_tbl(i).UNIT_THRESHOLD_ID);
3088: FND_MSG_PUB.ADD;
3089: END IF;
3090: CLOSE l_get_unit_threshold_csr;
3091: ELSIF (p_x_unit_threshold_tbl(i).OPERATION_FLAG = 'C') THEN
3096: IF (l_get_def_csr%FOUND) THEN
3097: OPEN l_get_dup_counter_csr(l_unit_deferral_id, p_x_unit_threshold_tbl(i).COUNTER_ID);
3098: FETCH l_get_dup_counter_csr INTO l_junk;
3099: IF (l_get_dup_counter_csr%FOUND) THEN
3100: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUPLICATE_COUNTER');
3101: FND_MESSAGE.Set_Token('COUNTERID', p_x_unit_threshold_tbl(i).COUNTER_ID);
3102: FND_MSG_PUB.ADD;
3103: CLOSE l_get_dup_counter_csr;
3104: RETURN;
3097: OPEN l_get_dup_counter_csr(l_unit_deferral_id, p_x_unit_threshold_tbl(i).COUNTER_ID);
3098: FETCH l_get_dup_counter_csr INTO l_junk;
3099: IF (l_get_dup_counter_csr%FOUND) THEN
3100: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUPLICATE_COUNTER');
3101: FND_MESSAGE.Set_Token('COUNTERID', p_x_unit_threshold_tbl(i).COUNTER_ID);
3102: FND_MSG_PUB.ADD;
3103: CLOSE l_get_dup_counter_csr;
3104: RETURN;
3105: END IF;
3229: P_CREATED_BY => fnd_global.user_id,
3230: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3231: ELSE
3232: -- unrecognized operation flag
3233: FND_MESSAGE.Set_Name('AHL','AHL_UMP_OPERATION_INVALID');
3234: FND_MESSAGE.Set_Token('OPERATION', p_x_unit_threshold_tbl(i).OPERATION_FLAG);
3235: FND_MSG_PUB.ADD;
3236: END IF;
3237: END LOOP;
3230: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3231: ELSE
3232: -- unrecognized operation flag
3233: FND_MESSAGE.Set_Name('AHL','AHL_UMP_OPERATION_INVALID');
3234: FND_MESSAGE.Set_Token('OPERATION', p_x_unit_threshold_tbl(i).OPERATION_FLAG);
3235: FND_MSG_PUB.ADD;
3236: END IF;
3237: END LOOP;
3238: END IF;
3299: FETCH l_get_unit_accomplish_csr INTO l_accomplish_details;
3300: IF (l_get_unit_accomplish_csr%FOUND) THEN
3301: -- If object version no is different, write error message and skip to next unit effectivity
3302: IF(l_accomplish_details.OBJECT_VERSION_NUMBER <> p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
3303: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
3304: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_accomplish_tbl(i).COUNTER_NAME);
3305: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3306: FND_MSG_PUB.ADD;
3307: RETURN;
3300: IF (l_get_unit_accomplish_csr%FOUND) THEN
3301: -- If object version no is different, write error message and skip to next unit effectivity
3302: IF(l_accomplish_details.OBJECT_VERSION_NUMBER <> p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
3303: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
3304: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_accomplish_tbl(i).COUNTER_NAME);
3305: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3306: FND_MSG_PUB.ADD;
3307: RETURN;
3308: END IF;
3301: -- If object version no is different, write error message and skip to next unit effectivity
3302: IF(l_accomplish_details.OBJECT_VERSION_NUMBER <> p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER) THEN
3303: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSHMNT_CHANGED');
3304: -- FND_MESSAGE.Set_Token('COUNTER', p_x_unit_accomplish_tbl(i).COUNTER_NAME);
3305: FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3306: FND_MSG_PUB.ADD;
3307: RETURN;
3308: END IF;
3309: p_x_unit_accomplish_tbl(i).OBJECT_VERSION_NUMBER := l_accomplish_details.OBJECT_VERSION_NUMBER + 1;
3415: P_LAST_UPDATE_DATE => TRUNC(sysdate),
3416: P_LAST_UPDATED_BY => fnd_global.user_id,
3417: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3418: ELSE
3419: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSH_ID_INVALID');
3420: FND_MESSAGE.Set_Token('ACCOMPLISHMENTID', p_x_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
3421: FND_MSG_PUB.ADD;
3422: END IF;
3423: CLOSE l_get_unit_accomplish_csr;
3416: P_LAST_UPDATED_BY => fnd_global.user_id,
3417: P_LAST_UPDATE_LOGIN => fnd_global.login_id);
3418: ELSE
3419: FND_MESSAGE.Set_Name('AHL','AHL_UMP_ACCMPLSH_ID_INVALID');
3420: FND_MESSAGE.Set_Token('ACCOMPLISHMENTID', p_x_unit_accomplish_tbl(i).UNIT_ACCOMPLISH_ID);
3421: FND_MSG_PUB.ADD;
3422: END IF;
3423: CLOSE l_get_unit_accomplish_csr;
3424: ELSIF (p_x_unit_accomplish_tbl(i).OPERATION_FLAG = 'C') THEN
3425: -- insert row
3426: OPEN l_get_dup_counter_csr(p_x_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID, p_x_unit_accomplish_tbl(i).COUNTER_ID);
3427: FETCH l_get_dup_counter_csr INTO l_junk;
3428: IF (l_get_dup_counter_csr%FOUND) THEN
3429: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUPLICATE_COUNTER');
3430: FND_MESSAGE.Set_Token('COUNTERID', p_x_unit_accomplish_tbl(i).COUNTER_ID);
3431: FND_MSG_PUB.ADD;
3432: CLOSE l_get_dup_counter_csr;
3433: RETURN;
3426: OPEN l_get_dup_counter_csr(p_x_unit_accomplish_tbl(i).UNIT_EFFECTIVITY_ID, p_x_unit_accomplish_tbl(i).COUNTER_ID);
3427: FETCH l_get_dup_counter_csr INTO l_junk;
3428: IF (l_get_dup_counter_csr%FOUND) THEN
3429: FND_MESSAGE.Set_Name('AHL','AHL_UMP_DUPLICATE_COUNTER');
3430: FND_MESSAGE.Set_Token('COUNTERID', p_x_unit_accomplish_tbl(i).COUNTER_ID);
3431: FND_MSG_PUB.ADD;
3432: CLOSE l_get_dup_counter_csr;
3433: RETURN;
3434: END IF;
3546: END IF;
3547: */
3548: ELSE
3549: -- unrecognized operation flag
3550: FND_MESSAGE.Set_Name('AHL','AHL_UMP_OPERATION_INVALID');
3551: FND_MESSAGE.Set_Token('OPERATION', p_x_unit_accomplish_tbl(i).OPERATION_FLAG);
3552: FND_MSG_PUB.ADD;
3553: END IF;
3554: END LOOP;
3547: */
3548: ELSE
3549: -- unrecognized operation flag
3550: FND_MESSAGE.Set_Name('AHL','AHL_UMP_OPERATION_INVALID');
3551: FND_MESSAGE.Set_Token('OPERATION', p_x_unit_accomplish_tbl(i).OPERATION_FLAG);
3552: FND_MSG_PUB.ADD;
3553: END IF;
3554: END LOOP;
3555: END IF;
4158: END IF;
4159:
4160: IF (l_old_mr_header_id IS NULL) THEN
4161: x_return_status := FND_API.G_RET_STS_ERROR;
4162: FND_MESSAGE.Set_Name('AHL','AHL_UMP_TERMNT_MR_DET_MAND');
4163: FND_MSG_PUB.ADD;
4164: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4165: -- RETURN;
4166: END IF;
4167:
4168: /*
4169: IF (l_new_mr_header_id IS NULL) THEN
4170: x_return_status := FND_API.G_RET_STS_ERROR;
4171: FND_MESSAGE.Set_Name('AHL','AHL_UMP_CUR_MR_DET_MAND');
4172: FND_MSG_PUB.ADD;
4173: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4174: -- RETURN;
4175: END IF;
4180: IF (l_get_effective_to_date%FOUND) THEN
4181: --check if effective to date has passed
4182: /* IF( TRUNC(l_effective_to_date) > TRUNC(SYSDATE)) THEN
4183: x_return_status := FND_API.G_RET_STS_ERROR;
4184: FND_MESSAGE.Set_Name('AHL','AHL_UMP_EFF_TO_DATE');
4185: FND_MESSAGE.Set_Token('EFF_TO', l_effective_to_date);
4186: FND_MSG_PUB.ADD;
4187: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4188: END IF; */
4181: --check if effective to date has passed
4182: /* IF( TRUNC(l_effective_to_date) > TRUNC(SYSDATE)) THEN
4183: x_return_status := FND_API.G_RET_STS_ERROR;
4184: FND_MESSAGE.Set_Name('AHL','AHL_UMP_EFF_TO_DATE');
4185: FND_MESSAGE.Set_Token('EFF_TO', l_effective_to_date);
4186: FND_MSG_PUB.ADD;
4187: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4188: END IF; */
4189:
4203: OPEN l_get_effective_from_date(l_new_mr_header_id);
4204: FETCH l_get_effective_from_date INTO l_effective_from_date;
4205: IF (l_get_effective_from_date%NOTFOUND) THEN
4206: x_return_status := FND_API.G_RET_STS_ERROR;
4207: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
4208: FND_MESSAGE.Set_Token('MR_ID',l_new_mr_header_id);
4209: FND_MSG_PUB.ADD;
4210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4211: END IF;
4204: FETCH l_get_effective_from_date INTO l_effective_from_date;
4205: IF (l_get_effective_from_date%NOTFOUND) THEN
4206: x_return_status := FND_API.G_RET_STS_ERROR;
4207: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
4208: FND_MESSAGE.Set_Token('MR_ID',l_new_mr_header_id);
4209: FND_MSG_PUB.ADD;
4210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4211: END IF;
4212: CLOSE l_get_effective_from_date;
4856: (p_version_number IS NOT NULL AND p_version_number <> FND_API.G_MISS_NUM) THEN
4857: OPEN ahl_mr_headers_csr(p_mr_title, p_version_number);
4858: FETCH ahl_mr_headers_csr INTO l_mr_id, l_effective_to;
4859: IF (ahl_mr_headers_csr%NOTFOUND) THEN
4860: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_TITLE_INVALID');
4861: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4862: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4857: OPEN ahl_mr_headers_csr(p_mr_title, p_version_number);
4858: FETCH ahl_mr_headers_csr INTO l_mr_id, l_effective_to;
4859: IF (ahl_mr_headers_csr%NOTFOUND) THEN
4860: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_TITLE_INVALID');
4861: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4862: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4865: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_MR_EXPIRED');
4858: FETCH ahl_mr_headers_csr INTO l_mr_id, l_effective_to;
4859: IF (ahl_mr_headers_csr%NOTFOUND) THEN
4860: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_TITLE_INVALID');
4861: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4862: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4865: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_MR_EXPIRED');
4866: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4861: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4862: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4865: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_MR_EXPIRED');
4866: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4867: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4868: FND_MSG_PUB.ADD;
4869: ELSE
4862: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4865: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_MR_EXPIRED');
4866: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4867: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4868: FND_MSG_PUB.ADD;
4869: ELSE
4870: p_x_mr_id := l_mr_id;
4863: FND_MSG_PUB.ADD;
4864: ELSIF (trunc(l_effective_to) < trunc(sysdate)) THEN
4865: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_MR_EXPIRED');
4866: FND_MESSAGE.Set_Token('TITLE',p_mr_title);
4867: FND_MESSAGE.Set_Token('VERSION',p_version_number);
4868: FND_MSG_PUB.ADD;
4869: ELSE
4870: p_x_mr_id := l_mr_id;
4871: END IF;
4893: IF (p_unit_name IS NOT NULL AND p_unit_name <> FND_API.G_MISS_CHAR) THEN
4894: OPEN ahl_uc_headers_csr(p_unit_name);
4895: FETCH ahl_uc_headers_csr INTO l_uc_header_id;
4896: IF (ahl_uc_headers_csr%NOTFOUND) THEN
4897: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_UNIT_INVALID');
4898: FND_MESSAGE.Set_Token('NAME',p_unit_name);
4899: FND_MSG_PUB.ADD;
4900: ELSE
4901: p_x_uc_header_id := l_uc_header_id;
4894: OPEN ahl_uc_headers_csr(p_unit_name);
4895: FETCH ahl_uc_headers_csr INTO l_uc_header_id;
4896: IF (ahl_uc_headers_csr%NOTFOUND) THEN
4897: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_UNIT_INVALID');
4898: FND_MESSAGE.Set_Token('NAME',p_unit_name);
4899: FND_MSG_PUB.ADD;
4900: ELSE
4901: p_x_uc_header_id := l_uc_header_id;
4902: END IF;
4926: -- get instance_id.
4927: OPEN csi_item_instances_csr (p_csi_instance_number);
4928: FETCH csi_item_instances_csr INTO l_csi_item_instance_id;
4929: IF (csi_item_instances_csr%NOTFOUND) THEN
4930: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_INST_NOTFOUND');
4931: FND_MESSAGE.Set_Token('NUMBER',p_csi_instance_number);
4932: FND_MSG_PUB.ADD;
4933: ELSE
4934: p_x_csi_item_instance_id := l_csi_item_instance_id;
4927: OPEN csi_item_instances_csr (p_csi_instance_number);
4928: FETCH csi_item_instances_csr INTO l_csi_item_instance_id;
4929: IF (csi_item_instances_csr%NOTFOUND) THEN
4930: FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_INST_NOTFOUND');
4931: FND_MESSAGE.Set_Token('NUMBER',p_csi_instance_number);
4932: FND_MSG_PUB.ADD;
4933: ELSE
4934: p_x_csi_item_instance_id := l_csi_item_instance_id;
4935: END IF;
4981: OPEN ahl_mr_headers_csr (p_mr_header_id);
4982: FETCH ahl_mr_headers_csr INTO l_mr_title, l_version_number,
4983: l_effective_to, l_effective_from;
4984: IF (ahl_mr_headers_csr%NOTFOUND) THEN
4985: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
4986: FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
4987: FND_MSG_PUB.ADD;
4988: CLOSE ahl_mr_headers_csr;
4989: --dbms_output.put_line('MR not found.');
4982: FETCH ahl_mr_headers_csr INTO l_mr_title, l_version_number,
4983: l_effective_to, l_effective_from;
4984: IF (ahl_mr_headers_csr%NOTFOUND) THEN
4985: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
4986: FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
4987: FND_MSG_PUB.ADD;
4988: CLOSE ahl_mr_headers_csr;
4989: --dbms_output.put_line('MR not found.');
4990: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4989: --dbms_output.put_line('MR not found.');
4990: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4991: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
4992: trunc(sysdate) > trunc(l_effective_to)) THEN
4993: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
4994: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
4995: FND_MESSAGE.Set_Token('VERSION',l_version_number);
4996: FND_MSG_PUB.ADD;
4997: --dbms_output.put_line('MR is not valid for today.');
4990: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4991: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
4992: trunc(sysdate) > trunc(l_effective_to)) THEN
4993: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
4994: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
4995: FND_MESSAGE.Set_Token('VERSION',l_version_number);
4996: FND_MSG_PUB.ADD;
4997: --dbms_output.put_line('MR is not valid for today.');
4998: END IF;
4991: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
4992: trunc(sysdate) > trunc(l_effective_to)) THEN
4993: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
4994: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
4995: FND_MESSAGE.Set_Token('VERSION',l_version_number);
4996: FND_MSG_PUB.ADD;
4997: --dbms_output.put_line('MR is not valid for today.');
4998: END IF;
4999:
5004: IF (p_x_csi_item_instance_id IS NOT NULL) THEN
5005: OPEN csi_item_instances_csr (p_x_csi_item_instance_id);
5006: FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
5007: IF (csi_item_instances_csr%NOTFOUND) THEN
5008: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
5009: FND_MESSAGE.Set_Token('INST_ID', p_x_csi_item_instance_id);
5010: FND_MSG_PUB.ADD;
5011: CLOSE csi_item_instances_csr;
5012: --dbms_output.put_line('Instance not found');
5005: OPEN csi_item_instances_csr (p_x_csi_item_instance_id);
5006: FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
5007: IF (csi_item_instances_csr%NOTFOUND) THEN
5008: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
5009: FND_MESSAGE.Set_Token('INST_ID', p_x_csi_item_instance_id);
5010: FND_MSG_PUB.ADD;
5011: CLOSE csi_item_instances_csr;
5012: --dbms_output.put_line('Instance not found');
5013: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5011: CLOSE csi_item_instances_csr;
5012: --dbms_output.put_line('Instance not found');
5013: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5014: ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
5015: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
5016: FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
5017: FND_MSG_PUB.ADD;
5018: --dbms_output.put_line('Instance has expired');
5019: END IF;
5012: --dbms_output.put_line('Instance not found');
5013: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5014: ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
5015: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
5016: FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
5017: FND_MSG_PUB.ADD;
5018: --dbms_output.put_line('Instance has expired');
5019: END IF;
5020: CLOSE csi_item_instances_csr;
5025: OPEN ahl_unit_config_headers_csr (p_unit_config_header_id);
5026: FETCH ahl_unit_config_headers_csr INTO l_name, l_active_start_date,
5027: l_active_end_date, l_csi_item_instance_id;
5028: IF (ahl_unit_config_headers_csr%NOTFOUND) THEN
5029: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UNIT_NOTFOUND');
5030: FND_MESSAGE.Set_Token('UNIT_ID',p_unit_config_header_id);
5031: FND_MSG_PUB.ADD;
5032: --dbms_output.put_line('Unit not found');
5033: CLOSE ahl_unit_config_headers_csr;
5026: FETCH ahl_unit_config_headers_csr INTO l_name, l_active_start_date,
5027: l_active_end_date, l_csi_item_instance_id;
5028: IF (ahl_unit_config_headers_csr%NOTFOUND) THEN
5029: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UNIT_NOTFOUND');
5030: FND_MESSAGE.Set_Token('UNIT_ID',p_unit_config_header_id);
5031: FND_MSG_PUB.ADD;
5032: --dbms_output.put_line('Unit not found');
5033: CLOSE ahl_unit_config_headers_csr;
5034: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5035:
5036: -- Dates obsoleted 11.5.10.
5037: -- ELSIF (trunc(l_active_start_date) > trunc(sysdate) OR
5038: -- trunc(sysdate) > trunc(l_active_end_date)) THEN
5039: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UC_EXPIRED');
5040: -- FND_MESSAGE.Set_Token('NAME',l_name);
5041: -- FND_MSG_PUB.ADD;
5042: --dbms_output.put_line('Unit has expired');
5043: ELSIF (p_x_csi_item_instance_id IS NULL) THEN
5036: -- Dates obsoleted 11.5.10.
5037: -- ELSIF (trunc(l_active_start_date) > trunc(sysdate) OR
5038: -- trunc(sysdate) > trunc(l_active_end_date)) THEN
5039: -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UC_EXPIRED');
5040: -- FND_MESSAGE.Set_Token('NAME',l_name);
5041: -- FND_MSG_PUB.ADD;
5042: --dbms_output.put_line('Unit has expired');
5043: ELSIF (p_x_csi_item_instance_id IS NULL) THEN
5044: p_x_csi_item_instance_id := l_csi_item_instance_id;
5047:
5048: -- Validate for too many parameters.
5049: -- If both item instance and mr_id present then raise error.
5050: IF (p_mr_header_id IS NOT NULL AND p_x_csi_item_instance_id IS NOT NULL) THEN
5051: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5052: -- Fix for bug# 3962903.
5053: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5054: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5055: FND_MSG_PUB.ADD;
5049: -- If both item instance and mr_id present then raise error.
5050: IF (p_mr_header_id IS NOT NULL AND p_x_csi_item_instance_id IS NOT NULL) THEN
5051: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5052: -- Fix for bug# 3962903.
5053: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5054: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5055: FND_MSG_PUB.ADD;
5056: END IF;
5057:
5050: IF (p_mr_header_id IS NOT NULL AND p_x_csi_item_instance_id IS NOT NULL) THEN
5051: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5052: -- Fix for bug# 3962903.
5053: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5054: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5055: FND_MSG_PUB.ADD;
5056: END IF;
5057:
5058: -- If both p_unit_config_header_id and p_x_csi_item_instance_id are not
5059: -- null, then p_x_csi_item_instance_id should match l_csi_item_instance_id.
5060: IF ((p_x_csi_item_instance_id IS NOT NULL) AND
5061: (p_unit_config_header_id IS NOT NULL)) THEN
5062: IF (l_csi_item_instance_id <> p_x_csi_item_instance_id) THEN
5063: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5064: -- Fix for bug# 3962903.
5065: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5066: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5067: FND_MSG_PUB.ADD;
5061: (p_unit_config_header_id IS NOT NULL)) THEN
5062: IF (l_csi_item_instance_id <> p_x_csi_item_instance_id) THEN
5063: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5064: -- Fix for bug# 3962903.
5065: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5066: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5067: FND_MSG_PUB.ADD;
5068: END IF;
5069: END IF;
5062: IF (l_csi_item_instance_id <> p_x_csi_item_instance_id) THEN
5063: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INPUT_INVALID');
5064: -- Fix for bug# 3962903.
5065: --FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5066: --FND_MESSAGE.Set_Token('INST_ID',p_x_csi_item_instance_id);
5067: FND_MSG_PUB.ADD;
5068: END IF;
5069: END IF;
5070:
5118: OPEN ahl_mr_headers_csr (p_mr_header_id);
5119: FETCH ahl_mr_headers_csr INTO l_mr_title, l_version_number,
5120: l_effective_to, l_effective_from;
5121: IF (ahl_mr_headers_csr%NOTFOUND) THEN
5122: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
5123: FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5124: FND_MSG_PUB.ADD;
5125: CLOSE ahl_mr_headers_csr;
5126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5119: FETCH ahl_mr_headers_csr INTO l_mr_title, l_version_number,
5120: l_effective_to, l_effective_from;
5121: IF (ahl_mr_headers_csr%NOTFOUND) THEN
5122: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
5123: FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
5124: FND_MSG_PUB.ADD;
5125: CLOSE ahl_mr_headers_csr;
5126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5127: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
5125: CLOSE ahl_mr_headers_csr;
5126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5127: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
5128: trunc(sysdate) > trunc(l_effective_to)) THEN
5129: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
5130: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
5131: FND_MESSAGE.Set_Token('VERSION',l_version_number);
5132: FND_MSG_PUB.ADD;
5133: END IF;
5126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5127: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
5128: trunc(sysdate) > trunc(l_effective_to)) THEN
5129: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
5130: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
5131: FND_MESSAGE.Set_Token('VERSION',l_version_number);
5132: FND_MSG_PUB.ADD;
5133: END IF;
5134:
5127: ELSIF ( trunc(l_effective_from) > trunc(sysdate) OR
5128: trunc(sysdate) > trunc(l_effective_to)) THEN
5129: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_EXPIRED');
5130: FND_MESSAGE.Set_Token('TITLE',l_mr_title);
5131: FND_MESSAGE.Set_Token('VERSION',l_version_number);
5132: FND_MSG_PUB.ADD;
5133: END IF;
5134:
5135: CLOSE ahl_mr_headers_csr;
5139: IF (p_csi_item_instance_id IS NOT NULL) THEN
5140: OPEN csi_item_instances_csr (p_csi_item_instance_id);
5141: FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
5142: IF (csi_item_instances_csr%NOTFOUND) THEN
5143: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
5144: FND_MESSAGE.Set_Token('INST_ID', p_csi_item_instance_id);
5145: FND_MSG_PUB.ADD;
5146: CLOSE csi_item_instances_csr;
5147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5140: OPEN csi_item_instances_csr (p_csi_item_instance_id);
5141: FETCH csi_item_instances_csr INTO l_instance_number, l_active_end_date;
5142: IF (csi_item_instances_csr%NOTFOUND) THEN
5143: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INSTID_NOTFOUND');
5144: FND_MESSAGE.Set_Token('INST_ID', p_csi_item_instance_id);
5145: FND_MSG_PUB.ADD;
5146: CLOSE csi_item_instances_csr;
5147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5148: ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
5145: FND_MSG_PUB.ADD;
5146: CLOSE csi_item_instances_csr;
5147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5148: ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
5149: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
5150: FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
5151: FND_MSG_PUB.ADD;
5152: END IF;
5153: CLOSE csi_item_instances_csr;
5146: CLOSE csi_item_instances_csr;
5147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5148: ELSIF (trunc(l_active_end_date) < trunc(sysdate)) THEN
5149: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_INST_EXPIRED');
5150: FND_MESSAGE.Set_Token('NUMBER', l_instance_number);
5151: FND_MSG_PUB.ADD;
5152: END IF;
5153: CLOSE csi_item_instances_csr;
5154: END IF;
5160: OPEN contract_number_csr;
5161: FETCH contract_number_csr INTO l_dummy;
5162: IF (contract_number_csr%NOTFOUND)
5163: THEN
5164: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_CONT_NOTFOUND');
5165: FND_MESSAGE.Set_Token('CONTRACT',p_contract_number);
5166: FND_MESSAGE.Set_Token('MODIFIER',p_contract_modifier);
5167: FND_MSG_PUB.ADD;
5168: END IF;
5161: FETCH contract_number_csr INTO l_dummy;
5162: IF (contract_number_csr%NOTFOUND)
5163: THEN
5164: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_CONT_NOTFOUND');
5165: FND_MESSAGE.Set_Token('CONTRACT',p_contract_number);
5166: FND_MESSAGE.Set_Token('MODIFIER',p_contract_modifier);
5167: FND_MSG_PUB.ADD;
5168: END IF;
5169: CLOSE contract_number_csr;
5162: IF (contract_number_csr%NOTFOUND)
5163: THEN
5164: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_CONT_NOTFOUND');
5165: FND_MESSAGE.Set_Token('CONTRACT',p_contract_number);
5166: FND_MESSAGE.Set_Token('MODIFIER',p_contract_modifier);
5167: FND_MSG_PUB.ADD;
5168: END IF;
5169: CLOSE contract_number_csr;
5170: ELSIF (p_contract_modifier IS NOT NULL and p_contract_modifier <> FND_API.G_MISS_CHAR)
5168: END IF;
5169: CLOSE contract_number_csr;
5170: ELSIF (p_contract_modifier IS NOT NULL and p_contract_modifier <> FND_API.G_MISS_CHAR)
5171: THEN
5172: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_CONT_MOD_INV');
5173: FND_MESSAGE.Set_Token('MODIFIER',p_contract_modifier);
5174: FND_MSG_PUB.ADD;
5175: END IF;
5176:
5169: CLOSE contract_number_csr;
5170: ELSIF (p_contract_modifier IS NOT NULL and p_contract_modifier <> FND_API.G_MISS_CHAR)
5171: THEN
5172: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_CONT_MOD_INV');
5173: FND_MESSAGE.Set_Token('MODIFIER',p_contract_modifier);
5174: FND_MSG_PUB.ADD;
5175: END IF;
5176:
5177: -- Validate for too many params(any combination of 2 is an issue)
5184: p_contract_number IS NOT NULL
5185: )
5186: )
5187: THEN
5188: FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_PM_INPUT_INVALID');
5189: FND_MSG_PUB.ADD;
5190: END IF;
5191: END Validate_PM_Input_Parameters;
5192: -- Tamal: Bug #4207212, #4114368 End
5466: END IF;
5467:
5468: CLOSE check_for_pc_csr;
5469: -- invalid pc_header_id
5470: FND_MESSAGE.Set_Name('AHL','AHL_PC_NOT_FOUND');
5471: FND_MSG_PUB.ADD;
5472:
5473: retcode := 2;
5474: errbuf := FND_MSG_PUB.Get;