[Home] [Help]
33: FUNCTION validate_parameters (
34: p_lgcy_prout_rec IN OUT NOCOPY lgcy_prout_rec_type
35: ) RETURN BOOLEAN IS
36: --
37: l_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
38: --
39: BEGIN
40: --
41: -- If the Person Number is not passed then log an error and continue.
40: --
41: -- If the Person Number is not passed then log an error and continue.
42: --
43: IF (p_lgcy_prout_rec.person_number IS NULL) THEN
44: l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
45: FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PER_NUM_NULL');
46: FND_MSG_PUB.ADD;
47: END IF;
48: --
48: --
49: -- If the Program Code is not passed then log an error and continue.
50: --
51: IF (p_lgcy_prout_rec.program_cd IS NULL) THEN
52: l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
53: FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PRGM_CD_NULL');
54: FND_MSG_PUB.ADD;
55: END IF;
56: --
57: -- If the Alternate Code of the Progression Calendar is not passed then
58: -- log an error and continue.
59: --
60: IF (p_lgcy_prout_rec.prg_cal_alternate_code IS NULL) THEN
61: l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
62: FND_MESSAGE.SET_NAME ('IGS', 'IGS_PRG_ALT_CODE_NULL');
63: FND_MSG_PUB.ADD;
64: END IF;
65: --
65: --
66: -- If the Decision Status is not passed then log an error and continue.
67: --
68: IF (p_lgcy_prout_rec.decision_status IS NULL) THEN
69: l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
70: FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_DECI_STAT_NULL');
71: FND_MSG_PUB.ADD;
72: END IF;
73: --
116: BEGIN
117: --
118: -- Initialise the return value
119: --
120: x_return_value := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
121: --
122: -- Derive Person ID from Person Number
123: --
124: p_person_id := igs_ge_gen_003.get_person_id (p_lgcy_prout_rec.person_number);
127: --
128: IF (p_person_id IS NULL) THEN
129: FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
130: FND_MSG_PUB.ADD;
131: x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
132: RETURN;
133: END IF;
134: --
135: -- Derive the Progression Calendar Information for the Alternate Code passed
159: ELSIF (l_return_status = 'MULTIPLE') THEN
160: FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_MULTI_ALT_CODE');
161: FND_MSG_PUB.ADD;
162: END IF;
163: x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
164: RETURN;
165: END IF;
166: --
167: -- Derive the Outcome Sequence Number.
191: p_start_dt => p_org_start_dt
192: )) THEN
193: FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_ORG_START_DT');
194: FND_MSG_PUB.ADD;
195: x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
196: END IF;
197: END IF;
198: --
199: -- Derive the Hold Effect Type if Progression Outcome Type is specified.
208: IF (cur_hold_effect_type%NOTFOUND) THEN
209: IF (l_s_progression_outcome_type NOT IN ('AWARD', 'MANUAL', 'NOPENALTY', 'REPEATYR', 'ADVANCE')) THEN
210: FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_HOLD_EFCT');
211: FND_MSG_PUB.ADD;
212: x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
213: END IF;
214: END IF;
215: --
216: p_hold_effect_type := l_hold_effect_type;
235: p_org_start_dt IN igs_pe_hz_parties.ou_start_dt%TYPE
236:
237: ) RETURN BOOLEAN IS
238: --
239: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
240: --
241: BEGIN
242: --
243: -- Foreign Key checks (Parent Existence)
445: FROM igs_pr_ou_type pot
446: WHERE pot.progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type;
447: --
448: rec_sca cur_sca%ROWTYPE;
449: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
450: x_message_name fnd_new_messages.message_name%TYPE;
451: v_s_progression_outcome_type igs_pr_ou_type.s_progression_outcome_type%TYPE;
452: l_where_clause VARCHAR2(2000);
453: TYPE ref_cur IS REF CURSOR;
894: p_person_id IN igs_pe_person.person_id%TYPE,
895: p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
896: ) RETURN BOOLEAN IS
897: --
898: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
899: --
900: BEGIN
901: --
902: -- Foreign Key Checks (Checking Parent Existence)
954: p_lgcy_prout_rec IN lgcy_prout_rec_type,
955: p_hold_effect_type IN VARCHAR2
956: ) RETURN BOOLEAN IS
957: --
958: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
959: --
960: BEGIN
961: --
962: -- Check that spo_program_cd is Not Null only when the hold effect type is
986: p_person_id IN igs_pe_person.person_id%TYPE,
987: p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
988: ) RETURN BOOLEAN IS
989: --
990: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
991: --
992: BEGIN
993: --
994: -- Foreign Key Checks (Checking Parent Existence)
1061: AND us.version_number = p_lgcy_prout_rec.us_version_number
1062: AND us.unit_set_status = uss.unit_set_status
1063: AND uss.s_unit_set_status <> 'PLANNED';
1064: --
1065: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1066: x_message_name fnd_new_messages.message_name%TYPE;
1067: l_record_found VARCHAR2(1) := 'N';
1068: --
1069: BEGIN
1117: p_person_id IN igs_pe_person.person_id%TYPE,
1118: p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1119: ) RETURN BOOLEAN IS
1120: --
1121: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1122: --
1123: BEGIN
1124: --
1125: -- Foreign Key Checks (Checking Parent Existence)
1197: p_lgcy_prout_rec IN lgcy_prout_rec_type,
1198: p_hold_effect_type IN VARCHAR2
1199: ) RETURN BOOLEAN IS
1200: --
1201: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1202: --
1203: BEGIN
1204: --
1205: -- Check that Unit is Not Null with s_unit_type as 'EXCLUDED' only when the
1236: p_person_id IN igs_pe_person.person_id%TYPE,
1237: p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1238: ) RETURN BOOLEAN IS
1239: --
1240: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1241: --
1242: BEGIN
1243: --
1244: -- Foreign Key Checks (Checking Parent Existence)
1305: SELECT positive_outcome_ind
1306: FROM igs_pr_ou_type
1307: WHERE progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type;
1308: --
1309: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1310: rec_award cur_award%ROWTYPE;
1311: rec_positive_otcm cur_positive_otcm%ROWTYPE;
1312: --
1313: BEGIN
1354: p_person_id IN igs_pe_person.person_id%TYPE,
1355: p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1356: ) RETURN BOOLEAN IS
1357: --
1358: x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1359: --
1360: BEGIN
1361: --
1362: -- Foreign Key Checks (Checking Parent Existence)
1393: END validate_fnd_db_cons;
1394: --
1395: PROCEDURE create_outcome (
1396: p_api_version IN NUMBER,
1397: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1398: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1399: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1400: x_return_status OUT NOCOPY VARCHAR2,
1401: x_msg_count OUT NOCOPY NUMBER,
1394: --
1395: PROCEDURE create_outcome (
1396: p_api_version IN NUMBER,
1397: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1398: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1399: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1400: x_return_status OUT NOCOPY VARCHAR2,
1401: x_msg_count OUT NOCOPY NUMBER,
1402: x_msg_data OUT NOCOPY VARCHAR2,
1395: PROCEDURE create_outcome (
1396: p_api_version IN NUMBER,
1397: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1398: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1399: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1400: x_return_status OUT NOCOPY VARCHAR2,
1401: x_msg_count OUT NOCOPY NUMBER,
1402: x_msg_data OUT NOCOPY VARCHAR2,
1403: p_lgcy_prout_rec IN OUT NOCOPY lgcy_prout_rec_type
1423: SAVEPOINT create_outcome;
1424: --
1425: -- Standard call to check for call compatibility.
1426: --
1427: IF (NOT FND_API.Compatible_API_Call (
1428: l_api_version,
1429: p_api_version,
1430: l_api_name,
1431: g_pkg_name)) THEN
1428: l_api_version,
1429: p_api_version,
1430: l_api_name,
1431: g_pkg_name)) THEN
1432: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433: END IF;
1434: --
1435: -- Initialize message list if p_init_msg_list is set to TRUE.
1436: --
1433: END IF;
1434: --
1435: -- Initialize message list if p_init_msg_list is set to TRUE.
1436: --
1437: IF (FND_API.to_Boolean (p_init_msg_list)) THEN
1438: FND_MSG_PUB.Initialize;
1439: END IF;
1440: --
1441: -- Initialize API return status to success.
1439: END IF;
1440: --
1441: -- Initialize API return status to success.
1442: --
1443: x_return_status := FND_API.G_RET_STS_SUCCESS;
1444: --
1445: -- API body
1446: --
1447: -- Validate the params passed to this API
1446: --
1447: -- Validate the params passed to this API
1448: --
1449: IF (NOT validate_parameters (p_lgcy_prout_rec)) THEN
1450: x_return_status := FND_API.G_RET_STS_ERROR;
1451: RAISE FND_API.G_EXC_ERROR;
1452: END IF;
1453: --
1454: -- Derive Progression Outcome Type data
1447: -- Validate the params passed to this API
1448: --
1449: IF (NOT validate_parameters (p_lgcy_prout_rec)) THEN
1450: x_return_status := FND_API.G_RET_STS_ERROR;
1451: RAISE FND_API.G_EXC_ERROR;
1452: END IF;
1453: --
1454: -- Derive Progression Outcome Type data
1455: --
1463: p_org_start_dt => l_org_start_dt,
1464: x_return_value => l_return_boolean_value
1465: );
1466: IF (NOT l_return_boolean_value) THEN
1467: x_return_status := FND_API.G_RET_STS_ERROR;
1468: RAISE FND_API.G_EXC_ERROR;
1469: END IF;
1470: --
1471: -- Check Student Progression Outcome Database Constraints
1464: x_return_value => l_return_boolean_value
1465: );
1466: IF (NOT l_return_boolean_value) THEN
1467: x_return_status := FND_API.G_RET_STS_ERROR;
1468: RAISE FND_API.G_EXC_ERROR;
1469: END IF;
1470: --
1471: -- Check Student Progression Outcome Database Constraints
1472: --
1475: p_person_id => l_person_id,
1476: p_sequence_number => l_outcome_sequence_number,
1477: p_org_start_dt => l_org_start_dt
1478: )) THEN
1479: x_return_status := FND_API.G_RET_STS_ERROR;
1480: RAISE FND_API.G_EXC_ERROR;
1481: END IF;
1482: --
1483: -- Validate all the business rules before inserting a record IGS_PR_STDNT_PR_OU
1476: p_sequence_number => l_outcome_sequence_number,
1477: p_org_start_dt => l_org_start_dt
1478: )) THEN
1479: x_return_status := FND_API.G_RET_STS_ERROR;
1480: RAISE FND_API.G_EXC_ERROR;
1481: END IF;
1482: --
1483: -- Validate all the business rules before inserting a record IGS_PR_STDNT_PR_OU
1484: --
1490: p_outcome_sequence_number => l_outcome_sequence_number,
1491: p_hold_effect_type => l_hold_effect_type,
1492: p_decision_ou_start_dt => l_org_start_dt
1493: )) THEN
1494: x_return_status := FND_API.G_RET_STS_ERROR;
1495: RAISE FND_API.G_EXC_ERROR;
1496: END IF;
1497: --
1498: -- Create an entry in the Student Program Outcome table is does not already exist
1491: p_hold_effect_type => l_hold_effect_type,
1492: p_decision_ou_start_dt => l_org_start_dt
1493: )) THEN
1494: x_return_status := FND_API.G_RET_STS_ERROR;
1495: RAISE FND_API.G_EXC_ERROR;
1496: END IF;
1497: --
1498: -- Create an entry in the Student Program Outcome table is does not already exist
1499: --
1549: p_lgcy_prout_rec => p_lgcy_prout_rec,
1550: p_person_id => l_person_id,
1551: p_sequence_number => l_outcome_sequence_number
1552: )) THEN
1553: x_return_status := FND_API.G_RET_STS_ERROR;
1554: RAISE FND_API.G_EXC_ERROR;
1555: END IF;
1556: --
1557: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_PS
1550: p_person_id => l_person_id,
1551: p_sequence_number => l_outcome_sequence_number
1552: )) THEN
1553: x_return_status := FND_API.G_RET_STS_ERROR;
1554: RAISE FND_API.G_EXC_ERROR;
1555: END IF;
1556: --
1557: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_PS
1558: --
1559: IF (NOT validate_progression (
1560: p_lgcy_prout_rec => p_lgcy_prout_rec,
1561: p_hold_effect_type => l_hold_effect_type
1562: )) THEN
1563: x_return_status := FND_API.G_RET_STS_ERROR;
1564: RAISE FND_API.G_EXC_ERROR;
1565: END IF;
1566: --
1567: -- Create an entry in the igs_pr_stdnt_pr_ps if the hold effect type is
1560: p_lgcy_prout_rec => p_lgcy_prout_rec,
1561: p_hold_effect_type => l_hold_effect_type
1562: )) THEN
1563: x_return_status := FND_API.G_RET_STS_ERROR;
1564: RAISE FND_API.G_EXC_ERROR;
1565: END IF;
1566: --
1567: -- Create an entry in the igs_pr_stdnt_pr_ps if the hold effect type is
1568: -- either 'SUS_COURSE' or 'EXC_COURSE'
1596: p_lgcy_prout_rec => p_lgcy_prout_rec,
1597: p_person_id => l_person_id,
1598: p_sequence_number => l_outcome_sequence_number
1599: )) THEN
1600: x_return_status := FND_API.G_RET_STS_ERROR;
1601: RAISE FND_API.G_EXC_ERROR;
1602: END IF;
1603: --
1604: -- Validate all the business rules before inserting a record into IGS_PR_SDT_PR_UNT_ST
1597: p_person_id => l_person_id,
1598: p_sequence_number => l_outcome_sequence_number
1599: )) THEN
1600: x_return_status := FND_API.G_RET_STS_ERROR;
1601: RAISE FND_API.G_EXC_ERROR;
1602: END IF;
1603: --
1604: -- Validate all the business rules before inserting a record into IGS_PR_SDT_PR_UNT_ST
1605: --
1608: p_person_id => l_person_id,
1609: p_sequence_number => l_outcome_sequence_number,
1610: p_hold_effect_type => l_hold_effect_type
1611: )) THEN
1612: x_return_status := FND_API.G_RET_STS_ERROR;
1613: RAISE FND_API.G_EXC_ERROR;
1614: END IF;
1615: --
1616: -- Create an entry in the igs_pr_sdt_pr_unt_st if the hold effect type is 'EXC_CRS_US'
1609: p_sequence_number => l_outcome_sequence_number,
1610: p_hold_effect_type => l_hold_effect_type
1611: )) THEN
1612: x_return_status := FND_API.G_RET_STS_ERROR;
1613: RAISE FND_API.G_EXC_ERROR;
1614: END IF;
1615: --
1616: -- Create an entry in the igs_pr_sdt_pr_unt_st if the hold effect type is 'EXC_CRS_US'
1617: --
1639: p_lgcy_prout_rec => p_lgcy_prout_rec,
1640: p_person_id => l_person_id,
1641: p_sequence_number => l_outcome_sequence_number
1642: )) THEN
1643: x_return_status := FND_API.G_RET_STS_ERROR;
1644: RAISE FND_API.G_EXC_ERROR;
1645: END IF;
1646: --
1647: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_UNIT
1640: p_person_id => l_person_id,
1641: p_sequence_number => l_outcome_sequence_number
1642: )) THEN
1643: x_return_status := FND_API.G_RET_STS_ERROR;
1644: RAISE FND_API.G_EXC_ERROR;
1645: END IF;
1646: --
1647: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_UNIT
1648: --
1649: IF (NOT validate_unit (
1650: p_lgcy_prout_rec => p_lgcy_prout_rec,
1651: p_hold_effect_type => l_hold_effect_type
1652: )) THEN
1653: x_return_status := FND_API.G_RET_STS_ERROR;
1654: RAISE FND_API.G_EXC_ERROR;
1655: END IF;
1656: --
1657: -- Create an entry in the igs_pr_stdnt_pr_unit if the hold effect type is
1650: p_lgcy_prout_rec => p_lgcy_prout_rec,
1651: p_hold_effect_type => l_hold_effect_type
1652: )) THEN
1653: x_return_status := FND_API.G_RET_STS_ERROR;
1654: RAISE FND_API.G_EXC_ERROR;
1655: END IF;
1656: --
1657: -- Create an entry in the igs_pr_stdnt_pr_unit if the hold effect type is
1658: -- either 'EXC_CRS_U' or 'RQRD_CRS_U'
1678: p_lgcy_prout_rec => p_lgcy_prout_rec,
1679: p_person_id => l_person_id,
1680: p_sequence_number => l_outcome_sequence_number
1681: )) THEN
1682: x_return_status := FND_API.G_RET_STS_ERROR;
1683: RAISE FND_API.G_EXC_ERROR;
1684: END IF;
1685: --
1686: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_AWD
1679: p_person_id => l_person_id,
1680: p_sequence_number => l_outcome_sequence_number
1681: )) THEN
1682: x_return_status := FND_API.G_RET_STS_ERROR;
1683: RAISE FND_API.G_EXC_ERROR;
1684: END IF;
1685: --
1686: -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_AWD
1687: --
1687: --
1688: IF (NOT validate_award (
1689: p_lgcy_prout_rec => p_lgcy_prout_rec
1690: )) THEN
1691: x_return_status := FND_API.G_RET_STS_ERROR;
1692: RAISE FND_API.G_EXC_ERROR;
1693: END IF;
1694: --
1695: -- Create an entry in the Student Program Awards table if the outcome is positive
1688: IF (NOT validate_award (
1689: p_lgcy_prout_rec => p_lgcy_prout_rec
1690: )) THEN
1691: x_return_status := FND_API.G_RET_STS_ERROR;
1692: RAISE FND_API.G_EXC_ERROR;
1693: END IF;
1694: --
1695: -- Create an entry in the Student Program Awards table if the outcome is positive
1696: --
1714: p_lgcy_prout_rec => p_lgcy_prout_rec,
1715: p_person_id => l_person_id,
1716: p_sequence_number => l_outcome_sequence_number
1717: )) THEN
1718: x_return_status := FND_API.G_RET_STS_ERROR;
1719: RAISE FND_API.G_EXC_ERROR;
1720: END IF;
1721: --
1722: -- Create an entry in the igs_pr_stdnt_pr_fnd table
1715: p_person_id => l_person_id,
1716: p_sequence_number => l_outcome_sequence_number
1717: )) THEN
1718: x_return_status := FND_API.G_RET_STS_ERROR;
1719: RAISE FND_API.G_EXC_ERROR;
1720: END IF;
1721: --
1722: -- Create an entry in the igs_pr_stdnt_pr_fnd table
1723: --
1736: -- End of API body
1737: --
1738: -- Standard check of p_commit.
1739: --
1740: IF FND_API.To_Boolean (p_commit) THEN
1741: COMMIT WORK;
1742: END IF;
1743: --
1744: -- Standard call to get message count and if count is 1, get message info.
1748: p_data => x_msg_data
1749: );
1750: --
1751: EXCEPTION
1752: WHEN FND_API.G_EXC_ERROR THEN
1753: ROLLBACK TO create_outcome;
1754: x_return_status := FND_API.G_RET_STS_ERROR;
1755: FND_MSG_PUB.Count_And_Get (
1756: p_count => x_msg_count,
1750: --
1751: EXCEPTION
1752: WHEN FND_API.G_EXC_ERROR THEN
1753: ROLLBACK TO create_outcome;
1754: x_return_status := FND_API.G_RET_STS_ERROR;
1755: FND_MSG_PUB.Count_And_Get (
1756: p_count => x_msg_count,
1757: p_data => x_msg_data
1758: );
1756: p_count => x_msg_count,
1757: p_data => x_msg_data
1758: );
1759: --
1760: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761: ROLLBACK TO create_outcome;
1762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763: FND_MSG_PUB.Count_And_Get (
1764: p_count => x_msg_count,
1758: );
1759: --
1760: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761: ROLLBACK TO create_outcome;
1762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763: FND_MSG_PUB.Count_And_Get (
1764: p_count => x_msg_count,
1765: p_data => x_msg_data
1766: );
1766: );
1767: --
1768: WHEN OTHERS THEN
1769: ROLLBACK TO create_outcome;
1770: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771: --
1772: -- kdande; 02-Jan-2003; Bug# 2732568; Removed the call to
1773: -- FND_MSG_PUB.Add_Exc_Msg to avoid message repeatition.
1774: --