[Home] [Help]
228: l_debug_str := 'IGS_SV_PRTNR_STP_ERR error in Submit_Event.';
229: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
230: END IF;
231:
232: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_PRTNR_STP_ERR'); -- No trading partner setup found
233: FND_MSG_PUB.Add;
234: RAISE FND_API.G_EXC_ERROR;
235:
236: END IF;
764: INTO l_name_suffix;
765:
766: IF (c_name_suffix%NOTFOUND) AND p_code IS NOT NULL THEN
767:
768: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
769: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Suffix');
770: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
771: Put_Log_Msg(FND_MESSAGE.Get,1);
772:
765:
766: IF (c_name_suffix%NOTFOUND) AND p_code IS NOT NULL THEN
767:
768: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
769: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Suffix');
770: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
771: Put_Log_Msg(FND_MESSAGE.Get,1);
772:
773: l_name_suffix := NULL;
766: IF (c_name_suffix%NOTFOUND) AND p_code IS NOT NULL THEN
767:
768: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
769: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Suffix');
770: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
771: Put_Log_Msg(FND_MESSAGE.Get,1);
772:
773: l_name_suffix := NULL;
774:
767:
768: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
769: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Suffix');
770: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
771: Put_Log_Msg(FND_MESSAGE.Get,1);
772:
773: l_name_suffix := NULL;
774:
775: END IF;
915: INTO l_name_suffix;
916:
917: IF p_code IS NOT NULL AND c_name%FOUND THEN
918:
919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_TERR_CD_ERR'); -- Error
920: FND_MESSAGE.SET_TOKEN('CNTRY_CODE', p_code);
921: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
922: Put_Log_Msg(FND_MESSAGE.Get,1);
923:
916:
917: IF p_code IS NOT NULL AND c_name%FOUND THEN
918:
919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_TERR_CD_ERR'); -- Error
920: FND_MESSAGE.SET_TOKEN('CNTRY_CODE', p_code);
921: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
922: Put_Log_Msg(FND_MESSAGE.Get,1);
923:
924: CLOSE c_name;
917: IF p_code IS NOT NULL AND c_name%FOUND THEN
918:
919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_TERR_CD_ERR'); -- Error
920: FND_MESSAGE.SET_TOKEN('CNTRY_CODE', p_code);
921: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
922: Put_Log_Msg(FND_MESSAGE.Get,1);
923:
924: CLOSE c_name;
925: /* Debug */
918:
919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_TERR_CD_ERR'); -- Error
920: FND_MESSAGE.SET_TOKEN('CNTRY_CODE', p_code);
921: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_num);
922: Put_Log_Msg(FND_MESSAGE.Get,1);
923:
924: CLOSE c_name;
925: /* Debug */
926: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1306: IF p_data_rec.issuing_reason = 'C' THEN
1307:
1308: IF p_data_rec.curr_session_end_date IS NULL OR p_data_rec.next_session_start_date IS NULL THEN
1309:
1310: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_C_REQ_ERR'); -- SEVIS common error
1311: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1312:
1313: Put_Log_Msg(FND_MESSAGE.Get,1);
1314: l_not_valid := TRUE;
1307:
1308: IF p_data_rec.curr_session_end_date IS NULL OR p_data_rec.next_session_start_date IS NULL THEN
1309:
1310: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_C_REQ_ERR'); -- SEVIS common error
1311: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1312:
1313: Put_Log_Msg(FND_MESSAGE.Get,1);
1314: l_not_valid := TRUE;
1315:
1309:
1310: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_C_REQ_ERR'); -- SEVIS common error
1311: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1312:
1313: Put_Log_Msg(FND_MESSAGE.Get,1);
1314: l_not_valid := TRUE;
1315:
1316: END IF;
1317:
1318: ELSIF p_data_rec.issuing_reason = 'T' THEN
1319:
1320: IF p_data_rec.Transfer_from_school IS NULL THEN
1321:
1322: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_T_REQ_ERR'); -- SEVIS common error
1323: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1324:
1325: Put_Log_Msg(FND_MESSAGE.Get,1);
1326: l_not_valid := TRUE;
1319:
1320: IF p_data_rec.Transfer_from_school IS NULL THEN
1321:
1322: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_T_REQ_ERR'); -- SEVIS common error
1323: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1324:
1325: Put_Log_Msg(FND_MESSAGE.Get,1);
1326: l_not_valid := TRUE;
1327:
1321:
1322: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_T_REQ_ERR'); -- SEVIS common error
1323: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1324:
1325: Put_Log_Msg(FND_MESSAGE.Get,1);
1326: l_not_valid := TRUE;
1327:
1328: END IF;
1329:
1330: ELSIF p_data_rec.issuing_reason = 'I' THEN
1331:
1332: IF l_prog_start_date IS NULL OR l_prog_start_date < trunc(sysdate) THEN
1333:
1334: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRG_START_DT_ERR'); -- SEVIS common error
1335: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1336: FND_MESSAGE.SET_TOKEN('PRGM_DATE', l_prog_start_date );
1337:
1338: Put_Log_Msg(FND_MESSAGE.Get,1);
1331:
1332: IF l_prog_start_date IS NULL OR l_prog_start_date < trunc(sysdate) THEN
1333:
1334: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRG_START_DT_ERR'); -- SEVIS common error
1335: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1336: FND_MESSAGE.SET_TOKEN('PRGM_DATE', l_prog_start_date );
1337:
1338: Put_Log_Msg(FND_MESSAGE.Get,1);
1339: l_not_valid := TRUE;
1332: IF l_prog_start_date IS NULL OR l_prog_start_date < trunc(sysdate) THEN
1333:
1334: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRG_START_DT_ERR'); -- SEVIS common error
1335: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1336: FND_MESSAGE.SET_TOKEN('PRGM_DATE', l_prog_start_date );
1337:
1338: Put_Log_Msg(FND_MESSAGE.Get,1);
1339: l_not_valid := TRUE;
1340:
1334: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRG_START_DT_ERR'); -- SEVIS common error
1335: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1336: FND_MESSAGE.SET_TOKEN('PRGM_DATE', l_prog_start_date );
1337:
1338: Put_Log_Msg(FND_MESSAGE.Get,1);
1339: l_not_valid := TRUE;
1340:
1341: END IF;
1342:
1344: ELSIF p_data_rec.issuing_reason = 'O' THEN
1345:
1346: IF p_data_rec.other_reason IS NULL THEN
1347:
1348: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_O_REQ_ERR'); -- SEVIS common error
1349: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1350:
1351: Put_Log_Msg(FND_MESSAGE.Get,1);
1352: l_not_valid := TRUE;
1345:
1346: IF p_data_rec.other_reason IS NULL THEN
1347:
1348: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_O_REQ_ERR'); -- SEVIS common error
1349: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1350:
1351: Put_Log_Msg(FND_MESSAGE.Get,1);
1352: l_not_valid := TRUE;
1353:
1347:
1348: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ISSUE_O_REQ_ERR'); -- SEVIS common error
1349: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1350:
1351: Put_Log_Msg(FND_MESSAGE.Get,1);
1352: l_not_valid := TRUE;
1353:
1354: END IF;
1355:
1354: END IF;
1355:
1356: ELSIF p_person_rec.batch_type = 'E' AND p_data_rec.issuing_reason = 'CONT' AND p_data_rec.init_prgm_start_date IS NULL THEN
1357:
1358: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error - no initial date specified
1359: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1360:
1361: Put_Log_Msg(FND_MESSAGE.Get,1);
1362: l_not_valid := TRUE;
1355:
1356: ELSIF p_person_rec.batch_type = 'E' AND p_data_rec.issuing_reason = 'CONT' AND p_data_rec.init_prgm_start_date IS NULL THEN
1357:
1358: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error - no initial date specified
1359: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1360:
1361: Put_Log_Msg(FND_MESSAGE.Get,1);
1362: l_not_valid := TRUE;
1363:
1357:
1358: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error - no initial date specified
1359: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1360:
1361: Put_Log_Msg(FND_MESSAGE.Get,1);
1362: l_not_valid := TRUE;
1363:
1364: ELSIF p_person_rec.batch_type = 'E'
1365: AND ( p_data_rec.curr_session_end_date IS NULL
1364: ELSIF p_person_rec.batch_type = 'E'
1365: AND ( p_data_rec.curr_session_end_date IS NULL
1366: OR p_data_rec.next_session_start_date IS NULL ) THEN
1367:
1368: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error
1369: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1370:
1371: Put_Log_Msg(FND_MESSAGE.Get,1);
1372: l_not_valid := TRUE;
1365: AND ( p_data_rec.curr_session_end_date IS NULL
1366: OR p_data_rec.next_session_start_date IS NULL ) THEN
1367:
1368: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error
1369: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1370:
1371: Put_Log_Msg(FND_MESSAGE.Get,1);
1372: l_not_valid := TRUE;
1373:
1367:
1368: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- error
1369: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1370:
1371: Put_Log_Msg(FND_MESSAGE.Get,1);
1372: l_not_valid := TRUE;
1373:
1374: END IF;
1375:
1376: IF p_person_rec.batch_type = 'I' THEN
1377: IF ( p_data_rec.last_session_flag = 'Y'
1378: AND p_data_rec.next_session_start_date IS NOT NULL ) THEN
1379:
1380: --FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_LAST_SESN'); -- error
1381: --FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1382: p_data_rec.next_session_start_date := NULL;
1383:
1384: END IF;
1377: IF ( p_data_rec.last_session_flag = 'Y'
1378: AND p_data_rec.next_session_start_date IS NOT NULL ) THEN
1379:
1380: --FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_LAST_SESN'); -- error
1381: --FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
1382: p_data_rec.next_session_start_date := NULL;
1383:
1384: END IF;
1385: END IF;
1672: CLOSE c_hz_data;
1673:
1674: IF Validate_Name(p_data_rec.last_name) THEN
1675: l_not_valid := TRUE;
1676: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_CHAR');
1677: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1678: Put_Log_Msg(FND_MESSAGE.Get,1);
1679: END IF;
1680:
1673:
1674: IF Validate_Name(p_data_rec.last_name) THEN
1675: l_not_valid := TRUE;
1676: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_CHAR');
1677: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1678: Put_Log_Msg(FND_MESSAGE.Get,1);
1679: END IF;
1680:
1681: IF p_data_rec.birth_cntry_code = 'US' AND p_data_rec.birth_cntry_resn_code IS NULL
1674: IF Validate_Name(p_data_rec.last_name) THEN
1675: l_not_valid := TRUE;
1676: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_CHAR');
1677: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1678: Put_Log_Msg(FND_MESSAGE.Get,1);
1679: END IF;
1680:
1681: IF p_data_rec.birth_cntry_code = 'US' AND p_data_rec.birth_cntry_resn_code IS NULL
1682: THEN
1680:
1681: IF p_data_rec.birth_cntry_code = 'US' AND p_data_rec.birth_cntry_resn_code IS NULL
1682: THEN
1683: l_not_valid := TRUE;
1684: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CNTRY_RSN');
1685: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1686: Put_Log_Msg(FND_MESSAGE.Get,1);
1687: END IF;
1688:
1681: IF p_data_rec.birth_cntry_code = 'US' AND p_data_rec.birth_cntry_resn_code IS NULL
1682: THEN
1683: l_not_valid := TRUE;
1684: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CNTRY_RSN');
1685: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1686: Put_Log_Msg(FND_MESSAGE.Get,1);
1687: END IF;
1688:
1689: IF (p_data_rec.batch_id IS NULL) THEN
1682: THEN
1683: l_not_valid := TRUE;
1684: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CNTRY_RSN');
1685: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1686: Put_Log_Msg(FND_MESSAGE.Get,1);
1687: END IF;
1688:
1689: IF (p_data_rec.batch_id IS NULL) THEN
1690:
1706: IF (p_data_rec.last_name IS NULL OR
1707: p_data_rec.birth_date IS NULL OR
1708: p_data_rec.birth_cntry_code IS NULL) THEN
1709: l_not_valid := TRUE;
1710: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_REQ_FLD_ERR'); -- Required fields for both Exchange Visitor or Foreign students
1711: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1712: Put_Log_Msg(FND_MESSAGE.Get,1);
1713: END IF;
1714: --
1707: p_data_rec.birth_date IS NULL OR
1708: p_data_rec.birth_cntry_code IS NULL) THEN
1709: l_not_valid := TRUE;
1710: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_REQ_FLD_ERR'); -- Required fields for both Exchange Visitor or Foreign students
1711: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1712: Put_Log_Msg(FND_MESSAGE.Get,1);
1713: END IF;
1714: --
1715: -- Validate all the data for required fields for Foreign students
1708: p_data_rec.birth_cntry_code IS NULL) THEN
1709: l_not_valid := TRUE;
1710: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_REQ_FLD_ERR'); -- Required fields for both Exchange Visitor or Foreign students
1711: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1712: Put_Log_Msg(FND_MESSAGE.Get,1);
1713: END IF;
1714: --
1715: -- Validate all the data for required fields for Foreign students
1716: --
1719: OR ( NVL(p_data_rec.visa_type,'X') NOT IN ('04','05') AND p_person_rec.dep_flag ='Y' AND p_person_rec.batch_type = 'I')
1720: OR ( NVL(p_data_rec.visa_type,'X') <> '03' AND p_person_rec.dep_flag ='N' AND p_person_rec.batch_type = 'E')
1721: OR ( NVL(p_data_rec.visa_type,'X') <> '06' AND p_person_rec.dep_flag ='Y' AND p_person_rec.batch_type = 'E') THEN
1722: l_not_valid := TRUE;
1723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_NIMG_REQ_FLD_ERR'); -- Required Fields for Foreign students
1724: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1725: Put_Log_Msg(FND_MESSAGE.Get,1);
1726: END IF;
1727:
1720: OR ( NVL(p_data_rec.visa_type,'X') <> '03' AND p_person_rec.dep_flag ='N' AND p_person_rec.batch_type = 'E')
1721: OR ( NVL(p_data_rec.visa_type,'X') <> '06' AND p_person_rec.dep_flag ='Y' AND p_person_rec.batch_type = 'E') THEN
1722: l_not_valid := TRUE;
1723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_NIMG_REQ_FLD_ERR'); -- Required Fields for Foreign students
1724: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1725: Put_Log_Msg(FND_MESSAGE.Get,1);
1726: END IF;
1727:
1728: -- 2908378 validation of legal residency code, birth city should fire for all persons
1721: OR ( NVL(p_data_rec.visa_type,'X') <> '06' AND p_person_rec.dep_flag ='Y' AND p_person_rec.batch_type = 'E') THEN
1722: l_not_valid := TRUE;
1723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_NIMG_REQ_FLD_ERR'); -- Required Fields for Foreign students
1724: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1725: Put_Log_Msg(FND_MESSAGE.Get,1);
1726: END IF;
1727:
1728: -- 2908378 validation of legal residency code, birth city should fire for all persons
1729: --
1733: IF (NVL(p_person_rec.dep_flag,'N') <> 'Y' AND p_person_rec.batch_type = 'E' AND(p_data_rec.legal_res_cntry_code IS NULL OR
1734: p_data_rec.birth_city IS NULL
1735: ) ) THEN
1736: l_not_valid := TRUE;
1737: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1738: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1739: Put_Log_Msg(FND_MESSAGE.Get,1);
1740: END IF;
1741:
1734: p_data_rec.birth_city IS NULL
1735: ) ) THEN
1736: l_not_valid := TRUE;
1737: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1738: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1739: Put_Log_Msg(FND_MESSAGE.Get,1);
1740: END IF;
1741:
1742: -- 2908378 validation of position_code, category_code should not fire for dependants, since they need not have EV form
1735: ) ) THEN
1736: l_not_valid := TRUE;
1737: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1738: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1739: Put_Log_Msg(FND_MESSAGE.Get,1);
1740: END IF;
1741:
1742: -- 2908378 validation of position_code, category_code should not fire for dependants, since they need not have EV form
1743: --
1747: p_data_rec.position_code IS NULL OR
1748: p_data_rec.category_code IS NULL
1749: ) ) THEN
1750: l_not_valid := TRUE;
1751: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1752: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1753: Put_Log_Msg(FND_MESSAGE.Get,1);
1754: END IF;
1755:
1748: p_data_rec.category_code IS NULL
1749: ) ) THEN
1750: l_not_valid := TRUE;
1751: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1752: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1753: Put_Log_Msg(FND_MESSAGE.Get,1);
1754: END IF;
1755:
1756:
1749: ) ) THEN
1750: l_not_valid := TRUE;
1751: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BIO_EV_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
1752: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1753: Put_Log_Msg(FND_MESSAGE.Get,1);
1754: END IF;
1755:
1756:
1757: IF (p_person_rec.batch_type = 'I' AND p_data_rec.citizen_cntry_code IS NULL) THEN
1755:
1756:
1757: IF (p_person_rec.batch_type = 'I' AND p_data_rec.citizen_cntry_code IS NULL) THEN
1758: l_not_valid := TRUE;
1759: FND_MESSAGE.SET_NAME('IGS', 'PERSON_NUMBER'); -- Required Fields for Exchange Visitor students
1760: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1761: Put_Log_Msg(FND_MESSAGE.Get,1);
1762: END IF;
1763:
1756:
1757: IF (p_person_rec.batch_type = 'I' AND p_data_rec.citizen_cntry_code IS NULL) THEN
1758: l_not_valid := TRUE;
1759: FND_MESSAGE.SET_NAME('IGS', 'PERSON_NUMBER'); -- Required Fields for Exchange Visitor students
1760: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1761: Put_Log_Msg(FND_MESSAGE.Get,1);
1762: END IF;
1763:
1764:
1757: IF (p_person_rec.batch_type = 'I' AND p_data_rec.citizen_cntry_code IS NULL) THEN
1758: l_not_valid := TRUE;
1759: FND_MESSAGE.SET_NAME('IGS', 'PERSON_NUMBER'); -- Required Fields for Exchange Visitor students
1760: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1761: Put_Log_Msg(FND_MESSAGE.Get,1);
1762: END IF;
1763:
1764:
1765: IF Check_US_Terr(p_person_rec.person_number,p_data_rec.citizen_cntry_code) ='E' THEN
1926: Put_Log_Msg('taxid pf person id '||p_person_rec.person_id ||' is '|| l_taxid,0);
1927: IF (c_alternate_id%FOUND) THEN
1928: p_data_rec.tax_id := SUBSTR(l_taxid, 1, 9);
1929: IF Is_Number(p_data_rec.tax_id) = '' OR Is_Number(p_data_rec.tax_id) IS NULL THEN
1930: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TAXID');
1931: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number);
1932: Put_Log_Msg(FND_MESSAGE.Get,1);
1933: l_not_valid := true;
1934: END IF;
1927: IF (c_alternate_id%FOUND) THEN
1928: p_data_rec.tax_id := SUBSTR(l_taxid, 1, 9);
1929: IF Is_Number(p_data_rec.tax_id) = '' OR Is_Number(p_data_rec.tax_id) IS NULL THEN
1930: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TAXID');
1931: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number);
1932: Put_Log_Msg(FND_MESSAGE.Get,1);
1933: l_not_valid := true;
1934: END IF;
1935: END IF;
1928: p_data_rec.tax_id := SUBSTR(l_taxid, 1, 9);
1929: IF Is_Number(p_data_rec.tax_id) = '' OR Is_Number(p_data_rec.tax_id) IS NULL THEN
1930: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TAXID');
1931: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number);
1932: Put_Log_Msg(FND_MESSAGE.Get,1);
1933: l_not_valid := true;
1934: END IF;
1935: END IF;
1936: CLOSE c_alternate_id;
1949: IF (l_new_ssn IS NOT NULL) THEN
1950:
1951: IF (LENGTH(l_new_ssn) <> 9) THEN
1952:
1953: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1954: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'SSN');
1955: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1956: Put_Log_Msg(FND_MESSAGE.Get,1);
1957: p_data_rec.ssn := NULL; -- Only warning for bad SSN. Just NULL out value to be sent.
1950:
1951: IF (LENGTH(l_new_ssn) <> 9) THEN
1952:
1953: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1954: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'SSN');
1955: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1956: Put_Log_Msg(FND_MESSAGE.Get,1);
1957: p_data_rec.ssn := NULL; -- Only warning for bad SSN. Just NULL out value to be sent.
1958:
1951: IF (LENGTH(l_new_ssn) <> 9) THEN
1952:
1953: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1954: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'SSN');
1955: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1956: Put_Log_Msg(FND_MESSAGE.Get,1);
1957: p_data_rec.ssn := NULL; -- Only warning for bad SSN. Just NULL out value to be sent.
1958:
1959: ELSE
1952:
1953: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1954: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'SSN');
1955: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1956: Put_Log_Msg(FND_MESSAGE.Get,1);
1957: p_data_rec.ssn := NULL; -- Only warning for bad SSN. Just NULL out value to be sent.
1958:
1959: ELSE
1960:
1968: -- Check to see if there is a state associated to the license
1969: --
1970: IF (p_data_rec.drivers_license IS NOT NULL AND
1971: p_data_rec.drivers_license_state IS NULL) THEN
1972: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1973: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Drivers License State');
1974: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1975: Put_Log_Msg(FND_MESSAGE.Get,1);
1976: END IF;
1969: --
1970: IF (p_data_rec.drivers_license IS NOT NULL AND
1971: p_data_rec.drivers_license_state IS NULL) THEN
1972: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1973: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Drivers License State');
1974: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1975: Put_Log_Msg(FND_MESSAGE.Get,1);
1976: END IF;
1977:
1970: IF (p_data_rec.drivers_license IS NOT NULL AND
1971: p_data_rec.drivers_license_state IS NULL) THEN
1972: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1973: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Drivers License State');
1974: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1975: Put_Log_Msg(FND_MESSAGE.Get,1);
1976: END IF;
1977:
1978: IF (p_data_rec.tax_id IS NOT NULL OR
1971: p_data_rec.drivers_license_state IS NULL) THEN
1972: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_WARN_FLD_ERR'); -- Warning message for optional field
1973: FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'Drivers License State');
1974: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
1975: Put_Log_Msg(FND_MESSAGE.Get,1);
1976: END IF;
1977:
1978: IF (p_data_rec.tax_id IS NOT NULL OR
1979: p_data_rec.ssn IS NOT NULL OR
2173:
2174: IF (p_data_rec(l_counter).country_code IS NULL OR
2175: p_data_rec(l_counter).address_line1 IS NULL) THEN
2176:
2177: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_F_ADDR_RQD_FLD_ERR'); -- Foreign address block error
2178: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2179:
2180: Put_Log_Msg(FND_MESSAGE.Get,1);
2181: l_not_valid := TRUE;
2174: IF (p_data_rec(l_counter).country_code IS NULL OR
2175: p_data_rec(l_counter).address_line1 IS NULL) THEN
2176:
2177: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_F_ADDR_RQD_FLD_ERR'); -- Foreign address block error
2178: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2179:
2180: Put_Log_Msg(FND_MESSAGE.Get,1);
2181: l_not_valid := TRUE;
2182:
2176:
2177: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_F_ADDR_RQD_FLD_ERR'); -- Foreign address block error
2178: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2179:
2180: Put_Log_Msg(FND_MESSAGE.Get,1);
2181: l_not_valid := TRUE;
2182:
2183: END IF;
2184: ELSE
2182:
2183: END IF;
2184: ELSE
2185: -- bug 5405935
2186: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2187: Put_Log_Msg(FND_MESSAGE.Get,1);
2188: l_not_valid := TRUE;
2189: END IF;
2190: END IF;
2183: END IF;
2184: ELSE
2185: -- bug 5405935
2186: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2187: Put_Log_Msg(FND_MESSAGE.Get,1);
2188: l_not_valid := TRUE;
2189: END IF;
2190: END IF;
2191:
2350: OR p_data_rec(l_counter).state IS NULL
2351: OR p_data_rec(l_counter).postal_code IS NULL
2352: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2353:
2354: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2355: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2356:
2357: Put_Log_Msg(FND_MESSAGE.Get,1);
2358: l_not_valid := TRUE;
2351: OR p_data_rec(l_counter).postal_code IS NULL
2352: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2353:
2354: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2355: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2356:
2357: Put_Log_Msg(FND_MESSAGE.Get,1);
2358: l_not_valid := TRUE;
2359:
2353:
2354: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2355: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2356:
2357: Put_Log_Msg(FND_MESSAGE.Get,1);
2358: l_not_valid := TRUE;
2359:
2360: END IF;
2361: ELSE
2359:
2360: END IF;
2361: ELSE
2362: -- bug 5405935
2363: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2364: Put_Log_Msg(FND_MESSAGE.Get,1);
2365: l_not_valid := TRUE;
2366: END IF;
2367:
2360: END IF;
2361: ELSE
2362: -- bug 5405935
2363: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2364: Put_Log_Msg(FND_MESSAGE.Get,1);
2365: l_not_valid := TRUE;
2366: END IF;
2367:
2368: IF l_not_valid THEN
2625: FETCH c_activity_site INTO l_site_count;
2626: CLOSE c_activity_site;
2627:
2628: IF l_site_count = 0 THEN
2629: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_SITE_REQ');
2630: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2631: Put_Log_Msg(FND_MESSAGE.Get,1);
2632: l_not_valid := TRUE;
2633: END IF;
2626: CLOSE c_activity_site;
2627:
2628: IF l_site_count = 0 THEN
2629: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_SITE_REQ');
2630: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2631: Put_Log_Msg(FND_MESSAGE.Get,1);
2632: l_not_valid := TRUE;
2633: END IF;
2634:
2627:
2628: IF l_site_count = 0 THEN
2629: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_SITE_REQ');
2630: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2631: Put_Log_Msg(FND_MESSAGE.Get,1);
2632: l_not_valid := TRUE;
2633: END IF;
2634:
2635: */
2638: FETCH c_primary_site INTO l_primary_site_count;
2639: CLOSE c_primary_site;
2640:
2641: IF l_primary_site_count <> 1 THEN
2642: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_PRIMARY_IND');
2643: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2644: Put_Log_Msg(FND_MESSAGE.Get,1);
2645: l_not_valid := TRUE;
2646: END IF;
2639: CLOSE c_primary_site;
2640:
2641: IF l_primary_site_count <> 1 THEN
2642: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_PRIMARY_IND');
2643: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2644: Put_Log_Msg(FND_MESSAGE.Get,1);
2645: l_not_valid := TRUE;
2646: END IF;
2647:
2640:
2641: IF l_primary_site_count <> 1 THEN
2642: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_PRIMARY_IND');
2643: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2644: Put_Log_Msg(FND_MESSAGE.Get,1);
2645: l_not_valid := TRUE;
2646: END IF;
2647:
2648: FOR c_addr_rec IN c_data_add LOOP
2689: OR p_data_rec(l_counter).state IS NULL
2690: OR p_data_rec(l_counter).postal_code IS NULL
2691: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2692:
2693: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2694: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2695:
2696: Put_Log_Msg(FND_MESSAGE.Get,1);
2697: l_not_valid := TRUE;
2690: OR p_data_rec(l_counter).postal_code IS NULL
2691: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2692:
2693: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2694: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2695:
2696: Put_Log_Msg(FND_MESSAGE.Get,1);
2697: l_not_valid := TRUE;
2698:
2692:
2693: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2694: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2695:
2696: Put_Log_Msg(FND_MESSAGE.Get,1);
2697: l_not_valid := TRUE;
2698:
2699: END IF;
2700: ELSE
2698:
2699: END IF;
2700: ELSE
2701: -- bug 5405935
2702: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2703: Put_Log_Msg(FND_MESSAGE.Get,1);
2704: l_not_valid := TRUE;
2705: END IF;
2706:
2699: END IF;
2700: ELSE
2701: -- bug 5405935
2702: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DUP_ADDRESS'); -- Address already exists
2703: Put_Log_Msg(FND_MESSAGE.Get,1);
2704: l_not_valid := TRUE;
2705: END IF;
2706:
2707: END LOOP;
2750: OR p_data_rec(l_counter).state IS NULL
2751: OR p_data_rec(l_counter).postal_code IS NULL
2752: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2753:
2754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2755: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2756:
2757: Put_Log_Msg(FND_MESSAGE.Get,1);
2758: l_not_valid := TRUE;
2751: OR p_data_rec(l_counter).postal_code IS NULL
2752: OR p_data_rec(l_counter).address_line1 IS NULL THEN
2753:
2754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2755: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2756:
2757: Put_Log_Msg(FND_MESSAGE.Get,1);
2758: l_not_valid := TRUE;
2759:
2753:
2754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_US_ADDR_RQD_FLD_ERR'); -- US address block error
2755: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
2756:
2757: Put_Log_Msg(FND_MESSAGE.Get,1);
2758: l_not_valid := TRUE;
2759:
2760: END IF;
2761:
3014: p_data_rec.prgm_start_date IS NULL OR
3015: p_data_rec.prgm_end_date IS NULL OR
3016: p_data_rec.english_reqd IS NULL) THEN
3017: l_not_valid := TRUE;
3018: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required fields for Foreign Students
3019: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3020: Put_Log_Msg(FND_MESSAGE.Get,1);
3021: END IF;
3022:
3015: p_data_rec.prgm_end_date IS NULL OR
3016: p_data_rec.english_reqd IS NULL) THEN
3017: l_not_valid := TRUE;
3018: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required fields for Foreign Students
3019: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3020: Put_Log_Msg(FND_MESSAGE.Get,1);
3021: END IF;
3022:
3023: --
3016: p_data_rec.english_reqd IS NULL) THEN
3017: l_not_valid := TRUE;
3018: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required fields for Foreign Students
3019: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3020: Put_Log_Msg(FND_MESSAGE.Get,1);
3021: END IF;
3022:
3023: --
3024: -- Make sure that there is a value for the English requirement being met or not
3024: -- Make sure that there is a value for the English requirement being met or not
3025: --
3026: IF ( p_data_rec.english_reqd = 'Y' AND p_data_rec.english_reqd_met IS NULL) THEN
3027: l_not_valid := TRUE;
3028: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_MET_ERR'); -- Required field for Foreign students if english required
3029: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3030: Put_Log_Msg(FND_MESSAGE.Get,1);
3031: END IF;
3032:
3025: --
3026: IF ( p_data_rec.english_reqd = 'Y' AND p_data_rec.english_reqd_met IS NULL) THEN
3027: l_not_valid := TRUE;
3028: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_MET_ERR'); -- Required field for Foreign students if english required
3029: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3030: Put_Log_Msg(FND_MESSAGE.Get,1);
3031: END IF;
3032:
3033: IF ( p_data_rec.education_level = '11' AND p_data_rec.educ_lvl_remarks IS NULL) THEN
3026: IF ( p_data_rec.english_reqd = 'Y' AND p_data_rec.english_reqd_met IS NULL) THEN
3027: l_not_valid := TRUE;
3028: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_MET_ERR'); -- Required field for Foreign students if english required
3029: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3030: Put_Log_Msg(FND_MESSAGE.Get,1);
3031: END IF;
3032:
3033: IF ( p_data_rec.education_level = '11' AND p_data_rec.educ_lvl_remarks IS NULL) THEN
3034: l_not_valid := TRUE;
3031: END IF;
3032:
3033: IF ( p_data_rec.education_level = '11' AND p_data_rec.educ_lvl_remarks IS NULL) THEN
3034: l_not_valid := TRUE;
3035: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required field for Foreign students if english required
3036: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3037: Put_Log_Msg(FND_MESSAGE.Get,1);
3038: END IF;
3039:
3032:
3033: IF ( p_data_rec.education_level = '11' AND p_data_rec.educ_lvl_remarks IS NULL) THEN
3034: l_not_valid := TRUE;
3035: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required field for Foreign students if english required
3036: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3037: Put_Log_Msg(FND_MESSAGE.Get,1);
3038: END IF;
3039:
3040: --
3033: IF ( p_data_rec.education_level = '11' AND p_data_rec.educ_lvl_remarks IS NULL) THEN
3034: l_not_valid := TRUE;
3035: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_REQ_FLD_ERR'); -- Required field for Foreign students if english required
3036: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3037: Put_Log_Msg(FND_MESSAGE.Get,1);
3038: END IF;
3039:
3040: --
3041: -- Make sure that there is a reason for the requirement not being met.
3042: --
3043: IF (p_data_rec.english_reqd = 'N' AND
3044: p_data_rec.not_reqd_reason IS NULL) THEN
3045: l_not_valid := TRUE;
3046: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_NOT_MET_ERR'); -- Required field for Foreign students english not met
3047: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3048: Put_Log_Msg(FND_MESSAGE.Get,1);
3049: END IF;
3050:
3043: IF (p_data_rec.english_reqd = 'N' AND
3044: p_data_rec.not_reqd_reason IS NULL) THEN
3045: l_not_valid := TRUE;
3046: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_NOT_MET_ERR'); -- Required field for Foreign students english not met
3047: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3048: Put_Log_Msg(FND_MESSAGE.Get,1);
3049: END IF;
3050:
3051: ELSE
3044: p_data_rec.not_reqd_reason IS NULL) THEN
3045: l_not_valid := TRUE;
3046: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_NIMG_NOT_MET_ERR'); -- Required field for Foreign students english not met
3047: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3048: Put_Log_Msg(FND_MESSAGE.Get,1);
3049: END IF;
3050:
3051: ELSE
3052:
3056: IF (p_data_rec.position_code IS NULL
3057: OR p_data_rec.subject_field_code IS NULL
3058: OR p_data_rec.remarks IS NULL) THEN
3059: l_not_valid := TRUE;
3060: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_EV_REQ_FLD_ERR'); -- Required field for Exchange Visitor not available.
3061: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3062: Put_Log_Msg(FND_MESSAGE.Get,1);
3063: END IF;
3064:
3057: OR p_data_rec.subject_field_code IS NULL
3058: OR p_data_rec.remarks IS NULL) THEN
3059: l_not_valid := TRUE;
3060: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_EV_REQ_FLD_ERR'); -- Required field for Exchange Visitor not available.
3061: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3062: Put_Log_Msg(FND_MESSAGE.Get,1);
3063: END IF;
3064:
3065: END IF;
3058: OR p_data_rec.remarks IS NULL) THEN
3059: l_not_valid := TRUE;
3060: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EDU_EV_REQ_FLD_ERR'); -- Required field for Exchange Visitor not available.
3061: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
3062: Put_Log_Msg(FND_MESSAGE.Get,1);
3063: END IF;
3064:
3065: END IF;
3066:
3719: p_auth_drp_data_rec(l_auth_rec_count).last_update_date := sysdate;
3720: p_auth_drp_data_rec(l_auth_rec_count).last_update_login := g_update_login;
3721:
3722: IF ADD_MONTHS(l_auth_end_dt,-12) > l_auth_start_dt AND l_visa_type = 'F-1' THEN
3723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ATH_DRP_BLW_DR');
3724: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3725: FND_MESSAGE.SET_TOKEN('MON', '12');
3726: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'F-1');
3727: Put_Log_Msg(FND_MESSAGE.Get,1);
3720: p_auth_drp_data_rec(l_auth_rec_count).last_update_login := g_update_login;
3721:
3722: IF ADD_MONTHS(l_auth_end_dt,-12) > l_auth_start_dt AND l_visa_type = 'F-1' THEN
3723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ATH_DRP_BLW_DR');
3724: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3725: FND_MESSAGE.SET_TOKEN('MON', '12');
3726: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'F-1');
3727: Put_Log_Msg(FND_MESSAGE.Get,1);
3728: l_not_valid := TRUE;
3721:
3722: IF ADD_MONTHS(l_auth_end_dt,-12) > l_auth_start_dt AND l_visa_type = 'F-1' THEN
3723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ATH_DRP_BLW_DR');
3724: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3725: FND_MESSAGE.SET_TOKEN('MON', '12');
3726: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'F-1');
3727: Put_Log_Msg(FND_MESSAGE.Get,1);
3728: l_not_valid := TRUE;
3729: /* Debug */
3722: IF ADD_MONTHS(l_auth_end_dt,-12) > l_auth_start_dt AND l_visa_type = 'F-1' THEN
3723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ATH_DRP_BLW_DR');
3724: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3725: FND_MESSAGE.SET_TOKEN('MON', '12');
3726: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'F-1');
3727: Put_Log_Msg(FND_MESSAGE.Get,1);
3728: l_not_valid := TRUE;
3729: /* Debug */
3730: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3723: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_ATH_DRP_BLW_DR');
3724: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3725: FND_MESSAGE.SET_TOKEN('MON', '12');
3726: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'F-1');
3727: Put_Log_Msg(FND_MESSAGE.Get,1);
3728: l_not_valid := TRUE;
3729: /* Debug */
3730: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3731: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Validate_Prgm_Info';
3732: l_debug_str := 'Validation failure for Authorization. Error: Invalid authorization drop below for F-1 visa';
3733: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
3734: END IF;
3735: ELSIF ADD_MONTHS(l_auth_end_dt,-5) > l_auth_start_dt AND l_visa_type = 'M-1' THEN
3736: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_DRP_BLW');
3737: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3738: FND_MESSAGE.SET_TOKEN('MON', '5');
3739: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'M-1');
3740: Put_Log_Msg(FND_MESSAGE.Get,1);
3733: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
3734: END IF;
3735: ELSIF ADD_MONTHS(l_auth_end_dt,-5) > l_auth_start_dt AND l_visa_type = 'M-1' THEN
3736: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_DRP_BLW');
3737: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3738: FND_MESSAGE.SET_TOKEN('MON', '5');
3739: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'M-1');
3740: Put_Log_Msg(FND_MESSAGE.Get,1);
3741: l_not_valid := TRUE;
3734: END IF;
3735: ELSIF ADD_MONTHS(l_auth_end_dt,-5) > l_auth_start_dt AND l_visa_type = 'M-1' THEN
3736: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_DRP_BLW');
3737: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3738: FND_MESSAGE.SET_TOKEN('MON', '5');
3739: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'M-1');
3740: Put_Log_Msg(FND_MESSAGE.Get,1);
3741: l_not_valid := TRUE;
3742: /* Debug */
3735: ELSIF ADD_MONTHS(l_auth_end_dt,-5) > l_auth_start_dt AND l_visa_type = 'M-1' THEN
3736: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_DRP_BLW');
3737: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3738: FND_MESSAGE.SET_TOKEN('MON', '5');
3739: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'M-1');
3740: Put_Log_Msg(FND_MESSAGE.Get,1);
3741: l_not_valid := TRUE;
3742: /* Debug */
3743: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3736: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_DRP_BLW');
3737: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3738: FND_MESSAGE.SET_TOKEN('MON', '5');
3739: FND_MESSAGE.SET_TOKEN('VISA_TYPE', 'M-1');
3740: Put_Log_Msg(FND_MESSAGE.Get,1);
3741: l_not_valid := TRUE;
3742: /* Debug */
3743: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3744: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Validate_Prgm_Info';
3750:
3751: IF p_auth_drp_data_rec(l_auth_rec_count).auth_action_code = 'A' THEN
3752: Put_Log_Msg('validating Authorization in add mode',0);
3753: IF ((l_auth_start_dt <= sysdate) OR (l_auth_start_dt <= l_prgm_start_dt)) THEN
3754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_ST_DT'); -- Invalid authorization start date
3755: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3756: Put_Log_Msg(FND_MESSAGE.Get,1);
3757: l_not_valid := TRUE;
3758: /* Debug */
3751: IF p_auth_drp_data_rec(l_auth_rec_count).auth_action_code = 'A' THEN
3752: Put_Log_Msg('validating Authorization in add mode',0);
3753: IF ((l_auth_start_dt <= sysdate) OR (l_auth_start_dt <= l_prgm_start_dt)) THEN
3754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_ST_DT'); -- Invalid authorization start date
3755: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3756: Put_Log_Msg(FND_MESSAGE.Get,1);
3757: l_not_valid := TRUE;
3758: /* Debug */
3759: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3752: Put_Log_Msg('validating Authorization in add mode',0);
3753: IF ((l_auth_start_dt <= sysdate) OR (l_auth_start_dt <= l_prgm_start_dt)) THEN
3754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_ST_DT'); -- Invalid authorization start date
3755: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3756: Put_Log_Msg(FND_MESSAGE.Get,1);
3757: l_not_valid := TRUE;
3758: /* Debug */
3759: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3760: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Validate_Prgm_Info';
3762: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
3763: END IF;
3764: END IF;
3765: IF ((l_auth_end_dt <= sysdate) OR (l_auth_end_dt >= l_prgm_end_dt)) THEN
3766: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_END_DT'); -- Invalid authorization end date
3767: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3768: Put_Log_Msg(FND_MESSAGE.Get,1);
3769: l_not_valid := TRUE;
3770: /* Debug */
3763: END IF;
3764: END IF;
3765: IF ((l_auth_end_dt <= sysdate) OR (l_auth_end_dt >= l_prgm_end_dt)) THEN
3766: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_END_DT'); -- Invalid authorization end date
3767: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3768: Put_Log_Msg(FND_MESSAGE.Get,1);
3769: l_not_valid := TRUE;
3770: /* Debug */
3771: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3764: END IF;
3765: IF ((l_auth_end_dt <= sysdate) OR (l_auth_end_dt >= l_prgm_end_dt)) THEN
3766: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_AUTH_END_DT'); -- Invalid authorization end date
3767: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
3768: Put_Log_Msg(FND_MESSAGE.Get,1);
3769: l_not_valid := TRUE;
3770: /* Debug */
3771: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
3772: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Validate_Prgm_Info';
3887: ELSIF p_data_rec(l_counter).prgm_action_type = 'D' AND
3888: ( p_data_rec(l_counter).prgm_start_date IS NULL
3889: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
3890:
3891: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CMP_PRG_RQD_FLD_ERR'); -- Completion block error
3892: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3893:
3894: Put_Log_Msg(FND_MESSAGE.Get,1);
3895: l_not_valid := TRUE;
3888: ( p_data_rec(l_counter).prgm_start_date IS NULL
3889: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
3890:
3891: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CMP_PRG_RQD_FLD_ERR'); -- Completion block error
3892: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3893:
3894: Put_Log_Msg(FND_MESSAGE.Get,1);
3895: l_not_valid := TRUE;
3896:
3890:
3891: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CMP_PRG_RQD_FLD_ERR'); -- Completion block error
3892: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3893:
3894: Put_Log_Msg(FND_MESSAGE.Get,1);
3895: l_not_valid := TRUE;
3896:
3897: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND p_data_rec(l_counter).termination_reason IS NULL THEN
3898:
3895: l_not_valid := TRUE;
3896:
3897: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND p_data_rec(l_counter).termination_reason IS NULL THEN
3898:
3899: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
3900: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3901:
3902: Put_Log_Msg(FND_MESSAGE.Get,1);
3903: l_not_valid := TRUE;
3896:
3897: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND p_data_rec(l_counter).termination_reason IS NULL THEN
3898:
3899: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
3900: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3901:
3902: Put_Log_Msg(FND_MESSAGE.Get,1);
3903: l_not_valid := TRUE;
3904: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND l_visa_type= 'M-1' THEN
3898:
3899: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
3900: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3901:
3902: Put_Log_Msg(FND_MESSAGE.Get,1);
3903: l_not_valid := TRUE;
3904: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND l_visa_type= 'M-1' THEN
3905: OPEN c_termination_reason('M', p_data_rec(l_counter).termination_reason);
3906: FETCH c_termination_reason INTO l_termination_count;
3905: OPEN c_termination_reason('M', p_data_rec(l_counter).termination_reason);
3906: FETCH c_termination_reason INTO l_termination_count;
3907: CLOSE c_termination_reason;
3908: IF l_termination_count > 0 THEN
3909: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3910: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3911: Put_Log_Msg(FND_MESSAGE.Get,1);
3912: l_not_valid := TRUE;
3913: END IF;
3906: FETCH c_termination_reason INTO l_termination_count;
3907: CLOSE c_termination_reason;
3908: IF l_termination_count > 0 THEN
3909: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3910: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3911: Put_Log_Msg(FND_MESSAGE.Get,1);
3912: l_not_valid := TRUE;
3913: END IF;
3914: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND l_visa_type= 'F-1' THEN
3907: CLOSE c_termination_reason;
3908: IF l_termination_count > 0 THEN
3909: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3910: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3911: Put_Log_Msg(FND_MESSAGE.Get,1);
3912: l_not_valid := TRUE;
3913: END IF;
3914: ELSIF p_data_rec(l_counter).prgm_action_type = 'T' AND l_visa_type= 'F-1' THEN
3915: OPEN c_termination_reason('F', p_data_rec(l_counter).termination_reason);
3915: OPEN c_termination_reason('F', p_data_rec(l_counter).termination_reason);
3916: FETCH c_termination_reason INTO l_termination_count;
3917: CLOSE c_termination_reason;
3918: IF l_termination_count > 0 THEN
3919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3920: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3921: Put_Log_Msg(FND_MESSAGE.Get,1);
3922: l_not_valid := TRUE;
3923: END IF;
3916: FETCH c_termination_reason INTO l_termination_count;
3917: CLOSE c_termination_reason;
3918: IF l_termination_count > 0 THEN
3919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3920: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3921: Put_Log_Msg(FND_MESSAGE.Get,1);
3922: l_not_valid := TRUE;
3923: END IF;
3924:
3917: CLOSE c_termination_reason;
3918: IF l_termination_count > 0 THEN
3919: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_TERM_REASON');
3920: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3921: Put_Log_Msg(FND_MESSAGE.Get,1);
3922: l_not_valid := TRUE;
3923: END IF;
3924:
3925: ELSIF p_data_rec(l_counter).prgm_action_type = 'E' AND
3925: ELSIF p_data_rec(l_counter).prgm_action_type = 'E' AND
3926: ( p_data_rec(l_counter).remarks IS NULL
3927: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
3928:
3929: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EXT_PRG_RQD_FLD_ERR'); -- Extention block error
3930: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3931:
3932: Put_Log_Msg(FND_MESSAGE.Get,1);
3933: l_not_valid := TRUE;
3926: ( p_data_rec(l_counter).remarks IS NULL
3927: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
3928:
3929: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EXT_PRG_RQD_FLD_ERR'); -- Extention block error
3930: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3931:
3932: Put_Log_Msg(FND_MESSAGE.Get,1);
3933: l_not_valid := TRUE;
3934:
3928:
3929: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EXT_PRG_RQD_FLD_ERR'); -- Extention block error
3930: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
3931:
3932: Put_Log_Msg(FND_MESSAGE.Get,1);
3933: l_not_valid := TRUE;
3934:
3935: END IF;
3936:
3939: OPEN c_prev_end_date;
3940: FETCH c_prev_end_date INTO lv_prgm_end_dt;
3941: CLOSE c_prev_end_date;
3942: IF to_date(p_data_rec(l_counter).prgm_end_date,'YYYY-MM-DD') > ADD_MONTHS(lv_prgm_end_dt,12) THEN
3943: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_EXT_PRG_DRN'); -- Extention block error
3944: Put_Log_Msg(FND_MESSAGE.Get,1);
3945: l_not_valid := TRUE;
3946: END IF;
3947:
3940: FETCH c_prev_end_date INTO lv_prgm_end_dt;
3941: CLOSE c_prev_end_date;
3942: IF to_date(p_data_rec(l_counter).prgm_end_date,'YYYY-MM-DD') > ADD_MONTHS(lv_prgm_end_dt,12) THEN
3943: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_EXT_PRG_DRN'); -- Extention block error
3944: Put_Log_Msg(FND_MESSAGE.Get,1);
3945: l_not_valid := TRUE;
3946: END IF;
3947:
3948: END IF;
4011: l_prgm_end_dt;
4012: CLOSE c_ev_category_data;
4013:
4014: IF l_category_code = '2A' AND l_prgm_end_dt > ADD_MONTHS(l_prgm_start_dt,24) THEN
4015: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_PROG_DURN');
4016: FND_MESSAGE.SET_TOKEN('PROGDURATION','not exceed 24 months');
4017: Put_Log_Msg(FND_MESSAGE.Get,1);
4018: l_not_valid := TRUE;
4019: END IF;
4012: CLOSE c_ev_category_data;
4013:
4014: IF l_category_code = '2A' AND l_prgm_end_dt > ADD_MONTHS(l_prgm_start_dt,24) THEN
4015: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_PROG_DURN');
4016: FND_MESSAGE.SET_TOKEN('PROGDURATION','not exceed 24 months');
4017: Put_Log_Msg(FND_MESSAGE.Get,1);
4018: l_not_valid := TRUE;
4019: END IF;
4020:
4013:
4014: IF l_category_code = '2A' AND l_prgm_end_dt > ADD_MONTHS(l_prgm_start_dt,24) THEN
4015: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_PROG_DURN');
4016: FND_MESSAGE.SET_TOKEN('PROGDURATION','not exceed 24 months');
4017: Put_Log_Msg(FND_MESSAGE.Get,1);
4018: l_not_valid := TRUE;
4019: END IF;
4020:
4021: IF p_data_rec(l_counter).prgm_action_type = 'EM' AND p_data_rec(l_counter).prgm_end_date < trunc(SYSDATE) THEN
4018: l_not_valid := TRUE;
4019: END IF;
4020:
4021: IF p_data_rec(l_counter).prgm_action_type = 'EM' AND p_data_rec(l_counter).prgm_end_date < trunc(SYSDATE) THEN
4022: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRGM_END_DT');
4023: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
4024: Put_Log_Msg(FND_MESSAGE.Get,1);
4025: l_not_valid := TRUE;
4026: END IF;
4019: END IF;
4020:
4021: IF p_data_rec(l_counter).prgm_action_type = 'EM' AND p_data_rec(l_counter).prgm_end_date < trunc(SYSDATE) THEN
4022: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRGM_END_DT');
4023: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
4024: Put_Log_Msg(FND_MESSAGE.Get,1);
4025: l_not_valid := TRUE;
4026: END IF;
4027:
4020:
4021: IF p_data_rec(l_counter).prgm_action_type = 'EM' AND p_data_rec(l_counter).prgm_end_date < trunc(SYSDATE) THEN
4022: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INV_PRGM_END_DT');
4023: FND_MESSAGE.SET_TOKEN('PERSON_NUM', p_person_rec.person_number );
4024: Put_Log_Msg(FND_MESSAGE.Get,1);
4025: l_not_valid := TRUE;
4026: END IF;
4027:
4028: IF p_data_rec(l_counter).prgm_action_type = 'AP' AND -- AP Amend Program
4028: IF p_data_rec(l_counter).prgm_action_type = 'AP' AND -- AP Amend Program
4029: ( p_data_rec(l_counter).prgm_start_date IS NULL
4030: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
4031:
4032: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4033: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4034:
4035: Put_Log_Msg(FND_MESSAGE.Get,1);
4036: l_not_valid := TRUE;
4029: ( p_data_rec(l_counter).prgm_start_date IS NULL
4030: OR p_data_rec(l_counter).prgm_end_date IS NULL) THEN
4031:
4032: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4033: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4034:
4035: Put_Log_Msg(FND_MESSAGE.Get,1);
4036: l_not_valid := TRUE;
4037:
4031:
4032: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4033: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4034:
4035: Put_Log_Msg(FND_MESSAGE.Get,1);
4036: l_not_valid := TRUE;
4037:
4038: ELSIF p_data_rec(l_counter).prgm_action_type IN ('EE','EF') AND -- EE Exchange Visitor Extension, EF Extend Failure
4039: ( p_data_rec(l_counter).prgm_end_date IS NULL) THEN
4037:
4038: ELSIF p_data_rec(l_counter).prgm_action_type IN ('EE','EF') AND -- EE Exchange Visitor Extension, EF Extend Failure
4039: ( p_data_rec(l_counter).prgm_end_date IS NULL) THEN
4040:
4041: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4042: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4043:
4044: Put_Log_Msg(FND_MESSAGE.Get,1);
4045: l_not_valid := TRUE;
4038: ELSIF p_data_rec(l_counter).prgm_action_type IN ('EE','EF') AND -- EE Exchange Visitor Extension, EF Extend Failure
4039: ( p_data_rec(l_counter).prgm_end_date IS NULL) THEN
4040:
4041: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4042: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4043:
4044: Put_Log_Msg(FND_MESSAGE.Get,1);
4045: l_not_valid := TRUE;
4046:
4040:
4041: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR');
4042: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4043:
4044: Put_Log_Msg(FND_MESSAGE.Get,1);
4045: l_not_valid := TRUE;
4046:
4047: ELSIF p_data_rec(l_counter).prgm_action_type IN ('SP') AND
4048: p_data_rec(l_counter).remarks IS NULL THEN
4048: p_data_rec(l_counter).remarks IS NULL THEN
4049: -- AF Approve Failure
4050: -- OI Other Infraction
4051:
4052: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- Amend program block error
4053: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4054:
4055: Put_Log_Msg(FND_MESSAGE.Get,1);
4056: l_not_valid := TRUE;
4049: -- AF Approve Failure
4050: -- OI Other Infraction
4051:
4052: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- Amend program block error
4053: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4054:
4055: Put_Log_Msg(FND_MESSAGE.Get,1);
4056: l_not_valid := TRUE;
4057:
4051:
4052: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EV_PRG_RQD_FLD_ERR'); -- Amend program block error
4053: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4054:
4055: Put_Log_Msg(FND_MESSAGE.Get,1);
4056: l_not_valid := TRUE;
4057:
4058:
4059: ELSIF p_data_rec(l_counter).prgm_action_type = 'TR' AND -- TR Terminate Exchange Visitor
4059: ELSIF p_data_rec(l_counter).prgm_action_type = 'TR' AND -- TR Terminate Exchange Visitor
4060: ( p_data_rec(l_counter).termination_reason IS NULL
4061: OR p_data_rec(l_counter).effective_date IS NULL ) THEN
4062:
4063: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4064: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4065:
4066: Put_Log_Msg(FND_MESSAGE.Get,1);
4067: l_not_valid := TRUE;
4060: ( p_data_rec(l_counter).termination_reason IS NULL
4061: OR p_data_rec(l_counter).effective_date IS NULL ) THEN
4062:
4063: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4064: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4065:
4066: Put_Log_Msg(FND_MESSAGE.Get,1);
4067: l_not_valid := TRUE;
4068:
4062:
4063: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4064: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4065:
4066: Put_Log_Msg(FND_MESSAGE.Get,1);
4067: l_not_valid := TRUE;
4068:
4069: ELSIF p_data_rec(l_counter).prgm_action_type = 'ED' AND -- ED End Program
4070: ( p_data_rec(l_counter).end_prgm_reason IS NULL
4069: ELSIF p_data_rec(l_counter).prgm_action_type = 'ED' AND -- ED End Program
4070: ( p_data_rec(l_counter).end_prgm_reason IS NULL
4071: OR p_data_rec(l_counter).effective_date IS NULL ) THEN
4072:
4073: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4074: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4075:
4076: Put_Log_Msg(FND_MESSAGE.Get,1);
4077: l_not_valid := TRUE;
4070: ( p_data_rec(l_counter).end_prgm_reason IS NULL
4071: OR p_data_rec(l_counter).effective_date IS NULL ) THEN
4072:
4073: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4074: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4075:
4076: Put_Log_Msg(FND_MESSAGE.Get,1);
4077: l_not_valid := TRUE;
4078:
4072:
4073: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_PRG_RQD_FLD_ERR'); -- Termination block error
4074: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4075:
4076: Put_Log_Msg(FND_MESSAGE.Get,1);
4077: l_not_valid := TRUE;
4078:
4079:
4080: END IF;
4080: END IF;
4081:
4082: IF p_data_rec(l_counter).prgm_action_type = 'TR' AND p_data_rec(l_counter).termination_reason = 'OTHER'
4083: AND p_data_rec(l_counter).remarks IS NULL THEN
4084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_REM_RQD');
4085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4086: Put_Log_Msg(FND_MESSAGE.Get,1);
4087: l_not_valid := TRUE;
4088: END IF;
4081:
4082: IF p_data_rec(l_counter).prgm_action_type = 'TR' AND p_data_rec(l_counter).termination_reason = 'OTHER'
4083: AND p_data_rec(l_counter).remarks IS NULL THEN
4084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_REM_RQD');
4085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4086: Put_Log_Msg(FND_MESSAGE.Get,1);
4087: l_not_valid := TRUE;
4088: END IF;
4089:
4082: IF p_data_rec(l_counter).prgm_action_type = 'TR' AND p_data_rec(l_counter).termination_reason = 'OTHER'
4083: AND p_data_rec(l_counter).remarks IS NULL THEN
4084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_TRM_REM_RQD');
4085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4086: Put_Log_Msg(FND_MESSAGE.Get,1);
4087: l_not_valid := TRUE;
4088: END IF;
4089:
4090: IF l_not_valid THEN
4397:
4398: -- Validate all the data required for either type of request.
4399: IF (p_data_rec.print_form IS NULL AND p_person_rec.batch_type = 'E') THEN
4400: l_not_valid := TRUE;
4401: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
4402: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4403: Put_Log_Msg(FND_MESSAGE.Get,1);
4404: END IF;
4405:
4398: -- Validate all the data required for either type of request.
4399: IF (p_data_rec.print_form IS NULL AND p_person_rec.batch_type = 'E') THEN
4400: l_not_valid := TRUE;
4401: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
4402: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4403: Put_Log_Msg(FND_MESSAGE.Get,1);
4404: END IF;
4405:
4406: --
4399: IF (p_data_rec.print_form IS NULL AND p_person_rec.batch_type = 'E') THEN
4400: l_not_valid := TRUE;
4401: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_FLD_ERR'); -- Required Fields for Exchange Visitor students
4402: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4403: Put_Log_Msg(FND_MESSAGE.Get,1);
4404: END IF;
4405:
4406: --
4407: -- Validate data specific for Foreign Students
4412: p_data_rec.tuition IS NULL OR
4413: p_data_rec.living_exp IS NULL OR
4414: p_data_rec.personal_funds IS NULL) THEN
4415: l_not_valid := TRUE;
4416: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_ERR'); -- Required Fields for Exchange Visitor students
4417: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4418: Put_Log_Msg(FND_MESSAGE.Get,1);
4419: END IF;
4420:
4413: p_data_rec.living_exp IS NULL OR
4414: p_data_rec.personal_funds IS NULL) THEN
4415: l_not_valid := TRUE;
4416: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_ERR'); -- Required Fields for Exchange Visitor students
4417: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4418: Put_Log_Msg(FND_MESSAGE.Get,1);
4419: END IF;
4420:
4421: IF ((p_data_rec.other_exp IS NOT NULL AND
4414: p_data_rec.personal_funds IS NULL) THEN
4415: l_not_valid := TRUE;
4416: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_ERR'); -- Required Fields for Exchange Visitor students
4417: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4418: Put_Log_Msg(FND_MESSAGE.Get,1);
4419: END IF;
4420:
4421: IF ((p_data_rec.other_exp IS NOT NULL AND
4422: p_data_rec.other_exp_desc IS NULL) OR
4424: p_data_rec.school_funds_desc IS NULL) OR
4425: (p_data_rec.other_funds IS NOT NULL AND
4426: p_data_rec.other_funds_desc IS NULL)) THEN
4427: l_not_valid := TRUE;
4428: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_DESC_ERR'); -- Required Fields for Exchange Visitor students
4429: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4430: Put_Log_Msg(FND_MESSAGE.Get,1);
4431: END IF;
4432:
4425: (p_data_rec.other_funds IS NOT NULL AND
4426: p_data_rec.other_funds_desc IS NULL)) THEN
4427: l_not_valid := TRUE;
4428: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_DESC_ERR'); -- Required Fields for Exchange Visitor students
4429: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4430: Put_Log_Msg(FND_MESSAGE.Get,1);
4431: END IF;
4432:
4433: ELSE
4426: p_data_rec.other_funds_desc IS NULL)) THEN
4427: l_not_valid := TRUE;
4428: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_NIMG_DESC_ERR'); -- Required Fields for Exchange Visitor students
4429: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4430: Put_Log_Msg(FND_MESSAGE.Get,1);
4431: END IF;
4432:
4433: ELSE
4434:
4443: p_data_rec.intl_org1_code IS NULL) OR
4444: (p_data_rec.intl_org2 IS NOT NULL AND
4445: p_data_rec.intl_org2_code IS NULL)) THEN
4446: l_not_valid := TRUE;
4447: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_EV_ERR'); -- Required Fields for Exchange Visitor students
4448: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4449: Put_Log_Msg(FND_MESSAGE.Get,1);
4450: END IF;
4451:
4444: (p_data_rec.intl_org2 IS NOT NULL AND
4445: p_data_rec.intl_org2_code IS NULL)) THEN
4446: l_not_valid := TRUE;
4447: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_EV_ERR'); -- Required Fields for Exchange Visitor students
4448: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4449: Put_Log_Msg(FND_MESSAGE.Get,1);
4450: END IF;
4451:
4452:
4445: p_data_rec.intl_org2_code IS NULL)) THEN
4446: l_not_valid := TRUE;
4447: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_FIN_REQ_EV_ERR'); -- Required Fields for Exchange Visitor students
4448: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4449: Put_Log_Msg(FND_MESSAGE.Get,1);
4450: END IF;
4451:
4452:
4453: IF ((p_data_rec.govt_org1_code = 'OTHER' AND
4460: p_data_rec.intl_org2_othr_name IS NULL) OR
4461: p_data_rec.other_govt_name IS NULL AND
4462: p_data_rec.other_org IS NOT NULL) THEN
4463: l_not_valid := TRUE;
4464: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_OTHR_ORG');
4465: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4466: Put_Log_Msg(FND_MESSAGE.Get,1);
4467: END IF;
4468:
4461: p_data_rec.other_govt_name IS NULL AND
4462: p_data_rec.other_org IS NOT NULL) THEN
4463: l_not_valid := TRUE;
4464: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_OTHR_ORG');
4465: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4466: Put_Log_Msg(FND_MESSAGE.Get,1);
4467: END IF;
4468:
4469: END IF;
4462: p_data_rec.other_org IS NOT NULL) THEN
4463: l_not_valid := TRUE;
4464: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_OTHR_ORG');
4465: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
4466: Put_Log_Msg(FND_MESSAGE.Get,1);
4467: END IF;
4468:
4469: END IF;
4470:
4672: OPEN c_spouse_of_rel;
4673: FETCH c_spouse_of_rel INTO l_rel_type_count;
4674: CLOSE c_spouse_of_rel;
4675: IF l_rel_type_count > 1 THEN
4676: FND_MESSAGE.SET_NAME('IGS','IGS_SV_SPOUSE_REL');
4677: FND_MESSAGE.SET_TOKEN('PERSON_NUM',p_person_rec.person_number);
4678: Put_Log_Msg(FND_MESSAGE.Get,1);
4679: RETURN 'E'; -- fix for bug 5255450
4680: END IF;
4673: FETCH c_spouse_of_rel INTO l_rel_type_count;
4674: CLOSE c_spouse_of_rel;
4675: IF l_rel_type_count > 1 THEN
4676: FND_MESSAGE.SET_NAME('IGS','IGS_SV_SPOUSE_REL');
4677: FND_MESSAGE.SET_TOKEN('PERSON_NUM',p_person_rec.person_number);
4678: Put_Log_Msg(FND_MESSAGE.Get,1);
4679: RETURN 'E'; -- fix for bug 5255450
4680: END IF;
4681:
4674: CLOSE c_spouse_of_rel;
4675: IF l_rel_type_count > 1 THEN
4676: FND_MESSAGE.SET_NAME('IGS','IGS_SV_SPOUSE_REL');
4677: FND_MESSAGE.SET_TOKEN('PERSON_NUM',p_person_rec.person_number);
4678: Put_Log_Msg(FND_MESSAGE.Get,1);
4679: RETURN 'E'; -- fix for bug 5255450
4680: END IF;
4681:
4682: END IF;
4707: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
4708: END IF;
4709:
4710: -- probably a bug
4711: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
4712: FND_MESSAGE.SET_TOKEN('BLOCK_ID',1);
4713: FND_MSG_PUB.Add;
4714: RAISE FND_API.G_EXC_ERROR;
4715:
4708: END IF;
4709:
4710: -- probably a bug
4711: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
4712: FND_MESSAGE.SET_TOKEN('BLOCK_ID',1);
4713: FND_MSG_PUB.Add;
4714: RAISE FND_API.G_EXC_ERROR;
4715:
4716: END IF;
4892: RETURN 'N';
4893:
4894: ELSIF l_not_valid THEN
4895:
4896: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CONV_RQD_FLD_ERR'); -- Convictions block error
4897: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4898:
4899: Put_Log_Msg(FND_MESSAGE.Get,1);
4900:
4893:
4894: ELSIF l_not_valid THEN
4895:
4896: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CONV_RQD_FLD_ERR'); -- Convictions block error
4897: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4898:
4899: Put_Log_Msg(FND_MESSAGE.Get,1);
4900:
4901: Put_Log_Msg(l_api_name||' Validation error, return E ',0);
4895:
4896: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_CONV_RQD_FLD_ERR'); -- Convictions block error
4897: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
4898:
4899: Put_Log_Msg(FND_MESSAGE.Get,1);
4900:
4901: Put_Log_Msg(l_api_name||' Validation error, return E ',0);
4902: /* Debug */
4903: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
5432: AND p_data_rec.rescind_empl IS NULL
5433: )
5434: OR p_data_rec.remarks IS NULL) THEN
5435:
5436: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_F_RQD_FLD_ERR'); -- Employment block error
5437: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5438:
5439: Put_Log_Msg(FND_MESSAGE.Get,1);
5440: l_not_valid := TRUE;
5433: )
5434: OR p_data_rec.remarks IS NULL) THEN
5435:
5436: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_F_RQD_FLD_ERR'); -- Employment block error
5437: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5438:
5439: Put_Log_Msg(FND_MESSAGE.Get,1);
5440: l_not_valid := TRUE;
5441:
5435:
5436: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_F_RQD_FLD_ERR'); -- Employment block error
5437: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5438:
5439: Put_Log_Msg(FND_MESSAGE.Get,1);
5440: l_not_valid := TRUE;
5441:
5442: ELSIF p_data_rec.empl_rec_type = 'O'
5443: AND ( p_data_rec.empl_time IS NULL
5444: OR p_data_rec.empl_start_date IS NULL
5445: OR p_data_rec.empl_end_date IS NULL
5446: OR p_data_rec.course_relevance IS NULL) THEN
5447:
5448: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_O_RQD_FLD_ERR'); -- Employment block error
5449: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5450:
5451: Put_Log_Msg(FND_MESSAGE.Get,1);
5452: l_not_valid := TRUE;
5445: OR p_data_rec.empl_end_date IS NULL
5446: OR p_data_rec.course_relevance IS NULL) THEN
5447:
5448: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_O_RQD_FLD_ERR'); -- Employment block error
5449: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5450:
5451: Put_Log_Msg(FND_MESSAGE.Get,1);
5452: l_not_valid := TRUE;
5453:
5447:
5448: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_O_RQD_FLD_ERR'); -- Employment block error
5449: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5450:
5451: Put_Log_Msg(FND_MESSAGE.Get,1);
5452: l_not_valid := TRUE;
5453:
5454: ELSIF p_data_rec.empl_rec_type = 'C'
5455: AND ( p_data_rec.empl_time IS NULL
5462: OR p_data_rec.state IS NULL
5463: OR p_data_rec.postal_code IS NULL
5464: ) THEN
5465:
5466: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_C_RQD_FLD_ERR'); -- Employment block error
5467: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5468:
5469: Put_Log_Msg(FND_MESSAGE.Get,1);
5470: l_not_valid := TRUE;
5463: OR p_data_rec.postal_code IS NULL
5464: ) THEN
5465:
5466: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_C_RQD_FLD_ERR'); -- Employment block error
5467: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5468:
5469: Put_Log_Msg(FND_MESSAGE.Get,1);
5470: l_not_valid := TRUE;
5471: ELSIF p_data_rec.empl_rec_type = 'O' THEN
5465:
5466: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_EMPL_C_RQD_FLD_ERR'); -- Employment block error
5467: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number );
5468:
5469: Put_Log_Msg(FND_MESSAGE.Get,1);
5470: l_not_valid := TRUE;
5471: ELSIF p_data_rec.empl_rec_type = 'O' THEN
5472: IF Check_emp_duration(p_data_rec.empl_start_date, p_data_rec.empl_end_date, l_visa_type) THEN
5473: FND_MESSAGE.SET_NAME('IGS','IGS_SV_EMP_INV_PRD');
5469: Put_Log_Msg(FND_MESSAGE.Get,1);
5470: l_not_valid := TRUE;
5471: ELSIF p_data_rec.empl_rec_type = 'O' THEN
5472: IF Check_emp_duration(p_data_rec.empl_start_date, p_data_rec.empl_end_date, l_visa_type) THEN
5473: FND_MESSAGE.SET_NAME('IGS','IGS_SV_EMP_INV_PRD');
5474: FND_MESSAGE.SET_TOKEN('MONTHS',l_months);
5475: FND_MESSAGE.SET_TOKEN('VISA_TYPE',l_visa_type);
5476: Put_Log_Msg(FND_MESSAGE.Get,1);
5477: l_not_valid := TRUE;
5470: l_not_valid := TRUE;
5471: ELSIF p_data_rec.empl_rec_type = 'O' THEN
5472: IF Check_emp_duration(p_data_rec.empl_start_date, p_data_rec.empl_end_date, l_visa_type) THEN
5473: FND_MESSAGE.SET_NAME('IGS','IGS_SV_EMP_INV_PRD');
5474: FND_MESSAGE.SET_TOKEN('MONTHS',l_months);
5475: FND_MESSAGE.SET_TOKEN('VISA_TYPE',l_visa_type);
5476: Put_Log_Msg(FND_MESSAGE.Get,1);
5477: l_not_valid := TRUE;
5478: END IF;
5471: ELSIF p_data_rec.empl_rec_type = 'O' THEN
5472: IF Check_emp_duration(p_data_rec.empl_start_date, p_data_rec.empl_end_date, l_visa_type) THEN
5473: FND_MESSAGE.SET_NAME('IGS','IGS_SV_EMP_INV_PRD');
5474: FND_MESSAGE.SET_TOKEN('MONTHS',l_months);
5475: FND_MESSAGE.SET_TOKEN('VISA_TYPE',l_visa_type);
5476: Put_Log_Msg(FND_MESSAGE.Get,1);
5477: l_not_valid := TRUE;
5478: END IF;
5479: END IF;
5472: IF Check_emp_duration(p_data_rec.empl_start_date, p_data_rec.empl_end_date, l_visa_type) THEN
5473: FND_MESSAGE.SET_NAME('IGS','IGS_SV_EMP_INV_PRD');
5474: FND_MESSAGE.SET_TOKEN('MONTHS',l_months);
5475: FND_MESSAGE.SET_TOKEN('VISA_TYPE',l_visa_type);
5476: Put_Log_Msg(FND_MESSAGE.Get,1);
5477: l_not_valid := TRUE;
5478: END IF;
5479: END IF;
5480:
8420:
8421: --Error dates must be not null
8422: Put_Log_Msg('Dates are null return S',0);
8423:
8424: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --Dates not found
8425: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8426: Put_Log_Msg(FND_MESSAGE.Get,1);
8427:
8428: p_person_rec.person_status := 'I';
8421: --Error dates must be not null
8422: Put_Log_Msg('Dates are null return S',0);
8423:
8424: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --Dates not found
8425: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8426: Put_Log_Msg(FND_MESSAGE.Get,1);
8427:
8428: p_person_rec.person_status := 'I';
8429: /* Debug
8422: Put_Log_Msg('Dates are null return S',0);
8423:
8424: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --Dates not found
8425: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8426: Put_Log_Msg(FND_MESSAGE.Get,1);
8427:
8428: p_person_rec.person_status := 'I';
8429: /* Debug
8430: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
8481: ELSIF p_person_rec.f_addr_status = 'N' THEN -- Not found
8482:
8483: Put_Log_Msg('F Address block is not found return S',0);
8484:
8485: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --f address is not found
8486: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8487: Put_Log_Msg(FND_MESSAGE.Get,1);
8488:
8489: p_person_rec.person_status := 'I';
8482:
8483: Put_Log_Msg('F Address block is not found return S',0);
8484:
8485: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --f address is not found
8486: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8487: Put_Log_Msg(FND_MESSAGE.Get,1);
8488:
8489: p_person_rec.person_status := 'I';
8490: /* Debug */
8483: Put_Log_Msg('F Address block is not found return S',0);
8484:
8485: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_REG_REQD_FLD_ERR'); --f address is not found
8486: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', p_person_rec.person_number);
8487: Put_Log_Msg(FND_MESSAGE.Get,1);
8488:
8489: p_person_rec.person_status := 'I';
8490: /* Debug */
8491: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
8818: ELSIF p_person_rec.us_addr_status = 'N' THEN -- Not found
8819:
8820: Put_Log_Msg('US Address block is not found return S',0);
8821:
8822: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_US_ADDR_FLD_ERR'); --US address is not found
8823: Put_Log_Msg(FND_MESSAGE.Get,1);
8824:
8825: p_person_rec.person_status := 'I';
8826: /* Debug */
8819:
8820: Put_Log_Msg('US Address block is not found return S',0);
8821:
8822: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_US_ADDR_FLD_ERR'); --US address is not found
8823: Put_Log_Msg(FND_MESSAGE.Get,1);
8824:
8825: p_person_rec.person_status := 'I';
8826: /* Debug */
8827: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
9250: END IF;
9251:
9252:
9253:
9254: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
9255: FND_MESSAGE.SET_TOKEN('BLOCK_ID',2);
9256: FND_MSG_PUB.Add;
9257:
9258: RAISE FND_API.G_EXC_ERROR;
9251:
9252:
9253:
9254: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
9255: FND_MESSAGE.SET_TOKEN('BLOCK_ID',2);
9256: FND_MSG_PUB.Add;
9257:
9258: RAISE FND_API.G_EXC_ERROR;
9259:
9845: l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is N.';
9846: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
9847: END IF;
9848:
9849: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
9850: FND_MESSAGE.SET_TOKEN('BLOCK_ID',3);
9851: FND_MSG_PUB.Add;
9852:
9853: RAISE FND_API.G_EXC_ERROR;
9846: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
9847: END IF;
9848:
9849: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
9850: FND_MESSAGE.SET_TOKEN('BLOCK_ID',3);
9851: FND_MSG_PUB.Add;
9852:
9853: RAISE FND_API.G_EXC_ERROR;
9854:
10132: l_debug_str := 'RAISE FND_API.G_EXC_ERROR in Update_Program_Info. p_person_rec.edu_status is N.';
10133: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
10134: END IF;
10135:
10136: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
10137: FND_MESSAGE.SET_TOKEN('BLOCK_ID',3);
10138: FND_MSG_PUB.Add;
10139:
10140: RAISE FND_API.G_EXC_ERROR;
10133: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
10134: END IF;
10135:
10136: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
10137: FND_MESSAGE.SET_TOKEN('BLOCK_ID',3);
10138: FND_MSG_PUB.Add;
10139:
10140: RAISE FND_API.G_EXC_ERROR;
10141:
10409: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
10410: END IF;
10411:
10412:
10413: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
10414: FND_MESSAGE.SET_TOKEN('BLOCK_ID',4);
10415: FND_MSG_PUB.Add;
10416:
10417: RAISE FND_API.G_EXC_ERROR;
10410: END IF;
10411:
10412:
10413: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
10414: FND_MESSAGE.SET_TOKEN('BLOCK_ID',4);
10415: FND_MSG_PUB.Add;
10416:
10417: RAISE FND_API.G_EXC_ERROR;
10418:
10721: -- check if dependent sevis id is present
10722:
10723: IF l_status = 'S' AND l_cur_rec(l_current).depdnt_sevis_id IS NULL THEN
10724:
10725: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
10726: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_cur_rec(l_current).person_number);
10727: Put_Log_Msg(FND_MESSAGE.Get,1);
10728:
10729: p_person_rec.person_status := 'I';
10722:
10723: IF l_status = 'S' AND l_cur_rec(l_current).depdnt_sevis_id IS NULL THEN
10724:
10725: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
10726: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_cur_rec(l_current).person_number);
10727: Put_Log_Msg(FND_MESSAGE.Get,1);
10728:
10729: p_person_rec.person_status := 'I';
10730:
10723: IF l_status = 'S' AND l_cur_rec(l_current).depdnt_sevis_id IS NULL THEN
10724:
10725: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
10726: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_cur_rec(l_current).person_number);
10727: Put_Log_Msg(FND_MESSAGE.Get,1);
10728:
10729: p_person_rec.person_status := 'I';
10730:
10731: Put_Log_Msg('Validation error occurs ',0);
11685: l_debug_str := 'Raise ERROR: Sevis Disabled';
11686: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11687: END IF;
11688:
11689: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DISABLED'); -- SEVIS disabled
11690: FND_MSG_PUB.Add;
11691: RAISE FND_API.G_EXC_ERROR;
11692:
11693: END IF;
11724: l_debug_str := 'Raise FND_API.G_EXC_ERROR: l_person_sevis_id is null';
11725: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11726: END IF;
11727:
11728: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_SUBMITTER_ID_ERR'); -- SEVIS submitter id not found
11729: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_submiter_number);
11730: FND_MSG_PUB.Add;
11731: RAISE FND_API.G_EXC_ERROR;
11732:
11725: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11726: END IF;
11727:
11728: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_SUBMITTER_ID_ERR'); -- SEVIS submitter id not found
11729: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_submiter_number);
11730: FND_MSG_PUB.Add;
11731: RAISE FND_API.G_EXC_ERROR;
11732:
11733: END IF;
11750: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11751: END IF;
11752:
11753: -- Active batch is found and mode not validate only - terminate current process
11754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BATCH_FOUND');
11755: FND_MESSAGE.SET_TOKEN('BATCH_ID', l_batch_id);
11756: FND_MSG_PUB.Add;
11757: RAISE FND_API.G_EXC_ERROR;
11758:
11751: END IF;
11752:
11753: -- Active batch is found and mode not validate only - terminate current process
11754: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BATCH_FOUND');
11755: FND_MESSAGE.SET_TOKEN('BATCH_ID', l_batch_id);
11756: FND_MSG_PUB.Add;
11757: RAISE FND_API.G_EXC_ERROR;
11758:
11759: END IF;
11872: IF c_prev_info%FOUND THEN
11873: l_student_rec.record_status := 'C'; --change mode
11874: -- If mode update - check SEVIS person Id
11875:
11876: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_UPD'); -- Student is found in Update mode
11877: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11878: Put_Log_Msg(FND_MESSAGE.Get,1);
11879:
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11873: l_student_rec.record_status := 'C'; --change mode
11874: -- If mode update - check SEVIS person Id
11875:
11876: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_UPD'); -- Student is found in Update mode
11877: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11878: Put_Log_Msg(FND_MESSAGE.Get,1);
11879:
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11881:
11874: -- If mode update - check SEVIS person Id
11875:
11876: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_UPD'); -- Student is found in Update mode
11877: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11878: Put_Log_Msg(FND_MESSAGE.Get,1);
11879:
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11881:
11882: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
11878: Put_Log_Msg(FND_MESSAGE.Get,1);
11879:
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11881:
11882: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
11883: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11884: Put_Log_Msg(FND_MESSAGE.Get,1);
11885: Put_Log_Msg('Sevis id not found',0);
11886:
11879:
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11881:
11882: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
11883: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11884: Put_Log_Msg(FND_MESSAGE.Get,1);
11885: Put_Log_Msg('Sevis id not found',0);
11886:
11887: l_student_rec.person_status := 'I';
11880: IF l_student_rec.sevis_user_id IS NULL THEN
11881:
11882: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_ID_ERR'); -- SEVIS STUDENT id not found
11883: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11884: Put_Log_Msg(FND_MESSAGE.Get,1);
11885: Put_Log_Msg('Sevis id not found',0);
11886:
11887: l_student_rec.person_status := 'I';
11888:
11889: END IF;
11890:
11891: ELSE
11892:
11893: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_CRT'); -- Student is found in Create mode
11894: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11895: Put_Log_Msg(FND_MESSAGE.Get,1);
11896:
11897: END IF;
11890:
11891: ELSE
11892:
11893: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_CRT'); -- Student is found in Create mode
11894: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11895: Put_Log_Msg(FND_MESSAGE.Get,1);
11896:
11897: END IF;
11898:
11891: ELSE
11892:
11893: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_CRT'); -- Student is found in Create mode
11894: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
11895: Put_Log_Msg(FND_MESSAGE.Get,1);
11896:
11897: END IF;
11898:
11899: CLOSE c_prev_info;
12062: OR l_student_rec.fin_status = 'N' THEN -- Not found
12063:
12064: -- These are I-20 blocks and should be found. If this happens - its a bug
12065:
12066: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
12067: FND_MESSAGE.SET_TOKEN('BLOCK_ID',5);
12068: FND_MSG_PUB.Add;
12069: /* Debug */
12070: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
12063:
12064: -- These are I-20 blocks and should be found. If this happens - its a bug
12065:
12066: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_UNEXP_EXCPT_ERR'); -- I-20 is missing! Unexpected error
12067: FND_MESSAGE.SET_TOKEN('BLOCK_ID',5);
12068: FND_MSG_PUB.Add;
12069: /* Debug */
12070: IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
12071: l_label := 'igs.plsql.igs_sv_batch_process_pkg.Create_Batch';
12080: -- Check for required blocks
12081:
12082: IF l_student_rec.f_addr_status = 'N' AND p_batch_type = 'I' THEN
12083:
12084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_F_ADDR_FLD_ERR'); -- Foreign address not found
12085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12086: Put_Log_Msg(FND_MESSAGE.Get,1);
12087:
12088: l_student_rec.person_status := 'I';
12081:
12082: IF l_student_rec.f_addr_status = 'N' AND p_batch_type = 'I' THEN
12083:
12084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_F_ADDR_FLD_ERR'); -- Foreign address not found
12085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12086: Put_Log_Msg(FND_MESSAGE.Get,1);
12087:
12088: l_student_rec.person_status := 'I';
12089:
12082: IF l_student_rec.f_addr_status = 'N' AND p_batch_type = 'I' THEN
12083:
12084: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_F_ADDR_FLD_ERR'); -- Foreign address not found
12085: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12086: Put_Log_Msg(FND_MESSAGE.Get,1);
12087:
12088: l_student_rec.person_status := 'I';
12089:
12090: END IF;
12089:
12090: END IF;
12091: IF l_student_rec.us_addr_status = 'N' AND p_batch_type = 'E' THEN
12092:
12093: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_US_ADDR_FLD_ERR'); -- US address not found
12094: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12095: Put_Log_Msg(FND_MESSAGE.Get,1);
12096:
12097: l_student_rec.person_status := 'I';
12090: END IF;
12091: IF l_student_rec.us_addr_status = 'N' AND p_batch_type = 'E' THEN
12092:
12093: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_US_ADDR_FLD_ERR'); -- US address not found
12094: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12095: Put_Log_Msg(FND_MESSAGE.Get,1);
12096:
12097: l_student_rec.person_status := 'I';
12098:
12091: IF l_student_rec.us_addr_status = 'N' AND p_batch_type = 'E' THEN
12092:
12093: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_US_ADDR_FLD_ERR'); -- US address not found
12094: FND_MESSAGE.SET_TOKEN('PERSON_NUMBER', l_student_rec.person_number);
12095: Put_Log_Msg(FND_MESSAGE.Get,1);
12096:
12097: l_student_rec.person_status := 'I';
12098:
12099: END IF;
12403: --Remove all person data from tables
12404:
12405: Remove_Person_Data(p_person_id => l_student_rec.person_id, p_batch_id => l_batch_id);
12406:
12407: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_INVALID'); -- Student has validation error - not processed
12408: Put_Log_Msg(FND_MESSAGE.Get,1);
12409:
12410: ELSIF l_student_rec.changes_found = 'Y' THEN
12411:
12404:
12405: Remove_Person_Data(p_person_id => l_student_rec.person_id, p_batch_id => l_batch_id);
12406:
12407: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_INVALID'); -- Student has validation error - not processed
12408: Put_Log_Msg(FND_MESSAGE.Get,1);
12409:
12410: ELSIF l_student_rec.changes_found = 'Y' THEN
12411:
12412: IF p_validate_only ='N' THEN
12410: ELSIF l_student_rec.changes_found = 'Y' THEN
12411:
12412: IF p_validate_only ='N' THEN
12413:
12414: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_SUCCESS'); -- Student successfully processed
12415: Put_Log_Msg(FND_MESSAGE.Get,1);
12416:
12417: ELSE
12418:
12411:
12412: IF p_validate_only ='N' THEN
12413:
12414: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_SUCCESS'); -- Student successfully processed
12415: Put_Log_Msg(FND_MESSAGE.Get,1);
12416:
12417: ELSE
12418:
12419: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STDNT_VALID_SUCC'); -- Student successfully processed
12415: Put_Log_Msg(FND_MESSAGE.Get,1);
12416:
12417: ELSE
12418:
12419: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STDNT_VALID_SUCC'); -- Student successfully processed
12420: Put_Log_Msg(FND_MESSAGE.Get,1);
12421:
12422: END IF;
12423:
12416:
12417: ELSE
12418:
12419: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STDNT_VALID_SUCC'); -- Student successfully processed
12420: Put_Log_Msg(FND_MESSAGE.Get,1);
12421:
12422: END IF;
12423:
12424: l_batch_status :='S';
12427:
12428: --Remove all person data from tables
12429: Remove_Person_Data(p_person_id => l_student_rec.person_id, p_batch_id => l_batch_id);
12430:
12431: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_NO_CHANGE'); -- No changes found for the student
12432: Put_Log_Msg(FND_MESSAGE.Get,1);
12433:
12434: END IF;
12435: IF p_batch_type ='I' THEN
12428: --Remove all person data from tables
12429: Remove_Person_Data(p_person_id => l_student_rec.person_id, p_batch_id => l_batch_id);
12430:
12431: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_STUDENT_NO_CHANGE'); -- No changes found for the student
12432: Put_Log_Msg(FND_MESSAGE.Get,1);
12433:
12434: END IF;
12435: IF p_batch_type ='I' THEN
12436: FETCH C_NI_CUR INTO c_student_list_rec;
12449:
12450: IF l_batch_status = 'N' THEN
12451: -- no students for the batch - change
12452:
12453: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_STUDENTS'); -- No students found to submit
12454: Put_Log_Msg(FND_MESSAGE.Get,1);
12455:
12456: DELETE FROM igs_sv_batches WHERE batch_id = l_batch_id;
12457:
12450: IF l_batch_status = 'N' THEN
12451: -- no students for the batch - change
12452:
12453: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_STUDENTS'); -- No students found to submit
12454: Put_Log_Msg(FND_MESSAGE.Get,1);
12455:
12456: DELETE FROM igs_sv_batches WHERE batch_id = l_batch_id;
12457:
12458: END IF;
12459:
12460: /*
12461: IF l_record_number >=250 THEN
12462:
12463: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_MAX_RECORDS'); -- Too many students.
12464: Put_Log_Msg(FND_MESSAGE.Get,1);
12465:
12466: END IF;
12467: */
12460: /*
12461: IF l_record_number >=250 THEN
12462:
12463: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_MAX_RECORDS'); -- Too many students.
12464: Put_Log_Msg(FND_MESSAGE.Get,1);
12465:
12466: END IF;
12467: */
12468:
12688: CLOSE c_active_batch;
12689:
12690: IF l_batch_id IS NULL THEN
12691: -- Active batch is not found and mode not validate only - terminate current process
12692: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_BATCH_FOUND');
12693: Put_Log_Msg(FND_MESSAGE.Get,1);
12694:
12695: ELSE
12696:
12689:
12690: IF l_batch_id IS NULL THEN
12691: -- Active batch is not found and mode not validate only - terminate current process
12692: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_NO_BATCH_FOUND');
12693: Put_Log_Msg(FND_MESSAGE.Get,1);
12694:
12695: ELSE
12696:
12697: --Remove batch
13123:
13124: IF c_batch%NOTFOUND THEN
13125:
13126: CLOSE c_batch;
13127: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BATCH_NOT_FOUND'); -- Batch not found
13128: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_BatchID);
13129: FND_MSG_PUB.Add;
13130:
13131: RAISE FND_API.G_EXC_ERROR;
13124: IF c_batch%NOTFOUND THEN
13125:
13126: CLOSE c_batch;
13127: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_BATCH_NOT_FOUND'); -- Batch not found
13128: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_BatchID);
13129: FND_MSG_PUB.Add;
13130:
13131: RAISE FND_API.G_EXC_ERROR;
13132:
13137: IF lower(p_FileValidation) = 'true' OR p_FileValidation='1' THEN
13138: -- successfull batch transmition.
13139:
13140: IF l_status <>'S' THEN
13141: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INVALID_STATUS'); -- Batch is not in process
13142: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_BatchID);
13143: FND_MSG_PUB.Add;
13144:
13145: RAISE FND_API.G_EXC_ERROR;
13138: -- successfull batch transmition.
13139:
13140: IF l_status <>'S' THEN
13141: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_INVALID_STATUS'); -- Batch is not in process
13142: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_BatchID);
13143: FND_MSG_PUB.Add;
13144:
13145: RAISE FND_API.G_EXC_ERROR;
13146:
13314: IF c_dep%NOTFOUND THEN
13315:
13316: CLOSE c_dep;
13317:
13318: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DEP_NOT_FOUND'); -- Dependent not found
13319: FND_MESSAGE.SET_TOKEN('PERSON_ID',p_PersonID);
13320: FND_MSG_PUB.Add;
13321:
13322: RAISE FND_API.G_EXC_ERROR;
13315:
13316: CLOSE c_dep;
13317:
13318: FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_DEP_NOT_FOUND'); -- Dependent not found
13319: FND_MESSAGE.SET_TOKEN('PERSON_ID',p_PersonID);
13320: FND_MSG_PUB.Add;
13321:
13322: RAISE FND_API.G_EXC_ERROR;
13323:
13491: EXCEPTION
13492: WHEN OTHERS THEN
13493: ROLLBACK;
13494: retcode := 2;
13495: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
13496: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Generate_Batch_XML'|| '-' || SQLERRM);
13497: igs_ge_msg_stack.conc_exception_hndl;
13498: END Generate_Batch_XML;
13499:
13492: WHEN OTHERS THEN
13493: ROLLBACK;
13494: retcode := 2;
13495: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
13496: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Generate_Batch_XML'|| '-' || SQLERRM);
13497: igs_ge_msg_stack.conc_exception_hndl;
13498: END Generate_Batch_XML;
13499:
13500:
13557: FOR c_new_pers_rec IN c_new_pers(p_batch_id) LOOP
13558: l_per_count := l_per_count + 1;
13559: IF l_per_count = 1 THEN
13560: fnd_file.put_line(FND_FILE.LOG,' ');
13561: fnd_message.set_name('IGS','IGS_SV_PERS_ON_HOLD');
13562: fnd_file.put_line(fnd_file.log,fnd_message.get());
13563: fnd_file.put_line(FND_FILE.LOG,' ');
13564: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13565: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13558: l_per_count := l_per_count + 1;
13559: IF l_per_count = 1 THEN
13560: fnd_file.put_line(FND_FILE.LOG,' ');
13561: fnd_message.set_name('IGS','IGS_SV_PERS_ON_HOLD');
13562: fnd_file.put_line(fnd_file.log,fnd_message.get());
13563: fnd_file.put_line(FND_FILE.LOG,' ');
13564: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13565: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13566: Put_Log_Msg(' --------------',1);
13560: fnd_file.put_line(FND_FILE.LOG,' ');
13561: fnd_message.set_name('IGS','IGS_SV_PERS_ON_HOLD');
13562: fnd_file.put_line(fnd_file.log,fnd_message.get());
13563: fnd_file.put_line(FND_FILE.LOG,' ');
13564: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13565: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13566: Put_Log_Msg(' --------------',1);
13567: END IF;
13568: Put_Log_Msg(' ' || c_new_pers_rec.person_number,1);
13561: fnd_message.set_name('IGS','IGS_SV_PERS_ON_HOLD');
13562: fnd_file.put_line(fnd_file.log,fnd_message.get());
13563: fnd_file.put_line(FND_FILE.LOG,' ');
13564: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13565: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13566: Put_Log_Msg(' --------------',1);
13567: END IF;
13568: Put_Log_Msg(' ' || c_new_pers_rec.person_number,1);
13569: END LOOP;
13573: FOR c_updated_pers_rec IN c_updated_pers(p_batch_id) LOOP
13574: l_per_count := l_per_count + 1;
13575: IF l_per_count = 1 THEN
13576: fnd_file.put_line(FND_FILE.LOG,' ');
13577: fnd_message.set_name('IGS','IGS_SV_INFO_ON_HOLD');
13578: fnd_file.put_line(fnd_file.log,fnd_message.get());
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13574: l_per_count := l_per_count + 1;
13575: IF l_per_count = 1 THEN
13576: fnd_file.put_line(FND_FILE.LOG,' ');
13577: fnd_message.set_name('IGS','IGS_SV_INFO_ON_HOLD');
13578: fnd_file.put_line(fnd_file.log,fnd_message.get());
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13582: fnd_message.set_name('IGS','IGS_SV_INFMN');
13576: fnd_file.put_line(FND_FILE.LOG,' ');
13577: fnd_message.set_name('IGS','IGS_SV_INFO_ON_HOLD');
13578: fnd_file.put_line(fnd_file.log,fnd_message.get());
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13582: fnd_message.set_name('IGS','IGS_SV_INFMN');
13583: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13584: Put_Log_Msg(' -------------- ------------ ',1);
13577: fnd_message.set_name('IGS','IGS_SV_INFO_ON_HOLD');
13578: fnd_file.put_line(fnd_file.log,fnd_message.get());
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13582: fnd_message.set_name('IGS','IGS_SV_INFMN');
13583: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13584: Put_Log_Msg(' -------------- ------------ ',1);
13585:
13578: fnd_file.put_line(fnd_file.log,fnd_message.get());
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13582: fnd_message.set_name('IGS','IGS_SV_INFMN');
13583: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13584: Put_Log_Msg(' -------------- ------------ ',1);
13585:
13586: END IF;
13579: fnd_file.put_line(FND_FILE.LOG,' ');
13580: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13581: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13582: fnd_message.set_name('IGS','IGS_SV_INFMN');
13583: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13584: Put_Log_Msg(' -------------- ------------ ',1);
13585:
13586: END IF;
13587: OPEN c_get_prsn_num(c_updated_pers_rec.person_id);
13665: AND svbs.batch_id = pers.batch_id
13666: AND pers.record_status = 'C';
13667:
13668: BEGIN
13669: fnd_message.set_name('IGS','IGS_SV_BTCH');
13670: fnd_file.put_line(fnd_file.log,fnd_message.get());
13671: fnd_file.put_line(FND_FILE.LOG,' ');
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13666: AND pers.record_status = 'C';
13667:
13668: BEGIN
13669: fnd_message.set_name('IGS','IGS_SV_BTCH');
13670: fnd_file.put_line(fnd_file.log,fnd_message.get());
13671: fnd_file.put_line(FND_FILE.LOG,' ');
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13674: fnd_file.put(fnd_file.log,' ' || fnd_message.get());
13669: fnd_message.set_name('IGS','IGS_SV_BTCH');
13670: fnd_file.put_line(fnd_file.log,fnd_message.get());
13671: fnd_file.put_line(FND_FILE.LOG,' ');
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13674: fnd_file.put(fnd_file.log,' ' || fnd_message.get());
13675: fnd_message.set_name('IGS','IGS_SV_PERS_COUNT');
13676: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13677: --Put_Log_Msg(' Batch ID No. of Persons',0);
13670: fnd_file.put_line(fnd_file.log,fnd_message.get());
13671: fnd_file.put_line(FND_FILE.LOG,' ');
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13674: fnd_file.put(fnd_file.log,' ' || fnd_message.get());
13675: fnd_message.set_name('IGS','IGS_SV_PERS_COUNT');
13676: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13677: --Put_Log_Msg(' Batch ID No. of Persons',0);
13678: --Put_Log_Msg(' -------- --------------',0);
13671: fnd_file.put_line(FND_FILE.LOG,' ');
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13674: fnd_file.put(fnd_file.log,' ' || fnd_message.get());
13675: fnd_message.set_name('IGS','IGS_SV_PERS_COUNT');
13676: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13677: --Put_Log_Msg(' Batch ID No. of Persons',0);
13678: --Put_Log_Msg(' -------- --------------',0);
13679: -- g_running_batches(1) := 2020205;
13672: --Put_Log_Msg(' Following Batch IDs are generated: ',0);
13673: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13674: fnd_file.put(fnd_file.log,' ' || fnd_message.get());
13675: fnd_message.set_name('IGS','IGS_SV_PERS_COUNT');
13676: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13677: --Put_Log_Msg(' Batch ID No. of Persons',0);
13678: --Put_Log_Msg(' -------- --------------',0);
13679: -- g_running_batches(1) := 2020205;
13680:
13696: l_batch_count := l_batch_count+1;
13697: END LOOP;
13698:
13699: fnd_file.put_line(FND_FILE.LOG,' ');
13700: fnd_message.set_name('IGS','IGS_SV_BTCH_PERS');
13701: fnd_file.put_line(fnd_file.log,fnd_message.get());
13702: fnd_file.put_line(FND_FILE.LOG,' ');
13703: -- Put_Log_Msg(' ',0);
13704: -- Put_Log_Msg(' Following are the person records included in each batch: ',0);
13697: END LOOP;
13698:
13699: fnd_file.put_line(FND_FILE.LOG,' ');
13700: fnd_message.set_name('IGS','IGS_SV_BTCH_PERS');
13701: fnd_file.put_line(fnd_file.log,fnd_message.get());
13702: fnd_file.put_line(FND_FILE.LOG,' ');
13703: -- Put_Log_Msg(' ',0);
13704: -- Put_Log_Msg(' Following are the person records included in each batch: ',0);
13705: -- Put_Log_Msg(' ',0);
13704: -- Put_Log_Msg(' Following are the person records included in each batch: ',0);
13705: -- Put_Log_Msg(' ',0);
13706:
13707: FOR i IN 1..g_parallel_batches.COUNT LOOP
13708: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13709: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get() || ': ' || g_parallel_batches(i));
13710: -- Put_Log_Msg(' Batch ID: '||g_parallel_batches(i),0);
13711: -- Put_Log_Msg(' ------------------------',0);
13712: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13705: -- Put_Log_Msg(' ',0);
13706:
13707: FOR i IN 1..g_parallel_batches.COUNT LOOP
13708: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13709: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get() || ': ' || g_parallel_batches(i));
13710: -- Put_Log_Msg(' Batch ID: '||g_parallel_batches(i),0);
13711: -- Put_Log_Msg(' ------------------------',0);
13712: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13713: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13708: fnd_message.set_name('IGS','IGS_SV_BTCH_ID');
13709: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get() || ': ' || g_parallel_batches(i));
13710: -- Put_Log_Msg(' Batch ID: '||g_parallel_batches(i),0);
13711: -- Put_Log_Msg(' ------------------------',0);
13712: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13713: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13714: --- Put_Log_Msg(' New Persons: ',0);
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13709: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get() || ': ' || g_parallel_batches(i));
13710: -- Put_Log_Msg(' Batch ID: '||g_parallel_batches(i),0);
13711: -- Put_Log_Msg(' ------------------------',0);
13712: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13713: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13714: --- Put_Log_Msg(' New Persons: ',0);
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13717: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13712: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13713: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13714: --- Put_Log_Msg(' New Persons: ',0);
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13717: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13718: fnd_message.set_name('IGS','IGS_SV_INFMN');
13719: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13720: -- Put_Log_Msg(' Person Number Information ',0);
13713: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13714: --- Put_Log_Msg(' New Persons: ',0);
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13717: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13718: fnd_message.set_name('IGS','IGS_SV_INFMN');
13719: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13720: -- Put_Log_Msg(' Person Number Information ',0);
13721: Put_Log_Msg(' -------------- ------------ ',1);
13714: --- Put_Log_Msg(' New Persons: ',0);
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13717: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13718: fnd_message.set_name('IGS','IGS_SV_INFMN');
13719: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13720: -- Put_Log_Msg(' Person Number Information ',0);
13721: Put_Log_Msg(' -------------- ------------ ',1);
13722:
13715: -- Put_Log_Msg(' ------------',0);
13716: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13717: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13718: fnd_message.set_name('IGS','IGS_SV_INFMN');
13719: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13720: -- Put_Log_Msg(' Person Number Information ',0);
13721: Put_Log_Msg(' -------------- ------------ ',1);
13722:
13723: FOR new_persons IN c_get_new_persons(g_parallel_batches(i)) LOOP
13723: FOR new_persons IN c_get_new_persons(g_parallel_batches(i)) LOOP
13724: Put_Log_Msg(' ' || rpad(new_persons.prsn_num,30,' ') || ' ' || new_persons.info,1);
13725: END LOOP;
13726:
13727: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13728: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13729: --Put_Log_Msg(' Updated Persons: ',0);
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13724: Put_Log_Msg(' ' || rpad(new_persons.prsn_num,30,' ') || ' ' || new_persons.info,1);
13725: END LOOP;
13726:
13727: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13728: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13729: --Put_Log_Msg(' Updated Persons: ',0);
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13732: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13727: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13728: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13729: --Put_Log_Msg(' Updated Persons: ',0);
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13732: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13733: fnd_message.set_name('IGS','IGS_SV_INFMN');
13734: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13735: --Put_Log_Msg(' Person Number Information ',0);
13728: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13729: --Put_Log_Msg(' Updated Persons: ',0);
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13732: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13733: fnd_message.set_name('IGS','IGS_SV_INFMN');
13734: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13735: --Put_Log_Msg(' Person Number Information ',0);
13736: Put_Log_Msg(' -------------- ------------ ',1);
13729: --Put_Log_Msg(' Updated Persons: ',0);
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13732: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13733: fnd_message.set_name('IGS','IGS_SV_INFMN');
13734: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13735: --Put_Log_Msg(' Person Number Information ',0);
13736: Put_Log_Msg(' -------------- ------------ ',1);
13737:
13730: Put_Log_Msg(' ----------------',1);
13731: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13732: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13733: fnd_message.set_name('IGS','IGS_SV_INFMN');
13734: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13735: --Put_Log_Msg(' Person Number Information ',0);
13736: Put_Log_Msg(' -------------- ------------ ',1);
13737:
13738: FOR updated_persons IN c_get_updated_persons(g_parallel_batches(i)) LOOP
13743:
13744: FOR i IN 1..g_running_batches.COUNT LOOP
13745: Put_Log_Msg(' Batch ID: ' || g_running_batches(i),1);
13746: Put_Log_Msg(' ------------------------',1);
13747: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13748: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13749: -- Put_Log_Msg(' New Persons: ',0);
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13744: FOR i IN 1..g_running_batches.COUNT LOOP
13745: Put_Log_Msg(' Batch ID: ' || g_running_batches(i),1);
13746: Put_Log_Msg(' ------------------------',1);
13747: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13748: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13749: -- Put_Log_Msg(' New Persons: ',0);
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13752: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13747: fnd_message.set_name('IGS','IGS_SV_NEW_PERS');
13748: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13749: -- Put_Log_Msg(' New Persons: ',0);
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13752: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13753: fnd_message.set_name('IGS','IGS_SV_INFMN');
13754: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13755: -- Put_Log_Msg(' Person Number Information ',0);
13748: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13749: -- Put_Log_Msg(' New Persons: ',0);
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13752: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13753: fnd_message.set_name('IGS','IGS_SV_INFMN');
13754: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13755: -- Put_Log_Msg(' Person Number Information ',0);
13756: Put_Log_Msg(' -------------- ------------ ',1);
13749: -- Put_Log_Msg(' New Persons: ',0);
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13752: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13753: fnd_message.set_name('IGS','IGS_SV_INFMN');
13754: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13755: -- Put_Log_Msg(' Person Number Information ',0);
13756: Put_Log_Msg(' -------------- ------------ ',1);
13757:
13750: Put_Log_Msg(' ------------',1);
13751: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13752: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13753: fnd_message.set_name('IGS','IGS_SV_INFMN');
13754: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13755: -- Put_Log_Msg(' Person Number Information ',0);
13756: Put_Log_Msg(' -------------- ------------ ',1);
13757:
13758: FOR new_persons IN c_get_new_persons(g_running_batches(i)) LOOP
13758: FOR new_persons IN c_get_new_persons(g_running_batches(i)) LOOP
13759: Put_Log_Msg(' ' || rpad(new_persons.prsn_num,30,' ') || ' ' || new_persons.info,1);
13760: END LOOP;
13761:
13762: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13763: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13764: -- Put_Log_Msg(' Updated Persons: ',0);
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13759: Put_Log_Msg(' ' || rpad(new_persons.prsn_num,30,' ') || ' ' || new_persons.info,1);
13760: END LOOP;
13761:
13762: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13763: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13764: -- Put_Log_Msg(' Updated Persons: ',0);
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13767: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13762: fnd_message.set_name('IGS','IGS_SV_UPD_PERS');
13763: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13764: -- Put_Log_Msg(' Updated Persons: ',0);
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13767: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13768: fnd_message.set_name('IGS','IGS_SV_INFMN');
13769: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13770: --Put_Log_Msg(' Person Number Information ',0);
13763: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13764: -- Put_Log_Msg(' Updated Persons: ',0);
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13767: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13768: fnd_message.set_name('IGS','IGS_SV_INFMN');
13769: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13770: --Put_Log_Msg(' Person Number Information ',0);
13771: Put_Log_Msg(' -------------- ------------ ',1);
13764: -- Put_Log_Msg(' Updated Persons: ',0);
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13767: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13768: fnd_message.set_name('IGS','IGS_SV_INFMN');
13769: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13770: --Put_Log_Msg(' Person Number Information ',0);
13771: Put_Log_Msg(' -------------- ------------ ',1);
13772:
13765: Put_Log_Msg(' ----------------',1);
13766: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
13767: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),30,' '));
13768: fnd_message.set_name('IGS','IGS_SV_INFMN');
13769: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
13770: --Put_Log_Msg(' Person Number Information ',0);
13771: Put_Log_Msg(' -------------- ------------ ',1);
13772:
13773: FOR updated_persons IN c_get_updated_persons(g_running_batches(i)) LOOP
13919: IF p_group_type = 'G' THEN
13920:
13921: IF p_group_id IS NULL THEN
13922: -- Put_Log_Msg('Group ID cannot be left blank when new DSO is being assigned for a group', 0);
13923: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_GRP_ID');
13924: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
13925: FND_MSG_PUB.Add;
13926: RAISE FND_API.G_EXC_ERROR;
13927: END IF;
13920:
13921: IF p_group_id IS NULL THEN
13922: -- Put_Log_Msg('Group ID cannot be left blank when new DSO is being assigned for a group', 0);
13923: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_GRP_ID');
13924: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
13925: FND_MSG_PUB.Add;
13926: RAISE FND_API.G_EXC_ERROR;
13927: END IF;
13928:
13927: END IF;
13928:
13929: l_str := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_group_id ,lv_status);
13930: IF lv_status <> 'S' THEN
13931: FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
13932: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
13933: RETURN;
13934: END IF;
13935:
13928:
13929: l_str := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_group_id ,lv_status);
13930: IF lv_status <> 'S' THEN
13931: FND_MESSAGE.SET_NAME('IGF','IGF_AW_NO_QUERY');
13932: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
13933: RETURN;
13934: END IF;
13935:
13936: /* Debug */
14003: FETCH c_get_person_num INTO l_person_num, l_first_name, l_last_name;
14004: CLOSE c_get_person_num;
14005:
14006: IF l_counter = 1 THEN
14007: fnd_message.set_name('IGS','IGS_SV_NEW_REL');
14008: fnd_message.set_token('PRSN_NAME', l_first_name||' '|| l_last_name);
14009: fnd_file.put_line(fnd_file.log,fnd_message.get());
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14004: CLOSE c_get_person_num;
14005:
14006: IF l_counter = 1 THEN
14007: fnd_message.set_name('IGS','IGS_SV_NEW_REL');
14008: fnd_message.set_token('PRSN_NAME', l_first_name||' '|| l_last_name);
14009: fnd_file.put_line(fnd_file.log,fnd_message.get());
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14005:
14006: IF l_counter = 1 THEN
14007: fnd_message.set_name('IGS','IGS_SV_NEW_REL');
14008: fnd_message.set_token('PRSN_NAME', l_first_name||' '|| l_last_name);
14009: fnd_file.put_line(fnd_file.log,fnd_message.get());
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14013: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14008: fnd_message.set_token('PRSN_NAME', l_first_name||' '|| l_last_name);
14009: fnd_file.put_line(fnd_file.log,fnd_message.get());
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14013: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14014: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14015: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14016: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14009: fnd_file.put_line(fnd_file.log,fnd_message.get());
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14013: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14014: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14015: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14016: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14017: fnd_file.put_line(fnd_file.log,' -------------');
14010: -- Put_Log_Msg('New DSO_OF relationship created for following persons with '||l_first_name||' '|| l_last_name, 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14013: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14014: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14015: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14016: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14017: fnd_file.put_line(fnd_file.log,' -------------');
14018: -- Put_Log_Msg('Person Name Person Number', 0);
14011: Put_Log_Msg(' ', 1);
14012: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14013: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14014: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14015: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14016: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14017: fnd_file.put_line(fnd_file.log,' -------------');
14018: -- Put_Log_Msg('Person Name Person Number', 0);
14019: -- Put_Log_Msg('----------- -------------', 0);
14042: CLOSE c_person_rec;
14043: FOR i IN 1..g_existing_rel.COUNT LOOP
14044: IF i = 1 THEN
14045: Put_Log_Msg(' ', 1);
14046: fnd_message.set_name('IGS','IGS_SV_REL_EXISTS');
14047: fnd_file.put_line(fnd_file.log,fnd_message.get());
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14043: FOR i IN 1..g_existing_rel.COUNT LOOP
14044: IF i = 1 THEN
14045: Put_Log_Msg(' ', 1);
14046: fnd_message.set_name('IGS','IGS_SV_REL_EXISTS');
14047: fnd_file.put_line(fnd_file.log,fnd_message.get());
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14045: Put_Log_Msg(' ', 1);
14046: fnd_message.set_name('IGS','IGS_SV_REL_EXISTS');
14047: fnd_file.put_line(fnd_file.log,fnd_message.get());
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14046: fnd_message.set_name('IGS','IGS_SV_REL_EXISTS');
14047: fnd_file.put_line(fnd_file.log,fnd_message.get());
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14054: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14047: fnd_file.put_line(fnd_file.log,fnd_message.get());
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14054: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14055:
14048: Put_Log_Msg(' ', 1);
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14054: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14055:
14056: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14049: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14054: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14055:
14056: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14057: fnd_file.put(fnd_file.log, rpad(' -----------',20,' '));
14050: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14051: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14052: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),20,' '));
14053: fnd_message.set_name('IGS','IGS_SV_DSO');
14054: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14055:
14056: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14057: fnd_file.put(fnd_file.log, rpad(' -----------',20,' '));
14058: fnd_file.put_line(fnd_file.log,' -------------');
14066: fnd_file.put_line(fnd_file.log,' ' || g_existing_rel(i).dso_person_num);
14067: -- Put_Log_Msg(g_existing_rel(i).person_name || ' '||rpad(g_existing_rel(i).person_num,20,' ')||' ' || g_existing_rel(i).dso_person_num,0);
14068: END LOOP;
14069: Put_Log_Msg(' ', 1);
14070: fnd_message.set_name('IGS','IGS_SV_REL_CREATED');
14071: fnd_message.set_token('COUNT', l_counter);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14067: -- Put_Log_Msg(g_existing_rel(i).person_name || ' '||rpad(g_existing_rel(i).person_num,20,' ')||' ' || g_existing_rel(i).dso_person_num,0);
14068: END LOOP;
14069: Put_Log_Msg(' ', 1);
14070: fnd_message.set_name('IGS','IGS_SV_REL_CREATED');
14071: fnd_message.set_token('COUNT', l_counter);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14075: fnd_message.set_token('COUNT', g_existing_rel.COUNT);
14068: END LOOP;
14069: Put_Log_Msg(' ', 1);
14070: fnd_message.set_name('IGS','IGS_SV_REL_CREATED');
14071: fnd_message.set_token('COUNT', l_counter);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14075: fnd_message.set_token('COUNT', g_existing_rel.COUNT);
14076: fnd_file.put_line(fnd_file.log,fnd_message.get());
14070: fnd_message.set_name('IGS','IGS_SV_REL_CREATED');
14071: fnd_message.set_token('COUNT', l_counter);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14075: fnd_message.set_token('COUNT', g_existing_rel.COUNT);
14076: fnd_file.put_line(fnd_file.log,fnd_message.get());
14077: ELSE
14078: IF p_old_dso_id IS NULL THEN
14071: fnd_message.set_token('COUNT', l_counter);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14075: fnd_message.set_token('COUNT', g_existing_rel.COUNT);
14076: fnd_file.put_line(fnd_file.log,fnd_message.get());
14077: ELSE
14078: IF p_old_dso_id IS NULL THEN
14079: --Put_Log_Msg('Enter the old DSO ID for which new relationships have to be created.', 0);
14072: fnd_file.put_line(fnd_file.log,fnd_message.get());
14073: Put_Log_Msg(' ', 1);
14074: fnd_message.set_name('IGS','IGS_SV_REL_NOT_CRTED');
14075: fnd_message.set_token('COUNT', g_existing_rel.COUNT);
14076: fnd_file.put_line(fnd_file.log,fnd_message.get());
14077: ELSE
14078: IF p_old_dso_id IS NULL THEN
14079: --Put_Log_Msg('Enter the old DSO ID for which new relationships have to be created.', 0);
14080: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_DSO_ID');
14076: fnd_file.put_line(fnd_file.log,fnd_message.get());
14077: ELSE
14078: IF p_old_dso_id IS NULL THEN
14079: --Put_Log_Msg('Enter the old DSO ID for which new relationships have to be created.', 0);
14080: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_DSO_ID');
14081: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
14082: FND_MSG_PUB.Add;
14083: RAISE FND_API.G_EXC_ERROR;
14084: END IF;
14077: ELSE
14078: IF p_old_dso_id IS NULL THEN
14079: --Put_Log_Msg('Enter the old DSO ID for which new relationships have to be created.', 0);
14080: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_DSO_ID');
14081: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
14082: FND_MSG_PUB.Add;
14083: RAISE FND_API.G_EXC_ERROR;
14084: END IF;
14085: IF p_old_dso_id = p_new_dso_id THEN
14083: RAISE FND_API.G_EXC_ERROR;
14084: END IF;
14085: IF p_old_dso_id = p_new_dso_id THEN
14086: --Put_Log_Msg('Old DSO ID and new DSO ID cannot be same. Request Terminated', 0);
14087: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_PARAMS');
14088: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
14089: FND_MSG_PUB.Add;
14090: RAISE FND_API.G_EXC_ERROR;
14091: END IF;
14084: END IF;
14085: IF p_old_dso_id = p_new_dso_id THEN
14086: --Put_Log_Msg('Old DSO ID and new DSO ID cannot be same. Request Terminated', 0);
14087: FND_MESSAGE.SET_NAME('IGS','IGS_SV_INV_PARAMS');
14088: FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
14089: FND_MSG_PUB.Add;
14090: RAISE FND_API.G_EXC_ERROR;
14091: END IF;
14092: FOR r_old_dso_rel_rec IN c_old_dso_rel(l_old_dso_partyid) LOOP
14164: --Put_Log_Msg('DSO_OF relationship of following persons is end dated with '||l_first_name||' '|| l_last_name, 0);
14165: OPEN c_get_person_num(l_new_dso_partyid);
14166: FETCH c_get_person_num INTO l_new_person_num, l_new_first_name, l_new_last_name;
14167: CLOSE c_get_person_num;
14168: fnd_message.set_name('IGS','IGS_SV_REL_UPD');
14169: fnd_message.set_token('OLD_DSO_NAME', l_first_name||' '|| l_last_name);
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14165: OPEN c_get_person_num(l_new_dso_partyid);
14166: FETCH c_get_person_num INTO l_new_person_num, l_new_first_name, l_new_last_name;
14167: CLOSE c_get_person_num;
14168: fnd_message.set_name('IGS','IGS_SV_REL_UPD');
14169: fnd_message.set_token('OLD_DSO_NAME', l_first_name||' '|| l_last_name);
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14166: FETCH c_get_person_num INTO l_new_person_num, l_new_first_name, l_new_last_name;
14167: CLOSE c_get_person_num;
14168: fnd_message.set_name('IGS','IGS_SV_REL_UPD');
14169: fnd_message.set_token('OLD_DSO_NAME', l_first_name||' '|| l_last_name);
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14167: CLOSE c_get_person_num;
14168: fnd_message.set_name('IGS','IGS_SV_REL_UPD');
14169: fnd_message.set_token('OLD_DSO_NAME', l_first_name||' '|| l_last_name);
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14175: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14169: fnd_message.set_token('OLD_DSO_NAME', l_first_name||' '|| l_last_name);
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14175: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14176: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14177: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14170: fnd_message.set_token('NEW_DSO_NAME', l_new_first_name||' '|| l_new_last_name);
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14175: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14176: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14177: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14178: fnd_file.put_line(fnd_file.log,' -------------');
14171: fnd_file.put_line(fnd_file.log,fnd_message.get());
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14175: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14176: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14177: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14178: fnd_file.put_line(fnd_file.log,' -------------');
14179:
14172: Put_Log_Msg(' ', 1);
14173: fnd_message.set_name('IGS','IGS_SV_PRSN_NAME');
14174: fnd_file.put(fnd_file.log,' ' || rpad(fnd_message.get(),60,' '));
14175: fnd_message.set_name('IGS','IGS_SV_PER_NUM');
14176: fnd_file.put_line(fnd_file.log,' ' || fnd_message.get());
14177: fnd_file.put(fnd_file.log, rpad(' -----------',60,' '));
14178: fnd_file.put_line(fnd_file.log,' -------------');
14179:
14180: --Put_Log_Msg('and new relationship created with '||l_new_first_name||' '|| l_new_last_name||':', 0);
14190:
14191:
14192: END LOOP;
14193: Put_Log_Msg(' ', 1);
14194: fnd_message.set_name('IGS','IGS_SV_REL_UPDATED');
14195: fnd_message.set_token('COUNT', l_count);
14196: fnd_file.put_line(fnd_file.log,fnd_message.get());
14197: END IF;
14198:
14191:
14192: END LOOP;
14193: Put_Log_Msg(' ', 1);
14194: fnd_message.set_name('IGS','IGS_SV_REL_UPDATED');
14195: fnd_message.set_token('COUNT', l_count);
14196: fnd_file.put_line(fnd_file.log,fnd_message.get());
14197: END IF;
14198:
14199: EXCEPTION
14192: END LOOP;
14193: Put_Log_Msg(' ', 1);
14194: fnd_message.set_name('IGS','IGS_SV_REL_UPDATED');
14195: fnd_message.set_token('COUNT', l_count);
14196: fnd_file.put_line(fnd_file.log,fnd_message.get());
14197: END IF;
14198:
14199: EXCEPTION
14200: WHEN FND_API.G_EXC_ERROR THEN
14199: EXCEPTION
14200: WHEN FND_API.G_EXC_ERROR THEN
14201: ROLLBACK;
14202: retcode := 2;
14203: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
14204: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Assign_DSO'|| '-' || SQLERRM);
14205: igs_ge_msg_stack.conc_exception_hndl;
14206: WHEN OTHERS THEN
14207: ROLLBACK;
14200: WHEN FND_API.G_EXC_ERROR THEN
14201: ROLLBACK;
14202: retcode := 2;
14203: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
14204: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Assign_DSO'|| '-' || SQLERRM);
14205: igs_ge_msg_stack.conc_exception_hndl;
14206: WHEN OTHERS THEN
14207: ROLLBACK;
14208: retcode := 2;
14205: igs_ge_msg_stack.conc_exception_hndl;
14206: WHEN OTHERS THEN
14207: ROLLBACK;
14208: retcode := 2;
14209: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
14210: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Assign_DSO'|| '-' || SQLERRM);
14211: igs_ge_msg_stack.conc_exception_hndl;
14212: END Assign_DSO;
14213:
14206: WHEN OTHERS THEN
14207: ROLLBACK;
14208: retcode := 2;
14209: fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
14210: fnd_message.set_token ('NAME', 'igs_sv_batch_process_pkg.Assign_DSO'|| '-' || SQLERRM);
14211: igs_ge_msg_stack.conc_exception_hndl;
14212: END Assign_DSO;
14213:
14214: END igs_sv_batch_process_pkg;